web_server/src/main/java/thyyxxk/webserver/dao/his/medicine/YpTracCodgDao.java

package thyyxxk.webserver.dao.his.medicine;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import thyyxxk.webserver.entity.datamodify.YpInDetl;
import thyyxxk.webserver.entity.datamodify.YpOutDetl;
import thyyxxk.webserver.entity.medicalinsurance.manage.inventory.InvinfoUpload;
import thyyxxk.webserver.entity.medicalinsurance.manage.inventory.PurcinfoBought;
import thyyxxk.webserver.entity.medicalinsurance.manage.inventory.SelinfoSold;
import thyyxxk.webserver.entity.medicine.YpCodgUpload;
import thyyxxk.webserver.entity.medicine.YpInvinfoModify;
import thyyxxk.webserver.entity.medicine.YpPurcinfoBought;
import thyyxxk.webserver.entity.medicine.YpSelinfoSold;

import java.util.List;
import java.util.Map;

@Mapper
public interface YpTracCodgDao {

    @Select(" select group_no as value, group_name as label from yp_zd_group_name where yf_flag = '1' order by group_no ")
    List<Map<String, String>> selectGroupNoInfoYf();

    @Select(" select group_no as value, group_name as label from yp_zd_group_name where yf_flag = '0' order by group_no ")
    List<Map<String, String>> selectGroupNoInfoYk();

    @Select(" select ltrim(code) as value, ltrim(name) as label, supply_code as code from yp_zd_supply where isnull(del_flag, '0') = '0' ")
    List<Map<String, String>> selectSupplyType();

    @Select("<script> " +
            " select rtrim(zd.national_code) as medListCodg, rtrim(zd.national_code) as fixmedinsHilistId, rtrim(zd.name) as fixmedinsHilistName, " +
            " rtrim(de.in_docu_no)  + '_' + cast(de.in_seri as varchar(4)) as fixmedinsBchno, " +
            " rtrim(gys.name) as splerName, rtrim(de.manu_no) as manuLotnum, rtrim(cj.name) as prodentpName, rtrim(de.license_no) as aprvno, " +
            " convert(varchar(100), de.producing_date, 23) as manuDate, convert(varchar(100), de.eff_date, 23) as expyEnd, de.buy_price as finlTrnsPric, " +
            " de.buy_amt as purcRetnCnt, de.fp_no as purcInvoNo, zd.rx_flag as rxFlag, rtrim(zd.name) as chargeName, " +
            " convert(varchar(100), de.in_date, 20) as purcRetnStoinTime, rtrim(mi.name) as purcRetnOpterName, " +
            " rtrim(de.in_docu_no) as inDocuNo, de.in_seri as inSeri, de.serial, de.group_no " +
            " from yp_in_detl de  " +
            " left join yp_zd_dict zd on de.charge_code = zd.code and de.serial = zd.serial " +
            " left join yp_zd_manufactory cj on de.manu_code = cj.code " +
            " left join yp_zd_supply gys on de.supply_code = gys.code " +
            " left join a_employee_mi mi on de.purchaser = mi.code " +
            " where not exists (select 1 from yp_purcinfo_bought bo where bo.upload_flag = '1' " +
            " and de.in_docu_no = bo.in_docu_no and de.in_seri = bo.in_seri) " +
            " and de.in_docu_no not like 'T%' " +
            " and de.in_date &gt;= #{dto.startTime} " +
            " and de.in_date &lt;= #{dto.endTime} " +
            " and de.group_no = #{dto.groupNo} " +
            " <if test=\"dto.docuNo != null and dto.docuNo != ''\"> " +
            " and de.in_docu_no = #{dto.inDocuNo} " +
            " </if> " +
            " <if test=\"dto.chargeCode != null and dto.chargeCode != ''\"> " +
            " and de.charge_code = #{dto.chargeCode} " +
            " </if> " +
            " order by de.in_docu_no, de.in_seri " +
            "</script>")
    List<YpPurcinfoBought> selectYpPurchaseGoodsDetail(@Param("dto") YpCodgUpload dto);

