public ChartReportApiModel GetKeyAccountsReport()
{
var report = new ChartReportApiModel();
report.reportName = "KEY ACCOUNTS";
var dict = new Dictionary <ACCOUNT, long>();
var ordered = db.ACCOUNTs.OrderByDescending(c => c.DEALs.Count).Take(10).ToList();
foreach (var item in ordered)
{
//label
report.labels.Add(item.Name);
var numDealData = new Data();
numDealData.x = item.Name;
numDealData.y = item.DEALs.Count;
report.data.Add(numDealData);
var amountData = new Data();
amountData.x = item.Name;
amountData.y = item.DEALs.Aggregate(0, (long amount, DEAL next) =>
amount += next.Amount.Value);
report.data1.Add(amountData);
}
return(report);
}
public ChartReportApiModel GetTopMarketingsReport()
{
var report = new ChartReportApiModel();
report.reportName = "HIGHEST MARKET-REACH MARKETERS";
var dict = new Dictionary <USER, int>();
var leads = db.LEADs.ToList();
foreach (var lead in leads)
{
var createdUser = lead.CreatedUser;
if (createdUser != null)
{
if (dict.ContainsKey(createdUser))
{
dict[createdUser] = dict[createdUser] + 1;
}
else
{
dict[createdUser] = 1;
}
}
}
var convertedAccounts = db.ACCOUNTs.Where(c => c.ConvertFrom != 0).ToList();
foreach (var account in convertedAccounts)
{
var convertedUser = account.CreatedUser;
if (convertedUser != null)
{
if (dict.ContainsKey(convertedUser))
{
dict[convertedUser] = dict[convertedUser] + 1;
}
else
{
dict[convertedUser] = 1;
}
}
}
var ordered = dict.OrderByDescending(x => x.Value).Take(10);
foreach (var item in ordered)
{
var entry = new Data();
entry.x = item.Key.Username;
entry.y = item.Value;
report.data.Add(entry);
}
return(report);
}
public ChartReportApiModel GetAccountsByIndustryReport()
{
var report = new ChartReportApiModel();
report.reportName = "ACCOUNTS BY INDUSTRY";
var accountCounts = db.ACCOUNTs.Count();
var accountsWithIndustry = db.ACCOUNTs.Where(c => c.INDUSTRY != null);
var dict = new Dictionary <INDUSTRY, long>();
var total = 0;
foreach (var account in accountsWithIndustry)
{
var industry = account.INDUSTRY;
if (industry != null)
{
if (dict.ContainsKey(industry))
{
dict[industry] = dict[industry] + 1;
}
else
{
dict[industry] = 1;
}
total++;
}
}
var ordered = dict.OrderByDescending(c => c.Value).ToList();
foreach (var item in ordered)
{
var data = new Data();
data.x = item.Key.Name;
data.y = item.Value;
report.data.Add(data);
}
if (accountCounts - total > 0)
{
var data = new Data();
data.x = "Others";
data.y = accountCounts - total;
report.data.Add(data);
}
return(report);
}
public ChartReportApiModel GetTopSalesReport()
{
var report = new ChartReportApiModel();
report.reportName = "TOP SALES HAVE HIGH TURNOVER";
var dict = new Dictionary <USER, long>();
var deals = db.DEALs.Where(c => c.STAGE_HISTORY.Count > 0).ToList();
var wonDeals = deals.Where(c => c.STAGE_HISTORY.OrderByDescending(x => x.ModifiedAt).FirstOrDefault().STAGE_ID == (int)EnumStage.WON);
foreach (var deal in wonDeals)
{
var owner = deal.Owner;
if (owner != null)
{
if (dict.ContainsKey(owner))
{
dict[owner] = dict[owner] + deal.Amount.GetValueOrDefault();
}
else
{
dict[owner] = deal.Amount.GetValueOrDefault();
}
}
}
var ordered = dict.OrderByDescending(x => x.Value).Take(10);
foreach (var item in ordered)
{
var entry = new Data();
entry.x = item.Key.Username;
entry.y = item.Value;
report.data.Add(entry);
}
return(report);
}
public ChartReportApiModel GetRevenueComparisonReport()
{
var report = new ChartReportApiModel();
report.reportName = "MONTHLY REVENUE COMPARE TO LAST YEAR";
//get won deals
var deals = db.DEALs.Where(c => c.STAGE_HISTORY.Count > 0).ToList();
var wonDeals = deals.Where(c => c.STAGE_HISTORY.OrderByDescending(x => x.ModifiedAt).Where(x => x.ModifiedAt.Value.Year == DateTime.Now.Year || x.ModifiedAt.Value.Year == DateTime.Now.Year - 1).FirstOrDefault().STAGE_ID == (int)EnumStage.WON);
report.labels = new List <string> {
"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
};
var lastYearDict = new Dictionary <int, long>();
var thisYearDict = new Dictionary <int, long>();
for (var i = 1; i < 13; i++)
{
lastYearDict.Add(i, 0);
thisYearDict.Add(i, 0);
}
foreach (var deal in wonDeals)
{
var wonDate = deal.STAGE_HISTORY.OrderByDescending(x => x.ModifiedAt).FirstOrDefault().ModifiedAt;
if (wonDate.Value.Year == DateTime.Now.Year)
{
switch (wonDate.Value.Month)
{
case var i when(i >= 1 && i <= 12):
if (thisYearDict.ContainsKey(i))
{
thisYearDict[i] += deal.Amount.Value;
}
else
{
thisYearDict.Add(i, deal.Amount.Value);
}
break;
default:
break;
}
}
else if (wonDate.Value.Year == DateTime.Now.Year - 1)
{
switch (wonDate.Value.Month)
{
case var i when(i >= 1 && i <= 12):
if (lastYearDict.ContainsKey(i))
{
lastYearDict[i] += deal.Amount.Value;
}
else
{
lastYearDict.Add(i, deal.Amount.Value);
}
break;
default:
break;
}
}
}
var lastYearData = lastYearDict.OrderBy(c => c.Key);
var thisYearData = thisYearDict.OrderBy(c => c.Key);
foreach (var item in lastYearData)
{
var data = new Data();
data.y = item.Value;
report.data.Add(data);
}
foreach (var item in thisYearData)
{
var data = new Data();
data.y = item.Value;
report.data1.Add(data);
}
return(report);
}
public ChartReportApiModel GetAmountByStageReport()
{
var report = new ChartReportApiModel();
report.reportName = "AMOUNT BY STAGE";
var deals = db.DEALs.Where(c => c.STAGE_HISTORY.Count > 0).ToList();
//qualified deals
var qualifiedDeals = deals.Where(c => c.STAGE_HISTORY.OrderByDescending(x => x.ModifiedAt).FirstOrDefault().STAGE_ID == (int)EnumStage.QUALIFIED);
var valuePropositionDeals = deals.Where(c => c.STAGE_HISTORY.OrderByDescending(x => x.ModifiedAt).FirstOrDefault().STAGE_ID == (int)EnumStage.VALUE_PROPOSITION);
var findKeyContactsDeals = deals.Where(c => c.STAGE_HISTORY.OrderByDescending(x => x.ModifiedAt).FirstOrDefault().STAGE_ID == (int)EnumStage.FIND_KEY_CONTACTS);
var sendProposalDeals = deals.Where(c => c.STAGE_HISTORY.OrderByDescending(x => x.ModifiedAt).FirstOrDefault().STAGE_ID == (int)EnumStage.SEND_PROPOSAL);
var reviewDeals = deals.Where(c => c.STAGE_HISTORY.OrderByDescending(x => x.ModifiedAt).FirstOrDefault().STAGE_ID == (int)EnumStage.REVIEW);
var negotiateDeals = deals.Where(c => c.STAGE_HISTORY.OrderByDescending(x => x.ModifiedAt).FirstOrDefault().STAGE_ID == (int)EnumStage.NEGOTIATE);
var wonDeals = deals.Where(c => c.STAGE_HISTORY.OrderByDescending(x => x.ModifiedAt).FirstOrDefault().STAGE_ID == (int)EnumStage.WON);
var lostDeals = deals.Where(c => c.STAGE_HISTORY.OrderByDescending(x => x.ModifiedAt).FirstOrDefault().STAGE_ID == (int)EnumStage.LOST);
var qualified = new Data();
qualified.x = "Qualified";
qualified.y = qualifiedDeals.Aggregate(0, (long amount, DEAL next) =>
amount += next.Amount.Value);
var qualifiedCount = new Data();
qualifiedCount.x = "Qualified";
qualifiedCount.y = qualifiedDeals.Count();
var valueProposition = new Data();
valueProposition.x = "Value Proposition";
valueProposition.y = valuePropositionDeals.Aggregate(0, (long amount, DEAL next) =>
amount += next.Amount.Value);
var valuePropositionCount = new Data();
valuePropositionCount.x = "Value Proposition";
valuePropositionCount.y = valuePropositionDeals.Count();
var findKeyContacts = new Data();
findKeyContacts.x = "Find Key Contacts";
findKeyContacts.y = findKeyContactsDeals.Aggregate(0, (long amount, DEAL next) =>
amount += next.Amount.Value);
var findKeyContactsCount = new Data();
findKeyContactsCount.x = "Find Key Contacts";
findKeyContactsCount.y = findKeyContactsDeals.Count();
var sendProposal = new Data();
sendProposal.x = "Send Proposal";
sendProposal.y = sendProposalDeals.Aggregate(0, (long amount, DEAL next) =>
amount += next.Amount.Value);
var sendProposalCount = new Data();
sendProposalCount.x = "Send Proposal";
sendProposalCount.y = sendProposalDeals.Count();
var review = new Data();
review.x = "Review";
review.y = reviewDeals.Aggregate(0, (long amount, DEAL next) =>
amount += next.Amount.Value);
var reviewCount = new Data();
reviewCount.x = "Review";
reviewCount.y = reviewDeals.Count();
var negotiate = new Data();
negotiate.x = "Negotiate";
negotiate.y = negotiateDeals.Aggregate(0, (long amount, DEAL next) =>
amount += next.Amount.Value);
var negotiateCount = new Data();
negotiateCount.x = "Negotiate";
negotiateCount.y = negotiateDeals.Count();
var won = new Data();
won.x = "Won";
won.y = wonDeals.Aggregate(0, (long amount, DEAL next) =>
amount += next.Amount.Value);
var wonCount = new Data();
wonCount.x = "Won";
wonCount.y = wonDeals.Count();
var lost = new Data();
lost.x = "Lost";
lost.y = lostDeals.Aggregate(0, (long amount, DEAL next) =>
amount += next.Amount.Value);
var lostCount = new Data();
lostCount.x = "Lost";
lostCount.y = lostDeals.Count();
report.labels.AddRange(new List <string> {
"Qualified", "Value Proposition", "Find Key Contacts", "Send Proposal", "Review", "Negotiate", "Won", "Lost"
});
report.data.AddRange(new List <Data> {
qualified, valueProposition, findKeyContacts, sendProposal, review, negotiate, won, lost
});
report.data1.AddRange(new List <Data> {
qualifiedCount, valuePropositionCount, findKeyContactsCount, sendProposalCount, reviewCount, negotiateCount, wonCount, lostCount
});
return(report);
}
