部门任务 废弃
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'
