291 lines
11 KiB
C#
291 lines
11 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 System.Collections.Generic;
|
|
|
|
namespace GThinking.N_XLXSBJ
|
|
{
|
|
public partial class CLYL_DRYLInfo : SingleCardBase
|
|
{
|
|
CLYL_DRYL2 entity = new CLYL_DRYL2();
|
|
|
|
protected void Page_Load(object sender, EventArgs e)
|
|
{
|
|
this.UMID = 100860014;
|
|
this.UMName = "线缆销售报价材料用量";
|
|
base.CurMaster = Master;
|
|
base.CurEntity = entity;
|
|
this.PageHeight = 180;
|
|
this.PageWidth = 420;
|
|
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;
|
|
fupdPost.FileSaveType = gtAsyncFileUpload.DFileSaveType.SaveToPath;
|
|
fupdPost.Filter = "XLS,XLSX";
|
|
|
|
if (!IsPostBack)
|
|
{
|
|
TComboBox.SetComboBox("DBERP", cbxGCID, "select GC_GCID,GC_GCMC from GC where 1=1 and ISNULL(GC_TYBZ,' ')<>'Y' order by GC_GCID ", false);
|
|
if (cbxGCID.ItemsCount == 1)
|
|
cbxGCID.SelectedIndex = 0;
|
|
}
|
|
base.InitPage();
|
|
}
|
|
|
|
#region 控件事件
|
|
|
|
protected void btnQD_Click(object sender, EventArgs e)
|
|
{
|
|
if (cbxGCID.SelectedValue.Trim().Equals(""))
|
|
{
|
|
ShowMessage("请选择工厂！");
|
|
return;
|
|
}
|
|
if (fupdPost.FilePath.Equals(""))
|
|
{
|
|
ShowMessage("请选择需要导入的文件！");
|
|
return;
|
|
}
|
|
|
|
string sAnswer = "";
|
|
Confirm.Register("IsQD", "是否需要用最新材料物料用量更新各线缆产品的材料用量？");
|
|
if (Confirm.GetValue("IsQD").Trim().Equals(""))
|
|
{
|
|
Confirm.Ask("IsQD");
|
|
return;
|
|
}
|
|
else if (Confirm.GetValue("IsQD").Trim().Equals("N"))
|
|
{
|
|
//表示进行了提示并选择了取消，直接返回或其他处理
|
|
Confirm.Clear();
|
|
sAnswer = "N";
|
|
}
|
|
else if (Confirm.GetValue("IsQD").Trim().Equals("Y"))
|
|
{
|
|
//表示进行了提示并选择了确认
|
|
Confirm.Clear();
|
|
sAnswer = "Y";
|
|
}
|
|
|
|
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
|
TSQL sSQL = new TSQL(dberp);
|
|
try
|
|
{
|
|
lblTitle.Text = "正在导入数据，请稍后...";
|
|
dberp.BeginTrans();
|
|
DataSet aDataExcel = new DataSet();
|
|
ReadWJ(fupdPost.FilePath, ref aDataExcel);
|
|
ExcelColumnValid(aDataExcel);
|
|
InsertXLXSCLWLYL(dberp, sSQL, aDataExcel);
|
|
if (sAnswer.Equals("Y"))
|
|
{
|
|
UpdateXLXSCLQD(dberp, sSQL);
|
|
}
|
|
dberp.CommitTrans();
|
|
if (sAnswer.Equals("Y"))
|
|
ShowMessage("完成材料物料用量导入，并同步更新了各线缆产品的材料用量！");
|
|
else
|
|
ShowMessage("完成材料物料用量导入！");
|
|
CloseWindow();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
lblTitle.Text = "";
|
|
dberp.RollbackTrans();
|
|
ShowMessage(ex.Message);
|
|
}
|
|
}
|
|
|
|
protected void btnQX_Click(object sender, EventArgs e)
|
|
{
|
|
CloseWindow();
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region 自定义事件
|
|
|
|
private void UpdateXLXSCLQD(TDatabase dberp, TSQL sSQL)
|
|
{
|
|
sSQL.Clear();
|
|
sSQL.Add("update XLXSCLQD set ");
|
|
for (int i = 1; i < 80; i++)
|
|
{
|
|
sSQL.Add(string.Format("XLXSCLQD_SL{0}=SL{0},", i.ToString().PadLeft(2, '0')));
|
|
}
|
|
sSQL.Add("XLXSCLQD_SL80=SL80 ");
|
|
sSQL.Add(" from (");
|
|
|
|
sSQL.Add("select XLXSCLWLYL_CPWLID as WLID, ");
|
|
for (int i = 1; i < 80; i++)
|
|
{
|
|
sSQL.Add(string.Format("sum(case when XLXSBJCLWL_BJCLID='{0}' then XLXSCLWLYL_SL else 0.0 end) as SL{1}, ", "BJCL" + i.ToString().PadLeft(2, '0') + "0000", i.ToString().PadLeft(2, '0')));
|
|
}
|
|
sSQL.Add("sum(case when XLXSBJCLWL_BJCLID='BJCL800000' then XLXSCLWLYL_SL else 0.0 end) as SL80 ");
|
|
sSQL.Add(" from XLXSCLWLYL,XLXSBJCLWL ");
|
|
sSQL.Add(" where XLXSCLWLYL_GCID=:pGCID ");
|
|
sSQL.Add(" and XLXSCLWLYL_WLID=XLXSBJCLWL_WLID ");
|
|
sSQL.Add("group by XLXSCLWLYL_CPWLID");
|
|
sSQL.Add(") a ");
|
|
sSQL.Add("where XLXSCLQD_GCID=:pGCID and a.WLID=XLXSCLQD_WLID ");
|
|
sSQL.ParamByName("pGCID").Value = cbxGCID.SelectedValue.Trim();
|
|
dberp.ExecSQL(sSQL);
|
|
}
|
|
|
|
private void InsertXLXSCLWLYL(TDatabase dberp, TSQL sSQL, DataSet ds)
|
|
{
|
|
int KeyRun = 200;
|
|
int tmpRun = 1;//循环KeyRun次执行1次SQL
|
|
TSQL sqlDel = new TSQL(dberp);
|
|
string strDel = "";
|
|
string strIns = "";
|
|
string sWHR = tSession.UserName;
|
|
string sWHRID = tSession.UserId;
|
|
string sWHSJ = DateTime.Now.ToString();
|
|
|
|
foreach (DataRow Row in ds.Tables[0].Rows)
|
|
{
|
|
string sCPWLID = Row[0].ToString().Trim();
|
|
string sWLID = Row[2].ToString().Trim();
|
|
double fSL = CMISproConvert.StrToFloat(Row[4].ToString(), 0);
|
|
string sSL = "0";
|
|
if (fSL.ToString().ToUpper().Contains("E"))//科学记数法
|
|
{
|
|
sSL = fSL.ToString("0.000000");//显示6位小数
|
|
}
|
|
else
|
|
sSL = fSL.ToString();
|
|
|
|
|
|
if (!sCPWLID.Equals("") && !sWLID.Equals(""))
|
|
{
|
|
if (strDel.Trim().Equals(""))
|
|
strDel = string.Format("(XLXSCLWLYL_CPWLID='{0}' and XLXSCLWLYL_WLID='{1}')", sCPWLID, sWLID);
|
|
else
|
|
strDel += string.Format(" or (XLXSCLWLYL_CPWLID='{0}' and XLXSCLWLYL_WLID='{1}')", sCPWLID, sWLID);
|
|
|
|
if (strIns.Trim().Equals(""))
|
|
strIns = string.Format("select '{0}' as GC,'{1}' as CPWL,'{2}' as WL,{3} as SL,'{4}' as WHR,'{5}' as WHRID,'{6}' as WHSJ ",
|
|
cbxGCID.SelectedValue.Trim(), sCPWLID, sWLID, sSL, sWHR, sWHRID, sWHSJ);
|
|
else
|
|
{
|
|
strIns += "\r\n" + " union all " + "\r\n";
|
|
strIns += string.Format("select '{0}' as GC,'{1}' as CPWL,'{2}' as WL,{3} as SL,'{4}' as WHR,'{5}' as WHRID,'{6}' as WHSJ ",
|
|
cbxGCID.SelectedValue.Trim(), sCPWLID, sWLID, sSL, sWHR, sWHRID, sWHSJ);
|
|
}
|
|
}
|
|
|
|
if (tmpRun >= KeyRun && (tmpRun % KeyRun == 0))
|
|
{
|
|
if (!strDel.Trim().Equals(""))
|
|
{
|
|
sqlDel.Clear();
|
|
sqlDel.Add("delete from XLXSCLWLYL where XLXSCLWLYL_GCID=:pGCID ");
|
|
sqlDel.Add(" and (" + strDel + ")");
|
|
sqlDel.ParamByName("pGCID").Value = cbxGCID.SelectedValue.Trim();
|
|
dberp.ExecSQL(sqlDel);
|
|
strDel = "";
|
|
}
|
|
|
|
if (!strIns.Trim().Equals(""))
|
|
{
|
|
sSQL.Clear();
|
|
sSQL.Add("insert into XLXSCLWLYL(XLXSCLWLYL_GCID,XLXSCLWLYL_CPWLID,XLXSCLWLYL_WLID,XLXSCLWLYL_SL,XLXSCLWLYL_WHR,XLXSCLWLYL_WHRID,XLXSCLWLYL_WHSJ) ");
|
|
sSQL.Add(strIns);
|
|
dberp.ExecSQL(sSQL);
|
|
strIns = "";
|
|
}
|
|
}
|
|
|
|
tmpRun++;
|
|
}
|
|
|
|
if (!strDel.Trim().Equals(""))
|
|
{
|
|
sqlDel.Clear();
|
|
sqlDel.Add("delete from XLXSCLWLYL where XLXSCLWLYL_GCID=:pGCID ");
|
|
sqlDel.Add(" and (" + strDel + ")");
|
|
sqlDel.ParamByName("pGCID").Value = cbxGCID.SelectedValue.Trim();
|
|
dberp.ExecSQL(sqlDel);
|
|
strDel = "";
|
|
}
|
|
|
|
if (!strIns.Trim().Equals(""))
|
|
{
|
|
sSQL.Clear();
|
|
sSQL.Add("insert into XLXSCLWLYL(XLXSCLWLYL_GCID,XLXSCLWLYL_CPWLID,XLXSCLWLYL_WLID,XLXSCLWLYL_SL,XLXSCLWLYL_WHR,XLXSCLWLYL_WHRID,XLXSCLWLYL_WHSJ) ");
|
|
sSQL.Add(strIns);
|
|
dberp.ExecSQL(sSQL);
|
|
strIns = "";
|
|
}
|
|
}
|
|
|
|
private void ExcelColumnValid(DataSet ds)
|
|
{
|
|
Dictionary<string, string> ColumnNames = new Dictionary<string, string>();
|
|
ColumnNames.Add("0", "产品物料编码");
|
|
ColumnNames.Add("1", "产品物料信息");
|
|
ColumnNames.Add("2", "材料物料编码");
|
|
ColumnNames.Add("3", "材料物料信息");
|
|
ColumnNames.Add("4", "材料物料用量");
|
|
ColumnNames.Add("5", "报价材料");
|
|
|
|
for (int i = 0; i < 5; i++)
|
|
{
|
|
try
|
|
{
|
|
if (!ds.Tables[0].Columns[i].ColumnName.Trim().Equals(ColumnNames[i.ToString()].Trim()))
|
|
{
|
|
throw new Exception("导入文件的内容格式不正确，请按记录导出的excel格式调整导入文件！");
|
|
}
|
|
}
|
|
catch
|
|
{
|
|
throw new Exception("导入文件的内容格式不正确，请按记录导出的excel格式调整导入文件！");
|
|
}
|
|
}
|
|
}
|
|
|
|
private void ReadWJ(string sPath, ref DataSet aDataExcel)
|
|
{
|
|
string sMessage = "";
|
|
aDataExcel = TExcelData.ReadExcel(sPath.Trim(), "", "", false, ref sMessage);
|
|
if (!sMessage.Trim().Equals(""))
|
|
{
|
|
throw new Exception("文件读取失败！");
|
|
}
|
|
}
|
|
|
|
private string sGetCellTest(DataRow Row, int ColumnIndex)
|
|
{
|
|
return Row[ColumnIndex].ToString().Trim().Replace("\r\n", "").Replace("\n", "");
|
|
}
|
|
|
|
#endregion
|
|
}
|
|
}
|