<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.unis.crk.common.mapper.BillnonewgjjMapper">

        <!-- 通用查询映射结果 -->
        <resultMap id="BaseResultMap" type="com.unis.crk.common.entity.Billnonewgjj">
                    <id column="NewId" property="NewId" />
                    <result column="UnitId" property="UnitId" />
                    <result column="BillNo" property="BillNo" />
                    <result column="tableName" property="tableName" />
                    <result column="ywdh" property="ywdh" />
                    <result column="buildDate" property="buildDate" />
        </resultMap>
    <insert id="addRkYwGjj">

INSERT INTO  ods_up_lsrkxxjk_final (
id, -- 1主键
rkywdh, -- 2入库业务单号
hwdm, -- 3货位代码
ywlx, -- 4业务类型
ywrq, -- 5业务日期
jhmxh, -- 6计划明细号
hth, -- 7合同号
cyr, -- 8承运人
lxdh, -- 9联系电话
sfzh, -- 10身份证号
xxdz, -- 11详细地址
ysgj, -- 12运输工具
cchlx, -- 13车船号类型
cch, -- 14车船号
gch, -- 15挂车号
djsj, -- 16登记时间
djmgryxm, -- 17登记门岗人员
lspzdm, -- 18粮食品种代码
lsxzdm, -- 19粮食性质代码
shnd, -- 20收获年度
cddm, -- 21产地代码
jjlx, -- 22检斤类型
mz, -- 23毛重
mzjby, -- 24毛重监磅员
mzjlsj, -- 25毛重计量时间
mzjly, -- 26毛重计量员
zcy, -- 27值仓员
pz, -- 28皮重
pzjby, -- 29皮重监磅员
pzjlsj, -- 30皮重计量时间
pzjly, -- 31皮重计量员
bzw, -- 32包装物
bzbdbz, -- 33标准包单包重
bzbjs, -- 34标准包件数
zjklxj, -- 35质检扣量
bzwkl, -- 36包装物扣量
qtkl, -- 37其他扣量
klyy, -- 38扣量原因
xckl, -- 39现场扣量
zkj, -- 40增扣价
zkhyy, -- 41增扣价原因 z
jz, -- 42净重
zxzydw, -- 43装卸作业单位
cmsj, -- 44出门时间
cmqrmgryxm, -- 45出门确认门岗
rkjsdh, -- 46入库结算单号
bz, -- 47备注
czbz, -- 48操作标志
zhgxsj, -- 49最后更新时间
unitId, -- 50单位id
bizNo, -- 51单据号
is_upload, -- 52是否上传
load_date, -- 53加载时间
qzsfzkl,
qzzzzkl,
qzbwslkl,
qzhhkl,
qzsmlkl,
qzzjmlkl,
qzgwcmkl,
qzhlmkl,
qzqtkl,
zlfyzkl
)
SELECT 	DISTINCT
					H.DataID as "id", -- 1主键
					'${rkywdh}' as "rkywdh", -- 2入库业务单号
					 st.hwdm as "hwdm", -- 3货位代码
					 '2' as "ywlx", -- 4：入库（默认）
           DATE_FORMAT(cpz.BuildDate,'%Y-%m-%d') as "ywrq", -- 5业务日期
           '' as "jhdbh", -- 6计划明细号
			CONCAT(IFNULL(o.kqbm,''),a.htbh) as "htbh" -- 7合同编号
           ,a.cyr as "cyr" -- 8承运人
			,a.cyrlxdh as "lxdh" -- 9联系电话
           		,UPPER(A.CYRSFZH) AS "sfzh"  -- 10身份证号
					,a.cyrlxdz as "xxdz", -- 11详细地址
                      CASE
               WHEN A.CLLX IN ('2768','2769') THEN
               '9'
               ELSE
               SUBSTR(X.GBCODE,-1,1)
            END AS "ysgj", -- 12运输工具
           Y.enumitemcode AS "cchlx",  --  13车船号类型
           A.CPH AS "cch",  -- 14车船号
					 A.GCH as "gch",  -- 15挂车号
           DATE_FORMAT(a.BuildDate,'%Y-%m-%d %T') as "djsj", -- 16登记时间
           a.djr AS "djmgryxm",  -- 17登记门岗人员
           K.GBCODE  AS "lspzdm",  -- 18粮食品种代码
           I.GBCODE AS "lsxzdm",  -- 19粮食性质代码
           M.ENUMITEMNAME AS "shnd",  -- 20收获年度
           LPAD(G.ENUMITEMCODE,6,0) AS "cddm",  -- 21产地代码
           -- DATE_FORMAT(C.JYSJ ,'%Y-%m-%d %T') AS JYRQ,  -- 23  质检时间
					 '0' as "jjlx", -- 22检斤类型
           E.MZ AS "mz",  -- 23毛重
           E.MJY AS "mzjby",  -- 24毛重监磅员
           DATE_FORMAT(E.BuildDate,'%Y-%m-%d %T')  AS "mzjlsj",  -- 25毛重计量时间
           E.MJY AS "mzjly",  -- 26毛重计量员
					 cpz.bgy as "zcy", -- 27值仓员
					 cpz.PZ AS "pz",  -- 28皮重
           cpz.PJY AS "pzjby",  -- 29皮重监磅员
           DATE_FORMAT(cpz.BuildDate,'%Y-%m-%d %T')  AS "pzjlsj",  -- 30 皮重计量时间
           cpz.PJY AS "mzjly",  -- 31 皮重计量员
					'3' as "bzw", -- 32包装物
					cpz.dbbzkl as "bzbdbz", -- 33标准包单包重
	        cpz.bzsl as "bzbjs", -- 34 标准包件数
	        (ifnull(mz, 0)-ifnull(pz, 0)+ifnull(cpz.bzwkl, 0)*-1+ifnull(cpz.xckqt, 0)*-1-cpz.xzklhjz)*-1 as "zjklxj" -- 35质检扣量
					,cpz.bzwkl*-1 as "bzwkl" -- 36包装物扣量
					,cpz.xckqt*-1 as "qtkl" -- 37其他扣量
					,cpz.klyy as "klyy" -- 38扣量原因
					,0.0 as "xckl" -- 39现场扣量
					,cpz.zkj as "zkj" -- 40增扣价
					,cpz.zkjyy as "zkjyy" -- 41增扣价原因
					,cpz.xzklhjz as "jz"  -- 42净重
          ,cpz.zyz as "zxzydw",  -- 43装卸作业单位
           H.CMSJ AS "cmsj",  -- 44出门时间
           H.CZR AS "cmqrmgryxm",  -- 45出门确认门岗人员姓名
           '${rkjsdh}' AS "rkjsdh",  -- 46结算单号
			'' as "bz", -- 47备注
          	'${czbz}' as "czbz", -- 48操作标志
            NOW() AS "zhgxsj", -- 49最后更新时间
			a.UnitID as "unitId", -- 50单位id
            A.BIZNO as "bizNo", -- 51单据号
            0 as "is_upload", -- 52是否上传
			NOW() AS "load_date", -- 53加载时间
			0 as "qzsfzkl",-- 54其中水分增扣量
			0 as "qzzzzkl",-- 55其中：杂质增扣量
			0 as "qzbwslkl",-- 56其中：不完善粒扣量
			0 as "qzhhkl",-- 57其中：互混扣量
			0 as "qzsmlkl",-- 58其中：生霉粒扣量
			0 as "qzzjmlkl",-- 59其中：整精米粒增扣量
			0 as "qzgwcmkl",-- 60其中：谷外糙米增扣量
			0 as "qzhlmkl",-- 61其中黄粒米增扣量
			0 as "qzqtkl",-- 62其中：其他扣量
			0 as "zlfyzkl"-- 63其中：整理费用折扣量
   FROM  DATA_CRKYW_RMDJ_DEFAULT A  --   汽车入门
         LEFT JOIN  DATA_CRKYW_QYXX_DEFAULT B  --   汽车扦样
        ON A.UNITID = B.UNITID
       AND A.BIZNO = B.BIZNO
      LEFT JOIN  DATA_CRKYW_JYXX_DEFAULT C  --   汽车化验
        ON A.UNITID = C.UNITID
       AND A.BIZNO = C.BIZNO

     LEFT JOIN DEPOT.ORG_INFO O
        ON O.ORG_ID = A.unitID
       AND O.ORG_CLASS_ID = 5318
	    -- LEFT JOIN DEPOT.BUSINESS_PLAN Q -- 计划明细
     --   ON A.JHDBH=Q.PLAN_NUMBER
      inner JOIN DATA_CRKYW_CMZ_DEFAULT E  --   汽车称毛重
        ON A.UNITID = E.UNITID
       AND A.BIZNO = E.BIZNO
			 inner JOIN DATA_CRKYW_CPZ_DEFAULT cpz  --   汽车称皮重
        ON A.UNITID = cpz.UNITID
       AND A.BIZNO = cpz.BIZNO
      LEFT JOIN  DATA_CRKYW_JSXX_DEFAULT F  --   汽车结算
        ON A.UNITID = F.UNITID
       AND A.BIZNO = F.BIZNO
      inner JOIN DATA_CRKYW_RKCM_DEFAULT H  --   汽车出门
        ON A.UNITID = H.UNITID
       AND A.BIZNO = H.BIZNO
		 LEFT JOIN DEPOT.basic_warehouse st -- 货物表
        ON st.org_id = A.unitID
       AND st.warehouse_id = cpz.sjhwID
       LEFT JOIN  ENUMITEM M
        ON A.SCNF=M.ENUMITEMID
        AND M.ENUMID='1004'
        LEFT JOIN  ENUMITEM G
        ON A.CD=G.ENUMITEMID
        AND G.ENUMID='1010'
        LEFT JOIN DEPOT.BASIC_ENUM I --  粮权性质编码
          ON I.ENUMID = A.LSXZ
        -- LEFT JOIN DEPOT.BASIC_ENUM J --  等级编码
         -- ON J.ENUMID = C.LSDJ
        LEFT JOIN DEPOT.BASIC_ENUM K --  品种编码
          ON K.ENUMID = A.LSPZ
        LEFT JOIN DEPOT.BASIC_ENUM X --  运输工具
          ON X.ENUMID = A.CLLX
       -- LEFT JOIN (select COPY.* from DATA_CRKYW_JYXX_FLT_001 COPY ) P
       --   ON C.DATAID = P.MAINDATAID
        -- LEFT JOIN  ENUMITEM  Z
         --  ON  P.ZBXMC = Z.ENUMITEMID
        left join  ENUMITEM Y
          ON A.cchlx=Y.enumitemid
 where a.BizNo = '${BizNo}'
    </insert>
    <insert id="addJyklGjj">
        <!-- 国家局检验扣量 -->
        SELECT DISTINCT
        p.zbxmc as "zbxmc",p.jykl as "jykl",IFNULL(z.EnumItemName,'') as "EnumItemName",ROUND(mz-pz)  as "jz",round((mz-pz)*p.jykl/100) as "zklsl"
        FROM  data_crkyw_jyxx_default A
        inner JOIN DATA_CRKYW_JYXX_FLT_001 P
        ON A.DATAID = P.MAINDATAID
        inner JOIN DATA_CRKYW_CMZ_DEFAULT E  --   汽车称毛重
        ON A.UNITID = E.UNITID
        AND A.BIZNO = E.BIZNO
        inner JOIN DATA_CRKYW_CPZ_DEFAULT cpz  --   汽车称皮重
        ON A.UNITID = cpz.UNITID
        AND A.BIZNO = cpz.BIZNO
        LEFT JOIN ENUMITEM  Z
        ON  P.ZBXMC = Z.ENUMITEMID
        where A.BizNo = '${BizNo}'  and  IFNULL(p.jykl,'0') != '0' and IFNULL(p.jykl,'0.0') != '0.0' ORDER BY z.EnumItemCode
    </insert>
    <insert id="addRkJyywGjj">
        <!-- 	国家局入库检验业务 -->
      INSERT INTO ods_up_lsrkjyxxjk_final (
	id,
	rkjydh,
	hwdm,
	rkywdh,
	qysj,
	qyrxm,
	qyfs,
	jyxm,
	jyz,
	zkj,
	zkl,
	jyrxm,
	jysj,
	jyjg,
	lspzdm,
	lsdd,
	bgyfh,
	czbz,
	zhgxsj,
	unitId,
	bizNo,
	is_upload,
	load_date
)
SELECT    DISTINCT
					 a.dataid  as "id",  -- 1 主键
					'${rkywdh}' as "rkjydh", -- 2 检验单编号
					 w.hwdm as "hwdm", -- 3货位代码
					 '${rkywdh}' as "rkywdh", -- 4 入库业务单号
					 DATE_FORMAT(T.QYSJ, '%Y-%m-%d %T') AS "qysj", -- 5扦样时间
					 T.QYR AS "qyrxm", -- 6扦样人姓名
					 T.QYLB as "qyfs", -- 7原扦样类别 改 扦样方式
					 '' as "jyxm", -- 8
					 '' as "jyz", -- 9
					 '' as "zkj", -- 10
					 '' as "zkl", -- 11
					 a.jyr AS "jyrxm", -- 12检验人姓名
					 DATE_FORMAT(A.JYSJ, '%Y-%m-%d %T') AS "jysj", -- 13检验时间
					 IFNULL(D.ENUMITEMCode,'') AS "jyjg", -- 14检验结果
					 K.GBCODE AS "lspzdm", -- 15粮食品种编码
					 G.gbcode AS "lsdd", -- 16粮食等级
					 IFNULL(D.ENUMITEMCode,'') AS "jyjg", -- 17保管员复核
					'${czbz}' as "czbz", -- 18操作标志
       DATE_FORMAT(NOW(),'%Y-%m-%d %T') AS "zhgxsj", -- 19最后更新时间
			  a.UnitID as "unitId", -- 20单位id
            A.BIZNO as "bizNo", -- 21单据号
            0 as "is_upload", -- 22是否上传
			    NOW() AS "load_date" -- 23加载时间
  FROM
	DATA_CRKYW_JYXX_DEFAULT A -- 汽车 检验主表
