235 lines
12 KiB
C#
235 lines
12 KiB
C#
using System;
|
|
using System.Data;
|
|
using System.Configuration;
|
|
using System.Collections;
|
|
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_MISPRO.Data;
|
|
using GThinking.N_MODEL.Page;
|
|
using GThinking.N_MISPRO;
|
|
using GThinking.N_CONTROL;
|
|
using GThinking.N_MISPRO.Utility;
|
|
using System.Text;
|
|
using System.Collections.Generic;
|
|
|
|
namespace GThinking.N_XLXSBJ
|
|
{
|
|
public partial class CLWL_XLJGCZBList : SingleGridBase
|
|
{
|
|
CLWL_XLJGCZB entity = new CLWL_XLJGCZB();
|
|
|
|
protected void Page_Load(object sender, EventArgs e)
|
|
{
|
|
this.UMID = 100860013;
|
|
this.UMName = "线缆销售报价材料物料";
|
|
this.CurMaster = Master;
|
|
this.ModuleGV = ContentGrid;
|
|
this.CurEntity = entity;
|
|
this.PageHeight = 600;
|
|
this.PageWidth = 750;
|
|
base.DataKeyNames = new string[] { "JGCZBID" };
|
|
Master.EnableNewBtn = false;
|
|
Master.EnableCopyBtn = false;
|
|
Master.EnableViewBtn = false;
|
|
Master.EnableEditBtn = false;
|
|
Master.EnableDeleteBtn = false;
|
|
Master.EnableQueryBtn = false;
|
|
|
|
|
|
base.InitPage();
|
|
this.ModuleGV.OnClientDblClick = "return false;";
|
|
if (!IsPostBack)
|
|
{
|
|
Master_OnStandardQuery(null, null);
|
|
}
|
|
|
|
|
|
}
|
|
|
|
#region 模板继承事件
|
|
|
|
protected override void Master_OnStandardQuery(object sender, EventArgs e)
|
|
{
|
|
base.Master_OnStandardQuery(sender, e);
|
|
entity.EntityTable.QuerySelSql = GetQuerySql();
|
|
entity.GridDataBind(ContentGrid, tSession);
|
|
}
|
|
|
|
protected override void moduleGV_PageIndexChanging(object sender, GridViewPageEventArgs e)
|
|
{
|
|
entity.EntityTable.QuerySelSql = GetQuerySql();
|
|
ContentGrid.SelectedIndex = -1;
|
|
base.moduleGV_PageIndexChanging(sender, e);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region 自定义方法
|
|
|
|
private string GetQuerySql()
|
|
{
|
|
Dictionary<string, string> dicJGCZBID = new Dictionary<string, string>();
|
|
dicJGCZBID.Clear();
|
|
string sWLID = PageParam.GetRTrimString("WLID");
|
|
|
|
string sJGCZBID ="";
|
|
string sJGCZBBBID = "";
|
|
string sDRHH = "";
|
|
string sJGCZBXH = "";
|
|
string sCLQDL = "";
|
|
//先查询结构尺重表和结构尺重表ID----
|
|
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
|
TSQL SQL = new TSQL(dberp);
|
|
SQL.Clear();
|
|
|
|
SQL.Add(" select a.JGCZBID, a.JGCZBBBID, a.DRHH, a.JGCZBXH from ( ");
|
|
SQL.Add(" select XLXSCLQDWL_JGCZBID as JGCZBID,XLXSCLQDWL_JGCZBBBID as JGCZBBBID,XLXSCLQDWL_DRHH as DRHH,XLXSCLQDWL_JGCZBXH as JGCZBXH FROM XLXSCLQDWL where 1 =1 and ( ");
|
|
for (int i = 1; i <= 80; i++)
|
|
{
|
|
SQL.Add(" ISNULL(XLXSCLQDWL_WLID" + i.ToString().PadLeft(2, '0') + ",' ') = :pWLID ");
|
|
if (i != 80)
|
|
{
|
|
SQL.Add(" or ");
|
|
}
|
|
}
|
|
SQL.Add(" ) group by XLXSCLQDWL_JGCZBID ,XLXSCLQDWL_JGCZBBBID,XLXSCLQDWL_DRHH,XLXSCLQDWL_JGCZBXH ");
|
|
SQL.Add(") a order by JGCZBID, JGCZBBBID, DRHH ");
|
|
SQL.ParamByName("pWLID").Value = sWLID;
|
|
DataSet dsXLJGCZBID = dberp.OpenDataSet(SQL);
|
|
if (dsXLJGCZBID != null && dsXLJGCZBID.Tables.Count > 0 && dsXLJGCZBID.Tables[0].Rows.Count > 0)
|
|
{
|
|
for (int i = 0; i < dsXLJGCZBID.Tables[0].Rows.Count; i++)
|
|
{
|
|
sJGCZBID = dsXLJGCZBID.Tables[0].Rows[i]["JGCZBID"].ToString().Trim();
|
|
sJGCZBBBID = dsXLJGCZBID.Tables[0].Rows[i]["JGCZBBBID"].ToString().Trim();
|
|
sDRHH = dsXLJGCZBID.Tables[0].Rows[i]["DRHH"].ToString().Trim();
|
|
sJGCZBXH = dsXLJGCZBID.Tables[0].Rows[i]["JGCZBXH"].ToString().Trim();
|
|
//再将数据反查对应的XLXSCLQDWL对应的WLIDXX，将数据存储在dic中
|
|
SQL.Clear();
|
|
|
|
SQL.Add(" SELECT ");
|
|
for (int j = 1; j <= 80; j++)
|
|
{
|
|
if (j != 80)
|
|
{
|
|
SQL.Add(" ISNULL(XLXSCLQDWL_WLID" + j.ToString().PadLeft(2, '0') + ",' '), ");
|
|
}
|
|
else
|
|
{
|
|
SQL.Add(" ISNULL(XLXSCLQDWL_WLID" + j.ToString().PadLeft(2, '0') + ",' ') ");
|
|
}
|
|
}
|
|
SQL.Add(" FROM XLXSCLQDWL WHERE XLXSCLQDWL_JGCZBID=:pJGCZBID and XLXSCLQDWL_JGCZBBBID = :pJGCZBBBID ");
|
|
SQL.Add(" and XLXSCLQDWL_DRHH =:pDRHH and XLXSCLQDWL_JGCZBXH =:pJGCZBXH ");
|
|
SQL.ParamByName("pJGCZBID").Value = sJGCZBID;
|
|
SQL.ParamByName("pJGCZBBBID").Value = sJGCZBBBID;
|
|
SQL.ParamByName("pDRHH").Value = sDRHH;
|
|
SQL.ParamByName("pJGCZBXH").Value = sJGCZBXH;
|
|
DataSet dsWLID = dberp.OpenDataSet(SQL);
|
|
if (dsWLID != null && dsWLID.Tables.Count > 0 && dsWLID.Tables[0].Rows.Count > 0)
|
|
{
|
|
for (int k = 1; k <= dsWLID.Tables[0].Columns.Count; k++)
|
|
{
|
|
if (dsWLID.Tables[0].Rows[0][k - 1].ToString().Trim().Equals(sWLID))
|
|
{
|
|
sCLQDL = sCLQDL + k.ToString().PadLeft(2, '0') + ",";
|
|
}
|
|
}
|
|
sCLQDL = sCLQDL.TrimStart(',').TrimEnd(',');
|
|
sCLQDL = sCLQDL.Replace(",", "列,第");
|
|
sCLQDL = "第" + sCLQDL + "列";
|
|
if (!dicJGCZBID.ContainsKey(sJGCZBID + "|" + sJGCZBBBID))
|
|
{
|
|
dicJGCZBID.Add(sJGCZBID + "|" + sJGCZBBBID, sCLQDL);
|
|
}
|
|
sCLQDL = "";
|
|
}
|
|
}
|
|
}
|
|
|
|
//dic 【B0003|1.0】【第01行,第02行,第03行】
|
|
|
|
//查询XLXSCLQDWL数据，关联XLJGCZBLM，将上述dic放入限制条件。
|
|
StringBuilder sSQL = new StringBuilder();
|
|
sSQL.AppendLine(" select a.JGCZBID, a.JGCZBBBID, a.JGCZBXH, a.DRHH,b.CZBLMBH,b.CZBLMMC,b.JGCZBMC,");
|
|
sSQL.AppendLine(" b.CLQDL,b.XLCLZLID,b.XLJGCZBLM_XLCLID,b.BJCL ");
|
|
sSQL.AppendLine(" from ( ");
|
|
sSQL.AppendLine(" select XLXSCLQDWL_JGCZBID as JGCZBID,XLXSCLQDWL_JGCZBBBID as JGCZBBBID,XLXSCLQDWL_DRHH as DRHH,XLXSCLQDWL_JGCZBXH as JGCZBXH FROM XLXSCLQDWL where 1 =1 and ( ");
|
|
for (int i = 1; i <= 80; i++)
|
|
{
|
|
sSQL.AppendLine(" ISNULL(XLXSCLQDWL_WLID" + i.ToString().PadLeft(2, '0') + ",' ') = :pWLID ");
|
|
if (i != 80)
|
|
{
|
|
sSQL.AppendLine(" or ");
|
|
}
|
|
}
|
|
sSQL.AppendLine(" ) group by XLXSCLQDWL_JGCZBID ,XLXSCLQDWL_JGCZBBBID,XLXSCLQDWL_DRHH,XLXSCLQDWL_JGCZBXH ");
|
|
entity.Param.Add("pWLID", sWLID);
|
|
sSQL.AppendLine(" ) a left join ");
|
|
sSQL.AppendLine(" ( ");
|
|
sSQL.AppendLine(" select XLJGCZBLM_JGCZBID as JGCZBID, XLJGCZBML_JGCZBMC as JGCZBMC,XLJGCZBLM_JGCZBBBID AS JGCZBBBID, ");
|
|
sSQL.AppendLine(" XLJGCZBLM_CZBLMBH AS CZBLMBH,XLJGCZBLM_CZBLMMC AS CZBLMMC,XLJGCZBLM_CLQDL AS CLQDL, ");
|
|
sSQL.AppendLine(" XLJGCZBLM_XLCLZLID AS XLCLZLID,rtrim(XLJGCZBLM_XLCLID)||'|'||rtrim(CL.XLCLMC) as XLJGCZBLM_XLCLID,XLJGCZBLM_XLCLID AS XLCLID, ");
|
|
sSQL.AppendLine(" case isnull(XLJGCZBLM_BJCLID,' ') when ' ' then ' ' else rtrim(XLJGCZBLM_BJCLID)||'|'||XLXSBJCL_BJCLMC end as BJCL ");
|
|
sSQL.AppendLine(" from XLJGCZBLM ");
|
|
sSQL.AppendLine(" left join XLXSBJCL on XLXSBJCL_BJCLID=XLJGCZBLM_BJCLID ");
|
|
sSQL.AppendLine(" left join XLJGCZBML on XLJGCZBML_JGCZBID=XLJGCZBLM_JGCZBID ");
|
|
|
|
sSQL.AppendLine(" left join ( ");
|
|
sSQL.AppendLine(" select 'DTCL' as XLCLZLID,XLDTCLB_DTCLID as XLCLID,XLDTCLB_XLXTCLID as XLXTCLID,XLDTCLB_WLMC as XLCLMC from XLDTCLB where XLDTCLB_WLID=' ' ");
|
|
sSQL.AppendLine(" union all ");
|
|
sSQL.AppendLine(" select 'JYCL' as XLCLZLID,XLJYCLB_JYCLID as XLCLID,XLJYCLB_XLXTCLID as XLXTCLID,XLJYCLB_WLMC as XLCLMC from XLJYCLB where XLJYCLB_WLID=' ' ");
|
|
sSQL.AppendLine(" union all ");
|
|
sSQL.AppendLine(" select 'HTCL' as XLCLZLID,XLHTCLB_HTCLID as XLCLID,XLHTCLB_XLXTCLID as XLXTCLID,XLHTCLB_WLMC as XLCLMC from XLHTCLB where XLHTCLB_WLID=' ' ");
|
|
sSQL.AppendLine(" union all ");
|
|
sSQL.AppendLine(" select 'KZCL' as XLCLZLID,XLKZCLB_KZCLID as XLCLID,XLKZCLB_XLXTCLID as XLXTCLID,XLKZCLB_WLMC as XLCLMC from XLKZCLB where XLKZCLB_WLID=' ' ");
|
|
sSQL.AppendLine(" union all ");
|
|
sSQL.AppendLine(" select 'WHCL' as XLCLZLID,XLWHCLB_WHCLID as XLCLID,XLWHCLB_XLXTCLID as XLXTCLID,XLWHCLB_WLMC as XLCLMC from XLWHCLB where XLWHCLB_WLID=' ' ");
|
|
sSQL.AppendLine(" union all ");
|
|
sSQL.AppendLine(" select 'PBCL' as XLCLZLID,XLPBCLB_PBCLID as XLCLID,XLPBCLB_XLXTCLID as XLXTCLID,XLPBCLB_WLMC as XLCLMC from XLPBCLB where XLPBCLB_WLID=' ' ");
|
|
sSQL.AppendLine(" union all ");
|
|
sSQL.AppendLine(" select 'TCCL' as XLCLZLID,XLTCCLB_TCCLID as XLCLID,XLTCCLB_XLXTCLID as XLXTCLID,XLTCCLB_WLMC as XLCLMC from XLTCCLB where XLTCCLB_WLID=' ' ");
|
|
sSQL.AppendLine(" union all ");
|
|
sSQL.AppendLine(" select 'RBCL' as XLCLZLID,XLRBCLB_RBCLID as XLCLID,XLRBCLB_XLXTCLID as XLXTCLID,XLRBCLB_WLMC as XLCLMC from XLRBCLB where XLRBCLB_WLID=' ' ");
|
|
sSQL.AppendLine(" union all ");
|
|
sSQL.AppendLine(" select 'FZCL' as XLCLZLID,XLFZCLB_FZCLID as XLCLID,XLFZCLB_XLXTCLID as XLXTCLID,XLFZCLB_WLMC as XLCLMC from XLFZCLB where XLFZCLB_WLID=' ' ");
|
|
sSQL.AppendLine(" ) CL on CL.XLCLZLID=case when XLJGCZBLM_XLCLZLID='DTCP' then 'DTCL' else XLJGCZBLM_XLCLZLID end and CL.XLCLID = XLJGCZBLM_XLCLID ");
|
|
sSQL.AppendLine(" where 1 = 1 ");
|
|
/*
|
|
* 限制行栏目类型为用量栏目
|
|
* 限制物料行情
|
|
* 限制材料清单列不允许为空
|
|
*/
|
|
sSQL.AppendLine(" and isnull(XLJGCZBLM_CLQDL,' ')<>' ' ");
|
|
sSQL.AppendLine(" and XLJGCZBLM_ZDLMLX='4' ");
|
|
sSQL.AppendLine(" and XLXSBJCL_BJHQ = '2' ");
|
|
sSQL.AppendLine(" and (1<>1 ");
|
|
int index = 1;
|
|
foreach (KeyValuePair<string, string> kvp in dicJGCZBID)
|
|
{
|
|
sSQL.AppendLine(" or (XLJGCZBLM_JGCZBID = :pJGCZBID" + index.ToString().Trim() + " and XLJGCZBLM_JGCZBBBID =:pJGCZBBBID" + index.ToString().Trim());
|
|
sSQL.AppendLine(" and " + CMISproDataBase.GetWhereEqual("XLJGCZBLM_CLQDL", kvp.Value));
|
|
sSQL.AppendLine(" ) ");
|
|
entity.Param.Add("pJGCZBID" + index.ToString().Trim(), kvp.Key.Split('|')[0]);
|
|
entity.Param.Add("pJGCZBBBID" + index.ToString().Trim(), kvp.Key.Split('|')[1]);
|
|
index++;
|
|
}
|
|
sSQL.AppendLine(" ) ");
|
|
sSQL.AppendLine(" ) b on b.JGCZBID = a.JGCZBID and b.JGCZBBBID = a.JGCZBBBID ");
|
|
|
|
sSQL.AppendLine(" order by JGCZBID,JGCZBBBID,JGCZBXH, DRHH,CZBLMBH ");
|
|
return sSQL.ToString();
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region 控件事件
|
|
|
|
#endregion
|
|
}
|
|
}
|