<?php
/**
 * Class invoice model
 */
class invoiceMod extends mod{
	function __construct(){
		$e = require(mvc::$cfg['ROOT'].'mod/element/invoice.php');
		parent::__construct($e);
    }
	/**
	 * Method 列表数据
	 * @param string $where 查询sql条件
	 * @param int $page 页码(从1开始)
	 * @param int $pagesize 分页大小
	 * @param string $sidx 排序字段(单个)
	 * @param string $sord 排序方式 asc|desc
	 * @param string $orderby 排序sql(可多个)[优先]
	 * @return array 数据集
	 */
	public function list($where='',$page=0,$pagesize=0,$sidx='',$sord='asc',$orderby=''){
		$sql = $this->e['view'];
		if(trim($where)!='') {
			if(strtolower(substr(trim($where),0,3))!=='and') $where = ' AND ' . $where;
			$sql .= $where;
		}
		if($sidx=='' && $orderby=="") $orderby =  $this->e['order']; //默认排序
		if($orderby!=''){
			$sql.= sprintf(' ORDER BY %s', $orderby);
		}else if($sidx!=''){
			$sql.= sprintf(' ORDER BY %s %s',$sidx,$sord);
		}
		if($page>0 && $pagesize>0){
			$pages = $this->dbRead->PageLimit($sql,$page,$pagesize);
			$sql .= $pages['limit'];
		}
		$rows = $this->dbRead->Table($sql);
		$footerData=[];
		$data = [
			'page'=> $pages['page'],
			'total'=> $pages['total'],
			'records' => $pages['records'],
			"userdata"=> $footerData,
			'rows' => $rows
		];
		return $data;
	}
	/**
	 * Method 月报表
	 */
	function report($begin,$end){
		$sql = sprintf("SELECT SUM(inv_money) FROM invoice WHERE inv_object='宜配' and inv_state='已开票' AND MID(inv_date,1,7)>='%s' AND MID(inv_date,1,7)<='%s'",
			$begin,$end);
		$yp_sum = $this->dbRead->One($sql);

		$sql = sprintf("SELECT IFNULL(SUM(inv_money),0) FROM invoice WHERE inv_object='宜配' and  inv_state='待开票'");
		$yp_none = $this->dbRead->One($sql);

		$sql = sprintf("SELECT IFNULL(SUM(inv_money),0) FROM invoice WHERE inv_object='宜配' AND inv_state='已开票' AND MID(inv_date,1,7)='%s'",$end);
		$yp_month = $this->dbRead->One($sql);

		$sql = sprintf("SELECT SUM(inv_money) FROM invoice WHERE inv_object='跑街' and inv_state='已开票' AND MID(inv_date,1,7)>='%s' AND MID(inv_date,1,7)<='%s'",
			$begin,$end);
		$pjl_sum = $this->dbRead->One($sql);

		$sql = sprintf("SELECT IFNULL(SUM(inv_money),0) FROM invoice WHERE inv_object='跑街' and  inv_state='待开票'");
		$pjl_none = $this->dbRead->One($sql);

		$sql = sprintf("SELECT IFNULL(SUM(inv_money),0) FROM invoice WHERE inv_object='跑街' AND inv_state='已开票' AND MID(inv_date,1,7)='%s'",$end);
		$pjl_month = $this->dbRead->One($sql);
		
		$rows[] = [
			'title'=> sprintf("%s - %s 开票金额",$begin,$end),
			'yp'=>$yp_sum,
			'pjl'=>$pjl_sum,
			'total'=>$yp_sum+$pjl_sum,
		];
		$rows[] = [
			'title'=> sprintf("累计待开票金额"),
			'yp'=>$yp_none,
			'pjl'=>$pjl_none,
			'total'=>$yp_none+$pjl_none,
		];
		$rows[] = [
			'title'=> sprintf("本月开票金额"),
			'yp'=>$yp_month,
			'pjl'=>$pjl_month,
			'total'=>$yp_month+$pjl_month,
		];

		$data = [
			'page'=> $pages['page'],
			'total'=> $pages['total'],
			'records' => $pages['records'],
			'rows' => $rows
		];
		return $data;
	}
	function monthReport($begin,$end){
		$sql.= sprintf("SELECT
			inv_date,c1,c2, ROUND(c1/(c1+c2)*100) b1, ROUND(c2/(c1+c2)*100) b2, (c1+c2) total
			FROM
			(
				SELECT
				inv_date,
				IFNULL((SELECT SUM(inv_money)  FROM invoice WHERE inv_object='宜配' AND CONCAT(MID(inv_date,1,4),MID(inv_date,6,2))=t1.inv_date),0) c1,
				IFNULL((SELECT SUM(inv_money)  FROM invoice WHERE inv_object='跑街' AND CONCAT(MID(inv_date,1,4),MID(inv_date,6,2))=t1.inv_date),0) c2
				FROM
				  (SELECT inv_object,CONCAT(MID(inv_date,1,4),MID(inv_date,6,2)) inv_date,SUM(inv_money) FROM `invoice` WHERE inv_date>='%s' 
				  AND inv_date<='%s' AND inv_state='已开票' GROUP BY  MID(inv_date,1,7)) t1
			) t2 order by inv_date desc", $begin, $end);
		
		$rows = $this->dbRead->Table($sql);
		$footerData=[];
		$data = [
			'page'=> $pages['page'],
			'total'=> $pages['total'],
			'records' => $pages['records'],
			"userdata"=> $footerData,
			'rows' => $rows
		];
		return $data;
	}

}