<?php
/**
 *  出货数据统计
 */
class Order_ShippingCountController extends Ec_Controller_Action
{
    public function preDispatch()
    {
        $this->tplDirectory = "order/views/shippingcount/";
        $this->serviceClass = new Service_ShippingCount();
    }

    public function indexAction()
    {
        $shippingCountDate = Service_ShippingCount::getByCondition(array(), 'DISTINCT(date) AS date', 0, 1, 'date', '');
        $shippingCountDate = array_reduce($shippingCountDate, create_function('$v, $w', '$v[]=$w["date"];return $v;'));
        $this->view->shippingCountDate = json_encode($shippingCountDate);
        echo Ec::renderTpl($this->tplDirectory . "index.tpl", 'layout');
    }

    // 任务赶 有时间再优化吧 现在就用流程语句一路走到底了
    public function rankingExportAction()
    {
        if ($this->_request->isGet()){
            $dateFrom = trim($this->_request->getParam('dateFrom',''));
            $dateTo = trim($this->_request->getParam('dateTo',''));
            try {
                $condition['dateFrom'] = $dateFrom;
                $condition['dateTo'] = $dateTo;

                $filename = "shippingCountRanking".date("YmdHis");
                require_once("PHPExcel.php");
                require_once("PHPExcel/Reader/Excel2007.php");
                require_once("PHPExcel/Reader/Excel5.php");
                require_once ('PHPExcel/IOFactory.php');

                $objExcel = new PHPExcel();
                $objProps = $objExcel->getProperties();
                $objProps->setCreator('EC');
                $objProps->setTitle('Template');
                $objExcel->setActiveSheetIndex(0);
                $objExcel->getActiveSheet()->setTitle('统计排名');
                $objActSheet = $objExcel->getActiveSheet();

                // 设置默认样式 左右垂直居中
                $objActSheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
                    ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);


                $flag = 'quantity';
                $type = array(
                    'shipping_count.customer_code',
                    'customer.customer_company_name',
                    'SUM('.$flag.') AS '.$flag
                    );
                // 获取统计数据
                $dataTotal = Service_ShippingCount::getTotal($condition, $type, $flag);
                $customerCompanyName = array_reduce($dataTotal, create_function('$v,$w', '$v[$w["customer_code"]]=$w["customer_company_name"];return $v;'));
                // 获取月份统计数据
                $dataGroupByDate = Service_ShippingCount::getGroupByDate($condition, $flag);
                // 数据格式化 $dataGroupByDate[customer_code][date] = $flag
                $dataGroupByDateFormat = array_reduce($dataGroupByDate, create_function('$v, $w', '$v[$w["customer_code"]][$w["date"]]=$w["'.$flag.'"];return $v;'));
                // $dataGroupByDate 内 date 字段 组成的 一维数组
                $monthArr = array_flip(array_flip(array_reduce($dataGroupByDate, create_function('$v, $w', '$v[] =$w["date"];return $v;'))));
                $monthArr = $this->dateFromAndToArray($dateFrom, $dateTo, $monthArr);
                // 统计$monthArr内 年的月份集合 如 ['2015'=>['2015-11', '2015-12'], '2016'=> ['2016-01']……]
                $yearInfo = array_reduce($monthArr, create_function('$v, $w', '$v[substr($w, 0, 4)][]=$w;return $v;'));


                // A1 合并单元格 tip: 方法联用 数值要减一
                $col = 1;
                $thisLetter = 'A';
                $objActSheet->mergeCells($thisLetter.$col.':' . $this->getExcelLetter('A', 3 + count($monthArr)) . '1')
                    ->setCellValue($thisLetter.$col, '招海出口融资项目经营数据统计');

                $col++;
                //A2 合并单元格
                $objActSheet->mergeCells($thisLetter.$col.':'.$thisLetter.($col+1))
                    ->setCellValue($thisLetter.$col, '排名');
                $thisLetter = $this->getExcelLetter($thisLetter, 1);
                $objActSheet->mergeCells($thisLetter.$col.':'.$thisLetter.($col+1))
                    ->setCellValue($thisLetter.$col, '品牌商')
                    ->getColumnDimension($thisLetter)->setAutoSize(true);
                $thisLetter = $this->getExcelLetter($thisLetter, 1);
                $objActSheet->mergeCells($thisLetter.$col.':'.$thisLetter.($col+1))
                    ->setCellValue($thisLetter.$col, '合计');

                // 根据数据源设置 年
                $preLetter = $thisLetter;
                foreach ($yearInfo as $yearKey => $yearRow) {
                    $mergeLetterFrom = $this->getExcelLetter($preLetter, 1);
                    $mergeLetterTo = $this->getExcelLetter($preLetter, count($yearRow));
                    $objActSheet->mergeCells($mergeLetterFrom.$col.":".$mergeLetterTo.$col)
                    ->setCellValue($mergeLetterFrom.$col, $yearKey.'出货数量(台)');
                    $preLetter = $mergeLetterTo;

                    // 填写月份
                    foreach ($yearRow as $monthRow) {
                        $objActSheet->setCellValue($mergeLetterFrom.($col+1), (int)substr($monthRow, -2).'月份');
                        $mergeLetterFrom = $this->getExcelLetter($mergeLetterFrom, 1);
                    }
                }
                $thisLetter = $this->getExcelLetter($preLetter, 1);
                $objActSheet->mergeCells($thisLetter.$col.":".$thisLetter.($col+1))
                    ->setCellValue($thisLetter.$col, (int)substr($monthRow, -2).'月增长百分比')
                    ->getColumnDimension($thisLetter)->setWidth(12);

                $col += 2;
                // 填值
                foreach ($dataTotal as $dataTotalKey => $dataTotalRow) {
                    $thisLetter = 'A';
                    $objActSheet->setCellValue($thisLetter.$col, $dataTotalKey+1)
                                ->setCellValue(($thisLetter = $this->getExcelLetter($thisLetter, 1)). $col, $customerCompanyName[$dataTotalRow['customer_code']])
                                ->setCellValue(($thisLetter = $this->getExcelLetter($thisLetter, 1)). $col, $dataTotalRow[$flag]);
                    foreach ($yearInfo as $yearKey => $yearRow) {
                        foreach ($yearRow as $monthRow) {
                            $objActSheet->setCellValue(($thisLetter = $this->getExcelLetter($thisLetter, 1)). $col, $dataGroupByDateFormat[$dataTotalRow['customer_code']][$monthRow]?$dataGroupByDateFormat[$dataTotalRow['customer_code']][$monthRow]:0);
                        }
                    }
                    $col++;
                }
                // 合计
                $thisLetter = 'B';
                $objActSheet->setCellValue($thisLetter.$col, '合计');

                for ($i=count($monthArr)+1; $i>0; $i--) {
                    $objActSheet->setCellValue(($thisLetter = $this->getExcelLetter($thisLetter, 1)). $col, "=SUM(".$thisLetter.($col - count($dataTotal)).":".$thisLetter.($col-1).")");
                }
                // 统计最后一月增长百分比
                $preLetter = $this->getExcelLetter($thisLetter, -1);
                $nextLetter = $this->getExcelLetter($thisLetter, 1);
                for ($i=count($dataTotal)+1; $i>0; $i--) {
                    $colNum = $col - $i + 1;
                    $preLetterValue = $objActSheet->getCell($preLetter.$colNum)->getValue();
                    $thisLetterValue = $objActSheet->getCell($thisLetter.$colNum)->getValue();
                    if ($preLetterValue == 0 && $thisLetterValue == 0) {
                        $percent = "\t0%";
                    } elseif ($preLetterValue == 0 && $thisLetterValue != 0) {
                        $percent = "\t100%";
                    } elseif ($preLetterValue != 0 && $thisLetterValue == 0) {
                        $percent = "\t-100%";
                    } else {
                        $percent = "\t" . sprintf("%1\$.2f%%", ($thisLetterValue-$preLetterValue)/$preLetterValue * 100);
                    }
                    if (is_numeric($preLetterValue)) {
                        $objActSheet->setCellValue($nextLetter.$colNum, $percent);
                    } else {
                        $objActSheet->setCellValue($nextLetter.$colNum, "=CONCATENATE(ROUND((".(substr($thisLetterValue, 1) . "-" .substr($preLetterValue, 1)) . ")/" . substr($preLetterValue, 1) .'*100,2), RIGHT('. $nextLetter.($colNum-1) .', 1))');
                    }
                }


                // 第二个表 copy start
                $flag = 'product_value';
                $type = array(
                    'shipping_count.customer_code',
                    'customer.customer_company_name',
                    'SUM('.$flag.') AS '.$flag
                    );
                // 获取统计数据
                $dataTotal = Service_ShippingCount::getTotal($condition, $type, $flag);
                $customerCompanyName = array_reduce($dataTotal, create_function('$v,$w', '$v[$w["customer_code"]]=$w["customer_company_name"];return $v;'));
                // 获取月份统计数据
                $dataGroupByDate = Service_ShippingCount::getGroupByDate($condition, $flag);
                // 数据格式化 $dataGroupByDate[customer_code][date] = $flag
                $dataGroupByDateFormat = array_reduce($dataGroupByDate, create_function('$v, $w', '$v[$w["customer_code"]][$w["date"]]=$w["'.$flag.'"];return $v;'));
                // $dataGroupByDate 内 date 字段 组成的 一维数组
                $monthArr = array_flip(array_flip(array_reduce($dataGroupByDate, create_function('$v, $w', '$v[] =$w["date"];return $v;'))));
                $monthArr = $this->dateFromAndToArray($dateFrom, $dateTo, $monthArr);
                // 统计$monthArr内 年的月份集合 如 ['2015'=>['2015-11', '2015-12'], '2016'=> ['2016-01']……]
                $yearInfo = array_reduce($monthArr, create_function('$v, $w', '$v[substr($w, 0, 4)][]=$w;return $v;'));


                $col = count($dataTotal)+7;
                $thisLetter = 'A';
                //A2 合并单元格
                $objActSheet->mergeCells($thisLetter.$col.':'.$thisLetter.($col+1))
                    ->setCellValue($thisLetter.$col, '排名');
                $thisLetter = $this->getExcelLetter($thisLetter, 1);
                $objActSheet->mergeCells($thisLetter.$col.':'.$thisLetter.($col+1))
                    ->setCellValue($thisLetter.$col, '品牌商');
                $thisLetter = $this->getExcelLetter($thisLetter, 1);
                $objActSheet->mergeCells($thisLetter.$col.':'.$thisLetter.($col+1))
                    ->setCellValue($thisLetter.$col, '合计');

                // 根据数据源设置 年
                $preLetter = $thisLetter;
                foreach ($yearInfo as $yearKey => $yearRow) {
                    $mergeLetterFrom = $this->getExcelLetter($preLetter, 1);
                    $mergeLetterTo = $this->getExcelLetter($preLetter, count($yearRow));
                    $objActSheet->mergeCells($mergeLetterFrom.$col.":".$mergeLetterTo.$col)
                    ->setCellValue($mergeLetterFrom.$col, $yearKey.'货值(USD)');
                    $preLetter = $mergeLetterTo;

                    // 填写月份
                    foreach ($yearRow as $monthRow) {
                        $objActSheet->setCellValue($mergeLetterFrom.($col+1), (int)substr($monthRow, -2).'月份');
                        $mergeLetterFrom = $this->getExcelLetter($mergeLetterFrom, 1);
                    }
                }
                $thisLetter = $this->getExcelLetter($preLetter, 1);
                $objActSheet->mergeCells($thisLetter.$col.":".$thisLetter.($col+1))
                    ->setCellValue($thisLetter.$col, (int)substr($monthRow, -2).'月增长百分比');

                $col += 2;
                // 填值
                foreach ($dataTotal as $dataTotalKey => $dataTotalRow) {
                    $thisLetter = 'A';
                    $objActSheet->setCellValue($thisLetter.$col, $dataTotalKey+1)
                                ->setCellValue(($thisLetter = $this->getExcelLetter($thisLetter, 1)). $col, $customerCompanyName[$dataTotalRow['customer_code']])
                                ->setCellValue(($thisLetter = $this->getExcelLetter($thisLetter, 1)). $col, $dataTotalRow[$flag]);
                    foreach ($yearInfo as $yearKey => $yearRow) {
                        foreach ($yearRow as $monthRow) {
                            $objActSheet->setCellValue(($thisLetter = $this->getExcelLetter($thisLetter, 1)). $col, $dataGroupByDateFormat[$dataTotalRow['customer_code']][$monthRow]?$dataGroupByDateFormat[$dataTotalRow['customer_code']][$monthRow]:0);
                        }
                    }
                    $col++;
                }
                // 合计
                $thisLetter = 'B';
                $objActSheet->setCellValue($thisLetter.$col, '合计');

                for ($i=count($monthArr)+1; $i>0; $i--) {
                    $objActSheet->setCellValue(($thisLetter = $this->getExcelLetter($thisLetter, 1)). $col, "=SUM(".$thisLetter.($col - count($dataTotal)).":".$thisLetter.($col-1).")");
                }
                // 统计最后一月增长百分比
                $preLetter = $this->getExcelLetter($thisLetter, -1);
                $nextLetter = $this->getExcelLetter($thisLetter, 1);
                for ($i=count($dataTotal)+1; $i>0; $i--) {
                    $colNum = $col - $i + 1;
                    $preLetterValue = $objActSheet->getCell($preLetter.$colNum)->getValue();
                    $thisLetterValue = $objActSheet->getCell($thisLetter.$colNum)->getValue();
                    if ($preLetterValue == 0 && $thisLetterValue == 0) {
                        $percent = "\t0%";
                    } elseif ($preLetterValue == 0 && $thisLetterValue != 0) {
                        $percent = "\t100%";
                    } elseif ($preLetterValue != 0 && $thisLetterValue == 0) {
                        $percent = "\t-100%";
                    } else {
                        $percent = "\t" . sprintf("%1\$.2f%%", ($thisLetterValue-$preLetterValue)/$preLetterValue * 100);
                    }
                    if (is_numeric($preLetterValue)) {
                        $objActSheet->setCellValue($nextLetter.$colNum, $percent);
                    } else {
                        $objActSheet->setCellValue($nextLetter.$colNum, "=CONCATENATE(ROUND((".(substr($thisLetterValue, 1) . "-" .substr($preLetterValue, 1)) . ")/" . substr($preLetterValue, 1) .'*100,2), RIGHT('. $nextLetter.($colNum-1) .', 1))');
                    }
                }

                // copy end
                header('Pragma:public');
                header('Content-Type:application/x-msexecl;name="' . $filename . '.xls');
                header("Content-Disposition:inline;filename=" . $filename . '.xls');
                $objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
                $objWriter->save('php://output');
                exit;
            } catch (Exception $e) {
                echo $e->getMessage();
                exit;
            }
        }
    }

    // 验证是否存在导出数据
    public function shippingExportValidateAction()
    {
        if ($this->_request->isPost()){
            $customerCode = trim($this->_request->getParam('customerCode',''));
            $date = trim($this->_request->getParam('shippingDate',''));
            $condition['customer_code'] = $customerCode;
            $condition['date'] = $date;
            $result  = array('status' => 0, 'message' => '');
            $dataTotal = Service_ShippingCount::getByCondition($condition, 'COUNT(*)');
            if ($dataTotal[0]['COUNT(*)'] > 0) {
                $result['status'] = 1;
            } else {
                $result['message'] = 'have no data.';
            }
            echo json_encode($result);
            exit;
        }
    }


    public function shippingExportAction()
    {
        if ($this->_request->isGet()){
            $customerCode = trim($this->_request->getParam('customerCode',''));
            $date = trim($this->_request->getParam('shippingDate',''));
            $type = array(
                'quantity',
                'product_value'
                );
            if (empty($customerCode)) {
                $type[] = 'shipping_count.customer_code';
            } else {
                $customerRow = Service_Customer::getByField($customerCode, 'customer_code', array('customer_company_name', 'customer_email'));
            }

            $condition['customer_code'] = $customerCode;
            $condition['date'] = $date;
            $dataTotal = Service_ShippingCount::getWithProductInfo($condition, $type, 'shipping_count.customer_code', 'shipping_count.product_id');

            try {
                $filename = "shippingCount".date("YmdHis");
                require_once("PHPExcel.php");
                require_once("PHPExcel/Reader/Excel2007.php");
                require_once("PHPExcel/Reader/Excel5.php");
                require_once ('PHPExcel/IOFactory.php');

                $objExcel = new PHPExcel();
                $objProps = $objExcel->getProperties();
                $objProps->setCreator('EC');
                $objProps->setTitle('Template');
                $objExcel->setActiveSheetIndex(0);
                $objActSheet = $objExcel->getActiveSheet();

                // 设置默认样式 左右垂直居中
                $objActSheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
                        ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

                $objActSheet->setCellValue('A1', '客户名称')
                            ->setCellValue('B1', '客户邮箱')
                            ->setCellValue('C1', '客户代码')
                            ->setCellValue('D1', '产品SKU')
                            ->setCellValue('E1', '产品名称')
                            ->setCellValue('F1', '数量')
                            ->setCellValue('G1', '货值(USD)')
                            ->setCellValue('H1', '申报要素(型号)');
                $objActSheet->getColumnDimension('A')->setAutoSize(true);
                $objActSheet->getColumnDimension('B')->setAutoSize(true);
                $objActSheet->getColumnDimension('C')->setAutoSize(true);
                $objActSheet->getColumnDimension('D')->setAutoSize(true);
                $objActSheet->getColumnDimension('E')->setAutoSize(true);
                $objActSheet->getColumnDimension('G')->setAutoSize(true);
                $objActSheet->getColumnDimension('H')->setAutoSize(true);
                foreach ($dataTotal as $key => $val) {
                    $objActSheet->setCellValue('A'.($key+2), isset($customerRow)?$customerRow['customer_company_name']:$val['customer_company_name'])
                        ->setCellValue('B'.($key+2), isset($customerRow)?$customerRow['customer_email']:$val['customer_email'])
                        ->setCellValue('C'.($key+2), empty($customerCode)?$val['customer_code']:$customerCode)
                        ->setCellValue('D'.($key+2), $val['product_sku'])
                        ->setCellValue('E'.($key+2), $val['product_title'])
                        ->setCellValue('F'.($key+2), $val['quantity'])
                        ->setCellValue('G'.($key+2), $val['product_value'])
                        ->setCellValue('H'.($key+2), $val['hem_detail']);
                }
                header('Pragma:public');
                header('Content-Type:application/x-msexecl;name="' . $filename . '.xls');
                header("Content-Disposition:inline;filename=" . $filename . '.xls');
                $objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
                $objWriter->save('php://output');
                exit;
            } catch (Exception $e) {
                echo $e->getMessage();
                exit;
            }
        }
    }

    // 返回 $dateFrom 至 $dateTo 之间 且在 $array 内的 以月为单位的 date 数组
    private function dateFromAndToArray($dateFrom, $dateTo, $dateArr)
    {
        $result = array();
        if ($dateFrom == '0000-00') {
            $result[] = $dateFrom;
            $timeFrom = strtotime('2000-01');
        } else {
            $timeFrom = strtotime($dateFrom);
        }
        $timeTo = strtotime($dateTo);
        while ($timeFrom <= $timeTo) {
            $dateFrom = date('Y-m', $timeFrom);
            if (in_array($dateFrom, $dateArr)) {
                $result[] = $dateFrom;
            }
            if ($dateFrom == '0000-00') {
                $timeFrom = strtotime('2000-01');
            } else {
                $timeFrom = strtotime("$dateFrom + 1 month");
            }
        }
        return $result;
    }

    // 返回excel表头字母
    private function getExcelLetter ($preLetter, $num)
    {
        return PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($preLetter) + $num - 1);
    }
}