/// <summary>
/// 在线在玩
/// </summary>
/// <param name="bdv"></param>
/// <returns></returns>
internal static IEnumerable <BaseDataInfoForOnlinePlay> GetOnlinePlay(BaseDataView bdv)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
StringBuilder str = new StringBuilder();
str.Append("SELECT CreateTime as date, Online as online, Playing as playing FROM 515game.BG_OnlinePlaying where CreateTime >= date(@StartDate) and CreateTime < date_add(date(@StartDate), interval 1 day)");
if (bdv.Channels > 0)
{
str.AppendFormat(" and find_in_set(AgentID, '{0}')", bdv.UserList);
}
switch (bdv.Groupby)
{
case groupby.日:
str.Append("");
break;
case groupby.月:
str.Append(" GROUP BY DATE_FORMAT( CreateTime, '%Y-%m-%d' );");
break;
}
IEnumerable <BaseDataInfoForOnlinePlay> i = cn.Query <BaseDataInfoForOnlinePlay>(str.ToString(), bdv);
cn.Close();
return(i);
}
}
public ActionResult OnlineProblem(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");
ViewData["StartDate"] = _StartDate;
ViewData["ExpirationDate"] = _ExpirationDate;
BaseDataView bdv = new BaseDataView {
StartDate = _StartDate, ExpirationDate = _ExpirationDate, Page = page
};
if (queryvalues.ContainsKey("StartDate") == false)
{
PagedList <CustomerServCenter> model2 = new PagedList <CustomerServCenter>(new List <CustomerServCenter>(), 1, 1);
bdv.BaseDataList = model2;
return(View(bdv));
}
if (Request.IsAjaxRequest())
{
return(PartialView("OnlineProblem_PageList", CustomerServCenterBLL.GetListByPage(bdv)));
}
PagedList <CustomerServCenter> model = CustomerServCenterBLL.GetListByPage(bdv);
bdv.BaseDataList = model;
return(View(bdv));
}
public ActionResult GetDetailsDataForUser(Dictionary <string, string> queryvalues) //无效
{
string userID = queryvalues.ContainsKey("Value") ? queryvalues["Value"] : ""; //用户ID
BaseDataView vbd = new BaseDataView
{
StartDate = DateTime.Now.AddDays(-1).Date.ToString(), //计算今天的
ExpirationDate = DateTime.Now.AddDays(1).Date.ToString(),
//Channels = _Channels,
SearchExt = userID
};
UserRecord model = new UserRecord();
IEnumerable <BaseDataInfo> ibd = BaseDataBLL.GetGameProfit(vbd);
foreach (BaseDataInfo m in ibd)
{
model.TexasLost = m.ProfitDel1;
model.TexasWin = m.ProfitAdd2;
model.ScaleLost = m.ProfitDel2;
model.ScaleWin = m.ProfitAdd2;
model.ZodiacLost = m.ProfitDel3;
model.ZodiacWin = m.ProfitAdd3;
model.CarLost = m.ProfitDel5;
model.CarWin = m.ProfitAdd5;
model.HundredLost = m.ProfitDel6;
model.HundredWin = m.ProfitAdd6;
}
model.TexasZyk = 0;
model.ScaleZyk = MemberCenterBLL.GetScaleForZyinkui(int.Parse(userID)) * -1;
model.ZodiacZyk = MemberCenterBLL.GetListForZodiacyinkui(int.Parse(userID)); //这个不用*-1 因为直接算的是庄家输赢 而不是玩家输赢的总和
model.CarZyk = MemberCenterBLL.GetListForCaryinkui(int.Parse(userID)); //这个不用*-1 因为直接算的是庄家输赢 而不是玩家输赢的总和
model.HundredZyk = MemberCenterBLL.GetListForHundredyinkui(int.Parse(userID));
return(View(model));
}
public static PagedList <RebateUser> GetRebateuser(int page, BaseDataView vbd)
{
PagerQuery pq = new PagerQuery();
pq.CurrentPage = page;
pq.PageSize = 10;
pq.RecordCount = 1;
pq.Sql = string.Format(@"select a.UserID ,d.LoginTime ,c.TexasCount ,c.ScaleCount ,c.ZodiacCount ,c.HorseCount ,c.CarCount ,b.ServiceSum ,b.GiveSum
from (select ID UserID ,NickName from " + database1 + @".Role where id = {0}) a
left join (
select a.UserID ,sum(ServiceSum) ServiceSum ,sum(GiveSum) GiveSum
from " + database2 + @".C_RebateGive a
where a.CountDate >= '{1}' and a.CountDate <'{2}' and a.UserID = {0} group by a.UserID
)b on a.UserID = b.UserID
left join (
select userid ,sum(Texas_LCount+Texas_MCount+Texas_HCount) TexasCount ,sum(Scale_Count) ScaleCount
,sum(Zodiac_Count) ZodiacCount ,sum(Horse_Count) HorseCount ,sum(Car_Count) CarCount
from " + database3 + @".Clearing_Game where userid = {0} and CountDate >= '{1}' and CountDate < '{2}' group by userid
)c on a.UserID = c.UserID
left join (
select userid ,max(LoginTime) LoginTime from " + database1 + @".BG_LoginRecord where userid = {0} group by userid
)d on a.UserID = d.UserID ;",
vbd.SearchExt, vbd.StartDate, vbd.ExpirationDate);
PagedList <RebateUser> obj = new PagedList <RebateUser>(DAL.PagedListDAL <RebateUser> .GetListByPage(pq, sqlconnectionString), pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(obj);
}
public ActionResult GameOutputDetail2(Dictionary <string, string> queryvalues)
{
int _Channels = queryvalues.ContainsKey("Channels") ? Convert.ToInt32(queryvalues["Channels"]) : 0;
string _StartDate = queryvalues.ContainsKey("StartDate") ? queryvalues["StartDate"] : DateTime.Now.ToString("yyyy-MM-dd 00:00:00");
groupby _Groupby = (groupby)(queryvalues.ContainsKey("groupby") ? Convert.ToInt32(queryvalues["groupby"]) : 1);
int MasterID = User.Identity.GetUserId <int>();
if (AgentUserBLL.CheckUser(_Channels, MasterID))
{
_Channels = MasterID;
}
string _UserList = AgentUserBLL.GetUserListString(_Channels);
string _MasterList = AgentUserBLL.GetUserListString(MasterID);
BaseDataView vbd = new BaseDataView {
UserList = _UserList, StartDate = _StartDate, Groupby = _Groupby, Channels = _Channels
};
vbd.BaseDataList = BaseDataBLL.GetGameOutputDetail2(vbd);
if (Request.IsAjaxRequest())
{
return(PartialView("GameOutputDetail2", vbd));
}
return(View(vbd));
}
public ActionResult KuCunFlow(Dictionary <string, string> queryvalues)
{
int tab = queryvalues.ContainsKey("RaType") ? Convert.ToInt32(queryvalues["RaType"]) : -1;
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");
BaseDataView vbd = new BaseDataView
{
StartDate = _StartDate,
ExpirationDate = _ExpirationDate,
RaType = tab
};
if (Request.IsAjaxRequest())
{
return(PartialView("KuCunFlow_PageList", DaiLiBLL.GetKuCunListByPage(page, Convert.ToInt32(vbd.RaType))));
}
vbd.BaseDataList = DaiLiBLL.GetKuCunListByPage(page, Convert.ToInt32(vbd.RaType));
return(View(vbd));
}
public static PagedList <Role> GetListByCreateTime(BaseDataView bdv)
{
PagerQuery pq = new PagerQuery();
if (bdv.Channels > 0)
{
pq.RecordCount = DAL.PagedListDAL <Role> .GetRecordCount(string.Format(@"select count(0) from Role where CreateTime >= '{0}' and CreateTime<'{1}' and Agent in ({2})", bdv.StartDate, bdv.ExpirationDate, bdv.UserList), sqlconnectionString);
pq.Sql = string.Format(@"select a.ID, a.Account, a.Email, a.Tel, a.TrueName, a.Identity, a.Agent, a.Password, a.Gold, a.Diamond, a.Zicard, a.Telfare, a.MaxNoble, a.ShowGift, a.NickName, a.Gender, a.Country, a.Province, a.City, a.FigureUrl, a.IsYellowVip, a.IsYellowVipYear, a.YellowVipLevel, a.IsYellowHighVip, a.PF, a.OpenID, a.IOpenID, a.Invkey, a.Itime, a.LoginDevice, a.LastModify, a.CreateTime, a.CreateIP, a.NoSpeak, (CASE WHEN a.IsFreeze=0 THEN 0 when a.IsFreeze>=1 then 1 ELSE a.IsFreeze END) as IsFreeze, a.SafeBox, a.SafePwd, a.CreateMac, a.ClubID ,b.AgentName
from Role as a left join " + database2 + ".AgentUsers as b on a.Agent = b.id where a.CreateTime >= '{2}' and a.CreateTime<'{3}' and a.Agent in ({4}) order by a.CreateTime desc ", pq.StartRowNumber, pq.PageSize, bdv.StartDate, bdv.ExpirationDate, bdv.UserList);
}
else
{
pq.RecordCount = DAL.PagedListDAL <Role> .GetRecordCount(string.Format(@"select count(0) from Role where CreateTime >= '{0}' and CreateTime<'{1}' and Agent!=10010", bdv.StartDate, bdv.ExpirationDate), sqlconnectionString);
pq.Sql = string.Format(@"select a.ID, a.Account, a.Email, a.Tel, a.TrueName, a.Identity, a.Agent, a.Password, a.Gold, a.Diamond, a.Zicard, a.Telfare, a.MaxNoble, a.ShowGift, a.NickName, a.Gender, a.Country, a.Province, a.City, a.FigureUrl, a.IsYellowVip, a.IsYellowVipYear, a.YellowVipLevel, a.IsYellowHighVip, a.PF, a.OpenID, a.IOpenID, a.Invkey, a.Itime, a.LoginDevice, a.LastModify, a.CreateTime, a.CreateIP, a.NoSpeak, (CASE WHEN a.IsFreeze=0 THEN 0 when a.IsFreeze>=1 then 1 ELSE a.IsFreeze END) as IsFreeze, a.SafeBox, a.SafePwd, a.CreateMac, a.ClubID ,b.AgentName
from Role as a left join " + database2 + ".AgentUsers as b on a.Agent = b.id where a.CreateTime >= '{2}' and a.CreateTime<'{3}' and a.Agent!=10010 order by a.CreateTime desc ", pq.StartRowNumber, pq.PageSize, bdv.StartDate, bdv.ExpirationDate);
}
var d = DAL.PagedListDAL <Role> .GetListByPage(pq, sqlconnectionString);
PagedList <Role> obj = new PagedList <Role>(d, 1, d.Count() + 1, d.Count() + 1);
return(obj);
}
public static PagedList <LogInfo> GetLogListByPage(BaseDataView bdv)
{
PagerQuery pq = new PagerQuery();
pq.CurrentPage = bdv.Page;
pq.PageSize = 10;
if (bdv.SearchExt == "")
{
pq.RecordCount = DAL.PagedListDAL <LogInfo> .GetRecordCount(@"select count(0) from " + database3 + @".Log where CreateTime BETWEEN '" + bdv.StartDate + "' and '" + bdv.ExpirationDate + "' ");
//pq.Sql = string.Format(@"SELECT * FROM record.Log where CreateTime BETWEEN '{3}' and '{4}' order by CreateTime desc limit {0}, {1}", pq.StartRowNumber, pq.PageSize, bdv.SearchExt, bdv.StartDate, bdv.ExpirationDate);
pq.Sql = string.Format(@" SELECT l.*,u.NickName FROM " + database3 + @".Log as l left join " + database2 + @".AspNetUsers as u
on l.UserAccount = u.UserName where
l.CreateTime BETWEEN '{3}' and '{4}' order by l.CreateTime desc limit {0}, {1}", pq.StartRowNumber, pq.PageSize, bdv.SearchExt, bdv.StartDate, bdv.ExpirationDate);
}
else
{
pq.RecordCount = DAL.PagedListDAL <LogInfo> .GetRecordCount(@"select count(0) from " + database3 + @".Log where UserAccount='" + bdv.SearchExt + "' and CreateTime BETWEEN '" + bdv.StartDate + "' and '" + bdv.ExpirationDate + "' ");
pq.Sql = string.Format(@" SELECT l.*,u.NickName FROM " + database3 + @".Log as l left join " + database2 + @".AspNetUsers as u
on l.UserAccount = u.UserName where l.UserAccount = '" + bdv.SearchExt + @"'
and l.CreateTime BETWEEN '{3}' and '{4}' order by l.CreateTime desc limit {0}, {1}", pq.StartRowNumber, pq.PageSize, bdv.SearchExt, bdv.StartDate, bdv.ExpirationDate);
}
PagedList <LogInfo> obj = new PagedList <LogInfo>(DAL.PagedListDAL <LogInfo> .GetListByPage(pq), pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(obj);
}
//WhiteList
public static PagedList <WhiteList> GetListByPage(BaseDataView bdv)
{
PagerQuery pq = new PagerQuery();
pq.CurrentPage = bdv.Page;
pq.PageSize = 10;
if (!string.IsNullOrEmpty(bdv.SearchExt))
{
pq.RecordCount = DAL.PagedListDAL <WhiteList> .GetRecordCount(string.Format(@"select count(0) from Role where CreateIP = '{0}'", bdv.SearchExt), sqlconnectionString);
pq.Sql = string.Format(@"select DISTINCT CreateIP as IP,(select count(*) from IPWhiteList where IP='{2}') as Status from Role where CreateIP='{2}' limit {0}, {1}", pq.StartRowNumber, pq.PageSize, bdv.SearchExt);
}
else
{
pq.RecordCount = DAL.PagedListDAL <WhiteList> .GetRecordCount(@"select count(0) from IPWhiteList ", sqlconnectionString);
pq.Sql = string.Format(@"select IP,1 as Status from IPWhiteList limit {0}, {1}", pq.StartRowNumber, pq.PageSize);
}
PagedList <WhiteList> obj = new PagedList <WhiteList>(DAL.PagedListDAL <WhiteList> .GetListByPage(pq, sqlconnectionString), pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(obj);
}
/// <summary>
/// 产出消耗明细
/// </summary>
/// <param name="vbd"></param>
/// <returns></returns>
internal static GameOutputDetail GetGameOutputDetail2(BaseDataView vbd)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
StringBuilder str = new StringBuilder();
str.AppendFormat(@"SELECT sum(Chip) as Chip, sum(Diamond) as Diamond, sum(Score) as Score, `RecordType` as ChipChangeType from record.Clearing_UserMoneyRecord where `RecordType` in (1,2,3,4,5,6,7,8,9,12,13,16,17,18,19,20,21,22,23,24,25,26,37,38,39,40,41,42,43,44,45,46,47,48,50,51,59,60,61,62,63,64,65,67,68,70,71,74,76,79,80,81,82,83,84,85,86,88,90) and RecordTime = @StartDate {0} group by `RecordType`;
select systemChip as systemBargainingChip, systemDiamond, systemScore, systemFish as systemFishChip from record.Clearing_UserMoneyStock where RecordTime = @StartDate {0}", vbd.Channels > 0 ? "and find_in_set(Agent, @UserList)" : "");
GameOutputDetail j = new GameOutputDetail();
using (var multi = cn.QueryMultiple(str.ToString(), vbd))
{
var i = multi.Read <GameOutputList>().ToList();
var systemSum = multi.Read <GameOutput>().ToList();
j.date = Convert.ToDateTime(vbd.StartDate);
j.systemBargainingChip = systemSum.Select(x => x.systemBargainingChip).Sum();
j.systemDiamond = systemSum.Select(x => x.systemDiamond).Sum();
j.systemScore = systemSum.Select(x => x.systemScore).Sum();
j.list = i.ToList();
}
cn.Close();
return(j);
}
}
public ActionResult QQZoneRecharge(Dictionary <string, string> queryvalues)
{
int page = queryvalues.ContainsKey("page") ? Convert.ToInt32(queryvalues["page"]) : 1;
int _UserID = queryvalues.ContainsKey("UserID") ? string.IsNullOrWhiteSpace(queryvalues["UserID"]) ? 0 : Convert.ToInt32(queryvalues["UserID"]) : 0;
int _Channels = queryvalues.ContainsKey("Channels") ? Convert.ToInt32(queryvalues["Channels"]) : 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");
groupby _Groupby = (groupby)(queryvalues.ContainsKey("groupby") ? Convert.ToInt32(queryvalues["groupby"]) : 1);
BaseDataView vbd = new BaseDataView {
UserID = _UserID, StartDate = _StartDate, ExpirationDate = _ExpirationDate, Groupby = _Groupby, Channels = _Channels
};
ViewData["groupby"] = vbd.Groupby.ToSelectListItemForSelect();
ViewData["SumRecharge"] = QQZoneRechargeBLL.GetSumRecharge(vbd);
if (Request.IsAjaxRequest())
{
return(PartialView("QQZoneRecharge_PageList", QQZoneRechargeBLL.GetListByPage(page, vbd)));
}
vbd.BaseDataList = QQZoneRechargeBLL.GetListByPage(page, vbd);
return(View(vbd));
}
public ActionResult SystemExpend(Dictionary <string, string> queryvalues)
{
string _StartDate = queryvalues.ContainsKey("StartDate") ? queryvalues["StartDate"] : DateTime.Now.AddDays(-7).ToString("yyyy-MM-dd");
string _ExpirationDate = queryvalues.ContainsKey("ExpirationDate") ? queryvalues["ExpirationDate"] : DateTime.Now.ToString("yyyy-MM-dd");
BaseDataView bdv = new BaseDataView()
{
StartDate = _StartDate,
ExpirationDate = _ExpirationDate
};
if (Request.IsAjaxRequest())
{
List <SystemExpend> pays = SystemPayBLL.GetSystemSystemExpend(_StartDate, _ExpirationDate, 0);
return(Json(new
{
datatype = "list",
ret = 0,
data = pays
}));
}
return(View(bdv));
}
public ActionResult EmailStock(Dictionary <string, string> queryvalues)
{
int _page = queryvalues.ContainsKey("page") ? Convert.ToInt32(queryvalues["page"]) : 1;
string UserName = queryvalues.ContainsKey("SearchExt") ? queryvalues["SearchExt"] : "";
string GroupName = queryvalues.ContainsKey("SearchExt2") ? queryvalues["SearchExt2"] : "";
//
BaseDataView bdv = new BaseDataView()
{
SearchExt = UserName,
SearchExt2 = GroupName,
Page = _page
};
if (Request.IsAjaxRequest())
{
return(PartialView("EmailStock_PageList", ServEmailBLL.GetListByUserStock(bdv)));
}
//UserStock
bdv.BaseDataList = ServEmailBLL.GetListByUserStock(bdv);
return(View(bdv));
}
public ActionResult GameOutput2(Dictionary <string, string> queryvalues)
{
int _Channels = queryvalues.ContainsKey("Channels") ? Convert.ToInt32(queryvalues["Channels"]) : 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");
groupby _Groupby = (groupby)(queryvalues.ContainsKey("groupby") ? Convert.ToInt32(queryvalues["groupby"]) : 1);
int MasterID = User.Identity.GetUserId <int>();
if (AgentUserBLL.CheckUser(_Channels, MasterID))
{
_Channels = MasterID;
}
string _UserList = AgentUserBLL.GetUserListString(_Channels);
string _MasterList = AgentUserBLL.GetUserListString(MasterID);
BaseDataView vbd = new BaseDataView {
UserList = _UserList, StartDate = _StartDate, ExpirationDate = _ExpirationDate, Groupby = _Groupby, Channels = _Channels
};
ViewData["groupby"] = vbd.Groupby.ToSelectListItemForSelect();
ViewData["Channels"] = AgentUserBLL.GetUserList(_MasterList).Select(x => new SelectListItem {
Text = x.AgentName, Value = x.Id.ToString(), Selected = x.Id == _Channels
});
vbd.BaseDataList = BaseDataBLL.GetGameOutput2(vbd);
return(View(vbd));
}
/// <summary>
/// 活跃用户
/// </summary>
/// <param name="bdv"></param>
/// <returns></returns>
internal static IEnumerable <BaseDataInfo> GetActiveUsers(BaseDataView bdv)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
StringBuilder str = new StringBuilder();
str.Append("SELECT DATE_FORMAT( CreateTime, '%Y-%m-%d') as date, sum(ActiveNum) as count FROM record.Clearing_RoleStatic where CreateTime between @StartDate and @ExpirationDate");
if (bdv.Channels > 0)
{
str.AppendFormat(" and find_in_set(AgentID, '{0}')", bdv.UserList);
}
switch (bdv.Groupby)
{
case groupby.日:
str.Append(" GROUP BY DATE_FORMAT( CreateTime, '%Y-%m-%d' );");
break;
case groupby.月:
str.Append(" GROUP BY DATE_FORMAT( CreateTime, '%Y-%m' );");
break;
}
IEnumerable <BaseDataInfo> i = cn.Query <BaseDataInfo>(str.ToString(), bdv);
cn.Close();
return(i);
}
}
internal static int GetAllPlayCount(BaseDataView bdv)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
StringBuilder str = new StringBuilder();
str.Append("SELECT sum(Count) FROM (SELECT Count FROM playcounttexas union all SELECT Count from playcountland) A;");
int i = cn.Query <int>(str.ToString()).First();
cn.Close();
return(i);
}
}
public ActionResult OperLog(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 _UserAccount = queryvalues.ContainsKey("UserAccount") ? queryvalues["UserAccount"] : "";
BaseDataView vbd = new BaseDataView {
SearchExt = _UserAccount, Page = _page, StartDate = _StartDate, ExpirationDate = _ExpirationDate
};
IEnumerable <AspNetUser> users = SUBLL.GetAspNetUsers();
List <SelectListItem> ieList = new List <SelectListItem>();
ieList.Insert(0, new SelectListItem {
Text = "所有账号", Value = "", Selected = "" == _UserAccount
});
int i = 1;
foreach (var item in users)
{
if (string.IsNullOrEmpty(item.NickName))
{
ieList.Insert(i++, new SelectListItem {
Text = item.UserName, Value = item.UserName, Selected = item.UserName == _UserAccount
});
}
else
{
ieList.Insert(i++, new SelectListItem {
Text = item.UserName + "(" + item.NickName + ")", Value = item.UserName, Selected = item.UserName == _UserAccount
});
}
}
ViewData["UserAccount"] = ieList;
//通过时间查询role列表
vbd.BaseDataList = SUBLL.GetLogListByPage(vbd);
if (Request.IsAjaxRequest())
{
return(PartialView("OperLog_PageList", vbd.BaseDataList));
}
return(View(vbd));
}
internal static object GetAllUser(BaseDataView vbd)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
StringBuilder str = new StringBuilder();
str.Append("SELECT count(0) FROM role;");
int i = cn.Query <int>(str.ToString()).First();
cn.Close();
return(i);
}
}
/// <summary>
/// vip分布比
/// </summary>
/// <param name="bdv"></param>
/// <returns></returns>
internal static BaseDataInfoForVIPDistributionRatio GetVIPDistributionRatio(BaseDataView bdv)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
StringBuilder str = new StringBuilder();
str.Append(@"record.sys_get_vip_rate");
IEnumerable <BaseDataInfoForVIPDistributionRatio> i = cn.Query <BaseDataInfoForVIPDistributionRatio>(str.ToString(), commandType: CommandType.StoredProcedure, param: new { agentids = bdv.Channels > 0 ? bdv.UserList : "0" });
cn.Close();
return(i.FirstOrDefault());
}
}
//UserStock
public static PagedList <UserStock> GetListByUserStock(BaseDataView bdv)
{
PagerQuery pq = new PagerQuery();
pq.CurrentPage = bdv.Page;
pq.PageSize = 10;
// pq.RecordCount = DAL.PagedListDAL<UserStock>.GetRecordCount(
// string.Format(@"
//select count(0) from gserverinfo.e_userstock {0}",
//bdv.SearchExt==""? "": " where UserName='******' "
//));
pq.RecordCount = DAL.PagedListDAL <UserStock> .GetRecordCount(
string.Format(@"
SELECT COUNT(*) from (
select c.id,c.GroupName,c.Value,group_concat(c.UserName separator ',') as UserName
from
(select b.*,a.UserName from e_userstockgroup as b LEFT JOIN e_userstock as a on a.groupid = b.id
WHERE 1=1 {0} {1}
) as c
group by c.id,c.GroupName,c.Value
) as d
",
bdv.SearchExt == "" ? "" : " and a.groupid in (SELECT groupid from e_userstock where UserName = @UserName)",
bdv.SearchExt2 == "" ? "" : " and GroupName = @GroupName "
), new { UserName = bdv.SearchExt, GroupName = bdv.SearchExt2 });
pq.Sql = string.Format(@"
select c.UpdateTime,c.id as GroupID,c.GroupName,c.Value,group_concat(c.UserName separator ',') as UserName
from
(select b.*,a.UserName from e_userstockgroup as b LEFT JOIN e_userstock as a on a.groupid = b.id
WHERE 1=1 {2} {3}
) as c
group by c.id,c.GroupName,c.Value,c.UpdateTime
order by c.UpdateTime desc
limit {0}, {1}", pq.StartRowNumber, pq.PageSize,
bdv.SearchExt == "" ? "" : " and a.groupid in (SELECT groupid from e_userstock where UserName = @UserName) ",
bdv.SearchExt2 == "" ? "" : " and GroupName = @GroupName "
);
PagedList <UserStock> obj = new PagedList <UserStock>(
DAL.PagedListDAL <UserStock> .GetListByPage(pq, new { UserName = bdv.SearchExt, GroupName = bdv.SearchExt2 }),
pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(obj);
}
public ActionResult RouletteDataDetail(Dictionary <string, string> queryvalues)
{
int _Channels = queryvalues.ContainsKey("Channels") ? Convert.ToInt32(queryvalues["Channels"]) : 0;
string _StartDate = queryvalues.ContainsKey("StartDate") ? queryvalues["StartDate"] : DateTime.Now.ToString("yyyy-MM-dd 00:00:00");
BaseDataView vbd = new BaseDataView {
Channels = _Channels, StartDate = _StartDate
};
IEnumerable <Roulette> detail = new List <Roulette>();
detail = GL.Data.BLL.ActiveBLL.GetRouletteDataDetail(vbd);
return(View(detail));
}
public static PagedList <CustomerServCenter> GetListByPage(BaseDataView bdv)
{
PagerQuery pq = new PagerQuery();
pq.CurrentPage = bdv.Page;
pq.PageSize = 10;
pq.RecordCount = DAL.PagedListDAL <CustomerServCenter> .GetRecordCount(@"select count(0) from CustomerServCenter where CSCSubId = 0 and CSCTime BETWEEN '" + bdv.StartDate + "' and '" + bdv.ExpirationDate + "'");
pq.Sql = string.Format(@"select * from CustomerServCenter where CSCSubId = 0 and CSCTime BETWEEN '{2}' and '{3}' order by CSCTime desc limit {0}, {1}", pq.StartRowNumber, pq.PageSize, bdv.StartDate, bdv.ExpirationDate);
PagedList <CustomerServCenter> csc = new PagedList <CustomerServCenter>(DAL.PagedListDAL <CustomerServCenter> .GetListByPage(pq), pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(csc);
}
/// <summary>
/// 产出消耗
/// </summary>
/// <param name="vbd"></param>
/// <returns></returns>
internal static IEnumerable <GameOutput> GetGameOutput2(BaseDataView vbd)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
StringBuilder str = new StringBuilder();
str.AppendFormat(@"select DATE_FORMAT(RecordTime, '%Y-%m-%d') as date, sum(Chip) as ChipOutput from record.Clearing_UserMoneyRecord where `RecordType` in (1,2,3, 5,20,12,13,16,9,37,39,45,46,48,51,44,59,60,61,62,63,67,70,71,74,79,80,81,82,83,84,85,86,88,90) and RecordTime between @StartDate and @ExpirationDate {0} group by DATE_FORMAT(RecordTime, '%Y-%m-%d');
select DATE_FORMAT(RecordTime, '%Y-%m-%d') as date, sum(Chip) as ChipConsume from record.Clearing_UserMoneyRecord where RecordTime between @StartDate and @ExpirationDate and `RecordType` in (24,25,26,22,23,19,7,21,18,17,47,50,64,65,68,76) {0} group by DATE_FORMAT(RecordTime, '%Y-%m-%d');
select DATE_FORMAT(RecordTime, '%Y-%m-%d') as date, sum(Chip) as ChipRecharge from record.Clearing_UserMoneyRecord where RecordType = 5 and RecordTime between @StartDate and @ExpirationDate {0} group by DATE_FORMAT(RecordTime, '%Y-%m-%d');
select DATE_FORMAT(RecordTime, '%Y-%m-%d') as date, sum(Diamond) as DiamondOutput from record.Clearing_UserMoneyRecord where `RecordType` in (40,6,38,45) and RecordTime between @StartDate and @ExpirationDate {0} group by DATE_FORMAT(RecordTime, '%Y-%m-%d');
select DATE_FORMAT(RecordTime, '%Y-%m-%d') as date, sum(Diamond) as DiamondConsume from record.Clearing_UserMoneyRecord where `RecordType` in (8,4) and RecordTime between @StartDate and @ExpirationDate {0} group by DATE_FORMAT(RecordTime, '%Y-%m-%d');
select DATE_FORMAT(RecordTime, '%Y-%m-%d') as date, sum(Diamond) as DiamondRecharge from record.Clearing_UserMoneyRecord where RecordType = 6 and RecordTime between @StartDate and @ExpirationDate {0} group by DATE_FORMAT(RecordTime, '%Y-%m-%d');
select DATE_FORMAT(RecordTime, '%Y-%m-%d') as date, sum(systemChip) as systemBargainingChip, sum(systemDiamond) as systemDiamond, systemScore, sum(systemFish) as systemFishChip from record.Clearing_UserMoneyStock where RecordTime between @StartDate and @ExpirationDate {0} group by DATE_FORMAT(RecordTime, '%Y-%m-%d');
", vbd.Channels > 0 ? "and find_in_set(Agent, @UserList)" : "");
IEnumerable <GameOutput> i;
using (var multi = cn.QueryMultiple(str.ToString(), vbd))
{
var ChipOutput = multi.Read <GameOutput>().ToList();
var ChipConsume = multi.Read <GameOutput>().ToList();
var ChipRecharge = multi.Read <GameOutput>().ToList();
var DiamondOutput = multi.Read <GameOutput>().ToList();
var DiamondConsume = multi.Read <GameOutput>().ToList();
var DiamondRecharge = multi.Read <GameOutput>().ToList();
var systemSum = multi.Read <GameOutput>().ToList();
//var FishChip = multi.Read<GameOutput>().ToList();
i = ChipOutput.Union(ChipConsume).Union(ChipRecharge).Union(DiamondOutput).Union(DiamondConsume).Union(DiamondRecharge).Union(systemSum).GroupBy(n => n.date).Select(g => new GameOutput
{
date = g.Select(s => s.date).First(),
ChipOutput = g.Select(s => s.ChipOutput).Sum(),
ChipConsume = g.Select(s => s.ChipConsume).Sum(),
ChipRecharge = g.Select(s => s.ChipRecharge).Sum(),
DiamondOutput = g.Select(s => s.DiamondOutput).Sum(),
DiamondConsume = g.Select(s => s.DiamondConsume).Sum(),
DiamondRecharge = g.Select(s => s.DiamondRecharge).Sum(),
systemBargainingChip = g.Select(s => s.systemBargainingChip).Sum(),
systemDiamond = g.Select(s => s.systemDiamond).Sum(),
systemFishChip = g.Select(s => s.systemFishChip).Sum()
}).OrderBy(x => x.date).ToList();
}
cn.Close();
return(i);
}
}
internal static IEnumerable <BaseDataInfo> GetActiveUsers(BaseDataView bdv)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
StringBuilder str = new StringBuilder();
str.Append("SELECT DATE_FORMAT( CreateTime, '%Y-%m-%d') as date, sum(count) as count FROM bg_activeusers where date(CreateTime) >= date(@StartDate) and date(CreateTime) < date(@ExpirationDate)");
switch (bdv.Terminals)
{
case terminals.页游:
str.AppendFormat(" and type = {0}", (int)terminals.页游);
break;
case terminals.安卓:
str.AppendFormat(" and type = {0}", (int)terminals.安卓);
break;
case terminals.IOS:
str.AppendFormat(" and type = {0}", (int)terminals.IOS);
break;
case terminals.所有终端:
default:
str.Append(" and (type = 1 or type = 2)");
break;
}
switch (bdv.Groupby)
{
case groupby.日:
str.Append(" GROUP BY DATE_FORMAT( CreateTime, '%Y-%m-%d' );");
break;
case groupby.月:
str.Append(" GROUP BY DATE_FORMAT( CreateTime, '%Y-%m' );");
break;
}
IEnumerable <BaseDataInfo> i = cn.Query <BaseDataInfo>(str.ToString(), bdv);
cn.Close();
return(i);
}
}
public static PagedList <QQZoneRecharge> GetListByPage(int page, BaseDataView vbd)
{
PagerQuery pq = new PagerQuery();
pq.CurrentPage = page;
pq.PageSize = 10;
if (!string.IsNullOrEmpty(vbd.SearchExt))
{
string myWhere = "q.UserID = r.ID and ( r.ID = BINARY '{2}' or r.NickName= BINARY '{2}' or r.Account= BINARY '{2}' ) {3} and q.CreateTime between '{0}' and '{1}'";
if (vbd.RaType != null && Convert.ToInt32(vbd.RaType) > 0)
{
myWhere += " and q.PF=" + vbd.RaType + " ";
}
pq.RecordCount = DAL.PagedListDAL <QQZoneRecharge> .GetRecordCount(
string.Format(@"select count(0) from QQZoneRecharge as q ,Role as r where " + myWhere,
vbd.StartDate, vbd.ExpirationDate, vbd.SearchExt, vbd.Channels == 0 ? "" : string.Format(" and r.Agent in ({0})", vbd.Channels))
, sqlconnectionString);
pq.Sql = string.Format(@"select q.*, r.NickName as NickName, r.Account as UserAccount,a.AgentName from QQZoneRecharge as q ,Role as r,GServerInfo.AgentUsers as a
where r.Agent = a.Id and " + myWhere + @" order by q.CreateTime desc limit {4},{5}",
vbd.StartDate, vbd.ExpirationDate, vbd.SearchExt, vbd.Channels == 0 ? "" : string.Format(" and r.Agent in ({0})", vbd.Channels), pq.StartRowNumber, pq.PageSize);
}
else
{
string myWhere = " q.CreateTime between '{0}' and '{1}' ";
if (vbd.RaType != null && Convert.ToInt32(vbd.RaType) > 0)
{
myWhere += " and q.PF=" + vbd.RaType + " ";
}
pq.RecordCount = DAL.PagedListDAL <QQZoneRecharge> .GetRecordCount(string.Format(@"select count(0) from QQZoneRecharge as q join Role r on q.userid = r.id where " + myWhere + " {2}",
vbd.StartDate, vbd.ExpirationDate, vbd.Channels == 0 ? "" : string.Format(" and r.Agent in ({0})", vbd.Channels)), sqlconnectionString);
pq.Sql = string.Format(@"select q.*, r.NickName as NickName, r.Account as UserAccount,a.AgentName from QQZoneRecharge as q,Role as r,GServerInfo.AgentUsers as a
where q.UserID = r.ID and r.Agent = a.Id and " + myWhere + "{4} order by q.CreateTime desc limit {2}, {3}",
vbd.StartDate, vbd.ExpirationDate, pq.StartRowNumber, pq.PageSize, vbd.Channels == 0 ? "" : string.Format(" and r.Agent in ({0})", vbd.Channels));
}
PagedList <QQZoneRecharge> obj = new PagedList <QQZoneRecharge>(DAL.PagedListDAL <QQZoneRecharge> .GetListByPage(pq, sqlconnectionString), pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(obj);
}
internal static IEnumerable <OpenFuDai> GetOpenFuDai(BaseDataView vbd)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
StringBuilder str = new StringBuilder();
str.Append("SELECT UserID,UserName ,sum(Count) as Count,Createtime FROM OpenFuDai where DATE_FORMAT( CreateTime, '%Y-%m-%d') = DATE_FORMAT(@StartDate, '%Y-%m-%d') group by UserID order by Count desc limit 0,30;");
IEnumerable <OpenFuDai> i = cn.Query <OpenFuDai>(str.ToString(), vbd);
cn.Close();
return(i);
}
}
internal static IEnumerable <StrongPushADRecord> GetStrongPushADRecord(BaseDataView bdv)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
StringBuilder str = new StringBuilder();
str.AppendFormat(@"
select * from " + database3 + @".strongpushadrecord ORDER BY CreateTime DESC;
");
IEnumerable <StrongPushADRecord> i = cn.Query <StrongPushADRecord>(str.ToString());
cn.Close();
return(i);
}
}
public static PagedList <ServEmail> GetListByPage(BaseDataView bdv)
{
PagerQuery pq = new PagerQuery();
pq.CurrentPage = bdv.Page;
pq.PageSize = 10;
string where = " where ServEmailTime BETWEEN '" + bdv.StartDate + "' and '" + bdv.ExpirationDate + "' ";
pq.RecordCount = DAL.PagedListDAL <ServEmail> .GetRecordCount(@"select count(0) from ServEmail " + where);
pq.Sql = string.Format(@"select * from ServEmail " + where + " order by ServEmailID desc limit {0}, {1}", pq.StartRowNumber, pq.PageSize);
PagedList <ServEmail> obj = new PagedList <ServEmail>(DAL.PagedListDAL <ServEmail> .GetListByPage(pq), pq.CurrentPage, pq.PageSize, pq.RecordCount);
return(obj);
}
public ActionResult QQZoneRechargeDetail(Dictionary <string, string> queryvalues)
{
int _Channels = queryvalues.ContainsKey("Channels") ? Convert.ToInt32(queryvalues["Channels"]) : 0; //渠道id
string _StartDate = queryvalues.ContainsKey("StartDate") ? queryvalues["StartDate"] : DateTime.Now.ToString("yyyy-MM-dd 00:00:00"); //时间
//select * from 515game.Role where CreateTime between '2016-1-5' and '2016-1-6' and Agent=if(a=0,Agent,a) ;
string type = queryvalues.ContainsKey("type") ? queryvalues["type"] : "";
//AllCount
string _allCount = queryvalues.ContainsKey("AllCount") ? queryvalues["AllCount"] : "";
var arr = _StartDate.Split('-');
DateTime dts = new DateTime(Convert.ToInt32(arr[0]), Convert.ToInt32(arr[1]), Convert.ToInt32(_StartDate.Substring(8, 2)), 0, 0, 0);
DateTime dte = dts.AddDays(1);
BaseDataView vbd = new BaseDataView {
StartDate = _StartDate, ExpirationDate = dte.ToString("yyyy-MM-dd 00:00:00"), Channels = _Channels
};
IEnumerable <QQZoneRechargeCountDetail> detail = new List <QQZoneRechargeCountDetail>();
switch (type)
{
case "1":
detail = BaseDataBLL.GetQQZoneRechargeFirstChargeDetail(vbd);
ViewData["AllCount"] = "首次充值人数[" + _allCount + "]";
break;
case "2":
detail = BaseDataBLL.GetQQZoneRechargeReChargeDetail(vbd);
ViewData["AllCount"] = "再次付费人数[" + _allCount + "]";
break;
case "3":
detail = BaseDataBLL.GetQQZoneRechargeCurReChaDetail(vbd);
ViewData["AllCount"] = "当日注册且充值玩家[" + _allCount + "]";
break;
case "4":
detail = BaseDataBLL.GetQQZoneRechargeAllReChaDetail(vbd);
ViewData["AllCount"] = "总充值人数[" + _allCount + "]";
break;
default:
break;
}
return(View(detail));
}
internal static IEnumerable <BaseDataInfo> GetPlayCount(BaseDataView bdv)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
StringBuilder str = new StringBuilder();
str.Append("SELECT DATE_FORMAT( @StartDate, '%Y-%m-%d') as date, sum(count) as count FROM (SELECT Count, CreateTime FROM playcounttexas union all SELECT Count,CreateTime from playcountland) A ");
//switch (bdv.Terminals)
//{
// case terminals.页游:
// str.AppendFormat(" and type = {0}", (int)terminals.页游);
// break;
// case terminals.安卓:
// str.AppendFormat(" and type = {0}", (int)terminals.安卓);
// break;
// case terminals.IOS:
// str.AppendFormat(" and type = {0}", (int)terminals.IOS);
// break;
// case terminals.所有终端:
// default:
// str.Append(" and (type = 1 or type = 2)");
// break;
//}
switch (bdv.Groupby)
{
case groupby.日:
str.Append(" where DATE_FORMAT( CreateTime, '%Y-%m-%d') = DATE_FORMAT(@StartDate, '%Y-%m-%d');");
break;
case groupby.月:
str.Append(" where DATE_FORMAT( CreateTime, '%Y-%m') = DATE_FORMAT(@StartDate, '%Y-%m');");
break;
}
IEnumerable <BaseDataInfo> i = cn.Query <BaseDataInfo>(str.ToString(), bdv);
cn.Close();
return(i);
}
}
