public async Task <BDtoCuserList> GetBDtoCuserNameList()
{
string sql = string.Format(@"WITH dtbd as (
select wx_open_id,name,ue.create_time from user_main um
inner join user_type ut on um.id=ut.user_id
inner join user_extra ue on um.id=ue.user_id
where ut.user_type =0 and um.is_available =0)
SELECT dtbd.name AS name from dtbd
inner join user_main um on dtbd.wx_open_id = um.wx_open_id_b
inner join user_type ut on um.id = ut.user_id
where ut.user_type=1
group BY dtbd.wx_open_id,dtbd.name,convert(varchar(10),dtbd.create_time,120)
ORDER BY convert(varchar(10),dtbd.create_time,120) ASC" );
BDtoCuserEntity temp = null;
BDtoCuserList result = new BDtoCuserList
{
list = new List <BDtoCuserEntity>()
};
var list = await DapperHelper.GetListAsync <BDtoCuserEntity>(sql, this._conn);
foreach (var item in list)
{
temp = new BDtoCuserEntity
{
name = item.name
};
result.list.Add(temp);
}
return(result ?? null);
}
/// <summary>
/// 查询地推下渠道发展的c类用户
/// </summary>
/// <param name="startTime">开始时间</param>
/// <param name="endTime">截止时间</param>
/// <returns></returns>
public async Task <BDtoCuserList> GetBDtoCuserList(string startTime, string endTime)
{
string sql = string.Format(@"with dtbd as
(select wx_open_id,ue.name from user_main um
inner join user_type ut on um.id=ut.user_id
INNER JOIN dbo.user_extra ue ON um.id=ue.user_id
WHERE ut.user_type =0 and um.is_available =0),
dtqd as
(select um.wx_open_id,um.wx_open_id_b,dtbd.name from dtbd
INNER join user_main um on dtbd.wx_open_id = um.wx_open_id_b
INNER join user_type ut on um.id = ut.user_id where ut.user_type=1)
SELECT count(dtqd.wx_open_id_b) AS num,dtqd.name AS name,convert(varchar(10),um.create_time,120) as dates from dtqd
INNER join user_main um on dtqd.wx_open_id=um.wx_open_id_b
INNER join user_type ut on um.id = ut.user_id
where ut.user_type =3 AND um.create_time BETWEEN '{0}' AND '{1}'
GROUP by dtqd.name,convert(varchar(10),um.create_time,120)
ORDER BY convert(varchar(10),um.create_time,120) asc", startTime, endTime);
//using (SqlConnection db = new SqlConnection(connStr))
//{
// return db.Query<BDtoCuserEntity>(sql).AsList();
//}
BDtoCuserEntity temp = null;
BDtoCuserList result = new BDtoCuserList
{
list = new List <BDtoCuserEntity>()
};
var list = await DapperHelper.GetListAsync <BDtoCuserEntity>(sql, this._conn);
foreach (var item in list)
{
temp = new BDtoCuserEntity
{
dates = item.dates,//.Insert(4, "-").Insert(7, "-"),
num = item.num,
name = item.name
};
result.list.Add(temp);
}
return(result ?? null);
}
/// <summary>
/// 获取地推下渠道发展C类用户
/// </summary>
/// <param name="timeType"></param>
/// <returns></returns>
public async Task <BDtoCuserResultList> GetBDtoCuserList(string timeType)
{
BDtoCuserList data = null;
//BDtoCuserResult temps = null;
BDtoCuserResultList result = new BDtoCuserResultList
{
list = new List <BDtoCuserResult>()
};
string startTime = string.Empty;
string endTime = string.Empty;
string[] times = null;
string[] nums = null;
string names = null;
int counts = 0;
#region new
switch (timeType)
{
/**
* 处理开始时间和和结束时间。
* addDays(+1)为between不包含结束当天数据,
* */
case "1":
{
startTime = DateTime.Now.AddDays(-6).ToString("yyyy-MM-dd");
endTime = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd");
times = new string[7];
nums = new string[7];
counts = 7;
//names = new string[7];
for (int i = 0; i < 7; i++)
{
times[i] = DateTime.Now.AddDays(+(i - 6)).ToString("yyyy-MM-dd");
nums[i] = "0";
//names[i] = "";
}
}
break;
case "2":
{
startTime = DateTime.Now.AddDays(-29).ToString("yyyy-MM-dd");
endTime = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd");
times = new string[30];
nums = new string[30];
counts = 30;
//names = new string[30];
for (int i = 0; i < 30; i++)
{
times[i] = DateTime.Now.AddDays(+(i - 29)).ToString("yyyy-MM-dd");
nums[i] = "0";
//names[i] = "";
}
}
break;
case "3":
{
startTime = DateTime.Now.AddDays(-89).ToString("yyyy-MM-dd");
endTime = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd");
int days = DateTimeExtensions.DateDiff() + 1;
times = new string[90];
nums = new string[90];
counts = 90;
//names = new string[90];
for (int i = 0; i < 90; i++)
{
times[i] = DateTime.Now.AddDays(+(i - 89)).ToString("yyyy-MM-dd");
nums[i] = "0";
//names[i] = "";
}
}
break;
default:
break;
}
result.date = times;
//时间
for (int i = 0; i < times.Length; i++)
{
try
{
result.date[i] = Convert.ToDateTime(times[i]).ToString("MM-dd");
}
catch { }
}
data = await BDtoCuserDal.Instance.GetBDtoCuserList(startTime, endTime);
BDtoCuserList namelist1 = await BDtoCuserDal.Instance.GetBDtoCuserNameList();
#region 将BD名称写入
var namelist = namelist1.list.Select(c => c.name).Distinct().ToList();
foreach (var item in namelist)
{
nums = new string[counts];
times = new string[counts];
for (int i = 0; i < counts; i++)
{
nums[i] = "0";
times[i] = DateTime.Now.AddDays(+(i - (counts - 1))).ToString("yyyy-MM-dd");
}
if (item == null || item == "")
{
names = "匿名";
}
else
{
names = item.ToString();
}
//根据名字写入数量
List <BDtoCuserEntity> numlist = data.list.Where(c => c.name == item).ToList();
foreach (var items in numlist)
{
if (((IList)times).Contains(items.dates)) //判断那天有数据。
{
//data中数据插入到数组中。
nums[times.ToList().IndexOf(items.dates)] = items.num;
}
}
var temps = new BDtoCuserResult
{
name = names,
data = nums
};
result.list.Add(temps);
}
#endregion
#endregion
return(result);
}