    @Select("<script> " +
            " select rtrim(zd.national_code) as medListCodg, rtrim(zd.national_code) as fixmedinsHilistId, rtrim(zd.name) as fixmedinsHilistName, " +
            " rtrim(de.in_docu_no)  + '_' + cast(de.in_seri as varchar(4)) as fixmedinsBchno, " +
            " rtrim(gys.name) as splerName, rtrim(de.manu_no) as manuLotnum, rtrim(cj.name) as prodentpName, rtrim(de.license_no) as aprvno, " +
            " convert(varchar(100), de.producing_date, 23) as manuDate, convert(varchar(100), de.eff_date, 23) as expyEnd, de.buy_price as finlTrnsPric, " +
            " -de.buy_amt as purcRetnCnt, de.fp_no as purcInvoNo, zd.rx_flag as rxFlag, rtrim(zd.name) as chargeName, " +
            " convert(varchar(100), de.in_date, 20) as purcRetnStoinTime, rtrim(mi.name) as purcRetnOpterName, " +
            " rtrim(de.in_docu_no) as inDocuNo, de.in_seri as inSeri, de.serial, de.group_no " +
            " from yp_in_detl de  " +
            " left join yp_zd_dict zd on de.charge_code = zd.code and de.serial = zd.serial " +
            " left join yp_zd_manufactory cj on de.manu_code = cj.code " +
            " left join yp_zd_supply gys on de.supply_code = gys.code " +
            " left join a_employee_mi mi on de.purchaser = mi.code " +
            " where not exists (select 1 from yp_purcinfo_bought bo where bo.upload_flag = '1' " +
            " and de.in_docu_no = bo.in_docu_no and de.in_seri = bo.in_seri) " +
            " and de.in_docu_no like 'T%' " +
            " and de.in_date &gt;= #{dto.startTime} " +
            " and de.in_date &lt;= #{dto.endTime} " +
            " and de.group_no = #{dto.groupNo}  " +
            " <if test=\"dto.docuNo != null and dto.docuNo != ''\"> " +
            " and de.in_docu_no = #{dto.docuNo} " +
            " </if> " +
            " <if test=\"dto.chargeCode != null and dto.chargeCode != ''\"> " +
            " and de.charge_code = #{dto.chargeCode} " +
            " </if> " +
            " order by de.in_docu_no, de.in_seri " +
            "</script>")
    List<YpPurcinfoBought> selectYpPurchaseGoodsTkDetail(@Param("dto") YpCodgUpload dto);

    @Select(" select count(*) c from yp_in_detl where in_docu_no = #{inDocuNo} and in_seri = #{inSeri} and charge_code = #{chargeCode} and serial = #{serial} ")
    int selectYpInvoiceData(@Param("inDocuNo") String inDocuNo, @Param("inSeri") String inSeri, @Param("chargeCode") String chargeCode, @Param("serial") String serial);

    @Update(" update yp_in_detl set fp_no = #{fpNo} where in_docu_no = #{inDocuNo} and in_seri = #{inSeri} and charge_code = #{chargeCode} and serial = #{serial} ")
    int updateYpInvoice(@Param("inDocuNo") String inDocuNo, @Param("inSeri") String inSeri,
                        @Param("chargeCode") String chargeCode, @Param("serial") String serial, @Param("fpNo") String fpNo);

    @Select(" select top 10 * from yp_in_detl ")
    List<SelinfoSold> selectYpSaleGoodsData(@Param("inDocuNo") String inDocuNo, @Param("hisId") String hisId, @Param("hisName") String hisName);

