2021-01-11 17:29:31
正常:17:29:31 http-nio-8075-exec-5 INFO [standard] [SQL]SQL: SELECT
	if(isnull(parent_project_erp), agent.erp_code, agent.parent_project_erp) AS parentERP,
	1 as pCode,"全部" as pName,
	agent.NAME as agent_name,
	agent.erp_code as erpCode,
	area.NAME AS area_name,
	area.uuid AS area_id 
FROM
	`rims_basic_agent` agent
	LEFT JOIN rims_basic_area_agent area_agent ON agent.uuid = area_agent.agent_fk
	LEFT JOIN rims_basic_area area ON area.uuid = area_agent.area_fk 
	left join view_permission_agent_erpreport on view_permission_agent_erpreport.agent_erp_code=agent.erp_code
WHERE
	length( agent.erp_code ) != 0 and agent.parent_project_erp is null
	AND agent.state = "1" and agent.report_operate !=2
	AND area.pid = 0 
	 and view_permission_agent_erpreport.username='15076328933'

2021-01-11 17:29:32
正常:17:29:32 http-nio-8075-exec-5 INFO [standard] sql time: 1075 ms!

2021-01-11 17:29:32
正常:17:29:32 http-nio-8075-exec-5 INFO [standard] [SQL]SQL: SELECT
	1 as pCode,"全部" as pName,
	if(isnull(parent_project_erp), agent.erp_code, agent.parent_project_erp) AS parentERP,
	if(isnull(parent_project_erp), 1, 2) AS level,
	agent.NAME as agent_name,
	agent.erp_code as erpCode,
	area.NAME AS area_name,
	area.uuid AS area_id,
	(select count(parent_project_erp) from rims_basic_agent where rims_basic_agent.parent_project_erp= agent.erp_code and rims_basic_agent.state = "1" and rims_basic_agent.report_operate !=2) AS num 
FROM
	`rims_basic_agent` agent
	LEFT JOIN rims_basic_area_agent area_agent ON agent.uuid = area_agent.agent_fk
	LEFT JOIN rims_basic_area area ON area.uuid = area_agent.area_fk 
WHERE
	length( agent.erp_code ) != 0 
	AND agent.state = "1" and agent.report_operate !=2
	AND area.pid = 0 
	ORDER BY area_id asc,agent.erp_code asc 

2021-01-11 17:29:33
正常:17:29:33 http-nio-8075-exec-5 INFO [standard] sql time: 33 ms!

2021-01-11 17:29:39
正常:17:29:39 http-nio-8075-exec-9 INFO [standard] Start to execute report session f3332836-b407-4e03-9636-a5435bbe6591.

2021-01-11 17:29:39
正常:17:29:39 http-nio-8075-exec-9 INFO [standard] 开始计算报表

2021-01-11 17:29:39
正常:17:29:39 http-nio-8075-exec-9 INFO [standard] [SQL]SQL: SELECT
	1 as pCode,"全部" as pName,
	if(isnull(parent_project_erp), agent.erp_code, agent.parent_project_erp) AS parentERP,
	if(isnull(parent_project_erp), 1, 2) AS level,
	agent.NAME as agent_name,
	agent.erp_code as erpCode,
	area.NAME AS area_name,
	area.uuid AS area_id,
	operate_time,
if(operate_time>'2019-12-31',TIMESTAMPDIFF(month,operate_time,now()),'')
 as monthDiff, 
	(select count(parent_project_erp) from rims_basic_agent where rims_basic_agent.parent_project_erp= agent.erp_code and rims_basic_agent.state = "1" and rims_basic_agent.report_operate !=2) AS num
FROM
	`rims_basic_agent` agent
	LEFT JOIN rims_basic_area_agent area_agent ON agent.uuid = area_agent.agent_fk
	LEFT JOIN rims_basic_area area ON area.uuid = area_agent.area_fk 
WHERE
	length( agent.erp_code ) != 0 
     and agent.erp_code in ('1502001','1502001-1@','2102002')
	AND agent.state = "1" and agent.report_operate =1
	AND area.pid = 0 
	ORDER BY area_id asc,agent.erp_code asc 

