/// <summary>
/// LogicDeleteInfo
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool LogicDeleteInfo(long id, string loginUser)
{
SupplierPartQuotaInfo supplierPartQuotaInfo = dal.GetInfo(id);
if (supplierPartQuotaInfo == null)
{
throw new Exception("MC:0x00000084");///数据错误
}
///生成中间表同步数据
string sql = GetSyncVmiSupplierPartSql(supplierPartQuotaInfo, true, loginUser);
using (TransactionScope trans = new TransactionScope())
{
if (dal.LogicDelete(id, loginUser) == 0)
{
return(false);
}
if (sql.Length > 0)
{
CommonDAL.ExecuteNonQueryBySql(sql);
}
trans.Complete();
}
return(true);
}
/// <summary>
///
/// </summary>
/// <param name="fields"></param>
/// <param name="id"></param>
/// <returns></returns>
public bool UpdateInfo(string fields, long id)
{
SupplierPartQuotaInfo supplierPartQuotaInfo = dal.GetInfo(id);
if (supplierPartQuotaInfo == null)
{
throw new Exception("MC:0x00000084");///数据错误
}
string loginUser = CommonBLL.GetFieldValue(fields, "MODIFY_USER");
///生成中间表同步数据
string sql = GetSyncVmiSupplierPartSql(supplierPartQuotaInfo, false, loginUser);
using (TransactionScope trans = new TransactionScope())
{
if (dal.UpdateInfo(fields, id) == 0)
{
return(false);
}
if (sql.Length > 0)
{
CommonDAL.ExecuteNonQueryBySql(sql);
}
trans.Complete();
}
return(true);
}
/// <summary>
/// InsertInfo
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public long InsertInfo(SupplierPartQuotaInfo info)
{
///物料号①、供应商代码②组合不允许重复，必选项
int cnt = dal.GetCounts("[PART_NO] = N'" + info.PartNo + "' and [SUPPLIER_NUM] = N'" + info.SupplierNum + "'");
if (cnt >= 1)
{
throw new Exception("MC:0x00000097");///同一供应商中物料号不允许重复
}
///生成中间表同步数据
string sql = GetSyncVmiSupplierPartSql(info, false, info.CreateUser);
long id = 0;
using (TransactionScope trans = new TransactionScope())
{
id = dal.Add(info);
if (id > 0 && sql.Length > 0)
{
CommonDAL.ExecuteNonQueryBySql(sql);
}
trans.Complete();
}
return(id);
}
/// <summary>
/// Create SupplierPartQuotaInfo
/// </summary>
/// <param name="loginUser"></param>
/// <returns>SupplierPartQuotaInfo</returns>
public static SupplierPartQuotaInfo CreateSupplierPartQuotaInfo(string loginUser)
{
SupplierPartQuotaInfo info = new SupplierPartQuotaInfo();
///FID,
info.Fid = Guid.NewGuid();
///PLANT,工厂
info.Plant = null;
///PART_NO,物料号
info.PartNo = null;
///SUPPLIER_NUM,供应商
info.SupplierNum = null;
///TRANS_PACKAGE_MODEL,运输包装型号
//info.TransPackageModel = null;
/////TRANS_PACKAGE,运输包装收容数
//info.TransPackage = null;
/////INBOUND_PACKAGE_MODEL,标准入库包装
//info.InboundPackageModel = null;
/////INBOUND_PACKAGE,标准包装收容数
//info.InboundPackage = null;
/////ONLINE_PACKAGE_MODEL,上线包装型号
//info.OnlinePackageModel = null;
/////ONLINE_PACKAGE,上线包装收容数
//info.OnlinePackage = null;
/////EFFECTIVE_DATE,生效日期
//info.EffectiveDate = null;
/////INVALID_DATE,失效日期
//info.InvalidDate = null;
///QUOTE,配额
info.Quote = null;
///PROJECT,项目
info.Project = null;
///AGREEMENT_NO,协议编号
info.AgreementNo = null;
///COMMENTS,备注
info.Comments = null;
///VALID_FLAG,
info.ValidFlag = true;
///CREATE_USER,创建人
info.CreateUser = loginUser;
///CREATE_DATE,创建时间
info.CreateDate = DateTime.Now;
///MODIFY_USER,MODIFY_USER
info.ModifyUser = null;
///MODIFY_DATE,MODIFY_DATE
info.ModifyDate = null;
return(info);
}
public List <SupplierPartQuotaInfo> GetListByPages(string textWhere, int pageIndex, int pageRow, string wmNo, out int dataCount)
{
List <SupplierPartQuotaInfo> supplierPartQuotaInfos = new List <SupplierPartQuotaInfo>();
SupplierPartQuotaInfo supplierPart = dal.GetListByPages(textWhere, "[ID] asc", pageIndex, pageRow).FirstOrDefault();
if (supplierPart == null)
{
dataCount = supplierPartQuotaInfos.Count();
return(supplierPartQuotaInfos);
}
WarehouseInfo warehouseInfo = new WarehouseDAL().GetList("[WAREHOUSE] = N'" + wmNo + "'", string.Empty).FirstOrDefault();
SupplierPartQuotaInfo partQuotaInfo = new SupplierPartQuotaInfo();
// partQuotaInfo.InboundPackageModel = supplierPart.InboundPackageModel;
partQuotaInfo.Plant = "标准包装型号";
//partQuotaInfo.InboundPackage = supplierPart.InboundPackage;
supplierPartQuotaInfos.Add(partQuotaInfo);
partQuotaInfo = new SupplierPartQuotaInfo();
//partQuotaInfo.InboundPackageModel = supplierPart.TransPackageModel;
//partQuotaInfo.Plant = "运输包装型号";
//partQuotaInfo.InboundPackage = supplierPart.TransPackage;
supplierPartQuotaInfos.Add(partQuotaInfo);
partQuotaInfo = new SupplierPartQuotaInfo();
//partQuotaInfo.InboundPackageModel = supplierPart.OnlinePackageModel;
//partQuotaInfo.Plant = "上线包装型号";
//partQuotaInfo.InboundPackage = supplierPart.OnlinePackage;
supplierPartQuotaInfos.Add(partQuotaInfo);
if (warehouseInfo.WarehouseType == (int)WarehouseTypeConstants.VMI)
{
SupplierPartQuotaInfo supplierPartQuota = new SupplierPartQuotaInfo();
supplierPartQuota = supplierPartQuotaInfos[0];
supplierPartQuotaInfos[0] = supplierPartQuotaInfos[1];
supplierPartQuotaInfos[1] = supplierPartQuota;
}
dataCount = supplierPartQuotaInfos.Count();
return(supplierPartQuotaInfos);
}
/// <summary>
/// SapSupplierQuotaInfo -> SupplierPartQuotaInfo
/// </summary>
/// <param name="sapSupplierQuotaInfo"></param>
/// <param name="info"></param>
public static void GetSupplierPartQuotaInfo(SapSupplierQuotaInfo sapSupplierQuotaInfo, ref SupplierPartQuotaInfo info)
{
if (sapSupplierQuotaInfo == null)
{
return;
}
///FID,
info.Fid = Guid.NewGuid();
///PART_NO,零件号
info.PartNo = sapSupplierQuotaInfo.PartNo;
///QUOTE,配额
info.Quote = sapSupplierQuotaInfo.Quote;
///EFFECTIVE_DATE,生效日期
//info.EffectiveDate = sapSupplierQuotaInfo.IDate;
/////INVALID_DATE,失效日期
//info.InvalidDate = sapSupplierQuotaInfo.EDate;
///AGREEMENT_NO,协议编号
info.AgreementNo = sapSupplierQuotaInfo.Znrmm;
///SUPPLIER_NUM,供应商
info.SupplierNum = sapSupplierQuotaInfo.Lifnr;
}
/// <summary>
/// ImportDataByExcel
/// </summary>
/// <param name="dataTable"></param>
/// <param name="fieldNames"></param>
/// <param name="loginUser"></param>
/// <returns></returns>
public bool ImportDataByExcel(DataTable dataTable, Dictionary <string, string> fieldNames, string loginUser)
{
List <SupplierPartQuotaInfo> supplierPartQuotaExcelInfos = CommonDAL.DatatableConvertToList <SupplierPartQuotaInfo>(dataTable).ToList();
if (supplierPartQuotaExcelInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///获取业务表中要变更的数据集合,准备对比
List <SupplierPartQuotaInfo> supplierPartQuotaInfos = new SupplierPartQuotaDAL().GetListForInterfaceDataSync(supplierPartQuotaExcelInfos.Select(d => d.PartNo).ToList());
///执行的SQL语句
string sql = string.Empty;
List <string> fields = new List <string>(fieldNames.Keys);
///逐条处理中间表数据
foreach (var supplierPartQuotaExcelInfo in supplierPartQuotaExcelInfos)
{
SupplierPartQuotaInfo supplierPartQuotaInfo = supplierPartQuotaInfos.FirstOrDefault(d =>
d.PartNo == supplierPartQuotaExcelInfo.PartNo &&
d.SupplierNum == supplierPartQuotaExcelInfo.SupplierNum);
if (supplierPartQuotaInfo == null)
{
if (string.IsNullOrEmpty(supplierPartQuotaExcelInfo.PartNo) ||
string.IsNullOrEmpty(supplierPartQuotaExcelInfo.SupplierNum))
{
throw new Exception("MC:0x00000297");///物料号、供应商为必填项
}
///字段
string insertFieldString = string.Empty;
///值
string insertValueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <SupplierPartQuotaInfo>(supplierPartQuotaExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
insertFieldString += "[" + fieldNames[fields[i]] + "],";
insertValueString += valueStr + ",";
}
sql += "if not exists (select * from [LES].[TM_BAS_SUPPLIER_PART_QUOTA] with(nolock) where " +
"[PART_NO] = N'" + supplierPartQuotaExcelInfo.PartNo + "' and " +
"[SUPPLIER_NUM] = N'" + supplierPartQuotaExcelInfo.SupplierNum + "' and " +
"[VALID_FLAG] = 1) "
+ "insert into [LES].[TM_BAS_SUPPLIER_PART_QUOTA] ("
+ "[FID],"
+ insertFieldString
+ "[CREATE_USER],"
+ "[CREATE_DATE],"
+ "[VALID_FLAG]"
+ ") values ("
+ "NEWID()," ///FID
+ insertValueString
+ "N'" + loginUser + "'," ///CREATE_USER
+ "GETDATE()," ///CREATE_DATE
+ "1" ///VALID_FLAG
+ ");";
continue;
}
///
if (string.IsNullOrEmpty(supplierPartQuotaExcelInfo.PartNo) ||
string.IsNullOrEmpty(supplierPartQuotaExcelInfo.SupplierNum))
{
throw new Exception("MC:0x00000297");///物料号、供应商为必填项
}
///值
string valueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <SupplierPartQuotaInfo>(supplierPartQuotaExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
}
sql += "update [LES].[TM_BAS_SUPPLIER_PART_QUOTA] set "
+ valueString
+ "[MODIFY_USER] = N'" + loginUser + "',"
+ "[MODIFY_DATE] = GETDATE() "
+ "where [ID] = " + supplierPartQuotaInfo.Id + ";";
}
///
if (string.IsNullOrEmpty(sql))
{
return(false);
}
return(CommonDAL.ExecuteNonQueryBySql(sql));
}
/// <summary>
/// 获取同步VMI供应商物料关系接口同步语句
/// </summary>
/// <param name="supplierName"></param>
/// <param name="partCname"></param>
/// <param name="warehouseInfos"></param>
/// <param name="supplierPartQuotaInfo"></param>
/// <param name="enable_srm_flag"></param>
/// <param name="enable_vmi_flag"></param>
/// <param name="deleteFlag"></param>
/// <param name="loginUser"></param>
/// <returns></returns>
public static string GetSyncVmiSupplierPartSql(string supplierName, string partCname, List <WarehouseInfo> warehouseInfos, SupplierPartQuotaInfo supplierPartQuotaInfo, string enable_srm_flag, string enable_vmi_flag, bool deleteFlag, string loginUser)
{
StringBuilder @string = new StringBuilder();
///
if (!string.IsNullOrEmpty(enable_srm_flag) && enable_srm_flag.ToLower() == "true")
{
///TI_IFM_SRM_VMI_SUPPLIER_PART
SrmVmiSupplierPartInfo srmVmiSupplierPartInfo = new SrmVmiSupplierPartInfo();
srmVmiSupplierPartInfo.LogFid = Guid.NewGuid();
srmVmiSupplierPartInfo.Plant = supplierPartQuotaInfo.Plant;
srmVmiSupplierPartInfo.SupplierCode = supplierPartQuotaInfo.SupplierNum;
srmVmiSupplierPartInfo.SupplierName = supplierName;
srmVmiSupplierPartInfo.PartNo = supplierPartQuotaInfo.PartNo;
srmVmiSupplierPartInfo.PartCname = partCname;
srmVmiSupplierPartInfo.DeleteFlag = deleteFlag;
srmVmiSupplierPartInfo.ProcessFlag = (int)ProcessFlagConstants.Untreated;
srmVmiSupplierPartInfo.CreateUser = loginUser;
if (warehouseInfos.Count > 0)
{
foreach (WarehouseInfo warehouseInfo in warehouseInfos)
{
srmVmiSupplierPartInfo.VmiWarehouseCode = warehouseInfo.Warehouse;
srmVmiSupplierPartInfo.VmiWarehouseName = warehouseInfo.WarehouseName;
///
@string.AppendLine(SrmVmiSupplierPartDAL.GetInsertSql(srmVmiSupplierPartInfo));
}
}
else
{
@string.AppendLine(SrmVmiSupplierPartDAL.GetInsertSql(srmVmiSupplierPartInfo));
}
@string.AppendLine(CommonBLL.GetCreateOutboundLogSql(
"SRM",
srmVmiSupplierPartInfo.LogFid.GetValueOrDefault(),
"LES-SRM-001",
srmVmiSupplierPartInfo.PartNo + "|" + srmVmiSupplierPartInfo.SupplierCode + "|" + string.Join("|", warehouseInfos.Select(d => d.Warehouse).ToArray()),
loginUser));
}
///如果没有仓库信息则不发送给WMS
if (warehouseInfos.Count == 0)
{
return(@string.ToString());
}
///
//if (!string.IsNullOrEmpty(enable_vmi_flag) && enable_vmi_flag.ToLower() == "true")
//{
// ///TI_IFM_SRM_VMI_SUPPLIER_PART
// WmsVmiSupplierPartInfo wmsVmiSupplierPartInfo = new WmsVmiSupplierPartInfo();
// wmsVmiSupplierPartInfo.LogFid = Guid.NewGuid();
// wmsVmiSupplierPartInfo.Werks = supplierPartQuotaInfo.Plant;///TODO:SAP工厂代码?
// wmsVmiSupplierPartInfo.SupplierCode = supplierPartQuotaInfo.SupplierNum;
// wmsVmiSupplierPartInfo.SupplierName = supplierName;
// wmsVmiSupplierPartInfo.PartNo = supplierPartQuotaInfo.PartNo;
// wmsVmiSupplierPartInfo.PartCname = partCname;
// wmsVmiSupplierPartInfo.Cartoncode = supplierPartQuotaInfo.TransPackageModel;
// wmsVmiSupplierPartInfo.Cartonqty = supplierPartQuotaInfo.TransPackage;
// wmsVmiSupplierPartInfo.DeleteFlag = deleteFlag;
// wmsVmiSupplierPartInfo.ProcessFlag = (int)ProcessFlagConstants.Untreated;
// wmsVmiSupplierPartInfo.CreateUser = loginUser;
// foreach (WarehouseInfo warehouseInfo in warehouseInfos)
// {
// wmsVmiSupplierPartInfo.VmiWarehouseCode = warehouseInfo.Warehouse;
// wmsVmiSupplierPartInfo.VmiWarehouseName = warehouseInfo.WarehouseName;
// ///
// @string.AppendLine(WmsVmiSupplierPartDAL.GetInsertSql(wmsVmiSupplierPartInfo));
// }
// @string.AppendLine(CommonBLL.GetCreateOutboundLogSql(
// "VMI",
// wmsVmiSupplierPartInfo.LogFid.GetValueOrDefault(),
// "LES-WMS-008",
// wmsVmiSupplierPartInfo.PartNo + "|" + wmsVmiSupplierPartInfo.SupplierCode + "|" + string.Join("|", warehouseInfos.Select(d => d.Warehouse).ToArray()),
// loginUser));
//}
///
return(@string.ToString());
}
/// <summary>
/// 获取同步VMI供应商物料关系接口同步语句
/// </summary>
/// <param name="supplierPartQuotaInfo"></param>
/// <param name="deleteFlag"></param>
/// <param name="loginUser"></param>
/// <returns></returns>
private string GetSyncVmiSupplierPartSql(SupplierPartQuotaInfo supplierPartQuotaInfo, bool deleteFlag, string loginUser)
{
StringBuilder sql = new StringBuilder();
///供应商名称
string supplierName = new SupplierDAL().GetSupplierName(supplierPartQuotaInfo.SupplierNum);
///物料中文描述
string partCname = new MaintainPartsDAL().GetPartCname(supplierPartQuotaInfo.PartNo, supplierPartQuotaInfo.Plant);
if (!string.IsNullOrEmpty(partCname))
{
partCname = partCname.Replace("'", "''");
}
///供应商对应的外部仓库
List <WarehouseInfo> warehouseInfos = new WarehouseBLL().GetList("[WAREHOUSE] in (select [WM_NO] from [LES].[TM_BAS_VMI_SUPPLIER] with(nolock) " +
"where [SUPPLIER_NUM] = N'" + supplierPartQuotaInfo.SupplierNum + "' and [VALID_FLAG] = 1) and " +
"[WAREHOUSE_TYPE] = " + (int)WarehouseTypeConstants.VMI + "", string.Empty);
if (warehouseInfos.Count == 0)
{
return(string.Empty);
}
///是否启用SRM系统标记
string enable_srm_flag = new ConfigDAL().GetValueByCode("ENABLE_SRM_FLAG");
if (enable_srm_flag.ToLower() == "true")
{
///TI_IFM_SRM_VMI_SUPPLIER_PART
SrmVmiSupplierPartInfo srmVmiSupplierPartInfo = new SrmVmiSupplierPartInfo();
srmVmiSupplierPartInfo.LogFid = Guid.NewGuid();
srmVmiSupplierPartInfo.Plant = supplierPartQuotaInfo.Plant;
srmVmiSupplierPartInfo.SupplierCode = supplierPartQuotaInfo.SupplierNum;
srmVmiSupplierPartInfo.SupplierName = supplierName;
srmVmiSupplierPartInfo.PartNo = supplierPartQuotaInfo.PartNo;
srmVmiSupplierPartInfo.PartCname = partCname;
srmVmiSupplierPartInfo.DeleteFlag = deleteFlag;
srmVmiSupplierPartInfo.ProcessFlag = (int)ProcessFlagConstants.Untreated;
srmVmiSupplierPartInfo.CreateUser = loginUser;
foreach (WarehouseInfo warehouseInfo in warehouseInfos)
{
srmVmiSupplierPartInfo.VmiWarehouseCode = warehouseInfo.Warehouse;
srmVmiSupplierPartInfo.VmiWarehouseName = warehouseInfo.WarehouseName;
if (string.IsNullOrEmpty(srmVmiSupplierPartInfo.Plant))
{
srmVmiSupplierPartInfo.Plant = warehouseInfo.Plant;
}
///
sql.AppendLine(SrmVmiSupplierPartDAL.GetInsertSql(srmVmiSupplierPartInfo));
}
sql.AppendLine(CommonBLL.GetCreateOutboundLogSql(
"SRM",
srmVmiSupplierPartInfo.LogFid.GetValueOrDefault(),
"LES-SRM-001",
srmVmiSupplierPartInfo.PartNo + "|" + srmVmiSupplierPartInfo.SupplierCode + "|" + string.Join("|", warehouseInfos.Select(d => d.Warehouse).ToArray()),
loginUser));
}
///Log.WriteLogToFile("enable_srm_flag.ToLower();"+ enable_srm_flag.ToLower() +"; \r" + sql.ToString(), AppDomain.CurrentDomain.BaseDirectory + @"\SQL-Log\", DateTime.Now.ToString("yyyyMMddHH"));
///是否启用WMS系统标记
string enable_vmi_flag = new ConfigDAL().GetValueByCode("ENABLE_VMI_FLAG");
//if (enable_vmi_flag.ToLower() == "true")
//{
// ///TI_IFM_SRM_VMI_SUPPLIER_PART
// WmsVmiSupplierPartInfo wmsVmiSupplierPartInfo = new WmsVmiSupplierPartInfo();
// wmsVmiSupplierPartInfo.LogFid = Guid.NewGuid();
// wmsVmiSupplierPartInfo.Werks = supplierPartQuotaInfo.Plant;///TODO:SAP工厂代码?
// wmsVmiSupplierPartInfo.SupplierCode = supplierPartQuotaInfo.SupplierNum;
// wmsVmiSupplierPartInfo.SupplierName = supplierName;
// wmsVmiSupplierPartInfo.PartNo = supplierPartQuotaInfo.PartNo;
// wmsVmiSupplierPartInfo.PartCname = partCname;
// wmsVmiSupplierPartInfo.Cartoncode = supplierPartQuotaInfo.TransPackageModel;
// wmsVmiSupplierPartInfo.Cartonqty = supplierPartQuotaInfo.TransPackage;
// wmsVmiSupplierPartInfo.DeleteFlag = deleteFlag;
// wmsVmiSupplierPartInfo.ProcessFlag = (int)ProcessFlagConstants.Untreated;
// wmsVmiSupplierPartInfo.CreateUser = loginUser;
// foreach (WarehouseInfo warehouseInfo in warehouseInfos)
// {
// wmsVmiSupplierPartInfo.VmiWarehouseCode = warehouseInfo.Warehouse;
// wmsVmiSupplierPartInfo.VmiWarehouseName = warehouseInfo.WarehouseName;
// ///
// sql.AppendLine(WmsVmiSupplierPartDAL.GetInsertSql(wmsVmiSupplierPartInfo));
// }
// sql.AppendLine(CommonBLL.GetCreateOutboundLogSql(
// "VMI",
// wmsVmiSupplierPartInfo.LogFid.GetValueOrDefault(),
// "LES-WMS-008",
// wmsVmiSupplierPartInfo.PartNo + "|" + wmsVmiSupplierPartInfo.SupplierCode + "|" + string.Join("|", warehouseInfos.Select(d => d.Warehouse).ToArray()),
// loginUser));
//}
///
return(sql.ToString());
}
/// <summary>
/// SAP供应商配额基础数据同步
/// </summary>
/// <returns></returns>
public static void Sync(string loginUser)
{
List <SapSupplierQuotaInfo> sapSupplierQuotaInfos = new SapSupplierQuotaBLL().GetListByPage("" +
"[PROCESS_FLAG] in (" + (int)ProcessFlagConstants.Untreated + "," + (int)ProcessFlagConstants.Resend + ")", "[ID]", 1, 1000, out int dataCnt);
if (dataCnt == 0)
{
return;
}
///执行的SQL语句
StringBuilder @string = new StringBuilder();
///是否启用SRM系统标记
string enable_srm_flag = new ConfigDAL().GetValueByCode("ENABLE_SRM_FLAG");
///是否启用WMS系统标记
string enable_vmi_flag = new ConfigDAL().GetValueByCode("ENABLE_VMI_FLAG");
///同步供应商基础数据
@string.AppendLine(GetSyncSupplierSql(sapSupplierQuotaInfos, loginUser));
///获取业务表中要变更的数据集合,准备对比
List <SupplierPartQuotaInfo> supplierPartQuotaInfos = new SupplierPartQuotaBLL().GetListForInterfaceDataSync(sapSupplierQuotaInfos.Select(d => d.PartNo).ToList());
///物料信息
List <MaintainPartsInfo> maintainPartsInfos = new MaintainPartsBLL().GetListForInterfaceDataSync(sapSupplierQuotaInfos.Select(d => d.PartNo).ToList());
///VMI供应商关系
List <VmiSupplierInfo> vmiSupplierInfos = new VmiSupplierBLL().GetList("" +
"[SUPPLIER_NUM] in ('" + string.Join("','", sapSupplierQuotaInfos.Select(d => d.Lifnr).ToArray()) + "'", string.Empty);
///VMI仓库
List <WarehouseInfo> warehouseInfos = new List <WarehouseInfo>();
if (vmiSupplierInfos.Count > 0)
{
warehouseInfos = new WarehouseBLL().GetList("" +
"[WAREHOUSE] in ('" + string.Join("','", vmiSupplierInfos.Select(d => d.WmNo).ToArray()) + "') and " +
"[WAREHOUSE_TYPE] = " + (int)WarehouseTypeConstants.VMI + "", string.Empty);
}
///获取工厂信息
List <PlantInfo> plantInfos = new PlantBLL().GetListForInterfaceDataSync();
///已处理完成的ID
List <long> dealedIds = new List <long>();
///逐条处理中间表数据
foreach (var sapSupplierQuotaInfo in sapSupplierQuotaInfos)
{
PlantInfo plantInfo = plantInfos.FirstOrDefault(d => d.SapPlantCode == sapSupplierQuotaInfo.Werks);
if (plantInfo == null)
{
///将这样的数据更新为挂起状态
@string.AppendLine("update [LES].[TI_IFM_SAP_SUPPLIER_QUOTA] " +
"set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'3x00000019'," +///工厂信息不存在
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [ID] = " + sapSupplierQuotaInfo.Id + ";");
continue;
}
MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.Plant == plantInfo.Plant && d.PartNo == sapSupplierQuotaInfo.PartNo);
if (maintainPartsInfo == null)
{
///将这样的数据更新为挂起状态
@string.AppendLine("update [LES].[TI_IFM_SAP_SUPPLIER_QUOTA] " +
"set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000417'," +///物料信息错误
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [ID] = " + sapSupplierQuotaInfo.Id + ";");
continue;
}
///VMI供应商关系，且未启用LES的VMI模块的
List <VmiSupplierInfo> vmiSuppliers = vmiSupplierInfos.Where(d => d.SupplierNum == sapSupplierQuotaInfo.Lifnr && !d.VmiFlag.GetValueOrDefault()).ToList();
List <WarehouseInfo> warehouses = new List <WarehouseInfo>();
if (vmiSuppliers.Count > 0)
{
warehouses = warehouseInfos.Where(d => vmiSuppliers.Select(v => v.WmNo).Contains(d.Warehouse)).ToList();
}
///当前业务数据表中无此工厂代码+物料编号+供应商信息时需要新增
SupplierPartQuotaInfo supplierPartQuotaInfo = supplierPartQuotaInfos.FirstOrDefault(d =>
d.PartNo == maintainPartsInfo.PartNo &&
d.Plant == plantInfo.Plant &&
d.SupplierNum == sapSupplierQuotaInfo.Lifnr);
///标识该配额需要删除
///停供作为删除处理，ZSTOP = X时标识停供
if (sapSupplierQuotaInfo.Flag.ToUpper() == "D" || sapSupplierQuotaInfo.Zstop.ToUpper() == "X")
{
///根据工厂代码+物料编号+供应商对配额信息进行逻辑删除
@string.AppendLine("update [LES].[TM_BAS_SUPPLIER_PART_QUOTA] " +
"set [VALID_FLAG] = 0," +
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [PART_NO] = N'" + sapSupplierQuotaInfo.PartNo + "' and " +
"[PLANT] = N'" + plantInfo.Plant + "' and " +
"[SUPPLIER_NUM] = N'" + sapSupplierQuotaInfo.Lifnr + "' and " +
"[VALID_FLAG] = 1;");
dealedIds.Add(sapSupplierQuotaInfo.Id);
if (supplierPartQuotaInfo == null)
{
supplierPartQuotaInfo = SupplierPartQuotaBLL.CreateSupplierPartQuotaInfo(loginUser);
}
///SapSupplierQuotaInfo -> SupplierPartQuotaInfo
SupplierPartQuotaBLL.GetSupplierPartQuotaInfo(sapSupplierQuotaInfo, ref supplierPartQuotaInfo);
@string.AppendLine(SupplierPartQuotaBLL.GetSyncVmiSupplierPartSql(
sapSupplierQuotaInfo.SupplierName,
maintainPartsInfo.PartCname,
warehouses,
supplierPartQuotaInfo,
enable_srm_flag,
enable_vmi_flag,
true,
loginUser));
continue;
}
///
if (supplierPartQuotaInfo == null)
{
supplierPartQuotaInfo = SupplierPartQuotaBLL.CreateSupplierPartQuotaInfo(loginUser);
///SapSupplierQuotaInfo -> SupplierPartQuotaInfo
SupplierPartQuotaBLL.GetSupplierPartQuotaInfo(sapSupplierQuotaInfo, ref supplierPartQuotaInfo);
///
@string.AppendLine(SupplierPartQuotaDAL.GetInsertSql(supplierPartQuotaInfo));
///加入后以免影响下次判断
supplierPartQuotaInfos.Add(supplierPartQuotaInfo);
///
dealedIds.Add(sapSupplierQuotaInfo.Id);
///
@string.AppendLine(SupplierPartQuotaBLL.GetSyncVmiSupplierPartSql(
sapSupplierQuotaInfo.SupplierName,
maintainPartsInfo.PartCname,
warehouses,
supplierPartQuotaInfo,
enable_srm_flag,
enable_vmi_flag,
false,
loginUser));
continue;
}
///
if (supplierPartQuotaInfo.Id == 0)
{
continue;
}
///
@string.AppendLine("update [LES].[TM_BAS_SUPPLIER_PART_QUOTA] set "
+ "[EFFECTIVE_DATE] = N'" + sapSupplierQuotaInfo.IDate + "',"
+ "[INVALID_DATE] = N'" + sapSupplierQuotaInfo.EDate + "',"
+ "[QUOTE] = " + sapSupplierQuotaInfo.Quote.GetValueOrDefault() + ","
+ "[MODIFY_USER] = N'" + loginUser + "',"
+ "[MODIFY_DATE] = GETDATE() "
+ " where [FID] = N'" + supplierPartQuotaInfo.Fid.GetValueOrDefault() + "';");
///
@string.AppendLine(SupplierPartQuotaBLL.GetSyncVmiSupplierPartSql(
sapSupplierQuotaInfo.SupplierName,
maintainPartsInfo.PartCname,
warehouses,
supplierPartQuotaInfo,
enable_srm_flag,
enable_vmi_flag,
false,
loginUser));
dealedIds.Add(sapSupplierQuotaInfo.Id);
}
///
if (dealedIds.Count > 0)
{
///已处理的中间表数据更新为已处理状态
@string.AppendLine("update [LES].[TI_IFM_SAP_SUPPLIER_QUOTA] " +
"set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Processed + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = NULL," +
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [ID] in (" + string.Join(",", dealedIds.ToArray()) + ");");
}
using (var trans = new TransactionScope())
{
if (@string.Length > 0)
{
BLL.SYS.CommonBLL.ExecuteNonQueryBySql(@string.ToString());
}
trans.Complete();
}
}
