public DataTable GetExportSR(ExportSRSearchFilter searchFilter)
{
_reportDataAccess = new ReportDataAccess(_context);
IList <ExportSREntity> exportSR = _reportDataAccess.GetExportSR(searchFilter);
DataTable dt = DataTableHelpers.ConvertTo(exportSR);
return(dt);
}
public IList <ExportSREntity> GetExportSR(ExportSRSearchFilter 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?slaId = searchFilter.Sla.ToNullable <int>();
int?srstatusId = searchFilter.SRStatus.ToNullable <int>();
int?channelId = searchFilter.SRChannel.ToNullable <int>();
int?ownerId = searchFilter.OwnerSR.ToNullable <int>();
int?ownerBranchId = searchFilter.OwnerBranch.ToNullable <int>();
int?creatorId = searchFilter.CreatorSR.ToNullable <int>();
int?creatorBranchId = searchFilter.CreatorBranch.ToNullable <int>();
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);
}
#region "Find Role CallCenter"
List <int> lstRole = _context.TB_C_ROLE.Where
(o => (o.ROLE_CODE == Constants.SrRoleCode.ContactCenterAgent || o.ROLE_CODE == Constants.SrRoleCode.ContactCenterFollowUp ||
o.ROLE_CODE == Constants.SrRoleCode.ContactCenterManager || o.ROLE_CODE == Constants.SrRoleCode.ContactCenterSupervisor)
).Select(o => o.ROLE_ID).ToList();
#endregion
var query = (from sr in _context.TB_T_SR.AsNoTracking()
from cs in _context.TB_M_CUSTOMER.Where(c => c.CUSTOMER_ID == sr.CUSTOMER_ID).DefaultIfEmpty()
from ac in _context.TB_M_ACCOUNT.Where(c => c.ACCOUNT_ID == sr.ACCOUNT_ID).DefaultIfEmpty()
from ch in _context.TB_R_CHANNEL.Where(c => c.CHANNEL_ID == sr.CHANNEL_ID).DefaultIfEmpty()
from pg in _context.TB_R_PRODUCTGROUP.Where(c => c.PRODUCTGROUP_ID == sr.PRODUCTGROUP_ID).DefaultIfEmpty()
from pr in _context.TB_R_PRODUCT.Where(c => c.PRODUCT_ID == sr.PRODUCT_ID).DefaultIfEmpty()
from cp in _context.TB_R_CAMPAIGNSERVICE.Where(c => c.CAMPAIGNSERVICE_ID == sr.CAMPAIGNSERVICE_ID).DefaultIfEmpty()
from ty in _context.TB_M_TYPE.Where(c => c.TYPE_ID == sr.TYPE_ID).DefaultIfEmpty()
from ar in _context.TB_M_AREA.Where(c => c.AREA_ID == sr.AREA_ID).DefaultIfEmpty()
from sb in _context.TB_M_SUBAREA.Where(c => c.SUBAREA_ID == sr.SUBAREA_ID).DefaultIfEmpty()
from st in _context.TB_C_SR_STATUS.Where(c => c.SR_STATUS_ID == sr.SR_STATUS_ID).DefaultIfEmpty()
from cu in _context.TB_R_USER.Where(c => c.USER_ID == sr.CREATE_USER).DefaultIfEmpty()
from ou in _context.TB_R_USER.Where(c => c.USER_ID == sr.OWNER_USER_ID).DefaultIfEmpty()
from du in _context.TB_R_USER.Where(c => c.USER_ID == sr.DELEGATE_USER_ID).DefaultIfEmpty()
from ct in _context.TB_M_CONTACT.Where(c => c.CONTACT_ID == sr.CONTACT_ID).DefaultIfEmpty()
from me in _context.TB_M_MEDIA_SOURCE.Where(c => c.MEDIA_SOURCE_ID == sr.MEDIA_SOURCE_ID).DefaultIfEmpty()
from jb in _context.TB_T_JOB.Where(c => c.SR_ID == sr.SR_ID).DefaultIfEmpty()
from jc in _context.TB_R_CHANNEL.Where(c => c.CHANNEL_ID == jb.CHANNEL_ID).DefaultIfEmpty()
from cb in _context.TB_R_BRANCH.Where(c => c.BRANCH_ID == sr.CREATE_BRANCH_ID).DefaultIfEmpty()
from ob in _context.TB_R_BRANCH.Where(o => o.BRANCH_ID == sr.OWNER_BRANCH_ID).DefaultIfEmpty()
from rp in _context.TB_M_RELATIONSHIP.Where(c => c.RELATIONSHIP_ID == sr.CONTACT_RELATIONSHIP_ID).DefaultIfEmpty()
where
(sr.SR_PAGE_ID == Constants.SRPage.DefaultPageId || sr.SR_PAGE_ID == Constants.SRPage.AFSPageId || !sr.SR_PAGE_ID.HasValue ||
(cu.ROLE_ID.HasValue && lstRole.Contains(cu.ROLE_ID.Value))
) &&
(ownerId == null || sr.OWNER_USER_ID == ownerId) &&
(ownerBranchId == null || sr.OWNER_BRANCH_ID == ownerBranchId) &&
(creatorId == null || sr.CREATE_USER == creatorId) &&
(creatorBranchId == null || sr.CREATE_BRANCH_ID == creatorBranchId) &&
(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()))) &&
(string.IsNullOrEmpty(searchFilter.CardId) || cs.CARD_NO.Contains(searchFilter.CardId)) &&
(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) &&
(slaId == null || slaId == Constants.ApplicationStatus.All ||
(slaId == 1 && (sr.RULE_THIS_ALERT == null || sr.RULE_THIS_ALERT == 0)) ||
(slaId == 2 && (sr.RULE_THIS_ALERT.HasValue && sr.RULE_THIS_ALERT > 0))
) &&
(srstatusId == null || srstatusId == -1 || st.SR_STATUS_ID == srstatusId) &&
(channelId == null || channelId == -1 || ch.CHANNEL_ID == channelId) &&
(sr.CREATE_DATE >= srMinDate) &&
(sr.CREATE_DATE <= srMaxDate) &&
(string.IsNullOrEmpty(searchFilter.Subject) || sr.SR_SUBJECT.Contains(searchFilter.Subject)) &&
(string.IsNullOrEmpty(searchFilter.Description) || sr.SR_REMARK.Contains(searchFilter.Description)) &&
(string.IsNullOrEmpty(searchFilter.SRId) || sr.SR_NO.Contains(searchFilter.SRId)) &&
(string.IsNullOrEmpty(searchFilter.AccountNo) || ac.ACCOUNT_NO == searchFilter.AccountNo)
select new ExportSREntity
{
TotalSla = sr.RULE_TOTAL_ALERT, //sr.RULE_TOTAL_WORK,
CurrentAlert = sr.RULE_THIS_ALERT,
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,
CardNo = cs.CARD_NO,
AccountNo = ac.ACCOUNT_NO,
CarRegisNo = ac.CAR_NO,
SRNo = sr.SR_NO,
CreatorBranch = cb.BRANCH_NAME,
ChannelName = ch.CHANNEL_NAME,
CallId = sr.SR_CALL_ID,
ANo = sr.SR_ANO,
ProductGroupName = pg.PRODUCTGROUP_NAME,
ProductName = pr.PRODUCT_NAME,
CampaignServiceName = cp.CAMPAIGNSERVICE_NAME,
TypeName = ty.TYPE_NAME,
AreaName = ar.AREA_NAME,
SubAreaName = sb.SUBAREA_NAME,
SRStatusName = st.SR_STATUS_NAME,
CloseDate = sr.CLOSE_DATE,
SRIsverifyPass = sr.SR_IS_VERIFY_PASS,
CreatorName = cu.POSITION_CODE + "-" + cu.FIRST_NAME + " " + cu.LAST_NAME,
CreateDate = sr.CREATE_DATE,
OwnerName = ou.POSITION_CODE + "-" + ou.FIRST_NAME + " " + ou.LAST_NAME,
UpdateDateOwner = sr.UPDATE_DATE_BY_OWNER,
DelegatorName = du.POSITION_CODE + "-" + du.FIRST_NAME + " " + du.LAST_NAME,
UpdateDelegate = sr.UPDATE_DATE_BY_DELEGATE,
SRSubject = sr.SR_SUBJECT,
SRRemark = sr.SR_REMARK,
ContactName = ct.FIRST_NAME_TH,
ContactSurname = ct.LAST_NAME_TH,
Relationship = rp.RELATIONSHIP_NAME,
MediaSourceName = me.MEDIA_SOURCE_NAME,
AttachFile = sr.TB_T_SR_ATTACHMENT.Any() ? "Yes" : "No",
JobType = jc.CHANNEL_NAME,
ContactNo = (from p in ct.TB_M_CONTACT_PHONE
where p.TB_M_PHONE_TYPE.PHONE_TYPE_CODE == Constants.PhoneTypeCode.Mobile
select new
{
PhoneNo = p.PHONE_NO
}).FirstOrDefault().PhoneNo
});
return(query.ToList());
}
//public DataTable GetExportSR(ExportSRSearchFilter searchFilter)
//{
// _reportDataAccess = new ReportDataAccess(_context);
// IList<ExportSREntity> exportSR = _reportDataAccess.GetExportSR(searchFilter);
// DataTable dt = DataTableHelpers.ConvertTo(exportSR);
// return dt;
//}
public IList <ExportSREntity> GetExportSR(ExportSRSearchFilter searchFilter)
{
_reportDataAccess = new ReportDataAccess(_context);
return(_reportDataAccess.GetExportSR(searchFilter));
}
public byte[] CreateReportSR(IList <ExportSREntity> srList, ExportSRSearchFilter 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 (!string.IsNullOrWhiteSpace(searchFilter.CardId))
{
search_criteria.Add(SetReportData("Subscription ID:" + searchFilter.CardId));
}
if (!string.IsNullOrWhiteSpace(searchFilter.ProductGroup))
{
search_criteria.Add(SetReportData("Product Group:" + searchFilter.ProductGroupName));
}
if (!string.IsNullOrWhiteSpace(searchFilter.AccountNo))
{
search_criteria.Add(SetReportData("เลขที่บัญชี/สัญญา:" + searchFilter.AccountNo));
}
if (!string.IsNullOrWhiteSpace(searchFilter.Product))
{
search_criteria.Add(SetReportData("Product:" + searchFilter.ProductName));
}
if (!string.IsNullOrWhiteSpace(searchFilter.Type))
{
search_criteria.Add(SetReportData("Type:" + searchFilter.TypeName));
}
if (!string.IsNullOrWhiteSpace(searchFilter.Campaign))
{
search_criteria.Add(SetReportData("Campaign/Service:" + searchFilter.CampaignName));
}
if (!string.IsNullOrWhiteSpace(searchFilter.Area))
{
search_criteria.Add(SetReportData("Area:" + searchFilter.AreaName));
}
if (!string.IsNullOrWhiteSpace(searchFilter.SRId))
{
search_criteria.Add(SetReportData("SR ID:" + searchFilter.SRId));
}
if (!string.IsNullOrWhiteSpace(searchFilter.SubArea))
{
search_criteria.Add(SetReportData("Sub Area:" + searchFilter.SubAreaName));
}
if (!string.IsNullOrWhiteSpace(searchFilter.Subject))
{
search_criteria.Add(SetReportData("Subject:" + searchFilter.Subject));
}
if (!string.IsNullOrWhiteSpace(searchFilter.Sla))
{
search_criteria.Add(SetReportData("SLA:" + searchFilter.SlaName));
}
if (!string.IsNullOrWhiteSpace(searchFilter.Description))
{
search_criteria.Add(SetReportData("Description:" + searchFilter.Description));
}
if (!string.IsNullOrWhiteSpace(searchFilter.SRChannel))
{
search_criteria.Add(SetReportData("SR Channel:" + searchFilter.SRChannelName));
}
if (!string.IsNullOrWhiteSpace(searchFilter.SRStatus))
{
search_criteria.Add(SetReportData("SR Status:" + searchFilter.SRStatusName));
}
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));
}
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));
}
sb.AppendLine(string.Join(",", search_criteria));
sb.AppendLine("");
string[] headers = new string[]
{
SetReportData("No"),
SetReportData("จำนวนครั้งที่เกิน SLA ทั้งหมด"),
SetReportData("แจ้งเตือนครั้งที่"),
SetReportData("ชื่อลูกค้า"),
SetReportData("นามสกุล"),
SetReportData("Subscription ID"),
SetReportData("เลขที่สัญญา / เลขที่บัญชี"),
SetReportData("ทะเบียนรถ"),
SetReportData("SR ID"),
SetReportData("SR Creator Branch"),
SetReportData("Channel"),
SetReportData("Call ID"),
SetReportData("Tell (A-Number)"),
SetReportData("Product Group"),
SetReportData("Product"),
SetReportData("Campaign/Service"),
SetReportData("Type"),
SetReportData("Area"),
SetReportData("Sub-Area"),
SetReportData("SR Status"),
SetReportData("Closed By"),
SetReportData("Closed Date Time"),
SetReportData("Verify Result"),
SetReportData("SR Creator"),
SetReportData("SR Created Date Time"),
SetReportData("Updated By"),
SetReportData("Updated Date Time"),
SetReportData("SR Owner"),
SetReportData("Owner Updated Date Time"),
SetReportData("SR Delegate"),
SetReportData("Delegate Updated Date Time"),
SetReportData("Subject"),
SetReportData("SR Description"),
SetReportData("ชื่อผู้ติดต่อ"),
SetReportData("นามสกุลผู้ติดต่อ"),
SetReportData("ความสัมพันธ์"),
SetReportData("เบอร์ติดต่อ"),
SetReportData("media source")
};
sb.AppendLine(string.Join(",", headers));
int i = 0;
foreach (var item in srList)
{
i += 1;
string[] fields = new string[]
{
SetReportData(i.ToString()),
SetReportData(item.TotalSla != null ? item.TotalSla.ToString() : ""),
SetReportData(item.CurrentAlert != null ? item.CurrentAlert.ToString() : ""),
SetReportData(item.CustomerFistname),
SetReportData(item.CustomerLastname),
SetReportData(addSingleQuote(item.CardNo)),
SetReportData(addSingleQuote(item.AccountNo)),
SetReportData(item.CarRegisNo),
SetReportData(addSingleQuote(item.SRNo)),
SetReportData(item.CreatorBranch),
SetReportData(item.ChannelName),
SetReportData(addSingleQuote(item.CallId)),
SetReportData(addSingleQuote(item.ANo)),
SetReportData(item.ProductGroupName),
SetReportData(item.ProductName),
SetReportData(item.CampaignServiceName),
SetReportData(item.TypeName),
SetReportData(item.AreaName),
SetReportData(item.SubAreaName),
SetReportData(item.SRStatusName),
SetReportData(item.CloseUserName),
SetReportData(item.CloseDateDisplay),
SetReportData(item.SRIsverifyPassDisplay),
SetReportData(item.CreatorName),
SetReportData(item.CreateDateDisplay),
SetReportData(item.UpdaterName),
SetReportData(item.UpdateDate.ToDisplay()),
SetReportData(item.OwnerName),
SetReportData(item.UpdateDateOwnerDisplay),
SetReportData(item.DelegatorName),
SetReportData(item.UpdateDelegateDisplay),
SetReportData(ReplaceHTML(item.SRSubject)),
SetReportData(ReplaceHTML(item.SRRemarkDisplay)),
SetReportData(item.ContactName),
SetReportData(item.ContactSurname),
SetReportData(item.Relationship),
SetReportData(addSingleQuote(item.ContactNo)),
SetReportData(item.MediaSourceName)
};
sb.AppendLine(string.Join(",", fields));
}
return(Encoding.GetEncoding(874).GetBytes(sb.ToString()));
}
catch
{
throw;
}
}