    @Select("<script> " +
            "select case when yp.national_code = '' then 'NMLC999' else rtrim(isnull(yp.national_code, 'NMLC999')) end as medListCodg, " +
            " case when yp.national_code = '' then 'NMLC999' else rtrim(isnull(yp.national_code, 'NMLC999')) end as fixmedinsHilistId, " +
            " rtrim(yp.name) as fixmedinsHilistName, " +
            " rtrim(fy.patient_id)  + '_' + cast(fy.times as varchar(4)) + '_' + cast(fy.receipt_no as varchar(4)) " +
            " + cast(fy.order_no as varchar(4)) + cast(fy.item_no as varchar(4)) as fixmedinsBchno, " +
            " fy.doctor_name as prscDrName, fy.confirm_name as pharName, mi.yb_code as pharPracCertNo, mi.yb_code as pharNo, " +
            " isnull(si.setl_id, (rtrim(fy.patient_id)  + '_' + cast(fy.times as varchar(4)))) as setlId, isnull(si.mdtrt_id, (rtrim(fy.patient_id)  + '_' + cast(fy.times as varchar(4)))) as mdtrtSn, " +
            " case when si.setl_id is null then '2' else '1' end as mdtrtSetlType, mi1.yb_code as prscDrNo, rtrim(fy.patient_id) as rxno,  " +
            " '' as manuLotnum, '' as manuDate, '' as expyEnd, isnull(yp.rx_flag, '1') as rxFlag, '' as trdnFlag, rtrim(fy.charge_item_code) as chargeCode, " +
            " yp.retprice as finlTrnsPric, fy.real_no as rtalDocno, '' as drugTracCodg, isnull(yp.bar_code, 'NDPB999') as drugProdBarc, fy.manu_no as manuNo, " +
            " fy.quantity as selRetnCnt, convert(varchar(100), fy.charge_date, 20) as selRetnTime, fy.confirm_name as selRetnOpterName, " +
            " rtrim(yp.code) as chargeCode, rtrim(yp.name) as chargeName, fy.serial, rtrim(fy.patient_id) as patNo, fy.times, fy.group_no as groupNo, " +
            " fy.receipt_no as receiptNo, fy.order_no as orderNo, fy.item_no as itemNo, fy.serial_no as serialNo, fy.real_no as realNo " +
            "from yp_mz_fytj as fy " +
            "left join yp_zd_dict as yp on fy.charge_item_code = yp.code and fy.serial = yp.serial " +
            "left join a_employee_mi as mi on fy.confirm_id = mi.code " +
            "left join a_employee_mi as mi1 on fy.doctor_id = mi1.code " +
            "left join t_si_setlinfo as si on si.revoked = '0' and fy.patient_id = si.pat_no and fy.times = si.times " +
            "inner join (select distinct patient_id, times, receipt_no, order_no, item_no from mz_drug_trac_codg where flag = '0') as cd " +
            " on fy.patient_id = cd.patient_id and fy.times = cd.times " +
            "and fy.receipt_no = cd.receipt_no and fy.order_no = cd.order_no and fy.item_no = cd.item_no " +
            "where not exists ( select 1 from yp_selinfo_sold so " +
            " where so.is_ty = '0' and so.upload_flag = '1' and fy.patient_id = so.pat_no and fy.times = so.times" +
            " and fy.receipt_no = so.receipt_no and fy.order_no = so.order_no and fy.item_no = so.item_no )" +
            " and fy.confirm_flag in (1, 2) " +
            " and fy.charge_date &gt;= #{dto.startTime} " +
            " and fy.charge_date &lt;= #{dto.endTime} " +
            " and fy.group_no = #{dto.groupNo}  " +
            " <if test=\"dto.patNo != null and dto.patNo != ''\"> " +
            " and fy.patient_id = #{dto.patNo} " +
            " </if> " +
            " <if test=\"dto.chargeCode != null and dto.chargeCode != ''\"> " +
            " and fy.charge_item_code = #{dto.chargeCode} " +
            " </if> " +
            " <if test=\"dto.setlId != null and dto.setlId != ''\"> " +
            " and si.setl_id = #{dto.setlId} " +
            " </if> " +
            " order by fy.charge_date " +
            "</script>")
    List<YpSelinfoSold> selectYpSelinfoSoldDetailByXyf(@Param("dto") YpCodgUpload dto);

