878 lines
49 KiB
C#
878 lines
49 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_WORKFLOW;
|
||
using GThinking.N_MODEL.Dialog;
|
||
using GThinking.N_PUBLIC;
|
||
using GThinking.N_MISPRO.Utility;
|
||
using GThinking.N_CONTROL;
|
||
using System.IO;
|
||
using System.Data.OleDb;
|
||
using System.Data.Common;
|
||
using GThinking.N_MISPROAS;
|
||
using System.Collections.Generic;
|
||
using GThinking.N_PUBLIC2;
|
||
using Newtonsoft.Json;
|
||
using Newtonsoft.Json.Linq;
|
||
|
||
namespace GThinking.N_XLXSBJ
|
||
{
|
||
public partial class XLBJD_FZDRXInfo : SingleCardBase
|
||
{
|
||
XLBJD_WBBJDNR entity = new XLBJD_WBBJDNR();
|
||
|
||
protected void Page_Load(object sender, EventArgs e)
|
||
{
|
||
this.UMID = 100860000;
|
||
this.UMName = "线缆销售报价单";
|
||
base.CurMaster = Master;
|
||
base.CurEntity = entity;
|
||
Master.EnableAttachBtn = false;
|
||
Master.EnableCancelBtn = false;
|
||
Master.EnableCopyBtn = false;
|
||
Master.EnableDeleteBtn = false;
|
||
Master.EnableEditBtn = false;
|
||
Master.EnableInsertBtn = false;
|
||
Master.EnablePrintBtn = false;
|
||
Master.EnableRefreshBtn = false;
|
||
Master.EnableSaveBtn = false;
|
||
Server.ScriptTimeout = 3600;
|
||
|
||
string sXSYXJ = PageParam.GetRTrimString("XSYXJ");
|
||
string sXSYZ = PageParam.GetRTrimString("XSYZ");
|
||
string sXSYSCP = PageParam.GetRTrimString("XSYSCP");
|
||
string sXSBH = PageParam.GetRTrimString("XSBH");
|
||
string sXSCPBJXJ = PageParam.GetRTrimString("XSCPBJXJ");
|
||
|
||
int col = 0;
|
||
string stitle = "";
|
||
|
||
//序号
|
||
stitle += "序号,";
|
||
col++;
|
||
//固定列：型号、规格、标准、电压等级、计量单位、报价数量、报价
|
||
stitle += "型号,";
|
||
col++;
|
||
stitle += "规格,";
|
||
col++;
|
||
stitle += "标准,";
|
||
col++;
|
||
stitle += "电压等级,";
|
||
col++;
|
||
stitle += "计量单位,";
|
||
col++;
|
||
stitle += "报价数量,";
|
||
col++;
|
||
stitle += "报价,";
|
||
col++;
|
||
|
||
//动态列
|
||
if (sXSYXJ == "Y")
|
||
{
|
||
stitle += "意向价,";
|
||
col++;
|
||
}
|
||
if (sXSYZ == "Y")
|
||
{
|
||
stitle += "印字,";
|
||
col++;
|
||
}
|
||
stitle += "备注,";
|
||
col++;
|
||
stitle += "报价产品,";
|
||
col++;
|
||
if (sXSYSCP == "Y")
|
||
{
|
||
stitle += "原始产品,";
|
||
col++;
|
||
}
|
||
if (sXSBH == "Y")
|
||
{
|
||
stitle += "包号,";
|
||
col++;
|
||
}
|
||
if (sXSCPBJXJ == "Y")
|
||
{
|
||
stitle += "产品报价限价,";
|
||
col++;
|
||
stitle += "报价金额限价,";
|
||
col++;
|
||
}
|
||
|
||
stitle.TrimEnd(',');
|
||
txtCols.Value = col.ToString();
|
||
txtTitle.Value = stitle;
|
||
base.InitPage();
|
||
}
|
||
|
||
#region 控件事件
|
||
|
||
protected void btnQD_Click(object sender, EventArgs e)
|
||
{
|
||
DataTable dt = ConvertJsonToDataTable(txttabledata.Value);
|
||
string sBJDID = PageParam.GetRTrimString("BJDID");
|
||
string sBJFLID = PageParam.GetRTrimString("BJFLID");
|
||
string sJGBID = PageParam.GetRTrimString("JGBID");
|
||
string sBJU003 = PageParam.GetRTrimString("BJU003");
|
||
string sDefaultXLBZID = PageParam.GetRTrimString("sDefaultXLBZID");
|
||
string sXLJGU007 = PageParam.GetRTrimString("XLJGU007");
|
||
string sXSYXJ = PageParam.GetRTrimString("XSYXJ");
|
||
string sXSYZ = PageParam.GetRTrimString("XSYZ");
|
||
string sXSYSCP = PageParam.GetRTrimString("XSYSCP");
|
||
string sXSBH = PageParam.GetRTrimString("XSBH");
|
||
string sXSCPBJXJ = PageParam.GetRTrimString("XSCPBJXJ");
|
||
string sBJU002 = PageParam.GetRTrimString("BJU002");
|
||
string sBJU018 = PageParam.GetRTrimString("BJU018");
|
||
string sBJU038 = PageParam.GetRTrimString("BJU038");
|
||
string sMRBZID = PageParam.GetRTrimString("txtMRBZID");
|
||
|
||
if (sBJDID.Equals(""))
|
||
{
|
||
sBJDID = " ";
|
||
}
|
||
|
||
//动态添加的栏目序号（DataTable中的列索引，从0开始：0=型号,1=规格,2=标准,3=电压等级,4=计量单位,5=报价数量,6=报价）
|
||
int ColumnIndex = 6;
|
||
int indexYXJ = -1;
|
||
int indexYZ = -1;
|
||
int indexBZ = -1;
|
||
int indexBJCP = -1;
|
||
int indexYSCP = -1;
|
||
int indexBH = -1;
|
||
int indexCPBJXJ = -1;
|
||
int indexBJJEXJ = -1;
|
||
if (sXSYXJ == "Y")
|
||
indexYXJ = ++ColumnIndex;
|
||
|
||
if (sXSYZ == "Y")
|
||
indexYZ = ++ColumnIndex;
|
||
indexBZ = ++ColumnIndex;
|
||
indexBJCP = ++ColumnIndex;
|
||
if (sXSYSCP == "Y")
|
||
indexYSCP = ++ColumnIndex;
|
||
if (sXSBH == "Y")
|
||
indexBH = ++ColumnIndex;
|
||
if (sXSCPBJXJ == "Y")
|
||
{
|
||
indexCPBJXJ = ++ColumnIndex;
|
||
indexBJJEXJ = ++ColumnIndex;
|
||
}
|
||
|
||
//确定有效行数
|
||
int LastRows = 0;
|
||
for (int i = 0; i < dt.Rows.Count; i++)
|
||
{
|
||
for (int j = 0; j < dt.Columns.Count; j++)
|
||
{
|
||
if (!dt.Rows[i][j].ToString().Trim().Equals(""))
|
||
{
|
||
LastRows = i + 1;
|
||
break;
|
||
}
|
||
}
|
||
}
|
||
|
||
if (LastRows == 0)
|
||
{
|
||
ShowMessage("没有可导入的数据！");
|
||
return;
|
||
}
|
||
|
||
//标准字典
|
||
Dictionary<string, string> dicDRMXBZ = new Dictionary<string, string>();
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
TSQL sSQL = new TSQL(dberp);
|
||
sSQL.Clear();
|
||
sSQL.Add("select XLBZ_XLBZID,XLBZ_XLBZLXMC from XLBZ where 1 = 1 ");
|
||
DataSet dsXLBZ = dberp.OpenDataSet(sSQL);
|
||
if (dsXLBZ.Tables[0].Rows.Count > 0)
|
||
{
|
||
for (int i = 0; i < dsXLBZ.Tables[0].Rows.Count; i++)
|
||
{
|
||
dicDRMXBZ.Add(dsXLBZ.Tables[0].Rows[i]["XLBZ_XLBZID"].ToString().Trim(), dsXLBZ.Tables[0].Rows[i]["XLBZ_XLBZLXMC"].ToString().Trim());
|
||
}
|
||
}
|
||
|
||
int iXH = CMISproDataBase.GetMaxXH("DBERP", "XLXSBJDMX", "XLXSBJDMX_XH", "XLXSBJDMX_BJDID='" + sBJDID + "'") + 1;
|
||
int iXSSX = CMISproDataBase.GetMaxXH("DBERP", "XLXSBJDMX", "XLXSBJDMX_XSSX", "XLXSBJDMX_BJDID = '" + sBJDID + "'") + 1;
|
||
int iStartXH = iXH;
|
||
string sErrorNum = "";
|
||
|
||
try
|
||
{
|
||
dberp.BeginTrans();
|
||
sSQL.Clear();
|
||
sSQL.Add(" insert into XLXSBJDMX( ");
|
||
sSQL.Add(" XLXSBJDMX_BJDID, XLXSBJDMX_XH, XLXSBJDMX_XSSX, XLXSBJDMX_XLXHID, XLXSBJDMX_XLGGID, ");
|
||
sSQL.Add(" XLXSBJDMX_XLBZID,XLXSBJDMX_XDXLBZID,XLXSBJDMX_XDXLXHID, XLXSBJDMX_DYDJID, XLXSBJDMX_XLYSID, XLXSBJDMX_BJCP,XLXSBJDMX_BJCPSD,XLXSBJDMX_WGCPBZ, ");
|
||
sSQL.Add(" XLXSBJDMX_JLDW, XLXSBJDMX_BJSL, XLXSBJDMX_JGBBJ, XLXSBJDMX_TZQCPBJ,XLXSBJDMX_TZCPBJ,XLXSBJDMX_CPBJ,XLXSBJDMX_BJSDBZ,XLXSBJDMX_TZQBJJE, XLXSBJDMX_BJJE, XLXSBJDMX_JGBJJ,XLXSBJDMX_CLCB,XLXSBJDMX_JGBBJJCL,XLXSBJDMX_JGBBJJCE,XLXSBJDMX_CLZL, ");
|
||
sSQL.Add(" XLXSBJDMX_BJDHQCLCB,XLXSBJDMX_CLCBJCL,XLXSBJDMX_JGBBJLJJCL,XLXSBJDMX_YXJG,XLXSBJDMX_YZ,XLXSBJDMX_YSCP,XLXSBJDMX_FBH,XLXSBJDMX_CPBJXJ,XLXSBJDMX_BJJEXJ,XLXSBJDMX_BZ, XLXSBJDMX_DZSC,XLXSBJDMX_WHRID, XLXSBJDMX_WHR, XLXSBJDMX_WHSJ)");
|
||
sSQL.Add(" values(");
|
||
sSQL.Add(" :XLXSBJDMX_BJDID, :XLXSBJDMX_XH, :XLXSBJDMX_XSSX, :XLXSBJDMX_XLXHID, :XLXSBJDMX_XLGGID, ");
|
||
sSQL.Add(" :XLXSBJDMX_XLBZID,:XLXSBJDMX_XDXLBZID,:XLXSBJDMX_XDXLXHID, isnull(:XLXSBJDMX_DYDJID,' '), :XLXSBJDMX_XLYSID, :XLXSBJDMX_BJCP,:XLXSBJDMX_BJCPSD,:XLXSBJDMX_WGCPBZ, ");
|
||
sSQL.Add(" :XLXSBJDMX_JLDW, :XLXSBJDMX_BJSL, :XLXSBJDMX_JGBBJ, :XLXSBJDMX_TZQCPBJ,:XLXSBJDMX_TZCPBJ,:XLXSBJDMX_CPBJ,:XLXSBJDMX_BJSDBZ,:XLXSBJDMX_TZQBJJE, :XLXSBJDMX_BJJE, :XLXSBJDMX_JGBJJ,:XLXSBJDMX_CLCB,:XLXSBJDMX_JGBBJJCL,:XLXSBJDMX_JGBBJJCE,:XLXSBJDMX_CLZL, ");
|
||
sSQL.Add(" :XLXSBJDMX_BJDHQCLCB,:XLXSBJDMX_CLCBJCL,:XLXSBJDMX_JGBBJLJJCL,:XLXSBJDMX_YXJG,:XLXSBJDMX_YZ,:XLXSBJDMX_YSCP,:XLXSBJDMX_FBH,isnull(:XLXSBJDMX_CPBJXJ,0),isnull(:XLXSBJDMX_BJJEXJ,0),:XLXSBJDMX_BZ, :XLXSBJDMX_DZSC,:XLXSBJDMX_WHRID, :XLXSBJDMX_WHR, :XLXSBJDMX_WHSJ)");
|
||
sSQL.ParamByName("XLXSBJDMX_BJDID").Value = sBJDID;
|
||
|
||
for (int i = 0; i < LastRows; i++)
|
||
{
|
||
string sXLXHID = dt.Rows[i][0].ToString().Trim();
|
||
string sXLGGID = dt.Rows[i][1].ToString().Trim();
|
||
string sST = dt.Rows[i][2].ToString().Trim();
|
||
string sDYDJ = dt.Rows[i][3].ToString().Trim();
|
||
string sJLDW = dt.Rows[i][4].ToString().Trim();
|
||
string sBJSL = dt.Rows[i][5].ToString().Trim();
|
||
string sCPBJ = dt.Rows[i][6].ToString().Trim();
|
||
string sYXJG = "0";
|
||
string sYZ = "";
|
||
string sBZ = "";
|
||
string sBJCP = "";
|
||
string sYSCP = "";
|
||
string sBH = "";
|
||
string sCPBJXJ = "0";
|
||
string sBJJEXJ = "0";
|
||
|
||
if (indexYXJ >= 0 && indexYXJ < dt.Columns.Count)
|
||
sYXJG = dt.Rows[i][indexYXJ].ToString().Trim();
|
||
if (indexYZ >= 0 && indexYZ < dt.Columns.Count)
|
||
sYZ = dt.Rows[i][indexYZ].ToString().Trim();
|
||
if (indexBZ >= 0 && indexBZ < dt.Columns.Count)
|
||
sBZ = dt.Rows[i][indexBZ].ToString().Trim();
|
||
if (indexBJCP >= 0 && indexBJCP < dt.Columns.Count)
|
||
sBJCP = dt.Rows[i][indexBJCP].ToString().Trim();
|
||
if (indexYSCP >= 0 && indexYSCP < dt.Columns.Count)
|
||
sYSCP = dt.Rows[i][indexYSCP].ToString().Trim();
|
||
if (indexBH >= 0 && indexBH < dt.Columns.Count)
|
||
sBH = dt.Rows[i][indexBH].ToString().Trim();
|
||
if (indexCPBJXJ >= 0 && indexCPBJXJ < dt.Columns.Count)
|
||
sCPBJXJ = dt.Rows[i][indexCPBJXJ].ToString().Trim();
|
||
if (indexBJJEXJ >= 0 && indexBJJEXJ < dt.Columns.Count)
|
||
sBJJEXJ = dt.Rows[i][indexBJJEXJ].ToString().Trim();
|
||
|
||
//跳过空行
|
||
if (sXLXHID.Equals("") && sXLGGID.Equals(""))
|
||
continue;
|
||
|
||
//规格处理 A|× B|*
|
||
if (sXLJGU007.Equals("A"))
|
||
{
|
||
sXLGGID = sXLGGID.Replace("x", "X").Replace("X", "*").Replace("*", "×");
|
||
}
|
||
else if (sXLJGU007.Equals("B"))
|
||
{
|
||
sXLGGID = sXLGGID.Replace("x", "X").Replace("X", "*").Replace("×", "*");
|
||
}
|
||
|
||
//标准匹配
|
||
string sXLBZID = XLBJDUtils.GetBZXLZBZID(sST, sDefaultXLBZID, dicDRMXBZ);
|
||
|
||
//电压等级处理
|
||
sDYDJ = sDYDJ.Replace('K', 'k').Replace('v', 'V');
|
||
|
||
//材料清单匹配
|
||
string tmpXLXHID = sXLXHID;
|
||
string tmpXLGGID = sXLGGID;
|
||
string tmpXLBZID = sXLBZID;
|
||
string tmpDYDJID = sDYDJ;
|
||
bool bExistCLQD = XLBJDUtils.ExistCLQD(ref tmpXLXHID, ref tmpXLGGID, ref tmpXLBZID, ref tmpDYDJID);
|
||
if (bExistCLQD)
|
||
{
|
||
sXLXHID = tmpXLXHID;
|
||
sXLGGID = tmpXLGGID;
|
||
sXLBZID = tmpXLBZID;
|
||
sDYDJ = tmpDYDJID;
|
||
}
|
||
|
||
sSQL.ParamByName("XLXSBJDMX_XLXHID").Value = sXLXHID;
|
||
sSQL.ParamByName("XLXSBJDMX_XDXLXHID").Value = sXLXHID;
|
||
sSQL.ParamByName("XLXSBJDMX_XLGGID").Value = sXLGGID;
|
||
sSQL.ParamByName("XLXSBJDMX_XLBZID").Value = sXLBZID;
|
||
sSQL.ParamByName("XLXSBJDMX_XDXLBZID").Value = sXLBZID;
|
||
sSQL.ParamByName("XLXSBJDMX_DYDJID").Value = sDYDJ;
|
||
sSQL.ParamByName("XLXSBJDMX_XH").Value = iXH;
|
||
sSQL.ParamByName("XLXSBJDMX_XSSX").Value = iXSSX;
|
||
sSQL.ParamByName("XLXSBJDMX_XLYSID").Value = XLBJDInfo.GetDefaultYSID(sXLXHID, sXLGGID);
|
||
|
||
//报价产品
|
||
if (!sBJCP.Equals(""))
|
||
{
|
||
sSQL.ParamByName("XLXSBJDMX_BJCP").Value = sBJCP;
|
||
sSQL.ParamByName("XLXSBJDMX_BJCPSD").Value = "Y";
|
||
}
|
||
else
|
||
{
|
||
if (!sDYDJ.Equals(""))
|
||
sSQL.ParamByName("XLXSBJDMX_BJCP").Value = sXLXHID + "-" + sDYDJ + " " + sXLGGID;
|
||
else
|
||
sSQL.ParamByName("XLXSBJDMX_BJCP").Value = sXLXHID + " " + sXLGGID;
|
||
sSQL.ParamByName("XLXSBJDMX_BJCPSD").Value = "N";
|
||
}
|
||
|
||
//计量单位
|
||
if (sJLDW.Trim().Equals(""))
|
||
{
|
||
string sMRJLDW = XLBJDInfo.GetMRJLDWByJBXH(sXLXHID);
|
||
if (sMRJLDW.Equals(""))
|
||
sSQL.ParamByName("XLXSBJDMX_JLDW").Value = sBJU003;
|
||
else
|
||
sSQL.ParamByName("XLXSBJDMX_JLDW").Value = sMRJLDW;
|
||
}
|
||
else
|
||
{
|
||
if (sJLDW.Trim().ToUpper().Equals("KM"))
|
||
sSQL.ParamByName("XLXSBJDMX_JLDW").Value = "Km";
|
||
else if (sJLDW.Trim().ToUpper().Equals("M"))
|
||
sSQL.ParamByName("XLXSBJDMX_JLDW").Value = "M";
|
||
else if (sJLDW.Trim().ToUpper().Equals("KG"))
|
||
sSQL.ParamByName("XLXSBJDMX_JLDW").Value = "Kg";
|
||
else if (sJLDW.Trim().ToUpper().Equals("T"))
|
||
sSQL.ParamByName("XLXSBJDMX_JLDW").Value = "T";
|
||
else
|
||
sSQL.ParamByName("XLXSBJDMX_JLDW").Value = sJLDW.Trim();
|
||
}
|
||
|
||
sSQL.ParamByName("XLXSBJDMX_BJSL").Value = CMISproConvert.StrToFloat(sBJSL, 0);
|
||
sSQL.ParamByName("XLXSBJDMX_CPBJ").Value = CMISproConvert.StrToFloat(sCPBJ, 0);
|
||
|
||
sSQL.ParamByName("XLXSBJDMX_JGBBJ").Value = 0.0;
|
||
sSQL.ParamByName("XLXSBJDMX_TZQCPBJ").Value = 0.0;
|
||
sSQL.ParamByName("XLXSBJDMX_TZCPBJ").Value = 0;
|
||
sSQL.ParamByName("XLXSBJDMX_YXJG").Value = CMISproConvert.StrToFloat(sYXJG, 0);
|
||
sSQL.ParamByName("XLXSBJDMX_YZ").Value = sYZ;
|
||
sSQL.ParamByName("XLXSBJDMX_YSCP").Value = sYSCP;
|
||
sSQL.ParamByName("XLXSBJDMX_FBH").Value = sBH;
|
||
sSQL.ParamByName("XLXSBJDMX_CPBJXJ").Value = CMISproConvert.StrToFloat(sCPBJXJ, 0);
|
||
sSQL.ParamByName("XLXSBJDMX_BJJEXJ").Value = CMISproConvert.StrToFloat(sBJJEXJ, 0);
|
||
sSQL.ParamByName("XLXSBJDMX_BZ").Value = sBZ;
|
||
|
||
if (CMISproConvert.StrToFloat(sCPBJ, 0) != 0)
|
||
{
|
||
sSQL.ParamByName("XLXSBJDMX_BJSDBZ").Value = "Y";
|
||
}
|
||
else
|
||
{
|
||
sSQL.ParamByName("XLXSBJDMX_BJSDBZ").Value = "N";
|
||
}
|
||
|
||
sSQL.ParamByName("XLXSBJDMX_JGBJJ").Value = 0.0;
|
||
sSQL.ParamByName("XLXSBJDMX_CLCB").Value = 0.0;
|
||
sSQL.ParamByName("XLXSBJDMX_BJDHQCLCB").Value = 0.0;
|
||
sSQL.ParamByName("XLXSBJDMX_CLZL").Value = 0.0;
|
||
|
||
sSQL.ParamByName("XLXSBJDMX_BJJE").Value = NumberUtils.RoundX(CMISproConvert.StrToFloat(sBJSL, 0) * CMISproConvert.StrToFloat(sCPBJ, 0), 2);
|
||
sSQL.ParamByName("XLXSBJDMX_TZQBJJE").Value = sSQL.ParamByName("XLXSBJDMX_BJJE").Value;
|
||
|
||
sSQL.ParamByName("XLXSBJDMX_JGBBJJCE").Value = 0;
|
||
sSQL.ParamByName("XLXSBJDMX_JGBBJJCL").Value = 0;
|
||
sSQL.ParamByName("XLXSBJDMX_CLCBJCL").Value = 0;
|
||
sSQL.ParamByName("XLXSBJDMX_JGBBJLJJCL").Value = 0;
|
||
|
||
sSQL.ParamByName("XLXSBJDMX_WGCPBZ").Value = "N";
|
||
if (XLBJDUtils.ISDZBZ(sXLBZID))
|
||
{
|
||
sSQL.ParamByName("XLXSBJDMX_DZSC").Value = "Y";
|
||
}
|
||
else
|
||
{
|
||
sSQL.ParamByName("XLXSBJDMX_DZSC").Value = "N";
|
||
}
|
||
sSQL.ParamByName("XLXSBJDMX_WHR").Value = tSession.UserName;
|
||
sSQL.ParamByName("XLXSBJDMX_WHRID").Value = tSession.UserId;
|
||
sSQL.ParamByName("XLXSBJDMX_WHSJ").Value = CMISproConvert.ConvertTime(DateTime.Now);
|
||
dberp.ExecSQL(sSQL);
|
||
|
||
iXH++;
|
||
iXSSX++;
|
||
}
|
||
XLBJDUtils.SyncXLXSBJXLGGTHB_ByXLXSBJDMX_Batch(dberp, tSession, sBJDID, "", iStartXH - 1);
|
||
|
||
//匹配标准电压等级
|
||
if (!string.IsNullOrEmpty(sBJU038) && sBJU038.Contains(sBJFLID))
|
||
{
|
||
PPBZDY(dberp, sSQL, sBJDID, iStartXH, sJGBID, sMRBZID);
|
||
}
|
||
|
||
XLBJDUtils.UpdateXDXLBZID(dberp, sSQL, sBJDID, "", iStartXH - 1, ref sErrorNum);
|
||
|
||
XLBJDCalcUtils.CalcBJDMXAfterIns(dberp, sSQL, sBJDID, iStartXH, sBJU002, sBJU003, sBJU018, tSession.UserId, tSession.UserName, ref sErrorNum);
|
||
|
||
dberp.CommitTrans();
|
||
DialogOperationScript dos = new DialogOperationScript(this);
|
||
dos.CloseSelf();
|
||
dos.Execute();
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
dberp.RollbackTrans();
|
||
ShowMessage(ex.Message);
|
||
}
|
||
}
|
||
|
||
protected void btnQX_Click(object sender, EventArgs e)
|
||
{
|
||
CloseWindow();
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 自定义事件
|
||
|
||
public DataTable ConvertJsonToDataTable(string jsonData)
|
||
{
|
||
DataTable dataTable = new DataTable();
|
||
|
||
// 将JSON数据转换为JArray
|
||
JArray jsonArray = JArray.Parse(jsonData);
|
||
int index2 = 0;
|
||
// 创建表的列
|
||
if (jsonArray.Count > 0)
|
||
{
|
||
foreach (JToken token in jsonArray[0])
|
||
{
|
||
//第一列是标题，所以去掉
|
||
if (index2 > 0)
|
||
{
|
||
// 获取所有子元素
|
||
foreach (JToken child in token.Children())
|
||
{
|
||
dataTable.Columns.Add(child.ToString(), typeof(string));
|
||
break;
|
||
}
|
||
}
|
||
index2++;
|
||
}
|
||
}
|
||
|
||
int index = 0;
|
||
|
||
// 将JSON数据填充到DataTable中
|
||
foreach (JObject jsonObject in jsonArray)
|
||
{
|
||
//第一行是标题，所以去掉
|
||
if (index > 0)
|
||
{
|
||
index2 = 0;
|
||
DataRow row = dataTable.NewRow();
|
||
foreach (JProperty property in jsonObject.Properties())
|
||
{
|
||
//第一列是标题，所以去掉
|
||
if (index2 > 0)
|
||
{
|
||
row[property.Name] = property.Value.ToString();
|
||
}
|
||
index2++;
|
||
}
|
||
dataTable.Rows.Add(row);
|
||
}
|
||
|
||
index++;
|
||
}
|
||
|
||
return dataTable;
|
||
}
|
||
|
||
private void PPBZDY(TDatabase dberp, TSQL SQL, string sBJDID, int startBJDXH, string sJGBID, string sMRBZID)
|
||
{
|
||
if (!string.IsNullOrEmpty(sMRBZID))
|
||
{
|
||
#region 根据型号、规格，匹配唯一标准、电压等级
|
||
SQL.Clear();
|
||
SQL.Add("merge into XLXSBJDMX tar ");
|
||
SQL.Add(" using ( ");
|
||
SQL.Add(" select XLXSJGBMX_XLXHID as XLXHID,XLXSJGBMX_XLGGID as XLGGID, XLXSJGBMX_XLBZID as XLBZID, XLXSJGBMX_DYDJID as DYDJID ");
|
||
SQL.Add(" from XLXSJGBMX b ");
|
||
SQL.Add(" where 1=1 and " + CMISproDataBase.GetWhereEqual("XLXSJGBMX_JGBID", sJGBID));
|
||
SQL.Add(" and XLXSJGBMX_XLBZID=:pXLBZID ");
|
||
SQL.Add(" and exists(select 1 from XLXSBJDMX tmp where XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" and XLXSBJDMX_XLXHID=b.XLXSJGBMX_XLXHID");
|
||
SQL.Add(" and XLXSBJDMX_XLGGID=b.XLXSJGBMX_XLGGID");
|
||
SQL.Add(" )");
|
||
SQL.Add(" and exists ( ");
|
||
SQL.Add(" select a.XLXHID, a.XLGGID from ");
|
||
SQL.Add(" ( ");
|
||
SQL.Add(" select XLXSJGBMX_XLXHID as XLXHID,XLXSJGBMX_XLGGID as XLGGID ");
|
||
SQL.Add(" from XLXSJGBMX");
|
||
SQL.Add(" where 1=1 and " + CMISproDataBase.GetWhereEqual("XLXSJGBMX_JGBID", sJGBID));
|
||
SQL.Add(" and XLXSJGBMX_XLBZID=:pXLBZID ");
|
||
SQL.Add(" and exists(select 1 from XLXSBJDMX tmp where XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" and XLXSBJDMX_XLXHID=XLXSJGBMX_XLXHID");
|
||
SQL.Add(" and XLXSBJDMX_XLGGID=XLXSJGBMX_XLGGID");
|
||
SQL.Add(" )");
|
||
SQL.Add(" group by XLXSJGBMX_XLXHID,XLXSJGBMX_XLGGID ");
|
||
SQL.Add(" having (count(XLXSJGBMX_XLXHID) + count(XLXSJGBMX_XLGGID)) = 2 ");
|
||
SQL.Add(" ) a where 1=1 and a.XLXHID = b.XLXSJGBMX_XLXHID and a.XLGGID =b.XLXSJGBMX_XLGGID ");
|
||
SQL.Add(" ) ");
|
||
SQL.Add(" ) sor ");
|
||
SQL.Add(" on( ");
|
||
SQL.Add(" tar.XLXSBJDMX_XLXHID = sor.XLXHID ");
|
||
SQL.Add(" and tar.XLXSBJDMX_XLGGID = sor.XLGGID ");
|
||
SQL.Add(" and tar.XLXSBJDMX_XLXHID <> ' ' and tar.XLXSBJDMX_XLGGID <> ' ' ");
|
||
SQL.Add(" and tar.XLXSBJDMX_XLBZID = ' ' and tar.XLXSBJDMX_DYDJID = ' ' ");
|
||
SQL.Add(" and tar.XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" and tar.XLXSBJDMX_XH>=:pSTARTXH ");
|
||
SQL.Add(" ) ");
|
||
SQL.Add(" when matched then update set ");
|
||
SQL.Add(" tar.XLXSBJDMX_XLBZID =sor.XLBZID ");
|
||
SQL.Add(", tar.XLXSBJDMX_DYDJID=sor.DYDJID ");
|
||
SQL.ParamByName("pXLBZID").Value = sMRBZID;
|
||
SQL.ParamByName("pBJDID").Value = sBJDID;
|
||
SQL.ParamByName("pSTARTXH").Value = startBJDXH;
|
||
dberp.ExecSQL(SQL);
|
||
#endregion
|
||
|
||
#region 根据型号、规格，匹配唯一标准、电压等级（材料清单）
|
||
SQL.Clear();
|
||
SQL.Add(" merge into XLXSBJDMX tag");
|
||
SQL.Add(" using ");
|
||
SQL.Add(" ( ");
|
||
SQL.Add(" select XLXSCLQD_XLXHID,XLXSCLQD_XLGGID,XLXSCLQD_XLBZID,XLXSCLQD_DYDJID FROM XLXSCLQD a ");
|
||
SQL.Add(" where XLXSCLQD_WPID = 'ZZZZ' ");
|
||
SQL.Add(" and XLXSCLQD_XLBZID = :pXLBZID ");
|
||
SQL.Add(" and exists ( ");
|
||
SQL.Add(" SELECT XLXSCLQD_XLXHID,XLXSCLQD_XLGGID FROM XLXSCLQD b ");
|
||
SQL.Add(" WHERE a.XLXSCLQD_XLXHID = b.XLXSCLQD_XLXHID and a.XLXSCLQD_XLGGID = b.XLXSCLQD_XLGGID ");
|
||
SQL.Add(" and b.XLXSCLQD_WPID = 'ZZZZ' and b.XLXSCLQD_XLBZID = :pXLBZID and exists( ");
|
||
SQL.Add(" select XLXSBJDMX_XLXHID, XLXSBJDMX_XLGGID from XLXSBJDMX WHERE XLXSBJDMX_XLXHID = XLXSCLQD_XLXHID ");
|
||
SQL.Add(" and XLXSBJDMX_XLGGID= XLXSCLQD_XLGGID ");
|
||
SQL.Add(" and XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" ) ");
|
||
SQL.Add(" group by XLXSCLQD_XLXHID,XLXSCLQD_XLGGID ");
|
||
SQL.Add(" having (count(XLXSCLQD_XLXHID) + count(XLXSCLQD_XLGGID)) = 2 ");
|
||
SQL.Add(" ) ");
|
||
SQL.Add(" ) c ");
|
||
SQL.Add(" on ( XLXSBJDMX_XLXHID <> ' ' and XLXSBJDMX_XLGGID <> ' ' ");
|
||
SQL.Add(" and XLXSBJDMX_XLBZID = ' ' and XLXSBJDMX_DYDJID = ' ' ");
|
||
SQL.Add(" and XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" and XLXSBJDMX_XH>=:pSTARTXH ");
|
||
SQL.Add(" and c.XLXSCLQD_XLXHID =XLXSBJDMX_XLXHID and c.XLXSCLQD_XLGGID=XLXSBJDMX_XLGGID ) ");
|
||
SQL.Add(" when matched then update set XLXSBJDMX_XLBZID =c.XLXSCLQD_XLBZID ");
|
||
SQL.Add(" , XLXSBJDMX_DYDJID=c.XLXSCLQD_DYDJID ");
|
||
SQL.ParamByName("pXLBZID").Value = sMRBZID;
|
||
SQL.ParamByName("pBJDID").Value = sBJDID;
|
||
SQL.ParamByName("pSTARTXH").Value = startBJDXH;
|
||
dberp.ExecSQL(SQL);
|
||
#endregion
|
||
|
||
#region 根据型号、规格、电压等级，匹配唯一标准
|
||
SQL.Clear();
|
||
SQL.Add("merge into XLXSBJDMX tar ");
|
||
SQL.Add(" using ( ");
|
||
SQL.Add(" select XLXSJGBMX_XLXHID as XLXHID,XLXSJGBMX_XLGGID as XLGGID, XLXSJGBMX_XLBZID as XLBZID, XLXSJGBMX_DYDJID as DYDJID ");
|
||
SQL.Add(" from XLXSJGBMX b ");
|
||
SQL.Add(" where 1=1 and " + CMISproDataBase.GetWhereEqual("XLXSJGBMX_JGBID", sJGBID));
|
||
SQL.Add(" and XLXSJGBMX_XLBZID=:pXLBZID ");
|
||
SQL.Add(" and exists(select 1 from XLXSBJDMX tmp where XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" and XLXSBJDMX_XLXHID=b.XLXSJGBMX_XLXHID");
|
||
SQL.Add(" and XLXSBJDMX_XLGGID=b.XLXSJGBMX_XLGGID");
|
||
SQL.Add(" and XLXSBJDMX_DYDJID=b.XLXSJGBMX_DYDJID");
|
||
SQL.Add(" )");
|
||
SQL.Add(" and exists ( ");
|
||
SQL.Add(" select a.XLXHID, a.XLGGID from ");
|
||
SQL.Add(" ( ");
|
||
SQL.Add(" select XLXSJGBMX_XLXHID as XLXHID,XLXSJGBMX_XLGGID as XLGGID, XLXSJGBMX_DYDJID as DYDJID ");
|
||
SQL.Add(" from XLXSJGBMX");
|
||
SQL.Add(" where 1=1 and " + CMISproDataBase.GetWhereEqual("XLXSJGBMX_JGBID", sJGBID));
|
||
SQL.Add(" and XLXSJGBMX_XLBZID=:pXLBZID ");
|
||
SQL.Add(" and exists(select 1 from XLXSBJDMX tmp where XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" and XLXSBJDMX_XLXHID=XLXSJGBMX_XLXHID");
|
||
SQL.Add(" and XLXSBJDMX_XLGGID=XLXSJGBMX_XLGGID");
|
||
SQL.Add(" and XLXSBJDMX_DYDJID=XLXSJGBMX_DYDJID");
|
||
SQL.Add(" )");
|
||
SQL.Add(" group by XLXSJGBMX_XLXHID,XLXSJGBMX_XLGGID, XLXSJGBMX_DYDJID ");
|
||
SQL.Add(" having (count(XLXSJGBMX_XLXHID) + count(XLXSJGBMX_XLGGID) + count(XLXSJGBMX_DYDJID)) = 3 ");
|
||
SQL.Add(" ) a where 1=1 and a.XLXHID = b.XLXSJGBMX_XLXHID and a.XLGGID =b.XLXSJGBMX_XLGGID and a.DYDJID = b.XLXSJGBMX_DYDJID ");
|
||
SQL.Add(" ) ");
|
||
SQL.Add(" ) sor ");
|
||
SQL.Add(" on( ");
|
||
SQL.Add(" tar.XLXSBJDMX_XLXHID = sor.XLXHID ");
|
||
SQL.Add(" and tar.XLXSBJDMX_XLGGID = sor.XLGGID ");
|
||
SQL.Add(" and tar.XLXSBJDMX_DYDJID=sor.DYDJID ");
|
||
SQL.Add(" and tar.XLXSBJDMX_XLXHID <> ' ' and tar.XLXSBJDMX_XLGGID <> ' ' ");
|
||
SQL.Add(" and tar.XLXSBJDMX_XLBZID = ' ' and tar.XLXSBJDMX_DYDJID <> ' ' ");
|
||
SQL.Add(" and tar.XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" and tar.XLXSBJDMX_XH>=:pSTARTXH ");
|
||
SQL.Add(" ) ");
|
||
SQL.Add(" when matched then update set ");
|
||
SQL.Add(" tar.XLXSBJDMX_XLBZID =sor.XLBZID ");
|
||
SQL.ParamByName("pBJDID").Value = sBJDID;
|
||
SQL.ParamByName("pSTARTXH").Value = startBJDXH;
|
||
SQL.ParamByName("pXLBZID").Value = sMRBZID;
|
||
dberp.ExecSQL(SQL);
|
||
#endregion
|
||
|
||
#region 根据型号、规格、电压等级，匹配唯一标准（材料清单）
|
||
SQL.Clear();
|
||
SQL.Add(" merge into XLXSBJDMX tag ");
|
||
SQL.Add(" using ");
|
||
SQL.Add(" ( ");
|
||
SQL.Add(" select XLXSCLQD_XLXHID,XLXSCLQD_XLGGID,XLXSCLQD_XLBZID,XLXSCLQD_DYDJID FROM XLXSCLQD a ");
|
||
SQL.Add(" where 1 = 1 and XLXSCLQD_WPID = 'ZZZZ' ");
|
||
SQL.Add(" and XLXSCLQD_XLBZID = :pXLBZID ");
|
||
SQL.Add(" and exists ( ");
|
||
SQL.Add(" SELECT XLXSCLQD_XLXHID,XLXSCLQD_XLGGID,XLXSCLQD_DYDJID FROM XLXSCLQD b ");
|
||
SQL.Add(" WHERE 1 = 1 and a.XLXSCLQD_XLXHID = b.XLXSCLQD_XLXHID and a.XLXSCLQD_XLGGID = b.XLXSCLQD_XLGGID ");
|
||
SQL.Add(" and a.XLXSCLQD_DYDJID = b.XLXSCLQD_DYDJID ");
|
||
SQL.Add(" and b.XLXSCLQD_WPID = 'ZZZZ'and b.XLXSCLQD_XLBZID = :pXLBZID and exists( ");
|
||
SQL.Add(" select XLXSBJDMX_XLXHID, XLXSBJDMX_XLGGID,XLXSCLQD_DYDJID from XLXSBJDMX WHERE XLXSBJDMX_XLXHID = XLXSCLQD_XLXHID ");
|
||
SQL.Add(" and XLXSBJDMX_XLGGID= XLXSCLQD_XLGGID");
|
||
SQL.Add(" and XLXSBJDMX_DYDJID=XLXSCLQD_DYDJID");
|
||
SQL.Add(" and XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" ) ");
|
||
SQL.Add(" group by XLXSCLQD_XLXHID,XLXSCLQD_XLGGID,XLXSCLQD_DYDJID ");
|
||
SQL.Add(" having (count(XLXSCLQD_XLXHID) + count(XLXSCLQD_XLGGID) + count(XLXSCLQD_DYDJID)) = 3 ");
|
||
SQL.Add(" ) ");
|
||
SQL.Add(" ) c ");
|
||
SQL.Add(" on ( XLXSBJDMX_XLXHID <> ' ' and XLXSBJDMX_XLGGID <> ' ' ");
|
||
SQL.Add(" and XLXSBJDMX_XLBZID = ' ' and XLXSBJDMX_DYDJID <> ' ' ");
|
||
SQL.Add(" and XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" and XLXSBJDMX_XH>=:pSTARTXH ");
|
||
SQL.Add(" and c.XLXSCLQD_XLXHID =XLXSBJDMX_XLXHID and c.XLXSCLQD_XLGGID=XLXSBJDMX_XLGGID and c.XLXSCLQD_DYDJID=XLXSBJDMX_DYDJID ) ");
|
||
SQL.Add(" when matched then update set XLXSBJDMX_XLBZID =c.XLXSCLQD_XLBZID ");
|
||
SQL.ParamByName("pBJDID").Value = sBJDID;
|
||
SQL.ParamByName("pSTARTXH").Value = startBJDXH;
|
||
SQL.ParamByName("pXLBZID").Value = sMRBZID;
|
||
dberp.ExecSQL(SQL);
|
||
#endregion
|
||
}
|
||
|
||
//先价格表，再材料清单
|
||
#region 根据型号、规格，匹配唯一标准、电压等级
|
||
SQL.Clear();
|
||
SQL.Add("merge into XLXSBJDMX tar ");
|
||
SQL.Add(" using ( ");
|
||
SQL.Add(" select XLXSJGBMX_XLXHID as XLXHID,XLXSJGBMX_XLGGID as XLGGID, XLXSJGBMX_XLBZID as XLBZID, XLXSJGBMX_DYDJID as DYDJID ");
|
||
SQL.Add(" from XLXSJGBMX b ");
|
||
SQL.Add(" where 1=1 and " + CMISproDataBase.GetWhereEqual("XLXSJGBMX_JGBID", sJGBID));
|
||
SQL.Add(" and exists(select 1 from XLXSBJDMX tmp where XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" and XLXSBJDMX_XLXHID=b.XLXSJGBMX_XLXHID");
|
||
SQL.Add(" and XLXSBJDMX_XLGGID=b.XLXSJGBMX_XLGGID");
|
||
SQL.Add(" )");
|
||
SQL.Add(" and exists ( ");
|
||
SQL.Add(" select a.XLXHID, a.XLGGID from ");
|
||
SQL.Add(" ( ");
|
||
SQL.Add(" select XLXSJGBMX_XLXHID as XLXHID,XLXSJGBMX_XLGGID as XLGGID ");
|
||
SQL.Add(" from XLXSJGBMX");
|
||
SQL.Add(" where 1=1 and " + CMISproDataBase.GetWhereEqual("XLXSJGBMX_JGBID", sJGBID));
|
||
SQL.Add(" and exists(select 1 from XLXSBJDMX tmp where XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" and XLXSBJDMX_XLXHID=XLXSJGBMX_XLXHID");
|
||
SQL.Add(" and XLXSBJDMX_XLGGID=XLXSJGBMX_XLGGID");
|
||
SQL.Add(" )");
|
||
SQL.Add(" group by XLXSJGBMX_XLXHID,XLXSJGBMX_XLGGID ");
|
||
SQL.Add(" having (count(XLXSJGBMX_XLXHID) + count(XLXSJGBMX_XLGGID)) = 2 ");
|
||
SQL.Add(" ) a where 1=1 and a.XLXHID = b.XLXSJGBMX_XLXHID and a.XLGGID =b.XLXSJGBMX_XLGGID ");
|
||
SQL.Add(" ) ");
|
||
SQL.Add(" ) sor ");
|
||
SQL.Add(" on( ");
|
||
SQL.Add(" tar.XLXSBJDMX_XLXHID = sor.XLXHID ");
|
||
SQL.Add(" and tar.XLXSBJDMX_XLGGID = sor.XLGGID ");
|
||
SQL.Add(" and tar.XLXSBJDMX_XLXHID <> ' ' and tar.XLXSBJDMX_XLGGID <> ' ' ");
|
||
SQL.Add(" and tar.XLXSBJDMX_XLBZID = ' ' and tar.XLXSBJDMX_DYDJID = ' ' ");
|
||
SQL.Add(" and tar.XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" and tar.XLXSBJDMX_XH>=:pSTARTXH ");
|
||
SQL.Add(" ) ");
|
||
SQL.Add(" when matched then update set ");
|
||
SQL.Add(" tar.XLXSBJDMX_XLBZID =sor.XLBZID ");
|
||
SQL.Add(", tar.XLXSBJDMX_DYDJID=sor.DYDJID ");
|
||
SQL.ParamByName("pBJDID").Value = sBJDID;
|
||
SQL.ParamByName("pSTARTXH").Value = startBJDXH;
|
||
dberp.ExecSQL(SQL);
|
||
#endregion
|
||
|
||
#region 根据型号、规格、标准，匹配唯一电压等级
|
||
SQL.Clear();
|
||
SQL.Add("merge into XLXSBJDMX tar ");
|
||
SQL.Add(" using ( ");
|
||
SQL.Add(" select XLXSJGBMX_XLXHID as XLXHID,XLXSJGBMX_XLGGID as XLGGID, XLXSJGBMX_XLBZID as XLBZID, XLXSJGBMX_DYDJID as DYDJID ");
|
||
SQL.Add(" from XLXSJGBMX b ");
|
||
SQL.Add(" where 1=1 and " + CMISproDataBase.GetWhereEqual("XLXSJGBMX_JGBID", sJGBID));
|
||
SQL.Add(" and exists(select 1 from XLXSBJDMX tmp where XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" and XLXSBJDMX_XLXHID=b.XLXSJGBMX_XLXHID");
|
||
SQL.Add(" and XLXSBJDMX_XLGGID=b.XLXSJGBMX_XLGGID");
|
||
SQL.Add(" and XLXSBJDMX_XLBZID=b.XLXSJGBMX_XLBZID");
|
||
SQL.Add(" )");
|
||
SQL.Add(" and exists ( ");
|
||
SQL.Add(" select a.XLXHID, a.XLGGID from ");
|
||
SQL.Add(" ( ");
|
||
SQL.Add(" select XLXSJGBMX_XLXHID as XLXHID,XLXSJGBMX_XLGGID as XLGGID, XLXSJGBMX_XLBZID as XLBZID ");
|
||
SQL.Add(" from XLXSJGBMX");
|
||
SQL.Add(" where 1=1 and " + CMISproDataBase.GetWhereEqual("XLXSJGBMX_JGBID", sJGBID));
|
||
SQL.Add(" and exists(select 1 from XLXSBJDMX tmp where XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" and XLXSBJDMX_XLXHID=XLXSJGBMX_XLXHID");
|
||
SQL.Add(" and XLXSBJDMX_XLGGID=XLXSJGBMX_XLGGID");
|
||
SQL.Add(" and XLXSBJDMX_XLBZID=XLXSJGBMX_XLBZID");
|
||
SQL.Add(" )");
|
||
SQL.Add(" group by XLXSJGBMX_XLXHID,XLXSJGBMX_XLGGID, XLXSJGBMX_XLBZID ");
|
||
SQL.Add(" having (count(XLXSJGBMX_XLXHID) + count(XLXSJGBMX_XLGGID) + count(XLXSJGBMX_XLBZID)) = 3 ");
|
||
SQL.Add(" ) a where 1=1 and a.XLXHID = b.XLXSJGBMX_XLXHID and a.XLGGID =b.XLXSJGBMX_XLGGID and a.XLBZID = b.XLXSJGBMX_XLBZID ");
|
||
SQL.Add(" ) ");
|
||
SQL.Add(" ) sor ");
|
||
SQL.Add(" on( ");
|
||
SQL.Add(" tar.XLXSBJDMX_XLXHID = sor.XLXHID ");
|
||
SQL.Add(" and tar.XLXSBJDMX_XLGGID = sor.XLGGID ");
|
||
SQL.Add(" and tar.XLXSBJDMX_XLBZID = sor.XLBZID ");
|
||
SQL.Add(" and tar.XLXSBJDMX_XLXHID <> ' ' and tar.XLXSBJDMX_XLGGID <> ' ' ");
|
||
SQL.Add(" and tar.XLXSBJDMX_XLBZID <> ' ' and tar.XLXSBJDMX_DYDJID = ' ' ");
|
||
SQL.Add(" and tar.XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" and tar.XLXSBJDMX_XH>=:pSTARTXH ");
|
||
SQL.Add(" ) ");
|
||
SQL.Add(" when matched then update set ");
|
||
SQL.Add(" tar.XLXSBJDMX_DYDJID=sor.DYDJID ");
|
||
SQL.ParamByName("pBJDID").Value = sBJDID;
|
||
SQL.ParamByName("pSTARTXH").Value = startBJDXH;
|
||
dberp.ExecSQL(SQL);
|
||
#endregion
|
||
|
||
#region 根据型号、规格、电压等级，匹配唯一标准
|
||
SQL.Clear();
|
||
SQL.Add("merge into XLXSBJDMX tar ");
|
||
SQL.Add(" using ( ");
|
||
SQL.Add(" select XLXSJGBMX_XLXHID as XLXHID,XLXSJGBMX_XLGGID as XLGGID, XLXSJGBMX_XLBZID as XLBZID, XLXSJGBMX_DYDJID as DYDJID ");
|
||
SQL.Add(" from XLXSJGBMX b ");
|
||
SQL.Add(" where 1=1 and " + CMISproDataBase.GetWhereEqual("XLXSJGBMX_JGBID", sJGBID));
|
||
SQL.Add(" and exists(select 1 from XLXSBJDMX tmp where XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" and XLXSBJDMX_XLXHID=b.XLXSJGBMX_XLXHID");
|
||
SQL.Add(" and XLXSBJDMX_XLGGID=b.XLXSJGBMX_XLGGID");
|
||
SQL.Add(" and XLXSBJDMX_DYDJID=b.XLXSJGBMX_DYDJID");
|
||
SQL.Add(" )");
|
||
SQL.Add(" and exists ( ");
|
||
SQL.Add(" select a.XLXHID, a.XLGGID from ");
|
||
SQL.Add(" ( ");
|
||
SQL.Add(" select XLXSJGBMX_XLXHID as XLXHID,XLXSJGBMX_XLGGID as XLGGID, XLXSJGBMX_DYDJID as DYDJID ");
|
||
SQL.Add(" from XLXSJGBMX");
|
||
SQL.Add(" where 1=1 and " + CMISproDataBase.GetWhereEqual("XLXSJGBMX_JGBID", sJGBID));
|
||
SQL.Add(" and exists(select 1 from XLXSBJDMX tmp where XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" and XLXSBJDMX_XLXHID=XLXSJGBMX_XLXHID");
|
||
SQL.Add(" and XLXSBJDMX_XLGGID=XLXSJGBMX_XLGGID");
|
||
SQL.Add(" and XLXSBJDMX_DYDJID=XLXSJGBMX_DYDJID");
|
||
SQL.Add(" )");
|
||
SQL.Add(" group by XLXSJGBMX_XLXHID,XLXSJGBMX_XLGGID, XLXSJGBMX_DYDJID ");
|
||
SQL.Add(" having (count(XLXSJGBMX_XLXHID) + count(XLXSJGBMX_XLGGID) + count(XLXSJGBMX_DYDJID)) = 3 ");
|
||
SQL.Add(" ) a where 1=1 and a.XLXHID = b.XLXSJGBMX_XLXHID and a.XLGGID =b.XLXSJGBMX_XLGGID and a.DYDJID = b.XLXSJGBMX_DYDJID ");
|
||
SQL.Add(" ) ");
|
||
SQL.Add(" ) sor ");
|
||
SQL.Add(" on( ");
|
||
SQL.Add(" tar.XLXSBJDMX_XLXHID = sor.XLXHID ");
|
||
SQL.Add(" and tar.XLXSBJDMX_XLGGID = sor.XLGGID ");
|
||
SQL.Add(" and tar.XLXSBJDMX_DYDJID=sor.DYDJID ");
|
||
SQL.Add(" and tar.XLXSBJDMX_XLXHID <> ' ' and tar.XLXSBJDMX_XLGGID <> ' ' ");
|
||
SQL.Add(" and tar.XLXSBJDMX_XLBZID = ' ' and tar.XLXSBJDMX_DYDJID <> ' ' ");
|
||
SQL.Add(" and tar.XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" and tar.XLXSBJDMX_XH>=:pSTARTXH ");
|
||
SQL.Add(" ) ");
|
||
SQL.Add(" when matched then update set ");
|
||
SQL.Add(" tar.XLXSBJDMX_XLBZID =sor.XLBZID ");
|
||
SQL.ParamByName("pBJDID").Value = sBJDID;
|
||
SQL.ParamByName("pSTARTXH").Value = startBJDXH;
|
||
dberp.ExecSQL(SQL);
|
||
#endregion
|
||
|
||
#region 根据型号、规格，匹配唯一标准、电压等级（材料清单-无条件）
|
||
SQL.Clear();
|
||
SQL.Add(" merge into XLXSBJDMX tag");
|
||
SQL.Add(" using ");
|
||
SQL.Add(" ( ");
|
||
SQL.Add(" select XLXSCLQD_XLXHID,XLXSCLQD_XLGGID,XLXSCLQD_XLBZID,XLXSCLQD_DYDJID FROM XLXSCLQD a ");
|
||
SQL.Add(" where XLXSCLQD_WPID = 'ZZZZ' ");
|
||
SQL.Add(" and exists ( ");
|
||
SQL.Add(" SELECT XLXSCLQD_XLXHID,XLXSCLQD_XLGGID FROM XLXSCLQD b ");
|
||
SQL.Add(" WHERE a.XLXSCLQD_XLXHID = b.XLXSCLQD_XLXHID and a.XLXSCLQD_XLGGID = b.XLXSCLQD_XLGGID ");
|
||
SQL.Add(" and b.XLXSCLQD_WPID = 'ZZZZ' and exists( ");
|
||
SQL.Add(" select XLXSBJDMX_XLXHID, XLXSBJDMX_XLGGID from XLXSBJDMX WHERE XLXSBJDMX_XLXHID = XLXSCLQD_XLXHID ");
|
||
SQL.Add(" and XLXSBJDMX_XLGGID= XLXSCLQD_XLGGID ");
|
||
SQL.Add(" and XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" ) ");
|
||
SQL.Add(" group by XLXSCLQD_XLXHID,XLXSCLQD_XLGGID ");
|
||
SQL.Add(" having (count(XLXSCLQD_XLXHID) + count(XLXSCLQD_XLGGID)) = 2 ");
|
||
SQL.Add(" ) ");
|
||
SQL.Add(" ) c ");
|
||
SQL.Add(" on ( XLXSBJDMX_XLXHID <> ' ' and XLXSBJDMX_XLGGID <> ' ' ");
|
||
SQL.Add(" and XLXSBJDMX_XLBZID = ' ' and XLXSBJDMX_DYDJID = ' ' ");
|
||
SQL.Add(" and XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" and XLXSBJDMX_XH>=:pSTARTXH ");
|
||
SQL.Add(" and c.XLXSCLQD_XLXHID =XLXSBJDMX_XLXHID and c.XLXSCLQD_XLGGID=XLXSBJDMX_XLGGID ) ");
|
||
SQL.Add(" when matched then update set XLXSBJDMX_XLBZID =c.XLXSCLQD_XLBZID ");
|
||
SQL.Add(" , XLXSBJDMX_DYDJID=c.XLXSCLQD_DYDJID ");
|
||
SQL.ParamByName("pBJDID").Value = sBJDID;
|
||
SQL.ParamByName("pSTARTXH").Value = startBJDXH;
|
||
dberp.ExecSQL(SQL);
|
||
#endregion
|
||
|
||
#region 根据型号、规格、标准，匹配唯一电压等级（材料清单-无条件）
|
||
SQL.Clear();
|
||
SQL.Add(" merge into XLXSBJDMX tag ");
|
||
SQL.Add(" using ");
|
||
SQL.Add(" ( ");
|
||
SQL.Add(" select XLXSCLQD_XLXHID,XLXSCLQD_XLGGID,XLXSCLQD_XLBZID,XLXSCLQD_DYDJID FROM XLXSCLQD a ");
|
||
SQL.Add(" where 1 = 1 and XLXSCLQD_WPID = 'ZZZZ' ");
|
||
SQL.Add(" and exists ( ");
|
||
SQL.Add(" SELECT XLXSCLQD_XLXHID,XLXSCLQD_XLGGID,XLXSCLQD_XLBZID FROM XLXSCLQD b ");
|
||
SQL.Add(" WHERE 1 = 1 and a.XLXSCLQD_XLXHID = b.XLXSCLQD_XLXHID and a.XLXSCLQD_XLGGID = b.XLXSCLQD_XLGGID ");
|
||
SQL.Add(" and a.XLXSCLQD_XLBZID = b.XLXSCLQD_XLBZID");
|
||
SQL.Add(" and b.XLXSCLQD_WPID = 'ZZZZ' and exists( ");
|
||
SQL.Add(" select XLXSBJDMX_XLXHID, XLXSBJDMX_XLGGID,XLXSBJDMX_XLBZID from XLXSBJDMX WHERE XLXSBJDMX_XLXHID = XLXSCLQD_XLXHID ");
|
||
SQL.Add(" and XLXSBJDMX_XLGGID= XLXSCLQD_XLGGID ");
|
||
SQL.Add(" and XLXSBJDMX_XLBZID=XLXSCLQD_XLBZID");
|
||
SQL.Add(" and XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" ) ");
|
||
SQL.Add(" group by XLXSCLQD_XLXHID,XLXSCLQD_XLGGID,XLXSCLQD_XLBZID ");
|
||
SQL.Add(" having (count(XLXSCLQD_XLXHID) + count(XLXSCLQD_XLGGID) + count(XLXSCLQD_XLBZID)) = 3 ");
|
||
SQL.Add(" ) ");
|
||
SQL.Add(" ) c ");
|
||
SQL.Add(" on ( XLXSBJDMX_XLXHID <> ' ' and XLXSBJDMX_XLGGID <> ' ' ");
|
||
SQL.Add(" and XLXSBJDMX_XLBZID <> ' ' and XLXSBJDMX_DYDJID = ' ' ");
|
||
SQL.Add(" and XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" and XLXSBJDMX_XH>=:pSTARTXH ");
|
||
SQL.Add(" and c.XLXSCLQD_XLXHID =XLXSBJDMX_XLXHID and c.XLXSCLQD_XLGGID=XLXSBJDMX_XLGGID and c.XLXSCLQD_XLBZID=XLXSBJDMX_XLBZID ) ");
|
||
SQL.Add(" when matched then update set XLXSBJDMX_DYDJID =c.XLXSCLQD_DYDJID ");
|
||
SQL.ParamByName("pBJDID").Value = sBJDID;
|
||
SQL.ParamByName("pSTARTXH").Value = startBJDXH;
|
||
dberp.ExecSQL(SQL);
|
||
#endregion
|
||
|
||
#region 根据型号、规格、电压等级，匹配唯一标准（材料清单-无条件）
|
||
SQL.Clear();
|
||
SQL.Add(" merge into XLXSBJDMX tag ");
|
||
SQL.Add(" using ");
|
||
SQL.Add(" ( ");
|
||
SQL.Add(" select XLXSCLQD_XLXHID,XLXSCLQD_XLGGID,XLXSCLQD_XLBZID,XLXSCLQD_DYDJID FROM XLXSCLQD a ");
|
||
SQL.Add(" where 1 = 1 and XLXSCLQD_WPID = 'ZZZZ' ");
|
||
SQL.Add(" and exists ( ");
|
||
SQL.Add(" SELECT XLXSCLQD_XLXHID,XLXSCLQD_XLGGID,XLXSCLQD_DYDJID FROM XLXSCLQD b ");
|
||
SQL.Add(" WHERE 1 = 1 and a.XLXSCLQD_XLXHID = b.XLXSCLQD_XLXHID and a.XLXSCLQD_XLGGID = b.XLXSCLQD_XLGGID ");
|
||
SQL.Add(" and a.XLXSCLQD_DYDJID = b.XLXSCLQD_DYDJID ");
|
||
SQL.Add(" and b.XLXSCLQD_WPID = 'ZZZZ' and exists( ");
|
||
SQL.Add(" select XLXSBJDMX_XLXHID, XLXSBJDMX_XLGGID,XLXSCLQD_DYDJID from XLXSBJDMX WHERE XLXSBJDMX_XLXHID = XLXSCLQD_XLXHID ");
|
||
SQL.Add(" and XLXSBJDMX_XLGGID= XLXSCLQD_XLGGID");
|
||
SQL.Add(" and XLXSBJDMX_DYDJID=XLXSCLQD_DYDJID");
|
||
SQL.Add(" and XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" ) ");
|
||
SQL.Add(" group by XLXSCLQD_XLXHID,XLXSCLQD_XLGGID,XLXSCLQD_DYDJID ");
|
||
SQL.Add(" having (count(XLXSCLQD_XLXHID) + count(XLXSCLQD_XLGGID) + count(XLXSCLQD_DYDJID)) = 3 ");
|
||
SQL.Add(" ) ");
|
||
SQL.Add(" ) c ");
|
||
SQL.Add(" on ( XLXSBJDMX_XLXHID <> ' ' and XLXSBJDMX_XLGGID <> ' ' ");
|
||
SQL.Add(" and XLXSBJDMX_XLBZID = ' ' and XLXSBJDMX_DYDJID <> ' ' ");
|
||
SQL.Add(" and XLXSBJDMX_BJDID=:pBJDID ");
|
||
SQL.Add(" and XLXSBJDMX_XH>=:pSTARTXH ");
|
||
SQL.Add(" and c.XLXSCLQD_XLXHID =XLXSBJDMX_XLXHID and c.XLXSCLQD_XLGGID=XLXSBJDMX_XLGGID and c.XLXSCLQD_DYDJID=XLXSBJDMX_DYDJID ) ");
|
||
SQL.Add(" when matched then update set XLXSBJDMX_XLBZID =c.XLXSCLQD_XLBZID ");
|
||
SQL.ParamByName("pBJDID").Value = sBJDID;
|
||
SQL.ParamByName("pSTARTXH").Value = startBJDXH;
|
||
dberp.ExecSQL(SQL);
|
||
#endregion
|
||
}
|
||
|
||
#endregion
|
||
}
|
||
}
|