package com.mandi.fendan.util;
|
|
public class SqlTxt {
|
|
//where a.XSTD_TDBH='td-00025'
|
|
//获取巩义erp销售提单信息
|
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,"
|
+ "d.PP0XSDHDD_MC td_shdd,a.XSTD_BL_WFDSL td_wfdsl,"
|
+ "Convert(decimal(18,3),a.XSTD_BL_WFDMZ) td_wfdzl,"
|
+ "a.XSTD_C9 td_shsj,a.XSTD_C1 td_fpbh,"
|
+ "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,"
|
+ "case a.XSTD_C3 when '1' then '送货' when '2' then '自提' else '未定义' end td_ztbz,c.ZWZGZD_ZGXM td_xsr,a.XSTD_C2 td_shddbh "
|
+ " from XSTD a WITH (NOLOCK) LEFT JOIN KCBMZD b on a.XSTD_BMBH=b.KCBMZD_BMBH "
|
+ " LEFT JOIN ZWZGZD c on a.XSTD_RYBH=c.ZWZGZD_ZGBH "
|
+ " LEFT JOIN PP0XSDHDD d on a.XSTD_C2=d.PP0XSDHDD_BH ";
|
//获取巩义erp已经分配的销售提单数量
|
public static String countGYErpXSTDsql="SELECT count(a.XSTD_TDBH) as cc "
|
+ "from XSTD a WITH (NOLOCK) LEFT JOIN KCBMZD b on a.XSTD_BMBH=b.KCBMZD_BMBH "
|
+ " LEFT JOIN PP0XSDHDD d on a.XSTD_C2=d.PP0XSDHDD_BH ";
|
|
//获取巩义销售提单明细
|
public static String GYErpXSTDMXsql="SELECT b.XSTDMX_WLBH tdmx_wlbh,c.LSWLZD_WLMC tdmx_wlmc,"
|
+ "b.XSTDMX_TDFL tdmx_tdfl,b.XSTDMX_TDLS tdmx_tdls,d.LSFZMB_MC tdmx_zpbz,"
|
+ "e.JSJLDW_DWMC tdmx_dwmx, b.XSTDMX_PCH tdmx_pch,b.XSTDMX_ZYX1 tdmx_zyx1,"
|
+ "b.XSTDMX_BHSE tdmx_bhse,b.XSTDMX_U2 tdmx_mz,b.XSTDMX_ZSL tdmx_pz,"
|
+ "b.XSTDMX_ZSL tdmx_jz,a.XSTD_SHDKHMC tdmx_shdw,f.XSTDZL_ZLMC tdmx_tdlx,"
|
+ "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,"
|
+ "i.LSFZMB_MC hj,j.LSFZMB_MC zt,h.KCHWZD_HWMC tdmx_hwbh,"
|
+ "b.XSTDMX_BL_FDDJBH tdmx_fdywbh,b.XSTDMX_BL_FDCPH tdmx_cph "
|
+ "from XSTD a WITH (NOLOCK) LEFT JOIN XSTDMX b WITH (NOLOCK) on a.XSTD_TDLS =b.XSTDMX_TDLS "
|
+ "LEFT JOIN LSWLZD c on b.XSTDMX_WLBH=c.LSWLZD_WLBH "
|
+ "left JOIN LSFZMB d on b.XSTDMX_ZYX3=d.LSFZMB_BH and d.LSFZMB_LBBH='0026' "
|
+ "left JOIN JSJLDW e on c.LSWLZD_JLDW=e.JSJLDW_DWDM "
|
+ "left join XSTDZL f on a.XSTD_ZLBH=XSTDZL_ZLBH "
|
+ "left join LSPCSX g WITH (NOLOCK) on (b.XSTDMX_WLBH=g.LSPCSX_WLBH and XSTDMX_PCH=g.LSPCSX_PCH) "
|
+ "LEFT JOIN KCHWZD h on b.XSTDMX_HWBH=h.KCHWZD_HWBH "
|
+ "left JOIN LSFZMB i on b.XSTDMX_ZYX1=i.LSFZMB_BH and i.LSFZMB_LBBH='0024' "
|
+ "left JOIN LSFZMB j on b.XSTDMX_ZYX2=j.LSFZMB_BH and j.LSFZMB_LBBH='0029' ";
|
|
public static String GYErpXSTDandXSTDMXsql="SELECT b.XSTDMX_WLBH tdmx_wlbh,c.LSWLZD_WLMC tdmx_wlmc,"
|
+ "b.XSTDMX_TDFL tdmx_tdfl,b.XSTDMX_TDLS tdmx_tdls,d.LSFZMB_MC tdmx_zpbz,"
|
+ "e.JSJLDW_DWMC tdmx_dwmx, b.XSTDMX_PCH tdmx_pch,b.XSTDMX_ZYX1 tdmx_zyx1,"
|
+ "b.XSTDMX_BHSE tdmx_bhse,b.XSTDMX_U2 tdmx_mz,b.XSTDMX_ZSL tdmx_pz,"
|
+ "b.XSTDMX_ZSL tdmx_jz,a.XSTD_SHDKHMC tdmx_shdw,f.XSTDZL_ZLMC tdmx_tdlx,"
|
+ "1 tdmx_sl,g.LSPCSX_BZ tdmx_ggxh,LTRIM(RTRIM(b.XSTDMX_BL_ZCCPH)) tdmx_sfzc,b.XSTDMX_BL_ZCCPH tdmx_zccph,"
|
+ "i.LSFZMB_MC hj,j.LSFZMB_MC zt,h.KCHWZD_HWMC tdmx_hwbh,"
|
+ "b.XSTDMX_BL_FDDJBH tdmx_fdywbh,b.XSTDMX_BL_FDCPH tdmx_cph,a.XSTD_TDBH tdmx_tdbh,"
|
+ "a.XSTD_BZ td_bz,a.XSTD_YWRQ td_ywrq,a.XSTD_ZDXM td_zdr,k.KCBMZD_BMMC td_bmmc "
|
+ "from XSTD a WITH (NOLOCK) LEFT JOIN XSTDMX b WITH (NOLOCK) on a.XSTD_TDLS =b.XSTDMX_TDLS "
|
+ "LEFT JOIN LSWLZD c on b.XSTDMX_WLBH=c.LSWLZD_WLBH "
|
+ "left JOIN LSFZMB d on b.XSTDMX_ZYX3=d.LSFZMB_BH and d.LSFZMB_LBBH='0026' "
|
+ "left JOIN JSJLDW e on c.LSWLZD_JLDW=e.JSJLDW_DWDM "
|
+ "left join XSTDZL f on a.XSTD_ZLBH=XSTDZL_ZLBH "
|
+ "left join LSPCSX g WITH (NOLOCK) on (b.XSTDMX_WLBH=g.LSPCSX_WLBH and XSTDMX_PCH=g.LSPCSX_PCH) "
|
+ "LEFT JOIN KCHWZD h on b.XSTDMX_HWBH=h.KCHWZD_HWBH "
|
+ "left JOIN LSFZMB i on b.XSTDMX_ZYX1=i.LSFZMB_BH and i.LSFZMB_LBBH='0024' "
|
+ "left JOIN LSFZMB j on b.XSTDMX_ZYX2=j.LSFZMB_BH and j.LSFZMB_LBBH='0029' "
|
+ "LEFT JOIN KCBMZD k on a.XSTD_BMBH=k.KCBMZD_BMBH ";
|
|
//修改分单明细
|
public static String XGGYErpXSTDMXsql="update XSTDMX WITH(ROWLOCK) set XSTDMX_BL_FDCPH=?,XSTDMX_BL_FDBZ=?,XSTDMX_BL_FDDJBH=? "
|
+ "from XSTDMX a WITH(ROWLOCK) left join XSTD b WITH(NOLOCK) on b.XSTD_TDLS =a.XSTDMX_TDLS ";
|
//批量提交装车修改分单明细
|
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=? "
|
+ "from XSTDMX a WITH(ROWLOCK) left join XSTD b WITH(NOLOCK) on b.XSTD_TDLS =a.XSTDMX_TDLS ";
|
public static String XGGYErpXSTDMXsql2="update XSTDMX WITH(ROWLOCK) set XSTDMX_BL_SJMC=?,XSTDMX_BL_SJTELE=?,XSTDMX_BL_WLGS=? "
|
+ "from XSTDMX a WITH(ROWLOCK) left join XSTD b WITH(NOLOCK) on b.XSTD_TDLS =a.XSTDMX_TDLS ";
|
//根据提单号汇总提单明细
|
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 ";
|
//根据销售提单修改销售提单信息
|
public static String XGGYErpXSTDsql="update XSTD WITH(ROWLOCK) set XSTD_BL_WFDSL=?,XSTD_BL_WFDMZ=? where XSTD_TDBH=?";
|
|
//关闭提单修改提单操作
|
public static String GBXSTDSsql="update XSTD WITH(ROWLOCK) set XSTD_BL_WFDSL=?,XSTD_BL_WFDMZ=?,XSTD_BL_ZCWCBZ=? where XSTD_TDBH=? ";
|
//关闭提单修改提单明细操作
|
public static String GBXSTDMXSsql="update XSTDMX WITH(ROWLOCK) "
|
+ "set XSTDMX_BL_FDCPH=?,XSTDMX_BL_FDBZ=?,XSTDMX_BL_ZCCPH=?,XSTDMX_BL_ZCBZ=?,XSTDMX_BL_SJMC=?,XSTDMX_BL_SJTELE=? "
|
+ "from XSTDMX a WITH(ROWLOCK) left join XSTD b WITH(NOLOCK) on b.XSTD_TDLS =a.XSTDMX_TDLS "
|
+ "where b.XSTD_TDBH=? and a.XSTDMX_TDFL=? ";
|
|
|
//增加
|
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=?";
|
|
//换车
|
public static String HCGYErpXSTDMXsql="UPDATE XSTDMX WITH(ROWLOCK) set XSTDMX_BL_FDCPH=? WHERE XSTDMX_BL_FDDJBH=? AND XSTDMX_BL_FDCPH=? ";
|
|
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 ";
|
|
|
//获得发货地区
|
public static String FHDQsql="SELECT PP0XSDHDD_BH bianhao,PP0XSDHDD_JS jishu,PP0XSDHDD_MC name from PP0XSDHDD WITH (NOLOCK) ";
|
//获得发货地区与物流公司关联表
|
public static String FHWLsql="SELECT XSDQWL_DQBH,XSDQWL_WLBH from XSDQWL WITH (NOLOCK) ";
|
//删除发货地区
|
public static String SCFHDQsql="delete from PP0XSDHDD ";
|
//删除物流公司与发货地区关联表
|
public static String SCFHWLsql="delete from XSDQWL ";
|
// //新增发货地区
|
public static String XZFHDQsql="insert into PP0XSDHDD (PP0XSDHDD_JS,PP0XSDHDD_MC,PP0XSDHDD_MX,PP0XSDHDD_BH) values (?,?,?,?) ";
|
// //新增物流公司合发货地区关联表
|
public static String XZFHWLsql="insert into XSDQWL (XSDQWL_DQBH,XSDQWL_WLBH) values (?,?) ";
|
//修改发货地区
|
public static String XGFHDQsql="UPDATE PP0XSDHDD set PP0XSDHDD_JS=?,PP0XSDHDD_MC=?,PP0XSDHDD_MX=? where PP0XSDHDD_BH=? ";
|
//修改发货地区是否明细字段
|
public static String XGFHDQMXsql="UPDATE PP0XSDHDD set PP0XSDHDD_MX=? where PP0XSDHDD_BH=? ";
|
}
|
