导出包材报表

//Controller
class Product_PackageController extends Zend_Controller_Action {
    //导出包材报表
    public function exportPackagingMaterialsAction() {
        $type = $this->_request->getParam('type', '');
        $warehouseId = $this->_request->getParam('userWarehouse', '');
        $productSku = $this->_request->getParam('product_sku', '');
        $startDate = $this->_request->getParam('start_date', '');
        $endDate = $this->_request->getParam('end_date', '');
        if (!$warehouseId) {
            die('请选择仓库');
        }
        if ($startDate && $endDate) {
            $combination = array(
                'warehouse_id' => $warehouseId,
                'product_sku' => $productSku,
                'start_date' => $startDate . ' 00:00:00',
                'end_date' => $endDate . ' 23:59:59',
            );
        } else {
            $combination = array(
                'warehouse_id' => $warehouseId,
                'product_sku' => $productSku,
                'start_date' => date('Y-m-01 00:00:00'),
                'end_date' => date('Y-m-d 23:59:59'),
            );
        }
        if ($type == 0) {
            if (!$productSku) {
                die('请填写 SKU');
            }
            //导出指定 SKU 详细报表
            Product_Service_PackagingMaterials::exportSpecifiedPackagingMaterialsReport($combination);
        } else {
            //导出包材汇总表
            Product_Service_PackagingMaterials::exportPackagingMaterialsCount($combination);
        }
    }
}


Html
<input class="button" type="button" value="导出指定 SKU 详细报表" onclick="exportPackagingMaterials(0)" style="width: 4cm">
&nbsp;&nbsp;
<input class="button" type="button" value="导出包材汇总表" onclick="exportPackagingMaterials(1)" style="width: 3cm">


js
//导出包材报表
function exportPackagingMaterials(type) {
    var warehouse_id = $("#userWarehouse").find("option:selected").val();
    var product_sku = $("#product_sku").val();
    var start_date = $("#start_date").val();
    var end_date = $("#end_date").val();
    window.open("/product/package/export-packaging-materials/userWarehouse/" + warehouse_id + "/product_sku/" + product_sku + "/start_date/" + start_date + "/end_date/" + end_date + "/type/" + type);
}


