/// <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="ids"></param>
/// <returns></returns>
public static string DeleteData(string ids)
{
sql = string.Format(@" delete WorkSn where WorkSnNumber in ({0}) ", ids);
return(SqlHelper.ExecuteSql(sql, ref error) == true ? "1" : error);
}
/// <summary>
/// 编辑工序
/// </summary>
/// <param name="user"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool EditWorkSn(WorkSn worksn, ref string error)
{
sql = string.Format(@" update WorkSn set WorkSnName='{0}',sn='{2}' where WorkSnNumber='{1}' ", worksn.WorkSnName, worksn.WorkSnNumber, worksn.Sn);
return(SqlHelper.ExecuteSql(sql, ref error));
}
/// <summary>
/// 原材料出入库审核【单条审核】
/// </summary>
/// <param name="auditor"></param>
/// <param name="warehouseNumber"></param>
/// <returns></returns>
public static string AuditorMarerialWarehouseLogForWarehouseNumber(string auditor, string warehouseNumber)
{
string checksqls = string.Format(@" select CheckTime from MarerialWarehouseLog where WarehouseNumber='{0}'", warehouseNumber);
if (!string.IsNullOrEmpty(SqlHelper.GetScalar(checksqls)))
{
return("该单已审核，请勿重复审核！");
}
List <string> sqls = new List <string>();
DataTable dtTemp = null;
string result = string.Empty;
string sql = string.Format(@"
select COUNT(*) from MaterialWarehouseLogDetail where WarehouseNumber='{0}'", warehouseNumber);
if (SqlHelper.GetScalar(sql).Equals("0"))
{
return("没有需要审核的记录！");
}
sql = string.Format("select Type from MarerialWarehouseLog where WarehouseNumber='{0}'", warehouseNumber);
string type = SqlHelper.GetScalar(sql);
if (type.Equals("生产出库") || type.Equals("包装出库"))
{
return(SCCKAuditor(auditor, warehouseNumber));
}
if (type.Equals("销售出库（贸易）"))
{
return(CheckMYXS(auditor, warehouseNumber));
}
//步骤一：检测采购入库和销售出库（贸易）的数量-订单未交数量是否大于0（大于0为异常数据）
if (type.Equals("采购入库"))
{
sql = string.Format(@"
select t.DocumentNumber ,t.MaterialNumber ,t.LeadTime from (
select WarehouseNumber,DocumentNumber,MaterialNumber,LeadTime,sum(isnull(Qty,0)) qty from MaterialWarehouseLogDetail
where WarehouseNumber ='{0}'
group by
WarehouseNumber,DocumentNumber,MaterialNumber,LeadTime
)t left join CertificateOrdersDetail cod on t.DocumentNumber=cod.OrdersNumber
and t.MaterialNumber =cod.MaterialNumber and t.LeadTime =cod.LeadTime where t.Qty -cod.NonDeliveryQty >0", warehouseNumber);
dtTemp = SqlHelper.GetTable(sql);
if (dtTemp.Rows.Count > 0)
{
foreach (DataRow dr in dtTemp.Rows)
{
result += "<br/>" + string.Format(" 订单编号为：{0} 原材料编号为：{1} 交期为{2}的记录数量大于订单未交数量！ "
, dr["DocumentNumber"], dr["MaterialNumber"], dr["LeadTime"]);
}
return(result);
}
//sqls.AddRange(FinancialManager.GetCSYF(warehouseNumber));
}
else if (type.Equals("销售出库（贸易）"))
{
sql = string.Format(@"select mwld.DocumentNumber ,mwld.MaterialNumber ,mwld.LeadTime
from MaterialWarehouseLogDetail mwld inner join MarerialWarehouseLog mwl on mwl.WarehouseNumber =mwld.WarehouseNumber
left join TradingOrderDetail cod on mwld.DocumentNumber=cod.OdersNumber
and mwld.MaterialNumber =cod.ProductNumber and mwld.LeadTime =cod.Delivery and mwld.RowNumber =cod.RowNumber
where mwld.Qty -cod.NonDeliveryQty >0
and mwl.WarehouseNumber ='{0}'", warehouseNumber);
dtTemp = SqlHelper.GetTable(sql);
if (dtTemp.Rows.Count > 0)
{
foreach (DataRow dr in dtTemp.Rows)
{
result += string.Format(" 订单编号为：{0} 原材料编号为：{1} 交期为{2}的记录的数量大于订单未交数量！ "
, dr["DocumentNumber"], dr["MaterialNumber"], dr["LeadTime"]);
}
return(result);
}
//纠错 仅限于销售出库（贸易）
string tempsql1 = string.Format(@"
DELETE MaterialWarehouseLogDetail
WHERE WarehouseNumber = '{0}'
AND Qty < 0;
", warehouseNumber);
SqlHelper.ExecuteSql(tempsql1);
//更新订单完成数量
sqls.Add(GetUpdateQtySql(warehouseNumber));
//生成送货单
List <string> sqlsTemp = GetSqlForShSHD(warehouseNumber);
if (sqlsTemp.Count > 0)
{
sqls.AddRange(sqlsTemp);
}
}
//步骤二：检测库存数量是否满足出库
if (type.Equals("销售出库（贸易）"))
{
string tempchecksql = string.Format(@" select COUNT (*) from (
select t.MaterialNumber,t.WarehouseName, (ISNULL ( ms.StockQty,0)-t.Qty) as 差 from (
select mwld.MaterialNumber ,sum( mwld.Qty ) as Qty,mwl.WarehouseName
from MaterialWarehouseLogDetail mwld
inner join MarerialWarehouseLog mwl on mwld.WarehouseNumber=mwl.WarehouseNumber
where mwl.ChangeDirection ='出库' and mwl.WarehouseNumber in ('{0}') and mwld.Qty>0
group by mwld.MaterialNumber,mwl.WarehouseName ) t left join MaterialStock ms on t.MaterialNumber=ms.MaterialNumber ) t where 差<0", warehouseNumber);
if (!SqlHelper.GetScalar(tempchecksql).Equals("0"))
{
return("当前库存数量低，无法满足当前出库操作。");
}
//return SqlHelper.GetScalar(sql).Equals("0") ? true : false;
}
else
{
//if (!CheckInventoryQty("'" + warehouseNumber + "'"))
//{
// return "当前库存数量低，无法满足当前出库操作。";
//}
}
//步骤三：同步更新库存数量 并产生应付 同步更新采购入库的已交货数量
string paymentdays = string.Empty;
string mode = string.Empty;
string CGOrderNumbers = string.Empty;
string error = string.Empty;
sql = string.Format(@"select * from V_MaterialWarehouseLogDetail_Detail where 出入库编号='{0}' ", warehouseNumber);
dtTemp = SqlHelper.GetTable(sql);
foreach (DataRow dr in dtTemp.Rows)
{
if (type.Equals("采购入库")) //产生应付
{
sql = string.Format(@"update CertificateOrdersDetail set DeliveryQty +={0}
where OrdersNumber='{1}' and MaterialNumber='{2}' and LeadTime ='{3}' and Status ='未完成'
", dr["数量"], dr["订单编号"], dr["原材料编号"], dr["交期"]); //同步更新采购入库的已交货数量
sqls.Add(sql);
CGOrderNumbers += string.Format("'{0}',", dr["订单编号"]);
}
//同步更新库存数量
//sqls.Add(GetUpdateInventoryQtySql(dr["原材料编号"].ToString(), dr["仓库ID"].ToString(), dr["数量"].ToString(), dr["方向"].ToString()));
}
string timeNow = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
//类型为采购入库=======生成预付第二层信息
sql = string.Format(@"insert into T_AccountsPayable_Detail(ReceiptNo,PurchaseOrderNumber ,PurchaseContractNumber,MaterialNumber ,SupplierMaterialNumber
,Description,PurchaseCount,NumberOfArrival,UnitPrice ,SumPrice ,TransportNo,LeadTime,CreateTime )
select vmld.WarehouseNumber ,vmld.DocumentNumber ,co.HTNumber,co.MaterialNumber,co.SupplierMaterialNumber,mit.Description ,co.OrderQty
,vmld.Qty,co.UnitPrice ,co.UnitPrice *vmld.Qty ,vmld.RoadTransport,vmld.LeadTime,'{1}' from ( select mwl.Type,mwld.* from MarerialWarehouseLog mwl inner join MaterialWarehouseLogDetail mwld on mwl.WarehouseNumber =mwld.WarehouseNumber
) vmld inner join ( select co.HTNumber ,co.PaymentMode ,cod.* from CertificateOrders co inner join CertificateOrdersDetail cod on co.OrdersNumber=cod.OrdersNumber
) co on vmld.DocumentNumber =
co.OrdersNumber and vmld.MaterialNumber =co.MaterialNumber and vmld.LeadTime =co.LeadTime
inner join MarerialInfoTable mit on vmld .MaterialNumber =mit.MaterialNumber
where vmld.WarehouseNumber ='{0}' and vmld.Type ='采购入库' ", warehouseNumber, timeNow);
sqls.Add(sql);
//类型为采购入库的生成应付第一层
sql = string.Format(@"insert into T_AccountsPayable_Main(OrdersNumber,CreateTime ,CGHTNumber ,ArrivalNumber,SumPrice ,ArrivalPrice
,SupplierId,PaymentTypes,PaymentMode,DeliveryDate)
select a.OrdersNumber ,'{0}',co.HTNumber ,a.到货数量,a.订单总价,a.到货总价,si.SupplierId ,si.PayType,si.PaymentMode,CONVERT(varchar(100), GETDATE(), 23) from (
select co.OrdersNumber,SUM(vmld.Qty ) as 到货数量,SUM (vmld .Qty *co.UnitPrice ) as 到货总价,SUM( co.SumPrice) as 订单总价 from ( select mwl.Type,mwld.* from MarerialWarehouseLog mwl inner join MaterialWarehouseLogDetail mwld on mwl.WarehouseNumber =mwld.WarehouseNumber
) vmld inner join ( select co.HTNumber ,co.PaymentMode,co.SupplierId ,cod.* from CertificateOrders co inner join CertificateOrdersDetail cod on co.OrdersNumber=cod.OrdersNumber
) co on vmld.DocumentNumber =
co.OrdersNumber and vmld.MaterialNumber =co.MaterialNumber and vmld.LeadTime =co.LeadTime
inner join MarerialInfoTable mit on vmld .MaterialNumber =mit.MaterialNumber
inner join SupplierInfo si on co.SupplierId=si.SupplierId
where vmld.WarehouseNumber ='{1}' and vmld.Type ='采购入库' and co.PaymentMode!='YFBF' and co.PaymentMode!='YFQK'
group by co.OrdersNumber) a inner join CertificateOrders co on a.OrdersNumber =co.OrdersNumber
inner join SupplierInfo si on co.SupplierId =si.SupplierId ", timeNow, warehouseNumber);
sqls.Add(sql);
//统一更新库存数量
// sql = string.Format(@"update MaterialStock set StockQty =
//case when mwl.ChangeDirection='入库' then StockQty +a.Qty else StockQty-a.Qty end
//,UpdateTime =CONVERT (varchar(100),GETDATE(),120) from
//( select mwld.MaterialNumber,sum(mwld.Qty) as Qty from MaterialWarehouseLogDetail mwld
// inner join MarerialWarehouseLog mwl on
// mwld.WarehouseNumber =mwl.WarehouseNumber
// where mwl.WarehouseNumber='{0}'
// group by mwld.MaterialNumber) a
// inner join MaterialStock ms on ms.MaterialNumber=a.MaterialNumber
// where ms.WarehouseName ='ycl'
// ", warehouseNumber);
sql = string.Format(@"update MaterialStock set StockQty =
case when a.ChangeDirection='入库' then StockQty +a.Qty else StockQty-a.Qty end
,UpdateTime =CONVERT (varchar(100),GETDATE(),120) from
( select mwld.MaterialNumber,mwl.ChangeDirection,sum(mwld.Qty) as Qty from MaterialWarehouseLogDetail mwld
inner join MarerialWarehouseLog mwl on
mwld.WarehouseNumber =mwl.WarehouseNumber
where mwl.WarehouseNumber='{0}'
group by mwld.MaterialNumber,mwl.ChangeDirection) a
inner join MaterialStock ms on ms.MaterialNumber=a.MaterialNumber
where ms.WarehouseName ='ycl'
", warehouseNumber);
sqls.Add(sql);
//如果是采购退料出库则反写出库单号至采购订单上
if (type.Equals("采购退料出库"))
{
string tempSql = string.Format(@"
update CertificateOrders set CCTCOrdersNumber='{0}'
where OrdersNumber in (
select distinct DocumentNumber from MaterialWarehouseLogDetail
where WarehouseNumber='{0}'
)", warehouseNumber);
sqls.Add(tempSql);
//warehouseNumber
}
//步骤四：同步更新采购入库订单明细的已交货、未交货、状态以及整条订单状态
sql = string.Format("update CertificateOrdersDetail set NonDeliveryQty =OrderQty-DeliveryQty");
sqls.Add(sql);
sql = string.Format("update CertificateOrdersDetail set Status ='已完成' where NonDeliveryQty=0");
sqls.Add(sql);
if (!string.IsNullOrEmpty(CGOrderNumbers))
{
sql = string.Format(@"update CertificateOrders set OrderStatus ='已完成'
where OrdersNumber in (
select OrdersNumber from CertificateOrdersDetail where OrdersNumber in({0})
group by OrdersNumber having SUM(NonDeliveryQty )=0
) and OrderStatus ='未完成'", CGOrderNumbers.TrimEnd(',')); //更新采购订单主表的状态
sqls.Add(sql);
}
sql = string.Format(@" update MarerialWarehouseLog set Auditor='{0}' , CheckTime='{1}' where WarehouseNumber ='{2}'", auditor, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), warehouseNumber);
sqls.Add(sql);
//更新瞬时库存数量
sqls.Add(StoreroomToolManager.GetUpDateInventoryQtySql(warehouseNumber));
//写入流水账
sqls.Add(StoreroomToolManager.WriteMateriLSZ(warehouseNumber, auditor));
sql = string.Format(" delete DeliveryNoteDetailed where DeliveryQty=0 ");//清除发货数量为0的送货单
sqls.Add(sql);
return(SqlHelper.BatchExecuteSql(sqls, ref error) ? "1" : error);
}
/// <summary>
/// 销售出库（贸易）审核
/// </summary>
/// <param name="warehouseNumber"></param>
/// <returns></returns>
public static string CheckMYXS(string auditor, string warehouseNumber)
{
string sql = string.Empty;
DataTable dtTemp = new DataTable();
string result = string.Empty;
List <string> sqls = new List <string>();
string error = string.Empty;
sql = string.Format(@"select mwld.DocumentNumber ,mwld.MaterialNumber ,mwld.LeadTime
from MaterialWarehouseLogDetail mwld inner join MarerialWarehouseLog mwl on mwl.WarehouseNumber =mwld.WarehouseNumber
left join TradingOrderDetail cod on mwld.DocumentNumber=cod.OdersNumber
and mwld.MaterialNumber =cod.ProductNumber and mwld.LeadTime =cod.Delivery and mwld.RowNumber =cod.RowNumber
where mwld.Qty -cod.NonDeliveryQty >0
and mwl.WarehouseNumber ='{0}'", warehouseNumber);
dtTemp = SqlHelper.GetTable(sql);
if (dtTemp.Rows.Count > 0)
{
foreach (DataRow dr in dtTemp.Rows)
{
result += string.Format(" 订单编号为：{0} 原材料编号为：{1} 交期为{2}的记录的数量大于订单未交数量！ "
, dr["DocumentNumber"], dr["MaterialNumber"], dr["LeadTime"]);
}
return(result);
}
//纠错 仅限于销售出库（贸易）
string tempsql1 = string.Format(@"
DELETE MaterialWarehouseLogDetail
WHERE WarehouseNumber = '{0}'
AND Qty < 0;
", warehouseNumber);
SqlHelper.ExecuteSql(tempsql1);
//更新订单完成数量
//sqls.Add(GetUpdateQtySql(warehouseNumber));
//生成送货单
List <string> sqlsTemp = GetSqlForShSHD(warehouseNumber);
if (sqlsTemp.Count > 0)
{
sqls.AddRange(sqlsTemp);
}
sql = string.Format(@"
INSERT INTO T_LessMaterialBreakdown(WarehouseNumber ,DocumentNumber ,MaterialNumber ,RowNumber ,LeadTime ,CustomerMaterialNumber ,LibraryQty,StockQty ,LessMaterialQty ,CreateTime ,IsLessMaterial)
SELECT '',
mwld.DocumentNumber ,
mwld. MaterialNumber,
'',
'' ,
mwld.CustomerMaterialNumber,
mwld. Qty,
vmq.StockQty,
CASE
WHEN vmq.StockQty<0 THEN 0-mwld.Qty
ELSE vmq.StockQty -mwld.Qty
END,
'{1}',
'未还料'
FROM
(SELECT DocumentNumber,
MaterialNumber,
CustomerMaterialNumber,
SUM(Qty) Qty
FROM MaterialWarehouseLogDetail
WHERE WarehouseNumber ='{0}'
GROUP BY DocumentNumber,
MaterialNumber,
CustomerMaterialNumber) mwld
INNER JOIN MaterialStock vmq ON mwld.MaterialNumber =vmq.MaterialNumber
WHERE vmq.StockQty-mwld.Qty <0
", warehouseNumber, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
sqls.Add(sql);
//更新库存
sql = string.Format(@"update MaterialStock set StockQty =
case when a.ChangeDirection='入库' then StockQty +a.Qty else StockQty-a.Qty end
,UpdateTime =CONVERT (varchar(100),GETDATE(),120) from
( select mwld.MaterialNumber,mwl.ChangeDirection,sum(mwld.Qty) as Qty from MaterialWarehouseLogDetail mwld
inner join MarerialWarehouseLog mwl on
mwld.WarehouseNumber =mwl.WarehouseNumber
where mwl.WarehouseNumber='{0}'
group by mwld.MaterialNumber,mwl.ChangeDirection) a
inner join MaterialStock ms on ms.MaterialNumber=a.MaterialNumber
where ms.WarehouseName ='ycl'
", warehouseNumber);
sqls.Add(sql);
sql = string.Format(@" update MarerialWarehouseLog set Auditor='{0}' , CheckTime='{1}' where WarehouseNumber ='{2}'", auditor, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), warehouseNumber);
sqls.Add(sql);
//更新瞬时库存数量
sqls.Add(StoreroomToolManager.GetUpDateInventoryQtySql(warehouseNumber));
//写入流水账
sqls.Add(StoreroomToolManager.WriteMateriLSZ(warehouseNumber, auditor));
sql = string.Format(" delete DeliveryNoteDetailed where DeliveryQty=0 ");//清除发货数量为0的送货单
sqls.Add(sql);
return(SqlHelper.BatchExecuteSql(sqls, ref error) ? "1" : error);
}
public static bool AddQuoteInfoMachineForListory(DataRow dr, ref string error, string userId)
{
string version = dr["版本"].ToString().ToUpper();
if (string.IsNullOrEmpty(version))
{
version = "WU";
}
if (version.Equals("OO"))
{
version = "00";
}
if (dr["单价（未税）"].ToString().Equals(""))
{
error = "单价（未税）不能为空";
return(false);
}
string sql = string.Format(@" select ProductNumber from ProductCustomerProperty where CustomerProductNumber ='{0}' and version='{1}'"
, dr["图纸号"], version);
string productNumber = SqlHelper.GetScalar(sql);
if (string.IsNullOrEmpty(productNumber))
{
error = string.Format("系统不存在该图纸号，请先录入该图纸号:{0},版本{1}", dr["图纸号"], version);
return(false);
}
sql = string.Format(@"
select customerName from customer where customerId='{0}'", dr["客户编号"]);
string customerName = SqlHelper.GetScalar(sql);
if (string.IsNullOrEmpty(customerName))
{
error = string.Format("系统不存在该客户编号:{0}", dr["客户编号"]);
return(false);
}
string bjTime = "";
try
{
bjTime = dr["报价时间"] == null?
DateTime.Now.AddSeconds(2).ToString("yyyy-MM-dd") : Convert.ToDateTime(dr["报价时间"]).ToString("yyyy-MM-dd");
}
catch (Exception ec)
{
error = string.Format("报价时间:{0}不是正规的日期格式yyyy-MM-dd", dr["报价时间"]);
return(false);
}
//sql = string.Format(@"select COUNT(0) from PM_USER where USER_NAME='{0}'", dr["客户联系人"]);
//if (SqlHelper.GetScalar(sql).Equals("0"))
//{
// error = string.Format("系统不存该客户联系人：{0}", dr["客户联系人"]);
// return false;
//}
string materiNumber = string.Empty;
if (dr["是否是原材料"].ToString().Equals("是"))
{
sql = string.Format(@"select MaterialNumber from MaterialCustomerProperty where CustomerMaterialNumber='{0}'", dr["客户物料号"]);
materiNumber = SqlHelper.GetScalar(sql);
if (string.IsNullOrEmpty(materiNumber))
{
error = string.Format("系统不存该客户物料号：{0}", dr["客户物料号"]);
return(false);
}
}
else
{
sql = string.Format(@"
select count(*) from V_MachineQuoteDetail_Report
where 客户产成品编号='{0}'
and 版本='{1}'
and isone='是'
and 客户名称='{2}'
and 报价时间='{3}'
group by 客户产成品编号,版本,客户名称,报价时间"
, dr["图纸号"], version, customerName, bjTime);
string tempcount = SqlHelper.GetScalar(sql);
//出现报价时间相同的报价单【此处进行异常数据处理】
if (!string.IsNullOrEmpty(tempcount) && Convert.ToInt32(tempcount) > 1)
{
bjTime = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd");
}
}
List <string> sqls = new List <string>();
//S# 阶层 图纸号 BAC物料号 客户物料号 版本
//物料描述 BOM用量 库存数量（理论值） 原材料单价（未税）
//原材料采购单价 工时费 利润（未税） 管销研费用（未税）
//损耗（未税） 单价（未税） 固定提前期 备注 是否第一级 是否是原材料
// sql = string.Format(@"
//select COUNT(*) from MachineQuoteDetail where QuoteNumber='{0}' and CustomerProductNumber='{1}'
//", quoteNumber, dr["图纸号"]);
// if (!SqlHelper.GetScalar(sql).Equals("0"))
// {
// sql = string.Format(@"
//delete MachineQuoteDetail where QuoteNumber='{0}' and CustomerProductNumber='{1}' ", quoteNumber, dr["图纸号"]);
// sqls.Add(sql);
// }
// 客户编号 客户联系人 报价时间
sql = string.Format(@"insert into [T_MachineQuoteDetail_Temp] (
SS,Hierarchy,CustomerProductNumber,BACNumber,CustomerMaterialNumber,
Version,Description ,BOMAmount
,MaterialPrcie,TimeCharge,Profit,ManagementPrcie
,LossPrcie,UnitPrice,FixedLeadTime,Remark ,ProductType,IsOne,IsMaril,ProductNumber,MaterialNumber,UserId,CustomerId,ContactId,QuteTime)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',{8},{9},{10},{11},{12},{13},'{14}','{15}',
'{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}')"
, dr["S#"], dr["阶层"], dr["图纸号"], dr["BAC物料号"], dr["客户物料号"], version,
dr["物料描述"], dr["BOM用量"].ToString().Equals("") ? "1" : dr["BOM用量"],
SrConvert(dr["原材料单价（未税）"]), SrConvert(dr["工时费（未税）"]), SrConvert(dr["利润（未税）"]),
SrConvert(dr["管销研费用（未税）"]),
SrConvert(dr["损耗（未税）"]), dr["单价（未税）"], dr["固定提前期"], dr["备注"], "", dr["是否第一级"], dr["是否是原材料"],
productNumber, materiNumber, userId, dr["客户编号"], dr["客户联系人"], bjTime);
sqls.Add(sql);
// if (dr["是否是原材料"].ToString().Equals("否"))
// {
// string productNumber = dt.Rows[0]["ProductNumber"].ToString();
// string version = dt.Rows[0]["Version"].ToString();
// sql = string.Format(@"
// update Product set SalesQuotation={2} where ProductNumber ='{0}' and Version ='{1}'", productNumber, version, dr["单价（未税）"]);
// sqls.Add(sql);
// }
return(SqlHelper.ExecuteSql(sql, ref error));
}
/// <summary>
/// 删除原材料图纸属性
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public static string DeleteData(string ids)
{
string sql = string.Format(@" delete MaterialBlueprintProperty where guid in ({0}) ", ids);
return(SqlHelper.ExecuteSql(sql, ref error) == true ? "1" : error);
}
public void Delete(string guid)
{
string sql = string.Format(@"delete ProductWarehouseLogDetail where guid='{0}'", guid);
SqlHelper.ExecuteSql(sql);
}
/// <summary>
/// 删除产成品客户属性
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public static string DeleteData(string ids)
{
sql = string.Format(@" delete ProductCustomerProperty where guid in ({0}) ", ids);
return(SqlHelper.ExecuteSql(sql, ref error) == true ? "1" : error);
}
/// <summary>
/// 删除付款方式
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public static string DeleteData(string ids)
{
sql = string.Format(@" delete PaymentMode where Id in ('{0}') ", ids);
return(SqlHelper.ExecuteSql(sql, ref error) == true ? "1" : error);
}
/// <summary>
/// 编辑付款方式
/// </summary>
/// <param name="user"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool EditPaymentMode(PaymentModes paymentmode, ref string error)
{
sql = string.Format(@" update PaymentMode set PaymentMode='{0}' where Id='{1}'", paymentmode.PaymentMode, paymentmode.Id);
return(SqlHelper.ExecuteSql(sql, ref error));
}
/// <summary>
/// 编辑收款方式
/// </summary>
/// <param name="user"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool EditMakeCollectionsMode(MakeCollectionsModes makecollectionsmode, ref string error)
{
sql = string.Format(@" update MakeCollectionsMode set MakeCollectionsMode='{0}' where Id='{1}'", makecollectionsmode.MakeCollectionsMode, makecollectionsmode.Id);
return(SqlHelper.ExecuteSql(sql, ref error));
}
/// <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);
}
public static bool AddQuoteInfoTradingForListory(DataRow dr, ref string error, string userId)
{
string sql = string.Empty;
string materialNumber = string.Empty;
if (!dr["客户编号"].ToString().Equals(""))
{
sql = string.Format(@"select COUNT(*) from Customer where CustomerId ='{0}'", dr["客户编号"]);
if (SqlHelper.GetScalar(sql).Equals("0"))
{
error = string.Format("系统不存在该客户编号:{0}", dr["客户编号"]);
return(false);
}
}
if (dr.Table.Columns.Contains("供应商编号") && !dr["供应商编号"].ToString().Equals(""))
{
sql = string.Format(" select COUNT(*) from SupplierInfo where SupplierId ='{0}' ", dr["供应商编号"]);
if (SqlHelper.GetScalar(sql).Equals("0"))
{
error = string.Format("系统不存在该供应商编号:{0}", dr["供应商编号"]);
return(false);
}
}
if (!dr["供应商物料编号"].ToString().Equals(""))
{
sql = string.Format(@" select COUNT (*) from
MaterialSupplierProperty where SupplierMaterialNumber='{0}' and SupplierId ='{1}'", dr["供应商物料编号"], dr["供应商编号"]);
if (SqlHelper.GetScalar(sql).Equals("0"))
{
error = string.Format("系统不存在该供应商{0}的供应商物料编号{1}", dr["供应商编号"], dr["供应商物料编号"]);
return(false);
}
}
if (!dr["客户物料编号"].ToString().Equals(""))
{
sql = string.Format(@"
select MaterialNumber from MaterialCustomerProperty where CustomerMaterialNumber='{0}' and CustomerId ='{1}' ", dr["客户物料编号"], dr["客户编号"]);
materialNumber = SqlHelper.GetScalar(sql);
if (string.IsNullOrEmpty(materialNumber))
{
error = string.Format("系统不存在该客户{0}的客户物料编号{1}", dr["客户编号"], dr["客户物料编号"]);
return(false);
}
}
string quteDate = string.Empty;
try
{
quteDate = Convert.ToDateTime(dr["报价日期"]).ToString("yyyy-MM-dd");
}
catch (Exception ex)
{
error = string.Format("报价日期：{0}不是标准的日期格式'yyyy-MM-dd'", dr["报价日期"]);
return(false);
}
sql = string.Format(@"
insert into T_TradingQuoteDetail_Temp (MaterialNumber,CustomerMaterialNumber,FixedLeadTime
,UnitPrice,MinPackage,MinMOQ,MaterialDescription,QuoteTime,CustomerId,ContactId,UserId,DW,Remark,SupplierMaterialNumber)
values('{0}','{1}','{2}',{3},'{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}') ", materialNumber, dr["客户物料编号"],
dr["交货周期"], dr["单价（未税）"].ToString().Replace('*', ' '), dr["最小包装"], dr["最小起订量"], dr["描述"], quteDate, dr["客户编号"],
dr["客户联系人"], userId, dr["单位"], dr["备注"], dr["供应商物料编号"]);
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 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="user"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool EditMarerialKind(MarerialKind mareialkind, string kind, ref string error)
{
sql = string.Format(@" update MarerialKind set Kind='{0}' where Kind='{1}' ", mareialkind.Kind, kind);
return(SqlHelper.ExecuteSql(sql, ref error));
}
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));
}
public static bool AddQuoteInfoMachine(DataRow dr, string quoteNumber, ref string error)
{
string version = dr["版本"].ToString().ToUpper();
if (string.IsNullOrEmpty(version))
{
version = "WU";
}
if (version.Equals("OO"))
{
version = "00";
}
string sql = string.Format(@" select ProductNumber from ProductCustomerProperty where CustomerProductNumber ='{0}' and version='{1}'"
, dr["图纸号"], version);
string productNumber = SqlHelper.GetScalar(sql);
if (string.IsNullOrEmpty(productNumber))
{
error = string.Format("系统不存在该图纸号，请先录入该图纸号:{0},版本{1}", dr["图纸号"], version);
return(false);
}
string materiNumber = string.Empty;
if (dr["是否是原材料"].ToString().Equals("是"))
{
sql = string.Format(@"select MaterialNumber from MaterialCustomerProperty where CustomerMaterialNumber='{0}'", dr["客户物料号"]);
materiNumber = SqlHelper.GetScalar(sql);
if (string.IsNullOrEmpty(materiNumber))
{
error = string.Format("系统不存该客户物料号：{0}", dr["客户物料号"]);
return(false);
}
}
List <string> sqls = new List <string>();
//S# 阶层 图纸号 BAC物料号 客户物料号 版本
//物料描述 BOM用量 库存数量（理论值） 原材料单价（未税）
//原材料采购单价 工时费 利润（未税） 管销研费用（未税）
//损耗（未税） 单价（未税） 固定提前期 备注 是否第一级 是否是原材料
// sql = string.Format(@"
//select COUNT(*) from MachineQuoteDetail where QuoteNumber='{0}' and CustomerProductNumber='{1}'
//", quoteNumber, dr["图纸号"]);
// if (!SqlHelper.GetScalar(sql).Equals("0"))
// {
// sql = string.Format(@"
//delete MachineQuoteDetail where QuoteNumber='{0}' and CustomerProductNumber='{1}' ", quoteNumber, dr["图纸号"]);
// sqls.Add(sql);
// }
sql = string.Format(@"insert into MachineQuoteDetail (QuoteNumber ,
SS,Hierarchy,CustomerProductNumber,BACNumber,CustomerMaterialNumber,
Version,Description ,BOMAmount
,MaterialPrcie,TimeCharge,Profit,ManagementPrcie
,LossPrcie,UnitPrice,FixedLeadTime,Remark ,ProductType,IsOne,IsMaril,ProductNumber,MaterialNumber)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',{8},{9},{10},{11},{12},{13},{14},'{15}','{16}','{17}','{18}','{19}','{20}','{21}')"
, quoteNumber, dr["S#"], dr["阶层"], dr["图纸号"], dr["BAC物料号"], dr["客户物料号"], version,
dr["物料描述"], dr["BOM用量"],
SrConvert(dr["原材料单价（未税）"]), SrConvert(dr["工时费"]), SrConvert(dr["利润（未税）"]),
SrConvert(dr["管销研费用（未税）"]),
SrConvert(dr["损耗（未税）"]), dr["单价（未税）"], dr["固定提前期"], dr["备注"], "", dr["是否第一级"], dr["是否是原材料"], productNumber, materiNumber);
sqls.Add(sql);
// if (dr["是否是原材料"].ToString().Equals("否"))
// {
// string productNumber = dt.Rows[0]["ProductNumber"].ToString();
// string version = dt.Rows[0]["Version"].ToString();
// sql = string.Format(@"
// update Product set SalesQuotation={2} where ProductNumber ='{0}' and Version ='{1}'", productNumber, version, dr["单价（未税）"]);
// sqls.Add(sql);
// }
return(SqlHelper.ExecuteSql(sql, ref error));
}
