public async Task <PagedResultDto <EnquiryList> > GetEnquiryGrid(EnquiryListInput input) { int TenantId = (int)(AbpSession.TenantId); using (_unitOfWorkManager.Current.SetTenantId(TenantId)) { var query = _EnquiryRepository.GetAll() .WhereIf( !input.Filter.IsNullOrEmpty(), p => p.EnquiryNo.Contains(input.Filter) || p.Title.Contains(input.Filter) || p.Companys.Name.Contains(input.Filter) || p.Contacts.Name.Contains(input.Filter) || p.MileStones.Name.Contains(input.Filter) ); var enquiry = (from a in query select new EnquiryList { Id = a.Id, EnquiryNo = a.EnquiryNo, Title = a.Title, CompanyId = a.CompanyId, CompanyName = a.Companys.Name, ContactId = a.ContactId != null ? a.ContactId : 0, ContactName = a.ContactId != null ? a.Contacts.Name + " " + a.Contacts.LastName : "", MileStoneId = a.MileStoneId, MileStoneName = a.MileStones.Name, MileStoneStatusId = a.MileStoneStatusId != null ? a.MileStoneStatusId : 0, MileStoneStatusName = a.MileStoneStatusId != null ? a.MileStoneStatuss.Name : "", Remarks = a.Remarks, CreationTime = a.CreationTime, EstimationValue = a.EstimationValue }); var datacount = enquiry.Count(); var data = await enquiry.OrderBy(input.Sorting).PageBy(input).ToListAsync(); var enquirylistresult = ObjectMapper.Map <List <EnquiryList> >(data); return(new PagedResultDto <EnquiryList>(datacount, enquirylistresult)); } }
public async Task <PagedResultDto <EnquiryQuotationKanbanList> > GetGlobalReport(EnquiryListInput input) { int TenantId = (int)(AbpSession.TenantId); using (_unitOfWorkManager.Current.SetTenantId(TenantId)) { string Query = "SELECT * FROM [dbo].[View_Kanban] WHERE TenantId = " + TenantId; ConnectionAppService db = new ConnectionAppService(); DataTable dt = new DataTable(); SqlConnection con3 = new SqlConnection(db.ConnectionString()); con3.Open(); SqlCommand cmd3 = new SqlCommand(Query, con3); DataTable dt3 = new DataTable(); using (SqlDataAdapter sda = new SqlDataAdapter(cmd3)) { sda.Fill(dt); } var NormalTicket = (from DataRow dr in dt.Rows select new EnquiryQuotationKanbanList { Id = Convert.ToInt32(dr["Id"]), Title = Convert.ToString(dr["Title"]), EnquiryNo = Convert.ToString(dr["EnquiryNo"]), MileStoneId = Convert.ToInt32(dr["MileStoneId"]), MileStoneStatusName = Convert.ToString(dr["StatusName"]), CompanyName = Convert.ToString(dr["CompanyName"]), ContactName = Convert.ToString(dr["ContactName"]), QuotationId = Convert.ToInt32(dr["QuotationId"]), QRefno = Convert.ToString(dr["QRefno"]), CreationTime = Convert.ToDateTime(dr["CreationTime"]), CloseDate = Convert.ToDateTime(dr["CloseDate"]), Creator = Convert.ToString(dr["Creator"]), CreatorImg = Convert.ToString(dr["CreatorImg"]), Salesperson = Convert.ToString(dr["Salesperson"]), SalespersonImg = Convert.ToString(dr["SalespersonImg"]), Remarks = Convert.ToString(dr["Remarks"]), Total = Convert.ToDecimal(dr["Total"]), }); NormalTicket = NormalTicket.WhereIf( !input.Filter.IsNullOrEmpty(), p => p.Title.ToLower().Replace(" ", string.Empty).Contains(input.Filter.ToLower().Replace(" ", string.Empty)) || p.EnquiryNo.ToLower().Replace(" ", string.Empty).Contains(input.Filter.ToLower().Replace(" ", string.Empty)) || p.CompanyName.ToLower().Replace(" ", string.Empty).Contains(input.Filter.ToLower().Replace(" ", string.Empty)) || p.MileStoneStatusName.ToLower().Replace(" ", string.Empty).Contains(input.Filter.ToLower().Replace(" ", string.Empty)) || p.QRefno.ToLower().Replace(" ", string.Empty).Contains(input.Filter.ToLower().Replace(" ", string.Empty)) ); var NewStatussCount = NormalTicket.Count(); var NewStatussList = NormalTicket.OrderByDescending(p => p.CreationTime).Skip(input.SkipCount).Take(input.MaxResultCount).ToList(); var NewStatusdtos = NewStatussList.MapTo <List <EnquiryQuotationKanbanList> >(); return(new PagedResultDto <EnquiryQuotationKanbanList>(NewStatussCount, NewStatusdtos)); } }