1375 lines
70 KiB
C#
1375 lines
70 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_MODEL.Page;
|
||
using GThinking.N_MISPRO.Utility;
|
||
using GThinking.N_MISPRO.Data;
|
||
using GThinking.N_MISPRO;
|
||
using GThinking.N_WORKFLOW;
|
||
using System.Collections.Generic;
|
||
using GThinking.N_CONTROL;
|
||
|
||
namespace GThinking.N_XLXSBJ
|
||
{
|
||
public partial class CLQDList : SingleGridBase
|
||
{
|
||
CLQD entity = new CLQD();
|
||
CLQD_M queryentity = new CLQD_M();
|
||
Dictionary<string, string> dicHeaderText = new Dictionary<string, string>();
|
||
Dictionary<string, string> dicHeaderTextCLWL = new Dictionary<string, string>();
|
||
#region Page类事件继承
|
||
|
||
protected void Page_Load(object sender, EventArgs e)
|
||
{
|
||
this.UMID = 100860002;
|
||
this.UMName = "线缆销售报价材料清单";
|
||
this.CurMaster = Master;
|
||
this.ModuleGV = ContentGrid;
|
||
this.CurEntity = entity;
|
||
this.CurQueryEntity = queryentity;
|
||
this.PageHeight = 600;
|
||
this.PageWidth = 750;
|
||
this.sURLTarget = "CLQDInfo.aspx";
|
||
this.sQueryURLTarget = "CLQDQuery.aspx";
|
||
base.DataKeyNames = new string[] { "XLXSCLQD_XLXHID", "XLXSCLQD_XLGGID", "XLXSCLQD_XLBZID", "XLXSCLQD_DYDJID" };
|
||
|
||
//if (GetOption("BJU003").Equals("M"))
|
||
// ContentGrid.FindColumnExByFieldName("XLXSCLQD_CLCB").NumberDecimalPlace = 5;
|
||
|
||
ContentGrid.FindColumnByFieldName("XLXSCLQD_CLZL").HeaderText = "材料重量(Kg/" + GetOption("BJU003") + ")";
|
||
|
||
this.Master.EnableAttachBtn = false;
|
||
Master.EnableNewBtn = false;
|
||
Master.EnableCopyBtn = false;
|
||
Master.EnableEditBtn = false;
|
||
|
||
Server.ScriptTimeout = 3600;
|
||
CanDeleteCheckedRows = true;
|
||
if (!IsPostBack)
|
||
{
|
||
//从结构尺重表模块跳转
|
||
if (!PageParam.GetRTrimString("JGCZBIDFromJGCZBML").Equals(""))
|
||
{
|
||
CurQueryEntity.FieldByName("JGCZBID").Value = PageParam.GetRTrimString("JGCZBIDFromJGCZBML");
|
||
CurQueryEntity.FieldByName("JGCZBBBID").Value = PageParam.GetRTrimString("JGCZBBBIDFromJGCZBML");
|
||
|
||
}
|
||
CurQueryEntity.FieldByName("PXFS").Value = "型号+序号";
|
||
|
||
}
|
||
|
||
base.InitPage();
|
||
Server.ScriptTimeout = 3600;
|
||
|
||
if (!IsPostBack)
|
||
{
|
||
if (!PageParam.GetRTrimString("JGCZBIDFromJGCZBML").Equals(""))
|
||
{
|
||
Master_OnStandardQuery(null, null);
|
||
}
|
||
else
|
||
{
|
||
if (IsDefaultExecute)
|
||
Master_OnStandardQuery(null, null);
|
||
else
|
||
{
|
||
IfExistMC_Get();
|
||
initGrid();
|
||
}
|
||
}
|
||
}
|
||
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 模板继承事件
|
||
|
||
protected override bool Master_BeforeDeleteAction(object sender, EventArgs e)
|
||
{
|
||
string sSQL = "";
|
||
sSQL = string.Format("select top 1 XLXSJGBMX_JGBID from XLXSJGBMX where XLXSJGBMX_XLXHID='{0}' and XLXSJGBMX_XLGGID='{1}' and XLXSJGBMX_XLBZID='{2}' and isnull(XLXSJGBMX_DYDJID,' ')='{3}'",
|
||
ContentGrid.DataKeys[ContentGrid.SelectedIndex]["XLXSCLQD_XLXHID"].ToString().Trim(),
|
||
ContentGrid.DataKeys[ContentGrid.SelectedIndex]["XLXSCLQD_XLGGID"].ToString().Trim(), ContentGrid.DataKeys[ContentGrid.SelectedIndex]["XLXSCLQD_XLBZID"].ToString().Trim(),
|
||
ContentGrid.DataKeys[ContentGrid.SelectedIndex]["XLXSCLQD_DYDJID"].ToString().Trim());
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
TSQL SQL = new TSQL(dberp);
|
||
SQL.Add(sSQL);
|
||
DataSet ds = dberp.OpenDataSet(SQL);
|
||
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
|
||
{
|
||
string tmpKey = ContentGrid.DataKeys[ContentGrid.SelectedIndex]["XLXSCLQD_XLXHID"].ToString().Trim() + " "
|
||
+ ContentGrid.DataKeys[ContentGrid.SelectedIndex]["XLXSCLQD_XLGGID"].ToString().Trim() + " "
|
||
+ ContentGrid.DataKeys[ContentGrid.SelectedIndex]["XLXSCLQD_XLBZID"].ToString().Trim() + " "
|
||
+ ContentGrid.DataKeys[ContentGrid.SelectedIndex]["XLXSCLQD_DYDJID"].ToString().Trim();
|
||
ShowMessage("【" + tmpKey + "】已被销售价格表【" +ds.Tables[0].Rows[0][0].ToString().Trim() +"】使用，不允许删除！");
|
||
return false;
|
||
}
|
||
|
||
if (CMISproDataBase.ExistsRecord("DBERP", string.Format("select 1 from XLXSCLQD where XLXSCLQD_XLXHID='{0}' and XLXSCLQD_XLGGID='{1}' and XLXSCLQD_XLBZID='{2}' and XLXSCLQD_DYDJID='{3}' and isnull(XLXSCLQD_WFHANDLER,' ')<>' '",
|
||
ContentGrid.DataKeys[ContentGrid.SelectedIndex]["XLXSCLQD_XLXHID"].ToString().Trim(), ContentGrid.DataKeys[ContentGrid.SelectedIndex]["XLXSCLQD_XLGGID"].ToString().Trim(),
|
||
ContentGrid.DataKeys[ContentGrid.SelectedIndex]["XLXSCLQD_XLBZID"].ToString().Trim(), ContentGrid.DataKeys[ContentGrid.SelectedIndex]["XLXSCLQD_DYDJID"].ToString().Trim())))
|
||
{
|
||
ShowMessage("已经过工作流转，不允许直接删除！");
|
||
return false;
|
||
}
|
||
|
||
CurEntity.Param.Add("XLXSCLQD_XLXHID", ContentGrid.DataKeys[ContentGrid.SelectedIndex]["XLXSCLQD_XLXHID"].ToString().Trim());
|
||
CurEntity.Param.Add("XLXSCLQD_XLGGID", ContentGrid.DataKeys[ContentGrid.SelectedIndex]["XLXSCLQD_XLGGID"].ToString().Trim());
|
||
CurEntity.Param.Add("XLXSCLQD_XLBZID", ContentGrid.DataKeys[ContentGrid.SelectedIndex]["XLXSCLQD_XLBZID"].ToString().Trim());
|
||
CurEntity.Param.Add("XLXSCLQD_DYDJID", ContentGrid.DataKeys[ContentGrid.SelectedIndex]["XLXSCLQD_DYDJID"].ToString().Trim());
|
||
return base.Master_BeforeDeleteAction(sender, e);
|
||
}
|
||
|
||
protected override bool Master_AfterDeleteAction(object sender, EventArgs e)
|
||
{
|
||
if (!base.Master_AfterDeleteAction(sender, e)) return false;
|
||
|
||
base.InitEntityByCondition();
|
||
IfExistMC_Get();
|
||
initGrid();
|
||
entity.EntityTable.QuerySelSql = GetQuerySql();
|
||
entity.GridDataBind(ContentGrid, tSession);
|
||
ContentGrid.SelectedIndex = -1;
|
||
|
||
return true;
|
||
}
|
||
|
||
protected override void Master_OnStandardQuery(object sender, EventArgs e)
|
||
{
|
||
base.InitEntityByCondition();
|
||
base.Master_OnStandardQuery(sender, e);
|
||
IfExistMC_Get();
|
||
initGrid();
|
||
entity.EntityTable.QuerySelSql = GetQuerySql();
|
||
entity.GridDataBind(ContentGrid, tSession);
|
||
}
|
||
|
||
protected override void moduleGV_PageIndexChanging(object sender, GridViewPageEventArgs e)
|
||
{
|
||
base.InitEntityByCondition();
|
||
IfExistMC_Get();
|
||
initGrid();
|
||
entity.EntityTable.QuerySelSql = GetQuerySql();
|
||
ContentGrid.SelectedIndex = -1;
|
||
base.moduleGV_PageIndexChanging(sender, e);
|
||
}
|
||
|
||
protected override void moduleGV_RowDataBound(object sender, GridViewRowEventArgs e)
|
||
{
|
||
if (e.Row.RowType == DataControlRowType.DataRow)
|
||
{
|
||
LinkPageParam.AddString("XLXSCLQD_XLXHID", ContentGrid.DataKeys[e.Row.RowIndex]["XLXSCLQD_XLXHID"].ToString().Trim());
|
||
LinkPageParam.AddString("XLXSCLQD_XLGGID", ContentGrid.DataKeys[e.Row.RowIndex]["XLXSCLQD_XLGGID"].ToString().Trim());
|
||
LinkPageParam.AddString("XLXSCLQD_XLBZID", ContentGrid.DataKeys[e.Row.RowIndex]["XLXSCLQD_XLBZID"].ToString().Trim());
|
||
LinkPageParam.AddString("XLXSCLQD_DYDJID", ContentGrid.DataKeys[e.Row.RowIndex]["XLXSCLQD_DYDJID"].ToString().Trim());
|
||
}
|
||
base.moduleGV_RowDataBound(sender, e);
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 私有函数
|
||
|
||
private string GetQuerySql()
|
||
{
|
||
//若所选尺重表为Z类、B类，则关联XLJGCZBNR表，按XLJGCZBNR_DRHH排序；
|
||
//若所选尺重表为A类、C类、G类，则关联XLXHJGCZBNR表，按XLXHJGCZBML_JGCZBXH,XLXHJGCZBNR_DRHH排序。
|
||
|
||
string sCZBLX = "";
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
TSQL sql = new TSQL(dberp);
|
||
sql.Clear();
|
||
sql.Add(" SELECT XLJGCZBML_CZBLX from XLJGCZBML ");
|
||
sql.Add(" where XLJGCZBML_JGCZBID=:pJGCZBID ");
|
||
sql.ParamByName("pJGCZBID").Value = CurQueryEntity.FieldByName("JGCZBID").Value.Trim().Split('|')[0];
|
||
DataSet ds = dberp.OpenDataSet(sql);
|
||
if (ds.Tables[0].Rows.Count > 0)
|
||
{
|
||
sCZBLX = ds.Tables[0].Rows[0][0].ToString().Trim();
|
||
}
|
||
|
||
string sSQL = " select XLXSCLQD_XLXHID, XLXSCLQD_XLGGID, XLXSCLQD_XLBZID,RTRIM(XLXSCLQD_XLBZID)||'|'|| XLBZ_XLBZLXMC as XLBZ, XLXSCLQD_DYDJID,'" + GetOption("BJU003") + "' as JLDW, XLXSCLQD_CLZL,\r\n"
|
||
+ "XLXSCLQD_SL01, XLXSCLQD_SL02, XLXSCLQD_SL03, XLXSCLQD_SL04, XLXSCLQD_SL05, XLXSCLQD_SL06, XLXSCLQD_SL07, XLXSCLQD_SL08, XLXSCLQD_SL09, XLXSCLQD_SL10,\r\n"
|
||
+ "XLXSCLQD_SL11, XLXSCLQD_SL12, XLXSCLQD_SL13, XLXSCLQD_SL14, XLXSCLQD_SL15, XLXSCLQD_SL16, XLXSCLQD_SL17, XLXSCLQD_SL18, XLXSCLQD_SL19, XLXSCLQD_SL20,\r\n"
|
||
+ "XLXSCLQD_SL21, XLXSCLQD_SL22, XLXSCLQD_SL23, XLXSCLQD_SL24, XLXSCLQD_SL25, XLXSCLQD_SL26, XLXSCLQD_SL27, XLXSCLQD_SL28, XLXSCLQD_SL29, XLXSCLQD_SL30,\r\n"
|
||
+ "XLXSCLQD_SL31, XLXSCLQD_SL32, XLXSCLQD_SL33, XLXSCLQD_SL34, XLXSCLQD_SL35, XLXSCLQD_SL36, XLXSCLQD_SL37, XLXSCLQD_SL38, XLXSCLQD_SL39, XLXSCLQD_SL40,\r\n"
|
||
+ "XLXSCLQD_SL41, XLXSCLQD_SL42, XLXSCLQD_SL43, XLXSCLQD_SL44, XLXSCLQD_SL45, XLXSCLQD_SL46, XLXSCLQD_SL47, XLXSCLQD_SL48, XLXSCLQD_SL49, XLXSCLQD_SL50,\r\n"
|
||
+ "XLXSCLQD_SL51, XLXSCLQD_SL52, XLXSCLQD_SL53, XLXSCLQD_SL54, XLXSCLQD_SL55, XLXSCLQD_SL56, XLXSCLQD_SL57, XLXSCLQD_SL58, XLXSCLQD_SL59, XLXSCLQD_SL60,\r\n"
|
||
+ "XLXSCLQD_SL61, XLXSCLQD_SL62, XLXSCLQD_SL63, XLXSCLQD_SL64, XLXSCLQD_SL65, XLXSCLQD_SL66, XLXSCLQD_SL67, XLXSCLQD_SL68, XLXSCLQD_SL69, XLXSCLQD_SL70,\r\n"
|
||
+ "XLXSCLQD_SL71, XLXSCLQD_SL72, XLXSCLQD_SL73, XLXSCLQD_SL74, XLXSCLQD_SL75, XLXSCLQD_SL76, XLXSCLQD_SL77, XLXSCLQD_SL78, XLXSCLQD_SL79, XLXSCLQD_SL80,\r\n";
|
||
|
||
for (int i = 1; i <= 80; i++)
|
||
{
|
||
if (dicHeaderTextCLWL.ContainsKey("第" + i.ToString().PadLeft(2, '0') + "列"))
|
||
sSQL += string.Format("RTRIM(XLXSCLQDWL_WLID{0}) || '|' || RTRIM(ISNULL(WL{0}.WL_MC,' ')) || ' ' || RTRIM(ISNULL(WL{0}.WL_GG,' ')) as XLXSCLWL{0},\r\n", i.ToString().PadLeft(2, '0'));
|
||
else
|
||
sSQL += string.Format("' ' as XLXSCLWL{0},", i.ToString().PadLeft(2, '0'));
|
||
}
|
||
sSQL += " XLXSCLQD_CPMLID,XLXSCLQD_BZ, XLXSCLQD_WHR, XLXSCLQD_WHRID, XLXSCLQD_WHSJ "
|
||
+ " from XLXSCLQD "
|
||
+ " left join XLBZ on XLBZ_XLBZID = XLXSCLQD_XLBZID "
|
||
+ " LEFT JOIN XLXSCLQDWL ON XLXSCLQDWL_JGCZBID = XLXSCLQD_JGCZBID AND XLXSCLQDWL_JGCZBBBID = XLXSCLQD_JGCZBBBID and XLXSCLQD_JGCZBXH = XLXSCLQDWL_JGCZBXH AND XLXSCLQDWL_DRHH = XLXSCLQD_DRHH ";
|
||
if (CurQueryEntity.FieldByName("PXFS").Value.Trim().Equals("尺重表内容导入行号"))
|
||
{
|
||
if (sCZBLX.Equals("B"))
|
||
{
|
||
sSQL += " left join XLJGCZBNR on XLJGCZBNR_JGCZBID = XLXSCLQD_JGCZBID and XLJGCZBNR_JGCZBBBID = XLXSCLQD_JGCZBBBID and XLJGCZBNR_DRHH = XLXSCLQD_DRHH ";
|
||
}
|
||
else if (sCZBLX.Equals("Z") || sCZBLX.Equals("A") || sCZBLX.Equals("C") || sCZBLX.Equals("G"))
|
||
{
|
||
sSQL += " left join XLXHJGCZBNR on XLXHJGCZBNR_JGCZBID = XLXSCLQD_JGCZBID and XLXHJGCZBNR_JGCZBBBID = XLXSCLQD_JGCZBBBID "
|
||
+ " and XLXHJGCZBNR_JGCZBXH = XLXSCLQD_JGCZBXH "
|
||
+ " and XLXHJGCZBNR_DRHH = XLXSCLQD_DRHH ";
|
||
}
|
||
}
|
||
for (int i = 1; i <= 80; i++)
|
||
{
|
||
if (dicHeaderTextCLWL.ContainsKey("第" + i.ToString().PadLeft(2, '0') + "列"))
|
||
sSQL += string.Format("LEFT JOIN WL WL{0} ON WL{0}.WL_WLID =XLXSCLQDWL_WLID{0} \r\n", i.ToString().PadLeft(2, '0'));
|
||
}
|
||
//加入线芯结构，进行规格排序
|
||
//+ " left join XLGGB on XLGGB_XLGGID=XLXSCLQD_XLGGID "
|
||
//+ " left join XLXXJGB on XLXXJGB_XXJGID=XLGGB_XXJGID "
|
||
//+ " left join XLDTJMZHB on XLDTJMZHB_DTJMZHID=XLGGB_DTJMZHID "
|
||
|
||
sSQL += " where 1=1 and XLXSCLQD_WPID ='ZZZZ' ";
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("JGCZBID").Value.Trim()))
|
||
{
|
||
sSQL += "\r\n and XLXSCLQD_JGCZBID = :pJGCZBID ";
|
||
entity.Param.Add("pJGCZBID", CurQueryEntity.FieldByName("JGCZBID").Value.Trim().Split('|')[0]);
|
||
}
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("JGCZBBBID").Value.Trim()))
|
||
{
|
||
sSQL += "\r\n and XLXSCLQD_JGCZBBBID = :pJGCZBBBID ";
|
||
entity.Param.Add("pJGCZBBBID", CurQueryEntity.FieldByName("JGCZBBBID").Value.Trim());
|
||
}
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("CPMLID").Value.Trim()))
|
||
{
|
||
sSQL += "\r\n and XLXSCLQD_CPMLID = :pCPMLID ";
|
||
entity.Param.Add("pCPMLID", CurQueryEntity.FieldByName("CPMLID").Value.Trim());
|
||
}
|
||
|
||
//型号模糊
|
||
if (!CurQueryEntity.FieldByName("XLXHIDJQPP").Value.ToString().Trim().ToUpper().Equals("TRUE"))
|
||
{
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("XLXHID").Value.Trim()))
|
||
{
|
||
sSQL += "\r\n and XLXSCLQD_XLXHID like :pXLXHID ";
|
||
entity.Param.Add("pXLXHID", "%" + CurQueryEntity.FieldByName("XLXHID").Value.Trim() + "%");
|
||
}
|
||
}
|
||
else
|
||
{
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("XLXHID").Value.Trim()))
|
||
{
|
||
sSQL += "\r\n and XLXSCLQD_XLXHID = :pXLXHID ";
|
||
entity.Param.Add("pXLXHID", CurQueryEntity.FieldByName("XLXHID").Value.Trim());
|
||
}
|
||
}
|
||
//规格模糊
|
||
if (!CurQueryEntity.FieldByName("XLGGIDJQPP").Value.ToString().Trim().ToUpper().Equals("TRUE"))
|
||
{
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("XLGGID").Value.Trim()))
|
||
{
|
||
sSQL += " and replace(XLXSCLQD_XLGGID,'*','×') like :pXLGGID ";
|
||
entity.Param.Add("pXLGGID", "%" + CurQueryEntity.FieldByName("XLGGID").Value.Trim().Replace("*", "×") + "%");
|
||
}
|
||
}
|
||
else
|
||
{
|
||
string sXLGGID = CurQueryEntity.FieldByName("XLGGID").Value.Trim();
|
||
if (!string.IsNullOrEmpty(sXLGGID))
|
||
{
|
||
string[] sTemp = sXLGGID.Split(new Char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
|
||
if (sTemp.Length >= 2)
|
||
{
|
||
sSQL += " and " + CMISproDataBase.GetWhereEqual(" replace(XLXSCLQD_XLGGID,'*','×')", sXLGGID.Replace('*', '×'));
|
||
}
|
||
else
|
||
{
|
||
sSQL += " and replace(XLXSCLQD_XLGGID,'*','×') = :pXLGGID ";
|
||
entity.Param.Add("pXLGGID", CurQueryEntity.FieldByName("XLGGID").Value.Trim().Replace("*", "×"));
|
||
}
|
||
}
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("ST").Value.Trim()))
|
||
{
|
||
sSQL += "\r\n and XLXSCLQD_XLBZID = :pST ";
|
||
entity.Param.Add("pST", CurQueryEntity.FieldByName("ST").Value.Trim());
|
||
}
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("DYDJ").Value.Trim()))
|
||
{
|
||
sSQL += "\r\n and XLXSCLQD_DYDJID = :pDYDJ ";
|
||
entity.Param.Add("pDYDJ", CurQueryEntity.FieldByName("DYDJ").Value.Trim());
|
||
}
|
||
if (CurQueryEntity.FieldByName("PXFS").Value.Trim().Equals("型号+序号"))
|
||
{
|
||
sSQL += " order by XLXSCLQD_XLXHID,XLXSCLQD_XH ";
|
||
}
|
||
else if (CurQueryEntity.FieldByName("PXFS").Value.Trim().Equals("尺重表内容导入行号"))
|
||
{
|
||
if ( sCZBLX.Equals("B"))
|
||
{
|
||
sSQL += " order by XLJGCZBNR_DRHH ";
|
||
}
|
||
else if (sCZBLX.Equals("Z") || sCZBLX.Equals("A") || sCZBLX.Equals("C") || sCZBLX.Equals("G"))
|
||
{
|
||
sSQL += " order by XLXHJGCZBNR_JGCZBXH,XLXHJGCZBNR_DRHH ";
|
||
}
|
||
else
|
||
{
|
||
sSQL += " order by XLXSCLQD_XLXHID,XLXSCLQD_XH ";
|
||
}
|
||
}
|
||
else
|
||
{
|
||
sSQL += " order by XLXSCLQD_XLXHID,XLXSCLQD_XH ";
|
||
}
|
||
|
||
//sSQL += " order by XLXSCLQD_XLXHID,";
|
||
//sSQL += " charindex('+',XLGGB_XXJGID),charindex('×',XLGGB_XXJGID),len(XLGGB_XXJGID),";
|
||
//sSQL += " XLGGB_XXJGID,XLDTJMZHB_DTJM1,XLDTJMZHB_DTJM2,XLDTJMZHB_DTJM3,XLDTJMZHB_DTJM4,";
|
||
//sSQL += " XLXSCLQD_XH,XLXSCLQD_XLGGID,XLXSCLQD_XLBZID,XLXSCLQD_DYDJID";
|
||
string s = sSQL;
|
||
return sSQL;
|
||
}
|
||
|
||
public void IfExistMC_Get()
|
||
{
|
||
dicHeaderText.Clear();
|
||
dicHeaderTextCLWL.Clear();
|
||
if (string.IsNullOrEmpty(CurQueryEntity.FieldByName("JGCZBID").Value.Trim()))
|
||
{
|
||
for (int i = 1; i <= 80; i++)
|
||
{
|
||
ContentGrid.Columns[2 * i + 5].Visible = false;
|
||
}
|
||
return;
|
||
}
|
||
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
TSQL sql = new TSQL(dberp);
|
||
|
||
DataSet ds;
|
||
sql.Add(" select XLXSBJCL_BJHQ,XLJGCZBLM_CLQDL, ");
|
||
sql.Add(" case when isnull(XLXSBJCL_BJCLID,' ')=' ' then XLJGCZBLM_CZBLMMC else XLXSBJCL_BJCLMC end as CLMC");
|
||
sql.Add(" from XLJGCZBLM ");
|
||
sql.Add(" left join XLXSBJCL on XLXSBJCL_BJCLID=XLJGCZBLM_BJCLID");
|
||
//sql.Add(" left join XLJGCZBML on XLJGCZBML_JGCZBID = XLJGCZBLM_JGCZBID ");
|
||
sql.Add(" where XLJGCZBLM_JGCZBID=:pJGCZBID and XLJGCZBLM_JGCZBBBID =:pJGCZBBBID and isnull(XLJGCZBLM_CLQDL,' ')<>' '");
|
||
sql.Add(" order by XLJGCZBLM_CLQDL ");
|
||
sql.ParamByName("pJGCZBID").Value = CurQueryEntity.FieldByName("JGCZBID").Value.Trim().Split('|')[0];
|
||
sql.ParamByName("pJGCZBBBID").Value = CurQueryEntity.FieldByName("JGCZBBBID").Value.Trim();
|
||
ds = dberp.OpenDataSet(sql);
|
||
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
|
||
{
|
||
dicHeaderText.Add((ds.Tables[0].Rows[i]["XLJGCZBLM_CLQDL"].ToString().Trim()), ds.Tables[0].Rows[i]["CLMC"].ToString().Trim());
|
||
//添加物料列
|
||
if (ds.Tables[0].Rows[i]["XLXSBJCL_BJHQ"].ToString().Trim().Equals("2"))
|
||
{
|
||
dicHeaderTextCLWL.Add((ds.Tables[0].Rows[i]["XLJGCZBLM_CLQDL"].ToString().Trim()), ds.Tables[0].Rows[i]["CLMC"].ToString().Trim());
|
||
}
|
||
}
|
||
SetColumnsVisible();
|
||
}
|
||
|
||
private void initGrid()
|
||
{
|
||
for (int i = 1; i <= 80; i++)
|
||
{
|
||
string sCLQDL = "第" + i.ToString().PadLeft(2, '0') + "列";
|
||
string sExist = "";
|
||
if (dicHeaderText.ContainsKey(sCLQDL))
|
||
{
|
||
sExist = dicHeaderText[sCLQDL].Trim();
|
||
}
|
||
|
||
if (!sExist.Equals(""))
|
||
{
|
||
ContentGrid.Columns[2 * i - 1 + 5].HeaderText = sExist;
|
||
}
|
||
}
|
||
}
|
||
|
||
private void SetColumnsVisible()
|
||
{
|
||
if (dicHeaderText.Count > 0)
|
||
{
|
||
for (int i = 1; i <= 80; i++)
|
||
{
|
||
string sCLQDL = "第" + i.ToString().PadLeft(2, '0') + "列";
|
||
string sExist = "";
|
||
if (dicHeaderText.ContainsKey(sCLQDL))
|
||
{
|
||
sExist = dicHeaderText[sCLQDL].Trim();
|
||
}
|
||
if (!CurQueryEntity.FieldByName("XSBJCLWL").Value.ToString().Trim().ToUpper().Equals("TRUE"))
|
||
{
|
||
ContentGrid.Columns[2 * i + 5].Visible = false;
|
||
}
|
||
else
|
||
{
|
||
if (!dicHeaderTextCLWL.ContainsKey(sCLQDL))
|
||
{
|
||
ContentGrid.Columns[2 * i + 5].Visible = false;
|
||
}
|
||
}
|
||
if (sExist.Equals(""))
|
||
{
|
||
ContentGrid.Columns[2 * i - 1 + 5].Visible = false;
|
||
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
//判断收货通知单记录是否存在
|
||
private bool IsExistsJL(string sXLXHID, string sXLGGID, string sST, string sDYDJ)
|
||
{
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
TSQL sql = new TSQL(dberp);
|
||
|
||
sql.Clear();
|
||
sql.Add("select 1 from XLXSCLQD where XLXSCLQD_XLXHID=:XLXSCLQD_XLXHID and XLXSCLQD_XLGGID=:XLXSCLQD_XLGGID and XLXSCLQD_XLBZID=:XLXSCLQD_XLBZID and XLXSCLQD_DYDJID=:XLXSCLQD_DYDJID ");
|
||
sql.ParamByName("XLXSCLQD_XLXHID").Value = sXLXHID;
|
||
sql.ParamByName("XLXSCLQD_XLGGID").Value = sXLGGID;
|
||
sql.ParamByName("XLXSCLQD_XLBZID").Value = sST;
|
||
sql.ParamByName("XLXSCLQD_DYDJID").Value = sDYDJ;
|
||
DataSet ds = dberp.OpenDataSet(sql);
|
||
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
|
||
{
|
||
return true;
|
||
}
|
||
|
||
return false;
|
||
}
|
||
|
||
|
||
#region 添加虚拟材料
|
||
|
||
private string GetBJCLMC(string sBJCLID)
|
||
{
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
TSQL sql = new TSQL(dberp);
|
||
sql.Add("select XLXSBJCL_BJCLMC from XLXSBJCL where XLXSBJCL_BJCLID=:pBJCLID ");
|
||
sql.ParamByName("pBJCLID").Value = sBJCLID.Trim();
|
||
DataSet ds = dberp.OpenDataSet(sql);
|
||
string sBJCLMC = "";
|
||
if (ds.Tables[0].Rows.Count > 0)
|
||
{
|
||
sBJCLMC = ds.Tables[0].Rows[0][0].ToString().Trim();
|
||
}
|
||
return sBJCLMC;
|
||
}
|
||
|
||
private DataSet GetJGCZBbyYZXX(string sJGCZBID, string sJGCZBBBID,string sYZID,string sJGBID)
|
||
{
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
|
||
TSQL SQL = new TSQL(dberp);
|
||
|
||
SQL.Clear();
|
||
|
||
SQL.Add(@" select distinct XLXSCLQD_JGCZBID,XLXSCLQD_JGCZBBBID,XLJGCZBML_JGCZBMC as JGCZBMC from XLXSCLQD
|
||
left join XLJGCZBML on XLXSCLQD_JGCZBID=XLJGCZBML_JGCZBID and XLXSCLQD_JGCZBBBID=XLJGCZBML_JGCZBBBID
|
||
where XLXSCLQD_WPID='ZZZZ' and exists(select 1 from XLXSJGBYZZ1 where XLXSJGBYZZ1_JGBID=:pJGBID and XLXSJGBYZZ1_YZID=:pYZID and XLXSJGBYZZ1_XLXHID=XLXSCLQD_XLXHID
|
||
and XLXSJGBYZZ1_XLGGID=XLXSCLQD_XLGGID and XLXSJGBYZZ1_XLBZID=XLXSCLQD_XLBZID and XLXSJGBYZZ1_DYDJID=XLXSCLQD_DYDJID) ");
|
||
|
||
if (!string.IsNullOrEmpty(sJGCZBID))
|
||
{
|
||
SQL.Add(" and XLXSCLQD_JGCZBID=:pJGCZBID and XLXSCLQD_JGCZBBBID=:pJGCZBBBID");
|
||
SQL.ParamByName("pJGCZBID").Value = sJGCZBID;
|
||
SQL.ParamByName("pJGCZBBBID").Value = sJGCZBBBID;
|
||
}
|
||
|
||
SQL.Add(" order by XLXSCLQD_JGCZBBBID,XLXSCLQD_JGCZBID");
|
||
|
||
SQL.ParamByName("pJGBID").Value = sJGBID;
|
||
SQL.ParamByName("pYZID").Value = sYZID;
|
||
|
||
DataSet ds = dberp.OpenDataSet(SQL);
|
||
|
||
return ds;
|
||
}
|
||
|
||
private DataSet GetCLQDLbyBJCLID(string sJGCZBID, string sJGCZBBBID, string sBJCLID)
|
||
{
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
|
||
TSQL SQL = new TSQL(dberp);
|
||
|
||
SQL.Clear();
|
||
SQL.Add(@"select XLJGCZBLM_CLQDL
|
||
from XLJGCZBLM
|
||
where XLJGCZBLM_JGCZBID=:pJGCZBID and XLJGCZBLM_JGCZBBBID=:pJGCZBBBID and XLJGCZBLM_BJCLID=:pBJCLID ");
|
||
SQL.ParamByName("pJGCZBID").Value = sJGCZBID;
|
||
SQL.ParamByName("pJGCZBBBID").Value = sJGCZBBBID;
|
||
SQL.ParamByName("pBJCLID").Value = sBJCLID;
|
||
DataSet dsCLQDL = dberp.OpenDataSet(SQL);
|
||
|
||
return dsCLQDL;
|
||
}
|
||
|
||
private int GetMaxCLQDL(string sJGCZBID, string sJGCZBBBID)
|
||
{
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
|
||
TSQL SQL = new TSQL(dberp);
|
||
|
||
SQL.Clear();
|
||
SQL.Add(@"select max(SUBSTRING(XLJGCZBLM_CLQDL,2,2))
|
||
from XLJGCZBLM
|
||
where XLJGCZBLM_JGCZBID=:pJGCZBID and XLJGCZBLM_JGCZBBBID=:pJGCZBBBID and XLJGCZBLM_ZDLMLX='4'
|
||
group by XLJGCZBLM_JGCZBID,XLJGCZBLM_JGCZBBBID ");
|
||
SQL.ParamByName("pJGCZBID").Value = sJGCZBID;
|
||
SQL.ParamByName("pJGCZBBBID").Value = sJGCZBBBID;
|
||
DataSet dsMAX = dberp.OpenDataSet(SQL);
|
||
int iMaxCLQDL = 0;
|
||
if (dsMAX.Tables[0].Rows.Count > 0)
|
||
iMaxCLQDL = CMISproConvert.StrToInt(dsMAX.Tables[0].Rows[0][0].ToString().Trim(), 0);
|
||
|
||
return iMaxCLQDL;
|
||
}
|
||
|
||
#endregion
|
||
|
||
#endregion
|
||
|
||
# region 控件事件
|
||
|
||
protected void btnZJCLLM_DropDown(object sender, EventArgs e)
|
||
{
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
TSQL SQL = new TSQL(dberp);
|
||
SQL.Clear();
|
||
SQL.Add("select distinct XLXSTZYZ_XSXH,XLXSTZYZ_YZID,XLXSTZYZ_YZMC,XLXSTZYZ_JGBID,XLXSTZYZ_BJCLID from XLXSTZYZ where ISNULL(XLXSTZYZ_BJCLID,' ')<>' ' and XLXSTZYZ_MXBH='JZJ' and XLXSTZYZ_TJYS='1' order by XLXSTZYZ_XSXH,XLXSTZYZ_YZID ");
|
||
DataSet ds = dberp.OpenDataSet(SQL);
|
||
btnZJCLLM.Nodes.Clear();
|
||
if (ds.Tables[0].Rows.Count > 0)
|
||
{
|
||
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
|
||
{
|
||
string sYZID = ds.Tables[0].Rows[i]["XLXSTZYZ_YZID"].ToString().Trim();
|
||
string sYZMC = ds.Tables[0].Rows[i]["XLXSTZYZ_YZMC"].ToString().Trim();
|
||
string sJGBID = ds.Tables[0].Rows[i]["XLXSTZYZ_JGBID"].ToString().Trim();
|
||
string sBJCLID = ds.Tables[0].Rows[i]["XLXSTZYZ_BJCLID"].ToString().Trim();
|
||
btnZJCLLM.Nodes.Add(new gtTreeNode(sYZID+"|"+sBJCLID+"|"+sYZMC+"|"+sJGBID,sYZMC));
|
||
}
|
||
}
|
||
MenuAreaUpdate();
|
||
}
|
||
|
||
protected void btnZJCLLM_MenuItemClick(object sender, string sValue)
|
||
{
|
||
Dictionary<string, string> dicXNBJCL = new Dictionary<string, string>();
|
||
string sYZID = sValue.Split('|')[0];
|
||
string sBJCLID = sValue.Split('|')[1];
|
||
string sYZMC=sValue.Split('|')[2];
|
||
string sJGBID = sValue.Split('|')[3];
|
||
string sBJCLMC = GetBJCLMC(sBJCLID);
|
||
|
||
base.InitEntityByCondition();
|
||
string sMsg = "";
|
||
if (string.IsNullOrEmpty(CurQueryEntity.FieldByName("JGCZBID").Value.Trim()))
|
||
{
|
||
sMsg = string.Format("将对所有存在【{0}】调价表覆盖产品的尺重表追加材料栏目【{1}】，是否继续？", sYZMC,sBJCLMC);
|
||
}
|
||
else
|
||
sMsg = string.Format("将对当前尺重表追加材料栏目【{0}】，是否继续？", sBJCLMC);
|
||
Confirm.Register("IsQD", sMsg);
|
||
Confirm.ShowBtnText = TConfirm.cBtnText.Yes_No;
|
||
if (Confirm.GetValue("IsQD").Trim().Equals(""))
|
||
{
|
||
Confirm.Ask("IsQD");
|
||
return;
|
||
}
|
||
else if (Confirm.GetValue("IsQD").Trim().Equals("N"))
|
||
{
|
||
Confirm.Clear();
|
||
return;
|
||
}
|
||
else if (Confirm.GetValue("IsQD").Trim().Equals("Y"))
|
||
{
|
||
Confirm.Clear();
|
||
}
|
||
|
||
DataSet dsJGCZB = GetJGCZBbyYZXX(CurQueryEntity.FieldByName("JGCZBID").Value.Trim().Split('|')[0], CurQueryEntity.FieldByName("JGCZBBBID").Value.Trim(), sYZID, sJGBID);
|
||
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
|
||
TSQL SQL = new TSQL(dberp);
|
||
|
||
if (dsJGCZB.Tables[0].Rows.Count > 0)
|
||
{
|
||
try
|
||
{
|
||
dberp.BeginTrans();
|
||
for (int i = 0; i < dsJGCZB.Tables[0].Rows.Count; i++)
|
||
{
|
||
string sJGCZBID = dsJGCZB.Tables[0].Rows[i]["XLXSCLQD_JGCZBID"].ToString().Trim();
|
||
string sJGCZBBBID = dsJGCZB.Tables[0].Rows[i]["XLXSCLQD_JGCZBBBID"].ToString().Trim();
|
||
string sJGCZBMC = dsJGCZB.Tables[0].Rows[i]["JGCZBMC"].ToString().Trim();
|
||
|
||
DataSet dsCLQDL = GetCLQDLbyBJCLID(sJGCZBID, sJGCZBBBID, sBJCLID);
|
||
|
||
if(dsCLQDL.Tables[0].Rows.Count >1)
|
||
{
|
||
string sEXmsg = string.Format("【{0}|{1}|{2}】存在多个{3}栏目，无法继续追加材料栏目！",sJGCZBID,sJGCZBMC,sJGCZBBBID,sBJCLMC);
|
||
throw (new Exception(sEXmsg));
|
||
}
|
||
else if (dsCLQDL.Tables[0].Rows.Count ==1)
|
||
dicXNBJCL.Add(sJGCZBID + "|" + sJGCZBBBID, dsCLQDL.Tables[0].Rows[0][0].ToString().Trim());
|
||
else
|
||
{
|
||
|
||
int iMaxCLQDL = GetMaxCLQDL(sJGCZBID,sJGCZBBBID);
|
||
if (iMaxCLQDL >= 80)
|
||
{
|
||
string sEXmsg = string.Format("【{0}|{1}|{2}】已有材料栏目数量大于等于80，无法继续追加材料栏目！", sJGCZBID, sJGCZBMC, sJGCZBBBID);
|
||
throw (new Exception(sEXmsg));
|
||
}
|
||
else
|
||
{
|
||
iMaxCLQDL += 1;
|
||
dicXNBJCL.Add(sJGCZBID + "|" + sJGCZBBBID, "第" + iMaxCLQDL.ToString().PadLeft(2, '0') + "列");
|
||
SQL.Clear();
|
||
SQL.Add(@" insert into XLJGCZBLM(XLJGCZBLM_JGCZBID,XLJGCZBLM_WHRID,XLJGCZBLM_WHR,XLJGCZBLM_WHSJ,XLJGCZBLM_CZBLMBH,XLJGCZBLM_CZBLMMC,XLJGCZBLM_CLQDL,XLJGCZBLM_BJCLID,XLJGCZBLM_HLMLX,XLJGCZBLM_ZDLMLX,XLJGCZBLM_JGCZBBBID)
|
||
values(:pJGCZBID,:pWHRID,:pWHR,:pWHSJ,:pCZBLMBH,:pCZBLMMC,:pCLQDL,:pBJCLID,:pHLMLX,:pZDLMLX,:pJGCZBBBID)");
|
||
|
||
string sConditon = "XLJGCZBLM_JGCZBID ='" + sJGCZBID + "' and XLJGCZBLM_JGCZBBBID='" + sJGCZBBBID + "'";
|
||
int iLMBH = CMISproDataBase.GetMaxXH("DBERP", "XLJGCZBLM", "XLJGCZBLM_CZBLMBH", sConditon) + 1;
|
||
SQL.ParamByName("pJGCZBID").Value = sJGCZBID;
|
||
SQL.ParamByName("pJGCZBBBID").Value = sJGCZBBBID;
|
||
SQL.ParamByName("pWHRID").Value = tSession.UserId;
|
||
SQL.ParamByName("pWHR").Value = tSession.UserName;
|
||
SQL.ParamByName("pWHSJ").Value = DateTime.Now;
|
||
SQL.ParamByName("pCZBLMBH").Value = iLMBH.ToString().PadLeft(2, '0');
|
||
SQL.ParamByName("pCZBLMMC").Value = sBJCLMC;
|
||
SQL.ParamByName("pCLQDL").Value = "第" + iMaxCLQDL.ToString().PadLeft(2, '0') + "列";
|
||
SQL.ParamByName("pBJCLID").Value = sBJCLID;
|
||
SQL.ParamByName("pHLMLX").Value = "Z";
|
||
SQL.ParamByName("pZDLMLX").Value = "4";
|
||
dberp.ExecSQL(SQL);
|
||
|
||
}
|
||
|
||
}
|
||
|
||
}
|
||
foreach (string key in dicXNBJCL.Keys)
|
||
{
|
||
string sJGCZBID = key.Split('|')[0];
|
||
string sJGCZBBBID = key.Split('|')[1];
|
||
string sCLQDL = dicXNBJCL[key];
|
||
SQL.Clear();
|
||
SQL.Add(string.Format(@"update XLXSCLQD set XLXSCLQD_SL{0} = 0
|
||
where XLXSCLQD_JGCZBID='{1}' and XLXSCLQD_JGCZBBBID='{2}' ", sCLQDL.Substring(1, 2), sJGCZBID, sJGCZBBBID));
|
||
dberp.ExecSQL(SQL);
|
||
SQL.Clear();
|
||
SQL.Add(string.Format(@"merge into XLXSCLQD tar
|
||
using (
|
||
select XLXSJGBYZZ1_XLXHID,XLXSJGBYZZ1_XLGGID,XLXSJGBYZZ1_XLBZID,XLXSJGBYZZ1_DYDJID,XLXSJGBYZZ1_YZZ from XLXSJGBYZZ1
|
||
where XLXSJGBYZZ1_JGBID='{0}' and XLXSJGBYZZ1_YZID='{1}'
|
||
) sor
|
||
on ( tar.XLXSCLQD_XLXHID=sor.XLXSJGBYZZ1_XLXHID and tar.XLXSCLQD_XLGGID=sor.XLXSJGBYZZ1_XLGGID
|
||
and tar.XLXSCLQD_XLBZID=sor.XLXSJGBYZZ1_XLBZID and tar.XLXSCLQD_DYDJID=sor.XLXSJGBYZZ1_DYDJID
|
||
and tar.XLXSCLQD_WPID='ZZZZ' and tar.XLXSCLQD_JGCZBID='{2}' and tar.XLXSCLQD_JGCZBBBID='{3}')
|
||
when matched then
|
||
update set tar.XLXSCLQD_SL{4}=sor.XLXSJGBYZZ1_YZZ ",sJGBID,sYZID, sJGCZBID, sJGCZBBBID, sCLQDL.Substring(1, 2)));
|
||
dberp.ExecSQL(SQL);
|
||
|
||
}
|
||
ShowMessage("追加材料栏目成功！");
|
||
dberp.CommitTrans();
|
||
moduleGV_PageIndexChanging(null, null);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
dberp.RollbackTrans();
|
||
ShowMessage(ex.Message);
|
||
return;
|
||
}
|
||
}
|
||
|
||
|
||
|
||
}
|
||
|
||
protected void btnRefreshDR_Click(object sender, EventArgs e)
|
||
{
|
||
//设置材料用量比例后更新材料成本
|
||
if (!String.IsNullOrEmpty(txtCLMXList.Value.Trim()))
|
||
{
|
||
string[] RowsArry;
|
||
|
||
if (ContentGrid.CheckBox && !String.IsNullOrEmpty(ContentGrid.CheckedRowIndexs))
|
||
{
|
||
RowsArry = ContentGrid.CheckedRowIndexs.Split(new char[] { ',' });
|
||
}
|
||
else
|
||
{
|
||
RowsArry = new string[1];
|
||
RowsArry[0] = ContentGrid.SelectedIndex.ToString();
|
||
}
|
||
for (int j = 0; j < RowsArry.Length; j++)
|
||
{
|
||
string sXLXHID = ContentGrid.DataKeys[CMISproConvert.StrToInt(RowsArry[j])]["XLXSCLQD_XLXHID"].ToString().Trim();
|
||
string sXLGGID = ContentGrid.DataKeys[CMISproConvert.StrToInt(RowsArry[j])]["XLXSCLQD_XLGGID"].ToString().Trim();
|
||
string sST = ContentGrid.DataKeys[CMISproConvert.StrToInt(RowsArry[j])]["XLXSCLQD_XLBZID"].ToString().Trim();
|
||
string sDYDJ = ContentGrid.DataKeys[CMISproConvert.StrToInt(RowsArry[j])]["XLXSCLQD_DYDJID"].ToString().Trim();
|
||
//更新80列数量
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
TSQL sql = new TSQL(dberp);
|
||
dberp.BeginTrans();
|
||
try
|
||
{
|
||
string sGXCLJGBL = "";
|
||
double dCLJGBL = CMISproConvert.StrToFloat(txtCLJGBL.Value.Trim(), 1);
|
||
string[] sCLArry = txtCLMXList.Value.Trim().Split(',');
|
||
for (int i = 0; i < sCLArry.Length; i++)
|
||
{
|
||
if (sGXCLJGBL.Equals(""))
|
||
{
|
||
sGXCLJGBL = " XLXSCLQD_CLJGBL" + sCLArry[i].ToString().PadLeft(2, '0') + " =" + dCLJGBL;
|
||
|
||
}
|
||
else
|
||
{
|
||
sGXCLJGBL = sGXCLJGBL + " , XLXSCLQD_CLJGBL" + sCLArry[i].ToString().PadLeft(2, '0') + " =" + dCLJGBL;
|
||
|
||
}
|
||
}
|
||
if (!sGXCLJGBL.Equals(""))
|
||
{
|
||
//Dictionary<string, string> dicCLJG = new Dictionary<string, string>();
|
||
dberp.ExecSQL(string.Format("update XLXSCLQD set " + sGXCLJGBL + " where XLXSCLQD_XLXHID='{0}' and XLXSCLQD_XLGGID='{1}' and XLXSCLQD_XLBZID='{2}' and XLXSCLQD_DYDJID='{3}'"
|
||
, sXLXHID
|
||
, sXLGGID
|
||
, sST
|
||
, sDYDJ));
|
||
|
||
dberp.CommitTrans();
|
||
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
txtCLMXList.Value = "";
|
||
txtCLJGBL.Value = "";
|
||
dberp.RollbackTrans();
|
||
ShowMessage(ex.Message);
|
||
return;
|
||
}
|
||
}
|
||
}
|
||
//更新完成后清空隐藏控件的值
|
||
txtCLMXList.Value = "";
|
||
txtCLJGBL.Value = "";
|
||
Master_OnStandardQuery(null, null);
|
||
FormAreaUpdate();
|
||
}
|
||
|
||
protected void btnSZCLJGBL_Click(object sender, EventArgs e)
|
||
{
|
||
txtCLJGBL.Value = "";
|
||
txtCLMXList.Value = "";
|
||
|
||
base.InitEntityByCondition();
|
||
if (!((ContentGrid.CheckBox && !String.IsNullOrEmpty(ContentGrid.CheckedRowIndexs)) || (ContentGrid.SelectedIndex >= 0)))
|
||
{
|
||
ShowMessage("请选择记录！");
|
||
return;
|
||
}
|
||
|
||
TPageParams param = new TPageParams();
|
||
param.UrlPath = "CLQD_SZCLJGBL.aspx";
|
||
param.AddString("JGCZBID", CurQueryEntity.FieldByName("JGCZBID").Value.Trim().Split('|')[0]);
|
||
param.AddString("JGCZBBBID", CurQueryEntity.FieldByName("JGCZBBBID").Value.Trim());
|
||
param.AddString("RefreshCtrlID", btnRefresh1.ClientID.Trim());
|
||
OpenDialog(param);
|
||
}
|
||
|
||
protected void btnJSCLZL_OnClick(object sender, EventArgs e)
|
||
{
|
||
base.InitEntityByCondition();
|
||
string sMsg = "";
|
||
if (string.IsNullOrEmpty(CurQueryEntity.FieldByName("JGCZBID").Value.Trim()))
|
||
{
|
||
sMsg = "将按结构尺重表依次重新计算材料重量，是否继续？";
|
||
}
|
||
else
|
||
sMsg = "将重新计算当前结构尺重表下的材料重量，是否继续？";
|
||
|
||
Confirm.Register("IsQD", sMsg);
|
||
Confirm.ShowBtnText = TConfirm.cBtnText.Yes_No;
|
||
if (Confirm.GetValue("IsQD").Trim().Equals(""))
|
||
{
|
||
Confirm.Ask("IsQD");
|
||
return;
|
||
}
|
||
else if (Confirm.GetValue("IsQD").Trim().Equals("N"))
|
||
{
|
||
//表示进行了提示并选择了取消，直接返回或其他处理
|
||
|
||
Confirm.Clear();
|
||
return;
|
||
}
|
||
else if (Confirm.GetValue("IsQD").Trim().Equals("Y"))
|
||
{
|
||
//表示进行了提示并选择了确认
|
||
|
||
Confirm.Clear();
|
||
}
|
||
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
TSQL sql = new TSQL(dberp);
|
||
UpdateCLZL(dberp, sql);
|
||
|
||
ShowMessage("材料重量计算完成！");
|
||
moduleGV_PageIndexChanging(null, null);
|
||
}
|
||
|
||
protected void UpdateCLZL(TDatabase dberp, TSQL sql)
|
||
{
|
||
if (!string.IsNullOrEmpty(CurQueryEntity.FieldByName("JGCZBID").Value.Trim()))
|
||
{
|
||
UpdatCLZL(dberp, sql, CurQueryEntity.FieldByName("JGCZBID").Value.Trim().Split('|')[0], CurQueryEntity.FieldByName("JGCZBBBID").Value.Trim());
|
||
}
|
||
else
|
||
{
|
||
sql.Clear();
|
||
sql.Add("select distinct XLXSCLQD_JGCZBID,XLXSCLQD_JGCZBBBID from XLXSCLQD where XLXSCLQD_WPID ='ZZZZ' order by XLXSCLQD_JGCZBID ");
|
||
DataSet dsCZB = dberp.OpenDataSet(sql);
|
||
for (int i = 0; i < dsCZB.Tables[0].Rows.Count; i++)
|
||
{
|
||
string sJGCZBID = dsCZB.Tables[0].Rows[i]["XLXSCLQD_JGCZBID"].ToString().Trim();
|
||
string sJGCZBBBID = dsCZB.Tables[0].Rows[i]["XLXSCLQD_JGCZBBBID"].ToString().Trim();
|
||
UpdatCLZL(dberp, sql, sJGCZBID, sJGCZBBBID);
|
||
}
|
||
}
|
||
}
|
||
|
||
public static void UpdatCLZL(TDatabase dberp, TSQL sql, string sJGCZBID, string sJGCZBBBID)
|
||
{
|
||
sql.Clear();
|
||
sql.Add(" select XLJGCZBLM_CLQDL,XLXSBJCL_JTZ,XLXSBJCL_JLZ ");
|
||
sql.Add(" from XLJGCZBLM ");
|
||
sql.Add(" left join XLXSBJCL on XLXSBJCL_BJCLID=XLJGCZBLM_BJCLID");
|
||
sql.Add(" where XLJGCZBLM_JGCZBID=:pJGCZBID and XLJGCZBLM_JGCZBBBID =:pJGCZBBBID and isnull(XLJGCZBLM_CLQDL,' ')<>' '");
|
||
sql.Add(" and isnull(XLXSBJCL_BJJZ,'Y')<>'N'");//报价计重
|
||
sql.Add(" order by XLJGCZBLM_CLQDL ");
|
||
sql.ParamByName("pJGCZBID").Value = sJGCZBID;
|
||
sql.ParamByName("pJGCZBBBID").Value = sJGCZBBBID;
|
||
DataSet ds = dberp.OpenDataSet(sql);
|
||
|
||
sql.Clear();
|
||
sql.Add("update XLXSCLQD set ");
|
||
if (ds.Tables[0].Rows.Count > 0)
|
||
{
|
||
sql.Add("XLXSCLQD_CLZL=(");
|
||
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
|
||
{
|
||
if (i == 0)
|
||
sql.Add(string.Format("XLXSCLQD_SL{0}*XLXSCLQD_CLJGBL{0}*(1+XLXSCLQD_SHL{0}/100)", ds.Tables[0].Rows[i]["XLJGCZBLM_CLQDL"].ToString().Trim().Replace("第", "").Replace("列", "")));
|
||
else
|
||
sql.Add(string.Format("+XLXSCLQD_SL{0}*XLXSCLQD_CLJGBL{0}*(1+XLXSCLQD_SHL{0}/100)", ds.Tables[0].Rows[i]["XLJGCZBLM_CLQDL"].ToString().Trim().Replace("第", "").Replace("列", "")));
|
||
}
|
||
sql.Add("), ");
|
||
|
||
sql.Add("XLXSCLQD_TZL=0");
|
||
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
|
||
{
|
||
if (ds.Tables[0].Rows[i]["XLXSBJCL_JTZ"].ToString().Trim().Equals("Y"))//计铜重
|
||
{
|
||
sql.Add(string.Format("+XLXSCLQD_SL{0}*XLXSCLQD_CLJGBL{0}*(1+XLXSCLQD_SHL{0}/100)", ds.Tables[0].Rows[i]["XLJGCZBLM_CLQDL"].ToString().Trim().Replace("第", "").Replace("列", "")));
|
||
}
|
||
}
|
||
sql.Add(", ");
|
||
|
||
sql.Add("XLXSCLQD_LZL=0");
|
||
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
|
||
{
|
||
if (ds.Tables[0].Rows[i]["XLXSBJCL_JLZ"].ToString().Trim().Equals("Y"))//计铜重
|
||
{
|
||
sql.Add(string.Format("+XLXSCLQD_SL{0}*XLXSCLQD_CLJGBL{0}*(1+XLXSCLQD_SHL{0}/100)", ds.Tables[0].Rows[i]["XLJGCZBLM_CLQDL"].ToString().Trim().Replace("第", "").Replace("列", "")));
|
||
}
|
||
}
|
||
}
|
||
else
|
||
sql.Add("XLXSCLQD_CLZL=0,XLXSCLQD_TZL=0,XLXSCLQD_LZL=0");
|
||
sql.Add(" where XLXSCLQD_WPID ='ZZZZ' and XLXSCLQD_JGCZBID = :pJGCZBID and XLXSCLQD_JGCZBBBID = :pJGCZBBBID ");
|
||
sql.ParamByName("pJGCZBID").Value = sJGCZBID;
|
||
sql.ParamByName("pJGCZBBBID").Value = sJGCZBBBID;
|
||
dberp.ExecSQL(sql);
|
||
}
|
||
|
||
public static void UpdateSHL(TDatabase dberp, TSQL sql, string sJGCZBID, string sJGCZBBBID, string sTJYS)
|
||
{
|
||
//先将材料损耗率全部清0
|
||
if (!string.IsNullOrEmpty(sJGCZBID))
|
||
{
|
||
sql.Clear();
|
||
sql.Add("update XLXSCLQD set ");
|
||
for (int i = 1; i < 80; i++)
|
||
{
|
||
sql.Add(string.Format("XLXSCLQD_SHL{0}=0,", i.ToString().PadLeft(2, '0')));
|
||
}
|
||
sql.Add("XLXSCLQD_SHL80=0 ");
|
||
sql.Add("where XLXSCLQD_WPID='ZZZZ'");
|
||
sql.Add(" and XLXSCLQD_JGCZBID=:pJGCZBID and XLXSCLQD_JGCZBBBID=:pJGCZBBBID");
|
||
sql.ParamByName("pJGCZBID").Value = sJGCZBID;
|
||
sql.ParamByName("pJGCZBBBID").Value = sJGCZBBBID;
|
||
dberp.ExecSQL(sql);
|
||
}
|
||
else
|
||
{
|
||
sql.Clear();
|
||
sql.Add("update XLXSCLQD set ");
|
||
for (int i = 1; i < 80; i++)
|
||
{
|
||
sql.Add(string.Format("XLXSCLQD_SHL{0}=0,", i.ToString().PadLeft(2, '0')));
|
||
}
|
||
sql.Add("XLXSCLQD_SHL80=0 ");
|
||
sql.Add("where XLXSCLQD_WPID='ZZZZ'");
|
||
dberp.ExecSQL(sql);
|
||
}
|
||
|
||
//使用调价因素2或6或7“材料损耗系数”更新XLXSCLQD_SHL
|
||
if (!string.IsNullOrEmpty(sJGCZBID))
|
||
{
|
||
/*
|
||
merge into 目标表 a
|
||
using 源表 b
|
||
on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)
|
||
when matched then update set a.更新字段=b.字段
|
||
when not matched then insert into a(字段1,字段2……)values(值1,值2……)
|
||
*/
|
||
if (sTJYS.Equals("2"))
|
||
{
|
||
sql.Clear();
|
||
sql.Add(" merge into XLXSCLQD tar ");
|
||
sql.Add(" using ( ");
|
||
|
||
sql.Add(" select XLXSCLQD_XLXHID as XLXHID,XLXSCLQD_XLGGID as XLGGID,XLXSCLQD_XLBZID as XLBZID,XLXSCLQD_DYDJID as DYDJID,");
|
||
for (int i = 1; i < 80; i++)
|
||
{
|
||
sql.Add(string.Format("sum(case when XLJGCZBLM_CLQDL='{0}' then YZZ else 0 end) as SHL{1},", "第" + i.ToString().PadLeft(2, '0') + "列", i.ToString().PadLeft(2, '0')));
|
||
}
|
||
sql.Add(" sum(case when XLJGCZBLM_CLQDL='第80列' then YZZ else 0 end) as SHL80");
|
||
sql.Add(" from XLXSCLQD");
|
||
sql.Add(" left join XLXHB on XLXHB_XLXHID=XLXSCLQD_XLXHID");
|
||
sql.Add(" left join XLDYDJB on XLDYDJB_DYDJID=XLXSCLQD_DYDJID");
|
||
sql.Add(" left join XLJGCZBLM on XLJGCZBLM_JGCZBID=XLXSCLQD_JGCZBID and XLJGCZBLM_JGCZBBBID = XLXSCLQD_JGCZBBBID and isnull(XLJGCZBLM_CLQDL,' ')<>' '");
|
||
sql.Add(" left join");
|
||
sql.Add(" (select XLXSJGBYZZ2_CPYT,XLXSJGBYZZ2_DYDJFL,XLXSJGBYZZ2_DTCLID,XLXSJGBYZZ2_BJCLID,sum(XLXSJGBYZZ2_YZZ) as YZZ");
|
||
sql.Add(" from XLXSTZYZ,XLXSJGBYZZ2");
|
||
sql.Add(" where XLXSTZYZ_MXBH='JZJ' and XLXSTZYZ_TJYS='2' and XLXSJGBYZZ2_JGBID=XLXSTZYZ_JGBID and XLXSJGBYZZ2_YZID=XLXSTZYZ_YZID");
|
||
sql.Add(" group by XLXSJGBYZZ2_CPYT,XLXSJGBYZZ2_DYDJFL,XLXSJGBYZZ2_DTCLID,XLXSJGBYZZ2_BJCLID");
|
||
sql.Add(" ) JGBYZZ2 on XLXSJGBYZZ2_CPYT=XLXHB_CPYT and (XLXSJGBYZZ2_DYDJFL=' ' or XLXSJGBYZZ2_DYDJFL=XLDYDJB_DYDJFL) and (XLXSJGBYZZ2_DTCLID=' ' or XLXSJGBYZZ2_DTCLID=XLXHB_DTCLID) and XLXSJGBYZZ2_BJCLID=XLJGCZBLM_BJCLID");
|
||
sql.Add(" where XLXSCLQD_WPID='ZZZZ'");
|
||
sql.Add(" and XLXSCLQD_JGCZBID=:pJGCZBID and XLXSCLQD_JGCZBBBID=:pJGCZBBBID");
|
||
sql.Add(" group by XLXSCLQD_XLXHID,XLXSCLQD_XLGGID,XLXSCLQD_XLBZID,XLXSCLQD_DYDJID");
|
||
sql.Add(" ) sor ");
|
||
sql.Add(" on( ");
|
||
sql.Add(" XLXSCLQD_XLXHID=XLXHID and XLXSCLQD_XLGGID=XLGGID and XLXSCLQD_XLBZID=XLBZID and XLXSCLQD_DYDJID=DYDJID");
|
||
sql.Add(" and XLXSCLQD_WPID='ZZZZ'");
|
||
sql.Add(" and XLXSCLQD_JGCZBID=:pJGCZBID and XLXSCLQD_JGCZBBBID=:pJGCZBBBID");
|
||
sql.Add(" ) ");
|
||
sql.Add(" when matched then update set ");
|
||
for (int i = 1; i < 80; i++)
|
||
{
|
||
sql.Add(string.Format("XLXSCLQD_SHL{0}=isnull(SHL{0},0),", i.ToString().PadLeft(2, '0')));
|
||
}
|
||
sql.Add("XLXSCLQD_SHL80=isnull(SHL80,0) ");
|
||
sql.ParamByName("pJGCZBID").Value = sJGCZBID;
|
||
sql.ParamByName("pJGCZBBBID").Value = sJGCZBBBID;
|
||
dberp.ExecSQL(sql);
|
||
}
|
||
else if (sTJYS.Equals("6"))
|
||
{
|
||
sql.Clear();
|
||
sql.Add(" merge into XLXSCLQD tar ");
|
||
sql.Add(" using ( ");
|
||
|
||
sql.Add(" select XLXSCLQD_XLXHID as XLXHID,XLXSCLQD_XLGGID as XLGGID,XLXSCLQD_XLBZID as XLBZID,XLXSCLQD_DYDJID as DYDJID,");
|
||
for (int i = 1; i < 80; i++)
|
||
{
|
||
sql.Add(string.Format("sum(case when XLJGCZBLM_CLQDL='{0}' then YZZ else 0 end) as SHL{1},", "第" + i.ToString().PadLeft(2, '0') + "列", i.ToString().PadLeft(2, '0')));
|
||
}
|
||
sql.Add(" sum(case when XLJGCZBLM_CLQDL='第80列' then YZZ else 0 end) as SHL80");
|
||
sql.Add(" from XLXSCLQD");
|
||
sql.Add(" left join XLJGCZBLM on XLJGCZBLM_JGCZBID=XLXSCLQD_JGCZBID and XLJGCZBLM_JGCZBBBID = XLXSCLQD_JGCZBBBID and isnull(XLJGCZBLM_CLQDL,' ')<>' '");
|
||
sql.Add(" left join");
|
||
sql.Add(" (select XLXSJGBYZZ6_XLXHID,XLXSJGBYZZ6_DYDJIDS,XLXSJGBYZZ6_BJCLID,sum(XLXSJGBYZZ6_YZZ) as YZZ");
|
||
sql.Add(" from XLXSTZYZ,XLXSJGBYZZ6");
|
||
sql.Add(" where XLXSTZYZ_MXBH='JZJ' and XLXSTZYZ_TJYS='6' and XLXSJGBYZZ6_JGBID=XLXSTZYZ_JGBID and XLXSJGBYZZ6_YZID=XLXSTZYZ_YZID");
|
||
sql.Add(" group by XLXSJGBYZZ6_XLXHID,XLXSJGBYZZ6_DYDJIDS,XLXSJGBYZZ6_BJCLID");
|
||
sql.Add(" ) JGBYZZ6 on XLXSJGBYZZ6_XLXHID=XLXSCLQD_XLXHID and (XLXSJGBYZZ6_DYDJIDS = ' ' or ',' || rtrim(XLXSJGBYZZ6_DYDJIDS) || ',' like '%,' || rtrim(XLXSCLQD_DYDJID) || ',%') and XLXSJGBYZZ6_BJCLID=XLJGCZBLM_BJCLID");
|
||
sql.Add(" where XLXSCLQD_WPID='ZZZZ'");
|
||
sql.Add(" and XLXSCLQD_JGCZBID=:pJGCZBID and XLXSCLQD_JGCZBBBID=:pJGCZBBBID ");
|
||
sql.Add(" group by XLXSCLQD_XLXHID,XLXSCLQD_XLGGID,XLXSCLQD_XLBZID,XLXSCLQD_DYDJID");
|
||
sql.Add(" ) sor ");
|
||
sql.Add(" on( ");
|
||
sql.Add(" XLXSCLQD_XLXHID=XLXHID and XLXSCLQD_XLGGID=XLGGID and XLXSCLQD_XLBZID=XLBZID and XLXSCLQD_DYDJID=DYDJID");
|
||
sql.Add(" and XLXSCLQD_WPID='ZZZZ'");
|
||
sql.Add(" and XLXSCLQD_JGCZBID=:pJGCZBID and XLXSCLQD_JGCZBBBID=:pJGCZBBBID");
|
||
sql.Add(" ) ");
|
||
sql.Add(" when matched then update set ");
|
||
for (int i = 1; i < 80; i++)
|
||
{
|
||
sql.Add(string.Format("XLXSCLQD_SHL{0}=isnull(SHL{0},0),", i.ToString().PadLeft(2, '0')));
|
||
}
|
||
sql.Add("XLXSCLQD_SHL80=isnull(SHL80,0) ");
|
||
sql.ParamByName("pJGCZBID").Value = sJGCZBID;
|
||
sql.ParamByName("pJGCZBBBID").Value = sJGCZBBBID;
|
||
dberp.ExecSQL(sql);
|
||
}
|
||
else if (sTJYS.Equals("7"))
|
||
{
|
||
sql.Clear();
|
||
sql.Add(" merge into XLXSCLQD tar ");
|
||
sql.Add(" using ( ");
|
||
|
||
sql.Add(" select XLXSCLQD_XLXHID as XLXHID,XLXSCLQD_XLGGID as XLGGID,XLXSCLQD_XLBZID as XLBZID,XLXSCLQD_DYDJID as DYDJID,");
|
||
for (int i = 1; i < 80; i++)
|
||
{
|
||
sql.Add(string.Format("sum(case when XLJGCZBLM_CLQDL='{0}' then YZZ else 0 end) as SHL{1},", "第" + i.ToString().PadLeft(2, '0') + "列", i.ToString().PadLeft(2, '0')));
|
||
}
|
||
sql.Add(" sum(case when XLJGCZBLM_CLQDL='第80列' then YZZ else 0 end) as SHL80");
|
||
sql.Add(" from XLXSCLQD");
|
||
sql.Add(" left join XLJGCZBLM on XLJGCZBLM_JGCZBID=XLXSCLQD_JGCZBID and XLJGCZBLM_JGCZBBBID = XLXSCLQD_JGCZBBBID and isnull(XLJGCZBLM_CLQDL,' ')<>' '");
|
||
sql.Add(" left join");
|
||
sql.Add(" (select XLXSJGBYZZ7_BJCLID,sum(XLXSJGBYZZ7_YZZ) as YZZ");
|
||
sql.Add(" from XLXSTZYZ,XLXSJGBYZZ7");
|
||
sql.Add(" where XLXSTZYZ_MXBH='JZJ' and XLXSTZYZ_TJYS='7' and XLXSJGBYZZ7_JGBID=XLXSTZYZ_JGBID and XLXSJGBYZZ7_YZID=XLXSTZYZ_YZID");
|
||
sql.Add(" group by XLXSJGBYZZ7_BJCLID");
|
||
sql.Add(" ) JGBYZZ7 on XLXSJGBYZZ7_BJCLID=XLJGCZBLM_BJCLID");
|
||
sql.Add(" where XLXSCLQD_WPID='ZZZZ'");
|
||
sql.Add(" and XLXSCLQD_JGCZBID=:pJGCZBID and XLXSCLQD_JGCZBBBID=:pJGCZBBBID ");
|
||
sql.Add(" group by XLXSCLQD_XLXHID,XLXSCLQD_XLGGID,XLXSCLQD_XLBZID,XLXSCLQD_DYDJID");
|
||
sql.Add(" ) sor ");
|
||
sql.Add(" on( ");
|
||
sql.Add(" XLXSCLQD_XLXHID=XLXHID and XLXSCLQD_XLGGID=XLGGID and XLXSCLQD_XLBZID=XLBZID and XLXSCLQD_DYDJID=DYDJID");
|
||
sql.Add(" and XLXSCLQD_WPID='ZZZZ'");
|
||
sql.Add(" and XLXSCLQD_JGCZBID=:pJGCZBID and XLXSCLQD_JGCZBBBID=:pJGCZBBBID");
|
||
sql.Add(" ) ");
|
||
sql.Add(" when matched then update set ");
|
||
for (int i = 1; i < 80; i++)
|
||
{
|
||
sql.Add(string.Format("XLXSCLQD_SHL{0}=isnull(SHL{0},0),", i.ToString().PadLeft(2, '0')));
|
||
}
|
||
sql.Add("XLXSCLQD_SHL80=isnull(SHL80,0) ");
|
||
sql.ParamByName("pJGCZBID").Value = sJGCZBID;
|
||
sql.ParamByName("pJGCZBBBID").Value = sJGCZBBBID;
|
||
dberp.ExecSQL(sql);
|
||
}
|
||
|
||
}
|
||
else
|
||
{
|
||
if (sTJYS.Equals("2"))
|
||
{
|
||
sql.Clear();
|
||
sql.Add(" merge into XLXSCLQD tar ");
|
||
sql.Add(" using ( ");
|
||
sql.Add(" select XLXSCLQD_XLXHID as XLXHID,XLXSCLQD_XLGGID as XLGGID,XLXSCLQD_XLBZID as XLBZID,XLXSCLQD_DYDJID as DYDJID,");
|
||
for (int i = 1; i < 80; i++)
|
||
{
|
||
sql.Add(string.Format("sum(case when XLJGCZBLM_CLQDL='{0}' then YZZ else 0 end) as SHL{1},", "第" + i.ToString().PadLeft(2, '0') + "列", i.ToString().PadLeft(2, '0')));
|
||
}
|
||
sql.Add(" sum(case when XLJGCZBLM_CLQDL='第80列' then YZZ else 0 end) as SHL80");
|
||
sql.Add(" from XLXSCLQD");
|
||
sql.Add(" left join XLXHB on XLXHB_XLXHID=XLXSCLQD_XLXHID");
|
||
sql.Add(" left join XLDYDJB on XLDYDJB_DYDJID=XLXSCLQD_DYDJID");
|
||
sql.Add(" left join XLJGCZBLM on XLJGCZBLM_JGCZBID=XLXSCLQD_JGCZBID and XLJGCZBLM_JGCZBBBID = XLXSCLQD_JGCZBBBID and isnull(XLJGCZBLM_CLQDL,' ')<>' '");
|
||
sql.Add(" left join");
|
||
sql.Add(" (select XLXSJGBYZZ2_CPYT,XLXSJGBYZZ2_DYDJFL,XLXSJGBYZZ2_DTCLID,XLXSJGBYZZ2_BJCLID,sum(XLXSJGBYZZ2_YZZ) as YZZ");
|
||
sql.Add(" from XLXSTZYZ,XLXSJGBYZZ2");
|
||
sql.Add(" where XLXSTZYZ_MXBH='JZJ' and XLXSTZYZ_TJYS='2' and XLXSJGBYZZ2_JGBID=XLXSTZYZ_JGBID and XLXSJGBYZZ2_YZID=XLXSTZYZ_YZID");
|
||
sql.Add(" group by XLXSJGBYZZ2_CPYT,XLXSJGBYZZ2_DYDJFL,XLXSJGBYZZ2_DTCLID,XLXSJGBYZZ2_BJCLID");
|
||
sql.Add(" ) JGBYZZ2 on XLXSJGBYZZ2_CPYT=XLXHB_CPYT and (XLXSJGBYZZ2_DYDJFL=' ' or XLXSJGBYZZ2_DYDJFL=XLDYDJB_DYDJFL) and (XLXSJGBYZZ2_DTCLID=' ' or XLXSJGBYZZ2_DTCLID=XLXHB_DTCLID) and XLXSJGBYZZ2_BJCLID=XLJGCZBLM_BJCLID");
|
||
sql.Add(" where XLXSCLQD_WPID='ZZZZ'");
|
||
sql.Add(" group by XLXSCLQD_XLXHID,XLXSCLQD_XLGGID,XLXSCLQD_XLBZID,XLXSCLQD_DYDJID");
|
||
sql.Add(" ) sor ");
|
||
sql.Add(" on( ");
|
||
sql.Add(" XLXSCLQD_XLXHID=XLXHID and XLXSCLQD_XLGGID=XLGGID and XLXSCLQD_XLBZID=XLBZID and XLXSCLQD_DYDJID=DYDJID");
|
||
sql.Add(" and XLXSCLQD_WPID='ZZZZ'");
|
||
sql.Add(" ) ");
|
||
sql.Add(" when matched then update set ");
|
||
for (int i = 1; i < 80; i++)
|
||
{
|
||
sql.Add(string.Format("XLXSCLQD_SHL{0}=isnull(SHL{0},0),", i.ToString().PadLeft(2, '0')));
|
||
}
|
||
sql.Add("XLXSCLQD_SHL80=isnull(SHL80,0) ");
|
||
dberp.ExecSQL(sql);
|
||
}
|
||
else if (sTJYS.Equals("6"))
|
||
{
|
||
sql.Clear();
|
||
sql.Add(" merge into XLXSCLQD tar ");
|
||
sql.Add(" using ( ");
|
||
|
||
sql.Add(" select XLXSCLQD_XLXHID as XLXHID,XLXSCLQD_XLGGID as XLGGID,XLXSCLQD_XLBZID as XLBZID,XLXSCLQD_DYDJID as DYDJID,");
|
||
for (int i = 1; i < 80; i++)
|
||
{
|
||
sql.Add(string.Format("sum(case when XLJGCZBLM_CLQDL='{0}' then YZZ else 0 end) as SHL{1},", "第" + i.ToString().PadLeft(2, '0') + "列", i.ToString().PadLeft(2, '0')));
|
||
}
|
||
sql.Add(" sum(case when XLJGCZBLM_CLQDL='第80列' then YZZ else 0 end) as SHL80");
|
||
sql.Add(" from XLXSCLQD");
|
||
sql.Add(" left join XLJGCZBLM on XLJGCZBLM_JGCZBID=XLXSCLQD_JGCZBID and XLJGCZBLM_JGCZBBBID = XLXSCLQD_JGCZBBBID and isnull(XLJGCZBLM_CLQDL,' ')<>' '");
|
||
sql.Add(" left join");
|
||
sql.Add(" (select XLXSJGBYZZ6_XLXHID,XLXSJGBYZZ6_DYDJIDS,XLXSJGBYZZ6_BJCLID,sum(XLXSJGBYZZ6_YZZ) as YZZ");
|
||
sql.Add(" from XLXSTZYZ,XLXSJGBYZZ6");
|
||
sql.Add(" where XLXSTZYZ_MXBH='JZJ' and XLXSTZYZ_TJYS='6' and XLXSJGBYZZ6_JGBID=XLXSTZYZ_JGBID and XLXSJGBYZZ6_YZID=XLXSTZYZ_YZID");
|
||
sql.Add(" group by XLXSJGBYZZ6_XLXHID,XLXSJGBYZZ6_DYDJIDS,XLXSJGBYZZ6_BJCLID");
|
||
sql.Add(" ) JGBYZZ6 on XLXSJGBYZZ6_XLXHID=XLXSCLQD_XLXHID and (XLXSJGBYZZ6_DYDJIDS = ' ' or ',' || rtrim(XLXSJGBYZZ6_DYDJIDS) || ',' like '%,' || rtrim(XLXSCLQD_DYDJID) || ',%') and XLXSJGBYZZ6_BJCLID=XLJGCZBLM_BJCLID");
|
||
sql.Add(" where XLXSCLQD_WPID='ZZZZ' ");
|
||
sql.Add(" group by XLXSCLQD_XLXHID,XLXSCLQD_XLGGID,XLXSCLQD_XLBZID,XLXSCLQD_DYDJID");
|
||
sql.Add(" ) sor ");
|
||
sql.Add(" on( ");
|
||
sql.Add(" XLXSCLQD_XLXHID=XLXHID and XLXSCLQD_XLGGID=XLGGID and XLXSCLQD_XLBZID=XLBZID and XLXSCLQD_DYDJID=DYDJID");
|
||
sql.Add(" and XLXSCLQD_WPID='ZZZZ'");
|
||
sql.Add(" ) ");
|
||
sql.Add(" when matched then update set ");
|
||
for (int i = 1; i < 80; i++)
|
||
{
|
||
sql.Add(string.Format("XLXSCLQD_SHL{0}=isnull(SHL{0},0),", i.ToString().PadLeft(2, '0')));
|
||
}
|
||
sql.Add("XLXSCLQD_SHL80=isnull(SHL80,0) ");
|
||
dberp.ExecSQL(sql);
|
||
}
|
||
else if (sTJYS.Equals("7"))
|
||
{
|
||
sql.Clear();
|
||
sql.Add(" merge into XLXSCLQD tar ");
|
||
sql.Add(" using ( ");
|
||
|
||
sql.Add(" select XLXSCLQD_XLXHID as XLXHID,XLXSCLQD_XLGGID as XLGGID,XLXSCLQD_XLBZID as XLBZID,XLXSCLQD_DYDJID as DYDJID,");
|
||
for (int i = 1; i < 80; i++)
|
||
{
|
||
sql.Add(string.Format("sum(case when XLJGCZBLM_CLQDL='{0}' then YZZ else 0 end) as SHL{1},", "第" + i.ToString().PadLeft(2, '0') + "列", i.ToString().PadLeft(2, '0')));
|
||
}
|
||
sql.Add(" sum(case when XLJGCZBLM_CLQDL='第80列' then YZZ else 0 end) as SHL80");
|
||
sql.Add(" from XLXSCLQD");
|
||
sql.Add(" left join XLJGCZBLM on XLJGCZBLM_JGCZBID=XLXSCLQD_JGCZBID and XLJGCZBLM_JGCZBBBID = XLXSCLQD_JGCZBBBID and isnull(XLJGCZBLM_CLQDL,' ')<>' '");
|
||
sql.Add(" left join");
|
||
sql.Add(" (select XLXSJGBYZZ7_BJCLID,sum(XLXSJGBYZZ7_YZZ) as YZZ");
|
||
sql.Add(" from XLXSTZYZ,XLXSJGBYZZ7");
|
||
sql.Add(" where XLXSTZYZ_MXBH='JZJ' and XLXSTZYZ_TJYS='7' and XLXSJGBYZZ7_JGBID=XLXSTZYZ_JGBID and XLXSJGBYZZ7_YZID=XLXSTZYZ_YZID");
|
||
sql.Add(" group by XLXSJGBYZZ7_BJCLID");
|
||
sql.Add(" ) JGBYZZ6 on XLXSJGBYZZ7_BJCLID=XLJGCZBLM_BJCLID");
|
||
sql.Add(" where XLXSCLQD_WPID='ZZZZ' ");
|
||
sql.Add(" group by XLXSCLQD_XLXHID,XLXSCLQD_XLGGID,XLXSCLQD_XLBZID,XLXSCLQD_DYDJID");
|
||
sql.Add(" ) sor ");
|
||
sql.Add(" on( ");
|
||
sql.Add(" XLXSCLQD_XLXHID=XLXHID and XLXSCLQD_XLGGID=XLGGID and XLXSCLQD_XLBZID=XLBZID and XLXSCLQD_DYDJID=DYDJID");
|
||
sql.Add(" and XLXSCLQD_WPID='ZZZZ'");
|
||
sql.Add(" ) ");
|
||
sql.Add(" when matched then update set ");
|
||
for (int i = 1; i < 80; i++)
|
||
{
|
||
sql.Add(string.Format("XLXSCLQD_SHL{0}=isnull(SHL{0},0),", i.ToString().PadLeft(2, '0')));
|
||
}
|
||
sql.Add("XLXSCLQD_SHL80=isnull(SHL80,0) ");
|
||
dberp.ExecSQL(sql);
|
||
}
|
||
}
|
||
|
||
}
|
||
|
||
public static void UpdatCLZL(TDatabase dberp, TSQL sql, string sJGCZBID, string sJGCZBBBID, string sXLXHID, string sXLGGID, string sXLBZID, string sDYDJID)
|
||
{
|
||
sql.Clear();
|
||
sql.Add(" select XLJGCZBLM_CLQDL,XLXSBJCL_JTZ,XLXSBJCL_JLZ ");
|
||
sql.Add(" from XLJGCZBLM ");
|
||
sql.Add(" left join XLXSBJCL on XLXSBJCL_BJCLID=XLJGCZBLM_BJCLID");
|
||
sql.Add(" where XLJGCZBLM_JGCZBID=:pJGCZBID and XLJGCZBLM_JGCZBBBID =:pJGCZBBBID and isnull(XLJGCZBLM_CLQDL,' ')<>' '");
|
||
sql.Add(" and isnull(XLXSBJCL_BJJZ,'Y')<>'N'");//报价计重
|
||
sql.Add(" order by XLJGCZBLM_CLQDL ");
|
||
sql.ParamByName("pJGCZBID").Value = sJGCZBID;
|
||
sql.ParamByName("pJGCZBBBID").Value = sJGCZBBBID;
|
||
DataSet ds = dberp.OpenDataSet(sql);
|
||
|
||
sql.Clear();
|
||
sql.Add("update XLXSCLQD set ");
|
||
if (ds.Tables[0].Rows.Count > 0)
|
||
{
|
||
sql.Add("XLXSCLQD_CLZL=(");
|
||
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
|
||
{
|
||
if (i == 0)
|
||
sql.Add(string.Format("XLXSCLQD_SL{0}*XLXSCLQD_CLJGBL{0}*(1+XLXSCLQD_SHL{0}/100)", ds.Tables[0].Rows[i]["XLJGCZBLM_CLQDL"].ToString().Trim().Replace("第", "").Replace("列", "")));
|
||
else
|
||
sql.Add(string.Format("+XLXSCLQD_SL{0}*XLXSCLQD_CLJGBL{0}*(1+XLXSCLQD_SHL{0}/100)", ds.Tables[0].Rows[i]["XLJGCZBLM_CLQDL"].ToString().Trim().Replace("第", "").Replace("列", "")));
|
||
}
|
||
sql.Add("), ");
|
||
|
||
sql.Add("XLXSCLQD_TZL=0");
|
||
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
|
||
{
|
||
if (ds.Tables[0].Rows[i]["XLXSBJCL_JTZ"].ToString().Trim().Equals("Y"))//计铜重
|
||
{
|
||
sql.Add(string.Format("+XLXSCLQD_SL{0}*XLXSCLQD_CLJGBL{0}*(1+XLXSCLQD_SHL{0}/100)", ds.Tables[0].Rows[i]["XLJGCZBLM_CLQDL"].ToString().Trim().Replace("第", "").Replace("列", "")));
|
||
}
|
||
}
|
||
sql.Add(", ");
|
||
|
||
sql.Add("XLXSCLQD_LZL=0");
|
||
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
|
||
{
|
||
if (ds.Tables[0].Rows[i]["XLXSBJCL_JLZ"].ToString().Trim().Equals("Y"))//计铜重
|
||
{
|
||
sql.Add(string.Format("+XLXSCLQD_SL{0}*XLXSCLQD_CLJGBL{0}*(1+XLXSCLQD_SHL{0}/100)", ds.Tables[0].Rows[i]["XLJGCZBLM_CLQDL"].ToString().Trim().Replace("第", "").Replace("列", "")));
|
||
}
|
||
}
|
||
}
|
||
else
|
||
sql.Add("XLXSCLQD_CLZL=0,XLXSCLQD_TZL=0,XLXSCLQD_LZL=0");
|
||
sql.Add(" where XLXSCLQD_WPID ='ZZZZ' and XLXSCLQD_JGCZBID = :pJGCZBID and XLXSCLQD_JGCZBBBID = :pJGCZBBBID ");
|
||
sql.Add(" and XLXSCLQD_XLXHID=:pXLXHID and XLXSCLQD_XLGGID=:pXLGGID");
|
||
sql.Add(" and XLXSCLQD_XLBZID=:pXLBZID and XLXSCLQD_DYDJID=:pDYDJID");
|
||
sql.ParamByName("pJGCZBID").Value = sJGCZBID;
|
||
sql.ParamByName("pJGCZBBBID").Value = sJGCZBBBID;
|
||
sql.ParamByName("pXLXHID").Value = sXLXHID;
|
||
sql.ParamByName("pXLGGID").Value = sXLGGID;
|
||
sql.ParamByName("pXLBZID").Value = sXLBZID;
|
||
sql.ParamByName("pDYDJID").Value = sDYDJID;
|
||
dberp.ExecSQL(sql);
|
||
}
|
||
|
||
//计算材料损耗率
|
||
protected void btnJSSHL_OnClick(object sender, EventArgs e)
|
||
{
|
||
//查询调价因素为‘2、6、7’的调价因子
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
TSQL sql = new TSQL(dberp);
|
||
sql.Add("select XLXSTZYZ_YZID,XLXSTZYZ_YZMC,XLXSTZYZ_TJYS from XLXSTZYZ ");
|
||
sql.Add("where XLXSTZYZ_MXBH='JZJ' and (XLXSTZYZ_TJYS='2' or XLXSTZYZ_TJYS='6' or XLXSTZYZ_TJYS='7') ");
|
||
sql.Add("order by XLXSTZYZ_XSXH,XLXSTZYZ_YZID");
|
||
DataSet ds = dberp.OpenDataSet(sql);
|
||
if (ds.Tables[0].Rows.Count <= 0)
|
||
{
|
||
ShowMessage("还没有指定调整材料损耗率的因子！");
|
||
return;
|
||
}
|
||
string sTJYS = ds.Tables[0].Rows[0]["XLXSTZYZ_TJYS"].ToString().Trim();
|
||
|
||
string sYZMCList = "";
|
||
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
|
||
{
|
||
sYZMCList += ds.Tables[0].Rows[i]["XLXSTZYZ_YZMC"].ToString().Trim() + ",";
|
||
}
|
||
sYZMCList = sYZMCList.TrimEnd(',');
|
||
|
||
base.InitEntityByCondition();
|
||
string sMsg = "";
|
||
if (string.IsNullOrEmpty(CurQueryEntity.FieldByName("JGCZBID").Value.Trim()))
|
||
{
|
||
sMsg = string.Format("所有尺重表下的材料损耗率都将全部清0，并按【{0}】重新设置，是否继续？", sYZMCList);
|
||
}
|
||
else
|
||
sMsg = string.Format("当前尺重表下的材料损耗率将全部清0，并按【{0}】重新设置，是否继续？", sYZMCList);
|
||
|
||
Confirm.Register("IsQD", sMsg);
|
||
Confirm.ShowBtnText = TConfirm.cBtnText.Yes_No;
|
||
if (Confirm.GetValue("IsQD").Trim().Equals(""))
|
||
{
|
||
Confirm.Ask("IsQD");
|
||
return;
|
||
}
|
||
else if (Confirm.GetValue("IsQD").Trim().Equals("N"))
|
||
{
|
||
Confirm.Clear();
|
||
return;
|
||
}
|
||
else if (Confirm.GetValue("IsQD").Trim().Equals("Y"))
|
||
{
|
||
Confirm.Clear();
|
||
}
|
||
|
||
UpdateSHL(dberp, sql, CurQueryEntity.FieldByName("JGCZBID").Value.Trim().Split('|')[0], CurQueryEntity.FieldByName("JGCZBBBID").Value.Trim(), sTJYS);
|
||
|
||
UpdateCLZL(dberp, sql);
|
||
|
||
ShowMessage("材料损耗率计算完成、材料重量也一并更新了！");
|
||
}
|
||
|
||
protected void btnPX_MenuItemClick(object sender, string ItemIndex)
|
||
{
|
||
int index = Int32.Parse(ItemIndex);
|
||
switch (index)
|
||
{
|
||
case 0:
|
||
sort_XHBZGGDY();
|
||
break;
|
||
}
|
||
}
|
||
|
||
//型号+标准+规格+电压等级
|
||
private void sort_XHBZGGDY()
|
||
{
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
TSQL SQL = new TSQL(dberp);
|
||
|
||
try
|
||
{
|
||
/*
|
||
merge into 目标表 a
|
||
using 源表 b
|
||
on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)
|
||
when matched then update set a.更新字段=b.字段
|
||
when not matched then insert into a(字段1,字段2……)values(值1,值2……)
|
||
*/
|
||
dberp.BeginTrans();
|
||
SQL.Clear();
|
||
SQL.Add(" merge into XLXSCLQD a ");
|
||
SQL.Add(" using ");
|
||
SQL.Add("( ");
|
||
SQL.Add(" select XLXHID,XLGGID,XLBZID,DYDJID,min(XH) AS XH from ");
|
||
SQL.Add(" ( ");
|
||
SQL.Add(" select ");
|
||
SQL.Add(" XLXSCLQD_XLXHID as XLXHID,XLXSCLQD_XLGGID as XLGGID, ");
|
||
SQL.Add(" XLXSCLQD_XLBZID as XLBZID,XLXSCLQD_DYDJID as DYDJID, ");
|
||
SQL.Add(" row_number() ");
|
||
SQL.Add(" over(order by XLXSCLQD_XLXHID,XLXSCLQD_XLBZID,XLGGB_XH,XLXSCLQD_XLGGID,XLXSCLQD_DYDJID ) as XH ");
|
||
SQL.Add(" from XLXSCLQD");
|
||
SQL.Add(" left join XLGGB on XLGGB_XLGGID=replace(XLXSCLQD_XLGGID,'*','×') ");
|
||
SQL.Add(" where XLXSCLQD_WPID = 'ZZZZ' ");
|
||
SQL.Add(" ) c ");
|
||
SQL.Add(" group by XLXHID,XLGGID,XLBZID,DYDJID ");
|
||
SQL.Add(" ) b ");
|
||
SQL.Add(" on( ");
|
||
SQL.Add(" a.XLXSCLQD_XLXHID=b.XLXHID ");
|
||
SQL.Add(" and a.XLXSCLQD_XLGGID=b.XLGGID ");
|
||
SQL.Add(" and a.XLXSCLQD_XLBZID=b.XLBZID ");
|
||
SQL.Add(" and a.XLXSCLQD_DYDJID=b.DYDJID ");
|
||
SQL.Add(" ) ");
|
||
SQL.Add(" when matched then update set a.XLXSCLQD_XH=b.XH ");
|
||
string s = SQL.Text;
|
||
dberp.ExecSQL(SQL);
|
||
|
||
moduleGV_PageIndexChanging(null, null);
|
||
FormAreaUpdate();
|
||
ShowMessage("已按照型号、标准、规格、电压等级排序");
|
||
dberp.CommitTrans();
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
dberp.RollbackTrans();
|
||
ShowMessage(ex.Message);
|
||
return;
|
||
}
|
||
}
|
||
#endregion
|
||
}
|
||
}
|