//获取流水记录
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);
}