private static bool AddSaleOrder(DataRow dr, SaleOder so, string userId, ref string error) { //要求交期、行号、客户产成品编号、版本、数量、是否是原材料 string leadTime = ""; string sql = ""; // string materialNumber = ""; string version = dr["版本"].ToString() == "" ? "WU" : dr["版本"].ToString().ToUpper(); string customerProductNumber = dr["客户产成品编号"].ToString(); string productNumber = ""; try { leadTime = Convert.ToDateTime(dr["要求交期"]).ToString("yyyy-MM-dd"); } catch (Exception ex) { error = string.Format("要求交期:{0},不是正规的日期格式‘yyyy-MM-dd’", dr["要求交期"]); return(false); } if (dr["是否是原材料"].ToString().Equals("是")) { sql = string.Format(@" select 产成品编号 from V_TradingQuoteDetailReport_New where 客户物料编号='{0}' and 客户名称='{1}'", customerProductNumber, so.CustomerName); productNumber = SqlHelper.GetScalar(sql); if (string.IsNullOrEmpty(productNumber)) { error = string.Format(" 系统不存在该客户物料编号:{0} 的报价信息,客户{1}", customerProductNumber, so.CustomerName); return(false); } sql = string.Format(@" select MaterialNumber from MaterialCustomerProperty where CustomerMaterialNumber='{0}' and CustomerId='{1}'", customerProductNumber, so.CustomerId); productNumber = SqlHelper.GetScalar(sql); if (string.IsNullOrEmpty(productNumber)) { error = string.Format(" 系统不存在该客户物料编号:{0} 的原材料编号,客户{1}", customerProductNumber, so.CustomerName); return(false); } } else { if (string.IsNullOrEmpty(version)) { version = SellManager.GetMostNewVersion(customerProductNumber); } sql = string.Format(@"select 产成品编号 from V_FindLastNewPriceForMachineQuoteDeatil where 客户产成品编号='{0}' and 版本='{1}' and 客户名称='{2}'", customerProductNumber, version, so.CustomerName); productNumber = SqlHelper.GetScalar(sql); if (string.IsNullOrEmpty(productNumber)) { error = string.Format("系统不存在该客户产成品编号:{0},版本:{1}的报价信息,客户{2}", customerProductNumber, version, so.CustomerName); return(false); } sql = string.Format(@" select ProductNumber from ProductCustomerProperty where CustomerProductNumber='{0}' and Version='{1}' and CustomerId='{2}'", customerProductNumber, version, so.CustomerId); productNumber = SqlHelper.GetScalar(sql); if (string.IsNullOrEmpty(productNumber)) { error = string.Format("系统不存在该客户产成品编号:{0},版本:{1}的产成品编号,客户{2}", customerProductNumber, version, so.CustomerName); return(false); } if (!ProductManager.HasBOM(productNumber, version)) { error = string.Format("该产成品编号和版本找不到BOM信息,请先录入BOM!客户产成品编号:{0},产成品编号:{1},版本:{2}", customerProductNumber, productNumber, version); return(false); } } sql = string.Format(" select count(*) from T_ImpSaleOder_Temp where userId='{0}' and RowNumber='{1}'", userId, dr["行号"]); if (!SqlHelper.GetScalar(sql).Equals("0")) { error = string.Format("已存在相同行号:{0}", dr["行号"]); return(false); } // if (dr["是否是原材料"].ToString().Equals("是")) // { // sql = string.Format(@" select count(*) from T_ImpSaleOder_Temp where ProductNumber='{0}' and LeadTime='{1}' // and IsMateriNumber='是'", productNumber, leadTime); // if (!SqlHelper.GetScalar(sql).Equals("0")) // { // error = string.Format("已存在相同交期的贸易销售订单明细!重复行: 客户物料号{0},交期{1}", customerProductNumber, leadTime); // return false; // } // } sql = string.Format(@" insert into T_ImpSaleOder_Temp (ProductNumber,version, CustomerProductNumber,RowNumber,LeadTime,Qty,IsMateriNumber,UserId) values('{0}','{1}','{2}','{3}','{4}',{5},'{6}','{7}')", productNumber, version, customerProductNumber, dr["行号"], leadTime , dr["数量"], dr["是否是原材料"], userId); return(SqlHelper.ExecuteSql(sql, ref error)); }
/// <summary> /// 批量导入销售订单 /// </summary> /// <param name="so"></param> /// <param name="FU_Excel"></param> /// <param name="server"></param> /// <param name="error"></param> /// <returns></returns> public static bool BacthAddSaleOrder(SaleOder so, FileUpload FU_Excel, HttpServerUtility server, ref string error) { bool result = false; DataSet ds = ToolManager.ImpExcel(FU_Excel, server); if (ds == null) { error = "选择的文件为空或不是标准的Excel文件!"; return(false); } DataTable dt = ds.Tables[0]; if (so.ProductType.Equals("贸易")) { if (!dt.Columns.Contains("客户物料编号")) { error = "导入模板与生产类型不一致!"; return(false); } } else { if (!dt.Columns.Contains("客户产成品编号")) { error = "导入模板与生产类型不一致!"; return(false); } } //插入主表信息 string sql = string.Format(@"insert into SaleOder (OdersNumber ,OrdersDate ,OdersType ,ProductType ,MakeCollectionsMode,CustomerId ,ContactId ,CreateTime ,Remark ,CustomerOrderNumber,KhddH ) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')", so.OrdersNumber, so.OrdersDate, so.OdersType , so.ProductType, so.MakeCollectionsMode, so.CustomerId, so.ContactId, so.CreateTime, so.Remark, so.CustomerOrderNumber, so.KhddH); SqlHelper.ExecuteSql(sql, ref error); //按照生产类型分别批量添加 if (so.ProductType.Equals("贸易")) { List <TradingOrderDetail> tods = new List <TradingOrderDetail>(); foreach (DataRow dr in dt.Rows) { TradingOrderDetail tod = new TradingOrderDetail(); tod.OrdersNumber = so.OrdersNumber; tod.SN = dr["序号"].ToString(); tod.RowNumber = dr["行号"].ToString(); tod.Delivery = GetLeadTime(dr["交期"].ToString()); tod.CustomerMaterialNumber = dr["客户物料编号"].ToString(); tod.Quantity = dr["订单数量"].ToString(); tod.Remark = dr["备注"].ToString(); tod.CustomerId = so.CustomerId; tods.Add(tod); } result = BacthAddTradingOrderDetail(tods, ref error); } else // 序号 行号 交期 客户产成品编号 订单数量 { List <MachineOderDetail> mods = new List <MachineOderDetail>(); foreach (DataRow dr in dt.Rows) { MachineOderDetail mod = new MachineOderDetail(); mod.OrdersNumber = so.OrdersNumber; mod.SN = dr["序号"].ToString(); mod.RowNumber = dr["行号"].ToString(); mod.LeadTime = GetLeadTime(dr["交期"].ToString()); mod.CustomerProductNumber = dr["客户产成品编号"].ToString(); mod.Qty = dr["订单数量"].ToString(); mod.CustomerId = so.CustomerId; mod.Version = dr["版本"].ToString().ToUpper(); if (mod.Version.Equals("")) { mod.Version = SellManager.GetMostNewVersion(dr["客户产成品编号"].ToString()); } else if (mod.Version.Equals("OO")) { mod.Version = "00"; } mods.Add(mod); } result = BacthAddMachineOderDetail(mods, ref error); } return(result); }
/// <summary> /// 批量导入销售订单 /// </summary> /// <param name="so"></param> /// <param name="FU_Excel"></param> /// <param name="server"></param> /// <param name="error"></param> /// <returns></returns> public static bool BacthAddSaleOrderNew(SaleOder so, FileUpload FU_Excel, HttpServerUtility server, string userId, ref string error) { DataSet ds = ToolManager.ImpExcel(FU_Excel, server); if (ds == null) { error = "选择的文件为空或不是标准的Excel文件!"; return(false); } string sql = string.Format(@" delete T_ImpSaleOder_Temp where userId='{0}' ", userId); SqlHelper.ExecuteSql(sql); DataTable dt = ds.Tables[0]; int i = 0; string tempError = string.Empty; if (dt.Rows.Count <= 0) { error = "没有要添加的数据"; return(false); } foreach (DataRow dr in dt.Rows) { tempError = ""; if (!AddSaleOrder(dr, so, userId, ref tempError)) { i++; error += string.Format("添加失败:原因--{0}<br/>", tempError); } } bool result = i > 0 ? false : true; if (!result) { error = string.Format("添加成功{0}条,添加失败{1}条。<br/>{2}", (dt.Rows.Count - i).ToString(), i.ToString(), error); } List <string> sqls = new List <string>(); string xsNumber = ""; sql = string.Format(@"select COUNT (*) from T_ImpSaleOder_Temp where ismateriNumber ='是' and userid='{0}'", userId); if (!SqlHelper.GetScalar(sql).Equals("0")) { xsNumber = "XS" + DateTime.Now.AddSeconds(1).ToString("yyyyMMddHHmmss"); sql = string.Format(@"insert into SaleOder(OdersNumber,OrdersDate,OdersType,ProductType , MakeCollectionsMode,CustomerId ,ContactId ,OrderStatus ,CreateTime ,CustomerOrderNumber,KhddH ) values('{0}','{1}','{2}','贸易','{3}','{4}','{5}','未完成','{6}','{7}','{8}')", xsNumber, so.OrdersDate, so.OdersType.Equals("包装生产订单") ? "正常订单" : so.OdersType, so.MakeCollectionsMode, so.CustomerId, so.ContactId, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), so.CustomerOrderNumber, so.KhddH); sqls.Add(sql); sql = string.Format(@"insert into TradingOrderDetail (OdersNumber ,ProductNumber,RowNumber ,Delivery ,SN,CustomerMaterialNumber,MaterialName,Brand ,Quantity ,NonDeliveryQty ,DeliveryQty ,UnitPrice ,TotalPrice ,CreateTime ,Status ) select '{0}',tit.ProductNumber,tit.RowNumber,CONVERT(varchar(100), CAST(tit.LeadTime as date ), 23),1,tit.CustomerProductNumber,vtdn .物料描述 ,vtdn.品牌,tit.Qty,tit.Qty,0,vtdn .单价,vtdn .单价*tit.Qty,'{1}','未完成' from T_ImpSaleOder_Temp tit inner join V_FindLastNewPriceForTradingQuoteDetail vtdn on tit.ProductNumber=vtdn.原材料编号 where tit.userId='{2}' and vtdn.客户名称='{3}' and tit.IsMateriNumber='是'", xsNumber, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), userId, so.CustomerName); sqls.Add(sql); } sql = string.Format(@"select COUNT (*) from T_ImpSaleOder_Temp where ismateriNumber ='否' and userid='{0}'", userId); if (!SqlHelper.GetScalar(sql).Equals("0")) { xsNumber = "XS" + DateTime.Now.AddSeconds(2).ToString("yyyyMMddHHmmss"); sql = string.Format(@"insert into SaleOder(OdersNumber,OrdersDate,OdersType,ProductType , MakeCollectionsMode,CustomerId ,ContactId ,OrderStatus ,CreateTime ,CustomerOrderNumber,KhddH ) values('{0}','{1}','{2}','加工','{3}','{4}','{5}','未完成','{6}','{7}','{8}')", xsNumber, so.OrdersDate, so.OdersType, so.MakeCollectionsMode, so.CustomerId, so.ContactId, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), so.CustomerOrderNumber, so.KhddH); sqls.Add(sql); sql = string.Format(@" insert into MachineOderDetail (OdersNumber ,ProductNumber ,Version ,LeadTime ,RowNumber ,SN ,CustomerProductNumber ,Qty ,NonDeliveryQty ,DeliveryQty ,UnitPrice ,SumPrice ,CreateTime ,Status ) select '{0}',tit.ProductNumber ,tit.Version ,CONVERT(varchar(100), CAST(tit.LeadTime as date ), 23) ,tit.RowNumber ,1,tit.CustomerProductNumber ,tit.Qty ,tit.Qty ,0,vmr.单价未税,tit.Qty *vmr.单价未税,'{1}','未完成' from T_ImpSaleOder_Temp tit inner join V_FindLastNewPriceForMachineQuoteDeatil vmr on tit.ProductNumber =vmr.产成品编号 and tit.Version =vmr.版本 where tit.UserId ='{2}' and vmr.客户名称='{3}' and tit.IsMateriNumber='否' ", xsNumber, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") , userId, so.CustomerName); sqls.Add(sql); } sql = string.Format(@"delete SaleOder where OdersNumber not in ( select distinct OdersNumber from MachineOderDetail union all select distinct OdersNumber from TradingOrderDetail)"); sqls.Add(sql); string errorTwo = string.Empty; bool resultTwo = SqlHelper.BatchExecuteSql(sqls, ref errorTwo); if (result && resultTwo) { return(true); } else { error += "<br/>" + errorTwo; return(false); } }
protected void btnUpload_Click(object sender, EventArgs e) { string odersnumber = "XS" + DateTime.Now.ToString("yyyyMMddHHmmss"); string error = string.Empty; string sql = string.Empty; string ordersDate = txtOrdersDate.Text.Trim(); string odersType = drpOdersType.SelectedValue; string productType = drpProductType.SelectedValue; string isMinusStock = drpIsMinusStock.SelectedValue; string makeCollectionsMode = this.drpMakeCollectionsMode.SelectedValue; string customerId = drpClient.SelectedValue; string contactId = drpContact.SelectedValue; string remark = txtRemark.Text.Trim(); string customerordernumber = txtCustomerOrderNumber.Text; string khddH = txtKhddH.Text; // List<string> sqls = new List<string>(); string brand = string.Empty; string description = string.Empty; string unitPrice = string.Empty; string quantity = string.Empty; string name = string.Empty; SaleOder so = new SaleOder(); so.OrdersNumber = odersnumber; so.OrdersDate = ordersDate; so.OdersType = odersType; so.ProductType = productType; so.MakeCollectionsMode = makeCollectionsMode; so.CustomerId = customerId; so.ContactId = contactId; so.CreateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); so.CustomerOrderNumber = customerordernumber; so.KhddH = khddH; so.Remark = remark; so.CustomerName = drpClient.SelectedItem.Text.Trim(); string userId = ToolCode.Tool.GetUser().UserNumber; bool result = SaleOderManager.BacthAddSaleOrderNew(so, FU_Excel, Server, userId, ref error); if (result == true) { lbMsg.Text = "导入成功"; Tool.WriteLog(Tool.LogType.Operating, "导入销售订单", "导入成功!"); return; } else { lbMsg.Text = error; Tool.WriteLog(Tool.LogType.Operating, "导入销售订单", "导入失败!原因" + lbSubmit.Text); return; } // sql = string.Format(@"insert into SaleOder (OdersNumber ,OrdersDate,OdersType,ProductType //,IsMinusStock,MakeCollectionsMode,CustomerId,ContactId ,OrderStatus,CreateTime ,Remark,CustomerOrderNumber ,KhddH) //values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','未完成','{8}','{9}','{10}','{11}')", odersnumber, // ordersDate, odersType, productType, isMinusStock, makeCollectionsMode, customerId, contactId // , DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), remark, customerordernumber, khddH); // sqls.Add(sql); //DataSet ds = ToolManager.ImpExcel(this.FU_Excel, Server); //if (ds == null) //{ // lbMsg.Text = "选择的文件为空或不是标准的Excel文件!"; // return; //} // foreach (DataRow dr in ds.Tables[0].Rows) // { // quantity =dr["订单数量"].ToString(); // sql = string.Format(@"select count(*) from MaterialCustomerProperty where CustomerMaterialNumber='{0}'", dr["客户物料编号"]); // string num = SqlHelper.GetScalar(sql); // if (num == "0") // { // lbSubmit.Text += string.Format("贸易销售订单{0}添加失败!原因:该原材料不存在客户物料编号<br/>", dr["客户物料编号"]); // i++; // continue; // } // sql = string.Format(@" select MaterialNumber from MaterialCustomerProperty where CustomerMaterialNumber='{0}' and CustomerId=(select CustomerId from SaleOder where OdersNumber='{1}') //", dr["客户物料编号"], odersnumber); // string productNumber = SqlHelper.GetScalar(sql); // if (productNumber.Equals("")) // { // lbSubmit.Text += string.Format("贸易销售订单{0}添加失败!原因:该客户原材料编号不存在<br/>", dr["客户物料编号"]); // i++; // continue; // } // sql = string.Format(@"select Description,Brand,ProcurementPrice,MaterialName from MarerialInfoTable where MaterialNumber='{0}'", productNumber); // dt = SqlHelper.GetTable(sql, ref error); // if (dt.Rows.Count > 0) // { // brand = dt.Rows[0]["Brand"].ToString(); // description = dt.Rows[0]["Description"].ToString(); // unitPrice = dt.Rows[0]["ProcurementPrice"].ToString(); // name=dt.Rows[0]["MaterialName"].ToString(); // qty = string.IsNullOrEmpty(quantity) ? 0 : Convert.ToInt32(quantity); // uprice = string.IsNullOrEmpty(unitPrice) ? 0 : Convert.ToDecimal(unitPrice); // totalPrice = qty * uprice; // } // sql = string.Format(@" //select * from TradingOrderDetail where OdersNumber ='{0}' and ProductNumber='{1}' and RowNumber='{2}'", odersnumber, productNumber, dr["行号"]); // dt = SqlHelper.GetTable(sql, ref error); // if (dt.Rows.Count > 0) // { // lbSubmit.Text += string.Format("贸易销售订单{0}添加失败!原因:此记录已存在<br/>", odersnumber); // i++; // continue; // } // sql = string.Format(@" insert into TradingOrderDetail (OdersNumber,SN,ProductNumber,RowNumber ,CustomerMaterialNumber,MaterialName //,Brand,Quantity,NonDeliveryQty,DeliveryQty,UnitPrice,TotalPrice,Delivery,CreateTime,Remark,Status,ProductModel) //values('{0}',{1},'{2}','{3}','{4}','{5}','{6}',{7},{8},{9},{10},{11},'{12}','{13}','{14}','{15}','{16}')" // , odersnumber, dr["序号"], productNumber, dr["行号"], dr["客户物料编号"], description, brand, qty, qty, 0 // , uprice, totalPrice, dr["交期"], DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), remark,"未完成",name); // sqls.Add(sql); // } // bool result = false; // if (i == 0) // { // result = true; // } // if (result == true) // { // lbMsg.Text = "导入成功!"; // } // else // { // lbMsg.Text = "导入失败!<br/>" + lbSubmit.Text; // } //if (result == true) //{ // Tool.WriteLog(Tool.LogType.Operating, "导入贸易销售订单", "导入成功!"); // return; //} //else //{ // Tool.WriteLog(Tool.LogType.Operating, "导入贸易销售订单", "导入失败!原因" + lbSubmit.Text); // return; //} }