public class ListShipController {
    private static String YEAR_NULL = '-';
    public List<PageLine> pageLines { get; private set; }
    public PageLine totalLine { get; private set; }
    // where
    public String enduser { get; set; }
    public String area { get; set; }
    public String salesDept { get; set; }
    public User dateFrom { get; set; }
    public User dateTo { get; set; }
    public String category2 { get; set; }
    public String category3 { get; set; }
    public String category4 { get; set; }
    public String category5 { get; set; }
    public String modelNo { get; set; }

    public static List<SelectOption> getAreaOpts() {
        List<SelectOption> areaOpts = new List<SelectOption>();
        areaOpts.add(new SelectOption('全部','全部'));
        areaOpts.add(new SelectOption('安徽','安徽'));
        areaOpts.add(new SelectOption('北京','北京'));
        areaOpts.add(new SelectOption('福建','福建'));
        areaOpts.add(new SelectOption('甘肃','甘肃'));
        areaOpts.add(new SelectOption('广东','广东'));
        areaOpts.add(new SelectOption('海南','海南'));
        areaOpts.add(new SelectOption('广西','广西'));
        areaOpts.add(new SelectOption('贵州','贵州'));
        areaOpts.add(new SelectOption('河北','河北'));
        areaOpts.add(new SelectOption('河南','河南'));
        areaOpts.add(new SelectOption('黑龙江','黑龙江'));
        areaOpts.add(new SelectOption('湖北','湖北'));
        areaOpts.add(new SelectOption('湖南','湖南'));
        areaOpts.add(new SelectOption('吉林','吉林'));
        areaOpts.add(new SelectOption('江苏','江苏'));
        areaOpts.add(new SelectOption('江西','江西'));
        areaOpts.add(new SelectOption('辽宁','辽宁'));
        areaOpts.add(new SelectOption('内蒙古','内蒙古'));
        areaOpts.add(new SelectOption('宁夏','宁夏'));
        areaOpts.add(new SelectOption('青海','青海'));
        areaOpts.add(new SelectOption('山东','山东'));
        areaOpts.add(new SelectOption('山西','山西'));
        areaOpts.add(new SelectOption('陕西','陕西'));
        areaOpts.add(new SelectOption('上海','上海'));
        areaOpts.add(new SelectOption('四川','四川'));
        areaOpts.add(new SelectOption('西藏','西藏'));
        areaOpts.add(new SelectOption('天津','天津'));
        areaOpts.add(new SelectOption('新疆','新疆'));
        areaOpts.add(new SelectOption('云南','云南'));
        areaOpts.add(new SelectOption('浙江','浙江'));
        areaOpts.add(new SelectOption('重庆','重庆'));
        return areaOpts;
    }
    
    public static List<SelectOption> getSalesDeptOpts() {
        List<SelectOption> salesDeptOpts = new List<SelectOption>();
        salesDeptOpts.add(new SelectOption('全部','全部'));
        salesDeptOpts.add(new SelectOption('1.华北','1.华北'));
        salesDeptOpts.add(new SelectOption('2.东北','2.东北'));
        salesDeptOpts.add(new SelectOption('3.西北','3.西北'));
        salesDeptOpts.add(new SelectOption('4.西南','4.西南'));
        salesDeptOpts.add(new SelectOption('5.华东','5.华东'));
        salesDeptOpts.add(new SelectOption('6.华南','6.华南'));
        return salesDeptOpts;
    }

    public static List<SelectOption> getCategory2Opts() {
        List<SelectOption> category2Opts = new List<SelectOption>();
        category2Opts.add(new SelectOption('全部','全部'));
        category2Opts.add(new SelectOption('本体','本体'));
        category2Opts.add(new SelectOption('耗材','耗材'));
        category2Opts.add(new SelectOption('附属品','附属品'));
        return category2Opts;
    }

