public JsonResult _GetIncomebyProjectInMonth()
{
var totaldeals = CRM_Logical.GetDeals(true);
totaldeals = totaldeals.Where(w => w.Income > 0);
var dealprojectmonth = from d in totaldeals
group d by new { d.ActualPaymentDate.Value.Month, d.ActualPaymentDate.Value.Year,d.Project.Name_CH }
into grp
select new
{
Year = grp.Key.Year,
Month = grp.Key.Month,
IncomeAmount = grp.Sum(c => c.Income),
ProjectName = grp.Key.Name_CH,
Category = grp.Key.Year.ToString()+"年"+grp.Key.Month.ToString()+"月",
};
var ps = from p in CH.DB.Projects.Where(p=>p.IsActived==true)
select new {
Name =p.Name_CH,
Categories = string.Join(",", dealprojectmonth.Where(w=>w.ProjectName == p.Name_CH).Select(s=>s.Category) ),
IncomeAmount = string.Join(",", dealprojectmonth.Where(w => w.ProjectName == p.Name_CH).Select(s => s.IncomeAmount)),
};
//var data = from p in ps{}
// from p in ps
// group d by new { d.ActualPaymentDate.Value.Month, d.ActualPaymentDate.Value.Year,d.Project.Name_CH }
return Json(ps);
}
public ActionResult DealGroupBySales(int? year, int? month)
{
if(year==null)year = DateTime.Now.Year;
if(month==null)month = DateTime.Now.Month;
var totaldeals = CRM_Logical.GetDeals(true);
totaldeals = totaldeals.Where(w => w.Income > 0);
var deals = totaldeals.Where(d =>d.ActualPaymentDate.Value.Year == year && d.ActualPaymentDate.Value.Month== month);
var data = from d in deals
group d by new { d.Sales,d.Project.Name_CH}
into grp
select new _DealBySales {
Sales = grp.Key.Sales,
IncomeAmount = grp.Sum(c=>c.Income),
DealAmount = grp.Sum(c => c.Payment),
ProjectName = grp.Key.Name_CH,
TotalIncomeAmount = totaldeals.Where(w => w.Sales == grp.Key.Sales).Sum(s => s.Income)
};
return View(data);
}
private IOrderedEnumerable <AjaxViewDeal> getData(string filter = "", int?projectId = null, string CompanyDealCodeLike = "", int?PaymentID = null, int?ParticipantsID = null, int?year = null, int?month = null)
{
var deals = CRM_Logical.GetDeals(false, projectId, null, filter);
if (year != null)
{
deals = deals.Where(w => w.ActualPaymentDate.Value.Year == year);
}
if (month != null)
{
deals = deals.Where(w => w.ActualPaymentDate.Value.Month == month);
}
if (!string.IsNullOrWhiteSpace(CompanyDealCodeLike))
{
deals = deals.Where(w => w.CompanyRelationship.Company.Name_EN.Contains(CompanyDealCodeLike.Trim()) || w.CompanyRelationship.Company.Name_CH.Contains(CompanyDealCodeLike.Trim()) || w.DealCode.Contains(CompanyDealCodeLike.Trim()));
}
if (PaymentID != null)
{
switch (PaymentID)
{
case 1:
deals = deals.Where(s => s.Payment > 0 && s.Payment <= 3000);
break;
case 2:
deals = deals.Where(s => s.Payment > 3000 && s.Payment <= 5000);
break;
case 3:
deals = deals.Where(s => s.Payment > 5000 && s.Payment <= 8000);
break;
case 4:
deals = deals.Where(s => s.Payment > 8000 && s.Payment <= 10000);
break;
case 5:
deals = deals.Where(s => s.Payment > 10000 && s.Payment <= 15000);
break;
case 6:
deals = deals.Where(s => s.Payment > 15000);
break;
default:
break;
}
}
if (ParticipantsID != null)
{
if (ParticipantsID == 1)
{
deals = deals.Where(s => s.Participants.Any());
}
else
{
deals = deals.Where(s => !s.Participants.Any());
}
}
var emprole = CH.DB.EmployeeRoles.Where(w => 1 == 1);
List <Deal> dllist;
if (Employee.CurrentRole.Level == 4)//财务填写income
{
dllist = deals.ToList();
var ds = from d in dllist
where d.IsConfirm == true
//where d.IsConfirm == true && (d.Income == 0 || d.ActualPaymentDate == null)
select new AjaxViewDeal
{
CompanyNameEN = d.CompanyRelationship.Company.Name_EN,
CompanyNameCH = d.CompanyRelationship.Company.Name_CH,
DealCode = d.DealCode,
Abandoned = d.Abandoned,
AbandonReason = d.AbandonReason,
ActualPaymentDate = d.ActualPaymentDate,
Committer = d.Committer,
CommitterContect = d.Committer,
CommitterEmail = d.CommitterEmail,
ExpectedPaymentDate = d.ExpectedPaymentDate,
ID = d.ID,
Income = d.Income,
IsClosed = d.IsClosed,
PackageNameCH = d.Package.Name_CH,
PackageNameEN = d.Package.Name_EN,
Payment = d.Payment,
Currency = d.Currencytype.Name,
PaymentDetail = d.PaymentDetail,
Sales = d.Sales,
DealType = d.DealType,
ParticipantTypeName = d.Package.ParticipantType.Name_EN,
Poll = d.Poll,
ProjectCode = d.Project.ProjectCode,
SignDate = d.SignDate,
TicketDescription = d.TicketDescription,
IsConfirm = (d.IsConfirm == true ? "是" : "否"),
ModifiedDate = d.ModifiedDate,
Role = emprole.Where(w => w.AccountName == d.Sales).FirstOrDefault() == null ? "" : emprole.Where(w => w.AccountName == d.Sales).FirstOrDefault().Role.Name
};
return(ds.OrderByDescending(o => o.SignDate));
}
else if (Employee.CurrentRole.Level == 3) //会务确定出单
{
var user = Employee.CurrentUserName.Trim();
var pids = new List <int>();
foreach (var c in CH.DB.Projects.Where(w => w.IsActived))
{
if (!string.IsNullOrEmpty(c.Conference))
{
var names = c.Conference.Trim().Split(new string[] { ";", "；" }, StringSplitOptions.RemoveEmptyEntries);
if (names.Contains(user))
{
pids.Add(c.ID);
}
}
}
dllist = deals.Where(w => pids.Contains((int)w.ProjectID)).ToList();
var ds = from d in dllist
select new AjaxViewDeal
{
CompanyNameEN = d.CompanyRelationship.Company.Name_EN,
CompanyNameCH = d.CompanyRelationship.Company.Name_CH,
DealCode = d.DealCode,
Abandoned = d.Abandoned,
AbandonReason = d.AbandonReason,
ActualPaymentDate = d.ActualPaymentDate,
Committer = d.Committer,
CommitterContect = d.Committer,
CommitterEmail = "",
ExpectedPaymentDate = d.ExpectedPaymentDate,
ID = d.ID,
Income = d.Income,
IsClosed = d.IsClosed,
PackageNameCH = d.Package.Name_CH,
PackageNameEN = d.Package.Name_EN,
Payment = d.Payment,
Currency = d.Currencytype.Name,
PaymentDetail = d.PaymentDetail,
Sales = d.Sales,
ProjectCode = d.Project.ProjectCode,
SignDate = d.SignDate,
TicketDescription = d.TicketDescription,
IsConfirm = (d.IsConfirm == true ? "是" : "否"),
ModifiedDate = d.ModifiedDate,
};
return(ds.OrderByDescending(o => o.SignDate));
}
else//板块修改deal
{
List <int> idlist = new List <int>();
foreach (var p in CH.DB.Projects.Where(w => w.IsActived == true && w.Manager != null))
{
var names = p.Manager.Trim().Split(new string[] { ";", "；" }, StringSplitOptions.RemoveEmptyEntries);
if (names.Contains(Employee.CurrentUserName))
{
if (!idlist.Contains(p.ID))
{
idlist.Add(p.ID);
}
}
}
dllist = deals.Where(d => idlist.Contains((int)d.ProjectID)).ToList();
var ds = from d in dllist
select new AjaxViewDeal
{
CompanyNameEN = d.CompanyRelationship.Company.Name_EN,
CompanyNameCH = d.CompanyRelationship.Company.Name_CH,
DealCode = d.DealCode,
Abandoned = d.Abandoned,
AbandonReason = d.AbandonReason,
ActualPaymentDate = d.ActualPaymentDate,
Committer = d.Committer,
CommitterContect = d.Committer,
CommitterEmail = d.CommitterEmail,
ExpectedPaymentDate = d.ExpectedPaymentDate,
ID = d.ID,
Income = d.Income,
IsClosed = d.IsClosed,
PackageNameCH = d.Package.Name_CH,
PackageNameEN = d.Package.Name_EN,
Payment = d.Payment,
Currency = d.Currencytype.Name,
PaymentDetail = d.PaymentDetail,
Sales = d.Sales,
ProjectCode = d.Project.ProjectCode,
SignDate = d.SignDate,
TicketDescription = d.TicketDescription,
IsConfirm = (d.IsConfirm == true ? "是" : "否"),
ModifiedDate = d.ModifiedDate,
};
return(ds.OrderByDescending(o => o.SignDate));
}
}
