/// <summary>
/// InsertInfo
/// </summary>
/// <param name="info">对象</param>
/// <returns></returns>
public long InsertInfo(JisPartBoxInfo info)
{
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 <JisPartBoxInfo> jisPartBoxes = CommonDAL.DatatableConvertToList <JisPartBoxInfo>(dataTable).ToList();
if (jisPartBoxes.Count == 0 || jisPartBoxes == null)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///获取业务表中要变更的数据集合,准备对比
List <JisPartBoxInfo> jisParts = new JisPartBoxDAL().GetList(" [PART_BOX_CODE] in ('" + string.Join("', '", jisPartBoxes.Select(d => d.PartBoxCode).ToList().ToArray()) + "')", "");
List <JisPartBoxInfo> boxInfos = new JisPartBoxDAL().GetList(" [PART_BOX_NAME] in ('" + string.Join("', '", jisPartBoxes.Select(d => d.PartBoxName).ToList().ToArray()) + "')", "");
///执行的SQL语句
string sql = string.Empty;
List <string> fields = new List <string>(fieldNames.Keys);
///逐条处理中间表数据
foreach (var item in jisPartBoxes)
{
JisPartBoxInfo info = jisParts.FirstOrDefault(d => d.PartBoxCode == item.PartBoxCode);
if (info == null)
{
if (string.IsNullOrEmpty(item.PartBoxCode) ||
string.IsNullOrEmpty(item.PartBoxName) ||
string.IsNullOrEmpty(item.Plant) ||
string.IsNullOrEmpty(item.Workshop) ||
string.IsNullOrEmpty(item.AssemblyLine) ||
string.IsNullOrEmpty(item.StatusPointCode) ||
string.IsNullOrEmpty(item.TWmNo) ||
string.IsNullOrEmpty(item.TZoneNo))
{
throw new Exception("MC:0x00000452");///零件类代码、零件类名称、工厂、车间、产线、状态点、目标仓库存储区为必填项
}
JisPartBoxInfo jisPartBoxInfo = boxInfos.FirstOrDefault(d => d.PartBoxName == item.PartBoxName);
if (jisPartBoxInfo != null)
{
throw new Exception("MC:0x00000303");///零件类名称重复
}
///字段
string insertFieldString = string.Empty;
///值
string insertValueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <JisPartBoxInfo>(item, 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_MPM_TWD_PART_BOX with(nolock) where [PART_BOX_CODE] = N'" + item.PartBoxCode + "' and [VALID_FLAG] = 1)"
+ " insert into [LES].[TM_MPM_JIS_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
+ ");";
jisParts.Add(item);
continue;
}
if (info.Status == (int)BasicDataStatusConstants.Created)
{
if (string.IsNullOrEmpty(item.PartBoxName) ||
string.IsNullOrEmpty(item.Plant) ||
string.IsNullOrEmpty(item.Workshop) ||
string.IsNullOrEmpty(item.AssemblyLine) ||
string.IsNullOrEmpty(item.StatusPointCode) ||
string.IsNullOrEmpty(item.TWmNo) ||
string.IsNullOrEmpty(item.TZoneNo))
{
throw new Exception("MC:0x00000452");///零件类代码、零件类名称、工厂、车间、产线、状态点、目标仓库存储区为必填项
}
JisPartBoxInfo jisPartBoxInfo = boxInfos.FirstOrDefault(d => d.PartBoxName == item.PartBoxName && d.Id != info.Id);
if (jisPartBoxInfo != null)
{
throw new Exception("MC:0x00000303");///零件类名称重复
}
///值
string valueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <JisPartBoxInfo>(item, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
}
sql += "update [LES].[TM_MPM_JIS_PART_BOX] set "
+ valueString
+ "[MODIFY_USER] = N'" + loginUser + "',"
+ "[MODIFY_DATE] = GETDATE() "
+ "where [ID] = " + info.Id + ";";
}
}
if (string.IsNullOrEmpty(sql))
{
return(false);
}
return(CommonDAL.ExecuteNonQueryBySql(sql));
}
