public static string QueryTotalCost(string filter) { string sqlCondi = ""; if (queryInfo.start_time == null || queryInfo.end_time == null) { sqlCondi = string.Format(" last_update_time between '{0}' and '{1}'" , queryInfo.start_time.ToLocalTime().ToString("yyyy/MM/dd HH:mm:ss") , queryInfo.end_time.ToLocalTime().ToString("yyyy/MM/dd HH:mm:ss") ); } if (queryInfo.playerId != 0) { sqlCondi += " and uid = " + queryInfo.playerId; } if (!string.IsNullOrEmpty(filter)) { sqlCondi += GetFilterStringByJson(filter); } string sql = string.Format("SELECT orderID FROM receipt where 1 {0}", sqlCondi); UInt64 totalCost = 0; gmt.Server server = gmt.Server.GetServerAt(0); DatabaseAssistant.Execute(reader => { while (reader.Read()) { string orderInfo = reader.GetString(0); string[] orderArray = orderInfo.Split(','); mw.RmbShopConfig config = null; string index = string.Format("{0}-{1}", orderArray[1], orderArray[4]); TableManager.RmbShopTable.TryGetValue(index, out config); int cost = (null == config) ? config.rmb_cost : 0; totalCost += (uint)cost; } }, server.BillDatabaseAddress, server.BillDatabasePort, server.BillDatabaseCharSet, server.BillDatabase, server.BillDatabaseUserId, server.BillDatabasePassword, sql); return(string.Format(@"{{ ""totalCost"": {0} }}", totalCost)); }
public static bool GetServerDataFromDB(gmt.Server server , string channel_id , DateTime date_start , DateTime date_end , ref List <STServerTableData> listTable) { STServerTableData stData = new STServerTableData(); stData.section = server.Id; stData.serverName = server.Name; stData.channelId = channel_id; Dictionary <string, int> dicRawType = new Dictionary <string, int>() { { "lzsgkr_ios", 1 }, { "lzsgkr_google", 2 }, { "lzsgkr_onestore", 3 } }; string sqlCondi = ""; string sqlAuthCondi = ""; if (!string.IsNullOrEmpty(channel_id)) { sqlCondi += string.Format("and channel_id = '{0}'", channel_id); sqlAuthCondi += string.Format("and raw_type={0}", dicRawType[channel_id]); } if (date_start != new DateTime(0)) { sqlCondi += string.Format(" and date >= '{0}'", date_start.ToString("yyyy/MM/dd HH:mm:ss")); sqlAuthCondi += string.Format(" and last_update_time >= '{0}'", date_start.ToString("yyyy/MM/dd HH:mm:ss")); } if (date_end != new DateTime(0)) { sqlCondi += string.Format(" and date <= '{0}'", date_end.ToString("yyyy/MM/dd HH:mm:ss")); sqlAuthCondi += string.Format(" and last_update_time <= '{0}'", date_end.ToString("yyyy/MM/dd HH:mm:ss")); } string sql = ""; #region 今日活跃 sql = string.Format("SELECT COUNT(DISTINCT cyuid) FROM login_ WHERE cyuid <> 'null' {0};", sqlCondi); bool suc = DatabaseAssistant.Execute(reader => { if (reader.Read()) { stData.activeUserCnt += reader.GetInt32(0); } }, server.LogDatabaseAddress, server.LogDatabasePort, server.LogDatabaseCharSet, server.LogDatabase, server.LogDatabaseUserId, server.LogDatabasePassword, sql); #endregion if (!suc) { return(false); } #region 今日新增 sql = string.Format("SELECT COUNT(DISTINCT uid) FROM `rolebuild_` WHERE uid <> 'null' {0};", sqlCondi); DatabaseAssistant.Execute(reader => { if (reader.Read()) { stData.newUserCnt += reader.GetInt32(0); } }, server.LogDatabaseAddress, server.LogDatabasePort, server.LogDatabaseCharSet, server.LogDatabase, server.LogDatabaseUserId, server.LogDatabasePassword, sql); #endregion #region 今日设备新增 sql = string.Format("SELECT COUNT(DISTINCT deviceid) FROM `rolebuild_` WHERE uid <> 'null' {0};", sqlCondi); DatabaseAssistant.Execute(reader => { if (reader.Read()) { stData.newDeviceCnt += reader.GetInt32(0); } }, server.LogDatabaseAddress, server.LogDatabasePort, server.LogDatabaseCharSet, server.LogDatabase, server.LogDatabaseUserId, server.LogDatabasePassword, sql); #endregion StringBuilder sbIds = new StringBuilder(); #region 新用户付费数 sql = string.Format("SELECT uid FROM rolebuild_ WHERE uid <> 'null' {0}", sqlCondi); DatabaseAssistant.Execute(reader => { while (reader.Read()) { sbIds.Append(reader.GetString(0)); sbIds.Append(","); } if (sbIds.Length != 0) { sbIds.Remove(sbIds.Length - 1, 1); } }, server.LogDatabaseAddress, server.LogDatabasePort, server.LogDatabaseCharSet, server.LogDatabase, server.LogDatabaseUserId, server.LogDatabasePassword, sql ); sql = string.Format("SELECT COUNT(DISTINCT userID) FROM receipt WHERE server_id = {0} and userID IN ({1})" , server.serverID , sbIds.ToString()); DatabaseAssistant.Execute(reader => { if (reader.Read()) { stData.newUserPayCnt += reader.GetInt32(0); } }, server.BillDatabaseAddress, server.BillDatabasePort, server.BillDatabaseCharSet, server.BillDatabase, server.BillDatabaseUserId, server.BillDatabasePassword, sql); #endregion #region 总付费数 sql = string.Format("SELECT COUNT(DISTINCT userID) FROM receipt WHERE server_id = {0} {1}" , server.serverID , sqlAuthCondi); DatabaseAssistant.Execute(reader => { if (reader.Read()) { stData.totalPayCnt += reader.GetInt32(0); } }, server.BillDatabaseAddress, server.BillDatabasePort, server.BillDatabaseCharSet, server.BillDatabase, server.BillDatabaseUserId, server.BillDatabasePassword, sql); #endregion #region 新用户充值额 sql = string.Format("SELECT uid FROM rolebuild_ WHERE uid <> 'null' {0}", sqlCondi); sbIds.Remove(0, sbIds.Length); DatabaseAssistant.Execute(reader => { while (reader.Read()) { sbIds.Append(reader.GetString(0)); sbIds.Append(","); } if (sbIds.Length != 0) { sbIds.Remove(sbIds.Length - 1, 1); } }, server.LogDatabaseAddress, server.LogDatabasePort, server.LogDatabaseCharSet, server.LogDatabase, server.LogDatabaseUserId, server.LogDatabasePassword, sql ); sql = string.Format("SELECT orderID FROM receipt WHERE server_id = {0} and userID IN ({1})" , server.serverID , sbIds.ToString()); DatabaseAssistant.Execute(reader => { while (reader.Read()) { string orderInfo = reader.GetString(0); string[] orderArray = orderInfo.Split(','); mw.RmbShopConfig config = null; string index = string.Format("{0}-{1}", orderArray[1], orderArray[4]); TableManager.RmbShopTable.TryGetValue(index, out config); int cost = (null == config) ? config.rmb_cost : 0; stData.newUserPayVal += (uint)cost; } }, server.BillDatabaseAddress, server.BillDatabasePort, server.BillDatabaseCharSet, server.BillDatabase, server.BillDatabaseUserId, server.BillDatabasePassword, sql); #endregion #region 总充值额 sql = string.Format("SELECT orderID FROM receipt WHERE server_id = {0} {1}" , server.serverID , sqlAuthCondi); Log.AddLog(sql); DatabaseAssistant.Execute(reader => { while (reader.Read()) { string orderInfo = reader.GetString(0); string[] orderArray = orderInfo.Split(','); mw.RmbShopConfig config = null; string index = string.Format("{0}-{1}", orderArray[1], orderArray[4]); TableManager.RmbShopTable.TryGetValue(index, out config); int cost = (null == config) ? config.rmb_cost : 0; stData.totalPayVal += (uint)cost; } }, server.BillDatabaseAddress, server.BillDatabasePort, server.BillDatabaseCharSet, server.BillDatabase, server.BillDatabaseUserId, server.BillDatabasePassword, sql ); #endregion #region 新用户付费率 if (stData.newUserCnt != 0) { stData.newUserPayRate = stData.newUserPayCnt / (float)stData.newUserCnt * 100; } #endregion #region 新增付费用户 sql = string.Format("SELECT last_update_time FROM receipt where server_id = {0} GROUP BY userID;" , server.serverID); DatabaseAssistant.Execute(reader => { while (reader.Read()) { DateTime firstTime = reader.GetDateTime(0); if (firstTime >= date_start && firstTime <= date_end) { stData.newPayUserCnt += 1; } } }, server.BillDatabaseAddress, server.BillDatabasePort, server.BillDatabaseCharSet, server.BillDatabase, server.BillDatabaseUserId, server.BillDatabasePassword, sql); #endregion #region 活跃付费率 if (stData.activeUserCnt != 0) { stData.activePayRate = stData.totalPayCnt / (float)stData.activeUserCnt * 100; } #endregion #region ARPPU if (stData.totalPayCnt != 0) { stData.arppu = stData.totalPayVal / (float)stData.totalPayCnt; } #endregion #region ARPU if (stData.activeUserCnt != 0) { stData.arpu = stData.totalPayVal / (float)stData.activeUserCnt; } #endregion listTable.Add(stData); return(true); }
public static string UpdatePlayerRechargeTableData(int limit , int offset , string sort , string order , string filter ) { #region 数据库查询语句拼接 string sqlCondi = ""; if (queryInfo.start_time != new DateTime(0)) { sqlCondi = string.Format(" last_update_time >= '{0}'", queryInfo.start_time.ToLocalTime().ToString("yyyy/MM/dd HH:mm:ss")); } if (queryInfo.end_time != new DateTime(0)) { sqlCondi = string.Format(" and last_update_time <= '{0}'", queryInfo.end_time.ToLocalTime().ToString("yyyy/MM/dd HH:mm:ss")); } if (queryInfo.playerId != 0) { sqlCondi += " and userID = " + queryInfo.playerId; } if (!string.IsNullOrEmpty(filter)) { sqlCondi += GetFilterStringByJson(filter); } #endregion int totalRows = 0; List <PlayerRechargeInfo> rechargeList = new List <PlayerRechargeInfo>(); #region 查询目标总数 gmt.Server server = gmt.Server.GetServerAt(0); string sql = string.Format("SELECT COUNT(state) FROM receipt where 1 {0}", sqlCondi); DatabaseAssistant.Execute(reader => { if (reader.Read()) { totalRows = reader.GetInt32(0); } }, server.BillDatabaseAddress, server.BillDatabasePort, server.BillDatabaseCharSet, server.BillDatabase, server.BillDatabaseUserId, server.BillDatabasePassword, sql); #endregion if (totalRows != 0) { string sql_order = ""; #region 排序 if (!string.IsNullOrEmpty(sort)) { sql_order = string.Format("ORDER BY {0} {1}", sort, order); } #endregion StringBuilder sbIds = new StringBuilder(); #region 查询id集合 sql = string.Format("select id from receipt where 1 {0} {1} limit {2}, {3}" , sqlCondi , sql_order , offset , limit ); DatabaseAssistant.Execute(reader => { while (reader.Read()) { sbIds.Append(reader.GetString(0)); sbIds.Append(","); } if (sbIds.Length != 0) { sbIds.Remove(sbIds.Length - 1, 1); } }, server.BillDatabaseAddress, server.BillDatabasePort, server.BillDatabaseCharSet, server.BillDatabase, server.BillDatabaseUserId, server.BillDatabasePassword, sql ); #endregion #region 根据id集合查询结果 sql = string.Format("SELECT orderID, state, plat_order_id, raw_type, last_update_time FROM receipt where id in ({0}) {1}" , sbIds.ToString() , sql_order); DatabaseAssistant.Execute(reader => { while (reader.Read()) { PlayerRechargeInfo prInfo = new PlayerRechargeInfo(); string orderInfo = reader.GetString(0); string[] orderArray = orderInfo.Split(','); int.TryParse(orderArray[0], out prInfo.userID); prInfo.cyUid = orderArray[2]; int.TryParse(orderArray[3], out prInfo.serverId); prInfo.serverIdStr = IntToBytes(prInfo.serverId); mw.RmbShopConfig config = null; string index = string.Format("{0}-{1}", orderArray[1], orderArray[4]); TableManager.RmbShopTable.TryGetValue(index, out config); if (null == config) { prInfo.goodsName = config.goods_name; prInfo.goodsCost = config.rmb_cost; } else { prInfo.goodsName = TableManager.GetGMTText(719) + "(" + orderArray[4] + ")"; prInfo.goodsCost = 0; } int.TryParse(orderArray[5], out prInfo.goodsNum); int nState = reader.GetInt32(1); if (ReceiptStateTable.ContainsKey((EReceiptState)nState)) { prInfo.state = ReceiptStateTable[(EReceiptState)nState]; } else { prInfo.state = TableManager.GetGMTText(719) + "(" + nState + ")"; } prInfo.plat_order_id = reader.IsDBNull(2) ? "" : reader.GetString(2); prInfo.raw_type = m_rawType[reader.GetInt16(3)]; prInfo.last_update_time = reader.GetDateTime(4).ToString("yyyy-MM-dd HH:mm:ss"); rechargeList.Add(prInfo); } }, server.BillDatabaseAddress, server.BillDatabasePort, server.BillDatabaseCharSet, server.BillDatabase, server.BillDatabaseUserId, server.BillDatabasePassword, sql); #endregion } string rechargeData = JsonConvert.SerializeObject(rechargeList); return(string.Format(@"{{""error"":0, ""data"": {{""total"":{0}, ""rows"":{1} }} }}", totalRows, rechargeData)); }