protected void txtOrderNo_Blur(object sender, EventArgs e)
{
try
{
string orderno = txtOrderNo.Text;
if (orderno.Length == 12 && orderno.Substring(0, 2) == "RL" && CommFunction.IsNumeric(orderno.Substring(2, 10)))
{
}
else
{
Alert.Show("订单号格式不正确", string.Empty, txtOrderNo.GetFocusReference());
//PageContext.RegisterStartupScript(txtOrderNo.GetFocusReference(true));
}
}catch (Exception ee)
{
Alert.Show(ee.Message);
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
try
{
string OrderNo = txtOrderNo.Text.Trim();
if (OrderNo.Length == 12 && OrderNo.Substring(0, 2) == "RL" && CommFunction.IsNumeric(OrderNo.Substring(2, 10)))
{
}
else
{
Alert.Show("订单号格式不正确", string.Empty, txtOrderNo.GetFocusReference());
return;
}
int i = SaveItem();
if (i == 1)
{
Notify n = new Notify();
n.PositionX = Position.Center;
n.PositionY = Position.Top;
n.DisplayMilliseconds = 3000;
n.Message = "保存成功";
n.Show();
}
else if (i == 0)
{
Alert.Show("该订单编号已经存在请更改");
}
else
{
Alert.Show("保存失败");
}
//msglab.Text = "保存成功";
string scripts = String.Format("F.getActiveWindow().window.__doPostBack('','RefreshGrid1');");
PageContext.RegisterStartupScript(scripts);
}
catch (Exception ee)
{
Alert.Show("保存失败:" + ee.Message);
}
}
protected void btnSaveClose_Click(object sender, EventArgs e)
{
try
{
string OrderNo = txtOrderNo.Text.Trim();
if (OrderNo.Length == 12 && OrderNo.Substring(0, 2) == "RL" && CommFunction.IsNumeric(OrderNo.Substring(2, 10)))
{
}
else
{
Alert.Show("订单号格式不正确", string.Empty, txtOrderNo.GetFocusReference());
return;
}
int i = SaveItem();
if (i == 1)
{
string scripts = String.Format("F.getActiveWindow().window.__doPostBack('','RefreshGrid1');");
PageContext.RegisterStartupScript(scripts + Alert.GetShowInTopReference("保存成功") + ActiveWindow.GetHideReference());
}
else if (i == 0)
{
Alert.Show("该订单编号已经存在请更改");
}
else
{
Alert.Show("保存失败");
}
}
catch (Exception ee)
{
Alert.Show("保存失败:" + ee.Message);
}
finally
{
}
}
private void readExcel(string filename)
{
string fsn = "";
try
{
string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/OrderFile/" + filename) + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"";
ArrayList al = new ArrayList();
using (OleDbConnection conn = new OleDbConnection(connstring))
{
conn.Open();
DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字
//for(int k=0;k<sheetsName.Rows.Count;k++)
//{
// log.Info(sheetsName.Rows[k][2].ToString());
//}
string firstSheetName = sheetsName.Rows[0][2].ToString(); //得到第一个sheet的名字
string sql = string.Format("SELECT * FROM [{0}]", firstSheetName); //查询字符串
OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
DataSet set = new DataSet();
ada.Fill(set);
DataTable dt = set.Tables[0];
int i = 0;
SQLHelper.DbHelperSQL.SetConnectionString("");
string OrderNo = "", LotNo = "", ClientCode = "", ClientOrderNo = "", RecOrderPersonID = "", RecOrderPerson = "", RecOrderDate = "", SendOrderDate = "", OutGoodsDate = "", checkman = "", CheckDate = "", ContainerType = "", ischeck = "", OrderType = "";
//产品名称、产品编号、版本、瑞麟编号、客户编号、客户代号、日期
//获取表头 添加orderheader
if (dt != null && dt.Rows.Count >= 6)
{
OrderNo = dt.Rows[1][2].ToString();
if (OrderNo.Length == 12 && OrderNo.Substring(0, 2) == "RL" && CommFunction.IsNumeric(OrderNo.Substring(2, 10)))
{
}
else
{
Alert.Show("订单号格式不正确,禁止导入");
return;
}
OrderType = dt.Rows[1][5].ToString();
LotNo = dt.Rows[1][8].ToString();
ClientCode = dt.Rows[1][11].ToString();
ClientOrderNo = dt.Rows[2][2].ToString();
RecOrderPerson = dt.Rows[3][2].ToString();
checkman = dt.Rows[3][11].ToString();
ContainerType = dt.Rows[3][8].ToString();
RecOrderDate = dt.Rows[4][2].ToString();
CheckDate = dt.Rows[4][8].ToString();
OutGoodsDate = dt.Rows[4][11].ToString();
if (CheckDate != "" || checkman != "")
{
ischeck = "1";
}
else
{
ischeck = "0";
}
sql = "select sn from OrderHeader where OrderNo='" + OrderNo + "'";
log.Info(sql);
DataTable dtitem = SQLHelper.DbHelperSQL.ReturnDataTable(sql, 30);
if (dtitem == null || dtitem.Rows.Count == 0)
{
//sql = "insert into allitem(itemno,name) values('" + dt.Rows[2][4].ToString() + "','" + dt.Rows[2][2].ToString() + "')";
//log.Info("sqlallitem::::" + sql);
//al.Add(sql);
sql = "insert into OrderHeader(OrderNo,LotNo,ClientCode,ClientOrderNo,RecOrderPerson,RecOrderDate,SendOrderDate,OutGoodsDate,Inputer,InputerDate,IsCheck,OrderExcel,ContainerType,Checker,CheckDate,OrderType) values('" + OrderNo + "','" + LotNo + "','" + ClientCode + "','" + ClientOrderNo + "','" + RecOrderPerson + "','" + RecOrderDate.Replace(".", "-") + "','" + SendOrderDate.Replace(".", "-") + "','" + OutGoodsDate.Replace(".", "-") + "','" + User.Identity.Name + "',getdate()," + ischeck + ",'" + filename + "','" + ContainerType + "','" + checkman + "','" + CheckDate + "','" + OrderType + "')";
log.Info("sqlbase::::" + sql);
al.Add(sql);
//SQLHelper.DbHelperSQL.ExecuteSql(sql, 30);
}
else
{
Alert.Show("订单号已存在");
return;
//产品名称、产品编号、版本、瑞麟编号、客户编号、客户代号、日期
//sql = "update OrderHeader set LotNo='" + LotNo + "',ClientCode='" + ClientCode + "',ClientOrderNo='" + ClientOrderNo + "',RecOrderPerson='" + RecOrderPerson + "',RecOrderDate='" + RecOrderDate.Replace(".", "-") + "',SendOrderDate='" + SendOrderDate.Replace(".", "-") + "',OutGoodsDate='" + OutGoodsDate.Replace(".", "-") + "',Updater='" + User.Identity.Name + "',UpdateDate=getdate(),OrderExcel='" + filename + "',ContainerType='" + ContainerType + "',Checker='" + checkman + "',CheckDate='" + CheckDate + "',IsCheck="+ischeck+",OrderType='"+OrderType+"' where OrderNo='" + OrderNo + "'";
//log.Info("sqlbase::::" + sql);
//SQLHelper.DbHelperSQL.ExecuteSql(sql, 30);
}
//sql = "select max(sn) from OrderHeader where OrderNo='" + OrderNo + "'";
SQLHelper.DbHelperSQL.SetConnectionString("");
fsn = "select max(sn) from OrderHeader where OrderNo='" + OrderNo + "'";
#region orderdtl add
for (i = 6; i < dt.Rows.Count; i++)
{
if (dt.Rows[i][0].ToString() == "" || dt.Rows[i][1].ToString() == "")
{
break;
}
else
{
#region orderdtl add
if (dt.Rows[i][2].ToString() != "配件")
{
sql = "select count(*) from rlitems where itemno='" + dt.Rows[i][2].ToString() + "'";
if (int.Parse(SQLHelper.DbHelperSQL.GetSingle(sql, 30)) > 0)
{
//sql = "update rlitems set surfacedeal='" + dt.Rows[i][4].ToString() + "' where itemno='" + dt.Rows[i][2].ToString() + "'";
//al.Add(sql);
//FileOper.writeLog(sql);
}
else
{
sql = "insert into rlitems(itemno,itemname,surfacedeal,Unit,sclass) values('" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][6].ToString() + "','成品类')";
al.Add(sql);
FileOper.writeLog(sql);
}
}
else
{
sql = "insert into rlitems(itemno,itemname,surfacedeal,Unit,sclass) values('" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][6].ToString() + "','配件类')";
al.Add(sql);
FileOper.writeLog(sql);
}
sql = "insert into OrderDetail(FSN,OrderNo,ClinetNo,ItemNo,ItemName,Quantity,Demand1,Demand2,Remark,Inputer,InputerDate,Color,Unit,IsNew,IsPackingmaterials,CountryPackVer,IsChange) values((" + fsn + "),'" + OrderNo + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][1].ToString() + "'," + dt.Rows[i][5].ToString() + ",'" + dt.Rows[i][11].ToString() + "','" + dt.Rows[i][12].ToString() + "','','" + User.Identity.Name + "',getdate(),'" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "')";
FileOper.writeLog(sql);
al.Add(sql);
//料号，名称，规格，材质，表面处理或颜色，底数，类别
//ItemNo,Name,Spec,MaterialNo,ItemColor,AddReserve1,ClassName
//sql = "select top 1 * from OrderDetail where itemno='" + dt.Rows[i][2].ToString() + "' and OrderNo='" + OrderNo + "' ";
//dtitem = SQLHelper.DbHelperSQL.ReturnDataTable(sql, 30);
//if (dtitem == null || dtitem.Rows.Count == 0)
//{
// //log.Info("sqlallitem::::" + sql);
// //SQLHelper.DbHelperSQL.ExecuteSql(sql, 30);
// //bomsn,物料sn，料号，名称，规格，材质，表面处理，用量，分类
// sql = "insert into OrderDetail(FSN,OrderNo,ClinetNo,ItemNo,ItemName,Quantity,Demand1,Demand2,Remark,Inputer,InputerDate,Color,Unit,IsNew,IsPackingmaterials,CountryPackVer,IsChange) values((" + fsn + "),'" + OrderNo + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][1].ToString() + "'," + dt.Rows[i][5].ToString() + ",'" + dt.Rows[i][11].ToString() + "','" + dt.Rows[i][12].ToString() + "','','" + User.Identity.Name + "',getdate(),'" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "')";
// log.Info("sqldtl::::" + sql);
// al.Add(sql);
// //SQLHelper.DbHelperSQL.ExecuteSql(sql, 30);
//}
//else
//{
// sql = "update OrderDetail set ClinetNo='" + dt.Rows[i][3].ToString() + "',Quantity=" + dt.Rows[i][5].ToString() + ",Demand1='" + dt.Rows[i][11].ToString() + "',Demand2='" + dt.Rows[i][12].ToString() + "',Updater='" + User.Identity.Name + "',UpdateDate=getdate(),Color='" + dt.Rows[i][4].ToString() + "',Unit='" + dt.Rows[i][6].ToString() + "',IsNew='" + dt.Rows[i][7].ToString() + "',IsPackingmaterials='" + dt.Rows[i][8].ToString() + "',CountryPackVer='" + dt.Rows[i][9].ToString() + "',IsChange='" + dt.Rows[i][10].ToString() + "' where itemno='" + dt.Rows[i][1].ToString() + "' and ItemName='" + dt.Rows[i][2].ToString() + "' and OrderNo='" + OrderNo + "' ";
// log.Info("sqldtl::::" + sql);
// al.Add(sql);
// //SQLHelper.DbHelperSQL.ExecuteSql(sql, 30);
//}
#endregion
}
}
#endregion
}
else
{
Alert.Show("没有要导入的数据");
}
}
if (al.Count > 0 && SQLHelper.DbHelperSQL.ExecuteSqlTran(al))
{
Alert.Show("导入成功");
}
else
{
Alert.Show("导入失败");
}
}
catch (Exception ee)
{
Alert.Show("导入失败");
log.Info(ee.ToString());
}
finally
{
BindGrid();
}
}
private void readExcel(string filename)
{
string bomsn = "";
try
{
string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/BOMFile/" + filename) + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"";
using (OleDbConnection conn = new OleDbConnection(connstring))
{
conn.Open();
DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字
//for(int k=0;k<sheetsName.Rows.Count;k++)
//{
// log.Info(sheetsName.Rows[k][2].ToString());
//}
string firstSheetName = sheetsName.Rows[0][2].ToString(); //得到第一个sheet的名字
string sql = string.Format("SELECT * FROM [{0}]", firstSheetName); //查询字符串
OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
DataSet set = new DataSet();
ada.Fill(set);
DataTable dt = set.Tables[0];
//for(int kk = 0; kk < dt.Columns.Count; kk++)
//{
// log.Info(dt.Columns[kk].ColumnName);
//}
#region 判断版本号是否为数字和是否版本已存在
if (!CommFunction.IsNumeric(dt.Rows[3][9].ToString()))
{
Alert.Show("版本号必须为数字");
return;
}
SQLHelper.DbHelperSQL.SetConnectionString("");
sql = "select count(*) from probomheader where ProNo = '" + dt.Rows[3][2].ToString() + "' and Ver = '" + dt.Rows[3][7].ToString() + "'";
if (int.Parse(SQLHelper.DbHelperSQL.GetSingle(sql, 30)) > 0)
{
Alert.Show("该产品已经存在相同版本的工程BOM，请修改版本再上传！", MessageBoxIcon.Error);
return;
}
#endregion
#region 判断料号的合法性
DataTable errdt = new DataTable();
errdt.Columns.Add("Seq", typeof(string)); //数据类型为 文本
errdt.Columns.Add("ItemNo", typeof(string)); //数据类型为 文本
errdt.Columns.Add("ItemName", typeof(string)); //数据类型为 文本
errdt.Columns.Add("Spec", typeof(string)); //数据类型为 文本
errdt.Columns.Add("Material", typeof(string)); //数据类型为 文本
errdt.Columns.Add("SurfaceDeal", typeof(string)); //数据类型为 文本
DataTable errwldt = new DataTable();
errwldt.Columns.Add("Seq", typeof(string)); //数据类型为 文本
errwldt.Columns.Add("ItemNo", typeof(string)); //数据类型为 文本
errwldt.Columns.Add("ItemName", typeof(string)); //数据类型为 文本
errwldt.Columns.Add("Spec", typeof(string)); //数据类型为 文本
errwldt.Columns.Add("Material", typeof(string)); //数据类型为 文本
errwldt.Columns.Add("SurfaceDeal", typeof(string)); //数据类型为 文本
errwldt.Columns.Add("wlFrom", typeof(string)); //数据类型为 文本
int i = 5; int k = 0;
Regex reg = new Regex(@"^[A-Z]{2}[\d]{4}$");
bool err = false;
string seq = "";
for (; i < dt.Rows.Count; i++)
{
if (dt.Rows[i][0].ToString() == "锐 麟 铝 制 品 有 限 公 司" || dt.Rows[i][0].ToString() == "物料清单(BOM)" || dt.Rows[i][0].ToString() == "产品名称" || dt.Rows[i][0].ToString() == "产品编号" || dt.Rows[i][0].ToString() == "序号")
{
continue;
}
else
{
#region 判断料号的合法性
if (dt.Rows[i][0].ToString() == "" && dt.Rows[i][1].ToString() == "" && dt.Rows[i][2].ToString() == "" && dt.Rows[i][3].ToString() == "" && dt.Rows[i][4].ToString() == "" && dt.Rows[i][5].ToString() == "" && dt.Rows[i][6].ToString() == "" && dt.Rows[i][7].ToString() == "" && dt.Rows[i][8].ToString() == "" && dt.Rows[i][9].ToString() == "" && dt.Rows[i][10].ToString() == "" && dt.Rows[i][11].ToString() == "")
{
break;
}
else if (dt.Rows[i][0].ToString() != "" && dt.Rows[i][1].ToString() == "" && dt.Rows[i][2].ToString() == "" && dt.Rows[i][3].ToString() == "" && dt.Rows[i][4].ToString() == "" && dt.Rows[i][5].ToString() == "" && dt.Rows[i][6].ToString() == "" && dt.Rows[i][7].ToString() == "" && dt.Rows[i][8].ToString() == "" && dt.Rows[i][9].ToString() == "" && dt.Rows[i][10].ToString() == "" && dt.Rows[i][11].ToString() == "")
{
continue;
}
else
{
seq = dt.Rows[i][0].ToString();
string lastseq = dt.Rows[i - 1][0].ToString();
if (lastseq.IndexOf(".") != -1)
{
lastseq = lastseq.Substring(0, lastseq.LastIndexOf("."));
}
#region 料号和序号不规范的物料
string tstr = dt.Rows[i][1].ToString().Replace(" ", "");
if (tstr.Length >= 6)
{
if (CommFunction.IsNumeric(tstr) && tstr.Length == 7)
{
#region 序号问题
if (seq.IndexOf(".") != -1 && seq.Substring(0, seq.LastIndexOf(".")) != lastseq && dt.Select("F1='" + seq.Substring(0, seq.LastIndexOf(".")) + "'").Count() == 0)
{
//如果已经存在错误列表中 不在重复添加
if (errdt.Select("Seq='" + dt.Rows[i - 1][0].ToString() + "'").Count() > 0)
{
continue;
}
log.Info("seq::::" + seq.Substring(0, seq.LastIndexOf(".")) + ":::lastseq:::" + lastseq + ":::::" + dt.Select("F1='" + seq.Substring(0, seq.LastIndexOf(".")) + "'").Count().ToString() + "::::::" + "F1='" + seq.Substring(0, seq.LastIndexOf(".")) + "'");
err = true;
DataRow dr = errdt.NewRow();
dr[0] = dt.Rows[i - 1][0].ToString() + "(上一条)"; //通过索引赋值
dr[1] = dt.Rows[i - 1][1].ToString();
dr[2] = dt.Rows[i - 1][2].ToString(); //+ reg.Match(tstr).Success.ToString()
dr[3] = dt.Rows[i - 1][3].ToString();
dr[4] = dt.Rows[i - 1][4].ToString();
dr[5] = dt.Rows[i - 1][5].ToString();
errdt.Rows.InsertAt(dr, k);
k++;
dr = errdt.NewRow();
dr[0] = dt.Rows[i][0].ToString(); //通过索引赋值
dr[1] = dt.Rows[i][1].ToString();
dr[2] = dt.Rows[i][2].ToString(); //+ reg.Match(tstr).Success.ToString()
dr[3] = dt.Rows[i][3].ToString();
dr[4] = dt.Rows[i][4].ToString();
dr[5] = dt.Rows[i][5].ToString();
errdt.Rows.InsertAt(dr, k);
k++;
continue;
}
#endregion
}
else
{
#region 序号问题
if (seq.IndexOf(".") != -1 && seq.Substring(0, seq.LastIndexOf(".")) != lastseq && dt.Select("F1='" + seq.Substring(0, seq.LastIndexOf(".")) + "'").Count() == 0)
{
//如果已经存在错误列表中 不在重复添加
if (errdt.Select("Seq='" + dt.Rows[i - 1][0].ToString() + "'").Count() > 0)
{
continue;
}
//&& dt.Select("F1='" + seq.Substring(0, seq.LastIndexOf(".")) + "'").Count() > 0
log.Info("seq::::" + seq.Substring(0, seq.LastIndexOf(".")) + ":::lastseq:::" + lastseq + ":::::" + dt.Select("F1='" + seq.Substring(0, seq.LastIndexOf(".")) + "'").Count().ToString() + "::::::" + "F1='" + seq.Substring(0, seq.LastIndexOf(".")) + "'");
err = true;
DataRow dr = errdt.NewRow();
dr[0] = dt.Rows[i - 1][0].ToString() + "(上一条)"; //通过索引赋值
dr[1] = dt.Rows[i - 1][1].ToString();
dr[2] = dt.Rows[i - 1][2].ToString(); //+ reg.Match(tstr).Success.ToString()
dr[3] = dt.Rows[i - 1][3].ToString();
dr[4] = dt.Rows[i - 1][4].ToString();
dr[5] = dt.Rows[i - 1][5].ToString();
errdt.Rows.InsertAt(dr, k);
k++;
dr = errdt.NewRow();
dr[0] = dt.Rows[i][0].ToString(); //通过索引赋值
dr[1] = dt.Rows[i][1].ToString();
dr[2] = dt.Rows[i][2].ToString(); //+ reg.Match(tstr).Success.ToString()
dr[3] = dt.Rows[i][3].ToString();
dr[4] = dt.Rows[i][4].ToString();
dr[5] = dt.Rows[i][5].ToString();
errdt.Rows.InsertAt(dr, k);
k++;
continue;
}
#endregion
tstr = tstr.Substring(0, 6);
#region 料号问题
if (!reg.Match(tstr).Success)
{
err = true;
DataRow dr = errdt.NewRow();
dr[0] = dt.Rows[i][0].ToString(); //通过索引赋值
dr[1] = dt.Rows[i][1].ToString();
dr[2] = dt.Rows[i][2].ToString(); //+ reg.Match(tstr).Success.ToString()
dr[3] = dt.Rows[i][3].ToString();
dr[4] = dt.Rows[i][4].ToString();
dr[5] = dt.Rows[i][5].ToString();
errdt.Rows.InsertAt(dr, k);
k++;
continue;
}
#endregion
}
}
else
{
err = true;
DataRow dr = errdt.NewRow();
dr[0] = dt.Rows[i][0].ToString(); //通过索引赋值
dr[1] = dt.Rows[i][1].ToString();
dr[2] = dt.Rows[i][2].ToString(); //+ reg.Match(tstr).Success.ToString()
dr[3] = dt.Rows[i][3].ToString();
dr[4] = dt.Rows[i][4].ToString();
dr[5] = dt.Rows[i][5].ToString();
errdt.Rows.InsertAt(dr, k);
k++;
continue;
}
#endregion
#region 一物多码
sql = "select sn,ItemNo,ItemName,Spec,Material,SurfaceDeal from RLItems where ItemNo='" + dt.Rows[i][1].ToString() + "' and ( ItemName!='" + dt.Rows[i][2].ToString() + "' or Spec!='" + dt.Rows[i][3].ToString() + "' or Material!='" + dt.Rows[i][4].ToString() + "' or SurfaceDeal!='" + dt.Rows[i][5].ToString() + "')";
log.Info("sql 一物多码::" + sql);
DataTable dtwl = SQLHelper.DbHelperSQL.ReturnDataTable(sql, 30);
if (dtwl != null && dtwl.Rows.Count > 0)
{
err = true;
DataRow dr = errwldt.NewRow();
dr[0] = dt.Rows[i][0].ToString(); //通过索引赋值
dr[1] = dt.Rows[i][1].ToString();
dr[2] = dt.Rows[i][2].ToString(); //+ reg.Match(tstr).Success.ToString()
dr[3] = dt.Rows[i][3].ToString();
dr[4] = dt.Rows[i][4].ToString();
dr[5] = dt.Rows[i][5].ToString();
dr[6] = "导入";
errwldt.Rows.InsertAt(dr, k);
k++;
DataRow dr2 = errwldt.NewRow();
dr2[0] = dtwl.Rows[0][0].ToString(); //通过索引赋值
dr2[1] = dtwl.Rows[0][1].ToString();
dr2[2] = dtwl.Rows[0][2].ToString(); //+ reg.Match(tstr).Success.ToString()
dr2[3] = dtwl.Rows[0][3].ToString();
dr2[4] = dtwl.Rows[0][4].ToString();
dr2[5] = dtwl.Rows[0][5].ToString();
dr2[6] = "物料表";
errwldt.Rows.InsertAt(dr2, k);
k++;
}
else
{
//物料不存在
DataRow dr = errwldt.NewRow();
dr[0] = dt.Rows[i][0].ToString(); //通过索引赋值
dr[1] = dt.Rows[i][1].ToString();
dr[2] = dt.Rows[i][2].ToString(); //+ reg.Match(tstr).Success.ToString()
dr[3] = dt.Rows[i][3].ToString();
dr[4] = dt.Rows[i][4].ToString();
dr[5] = dt.Rows[i][5].ToString();
dr[6] = "物料不存在";
errwldt.Rows.InsertAt(dr, k);
k++;
}
#endregion
}
#endregion
}
}
if (err)
{
Session["errdt"] = errdt;
Session["errwldt"] = errwldt;
//str = str.TrimEnd(new char[] { ',' });
//Alert.Show(str);
PageContext.RegisterStartupScript(Window1.GetShowReference("showerr.aspx", "有问题的物料", Unit.Parse("900"), Unit.Parse("800")));
return;
}
#endregion
string sqlbase = "", sqldtl = "", sclass = "", ProName = "", ProNo = "", Ver = "", ClientCode = "", BomDate = "", ClientProNo = "", FileNo = "", Color = "";//产品名称、产品编号、版本、客户代号、日期、客户产品型号
ArrayList al = new ArrayList();
//获取表头 添加bombase
if (dt != null && dt.Rows.Count >= 4)
{
seq = "";
ProName = dt.Rows[2][2].ToString();
ProNo = dt.Rows[3][2].ToString();
Color = dt.Rows[3][4].ToString();
Ver = dt.Rows[3][7].ToString();
//ClientCode = dt.Rows[2][9].ToString();
BomDate = dt.Rows[3][10].ToString().Replace(".", "-");
//ClientProNo = dt.Rows[3][5].ToString();
FileNo = dt.Rows[2][7].ToString();
//料号，名称，规格，材质，表面处理或颜色，底数，类别
//ItemNo,Name,Spec,MaterialNo,ItemColor,AddReserve1,ClassName
sql = "select top 1 * from rlitems where itemno='" + ProNo + "' ";// or name='" + dt.Rows[2][2].ToString() + "'
DataTable dtitem = SQLHelper.DbHelperSQL.ReturnDataTable(sql, 30);
if (dtitem == null || dtitem.Rows.Count == 0)
{
sql = "insert into rlitems(itemno,itemname) values('" + ProNo + "','" + ProName + "')";
log.Info("sqlallitem::::" + sql);
al.Add(sql);
//产品名称、产品编号、版本、瑞麟编号、客户编号、客户代号、日期
sqlbase = "insert into ProBomHeader(ProName,ProNo,Ver,ClientProNo,ClientCode,BomDate,AllitemSN,Inputer,InputeDate,BomExcel,FileNo,Color) values('" + ProName + "','" + ProNo + "','" + Ver + "','" + ClientProNo + "','" + ClientCode + "','" + BomDate + "',(select sn from rlitems where itemno='" + ProNo + "'),'" + User.Identity.Name + "',getdate(),'" + filename + "','" + FileNo + "','" + Color + "')";
log.Info("sqlbase::::" + sqlbase);
al.Add(sqlbase);
SQLHelper.DbHelperSQL.ExecuteSqlTran(al);
}
else
{
//产品名称、产品编号、版本、瑞麟编号、客户编号、客户代号、日期
sqlbase = "insert into ProBomHeader(ProName,ProNo,Ver,ClientProNo,ClientCode,BomDate,AllitemSN,Inputer,InputeDate,BomExcel,FileNo,Color) values('" + ProName + "','" + ProNo + "','" + Ver + "','" + ClientProNo + "','" + ClientCode + "','" + BomDate + "'," + dtitem.Rows[0]["sn"].ToString() + ",'" + User.Identity.Name + "',getdate(),'" + filename + "','" + FileNo + "','" + Color + "')";
log.Info("sqlbase::::" + sqlbase);
SQLHelper.DbHelperSQL.ExecuteSql(sqlbase, 30);
}
//查询新增bom的sn
sql = "select max(sn) from ProBomHeader where ver='" + Ver + "' and prono='" + ProNo + "'";
log.Info(sql);
SQLHelper.DbHelperSQL.SetConnectionString("");
bomsn = SQLHelper.DbHelperSQL.GetSingle(sql).ToString();
log.Info("bombasesn:::" + sql);
al.Clear();
#region bomdtl add
string proquantity = "0", basenum = "0";
for (i = 5; i < dt.Rows.Count; i++)
{
#region 判断数量和底数是否为空
proquantity = dt.Rows[i][6].ToString();
if (!string.IsNullOrEmpty(proquantity))
{
if (proquantity.IndexOf("/") != -1)
{
proquantity = (float.Parse(proquantity.Substring(0, proquantity.IndexOf("/"))) / float.Parse(proquantity.Substring(proquantity.IndexOf("/") + 1))).ToString();
}
}
else
{
proquantity = "0";
}
basenum = dt.Rows[i][7].ToString();
if (string.IsNullOrEmpty(basenum))
{
basenum = "0";
}
#endregion
if (dt.Rows[i][0].ToString() == "锐 麟 铝 制 品 有 限 公 司" || dt.Rows[i][0].ToString() == "物料清单(BOM)" || dt.Rows[i][0].ToString() == "产品名称" || dt.Rows[i][0].ToString() == "锐麟编号" || dt.Rows[i][0].ToString() == "序号")
{
sclass = "";
continue;
}
else
{
#region
if (dt.Rows[i][0].ToString() == "" && dt.Rows[i][1].ToString() == "" && dt.Rows[i][2].ToString() == "" && dt.Rows[i][3].ToString() == "" && dt.Rows[i][4].ToString() == "" && dt.Rows[i][5].ToString() == "" && dt.Rows[i][6].ToString() == "" && dt.Rows[i][7].ToString() == "" && dt.Rows[i][8].ToString() == "" && dt.Rows[i][9].ToString() == "")
{
break;
}
else if (dt.Rows[i][0].ToString() != "" && dt.Rows[i][1].ToString() == "" && dt.Rows[i][2].ToString() == "" && dt.Rows[i][3].ToString() == "" && dt.Rows[i][4].ToString() == "" && dt.Rows[i][5].ToString() == "" && dt.Rows[i][6].ToString() == "" && dt.Rows[i][7].ToString() == "" && dt.Rows[i][8].ToString() == "" && dt.Rows[i][9].ToString() == "")
{
sclass = dt.Rows[i][0].ToString();
}
else
{
#region bomdtl add
//料号，名称，规格，材质，表面处理或颜色，底数，类别
//ItemNo,Name,Spec,MaterialNo,ItemColor,AddReserve1,ClassName
sql = "select top 1 * from rlitems where itemno='" + dt.Rows[i][1].ToString() + "' ";//or name='" + dt.Rows[i][2].ToString() + "'
dtitem = SQLHelper.DbHelperSQL.ReturnDataTable(sql, 30);
seq = dt.Rows[i][0].ToString();
int num = Regex.Matches(seq, ".").Count;
if (dtitem == null || dtitem.Rows.Count == 0)
{
#region
sql = "insert into rlitems(itemno,itemname,Spec,Material,SurfaceDeal,ProUsingQuantity,BaseNum,WorkShop,MainFrom,Sclass,StoreHouse,zongcheng) values('" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "'," + proquantity + "," + basenum + ",'" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "','" + dt.Rows[i][11].ToString() + "','" + sclass + "')";
log.Info("sqlallitem::::" + sql);
SQLHelper.DbHelperSQL.ExecuteSql(sql, 30);
if (seq.IndexOf(".") != -1)
{
sqldtl = "insert into ProBomDetail(FSN,AllitemSN,ItemNo,ItemName,Spec,Material,SurfaceDeal,ProUsingQuantity,ZongCheng,Inputer,InputeDate,seq,parentsn,ZuHe,BaseNum,WorkShop,MainFrom,Sclass,StoreHouse) values(" + bomsn + ",(select sn from rlitems where itemno='" + dt.Rows[i][1].ToString() + "'),'" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "'," + proquantity + ",'" + sclass + "','" + User.Identity.Name + "',getdate(),'" + dt.Rows[i][0].ToString() + "',(select sn from ProBomDetail where seq='" + seq.Substring(0, seq.LastIndexOf(".")) + "' and fsn=" + bomsn + "),1," + basenum + ",'" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "','" + dt.Rows[i][11].ToString() + "')";
}
else
{
//bomsn,物料sn，料号，名称，规格，材质，表面处理，用量，分类
sqldtl = "insert into ProBomDetail(FSN,AllitemSN,ItemNo,ItemName,Spec,Material,SurfaceDeal,ProUsingQuantity,ZongCheng,Inputer,InputeDate,seq,ZuHe,BaseNum,WorkShop,MainFrom,Sclass,StoreHouse) values(" + bomsn + ",(select sn from rlitems where itemno='" + dt.Rows[i][1].ToString() + "'),'" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "'," + proquantity + ",'" + sclass + "','" + User.Identity.Name + "',getdate(),'" + dt.Rows[i][0].ToString() + "',0," + basenum + ",'" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "','" + dt.Rows[i][11].ToString() + "')";
}
#endregion
log.Info("sqldtl::::" + sqldtl);
al.Add(sqldtl);
SQLHelper.DbHelperSQL.ExecuteSql(sqldtl, 30);
}
else
{
//sql = "update rlitems set (itemno,itemname,Spec,Material,SurfaceDeal,ProUsingQuantity,BaseNum,WorkShop,MainFrom,Sclass,StoreHouse,zongcheng) values('" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "'," + proquantity + "," + basenum + ",'" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "','" + dt.Rows[i][11].ToString() + "','" + sclass + "')";
#region
if (seq.IndexOf(".") != -1)
{
//bomsn,物料sn，料号，名称，规格，材质，表面处理，用量，分类
sqldtl = "insert into ProBomDetail(FSN,AllitemSN,ItemNo,ItemName,Spec,Material,SurfaceDeal,ProUsingQuantity,ZongCheng,Inputer,InputeDate,seq,parentsn,ZuHe,BaseNum,WorkShop,MainFrom,Sclass,StoreHouse) values(" + bomsn + "," + dtitem.Rows[0]["sn"].ToString() + ",'" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "'," + proquantity + ",'" + sclass + "','" + User.Identity.Name + "',getdate(),'" + dt.Rows[i][0].ToString() + "',(select sn from ProBomDetail where seq='" + seq.Substring(0, seq.LastIndexOf(".")) + "' and fsn=" + bomsn + "),1,'" + basenum + "','" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "','" + dt.Rows[i][11].ToString() + "')";
}
else
{
//bomsn,物料sn，料号，名称，规格，材质，表面处理，用量，分类
sqldtl = "insert into ProBomDetail(FSN,AllitemSN,ItemNo,ItemName,Spec,Material,SurfaceDeal,ProUsingQuantity,ZongCheng,Inputer,InputeDate,seq,ZuHe,BaseNum,WorkShop,MainFrom,Sclass,StoreHouse) values(" + bomsn + "," + dtitem.Rows[0]["sn"].ToString() + ",'" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "'," + proquantity + ",'" + sclass + "','" + User.Identity.Name + "',getdate(),'" + dt.Rows[i][0].ToString() + "',0," + basenum + ",'" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "','" + dt.Rows[i][11].ToString() + "')";
}
#endregion
SQLHelper.DbHelperSQL.ExecuteSql(sqldtl, 30);
log.Info("sqldtl::::" + sqldtl);
sql = "update proBomDetail set subsn=SN where subsn is null";
SQLHelper.DbHelperSQL.ExecuteSql(sqldtl, 30);
al.Add(sqldtl);
}
#endregion
}
#endregion
}
}
//if (SQLHelper.DbHelperSQL.ExecuteSqlTran(al))
//{
Alert.Show("导入成功");
//}
//else
//{
// Alert.Show("导入失败");
//}
//al.Clear();
#endregion
}
else
{
Alert.Show("NO Data");
}
}
}
catch (Exception ee)
{
string sql = "delete proBomHeader where sn=" + bomsn;
ArrayList al = new ArrayList();
al.Add(sql);
sql = "delete proBomDetail where fsn=" + bomsn;
SQLHelper.DbHelperSQL.SetConnectionString("");
SQLHelper.DbHelperSQL.ExecuteSqlTran(al);
log.Info(ee.ToString());
Alert.Show(ee.Message);
}
finally
{
BindGrid();
}
}