public bool ReadExcelData() { string path = FileName; //Application exc = null; //Workbooks workBooks = null; //Workbook workBook = null; //Worksheet workSheet = null; //Range r = null; // object oMissing = System.Reflection.Missing.Value; SMT.Foundation.Core.BaseDAL dal = new SMT.Foundation.Core.BaseDAL(); try { dal.BeginTransaction(); //exc = new Application(); //exc.UserControl = true; //exc.Application.Workbooks.Open(path, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing); //workBooks = exc.Workbooks; //workBook = workBooks[1]; //workSheet = (Worksheet)workBook.Worksheets[WorkSheetIndex <= 0 ? 1 : WorkSheetIndex]; //int colCount = workSheet.UsedRange.Columns.Count; //获得列数 //int rowCount = workSheet.UsedRange.Rows.Count; //获得行数 //Dictionary<string, string> dictIndexs = new Dictionary<string, string>(); //dictIndexs.Add("A", "0"); //dictIndexs.Add("B", "1"); //dictIndexs.Add("C", "2"); //dictIndexs.Add("D", "3"); //dictIndexs.Add("E", "4"); //dictIndexs.Add("F", "5"); //dictIndexs.Add("G", "6"); //dictIndexs.Add("H", "7"); //dictIndexs.Add("I", "8"); //dictIndexs.Add("J", "9"); //dictIndexs.Add("K", "10"); //dictIndexs.Add("L", "11"); //dictIndexs.Add("M", "12"); //dictIndexs.Add("N", "13"); //dictIndexs.Add("O", "14"); //dictIndexs.Add("P", "15"); //dictIndexs.Add("Q", "16"); //dictIndexs.Add("R", "17"); //dictIndexs.Add("S", "18"); //dictIndexs.Add("T", "19"); //dictIndexs.Add("U", "20"); //dictIndexs.Add("V", "21"); //dictIndexs.Add("W", "22"); //dictIndexs.Add("X", "23"); //dictIndexs.Add("Y", "24"); //dictIndexs.Add("Z", "25"); Dictionary <string, Int32> dictIndexs = new Dictionary <string, Int32>(); dictIndexs.Add("A", 0); dictIndexs.Add("B", 1); dictIndexs.Add("C", 2); dictIndexs.Add("D", 3); dictIndexs.Add("E", 4); dictIndexs.Add("F", 5); dictIndexs.Add("G", 6); dictIndexs.Add("H", 7); dictIndexs.Add("I", 8); dictIndexs.Add("J", 9); dictIndexs.Add("K", 10); dictIndexs.Add("L", 11); dictIndexs.Add("M", 12); dictIndexs.Add("N", 13); dictIndexs.Add("O", 14); dictIndexs.Add("P", 15); dictIndexs.Add("Q", 16); dictIndexs.Add("R", 17); dictIndexs.Add("S", 18); dictIndexs.Add("T", 19); dictIndexs.Add("U", 20); dictIndexs.Add("V", 21); dictIndexs.Add("W", 22); dictIndexs.Add("X", 23); dictIndexs.Add("Y", 24); dictIndexs.Add("Z", 25); //excel读取开始行号 int beginRow = Convert.ToInt32(ImportConfig.STARTROW.GetValueOrDefault(2)); //excel读取结束行号 int endRow = Convert.ToInt32(ImportConfig.ENDROW.GetValueOrDefault(2)); //if (endRow > rowCount) // endRow = rowCount; int i = 1; System.Text.UTF8Encoding code = new UTF8Encoding(); using (StreamReader sr = new StreamReader(path, Encoding.GetEncoding("gb2312"))) { string line; // Read and display lines from the file until the end of // the file is reached. //while ((line = sr.ReadLine()) != null) while (!string.IsNullOrEmpty(line = sr.ReadLine())) { string[] lineTmp = line.Split(','); if (i >= beginRow && i <= endRow) { //创建一个实列 Type type = EntityInstance.GetType(); object entity = Activator.CreateInstance(type); //得到excel二维数组当前行中A列的值 //r = (Range)workSheet.Cells[i, "A"]; //string value = r.Text.ToString().Trim(); #region 实例化对像 foreach (T_HR_IMPORTSETDETAIL detail in ImportConfig.T_HR_IMPORTSETDETAIL) { if (!string.IsNullOrEmpty(detail.EXECELCOLUMN)) { //r = (Range)workSheet.Cells[i, detail.EXECELCOLUMN]; ////r = (Range)workSheet.Cells[i, "D"]; //string value = r.Text.ToString().Trim(); int index = 0; //把列名拆成字符数组 char[] tmps = detail.EXECELCOLUMN.ToUpper().ToCharArray(); if (tmps.Length == 1) { //列名是一个字母 直接从字典查出列对应的索引 index = dictIndexs[tmps[0].ToString()]; } else { //列名是双字符 计算出列对应的索引 index = (dictIndexs[tmps[0].ToString()] + 1) * 26 + (dictIndexs[tmps[1].ToString()]); } // int index = Convert.ToInt32(dictIndexs[detail.EXECELCOLUMN.ToUpper()]); string value = lineTmp[index].Trim(); PropertyInfo prop = type.GetProperty(detail.ENTITYCOLUMNCODE); if (prop != null) { if (!string.IsNullOrEmpty(value)) { if (prop.PropertyType.BaseType == typeof(System.ValueType)) { if (prop.PropertyType.ToString().Contains(typeof(DateTime).ToString())) { prop.SetValue(entity, Convert.ToDateTime(value), null); } else { decimal tmpValue; decimal.TryParse(value, out tmpValue); prop.SetValue(entity, tmpValue, null); } } else { prop.SetValue(entity, value, null); } } } } } #endregion PropertyInfo prop1 = type.GetProperty("PENSIONDETAILID"); prop1.SetValue(entity, Guid.NewGuid().ToString(), null); PropertyInfo prop2 = type.GetProperty("OWNERCOMPANYID"); prop2.SetValue(entity, paras["OWNERCOMPANYID"], null); PropertyInfo prop3 = type.GetProperty("OWNERDEPARTMENTID"); prop3.SetValue(entity, paras["OWNERDEPARTMENTID"], null); PropertyInfo prop4 = type.GetProperty("OWNERPOSTID"); prop4.SetValue(entity, paras["OWNERPOSTID"], null); PropertyInfo prop5 = type.GetProperty("OWNERID"); prop5.SetValue(entity, paras["OWNERID"], null); PropertyInfo prop6 = type.GetProperty("CREATEUSERID"); prop6.SetValue(entity, paras["CREATEUSERID"], null); PropertyInfo prop7 = type.GetProperty("PENSIONYEAR"); prop7.SetValue(entity, Convert.ToDecimal(paras["YEAR"]), null); PropertyInfo prop8 = type.GetProperty("PENSIONMOTH"); prop8.SetValue(entity, Convert.ToDecimal(paras["MONTH"]), null); PropertyInfo prop9 = type.GetProperty("CREATEDATE"); prop9.SetValue(entity, System.DateTime.Now, null); T_HR_PENSIONDETAIL tmpDetail = entity as T_HR_PENSIONDETAIL; string idnumber = string.Empty; if (string.IsNullOrEmpty(tmpDetail.IDNUMBER)) { idnumber = "Null0"; } else { idnumber = tmpDetail.IDNUMBER.ToString(); } //根据身份证号 查询员工 如果没有找到就不插入此条记录 var employee = from e in dal.GetObjects <T_HR_EMPLOYEE>() where e.IDNUMBER == idnumber && e.EDITSTATE == "1" select e; if (employee.Count() > 0) { //删除旧的记录 var oldDetail = from c in dal.GetObjects <T_HR_PENSIONDETAIL>() where c.PENSIONMOTH == tmpDetail.PENSIONMOTH && c.PENSIONYEAR == tmpDetail.PENSIONYEAR && c.IDNUMBER == idnumber select c; if (oldDetail.Count() > 0) { dal.DeleteFromContext(oldDetail.FirstOrDefault()); } T_HR_EMPLOYEE entCurEmp = employee.FirstOrDefault(); if (tmpDetail.EMPLOYEEID != entCurEmp.EMPLOYEEID) { tmpDetail.EMPLOYEEID = entCurEmp.EMPLOYEEID; entity = tmpDetail; } //插入数据到数据库 dal.AddToContext(entity); // dal.Add(entity); } else { Tracer.Debug("PensionImport:" + i.ToString() + "行,没有员工身份证为此号码:" + idnumber); } } i++; } } dal.SaveContextChanges(); dal.CommitTransaction(); CommDal <T_HR_PENSIONDETAIL> cdal = new CommDal <T_HR_PENSIONDETAIL>(); // string strSql = @" update t_hr_pensiondetail a set a.pensionmasterid = (select pensionmasterid from t_hr_pensionmaster b where a.computerno // = b.computerno),a.employeeid= (select employeeid from t_hr_pensionmaster b where a.computerno = b.computerno) // ,a.CARDID= (select CARDID from t_hr_pensionmaster b where a.computerno = b.computerno)"; //string strSql = @" update t_hr_pensiondetail a set a.employeeid= (select employeeid from t_hr_employee b where a.idnumber = b.IDNUMBER)"; //string strSql = " update t_hr_pensiondetail a set a.pensionmasterid = (select pensionmasterid from t_hr_pensionmaster b where a.computerno" // +"= b.computerno and b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"'),a.employeeid= (select employeeid from t_hr_pensionmaster b where a.computerno = b.computerno and b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"')" // +" ,a.CARDID= (select CARDID from t_hr_pensionmaster b where a.computerno = b.computerno b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"')" // +" ,a.OWNERID=(select employeeid from t_hr_pensionmaster b where a.computerno = b.computerno b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"')" // +" ,a.OWNERPOSTID=(select c.OWNERPOSTID from t_hr_pensionmaster b ,t_hr_employee c where a.computerno = b.computerno and b.employeeid= c.employeeid + b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"') " // +" ,a.OWNERDEPARTMENTID=(select c.OWNERDEPARTMENTID from t_hr_pensionmaster b ,t_hr_employee c where a.computerno = b.computerno and b.employeeid= c.employeeid b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"')" // +" ,a.OWNERCOMPANYID=(select c.OWNERCOMPANYID from t_hr_pensionmaster b ,t_hr_employee c where a.computerno = b.computerno and b.employeeid= c.employeeid b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"')" // +" where a.PENSIONYEAR='" + paras["YEAR"] + "' and a.PENSIONMOTH='" + paras["MONTH"] + "'"; //cdal.ExecuteCustomerSql(strSql); //strSql = "update t_hr_pensiondetail a set a.pensionmasterid = (select pensionmasterid from t_hr_pensionmaster b where a.employeeid = b.employeeid),a.cardid =(select b.cardid from t_hr_pensionmaster b where a.employeeid = b.employeeid)"; string strSql = " update t_hr_pensiondetail a set a.pensionmasterid = (select pensionmasterid from t_hr_pensionmaster b,t_hr_employee c where a.IDNUMBER= c.IDNUMBER and b.employeeid=c.employeeid and b.editstate=1)" + " ,a.employeeid= (select employeeid from t_hr_employee c where a.IDNUMBER = c.IDNUMBER )" + " ,a.CARDID= (select CARDID from t_hr_pensionmaster b ,t_hr_employee c where a.IDNUMBER = c.IDNUMBER and b.employeeid=c.employeeid and b.editstate=1)" + " ,a.OWNERID=(select employeeid from t_hr_employee c where a.IDNUMBER = c.IDNUMBER )" + " ,a.OWNERPOSTID=(select c.OWNERPOSTID from t_hr_employee c where a.IDNUMBER= c.IDNUMBER ) " + " ,a.OWNERDEPARTMENTID=(select c.OWNERDEPARTMENTID from t_hr_employee c where a.IDNUMBER= c.IDNUMBER )" + " ,a.OWNERCOMPANYID=(select c.OWNERCOMPANYID from t_hr_employee c where a.IDNUMBER = c.IDNUMBER )" + " where a.PENSIONYEAR='" + paras["YEAR"] + "' and a.PENSIONMOTH='" + paras["MONTH"] + "' and a.CREATEUSERID='" + paras["CREATEUSERID"] + "'"; cdal.ExecuteCustomerSql(strSql); return(true); } catch (Exception ex) { //TODO: 写成导入日志 //throw ex; Tracer.Debug("My error" + ex.Message); dal.RollbackTransaction(); return(false); } finally { //关闭当前的excel进程 //exc.Application.Workbooks.Close(); //exc.Quit(); //System.Runtime.InteropServices.Marshal.ReleaseComObject(exc); //IntPtr t = new IntPtr(exc.Hwnd); //exc = null; //int kid = 0; //GetWindowThreadProcessId(t, out kid); //System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(kid); //p.Kill(); } }
public bool ReadExcelData() { string path = FileName; //Application exc = null; //Workbooks workBooks = null; //Workbook workBook = null; //Worksheet workSheet = null; //Range r = null; // object oMissing = System.Reflection.Missing.Value; SMT.Foundation.Core.BaseDAL dal = new SMT.Foundation.Core.BaseDAL(); try { dal.BeginTransaction(); //exc = new Application(); //exc.UserControl = true; //exc.Application.Workbooks.Open(path, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing); //workBooks = exc.Workbooks; //workBook = workBooks[1]; //workSheet = (Worksheet)workBook.Worksheets[WorkSheetIndex <= 0 ? 1 : WorkSheetIndex]; //int colCount = workSheet.UsedRange.Columns.Count; //获得列数 //int rowCount = workSheet.UsedRange.Rows.Count; //获得行数 //Dictionary<string, string> dictIndexs = new Dictionary<string, string>(); //dictIndexs.Add("A", "0"); //dictIndexs.Add("B", "1"); //dictIndexs.Add("C", "2"); //dictIndexs.Add("D", "3"); //dictIndexs.Add("E", "4"); //dictIndexs.Add("F", "5"); //dictIndexs.Add("G", "6"); //dictIndexs.Add("H", "7"); //dictIndexs.Add("I", "8"); //dictIndexs.Add("J", "9"); //dictIndexs.Add("K", "10"); //dictIndexs.Add("L", "11"); //dictIndexs.Add("M", "12"); //dictIndexs.Add("N", "13"); //dictIndexs.Add("O", "14"); //dictIndexs.Add("P", "15"); //dictIndexs.Add("Q", "16"); //dictIndexs.Add("R", "17"); //dictIndexs.Add("S", "18"); //dictIndexs.Add("T", "19"); //dictIndexs.Add("U", "20"); //dictIndexs.Add("V", "21"); //dictIndexs.Add("W", "22"); //dictIndexs.Add("X", "23"); //dictIndexs.Add("Y", "24"); //dictIndexs.Add("Z", "25"); Dictionary<string, Int32> dictIndexs = new Dictionary<string, Int32>(); dictIndexs.Add("A", 0); dictIndexs.Add("B", 1); dictIndexs.Add("C", 2); dictIndexs.Add("D", 3); dictIndexs.Add("E", 4); dictIndexs.Add("F", 5); dictIndexs.Add("G", 6); dictIndexs.Add("H", 7); dictIndexs.Add("I", 8); dictIndexs.Add("J", 9); dictIndexs.Add("K", 10); dictIndexs.Add("L", 11); dictIndexs.Add("M", 12); dictIndexs.Add("N", 13); dictIndexs.Add("O", 14); dictIndexs.Add("P", 15); dictIndexs.Add("Q", 16); dictIndexs.Add("R", 17); dictIndexs.Add("S", 18); dictIndexs.Add("T", 19); dictIndexs.Add("U", 20); dictIndexs.Add("V", 21); dictIndexs.Add("W", 22); dictIndexs.Add("X", 23); dictIndexs.Add("Y", 24); dictIndexs.Add("Z", 25); //excel读取开始行号 int beginRow = Convert.ToInt32(ImportConfig.STARTROW.GetValueOrDefault(2)); //excel读取结束行号 int endRow = Convert.ToInt32(ImportConfig.ENDROW.GetValueOrDefault(2)); //if (endRow > rowCount) // endRow = rowCount; int i = 1; System.Text.UTF8Encoding code = new UTF8Encoding(); using (StreamReader sr = new StreamReader(path, Encoding.GetEncoding("gb2312"))) { string line; // Read and display lines from the file until the end of // the file is reached. //while ((line = sr.ReadLine()) != null) while (!string.IsNullOrEmpty(line = sr.ReadLine())) { string[] lineTmp = line.Split(','); if (i >= beginRow && i <= endRow) { //创建一个实列 Type type = EntityInstance.GetType(); object entity = Activator.CreateInstance(type); //得到excel二维数组当前行中A列的值 //r = (Range)workSheet.Cells[i, "A"]; //string value = r.Text.ToString().Trim(); #region 实例化对像 foreach (T_HR_IMPORTSETDETAIL detail in ImportConfig.T_HR_IMPORTSETDETAIL) { if (!string.IsNullOrEmpty(detail.EXECELCOLUMN)) { //r = (Range)workSheet.Cells[i, detail.EXECELCOLUMN]; ////r = (Range)workSheet.Cells[i, "D"]; //string value = r.Text.ToString().Trim(); int index = 0; //把列名拆成字符数组 char[] tmps = detail.EXECELCOLUMN.ToUpper().ToCharArray(); if (tmps.Length == 1) { //列名是一个字母 直接从字典查出列对应的索引 index = dictIndexs[tmps[0].ToString()]; } else { //列名是双字符 计算出列对应的索引 index = (dictIndexs[tmps[0].ToString()] + 1) * 26 + (dictIndexs[tmps[1].ToString()]); } // int index = Convert.ToInt32(dictIndexs[detail.EXECELCOLUMN.ToUpper()]); string value = lineTmp[index].Trim(); PropertyInfo prop = type.GetProperty(detail.ENTITYCOLUMNCODE); if (prop != null) { if (!string.IsNullOrEmpty(value)) { if (prop.PropertyType.BaseType == typeof(System.ValueType)) { if (prop.PropertyType.ToString().Contains(typeof(DateTime).ToString())) { prop.SetValue(entity, Convert.ToDateTime(value), null); } else { decimal tmpValue; decimal.TryParse(value, out tmpValue); prop.SetValue(entity, tmpValue, null); } } else { prop.SetValue(entity, value, null); } } } } } #endregion PropertyInfo prop1 = type.GetProperty("PENSIONDETAILID"); prop1.SetValue(entity, Guid.NewGuid().ToString(), null); PropertyInfo prop2 = type.GetProperty("OWNERCOMPANYID"); prop2.SetValue(entity, paras["OWNERCOMPANYID"], null); PropertyInfo prop3 = type.GetProperty("OWNERDEPARTMENTID"); prop3.SetValue(entity, paras["OWNERDEPARTMENTID"], null); PropertyInfo prop4 = type.GetProperty("OWNERPOSTID"); prop4.SetValue(entity, paras["OWNERPOSTID"], null); PropertyInfo prop5 = type.GetProperty("OWNERID"); prop5.SetValue(entity, paras["OWNERID"], null); PropertyInfo prop6 = type.GetProperty("CREATEUSERID"); prop6.SetValue(entity, paras["CREATEUSERID"], null); PropertyInfo prop7 = type.GetProperty("PENSIONYEAR"); prop7.SetValue(entity, Convert.ToDecimal(paras["YEAR"]), null); PropertyInfo prop8 = type.GetProperty("PENSIONMOTH"); prop8.SetValue(entity, Convert.ToDecimal(paras["MONTH"]), null); PropertyInfo prop9 = type.GetProperty("CREATEDATE"); prop9.SetValue(entity, System.DateTime.Now, null); T_HR_PENSIONDETAIL tmpDetail = entity as T_HR_PENSIONDETAIL; string idnumber = string.Empty; if (string.IsNullOrEmpty(tmpDetail.IDNUMBER)) { idnumber = "Null0"; } else { idnumber = tmpDetail.IDNUMBER.ToString(); } //根据身份证号 查询员工 如果没有找到就不插入此条记录 var employee = from e in dal.GetObjects<T_HR_EMPLOYEE>() where e.IDNUMBER == idnumber && e.EDITSTATE == "1" select e; if (employee.Count() > 0) { //删除旧的记录 var oldDetail = from c in dal.GetObjects<T_HR_PENSIONDETAIL>() where c.PENSIONMOTH == tmpDetail.PENSIONMOTH && c.PENSIONYEAR == tmpDetail.PENSIONYEAR && c.IDNUMBER == idnumber select c; if (oldDetail.Count() > 0) { dal.DeleteFromContext(oldDetail.FirstOrDefault()); } T_HR_EMPLOYEE entCurEmp = employee.FirstOrDefault(); if (tmpDetail.EMPLOYEEID != entCurEmp.EMPLOYEEID) { tmpDetail.EMPLOYEEID = entCurEmp.EMPLOYEEID; entity = tmpDetail; } //插入数据到数据库 dal.AddToContext(entity); // dal.Add(entity); } else { Tracer.Debug("PensionImport:" + i.ToString() + "行,没有员工身份证为此号码:" + idnumber); } } i++; } } dal.SaveContextChanges(); dal.CommitTransaction(); CommDal<T_HR_PENSIONDETAIL> cdal = new CommDal<T_HR_PENSIONDETAIL>(); // string strSql = @" update t_hr_pensiondetail a set a.pensionmasterid = (select pensionmasterid from t_hr_pensionmaster b where a.computerno // = b.computerno),a.employeeid= (select employeeid from t_hr_pensionmaster b where a.computerno = b.computerno) // ,a.CARDID= (select CARDID from t_hr_pensionmaster b where a.computerno = b.computerno)"; //string strSql = @" update t_hr_pensiondetail a set a.employeeid= (select employeeid from t_hr_employee b where a.idnumber = b.IDNUMBER)"; //string strSql = " update t_hr_pensiondetail a set a.pensionmasterid = (select pensionmasterid from t_hr_pensionmaster b where a.computerno" // +"= b.computerno and b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"'),a.employeeid= (select employeeid from t_hr_pensionmaster b where a.computerno = b.computerno and b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"')" // +" ,a.CARDID= (select CARDID from t_hr_pensionmaster b where a.computerno = b.computerno b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"')" // +" ,a.OWNERID=(select employeeid from t_hr_pensionmaster b where a.computerno = b.computerno b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"')" // +" ,a.OWNERPOSTID=(select c.OWNERPOSTID from t_hr_pensionmaster b ,t_hr_employee c where a.computerno = b.computerno and b.employeeid= c.employeeid + b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"') " // +" ,a.OWNERDEPARTMENTID=(select c.OWNERDEPARTMENTID from t_hr_pensionmaster b ,t_hr_employee c where a.computerno = b.computerno and b.employeeid= c.employeeid b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"')" // +" ,a.OWNERCOMPANYID=(select c.OWNERCOMPANYID from t_hr_pensionmaster b ,t_hr_employee c where a.computerno = b.computerno and b.employeeid= c.employeeid b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"')" // +" where a.PENSIONYEAR='" + paras["YEAR"] + "' and a.PENSIONMOTH='" + paras["MONTH"] + "'"; //cdal.ExecuteCustomerSql(strSql); //strSql = "update t_hr_pensiondetail a set a.pensionmasterid = (select pensionmasterid from t_hr_pensionmaster b where a.employeeid = b.employeeid),a.cardid =(select b.cardid from t_hr_pensionmaster b where a.employeeid = b.employeeid)"; string strSql = " update t_hr_pensiondetail a set a.pensionmasterid = (select pensionmasterid from t_hr_pensionmaster b,t_hr_employee c where a.IDNUMBER= c.IDNUMBER and b.employeeid=c.employeeid and b.editstate=1)" + " ,a.employeeid= (select employeeid from t_hr_employee c where a.IDNUMBER = c.IDNUMBER )" + " ,a.CARDID= (select CARDID from t_hr_pensionmaster b ,t_hr_employee c where a.IDNUMBER = c.IDNUMBER and b.employeeid=c.employeeid and b.editstate=1)" + " ,a.OWNERID=(select employeeid from t_hr_employee c where a.IDNUMBER = c.IDNUMBER )" + " ,a.OWNERPOSTID=(select c.OWNERPOSTID from t_hr_employee c where a.IDNUMBER= c.IDNUMBER ) " + " ,a.OWNERDEPARTMENTID=(select c.OWNERDEPARTMENTID from t_hr_employee c where a.IDNUMBER= c.IDNUMBER )" + " ,a.OWNERCOMPANYID=(select c.OWNERCOMPANYID from t_hr_employee c where a.IDNUMBER = c.IDNUMBER )" + " where a.PENSIONYEAR='" + paras["YEAR"] + "' and a.PENSIONMOTH='" + paras["MONTH"] + "' and a.CREATEUSERID='" + paras["CREATEUSERID"] + "'"; cdal.ExecuteCustomerSql(strSql); return true; } catch (Exception ex) { //TODO: 写成导入日志 //throw ex; Tracer.Debug("My error" + ex.Message); dal.RollbackTransaction(); return false; } finally { //关闭当前的excel进程 //exc.Application.Workbooks.Close(); //exc.Quit(); //System.Runtime.InteropServices.Marshal.ReleaseComObject(exc); //IntPtr t = new IntPtr(exc.Hwnd); //exc = null; //int kid = 0; //GetWindowThreadProcessId(t, out kid); //System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(kid); //p.Kill(); } }
/// <summary> /// 批量添加员工社保 /// </summary> /// <param name="listPension">社保集合</param> /// <param name="StrCity">城市</param> /// <param name="StrCompanyid">公司ID</param> /// <param name="StrYear">年</param> /// <param name="StrMonth">月</param> /// <param name="StrCreateUserId">创建用户</param> /// <param name="StrMsg">返回的消息,用来存放不合格记录的身份证号</param> /// <returns></returns> public bool BatchAddPensionDetail(List <T_HR_PENSIONDETAIL> listPension, Dictionary <string, string> paras, ref string StrMsg) { bool IsResult = false; SMT.Foundation.Log.Tracer.Debug("开始导入员工社保。"); try { dal.BeginTransaction(); ImportSetMasterBLL bll = new ImportSetMasterBLL(); T_HR_IMPORTSETMASTER master = bll.GetImportSetMasterByEntityCode("T_HR_PENSIONDETAIL", paras["CITY"], paras["OWNERCOMPANYID"]); if (master != null) { //用来记录身份证信息不存在的数量 int i = 0; foreach (var ent in listPension) { var employee = from e in dal.GetObjects <T_HR_EMPLOYEE>() where e.IDNUMBER == ent.IDNUMBER && e.EDITSTATE == "1" select e; if (employee.Count() > 0) { //删除旧的记录 var oldDetail = from c in dal.GetObjects <T_HR_PENSIONDETAIL>() where c.PENSIONMOTH == ent.PENSIONMOTH && c.PENSIONYEAR == ent.PENSIONYEAR && c.IDNUMBER == ent.IDNUMBER select c; if (oldDetail.Count() > 0) { dal.DeleteFromContext(oldDetail.FirstOrDefault()); } T_HR_EMPLOYEE entCurEmp = employee.FirstOrDefault(); if (ent.EMPLOYEEID != entCurEmp.EMPLOYEEID) { ent.EMPLOYEEID = entCurEmp.EMPLOYEEID; } //插入数据到数据库 dal.AddToContext(ent); } else { i++; SMT.Foundation.Log.Tracer.Debug("PensionImport:" + i.ToString() + "行,没有员工身份证为此号码:" + ent.IDNUMBER); StrMsg += "身份证号为:" + ent.IDNUMBER + "没有找到员工记录\n"; } } if (i > 0) { StrMsg += "共有" + i.ToString() + " 位员工社保导入错误。"; } int k = dal.SaveContextChanges(); if (k > 0) { dal.CommitTransaction(); IsResult = true; CommDal <T_HR_PENSIONDETAIL> cdal = new CommDal <T_HR_PENSIONDETAIL>(); string strSql = " update t_hr_pensiondetail a set a.pensionmasterid = (select pensionmasterid from t_hr_pensionmaster b,t_hr_employee c where a.IDNUMBER= c.IDNUMBER and b.employeeid=c.employeeid and b.editstate=1)" + " ,a.employeeid= (select employeeid from t_hr_employee c where a.IDNUMBER = c.IDNUMBER )" + " ,a.CARDID= (select CARDID from t_hr_pensionmaster b ,t_hr_employee c where a.IDNUMBER = c.IDNUMBER and b.employeeid=c.employeeid and b.editstate=1)" + " ,a.OWNERID=(select employeeid from t_hr_employee c where a.IDNUMBER = c.IDNUMBER )" + " ,a.OWNERPOSTID=(select c.OWNERPOSTID from t_hr_employee c where a.IDNUMBER= c.IDNUMBER ) " + " ,a.OWNERDEPARTMENTID=(select c.OWNERDEPARTMENTID from t_hr_employee c where a.IDNUMBER= c.IDNUMBER )" + " ,a.OWNERCOMPANYID=(select c.OWNERCOMPANYID from t_hr_employee c where a.IDNUMBER = c.IDNUMBER )" + " where a.PENSIONYEAR='" + paras["YEAR"] + "' and a.PENSIONMOTH='" + paras["MONTH"] + "' and a.CREATEUSERID='" + paras["CREATEUSERID"] + "'"; //修改社保的所属人信息 cdal.ExecuteCustomerSql(strSql); } else { dal.RollbackTransaction(); } } else { StrMsg = "社保主表记录不存在。"; } } catch (Exception ex) { SMT.Foundation.Log.Tracer.Debug(System.DateTime.Now.ToString() + " ImportPensionByImportExcel:" + ex.Message); dal.RollbackTransaction(); } return(IsResult); }