    @Select("<script> " +
            "select so.*  " +
            "from yp_selinfo_sold as so, yp_mz_fytj as fy " +
            "inner join (select distinct patient_id, times, receipt_no, order_no, item_no from mz_drug_trac_codg_ty where flag = '0') as cd " +
            " on fy.patient_id = cd.patient_id and fy.times = cd.times " +
            "and fy.receipt_no = -cd.receipt_no and fy.order_no = cd.order_no and fy.item_no = cd.item_no " +
            " where so.pat_no = fy.patient_id " +
            " and so.times = fy.times " +
            " and fy.receipt_no = -so.receipt_no " +
            " and fy.order_no = so.order_no " +
            " and fy.item_no = so.item_no " +
            " and fy.confirm_flag = 3 " +
            " and fy.confirm_time &gt;= #{dto.startTime} " +
            " and fy.confirm_time &lt;= #{dto.endTime} " +
            " and fy.group_no = #{dto.groupNo}  " +
            " <if test=\"dto.patNo != null and dto.patNo != ''\"> " +
            " and so.pat_no = #{dto.patNo} " +
            " </if> " +
            " <if test=\"dto.chargeCode != null and dto.chargeCode != ''\"> " +
            " and so.charge_code = #{dto.chargeCode} " +
            " </if> " +
            " <if test=\"dto.setlId != null and dto.setlId != ''\"> " +
            " and so.setl_id = #{dto.setlId} " +
            " </if> " +
            " order by fy.confirm_time " +
            "</script>")
    List<YpSelinfoSold> selectYpSelinfoSoldThDetailByXyf(@Param("dto") YpCodgUpload dto);

    @Select("<script> " +
            "select case when yp.national_code = '' then 'NMLC999' else rtrim(isnull(yp.national_code, 'NMLC999')) end as medListCodg, " +
            " case when yp.national_code = '' then 'NMLC999' else rtrim(isnull(yp.national_code, 'NMLC999')) end as fixmedinsHilistId, " +
            " rtrim(yp.name) as fixmedinsHilistName, " +
            " rtrim(fy.patient_id)  + '_' + cast(fy.times as varchar(4)) + '_' + cast(fy.receipt_no as varchar(4)) " +
            " + cast(fy.order_no as varchar(4)) + cast(fy.item_no as varchar(4)) as fixmedinsBchno, rtrim(fy.patient_id) as rxno, " +
            " fy.doctor_name as prscDrName, fy.confirm_name as pharName, mi.yb_code as pharPracCertNo, mi.yb_code as pharNo, " +
            " isnull(si.setl_id, rtrim(fy.patient_id)) as setlId, isnull(si.mdtrt_id, (rtrim(fy.patient_id)  + '_' + cast(fy.times as varchar(4)))) as mdtrtSn, " +
            " '' as manuLotnum, '' as manuDate, '' as expyEnd, isnull(yp.rx_flag, '1') as rxFlag, '' as trdnFlag, rtrim(fy.charge_item_code) as chargeCode, " +
            " yp.retprice as finlTrnsPric, fy.real_no as rtalDocno, '' as drugTracCodg, isnull(yp.bar_code, 'NDPB999') as drugProdBarc, fy.manu_no as manuNo, " +
            " fy.quantity as selRetnCnt, convert(varchar(100), fy.charge_date, 20) as selRetnTime, fy.confirm_name as selRetnOpterName, " +
            " rtrim(yp.code) as chargeCode, rtrim(yp.name) as chargeName, fy.serial, rtrim(fy.patient_id) as patNo, fy.times, fy.group_no as groupNo, " +
            " fy.receipt_no as receiptNo, fy.order_no as orderNo, fy.item_no as itemNo, fy.serial_no as serialNo, fy.real_no as realNo " +
            "from yp_mz_fytj as fy " +
            "left join yp_zd_dict as yp on fy.charge_item_code = yp.code and fy.serial = yp.serial " +
            "left join a_employee_mi as mi on fy.confirm_id = mi.code " +
            "left join t_si_setlinfo as si on si.revoked = '0' and fy.patient_id = si.pat_no and fy.times = si.times " +
            "inner join (select distinct patient_id, times, receipt_no, order_no, item_no from mz_drug_trac_codg_ty where flag = '0') as cd " +
            " on fy.patient_id = cd.patient_id and fy.times = cd.times " +
            "and fy.receipt_no = -cd.receipt_no and fy.order_no = cd.order_no and fy.item_no = cd.item_no " +
            " where not exists ( select 1 from yp_selinfo_sold so " +
            " where so.is_ty = '1' and so.upload_flag = '1' and fy.patient_id = so.pat_no and fy.times = so.times" +
            " and fy.receipt_no = so.receipt_no and fy.order_no = so.order_no and fy.item_no = so.item_no )" +
            " and fy.confirm_flag = 3 " +
            " and fy.confirm_time &gt;= #{dto.startTime} " +
            " and fy.confirm_time &lt;= #{dto.endTime} " +
            " and fy.group_no = #{dto.groupNo}  " +
            " <if test=\"dto.patNo != null and dto.patNo != ''\"> " +
            " and fy.patient_id = #{dto.patNo} " +
            " </if> " +
            " <if test=\"dto.chargeCode != null and dto.chargeCode != ''\"> " +
            " and fy.charge_item_code = #{dto.chargeCode} " +
            " </if> " +
            " order by fy.confirm_time " +
            "</script>")
    List<YpSelinfoSold> selectYpSelinfoSoldThDetailByXyfOld(@Param("dto") YpCodgUpload dto);

