/*
 * @author liyf@sohobb.jp
 */
global without sharing class ET_Agency_Target_Result_Table_Batch implements Database.Batchable<SObject>, Database.Stateful{
    global Integer totalCount = 0; // 总件数
    global Integer executedCount = 0;
    global Integer successCount = 0; // 特約店予実管理成功件数
    global Integer failedCount = 0;  // 特約店予実管理失败件数
    global String term;            
    global String errMsgBody = '';
    global Boolean isAllFlag = false;
    global Integer cstYear = 9999;
    global Integer cstMonth = 0;
    global Integer testSkip = 0;

    // 月ごとにProduct_Score_Table_History__cのSales_Amount__cとDetail_Count__cに集計する
    // ②活動管理画面で、特約店の実績がある場合、テーブルの実績という箇所が使ってる
    global Database.QueryLocator start(Database.BatchableContext BC){
        // testskip
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        testSkip++;
        
        System.debug('********** ET_Agency_Target_Result_Table_Batch'+BC.getJobId()+'start');

        Date systemDate = Date.today().addDays(-Integer.valueof(System.Label.ET_Date_Calculate));
        Set<String> s = new Set<String>{'停止','有効','有効（再申請中）','失効（期限内生産済在庫対応）'};
        Product2 p = [SELECT Plan_Term__c FROM Product2
            WHERE Is_ET_APP__c = TRUE
            AND Category3__c <> ''
            AND SFDA_Status__c IN :s
            LIMIT 1];

        Date date_limit_h = Date.today().addDays(-1);
        Date date_limit_l = Date.newInstance(date_limit_h.year(), date_limit_h.month(), 1);
        term = p.Plan_Term__c.left(4);
        if (4 == Date.today().month() && Date.today().day() <= 1) {
            term = String.valueOf(Integer.valueOf(term.left(3))-1)+'P';
        }

        if (true == this.isAllFlag && 9999 != cstYear && 0 != cstMonth) {
            date_limit_h = Date.newInstance(cstYear, cstMonth + 1, 1).addDays(-1);
            date_limit_l = Date.newInstance(cstYear, cstMonth, 1);
            // the difference month between the year of cstDate and this year(cstDate - YYYY/04/01)
            Integer year_diff = Date.today().addDays(-1).addMonths(-3).year() - date_limit_l.addMonths(-3).year();
            if (year_diff > 0) {
                term = String.valueOf(Integer.valueOf(term.left(3)) - year_diff) + 'P';
            }
        }

        //Get 经销商Recordtype Id
        Id PSTH_RecordTypeId = Schema.SObjectType.Product_Score_Table_History__c.getRecordTypeInfosByDeveloperName().get('Agency').getRecordTypeId();
        /*
         *  按月筛选本次 Batch 产生/更新的行
         */
        Product_Score_Table_History__c [] pcts = [
                SELECT Id
                FROM  Product_Score_Table_History__c
                WHERE Shipping_date__c = :date_limit_l 
                AND RecordTypeId = :PSTH_RecordTypeId
                AND Province_From_Consume__c = FALSE
        ];
        /*
         *  清除表中临时统计信息
         */
        // for(Product_Score_Table_History__c  pct: pcts) {
        //     pct.put('Sales_Amount__c', 0);
        //     pct.put('Qty__c', 0);
        // }
        delete pcts;
        
        // Id devRecordTypeId = Schema.SObjectType.Account.getRecordTypeInfosByDeveloperName().get('Agency').getRecordTypeId();

        String query =
                ' SELECT Detail_Count__c, Intra_Trade_Without_Tax__c, Dealer_Info_text__c, Dealer_Info_ID__c, HospItal_Name__c, HospItal_Code__c, Hospital_ID__c, Consumable_product__c, Product_OutDate__c, Asset_Model_No__c, ' +
                ' Consumable_product__r.Product2__r.Category3__c, Consumable_product__r.Product2__r.Category4__c,Consumable_product__r.Product2__r.Plan_Term__c,Consumable_Sale_order__r.Opportunity__r.Sales_Root__c' +
                ' FROM Consumable_order_details2__c '+
                ' WHERE (NOT Dealer_Info_text__c LIKE \'%TEST%\') ';
        // if (true != this.isAllFlag) {
        //        query += 
        //         ' AND LastModifiedDate >= :systemDate ';
        // }
               query +=
                ' AND (NOT HospItal_Name__c LIKE \'%TEST%\') ' +
                ' AND HospItal_Name__c <> null ' +
                ' AND Consumable_product__r.Product2__r.Category3__c <> null ' +
                ' AND Consumable_product__r.Product2__r.Category4__c <> null ' +
                ' AND Detail_Count__c <> null ' +
                ' AND Intra_Trade_Without_Tax__c <> null ' +
                ' AND Inventory_Status__c <> \'未到经销商\' ' +
                ' AND Inventory_Status__c <> \'经销商库存\' ' +
                ' AND Consumable_product__r.Product2__r.Is_ET_APP__c = true ' +
                ' AND Cancellation_Flag__c = false ' +
                ' AND Dealer_Returned__c = false ' +
                ' AND Lose_Flag__c = false' + 
                ' AND Consumable_Sale_order__r.Opportunity__r.Sales_Root__c <> \'OCM直接販売\'' + 
                ' AND Product_OutDate__c >= :date_limit_l' + 
                ' AND Product_OutDate__c <= :date_limit_h';
        return Database.getQueryLocator(query);
    }

    global void execute(Database.BatchableContext BC, List<Consumable_order_details2__c> codList){
        totalCount += codList.size();

        System.debug('ET_Agency_Target_Result_Table_Batch '+BC.getJobId()+' execute start');
        //产品得分表取得
        List<String> pstIdList = new List<String>();
        for (Consumable_order_details2__c cod : codList) {
            String pSTUniqueKey = term + ':' + cod.Consumable_product__r.Product2__r.Category3__c + ':';
            pstIdList.add(uniqueCalculate(pSTUniqueKey, cod.Consumable_product__r.Product2__r.Category4__c, cod.Consumable_product__r.Product2__r.Category3__c, cod.Asset_Model_No__c));
        }
        Map<String, Product_Score_Table__c> pstIdMap = new Map<String, Product_Score_Table__c>();
        for(Product_Score_Table__c tmpObj : [SELECT Id, UniqueKey__c
                FROM Product_Score_Table__c
                WHERE UniqueKey__c IN :pstIdList]){
            pstIdMap.put(tmpObj.UniqueKey__c, tmpObj);
        }

        // 医院和经销商的关联参照取得
        // List<String> hosIdList = new List<String>();
        // for (Consumable_order_details2__c cod : codList) {
        //     hosIdList.add(cod.Hospital_ID__c);
        // }
        // Map<String, Agency_Hospital_Link__c> hosAgcMap = new Map<String, Agency_Hospital_Link__c>();
        // for(Agency_Hospital_Link__c tmpObj : [SELECT Id, Name, Agency__c, Agency__r.Name, Hospital__c 
        //         FROM Agency_Hospital_Link__c
        //         WHERE Hospital__c IN :hosIdList 
        //         AND AgencyHos_ET__c = true 
        //         AND Agency__c IN (SELECT Agent_Ref__c 
        //             FROM Account 
        //             WHERE ET_SP_Dealer__c = true)]){
        //     hosAgcMap.put(tmpObj.Hospital__c, tmpObj);
        // }

        // 医院和管理省的关联参照取得
        List<String> hosIdList = new List<String>();
        for (Consumable_order_details2__c cod : codList) {
            hosIdList.add(cod.Hospital_ID__c);
        }
        Map<String, Account> hosProvinMap = new Map<String, Account>();
        for(Account tmpObj : [SELECT Id, Name, OCM_man_province_HP__c 
                FROM Account
                WHERE Id IN :hosIdList]){
            hosProvinMap.put(tmpObj.Id, tmpObj);
        }


        // upsert产品得分表历史
        // 今現在処理対象の合計
        Map<String, Product_Score_Table_History__c> tempRollupMap = new Map<String, Product_Score_Table_History__c>();
        //Get 经销商Recordtype Id
        Id PSTH_RecordTypeId = Schema.SObjectType.Product_Score_Table_History__c.getRecordTypeInfosByDeveloperName().get('Agency').getRecordTypeId();

        for (Consumable_order_details2__c cod : codList) { 

            //uniqueKeyATR :  A:计画财年(151P):发货月:医院:经销商:第三分类 :新4.5分类
            String uniqueKeyPSTH = 'A:' + term + ':' + cod.Product_OutDate__c.month() + ':' + cod.HospItal_Code__c + ':' + cod.Dealer_Info_ID__c + ':' + cod.Consumable_product__r.Product2__r.Category3__c + ':';
            uniqueKeyPSTH = uniqueCalculate(uniqueKeyPSTH, cod.Consumable_product__r.Product2__r.Category4__c, cod.Consumable_product__r.Product2__r.Category3__c, cod.Asset_Model_No__c);


            // Shipping_date__c yyyy/mm/01
            Date sd = Date.newInstance(cod.Product_OutDate__c.year(), cod.Product_OutDate__c.month(), 1);
            Product_Score_Table_History__c psth = new Product_Score_Table_History__c(
                    RecordTypeId = PSTH_RecordTypeId,
                    UniqueKey__c = uniqueKeyPSTH,
                    Shipping_date__c = sd,
                    Hospital__c = cod.Hospital_ID__c,
                    Agency__c = cod.Dealer_Info_ID__c
            );

            //产品得分表  step: soql map id
            String pstUniqueKey = term + ':' + cod.Consumable_product__r.Product2__r.Category3__c + ':';
            pstUniqueKey = uniqueCalculate(pstUniqueKey, cod.Consumable_product__r.Product2__r.Category4__c, cod.Consumable_product__r.Product2__r.Category3__c, cod.Asset_Model_No__c);
            
            if (pstIdMap.containsKey(pstUniqueKey)) {
                psth.Product_Score_Table__c = pstIdMap.get(pstUniqueKey).Id;
            }

            //设置经销商字段
            // String hospitalKey = cod.Hospital_ID__c;
            // if (hosAgcMap.containsKey(hospitalKey)) {
            //     psth.Agency__c = hosAgcMap.get(hospitalKey).Agency__c;
            // }

            //设置医院的管理省
            String hospitalKey = cod.Hospital_ID__c;
            if (hosProvinMap.containsKey(hospitalKey)) {
                psth.OCM_Province_Text__c = hosProvinMap.get(hospitalKey).OCM_man_province_HP__c;
            }

            //金額(含税): Intra_Trade_Without_Tax__c  と 数量(盒)：Detail_Count__c  ばら売りした場合は小数が入る
            if (tempRollupMap.containsKey(uniqueKeyPSTH)) {
                Product_Score_Table_History__c tempPSTH = tempRollupMap.get(uniqueKeyPSTH);
                psth.Sales_Amount__c = cod.Intra_Trade_Without_Tax__c + tempPSTH.Sales_Amount__c;
                psth.Qty__c = cod.Detail_Count__c + tempPSTH.Qty__c;
                tempRollupMap.put(uniqueKeyPSTH, psth);
            } else {
                psth.Sales_Amount__c = cod.Intra_Trade_Without_Tax__c;
                psth.Qty__c = cod.Detail_Count__c;
                tempRollupMap.put(uniqueKeyPSTH, psth);
            }

        }

        // 既に保存済みのRecord  soql map
        Map<String, Product_Score_Table_History__c> donePSTHMap = new Map<String, Product_Score_Table_History__c>();
        for(Product_Score_Table_History__c tmpPSTH : [SELECT Id, UniqueKey__c, Sales_Amount__c, Shipping_date__c,
                Hospital__c, Agency__c,Qty__c
                FROM Product_Score_Table_History__c
                WHERE UniqueKey__c IN :tempRollupMap.keySet()]){
            donePSTHMap.put(tmpPSTH.UniqueKey__c, tmpPSTH);
        }

        //取得既存テーブルのSales_Amount__cとQty__c 集計
        for (String uniqueKey : tempRollupMap.keySet()) {
            Product_Score_Table_History__c psth = tempRollupMap.get(uniqueKey);
            //有既存资料且为第二次进来的产品得分表历史相加
            if (donePSTHMap.containsKey(uniqueKey)) {
                Product_Score_Table_History__c tmpPSTH = donePSTHMap.get(uniqueKey);
                tmpPSTH.Sales_Amount__c = psth.Sales_Amount__c + tmpPSTH.Sales_Amount__c;
                tmpPSTH.Shipping_date__c = psth.Shipping_date__c;
                tmpPSTH.Hospital__c = psth.Hospital__c;
                tmpPSTH.Agency__c = psth.Agency__c;
                tmpPSTH.RecordTypeId = psth.RecordTypeId;
                tmpPSTH.Product_Score_Table__c = psth.Product_Score_Table__c;
                tmpPSTH.Qty__c = psth.Qty__c+tmpPSTH.Qty__c;
                tempRollupMap.put(uniqueKey, tmpPSTH);    // doneのIdを利用する
            }
            else {
                tempRollupMap.put(uniqueKey, psth);    // doneのIdを利用する
            }

        }


        if (!tempRollupMap.isEmpty()){
            Database.UpsertResult [] results = Database.upsert(tempRollupMap.values(), Product_Score_Table_History__c.Id, false);
            for(Integer index = 0; index < results.size(); index++) {
                if(results[index].isSuccess()) { 
                    successCount++;
                }
                else{
                    for (Database.Error err : results[index].getErrors()) {
                        String message = 'UniqueKey : ' 
                        + tempRollupMap.values()[index].UniqueKey__c + ' ; '
                        + err.getStatusCode() + '; '
                        + err.getFields() + ' ; '
                        + err.getMessage();
                        failedCount++;
                        errMsgBody += message + '\n';
                    }
                }
            }
        }

        executedCount += codList.size();
        System.debug('ET_Agency_Target_Result_Table_Batch execute end');
    }  

    global void finish(Database.BatchableContext BC) {
        System.debug('ET_Agency_Target_Result_Table_Batch finish method start');
        BatchEmailUtil eb = new BatchEmailUtil();
        String title = '经销商实绩 Batch';
        String[] emailList = new String[] {};
        // 失败件数为0 寄出成功的mail
        if (failedCount == 0 && totalCount == executedCount) {
            String emailLabel = 'BatchNotify';
            for (OrgWideEmailAddress tmpEmailObj : [SELECT Id, Address, DisplayName 
                FROM OrgWideEmailAddress
                WHERE DisplayName = :emailLabel]) {
                    emailList.add(tmpEmailObj.Address);
            }
            eb.successMail(emailList, null, title, executedCount);
        }
        else {
            if (totalCount != executedCount) {
                errMsgBody += '有一部分Batch没有运行, 请确认系统管理员。\n';
            }
            eb.failedMail(title, errMsgBody, totalCount, executedCount, failedCount);
        }
        eb.send();
        System.debug('ET_Agency_Target_Result_Table_Batch finish method end');
    }

    private static String uniqueCalculate(String uniqueKey, String category4, String category3, String assetModelNo) {
        if ('吸引活检针' == category4 && 'EUS' == category3) {
            if (assetModelNo.startsWith('NA-U200H')) {
                uniqueKey += '吸引活检针-EZ3P';
            } else {
                uniqueKey += '吸引活检针-EZ3P以外';
            }
        } else if ('先端系粘膜切开刀' == category4 && 'ESD' == category3) {
            if (assetModelNo.startsWith('KD-655')) {
                uniqueKey += '先端系粘膜切开刀-DualJ';
            } else {
                uniqueKey += '先端系粘膜切开刀-DualJ以外';
            }
        } else {
            uniqueKey += category4;
        }

        return uniqueKey;
    }
}