Location: PHPKode > projects > BambooInvoice > bamboo_system_files/application/models/invoices_model.php
<?php
class invoices_model extends Model {

	function invoices_model()
	{
		parent::Model();
		$this->obj =& get_instance();
	}

	// --------------------------------------------------------------------

	function addInvoice($invoice_data)
	{
		if ($this->db->insert('invoices', $invoice_data))
		{
			return $this->db->insert_id();
		}
		else
		{
			return FALSE;
		}
	}

	function addInvoiceItem($invoice_items)
	{
		if ($this->db->insert('invoice_items', $invoice_items))
		{
			return TRUE;
		}
		else
		{
			return FALSE;
		}
	}

	// --------------------------------------------------------------------

	function updateInvoice($invoice_id, $invoice_data)
	{
		$this->db->where('id', $invoice_id);

		if ($this->db->update('invoices', $invoice_data))
		{
			return $invoice_id;
		}
		else
		{
			return FALSE;
		}
	}

	// --------------------------------------------------------------------

	function payment($invoice_data)
	{
		if ($this->db->insert('invoice_payments', $invoice_data))
		{
			return $invoice_data['invoice_id'];
		}
		else
		{
			return FALSE;
		}
	}

	// --------------------------------------------------------------------

	function delete_invoice($invoice_id)
	{
		$this->db->where('invoice_id', $invoice_id);
		$this->db->delete('invoice_payments'); // remove invoice payments

		$this->db->where('invoice_id', $invoice_id);
		$this->db->delete('invoice_histories'); // remove invoice_histories info

		$this->db->where('id', $invoice_id);
		$this->db->delete('invoices'); // remove invoice info

		$this->delete_invoice_items($invoice_id); // remove invoice items
	}

	// --------------------------------------------------------------------

	function delete_invoice_items($invoice_id)
	{
		$this->db->where('invoice_id', $invoice_id);
		$this->db->delete('invoice_items');
	}

	// --------------------------------------------------------------------

	function getSingleInvoice($invoice_id)
	{
		$this->db->select('invoices.*, clients.name, clients.address1, clients.address2, clients.city, clients.country, clients.province, clients.website, clients.postal_code, clients.tax_code');
		$this->db->select('(SELECT SUM('.$this->db->dbprefix('invoice_payments').'.amount_paid) FROM '.$this->db->dbprefix('invoice_payments').' WHERE '.$this->db->dbprefix('invoice_payments').'.invoice_id=' . $invoice_id . ') AS amount_paid', FALSE); 
		$this->db->select('TO_DAYS('.$this->db->dbprefix('invoices').'.dateIssued) - TO_DAYS(curdate()) AS daysOverdue', FALSE);
		$this->db->select('(SELECT SUM('.$this->db->dbprefix('invoice_items').'.amount * '.$this->db->dbprefix('invoice_items').'.quantity) FROM '.$this->db->dbprefix('invoice_items').' WHERE '.$this->db->dbprefix('invoice_items').'.invoice_id=' . $invoice_id . ') AS total_notax', FALSE);
		$this->db->select('(SELECT SUM('.$this->db->dbprefix('invoice_items').'.amount * '.$this->db->dbprefix('invoice_items').'.quantity * ('.$this->db->dbprefix('invoices').'.tax1_rate/100 * '.$this->db->dbprefix('invoice_items').'.taxable)) FROM '.$this->db->dbprefix('invoice_items').' WHERE '.$this->db->dbprefix('invoice_items').'.invoice_id=' . $invoice_id . ') AS total_tax1', FALSE);
		$this->db->select('(SELECT SUM('.$this->db->dbprefix('invoice_items').'.amount * '.$this->db->dbprefix('invoice_items').'.quantity * ('.$this->db->dbprefix('invoices').'.tax2_rate/100 * '.$this->db->dbprefix('invoice_items').'.taxable)) FROM '.$this->db->dbprefix('invoice_items').' WHERE '.$this->db->dbprefix('invoice_items').'.invoice_id=' . $invoice_id . ') AS total_tax2', FALSE);
		$this->db->select('(SELECT SUM('.$this->db->dbprefix('invoice_items').'.amount * '.$this->db->dbprefix('invoice_items').'.quantity + ROUND(('.$this->db->dbprefix('invoice_items').'.amount * '.$this->db->dbprefix('invoice_items').'.quantity * ('.$this->db->dbprefix('invoices').'.tax1_rate/100 + '.$this->db->dbprefix('invoices').'.tax2_rate/100) * '.$this->db->dbprefix('invoice_items').'.taxable), 2)) FROM '.$this->db->dbprefix('invoice_items').' WHERE '.$this->db->dbprefix('invoice_items').'.invoice_id=' . $invoice_id . ') AS total_with_tax', FALSE);

		$this->db->join('clients', 'invoices.client_id = clients.id');
		$this->db->join('invoice_items', 'invoices.id = invoice_items.invoice_id', 'left');
		$this->db->join('invoice_payments', 'invoices.id = invoice_payments.invoice_id', 'left');
		$this->db->groupby('invoices.id'); 
		$this->db->where('invoices.id', $invoice_id);

		return $this->db->get('invoices');
	}

	// --------------------------------------------------------------------

	function build_short_descriptions()
	{
		$limit = ($this->config->item('short_description_characters') != '') ? $this->config->item('short_description_characters') : 50;

		$short_descriptions = array();

		$this->db->select('invoice_id, work_description', FALSE);
		$this->db->group_by('invoice_id');
		
		foreach($this->db->get('invoice_items')->result() as $short_desc)
		{
			$short_descriptions[$short_desc->invoice_id] = ($limit == 0) ? '' : '['.character_limiter($short_desc->work_description, $limit).']';
		}

		return $short_descriptions;
	}

