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 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 <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 PagedList <ModelBaseData> GetModelByID(int id)
{
PagerQuery pq = new PagerQuery();
pq.Sql = string.Format(@"select ID ,ModelName ,Model ,Para from S_DataModel where id = {0}", id);
PagedList <ModelBaseData> obj = new PagedList <ModelBaseData>(PagedListDAL <ModelBaseData> .GetListByPage(pq, sqlconnectionString), pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(obj);
}
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);
}
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);
}
public static PagedList <ScaleRecord> GetListByPage(int page)
{
PagerQuery pq = new PagerQuery();
pq.CurrentPage = page;
pq.PageSize = 10;
pq.RecordCount = PagedListDAL <ScaleRecord> .GetRecordCount(string.Format(@"select count(0) from BG_ScaleRecord"), sqlconnectionString);
pq.Sql = string.Format(@"select * from BG_ScaleRecord order by CreateTime desc limit {0}, {1}", pq.StartRowNumber, pq.PageSize);
PagedList <ScaleRecord> obj = new PagedList <ScaleRecord>(PagedListDAL <ScaleRecord> .GetListByPage(pq, sqlconnectionString), pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(obj);
}
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);
}
public static PagedList <SpellCard> GetListByPage(GameRecordView bdv)
{
PagerQuery pq = new PagerQuery();
pq.CurrentPage = bdv.Page;
pq.PageSize = 10;
pq.RecordCount = PagedListDAL <SpellCard> .GetRecordCount(string.Format(@"select count(0) from " + database1 + @".SpellCardRecord where Time>='" + bdv.StartDate + "' and Time<'" + bdv.ExpirationDate + "' {0} ", string.IsNullOrEmpty(bdv.SearchExt)?"": " and PlayerID='" + bdv.SearchExt + "'"), sqlconnectionString);
pq.Sql = string.Format(@"select * from " + database1 + @".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 + "'");
PagedList <SpellCard> obj = new PagedList <SpellCard>(PagedListDAL <SpellCard> .GetListByPage(pq, sqlconnectionString), pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(obj);
}
public static PagedList <ModelBaseData> GetModelByPage(int page, string name)
{
PagerQuery pq = new PagerQuery();
pq.CurrentPage = page;
pq.PageSize = 50;
string CountSql = string.Format(@"select count(0) from S_DataModel where locate('{0}' ,ModelName) > 0", name);
pq.RecordCount = PagedListDAL <ModelBaseData> .GetRecordCount(CountSql, sqlconnectionString);
pq.Sql = string.Format(@"select * from S_DataModel where locate('{2}' ,ModelName) > 0 order by checkcount desc ,id desc limit {0}, {1}", pq.StartRowNumber, pq.PageSize, name);
PagedList <ModelBaseData> obj = new PagedList <ModelBaseData>(PagedListDAL <ModelBaseData> .GetListByPage(pq, sqlconnectionString), pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(obj);
}
public static PagedList <TexasPotLog> GetKuCunListByPage(GameRecordView model)
{
PagerQuery pq = new PagerQuery();
pq.CurrentPage = model.Page;
pq.PageSize = 10;
pq.RecordCount = PagedListDAL <TexasPotLog> .GetRecordCount(@"select count(0) from KuCunUpdateLog ", sqlconnectionString);
pq.Sql = string.Format(@"select * from KuCunUpdateLog order by Time desc limit {0}, {1}", pq.StartRowNumber, pq.PageSize, model.StartDate, model.ExpirationDate);
PagedList <TexasPotLog> obj = new PagedList <TexasPotLog>(PagedListDAL <TexasPotLog> .GetListByPage(pq, sqlconnectionString), pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(obj);
}
public static PagedList <FishInfo> GetListByPage(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 a join " + database1 + @".Role r on a.UserID = r.id and r.agent <> 10010 where a.CreateTime between '{0}' and '{1}'", grv.StartDate, grv.ExpirationDate), sqlconnectionString);
pq.Sql = string.Format(@"select a.* from FishInfoRecord a join " + database1 + @".Role r on a.UserID = r.id and r.agent <> 10010 where a.CreateTime between '{2}' and '{3}' order by a.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 r.agent <> 10010 and f.UserID = r.ID 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 f.* from FishInfoRecord as f,Role as r where r.agent <> 10010 and f.UserID = r.ID and ( r.ID ='{4}' or r.NickName='{4}' or r.Account='{4}' ) and f.CreateTime between '{2}' and '{3}' order by f.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);
}
public static PagedList <ExpRecord> GetListByPageForAgent(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}') and find_in_set(Agent, '{3}'))", model.StartDate, model.ExpirationDate, model.SearchExt, model.UserList), sqlconnectionString);
pq.Sql = string.Format(@"select aa.*, b.NickName as UserName from " + database3 + @".BG_ExpRecord aa inner join Role b on aa.UserID = b.ID and (b.ID = '{4}' or b.Account='{4}' or b.NickName='{4}') and find_in_set(b.Agent, '{5}') where aa.CreateTime between '{2}' and '{3}' order by aa.CreateTime desc limit {0}, {1};", pq.StartRowNumber, pq.PageSize, model.StartDate, model.ExpirationDate, model.SearchExt, model.UserList);
}
else
{
return(new PagedList <ExpRecord>(new List <ExpRecord>(), 1, 1));
}
PagedList <ExpRecord> obj = new PagedList <ExpRecord>(PagedListDAL <ExpRecord> .GetListByPage(pq, sqlconnectionString), pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(obj);
}
public static IEnumerable <object> GetModelData(string sql, int id)
{
return(PagedListDAL <object> .GetModelData(sql, id, sqlconnectionString));
}