INNER JOIN DATA_CRKYW_CPZ_DEFAULT B -- 汽车 称皮主表
ON A.BIZNO = B.BIZNO
INNER JOIN depot.BASIC_WAREHOUSE W -- 货位表
ON B.UNITID = W.ORG_ID
AND B.SJCHID = W.STOREHOUSE_ID
AND B.SJHWID = W.WAREHOUSE_ID
INNER JOIN DATA_CRKYW_QYXX_DEFAULT T -- 汽车 扦样主表
ON T.BIZNO = A.BIZNO
left JOIN DATA_CRKYW_RMDJ_DEFAULT E -- 汽车 入门登记主表
	ON A.BIZNO = E.BIZNO
 AND A.UNITID = e.UNITID
LEFT JOIN ENUMITEM D -- 枚举表
ON A.JYJG = D.ENUMITEMID
LEFT JOIN  depot.basic_enum G ON if(IFNULL(a.lsdj,'')= '',e.lydj,a.lsdj) = G.enumid
LEFT JOIN  depot.basic_enum G1 -- 粮食性质
ON e.LSxz = G1.enumid
LEFT JOIN ENUMITEM H ON E.CD = H.ENUMITEMID
LEFT JOIN ENUMITEM I ON E.LSXZ = H.ENUMITEMID
LEFT JOIN DEPOT.basic_enum K ON E.LSPZ = K.enumId and K.PARENTID = '1061'
 --  where g1.gbcode  not in ('110','270','300','310','320','330','340','900')