	// --------------------------------------------------------------------

	function getInvoiceItems($invoice_id)
	{

		$this->db->where('invoice_id', $invoice_id);
		$this->db->order_by('id', 'ASC');

		$items = $this->db->get('invoice_items');

		if ($items->num_rows() > 0)
		{
			return $items;
		}
		else
		{
			return FALSE;
		}
	}

	// --------------------------------------------------------------------

	function getInvoiceHistory($invoice_id)
	{
		$this->db->where('invoice_histories.invoice_id', $invoice_id);
		$this->db->orderby('date_sent');

		return $this->db->get('invoice_histories');
	}

	// --------------------------------------------------------------------

	function getInvoicePaymentHistory($invoice_id)
	{
		$this->db->where('invoice_id', $invoice_id);
		$this->db->orderby('date_paid');

		return $this->db->get('invoice_payments');
	}

	// --------------------------------------------------------------------

	function getInvoices($status, $days_payment_due = 30, $offset=0, $limit=100)
    {
		return $this->_getInvoices(FALSE, FALSE, $status, $days_payment_due, $offset, $limit);
	}

	// --------------------------------------------------------------------

	function getInvoicesAJAX ($status, $client_id, $days_payment_due = 30)
	{
		return $this->_getInvoices(FALSE, $client_id, $status, $days_payment_due);
	}

	// --------------------------------------------------------------------

	function _getInvoices($invoice_id, $client_id, $status, $days_payment_due = 30, $offset=0, $limit=100)
    {
		// check for any invoices first
		if ($this->db->count_all_results('invoices') < 1)
		{
			return FALSE;
		}

		if (is_numeric($invoice_id))
		{
			$this->db->where('invoices.id', $invoice_id);
		}

		if (is_numeric($client_id))
		{
			$this->db->where('client_id', $client_id);
		}
		else
		{
			$this->db->where('client_id IS NOT NULL');
		}

		if ($status == 'overdue')
		{
			$this->db->having("daysOverdue <= -$days_payment_due AND (ROUND(amount_paid, 2) < ROUND(subtotal, 2) OR amount_paid is null)", '', FALSE);
		}
		elseif ($status == 'open')
		{
			$this->db->having("(ROUND(amount_paid, 2) < ROUND(subtotal, 2) or amount_paid is null)", '', FALSE);
		}
		elseif ($status == 'closed')
		{
			$this->db->having('ROUND(amount_paid, 2) >= ROUND(subtotal, 2)', '', FALSE);
		}

		$this->db->select('invoices.*, clients.name');
		$this->db->select('(SELECT SUM(amount_paid) FROM '.$this->db->dbprefix('invoice_payments').' WHERE invoice_id='.$this->db->dbprefix('invoices').'.id) AS amount_paid', FALSE); 
		$this->db->select('TO_DAYS('.$this->db->dbprefix('invoices').'.dateIssued) - TO_DAYS(curdate()) AS daysOverdue', FALSE);
		$this->db->select('ROUND((SELECT SUM('.$this->db->dbprefix('invoice_items').'.amount * '.$this->db->dbprefix('invoice_items').'.quantity + ('.$this->db->dbprefix('invoice_items').'.amount * '.$this->db->dbprefix('invoice_items').'.quantity * ('.$this->db->dbprefix('invoices').'.tax1_rate/100 + '.$this->db->dbprefix('invoices').'.tax2_rate/100) * '.$this->db->dbprefix('invoice_items').'.taxable)) FROM '.$this->db->dbprefix('invoice_items').' WHERE '.$this->db->dbprefix('invoice_items').'.invoice_id='.$this->db->dbprefix('invoices').'.id), 2) AS subtotal', FALSE);

		$this->db->join('clients', 'invoices.client_id = clients.id');
		$this->db->join('invoice_items', 'invoices.id = invoice_items.invoice_id', 'left');
		$this->db->join('invoice_payments', 'invoices.id = invoice_payments.invoice_id', 'left');

		$this->db->order_by('dateIssued desc, invoice_number desc');
		$this->db->groupby('invoices.id'); 
		$this->db->offset($offset);
		$this->db->limit($limit);

		return $this->db->get('invoices');
	}

	// --------------------------------------------------------------------

	function lastInvoiceNumber($client_id)
	{
		if ($this->config->item('unique_invoice_per_client') === TRUE)
		{
			$this->db->where('client_id', $client_id);
		}

		$this->db->where('invoice_number != ""');
		$this->db->orderby("id", "desc"); 
		$this->db->limit(1);

		$query = $this->db->get('invoices');

		if ($query->num_rows() > 0)
		{
			return $query->row()->invoice_number;
		}
		else
		{
			return '0';
		}
	}

	// --------------------------------------------------------------------

	function uniqueInvoiceNumber($invoice_number)
	{
		$this->db->where('invoice_number', $invoice_number);

		$query = $this->db->get('invoices');

		$num_rows = $query->num_rows();

		if ($num_rows == 0)
		{
			return TRUE;
		}
		else
		{
			return FALSE;
		}
	}

	// --------------------------------------------------------------------

	function uniqueInvoiceNumberEdit($invoice_number, $invoice_id)
	{
		$this->db->where('invoice_number', $invoice_number);
		$this->db->where('id != ', $invoice_id);
		$query = $this->db->get('invoices');

		$num_rows = $query->num_rows();

		if ($num_rows == 0)
		{
			return TRUE;
		}
		else
		{
			return FALSE;
		}
	}}
?>
Return current item: BambooInvoice