<?php
defined('BASEPATH') OR exit('No direct script access allowed');

	class invoice extends CI_Model
	{
		//posts info
		private $id;
		private $invNo;
		private $customerId;
		private $type;
		private $note;
		private $paymentNote;
		private $paymentMethod;
		private $date;
		private $duedate;
		private $generated_invoice;
		private $proof;
		
		
		private $itemId;
		private $item;
		private $itemDesc;
		private $price;
		private $qty;
		private $discount;
		private $vat;
		
		private $field;
		private $value;		

		function setID($id){$this->id = $id;}
		function setInvNo($invNo){$this->invNo = $invNo;}
		function setCustomerId($customerId){$this->customerId = $customerId;}
		function setInvType($type){$this->type = $type;}
		function setNote($note){$this->note = $note;}
		function setPaymentNote($paymentNote){$this->paymentNote = $paymentNote;}
		function setPaymentMethod($paymentMethod){$this->paymentMethod = $paymentMethod;}
		function setDate($date){$this->date = $date;}
		function setDuedate($duedate){$this->duedate = $duedate;}
		function setProof($proof){$this->proof = $proof;}
		
		function setItemId($itemId){$this->itemId = $itemId;}
		function setItem($item){$this->item = $item;}
		function setItemDesc($itemDesc){$this->itemDesc = $itemDesc;}
		function setPrice($price){$this->price = $price;}
		function setQty($qty){$this->qty = $qty;}
		function setDiscount($discount){$this->discount = $discount;}
		function setVat($vat){$this->vat = $vat;}

		function setField($field){$this->field = $field;}
		function setValue($value){$this->value = $value;}

		function getId(){return $this->id;}
		function getItem(){return $this->item;}
		function getPrice(){return $this->price;}
		function getQty(){return $this->qty;}
		function getTotPrice(){return $this->totPrice;}
		function getIp(){return $this->ip;}
		
		
		function getField(){return $this->field;}
		function getValue(){return $this->value;}
		
		
		public function markInvoiceSent(){
			return $this->db->query("UPDATE invoices SET sent = sent + 1 WHERE id = '$this->id'");
		}
		public function checkInvoiceNo(){
			$inv_exists = $this->db->get_where('invoices',
			['id'=>$this->invNo,'customerId'=>$this->customerId])->num_rows();
			if($inv_exists > 0)
				$this->setInvNo($this->genInvoiceNo());
		}
		public function genInvoiceNo(){
			$inv = $this->db->query("SELECT id+1 invno FROM invoices where customerId = '$this->customerId' order by id desc")->row();
			if(!$inv)
				$res = 1;
			else
				$res = $inv->invno;
			return $res;
		}
		public function changeStatusToUnpaid(){
			$param = [
				'paid'=>0,
				'amount'=>0,
				'payment_method'=>"",
				'payment_note'=>null,
				'paiddate'=>0
			];	
			$this->db->where('id', $this->id);	
			return $this->db->update('invoices', $param);
		}
		public function payInvoice(){
			$param = [
				'paid'=>1,
				'amount'=>$this->price,
				'payment_method'=>$this->paymentMethod,
				'payment_note'=>$this->paymentNote,
				'paiddate'=>$this->date
			];	
			$this->db->where('id', $this->id);	
			return $this->db->update('invoices', $param);
		}
		public function createInvoice(){
			$param = [
				'invno'=>$this->invNo,
				'customerId'=>$this->customerId,
				'inv_type'=>$this->type,
				'note'=>$this->note,
				'date'=>$this->date,
				'duedate'=>$this->duedate,
				'user_id'=>$this->api->getUserId(),
				'vat'=>$this->vat
			];
			if($this->id)
			{
				$this->db->where('id',$this->id);
				$this->db->update('invoices', $param);
			}else
			{
				$this->db->insert('invoices', $param);
				$this->setID($this->db->insert_id());
			}
		}
		public function insrtInvoiceItem(){
			$param = [
				'invId'=>$this->id,
				'item'=>$this->item,
				'itemDesc'=>$this->itemDesc,
				'cost'=>$this->price,
				'qty'=>$this->qty,
				'discount'=>$this->discount
			];
			if($this->itemId)
			{
				$this->db->where('id',$this->itemId);
				$res = $this->db->update('invoice_items', $param);				
			}else
			{
				$res = $this->db->insert('invoice_items', $param);
			}
				
			return $res;			
		}		
		public function paidStatus($p){	
			$st = '<span class="badge badge-pill badge-light-danger" text-capitalized=""> Unpaid </span>';
			if($p > 0)
				$st = '<span class="badge badge-pill badge-light-success" text-capitalized=""> Paid </span>';	
				
			return $st;
		}		
		public function invoiceQry(){
			$this->users->setID($this->api->getUserId());
			$user = $this->users->getUserById();
			
			$ext = "";
			if($user->role != "Subscriber")
				$ext = "WHERE i.user_id = '$user->id'";
			
			return "SELECT * FROM (SELECT i.id invId,i.invno,i.note,ifnull(i.vat,'') vat,i.inv_type,from_unixtime(i.date,'%Y-%m-%d') AS date, 
			from_unixtime(i.duedate,'%Y-%m-%d') AS duedate,i.paid,ifnull(s.company_name,concat(u.name,' ',u.surname)) AS names,concat(b.name,' ',b.surname,' (',b.phone_number,')') AS salesperson,
			i.customerId,i.receipt FROM invoices i JOIN users u ON u.id = i.customerId JOIN users b ON b.id = i.user_id LEFT JOIN suppliers s 
			ON s.user_id = u.id JOIN address_details a ON (a.user_id = i.customerId OR a.user_id = s.id) AND a.category = 'Customer' 
			$ext UNION 
               
			SELECT i.id invId,if(i.invno = 0,i.id,i.invno) AS invno,i.note,ifnull(i.vat,'') vat,i.inv_type, from_unixtime(i.date,'%Y-%m-%d') AS date, 
			from_unixtime(i.duedate,'%Y-%m-%d') AS duedate,i.paid,c.company_name AS names, concat(b.name,' ',b.surname,' (',b.phone_number,')') as salesperson,i.customerId,
			i.receipt FROM invoices i JOIN suppliers c ON c.id = i.customerId JOIN users b ON b.id = i.user_id JOIN address_details a 
			ON a.user_id = i.customerId AND a.category = 'Customer' $ext) t";
		}
		public function invoices(){
			$qry = $this->invoiceQry();
			
			$invs = $this->db->query("$qry ORDER BY date desc")->result_array(); 
			
			$data = [];
			foreach($invs as $row)
			{
				$row['paid'] = $this->paidStatus($row['paid']);
				$row['inv_type'] = trim($row['inv_type']);
				$item = $this->db->query("SELECT SUM(cost*qty) total FROM `invoice_items` WHERE invId = '".$row['invId']."'")->row();
				
				$row['total'] = $item->total + $row['vat'];
				
				$data[] = $row;
			}
			
			return $data;
		}
		public function allItems(){	
			$data = [];
			foreach($this->db->query("SELECT item FROM `invoice_items` GROUP BY item")->result_array() as $row)
			{
				$data[]=$row['item'];
			}
			return $data;
		}
		public function getTotalAmnt(){	
			return $this->db->query("SELECT SUM(cost*qty) total FROM `invoice_items` WHERE `invId` = '$this->id'")->row();
		}
		public function getInvoice(){	
			$qry = $this->invoiceQry();
			
			$inv = $this->db->query("$qry WHERE invId = '$this->id'")->row(); 
			
			$inv->from = 'info@'.$this->model_db->getSubdomain().'.cloudprintpos.com';	
			$inv->paid_status = $this->paidStatus($inv->paid);
			$inv->items = $this->invoiceItems();
			if($inv->receipt)
				$inv->receipt = BASE_URL.'/uploads/Proof_of_Payment/'.$inv->invId.'-'.$inv->receipt;
			
			return $inv;
		}		
		public function deleteInvoice(){
			return $this->db->query("DELETE inv,item FROM invoices inv LEFT JOIN invoice_items item ON 
			inv.id = item.invId WHERE inv.id = '$this->id' and paid = 0");
		}
		public function deleteInvoiceItem(){
			
			return $this->db->query("DELETE FROM invoice_items WHERE id = '$this->itemId'");
		}
		public function invoiceItems(){
			return $this->db->query("SELECT id,item,itemDesc,qty,cost,ifnull(discount,0) discount,ifnull(vat,0) vat,(cost*qty) total FROM `invoice_items` WHERE invId = '".$this->id."'")->result_array();
		}			
		public function getPostById(){		
			return $this->db->get_where('careers',array('id' => $this->id)); 
		}
		public function insertProof(){
			return $this->db->query("update invoices set receipt = '$this->proof' where id = ". $this->id); 
		}		
	}
?>