protected Model.OrdersCondition condition()
{
Model.OrdersCondition con = new Model.OrdersCondition
{
KeyWords = this.txtKeyWords.Text.Trim()
};
con.dateTime = ddlDateTime.SelectedValue;
if (!string.IsNullOrEmpty(txtDateStart.Text))
{
con.startTime = DateTime.Parse(txtDateStart.Text);
}
if (!string.IsNullOrEmpty(txtDateEnd.Text))
{
con.endTime = DateTime.Parse(txtDateEnd.Text);
}
if (!string.IsNullOrEmpty(ddlAppKeyID.SelectedValue))
{
con.AdId = ddlAppKeyID.SelectedValue;
}
if (!string.IsNullOrEmpty(ddlAccount.SelectedValue))
{
con.setName = ddlAccount.SelectedValue;
}
return(con);
}
protected void DataInfoBind()
{
ShowPager.PageSize = PageSize;
int total = 0;
Model.OrdersCondition con = condition();
var list = bllOrders.GetList(ShowPager.CurrentPageIndex, ShowPager.PageSize, ref total, con, p => p.ID, false);
ShowPager.RecordCount = total;
this.rpData.DataSource = list;
this.rpData.DataBind();
listAll = bllOrders.GetList(1, int.MaxValue, ref total, con, p => p.ID, false);
totalyj = listAll.Sum(m => m.Effect.HasValue ? m.Effect.Value : 0.00m);
totalyjds = listAll.Sum(m => m.Effect.HasValue ? Math.Round(m.Effect.Value * 0.84m, 2) : 0.00m);
totallijin = listAll.Sum(m => m.lijin.HasValue ? m.lijin.Value : 0.00m);
totalrealshouru = listAll.Sum(m => m.realshouru.HasValue ? m.realshouru.Value : 0.00m);
totalmd_amount = listAll.Where(p => p.realshouru < 0).Sum(m => m.realshouru.HasValue ? m.realshouru.Value : 0.00m);
totalmd_count = listAll.Where(p => p.realshouru < 0).Count();
}
protected Model.OrdersCondition condition()
{
Model.OrdersCondition con = new Model.OrdersCondition();
con.isstat = true;
if (!string.IsNullOrEmpty(txtDateStart.Text))
{
con.statStartTime = DateTime.Parse(txtDateStart.Text);
}
if (!string.IsNullOrEmpty(txtDateEnd.Text))
{
con.statEndTime = DateTime.Parse(txtDateEnd.Text).AddDays(1);
}
if (!string.IsNullOrEmpty(ddlAppKeyID.SelectedValue))
{
con.AdId = ddlAppKeyID.SelectedValue;
}
if (!string.IsNullOrEmpty(ddlAccount.SelectedValue))
{
con.setName = ddlAccount.SelectedValue;
}
return(con);
}
protected void DataInfoBind()
{
ShowPager.PageSize = PageSize;
int total = 0;
Model.OrdersCondition con = condition();
var iEnumerale = bllOrders.GetList(1, int.MaxValue, ref total, con, p => p.ID, false)
.GroupBy(m => new
{
m.ProductID,
m.ProductName,
m.OwnedShop,
m.pic
}).Select(m => new
{
ProductID = m.Key.ProductID,
ProductName = m.Key.ProductName,
OwnedShop = m.Key.OwnedShop,
pic = m.Key.pic,
totalLijin = m.Sum(p => p.lijin.HasValue ? p.lijin.Value : 0.00m),
totalRealshouru = m.Sum(p => p.realshouru.HasValue ? p.realshouru.Value : 0.00m),
totalEffect = m.Sum(p => p.Effect.HasValue ? p.Effect.Value : 0.00m),
totalValidEffect = m.Where(p => p.OrderStatus == "订单付款" || p.OrderStatus == "订单结算").Sum(p => p.Effect.HasValue ? p.Effect.Value : 0.00m),
totalOrderNums = m.Count(),
totalValidOrderNums = m.Where(p => p.OrderStatus == "订单付款" || p.OrderStatus == "订单结算").Count(),
totalReturnOrderNums = m.Where(p => p.OrderStatus == "订单失效").Count(),
adverLijin = m.Where(p => p.OrderStatus == "订单付款" || p.OrderStatus == "订单结算").Count() > 0 ?
Convert.ToDecimal(Math.Round(m.Sum(p => p.lijin.HasValue ? p.lijin.Value : 0.00m) / (m.Where(p => p.OrderStatus == "订单付款" || p.OrderStatus == "订单结算").Count()), 2, MidpointRounding.AwayFromZero)) : 0.00m
}).OrderByDescending(m => m.totalEffect);
var list = iEnumerale.Skip((ShowPager.CurrentPageIndex - 1) * ShowPager.PageSize).Take(ShowPager.PageSize).ToList();
total = iEnumerale.Count();
ShowPager.RecordCount = total;
this.rpData.DataSource = list;
this.rpData.DataBind();
}
protected void getData()
{
listRes.Clear();
DateTime dtNow = new DateTime();
if (!string.IsNullOrEmpty(txtDateStart.Text) && !string.IsNullOrEmpty(txtDateEnd.Text))
{
DateTime dtStart = DateTime.Parse(txtDateStart.Text);
DateTime dtEnd = DateTime.Parse(txtDateEnd.Text);
for (DateTime dt = dtStart; dt <= dtEnd; dt = dt.AddDays(1))
{
listRes.Add(new TljStatXls {
dotime = dt.ToString("yyyy-MM-dd"),
yj = 0.00m,
yjds = 0.00m,
use_amount = 0.00m,
ykresult = 0.00m,
md_amount = 0.00m,
md_count = 0
});
}
int total = 0;
Model.OrdersCondition con = condition();
var list = bllOrder.GetList(1, int.MaxValue, ref total, con, m => m.CreateTime)
.GroupBy(m => new
{
dotime = m.CreateTime.Value.ToString("yyyy-MM-dd")
})
.Select(m => new
{
yj = m.Sum(p => p.Effect.HasValue ? p.Effect.Value : 0.00m),
yjds = m.Sum(p => p.Effect.HasValue ? Math.Round(p.Effect.Value * 0.84m, 2) : 0.00m),
use_amount = m.Sum(p => p.lijin.HasValue ? p.lijin.Value : 0.00m),
ykresult = m.Sum(p => p.realshouru.HasValue ? p.realshouru.Value : 0.00m),
md_amount = m.Where(p => p.realshouru < 0).Sum(p => p.realshouru.HasValue ? p.realshouru.Value : 0.00m),
md_count = m.Where(p => p.realshouru < 0).Count(),
dotime = m.Key.dotime
});
listRes.ForEach(m =>
{
foreach (var item in list)
{
if (item.dotime == m.dotime)
{
m.yj = item.yj;
m.yjds = item.yjds;
m.use_amount = item.use_amount;
m.ykresult = item.ykresult;
m.md_amount = item.md_amount;
m.md_count = item.md_count;
}
}
});
xAxis_arr = Newtonsoft.Json.JsonConvert.SerializeObject(listRes.Select(m => m.dotime).ToArray());
yj_arr = Newtonsoft.Json.JsonConvert.SerializeObject(listRes.Select(m => m.yj).ToArray());
yjds_arr = Newtonsoft.Json.JsonConvert.SerializeObject(listRes.Select(m => m.yjds).ToArray());
use_amount_arr = Newtonsoft.Json.JsonConvert.SerializeObject(listRes.Select(m => m.use_amount).ToArray());
md_amount_arr = Newtonsoft.Json.JsonConvert.SerializeObject(listRes.Select(m => m.md_amount).ToArray());
ykresult_arr = Newtonsoft.Json.JsonConvert.SerializeObject(listRes.Select(m => m.ykresult).ToArray());
totalyj = list.Sum(m => m.yj);
totalyjds = list.Sum(m => m.yjds);
totaluse_amount = list.Sum(m => m.use_amount);
totalykresult = list.Sum(m => m.ykresult);
totalmd_amount = list.Sum(m => m.md_amount);
totalmd_count = list.Sum(m => m.md_count);
}
}
protected void btnExcel_Click(object sender, EventArgs e)
{
int total = 0;
Model.OrdersCondition con = condition();
var listAll = bllOrders.GetList(1, int.MaxValue, ref total, con, p => p.ID, false)
.GroupBy(m => new
{
m.ProductID,
m.ProductName,
m.OwnedShop,
m.pic
}).Select(m => new
{
ProductID = m.Key.ProductID,
ProductName = m.Key.ProductName,
OwnedShop = m.Key.OwnedShop,
pic = m.Key.pic,
totalLijin = m.Sum(p => p.lijin.HasValue ? p.lijin.Value : 0.00m),
totalRealshouru = m.Sum(p => p.realshouru.HasValue ? p.realshouru.Value : 0.00m),
totalEffect = m.Sum(p => p.Effect.HasValue ? p.Effect.Value : 0.00m),
totalValidEffect = m.Where(p => p.OrderStatus == "订单付款" || p.OrderStatus == "订单结算").Sum(p => p.Effect.HasValue ? p.Effect.Value : 0.00m),
totalOrderNums = m.Count(),
totalValidOrderNums = m.Where(p => p.OrderStatus == "订单付款" || p.OrderStatus == "订单结算").Count(),
totalReturnOrderNums = m.Where(p => p.OrderStatus == "订单失效").Count(),
adverLijin = m.Where(p => p.OrderStatus == "订单付款" || p.OrderStatus == "订单结算").Count() > 0 ?
Convert.ToDecimal(Math.Round(m.Sum(p => p.lijin.HasValue ? p.lijin.Value : 0.00m) / (m.Where(p => p.OrderStatus == "订单付款" || p.OrderStatus == "订单结算").Count()), 2, MidpointRounding.AwayFromZero)) : 0.00m
}).OrderByDescending(m => m.totalEffect);
var excelList = new List <Model.ProductStatXls>();
foreach (var i in listAll)
{
excelList.Add(new Model.ProductStatXls
{
ProductName = i.ProductName,
ProductID = i.ProductID,
OwnedShop = i.OwnedShop,
totalEffect = i.totalEffect,
totalValidEffect = i.totalValidEffect,
totalLijin = i.totalLijin,
adverLijin = i.adverLijin,
totalRealshouru = i.totalRealshouru,
totalOrderNums = i.totalOrderNums,
totalValidOrderNums = i.totalValidOrderNums,
totalReturnOrderNums = i.totalReturnOrderNums
});
}
string fileName = string.Format("产品排行_{0}.xls", DateTime.Now.ToString("yyyy-MM-dd"));
ExcelHelp <Model.ProductStatXls> excelH = new ExcelHelp <Model.ProductStatXls>();
Hashtable ht = new Hashtable();
ht.Add("ProductName", "商品名称");
ht.Add("ProductID", "商品ID");
ht.Add("OwnedShop", "店铺名");
ht.Add("totalEffect", "全部佣金");
ht.Add("totalValidEffect", "有效佣金");
ht.Add("totalLijin", "合计礼金");
ht.Add("adverLijin", "平均礼金");
ht.Add("totalRealshouru", "实际收益");
ht.Add("totalOrderNums", "全部订单");
ht.Add("totalValidOrderNums", "有效订单");
ht.Add("totalReturnOrderNums", "退款订单");
excelH.getExcel(excelList, ht, fileName);
}