    // group by y
    public Boolean isSalesDept { get; set; }
    public Boolean isArea { get; set; }
    public Boolean isEnduser { get; set; }
    public Boolean isCategory2 { get; set; }
    public Boolean isCategory3 { get; set; }
    public Boolean isCategory4 { get; set; }
    public Boolean isCategory5 { get; set; }
    public Boolean isProductCode { get; set; }
    // group by x
    public Boolean isShowMonth { get; set; }
    public Boolean isShowWeek { get; set; }
    public List<String> headerYrCols { get; private set; }
    public Map<String, List<String>> headerDcCols { get; private set; }     // year毎の dcのList
    public Map<String, Integer> headerYrColspan { get; private set; }
    public Map<String, Integer> headerXColspan { get; private set; }        // Mapのキーにyearの情報のあります。
    
    // 集計項目
    public Boolean isShowSumQty { get; set; }
    public Boolean isShowSumAmount { get; set; }
    public Integer aggregateColspan { get; private set; }
    public Integer setAggregateColspan() {
        aggregateColspan = 0;
        if (isShowSumQty) aggregateColspan++;
        if (isShowSumAmount) aggregateColspan++;
        return aggregateColspan;
    }

    public ListShipController(ApexPages.StandardController controller) {
    }
    
    public ListShipController() {
    }
    
    public PageReference init() {
        area = '全部';
        salesDept = '全部';
        dateFrom = new User(id=Userinfo.getUserId());
        dateTo = new User(id=Userinfo.getUserId());
        category2 = '全部';
        
        isSalesDept = false;
        isArea = true;
        isEnduser = true;
        isCategory2 = true;
        isCategory3 = true;
        isCategory4 = true;
        isCategory5 = true;
        isProductCode = true;
        
        isShowMonth = false;
        isShowWeek = false;
        // 集計項目
        isShowSumQty = true;
        isShowSumAmount = true;
        
        return searchBtn();
    }
    
