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