cld/protected/model/invoiceStatistics.php

<?php
Doo::loadCore ( 'db/DooModel' );
class invoiceStatistics extends DooModel {
	public $sid;
	public $priceClass;
	public $date;
	public $cid;
	public $staff;
	public $invoicePrice;
	public $accountPrice;
	public $receivablesPrice;
	public $irid;
	public $_table = 'CLD_invoiceStatistics';
	public $_primarykey = 'sid';
	public $_fields = array (
			'sid',
			'staff',
			'priceClass',
			'invoicePrice',
			'receivablesPrice',
			'accountPrice',
			'date',
			'cid',
			'irid' 
	);
	
	/**
	 * 根据参数字段更新相应字段（主键ID必须传）
	 * @param array $item 相关需要更新的字段信息
	 * @return number 返回发票ID
	 */
	public function setInvoiceStatisticsByCondition($item = array()) {
		$lid = 0;
		if (is_array ( $item ) && ! empty ( $item )) {
			foreach ( $item as $key => $value ) {
				$this->$key = $value;
			}
			$lid = $this->insert ();
		}
		return $lid;
	}
	
	/**
	 * 根据参数字段更新相应字段（主键ID必须传）
	 * @param array $item 相关需要更新的字段信息
	 * @return number 返回发票ID
	 */
	public function updateInvoiceStatisticsByIrid($item = array(), $irid = 0, $priceClass = 0) {
		if (is_numeric ( $irid ) && is_array ( $item ) && ! empty ( $item )) {
			foreach ( $item as $key => $value ) {
				$this->$key = $value;
			}
			$sql = "";
			if (! empty ( $priceClass ))
				$sql = " and priceClass=" . $priceClass;
			$lid = $this->update ( array (
					'where' => 'irid=' . $irid . $sql 
			) );
		}
		return $lid;
	}
	
	/**
	 * 根据发票ID和绑定状态获得收款数据，绑定状态为ALL获取所有状态数据
	 * @param string $iid 发票ID
	 * @param string $bindStatus
	 * @return mixed
	 */
	public function getInvoiceStatisticsByYear($year = "", $cid = "0") {
		$list = array ();
		$sql = '';
		if (empty ( $year ))
			$year = date ( "Y" );
		if ($cid != 0)
			$sql = " and cid=" . $cid;
		
		$list ['statisticsMonthCid'] = $this->find ( array (
				'select' => 'cid,sum(invoicePrice) as invoicePrice,sum(receivablesPrice) as receivablesPrice,sum(accountPrice) as accountPrice,Month(date) as month',
				'where' => " cid!=0 and staff!=0 and  Year(date) =" . $year . $sql,
				'groupby' => 'cid,Month(date)',
				'asArray' => TRUE 
		) );
		
		$list ['statisticsYear'] = $this->find ( array (
				'select' => 'sum(invoicePrice) as invoicePrice,sum(receivablesPrice) as receivablesPrice,sum(accountPrice) as accountPrice,Year(date) as Year',
				'where' => " cid!=0 and staff!=0 and Year(date) =" . $year . $sql,
				'groupby' => 'Year(date)',
				'asArray' => TRUE 
		) );
		
		if (empty ( $list ['statisticsYear'] )) {
			$list ['statisticsYear'] [0] ['invoicePrice'] = 0;
			$list ['statisticsYear'] [0] ['receivablesPrice'] = 0;
			$list ['statisticsYear'] [0] ['accountPrice'] = 0;
		}
		
		$list ['statisticsMonth'] = $this->find ( array (
				'select' => 'sum(invoicePrice) as invoicePrice,sum(receivablesPrice) as receivablesPrice,sum(accountPrice) as accountPrice,Month(date) as month',
				'where' => " cid!=0 and staff!=0 and Year(date) =" . $year . $sql,
				'groupby' => 'Month(date)',
				'asArray' => TRUE 
		) );
		
		return $list;
	}
	
