public async Task <Array> GetEnquiryKanban(EnquiryKanbanInput input) { int TenantId = (int)(AbpSession.TenantId); using (_unitOfWorkManager.Current.SetTenantId(TenantId)) { var milestone = (from r in _MilestoneRepository.GetAll() select r).ToArray(); 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 > 0 ? a.ContactId : 0, ContactName = a.ContactId > 0 ? a.Contacts.Name + " " + a.Contacts.LastName : "", MileStoneId = a.MileStoneId, MileStoneName = a.MileStones.Name, MileStoneStatusId = a.MileStoneStatusId > 0 ? a.MileStoneStatusId : 0, MileStoneStatusName = a.MileStoneStatusId > 0 ? a.MileStoneStatuss.Name : "", Remarks = a.Remarks, CreationTime = a.CreationTime, CloseDate = a.CloseDate }).ToList(); var enquirylist = ObjectMapper.Map <List <EnquiryList> >(enquiry); var enquirykanban = new List <EnquiryKanbanArray>(); foreach (var mile in milestone) { enquirykanban.Add(new EnquiryKanbanArray { MilestoneName = mile.Name, EnquiryKanban = (from r in enquirylist where r.MileStoneId == mile.Id select r).OrderByDescending(p => p.CreationTime).ToArray() }); } return(enquirykanban.ToArray()); } }
public async Task <Array> GetInquiryKanban(EnquiryKanbanInput input) { using (_unitOfWorkManager.Current.SetTenantId((int)AbpSession.TenantId)) { string Query = "SELECT * FROM [dbo].[View_Kanban] WHERE TenantId = " + AbpSession.TenantId; var SupportMileStones = (from r in _MilestoneRepository.GetAll() where r.IsQuotation == false select r).ToArray(); var QSupportMileStones = (from r in _MilestoneRepository.GetAll() where r.IsQuotation == true select r).ToArray(); var SubListout = new List <EnquiryQuotationKanbanArray>(); 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"]), EnqQuotation = Convert.ToString(dr["QuotationInfo"]), }); var NewStatuss = NormalTicket; NewStatuss = NewStatuss.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)) ); var NewStatusdtos = NewStatuss.MapTo <List <EnquiryQuotationKanbanList> >(); foreach (var newsts in SupportMileStones) { SubListout.Add(new EnquiryQuotationKanbanArray { Flag = false, MilestoneName = newsts.Name, EndQuotation = false, Total = (from r in NewStatuss where r.MileStoneId == newsts.Id && r.QuotationId > 0 select r.Total).Sum(), EnquiryQuotationKanban = (from r in NewStatuss where r.MileStoneId == newsts.Id && r.QuotationId == 0 select r).OrderByDescending(p => p.CreationTime).ToArray() }); } foreach (var Qnewsts in QSupportMileStones) { SubListout.Add(new EnquiryQuotationKanbanArray { Flag = true, EndQuotation = Qnewsts.EndOfQuotation, MilestoneName = Qnewsts.Name, Total = (from r in NewStatuss where r.MileStoneId == Qnewsts.Id && r.QuotationId > 0 select r.Total).Sum(), EnquiryQuotationKanban = (from r in NewStatuss where r.MileStoneId == Qnewsts.Id && r.QuotationId > 0 select r).OrderByDescending(p => p.CreationTime).ToArray() }); } return(SubListout.ToArray()); } }
public async Task <Array> GetEnquiryQuotationKanbanOld(EnquiryKanbanInput input) { //int TenantId = 2; int TenantId = (int)(AbpSession.TenantId); using (_unitOfWorkManager.Current.SetTenantId(TenantId)) { var milestone = (from r in _MilestoneRepository.GetAll() select r).ToArray(); 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 EnquiryQuotationKanbanListOld { 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 : "", CreationTime = a.CreationTime }); var enquiryquotation = new List <EnquiryQuotationKanbanListOld>(); var enquiryquotationkanban = new List <EnquiryQuotationKanbanArrayOld>(); var quotation = (from a in _QuotationRepository.GetAll() select new QuotationKanbanList { Id = a.Id, SubjectName = a.SubjectName, ProposalNumber = a.ProposalNumber, EnquiryId = a.EnquiryId, EnquiryTitle = a.EnquiryId != null ? a.Enquirys.Title : "", QuotationTitleId = a.QuotationTitleId, QuotationTitleName = a.QuotationTitleId != null ? a.QuotationTitle.Name : "", MileStoneId = a.MileStoneId, CompanyId = a.CompanyId, CompanyName = a.CompanyId != null ? a.Companys.Name : "", StatusId = a.StatusId, StatusName = a.StatusId != null ? a.Status.QuotationStatusName : "", ContactId = a.ContactId, ContactName = a.ContactId != null ? a.Contacts.Name + a.Contacts.LastName : "", }).ToList(); foreach (var enq in enquiry) { var qutcon = (from r in quotation where r.EnquiryId == enq.Id select r.MileStoneId).Distinct(); var qutcount = qutcon.ToList(); if (qutcount.Count > 1) { foreach (var item in qutcon) { var mil = (from r in milestone where r.Id == item select r).FirstOrDefault(); enquiryquotation.Add(new EnquiryQuotationKanbanListOld { Id = enq.Id, EnquiryNo = enq.EnquiryNo, Title = enq.Title, CompanyId = enq.CompanyId, CompanyName = enq.CompanyName, ContactId = enq.ContactId, ContactName = enq.ContactName, MileStoneId = mil.Id, MileStoneName = mil.Name, MileStoneStatusId = enq.MileStoneStatusId, MileStoneStatusName = enq.MileStoneStatusName, CreationTime = enq.CreationTime, QuotationKanban = (from r in quotation where r.EnquiryId == enq.Id && r.MileStoneId == mil.Id select r).ToArray() }); } } else { try { enquiryquotation.Add(new EnquiryQuotationKanbanListOld { Id = enq.Id, EnquiryNo = enq.EnquiryNo, Title = enq.Title, CompanyId = enq.CompanyId, CompanyName = enq.CompanyName, ContactId = enq.ContactId, ContactName = enq.ContactName, MileStoneId = enq.MileStoneId, MileStoneName = enq.MileStoneName, MileStoneStatusId = enq.MileStoneStatusId, MileStoneStatusName = enq.MileStoneStatusName, CreationTime = enq.CreationTime, QuotationKanban = (from r in quotation where r.EnquiryId == enq.Id && r.MileStoneId == enq.MileStoneId select r).ToArray() }); } catch (Exception ex) { throw; } } } foreach (var mile in milestone) { var flag = true; if (mile.Name == "Lead" || mile.Name == "Qualified") { flag = false; } enquiryquotationkanban.Add(new EnquiryQuotationKanbanArrayOld { MilestoneName = mile.Name, Flag = flag, EnquiryQuotationKanban = (from r in enquiryquotation where r.MileStoneId == mile.Id select r).OrderByDescending(p => p.CreationTime).ToArray() }); } return(enquiryquotationkanban.ToArray()); } }
public async Task <Array> GetEnquiryQuotationsKanban(EnquiryKanbanInput input) { int TenantId = (int)(AbpSession.TenantId); using (_unitOfWorkManager.Current.SetTenantId(TenantId)) { var query = _EnquiryRepository.GetAll(); query = query.WhereIf( !input.Filter.IsNullOrEmpty(), p => p.Companys.Name.Contains(input.Filter) || p.Title.Contains(input.Filter) || p.MileStones.Name.Contains(input.Filter) || p.EnquiryNo.Contains(input.Filter) ); var SupportMileStones = (from r in _MilestoneRepository.GetAll() where r.IsQuotation == false select r).ToArray(); var QSupportMileStones = (from r in _MilestoneRepository.GetAll() where r.IsQuotation == true select r).ToArray(); var NormalTicket = (from a in query select new EnquiryQuotationKanbanList { 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, IsQuotation = a.MileStones.IsQuotation, MileStoneStatusId = a.MileStoneStatusId != null ? a.MileStoneStatusId : 0, MileStoneStatusName = a.MileStoneStatusId != null ? a.MileStoneStatuss.Name : "", CreationTime = a.CreationTime, QuotationId = 0, SubjectName = "" }).ToList(); var NewStatuss = (from r in NormalTicket where r.IsQuotation == false select r).OrderByDescending(p => p.CreationTime).ToList(); var NewStatusdtos = NewStatuss.MapTo <List <EnquiryQuotationKanbanList> >(); var SubListout = new List <EnquiryQuotationKanbanArray>(); foreach (var newsts in SupportMileStones) { var flag = true; if (newsts.Name == "Lead" || newsts.Name == "Qualified") { flag = false; } SubListout.Add(new EnquiryQuotationKanbanArray { MilestoneName = newsts.Name, Flag = flag, EnquiryQuotationKanban = (from r in NewStatuss where r.MileStoneId == newsts.Id select r).OrderByDescending(p => p.CreationTime).ToArray() }); } var QNewStatuss = (from r in NormalTicket where r.IsQuotation == true select r).OrderByDescending(p => p.CreationTime).ToList(); var NewQuotationStatus = new List <EnquiryQuotationKanbanList>(); foreach (var tickt in QNewStatuss) { var Quotation = _QuotationRepository.GetAll().Where(a => a.EnquiryId == tickt.Id).ToList(); foreach (var a in Quotation) { //try //{ NewQuotationStatus.Add(new EnquiryQuotationKanbanList { Id = a.EnquiryId ?? 0, EnquiryNo = "", Title = "", CompanyId = (int)a.CompanyId, CompanyName = a.Companys != null? a.Companys.Name: "", ContactId = a.ContactId != null ? a.ContactId : 0, ContactName = a.Contacts != null? a.Contacts.Name + " " + a.Contacts.LastName : "", MileStoneId = (int)a.MileStoneId, MileStoneName = a.MileStones.Name, IsQuotation = a.MileStones.IsQuotation, MileStoneStatusId = 0, MileStoneStatusName = "", CreationTime = a.CreationTime, QuotationId = a.Id, SubjectName = a.SubjectName }); //} //catch (Exception ex) //{ // throw ex; //} } } foreach (var Qnewsts in QSupportMileStones) { var flag = true; if (Qnewsts.Name == "Lead" || Qnewsts.Name == "Qualified") { flag = false; } SubListout.Add(new EnquiryQuotationKanbanArray { MilestoneName = Qnewsts.Name, Flag = flag, EnquiryQuotationKanban = (from r in NewQuotationStatus where r.MileStoneId == Qnewsts.Id select r).OrderByDescending(p => p.CreationTime).ToArray() }); } return(SubListout.ToArray()); } }