2021-01-11 17:29:39
正常:17:29:39 http-nio-8075-exec-9 INFO [standard] sql time: 6 ms!

2021-01-11 17:29:39
正常:17:29:39 http-nio-8075-exec-9 INFO [standard] [SQL]SQL: select 
if(d.type=1,d.type,6) as type,
c.code as erpCode,
c.name as costCenterName,
subject_id,sum(d.amount) as amount,
-- sum(if(d.type!=1 and subject_id=100,d.amount,0)) as income_checkup,
-- sum(if(d.type!=1 and subject_id=7910,d.amount,0)) as income_settlement,
-- sum(if(d.type!=1 and subject_id=8000,d.amount,0)) as settlement,
-- sum(if(
-- =1 and subject_id=100,d.amount,0)) as budget_checkup,
-- sum(if(d.type=1 and subject_id=7910,d.amount,0)) as budget_settlement,
d.date as month

from data_cache d
LEFT JOIN budget_costcenter c ON c.code=d.code
WHERE code_type in(1)  and (d.subject_id  in (100,4100,8100,9700,7910,8000,12400,12500) or d.subject_id in (SELECT id FROM budget_subject WHERE parentID IN ( '4100', '4200' )  AND enable = 1))  and d.status=0
 and c.code in ('1502001','1502001-1@','2102002')
 and d.date between '2020-01' and '2020-03'

group by
d.code,if(d.type=1,d.type,6),d.subject_id,d.date

2021-01-11 17:29:39
正常:17:29:39 http-nio-8075-exec-9 INFO [standard] sql time: 19 ms!

2021-01-11 17:29:39
正常:17:29:39 http-nio-8075-exec-9 INFO [standard] [SQL]SQL: SELECT * FROM budget_subject WHERE parentID = '4100' OR parentID = '4200' AND enable = 1

2021-01-11 17:29:39
正常:17:29:39 http-nio-8075-exec-9 INFO [standard] sql time: 6 ms!

2021-01-11 17:29:39
正常:17:29:39 http-nio-8075-exec-9 INFO [standard] [SQL]SQL: SELECT
	* 
FROM
	budget_subject 
WHERE
	parentID IN ( '8100', '9000' ) 
	AND enable = 1 
	AND ID NOT IN ('8200','8300','8400','8500','8600','9000')

2021-01-11 17:29:39
正常:17:29:39 http-nio-8075-exec-9 INFO [standard] sql time: 5 ms!

