/// <summary>
/// 分组取交集
/// </summary>
/// <param name="mainSqlWhere"></param>
/// <param name="followSqlWhere"></param>
/// <returns></returns>
public SysShopActiveList GetGroupListContainSummary(int pageIndex, int pageSize, List <DapperWhere> mainSqlWhere, List <DapperWhere> followSqlWhere)
{
SysShopActiveList activeList = new SysShopActiveList();
pageIndex = pageIndex > 0 ? pageIndex : 1;
Dictionary <string, object> sqlParm = new Dictionary <string, object>();
#region 主要条件
string mainWhereSt = "";
foreach (DapperWhere item in mainSqlWhere)
{
if (mainWhereSt.Length > 0)
{
mainWhereSt += " and ";
}
mainWhereSt += item.Where;
sqlParm[item.ColumnName] = item.Value;
}
#endregion
#region 次要条件
string followWhereSt = "";
foreach (DapperWhere item in followSqlWhere)
{
if (followWhereSt.Length > 0)
{
followWhereSt += " and ";
}
followWhereSt += item.Where;
sqlParm[item.ColumnName] = item.Value;
}
#endregion
StringBuilder strSql = new StringBuilder();
strSql.Append(" select DISTINCT accid into #main from SysRpt_ShopActive ");
if (mainWhereSt.Length > 0)
{
strSql.Append(" where " + mainWhereSt);
}
strSql.Append(" ; ");
strSql.Append(" select DISTINCT accid into #follow from SysRpt_ShopActive ");
if (followWhereSt.Length > 0)
{
strSql.Append(" where " + followWhereSt);
}
strSql.Append(" ; ");
strSql.Append(" delete #main where #main.accid not in(select accid from #follow); ");
strSql.Append(" drop table #follow; ");
strSql.Append(" select top " + pageSize.ToString() + " accid from #main where accid not in(select top " + (pageSize * (pageIndex - 1)) + " accid from #main order by accid desc)order by accid desc; ");
strSql.Append(" select COUNT(*) countNum from #main; ");
strSql.Append(" drop table #main; ");
var dsJson = DapperHelper.QueryMultiple(strSql.ToString(), sqlParm);
activeList.rowCount = 0;
if (dsJson.Count > 0)
{
List <int> accountIdList = new List <int>();
foreach (dynamic item in dsJson[0].ToList())
{
accountIdList.Add(Convert.ToInt32(item.accid));
}
SysRpt_ShopInfoDAL shopDal = new SysRpt_ShopInfoDAL();
activeList.shopList = shopDal.GetAccountSummarize(accountIdList.ToArray());
}
if (dsJson.Count > 1)
{
activeList.rowCount = Convert.ToInt32(dsJson[1].ToList().First().countNum);
}
return(activeList);
}
public string GetLatestLogClient(int accId)
{
string client = "";
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 LogMode,OperDate from i200.dbo.T_LOG where Accountid=" + accId +
" order by OperDate desc");
dynamic Log = DapperHelper.GetModel <dynamic>(strSql.ToString());
strSql.Clear();
strSql.Append("select top 1 AppKey,createTime from i200.dbo.T_Token_Api where accId=" + accId +
" order by createTime desc");
dynamic App = DapperHelper.GetModel <dynamic>(strSql.ToString());
if (App != null && Log != null)
{
if (Convert.ToDateTime(Log.OperDate) > Convert.ToDateTime(App.createTime))
{
client = Log.LogMode.ToString();
if (client == "4")
{
client = App.AppKey.ToString();
}
}
else
{
client = App.AppKey.ToString();
}
}
else if (App == null)
{
client = Log.LogMode.ToString();
}
else
{
client = App.AppKey.ToString();
}
if (client == "0" || client == "1")
{
return("0");
}
else if (client.IndexOf('3') == 0)
{
return("8");
}
else if (client.IndexOf("Android") >= 0)
{
return("11");
}
else if (client.IndexOf("iPad") >= 0)
{
return("13");
}
else if (client.IndexOf("iPhone") >= 0)
{
return("10");
}
return("");
}
/// <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);
}
/// <summary>
/// 删除收货地址
/// </summary>
/// <param name="addressInfoId"></param>
/// <returns></returns>
public int DeleteAddressInfo(int addressInfoId)
{
string sql = $"delete from AddressInfo where AddressInfoId={addressInfoId}";
return(DapperHelper.Cud(sql));
}
/// <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="addressInfo"></param>
/// <returns></returns>
public int AddAddressInfo(AddressInfo addressInfo)
{
string sql = $"insert into AddressInfo values('{addressInfo.AddressInfoPeople}','{addressInfo.AddressInfoPhone}',{addressInfo.UserInfoIdOut},{addressInfo.PosstionInfoPIdOut},{addressInfo.PosstionInfoCIdOut},{addressInfo.PosstionInfoDIdOut},'{addressInfo.AddressInfoName}',{addressInfo.AddressInfoState})";
return(DapperHelper.Cud(sql));
}
/// <summary>
/// 全为不默认
/// </summary>
/// <param name="stateid"></param>
/// <returns></returns>
public int ModifyStateAddressInfo(int userInfoId)
{
string sql = $"update AddressInfo set AddressInfoState=0 where UserInfoIdOut = {userInfoId}";
return(DapperHelper.Cud(sql));
}
public Roles ShowById(int id)
{
string sql = "select * from Comments where id=@id";
return(DapperHelper <Roles> .Query(sql, new { id }).FirstOrDefault());
}
public int Update(Roles t)
{
string sql = "update Comments set Role_Name=@Role_Name";
return(DapperHelper <object> .Execut(sql, t));
}
public int Delete(int id)
{
string sql = "delete from Comments id=@id";
return(DapperHelper <object> .Execut(sql, new { id }));
}
public List <Roles> Show()
{
string sql = "select * from Comments";
return(DapperHelper <Roles> .Query(sql, null));
}
public int Create(Roles t)
{
string sql = "insert into Roles values(@Role_Name)";
return(DapperHelper <object> .Execut(sql, t));
}
/// <summary>
/// 分页得到列表
/// </summary>
/// <param name="pageIndex">显示页号</param>
/// <param name="pageSize">每页显示数</param>
/// <param name="dapperWheres">条件列表</param>
/// <param name="filedOrder">排序</param>
/// <returns>返回列表</returns>
public new Dictionary <string, string> GetList(int pageIndex, int pageSize, List <DapperWhere> dapperWheres, string filedOrder)
{
Dictionary <string, string> dataDic = new Dictionary <string, string>()
{
{ "dataList", "" },
{ "maxPage", "" },
{ "rowCount", "" }
};
StringBuilder strSqlCount = new StringBuilder();
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;
}
strSqlCount.Append(" select count(T_OutLink.id) from I200.dbo.T_OutLink ");
strSqlCount.Append(" left join Sys_Manage_User on T_OutLink.managerid=Sys_Manage_User.id ");
if (where.Length > 0)
{
strSqlCount.Append(" where " + where + " ; ");
}
int rowCount = DapperHelper.ExecuteScalar <int>(strSqlCount.ToString(), parm);
dataDic["rowCount"] = rowCount.ToString();
if (pageIndex < 1)
{
pageIndex = 1;
}
parm["pageSize"] = pageSize;
parm["pageIndex"] = pageIndex - 1;
if (rowCount % 15 != 0)
{
dataDic["maxPage"] = (rowCount / 15 + 1).ToString();
}
else
{
dataDic["maxPage"] = (rowCount / 15).ToString();
}
strSql.Append(" select top (@pageSize) T_OutLink.id,linkurl,remark,ClickCount,CreateTime,t_outlink.state,linkname,ShortUrl,name manageName,ot_name1 maxClassName,ot_name2 minClassName from I200.dbo.T_OutLink ");
strSql.Append(" left join Sys_Manage_User on T_OutLink.managerid=Sys_Manage_User.id ");
strSql.Append(" left outer join ( ");
strSql.Append(" select b.id id1,b.ot_name ot_name1,a.id id2,a.ot_name ot_name2 ");
strSql.Append(" from I200.dbo.T_outlinktype a ");
strSql.Append(" inner join ");
strSql.Append(" (select id,ot_name from I200.dbo.T_OutLinkType where ot_id=0) b ");
strSql.Append(" on b.id=a.ot_id) c on c.id2=T_OutLink.linktype where ");
if (where.Length > 0)
{
strSql.Append(" " + where + " and ");
}
strSql.Append(" T_OutLink.id not in ( ");
strSql.Append(" select top (@pageSize * @pageIndex) id ");
strSql.Append(" from I200.dbo.T_OutLink ");
if (where.Length > 0)
{
strSql.Append(" where " + where + " ");
}
strSql.Append(" order by createtime desc) order by createtime desc ");
dataDic["dataList"] =
CommonLib.Helper.JsonSerializeObject(DapperHelper.Query <T_OutLinkInfo>(strSql.ToString(), parm).ToList(), "yyyy-MM-dd HH:mm:ss");
return(dataDic);
}
/// <summary>
/// 根据当日时间生成新的活跃数据
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public ActiveStatus GenerateActiveModel(DateTime dt)
{
ActiveStatus model = new ActiveStatus();
StringBuilder strSql = new StringBuilder();
model.ShowDate = dt;
model.Time = dt;
strSql.Append("select active,COUNT(distinct accid) cnt from Sys_I200.dbo.SysRpt_ShopActive " +
"where startTime<=@dayDate and updatetime>=@dayDate " +
"group by active");
List <ActiveModel> modelData =
DapperHelper.Query <ActiveModel>(strSql.ToString(), new { dayDate = dt }).ToList();
foreach (var item in modelData)
{
switch (item.active)
{
case -3:
//流失用户
model.OutUsr = item.cnt;
break;
case -1:
//休眠用户
model.SleepUsr = item.cnt;
break;
case 1:
//新注册用户
model.NewReg = item.cnt;
break;
case 3:
//需关怀用户
model.RegAttention = item.cnt;
break;
case 4:
//流失需关怀用户
model.Attention = item.cnt;
break;
case 5:
//活跃用户
model.ActiveUsr = item.cnt;
break;
case 7:
//忠诚用户
model.FaithUsr = item.cnt;
break;
}
}
//获取当日注册人数
strSql.Clear();
strSql.Append("select count(*) from i200.dbo.T_Account where datediff(day,regtime,@dayDate)=0 and state=1;");
model.RegUsr = DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { dayDate = dt });
//获取当日登录人数
strSql.Clear();
strSql.Append(
"select COUNT(distinct Accountid) from i200.dbo.T_LOG where DATEDIFF(DAY,OperDate,@dayDate)=0;");
model.LoginUsr = DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { dayDate = dt });
strSql.Clear();
strSql.Append(
"select COUNT(distinct ID) from i200.dbo.T_Account where state=1 and regtime<=@dayDate;");
model.AllUsr = DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { dayDate = dt });
if (CheckShopActive(dt) == 0)
{
InsertShopActive(model);
}
else
{
UpdateTodayActive(model);
}
return(model);
}
/// <summary>
/// 获取收货地址
/// </summary>
/// <returns></returns>
public List <AddressInfo> GetAddressInfos(int userInfoId)
{
string sql = $"select * from AddressInfo where UserInfoIdOut = {userInfoId}";
return(DapperHelper.GetList <AddressInfo>(sql));
}
/// <summary>
/// 获得短信通道设置信息
/// </summary>
/// <returns></returns>
public SmsChannelInfo GetSysChannelInfo()
{
SmsConfigInfoTmp tmpResult = new SmsConfigInfoTmp();
SmsChannelInfo smsChannelInfo = new SmsChannelInfo();
StringBuilder strSql = new StringBuilder();
strSql.Append("declare @SendMode int;");
strSql.Append(" select @SendMode=Value from Sms_SysConfig where Item='sms_SendMode';");
strSql.Append(" if(@SendMode=1)");
strSql.Append(" begin");
strSql.Append(" SELECT @SendMode as sendmode,max(case when Item='sms_channel_sys' then Value end) as sms_channel_sys,max(case when Item='sms_channel_f' then Value end) as sms_channel_f,max(case when Item='sms_channel_s' then Value end) as sms_channel_s FROM Sms_SysConfig where Item='sms_channel_f' or Item='sms_channel_s' or Item='sms_channel_sys';");
strSql.Append(" end");
strSql.Append(" else");
strSql.Append(" begin");
strSql.Append(" SELECT @SendMode as sendmode,Value from Sms_SysConfig where Item='sms_option';");
strSql.Append(" end");
try
{
tmpResult = DapperHelper.Query <SmsConfigInfoTmp>(strSql.ToString()).ToList()[0];
}
catch (Exception)
{
tmpResult = null;
}
if (tmpResult != null)
{
if (tmpResult.sendmode != null)
{
smsChannelInfo.SendMode = tmpResult.sendmode;
if (smsChannelInfo.SendMode == 2)
{
//运营商绑定模式
if (tmpResult.Value != null)
{
string objVal = tmpResult.Value.ToString();
SmsChannel smsChannel = new SmsChannel();
smsChannel = Helper.JsonDeserializeObject <SmsChannel>(objVal.Trim());
smsChannelInfo.SmsOperator = smsChannel;
}
}
else
{
//通道绑定模式
SmsPriorityItem smsPriorityItem = new SmsPriorityItem();
SmsChannelUnit smsChannelUnitSys = new SmsChannelUnit();
SmsChannelUnit smsChannelUnitFast = new SmsChannelUnit();
SmsChannelUnit smsChannelUnitGroup = new SmsChannelUnit();
smsPriorityItem.SysPriority = smsChannelUnitSys;
smsPriorityItem.FastPriority = smsChannelUnitFast;
smsPriorityItem.GroupPriority = smsChannelUnitGroup;
smsPriorityItem.SysPriority.ChannelId = tmpResult.sms_channel_sys;
smsPriorityItem.FastPriority.ChannelId = tmpResult.sms_channel_f;
smsPriorityItem.GroupPriority.ChannelId = tmpResult.sms_channel_s;
smsChannelInfo.SmsPriority = smsPriorityItem;
}
}
}
return(smsChannelInfo);
}
/// <summary>
/// 根据主键查询地址
/// </summary>
/// <param name="AddressId"></param>
/// <returns></returns>
public List <AddressInfo> GetAddressInfosByAddressId(int AddressId)
{
string sql = $"select * from AddressInfo where AddressInfoId = {AddressId}";
return(DapperHelper.GetList <AddressInfo>(sql));
}
public string AddUserPortrait(P_Sys_UserPortraitModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(*) from P_Sys_UserPortrait where AccId=@accid;");
int count = DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { accid = model.AccId });
strSql.Clear();
if (count == 0)
{
strSql.Append("insert into P_Sys_UserPortrait (" +
" InsertTime," +
" AccId," +
" Industry," +
" UserSourcePortrait," +
" ChoiceReason," +
" CompetingProduct," +
" PotentialNeed," +
" Attitude," +
" BusinessExp," +
" PhoneNum," +
" QQNum," +
" AgeGrade," +
" Gender," +
" WebExpGrade," +
" SalesmanNum," +
" RealName," +
" Email," +
" Weixin," +
" IdCardNo," +
" InvoiceTitle," +
" ShopAddress," +
" BranchNum," +
" RemarkId,MainQuestion) values(" +
" @InsertTime," +
" @AccId," +
" @Industry," +
" @UserSourcePortrait," +
" @ChoiceReason," +
" @CompetingProduct," +
" @PotentialNeed," +
" @Attitude," +
" @BusinessExp," +
" @PhoneNum," +
" @QQNum," +
" @AgeGrade," +
" @Gender," +
" @WebExpGrade," +
" @SalesmanNum," +
" @RealName," +
" @Email," +
" @Weixin," +
" @IdCardNo," +
" @InvoiceTitle," +
" @ShopAddress," +
" @BranchNum," +
" @RemarkId,@MainQuestion);");
try
{
int reVal = DapperHelper.Execute(strSql.ToString(), new
{
InsertTime = DateTime.Now,
AccId = model.AccId,
Industry = model.Industry,
UserSourcePortrait = model.UserSourcePortrait,
ChoiceReason = model.ChoiceReason,
CompetingProduct = model.CompetingProduct,
PotentialNeed = model.PotentialNeed,
Attitude = model.Attitude,
BusinessExp = model.BusinessExp,
PhoneNum = model.PhoneNum,
QQNum = model.QQNum,
AgeGrade = model.AgeGrade,
Gender = model.Gender,
WebExpGrade = model.WebExpGrade,
SalesmanNum = model.SalesmanNum,
RealName = model.RealName,
Email = model.Email,
Weixin = model.Weinxin,
IdCardNo = model.IdCardNo,
InvoiceTitle = model.InvoiceTitle,
ShopAddress = model.ShopAddress,
BranchNum = model.BranchNum,
RemarkId = model.RemarkId,
MainQuestion = model.MainQuestion
});
if (reVal > 0)
{
return("");
}
else
{
return("err");
}
}
catch (Exception ex)
{
return("err");
}
}
else
{
strSql.Append("update P_Sys_UserPortrait set " +
" InsertTime=@insertTime," +
" Industry=@industry," +
" UserSourcePortrait=@userSource," +
" ChoiceReason=@choiceReason," +
" CompetingProduct=@competingProduct," +
" PotentialNeed=@potentialNeed," +
" Attitude=@attitude," +
" BusinessExp=@business," +
" PhoneNum=@phone," +
" QQNum=@qq," +
" AgeGrade=@ageGrade," +
" Gender=@gender," +
" WebExpGrade=@webExpGrade," +
" SalesmanNum=@salemanNum," +
" RealName=@RealName," +
" Email=@Email," +
" Weixin=@Weixin," +
" IdCardNo=@IdCardNo," +
" InvoiceTitle=@InvoiceTitle," +
" ShopAddress=@ShopAddress," +
" BranchNum=@BranchNum," +
" RemarkId=@remarkId,MainQuestion=@MainQuestion where AccId=@accid;");
try
{
int reVal = DapperHelper.Execute(strSql.ToString(), new
{
insertTime = DateTime.Now,
industry = model.Industry,
userSource = model.UserSourcePortrait,
choiceReason = model.ChoiceReason,
competingProduct = model.CompetingProduct,
potentialNeed = model.PotentialNeed,
attitude = model.Attitude,
business = model.BusinessExp,
phone = model.PhoneNum,
qq = model.QQNum,
ageGrade = model.AgeGrade,
gender = model.Gender,
webExpGrade = model.WebExpGrade,
salemanNum = model.SalesmanNum,
remarkId = model.RemarkId,
RealName = model.RealName,
Email = model.Email,
Weixin = model.Weinxin,
IdCardNo = model.IdCardNo,
InvoiceTitle = model.InvoiceTitle,
ShopAddress = model.ShopAddress,
BranchNum = model.BranchNum,
accid = model.AccId,
MainQuestion = model.MainQuestion
});
if (reVal > 0)
{
return("");
}
else
{
return("err");
}
}
catch (Exception ex)
{
return("err");
}
}
}
/// <summary>
/// 修改收货地址
/// </summary>
/// <param name="addressInfo"></param>
/// <returns></returns>
public int ModifyAddressInfo(AddressInfo addressInfo)
{
string sql = $"update AddressInfo set AddressInfoPeople='{addressInfo.AddressInfoPeople}',AddressInfoPhone='{addressInfo.AddressInfoPhone}',PosstionInfoPIdOut={addressInfo.PosstionInfoPIdOut},PosstionInfoCIdOut={addressInfo.PosstionInfoCIdOut},PosstionInfoDIdOut={addressInfo.PosstionInfoDIdOut},AddressInfoName='{addressInfo.AddressInfoName}',AddressInfoState={addressInfo.AddressInfoState} where AddressInfoId = {addressInfo.AddressInfoId}";
return(DapperHelper.Cud(sql));
}
/// <summary>
/// 发送批次汇总
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="stDate"></param>
/// <param name="edDate"></param>
/// <param name="batchId"></param>
/// <param name="remark"></param>
/// <param name="content"></param>
/// <param name="sourceType"></param>
/// <param name="channel"></param>
/// <returns></returns>
public Dictionary <string, object> GetBatchSummaryInfo(int pageIndex, string stDate, string edDate, string batchId,
string remark, int sourceType, int channel)
{
Dictionary <string, object> dicData = new Dictionary <string, object>()
{
{ "data", null },
{ "count", null }
};
StringBuilder strSql = new StringBuilder();
string strWhere = "";
List <BatchSummary> sumList = new List <BatchSummary>();
List <BatchSummary> reList = new List <BatchSummary>();
//页数计算
int bgNumber = ((pageIndex - 1) * 15) + 1;
int edNumber = (pageIndex) * 15;
strSql.Append("select * into #List from (");
strSql.Append("select *,ROW_NUMBER() over (order by id desc) rowNumber from MessageSending where 1=1 ");
if (Convert.ToDateTime(edDate) > Convert.ToDateTime(stDate))
{
if (stDate != "")
{
DateTime stTime = Convert.ToDateTime(stDate);
strWhere += " and CreateTime >='" + stTime.ToString("yyyy-MM-dd") + "' ";
}
if (edDate != "")
{
DateTime edTime = Convert.ToDateTime(edDate);
strWhere += " and CreateTime <'" + edTime.AddDays(1).Date.ToString("yyyy-MM-dd") + "' ";
}
}
else if ((Convert.ToDateTime(edDate) == Convert.ToDateTime(stDate)) && Convert.ToDateTime(stDate).ToShortDateString() != DateTime.Now.ToShortDateString())
{
DateTime time = Convert.ToDateTime(stDate);
strWhere += " and datediff(day,CreateTime,'" + time.Date.ToString("yyyy-MM-dd") + "')=0";
}
if (!string.IsNullOrEmpty(batchId))
{
strWhere += " and batchId like '%" + batchId + "%' ";
}
if (!string.IsNullOrEmpty(remark))
{
strWhere += " and Remark like '%" + remark + "%' ";
}
//if (!string.IsNullOrEmpty(content))
//{
// strWhere += " and Content like '%" + content + "%' ";
//}
//if (sourceType > 0)
//{
// strWhere += " and SourceType=" + sourceType;
//}
if (channel > 0)
{
strWhere += " and ChannelSet like '%" + channel + "%' ";
}
strSql.Append(strWhere);
strSql.Append(" ) t ");
strSql.Append(" where t.rowNumber between @bgNumber and @edNumber; ");
#region 处理合并Batch逻辑
StringBuilder strCondition = new StringBuilder();
strCondition.Append(strSql.ToString());
strCondition.Append("select BatchId from #List;");
List <string> batchList = DapperHelper.Query <string>(strCondition.ToString(), new
{
bgNumber = bgNumber,
edNumber = edNumber
}).ToList();
if (batchList != null && batchList.Count > 0)
{
foreach (var str in batchList)
{
sumList.Add(new BatchSummary(str));
}
}
else
{
return(null);
}
strCondition.Clear();
strCondition.Append(strSql.ToString());
strCondition.Append(
"select bat.BatchId,ChannelId,AllowNumber,FeedBackArrive,FeedBackOpen,bat.AccIdCount,bat.CreateTime,bat.Remark from #List left join MessageBatch bat on #List.BatchId=bat.BatchId;");
List <dynamic> detailList = DapperHelper.Query <dynamic>(strCondition.ToString(), new
{
bgNumber = bgNumber,
edNumber = edNumber
}).ToList();
foreach (var item in detailList)
{
BatchSummary summary = sumList.Find(x => x.BatchId == item.BatchId.ToString());
summary.AccIdCount = Convert.ToInt32(item.AccIdCount);
summary.CreateTime = Convert.ToDateTime(item.CreateTime);
summary.SendRemark = item.Remark.ToString();
string channelId = item.ChannelId.ToString();
switch (channelId)
{
case "1":
summary.SmsSend = Convert.ToInt32(item.AllowNumber);
summary.SmsArrive = Convert.ToInt32(item.FeedBackArrive);
summary.SmsOpen = Convert.ToInt32(item.FeedBackOpen);
if (summary.SmsSend != 0)
{
summary.SmsPartition = (Convert.ToDouble(summary.SmsOpen) / summary.SmsSend * 100).ToString("F2") + "%";
}
break;
case "2":
summary.WebSend = Convert.ToInt32(item.AllowNumber);
summary.WebArrive = Convert.ToInt32(item.FeedBackArrive);
summary.WebOpen = Convert.ToInt32(item.FeedBackOpen);
if (summary.WebSend != 0)
{
summary.WebPartition = (Convert.ToDouble(summary.WebOpen) / summary.WebSend * 100).ToString("F2") + "%";
}
break;
case "3":
summary.MobSend = Convert.ToInt32(item.AllowNumber);
summary.MobArrive = Convert.ToInt32(item.FeedBackArrive);
summary.MobOpen = Convert.ToInt32(item.FeedBackOpen);
if (summary.MobSend != 0)
{
summary.MobPartition = (Convert.ToDouble(summary.MobOpen) / summary.MobSend * 100).ToString("F2") + "%";
}
break;
case "4":
summary.EmailSend = Convert.ToInt32(item.AllowNumber);
summary.EmailArrive = Convert.ToInt32(item.FeedBackArrive);
summary.EmailOpen = Convert.ToInt32(item.FeedBackOpen);
if (summary.EmailSend != 0)
{
summary.EmailPartition = (Convert.ToDouble(summary.EmailOpen) / summary.EmailSend * 100).ToString("F2") + "%";
}
break;
}
reList.Add(summary);
}
#endregion
dicData["data"] = reList.Distinct();
dicData["count"] = GetSendingCount(strWhere);
return(dicData);
}
/// <summary>
/// 设置默认
/// </summary>
/// <param name="addressInfoId"></param>
/// <returns></returns>
public int ModifyDefaultAddressInfo(int addressInfoId)
{
string sql = $"update AddressInfo set AddressInfoState=1 where AddressInfoId = {addressInfoId}";
return(DapperHelper.Cud(sql));
}
/// <summary>
/// 获取分页详情列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="batchId"></param>
/// <param name="channelId"></param>
/// <returns></returns>
public Dictionary <string, object> GetBatchDetail(int pageIndex, string batchId, int channelId, int arrived = 0)
{
Dictionary <string, object> dicData = new Dictionary <string, object>()
{
{ "data", null },
{ "count", null }
};
StringBuilder strSql = new StringBuilder();
string strWhere = " where BatchId='" + batchId + "' and channelId=" + channelId;
if (arrived != 0)
{
strWhere += " and OpenMark=1";
}
//页数计算
int bgNumber = ((pageIndex - 1) * 15) + 1;
int edNumber = (pageIndex) * 15;
strSql.Append("select * into #List from MessageDetail ");
strSql.Append(strWhere);
strSql.Append(" select a.ID,a.CompanyName ,a.UserRealName ,f.PhoneNumber,f.UserEmail,a.RegTime, ");
strSql.Append(" case when b.aotjb=3 then '高级' when b.aotjb=2 then '标准' else '免费' end aotjb ");
strSql.Append(" ,b.endtime aotjbEndtime,c.userNum,c.goodsNum,c.saleNum,c.smsNum,b.dxunity,c.outlayNum,e.insertName returnInsertTime , ");
strSql.Append(" d.allCount ,d.userCount,a.LoginTimeWeb,LoginTimeLast, ");
strSql.Append(" case when c.active=1 then '新注册' when c.active=3 then '需关怀' when c.active=5 then '活跃' ");
strSql.Append(" when c.active=7 then '忠诚' when c.active=-1 then '休眠' when c.active=-3 then '流失' else '新注册' end active, ");
strSql.Append(" c.orderMoney ,g.AgentName into #ListAll from i200.dbo.T_Account a left outer join i200.dbo.T_Business b on a.ID=b.accountid ");
strSql.Append(" left outer join SysRpt_ShopInfo c on a.ID=c.accountid left outer join ( ");
strSql.Append(" select toAccId,COUNT(id) allCount,sum(case when useAccId IS null then 0 else 1 end) userCount from i200.dbo.T_Order_CouponList ");
strSql.Append(" where toAccId in(select AccId from #list) group by toAccId) d on a.ID=d.toAccId ");
strSql.Append(" left outer join( ");
strSql.Append(" select a.accid,a.insertName from Sys_VisitInfo a inner join( ");
strSql.Append(" select accid,MAX(insertTime) it from Sys_VisitInfo where ");
strSql.Append(" accid in(select AccId from #list) group by accid ) b on a.insertTime=b.it and a.accid=b.accid) e on a.ID=e.accid ");
strSql.Append(" left outer join ( ");
strSql.Append(" select accountid,PhoneNumber,UserEmail from i200.dbo.T_Account_User where grade='管理员') f on a.ID=f.accountid left outer join Sys_agent_mess g on a.AgentId=g.ID ");
strSql.Append(" where a.ID in( ");
strSql.Append(" select accid from #list ");
strSql.Append(" ); ");
strSql.Append("select * from (");
strSql.Append("select #List.ArriveMark,#List.OpenMark,#List.Remark,#List.CreateTime,#ListAll.*,ROW_NUMBER() over (order by #List.AccId desc) rowNumber from #List left join #ListAll on #List.AccId=#ListAll.ID ");
strSql.Append(" ) t ");
strSql.Append(" where t.rowNumber between @bgNumber and @edNumber; ");
strSql.Append(" drop table #List ");
strSql.Append(" drop table #ListAll ");
try
{
List <dynamic> list = DapperHelper.Query <dynamic>(strSql.ToString(), new
{
bgNumber = bgNumber,
edNumber = edNumber
}).ToList();
T_AccountDAL aDal = new T_AccountDAL();
//foreach (var item in list)
//{
// item.CompanyName = aDal.GetCompanyName(item.AccId);
//}
dicData["data"] = list;
dicData["count"] = GetDetailCount(strWhere);
return(dicData);
}
catch (Exception ex)
{
return(null);
}
}
public DxChartModel GetDataSource(DateTime stDate, DateTime edDate, string[] sourceType,
string[] conditions)
{
StringBuilder strSql = new StringBuilder();
List <dynamic> listSale = new List <dynamic>();
List <dynamic> listReg = new List <dynamic>();
List <dynamic> listGoods = new List <dynamic>();
DxChartModel chartModel = new DxChartModel();
//List<DxChartData> dataList = new List<DxChartData>();
DateTime iter = stDate;
while (iter < edDate)
{
chartModel.DataList.Add(new DxChartData(iter.ToShortDateString()));
iter = iter.AddDays(1);
}
foreach (string conds in conditions)
{
strSql.Clear();
switch (conds)
{
case "销售":
strSql.Append(
"select CAST(saleTime as date) opDate,COUNT(*) cnt,saleFlag from [i200].[dbo].[T_SaleInfo] " +
"where saleTime between @stDate and @edDate group by CAST(saleTime as date),saleFlag;");
listSale =
DapperHelper.Query <dynamic>(strSql.ToString(), new { stDate = stDate, edDate = edDate })
.ToList();
break;
case "商品":
strSql.Append(
"select CAST(gAddTime as date) opDate,COUNT(*) cnt,gFlag from [i200].[dbo].[T_GoodsInfo] " +
"where gAddTime between @stDate and @edDate group by CAST(gAddTime as date),gFlag;");
listGoods =
DapperHelper.Query <dynamic>(strSql.ToString(), new { stDate = stDate, edDate = edDate })
.ToList();
break;
case "会员":
strSql.Append(
"select CAST(uRegTime as date) opDate,COUNT(*) cnt,uFlag from [i200].[dbo].[T_UserInfo] " +
"where uRegTime between @stDate and @edDate group by CAST(uRegTime as date),uFlag;");
listReg =
DapperHelper.Query <dynamic>(strSql.ToString(), new { stDate = stDate, edDate = edDate })
.ToList();
break;
}
}
foreach (DxChartData day in chartModel.DataList)
{
List <dynamic> daySaleList = listSale.FindAll(x => x.opDate.ToShortDateString() == day.Date);
List <dynamic> dayGoodsList = listGoods.FindAll(x => x.opDate.ToShortDateString() == day.Date);
List <dynamic> dayRegList = listReg.FindAll(x => x.opDate.ToShortDateString() == day.Date);
if (daySaleList.Count > 0)
{
if (sourceType.Contains("IPHONE"))
{
day.Data.Add("IPHONE销售",
(daySaleList.Exists(x => x.saleFlag == 1)
? daySaleList.Find(x => x.saleFlag == 1).cnt
: 0));
}
if (sourceType.Contains("Android"))
{
day.Data.Add("Android销售",
(daySaleList.Exists(x => x.saleFlag == 2)
? daySaleList.Find(x => x.saleFlag == 2).cnt
: 0));
}
if (sourceType.Contains("Web"))
{
day.Data.Add("Web销售",
(daySaleList.Exists(x => x.saleFlag == 0)
? daySaleList.Find(x => x.saleFlag == 0).cnt
: 0));
}
if (sourceType.Contains("iPad"))
{
day.Data.Add("iPad销售",
(daySaleList.Exists(x => x.saleFlag == 3)
? daySaleList.Find(x => x.saleFlag == 3).cnt
: 0));
}
}
else if (conditions.Contains("销售"))
{
foreach (string str in sourceType)
{
day.Data.Add(str + "销售", 0);
}
}
if (dayGoodsList.Count > 0)
{
if (sourceType.Contains("IPHONE"))
{
day.Data.Add("IPHONE商品",
(dayGoodsList.Exists(x => x.gFlag == 1)
? dayGoodsList.Find(x => x.gFlag == 1).cnt
: 0));
}
if (sourceType.Contains("Android"))
{
day.Data.Add("Android商品",
(dayGoodsList.Exists(x => x.gFlag == 2)
? dayGoodsList.Find(x => x.gFlag == 2).cnt
: 0));
}
if (sourceType.Contains("Web"))
{
day.Data.Add("Web商品",
(dayGoodsList.Exists(x => x.gFlag == 0)
? dayGoodsList.Find(x => x.gFlag == 0).cnt
: 0));
}
if (sourceType.Contains("iPad"))
{
day.Data.Add("iPad商品",
(dayGoodsList.Exists(x => x.gFlag == 3)
? dayGoodsList.Find(x => x.gFlag == 3).cnt
: 0));
}
}
else if (conditions.Contains("商品"))
{
foreach (string str in sourceType)
{
day.Data.Add(str + "商品", 0);
}
}
if (dayRegList.Count > 0)
{
if (sourceType.Contains("IPHONE"))
{
day.Data.Add("IPHONE会员",
(dayRegList.Exists(x => x.uFlag == 1)
? dayRegList.Find(x => x.uFlag == 1).cnt
: 0));
}
if (sourceType.Contains("Android"))
{
day.Data.Add("Android会员",
(dayRegList.Exists(x => x.uFlag == 2)
? dayRegList.Find(x => x.uFlag == 2).cnt
: 0));
}
if (sourceType.Contains("Web"))
{
day.Data.Add("Web会员",
(dayRegList.Exists(x => x.uFlag == 0)
? dayRegList.Find(x => x.uFlag == 0).cnt
: 0));
}
if (sourceType.Contains("iPad"))
{
day.Data.Add("iPad会员",
(dayRegList.Exists(x => x.uFlag == 3)
? dayRegList.Find(x => x.uFlag == 3).cnt
: 0));
}
}
else if (conditions.Contains("会员"))
{
foreach (string str in sourceType)
{
day.Data.Add(str + "会员", 0);
}
}
}
return(chartModel);
}
/// <summary>
/// 获取批次列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="stDate"></param>
/// <param name="edDate"></param>
/// <param name="batchId"></param>
/// <param name="remark"></param>
/// <param name="content"></param>
/// <param name="sourceType"></param>
/// <param name="channel"></param>
/// <returns></returns>
public Dictionary <string, object> GetBatchList(int pageIndex, string stDate, string edDate, string batchId, string remark, string content, int sourceType, int channel)
{
Dictionary <string, object> dicData = new Dictionary <string, object>()
{
{ "data", null },
{ "count", null }
};
StringBuilder strSql = new StringBuilder();
string strWhere = "";
//页数计算
int bgNumber = ((pageIndex - 1) * 15) + 1;
int edNumber = (pageIndex) * 15;
strSql.Append("select * from (");
strSql.Append("select *,ROW_NUMBER() over (order by id desc) rowNumber from MessageBatch where 1=1 ");
if (Convert.ToDateTime(edDate) > Convert.ToDateTime(stDate))
{
if (stDate != "")
{
DateTime stTime = Convert.ToDateTime(stDate);
strWhere += " and CreateTime >='" + stTime.ToString("yyyy-MM-dd") + "' ";
}
if (edDate != "")
{
DateTime edTime = Convert.ToDateTime(edDate);
strWhere += " and CreateTime <'" + edTime.AddDays(1).Date.ToString("yyyy-MM-dd") + "' ";
}
}
else if ((Convert.ToDateTime(edDate) == Convert.ToDateTime(stDate)) && Convert.ToDateTime(stDate).ToShortDateString() != DateTime.Now.ToShortDateString())
{
DateTime time = Convert.ToDateTime(stDate);
strWhere += " and datediff(day,CreateTime,'" + time.Date.ToString("yyyy-MM-dd") + "')=0";
}
if (!string.IsNullOrEmpty(batchId))
{
strWhere += " and batchId like '%" + batchId + "%' ";
}
if (!string.IsNullOrEmpty(remark))
{
strWhere += " and Remark like '%" + remark + "%' ";
}
if (!string.IsNullOrEmpty(content))
{
strWhere += " and Content like '%" + content + "%' ";
}
if (sourceType > 0)
{
strWhere += " and SourceType=" + sourceType;
}
if (channel > 0)
{
strWhere += " and ChannelId=" + channel;
}
strSql.Append(strWhere);
strSql.Append(" ) t ");
strSql.Append(" where t.rowNumber between @bgNumber and @edNumber; ");
try
{
List <MessageBatch> list = DapperHelper.Query <MessageBatch>(strSql.ToString(), new
{
bgNumber = bgNumber,
edNumber = edNumber
}).ToList();
dicData["data"] = list;
dicData["count"] = GetPageCount(strWhere);
return(dicData);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 根据ID得到店铺汇总信息 通用版本
/// </summary>
/// <param name="accids"></param>
public List <SysShopSummarizeInfo> GetAccountSummarize(int[] accids, string order = "", int pageIndex = 1)
{
List <SysShopSummarizeInfo> shopInfo = new List <SysShopSummarizeInfo>();
string accidStr = "";
foreach (int a in accids)
{
accidStr += "," + a.ToString();
}
if (accidStr.Length > 0)
{
StringBuilder strSql = new StringBuilder();
if (string.IsNullOrEmpty(order))
{
strSql.Append(" create table #accountList(accid int); ");
strSql.Append(" insert into #accountList(accid) ");
strSql.Append(" select id from I200.dbo.T_Account where ID in(0" + accidStr + ") and State=1 ");
strSql.Append(" select ");
strSql.Append(" a.ID,a.CompanyName,a.RegTime,c.active,b.aotjb ");
strSql.Append(" ,b.endtime aotjbEndTime,b.dxunity, ");
strSql.Append(" c.userNum,c.goodsNum,c.saleNum ,c.smsNum ,c.outlayNum , ");
strSql.Append(" d.allCount ,d.userCount ,a.LoginTimeWeb ,LoginTimeLast,c.orderMoney ");
strSql.Append(" from i200.dbo.T_Account a left outer join i200.dbo.T_Business b on a.ID=b.accountid ");
strSql.Append(" left outer join SysRpt_ShopInfo c on a.ID=c.accountid left outer join ( ");
strSql.Append(" select toAccId,COUNT(id) allCount,sum(case when useAccId IS null then 0 else 1 end) userCount ");
strSql.Append(" from i200.dbo.T_Order_CouponList where toAccId in(select accid from #accountList) group by toAccId) d on a.ID=d.toAccId ");
strSql.Append(" where a.ID in(select accid from #accountList) order by a.RegTime desc");
strSql.Append(" drop table #accountList; ");
shopInfo = DapperHelper.Query <SysShopSummarizeInfo>(strSql.ToString()).ToList();
}
else
{
int bgNumber = ((pageIndex - 1) * 15) + 1;
int edNumber = (pageIndex) * 15;
if (order == "active")
{
order = "c." + order;
}
strSql.Append(" create table #accountList(accid int); ");
strSql.Append(" insert into #accountList(accid) ");
strSql.Append(" select id from I200.dbo.T_Account where ID in(0" + accidStr + ") and State=1 ");
strSql.Append(" select * from (");
strSql.Append(" select row_number() over (order by " + order + " desc) rowNumber,");
strSql.Append(" a.ID,a.CompanyName,a.RegTime,c.active,b.aotjb ");
strSql.Append(" ,b.endtime aotjbEndTime,b.dxunity, ");
strSql.Append(" c.userNum,c.goodsNum,c.saleNum ,c.smsNum ,c.outlayNum , ");
strSql.Append(" d.allCount ,d.userCount ,a.LoginTimeWeb ,LoginTimeLast,c.orderMoney ");
strSql.Append(" from i200.dbo.T_Account a left outer join i200.dbo.T_Business b on a.ID=b.accountid ");
strSql.Append(" left outer join SysRpt_ShopInfo c on a.ID=c.accountid left outer join ( ");
strSql.Append(" select toAccId,COUNT(id) allCount,sum(case when useAccId IS null then 0 else 1 end) userCount ");
strSql.Append(" from i200.dbo.T_Order_CouponList where toAccId in(select accid from #accountList) group by toAccId) d on a.ID=d.toAccId ");
strSql.Append(" where a.ID in(select accid from #accountList)) t where t.rowNumber between @bgNumber and @edNumber order by t.rowNumber;");
strSql.Append(" drop table #accountList; ");
shopInfo =
DapperHelper.Query <SysShopSummarizeInfo>(strSql.ToString(),
new { bgNumber = bgNumber, edNumber = edNumber }).ToList();
}
foreach (SysShopSummarizeInfo model in shopInfo)
{
model.aotjbName = Enum.GetName(typeof(Model.Enum.AccountEnum.StoreVer), model.aotjb);
model.activeName = Enum.GetName(typeof(Model.Enum.AccountEnum.ActiveStatus), model.active);
strSql.Clear();
strSql.Append(
"select COUNT(*) from SysRpt_ShopActive where active in (select active from SysRpt_ShopActive where accid=@AccId and stateVal=0 and active=5) and accid=@AccId;");
int activeCnt = DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { AccId = model.ID });
strSql.Clear();
strSql.Append(
"select COUNT(*) from SysRpt_ShopActive where active in (select active from SysRpt_ShopActive where accid=@AccId and stateVal=0 and active=-3) and accid=@AccId;");
int lostCnt = DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { AccId = model.ID });
if (activeCnt == 1)
{
model.FirstActive = true;
}
else
{
model.FirstActive = false;
}
if (lostCnt == 1)
{
model.FirstLost = true;
}
else
{
model.FirstLost = false;
}
}
}
//shopInfo.OrderByDescending(x => x.RegTime);
return(shopInfo);
}
/// <summary>
/// 获取该用户的失效的优惠券
/// </summary>
/// <param name="UserId"></param>
/// <returns></returns>
public List <DisCountInfo> GetEndDisCountInfosById(int UserId)
{
string sql = $"select c.DisCountInfoPrice,a.StartTime,a.EndTime,a.DisCountAndUserId from DisCountAndUser a join UserInfo b on a.UserIdOut = b.UserInfoId join DisCountInfo c on a.DisCountId = c.DisCountInfoId where b.UserInfoId = 2 and a.EndTime <= '{DateTime.Now}'";
return(DapperHelper.GetList <DisCountInfo>(sql));
}
/// <summary>
/// 获得归属地店铺信息
/// </summary>
/// <param name="areaName">归属地名称</param>
/// <param name="bgDate">开始日期</param>
/// <param name="edDate">结束日期</param>
/// <param name="iPage">页数</param>
/// <returns></returns>
public Dictionary <string, object> GetAreaShopInfoEx(string areaName, DateTime bgDate, DateTime edDate, int iPage, int pageSize)
{
Dictionary <string, object> list = new Dictionary <string, object>();
areaName = areaName.Trim('市');
areaName = areaName.Trim('省');
if (pageSize < 1)
{
pageSize = 20;
}
if (iPage < 1)
{
iPage = 1;
}
List <AreaShopInfoEx> areaShopInfoList = new List <AreaShopInfoEx>();
StringBuilder strSql = new StringBuilder();
strSql.Append(" select accid into #list from Sys_Account where accid in(select id from i200.dbo.T_Account where ");
strSql.Append(" RegTime between @bgDate and @edDate and State=1) and sysAddress like '%'+ @AreaName +'%'; ");
strSql.Append(" select count(distinct accid) as accNum from #list; ");
strSql.Append(" select * from (select row_number() over(order by accountid desc) as rowNumer,accountid,I200.dbo.T_Account.RegTime,I200.dbo.T_Account.CompanyName, ");
strSql.Append(" max(allLoginNum) as LoginNum,max(lastLoginTime) as lastLoginTime ");
strSql.Append(" from SysRpt_ShopDayInfo left outer join I200.dbo.T_Account on I200.dbo.T_Account.ID=SysRpt_ShopDayInfo.accountid ");
strSql.Append(" where SysRpt_ShopDayInfo.accountid in(select accid from #list) group by accountid,I200.dbo.T_Account.RegTime,I200.dbo.T_Account.CompanyName ");
strSql.Append(" ) List ");
strSql.Append(" where List.rowNumer between @bgNumber and @edNumber ");
strSql.Append(" order by LoginNum desc ");
strSql.Append(" drop table #list ");
//页数计算
int bgNumber = ((iPage - 1) * pageSize) + 1;
int edNumber = (iPage) * pageSize;
var dataList = DapperHelper.QueryMultiple(strSql.ToString(), new {
AreaName = areaName,
bgDate = bgDate,
edDate = edDate,
bgNumber = bgNumber,
edNumber = edNumber
});
if (dataList != null && dataList.Count > 0)
{
var maxCntList = dataList[0].ToList();
int rowCount = Convert.ToInt32(maxCntList[0].accNum);
int maxPage = 0;
if (rowCount > 0)
{
maxPage = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(rowCount) / pageSize));
}
foreach (dynamic dr in dataList[1].ToList())
{
AreaShopInfoEx areaShopInfo = new AreaShopInfoEx();
areaShopInfo.accID = int.Parse(dr.accountid.ToString());
areaShopInfo.accName = dr.CompanyName.ToString();
areaShopInfo.regTime = DateTime.Parse(dr.RegTime.ToString());
areaShopInfo.loginTimes = int.Parse(dr.LoginNum.ToString());
areaShopInfo.lastLoginTime = DateTime.Parse(dr.lastLoginTime.ToString());
areaShopInfoList.Add(areaShopInfo);
}
list["rowCount"] = rowCount;
list["maxPage"] = maxPage;
list["pageIndex"] = iPage;
list["listData"] = areaShopInfoList;
}
return(list);
}
/// <summary>
/// 删除过期优惠券
/// </summary>
/// <param name="DisAndUserId"></param>
/// <returns></returns>
public int DeleteDisCount(int DisAndUserId)
{
string sql = $"delete from DisCountAndUser where DisCountAndUserId = {DisAndUserId}";
return(DapperHelper.Cud(sql));
}
public List <ShopLocationModel> GetShopAround(string lng, string lat)
{
StringBuilder strSql = new StringBuilder();
double lngD = Convert.ToDouble(lng);
double latD = Convert.ToDouble(lat);
strSql.Append("select ap.AccId,ap.PredLng Longitude,ap.PredLat Latitude,ta.CompanyName,ta.UserRealName,ta.PhoneNumber,ta.CompanyAddress,tb.active as ActiveStatus from Sys_AddressPrecision ap " +
"left join i200.dbo.T_Account ta on ap.AccId=ta.ID " +
"left join i200.dbo.T_Business tb on ap.AccId=tb.accountid " +
"where ap.PredLat is not null " +
"and CAST(ap.PredLng as decimal(18,6))-@lngD<0.1 and CAST(ap.PredLng as decimal(18,6))-@lngD>-0.1 " +
"and CAST(ap.PredLat as decimal(18,6))-@latD<0.1 and CAST(ap.PredLat as decimal(18,6))-@latD>-0.1 " +
"and ta.LoginTimeWeb>5;");
try
{
//获取符合条件的店铺
List <ShopLocationModel> model =
DapperHelper.Query <ShopLocationModel>(strSql.ToString(), new { lngD = lngD, latD = latD }).ToList();
return(model);
}
catch (Exception ex)
{
return(null);
}
//string where = "where ";
//int count = 0;
//string[] strList = location.Split(new char[] {'省', '市', '区', '路'});
//foreach (string str in strList)
//{
// if (str != "")
// {
// count++;
// if (location.Contains("北京") || location.Contains("天津") || location.Contains("上海") || location.Contains("重庆"))
// {
// if (count < 2)
// {
// where += " companyaddress like '%" + str + "%' or ";
// }
// }
// else
// {
// if (count < 3)
// {
// where += " companyaddress like '%" + str + "%' and ";
// }
// }
// }
//}
//where = (location.Contains("北京") || location.Contains("天津") || location.Contains("上海") || location.Contains("重庆"))
// ? where.Substring(0, where.LastIndexOf('o'))
// : where.Substring(0, where.LastIndexOf('a'));
//List<MobileMapModel> list = SeperateModels(where);
//List<MobileMapModel> listEx = list.Distinct(new ModelComparer()).ToList();
//return listEx.OrderByDescending(x=>x.picUrl).Take(15).ToList();
}
/// <summary>
/// 更新规则详情列表
/// </summary>
/// <param name="list"></param>
/// <param name="type"></param>
/// <param name="verifId"></param>
/// <returns></returns>
public int AddNewRule(List <FilterCondition> list, int type, string verifId)
{
StringBuilder strSql = new StringBuilder();
if (list != null && list.Count > 0)
{
foreach (var conItem in list)
{
switch (conItem.ConditionType)
{
case "TimePair":
if (string.IsNullOrEmpty(conItem.DataRange.Max.ToString()) || conItem.DataRange.Max.ToString() == "null")
{
conItem.DataRange.Max = "null";
}
else
{
conItem.DataRange.Max = Convert.ToDateTime(conItem.DataRange.Max);
}
if (string.IsNullOrEmpty(conItem.DataRange.Min.ToString()) || conItem.DataRange.Min.ToString() == "null")
{
conItem.DataRange.Min = "null";
}
else
{
conItem.DataRange.Min = Convert.ToDateTime(conItem.DataRange.Min);
}
strSql.Append(
"insert into Sys_RuleList (VerifId,RuleId,MaxValue,MinValue,RangeData,Remark) " +
"Values ('" + verifId + "'," + conItem.Id + ",'" +
conItem.DataRange.Max + "','" +
conItem.DataRange.Min +
"',null,'" + conItem.Remark + "');");
break;
case "StrPair":
case "IntPair":
if (string.IsNullOrEmpty(conItem.DataRange.Max.ToString()))
{
conItem.DataRange.Max = "null";
}
if (string.IsNullOrEmpty(conItem.DataRange.Min.ToString()))
{
conItem.DataRange.Min = "null";
}
strSql.Append("insert into Sys_RuleList (VerifId,RuleId,MaxValue,MinValue,RangeData,Remark) " +
"Values ('" + verifId + "'," + conItem.Id + "," +
conItem.DataRange.Max.ToString() + "," + conItem.DataRange.Min.ToString() +
",null,'" + conItem.Remark + "');");
break;
case "IntRange":
case "StrRange":
case "SpecRange":
string intList = "";
foreach (var intItem in conItem.DataRange.Range)
{
intList += intItem + ",";
}
intList = intList.Substring(0, intList.LastIndexOf(','));
strSql.Append("insert into Sys_RuleList (VerifId,RuleId,MaxValue,MinValue,RangeData,Remark) " +
"Values ('" + verifId + "'," + conItem.Id + ",null,null,'" + intList + "','" + conItem.Remark + "');");
break;
default:
break;
}
}
try
{
return(DapperHelper.Execute(strSql.ToString()));
}
catch (Exception ex)
{
Logger.Error("插入规则详情表出错！", ex);
return(0);
}
}
else
{
return(0);
}
}