public DataTable GetExportVerify(ExportVerifySearchFilter searchFilter)
{
_reportDataAccess = new ReportDataAccess(_context);
IList <ExportVerifyEntity> exportVerify = _reportDataAccess.GetExportVerify(searchFilter);
DataTable dt = DataTableHelpers.ConvertTo(exportVerify);
return(dt);
}
public IList <ExportVerifyEntity> GetExportVerify(ExportVerifySearchFilter searchFilter)
{
int? productGroupId = searchFilter.ProductGroup.ToNullable <int>();
int? productId = searchFilter.Product.ToNullable <int>();
int? campaignId = searchFilter.Campaign.ToNullable <int>();
int? typeId = searchFilter.Type.ToNullable <int>();
int? areaId = searchFilter.Area.ToNullable <int>();
int? subareaId = searchFilter.SubArea.ToNullable <int>();
int? ownerId = searchFilter.OwnerSR.ToNullable <int>();
int? ownerbranchId = searchFilter.OwnerBranch.ToNullable <int>();
string selectAllValue = Constants.ApplicationStatus.All.ConvertToString();
DateTime?srMinDate = null;
DateTime?srMaxDate = null;
if (searchFilter.SRDateTimeFromValue.HasValue && searchFilter.SRDateTimeToValue.HasValue)
{
srMinDate = searchFilter.SRDateTimeFromValue.Value;
srMaxDate = searchFilter.SRDateTimeToValue.Value;
}
else
{
int?monthOfReportExport =
_context.TB_C_PARAMETER.Where(x => x.PARAMETER_NAME == Constants.ParameterName.ReportExportDate)
.FirstOrDefault().PARAMETER_VALUE.ToNullable <int>();
srMinDate = DateTime.Now.Date.AddMonths(monthOfReportExport.Value * -1);
srMaxDate = DateTime.Now.Date.AddDays(1);
}
var query = (from sr in _context.TB_T_SR.AsNoTracking()
join cs in _context.TB_M_CUSTOMER on sr.CUSTOMER_ID equals cs.CUSTOMER_ID
join ac in _context.TB_M_ACCOUNT on sr.ACCOUNT_ID equals ac.ACCOUNT_ID
join pg in _context.TB_R_PRODUCTGROUP on sr.PRODUCTGROUP_ID equals pg.PRODUCTGROUP_ID
join pr in _context.TB_R_PRODUCT on sr.PRODUCT_ID equals pr.PRODUCT_ID
join cp in _context.TB_R_CAMPAIGNSERVICE on sr.CAMPAIGNSERVICE_ID equals cp.CAMPAIGNSERVICE_ID
join ty in _context.TB_M_TYPE on sr.TYPE_ID equals ty.TYPE_ID
join ar in _context.TB_M_AREA on sr.AREA_ID equals ar.AREA_ID
join sb in _context.TB_M_SUBAREA on sr.SUBAREA_ID equals sb.SUBAREA_ID
join st in _context.TB_C_SR_STATUS on sr.SR_STATUS_ID equals st.SR_STATUS_ID
from ou in _context.TB_R_USER.Where(c => c.USER_ID == sr.OWNER_USER_ID).DefaultIfEmpty()
from ob in _context.TB_R_BRANCH.Where(c => c.BRANCH_ID == sr.OWNER_BRANCH_ID).DefaultIfEmpty()
from cb in _context.TB_R_BRANCH.Where(c => c.BRANCH_ID == sr.CREATE_BRANCH_ID).DefaultIfEmpty()
where (productGroupId == null || pg.PRODUCTGROUP_ID == productGroupId) &&
(productId == null || pr.PRODUCT_ID == productId) &&
(campaignId == null || cp.CAMPAIGNSERVICE_ID == campaignId) &&
(typeId == null || ty.TYPE_ID == typeId) &&
(areaId == null || ar.AREA_ID == areaId) &&
(subareaId == null || sb.SUBAREA_ID == subareaId) &&
(ownerId == null || ou.USER_ID == ownerId) &&
(ownerbranchId == null || sr.OWNER_BRANCH_ID == ownerbranchId) &&
(string.IsNullOrEmpty(searchFilter.SRId) || sr.SR_NO.Contains(searchFilter.SRId)) &&
(string.IsNullOrEmpty(searchFilter.SRIsverify) || selectAllValue == searchFilter.SRIsverify || sr.SR_IS_VERIFY_PASS == searchFilter.SRIsverify) &&
(sr.CREATE_DATE >= srMinDate) &&
(sr.CREATE_DATE <= srMaxDate) &&
(sr.SR_IS_VERIFY == true) &&
(string.IsNullOrEmpty(searchFilter.Description) || sr.SR_REMARK.Contains(searchFilter.Description)) &&
(string.IsNullOrEmpty(searchFilter.SRId) || sr.SR_NO == searchFilter.SRId)
select new ExportVerifyEntity
{
SRId = sr.SR_NO,
AccountNo = ac.ACCOUNT_NO,
CustomerFistname = !string.IsNullOrEmpty(cs.FIRST_NAME_TH) ? cs.FIRST_NAME_TH : cs.FIRST_NAME_EN,
CustomerLastname = !string.IsNullOrEmpty(cs.FIRST_NAME_TH) ? cs.LAST_NAME_TH : cs.LAST_NAME_EN,
ProductGroupName = pg.PRODUCTGROUP_NAME,
SROwnerName = ou.POSITION_CODE + "-" + ou.FIRST_NAME + " " + ou.LAST_NAME,
SRCreateDate = sr.CREATE_DATE,
SRCreatorBranch = cb.BRANCH_NAME,
ProductName = pr.PRODUCT_NAME,
CampaignServiceName = cp.CAMPAIGNSERVICE_NAME,
TypeName = ty.TYPE_NAME,
AreaName = ar.AREA_NAME,
SubAreaName = sb.SUBAREA_NAME,
SRSubject = sr.SR_SUBJECT,
SRDescription = sr.SR_REMARK,
SRStatus = st.SR_STATUS_NAME,
IsVerifyResult = sr.SR_IS_VERIFY_PASS,
TotalQuestion = (from Question in _context.TB_T_SR_VERIFY_RESULT_QUESTION.Where(c => c.TB_T_SR_VERIFY_RESULT_GROUP.SR_ID == sr.SR_ID) select Question).Count(),
TotalPass = (from Pass in _context.TB_T_SR_VERIFY_RESULT_QUESTION.Where(c => c.TB_T_SR_VERIFY_RESULT_GROUP.SR_ID == sr.SR_ID && c.RESULT == Constants.VerifyResultStatus.Pass) select Pass).Count(),
TotalFailed = (from Failed in _context.TB_T_SR_VERIFY_RESULT_QUESTION.Where(c => c.TB_T_SR_VERIFY_RESULT_GROUP.SR_ID == sr.SR_ID && c.RESULT == Constants.VerifyResultStatus.Fail) select Failed).Count(),
TotalDisregard = (from Disregard in _context.TB_T_SR_VERIFY_RESULT_QUESTION.Where(c => c.TB_T_SR_VERIFY_RESULT_GROUP.SR_ID == sr.SR_ID && c.RESULT == Constants.VerifyResultStatus.Skip) select Disregard).Count()
});
return(query.ToList());
}
public byte[] CreateReportVerify(IList <ExportVerifyEntity> vfList, ExportVerifySearchFilter searchFilter)
{
try
{
StringBuilder sb = new StringBuilder();
List <string> search_criteria = new List <string>();
if (!string.IsNullOrWhiteSpace(searchFilter.ProductGroup))
{
search_criteria.Add(SetReportData("ProductGroup:" + searchFilter.ProductGroupName));
}
if (!string.IsNullOrWhiteSpace(searchFilter.Product))
{
search_criteria.Add(SetReportData("Product:" + searchFilter.ProductName));
}
if (!string.IsNullOrWhiteSpace(searchFilter.Campaign))
{
search_criteria.Add(SetReportData("Campaign/Service:" + searchFilter.CampaignName));
}
if (!string.IsNullOrWhiteSpace(searchFilter.Type))
{
search_criteria.Add(SetReportData("Type:" + searchFilter.TypeName));
}
if (!string.IsNullOrWhiteSpace(searchFilter.Area))
{
search_criteria.Add(SetReportData("Area:" + searchFilter.AreaName));
}
if (!string.IsNullOrWhiteSpace(searchFilter.SubArea))
{
search_criteria.Add(SetReportData("Sub Area:" + searchFilter.SubAreaName));
}
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.SRId))
{
search_criteria.Add(SetReportData("SR ID:" + searchFilter.SRId));
}
if (!string.IsNullOrWhiteSpace(searchFilter.SRIsverify))
{
search_criteria.Add(SetReportData("Verify Result:" + searchFilter.SRIsverifyName));
}
if (!string.IsNullOrWhiteSpace(searchFilter.Description))
{
search_criteria.Add(SetReportData("Description:" + searchFilter.Description));
}
if (!string.IsNullOrWhiteSpace(searchFilter.SRDateFrom))
{
search_criteria.Add(SetReportData("SR Created Date From:" + searchFilter.SRDateFrom));
}
if (!string.IsNullOrWhiteSpace(searchFilter.SRDateTo))
{
search_criteria.Add(SetReportData("SR Created Date To:" + searchFilter.SRDateTo));
}
if (!string.IsNullOrWhiteSpace(searchFilter.SRTimeFrom))
{
search_criteria.Add(SetReportData("SR Created Time From:" + searchFilter.SRTimeFrom));
}
if (!string.IsNullOrWhiteSpace(searchFilter.SRTimeTo))
{
search_criteria.Add(SetReportData("SR Created Time To:" + searchFilter.SRTimeTo));
}
sb.AppendLine(string.Join(",", search_criteria));
sb.AppendLine("");
string[] headers = new string[]
{
SetReportData("No"),
SetReportData("SR ID"),
SetReportData("เลขที่สัญญา / เลขที่บัญชีชื่อลูกค้า"),
SetReportData("ชื่อลูกค้า"),
SetReportData("นามสกุลลูกค้า"),
SetReportData("SR Owner"),
SetReportData("SR Created Date Time"),
SetReportData("SR Creator Branch"),
SetReportData("Product Group"),
SetReportData("Product"),
SetReportData("Campaign/Service"),
SetReportData("Type"),
SetReportData("Area"),
SetReportData("Sub-Area"),
SetReportData("Subject"),
SetReportData("SR Description"),
SetReportData("SR Status"),
SetReportData("Verify Result"),
SetReportData("Total Question"),
SetReportData("Total Passed"),
SetReportData("Total Failed"),
SetReportData("Total disregarded")
};
sb.AppendLine(string.Join(",", headers));
int i = 0;
foreach (var item in vfList)
{
i += 1;
string[] fields = new string[]
{
SetReportData(i.ToString()),
SetReportData(addSingleQuote(item.SRId)),
SetReportData(addSingleQuote(item.AccountNo)),
SetReportData(item.CustomerFistname),
SetReportData(item.CustomerLastname),
SetReportData(item.SROwnerName),
SetReportData(item.SRCreateDateDisplay),
SetReportData(item.SRCreatorBranch),
SetReportData(item.ProductGroupName),
SetReportData(item.ProductName),
SetReportData(item.CampaignServiceName),
SetReportData(item.TypeName),
SetReportData(item.AreaName),
SetReportData(item.SubAreaName),
SetReportData(ReplaceHTML(item.SRSubject)),
SetReportData(ReplaceHTML(item.SRDescDisplay)),
SetReportData(item.SRStatus),
SetReportData(item.IsVerifyResultDisplay),
SetReportData(item.TotalQuestion.ToString()),
SetReportData(item.TotalPass.ToString()),
SetReportData(item.TotalFailed.ToString()),
SetReportData(item.TotalDisregard.ToString())
};
sb.AppendLine(string.Join(",", fields));
}
return(Encoding.GetEncoding(874).GetBytes(sb.ToString()));
}
catch
{
throw;
}
}
//public DataTable GetExportVerify(ExportVerifySearchFilter searchFilter)
//{
// _reportDataAccess = new ReportDataAccess(_context);
// IList<ExportVerifyEntity> exportVerify = _reportDataAccess.GetExportVerify(searchFilter);
// DataTable dt = DataTableHelpers.ConvertTo(exportVerify);
// return dt;
//}
public IList <ExportVerifyEntity> GetExportVerify(ExportVerifySearchFilter searchFilter)
{
_reportDataAccess = new ReportDataAccess(_context);
return(_reportDataAccess.GetExportVerify(searchFilter));
}
