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); }