public void GetParlayBillReport(ReportQueryObj queryObj)
{
try
{
using (var ctx = new BillDBContext())
{
var predicate = PredicateBuilder.True <ParlayBill>();
Func <ParlayBill, bool> query = predicate.Compile();
var collectionQuery = ctx.ParlayBills.Where(query);
#region Query Obj
var resultQuery = from b in collectionQuery
group b by new { b.Majordomo, b.Status } into g
orderby g.Key.Majordomo, g.Key.Status //descending
select new ReportResultObj
{
Majordomo = g.Key.Majordomo,
IdCount = g.Key.Status != 0 ? 0 : g.Count(),
PublicPoint = Convert.ToDouble(g.Sum(m => m.PublicPoint)),
BetMoney = Convert.ToDouble(g.Sum(m => m.BetMoney)),
BetMoneyGame = Convert.ToDouble(g.Sum(m => m.EffectiveBetOfMember)),
MemberResult = Convert.ToDouble(g.Sum(m => m.MemberResult)),
CompanyResult = Convert.ToDouble(g.Sum(m => m.CompanyResult)),
EffectiveBetOfMajordomo = Convert.ToDouble(g.Sum(m => m.EffectiveBetOfMajordomo)),
EffectiveBetOfBigPartner = Convert.ToDouble(g.Sum(m => m.EffectiveBetOfBigPartner)),
EffectiveBetOfPartner = Convert.ToDouble(g.Sum(m => m.EffectiveBetOfPartner)),
EffectiveBetOfGeneralAgency = Convert.ToDouble(g.Sum(m => m.EffectiveBetOfGeneralAgency)),
EffectiveBetOfAgency = Convert.ToDouble(g.Sum(m => m.EffectiveBetOfAgency)),
EffectiveBetOfMember = Convert.ToDouble(g.Sum(m => m.EffectiveBetOfMember)),
RefundMember = g.Sum(m => Math.Floor(Convert.ToDouble(m.EffectiveBetOfMember * m.RefundAmount / 10000 * 100)) / 100),
RefundMajordomo = g.Sum(m => Math.Floor(Convert.ToDouble(m.EffectiveBetOfMember * m.RefundMajordomo / 10000 * (100 - m.OccupyByMajordomo))) / 100),
RefundBigPartner = g.Sum(m => Math.Floor(Convert.ToDouble(m.EffectiveBetOfMember * m.RefundBigPartner / 10000 * (100 - m.OccupyByBigPartner))) / 100),
RefundPartner = g.Sum(m => Math.Floor(Convert.ToDouble(m.EffectiveBetOfMember * m.RefundPartner / 10000 * (100 - m.OccupyByPartner))) / 100),
RefundGeneralAgency = g.Sum(m => Math.Floor(Convert.ToDouble(m.EffectiveBetOfMember * m.RefundGeneralAgency / 10000 * (100 - m.OccupyByGeneralAgency))) / 100),
RefundAgency = g.Sum(m => Math.Floor(Convert.ToDouble(m.EffectiveBetOfMember * m.RefundAgency / 10000 * (100 - m.OccupyByAgency))) / 100),
Nocc00 = g.Where(m => !(m.GameType == "b_dz" || m.OccupyByMajordomo == 100)).Sum(m => Math.Floor(Convert.ToDouble(m.EffectiveBetOfMajordomo / ((100 - m.OccupyByMajordomo) / 100) * 100)) / 100),
Nocc01 = g.Where(m => !(m.GameType == "b_dz" || m.OccupyByBigPartner == 100)).Sum(m => Math.Floor(Convert.ToDouble(m.EffectiveBetOfBigPartner / ((100 - m.OccupyByBigPartner) / 100) * 100)) / 100),
Nocc02 = g.Where(m => !(m.GameType == "b_dz" || m.OccupyByPartner == 100)).Sum(m => Math.Floor(Convert.ToDouble(m.EffectiveBetOfPartner / ((100 - m.OccupyByPartner) / 100) * 100)) / 100),
Nocc03 = g.Where(m => !(m.GameType == "b_dz" || m.OccupyByGeneralAgency == 100)).Sum(m => Math.Floor(Convert.ToDouble(m.EffectiveBetOfGeneralAgency / ((100 - m.OccupyByGeneralAgency) / 100) * 100)) / 100),
Nocc04 = g.Where(m => !(m.GameType == "b_dz" || m.OccupyByAgency == 100)).Sum(m => Math.Floor(Convert.ToDouble(m.EffectiveBetOfAgency / ((100 - m.OccupyByAgency) / 100) * 100)) / 100),
BetMoneyCount = g.Where(m => m.BetMoney > 0 && m.Status == 0).Select(m => m.Member).Distinct().Count(),
NewDate = Convert.ToDateTime(g.Max(m => m.TransferDate))
};
#endregion
}
}
catch (Exception ex)
{
throw ex;
}
}
public void GetBillReport(ReportQueryObj queryObj)
{
try
{
using (var ctx = new BillDBContext())
{
var predicate = PredicateBuilder.True <Bill>();
switch (queryObj.QueryType)
{
case ReportQueryType.Default:
AddDatetimeCondition(ref predicate, queryObj);
break;
case ReportQueryType.All:
case ReportQueryType.Uncalculate:
case ReportQueryType.Calculated:
break;
}
Func <Bill, bool> query = predicate.Compile();
var collectionQuery = ctx.Bills.Where(query);
#region Query Obj
var resultQuery = from b in collectionQuery
group b by new { b.Majordomo, b.Status } into g
orderby g.Key.Majordomo, g.Key.Status //descending
select new ReportResultObj
{
Majordomo = g.Key.Majordomo,
IdCount = g.Key.Status != 0 ? 0 : g.Count(),
PublicPoint = Convert.ToDouble(g.Sum(m => m.PublicPoint)),
BetMoney = Convert.ToDouble(g.Sum(m => m.BetMoney)),
BetMoneyGame = Convert.ToDouble(g.Where(m => !(m.GameType == "b_vd" || m.GameType == "b_dz" || m.MemberResult == null || m.MemberResult == 0)).Sum(m => m.EffectiveBetOfMember)),
MemberResult = Convert.ToDouble(g.Sum(m => m.MemberResult)),
CompanyResult = Convert.ToDouble(g.Sum(m => m.CompanyResult)),
EffectiveBetOfMajordomo = Convert.ToDouble(g.Sum(m => m.EffectiveBetOfMajordomo)),
EffectiveBetOfBigPartner = Convert.ToDouble(g.Sum(m => m.EffectiveBetOfBigPartner)),
EffectiveBetOfPartner = Convert.ToDouble(g.Sum(m => m.EffectiveBetOfPartner)),
EffectiveBetOfGeneralAgency = Convert.ToDouble(g.Sum(m => m.EffectiveBetOfGeneralAgency)),
EffectiveBetOfAgency = Convert.ToDouble(g.Sum(m => m.EffectiveBetOfAgency)),
EffectiveBetOfMember = Convert.ToDouble(g.Sum(m => m.EffectiveBetOfMember)),
RefundMember = g.Sum(m => Math.Floor(Convert.ToDouble(m.EffectiveBetOfMember * m.RefundAmount / 10000 * 100)) / 100),
RefundMajordomo = g.Sum(m => Math.Floor(Convert.ToDouble(m.EffectiveBetOfMember * m.RefundMajordomo / 10000 * (100 - m.OccupyByMajordomo))) / 100),
RefundBigPartner = g.Sum(m => Math.Floor(Convert.ToDouble(m.EffectiveBetOfMember * m.RefundBigPartner / 10000 * (100 - m.OccupyByBigPartner))) / 100),
RefundPartner = g.Sum(m => Math.Floor(Convert.ToDouble(m.EffectiveBetOfMember * m.RefundPartner / 10000 * (100 - m.OccupyByPartner))) / 100),
RefundGeneralAgency = g.Sum(m => Math.Floor(Convert.ToDouble(m.EffectiveBetOfMember * m.RefundGeneralAgency / 10000 * (100 - m.OccupyByGeneralAgency))) / 100),
RefundAgency = g.Sum(m => Math.Floor(Convert.ToDouble(m.EffectiveBetOfMember * m.RefundAgency / 10000 * (100 - m.OccupyByAgency))) / 100),
Nocc00 = g.Where(m => !(m.GameType == "b_dz" || m.OccupyByMajordomo == 100)).Sum(m => Math.Floor(Convert.ToDouble(m.EffectiveBetOfMajordomo / ((100 - m.OccupyByMajordomo) / 100) * 100)) / 100),
Nocc01 = g.Where(m => !(m.GameType == "b_dz" || m.OccupyByBigPartner == 100)).Sum(m => Math.Floor(Convert.ToDouble(m.EffectiveBetOfBigPartner / ((100 - m.OccupyByBigPartner) / 100) * 100)) / 100),
Nocc02 = g.Where(m => !(m.GameType == "b_dz" || m.OccupyByPartner == 100)).Sum(m => Math.Floor(Convert.ToDouble(m.EffectiveBetOfPartner / ((100 - m.OccupyByPartner) / 100) * 100)) / 100),
Nocc03 = g.Where(m => !(m.GameType == "b_dz" || m.OccupyByGeneralAgency == 100)).Sum(m => Math.Floor(Convert.ToDouble(m.EffectiveBetOfGeneralAgency / ((100 - m.OccupyByGeneralAgency) / 100) * 100)) / 100),
Nocc04 = g.Where(m => !(m.GameType == "b_dz" || m.OccupyByAgency == 100)).Sum(m => Math.Floor(Convert.ToDouble(m.EffectiveBetOfAgency / ((100 - m.OccupyByAgency) / 100) * 100)) / 100),
BetMoneyCount = g.Where(m => m.BetMoney > 0 && m.Status == 0).Select(m => m.Member).Distinct().Count(),
NewDate = Convert.ToDateTime(g.Max(m => m.TransferDate))
};
#endregion
var result = resultQuery.ToList();
foreach (var a in result)
{
Console.WriteLine(a.Majordomo);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
private void AddDatetimeCondition(ref Expression <Func <Bill, bool> > predicate, ReportQueryObj queryObj)
{
if (queryObj.QueryTimeType == QueryTimeType.TransferDate)
{
predicate = predicate.And(p => p.TransferDate >= queryObj.QueryStart && p.TransferDate <= queryObj.QueryEnd);
}
else
{
predicate = predicate.And(p => p.BetTime >= queryObj.QueryStart && p.BetTime <= queryObj.QueryEnd);
}
}