/// <summary>
/// 订单分析
/// </summary>
/// <param name="statTime"></param>
/// <param name="endTime"></param>
/// <param name="sourceList"></param>
/// <returns></returns>
public List <SourceAnalyzeModel> OrderSourceAnalyze(DateTime startTime, DateTime endTime, int[] sourceList)
{
string sourceStr = "";
if (sourceList.Length > 0)
{
foreach (int id in sourceList)
{
sourceStr += "," + id.ToString();
}
}
if (sourceStr.Length > 0)
{
sourceStr = sourceStr.Trim(',');
}
else
{
sourceStr = "21,22,23,24";
}
StringBuilder strSql = new StringBuilder();
strSql.Append(" create table #table (id int,purchaseTime datetime,sumMoney decimal(18,2),tag_id int) ");
strSql.Append(" insert into #table(id,purchaseTime,sumMoney) ");
strSql.Append(" select accId,CAST(transactionDate as date),RealPayMoney from i200.dbo.T_OrderInfo ");
strSql.Append(" where transactionDate>@statTime and transactionDate<@endTime and orderStatus=2 ");
strSql.Append(" update #table set tag_id=a.tag_id from (select id,acc_id,tag_id from Sys_TagNexus where ");
strSql.Append(" tag_id in(" + sourceStr + ") and acc_id in(select ID from #table)) a where a.acc_id=#table.id; ");
strSql.Append(" update #table set tag_id=24 where tag_id is null; ");
strSql.Append(" select purchaseTime,tag_id,sum(sumMoney) countNum from #table group by purchaseTime,tag_id; ");
strSql.Append(" drop table #table; ");
List <dynamic> dataList = DapperHelper.Query(strSql.ToString(), new { statTime = startTime, endTime = endTime }).ToList();
Dictionary <string, SourceAnalyzeModel> sourceModleList = new Dictionary <string, SourceAnalyzeModel>();
foreach (dynamic item in sourceModleList)
{
string timeString = Convert.ToDateTime(item.purchaseTime).ToString("yyyy-MM-dd");
if (!sourceModleList.ContainsKey(timeString))
{
sourceModleList[timeString] = new SourceAnalyzeModel()
{
DateTime = Convert.ToDateTime(item.purchaseTime)
};
}
int sourceid = Convert.ToInt32(item.tag_id);
SourceAnalyzeItemList sourceItemList = new SourceAnalyzeItemList();
sourceItemList.SourceId = sourceid;
sourceItemList.CountValue = Convert.ToDecimal(item.countNum);
sourceModleList[timeString].ItemList.Add(sourceid.ToString(), sourceItemList);
sourceModleList[timeString].CountValue += sourceItemList.CountValue;
sourceModleList[timeString].count++;
}
List <SourceAnalyzeModel> modelList = new List <SourceAnalyzeModel>();
foreach (KeyValuePair <string, SourceAnalyzeModel> keyValue in sourceModleList)
{
SourceAnalyzeModel sm = keyValue.Value;
foreach (KeyValuePair <string, SourceAnalyzeItemList> il in sm.ItemList)
{
il.Value.ValueScore = (il.Value.CountValue / sm.CountValue);
il.Value.weekend = ((int)sm.DateTime.DayOfWeek).ToString();
}
modelList.Add(sm);
}
return(modelList);
}
/// <summary>
/// 注册来源分析
/// </summary>
/// <param name="statTime"></param>
/// <param name="endTime"></param>
/// <returns></returns>
public List <SourceAnalyzeModel> RegSourceAnalyze(DateTime startTime, DateTime endTime, string[] sourceList, string detailMark = "")
{
StringBuilder strSql = new StringBuilder();
string sourceStr = "";
if (sourceList.Length > 0 && string.IsNullOrEmpty(detailMark))
{
foreach (int id in sourceList.Select(x => int.Parse(x)).ToList())
{
sourceStr += ",'" + id.ToString() + "'";
}
if (sourceStr.Length > 0)
{
sourceStr = sourceStr.Trim(',');
}
else
{
sourceStr = "'0','8','11','13','10'";
}
strSql.Append(" create table #table (id int,regtime datetime,remark nvarchar(10)) ");
strSql.Append(" insert into #table(id,regtime,remark) ");
strSql.Append(" select ID,CAST(RegTime as date),remark from i200.dbo.T_Account where ");
strSql.Append(" RegTime>@statTime and RegTime<@endTime and State=1 and remark in (" + sourceStr + "); ");
strSql.Append(" select regtime,remark,COUNT(id) countNum from #table group by regtime,remark ");
strSql.Append(" drop table #table; ");
}
else if (sourceList.Length > 0 && !string.IsNullOrEmpty(detailMark))//细分来源
{
foreach (string id in sourceList)
{
sourceStr += "," + id.ToString();
}
if (sourceStr.Length > 0)
{
sourceStr = sourceStr.Trim(',');
}
else
{
sourceStr = "'market_360','market_huawei','market_baidu','market_xiaomi'";
}
strSql.Append(" create table #table (id int,regtime datetime,fromName nvarchar(100)) ");
strSql.Append(" insert into #table(id,regtime,fromName) ");
strSql.Append(" select ID,CAST(RegTime as date),fromName from i200.dbo.T_Account where ");
strSql.Append(" RegTime>@statTime and RegTime<@endTime and State=1 and fromName in(" + sourceStr + "); ");
strSql.Append(" select regtime,fromName,COUNT(id) countNum from #table group by regtime,fromName ");
strSql.Append(" drop table #table; ");
}
//List<dynamic> itemList = DapperHelper.Query(strSql.ToString(), new { statTime = startTime, endTime = endTime }).ToList();
//Dictionary<string, SourceAnalyzeModel> sourceModleList = new Dictionary<string, SourceAnalyzeModel>();
//SourceAnalyzeModel赋值
List <dynamic> itemList = DapperHelper.Query(strSql.ToString(), new { statTime = startTime, endTime = endTime }).ToList();
Dictionary <string, SourceAnalyzeModel> sourceModleList = new Dictionary <string, SourceAnalyzeModel>();
foreach (dynamic item in itemList)
{
string timeString = Convert.ToDateTime(item.regtime).ToString("yyyy-MM-dd");
if (!sourceModleList.ContainsKey(timeString))
{
sourceModleList[timeString] = new SourceAnalyzeModel()
{
DateTime = Convert.ToDateTime(item.regtime)
};
}
SourceAnalyzeItemList sourceItemList = new SourceAnalyzeItemList();
if (item.remark != null)
{
int sourceid = Convert.ToInt32(item.remark);
sourceItemList.SourceId = sourceid;
sourceItemList.CountValue = Convert.ToDecimal(item.countNum);
sourceModleList[timeString].ItemList.Add(sourceid.ToString(), sourceItemList);
}
else
{
string detailSource = item.fromName.ToString();
sourceItemList.DetailSource = detailSource;
sourceItemList.CountValue = Convert.ToDecimal(item.countNum);
sourceModleList[timeString].ItemList.Add(detailSource, sourceItemList);
}
sourceModleList[timeString].CountValue += sourceItemList.CountValue;
sourceModleList[timeString].count++;
}
List <SourceAnalyzeModel> modelList = new List <SourceAnalyzeModel>();
DateTime historyTime = startTime;
while (historyTime.Date <= endTime.Date)
{
string timeString = historyTime.ToString("yyyy-MM-dd");
if (sourceModleList.ContainsKey(timeString))
{
SourceAnalyzeModel sm = sourceModleList[timeString];
foreach (KeyValuePair <string, SourceAnalyzeItemList> il in sm.ItemList)
{
il.Value.ValueScore = (il.Value.CountValue / sm.CountValue);
il.Value.weekend = ((int)sm.DateTime.DayOfWeek).ToString();
}
modelList.Add(sm);
}
else
{
SourceAnalyzeModel sm = new SourceAnalyzeModel();
sm.DateTime = historyTime;
sm.CountValue = 0;
sm.count = 0;
sm.ItemList = new Dictionary <string, SourceAnalyzeItemList>();
if (string.IsNullOrEmpty(detailMark))
{
foreach (int s in sourceList.Select(x => int.Parse(x)).ToList())
{
sm.ItemList[s.ToString()] = new SourceAnalyzeItemList()
{
SourceId = s,
CountValue = 0,
ValueScore = 0
};
}
}
else
{
foreach (string s in sourceList)
{
sm.ItemList[s.ToString()] = new SourceAnalyzeItemList()
{
DetailSource = s,
CountValue = 0,
ValueScore = 0
};
}
}
modelList.Add(sm);
}
historyTime = historyTime.AddDays(1);
}
//foreach (dynamic item in itemList)
//{
// string timeString = Convert.ToDateTime(item.regtime).ToString("yyyy-MM-dd");
// if (!sourceModleList.ContainsKey(timeString))
// {
// sourceModleList[timeString] = new SourceAnalyzeModel()
// {
// DateTime = Convert.ToDateTime(item.regtime)
// };
// }
// int sourceid = Convert.ToInt32(item.tag_id);
// SourceAnalyzeItemList sourceItemList = new SourceAnalyzeItemList();
// sourceItemList.SourceId = sourceid;
// sourceItemList.CountValue = Convert.ToDecimal(item.countNum);
// sourceModleList[timeString].ItemList.Add(sourceid.ToString(), sourceItemList);
// sourceModleList[timeString].CountValue += sourceItemList.CountValue;
// sourceModleList[timeString].count++;
//}
//List<SourceAnalyzeModel> modelList = new List<SourceAnalyzeModel>();
//DateTime historyTime = startTime;
//while (historyTime.Date <= endTime.Date)
//{
// string timeString = historyTime.ToString("yyyy-MM-dd");
// if (sourceModleList.ContainsKey(timeString))
// {
// SourceAnalyzeModel sm = sourceModleList[timeString];
// foreach (KeyValuePair<string, SourceAnalyzeItemList> il in sm.ItemList)
// {
// il.Value.ValueScore = (il.Value.CountValue / sm.CountValue);
// il.Value.weekend = ((int)sm.DateTime.DayOfWeek).ToString();
// }
// modelList.Add(sm);
// }
// else
// {
// SourceAnalyzeModel sm = new SourceAnalyzeModel();
// sm.DateTime = historyTime;
// sm.CountValue = 0;
// sm.count = 0;
// sm.ItemList = new Dictionary<string, SourceAnalyzeItemList>();
// foreach (int s in sourceList)
// {
// sm.ItemList[s.ToString()] = new SourceAnalyzeItemList()
// {
// SourceId = s,
// CountValue = 0,
// ValueScore = 0
// };
// }
// modelList.Add(sm);
// }
// historyTime = historyTime.AddDays(1);
//}
//strSql.Clear();
//strSql.Append("select cast(OperDate as date) dayDate,'tPC' terminal,count(distinct Accountid) logCount from i200.dbo.T_LOG where (cast(LogMode as nvarchar(10)) like '3%') and OperDate>@statTime and OperDate<@endTime group by cast(OperDate as date) ");
//strSql.Append(" union all ");
//strSql.Append("select cast(OperDate as date) dayDate,'tWEB' terminal,count(distinct Accountid) logCount from i200.dbo.T_LOG where (LogMode=1 or LogMode=0) and OperDate>@statTime and OperDate<@endTime group by cast(OperDate as date) ");
//strSql.Append(" union all ");
//strSql.Append("select cast(createTime as date) dayDate,'tIOS' terminal,count(distinct AccId) logCount from i200.dbo.T_Token_Api where (AppKey like 'iP%') and createTime>@statTime and createTime<@endTime group by cast(createTime as date) ");
//strSql.Append(" union all ");
//strSql.Append("select cast(createTime as date) dayDate,'tAndroid' terminal,count(distinct AccId) logCount from i200.dbo.T_Token_Api where (AppKey like 'Android%') and createTime>@statTime and createTime<@endTime group by cast(createTime as date);");
//List<LoginTrend> loginTypeList = DapperHelper.Query<LoginTrend>(strSql.ToString(), new { statTime = startTime, endTime = endTime }).ToList();
//List<LoginTrend> tmpList = new List<LoginTrend>();
//SourceAnalyzeItemList tmpItem = new SourceAnalyzeItemList();
////KeyValuePair<string, SourceAnalyzeItemList> tmpDic = new KeyValuePair<string, SourceAnalyzeItemList>();
//foreach (var item in modelList)
//{
// if (loginTypeList.Exists(x => x.dayDate == item.DateTime))
// {
// tmpList = loginTypeList.FindAll(x => x.dayDate == item.DateTime);
// foreach (var listItem in tmpList)
// {
// //tmpDic = item.ItemList.First();
// //tmpItem.weekend = tmpDic.Value.weekend;
// //tmpItem.CountValue=listItem.logCount;
// modelList.Find(x => x.DateTime == item.DateTime).ItemList.Add(listItem.terminal, new SourceAnalyzeItemList(listItem.logCount));
// }
// }
//}
//foreach (KeyValuePair<string, SourceAnalyzeModel> keyValue in sourceModleList)
//{
//}
return(modelList);
}
