public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/plain"; context.Response.Charset = "utf-8"; HttpPostedFile file = context.Request.Files["Filedata"]; if (file != null) { string AreaId = context.Request.Form["AreaId"]; IList <StringObjectDictionary> datas = NPOIManager.GetSheetData(file.InputStream, 0, true); List <IBatisNetBatchStatement> statements = new List <IBatisNetBatchStatement>(); FamilyMember familyMember = new FamilyMember() { DataSource = GlobalManager.DIKey_00012_UploadFile, OperatedBy = NormalSession.UserId.ToGuid(), OperatedOn = DateTime.Now, AreaId = AreaId }; foreach (var data in datas) { familyMember.FamilyMemberId = Guid.NewGuid(); StringObjectDictionary sod = familyMember.ToStringObjectDictionary(false); IDictionary <string, object> dataItem = sod.MixInObject(data, false, e0571.web.core.Other.CaseSensitive.NORMAL); statements.Add(new IBatisNetBatchStatement { StatementName = familyMember.GetCreateMethodName(), ParameterObject = dataItem, Type = SqlExecuteType.INSERT }); } BuilderFactory.DefaultBulder().ExecuteNativeSqlNoneQuery(statements); //下面这句代码缺少的话,上传成功后上传队列的显示不会自动消失 context.Response.Write("1"); } else { context.Response.Write("0"); } }
public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/plain"; context.Response.Charset = "utf-8"; HttpPostedFile file = context.Request.Files["Filedata"]; if (file != null) { string AreaId = context.Request.Form["AreaId"]; string AreaId2 = context.Request.Form["AreaId2"]; string AreaId3 = context.Request.Form["AreaId3"]; string conectId = context.Request.Form["ConnectId"]; string iResidentStatus = context.Request.Form["ResidentStatus"]; int iStatus = int.Parse(context.Request.Form["Status"]); IList <StringObjectDictionary> datas = NPOIManager.GetSheetData(file.InputStream, 0, true); List <IBatisNetBatchStatement> statements = new List <IBatisNetBatchStatement>(); //数据转换 List <ExcelError> errors = new List <ExcelError>(); IList <StringObjectDictionary> basedatas = RetEffectiveDatas(datas, errors); XlsOfResidentBaseInfo residentBaseInfo = new XlsOfResidentBaseInfo() { Status = (byte)iStatus, DataSource = GlobalManager.DIKey_00012_UploadFile, OperatedBy = NormalSession.UserId.ToGuid(), OperatedOn = DateTime.Now, AreaId = AreaId }; foreach (var data in basedatas) { residentBaseInfo.ResidentId = Guid.NewGuid(); StringObjectDictionary sod = residentBaseInfo.ToStringObjectDictionary(false); IDictionary <string, object> dataItem = sod.MixInObject(data, false, e0571.web.core.Other.CaseSensitive.NORMAL); dataItem["AreaId2"] = AreaId2; dataItem["AreaId3"] = AreaId3; dataItem["ResidentStatus"] = iResidentStatus; statements.Add(new IBatisNetBatchStatement { StatementName = residentBaseInfo.GetCreateMethodName(), ParameterObject = dataItem, Type = SqlExecuteType.INSERT }); } //先清空临时表 BuilderFactory.DefaultBulder(conectId).ExecuteNativeSqlNoneQuery("Truncate table Tmp_XlsOfResidentBaseInfo"); //插入数据 BuilderFactory.DefaultBulder(conectId).ExecuteNativeSqlNoneQuery(statements); //下面这句代码缺少的话,上传成功后上传队列的显示不会自动消失 context.Response.Write(errors.ToJson()); } else { context.Response.Write("0"); } }
public IWorkbook GetDemandAggregationReportData(DemandAggregationSearchItems search, FileExtensions fileExtension) { var filters = new DemandAggregationSearchFilters { Employers = search.Employers, FinancialYear = search.FinancialYear, JobRoles = search.JobRoles, JobStates = search.JobStates, UserRole = search.UserRole }; var reportData = dashboard.GetDemandAggregationReportData(filters); return(ModifyWorkBook(NPOIManager.CreateExcelWorkBook(reportData, fileExtension, "Demand Aggregation Report"))); }
private void ExportAssessmentNotice(HttpContext context, dynamic param) { try { /***********************begin 复杂查询添加代码*********************/ StringObjectDictionary filters = new StringObjectDictionary(); List <string> whereClause = new List <string>(); if (param.ReportIds != null) { List <string> fuzzys = new List <string>(); foreach (var field in param.ReportIds) { fuzzys.Add(string.Format(" '{0}' ", field)); } if (fuzzys.Count > 0) { whereClause.Add("ReportId in(" + string.Join(" , ", fuzzys.ToArray()) + ") "); } if (whereClause.Count > 0) { filters.Add("WhereClause", string.Join(" AND ", whereClause.ToArray())); } } /**********************************************************/ /***********************begin 排序*************************/ /**********************************************************/ if (!string.IsNullOrEmpty((string)param.sort)) { filters.Add("OrderByClause", (string)param.sort + " " + (string)param.order ?? "ASC"); } /***********************end 排序***************************/ string strConnectId = (string)param.ConnectId; IList <EvaluatedReport> evaluatedReportList = BuilderFactory.DefaultBulder(strConnectId).List <EvaluatedReport>(filters); string xlsName = "告知书-" + DateTime.Now.ToString("yyyy年MM月dd日") + "-" + DateTime.Now.Millisecond.ToString() + ".xls"; NPOIManager.CreateEvaluateNoticeExcel <EvaluatedReport>(context, evaluatedReportList, xlsName, "养老服务告知书", strConnectId); } catch (Exception ex) { context.Response.Write("<script type='text/javascript'>parent.alertInfo('Excel导出失败!');</script>"); context.Response.End(); } }
public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/plain"; context.Response.Charset = "utf-8"; HttpPostedFile file = context.Request.Files["Filedata"]; if (file != null) { string parentId = context.Request.Form["AreaId"]; //Guid areaId = new Guid(areaIdStr); IList <StringObjectDictionary> datas = NPOIManager.GetSheetData(file.InputStream, 0, true); List <IBatisNetBatchStatement> statements = new List <IBatisNetBatchStatement>(); Area area = new Area() { ParentId = parentId, Status = 1, CheckInTime = DateTime.Now }; foreach (var data in datas) { StringObjectDictionary sod = area.ToStringObjectDictionary(false); IDictionary <string, object> dataItem = sod.MixInObject(data, false, e0571.web.core.Other.CaseSensitive.NORMAL); statements.Add(new IBatisNetBatchStatement { StatementName = area.GetCreateMethodName(), ParameterObject = dataItem, Type = SqlExecuteType.INSERT }); } BuilderFactory.DefaultBulder().ExecuteNativeSqlNoneQuery(statements); //下面这句代码缺少的话,上传成功后上传队列的显示不会自动消失 context.Response.Write("1"); } else { context.Response.Write("0"); } }
public IEnumerable <BulkUploadSummaryViewModel <BulkJobSeekerUploadSummaryViewModel> > RegisterJobSeekers(UserViewModel user, IList <IFormFile> files) { string roleName = user.RoleName; string mappingFilePath = Path.Combine(environment.WebRootPath, "DataMappings", "BulkJobSeekerMappings", "JobSeekerMapping.xml"); DataTable dt = XmlProcessor.XmlToTable(mappingFilePath); if (null != dt) { var summary = new List <BulkUploadSummaryViewModel <BulkJobSeekerUploadSummaryViewModel> >(); foreach (var file in files) { var fileSummary = new BulkUploadSummaryViewModel <BulkJobSeekerUploadSummaryViewModel> { FileName = file.FileName, Summary = new List <BulkJobSeekerUploadSummaryViewModel>() }; DataTable dTable = null; NPOIManager.ReadFile(file, dt, out dTable, true, roleName); if (null != dTable && dTable.Rows.Count > 1) { string[] additionalColumns = new string[] { "ProcessedBy", "ProcessedOn", "Status", "ErrorDetails" }; // If at the time of file read we pass true for first row as header then here also we need to pass the same. ExtendTable(additionalColumns, ref dTable, user.UserId, true); UserModel jsDetail = null; int rIndex = -1; foreach (DataRow row in dTable.Rows) { rIndex++; if (rIndex == 0) { fileSummary.Summary.Add(AddSummary(row)); continue; } try { row[additionalColumns[0]] = user.FullName; row[additionalColumns[1]] = DateTime.Now; ResolveJobSeekerDetail(out jsDetail, row, additionalColumns); foreach (DataColumn col in dTable.Columns) { // Check if any required field is empty switch (col.ColumnName) { case "CandidateId": case "FirstName": case "LastName": case "Email": if (string.IsNullOrEmpty(Convert.ToString(row[col.ColumnName]))) { row[additionalColumns[2]] = "Failed"; row[additionalColumns[3]] += $"<li>{dTable.Rows[0][col.ColumnName]} is mandatory</li>"; } break; } // Validate User Info switch (col.ColumnName) { // User already exist or not case "Email": if (authProcessor.CheckIfUserExists(Convert.ToString(row[col.ColumnName]))) { row[additionalColumns[2]] = "Failed"; row[additionalColumns[3]] += "<li>Candidate is already registered in Placement Portal</li>"; } else { jsDetail.IsActive = false; jsDetail.ActivationKey = RandomGenerator.GetRandom(20); jsDetail.Password = RandomGenerator.GetRandom(); } break; // Candidate Id already exist or not case "CandidateId": if (authProcessor.CheckCandidateIdExist(Convert.ToString(row[col.ColumnName]))) { row[additionalColumns[2]] = "Failed"; row[additionalColumns[3]] += "<li>Candidate Id already exist in our system.</li>"; } else { try { DataRow r = authProcessor.CandidateResult(Convert.ToString(row[col.ColumnName])); if (null != r) { jsDetail.GetType().GetProperty("BatchNumber").SetValue(jsDetail, r["BatchNumber"]); } } catch (DataNotFound ex) { row[additionalColumns[2]] = "Failed"; row[additionalColumns[3]] += $"<li>{ex.Message}</li>"; } } break; default: break; } } if ("Failed" != Convert.ToString(row[additionalColumns[2]])) { jsDetail.CreatedBy = Convert.ToInt32(user.UserId); jsDetail.RoleId = 2; int userId = authProcessor.RegisterUser(jsDetail); if (userId > 0) { try { jsDetail.UserId = userId; SendActivationMail(jsDetail); } catch (Exception ex) { row[additionalColumns[2]] = "Failed"; row[additionalColumns[3]] += "<li>Unable to send email verification link to user, Please contact your tech deck.</li>"; } row[additionalColumns[2]] = "Success"; } else { row[additionalColumns[2]] = "Failed"; row[additionalColumns[3]] += "<li>Unable to register this user, Please contact your tech deck.</li>"; } } } catch (Exception ex) { row[additionalColumns[2]] = "Failed"; row[additionalColumns[3]] += $"<li>{ex.Message}</li>"; } fileSummary.Summary.Add(AddSummary(row)); } } else { throw new DataNotFound( string.Format("{0}", "Data not found in file to import.")); } summary.Add(fileSummary); } return(summary); } else { throw new XmlFileMapperException( string.Format("{0}", "JobSeekerMapping.xml file is not in proper format to convert in data table.")); } }
public bool UploadFile(UserViewModel user, List <IFormFile> files) { string roleName = user.RoleName; //var status = true; string mappingFilePath = Path.Combine(environment.WebRootPath, "DataMappings", "PlacedCandidate", "PlacedCandidateMapping.xml"); DataTable dt = XmlProcessor.XmlToTable(mappingFilePath); if (null != dt) { foreach (var file in files) { DataTable dTable = null; NPOIManager.ReadFile(file, dt, out dTable, true, roleName); if (null != dTable && dTable.Rows.Count > 1) { //string[] additionalColumns = new string[] { "CreatedDate", "CreatedBy" }; //ExtendTable(additionalColumns, ref dTable, user.UserId, true); //PlacedCandidateModel placeduser = null; int header = -1; foreach (DataRow row in dTable.Rows) { header++; if (header == 0) { continue; } PlacedCandidateModel placeduser = new PlacedCandidateModel() { AverageofNoOfMonthsofPlacement = row["AverageofNoOfMonthsofPlacement"] as string ?? "", AvgofNoofdaysbetweennDOCDOP = row["AvgofNoofdaysbetweennDOCDOP"] as string ?? "", CandidateEmail = row["CandidateEmail"] as string ?? "", CandidateID = row["CandidateID"] as string ?? "", CandidateName = row["CandidateName"] as string ?? "", Castecategory = row["Castecategory"] as string ?? "", CertificateDate = row["CertificateDate"] as string ?? "", Certified = row["Certified"] as string ?? "", CountofPartnerID = row["CountofPartnerID"] as string ?? "", CountofSCTrainingCentreID = row["CountofSCTrainingCentreID"] as string ?? "", EducationAttained = row["EducationAttained"] as string ?? "", EmployerspocEmail = row["EmployerspocEmail"] as string ?? "", EmployerspocMobile = row["EmployerspocMobile"] as string ?? "", EmployerSpocName = row["EmployerSpocName"] as string ?? "", EmployerType = row["EmployerType"] as string ?? "", FirstEmploymentCreatedDate = row["FirstEmploymentCreatedDate"] as string ?? "", FromDate = row["FromDate"] as string ?? "", FYWise = row["FYWise"] as string ?? "", Gender = row["Gender"] as string ?? "", Jobrole = row["Jobrole"] as string ?? "", OrganisationDistrict = row["OrganisationDistrict"] as string ?? "", OrganisationState = row["OrganisationState"] as string ?? "", OrganizationAddress = row["OrganizationAddress"] as string ?? "", OrganizationName = row["OrganizationName"] as string ?? "", PartnerName = row["PartnerName"] as string ?? "", PartnerSPOCEmail = row["PartnerSPOCEmail"] as string ?? "", PartnerSPOCMobile = row["PartnerSPOCMobile"] as string ?? "", PartnerSPOCName = row["PartnerSPOCName"] as string ?? "", SectorName = row["SectorName"] as string ?? "", SelfEmployedDistrict = row["SelfEmployedDistrict"] as string ?? "", SelfEmployedState = row["SelfEmployedState"] as string ?? "", SumofCandidateContactNo = row["AverageofNoOfMonthsofPlacement"] as string ?? "", SumofSalleryPerMonth = row["SumofSalleryPerMonth"] as string ?? "", SumofTCSPOCMobile = row["SumofTCSPOCMobile"] as string ?? "", TCDistrict = row["TCDistrict"] as string ?? "", TCSPOCEmail = row["TCSPOCEmail"] as string ?? "", TCSPOCName = row["TCSPOCName"] as string ?? "", TCState = row["TCState"] as string ?? "", ToDate = row["ToDate"] as string ?? "", TrainingCentreName = row["TrainingCentreName"] as string ?? "", TrainingType = row["TrainingType"] as string ?? "", }; try { _placedCandidateRepository.UploadFileData(placeduser, user.UserId); } catch (Exception ex) { continue; } } } } } return(true); }
public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/plain"; context.Response.Charset = "utf-8"; HttpPostedFile file = context.Request.Files["Filedata"]; if (file != null) { string AreaId = context.Request.Form["AreaId"]; string retFailInfo = ""; int relationOfMan = 99999; int count = 0; IList <StringObjectDictionary> datas = NPOIManager.GetSheetData(file.InputStream, 0, true); List <IBatisNetBatchStatement> statements = new List <IBatisNetBatchStatement>(); List <IBatisNetBatchStatement> oldmanconfig_statements = new List <IBatisNetBatchStatement>(); List <IBatisNetBatchStatement> familymember_statements = new List <IBatisNetBatchStatement>(); List <IBatisNetBatchStatement> oldmanfamily_statements = new List <IBatisNetBatchStatement>(); //获取图片 //NPOI.HSSF.UserModel.HSSFWorkbook hssfworkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(file.InputStream); //NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0); //Dictionary<string, Byte[]> picDataDictionary = getSheetPictrues03((NPOI.HSSF.UserModel.HSSFSheet)sheet); //获取所有老人基本信息 IList <OldManBaseInfo> oldManBaseInfoList = RetExistsIdNo(AreaId); //获取所有老人配置终端信息 IList <OldManConfigInfo> oldManConfigInfoList = RetAllConfigCallNo(); //获取所有老人的亲属资料 IList <StringObjectDictionary> familyMemberList = RetAllFamilyMember(); //获取所有街道社区信息 IList <Area> AreaList = RetAllStreetCommunity(); //保存图片地址 //基本信息 IList <StringObjectDictionary> basedatas = RetEffectiveDatas(datas, ref retFailInfo); OldManBaseInfo baseInfo = new OldManBaseInfo() { DataSource = GlobalManager.DIKey_00012_UploadFile, OperatedBy = NormalSession.UserId.ToGuid(), OperatedOn = DateTime.Now, AreaId = AreaId }; OldManConfigInfo oldmanconfig = new OldManConfigInfo(); FamilyMember familymember = new FamilyMember() { Gender = "M", IDNo = "", Status = 1 }; OldManFamilyInfo oldmanfamily = new OldManFamilyInfo(); foreach (var data in basedatas) { //老人基本资料 baseInfo.OldManId = Guid.NewGuid(); count = oldManBaseInfoList.Count(s => s.IDNo == data["IDNo"].ToString()); if (count > 0) { baseInfo.OldManId = oldManBaseInfoList.First(s => s.IDNo == data["IDNo"].ToString()).OldManId; } baseInfo.Status = 1; baseInfo.HealthInsuranceFlag = Convert.ToByte(data["HealthInsuranceNumber"] != null); baseInfo.SocialInsuranceFlag = Convert.ToByte(data["SocialInsuranceNumber"] != null); StringObjectDictionary sod = baseInfo.ToStringObjectDictionary(false); IDictionary <string, object> dataItem = sod.MixInObject(data, false, e0571.web.core.Other.CaseSensitive.NORMAL); var tmpArea = AreaList.FirstOrDefault(s => s.AreaName.IndexOf(dataItem["AreaId2"].ToString()) > -1); if (dataItem.ContainsKey("AreaId2") && dataItem["AreaId2"] != null && tmpArea != null) { dataItem["AreaId2"] = tmpArea.AreaId != null?tmpArea.AreaId.ToString().ToUpper() : ""; } else { dataItem.Remove("AreaId2"); } tmpArea = AreaList.FirstOrDefault(s => s.AreaName.IndexOf(dataItem["AreaId3"].ToString()) > -1); if (dataItem.ContainsKey("AreaId3") && dataItem["AreaId3"] != null && tmpArea != null) { dataItem["AreaId3"] = tmpArea.AreaId != null?tmpArea.AreaId.ToString().ToUpper() : ""; } else { dataItem.Remove("AreaId3"); } if (count > 0) { statements.Add(new IBatisNetBatchStatement { StatementName = baseInfo.GetUpdateMethodName(), ParameterObject = dataItem, Type = SqlExecuteType.UPDATE }); } else { statements.Add(new IBatisNetBatchStatement { StatementName = baseInfo.GetCreateMethodName(), ParameterObject = dataItem, Type = SqlExecuteType.INSERT }); } //老人呼叫设备资料,存在则跳过 if (dataItem.ContainsKey("CallNo") && dataItem["CallNo"] != null) { if (oldManConfigInfoList.Count(s => s.CallNo == dataItem["CallNo"].ToString()) == 0) { var configinfoitem = oldManConfigInfoList.FirstOrDefault(s => s.OldManId.ToString() == dataItem["OldManId"].ToString()); if (configinfoitem != null && configinfoitem.OldManId != null) { oldmanconfig_statements.Add(new IBatisNetBatchStatement { StatementName = oldmanconfig.GetUpdateMethodName(), ParameterObject = dataItem, Type = SqlExecuteType.UPDATE }); } else { oldmanconfig_statements.Add(new IBatisNetBatchStatement { StatementName = oldmanconfig.GetCreateMethodName(), ParameterObject = dataItem, Type = SqlExecuteType.INSERT }); } } } //获取亲人信息 StringObjectDictionary sod2 = new StringObjectDictionary(); for (int i = 0; i < 3; i++) { string familyName = "FamilyName" + (i + 1).ToString(); string callNo = "CallNo" + (i + 1).ToString(); if (!data.ContainsKey(familyName) || data[familyName] == null) { continue; } if (!data.ContainsKey(callNo) || data[callNo] == null) { continue; } oldmanfamily.RelationIdOfFamily = (relationOfMan - i).ToString(); oldmanfamily.RelationIdOfOldMan = (relationOfMan - i).ToString(); string callNoData = data[callNo].ToString(); familymember.FamilyMemberId = Guid.NewGuid(); familymember.FamilyMemberName = data[familyName].ToString(); //匹配手机号 familymember.Mobile = System.Text.RegularExpressions.Regex.Match(callNoData, @"0*[1][358]\d{9}").Value; if (familymember.Mobile.Length > 0) { callNoData = callNoData.Replace(familymember.Mobile, ""); } //匹配座机 familymember.Tel = System.Text.RegularExpressions.Regex.Match(callNoData, @"((\d{7,8})|(\d{4}|\d{3})-(\d{7,8})|(\d{4}|\d{3})-(\d{7,8})-(\d{4}|\d{3}|\d{2}|\d{1})|(\d{7,8})-(\d{4}|\d{3}|\d{2}|\d{1}))").Value; if (count > 0) { sod2 = familyMemberList.FirstOrDefault(s => s["OldManId"].ToString() == baseInfo.OldManId.ToString() && s["RelationIdOfFamily"].ToString() == oldmanfamily.RelationIdOfFamily); if (sod2 != null && sod2.Count > 0) { if (familyMemberList.Count(s => s["FamilyMemberName"].ToString() == familymember.FamilyMemberName && s["Mobile"].ToString() == familymember.Mobile && s["Tel"].ToString() == familymember.Tel) > 0) { continue; } familymember.FamilyMemberId = sod2["FamilyMemberId"].ToString().ToGuid(); } } sod = baseInfo.ToStringObjectDictionary(false); dataItem = sod.MixInObject(familymember, false, e0571.web.core.Other.CaseSensitive.NORMAL); if (sod2 != null && sod2.Count > 0) { familymember_statements.Add(new IBatisNetBatchStatement { StatementName = familymember.GetUpdateMethodName(), ParameterObject = dataItem, Type = SqlExecuteType.UPDATE }); } else { familymember_statements.Add(new IBatisNetBatchStatement { StatementName = familymember.GetCreateMethodName(), ParameterObject = dataItem, Type = SqlExecuteType.INSERT }); dataItem = sod.MixInObject(oldmanfamily, false, e0571.web.core.Other.CaseSensitive.NORMAL); oldmanfamily_statements.Add(new IBatisNetBatchStatement { StatementName = oldmanfamily.GetCreateMethodName(), ParameterObject = dataItem, Type = SqlExecuteType.INSERT }); } } } BuilderFactory.DefaultBulder().ExecuteNativeSqlNoneQuery(statements); BuilderFactory.DefaultBulder().ExecuteNativeSqlNoneQuery(oldmanconfig_statements); BuilderFactory.DefaultBulder().ExecuteNativeSqlNoneQuery(familymember_statements); BuilderFactory.DefaultBulder().ExecuteNativeSqlNoneQuery(oldmanfamily_statements); /*string saveName = @context.Request["saveName"]; * string fileType = file.FileName.Substring(file.FileName.LastIndexOf(".")); * string uploadPath = HttpContext.Current.Server.MapPath(@context.Request["folder"]); * FileAdapter.EnsurePath(uploadPath); * if (string.IsNullOrEmpty(saveName)) * { * file.SaveAs(uploadPath + file.FileName); * } * else * { * file.SaveAs(uploadPath + saveName + fileType); * } */ //下面这句代码缺少的话,上传成功后上传队列的显示不会自动消失 context.Response.Write("1"); } else { context.Response.Write("0"); } }
private void ExportFamilyMember(HttpContext context, dynamic param) { try { /***********************begin 复杂查询添加代码*********************/ StringObjectDictionary filters = new StringObjectDictionary(); if (param.instance != null) { foreach (var field in param.instance) { filters.Add(field.Name, (string)field.Value); } } List <string> whereClause = new List <string>(); /**********************************************************/ if (param.filterFields != null) { foreach (var field in param.filterFields) { DateTime parseTime = new DateTime(); if (field.key.Equals("OperatedOn_Start") && DateTime.TryParse(field.value, out parseTime)) { whereClause.Add(string.Format("OperatedOn >= '{0}' ", field.value)); } else if (field.key.Equals("OperatedOn_End") && DateTime.TryParse(field.value, out parseTime)) { whereClause.Add(string.Format("OperatedOn <= '{0}' ", field.value)); } else { filters[field.key] = field.value; } } } /***********************begin 模糊查询*********************/ if (param.fuzzyFields != null) { List <string> fuzzys = new List <string>(); foreach (var field in param.fuzzyFields) { fuzzys.Add(string.Format("{0} like '%{1}%'", field.key, field.value)); } if (fuzzys.Count > 0) { whereClause.Add("(" + string.Join((string.IsNullOrEmpty(param.fuzzyFieldOP) ? " AND " : " " + param.fuzzyFieldOP + " "), fuzzys.ToArray()) + ")"); } } /***********************end 模糊查询***********************/ /***********************begin 自定义代码*******************/ /***********************此处添加自定义查询代码*************/ /***********************end 自定义代码*********************/ if (whereClause.Count > 0) { filters.Add("WhereClause", string.Join(" AND ", whereClause.ToArray())); } /**********************************************************/ /***********************begin 排序*************************/ /**********************************************************/ if (!string.IsNullOrEmpty((string)param.sort)) { filters.Add("OrderByClause", (string)param.sort + " " + (string)param.order ?? "ASC"); } /***********************end 排序***************************/ IList <FamilyMember> familyMemberList = BuilderFactory.DefaultBulder().List <FamilyMember>(filters); //System.Data.DataTable dt = BuilderFactory.DefaultBulder().QueryForDataTable("FamilyMember_List", filters); IList <string> colNames = new List <string>(); colNames.Add("姓名"); colNames.Add("身份证"); colNames.Add("性别"); colNames.Add("操作时间"); colNames.Add("座机"); colNames.Add("手机"); colNames.Add("地址"); IList <string> colKeys = new List <string>(); colKeys.Add("FamilyMemberName"); colKeys.Add("IDNo"); colKeys.Add("Gender"); colKeys.Add("OperatedOn"); colKeys.Add("Tel"); colKeys.Add("Mobile"); colKeys.Add("Address"); //NPOIManager.RenderToBrowserByDataSet(context, dt.DataSet, 0, "ddss.xls", "你知不知道", colNames, colKeys, false); NPOIManager.RenderToBrowserByList <FamilyMember>(context, familyMemberList, 0, "ddss.xls", "", colNames, colKeys, false); } catch (Exception ex) { context.Response.Write("<script type='text/javascript'>parent.alertInfo('Excel导出失败!');</script>"); context.Response.End(); } }
private void ExportOldManBaseInfo(HttpContext context, dynamic param) { try { /***********************begin 复杂查询添加代码*********************/ StringObjectDictionary filters = new StringObjectDictionary(); if (param.instance != null) { foreach (var field in param.instance) { filters.Add(field.Name, (string)field.Value); } } List <string> whereClause = new List <string>(); /**********************************************************/ if (param.filterFields != null) { foreach (var field in param.filterFields) { if (field.key.Value.Contains("AreaId2_Start") && field.value.Value != "") { filters["AreaId2"] = field.value.Value; } else if (field.key.Value.Contains("AreaId3_Start") && field.value.Value != "") { filters["AreaId3"] = field.value.Value; } else { filters[field.key.Value] = field.value.Value; } } } /***********************begin 模糊查询*********************/ if (param.fuzzyFields != null) { List <string> fuzzys = new List <string>(); foreach (var field in param.fuzzyFields) { if (field.key.Value.Equals("AreaId") && field.value.Value != "") { whereClause.Add(string.Format("(AreaId3 in (select cast(AreaId as varchar (40)) from Pub_Area where {0} like '{1}%') or AreaId2 in (select cast(AreaId as varchar (40)) from Pub_Area where {0} like '{1}%'))", field.key.Value, field.value.Value)); } else if (field.key.Value.Equals("AreaId") && field.value.Value == "") { } else { fuzzys.Add(string.Format("{0} like '%{1}%'", field.key.Value, field.value.Value)); } } if (fuzzys.Count > 0) { whereClause.Add("(" + string.Join((string.IsNullOrEmpty(param.fuzzyFieldOP.Value) ? " AND " : " " + param.fuzzyFieldOP.Value + " "), fuzzys.ToArray()) + ")"); } } /***********************end 模糊查询***********************/ /***********************begin 自定义代码*******************/ if (filters["isSuperAdmin"].ToString() == "False") { string sql = PermissionsCategoryView(filters["UserId"].ToString()); whereClause.Add(sql); } /***********************end 自定义代码*********************/ whereClause.Add(" a.IDNo not like '%*%' "); if (whereClause.Count > 0) { filters.Add("WhereClause", string.Join(" AND ", whereClause.ToArray())); } if (!string.IsNullOrEmpty((string)param.sort.Value)) { filters.Add("OrderByClause", (string)param.sort.Value + " " + (string)param.order.Value ?? "ASC"); } /***********************end 排序***************************/ IList <StringObjectDictionary> oldManBaseInfoList = BuilderFactory.DefaultBulder().ListStringObjectDictionary("OldManBaseInfoForName", filters); //System.Data.DataTable dt = BuilderFactory.DefaultBulder().QueryForDataTable("FamilyMember_List", filters); IList <string> colNames = new List <string>(); colNames.Add("姓名"); colNames.Add("性别"); colNames.Add("年龄"); colNames.Add("身份证号码"); colNames.Add("医保标志"); colNames.Add("医保号码"); colNames.Add("社保标志"); colNames.Add("社保号码"); colNames.Add("居住情况"); colNames.Add("身份情况"); colNames.Add("所属辖区"); colNames.Add("所属街道"); colNames.Add("所属社区"); colNames.Add("地址"); colNames.Add("经度"); colNames.Add("纬度"); colNames.Add("邮编"); colNames.Add("电话"); colNames.Add("手机"); colNames.Add("备注"); colNames.Add("呼叫号码1"); colNames.Add("呼叫号码2"); colNames.Add("呼叫号码3"); colNames.Add("政府购买标志"); IList <string> colKeys = new List <string>(); colKeys.Add("OldManName"); colKeys.Add("GenderName"); colKeys.Add("Age"); colKeys.Add("IDNo"); colKeys.Add("HealthInsuranceFlagName"); colKeys.Add("HealthInsuranceNumber"); colKeys.Add("SocialInsuranceFlagName"); colKeys.Add("SocialInsuranceNumber"); colKeys.Add("LivingStatusName"); colKeys.Add("OldManIdentityName"); colKeys.Add("AreaIdName"); colKeys.Add("AreaId2Name"); colKeys.Add("AreaId3Name"); colKeys.Add("Address"); colKeys.Add("LongitudeS"); colKeys.Add("LatitudeS"); colKeys.Add("PostCode"); colKeys.Add("Tel"); colKeys.Add("Mobile"); colKeys.Add("Remark"); colKeys.Add("CallNo"); colKeys.Add("CallNo2"); colKeys.Add("CallNo3"); colKeys.Add("GovTurnkeyFlagName"); //NPOIManager.RenderToBrowserByDataSet(context, dt.DataSet, 0, "ddss.xls", "你知不知道", colNames, colKeys, false); NPOIManager.RenderToBrowserByListStringObjectDictionary(context, oldManBaseInfoList, 0, "老人基本信息" + DateTime.Now.ToString("yyyyMMddhh:mm:ss") + ".xls", "", colNames, colKeys, false); //NPOIManager.RenderToBrowserByList<StringObjectDictionary>(context, oldManBaseInfoList, 0, "ddss.xls", "", colNames, colKeys, false); } catch (Exception ex) { context.Response.Write("<script type='text/javascript'>parent.alertInfo('Excel导出失败!');</script>"); context.Response.End(); } }
public IEnumerable <BulkUploadSummaryViewModel <BulkJobPostSummaryDetailViewModel> > UploadJobs(UserViewModel user, IList <IFormFile> files) { string roleName = user.RoleName; string mappingFilePath = Path.Combine(environment.WebRootPath, "DataMappings", "BulkJobPostMappings", "JobPostMapping.xml"); DataTable dt = XmlProcessor.XmlToTable(mappingFilePath); if (null != dt) { var summary = new List <BulkUploadSummaryViewModel <BulkJobPostSummaryDetailViewModel> >(); foreach (var file in files) { var fileSummary = new BulkUploadSummaryViewModel <BulkJobPostSummaryDetailViewModel> { FileName = file.FileName, Summary = new List <BulkJobPostSummaryDetailViewModel>() }; DataTable dTable = null; NPOIManager.ReadFile(file, dt, out dTable, true, roleName); if (null != dTable && dTable.Rows.Count > 1) { string[] additionalColumns = new string[] { "ProcessedBy", "ProcessedOn", "Status", "ErrorDetails" }; // If at the time of file read we pass true for first row as header then here also we need to pass the same. ExtendTable(additionalColumns, ref dTable, user.UserId, true); // In Case of multiple cities in Job Location Column we need to divide them in multiple rows as below. SimpliFyTableDataForMultipleCitiesForSingleJob(dTable); JobPostModel jDetail = null; int rIndex = -1; foreach (DataRow row in dTable.Rows) { rIndex++; if (rIndex == 0) { fileSummary.Summary.Add(AddSummary(row)); continue; } BulkJobPostSummaryDetailViewModel sData = null; row[additionalColumns[0]] = user.FullName; row[additionalColumns[1]] = DateTime.Now; // Checking if user authorized to post jobs. if (!IsAllowToPostJob(user, row)) { row[additionalColumns[2]] = "Failed"; row[additionalColumns[3]] += $"<li>You are not allowed to post this job - Enter company name used while registration.</li>"; sData = AddSummary(row); fileSummary.Summary.Add(sData); SaveDetailToAudit(sData, fileSummary.FileName, user.UserId); continue; } try { //Saving JobTitleByEmployer as JobTitle GetJobTitle(row, additionalColumns); ManageJobType(row, additionalColumns); // Checking if at least one quarter must have a value //ValidateQuarters(row, additionalColumns); //Validate SPOC Contact ValidateSpocContact(row, additionalColumns); // Validate Job Expiry Dates ValidateJobExpiryDates(row, additionalColumns); // All external validations should be done before this method. ResolveJobData(out jDetail, row, additionalColumns); // Validate model if some thing needs for default value. ValidateJobModel(ref jDetail); foreach (DataColumn col in dTable.Columns) { // Check if any required field is empty switch (col.ColumnName) { case "CompanyName": case "StateCode": case "CityCode": case "JobTitleByEmployer": case "JobTitleId": case "SPOC": case "SPOCEmail": case "SPOCContact": //case "CTC": case "HiringCriteria": //case "Quarter1": //case "Quarter2": //case "Quarter3": //case "Quarter4": case "JobType": case "PositionStartDate": case "PositionEndDate": if (string.IsNullOrEmpty(Convert.ToString(row[col.ColumnName]))) { switch (col.ColumnName) { case "JobTitleId": row[additionalColumns[2]] = "Failed"; row[additionalColumns[3]] += $"<li>At least one job Title is mandatory</li>"; break; default: row[additionalColumns[2]] = "Failed"; row[additionalColumns[3]] += $"<li>{dTable.Rows[0][col.ColumnName]} is mandatory</li>"; break; } } break; default: break; } // Check if master information available in our system or not. DataTable t = null; switch (col.ColumnName) { //case "FinancialYear": // int year = Convert.ToInt32(row[col.ColumnName]); // if (year <= 0) // { // jDetail.GetType().GetProperty(col.ColumnName).SetValue(jDetail, DateTime.Now.Year); // } // break; case "StateCode": string state = Convert.ToString(row[col.ColumnName]); t = bjpProcessor.GetIdFromValue(state, col.ColumnName); if (null != t && t.Rows.Count > 0) { jDetail.GetType().GetProperty(col.ColumnName).SetValue(jDetail, t.Rows[0]["Id"]); } else { //var sModel = new StateModel //{ // CountryCode = jDetail.CountryCode, // State = state, // StateCode = string.Empty //}; //bjpProcessor.InsertState(ref sModel); //if (!string.IsNullOrWhiteSpace(sModel.StateCode)) //{ // jDetail.GetType().GetProperty(col.ColumnName).SetValue(jDetail, sModel.StateCode); //} //else //{ row[additionalColumns[2]] = "Failed"; row[additionalColumns[3]] += "<li>State Not Found In Our Record</li>"; //} InformToAdmin(row[col.ColumnName], col.ColumnName, user.Email, user.FullName); } break; case "CityCode": string city = Convert.ToString(row[col.ColumnName]); t = bjpProcessor.GetIdFromValue(city, col.ColumnName); if (null != t && t.Rows.Count > 0) { jDetail.GetType().GetProperty(col.ColumnName).SetValue(jDetail, t.Rows[0]["Id"]); } else { //var cModel = new CityModel //{ // City = city, // CityCode = string.Empty, // StateCode = jDetail.StateCode //}; //bjpProcessor.InsertCity(ref cModel); //if (!string.IsNullOrWhiteSpace(cModel.CityCode)) //{ // jDetail.GetType().GetProperty(col.ColumnName).SetValue(jDetail, cModel.CityCode); //} //else //{ row[additionalColumns[2]] = "Failed"; row[additionalColumns[3]] += "<li>City Not Found In Our Record</li>"; //} InformToAdmin(row[col.ColumnName], col.ColumnName, user.Email, user.FullName); } break; case "SPOCEmail": string email = Convert.ToString(row[col.ColumnName]); if (!string.IsNullOrWhiteSpace(email) && email != Constants.NotAvailalbe) { if (!emailHandler.IsValidEmail(email)) { row[additionalColumns[2]] = "Failed"; row[additionalColumns[3]] += $"<li>SPOC email is not in valid format, please use {Constants.NotAvailalbe} if SPOC email is not available.</li>"; } } break; case "CompanyName": string companyName = Convert.ToString(row[col.ColumnName]); if (!authProcessor.CheckIfEmployerExists(companyName, true)) { string mailId = companyName.ToLower().Replace(" ", "_"); var u = new UserModel { CompanyName = companyName, Email = mailId, Password = RandomGenerator.GetRandom(), RoleId = 3, ProfilePic = string.Empty }; bool isRegister = authProcessor.RegisterEmployer(u, true); } t = bjpProcessor.GetIdFromValue(companyName, col.ColumnName); if (null != t && t.Rows.Count > 0) { jDetail.Userid = Convert.ToInt32(t.Rows[0]["Id"]); } else { row[additionalColumns[2]] = "Failed"; row[additionalColumns[3]] += "<li>User Not Found In Our Record</li>"; } break; default: break; } } if ("Failed" != Convert.ToString(row[additionalColumns[2]])) { jDetail.IsFromBulkUpload = true; jDetail.CreatedBy = Convert.ToString(user.UserId); var date = DateTime.Now; jDetail.FinancialYear = date.Year; //ValidateJobModel(ref jDetail); if (jobPostProcessor.AddJobPostData(jDetail)) { row[additionalColumns[2]] = "Success"; } else { row[additionalColumns[2]] = "Failed"; } } } catch (Exception ex) { row[additionalColumns[2]] = "Failed"; row[additionalColumns[3]] += $"<li>{ex.Message}</li>"; } sData = AddSummary(row); fileSummary.Summary.Add(sData); SaveDetailToAudit(sData, fileSummary.FileName, user.UserId); } } else { throw new DataNotFound( string.Format("{0}", "Data not found in file to import.")); } summary.Add(fileSummary); } return(summary); } else { throw new XmlFileMapperException( string.Format("{0}", "JobPostMapping.xml file is not in proper format to convert in data table.")); } }