package nc.impl.sscpfa.pfareport.bp;

import java.util.List;
import java.util.Map;
import nc.bs.dao.BaseDAO;
import nc.jdbc.framework.SQLParameter;
import nc.jdbc.framework.processor.BeanListProcessor;
import nc.jdbc.framework.processor.ColumnListProcessor;
import nc.jdbc.framework.processor.MapListProcessor;
import nc.vo.pf.pub.util.SQLUtil;
import nc.vo.pub.BusinessException;
import nc.vo.sscpfa.pfareport.report.SSCWorkingGroupBillReportVO;

public class SSCWorkingGroupBP {
	public List<SSCWorkingGroupBillReportVO> listBillDealData(String pkUnit, String[] pkWorkGroups,
			String[] leafPkWorkGroups, String startDate, String endDate) throws BusinessException {
		String sql = "select workinggroup.pk_workinggroup, workinggroup.code pk_workinggroup_code, workinggroup.name pk_workinggroup_name, sum(flowpath.num) pre_quantity from ssctp_workinggroup workinggroup left join (select workinggroup.innercode, count(distinct flowpath.busiid) num from ssctp_flowpath flowpath inner join ssctp_workinggroup workinggroup on flowpath.pk_sscgroup = workinggroup.pk_workinggroup where flowpath.actiontype in ('sscapprove', 'sscreject') and flowpath.operatetime >= ? and flowpath.operatetime <= ? and "
				+ SQLUtil.buildSqlForIn("flowpath.pk_sscgroup", leafPkWorkGroups)
				+ "group by workinggroup.innercode) flowpath on flowpath.innercode like concat(workinggroup.innercode, '%') where "
				+ SQLUtil.buildSqlForIn("workinggroup.pk_workinggroup", pkWorkGroups)
				+ "group by workinggroup.pk_workinggroup, workinggroup.code, workinggroup.name";
		SQLParameter parameter = new SQLParameter();
		parameter.addParam(startDate);
		parameter.addParam(endDate);
		return (List) (new BaseDAO()).executeQuery(sql, parameter,
				new BeanListProcessor(SSCWorkingGroupBillReportVO.class));
	}

	public String[] listLeafPkWorkGroups(String[] pkWorkGroups) throws BusinessException {
		String sql = "select workinggroup.pk_workinggroup from ssctp_workinggroup workinggroup inner join (select innercode from ssctp_workinggroup where "
				+ SQLUtil.buildSqlForIn("pk_workinggroup", pkWorkGroups)
				+ ") a on workinggroup.innercode like concat(a.innercode, '%') where workinggroup.isleaf = 'Y'";
		List<Object> ids = (List) (new BaseDAO()).executeQuery(sql, (SQLParameter) null,
				new ColumnListProcessor("pk_workinggroup"));
		return (String[]) ids.toArray(new String[ids.size()]);
	}

	public List<Map<String, String>> listWorkingGroupAndUser(String[] pkWorkGroups) throws BusinessException {
		String sql = "select distinct workinggroup.pk_workinggroup, workinggroup.code pk_workinggroup_code, workinggroup.name pk_workinggroup_name, u.cuserid pk_user, u.user_name pk_user_name from ssctp_workinggroup_user wu inner join sm_user u on wu.pk_user = u.cuserid inner join ssctp_workinggroup workinggroup on wu.pk_workinggroup = workinggroup.pk_workinggroup where "
				+ SQLUtil.buildSqlForIn("wu.pk_workinggroup", pkWorkGroups)
				+ "order by workinggroup.pk_workinggroup, u.cuserid";
		return (List) (new BaseDAO()).executeQuery(sql, (SQLParameter) null, new MapListProcessor());
	}

	public List<Map<String, Object>> listApproveAndRejectData(String[] pkWorkGroups, String startDate, String endDate)
			throws BusinessException {
		String sql = "select flowpath.pk_sscgroup pk_workinggroup, flowpath.pk_sscuser pk_user, flowpath.actiontype, count(1) num,sum (flowpath.statisticaltime) totaltime from (select pk_sscgroup, pk_sscuser, actiontype, operatetime, statisticaltime, max(operatetime) over(partition by pk_sscgroup, pk_sscuser, busiid) max_operatetime from ssctp_flowpath where actiontype in ('sscapprove', 'sscreject') and "
				+ SQLUtil.buildSqlForIn("pk_sscgroup", pkWorkGroups)
				+ "and operatetime >= ? and operatetime <= ?) flowpath where flowpath.operatetime = flowpath.max_operatetime group by flowpath.pk_sscgroup, flowpath.pk_sscuser, flowpath.actiontype order by flowpath.pk_sscgroup, flowpath.pk_sscuser";
		SQLParameter parameter = new SQLParameter();
		parameter.addParam(startDate);
		parameter.addParam(endDate);
		return (List) (new BaseDAO()).executeQuery(sql, parameter, new MapListProcessor());
	}

