/// <summary>
/// 新增KPI数据
/// </summary>
/// <param name="kpiModel"></param>
/// <returns></returns>
public int SetNewKPI(MonthlyKPI kpiModel)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" declare @count int; ");
strSql.Append(" select @count=COUNT(*) from Sys_PresetKPI where MDate=@MDate and DelStatus=0; ");
strSql.Append(" if(isnull(@count,0)>0) ");
strSql.Append(" begin ");
strSql.Append(" update Sys_PresetKPI set RegNum = @RegNum, SellCount = @SellCount , ");
strSql.Append(" UsrAdd = @UsrAdd ,Sku = @Sku ,Sms = @Sms ,OrderCount = @OrderCount where MDate=@MDate; ");
strSql.Append(" select 2; ");
strSql.Append(" end ");
strSql.Append(" else ");
strSql.Append(" begin ");
strSql.Append(" insert into Sys_PresetKPI(MDate,RegNum,SellCount,UsrAdd,Sku,Sms,OrderCount) ");
strSql.Append(" values (@MDate,@RegNum,@SellCount,@UsrAdd,@Sku,@Sms,@OrderCount) ; ");
strSql.Append(" select 1; ");
strSql.Append(" end ");
object id = DapperHelper.ExecuteScalar(strSql.ToString(), kpiModel);
if (id != null)
{
return(Convert.ToInt32(id));
}
else
{
return(0);
}
}
/// <summary>
/// 增加标签
/// </summary>
/// <param name="tagName"></param>
/// <param name="insertName"></param>
/// <returns></returns>
public new int Add(string tagName, string insertName)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" declare @tagid int; ");
strSql.Append(" select @tagid=id from Sys_TagInfo where t_Name=@tageName; ");
strSql.Append(" if(ISNULL(@tagid,0)=0) ");
strSql.Append(" begin ");
strSql.Append(" insert into Sys_TagInfo(t_Name,insertName,tagStatus) ");
strSql.Append(" values(@tageName,@insertName,1); ");
strSql.Append(" select @@IDENTITY; ");
strSql.Append(" end ");
strSql.Append(" else ");
strSql.Append(" select @tagid; ");
object r = DapperHelper.ExecuteScalar(strSql.ToString(), new
{
tageName = tagName,
insertName = insertName
});
if (r != null)
{
return(Convert.ToInt32(r));
}
else
{
return(0);
}
}
/// <summary>
/// 添加新的需求Model
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int AddNewModel(RequirementManage model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(
"insert into RequirementManage (CategoryId,RequirementType,Description,Status,CreateTime,Operator,AccId,RefId,OriginDesc,Terminal,UserVal,Difficult) " +
"Values(@CategoryId,@RequirementType,@Description,@Status,@CreateTime,@Operator,@AccId,@RefId,@OriginDesc,@Terminal,@UserVal,@Difficult);" +
"select @@IDENTITY;");
try
{
return(DapperHelper.ExecuteScalar <int>(strSql.ToString(), new
{
CategoryId = model.CategoryId,
RequirementType = model.RequirementType,
Description = model.Description,
Status = model.Status,
CreateTime = DateTime.Now,
Operator = model.Operator,
AccId = model.AccId,
RefId = model.RefId,
OriginDesc = model.OriginDesc,
Terminal = model.Terminal,
UserVal = model.UserVal,
Difficult = model.Difficult
}));
}
catch (Exception ex)
{
Logger.Error("新增需求信息出错！", ex);
return(0);
}
}
/// <summary>
/// 根据busId和订单类型获取产品名称
/// </summary>
/// <param name="busId"></param>
/// <param name="orderTypeId">订单类型
/// 1-Saas订单
/// 2-实物订单
/// 3-京东订单
/// 4-话费订单
/// </param>
/// <returns></returns>
public string GetProductName(int busId, int orderTypeId)
{
StringBuilder strSql = new StringBuilder();
switch (orderTypeId)
{
case 1:
case 4:
strSql.Append("select displayName from [i200].[dbo].[T_Order_Project] where busId=@busId;");
break;
case 2:
strSql.Append("select AliasName from [i200].[dbo].[T_MaterialGoods] where GoodsId=@busId;");
break;
case 3:
return("京东订单");
}
try
{
return(DapperHelper.ExecuteScalar <string>(strSql.ToString(), new { busId = busId }));
}
catch (Exception ex)
{
Logger.Error("退货时获取产品名称出错！", ex);
return("");
}
}
/// <summary>
/// 根据列名更新信息
/// </summary>
/// <param name="accid">店铺ID</param>
/// <param name="Column">列名</param>
/// <param name="value">值</param>
/// <returns></returns>
public bool UpdateColumn(int accid, string Column, object value)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" if(exists(select * from Sys_Account where accid=@accid)) ");
strSql.Append(" begin ");
strSql.Append(" update Sys_Account set "+ Column + "=@value where accid=@accid; ");
strSql.Append(" select 1; ");
strSql.Append(" end ");
strSql.Append(" else ");
strSql.Append(" begin ");
strSql.Append(" insert into Sys_Account (accid,"+ Column + ")values(@accid,@value); ");
strSql.Append(" select 1; ");
strSql.Append(" end ");
object rid = DapperHelper.ExecuteScalar(strSql.ToString(), new { value = value, accid = accid });
if (rid != null)
{
return(true);
}
else
{
return(false);
}
}
public int AddModel(Sys_TaskDailyInfo model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into Sys_TaskDaily(");
strSql.Append("insetName,dt_remark,dt_logUid,dt_Source,accountid,t_mk,dt_Time,dt_Level,dt_Status,inertTime");
strSql.Append(") values (");
strSql.Append("@insetName,@dt_remark,@dt_logUid,@dt_Source,@accountid,@t_mk,@dt_Time,@dt_Level,@dt_Status,@inertTime");
strSql.Append(") ");
strSql.Append(";select @@IDENTITY");
return(DapperHelper.ExecuteScalar <int>(strSql.ToString(), new
{
insetName = model.insetName,
dt_remark = model.dt_remark,
dt_logUid = model.dt_logUid,
dt_Source = model.dt_Source,
accountid = model.accountid,
t_mk = model.t_mk,
dt_Time = model.dt_Time,
dt_Level = model.dt_Level,
dt_Status = model.dt_Status,
inertTime = model.inertTime
}));
}
public int UpdateExpressAddress(int oid, int accId, string expressCompany, string expressCode)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ReceivingAddressId from [i200].[dbo].[T_OrderInfo] where oid=@oid and accId=@accId;");
int receiveId = DapperHelper.ExecuteScalar <int>(strSql.ToString(), new
{
oid = oid,
accId = accId
});
strSql.Clear();
strSql.Append(
"update [i200].[dbo].[T_Receiving_Address] set ExpressCompany=@expressCompany,ExpressCode=@expressCode where AddressId=@addressId;");
try
{
int reVal = HelperForFrontend.Execute(strSql.ToString(), new
{
expressCompany = expressCompany,
expressCode = expressCode,
addressId = receiveId
});
return(reVal);
}
catch (Exception ex)
{
Logger.Error("更新实物商品物流信息出错！", ex);
return(0);
}
}
/// <summary>
/// 初始化用户问题信息
/// </summary>
/// <param name="accId"></param>
/// <returns></returns>
public void InitialUserQuestion(int accId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("if exists (select * from Sys_Visit_Question_List where AccId=@accid) ");
strSql.Append(" begin ");
strSql.Append(" select 1; ");
strSql.Append(" end ");
strSql.Append(" else ");
strSql.Append(" begin ");
strSql.Append(" select 0 ");
//strSql.Append(" select @@IDENTITY; ");
strSql.Append(" end");
int mark = DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { accid = accId });
if (mark == 0)
{
strSql.Clear();
strSql.Append("select * from Sys_Visit_Question where Status=1;");
List <Sys_Visit_Question> list = DapperHelper.Query <Sys_Visit_Question>(strSql.ToString()).ToList();
strSql.Clear();
foreach (var item in list)
{
strSql.Append("insert into Sys_Visit_Question_List (AccId,Qid,Reply) Values (@accid," + item.Id +
",'');");
}
DapperHelper.Execute(strSql.ToString(), new { accid = accId });
}
}
/// <summary>
/// 增加新的关系
/// </summary>
/// <param name="visitId"></param>
/// <param name="tagId"></param>
/// <param name="insertName"></param>
/// <returns></returns>
public int Add(int visitId, int tagId, string insertName)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" declare @nid int; ");
strSql.Append(" select @nid=id from Sys_VisitTagNexus where tid=@tid and vid=@vid; ");
strSql.Append(" if(ISNULL(@nid,0)>0) ");
strSql.Append(" begin ");
strSql.Append(" select @nid; ");
strSql.Append(" end ");
strSql.Append(" else ");
strSql.Append(" begin ");
strSql.Append(" insert into Sys_VisitTagNexus(tid,vid,insertName,insertTime) ");
strSql.Append(" values(@tid,@vid,@iname,GETDATE()); ");
strSql.Append(" select @@IDENTITY; ");
strSql.Append(" end ");
object rl = DapperHelper.ExecuteScalar(strSql.ToString(), new { tid = tagId, vid = visitId, iname = insertName });
if (rl != null)
{
return(Convert.ToInt32(rl));
}
else
{
return(0);
}
}
/// <summary>
/// 得到总数
/// </summary>
/// <param name="dapperWheres">条件列表</param>
/// <returns></returns>
public int GetCount(List <DapperWhere> dapperWheres)
{
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;
}
StringBuilder strSql = new StringBuilder();
strSql.Append(" select count(*) from Sys_DailyCheckRecord ");
if (where.Length > 0)
{
strSql.Append(" where " + where);
}
object obj = DapperHelper.ExecuteScalar(strSql.ToString(), parm);
if (obj != null)
{
return(Convert.ToInt32(obj));
}
else
{
return(0);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public new int Add(Sys_TagNexus model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("if(exists(select * from Sys_TagNexus where acc_id=@acc_id and tag_id=@tag_id))");
strSql.Append(" begin ");
strSql.Append(" select -1; ");
strSql.Append(" end ");
strSql.Append(" else ");
strSql.Append(" begin ");
strSql.Append("insert into Sys_TagNexus(");
strSql.Append("tag_id,acc_id,insertName,insertTime");
strSql.Append(") values (");
strSql.Append("@tag_id,@acc_id,@insertName,@insertTime");
strSql.Append("); ");
strSql.Append("select @@IDENTITY;");
strSql.Append(" end ");
object obj = DapperHelper.ExecuteScalar(strSql.ToString(), model);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
public string ModifyAgentInfo(AgentModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select AgentPassword from Sys_I200.dbo.Sys_agent_mess where id=@id;");
string pwd = DapperHelper.ExecuteScalar <string>(strSql.ToString(), new { id = model.ID });
strSql.Clear();
if (model.AgentPassword != pwd)
{
model.AgentPassword = CommonLib.Helper.Md5Hash(model.AgentPassword);
}
strSql.Append("update Sys_I200.dbo.Sys_agent_mess set " +
" AgentPassword=@pwd," +
" AgentName=@name," +
" AgentGrade=@grade," +
" AgentPhone=@phone," +
" AgentAddress=@address," +
" AgentIdCard=@idCard," +
" AgentNumber=@number," +
" AgentEmail=@email," +
" AgentQQ=@qq," +
" Remark=@remark ," +
" ServiceType=@serviceType " +
" where id=@id");
try
{
int reVal = DapperHelper.Execute(strSql.ToString(), new
{
pwd = model.AgentPassword,
name = model.AgentName,
grade = model.AgentGrade,
phone = model.AgentPhone,
address = model.AgentAddress,
idCard = model.AgentIdCard,
number = model.AgentNumber,
email = model.AgentEmail,
qq = model.AgentQQ,
remark = model.Remark,
serviceType = model.ServiceType,
id = model.ID
});
if (reVal > 0)
{
return("1");
}
else
{
return("0");
}
}
catch (Exception ex)
{
return("0");
}
}
/// <summary>
/// 获取最近一次某渠道更新时间
/// </summary>
/// <param name="channelId">
/// 1-短信
/// 2-邮件
/// 3-移动端
/// 4-PC站内
/// </param>
/// <returns></returns>
public DateTime GetLastUpdateTime(int channelId, int accid)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 PushTime from Sys_UserMessage " +
"where ChannelId=@channelId and Accid=@accid " +
"order by PushTime desc;");
try
{
string dtStr = DapperHelper.ExecuteScalar <string>(strSql.ToString(), new { channelId = channelId, accid = accid });
if (!string.IsNullOrEmpty(dtStr))
{
return(Convert.ToDateTime(dtStr));
}
else
{
return(Convert.ToDateTime("2010-1-1 00:00:00"));
}
}
catch (Exception ex)
{
Logger.Error("获取用户推送信息汇总表最近一次更新时间出错！", ex);
return(DateTime.MinValue);
}
}
public int GetAccIdBybbsId(int bbsId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID from i200.dbo.T_Account where BBS_Uid=@bbsId;");
return(DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { bbsId = bbsId }));
}
/// <summary>
/// 获取维客短信发送条数
/// </summary>
/// <param name="accId"></param>
/// <returns></returns>
public int GetFreeSmsCnt(int accId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select sum(realCnt) as Cnt from [i200].[dbo].[T_Sms_List] where isFree=1 and accID=@accId;");
return(DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { accId = accId }));
}
/// <summary>
/// 获取充值记录表中最近的一个订单Id
/// </summary>
/// <returns></returns>
public int GetLastOid()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 oid from Sys_RechargeRecord order by oid desc;");
return(DapperHelper.ExecuteScalar <int>(strSql.ToString()));
}
/// <summary>
/// 获取维客短信到期时间
/// </summary>
/// <param name="appKey"></param>
/// <param name="accid"></param>
/// <returns></returns>
public DateTime GetSmsEndTime(int appKey, int accid)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select endtime from [i200].[dbo].[t_App_Au] where accid=@accid and appKey=@appKey;");
return(DapperHelper.ExecuteScalar <DateTime>(strSql.ToString(), new { accid = accid, appKey = appKey }));
}
/// <summary>
/// 返回符合条件的记录条数
/// </summary>
/// <param name="strWhere"></param>
/// <returns></returns>
public int GetPageCount(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(*) from RequirementManage rm where 1=1 ");
strSql.Append(strWhere);
return(DapperHelper.ExecuteScalar <int>(strSql.ToString()));
}
/// <summary>
/// 获取批次发送用户数
/// </summary>
/// <param name="batchId"></param>
/// <param name="channelId"></param>
/// <returns></returns>
public int GetBatchCount(string batchId, int channelId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select AccIdCount from MessageBatch where BatchId='" +
batchId + "' and channelId=" + channelId);
return(DapperHelper.ExecuteScalar <int>(strSql.ToString()));
}
public bool CheckForumUrl(string url)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("if exists(select * from Sys_TaskDaily where dt_remark like '%" + "@(" + url + ")" + "%') " +
"select 1;" +
"else " +
"select 0;");
return(DapperHelper.ExecuteScalar <int>(strSql.ToString()) == 1 ? true : false);
}
public bool CheckTaskDailyExist(string content)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("if exists(select * from Sys_TaskDaily where t_mk='" + content + "') " +
"select 1;" +
"else " +
"select 0;");
return(DapperHelper.ExecuteScalar <int>(strSql.ToString()) == 1 ? true : false);
}
/// <summary>
/// 检查是否有当日数据
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public int CheckShopActive(DateTime dt)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("if exists(select * from Sys_TempActiveStatus where DateDiff(day,ShowDate,@date)=0)" +
" select 1;" +
" else " +
" select 0;");
return(DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { date = dt }));
}
//public List<RechargeRecord> GetRecordList()
//{
// StringBuilder strSql = new StringBuilder();
//}
/// <summary>
/// 检查表中是否存在该订单号
/// </summary>
/// <param name="orderNo"></param>
/// <returns></returns>
public int CheckExist(int oid)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("if(exists(select * from Sys_RechargeRecord where oid=@oid)) " +
"select 1 " +
"else " +
"select 0");
return(DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { oid = oid }));
}
/// <summary>
/// 获取总条目
/// </summary>
/// <param name="accid"></param>
/// <returns></returns>
public int GetUserMessageCount(int accid, string str)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(*) from Sys_UserMessage where Accid=@accid ");
if (!string.IsNullOrEmpty(str))
{
strSql.Append(" and " + str);
}
return(DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { accid = accid }));
}
/// <summary>
/// 判断是否重复消息
/// </summary>
/// <param name="accid"></param>
/// <param name="msgKey"></param>
/// <returns></returns>
public int CheckMsgExist(int accid, string msgKey)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("if exists(select * from Sys_I200.dbo.Sys_UserMessage where accid=@accid and msgkey=@msgkey) " +
" " +
" select 1;" +
" " +
" else " +
" select 0; ");
return(DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { accid = accid, msgkey = msgKey }));
}
public int GetLogListCount(string where)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" select count(*) as cnt from Sys_ManageLog ");
if (where.Length > 0)
{
strSql.Append("where " + where + "");
}
strSql.Append("; ");
return(DapperHelper.ExecuteScalar <int>(strSql.ToString()));
}
/// <summary>
/// 查询信息
/// </summary>
/// <param name="whereModel">条件</param>
/// <param name="userId">查询人ID</param>
/// <param name="verification">查询验证</param>
/// <param name="userName">查询人名称</param>
/// <returns></returns>
public int SelectIfilterList(List <SynthesisIFilter> whereModels, int userId, string verification, string userName)
{
List <DapperWhere> parmsList = new List <DapperWhere>();
StringBuilder strSql = new StringBuilder();
strSql.Append("create table #list(accid int); ");
strSql.Append(" insert into #list(accid) ");
strSql.Append(" select distinct accountid from SysRpt_ShopInfo where taskStat>-10 ");
foreach (SynthesisIFilter itemModel in whereModels)
{
strSql.Append(GetSqlWhereByModel(itemModel));
}
strSql.Append("; ");
strSql.Append(" declare @list varchar(max),@count int; ");
strSql.Append(" set @list='0'; ");
strSql.Append(" select @count=COUNT(accid) from #list; ");
strSql.Append(" select @list= Isnull(@list,'0')+','+ltrim(accid) from #list ; ");
strSql.Append(" insert into SynthesisIFilterLog(strSql,resultData, ");
strSql.Append(" resultCount,userid,verification,inserName,inserTime) ");
strSql.Append(" values(@strSql,@list,@count,@userid,@verifi,@inserName,GETDATE());");
strSql.Append(";select @@IDENTITY;");
strSql.Append(" drop table #list ; ");
object obj = DapperHelper.ExecuteScalar(strSql.ToString(), new
{
strSql = strSql.ToString(),
userid = userId,
verifi = verification,
inserName = userName
});
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
public string AddAgent(AgentModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("if exists(select * from Sys_I200.dbo.Sys_agent_mess where AgentId=@AgentId) " +
" " +
" select -1;" +
" " +
" else " +
" begin " +
" insert into Sys_I200.dbo.Sys_agent_mess (" +
" AgentId,AgentPassword,AgentName,AgentGrade," +
" AgentPhone,AgentAddress,AgentIdCard,AgentNumber," +
" AgentEmail,AgentQQ,Creater,CreatingDate,Remark,activeStatus,AgentLink,ServiceType) values (" +
" @AgentId,@AgentPassword,@AgentName,@AgentGrade," +
" @AgentPhone,@AgentAddress,@AgentIdCard,@AgentNumber," +
" @AgentEmail,@AgentQQ,@Creater,getdate(),@Remark,@activeStatus,@AgentLink,@ServiceType);" +
" select @@IDENTITY;" +
" end ");
try
{
int reVal = DapperHelper.ExecuteScalar <int>(strSql.ToString(), new
{
AgentId = model.AgentId,
AgentPassword = model.AgentPassword,
AgentName = model.AgentName,
AgentGrade = model.AgentGrade,
AgentPhone = model.AgentPhone,
AgentAddress = model.AgentAddress,
AgentIdCard = model.AgentIdCard,
AgentNumber = model.AgentNumber,
AgentEmail = model.AgentEmail,
AgentQQ = model.AgentQQ,
Creater = model.Creater,
Remark = model.Remark,
activeStatus = 1,
AgentLink = model.AgentLink,
ServiceType = model.ServiceType
});
return(reVal.ToString());
}
catch (Exception ex)
{
return("0");
}
}
/// <summary>
/// 获取实物名称
/// </summary>
/// <param name="busId"></param>
/// <returns></returns>
public string GetMaterialGoodsName(int busId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select AliasName from [i200].[dbo].[T_MaterialGoods] where GoodsId=@id;");
try
{
return(DapperHelper.ExecuteScalar <string>(strSql.ToString(), new { id = busId }));
}
catch (Exception ex)
{
Logger.Error("获取实物名称出错", ex);
return("");
}
}
public string GetBusIdByOid(int oid)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select BusId from [i200].[dbo].[T_OrderInfo] where oid=@id;");
try
{
return(DapperHelper.ExecuteScalar <string>(strSql.ToString(), new { id = oid }));
}
catch (Exception ex)
{
Logger.Error("获取BusId出错", ex);
return("");
}
}