internal static string GetSumRecharge(Model.BaseDataView model)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
string str = string.Empty;
if (!string.IsNullOrEmpty(model.SearchExt))
{
str = @"select IFNULL(sum(Money) /100,0) from QQZoneRecharge a join " + database1 + @".Role b on a.userid = b.id and (b.id=@SearchExt or b.account=@SearchExt or b.nickname=@SearchExt)
where a.CreateTime between @StartDate and @ExpirationDate and b.agent = case @Channels when 0 then b.agent else @Channels end ";
}
else
{
str = @"select IFNULL(sum(Money) /100,0) from QQZoneRecharge a join " + database1 + @".Role b on a.userid = b.id
where a.CreateTime between @StartDate and @ExpirationDate and b.agent = case @Channels when 0 then b.agent else @Channels end";
}
//if (model.RaType != null )
//{
// if ( Convert.ToInt32(model.RaType) > 0) {
// str += " and PF=@RaType ";
// }
//}
string i = cn.ExecuteScalar(str, model).ToString();
cn.Close();
return(i);
}
}
/// <summary>
/// 新年充值活动-充值排行前10
/// </summary>
/// <param name="vbd"></param>
/// <returns></returns>
internal static IEnumerable <RechargeRank> NewYearChargeRank(Model.BaseDataView vbd)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
StringBuilder str = new StringBuilder();
//str.Append("SELECT DATE_FORMAT(curdate(), '%Y-%m-%d') as date, a.count as count, b.count as activeuser FROM (SELECT count(0) as count FROM Role where Gold > 10) as a, (SELECT count(0) as count FROM Role where Gold <= 10) as b;");
str.Append(@"
select t.*,r.NickName from (
select UserID , sum(Money)/100 as Money from " + database1 + @".QQZoneRecharge
where CreateTime >= @StartDate and CreateTime< @ExpirationDate
GROUP BY UserID
order by Money desc
limit 10 ) t,Role r
where t.UserID = r.ID
");
IEnumerable <RechargeRank> i = cn.Query <RechargeRank>(str.ToString(), vbd);
cn.Close();
return(i);
}
}
internal static string GetFirstSumRecharge(Model.BaseDataView model)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
string str = @"select IFNULL(sum(Money) /100,0)
from QQZoneRecharge
where CreateTime between @StartDate and @ExpirationDate and IsFirst=1 ";
string i = cn.ExecuteScalar(str, model).ToString();
cn.Close();
return(i);
}
}
/// <summary>
/// 新年充值活动
/// </summary>
internal static IEnumerable <NewYearRechargeSum> GetNewYearCharge(Model.BaseDataView vbd)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
StringBuilder str = new StringBuilder();
//str.Append("SELECT DATE_FORMAT(curdate(), '%Y-%m-%d') as date, a.count as count, b.count as activeuser FROM (SELECT count(0) as count FROM Role where Gold > 10) as a, (SELECT count(0) as count FROM Role where Gold <= 10) as b;");
str.Append(@" select count(*) as Count,GiftID as Postion from " + database1 + @".BG_ActiveRecord
where CreateTime >= @StartDate and CreateTime< @ExpirationDate and ActiveID=2
GROUP BY GiftID;
");
IEnumerable <NewYearRechargeSum> i = cn.Query <NewYearRechargeSum>(str.ToString(), vbd);
cn.Close();
return(i);
}
}
internal static IEnumerable <FirstChargeItem> GetFirstRechargeItemCount(Model.BaseDataView vbd)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
StringBuilder str = new StringBuilder();
//str.Append("SELECT DATE_FORMAT(curdate(), '%Y-%m-%d') as date, a.count as count, b.count as activeuser FROM (SELECT count(0) as count FROM Role where Gold > 10) as a, (SELECT count(0) as count FROM Role where Gold <= 10) as b;");
str.Append(@" select count(*) as Count,PayItem from " + database1 + @".QQZoneRecharge
where PayItem in ('firstCharge_1', 'firstCharge_2', 'firstCharge_3', 'firstCharge_4')
and CreateTime between @StartDate and @ExpirationDate
GROUP BY PayItem
");
IEnumerable <FirstChargeItem> i = cn.Query <FirstChargeItem>(str.ToString(), vbd);
cn.Close();
return(i);
}
}
/// <summary>
/// 德州玩牌领大奖
/// </summary>
/// <param name="vbd"></param>
/// <returns></returns>
internal static IEnumerable <TexasGameGetAward> GetTexasGameGetAwardItemCount(Model.BaseDataView vbd)
{
using (var cn = new MySqlConnection(sqlconnectionString))
{
cn.Open();
StringBuilder str = new StringBuilder();
//str.Append("SELECT DATE_FORMAT(curdate(), '%Y-%m-%d') as date, a.count as count, b.count as activeuser FROM (SELECT count(0) as count FROM Role where Gold > 10) as a, (SELECT count(0) as count FROM Role where Gold <= 10) as b;");
if (vbd.StartDate == "")
{
str.Append(@"
select date(CreateTime) CreateTime
,sum(case GiftID when 0 then 1 else 0 end) as Position_18
,sum(case GiftID when 1 then 1 else 0 end) as Position_58
,sum(case GiftID when 2 then 1 else 0 end) as Position_118
,sum(case GiftID when 3 then 1 else 0 end) as Position_238
,sum(case GiftID when 4 then 1 else 0 end) as Position_388
from " + database1 + @".BG_ActiveRecord
where ActiveID=1
GROUP BY date(CreateTime)
");
}
else
{
str.Append(@"
select date(CreateTime) CreateTime
,sum(case GiftID when 0 then 1 else 0 end) as Position_18
,sum(case GiftID when 1 then 1 else 0 end) as Position_58
,sum(case GiftID when 2 then 1 else 0 end) as Position_118
,sum(case GiftID when 3 then 1 else 0 end) as Position_238
,sum(case GiftID when 4 then 1 else 0 end) as Position_388
from " + database1 + @".BG_ActiveRecord
where CreateTime >= @StartDate and CreateTime< @ExpirationDate and ActiveID=1
GROUP BY date(CreateTime)
");
}
IEnumerable <TexasGameGetAward> i = cn.Query <TexasGameGetAward>(str.ToString(), vbd);
cn.Close();
return(i);
}
}
