SELECT
rpt.report_no,
case when prd.material_article_no like '%C3D%' or prd.material_article_no like '%C4X%' then '药物' else '非药' end prd_cls,
prd.material_article_no,
prd.cas_no,
rpt.u8_batch_no,
rpt_test.u8_report_test_plan_code,
t4.title lab_name,
t2.title test,
t3.standard,
rpt_test.remark,
from_unixtime(nullif(rpt_test.plan_time,0)) plan_time,
from_unixtime(nullif(rpt_test.receive_time,0)) receive_time,
from_unixtime(nullif(rpt_test.prepare_time,0)) prepare_time,
from_unixtime(nullif(rpt_test.weight_time,0)) weight_time,
from_unixtime(nullif(rpt_test.data_time,0)) data_time,
from_unixtime(nullif(rpt_test.check_time,0)) check_time,
rpt_test.price,
rpt_test.weight,
result.weight,
rpt_test.id

FROM `bv_report_test` rpt_test

LEFT JOIN `bv_test` t2
on rpt_test.test_id=t2.id
-- AND t2.`status` = 1

LEFT JOIN `bv_test_method` t3
on rpt_test.test_method_id=t3.id

LEFT JOIN `bv_company` t4
on t4.code=rpt_test.lab_id
and t4.type = 4

INNER JOIN `bv_report_index` rpt
ON rpt_test.report_no=rpt.report_no
AND rpt.status = 1

LEFT JOIN `bv_product` prd
on rpt.product_id=prd.id

LEFT JOIN bv_report_test_result result
on result.pid = rpt_test.id

where 1=1
AND rpt_test.`status` = 1
AND from_unixtime(nullif(rpt_test.prepare_time,0)) BETWEEN '2024-01-01' AND '2024-03-31'
AND t4.title like '%佳途实验室%'
AND t2.title like '%GC%'
-- AND rpt_test.plan_time>= UNIX_TIMESTAMP('2021-01-01')

ORDER BY rpt_test.prepare_time desc