/// <summary>
/// 得到数据分布图表信息
/// </summary>
/// <param name="sKeyValue"></param>
/// <param name="sClass"></param>
/// <param name="showDate"></param>
/// <param name="endDate"></param>
/// <returns></returns>
public static Sys_AreaDate4EchartsMapList GetDataDistributedChart(string sKeyValue, string sClass, DateTime showDate, DateTime endDate, string order)
{
Sys_AreaDate4EchartsMapList areaDateMapList = new Sys_AreaDate4EchartsMapList();
areaDateMapList = Sys_SysAreaData4EchartsBLL.GetAreaDataMap(sKeyValue, sClass, showDate, endDate, order);
return(areaDateMapList);
}
/// <summary>
/// 获得区域统计数据
/// </summary>
/// <param name="areaKey">统计关键值</param>
/// <param name="sClass">统计类型</param>
/// <param name="recDate">开始日期</param>
/// <param name="endDate">截止日期</param>
/// <param name="sAreaName">省份名称 none-全部</param>
/// <returns></returns>
public static Sys_AreaDate4EchartsMapList GetAreaDataMap(string areaKey, string sClass, DateTime recDate, DateTime endDate, string order)
{
Sys_SysAreaData4EchartsDAL dal = new Sys_SysAreaData4EchartsDAL();
Sys_AreaDate4EchartsMapList areaDateMapList = new Sys_AreaDate4EchartsMapList();
areaDateMapList = dal.GetAreaDataMap(areaKey, sClass, recDate, endDate, order);
if (areaDateMapList.areaDataList != null && areaDateMapList.areaDataList.Count > 0)
{
}
return(areaDateMapList);
}
/// <summary>
/// 获得区域统计数据
/// </summary>
/// <param name="areaKey">统计关键值</param>
/// <param name="sClass">统计类型</param>
/// <param name="recDate">开始日期</param>
/// <param name="endDate">截止日期</param>
/// <param name="sAreaName">省份名称 none-全部</param>
/// <returns></returns>
public Sys_AreaDate4EchartsMapList GetAreaDataMap(string areaKey, string sClass, DateTime recDate, DateTime endDate, string order)
{
Sys_AreaDate4EchartsMapList AreaDateMapList = new Sys_AreaDate4EchartsMapList();
List <Sys_AreaData4EchartsMap> AreaDataList = new List <Sys_AreaData4EchartsMap>();
if (sClass == "single")
{
//按单个日期统计之前所有数据
string Column = "recDate, areaID, areaName, regCnt, MemberCnt, saleCnt, payCnt, smsCnt, orderSum, UserActiveCnt, UserLoginCnt, UserSleepCnt, UserLostCnt,prov_name";
string orderSql = "";
//判断关键值(排序处理,防止拼接Sql)
string[] NameList = { "regCnt", "MemberCnt", "saleCnt", "payCnt", "smsCnt", "orderSum", "UserActiveCnt", "UserLoginCnt", "UserSleepCnt", "UserLostCnt", "prov_name" };
if (NameList.Contains(areaKey))
{
orderSql = (" order by ") + (areaKey) + (" desc;");
Column += "," + areaKey + " keyAreaValue";
}
//全国省市
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT ");
strSql.Append(Column);
strSql.Append(" FROM Sys_SysAreaData4Echarts ");
strSql.Append(" where datediff(day,recDate,@recDate)=0");
strSql.Append(orderSql);
AreaDataList = DapperHelper.Query <Sys_AreaData4EchartsMap>(strSql.ToString(), new { recDate = recDate }).ToList();
}
else
{
//按日期区间范围统计新增数据
StringBuilder strSql = new StringBuilder();
string Column = "areaID,areaName, sum(regCnt) as regCnt, sum(MemberCnt) as MemberCnt,sum(saleCnt) as saleCnt,sum(payCnt) as payCnt,sum(smsCnt) as smsCnt,sum(orderSum) as orderSum,sum(UserActiveCnt) as UserActiveCnt,sum(UserLoginCnt) as UserLoginCnt,sum(UserSleepCnt) as UserSleepCnt,sum(UserLostCnt) as UserLostCnt,'' prov_name";
string orderSql = "";
//判断关键值(排序处理,防止拼接Sql)
string[] NameList = { "regCnt", "MemberCnt", "saleCnt", "payCnt", "smsCnt", "orderSum", "UserActiveCnt", "UserLoginCnt", "UserSleepCnt", "UserLostCnt", "prov_name" };
if (NameList.Contains(areaKey))
{
orderSql = (" order by ") + (areaKey) + (" desc ");
if (areaKey != "prov_name")
{
Column += ",sum(" + areaKey + ") keyAreaValue";
}
else
{
Column += ",0 keyAreaValue";
}
}
strSql.Append("SELECT ");
strSql.Append(Column);
strSql.Append(" into #temp");
strSql.Append(" FROM Sys_SysAreaData4Echarts ");
strSql.Append(" where recDate>@bgDate and recDate<@edDate group by areaName,areaID ");
strSql.Append(orderSql + ";");
strSql.Append(" alter table #temp alter column prov_name varchar(100); ");
strSql.Append(" update #temp set prov_name=(select top 1 prov_name from Sys_SysAreaData4Echarts where #temp.areaID=Sys_SysAreaData4Echarts.areaID order by id desc); ");
strSql.Append(" select * from #temp; ");
strSql.Append(" drop table #temp; ");
AreaDataList = DapperHelper.Query <Sys_AreaData4EchartsMap>(strSql.ToString(), new { bgDate = recDate, edDate = endDate.Date.Add(new TimeSpan(23, 59, 59)) }).ToList();
}
if (AreaDataList != null && AreaDataList.Count() > 0)
{
decimal maxValue = 0; //最大值
Dictionary <string, int> city = getHash();
Dictionary <int, string> province = getProvince();
Dictionary <string, string> relation = new Dictionary <string, string>();
foreach (var item in city)
{
foreach (var prov in province)
{
if (item.Value == prov.Key)
{
relation.Add(item.Key, prov.Value);
}
}
}
foreach (var item in province)
{
Sys_AreaData4EchartsMap AtraData = new Sys_AreaData4EchartsMap();
AtraData.areaName = item.Value;
AtraData.keyAreaValue = 0;
AtraData.prov_name = "prov";
AreaDataList.Add(AtraData);
}
//bool exist = false;
foreach (var data in AreaDataList)
{
if (relation.ContainsKey(data.areaName))
{
//exist = true;
foreach (var prov in AreaDataList)
{
if (prov.areaName == relation[data.areaName])
{
prov.keyAreaValue += data.keyAreaValue;
prov.MemberCnt += data.MemberCnt;
prov.orderSum += data.orderSum;
prov.payCnt += data.payCnt;
prov.regCnt += data.regCnt;
prov.saleCnt += data.saleCnt;
prov.smsCnt += data.smsCnt;
prov.UserActiveCnt += data.UserActiveCnt;
prov.UserLoginCnt += data.UserLoginCnt;
prov.UserLostCnt += data.UserLostCnt;
prov.UserSleepCnt += data.UserSleepCnt;
}
}
}
}
AreaDateMapList.areaDataList = AreaDataList;
AreaDateMapList.areaCount = AreaDataList.Count;
decimal minValue = AreaDataList[0].keyAreaValue;
foreach (Sys_AreaData4EchartsMap AtraData in AreaDataList)
{
//检测最大值
if (AtraData.keyAreaValue > 0)
{
if (Convert.ToDecimal(AtraData.keyAreaValue) > maxValue)
{
if (AtraData.areaName.IndexOf("其他") < 0)
{
maxValue = Convert.ToDecimal(AtraData.keyAreaValue);
}
}
}
if (AtraData.keyAreaValue > 0)
{
if (Convert.ToDecimal(AtraData.keyAreaValue) < minValue)
{
if (AtraData.areaName.IndexOf("其他") < 0)
{
minValue = Convert.ToDecimal(AtraData.keyAreaValue);
}
}
}
}
AreaDateMapList.areaMaxValue = maxValue;
AreaDateMapList.areaMinValue = minValue;
}
//if (order != "")
//{
// switch (order)
// {
// case "regNum":
// AreaDateMapList.areaDataList.OrderByDescending(x => x.regCnt);
// break;
// case "usrNum":
// AreaDateMapList.areaDataList.OrderByDescending(x => x.MemberCnt);
// break;
// case "saleNum":
// AreaDateMapList.areaDataList.OrderByDescending(x => x.saleCnt);
// break;
// case "orderNum":
// AreaDateMapList.areaDataList.OrderByDescending(x => x.orderSum);
// break;
// case "actNum":
// AreaDateMapList.areaDataList.OrderByDescending(x => x.UserActiveCnt);
// break;
// case "logNum":
// AreaDateMapList.areaDataList.OrderByDescending(x => x.UserLoginCnt);
// break;
// }
//}
return(AreaDateMapList);
}
