<?php
class chartsAction extends baseAction
{
    function stackedLineChart()
    {
        $Params = mvc::$URL_PARAMS;
        switch ($Params['func']) {
            case 'getBrandChartsData':
                $echartsData = $this->getBrandChartsData($Params);
                break;
        }
        LibTpl::Set('echartsData', $echartsData);
        LibTpl::Put();
    }

    /**
     * @method 获取选中的数据的月份统计结果
     * @author hongzhang 
     * @copyright 2025-10-14
     */
    private function getBrandChartsData($parmas)
    {
        if($this->supData['shop_report_industry']<time()){
			return LibTpl::Error('您的服务已到期，请联系管理员续费！');
		}
        $tjDb = mvc::$cfg['DB']['API_TJ']['db_name'];
        $BaseDb = new BaseDb(mvc::$cfg['DB']['API_TJ']);

        $ids = $parmas['ids'];
        $level = $parmas['level'];
        $betime = $parmas['betime'];
        $betimeArr = explode(' ~ ', $betime);
        $startMonth = date('Ym',strtotime(trim($betimeArr[0])));
        $endMonth = date('Ym',strtotime(trim($betimeArr[1])));
        $chartData = [];
        switch ($level) {
            case 'brand':
                // 检查$ids是否为空，避免SQL语法错误
                if(empty($ids)){
                    $chartData = [];
                    break;
                }
                $sql = sprintf(
                    "SELECT
                    b2.brand_id as brand_id,
                    b2.brand_name as brand_name,
                    ari_date,
                    art_hotpv
                    FROM {$tjDb}.at_report_industry_item i1
                    LEFT JOIN {$tjDb}.bc_brand b2 ON i1.ari_keyid = b2.brand_id
                    WHERE i1.ari_keyid IN (%s)
                    AND i1.ari_date BETWEEN '%s' AND '%s'
                    AND i1.ari_type = 'brand'
                    GROUP BY i1.ari_keyid, i1.ari_date
                    ORDER BY art_hotpv  DESC, i1.ari_date ASC",
                    $ids,
                    $startMonth,
                    $endMonth
                );               //地图之后的省份筛选
                if($parmas['province_id']){
                    $sql = sprintf(
                        "SELECT
                        b2.brand_id as brand_id,
                        b2.brand_name as brand_name,
                        ari_date,
                        SUM(art_hotpv) as art_hotpv
                        FROM {$tjDb}.at_report_industry_month m1
                        JOIN {$tjDb}.bc_model2 bm2 ON ari_mod2_id = mod2_id
                        JOIN {$tjDb}.bc_brand b2 ON bm2.mod2_brand_id = b2.brand_id
                        WHERE bm2.mod2_brand_id IN (%s)
                        AND m1.ari_date BETWEEN '%s' AND '%s'
                        AND m1.ari_province = %d
                        GROUP BY bm2.mod2_brand_id, m1.ari_date
                        ORDER BY bm2.mod2_brand_id  ASC, m1.ari_date ASC",
                        $ids,
                        $startMonth,
                        $endMonth,
                        $parmas['province_id']
                    );
                }else{
                    // 检查$ids是否为空，避免SQL语法错误
                    if(empty($ids)){
                        $chartData = [];
                        break;
                    }
                    $sql = sprintf(
                        "SELECT
                        b2.brand_id as brand_id,
                        b2.brand_name as brand_name,
                        ari_date,
                        art_hotpv
                        FROM {$tjDb}.at_report_industry_item i1
                        LEFT JOIN {$tjDb}.bc_brand b2 ON i1.ari_keyid = b2.brand_id
                        WHERE i1.ari_keyid IN (%s)
                        AND i1.ari_date BETWEEN '%s' AND '%s'
                        AND i1.ari_type = 'brand'
                        GROUP BY i1.ari_keyid, i1.ari_date
                        ORDER BY i1.ari_keyid  ASC, i1.ari_date ASC",
                        $ids,
                        $startMonth,
                        $endMonth
                    );
                }
                break;
            case 'series':
                // 检查$ids是否为空，避免SQL语法错误
                if(empty($ids)){
                    $chartData = [];
                    break;
                }
                $sql = sprintf(
                    "SELECT
                    i1.ari_keyid2 as series_id,
                    b2.brand_name as brand_name,
                    bm1.mod1_name as series_name,
                    ari_date,
                    art_hotpv
                    FROM {$tjDb}.at_report_industry_item i1
                    LEFT JOIN {$tjDb}.bc_model1 bm1 ON i1.ari_keyid2 = bm1.mod1_id
                    LEFT JOIN {$tjDb}.bc_brand b2 ON bm1.mod1_brand_id = b2.brand_id
                    WHERE i1.ari_keyid2 IN (%s)
                    AND i1.ari_type = 'brand_model1'
                    AND i1.ari_date BETWEEN '%s' AND '%s'
                    GROUP BY i1.ari_keyid2, i1.ari_date
                    ORDER BY art_hotpv DESC, i1.ari_date ASC",
                    $ids,
                    $startMonth,
                    $endMonth
                );
                break;
            case 'year':
                // 检查$ids是否为空，避免SQL语法错误
                if(empty($ids)){
                    $chartData = [];
                    break;
                }
                $sql = sprintf(
                    "SELECT
                    i1.ari_keyid2 as year_id,
                    b2.brand_name as brand_name,
                    bm1.mod1_name as series_name,
                    bm2.mod2_start_year as start_year,
                    bm2.mod2_end_year as end_year,
                    ari_date,
                    art_hotpv
                    FROM {$tjDb}.at_report_industry_item i1
                    LEFT JOIN {$tjDb}.bc_model2 bm2 ON i1.ari_keyid2 = bm2.mod2_id
                    LEFT JOIN {$tjDb}.bc_model1 bm1 ON bm2.mod2_mod1_id = bm1.mod1_id
                    LEFT JOIN {$tjDb}.bc_brand b2 ON bm1.mod1_brand_id = b2.brand_id
                    WHERE i1.ari_keyid2 IN (%s)
                    AND i1.ari_type = 'brand_model2'
                    AND i1.ari_date BETWEEN '%s' AND '%s'
                    GROUP BY i1.ari_keyid2, ari_date
                    ORDER BY art_hotpv DESC, i1.ari_date ASC",
                    $ids,
                    $startMonth,
                    $endMonth
                );
                break;
            case 'city':
                if($parmas['brand']||$parmas['mod1_id']||$parmas['mod2_id']){
                    // 检查$ids是否为空，避免SQL语法错误
                    if(empty($ids)){
                        $chartData = [];
                        break;
                    }
                    $where1 = sprintf(' ari_city in (%s)', $ids);
                    if($parmas['brand']){
                        $where1 .= " AND bm2.mod2_brand_id IN (".$parmas['brand'].") ";
                    }
                    if($parmas['mod1_id']){
                        $where1 .= " AND bm2.mod2_mod1_id IN (".$parmas['mod1_id'].") ";
                    }
                    if($parmas['mod2_id']){
                        $where1 .= " AND bm2.mod2_id IN (".$parmas['mod2_id'].") ";
                    }
                    
                    $sql = sprintf(
                        "SELECT
                        pc.pc_id as city_id,
                        pc.pc_name,
                        ari_date,
                        SUM(art_hotpv) as art_hotpv
                        FROM {$tjDb}.at_report_industry_month m1
                        JOIN {$tjDb}.bc_model2 bm2 ON ari_mod2_id = mod2_id
                        JOIN {$tjDb}.yp_province_city pc ON m1.ari_city = pc.pc_id
                        WHERE %s
                        AND m1.ari_date BETWEEN '%s' AND '%s'
                        GROUP BY ari_city, ari_date
                        ORDER BY pc_seq,pc_id, ari_date ASC",
                        $where1,
                        $startMonth,
                        $endMonth
                    );
                }else{
                    $sql = sprintf(
                        "SELECT
                        pc.pc_id as city_id,
                        pc.pc_name,
                        ari_date,
                        art_hotpv
                        FROM {$tjDb}.at_report_industry_item m1
                        JOIN {$tjDb}.yp_province_city pc ON m1.ari_keyid2 = pc.pc_id
                        WHERE m1.ari_keyid2 IN (%s) and m1.ari_type='province_city'
                        AND m1.ari_date BETWEEN '%s' AND '%s'
                        GROUP BY ari_keyid2,ari_keyid, ari_date
                        ORDER BY pc_seq,pc_id,ari_date ASC",
                        $ids,
                        $startMonth,
                        $endMonth
                    ); 
                }
                break;
            case 'province':
                if($parmas['mod1_id']||$parmas['mod2_id']){
                    // 检查$ids是否为空，避免SQL语法错误
                    if(empty($ids)){
                        $chartData = [];
                        break;
                    }
                    $where1 = sprintf(' ari_province in (%s)', $ids);
                    
                    if($parmas['mod1_id']){
                        $where1 .= " AND bm2.mod2_mod1_id IN (".$parmas['mod1_id'].") ";
                    }
                    if($parmas['mod2_id']){
                        $where1 .= " AND bm2.mod2_id IN (".$parmas['mod2_id'].") ";
                    }
                    
                    $sql = sprintf(
                        "SELECT
                        pc.pc_id as province_id,
                        pc.pc_name,
                        ari_date,
                        SUM(art_hotpv) as art_hotpv
                        FROM {$tjDb}.at_report_industry_month m1
                        JOIN {$tjDb}.bc_model2 bm2 ON ari_mod2_id = mod2_id
                        JOIN {$tjDb}.yp_province_city pc ON m1.ari_province = pc.pc_id
                        WHERE %s
                        AND m1.ari_date BETWEEN '%s' AND '%s'
                        GROUP BY ari_province, ari_date
                        ORDER BY pc_seq,pc_id, ari_date ASC",
                        $where1,
                        $startMonth,
                        $endMonth
                    );
                }elseif ($parmas['brand']) {
                    // 检查$ids是否为空，避免SQL语法错误
                    if(empty($ids)){
                        $chartData = [];
                        break;
                    }
                    $sql = sprintf(
                        "SELECT
                        pc.pc_id as province_id,
                        pc.pc_name,
                        ari_date,
                        art_hotpv
                        FROM {$tjDb}.at_report_industry_item m1
                        JOIN {$tjDb}.yp_province_city pc ON m1.ari_keyid2 = pc.pc_id
                        WHERE m1.ari_keyid2 IN (%s) and m1.ari_type='brand_province' AND m1.ari_keyid = %d
                        AND m1.ari_date BETWEEN '%s' AND '%s'
                        GROUP BY ari_keyid2, ari_date
                        ORDER BY pc_seq,pc_id, ari_date ASC",
                        $ids,$parmas['brand'],
                        $startMonth,
                        $endMonth
                    );
                }else{
                    // 检查$ids是否为空，避免SQL语法错误
                    if(empty($ids)){
                        $chartData = [];
                        break;
                    }
                    $sql = sprintf(
                        "SELECT
                        pc.pc_id as province_id,
                        pc.pc_name,
                        ari_date,
                        art_hotpv
                        FROM {$tjDb}.at_report_industry_item m1
                        JOIN {$tjDb}.yp_province_city pc ON m1.ari_keyid = pc.pc_id
                        WHERE m1.ari_keyid IN (%s) and m1.ari_type='province'
                        AND m1.ari_date BETWEEN '%s' AND '%s'
                        GROUP BY ari_keyid, ari_date
                        ORDER BY pc_seq,pc_id, ari_date ASC",
                        $ids,
                        $startMonth,
                        $endMonth
                    );
                }
                
                break;
        }
        $chartData = $BaseDb->BaseDb->query($sql);
        if ($chartData) {
            $chartData = $BaseDb->BaseDb->Table($sql);
        }
        // 生成完整的月份数组
        $startYear = substr($startMonth, 0, 4);
        $startMonth = substr($startMonth, 4, 2);
        $endYear = substr($endMonth, 0, 4);
        $endMonth = substr($endMonth, 4, 2);

        $startDate = new DateTime($startYear . '-' . $startMonth . '-01');
        $endDate = new DateTime($endYear . '-' . $endMonth . '-01');
        $xAxis = [];
        while ($startDate <= $endDate) {
            $xAxis[] = $startDate->format('Ym');
            $startDate->modify('+1 month');
        }

        // 封装echarts需要的格式数据
        $echartsData = [];
        $series = [];
        $legend = [];

        // 先初始化所有系列的基本信息
        foreach ($chartData as $item) {
            if (!isset($series[$item[$level . '_id']])) {
                switch ($level) {
                    case 'brand':
                        $series[$item['brand_id']]['name'] = $item['brand_name'];
                        break;
                    case 'series':
                        $series[$item['series_id']]['name'] = $item['series_name'];
                        break;
                    case 'year':
                        $series[$item['year_id']]['name'] = $item['series_name'] . $item['start_year'] . '-' . $item['end_year'];
                        break;
                    case 'city':
                        $series[$item['city_id']]['name'] = $item['pc_name'];
                        break;
                    case 'province':
                        $series[$item['province_id']]['name'] = $item['pc_name'];
                        break;
                }
                $series[$item[$level . '_id']]['type'] = 'line';
                // 为每个系列初始化所有月份的数据为0
                $series[$item[$level . '_id']]['data'] = [];
                foreach ($xAxis as $month) {
                    $series[$item[$level . '_id']]['data'][] = 0;
                }
            }
        }

        // 填充实际数据
        foreach ($chartData as $item) {
            $monthIndex = array_search($item['ari_date'], $xAxis);
            if ($monthIndex !== false) {
                $series[$item[$level . '_id']]['data'][$monthIndex] = intval($item['art_hotpv']);
            }
        }

        // 转换series为数组格式并生成legend
        $seriesArray = [];
        foreach ($series as $value) {
            $legend[] = $value['name'];
            $value['data'] = array_values($value['data']);
            $seriesArray[] = $value;
        }
        $echartsData['legend'] = $legend;
        $echartsData['series'] = $seriesArray;
        $echartsData['xAxis'] = $xAxis;
        // var_dump($echartsData);
        return $echartsData;
    }
}