WHERE
	a.BizNo = '${BizNo}'  AND
	if(IFNULL(a.lsdj,'')= '',e.lydj,a.lsdj) != ''
	 limit 0,1
    </insert>
    <insert id="addRkJymxGjj">
        <!-- 国家局入库检验明细 -->
        SELECT DISTINCT
        z.EnumItemCode as "jyxm",IFNULL(p.jyz, '0') as "jyz",IFNULL(p.jykj,0.0) as "zkj",IFNULL(p.jykl,0.0) as "zkl"
        FROM  data_crkyw_jyxx_default A
        inner JOIN DATA_CRKYW_JYXX_FLT_001 P
        ON A.DATAID = P.MAINDATAID
        LEFT JOIN ENUMITEM  Z
        ON  P.ZBXMC = Z.ENUMITEMID
        where   IFNULL(z.EnumItemCode,'') != ''  and a.BizNo = '${BizNo}'  ORDER BY z.EnumItemCode

    </insert>
    <insert id="addRkJsywGjj">
    <!-- 国家局入库结算 -->
    INSERT INTO ods_up_lsrkjsxxjk_final (
    id,
    rkjsdh,
    hwdm,
    hth,
    jssl,
    jsdj,
    jsje,
    jssj,
    jsfs,
    skr,
    yhhbdm,
    skrsfzh,
    khhh,
    khhmc,
    yhzh,
    fphm,
    fpzt,
    fkdw,
    czbz,
    zhgxsj,
    unitId,
    bizNo,
    is_upload,
    load_date
    )
    select DISTINCT
    a.DataID as "id",
    '${rkjsdh}' as "rkjsdh" , --  1入库结算单号
    w.hwdm as "hwdm", -- 2货位代码
    CONCAT(IFNULL(o.kqbm,''),c.htbh) as "htbh", -- 3合同编号
    A.JSZL  AS  "jssl", --  4结算数量
    A.JSDJ  AS  "jsdj", --  5结算单价（元）
    A.JSDJ * A.JSZL AS  "jsje", -- 6结算金额（元）
    DATE_FORMAT(A.JSSJ,'%Y-%m-%d %T') as "jssj", --  7结算时间
    if(ifnull(g.enumCode,'')= '','0','1') AS  "jsfs", --  8结算方式
    f.name as "skr", -- 9收款人
    g.enumCode  AS  "yhhbdm", -- 10 银行行别代码
    IFNULL(f.identification,f.social_credit_code) AS "skrsfzh", -- 11收款人身份证号
    F.khhh AS "khhh", -- 12开户行号
    f.account_name AS "khhmc", -- 13开户行名称
    f.account_number AS "yhzh", -- 14银行账号
    A.FPHM AS "fphm", -- 15发票号码
    A.FPZT AS "fpzt", -- 16发票状态
    A.khmc AS "fkdw", -- 17付款单位
    '${czbz}' as "czbz", -- 18操作标志
    NOW() AS "zhgxsj", -- 19最后更新时间
    a.UnitID as "unitId", -- 20单位id
    A.BIZNO as "bizNo", -- 21单据号
    0 as "is_upload", -- 22是否上传
    NOW() AS "load_date" -- 23加载时间
    FROM
    DATA_CRKYW_JSXX_DEFAULT A -- 汽车入库结算
    LEFT JOIN depot.ORG_INFO O ON A.UNITID = O.ORG_ID

    inner JOIN DATA_CRKYW_CPZ_DEFAULT B -- 皮重表
    ON A.BIZNO = B.BIZNO
    AND A.UNITID = B.UNITID

    inner JOIN depot.BASIC_WAREHOUSE W -- 货位表
    ON A.UNITID = W.ORG_ID
    AND B.SJCHID = W.STOREHOUSE_ID
    AND B.SJHWID = W.WAREHOUSE_ID

    LEFT JOIN DATA_CRKYW_RMDJ_DEFAULT C -- 汽车入门登记
    ON A.BIZNO = C.BIZNO
    AND A.UNITID = C.UNITID

    LEFT JOIN data_crkyw_tzdxx_default e ON C.tzdbh = e.tzdbh
    AND A.UNITID = e.UNITID

    LEFT JOIN depot.business_customer f ON e.khid = f.id

    LEFT JOIN enumitem m ON a.jsfs = m.enumitemid
    LEFT JOIN depot.basic_enum g ON g.enumId = f.bank_name

    -- LEFT JOIN depot.basic_enum h ON c.lsxz = h.enumid
    -- where f.gbcode not in ('110','270','300','310','320','330','340','900') and c.lspz not in ('7144','7145')
    where a.BizNo = '${BizNo}' limit 0,1
    </insert>
    <insert id="addCkywGjj">
        <!-- 		国家局出库记录 -->
        INSERT INTO ods_up_lsckxxjk_final (
        id,
        ckywdh,
        hwdm,
        cktzdh,
        ywlx,
        ywrq,
        hth,
        cyr,
        lxdh,
        sfzh,
        ysgj,
        xldd,
        cchlx,
        cch,
        gch,
        djsj,
        djmgryxm,
        lspzdm,
        lsdjdm,
        lsxzdm,
        shnd,
        cddm,
        pz,
        pzjby,
        pzjlsj,
        pzjly,
        mz,
        mzjby,
        mzjlsj,
        mzjly,
        bzw,
        bzbdbz,
        bzbjs,
        jz,
        kzl,
        zcbgyxm,
        zxzydw,
        cmsj,
        cmqrmgryxm,
        ckjsdh,
        bz,
        czbz,
        zhgxsj,
        unitId,
        bizNo,
        is_upload,
        load_date
        )
        SELECT 	DISTINCT
        a.DataID as "id", -- 1:主键
        '${ckywdh}' as "ckywdh", -- 2:出库业务单号
        st.hwdm as "hwdm", -- 3货位代码
        a.tzdbh as "cktzdh",-- 4出库通知编号
        '1' as "ywlx", -- 5入库（默认）
        DATE_FORMAT(a.BuildDate,'%Y-%m-%d') as "ywrq", -- 6业务日期
        a.htbh as "hth" -- 7合同编号
        ,a.cyr as "cyr" -- 8承运人
        ,a.cyrdh as "lxdh" -- 9联系电话
        ,UPPER(A.cyrsfzh) AS "sfzh",  -- 10身份证号
        CASE
        WHEN A.CLLX IN ('2768','2769') THEN
        '9'
        ELSE
        SUBSTR(X.GBCODE,-1,1)
        END AS "ysgj", -- 11运输工具
        null as "xldd", -- 12卸粮地点
        Y.enumitemcode AS "cchlx",  --  13车船号类型
        A.CPH AS "cch",  -- 14车船号
        A.GCH as "gch",  -- 15挂车号
        DATE_FORMAT(a.BuildDate,'%Y-%m-%d %T') as "djsj", -- 16登记时间
        a.djr AS "djmgryxm",  -- 17登记门岗人员
        K.GBCODE  AS "lspzdm",  -- 18粮食品种代码
        IFNULL(J.GBCODE,'01')  AS "lsdjdm",  -- 19粮食等级代码
        I.GBCODE AS "lsxzdm",  -- 20粮食性质代码
        M.ENUMITEMNAME AS "shnd",  -- 21:收获年度
        LPAD(G.ENUMITEMCODE,6,0) AS "cddm",  -- 22产地代码

        cpz.PZ AS "pz",  -- 23皮重
        cpz.PJY AS "pzjby",  -- 24皮重监磅员
        DATE_FORMAT(cpz.BuildDate,'%Y-%m-%d %T')  AS "pzjlsj",  -- 25 皮重计量时间
        cpz.PJY AS "pzjly",  -- 26 皮重计量员

        E.MZ AS "mz",  -- 27毛重
        E.MJY AS "mzjby",  -- 28毛重监磅员
        DATE_FORMAT(E.BuildDate,'%Y-%m-%d %T')  AS "mzjlsj",  -- 29毛重计量时间
        E.MJY AS "mzjly",  -- 20毛重计量员

        "3" as "bzw", -- 31包装物
        E.bzwdjzl as "bzbdbz", -- 32标准包单包重
        E.js as "bzbjs", -- 33 标准包件数

        E.klhjz as "jz"  -- 34净重
        ,E.klhjz-(E.mz-cpz.pz) as "kzl" -- 35扣（增）量

        ,E.bgy as "zcbgyxm" -- 36值仓保管员姓名
        ,E.zyz as "zxzydw",  -- 37装卸作业单位
        DATE_FORMAT(H.BuildDate,'%Y-%m-%d %T') AS "cmsj",  -- 38出门时间
        H.cmdjr AS "cmqrmgryxm",  -- 39出门确认门岗人员姓名
        null AS "ckjsdh",  -- 40结算单号 无结算
        '' as "bz", -- 41备注
        '${czbz}' as "czbz", -- 42操作标志
        NOW() AS "zhgxsj", -- 43最后更新时间
        a.UnitID as "unitId", -- 442单位id
        A.BIZNO as "bizNo", -- 45单据号
        0 as "is_upload", -- 46是否上传
        NOW() AS "load_date" -- 47加载时间


        FROM  DATA_CRKYW_CKRMDJ_DEFAULT A  --   汽车入门


        LEFT JOIN DEPOT.ORG_INFO O
        ON O.ORG_ID = A.unitID
        AND O.ORG_CLASS_ID = 5318
        -- LEFT JOIN DEPOT.BUSINESS_PLAN Q -- 计划明细
        --   ON A.JHDBH=Q.PLAN_NUMBER
        inner JOIN DATA_CRKYW_CKCPZ_DEFAULT cpz  --   汽车称皮重
        ON A.UNITID = cpz.UNITID
        AND A.BIZNO = cpz.BIZNO

        inner JOIN DATA_CRKYW_CKCMZ_DEFAULT E  --   汽车称毛重
        ON A.UNITID = E.UNITID
        AND A.BIZNO = E.BIZNO


        inner JOIN DATA_CRKYW_CKCM_DEFAULT H  --   汽车出门
        ON A.UNITID = H.UNITID
        AND A.BIZNO = H.BIZNO
        left JOIN data_crkyw_tzdxx_default tzd  --   通知单
        ON A.tzdbh = tzd.tzdbh

        LEFT JOIN DEPOT.basic_warehouse st
        ON st.org_id = A.unitID
        AND st.warehouse_id = E.sjhwh
        LEFT JOIN  ENUMITEM M
        ON tzd.scnf=M.ENUMITEMID
        AND M.ENUMID='1004'
        LEFT JOIN  ENUMITEM G
        ON tzd.cd=G.ENUMITEMID
        AND G.ENUMID='1010'
        LEFT JOIN DEPOT.BASIC_ENUM I --  粮权性质编码
        ON I.ENUMID = A.LYXZ
        LEFT JOIN DEPOT.BASIC_ENUM J --  等级编码
        ON J.ENUMID = A.lydj
        LEFT JOIN DEPOT.BASIC_ENUM K --  品种编码
        ON K.ENUMID = A.LSPZ
        LEFT JOIN DEPOT.BASIC_ENUM X --  运输工具
        ON X.ENUMID = A.CLLX

        left join  ENUMITEM Y
        ON A.cchlx=Y.enumitemid
        where a.BizNo = '${BizNo}'
    </insert>
    <insert id="addDcywByYc">
        <!-- 通过移仓进行倒仓业务 -->
    INSERT INTO ods_up_dcxxjk_final (
	id,
	dcdh,
	dclx,
	dcjhwjbh,
	tzdh,
	dcdw,
	drdw,
	lspzdm,
	dchwdm,
	drhwdm,
	dcrq,
	dcsl,
	bzw,
	bzbjs,
	zxzydw,
	bz,
	czbz,
	zhgxsj,
	unitId,
	zycx,
	is_upload,
	load_date
	)
	SELECT DISTINCT
	a.DataID as "id", -- 1主键
	'${dcdh}' as "dcdh", -- 2倒仓单号
	 '0' as "dclx", -- 3倒仓类型
		null as "dcjhwjbh", -- 4倒仓计划文件编号
		null as "tzdh",-- 5出库通知编号
		o.kqbm as "dcdw", -- 6倒出单位
		o.kqbm as "drdw",-- 7倒入单位
		K.GBCODE  AS "lspzdm",  -- 8粮食品种代码
		st.hwdm as "dchwdm", -- 9倒出货位代码
		st1.hwdm as "drhwdm", -- 10倒入货位代码
		DATE_FORMAT(a.BuildDate,'%Y-%m-%d')  AS "dcrq",  -- 11 倒仓日期
		a.bdsl as "dcsl",  -- 12倒仓数量
		'3' as "bzw", -- 13包装物
		0 as "bzbjs", -- 14标准包件数
		null as "zxzydw", -- 15装卸作业单位
		'' as "bz", -- 16备注
     '${czbz}' as "czbz", -- 17操作标志
     DATE_FORMAT(NOW(),'%Y-%m-%d %T') AS "zhgxsj", -- 18最后更新时间
			a.UnitID as "unitId", -- 19单位id
      A.bddh as "bizNo", -- 20变动单号
      0 as "is_upload", -- 21是否上传
			DATE_FORMAT(NOW(),'%Y-%m-%d %T')  AS "load_date" -- 22加载时间
