public DataTable GetExportJobs(ExportJobsSearchFilter searchFilter)
{
_reportDataAccess = new ReportDataAccess(_context);
IList <ExportJobsEntity> exportJobs = _reportDataAccess.GetExportJobs(searchFilter);
DataTable dt = DataTableHelpers.ConvertTo(exportJobs);
return(dt);
}
public IList <ExportJobsEntity> GetExportJobs(ExportJobsSearchFilter searchFilter)
{
int?attachFile = searchFilter.AttachFile.ToNullable <int>();
int?OwnerBranchId = searchFilter.OwnerBranch.ToNullable <int>();
int?CreatorBranchId = searchFilter.CreatorBranch.ToNullable <int>();
int?OwnerId = searchFilter.OwnerSR.ToNullable <int>();
int?CreatorId = searchFilter.CreatorSR.ToNullable <int>();
int?ActionId = searchFilter.ActionBy.ToNullable <int>();
int?ActionBranchId = searchFilter.ActionBranch.ToNullable <int>();
int?BranchId = searchFilter.LoginUser.BranchId;
DateTime?jobMinDate = null;
DateTime?jobMaxDate = null;
if (searchFilter.JobDateTimeFromValue.HasValue && searchFilter.JobDateTimeToValue.HasValue)
{
jobMinDate = searchFilter.JobDateTimeFromValue.Value;
jobMaxDate = searchFilter.JobDateTimeToValue.Value;
}
else
{
int?monthOfReportExport =
_context.TB_C_PARAMETER.Where(x => x.PARAMETER_NAME == Constants.ParameterName.ReportExportDate)
.FirstOrDefault().PARAMETER_VALUE.ToNullable <int>();
jobMinDate = DateTime.Now.Date.AddMonths(monthOfReportExport.Value * -1);
jobMaxDate = DateTime.Now.Date.AddDays(1);
}
var query = (from jb in _context.TB_T_JOB.AsNoTracking()
from u2 in _context.TB_R_USER.Where(u => u.USER_ID == jb.UPDATE_USER).DefaultIfEmpty()
from ab in _context.TB_R_BRANCH.Where(u => u.BRANCH_ID == u2.BRANCH_ID).DefaultIfEmpty()
from pb in _context.TB_M_POOL_BRANCH.Where(u => u.POOL_ID == jb.POOL_ID).DefaultIfEmpty()
from pl in _context.TB_M_POOL.Where(u => u.POOL_ID == jb.POOL_ID).DefaultIfEmpty()
from sr in _context.TB_T_SR.Where(s => s.SR_ID == jb.SR_ID).DefaultIfEmpty()
from cs in _context.TB_M_CUSTOMER.Where(c => c.CUSTOMER_ID == sr.CUSTOMER_ID).DefaultIfEmpty()
from u1 in _context.TB_R_USER.Where(u => u.USER_ID == sr.CREATE_USER).DefaultIfEmpty()
from u3 in _context.TB_R_USER.Where(u => u.USER_ID == sr.OWNER_USER_ID).DefaultIfEmpty()
from js in _context.TB_C_JOB_STATUS.Where(j => j.STATUS_VALUE == jb.JOB_STATUS).DefaultIfEmpty()
from st in _context.TB_C_SR_STATUS.Where(u => u.SR_STATUS_ID == sr.SR_STATUS_ID).DefaultIfEmpty()
join ch in _context.TB_R_CHANNEL on jb.CHANNEL_ID equals ch.CHANNEL_ID
where
(string.IsNullOrEmpty(searchFilter.FirstName) || (cs.FIRST_NAME_TH.Contains(searchFilter.FirstName) || cs.FIRST_NAME_EN.ToUpper().Contains(searchFilter.FirstName.ToUpper()))) &&
(string.IsNullOrEmpty(searchFilter.LastName) || (cs.LAST_NAME_TH.Contains(searchFilter.LastName) || cs.LAST_NAME_TH.ToUpper().Contains(searchFilter.LastName.ToUpper()))) &&
(searchFilter.JobStatus == null || searchFilter.JobStatus == Constants.ApplicationStatus.All || jb.JOB_STATUS == searchFilter.JobStatus) &&
(string.IsNullOrEmpty(searchFilter.FromValue) || jb.FROM.ToUpper().Contains(searchFilter.FromValue.ToUpper())) &&
(string.IsNullOrEmpty(searchFilter.Subject) || jb.SUBJECT.ToUpper().Contains(searchFilter.Subject.ToUpper())) &&
(jb.JOB_DATE >= jobMinDate) &&
(jb.JOB_DATE <= jobMaxDate) &&
(!searchFilter.ActionDateTimeFromValue.HasValue || jb.UPDATE_DATE >= searchFilter.ActionDateTimeFromValue.Value) &&
(!searchFilter.ActionDateTimeToValue.HasValue || jb.UPDATE_DATE <= searchFilter.ActionDateTimeToValue.Value) &&
(attachFile == null || attachFile == Constants.ApplicationStatus.All ||
(Constants.AttachFile.Yes == attachFile ? jb.TB_T_JOB_ATTACHMENT.Any() == true : jb.TB_T_JOB_ATTACHMENT.Any() == false))
&& (string.IsNullOrEmpty(searchFilter.SRId) || sr.SR_NO == searchFilter.SRId) &&
(string.IsNullOrEmpty(searchFilter.OwnerBranch) || sr.OWNER_BRANCH_ID == OwnerBranchId) &&
(string.IsNullOrEmpty(searchFilter.CreatorBranch) || sr.CREATE_BRANCH_ID == CreatorBranchId) &&
(string.IsNullOrEmpty(searchFilter.OwnerSR) || sr.OWNER_USER_ID == OwnerId) &&
(string.IsNullOrEmpty(searchFilter.CreatorSR) || sr.CREATE_USER == CreatorId) &&
(string.IsNullOrEmpty(searchFilter.ActionBy) || jb.UPDATE_USER == ActionId) &&
(string.IsNullOrEmpty(searchFilter.ActionBranch) || ab.BRANCH_ID == ActionBranchId) &&
pb.BRANCH_ID == BranchId &&
jb.CHANNEL_ID != null
select new ExportJobsEntity
{
FirstName = !string.IsNullOrEmpty(cs.FIRST_NAME_TH) ? cs.FIRST_NAME_TH : cs.FIRST_NAME_EN,
LastName = !string.IsNullOrEmpty(cs.FIRST_NAME_TH) ? cs.LAST_NAME_TH : cs.LAST_NAME_EN,
JobType = ch.CHANNEL_NAME,
JobStatus = js.STATUS_NAME,
JobDateTime = jb.JOB_DATE,
From = jb.FROM,
Subject = jb.SUBJECT,
SRID = sr != null? sr.SR_NO : string.Empty,
SRCreator = u1 != null ? u1.POSITION_CODE + " - " + u1.FIRST_NAME + " " + u1.LAST_NAME : string.Empty,
SROwner = u3 != null ? u3.POSITION_CODE + " - " + u3.FIRST_NAME + " " + u3.LAST_NAME : string.Empty,
SRStatus = st.SR_STATUS_NAME,
AttachFile = jb.TB_T_JOB_ATTACHMENT.Any() ? Resource.Ddl_AttachFile_Yes : Resource.Ddl_AttachFile_No,
Remark = jb.REMARK,
PoolName = pl.POOL_NAME
});
return(query.ToList());
}
//public DataTable GetExportJobs(ExportJobsSearchFilter searchFilter)
//{
// _reportDataAccess = new ReportDataAccess(_context);
// IList<ExportJobsEntity> exportJobs = _reportDataAccess.GetExportJobs(searchFilter);
// DataTable dt = DataTableHelpers.ConvertTo(exportJobs);
// return dt;
//}
public IList <ExportJobsEntity> GetExportJobs(ExportJobsSearchFilter searchFilter)
{
_reportDataAccess = new ReportDataAccess(_context);
return(_reportDataAccess.GetExportJobs(searchFilter));
}
public byte[] CreateReportCommPool(IList <ExportJobsEntity> jobList, ExportJobsSearchFilter searchFilter)
{
try
{
StringBuilder sb = new StringBuilder();
List <string> search_criteria = new List <string>();
if (!string.IsNullOrWhiteSpace(searchFilter.FirstName))
{
search_criteria.Add(SetReportData("ชื่อ:" + searchFilter.FirstName));
}
if (!string.IsNullOrWhiteSpace(searchFilter.LastName))
{
search_criteria.Add(SetReportData("นามสกุล:" + searchFilter.LastName));
}
if (searchFilter.JobStatus != null)
{
search_criteria.Add(SetReportData("Job Status:" + searchFilter.JobStatusDesc));
}
if (!string.IsNullOrWhiteSpace(searchFilter.FromValue))
{
search_criteria.Add(SetReportData("From:" + searchFilter.FromValue));
}
if (!string.IsNullOrWhiteSpace(searchFilter.AttachFile))
{
search_criteria.Add(SetReportData("Attach File:" + searchFilter.AttachFileDesc));
}
if (!string.IsNullOrWhiteSpace(searchFilter.Subject))
{
search_criteria.Add(SetReportData("Subject:" + searchFilter.Subject));
}
if (!string.IsNullOrWhiteSpace(searchFilter.SRId))
{
search_criteria.Add(SetReportData("SR ID:" + searchFilter.SRId));
}
if (!string.IsNullOrWhiteSpace(searchFilter.ActionBranch))
{
search_criteria.Add(SetReportData("Action Branch:" + searchFilter.ActionBranchName));
}
if (!string.IsNullOrWhiteSpace(searchFilter.ActionBy))
{
search_criteria.Add(SetReportData("Action By:" + searchFilter.ActionByName));
}
if (!string.IsNullOrWhiteSpace(searchFilter.OwnerBranch))
{
search_criteria.Add(SetReportData("Owner Branch:" + searchFilter.OwnerBranchName));
}
if (!string.IsNullOrWhiteSpace(searchFilter.OwnerSR))
{
search_criteria.Add(SetReportData("Owner SR:" + searchFilter.OwnerSRName));
}
if (!string.IsNullOrWhiteSpace(searchFilter.CreatorBranch))
{
search_criteria.Add(SetReportData("Creator Branch:" + searchFilter.CreatorBranchName));
}
if (!string.IsNullOrWhiteSpace(searchFilter.CreatorSR))
{
search_criteria.Add(SetReportData("Creator SR:" + searchFilter.CreatorSRName));
}
if (!string.IsNullOrWhiteSpace(searchFilter.JobDateFrom))
{
search_criteria.Add(SetReportData("Job Date From:" + searchFilter.JobDateFrom));
}
if (!string.IsNullOrWhiteSpace(searchFilter.JobDateTo))
{
search_criteria.Add(SetReportData("Job Date To:" + searchFilter.JobDateTo));
}
if (!string.IsNullOrWhiteSpace(searchFilter.JobTimeFrom))
{
search_criteria.Add(SetReportData("Job Time From:" + searchFilter.JobTimeFrom));
}
if (!string.IsNullOrWhiteSpace(searchFilter.JobTimeTo))
{
search_criteria.Add(SetReportData("Job Time To:" + searchFilter.JobTimeTo));
}
if (!string.IsNullOrWhiteSpace(searchFilter.ActionDateFrom))
{
search_criteria.Add(SetReportData("Action Date From:" + searchFilter.ActionDateFrom));
}
if (!string.IsNullOrWhiteSpace(searchFilter.ActionDateTo))
{
search_criteria.Add(SetReportData("Action Date To:" + searchFilter.ActionDateTo));
}
if (!string.IsNullOrWhiteSpace(searchFilter.ActionTimeFrom))
{
search_criteria.Add(SetReportData("Action Time From:" + searchFilter.ActionTimeFrom));
}
if (!string.IsNullOrWhiteSpace(searchFilter.ActionTimeTo))
{
search_criteria.Add(SetReportData("Action Time To:" + searchFilter.ActionTimeTo));
}
sb.AppendLine(string.Join(",", search_criteria));
sb.AppendLine("");
string[] headers = new string[]
{
SetReportData("No"),
SetReportData("ชื่อลูกค้า"),
SetReportData("นามสกุลลูกค้า"),
SetReportData("Channel"),
SetReportData("Job Status"),
SetReportData("Job Date Time"),
SetReportData("From"),
SetReportData("Subject"),
SetReportData("SR ID."),
SetReportData("SR Creator"),
SetReportData("SR Owner"),
SetReportData("SR Status"),
SetReportData("Attach File"),
SetReportData("Remark"),
SetReportData("Pool Name")
};
sb.AppendLine(string.Join(",", headers));
int i = 0;
foreach (var item in jobList)
{
i += 1;
string[] fields = new string[]
{
SetReportData(i.ToString()),
SetReportData(item.FirstName),
SetReportData(item.LastName),
SetReportData(item.JobType),
SetReportData(item.JobStatus),
SetReportData(item.JobDateDisplay),
SetReportData(item.From),
SetReportData(ReplaceHTML(item.Subject)),
SetReportData(addSingleQuote(item.SRID)),
SetReportData(item.SRCreator),
SetReportData(item.SROwner),
SetReportData(item.SRStatus),
SetReportData(item.AttachFile),
SetReportData(ReplaceHTML(item.Remark)),
SetReportData(item.PoolName)
};
sb.AppendLine(string.Join(",", fields));
}
return(Encoding.GetEncoding(874).GetBytes(sb.ToString()));
}
catch
{
throw;
}
}