    @Select(" select drug_trac_codg as ypCodg, charge_item_code as chargeCode, convert(varchar(100), confirm_time, 20) as confirmTime from ${tableName} " +
            " where flag = '0' and patient_id = #{patNo} and times = #{times} and charge_item_code = #{chargeCode} and serial = #{serial} ")
    List<Map<String, String>> selectCodgInfoById(@Param("tableName") String tableName, @Param("patNo") String patNo, @Param("times") Integer times,
                                                 @Param("chargeCode") String chargeCode, @Param("serial") String serial);

    @Select(" select top 1 * from yp_out_detl where confirm_flag = '1' and out_type = '0' and charge_code = #{chargeCode} and group_no_out = #{groupNo} order by out_date desc ")
    YpOutDetl selectCodgInfoManu(@Param("chargeCode") String chargeCode, @Param("groupNo") String groupNo);

    @Select(" select top 1 * from yp_in_detl where confirm_flag = '1' and in_type in ('0', '1') and charge_code = #{chargeCode} " +
            "and serial = #{serial} and manu_no = #{manuNo} and in_date <= #{confirmTime} order by in_date desc ")
    YpInDetl selectCodgInfoProdTime(@Param("chargeCode") String chargeCode, @Param("serial") String serial, @Param("manuNo") String manuNo, @Param("confirmTime") String confirmTime);

