/// <summary>
/// Excel表格导出
/// </summary>
/// <returns></returns>
public JsonResult GetExcelExport()
{
try
{
var ADName = Request["AdName"] ?? "";
var IsAdmin = Request["IsAmdin"] ?? "";
var startLaunchTime = Request["StartLaunchTime"] ?? "";
var endLaunchTime = Request["EndLaunchTime"] ?? "";
var Status = Request["LaunchStatus"] ?? "";
var ADPlanID = Request["ADPlanID"] ?? "";
var NewAdPlanID = Request["NewAdPlanID"] ?? "";
#region 时间验证
if (startLaunchTime == "" || endLaunchTime == "")
{
return(Json(new BaseResponse()
{
result = false, msg = "请选择开始和结束时间"
}));
}
if (DateTime.Compare(DateTime.Now.AddDays(-31), Convert.ToDateTime(startLaunchTime)) > 0)
{
return(Json(new BaseResponse()
{
result = false, msg = "时间范围不能超出当前日期30天"
}));
}
TimeSpan ts1 = new TimeSpan(Convert.ToDateTime(startLaunchTime).Ticks);
TimeSpan ts2 = new TimeSpan(Convert.ToDateTime(endLaunchTime).Ticks);
TimeSpan ts = ts1.Subtract(ts2).Duration();
if (ts.Days > 30)
{
return(Json(new BaseResponse()
{
result = false, msg = "时间范围不能超出30天"
}));
}
#endregion
var paramRequest = new PlanReportRequest
{
UserManageId = base.UserInfo.UserManageID,
AdName = ADName,
NewAdPlanId = NewAdPlanID,
StartTime = startLaunchTime,
EndTime = endLaunchTime,
LaunchStatus = Status,
IsAmdin = IsAdmin == "1"
};
var res = planLogic.ExportReportPlanReport(paramRequest);
return(Json(new BaseResponse {
result = res.Item1, msg = res.Item2, fileUrl = res.Item3
}));
}
catch (Exception ex)
{
LogWriter.error($"下载excel异常，{ex.ToString() + ex.StackTrace}");
return(Json(new BaseResponse {
result = false, msg = "下载异常，请联系管理员处理"
}));
}
}
/// <summary>
/// 计划操报表信息分页查询
/// </summary>
/// <returns></returns>
public List <PlanReportEntity> QueryAdPlanReportList(PlanReportRequest request, out int total)
{
total = 0;
List <PlanReportEntity> list = new List <PlanReportEntity>();
StringBuilder sq = new StringBuilder();
sq.Append(" select * from ( ");
sq.Append(" select {2} ");
sq.Append(" from dbo.tblPlanReport a with(nolock) ");
sq.Append(" where {0} ");
sq.Append(" ) c where {1} ");
DynamicParameters dp = new DynamicParameters();
string where_1 = " 1=1 ";
if (!request.IsAmdin)
{
where_1 += " and a.UserManageId=@UserManageId";
dp.Add("UserManageId", request.UserManageId, DbType.String);
}
if (!string.IsNullOrWhiteSpace(request.StartTime))
{
where_1 += " and a.LaunchDate>=@StartTime";
dp.Add("StartTime", Convert.ToDateTime(Convert.ToDateTime(request.StartTime).ToString("yyyy-MM-dd")), DbType.DateTime);
}
if (!string.IsNullOrWhiteSpace(request.EndTime))
{
where_1 += " and a.LaunchDate<@EndTime";
dp.Add("EndTime", Convert.ToDateTime(request.EndTime).AddDays(1), DbType.DateTime);
}
if (!string.IsNullOrWhiteSpace(request.NewAdPlanId))
{
where_1 += " and a.NewAdPlanId=@NewAdPlanId";
dp.Add("NewAdPlanId", request.NewAdPlanId, DbType.String);
}
if (!string.IsNullOrWhiteSpace(request.AdName))
{
where_1 += " and a.AdName=@AdName";
dp.Add("AdName", request.AdName, DbType.String);
}
if (!string.IsNullOrWhiteSpace(request.LaunchStatus))
{
where_1 += " and a.LaunchStatus=@LaunchStatus";
dp.Add("LaunchStatus", request.LaunchStatus, DbType.String);
}
dp.Add("PageIndex", request.PageIndex, DbType.Int32, ParameterDirection.Input);
dp.Add("PageSize", request.PageSize, DbType.Int32, ParameterDirection.Input);
string sql_list = string.Format(sq.ToString(), where_1, " c.Num > (@PageIndex - 1) * @PageSize and c.Num <= @PageIndex * @PageSize", "ROW_NUMBER() over(order by a.CreateTime desc) as Num,* ");
string sql_count = string.Format(sq.ToString(), where_1, "1=1", "count(0) as nums");
using (IDbConnection conn = new SqlConnection(DBConnectionStringConfig.Default.JMGGConnectionString))
{
total = conn.Query <int>(sql_count, dp).FirstOrDefault();
list = conn.Query <PlanReportEntity>(sql_list, dp).ToList();
}
return(list);
}
/// <summary>
/// 计划报表信息excel查询
/// </summary>
/// <returns></returns>
public List <PlanReportEntity> QueryAdPlanReportExcel(PlanReportRequest request)
{
List <PlanReportEntity> list = new List <PlanReportEntity>();
StringBuilder sq = new StringBuilder();
sq.Append(" select * from ( ");
sq.Append(" select {2} ");
sq.Append(" from dbo.tblPlanReport a with(nolock) ");
sq.Append(" where {0} ");
sq.Append(" ) c where {1} ");
DynamicParameters dp = new DynamicParameters();
string where_1 = " 1=1 ";
if (!request.IsAmdin)
{
where_1 += " and a.UserManageId=@UserManageId";
dp.Add("UserManageId", request.UserManageId, DbType.String);
}
if (!string.IsNullOrWhiteSpace(request.StartTime))
{
where_1 += " and a.LaunchDate>=@StartTime";
dp.Add("StartTime", Convert.ToDateTime(Convert.ToDateTime(request.StartTime).ToString("yyyy-MM-dd")), DbType.DateTime);
}
if (!string.IsNullOrWhiteSpace(request.EndTime))
{
where_1 += " and a.LaunchDate<@EndTime";
dp.Add("EndTime", Convert.ToDateTime(request.EndTime).AddDays(1), DbType.DateTime);
}
if (!string.IsNullOrWhiteSpace(request.NewAdPlanId))
{
where_1 += " and a.NewAdPlanId=@NewAdPlanId";
dp.Add("NewAdPlanId", request.NewAdPlanId, DbType.String);
}
if (!string.IsNullOrWhiteSpace(request.AdName))
{
where_1 += " and a.AdName=@AdName";
dp.Add("AdName", request.AdName, DbType.String);
}
if (!string.IsNullOrWhiteSpace(request.LaunchStatus))
{
where_1 += " and a.LaunchStatus=@LaunchStatus";
dp.Add("LaunchStatus", request.LaunchStatus, DbType.String);
}
string sql_list = string.Format(sq.ToString(), where_1, "1=1", "* ");
using (IDbConnection conn = new SqlConnection(DBConnectionStringConfig.Default.JMGGConnectionString))
{
list = conn.Query <PlanReportEntity>(sql_list, dp).ToList();
}
return(list);
}
public PlanReportPageResponse QueryPlanRepostListPage(PlanReportRequest request)
{
int total = 0;
var pageList = planQuery.QueryAdPlanReportList(request, out total);
PlanReportPageResponse page = new PlanReportPageResponse();
if (pageList != null && pageList.Count > 0)
{
page.count = total;
page.data = pageList;
return(page);
}
return(page);
}
/// <summary>
/// 报表
/// </summary>
/// <returns></returns>
public JsonResult GetAdPlanReportList()
{
try
{
int page = !string.IsNullOrEmpty(Request["page"]) ? Convert.ToInt32(Request["page"]) : 1;
int limit = !string.IsNullOrEmpty(Request["limit"]) ? Convert.ToInt32(Request["limit"]) : 10;
var ADName = Request["AdName"] ?? "";
var IsAdmin = Request["IsAmdin"] ?? "";
var startLaunchTime = Request["StartLaunchTime"] ?? "";
var endLaunchTime = Request["EndLaunchTime"] ?? "";
var Status = Request["LaunchStatus"] ?? "";
var ADPlanID = Request["ADPlanID"] ?? "";
var NewAdPlanID = Request["NewAdPlanID"] ?? "";
#region 时间验证
if (startLaunchTime == "" || endLaunchTime == "")
{
return(Json(new PlanReportPageResponse()
{
code = -1, msg = "请选择开始和结束时间"
}));
}
if (DateTime.Compare(DateTime.Now.AddDays(-31), Convert.ToDateTime(startLaunchTime)) > 0)
{
return(Json(new PlanReportPageResponse()
{
code = -1, msg = "时间范围不能超出当前日期30天"
}));
}
TimeSpan ts1 = new TimeSpan(Convert.ToDateTime(startLaunchTime).Ticks);
TimeSpan ts2 = new TimeSpan(Convert.ToDateTime(endLaunchTime).Ticks);
TimeSpan ts = ts1.Subtract(ts2).Duration();
if (ts.Days > 30)
{
return(Json(new PlanReportPageResponse()
{
code = -1, msg = "时间范围不能超出30天"
}));
}
#endregion
var paramRequest = new PlanReportRequest
{
PageIndex = page,
PageSize = limit,
UserManageId = base.UserInfo.UserManageID,
AdName = ADName,
NewAdPlanId = NewAdPlanID,
StartTime = startLaunchTime,
EndTime = endLaunchTime,
LaunchStatus = Status,
IsAmdin = IsAdmin == "1"
};
if (paramRequest.PageIndex == 0)
{
paramRequest.PageIndex = 1;
}
else
{
paramRequest.PageIndex = (paramRequest.PageIndex / 10) + 1;
}
var result = planLogic.QueryPlanRepostListPage(paramRequest);
if (result != null && result.count > 0)
{
result.msg = "SUCCESS";
result.code = 0;
}
else
{
result.msg = "未查询到数据";
result.code = -1;
}
return(Json(result));
}
catch (Exception ex)
{
LogWriter.error($"GetAdPlanReportList=>获取广告信息异常：{ex.ToString() + ex.Message}");
return(Json(new PlanReportPageResponse()
{
code = -1, msg = "未查询到数据"
}));
}
}
/// <summary>
/// 统计报表
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public Tuple <bool, string, string> ExportReportPlanReport(PlanReportRequest request)
{
var basePath = AppDomain.CurrentDomain.BaseDirectory + "ReportFiles\\";
if (!Directory.Exists(basePath))
{
Directory.CreateDirectory(basePath);
}
var fileName = string.Format("data_{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss"));
var relativePath = "/ReportFiles/" + fileName;
var filePath = basePath + fileName;
if (!File.Exists(filePath))
{
var f = File.Create(filePath);
f.Close();
f.Dispose();
}
MemoryStream stream = new MemoryStream();
IWorkbook eworkbook = new HSSFWorkbook();
var ssumSheet = eworkbook.CreateSheet(string.Format("data"));
var ssumR0 = ssumSheet.CreateRow(0);
ssumR0.CreateCell(0).SetCellValue("投放时间");
ssumR0.CreateCell(1).SetCellValue("广告ID");
ssumR0.CreateCell(2).SetCellValue("商家ID");
ssumR0.CreateCell(3).SetCellValue("广告名称");
ssumR0.CreateCell(4).SetCellValue("投放状态");
ssumR0.CreateCell(5).SetCellValue("曝光(PV)");
ssumR0.CreateCell(6).SetCellValue("点击(CPV)");
ssumR0.CreateCell(7).SetCellValue("CTR(PV)");
ssumR0.CreateCell(8).SetCellValue("曝光(UV)");
ssumR0.CreateCell(9).SetCellValue("点击(CUV)");
ssumR0.CreateCell(10).SetCellValue("CTR(UV)");
ssumR0.CreateCell(11).SetCellValue("下载量(DPV)");
ssumR0.CreateCell(12).SetCellValue("安装量");
ssumR0.CreateCell(13).SetCellValue("实际消耗金额");
var rows = planQuery.QueryAdPlanReportExcel(request);
var index = 1;
foreach (var item in rows)
{
var status = item.LaunchStatus == "1" ? "已发布" : item.LaunchStatus == "2" ? "已结束" : item.LaunchStatus == "3" ? "已暂停" : "";
IRow theRow = ssumSheet.CreateRow(index);
theRow.CreateCell(0).SetCellValue(item.LaunchDate.ToString("yyyy-MM-dd HH:mm:ss"));
theRow.CreateCell(1).SetCellValue(item.NewAdPlanId);
theRow.CreateCell(2).SetCellValue(item.BussinessId);
theRow.CreateCell(3).SetCellValue(item.AdName);
theRow.CreateCell(4).SetCellValue(status);
theRow.CreateCell(5).SetCellValue(item.PV);
theRow.CreateCell(6).SetCellValue(item.CPV);
theRow.CreateCell(7).SetCellValue(item.CTRPV);
theRow.CreateCell(8).SetCellValue(item.UV);
theRow.CreateCell(9).SetCellValue(item.UV);
theRow.CreateCell(10).SetCellValue(item.CUV);
theRow.CreateCell(11).SetCellValue(item.DPV);
theRow.CreateCell(12).SetCellValue(item.InstallCount);
theRow.CreateCell(13).SetCellValue(item.ActualAmount);
index++;
}
ICellStyle style = eworkbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
IFont font = eworkbook.CreateFont();
font.FontHeightInPoints = 10;
style.SetFont(font);
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
for (var i = 0; i < eworkbook.NumberOfSheets; i++)
{
foreach (IRow row in eworkbook.GetSheetAt(i))
{
foreach (ICell cell in row)
{
cell.CellStyle = style;
}
}
}
var fs = new FileStream(filePath, FileMode.OpenOrCreate);
eworkbook.Write(fs);
fs.Close();
fs.Dispose();
return(new Tuple <bool, string, string>(true, "生成文件成功,请下载!", relativePath));
}