FROM
		data_kcgl_kcbd_default a
LEFT JOIN DEPOT.BASIC_ENUM K --  品种编码
ON K.ENUMID = a.pz

 LEFT JOIN DEPOT.basic_warehouse st -- 倒出仓
        ON st.org_id = A.unitID
       AND st.warehouse_id = a.hwid

			 LEFT JOIN DEPOT.basic_warehouse st1 -- 倒入仓
        ON st1.org_id = a.unitID
       AND st1.warehouse_id = a.yrhwid

  LEFT JOIN DEPOT.ORG_INFO O -- 单位
        ON O.ORG_ID = A.unitID
where a.yrhwid is not null and a.bddh = '${bddh}'
    </insert>
    <insert id="addDcyw">
        <!-- 国家局倒仓 -->
        	INSERT INTO ods_up_dcxxjk_final (
	id,
	dcdh,
	dclx,
	dcjhwjbh,
	tzdh,
	dcdw,
	drdw,
	lspzdm,
	dchwdm,
	drhwdm,
	dcrq,
	dcsl,
	bzw,
	bzbjs,
	zxzydw,
	bz,
	czbz,
	zhgxsj,
	unitId,
	zycx,
	is_upload,
	load_date
	)
SELECT DISTINCT
	a.DataID as "id", -- 1主键
	'${dcdh}' as "dcdh", -- 2倒仓单号
	 '0' as "dclx", -- 3倒仓类型
		null as "dcjhwjbh", -- 4倒仓计划文件编号
		 a.tzdbh as "tzdh",-- 5出库通知编号
		o.kqbm as "dcdw", -- 6倒出单位
		o.kqbm as "drdw",-- 7倒入单位
		K.GBCODE  AS "lspzdm",  -- 8粮食品种代码
		st.hwdm as "dchwdm", -- 9倒出货位代码
		st1.hwdm as "drhwdm", -- 10倒入货位代码
		DATE_FORMAT(b.cpzsj,'%Y-%m-%d')  AS "dcrq",  -- 11 倒仓日期
		b.xzklhjz as "dcsl",  -- 12倒仓数量
		'3' as "bzw", -- 13包装物
		0 as "bzbjs", -- 14标准包件数
		b.jpy as "zxzydw", -- 15装卸作业单位
		'' as "bz", -- 16备注
     '${czbz}' as "czbz", -- 17操作标志
     DATE_FORMAT(NOW(),'%Y-%m-%d %T') AS "zhgxsj", -- 18最后更新时间
			a.UnitID as "unitId", -- 19单位id
      A.zycx as "bizNo", -- 20作业次序
      0 as "is_upload", -- 21是否上传
			DATE_FORMAT(NOW(),'%Y-%m-%d %T')  AS "load_date" -- 22加载时间