    @Select("<script> " +
            " select case when zd.national_code = '' then 'NMLC999' else rtrim(isnull(zd.national_code, 'NMLC999')) end as medListCodg, " +
            " case when zd.national_code = '' then 'NMLC999' else rtrim(isnull(zd.national_code, 'NMLC999')) end as fixmedinsHilistId, " +
            " rtrim(zd.name) as fixmedinsHilistName, " +
            " rtrim(de.in_docu_no)  + '_' + cast(de.in_seri as varchar(4)) as fixmedinsBchno, isnull(de.in_type, '0') as type, " +
            " de.buy_price as pric, de.buy_amt as cnt, isnull(zd.rx_flag, '1') as rxFlag, rtrim(zd.name) as chargeName, " +
            " convert(varchar(100), de.in_date, 20) as invChgTime, rtrim(mi.name) as invChgOpterName, rtrim(zd.code) as chargeCode, " +
            " '0' as trdnFlag, rtrim(de.in_docu_no) as docuNo, de.in_seri as seri, de.serial, de.group_no as groupNo," +
            " rtrim(de.manu_no) as manuNo, convert(varchar(100), de.producing_date, 23) as producingDate, " +
            " convert(varchar(100), de.eff_date, 23) as effDate " +
            " from yp_in_detl as de  " +
            " left join yp_zd_dict as zd on de.charge_code = zd.code and de.serial = zd.serial " +
            " left join a_employee_mi as mi on de.confirm_id = mi.code " +
            " where not exists (" +
            " select 1 from yp_invinfo_modify mo where mo.upload_flag = '1' " +
            " and de.in_docu_no = mo.docu_no and de.in_seri = mo.seri )" +
            " and de.confirm_flag = '1' and de.in_type in ('0', '1') " +
            " and de.group_no = #{dto.groupNo} " +
            " <if test=\"dto.startTime != null and dto.startTime != ''\"> " +
            " and de.in_date &gt;= #{dto.startTime} " +
            " </if> " +
            " <if test=\"dto.endTime != null and dto.endTime != ''\"> " +
            " and de.in_date &lt;= #{dto.endTime} " +
            " </if> " +
            " <if test=\"dto.docuNo != null and dto.docuNo != ''\"> " +
            " and de.in_docu_no = #{dto.docuNo} " +
            " </if> " +
            " <if test=\"dto.chargeCode != null and dto.chargeCode != ''\"> " +
            " and de.charge_code = #{dto.chargeCode} " +
            " </if> " +
            " order by de.in_docu_no, de.in_seri " +
            "</script>")
    List<YpInvinfoModify> selectYpInvinfoModifyDetailRk(@Param("dto") YpCodgUpload dto);

    @Select("<script> " +
            " select case when zd.national_code = '' then 'NMLC999' else rtrim(isnull(zd.national_code, 'NMLC999')) end as medListCodg, " +
            " case when zd.national_code = '' then 'NMLC999' else rtrim(isnull(zd.national_code, 'NMLC999')) end as fixmedinsHilistId, " +
            " rtrim(zd.name) as fixmedinsHilistName, " +
            " rtrim(de.draw_no)  + '_' + cast(de.out_seri as varchar(4)) as fixmedinsBchno, isnull(de.out_type, '0') as type, " +
            " de.buy_price as pric, de.out_amt as cnt, isnull(zd.rx_flag, '1') as rxFlag, rtrim(zd.name) as chargeName, " +
            " convert(varchar(100), de.out_date, 20) as invChgTime, rtrim(mi.name) as invChgOpterName, rtrim(zd.code) as chargeCode, " +
            " '0' as trdnFlag, rtrim(de.draw_no) as docuNo, de.in_seri as seri, de.serial, de.group_no as groupNo " +
            " from yp_out_detl as de  " +
            " left join yp_zd_dict as zd on de.charge_code = zd.code and de.serial = zd.serial " +
            " left join a_employee_mi as mi on de.confirm_id = mi.code " +
            " where de.confirm_flag = '1' " +
            " and de.out_date &gt;= #{dto.startTime} " +
            " and de.out_date &lt;= #{dto.endTime} " +
            " and de.group_no = #{dto.groupNo} " +
            " <if test=\"dto.docuNo != null and dto.docuNo != ''\"> " +
            " and de.draw_no = #{dto.docuNo} " +
            " </if> " +
            " <if test=\"dto.chargeCode != null and dto.chargeCode != ''\"> " +
            " and de.charge_code = #{dto.chargeCode} " +
            " </if> " +
            " order by de.draw_no, de.out_seri " +
            "</script>")
    List<YpInvinfoModify> selectYpInvinfoModifyDetailCk(@Param("dto") YpCodgUpload dto);

}