    public PageReference searchBtn() {
        headerYrCols = new List<String>();
        headerDcCols = new Map<String, List<String>>();
        headerYrColspan = new Map<String, Integer>();
        headerXColspan = new Map<String, Integer>();
        pageLines = new List<PageLine>();
        setAggregateColspan();
        if (aggregateColspan == 0) {
            aggregateColspan = 1;
            isShowSumQty = true;
        }
        // 检索数据
        List<AggregateResult> aggAstList = getSqlResult();
        String breakKey = '';
        for (AggregateResult aggAst : aggAstList) {
            breakKey = addPageLines(breakKey, aggAst, isSalesDept, isArea, isEnduser, isCategory2, isCategory3, isCategory4, isCategory5, isProductCode, isShowMonth, isShowWeek);
        }
        // データを整形、行毎 Mapにない値を設定
        // まず、headerYrCols、headerDcCols の値をセット
        for (Integer line = 0; line < pageLines.size(); line++) {
            PageLine pl = pageLines.get(line);
            if (isShowMonth) {
                for (String installYear : pl.yrCols) {
                    Integer colspan = headerYrColspan.get(installYear);
                    if (colspan == null) {
                        headerYrCols.add(installYear);
System.debug('headerYrCols.add(' + installYear + ')');
                        headerYrColspan.put(installYear, aggregateColspan);     // Default 数量、修理金額
                    }
                    List<String> headerDcList = headerDcCols.get(installYear);
                    if (headerDcList == null) {
                        headerDcList = new List<String>();
                        headerDcCols.put(installYear, headerDcList);
                        if (isShowWeek == false) {
                            headerDcList.add(installYear);
                            headerXColspan.put(installYear, aggregateColspan);     // Default 数量、修理金額
                        }
                    }
                }
            } else {
                Integer colspan = headerYrColspan.get(YEAR_NULL);
                if (colspan == null) {
                    headerYrCols.add(YEAR_NULL);
System.debug('headerYrCols.add(' + YEAR_NULL + ')');
                    headerYrColspan.put(YEAR_NULL, aggregateColspan);     // Default 数量、修理金額
                }
                List<String> headerDcList = headerDcCols.get(YEAR_NULL);
                if (headerDcList == null) {
                    headerDcList = new List<String>();
                    headerDcCols.put(YEAR_NULL, headerDcList);
                    if (isShowWeek == false) {
                        headerDcList.add(YEAR_NULL);
                        headerXColspan.put(YEAR_NULL, aggregateColspan);     // Default 数量、修理金額
                    }
                }
            }
            if (isShowWeek) {
                for (String installYear : headerYrCols) {
                    List<String> xColList = pl.xColListMap.get(installYear);
                    List<String> dcList = pl.dcCols.get(installYear);
                    if (xColList != null) {
                        List<String> headerDcList = headerDcCols.get(installYear);          // 前のloopで必ず、newしたはず、nullの可能性がない。
                        for (Integer xColIdx = 0; xColIdx < xColList.size(); xColIdx++) {
                            String departmentClass = dcList.get(xColIdx);
                            String xKey = xColList.get(xColIdx);          // xKey にはすでにyearの情報が入っています。
System.debug(xKey + '=xColList.get(' + xColIdx + ')');
                            Integer colspan = headerXColspan.get(installYear + '｜' + departmentClass);
                            if (colspan == null) {
                                // x列がない、追加
System.debug('headerXColspan.put(' + installYear + '｜' + departmentClass + ')');
                                headerDcList.add(departmentClass);
                                headerXColspan.put(installYear + '｜' + departmentClass, aggregateColspan);     // Default 数量、修理金額
                            } else {
                                // 存在するとは、すでに別のlineにて追加済み
                            }
                        }
                    }
System.debug('headerDcCols.get(' + installYear + ').size()=' + headerDcCols.get(installYear).size());
                }
            }
        }
        // たりないセルの値をMapにput
        // headerYrCols と headerDcCols の sort
        // 最後の合計行を出す
        headerYrCols.sort();
        totalLine = new PageLine(isSalesDept, isArea, isEnduser, isCategory2, isCategory3, isCategory4, isCategory5, isProductCode);
        for (Integer line = 0; line < pageLines.size(); line++) {
            PageLine pl = pageLines.get(line);
            for (String installYear : headerYrCols) {
                // sort headerDcList
                List<String> headerDcList = headerDcCols.get(installYear);
                headerDcList.sort();
                headerDcCols.put(installYear, headerDcList);
System.debug('for pageLines :' + installYear + '｜' + line + '｜' + headerDcList.size());
                headerYrColspan.put(installYear, headerDcList.size() * aggregateColspan);
                for (String departmentClass : headerDcList) {
                    String xKey = installYear + '｜' + departmentClass + '｜' + line;
                    String xTKey = installYear + '｜' + departmentClass + '｜' + pageLines.size();
System.debug('pl.cntMap[' + xKey + ']');
                    if (pl.cntMap.get(xKey) == null) {
System.debug('pl.cntMap.put(' + xKey + ', 0)');
                        pl.cntMap.put(xKey, 0);
                        pl.sumRepairMap.put(xKey, 0);
                    } else {
                        if (totalLine.cntMap.get(xTKey) == null) {
                            totalLine.allCnt += pl.cntMap.get(xKey);
                            totalLine.allSumRepair += pl.sumRepairMap.get(xKey);
                            totalLine.cntMap.put(xTKey, pl.cntMap.get(xKey));
                            totalLine.sumRepairMap.put(xTKey, pl.sumRepairMap.get(xKey));
                        } else {
                            totalLine.allCnt += pl.cntMap.get(xKey);
                            totalLine.allSumRepair += pl.sumRepairMap.get(xKey);
                            totalLine.cntMap.put(xTKey, totalLine.cntMap.get(xTKey) + pl.cntMap.get(xKey));
                            totalLine.sumRepairMap.put(xTKey, totalLine.sumRepairMap.get(xTKey) + pl.sumRepairMap.get(xKey));
                        }
                    }
                }
            }
        }
        return null;
    }
    
