汇总报表源码

<?php
/**
 * 描述 : 汇总进销存报表界面
 */
class report_overseasbritish extends L{
    public function index(){
        $this->view->_pagingHtml = $this->hzPaging();
        $this->display('/overseasBritish.html');
    }
    /**
     * 描述 : 下载汇总进销存报表
     * 作者 : zhoulong
     */
    public function hzDownload(){
        $db='ENG002';
        if( empty( $_GET['search']) ) {
            $sql = '';
        }else if( $_GET['search']['whId'] == '001' ){
            $db = 'ENG002';
        }else{
            $db = 'USA001';
        }
        $sql = '';
        if( !empty($_GET['search']['sku'] )&& !empty( $_GET['search']['bestPuDateBegin'] ) && !empty( $_GET['search']['bestPuDateEnd']) ){
                $sql[] = '`sku` LIKE \'%'.strtoupper(trim($_GET['search']['sku'])).'%\'';
                $sql[] = 'FROM_UNIXTIME( s.out_time, \'%Y-%m-%d %H:%i:%s\' ) >= \''.$_GET['search']['bestPuDateBegin'].'\'';
                $sql[] = 'FROM_UNIXTIME( s.out_time, \'%Y-%m-%d %H:%i:%s\' )<= \''.$_GET['search']['bestPuDateEnd'].'\'';
                $sql = 'AND ' . join(' AND ', $sql);
            }else if ( !empty($_GET['search']['sku']) ){
                $sql[] = '`sku` LIKE \'%'.strtoupper(trim($_GET['search']['sku'])).'%\'';
                $sql = 'AND ' . join(' AND ', $sql);
            }else if ( !empty($_GET['search']['bestPuDateBegin'] ) && !empty( $_GET['search']['bestPuDateEnd']) ){
                $sql[] = 'FROM_UNIXTIME( s.out_time, \'%Y-%m-%d %H:%i:%s\' ) >= \''.$_GET['search']['bestPuDateBegin'].'\'';
                $sql[] = 'FROM_UNIXTIME( s.out_time, \'%Y-%m-%d %H:%i:%s\' )<= \''.$_GET['search']['bestPuDateEnd'].'\'';
                $sql = 'AND ' . join(' AND ', $sql);
            }

        $qty=$this->sku($db);
        $sql = "SELECT k.goods_sn,
            sum( CASE WHEN stockin_type =1 THEN so.goods_qty ELSE 0 END ) AS procurement,                        
            sum( CASE WHEN stockin_type =2 THEN so.goods_qty ELSE 0 END ) AS returns ,                           
            sum( CASE WHEN stockin_type =3 THEN so.goods_qty ELSE 0 END ) AS inventoryprofit,                    
            sum( CASE WHEN stockin_type =4 THEN so.goods_qty ELSE 0 END ) AS other,                              
            sum( CASE WHEN stockin_type =5 THEN so.goods_qty ELSE 0 END ) AS transfers,
            sum( so.goods_qty) AS amount,
            sum( CASE WHEN stockout_type =1 THEN do.goods_qty ELSE 0 END ) AS salesout,                        
            sum( CASE WHEN stockout_type =2 THEN do.goods_qty ELSE 0 END ) AS transfersout,                           
            sum( CASE WHEN stockout_type =3 THEN do.goods_qty ELSE 0 END ) AS lossout,                    
            sum( CASE WHEN stockout_type =4 THEN do.goods_qty ELSE 0 END ) AS otherout,                              
            sum( do.goods_qty) AS amountout             
        FROM
             myr_depot_stock  AS g
                LEFT JOIN myr_goods AS k ON
                  k.goods_id=g.goods_id
                LEFT JOIN myr_stockout  AS d ON
                    d.order_id=k.goods_id
                LEFT JOIN myr_stockout_detail AS do ON
                    d.order_id = do.order_id
                LEFT JOIN myr_stockin AS s ON
                    s.order_id = k.goods_id
                LEFT JOIN myr_stockin_detail AS so ON
                    so.order_id = s.order_id    
            WHERE
                k.goods_sn in (".$qty.")
                {$sql}
            GROUP BY
                k.goods_sn ";

        of_base_com_csv::download(array(
            'SKU','采购入库数量','调拨入库数量','盘盈入库数量',
            '退货入库数量','其它入库数量','汇总入库数量','销售出库数量',
            '调拨出库数量','盘亏出库数量','其它出库数量','汇总出库数量'
        ));
        while(of_base_com_com::eachSql($sql,$data,$db)) {                                                                //同步销量数据
            foreach($data as &$v) of_base_com_csv::download($v);
        }
    }
    /**
     * 描述 : 汇总进销存分页
     * 作者 :
     */
    public function hzPaging(&$params = array()) {
            $db='ENG002';
        if(empty($params['search'])) {
            $sql = '';
        }else if( $params['search']['whId'] == '001' ){
            $db = 'ENG002';
        }else{
            $db = 'USA001';
        }
        $sql ='';
        if(!empty($params['search']['sku'])&& !empty($params['search']['bestPuDateBegin']) && !empty($params['search']['bestPuDateEnd'])){
            $sql[] = '`sku` LIKE \'%'.strtoupper(trim($params['search']['sku'])).'%\'';
            $sql[] = 'FROM_UNIXTIME( s.out_time, \'%Y-%m-%d %H:%i:%s\' ) >= \''.$params['search']['bestPuDateBegin'].'\'';
            $sql[] = 'FROM_UNIXTIME( s.out_time, \'%Y-%m-%d %H:%i:%s\' )<= \''.$params['search']['bestPuDateEnd'].'\'';
            $sql = 'AND ' . join(' AND ', $sql);
        }else if(!empty($params['search']['sku'])){
            $sql[] = '`sku` LIKE \'%'.strtoupper(trim($params['search']['sku'])).'%\'';
            $sql = 'AND ' . join(' AND ', $sql);
        }else if(!empty($params['search']['bestPuDateBegin']) && !empty($params['search']['bestPuDateEnd'])){
            $sql[] = 'FROM_UNIXTIME( s.out_time, \'%Y-%m-%d %H:%i:%s\' ) >= \''.$params['search']['bestPuDateBegin'].'\'';
            $sql[] = 'FROM_UNIXTIME( s.out_time, \'%Y-%m-%d %H:%i:%s\' )<= \''.$params['search']['bestPuDateEnd'].'\'';
            $sql = 'AND ' . join(' AND ', $sql);
            }

        $qty=$this->sku($db);
        $sql = "SELECT k.goods_sn,
            sum( CASE WHEN stockin_type =1 THEN so.goods_qty ELSE 0 END ) AS procurement,                        
            sum( CASE WHEN stockin_type =2 THEN so.goods_qty ELSE 0 END ) AS returns ,                           
            sum( CASE WHEN stockin_type =3 THEN so.goods_qty ELSE 0 END ) AS inventoryprofit,                    
            sum( CASE WHEN stockin_type =4 THEN so.goods_qty ELSE 0 END ) AS other,                              
            sum( CASE WHEN stockin_type =5 THEN so.goods_qty ELSE 0 END ) AS transfers,
            sum( so.goods_qty) AS amount,
            sum( CASE WHEN stockout_type =1 THEN do.goods_qty ELSE 0 END ) AS salesout,                        
            sum( CASE WHEN stockout_type =2 THEN do.goods_qty ELSE 0 END ) AS transfersout,                           
            sum( CASE WHEN stockout_type =3 THEN do.goods_qty ELSE 0 END ) AS lossout,                    
            sum( CASE WHEN stockout_type =4 THEN do.goods_qty ELSE 0 END ) AS otherout,                              
            sum( do.goods_qty) AS amountout             
        FROM
            myr_depot_stock  AS g
                LEFT JOIN myr_goods AS k ON
                  k.goods_id=g.goods_id
                LEFT JOIN myr_stockout  AS d ON
                    d.order_id=k.goods_id
                LEFT JOIN myr_stockout_detail AS do ON
                    d.order_id = do.order_id
                LEFT JOIN myr_stockin AS s ON
                    s.order_id = k.goods_id
                LEFT JOIN myr_stockin_detail AS so ON
                    so.order_id = s.order_id    
            WHERE
                k.goods_sn in (".$qty.")
                {$sql}
            GROUP BY
                k.goods_sn ";

        $config = array(
            'SKU'      => array(
                '_attr' => array(
                    'body' => '{`goods_sn`}',
                    'sort' => 'goods_sn'
                )
            ),
            '期初数量'    => array(
                '_attr' => array(
                    'body' => '',
                )
            ),
            '采购入库数量'  => array(
                '_attr' => array(
                    'body' =>'{`procurement`}',
                )
            ),
            '调拨入库数量'=> array(
                '_attr' => array(
                    'body' => '{`transfers`}',
                )
            ),
            '盘盈入库数量'  => array(
                '_attr' => array(
                    'body' => '{`inventoryprofit`}',
                )
            ),
            '退货入库数量'  => array(
                '_attr' => array(
                    'body' =>'{`returns`}',
                )
            ),
            '其它入库数量'=> array(
                '_attr' => array(
                    'body' => '{`other`}',
                )
            ),
            '汇总入库数量' => array(
                '_attr' => array(
                    'body' => '{`amount`}',
                )
            ),
            '销售出库数量' => array(
                '_attr' => array(
                    'body' => '{`salesout`}',
                )
            ),
            '调拨出库数量' => array(
                '_attr' => array(
                    'body' => '{`transfersout`}',
                )
            ),
            '盘亏出库数量' => array(
                '_attr' => array(
                    'body' => '{`lossout`}',
                )
            ),
            '其它出库数量'=> array(
                '_attr' => array(
                    'body' => '{`otherout`}',
                )
            ),
            '汇总出库数量' => array(
                '_attr' => array(
                    'body' => '{`amountout`}',
                )
            ),
            '期末库存数量' => array(
                '_attr' => array(
                    'body' => '',
                )
            ),
            '_attr' => array(
                'attr'   => 'id="hzPaging" class="table table-hover table-striped table-scroll jsHoverDetails textC"',
                'data'   => &$sql,
                'dbPool' => $db,
                'params' => &$params,
                'method' => __METHOD__
            )
        );
        return of_base_com_com::paging($config);
    }
    /**
     * 描述 : 获取汇总全部SKU
     * 作者 :
     */
    public function sku($db){
        $str = '';
        $sql= "SELECT  d.goods_sn
        FROM
            myr_depot_stock AS g
        LEFT JOIN myr_goods AS d ON
            g.goods_id = d.goods_id";
        $qty= &of_db::sql($sql, $db);
        foreach ($qty as $a=>$sku) {
           $str .= "'".$sku['goods_sn']."',";
    }
        $str=substr($str,0,-1);
        return $str;
    }
}
return true;