| 提交 | 用户 | 时间 | ||
| 58d006 | 1 | package com.mandi.fendan.util; |
| A | 2 | |
| 3 | public class SqlTxt { | |
| 4 | ||
| 5 | //where a.XSTD_TDBH='td-00025' | |
| 6 | ||
| 7 | //获取巩义erp销售提单信息 | |
| 8 | public static String GYErpXSTDsql="SELECT a.XSTD_TDBH td_tdbh,a.XSTD_SODKHMC td_khmc,a.XSTD_BZ td_bz,a.XSTD_TDLS td_tdls," | |
| 9 | + "d.PP0XSDHDD_MC td_shdd,a.XSTD_BL_WFDSL td_wfdsl," | |
| 10 | + "Convert(decimal(18,3),a.XSTD_BL_WFDMZ) td_wfdzl," | |
| 11 | + "a.XSTD_C9 td_shsj,a.XSTD_C1 td_fpbh," | |
| 12 | + "a.XSTD_YWRQ td_ywrq,a.XSTD_C10 companyNo,b.KCBMZD_BMMC td_bmmc,a.XSTD_ZDXM td_zdr,a.XSTD_BMBH td_bmbh,a.XSTD_DJRQ td_djrq," | |
| 13 | + "case a.XSTD_C3 when '1' then '送货' when '2' then '自提' else '未定义' end td_ztbz,c.ZWZGZD_ZGXM td_xsr,a.XSTD_C2 td_shddbh " | |
| 14 | + " from XSTD a WITH (NOLOCK) LEFT JOIN KCBMZD b on a.XSTD_BMBH=b.KCBMZD_BMBH " | |
| 15 | + " LEFT JOIN ZWZGZD c on a.XSTD_RYBH=c.ZWZGZD_ZGBH " | |
| 16 | + " LEFT JOIN PP0XSDHDD d on a.XSTD_C2=d.PP0XSDHDD_BH "; | |
| 17 | //获取巩义erp已经分配的销售提单数量 | |
| 18 | public static String countGYErpXSTDsql="SELECT count(a.XSTD_TDBH) as cc " | |
| 19 | + "from XSTD a WITH (NOLOCK) LEFT JOIN KCBMZD b on a.XSTD_BMBH=b.KCBMZD_BMBH " | |
| 20 | + " LEFT JOIN PP0XSDHDD d on a.XSTD_C2=d.PP0XSDHDD_BH "; | |
| 21 | ||
| 22 | //获取巩义销售提单明细 | |
| 23 | public static String GYErpXSTDMXsql="SELECT b.XSTDMX_WLBH tdmx_wlbh,c.LSWLZD_WLMC tdmx_wlmc," | |
| 24 | + "b.XSTDMX_TDFL tdmx_tdfl,b.XSTDMX_TDLS tdmx_tdls,d.LSFZMB_MC tdmx_zpbz," | |
| 25 | + "e.JSJLDW_DWMC tdmx_dwmx, b.XSTDMX_PCH tdmx_pch,b.XSTDMX_ZYX1 tdmx_zyx1," | |
| 26 | + "b.XSTDMX_BHSE tdmx_bhse,b.XSTDMX_U2 tdmx_mz,b.XSTDMX_ZSL tdmx_pz," | |
| 27 | + "b.XSTDMX_ZSL tdmx_jz,a.XSTD_SHDKHMC tdmx_shdw,f.XSTDZL_ZLMC tdmx_tdlx," | |
| 28 | + "1 tdmx_sl,g.LSPCSX_BZ tdmx_ggxh,b.XSTDMX_BL_ZCBZ tdmx_sfzc,b.XSTDMX_BL_ZCCPH tdmx_zccph,b.XSTDMX_BL_FDBZ tdmx_fdbz," | |
| 29 | + "i.LSFZMB_MC hj,j.LSFZMB_MC zt,h.KCHWZD_HWMC tdmx_hwbh," | |
| 30 | + "b.XSTDMX_BL_FDDJBH tdmx_fdywbh,b.XSTDMX_BL_FDCPH tdmx_cph " | |
| 31 | + "from XSTD a WITH (NOLOCK) LEFT JOIN XSTDMX b WITH (NOLOCK) on a.XSTD_TDLS =b.XSTDMX_TDLS " | |
| 32 | + "LEFT JOIN LSWLZD c on b.XSTDMX_WLBH=c.LSWLZD_WLBH " | |
| 33 | + "left JOIN LSFZMB d on b.XSTDMX_ZYX3=d.LSFZMB_BH and d.LSFZMB_LBBH='0026' " | |
| 34 | + "left JOIN JSJLDW e on c.LSWLZD_JLDW=e.JSJLDW_DWDM " | |
| 35 | + "left join XSTDZL f on a.XSTD_ZLBH=XSTDZL_ZLBH " | |
| 36 | + "left join LSPCSX g WITH (NOLOCK) on (b.XSTDMX_WLBH=g.LSPCSX_WLBH and XSTDMX_PCH=g.LSPCSX_PCH) " | |
| 37 | + "LEFT JOIN KCHWZD h on b.XSTDMX_HWBH=h.KCHWZD_HWBH " | |
| 38 | + "left JOIN LSFZMB i on b.XSTDMX_ZYX1=i.LSFZMB_BH and i.LSFZMB_LBBH='0024' " | |
| 39 | + "left JOIN LSFZMB j on b.XSTDMX_ZYX2=j.LSFZMB_BH and j.LSFZMB_LBBH='0029' "; | |
| 40 | ||
| 41 | public static String GYErpXSTDandXSTDMXsql="SELECT b.XSTDMX_WLBH tdmx_wlbh,c.LSWLZD_WLMC tdmx_wlmc," | |
| 42 | + "b.XSTDMX_TDFL tdmx_tdfl,b.XSTDMX_TDLS tdmx_tdls,d.LSFZMB_MC tdmx_zpbz," | |
| 43 | + "e.JSJLDW_DWMC tdmx_dwmx, b.XSTDMX_PCH tdmx_pch,b.XSTDMX_ZYX1 tdmx_zyx1," | |
| 44 | + "b.XSTDMX_BHSE tdmx_bhse,b.XSTDMX_U2 tdmx_mz,b.XSTDMX_ZSL tdmx_pz," | |
| 45 | + "b.XSTDMX_ZSL tdmx_jz,a.XSTD_SHDKHMC tdmx_shdw,f.XSTDZL_ZLMC tdmx_tdlx," | |
| 46 | + "1 tdmx_sl,g.LSPCSX_BZ tdmx_ggxh,LTRIM(RTRIM(b.XSTDMX_BL_ZCCPH)) tdmx_sfzc,b.XSTDMX_BL_ZCCPH tdmx_zccph," | |
| 47 | + "i.LSFZMB_MC hj,j.LSFZMB_MC zt,h.KCHWZD_HWMC tdmx_hwbh," | |
| 48 | + "b.XSTDMX_BL_FDDJBH tdmx_fdywbh,b.XSTDMX_BL_FDCPH tdmx_cph,a.XSTD_TDBH tdmx_tdbh," | |
| 49 | + "a.XSTD_BZ td_bz,a.XSTD_YWRQ td_ywrq,a.XSTD_ZDXM td_zdr,k.KCBMZD_BMMC td_bmmc " | |
| 50 | + "from XSTD a WITH (NOLOCK) LEFT JOIN XSTDMX b WITH (NOLOCK) on a.XSTD_TDLS =b.XSTDMX_TDLS " | |
| 51 | + "LEFT JOIN LSWLZD c on b.XSTDMX_WLBH=c.LSWLZD_WLBH " | |
| 52 | + "left JOIN LSFZMB d on b.XSTDMX_ZYX3=d.LSFZMB_BH and d.LSFZMB_LBBH='0026' " | |
| 53 | + "left JOIN JSJLDW e on c.LSWLZD_JLDW=e.JSJLDW_DWDM " | |
| 54 | + "left join XSTDZL f on a.XSTD_ZLBH=XSTDZL_ZLBH " | |
| 55 | + "left join LSPCSX g WITH (NOLOCK) on (b.XSTDMX_WLBH=g.LSPCSX_WLBH and XSTDMX_PCH=g.LSPCSX_PCH) " | |
| 56 | + "LEFT JOIN KCHWZD h on b.XSTDMX_HWBH=h.KCHWZD_HWBH " | |
| 57 | + "left JOIN LSFZMB i on b.XSTDMX_ZYX1=i.LSFZMB_BH and i.LSFZMB_LBBH='0024' " | |
| 58 | + "left JOIN LSFZMB j on b.XSTDMX_ZYX2=j.LSFZMB_BH and j.LSFZMB_LBBH='0029' " | |
| 59 | + "LEFT JOIN KCBMZD k on a.XSTD_BMBH=k.KCBMZD_BMBH "; | |
| 60 | ||
| 61 | //修改分单明细 | |
| 62 | public static String XGGYErpXSTDMXsql="update XSTDMX WITH(ROWLOCK) set XSTDMX_BL_FDCPH=?,XSTDMX_BL_FDBZ=?,XSTDMX_BL_FDDJBH=? " | |
| 63 | + "from XSTDMX a WITH(ROWLOCK) left join XSTD b WITH(NOLOCK) on b.XSTD_TDLS =a.XSTDMX_TDLS "; | |
| 64 | //批量提交装车修改分单明细 | |
| 65 | public static String XGGYErpXSTDMXsql1="update XSTDMX WITH(ROWLOCK) set XSTDMX_BL_FDCPH=?,XSTDMX_BL_FDBZ=?,XSTDMX_BL_FDDJBH=?,XSTDMX_BL_SJMC=?,XSTDMX_BL_SJTELE=?,XSTDMX_BL_WLGS=? " | |
| 66 | + "from XSTDMX a WITH(ROWLOCK) left join XSTD b WITH(NOLOCK) on b.XSTD_TDLS =a.XSTDMX_TDLS "; | |
| 67 | public static String XGGYErpXSTDMXsql2="update XSTDMX WITH(ROWLOCK) set XSTDMX_BL_SJMC=?,XSTDMX_BL_SJTELE=?,XSTDMX_BL_WLGS=? " | |
| 68 | + "from XSTDMX a WITH(ROWLOCK) left join XSTD b WITH(NOLOCK) on b.XSTD_TDLS =a.XSTDMX_TDLS "; | |
| 69 | //根据提单号汇总提单明细 | |
| 70 | public static String CcGYErpXSTDMXsql="select count(b.XSTDMX_TDFL) XSTD_BL_WFDSL,ISNULL(sum(b.XSTDMX_U2),0) XSTD_BL_WFDMZ from XSTD a WITH (NOLOCK) LEFT JOIN XSTDMX b WITH (NOLOCK) on a.XSTD_TDLS =b.XSTDMX_TDLS "; | |
| 71 | //根据销售提单修改销售提单信息 | |
| 72 | public static String XGGYErpXSTDsql="update XSTD WITH(ROWLOCK) set XSTD_BL_WFDSL=?,XSTD_BL_WFDMZ=? where XSTD_TDBH=?"; | |
| 73 | ||
| 74 | //关闭提单修改提单操作 | |
| 75 | public static String GBXSTDSsql="update XSTD WITH(ROWLOCK) set XSTD_BL_WFDSL=?,XSTD_BL_WFDMZ=?,XSTD_BL_ZCWCBZ=? where XSTD_TDBH=? "; | |
| 76 | //关闭提单修改提单明细操作 | |
| 77 | public static String GBXSTDMXSsql="update XSTDMX WITH(ROWLOCK) " | |
| 78 | + "set XSTDMX_BL_FDCPH=?,XSTDMX_BL_FDBZ=?,XSTDMX_BL_ZCCPH=?,XSTDMX_BL_ZCBZ=?,XSTDMX_BL_SJMC=?,XSTDMX_BL_SJTELE=? " | |
| 79 | + "from XSTDMX a WITH(ROWLOCK) left join XSTD b WITH(NOLOCK) on b.XSTD_TDLS =a.XSTDMX_TDLS " | |
| 80 | + "where b.XSTD_TDBH=? and a.XSTDMX_TDFL=? "; | |
| 81 | ||
| 82 | ||
| 83 | //增加 | |
| 84 | public static String XGGYErpXSTDaddsql="update XSTD WITH(ROWLOCK) set XSTD_BL_WFDSL=XSTD_BL_WFDSL+?,XSTD_BL_WFDMZ=Convert(decimal(18,4),XSTD_BL_WFDMZ+?) where XSTD_TDBH=?"; | |
| 85 | ||
| 86 | //换车 | |
| 87 | public static String HCGYErpXSTDMXsql="UPDATE XSTDMX WITH(ROWLOCK) set XSTDMX_BL_FDCPH=? WHERE XSTDMX_BL_FDDJBH=? AND XSTDMX_BL_FDCPH=? "; | |
| 88 | ||
| 89 | public static String JCZCErpXSTDMXsql="SELECT top 1 b.XSTDMX_TDLS cc FROM XSTDMX b WITH (NOLOCK) left join XSTD a WITH (NOLOCK) on b.XSTDMX_TDLS=a.XSTD_TDLS "; | |
| 90 | ||
| 91 | ||
| 92 | //获得发货地区 | |
| 93 | public static String FHDQsql="SELECT PP0XSDHDD_BH bianhao,PP0XSDHDD_JS jishu,PP0XSDHDD_MC name from PP0XSDHDD WITH (NOLOCK) "; | |
| 94 | //获得发货地区与物流公司关联表 | |
| 95 | public static String FHWLsql="SELECT XSDQWL_DQBH,XSDQWL_WLBH from XSDQWL WITH (NOLOCK) "; | |
| 96 | //删除发货地区 | |
| 97 | public static String SCFHDQsql="delete from PP0XSDHDD "; | |
| 98 | //删除物流公司与发货地区关联表 | |
| 99 | public static String SCFHWLsql="delete from XSDQWL "; | |
| 100 | // //新增发货地区 | |
| 101 | public static String XZFHDQsql="insert into PP0XSDHDD (PP0XSDHDD_JS,PP0XSDHDD_MC,PP0XSDHDD_MX,PP0XSDHDD_BH) values (?,?,?,?) "; | |
| 102 | // //新增物流公司合发货地区关联表 | |
| 103 | public static String XZFHWLsql="insert into XSDQWL (XSDQWL_DQBH,XSDQWL_WLBH) values (?,?) "; | |
| 104 | //修改发货地区 | |
| 105 | public static String XGFHDQsql="UPDATE PP0XSDHDD set PP0XSDHDD_JS=?,PP0XSDHDD_MC=?,PP0XSDHDD_MX=? where PP0XSDHDD_BH=? "; | |
| 106 | //修改发货地区是否明细字段 | |
| 107 | public static String XGFHDQMXsql="UPDATE PP0XSDHDD set PP0XSDHDD_MX=? where PP0XSDHDD_BH=? "; | |
| 108 | } | |