	public List<Map<String, Object>> listHandonAndAdjustTimeData(String[] pkWorkGroups, String startDate,
			String endDate) throws BusinessException {
		String sql = "select flowpath.pk_sscgroup pk_workinggroup, flowpath.pk_sscuser pk_user, sum (isnull(flowpath.statisticaltime, 0)) totaltime from ssctp_flowpath flowpath inner join ssctp_task task on flowpath.pk_sscuser = task.pk_sscuser and flowpath.pk_task = task.pk_task where flowpath.actiontype in ('handon', 'adjust') and "
				+ SQLUtil.buildSqlForIn("flowpath.pk_sscgroup", pkWorkGroups)
				+ "and flowpath.operatetime >= ? and flowpath.operatetime <= ?group by flowpath.pk_sscgroup, flowpath.pk_sscuser order by flowpath.pk_sscgroup, flowpath.pk_sscuser";
		SQLParameter parameter = new SQLParameter();
		parameter.addParam(startDate);
		parameter.addParam(endDate);
		return (List) (new BaseDAO()).executeQuery(sql, parameter, new MapListProcessor());
	}

	public List<Map<String, Object>> listRejectedData(String[] pkWorkGroups, String startDate, String endDate)
			throws BusinessException {
		String sql = "select  b.pk_sscgroup pk_workinggroup, b.pk_sscuser pk_user, a.num from (select busiid, count(distinct busiid) num from ssctp_flowpath where pk_sscuser is not null and actiontype = 'sscreject' and "
				+ SQLUtil.buildSqlForIn("pk_sscgroup", pkWorkGroups)
				+ "and pk_sscnode = '0001ZG10000000NODE02' and operatetime >= ? and operatetime <= ?  group by busiid) a inner join (select distinct busiid, pk_sscuser, pk_sscgroup from ssctp_flowpath where pk_sscuser is not null and pk_sscnode = '0001ZG10000000NODE01' and actiontype = 'sscapprove' and "
				+ SQLUtil.buildSqlForIn("pk_sscgroup", pkWorkGroups)
				+ "and operatetime >= ? and operatetime <= ?) b on a.busiid = b.busiid";
		SQLParameter parameter = new SQLParameter();
		parameter.addParam(startDate);
		parameter.addParam(endDate);
		parameter.addParam(startDate);
		parameter.addParam(endDate);
		return (List) (new BaseDAO()).executeQuery(sql, parameter, new MapListProcessor());
	}
	
	// added by gujsh at 2022-04-13 根据时间段和工作组查询所有符合条件的批准和驳回的数据
	public List<Map<String, Object>> getAllApproveAndRejectData(String[] pkWorkGroups, String startDate, String endDate)
			throws BusinessException {
		String sql = "select * from (select pk_sscgroup, pk_sscuser, actiontype, operatetime, statisticaltime, max(operatetime) over(partition by pk_sscgroup, pk_sscuser, busiid) max_operatetime,beigntime from ssctp_flowpath where actiontype in ('sscapprove', 'sscreject') and "
				+ SQLUtil.buildSqlForIn("pk_sscgroup", pkWorkGroups)
				+ "and operatetime >= ? and operatetime <= ?) flowpath where flowpath.operatetime = flowpath.max_operatetime";
		SQLParameter parameter = new SQLParameter();
		parameter.addParam(startDate);
		parameter.addParam(endDate);
		return (List) (new BaseDAO()).executeQuery(sql, parameter, new MapListProcessor());
	}
	
	// added by gujsh at 2022-04-13 根据时间段和工作组查询所有符合条件的挂起和调整的数据
	public List<Map<String, Object>> getAllHandonAndAdjustTimeData(String[] pkWorkGroups, String startDate,
			String endDate) throws BusinessException {
		String sql = "select flowpath.pk_sscgroup pk_workinggroup, flowpath.pk_sscuser pk_user, isnull(flowpath.statisticaltime, 0) totaltime,flowpath.beigntime,flowpath.operatetime from ssctp_flowpath flowpath inner join ssctp_task task on flowpath.pk_sscuser = task.pk_sscuser and flowpath.pk_task = task.pk_task where flowpath.actiontype in ('handon', 'adjust') and "
				+ SQLUtil.buildSqlForIn("flowpath.pk_sscgroup", pkWorkGroups)
				+ "and flowpath.operatetime >= ? and flowpath.operatetime <= ?";
		SQLParameter parameter = new SQLParameter();
		parameter.addParam(startDate);
		parameter.addParam(endDate);
		return (List) (new BaseDAO()).executeQuery(sql, parameter, new MapListProcessor());
	}
}