/// <summary>
/// InsertInfo
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public long InsertInfo(TwdPartBoxInfo info)
{
ValidTwdPartBoxInfo(info);
info.Status = (int)BasicDataStatusConstants.Created;
return(dal.Add(info));
}
/// <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 <TwdPartBoxInfo> twdPartBoxExcelInfos = CommonDAL.DatatableConvertToList <TwdPartBoxInfo>(dataTable).ToList();
if (twdPartBoxExcelInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///获取业务表中要变更的数据集合,准备对比
List <TwdPartBoxInfo> twdPartBoxInfos = new TwdPartBoxDAL().GetList("[PART_BOX_CODE] in ('" + string.Join("', '", twdPartBoxExcelInfos.Select(d => d.PartBoxCode).ToList().ToArray()) + "')", "");
///执行的SQL语句
StringBuilder @string = new StringBuilder();
List <string> fields = new List <string>(fieldNames.Keys);
///逐条处理中间表数据
foreach (var twdPartBoxExcelInfo in twdPartBoxExcelInfos)
{
TwdPartBoxInfo twdPartBoxInfo = twdPartBoxInfos.FirstOrDefault(d => d.PartBoxCode == twdPartBoxExcelInfo.PartBoxCode);
///需要新增
if (twdPartBoxInfo == null)
{
///校验
ValidTwdPartBoxInfo(twdPartBoxExcelInfo);
///字段
string insertFieldString = string.Empty;
///值
string insertValueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <TwdPartBoxInfo>(twdPartBoxExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
insertFieldString += "[" + fieldNames[fields[i]] + "],";
insertValueString += valueStr + ",";
}
///判断业务主键是否重复，以防止EXCEL中有重复数据，适用于基础数据导入
@string.AppendLine("if not exists (select * from LES.TM_MPM_TWD_PART_BOX with(nolock) " +
"where [PART_BOX_CODE] = N'" + twdPartBoxExcelInfo.PartBoxCode + "' and [VALID_FLAG] = 1)"
+ " insert into [LES].[TM_MPM_TWD_PART_BOX] ("
+ "[FID],"
+ insertFieldString
+ "[STATUS],"
+ "[CREATE_USER],"
+ "[CREATE_DATE],"
+ "[VALID_FLAG]"
+ ") values ("
+ "NEWID(),"///FID
+ insertValueString
+ (int)BasicDataStatusConstants.Created + ","
+ "N'" + loginUser + "'," ///CREATE_USER
+ "GETDATE()," ///CREATE_DATE
+ "1" ///VALID_FLAG
+ ");");
///防止EXCEL中有重复项
twdPartBoxInfos.Add(twdPartBoxExcelInfo);
continue;
}
///
if (twdPartBoxInfo.Status.GetValueOrDefault() == (int)BasicDataStatusConstants.Disabled)
{
continue;
}
///
twdPartBoxExcelInfo.Id = twdPartBoxInfo.Id;
///校验
ValidTwdPartBoxInfo(twdPartBoxExcelInfo);
///是否更新了拉动模式,需要同步更新物料拉动信息中的拉动模式
if (twdPartBoxInfo.TwdPullMode.GetValueOrDefault() != twdPartBoxExcelInfo.TwdPullMode.GetValueOrDefault())
{
@string.AppendLine("update [LES].[TM_BAS_MAINTAIN_INHOUSE_LOGISTIC_STANDARD] " +
"set [INHOUSE_SYSTEM_MODE] = N'" + twdPartBoxExcelInfo.TwdPullMode.GetValueOrDefault() + "'," +
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [INHOUSE_SYSTEM_MODE] = N'" + twdPartBoxInfo.TwdPullMode.GetValueOrDefault() + "' and " +
"[INHOUSE_PART_CLASS] = N'" + twdPartBoxExcelInfo.PartBoxCode + "' and " +
"[VALID_FLAG] = 1;");
}
///已创建状态的数据可以全信息更新
if (twdPartBoxInfo.Status.GetValueOrDefault() == (int)BasicDataStatusConstants.Created)
{
///值
string valueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <TwdPartBoxInfo>(twdPartBoxExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
}
@string.AppendLine("update [LES].[TM_MPM_TWD_PART_BOX] set "
+ valueString
+ "[MODIFY_USER] = N'" + loginUser + "',"
+ "[MODIFY_DATE] = GETDATE() "
+ "where [ID] = " + twdPartBoxInfo.Id + ";");
continue;
}
///
@string.AppendLine("update [LES].[TM_MPM_TWD_PART_BOX] " +
"set [PART_BOX_NAME] = N'" + twdPartBoxExcelInfo.PartBoxName + "'," +
"[REQUIREMENT_ACCUMULATE_TIME] = " + (twdPartBoxExcelInfo.RequirementAccumulateTime == null ? "NULL" : "" + twdPartBoxExcelInfo.RequirementAccumulateTime.GetValueOrDefault() + "") + "," +
"[LOAD_TIME] = " + (twdPartBoxExcelInfo.LoadTime == null ? "NULL" : "" + twdPartBoxExcelInfo.LoadTime.GetValueOrDefault() + "") + "," +
"[TRANSPORT_TIME] = " + (twdPartBoxExcelInfo.TransportTime == null ? "NULL" : "" + twdPartBoxExcelInfo.TransportTime.GetValueOrDefault() + "") + "," +
"[UNLOAD_TIME] = " + (twdPartBoxExcelInfo.UnloadTime == null ? "NULL" : "" + twdPartBoxExcelInfo.UnloadTime.GetValueOrDefault() + "") + "," +
"[DELAY_TIME] = " + (twdPartBoxExcelInfo.DelayTime == null ? "NULL" : "" + twdPartBoxExcelInfo.DelayTime.GetValueOrDefault() + "") + "," +
"[ONLINE_TIME] = " + (twdPartBoxExcelInfo.OnlineTime == null ? "NULL" : "" + twdPartBoxExcelInfo.OnlineTime.GetValueOrDefault() + "") + "," +
"[REQUIREMENT_ACCUMULATE_MODE] = " + twdPartBoxExcelInfo.RequirementAccumulateMode.GetValueOrDefault() + "," +
"[STATUS_POINT_CODE] = N'" + twdPartBoxExcelInfo.StatusPointCode + "'," +
"[ROUNDNESS_MODE] = " + twdPartBoxExcelInfo.RoundnessMode.GetValueOrDefault() + "," +
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [ID] = " + twdPartBoxInfo.Id + ";");
}
///执行
using (TransactionScope trans = new TransactionScope())
{
if (@string.Length > 0)
{
CommonDAL.ExecuteNonQueryBySql(@string.ToString());
}
trans.Complete();
}
return(true);
}