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=? ";
}