/// <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(""); } }