diff --git "a/Sql\350\257\246\346\203\205\351\231\204\345\275\225/\350\207\252\345\212\250\346\243\200\345\256\232.sql" "b/Sql\350\257\246\346\203\205\351\231\204\345\275\225/\350\207\252\345\212\250\346\243\200\345\256\232.sql" new file mode 100644 index 0000000..472d062 --- /dev/null +++ "b/Sql\350\257\246\346\203\205\351\231\204\345\275\225/\350\207\252\345\212\250\346\243\200\345\256\232.sql" @@ -0,0 +1,369 @@ +部门任务 +select bi.id as "序号",br.sample_name as "设备名称",br.sample_model as "型号规格", ei.help_instruction as "辅助字段",ei.manufacture_no as "出厂编号",ei.manufacturer as "生产厂家",bi.customer_name as "委托方",dept.FULL_NAME as "使用部门",bi.require_over_time as "要求检完时间",bi.is_urgent as "是否加急" from biz_business_order_info bi,biz_business_order_sample_relation br,eqpt_equipment_info ei,sys_dept as dept WHERE bi.id=br.order_id AND br.sample_no=ei.equipment_no AND ei.dept_id=dept.ID + +SELECT + bi.id AS "序号", + br.sample_name AS "设备名称", + br.sample_model AS "型号规格", + ei.help_instruction AS "辅助字段", + ei.manufacture_no AS "出厂编号", + ei.manufacturer AS "生产厂家", + bi.customer_name AS "委托方", + dept.FULL_NAME AS "使用部门", + bi.require_over_time AS "要求检完时间", + bi.is_urgent AS "是否加急" +FROM + biz_business_order_info bi +LEft JOIN + biz_business_order_sample_relation br ON bi.id = br.order_id +LEft JOIN + eqpt_equipment_info ei ON br.sample_no = ei.equipment_no +LEft JOIN + sys_dept dept ON ei.dept_id = dept.ID +LEft JOIN + sys_user user ON user.ID = bi.create_user_id + WHERE user.id = '1693585369401880577' + + ------------------------------------------------------------------------------ +我的任务 +SELECT + bi.id AS "序号", + br.sample_name AS "设备名称", + br.sample_model AS "型号规格", + ei.help_instruction AS "辅助字段", + ei.manufacture_no AS "出厂编号", + ei.manufacturer AS "生产厂家", + bi.customer_name AS "委托方", + dept.FULL_NAME AS "使用部门", + bi.require_over_time AS "要求检完时间", + bi.is_urgent AS "是否加急" +FROM + biz_business_order_info bi +LEfT JOIN + biz_business_order_sample_relation br ON bi.id = br.order_id +LEfT JOIN + eqpt_equipment_info ei ON br.sample_no = ei.equipment_no +LEfT JOIN + sys_dept dept ON ei.dept_id = dept.ID +WHERE bi.create_user_id = '1640229293790650370' + ----------------------------------------------------------------------------------------- + 创建任务单->任务单列表 + SELECT bi.order_no as "任务单编号",bi.customer_name as "委托方名称",dept.FULL_NAME as "使用部门", bi.require_over_time AS "要求检完时间",bi.deliverer as "送检人",bi.is_urgent AS "是否加急" FROM biz_business_order_info bi,sys_dept dept WHERE bi.customer_id = dept.ID + + + 创建任务单->查看任务单 + SELECT bi.order_no as "任务单编号",bi.customer_name as "委托方名称",dept.FULL_NAME as "使用部门", bi.require_over_time AS "要求检完时间",bi.deliverer as "送检人",bi.deliverer_tel as "送检人电话",bi.undertaker_name as "承接人",bi.undertake_time as "承接时间",bi.is_urgent AS "是否加急",bi.receive_illustrate as "接受说明" FROM biz_business_order_info bi,sys_dept dept WHERE bi.customer_id = dept.ID AND bi.order_no = 'rwd202308220001' + + + 优化后 + + SELECT + bi.order_no AS "任务单编号", + bi.customer_name AS "委托方名称", + dept.FULL_NAME AS "使用部门", + bi.require_over_time AS "要求检完时间", + bi.deliverer AS "送检人", + bi.deliverer_tel AS "送检人电话", + bi.undertaker_name AS "承接人", + bi.undertake_time AS "承接时间", + bi.is_urgent AS "是否加急", + bi.receive_illustrate AS "接受说明" +FROM + biz_business_order_info bi + LEFT JOIN sys_dept dept ON bi.customer_id = dept.ID +WHERE + bi.order_no = 'rwd202308220001'; + 创建任务单->任务单列表（设备） +-- SELECT br.id as "序号", br.sample_name AS "设备名称",br.sample_model AS "型号规格", ei.manufacture_no AS "出厂编号",ei.manufacturer AS "生产厂家" FROM +-- biz_business_order_sample_relation br,eqpt_equipment_info ei WHERE br.order_id = "1696131409221058561" + +-- SELECT br.id as "序号",br.sample_name AS "设备名称",br.sample_model AS "型号规格", ei.manufacture_no AS "出厂编号",ei.manufacturer AS "生产厂家" +-- FROM biz_business_order_sample_relation br,eqpt_equipment_info ei WHERE br.order_id = "1717702029852635137" AND br.sample_no = ei.equipment_no +-- +-- SELECT br.id as "序号",br.sample_name AS "设备名称",br.sample_model AS "型号规格", ei.manufacture_no AS "出厂编号",ei.manufacturer AS "生产厂家", +-- ea.name as "附件" FROM biz_business_order_sample_relation br,eqpt_equipment_info ei,eqpt_equipment_attachment ea WHERE br.order_id = "1717702029852635137" AND br.sample_no = ei.equipment_no AND br.sample_id=ea.equipment_id + + + SELECT br.id as "序号", + br.sample_name AS "设备名称", + br.sample_model AS "型号规格", + ei.manufacture_no AS "出厂编号", + ei.manufacturer AS "生产厂家", + br.appendix_descn as "附件", + br.special_require as "特殊要求", + br.measure_complete_time as "检定完成时间", + bbdmi.conclusion as "检定结果", + ei.limit_instruction as "限用" + FROM biz_business_order_sample_relation br + Left JOIN eqpt_equipment_info ei ON br.sample_no = ei.equipment_no + Left JOIN biz_business_device_measure_info bbdmi ON br.order_id = bbdmi.order_id + WHERE br.order_id = "1735863426170793985"; + + + + --------------------------------------------------------------------------------------------------- + +创建任务单 -- 新建 + + +-- 假设已有sys_dept表中对应的新客户ID的记录 + +-- 插入biz_business_order_info表的新记录 +INSERT INTO biz_business_order_info ( + order_no, customer_name, require_over_time, deliverer, deliverer_tel, + undertaker_name, undertake_time, is_urgent, receive_illustrate, customer_id +) VALUES ( + '新任务单编号', '新委托方名称', '2023-09-01 14:30:00', -- 假设require_over_time为datetime类型 + '新送检人', '13800138000', '新承接人', '2023-09-02 09:00:00', -- 假设undertake_time为datetime类型 + true, -- 假设is_urgent为boolean类型，true表示是加急，false表示不是 + '新接受说明', + 新客户ID -- 这个ID应与sys_dept表中已存在的ID相对应 +); + + +-- 插入eqpt_equipment_info表 +INSERT INTO eqpt_equipment_info (manufacture_no, manufacturer, equipment_no) +VALUES ('新出厂编号', '新生产厂家', '新设备编号'); + +-- 插入biz_business_order_sample_relation表 +INSERT INTO biz_business_order_sample_relation (order_id,sample_id,sample_no, sample_name, sample_model, , measure_complete_time) +VALUES ('1697135299001356290', '设备的id','新设备编号', '新设备名称', '新型号规格', '新检定完成时间'); +查询被检设备和任务单关联表 +SELECT bbosr.id,bbosr.sample_id,bbosr.sample_name,bbosr.sample_model FROM biz_business_order_sample_relation bbosr +SELECT eei.help_instruction,eei.manufacture_no,eei.manufacturer FROM eqpt_equipment_info eei +WHERE id = '1715284942530105345' + + + + + + + + + +创建任务单-更新 +UPDATE biz_business_order_info bi +JOIN sys_dept dept ON bi.customer_id = dept.ID +SET + bi.order_no = '新任务单编号', -- 修改任务单编号 + bi.customer_name = '新委托方名称', -- 修改委托方名称 + bi.require_over_time = '新要求检完时间', -- 修改要求检完时间（假设为日期时间格式） + bi.deliverer = '新送检人', -- 修改送检人 + bi.deliverer_tel = '新送检人电话', -- 修改送检人电话 + bi.undertaker_name = '新承接人', -- 修改承接人 + bi.undertake_time = '新承接时间', -- 修改承接时间（假设为日期时间格式） + bi.is_urgent = 新是否加急值, -- 修改是否加急（如果是布尔类型则填true/false或1/0） + bi.receive_illustrate = '新接受说明' -- 修改接受说明 +WHERE + bi.order_no = 'rwd202308220001'; -- 根据原始任务单编号定位记录 + + UPDATE sys_dept + SET FULL_NAME = '新部门名称' + WHERE ID = (SELECT customer_id FROM biz_business_order_info WHERE order_no = 'rwd202308220001'); + + +创建任务单--删除 + +DELETE FROM biz_business_order_info WHERE order_no = '指定的任务单编号'; + + + +------------------------------------------------------------------------------------------ +标准设备管理 + + + +SELECT bei.id as "序号",bei.equipment_name as "设备名称",bei.model as "型号规格",bei.manufacture_no as "出厂编号",bei.manufacturer as "生产厂家" FROM biz_equipment_info bei + + +SELECT bei.equipment_name as "设备名称",bei.model as "型号规格",bei.manufacture_no as "出厂编号",bei.measure_valid_date as "检定有效期", +bei.product_country as "生产国家",bei.manufacturer as "生产厂家",bei.produce_date as "生产日期",bei.purchase_date as "购进日期", +bei.unit_price as "设备单价",bei.usage_status as "使用状态",bei.quality_status as "质量状况",bei.dept_name as "所属部门",bei.director_name as "负责人",bei.supplier_name as "设备供方",bei.location as "位置信息",bei.type as "设备类型",bei.standard_type as "标准类型",bei.category as "设备分类",bei.level as "重要等级",bei.measure_cycle as "检定周期",bei.unpack_record_name as "对应开箱记录",bei.remark as "备注" FROM biz_equipment_info bei WHERE bei.id = '1692772193007128577' + + + + +SELECT * from biz_equipment_info +WHERE biz_equipment_info.meter_standard_id = '1724358183538257921' + + + + + +SELECT bei.meter_standard_id FROM biz_equipment_info bei WHERE bei.id = '1692772193007128577' +SELECT besi.standard_name FROM biz_equipment_standard_info besi WHERE besi.id = "1724358183538257921" +合并为 +SELECT besi.standard_name FROM biz_equipment_standard_info besi WHERE besi.id = ( SELECT bei.meter_standard_id FROM biz_equipment_info bei WHERE bei.id = '1752157193060225025' ) + +SELECT bescic.id FROM biz_equipment_standard_check_item_category bescic WHERE bescic.belong_standard_equipment_name = "直流稳压电源检定装置" + + + +SELECT bescidc.id as "序号",bescidc.check_type as "核查类型",bescidc.unit as "单位",bescidc.frequency as "频率",bescidc.check_point as "核查点",bescidc.resolution as "分辨力",bescidc.cycle_number as "循环次数",bescidc.urel as "Urel",bescidc.thoroughfare as "通道" ,bescidc.tfer as "TFER",bescidc.dccp as "DCCP",bescidc.flit as "FLIT" from biz_equipment_standard_check_item_data_calibrator bescidc WHERE bescidc.item_category_id = "1730190255448227842" + + +SELECT besciddp.id ,besciddp.params,besciddp.unit,resolution,besciddp.urel,besciddp.check_type,datum_point,cycle_number +,datum_point,resistance_value,resistance_value_unit,remark FROM biz_equipment_standard_check_item_data_dc_power besciddp WHERE item_category_id = (SELECT bescic.id FROM biz_equipment_standard_check_item_category bescic WHERE bescic.belong_standard_equipment_name = "直流稳压电源检定装置") + + + +------------------------------------------------------------------------ +被检设备管理 + +SELECT eei.id as "序号",eei.equipment_name as "设备名称",eei.model as "型号规格",eei.help_instruction as "辅助字段",eei.manufacture_no as "出厂编号",eei.manufacturer as "生产厂家",sys_dept.FULL_NAME as "部门",eei.meter_identify as "计量标识",eei.check_cycle as "检定周期",eei.certificate_valid as "证书有效期" from eqpt_equipment_info eei LEFT JOIN sys_dept ON sys_dept.ID = eei.dept_id + +查看设备信息 +设备基本信息 + +SELECT eei.equipment_name as "设备名称",eei.model as "型号规格",eei.help_instruction as "辅助字段",eei.manufacture_no as "出厂编号",eei.product_country as "生产国家",eei.manufacturer as "生产厂家",eei.product_date as "生产日期",eei.purchase_date as "购进日期",eei.unit_price as "设备单价",eei.usage_status as "使用状态",eei.quality_condition as "质量状况",sys_dept.FULL_NAME as "部门", +eei.category as "设备分类",eei.check_cycle as "检定周期", +eei.level as "重要等级",eei.remark as "备注" + from eqpt_equipment_info eei LEFT JOIN sys_dept ON sys_dept.ID = eei.dept_id + WHERE eei.id = '1697133619962744833' + +检定项目及检定点： + +SELECT eei.model_id from eqpt_equipment_info eei WHERE eei.id = '1716997603953311746' + +SELECT item_category_id FROM biz_business_device_measure_item_info bbdmii WHERE bbdmii.device_model_id = '1716987426248163330' +合并为： +SELECT item_category_id,belong_standard_equipment FROM biz_business_device_measure_item_info bbdmii WHERE bbdmii.device_model_id =(SELECT eei.model_id from eqpt_equipment_info eei WHERE eei.id = '1716997603953311746') + + + + +检定项多功能校准源数据 +SELECT bbdmidc.id as "序号",bbdmidc.params as "参数",bbdmidc.capacity as "量",bbdmidc.unit as "单位", +bbdmidc.frequency as "频率",bbdmidc.range_range as "量程",bbdmidc.standard_value as "标准值",bbdmidc.resolution as "分辨力",bbdmidc.maximum_error as "最大允许误差",bbdmidc.error_param_a as "误差参数a",bbdmidc.error_param_b as "误差参数b" +FROM biz_business_device_measure_item_data_calibrator bbdmidc WHERE item_id = '1717086652508487681' + +------------------------------------------------------------------------------------- +检定数据管理 + +SELECT bbdmi.id as "序号", bbdmi.sample_name as "设备名称",bbdmi.model as "型号规格",eei.help_instruction as "辅助字段",bbdmi.manufacture_no as "出厂编号", +bbdmi.manufacturer as "生产厂家",bboi.customer_name as "委托单位",dept.FULL_NAME as "使用部门",_user.NAME as "检定员",bbdmi.trace_date as "检定时间", +bbdmi.measure_valid_date as "检定有效期",eei.meter_identify as "计量标识",bbdmi.restriction_instruction as "限用说明" + FROM biz_business_device_measure_info bbdmi + LEFT JOIN eqpt_equipment_info eei ON bbdmi.sample_id = eei.id + LEFT JOIN biz_business_order_info bboi ON bbdmi.order_id = bboi.id + LEFT JOIN sys_dept dept ON eei.dept_id = dept.ID + LEFT JOIN sys_user _user ON bbdmi.create_user_id = _user.id + + +检定基本信息 +SELECT bbdmi.sample_name as "设备名称",bbdmi.model as "型号规格",eei.help_instruction as "辅助字段",bbdmi.manufacture_no as "出厂编号",bbdmi.manufacturer as "生产厂家", +bboi.customer_name as "委托单位",eemtte.based_documents as "依据技术文件", bbdmi.trace_date as "检定日期",bbdmi.temperature as "环境温度",bbdmi.humidity as "环境湿度", +bbdmi.measure_address as "检定地点",bbdmi.measure_valid_date as "检定有效期",_user.NAME as "检定员",bbdmi.conclusion as "结论",bbdmi.restriction_instruction as "限用说明" + +FROM biz_business_device_measure_info bbdmi + LEFT JOIN eqpt_equipment_info eei ON bbdmi.sample_id = eei.id + LEFT JOIN biz_business_order_info bboi ON bbdmi.order_id = bboi.id +LEFT JOIN biz_business_device_measure_item_info bbdmii ON bbdmi.item_id = bbdmii.id +LEFT JOIN eqpt_equipment_model_technical_target eemtte ON bbdmii.device_model_id = eemtte.model_id + LEFT JOIN sys_user _user ON bbdmi.create_user_id = _user.id +WHERE bbdmi.id = '1708657907573821442' +检定结果信息 + +SELECT eei.model_id from eqpt_equipment_info eei WHERE eei.id = '1716997603953311746' + +SELECT item_category_id,belong_standard_equipment FROM biz_business_device_measure_item_info bbdmii WHERE bbdmii.device_model_id = '1716987426248163330' + +检定项多功能校准源数据 + SELECT bbdmi.other_outcome as "外观及功能性检查",bbdmi.other_remark as "说明",item_id FROM biz_business_device_measure_info bbdmi + WHERE sample_id = '1735134534179074049' AND other_outcome LIKE "不合格" + + SELECT item_id FROM biz_business_device_measure_info bbdmi + WHERE sample_id = '1716997603953311746' + +根据是否 合格判断查询 + + +SELECT bbdmidc.id, bbdmidc.params, bbdmidc.capacity, bbdmidc.unit, + bbdmidc.frequency, bbdmidc.range_range, bbdmidc.standard_value, + bbdmidc.resolution, bbdmidc.maximum_error, bbdmidc.error_param_a, + bbdmidc.error_param_b +FROM biz_business_device_measure_item_data_calibrator bbdmidc +WHERE item_id = (SELECT CONVERT(item_id USING utf8mb4) COLLATE utf8mb4_0900_ai_ci + FROM biz_business_device_measure_info bbdmi + WHERE sample_id = '1735134534179074049' AND other_outcome = '合格'); + + + + + + +SELECT * FROM biz_business_device_measure_info +WHERE sample_id = '1716997603953311746' AND other_outcome is NULL + + SELECT bbdmidc.id as "序号",bbdmidc.params as "参数",bbdmidc.capacity as "量",bbdmidc.unit as "单位", +bbdmidc.frequency as "频率",bbdmidc.range_range as "量程",bbdmidc.standard_value as "标准值",bbdmidc.resolution as "分辨力",bbdmidc.maximum_error as "最大允许误差",bbdmidc.error_param_a as "误差参数a",bbdmidc.error_param_b as "误差参数b" +FROM biz_business_device_measure_item_data_calibrator bbdmidc WHERE item_id = ( SELECT CONVERT(item_id USING utf8mb4) COLLATE utf8mb4_0900_ai_ci FROM biz_business_device_measure_info bbdmi + WHERE sample_id = '1716997603953311746') + +SELECT item_id , other_outcome FROM biz_business_device_measure_info bbdmi + WHERE sample_id = '1716997603953311746' + + + +查询所有 +SELECT bbdmidc.id as "序号",bbdmidc.params as "参数",bbdmidc.capacity as "量",bbdmidc.unit as "单位", +bbdmidc.frequency as "频率",bbdmidc.range_range as "量程",bbdmidc.standard_value as "标准值",bbdmidc.resolution as "分辨力",bbdmidc.maximum_error as "最大允许误差",bbdmidc.error_param_a as "误差参数a",bbdmidc.error_param_b as "误差参数b",bbdmi.other_outcome as "合格率" +FROM biz_business_device_measure_item_data_calibrator bbdmidc +LEFT JOIN biz_business_device_measure_info bbdmi ON bbdmi.item_id = bbdmidc.item_id +WHERE bbdmidc.item_id = ( SELECT CONVERT(item_id USING utf8mb4) COLLATE utf8mb4_0900_ai_ci FROM biz_business_device_measure_info bbdmi + WHERE sample_id = '1716997603953311746' ) + +编辑 + + +UPDATE biz_business_device_measure_info bbdmi +LEFT JOIN biz_business_order_info bboi ON bbdmi.order_id = bboi.id +LEFT JOIN biz_business_device_measure_item_info bbdmii ON bbdmi.item_id = bbdmii.id +LEFT JOIN eqpt_equipment_model_technical_target eemtte ON bbdmii.device_model_id = eemtte.model_id + LEFT JOIN sys_user _user ON bbdmi.create_user_id = _user.id +-- LEFT JOIN eqpt_equipment_model_technical_target eemtte ON bbdmi.item_id = bbdmii.id +SET + bboi.customer_name = '新的委托单位名称', -- 假设这是新的委托单位名称 + eemtte.based_documents = '新的依据技术文件', -- 假设这是新的依据技术文件 + bbdmi.trace_date = '新的检定日期', -- 假设这是新的检定日期（格式需与数据库一致，如'YYYY-MM-DD'） + bbdmi.temperature = '新的环境温度', + bbdmi.humidity = '新的环境湿度', + bbdmi.measure_address = '新的检定地点', + bbdmi.measure_valid_date = '新的检定有效期', -- 日期格式同上 + _user.NAME = '新的检定员姓名', -- 这里通常不能直接更新用户表，需要找到对应的检定员ID进行更新 + bbdmi.conclusion = '新的结论', + bbdmi.restriction_instruction = '新的限用说明' +WHERE bbdmi.id = '1708657907573821442'; + +-- 注意：对于_sys_user表中的检定员姓名，一般不会直接通过这种方式更新，而是应该通过检定员ID关联并更新。 +------------------------------------------------------------------------------------------------------------------------------ +核查数据管理 + +SELECT bei.id as "序号", bei.equipment_name as "设备名称",bei.model as "型号规格",bei.manufacture_no as "出厂编号",bei.meter_standard_name as "核查件",bei.meter_standard_id as "核查件编号", +besci.create_user_name as "核查员",besci.check_date as "核查日期",bei.dept_name as "部门" + FROM biz_equipment_info bei +LEFT JOIN biz_equipment_standard_check_info besci ON besci.equipment_id = bei.id + +核查数据管理-核查基本信息 + +SELECT bei.equipment_name as "设备名称",bei.model as "型号规格",bei.manufacture_no as "出厂编号",bei.manufacturer as "生产厂家",besci.temperature as "温度",besci.humidity as "湿度",besci.check_date as "核查日期", +besci.check_address as "核查地点",besci.create_user_name as "核查员",besci.conclusion as "结论" +FROM biz_equipment_info bei +LEFT JOIN biz_equipment_standard_check_info besci ON besci.equipment_id = bei.id +LEFT JOIN biz_equipment_standard_check_info_equipment bescie ON bei.id = bescie.check_equipment_id +WHERE bei.id = '1692796512381222914' +核查结果信息 + +TODO + +SELECT bescidc.id as "序号", bescidc.check_type as "核查类型",bescidc.params as "参数",bescidc.unit as "单位",bescidc.frequency as "频率",bescidc.check_point as "核查点",besci.check_date as "核查日期",bescidc.urel as "Urel",bescidc.tfer as "Tfer",bescidc.dccp as "Dccp",bescidc.flit as "Flit" +FROM biz_equipment_standard_check_item_data_calibrator bescidc +LEFT JOIN biz_equipment_standard_check_info besci ON besci.item_category_id = bescidc.item_category_id +WHERE bescidc.equipment_id = '1692796512381222914' + + + +技术文件管理 +SELECT brtf.file_distribute_no as "文件号",brtf.file_name as "文件名",brtf.version_no as "版本号",brtf.active_date as "颁布时间",brtf.remark as "备注" +FROM biz_resource_technique_file brtf +