2021-01-11 17:29:39
正常:17:29:39 http-nio-8075-exec-9 INFO [standard] [SQL]SQL: select * from 
(
( SELECT
* 
FROM
 (
 (
SELECT
 a.*,
 LEFT ( a.period, 4 ) AS YEAR,
 fnaBudgetERPlink.budgetIndex AS trueindex 
FROM
 (
SELECT
 str_to_date( concat( rims_basic_agent_expense.period, '-01' ), '%Y-%m-%d' ) AS expenseDate,
 fnaBudgetERPlink.budgetIndex AS budgetIndex,
 rims_basic_agent_expense.* 
FROM
 `rims_basic_agent_expense`
 LEFT JOIN fnaBudgetERPlink ON fnaBudgetERPlink.erpSequenceIndex = rims_basic_agent_expense.sequence  
 where period BETWEEN '2020-01' and '2020-03'
 ) a
 LEFT JOIN rims_basic_agent ON rims_basic_agent.erp_code = a.erp_code
 LEFT JOIN fnaBudgetERPlink ON fnaBudgetERPlink.erpSequenceIndex = a.sequence 
WHERE
 rims_basic_agent.state = "1" 
 AND fnaBudgetERPlink.budgetIndex NOT IN ( SELECT budgetindex FROM fnabudgetoalink WHERE budgetindex IS NOT NULL UNION SELECT DISTINCT sequence AS budgetindex FROM rims_basic_agent_part_service_expense where period BETWEEN '2020-01' and '2020-03') 
ORDER BY
 a.expenseDate 
 ) UNION
 (
SELECT
 a.*,
 LEFT ( a.period, 4 ) AS YEAR,
 fnaBudgetoalink.budgetIndex AS trueindex 
FROM
 (
SELECT
 str_to_date( concat( rims_basic_agent_oa_expense.period, '-01' ), '%Y-%m-%d' ) AS expenseDate,
 fnaBudgetoalink.budgetIndex AS budgetIndex,
 rims_basic_agent_oa_expense.* 
FROM
 `rims_basic_agent_oa_expense`
 LEFT JOIN fnaBudgetoalink ON fnaBudgetoalink.oaIndex = rims_basic_agent_oa_expense.sequence 
 where period BETWEEN '2020-01' and '2020-03'
  ) a
 LEFT JOIN rims_basic_agent ON rims_basic_agent.erp_code = a.erp_code
 LEFT JOIN fnaBudgetoalink ON fnaBudgetoalink.oaindex = a.sequence 
WHERE
 rims_basic_agent.state = "1" 
 AND fnaBudgetoalink.budgetIndex IS NOT NULL 
 AND fnaBudgetoalink.budgetIndex NOT IN ( SELECT DISTINCT sequence AS budgetindex FROM rims_basic_agent_part_service_expense where period BETWEEN '2020-01' and '2020-03') 
ORDER BY
 a.expenseDate 
 ) UNION
 (
SELECT
 a.*,
 LEFT ( a.period, 4 ) AS YEAR,
 a.sequence AS trueindex 
FROM
 (
SELECT
 str_to_date( concat( rims_basic_agent_part_service_expense.period, '-01' ), '%Y-%m-%d' ) AS expenseDate,
 rims_basic_agent_part_service_expense.sequence AS budgetIndex,
 rims_basic_agent_part_service_expense.* 
FROM
 `rims_basic_agent_part_service_expense` 
 where period BETWEEN '2020-01' and '2020-03'
 ) a
 LEFT JOIN rims_basic_agent ON rims_basic_agent.erp_code = a.erp_code 
WHERE
 rims_basic_agent.state = "1" 
 AND a.sequence IS NOT NULL 
ORDER BY
 a.expenseDate 
 ) 
 ) AS last 
WHERE
 ( last.period, last.erp_code, last.budgetIndex ) NOT IN (
SELECT
 res.budgetIndex,
 res.erp_code,
 res.period 
FROM
 (
SELECT
 a.*,
 LEFT ( a.period, 4 ) AS YEAR,
 a.sequence AS trueindex 
FROM
 (
SELECT
 str_to_date( concat( rims_basic_agent_part_fixed_expense.period, '-01' ), '%Y-%m-%d' ) AS expenseDate,
 rims_basic_agent_part_fixed_expense.sequence AS budgetIndex,
 rims_basic_agent_part_fixed_expense.* 
FROM
 `rims_basic_agent_part_fixed_expense` where period BETWEEN '2020-01' and '2020-03' UNION ALL
SELECT
 * 
FROM
 view_secondary_part_fixed_cost_reality 
 ) a
 LEFT JOIN rims_basic_agent ON rims_basic_agent.erp_code = a.erp_code 
WHERE
 rims_basic_agent.state = "1" 
 AND a.sequence IS NOT NULL 
ORDER BY
 a.expenseDate 
 ) res 
 ) 
 ) UNION
 (
SELECT
 a.*,
 LEFT ( a.period, 4 ) AS YEAR,
 a.sequence AS trueindex 
FROM
 (
SELECT
 str_to_date( concat( rims_basic_agent_part_fixed_expense.period, '-01' ), '%Y-%m-%d' ) AS expenseDate,
 rims_basic_agent_part_fixed_expense.sequence AS budgetIndex,
 rims_basic_agent_part_fixed_expense.* 
FROM
 `rims_basic_agent_part_fixed_expense` 
 where period BETWEEN '2020-01' and '2020-03'
 ) a
 LEFT JOIN rims_basic_agent ON rims_basic_agent.erp_code = a.erp_code 
WHERE
 rims_basic_agent.state = "1" 
 AND a.sequence IS NOT NULL 
ORDER BY
 a.expenseDate 
 )
 
)  as last
left join 

 (
  SELECT
     `agent`.`name` AS `agent_name`,
     `agent`.`state` AS `agent_state`,
   `agent`.`report_operate` AS `report_operate`,
    `agent`.`erp_code` AS `agent_erp_code`,
     `aa`.`area_fk` AS `area_id`,
     `area`.`name` AS `area_name`
  FROM ((`rims_basic_area_agent` `aa` join `rims_basic_agent` `agent`) join `rims_basic_area` `area`) where ((`aa`.`agent_fk` =    `agent`.`uuid`) and (`aa`.`area_fk` = `area`.`id`) and ( `aa`.`area_fk`<=34 or `aa`.`area_fk`=999) ) 
 ) as province on province.agent_erp_code = last.erp_code
 
 where 1=1 
 and agent_state = '1' and report_operate!=2

