270 lines
13 KiB
C#
270 lines
13 KiB
C#
using System;
|
||
using System.Data;
|
||
using System.Configuration;
|
||
using System.Web;
|
||
using System.Web.Security;
|
||
using System.Web.UI;
|
||
using System.Web.UI.WebControls;
|
||
using System.Web.UI.WebControls.WebParts;
|
||
using System.Web.UI.HtmlControls;
|
||
using GThinking.N_MODEL.Page;
|
||
using GThinking.N_MISPRO.Data;
|
||
using GThinking.N_MISPRO.Utility;
|
||
|
||
namespace GThinking.N_XLXSBJ
|
||
{
|
||
[EntityType(Db = "DBERP", Table = "XLWBBJDMX", IsWf = false, IsWhr = false, WhrPrefix = "", IsAttach = false,
|
||
DocGrpId = "", DocFjs = "")]
|
||
public class XLBJD_WBBJDMXEntity : EntityObject
|
||
{
|
||
#region 继承事件
|
||
|
||
public string sWBBJDID = "";
|
||
public string sBJDID = " ";
|
||
public string sTargetBJDID = "";
|
||
public string sJGBID = "";
|
||
public string sJGFID = "";
|
||
public string sJGCIDS = "";
|
||
|
||
//查询相关
|
||
public string sXLXHID = "";
|
||
public string sXLBZID = "";
|
||
public string sDYDJID = "";
|
||
public string sXLYSID = "";
|
||
public string sJLDW = "";
|
||
public string sXD = "";
|
||
public string sWG = "";
|
||
//
|
||
public string sBJCP = "";
|
||
public string sZHCPML = "";
|
||
public string sBHFCX = "";
|
||
//关联内容列展示原始信息
|
||
public string sLMNR = "";
|
||
public override TSQL SetQuerySql()
|
||
{
|
||
TSQL sql = base.SetQuerySql();
|
||
sql.Add(" select XLWBBJDMX_DRHH,XLWBBJDMX_BJCP, XLWBBJDMX_WLID, XLWBBJDMX_XLXHID,XLWBBJDMX_XLGGID, ");
|
||
if (!sLMNR.Equals(""))
|
||
{
|
||
sql.Add(String.Format(" XLWBBJDNR_LMNR{0} as XLWBBJDNR_LMNR, ", sLMNR.PadLeft(2, '0')));
|
||
}
|
||
sql.Add(" RTRIM(XLWBBJDMX_XLBZID)||'|'||RTRIM(XLBZ_XLBZLXMC) AS XLWBBJDMX_XLBZID,XLWBBJDMX_DYDJID,RTRIM(XLWBBJDMX_XLYSID)||'|'||RTRIM(XLYSB_XLYSMC) AS XLWBBJDMX_XLYSID, XLWBBJDMX_JLDW,XLWBBJDMX_BJSL,XLWBBJDMX_CPBJ,XLWBBJDMX_YXJG,XLWBBJDMX_YSCP,XLWBBJDMX_KHWLID,XLWBBJDMX_YZ, ");
|
||
sql.Add(" XLWBBJDMX_FBH,XLWBBJDMX_CPBJXJ,XLWBBJDMX_BJJEXJ,XLWBBJDMX_WGCPBZ,XLWBBJDMX_JMBGS,XLWBBJDMX_LSJGBBJ,XLWBBJDMX_BZ,XLWBBJDMX_XDBZ, ");
|
||
if (XLBJDUtils.IsJGEJGB(sJGBID))
|
||
{
|
||
sql.Add(" case when ( isnull(XLXSCLQD_WPID,' ') <> 'ZZZZ' ");
|
||
}
|
||
else
|
||
{
|
||
sql.Add(" case when ( isnull(XLXSJGBMX_JGBID,' ') = ' ' ");
|
||
}
|
||
if (!sJGFID.Equals(""))
|
||
{
|
||
sql.Add(" and not exists (select 1 from XLXSJGBMX JGBMX WHERE JGBMX.XLXSJGBMX_JGBID = '" + sJGFID + "' and JGBMX.XLXSJGBMX_XLXHID=XLWBBJDMX_XLXHID and JGBMX.XLXSJGBMX_XLGGID = XLWBBJDMX_XLGGID ");
|
||
sql.Add(" and JGBMX.XLXSJGBMX_XLBZID=isnull(XLWBBJDMX_XLBZID,' ') and JGBMX.XLXSJGBMX_DYDJID=isnull(XLWBBJDMX_DYDJID,' '))");
|
||
}
|
||
sql.Add(" ) then 'Y' else 'N' end as CLQWSC,");
|
||
sql.Add(" case when isnull(XLWBBJDMX_XLXHID,' ') = ' ' then 'N' when isnull(XLXHB_XLXHID,' ') = ' ' then 'Y' else 'N' end as XHBHG,");
|
||
sql.Add(" case when isnull(XLWBBJDMX_XLGGID,' ') = ' ' then 'N' when isnull(XLGGB_XLGGID,' ') = ' ' then 'Y' else 'N' end as GGBHG,");
|
||
sql.Add(" case when isnull(XLWBBJDMX_DYDJID,' ') = ' ' then 'N' when isnull(XLDYDJB_DYDJID,' ') = ' ' then 'Y' else 'N' end as DYDJBHG ");
|
||
sql.Add(" ,isnull(XLWBBJDMX_ZHCPMLMC,' ') as XLWBBJDMX_ZHCPMLMC,isnull(XLWBBJDMX_CPMLMC,' ') as XLWBBJDMX_CPMLMC,XLWBBJDMX_PWQDID,XLWBBJDMX_PWQDWLMC,XLWBBJDMX_PWQDMS,XLWBBJDMX_GWWLBM ");
|
||
if (XLWBBJDUtils.GetWBBJDDRYZList(sTargetBJDID).Length > 0)
|
||
{
|
||
sql.Add(" ,XLWBBJDMXYZZ_YZZ01,XLWBBJDMXYZZ_YZZ02,XLWBBJDMXYZZ_YZZ03,XLWBBJDMXYZZ_YZZ04,XLWBBJDMXYZZ_YZZ05 ");
|
||
sql.Add(" ,XLWBBJDMXYZZ_YZZ06,XLWBBJDMXYZZ_YZZ07,XLWBBJDMXYZZ_YZZ08,XLWBBJDMXYZZ_YZZ09,XLWBBJDMXYZZ_YZZ10 ");
|
||
sql.Add(" ,XLWBBJDMXYZZ_YZZ11,XLWBBJDMXYZZ_YZZ12,XLWBBJDMXYZZ_YZZ13,XLWBBJDMXYZZ_YZZ14,XLWBBJDMXYZZ_YZZ15 ");
|
||
sql.Add(" ,XLWBBJDMXYZZ_YZZ16,XLWBBJDMXYZZ_YZZ17,XLWBBJDMXYZZ_YZZ18,XLWBBJDMXYZZ_YZZ19,XLWBBJDMXYZZ_YZZ20 ");
|
||
}
|
||
else
|
||
{
|
||
for (int i = 1; i <= 20; i++)
|
||
sql.Add(string.Format(" ,0.0 as XLWBBJDMXYZZ_YZZ{0}",i.ToString().PadLeft(2,'0')));
|
||
}
|
||
sql.Add(" from XLWBBJDMX ");
|
||
if (XLBJDUtils.IsJGEJGB(sJGBID))
|
||
{
|
||
sql.Add(" left join XLXSCLQD on XLXSCLQD_XLXHID=XLWBBJDMX_XLXHID and XLXSCLQD_XLGGID=XLWBBJDMX_XLGGID and XLXSCLQD_XLBZID=XLWBBJDMX_XLBZID and XLXSCLQD_DYDJID=isnull(XLWBBJDMX_DYDJID,' ') ");
|
||
}
|
||
sql.Add(" left join XLXHB on XLXHB_XLXHID=XLWBBJDMX_XLXHID ");
|
||
sql.Add(" left join XLXSBJXLGGTHB XLXSBJXLGGTHB_GG on XLXSBJXLGGTHB_GG.XLXSBJXLGGTHB_XLGGID = XLWBBJDMX_XLGGID ");
|
||
sql.Add(" left join XLGGB on XLGGB_XLGGID=isnull(XLXSBJXLGGTHB_GG.XLXSBJXLGGTHB_XLGGIDCH,' ') and XLGGB_XLGGJDGZ = isnull(XLXHB_XLGGJDGZ,'0') ");
|
||
sql.Add(" left join XLBZ on XLBZ_XLBZID=XLWBBJDMX_XLBZID ");
|
||
sql.Add(" left join XLDYDJB on XLDYDJB_DYDJID=XLWBBJDMX_DYDJID ");
|
||
sql.Add(" left join XLYSB on XLYSB_XLYSID=XLWBBJDMX_XLYSID ");
|
||
if (!XLBJDUtils.IsJGEJGB(sJGBID))
|
||
{
|
||
if (sJGCIDS.Equals(""))
|
||
{
|
||
sql.Add(" left join XLXSJGBMX on XLXSJGBMX_JGBID = '" + sJGBID + "' and XLXSJGBMX_XLXHID=XLWBBJDMX_XLXHID and XLXSJGBMX_XLGGID = XLWBBJDMX_XLGGID ");
|
||
}
|
||
else
|
||
{
|
||
sql.Add(" left join XLXSJGBMX on " + CMISproDataBase.GetWhereEqual("XLXSJGBMX_JGBID", sJGCIDS) + " and XLXSJGBMX_XLXHID=XLWBBJDMX_XLXHID and XLXSJGBMX_XLGGID = XLWBBJDMX_XLGGID ");
|
||
}
|
||
sql.Add(" and XLXSJGBMX_XLBZID=isnull(XLWBBJDMX_XLBZID,' ') and XLXSJGBMX_DYDJID=isnull(XLWBBJDMX_DYDJID,' ') ");
|
||
}
|
||
if (!sLMNR.Equals(""))
|
||
{
|
||
sql.Add(" left join XLWBBJDNR on XLWBBJDNR_BJDID = XLWBBJDMX_BJDID and XLWBBJDNR_WBBJDID = XLWBBJDMX_WBBJDID and XLWBBJDNR_DRHH = XLWBBJDMX_DRHH ");
|
||
}
|
||
if (XLWBBJDUtils.GetWBBJDDRYZList(sTargetBJDID).Length > 0)
|
||
{
|
||
sql.Add(" left join XLWBBJDMXYZZ on XLWBBJDMXYZZ_WBBJDID=XLWBBJDMX_WBBJDID ");
|
||
sql.Add(" and XLWBBJDMXYZZ_DRHH=XLWBBJDMX_DRHH and XLWBBJDMXYZZ_BJDID=XLWBBJDMX_BJDID ");
|
||
}
|
||
sql.Add(" where 1 = 1 ");
|
||
|
||
if (!sWBBJDID.Equals(""))
|
||
{
|
||
sql.Add(" and XLWBBJDMX_WBBJDID =:XLWBBJDMX_WBBJDID and XLWBBJDMX_BJDID=:XLWBBJDMX_BJDID ");
|
||
sql.ParamByName("XLWBBJDMX_WBBJDID").Value = sWBBJDID;
|
||
sql.ParamByName("XLWBBJDMX_BJDID").Value = sBJDID;
|
||
}
|
||
else
|
||
{
|
||
sql.Add(" and 1 <> 1 ");
|
||
}
|
||
|
||
//型号
|
||
if (!String.IsNullOrEmpty(sXLXHID))
|
||
{
|
||
if (sXLXHID.Equals("空"))
|
||
{
|
||
sql.Add(" and isnull(XLWBBJDMX_XLXHID,' ') = ' ' ");
|
||
}
|
||
else
|
||
{
|
||
sql.Add(" and XLWBBJDMX_XLXHID = :XLWBBJDMX_XLXHID");
|
||
sql.ParamByName("XLWBBJDMX_XLXHID").Value = sXLXHID;
|
||
}
|
||
}
|
||
|
||
//标准
|
||
if (!String.IsNullOrEmpty(sXLBZID))
|
||
{
|
||
if (sXLBZID.Equals("空"))
|
||
{
|
||
sql.Add(" and isnull(XLWBBJDMX_XLBZID,' ') = ' ' ");
|
||
}
|
||
else
|
||
{
|
||
sql.Add(" and XLWBBJDMX_XLBZID = :XLWBBJDMX_XLBZID");
|
||
sql.ParamByName("XLWBBJDMX_XLBZID").Value = sXLBZID;
|
||
}
|
||
}
|
||
|
||
//电压等级
|
||
if (!String.IsNullOrEmpty(sDYDJID))
|
||
{
|
||
if (sDYDJID.Equals("空"))
|
||
{
|
||
sql.Add(" and isnull(XLWBBJDMX_DYDJID,' ') = ' ' ");
|
||
}
|
||
else
|
||
{
|
||
sql.Add(" and XLWBBJDMX_DYDJID = :XLWBBJDMX_DYDJID");
|
||
sql.ParamByName("XLWBBJDMX_DYDJID").Value = sDYDJID;
|
||
}
|
||
}
|
||
|
||
//颜色
|
||
if (!String.IsNullOrEmpty(sXLYSID))
|
||
{
|
||
sql.Add(" and XLWBBJDMX_XLYSID = :XLWBBJDMX_XLYSID");
|
||
sql.ParamByName("XLWBBJDMX_XLYSID").Value = sXLYSID;
|
||
}
|
||
|
||
//计量单位
|
||
if (!String.IsNullOrEmpty(sJLDW))
|
||
{
|
||
sql.Add(" and XLWBBJDMX_JLDW = :XLWBBJDMX_JLDW");
|
||
sql.ParamByName("XLWBBJDMX_JLDW").Value = sJLDW;
|
||
}
|
||
|
||
//修订
|
||
if (!String.IsNullOrEmpty(sXD))
|
||
{
|
||
sql.Add(" and XLWBBJDMX_XDBZ = :XLWBBJDMX_XDBZ");
|
||
sql.ParamByName("XLWBBJDMX_XDBZ").Value = sXD;
|
||
}
|
||
|
||
//外购
|
||
if (!String.IsNullOrEmpty(sWG))
|
||
{
|
||
sql.Add(" and XLWBBJDMX_WGCPBZ = :XLWBBJDMX_WGCPBZ");
|
||
sql.ParamByName("XLWBBJDMX_WGCPBZ").Value = sWG;
|
||
}
|
||
|
||
|
||
//报价产品
|
||
if (!String.IsNullOrEmpty(sBJCP))
|
||
{
|
||
sql.Add(" and replace(XLWBBJDMX_BJCP,'*','×') like :XLWBBJDMX_BJCP");
|
||
sql.ParamByName("XLWBBJDMX_BJCP").Value = "%" + sBJCP.Replace('*', '×') + "%";
|
||
}
|
||
|
||
if (!String.IsNullOrEmpty(sZHCPML)&&XLBJDUtils.GetXLXSBJPZXX(100860000).XLXSBJPZ_XSCPML == "Z")
|
||
{
|
||
sql.Add(" and XLWBBJDMX_ZHCPMLMC like :ZHCPML ");
|
||
sql.ParamByName("ZHCPML").Value = "%" + sZHCPML + "%";
|
||
}
|
||
|
||
//"型号不合规", "1"
|
||
//"规格不合规", "2"
|
||
//"电压等级不合规", "3"
|
||
//"型号&规格&电压等级不合规", "4"
|
||
//"材料清单未生成", "5"
|
||
if (!String.IsNullOrEmpty(sBHFCX))
|
||
{
|
||
if (sBHFCX.Equals("1"))
|
||
{
|
||
sql.Add(" and not exists (select 1 from XLXHB WHERE XLXHB_XLXHID = XLWBBJDMX_XLXHID) ");
|
||
}
|
||
else if (sBHFCX.Equals("2"))
|
||
{
|
||
sql.Add(" and not exists (select XLXSBJXLGGTHB_BHG2.XLXSBJXLGGTHB_XLGGID from XLXSBJXLGGTHB XLXSBJXLGGTHB_BHG2 ");
|
||
sql.Add(" left join XLGGB on XLGGB_XLGGID = XLXSBJXLGGTHB_BHG2.XLXSBJXLGGTHB_XLGGIDCH ");
|
||
sql.Add(" where XLXSBJXLGGTHB_BHG2.XLXSBJXLGGTHB_XLGGID = XLWBBJDMX_XLGGID ");
|
||
sql.Add(" and isnull(XLGGB.XLGGB_XLGGID,' ')<>' ') ");
|
||
}
|
||
else if (sBHFCX.Equals("3"))
|
||
{
|
||
sql.Add(" and not exists (select 1 from XLDYDJB WHERE XLDYDJB_DYDJID = isnull(XLWBBJDMX_DYDJID,' ')) ");
|
||
}
|
||
else if (sBHFCX.Equals("4"))
|
||
{
|
||
sql.Add(" and ( not exists (select 1 from XLXHB WHERE XLXHB_XLXHID = XLWBBJDMX_XLXHID) ");
|
||
sql.Add(" or not exists (select XLXSBJXLGGTHB_BHG4.XLXSBJXLGGTHB_XLGGID from XLXSBJXLGGTHB XLXSBJXLGGTHB_BHG4 ");
|
||
sql.Add(" left join XLGGB on XLGGB_XLGGID = XLXSBJXLGGTHB_BHG4.XLXSBJXLGGTHB_XLGGIDCH ");
|
||
sql.Add(" where XLXSBJXLGGTHB_BHG4.XLXSBJXLGGTHB_XLGGID = XLWBBJDMX_XLGGID ");
|
||
sql.Add(" and isnull(XLGGB.XLGGB_XLGGID,' ')<>' ') ");
|
||
sql.Add(" or not exists (select 1 from XLDYDJB WHERE XLDYDJB_DYDJID = isnull(XLWBBJDMX_DYDJID,' ')) ) ");
|
||
}
|
||
else if (sBHFCX.Equals("5"))
|
||
{
|
||
if (XLBJDUtils.IsJGEJGB(sJGBID))
|
||
sql.Add(" and not exists (select 1 from XLXSCLQD WHERE XLXSCLQD_WPID='ZZZZ' and XLXSCLQD_XLXHID=XLWBBJDMX_XLXHID and XLXSCLQD_XLGGID = XLWBBJDMX_XLGGID and XLXSCLQD_XLBZID=isnull(XLWBBJDMX_XLBZID,' ') and XLXSCLQD_DYDJID=isnull(XLWBBJDMX_DYDJID,' ')) ");
|
||
else
|
||
{
|
||
if (sJGCIDS.Equals(""))
|
||
{
|
||
sql.Add(" and not exists (select 1 from XLXSJGBMX WHERE XLXSJGBMX_JGBID = '" + sJGBID + "' and XLXSJGBMX_XLXHID=XLWBBJDMX_XLXHID and XLXSJGBMX_XLGGID = XLWBBJDMX_XLGGID and XLXSJGBMX_XLBZID=isnull(XLWBBJDMX_XLBZID,' ') and XLXSJGBMX_DYDJID=isnull(XLWBBJDMX_DYDJID,' ')) ");
|
||
}
|
||
else
|
||
{
|
||
sql.Add(" and not exists (select 1 from XLXSJGBMX WHERE " + CMISproDataBase.GetWhereEqual("XLXSJGBMX_JGBID", sJGCIDS) + " and XLXSJGBMX_XLXHID=XLWBBJDMX_XLXHID and XLXSJGBMX_XLGGID = XLWBBJDMX_XLGGID and XLXSJGBMX_XLBZID=isnull(XLWBBJDMX_XLBZID,' ') and XLXSJGBMX_DYDJID=isnull(XLWBBJDMX_DYDJID,' ')) ");
|
||
}
|
||
}
|
||
if (!sJGFID.Equals(""))
|
||
sql.Add(" and not exists (select 1 from XLXSJGBMX WHERE XLXSJGBMX_JGBID = '" + sJGFID + "' and XLXSJGBMX_XLXHID=XLWBBJDMX_XLXHID and XLXSJGBMX_XLGGID = XLWBBJDMX_XLGGID and XLXSJGBMX_XLBZID=isnull(XLWBBJDMX_XLBZID,' ') and XLXSJGBMX_DYDJID=isnull(XLWBBJDMX_DYDJID,' ')) ");
|
||
}
|
||
}
|
||
sql.Add("order by XLWBBJDMX_DRHH ");
|
||
string s = sql.Text;
|
||
return sql;
|
||
}
|
||
#endregion
|
||
|
||
}
|
||
}
|