	/**
	 * 获取用户的统计信息
	 * @param string $year
	 * @param string $cid
	 * @return NULL[]
	 */
	public function getInvoiceStatisticsByStaff($year = "", $cid = "0") {
		$list = array ();
		$sql = '';
		if (empty ( $year ))
			$year = date ( "Y" );
		if ($cid != 0)
			$sql = " and cid=" . $cid;
		
		$list ['statisticsMonthCid'] = $this->find ( array (
				'select' => 'cid,sum(invoicePrice) as invoicePrice,sum(receivablesPrice) as receivablesPrice,sum(accountPrice) as accountPrice,Month(date) as month',
				'where' => " cid!=0 and staff!=0 and Year(date) =" . $year . $sql,
				'groupby' => 'cid,Month(date)',
				'asArray' => TRUE 
		) );
		
		$list ['statisticsMonthStaff'] = $this->find ( array (
				'select' => 'cid,staff,sum(invoicePrice) as invoicePrice,sum(receivablesPrice) as receivablesPrice,sum(accountPrice) as accountPrice,Month(date) as month',
				'where' => " cid!=0 and staff!=0 and Year(date) =" . $year . $sql,
				'groupby' => 'staff,Month(date)',
				'asArray' => TRUE 
		) );
		
		$list ['statisticsMonth'] = $this->find ( array (
				'select' => 'sum(invoicePrice) as invoicePrice,sum(receivablesPrice) as receivablesPrice,sum(accountPrice) as accountPrice,Month(date) as month',
				'where' => " cid!=0 and staff!=0 and Year(date) =" . $year . $sql,
				'groupby' => 'Month(date)',
				'asArray' => TRUE 
		) );
		
		return $list;
	}
	public function getInvoiceStatisticsByCategory($year = "", $cid = "0") {
		$list = array ();
		$sql = '';
		if (empty ( $year ))
			$year = date ( "Y" );
		if ($cid != 0)
			$sql = " and cid=" . $cid;
		
		$list ['statisticsMonthCid'] = $this->find ( array (
				'select' => 'cid,sum(invoicePrice) as invoicePrice,sum(receivablesPrice) as receivablesPrice,sum(accountPrice) as accountPrice,Month(date) as month',
				'where' => " cid!=0 and staff!=0 and Year(date) =" . $year . $sql,
				'groupby' => 'cid,Month(date)',
				'asArray' => TRUE 
		) );
		
		$list ['statisticsMonthStaff'] = $this->find ( array (
				'select' => 'cid,staff,sum(invoicePrice) as invoicePrice,sum(receivablesPrice) as receivablesPrice,sum(accountPrice) as accountPrice,Month(date) as month',
				'where' => " cid!=0 and staff!=0 and Year(date) =" . $year . $sql,
				'groupby' => 'staff,Month(date)',
				'asArray' => TRUE 
		) );
		
		$list ['statisticsMonth'] = $this->find ( array (
				'select' => 'sum(invoicePrice) as invoicePrice,sum(receivablesPrice) as receivablesPrice,sum(accountPrice) as accountPrice,Month(date) as month',
				'where' => " cid!=0 and staff!=0 and Year(date) =" . $year . $sql,
				'groupby' => 'Month(date)',
				'asArray' => TRUE 
		) );
		
		return $list;
	}
	
	/**
	 * 获取某年的汇总金额
	 * @param string $year
	 * @param string $cid
	 * @return NULL[]
	 */
	public function getInvoiceStatisticsByTote($year = "", $cid = "0") {
		$list = array ();
		$sql = '';
		if (empty ( $year ))
			$year = date ( "Y" );
		if ($cid != 0)
			$sql = " and cid=" . $cid;
		$list ['statisticsYear'] = $this->find ( array (
				'select' => 'sum(invoicePrice) as invoicePrice,sum(receivablesPrice) as receivablesPrice,sum(accountPrice) as accountPrice,Year(date) as Year',
				'where' => "  Year(date) =" . $year . $sql,
				'groupby' => 'Year(date)',
				'asArray' => TRUE 
		) );
		
		if (empty ( $list ['statisticsYear'] )) {
			$list ['statisticsYear'] [0] ['invoicePrice'] = 0;
			$list ['statisticsYear'] [0] ['receivablesPrice'] = 0;
			$list ['statisticsYear'] [0] ['accountPrice'] = 0;
		}
		
		return $list;
	}
	public function getStatisticsByMonth($sid=0) {
		$month = date ( "m" );
		$year = date ( "Y" );
		if(empty($sid)){
			$list ['statisticsMonth'] [0] ['invoicePrice'] = 0;
			$list ['statisticsMonth'] [0] ['receivablesPrice'] = 0;
			$list ['statisticsMonth'] [0] ['accountPrice'] = 0;
			$list ['statisticsMonth'] [0] ['month'] = $month;
			$list ['statisticsYear'] [0] ['invoicePrice'] = 0;
			$list ['statisticsYear'] [0] ['receivablesPrice'] = 0;
			$list ['statisticsYear'] [0] ['accountPrice'] = 0;
			$list ['statisticsYear'] [0] ['month'] = $month;
			return $list;
		}
		$list ['statisticsMonth'] = $this->find ( array (
				'select' => 'sum(invoicePrice) as invoicePrice,sum(receivablesPrice) as receivablesPrice,sum(accountPrice) as accountPrice,Month(date) as month',
				'where' => " cid!=0 and staff=".$sid." and Year(date) =" . $year . " and Month(date)=" . $month,
				'groupby' => 'Month(date)',
				'asArray' => TRUE 
		) );
		if (empty ( $list ['statisticsMonth'] )) {
			$list ['statisticsMonth'] [0] ['invoicePrice'] = 0;
			$list ['statisticsMonth'] [0] ['receivablesPrice'] = 0;
			$list ['statisticsMonth'] [0] ['accountPrice'] = 0;
			$list ['statisticsMonth'] [0] ['month'] = $month;
		}
		$list ['statisticsYear'] = $this->find ( array (
				'select' => 'sum(invoicePrice) as invoicePrice,sum(receivablesPrice) as receivablesPrice,sum(accountPrice) as accountPrice,Year(date) as Year',
				'where' => " cid!=0 and staff=".$sid." and Year(date) =" . $year,
				'groupby' => 'Year(date)',
				'asArray' => TRUE 
		) );
		if (empty ( $list ['statisticsYear'] )) {
			$list ['statisticsYear'] [0] ['invoicePrice'] = 0;
			$list ['statisticsYear'] [0] ['receivablesPrice'] = 0;
			$list ['statisticsYear'] [0] ['accountPrice'] = 0;
			$list ['statisticsYear'] [0] ['month'] = $month;
		}
		return $list;
	}
	public function getBrieflyStatistics($sid=0) {
		$year = date ( "Y" );
		$month = date ( "m" );
		
		$tmp_date = date ( "Ym" );
		$tmp_year = substr ( $tmp_date, 0, 4 );
		$tmp_mon = substr ( $tmp_date, 4, 2 );
		$tmp_forwardmonth = mktime ( 0, 0, 0, $tmp_mon - 1, 1, $tmp_year );
		$fm_forward_month = date ( "m", $tmp_forwardmonth );
		
		$list ['statisticsMonth'] = $this->find ( array (
				'select' => 'sum(invoicePrice) as invoicePrice,sum(receivablesPrice) as receivablesPrice,sum(accountPrice) as accountPrice,Month(date) as month',
				'where' => " cid!=0 and staff='.$sid.' and Year(date) =" . $year . " and Month(date)=" . $fm_forward_month,
				'groupby' => 'Month(date)',
				'asArray' => TRUE 
		) );
		if (empty ( $list ['statisticsMonth'] )) {
			$list ['statisticsMonth'] [0] ['invoicePrice'] = 0;
			$list ['statisticsMonth'] [0] ['receivablesPrice'] = 0;
			$list ['statisticsMonth'] [0] ['accountPrice'] = 0;
			$list ['statisticsMonth'] [0] ['month'] = $fm_forward_month;
		}
		
		return $list;
	}
	public function getInvoiceStatisticsByCondition($condition = '') {
		if (empty ( $condition ))
			return array ();
		
		$list = $this->find ( array (
				'where' => $condition,
				'asArray' => TRUE 
		) );
		return $list;
	}
	