and province.area_id in (select id from rims_basic_area where pid = 0)
and period BETWEEN '2020-01' and '2020-03'
and (trueindex between 8200 and 9500 or trueindex between 9900 and 12300 )
order by period asc

2021-01-11 17:29:42
正常:17:29:42 http-nio-8075-exec-9 INFO [standard] sql time: 2588 ms!

2021-01-11 17:29:42
正常:17:29:42 http-nio-8075-exec-9 INFO [standard] [SQL]SQL: SELECT
	* 
FROM
	budget_subject 
WHERE
	parentID IN ( '9700', '9800', '11400', '11700' ) 
	AND id NOT IN ( '9800', '11700', '11400' )

2021-01-11 17:29:42
正常:17:29:42 http-nio-8075-exec-9 INFO [standard] sql time: 4 ms!

2021-01-11 17:29:42
正常:17:29:42 http-nio-8075-exec-9 INFO [standard] [SQL]SQL: SELECT
last.erp_code,last.period,sum(amount) as amout
FROM
 (
 (
SELECT
 a.erp_code,a.period,sum(a.item_amount ) as amount
FROM
 (
SELECT
 rims_basic_agent_expense.period,
 fnaBudgetERPlink.budgetIndex AS budgetIndex,
 rims_basic_agent_expense.erp_code,
 rims_basic_agent_expense.item_amount,
 rims_basic_agent_expense.sequence  
FROM
 `rims_basic_agent_expense`
 LEFT JOIN fnaBudgetERPlink ON fnaBudgetERPlink.erpSequenceIndex = rims_basic_agent_expense.sequence  
 where period BETWEEN '2020-01' and '2020-03'  and erp_code in ('1502001','1502001-1@','2102002')
 ) a
WHERE
 a.budgetIndex NOT IN ( SELECT budgetindex FROM fnabudgetoalink WHERE budgetindex IS NOT NULL UNION SELECT DISTINCT sequence AS budgetindex FROM rims_basic_agent_part_service_expense  where period BETWEEN '2020-01' and '2020-03'  and erp_code in ('1502001','1502001-1@','2102002'))  and (a.budgetIndex BETWEEN 8200 and 9500
 or a.budgetIndex BETWEEN 9900 and 12300) 
group by 
 a.erp_code,a.period 
 ) UNION
 (
SELECT
 a.erp_code,a.period,sum(a.item_amount ) as amount
FROM
 (
SELECT
 rims_basic_agent_oa_expense.period,
 fnaBudgetoalink.budgetIndex AS budgetIndex,
 rims_basic_agent_oa_expense.erp_code,
 rims_basic_agent_oa_expense.item_amount,
 rims_basic_agent_oa_expense.sequence 
FROM
 `rims_basic_agent_oa_expense`
 LEFT JOIN fnaBudgetoalink ON fnaBudgetoalink.oaIndex = rims_basic_agent_oa_expense.sequence
 where period BETWEEN '2020-01' and '2020-03'  and erp_code in ('1502001','1502001-1@','2102002') 
  ) a
WHERE
 a.sequence IS NOT NULL and (a.budgetIndex BETWEEN 8200 and 9500
 or a.budgetIndex BETWEEN 9900 and 12300)  and erp_code !=''  
 AND a.budgetIndex NOT IN ( SELECT DISTINCT sequence AS budgetindex FROM rims_basic_agent_part_service_expense ) 
group by 
 a.erp_code,a.period
 ) UNION
 (
SELECT
 a.erp_code,a.period,sum(a.item_amount) as amount
FROM
 (
SELECT
 rims_basic_agent_part_service_expense.period,
 rims_basic_agent_part_service_expense.sequence AS budgetIndex,
 rims_basic_agent_part_service_expense.erp_code,
 rims_basic_agent_part_service_expense.item_amount,
 rims_basic_agent_part_service_expense.sequence 
FROM
 `rims_basic_agent_part_service_expense`
  where period BETWEEN '2020-01' and '2020-03'  and erp_code in ('1502001','1502001-1@','2102002')
  union ALL
 SELECT
 rims_basic_agent_part_fixed_expense.period,
 rims_basic_agent_part_fixed_expense.sequence AS budgetIndex,
 rims_basic_agent_part_fixed_expense.erp_code,
  rims_basic_agent_part_fixed_expense.item_amount,
  rims_basic_agent_part_fixed_expense.sequence
FROM
 rims_basic_agent_part_fixed_expense 
  where period BETWEEN '2020-01' and '2020-03'  and erp_code in ('1502001','1502001-1@','2102002')
 ) a
WHERE
 a.sequence IS NOT NULL and (a.budgetIndex BETWEEN 8200 and 9500
 or a.budgetIndex BETWEEN 9900 and 12300)
group by 
 a.erp_code,a.period
 ) 
 ) AS last 
