952 lines
48 KiB
C#
952 lines
48 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;
|
||
using GThinking.N_MISPRO.Data;
|
||
using GThinking.N_MODEL.Page;
|
||
using GThinking.N_CONTROL;
|
||
using GThinking.N_PUBLIC;
|
||
using GThinking.N_MISPRO.Caching;
|
||
using GThinking.N_MISPRO.Utility;
|
||
using System.Collections.Generic;
|
||
using System.Text;
|
||
|
||
namespace GThinking.N_XLXSBJ
|
||
{
|
||
public partial class XLBJD_CBZSList : SingleGridBase
|
||
{
|
||
XLBJD_CBZS entity = new XLBJD_CBZS();
|
||
|
||
protected void Page_Load(object sender, EventArgs e)
|
||
{
|
||
this.UMID = 100860044;
|
||
this.UMName = "成本追溯";
|
||
this.CurMaster = Master;
|
||
this.ModuleGV = grdContentGrid;
|
||
this.CurEntity = entity;
|
||
this.PageWidth = 700;
|
||
this.PageHeight = 500;
|
||
|
||
Master.EnableQueryBtn = false;
|
||
Master.EnableAttachBtn = false;
|
||
Master.EnableNewBtn = false;
|
||
Master.EnableViewBtn = false;
|
||
Master.EnableEditBtn = false;
|
||
Master.EnableCopyBtn = false;
|
||
Master.EnableDeleteBtn = false;
|
||
|
||
base.InitPage();
|
||
ModuleGV.OnClientDblClick = "return false;";
|
||
|
||
grdContentGrid.FindColumnByFieldName("px0").Visible = false;
|
||
if (GetOption(100860000, "BJU002") != null)
|
||
{
|
||
grdContentGrid.FindColumnExByFieldName("CLCB").NumberDecimalPlace = CMISproConvert.StrToInt(GetOption(100860000, "BJU002"), 2);
|
||
txtCLCB.DecimalPlaces = CMISproConvert.StrToInt(GetOption(100860000, "BJU002"), 2);
|
||
}
|
||
|
||
if (GetOption(100860000, "BJU003") != null)
|
||
{
|
||
grdContentGrid.FindColumnByFieldName("CLSL").HeaderText = "材料用量(Kg/" + GetOption(100860000, "BJU003") + ")";
|
||
grdContentGrid.FindColumnByFieldName("CLCB").HeaderText = "材料成本(元/" + GetOption(100860000, "BJU003") + ")";
|
||
lblDW.Text = "元/" + GetOption(100860000, "BJU003") + "";
|
||
}
|
||
|
||
if (!IsPostBack)
|
||
{
|
||
Master_OnStandardQuery(null, null);
|
||
|
||
InitControl();
|
||
}
|
||
grdContentGrid.FindColumnExByFieldName("CLJG").NumberDecimalPlace = XLBJDUtils.BJCLPriceDecimal;
|
||
}
|
||
|
||
#region 模板继承事件
|
||
|
||
protected override void Master_OnStandardQuery(object sender, EventArgs e)
|
||
{
|
||
entity.EntityTable.QuerySelSql = "select 1 from XLXSCLQD where 1<>1";
|
||
base.Master_OnStandardQuery(sender, e);
|
||
DataSet ds = GetTable();
|
||
entity.GridDataBind(grdContentGrid, tSession);
|
||
entity.GridDataBind(grdContentGrid, ds.Tables[0], ds.Tables[0].Rows.Count);
|
||
grdContentGrid.DataSource = ds;
|
||
}
|
||
|
||
protected override void moduleGV_PageIndexChanging(object sender, GridViewPageEventArgs e)
|
||
{
|
||
entity.EntityTable.QuerySelSql = "select 1 from XLXSCLQD where 1<>1";
|
||
base.moduleGV_PageIndexChanging(sender, e);
|
||
grdContentGrid.SelectedIndex = -1;
|
||
DataSet ds = GetTable();
|
||
entity.GridDataBind(grdContentGrid, tSession);
|
||
entity.GridDataBind(grdContentGrid, ds.Tables[0], ds.Tables[0].Rows.Count);
|
||
grdContentGrid.DataSource = ds;
|
||
}
|
||
|
||
protected override void moduleGV_RowDataBound(object sender, GridViewRowEventArgs e)
|
||
{
|
||
if (e.Row.RowType == DataControlRowType.DataRow)
|
||
{
|
||
//TZCLHQ=Y说明可以调整材料行情
|
||
if (PageParam.GetRTrimString("TZCLHQ").Equals("Y") && PageParam.GetRTrimString("CANEDIT").Equals("Y"))
|
||
{
|
||
//材料价格
|
||
e.Row.Cells[4].Text = string.Format("<A href='#' onclick=\"CellClick('{0}','{1}','{2}') \"><U>" + strPadRight(e.Row.Cells[4].Text.ToString().Trim(),XLBJDUtils.BJCLPriceDecimal) + "</U></A>",
|
||
e.Row.Cells[0].Text.Trim(), e.Row.Cells[1].Text.Trim(), e.Row.Cells[3].Text.Trim());
|
||
}
|
||
}
|
||
base.moduleGV_RowDataBound(sender, e);
|
||
}
|
||
|
||
protected override void ReportRecordSetAdd()
|
||
{
|
||
DataSet ds = GetTable();
|
||
AddUserReportDataSet("XLXSBJDMXCLCB", ds);
|
||
base.ReportRecordSetAdd();
|
||
}
|
||
|
||
protected override void ReportDataSetAdd()
|
||
{
|
||
DataSet ds = GetTable();
|
||
for (int i = 0; i < grdContentGrid.Columns.Count; i++)
|
||
{
|
||
string sDataField = ((System.Web.UI.WebControls.BoundField)(grdContentGrid.Columns[i])).DataField.ToString().Trim();
|
||
string sHeaderText = grdContentGrid.Columns[i].HeaderText;
|
||
AddUserReportField("XLXSBJDMXCLCB", sDataField, sHeaderText, "S");
|
||
}
|
||
base.ReportDataSetAdd();
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 自定义事件
|
||
private void InitControl()
|
||
{
|
||
int iSYXH = 0;
|
||
double dYSJDZ = 1;
|
||
double dCLCBYSJDZ = 1;
|
||
string sJGBID = PageParam.GetRTrimString("JGBID");
|
||
string sXLXHID = PageParam.GetRTrimString("XLXHID");
|
||
string sXLGGID = PageParam.GetRTrimString("XLGGID");
|
||
string sXLBZID = PageParam.GetRTrimString("XLBZID");
|
||
string sDYDJID = PageParam.GetRTrimString("DYDJID");
|
||
string sJGBTJYS = XLBJDUtils.GetXLXSJGBXXByJGBID(sJGBID).XLXSJGB_TJYS;
|
||
string sCPLJGS = XLBJDUtils.GetCPLJGS();
|
||
bool bHasCLQD = HasCLQD(sXLXHID, sXLGGID, sXLBZID, sDYDJID);
|
||
string sTempXLXHID = sXLXHID;
|
||
if (XLBJDUtils.ExistedSYJGBMX(sXLXHID, sXLGGID, sXLBZID, sDYDJID, sJGBID, ref iSYXH, ref dYSJDZ,ref dCLCBYSJDZ) && !bHasCLQD)
|
||
{
|
||
lblYSCP.Visible = true;
|
||
lblYSCP.Text = GetBJCP(sXLXHID, sXLGGID, GetXLBZXX(sXLBZID), sDYDJID);
|
||
if ((sJGBTJYS == "1" || sJGBTJYS == "3") && (sCPLJGS == "2" || sCPLJGS == "4"))
|
||
{
|
||
lblZHJD.Visible = true;
|
||
lblZHJD.Text = dYSJDZ.ToString();
|
||
if(sCPLJGS == "4")
|
||
lblZHJD.Text = dCLCBYSJDZ.ToString();
|
||
|
||
}
|
||
XLBJDUtils.GetJGBMXBySYXH(sJGBID, iSYXH, ref sXLXHID, ref sXLGGID, ref sXLBZID, ref sDYDJID);
|
||
|
||
}
|
||
|
||
if (!bHasCLQD && sCPLJGS == "3")
|
||
{
|
||
XLBJDUtils.GetCLQDLJJDZ(sDYDJID, ref sTempXLXHID, ref dYSJDZ);
|
||
if (dYSJDZ > 1)
|
||
{
|
||
lblZHJD.Visible = true;
|
||
lblZHJD.Text = dYSJDZ.ToString();
|
||
sXLXHID = sTempXLXHID;
|
||
}
|
||
}
|
||
|
||
lblBJCP.Text = GetBJCP(sXLXHID, sXLGGID, GetXLBZXX(sXLBZID), sDYDJID);
|
||
}
|
||
private DataSet GetTable()
|
||
{
|
||
string sBJDID = PageParam.GetRTrimString("BJDID");
|
||
string sBJRQ = PageParam.GetRTrimString("BJRQ");
|
||
string sBJFLID = PageParam.GetRTrimString("BJFLID");
|
||
string sJGBID = PageParam.GetRTrimString("JGBID");
|
||
string sXLXHID = PageParam.GetRTrimString("XLXHID");
|
||
string sXLGGID = PageParam.GetRTrimString("XLGGID");
|
||
string sXLBZID = PageParam.GetRTrimString("XLBZID");
|
||
string sDYDJID = PageParam.GetRTrimString("DYDJID");
|
||
string sBJDHQLY = PageParam.GetRTrimString("BJDHQLY");
|
||
bool bHSBZ = PageParam.GetRTrimString("HSBZ").Equals("Y");
|
||
|
||
string sTBJCLID = PageParam.GetRTrimString("TBJCLID");
|
||
string sLBJCLID = PageParam.GetRTrimString("LBJCLID");
|
||
string sTJ = PageParam.GetRTrimString("TJ");
|
||
string sLJ = PageParam.GetRTrimString("LJ");
|
||
int iSYXH = 0;
|
||
double dYSJDZ = 1;
|
||
double dCLCBYSJDZ = 1;
|
||
string sCPLJGS = XLBJDUtils.GetCPLJGS();
|
||
bool bHasCLQD = HasCLQD(sXLXHID, sXLGGID, sXLBZID, sDYDJID);
|
||
bool bExistedSYJGBMX = XLBJDUtils.ExistedSYJGBMX(sXLXHID, sXLGGID, sXLBZID, sDYDJID, sJGBID, ref iSYXH, ref dYSJDZ, ref dCLCBYSJDZ);
|
||
if (!bHasCLQD && sCPLJGS == "3")
|
||
XLBJDUtils.GetCLQDLJJDZ(sDYDJID, ref sXLXHID, ref dYSJDZ);
|
||
else if (bExistedSYJGBMX && !bHasCLQD)
|
||
XLBJDUtils.GetJGBMXBySYXH(sJGBID, iSYXH, ref sXLXHID, ref sXLGGID, ref sXLBZID, ref sDYDJID);
|
||
Dictionary<string, string> dicCLHQ = new Dictionary<string, string>();
|
||
Dictionary<string, string> dicWLHQ = new Dictionary<string, string>();
|
||
DataSet dsWLHQ = null;
|
||
|
||
string sJSBLFS = XLBJDUtils.GetXLXSJGBXXByJGBID(sJGBID).XLXSJGB_JSBLFS;
|
||
|
||
int iXSWS = string.IsNullOrEmpty(GetOption(100860000, "BJU002")) ? 5 : CMISproConvert.StrToInt(GetOption(100860000, "BJU002"));
|
||
if (iXSWS < 0)
|
||
{
|
||
iXSWS = 0;
|
||
}
|
||
else if (iXSWS > 8)
|
||
{
|
||
iXSWS = 8;
|
||
}
|
||
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
TSQL tSql = new TSQL(dberp);
|
||
tSql.Clear();
|
||
tSql.Add("select XLJGCZBLM_CLQDL,XLJGCZBLM_BJCLID,XLXSBJCL_BJHQ");
|
||
tSql.Add(" from XLJGCZBLM");
|
||
tSql.Add(" left join XLXSCLQD on XLXSCLQD_JGCZBID=XLJGCZBLM_JGCZBID and XLXSCLQD_JGCZBBBID = XLJGCZBLM_JGCZBBBID ");
|
||
tSql.Add(" left join XLXSBJCL on XLXSBJCL_BJCLID=XLJGCZBLM_BJCLID");
|
||
tSql.Add(" where XLXSCLQD_XLXHID=:pXLXHID and XLXSCLQD_XLGGID=:pXLGGID and XLXSCLQD_XLBZID=:pXLBZID and XLXSCLQD_DYDJID=:pDYDJID");
|
||
tSql.Add(" and XLXSCLQD_WPID='ZZZZ'");
|
||
tSql.Add(" and isnull(XLJGCZBLM_CLQDL,' ')<>' ' ");
|
||
tSql.Add(" order by XLJGCZBLM_CLQDL");
|
||
tSql.ParamByName("pXLXHID").Value = sXLXHID;
|
||
tSql.ParamByName("pXLGGID").Value = sXLGGID;
|
||
tSql.ParamByName("pXLBZID").Value = sXLBZID;
|
||
tSql.ParamByName("pDYDJID").Value = sDYDJID;
|
||
DataSet dsCZB = dberp.OpenDataSet(tSql);
|
||
for (int i = 0; i < dsCZB.Tables[0].Rows.Count; i++)
|
||
{
|
||
if (dsCZB.Tables[0].Rows[i]["XLXSBJCL_BJHQ"].ToString().Trim().Equals("1"))//1|材料行情
|
||
dicCLHQ.Add(dsCZB.Tables[0].Rows[i]["XLJGCZBLM_CLQDL"].ToString().Trim(), dsCZB.Tables[0].Rows[i]["XLJGCZBLM_BJCLID"].ToString().Trim());
|
||
else if (dsCZB.Tables[0].Rows[i]["XLXSBJCL_BJHQ"].ToString().Trim().Equals("2"))//2|物料行情
|
||
dicWLHQ.Add(dsCZB.Tables[0].Rows[i]["XLJGCZBLM_CLQDL"].ToString().Trim(), dsCZB.Tables[0].Rows[i]["XLJGCZBLM_BJCLID"].ToString().Trim());
|
||
}
|
||
//存在物料行情材料
|
||
if (dicWLHQ.Keys.Count > 0)
|
||
{
|
||
tSql.Clear();
|
||
tSql.Add("select XLXSCLQD_JGCZBID,XLXSCLQD_JGCZBBBID,XLXSCLQD_JGCZBXH,XLXSCLQD_DRHH");
|
||
tSql.Add(" from XLXSCLQD ");
|
||
tSql.Add(" where XLXSCLQD_XLXHID=:pXLXHID and XLXSCLQD_XLGGID=:pXLGGID and XLXSCLQD_XLBZID=:pXLBZID and XLXSCLQD_DYDJID=:pDYDJID");
|
||
tSql.ParamByName("pXLXHID").Value = sXLXHID;
|
||
tSql.ParamByName("pXLGGID").Value = sXLGGID;
|
||
tSql.ParamByName("pXLBZID").Value = sXLBZID;
|
||
tSql.ParamByName("pDYDJID").Value = sDYDJID;
|
||
DataSet dsCZBXL = dberp.OpenDataSet(tSql);
|
||
if (dsCZBXL.Tables[0].Rows.Count > 0)
|
||
{
|
||
string sJGCZBID = dsCZBXL.Tables[0].Rows[0]["XLXSCLQD_JGCZBID"].ToString().Trim();
|
||
string sJGCZBBBID = dsCZBXL.Tables[0].Rows[0]["XLXSCLQD_JGCZBBBID"].ToString().Trim();
|
||
string sJGCZBXH = dsCZBXL.Tables[0].Rows[0]["XLXSCLQD_JGCZBXH"].ToString().Trim();
|
||
string sDRHH = dsCZBXL.Tables[0].Rows[0]["XLXSCLQD_DRHH"].ToString().Trim();
|
||
|
||
#region 创建临时表
|
||
DateTime TimeStamp = MISproUtils.GetServerTime();
|
||
int VnewID = CMISproDataBase.GetMaxXH("DBERP", "V_XLXSCLQDWLHQ", "V_XLXSCLQDWLHQ_ID", "") + 1;
|
||
|
||
tSql.Clear();
|
||
tSql.Add(@"insert into V_XLXSCLQDWLHQ(V_XLXSCLQDWLHQ_ID,V_XLXSCLQDWLHQ_TIMESTAMP,V_XLXSCLQDWLHQ_JGCZBID,V_XLXSCLQDWLHQ_JGCZBBBID,V_XLXSCLQDWLHQ_JGCZBXH,V_XLXSCLQDWLHQ_DRHH,V_XLXSCLQDWLHQ_HSBZ)
|
||
select :pID,:pTime,XLXSCLQD_JGCZBID, XLXSCLQD_JGCZBBBID,XLXSCLQD_JGCZBXH,XLXSCLQD_DRHH,:pHSBZ from XLXSCLQD
|
||
where XLXSCLQD_JGCZBID=:pJGCZBID and XLXSCLQD_JGCZBBBID=:pJGCZBBBID and XLXSCLQD_JGCZBXH=:pJGCZBXH and XLXSCLQD_DRHH=:pDRHH
|
||
");
|
||
tSql.ParamByName("pID").Value = VnewID;
|
||
tSql.ParamByName("pTime").Value = TimeStamp;
|
||
tSql.ParamByName("pHSBZ").Value = "N";
|
||
tSql.ParamByName("pJGCZBID").Value = sJGCZBID;
|
||
tSql.ParamByName("pJGCZBBBID").Value = sJGCZBBBID;
|
||
tSql.ParamByName("pJGCZBXH").Value = CMISproConvert.StrToFloat(sJGCZBXH);
|
||
tSql.ParamByName("pDRHH").Value = CMISproConvert.StrToFloat(sDRHH);
|
||
dberp.ExecSQL(tSql);
|
||
|
||
|
||
tSql.Clear();
|
||
tSql.Add(@"insert into V_XLXSCLQDWLHQ(V_XLXSCLQDWLHQ_ID,V_XLXSCLQDWLHQ_TIMESTAMP,V_XLXSCLQDWLHQ_JGCZBID,V_XLXSCLQDWLHQ_JGCZBBBID,V_XLXSCLQDWLHQ_JGCZBXH,V_XLXSCLQDWLHQ_DRHH,V_XLXSCLQDWLHQ_HSBZ)
|
||
select :pID,:pTime,XLXSCLQD_JGCZBID, XLXSCLQD_JGCZBBBID,XLXSCLQD_JGCZBXH,XLXSCLQD_DRHH,:pHSBZ from XLXSCLQD
|
||
where XLXSCLQD_JGCZBID=:pJGCZBID and XLXSCLQD_JGCZBBBID=:pJGCZBBBID and XLXSCLQD_JGCZBXH=:pJGCZBXH and XLXSCLQD_DRHH=:pDRHH
|
||
");
|
||
tSql.ParamByName("pID").Value = VnewID;
|
||
tSql.ParamByName("pTime").Value = TimeStamp;
|
||
tSql.ParamByName("pHSBZ").Value = "Y";
|
||
tSql.ParamByName("pJGCZBID").Value = sJGCZBID;
|
||
tSql.ParamByName("pJGCZBBBID").Value = sJGCZBBBID;
|
||
tSql.ParamByName("pJGCZBXH").Value = CMISproConvert.StrToFloat(sJGCZBXH);
|
||
tSql.ParamByName("pDRHH").Value = CMISproConvert.StrToFloat(sDRHH);
|
||
dberp.ExecSQL(tSql);
|
||
|
||
#endregion
|
||
|
||
#region 循环dsCLQDL更新临时表
|
||
|
||
|
||
#region 含税
|
||
|
||
string sSQL_1 = " merge into V_XLXSCLQDWLHQ tar "
|
||
+ " using ( "
|
||
+ " select XLXSCLQDWL_JGCZBID,XLXSCLQDWL_JGCZBBBID,XLXSCLQDWL_JGCZBXH,XLXSCLQDWL_DRHH, ";
|
||
string sSQL_2 = string.Empty;
|
||
string sSQL_3 = " ' ' as MJBZ from XLXSCLQDWL ";
|
||
string sSQL_4 = string.Empty;
|
||
string sSQL_5 = " where XLXSCLQDWL_JGCZBID=:pJGCZBID and XLXSCLQDWL_JGCZBBBID=:pJGCZBBBID and XLXSCLQDWL_JGCZBXH=:pJGCZBXH and XLXSCLQDWL_DRHH=:pDRHH "
|
||
+ " ) sor "
|
||
+ " on ( "
|
||
+ " XLXSCLQDWL_JGCZBID=V_XLXSCLQDWLHQ_JGCZBID and XLXSCLQDWL_JGCZBBBID=V_XLXSCLQDWLHQ_JGCZBBBID and XLXSCLQDWL_JGCZBXH=V_XLXSCLQDWLHQ_JGCZBXH and XLXSCLQDWL_DRHH=V_XLXSCLQDWLHQ_DRHH and V_XLXSCLQDWLHQ_HSBZ=:pHSBZ and V_XLXSCLQDWLHQ_ID=:pID "
|
||
+ " ) when matched then update set V_XLXSCLQDWLHQ_HSBZ=:pHSBZ";
|
||
string sSQL_6 = string.Empty;
|
||
|
||
int count = 0;//记录已经拼接的列数
|
||
foreach (string sCLQDL in dicWLHQ.Keys)
|
||
{
|
||
sSQL_2 += string.Format("XLXSCLQDWL_WLID{0},", sCLQDL.Replace("第", "").Replace("列", ""));
|
||
sSQL_2 += string.Format("case when d{0}.XLXSBJDWLHQ_WLID is null then isnull(c{0}.XLXSWLHQ_WSJG,0) else d{0}.XLXSBJDWLHQ_WSJG end as WSJG{0},", sCLQDL.Replace("第", "").Replace("列", ""));
|
||
sSQL_2 += string.Format("case when d{0}.XLXSBJDWLHQ_WLID is null then isnull(c{0}.XLXSWLHQ_HSJG,0) else d{0}.XLXSBJDWLHQ_HSJG end as HSJG{0},", sCLQDL.Replace("第", "").Replace("列", ""));
|
||
|
||
|
||
sSQL_4 += string.Format(" left join XLXSBJDWLHQ d{0} on d{0}.XLXSBJDWLHQ_BJDID='{1}' and d{0}.XLXSBJDWLHQ_WLID=XLXSCLQDWL_WLID{0} ", sCLQDL.Replace("第", "").Replace("列", ""), sBJDID);
|
||
|
||
|
||
sSQL_4 += " left join";
|
||
sSQL_4 += " (";
|
||
sSQL_4 += " select XLXSWLHQ_WLID,XLXSWLHQ_WSJG,XLXSWLHQ_HSJG";
|
||
sSQL_4 += " from XLXSWLHQ,";
|
||
sSQL_4 += " (";
|
||
sSQL_4 += " select XLXSWLHQ_WLID as WLID,max(XLXSWLHQ_YXQS)as maxYXQS";
|
||
sSQL_4 += " from XLXSWLHQ";
|
||
sSQL_4 += " where XLXSWLHQ_JGBID=' '";
|
||
sSQL_4 += " and XLXSWLHQ_YXQS<=:pRQ and (XLXSWLHQ_YXQZ>=:pRQ or XLXSWLHQ_YXQZ is null)";
|
||
sSQL_4 += " group by XLXSWLHQ_WLID";
|
||
sSQL_4 += string.Format(") a{0} ", sCLQDL.Replace("第", "").Replace("列", ""));
|
||
sSQL_4 += " where XLXSWLHQ_JGBID=' ' and XLXSWLHQ_WLID=WLID and XLXSWLHQ_YXQS=maxYXQS";
|
||
sSQL_4 += string.Format(") c{0} on c{0}.XLXSWLHQ_WLID=XLXSCLQDWL_WLID{0} ", sCLQDL.Replace("第", "").Replace("列", ""));
|
||
|
||
sSQL_6 += string.Format(" ,V_XLXSCLQDWLHQ_JG{0} = HSJG{0} ", sCLQDL.Replace("第", "").Replace("列", ""));
|
||
|
||
count++;
|
||
|
||
if (count % 25 == 0 || count == dicWLHQ.Count)
|
||
{
|
||
tSql.Clear();
|
||
tSql.Add(sSQL_1);
|
||
tSql.Add(sSQL_2);
|
||
tSql.Add(sSQL_3);
|
||
tSql.Add(sSQL_4);
|
||
tSql.Add(sSQL_5);
|
||
tSql.Add(sSQL_6);
|
||
tSql.ParamByName("pID").Value = VnewID;
|
||
tSql.ParamByName("pHSBZ").Value = "Y";
|
||
tSql.ParamByName("pRQ").Value = sBJRQ;
|
||
tSql.ParamByName("pJGCZBID").Value = sJGCZBID;
|
||
tSql.ParamByName("pJGCZBBBID").Value = sJGCZBBBID;
|
||
tSql.ParamByName("pJGCZBXH").Value = CMISproConvert.StrToFloat(sJGCZBXH);
|
||
tSql.ParamByName("pDRHH").Value = CMISproConvert.StrToFloat(sDRHH);
|
||
dberp.ExecSQL(tSql);
|
||
sSQL_2 = string.Empty;
|
||
sSQL_4 = string.Empty;
|
||
sSQL_6 = string.Empty;
|
||
|
||
|
||
}
|
||
|
||
}
|
||
#endregion
|
||
|
||
#region 无税
|
||
|
||
sSQL_1 = " merge into V_XLXSCLQDWLHQ tar "
|
||
+ " using ( "
|
||
+ " select XLXSCLQDWL_JGCZBID,XLXSCLQDWL_JGCZBBBID,XLXSCLQDWL_JGCZBXH,XLXSCLQDWL_DRHH, ";
|
||
sSQL_2 = string.Empty;
|
||
sSQL_3 = " from XLXSCLQDWL ";
|
||
sSQL_4 = string.Empty;
|
||
sSQL_5 = " where XLXSCLQDWL_JGCZBID=:pJGCZBID and XLXSCLQDWL_JGCZBBBID=:pJGCZBBBID and XLXSCLQDWL_JGCZBXH=:pJGCZBXH and XLXSCLQDWL_DRHH=:pDRHH "
|
||
+ " ) sor "
|
||
+ " on ( "
|
||
+ " XLXSCLQDWL_JGCZBID=V_XLXSCLQDWLHQ_JGCZBID and XLXSCLQDWL_JGCZBBBID=V_XLXSCLQDWLHQ_JGCZBBBID and XLXSCLQDWL_JGCZBXH=V_XLXSCLQDWLHQ_JGCZBXH and XLXSCLQDWL_DRHH=V_XLXSCLQDWLHQ_DRHH and V_XLXSCLQDWLHQ_HSBZ=:pHSBZ and V_XLXSCLQDWLHQ_ID=:pID "
|
||
+ " ) when matched then update set V_XLXSCLQDWLHQ_HSBZ=:pHSBZ";
|
||
sSQL_6 = string.Empty;
|
||
|
||
count = 0;//记录已经拼接的列数
|
||
foreach (string sCLQDL in dicWLHQ.Keys)
|
||
{
|
||
sSQL_2 += string.Format("XLXSCLQDWL_WLID{0},", sCLQDL.Replace("第", "").Replace("列", ""));
|
||
sSQL_2 += string.Format("case when d{0}.XLXSBJDWLHQ_WLID is null then isnull(c{0}.XLXSWLHQ_WSJG,0) else d{0}.XLXSBJDWLHQ_WSJG end as WSJG{0},", sCLQDL.Replace("第", "").Replace("列", ""));
|
||
sSQL_2 += string.Format("case when d{0}.XLXSBJDWLHQ_WLID is null then isnull(c{0}.XLXSWLHQ_HSJG,0) else d{0}.XLXSBJDWLHQ_HSJG end as HSJG{0}", sCLQDL.Replace("第", "").Replace("列", ""));
|
||
|
||
|
||
sSQL_4 += string.Format(" left join XLXSBJDWLHQ d{0} on d{0}.XLXSBJDWLHQ_BJDID='{1}' and d{0}.XLXSBJDWLHQ_WLID=XLXSCLQDWL_WLID{0} ", sCLQDL.Replace("第", "").Replace("列", ""), sBJDID);
|
||
|
||
|
||
sSQL_4 += " left join";
|
||
sSQL_4 += " (";
|
||
sSQL_4 += " select XLXSWLHQ_WLID,XLXSWLHQ_WSJG,XLXSWLHQ_HSJG";
|
||
sSQL_4 += " from XLXSWLHQ,";
|
||
sSQL_4 += " (";
|
||
sSQL_4 += " select XLXSWLHQ_WLID as WLID,max(XLXSWLHQ_YXQS)as maxYXQS";
|
||
sSQL_4 += " from XLXSWLHQ";
|
||
sSQL_4 += " where XLXSWLHQ_JGBID=' '";
|
||
sSQL_4 += " and XLXSWLHQ_YXQS<=:pRQ and (XLXSWLHQ_YXQZ>=:pRQ or XLXSWLHQ_YXQZ is null)";
|
||
sSQL_4 += " group by XLXSWLHQ_WLID";
|
||
sSQL_4 += string.Format(") a{0} ", sCLQDL.Replace("第", "").Replace("列", ""));
|
||
sSQL_4 += " where XLXSWLHQ_JGBID=' ' and XLXSWLHQ_WLID=WLID and XLXSWLHQ_YXQS=maxYXQS";
|
||
sSQL_4 += string.Format(") c{0} on c{0}.XLXSWLHQ_WLID=XLXSCLQDWL_WLID{0} ", sCLQDL.Replace("第", "").Replace("列", ""));
|
||
|
||
sSQL_6 += string.Format(" ,V_XLXSCLQDWLHQ_JG{0} = WSJG{0} ", sCLQDL.Replace("第", "").Replace("列", ""));
|
||
|
||
count++;
|
||
|
||
if (count % 25 == 0 || count == dicWLHQ.Count)
|
||
{
|
||
tSql.Clear();
|
||
tSql.Add(sSQL_1);
|
||
tSql.Add(sSQL_2);
|
||
tSql.Add(sSQL_3);
|
||
tSql.Add(sSQL_4);
|
||
tSql.Add(sSQL_5);
|
||
tSql.Add(sSQL_6);
|
||
tSql.ParamByName("pID").Value = VnewID;
|
||
tSql.ParamByName("pHSBZ").Value = "N";
|
||
tSql.ParamByName("pRQ").Value = sBJRQ;
|
||
tSql.ParamByName("pJGCZBID").Value = sJGCZBID;
|
||
tSql.ParamByName("pJGCZBBBID").Value = sJGCZBBBID;
|
||
tSql.ParamByName("pJGCZBXH").Value = CMISproConvert.StrToFloat(sJGCZBXH);
|
||
tSql.ParamByName("pDRHH").Value = CMISproConvert.StrToFloat(sDRHH);
|
||
dberp.ExecSQL(tSql);
|
||
sSQL_2 = string.Empty;
|
||
sSQL_4 = string.Empty;
|
||
sSQL_6 = string.Empty;
|
||
|
||
|
||
}
|
||
|
||
}
|
||
#endregion
|
||
|
||
#endregion
|
||
|
||
#region 查询物料行情
|
||
tSql.Clear();
|
||
tSql.Add("select ");
|
||
bool bFirst = true;
|
||
foreach (string sCLQDL in dicWLHQ.Keys)
|
||
{
|
||
if (!bFirst)
|
||
tSql.Add(",");
|
||
bFirst = false;
|
||
|
||
tSql.Add(string.Format("XLXSCLQDWL_WLID{0},isnull(a.V_XLXSCLQDWLHQ_JG{0},0) as WSJG{0},isnull(b.V_XLXSCLQDWLHQ_JG{0},0) as HSJG{0}", sCLQDL.Replace("第", "").Replace("列", "")));
|
||
}
|
||
tSql.Add(" from XLXSCLQDWL");
|
||
|
||
tSql.Add(" left join");
|
||
tSql.Add(" V_XLXSCLQDWLHQ a on ");
|
||
tSql.Add(" XLXSCLQDWL_JGCZBID=a.V_XLXSCLQDWLHQ_JGCZBID and XLXSCLQDWL_JGCZBBBID=a.V_XLXSCLQDWLHQ_JGCZBBBID and XLXSCLQDWL_JGCZBXH=a.V_XLXSCLQDWLHQ_JGCZBXH and XLXSCLQDWL_DRHH=a.V_XLXSCLQDWLHQ_DRHH and a.V_XLXSCLQDWLHQ_HSBZ='N' and a.V_XLXSCLQDWLHQ_ID =:pID ");
|
||
tSql.Add(" left join");
|
||
tSql.Add(" V_XLXSCLQDWLHQ b on ");
|
||
tSql.Add(" XLXSCLQDWL_JGCZBID=b.V_XLXSCLQDWLHQ_JGCZBID and XLXSCLQDWL_JGCZBBBID=b.V_XLXSCLQDWLHQ_JGCZBBBID and XLXSCLQDWL_JGCZBXH=b.V_XLXSCLQDWLHQ_JGCZBXH and XLXSCLQDWL_DRHH=b.V_XLXSCLQDWLHQ_DRHH and b.V_XLXSCLQDWLHQ_HSBZ='Y' and b.V_XLXSCLQDWLHQ_ID =:pID ");
|
||
|
||
tSql.Add(" where XLXSCLQDWL_JGCZBID=:pJGCZBID and XLXSCLQDWL_JGCZBBBID=:pJGCZBBBID and XLXSCLQDWL_JGCZBXH=:pJGCZBXH and XLXSCLQDWL_DRHH=:pDRHH");
|
||
tSql.ParamByName("pID").Value = VnewID;
|
||
tSql.ParamByName("pRQ").Value = sBJRQ;
|
||
tSql.ParamByName("pJGCZBID").Value = sJGCZBID;
|
||
tSql.ParamByName("pJGCZBBBID").Value = sJGCZBBBID;
|
||
tSql.ParamByName("pJGCZBXH").Value = sJGCZBXH;
|
||
tSql.ParamByName("pDRHH").Value = sDRHH;
|
||
dsWLHQ = dberp.OpenDataSet(tSql);
|
||
#endregion
|
||
|
||
//删除临时表
|
||
tSql.Clear();
|
||
tSql.Add("delete from V_XLXSCLQDWLHQ where V_XLXSCLQDWLHQ_ID=:pID ");
|
||
tSql.ParamByName("pID").Value = VnewID;
|
||
dberp.ExecSQL(tSql);
|
||
|
||
}
|
||
}
|
||
|
||
TSQL SQL = new TSQL(dberp);
|
||
SQL.Clear();
|
||
|
||
SQL.Add("select XLJGCZBLM_BJCLID,XLJGCZBLM_CLQDL,XLXSBJCL_BJCLMC,XLXSBJCL_BJHQ,WLXX,CLJG,CLSL,px0,SHL,CLJGBL,CLCB from( ");
|
||
SQL.Add("select XLJGCZBLM_BJCLID,XLJGCZBLM_CLQDL,bjcl.XLXSBJCL_BJCLMC, case bjcl.XLXSBJCL_BJHQ when '1' then '1|材料行情' else '2|物料行情' end as XLXSBJCL_BJHQ ,");
|
||
//物料信息
|
||
|
||
if (dicCLHQ.Count != 0 || dicWLHQ.Count != 0)
|
||
{
|
||
Dictionary<string, string> DicJGYWLID = new Dictionary<string, string>();
|
||
#region 物料加工原物料ID
|
||
TSQL sql = new TSQL(dberp);
|
||
sql.Clear();
|
||
sql.Add(" select XLXSBJCLWL_WLID,XLXSBJCLWL_JGYWLID ");
|
||
sql.Add(" from XLXSBJCLWL ");
|
||
sql.Add(" where XLXSBJCLWL_JGYWLID <> '-' ");
|
||
DataSet dsWLJGYWL = dberp.OpenDataSet(sql);
|
||
if (dsWLJGYWL.Tables[0].Rows.Count > 0)
|
||
{
|
||
foreach (DataRow trow in dsWLJGYWL.Tables[0].Rows)
|
||
{
|
||
string sWLID = trow["XLXSBJCLWL_WLID"].ToString().Trim();
|
||
string sJGYWLID = trow["XLXSBJCLWL_JGYWLID"].ToString().Trim();
|
||
if (!DicJGYWLID.ContainsKey(sWLID))
|
||
{
|
||
DicJGYWLID.Add(sWLID, sJGYWLID);
|
||
}
|
||
}
|
||
}
|
||
#endregion
|
||
SQL.Add(" case ");
|
||
foreach (string sCLQDL in dicCLHQ.Keys)
|
||
{
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then ' ' ", sCLQDL));
|
||
}
|
||
foreach (string sCLQDL in dicWLHQ.Keys)
|
||
{
|
||
if (dsWLHQ != null && dsWLHQ.Tables[0].Rows.Count > 0)
|
||
{
|
||
string sWLID = dsWLHQ.Tables[0].Rows[0]["XLXSCLQDWL_WLID" + sCLQDL.Replace("第", "").Replace("列", "")].ToString().Trim();
|
||
if (DicJGYWLID.ContainsKey(sWLID))
|
||
sWLID = DicJGYWLID[sWLID];
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then '{1}' ", sCLQDL, GetWLXX(sWLID)));
|
||
}
|
||
else
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then ' ' ", sCLQDL));
|
||
}
|
||
SQL.Add(" end as WLXX, ");
|
||
}
|
||
else
|
||
{
|
||
SQL.Add(" ' ' as WLXX, ");
|
||
}
|
||
|
||
//材料价格
|
||
if (dicCLHQ.Count != 0 || dicWLHQ.Count != 0)
|
||
{
|
||
SQL.Add(" case ");
|
||
foreach (string sCLQDL in dicCLHQ.Keys)
|
||
{
|
||
if (bHSBZ)
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then isnull(HSJG,0.0) ", sCLQDL));
|
||
else
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then isnull(CLJG,0.0) ", sCLQDL));
|
||
}
|
||
foreach (string sCLQDL in dicWLHQ.Keys)
|
||
{
|
||
if (dsWLHQ != null && dsWLHQ.Tables[0].Rows.Count > 0)
|
||
{
|
||
if (bHSBZ)
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then {1} ", sCLQDL, dsWLHQ.Tables[0].Rows[0]["HSJG" + sCLQDL.Replace("第", "").Replace("列", "")].ToString().Trim()));
|
||
else
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then {1} ", sCLQDL, dsWLHQ.Tables[0].Rows[0]["WSJG" + sCLQDL.Replace("第", "").Replace("列", "")].ToString().Trim()));
|
||
}
|
||
else
|
||
{
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then 0.0 ", sCLQDL));
|
||
}
|
||
}
|
||
SQL.Add(" end as CLJG, ");
|
||
}
|
||
else
|
||
{
|
||
SQL.Add(" 0 as CLJG, ");
|
||
}
|
||
//材料数量
|
||
if (dicCLHQ.Count != 0 || dicWLHQ.Count != 0)
|
||
{
|
||
SQL.Add(" case ");
|
||
foreach (string sCLQDL in dicCLHQ.Keys)
|
||
{
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then XLXSCLQD_SL{1} ", sCLQDL, sCLQDL.Replace("第", "").Replace("列", "")));
|
||
}
|
||
foreach (string sCLQDL in dicWLHQ.Keys)
|
||
{
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then XLXSCLQD_SL{1} ", sCLQDL, sCLQDL.Replace("第", "").Replace("列", "")));
|
||
}
|
||
SQL.Add(" end as CLSL, ");
|
||
}
|
||
else
|
||
{
|
||
SQL.Add(" 0 as CLSL, ");
|
||
}
|
||
|
||
if (dicCLHQ.Count != 0 || dicWLHQ.Count != 0)
|
||
{
|
||
SQL.Add(" case ");
|
||
foreach (string sCLQDL in dicCLHQ.Keys)
|
||
{
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then ( case when XLXSCLQD_SL{1} = 0 then 0 else 1 end) ", sCLQDL, sCLQDL.Replace("第", "").Replace("列", "")));
|
||
}
|
||
foreach (string sCLQDL in dicWLHQ.Keys)
|
||
{
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then ( case when XLXSCLQD_SL{1} = 0 then 0 else 1 end) ", sCLQDL, sCLQDL.Replace("第", "").Replace("列", "")));
|
||
}
|
||
SQL.Add(" end as px0, ");
|
||
}
|
||
else
|
||
{
|
||
SQL.Add(" 0 as px0, ");
|
||
}
|
||
|
||
|
||
//损耗率
|
||
if (dicCLHQ.Count != 0 || dicWLHQ.Count != 0)
|
||
{
|
||
SQL.Add(" case ");
|
||
foreach (string sCLQDL in dicCLHQ.Keys)
|
||
{
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then XLXSCLQD_SHL{1} ", sCLQDL, sCLQDL.Replace("第", "").Replace("列", "")));
|
||
}
|
||
foreach (string sCLQDL in dicWLHQ.Keys)
|
||
{
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then XLXSCLQD_SHL{1} ", sCLQDL, sCLQDL.Replace("第", "").Replace("列", "")));
|
||
}
|
||
SQL.Add(" end as SHL, ");
|
||
}
|
||
else
|
||
{
|
||
SQL.Add(" 0 as SHL, ");
|
||
}
|
||
//材料用量比例
|
||
if (dicCLHQ.Count != 0 || dicWLHQ.Count != 0)
|
||
{
|
||
SQL.Add(" case ");
|
||
foreach (string sCLQDL in dicCLHQ.Keys)
|
||
{
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then XLXSCLQD_CLJGBL{1} ", sCLQDL, sCLQDL.Replace("第", "").Replace("列", "")));
|
||
}
|
||
foreach (string sCLQDL in dicWLHQ.Keys)
|
||
{
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then XLXSCLQD_CLJGBL{1} ", sCLQDL, sCLQDL.Replace("第", "").Replace("列", "")));
|
||
}
|
||
SQL.Add(" end as CLJGBL, ");
|
||
}
|
||
else
|
||
{
|
||
SQL.Add(" 0 as CLJGBL, ");
|
||
}
|
||
//材料成本
|
||
if (dicCLHQ.Count != 0 || dicWLHQ.Count != 0)
|
||
{
|
||
SQL.Add(" case ");
|
||
if (sJSBLFS.Equals("C1"))//C1|进一法(材料价各分项进一)
|
||
{
|
||
foreach (string sCLQDL in dicCLHQ.Keys)
|
||
{
|
||
if (bHSBZ)
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then CEILING(RoundFix(isnull(HSJG,0.0)*XLXSCLQD_SL{1}*(1+XLXSCLQD_SHL{1}/100)*XLXSCLQD_CLJGBL{1}*{2},8))/{2} ", sCLQDL, sCLQDL.Replace("第", "").Replace("列", ""), Math.Pow(10.0, iXSWS)));
|
||
else
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then CEILING(RoundFix(isnull(CLJG,0.0)*XLXSCLQD_SL{1}*(1+XLXSCLQD_SHL{1}/100)*XLXSCLQD_CLJGBL{1}*{2},8))/{2} ", sCLQDL, sCLQDL.Replace("第", "").Replace("列", ""), Math.Pow(10.0, iXSWS)));
|
||
}
|
||
foreach (string sCLQDL in dicWLHQ.Keys)
|
||
{
|
||
if (dsWLHQ != null && dsWLHQ.Tables[0].Rows.Count > 0)
|
||
{
|
||
if (bHSBZ)
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then CEILING(RoundFix({2}*XLXSCLQD_SL{1}*(1+XLXSCLQD_SHL{1}/100)*XLXSCLQD_CLJGBL{1}*{3},8))/{3} ", sCLQDL, sCLQDL.Replace("第", "").Replace("列", ""),
|
||
dsWLHQ.Tables[0].Rows[0]["HSJG" + sCLQDL.Replace("第", "").Replace("列", "")].ToString().Trim(), Math.Pow(10.0, iXSWS)));
|
||
else
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then CEILING(RoundFix({2}*XLXSCLQD_SL{1}*(1+XLXSCLQD_SHL{1}/100)*XLXSCLQD_CLJGBL{1}*{3},8))/{3} ", sCLQDL, sCLQDL.Replace("第", "").Replace("列", ""),
|
||
dsWLHQ.Tables[0].Rows[0]["WSJG" + sCLQDL.Replace("第", "").Replace("列", "")].ToString().Trim(), Math.Pow(10.0, iXSWS)));
|
||
}
|
||
else
|
||
{
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then 0.0 ", sCLQDL));
|
||
}
|
||
}
|
||
}
|
||
else
|
||
{
|
||
foreach (string sCLQDL in dicCLHQ.Keys)
|
||
{
|
||
if (bHSBZ)
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then isnull(HSJG,0.0)*XLXSCLQD_SL{1}*(1+XLXSCLQD_SHL{1}/100)*XLXSCLQD_CLJGBL{1} ", sCLQDL, sCLQDL.Replace("第", "").Replace("列", "")));
|
||
else
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then isnull(CLJG,0.0)*XLXSCLQD_SL{1}*(1+XLXSCLQD_SHL{1}/100)*XLXSCLQD_CLJGBL{1} ", sCLQDL, sCLQDL.Replace("第", "").Replace("列", "")));
|
||
}
|
||
foreach (string sCLQDL in dicWLHQ.Keys)
|
||
{
|
||
if (dsWLHQ != null && dsWLHQ.Tables[0].Rows.Count > 0)
|
||
{
|
||
if (bHSBZ)
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then {2}*XLXSCLQD_SL{1}*(1+XLXSCLQD_SHL{1}/100)*XLXSCLQD_CLJGBL{1} ", sCLQDL, sCLQDL.Replace("第", "").Replace("列", ""),
|
||
dsWLHQ.Tables[0].Rows[0]["HSJG" + sCLQDL.Replace("第", "").Replace("列", "")].ToString().Trim()));
|
||
else
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then {2}*XLXSCLQD_SL{1}*(1+XLXSCLQD_SHL{1}/100)*XLXSCLQD_CLJGBL{1} ", sCLQDL, sCLQDL.Replace("第", "").Replace("列", ""),
|
||
dsWLHQ.Tables[0].Rows[0]["WSJG" + sCLQDL.Replace("第", "").Replace("列", "")].ToString().Trim()));
|
||
}
|
||
else
|
||
{
|
||
SQL.Add(string.Format(" when XLJGCZBLM_CLQDL='{0}' then 0.0 ", sCLQDL));
|
||
}
|
||
}
|
||
}
|
||
SQL.Add(" end as CLCB ");
|
||
}
|
||
else
|
||
{
|
||
SQL.Add(" 0 as CLCB ");
|
||
}
|
||
|
||
SQL.Add(" from XLXSCLQD, ");
|
||
SQL.Add(" XLJGCZBLM");
|
||
SQL.Add(" left join XLXSBJCL bjcl on bjcl.XLXSBJCL_BJCLID=XLJGCZBLM_BJCLID");
|
||
//SQL.Add(" left join XLJGCZBML on XLJGCZBML_JGCZBID = XLJGCZBLM_JGCZBID ");
|
||
SQL.Add(" left join ");
|
||
SQL.Add(" (");
|
||
if (!XLBJDUtils.IsJGEJGB(sJGBID))//表价实时计算价格表
|
||
{
|
||
if (sBJDHQLY.Equals("1"))
|
||
{
|
||
SQL.Add(XLBJD_CLHQTZ.GetBJDHQSql().ToString());
|
||
}
|
||
else
|
||
{
|
||
SQL.Add(XLBJD_CLHQTZ.GetBJRHQSql().ToString());
|
||
}
|
||
}
|
||
else
|
||
{
|
||
SQL.Add(XLBJD_CLHQTZ.GetJGEHQSql(bHSBZ, sTBJCLID, sLBJCLID, sTJ, sLJ).ToString());
|
||
}
|
||
SQL.Add(" ) curbjdhq on curbjdhq.XLXSBJCL_BJCLID=bjcl.XLXSBJCL_BJCLID ");
|
||
|
||
SQL.Add("where XLJGCZBLM_JGCZBID=XLXSCLQD_JGCZBID and XLXSCLQD_JGCZBBBID = XLJGCZBLM_JGCZBBBID and isnull(XLJGCZBLM_CLQDL,' ')<>' ' ");
|
||
SQL.Add(" and XLXSCLQD_XLXHID=:pXLXHID and XLXSCLQD_XLGGID=:pXLGGID and XLXSCLQD_XLBZID=:pXLBZID and XLXSCLQD_DYDJID=:pDYDJID ");
|
||
SQL.Add(" and XLXSCLQD_WPID='ZZZZ'");
|
||
SQL.Add(" ) M where CLSL>0 ");
|
||
|
||
SQL.Add(" order by px0 desc,XLJGCZBLM_CLQDL,XLJGCZBLM_BJCLID ");
|
||
|
||
SQL.ParamByName("pBJDID").Value = sBJDID;
|
||
SQL.ParamByName("pBJRQ").Value = sBJRQ;
|
||
SQL.ParamByName("pBJFLID").Value = sBJFLID;
|
||
SQL.ParamByName("pJGBID").Value = sJGBID;
|
||
|
||
SQL.ParamByName("pXLXHID").Value = sXLXHID;
|
||
SQL.ParamByName("pXLGGID").Value = sXLGGID;
|
||
SQL.ParamByName("pXLBZID").Value = sXLBZID;
|
||
SQL.ParamByName("pDYDJID").Value = sDYDJID;
|
||
DataSet dsResult = dberp.OpenDataSet(SQL);
|
||
|
||
txtCLCB.Value = 0;
|
||
|
||
for (int i = 0; i < dsResult.Tables[0].Rows.Count; i++)
|
||
{
|
||
txtCLCB.Value += CMISproConvert.StrToFloat(dsResult.Tables[0].Rows[i]["CLCB"].ToString());
|
||
}
|
||
string sJGBTJYS = XLBJDUtils.GetXLXSJGBXXByJGBID(sJGBID).XLXSJGB_TJYS;
|
||
if (sJSBLFS.Equals("C1") || (sJSBLFS.Equals("C2") && sJGBTJYS == "3"))//C1|进一法(材料价各分项进一)、C2|进一法(材料价汇总进一)
|
||
{
|
||
txtCLCB.Value = Math.Ceiling(txtCLCB.Value * Math.Pow(10.0, iXSWS)) / Math.Pow(10.0, iXSWS);
|
||
}
|
||
if (bExistedSYJGBMX && !bHasCLQD && (sJGBTJYS == "1" || sJGBTJYS == "3") && (sCPLJGS == "2" || sCPLJGS == "4"))
|
||
{
|
||
|
||
if (sCPLJGS == "4")
|
||
{
|
||
txtCLCB.Value *= dCLCBYSJDZ;
|
||
}
|
||
else
|
||
{
|
||
txtCLCB.Value *= dYSJDZ;
|
||
}
|
||
}
|
||
//无材料清单且料价计算方式为3|按报价单料价加点对照表计算
|
||
if (!bHasCLQD && (sCPLJGS == "3"))
|
||
{
|
||
txtCLCB.Value *= dYSJDZ;
|
||
}
|
||
return dsResult;
|
||
}
|
||
|
||
private string GetWLXX(string sWLID)
|
||
{
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
TSQL sql = new TSQL(dberp);
|
||
sql.Add("select WL_MC,WL_GG from WL where WL_WLID=:pWLID");
|
||
sql.ParamByName("pWLID").Value = sWLID.Trim();
|
||
DataSet ds = dberp.OpenDataSet(sql);
|
||
if (ds.Tables[0].Rows.Count > 0)
|
||
return sWLID.Trim() + "|" + ds.Tables[0].Rows[0]["WL_MC"].ToString().Trim() + "|" + ds.Tables[0].Rows[0]["WL_GG"].ToString().Trim();
|
||
else
|
||
return sWLID.Trim();
|
||
}
|
||
|
||
private string GetBJCP(string sXLXHID, string sXLGGID, string sXLBZID, string sDYDJID)
|
||
{
|
||
string sRetValue = "";
|
||
|
||
if (!sXLXHID.Equals(""))
|
||
{
|
||
sRetValue = sRetValue + " " + sXLXHID;
|
||
}
|
||
|
||
if (!sXLGGID.Equals(""))
|
||
{
|
||
sRetValue = sRetValue + " " + sXLGGID;
|
||
}
|
||
|
||
if (!sXLBZID.Equals(""))
|
||
{
|
||
sRetValue = sRetValue + " " + sXLBZID;
|
||
}
|
||
|
||
if (!sDYDJID.Equals(""))
|
||
{
|
||
sRetValue = sRetValue + " " + sDYDJID;
|
||
}
|
||
sRetValue = sRetValue.Trim();
|
||
return sRetValue;
|
||
}
|
||
|
||
private string GetXLBZXX(string sXLBZID)
|
||
{
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
TSQL SQL = new TSQL(dberp);
|
||
SQL.Clear();
|
||
SQL.Add(" select rtrim(XLBZ_XLBZID)||'|'||rtrim(XLBZ_XLBZLXMC) as XLBZ_XLBZID from XLBZ where XLBZ_XLBZID= :pXLBZID ");
|
||
SQL.ParamByName("pXLBZID").Value = sXLBZID;
|
||
DataSet ds = dberp.OpenDataSet(SQL);
|
||
if (ds.Tables[0].Rows.Count > 0)
|
||
{
|
||
return ds.Tables[0].Rows[0][0].ToString().Trim();
|
||
}
|
||
else
|
||
return "";
|
||
}
|
||
|
||
private string strPadRight(string s, int x)
|
||
{
|
||
string str = s;
|
||
if (str.Split('.').Length == 2)
|
||
{
|
||
str = str.Split('.')[0] + "." + str.Split('.')[1].PadRight(x, '0');
|
||
}
|
||
else
|
||
{
|
||
string tmp = "0";
|
||
if (x > 0)
|
||
{
|
||
tmp = tmp.PadRight(x, '0');
|
||
str = str + "." + tmp;
|
||
}
|
||
}
|
||
return str;
|
||
}
|
||
|
||
private bool HasCLQD(string sXLXHID, string sXLGGID, string sXLBZID, string sDYDJID)
|
||
{
|
||
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 XLXSCLQD_WPID='ZZZZ' ", sXLXHID, sXLGGID, sXLBZID, sDYDJID)))
|
||
return true;
|
||
return false;
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 按钮事件
|
||
|
||
protected void btnTZJG_Click(object sender, EventArgs e)
|
||
{
|
||
TPageParams pageParams = new TPageParams();
|
||
pageParams.UrlPath = "XLBJD_CLHQTZList.aspx";
|
||
pageParams.Title = "调整材料行情" + "：" + txtBJCLID.Value.Trim() + "|" + txtBJCLMC.Value.Trim() + (txtWLXX.Value.Trim().Equals("") ? "" : "【" + txtWLXX.Value.Trim() + "】");
|
||
pageParams.AddString("BJFLID", PageParam.GetRTrimString("BJFLID"));
|
||
pageParams.AddString("BJDID", PageParam.GetRTrimString("BJDID"));
|
||
pageParams.AddString("BJRQ", PageParam.GetRTrimString("BJRQ"));
|
||
pageParams.AddString("JGBID", PageParam.GetRTrimString("JGBID"));
|
||
pageParams.AddString("JCLLX", PageParam.GetRTrimString("JCLLX"));
|
||
pageParams.AddString("LJJCLLX", PageParam.GetRTrimString("LJJCLLX"));
|
||
pageParams.AddString("BJDHQLY", PageParam.GetRTrimString("BJDHQLY"));
|
||
pageParams.AddString("BJLY", PageParam.GetRTrimString("BJLY"));
|
||
pageParams.AddString("ZDXJJCLLX", PageParam.GetRTrimString("ZDXJJCLLX"));
|
||
pageParams.AddString("btnRefreshCID", btnRefreshCLHQTZ.ClientID);
|
||
|
||
pageParams.AddString("HSBZ", PageParam.GetRTrimString("HSBZ"));
|
||
pageParams.AddString("TBJCLID", PageParam.GetRTrimString("TBJCLID"));
|
||
pageParams.AddString("LBJCLID", PageParam.GetRTrimString("LBJCLID"));
|
||
pageParams.AddString("TJ", PageParam.GetRTrimString("TJ"));
|
||
pageParams.AddString("LJ", PageParam.GetRTrimString("LJ"));
|
||
pageParams.AddString("CPBJMRXSWS", PageParam.GetRTrimString("CPBJMRXSWS"));
|
||
|
||
pageParams.AddString("JYF", PageParam.GetRTrimString("JYF"));//计运费
|
||
pageParams.AddString("YSJDID", PageParam.GetRTrimString("YSJDID"));//运输节点
|
||
pageParams.AddString("GYSID", PageParam.GetRTrimString("GYSID"));//运输单位
|
||
pageParams.AddString("YFWLID", PageParam.GetRTrimString("YFWLID"));//运费物料
|
||
pageParams.AddString("BJHYF", PageParam.GetRTrimString("BJHYF"));//报价含运费
|
||
|
||
pageParams.AddString("BJCLID", txtBJCLID.Value.Trim());
|
||
OpenModuleDialog(100860000, pageParams);
|
||
}
|
||
|
||
protected void btnRefreshCLHQTZ_Click(object sender, EventArgs e)
|
||
{
|
||
int iIndex = grdContentGrid.SelectedIndex;
|
||
moduleGV_PageIndexChanging(null, null);
|
||
grdContentGrid.SelectedIndex = iIndex;
|
||
}
|
||
|
||
protected void lblBJCP_Click(object sender, EventArgs e)
|
||
{
|
||
string sJGCZBIDFromJGCZBML = "";
|
||
string sJGCZBBBIDFromJGCZBML = "";
|
||
string sXLXHID = PageParam.GetRTrimString("XLXHID");
|
||
string sXLGGID = PageParam.GetRTrimString("XLGGID");
|
||
string sXLBZID = PageParam.GetRTrimString("XLBZID");
|
||
string sDYDJID = PageParam.GetRTrimString("DYDJID");
|
||
int iSYXH = 0;
|
||
double dYSJDZ = 1;
|
||
double dCLCBYSJDZ = 1;
|
||
string sJGBID = PageParam.GetRTrimString("JGBID");
|
||
if (XLBJDUtils.ExistedSYJGBMX(sXLXHID, sXLGGID, sXLBZID, sDYDJID, sJGBID, ref iSYXH, ref dYSJDZ, ref dCLCBYSJDZ))
|
||
{
|
||
XLBJDUtils.GetJGBMXBySYXH(sJGBID, iSYXH, ref sXLXHID, ref sXLGGID, ref sXLBZID, ref sDYDJID);
|
||
}
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
TSQL sql = new TSQL(dberp);
|
||
sql.Clear();
|
||
sql.Add(" select XLXSCLQD_JGCZBID as JGCZBID, XLXSCLQD_JGCZBBBID as JGCZBBBID");
|
||
sql.Add(" from XLXSCLQD where 1=1 and XLXSCLQD_WPID ='ZZZZ' ");
|
||
sql.Add(" and XLXSCLQD_XLXHID = :pXLXHID ");
|
||
sql.Add(" and XLXSCLQD_XLGGID = :pXLGGID ");
|
||
sql.Add(" and XLXSCLQD_XLBZID = :pXLBZID ");
|
||
sql.Add(" and XLXSCLQD_DYDJID = :pDYDJID ");
|
||
sql.ParamByName("pXLXHID").Value = sXLXHID;
|
||
sql.ParamByName("pXLGGID").Value = sXLGGID;
|
||
sql.ParamByName("pXLBZID").Value = sXLBZID;
|
||
sql.ParamByName("pDYDJID").Value = sDYDJID.Equals("") ? " " : sDYDJID;
|
||
DataSet ds = dberp.OpenDataSet(sql);
|
||
if (ds.Tables[0].Rows.Count > 0)
|
||
{
|
||
sJGCZBBBIDFromJGCZBML = ds.Tables[0].Rows[0]["JGCZBBBID"].ToString().Trim();
|
||
sJGCZBIDFromJGCZBML = ds.Tables[0].Rows[0]["JGCZBID"].ToString().Trim() + "|" + sJGCZBBBIDFromJGCZBML;
|
||
}
|
||
else
|
||
{
|
||
ShowMessage("未查询到结构尺重表信息，请在102579008|报价材料用量模块检查该记录是否补充完整并流转结束！");
|
||
return;
|
||
}
|
||
|
||
TPageParams tpp = new TPageParams();
|
||
tpp.UrlPath = "../Components/N_XLBJCL/CLQDList.aspx";
|
||
tpp.CallAction = TPageCallAction.pcaBrowse;
|
||
tpp.AddString("JGCZBIDFromJGCZBML", sJGCZBIDFromJGCZBML);
|
||
tpp.AddString("JGCZBBBIDFromJGCZBML", sJGCZBBBIDFromJGCZBML);
|
||
OpenFuncModuleDialog(102579001, tpp);
|
||
}
|
||
#endregion
|
||
}
|
||
}
|