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(); } }