//获取流水记录 public DataTable SelAll(int puserid, DateTime time) { string stime, etime; B_User_UnitWeek.GetWeekSE(time, out stime, out etime); SqlParameter[] sp = new SqlParameter[] { new SqlParameter("stime", stime), new SqlParameter("etime", etime) }; string sql = "Select * From " + TbName + " Where CDate BetWeen @stime AND @etime And PUserID=" + puserid; return(SqlHelper.ExecuteTable(CommandType.Text, sql, sp)); }
//更新分成金额 public void UpdateUnit(M_User_UnitWeek pmodel, double percent, DateTime time) { string stime, etime; B_User_UnitWeek.GetWeekSE(time, out stime, out etime); SqlParameter[] sp = new SqlParameter[] { new SqlParameter("stime", stime), new SqlParameter("etime", etime), new SqlParameter("remind", DateTime.Now) }; if (pmodel.PUserID == 0 && !string.IsNullOrEmpty(pmodel.ChildIDS.Replace(",", ""))) { pmodel.ChildIDS = pmodel.ChildIDS.Trim(','); string sql = "Update " + TbName + " Set UnitPercent=" + percent + ",UnitAmount=AMount*" + percent + ",Remind=@remind Where UserID IN(" + pmodel.ChildIDS + ") And CDate BetWeen @stime AND @etime"; SqlHelper.ExecuteSql(sql, sp); } }
/// <summary> /// 按时间获取消费信息,为空则取全部的消费记录,同用户的消费记录聚合为一个 /// </summary> /// <param name="time">输入则自动获取该周的起始与结束时间</param> /// <returns></returns> public DataTable SelByTime(string time = "") { string stime, etime; B_User_UnitWeek.GetWeekSE(DataConvert.CDate(time), out stime, out etime); SqlParameter[] sp = new SqlParameter[] { new SqlParameter("stime", stime), new SqlParameter("etime", etime) }; string fields = "A.UserID,A.UserName,A.ParentUserID,A.GroupID,A.HoneyName,B.AMount"; string sql = "Select " + fields + " FROM ZL_User AS A LEFT JOIN" + " (SELECT UserID,Sum(AMount)AS AMount FROM " + TbName + " WHERE 1=1 "; if (!string.IsNullOrEmpty(time)) { sql += " AND CDate BetWeen @stime AND @etime"; } sql += " AND UserID>0 GROUP BY UserID)B ON A.UserID=B.UserID"; return(SqlHelper.ExecuteTable(CommandType.Text, sql, sp)); }
public PageSetting SelPage(int cpage, int psize, int puserid = -100, DateTime?time = null) { string where = " 1=1"; List <SqlParameter> sp = new List <SqlParameter>(); if (puserid != -100) { where += " AND PUserID=" + puserid; } if (time != null) { string stime, etime; B_User_UnitWeek.GetWeekSE((DateTime)time, out stime, out etime); where += " AND CDate BETWEEN @stime AND @etime"; sp.Add(new SqlParameter("stime", stime)); sp.Add(new SqlParameter("etime", etime)); } PageSetting setting = PageSetting.Single(cpage, psize, TbName, PK, where, "", sp); DBCenter.SelPage(setting); return(setting); }