    private List<AggregateResult> getSqlResult() {
        String soql = 'select CurrencyIsoCode, Sum(Qty__c) Sum_Qty, Sum(Sales_Amount__c) Sum_Amount';
        if (isSalesDept) {
//            soql += ', SalesDept__c';
        }
        if (isArea) {
            soql += ', Area__c';
        }
        if (isEnduser) {
            soql += ', Enduser__c';
        }
        if (isCategory2) {
            soql += ', Product_ID__r.Category2__c';
        }
        if (isCategory3) {
            soql += ', Product_ID__r.Category3__c';
        }
        if (isCategory4) {
            soql += ', Product_ID__r.Category4__c';
        }
        if (isCategory5) {
            soql += ', Product_ID__r.Category5__c';
        }
        if (isProductCode) {
            soql += ', Product_ID__r.Asset_Model_No__c';
        }
        if (isShowMonth) {
            soql += ', CALENDAR_YEAR(Shipping_date__c) ShippingYear, CALENDAR_MONTH(Shipping_date__c) ShippingMonth';
            if (isShowWeek) {
	            soql += ', WEEK_IN_MONTH(Shipping_date__c) ShippingWeek';
	        }
        } else {
	        if (isShowWeek) {
	            soql += ', CALENDAR_YEAR(Shipping_date__c) ShippingYear, CALENDAR_MONTH(Shipping_date__c) ShippingMonth, WEEK_IN_MONTH(Shipping_date__c) ShippingWeek';
	        }
        }
        soql = soql + ' from Ship__c';
        soql = soql + ' where Id != null';
        if (salesDept != '全部') {
            soql += ' and SalesDept__c = :salesDept';
        }
        if (area != '全部') {
            soql += ' and Area__c = :area';
        }
        if (dateFrom.Birth_Date__c != null) {
            Date fromDate = dateFrom.Birth_Date__c;
            soql += ' and Shipping_date__c >= :fromDate';
        }
        if (dateTo.Birth_Date__c != null) {
            Date toDate = dateTo.Birth_Date__c;
            soql += ' and Shipping_date__c <= :toDate';
        }
        if (String.isBlank(enduser) == false) {
            String prd_Enduser = '%' + enduser.replaceAll('%', '\\%') + '%';
            soql += ' and Enduser__c like :prd_Enduser';
        }
        if (category2 != '全部') {
            soql += ' and Product_ID__r.Category2__c = :category2';
        }
        if (String.isBlank(category3) == false) {
            String prd_Category3 = '%' + category3.replaceAll('%', '\\%') + '%';
            soql += ' and Product_ID__r.Category3__c like :prd_Category3';
        }
        if (String.isBlank(category4) == false) {
            String prd_Category4 = '%' + category4.replaceAll('%', '\\%') + '%';
            soql += ' and Product_ID__r.Category4__c like :prd_Category4';
        }
        if (String.isBlank(category5) == false) {
            String prd_Category5 = '%' + category5.replaceAll('%', '\\%') + '%';
            soql += ' and Product_ID__r.Category5__c like :prd_Category5';
        }
        if (String.isBlank(modelNo) == false) {
            String prd_Asset_Model_No = '%' + modelNo.replaceAll('%', '\\%') + '%';
            soql += ' and Product_ID__r.Asset_Model_No__c like :prd_Asset_Model_No';
        }
        soql += ' group by CurrencyIsoCode';
        if (isSalesDept) {
//            soql += ', SalesDept__c';
        }
        if (isArea) {
            soql += ', Area__c';
        }
        if (isEnduser) {
            soql += ', Enduser__c';
        }
        if (isCategory2) {
            soql += ', Product_ID__r.Category2__c';
        }
        if (isCategory3) {
            soql += ', Product_ID__r.Category3__c';
        }
        if (isCategory4) {
            soql += ', Product_ID__r.Category4__c';
        }
        if (isCategory5) {
            soql += ', Product_ID__r.Category5__c';
        }
        if (isProductCode) {
            soql += ', Product_ID__r.Asset_Model_No__c';
        }
        if (isShowMonth) {
            soql += ', CALENDAR_YEAR(Shipping_date__c), CALENDAR_MONTH(Shipping_date__c)';
            if (isShowWeek) {
	            soql += ', WEEK_IN_MONTH(Shipping_date__c)';
	        }
        } else {
	        if (isShowWeek) {
	            soql += ', CALENDAR_YEAR(Shipping_date__c), CALENDAR_MONTH(Shipping_date__c), WEEK_IN_MONTH(Shipping_date__c)';
	        }
        }
        soql += ' order by CurrencyIsoCode';
        if (isSalesDept) {
//            soql += ', SalesDept__c';
        }
        if (isArea) {
            soql += ', Area__c';
        }
        if (isEnduser) {
            soql += ', Enduser__c';
        }
        if (isCategory2) {
            soql += ', Product_ID__r.Category2__c';
        }
        if (isCategory3) {
            soql += ', Product_ID__r.Category3__c';
        }
        if (isCategory4) {
            soql += ', Product_ID__r.Category4__c';
        }
        if (isCategory5) {
            soql += ', Product_ID__r.Category5__c';
        }
        if (isProductCode) {
            soql += ', Product_ID__r.Asset_Model_No__c';
        }
        soql += ' limit ' + System.Label.ListShipLimit;
        system.debug('=====sql:' + soql);
        return Database.query(soql);
    }

