/// <summary>
/// 执行导入EXCEL数据
/// </summary>
/// <param name="dataTable"></param>
/// <param name="fieldNames"></param>
/// <returns></returns>
public bool ImportDataByExcel(DataTable dataTable, Dictionary <string, string> fieldNames, string loginUser)
{
List <SupplierInfo> supplierExcelInfos = CommonDAL.DatatableConvertToList <SupplierInfo>(dataTable).ToList();
if (supplierExcelInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///获取业务表中要变更的数据集合,准备对比
List <SupplierInfo> supplierInfos = new SupplierDAL().GetListForInterfaceDataSync(supplierExcelInfos.Select(d => d.SupplierNum).ToList());
///执行的SQL语句
string sql = string.Empty;
List <string> fields = new List <string>(fieldNames.Keys);
///逐条处理中间表数据
foreach (var supplierExcelInfo in supplierExcelInfos)
{
///当前业务数据表中此工厂的该物流路线时需要新增
SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == supplierExcelInfo.SupplierNum);
if (supplierInfo == null)
{
if (string.IsNullOrEmpty(supplierExcelInfo.SupplierNum) ||
string.IsNullOrEmpty(supplierExcelInfo.SupplierName) ||
supplierExcelInfo.SupplierType.GetValueOrDefault() == 0)
{
throw new Exception("MC:0x00000221");///供应商代码、名称、类型为必填项
}
///字段
string insertFieldString = string.Empty;
///值
string insertValueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <SupplierInfo>(supplierExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
insertFieldString += "[" + fieldNames[fields[i]] + "],";
insertValueString += valueStr + ",";
}
///判断业务主键是否重复，以防止EXCEL中有重复数据，适用于基础数据导入
sql += "if not exists (select * from LES.TM_BAS_SUPPLIER with(nolock) where [SUPPLIER_NUM] = N'" + supplierExcelInfo.SupplierNum + "' and [VALID_FLAG] = 1)"
+ " insert into [LES].[TM_BAS_SUPPLIER] ("
+ "[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(supplierExcelInfo.SupplierName) ||
supplierExcelInfo.SupplierType.GetValueOrDefault() == 0)
{
throw new Exception("MC:0x00000221");///供应商代码、名称、类型为必填项
}
///值
string valueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <SupplierInfo>(supplierExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
}
sql += "update [LES].[TM_BAS_SUPPLIER] set "
+ valueString
+ "[MODIFY_USER] = N'" + loginUser + "',"
+ "[MODIFY_DATE] = GETDATE() "
+ "where [ID] = " + supplierInfo.Id + ";";
}
///
if (string.IsNullOrEmpty(sql))
{
return(false);
}
return(CommonDAL.ExecuteNonQueryBySql(sql));
}
/// <summary>
/// 批量创建看板卡
/// </summary>
/// <param name="loginUser"></param>
/// <returns></returns>
public bool SynchronizationKanBanCardInfos(string loginUser)
{
#region 准备数据源
StringBuilder sqlBuilder = new StringBuilder();
List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardInfos = new MaintainInhouseLogisticStandardDAL().GetList("[STATUS] = " + (int)BasicDataStatusConstants.Enable + "and [INHOUSE_SYSTEM_MODE] = N'" + (int)PullModeConstants.Kanban + "'", string.Empty);
if (maintainInhouseLogisticStandardInfos.Count() == 0)
{
throw new Exception("MC:0x00000256");///不存在可同步的物料看板拉动信息
}
///已生成的看板卡，除了作废的卡片之外
List <KanbanCardInfo> kanbanCardInfos = dal.GetList("[STATUS] <> " + (int)BasicDataStatusConstants.Disabled, string.Empty);
///已生成的看板卡统计信息
var groupKanbanCardInfos = kanbanCardInfos.GroupBy(w => new { w.PartNo, w.PartBoxCode }).Select(w => new { w.Key.PartBoxCode, w.Key.PartNo, SumCount = w.Count() }).ToList();
///看板零件类
List <KanbanPartBoxInfo> kanbanPartBoxInfos = new KanbanPartBoxDAL().GetList(string.Format("[PART_BOX_CODE] in ('{0}') and [STATUS] = " + (int)BasicDataStatusConstants.Enable + "",
string.Join("','", maintainInhouseLogisticStandardInfos.GroupBy(w => new { w.InhousePartClass }).Select(w => w.Key.InhousePartClass).ToArray())), string.Empty);
///供应商信息
List <SupplierInfo> supplierinfos = new SupplierDAL().GetList(string.Format("[SUPPLIER_NUM] IN ('{0}')", maintainInhouseLogisticStandardInfos.GroupBy(w => new { w.SupplierNum }).Select(w => w.Key.SupplierNum).ToArray()), string.Empty);
#endregion
#region SQL脚本
string sqlstr = @"insert into [LES].[TM_MPM_KANBAN_CARD] (
FID,
CARD_NO,
PART_BOX_CODE,
PART_BOX_NAME,
PART_NO,
PART_NAME,
SUPPLIER_CODE,
SUPPLIER_NAME,
PART_QTY,
PACKAGE_CODE,
STATUS,
PRINT_CNT,
PRINT_TIME,
PRINT_USER,
USED_STATUS,
SCANNED_USER,
SCANNED_DATE,
VALID_FLAG,
CREATE_DATE,
CREATE_USER,
MODIFY_DATE,
MODIFY_USER
) values ({0});" ;
#endregion
#region 编列数据源生成脚本
foreach (var maintainInhouseLogisticStandardInfo in maintainInhouseLogisticStandardInfos)
{
///计算出还可创建多少张看板卡
int createCardCount = 0;
var groupKanbanCardInfo = groupKanbanCardInfos.FirstOrDefault(w =>
w.PartNo == maintainInhouseLogisticStandardInfo.PartNo &&
w.PartBoxCode == maintainInhouseLogisticStandardInfo.InhousePartClass);
if (groupKanbanCardInfo == null)
{
createCardCount = maintainInhouseLogisticStandardInfo.KanbanCircleCnt.GetValueOrDefault();
}
else
{
createCardCount = maintainInhouseLogisticStandardInfo.KanbanCircleCnt.GetValueOrDefault() - groupKanbanCardInfo.SumCount;
}
///如果没有可创建的卡片则继续下一个
if (createCardCount == 0)
{
continue;
}
///获取看板零件类信息，且零件类必须处于已启用状态
KanbanPartBoxInfo kanbanPartBoxInfo = kanbanPartBoxInfos.FirstOrDefault(d => d.PartBoxCode == maintainInhouseLogisticStandardInfo.InhousePartClass);
if (kanbanPartBoxInfo == null)
{
continue;
}
SupplierInfo supplierInfo = supplierinfos.FirstOrDefault(d => d.SupplierNum == maintainInhouseLogisticStandardInfo.SupplierNum);
for (int i = 0; i < createCardCount; i++)
{
string sqlKanbanPullOrderDetailValue = "NEWID()"///FID
+ ",N'" + new SeqDefineDAL().GetCurrentCode("KANBAN_CARD_NO", null) + "'"
+ ",N'HOSPITAL'"
//+ ",N'" + kanbanPartBoxInfo.PartBoxCode + "'"
+ ",N'" + kanbanPartBoxInfo.PartBoxName + "'"
+ ",N'" + maintainInhouseLogisticStandardInfo.PartNo + "'"
+ ",N'" + maintainInhouseLogisticStandardInfo.PartCname + "'"
+ ",N'" + maintainInhouseLogisticStandardInfo.SupplierNum + "'"
+ ",N'" + (supplierInfo == null ? string.Empty : supplierInfo.SupplierName) + "'"///供应商名称
+ "," + maintainInhouseLogisticStandardInfo.InboundPackage.GetValueOrDefault()
+ ",N'" + maintainInhouseLogisticStandardInfo.InboundPackageModel + "'"
+ "," + (int)BasicDataStatusConstants.Created
+ ",NULL"
+ ",NULL"
+ ",NULL"
+ ",10"
+ ",NULL"
+ ",NULL"
+ ",1" ///VALID_FLAG
+ ",GETDATE()" ///CREATE_DATE
+ ",N'" + loginUser + "'" ///CREATE_USER
+ ",NULL"
+ ",NULL";
sqlBuilder.AppendLine(string.Format(sqlstr, sqlKanbanPullOrderDetailValue));
}
}
#endregion
if (sqlBuilder.Length == 0)
{
throw new Exception("MC:0x00000249");///不存在需要生成的看板卡
}
///批量创建看板卡时是否同时启用
string batchCreateKanbanCardEnableAtSametime = new ConfigDAL().GetValueByCode("BATCH_CREATE_KANBAN_CARD_ENABLE_AT_SAMETIME");
if (batchCreateKanbanCardEnableAtSametime.ToLower() == "true")
{
sqlBuilder.AppendLine("update [LES].[TM_MPM_KANBAN_CARD] "
+ "set [STATUS] = " + (int)BasicDataStatusConstants.Enable + ",[MODIFY_DATE] = GETDATE(),[MODIFY_USER] = N'" + loginUser + "' "
+ "where [STATUS] = " + (int)BasicDataStatusConstants.Created + ";");
}
using (TransactionScope trans = new TransactionScope())
{
CommonDAL.ExecuteNonQueryBySql(sqlBuilder.ToString());
trans.Complete();
}
return(true);
}