/// <summary>
/// 导入工序工时
/// </summary>
/// <param name="FU_Excel"></param>
/// <param name="server"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool ImpProductWorkSN(FileUpload FU_Excel, HttpServerUtility server, ref string error)
{
DataSet ds = ToolManager.ImpExcel(FU_Excel, server);
if (ds == null)
{
error = "选择的文件为空或不是标准的Excel文件！";
return(false);
}
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 (!AddWorkSn(dr, 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);
}
sql = string.Format(@"update Product set RatedManhour =ISNULL (a.总工时,0) from Product p left join (
select ProductNumber ,Version,SUM(RatedManhour) as 总工时 from ProductWorkSnProperty group by ProductNumber ,Version
) a on p.ProductNumber=a.ProductNumber and p.Version =a.Version ");
SqlHelper.ExecuteSql(sql);
return(result);
}
/// <summary>
/// 导入采购订单
/// </summary>
/// <param name="FU_Excel"></param>
/// <param name="server"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool ImpCertificateOrdersList_New(FileUpload FU_Excel, string userId, HttpServerUtility server, ref string error)
{
string sql = string.Format(" delete T_CertificateOrders where userId='{0}'", userId);
SqlHelper.ExecuteSql(sql);
DataSet ds = ToolManager.ImpExcel(FU_Excel, server);
if (ds == null)
{
error = "选择的文件为空或不是标准的Excel文件！";
return(false);
}
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 (!AddCertificateOrdersDetail_New(dr, 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);
}
int a = 1;
string cgOrderNumber = string.Empty;
List <string> sqls = new List <string>();
sql = string.Format(@"select HTNumber ,OrdersDate,PaymentMode,SupplierId ,ContactId from T_CertificateOrders group by HTNumber ,OrdersDate,PaymentMode,SupplierId ,ContactId ");
DataTable dtNew = SqlHelper.GetTable(sql);
foreach (DataRow drNew in dtNew.Rows)
{
cgOrderNumber = "CG" + DateTime.Now.AddSeconds(a).ToString("yyyyMMddHHmmss");
a++;
sql = string.Format(@"insert into CertificateOrders (OrdersNumber ,HTNumber ,OrdersDate,
PaymentMode ,SupplierId ,ContactId ,OrderStatus,CreateTime )
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", cgOrderNumber, drNew["HTNumber"]
, drNew["OrdersDate"], drNew["PaymentMode"], drNew["SupplierId"], drNew["ContactId"], "未完成", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
sqls.Add(sql);
sql = string.Format(@" insert into CertificateOrdersDetail
(OrdersNumber,MaterialNumber ,LeadTime,SupplierMaterialNumber ,OrderQty ,NonDeliveryQty ,DeliveryQty ,UnitPrice ,
SumPrice,UnitPrice_C,SumPrice_C
,MinOrderQty ,PayOne ,PayTwo )
select '{0}',tco.MaterialNumber ,tco.LeadTime ,tco.SupplierMaterialNumber,tco.OrderQty ,tco.OrderQty ,0,msp.Prcie
,tco.OrderQty *msp.Prcie ,msp.Prcie *1.17,tco.OrderQty *msp.Prcie *1.17,tco.MinOrderQty ,tco.PayOne ,tco.PayTwo
from T_CertificateOrders tco inner join MaterialSupplierProperty msp on tco.SupplierMaterialNumber=msp.SupplierMaterialNumber
and tco.SupplierId =msp.SupplierId
where tco.HTNumber ='{1}' and tco.OrdersDate ='{2}' and tco.PaymentMode='{3}' and tco.SupplierId ='{4}' and tco.ContactId ='{5}' and tco.userId='{6}'"
, cgOrderNumber, drNew["HTNumber"]
, drNew["OrdersDate"], drNew["PaymentMode"], drNew["SupplierId"], drNew["ContactId"], userId);
sqls.Add(sql);
}
sql = string.Format(@"delete CertificateOrders where OrdersNumber not in (
select distinct OrdersNumber from CertificateOrdersDetail)");
sqls.Add(sql);
string tmeptempErrpr = string.Empty;
bool resulTwo = SqlHelper.BatchExecuteSql(sqls, ref tmeptempErrpr);
if (result && resulTwo)
{
return(true);
}
else
{
error = error + "<br/>" + tmeptempErrpr;
return(false);
}
}
/// <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);
}
}
/// <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="tods"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool BacthAddQuoteInfoTradingForListory(FileUpload FU_Excel, HttpServerUtility server, string userId, ref string error)
{
string sqlTemp = string.Format(" delete T_TradingQuoteDetail_Temp where userId='{0}' ", userId);
SqlHelper.ExecuteSql(sqlTemp, ref error);
DataSet ds = ToolManager.ImpExcel(FU_Excel, server);
if (ds == null)
{
error = "选择的文件为空或不是标准的Excel文件！";
return(false);
}
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 (!AddQuoteInfoTradingForListory(dr, ref tempError, userId))
{
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 sql = string.Format(@" select userId,CustomerId,ContactId,QuoteTime from T_TradingQuoteDetail_Temp
where userId='{0}'
group by userId,CustomerId,ContactId,QuoteTime ", userId);
DataTable dtNew = SqlHelper.GetTable(sql);
string bjNumber = "";
int h = 1;
foreach (DataRow drNew in dtNew.Rows)
{
bjNumber = "MYBJ" + DateTime.Now.AddSeconds(h).ToString("yyyyMMddHHmmss");
//开一个报价单
sql = string.Format(@"insert into QuoteInfo(QuoteNumber,QuoteTime,QuoteType,CustomerId ,ContactId,CreateDateTime ,QuoteUser )
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", bjNumber, drNew["QuoteTime"], "贸易报价单",
drNew["CustomerId"], drNew["ContactId"], DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), userId);
sqls.Add(sql);
sql = string.Format(@" insert into TradingQuoteDetail (QuoteNumber,ProductNumber,CustomerMaterialNumber,FixedLeadTime
,UnitPrice,MinPackage,MinMOQ,MaterialDescription,DW,Remark,SupplierMaterialNumber)
select '{4}',MaterialNumber,CustomerMaterialNumber,FixedLeadTime
,UnitPrice,MinPackage,MinMOQ,MaterialDescription, DW,Remark ,SupplierMaterialNumber from T_TradingQuoteDetail_Temp
where userId='{0}' and CustomerId='{1}' and ContactId='{2}'
and QuoteTime='{3}' ", userId, drNew["CustomerId"], drNew["ContactId"], drNew["QuoteTime"], bjNumber);
sqls.Add(sql);
h++;
}
string tempErrorNew = string.Empty;
SqlHelper.BatchExecuteSql(sqls, ref tempErrorNew);
return(result);
}