| | |
| | | </select> |
| | | |
| | | <select id="reagentList" parameterType="java.util.Map" resultMap="SysReagent"> |
| | | select sr.*, ss.meta_value as product_home_name, bm.meta_value as control_products_name,supplier.name as supplierName,f.id favor,f.create_time favortime |
| | | from sys_reagent as sr |
| | | left join base_meta as ss on sr.product_home = ss.id |
| | | left join base_meta bm on bm.id = sr.control_products |
| | | left join sys_supplier supplier on supplier.id = sr.supplier_id |
| | | LEFT JOIN favor f on f.reagent_id = sr.id AND f.user_id = #{user} |
| | | SELECT |
| | | * |
| | | FROM |
| | | ( |
| | | SELECT |
| | | sr.*, ss.meta_value AS product_home_name, |
| | | bm.meta_value AS control_products_name, |
| | | supplier. NAME AS supplierName, |
| | | f.id favor, |
| | | f.create_time favortime |
| | | FROM |
| | | sys_reagent AS sr |
| | | LEFT JOIN base_meta AS ss ON sr.product_home = ss.id |
| | | LEFT JOIN base_meta bm ON bm.id = sr.control_products |
| | | LEFT JOIN sys_supplier supplier ON supplier.id = sr.supplier_id |
| | | LEFT JOIN favor f ON f.reagent_id = sr.id |
| | | AND f.user_id= #{user} |
| | | WHERE f.id IS NOT NULL |
| | | and sr.valid_flag = 1 |
| | | <if test="cas != null and cas != ''"> |
| | | and sr.cas like concat("%", #{cas} ,"%") |
| | | </if> |
| | | <if test="supplierId != null and supplierId != ''"> |
| | | and sr.supplier_id = #{supplierId} |
| | | </if> |
| | | <if test="name != null and name != ''"> |
| | | and sr.name like concat("%", #{name} ,"%") |
| | | </if> |
| | | <if test="productSn != null and productSn != ''"> |
| | | and sr.product_sn like concat("%", #{productSn} ,"%") |
| | | </if> |
| | | UNION ALL |
| | | SELECT |
| | | sr.*, ss.meta_value AS product_home_name, |
| | | bm.meta_value AS control_products_name, |
| | | supplier.NAME AS supplierName, |
| | | f.id favor, |
| | | f.create_time favortime |
| | | FROM |
| | | sys_reagent AS sr |
| | | LEFT JOIN base_meta AS ss ON sr.product_home = ss.id |
| | | LEFT JOIN base_meta bm ON bm.id = sr.control_products |
| | | LEFT JOIN sys_supplier supplier ON supplier.id = sr.supplier_id |
| | | LEFT JOIN favor f ON f.reagent_id = sr.id |
| | | AND f.user_id = #{user} |
| | | AND f.id IS NULL |
| | | where sr.valid_flag = 1 |
| | | <if test="cas != null and cas != ''"> |
| | | and sr.cas like concat("%", #{cas} ,"%") |
| | |
| | | <if test="productSn != null and productSn != ''"> |
| | | and sr.product_sn like concat("%", #{productSn} ,"%") |
| | | </if> |
| | | ORDER BY f.create_time desc,sr.create_time desc |
| | | <if test="MaxSize != null"> |
| | | limit #{MaxSize} |
| | | </if> |
| | | ) as reagent |
| | | ORDER BY reagent.favortime DESC |
| | | <if test="first != null and pageSize != null"> |
| | | limit #{first}, #{pageSize} |
| | | </if> |