/// <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 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);
}
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);
}
public async Task <object> Export([FromBody] ImportStudentDto model)
{
Dictionary <string, string> columnNames = new Dictionary <string, string>();
List <string> lists = new List <string>();
lists.Add("IdCard");
var properties = typeof(ImportStudentDto).GetProperties();
foreach (var property in properties)
{
var name = property.GetCustomAttribute <DisplayAttribute>()?.Name;
if (!string.IsNullOrWhiteSpace(name))
{
foreach (var list in lists)
{
if (list == property.Name)
{
columnNames.Add(property.Name, name);
}
}
}
}
var students = await db.ImportStudentDtos.ToListAsync();
var sc = await ExportExcelHelper.GetByteToExportExcel(students, columnNames, lists, "sheet1", "测试");
return(File(sc, "application/octet-stream", "测试.xlsx"));
//columnNames.Add("SerialNumber", "序号");
//columnNames.Add("StudentCode", "学籍号");
//columnNames.Add("Name", "姓名");
//columnNames.Add("Gender", "性别");
//columnNames.Add("IdCard", "身份证号");
}
public ActionResult Export(string primaryKey)//, TestParamViewModel model
{
try
{
var pageData = _inGroupResultService.GetList(null, primaryKey);//.GetResultList(primaryKey);//model.patientId,model.startTime,model.endTime,
int filterId = int.Parse(primaryKey);
var filtername = _ingroupService.Get(e => e.SD_FILTER_ID == filterId).SD_FILTER_NAME;
var filename = string.Format("{0}_{1}_{2}.xlsx", ProjectProvider.Instance.Current.SD_NAME, filtername, DateTime.Now.ToString("yyyyMMddhhmmss"));
var ok = ExportExcelHelper.DataSetToExcel <InGroupResultViewModel>(pageData, false, filename);
if (ok.Item1)
{
return(Success(ok.Item2));
}
return(Error(ok.Item2));
}
catch (Exception ex)
{
_iErrorLogService.Insert(new PDP_ERROR_LOG
{
SD_ID = ProjectProvider.Instance.Current.SD_ID,
PROC_TYPE = ConvertExeFlag("export"),
//PROC_URL =
//DLL_NAME =
//CLASS_NAME =
//FUNC_NAME =
ERR_CONTENT = ex.ToString(),
});
return(Error(ex.ToString()));
}
}
public async Task <IActionResult> GetExcelFiles()
{
byte[] data = await ExportExcelHelper.GenerateExcelFile(EmployeeRepository.EmployeeData());
string filePath = Path.Combine(Directory.GetCurrentDirectory(), "Exports", "export.xlsx");
System.IO.File.WriteAllBytes(filePath, data);
return(Ok(filePath));
}
public IActionResult Export(string iconname)
{
var tuple = GetPrecondition(iconname);
var exportList = _iconService.PageList(0, 0, out int totalcount, tuple.Item1, tuple.Item2, true);
ExportExcelHelper <SysIcon> exprot = new ExportExcelHelper <SysIcon>(_hosting.WebRootPath);
if (exprot.Export(_exportName, exportList, out string filepath, out string excelname))
{
return(File($"~\\{filepath}", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelname));
}
/// <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);
}
public JsonResult ExportExcel(string ids)
{
ResultJsonInfo resultJsonInfo = new ResultJsonInfo()
{
Data = "", ErrorMsg = "", Success = true
};
List <GoodsExport> goodsExportModels = new List <GoodsExport>();
List <GoodsDto> list = Service.GetAllGoods();
//if (!string.IsNullOrEmpty(ids))
//{
// list = Service.Find(ids);
//}
//else
//{
//}
foreach (var item in list)
{
goodsExportModels.Add(new GoodsExport()
{
商品名称 = item.GoodsName,
原价 = item.Price,
会员价 = item.VipPrice,
是否上架 = item.IfShelves == 1 ? "是" : "否",
是否促销 = item.IfPromotion == 1 ? "是" : "否",
创建时间 = item.CredateTime.ToString()
});
}
if (list == null || list.Count == 0)
{
resultJsonInfo.Success = false;
resultJsonInfo.ErrorMsg = "暂未数据";
}
else
{
string fileName = "商品导出.xls";
ExportExcelHelper <GoodsExport> .ExportListToExcel_MvcResult(goodsExportModels, ref fileName);
var path = "/Export/Temp/" + fileName;
resultJsonInfo.Data = path;
}
return(Json(resultJsonInfo));
}
public ActionResult Export()
{
DataTable table = new DataTable();
table.Columns.Add("登录ID", typeof(string));
table.Columns.Add("姓名", typeof(string));
table.Columns.Add("密码", typeof(string));
DataRow row = table.NewRow();
row["登录ID"] = "登陆1";
row["姓名"] = "姓名1";
row["密码"] = "123";
table.Rows.Add(row);
ExportExcelHelper.ExportFromDataTable("员工信息.xls", table);
return(resultMsg("导出成功！"));
}
public JsonResult ExportExcel(string ids)
{
ResultJsonInfo resultJsonInfo = new ResultJsonInfo()
{
Data = "", ErrorMsg = "", Success = true
};
List <UserExport> userExportModels = new List <UserExport>();
List <UserDto> list = Service.GetAllUsers();
//if (!string.IsNullOrEmpty(ids))
//{
// list = Service.Find(ids);
//}
//else
//{
//}
foreach (var item in list)
{
userExportModels.Add(new UserExport()
{
用户名 = item.RealName,
账号 = item.LogingName,
邮箱 = item.Email
});
}
if (list == null || list.Count == 0)
{
resultJsonInfo.Success = false;
resultJsonInfo.ErrorMsg = "暂未数据";
}
else
{
string fileName = "用户导出.xls";
ExportExcelHelper <UserExport> .ExportListToExcel_MvcResult(userExportModels, ref fileName);
var path = "/Export/Temp/" + fileName;
resultJsonInfo.Data = path;
}
return(Json(resultJsonInfo));
}
public JsonResult ExportExcelAjax()
{
dynamic result = new ExpandoObject();
result.Code = "0001";
result.Msg = "error";
result.Url = string.Empty;
pbs_basic_OrderService pbsBasicOrderService = new pbs_basic_OrderService();
ResultInfo <List <SaleGoodsReport> > resultList = pbsBasicOrderService.GetSaleGoodsReportList();
if (resultList.Result && resultList.Data != null)
{
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
string savePath = Server.MapPath("~/Content/export/") + fileName;
ExportExcelHelper.ExportExcel(savePath, ParseHelper.ToDataTable(resultList.Data));
result.Code = "0000";
result.Msg = "ok";
result.Url = "/Content/export/" + fileName;
}
return(Json(JsonConvert.SerializeObject(result), JsonRequestBehavior.AllowGet));
}
public ActionResult ExportBill()
{
QueryParam queryParam = new QueryParam();
var extendParams = Request.Params["extendParams"];
if (extendParams != null)
{
JavaScriptSerializer js = new JavaScriptSerializer();
List <FilterModel> filters = js.Deserialize <List <FilterModel> >(extendParams);
Dictionary <string, FilterModel> filterSet = filters.ToDictionary(key => key.key, model => model);
queryParam.filters = filterSet;
}
OperateResult or = SalaryRecordManager.ExportBill(queryParam);
if (or.status == OperateStatus.Success &&
or.data != null)
{
string exportFileName = string.Concat("工资条", DateTime.Now.ToString("yyyyMMddHHmmss"), ".xlsx");
var excelHelper = new ExportExcelHelper()
{
SheetName = "工资条",
FileName = exportFileName,
ExportData = (DataTable)or.data
};
excelHelper.GenerateExcelEx(this.HttpContext);
return(new EmptyResult());
}
return(Json(or, JsonRequestBehavior.AllowGet));
}
/// <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);
}
}