五厂设备数字化
一、数据抓取相关
通用协议了解：QS、OPC、HTTP/HTTPS/TCP/UDP、websocket
1.相机数据获取
①BT相机
BT上料相机不良数据抓取.py
点击查看代码
import os
from datetime import datetime, timedelta
import pyodbc
import asyncio
import threading
import logging
import time
from concurrent.futures import ThreadPoolExecutor
shared_folders = [
('01A', '\\\\172.19.128.12\e\pic-A', '制造一部'),
('01B', '\\\\172.19.128.12\e\pic-B', '制造一部'),
('02A', '\\\\172.19.128.22\e\pic-A', '制造一部'),
('02B', '\\\\172.19.128.22\e\pic-B', '制造一部'),
('03A', '\\\\172.19.128.32\e\pic-A', '制造一部'),
('03B', '\\\\172.19.128.32\e\pic-B', '制造一部'),
('04A', '\\\\172.19.128.42\e\pic-A', '制造一部'),
('04B', '\\\\172.19.128.42\e\pic-B', '制造一部'),
('05A', '\\\\172.19.128.52\e\pic-A', '制造一部'),
('05B', '\\\\172.19.128.52\e\pic-B', '制造一部'),
('06A', '\\\\172.19.128.62\e\pic-a', '制造一部'),
('06B', '\\\\172.19.128.62\e\pic-b', '制造一部'),
('07A', '\\\\172.19.128.72\e\\210-a', '制造一部'),
('07B', '\\\\172.19.128.72\e\\210-b', '制造一部'),
('08A', '\\\\172.19.128.82\e\pic-a', '制造一部'),
('08B', '\\\\172.19.128.82\e\pic-b', '制造一部'),
('09A', '\\\\172.19.128.92\e\pic-a', '制造一部'),
('09B', '\\\\172.19.128.92\e\pic-b', '制造一部'),
('10A', '\\\\172.19.128.102\e\saveimage-a', '制造一部'),
('10B', '\\\\172.19.128.102\e\saveimage-b', '制造一部'),
('11A', '\\\\172.19.128.112\e\A-image', '制造二部'),
('11B', '\\\\172.19.128.112\e\B-image', '制造二部'),
('12A', '\\\\172.19.128.122\e\images-A', '制造二部'),
('12B', '\\\\172.19.128.122\e\images-B', '制造二部'),
('13A', '\\\\172.19.128.132\e\pic-a', '制造二部'),
('13B', '\\\\172.19.128.132\e\pic-b', '制造二部'),
('14A', '\\\\172.19.128.142\e\A-image', '制造二部'),
('14B', '\\\\172.19.128.142\e\B-image', '制造二部'),
('15A', '\\\\172.19.128.152\e\pic-a', '制造二部'),
('15B', '\\\\172.19.128.152\e\pic-b', '制造二部'),
('16A', '\\\\172.19.128.162\e\pic-a', '制造二部'),
('16B', '\\\\172.19.128.162\e\pic-b', '制造二部'),
('17A', '\\\\172.19.128.172\e\pic-a', '制造二部'),
('17B', '\\\\172.19.128.172\e\pic-b', '制造二部'),
('18A', '\\\\172.19.128.182\e\pic-a', '制造二部'),
('18B', '\\\\172.19.128.182\e\pic-b', '制造二部'),
('19A', '\\\\172.19.128.192\e\pic-a', '制造二部'),
('19B', '\\\\172.19.128.192\e\pic-b', '制造二部'),
('20A', '\\\\172.19.128.202\e\pic-a', '制造二部'),
('20B', '\\\\172.19.128.202\e\pic-b', '制造二部'),
]
logging.basicConfig(filename='btl_camera.log', level=logging.INFO,
format='%(asctime)s [%(levelname)s] - %(message)s')
# 数据库连接配置
server = '172.16.98.174'
database = 'Google'
username = 'temis'
password = 'Apple2023'
connection_string = f"DRIVER=ODBC Driver 17 for SQL Server;SERVER={server};DATABASE={database};UID={username};PWD={password}"
connection = pyodbc.connect(connection_string)
cursor = connection.cursor()
# 线程锁
db_lock = threading.Lock()
async_lock = asyncio.Lock()
def get_insert_update(dt, create_hours, eqp_id, camera_id, dept, qty, update_time, hour_key, wafer_type):
try:
with db_lock:
# 查询数据库中的数据
cursor.execute(
"SELECT TOP 1 qty FROM eqp.btl_camera_data WHERE dt = ? AND create_hours = ? AND eqp_id = ? AND camera_id = ? AND dept = ? AND hour_key = ? AND wafer_type = ?",
(dt, create_hours, eqp_id, camera_id, dept, hour_key, wafer_type))
row = cursor.fetchone()
data_changed = False
# 检查是否需要插入或更新数据
if row is None or qty != row.qty:
if row is None:
# 插入新数据
insert_sql = f"INSERT INTO eqp.btl_camera_data (qty, update_time, dt, create_hours, eqp_id, camera_id, dept, hour_key, wafer_type) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
cursor.execute(insert_sql,
(qty, update_time, dt, create_hours, eqp_id, camera_id, dept, hour_key, wafer_type))
print(f'数据插入成功时间为{update_time}---->', dt, create_hours, eqp_id, camera_id, dept, qty,
hour_key, wafer_type)
data_changed = True
else:
# 更新数据
if qty != row.qty:
update_sql = f"UPDATE eqp.btl_camera_data SET qty = ?, update_time = ? WHERE dt = ? AND create_hours = ? AND eqp_id = ? AND camera_id = ? AND dept = ? AND hour_key = ? AND wafer_type = ?"
cursor.execute(update_sql,
(qty, update_time, dt, create_hours, eqp_id, camera_id, dept, hour_key,
wafer_type))
print(f'数据更新成功时间为{update_time}---->', dt, create_hours, eqp_id, camera_id, dept, qty,
hour_key, wafer_type)
data_changed = True
if data_changed:
connection.commit()
else:
# print('数据未更新，无需重复插入或更新')
pass
except Exception as e:
logging.error(f"{insert_sql}or{update_sql}插入数据库报错: {str(e)}")
# 处理拿到每条线每个班各个相机下每个时间段的不良数
def get_all_counts(all_counts, eqp_dept, eqp_id, date_folder):
for eqp_id_inner, eqp_data in all_counts.items():
for camera, category_data in eqp_data.items():
for category, hourly_counts in category_data.items():
for hour_key, count in hourly_counts.items():
dt = hour_key.split()[0]
create_hours = int(hour_key.split()[1].split(":")[0])
dept = eqp_dept
camera_id = camera
qty = count
update_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
wafer_type = category
# print(dt, create_hours, eqp_id_inner, camera_id, dept, qty, update_time, hour_key, wafer_type)
get_insert_update(dt, create_hours, eqp_id_inner, camera_id, dept, qty, update_time,
hour_key,
wafer_type)
print(
f'--------------------------------------<<{eqp_id}>> 在 <<{date_folder}>> 的文件夹数据处理完毕----------------------------------------')
def process_folders(eqp_id, eqp_path, eqp_dept):
# 遍历文件夹
while True:
eqp_dir = os.path.join(eqp_path, "Original")
# 获取当前日期和过去三天的日期
current_date = datetime.now().date()
recent_dates = [current_date - timedelta(days=i) for i in range(4)]
# recent_dates = [current_date - timedelta(days=3)]
# print(recent_dates)
date_folders = [f for f in os.listdir(eqp_dir) if os.path.isdir(os.path.join(eqp_dir, f))]
for date_folder in date_folders:
# 检查日期是否在最近三天内
if datetime.strptime(date_folder, "%Y%m%d").date() in recent_dates:
date_folder_path = os.path.join(eqp_dir, date_folder)
# print(date_folder_path) # \\172.19.128.82\pic-a\Original\20231126
all_counts = {eqp_id: {}}
# 初始化计数
try:
for hk in os.listdir(date_folder_path):
# 创建一个嵌套字典用于存储每个小时的图片数量
all_counts[eqp_id][hk] = {}
try:
fail_path = os.path.join(os.path.join(date_folder_path, hk), 'Fail')
for category in os.listdir(fail_path):
category_folder_path = os.path.join(fail_path, category)
hourly_counts = {}
# 遍历类别文件夹中的每个 BMP 文件
try:
for root, dirs, files in os.walk(category_folder_path):
for file in files:
if file.endswith('.BMP'):
# 从文件路径中提取信息
modification_time = os.path.getmtime(os.path.join(root, file))
modification_datetime = datetime.fromtimestamp(modification_time)
hour_key = modification_datetime.strftime("%Y-%m-%d %H")
if hour_key not in hourly_counts:
hourly_counts[hour_key] = 0
hourly_counts[hour_key] += 1
# 将相机的小时计数添加到所有计数中
all_counts[eqp_id][hk][category] = hourly_counts
except Exception as e:
print(f"{eqp_id}处理不良分类目录 {category_folder_path} 时出现错误: {str(e)}")
logging.error(
f"{eqp_id}处理不良分类目录 {category_folder_path} 时出现错误: {str(e)}")
continue
except Exception as e:
print(f"{eqp_id}处理Fail目录 {fail_path} 时出现错误: {str(e)}")
logging.error(f"{eqp_id}处理Fail目录 {fail_path} 时出现错误: {str(e)}")
continue
except Exception as e:
print(f"{eqp_id}处理日期目录 {date_folder_path} 时出现错误: {str(e)}")
logging.error(f"{eqp_id}处理日期目录 {date_folder_path} 时出现错误: {str(e)}")
continue
# print(all_counts) # 这里是能拿到一个班的各个相机各个时间段的数量
get_all_counts(all_counts, eqp_dept, eqp_id, date_folder)
print(
f'-------------------------------------------------------------><<<{eqp_id}>>>在时间段内处理完毕， 等待下一个时间段继续处理！！<------------------------------------------------------------------------')
time.sleep(600)
from concurrent.futures import ThreadPoolExecutor, as_completed
from tqdm import tqdm
def main():
with ThreadPoolExecutor(max_workers=100) as executor:
futures = []
for eqp_id, eqp_path, eqp_dept in shared_folders:
futures.append((eqp_id, executor.submit(process_folders, eqp_id, eqp_path, eqp_dept)))
for eqp_id, future in as_completed(futures, key=lambda x: x[1]):
try:
future.result()
except Exception as e:
print(f"Error processing {eqp_id}: {str(e)}")
if __name__ == "__main__":
main()
点击查看代码
import os
from datetime import datetime, timedelta
import pyodbc
import asyncio
import threading
import logging
import time
from concurrent.futures import ThreadPoolExecutor
shared_folders = [
('BTU01', '\\\\172.19.128.17\e\SaveImg', '制造一部'),
('BTU02', '\\\\172.19.128.27\e\SaveImg', '制造一部'),
('BTU03', '\\\\172.19.128.37\e\SaveImg', '制造一部'),
('BTU04', '\\\\172.19.128.47\e\SaveImg', '制造一部'),
('BTU05', '\\\\172.19.128.57\e\SaveImg', '制造一部'),
('BTU06', '\\\\172.19.128.67\e\SaveImg', '制造一部'),
('BTU07', '\\\\172.19.128.77\e\SaveImg', '制造一部'),
('BTU08', '\\\\172.19.128.87\e\SaveImg', '制造一部'),
('BTU09', '\\\\172.19.128.97\e\SaveImg', '制造一部'),
('BTU10', '\\\\172.19.128.107\e\SaveImg', '制造一部'),
('BTU11', '\\\\172.19.128.117\e\SaveImg', '制造二部'),
('BTU12', '\\\\172.19.128.127\e\SaveImg', '制造二部'),
('BTU13', '\\\\172.19.128.137\e\SaveImg', '制造二部'),
('BTU14', '\\\\172.19.128.147\e\SaveImg', '制造二部'),
('BTU15', '\\\\172.19.128.157\e\SaveImg', '制造二部'),
('BTU16', '\\\\172.19.128.167\e\SaveImg', '制造二部'),
('BTU17', '\\\\172.19.128.177\e\SaveImg', '制造二部'),
('BTU18', '\\\\172.19.128.187\e\SaveImg', '制造二部'),
('BTU19', '\\\\172.19.128.197\e\SaveImg', '制造二部'),
('BTU20', '\\\\172.19.128.207\e\SaveImg', '制造二部'),
]
logging.basicConfig(filename='btu_camera.log', level=logging.INFO,
format='%(asctime)s [%(levelname)s] - %(message)s')
# 数据库连接配置
server = '172.16.98.174'
database = 'Google'
username = 'temis'
password = 'Apple2023'
connection_string = f"DRIVER=ODBC Driver 17 for SQL Server;SERVER={server};DATABASE={database};UID={username};PWD={password}"
connection = pyodbc.connect(connection_string)
cursor = connection.cursor()
# 线程锁
db_lock = threading.Lock()
async_lock = asyncio.Lock()
def get_insert_update(dt, create_hours, eqp_id, camera_id, dept, qty, update_time, hour_key, wafer_type):
try:
with db_lock:
# 查询数据库中的数据
cursor.execute(
"SELECT TOP 1 qty FROM eqp.btu_camera_data WHERE dt = ? AND create_hours = ? AND eqp_id = ? AND camera_id = ? AND dept = ? AND hour_key = ? AND wafer_type = ?",
(dt, create_hours, eqp_id, camera_id, dept, hour_key, wafer_type))
row = cursor.fetchone()
data_changed = False
# 检查是否需要插入或更新数据
if row is None or qty != row.qty:
if row is None:
# 插入新数据
insert_sql = f"INSERT INTO eqp.btu_camera_data (qty, update_time, dt, create_hours, eqp_id, camera_id, dept, hour_key, wafer_type) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
cursor.execute(insert_sql,
(qty, update_time, dt, create_hours, eqp_id, camera_id, dept, hour_key, wafer_type))
print(f'数据插入成功时间为{update_time}---->', dt, create_hours, eqp_id, camera_id, dept, qty,
hour_key, wafer_type)
data_changed = True
else:
# 更新数据
if qty != row.qty:
update_sql = f"UPDATE eqp.btu_camera_data SET qty = ?, update_time = ? WHERE dt = ? AND create_hours = ? AND eqp_id = ? AND camera_id = ? AND dept = ? AND hour_key = ? AND wafer_type = ?"
cursor.execute(update_sql,
(qty, update_time, dt, create_hours, eqp_id, camera_id, dept, hour_key,
wafer_type))
print(f'数据更新成功时间为{update_time}---->', dt, create_hours, eqp_id, camera_id, dept, qty,
hour_key, wafer_type)
data_changed = True
if data_changed:
connection.commit()
else:
# print('数据未更新，无需重复插入或更新')
pass
except Exception as e:
logging.error(f"{insert_sql}or{update_sql}插入数据库报错: {str(e)}")
# 处理拿到每条线每个班各个相机下每个时间段的不良数
def get_all_counts(all_counts, eqp_dept, eqp_id, date_folder):
for eqp_id_inner, eqp_data in all_counts.items():
for camera, camera_data in eqp_data.items():
if camera_data:
for hour_key, count in camera_data.items():
dt = hour_key.split()[0]
create_hours = int(hour_key.split()[1].split(":")[0])
dept = eqp_dept
camera_id = camera
qty = count
update_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
wafer_type = 'NG片'
# print(dt, create_hours, eqp_id_inner, camera_id, dept, qty, update_time, hour_key, wafer_type)
get_insert_update(dt, create_hours, eqp_id_inner, camera_id, dept, qty, update_time, hour_key, wafer_type)
print(
f'--------------------------------------<<{eqp_id}>> 在 <<{date_folder}>> 的文件夹数据处理完毕----------------------------------------')
def process_folders(eqp_id, eqp_path, eqp_dept):
# 遍历文件夹
while True:
eqp_dir = eqp_path
# 获取当前日期和过去三天的日期
current_date = datetime.now().date()
recent_dates = [current_date - timedelta(days=i) for i in range(3)]
# recent_dates = [current_date - timedelta(days=3)]
# print(recent_dates)
date_folders = [f for f in os.listdir(eqp_dir) if os.path.isdir(os.path.join(eqp_dir, f))]
for date_folder in date_folders:
# 检查日期是否在最近三天内
if datetime.strptime(date_folder, "%Y年%m月%d日").date() in recent_dates:
date_folder_path = os.path.join(eqp_dir, date_folder)
# print(date_folder_path) # \\172.19.128.17\e\SaveImg\2023年12月02日
all_counts = {eqp_id: {}}
# 初始化计数
try:
for camera in os.listdir(date_folder_path):
# 创建一个嵌套字典用于存储每个小时的图片数量
all_counts[eqp_id][camera] = {}
camera_folder_path = os.path.join(date_folder_path, camera)
hourly_counts = {}
# 遍历类别文件夹中的每个 BMP 文件
try:
if camera_folder_path.endswith('NG'):
for root, dirs, files in os.walk(camera_folder_path):
for file in files:
if file.endswith('.jpg'):
# 从文件路径中提取信息
modification_time = os.path.getmtime(os.path.join(root, file))
modification_datetime = datetime.fromtimestamp(modification_time)
hour_key = modification_datetime.strftime("%Y-%m-%d %H")
if hour_key not in hourly_counts:
hourly_counts[hour_key] = 0
hourly_counts[hour_key] += 1
# 将相机的小时计数添加到所有计数中
all_counts[eqp_id][camera] = hourly_counts
except Exception as e:
print(f"{eqp_id}处理相机目录 {camera_folder_path} 时出现错误: {str(e)}")
logging.error(
f"{eqp_id}处理相机目录 {camera_folder_path} 时出现错误: {str(e)}")
continue
except Exception as e:
print(f"{eqp_id}处理日期目录 {date_folder_path} 时出现错误: {str(e)}")
logging.error(f"{eqp_id}处理日期目录 {date_folder_path} 时出现错误: {str(e)}")
continue
# print(all_counts, eqp_dept, eqp_id, date_folder) # 这里是能拿到一个班的各个相机各个时间段的数量
get_all_counts(all_counts, eqp_dept, eqp_id, date_folder)
print(
f'-------------------------------------------------------------><<<{eqp_id}>>>在时间段内处理完毕， 等待下一个时间段继续处理！！<------------------------------------------------------------------------')
time.sleep(600)
from concurrent.futures import ThreadPoolExecutor, as_completed
from tqdm import tqdm
def main():
with ThreadPoolExecutor(max_workers=100) as executor:
futures = []
for eqp_id, eqp_path, eqp_dept in shared_folders:
futures.append((eqp_id, executor.submit(process_folders, eqp_id, eqp_path, eqp_dept)))
for eqp_id, future in as_completed(futures, key=lambda x: x[1]):
try:
future.result()
except Exception as e:
print(f"Error processing {eqp_id}: {str(e)}")
if __name__ == "__main__":
main()
②HF相机
③PL相机
二、报表相关
1.湿制程
2.高温制程
3.镀膜制程
4.金属化制程
5.综合制程
三、大屏看板相关
1.湿制程
2.高温制程
3.镀膜制程
4.金属化制程
5.综合制程
四、系统相关
需求新增
- SE破片追溯报表修改
- 包装机看板增加每小时CT产量和包装机产量对比+工控机Excel不良NG类型片数
| 时间 | 产品名称 | 磕伤是否检出 | 长断片是否检出 | 夹断片是否检出 | 遮挡是否检出 | 漏浆是否检出 | 整形不齐是否检出 |
|---|---|---|---|---|---|---|---|
| 2024/01/11 08:00:21:8908 | 产品1_11_PC40000IJ9 | 1 | 0 | 0 | 0 | 0 | 0 |
| 2024/01/11 08:01:08:0911 | 产品2_11_PC40000II3 | 1 | 0 | 0 | 0 | 0 | 0 |
| 2024/01/11 08:01:19:1393 | 产品3_11_PC40000IIS | 0 | 0 | 0 | 1 | 0 | 0 |
- 金属化制程看板报警页面+不分一二部分白夜班状态管理页
- BTL过检率看板展示+湿制程故障历史