    private String addPageLines(String breakKey, AggregateResult aggAst, Boolean isSalesDept, Boolean isArea, Boolean isEnduser, Boolean isCategory2, Boolean isCategory3, Boolean isCategory4, Boolean isCategory5, Boolean isProductCode, Boolean isShowMonth, Boolean isShowWeek) {
        // Y軸
        String y_salesDept = isSalesDept ? (aggAst.get('SalesDept__c') == null ? '' : String.valueOf(aggAst.get('SalesDept__c'))) : '';
        String y_area = isArea ? (aggAst.get('Area__c') == null ? '' : String.valueOf(aggAst.get('Area__c'))) : '';
        String y_enduser = isEnduser ? (aggAst.get('Enduser__c') == null ? '' : String.valueOf(aggAst.get('Enduser__c'))) : '';
        String category2 = isCategory2 ? (aggAst.get('Category2__c') == null ? '' : String.valueOf(aggAst.get('Category2__c'))) : '';
        String category3 = isCategory3 ? (aggAst.get('Category3__c') == null ? '' : String.valueOf(aggAst.get('Category3__c'))) : '';
        String category4 = isCategory4 ? (aggAst.get('Category4__c') == null ? '' : String.valueOf(aggAst.get('Category4__c'))) : '';
        String category5 = isCategory5 ? (aggAst.get('Category5__c') == null ? '' : String.valueOf(aggAst.get('Category5__c'))) : '';
        String productCode = isProductCode ? (aggAst.get('Asset_Model_No__c') == null ? '' : String.valueOf(aggAst.get('Asset_Model_No__c'))) : '';
        // X軸
        String x_month = isShowMonth ? ((aggAst.get('ShippingYear') == null && aggAst.get('ShippingMonth') == null) ? '' : String.valueOf(aggAst.get('ShippingYear')) + '-' + String.valueOf(aggAst.get('ShippingMonth'))) : '';
        String installYear = String.isBlank(x_month) ? YEAR_NULL : x_month;
        String departmentClass = isShowWeek ? (aggAst.get('ShippingWeek') == null ? '' : String.valueOf(aggAst.get('ShippingWeek'))) : installYear;

        // keyの比較
        String rtnKey = y_salesDept + '｜｜' + y_area + '｜｜' + y_enduser + '｜｜' + category2 + '｜｜' + category3 + '｜｜' + category4 + '｜｜' + category5 + '｜｜' + productCode;
        PageLine nowLine = null;
        if (rtnKey != breakKey) {
            nowLine = new PageLine(aggAst, isSalesDept, isArea, isEnduser, isCategory2, isCategory3, isCategory4, isCategory5, isProductCode, isShowMonth, isShowWeek);
            pageLines.add(nowLine);
        } else {
            nowLine = pageLines[pageLines.size() - 1];
        }
        String xKey = installYear + '｜' + departmentClass + '｜' + (pageLines.size() - 1);
        
        List<String> xColList = nowLine.xColListMap.get(installYear);
        List<String> dcList = nowLine.dcCols.get(installYear);
System.debug('nowLine.xColListMap.get(' + installYear + ')');
        if (xColList == null) {
            nowLine.yrCols.add(installYear);
            xColList = new List<String>();
            dcList = new List<String>();
            nowLine.xColListMap.put(installYear, xColList);
            nowLine.dcCols.put(installYear, dcList);
        }
        dcList.add(departmentClass);
        xColList.add(xKey);     // GroupByしているので、ここはX軸のxKeyもレコードごと重複がないはず
System.debug('xColList.add(' + xKey + ')');
        nowLine.allCnt += Integer.valueOf(aggAst.get('Sum_Qty') == null ? '0' : aggAst.get('Sum_Qty'));
        nowLine.allSumRepair += Double.valueOf(aggAst.get('Sum_Amount') == null ? '0' : aggAst.get('Sum_Amount'));
        nowLine.cntMap.put(xKey, Integer.valueOf(aggAst.get('Sum_Qty') == null ? '0' : aggAst.get('Sum_Qty')));
        nowLine.sumRepairMap.put(xKey, Double.valueOf(aggAst.get('Sum_Amount') == null ? '0' : aggAst.get('Sum_Amount')));

        // breakKeyを返す
        return rtnKey;
    }

