public static List <OrderRenewalModel> GetDailyRenewalModel(DateTime monthTime)
{
List <OrderRenewalModel> modelList = new List <OrderRenewalModel>();
DateTime edTime = monthTime.AddMonths(1).AddDays(-monthTime.Day); //获取本月最后一天
if (monthTime.Month == DateTime.Now.Month)
{
if (edTime > DateTime.Now)
{
edTime = DateTime.Now;
}
}
StringBuilder strSql = new StringBuilder();
strSql.Append(
"select o.accid,o.transactionDate,r.itemId,r.itemQuantity into #List " +
"from i200.dbo.T_OrderInfo o " +
"left join i200.dbo.T_Order_List r " +
"on o.oid=r.oid " +
"where r.itemId<>1;");
strSql.Append(
"select accid,transactionDate,itemId,itemQuantity into #accidList from #List where itemId<>11 and DATEDIFF(MONTH,transactionDate,@date)=itemQuantity " +
"union " +
"select accid,transactionDate,itemId,itemQuantity from #List where itemId=11 and DATEDIFF(MONTH,transactionDate,@date)=itemQuantity*12;");
strSql.Append("select accid,DATEADD(MONTH,itemQuantity,transactionDate) endTime from #accidList where itemId<>11 " +
"union " +
"select accid,DATEADD(year,itemQuantity,transactionDate) endTime from #accidList where itemId=11");
List <EndTimeModel> endTime =
DapperHelper.Query <EndTimeModel>(strSql.ToString(), new { date = monthTime }).ToList();
List <EndTimeModel> dayList = new List <EndTimeModel>();
StringBuilder strPartSql = new StringBuilder();
for (DateTime iter = monthTime.AddDays(1 - monthTime.Day); iter < edTime; iter = iter.AddDays(1))
{
dayList = endTime.FindAll(x => x.EndTime.Date == iter.Date);
if (dayList.Count > 0)
{
OrderRenewalModel dayModel = new OrderRenewalModel();
dayModel.Date = iter.ToShortDateString();
dayModel.DailyFlag = iter.ToString("yyyy-MM");
dayModel.ExpireUsr = dayList.Count;
string exAccid = "";
foreach (EndTimeModel model in dayList)
{
//dayModel.ExpireUsr += 1;
dayModel.ExAccids.Add(model.AccId);
exAccid += model.AccId.ToString() + ",";
}
dayModel.ExAccidStr = CommonLib.Helper.JsonSerializeObject(dayModel.ExAccids);
strPartSql.Clear();
strPartSql.Append(
"select o.accid from i200.dbo.T_OrderInfo o left join i200.dbo.T_Order_List r on o.oid=r.oid where r.itemId<>1 and DateDiff(day,o.transactionDate,@date)=0 and o.accid in (" +
exAccid.Trim(',') + "); ");
List <int> reAccid = DapperHelper.Query <int>(strPartSql.ToString(), new { date = iter }).ToList();
if (reAccid.Count > 0)
{
dayModel.RenewalUsr = reAccid.Count;
dayModel.ReAccids = reAccid;
dayModel.ReAccidStr = CommonLib.Helper.JsonSerializeObject(dayModel.ReAccids);
}
if (dayModel.ExpireUsr != 0)
{
dayModel.Ratio = dayModel.RenewalUsr * 100 / dayModel.ExpireUsr;
}
else
{
dayModel.Ratio = 0;
}
dayModel.NotReUsr = dayModel.ExpireUsr - dayModel.RenewalUsr;
dayModel.NotReAccidStr = CommonLib.Helper.JsonSerializeObject(dayModel.ExAccids.Except(dayModel.ReAccids));
modelList.Add(dayModel);
}
}
return(modelList);
}
public static List <OrderRenewalModel> GetSingleOrderRenewalModels(DateTime time)
{
List <OrderRenewalModel> thisMonthList = new List <OrderRenewalModel>();
OrderRenewalModel thisMonth = new OrderRenewalModel();
StringBuilder strSql = new StringBuilder();
strSql.Append(
"select o.accid,o.transactionDate,r.itemId,r.itemQuantity into #List " +
"from i200.dbo.T_OrderInfo o " +
"left join i200.dbo.T_Order_List r " +
"on o.oid=r.oid " +
"where r.itemId<>1;");
strSql.Append(
"select accid into #accidList from #List where itemId<>11 and DATEDIFF(MONTH,transactionDate,@date)=itemQuantity " +
"union " +
"select accid from #List where itemId=11 and DATEDIFF(MONTH,transactionDate,@date)=itemQuantity*12;");
strSql.Append("select * from #accidList;");
strSql.Append("select o.accid from i200.dbo.T_OrderInfo o left join i200.dbo.T_Order_List r on o.oid=r.oid where r.itemId not in (1,21,22,23) and DateDiff(month,o.transactionDate,@date)=0 and o.accid in (select accid from #accidList);");
strSql.Append("drop table #accidList;");
strSql.Append("drop table #List;");
thisMonth.Date = time.ToString("yyyy-MM");
List <IEnumerable <dynamic> > multiList = DapperHelper.QueryMultiple(strSql.ToString(),
new { date = time }).ToList();
thisMonth.ExpireUsr = multiList[0].Count();
if (thisMonth.ExpireUsr != 0)
{
foreach (dynamic i in multiList[0])
{
if (!thisMonth.ExAccids.Contains(i.accid))
{
thisMonth.ExAccids.Add(i.accid);
}
}
}
thisMonth.RenewalUsr = multiList[1].Count();
if (thisMonth.RenewalUsr != 0)
{
foreach (dynamic i in multiList[1])
{
if (!thisMonth.ReAccids.Contains(i.accid))
{
thisMonth.ReAccids.Add(i.accid);
}
}
}
if (thisMonth.ExpireUsr != 0)
{
thisMonth.Ratio = thisMonth.RenewalUsr * 100 / thisMonth.ExpireUsr;
}
thisMonth.ExAccidStr = CommonLib.Helper.JsonSerializeObject(thisMonth.ExAccids);
thisMonth.ReAccidStr = CommonLib.Helper.JsonSerializeObject(thisMonth.ReAccids);
thisMonth.NotReUsr = thisMonth.ExpireUsr - thisMonth.RenewalUsr;
thisMonth.NotReAccidStr = CommonLib.Helper.JsonSerializeObject(thisMonth.ExAccids.Except(thisMonth.ReAccids));
thisMonthList.Add(thisMonth);
List <OrderRenewalModel> dailyList = GetDailyRenewalModel(time);
if (dailyList.Count > 0)
{
foreach (OrderRenewalModel mod in dailyList)
{
thisMonthList.Add(mod);
}
}
return(thisMonthList);
}