FROM
	data_crkyw_cmz_default a
LEFT JOIN data_crkyw_cpz_default b ON a.zycx = b.zycx and a.unitID = b.unitID

        LEFT JOIN DEPOT.BASIC_ENUM K --  品种编码
          ON K.ENUMID = a.lspz

		 LEFT JOIN DEPOT.basic_warehouse st -- 倒出仓
        ON st.org_id = A.unitID
       AND st.warehouse_id = a.nrhwID

			 LEFT JOIN DEPOT.basic_warehouse st1 -- 倒入仓
        ON st1.org_id = b.unitID
       AND st1.warehouse_id = b.sjhwID

     LEFT JOIN DEPOT.ORG_INFO O
        ON O.ORG_ID = A.unitID
where a.zycx  = '${zycx}' and a.unitID = '${unitId}'
    </insert>
    <insert id="addXzbdywGjj">
        <!-- 国家局性质变动-->
	INSERT INTO ods_up_lsxzzbdxxjk_final (
	id,
	lsxzzbdh,
	hwdm,
	lssl,
	hzsl,
	bzwh,
	lspzdm,
	hzqlsxzdm,
	hzhlsxzdm,
	hzrq,
	ccshr,
	zjshr,
	tjshr,
	kjshr,
	ldshr,
	bz,
	czbz,
	zhgxsj,
	unitId,
	bddh,
	is_upload,
	load_date
)
SELECT
	a.dataid as "id", -- 1主键id
	'${lsxzzbdh}' as "lsxzzbdh", -- 2粮食性质转变单号
	 st.hwdm as "hwdm", -- 3倒出货位代码
	 a.bdsl as "lssl", -- 4粮食数量
	 a.bdsl as "hzsl", -- 5划转数量
		null as "bzwh", -- 6批准文号
		K.GBCODE  AS "lspzdm",  -- 7粮食品种代码
		G1.GBCODE  AS "hzqlsxzdm",  -- 8划转前粮食性质代码
		G2.GBCODE  AS "hzhlsxzdm",  -- 98 划转后粮食性质代码
		DATE_FORMAT(a.ywfssj,'%Y-%m-%d')  AS "hzrq",  -- 11 划转日期
		a.ccshr as "ccshr", -- 12仓储审核人
		a.zjshr as "zjshr", -- 13质检审核人
    a.tjshr as "tjshr", -- 14统计审核人
    a.kjshr as "kjshr", -- 15会计审核人
    a.ldshr as "ldshr", -- 16领导审核人
		'' as "bz", -- 16备注
    '${czbz}' as "czbz", -- 17操作标志
    DATE_FORMAT(NOW(),'%Y-%m-%d %T') AS "zhgxsj", -- 18最后更新时间
		a.UnitID as "unitId", -- 19单位id
     A.bddh as "bddh", -- 20作业次序
     0 as "is_upload", -- 21是否上传
		DATE_FORMAT(NOW(),'%Y-%m-%d %T')  AS "load_date" -- 22加载时间
