public DataTable GetExportNcb(ExportNcbSearchFilter searchFilter) { _reportDataAccess = new ReportDataAccess(_context); IList <ExportNcbEntity> exportNcb = _reportDataAccess.GetExportNcb(searchFilter); DataTable dt = DataTableHelpers.ConvertTo(exportNcb); return(dt); }
public IList <ExportNcbEntity> GetExportNcb(ExportNcbSearchFilter 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>(); int?slaId = searchFilter.Sla.ToNullable <int>(); int?srstatusId = searchFilter.SRStatus.ToNullable <int>(); int?upperbranchId = searchFilter.UpperBranch.ToNullable <int>(); int?creatorbranchId = searchFilter.CreatorBranch.ToNullable <int>(); int?creatorsrId = searchFilter.CreatorSR.ToNullable <int>(); int?delegatebranchId = searchFilter.DelegateBranch.ToNullable <int>(); int?delegatesrId = searchFilter.DelegateSR.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 NCB" List <int> lstRole = _context.TB_C_ROLE.Where (o => (o.ROLE_CODE == Constants.SrRoleCode.NCB) ).Select(o => o.ROLE_ID).ToList(); #endregion var query = (from sr in _context.TB_T_SR.AsNoTracking() from cs in _context.TB_M_CONTACT.Where(c => c.CONTACT_ID == sr.CONTACT_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 ou in _context.TB_R_USER.Where(c => c.USER_ID == sr.OWNER_USER_ID).DefaultIfEmpty() from cu in _context.TB_R_USER.Where(c => c.USER_ID == sr.CREATE_USER).DefaultIfEmpty() from du in _context.TB_R_USER.Where(c => c.USER_ID == sr.DELEGATE_USER_ID).DefaultIfEmpty() from st in _context.TB_C_SR_STATUS.Where(c => c.SR_STATUS_ID == sr.SR_STATUS_ID).DefaultIfEmpty() from mk in _context.TB_R_USER.Where(c => c.USER_ID == sr.SR_NCB_MARKETING_USER_ID).DefaultIfEmpty() where (sr.SR_PAGE_ID == Constants.SRPage.NCBPageId || (cu.ROLE_ID.HasValue && lstRole.Contains(cu.ROLE_ID.Value))) && (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)) && (!searchFilter.BirthDateValue.HasValue || sr.SR_NCB_CUSTOMER_BIRTHDATE == searchFilter.BirthDateValue.Value) && (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 || sr.OWNER_USER_ID == ownerId) && (ownerbranchId == null || sr.OWNER_BRANCH_ID == ownerbranchId) && (string.IsNullOrEmpty(searchFilter.SRId) || sr.SR_NO.Contains(searchFilter.SRId)) && (sr.CREATE_DATE >= srMinDate) && (sr.CREATE_DATE <= srMaxDate) //&& (customerTypeId == null || customerTypeId == Constants.ApplicationStatus.All || ct.TYPE == customerTypeId) && (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 == Constants.ApplicationStatus.All || sr.SR_STATUS_ID == srstatusId) && (upperbranchId == null || sr.SR_NCB_MARKETING_BRANCH_UPPER_1_ID == upperbranchId || sr.SR_NCB_MARKETING_BRANCH_UPPER_2_ID == upperbranchId || sr.SR_NCB_MARKETING_BRANCH_ID == upperbranchId) && (creatorsrId == null || sr.CREATE_USER == creatorsrId) && (creatorbranchId == null || sr.CREATE_BRANCH_ID == creatorbranchId) && (delegatesrId == null || sr.DELEGATE_USER_ID == delegatesrId) && (delegatebranchId == null || sr.DELEGATE_BRANCH_ID == delegatebranchId) select new ExportNcbEntity { Sla = sr.RULE_TOTAL_ALERT.HasValue ? sr.RULE_TOTAL_ALERT.ToString() : "", 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, CustomerBirthDate = sr.SR_NCB_CUSTOMER_BIRTHDATE, NcbCheckStatus = sr.SR_NCB_CHECK_STATUS, SRId = sr.SR_NO, SRStatus = st.SR_STATUS_NAME, ProductGroupName = pg.PRODUCTGROUP_NAME, ProductName = pr.PRODUCT_NAME, CampaignName = cp.CAMPAIGNSERVICE_NAME, TypeName = ty.TYPE_NAME, AreaName = ar.AREA_NAME, SubAreaName = sb.SUBAREA_NAME, SRCreator = cu.POSITION_CODE + "-" + cu.FIRST_NAME + " " + cu.LAST_NAME, SRCreateDate = sr.CREATE_DATE, SROwner = ou.POSITION_CODE + "-" + ou.FIRST_NAME + " " + ou.LAST_NAME, OwnerUpdate = sr.UPDATE_DATE_BY_OWNER, SRDelegate = du.POSITION_CODE + "-" + du.FIRST_NAME + " " + du.LAST_NAME, SRDelegateUpdate = sr.UPDATE_DATE_BY_DELEGATE, MKTUpperBranch1 = sr.SR_NCB_MARKETING_BRANCH_UPPER_1_NAME, MKTUpperBranch2 = sr.SR_NCB_MARKETING_BRANCH_UPPER_2_NAME, MKTEmployeeBranch = sr.SR_NCB_MARKETING_BRANCH_NAME, MKTEmployeeName = mk.POSITION_CODE + "-" + mk.FIRST_NAME + " " + mk.LAST_NAME }); return(query.ToList()); }
public byte[] CreateReportNCB(IList <ExportNcbEntity> ncbList, ExportNcbSearchFilter 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.BirthDate)) { search_criteria.Add(SetReportData("Date of Birth/วันที่จดทะเบียน: " + searchFilter.BirthDate)); } if (!string.IsNullOrWhiteSpace(searchFilter.Product)) { search_criteria.Add(SetReportData("Product: " + searchFilter.ProductName)); } if (!string.IsNullOrWhiteSpace(searchFilter.Sla)) { search_criteria.Add(SetReportData("SLA: " + searchFilter.SlaName)); } if (!string.IsNullOrWhiteSpace(searchFilter.Campaign)) { search_criteria.Add(SetReportData("Campaign/Service: " + searchFilter.CampaignName)); } if (!string.IsNullOrWhiteSpace(searchFilter.SRStatus)) { search_criteria.Add(SetReportData("SR Status: " + searchFilter.SRStatusName)); } 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.UpperBranch)) { search_criteria.Add(SetReportData("Marketing Branch: " + searchFilter.UpperBranchName)); } 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.DelegateBranch)) { search_criteria.Add(SetReportData("Delegate Branch: " + searchFilter.DelegateBranchName)); } if (!string.IsNullOrWhiteSpace(searchFilter.DelegateSR)) { search_criteria.Add(SetReportData("Deletegate SR: " + searchFilter.DelegateSRName)); } if (!string.IsNullOrWhiteSpace(searchFilter.SRId)) { search_criteria.Add(SetReportData("SR ID: " + searchFilter.SRId)); } 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("จำนวนครั้งที่เกิน SLA ทั้งหมด"), SetReportData("ชื่อลูกค้า"), SetReportData("นามสกุลลูกค้า"), SetReportData("Subscription ID"), SetReportData("วันเกิด/วันจดทะเบียน"), SetReportData("NCB Check Status"), SetReportData("SR ID"), SetReportData("SR Status"), SetReportData("Product Group"), SetReportData("Product"), SetReportData("Campaign"), SetReportData("Type"), SetReportData("Area"), SetReportData("Sub-Area"), SetReportData("SR Creator"), SetReportData("SR Created Date Time"), SetReportData("SR Owner"), SetReportData("Owner Updated Date Time"), SetReportData("SR Delegate"), SetReportData("Delegate Updated Date Time"), SetReportData("Marketing Upper Branch1"), SetReportData("Marketing Upper Branch2"), SetReportData("MKT Employee Branch"), SetReportData("MKT Employee Name"), }; sb.AppendLine(string.Join(",", headers)); int i = 0; foreach (var item in ncbList) { i += 1; string[] fields = new string[] { SetReportData(i.ToString()), SetReportData(item.Sla), SetReportData(item.CustomerFistname), SetReportData(item.CustomerLastname), SetReportData(addSingleQuote(item.CardNo)), SetReportData(item.CustomerBirthDateDisplay), SetReportData(item.NcbCheckStatus), SetReportData(addSingleQuote(item.SRId)), SetReportData(item.SRStatus), SetReportData(item.ProductGroupName), SetReportData(item.ProductName), SetReportData(item.CampaignName), SetReportData(item.TypeName), SetReportData(item.AreaName), SetReportData(item.SubAreaName), SetReportData(item.SRCreator), SetReportData(item.SRCreateDateDisplay), SetReportData(item.SROwner), SetReportData(item.OwnerUpdateDisplay), SetReportData(item.SRDelegate), SetReportData(item.SRDelegateUpdateDisplay), SetReportData(item.MKTUpperBranch1), SetReportData(item.MKTUpperBranch2), SetReportData(item.MKTEmployeeBranch), SetReportData(item.MKTEmployeeName) }; sb.AppendLine(string.Join(",", fields)); } return(Encoding.GetEncoding(874).GetBytes(sb.ToString())); } catch { throw; } }
//public DataTable GetExportNcb(ExportNcbSearchFilter searchFilter) //{ // _reportDataAccess = new ReportDataAccess(_context); // IList<ExportNcbEntity> exportNcb = _reportDataAccess.GetExportNcb(searchFilter); // DataTable dt = DataTableHelpers.ConvertTo(exportNcb); // return dt; //} public IList <ExportNcbEntity> GetExportNcb(ExportNcbSearchFilter searchFilter) { _reportDataAccess = new ReportDataAccess(_context); return(_reportDataAccess.GetExportNcb(searchFilter)); }