/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ExportExcel(object sender, EventArgs e)
{
if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text))
{
Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError);
return;
}
string sqlWhere = "";
DateTime searchdateB = DateTime.Now.AddDays(-6);
DateTime searchdateE = DateTime.Now;
if (tboxTimeB.Text.Length > 0)
{
searchdateB = Convert.ToDateTime(tboxTimeB.Text);
}
if (tboxTimeE.Text.Length > 0)
{
searchdateE = Convert.ToDateTime(tboxTimeE.Text);
}
if (!string.IsNullOrEmpty(ddlChannel.SelectedValue.Trim()))
{
sqlWhere += string.Format(" AND F_ChannelID=N'{0}'", ddlChannel.SelectedValue);
}
string sql = string.Format("SELECT CONVERT(VARCHAR(100),F_InsertTime,23) 日期,COUNT(F_GlobalID) 充值次数,COUNT(DISTINCT F_UserID) 充值人数,SUM(F_Price) 充值金额,SUM(F_Price)/COUNT(DISTINCT F_UserID) ARPPU FROM T_Deposit_Verify_Result_Log WHERE F_DepositResult=1 AND CONVERT(VARCHAR(100),F_InsertTime,23)>='{0}' AND CONVERT(VARCHAR(100),F_InsertTime,23)<='{1}' {2} GROUP BY CONVERT(VARCHAR(100),F_InsertTime,23) ORDER BY CONVERT(VARCHAR(100),F_InsertTime,23)", searchdateB.ToString("yyyy-MM-dd"), searchdateE.ToString("yyyy-MM-dd"), sqlWhere);
ds = DBHelperUserCoreDB.Query(sql);
ExportExcelHelper.ExportDataSet(ds);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ExportExcel(object sender, EventArgs e)
{
if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text))
{
Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError);
return;
}
string sqlWhere = "";
DateTime searchdateB = DateTime.Now.AddDays(-6);
DateTime searchdateE = DateTime.Now;
if (tboxTimeB.Text.Length > 0)
{
searchdateB = Convert.ToDateTime(tboxTimeB.Text);
}
if (tboxTimeE.Text.Length > 0)
{
searchdateE = Convert.ToDateTime(tboxTimeE.Text);
}
if (!string.IsNullOrEmpty(ddlChannel.SelectedValue.Trim()))
{
sqlWhere += string.Format(" AND F_ChannelID=N'{0}'", ddlChannel.SelectedValue);
}
string sql = string.Format("SELECT F_UserID 账号ID,F_GlobalID 角色ID,F_ZoneID 区服,F_ChannelID 渠道,F_TransactionID 充值订单号,F_ProductID 商品ID,F_Price 充值金额,F_PlayerLevel 等级,F_InsertTime 充值时间,F_StoreName 商店名称,F_DepositResult 充值结果 FROM T_Deposit_Verify_Result_Log WHERE 1=1 AND CONVERT(VARCHAR(100),F_InsertTime,23)>='{0}' AND CONVERT(VARCHAR(100),F_InsertTime,23)<='{1}' {2}", searchdateB.ToString("yyyy-MM-dd"), searchdateE.ToString("yyyy-MM-dd"), sqlWhere);
ds = DBHelperUserCoreDB.Query(sql);
ExportExcelHelper.ExportDataSet(ds);
}
protected void btnCreateToExcel_Click(object sender, EventArgs e)
{
if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text))
{
Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError);
return;
}
string sqlWhere = "";
DateTime searchdateB = DateTime.Now.AddDays(-7);
DateTime searchdateE = DateTime.Now;
if (tboxTimeB.Text.Length > 0)
{
searchdateB = Convert.ToDateTime(tboxTimeB.Text);
}
if (tboxTimeE.Text.Length > 0)
{
searchdateE = Convert.ToDateTime(tboxTimeE.Text);
}
if (!string.IsNullOrEmpty(ddlChannel.SelectedValue.Trim()))
{
sqlWhere += string.Format(" AND F_ChannelID=N'{0}'", ddlChannel.SelectedValue);
}
string sql = string.Format("SELECT CONVERT(VARCHAR(100),F_CreateTime,23) 时间,COUNT(DISTINCT F_UserID) 玩家数 FROM (SELECT F_UserID,F_CreateTime FROM T_RoleBaseData_0 WHERE CONVERT(VARCHAR(100),F_CreateTime,23)>='{0}' AND CONVERT(VARCHAR(100),F_CreateTime,23)<='{1}' UNION ALL SELECT F_UserID,F_CreateTime FROM T_RoleBaseData_1 WHERE CONVERT(VARCHAR(100),F_CreateTime,23)>='{0}' AND CONVERT(VARCHAR(100),F_CreateTime,23)<='{1}' UNION ALL SELECT F_UserID,F_CreateTime FROM T_RoleBaseData_2 WHERE CONVERT(VARCHAR(100),F_CreateTime,23)>='{0}' AND CONVERT(VARCHAR(100),F_CreateTime,23)<='{1}' UNION ALL SELECT F_UserID,F_CreateTime FROM T_RoleBaseData_3 WHERE CONVERT(VARCHAR(100),F_CreateTime,23)>='{0}' AND CONVERT(VARCHAR(100),F_CreateTime,23)<='{1}' UNION ALL SELECT F_UserID,F_CreateTime FROM T_RoleBaseData_4 WHERE CONVERT(VARCHAR(100),F_CreateTime,23)>='{0}' AND CONVERT(VARCHAR(100),F_CreateTime,23)<='{1}')TEMP GROUP BY CONVERT(VARCHAR(100),F_CreateTime,23)", searchdateB.ToString("yyyy-MM-dd"), searchdateE.ToString("yyyy-MM-dd"), sqlWhere);
ds = DBHelperGameCoreDB.Query(sql);
ExportExcelHelper.ExportDataSet(ds);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ExportExcel(object sender, EventArgs e)
{
if (!Common.Validate.IsDateTime(tboxTimeB.Text))
{
Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError);
return;
}
string sqlWhere = "";
DateTime searchdateB = DateTime.Now;
if (tboxTimeB.Text.Length > 0)
{
searchdateB = Convert.ToDateTime(tboxTimeB.Text);
}
if (!string.IsNullOrEmpty(ddlChannel.SelectedValue.Trim()))
{
sqlWhere += string.Format(" AND F_ChannelID=N'{0}'", ddlChannel.SelectedValue);
}
string sql = string.Format("SELECT LEFT(CONVERT(VARCHAR(100),F_LoginTime,120), 13) 时间,COUNT(F_UserID) 登录次数,COUNT(DISTINCT F_UserID) 登录用户数 FROM T_UserEnterExitLog{0} GROUP BY LEFT(CONVERT(VARCHAR(100),F_LoginTime,120), 13) ORDER BY LEFT(CONVERT(VARCHAR(100),F_LoginTime,120), 13)", searchdateB.ToString("yyyyMMdd"));
ds = DBHelperGameLogDB.Query(sql);
ExportExcelHelper.ExportDataSet(ds);
}
protected void ExportExcel(object sender, EventArgs e)
{
DateTime searchdateB = DateTime.Now;
DateTime searchdateE = DateTime.Now;
LabelTime.Text = searchdateB.ToString(SmallDateTimeFormat) + " ";
string sqlwhere = "where 1=1";
if (!string.IsNullOrEmpty(tbCorps.Text))
{
sqlwhere += @" and rtrim(CorpsName)=N'" + tbCorps.Text.Trim() + "'";
}
string bigZone = DropDownListArea1.SelectedIndex > 0 ? DropDownListArea1.SelectedValue.Split(',')[1] + "" : "0";
string battleZone = string.Empty;
if (WssPublish == "0")
{
battleZone = DropDownListArea2.SelectedIndex > 0 ? DropDownListArea2.SelectedValue.Split(',')[1] + "" : "1";
}
else
{
battleZone = DropDownListArea2.SelectedIndex > 0 ? DropDownListArea2.SelectedValue.Split(',')[1] + "" : "5";
}
string sql = "";
sql = @"SELECT * FROM OPENQUERY ([LKSV_3_gsdata_db_" + bigZone + "_" + battleZone + "],'SELECT szName 玩家名称,CorpsName 公会名称,CorpsNickName 公会旗帜,CM.CurActivity 公会贡献度,CorpsLevel 公会驻地,CorpsSkillLv 公会技能,CorpsShopLv 公会商店,CorpsWoodlv 公会木材,CorpsWoodStorageLv 公会木材仓库,CorpsInteriorLv 公会城堡 FROM playerbaseinfo PB LEFT JOIN corps_baseinfotable CB ON CB.CorpsID=PB.CorpsID LEFT JOIN corps_membertable CM ON CM.CorpsID=CB.CorpsID AND PB.nGlobalID=CM.GlobalID where 1=1 and PB.CorpsID<>-1' ) ";
ds = DBHelperDigGameDB.Query(sql);
ExportExcelHelper.ExportDataSet(ds);
}
protected void ExportExcel(object sender, EventArgs e)
{
DateTime searchdateB = DateTime.Now;
DateTime searchdateE = DateTime.Now;
LabelTime.Text = searchdateB.ToString(SmallDateTimeFormat) + " ";
string sqlwhere = "";
if (!string.IsNullOrEmpty(DropDownList1.SelectedValue))
{
sqlwhere += @"where 1=1 and UnionId=" + DropDownList1.SelectedValue;
}
string bigZone = DropDownListArea1.SelectedIndex > 0 ? DropDownListArea1.SelectedValue.Split(',')[1] + "" : "0";
string battleZone = string.Empty;
if (WssPublish == "0")
{
battleZone = DropDownListArea2.SelectedIndex > 0 ? DropDownListArea2.SelectedValue.Split(',')[1] + "" : "1";
}
else
{
battleZone = DropDownListArea2.SelectedIndex > 0 ? DropDownListArea2.SelectedValue.Split(',')[1] + "" : "5";
}
string sql = "";
sql = @"SELECT * FROM OPENQUERY ([LKSV_3_gsdata_db_" + bigZone + "_" + battleZone + "],'SELECT szName 玩家名称,nLevel 玩家等级,(CASE WHEN UnionId=1 THEN \"联盟阵营\" WHEN UnionId=2 THEN \"混沌军团\" ELSE \"\" END) 阵营,MeritLevel 玩家军衔等级 FROM playerbaseinfo WHERE 1=1' ) ";
ds = DBHelperDigGameDB.Query(sql);
ExportExcelHelper.ExportDataSet(ds);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ExportExcel(object sender, EventArgs e)
{
if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text))
{
Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError);
return;
}
string sqlWhere = "";
DateTime searchdateB = DateTime.Now.AddDays(-6);
DateTime searchdateE = DateTime.Now;
if (tboxTimeB.Text.Length > 0)
{
searchdateB = Convert.ToDateTime(tboxTimeB.Text);
}
if (tboxTimeE.Text.Length > 0)
{
searchdateE = Convert.ToDateTime(tboxTimeE.Text);
}
if (!string.IsNullOrEmpty(ddlChannel.SelectedValue.Trim()))
{
sqlWhere += string.Format(" AND F_ChannelID=N'{0}'", ddlChannel.SelectedValue);
}
string sql = string.Format("SELECT F_ZoneID 区服,F_UserID 用户编号,F_GlobalID 角色编号,COUNT(F_GlobalID) 充值次数,SUM(F_Price) 充值金额,MAX(F_InsertTime) 最后一次充值时间,MAX(F_PlayerLevel) 最后一次充值等级 FROM T_Deposit_Verify_Result_Log WHERE F_DepositResult=1 AND CONVERT(VARCHAR(100),F_InsertTime,23)>='{0}' AND CONVERT(VARCHAR(100),F_InsertTime,23)<='{1}' {2} GROUP BY F_ZoneID,F_UserID,F_GlobalID ORDER BY F_ZoneID", searchdateB.ToString("yyyy-MM-dd"), searchdateE.ToString("yyyy-MM-dd"), sqlWhere);
ds = DBHelperUserCoreDB.Query(sql);
DataSet newDataSet = new DataSet();
DataTable dt = new DataTable();
dt.Columns.Add("区服", System.Type.GetType("System.String"));
dt.Columns.Add("用户编号", System.Type.GetType("System.String"));
dt.Columns.Add("角色编号", System.Type.GetType("System.String"));
dt.Columns.Add("角色名称", System.Type.GetType("System.String"));
dt.Columns.Add("充值次数", System.Type.GetType("System.String"));
dt.Columns.Add("充值金额", System.Type.GetType("System.String"));
dt.Columns.Add("最后一次充值时间", System.Type.GetType("System.String"));
dt.Columns.Add("最后一次充值等级", System.Type.GetType("System.String"));
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow newRow = dt.NewRow();
newRow["区服"] = ds.Tables[0].Rows[i]["区服"];
newRow["用户编号"] = ds.Tables[0].Rows[i]["用户编号"];
newRow["角色编号"] = ds.Tables[0].Rows[i]["角色编号"];
newRow["角色名称"] = GetRoleName(ds.Tables[0].Rows[i]["角色编号"].ToString());
newRow["充值次数"] = ds.Tables[0].Rows[i]["充值次数"];
newRow["充值金额"] = ds.Tables[0].Rows[i]["充值金额"];
newRow["最后一次充值时间"] = ds.Tables[0].Rows[i]["最后一次充值时间"];
newRow["最后一次充值等级"] = ds.Tables[0].Rows[i]["最后一次充值等级"];
dt.Rows.Add(newRow);
}
newDataSet.Tables.Add(dt);
ExportExcelHelper.ExportDataSet(newDataSet);
}
protected void ExportExcel(object sender, EventArgs e)
{
if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text))
{
Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError);
return;
}
LabelArea.Text = DropDownListArea1.SelectedItem.Text;
if (DropDownListArea2.SelectedIndex > 0)
{
LabelArea.Text += "|" + DropDownListArea2.SelectedItem.Text;
}
if (DropDownListArea3.SelectedIndex > 0)
{
LabelArea.Text += "|" + DropDownListArea3.SelectedItem.Text;
}
DateTime searchdateB = DateTime.Now;
DateTime searchdateE = DateTime.Now;
if (tboxTimeB.Text.Length > 0)
{
searchdateB = Convert.ToDateTime(tboxTimeB.Text);
}
if (tboxTimeE.Text.Length > 0)
{
searchdateE = Convert.ToDateTime(tboxTimeE.Text);
}
LabelTime.Text = searchdateB.ToString(SmallDateTimeFormat) + " " + App_GlobalResources.Language.LblTo + " " + searchdateE.ToString(SmallDateTimeFormat);
string sql = "";
sql = @"SELECT CONVERT(VARCHAR(100),F_InsertTime,23) 日期,SUM(F_Price) 金额,COUNT(DISTINCT F_UserID) 人数,COUNT(F_UserID) 充值次数
FROM LKSV_5_UserCoreDB_0_1.[UserCoreDB].[dbo].[T_Deposit_Verify_Result_Log]
GROUP BY CONVERT(VARCHAR(100),F_InsertTime,23) having CONVERT(VARCHAR(100),F_InsertTime,23)>='" + searchdateB.ToString(SmallDateTimeFormat) + "' and CONVERT(VARCHAR(100),F_InsertTime,23)<='" + searchdateE.ToString(SmallDateTimeFormat) + "' ORDER BY CONVERT(VARCHAR(100),F_InsertTime,23) DESC";
//if (DropDownListArea1.SelectedIndex > 0)
//{
// sql += @" and F_BigZone=" + DropDownListArea1.SelectedValue.Split(',')[1] + "";
//}
//if (DropDownListArea2.SelectedIndex > 0)
//{
// sql += @" and F_ZoneID=" + DropDownListArea2.SelectedValue.Split(',')[1] + "";
//}
ds = DBHelperDigGameDB.Query(sql);
ExportExcelHelper.ExportDataSet(ds);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ExportExcel(object sender, EventArgs e)
{
if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text))
{
Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError);
return;
}
LabelArea.Text = DropDownListArea1.SelectedItem.Text;
if (DropDownListArea2.SelectedIndex > 0)
{
LabelArea.Text += "|" + DropDownListArea2.SelectedItem.Text;
}
DateTime searchdateB = DateTime.Now;
DateTime searchdateE = DateTime.Now;
if (tboxTimeB.Text.Length > 0)
{
searchdateB = Convert.ToDateTime(tboxTimeB.Text);
}
if (tboxTimeE.Text.Length > 0)
{
searchdateE = Convert.ToDateTime(tboxTimeE.Text);
}
string sqlWhere = "WHERE 1=1";
sqlWhere += string.Format(" AND DATE_FORMAT(SendGiftsTime,\"%Y-%m-%d\")>=\"{0}\"", searchdateB.ToString("yyyy-MM-dd"));
sqlWhere += string.Format(" AND DATE_FORMAT(SendGiftsTime,\"%Y-%m-%d\")<=\"{0}\"", searchdateE.ToString("yyyy-MM-dd"));
string bigZone = DropDownListArea1.SelectedIndex > 0 ? DropDownListArea1.SelectedValue.Split(',')[1] + "" : "0"; //大区
string battleZone = string.Empty; //战区
if (WssPublish == "0")
{
battleZone = DropDownListArea2.SelectedIndex > 0 ? DropDownListArea2.SelectedValue.Split(',')[1] + "" : "1";
}
else
{
battleZone = DropDownListArea2.SelectedIndex > 0 ? DropDownListArea2.SelectedValue.Split(',')[1] + "" : "5";
}
string sql = string.Format("SELECT * FROM OPENQUERY ([LKSV_3_gsdata_db_{0}_{1}],'SELECT DATE_FORMAT(SendGiftsTime,\"%Y-%m-%d\") SendGiftsTime,COUNT(DISTINCT SenderGlobalID) PeopleNum,COUNT(SenderGlobalID) CountNum FROM friendgifts {2} GROUP BY DATE_FORMAT(SendGiftsTime,\"%Y-%m-%d\")')", bigZone, battleZone, sqlWhere);
ds = DBHelperDigGameDB.Query(sql);
ExportExcelHelper.ExportDataSet(ds);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ExportExcel(object sender, EventArgs e)
{
if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text))
{
Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError);
return;
}
DateTime searchdateB = DateTime.Now.AddDays(-6);
DateTime searchdateE = DateTime.Now;
if (tboxTimeB.Text.Length > 0)
{
searchdateB = Convert.ToDateTime(tboxTimeB.Text);
}
if (tboxTimeE.Text.Length > 0)
{
searchdateE = Convert.ToDateTime(tboxTimeE.Text);
}
string sql = string.Format("SELECT COUNT (F_UserID) 充值总人数,SUM (CASE WHEN Num = 2 THEN 1 ELSE 0 END) 付费两次用户,SUM (CASE WHEN Num = 3 THEN 1 ELSE 0 END) 付费三次用户,CAST (CONVERT (DECIMAL (18, 2),SUM (CASE WHEN Num = 2 THEN 1 ELSE 0 END) / CAST (ISNULL(NULLIF(COUNT (F_UserID), 0), 1) AS FLOAT) * 100) AS VARCHAR (10)) + '%' AS 两次付费率,CAST (CONVERT (DECIMAL (18, 2),SUM (CASE WHEN Num = 3 THEN 1 ELSE 0 END) / CAST (ISNULL(NULLIF(COUNT (F_UserID), 0), 1) AS FLOAT) * 100) AS VARCHAR (10)) + '%' AS 三次付费率 FROM (SELECT F_UserID,COUNT (F_UserID) Num FROM UserCoreDB.dbo.T_Deposit_Verify_Result_Log WHERE F_DepositResult = 1 AND CONVERT (VARCHAR (100),F_InsertTime,23) >= '{0}' AND CONVERT (VARCHAR (100),F_InsertTime,23) <= '{1}' GROUP BY F_UserID) TEMP", searchdateB.ToString("yyyy-MM-dd"), searchdateE.ToString("yyyy-MM-dd"));
ds = DBHelperUserCoreDB.Query(sql);
ExportExcelHelper.ExportDataSet(ds);
}
protected void ExportExcel(object sender, EventArgs e)
{
if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text))
{
Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError);
return;
}
LabelArea.Text = DropDownListArea1.SelectedItem.Text;
if (DropDownListArea2.SelectedIndex > 0)
{
LabelArea.Text += "|" + DropDownListArea2.SelectedItem.Text;
}
DateTime searchdateB = DateTime.Now;
DateTime searchdateE = DateTime.Now;
if (tboxTimeB.Text.Length > 0)
{
searchdateB = Convert.ToDateTime(tboxTimeB.Text);
}
if (tboxTimeE.Text.Length > 0)
{
searchdateE = Convert.ToDateTime(tboxTimeE.Text);
}
LabelTime.Text = searchdateB.ToString(SmallDateTimeFormat) + " ";
string sqlwhere = " where 1=1 ";
if (!string.IsNullOrEmpty(tbEmailType.Text.Trim()))
{
sqlwhere += @" and Mail_TYPE=" + tbEmailType.Text.Trim();
}
if (!string.IsNullOrEmpty(tbRoleID.Text.Trim()))
{
sqlwhere += @" and Receiver_ID=" + tbRoleID.Text.Trim();
}
sqlwhere += " and Send_Time>\"" + Convert.ToDateTime(tboxTimeB.Text).ToString("yyyy-MM-dd") + "\"";
sqlwhere += " and Send_Time<\"" + Convert.ToDateTime(tboxTimeE.Text).AddDays(1).ToString("yyyy-MM-dd") + "\"";
string bigZone = DropDownListArea1.SelectedIndex > 0 ? DropDownListArea1.SelectedValue.Split(',')[1] + "" : "0";
string battleZone = string.Empty;
if (WssPublish == "0")
{
battleZone = DropDownListArea2.SelectedIndex > 0 ? DropDownListArea2.SelectedValue.Split(',')[1] + "" : "1";
}
else
{
battleZone = DropDownListArea2.SelectedIndex > 0 ? DropDownListArea2.SelectedValue.Split(',')[1] + "" : "5";
}
string sql = "";
sql = "SELECT * FROM OPENQUERY ([LKSV_3_gsdata_db_" + bigZone + "_" + battleZone + "],'SELECT Mail_Content 邮件内容,Mail_TYPE 邮件类型,Receiver_ID 接收人角色ID,Send_Time 发送时间,Invalid_Time 失效时间,State_Modi_Time 操作时间,Mail_State 邮件状态,Money 金币,Money_Sliver 银币,TongBao 红钻,TongBei 蓝钻,Item1,Item2,Item3,Item4,Item5,IsDelete 是否删除 FROM (SELECT Mail_Content,Mail_TYPE,Receiver_ID,Send_Time,Invalid_Time,State_Modi_Time,Mail_State,Money,Money_Sliver,TongBao,TongBei,Item1,Item2,Item3,Item4,Item5,0 AS IsDelete FROM maillist_table WHERE Mail_TYPE <> 120 UNION ALL SELECT Mail_Content,Mail_TYPE,Receiver_ID,Send_Time,Invalid_Time,State_Modi_Time,Mail_State,Money,Money_Sliver,TongBao,TongBei,Item1,Item2,Item3,Item4,Item5,0 AS IsDelete FROM invalid_mail_table WHERE Mail_TYPE <> 120) A " + sqlwhere + " ORDER BY ITEM1 DESC limit " + (Convert.ToInt32(lblcurPage.Text) - 1) * 20 + ",20')";
ds = DBHelperDigGameDB.Query(sql);
ds.Tables[0].Columns.Add("道具1", typeof(string));
ds.Tables[0].Columns.Add("数量1", typeof(string));
ds.Tables[0].Columns.Add("道具2", typeof(string));
ds.Tables[0].Columns.Add("数量2", typeof(string));
ds.Tables[0].Columns.Add("道具3", typeof(string));
ds.Tables[0].Columns.Add("数量3", typeof(string));
ds.Tables[0].Columns.Add("道具4", typeof(string));
ds.Tables[0].Columns.Add("数量4", typeof(string));
ds.Tables[0].Columns.Add("道具5", typeof(string));
ds.Tables[0].Columns.Add("数量5", typeof(string));
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
int item1 = 0;
int itemBak1 = 0;
byte[] btItem1 = (byte[])ds.Tables[0].Rows[i]["Item1"];
if (btItem1.Length >= 8)
{
item1 = BitConverter.ToInt32(btItem1, 0);
itemBak1 = BitConverter.ToInt32(btItem1, 4);
}
ds.Tables[0].Rows[i]["道具1"] = item1;
ds.Tables[0].Rows[i]["数量1"] = itemBak1;
int item2 = 0;
int itemBak2 = 0;
byte[] btItem2 = (byte[])ds.Tables[0].Rows[i]["Item2"];
if (btItem2.Length >= 8)
{
item2 = BitConverter.ToInt32(btItem2, 0);
itemBak2 = BitConverter.ToInt32(btItem2, 4);
}
ds.Tables[0].Rows[i]["道具2"] = item2;
ds.Tables[0].Rows[i]["数量2"] = itemBak2;
int item3 = 0;
int itemBak3 = 0;
byte[] btItem3 = (byte[])ds.Tables[0].Rows[i]["Item3"];
if (btItem3.Length >= 8)
{
item3 = BitConverter.ToInt32(btItem3, 0);
itemBak3 = BitConverter.ToInt32(btItem3, 4);
}
ds.Tables[0].Rows[i]["道具3"] = item3;
ds.Tables[0].Rows[i]["数量3"] = itemBak3;
int item4 = 0;
int itemBak4 = 0;
byte[] btItem4 = (byte[])ds.Tables[0].Rows[i]["Item4"];
if (btItem4.Length >= 8)
{
item4 = BitConverter.ToInt32(btItem4, 0);
itemBak4 = BitConverter.ToInt32(btItem4, 4);
}
ds.Tables[0].Rows[i]["道具4"] = item4;
ds.Tables[0].Rows[i]["数量4"] = itemBak4;
int item5 = 0;
int itemBak5 = 0;
byte[] btItem5 = (byte[])ds.Tables[0].Rows[i]["Item5"];
if (btItem5.Length >= 8)
{
item5 = BitConverter.ToInt32(btItem5, 0);
itemBak5 = BitConverter.ToInt32(btItem5, 4);
}
ds.Tables[0].Rows[i]["道具5"] = item5;
ds.Tables[0].Rows[i]["数量5"] = itemBak5;
}
ds.Tables[0].Columns.Remove("Item1");
ds.Tables[0].Columns.Remove("Item2");
ds.Tables[0].Columns.Remove("Item3");
ds.Tables[0].Columns.Remove("Item4");
ds.Tables[0].Columns.Remove("Item5");
ExportExcelHelper.ExportDataSet(ds);
}
protected void ExportExcel(object sender, EventArgs e)
{
if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text))
{
Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError);
return;
}
LabelArea.Text = DropDownListArea1.SelectedItem.Text;
if (DropDownListArea2.SelectedIndex > 0)
{
LabelArea.Text += "|" + DropDownListArea2.SelectedItem.Text;
}
if (DropDownListArea3.SelectedIndex > 0)
{
LabelArea.Text += "|" + DropDownListArea3.SelectedItem.Text;
}
DateTime searchdateB = DateTime.Now;
DateTime searchdateE = DateTime.Now;
if (tboxTimeB.Text.Length > 0)
{
searchdateB = Convert.ToDateTime(tboxTimeB.Text);
}
if (tboxTimeE.Text.Length > 0)
{
searchdateE = Convert.ToDateTime(tboxTimeE.Text);
}
LabelTime.Text = searchdateB.ToString(SmallDateTimeFormat) + " ";
string sqlwhere = " where 1=1 ";
if (!string.IsNullOrEmpty(tbRoleId.Text.Trim()))
{
sqlwhere += @" and CID=" + tbRoleId.Text.Trim();
}
if (!string.IsNullOrEmpty(ddlOpid.SelectedValue))
{
sqlwhere += @" and OPID=" + ddlOpid.SelectedValue;
}
if (!string.IsNullOrEmpty(txtPARA_1.Text.Trim()))
{
sqlwhere += @" and PARA_1=" + txtPARA_1.Text.Trim();
}
if (!string.IsNullOrEmpty(txtPARA_2.Text.Trim()))
{
sqlwhere += @" and PARA_2=" + txtPARA_2.Text.Trim();
}
string bigZone = DropDownListArea1.SelectedIndex > 0 ? DropDownListArea1.SelectedValue.Split(',')[1] + "" : "0";
string battleZone = string.Empty;
if (WssPublish == "0")
{
battleZone = DropDownListArea2.SelectedIndex > 0 ? DropDownListArea2.SelectedValue.Split(',')[1] + "" : "1";
}
else
{
battleZone = DropDownListArea2.SelectedIndex > 0 ? DropDownListArea2.SelectedValue.Split(',')[1] + "" : "5";
}
string sql = "";
sql = "SELECT * FROM OPENQUERY ([LKSV_0_GSLOG_DB_" + bigZone + "_" + battleZone + "],'SELECT CONCAT(PARA_2,\"#\") 职业,\"激活\",SUBSTRING_INDEX(OP_BAK,\" \",3) 时装分类,SUBSTRING_INDEX(OP_BAK,\" \",3) 时装对应部位,COUNT(SUBSTRING_INDEX(OP_BAK,\" \",3)) 数量 FROM "+ searchdateB.ToString("yyyy_MM_dd") + "_other_log WHERE OPID = 50144 AND SUBSTRING_INDEX(OP_BAK,\" \",1)=1 GROUP BY PARA_2,SUBSTRING_INDEX(OP_BAK,\" \",3)')";
ds = DBHelperDigGameDB.Query(sql);
if (ds != null)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
ds.Tables[0].Rows[i][0] = GetPro1(ds.Tables[0].Rows[i][0].ToString());
ds.Tables[0].Rows[i][2] = GetOPTYPE(ds.Tables[0].Rows[i][2].ToString());
ds.Tables[0].Rows[i][3] = GetOPTYPEITEMS(ds.Tables[0].Rows[i][3].ToString());
}
}
ExportExcelHelper.ExportDataSet(ds);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ExportExcel(object sender, EventArgs e)
{
if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text))
{
Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError);
return;
}
LabelArea.Text = DropDownListArea1.SelectedItem.Text;
if (DropDownListArea2.SelectedIndex > 0)
{
LabelArea.Text += "|" + DropDownListArea2.SelectedItem.Text;
}
DateTime searchdateB = DateTime.Now;
DateTime searchdateE = DateTime.Now;
if (tboxTimeB.Text.Length > 0)
{
searchdateB = Convert.ToDateTime(tboxTimeB.Text);
}
if (tboxTimeE.Text.Length > 0)
{
searchdateE = Convert.ToDateTime(tboxTimeE.Text);
}
int num = searchdateE.Subtract(searchdateB).Days;
if (num < 0)
{
Response.Write("<Script Language=JavaScript>alert('结束时间不能晚于开始时间！');</Script>");
return;
}
string sqlWhere = "WHERE 1=1";
if (!string.IsNullOrEmpty(tbUserID.Text.Trim()))
{
sqlWhere += string.Format(" AND PARA_1={0}", tbUserID.Text.Trim());
}
string bigZone = DropDownListArea1.SelectedIndex > 0 ? DropDownListArea1.SelectedValue.Split(',')[1] + "" : "0"; //大区
string battleZone = string.Empty; //战区
if (WssPublish == "0")
{
battleZone = DropDownListArea2.SelectedIndex > 0 ? DropDownListArea2.SelectedValue.Split(',')[1] + "" : "1";
}
else
{
battleZone = DropDownListArea2.SelectedIndex > 0 ? DropDownListArea2.SelectedValue.Split(',')[1] + "" : "5";
}
string sql = string.Format("SELECT F_UserID,F_UserName,Sum(F_TimeCount),{0} DayNum,Sum(F_TimeCount)/{1} TimeAvg FROM (", num + 1, num + 1);
for (int i = 0; i <= num; i++)
{
string strSTime = searchdateB.AddDays(i).ToString("yyyyMMdd");
if (i == 0)
{
sql += string.Format("SELECT F_UserID,F_UserName,F_TimeCount FROM [LKSV_1_GameLogDB_0_1].Gamelogdb.dbo.T_UserEnterExitLog{0} {1}", strSTime, sqlWhere);
}
else
{
sql += string.Format(" UNION ALL SELECT F_UserID,F_UserName,F_TimeCount FROM [LKSV_1_GameLogDB_0_1].Gamelogdb.dbo.T_UserEnterExitLog{0} {1}", strSTime, sqlWhere);
}
}
sql += ")TEMP GROUP BY F_UserID,F_UserName ";
ds = DBHelperDigGameDB.Query(sql);
ExportExcelHelper.ExportDataSet(ds);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ExportExcel(object sender, EventArgs e)
{
if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text))
{
Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError);
return;
}
LabelArea.Text = DropDownListArea1.SelectedItem.Text;
if (DropDownListArea2.SelectedIndex > 0)
{
LabelArea.Text += "|" + DropDownListArea2.SelectedItem.Text;
}
DateTime searchdateB = DateTime.Now;
DateTime searchdateE = DateTime.Now;
if (tboxTimeB.Text.Length > 0)
{
searchdateB = Convert.ToDateTime(tboxTimeB.Text);
}
if (tboxTimeE.Text.Length > 0)
{
searchdateE = Convert.ToDateTime(tboxTimeE.Text);
}
int num = searchdateE.Subtract(searchdateB).Days;
if (num < 0)
{
Response.Write("<Script Language=JavaScript>alert('结束时间不能晚于开始时间！');</Script>");
return;
}
string bigZone = DropDownListArea1.SelectedIndex > 0 ? DropDownListArea1.SelectedValue.Split(',')[1] + "" : "0"; //大区
string battleZone = string.Empty; //战区
if (WssPublish == "0")
{
battleZone = DropDownListArea2.SelectedIndex > 0 ? DropDownListArea2.SelectedValue.Split(',')[1] + "" : "1";
}
else
{
battleZone = DropDownListArea2.SelectedIndex > 0 ? DropDownListArea2.SelectedValue.Split(',')[1] + "" : "5";
}
DataTable dt = new DataTable();
dt.Columns.Add("DateTime", System.Type.GetType("System.String"));
dt.Columns.Add("Type", System.Type.GetType("System.String"));
dt.Columns.Add("SoldNum", System.Type.GetType("System.String"));
dt.Columns.Add("ConsumeNum", System.Type.GetType("System.String"));
dt.Columns.Add("Phases1", System.Type.GetType("System.String"));
dt.Columns.Add("Phases2", System.Type.GetType("System.String"));
dt.Columns.Add("Phases3", System.Type.GetType("System.String"));
dt.Columns.Add("Phases4", System.Type.GetType("System.String"));
dt.Columns.Add("Phases5", System.Type.GetType("System.String"));
dt.Columns.Add("Phases6", System.Type.GetType("System.String"));
dt.Columns.Add("Phases7", System.Type.GetType("System.String"));
dt.Columns.Add("Phases8", System.Type.GetType("System.String"));
int n = 0;
for (int m = 0; m <= num; m++)
{
//日期
string strSTime = searchdateB.AddDays(m).ToString("yyyy_MM_dd");
#region 查询商品名称；销售数量；消费元宝数
string sql = string.Format("SELECT * FROM OPENQUERY ([LKSV_0_GSLOG_DB_{0}_{1}],'SELECT \"{2}\" AS DateTime,(CASE WHEN PARA_2=-980 THEN 0 WHEN PARA_2=-2880 THEN 1 ELSE \"\" END) TYPE,SoldNum,ConsumeNum FROM (SELECT PARA_2,COUNT(CID) SoldNum,-SUM(PARA_2) ConsumeNum FROM {3}_gold_log WHERE PARA_1=63 AND PARA_2<0 GROUP BY PARA_2) TEMP ORDER BY TYPE')", bigZone, battleZone, strSTime, strSTime);
ds = DBHelperDigGameDB.Query(sql);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow newRow;
newRow = dt.NewRow();
newRow["DateTime"] = ds.Tables[0].Rows[i]["DateTime"];
newRow["Type"] = ds.Tables[0].Rows[i]["Type"];
newRow["SoldNum"] = ds.Tables[0].Rows[i]["SoldNum"];
newRow["ConsumeNum"] = ds.Tables[0].Rows[i]["ConsumeNum"];
dt.Rows.Add(newRow);
}
//如果缺少某类基金，补全
if (dt.Rows.Count == n + 1)
{
if (dt.Rows[n]["TYPE"].ToString() == "0")
{
DataRow newRow;
newRow = dt.NewRow();
newRow["DateTime"] = strSTime;
newRow["Type"] = "1";
newRow["SoldNum"] = "0";
newRow["ConsumeNum"] = "0";
dt.Rows.Add(newRow);
}
else if (dt.Rows[n]["TYPE"].ToString() == "1")
{
DataRow newRow;
newRow = dt.NewRow();
newRow["DateTime"] = strSTime;
newRow["Type"] = "0";
newRow["SoldNum"] = "0";
newRow["ConsumeNum"] = "0";
dt.Rows.Add(newRow);
}
}
#endregion
//查询各个阶段领取次数
string sql1 = string.Format("SELECT * FROM OPENQUERY ([LKSV_0_GSLOG_DB_{0}_{1}],'SELECT * FROM(SELECT \"{2}\" AS DateTime,SUBSTRING_INDEX(SUBSTRING_INDEX(OP_BAK,\" \",3),\" \",-1) TYPE,SUBSTRING_INDEX(SUBSTRING_INDEX(OP_BAK,\" \",4),\" \",-1) SUBTYPE,COUNT(SUBSTRING_INDEX(SUBSTRING_INDEX(OP_BAK,\" \",4),\" \",-1)) SUBNUM FROM {3}_gold_log WHERE PARA_1=63 AND PARA_2>0 GROUP BY SUBSTRING_INDEX(SUBSTRING_INDEX(OP_BAK,\" \",3),\" \",-1),SUBSTRING_INDEX(SUBSTRING_INDEX(OP_BAK,\" \",4),\" \",-1)) TEMP ORDER BY TYPE')", bigZone, battleZone, strSTime, strSTime);
ds = DBHelperDigGameDB.Query(sql1);
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
#region 各阶段成长基金赋值
if (ds.Tables[0].Rows[j]["TYPE"].ToString() == "0")//基础
{
switch (ds.Tables[0].Rows[j]["SUBTYPE"].ToString())
{
case "1":
dt.Rows[n]["Phases1"] = ds.Tables[0].Rows[j]["SUBNUM"].ToString();
break;
case "2":
dt.Rows[n]["Phases2"] = ds.Tables[0].Rows[j]["SUBNUM"].ToString();
break;
case "3":
dt.Rows[n]["Phases3"] = ds.Tables[0].Rows[j]["SUBNUM"].ToString();
break;
case "4":
dt.Rows[n]["Phases4"] = ds.Tables[0].Rows[j]["SUBNUM"].ToString();
break;
case "5":
dt.Rows[n]["Phases5"] = ds.Tables[0].Rows[j]["SUBNUM"].ToString();
break;
case "6":
dt.Rows[n]["Phases6"] = ds.Tables[0].Rows[j]["SUBNUM"].ToString();
break;
case "7":
dt.Rows[n]["Phases7"] = ds.Tables[0].Rows[j]["SUBNUM"].ToString();
break;
case "8":
dt.Rows[n]["Phases8"] = ds.Tables[0].Rows[j]["SUBNUM"].ToString();
break;
}
}
else if (ds.Tables[0].Rows[j]["TYPE"].ToString() == "1")//高阶
{
switch (ds.Tables[0].Rows[j]["SUBTYPE"].ToString())
{
case "1":
dt.Rows[n + 1]["Phases1"] = ds.Tables[0].Rows[j]["SUBNUM"].ToString();
break;
case "2":
dt.Rows[n + 1]["Phases2"] = ds.Tables[0].Rows[j]["SUBNUM"].ToString();
break;
case "3":
dt.Rows[n + 1]["Phases3"] = ds.Tables[0].Rows[j]["SUBNUM"].ToString();
break;
case "4":
dt.Rows[n + 1]["Phases4"] = ds.Tables[0].Rows[j]["SUBNUM"].ToString();
break;
case "5":
dt.Rows[n + 1]["Phases5"] = ds.Tables[0].Rows[j]["SUBNUM"].ToString();
break;
case "6":
dt.Rows[n + 1]["Phases6"] = ds.Tables[0].Rows[j]["SUBNUM"].ToString();
break;
case "7":
dt.Rows[n + 1]["Phases7"] = ds.Tables[0].Rows[j]["SUBNUM"].ToString();
break;
case "8":
dt.Rows[n + 1]["Phases8"] = ds.Tables[0].Rows[j]["SUBNUM"].ToString();
break;
}
}
#endregion
}
n += 2;
}
ds.Tables.Add(dt);
ExportExcelHelper.ExportDataSet(ds);
}
protected void ExportExcel(object sender, EventArgs e)
{
if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text))
{
Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError);
return;
}
LabelArea.Text = DropDownListArea1.SelectedItem.Text;
if (DropDownListArea2.SelectedIndex > 0)
{
LabelArea.Text += "|" + DropDownListArea2.SelectedItem.Text;
}
if (DropDownListArea3.SelectedIndex > 0)
{
LabelArea.Text += "|" + DropDownListArea3.SelectedItem.Text;
}
DateTime searchdateB = DateTime.Now;
DateTime searchdateE = DateTime.Now;
if (tboxTimeB.Text.Length > 0)
{
searchdateB = Convert.ToDateTime(tboxTimeB.Text);
}
if (tboxTimeE.Text.Length > 0)
{
searchdateE = Convert.ToDateTime(tboxTimeE.Text);
}
LabelTime.Text = searchdateB.ToString(SmallDateTimeFormat) + " ";
string sqlwhere = " where 1=1 and OPID=50156";
if (!string.IsNullOrEmpty(tbRoleId.Text.Trim()))
{
sqlwhere += @" and CID=" + tbRoleId.Text.Trim();
}
string bigZone = DropDownListArea1.SelectedIndex > 0 ? DropDownListArea1.SelectedValue.Split(',')[1] + "" : "0";
string battleZone = string.Empty;
if (WssPublish == "0")
{
battleZone = DropDownListArea2.SelectedIndex > 0 ? DropDownListArea2.SelectedValue.Split(',')[1] + "" : "1";
}
else
{
battleZone = DropDownListArea2.SelectedIndex > 0 ? DropDownListArea2.SelectedValue.Split(',')[1] + "" : "5";
}
string sql = "";
sql = @"SELECT * FROM OPENQUERY ([LKSV_0_GSLOG_DB_" + bigZone + "_" + battleZone + "],'SELECT UID 用户编号,CID 角色编号,CID 角色名称,PARA_1 角色等级,PARA_2 角色职业,OP_BAK 备注,OP_TIME 时间 FROM " + searchdateB.ToString("yyyy_MM_dd") + "_other_log " + sqlwhere + "')";
ds = DBHelperDigGameDB.Query(sql);
DataTable dt = new DataTable();
dt.Columns.Add("用户编号", System.Type.GetType("System.String"));
dt.Columns.Add("角色编号", System.Type.GetType("System.String"));
dt.Columns.Add("角色名称", System.Type.GetType("System.String"));
dt.Columns.Add("角色等级", System.Type.GetType("System.String"));
dt.Columns.Add("角色职业", System.Type.GetType("System.String"));
dt.Columns.Add("备注", System.Type.GetType("System.String"));
dt.Columns.Add("时间", System.Type.GetType("System.String"));
if (ds != null)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
dt.Rows.Add(
ds.Tables[0].Rows[i][0],
ds.Tables[0].Rows[i][1],
GetRoleName(ds.Tables[0].Rows[i][2].ToString()),
ds.Tables[0].Rows[i][3],
GetPro(ds.Tables[0].Rows[i][4].ToString()),
GetOPBakFormate(ds.Tables[0].Rows[i][5].ToString()),
ds.Tables[0].Rows[i][6]
);
}
}
DataSet dsNew = new DataSet();
dsNew.Tables.Add(dt);
ExportExcelHelper.ExportDataSet(dsNew);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ExportExcel(object sender, EventArgs e)
{
if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text))
{
Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError);
return;
}
LabelArea.Text = DropDownListArea1.SelectedItem.Text;
if (DropDownListArea2.SelectedIndex > 0)
{
LabelArea.Text += "|" + DropDownListArea2.SelectedItem.Text;
}
DateTime searchdateB = DateTime.Now;
DateTime searchdateE = DateTime.Now;
if (tboxTimeB.Text.Length > 0)
{
searchdateB = Convert.ToDateTime(tboxTimeB.Text);
}
if (tboxTimeE.Text.Length > 0)
{
searchdateE = Convert.ToDateTime(tboxTimeE.Text);
}
int num = searchdateE.Subtract(searchdateB).Days;
if (num < 0)
{
Response.Write("<Script Language=JavaScript>alert('结束时间不能晚于开始时间！');</Script>");
return;
}
string sqlWhere = "";
if (!string.IsNullOrEmpty(tbPara1.Text.Trim()))
{
sqlWhere += string.Format(" AND PARA_1={0}", tbPara1.Text.Trim());
}
string bigZone = DropDownListArea1.SelectedIndex > 0 ? DropDownListArea1.SelectedValue.Split(',')[1] + "" : "0"; //大区
string battleZone = string.Empty; //战区
if (WssPublish == "0")
{
battleZone = DropDownListArea2.SelectedIndex > 0 ? DropDownListArea2.SelectedValue.Split(',')[1] + "" : "1";
}
else
{
battleZone = DropDownListArea2.SelectedIndex > 0 ? DropDownListArea2.SelectedValue.Split(',')[1] + "" : "5";
}
string sql = string.Format("SELECT * FROM OPENQUERY ([LKSV_0_GSLOG_DB_{0}_{1}],'", bigZone, battleZone);
for (int i = 0; i <= num; i++)
{
string strSTime = searchdateB.AddDays(i).ToString("yyyy_MM_dd");
if (i == 0)
{
sql += string.Format("SELECT \"{0}\" AS DateTime,PARA_1,SUM(PARA_2) SumNum,COUNT(PARA_1) CountNum FROM {1}_other_log WHERE (OPID=40004 OR OPID=40005) AND PARA_1<>0 AND PARA_1<>1 {2} GROUP BY PARA_1", strSTime, strSTime, sqlWhere);
}
else
{
sql += string.Format(" UNION ALL SELECT \"{0}\" AS DateTime,PARA_1,SUM(PARA_2) SumNum,COUNT(PARA_1) CountNum FROM {1}_other_log WHERE (OPID=40004 OR OPID=40005) AND PARA_1<>0 AND PARA_1<>1 {2} GROUP BY PARA_1", strSTime, strSTime, sqlWhere);
}
}
sql += "')";
ds = DBHelperDigGameDB.Query(sql);
ExportExcelHelper.ExportDataSet(ds);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ExportExcel(object sender, EventArgs e)
{
if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text))
{
Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError);
return;
}
string sqlWhere = "";
DateTime searchdateB = DateTime.Now.AddDays(-6);
DateTime searchdateE = DateTime.Now;
if (tboxTimeB.Text.Length > 0)
{
searchdateB = Convert.ToDateTime(tboxTimeB.Text);
}
if (tboxTimeE.Text.Length > 0)
{
searchdateE = Convert.ToDateTime(tboxTimeE.Text);
}
if (!string.IsNullOrEmpty(ddlChannel.SelectedValue.Trim()))
{
sqlWhere += string.Format(" AND F_ChannelID=N'{0}'", ddlChannel.SelectedValue);
}
string sql = string.Format(@"SELECT CASE WHEN F_Price>=0 AND F_Price<=9 THEN 1
WHEN F_Price>=10 AND F_Price<=29 THEN 2
WHEN F_Price>=30 AND F_Price<=49 THEN 3
WHEN F_Price>=50 AND F_Price<=99 THEN 4
WHEN F_Price>=100 AND F_Price<=199 THEN 5
WHEN F_Price>=200 AND F_Price<=299 THEN 6
WHEN F_Price>=300 AND F_Price<=499 THEN 7
WHEN F_Price>=500 AND F_Price<=999 THEN 8
WHEN F_Price>=1000 AND F_Price<=1999 THEN 9
WHEN F_Price>=2000 AND F_Price<=4999 THEN 10
WHEN F_Price>=5000 AND F_Price<=9999 THEN 11
WHEN F_Price>=10000 AND F_Price<=29999 THEN 12
WHEN F_Price>=30000 AND F_Price<=49999 THEN 13
WHEN F_Price>=50000 AND F_Price<=99999 THEN 14
WHEN F_Price>=100000 THEN 15
ELSE '' END AS serialNum,
ISNULL(COUNT(F_UserID),0) RechargeNumOfPeople,SUM(F_Price) RechargeAmount,SUM(F_Price)/COUNT(F_UserID) ARPU
FROM (
SELECT F_UserID,SUM(F_Price) AS F_Price
FROM T_Deposit_Verify_Result_Log
WHERE F_DepositResult=1 AND CONVERT(VARCHAR(100),F_InsertTime,23)>='{0}' AND CONVERT(VARCHAR(100),F_InsertTime,23)<='{1}' {2}
GROUP BY F_UserID
)TEMP
GROUP BY
CASE WHEN F_Price>=0 AND F_Price<=9 THEN 1
WHEN F_Price>=10 AND F_Price<=29 THEN 2
WHEN F_Price>=30 AND F_Price<=49 THEN 3
WHEN F_Price>=50 AND F_Price<=99 THEN 4
WHEN F_Price>=100 AND F_Price<=199 THEN 5
WHEN F_Price>=200 AND F_Price<=299 THEN 6
WHEN F_Price>=300 AND F_Price<=499 THEN 7
WHEN F_Price>=500 AND F_Price<=999 THEN 8
WHEN F_Price>=1000 AND F_Price<=1999 THEN 9
WHEN F_Price>=2000 AND F_Price<=4999 THEN 10
WHEN F_Price>=5000 AND F_Price<=9999 THEN 11
WHEN F_Price>=10000 AND F_Price<=29999 THEN 12
WHEN F_Price>=30000 AND F_Price<=49999 THEN 13
WHEN F_Price>=50000 AND F_Price<=99999 THEN 14
WHEN F_Price>=100000 THEN 15
ELSE '' END
ORDER BY serialNum", searchdateB.ToString("yyyy-MM-dd"), searchdateE.ToString("yyyy-MM-dd"), sqlWhere);
ds = DBHelperUserCoreDB.Query(sql);
DataSet newDataSet = new DataSet();
DataTable dt = new DataTable();
dt.Columns.Add("序号", System.Type.GetType("System.String"));
dt.Columns.Add("价格段", System.Type.GetType("System.String"));
dt.Columns.Add("充值人数", System.Type.GetType("System.String"));
dt.Columns.Add("充值金额", System.Type.GetType("System.String"));
dt.Columns.Add("ARPU", System.Type.GetType("System.String"));
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow newRow = dt.NewRow();
newRow["序号"] = ds.Tables[0].Rows[i]["serialNum"];
newRow["价格段"] = GetPrice(ds.Tables[0].Rows[i]["serialNum"].ToString());
newRow["充值人数"] = ds.Tables[0].Rows[i]["RechargeNumOfPeople"];
newRow["充值金额"] = ds.Tables[0].Rows[i]["RechargeAmount"];
newRow["ARPU"] = ds.Tables[0].Rows[i]["ARPU"];
dt.Rows.Add(newRow);
}
newDataSet.Tables.Add(dt);
ExportExcelHelper.ExportDataSet(newDataSet);
}
protected void ExportExcel(object sender, EventArgs e)
{
if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text))
{
Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError);
return;
}
LabelArea.Text = DropDownListArea1.SelectedItem.Text;
if (DropDownListArea2.SelectedIndex > 0)
{
LabelArea.Text += "|" + DropDownListArea2.SelectedItem.Text;
}
if (DropDownListArea3.SelectedIndex > 0)
{
LabelArea.Text += "|" + DropDownListArea3.SelectedItem.Text;
}
DateTime searchdateB = DateTime.Now;
DateTime searchdateE = DateTime.Now;
if (tboxTimeB.Text.Length > 0)
{
searchdateB = Convert.ToDateTime(tboxTimeB.Text);
}
if (tboxTimeE.Text.Length > 0)
{
searchdateE = Convert.ToDateTime(tboxTimeE.Text);
}
LabelTime.Text = searchdateB.ToString(SmallDateTimeFormat) + " ";
string sqlwhere = " where 1=1 ";
if (!string.IsNullOrEmpty(tbRoleId.Text.Trim()))
{
sqlwhere += @" and F_GlobalID=" + tbRoleId.Text.Trim();
}
if (!string.IsNullOrEmpty(txtTransactionID.Text.Trim()))
{
sqlwhere += @" and F_TransactionID='" + txtTransactionID.Text.Trim() + "'";
}
if (!string.IsNullOrEmpty(txtProductID.Text.Trim()))
{
sqlwhere += @" and F_ProductID=" + txtProductID.Text.Trim();
}
if (!string.IsNullOrEmpty(ddlDepositResult.SelectedValue + ""))
{
sqlwhere += @" and F_DepositResult=" + ddlDepositResult.SelectedValue;
}
if (!string.IsNullOrEmpty(Convert.ToDateTime(tboxTimeB.Text).ToString("yyyy-MM-dd")))
{
sqlwhere += @" and CONVERT(VARCHAR(100),F_InsertTime,23)>='" + Convert.ToDateTime(tboxTimeB.Text).ToString("yyyy-MM-dd") + "'";
}
if (!string.IsNullOrEmpty(Convert.ToDateTime(tboxTimeE.Text).ToString("yyyy-MM-dd")))
{
sqlwhere += " and CONVERT(VARCHAR(100),F_InsertTime,23)<='" + Convert.ToDateTime(tboxTimeE.Text).ToString("yyyy-MM-dd") + "'";
}
//sqlwhere += @" and F_Date='" + searchdateB.ToString(SmallDateTimeFormat) + "'";
//if (DropDownListArea1.SelectedIndex > 0)
//{
// sqlwhere += @" and F_BigZone=" + DropDownListArea1.SelectedValue.Split(',')[1] + "";
//}
//if (DropDownListArea2.SelectedIndex > 0)
//{
// sqlwhere += @" and F_ZoneID=" + DropDownListArea2.SelectedValue.Split(',')[1] + "";
//}
string bigZone = DropDownListArea1.SelectedIndex > 0 ? DropDownListArea1.SelectedValue.Split(',')[1] + "" : "0";
string battleZone = string.Empty;
if (WssPublish == "0")
{
battleZone = DropDownListArea2.SelectedIndex > 0 ? DropDownListArea2.SelectedValue.Split(',')[1] + "" : "1";
}
else
{
battleZone = DropDownListArea2.SelectedIndex > 0 ? DropDownListArea2.SelectedValue.Split(',')[1] + "" : "5";
}
string sql = "";
sql = @"SELECT F_GlobalID 角色编号,F_UserID 用户编号,F_TransactionID 订单号,F_ProductID 商品编号,F_Price 充值钱数,F_InsertTime 充值时间,F_StoreName 商店名,F_DepositResult 充值状态
FROM [LKSV_5_UserCoreDB_0_1].[UserCoreDB].[dbo].[T_Deposit_Verify_Result_Log] ";
ds = DBHelperDigGameDB.Query(sql);
ExportExcelHelper.ExportDataSet(ds);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ExportExcel(object sender, EventArgs e)
{
if (!Common.Validate.IsDateTime(tboxTimeB.Text))
{
Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError);
return;
}
string sqlWhere = "";
DateTime searchdateB = DateTime.Now;
DateTime searchdateE = DateTime.Now;
if (tboxTimeB.Text.Length > 0)
{
searchdateB = Convert.ToDateTime(tboxTimeB.Text);
}
if (!string.IsNullOrEmpty(ddlChannel.SelectedValue.Trim()))
{
sqlWhere += string.Format(" AND F_ChannelID=N'{0}'", ddlChannel.SelectedValue);
}
//用户信息
string sqlUserInfo = string.Format("SELECT LEFT(CONVERT(VARCHAR(100),F_CreatTime,120), 13) DateTime,COUNT(A.F_UserID) UserNum FROM T_User A LEFT JOIN T_UserExInfo B ON A.F_UserID=B.F_UserID WHERE CONVERT(VARCHAR(100),F_CreatTime,23)='{0}' {1} GROUP BY LEFT(CONVERT(VARCHAR(100),F_CreatTime,120), 13) ORDER BY LEFT(CONVERT(VARCHAR(100),F_CreatTime,120), 13)", searchdateB.ToString("yyyy-MM-dd"), sqlWhere);
//角色信息
string sqlRoleInfo = string.Format("SELECT LEFT(CONVERT(VARCHAR(100),F_CreateTime,120), 13) DateTime,COUNT(DISTINCT F_UserID) RoleUserNum,COUNT(DISTINCT F_DeviceID) RoleDeviceNum,COUNT(F_RoleID) RoleNum FROM(SELECT F_UserID,F_RoleID,F_DeviceID,F_CreateTime FROM T_RoleBaseData_0 WHERE CONVERT(VARCHAR(100),F_CreateTime,23)='{0}' {1} UNION ALL SELECT F_UserID,F_RoleID,F_DeviceID,F_CreateTime FROM T_RoleBaseData_1 WHERE CONVERT(VARCHAR(100),F_CreateTime,23)='{0}' {1} UNION ALL SELECT F_UserID,F_RoleID,F_DeviceID,F_CreateTime FROM T_RoleBaseData_2 WHERE CONVERT(VARCHAR(100),F_CreateTime,23)='{0}' {1} UNION ALL SELECT F_UserID,F_RoleID,F_DeviceID,F_CreateTime FROM T_RoleBaseData_3 WHERE CONVERT(VARCHAR(100),F_CreateTime,23)='{0}' {1} UNION ALL SELECT F_UserID,F_RoleID,F_DeviceID,F_CreateTime FROM T_RoleBaseData_4 WHERE CONVERT(VARCHAR(100),F_CreateTime,23)='{0}' {1})TEMP GROUP BY LEFT(CONVERT(VARCHAR(100),F_CreateTime,120), 13) ORDER BY LEFT(CONVERT(VARCHAR(100),F_CreateTime,120), 13)", searchdateB.ToString("yyyy-MM-dd"), sqlWhere);
DataTable dt = new DataTable();
dt.Columns.Add("时间", System.Type.GetType("System.String"));
dt.Columns.Add("注册（激活）数", System.Type.GetType("System.String"));
dt.Columns.Add("创建角色账号数", System.Type.GetType("System.String"));
dt.Columns.Add("创建角色设备数", System.Type.GetType("System.String"));
dt.Columns.Add("创建角色个数", System.Type.GetType("System.String"));
DataSet dsUserInfo = new DataSet();
DataSet dsRoleInfo = new DataSet();
dsUserInfo = DBHelperUserCoreDB.Query(sqlUserInfo);
dsRoleInfo = DBHelperGameCoreDB.Query(sqlRoleInfo);
for (int i = 0; i < 24; i++)
{
int flagUser = 0;
int flagRole = 0;
DataRow newRow = dt.NewRow();
newRow["时间"] = searchdateB.AddHours(i).ToString("yyyy-MM-dd HH");
//用户信息
for (int j = 0; j < dsUserInfo.Tables[0].Rows.Count; j++)
{
if (searchdateB.AddHours(i).ToString("yyyy-MM-dd HH") == dsUserInfo.Tables[0].Rows[j]["DateTime"].ToString())
{
flagUser = 1;
newRow["注册（激活）数"] = dsUserInfo.Tables[0].Rows[j]["UserNum"].ToString();
}
}
if (flagUser == 0)
{
newRow["注册（激活）数"] = "0";
}
//角色信息
for (int m = 0; m < dsRoleInfo.Tables[0].Rows.Count; m++)
{
if (searchdateB.AddHours(i).ToString("yyyy-MM-dd HH") == dsRoleInfo.Tables[0].Rows[m]["DateTime"].ToString())
{
flagRole = 1;
newRow["创建角色账号数"] = dsRoleInfo.Tables[0].Rows[m]["RoleUserNum"].ToString();
newRow["创建角色设备数"] = dsRoleInfo.Tables[0].Rows[m]["RoleDeviceNum"].ToString();
newRow["创建角色个数"] = dsRoleInfo.Tables[0].Rows[m]["RoleNum"].ToString();
}
}
if (flagRole == 0)
{
newRow["创建角色账号数"] = "0";
newRow["创建角色设备数"] = "0";
newRow["创建角色个数"] = "0";
}
dt.Rows.Add(newRow);
}
DataSet newDataSet = new DataSet();
newDataSet.Tables.Add(dt);
ExportExcelHelper.ExportDataSet(newDataSet);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ExportExcel(object sender, EventArgs e)
{
if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text))
{
Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError);
return;
}
string sqlWhere = "";
DateTime searchdateB = DateTime.Now.AddDays(-6);
DateTime searchdateE = DateTime.Now;
if (tboxTimeB.Text.Length > 0)
{
searchdateB = Convert.ToDateTime(tboxTimeB.Text);
}
if (tboxTimeE.Text.Length > 0)
{
searchdateE = Convert.ToDateTime(tboxTimeE.Text);
}
if (!string.IsNullOrEmpty(ddlChannel.SelectedValue.Trim()))
{
sqlWhere += string.Format(" AND F_ChannelID=N'{0}'", ddlChannel.SelectedValue);
}
string bigZone = DropDownListArea1.SelectedIndex > 0 ? DropDownListArea1.SelectedValue.Split(',')[1] + "" : "0";
string battleZone = string.Empty;
if (WssPublish == "0")
{
battleZone = DropDownListArea2.SelectedIndex > 0 ? DropDownListArea2.SelectedValue.Split(',')[1] + "" : "1";
}
else
{
battleZone = DropDownListArea2.SelectedIndex > 0 ? DropDownListArea2.SelectedValue.Split(',')[1] + "" : "5";
}
string sql = string.Empty;
if (ddlType.SelectedValue == "0")
{
sql = string.Format("SELECT ROW_NUMBER() OVER(ORDER BY PlayerLevelThen) 序号,PlayerLevelThen 类型,COUNT(DISTINCT UserID) 人数 FROM(SELECT UserID,PlayerLevelThen,TakeTime,OrderNum From(SELECT UserID,PlayerLevelThen,TakeTime,ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY TakeTime) AS OrderNum FROM OPENQUERY ([LKSV_3_gsdata_db_{0}_{1}],'SELECT UserID,PlayerLevelThen,TakeTime FROM takegoldrecord'))Temp WHERE OrderNum=1 AND CONVERT(VARCHAR(100),TakeTime,23)>='{2}' AND CONVERT(VARCHAR(100),TakeTime,23)<='{3}')A GROUP BY PlayerLevelThen Order BY PlayerLevelThen", bigZone, battleZone, searchdateB.ToString("yyyy-MM-dd"), searchdateE.ToString("yyyy-MM-dd"), sqlWhere);
}
else if (ddlType.SelectedValue == "1")
{
sql = string.Format(
@"
SELECT ROW_NUMBER() OVER(ORDER BY Type) OrderNum,Type,RechargeNumOfPeople FROM
(
SELECT CASE WHEN Interval>=0 AND Interval<=4 THEN 1
WHEN Interval>=5 AND Interval<=9 THEN 2
WHEN Interval>=10 AND Interval<=14 THEN 3
WHEN Interval>=15 AND Interval<=19 THEN 4
WHEN Interval>=20 AND Interval<=24 THEN 5
WHEN Interval>=25 AND Interval<=29 THEN 6
WHEN Interval>=30 AND Interval<=34 THEN 7
WHEN Interval>=35 AND Interval<=39 THEN 8
WHEN Interval>=40 AND Interval<=44 THEN 9
WHEN Interval>=45 AND Interval<=49 THEN 10
WHEN Interval>=50 AND Interval<=54 THEN 11
WHEN Interval>=55 AND Interval<=59 THEN 12
WHEN Interval>=60 AND Interval<=64 THEN 13
WHEN Interval>=65 AND Interval<=69 THEN 14
WHEN Interval>=70 AND Interval<=74 THEN 15
WHEN Interval>=75 AND Interval<=79 THEN 16
WHEN Interval>=80 AND Interval<=84 THEN 17
WHEN Interval>=85 AND Interval<=89 THEN 18
WHEN Interval>=90 AND Interval<=94 THEN 19
WHEN Interval>=95 AND Interval<=99 THEN 20
WHEN Interval>=100 AND Interval<=104 THEN 21
WHEN Interval>=105 AND Interval<=109 THEN 22
WHEN Interval>=110 AND Interval<=114 THEN 23
WHEN Interval>=115 AND Interval<=119 THEN 24
WHEN Interval>=120 AND Interval<=149 THEN 25
WHEN Interval>=150 AND Interval<=179 THEN 26
WHEN Interval>=180 AND Interval<=209 THEN 27
WHEN Interval>=210 AND Interval<=239 THEN 28
WHEN Interval>=240 THEN 29
ELSE '' END TYPE,
COUNT(DISTINCT GlobalID) RechargeNumOfPeople FROM
(
SELECT GlobalID,TakeTime,F_CreateTime,DATEDIFF(N,F_CreateTime,TakeTime) Interval FROM
(
SELECT GlobalID,TakeTime,OrderNum From
(
SELECT GlobalID,TakeTime,ROW_NUMBER() OVER(PARTITION BY GlobalID ORDER BY TakeTime) AS OrderNum FROM OPENQUERY ([LKSV_3_gsdata_db_{0}_{1}],
'SELECT GlobalID,TakeTime FROM takegoldrecord'))Temp
WHERE OrderNum=1 AND CONVERT(VARCHAR(100),TakeTime,23)>='{2}' AND CONVERT(VARCHAR(100),TakeTime,23)<='{3}'
)A
LEFT JOIN
(
SELECT [F_RoleID],[F_CreateTime] FROM LKSV_2_GameCoreDB_0_1.GameCoreDB.[dbo].[T_RoleBaseData_0] WHERE CONVERT(VARCHAR(100),[F_CreateTime],23)>='{2}' AND CONVERT(VARCHAR(100),[F_CreateTime],23)<='{3}'
UNION ALL
SELECT [F_RoleID],[F_CreateTime] FROM LKSV_2_GameCoreDB_0_1.GameCoreDB.[dbo].[T_RoleBaseData_1] WHERE CONVERT(VARCHAR(100),[F_CreateTime],23)>='{2}' AND CONVERT(VARCHAR(100),[F_CreateTime],23)<='{3}'
UNION ALL
SELECT [F_RoleID],[F_CreateTime] FROM LKSV_2_GameCoreDB_0_1.GameCoreDB.[dbo].[T_RoleBaseData_2] WHERE CONVERT(VARCHAR(100),[F_CreateTime],23)>='{2}' AND CONVERT(VARCHAR(100),[F_CreateTime],23)<='{3}'
UNION ALL
SELECT [F_RoleID],[F_CreateTime] FROM LKSV_2_GameCoreDB_0_1.GameCoreDB.[dbo].[T_RoleBaseData_3] WHERE CONVERT(VARCHAR(100),[F_CreateTime],23)>='{2}' AND CONVERT(VARCHAR(100),[F_CreateTime],23)<='{3}'
UNION ALL
SELECT [F_RoleID],[F_CreateTime] FROM LKSV_2_GameCoreDB_0_1.GameCoreDB.[dbo].[T_RoleBaseData_4] WHERE CONVERT(VARCHAR(100),[F_CreateTime],23)>='{2}' AND CONVERT(VARCHAR(100),[F_CreateTime],23)<='{3}'
)B ON A.GlobalID=B.[F_RoleID]
WHERE F_CreateTime IS NOT NULL)C
GROUP BY
CASE WHEN Interval>=0 AND Interval<=4 THEN 1
WHEN Interval>=5 AND Interval<=9 THEN 2
WHEN Interval>=10 AND Interval<=14 THEN 3
WHEN Interval>=15 AND Interval<=19 THEN 4
WHEN Interval>=20 AND Interval<=24 THEN 5
WHEN Interval>=25 AND Interval<=29 THEN 6
WHEN Interval>=30 AND Interval<=34 THEN 7
WHEN Interval>=35 AND Interval<=39 THEN 8
WHEN Interval>=40 AND Interval<=44 THEN 9
WHEN Interval>=45 AND Interval<=49 THEN 10
WHEN Interval>=50 AND Interval<=54 THEN 11
WHEN Interval>=55 AND Interval<=59 THEN 12
WHEN Interval>=60 AND Interval<=64 THEN 13
WHEN Interval>=65 AND Interval<=69 THEN 14
WHEN Interval>=70 AND Interval<=74 THEN 15
WHEN Interval>=75 AND Interval<=79 THEN 16
WHEN Interval>=80 AND Interval<=84 THEN 17
WHEN Interval>=85 AND Interval<=89 THEN 18
WHEN Interval>=90 AND Interval<=94 THEN 19
WHEN Interval>=95 AND Interval<=99 THEN 20
WHEN Interval>=100 AND Interval<=104 THEN 21
WHEN Interval>=105 AND Interval<=109 THEN 22
WHEN Interval>=110 AND Interval<=114 THEN 23
WHEN Interval>=115 AND Interval<=119 THEN 24
WHEN Interval>=120 AND Interval<=149 THEN 25
WHEN Interval>=150 AND Interval<=179 THEN 26
WHEN Interval>=180 AND Interval<=209 THEN 27
WHEN Interval>=210 AND Interval<=239 THEN 28
WHEN Interval>=240 THEN 29
ELSE '' END) D
ORDER BY TYPE
", bigZone, battleZone, searchdateB.ToString("yyyy-MM-dd"), searchdateE.ToString("yyyy-MM-dd"), sqlWhere);
}
else if (ddlType.SelectedValue == "2")
{
sql = string.Format("SELECT ROW_NUMBER() OVER(ORDER BY Type) OrderNum,Type,RechargeNumOfPeople FROM(SELECT CASE WHEN CostMoney>=0 AND CostMoney<=9 THEN 1 WHEN CostMoney>=10 AND CostMoney<=29 THEN 2 WHEN CostMoney>=30 AND CostMoney<=49 THEN 3 WHEN CostMoney>=50 AND CostMoney<=99 THEN 4 WHEN CostMoney>=100 AND CostMoney<=199 THEN 5 WHEN CostMoney>=200 AND CostMoney<=299 THEN 6 WHEN CostMoney>=300 AND CostMoney<=499 THEN 7 WHEN CostMoney>=500 AND CostMoney<=799 THEN 8 WHEN CostMoney>=800 AND CostMoney<=999 THEN 9 WHEN CostMoney>=1000 AND CostMoney<=1999 THEN 10 WHEN CostMoney>=2000 AND CostMoney<=4999 THEN 11 WHEN CostMoney>=5000 AND CostMoney<=9999 THEN 12 WHEN CostMoney>=10000 THEN 13 ELSE '' END AS Type,COUNT(DISTINCT UserID) RechargeNumOfPeople FROM (SELECT UserID,CostMoney,TakeTime,ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY TakeTime) AS OrderNum FROM OPENQUERY ([LKSV_3_gsdata_db_{0}_{1}],'SELECT UserID,CostMoney,TakeTime FROM takegoldrecord'))Temp WHERE OrderNum=1 AND CONVERT(VARCHAR(100),TakeTime,23)>='{2}' AND CONVERT(VARCHAR(100),TakeTime,23)<='{3}' GROUP BY CASE WHEN CostMoney>=0 AND CostMoney<=9 THEN 1 WHEN CostMoney>=10 AND CostMoney<=29 THEN 2 WHEN CostMoney>=30 AND CostMoney<=49 THEN 3 WHEN CostMoney>=50 AND CostMoney<=99 THEN 4 WHEN CostMoney>=100 AND CostMoney<=199 THEN 5 WHEN CostMoney>=200 AND CostMoney<=299 THEN 6 WHEN CostMoney>=300 AND CostMoney<=499 THEN 7 WHEN CostMoney>=500 AND CostMoney<=799 THEN 8 WHEN CostMoney>=800 AND CostMoney<=999 THEN 9 WHEN CostMoney>=1000 AND CostMoney<=1999 THEN 10 WHEN CostMoney>=2000 AND CostMoney<=4999 THEN 11 WHEN CostMoney>=5000 AND CostMoney<=9999 THEN 12 WHEN CostMoney>=10000 THEN 13 ELSE '' END )A ORDER BY TYPE", bigZone, battleZone, searchdateB.ToString("yyyy-MM-dd"), searchdateE.ToString("yyyy-MM-dd"), sqlWhere);
}
else
{
//显示等级
sql = string.Format("SELECT ROW_NUMBER() OVER(ORDER BY PlayerLevelThen) 序号,PlayerLevelThen 类型,COUNT(DISTINCT UserID) 人数 FROM(SELECT UserID,PlayerLevelThen,TakeTime,OrderNum From(SELECT UserID,PlayerLevelThen,TakeTime,ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY TakeTime) AS OrderNum FROM OPENQUERY ([LKSV_3_gsdata_db_{0}_{1}],'SELECT UserID,PlayerLevelThen,TakeTime FROM takegoldrecord'))Temp WHERE OrderNum=1 AND CONVERT(VARCHAR(100),TakeTime,23)>='{2}' AND CONVERT(VARCHAR(100),TakeTime,23)<='{3}')A GROUP BY PlayerLevelThen Order BY PlayerLevelThen", bigZone, battleZone, searchdateB.ToString("yyyy-MM-dd"), searchdateE.ToString("yyyy-MM-dd"), sqlWhere);
}
ds = DBHelperDigGameDB.Query(sql);
DataView myView;
if (ddlType.SelectedValue == "1")
{
DataTable dt = new DataTable();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
dt.Columns.Add("序号", System.Type.GetType("System.String"));
dt.Columns.Add("类型", System.Type.GetType("System.String"));
dt.Columns.Add("人数", System.Type.GetType("System.String"));
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow newRow = dt.NewRow();
newRow["序号"] = ds.Tables[0].Rows[i]["OrderNum"];
newRow["类型"] = GetInterval(ds.Tables[0].Rows[i]["Type"] + "");
newRow["人数"] = ds.Tables[0].Rows[i]["RechargeNumOfPeople"];
dt.Rows.Add(newRow);
}
}
DataSet dsNew = new DataSet();
dsNew.Tables.Add(dt);
ExportExcelHelper.ExportDataSet(dsNew);
}
else if (ddlType.SelectedValue == "2")
{
DataTable dt = new DataTable();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
dt.Columns.Add("序号", System.Type.GetType("System.String"));
dt.Columns.Add("类型", System.Type.GetType("System.String"));
dt.Columns.Add("人数", System.Type.GetType("System.String"));
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow newRow = dt.NewRow();
newRow["序号"] = ds.Tables[0].Rows[i]["OrderNum"];
newRow["类型"] = GetPrice(ds.Tables[0].Rows[i]["Type"] + "");
newRow["人数"] = ds.Tables[0].Rows[i]["RechargeNumOfPeople"];
dt.Rows.Add(newRow);
}
}
DataSet dsNew = new DataSet();
dsNew.Tables.Add(dt);
ExportExcelHelper.ExportDataSet(dsNew);
}
else
{
ExportExcelHelper.ExportDataSet(ds);
}
}