游戏成本分析
2019-04-16 10:48:26
总：问题
前端问题：
1、页面404,，可能是主页面没加载
2、[exitStore.getProxy().extraParams = datastore.getProxy().extraParams;
exitStore.load();]用于将主数据源从搜索框获取的参数，拷贝到其他数据源
3、chartItems[{text: '奖品金额', field: 'n_award_normal'}],获取text数据使用chartItems[0].text(chartItems是数组)
4、搜索框xtype:'combo'文本框，xtype:'datefield'时间框，multiSelect: true支持复选，editable: true支持编辑，value默认显示
5、html抬头展示、innerText文本显示设置（详见Chart1.js）
6、echartsListeners:{click:function(){....}}用于前端点击监听
7、bbar: [{xtype: 'displayfield', width: '100%', style: {textAlign: 'center'},itemId: 'exit_list_desc', value: '趋势变化情况'}],---》me.down('#exit_chart_desc').setValue(）用于设置图标底端文字
后台问题：
1、BigDecimal自带加减乘除方法，支持数值运算
一、需求展示：
二、数据清洗
说明：数据清洗分为两个部分
一：添加用户标签属性存入IMPALA中间表dw_dmp.wf_game_awards_cost_user_info，用于成本分层
user_id，n_channel_id,n_award_exit(奖品出口),n_award_name(奖品名称),n_redemption_status(兑出状态),n_uesr_normal_flag,rmb_amout(奖品金额)
二：针对实际成本模块，将数据清洗到mysql表beeplay_channel.game_awards_cost
按n_channel_id,n_award_exit(奖品出口),n_award_name(奖品名称),n_redemption_status(兑出状态)分别考虑2的4次方，16种情况+最小精度（具体见GameAwardsCostDataMain.scala）
位置：
GameAwardsUserInfoClean.scala
package com.jdd.SparkCore.gameAwardsUserInfoClean
import CommonUtils.DateUtils
import com.jdd.common.Constant
import org.apache.log4j.Logger
import org.apache.spark.sql.SparkSession
object GameAwardsUserInfoClean {
def main(args: Array[String]): Unit = {
val logger = Logger.getLogger(this.getClass)
val spark = SparkSession.builder()
.appName("GameAwardsUserInfoClean")
.config("hive.exec.dynamic.partition", "true")
.config("hive.exec.max.created.files", "100000")
.config("hive.exec.max.dynamic.partitions", "100000")
.config("hive.exec.dynamic.partition.mode", "nonstrict")
.config("hive.exec.max.dynamic.partitions.pernode", "100000")
.config("dfs.permissions", "false")
.config("hive.metastore.uris", Constant.HIVE_URI)
.config("spark.network.timeout", "360")
.config("spark.sql.shuffle.partitions", "10")
.config("spark.shuffle.service.enabled", "true")
.config("spark.dynamicAllocation.enabled", "false") //动态申请executor，默认true
.config("spark.yarn.executor.memoryOverhead", "3072") // M
.config("spark.sql.autoBroadcastJoinThreshold", "5000")
.config("spark.debug.maxToStringFields", "5000")
.config("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
.config("mapreduce.job.inputformat.class", "org.apache.hadoop.mapreduce.lib.input.CombineFileInputFormat")
.config("mapreduce.input.fileinputformat.split.maxsize", "268435456")
.config("spark.kryoserializer.buffer.max", "1024m")
.config("spark.driver.maxResultSize", "4096")
.getOrCreate()
var beginDay = ""
var endDay = ""
if (args.size >= 2) {
beginDay = args.apply(0)
endDay = args.apply(1)
} else {
endDay = DateUtils.formatCurrentDate("yyyy-MM-dd")
beginDay = DateUtils.formatDate(DateUtils.getYesterday, "yyyy-MM-dd")
}
getAwardsCostInfo(spark,beginDay,endDay)
}
def getAwardsCostInfo(spark: SparkSession,beginDay:String,endDay:String): Unit = {
val logger = Logger.getLogger(this.getClass)
val create_time = DateUtils.formatCurrentDate("yyyy-MM-dd HH:mm:ss")
val today_pay = beginDay.replace("-","")
val p_day = DateUtils.getDateByDaysInterval(beginDay, -1, DateUtils.DATE_PATTERN).replace("-", "")
val beginTime = beginDay + " 00:00:00"
val endTime = endDay + " 00:00:00"
val prop = com.jdd.impalaProp
val mysqlProp = com.jdd.mysqlProp
//获奖用户部分属性：渠道、用户属性、累充层级、兑出状态
val awardsCostDataType = spark.sql(
s"""
|SELECT a.user_id
| ,case when a.receive_status = 3 then 2 else 1 end as n_redemption_status
| ,a.phy_awards_id
| ,a.activity_type
| ,case when c.n_user_normal_flag is null then 1 else c.n_user_normal_flag end as n_user_normal_flag
| ,case when b.parent_id is not null then b.parent_id
| when b.parent_id is null then a.channel_id end as n_channel_id
|FROM dw_wf_game.inventory_phy_awards_sendlog a
|left join dw_wf_game.channel_info b
|on a.channel_id = b.id
|left join (
| select user_id,user_type as n_user_normal_flag
| from dw_dmp.wf_user_basic_data
| where s_app_key = 'wf_game'
| and p_day = '$today_pay'
|) c
|on a.user_id = c.user_id
|where a.create_time >= '$beginTime'
|and a.create_time < '$endTime'
""".stripMargin)
awardsCostDataType.createOrReplaceTempView("awards_cost_data_type")
spark.sql(
s"""
|select * from dw_wf_game.inventory_phy_awards_info
""".stripMargin).createOrReplaceTempView("awards_cost_info")
spark.sql(
s"""
|select * from dw_wf_game.shop_dict
""".stripMargin).createOrReplaceTempView("shop_dict")
val beginValue = spark.sql(
s"""
|select a.user_id --用户ID
| ,a.n_channel_id --渠道号
| ,c.value as n_award_exit --奖品出口ID
| ,a.phy_awards_id as n_award_name --奖品名称ID
| ,a.n_redemption_status --兑出状态
| ,a.n_user_normal_flag --用户状态
| ,b.rmb_amount --奖品金额|
|from awards_cost_data_type a
|left join awards_cost_info b
|on a.phy_awards_id = b.id
|left join shop_dict c
|on a.activity_type = c.value
|where c.type ='activity_type'
""".stripMargin).createOrReplaceTempView("user_info")
val appKey = "wf_game"
spark.sql("insert overwrite table dw_dmp.wf_game_awards_cost_user_info partition(s_app_key, p_day)" +
" select *, '" + appKey + "' as s_app_key, '" + today_pay + "' as p_day from user_info")
}
}
GameAwardsCostDataMain.scala
package com.jdd.SparkCore.gameAwardsCostDataMain
import CommonUtils.DateUtils
import org.apache.log4j.Logger
import org.apache.spark.sql.{SaveMode, SparkSession}
import com.jdd.common.Constant
import constans.MysqlConstans
object GameAwardsCostDataMain {
def main(args: Array[String]): Unit = {
val logger = Logger.getLogger(this.getClass)
val spark = SparkSession.builder()
.appName("GameAwardsCostDataMain")
.config("hive.exec.dynamic.partition", "true")
.config("hive.exec.max.created.files", "100000")
.config("hive.exec.max.dynamic.partitions", "100000")
.config("hive.exec.dynamic.partition.mode", "nonstrict")
.config("hive.exec.max.dynamic.partitions.pernode", "100000")
.config("dfs.permissions", "false")
.config("hive.metastore.uris", Constant.HIVE_URI)
.config("spark.network.timeout", "360")
.config("spark.sql.shuffle.partitions", "10")
.config("spark.shuffle.service.enabled", "true")
.config("spark.dynamicAllocation.enabled", "false") //动态申请executor，默认true
.config("spark.yarn.executor.memoryOverhead", "3072") // M
.config("spark.sql.autoBroadcastJoinThreshold", "5000")
.config("spark.debug.maxToStringFields", "5000")
.config("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
.config("mapreduce.job.inputformat.class", "org.apache.hadoop.mapreduce.lib.input.CombineFileInputFormat")
.config("mapreduce.input.fileinputformat.split.maxsize", "268435456")
.config("spark.kryoserializer.buffer.max", "1024m")
.config("spark.driver.maxResultSize", "4096")
.getOrCreate()
var beginDay = ""
var endDay = ""
if (args.size >= 2) {
beginDay = args.apply(0)
endDay = args.apply(1)
} else {
endDay = DateUtils.formatCurrentDate("yyyy-MM-dd")
beginDay = DateUtils.formatDate(DateUtils.getYesterday, "yyyy-MM-dd")
}
getAwardsCostInfo(spark,beginDay,endDay)
}
def getAwardsCostInfo(spark: SparkSession,beginDay:String,endDay:String): Unit = {
val logger = Logger.getLogger(this.getClass)
val create_time = DateUtils.formatCurrentDate("yyyy-MM-dd HH:mm:ss")
val today_pay = beginDay.replace("-","")
val mysqlProp = com.jdd.mysqlProp
val result = spark.sql(
s"""
|select '$beginDay' as s_business_date
| ,'1' as n_channel_id
| ,'0' as n_award_exit
| ,'0' as n_award_name
| ,'0' as n_redemption_status
| ,n_user_normal_flag
| ,sum(rmb_amount) as n_award_normal
| ,count(distinct user_id) as n_award_count
| ,count(user_id) as n_awards
| ,'$create_time' as d_create_time
| ,'$create_time' as d_update_time
|from dw_dmp.wf_game_awards_cost_user_info
|where s_app_key = 'wf_game'
|and p_day = '$today_pay'
|group by n_user_normal_flag
|union all
|select '$beginDay' as s_business_date
| ,'1' as n_channel_id
| ,n_award_exit
| ,n_award_name
| ,n_redemption_status
| ,n_user_normal_flag
| ,sum(rmb_amount) as n_award_normal
| ,count(distinct user_id) as n_award_count
| ,count(user_id) as n_awards
| ,'$create_time' as d_create_time
| ,'$create_time' as d_update_time
|from dw_dmp.wf_game_awards_cost_user_info
|where s_app_key = 'wf_game'
|and p_day = '$today_pay'
|group by n_award_exit,n_award_name,n_redemption_status,n_user_normal_flag
|union all
|select '$beginDay' as s_business_date
| ,n_channel_id
| ,'0' as n_award_exit
| ,n_award_name
| ,n_redemption_status
| ,n_user_normal_flag
| ,sum(rmb_amount) as n_award_normal
| ,count(distinct user_id) as n_award_count
| ,count(user_id) as n_awards
| ,'$create_time' as d_create_time
| ,'$create_time' as d_update_time
|from dw_dmp.wf_game_awards_cost_user_info
|where s_app_key = 'wf_game'
|and p_day = '$today_pay'
|group by n_channel_id,n_award_name,n_redemption_status,n_user_normal_flag
|union all
|select '$beginDay' as s_business_date
| ,n_channel_id
| ,n_award_exit
| ,'0' as n_award_name
| ,n_redemption_status
| ,n_user_normal_flag
| ,sum(rmb_amount) as n_award_normal
| ,count(distinct user_id) as n_award_count
| ,count(user_id) as n_awards
| ,'$create_time' as d_create_time
| ,'$create_time' as d_update_time
|from dw_dmp.wf_game_awards_cost_user_info
|where s_app_key = 'wf_game'
|and p_day = '$today_pay'
|group by n_channel_id,n_award_exit,n_redemption_status,n_user_normal_flag
|union all
|select '$beginDay' as s_business_date
| ,n_channel_id
| ,n_award_exit
| ,n_award_name
| ,'0' as n_redemption_status
| ,n_user_normal_flag
| ,sum(rmb_amount) as n_award_normal
| ,count(distinct user_id) as n_award_count
| ,count(user_id) as n_awards
| ,'$create_time' as d_create_time
| ,'$create_time' as d_update_time
|from dw_dmp.wf_game_awards_cost_user_info
|where s_app_key = 'wf_game'
|and p_day = '$today_pay'
|group by n_channel_id,n_award_exit,n_award_name,n_user_normal_flag
|union all
|select '$beginDay' as s_business_date
| ,'1' as n_channel_id
| ,'0' as n_award_exit
| ,n_award_name
| ,n_redemption_status
| ,n_user_normal_flag
| ,sum(rmb_amount) as n_award_normal
| ,count(distinct user_id) as n_award_count
| ,count(user_id) as n_awards
| ,'$create_time' as d_create_time
| ,'$create_time' as d_update_time
|from dw_dmp.wf_game_awards_cost_user_info
|where s_app_key = 'wf_game'
|and p_day = '$today_pay'
|group by n_award_name,n_redemption_status,n_user_normal_flag
|union all
|select '$beginDay' as s_business_date
| ,'1' as n_channel_id
| ,n_award_exit
| ,'0' as n_award_name
| ,n_redemption_status
| ,n_user_normal_flag
| ,sum(rmb_amount) as n_award_normal
| ,count(distinct user_id) as n_award_count
| ,count(user_id) as n_awards
| ,'$create_time' as d_create_time
| ,'$create_time' as d_update_time
|from dw_dmp.wf_game_awards_cost_user_info
|where s_app_key = 'wf_game'
|and p_day = '$today_pay'
|group by n_award_exit,n_redemption_status,n_user_normal_flag
|union all
|select '$beginDay' as s_business_date
| ,'1' as n_channel_id
| ,n_award_exit
| ,n_award_name
| ,'0' as n_redemption_status
| ,n_user_normal_flag
| ,sum(rmb_amount) as n_award_normal
| ,count(distinct user_id) as n_award_count
| ,count(user_id) as n_awards
| ,'$create_time' as d_create_time
| ,'$create_time' as d_update_time
|from dw_dmp.wf_game_awards_cost_user_info
|where s_app_key = 'wf_game'
|and p_day = '$today_pay'
|group by n_award_exit,n_award_name,n_user_normal_flag
|union all
|select '$beginDay' as s_business_date
| ,n_channel_id
| ,'0' as n_award_exit
| ,'0' as n_award_name
| ,n_redemption_status
| ,n_user_normal_flag
| ,sum(rmb_amount) as n_award_normal
| ,count(distinct user_id) as n_award_count
| ,count(user_id) as n_awards
| ,'$create_time' as d_create_time
| ,'$create_time' as d_update_time
|from dw_dmp.wf_game_awards_cost_user_info
|where s_app_key = 'wf_game'
|and p_day = '$today_pay'
|group by n_channel_id,n_redemption_status,n_user_normal_flag
|union all
|select '$beginDay' as s_business_date
| ,n_channel_id
| ,'0' as n_award_exit
| ,n_award_name
| ,'0' as n_redemption_status
| ,n_user_normal_flag
| ,sum(rmb_amount) as n_award_normal
| ,count(distinct user_id) as n_award_count
| ,count(user_id) as n_awards
| ,'$create_time' as d_create_time
| ,'$create_time' as d_update_time
|from dw_dmp.wf_game_awards_cost_user_info
|where s_app_key = 'wf_game'
|and p_day = '$today_pay'
|group by n_channel_id,n_award_name,n_user_normal_flag
|union all
|select '$beginDay' as s_business_date
| ,n_channel_id
| ,n_award_exit
| ,'0' as n_award_name
| ,'0' as n_redemption_status
| ,n_user_normal_flag
| ,sum(rmb_amount) as n_award_normal
| ,count(distinct user_id) as n_award_count
| ,count(user_id) as n_awards
| ,'$create_time' as d_create_time
| ,'$create_time' as d_update_time
|from dw_dmp.wf_game_awards_cost_user_info
|where s_app_key = 'wf_game'
|and p_day = '$today_pay'
|group by n_channel_id,n_award_exit,n_user_normal_flag
|union all
|select '$beginDay' as s_business_date
| ,'1' as n_channel_id
| ,'0' as n_award_exit
| ,'0' as n_award_name
| ,n_redemption_status
| ,n_user_normal_flag
| ,sum(rmb_amount) as n_award_normal
| ,count(distinct user_id) as n_award_count
| ,count(user_id) as n_awards
| ,'$create_time' as d_create_time
| ,'$create_time' as d_update_time
|from dw_dmp.wf_game_awards_cost_user_info
|where s_app_key = 'wf_game'
|and p_day = '$today_pay'
|group by n_redemption_status,n_user_normal_flag
|union all
|select '$beginDay' as s_business_date
| ,'1' as n_channel_id
| ,'0' as n_award_exit
| ,n_award_name
| ,'0' as n_redemption_status
| ,n_user_normal_flag
| ,sum(rmb_amount) as n_award_normal
| ,count(distinct user_id) as n_award_count
| ,count(user_id) as n_awards
| ,'$create_time' as d_create_time
| ,'$create_time' as d_update_time
|from dw_dmp.wf_game_awards_cost_user_info
|where s_app_key = 'wf_game'
|and p_day = '$today_pay'
|group by n_award_name,n_user_normal_flag
|union all
|select '$beginDay' as s_business_date
| ,'1' as n_channel_id
| ,n_award_exit
| ,'0' as n_award_name
| ,'0' as n_redemption_status
| ,n_user_normal_flag
| ,sum(rmb_amount) as n_award_normal
| ,count(distinct user_id) as n_award_count
| ,count(user_id) as n_awards
| ,'$create_time' as d_create_time
| ,'$create_time' as d_update_time
|from dw_dmp.wf_game_awards_cost_user_info
|where s_app_key = 'wf_game'
|and p_day = '$today_pay'
|group by n_award_exit,n_user_normal_flag
|union all
|select '$beginDay' as s_business_date
| ,n_channel_id
| ,'0' as n_award_exit
| ,'0' as n_award_name
| ,'0' as n_redemption_status
| ,n_user_normal_flag
| ,sum(rmb_amount) as n_award_normal
| ,count(distinct user_id) as n_award_count
| ,count(user_id) as n_awards
| ,'$create_time' as d_create_time
| ,'$create_time' as d_update_time
|from dw_dmp.wf_game_awards_cost_user_info
|where s_app_key = 'wf_game'
|and p_day = '$today_pay'
|group by n_channel_id,n_user_normal_flag
|union all
|select '$beginDay' as s_business_date
| ,n_channel_id
| ,n_award_exit
| ,n_award_name
| ,n_redemption_status
| ,n_user_normal_flag
| ,sum(rmb_amount) as n_award_normal
| ,count(distinct user_id) as n_award_count
| ,count(user_id) as n_awards
| ,'$create_time' as d_create_time
| ,'$create_time' as d_update_time
|from dw_dmp.wf_game_awards_cost_user_info
|where s_app_key = 'wf_game'
|and p_day = '$today_pay'
|group by n_channel_id,n_award_exit,n_award_name,n_redemption_status,n_user_normal_flag
""".stripMargin)
//beginValue.show()
logger.warn("插入当天数据............")
println("jdbcurl:"+MysqlConstans.JDD_JDBC_BEEPLAY_CHANNEL_URL)
result.repartition(1).write.mode(SaveMode.Append).jdbc(MysqlConstans.JDD_JDBC_BEEPLAY_CHANNEL_URL, MysqlConstans.GAME_AWARDS_COST, mysqlProp)
}
}
三、前端
说明：前端页面分为四个部分
一、主体部分GameAwardsCost.js用于加载数据和调用其他页面（相当于一个储存器）
二、搜索框部分：GameAwardsCostSearch1Pannel.js用于获取前端传回后台参数
三、成本整体趋势：Charts1Panel.js Chart1.js用于相关属性的折线图展示
四、奖品出口分布、奖品名称分布：Chart2.js Chart3.js用于饼图展示
位置：
GameAwardsCost.js
Ext.define('Admin.view.gameAwardsCost.GameAwardsCost', {
extend: 'Ext.container.Container',
xtype: 'gameAwardsCost',
layout: 'responsivecolumn',
requires: [
'Admin.view.gameAwardsCost.GameAwardsCostSearch1Pannel',
'Admin.view.gameAwardsCost.Charts1Panel',
'Admin.view.gameAwardsCost.Chart2',
'Admin.view.gameAwardsCost.Chart3'
],
initComponent: function () {
this.callParent(arguments);
var me = this;
// 表格视图所需数据加载store
var datastore = Ext.create('DCIS.Store', {
autoLoad: false,
url: '/report/private/gameAwardsCost/list.do',
fields: []
});
var awardsExitStore = Ext.create('DCIS.Store', {
autoLoad: true,
url: 'report/private/dict/getAwardsExitList.do',
fields: ["code", "name"]
});
var awardsNameStore = Ext.create('DCIS.Store', {
autoLoad: true,
url: 'report/private/dict/getAwardsNameList.do',
fields: ["code", "name"]
});
var exitStore = Ext.create('DCIS.Store', {
autoLoad: false,
url: '/report/private/gameAwardsCost/listExit.do',
fields: []
});
var awardStore = Ext.create('DCIS.Store', {
autoLoad: false,
url: '/report/private/gameAwardsCost/listAward.do',
fields: []
});
var compareStore = Ext.create('DCIS.Store', {
autoLoad: false,
url: 'report/private/gameAwardsCost/getCompareData.do',
fields: []
});
var exitAwardStore = Ext.create('DCIS.Store', {
autoLoad: false,
url: '/report/private/gameAwardsCost/listExitAward.do',
fields: []
});
datastore.on('load',function () {
exitStore.getProxy().extraParams = datastore.getProxy().extraParams;
exitStore.load();
awardStore.getProxy().extraParams = datastore.getProxy().extraParams;
awardStore.load();
compareStore.getProxy().extraParams = datastore.getProxy().extraParams;
compareStore.load();
exitAwardStore.getProxy().extraParams = datastore.getProxy().extraParams;
exitAwardStore.load();
});
me.add({
xtype: 'gameAwardsCostSearch1Pannel',
userCls: 'big-100 small-100',
datastore: datastore,
compareStore: compareStore,
awardsExitStore: awardsExitStore,
awardsNameStore:awardsNameStore
});
me.add(
{
title: '成本整体趋势',
xtype: 'gameAwardsCostCharts1Panel',
userCls: 'big-100 small-100',
datastore: datastore,
compareStore: compareStore,
awardsExitStore: awardsExitStore,
chartItems: [
{title: '奖品金额', field: 'n_award_normal'},
{title: '奖品人数', field: 'n_award_count'},
{title: '奖品笔数', field: 'n_awards'},
{title: '人均奖品笔数', field: 'n_award_normal_avg'},
{title: '人均奖品金额', field: 'n_awards_avg'}
]
}
);
me.add(
{
title: '成本-出口分布情况',
xtype: 'tabpanel',
fullscreen: true, //fullscreen全屏
tabBarPosition: 'bottom',
defaults: {
styleHtmlContent: true
},
items: [
{
title: '奖品金额',
items: [
{
xtype: 'gameAwardsCostChart2',
userCls: 'big-100 small-100',
datastore: datastore,
exitStore:exitStore,
awardStore:awardStore,
exitAwardStore:exitAwardStore,
awardsExitStore: awardsExitStore,
awardsNameStore:awardsNameStore,
chartItems: [
{text: '奖品金额', field: 'n_award_normal'}
]
}
]
},
{
title: '奖品人数',
items: [
{
xtype: 'gameAwardsCostChart2',
userCls: 'big-100 small-100',
datastore: datastore,
exitStore:exitStore,
awardStore:awardStore,
exitAwardStore:exitAwardStore,
awardsExitStore: awardsExitStore,
awardsNameStore:awardsNameStore,
chartItems: [
{text: '奖品人数', field: 'n_award_count'}
]
}
]
},
{
title: '奖品笔数',
items: [
{
xtype: 'gameAwardsCostChart2',
userCls: 'big-100 small-100',
datastore: datastore,
exitStore:exitStore,
awardStore:awardStore,
exitAwardStore:exitAwardStore,
awardsExitStore: awardsExitStore,
awardsNameStore:awardsNameStore,
chartItems: [
{text: '奖品笔数', field: 'n_awards'}
]
}
]
}
]
}
);
me.add(
{
title: '成本-奖品项分布情况',
xtype: 'tabpanel',
fullscreen: true, //fullscreen全屏
tabBarPosition: 'bottom',
defaults: {
styleHtmlContent: true
},
items: [
{
title: '奖品金额',
items: [
{
xtype: 'gameAwardsCostChart3',
userCls: 'big-100 small-100',
datastore: datastore,
exitStore:exitStore,
awardStore:awardStore,
exitAwardStore:exitAwardStore,
awardsExitStore: awardsExitStore,
awardsNameStore:awardsNameStore,
chartItems: [
{text: '奖品金额', field: 'n_award_normal'}
]
}
]
},
{
title: '奖品人数',
items: [
{
xtype: 'gameAwardsCostChart3',
userCls: 'big-100 small-100',
datastore: datastore,
exitStore:exitStore,
awardStore:awardStore,
exitAwardStore:exitAwardStore,
awardsExitStore: awardsExitStore,
awardsNameStore:awardsNameStore,
chartItems: [
{text: '奖品人数', field: 'n_award_count'}
]
}
]
},
{
title: '奖品笔数',
items: [
{
xtype: 'gameAwardsCostChart3',
userCls: 'big-100 small-100',
datastore: datastore,
exitStore:exitStore,
awardStore:awardStore,
exitAwardStore:exitAwardStore,
awardsExitStore: awardsExitStore,
awardsNameStore:awardsNameStore,
chartItems: [
{text: '奖品笔数', field: 'n_awards'}
]
}
]
}
]
}
);
},
defaults: {
defaults: {
animation: !Ext.isIE9m && Ext.os.is.Desktop
}
},
});
GameAwardsCostSearch1Pannel.js
Ext.define( 'Admin.view.gameAwardsCost.GameAwardsCostSearch1Pannel', {
extend: 'Ext.panel.Panel',
title:'实际成本',
hidden:false,
xtype: 'gameAwardsCostSearch1Pannel',
closable: false,
layout: {
type: 'vbox',
align: 'stretch'
},
constructor: function (cfg) {
var config = Ext.apply({}, cfg);
this.datastore = config.datastore;
this.callParent([config]);
},
initComponent: function () {
this.callParent(arguments);
var me = this;
var dateEdit = function addDate(dd,dadd){
var a = new Date(dd);
a = a.valueOf();
a = a + dadd * 24 * 60 * 60 * 1000;
a = new Date(a);
return a;
};
var userAttrStore = Ext.create('Ext.data.Store', {
fields: ['typeId', 'name'],
data : [
{'typeId':'0','name':'全部用户'},
{'typeId':'1','name':'正常用户'},
{'typeId':'2','name':'非正常用户'}
]
});
var redempStatusStore = Ext.create('Ext.data.Store', {
fields: ['typeId', 'name'],
data : [
{'typeId':'0','name':'全部'},
{'typeId':'1','name':'遗留'},
{'typeId':'2','name':'兑出'}
]
});
var entranceStore = Ext.create('DCIS.Store', {
autoLoad:false,
url:'report/private/dict/getChannelList.do',
fields:["code","name"]
});
var delFile = function (result) {
callapi("report/private/common/delFdfsFile.do", {fileName: result}, function () {
});
};
var sp = Ext.create("DCIS.SearchPanel",{
border: false,
store: this.datastore,
showExportBtn: true,
xtype: 'searchpanel',
title: '',
collapsible: true,
collapsed: false,
columns: 4,
buildField: "Manual",
forceFit: true,
items: [
{
xtype: 'combo',
name: 'entrance',
fieldLabel: '主渠道',
store: entranceStore,
editable: false,
queryMode: "local",
valueField: "code",
displayField: "name",
allowBlank: false
},
{
xtype: 'combo',
name: 'awardExitId',
fieldLabel: '奖品出口',
store: me.awardsExitStore,
multiSelect: true,
editable: false,
queryMode: "local",
valueField: "code",
displayField: "name",
value:'0',
allowBlank: false
},
{
xtype: 'combo',
name: 'awardNameId',
fieldLabel: '奖品名称',
store: me.awardsNameStore,
multiSelect: false,
editable: false,
queryMode: "local",
valueField: "code",
displayField: "name",
value:'0',
allowBlank: false
},
{
xtype: 'combo',
name: 'userStatus',
fieldLabel: '兑出状态',
store: redempStatusStore,
multiSelect: false,
editable: false,
queryMode: "local",
valueField: "typeId",
displayField: "name",
value:'2',
allowBlank: false
},
{
xtype: 'combo',
name: 'userAttr',
fieldLabel: '用户属性',
store: userAttrStore,
multiSelect: false,
editable: false,
queryMode: "local",
valueField: "typeId",
displayField: "name",
value:'0',
allowBlank: false
},
{
xtype: 'datefield',
name: 'startDate',
format: 'Y-m-d',
fieldLabel: '数据开始日期',
value:dateEdit(new Date(),-14),
maxValue:dateEdit(new Date(),-1),
editable: false,
allowBlank: false,
listeners: {
change: function () {
var val = this.getValue();
this.nextSibling().setMinValue(val);
}
}
},
{
xtype: 'datefield',
name: 'endDate',
format: 'Y-m-d',
fieldLabel: '数据结束日期',
value:dateEdit(new Date(),-1),
minValue: DateUtil.getDate(-7),
maxValue:dateEdit(new Date(),-1),
editable: false,
allowBlank: false
}
],
resetCall: function () {
var first = sp.down('[name=entrance]').getStore().getAt(0);
first && sp.down('[name=entrance]').setValue(first.get('code'));
var first2 = sp.down('[name=awardExitId]').getStore().getAt(0);
first2 && sp.down('[name=awardExitId]').addValue('');
},
myExport: function (formValues) {
var url = 'report/private/gameAwardsCost/downloadAllList.do';
formValues.chartExitId = me.datastore.chartExitId;
formValues.chartExitAwardsId = me.datastore.chartExitAwardsId;
formValues.chartAwardsId = me.datastore.chartAwardsId;
formValues.chartAwardsExitId =me.datastore.chartAwardsExitId;
callapi(url, formValues, function (resu) {
popWin(resu);
setTimeout(function () {
delFile(resu)
}, 60000);
});
}
});
me.awardsExitStore.on('load',function (store) {
entranceStore.load();
/*var first = store.getAt(0);
first && sp.down('[name=gameIds]').addValue(first.get('code'));*/
});
entranceStore.on('load',function (store) {
var first = store.getAt(0);
first && sp.down('[name=entrance]').setValue(first.get('code'));
var btnSearch = me.down('[xtype=searchpanel]').down('[name=search]');
btnSearch.fireEvent('click', btnSearch);
});
me.add(sp);
}
}
);
Charts1Panel.js
Ext.define('Admin.view.gameAwardsCost.Charts1Panel', {
extend: 'Ext.panel.Panel',
xtype: 'gameAwardsCostCharts1Panel',
closable: false,
layout: {
type: 'vbox',
align: 'stretch'
},
requires: [
'Admin.view.gameAwardsCost.Chart1'
],
initComponent: function () {
this.callParent(arguments);
var me = this;
var panel = Ext.create('Ext.panel.Panel', {
layout: 'column',
width: '100%'
});
if (me.chartItems) {
for (var j = 0; j < me.chartItems.length; j++) {
var chartCfg = me.chartItems[j];
var columnWidth = 0.33;
if(chartCfg.columnWidth){
columnWidth = chartCfg.columnWidth;
}
var ds = me.datastore;
if(chartCfg.datastore){
ds = chartCfg.datastore;
}
panel.add({
xtype: 'gameAwardsCostChart',
columnWidth: columnWidth,
datastore: ds,
compareStore: me.compareStore,
awardsExitStore: me.awardsExitStore,
chartTitle: chartCfg.title,
chartField: chartCfg.field,
isPercent: chartCfg.isPercent,
hideCompare: chartCfg.hideCompare,
addEndDate:chartCfg.addEndDate
});
}
}
me.add(panel);
}
});
Chart1.js
Ext.define('Admin.view.gameAwardsCost.Chart1', {
extend: 'Ext.panel.Panel',
xtype: 'gameAwardsCostChart',
closable: false,
layout: {
type: 'vbox',
align: 'stretch'
},
initComponent: function () {
this.callParent(arguments);
var me = this;
var emptyOption = {
title: {
//text: me.chartTitle,
left: 'left',
textStyle: {
align: 'center'
}
},
legend: {
data: []
},
tooltip: {
trigger: 'axis',
axisPointer: { // 坐标轴指示器，坐标轴触发有效
type: 'shadow' // 默认为直线，可选为：'line' | 'shadow'
}
},
grid: {
left: '3%',
right: '4%',
bottom: '3%',
containLabel: true
},
xAxis: [
{
type: 'category',
data: []
}
],
yAxis: [
{
type: 'value'
}
],
series: [
{
name: me.chartTitle,
type: 'line',
barWidth: '60%',
data: [],
label: {
normal: {
show: false,
position: 'top'
}
}
}
]
};
var option = {
title: {
//text: me.chartTitle,
left: 'left',
textStyle: {
align: 'center'
}
},
legend: {
data: []
},
tooltip: {
trigger: 'axis',
axisPointer: { // 坐标轴指示器，坐标轴触发有效
type: 'shadow' // 默认为直线，可选为：'line' | 'shadow'
}
},
grid: {
left: '3%',
right: '4%',
bottom: '3%',
containLabel: true
},
xAxis: [
{
type: 'category',
data: []
}
],
yAxis: [
{
type: 'value'
}
],
series: [
{
name: me.chartTitle,
type: 'line',
barWidth: '60%',
data: [],
label: {
normal: {
show: false,
position: 'top'
}
}
}
]
};
me.add({
attr: 'compareHtml',
html: '<h2 style="margin-left: 20px;line-height: 30px; padding-top: 5px; color: #0b76cc">' + me.chartTitle + '</h2>' +
'<h4 style="margin-left: 20px" class="compareRow"><span class="lastDayDate">#endDate</span>: <span style="margin-left:5px;color:#4F94CD" class="lastDayVal">0</span>' +
'<span style="margin-right: 20px;float: right;" class="weekCompare">-</span><span style="margin-right:10px;float: right">周环比</span>' +
'<span style="margin-right:10px;float: right;" class="dayCompare">-</span><span style="margin-right:10px;float: right">日环比</span></h4>'
});
me.compareStore.on('load', function (store, records) {
var compareDom = me.down('[attr=compareHtml]').el.dom;
if (!me.hideCompare) {
var endDate = moment(store.getProxy().extraParams.endDate);
if (me.addEndDate) {
endDate.add(me.addEndDate, 'days')
}
var sEndDate = endDate.format('YYYY-MM-DD');
var data = store.getAt(0);
compareDom.getElementsByClassName("lastDayDate")[0].innerText = sEndDate;
var lastDayVal = compareDom.getElementsByClassName("lastDayVal")[0];
var dayCompare = compareDom.getElementsByClassName("dayCompare")[0];
var weekCompare = compareDom.getElementsByClassName("weekCompare")[0];
if (data && data.get(me.chartField)) {
lastDayVal.innerText = data.get(me.chartField) + (me.isPercent ? "%" : '');
} else {
lastDayVal.innerText = '-';
}
var dayCompareKey = 'day_rate_' + me.chartField;
if (data && data.get(dayCompareKey)) {
var val = data.get(dayCompareKey);
dayCompare.innerText = val + "% " + ('∞' === val || val > 0 ? '↑' : '↓');
dayCompare.style.color = ('∞' === val || val > 0 ? 'red' : 'green');
} else {
dayCompare.innerText = '-';
}
var weekCompareKey = 'week_rate_' + me.chartField;
if (data && data.get(weekCompareKey)) {
var val = data.get(weekCompareKey);
weekCompare.innerText = val + "% " + ('∞' === val || val > 0 ? '↑' : '↓');
weekCompare.style.color = ('∞' === val || val > 0 ? 'red' : 'green');
} else {
weekCompare.innerText = '-';
}
} else {
compareDom.getElementsByClassName('compareRow')[0].innerHTML = '';
}
});
var chartPanel = Ext.create({
xtype: 'echarts',
border: false,
height: 300,
yAxisIndex: 0,
echartsData: option,
getChartOption: function (prms, needQueryData) {
return option;
}
});
me.add(chartPanel);
me.datastore.on('load', function (store, records) {
option = JSON.parse(JSON.stringify(emptyOption));
var awardsExitIds = [];
if(me.datastore.proxy.extraParams) {
awardsExitIds = me.datastore.proxy.extraParams.awardExitId;
}
console.log(awardsExitIds);
var legendData = [];
var gameNameMap = {};
for(var i=0;i<awardsExitIds.length; i++) {
var rec = me.awardsExitStore.findRecord("code", awardsExitIds[i]);
var awardsExitIdsname = rec.get('name');
legendData.push(awardsExitIdsname);
gameNameMap[awardsExitIds[i]] = awardsExitIdsname;
}
option.legend.data = legendData;
//option.title.text = me.chartTitle;
var startDate = moment(store.getProxy().extraParams.startDate);
var endDate = moment(store.getProxy().extraParams.endDate);
if(me.addEndDate){
endDate.add(me.addEndDate, 'days')
}
var dataMap = {}, axisData = [];
for(var j=0;j<store.data.items.length; j++) {
var item = store.data.items[j];
var day = item.get('s_business_date');
var awardsId = item.get('n_award_exit');
var value = item.get(me.chartField) || 0;
dataMap[day+'_'+awardsId] = value;
};
var seriesList = [];
for(var i=0; i<awardsExitIds.length; i++) {
var values = [];
var seriesObj = {
name: gameNameMap[awardsExitIds[i]],
type: 'line',
barWidth: '60%',
data: [],
label: {
normal: {
show: false,
position: 'top'
}
}
};
for (var date = moment(startDate); date.isSameOrBefore(endDate, 'day'); date.add(1, 'days')) {
var day = date.format('YYYY-MM-DD');
if(i==0){
axisData.push(day);
}
values.push(dataMap[day+"_"+awardsExitIds[i]] || 0);
}
if (me.isPercent) {
option.yAxis[0].axisLabel = {formatter: '{value}%'};
var pencentFormatter = function (params) {
var tip = '';
if (params.length > 0) {
tip += params[0].axisValue;
for (var i = 0; i < params.length; i++) {
var item = params[i];
tip += '<br/>' + item.marker + item.seriesName + ': ' + item.value + '%';
}
return tip;
}
return '';
};
option.tooltip.formatter = pencentFormatter;
}
seriesObj.data = values;
seriesList.push(seriesObj);
}
option.xAxis[0].data = axisData;
option.series = seriesList;
// 刷新图表
var charts = me.query('[xtype=echarts]');
charts.forEach(function (chart) {
chart.fireEvent("reload", null, false);
chart.fireEvent("afterLayout", chart, null, null);
});
});
}
});
Chart2.js
Ext.define('Admin.view.gameAwardsCost.Chart2', {
extend: 'Ext.panel.Panel',
xtype: 'gameAwardsCostChart2',
closable: false,
layout: 'column',
initComponent: function () {
this.callParent(arguments);
var me = this;
var refreshChart = function (selector) {
var charts = me.query(selector || '[xtype=echarts]');
charts.forEach(function (chart) {
chart.fireEvent("reload", null, false);
chart.fireEvent("afterLayout", chart, null, null);
});
};
var sourceTarget = 71;
var awardsTarget = 0;
var exitOption = {
title: {
//text: me.text,
//subtext: '纯属虚构',
top: 20,
left: 20,
x: 'left'
},
tooltip: {
trigger: 'item',
formatter: "{b} : {c} ({d}%)"
},
legend: {
type: 'scroll',
orient: 'vertical',
right: 10,
top: 100,
data: []
},
toolbox: {
show: true,
feature: {
mark: {show: true},
dataView: {show: true, readOnly: false},
magicType: {
show: true,
type: ['pie', 'funnel']
},
restore: {show: true},
saveAsImage: {show: true}
}
},
calculable: true,
series: [
{
name: me.chartItems.text,
type: 'pie',
radius: [30, 110],
center: ['40%', '50%'],
roseType: 'area',
data: []
}
]
};
var exitPanel = Ext.create({
itemId: 'exit_chart',
xtype: 'echarts',
border: false,
columnWidth: 0.33,
height: 400,
echartsData: exitOption,
bbar: [{
xtype: 'displayfield', width: '100%', style: {textAlign: 'center'},
itemId: 'exit_chart_desc', value: '各项出口成本分布情况'
}],
getChartOption: function (prms, needQueryData) {
return exitOption;
},
echartsListeners: {
click: function (params) {
if(params.data && params.data.sourceTarget) {
setExitAwardChartData(me.exitAwardStore, params.data.sourceTarget);
setListChartData(me.exitStore, params.data.sourceTarget,awardsTarget);
refreshChart('#exit_award_chart');
refreshChart('#trend_chart');
me.datastore.chartExitId = params.data.sourceTarget;
}
}
}
});
me.add(exitPanel);
var exitAwardOption = {
title: {
//text: me.text,
//subtext: '纯属虚构',
top: 20,
left: 20,
x: 'left'
},
tooltip: {
trigger: 'item',
formatter: "{b}: {c} ({d}%)"
},
legend: {
type: 'scroll',
orient: 'vertical',
right: 10,
top: 10,
data: []
},
series: [
{
//name: '访问来源',
type: 'pie',
barWidth: '33%',
center: ['35%', '50%'],
radius: ['40%', '60%'],
avoidLabelOverlap: false,
label: {
normal: {
show: false,
position: 'center'
},
emphasis: {
show: true,
textStyle: {
fontSize: '30',
fontWeight: 'bold'
}
}
},
labelLine: {
normal: {
show: false
}
},
data: []
}
]
};
var exitAwardPanel = Ext.create({
itemId: 'exit_award_chart',
xtype: 'echarts',
border: false,
columnWidth: 0.33,
height: 400,
echartsData: exitAwardOption,
bbar: [{
xtype: 'displayfield', width: '100%', style: {textAlign: 'center'},
itemId: 'exit_awards_desc', value: '奖品分布'
}],
getChartOption: function (prms, needQueryData) {
return exitAwardOption;
}
,
echartsListeners: {
click: function (params) {
if(params.data && params.data.awardsTarget) {
setListChartData(me.exitAwardStore, params.data.sourceTarget,params.data.awardsTarget);
refreshChart('#trend_chart');
me.datastore.chartExitAwardsId = params.data.awardsTarget;
}
}
}
});
me.add(exitAwardPanel);
var trendOption = {
title: {
//text: me.chartTitle,
top: 20,
left: 20,
x: 'left'
},
legend: {
data: []
},
tooltip: {
trigger: 'axis',
axisPointer: { // 坐标轴指示器，坐标轴触发有效
type: 'shadow' // 默认为直线，可选为：'line' | 'shadow'
}
},
grid: {
left: '3%',
right: '4%',
bottom: '3%',
containLabel: true
},
xAxis: [
{
type: 'category',
data: []
}
],
yAxis: [
{
type: 'value'
}
],
series: [
{
type: 'line',
barWidth: '60%',
data: [],
label: {
normal: {
show: false,
position: 'top'
}
}
}
]
};
var trendPanel = Ext.create({
xtype: 'echarts',
itemId: 'trend_chart',
width: '90%',
columnWidth: 0.33,
height: 400,
border: false,
echartsData: trendOption,
bbar: [{
xtype: 'displayfield', width: '100%', style: {textAlign: 'center'},
itemId: 'exit_list_desc', value: '趋势变化情况'
}],
getChartOption: function (prms, needQueryData) {
return trendOption;
}
});
me.add(trendPanel);
var setExitChartData = function (store) {
var awardsExitIds = [];
var endDate = moment(store.getProxy().extraParams.endDate);
store.each(function (record) {
if (record.get('n_award_exit') != 0 && record.get('s_business_date') == endDate._i) {
awardsExitIds.push(record.get('n_award_exit'))
}
});
exitOption.title.text = me.chartItems[0].text;
console.log(awardsExitIds);
var legendData = [];
var gameNameMap = {};
for (var i = 0; i < awardsExitIds.length; i++) {
var rec = me.awardsExitStore.findRecord("code", awardsExitIds[i]);
var awardsExitIdsname = rec.get('name');
legendData.push(awardsExitIdsname);
gameNameMap[awardsExitIds[i]] = awardsExitIdsname;
}
exitOption.legend.data = legendData;
var dataMap = [];
for (var j = 0; j < store.data.items.length; j++) {
var item = store.data.items[j];
if(item.get('s_business_date') == endDate._i){
var exitId = item.get('n_award_exit');
var value = item.get(me.chartItems[0].field) || 0;
dataMap[exitId] = value;
}
}
var values = [];
for (var i = 0; i < awardsExitIds.length; i++) {
values.push({
value: dataMap[awardsExitIds[i]],
name: gameNameMap[awardsExitIds[i]],
sourceTarget: awardsExitIds[i]
} || 0);
}
exitOption.series[0].data = values;
me.down('#exit_chart_desc').setValue(endDate._i+'各项出口成本分布情况');
};
var setExitAwardChartData = function (store, sourceTarget) {
var awardsNameIds = [];
var endDate = moment(store.getProxy().extraParams.endDate);
store.each(function (record) {
if (record.get('n_award_name') != 0 && record.get("n_award_exit") == sourceTarget && record.get('s_business_date') == endDate._i) {
awardsNameIds.push(record.get('n_award_name'))
}
});
console.log(awardsNameIds);
var legendData1 = [];
var awardNameMap = {};
for (var i = 0; i < awardsNameIds.length; i++) {
var rec = me.awardsNameStore.findRecord("code", awardsNameIds[i]);
var awardsIdsname = rec.get('name');
legendData1.push(awardsIdsname);
awardNameMap[awardsNameIds[i]] = awardsIdsname;
}
exitAwardOption.legend.data = legendData1;
var rec = me.awardsExitStore.findRecord("code", sourceTarget);
var awardsExitIdsname = rec.get('name');
exitAwardOption.title.text = '['+awardsExitIdsname + ']' + me.chartItems[0].text;
var dataMap = [];
for (var j = 0; j < store.data.items.length; j++) {
var item = store.data.items[j];
if(item.get('n_award_exit') == sourceTarget && item.get('s_business_date') == endDate._i){
var awardsId = item.get('n_award_name');
dataMap[awardsId] = item;
}
}
var values = [];
for (var i = 0; i < awardsNameIds.length; i++) {
var exitID = dataMap[awardsNameIds[i]];
values.push({
value: exitID.get(me.chartItems[0].field),
name: awardNameMap[exitID.get('n_award_name')],
sourceTarget:sourceTarget,
awardsTarget:awardsNameIds[i]
} || 0);
}
exitAwardOption.series[0].data = values;
me.down('#exit_awards_desc').setValue(endDate._i + '['+awardsExitIdsname + ']' + '奖品分布');
};
var setListChartData = function (store, sourceTarget,awardsTarget) {
var awardsExitIds = sourceTarget;
var legendData = [];
var gameNameMap = {};
var rec = me.awardsExitStore.findRecord("code", awardsExitIds);
var awardsExitIdsname = rec.get('name');
legendData.push(awardsExitIdsname);
gameNameMap[awardsExitIds] = awardsExitIdsname;
trendOption.legend.data = legendData;
var rec1 = me.awardsNameStore.findRecord("code", awardsTarget);
var awardsIdsname = rec1.get('name');
trendOption.title.text = me.chartItems[0].text;
var startDate = moment(store.getProxy().extraParams.startDate);
var endDate = moment(store.getProxy().extraParams.endDate);
if (me.addEndDate) {
endDate.add(me.addEndDate, 'days')
}
var dataMap = {}, axisData = [];
for (var j = 0; j < store.data.items.length; j++) {
var item = store.data.items[j];
if(item.get('n_award_exit') == awardsExitIds && item.get('n_award_name') == awardsTarget){
var day = item.get('s_business_date');
var value = item.get(me.chartItems[0].field) || 0;
dataMap[day] = value;
}
}
var values = [];
for (var date = moment(startDate); date.isSameOrBefore(endDate, 'day'); date.add(1, 'days')) {
var day = date.format('YYYY-MM-DD');
axisData.push(day);
values.push(dataMap[day] || 0);
}
console.log(values);
trendOption.series[0].data = values;
trendOption.xAxis[0].data = axisData;
me.down('#exit_list_desc').setValue('['+awardsExitIdsname+'-'+awardsIdsname+']'+'趋势变化情况');
};
me.exitStore.on('load', function (store, records) {
setExitChartData(store);
setListChartData(store, sourceTarget,awardsTarget);
refreshChart();
});
me.exitAwardStore.on('load', function (store, records) {
setExitAwardChartData(store, sourceTarget);
refreshChart();
});
}
})
;
Chart3.js
Ext.define('Admin.view.gameAwardsCost.Chart3', {
extend: 'Ext.panel.Panel',
xtype: 'gameAwardsCostChart3',
closable: false,
layout: 'column',
initComponent: function () {
this.callParent(arguments);
var me = this;
var refreshChart = function (selector) {
var charts = me.query(selector || '[xtype=echarts]');
charts.forEach(function (chart) {
chart.fireEvent("reload", null, false);
chart.fireEvent("afterLayout", chart, null, null);
});
};
var sourceTarget = 0;
var awardsTarget = 81;
var awardsOption = {
title: {
//text: me.text,
//subtext: '纯属虚构',
top: 20,
left: 20,
x: 'left'
},
tooltip: {
trigger: 'item',
formatter: "{b} : {c} ({d}%)"
},
legend: {
type: 'scroll',
orient: 'vertical',
right: 10,
top: 100,
data: []
},
toolbox: {
show: true,
feature: {
mark: {show: true},
dataView: {show: true, readOnly: false},
magicType: {
show: true,
type: ['pie', 'funnel']
},
restore: {show: true},
saveAsImage: {show: true}
}
},
calculable: true,
series: [
{
name: me.chartItems.text,
type: 'pie',
radius: [30, 110],
center: ['40%', '50%'],
roseType: 'area',
data: []
}
]
};
var exitPanel = Ext.create({
itemId: 'exit_chart',
xtype: 'echarts',
border: false,
columnWidth: 0.45,
height: 400,
echartsData: awardsOption,
bbar: [{
xtype: 'displayfield', width: '100%', style: {textAlign: 'center'},
itemId: 'awards_desc', value: '各奖品成本分布情况'
}],
getChartOption: function (prms, needQueryData) {
return awardsOption;
},
echartsListeners: {
click: function (params) {
if(params.data && params.data.awardsTarget) {
setAwardsExitChartData(me.exitAwardStore, params.data.awardsTarget);
setListChartData(me.awardStore,sourceTarget, params.data.awardsTarget);
refreshChart('#award_chart');
refreshChart('#trend_chart');
me.datastore.chartAwardsId = params.data.awardsTarget;
}
}
}
});
me.add(exitPanel);
var exitAwardOption = {
title: {
//text: me.text,
//subtext: '纯属虚构',
top: 20,
left: 20,
x: 'left'
},
tooltip: {
trigger: 'item',
formatter: "{b}: {c} ({d}%)"
},
legend: {
type: 'scroll',
orient: 'vertical',
right: 10,
top: 10,
data: []
},
series: [
{
//name: '访问来源',
type: 'pie',
barWidth: '33%',
center: ['35%', '50%'],
radius: ['40%', '60%'],
avoidLabelOverlap: false,
label: {
normal: {
show: false,
position: 'center'
},
emphasis: {
show: true,
textStyle: {
fontSize: '30',
fontWeight: 'bold'
}
}
},
labelLine: {
normal: {
show: false
}
},
data: []
}
]
};
var exitAwardPanel = Ext.create({
itemId: 'award_chart',
xtype: 'echarts',
border: false,
columnWidth: 0.21,
height: 400,
echartsData: exitAwardOption,
bbar: [{
xtype: 'displayfield', width: '100%', style: {textAlign: 'center'},
itemId: 'awards_exit_desc', value: '出口分布'
}],
getChartOption: function (prms, needQueryData) {
return exitAwardOption;
}
,
echartsListeners: {
click: function (params) {
if(params.data && params.data.sourceTarget) {
setListChartData(me.exitAwardStore, params.data.sourceTarget,params.data.awardsTarget);
refreshChart('#trend_chart');
me.datastore.chartAwardsExitId = params.data.sourceTarget;
}
}
}
});
me.add(exitAwardPanel);
var trendOption = {
title: {
//text: me.chartTitle,
top: 20,
left: 20,
x: 'left'
},
legend: {
data: []
},
tooltip: {
trigger: 'axis',
axisPointer: { // 坐标轴指示器，坐标轴触发有效
type: 'shadow' // 默认为直线，可选为：'line' | 'shadow'
}
},
grid: {
left: '3%',
right: '4%',
bottom: '3%',
containLabel: true
},
xAxis: [
{
type: 'category',
data: []
}
],
yAxis: [
{
type: 'value'
}
],
series: [
{
type: 'line',
barWidth: '60%',
data: [],
label: {
normal: {
show: false,
position: 'top'
}
}
}
]
};
var trendPanel = Ext.create({
xtype: 'echarts',
itemId: 'trend_chart',
width: '90%',
columnWidth: 0.33,
height: 400,
border: false,
echartsData: trendOption,
bbar: [{
xtype: 'displayfield', width: '100%', style: {textAlign: 'center'},
itemId: 'awards_list_desc', value: '趋势变化情况'
}],
getChartOption: function (prms, needQueryData) {
return trendOption;
}
});
me.add(trendPanel);
var setAwardChartData = function (store) {
var awardsNameIds = [];
var endDate = moment(store.getProxy().extraParams.endDate);
store.each(function (record) {
if (record.get('n_award_name') != 0 && record.get('s_business_date') == endDate._i) {
awardsNameIds.push(record.get('n_award_name'))
}
});
awardsOption.title.text = me.chartItems[0].text;
console.log(awardsNameIds);
var legendData1 = [];
var awardNameMap = {};
for (var i = 0; i < awardsNameIds.length; i++) {
var rec = me.awardsNameStore.findRecord("code", awardsNameIds[i]);
var awardsIdsname = rec.get('name');
legendData1.push(awardsIdsname);
awardNameMap[awardsNameIds[i]] = awardsIdsname;
}
awardsOption.legend.data = legendData1;
var dataMap = [];
for (var j = 0; j < store.data.items.length; j++) {
var item = store.data.items[j];
if(item.get('s_business_date') == endDate._i){
var awardsId = item.get('n_award_name');
var value = item.get(me.chartItems[0].field) || 0;
dataMap[awardsId] = value;
}
}
var values = [];
for (var i = 0; i < awardsNameIds.length; i++) {
values.push({
value: dataMap[awardsNameIds[i]],
name: awardNameMap[awardsNameIds[i]],
awardsTarget:awardsNameIds[i]
} || 0);
}
awardsOption.series[0].data = values;
me.down('#awards_desc').setValue(endDate._i+'各奖品成本分布情况');
};
var setAwardsExitChartData = function (store, awardsTarget) {
var awardsExitIds = [];
var endDate = moment(store.getProxy().extraParams.endDate);
store.each(function (record) {
if (record.get('n_award_exit') != 0 && record.get('n_award_name') == awardsTarget && record.get('s_business_date') == endDate._i) {
awardsExitIds.push(record.get('n_award_exit'))
}
});
console.log(awardsExitIds);
var legendData = [];
var gameNameMap = {};
for (var i = 0; i < awardsExitIds.length; i++) {
var rec = me.awardsExitStore.findRecord("code", awardsExitIds[i]);
var awardsExitIdsname = rec.get('name');
legendData.push(awardsExitIdsname);
gameNameMap[awardsExitIds[i]] = awardsExitIdsname;
}
exitAwardOption.legend.data = legendData;
var rec = me.awardsNameStore.findRecord("code", awardsTarget);
var awardsIdsname = rec.get('name');
exitAwardOption.title.text = '['+awardsIdsname+']'+ me.chartItems[0].text;
var dataMap = [];
for (var j = 0; j < store.data.items.length; j++) {
var item = store.data.items[j];
if(item.get('n_award_name') == awardsTarget && item.get('s_business_date') == endDate._i){
var exitId = item.get('n_award_exit');
var value = item.get(me.chartItems[0].field) || 0;
dataMap[exitId] = value;
}
}
var values = [];
for (var i = 0; i < awardsExitIds.length; i++) {
values.push({
value: dataMap[awardsExitIds[i]],
name: gameNameMap[awardsExitIds[i]],
awardsTarget:awardsTarget,
sourceTarget: [awardsExitIds[i]]
} || 0);
}
exitAwardOption.series[0].data = values;
me.down('#awards_exit_desc').setValue(endDate._i+'['+awardsIdsname+']'+'出口分布');
};
var setListChartData = function (store, sourceTarget,awardsTarget) {
var awardsExitIds = sourceTarget;
var legendData = [];
var gameNameMap = {};
var rec = me.awardsExitStore.findRecord("code", awardsExitIds);
var awardsExitIdsname = rec.get('name');
legendData.push(awardsExitIdsname);
gameNameMap[awardsExitIds] = awardsExitIdsname;
trendOption.legend.data = legendData;
var rec1 = me.awardsNameStore.findRecord("code", awardsTarget);
var awardsIdsname = rec1.get('name');
trendOption.title.text =me.chartItems[0].text;
var startDate = moment(store.getProxy().extraParams.startDate);
var endDate = moment(store.getProxy().extraParams.endDate);
if (me.addEndDate) {
endDate.add(me.addEndDate, 'days')
}
var dataMap = {}, axisData = [];
for (var j = 0; j < store.data.items.length; j++) {
var item = store.data.items[j];
if(item.get('n_award_exit') == awardsExitIds && item.get('n_award_name') == awardsTarget){
var day = item.get('s_business_date');
var value = item.get(me.chartItems[0].field) || 0;
dataMap[day] = value;
}
}
var values = [];
for (var date = moment(startDate); date.isSameOrBefore(endDate, 'day'); date.add(1, 'days')) {
var day = date.format('YYYY-MM-DD');
axisData.push(day);
values.push(dataMap[day] || 0);
}
console.log(values);
trendOption.series[0].data = values;
trendOption.xAxis[0].data = axisData;
me.down('#awards_list_desc').setValue('['+awardsIdsname+'-'+awardsExitIdsname+']'+'趋势变化情况');
};
me.awardStore.on('load', function (store, records) {
setAwardChartData(store);
setListChartData(store, sourceTarget,awardsTarget);
refreshChart();
});
me.exitAwardStore.on('load', function (store, records) {
setAwardsExitChartData(store, awardsTarget);
refreshChart();
});
}
})
;
四、后台接口
说明：数据源分为4种情况
一、where获取前端参数，过滤所需数据，按s_business_date和n_award_exit汇总
二、where中忽略奖品出口、奖品名称=0，过滤所需数据，按s_business_date和n_award_exit汇总
三、where中忽略奖品名称、奖品出口=0，过滤所需数据，按s_business_date和n_award_name汇总
四、where中忽略奖品出口、奖品名称，过滤所需数据，按s_business_date、n_award_exit和n_award_name汇总
环比次日、环比上周：compare,获取今天的，昨天的，一周前的数据进行比较（详见GameAwardsCostServiceImpl.java）
数据导出，可用前端datastore传参数，用于后台判断（详见GameAwardsCostExport.java）
位置：
GameAwardsCostController.java
package com.jdd.bp.channel.biz.controller;
import com.alibaba.fastjson.JSONObject;
import com.jdd.bp.channel.biz.service.GameAwardsCostService;
import com.jdd.bp.channel.common.model.dataexport.GameAwardsCostExport;
import com.jdd.fm.core.model.ExtModel;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletRequest;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* Created by lidingzhou on 2019/04/09
* 游戏实际成本
*/
@RestController
@RequestMapping("/report/private/gameAwardsCost")
public class GameAwardsCostController {
@Autowired
GameAwardsCostService gameAwardsCostService;
@RequestMapping("list")
public Object list(@RequestBody JSONObject jsonParam) {
ExtModel result = gameAwardsCostService.gameAwardsCostList(jsonParam.getJSONObject("data"), "0");
return result;
}
@RequestMapping("listExit")
public Object listExit(@RequestBody JSONObject jsonParam) {
ExtModel result = gameAwardsCostService.gameAwardsCostList(jsonParam.getJSONObject("data"), "1");
return result;
}
@RequestMapping("listAward")
public Object listAward(@RequestBody JSONObject jsonParam) {
ExtModel result = gameAwardsCostService.gameAwardsCostList(jsonParam.getJSONObject("data"), "2");
return result;
}
@RequestMapping("listExitAward")
public Object listExitAward(@RequestBody JSONObject jsonParam) {
ExtModel result = gameAwardsCostService.gameAwardsCostList(jsonParam.getJSONObject("data"), "3");
return result;
}
@RequestMapping("getCompareData")
public Object getCompareData(@RequestBody JSONObject jsonParam) {
ExtModel result = new ExtModel();
JSONObject data = jsonParam.getJSONObject("data");
List<Map<String, Object>> allList = new ArrayList<>();
Map<String, Object> list = gameAwardsCostService.getCompareData(data, "0");
if (list != null) allList.add(list);
result.setData(allList);
return result;
}
/**
* 导出excel
*/
@RequestMapping("downloadAllList")
@ResponseBody
public Object downloadAllDataList(@RequestBody JSONObject jso, HttpServletRequest req) {
String fullExcel = "";
try {
GameAwardsCostExport gameAwardsCostExport = new GameAwardsCostExport(jso);
gameAwardsCostExport.setBasePath(req.getServletContext().getRealPath("/"));
fullExcel = gameAwardsCostExport.writeExcel();
} catch (Exception e) {
e.printStackTrace();
}
return fullExcel;
}
}
GameAwardsCostService.java
package com.jdd.bp.channel.biz.service;
import com.alibaba.fastjson.JSONObject;
import com.jdd.fm.core.model.ExtModel;
import java.util.Map;
public interface GameAwardsCostService {
ExtModel gameAwardsCostList(JSONObject jsonParam, String key);
Map<String, Object> getCompareData(JSONObject data, String key);
}
GameAwardsCostServiceImpl.java
package com.jdd.bp.channel.biz.service.impl;
import com.alibaba.fastjson.JSONObject;
import com.jdd.bp.channel.biz.dao.GameAwardsCostDao;
import com.jdd.bp.channel.biz.service.DictService;
import com.jdd.bp.channel.biz.service.GameAwardsCostService;
import com.jdd.bp.channel.common.commons.utils.DateUtils;
import com.jdd.fm.core.db.ds.DataSource;
import com.jdd.fm.core.model.ExtModel;
import org.apache.commons.collections.map.HashedMap;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
import java.util.List;
import java.util.Map;
@Service("gameAwardsCostService")
public class GameAwardsCostServiceImpl implements GameAwardsCostService {
@Autowired
GameAwardsCostDao gameAwardsCostDao;
@Autowired
DictService dictService;
@DataSource(name = DataSource.DATA_SOURCE_READ)
@Transactional(readOnly = true)
@Override
public ExtModel gameAwardsCostList(JSONObject jsonParam, String key) {
ExtModel result = new ExtModel();
result.setData(gameAwardsCostDao.gameAwardsCostList(jsonParam,key));
return result;
}
@Override
public Map<String, Object> getCompareData(JSONObject data, String key) {
Map<String, Object> result = new HashedMap();
String endDate = data.getString("endDate");
data.put("startDate", endDate);
data.put("endDate", endDate);
// 今日
List<Map<String, Object>> todayListtodayList = gameAwardsCostDao.gameAwardsCostList(data,key);
// 昨日
String yesterday = DateUtils.editDays(endDate, -1);
JSONObject yesterdayData = JSONObject.parseObject(data.toString());
yesterdayData.put("startDate", yesterday);
yesterdayData.put("endDate", yesterday);
List<Map<String, Object>> yesterdayList = gameAwardsCostDao.gameAwardsCostList(yesterdayData,key);
// 上周
String lastweekDay = DateUtils.editDays(endDate, -7);
JSONObject lastkweekData = JSONObject.parseObject(data.toString());
lastkweekData.put("startDate", lastweekDay);
lastkweekData.put("endDate", lastweekDay);
List<Map<String, Object>> lastweekList = gameAwardsCostDao.gameAwardsCostList(lastkweekData,key);
if (todayListtodayList != null && todayListtodayList.size() > 0) {
Map<String, Object> todayMap = todayListtodayList.get(0);
Map<String, Object> yesterdayMap = null;
Map<String, Object> lastweekMap = null;
// 对比昨日
if (yesterdayList != null && yesterdayList.size() > 0) {
yesterdayMap = yesterdayList.get(0);
}
// 对比上周
if (lastweekList != null && lastweekList.size() > 0) {
lastweekMap = lastweekList.get(0);
}
for (String key1 : todayMap.keySet()) {
if ("n_award_normal".equals(key1) || "n_award_count".equals(key1)
|| "n_awards".equals(key1) || "n_award_normal_avg".equals(key1)
|| "n_awards_avg".equals(key1)) { // 忽略此字段 其他字段计算增长比例
countRate(result, key1, todayMap, yesterdayMap, 1);
countRate(result, key1, todayMap, lastweekMap, 2);
}
}
} else {
return null;
}
return result;
}
/**
* 计算比例
*/
private void countRate(Map<String, Object> result, String key, Map<String, Object> todayAll, Map<String, Object> compareMap, int type) {
if (todayAll.get(key) != null) {
result.put(key, todayAll.get(key));
BigDecimal today = new BigDecimal(String.valueOf(todayAll.get(key)));
if (compareMap != null && compareMap.get(key) != null) {
BigDecimal compare = new BigDecimal(String.valueOf(compareMap.get(key)));
String rateRate;
if (new BigDecimal(0).compareTo(compare) == 0) {
rateRate = "∞";
} else {
BigDecimal rate = new BigDecimal(100).multiply(today.subtract(compare)).divide(compare, 2, BigDecimal.ROUND_HALF_UP);
rateRate = rate.toString();
}
if (type == 1) { // 日
result.put("day_rate_" + key, rateRate);
} else if (type == 2) { // 周
result.put("week_rate_" + key, rateRate);
}
}
}
}
}
GameAwardsCostDao.java
package com.jdd.bp.channel.biz.dao;
import com.alibaba.fastjson.JSONObject;
import java.util.List;
import java.util.Map;
public interface GameAwardsCostDao {
List<Map<String, Object>> gameAwardsCostList(JSONObject data, String key);
}
GameAwardsCostDaoImpl.java
package com.jdd.bp.channel.biz.dao.impl;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jdd.bp.channel.biz.dao.GameAwardsCostDao;
import com.jdd.bp.channel.biz.service.DictService;
import com.jdd.bp.channel.common.commons.utils.DateUtils;
import com.jdd.fm.core.db.BaseDao;
import org.apache.commons.collections.map.HashedMap;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@Repository("gameAwardsCostDao")
public class GameAwardsCostDaoImpl extends BaseDao implements GameAwardsCostDao {
@Autowired
DictService dictService;
@Override
public List<Map<String, Object>> gameAwardsCostList(JSONObject data, String key) {
List<Map<String, Object>> result = new ArrayList<>();
StringBuilder sql = new StringBuilder();
sql.append(getSql(data, key));
sql.append(" order by s_business_date desc");
SQLQuery query = getSQLQuery(sql.toString());
query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
List<Map<String, Object>> list = query.list();
result.addAll(list);
// 补充游戏名称和渠道名称
String userAtr = "";
if ("1".equalsIgnoreCase(data.getString("userAttr"))) {
userAtr = "正常用户";
} else if ("2".equalsIgnoreCase(data.getString("userAttr"))) {
userAtr = "非正常用户";
} else {
userAtr = "全部用户";
}
String awardsStatus = "";
if ("1".equalsIgnoreCase(data.getString("userStatus"))) {
awardsStatus = "遗留";
} else if ("2".equalsIgnoreCase(data.getString("userStatus"))) {
awardsStatus = "兑出";
} else {
awardsStatus = "全部";
}
Map<String, String> getAwardsExitMap = getAwardsExitMap();
Map<String, String> getAwardsNameMap = getAwardsNameMap();
List<Map<String, Object>> channelList = dictService.getChannelListWithAll(true);
for (Map<String, Object> map : result) {
if (null != map.get("n_award_exit")) {
String awardExitId = String.valueOf(map.get("n_award_exit"));
String awardExitName = getAwardsExitMap.get(awardExitId);
map.put("s_award_exit", awardExitName);
}
if (null != map.get("n_award_name")) {
String awardNameId = String.valueOf(map.get("n_award_name"));
String awardName = getAwardsNameMap.get(awardNameId);
map.put("s_award_name", awardName);
}
if (null != map.get("n_channel_id")) {
String channelId = String.valueOf(map.get("n_channel_id"));
String channelName = getValueName(channelList, channelId);
map.put("n_channel_name", channelName);
}
if (null != data.getString("userAttr")) {
map.put("s_user_normal_flag", userAtr);
}
if (null != data.getString("userStatus")) {
map.put("s_redemption_status", awardsStatus);
}
}
return result;
}
private Map<String, String> getAwardsExitMap() {
Map<String, String> awardsExitMap = new HashedMap();
List<Map<String, Object>> awardsExitList = dictService.getAwardsExitListAll();
for (Map<String, Object> map : awardsExitList) {
String code = String.valueOf(map.get("code"));
String name = String.valueOf(map.get("name"));
awardsExitMap.put(code, name);
}
return awardsExitMap;
}
private Map<String, String> getAwardsNameMap() {
Map<String, String> awardsNameMap = new HashedMap();
List<Map<String, Object>> awardsNameList = dictService.getAwardsNameListAll();
for (Map<String, Object> map : awardsNameList) {
String code = String.valueOf(map.get("code"));
String name = String.valueOf(map.get("name"));
awardsNameMap.put(code, name);
}
return awardsNameMap;
}
/**
* 获取渠道或名称
*/
private String getValueName(List<Map<String, Object>> dictList, String code) {
for (Map<String, Object> map : dictList) {
String listCode = String.valueOf(map.get("code"));
String name = String.valueOf(map.get("name"));
if (listCode.equals(code)) {
return name;
}
}
return code;
}
private String getSql(JSONObject data, String key) {
StringBuilder sql = new StringBuilder();
String val = data.getString("userAttr");
if (key.contains("0")) {
sql.append("select s_business_date,n_channel_id,n_award_exit\n" +
",n_award_name,n_redemption_status," +
"case when '"+val+"'= 0 then 0 else n_user_normal_flag end as n_user_normal_flag,sum(n_award_normal) as n_award_normal" +
",sum(n_award_count) as n_award_count,sum(n_awards) as n_awards\n" +
",round(sum(n_award_normal)/sum(n_award_count),2) as n_award_normal_avg\n" +
",round(sum(n_awards)/sum(n_award_count),2) as n_awards_avg\n" +
"from game_awards_cost");
sql.append(where(data, key));
sql.append(" group by s_business_date,n_award_exit");
}
if (key.contains("1")) {
sql.append("select s_business_date,n_channel_id,n_award_exit\n" +
",n_award_name,n_redemption_status" +
",case when '"+val+"'= 0 then 0 else n_user_normal_flag end as n_user_normal_flag,sum(n_award_normal) as n_award_normal" +
",sum(n_award_count) as n_award_count,sum(n_awards) as n_awards\n" +
",round(sum(n_award_normal)/sum(n_award_count),2) as n_award_normal_avg\n" +
",round(sum(n_awards)/sum(n_award_count),2) as n_awards_avg\n" +
"from game_awards_cost");
sql.append(where(data, key));
sql.append(" group by s_business_date,n_award_exit");
}
if (key.contains("2")) {
sql.append("select s_business_date,n_channel_id,n_award_exit\n" +
",n_award_name,n_redemption_status" +
",case when '"+val+"'= 0 then 0 else n_user_normal_flag end as n_user_normal_flag,sum(n_award_normal) as n_award_normal" +
",sum(n_award_count) as n_award_count,sum(n_awards) as n_awards\n" +
",round(sum(n_award_normal)/sum(n_award_count),2) as n_award_normal_avg\n" +
",round(sum(n_awards)/sum(n_award_count),2) as n_awards_avg\n" +
"from game_awards_cost");
sql.append(where(data, key));
sql.append(" group by s_business_date,n_award_name");
}
if (key.contains("3")) {
sql.append("select s_business_date,n_channel_id,n_award_exit\n" +
",n_award_name,n_redemption_status," +
"case when '"+val+"'= 0 then 0 else n_user_normal_flag end as n_user_normal_flag,sum(n_award_normal) as n_award_normal" +
",sum(n_award_count) as n_award_count,sum(n_awards) as n_awards\n" +
",round(sum(n_award_normal)/sum(n_award_count),2) as n_award_normal_avg\n" +
",round(sum(n_awards)/sum(n_award_count),2) as n_awards_avg\n" +
"from game_awards_cost");
sql.append(where(data, key));
sql.append(" group by s_business_date,n_award_exit,n_award_name");
}
return sql.toString();
}
private String where(JSONObject data, String key) {
StringBuilder sql = new StringBuilder();
sql.append(" where s_business_date >= '" + data.getString("startDate") + "'");
sql.append(" and s_business_date < '" + DateUtils.editDays(data.getString("endDate"), 1) + "' ");
if (StringUtils.isNotEmpty(data.getString("userAttr"))) { // 用户状态
if (!"0".equalsIgnoreCase(data.getString("userAttr"))) {
sql.append(" and n_user_normal_flag =").append(data.getString("userAttr"));
}
}
if (key.contains("0")) {
if (null != data.getJSONArray("awardExitId") && data.getJSONArray("awardExitId").size() > 0) { // 游戏
sql.append(" and n_award_exit in (");
JSONArray array = data.getJSONArray("awardExitId");
for (int i = 0; i < array.size(); i++) {
String val = array.get(i).toString();
sql.append("'" + val + "'");
if (i + 1 < array.size()) {
sql.append(",");
}
}
sql.append(" ) ");
}
sql.append(" and n_award_name = " + data.getString("awardNameId"));
}
if (key.contains("1")) {
sql.append(" and n_award_name = 0 ");
}
if (key.contains("2")) {
sql.append(" and n_award_exit = 0");
}
if(key.contains("3")) {
if("1".equals(data.getString("chartType"))){
if(StringUtils.isEmpty(data.getString("chartExitAwardsId"))) {
sql.append(" and n_award_exit = ").append(data.getString("chartExitId"));
}
if(StringUtils.isNotEmpty(data.getString("chartExitAwardsId"))) {
sql.append(" and n_award_exit = ").append(data.getString("chartExitId").replace("[","").replace("]",""));
sql.append(" and n_award_name = ").append(data.getString("chartExitAwardsId"));
}
}
if("2".equals(data.getString("chartType"))){
if(StringUtils.isEmpty(data.getString("chartAwardsExitId"))){
sql.append(" and n_award_name = ").append(data.getString("chartAwardsId"));
}
if(StringUtils.isNotEmpty(data.getString("chartAwardsExitId"))){
sql.append(" and n_award_name = ").append(data.getString("chartAwardsId"));
sql.append(" and n_award_exit = ").append(data.getString("chartAwardsExitId").replace("[","").replace("]",""));
}
}
}
if (StringUtils.isNotEmpty(data.getString("entrance"))) {
sql.append(" and n_channel_id = " + data.getString("entrance"));
}
if (StringUtils.isNotEmpty(data.getString("userStatus"))) {
sql.append(" and n_redemption_status = " + data.getString("userStatus"));
}
return sql.toString();
}
}
GameAwardsCostExport.java
package com.jdd.bp.channel.common.model.dataexport;
import com.alibaba.fastjson.JSONObject;
import com.jdd.bp.channel.biz.service.GameAwardsCostService;
import com.jdd.bp.channel.common.commons.utils.SpringGetBeanUtil;
import com.jdd.fm.core.model.ExtModel;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
public class GameAwardsCostExport extends MultiSheetDataExport {
public JSONObject queryData;
public GameAwardsCostExport(JSONObject queryData) {
this.queryData = queryData;
}
private static final Logger logger = LoggerFactory.getLogger(GameAwardsCostExport.class);
@Override
public String handleSpecialColumnVal(String columnName, String value) {
return value;
}
@Override
public String writeExcel() {
int sheetCount = 3;
String fullExcel = initWrite(titles(), columns(), defaultValues(), sheetCount, sheetNames());
//写标题
HSSFWorkbook wb = generateExportGridTitle();
GameAwardsCostService gameAwardsCostService = (GameAwardsCostService) SpringGetBeanUtil.getBean("gameAwardsCostService");
try {
LinkedList<List<Map<String, Object>>> resultsArray = new LinkedList<>();
//整体趋势
ExtModel res = gameAwardsCostService.gameAwardsCostList(queryData, "0");
List<Map<String, Object>> List = (List<Map<String, Object>>) res.getData();
resultsArray.add(List);
JSONObject exitQuery = JSONObject.parseObject(queryData.toJSONString());
exitQuery.remove("chartAwardsId");
exitQuery.remove("chartAwardsExitId");
exitQuery.put("chartType","1");
JSONObject awardQuery = JSONObject.parseObject(queryData.toJSONString());
awardQuery.remove("chartExitId");
awardQuery.remove("chartExitAwardsId");
awardQuery.put("chartType","2");
//出口分布
if(StringUtils.isEmpty(exitQuery.getString("chartExitId")) && StringUtils.isEmpty(exitQuery.getString("chartExitAwardsId"))){
ExtModel res1 = gameAwardsCostService.gameAwardsCostList(exitQuery, "1");
List<Map<String, Object>> exitList = (List<Map<String, Object>>) res1.getData();
resultsArray.add(exitList);
}else{
ExtModel res1 = gameAwardsCostService.gameAwardsCostList(exitQuery, "3");
List<Map<String, Object>> exitList = (List<Map<String, Object>>) res1.getData();
resultsArray.add(exitList);
}
//奖品分布
if(StringUtils.isEmpty(awardQuery.getString("chartAwardsId")) && StringUtils.isEmpty(awardQuery.getString("chartAwardsExitId"))){
ExtModel res2 = gameAwardsCostService.gameAwardsCostList(awardQuery, "2");
List<Map<String, Object>> awardsList = (List<Map<String, Object>>) res2.getData();
resultsArray.add(awardsList);
}else{
ExtModel res2 = gameAwardsCostService.gameAwardsCostList(awardQuery, "3");
List<Map<String, Object>> awardsList = (List<Map<String, Object>>) res2.getData();
resultsArray.add(awardsList);
}
generateExportGridData(wb, resultsArray);
} catch (Exception e) {
e.printStackTrace();
}
try {
wb.write(this.getOts());
this.getOts().close();
} catch (Exception e) {
logger.error("流关闭异常");
}
String fullUrl = uploadFileToFdfs(fullExcel);
return fullUrl;
}
private LinkedList<String> sheetNames() {
LinkedList<String> sheetNames = new LinkedList<String>();
sheetNames.add("成本整体趋势");
sheetNames.add("成本--出口分布情况");
sheetNames.add("成本--奖品项分布情况");
return sheetNames;
}
private LinkedList<LinkedList<String>> titles() {
// 成本整体
LinkedList<LinkedList<String>> arrayList = new LinkedList<LinkedList<String>>();
LinkedList<String> UserList = new LinkedList<String>();
UserList.add("日期");
UserList.add("渠道名称");
UserList.add("奖品出口");
UserList.add("奖品名称");
UserList.add("兑出状态");
UserList.add("用户属性");
UserList.add("奖品金额");
UserList.add("奖品人数");
UserList.add("奖品笔数");
UserList.add("人均奖品笔数");
UserList.add("人均奖品金额");
arrayList.add(UserList);
LinkedList<String> exitUserList = new LinkedList<String>();
exitUserList.add("日期");
exitUserList.add("渠道名称");
exitUserList.add("奖品出口");
exitUserList.add("奖品名称");
exitUserList.add("兑出状态");
exitUserList.add("用户属性");
exitUserList.add("奖品金额");
exitUserList.add("奖品人数");
exitUserList.add("奖品笔数");
exitUserList.add("人均奖品笔数");
exitUserList.add("人均奖品金额");
arrayList.add(exitUserList);
LinkedList<String> awardsUserList = new LinkedList<String>();
awardsUserList.add("日期");
awardsUserList.add("渠道名称");
awardsUserList.add("奖品名称");
awardsUserList.add("奖品出口");
awardsUserList.add("兑出状态");
awardsUserList.add("用户属性");
awardsUserList.add("奖品金额");
awardsUserList.add("奖品人数");
awardsUserList.add("奖品笔数");
awardsUserList.add("人均奖品笔数");
awardsUserList.add("人均奖品金额");
arrayList.add(awardsUserList);
return arrayList;
}
private LinkedList<LinkedList<String>> columns() {
// 全部用户
LinkedList<LinkedList<String>> arrayList = new LinkedList<>();
LinkedList<String> columns = new LinkedList<>();
columns.add("s_business_date");
columns.add("n_channel_name");
columns.add("s_award_exit");
columns.add("s_award_name");
columns.add("s_redemption_status");
columns.add("s_user_normal_flag");
columns.add("n_award_normal");
columns.add("n_award_count");
columns.add("n_awards");
columns.add("n_award_normal_avg");
columns.add("n_awards_avg");
arrayList.add(columns);
LinkedList<String> exitColumns = new LinkedList<>();
exitColumns.add("s_business_date");
exitColumns.add("n_channel_name");
exitColumns.add("s_award_exit");
exitColumns.add("s_award_name");
exitColumns.add("s_redemption_status");
exitColumns.add("s_user_normal_flag");
exitColumns.add("n_award_normal");
exitColumns.add("n_award_count");
exitColumns.add("n_awards");
exitColumns.add("n_award_normal_avg");
exitColumns.add("n_awards_avg");
arrayList.add(exitColumns);
LinkedList<String> awardsColumns = new LinkedList<>();
awardsColumns.add("s_business_date");
awardsColumns.add("n_channel_name");
awardsColumns.add("s_award_name");
awardsColumns.add("s_award_exit");
awardsColumns.add("s_redemption_status");
awardsColumns.add("s_user_normal_flag");
awardsColumns.add("n_award_normal");
awardsColumns.add("n_award_count");
awardsColumns.add("n_awards");
awardsColumns.add("n_award_normal_avg");
awardsColumns.add("n_awards_avg");
arrayList.add(awardsColumns);
return arrayList;
}
private LinkedList<LinkedHashMap<String, String>> defaultValues() {
LinkedList<LinkedHashMap<String, String>> arrayList = new LinkedList<>();
LinkedHashMap<String, String> defaultVals = new LinkedHashMap<>();
defaultVals.put("s_business_date", "");
defaultVals.put("n_channel_name", "");
defaultVals.put("s_award_exit", "");
defaultVals.put("s_award_name", "");
defaultVals.put("s_redemption_status", "");
defaultVals.put("s_user_normal_flag", "");
defaultVals.put("n_award_normal", "0");
defaultVals.put("n_award_count", "0");
defaultVals.put("n_awards", "0");
defaultVals.put("n_award_normal_avg", "0");
defaultVals.put("n_awards_avg", "0");
arrayList.add(defaultVals);
LinkedHashMap<String, String> exitDefaultVals = new LinkedHashMap<>();
exitDefaultVals.put("s_business_date", "");
exitDefaultVals.put("n_channel_name", "");
exitDefaultVals.put("s_award_exit", "");
exitDefaultVals.put("s_award_name", "");
exitDefaultVals.put("s_redemption_status", "");
exitDefaultVals.put("s_user_normal_flag", "");
exitDefaultVals.put("n_award_normal", "0");
exitDefaultVals.put("n_award_count", "0");
exitDefaultVals.put("n_awards", "0");
exitDefaultVals.put("n_award_normal_avg", "0");
exitDefaultVals.put("n_awards_avg", "0");
arrayList.add(exitDefaultVals);
LinkedHashMap<String, String> awardsDefaultVals = new LinkedHashMap<>();
awardsDefaultVals.put("s_business_date", "");
awardsDefaultVals.put("n_channel_name", "");
awardsDefaultVals.put("s_award_name", "");
awardsDefaultVals.put("s_award_exit", "");
awardsDefaultVals.put("s_redemption_status", "");
awardsDefaultVals.put("s_user_normal_flag", "");
awardsDefaultVals.put("n_award_normal", "0");
awardsDefaultVals.put("n_award_count", "0");
awardsDefaultVals.put("n_awards", "0");
awardsDefaultVals.put("n_award_normal_avg", "0");
awardsDefaultVals.put("n_awards_avg", "0");
arrayList.add(awardsDefaultVals);
return arrayList;
}
}
五、效果展示
浙公网安备 33010602011771号