/// <summary> /// Excel数据导入 /// </summary> /// <param name="path">相对路径</param> /// <param name="user">当前登录人员</param> /// <returns>导出结果</returns> public RemoveExistingRecordEntity ExcelImport(string path, LoginEntity user) { string cmdText = @"SELECT [专业代码],[专业名称] FROM [Sheet1$];"; List <Mod_Online_MajorCodeInfo> importList = new List <Mod_Online_MajorCodeInfo>(); int addCount = 0; int succCount = 0; int errorCount = 0; int rowNumber = 1; StringBuilder strCodeId = new StringBuilder();//获取当前专业代码 using (OleDbDataReader dr = ExcelHelper.ReadExcel(FileHelper.GetMapPath(string.Format("~{0}", path)), cmdText)) { while (dr.Read()) { Mod_Online_MajorCodeInfo model = new Mod_Online_MajorCodeInfo(); model.CodeId = StringHelper.TrimString(dr["专业代码"].ToString()); model.CodeName = StringHelper.TrimString(dr["专业名称"].ToString()); rowNumber++; model.RowNumber = rowNumber; importList.Add(model); strCodeId.AppendFormat("'{0}',", model.CodeId); } } addCount = importList.Count; List <Mod_Online_MajorCodeInfo> tempAddList = new List <Mod_Online_MajorCodeInfo>(); //待添加的列表(临时) List <Mod_Online_MajorCodeEntity> addList = new List <Mod_Online_MajorCodeEntity>(); //待添加的列表 List <Mod_Online_MajorCodeEntity> updateList = new List <Mod_Online_MajorCodeEntity>(); //待更新的列表 string codeIdStr = strCodeId.ToString().Trim().TrimEnd(','); List <Mod_Online_MajorCodeEntity> existList = GetList(); StringBuilder strAddUserStr = new StringBuilder(); bool isUpdate = false; foreach (Mod_Online_MajorCodeInfo import in importList) { isUpdate = false; Mod_Online_MajorCodeEntity exist = existList.Find(x => x.CodeId.Trim() == import.CodeId.Trim()); if (exist != null) { isUpdate = true; Mod_Online_MajorCodeEntity model = new Mod_Online_MajorCodeEntity(); model.Id = exist.Id; model.CodeId = import.CodeId; model.CodeName = import.CodeName; model.ParentId = exist.ParentId; updateList.Add(model); } if (!isUpdate) { Mod_Online_MajorCodeInfo model = new Mod_Online_MajorCodeInfo(); model.CodeId = import.CodeId; model.CodeName = import.CodeName; model.RowNumber = import.RowNumber; model.ParentId = ""; tempAddList.Add(model); } else { succCount++; } } RemoveExistingRecordEntity returnData = new RemoveExistingRecordEntity(); StringBuilder strReturn = new StringBuilder(); if (tempAddList.Count > 0) { List <Mod_Online_MajorCodeInfo> childList = tempAddList.FindAll(x => x.CodeId.Length > 4).ToList(); List <Mod_Online_MajorCodeInfo> parentList = tempAddList.FindAll(x => x.CodeId.Length <= 4).ToList(); foreach (Mod_Online_MajorCodeEntity parent in parentList) { Mod_Online_MajorCodeEntity model = new Mod_Online_MajorCodeEntity(); model.Id = StringHelper.GetGuid(); parent.Id = model.Id; model.CodeId = parent.CodeId; model.CreateTime = DateTime.Now; model.CodeName = parent.CodeName; model.ParentId = ""; addList.Add(model); succCount++; } foreach (Mod_Online_MajorCodeInfo child in childList) { Mod_Online_MajorCodeEntity model = new Mod_Online_MajorCodeEntity(); model.Id = StringHelper.GetGuid(); model.CreateTime = DateTime.Now; model.CodeId = child.CodeId; model.CodeName = child.CodeName; Mod_Online_MajorCodeEntity temp = parentList.Find(x => x.CodeId.Trim() == model.CodeId.Substring(0, 4)); if (temp != null) { model.ParentId = temp.Id; addList.Add(model); succCount++; } else { temp = existList.Find(x => x.CodeId.Trim() == model.CodeId.Substring(0, 4)); if (temp != null) { model.ParentId = temp.Id; addList.Add(model); succCount++; } else { Mod_Online_MajorCodeEntity parent = new Mod_Online_MajorCodeEntity(); parent.Id = StringHelper.GetGuid(); parent.CreateTime = DateTime.Now; parent.CodeId = model.CodeId.Substring(0, 4); parent.CodeName = ""; succCount++; model.ParentId = parent.Id; addList.Add(parent); addList.Add(model); } } } } bool flag = DBHelper.Transaction((dp, ex) => { UpdateMajorCode(updateList, dp); Add(addList, dp); }) > 0; returnData.flag = flag; returnData.AddCount = addCount; returnData.SuccCount = succCount; returnData.ErrorCount = errorCount; returnData.Existstr = strReturn.ToString().Trim().TrimEnd('、'); return(returnData); }
/// <summary> /// 添加一条记录 /// </summary> /// <param name="entity">待添加的实体对象</param> /// <returns>true:添加成功 false:添加失败</returns> public bool Add(Mod_Online_MajorCodeEntity entity, IDataProvider provider) { SQL sql = SQL.Build("INSERT INTO Mod_Online_MajorCode(Id,ParentId,CodeId,CodeName,CreateTime,StateFlag,OrderIndex,DeleteFlag,ExtFlag) VALUES(?,?,?,?,?,?,?,?,?)", entity.Id, entity.ParentId, entity.CodeId, entity.CodeName, entity.CreateTime, entity.StateFlag, entity.OrderIndex, entity.DeleteFlag, entity.ExtFlag); return(SqlMap <Mod_Online_MajorCodeEntity> .ParseSql(sql).Execute(provider) > 0); }
/// <summary> /// 更新一条记录 /// </summary> /// <param name="entity">待更新的实体对象</param> /// <returns>true:更新成功 false:更新失败</returns> public virtual bool Update(Mod_Online_MajorCodeEntity entity) { SQL sql = SQL.Build("UPDATE Mod_Online_MajorCode SET ParentId=?,CodeId=?,CodeName=?,CreateTime=?,StateFlag=?,OrderIndex=?,DeleteFlag=?,ExtFlag=? WHERE Id=?", entity.ParentId, entity.CodeId, entity.CodeName, entity.CreateTime, entity.StateFlag, entity.OrderIndex, entity.DeleteFlag, entity.ExtFlag, entity.Id); return(SqlMap <Mod_Online_MajorCodeEntity> .ParseSql(sql).Execute() > 0); }
/// <summary> /// 删除一条记录 /// </summary> /// <param name="entity">待删除的实体对象</param> /// <returns>true:删除成功 false:删除失败</returns> public virtual bool Delete(Mod_Online_MajorCodeEntity entity) { SQL sql = SQL.Build("DELETE FROM Mod_Online_MajorCode WHERE Id=?", entity.Id); return(SqlMap <Mod_Online_MajorCodeEntity> .ParseSql(sql).Execute() > 0); }