/// <summary>
/// 查询实名认证信息
/// </summary>
/// <returns></returns>
public RealNameAuthenticationModel FindRealName(int user_id)
{
using (var db = new BusinessBase().OpenConnection())
{
string viewSQl = (@"select real_name,card_no,card_type from user_realInfo where user_id=@user_id");
DynamicParameters param = new DynamicParameters();
param.Add("user_id", user_id);
var result = db.Query <RealNameAuthenticationModel>(viewSQl, param);
return(result.FirstOrDefault());
}
}
/// <summary>
/// 获取用户信息
/// </summary>
/// <param name="tel">手机号</param>
/// <returns></returns>
public LoginInfoModel GetUserInfo(string tel)
{
using (var db = new BusinessBase().OpenConnection())
{
string sql = @"select um.id as userid,um.wx_open_id as wxopenid from user_main as um where um.user_name=@tel";
DynamicParameters param = new DynamicParameters();
param.Add("tel", tel);
var result = db.Query <LoginInfoModel>(sql, param) as List <LoginInfoModel>;
return(result.FirstOrDefault());
}
}
/// <summary>
/// 获取用户权限
/// </summary>
/// <param name="userid">用户id</param>
/// <returns></returns>
public string GetUserRole(string userid)
{
using (var db = new BusinessBase().OpenConnection())
{
string sql = @"select top 1 F_RoleId as Roleid from Sys_User as sysuser
left join Sys_Role as sysrole on sysuser.F_id=sysrole.F_id
where sysuser.F_Id=@userid and (sysuser.F_RoleId='13b8fe89-c60c-48c0-a01d-2c4b6149fa27' or sysuser.F_RoleId='13b8fe89-c60c-48c0-a01d-2c4b6Leaders')";
DynamicParameters param = new DynamicParameters();
param.Add("userid", userid);
var result = db.Query <string>(sql, param).FirstOrDefault();
return(result);
}
}
/// <summary>
/// 总渠道
/// </summary>
/// <returns></returns>
public List <TotalChannel> GetTotalChannel()
{
using (var db = new BusinessBase().OpenConnection())
{
string sql = @" select CONVERT(varchar(10),um.create_time, 25)as createtime from WeChatServiceFlatform.dbo.user_main um
inner join WeChatServiceFlatform.dbo.user_type ut on um.id = ut.user_id
inner join WeChatServiceFlatform.dbo.user_main ums on um.wx_open_id_b=ums.wx_open_id
inner join WeChatServiceFlatform.dbo.user_type uts on ums.id =uts.user_id
INNER join WeChatServiceFlatform.dbo.Sys_User sus on sus.F_Id = CONVERT(varchar(50),ums.id)
where ut.user_type=1 and uts.user_type=0";
var result = db.Query <TotalChannel>(sql) as List <TotalChannel>;
return(result.ToList());
}
}
/// <summary>
/// 单日授权用户
/// </summary>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <returns></returns>
public List <DayUserNum> GetUserNums(string startTime, string endTime)
{
using (var db = new BusinessBase().OpenConnection())
{
string sql = string.Format(@"select dayData.createtime,COUNT(*) as usernum from (
select CONVERT(varchar(10),um.create_time, 25)as createtime
from [dbo].[user_main] as um left join user_type as ut on um.id=ut.user_id
where ut.user_type=3 and um.create_time BETWEEN'{0} 00:00:00' AND '{1} 23:59:59'
)as dayData
group by dayData.createtime order by dayData.createtime" , startTime, endTime);
var result = db.Query <DayUserNum>(sql) as List <DayUserNum>;
return(result.ToList());
}
}
/// <summary>
/// 单日订单数
/// </summary>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <returns></returns>
public List <DayOrderNum> GetDayOrderNums(string startTime, string endTime)
{
using (var db = new BusinessBase().OpenConnection())
{
string sql = string.Format(@"select dayData.createtime,COUNT(*) as ordernum from (
select CONVERT(varchar(10),ord.create_time, 25)as createtime
from [dbo].[order_main_spnc] as ord
where ord.create_time BETWEEN '{0} 00:00:00' AND '{1} 23:59:59'
)as dayData
group by dayData.createtime order by dayData.createtime" , startTime, endTime);
var result = db.Query <DayOrderNum>(sql) as List <DayOrderNum>;
return(result.ToList());
}
}
/// <summary>
/// 单日销售额
/// </summary>
/// <returns></returns>
public List <DaySalesVolume> GetDaySalesVolumes(string startTime, string endTime)
{
using (var db = new BusinessBase().OpenConnection())
{
string sql = string.Format(@"select dayData.createtime,sum(isnull(dayData.pricetotal ,0)) as pricetotal from (
select CONVERT(varchar(10),ord.create_time, 25)as createtime,ord.price_total as pricetotal
from [dbo].[order_main_spnc] as ord
where ord.create_time BETWEEN '{0} 00:00:00' AND '{1} 23:59:59')
as dayData
group by dayData.createtime order by dayData.createtime" , startTime, endTime);
var result = db.Query <DaySalesVolume>(sql) as List <DaySalesVolume>;
return(result.ToList());
}
}
/// <summary>
/// 根据渠道id查询渠道具体详细信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Info_TotalDataModel FindChannelDetail(int id)
{
using (var db = new BusinessBase().OpenConnection())
{
string sqlView = (@"select um.id,um.wx_picture,ISNULL(ct.career_type_name,'未激活') as career_type_name,um.user_nickname,
DATEDIFF(DAY,um.create_time,GETDATE()) as count_day from user_main um
left join career_type ct on um.career_type = ct.id
where um.id=@id");
DynamicParameters param = new DynamicParameters();
param.Add("id", id);
var result = db.Query <Info_TotalDataModel>(sqlView, param) as List <Info_TotalDataModel>;
return(result.FirstOrDefault());
}
}
/// <summary>
/// 地推每日获取人数
/// </summary>
/// <returns></returns>
public List <BDData> GetBDDatas()
{
using (var db = new BusinessBase().OpenConnection())
{
string date = " and DateDiff(dd,um.create_time,getdate())=0";//ub.create_time>'2018-09-19'
string sqlName = @"
select ums.wx_open_id,sus.F_RealName as name from WeChatServiceFlatform.dbo.user_main um
inner join WeChatServiceFlatform.dbo.user_type ut on um.id = ut.user_id
inner join WeChatServiceFlatform.dbo.user_main ums on um.wx_open_id_b=ums.wx_open_id
inner join WeChatServiceFlatform.dbo.user_type uts on ums.id =uts.user_id
inner join WeChatServiceFlatform.dbo.Sys_User sus on sus.F_Id = CONVERT(varchar(50),ums.id)
where ut.user_type=1 and uts.user_type=0
group by ums.wx_open_id,sus.F_RealName ";
var resultName = db.Query <BDData>(sqlName) as List <BDData>;
string sqlnum = string.Format(@"
select COUNT(*) as num, ums.wx_open_id,sus.F_RealName as name from WeChatServiceFlatform.dbo.user_main um
inner join WeChatServiceFlatform.dbo.user_type ut on um.id = ut.user_id
inner join WeChatServiceFlatform.dbo.user_main ums on um.wx_open_id_b=ums.wx_open_id
inner join WeChatServiceFlatform.dbo.user_type uts on ums.id =uts.user_id
inner join WeChatServiceFlatform.dbo.Sys_User sus on sus.F_Id = CONVERT(varchar(50),ums.id)
where ut.user_type=1 and uts.user_type=0 {0}
group by ums.wx_open_id,sus.F_RealName order by num ", date);
var resultnum = db.Query <BDData>(sqlnum) as List <BDData>;
foreach (var item in resultName.ToList())
{
string num = resultnum.Where(c => c.name == item.name).Select(c => c.num).FirstOrDefault();
item.num = num == null?"0":num;
}
return(resultName.ToList());
}
}
/// <summary>
/// 获取渠道详细信息
/// </summary>
/// <returns>渠道id</returns>
public Channel_User_Info GetChannel_User_Info(int userid)
{
using (var db = new BusinessBase().OpenConnection())
{
string sql = @"select ut.user_type_name as channeltype,ue.name as username,ue.mobile_number as phone,ct.career_type_name as channelname,up.village as address
from user_main as um
join user_type as ut on um.id=ut.user_id
left join user_extra as ue on um.id=ue.user_id
left join career_type as ct on um.career_type=ct.id
left join user_pickup_info as up on um.id=up.user_id
where ut.user_type=1 and um.id=@userid ";
DynamicParameters param = new DynamicParameters();
param.Add("userid", userid);
var result = db.Query <Channel_User_Info>(sql, param) as List <Channel_User_Info>;
return(result.FirstOrDefault());
}
}
/// <summary>
/// 统计新增用户数
/// </summary>
/// <param name="openid"></param>
/// <param name="date"></param>
/// <returns></returns>
public List <Info_TotalDataModel> FindEarthTotalByNewUserCount(string openid, int date)
{
int time = 0;
string filter = "";
DateTime current = DateTime.Now;
if (date == 0)
{
//日
time = current.Day;
filter = string.Format(" day(um.create_time)={0} ", time);
}
else if (date == 1)
{
//月
time = current.Month;
filter = string.Format(" month(um.create_time)={0} ", time);
}
else
{
//date == 2
//年
time = current.Year;
filter = string.Format(" year(um.create_time)={0} ", time);
}
using (var db = new BusinessBase().OpenConnection())
{
string viewSQl = string.Format(@"with t as (
select um.id,um.wx_picture,um.user_nickname,ct.career_type_name,um.create_time from user_main um
inner join user_type as ut on um.id=ut.user_id
left join career_type as ct on um.career_type=ct.id
where um.wx_open_id_b=@openid and ut.user_type=1 and {0}
group by um.id,um.wx_picture,um.user_nickname,ct.career_type_name,um.create_time
)
select t.*,count(*) as show_messages,row_number() over(order by count(*) desc) as seniority,DATEDIFF(DAY,t.create_time,GETDATE())as count_day
from user_b2c bc join t on bc.user_id_b = t.id
group by t.id,t.wx_picture,t.user_nickname,t.career_type_name,t.create_time", filter);
DynamicParameters param = new DynamicParameters();
param.Add("openid", openid);
param.Add("time", time);
var result = db.Query <Info_TotalDataModel>(viewSQl, param) as List <Info_TotalDataModel>;
return(result);
}
}
/// <summary>
/// 查询所有的地推信息
/// </summary>
/// <returns></returns>
public List <TotalChannelModel> FindAllEarthPushName()
{
using (var db = new BusinessBase().OpenConnection())
{
string viewSQl = @" with dtbd as (
select wx_open_id,name,um.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 count(dtbd.wx_open_id),dtbd.wx_open_id,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
group by dtbd.wx_open_id,dtbd.name,dtbd.create_time
order by convert(varchar(10),dtbd.create_time,23) asc";
var result = db.Query <TotalChannelModel>(viewSQl) as List <TotalChannelModel>;
return(result);
}
}
/// <summary>
/// 查询7天内所有的地推下的渠道数
/// </summary>
/// <param name="startTime">开始时间</param>
/// <param name="endTime">结束时间</param>
/// <returns></returns>
public List <TotalChannelModel> FindAllChannelCount(string startTime, string endTime)
{
using (var db = new BusinessBase().OpenConnection())
{
string viewSQl = string.Format(@"with dtbd as (
select wx_open_id,name 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 count(dtbd.wx_open_id)as channelCount,convert(varchar(100),um.create_time,23)as date,
isnull(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 and um.create_time between '{0}'and '{1}'
group by convert(varchar(100),um.create_time,23),dtbd.name", startTime, endTime);
var result = db.Query <TotalChannelModel>(viewSQl) as List <TotalChannelModel>;
return(result);
}
}
/// <summary>
/// 按销售额查询渠道排行信息
/// </summary>
/// <param name="day">日期编号 0是日 1是月 2是年</param>
/// <returns></returns>
public List <Info_TotalDataModel> FindEarthTotalBySales(string openid, int date)
{
string time = string.Empty;
using (var db = new BusinessBase().OpenConnection())
{
string whereStr = string.Empty;
string groupStr = string.Empty;
if (date == 0)
{
time = DateTime.Now.Day.ToString();//获取天数
whereStr = " day(create_time)=@time";
groupStr = " group by um.wx_picture,um.user_nickname,ct.career_type_name,um.create_time,um.id";
}
else if (date == 1)
{
time = DateTime.Now.Month.ToString();//获取当前月份
whereStr = " month(create_time)=@time";
groupStr = " group by um.wx_picture,um.user_nickname,ct.career_type_name,um.create_time,um.id";
}
else if (date == 2)
{
time = DateTime.Now.Year.ToString();//获取年份
whereStr = " year(create_time)=@time";
groupStr = " group by um.wx_picture,um.user_nickname,ct.career_type_name,um.create_time,um.id";
}
string viewSQl = string.Format(@"select um.id, um.wx_picture,ISNULL(ct.career_type_name,'未激活') as career_type_name,um.user_nickname,isnull( sum(isnull(ord.price_total ,0)), 0) show_messages,
DATEDIFF(DAY,um.create_time,GETDATE()) as count_day,row_number()over(order by sum(ord.price_total)desc) as seniority
from user_main um
left join (select * from order_main_spnc where {0}) ord on um.id = ord.user_id_b
left join career_type ct on um.career_type = ct.id
left join user_type ut on um.id = ut.user_id
where um.wx_open_id_b=@openid and ut.user_type = 1 {1}", whereStr, groupStr);
DynamicParameters param = new DynamicParameters();
param.Add("openid", openid);
param.Add("time", time);
var result = db.Query <Info_TotalDataModel>(viewSQl, param) as List <Info_TotalDataModel>;
return(result);
}
}
/// <summary>
/// 统计引流用户数
/// </summary>
/// <param name="openid"></param>
/// <param name="date"></param>
/// <returns></returns>
public List <Info_TotalDataModel> FindEarthTotalByNewdrainageCount(string openid, int date)
{
string time = string.Empty;
using (var db = new BusinessBase().OpenConnection())
{
string whereStr = string.Empty;
string groupStr = string.Empty;
if (date == 0)
{
time = DateTime.Now.Day.ToString();//获取天数
whereStr = " day(create_time)=@time";
groupStr = " group by um.wx_picture,um.user_nickname,ct.career_type_name,um.create_time,um.id";
}
else if (date == 1)
{
time = DateTime.Now.Month.ToString();//获取当前月份
whereStr = " month(create_time)=@time";
groupStr = " group by um.wx_picture,um.user_nickname,ct.career_type_name,um.create_time,um.id";
}
else if (date == 2)
{
time = DateTime.Now.Year.ToString();//获取年份
whereStr = " year(create_time)=@time";
groupStr = " group by um.wx_picture,um.user_nickname,ct.career_type_name,um.create_time,um.id";
}
string viewSQl = string.Format(@"select um.id,um.wx_picture,um.user_nickname,ct.career_type_name,um.create_time,count(*) as show_messages,
DATEDIFF(DAY,um.create_time,GETDATE()) as count_day,row_number() over(order by count(*) desc) as seniority
from(select * from order_main_spnc where {0}) as oms
join user_main as um on um.id=oms.user_id_b
left join user_type as ut on um.id=ut.user_id
left join career_type as ct on um.career_type=ct.id
where oms.Is_Import='1' and ut.user_type='1' and oms.wx_openid_bb=@openid {1}", whereStr, groupStr);
DynamicParameters param = new DynamicParameters();
param.Add("openid", openid);
param.Add("time", time);
var result = db.Query <Info_TotalDataModel>(viewSQl, param) as List <Info_TotalDataModel>;
return(result);
}
}
//获取该用户的个人信息
public User_main GetPersonInfo(string openid)
{
using (var db = new BusinessBase().OpenConnection())
{
string sql = string.Format(@"SELECT su.F_Id,su.F_RealName,so.F_FullName AS F_FullNameBm,sr.F_FullName AS F_FullNameGw,su.F_MobilePhone,pf.profit_percentage FROM dbo.Sys_User su inner
JOIN dbo.user_main um ON su.F_id=CONVERT(VARCHAR(200),um.id)
left JOIN dbo.profit pf ON um.id=pf.user_id
LEFT JOIN dbo.Sys_Role sr ON su.F_DutyId=sr.F_Id
LEFT JOIN dbo.Sys_Organize so ON su.F_DepartmentId=so.F_Id
WHERE um.wx_open_id='{0}'", openid);
var list = db.Query <User_main>(sql) as List <User_main>;
User_main model = new User_main()
{
F_Id = list[0].F_Id,
F_RealName = list[0].F_RealName,
DepartmentId = list[0].F_FullNameBm,
F_DutyId = list[0].F_FullNameGw,
F_MobilePhone = list[0].F_MobilePhone,
profit_percentage = list[0].profit_percentage
};
return(model);
}
}
/// <summary>
/// 查询某一具体渠道的业绩
/// </summary>
/// <param name="id">渠道id</param>
/// <param name="date">日期编号 0是日 1是月 2是年</param>
/// <returns></returns>
public Channel_AchievementDataModel GetChannel_AchievementById(int id, int date)
{
string PastTimes = string.Empty; //过去时间
string NowData = string.Empty; //现在时间
string userPastTimes = string.Empty; //过去时间
string userNowData = string.Empty; //现在时间
switch (date)
{
case 0:
PastTimes = " DateDiff(dd,create_time,getdate())=1";
NowData = " DateDiff(dd,create_time,getdate())=0";
userPastTimes = " DateDiff(dd,ums.create_time,getdate())=1";
userNowData = " DateDiff(dd,ums.create_time,getdate())=0";
break;
case 1:
PastTimes = " DateDiff(mm,create_time,getdate())=1";
NowData = " DateDiff(mm,create_time,getdate())=0";
userPastTimes = " DateDiff(mm,ums.create_time,getdate())=1";
userNowData = " DateDiff(mm,ums.create_time,getdate())=0";
break;
case 2:
PastTimes = " DateDiff(yy,create_time,getdate())=1";
NowData = " DateDiff(yy,create_time,getdate())=0";
userPastTimes = " DateDiff(yy,ums.create_time,getdate())=1";
userNowData = " DateDiff(yy,ums.create_time,getdate())=0";
break;
}
using (var db = new BusinessBase().OpenConnection())
{
string sql = string.Format(@"select (
( select isnull(sum(isnull(ord.price_total ,0)), 0)
from user_main um
inner join (select * from order_main_spnc where {1}) ord on um.id = ord.user_id_b
left join user_type ut on um.id = ut.user_id
where um.id='{0}' and ut.user_type = 1)) as sales,
( select COUNT(*)
from user_main as um
inner join (select * from order_main_spnc where {1}) as ord on um.id=ord.user_id_b
left join user_type ut on um.id = ut.user_id
where um.id='{0}' and ut.user_type = 1 ) as ordernum,
( select count(*) from user_main um
inner join user_type ut on um.id = ut.user_id
where wx_open_id_b in (
select wx_open_id from user_main ums where ums.id='{0}' and {3})
and ut.user_type=3) as newuser,
( select count(*)
from (select * from order_main_spnc where {1}) as oms
join user_main as um on um.id=oms.user_id_b
left join user_type as ut on um.id=ut.user_id
where oms.Is_Import='1' and ut.user_type='1' and oms.user_id='{0}') as drainage
union all
select (
( select isnull( sum(isnull(ord.price_total ,0)), 0)
from user_main um
inner join (select * from order_main_spnc where {2}) ord on um.id = ord.user_id_b
left join user_type ut on um.id = ut.user_id
where um.id='{0}' and ut.user_type = 1 )) as sales,
( select COUNT(*)
from user_main as um
inner join (select * from order_main_spnc where {2}) as ord on um.id=ord.user_id_b
left join user_type ut on um.id = ut.user_id
where um.id='{0}' and ut.user_type = 1 ) as ordernum,
( select count(*) from user_main um
inner join user_type ut on um.id = ut.user_id
where wx_open_id_b in (
select wx_open_id from user_main ums where ums.id='{0}' and {4})
and ut.user_type=3) as newuser,
( select count(*)
from (select * from order_main_spnc where {2}) as oms
join user_main as um on um.id=oms.user_id_b
left join user_type as ut on um.id=ut.user_id
left join career_type as ct on um.career_type=ct.id
where oms.Is_Import='1' and ut.user_type='1' and oms.user_id='{0}') as drainage", id, PastTimes, NowData, userPastTimes, userNowData);
var List = db.Query <Channel_AchievementDataModel>(sql) as List <Channel_AchievementDataModel>;
Channel_AchievementDataModel dataModel = List[1];//赋值现在时间数据
//今日数据 减去昨日数据 除以昨日数据
dataModel.salestage = Convert.ToDouble(List[0].sales) > 0 ? Math.Round((Convert.ToDouble(List[1].sales) - Convert.ToDouble(List[0].sales)) / Convert.ToDouble(List[0].sales) * 100, 2) : 0.00;
dataModel.ordernumtage = List[0].ordernum > 0 ? Math.Round(Convert.ToDouble((List[1].ordernum - List[0].ordernum) / List[0].ordernum) * 100, 2) : 0;
dataModel.newusertage = List[0].newuser > 0 ? Math.Round((Convert.ToDouble(List[1].newuser) - Convert.ToDouble(List[0].newuser)) / Convert.ToDouble(List[0].newuser) * 100, 2) : 0;
dataModel.drainagetage = List[0].drainage > 0 ? Math.Round(Convert.ToDouble((List[1].drainage - List[0].drainage) / List[0].drainage) * 100, 2) : 0;
dataModel.salesstates = dataModel.salestage >= 0 ? 0 : 1;
dataModel.ordernumstates = dataModel.ordernumtage >= 0 ? 0 : 1;
dataModel.newuserstates = dataModel.newusertage >= 0 ? 0 : 1;
dataModel.drainagestates = dataModel.drainagetage >= 0 ? 0 : 1;
return(dataModel);
}
}