/// <summary> /// 修改实体审核状态 /// </summary> /// <param name="strEntityName">实体名</param> /// <param name="EntityKeyName">主键名</param> /// <param name="EntityKeyValue">主键值</param> /// <param name="CheckState">审核状态</param> public int UpdateCheckState(string strEntityName, string EntityKeyName, string EntityKeyValue, string CheckState) { using (CommDal <TEntity> dal = new CommDal <TEntity>()) { int intResult = 0; Tracer.Debug("进入了COMMONBLL,实体名:" + strEntityName + ",DateTime:" + System.DateTime.Now.ToString()); Tracer.Debug("实体ID名:" + EntityKeyName + "实体主键值:" + EntityKeyValue); Tracer.Debug("审核的状态:" + CheckState); switch (strEntityName) { case "T_FB_BORROWAPPLYMASTER": //借款申请 BorrowApplyMasterBLL bll = new BorrowApplyMasterBLL(); intResult = bll.GetBorrowApplyForMobile(EntityKeyValue, CheckState); break; case "T_FB_CHARGEAPPLYMASTER": ChargeApplyMasterBLL Chargebll = new ChargeApplyMasterBLL(); intResult = Chargebll.GetChargeApplyForMobile(EntityKeyValue, CheckState); break; case "T_FB_REPAYAPPLYMASTER": RepayApplyMasterBLL Repaybll = new RepayApplyMasterBLL(); intResult = Repaybll.GetRepayApplyForMobile(EntityKeyValue, CheckState); break; default: intResult = dal.UpdateCheckState(strEntityName, EntityKeyName, EntityKeyValue, CheckState); break; } return(intResult); } }
public byte[] GetEmployeePhoto(string employeeid) { using (CommDal <T_HR_EMPLOYEE> cdl = new CommDal <T_HR_EMPLOYEE>()) { var q = from ent in cdl.GetObjects <T_HR_EMPLOYEE>() where ent.EMPLOYEEID == employeeid select ent.PHOTO; if (q.Count() > 0) { return(q.FirstOrDefault()); } else { return(null); } } }
/// <summary> /// 获取所有部门 /// </summary> /// <returns>所有有效的部门</returns> public List<DepartmentModel> GetAllDepartment() { using (CommDal<T_HR_DEPARTMENT> cdl = new CommDal<T_HR_DEPARTMENT>()) { var departmentList = from v in cdl.GetObjects() .Include("T_HR_COMPANY") .Include("T_HR_DEPARTMENTDICTIONARY") .Where(s => s.EDITSTATE == "1" && s.CHECKSTATE == "2") select new DepartmentModel { DeptID = v.DEPARTMENTID, DepartName = v.T_HR_DEPARTMENTDICTIONARY.DEPARTMENTNAME, CompanyID = v.T_HR_COMPANY.COMPANYID, ParentID = v.FATHERID, SORTINDEX = v.SORTINDEX, FatherType = v.FATHERTYPE }; return departmentList.ToList(); } }
/// <summary> /// 获取所有部门 /// </summary> /// <returns>所有有效的部门</returns> public List <DepartmentModel> GetAllDepartment() { using (CommDal <T_HR_DEPARTMENT> cdl = new CommDal <T_HR_DEPARTMENT>()) { var departmentList = from v in cdl.GetObjects() .Include("T_HR_COMPANY") .Include("T_HR_DEPARTMENTDICTIONARY") .Where(s => s.EDITSTATE == "1" && s.CHECKSTATE == "2") select new DepartmentModel { DeptID = v.DEPARTMENTID, DepartName = v.T_HR_DEPARTMENTDICTIONARY.DEPARTMENTNAME, CompanyID = v.T_HR_COMPANY.COMPANYID, ParentID = v.FATHERID, SORTINDEX = v.SORTINDEX, FatherType = v.FATHERTYPE }; return(departmentList.ToList()); } }
/// <summary> /// 获取员工 /// </summary> /// <returns>所有有效的员工</returns> public List <EmployeeModel> GetEmployeeComm() { using (CommDal <T_HR_EMPLOYEE> cdl = new CommDal <T_HR_EMPLOYEE>()) { var ents = from o in cdl.GetObjects() join ep in cdl.GetObjects <T_HR_EMPLOYEEPOST>() on o.EMPLOYEEID equals ep.T_HR_EMPLOYEE.EMPLOYEEID join p in cdl.GetObjects <T_HR_POST>() on ep.T_HR_POST.POSTID equals p.POSTID join pd in cdl.GetObjects <T_HR_POSTDICTIONARY>() on p.T_HR_POSTDICTIONARY.POSTDICTIONARYID equals pd.POSTDICTIONARYID join d in cdl.GetObjects <T_HR_DEPARTMENT>() on p.T_HR_DEPARTMENT.DEPARTMENTID equals d.DEPARTMENTID join c in cdl.GetObjects <T_HR_COMPANY>() on d.T_HR_COMPANY.COMPANYID equals c.COMPANYID join cd in cdl.GetObjects <T_HR_DEPARTMENTDICTIONARY>() on d.T_HR_DEPARTMENTDICTIONARY.DEPARTMENTDICTIONARYID equals cd.DEPARTMENTDICTIONARYID where o.EDITSTATE == "1" && o.EMPLOYEESTATE != "2" && ep.CHECKSTATE == "2" && ep.EDITSTATE == "1" select new EmployeeModel { CompanyName = c.CNAME, EmployeeId = o.EMPLOYEEID, EmployeeName = o.EMPLOYEECNAME, LoginAccount = o.EMPLOYEEENAME, Sex = o.SEX, Age = "", Email = o.EMAIL, Address = o.FAMILYADDRESS, AddCode = "", Mobile = o.MOBILE, Tel = o.OFFICEPHONE, Nation = "", Province = "", City = "", Remark = o.REMARK, DeptID = d.DEPARTMENTID, DepartMentName = cd.DEPARTMENTNAME, PostName = pd.POSTNAME, PostID = p.POSTID, IsAgencePost = ep.ISAGENCY }; return(ents.ToList()); } }
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> /// <returns>所有有效的员工</returns> public List<EmployeeModel> GetEmployeeComm() { using (CommDal<T_HR_EMPLOYEE> cdl = new CommDal<T_HR_EMPLOYEE>()) { var ents = from o in cdl.GetObjects() join ep in cdl.GetObjects<T_HR_EMPLOYEEPOST>() on o.EMPLOYEEID equals ep.T_HR_EMPLOYEE.EMPLOYEEID join p in cdl.GetObjects<T_HR_POST>() on ep.T_HR_POST.POSTID equals p.POSTID join pd in cdl.GetObjects<T_HR_POSTDICTIONARY>() on p.T_HR_POSTDICTIONARY.POSTDICTIONARYID equals pd.POSTDICTIONARYID join d in cdl.GetObjects<T_HR_DEPARTMENT>() on p.T_HR_DEPARTMENT.DEPARTMENTID equals d.DEPARTMENTID join c in cdl.GetObjects<T_HR_COMPANY>() on d.T_HR_COMPANY.COMPANYID equals c.COMPANYID join cd in cdl.GetObjects<T_HR_DEPARTMENTDICTIONARY>() on d.T_HR_DEPARTMENTDICTIONARY.DEPARTMENTDICTIONARYID equals cd.DEPARTMENTDICTIONARYID where o.EDITSTATE == "1" && o.EMPLOYEESTATE != "2" && ep.CHECKSTATE == "2" && ep.EDITSTATE == "1" select new EmployeeModel { CompanyName = c.CNAME, EmployeeId = o.EMPLOYEEID, EmployeeName = o.EMPLOYEECNAME, LoginAccount = o.EMPLOYEEENAME, Sex = o.SEX, Age = "", Email = o.EMAIL, Address = o.FAMILYADDRESS, AddCode = "", Mobile = o.MOBILE, Tel = o.OFFICEPHONE, Nation = "", Province = "", City = "", Remark = o.REMARK, DeptID = d.DEPARTMENTID, DepartMentName = cd.DEPARTMENTNAME, PostName = pd.POSTNAME, PostID = p.POSTID, IsAgencePost=ep.ISAGENCY }; return ents.ToList(); } }
public byte[] GetEmployeePhoto(string employeeid) { using (CommDal<T_HR_EMPLOYEE> cdl = new CommDal<T_HR_EMPLOYEE>()) { var q = from ent in cdl.GetObjects<T_HR_EMPLOYEE>() where ent.EMPLOYEEID == employeeid select ent.PHOTO; if (q.Count() > 0) return q.FirstOrDefault(); else return null; } }
/// <summary> /// 根据帐号获取员工信息 /// </summary> /// <param name="loginAccount"></param> /// <returns></returns> public string GetEmployeeInfo(string loginAccount) { StringBuilder StrReturn = new StringBuilder(); XmlWriterSettings settings = new XmlWriterSettings(); settings.Indent = true; settings.OmitXmlDeclaration = true; try { using (XmlWriter writer = XmlWriter.Create(StrReturn, settings)) { using (CommDal<T_HR_EMPLOYEE> cdl = new CommDal<T_HR_EMPLOYEE>()) { var ents = from ent in cdl.GetObjects<T_HR_EMPLOYEE>() where ent.EMPLOYEEENAME == loginAccount select ent; if (ents.Count() > 0) { EmployeeModel employee = new EmployeeModel(); if (ents.FirstOrDefault() != null) { T_HR_EMPLOYEE UserInfo = ents.FirstOrDefault(); employee = GetSingelEmployee(UserInfo.EMPLOYEEID); if (employee != null) { writer.WriteStartElement("BizRegReq"); writer.WriteStartElement("Employee"); writer.WriteAttributeString("EmployeeId", employee.EmployeeId); writer.WriteAttributeString("EmployeeName", employee.EmployeeName); writer.WriteAttributeString("LoginAccount", employee.LoginAccount); writer.WriteAttributeString("Sex", employee.Sex); //writer.WriteAttributeString("Age", ""); writer.WriteAttributeString("PostName", employee.PostName); writer.WriteAttributeString("Email", employee.Email); writer.WriteAttributeString("Address", employee.Address); writer.WriteAttributeString("AddCode", "");//没有默认为空 writer.WriteAttributeString("Mobile", employee.Mobile); writer.WriteAttributeString("Tel", employee.Tel); writer.WriteAttributeString("Nation", ""); writer.WriteAttributeString("Province", ""); writer.WriteAttributeString("City", ""); writer.WriteAttributeString("PostID", employee.PostID); writer.WriteAttributeString("Remark", employee.Remark); writer.WriteAttributeString("CompanyName", employee.CompanyName); writer.WriteEndElement();//完成Employee节点 writer.WriteEndElement();//完成BizRegReq节点 } else { ErrorMessage(writer, "没有获取到该员工信息"); } } else { ErrorMessage(writer, "没有获取到该员工信息"); } } else { ErrorMessage(writer, "没有获取到该员工信息"); } writer.Flush(); } } } catch (Exception ex) { using (XmlWriter catchError = XmlWriter.Create(StrReturn, settings)) { ErrorMessage(catchError, "服务器错误"); Tracer.Debug("即时通讯-GetEmployeeInfo:" + ex.ToString() + System.DateTime.Now); catchError.Flush(); } } return StrReturn.ToString().Replace("\r", "").Replace("\n", ""); }
/// <summary> /// 根据帐号获取员工信息 /// </summary> /// <param name="loginAccount"></param> /// <returns></returns> public string GetEmployeeInfo(string loginAccount) { StringBuilder StrReturn = new StringBuilder(); XmlWriterSettings settings = new XmlWriterSettings(); settings.Indent = true; settings.OmitXmlDeclaration = true; try { using (XmlWriter writer = XmlWriter.Create(StrReturn, settings)) { using (CommDal <T_HR_EMPLOYEE> cdl = new CommDal <T_HR_EMPLOYEE>()) { var ents = from ent in cdl.GetObjects <T_HR_EMPLOYEE>() where ent.EMPLOYEEENAME == loginAccount select ent; if (ents.Count() > 0) { EmployeeModel employee = new EmployeeModel(); if (ents.FirstOrDefault() != null) { T_HR_EMPLOYEE UserInfo = ents.FirstOrDefault(); employee = GetSingelEmployee(UserInfo.EMPLOYEEID); if (employee != null) { writer.WriteStartElement("BizRegReq"); writer.WriteStartElement("Employee"); writer.WriteAttributeString("EmployeeId", employee.EmployeeId); writer.WriteAttributeString("EmployeeName", employee.EmployeeName); writer.WriteAttributeString("LoginAccount", employee.LoginAccount); writer.WriteAttributeString("Sex", employee.Sex); //writer.WriteAttributeString("Age", ""); writer.WriteAttributeString("PostName", employee.PostName); writer.WriteAttributeString("Email", employee.Email); writer.WriteAttributeString("Address", employee.Address); writer.WriteAttributeString("AddCode", "");//没有默认为空 writer.WriteAttributeString("Mobile", employee.Mobile); writer.WriteAttributeString("Tel", employee.Tel); writer.WriteAttributeString("Nation", ""); writer.WriteAttributeString("Province", ""); writer.WriteAttributeString("City", ""); writer.WriteAttributeString("PostID", employee.PostID); writer.WriteAttributeString("Remark", employee.Remark); writer.WriteAttributeString("CompanyName", employee.CompanyName); writer.WriteEndElement(); //完成Employee节点 writer.WriteEndElement(); //完成BizRegReq节点 } else { ErrorMessage(writer, "没有获取到该员工信息"); } } else { ErrorMessage(writer, "没有获取到该员工信息"); } } else { ErrorMessage(writer, "没有获取到该员工信息"); } writer.Flush(); } } } catch (Exception ex) { using (XmlWriter catchError = XmlWriter.Create(StrReturn, settings)) { ErrorMessage(catchError, "服务器错误"); Tracer.Debug("即时通讯-GetEmployeeInfo:" + ex.ToString() + System.DateTime.Now); catchError.Flush(); } } return(StrReturn.ToString().Replace("\r", "").Replace("\n", "")); }
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); }
/// <summary> /// 根据设置的日历重新初始化考勤 /// </summary> /// <param name="entVacRd"></param> private void reInitAttandeceRecordWithOutWorkDaySet(string VACATIONID) { try { using (dal = new CommDal <T_HR_VACATIONSET>()) { var q = from ent in dal.GetObjects <T_HR_VACATIONSET>().Include("T_HR_OUTPLANDAYS") where ent.VACATIONID == VACATIONID select ent; var entVacRd = q.FirstOrDefault(); if (entVacRd == null) { Tracer.Debug("根据设置的日历重新初始化考勤跳过,获取的T_HR_VACATIONSET为空,id:" + VACATIONID); } List <T_HR_OUTPLANDAYS> entOutPlanDays = entVacRd.T_HR_OUTPLANDAYS.ToList(); bool needInitCompanyAttanceDayAgain = false; string strCurYearMonth = string.Empty; if (entOutPlanDays.Count == 0) { Tracer.Debug("根据设置的日历重新初始化考勤跳过,明细为空"); } foreach (T_HR_OUTPLANDAYS item in entOutPlanDays) { #region 处理考勤初始化 if (item.DAYTYPE == (Convert.ToInt32(Common.OutPlanDaysType.Vacation) + 1).ToString()) { try { string sql = @"delete smthrm.t_hr_attendancerecord a where a.ownercompanyid = '" + entVacRd.ASSIGNEDOBJECTID + @"' and a.attendancedate >= To_Date('" + item.STARTDATE.Value.ToString("yyyy-MM-dd") + @"', 'yyyy-MM-dd') and a.attendancedate <= To_Date('" + item.ENDDATE.Value.ToString("yyyy-MM-dd") + @"', 'yyyy-MM-dd')"; var attdel = from ent in dal.GetObjects <T_HR_ATTENDANCERECORD>() where ent.OWNERCOMPANYID == entVacRd.ASSIGNEDOBJECTID && ent.ATTENDANCEDATE >= item.STARTDATE && ent.ATTENDANCEDATE <= item.ENDDATE select ent; int i = 0; foreach (var att in attdel) { i = i + dal.Delete(att); } //int i = dal.ExecuteNonQuery(sql); Tracer.Debug("新增假期设置删除设定日期整个公司考勤初始化记录,共删除:" + i.ToString() + " 条数据:转换出的sql" + sql); } catch (Exception ex) { Tracer.Debug("新增假期设置删除设定日期整个公司考勤初始化记录异常:" + ex.ToString()); } } //if (item.DAYTYPE == (Convert.ToInt32(Common.OutPlanDaysType.WorkDay) + 1).ToString()) //{ //如果设置的日期是当月,需要重新初始化公司考勤 if (item.STARTDATE.Value <= DateTime.Now.AddMonths(1).AddDays(-1)) { needInitCompanyAttanceDayAgain = true; strCurYearMonth = item.STARTDATE.Value.Year + "-" + item.STARTDATE.Value.Month; } if (item.ENDDATE.Value <= DateTime.Now.AddMonths(1).AddDays(-1)) { needInitCompanyAttanceDayAgain = true; strCurYearMonth = item.ENDDATE.Value.Year + "-" + item.ENDDATE.Value.Month; } //} #endregion } if (needInitCompanyAttanceDayAgain) { Tracer.Debug("====================================新增假期设置工作日开始初始化整个公司考勤记录"); using (AttendanceSolutionAsignBLL bllAttendanceSolutionAsign = new AttendanceSolutionAsignBLL()) { bllAttendanceSolutionAsign.AsignAttendanceSolutionByOrgID("1", entVacRd.ASSIGNEDOBJECTID, strCurYearMonth); } Tracer.Debug("====================================新增假期设置工作日初始化整个公司考勤记录完毕"); } } } catch (Exception ex) { Tracer.Debug(ex.ToString()); } }