/// <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)); }