FROM
	data_kcgl_kcbd_default a
LEFT JOIN DEPOT.BASIC_ENUM K --  品种编码
ON K.ENUMID = a.pz

inner JOIN DEPOT.basic_warehouse st -- 货位
ON st.org_id = A.unitID

AND st.warehouse_id = a.hwid
LEFT JOIN depot.basic_enum G1 -- 变动前粮食性质
ON a.xz = G1.enumid

LEFT JOIN depot.basic_enum G2 -- 变动后粮食性质
ON a.bdhxz = G2.enumid
where a.bdhxz is not null and a.bddh = '${bddh}'
    </insert>
    <insert id="addSydYwGjj">
        <!-- 国家局损益单-->
        	 INSERT INTO ods_up_sydxxjk_final (
	id,
	sydh,
	hwdm,
	ywrq,
	rkjz,
	rksj,
	rksf,
	rkzz,
	cksj,
	ckjz,
	cksf,
	ckzz,
	jzsysl,
	qzszkl,
	qzzrshde,
	chsl,
	sysfzc,
	syyy,
	ccshr,
	zjshr,
	tjshr,
	kjshr,
	ldshr,
	bz,
	czbz,
	zhgxsj,
	unitId,
	bddh,
	is_upload,
	load_date
)
	SELECT
					 d.dataid as "id", -- 1id
					'${sydh}' as  "sydh", -- 2损溢单号
           f.hwdm AS "hwdm", -- 3货位代码
					 DATE_FORMAT(D.ywfssj,'%Y-%m-%d') as "ywrq", -- 4业务日期
					IF(d.qcsl = 0.0 or d.jysl = 0 ,d.qcsl+${syslNumber},d.qcsl) AS "rkjz",   -- 5入库净重
					 DATE_FORMAT(D.ywfssj,'%Y-%m-%d %T')as "rksj", -- 6入库时间
					 D.RKSF,--  7入库水分
           D.RKZZ,--  8入库杂质
					 DATE_FORMAT(D.ywjssj,'%Y-%m-%d %T')as "cksj", -- 9出库时间
					IF(d.qcsl = 0.0 or d.jysl = 0 ,d.jysl+${syslNumber},d.jysl) AS "ckjz",   -- 10出库净重
					 D.CKSF,--  11出库水分
           D.CKZZ,--  12出库杂质
					 IF(d.qcsl > d.jysl,d.bdsl,d.bdsl*-1) AS "jzsysl", -- 13净重损溢数量
					 D.sfjl AS "qzszkl", -- 14其中水杂减量
					 (D.hzzrsh+D.bgzrdesh) as "qzzrshde", -- 15自然损耗定额
           D.ch AS "chsl", -- 16超耗数量
           IFNULL(D.sysfzc,'0') AS "sysfzc" ,-- 17损益是否正常
					 D.SYYY,--  18损益原因
           D.CCSHR,--  19仓储审核人
           D.ZJSHR,--  20质检审核人
           D.TJSHR,--  21统计审核人
           D.KJSHR,--  22会计审核人
           D.LDSHR,--  23领导审核人
					 '' as "bz", -- 24备注
					'i' as "czbz", -- 25操作标志
					DATE_FORMAT(NOW(),'%Y-%m-%d %T') AS "zhgxsj", -- 26最后更新时间
					d.UnitID as "unitId", -- 27单位id
					 d.bddh as "bddh", -- 28变动单号
					 0 as "is_upload", -- 29是否上传
					DATE_FORMAT(NOW(),'%Y-%m-%d %T')  AS "load_date" -- 30加载时间
      FROM  DATA_KCGL_KCBD_DEFAULT D
      inner JOIN  depot.ORG_INFO O
        ON O.ORG_ID = d.UnitID

      inner JOIN  depot.BASIC_WAREHOUSE  F
       on d.hwid = F.WAREHOUSE_ID
       AND d.UNITID = F.ORG_ID

      left join  enumitem m
       on d.bdfs=m.enumitemid
      left join  depot.basic_enum c
        on d.xz=c.enumid
      where substr(m.enumitemcode,1,1) in (0,1)
		 and d.bddh = '${bddh}'
    </insert>
    <insert id="addZmkcywGjj">
        <!-- 国家局账面库存业务 -->
        		INSERT INTO ods_up_zmkcxxjk_final (
	id,
	kqdm,
	lspzdm,
	nd,
	yf,
	qcsl,
	bqsrsl,
	bqzcsl,
	qmye,
	yjbz,
	ywrq,
	czbz,
	zhgxsj,
	unitId,
	bizno,
	is_upload,
	load_date
)
SELECT
 a.DataID AS ID -- 015
 ,D.kqbm AS KQDM    -- 01库区代码