Service
class Product_Service_PackagingMaterials {
    //导出指定 SKU 详细报表
    public static function exportSpecifiedPackagingMaterialsReport($combination) {
        $pmIds = self::getCombination($combination, '*');
        $list = 3;
        $pmType = '';
        if ($pmIds) {
            include_once('../libs/PHPExcel_1.8.0/PHPExcel.php');
            $objPHPExcel = new PHPExcel();
            $sheet = $objPHPExcel->getActiveSheet();
            //背景色
            $objPHPExcel->getActiveSheet()->getStyle('A1:P1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('#8B1A1A');
            $objPHPExcel->getActiveSheet()->getStyle('A2:P2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('#8B1A1A');
            //左对齐
            $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
            $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
            //字体颜色
            $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB('FFFFFFF');
            $objPHPExcel->getActiveSheet()->getStyle('H')->getFont()->getColor()->setARGB('FFFF0000');
            $objPHPExcel->getActiveSheet()->getStyle('J')->getFont()->getColor()->setARGB('FFFF0000');
            //合并单元格
            $objPHPExcel->getActiveSheet()->mergeCells('B1:D1');
            $objPHPExcel->getActiveSheet()->mergeCells('E1:G1');
            $objPHPExcel->getActiveSheet()->mergeCells('H1:J1');
            $objPHPExcel->getActiveSheet()->mergeCells('K1:P1');
            //宽度
            $sheet->getColumnDimension('C')->setWidth(20);
            $sheet->getColumnDimension('N')->setWidth(20);
            $sheet->getColumnDimension('O')->setWidth(20);
            $sheet->getColumnDimension('P')->setWidth(20);
            $sheet->setCellValue('A1', 'SKU');
            $sheet->setCellValue('A2', '序号');
            $sheet->setCellValue('B1', $combination['product_sku']);
            $sheet->setCellValue('B2', '日期');
            $sheet->setCellValue('C2', '产品名称');
            $sheet->setCellValue('D2', '摘要');
            $sheet->setCellValue('E1', '本月入库');
            $sheet->setCellValue('E2', '数量');
            $sheet->setCellValue('F2', '单价');
            $sheet->setCellValue('G2', '金额');
            $sheet->setCellValue('H1', '本月出库');
            $sheet->setCellValue('H2', '数量');
            $sheet->setCellValue('I2', '单价');
            $sheet->setCellValue('J2', '金额');
            $sheet->setCellValue('K1', '期末结存');
            $sheet->setCellValue('K2', '数量');
            $sheet->setCellValue('L2', '单价');
            $sheet->setCellValue('M2', '金额');
            $sheet->setCellValue('N2', '备注');
            $sheet->setCellValue('O2', '申请人');
            $sheet->setCellValue('P2', '审批人');

            $data[0] = array(
                'pm_create_date' => date('Y-m-t', strtotime('-1 months', strtotime($combination['start_date']))),
                'pm_type' => 10,
                'pm_end_semester_quantity' => empty($pmIds[0]['pm_beginning_quantity']) ? 0 : $pmIds[0]['pm_beginning_quantity'],
                'pm_end_semester_price' => empty($pmIds[0]['pm_beginning_price']) ? 0 : $pmIds[0]['pm_beginning_price'],
                'pm_end_semester_gold' => empty($pmIds[0]['pm_beginning_gold']) ? 0 : $pmIds[0]['pm_beginning_gold'],
            );
            $row = array_merge($data, $pmIds);
            foreach ($row as $value) {
                if ($value['pm_type'] == 0) {
                    $pmType = '入库';
                } else if ($value['pm_type'] == 1) {
                    $pmType = '出库';
                } else if ($value['pm_type'] == 2 || $value['pm_type'] == 3) {
                    $pmType = '盘点';
                } else {
                    $pmType = '结存';
                }
                $sheet->setCellValue('A' . $list, $list - 2);
                $sheet->setCellValue('B' . $list, date('n月d', strtotime($value['pm_create_date'])));
                $sheet->setCellValue('C' . $list, $value['product_title_cn']);
                $sheet->setCellValue('D' . $list, $pmType);
                if ($value['pm_type'] == 0 || $value['pm_type'] == 2) {
                    $sheet->setCellValue('E' . $list, $value['pm_quantity']);
                    $sheet->setCellValue('F' . $list, $value['pm_price']);
                    $sheet->setCellValue('G' . $list, $value['pm_gold']);
                    $sheet->setCellValue('H' . $list);
                    $sheet->setCellValue('I' . $list);
                    $sheet->setCellValue('J' . $list);
                } else if ($value['pm_type'] == 1 || $value['pm_type'] == 3) {
                    $sheet->setCellValue('E' . $list);
                    $sheet->setCellValue('F' . $list);
                    $sheet->setCellValue('G' . $list);
                    $sheet->setCellValue('H' . $list, '-' . $value['pm_quantity']);
                    $sheet->setCellValue('I' . $list, $value['pm_price']);
                    $sheet->setCellValue('J' . $list, '-' . $value['pm_gold']);
                } else {
                    $sheet->setCellValue('E' . $list);
                    $sheet->setCellValue('F' . $list);
                    $sheet->setCellValue('G' . $list);
                    $sheet->setCellValue('H' . $list);
                    $sheet->setCellValue('I' . $list);
                    $sheet->setCellValue('J' . $list);
                }
                $sheet->setCellValue('K' . $list, $value['pm_end_semester_quantity']);
                $sheet->setCellValue('L' . $list, $value['pm_end_semester_price']);
                $sheet->setCellValue('M' . $list, $value['pm_end_semester_gold']);
                $sheet->setCellValue('N' . $list, $value['pm_code']);
                $sheet->setCellValue('O' . $list, $value['pm_applicant']);
                $sheet->setCellValue('P' . $list, $value['pm_approver']);
                $list++;
            }
            $warehouseNameCn = Warehouse_Service_Warehouse::getById($combination['warehouse_id']);
            $objPHPExcel->createSheet();
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            header('Content-Type: application/vnd.ms-excel;charset=utf8');
            header('Content-Disposition: attachment;filename=' . date('Y-m-d') . '导出' . $warehouseNameCn['warehouse_name_cn'] . '耗材 ' . $combination['product_id'] . ' 详细报表.xls');
            $objWriter->save('php://output');
        } else {
            die('未获取到对应数据');
        }
    }

    //导出包材汇总表
    public static function exportPackagingMaterialsCount($combination) {
        $pmIds = Product_Service_PackagingMaterials::getCombination($combination, '*');
        $list = 2;
        if ($pmIds) {
            include_once('../libs/PHPExcel_1.8.0/PHPExcel.php');
            $objPHPExcel = new PHPExcel();
            $sheet = $objPHPExcel->getActiveSheet();
            //左对齐
            $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
            //宽度
            $sheet->getColumnDimension('B')->setWidth(20);
            $sheet->setCellValue('A1', '月份');
            $sheet->setCellValue('B1', '产品 SKU');
            $sheet->setCellValue('C1', '期初库存数量');
            $sheet->setCellValue('D1', '期初库存金额');
            $sheet->setCellValue('E1', '入库数量汇总');
            $sheet->setCellValue('F1', '入库金额汇总');
            $sheet->setCellValue('G1', '出库数量汇总');
            $sheet->setCellValue('H1', '出库金额汇总');
            $sheet->setCellValue('I1', '期末库存数量');
            $sheet->setCellValue('J1', '期末库存金额');

            $row = array();
            $data = array();
            foreach ($pmIds as $key => $value) {
                $key = date('n月', strtotime($value['pm_create_date']));
                $product_sku = $value['product_sku'];
                if (!isset($row[$key][$product_sku])) {
                    $row[$key][$product_sku]['pm_beginning_quantity'] = $value['pm_beginning_quantity'];
                    $row[$key][$product_sku]['pm_beginning_gold'] = $value['pm_beginning_gold'];
                }
                $data[$key][$product_sku]['beginning_inventory_quantity'] = $row[$key][$product_sku]['pm_beginning_quantity'] < 0 ? 0 : $row[$key][$product_sku]['pm_beginning_quantity'];
                $data[$key][$product_sku]['beginning_inventory_gold'] = $row[$key][$product_sku]['pm_beginning_gold'] < 0 ? 0 : $row[$key][$product_sku]['pm_beginning_gold'];
                if (!isset($data[$key][$product_sku]['into_quantity_count'])) {
                    $data[$key][$product_sku]['into_quantity_count'] = '';
                    $data[$key][$product_sku]['into_gold_count'] = '';
                }
                if (!isset($data[$key][$product_sku]['outbound_quantity_count'])) {
                    $data[$key][$product_sku]['outbound_quantity_count'] = '';
                    $data[$key][$product_sku]['outbound_gold_count'] = '';
                }
                if ($value['pm_type'] == 0 || $value['pm_type'] == 2) {
                    $data[$key][$product_sku]['into_quantity_count'] += $value['pm_quantity'];
                    $data[$key][$product_sku]['into_gold_count'] += $value['pm_gold'];
                } else {
                    $data[$key][$product_sku]['outbound_quantity_count'] += $value['pm_quantity'];
                    $data[$key][$product_sku]['outbound_gold_count'] += $value['pm_gold'];
                }
                $data[$key][$product_sku]['end_semester_inventory_quantity'] = $value['pm_end_semester_quantity'];
                $data[$key][$product_sku]['end_semester_inventory_gold'] = $value['pm_end_semester_gold'];
            }
            ksort($data);
            foreach ($data as $ke => $valu) {
                foreach ($valu as $k => $val) {
                    $sheet->setCellValue('A' . $list, $ke);
                    $sheet->setCellValue('B' . $list, $k);
                    $sheet->setCellValue('C' . $list, $val['beginning_inventory_quantity']);
                    $sheet->setCellValue('D' . $list, $val['beginning_inventory_gold']);
                    $sheet->setCellValue('E' . $list, $val['into_quantity_count']);
                    $sheet->setCellValue('F' . $list, $val['into_gold_count']);
                    $sheet->setCellValue('G' . $list, $val['outbound_quantity_count']);
                    $sheet->setCellValue('H' . $list, $val['outbound_gold_count']);
                    $sheet->setCellValue('I' . $list, $val['end_semester_inventory_quantity']);
                    $sheet->setCellValue('J' . $list, $val['end_semester_inventory_gold']);
                    $list++;
                }
            }
            $warehouseNameCn = Warehouse_Service_Warehouse::getById($combination['warehouse_id']);
            $objPHPExcel->createSheet();
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            header('Content-Type: application/vnd.ms-excel;charset=utf8');
            header('Content-Disposition: attachment;filename=' . date('Y-m-d') . '导出' . $warehouseNameCn['warehouse_name_cn'] . '包材汇总表.xls');
            $objWriter->save('php://output');
        } else {
            die('未获取到对应数据');
        }
    }

}