	/**
	 * 获得办事处下成员本月的开票汇总
	 * @param number $cid
	 */
	public function getISMSByCategoryByMonth($cid = 0, $month) {
		if (empty ( $cid ))
			return array ();
		
		//date_format(column,'%Y-%m')=
// 		$sql = 'select sum(invoicePrice) as invoicePrice,sum(receivablesPrice) as receivablesPrice,sum(accountPrice) as accountPrice ,cid,staff,Month(date) as month
// 				from ' . $this->_table . '
// 				where Month(date)=' . $month . ' and cid = ' . $cid . '
// 					GROUP BY staff';
			$sql = 'select sum(invoicePrice) as invoicePrice,sum(receivablesPrice) as receivablesPrice,sum(accountPrice) as accountPrice ,cid,staff,Month(date) as month
				from ' . $this->_table . '
				where date_format(date,"%Y-%m")=' . $month . ' and cid = ' . $cid . '
					GROUP BY staff';
			//echo $sql;
		$query = Doo::db ()->query ( $sql );
		return $result = $query->fetchAll ();
	}
	
	/**
	 * 获得办事处下成员本月的开票汇总
	 * @param number $cid
	 */
	public function getISMSByCategory($cid = 0) {
		if (empty ( $cid ))
			return array ();
		$month = date ( "m" );
		$year=date('Y');
		
		$sql = 'select sum(invoicePrice) as invoicePrice,sum(receivablesPrice) as receivablesPrice,sum(accountPrice) as accountPrice ,cid,staff,Month(date) as month
				from ' . $this->_table . '
				where Month(date)=' . $month . ' and Year(date)='.$year.' and cid = ' . $cid . '
					GROUP BY staff';
		$query = Doo::db ()->query ( $sql );
		//echo $sql;
		return $result = $query->fetchAll ();
	}
	
	/**
	 * 获得办事处成员全部个人汇总数据
	 * @param unknown $cid
	 */
	public function getISASByCategory($cid) {
		if (empty ( $cid ))
			return array ();
		$month = date ( "m" );
		
		$sql = 'select sum(invoicePrice) as invoicePrice,sum(receivablesPrice) as receivablesPrice,sum(accountPrice) as accountPrice ,cid,staff,Month(date) as month
				from ' . $this->_table . '
				where cid = ' . $cid . '
					GROUP BY staff';
		$query = Doo::db ()->query ( $sql );
		return $result = $query->fetchAll ();
	}
	
	/**
	 * 获得某办事处本月汇总
	 */
	public function getInvoiceStatistiscsMothByCategory($cid = 0) {
		if (empty ( $cid ))
			return array ();
		
		$month = date ( "m" );
		
		$sql = 'select sum(invoicePrice) as invoicePrice,sum(receivablesPrice) as receivablesPrice,sum(accountPrice) as accountPrice ,cid,Month(date) as month
				from ' . $this->_table . '
				where Month(date)=' . $month . ' and cid = ' . $cid . ' and staff!=0 
					GROUP BY cid';
		$query = Doo::db ()->query ( $sql );
		//echo $sql;
		return $result = $query->fetch ();
	}
}

?>