public string GetGuiGeListByProductId(int ProductId = 0, string OpenId = "") { string sql = string.Format(@"SELECT s.SpecificationId, s.ProductId, s.Amount, s.Unit, PKI.ProductName , s.SpecificationPicUrl, s.VisitCount, PKI.ProductFirstId, PKI.ProductSecondId , CASE WHEN L.Id IS NULL THEN 'f' ELSE 't' END AS IsPraise, Display.IsDisplay FROM Specification s JOIN ProductKuInfo PKI ON s.ProductId = PKI.ProductId LEFT JOIN ( SELECT Id,SpecificationId FROM SpecificationPraiseLog WHERE OpenId = '{0}' ) L ON L.SpecificationId = s.SpecificationId LEFT JOIN SpecificationConf sConf ON s.SpecificationId = sConf.SpecificationId LEFT JOIN ( SELECT SpecificationConfId, SUM(IsDisplay) AS IsDisplay FROM ( SELECT SpecificationConfId , CASE IsDisplay WHEN 'true' THEN 1 ELSE 0 END AS IsDisplay FROM SpecificationConfVC ) A GROUP BY SpecificationConfId ) Display ON sConf.SpecificationConfId = Display.SpecificationConfId WHERE Display.IsDisplay > 0 AND PKi.ProductId ={1}", OpenId, ProductId); var r = SqlHelper2.ExecuteDataTable(sql); return(JsonConvert.SerializeObject(r)); }
public string GetLiuYanReplyInfo(dynamic requestData) { LogHelper.WriteMsgByDay("WeiXinController-GetLiuYanReplyInfo-log:" + requestData.ToString()); int page = requestData.page; int pagesize = requestData.pagesize; string keyType = requestData.KeyType; string keyId = requestData.KeyId; string parentId = requestData.ParentId; if (string.IsNullOrEmpty(keyType) || string.IsNullOrEmpty(keyId) || string.IsNullOrEmpty(parentId)) { return("参数不能为空"); } else { string sql = string.Format(@"SELECT row_number() over(order BY CreateDate) RowId,t.* FROM ( SELECT a.Id,a.KeyId,a.OpenId,a.LiuYan, c.Nickname,c.HeadImgUrl,r.HotelName, a.CreateDate,COUNT(b.Id) ReplyCount FROM CpkLiuYan a LEFT JOIN CpkLiuYan b ON a.Id=b.ParentId JOIN dbo.OpenIdAssociated c ON c.OpenId = a.OpenId JOIN dbo.RegistMember r ON r.MemberId = c.UserId AND c.UserType =2 WHERE a.ParentId={0} AND a.KeyId= {1} AND a.KeyType = '{2}' GROUP BY a.Id,a.KeyId,a.OpenId,a.LiuYan ,a.CreateDate,c.Nickname,c.HeadImgUrl,r.HotelName ) t", parentId, keyId, keyType); String sqlpage = PageHelper.GetPagerSql(page, pagesize, sql); //分页sql var data = SqlHelper2.ExecuteDataTable(sqlpage); //分页的数据 var totalcount = SqlHelper2.GetCountByNormalSql(sql); //总条数 var toatlpage = PageHelper.GetTotalPage(totalcount, pagesize); return(JsonConvert.SerializeObject(data)); } }
public string Create(dynamic requestData) { LogHelper.WriteMsgByDay("BanKuaiController-Create-log:" + requestData.ToString()); ReturnJson r = new ReturnJson(); string bankuainame = requestData.BanKuaiName; if (bankuainame.Length > 0 && bankuainame.Length <= 14) { string sql = string.Format(@"SELECT * FROM dbo.CpkBanKuai WHERE BanKuaiName = '{0}'", bankuainame); int count = SqlHelper2.GetCountByCountSql(sql); if (count == 0) { sql = string.Format(@"INSERT INTO dbo.CpkBanKuai( BanKuaiName )VALUES ('{0}')", bankuainame); dataContext.ExecuteNonQuery(CommandType.Text, sql); r.status = "succ"; r.message = "创建成功"; } else { r.message = "该板块已存在"; } } else if (bankuainame.Length > 14) { r.message = "板块名称长度已超过14"; } else { r.message = "BanKuaiName不能为空"; } return(JsonConvert.SerializeObject(r)); }
/// <summary> /// 查询列表(分页) /// </summary> /// <param name="strSql">sql语句</param> /// <param name="dbParameter">参数</param> /// <param name="pagination">分页数据</param> /// <returns></returns> public DataTable FindTable(string strSql, object dbParameter, Pagination pagination) { int total = pagination.records; pagination.sidx = SqlHelper2.SqlFilters(pagination.sidx); DataTable data; if (dbWhere != null) { int orderIndex = strSql.ToUpper().IndexOf("ORDER BY"); if (orderIndex > 0) { strSql = strSql.Substring(0, orderIndex); string orderString = strSql.Substring(orderIndex); strSql = string.Format(" select * From ({0})t Where {1} {2} ", strSql, dbWhere.sql, orderString); } else { strSql = string.Format(" select * From ({0})t Where {1} ", strSql, dbWhere.sql); } DynamicParameters dynamicParameters = SqlHelper.FieldValueParamToParameter(dbWhere.dbParameters); dynamicParameters.AddDynamicParams(dbParameter); data = db.FindTable(strSql, dynamicParameters, pagination.sidx, pagination.sord.ToLower() == "asc" ? true : false, pagination.rows, pagination.page, out total); } else { data = db.FindTable(strSql, dbParameter, pagination.sidx, pagination.sord.ToLower() == "asc" ? true : false, pagination.rows, pagination.page, out total); } pagination.records = total; return(data); }
/// <summary> /// 分页导航获取总页数 /// </summary> /// <param name="strwhere">查询条件</param> /// <param name="tblName">表、视图</param> /// <returns>Total:字段名(总的记录数)</returns> public static DataTable GetDataTable(string strwhere, string tblName) { SqlParameter[] param = new SqlParameter[] { new SqlParameter("@strwhere", strwhere), //查询条件(注意: 不要加where) new SqlParameter("@tblName", tblName), //表名 new SqlParameter("@doCount", 1) //返回记录总数,非 0 值则返回 }; return(SqlHelper2.ExecuteTable(CommandType.StoredProcedure, "SP_Pagination", param)); }
public string CreateVisitor(dynamic requestData) { RJson r = new RJson(); string telephone = requestData.Phone; string openid = requestData.OpenId; string jobType = requestData.JobType; if (string.IsNullOrEmpty(telephone) || string.IsNullOrEmpty(openid) || string.IsNullOrEmpty(jobType)) { r.message = "参数不能为空"; return(JsonConvert.SerializeObject(r)); } try { RegistMember member = new RegistMember(); member.MemberTelePhone = telephone; member.MemberState = 1; member.RegistDate = DateTime.Now; member.TotalIntegral = 0; member.LeaveIntegral = 0; member.IsEnable = 0; member.Remark = requestData.Remark; db.RegistMember.Add(member); db.SaveChanges(); #region 存入用户OpenId OpenIdAssociated openbase = new OpenIdAssociated(); openbase.OpenId = requestData.OpenId; openbase.UserId = member.MemberId; openbase.UserType = 2; openbase.Nickname = requestData.Nickname; openbase.HeadImgUrl = requestData.HeadImgUrl; openbase.CreateDate = DateTime.Now; db.OpenIdAssociated.Add(openbase); db.SaveChanges(); #endregion //当新增完会员之后 在会员简历表里同步新增一条数据 var sql = string.Format(@"INSERT INTO MemberProfile (MemberId ,JobType) VALUES ({0},'{1}') ", member.MemberId, jobType); SqlHelper2.ExecuteNonQuery(CommandType.Text, sql); r.result_status = "succ"; r.data = member.MemberId.ToString(); } catch (Exception ex) { r.message = ex.Message.ToString(); } return(JsonConvert.SerializeObject(r)); }
public string TestGetRedPack(string openid = "0") { //RedPack.CreateRedPack(2,"2018-08") ; //var a = RedPack.GetMoneys(); //var a = RedPack.GetMoneys2(); string nowday = DateTime.Now.ToString("yyyy-MM-dd"); string nowmonth = DateTime.Now.ToString("yyyy-MM"); if (string.IsNullOrEmpty(openid)) { for (int s = 51; s <= 52; s++) { openid = "xjy" + s.ToString(); for (int i = 1; i <= 10; i++) { string sql = string.Format("SELECT COUNT(1) FROM RedPackScanRecord WHERE OpenId ='{0}' AND CONVERT(VARCHAR(10),ScanDate,121) = '{1}' ", openid, nowday); int nowdayscan = SqlHelper2.GetCount(CommandType.Text, sql); var money = RedPack.GetMoney(nowdayscan, openid, nowmonth); //增加扫描红包记录 sql = string.Format(@"INSERT INTO dbo.RedPackScanRecord ( OpenId ,Money ,ScanCode ,IsPay ,PayId,IsFirst,ScanMonth) VALUES ( '{0}' , -- OpenId - varchar(50) {1} , -- Money - float '{2}' , -- ScanCode - varchar(50) 0 , -- IsPay - int 0 , -- PayId - int {4},'{3}') ; ", openid, money, "", nowmonth, nowdayscan == 0 ? 1 : 0); SqlHelper2.ExecuteNonQuery(CommandType.Text, sql); } } } else { string sql = string.Format("SELECT COUNT(1) FROM RedPackScanRecord WHERE OpenId ='{0}' AND CONVERT(VARCHAR(10),ScanDate,121) = '{1}' ", openid, nowday); int nowdayscan = SqlHelper2.GetCount(CommandType.Text, sql); var money = RedPack.GetMoney(nowdayscan, openid, nowmonth); //增加扫描红包记录 sql = string.Format(@"INSERT INTO dbo.RedPackScanRecord ( OpenId ,Money ,ScanCode ,IsPay ,PayId,IsFirst,ScanMonth) VALUES ( '{0}' , -- OpenId - varchar(50) {1} , -- Money - float '{2}' , -- ScanCode - varchar(50) 0 , -- IsPay - int 0 , -- PayId - int {4},'{3}') ; ", openid, money, "", nowmonth, nowdayscan == 0 ? 1 : 0); SqlHelper2.ExecuteNonQuery(CommandType.Text, sql); } return(""); }
/// <summary> /// 查询列表(分页) /// </summary> /// <typeparam name="T">类型</typeparam> /// <param name="condition">表达式</param> /// <param name="pagination">分页数据</param> /// <returns></returns> public IEnumerable <T> FindList <T>(Expression <Func <T, bool> > condition, Pagination pagination) where T : class, new() { int total = pagination.records; pagination.sidx = SqlHelper2.SqlFilters(pagination.sidx); if (string.IsNullOrEmpty(pagination.sidx)) { pagination.sidx = ""; pagination.sord = "asc"; } var data = db.FindList <T>(condition, pagination.sidx, pagination.sord.ToLower() == "asc" ? true : false, pagination.rows, pagination.page, out total); pagination.records = total; return(data); }
/// <summary> /// 分页数据库操作函数 /// </summary> /// <param name="strwhere">查询条件(注意: 不要加where)</param> /// <param name="tblName">表名</param> /// <param name="OrderType">设置排序类别,非 0 值则降序</param> /// <param name="fldName">排序的字段名</param> /// <param name="strGetFields">需要返回的列</param> /// <param name="PageSize">页尺寸</param> /// <param name="PageIndex">页码</param> /// <param name="doCount">,[0:返回查询的表数据,非0:值则返回记录总数]</param> /// <returns>返回一个表</returns> public static DataTable GetDataTable(string strwhere, string tblName, int OrderType, string fldName, string strGetFields, int PageSize, int PageIndex, int doCount) { SqlParameter[] param = new SqlParameter[] { new SqlParameter("@strwhere", strwhere), //查询条件(注意: 不要加where) new SqlParameter("@tblName", tblName), //表名 new SqlParameter("@OrderType", OrderType), //设置排序类别,非 0 值则降序 new SqlParameter("@fldName", fldName), //排序的字段名 new SqlParameter("@strGetFields", strGetFields), //需要返回的列 new SqlParameter("@PageSize", PageSize), //页尺寸 new SqlParameter("@PageIndex", PageIndex), //页码 new SqlParameter("@doCount", doCount) //返回记录总数,非 0 值则返回 }; return(SqlHelper2.ExecuteTable(CommandType.StoredProcedure, "SP_Pagination", param)); }
public string GetCaiPinCountBySecondId(string SecondId) { string sqlwhere = ""; if (!string.IsNullOrEmpty(SecondId)) { sqlwhere = string.Format(" and c.SecondId IN ({0})", SecondId); } string sql = string.Format(@"SELECT COUNT(1) FROM dbo.CpkCaiPinBasicInfo a JOIN dbo.CpkCaiPinCategory b ON b.CaiPinId = a.CaiPinId JOIN dbo.CpkSecondCategory c ON c.SecondId = b.SecondCategoryId WHERE a.IsEnable =1 AND a.IsPublish =1 AND c.IsEnable = 1 AND c.IsPublish =1 {0} ", sqlwhere); return(SqlHelper2.ExecuteScalar(CommandType.Text, sql).ToString()); }
public string GetHistory(string openid) { RHistoryJson r = new RHistoryJson(); try { if (string.IsNullOrEmpty(openid)) { r.message = "参数有误"; } else { string sql = string.Format(@"SELECT * FROM RedPackScanRecord WHERE OpenId = '{0}'", openid); DataTable dt = SqlHelper2.ExecuteDataTable(sql); r.haveTxMoney = Convert.ToDouble(dt.Compute("Sum(Money)", "IsPay=1").ToString() == "" ? 0 : dt.Compute("Sum(Money)", "IsPay=1")); //已提现金额 r.kTxMoney = Convert.ToDouble(dt.Compute("Sum(Money)", "IsPay=0").ToString() == "" ? 0 : dt.Compute("Sum(Money)", "IsPay=0")); //还没有进行提现的金额 //获取用户扫描但是未提现的金额总和 // string sql = string.Format(@"SELECT ISNULL(SUM(Money),0) Total // FROM dbo.RedPackScanRecord WHERE OpenId = '{0}' AND IsPay = 0 ", openid); // r.kTxMoney = Convert.ToDouble(SqlHelper2.ExecuteScalar(CommandType.Text, sql)); if (r.kTxMoney > 1) { r.isKeTiXian = 1; } //获取用户扫描 提现记录 sql = string.Format(@"SELECT * FROM ( SELECT Money Money,CONVERT(VARCHAR(30),ScanDate,121) CreateDate,'+' Action FROM dbo.RedPackScanRecord WHERE OpenId = '{0}' UNION SELECT PayAmout Money,CONVERT(VARCHAR(30),PayDate,121) CreateDate,'-' Action FROM dbo.RedPackPayRecord WHERE OpenId = '{0}' ) t ORDER BY CreateDate", openid); var q = SqlHelper2.ExecuteDataTable(sql); r.data = q; //用户扫描 提现记录 r.isRegist = RedPack.GetIsRegist(openid); //是否注册用户 0未注册 1已注册 r.scanCount = dt.Rows.Count; r.result_status = "succ"; //返回成功状态 } } catch (Exception ex) { r.message = "有异常"; RedPack.AddAlertLog(openid, ex.ToString(), "GetHistory-error"); } return(JsonConvert.SerializeObject(r)); }
public string GetDyrq(string openid = "") { string sql = string.Format(@"SELECT TOP 3 a.CaiPinId,a.CaiPinName,a.Images,b.Content Sbly, ISNULL(k.LlCount,0) LlCount, --浏览数量 CASE g.RecordAction WHEN 'collect' THEN 1 ELSE 0 END IsCollect ,--是否收藏 ISNULL(k.LlCount,0)*0.2+ISNULL(k.ScCount,0)*0.8 RQ ----当月人气计算逻辑 浏览量*0.2 + 收藏量*0.8 FROM dbo.CpkCaiPinBasicInfo a LEFT JOIN dbo.CpkCaiPinRichInfo b ON b.CaiPinId = a.CaiPinId AND b.TypeId = 1 LEFT JOIN ViewCpkTongji k ON a.CaiPinId = k.RecordKeyId AND k.RecordKeyType='菜品' LEFT JOIN dbo.CpkTongJi g ON a.CaiPinId=g.RecordKeyId AND g.RecordKeyType='菜品' AND g.RecordAction='collect' AND g.OpenId ='{0}' WHERE CONVERT(VARCHAR(7),a.CreateDate,121) = CONVERT(VARCHAR(7),GETDATE(),121) ORDER BY RQ DESC", openid); var r = SqlHelper2.ExecuteDataTable(sql); return(JsonConvert.SerializeObject(r)); }
/// <summary> /// 分页函数,执行SP_Pagination3分页操作 /// </summary> /// <param name="tblName">表名</param> /// <param name="strGetFields">需要返回的列</param> /// <param name="fldName">排序的字段名</param> /// <param name="PageSize">页尺寸</param> /// <param name="PageIndex">页码</param> /// <param name="strwhere">查询条件(注意: 不要加where)</param> /// <returns></returns> public static DataTable GetDataTableOfRow_Number(string tblName, string strGetFields, string fldName, int PageSize, int PageIndex, string strwhere) { SqlParameter[] param = new SqlParameter[] { new SqlParameter("@tblName", tblName), //表名 new SqlParameter("@strGetFields", strGetFields), //需要返回的列 new SqlParameter("@fldName", fldName), //排序的字段名 new SqlParameter("@PageSize", PageSize), //页尺寸 new SqlParameter("@PageIndex", PageIndex), //页码 new SqlParameter("@strwhere", strwhere), //查询条件(注意: 不要加where) }; //SqlParameter parm1 = new SqlParameter("@WebName", SqlDbType.Image); //parm1.Value = imageb; return(SqlHelper2.ExecuteTable(CommandType.StoredProcedure, "SP_Pagination3", param)); }
/// <summary> /// 把64服务器上byjiang数据库中的教师同步到本地Base_Teacher表中 /// </summary> /// <returns></returns> public int SynochroTeacher() { SqlHelper2 sh2 = new SqlHelper2(); sh2.Open(); Synchro64DAL sy64 = new Synchro64DAL(sh2); DataTable dt = sy64.SyncTeacher(); SqlHelper sh = new SqlHelper(); sh.Open(); DeleteOldDAL dlt = new DeleteOldDAL(sh); dlt.DeleteBase_Teacher(); InsertNewDAL insertNewDAL = new InsertNewDAL(sh); int res = 0; foreach (DataRow row in dt.Rows) { TeacherModel model = new TeacherModel(); model.Term = row.Value("Term", ""); model.UserID = row.Value("UserID", ""); model.UserName = row.Value("UserName", ""); model.Gender = row.Value("Gender", ""); model.SchoolID = row.Value("SchoolID", ""); model.SchoolName = row.Value("SchoolName", ""); model.SubjectIDs = row.Value("SubjectIDs", ""); model.SubjectNames = row.Value("SubjectNames", ""); //model.TS = row.Value("TS", DateTime.Now); res = insertNewDAL.InsertTeacher(model); } sh2.Close(); sh.Close(); return(res); }
public string Edit(dynamic requestData) { ReturnJson r = new ReturnJson(); int firstid = requestData.FirstId; int secondid = requestData.SecondId; string firstname = requestData.FirstName; string secondname = requestData.SecondName; if (!string.IsNullOrEmpty(firstname) && !string.IsNullOrEmpty(secondname)) { // var sql = string.Format(@"UPDATE dbo.CpkFirstCategory SET FirstName = '{0}' WHERE FirstId = {1} ; // UPDATE dbo.CpkSecondCategory SET SecondName = '{2}' WHERE SecondId = {3}", firstname, firstid, secondname, secondid); // dataContext.ExecuteNonQuery(CommandType.Text, sql); string sql = string.Format(@"SELECT FirstId FROM dbo.CpkFirstCategory WHERE FirstName = '{0}'", firstname); DataTable dt = SqlHelper2.ExecuteDataTable(sql); if (dt.Rows.Count > 0) //判断是否有该名称的一级分类 { //sql = string.Format("SELECT COUNT(1) FROM dbo.CpkSecondCategory WHERE FirstId ={0} AND SecondName = '{1}'", dt.Rows[0]["FirstId"].ToString(), secondname); //if (SqlHelper2.GetCountByCountSql(sql) > 0) //判断是否该一级分类下已存在该二级分类 //{ // r.message = "已存在改分类"; //} sql = string.Format(@"UPDATE dbo.CpkSecondCategory SET SecondName = '{0}',FirstId ={1} WHERE SecondId = {2}", secondname, dt.Rows[0]["FirstId"].ToString(), secondid); dataContext.ExecuteNonQuery(CommandType.Text, sql); r.message = "更新成功"; r.status = "succ"; } else { r.message = "不存在该名称的一级分类"; } } else { r.message = "分类名称不能为空"; } return(JsonConvert.SerializeObject(r)); }
public string AddLiuYanInfo(dynamic requestData) { LogHelper.WriteMsgByDay("WeiXinController-AddLiuYanInfo-log:" + requestData.ToString()); string keyType = requestData.KeyType; string keyId = requestData.KeyId; string parentId = requestData.ParentId; string liuYan = requestData.LiuYan; string openId = requestData.OpenId; if (string.IsNullOrEmpty(openId) || string.IsNullOrEmpty(liuYan) || string.IsNullOrEmpty(keyId) || string.IsNullOrEmpty(keyType)) { return("参数不能为空"); } else { string sql = string.Format(@"INSERT INTO dbo.CpkLiuYan ( ParentId ,KeyId ,KeyType ,OpenId , LiuYan ) VALUES ({0},{1},'{2}','{3}','{4}')", parentId, keyId, keyType, openId, liuYan); SqlHelper2.ExecuteNonQuery(CommandType.Text, sql); return("succ"); } }
/// <summary> /// 验证抽奖规则 /// </summary> /// <param name="openid"></param> /// <param name="scode"></param> /// <returns></returns> private string CheckRule(string openid, string scode) { string msg = "fail,"; string sql = ""; //验证scode码是否可用 sql = string.Format(@"SELECT State FROM dbo.RedPackCode WHERE Code = '{0}' ", scode); var a = SqlHelper2.ExecuteScalar(CommandType.Text, sql); string nowday = DateTime.Now.ToString("yyyy-MM-dd"); //验证当天扫描是否超过10次 sql = string.Format("SELECT COUNT(1) FROM dboRedPackScanRecord WHERE OpenId ='{0}' AND ScanDate LIKE'%{1}%' ", openid, nowday); int nowdayscan = SqlHelper2.GetCount(CommandType.Text, sql); if (nowdayscan >= 10) { msg = "fail,当天扫描是已超过10次"; return(msg); //r.message = "当天扫描是已超过10次"; //return JsonConvert.SerializeObject(r); } return(""); }
public string Index(string openid = "") { string sql = string.Format(@"select BanKuaiId,BanKuaiName,CaiPinId,HotelLongitude,HotelAtitude,CaiPinName,Images,Max(LlCount) LlCount,MAX(Content) Sbly, VideoImage,VideoUrl,UpdateDate ,IsCollect FROM (select *,ROW_NUMBER() over(partition by BanKuaiId order by UpdateDate desc) rowNum from (SELECT a.CaiPinId,a.HotelLongitude,a.HotelAtitude, a.CaiPinName,a.Images,a.VideoImage,a.VideoUrl,a.UpdateDate,c.BanKuaiId,c.BanKuaiName, ISNULL(k.LlCount,0) LlCount,r.Content, CASE g.RecordAction WHEN 'collect' THEN 1 ELSE 0 END IsCollect ,'' PCaiPuUrl FROM dbo.CpkCaiPinBasicInfo a JOIN dbo.CpkCpBkRelation b ON b.CaiPinId = a.CaiPinId AND b.IndexShow = 1 JOIN dbo.CpkBanKuai c ON c.BanKuaiId = b.BanKuaiId LEFT JOIN dbo.CpkCaiPinRichInfo r ON r.CaiPinId = a.CaiPinId AND r.TypeId = 4 -- TypeId 类别在CpkRichTextType中 LEFT JOIN ViewCpkTongji k ON a.CaiPinId = k.RecordKeyId AND k.RecordKeyType='菜品' LEFT JOIN dbo.CpkTongJi g ON a.CaiPinId=g.RecordKeyId AND g.RecordKeyType='菜品' AND g.RecordAction='collect' AND g.OpenId ='{0}' WHERE a.IsEnable =1 AND a.IsPublish =1 AND c.IsEnable =1 AND c.IsPublish =1 ) t ) as s WHERE s.rowNum <= 6 GROUP by BanKuaiId,HotelLongitude,HotelAtitude,BanKuaiName,CaiPinId,CaiPinName,Images,VideoImage,VideoUrl,UpdateDate,IsCollect ORDER by BanKuaiId,UpdateDate desc ", openid); var r = SqlHelper2.ExecuteDataTable(sql); return(JsonConvert.SerializeObject(r)); }
/// <summary> /// 查询列表(分页) /// </summary> /// <typeparam name="T">类型</typeparam> /// <param name="strSql">SQL语句</param> /// <param name="dbParameter">参数</param> /// <param name="pagination">分页数据</param> /// <returns></returns> public IEnumerable <T> FindList <T>(string strSql, object dbParameter, Pagination pagination) where T : class { int total = pagination.records; pagination.sidx = SqlHelper2.SqlFilters(pagination.sidx); if (string.IsNullOrEmpty(pagination.sidx)) { pagination.sidx = ""; pagination.sord = "asc"; } if (dbWhere != null) { int orderIndex = strSql.ToUpper().IndexOf("ORDER BY"); if (orderIndex > 0) { strSql = strSql.Substring(0, orderIndex); string orderString = strSql.Substring(orderIndex); strSql = string.Format(" select * From ({0})t Where {1} {2} ", strSql, dbWhere.sql, orderString); } else { strSql = string.Format(" select * From ({0})t Where {1} ", strSql, dbWhere.sql); } DynamicParameters dynamicParameters = SqlHelper.FieldValueParamToParameter(dbWhere.dbParameters); dynamicParameters.AddDynamicParams(dbParameter); var data = db.FindList <T>(strSql, dynamicParameters, pagination.sidx, pagination.sord.ToLower() == "asc" ? true : false, pagination.rows, pagination.page, out total); pagination.records = total; return(data); } else { var data = db.FindList <T>(strSql, dbParameter, pagination.sidx, pagination.sord.ToLower() == "asc" ? true : false, pagination.rows, pagination.page, out total); pagination.records = total; return(data); } }
//-------------------------使用用户创建的事务 public static int ExecuteNonQuery(SqlTransaction trans, string procName, params SqlParameter[] parms) { return(SqlHelper2.ExecuteNonQuery(trans, CommandType.StoredProcedure, procName, parms)); }
public static SqlDataReader Select(SqlTransaction trans, string procName, params SqlParameter[] parms) { return(SqlHelper2.ExecuteReader(trans, CommandType.StoredProcedure, procName, parms)); }
public async Task <string> ScanPressureTest(dynamic requestData) { RScanJson r = new RScanJson(); string kaiGuan = ConfigurationManager.AppSettings["kaiguan"]; //if (kaiGuan == "ON") //{ string openid = requestData.OpenId; string scode = requestData.Scode; try { if (string.IsNullOrEmpty(openid) || string.IsNullOrEmpty(scode)) { r.message = "参数有误"; } else { #region 1.判断是否注册,是否是队员 int isRegist = RedPack.GetIsRegist(openid); if (isRegist == 1) { r.isRegist = 1; } else if (isRegist == -1) //表示是队员 返回 { r.isRegist = 0; r.message = "队员不能扫码"; return(JsonConvert.SerializeObject(r)); } #endregion #region 2.获取用户扫描但是未提现的金额总和 string sql = ""; sql = string.Format(@"SELECT ISNULL(SUM(Money),0) Total FROM dbo.RedPackScanRecord WHERE OpenId = '{0}' AND IsPay = 0 ", openid); r.kTxMoney = Convert.ToDouble(SqlHelper2.ExecuteScalar(CommandType.Text, sql)); if (r.kTxMoney > 1) { r.isKeTiXian = 1; } #endregion //当天 string nowDay = DateTime.Now.ToString("yyyy-MM-dd"); //当月 string nowMonth = DateTime.Now.ToString("yyyy-MM"); //string a = CheckRule(openid, scode); //验证二维码是否有效 sql = string.Format(@"SELECT a.IsScan,b.Money,convert(varchar(19),b.ScanDate,121) ScanDate FROM dbo.RedPackCode a LEFT JOIN dbo.RedPackScanRecord b ON a.Code=b.ScanCode WHERE a.Code = '{0}' ", scode); DataTable dt = SqlHelper2.ExecuteDataTable(sql); if (dt.Rows.Count == 0) { r.code_status = "非活动码"; r.message = "此码不属于本次活动"; RedPack.AddAlertLog(openid, scode + ":" + r.message, "scan"); } //配合压测 mark掉 //else if (Convert.ToInt16(dt.Rows[0]["IsScan"]) == 1) //{ // r.code_status = "已被扫"; // r.money = Convert.ToDouble(dt.Rows[0]["Money"]); // r.scan_date = dt.Rows[0]["ScanDate"].ToString(); // r.message = "该二维码已被扫描"; // RedPack.AddAlertLog(openid, scode + ":" + r.message, "scan"); //} else { //验证当天扫描是否超过10次 sql = string.Format("SELECT COUNT(1) FROM RedPackScanRecord WHERE OpenId ='{0}' AND CONVERT(VARCHAR(10),ScanDate,121) = '{1}' ", openid, nowDay); int nowDayScan = SqlHelper2.GetCount(CommandType.Text, sql); if (nowDayScan >= 10) { r.message = "当天扫描是已超过10次"; RedPack.AddAlertLog(openid, r.message, "scan"); } else { sql = string.Format("SELECT COUNT(1) FROM RedPackScanRecord WHERE OpenId ='{0}' ", openid); int allScan = SqlHelper2.GetCount(CommandType.Text, sql); //去抽红包 double money = RedPack.GetMoney(allScan, openid, nowMonth); #region 增加扫描红包记录 sql = string.Format(@"INSERT INTO dbo.RedPackScanRecord ( OpenId ,Money ,ScanCode ,IsPay ,PayId,IsFirst,ScanMonth) VALUES ( '{0}' , -- OpenId - varchar(50) {1} , -- Money - float '{2}' , -- ScanCode - varchar(50) 0 , -- IsPay - int 0 , -- PayId - int {4},'{3}') ; UPDATE dbo.RedPackCode SET IsScan =1,UseDate = GETDATE() WHERE Code = '{2}'; UPDATE dbo.RedPackConfig SET LeiJiMoney = LeiJiMoney + {1} WHERE Month = '{3}' ", openid, money, scode, nowMonth, nowDayScan == 0 ? 1 : 0); LogHelper.WriteMsg("sacn增加扫描红包记录-openid:" + openid + ",sql:" + sql); SqlHelper2.ExecuteNonQuery(CommandType.Text, sql); #endregion r.scan_date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); r.money = money; //返回本次中奖金额 //获取用户扫描但是未提现的金额总和 sql = string.Format(@"SELECT ISNULL(SUM(Money),0) Total FROM dbo.RedPackScanRecord WHERE OpenId = '{0}' AND IsPay = 0 ", openid); r.kTxMoney = Convert.ToDouble(SqlHelper2.ExecuteScalar(CommandType.Text, sql)); if (r.kTxMoney > 1) { r.isKeTiXian = 1; } r.result_status = "succ"; //返回成功状态 } } } } catch (Exception ex) { r.message = "有异常"; RedPack.AddAlertLog(openid, ex.ToString(), "scan-error"); } //} //else //{ // r.message = "活动现处于关闭状态,谢谢~"; //} return(JsonConvert.SerializeObject(r)); }
public static SqlDataReader Select(SqlTransaction trans, string sql, params SqlParameter[] parms) { return(SqlHelper2.ExecuteReader(trans, CommandType.Text, sql, parms)); }
//-------------------------使用用户创建的事务 public static int ExecuteNonQuery(SqlTransaction trans, string sql, params SqlParameter[] parms) { return(SqlHelper2.ExecuteNonQuery(trans, CommandType.Text, sql, parms)); }
public static SqlDataReader Select(SqlConnection conn, string sql, params SqlParameter[] parms) { return(SqlHelper2.ExecuteReader(conn, CommandType.Text, sql, parms)); }
//-------------------------使用用户创建的connection public static int ExecuteNonQuery(SqlConnection conn, string sql, params SqlParameter[] parms) { return(SqlHelper2.ExecuteNonQuery(conn, CommandType.Text, sql, parms)); }
//-------------------------使用用户创建的connection public static int ExecuteNonQuery(SqlConnection conn, string procName, params SqlParameter[] parms) { return(SqlHelper2.ExecuteNonQuery(conn, CommandType.StoredProcedure, procName, parms)); }
public Synchro64DAL(SqlHelper2 db) : base(db) { }
public string CreateOrEdit(dynamic requestData) { LogHelper.WriteMsgByDay("ZhuanTiController-CreateOrEdit-log:" + requestData.ToString()); ReturnJson r = new ReturnJson(); int zhuantiid = requestData.ZhuanTiId; //专题id,新建时为0,编辑时为实际id string action = requestData.Action; //接口类型 分Create Edit string zhuantiname = requestData.ZhuanTiName; //专题名称 var contentList = requestData.ContentList; //专题内容列表 if (string.IsNullOrEmpty(zhuantiname)) { r.message = "专题名称不能为空"; } else if (zhuantiname.Length > 14) { zhuantiname = zhuantiname.Substring(0, 14); } else { string sql = ""; sql = string.Format("SELECT 1 FROM dbo.CpkZhuanTi WHERE ZhuanTiName = '{0}' ", zhuantiname); int c = dataContext.GetCount(CommandType.Text, sql); if (action == "Create") { if (c > 0) //判断是否存在 { r.message = "该专题名称已存在"; } } else { if (c > 1) //判断是否存在 { r.message = "该专题名称已存在"; } } if (string.IsNullOrEmpty(r.message)) //以上验证都通过时 { //现将该专题已存在的内容及菜品关系 逻辑删除 sql = string.Format(@"UPDATE dbo.CpkZhuanTiContent SET IsEnable = 0 WHERE ZhuanTiId ={0}", zhuantiid); SqlHelper2.ExecuteNonQuery(CommandType.Text, sql); foreach (var item in contentList) //循环内容列表 { string content = item["Content"].ToString().Replace("\"", "'"); //富文本内容 string caiPinId = item["CaiPinId"]; //内容关联的菜品 //LogHelper.WriteMsgByDay("CaiPinController-content-log:" + content); if (content.IndexOf("base64") > 0) { foreach (string a in ImageHandle.GetHtmlImageUrlList(content)) { //LogHelper.WriteMsgByDay("CaiPinController-string a-log:" + a); //LogHelper.WriteMsgByDay("CaiPinController-Common.ImageHandle.DNS-log:" + Common.ImageHandle.DNS); if (a.IndexOf(Common.ImageHandle.DNS) < 0) { string[] asplit = a.Split(','); //LogHelper.WriteMsgByDay("CaiPinController-asplit-log:" + asplit.ToString()); string imgtype = asplit[0].Substring(11, asplit[0].Length - 18); //LogHelper.WriteMsgByDay("CaiPinController-imgtype-log:" + imgtype); string filepath = Common.ImageHandle.Base64StringToImage(asplit[1], imgtype, "/Images"); content = content.Replace(a, filepath); } } } //新增编辑都是如下逻辑,因为编辑时上面已逻辑删除之前的内容 //新增专题基本信息 CpkZhuanTi m = new CpkZhuanTi(); m.ZhuanTiId = zhuantiid; m.ZhuanTiName = zhuantiname; m.UpdateDate = DateTime.Now; db.CpkZhuanTi.AddOrUpdate(m); //根据zhuantiid新增或者编辑 db.SaveChanges(); zhuantiid = m.ZhuanTiId; //新增专题内容 CpkZhuanTiContent mContent = new CpkZhuanTiContent(); mContent.ZtContentId = 0; mContent.ZhuanTiId = zhuantiid; mContent.Content = content; db.CpkZhuanTiContent.AddOrUpdate(mContent); db.SaveChanges(); // sql = string.Format(@"INSERT INTO dbo.CpkZhuanTiContent // ( ZhuanTiId , Content) // VALUES ( {0} , '{1}' ); select SCOPE_IDENTITY() ", zhuantiid, content); // int ztContentId = Convert.ToInt32(SqlHelper2.ExecuteScalar(CommandType.Text, sql)); int ztContentId = mContent.ZtContentId; //新增专题内容关联的菜品 sql = string.Format(@"INSERT INTO dbo.CpkCpZtContentRelation ( ZtContentId , CaiPinId ) SELECT {0},CaiPinId FROM dbo.CpkCaiPinBasicInfo WHERE CaiPinId IN ({1}); DELETE FROM dbo.CpkZhuanTiContent WHERE ZhuanTiId ={2} AND IsEnable = 0 ", ztContentId, caiPinId, zhuantiid); SqlHelper2.ExecuteNonQuery(CommandType.Text, sql); } r.status = "succ"; r.message = action + "成功"; } } return(JsonConvert.SerializeObject(r)); }
public static SqlDataReader Select(string procName, params SqlParameter[] parms) { return(SqlHelper2.ExecuteReader(connStr, CommandType.StoredProcedure, procName, parms)); }