public ResultInfo SettleCalVerion2(int y, int m)
{
db0 = getDB0;
var r = new ResultInfo();
try
{
#region MyRegion
//上個月日期
var p_date = DateTime.Parse(y + "/" + m + "/1").AddMonths(-1);
//上上個月日期
var pp_date = DateTime.Parse(y + "/" + m + "/1").AddMonths(-2);
//清空 SettleCal 資料
string sql = "delete from SettleCal where sales_no!='A'";
db0.Database.ExecuteSqlCommandAsync(sql).Wait();
//清楚計算月的資料
sql = "delete from SettleDetail where y=@y and m=@m";
SqlParameter[] sps = new SqlParameter[] { new SqlParameter("@y", y), new SqlParameter("@m", m) };
db0.Database.ExecuteSqlCommandAsync(sql, sps).Wait();
var sales_colle = db0.Sales.Select(x => new SalesCalInfo()
{
sales_no = x.sales_no,
sales_name = x.sales_name,
rank = x.rank,
recommend_no = x.recommend_no,
recommend_name = x.recommend_name,
share_sn = x.share_sn,
share_level = x.share_level,
share_name = x.ShareParent.sales_name
}).Where(x => x.sales_no != "A").ToList(); //取得會員
var sales_sum_kv = db0.PurchaseDetail
.Where(x => x.Purchase.set_date.Year == y && x.Purchase.set_date.Month == m & (x.Purchase.state == (int)PurchaseState.complete || x.Purchase.state == (int)PurchaseState.onSite))
.GroupBy(x => new { x.Purchase.sales_no })
.Select(g => new { sales_no = g.Key.sales_no, sum_kv = g.Sum(x => x.kv_sub_total) }).ToList();
foreach (var sales in sales_colle)
{
var md = new SettleCal();
var obj = sales_sum_kv.FirstOrDefault(x => x.sales_no == sales.sales_no);
if (obj != null)
{
md.sales_no = sales.sales_no;
md.sales_name = sales.sales_name;
md.rank = sales.rank;
md.share_level = sales.share_level;
md.share_sn = sales.share_sn;
md.share_name = sales.share_name;
md.recommend_no = sales.recommend_no;
md.recommend_name = sales.recommend_name;
md.KV = obj.sum_kv;
}
else
{
md.sales_no = sales.sales_no;
md.sales_name = sales.sales_name;
md.rank = sales.rank;
md.share_level = sales.share_level;
md.share_sn = sales.share_sn;
md.share_name = sales.share_name;
md.recommend_no = sales.recommend_no;
md.recommend_name = sales.recommend_name;
md.KV = 0;
}
db0.SettleCal.Add(md);
}
#region 取出該月獎金計算主檔
var settle = db0.Settle.Where(x => x.y == y & x.m == m).FirstOrDefault();
if (settle == null)
{
settle = new Settle()
{
settle_id = GetNewId(CodeTable.Settle),
y = y,
m = m,
state = (int)SettleState.progress,
set_date = DateTime.Now
};
db0.Settle.Add(settle);
}
#endregion
//以上只算出每位會員KV值
db0.SaveChangesAsync().Wait();
var sales_kv_data = db0.SettleCal.Where(x => x.KV >= 1000).ToList();
//取得上個月結算全部資料
var sales_settle_pre = db0.SettleDetail.Where(x => x.y == p_date.Year && x.m == p_date.Month).ToList();
//取得上上個月結算全部資料
var sales_settle_prepre = db0.SettleDetail.Where(x => x.y == pp_date.Year && x.m == pp_date.Month).ToList();
#endregion
#region 消費滿1000kv
foreach (var sales in sales_kv_data)
{
#region MyRegion
var p = sales_settle_pre.FirstOrDefault(x => x.sales_no == sales.sales_no);
var md = new SettleDetail();
#region 計算共享圈消費總kv
//加總子系KV值 且往下只探四層,取當月總消費(未滿1000kv也列入)
var cal_kv_sum = sales.SettleCalSub
.Traverse(x => x.SettleCalSub)
.Where(x => (x.share_level <= sales.share_level + 4))
.Sum(x => x.KV);
#endregion
#region 計算直推會員總kv
var r_kv_sum = 0;
//加總子系KV值 ,取當月總消費(未滿1000kv也列入)
if (sales.rank >= (int)SalesRankState.manager)
{//經理人以上才需要計算
r_kv_sum = sales.SettleCalRSub.Sum(x => x.KV);
}
#endregion
#region 計算直推經理人是否達30在線&下線總消費額
int count_manager = 0;
int l_kv_sum = 0;
//找出消費滿1000kv且等級為經理人以上且下線有達三位消費滿1000kv
if (sales.rank >= (int)SalesRankState.operationsCenter)
{//營運中心以上才需要計算
foreach (var sub in sales.SettleCalRSub.Where(x => x.KV >= 1000 & x.rank >= (int)SalesRankState.manager))
{
if (sub.SettleCalRSub.Where(x => x.KV >= 1000).Count() >= 3)
count_manager++;
}
if (count_manager >= 30)
{
l_kv_sum = sales.SettleCalRSub.Traverse(x => x.SettleCalRSub).Sum(x => x.KV);
}
}
#endregion
#region 計算直推營運中心營業總kv
var rc_kv_sum = 0;
if (sales.rank == (int)SalesRankState.managementOffice)
{//管理處以上才需要計算
rc_kv_sum = sales.SettleCalRSub.Where(x => x.rank >= (int)SalesRankState.operationsCenter)
.Traverse(x => x.SettleCalRSub).Sum(x => x.KV);
}
#endregion
md.settle_id = settle.settle_id;
md.y = y;
md.m = m;
md.sales_no = sales.sales_no;
md.sales_name = sales.sales_name;
md.rank = sales.rank;
md.kv_p_sum = sales.KV;//個人_kv總計
md.kv_g_sum = cal_kv_sum;//共享圈_kv總計
md.kv_r_sum = r_kv_sum;//直推會員_kv總計
md.kv_l_sum = l_kv_sum;//直推+間接會員_kv總計
md.kv_rc_sum = rc_kv_sum;//直推營運中心營業額_kv總計
md.count_manager = count_manager;//直推經理人在線人數
var x1 = Convert.ToInt32(cal_kv_sum * 0.01);
var x2 = Convert.ToInt32(sales.KV * 0.75);
md.a = x1 > x2 ? x2 : x1;
#region 上月累計獎金
if (p != null)
{
md.a_p = p.a;
md.b_p = p.b;
}
else
{
md.a_p = 0;
md.b_p = 0;
}
#endregion
//計算本月累計回饋金
md.b = x2 + md.b_p - md.a;
if (sales.rank >= (int)SalesRankState.manager) //經理人獎金
{
md.bound = Convert.ToInt32(md.kv_r_sum * 0.12);
}
if (sales.rank == (int)SalesRankState.operationsCenter & count_manager >= 30) //營運中心
{//30位經理人在線
md.center_bonus = Convert.ToInt32(md.kv_l_sum * 0.02);
}
if (sales.rank == (int)SalesRankState.managementOffice & count_manager >= 30)//管理處
{//30位經理人在線
md.center_bonus = Convert.ToInt32((md.kv_l_sum - md.kv_rc_sum) * 0.02);//管理處的營運中心紅利算法(與rank=營運中心)不太一樣
md.office_bonus = Convert.ToInt32(md.kv_rc_sum * 0.01);
}
db0.SettleDetail.Add(md);
#endregion
}
#endregion
#region 消費未滿1000kv(計算累計回饋)
var accumulate_kv_data = db0.SettleCal.Where(x => x.KV < 1000 & x.sales_no != "A").ToList();
foreach (var sales in accumulate_kv_data)
{
#region MyRegion
var p = sales_settle_pre.FirstOrDefault(x => x.sales_no == sales.sales_no);
var pp = sales_settle_pre.FirstOrDefault(x => x.sales_no == sales.sales_no);
var md = new SettleDetail();
md.settle_id = settle.settle_id;
md.y = y;
md.m = m;
md.sales_no = sales.sales_no;
md.sales_name = sales.sales_name;
md.rank = sales.rank;
md.kv_p_sum = sales.KV;//個人_kv總計
md.kv_g_sum = 0;//共享圈_kv總計
md.kv_r_sum = 0;//直推會員_kv總計
md.kv_l_sum = 0;//直推+間接會員_kv總計
md.kv_rc_sum = 0;//直推營運中心營業額_kv總計
md.count_manager = 0;//直推經理人在線人數
md.bound = 0;//經理人獎金
md.center_bonus = 0;//營運中心獎金
md.office_bonus = 0;//管理處獎金
//var x1 = Convert.ToInt32(cal_kv_sum * 0.01);
var x2 = Convert.ToInt32(sales.KV * 0.75);
md.a = 0;//未滿1000kv回饋金為0
#region 上月累計獎金
int pp_kv_p_sum = 0;//上上個月個人_kv總計
int p_kv_p_sum = 0;//上個月個人_kv總計
if (p != null)
{
md.a_p = p.a;
md.b_p = p.b;
p_kv_p_sum = p.kv_p_sum;
}
else
{
md.a_p = 0;
md.b_p = 0;
}
pp_kv_p_sum = pp != null ? pp.kv_p_sum : 0;
#endregion
//計算本月累計回饋金
md.b = x2 + md.b_p - md.a;
//連續三個月消費未滿1000kv累計回饋金歸0
if (pp_kv_p_sum < 1000 & p_kv_p_sum < 1000)
md.b = 0;
db0.SettleDetail.Add(md);
#endregion
}
#endregion
db0.SaveChangesAsync().Wait();
r.result = true;
return r;
}
catch (Exception ex)
{
r.result = false;
r.message = ex.Message;
return r;
}
finally
{
db0.Dispose();
}
}
public ResultInfo SettleCal(int y, int m)
{
db0 = getDB0;
var r = new ResultInfo();
try
{
#region MyRegion
//上個月日期
var p_date = DateTime.Parse(y + "/" + m + "/1").AddMonths(-1);
//清空 SettleCal 資料
string sql = "delete from SettleCal where sales_no!='A'";
db0.Database.ExecuteSqlCommandAsync(sql).Wait();
//清楚計算月的資料
sql = "delete from SettleDetail where y=@y and m=@m";
SqlParameter[] sps = new SqlParameter[] { new SqlParameter("@y", y), new SqlParameter("@m", m) };
db0.Database.ExecuteSqlCommandAsync(sql, sps).Wait();
var sales_colle = db0.Sales.Select(x => new SalesCalInfo()
{
sales_no = x.sales_no,
sales_name = x.sales_name,
rank = x.rank,
share_sn = x.share_sn,
share_level = x.share_level,
share_name = x.ShareParent.sales_name
}).Where(x => x.sales_no != "A").ToList(); //取得會員
var sales_sum_kv = db0.PurchaseDetail
.Where(x => x.Purchase.set_date.Year == y && x.Purchase.set_date.Month == m)
.GroupBy(x => new { x.Purchase.sales_no })
.Select(g => new { sales_no = g.Key.sales_no, sum_kv = g.Sum(x => x.kv_sub_total) }).ToList();
foreach (var sales in sales_colle)
{
var md = new SettleCal();
var obj = sales_sum_kv.FirstOrDefault(x => x.sales_no == sales.sales_no);
if (obj != null)
{
md.sales_no = sales.sales_no;
md.sales_name = sales.sales_name;
md.rank = sales.rank;
md.share_level = sales.share_level;
md.share_sn = sales.share_sn;
md.share_name = sales.share_name;
md.KV = obj.sum_kv;
}
else
{
md.sales_no = sales.sales_no;
md.sales_name = sales.sales_name;
md.rank = sales.rank;
md.share_level = sales.share_level;
md.share_sn = sales.share_sn;
md.share_name = sales.share_name;
md.KV = 0;
}
db0.SettleCal.Add(md);
}
//以上只算出每位會員KV值
db0.SaveChangesAsync().Wait();
var sales_kv_data = db0.SettleCal.Where(x => x.KV >= 1000).ToList();
//取得上個月結算全部資料
var sales_settle_pre = db0.SettleDetail.Where(x => x.y == p_date.Year && x.m == p_date.Month).ToList();
#endregion
foreach (var sales in sales_kv_data)
{
#region MyRegion
var p = sales_settle_pre.FirstOrDefault(x => x.sales_no == sales.sales_no);
var md = new SettleDetail();
//加總子系KV值 且往下只探四層
var cal_kv_sum = sales.SettleCalSub
.Traverse(x => x.SettleCalSub)
.Where(x => x.share_level < sales.share_level + 4)
.Sum(x => x.KV);
md.settle_id = 1;
md.y = y;
md.m = m;
md.sales_no = sales.sales_no;
md.sales_name = sales.sales_name;
md.rank = sales.rank;
md.kv_p_sum = sales.KV;
md.kv_g_sum = cal_kv_sum;
var x1 = Convert.ToInt32(cal_kv_sum * 0.01);
var x2 = Convert.ToInt32(sales.KV * 0.75);
md.a = x1 > x2 ? x2 : x1;
if (p != null)
{
md.a_p = p.a;
md.b_p = p.b;
}
else
{
md.a_p = 0;
md.b_p = 0;
}
//計算本月累計回饋金
md.b = x2 + md.b_p - md.a_p;
//全部總KV
var total_kv = sales_kv_data.Sum(x => x.KV);
if (sales.rank == 1 || sales.rank == 2) //經理人獎金
{
var f = sales.SettleCalSub.Sum(x => x.KV) * 0.12;
}
if (sales.rank == 2) //營運中心
{
}
db0.SettleDetail.Add(md);
#endregion
}
db0.SaveChangesAsync().Wait();
r.result = true;
return r;
}
catch (Exception ex)
{
r.result = false;
r.message = ex.Message;
return r;
}
finally
{
db0.Dispose();
}
}