, C.GBCODE AS LSPZDM       -- 02粮食品种代码
, A.NF AS ND    -- 03年度
, LPAD(A.YF,2,0)  AS YF    -- 04月份
, A.QCKC AS QCSL       -- 05期初数量
, A.KCZJ AS BQSRSL     -- 06本期收入数量
, A.KCJS AS BQZCSL     -- 07本期支出数量
, A.QMKC AS QMYE       -- 08期末数量
, '1' AS YJBZ         -- 09月结标志
, DATE_FORMAT(A.rq,'%Y-%m-%d') AS YWRQ  -- 010业务日期
, '${czbz}' AS CZBZ -- 011操作标识
, DATE_FORMAT(NOW(),'%Y-%m-%d %T')  AS ZHGXSJ  -- 012最后更新时间
, a.UnitID As UnitID -- 单位id
, '${bizno}' AS bizno -- 015 单位id+pz+年+月
, 0 AS IS_UPLOAD -- 013
, NOW() AS LOAD_DATE -- 014

FROM  DATA_KCGL_KCYJD_DEFAULT A

LEFT JOIN DEPOT.BASIC_ENUM C
ON A.PZ=C.ENUMID
inner JOIN depot.org_info D
ON A.UNITID=D.ORG_ID
where substr(D.kqbm,1,21) !='91120111103786384L072' AND a.dataId = '${zmkcId}'
    </insert>
    <select id="findBySqlQuery" resultType="java.util.Map">
        ${sql}
    </select>
</mapper>