import query_db import json import csv from datetime import datetime from PyQt5.QtWidgets import ( QApplication, QMainWindow, QVBoxLayout, QFileDialog,) def export_data(): client = query_db.bigata_mongo() sa_device_type = client.sa_device_type sa_device = client.sa_device sa_device_cbd_data = client.sa_device_cbd_data sa_device_bzy_data = client.sa_device_bzy_data sa_device_scd_data = client.sa_device_scd_data sa_device_xy_three_data = client.sa_device_xy_three_data sa_device_xct_data = client.sa_device_xct_data xct_haomi_data = client.xct_haomi_data sa_qxz_base_info = client.sa_qxz_base_info # 先判断是否有输入用户名，任务单号或设备号 search_input01_text = "数字大棚" search_input02_text = "" search = {} if (not search_input01_text) and (not search_input02_text): return if search_input01_text: print(search_input01_text) rek = query_db.user_info(search_input01_text) if rek: search.update(rek) else: return if search_input02_text: search.update({"salesman_task_number": search_input02_text}) result = sa_device.find( search, { "device_id": 1, "device_name":1, "device_status": 1, "device_type_id": 1, "device_model": 1, "salesman_task_number": 1, "province": 1, "city": 1, "district": 1, "addtime": 1, "uptime": 1, "off_time": 1, "simid": 1, "id":1, "_id":0 } ) result = list(result) title = [ "ID", "名称", "状态", "设备类型", "任务单号", "地址", "设备添加时间", "最近更新时间", "离线时间", "图片ICCID", "图片ICCD过期时间", "图片ICCD厂家", "设备ICCID", "设备ICCID过期时间", "设备ICCID厂家" ] if result: device_type_dict = sa_device_type.find({}, {"id": 1, "type_name": 1, "_id": 0}) device_type_dict = {str(i.get("id")): i.get("type_name") for i in list(device_type_dict)} # 弹出文件保存对话框让用户选择保存路径 # 获取文件保存路径 file_path = "device.csv" # 执行导出操作，将设备名称保存为CSV文件 try: with open(file_path, mode='w', newline='', encoding='utf-8') as file: writer = csv.writer(file) writer.writerow(title) for row in result: d_id = row.get("id") img_iccid = row.get("simid") device_type_id = row.get("device_type_id") if img_iccid: iccid_info = query_db.sim_query_new(img_iccid) else: iccid_info = {} expire = iccid_info.get("expire", "") company = iccid_info.get("company", "") device_model = row.get("device_model") model = None if device_type_id == 3: if device_model == 11: device_type_name = "虫情信息采集设备" else: device_type_name = "虫情测报灯" model = sa_device_cbd_data elif device_type_id == 7: if device_model == 50: device_type_name = "孢子仪1.0" elif device_model == 52: device_type_name = "孢子仪2.0" else: device_type_name = "孢子仪" model = sa_device_bzy_data elif device_type_id == 2: if device_model == 101: device_type_name = "风吸式杀虫灯" else: device_type_name = "杀虫灯" model = sa_device_scd_data elif device_type_id == 8: device_type_name = device_type_dict.get(str(device_type_id)) model = sa_device_xy_three_data elif device_type_id == 12: device_type_name = device_type_dict.get(str(device_type_id)) model = sa_device_xct_data elif device_type_id == 28: device_type_name = device_type_dict.get(str(device_type_id)) model = xct_haomi_data else: device_type_name = device_type_dict.get(str(device_type_id)) device_status = row.get("device_status") status = "在线" if device_status == 1 else "离线" device_iccid, device_expire, device_company = "", "", "" if device_type_id == 5: qxz = sa_qxz_base_info.find({"device_id": row.get("device_id")}, {"iccid": 1, "_id": 0}).sort("uptime", -1).limit(1) qxz = list(qxz) if qxz: try: device_iccid = qxz[0].get("iccid") device_iccid_info = query_db.sim_query_new(device_iccid) except Exception as e: device_iccid_info = {} device_expire = device_iccid_info.get("expire", "") device_company = device_iccid_info.get("company", "") else: if model: device_data = model.find({"device_id": d_id}, {"device_data":1, "_id":0}).sort("addtime", -1).limit(1) d_data = list(device_data) if d_data: device_data_str = d_data[0].get("device_data") device_data_json = {} if device_data_str: try: device_data_json = eval(device_data_str) except Exception as e: device_data_json = json.loads(device_data_str) device_iccid = device_data_json.get("iccid") if device_iccid: device_iccid_info = query_db.sim_query_new(device_iccid) else: device_iccid_info = {} device_expire = device_iccid_info.get("expire", "") device_company = device_iccid_info.get("company", "") data = [ row.get("device_id"), row.get("device_name"), status, device_type_name, row.get("salesman_task_number"), row.get("province") + row.get("city") + row.get("district"), datetime.fromtimestamp(row.get("addtime")).strftime("%Y-%m-%d %H:%M:%S") if row.get("off_time") != 0 else "", datetime.fromtimestamp(row.get("uptime")).strftime("%Y-%m-%d %H:%M:%S") if row.get("off_time") != 0 else "", datetime.fromtimestamp(row.get("off_time")).strftime("%Y-%m-%d %H:%M:%S") if row.get("off_time") != 0 else "", img_iccid, expire, company, device_iccid, device_expire, device_company ] writer.writerow(data) return except Exception as e: print(e) return else: return if __name__ == "__main__": export_data()