left join view_agent_area_erpreport  province on province.agent_erp_code = last.erp_code 
 where province.area_id<35 and agent_state = '1' and report_operate!=2
     and last.erp_code in ('1502001','1502001-1@','2102002')
 and last.period between '2020-01' and '2020-03'
group by last.erp_code,last.period

2021-01-11 17:29:42
正常:17:29:42 http-nio-8075-exec-9 INFO [standard] sql time: 50 ms!

2021-01-11 17:29:42
正常:17:29:42 http-nio-8075-exec-9 INFO [standard] [SQL]SQL: SELECT
	1 as pCode,"全部" as pName,
	if(isnull(parent_project_erp), agent.erp_code, agent.parent_project_erp) AS parentERP,
	if(isnull(parent_project_erp), 1, 2) AS level,
	agent.NAME as agent_name,
	agent.erp_code as erpCode,
	area.NAME AS area_name,
	area.uuid AS area_id,
	operate_time,
if(operate_time>'2019-12-31',TIMESTAMPDIFF(month,operate_time,now()),'')
 as monthDiff, 
	(select count(parent_project_erp) from rims_basic_agent where rims_basic_agent.parent_project_erp= agent.erp_code and rims_basic_agent.state = "1" and rims_basic_agent.report_operate !=2) AS num
FROM
	`rims_basic_agent` agent
	LEFT JOIN rims_basic_area_agent area_agent ON agent.uuid = area_agent.agent_fk
	LEFT JOIN rims_basic_area area ON area.uuid = area_agent.area_fk 
WHERE
	length( agent.erp_code ) != 0 
     and agent.erp_code in ('1502001','1502001-1@','2102002')
	AND agent.state = "1" and agent.report_operate =1
	AND area.pid = 0 
	ORDER BY area_id asc,agent.erp_code asc 

2021-01-11 17:29:42
正常:17:29:42 http-nio-8075-exec-9 INFO [standard] sql time: 1 ms!

2021-01-11 17:29:42
正常:17:29:42 http-nio-8075-exec-9 INFO [standard] [SQL]SQL: select * from view_operation_month

2021-01-11 17:29:42
正常:17:29:42 http-nio-8075-exec-9 INFO [standard] sql time: 63 ms!

2021-01-11 17:29:42
正常:17:29:42 http-nio-8075-exec-9 INFO [standard] [SQL]SQL: select * from data_measuring_income

2021-01-11 17:29:42
正常:17:29:42 http-nio-8075-exec-9 INFO [standard] sql time: 23 ms!

