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" index 472d062..7d3eeb6 100644 --- "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" @@ -106,13 +106,23 @@ 创建任务单 -- 新建 --- 假设已有sys_dept表中对应的新客户ID的记录 +SELECT brci.id,brci.customer_no,brci.customer_name,dept.FULL_NAME FROM biz_resource_customer_info brci LEFT JOIN sys_dept dept ON dept.ID = brci.dept_id +-- 查询委托方名录人员表 +SELECT customer_id FROM biz_resource_customer_staff --- 插入biz_business_order_info表的新记录 -INSERT INTO biz_business_order_info ( + + + + + + + + +-- 插入 biz_business_order_info 任务单表 的新记录 +INSERT INTO biz_business_order_info (id, order_no, customer_name, require_over_time, deliverer, deliverer_tel, undertaker_name, undertake_time, is_urgent, receive_illustrate, customer_id -) VALUES ( +) VALUES ('id', '新任务单编号', '新委托方名称', '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表示不是 @@ -120,10 +130,22 @@ 新客户ID -- 这个ID应与sys_dept表中已存在的ID相对应 ); +新增任务单样品表 +biz_business_order_sample_relation +INSERT INTO biz_business_order_sample_relation +(id,order_id,sample_id,sample_name,sample_model,measure_complete_time) +VALUES +(); --- 插入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) @@ -133,8 +155,22 @@ SELECT eei.help_instruction,eei.manufacture_no,eei.manufacturer FROM eqpt_equipment_info eei WHERE id = '1715284942530105345' + +UPDATE biz_business_order_info +SET order_no = :order_no, + customer_name = :customer_name, + require_over_time = :require_over_time, + deliverer = :deliverer, + deliverer_tel = :deliverer_tel, + undertaker_name = :undertaker_name, + undertake_time = :undertake_time, + is_urgent = :is_urgent, + receive_illustrate = :receive_illustrate, + customer_id = :customer_id +WHERE id = :id; + @@ -164,7 +200,7 @@ 创建任务单--删除 -DELETE FROM biz_business_order_info WHERE order_no = '指定的任务单编号'; +DELETE FROM biz_business_order_info WHERE id = ''; @@ -193,18 +229,21 @@ 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 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 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 = "1716630107844874241" +-- +多功能校准源 +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.equipment_id = '1692808528303013889' +直流稳压电源检定装置 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 = "直流稳压电源检定装置") +,datum_point,resistance_value,resistance_value_unit,remark FROM biz_equipment_standard_check_item_data_dc_power besciddp WHERE equipment_id = '1693435753272557570' +-- SELECT bescic.id FROM biz_equipment_standard_check_item_category bescic WHERE bescic.belong_standard_equipment_name = "直流稳压电源检定装置" ------------------------------------------------------------------------ @@ -226,12 +265,10 @@ 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" @@ -354,7 +391,7 @@ 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 @@ -367,3 +404,70 @@ 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 + +--------------------------------------------------------------------------------------------- +检定程序管理 + +检定程序管理首页 +SELECT id,program_name as "检定程序名称",create_name as "创建人",create_time as "创建时间",remark as "说明",comm_id as "通信设置id" FROM zd_verification_program_info +编辑----->拿着id去查详情表 +被检设备和标准设备和通讯设置合并查询 +SELECT zvpd.id,zvpd.verification_id as "检定程序id",zvpd.img_path as "连接图片地址",zvpd.tested_device_id as "被检设备id",zvpd.standard_device_id as "标准设备id",eei.equipment_name as "设备名称",eei.model as "型号规格",eei.help_instruction as "辅助字段",eei.manufacture_no as "出厂编号",eei.manufacturer as "生产厂家" +FROM zd_verification_program_details zvpd +LEFT JOIN eqpt_equipment_info eei ON eei.id = zvpd.tested_device_id +WHERE zvpd.verification_id = '1' +-- 标准设备查询 +UNION ALL +SELECT zvpd.id,zvpd.verification_id as "检定程序id",zvpd.img_path as "连接图片地址",zvpd.tested_device_id as "被检设备id",zvpd.standard_device_id as "标准设备id",bei.equipment_name as "设备名称",bei.model as "型号规格", NULL as "辅助字段",bei.manufacture_no as "出厂编号",bei.manufacturer as "生产厂家" +FROM zd_verification_program_details zvpd +LEFT JOIN biz_equipment_info bei ON bei.id = zvpd.standard_device_id +WHERE zvpd.verification_id = '1' + +检定设置 根据设备id查询 + + + + + +选择被检设备 +SELECT eei.id as "设备id",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 bei.id as "序号",bei.equipment_name as "设备名称",bei.model as "型号规格",bei.manufacture_no as "出厂编号",bei.manufacturer as "生产厂家" FROM biz_equipment_info + + +新建检定程序-检定设置 +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 bei.id as "序号",bei.equipment_name as "设备名称",bei.model as "型号规格",bei.manufacture_no as "出厂编号",bei.manufacturer as "生产厂家" FROM biz_equipment_info bei + +选择被检设备和标准设备的并集 +SELECT + bei.id as "序号", + bei.equipment_name as "设备名称", + bei.model as "型号规格", + bei.manufacture_no as "出厂编号", + bei.manufacturer as "生产厂家" + +FROM + biz_equipment_info bei + +UNION ALL + +SELECT + eei.id as "序号", + eei.equipment_name as "设备名称", + eei.model as "型号规格", + eei.manufacture_no as "出厂编号", + eei.manufacturer as "生产厂家" +FROM + eqpt_equipment_info eei +LEFT JOIN + sys_dept ON sys_dept.ID = eei.dept_id; +