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."));
}
}
