<?php
/**
 * Class balance model
 */
class balanceMod extends mod{
	function __construct(){
		$e = require(mvc::$cfg['ROOT'].'mod/element/balance.php');
		parent::__construct($e);
    	//上次结算余额到指定日期的发生额sql, 值 cycle, 要还< = <=等符号
    	//在余额表中有的科目与没有在余额表中的科目都要进来
    	//p_balance 上期余额
    	//v_balance 本期发生的余额
    	//balance 余额
    	$this->happenMoneySql = "
				SELECT 
				IFNULL(b_subject,v_subject) `subject`
				,t1.*
				,CASE WHEN s_inc=1 AND s_direction=1 THEN v_total_j - v_total_d WHEN s_inc=1 AND s_direction=0 THEN v_total_d - v_total_j ELSE 0 END v_balance
				,CASE WHEN s_inc=1 AND s_direction=1 THEN v_total_j - v_total_d + p_balance WHEN s_inc=1 AND s_direction=0 THEN v_total_d - v_total_j + p_balance ELSE 0 END balance
				,CASE WHEN s_direction=1 THEN v_total_j - v_total_d + p_balance WHEN s_direction=0 THEN v_total_d - v_total_j + p_balance ELSE 0 END balance_happen
				,s.*
				FROM 
				(
					SELECT b_id,b_subject,IFNULL(b_j,0) b_j,IFNULL(b_d,0) b_d,IFNULL(b_balance,0) p_balance
					,v_id,v_subject,IFNULL(v_cycle,0) v_cycle,IFNULL(v_total_j,0) v_total_j,IFNULL(v_total_d,0) v_total_d
					FROM `balance` b
					LEFT OUTER JOIN (
					   SELECT v_id,v_cycle,v_subject,SUM(v_j) v_total_j,SUM(v_d) v_total_d 
					   FROM `voucher` 
					   WHERE EXISTS( SELECT 1 FROM `voucher_checkout` HAVING(v_cycle > MAX(vc_cycle)) )  AND v_cycle %s AND v_state=1 GROUP BY v_subject
					)v ON b.b_subject = v.v_subject
					WHERE EXISTS( SELECT 1 FROM `voucher_checkout` HAVING(b.b_cycle = MAX(vc_cycle)) )
					
					UNION ALL
					
					SELECT b_id,b_subject,IFNULL(b_j,0) b_j,IFNULL(b_d,0) b_d,IFNULL(b_balance,0) p_balance
					,v_id,v_subject,IFNULL(v_cycle,0) v_cycle,IFNULL(v_total_j,0) v_total_j,IFNULL(v_total_d,0) v_total_d
					FROM (
					   SELECT v_id,v_cycle,v_subject,SUM(v_j) v_total_j,SUM(v_d) v_total_d 
					   FROM `voucher` 
					   WHERE EXISTS( SELECT 1 FROM `voucher_checkout` HAVING(v_cycle > MAX(vc_cycle)) )  AND v_cycle %s AND v_state=1 GROUP BY v_subject
					)v
					LEFT OUTER JOIN `balance` b ON b.b_subject = v.v_subject
					WHERE b_id IS NULL OR b_id<1
				) t1
				JOIN `view_subject` s ON (s_id = t1.b_subject OR s_id = t1.v_subject)
				WHERE 1=1";
    }
	/**
	 * 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($on, $where='',$page=0,$pagesize=0,$sidx='',$sord='asc',$orderby=''){
		$sql = $this->e['view'];
		if($on!=''){
			$sql = str_replace('b_subject = s.s_id','b_subject = s.s_id'.$on,$sql);
		}
		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'];
		}
		//print_r($sql);
		$rows = $this->dbRead->Table($sql);
		$footerData=[];
		$data = [
			'page'=> $pages['page'],
			'total'=> $pages['total'],
			'records' => $pages['records'],
			"userdata"=> $footerData,
			'rows' => $rows
		];
		return $data;
	}
	public function listNoCheckout($cycle, $where='',$page=0,$pagesize=0,$sidx='',$sord='asc',$orderby=''){
		$sql = sprintf($this->happenMoneySql,'<='.$cycle,'<='.$cycle);
		$sql = "SELECT * FROM (SELECT s.s_parent_name s_parent_name2 
						,s.s_name s_name2 
						,s.s_parent_keyword s_parent_keyword2 
						,s.s_keyword s_keyword2 
						,T_.*  FROM `view_subject` s LEFT OUTER JOIN (".$sql.") T_ ON s.s_id=T_.subject) T2_ WHERE 1=1";
		if(trim($where)!='') {
			if(strtolower(substr(trim($where),0,3))!=='and') $where = ' AND ' . $where;
			$sql .= $where;
		}
		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'];
		}
		//print_r($sql);
		$rows = $this->dbRead->Table($sql);
		$footerData=[];
		$data = [
			'page'=> $pages['page'],
			'total'=> $pages['total'],
			'records' => $pages['records'],
			"userdata"=> $footerData,
			'rows' => $rows
		];
		return $data;
	}
	/**
	 * Method 未结算的期列表
	 */	
	public function getNoCheckoutCycels(){
		$sql = "SELECT v_cycle FROM `voucher` WHERE v_cycle>(SELECT MAX(vc_cycle) FROM `voucher_checkout`)  GROUP BY v_cycle ORDER BY v_cycle DESC";
		$data = $this->dbRead->Table($sql);
		return $data;
	}
	/**
	 * Method 结算
	 */	
	public function checkout($cycle){
		if(empty($cycle)) 
			return 2001;
		$sql = sprintf($this->happenMoneySql, '=' .$cycle, '=' .$cycle);
		$rows =  $this->dbRead->Table($sql);
		$total_j = 0;
		$total_d = 0;
		foreach($rows as $row){
			$total_j += $row['v_total_j'] * 1;
			$total_d += $row['v_total_d'] * 1;
		}
		if($total_j.""!=$total_d.""){
			return 2004;
		}

		foreach($rows as $row){
			$data = [
				'b_cycle' => $cycle,
				'b_subject' => $row['subject'],
				'b_j' => $row['v_total_j'],     
				'b_d' => $row['v_total_d'],       
				'b_direction' => $row['s_direction'],   
				'b_p_balance' => $row['p_balance'], 
				'b_v_balance' => $row['v_balance'], 
				'b_balance' => $row['balance'], 
				'b_balance_happen' => $row['balance_happen'], 
			];
			$this->addRow($data);
		}
		$this->addRowSelectTable('voucher_checkout',['vc_cycle'=>$cycle]);
		return 0;
	}
	/**
	 * Method 反结算
	 */	
	public function backCheckout($cycle){
		if(empty($cycle)) return false;
		$this->deleteRows("b_cycle=".$cycle);
		$this->deleteRowsSelectTable("voucher_checkout","vc_cycle=".$cycle);
		return true;
	}
}