/// <summary>
/// 根据客户物料编号和订单Id获取原材料编号
/// </summary>
/// <param name="customerMaterialNumber"></param>
/// <param name="customerId"></param>
/// <returns></returns>
public static string GetMaterialNumberByOderNumberAndCustomerMN(string customerMaterialNumber, string saleOderNumber)
{
string customerId = SaleOderManager.GetCustomerIdByOrdersNumber(saleOderNumber);
sql = string.Format(@"select top 1 MaterialNumber from MaterialCustomerProperty where CustomerMaterialNumber='{0}' and CustomerId='{1}' ", customerMaterialNumber, customerId);
return(SqlHelper.GetScalar(sql));
}
/// <summary>
/// 根据客户产成品编号找出最新版本号
/// </summary>
/// <param name="customerProductNumber"></param>
/// <returns></returns>
public static string GetMostNewVersion(string customerProductNumber)
{
string version = string.Empty;
//先从不是字母的里面找【就是全是数字的】
string sql = string.Format(@" SELECT top 1 Version FROM ProductCustomerProperty pcp
WHERE pcp.Version COLLATE Chinese_PRC_CS_AS
not like '%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%' and CustomerProductNumber ='{0}' and Version !='WU' order by Version desc
", customerProductNumber);
version = SqlHelper.GetScalar(sql);
if (string.IsNullOrEmpty(version)) //如果没有数字的就从字母里面找
{
sql = string.Format(@"
SELECT top 1 Version FROM ProductCustomerProperty pcp
WHERE pcp.Version COLLATE Chinese_PRC_CS_AS
like '%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%' and CustomerProductNumber ='{0}' and Version !='WU' order by Version desc
", customerProductNumber);
version = SqlHelper.GetScalar(sql);
if (string.IsNullOrEmpty(version)) //字母和数字都没有就是“WU”
{
version = "WU";
}
}
return(version);
}
/// <summary>
/// 判断是否存在该采购订单明细
/// </summary>
/// <param name="certificateOrders"></param>
/// <param name="materialNumber"></param>
/// <param name="leadTime"></param>
/// <returns></returns>
public static bool IsExitForCertificateOrdersDetail(string ordersNumber, string materialNumber, string leadTime)
{
string sql = string.Format(@" select COUNT (*) from CertificateOrdersDetail
where OrdersNumber ='{0}' and MaterialNumber ='{1}' and LeadTime ='{2}' ", ordersNumber, materialNumber, leadTime);
return(SqlHelper.GetScalar(sql).Equals("0") ? false : true);
}
/// <summary>
/// 不合格品上报单条添加
/// </summary>
/// <param name="dr"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool AddProjectInfo(DataRow dr, ref string error)
{
string version = dr["版本"].ToString().ToUpper();
if (version.Equals(""))
{
version = "WU";
}
string sql = string.Format(@"select ProductNumber
from ProductCustomerProperty where CustomerProductNumber ='{0}' and Version='{1}'", dr["客户产成品编号"], version);
DataTable dt = SqlHelper.GetTable(sql);
if (dt.Rows.Count == 0)
{
error = string.Format("系统不存在该客户产成品编号:{0},版本{1}", dr["客户产成品编号"], version);
return(false);
}
string productNumber = dt.Rows[0]["ProductNumber"].ToString();
sql = string.Format(@"select COUNT(*) from T_ProjectInfo where ProjectName ='{0}'
and ProductNumber ='{1}' and Version ='{2}' and CustomerProductNumber ='{3}' and Hierarchy ='{4}'", dr["项目名称"], productNumber, version, dr["客户产成品编号"], dr["阶层"]);
if (!SqlHelper.GetScalar(sql).Equals("0"))
{
error = string.Format("已存在相同记录，请重新导入！");
return(false);
}
sql = string.Format(@"insert into T_ProjectInfo (ProjectName,ProductNumber,Version ,CustomerProductNumber,Hierarchy ,Description ,Single,Remark )
select '{0}','{1}','{2}','{3}', '{6}',Description as 描述,'{4}','{5}' from Product
where ProductNumber ='{1}' and Version ='{2}'", dr["项目名称"], productNumber, version, dr["客户产成品编号"], dr["单机"], dr["备注"], dr["阶层"]);
return(SqlHelper.ExecuteSql(sql, ref error));
}
/// <summary>
/// 根据订单ID获取客户ID
/// </summary>
/// <param name="ordersNumber"></param>
/// <returns></returns>
public static string GetCustomerIdByOrdersNumber(string ordersNumber)
{
string CustomerId = string.Empty;
string sql = string.Format(@"
select CustomerId from SaleOder where OdersNumber='{0}'", ordersNumber);
return(SqlHelper.GetScalar(sql));
}
public string GetTypeName(string warehouseNumber)
{
string sql = string.Format(@"
select type from ProductWarehouseLog where warehousenumber='{0}'
", warehouseNumber);
return(SqlHelper.GetScalar(sql));
}
public bool CanAutior(string warehouseNumber)
{
string sql = string.Format(@"
select CheckTime from ProductWarehouseLog where warehousenumber='{0}'
", warehouseNumber);
return(string.IsNullOrEmpty(SqlHelper.GetScalar(sql)));
}
/// <summary>
/// 添加加工销售订单明细
/// </summary>
/// <param name="mod"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool AddMachineOderDetail(MachineOderDetail mod, ref string error)
{
try
{
Convert.ToDateTime(mod.LeadTime);
}
catch (Exception)
{
error = string.Format("交期{0}不是标准日期格式 yyyy-MM-dd ", mod.LeadTime);
return(false);
}
if (string.IsNullOrEmpty(mod.OrdersNumber) || string.IsNullOrEmpty(mod.RowNumber) ||
string.IsNullOrEmpty(mod.SN) || string.IsNullOrEmpty(mod.LeadTime) ||
string.IsNullOrEmpty(mod.CustomerProductNumber) || string.IsNullOrEmpty(mod.Qty) ||
string.IsNullOrEmpty(mod.CustomerId))
{
error = string.Format("信息填写不完整！");
return(false);
}
string sql = string.Format(@" select ProductNumber from ProductCustomerProperty
where CustomerProductNumber ='{0}' and CustomerId='{1}' and Version='{2}' ", mod.CustomerProductNumber, mod.CustomerId, mod.Version);
DataTable dt = SqlHelper.GetTable(sql);
if (dt.Rows.Count == 0)
{
error = string.Format("系统不存在该客户产成品编号：{0},版本：{1}", mod.CustomerProductNumber, mod.Version);
return(false);
}
string productNumber = dt.Rows[0]["ProductNumber"].ToString();
string version = mod.Version;
sql = string.Format(@" select COUNT (*) from MachineOderDetail where OdersNumber ='{0}' and ProductNumber ='{1}' and Version ='{2}'
and RowNumber ='{3}'", mod.OrdersNumber, productNumber, version, mod.RowNumber);
if (!SqlHelper.GetScalar(sql).Equals("0"))
{
error = string.Format("订单记录已存在");
return(false);
}
List <string> sqls = new List <string>();
sql = string.Format(@" select UnitPrice from V_FindPriceForNew
where ProductNumber ='{0}' and Version ='{1}' and CustomerId='{2}' ", productNumber, version, mod.CustomerId);
string salesQuotation = SqlHelper.GetScalar(sql);
salesQuotation = string.IsNullOrEmpty(salesQuotation) ? "0" : salesQuotation;
sql = string.Format(@" insert into MachineOderDetail (OdersNumber ,ProductNumber ,Version ,LeadTime ,RowNumber ,SN ,CustomerProductNumber ,Qty
,NonDeliveryQty ,DeliveryQty ,UnitPrice ,SumPrice ,CreateTime,remark )
select '{0}',ProductNumber,Version ,'{1}','{2}',{3},'{4}',{5},{5},0,{10},{10}*{5},'{6}','{9}'
from Product where ProductNumber ='{7}' and Version ='{8}'", mod.OrdersNumber, mod.LeadTime, mod.RowNumber, mod.SN, mod.CustomerProductNumber, mod.Qty
, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), productNumber, version, mod.Remark, salesQuotation);
return(SqlHelper.ExecuteSql(sql, ref error));
}
// select ProductNumber ,Version from ProductCustomerProperty where CustomerProductNumber=''
public static bool AddWorkSn(DataRow dr, ref string error)
{
string columName = string.Empty;
string version = dr["版本"].ToString().Trim().Equals("") ? "WU" : dr["版本"].ToString().ToUpper();
string snNumber = string.Empty;
string sn = string.Empty;
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);
}
List <string> sqls = new List <string>();
sql = string.Format("select WorkSnNumber,WorkSnName,Sn from WorkSn");
foreach (DataRow drTemp in SqlHelper.GetTable(sql).Rows)
{
columName = drTemp["WorkSnName"].ToString();
if (dr.Table.Columns.Contains(columName) && !dr[columName].ToString().Trim().Equals(""))//如果有工时
{
// sql = string.Format(@"select COUNT(*) from ProductWorkSnProperty
//where ProductNumber='{0}' and Version ='{1}' and WorkSnNumber='{2}'", productNumber, version, drTemp["WorkSnNumber"].ToString());
// if (SqlHelper.GetScalar(sql).Equals("0"))
// {
// sqls.Add(GetInsertSqlForWorkSn(productNumber, version, drTemp["WorkSnNumber"].ToString(), drTemp["Sn"].ToString(), dr[columName].ToString()));
// }
//将原有数据库删除
sql = string.Format(@"delete ProductWorkSnProperty
where ProductNumber='{0}' and Version ='{1}' and WorkSnNumber='{2}'", productNumber, version, drTemp["WorkSnNumber"].ToString());
sqls.Add(sql);
sqls.Add(GetInsertSqlForWorkSn(productNumber, version, drTemp["WorkSnNumber"].ToString(), drTemp["Sn"].ToString(), dr[columName].ToString()));
}
}
bool result = SqlHelper.BatchExecuteSql(sqls, ref error);
string temp = string.Empty;
if (!result)
{
foreach (string str in sqls)
{
temp += string.Format("<br/>{0}", str);
}
error += string.Format("具体执行语句：{0},图纸号：{1},版本：{2}", temp, dr["图纸号"], version);
}
return(result);
}
/// <summary>
/// 检测当前库存数量是否满足出库数量(原材料出入库)
/// </summary>
/// <param name="warehouseNumber"></param>
/// <returns></returns>
public static bool CheckInventoryQty(string warehouseNumber)
{
string sql = 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})
group by mwld.MaterialNumber,mwl.WarehouseName ) t left join MaterialStock ms on t.MaterialNumber=ms.MaterialNumber ) t where 差<0", warehouseNumber);
return(SqlHelper.GetScalar(sql).Equals("0") ? true : false);
}
/// <summary>
/// 检查某产品是否有BOM
/// </summary>
/// <param name="productNumber"></param>
/// <param name="version"></param>
/// <returns></returns>
public static bool HasBOM(string productNumber, string version)
{
string sql = string.Format(@"
select SUM(number) from(
select COUNT(0) as number from BOMInfo where ProductNumber = '{0}' and Version = '{1}'
union all
select COUNT(0) from PackageAndProductRelation where PackageNumber = '{0}'
)t
", productNumber, version);
return(SqlHelper.GetScalar(sql).Equals("0") ? false : true);
}
public static bool AddExaminationLog(string year, string month, DataRow dr, ref string error)
{
string sql = string.Format(" select count(*) from pm_user where user_name='{0}' ", dr["姓名"]);
if (SqlHelper.GetScalar(sql).Equals("0"))
{
error = string.Format("系统不存在该姓名：{0}", dr["姓名"]);
return(false);
}
sql = string.Format(@"insert into ExaminationLog(Year ,Month ,Name ,Score,Operation,TotalScore )
values('{0}','{1}','{2}',{3},{4},{3}+{4})", year, month, dr["姓名"], dr["笔试得分"], dr["实操得分"]);
return(SqlHelper.ExecuteSql(sql, ref error));
}
/// <summary>
/// 添加贸易销售订单明细
/// </summary>
/// <param name="tod">实体类</param>
/// <param name="error"></param>
/// <returns></returns>
public static bool AddTradingOrderDetail(TradingOrderDetail tod, ref string error)
{
try
{
Convert.ToDateTime(tod.Delivery);
}
catch (Exception)
{
error = string.Format("交期{0}不是标准日期格式 yyyy-MM-dd ", tod.Delivery);
return(false);
}
string sql = string.Format(@"select top 1 MaterialNumber
from MaterialCustomerProperty where CustomerMaterialNumber='{0}' and CustomerId='{1}'", tod.CustomerMaterialNumber, tod.CustomerId);
string materialNumber = SqlHelper.GetScalar(sql);
if (string.IsNullOrEmpty(materialNumber))
{
error = string.Format("系统不存在该客户原材料编号{0}", tod.CustomerMaterialNumber);
return(false);
}
if (string.IsNullOrEmpty(tod.OrdersNumber) || string.IsNullOrEmpty(tod.CustomerMaterialNumber) ||
string.IsNullOrEmpty(tod.RowNumber) || string.IsNullOrEmpty(tod.Delivery) ||
string.IsNullOrEmpty(tod.SN) || string.IsNullOrEmpty(tod.Quantity))
{
error = string.Format("信息填写不完整！");
return(false);
}
sql = string.Format(@" select COUNT(*) from TradingOrderDetail where OdersNumber ='{0}' and ProductNumber ='{1}' and RowNumber ='{2}'"
, tod.OrdersNumber, tod.ProductNumber, tod.RowNumber);
if (!SqlHelper.GetScalar(sql).Equals("0"))
{
error = string.Format("订单记录已存在");
return(false);
}
List <string> sqls = new List <string>();
sql = string.Format(@"
select 单价 from V_FindPriceForNewForTrading where 原材料编号='{0}' and 客户编号='{1}' ", materialNumber, tod.CustomerId);
string unitPrice = SqlHelper.GetScalar(sql);
unitPrice = string.IsNullOrEmpty(unitPrice) ? "0" : unitPrice;
sql = string.Format(@" insert into TradingOrderDetail(OdersNumber ,ProductNumber,RowNumber ,Delivery ,SN,CustomerMaterialNumber,
MaterialName ,Brand ,Quantity ,NonDeliveryQty ,DeliveryQty ,UnitPrice ,TotalPrice ,CreateTime ,Remark )
select '{0}',MaterialNumber,'{1}','{2}',{3},'{4}',Description ,Brand ,{5},{5},0,{9},{9}*{5},'{6}','{8}'
from MarerialInfoTable where MaterialNumber ='{7}'", tod.OrdersNumber, tod.RowNumber, tod.Delivery, tod.SN, tod.CustomerMaterialNumber,
tod.Quantity, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), materialNumber, tod.Remark, unitPrice);
sqls.Add(sql);
return(SqlHelper.BatchExecuteSql(sqls, ref error));
}
/// <summary>
/// 编辑员工考试成绩信息
/// </summary>
/// <param name="user"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool EditExaminationLog(ExaminationLog examinationlog, ref string error)
{
sql = string.Format(@" select user_id from pm_user where user_name='{0}'", examinationlog.Name);
string name = SqlHelper.GetScalar(sql);
sql = string.Format(@" update ExaminationLog set Score='{0}',
LeaderScore='{1}',TotalScore='{2}',Operation='{3}',WorkAttendance='{4}',WorkState='{5}',
Teamwork='{6}',RejectsProduct='{7}',Security='{8}',Remark='{9}' where year='{10}' and month='{11}' and name='{12}' ",
examinationlog.Score, examinationlog.LeaderScore,
examinationlog.TotalScore, examinationlog.Operation, examinationlog.WorkAttendance, examinationlog.WorkState,
examinationlog.Teamwork, examinationlog.RejectsProduct, examinationlog.Security, examinationlog.Remark,
examinationlog.Year, examinationlog.Month, name);
return(SqlHelper.ExecuteSql(sql, ref error));
}
public double GetWeiQueRenSongHuoDan(string materialNumber)
{
string sql = string.Format(@"
select t.qty from (
select ProductNumber AS MaterialNumber, 0-sum(isnull( DeliveryQty,0)) as qty from [DeliveryNoteDetailed]
where DeliveryNumber in (select distinct DeliveryNumber from [DeliveryBill] where IsConfirm !='已确认')
and ISNULL(Version,'')=''
group by ProductNumber)t
where t.MaterialNumber='{0}'
", materialNumber);
string ob = SqlHelper.GetScalar(sql);
return(string.IsNullOrEmpty(ob) ? 0 : Convert.ToDouble(ob));
}
/// <summary>
/// 添加产成品基本信息
/// </summary>
/// <param name="dr"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool AddProduct(DataRow dr, ref string error)
{
string version = dr["版本"].ToString().Trim().Equals("") ? "WU" : dr["版本"].ToString().ToUpper();
if (version.Equals("OO"))
{
version = "00";
}
List <string> sqls = new List <string>();
//客户产成品编号（图纸号） 客户编号 产成品编号(自己的号) 版本 型号 成品类别 描述
string sql = string.Format(@"
select count(*) from ProductCustomerProperty where CustomerProductNumber ='{0}' and CustomerId='{1}' and Version='{2}' ", dr["客户产成品编号（图纸号）"], dr["客户编号"], version);
if (!SqlHelper.GetScalar(sql).Equals("0"))
{
error = string.Format("系统已存在该图纸号：{0},版本：{1}", dr["客户产成品编号（图纸号）"], version);
return(false);
}
sql = string.Format(@"
select count(0) from ProductCustomerProperty where ProductNumber ='{0}' and Version ='{1}' and CustomerProductNumber='{2}'
", dr["产成品编号(自己的号)"], version, dr["客户产成品编号（图纸号）"]);
if (SqlHelper.GetScalar(sql) != "0")
{
error = string.Format("系统已存在该图纸号：{0} ", dr["客户产成品编号（图纸号）"]);
return(false);
}
sql = string.Format(" select COUNT(*) from Product where ProductNumber ='{0}' and Version ='{1}' ", dr["产成品编号(自己的号)"], version);
if (SqlHelper.GetScalar(sql).Equals("0"))
{
sql = string.Format(@" insert into Product (ProductNumber,Version,ProductName ,Type,Description )
values('{0}','{1}','{2}','{3}','{4}')", dr["产成品编号(自己的号)"], version, dr["型号"], dr["成品类别"], dr["描述"]);
sqls.Add(sql);
}
sql = string.Format(@"
insert into ProductCustomerProperty(ProductNumber ,Version ,CustomerProductNumber,CustomerId )
values('{0}','{1}','{2}','{3}')", dr["产成品编号(自己的号)"], version, dr["客户产成品编号（图纸号）"], dr["客户编号"]);
sqls.Add(sql);
sql = string.Format(" select COUNT(*) from ProductStock where ProductNumber ='{0}' and Version ='{1}' "
, dr["产成品编号(自己的号)"], version);
if (SqlHelper.GetScalar(sql).Equals("0"))
{
sql = string.Format(@" insert into ProductStock (ProductNumber ,Version ,WarehouseName ,StockQty ,UpdateTime )
values('{0}','{1}','cpk',0,'{2}')", dr["产成品编号(自己的号)"], version, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
sqls.Add(sql);
}
return(SqlHelper.BatchExecuteSql(sqls, ref error));
}
/// <summary>
/// 不合格品上报单条添加
/// </summary>
/// <param name="dr"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool AddRejectsProduct(DataRow dr, ref string error)
{
string version = dr["版本"].ToString().Equals("") ? "WU" : dr["版本"].ToString().ToUpper();
string sql = string.Format(@"select ProductNumber ,Version
from ProductCustomerProperty where CustomerProductNumber ='{0}' and Version='{1}'", dr["客户产成品编号"], version);
DataTable dt = SqlHelper.GetTable(sql);
if (dt.Rows.Count == 0)
{
error = string.Format("系统不存在该客户产成品编号:{0},版本:{1}", dr["客户产成品编号"], version);
return(false);
}
sql = string.Format(@"select COUNT (*) from PM_USER where USER_NAME ='{0}'", dr["姓名"]);
if (SqlHelper.GetScalar(sql).Equals("0"))
{
error = string.Format("系统不存在该职工：{0}", dr["姓名"]);
return(false);
}
sql = string.Format(@"select COUNT (*) from PM_USER where Team ='{0}'", dr["班组"].ToString().ToUpper());
if (SqlHelper.GetScalar(sql).Equals("0"))
{
error = string.Format("系统不存在该班组：{0}", dr["班组"].ToString().ToUpper());
return(false);
}
string productNumber = dt.Rows[0]["ProductNumber"].ToString();
//version = dt.Rows[0]["Version"].ToString();
string fxDate = dr["返修日期"].ToString();
string xhDate = dr["修回检验日期"].ToString();
try
{
fxDate = Convert.ToDateTime(fxDate).ToString("yyyy-MM-dd");
xhDate = Convert.ToDateTime(xhDate).ToString("yyyy-MM-dd");
}
catch (Exception ex)
{
error = string.Format("日期不是正确的yyyy-MM-dd格式,返修日期:{0},修回检验日期:{1}", dr["返修日期"].ToString(), dr["修回检验日期"].ToString());;
return(false);
}
sql = string.Format(@"insert into RejectsProduct (ReportTime ,ProductNumber ,Version ,CustomerProductNumber ,
Qty ,RepairReason
,RepairDate,RepairInspectionDate,Name,Team ,Remark ,WhetherBatch)
values('{0}','{1}','{2}','{3}',{4},'{5}','{6}','{7}','{8}','{9}','{10}','{11}')"
, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), productNumber, version, dr["客户产成品编号"], dr["数量"].ToString().Equals("") ? "0" : dr["数量"], dr["返修原因"]
, fxDate, xhDate, dr["姓名"], dr["班组"].ToString().ToUpper(), dr["备注"], dr["是否成批"]);
return(SqlHelper.ExecuteSql(sql, ref error));
}
public double GetMaoYiWeiJiao(string materialNumber)
{
string sql = string.Format(@"
SELECT
SUM(NonDeliveryQty) qty
FROM TradingOrderDetail
WHERE NonDeliveryQty>0
AND OdersNumber IN
(SELECT DISTINCT OdersNumber
FROM SaleOder
WHERE CheckTime IS NOT NULL)
and ProductNumber='{0}'
GROUP BY ProductNumber
", materialNumber);
string ob = SqlHelper.GetScalar(sql);
return(string.IsNullOrEmpty(ob) ? 0 : Convert.ToDouble(ob));
}
public static bool AddQuoteInfoMY(DataRow dr, string quoteNumber, ref string error)
{
string sql = string.Format("select COUNT (*) from MaterialCustomerProperty where CustomerMaterialNumber='{0}' ", dr["客户物料编号"]);
if (SqlHelper.GetScalar(sql).Equals("0"))
{
error = string.Format("系统不存在该客户物料编号：{0}", dr["客户物料编号"]);
return(false);
}
sql = string.Format(@" insert into TradingQuoteDetail(QuoteNumber ,
ProductNumber ,CustomerMaterialNumber ,FixedLeadTime ,UnitPrice ,MinPackage
,MinMOQ ,MaterialDescription ,Brand,DW )
select '{0}',MaterialNumber,'{1}','{2}',{3},'{4}' ,'{5}',Description ,
Brand ,'{6}' from MarerialInfoTable where MaterialNumber=(
select top 1 MaterialNumber from MaterialCustomerProperty where CustomerMaterialNumber='{1}') ",
quoteNumber, dr["客户物料编号"], dr["固定提前期"], dr["单价"].ToString().Equals("") ? "0" : dr["单价"].ToString(),
dr["最小包装"], dr["最小起订量"], dr["单位"]);
return(SqlHelper.ExecuteSql(sql, ref error));
}
/// <summary>
/// 保存报价单
/// </summary>
/// <param name="quteNumber"></param>
/// <param name="quoteType"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool SaveQuteInfo(string quteNumber, string quoteType, ref string error)
{
string quteNumberNew = string.Empty;
if (quteNumber.Contains("_"))
{
string[] temp = quteNumber.Split('_');
quteNumberNew = temp[0] + "_" + (Convert.ToInt32(temp[1]) + 1).ToString();
}
else
{
quteNumberNew = quteNumber + "_1";
}
string sql = string.Format(" select COUNT (*) from QuoteInfo where QuoteNumber='{0}' ", quteNumberNew);
if (!SqlHelper.GetScalar(sql).Equals("0"))
{
error = "该版本报价单已被保存过，请找到最新版的报价单进行修改保存操作！";
return(false);
}
List <string> sqls = new List <string>();
sql = string.Format(@"insert into QuoteInfo
select '{0}',QuoteTime ,QuoteType ,CustomerId,ContactId ,'{1}',
'' from QuoteInfo where QuoteNumber='{2}'", quteNumberNew, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), quteNumber);
sqls.Add(sql);
if (quoteType.Equals("加工报价单"))
{
sql = string.Format(@" insert into MachineQuoteDetail (QuoteNumber,PackageNumber ,ProductNumber ,Version ,CustomerProductNumber
,MaterialNumber,CustomerMaterialNumber,FixedLeadTime ,Hierarchy ,BOMAmount
,MaterialPrcie ,TimeCharge ,Profit ,ManagementPrcie,LossPrcie ,UnitPrice ,Remark
,Description ,SS,BACNumber ,ProductType ,IsOne)
select '{0}',PackageNumber ,ProductNumber ,Version ,CustomerProductNumber
,MaterialNumber,CustomerMaterialNumber,FixedLeadTime ,Hierarchy ,BOMAmount
,MaterialPrcie ,TimeCharge ,Profit ,ManagementPrcie,LossPrcie ,UnitPrice ,Remark
,Description ,SS,BACNumber ,ProductType ,IsOne from MachineQuoteDetail where QuoteNumber='{1}'", quteNumberNew, quteNumber);
sqls.Add(sql);
}
else
{
}
return(SqlHelper.BatchExecuteSql(sqls, ref error));
}
/// <summary>
/// 导入员工考试成绩
/// </summary>
/// <param name="year"></param>
/// <param name="month"></param>
/// <param name="FU_Excel"></param>
/// <param name="server"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool ImpExaminationLog(string year, string month, FileUpload FU_Excel, HttpServerUtility server, ref string error)
{
sql = string.Format("select COUNT(*) from ExaminationLog where YEAR ='{0}' and MONTH ='{1}' ", year, month);
if (!SqlHelper.GetScalar(sql).Equals("0"))
{
error = string.Format("已上报过{0}年{1}月的考试成绩", year, month);
return(false);
}
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 (!AddExaminationLog(year, month, 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);
}
return(result);
}
/// <summary>
/// 审核采购订单
/// </summary>
/// <param name="OrdersNumbers">订单编号集合</param>
/// <param name="userNumber">当前登录用户k</param>
/// <returns></returns>
public static string AuditorPurchase(string OrdersNumbers, string userNumber)
{
string error = string.Empty;
List <string> sqls = new List <string>();
string sql = string.Format(@" update CertificateOrders set CheckTime ='{0}' ,Auditor ='{1}'
where OrdersNumber in({2}) "
, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), userNumber, OrdersNumbers);
sqls.Add(sql);
string[] numbers = OrdersNumbers.Split(',');
foreach (string number in numbers)
{
sql = string.Format(" select PaymentMode from CertificateOrders where OrdersNumber ={0} ", number);
sql = GeneratingCopeSql(SqlHelper.GetScalar(sql), number); //获取产生应付的sql语句
if (!string.IsNullOrEmpty(sql))
{
sqls.Add(sql);
}
}
return(SqlHelper.BatchExecuteSql(sqls, ref error) ? "1" : error);
}
public void changeproduct(string productnumber, string version)
{
//成本价
sql = string.Format(@" select isnull(CONVERT(decimal(18,2), sum(b.SingleDose*mt.ProcurementPrice)),0) as 成本价 from Product p
left join BOMInfo b on p.ProductNumber=b.ProductNumber and p.Version=b.Version
left join MarerialInfoTable mt on b.MaterialNumber=mt.MaterialNumber
where b.ProductNumber='{0}' and b.Version='{1}'", productnumber, version);
string costprice = SqlHelper.GetScalar(sql);
//额定工时
sql = string.Format(@" select ISNULL(sum(pwp.RatedManhour),0) as 额定工时 from Product p left join
ProductWorkSnProperty pwp on p.ProductNumber=pwp.ProductNumber
and p.Version=pwp.Version where pwp.ProductNumber='{0}' and pwp.Version='{1}'", productnumber, version);
string ratedmanhour = SqlHelper.GetScalar(sql);
if (costprice != null && costprice != "" && ratedmanhour != null && ratedmanhour != "")
{
sql = string.Format(@" update Product set RatedManhour='{0}',CostPrice='{1}'
where ProductNumber='{2}' and Version='{3}'", ratedmanhour, costprice, productnumber, version);
SqlHelper.ExecuteSql(sql, ref error);
}
}
/// <summary>
/// 导入采购订单明细
/// </summary>
/// <param name="dr"></param>
/// <param name="ordersNumber"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool AddCertificateOrdersDetail(DataRow dr, string ordersNumber, ref string error)
{
string leadTime = string.Empty;
try
{
leadTime = Convert.ToDateTime(dr["交期"]).ToString("yyyy-MM-dd");
}
catch (Exception ex)
{
error = string.Format(" 此交期{0}不是正规的日期格式，日期格式应为'yyyy-MM-dd'", dr["交期"]);
return(false);
}
//序号 供应商物料编号 交期 数量
string sql = string.Format(@" select MaterialNumber from MaterialSupplierProperty where SupplierMaterialNumber='{0}'", dr["供应商物料编号"]);
string materialNumber = SqlHelper.GetScalar(sql);
if (string.IsNullOrEmpty(materialNumber))
{
error = string.Format(" 系统不存在该供应商物料编号：{0} ", dr["供应商物料编号"]);;
return(false);
}
sql = string.Format(@"select COUNT(*) from CertificateOrdersDetail
where OrdersNumber ='{0}' and MaterialNumber='{1}' and LeadTime =convert(varchar,CAST( '{2}' as date),120) ",
ordersNumber, materialNumber, leadTime);
if (!SqlHelper.GetScalar(sql).Equals("0"))
{
error = string.Format("已存在相同记录！采购订单号：{0},供应商物料编号:{1},交期:{2}", ordersNumber, dr["供应商物料编号"], leadTime);
return(false);
}
sql = string.Format(@"insert into CertificateOrdersDetail (OrdersNumber,MaterialNumber ,
LeadTime ,RowNumber ,SupplierMaterialNumber ,OrderQty ,
NonDeliveryQty ,DeliveryQty ,UnitPrice ,SumPrice ,Status )
select '{0}','{1}',convert(varchar,CAST( '{2}' as date),120),{3},'{4}',{5},{5},0,ProcurementPrice,{5}*ProcurementPrice,'未完成'
from MarerialInfoTable where MaterialNumber='{1}'", ordersNumber, materialNumber, leadTime, dr["序号"], dr["供应商物料编号"]
, dr["数量"]);
return(SqlHelper.ExecuteSql(sql, ref error));
}
/// <summary>
/// 批量添加原材料新
/// </summary>
/// <param name="marerialinfotable"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool AddMarielToBatch(MarerialInfoTableNew marerialinfotable, ref string error)
{
List <string> sqls = new List <string>();
string sql = string.Format(" select COUNT (*) from MarerialInfoTable where MaterialNumber='{0}' ", marerialinfotable.MaterialNumber);
if (SqlHelper.GetScalar(sql).Equals("0"))
{
sql = string.Format(@" insert into MarerialInfoTable (MaterialNumber,MaterialName,Description,Kind,Type,Brand,StockSafeQty,
ProcurementPrice,MaterialPosition,MinPacking,MinOrderQty,ScrapPosition,Remark,Cargo)
values ('{0}','{1}','{2}','{3}','{4}','{5}',{6},'{7}','{8}','{9}','{10}','{11}','{12}','{13}')",
marerialinfotable.MaterialNumber, marerialinfotable.MaterialName, marerialinfotable.Description, marerialinfotable.Kind,
marerialinfotable.Type, marerialinfotable.Brand, marerialinfotable.StockSafeQty, marerialinfotable.ProcurementPrice,
marerialinfotable.MaterialPosition, marerialinfotable.MinPacking, marerialinfotable.MinOrderQty,
marerialinfotable.ScrapPosition, marerialinfotable.Remark, marerialinfotable.Cargo);
sqls.Add(sql);
sqls.AddRange(GetAddMaterialSupplieAndCustomerSql(marerialinfotable));
}
else
{
sqls.AddRange(GetAddMaterialSupplieAndCustomerSql(marerialinfotable));
}
return(SqlHelper.BatchExecuteSql(sqls, 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));
}
/// <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="dr"></param>
/// <param name="ordersNumber"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool AddCertificateOrdersDetail_New(DataRow dr, string userId, ref string error)
{
string htNumber = dr["采购合同号"].ToString();
string leadTime = string.Empty;
string ordersDate = string.Empty;
string paymentMode = "";
//string ordersNumber = "CG" + DateTime.Now.ToString("yyyyMMddHHmmss");
string materialNumber = string.Empty;
string contactId = string.Empty;
if (string.IsNullOrEmpty(htNumber))
{
error = "采购合同号不能为空";
return(false);
}
try
{
leadTime = Convert.ToDateTime(dr["交期"]).ToString("yyyy-MM-dd");
}
catch (Exception ex)
{
error = string.Format(" 此交期{0}不是正规的日期格式，日期格式应为'yyyy-MM-dd'", dr["交期"]);
return(false);
}
try
{
ordersDate = Convert.ToDateTime(dr["采购订单日期"]).ToString("yyyy-MM-dd");
}
catch (Exception ex)
{
error = string.Format(" 采购订单日期{0}不是正规的日期格式，日期格式应为'yyyy-MM-dd'", dr["采购订单日期"]);
return(false);
}
//检查是否有该付款方式
//string sql = string.Format(@" select id from PaymentMode where PaymentMode='{0}' ", paymentMode);
//paymentMode = SqlHelper.GetScalar(sql);
//if (string.IsNullOrEmpty(paymentMode))
//{
// error = string.Format("系统不存在该付款方式:{0}", dr["付款方式"]);
// return false;
//}
string sql = string.Empty;
sql = string.Format(@"select count(*) from SupplierInfo where SupplierId='{0}'", dr["供应商编号"]);
if (SqlHelper.GetScalar(sql).Equals("0"))
{
error = string.Format("系统不存在该供应商编号:{0}", dr["供应商编号"]);
return(false);
}
sql = string.Format(@" select pm.Id from SupplierInfo si inner join PaymentMode pm on si.PaymentMode=pm.PaymentMode
where si.SupplierId ='{0}'", dr["供应商编号"]);
paymentMode = SqlHelper.GetScalar(sql);
if (string.IsNullOrEmpty(paymentMode))
{
error = string.Format("系统不存在该供应商编号：{0}的付款方式", dr["供应商编号"]);
return(false);
}
sql = string.Format("select USER_ID from PM_USER where USER_NAME='{0}' ", dr["业务员"]);
contactId = SqlHelper.GetScalar(sql);
if (string.IsNullOrEmpty(contactId))
{
error = string.Format("系统不存在该业务员信息：{0}", dr["业务员"]);
return(false);
}
sql = string.Format(@"
select MaterialNumber from MaterialSupplierProperty
where SupplierMaterialNumber='{0}' and SupplierId ='{1}' ", dr["供应商物料编号"], dr["供应商编号"]);
materialNumber = SqlHelper.GetScalar(sql);
if (string.IsNullOrEmpty(materialNumber))
{
error = string.Format("系统不存在该供应商物料编号:{0}", dr["供应商物料编号"]);
return(false);
}
sql = string.Format(@"select COUNT(*) from T_CertificateOrders
where HTNumber ='{0}' and OrdersDate ='{1}' and PaymentMode='{2}' and SupplierId ='{3}'
and ContactId ='{4}' and SupplierMaterialNumber='{5}' and LeadTime ='{6}' and userId='{7}' "
, htNumber, ordersDate, paymentMode, dr["供应商编号"], contactId, dr["供应商物料编号"], leadTime, userId);
if (!SqlHelper.GetScalar(sql).Equals("0"))
{
error = string.Format("已存在相同记录！合同号：{0}，供应商物料编号：{1}，交期：{2}", htNumber, dr["供应商物料编号"], leadTime);
return(false);
}
sql = string.Format(@"select MinOrderQty from MaterialSupplierProperty
where MaterialNumber ='{0}' and SupplierId='{1}'", materialNumber, dr["供应商编号"]);
string minOrderQty = SqlHelper.GetScalar(sql);
sql = string.Format(@"
insert into T_CertificateOrders(HTNumber ,OrdersDate,PaymentMode,SupplierId ,ContactId ,SupplierMaterialNumber,MaterialNumber ,
LeadTime ,OrderQty ,MinOrderQty ,PayOne ,PayTwo,UserId )
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',{8},'{9}',{10},{11},'{12}')
", htNumber, ordersDate, paymentMode, dr["供应商编号"], contactId, dr["供应商物料编号"], materialNumber, leadTime, dr["数量"].ToString().Equals("") ? "0" : dr["数量"],
minOrderQty, dr["付款一"].ToString().Equals("") ? "0" : dr["付款一"], dr["付款二"].ToString().Equals("") ? "0" : dr["付款二"], userId);
return(SqlHelper.ExecuteSql(sql, ref error));
}
/// <summary>
/// 获取采购订单审核状态
/// </summary>
/// <param name="ordersNumber"></param>
/// <returns></returns>
public static bool GetCheckStatus(string ordersNumber)
{
string sql = string.Format(@" select ISNULL (CheckTime ,'未审核') as 审核状态 from CertificateOrders where OrdersNumber ='{0}'", ordersNumber);
return(SqlHelper.GetScalar(sql) == "未审核" ? false : true);
}
/// <summary>
/// 根据客户物料编号和客户Id获取原材料编号
/// </summary>
/// <param name="customerMaterialNumber"></param>
/// <param name="customerId"></param>
/// <returns></returns>
public static string GetMaterialNumber(string customerMaterialNumber, string customerId)
{
sql = string.Format(@"select top 1 MaterialNumber from MaterialCustomerProperty where CustomerMaterialNumber='{0}' and CustomerId='{1}' ", customerMaterialNumber, customerId);
return(SqlHelper.GetScalar(sql));
}