<?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;
/**
* 描述 : 汇总进销存报表界面
*/
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;