protected void ControlChartSelect_SelectChanged(object sender, EventArgs e) { if (ControlChartSelect1.State == 0) { GridView1.Visible = true; ControlChart1.Visible = false; } else { GridView1.Visible = false; ControlChart1.Visible = true; ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State, false, 1, 1); } }
protected void ControlChartSelect_SelectChanged(object sender, EventArgs e) { if (ControlChartSelect1.State == 0) { GridView1.Visible = true; ControlChart1.Visible = false; } else { GridView1.Visible = false; ControlChart1.Visible = true; ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + "所有大区" + " " + DateTime.Now.ToString("yyyy-MM-dd"), ControlChartSelect1.State, false, 0, 0); } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { 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) F_InsertTime,SUM(F_Price) SumAmount,COUNT(DISTINCT F_UserID) PeopleCount,COUNT(F_UserID) Count FROM LKSV_5_UserCoreDB_0_1.[UserCoreDB].[dbo].[T_Deposit_Verify_Result_Log] WHERE F_DepositResult=1 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) + "'"; //if (DropDownListArea1.SelectedIndex > 0) //{ // sql += @" and F_BigZone=" + DropDownListArea1.SelectedValue.Split(',')[1] + ""; //} //if (DropDownListArea2.SelectedIndex > 0) //{ // sql += @" and F_ZoneID=" + DropDownListArea2.SelectedValue.Split(',')[1] + ""; //} sql += " order by F_InsertTime desc"; try { ds = DBHelperDigGameDB.Query(sql); DataTable dt = ds.Tables[0]; DataView myView = ds.Tables[0].DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State, true, 0, 0); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text)) { Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError); return; } //设置日期按钮状态 string dates = Convert.ToDateTime(tboxTimeB.Text).ToString("yyyy-MM-01"); DateTime dtb = Convert.ToDateTime(dates); DateTime dte = dtb.AddMonths(1).AddDays(-1); for (int i = 0; i <= 32; i++) { Control ctl = DateSelect.FindControl("btndateselect" + i); if (ctl != null && ctl.GetType() == typeof(Button)) { Button btn = (Button)ctl; if (dtb.AddDays(i - 1) <= DateTime.Now && (i <= dte.Day || i == 32)) { btn.Visible = true; if (btn.Text == Convert.ToDateTime(tboxTimeB.Text).ToString("dd")) { btn.CssClass = "buttonblueo"; btn.Enabled = false; } else { btn.CssClass = "buttonblue"; btn.Enabled = true; } } else { btn.Visible = false; } } } 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 "; 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 sql = ""; sql = @" with RVcte(F_TimePara, F_Pro, F_Num) as ( SELECT F_TimePara,F_Pro,Sum(F_Num) as F_Num FROM T_RoleLoseTimeALL " + sqlwhere + @" group by F_TimePara,F_Pro ) select distinct(a.F_TimePara) as '" + App_GlobalResources.Language.LblTime + @"',CAST(replace(replace(replace(a.F_TimePara,'HOUR',''),'DAY',''),'MONTH','') AS INT) AS TimeInt, {0} FROM RVcte a order by TimeInt ASC "; try { string tmpl = "(select isnull(Sum(F_Num),0) from RVcte where F_TimePara=a.F_TimePara and F_Pro={0}) as '{1}'"; List <string> fields = new List <string>(); foreach (KeyValuePair <string, string> item in MapVacationType()) { fields.Add(string.Format(tmpl, item.Key, item.Value)); } sql = string.Format(sql, string.Join(",", fields)); ds = DBHelperDigGameDB.Query(sql); DataTable dtNew = ds.Tables[0].Clone(); string[] times = { "HOUR", "DAY", "MONTH" }; foreach (string time in times) { DataRow[] drs = ds.Tables[0].Select("" + App_GlobalResources.Language.LblTime + " like '%" + time + "'", "TimeInt asc"); if (drs.Length > 0) { foreach (DataRow dr in drs) { DataRow drnew = dtNew.NewRow(); for (int i = 0; i < dtNew.Columns.Count; i++) { drnew[i] = dr[i].ToString().Replace("HOUR", App_GlobalResources.Language.LblUnit_Hour).Replace("DAY", App_GlobalResources.Language.LblUnit_Day).Replace("MONTH", App_GlobalResources.Language.LblUnit_Month); } string ti = drnew["TimeInt"].ToString(); string tp = drnew[App_GlobalResources.Language.LblTime].ToString().Replace(ti, ""); drnew[App_GlobalResources.Language.LblTime] = string.Format("{0}-{1} {2}", ti, Convert.ToInt32(ti) + 1, tp); dtNew.Rows.Add(drnew); } } } //结果集中忽略字段处理 GridView1.Columns.Clear(); columnValue = new List <long>(); columnValue.Add(0); GridView1.Columns.Add(new BoundField() { DataField = App_GlobalResources.Language.LblTime, HeaderText = App_GlobalResources.Language.LblTime }); foreach (KeyValuePair <string, string> item in MapVacationType()) { columnValue.Add(0); GridView1.Columns.Add(new BoundField() { DataField = item.Value, HeaderText = item.Value }); } GridView1.Columns.Add(new BoundField() { HeaderText = App_GlobalResources.Language.LblSum }); DataView myView = dtNew.DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; lblinfo.Text = sql; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { 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) + " " + App_GlobalResources.Language.LblTo + " " + searchdateE.ToString(SmallDateTimeFormat); string sql = ""; sql = @" SELECT F_BigZone, F_ZoneID,F_Date, SUM(F_TradeCount) AS F_TradeCount, SUM(F_RoleCount) AS F_RoleCount, SUM(F_MoneyCount) AS F_MoneyCount FROM T_ShopSale with(nolock) where F_ShopType=0 AND F_Date>='" + searchdateB.ToString(SmallDateTimeFormat) + "' and F_Date<='" + searchdateE.ToString(SmallDateTimeFormat) + "' "; if (DropDownListArea1.SelectedIndex >= 0) { sql += @" and F_BigZone=" + DropDownListArea1.SelectedValue.Split(',')[1] + ""; } //if (DropDownListArea2.SelectedIndex > 0) //{ // sql += @" and F_ZoneID=" + DropDownListArea2.SelectedValue.Split(',')[1] + ""; //} sql += " GROUP BY F_Date,F_BigZone,F_ZoneID order by F_Date"; try { ds = DBHelperDigGameDB.Query(sql); DataView myView = ds.Tables[0].DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State, true, 0, 0); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { 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 "; 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 sql = ""; sql = @"SELECT ROW_NUMBER() OVER(order by F_CorpsHonor desc) AS rownum, F_Year, F_Month, F_Day, F_Date, F_BigZone, F_ZoneID, F_CorpsHonor, F_CorpsID, F_CorpsName, F_CorpsLevel, F_OrgCreaterName, F_CreaterName, F_CreaterPro, F_CurMoney, F_CurExp, F_CreateTime, F_LastUpDataTime FROM T_CorpsRankHonor " + sqlwhere + ""; try { ds = DBHelperDigGameDB.Query(sql); DataView myView = ds.Tables[0].DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); GridView2.DataSource = myView; GridView2.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView2, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State, false, 0, 0); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; //lblerro.Text = sql; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { 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(10),F_Date,120) as 日期,F_zoneId, F_GoldSurplus as 结余, F_GoldOut as 产出, F_GoldIn as 消耗 FROM T_MoneySilver where F_Date>='" + searchdateB.ToString(SmallDateTimeFormat) + "' and F_Date<='" + searchdateE.ToString(SmallDateTimeFormat) + "' "; if (DropDownListArea1.SelectedIndex > 0) { sql += @" and F_BigZone=" + DropDownListArea1.SelectedValue.Split(',')[1] + ""; } if (DropDownListArea2.SelectedIndex > 0) { sql += @" and F_ZoneID=" + DropDownListArea2.SelectedValue.Split(',')[1] + ""; } sql += " order by F_Date desc"; // sql += " union all select '总计' as 日期,sum(F_GoldSurplus) as 结余,sum(F_GoldOut) as 产出, sum(F_GoldIn) as 消耗"; try { ds = DBHelperDigGameDB.Query(sql); DataView myView = ds.Tables[0].DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State, true, 0, 0); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; } }
void QueryZoneRole(DateTime timeWhere) { string cmd = @"select convert(nvarchar(2),F_Createtime,108) as F_CreateTime,F_GZONENAME,MAX(F_PlayerNumOnline) as F_PlayerNumOnline from ( select t.F_CreateTime,t.F_GZONENAME,SUM(t.F_PlayerNumOnline) as F_PlayerNumOnline from ( SELECT F_CreateTime,F_GGSID, F_GZONENAME ,F_PlayerNumOnline from T_RoleOnLineFlow where F_Date=CONVERT(nvarchar(10),'{time}',120) group by F_CreateTime,F_GGSID, F_GZONEID,F_GZONENAME,F_PlayerNumOnline ) as t group by t.F_CreateTime,t.F_GZONENAME ) res group by convert(nvarchar(2),F_Createtime,108),F_GZONENAME"; cmd = cmd.Replace("{time}", timeWhere.ToString(SmallDateTimeFormat)); DataSet ds = DBHelperDigGameDB.Query(cmd); DataTable table = ds.Tables[0]; if (table.Rows.Count == 0) { GridView1.DataSource = (new DataTable()); GridView1.DataBind(); lblerro.Visible = true; return; } List <string> yd = new List <string>(); List <string> xd = new List <string>(); int x = 0, y = 0; GridView1.Columns.Clear(); GridView1.Columns.Add(new BoundField() { HeaderText = App_GlobalResources.Language.LblTime, DataField = "0" }); string columnName = "F_GZONENAME", rowName = "F_CreateTime"; int columnIndex = table.DefaultView.ToTable(true, new string[] { columnName }).Rows.Count; int rowIndex = table.DefaultView.ToTable(true, new string[] { rowName }).Rows.Count; string[,] source = new string[rowIndex, columnIndex]; foreach (DataRow item in table.Rows) { string num = ((int)item["F_PlayerNumOnline"]).ToString(); string xi = (string)item[columnName]; string yi = (string)item[rowName]; if (!xd.Contains(xi)) { x++; BoundField column = new BoundField(); column.HeaderText = xi; column.DataField = x.ToString(); GridView1.Columns.Add(column); xd.Add(xi); } if (!yd.Contains(yi)) { yd.Add(yi); y++; } source[Array.IndexOf(yd.ToArray(), yi), Array.IndexOf(xd.ToArray(), xi)] = num; } table.Clear(); table.Dispose(); GridView1.Columns.Add(new BoundField() { HeaderText = App_GlobalResources.Language.LblSumOfMax, DataField = (xd.Count + 1).ToString() }); DataTable res = new DataTable(); for (int i = 0; i <= xd.Count + 1; i++) {//额外增加两列: 首列>名称 尾列>总计 res.Columns.Add(i.ToString()); } for (int i = 0; i < yd.Count; i++) { DataRow row = res.NewRow(); object[] l = new object[xd.Count + 2]; l[0] = yd[i] + App_GlobalResources.Language.LblHourUnit; int sum = 0; for (int j = 0; j < xd.Count; j++) { int t = 0; int.TryParse(source[i, j], out t); sum = sum < t ? t : sum; l[j + 1] = t; } l[xd.Count + 1] = sum; row.ItemArray = l; res.Rows.Add(row); } GridView1.DataSource = res; GridView1.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State); } lblerro.Visible = false; }
/// <summary> /// 绑定数据 /// </summary> public void bind() { 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) + " " + App_GlobalResources.Language.LblTo + " " + searchdateE.ToString(SmallDateTimeFormat); string queryDateSql = @"select f_Date from T_ShopSale where F_ShopType=0 AND F_Date>='" + searchdateB.ToString(SmallDateTimeFormat) + "' and F_Date<='" + searchdateE.ToString(SmallDateTimeFormat) + "'"; if (DropDownListArea1.SelectedIndex > 0) { queryDateSql += @" and F_BigZone=" + DropDownListArea1.SelectedValue.Split(',')[1] + ""; } queryDateSql += " GROUP BY F_Date order by F_Date"; queryDateDS = DBHelperDigGameDB.Query(queryDateSql); DataView queryDateView = queryDateDS.Tables[0].DefaultView; StringBuilder inSqlBuilder = new StringBuilder(); if (queryDateView != null && queryDateView.Count > 0) { for (int i = 0; i < queryDateView.Count; i++) { if (i == queryDateView.Count - 1) { inSqlBuilder.AppendFormat("[{0}]", Convert.ToDateTime(queryDateView[i]["f_Date"]).ToString("yyyy-MM-dd")); } else { inSqlBuilder.AppendFormat("[{0}],", Convert.ToDateTime(queryDateView[i]["f_Date"]).ToString("yyyy-MM-dd")); } } string inSql = inSqlBuilder.ToString(); string sql = @"SELECT F_ItemExcelID,F_ItemChildNum," + inSql + "FROM (SELECT F_ItemExcelID,F_ItemChildNum,F_Date,SUM(F_ItemSaleNum / F_ItemChildNum) AS F_GoodsNum FROM T_ShopSaleItem with(nolock) where F_ShopType=0 AND F_Date>='" + searchdateB.ToString(SmallDateTimeFormat) + "' and F_Date<='" + searchdateE.ToString(SmallDateTimeFormat) + "' "; if (DropDownListArea1.SelectedIndex > 0) { sql += @" and F_BigZone=" + DropDownListArea1.SelectedValue.Split(',')[1] + ""; } sql += " GROUP BY F_Date,F_ItemExcelID,F_ItemChildNum) as TempData pivot (sum(F_GoodsNum) for F_Date in (" + inSql + ")) as ourpivot order by F_ItemExcelID"; try { ds = DBHelperDigGameDB.Query(sql); DataView myView = ds.Tables[0].DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State, true, 0, 0); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; } } else { lblerro.Visible = true; GridView1.DataSource = null; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { 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 "; 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 sql = ""; sql = @"SELECT F_Level, sum(F_Num) as F_Num, sum(F_Num0) as F_Num0, sum(F_Num1) as F_Num1, sum(F_Money) as F_Money, sum(F_Money0) as F_Money0, sum(F_Money1) as F_Money1, sum(F_ItemNum) as F_ItemNum, sum(F_ItemNum0) as F_ItemNum0, sum(F_ItemNum1) as F_ItemNum1 FROM T_PublicSaleHuanhua " + sqlwhere + @" group by F_Level order by F_Level asc"; try { ds = DBHelperDigGameDB.Query(sql); DataView myView = ds.Tables[0].DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; //lblerro.Text = sql; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { 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; DateTime searchdateE = DateTime.Now; if (tboxTimeB.Text.Length > 0) { searchdateB = Convert.ToDateTime(tboxTimeB.Text); } if (tboxTimeE.Text.Length > 0) { searchdateE = Convert.ToDateTime(tboxTimeE.Text).AddDays(1); } string sql = @" select convert(varchar(10),F_EditTime,120) as 时间, (select top 1 F_UserName+F_RealName from T_Users with(nolock) where F_UserID=F_EditMan) as 客服, sum(case when (F_State is null) then 1 else 0 end) as 回复, sum(case when (F_State = 100100101) then 1 else 0 end) as 接收, sum(case when (F_State = 100100100) then 1 else 0 end) as 结束, count(distinct(F_ID)) as 工单 FROM T_TasksLog with(nolock) where F_EditMan is not null and F_ID in (select F_ID From T_Tasks with(nolock) where F_Type=20000216 ) and F_EditTime>='" + searchdateB.ToString(SmallDateTimeFormat) + "' and F_EditTime<='" + searchdateE.ToString(SmallDateTimeFormat) + @"' group by convert(varchar(10),F_EditTime,120), F_EditMan order by 时间"; try { ds = DBHelperGSSDB.Query(sql); DataView myView = ds.Tables[0].DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - "), ControlChartSelect1.State, true, 0, 0); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text)) { Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError); return; } //设置日期按钮状态 string dates = Convert.ToDateTime(tboxTimeB.Text).ToString("yyyy-01-01"); DateTime dtb = Convert.ToDateTime(dates); for (int i = 1; i <= 12; i++) { Control ctl = DateSelect.FindControl("btndateselect" + i); if (ctl != null && ctl.GetType() == typeof(Button)) { Button btn = (Button)ctl; if (dtb.AddMonths(i - 1) <= DateTime.Now) { btn.Text = dtb.AddMonths(i - 1).ToString("yyyy-MM"); btn.Visible = true; DateTime dttb = Convert.ToDateTime(btn.Text); DateTime dtte = dttb.AddMonths(1) > DateTime.Now ? DateTime.Now : dttb.AddMonths(1).AddDays(-1); if (dttb.ToString("yyyy-MM-dd") == tboxTimeB.Text && dtte.ToString("yyyy-MM-dd") == tboxTimeE.Text) { btn.CssClass = "buttonblo"; btn.Enabled = false; } else { btn.CssClass = "buttonbl"; btn.Enabled = true; } } else { btn.Visible = false; } } } 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 sqlwhere = " where 1=1 "; sqlwhere += @" and F_Date>='" + searchdateB.ToString(SmallDateTimeFormat) + "' and F_Date<='" + searchdateE.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 sql = ""; sql = @"SELECT Convert(varchar(10),F_Date,120) as F_Date,F_ChatType, Sum(F_Num) as F_Num FROM T_ChatTypeNum " + sqlwhere + @" group by F_Date,F_ChatType"; try { ds = DBHelperDigGameDB.Query(sql); List <string> lines = new List <string>(); DataTable dtt = new DataTable(); dtt.Columns.Add("F_Date", typeof(string)); foreach (DataRow dr in ds.Tables[0].Rows) { string line = dr["F_ChatType"].ToString().Trim(); string time = dr["F_Date"].ToString(); string lineName = GetChatTypeName(line); if (!lines.Contains(lineName) && !string.IsNullOrEmpty(lineName)) { lines.Add(lineName); } if (dtt.Select("F_Date='" + time + "'").Length == 0) { DataRow drt = dtt.NewRow(); drt["F_Date"] = time; dtt.Rows.Add(drt); } } while (GridView1.Columns.Count > 1) { GridView1.Columns.RemoveAt(1); } foreach (string linename in lines) { BoundField col = new BoundField(); col.HeaderText = linename; GridView1.Columns.Add(col); } BoundField coli = new BoundField(); coli.HeaderText = App_GlobalResources.Language.LblSum; GridView1.Columns.Add(coli); DataView myView = dtt.DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State, false, 0, 1); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; //lblerro.Text = sql; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { 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).AddDays(1); } LabelTime.Text = searchdateB.ToString(SmallDateTimeFormat) + " " + App_GlobalResources.Language.LblTo + " " + searchdateE.ToString(SmallDateTimeFormat); string sql = ""; sql = @"SELECT convert(VARCHAR(10), F_Time,120) as 日期, sum(case F_Type when 0 then 1 else 0 end) as 安装, sum(case F_Type when 1 then 1 else 0 end) as 卸载, sum(case F_Type when 4 then 1 else 0 end) as 下载 FROM T_CountDetail where F_Time>='" + searchdateB.ToString(SmallDateTimeFormat) + "' and F_Time<='" + searchdateE.ToString(SmallDateTimeFormat) + "' "; //if (DropDownListArea1.SelectedIndex > 0) //{ // sql += @" and F_BigZone=" + DropDownListArea1.SelectedValue.Split(',')[1] + ""; //} //if (DropDownListArea2.SelectedIndex > 0) //{ // sql += @" and F_ZoneID=" + DropDownListArea2.SelectedValue.Split(',')[1] + ""; //} sql += "group by convert(VARCHAR(10), F_Time,120) order by convert(VARCHAR(10), F_Time,120) desc"; try { ds = DBHelperGSSDB.Query(sql); DataView myView = ds.Tables[0].DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State, true, 0, 0); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text)) { Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError); return; } //设置日期按钮状态 string dates = Convert.ToDateTime(tboxTimeB.Text).ToString("yyyy-MM-01"); DateTime dtb = Convert.ToDateTime(dates); DateTime dte = dtb.AddMonths(1).AddDays(-1); for (int i = 0; i <= 32; i++) { Control ctl = DateSelect.FindControl("btndateselect" + i); if (ctl != null && ctl.GetType() == typeof(Button)) { Button btn = (Button)ctl; if (dtb.AddDays(i - 1) <= DateTime.Now && (i <= dte.Day || i == 32)) { btn.Visible = true; if (btn.Text == Convert.ToDateTime(tboxTimeB.Text).ToString("dd")) { btn.CssClass = "buttonblueo"; btn.Enabled = false; } else { btn.CssClass = "buttonblue"; btn.Enabled = true; } } else { btn.Visible = false; } } } 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 "; 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 itemid = "1"; if (Request["itemid"] != null) { itemid = Request["itemid"].ToString(); } string sql = ""; sql = @"SELECT * FROM T_QuestKey WITH(NOLOCK) where F_ItemID=" + itemid + " order by F_ItemID "; try { ds = DBHelperGSSDB.Query(sql); DataView myView = ds.Tables[0].DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblTitle.Text = myView.Table.Rows[0]["F_ItemTitle"].ToString(); lblSum.Text = GetAnswerSum(myView); lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + lblTitle.Text + " " + LabelTime.Text, ControlChartSelect1.State, false, 0, 1); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; //lblerro.Text = sql; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { 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 = @" WITH T_EquipStarVacation_CTE ( F_Date,F_StarLevel, F_ItemCount,F_Vocation) AS ( SELECT F_Date,F_StarLevel, F_ItemCount,isnull(F_Vocation,'') FROM T_EquipLevelVacation where F_Date >='" + searchdateB.ToString(SmallDateTimeFormat) + @"' and F_Date<='" + searchdateE.ToString(SmallDateTimeFormat) + @"' and F_SuitName='精炼' and F_EquipType='宝石' "; if (DropDownListArea1.SelectedIndex > 0) { sql += @" and F_BigZone=" + DropDownListArea1.SelectedValue.Split(',')[1] + ""; } if (DropDownListArea2.SelectedIndex > 0) { sql += @" and F_ZoneID=" + DropDownListArea2.SelectedValue.Split(',')[1] + ""; } sql += @") select convert(VARCHAR(10), F_date,111) as date ,(select isnull(sum(F_ItemCount),0) from T_EquipStarVacation_CTE where F_Date=a.F_Date and F_StarLevel='1档' and len(F_Vocation)=0) as num1 ,(select isnull(sum(F_ItemCount),0) from T_EquipStarVacation_CTE where F_Date=a.F_Date and F_StarLevel='2档' and len(F_Vocation)=0) as num2 ,(select isnull(sum(F_ItemCount),0) from T_EquipStarVacation_CTE where F_Date=a.F_Date and F_StarLevel='3档' and len(F_Vocation)=0) as num3 ,(select isnull(sum(F_ItemCount),0) from T_EquipStarVacation_CTE where F_Date=a.F_Date and F_StarLevel='4档' and len(F_Vocation)=0) as num4 ,(select isnull(sum(F_ItemCount),0) from T_EquipStarVacation_CTE where F_Date=a.F_Date and F_StarLevel='5档' and len(F_Vocation)=0) as num5 ,(select isnull(sum(F_ItemCount),0) from T_EquipStarVacation_CTE where F_Date=a.F_Date and F_StarLevel='6档' and len(F_Vocation)=0) as num6 ,(select isnull(sum(F_ItemCount),0) from T_EquipStarVacation_CTE where F_Date=a.F_Date and F_StarLevel='7档' and len(F_Vocation)=0) as num7 ,(select isnull(sum(F_ItemCount),0) from T_EquipStarVacation_CTE where F_Date=a.F_Date and F_StarLevel='8档' and len(F_Vocation)=0) as num8 ,(select isnull(sum(F_ItemCount),0) from T_EquipStarVacation_CTE where F_Date=a.F_Date and F_StarLevel='1档' and len(F_Vocation)=2) as numB1 ,(select isnull(sum(F_ItemCount),0) from T_EquipStarVacation_CTE where F_Date=a.F_Date and F_StarLevel='2档' and len(F_Vocation)=2) as numB2 ,(select isnull(sum(F_ItemCount),0) from T_EquipStarVacation_CTE where F_Date=a.F_Date and F_StarLevel='3档' and len(F_Vocation)=2) as numB3 ,(select isnull(sum(F_ItemCount),0) from T_EquipStarVacation_CTE where F_Date=a.F_Date and F_StarLevel='4档' and len(F_Vocation)=2) as numB4 ,(select isnull(sum(F_ItemCount),0) from T_EquipStarVacation_CTE where F_Date=a.F_Date and F_StarLevel='5档' and len(F_Vocation)=2) as numB5 ,(select isnull(sum(F_ItemCount),0) from T_EquipStarVacation_CTE where F_Date=a.F_Date and F_StarLevel='6档' and len(F_Vocation)=2) as numB6 ,(select isnull(sum(F_ItemCount),0) from T_EquipStarVacation_CTE where F_Date=a.F_Date and F_StarLevel='7档' and len(F_Vocation)=2) as numB7 ,(select isnull(sum(F_ItemCount),0) from T_EquipStarVacation_CTE where F_Date=a.F_Date and F_StarLevel='8档' and len(F_Vocation)=2) as numB8 from T_EquipStarVacation_CTE a group by F_Date order by F_Date desc "; try { ds = DBHelperDigGameDB.Query(sql); DataView myView = ds.Tables[0].DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); if (ControlChart1.Visible) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State, true, 0, 1); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text)) { Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError); return; } //设置日期按钮状态 string dates = Convert.ToDateTime(tboxTimeB.Text).ToString("yyyy-MM-01"); DateTime dtb = Convert.ToDateTime(dates); DateTime dte = dtb.AddMonths(1).AddDays(-1); for (int i = 0; i <= 32; i++) { Control ctl = DateSelect.FindControl("btndateselect" + i); if (ctl != null && ctl.GetType() == typeof(Button)) { Button btn = (Button)ctl; if (dtb.AddDays(i - 1) <= DateTime.Now && (i <= dte.Day || i == 32)) { btn.Visible = true; if (btn.Text == Convert.ToDateTime(tboxTimeB.Text).ToString("dd")) { btn.CssClass = "buttonblueo"; btn.Enabled = false; } else { btn.CssClass = "buttonblue"; btn.Enabled = true; } } else { btn.Visible = false; } } } 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) + " "; QueryZoneRole(searchdateB); return; string sqlwhere = ""; 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 sql = ""; sql = @"SELECT convert(nvarchar(2), F_CreateTime,108) as F_CreateTime, F_GZONEID , max(F_GZONENAME) as F_GZONENAME from T_RoleOnLineFlow with(nolock) where 1=1 " + sqlwhere + " group by convert(nvarchar(2), F_CreateTime,108),F_GZONEID order by F_CreateTime asc"; try { ds = DBHelperDigGameDB.Query(sql); lblDebug.Text = sql; List <string> lines = new List <string>(); DataTable dtt = new DataTable(); dtt.Columns.Add("F_Time", typeof(string)); dtt.Columns.Add("num", typeof(int)); foreach (DataRow dr in ds.Tables[0].Rows) { string line = dr["F_GZONENAME"].ToString(); string time = dr["F_CreateTime"].ToString(); if (!lines.Contains(line) && !string.IsNullOrEmpty(line)) { lines.Add(line); } if (dtt.Select("F_Time='" + time + "'").Length == 0) { DataRow drt = dtt.NewRow(); drt["F_Time"] = time; drt["num"] = 1; dtt.Rows.Add(drt); } } while (GridView1.Columns.Count > 1) { GridView1.Columns.RemoveAt(1); } foreach (string linename in lines) { BoundField col = new BoundField(); col.HeaderText = linename; GridView1.Columns.Add(col); lblHistory.Text += GetHistoryOnlineInfo(linename); } BoundField coli = new BoundField(); coli.HeaderText = App_GlobalResources.Language.LblSum; GridView1.Columns.Add(coli); DataView myView = dtt.DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); for (int i = 1; i < GridView1.Columns.Count; i++) { GridView1.FooterRow.Cells[i].Text = "0"; for (int y = 0; y < GridView1.Rows.Count; y++) { string max = GetMax(GridView1.FooterRow.Cells[i].Text, GridView1.Rows[y].Cells[i].Text); string now = GridView1.FooterRow.Cells[i].Text.Split(' ')[0]; if (Convert.ToInt32(max) > Convert.ToInt32(now)) { GridView1.FooterRow.Cells[i].Text = "" + max + " [" + GridView1.Rows[y].Cells[0].Text + "]"; } } } GridView1.HeaderRow.Cells[GridView1.Columns.Count - 1].Text = App_GlobalResources.Language.LblSum + " <span class=tyellow>" + GridView1.FooterRow.Cells[GridView1.Columns.Count - 1].Text + "</span>"; if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { 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 * FROM T_RoleRemain where F_Date>='" + searchdateB.ToString(SmallDateTimeFormat) + "' and F_Date<='" + searchdateE.ToString(SmallDateTimeFormat) + "' "; if (DropDownListArea1.SelectedIndex > 0) { sql += @" and F_BigZone=" + DropDownListArea1.SelectedValue.Split(',')[1] + ""; } if (DropDownListArea2.SelectedIndex > 0) { sql += @" and F_Zone=" + DropDownListArea2.SelectedValue.Split(',')[1] + ""; } sql += " ORDER BY [F_Zone],[F_Date] DESC"; try { ds = DBHelperDigGameDB.Query(sql); DataTable dt = ds.Tables[0]; #region 增加留存数据计算 //增加新列 dt.Columns.Add("F_Remain2").SetOrdinal(dt.Columns.Count - 1); dt.Columns.Add("F_Remain3").SetOrdinal(dt.Columns.Count - 1); dt.Columns.Add("F_Remain4").SetOrdinal(dt.Columns.Count - 1); dt.Columns.Add("F_Remain5").SetOrdinal(dt.Columns.Count - 1); dt.Columns.Add("F_Remain6").SetOrdinal(dt.Columns.Count - 1); dt.Columns.Add("F_Remain7").SetOrdinal(dt.Columns.Count - 1); //加入数据 for (int i = 0; i < dt.Rows.Count; i++) { if (i + 1 >= dt.Rows.Count)//2日留存 { dt.Rows[i]["F_Remain2"] = "-"; } else { if (((int)dt.Rows[i + 1]["F_LoginDay1Num"] != 0) && (dt.Rows[i]["F_Zone"].ToString() == dt.Rows[i + 1]["F_Zone"].ToString())) { double percent = Math.Round((int)dt.Rows[i]["F_LoginDay2Num"] * 1.00 / (int)dt.Rows[i + 1]["F_LoginDay1Num"] * 100.0, 2); dt.Rows[i]["F_Remain2"] = percent + "%"; } else { dt.Rows[i]["F_Remain2"] = "-"; } } if (i + 2 >= dt.Rows.Count)//3日留存 { dt.Rows[i]["F_Remain3"] = "-"; } else { if ((int)dt.Rows[i + 2]["F_LoginDay1Num"] != 0 && dt.Rows[i]["F_Zone"].ToString() == dt.Rows[i + 2]["F_Zone"].ToString()) { double percent1 = Math.Round((int)dt.Rows[i]["F_LoginDay3Num"] * 1.00 / (int)dt.Rows[i + 2]["F_LoginDay1Num"] * 100.0, 2); dt.Rows[i]["F_Remain3"] = percent1 + "%"; } else { dt.Rows[i]["F_Remain3"] = "-"; } } if (i + 3 >= dt.Rows.Count)//4日留存 { dt.Rows[i]["F_Remain4"] = "-"; } else { if ((int)dt.Rows[i + 3]["F_LoginDay1Num"] != 0 && dt.Rows[i]["F_Zone"].ToString() == dt.Rows[i + 3]["F_Zone"].ToString()) { double percent1 = Math.Round((int)dt.Rows[i]["F_LoginDay4Num"] * 1.00 / (int)dt.Rows[i + 3]["F_LoginDay1Num"] * 100.0, 2); dt.Rows[i]["F_Remain4"] = percent1 + "%"; } else { dt.Rows[i]["F_Remain4"] = "-"; } } if (i + 4 >= dt.Rows.Count)//5日留存 { dt.Rows[i]["F_Remain5"] = "-"; } else { if ((int)dt.Rows[i + 4]["F_LoginDay1Num"] != 0 && dt.Rows[i]["F_Zone"].ToString() == dt.Rows[i + 4]["F_Zone"].ToString()) { double percent2 = Math.Round((int)dt.Rows[i]["F_LoginDay5Num"] * 1.00 / (int)dt.Rows[i + 4]["F_LoginDay1Num"] * 100.0, 2); dt.Rows[i]["F_Remain5"] = percent2 + "%"; } else { dt.Rows[i]["F_Remain5"] = "-"; } } if (i + 5 >= dt.Rows.Count)//6日留存 { dt.Rows[i]["F_Remain6"] = "-"; } else { if ((int)dt.Rows[i + 5]["F_LoginDay1Num"] != 0 && dt.Rows[i]["F_Zone"].ToString() == dt.Rows[i + 5]["F_Zone"].ToString()) { double percent2 = Math.Round((int)dt.Rows[i]["F_LoginDay6Num"] * 1.00 / (int)dt.Rows[i + 5]["F_LoginDay1Num"] * 100.0, 2); dt.Rows[i]["F_Remain6"] = percent2 + "%"; } else { dt.Rows[i]["F_Remain6"] = "-"; } } if (i + 6 >= dt.Rows.Count) { dt.Rows[i]["F_Remain7"] = "-"; } else { if ((int)dt.Rows[i + 6]["F_LoginDay1Num"] != 0 && dt.Rows[i]["F_Zone"].ToString() == dt.Rows[i + 6]["F_Zone"].ToString()) { double percent3 = Math.Round((int)dt.Rows[i]["F_LoginDay7Num"] * 1.00 / (int)dt.Rows[i + 6]["F_LoginDay1Num"] * 100.0, 2); dt.Rows[i]["F_Remain7"] = percent3 + "%"; } else { dt.Rows[i]["F_Remain7"] = "-"; } } } #endregion DataView myView = ds.Tables[0].DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State, true, 0, 0); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text)) { Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError); return; } string itemStr = "F_OS"; if (Request.QueryString["item"] != null) { itemStr = Request.QueryString["item"]; itemStr = itemStr.Replace("+*+", "+'*'+");//由于在前段处理 字符串转义 +'*'+较为复杂,此处直接修改后台替换 } 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).AddDays(1); } LabelTime.Text = searchdateB.ToString(SmallDateTimeFormat) + " " + App_GlobalResources.Language.LblTo + " " + searchdateE.ToString(SmallDateTimeFormat); string sql = ""; sql = @"SELECT isnull(" + itemStr + ",'未知') as item,count(1) as num FROM T_CountDetail where F_Time>='" + searchdateB.ToString(SmallDateTimeFormat) + "' and F_Time<='" + searchdateE.ToString(SmallDateTimeFormat) + "' "; //if (DropDownListArea1.SelectedIndex > 0) //{ // sql += @" and F_BigZone=" + DropDownListArea1.SelectedValue.Split(',')[1] + ""; //} //if (DropDownListArea2.SelectedIndex > 0) //{ // sql += @" and F_ZoneID=" + DropDownListArea2.SelectedValue.Split(',')[1] + ""; //} sql += "group by " + itemStr + " order by num desc"; try { ds = DBHelperGSSDB.Query(sql); DataView myView = ds.Tables[0].DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State, true, 0, 0); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text)) { Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError); return; } //设置日期按钮状态 string dates = Convert.ToDateTime(tboxTimeB.Text).ToString("yyyy-MM-01"); DateTime dtb = Convert.ToDateTime(dates); DateTime dte = dtb.AddMonths(1).AddDays(-1); for (int i = 0; i <= 32; i++) { Control ctl = DateSelect.FindControl("btndateselect" + i); if (ctl != null && ctl.GetType() == typeof(Button)) { Button btn = (Button)ctl; if (dtb.AddDays(i - 1) <= DateTime.Now && (i <= dte.Day || i == 32)) { btn.Visible = true; if (btn.Text == Convert.ToDateTime(tboxTimeB.Text).ToString("dd")) { btn.CssClass = "buttonblueo"; btn.Enabled = false; } else { btn.CssClass = "buttonblue"; btn.Enabled = true; } } else { btn.Visible = false; } } } 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 "; 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 sql = ""; sql = @"with RVcte(F_TimePara, F_Pro, F_Num) as ( SELECT F_TimePara,F_Pro,Sum(F_Num) as F_Num FROM T_RoleLoseTimeFrist " + sqlwhere + @" group by F_TimePara,F_Pro ) select distinct(a.F_TimePara) as aa ,replace(F_TimePara,'301-9999999','" + string.Format(App_GlobalResources.Language.Msg_AndAboveFormat, "301") + "') as '" + App_GlobalResources.Language.LblTime + "',{0} FROM RVcte a order by a.F_TimePara asc"; try { string tmpl = "(select isnull(Sum(F_Num),0) from RVcte where F_TimePara=a.F_TimePara and F_Pro={0}) as '{1}'"; List <string> fields = new List <string>(); foreach (KeyValuePair <string, string> item in MapVacationType()) { fields.Add(string.Format(tmpl, item.Key, item.Value)); } sql = string.Format(sql, string.Join(",", fields)); ds = DBHelperDigGameDB.Query(sql); DataTable dtNew = ds.Tables[0].Clone(); string[] times = { "0-5", "6-10", "11-15", "16-30", "31-60", "61-90", "91-120", "121-180", "181-300", string.Format(App_GlobalResources.Language.Msg_AndAboveFormat, "301") }; foreach (string time in times) { DataRow[] drs = ds.Tables[0].Select(App_GlobalResources.Language.LblTime + "='" + time + "'"); if (drs.Length > 0) { DataRow drnew = dtNew.NewRow(); for (int i = 0; i < dtNew.Columns.Count; i++) { drnew[i] = drs[0][i].ToString(); } dtNew.Rows.Add(drnew); } } dtNew.Columns.Remove("aa"); GridView1.Columns.Clear(); columnValue = new List <long>(); foreach (DataColumn item in dtNew.Columns) { columnValue.Add(0); if (item.ColumnName == "F_TimeParaArea") { GridView1.Columns.Add(new BoundField() { DataField = item.ColumnName, HeaderText = item.ColumnName, DataFormatString = App_GlobalResources.Language.Msg_MinuteFormat }); } else { GridView1.Columns.Add(new BoundField() { DataField = item.ColumnName, HeaderText = item.ColumnName }); } } GridView1.Columns.Add(new BoundField() { HeaderText = App_GlobalResources.Language.LblSum }); DataView myView = dtNew.DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; lblinfo.Text = sql; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text)) { Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError); return; } //设置日期按钮状态 string dates = Convert.ToDateTime(tboxTimeB.Text).ToString("yyyy-01-01"); DateTime dtb = Convert.ToDateTime(dates); for (int i = 1; i <= 12; i++) { Control ctl = DateSelect.FindControl("btndateselect" + i); if (ctl != null && ctl.GetType() == typeof(Button)) { Button btn = (Button)ctl; if (dtb.AddMonths(i - 1) <= DateTime.Now) { btn.Text = dtb.AddMonths(i - 1).ToString("yyyy-MM"); btn.Visible = true; DateTime dttb = Convert.ToDateTime(btn.Text); DateTime dtte = dttb.AddMonths(1) > DateTime.Now ? DateTime.Now : dttb.AddMonths(1).AddDays(-1); if (dttb.ToString("yyyy-MM-dd") == tboxTimeB.Text && dtte.ToString("yyyy-MM-dd") == tboxTimeE.Text) { btn.CssClass = "buttonblo"; btn.Enabled = false; } else { btn.CssClass = "buttonbl"; btn.Enabled = true; } } else { btn.Visible = false; } } } 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 sqlwhere = ""; sqlwhere += @" and F_Date>='" + searchdateB.ToString(SmallDateTimeFormat) + "' and F_Date<='" + searchdateE.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 sql = ""; sql = @"select distinct F_Date as '" + App_GlobalResources.Language.LblTime + @"', {0},(select SUM(F_VocationNum) from T_RoleVocationGrow where F_Date=a.F_Date ) as '" + App_GlobalResources.Language.LblSum + "' FROM T_RoleVocationGrow a WHERE 1=1"; sql += sqlwhere; sql += " order by a.F_Date desc"; try { string template = "(select isnull(Sum(F_VocationNum),0) from T_RoleVocationGrow where F_Date=a.F_Date and F_VocationType={0} ) as '{1}'"; List <string> fields = new List <string>(); foreach (KeyValuePair <string, string> item in MapVacationType()) { fields.Add(string.Format(template, item.Key, item.Value)); } sql = string.Format(sql, string.Join(",", fields)); ds = DBHelperDigGameDB.Query(sql); DataTable table = ds.Tables[0]; GridView1.Columns.Clear(); foreach (DataColumn item in table.Columns) { GridView1.Columns.Add(new BoundField() { DataField = item.ColumnName, HeaderText = item.ColumnName }); } DataView myView = ds.Tables[0].DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State, true); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; //lblerro.Text = sql; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { 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 "; 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 * FROM OPENQUERY ([LKSV_3_gsdata_db_" + bigZone + "_" + battleZone + "],'SELECT * FROM (SELECT RankId,RankSubType,RankNumber,ObjectId,ObjectName,ObjectIntInfo,ObjectSubIntInfo,ObjectThdIntInfo,SortValue,UpdateTime FROM ranktable WHERE RankId=21 AND RankSubType=0) A LEFT JOIN (SELECT RankId AS RankId1,RankSubType AS RankSubType1,RankNumber AS RankNumber1,ObjectId AS ObjectId1,ObjectName AS ObjectName1,ObjectIntInfo AS ObjectIntInfo1,ObjectSubIntInfo AS ObjectSubIntInfo1,ObjectThdIntInfo AS ObjectThdIntInfo1,SortValue AS SortValue1,UpdateTime AS UpdateTime1 FROM ranktable WHERE RankId=21 AND RankSubType=1) A1 ON A.RankNumber=A1.RankNumber1 LEFT JOIN (SELECT RankId AS RankId2,RankSubType AS RankSubType2,RankNumber AS RankNumber2,ObjectId AS ObjectId2,ObjectName AS ObjectName2,ObjectIntInfo AS ObjectIntInfo2,ObjectSubIntInfo AS ObjectSubIntInfo2,ObjectThdIntInfo AS ObjectThdIntInfo2,SortValue AS SortValue2,UpdateTime AS UpdateTime2 FROM ranktable WHERE RankId=21 AND RankSubType=2) A2 ON A.RankNumber=A2.RankNumber2')"; try { ds = DBHelperDigGameDB.Query(sql); DataView myView = ds.Tables[0].DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); GridView2.DataSource = myView; GridView2.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView2, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State, false, 0, 0); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; //lblerro.Text = sql; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { 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 F_id ,F_Year,F_Month,F_Day,F_Date,F_BigZone, ISNULL(F_BackIntervalDay3,0) as F_BackIntervalDay3, ISNULL(F_BackIntervalDay4,0) as F_BackIntervalDay4, ISNULL(F_BackIntervalDay5,0) as F_BackIntervalDay5, ISNULL(F_BackIntervalDay6,0) as F_BackIntervalDay6, ISNULL(F_BackIntervalDay7AM,0) as F_BackIntervalDay7AM FROM T_UserStateBack where F_Date>='" + searchdateB.ToString(DateTimeFormat) + "' and F_Date<='" + searchdateE.ToString(DateTimeFormat) + "' "; if (DropDownListArea1.SelectedIndex > 0) { sql += @" and F_BigZone=" + DropDownListArea1.SelectedValue.Split(',')[1] + ""; } if (DropDownListArea2.SelectedIndex > 0) { sql += @" and F_ZoneID=" + DropDownListArea2.SelectedValue.Split(',')[1] + ""; } sql += " order by F_Date desc"; try { ds = DBHelperDigGameDB.Query(sql); DataView myView = ds.Tables[0].DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State, true, 0, 0); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text)) { Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError); return; } //设置日期按钮状态 string dates = Convert.ToDateTime(tboxTimeB.Text).ToString("yyyy-MM-01"); DateTime dtb = Convert.ToDateTime(dates); DateTime dte = dtb.AddMonths(1).AddDays(-1); for (int i = 0; i <= 32; i++) { Control ctl = DateSelect.FindControl("btndateselect" + i); if (ctl != null && ctl.GetType() == typeof(Button)) { Button btn = (Button)ctl; if (dtb.AddDays(i - 1) <= DateTime.Now && (i <= dte.Day || i == 32)) { btn.Visible = true; if (btn.Text == Convert.ToDateTime(tboxTimeB.Text).ToString("dd")) { btn.CssClass = "buttonblueo"; btn.Enabled = false; } else { btn.CssClass = "buttonblue"; btn.Enabled = true; } } else { btn.Visible = false; } } } 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 "; 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 sql = ""; sql = @"with RVcte(F_Level,F_VocationType, F_VocationNum) as ( SELECT (F_Level-1)/5 as F_Level,F_VocationType, Sum(F_VocationNum) as F_VocationNum FROM T_RoleLevelGrow " + sqlwhere + @" group by (F_Level-1)/5,F_VocationType ) select distinct(a.F_Level) as aa ,cast(F_Level*5+1 as varchar(3))+' - '+cast((F_Level+1)*5 as varchar(3)) as F_LevelArea, {0} FROM RVcte a order by a.F_Level asc"; try { string template = "(select isnull(Sum(F_VocationNum),0) from RVcte where F_Level=a.F_Level and F_VocationType={0}) as '{1}'"; List <string> fields = new List <string>(); columnValue = new List <long>(); foreach (KeyValuePair <string, string> item in MapVacationType()) { fields.Add(string.Format(template, item.Key, item.Value)); columnValue.Add(0); } sql = string.Format(sql, string.Join(",", fields)); ds = DBHelperDigGameDB.Query(sql); DataTable table = ds.Tables[0]; table.Columns.Remove("aa"); GridView1.Columns.Clear(); foreach (DataColumn item in table.Columns) { if (item.ColumnName == "F_LevelArea") { GridView1.Columns.Add(new BoundField() { DataField = item.ColumnName, HeaderText = App_GlobalResources.Language.LblLevel, DataFormatString = App_GlobalResources.Language.Msg_RoleGradeFormat }); } else { GridView1.Columns.Add(new BoundField() { DataField = item.ColumnName, HeaderText = item.ColumnName }); } } GridView1.Columns.Add(new BoundField() { HeaderText = App_GlobalResources.Language.LblSum }); DataView myView = table.DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; //lblerro.Text = sql; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { 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 = ""; sqlwhere += @" and F_Date>='" + searchdateB.ToString(SmallDateTimeFormat) + "'"; sqlwhere += @" and F_Date<='" + searchdateE.ToString(SmallDateTimeFormat) + "'"; if (DropDownListArea1.SelectedIndex >= 0) { sqlwhere += @" and F_BigZone=" + DropDownListArea1.SelectedValue.Split(',')[1] + ""; } string sql = ""; sql = @"SELECT F_ID, F_Year, F_Month, F_Day, convert(varchar(10),F_Date,111) as F_Date, F_BigZone, F_ZoneID, F_Count FROM T_RoleWeibo where 1=1 " + sqlwhere + " order by F_Date ASC"; try { ds = DBHelperDigGameDB.Query(sql); lblDebug.Text = sql; List <string> lines = new List <string>(); DataTable dtt = new DataTable(); dtt.Columns.Add("F_Date", typeof(string)); foreach (DataRow dr in ds.Tables[0].Rows) { string line = dr["F_ZoneID"].ToString(); string time = dr["F_Date"].ToString(); if (!lines.Contains(line) && !string.IsNullOrEmpty(line)) { lines.Add(line); } if (dtt.Select("F_Date='" + time + "'").Length == 0) { DataRow drt = dtt.NewRow(); drt["F_Date"] = time; dtt.Rows.Add(drt); } } while (GridView1.Columns.Count > 1) { GridView1.Columns.RemoveAt(1); } foreach (string linename in lines) { BoundField col = new BoundField(); col.HeaderText = linename; GridView1.Columns.Add(col); // lblHistory.Text += GetHistoryOnlineInfo(linename); } BoundField coli = new BoundField(); coli.HeaderText = App_GlobalResources.Language.LblSum; GridView1.Columns.Add(coli); DataView myView = dtt.DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); if (GridView1.Columns.Count == 2) { return; } for (int i = 1; i < GridView1.Columns.Count; i++) { GridView1.FooterRow.Cells[i].Text = "0"; for (int y = 0; y < GridView1.Rows.Count; y++) { string max = GridView1.Rows[y].Cells[i].Text; string now = GridView1.FooterRow.Cells[i].Text; GridView1.FooterRow.Cells[i].Text = (Convert.ToInt32(max) + Convert.ToInt32(now)).ToString(); } } GridView1.HeaderRow.Cells[GridView1.Columns.Count - 1].Text = App_GlobalResources.Language.LblSum; if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { 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 F_SuitName,F_StarLevel,F_EquipType,sum(F_ItemCount) as F_Num FROM T_EquipLevelVacation where F_SuitName<>'精炼' and F_Date>='" + searchdateB.ToString(SmallDateTimeFormat) + "' and F_Date<='" + searchdateE.ToString(SmallDateTimeFormat) + "' "; if (DropDownListArea1.SelectedIndex > 0) { sql += @" and F_BigZone=" + DropDownListArea1.SelectedValue.Split(',')[1] + ""; } if (DropDownListArea2.SelectedIndex > 0) { sql += @" and F_ZoneID=" + DropDownListArea2.SelectedValue.Split(',')[1] + ""; } sql += @" group by F_SuitName,F_StarLevel,F_EquipType"; try { dsMS = DBHelperDigGameDB.Query(sql); string sqlMy = "select 1";//where OPID=50087 string bigzoneid = DropDownListArea1.SelectedValue.Split(',')[1]; string zoneid = DropDownListArea2.SelectedValue.Split(',')[1]; SqlParameter[] parameters = { new SqlParameter("@BigZoneID", SqlDbType.Int), new SqlParameter("@ZoneID", SqlDbType.Int), new SqlParameter("@DBType", SqlDbType.Int), new SqlParameter("@Query", SqlDbType.NVarChar), new SqlParameter("@PageIndex", SqlDbType.Int), new SqlParameter("@PageSize", SqlDbType.Int), new SqlParameter("@PCount", SqlDbType.Int), }; parameters[0].Value = bigzoneid; parameters[1].Value = zoneid; parameters[2].Value = 0;//GSLOG_DB parameters[3].Value = sqlMy.Replace("'", "''"); parameters[4].Value = 1; parameters[5].Value = 1000; parameters[6].Direction = ParameterDirection.Output; dsMy = DBHelperDigGameDB.RunProcedure("_Query_SQLCustom", parameters, "dsMy"); List <ItemTemp> datalist = new List <ItemTemp>(); datalist.Add(new ItemTemp("第一套", "1星")); datalist.Add(new ItemTemp("第一套", "2星")); datalist.Add(new ItemTemp("第一套", "3星")); datalist.Add(new ItemTemp("第二套", "2星")); datalist.Add(new ItemTemp("第二套", "3星")); datalist.Add(new ItemTemp("第二套", "4星")); datalist.Add(new ItemTemp("第三套", "3星")); datalist.Add(new ItemTemp("第三套", "4星")); datalist.Add(new ItemTemp("第三套", "5星")); datalist.Add(new ItemTemp("第四套", "4星")); datalist.Add(new ItemTemp("第四套", "5星")); datalist.Add(new ItemTemp("第四套", "6星")); datalist.Add(new ItemTemp("第五套", "5星")); datalist.Add(new ItemTemp("第五套", "6星")); datalist.Add(new ItemTemp("第五套", "7星")); datalist.Add(new ItemTemp("第六套", "6星")); datalist.Add(new ItemTemp("第六套", "7星")); datalist.Add(new ItemTemp("第六套", "8星")); datalist.Add(new ItemTemp("第七套", "6星")); datalist.Add(new ItemTemp("第七套", "7星")); datalist.Add(new ItemTemp("第七套", "8星")); GridView1.DataSource = datalist; GridView1.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, lblTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State, true, 0, 0); } lblerro.Visible = false; } catch (System.Exception ex) { lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text)) { Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError); return; } //设置日期按钮状态 string dates = Convert.ToDateTime(tboxTimeB.Text).ToString("yyyy-01-01"); DateTime dtb = Convert.ToDateTime(dates); for (int i = 1; i <= 12; i++) { Control ctl = DateSelect.FindControl("btndateselect" + i); if (ctl != null && ctl.GetType() == typeof(Button)) { Button btn = (Button)ctl; if (dtb.AddMonths(i - 1) <= DateTime.Now) { btn.Text = dtb.AddMonths(i - 1).ToString("yyyy-MM"); btn.Visible = true; DateTime dttb = Convert.ToDateTime(btn.Text); DateTime dtte = dttb.AddMonths(1) > DateTime.Now ? DateTime.Now : dttb.AddMonths(1).AddDays(-1); if (dttb.ToString("yyyy-MM-dd") == tboxTimeB.Text && dtte.ToString("yyyy-MM-dd") == tboxTimeE.Text) { btn.CssClass = "buttonblo"; btn.Enabled = false; } else { btn.CssClass = "buttonbl"; btn.Enabled = true; } } else { btn.Visible = false; } } } 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 sqlwhere = " where F_OPID=40052 "; sqlwhere += @" and F_Date>='" + searchdateB.ToString(SmallDateTimeFormat) + "' and F_Date<='" + searchdateE.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 sql = @"SELECT F_Date,SUM(F_InRoleCount) as F_InRoleCount FROM T_Other_GSLog_Total " + sqlwhere + @" group by F_Date order by F_Date"; try { ds = DBHelperDigGameDB.Query(sql); DataView myView = ds.Tables[0].DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State, true, 0, 0); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; //lblerro.Text = sql; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { 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 "; 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 sql = ""; sql = @"with RVcte(F_BigZone, F_ZoneID,F_Level, F_PKHonor, F_RoleID, F_Pro, F_RoleName, F_LevelUpTime, F_LastTime) as ( SELECT F_BigZone, F_ZoneID,F_Level, F_PKHonor, F_RoleID, F_Pro, F_RoleName, F_LevelUpTime, F_LastTime FROM T_RolePKHonorRank " + sqlwhere + @" ) select * from (SELECT TOP 50 ROW_NUMBER() OVER(order by F_PKHonor desc) AS rownum,F_BigZone, F_ZoneID,F_Level, F_PKHonor, F_RoleID, F_Pro, F_RoleName, F_LevelUpTime, F_LastTime FROM RVcte) a left join (SELECT TOP 50 ROW_NUMBER() OVER(order by F_PKHonor desc) AS rownum1,F_BigZone as F_BigZone1,F_ZoneID as F_ZoneID1,F_Level as F_Level1, F_PKHonor as F_PKHonor1, F_RoleID as F_RoleID1, F_Pro as F_Pro1, F_RoleName as F_RoleName1, F_LevelUpTime as F_LevelUpTime1, F_LastTime as F_LastTime1 FROM RVcte where F_Pro=1) a1 on a.rownum=a1.rownum1 left join (SELECT TOP 50 ROW_NUMBER() OVER(order by F_PKHonor desc) AS rownum2,F_BigZone as F_BigZone2,F_ZoneID as F_ZoneID2,F_Level as F_Level2, F_PKHonor as F_PKHonor2, F_RoleID as F_RoleID2, F_Pro as F_Pro2, F_RoleName as F_RoleName2, F_LevelUpTime as F_LevelUpTime2, F_LastTime as F_LastTime2 FROM RVcte where F_Pro=2) a2 on a.rownum=a2.rownum2 left join (SELECT TOP 50 ROW_NUMBER() OVER(order by F_PKHonor desc) AS rownum3,F_BigZone as F_BigZone3,F_ZoneID as F_ZoneID3,F_Level as F_Level3, F_PKHonor as F_PKHonor3, F_RoleID as F_RoleID3, F_Pro as F_Pro3, F_RoleName as F_RoleName3, F_LevelUpTime as F_LevelUpTime3, F_LastTime as F_LastTime3 FROM RVcte where F_Pro=3) a3 on a.rownum=a3.rownum3 left join (SELECT TOP 50 ROW_NUMBER() OVER(order by F_PKHonor desc) AS rownum4,F_BigZone as F_BigZone4,F_ZoneID as F_ZoneID4,F_Level as F_Level4, F_PKHonor as F_PKHonor4, F_RoleID as F_RoleID4, F_Pro as F_Pro4, F_RoleName as F_RoleName4, F_LevelUpTime as F_LevelUpTime4, F_LastTime as F_LastTime4 FROM RVcte where F_Pro=4) a4 on a.rownum=a4.rownum4 left join (SELECT TOP 50 ROW_NUMBER() OVER(order by F_PKHonor desc,F_LastTime desc) AS rownum99,F_BigZone as F_BigZone99,F_ZoneID as F_ZoneID99,F_Level as F_Level99, F_PKHonor as F_PKHonor99, F_RoleID as F_RoleID99, F_Pro as F_Pro99, F_RoleName as F_RoleName99, F_LevelUpTime as F_LevelUpTime99, F_LastTime as F_LastTime99 FROM RVcte ) a99 on a.rownum=a99.rownum99 "; try { ds = DBHelperDigGameDB.Query(sql); DataView myView = ds.Tables[0].DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); GridView2.DataSource = myView; GridView2.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView2, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State, false, 0, 0); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; //lblerro.Text = sql; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text)) { Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError); return; } //设置日期按钮状态 string dates = Convert.ToDateTime(tboxTimeB.Text).ToString("yyyy-MM-01"); DateTime dtb = Convert.ToDateTime(dates); DateTime dte = dtb.AddMonths(1).AddDays(-1); for (int i = 0; i <= 32; i++) { Control ctl = DateSelect.FindControl("btndateselect" + i); if (ctl != null && ctl.GetType() == typeof(Button)) { Button btn = (Button)ctl; if (dtb.AddDays(i - 1) <= DateTime.Now && (i <= dte.Day || i == 32)) { btn.Visible = true; if (btn.Text == Convert.ToDateTime(tboxTimeB.Text).ToString("dd")) { btn.CssClass = "buttonblueo"; btn.Enabled = false; } else { btn.CssClass = "buttonblue"; btn.Enabled = true; } } else { btn.Visible = false; } } } 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 = ""; //sqlwhere += @" and F_Date='" + searchdateB.ToString(SmallDateTimeFormat) + "'"; if (DropDownListArea1.SelectedIndex > 0) { sqlwhere += @" and F_BigZone=" + DropDownListArea1.SelectedValue.Split(',')[0] + ""; } if (DropDownListArea2.SelectedIndex > 0) { sqlwhere += @" and F_ZoneID=" + DropDownListArea2.SelectedValue.Split(',')[0] + ""; } string sql = ""; //sql = @"SELECT top 100 ROW_NUMBER() OVER(order by sum(F_IPLoginNum) desc) AS rownum, SUM(F_IPLoginNum) as F_IPLoginNum, F_AreaName from T_UserLoginArea where 1=1 " + sqlwhere + " group by F_AreaName"; sql = @"SELECT F_UserID,F_UserName,F_LoginTime,F_ExitTime,F_LoginIP,F_Device_id FROM [LKSV_1_GameLogDB_0].GameLogDB.[dbo].[T_UserEnterExitLog" + searchdateB.ToString("yyyyMMdd") + "] WHERE 1=1" + sqlwhere + "ORDER BY F_UserID"; try { ds = DBHelperDigGameDB.Query(sql); DataView myView = ds.Tables[0].DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; //lblSum.Text = GetSum(myView); } GridView1.DataSource = myView; GridView1.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State, false, 1, 1); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; //lblerro.Text = sql; } }
/// <summary> /// 绑定数据 /// </summary> public void bind() { if (!Common.Validate.IsDateTime(tboxTimeB.Text) || !Common.Validate.IsDateTime(tboxTimeE.Text)) { Common.MsgBox.Show(this, App_GlobalResources.Language.Tip_TimeError); return; } //设置日期按钮状态 string dates = Convert.ToDateTime(tboxTimeB.Text).ToString("yyyy-MM-01"); DateTime dtb = Convert.ToDateTime(dates); DateTime dte = dtb.AddMonths(1).AddDays(-1); for (int i = 0; i <= 32; i++) { Control ctl = DateSelect.FindControl("btndateselect" + i); if (ctl != null && ctl.GetType() == typeof(Button)) { Button btn = (Button)ctl; if (dtb.AddDays(i - 1) <= DateTime.Now && (i <= dte.Day || i == 32)) { btn.Visible = true; if (btn.Text == Convert.ToDateTime(tboxTimeB.Text).ToString("dd")) { btn.CssClass = "buttonblueo"; btn.Enabled = false; } else { btn.CssClass = "buttonblue"; btn.Enabled = true; } } else { btn.Visible = false; } } } LabelArea.Text = DropDownListArea1.SelectedItem.Text; if (DropDownListArea2.SelectedIndex > 0) { LabelArea.Text += "|" + DropDownListArea2.SelectedItem.Text; } if (DropDownListArea3.SelectedIndex > 0) { LabelArea.Text += "|" + DropDownListArea3.SelectedItem.Text; } string searchdateB = DateTime.Now.ToString("yyyy-MM-dd"); string searchdateE = DateTime.Now.ToString("yyyy-MM-dd"); if (tboxTimeB.Text.Length > 0) { searchdateB = Convert.ToDateTime(tboxTimeB.Text).ToString("yyyy-MM-dd"); } if (tboxTimeE.Text.Length > 0) { searchdateE = Convert.ToDateTime(tboxTimeE.Text).ToString("yyyy-MM-dd"); } LabelTime.Text = searchdateB; string sqlwhere = " where 1=1 "; //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 sql = ""; sql = string.Format(@"WITH RVcte(F_Level,F_Pro,F_Num) AS ( SELECT F_Level,F_Pro,Sum(Num) AS F_Num FROM ( SELECT CONVERT(VARCHAR(100),F_CreateTime,23) F_CreateTime,F_ZoneID,F_Level,F_Pro,count(1) AS Num FROM GameCoreDB.dbo.T_RoleBaseData_0 WITH(NOLOCK) WHERE F_UpdateTime<Dateadd(DAY,-3,GETDATE()) AND CONVERT(VARCHAR(100),F_CreateTime,23)>='{0}' AND CONVERT(VARCHAR(100),F_CreateTime,23)<='{1}' group by CONVERT(VARCHAR(100),F_CreateTime,23),F_ZoneID,F_Level,F_Pro UNION ALL SELECT CONVERT(VARCHAR(100),F_CreateTime,23) F_CreateTime,F_ZoneID,F_Level,F_Pro,count(1) AS Num FROM GameCoreDB.dbo.T_RoleBaseData_1 WITH(NOLOCK) WHERE F_UpdateTime<Dateadd(DAY,-3,GETDATE()) AND CONVERT(VARCHAR(100),F_CreateTime,23)>='{2}' AND CONVERT(VARCHAR(100),F_CreateTime,23)<='{3}' group by CONVERT(VARCHAR(100),F_CreateTime,23),F_ZoneID,F_Level,F_Pro UNION ALL SELECT CONVERT(VARCHAR(100),F_CreateTime,23) F_CreateTime,F_ZoneID,F_Level,F_Pro,count(1) AS Num FROM GameCoreDB.dbo.T_RoleBaseData_2 WITH(NOLOCK) WHERE F_UpdateTime<Dateadd(DAY,-3,GETDATE()) AND CONVERT(VARCHAR(100),F_CreateTime,23)>='{4}' AND CONVERT(VARCHAR(100),F_CreateTime,23)<='{5}' group by CONVERT(VARCHAR(100),F_CreateTime,23),F_ZoneID,F_Level,F_Pro UNION ALL SELECT CONVERT(VARCHAR(100),F_CreateTime,23) F_CreateTime,F_ZoneID,F_Level,F_Pro,count(1) AS Num FROM GameCoreDB.dbo.T_RoleBaseData_3 WITH(NOLOCK) WHERE F_UpdateTime<Dateadd(DAY,-3,GETDATE()) AND CONVERT(VARCHAR(100),F_CreateTime,23)>='{6}' AND CONVERT(VARCHAR(100),F_CreateTime,23)<='{7}' group by CONVERT(VARCHAR(100),F_CreateTime,23),F_ZoneID,F_Level,F_Pro UNION ALL SELECT CONVERT(VARCHAR(100),F_CreateTime,23) F_CreateTime,F_ZoneID,F_Level,F_Pro,count(1) AS Num FROM GameCoreDB.dbo.T_RoleBaseData_4 WITH(NOLOCK) WHERE F_UpdateTime<Dateadd(DAY,-3,GETDATE()) AND CONVERT(VARCHAR(100),F_CreateTime,23)>='{8}' AND CONVERT(VARCHAR(100),F_CreateTime,23)<='{9}' group by CONVERT(VARCHAR(100),F_CreateTime,23),F_ZoneID,F_Level,F_Pro )TEMP {10} GROUP BY F_Level,F_Pro ) SELECT DISTINCT (a.F_Level) AS '等级', (SELECT ISNULL(SUM(F_Num), 0) FROM RVcte WHERE F_Level = a.F_Level AND F_Pro = 1) AS '猎魔者', (SELECT ISNULL(SUM(F_Num), 0) FROM RVcte WHERE F_Level = a.F_Level AND F_Pro = 2) AS '魔导师', (SELECT ISNULL(SUM(F_Num), 0) FROM RVcte WHERE F_Level = a.F_Level AND F_Pro = 3) AS '龙战士', (SELECT ISNULL(SUM(F_Num), 0) FROM RVcte WHERE F_Level = a.F_Level AND F_Pro = 4) AS '召唤师' FROM RVcte a ORDER BY a.F_Level ASC", searchdateB, searchdateE, searchdateB, searchdateE, searchdateB, searchdateE, searchdateB, searchdateE, searchdateB, searchdateE, sqlwhere); try { //string tmpl = "(select isnull(Sum(F_Num),0) from RVcte where F_Level=a.F_Level and F_Pro={0}) as '{1}'"; //List<string> fields = new List<string>(); //foreach (KeyValuePair<String, string> item in MapVacationType()) //{ // fields.Add(string.Format(tmpl, item.Key, item.Value)); //} //sql = string.Format(sql, string.Join(",", fields)); ds = DBHelperGameCoreDB.Query(sql); columnValue = new List <long>(); DataTable table = ds.Tables[0]; GridView1.Columns.Clear(); GridView1.Columns.Add(new BoundField() { DataField = "等级", HeaderText = App_GlobalResources.Language.LblLevel, DataFormatString = App_GlobalResources.Language.Msg_RoleGradeFormat }); columnValue.Add(0); foreach (KeyValuePair <string, string> item in MapVacationType1()) { GridView1.Columns.Add(new BoundField() { DataField = item.Value, HeaderText = item.Value }); columnValue.Add(0); } GridView1.Columns.Add(new BoundField() { HeaderText = App_GlobalResources.Language.LblSum }); DataView myView = table.DefaultView; if (myView.Count == 0) { lblerro.Visible = true; myView.AddNew(); } else { lblerro.Visible = false; } GridView1.DataSource = myView; GridView1.DataBind(); if (ControlChart1.Visible == true) { ControlChart1.SetChart(GridView1, LabelTitle.Text.Replace(">>", " - ") + " " + LabelArea.Text + " " + LabelTime.Text, ControlChartSelect1.State); } } catch (System.Exception ex) { GridView1.DataSource = null; GridView1.DataBind(); lblerro.Visible = true; lblerro.Text = App_GlobalResources.Language.LblError + ex.Message; lblinfo.Text = sql; } }