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;
//}
}
