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); } }