public ActionResult ClubList(Dictionary <string, string> queryvalues)
{
string _target = queryvalues.ContainsKey("target") ? queryvalues["target"] : "";
int _id = queryvalues.ContainsKey("SearchExter") ? string.IsNullOrWhiteSpace(queryvalues["SearchExter"]) ? 0 : Convert.ToInt32(queryvalues["SearchExter"]) : 0;
int Search = queryvalues.ContainsKey("Search") ? string.IsNullOrWhiteSpace(queryvalues["Search"]) ? 0 : Convert.ToInt32(queryvalues["Search"]) : 0;
int _page = queryvalues.ContainsKey("page") ? Convert.ToInt32(queryvalues["page"]) : 1;
int page2 = queryvalues.ContainsKey("page2") ? Convert.ToInt32(queryvalues["page2"]) : 1;
string _StartDate = queryvalues.ContainsKey("StartDate") ? queryvalues["StartDate"] : DateTime.Now.ToString("yyyy-MM-dd 00:00:00");
string _ExpirationDate = queryvalues.ContainsKey("ExpirationDate") ? queryvalues["ExpirationDate"] : DateTime.Now.AddDays(1).ToString("yyyy-MM-dd 00:00:00");
GameRecordView model = new GameRecordView {
UserID = _id, SearchExt = Session["name"].ToString(), SearchExter = _id, StartDate = _StartDate, ExpirationDate = _ExpirationDate, Page = _page
};
ViewData["pageindex"] = _page;
ViewData["pageindex2"] = page2;
if (_target == "box")
{
int _pageGroup = queryvalues.ContainsKey("pageGroup") ? Convert.ToInt32(queryvalues["pageGroup"]) : 1;
ViewData["ClubList_PageList"] = ClubBLL.GetMyRebate(model);
return(PartialView("ClubList_PageList", ViewData["ClubList_PageList"]));
}
else if ((_target == "box2"))
{
ViewData["hid"] = "1";
model.Page = page2;
model.SearchExter = Search;
model.UserID = Search;
ViewData["ClubListDetail"] = ClubBLL.GetMyRebateDetail(model);
return(PartialView("ClubListDetail", ViewData["ClubListDetail"]));
}
else
{
int btn = queryvalues.ContainsKey("btn") ? string.IsNullOrWhiteSpace(queryvalues["btn"]) ? 0 : Convert.ToInt32(queryvalues["btn"]) : 0;
int _pageGroup = queryvalues.ContainsKey("pageGroup") ? Convert.ToInt32(queryvalues["pageGroup"]) : 1;
//Search
ViewData["ClubList_PageList"] = ClubBLL.GetMyRebate(model);
PagedList <UserClubDetail> obj = new PagedList <UserClubDetail>(
new List <UserClubDetail>()
, 1, 1, 1);
ViewData["ClubListDetail"] = obj;
if (btn == 1)
{
ViewData["hid"] = "1";
int tmp = model.SearchExter;
model.SearchExter = Search;
model.UserID = Search;
ViewData["ClubListDetail"] = ClubBLL.GetMyRebateDetail(model);
model.SearchExter = tmp;
model.UserID = tmp;
}
return(View(model));
}
}
public ActionResult PotCtrlList(Dictionary <string, string> queryvalues)
{
int _id = queryvalues.ContainsKey("UserID") ? string.IsNullOrWhiteSpace(queryvalues["UserID"]) ? 0 : Convert.ToInt32(queryvalues["UserID"]) : 0;
string _StartDate = queryvalues.ContainsKey("StartDate") ? queryvalues["StartDate"] : DateTime.Now.ToString("yyyy-MM-dd 00:00:00");
string _ExpirationDate = queryvalues.ContainsKey("ExpirationDate") ? queryvalues["ExpirationDate"] : DateTime.Now.AddDays(1).ToString("yyyy-MM-dd 00:00:00");
int page = queryvalues.ContainsKey("page") ? Convert.ToInt32(queryvalues["page"]) : 1;
string _search = queryvalues.ContainsKey("SearchExt") ? queryvalues["SearchExt"].ToString() : "";
GameRecordView grv = new GameRecordView {
Page = page, SearchExt = _search, UserID = _id, StartDate = _StartDate, ExpirationDate = _ExpirationDate
};
if (Request.IsAjaxRequest())
{
return(PartialView("PotCtrlList_PageList", ScaleRecordBLL.GetListByPage(grv)));
}
PagedList <ScaleRecord> model = ScaleRecordBLL.GetListByPage(grv);
grv.DataList = model;
return(View(grv));
}
public static PagedList <UserMoneyRecord> Get007ListByPage(GameRecordView grv, out int recordCount)
{
PagerQuery pq = new PagerQuery();
pq.CurrentPage = grv.Page;
pq.PageSize = 10;
// pq.RecordCount = DAL.PagedListDAL<UserMoneyRecord>.GetRecordCount(string.Format(@"
//select count(0) from record.BG_UserMoneyRecord where UserID in (select ID from Role where ID = '{0}' or Account='{0}' or NickName='{0}') and CreateTime between '{1}' and '{2}' ", grv.SearchExt, grv.StartDate, grv.ExpirationDate), sqlconnectionString);
pq.RecordCount = DAL.PagedListDAL <UserMoneyRecord> .GetRecordCount(string.Format(@"SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
select count(0) from " + database3 + @".BG_UserMoneyRecord where UserID in (select ID from " + database1 + @".Role where agent = 10010) and CreateTime between '{1}' and '{2}' and Type in (126,138) ", grv.SearchExt, grv.StartDate, grv.ExpirationDate), sqlconnectionString);
//pq.Sql = string.Format(@"select u.* ,ifnull(s.UserOper ,concat(u.Type,'未定义描述')) UserOper, r.NickName as UserName from (select * from record.BG_UserMoneyRecord where UserID = (select ID from Role where ID = '{2}' or Account= '{2}' or NickName= '{2}' limit 1) and CreateTime between '{3}' and '{4}' order by CreateTime desc limit {0}, {1}) as u left join record.S_Desc s on u.Type = s.Type left join Role as r on u.UserID = r.ID", pq.StartRowNumber, pq.PageSize, grv.SearchExt, grv.StartDate, grv.ExpirationDate);
pq.Sql = string.Format(@"SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
select u.* ,ifnull(s.UserOper ,concat(u.Type,'未定义描述')) UserOper
from (
select a.* ,b.NickName as UserName from " + database3 + @".BG_UserMoneyRecord a
join " + database1 + @".Role b on a.userid = b.id and b.Agent=10010
where a.CreateTime between '{3}' and '{4}' and Type in(126 ,138) order by CreateTime desc limit {0}, {1}
) as u left join " + database3 + @".S_Desc s on u.Type = s.Type
", pq.StartRowNumber, pq.PageSize, grv.SearchExt, grv.StartDate, grv.ExpirationDate);
PagedList <UserMoneyRecord> obj = new PagedList <UserMoneyRecord>(DAL.PagedListDAL <UserMoneyRecord> .GetListByPage(pq, sqlconnectionString), pq.CurrentPage, pq.PageSize, pq.RecordCount);
recordCount = pq.RecordCount;
return(obj);
}
public ActionResult PotGold(Dictionary <string, string> queryvalues)
{
// gametype ChipGold ChipStandard
int gametype = queryvalues.ContainsKey("gametype") ? Convert.ToInt32(queryvalues["gametype"]) : 0;
decimal ChipGold = queryvalues.ContainsKey("ChipGold") ? Convert.ToInt32(queryvalues["ChipGold"]) : 0;
decimal ChipStandard = queryvalues.ContainsKey("ChipStandard") ? Convert.ToInt32(queryvalues["ChipStandard"]) : 0;
GameRecordView vbd = new GameRecordView
{
};
List <PotGold> data = new List <PotGold>(RobotBLL.GetPotGoldList());
if (gametype != 0)
{
data.RemoveAll(m => m.GameType == gametype);
data.Add(new PotGold()
{
GameType = gametype, Gold = ChipGold, Standard = ChipStandard
});
}
vbd.DataList = data.OrderBy(m => m.GameType).ToList();
return(View(vbd));
}
public ActionResult MemberForSearchGameRecord(Dictionary <string, string> queryvalues)
{
int _id = queryvalues.ContainsKey("UserID") ? string.IsNullOrWhiteSpace(queryvalues["UserID"]) ? 0 : Convert.ToInt32(queryvalues["UserID"]) : 0;
int _page = queryvalues.ContainsKey("page") ? Convert.ToInt32(queryvalues["page"]) : 1;
string _StartDate = queryvalues.ContainsKey("StartDate") ? queryvalues["StartDate"] : DateTime.Now.ToString("yyyy-MM-dd 00:00:00");
string _ExpirationDate = queryvalues.ContainsKey("ExpirationDate") ? queryvalues["ExpirationDate"] : DateTime.Now.AddDays(1).ToString("yyyy-MM-dd 00:00:00");
string _SearchExt = queryvalues.ContainsKey("SearchExt") ? queryvalues["SearchExt"] : "";
string hidDataCount = queryvalues.ContainsKey("hidDataCount") ? queryvalues["hidDataCount"] : "";
int MasterID = User.Identity.GetUserId <int>();
string _MasterList = AgentUserBLL.GetUserListString(MasterID);
GameRecordView grv = new GameRecordView {
SearchExt = _SearchExt, StartDate = _StartDate, ExpirationDate = _ExpirationDate, Page = _page, UserList = _MasterList
};
if (Request.IsAjaxRequest())
{
PagedList <UserMoneyRecord> data = UserMoneyRecordBLL.GetListByPageForAgent(grv);
return(PartialView("MemberForSearchGameRecord_PageList", data));
}
grv.DataList = UserMoneyRecordBLL.GetListByPageForAgent(grv);
grv.Data = UserMoneyRecordBLL.GetUserInfo(grv);
return(View(grv));
}
public ActionResult GameLog(Dictionary <string, string> queryvalues)
{
int _id = queryvalues.ContainsKey("UserID") ? string.IsNullOrWhiteSpace(queryvalues["UserID"]) ? 0 : Convert.ToInt32(queryvalues["UserID"]) : 0;
ViewData["UserId"] = _id;
int _page = queryvalues.ContainsKey("page") ? Convert.ToInt32(queryvalues["page"]) : 1;
int _seachtype = queryvalues.ContainsKey("seachtype") ? Convert.ToInt32(queryvalues["seachtype"]) : 0;
string _StartDate = queryvalues.ContainsKey("StartDate") ? queryvalues["StartDate"] : DateTime.Now.ToString("yyyy-MM-dd 00:00:00");
string _ExpirationDate = queryvalues.ContainsKey("ExpirationDate") ? queryvalues["ExpirationDate"] : DateTime.Now.AddDays(1).ToString("yyyy-MM-dd 00:00:00");
string _SearchExt = queryvalues.ContainsKey("SearchExt") ? queryvalues["SearchExt"] : "";
string _Gametype = queryvalues.ContainsKey("Gametype") ? queryvalues["Gametype"] : "0";
object _data = queryvalues.ContainsKey("Data") ? string.IsNullOrWhiteSpace(queryvalues["Data"]) ? 0 : Convert.ToInt64(queryvalues["Data"]) : 0;
int _roundID = queryvalues.ContainsKey("RoundID") ? string.IsNullOrWhiteSpace(queryvalues["RoundID"]) ? 0 : Convert.ToInt32(queryvalues["RoundID"]) : 0;
int _roundID2 = queryvalues.ContainsKey("RoundID2") ? string.IsNullOrWhiteSpace(queryvalues["RoundID2"]) ? 0 : Convert.ToInt32(queryvalues["RoundID2"]) : 0;
int _RoundID3 = queryvalues.ContainsKey("RoundID3") ? string.IsNullOrWhiteSpace(queryvalues["RoundID3"]) ? 0 : Convert.ToInt32(queryvalues["RoundID3"]) : 0;
GameRecordView grv = new GameRecordView {
Gametype = Convert.ToInt32(_Gametype), Data = _data, UserID = _id, SearchExt = _SearchExt, StartDate = _StartDate, ExpirationDate = _ExpirationDate, Page = _page, SeachType = (seachType)_seachtype, RoundID = _roundID, RoundID2 = _roundID2
};
string pageList = "";
switch (_Gametype)
{
case "1": //德州扑克
grv.DataList = MemberCenterBLL.GetListByPageForTexas(grv);
pageList = "TexasGameLog_PageList";
break;
case "2": //中发白
grv.DataList = MemberCenterBLL.GetListByPageForScale(grv);
pageList = "ScaleGameLog_PageList";
break;
case "3": //十二生肖
grv.DataList = GameDataBLL.GetListByPageForZodiac(grv);
pageList = "ZodiacGameLog_PageList";
break;
case "5": //奔驰宝马
grv.DataList = GameDataBLL.GetListByPageForCar(grv);
pageList = "CarGameLog_PageList";
break;
case "6": //百人德州
grv.DataList = GameDataBLL.GetListByPageForTexPro(grv);
pageList = "TexProGameLog_PageList";
break;
case "9": //百家乐 ShuihuGameRecord BaccaratGameRecord
grv.DataList = GameDataBLL.GetListByRoundForBaiJiaLe(grv);
pageList = "BaijialeGameLog_PageList";
break;
}
grv.PageList = pageList;
if (Request.IsAjaxRequest())
{
return(PartialView(pageList, grv.DataList));
}
return(View(grv));
}
internal static IEnumerable <UserFishInfo> GetUserInfo(GameRecordView vbd)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
StringBuilder str = new StringBuilder();
//str.Append(@"SELECT count(f.FishID) as Num, f.FishID, `Type` FROM FishInfoRecord f,Role r
// where f.UserID = r.ID and ( r.ID =@SearchExt or r.NickName=@SearchExt or r.Account=@SearchExt ) and
// f.CreateTime between @StartDate and @ExpirationDate
// group by f.FishID, `Type`;");
if (vbd.SearchExt == "")
{
str.Append(@"
select a.Type ,case a.Type when 1 then '被赠送' when 2 then '购买' when 3 then '放生' when 0 then '赠送给' end Oper,sum(b.FishPrice) FishPrice
,sum(case a.FishID when 1 then 1 else 0 end ) Fish1 ,sum(case a.FishID when 2 then 1 else 0 end ) Fish2
,sum(case a.FishID when 3 then 1 else 0 end ) Fish3 ,sum(case a.FishID when 4 then 1 else 0 end ) Fish4
,sum(case a.FishID when 5 then 1 else 0 end ) Fish5,sum(case a.FishID when 6 then 1 else 0 end ) Fish6
from " + database1 + @".FishInfoRecord a join " + database3 + @".S_FishInfo b on a.FishID = b.FishID
join " + database1 + @".Role r on a.UserID = r.id and r.agent <> 10010
where a.Type in (0,1,2,3) and a.CreateTime between @StartDate and @ExpirationDate and a.CreateTime>='2016-05-28 06:00:00'
group by a.Type ,case a.Type when 1 then '被赠送' when 2 then '购买' when 3 then '放生' when 0 then '赠送给' end
union all
select 5 ,'用户鱼缸' ,sum(case a.Type when 3 then (FishPrice * -1) else b.FishPrice end) FishPrice
,sum(case a.FishID when 1 then 1 else 0 end ) Fish1 ,sum(case a.FishID when 2 then 1 else 0 end ) Fish2
,sum(case a.FishID when 3 then 1 else 0 end ) Fish3 ,sum(case a.FishID when 4 then 1 else 0 end ) Fish4
,sum(case a.FishID when 5 then 1 else 0 end ) Fish5,sum(case a.FishID when 6 then 1 else 0 end ) Fish6
from FishInfoRecord a join " + database3 + @".S_FishInfo b on a.FishID = b.FishID
join " + database1 + @".Role r on a.UserID = r.id and r.agent <> 10010
where a.Type in (1,2,3) and a.Flag = 1 and a.CreateTime between @StartDate and @ExpirationDate ;
");
}
else
{
str.Append(@"
select a.Type ,case a.Type when 1 then '被赠送' when 2 then '购买' when 3 then '放生' when 0 then '赠送给' end Oper,sum(b.FishPrice) FishPrice
,sum(case a.FishID when 1 then 1 else 0 end ) Fish1 ,sum(case a.FishID when 2 then 1 else 0 end ) Fish2
,sum(case a.FishID when 3 then 1 else 0 end ) Fish3 ,sum(case a.FishID when 4 then 1 else 0 end ) Fish4
,sum(case a.FishID when 5 then 1 else 0 end ) Fish5,sum(case a.FishID when 6 then 1 else 0 end ) Fish6
from " + database1 + @".FishInfoRecord a join " + database3 + @".S_FishInfo b on a.FishID = b.FishID
join " + database1 + @".Role r on a.UserID = r.id and r.agent <> 10010
where a.Type in (0,1,2,3) and a.CreateTime between @StartDate and @ExpirationDate and a.userid = @SearchExt and a.CreateTime>='2016-05-28 06:00:00'
group by a.Type ,case a.Type when 1 then '被赠送' when 2 then '购买' when 3 then '放生' when 0 then '赠送给' end
union all
select 5 ,'用户鱼缸' ,sum(case a.Type when 3 then (FishPrice * -1) else b.FishPrice end) FishPrice
,sum(case a.FishID when 1 then 1 else 0 end ) Fish1 ,sum(case a.FishID when 2 then 1 else 0 end ) Fish2
,sum(case a.FishID when 3 then 1 else 0 end ) Fish3 ,sum(case a.FishID when 4 then 1 else 0 end ) Fish4
,sum(case a.FishID when 5 then 1 else 0 end ) Fish5,sum(case a.FishID when 6 then 1 else 0 end ) Fish6
from FishInfoRecord a join " + database3 + @".S_FishInfo b on a.FishID = b.FishID
join " + database1 + @".Role r on a.UserID = r.id and r.agent <> 10010
where a.Type in (1,2,3) and a.Flag = 1 and a.CreateTime between @StartDate and @ExpirationDate and a.userid = @SearchExt ;
");
}
IEnumerable <UserFishInfo> i = cn.Query <UserFishInfo>(str.ToString(), vbd);
cn.Close();
return(i);
}
}
internal static UserInfo GetUserInfo007(GameRecordView vbd)
{
if (!string.IsNullOrEmpty(vbd.SearchExt))
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
StringBuilder str = new StringBuilder();
str.Append(@" select a.ID ,a.CreateTime ,a.CreateIP ,IFNULL(sum(b.Money), 0) ,count(b.userid) ,a.Gold as money
from Role a
left
join QQZoneRecharge b on b.CreateTime between @StartDate and @ExpirationDate and b.UserID = a.id
where(a.ID = @SearchExt or a.Account = @SearchExt or a.NickName = @SearchExt) and find_in_set(a.Agent, @UserList) and a.Agent = 10010
group by a.ID ,a.CreateTime ,a.CreateIP ,a.Gold;");
UserInfo i = cn.Query <UserInfo>(str.ToString(), vbd).FirstOrDefault();
cn.Close();
return(i);
}
}
else
{
return(new UserInfo {
CreateTime = DateTime.Today, CreateIP = "127.0.0.1"
});
}
}
public static IEnumerable <BoardDetail_Right> GetBoardDetail_RightList(GameRecordView model)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
StringBuilder str = new StringBuilder();
str.Append(@"
select a.CreateTime ," + model.Gametype + @" as GameID ,a.BoardID RoomCategory ,ifnull(sum(BoardNum),0) BoardNum ,ifnull(sum(BoardCount),0) BoardCount
,ifnull(sum(BoardTime),0) BoardTime ,ifnull(sum(BetVal),0) BetVal ,ifnull(sum(CallBack),0) CallBack
from (
select date_add('" + model.StartDate + @"' ,interval a.id day) CreateTime ,BoardID from " + database3 + @".S_Ordinal a
join (select distinct BoardID from " + database3 + @".S_Board where BoardID in (" + model.SearchExt + @")) t on 1 = 1
where a.id < datediff('" + model.ExpirationDate + @"' ,'" + model.StartDate + @"')
)a left join (
select a.CountDate AS CreateTime ,GameType AS GameID ,BoardID AS RoomCategory ,sum(BoardVal) AS BoardNum ,sum(CountVal) AS BoardCount ,
sum(TimeVal) AS BoardTime ,sum(BetVal) AS BetVal ,sum(RakeVal) AS CallBack
from " + database3 + @".Clearing_GameTotal a join " + database3 + @".S_Board b on b.ChildBoard = a.BetType and b.BoardID in (" + model.SearchExt + @")
where a.CountDate >='" + model.StartDate + @"' and a.CountDate < '" + model.ExpirationDate + @"' and a.GameType = " + model.Gametype + @" and a.UserType = " + model.UserType + @" and a.agent = a.agent
group by a.CountDate,GameType,BoardID
)b on b.CreateTime = a.CreateTime and a.BoardID = RoomCategory group by a.CreateTime,a.BoardID ;
");
IEnumerable <BoardDetail_Right> i = cn.Query <BoardDetail_Right>(str.ToString());
cn.Close();
return(i);
}
}
public static long GetListForCaryinkui(int zID)
{
IEnumerable <CarGameRecord> data = null;
long score = 0;
GameRecordView vbd = new GameRecordView {
StartDate = DateTime.Now.AddDays(-1).Date.ToString("yyyy-MM-dd 00:00:00"), ExpirationDate = DateTime.Now.AddDays(1).Date.ToString("yyyy-MM-dd 00:00:00")
};
using (var cn = new MySqlConnection(sqlconnectionStringForRecord))
{
cn.Open();
StringBuilder str = new StringBuilder();
str.Append("select * from BG_CarGameRecord where CreateTime between '" + vbd.StartDate + "' and '" + vbd.ExpirationDate + "' and CreateTime!='" + vbd.ExpirationDate + "' order by CreateTime desc ");
data = cn.Query <CarGameRecord>(str.ToString());
cn.Close();
}
foreach (CarGameRecord m in data)
{
if (int.Parse(m.UserData.Split(',').ToList()[0]) == zID)
{
score = score + int.Parse(m.UserData.Split(',').ToList()[5]); //庄家输赢
}
}
return(score);
}
public static PagedList <ExpRecord> GetListByPage(GameRecordView model)
{
PagerQuery pq = new PagerQuery();
pq.CurrentPage = model.Page;
pq.PageSize = 10;
if (!string.IsNullOrEmpty(model.SearchExt))
{
pq.RecordCount = PagedListDAL <ExpRecord> .GetRecordCount(string.Format(@"select count(0) from " + database3 + ".BG_ExpRecord where CreateTime between '{0}' and '{1}' and UserID in (select ID from Role where ID = '{2}' or Account='{2}' or NickName='{2}')", model.StartDate, model.ExpirationDate, model.SearchExt), sqlconnectionString);
pq.Sql = string.Format(@"select aa.*, Role.NickName as UserName from (select * from " + database3 + @".BG_ExpRecord where CreateTime between '{2}' and '{3}' and UserID in (select ID from Role where ID = '{4}' or Account='{4}' or NickName='{4}') order by CreateTime desc limit {0}, {1}) as aa inner join Role on aa.UserID = Role.ID", pq.StartRowNumber, pq.PageSize, model.StartDate, model.ExpirationDate, model.SearchExt);
}
else
{
pq.RecordCount = PagedListDAL <ExpRecord> .GetRecordCount(string.Format(@"select count(0) from " + database3 + @".BG_ExpRecord where CreateTime between '{0}' and '{1}'", model.StartDate, model.ExpirationDate), sqlconnectionString);
pq.Sql = string.Format(@"select aa.*, Role.NickName as UserName from (select * from " + database3 + @".BG_ExpRecord where CreateTime between '{2}' and '{3}' order by CreateTime desc limit {0}, {1}) as aa inner join Role on aa.UserID = Role.ID", pq.StartRowNumber, pq.PageSize, model.StartDate, model.ExpirationDate);
}
PagedList <ExpRecord> obj = new PagedList <ExpRecord>(PagedListDAL <ExpRecord> .GetListByPage(pq, sqlconnectionString), pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(obj);
}
public ActionResult PotRobotFlow(Dictionary <string, string> queryvalues)
{
int _page = queryvalues.ContainsKey("page") ? Convert.ToInt32(queryvalues["page"]) : 1;
string _StartDate = queryvalues.ContainsKey("StartDate") ? queryvalues["StartDate"] : DateTime.Now.ToString("yyyy-MM-dd 00:00:00");
string _ExpirationDate = queryvalues.ContainsKey("ExpirationDate") ? queryvalues["ExpirationDate"] : DateTime.Now.AddDays(1).ToString("yyyy-MM-dd 00:00:00");
string hidDataCount = queryvalues.ContainsKey("hidDataCount") ? queryvalues["hidDataCount"] : "";
UserInfo u = null;
GameRecordView grv = new GameRecordView {
StartDate = _StartDate, ExpirationDate = _ExpirationDate, Page = _page
};
if (Request.IsAjaxRequest())
{
PagedList <UserMoneyRecord> data2 = UserMoneyRecordBLL.Get007ListByPage(grv, Convert.ToInt32(hidDataCount));
ViewData["totalData"] = hidDataCount;
return(PartialView("PotRobotFlow_PageList", data2));
}
int total;
PagedList <UserMoneyRecord> data = UserMoneyRecordBLL.Get007ListByPage(grv, out total);
ViewData["totalData"] = total;
grv.DataList = data;
return(View(grv));
}
public static PagedList <Rebate> GetListByPage(GameRecordView bdv)
{
PagerQuery pq = new PagerQuery();
pq.CurrentPage = bdv.Page;
pq.PageSize = 10;
if (string.IsNullOrEmpty(bdv.SearchExt))
{
bdv.SearchExt = "0";
}
pq.RecordCount = PagedListDAL <Rebate> .GetRecordCount(string.Format(@"
select count(0) from " + database3 + @".BG_PointRecord where (Type = 1 or Type=2) and CreateTime>='" + bdv.StartDate + "' and CreateTime<'" + bdv.ExpirationDate + "' and UserID=" + bdv.SearchExt
), sqlconnectionString);
//pq.Sql = string.Format(@"select * from gamedata.SpellCardRecord where Time>='" + bdv.StartDate + "' and Time<'" + bdv.ExpirationDate + "' {2} order by Time desc limit {0}, {1}", pq.StartRowNumber, pq.PageSize, string.IsNullOrEmpty(bdv.SearchExt) ? "" : " and PlayerID='" + bdv.SearchExt + "'");
//pq.RecordCount = 20;
pq.Sql = string.Format(@"
select b.CreateTime ,a.id as UserID ,NickName ,b.PointNum TotalRebate ,b.PointChange ChangeRebate ,case b.Type when 1 then '德州获取' when 2 then '德州消耗' else '' end as Oper
from (select id ,nickname from " + database1 + @".Role a where a.id = {2}) a
join " + database3 + @".BG_PointRecord b on a.id = b.UserID and b.CreateTime >= '{3}' and b.CreateTime < '{4}' and (b.Type = 1 or b.Type = 2 )
order by CreateTime desc limit {0}, {1}"
, pq.StartRowNumber, pq.PageSize, bdv.SearchExt, bdv.StartDate, bdv.ExpirationDate);
PagedList <Rebate> obj = new PagedList <Rebate>(PagedListDAL <Rebate> .GetListByPage(pq, sqlconnectionString), pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(obj);
}
public static PagedList <ItemRecord> GetItemRecordList(GameRecordView model)
{
PagerQuery pq = new PagerQuery();
pq.CurrentPage = model.Page;
pq.PageSize = 10;
pq.RecordCount = PagedListDAL <ItemRecord> .GetRecordCount(string.Format(@"select count(0) from " + database3 + @".BG_ItemRecord
where CreateTime between '{0}' and '{1}' and UserID = case when {2} = 0 then UserID else {2} end and ItemID = case when {3} = 0 then ItemID else {3} end "
, model.StartDate, model.ExpirationDate, model.SearchExt, model.ItemID), sqlconnectionString);
pq.Sql = string.Format(@"select aa.CreateTime, aa.UserID , Role.NickName as UserName ,ifnull(c.UserOper ,aa.ChangeType) ChangeType
,ifnull(t.TempalteName ,aa.ItemID) TemplateName ,aa.NowNum ,aa.OldNum ,aa.ItemID ItemName
from (
select * from " + database3 + @".BG_ItemRecord where CreateTime between '{2}' and '{3}' and UserID = case when {4} = 0 then UserID else {4} end
and ItemID = case when {5} = 0 then ItemID else {5} end order by CreateTime desc limit {0}, {1}
) as aa
join Role on aa.UserID = Role.ID left join " + database3 + @".S_Desc c on aa.ChangeType = c.Type
left join " + database1 + @".S_Template t on aa.ItemID = t.TempalteID and t.TemplateType = 0 ;
"
, pq.StartRowNumber, pq.PageSize, model.StartDate, model.ExpirationDate, model.SearchExt, model.ItemID);
PagedList <ItemRecord> obj = new PagedList <ItemRecord>(PagedListDAL <ItemRecord> .GetListByPage(pq, sqlconnectionString), pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(obj);
}
public static IEnumerable <ThanksRank> GetThanksRankList(GameRecordView model)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
StringBuilder str = new StringBuilder();
str.Append(@"
select UserID,r.NickName,
sum(
case a.ModeluID
when 4 then 150000 *ClickCount
when 5 then 360000 *ClickCount
when 6 then 750000 *ClickCount
when 7 then 1980000 *ClickCount
else 0
end
) AS TotalMoney
from " + database3 + @".BG_ClickRecord as a," + database1 + @".Role as r
where ModeluID in (4,5,6,7) and a.UserID = r.ID
and a.CreateTime>='" + model.StartDate + @"' and a.CreateTime<'" + model.ExpirationDate + @"'
GROUP BY UserID
ORDER BY TotalMoney desc
limit 100
;
");
IEnumerable <ThanksRank> i = cn.Query <ThanksRank>(str.ToString());
cn.Close();
return(i);
}
}
public static long GetListForHundredyinkui(int zID)
{
long score = 0;
IEnumerable <ScaleGameRecord> data = null;
GameRecordView vbd = new GameRecordView {
StartDate = DateTime.Now.AddDays(-1).Date.ToString("yyyy-MM-dd 00:00:00"), ExpirationDate = DateTime.Now.AddDays(1).Date.ToString("yyyy-MM-dd 00:00:00")
};
using (var cn = new MySqlConnection(sqlconnectionStringForRecord))
{
cn.Open();
StringBuilder str = new StringBuilder();
str.Append("select * from " + database3 + @".BG_TexProGameRecord where CreateTime between '" + vbd.StartDate + "' and '" + vbd.ExpirationDate + "' and CreateTime!='" + vbd.ExpirationDate + "' order by CreateTime desc ");
data = cn.Query <ScaleGameRecord>(str.ToString());
cn.Close();
}
foreach (ScaleGameRecord m in data)
{
if (int.Parse(m.UserData.Split(',').ToList()[0]) == zID)
{
List <string> wanjia = m.UserData.Split('_').ToList();
wanjia.RemoveAt(0); //出去庄家
wanjia.RemoveAt(wanjia.Count - 1); // 去除最后一个元素 因为是空的
for (int i = 0; i < wanjia.Count; i++)
{
score = score + int.Parse(wanjia[i].Split(',')[2]);
}
}
}
return(score);
}
public static PagedList <ScaleRecord> GetListByPage(GameRecordView grv)
{
PagerQuery pq = new PagerQuery();
pq.CurrentPage = grv.Page;
pq.PageSize = 10;
pq.RecordCount = PagedListDAL <ScaleRecord> .GetRecordCount(string.Format(@"
select count(*) from (
select UserID, GameID, Proj,Content,CreateTime
from " + database3 + @".BG_ScaleRecord
where CreateTime>='" + grv.StartDate + @"' and CreateTime<'" + grv.ExpirationDate + @"'
) as a
where a.CreateTime>='" + grv.StartDate + @"' and a.CreateTime<'" + grv.ExpirationDate + @"' {2} {3}
", pq.StartRowNumber, pq.PageSize, grv.UserID <= 0 ? "" : " and a.UserID=" + grv.UserID, grv.SearchExt == "" ? "" : " and a.Proj='" + grv.SearchExt + "'"), sqlconnectionString);
pq.Sql = string.Format(@"
select a.*,b.NickName as UserName from (
select UserID, GameID, Proj,Content,CreateTime,UpdateValue
from " + database3 + @".BG_ScaleRecord
where CreateTime>='" + grv.StartDate + @"' and CreateTime<'" + grv.ExpirationDate + @"'
) as a LEFT JOIN " + database1 + @".Role as b on a.UserID = b.ID
where a.CreateTime>='" + grv.StartDate + @"' and a.CreateTime<'" + grv.ExpirationDate + @"' {2} {3}
ORDER BY a.CreateTime DESC
limit {0}, {1}
", pq.StartRowNumber, pq.PageSize, grv.UserID <= 0?"":" and a.UserID=" + grv.UserID, grv.SearchExt == ""?"":" and a.Proj='" + grv.SearchExt + "'");
PagedList <ScaleRecord> obj = new PagedList <ScaleRecord>(PagedListDAL <ScaleRecord> .GetListByPage(pq, sqlconnectionString), pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(obj);
}
public static PagedList <UserMoneyRecord> GetListByPageForAgent(GameRecordView grv)
{
PagerQuery pq = new PagerQuery();
pq.CurrentPage = grv.Page;
pq.PageSize = 10;
if (!string.IsNullOrEmpty(grv.SearchExt))
{
pq.RecordCount = DAL.PagedListDAL <UserMoneyRecord> .GetRecordCount(string.Format(@"
select count(0) from " + database3 + @".BG_UserMoneyRecord where UserID = (select ID from Role where ID = '{0}' or Account= '{0}' or NickName= '{0}' and find_in_set(Agent, '{3}') limit 1) and CreateTime between '{1}' and '{2}' ", grv.SearchExt, grv.StartDate, grv.ExpirationDate, grv.UserList), sqlconnectionString);
pq.Sql = string.Format(@"select u.* ,ifnull(s.UserOper ,concat(u.Type,'未定义描述')) UserOper
from (select a.* ,b.NickName as UserName from " + database3 + @".BG_UserMoneyRecord a
join (select ID ,Nickname from " + database1 + @".Role where find_in_set(Agent, '{5}') and (ID = '{2}' or Account= '{2}' or NickName= '{2}') limit 1) b on a.userid = b.id
where CreateTime between '{3}' and '{4}' order by CreateTime desc limit {0}, {1}
) as u left join " + database3 + @".S_Desc s on u.Type = s.Type;", pq.StartRowNumber, pq.PageSize, grv.SearchExt, grv.StartDate, grv.ExpirationDate, grv.UserList);
}
else
{
return(new PagedList <UserMoneyRecord>(new List <UserMoneyRecord>(), 1, 1));
}
PagedList <UserMoneyRecord> obj = new PagedList <UserMoneyRecord>(DAL.PagedListDAL <UserMoneyRecord> .GetListByPage(pq, sqlconnectionString), pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(obj);
}
internal static int UpdateExchange(GameRecordView model)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
int i = cn.Execute(@"update ToolsUseRecord set ExchangeStatus = 1 where id = @UserID", model);
cn.Close();
return(i);
}
}
internal static IEnumerable <RobotOutPut> GetRobotOutPutList(GameRecordView vbd)
{
using (var cn = new MySqlConnection(sqlconnectionString2))
{
cn.Open();
IEnumerable <RobotOutPut> i = cn.Query <RobotOutPut>(@"S_Get007", param: new { begin_date = vbd.StartDate, end_date = vbd.ExpirationDate }, commandType: CommandType.StoredProcedure);
cn.Close();
return(i);
}
}
///call S_ClubRebate_Detail(12163 ,'2015-10-30'); 俱乐部明细查询
///查询汇总信息 call S_ClubRebate(12163 ,'2015-10-30');
internal static IEnumerable <ClubDataDetail> GetClubDataDetail(GameRecordView model)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
IEnumerable <ClubDataDetail> i = cn.Query <ClubDataDetail>(@"S_ClubSumDetail", param: new { }, commandType: CommandType.StoredProcedure);
cn.Close();
return(i);
}
}
public static PagedList <FishInfo> GetListByFish(GameRecordView grv)
{
PagerQuery pq = new PagerQuery();
pq.CurrentPage = grv.Page;
pq.PageSize = 10;
if (grv.commandID == 5)
{
pq.RecordCount = PagedListDAL <FishInfo> .GetRecordCount(string.Format(@"
select count(0) from FishInfoRecord a join " + database1 + @".Role r on a.UserID = r.id and r.agent <> 10010 where a.CreateTime between '{0}' and '{1}' and FishID = {2} and Type < 3 and Flag = 1 and a.UserID = case when '{4}' = '' then a.UserID else '{4}' end "
, grv.StartDate, grv.ExpirationDate, grv.ItemID, grv.commandID, grv.SearchExt), sqlconnectionString);
pq.Sql = string.Format(@"
select * from FishInfoRecord a join " + database1 + @".Role r on a.UserID = r.id and r.agent <> 10010 where a.CreateTime between '{2}' and '{3}' and a.FishID = {4} and a.Type < 3 and a.Flag = 1 and a.UserID = case when '{5}' = '' then a.UserID else '{5}' end order by a.CreateTime desc limit {0}, {1}"
, pq.StartRowNumber, pq.PageSize, grv.StartDate, grv.ExpirationDate, grv.ItemID, grv.SearchExt);
}
else
{
pq.RecordCount = PagedListDAL <FishInfo> .GetRecordCount(string.Format(@"
select count(0) from FishInfoRecord as f join " + database1 + @".Role r on f.UserID = r.id and r.agent <> 10010 where f.CreateTime between '{0}' and '{1}' and f.FishID = {3} and f.Type = {4} and UserID = case when '{2}' = '' then UserID else '{2}' end"
, grv.StartDate, grv.ExpirationDate, grv.SearchExt, grv.ItemID, grv.commandID), sqlconnectionString);
pq.Sql = string.Format(@"
select f.* from FishInfoRecord as f join " + database1 + @".Role r on f.UserID = r.id and r.agent <> 10010 where UserID = case when '{4}' = '' then UserID else '{4}' end
and f.CreateTime between '{2}' and '{3}' and f.FishID = {5} and f.Type = {6} order by f.CreateTime desc limit {0}, {1}"
, pq.StartRowNumber, pq.PageSize, grv.StartDate, grv.ExpirationDate, grv.SearchExt, grv.ItemID, grv.commandID);
}
//if (string.IsNullOrEmpty(grv.SearchExt))
//{
// pq.RecordCount = PagedListDAL<FishInfo>.GetRecordCount(string.Format(@"
// select count(0) from FishInfoRecord where CreateTime between '{0}' and '{1}' and FishID = {2} and Type = {3}"
// , grv.StartDate, grv.ExpirationDate ,grv.ItemID ,grv.commandID), sqlconnectionString);
// pq.Sql = string.Format(@"
// select * from FishInfoRecord where CreateTime between '{2}' and '{3}' and FishID = {4} and Type = {5} order by CreateTime desc limit {0}, {1}"
// , pq.StartRowNumber, pq.PageSize, grv.StartDate, grv.ExpirationDate, grv.ItemID, grv.commandID);
//}
//else
//{
// pq.RecordCount = PagedListDAL<FishInfo>.GetRecordCount(string.Format(@"
// select count(0) from FishInfoRecord as f,Role as r where f.UserID = r.ID and ( r.ID ='{2}' or r.NickName='{2}' or r.Account='{2}' )
// and f.CreateTime between '{0}' and '{1}' and f.FishID = {3} and f.Type = {4}"
// , grv.StartDate, grv.ExpirationDate, grv.SearchExt, grv.ItemID, grv.commandID), sqlconnectionString);
// pq.Sql = string.Format(@"
// select f.* from FishInfoRecord as f,Role as r where f.UserID = r.ID and ( r.ID ='{4}' or r.NickName='{4}' or r.Account='{4}' )
// and f.CreateTime between '{2}' and '{3}' and f.FishID = {5} and f.Type = {6} order by f.CreateTime desc limit {0}, {1}"
// , pq.StartRowNumber, pq.PageSize, grv.StartDate, grv.ExpirationDate, grv.SearchExt ,grv.ItemID, grv.commandID);
//}
PagedList <FishInfo> obj = new PagedList <FishInfo>(PagedListDAL <FishInfo> .GetListByPage(pq, sqlconnectionString), pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(obj);
}
internal static int UpdateBeginTimeForGame(GameRecordView vbd)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
int i = cn.Execute(@"update S_Update set id_date='" + vbd.ExpirationDate + @"'
where UpdateTable = '" + vbd.SearchExt + "'");
cn.Close();
return(i);
}
}
public static PagedList <ClubDataDetail> GetMyRebate(GameRecordView model)
{
PagerQuery pq = new PagerQuery();
pq.CurrentPage = model.Page;
pq.PageSize = 10;
pq.RecordCount = DAL.PagedListDAL <ClubDataDetail> .GetRecordCount(string.Format(@"
select count(*) from " + database2 + @".C_LoginUserClub cc," + database2 + @".C_LoginUser cu
where cc.UserId=cu.UserId and cu.UserAccount='" + model.SearchExt + "' {0}",
model.SearchExter <= 0 ?"": " and cc.ClubId = " + model.SearchExter));
string sql = string.Format(@"
select a.* from (
select Club ,sum(ClubCount) ClubCount ,sum(GiveYes) GiveYes ,sum(GiveLast) GiveLast ,sum(Login) Login ,s.Nickname
from (
select a.ClubID Club ,count(distinct a.userid) ClubCount ,0 GiveYes ,0 GiveLast ,ifnull(count(distinct b.userid ) ,0) Login
from " + database1 + @".ClubUser a
left join " + database1 + @".ClubGive b on a.userid = b.userid and b.CreateTime >= date_add('" + model.StartDate + @"' ,interval -1 day) and b.CreateTime < '" + model.StartDate + @"'
group by a.ClubID
union all
select ClubID Club ,0 ,0 ,sum(Gold) GiveLast ,0
from " + database1 + @".ClubGive
where ClubType = 2 and CreateTime < subdate('" + model.StartDate + @"' ,weekday('" + model.StartDate + @"' )) and
CreateTime >= date_sub(subdate('" + model.StartDate + @"' ,weekday('" + model.StartDate + @"' )) ,interval 7 day)
group by ClubID
union all
select ClubID ,0 ,sum(Gold) GiveYes ,0 ,0
from " + database1 + @".ClubGive
where ClubType = 2 and CreateTime < '" + model.StartDate + @"' and CreateTime >= date_sub('" + model.StartDate + @"' ,interval 1 day)
group by ClubID
union all
select ID ,0 ,0 ,0 ,0
from " + database1 + @".ClubInfo
)t join " + database1 + @".Role s on t.Club = s.id group by Club ,s.Nickname order by 4 desc
) as a
where a.Club in (
select cc.ClubId from " + database2 + @".C_LoginUserClub as cc," + database2 + @".C_LoginUser as cu
where cc.UserId=cu.UserId and cu.UserAccount ='" + model.SearchExt + @"'
) {0}
limit {1}, {2}
", model.SearchExter <= 0 ? "" : " and a.Club = " + model.SearchExter, pq.StartRowNumber, pq.PageSize);
pq.Sql = sql;
PagedList <ClubDataDetail> obj = new PagedList <ClubDataDetail>(DAL.PagedListDAL <ClubDataDetail> .GetListByPage(pq), pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(obj);
}
public static PagedList <FishInfo> FishCountOnUser(GameRecordView grv)
{
PagerQuery pq = new PagerQuery();
pq.CurrentPage = grv.Page;
pq.PageSize = 10;
if (string.IsNullOrEmpty(grv.SearchExt))
{
pq.RecordCount = PagedListDAL <FishInfo> .GetRecordCount(string.Format(@"select count(0) from FishInfoRecord where Type < 3 and Flag = 1 and CreateTime between '{0}' and '{1}'", grv.StartDate, grv.ExpirationDate), sqlconnectionString);
//pq.Sql = string.Format(@"select * from FishInfoRecord where Type < 3 and Flag = 1 and CreateTime between '{2}' and '{3}' order by CreateTime desc limit {0}, {1}", pq.StartRowNumber, pq.PageSize, grv.StartDate, grv.ExpirationDate);
pq.Sql = string.Format(@"select * from (
select *,
CASE FishID
WHEN 4 THEN 10
WHEN 3 THEN 20
WHEN 2 THEN 30
WHEN 1 THEN 40
WHEN 5 THEN 50
ELSE 60
END FishNameOrder from FishInfoRecord
where Type < 3 and Flag = 1 and CreateTime between '{2}' and '{3}'
) t
order by t.FishNameOrder,t.CreateTime desc limit {0}, {1}", pq.StartRowNumber, pq.PageSize, grv.StartDate, grv.ExpirationDate);
}
else
{
pq.RecordCount = PagedListDAL <FishInfo> .GetRecordCount(string.Format(@"select count(0) from FishInfoRecord as f,Role as r where f.UserID = r.ID and f.Type < 3 and f.Flag = 1 and ( r.ID ='{2}' or r.NickName='{2}' or r.Account='{2}' ) and f.CreateTime between '{0}' and '{1}'", grv.StartDate, grv.ExpirationDate, grv.SearchExt), sqlconnectionString);
// pq.Sql = string.Format(@"select * from FishInfoRecord where Type < 3 and Flag = 1 and UserID = {4} and CreateTime between '{2}' and '{3}' order by CreateTime desc limit {0}, {1}", pq.StartRowNumber, pq.PageSize, grv.StartDate, grv.ExpirationDate, grv.UserID);
pq.Sql = string.Format(@"select * from (
select f.*,
CASE f.FishID
WHEN 4 THEN 10
WHEN 3 THEN 20
WHEN 2 THEN 30
WHEN 1 THEN 40
WHEN 5 THEN 50
ELSE 60
END FishNameOrder from FishInfoRecord as f,Role as r
where f.UserID = r.ID and f.Type < 3 and f.Flag = 1 and ( r.ID ='{4}' or r.NickName='{4}' or r.Account='{4}' ) and f.CreateTime between '{2}' and '{3}'
) t
order by t.FishNameOrder,t.CreateTime desc limit {0}, {1}", pq.StartRowNumber, pq.PageSize, grv.StartDate, grv.ExpirationDate, grv.SearchExt);
}
PagedList <FishInfo> obj = new PagedList <FishInfo>(PagedListDAL <FishInfo> .GetListByPage(pq, sqlconnectionString), pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(obj);
}
internal static IEnumerable <CarGameRecord> GetListForCar(GameRecordView vbd)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
StringBuilder str = new StringBuilder();
str.Append("select * from BG_CarGameRecord where CreateTime between '" + vbd.StartDate + "' and '" + vbd.ExpirationDate + "' and CreateTime!='" + vbd.ExpirationDate + "' order by CreateTime desc ");
IEnumerable <CarGameRecord> i = cn.Query <CarGameRecord>(str.ToString());
cn.Close();
return(i);
}
}
internal static int InsertBeginTimeForGame(GameRecordView vbd)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
int i = cn.Execute(@"
insert into s_update(CountDate,UpdateTable,id_date,Description)
values(CURDATE(),'" + vbd.SearchExt + @"',now(),'')
");
cn.Close();
return(i);
}
}
public static PagedList <UserClubDetail> GetMyRebateDetail(GameRecordView model)
{
IEnumerable <UserClubDetail> data = GetRebateDetail(model);
PagerQuery pq = new PagerQuery();
pq.CurrentPage = model.Page;
pq.PageSize = 10;
pq.RecordCount = data.Count();
data = data.Skip((pq.CurrentPage - 1) * 10).Take(10);
PagedList <UserClubDetail> obj = new PagedList <UserClubDetail>(
data, pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(obj);
}
internal static IEnumerable <UserClub> GetRebateGroup(GameRecordView model)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
string sql = string.Format(@"
select a.GroupID ClubID ,a.GroupName ,ifnull(sum(c.ServiceSum) ,0) ServiceSum ,ifnull(sum(c.GiveSum) ,0) GiveSum ,a.CreateTime ClubUpdate ,a.RebatePer
from " + database2 + @".C_RebateGroup a
left join " + database2 + @".C_RebateUser b on a.GroupID = b.GroupID
left join " + database2 + @".C_RebateGive c on b.UserID = c.UserID and date(b.CreateTime) <= c.CountDate
where a.GroupID = case @UserID when 0 then a.GroupID else @UserID end group by a.GroupID ,a.GroupName ,a.CreateTime ; ");
IEnumerable <UserClub> i = cn.Query <UserClub>(sql, model);
cn.Close();
return(i);
}
}
public static PagedList <FinishTask> GetListByPage(GameRecordView grv)
{
PagerQuery pq = new PagerQuery();
pq.CurrentPage = grv.Page;
pq.PageSize = 10;
//select f.*,r.Agent from FinishTask f, Role r where r.ID = f.UserID
//(grv.Channels > 0 ? " and find_in_set(Role.Agent, {5})" : "" )
pq.RecordCount = DAL.PagedListDAL <FinishTask> .GetRecordCount(string.Format(@"select count(0) from FinishTask f, Role r where r.ID = f.UserID and f.FinishTime between '{0}' and '{1}' " + (grv.Channels > 0 ? " and find_in_set(r.Agent, {2})" : ""), grv.StartDate, grv.ExpirationDate, grv.UserList), sqlconnectionString);
pq.Sql = string.Format(@"select f.* from FinishTask f, Role r where r.ID = f.UserID and f.FinishTime between '{2}' and '{3}' " + (grv.Channels > 0 ? " and find_in_set(r.Agent, {4})" : "") + " order by f.id desc limit {0}, {1}", pq.StartRowNumber, pq.PageSize, grv.StartDate, grv.ExpirationDate, grv.UserList);
PagedList <FinishTask> obj = new PagedList <FinishTask>(PagedListDAL <FinishTask> .GetListByPage(pq, sqlconnectionString), pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(obj);
}
