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" deleted file mode 100644 index 7d3eeb6..0000000 --- "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" +++ /dev/null @@ -1,473 +0,0 @@ -部门任务 -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"; - - - - --------------------------------------------------------------------------------------------------- - -创建任务单 -- 新建 - - -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 (id, - order_no, customer_name, require_over_time, deliverer, deliverer_tel, - undertaker_name, undertake_time, is_urgent, receive_illustrate, customer_id -) 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表示不是 - '新接受说明', - 新客户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 -(); - - - - - - - - - - - --- 插入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 -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; - - - - - - - -创建任务单-更新 -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 id = ''; - - - ------------------------------------------------------------------------------------------- -标准设备管理 - - - -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 = "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 equipment_id = '1693435753272557570' - --- 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' -核查结果信息 - - - -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 - - ---------------------------------------------------------------------------------------------- -检定程序管理 - -检定程序管理首页 -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; - 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" deleted file mode 100644 index 7d3eeb6..0000000 --- "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" +++ /dev/null @@ -1,473 +0,0 @@ -部门任务 -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"; - - - - --------------------------------------------------------------------------------------------------- - -创建任务单 -- 新建 - - -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 (id, - order_no, customer_name, require_over_time, deliverer, deliverer_tel, - undertaker_name, undertake_time, is_urgent, receive_illustrate, customer_id -) 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表示不是 - '新接受说明', - 新客户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 -(); - - - - - - - - - - - --- 插入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 -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; - - - - - - - -创建任务单-更新 -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 id = ''; - - - ------------------------------------------------------------------------------------------- -标准设备管理 - - - -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 = "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 equipment_id = '1693435753272557570' - --- 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' -核查结果信息 - - - -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 - - ---------------------------------------------------------------------------------------------- -检定程序管理 - -检定程序管理首页 -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; - 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\232sql\347\274\226\345\206\231.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\232sql\347\274\226\345\206\231.sql" new file mode 100644 index 0000000..bbbf1bf --- /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\232sql\347\274\226\345\206\231.sql" @@ -0,0 +1,688 @@ +部门任务 废弃 +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 +业务系统部门任务 已改为 自动检定部门任务 2024年3月15日10:49:14 +SELECT blei.id AS id, eei.equipment_name AS sampleName, eei.model AS sampleModel,eei.help_instruction AS helpInstruction, + eei.manufacture_no AS manufactureNo, eei.manufacturer,bo.customer_name,sd.SIMPLE_NAME,bo.require_over_time, bo.is_urgent +FROM biz_business_lab_executive_info blei +JOIN eqpt_equipment_info eei ON eei.id = blei.sample_id +LEFT JOIN sys_dept sd ON blei.measure_dept_id = sd.ID +JOIN biz_business_order_info bo ON bo.id = blei.order_id +WHERE blei.measure_dept_id = 1601423748184150017 +AND blei.measure_status = 3 +ORDER BY blei.update_time DESC + + + + + + + + +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 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' + +业务系统我的任务 改为 自动检定 我的任务 2024年3月15日10:59:34 + SELECT blei.id AS id, eei.equipment_name AS sampleName, eei.model AS sampleModel, eei.help_instruction AS helpInstruction, + eei.manufacture_no AS manufactureNo, eei.manufacturer,bo.customer_name,sd.SIMPLE_NAME AS measureDeptName,bo.require_over_time , bo.is_urgent +FROM biz_business_lab_executive_info blei +JOIN eqpt_equipment_info eei ON eei.id = blei.sample_id +JOIN biz_business_order_info bo ON bo.id = blei.order_id +LEFT JOIN sys_dept sd ON blei.measure_dept_id = sd.ID +WHERE blei.measure_person_id = 1695282436182188034 +AND blei.measure_status = 3 +ORDER BY blei.update_time DESC + ----------------------------------------------------------------------------------------- + 创建任务单->任务单列表 + 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"; + + + + --------------------------------------------------------------------------------------------------- + +创建任务单 -- 新建 + + +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 (id, + order_no, customer_name, require_over_time, deliverer, deliverer_tel, + undertaker_name, undertake_time, is_urgent, receive_illustrate, customer_id +) 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表示不是 + '新接受说明', + 新客户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 +(); + + + + + + + + + + + +-- 插入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 +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; + + + + + + + +创建任务单-更新 +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 id = ''; + + + +------------------------------------------------------------------------------------------ +标准设备管理 + + + +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 = "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 equipment_id = '1693435753272557570' + +-- SELECT bescic.id FROM biz_equipment_standard_check_item_category bescic WHERE bescic.belong_standard_equipment_name = "直流稳压电源检定装置" + + +------------------------------------------------------------------------ +被检设备管理 +SELECT DISTINCT eei.id ,eei.equipment_name ,eei.model ,eei.help_instruction ,eei.manufacture_no ,eei.manufacturer ,bboi.customer_name ,sys_dept.FULL_NAME ,eei.meter_identify ,eei.check_cycle ,eei.certificate_valid from eqpt_equipment_info eei +LEFT JOIN sys_dept ON sys_dept.ID = eei.dept_id +LEFT JOIN biz_business_order_info bboi ON bboi.customer_id = eei.company_id +WHERE eei.id = 1763029795656036354 + + + +SELECT eei.equipment_name ,eei.model ,eei.help_instruction ,eei.manufacture_no ,eei.product_country ,eei.manufacturer ,eei.product_date ,eei.purchase_date ,eei.unit_price ,eei.usage_status ,eei.quality_condition ,sys_dept.FULL_NAME , + eei.category , eei.check_cycle ,eei.level , eei.remark from eqpt_equipment_info eei LEFT JOIN sys_dept ON sys_dept.ID = eei.dept_id WHERE eei.id = :id + + +SELECT eei.id as "序号",eei.equipment_name as "设备名称",eei.model as "型号规格",eei.help_instruction as "辅助字段",eei.manufacture_no as "出厂编号",eei.manufacturer as "生产厂家",bboi.customer_name 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 +LEFT JOIN biz_business_order_info bboi ON bboi.customer_id = eei.company_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 "质量状况",bboi.customer_name 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 + LEFT JOIN biz_business_order_info bboi ON bboi.customer_id = eei.company_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 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' + +2024年3月15日17:45:50 应该查biz_business_device_measure_data_calibrator这张表 检定数据管理 +SELECT bbdmdc.id as "序号",bbdmdc.params as "参数",bbdmdc.capacity as "量",bbdmdc.unit as "单位", +bbdmdc.frequency as "频率",bbdmdc.range_range as "量程",bbdmdc.standard_value as "标准值", +bbdmdc.indicating_value as "示值",bbdmdc.absolute_error as "绝对误差",bbdmdc.maximum_error as "最大允许误差",bbdmdc.resolution as "分辨力" FROM biz_business_device_measure_data_calibrator bbdmdc + + + +根据是否 合格判断查询 + + +SELECT bbdmdc.id as "序号",bbdmdc.params as "参数",bbdmdc.capacity as "量",bbdmdc.unit as "单位", +bbdmdc.frequency as "频率",bbdmdc.range_range as "量程",bbdmdc.standard_value as "标准值", +bbdmdc.indicating_value as "示值",bbdmdc.absolute_error as "绝对误差",bbdmdc.maximum_error as "最大允许误差",bbdmdc.resolution as "分辨力" FROM biz_business_device_measure_data_calibrator bbdmdc +WHERE data_id = (SELECT id + FROM biz_business_device_measure_info bbdmi + WHERE sample_id = '1734405681810878466') 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关联并更新。 +------------------------------------------------------------------------------------------------------------------------------ +核查数据管理 +------------------------------------------------------------------------------------------------ +2024年3月14日17:02:06 修改 +SELECT bei.equipment_name as "设备名称",bei.model as "型号规格",bei.manufacture_no as "出厂编号" +FROM biz_equipment_standard_check_info besci +JOIN biz_equipment_info bei ON bei.id = besci.equipment_id + +SELECT bei.id,bei.equipment_name as "核查件名称" FROM biz_equipment_standard_check_info besci +JOIN biz_equipment_standard_check_info_equipment bescie ON bescie.data_id = besci.id +JOIN biz_equipment_info bei ON bei.id = bescie.check_equipment_id + +猜测 可能是通过装置来区分 标准设备和核查件的关系 + + + +------------------------------------------------------------------------------------------------ + +SELECT bei.id as "序号", bei.equipment_name as "设备名称",bei.model as "型号规格",bei.manufacture_no as "出厂编号",bei.meter_standard_name as "核查件",bei.model as "核查件型号",bei.meter_standard_id as "核查件编号", +besci.create_user_name as "核查员",besci.check_date as "核查日期",bei.dept_name as "部门",blrd.biz_lab_code as "实验室" + FROM biz_equipment_info bei + LEFT JOIN biz_lab_rel_dept blrd ON blrd.dept_id = bei.dept_id +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' +核查结果信息 + + + +SELECT DISTINCT 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 + + +--------------------------------------------------------------------------------------------- +检定程序管理 + +检定程序管理首页 +SELECT id,program_name as "检定程序名称",create_name as "创建人",create_time as "创建时间",remark as "说明" FROM zd_verification_program_info +编辑----->拿着id去查详情表 +被检设备和标准设备和通讯设置合并查询 +SELECT id,program_name,create_name,create_time,remark,img_path,tested_device_id,standard_device_id,update_time FROM zd_verification_program_info WHERE id = '820662041046941696' + + + + + + +-- SELECT zvpi.id,zvpi.program_name as "检定程序名称",zvpi.create_name as "创建人",zvpi.create_time as "创建时间",zvpi.remark as "说明",zvpi.img_path as "连接图片地址",zvpi.tested_device_id as "被检设备id",zvpi.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_info zvpi +-- LEFT JOIN eqpt_equipment_info eei ON eei.id = zvpi.tested_device_id +-- WHERE zvpi.id = '1' +-- -- 标准设备查询 +-- UNION ALL +-- SELECT zvpi.id,zvpi.program_name as "检定程序名称",zvpi.create_name as "创建人",zvpi.create_time as "创建时间",zvpi.remark as "说明",zvpi.img_path as "连接图片地址",zvpi.tested_device_id as "被检设备id",zvpi.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_info zvpi +-- LEFT JOIN biz_equipment_info bei ON bei.id = zvpi.standard_device_id +-- WHERE zvpi.id = '1' + +检定设置 根据设备id查询 +SELECT id,device_id as "设备id",communication_type as "通讯类型",serial_port as "串口",visa as "visa",create_time as "创建时间",update_time as "更新时间" FROM zd_communication_setting WHERE device_id = '1' + +选择被检设备 +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 + + +根据设备id 查询指令库 +SELECT id,device_id,device_name,instruct_config,instruct_read,type,belong_vername,belong_checkname,create_time,update_time FROM zd_instruction_library WHERE device_id = '' + + + +新建检定程序-检定设置 +SELECT 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 = '1707329413374201857') +检定项多功能校准源数据 +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 = '1707329986036867074' + +新增检定程序 + +检定程序表 +INSERT INTO zd_verification_program_info (id,program_name,create_name,create_time, remark,img_path,tested_device_id,standard_device_id) +VALUES ('1697135299001356290', '检定程序名称','创建人', '创建时间', '备注','连接图片地址','被检设备id','标准设备id'); +-- 检定配置表 +-- INSERT INTO zd_verification_program_details (id,verification_id,img_path,tested_device_id,standard_device_id) +-- VALUES ('id','检定程序id','连接图片地址','被检设备id','标准设备id') +指令库 待定 确定哪个页面 +INSERT INTO zd_instruction_library (id,device_id,device_name,instruct_config,instruct_read,type,belong_vername,belong_checkname,create_time,update_time) +VALUES ('id','设备id','设备名称','配置指令','读数指令','0/1','检定项id','核查项id','创建时间','更新时间') +设备通信接口 +INSERT INTO zd_communication_setting (id,device_id,communication_type,serial_port,visa,create_time,update_time) +VALUES ('id','设备id','通讯类型','串口','visa','创建时间','更新时间') + +更新检定程序管理 +首页 +UPDATE zd_verification_program_info +SET program_name = :program_name, + create_name = :create_name, + create_time = :create_time, + remark = :remark, + img_path = :img_path, + tested_device_id = :tested_device_id, + standard_device_id = :standard_device_id +WHERE id = :id; + +-- 更新检定配置 +-- UPDATE zd_verification_program_details +-- SET img_path = :img_path, +-- tested_device_id = :tested_device_id, +-- standard_device_id = :standard_device_id +-- WHERE verification_id = :verification_id; +更新设备通信接口 +UPDATE zd_communication_setting +SET communication_type = :communication_type, + serial_port = :serial_port, + visa = :visa, + update_time = :update_time +WHERE device_id = :device_id; + +删除 +检定程序管理 +DELETE FROM zd_verification_program_info WHERE id = '' +检定程序详情 +DELETE FROM zd_verification_program_details WHERE verification_id = '' + +删除指令库 +DELETE FROM zd_instruction_library WHERE device_id = '' +更新指令库 +UPDATE zd_instruction_library +SET device_id=:device_id, +device_name = :device_name, +instruct_config = :instruct_config, +instruct_read = :instruct_read, +type = :type, +belong_vername = :belong_vername, +belong_checkname = :belong_checkname, +update_time = :update_time +WHERE id = '' + + + + + + + + + + + + + +核查程序管理------------------------------------------------------------------------------------------------- +查询所有核查程序 +SELECT id,check_name,create_name,create_time,remark,img_path,standard_device_id,verification_documents_id,update_time FROM zd_verification_program_manage + + + +选择标准设备 +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; + + +查询所有核查项分类 +SELECT id,category_no,category_name,equipment_type,belong_standard_equipment,belong_standard_equipment_name,create_time,update_time FROM biz_equipment_standard_check_item_category + + + + +核查项多功能校准源 biz_equipment_standard_check_item_data_calibrator + +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' + + + +新增核查程序 +INSERT INTO zd_verification_program_manage +(id,check_name,create_name,create_time,remark,img_path,standard_device_id,verification_documents_id,update_time) +VALUES +(:id,:check_name,:create_name,:create_time,:remark,:img_path,:standard_device_id, +:verification_documents_id,:update_time) + +更新核查程序 +UPDATE zd_verification_program_manage +SET check_name =:check_name, + create_name = :create_name, + remark = :remark, + img_path =:img_path, + standard_device_id =:standard_device_id, + verification_documents_id=:verification_documents_id, + update_time=:update_time +WHERE + id = :id; + +删除核查程序 +DELETE FROM zd_verification_program_manage WHERE id = + + + + + + + +自动检定 + + +结果保存 +基本信息表 +biz_business_device_measure_info + +INSERT INTO biz_business_device_measure_info +(id,data_no,order_id,sample_id,item_id, +trace_date,measure_valid_date,measure_address,temperature,humidity,data_source, +outcome,create_time,update_time) +VALUES +(:id,:data_no,:order_id,:sample_id,:item_id,:trace_date,:measure_valid_date,:measure_address,:temperature,:humidity,:data_source,:outcome,:create_time,:update_time) + +根据设备id查询哪张装置表，基本信息表的item_id对应具体装置表的id + +UPDATE biz_business_device_measure_item_data_calibrator +SET indicating_value = :indicating_value, +SET absolute_error = :absolute_error, +SET update_time = :update_time, +SET remark = :remark +WHERE id = :id; + + +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'