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());
}
}