2021-01-11 17:29:42
正常:17:29:42 http-nio-8075-exec-9 INFO [standard] [SQL]SQL: SELECT * FROM budget_subject WHERE parentID = '4100' OR parentID = '4200' AND enable = 1

2021-01-11 17:29:42
正常:17:29:42 http-nio-8075-exec-9 INFO [standard] sql time: 4 ms!

2021-01-11 17:29:42
正常:17:29:42 http-nio-8075-exec-9 INFO [standard] [SQL]SQL: SELECT
	* 
FROM
	budget_subject 
WHERE
	parentID IN ( '8100', '9000' ) 
	AND enable = 1 
	AND ID NOT IN ('8200','8300','8400','8500','8600','9000')

2021-01-11 17:29:42
正常:17:29:42 http-nio-8075-exec-9 INFO [standard] sql time: 3 ms!

2021-01-11 17:29:42
正常:17:29:42 http-nio-8075-exec-9 INFO [standard] [SQL]SQL: SELECT
	* 
FROM
	budget_subject 
WHERE
	parentID IN ( '9700', '9800', '11400', '11700' ) 
	AND id NOT IN ( '9800', '11700', '11400' )

2021-01-11 17:29:42
正常:17:29:42 http-nio-8075-exec-9 INFO [standard] sql time: 4 ms!

2021-01-11 17:29:42
正常:17:29:42 http-nio-8075-exec-9 INFO [standard] [SQL]SQL: SELECT
	if(isnull(parent_project_erp), agent.erp_code, agent.parent_project_erp) AS parentERP,
	1 as pCode,"全部" as pName,
	agent.NAME as agent_name,
	agent.erp_code as erpCode,
	area.NAME AS area_name,
	area.uuid AS area_id 
FROM
	`rims_basic_agent` agent
	LEFT JOIN rims_basic_area_agent area_agent ON agent.uuid = area_agent.agent_fk
	LEFT JOIN rims_basic_area area ON area.uuid = area_agent.area_fk 
	left join view_permission_agent_erpreport on view_permission_agent_erpreport.agent_erp_code=agent.erp_code
WHERE
	length( agent.erp_code ) != 0 and agent.parent_project_erp is null
	AND agent.state = "1" and agent.report_operate !=2
	AND area.pid = 0 
	 and view_permission_agent_erpreport.username='15076328933'

2021-01-11 17:29:43
正常:17:29:43 http-nio-8075-exec-9 INFO [standard] sql time: 1075 ms!

2021-01-11 17:29:43
正常:17:29:43 http-nio-8075-exec-9 INFO [standard] [SQL]SQL: SELECT
	1 as pCode,"全部" as pName,
	if(isnull(parent_project_erp), agent.erp_code, agent.parent_project_erp) AS parentERP,
	if(isnull(parent_project_erp), 1, 2) AS level,
	agent.NAME as agent_name,
	agent.erp_code as erpCode,
	area.NAME AS area_name,
	area.uuid AS area_id,
	operate_time,
if(operate_time>'2019-12-31',TIMESTAMPDIFF(month,operate_time,now()),'')
 as monthDiff, 
	(select count(parent_project_erp) from rims_basic_agent where rims_basic_agent.parent_project_erp= agent.erp_code and rims_basic_agent.state = "1" and rims_basic_agent.report_operate !=2) AS num
FROM
	`rims_basic_agent` agent
	LEFT JOIN rims_basic_area_agent area_agent ON agent.uuid = area_agent.agent_fk
	LEFT JOIN rims_basic_area area ON area.uuid = area_agent.area_fk 
WHERE
	length( agent.erp_code ) != 0 
     and agent.erp_code in ('1502001','1502001-1@','2102002')
	AND agent.state = "1" and agent.report_operate =1
	AND area.pid = 0 
	ORDER BY area_id asc,agent.erp_code asc 

2021-01-11 17:29:43
正常:17:29:43 http-nio-8075-exec-9 INFO [standard] sql time: 5 ms!

2021-01-11 17:29:43
正常:17:29:43 http-nio-8075-exec-9 INFO [standard] 完成报表计算	:4246ms!

2021-01-11 17:29:43
警告:17:29:43 http-nio-8075-exec-9 ERROR [standard] 6