    public class PageLine {
        public String salesDept { get; private set; }
        public String area { get; private set; }
        public String enduser { get; private set; }
        public String category2 { get; private set; }
        public String category3 { get; private set; }
        public String category4 { get; private set; }
        public String category5 { get; private set; }
        public String productCode { get; private set; }
        public Integer ttlColspan { get; private set; }
        public Integer allCnt { get; private set; }
        public Double allSumRepair { get; private set; }
        public Map<String, Integer> cntMap { get; private set; }        // 非表示でも、必ずCntする
        public Map<String, Double> sumRepairMap { get; private set; }
        public List<String> yrCols { get; private set; }
        public Map<String, List<String>> dcCols { get; private set; }
        public Map<String, List<String>> xColListMap { get; private set; }

        public PageLine(AggregateResult aggAst, Boolean isSalesDept, Boolean isArea, Boolean isEnduser, Boolean isCategory2, Boolean isCategory3, Boolean isCategory4, Boolean isCategory5, Boolean isProductCode, Boolean isShowMonth, Boolean isShowWeek) {
            salesDept = isSalesDept ? (aggAst.get('SalesDept__c') == null ? '' : String.valueOf(aggAst.get('SalesDept__c'))) : '';
            area = isArea ? (aggAst.get('Area__c') == null ? '' : String.valueOf(aggAst.get('Area__c'))) : '';
            enduser = isEnduser ? (aggAst.get('Enduser__c') == null ? '' : String.valueOf(aggAst.get('Enduser__c'))) : '';
            category2 = isCategory2 ? (aggAst.get('Category2__c') == null ? '' : String.valueOf(aggAst.get('Category2__c'))) : '';
            category3 = isCategory3 ? (aggAst.get('Category3__c') == null ? '' : String.valueOf(aggAst.get('Category3__c'))) : '';
            category4 = isCategory4 ? (aggAst.get('Category4__c') == null ? '' : String.valueOf(aggAst.get('Category4__c'))) : '';
            category5 = isCategory5 ? (aggAst.get('Category5__c') == null ? '' : String.valueOf(aggAst.get('Category5__c'))) : '';
            productCode = isProductCode ? (aggAst.get('Asset_Model_No__c') == null ? '' : String.valueOf(aggAst.get('Asset_Model_No__c'))) : '';
            allCnt = 0;
            allSumRepair = 0;
            cntMap = new Map<String, Integer>();
            sumRepairMap = new Map<String, Double>();
            yrCols = new List<String>();
            dcCols = new Map<String, List<String>>();
            xColListMap = new Map<String, List<String>>();
        }
        
        // 合計行の場合
        public PageLine(Boolean isSalesDept, Boolean isArea, Boolean isEnduser, Boolean isCategory2, Boolean isCategory3, Boolean isCategory4, Boolean isCategory5, Boolean isProductCode) {
            allCnt = 0;
            allSumRepair = 0;
            cntMap = new Map<String, Integer>();
            sumRepairMap = new Map<String, Double>();
            yrCols = new List<String>();
            dcCols = new Map<String, List<String>>();
            xColListMap = new Map<String, List<String>>();
            ttlColspan = 0;
            if (isSalesDept) ttlColspan++;
            if (isArea) ttlColspan++;
            if (isEnduser) ttlColspan++;
            if (isCategory2) ttlColspan++;
            if (isCategory3) ttlColspan++;
            if (isCategory4) ttlColspan++;
            if (isCategory5) ttlColspan++;
            if (isProductCode) ttlColspan++;
        }
    }
}