//判断权限
public int GetLogins(string name)
{
List <UserLogin> list = null;
int result = 0;
try
{
string str = "select U_Impower from UserLogin where U_Name = @U_Name";
var args = new DynamicParameters();
args.Add("@U_Name", name);
list = DapperHelper <UserLogin> .Query(str, args);
result = list[0].U_Impower;
}
catch (Exception e)
{
Logger.Error(e.TargetSite.ToString());
}
return(result);
}
/// <summary>
/// 获取兑换的各类商品的比例
/// </summary>
/// <param name="stDate"></param>
/// <param name="edDate"></param>
/// <returns></returns>
public List <IntPie> GetExRatio(DateTime stDate, DateTime edDate)
{
List <IntPie> dic = new List <IntPie>();
StringBuilder strSql = new StringBuilder();
strSql.Append("select eProjectName Name, count(*) Value " +
"from [i200].[dbo].[T_ExchangeLog] " +
"where eInsertTime<@edDate and eInsertTime>@stDate group by eProjectName;");
try
{
dic =
DapperHelper.Query <IntPie>(strSql.ToString(), new { stDate = stDate, edDate = edDate }).ToList();
}
catch (Exception ex)
{
return(dic);
}
return(dic);
}
public List <MobileMapModel> SeperateModels(string where)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" select id,gName into #list from OTwoO.dbo.GoodsInfo;");
strSql.Append(" select * into #info from #list inner join ( ");
strSql.Append(" select gid,accid,accountName,maxClass,minClass,goodsNum,price from OTwoO.dbo.GoodsList where ");
strSql.Append(" gid in(select id from #list)) a on a.gid=#list.id; ");
strSql.Append(
" select * into #all from #info left join (select picUrl,gid as goodsId from OTwoO.dbo.goodsPic) g on g.goodsId=#info.gid;");
strSql.Append(" select top 50000 * from #all inner join( ");
strSql.Append(" select accountid,TrueConty from i200.dbo.T_Account_User where ");
strSql.Append(" accountid in(select accid from #info) and grade='管理员' and TrueConty is not null) a ");
strSql.Append(" on a.accountid=#all.accid left join ( select companyaddress,phonenumber,id from i200.dbo.T_Account " + where + " ) t on t.ID=#all.accid left join ( select accid aid,aa_ShortUrl as ShortUrl from i200.dbo.t_App_Au where appName='HTML' ) s on #all.accid=s.aid where companyaddress is not null;");
strSql.Append(" drop table #list; ");
strSql.Append(" drop table #info; ");
strSql.Append(" drop table #all; ");
return(DapperHelper.Query <MobileMapModel>(strSql.ToString()).ToList());
}
/// <summary>
/// 获取符合条件的推送用户列表
/// 符合条件用户的公告型消息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public List <string> GetPushUserList(int id)
{
StringBuilder strSql = new StringBuilder();
switch (id)
{
case 1:
//strSql.Append("select ID from i200.dbo.T_Account where LoginTimeLast between CAST((GETDATE()-3) as date) and (CAST(GETDATE() as date)) " +
// "and ID in (select distinct accid from [I200].[dbo].[T_Token_Api] where createTime between CAST((GETDATE()-3) as date) and (CAST(GETDATE() as date)));");
//移动端三天内登录过用户
strSql.Append(
"select distinct Accountid from i200.dbo.T_LOG where DATEDIFF(DAY,OperDate,GETDATE())<=3 and DATEDIFF(DAY,OperDate,GETDATE())>=0 and LogMode=4 ");
strSql.Append(" union ");
strSql.Append(
" select ID from i200.dbo.T_Account where State=1 and DATEDIFF(DAY,RegTime,GETDATE())<=3 and DATEDIFF(DAY,RegTime,GETDATE())>=0;");
break;
default:
return(null);
}
return(DapperHelper.Query <string>(strSql.ToString()).ToList());
}
/// <summary>
/// 页面获取条件组
/// </summary>
/// <param name="verif"></param>
/// <returns></returns>
public List <ConditionSettingModel.ConditionRecoveryModel> GetRuleCondition(string verif)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(
"select rl.Remark,rl.RuleId,rl.MaxValue,rl.MinValue,rl.RangeData,rd.ColDesc,rl.Id,rd.ColName,rd.ConditionGroup,rd.ConditionType " +
"from Sys_RuleList rl left join Sys_RuleDetail rd " +
"on rl.RuleId=rd.Id " +
"where VerifId=@verif;");
try
{
return(DapperHelper.Query <ConditionSettingModel.ConditionRecoveryModel>(strSql.ToString(), new
{
verif = verif
}).ToList());
}
catch (Exception ex)
{
Logger.Error("在消息发送页面获取筛选器条件出错！", ex);
return(null);
}
}
/// <summary>
/// 获取每日店铺支付宝收单信息
/// </summary>
/// <param name="stDate"></param>
/// <param name="edDate"></param>
/// <returns></returns>
public List <ShopAlipay> GetDailyShopAlipayList(DateTime stDate, DateTime edDate)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(
"select cast(CreateTime as date) DayDate,AccId,count(SalesId) SaleCount,sum(TotalMoney) SaleMoney " +
"from [i200].[dbo].[T_Sale_Payment] " +
"where PayStatus>=8 and CreateTime between @stDate and @edDate " +
"group by AccId,cast(CreateTime as date)");
try
{
return(DapperHelper.Query <ShopAlipay>(strSql.ToString(), new
{
stDate = stDate,
edDate = edDate
}).ToList());
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 获取每日支付宝交易店铺汇总信息(附加微信数据)
/// </summary>
/// <param name="stDate"></param>
/// <param name="edDate"></param>
/// <returns></returns>
public List <dynamic> GetDailySummaryAboutAlipay(DateTime stDate, DateTime edDate)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(
"select cast(CreateTime as date) dayDate,Count(distinct AccId) AccIdCount,count(SalesId) SaleCount,sum(TotalMoney) SaleMoney " +
"into #Alipay from [i200].[dbo].[T_Sale_Payment] " +
"where PayStatus>=8 and CreateTime between @stDate and @edDate " +
"group by cast(CreateTime as date);" +
"select cast(bInsertTime as date) dayDate,Count(distinct AccId) wAccIdCount,count(bid) wSaleCount,sum(bRealMoney) wSaleMoney " +
"into #weixin from [I200].[dbo].[T_Goods_Booking] where bState in (1,2,3,4) and payType=1 " +
"and bInsertTime between @stDate and @edDate " +
"group by cast(bInsertTime as date);" +
"select #Alipay.*,#weixin.wAccIdCount,#weixin.wSaleCount,#weixin.wSaleMoney " +
"from #Alipay left join #weixin " +
"on #Alipay.dayDate=#weixin.dayDate;" +
"drop table #Alipay;" +
"drop table #weixin;");
//"select cast(CreateTime as date) dayDate,Count(distinct AccId) AccIdCount,count(SalesId) SaleCount,sum(TotalMoney) SaleMoney " +
//"from [i200].[dbo].[T_Sale_Payment] " +
//"where PayStatus>=8 and CreateTime between @stDate and @edDate " +
//"group by cast(CreateTime as date)"
try
{
return(DapperHelper.Query <dynamic>(strSql.ToString(), new
{
stDate = stDate,
edDate = edDate
}).ToList());
}
catch (Exception ex)
{
Logger.Error("获取店铺支付宝收款详情出错！", ex);
return(null);
}
}
/// <summary>
/// 获得数据列表
/// </summary>
/// <param name="top">前几行</param>
/// <param name="columnName">需要获取的列名<para>为了方便不在处理，列与列用逗号分开，参照SQL写法</para></param>
/// <param name="dapperWheres">查询条件列表</param>
/// <param name="filedOrder">排序</param>
public List <T> GetList <T>(int top, string columnName, List <DapperWhere> dapperWheres, string filedOrder)
{
StringBuilder strSql = new StringBuilder();
string where = "";
Dictionary <string, object> parm = new Dictionary <string, object>();
foreach (DapperWhere item in dapperWheres)
{
if (where.Length > 0)
{
where += " and ";
}
where += item.Where;
parm[item.ColumnName] = item.Value;
}
strSql.Append("select ");
if (top > 0)
{
strSql.Append(" top " + top.ToString() + " ");
}
strSql.Append(" " + columnName + " ");
strSql.Append(" FROM Sys_DailyCheck ");
if (where.Length > 0)
{
strSql.Append(" where " + where);
}
if (filedOrder.Length > 0)
{
strSql.Append(" order by " + filedOrder);
}
strSql.Append(";");
return(DapperHelper.Query <T>(strSql.ToString(), parm).ToList());
}
/// <summary>
/// 获取单个用户发送的用户模型列表
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public List <SingleShopInfoForMsg> GetSingleUserMsg(int id)
{
StringBuilder strSql = new StringBuilder();
switch (id)
{
case 2: //付费功能到期(三天和一天)
//strSql.Append("")
strSql.Append("");
break;
case 3: //优惠券到期(三天或一天)
strSql.Append(
"select ci.couponDesc CouponName,ta.CompanyName,ta.ID Accid,DATEDIFF(DAY,getdate(),cl.endDate) Interval from [I200].[dbo].[T_Order_CouponList] cl " +
"left join i200.dbo.T_Account ta on cl.toAccId=ta.ID " +
"left join i200.dbo.T_Order_CouponInfo ci on cl.groupId=ci.id " +
"where cl.couponStatus=2 and (DATEDIFF(DAY,getdate(),cl.endDate)=3 or DATEDIFF(DAY,getdate(),cl.endDate)=1);");
break;
case 4: //获取周年用户
strSql.Append(
"select DATEDIFF(YEAR,regtime,getdate()) Interval,ID Accid,CompanyName from [i200].[dbo].[T_Account] " +
"where DATEPART(MONTH,RegTime)=DATEPART(MONTH,getdate()) and DATEPART(day,RegTime)=DATEPART(day,getdate()) and DATEDIFF(MONTH,RegTime,GETDATE())>0 and state=1");
break;
}
try
{
return(DapperHelper.Query <SingleShopInfoForMsg>(strSql.ToString()).ToList());
}
catch (Exception ex)
{
Logger.Error("获取消息发送对象列表出错！", ex);
return(new List <SingleShopInfoForMsg>());//异常则返回空列表
}
}
public List <HourAnalysisItemList> GetLogRegTimeReportSourceDAL(int timeType)
{
DateTime startTime = DateTime.Now;
DateTime endTime = DateTime.Now;
startTime = startTime.AddDays(0 - Math.Abs(Convert.ToInt32(timeType)));
StringBuilder strSql = new StringBuilder();
strSql.Append(" select cast((DATENAME(HOUR,OperDate))as int) [hour],COUNT(DISTINCT Accountid) [num] ");
strSql.Append(" from i200.dbo.T_LOG ");
strSql.Append(" where OperDate>@statTime and OperDate<@endTime ");
strSql.Append(" group by DATENAME(HOUR,OperDate) ");
strSql.Append(" order by hour asc ");
List <HourAnalysisItemList> dynamicList = DapperHelper.Query <HourAnalysisItemList>(strSql.ToString(), new { statTime = startTime, endTime = endTime }).ToList();
return(dynamicList);
//foreach (dynamic itemList in dynamicList)
//{
// HourAnalysisItemList item = new HourAnalysisItemList();
// item.hour = itemList.hour;
// item.num = itemList.num;
//}
}
public AgentList GetAgentList(int pageIndex)
{
AgentList agentModel = new AgentList();
int bgNumber = ((pageIndex - 1) * 15) + 1;
int edNumber = (pageIndex) * 15;
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from (select row_number() over (order by ID desc) rowNumber,* from Sys_agent_mess) t where t.rowNumber between @bgNumber and @edNumber; ");
agentModel.list =
DapperHelper.Query <AgentModel>(strSql.ToString(), new { bgNumber = bgNumber, edNumber = edNumber })
.ToList();
strSql.Clear();
strSql.Append("select count(*) from Sys_agent_mess;");
agentModel.rowCount = DapperHelper.ExecuteScalar <int>(strSql.ToString());
agentModel.maxPage = (agentModel.rowCount % 15 == 0) ? agentModel.rowCount / 15 : (agentModel.rowCount / 15 + 1);
return(agentModel);
}
/// <summary>
/// 每种商品的访客和兑换比
/// </summary>
/// <param name="stDate"></param>
/// <param name="edDate"></param>
/// <returns></returns>
public List <DecimalPie> GetVisitRatio(DateTime stDate, DateTime edDate)
{
List <DecimalPie> dic = new List <DecimalPie>();
StringBuilder strSql = new StringBuilder();
strSql.Append(
"select ProductName Name,sum(Ratio)/count(*) Value " +
"from [Sys_I200].[dbo].[Sys_DailyIntegralExchange] " +
"where Daydate between @stDate and @edDate group by ProductName;");
try
{
dic =
DapperHelper.Query <DecimalPie>(strSql.ToString(), new { stDate = stDate, edDate = edDate }).ToList();
}
catch (Exception ex)
{
return(dic);
}
return(dic);
}
/// <summary>
/// 得到短信详情
/// </summary>
/// <param name="noticeid">标记ID</param>
/// <param name="accid">店铺ID</param>
/// <param name="page">分页</param>
/// <param name="pageSiz">每页面显示行</param>
/// <param name="phone">电话</param>
/// <returns></returns>
public SmsDetail GetSmsDetailList(int noticeid, int accid = 0, int page = 1, int pageSiz = 50, string phone = "")
{
List <SmsDetails> detailList = new List <SmsDetails>();
SmsDetail details = new SmsDetail();
//页数计算
int bgNumber = ((page - 1) * pageSiz) + 1;
int edNumber = (page) * pageSiz;
StringBuilder strWhere = new StringBuilder();
strWhere.Append(" notifyID=@notifyID ");
if (accid > 0)
{
strWhere.Append(" and accID=@accid ");
}
if (phone.Length > 0)
{
strWhere.Append(" and phoneNum like '%'+ @phone +'%'");
}
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from(SELECT ROW_NUMBER() over(order by id desc) as rowNumber, " +
"id, " +
"phoneNum," +
"cast(smsChannel as varchar(100)) as smsChannel, " +
"smsContent,LEN(smsContent) as ContentLength, " +
"sendtime, " +
"cast(smsStatus as varchar(100)) as smsStatus," +
"userID, " +
"realCnt, " +
"isFree, " +
"errDesc," +
"smsReport " +
" FROM I200.dbo.T_Sms_List where " + strWhere.ToString() + ") t where t.rowNumber between @startIndex and @endIndex;");
detailList = DapperHelper.Query <SmsDetails>(strSql.ToString(), new
{
accid = accid,
notifyID = noticeid,
phone = phone,
startIndex = bgNumber,
endIndex = edNumber
}).ToList();
strSql.Clear();
strSql.Append("SELECT count(id) as smsCnt,'' as moreStr FROM I200.dbo.T_Sms_List where " + strWhere.ToString() + " ; ");
details = DapperHelper.Query <SmsDetail>(strSql.ToString(), new
{
accid = accid,
notifyID = noticeid,
phone = phone,
startIndex = bgNumber,
endIndex = edNumber
}).ToList()[0];
details.detailList = detailList;
return(details);
}
/// <summary>
/// 根据业务Id计算相关产品的成本和利润
/// </summary>
/// <param name="busId"></param>
/// <param name="accId"></param>
/// <returns></returns>
/// 所有打包产品都算Saas服务
/// 单独的短信包和维客短信是第三方服务
/// 话费充值和实物产品算他营产品
public OrderItemProp GetListItemProps(int busId, int accId)
{
StringBuilder strSql = new StringBuilder();
OrderItemProp propModel = new OrderItemProp();
strSql.Append("select * from [i200].[dbo].[T_Order_Project_List] where busId=@busId;");
try
{
List <dynamic> projectList = DapperHelper.Query <dynamic>(strSql.ToString(), new { busId = busId }).ToList();
//独立产品的处理
if (projectList.Count == 1)
{
dynamic model = projectList[0];
//处理短信
if (model.itemId == 1)
{
propModel.Cost = Convert.ToInt32(model.itemQuantity) * 0.05;
//标记为第三方服务
propModel.PureSms = "1";
}
//处理维客短信
else if (model.itemName.ToString().IndexOf("维客") >= 0)
{
T_Sms_ListDAL dal = new T_Sms_ListDAL();
double smsCnt = dal.GetFreeSmsCnt(accId);
propModel.Cost = smsCnt * 0.05;
propModel.isFreeSms = 1;
//标记为第三方服务
propModel.PureSms = "1";
}
else if (model.itemName.ToString().IndexOf("充值100") >= 0)
{
propModel.Cost = 100;
}
else if (model.itemName.ToString().IndexOf("充值50") >= 0)
{
propModel.Cost = 50;
}
else if (model.itemName.ToString().IndexOf("充值30") >= 0)
{
propModel.Cost = 30;
}
else
{
propModel.Cost = 0;
propModel.SelfMark = "1";
}
}
//组合产品的处理
else if (projectList.Count > 1)
{
dynamic model = projectList[0];
//propModel.SelfMark = "1";
//处理维客类套餐
if (model.itemName.ToString().IndexOf("维客") >= 0)
{
T_Sms_ListDAL dal = new T_Sms_ListDAL();
double smsCnt = dal.GetFreeSmsCnt(accId);
propModel.Cost = smsCnt * 0.06;
propModel.isFreeSms = 1;
//标记为第三方服务
propModel.PureSms = "1";
}
else
{
foreach (var item in projectList)
{
if (item.itemId == 1)
{
propModel.Cost += Convert.ToInt32(model.itemQuantity) * 0.06;
}
}
propModel.SelfMark = "1";
}
}
//空列表
else
{
}
return(propModel);
}
catch (Exception ex)
{
Logger.Error("获取用户详情订单摘要信息出错", ex);
return(propModel);
}
}
public Dictionary <string, List <DecimalPie> > GetPaidType(DateTime stDate, DateTime edDate)
{
Dictionary <string, List <DecimalPie> > dataDic = new Dictionary <string, List <DecimalPie> >();
List <DecimalPie> dic = new List <DecimalPie>();
List <DecimalPie> dicSec = new List <DecimalPie>();
List <DecimalPie> dicAppend = new List <DecimalPie>();
StringBuilder strSql = new StringBuilder();
strSql.Append("select Remark Name," +
"sum( case when (cast(FinialVal as int)>cast(OriginalVal as int)) then cast(EditVal as int) else 0 end ) Value " +
"FROM [i200].[dbo].[T_LogInfo] where CreatTime>@stDate and CreatTime<@edDate and Keys='Integral' and LogType=17 group by Remark;");
dicSec = DapperHelper.Query <DecimalPie>(strSql.ToString(), new { stDate = stDate, edDate = edDate }).ToList();
dicSec.RemoveAll(x => x.Value == 0);
strSql.Clear();
strSql.Append(
"SELECT LogType Name," +
"sum( case when (cast(FinialVal as int)>cast(OriginalVal as int)) then cast(EditVal as int) else 0 end ) Value " +
"FROM [i200].[dbo].[T_LogInfo] where CreatTime>@stDate and CreatTime<@edDate and Keys='Integral' group by LogType;");
try
{
dic =
DapperHelper.Query <DecimalPie>(strSql.ToString(), new { stDate = stDate, edDate = edDate }).ToList();
foreach (DecimalPie item in dic)
{
switch (item.Name)
{
case "1":
item.Name = "积分兑换";
break;
case "2":
item.Name = "抽奖活动";
break;
case "4":
item.Name = "订单购买";
break;
case "5":
item.Name = "支持生意专家";
break;
case "6":
item.Name = "分享生意专家 完善资料 分享视频";
break;
case "7":
item.Name = "每日签到";
break;
case "8":
item.Name = "每日心情";
break;
case "9":
item.Name = "关注微信";
break;
case "10":
item.Name = "论坛金币兑换";
break;
case "11":
item.Name = "完成会员引导";
break;
case "12":
item.Name = "完成商品引导";
break;
case "13":
item.Name = "完成销售引导";
break;
case "14":
item.Name = "邀请注册";
break;
case "16":
item.Name = "新手任务";
break;
case "17":
item.Name = "每日行为";
break;
}
}
dic.RemoveAll(x => x.Value == 0);
}
catch (Exception ex)
{
return(null);
}
foreach (DecimalPie item in dic)
{
dicAppend.Add(item);
}
dicAppend.RemoveAll(x => x.Name == "每日行为");
foreach (DecimalPie item in dicSec)
{
dicAppend.Add(item);
}
dataDic.Add("fData", dic);
dataDic.Add("sData", dicAppend);
return(dataDic);
}
/// <summary>
/// 绑定等级下拉
/// </summary>
/// <returns></returns>
public List <ShenLevel> GetLevel()
{
return(DapperHelper.Query <ShenLevel>("select * from slevel", null));
}
/// <summary>
/// 得到优惠券列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="dapperWheres"></param>
/// <returns></returns>
public new List <OrderCouponInfoListItem> GetList(int pageIndex, int pageSize, List <DapperWhere> dapperWheres, string filedOrder = "")
{
StringBuilder strSql = new StringBuilder();
string where = "";
Dictionary <string, object> parm = new Dictionary <string, object>();
foreach (DapperWhere item in dapperWheres)
{
if (where.Length > 0)
{
where += " and ";
}
where += item.Where;
parm[item.ColumnName] = item.Value;
}
if (filedOrder == "")
{
filedOrder = " id desc";
}
strSql.Append(" SELECT id,couponDesc,maxLimitNum,createDate,endDate,operatorId,remark,couponStatus into #list FROM ( ");
strSql.Append(" SELECT ROW_NUMBER() OVER ( ");
if (filedOrder.Length > 0)
{
strSql.Append(" order by " + filedOrder + " ");
}
else
{
strSql.Append(" order by T.id desc");
}
strSql.Append(" )AS Row, * from I200.dbo.T_Order_CouponInfo T ");
if (where.Length > 0)
{
strSql.Append(" where " + where + " ");
}
strSql.Append(" ) TT ");
strSql.Append(" WHERE TT.Row between @startIndex and @endIndex; ");
strSql.Append(" select #list.id,couponDesc,maxLimitNum,createDate,endDate,operatorId,isnull(b.name,'系统') opertorName, ");
strSql.Append(" remark,couponStatus,isnull(produceNum,0)produceNum,isnull(bindingNum,0)bindingNum,isnull(useNum,0) useNum ");
strSql.Append(" from #list left outer join( ");
strSql.Append(" select groupId,COUNT(id) produceNum,sum(case when toAccId>0 then 1 else 0 end) bindingNum,SUM(case when useAccId>0 then 1 else 0 end) useNum ");
strSql.Append(" from i200.dbo.T_Order_CouponList where groupId in(select id from #list) group by groupId ");
strSql.Append(" ) a on a.groupId=#list.id left outer join( ");
strSql.Append(" select Id,name from Sys_Manage_User ");
strSql.Append(" where Id in(select operatorId from #list)) b on b.Id=#list.operatorId ");
strSql.Append(" drop table #list ");
if (pageIndex < 1)
{
pageIndex = 1;
}
int bgNumber = ((pageIndex - 1) * pageSize) + 1;
int edNumber = (pageIndex) * pageSize;
parm["startIndex"] = bgNumber;
parm["endIndex"] = edNumber;
return(DapperHelper.Query <OrderCouponInfoListItem>(strSql.ToString(), parm).ToList());
}
/// <summary>
/// 查询项目详情
/// </summary>
/// <returns></returns>
public List <ProjectInfo> ProjectDetail(int ProjectId)
{
string sql = "select * from ProjectInfo where ProjectId=@projectId and ProjectStaus=1";
return(DapperHelper <ProjectInfo> .Query(sql, new { projectId = ProjectId }));
}
public Dictionary <string, string> GetTagByCondition(int pageIndex, int tagType, string insertName = "",
string tagName = "")
{
Dictionary <string, string> dict = new Dictionary <string, string>()
{
{ "list", "" },
{ "PageCount", "" },
{ "RowCount", "" },
};
StringBuilder strSql = new StringBuilder();
string where = " 1=1 ";
int bgNumber = ((pageIndex - 1) * 15) + 1;
int edNumber = (pageIndex) * 15;
if (tagType != -99)
{
where += " and tagTypeid=" + tagType + " ";
}
if (!string.IsNullOrEmpty(insertName))
{
where += " and insertName like '%" + insertName + "%' ";
}
if (!string.IsNullOrEmpty(tagName))
{
where += " and t_Name like '%" + tagName + "%' ";
}
where += " and tagStatus=1 ";
strSql.Append("select * from ( select *,row_number() over (order by id) rowNumber from Sys_TagInfo where" +
where + ") t where t.rowNumber between @bgNumber and @edNumber;");
List <Sys_TagInfoBasic> list = DapperHelper.Query <Sys_TagInfoBasic>(strSql.ToString(),
new { bgNumber = bgNumber, edNumber = edNumber })
.ToList();
//dict["list"] =
// CommonLib.Helper.JsonSerializeObject(
// , "yyyy-mm-dd HH:mm:ss");
strSql.Clear();
strSql.Append("select tag_id,count(*) countNum from [sys_i200].[dbo].[Sys_TagNexus] group by tag_id;");
List <dynamic> tagList = DapperHelper.Query <dynamic>(strSql.ToString()).ToList();
foreach (Sys_TagInfoBasic item in list)
{
try
{
item.AccidCount = Convert.ToInt32(tagList.Find(x => x.tag_id == item.id).countNum);
}
catch (Exception ex)
{
item.AccidCount = 0;
}
}
strSql.Clear();
strSql.Append("select count(*) from Sys_TagInfo where" + where);
int rowCount = DapperHelper.ExecuteScalar <int>(strSql.ToString());
dict["list"] = CommonLib.Helper.JsonSerializeObject(list, "yyyy-mm-dd HH:mm:ss");
dict["RowCount"] = rowCount.ToString();
dict["PageCount"] = (rowCount % 15 == 0 ? rowCount / 15 : (rowCount / 15 + 1)).ToString();
return(dict);
}
public chartDataModel GetAgentStat(string stDate, string edDate, string[] cols)
{
DateTime st = Convert.ToDateTime(stDate);
DateTime ed = Convert.ToDateTime(edDate);
StringBuilder strSql = new StringBuilder();
StringBuilder strColumn = new StringBuilder();
StringBuilder strDrop = new StringBuilder();
Dictionary <string, string> ColumnList = new Dictionary <string, string>();
strSql.Append("declare @Reg int,@Act int,@Loyal int;");
strColumn.Append("select cast(@date as date) dayDate");
if (cols.Contains("reg"))
{
strColumn.Append(",@Reg Reg");
ColumnList.Add("Reg", "注册用户");
strSql.Append("select @Reg=COUNT(*) from i200.dbo.T_Account where agentID>0 and state=1 and cast(RegTime as date) <=@date;");
}
if (cols.Contains("active"))
{
strColumn.Append(",@Act Active");
ColumnList.Add("Active", "活跃用户");
strSql.Append("select distinct accid into #actList from [Sys_I200].[dbo].[SysRpt_ShopActive] " +
"where DATEDIFF(DAY,@date,updatetime)<lastNum and DATEDIFF(DAY,@date,updatetime)>=0 and active>4;" +
"select @Act=COUNT(*) from i200.dbo.T_Account where AgentId>0 and state=1 and ID in (select accid from #actList);");
strDrop.Append("drop table #actList;");
}
//if (cols.Contains("loyal"))
//{
// strColumn.Append(",@Loyal Loyal");
// ColumnList.Add("Loyal", "忠诚用户");
// strSql.Append("select distinct accid into #loyList from [Sys_I200].[dbo].[SysRpt_ShopActive] " +
// "where DATEDIFF(DAY,@date,updatetime)<lastNum and DATEDIFF(DAY,@date,updatetime)>0 and active=7;" +
// "select @Loyal=COUNT(*) from i200.dbo.T_Account where AgentId<>0 and ID in (select accid from #loyList);");
// strDrop.Append("drop table #loyList;");
//}
strSql.Append(strDrop.ToString());
strSql.Append(strColumn.ToString());
List <dynamic> list = new List <dynamic>();
DateTime iter = st;
while (iter < ed)
{
list.Add(DapperHelper.Query <dynamic>(strSql.ToString(), new { date = iter.Date }).ToList()[0]);
iter = iter.AddDays(1);
}
chartDataModel chartModel = new chartDataModel();
return(chartModel.SetDataAboutDataTime(st, ed, list, ColumnList, "代理商汇总"));
}
/// <summary>
/// 绑定项目状态
/// </summary>
/// <returns></returns>
public List <ProjectInfo> BandProjectStaus(int ProjectId)
{
string sql = "select P.Projectstage,s.StageName from ProjectInfo p join StagePlanInfo s on p.Projectstage=s.ProjectStageId where s.ProjectStageId=projectId";
return(DapperHelper <ProjectInfo> .Query(sql, new { projectId = ProjectId }));
}
//以现有扩展信息更新行业结果
public int UpdateIndustryByExtendinfo()
{
StringBuilder strSql = new StringBuilder();
List <ShopExtIndustry> shopExtList = new List <ShopExtIndustry>();
//获取有分类的店铺及其大小分类
strSql.Append("select AccId,Industry IndustryStr from P_Sys_UserPortrait where PATINDEX('%[0-9]%',Industry)<>0;");
try
{
shopExtList = DapperHelper.Query <ShopExtIndustry>(strSql.ToString()).ToList();
}
catch (Exception ex)
{
Logger.Error("获取现有用户扩展信息行业出错！", ex);
}
//如果获取到信息进行处理
if (shopExtList.Count > 0)
{
foreach (var item in shopExtList)
{
List <int> industryList = new List <int>();
List <string> industryStrList =
item.IndustryStr.Split(',').ToList();
foreach (var indust in industryStrList)
{
int i = 0;
if (int.TryParse(indust, out i))
{
industryList.Add(i);
}
}
if (industryList.Count > 0)
{
//获取大小行业
foreach (var itemId in industryList)
{
ShopIndustryDic dic = GetIndustryPairDic(itemId);
if (dic.ParentId == 0)
{
item.Industry_1 = dic.Keyword;
}
else
{
item.Industry_2 = dic.Keyword;
}
}
}
//更新用户行业表
UpdateExtIndustry(item);
}
return(1);
}
else
{
return(-1);
}
}
/// <summary>
/// 得到销售地图销售信息
/// </summary>
/// <param name="oid"></param>
/// <returns></returns>
public List <saleMapItemList> GetSaleMapSaleList(int oid)
{
List <saleMapItemList> mapList = new List <saleMapItemList>();
StringBuilder sql = new StringBuilder();
sql.Append("create table #temp (orderid int,ShopNum nvarchar(30), ShopName nvarchar(300),ShopAddress nvarchar(100),SellMoney money,DetailAddress nvarchar(200),SellTime datetime,accountid int ); ");
if (oid > 0)
{
sql.Append("insert into #temp(orderid,accountid,ShopNum,SellMoney,SellTime) ");
sql.Append("select top 20 saleID,accID,saleNo,RealMoney,insertTime from i200.dbo.T_SaleInfo where saleID>'" + oid.ToString() + "' order by saleID asc; ");
}
else
{
sql.Append("insert into #temp(orderid,accountid,ShopNum,SellMoney,SellTime) ");
sql.Append("select top 30 saleID,accID,saleNo,RealMoney,insertTime from i200.dbo.T_SaleInfo order by saleID desc; ");
}
sql.Append(" update #temp set ShopAddress=TrueConty from #temp inner join i200.dbo.t_account_user on t_account_user.accountid=#temp.accountid and grade='管理员'; ");
sql.Append(" update #temp set ShopName=CompanyName,DetailAddress=CompanyAddress from #temp inner join i200.dbo.T_Account on #temp.accountid=T_Account.ID; ");
sql.Append(" select * from #temp order by orderid asc ; ");
sql.Append(" drop table #temp; ");
List <dynamic> list = DapperHelper.Query(sql.ToString()).ToList();
if (list != null && list.Count > 0)
{
foreach (dynamic item in list)
{
saleMapItemList itemObj = new saleMapItemList();
itemObj.ShopCar = item.ShopNum.ToString();
itemObj.ShopName = item.ShopName.ToString();
itemObj.Address = item.ShopAddress.ToString();
itemObj.id = item.orderid.ToString();
if (string.IsNullOrEmpty(itemObj.Address) || itemObj.Address == "not find")
{
itemObj.Address = "乌鲁木齐";
}
else
{
try
{
itemObj.Address = itemObj.Address.Split('.')[0];
}
catch
{
itemObj.Address = "乌鲁木齐";
}
}
itemObj.SellMoney = string.Format("{0:C}", Convert.ToDouble(item.SellMoney.ToString()));
if (item.DetailAddress != null)
{
itemObj.DetailAddress = item.DetailAddress.ToString();
}
itemObj.SellTime = (int)System.DateTime.Now.Subtract(Convert.ToDateTime(item.SellTime.ToString())).TotalMinutes;
mapList.Add(itemObj);
}
}
return(mapList);
}
/// <summary>
/// 查询车辆品牌信息用于绑定下拉
/// </summary>
/// <returns></returns>
public List <Brand> GetBrands()
{
return(DapperHelper.Query <Brand>("select * from Brand", null));
}
public List <UserInfos> Show()
{
string sql = "select * from UserInfos";
return(DapperHelper <UserInfos> .Query(sql, null));
}
/// <summary>
/// 项目阶段详情
/// </summary>
/// <param name="ProjectStageId"></param>
/// <returns></returns>
public List <StagePlanInfo> StageDetail(int ProjectStageId)
{
string sql = "select * from StagePlanInfo where ProjectStageId=@projectStageId and StageStaus=1";
return(DapperHelper <StagePlanInfo> .Query(sql, new { projectStageId = ProjectStageId }));
}
public UserInfos ShowById(int id)
{
string sql = "select * from UserInfos where id=@id";
return(DapperHelper <UserInfos> .Query(sql, new { id }).FirstOrDefault());
}
/// <summary>
/// 数据反填
/// </summary>
/// <returns></returns>
public List <StagePlanInfo> StageBackFill(int PlanId)
{
string sql = "select * from StagePlanInfo where PlanId=@planId";
return(DapperHelper <StagePlanInfo> .Query(sql, new { planId = PlanId }));
}
public string TagTransfer()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select acc_id accid " +
"from Sys_TagNexus where tag_id=( select id from Sys_TagInfo where t_Name='朋友介绍'); ");
List <int> accidList = DapperHelper.Query <int>(strSql.ToString()).ToList();
string reStr = "";
foreach (int accid in accidList)
{
strSql.Clear();
strSql.Append("if exists (select * from P_Sys_UserPortrait where AccId=@accid) ");
strSql.Append(" begin ");
strSql.Append(" select Id from P_Sys_UserPortrait where AccId=@accid; ");
strSql.Append(" end ");
strSql.Append(" else ");
strSql.Append(" begin ");
strSql.Append(" insert into P_Sys_UserPortrait (InsertTime,AccId,industry,UserSourcePortrait,ChoiceReason,PotentialNeed) values (GETDATE(),@accid,',',',',',',',') ");
strSql.Append(" select @@IDENTITY; ");
strSql.Append(" end");
int reVal = DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { accid = accid });
strSql.Clear();
strSql.Append("select UserSourcePortrait from P_Sys_UserPortrait where AccId=@accid;");
string industry = DapperHelper.ExecuteScalar <string>(strSql.ToString(), new { accid = @accid });
if (string.IsNullOrEmpty(industry))
{
industry = ",64,";
}
else
{
if (!industry.Contains(",64,"))
{
industry += ",64,";
}
//if (!industry.Contains(",27,"))
//{
// industry += ",27,";
//}
}
strSql.Clear();
strSql.Append("update P_Sys_UserPortrait set UserSourcePortrait='" + industry + "' where accid=@accid;");
reVal = DapperHelper.Execute(strSql.ToString(), new { accid = accid });
if (reVal != 1)
{
reStr += " " + accid + ":" + reVal + "; ";
}
}
return(reStr);
}
/// <summary>
/// 查询车辆型号信息用于绑定下拉
/// </summary>
/// <returns></returns>
public List <Types> GetType(int bid)
{
return(DapperHelper.Query <Types>($"select * from Type where Tbid={bid}", null));
}