<?
include_once('baseModelPMS.php');

class invoiceModel extends baseModelPMS {


	/* QUERYING FUNCTIONS */

			/*
				
			*/


	/* ********************************************************************************
	Select list of invoices by criteria
	******************************************************************************** */

	function getlist_invoicetable($projectpkey,$supplierpkey="",$popkey="",
								$invoice_no="",$sortby="IV.item_invoiceno", $sortdir="ASC") {

		$listdata=array();
		
		/*
		$sql="select invoice_items.item_projectpkey,invoice_items.item_invoiceno,
		        (invoice_items.item_quantity*invoice_items.item_rate) as amount,
				po_details.po_no,zikay_cmsv5.settings_suppliers.supplier_name from invoice_items left join po_details
				on invoice_items.item_popkey=po_details.po_pkey
				left join zikay_cmsv5.settings_suppliers
				on invoice_items.item_supplierpkey=zikay_cmsv5.settings_suppliers.supplier_pkey";
		*/
		

		$sql="select IV.item_invoiceno,sum(IV.item_quantity*IV.item_rate) as amount,
				IV.item_debitnote,PO.po_no,SP.supplier_name
				from invoice_items as IV inner join do_items as DT
					on IV.item_popkey=DT.item_popkey
						and IV.item_projectpkey =DT.item_projectpkey 
						and IV.item_supplierpkey=DT.item_supplierpkey
						and IV.item_subsubtradepkey=DT.item_subsubtradepkey
						and IV.item_dopkey=DT.item_pkey
				inner join po_details as PO on IV.item_popkey=PO.po_pkey
				inner join zikay_cmsv5.settings_suppliers as SP	on IV.item_supplierpkey=SP.supplier_pkey";
		$sql.=" where IV.item_projectpkey='$projectpkey'";
		
		
		if($supplierpkey!=""){
			$sql.=" and IV.item_supplierpkey='$supplierpkey'";
		}

		if($popkey!=""){
			$sql.=" and IV.item_popkey='$popkey'";
		}

		if($invoice_no!=""){
			$sql.=" and IV.item_invoiceno='$invoice_no'";
		}
		
		$sql.=" group by IV.item_invoiceno,PO.po_no";
		$sql.=" order by $sortby $sortdir";

		$listdata=$this->getextendedrows($sql);
		return $listdata;

	
	}





	/* ********************************************************************************
	Select list of invoices by project
	******************************************************************************** */

	function getlist_invoicebyproject($projectpkey, $sortby="invoice_no", $sortdir="ASC") {

		$listdata=array();

		$sql="Select INV.* from invoice_details INV where INV.invoice_projectpkey = '".$projectpkey."' order by INV.".$sortby." ".$sortdir;
		$listdata=$this->getextendedrows($sql);
		return $listdata;
	}


	/* ********************************************************************************
	Select list of invoices by po, supplier, and item subsub
	******************************************************************************** */

	function getlist_invoicebypoitem($popkey, $supplierpkey, $subsubtradepkey, $sortby="item_invoicedate", $sortdir="ASC") {

		$listdata=array();

		$sql="Select INVI.* from invoice_items INVI where INVI.item_popkey = '".$popkey."' and INVI.item_supplierpkey = '".$supplierpkey."' and INVI.item_subsubtradepkey = '".$subsubtradepkey."' order by INVI.".$sortby." ".$sortdir;
		$listdata=$this->getextendedrows($sql);
		return $listdata;
	}


	/* ********************************************************************************
	Select list of invoices by po (TODO: and by do!! seems invoice is linked to do!!)
	******************************************************************************** */

	function getlist_invoice_pmcreport($projectpkey, $popkey) {

		$listdata=array();

		$sql="Select INVI.* from invoice_items INVI where INVI.item_projectpkey = '".$projectpkey."' and INVI.item_popkey = '".$popkey."' order by INVI.item_invoicedate";
		//echo $sql;
		$listdata=$this->getextendedrows($sql);
		return $listdata;
	}


	/* ********************************************************************************
	Select list of invoices by po
	******************************************************************************** */

	function getlist_invoice_scmreport($projectpkey, $popkey) {

		$listdata=array();

		$sql="Select INVI.* from invoice_items INVI where INVI.item_projectpkey = '".$projectpkey."' and INVI.item_popkey = '".$popkey."' order by INVI.item_invoicedate";
		///echo $sql; die();
		$listdata=$this->getextendedrows($sql);
		return $listdata;
	}


	/* ********************************************************************************
	Select a particular invoice
	******************************************************************************** */

	function getdata_invoice($invoicepkey){

		$data=array();

		$sql="Select INV.* from invoice_details INV where INV.invoice_pkey='".$invoicepkey."'";
		$data=$this->getextendedrow($sql);
		return $data;
	}


	/* FUNCTIONS TO INSERT RECORDS */


	/* ********************************************************************************
	Insert/Create a new invoice
	******************************************************************************** */

	/*
	function insertdata_invoice($p_invoice_pkey, $p_invoice_projectpkey, $p_invoice_popkeylist, $p_invoice_dopkeylist, $p_invoice_sono, $p_invoice_no, $p_invoice_date, $p_invoice_terms, $p_invoice_salesman, $p_invoice_currency, $p_invoice_customerno) {

		// clean/convert/add params..
		$p_invoice_date=converttounix($p_invoice_date);

		// update..
		$new_invoice_pkey=md5(microtime());

		$sa = $this->add_data("Invoice [Add]", "invoice_details", "invoice_pkey, invoice_projectpkey, invoice_popkeylist, invoice_dopkeylist, invoice_sono, invoice_no, invoice_date, invoice_terms, invoice_salesman, invoice_currency, invoice_customerno",
			"'".$p_invoice_pkey."','".$p_invoice_projectpkey."','".$p_invoice_popkeylist.
			"'".$p_invoice_dopkeylist."','".$p_invoice_sono."','".$p_invoice_no.
			"'".$p_invoice_date."',".$p_invoice_terms.",'".$p_invoice_salesman.
			"'".$p_invoice_currency."','".$p_invoice_customerno."'");

		// return result..
		if ($sa==1) {
			return $new_invoice_pkey;
		} else {
			return false;
		}
	}
	*/

	function insertdata_invoice($p_item_popkey, $p_item_projectpkey, $p_item_supplierpkey, $p_item_invoiceactions, $p_item_invoicepkeys, $p_item_invoicenos, $p_item_invoicedates, $p_item_invoiceitems, $p_item_quantities, $p_item_rates, $p_item_debitnotes) {

		// TODO: later on check if all final qty matches with expected
		// total in PO, if so, close PO (change status to 3)

		// clean/convert/add params..
		$p_item_quantities=str_replace(",","",$p_item_quantities);
		$p_item_rates=str_replace(",","",$p_item_rates);
		$p_item_debitnotes=str_replace(",","",$p_item_debitnotes);

		$p_item_invoiceaction=explode("|",$p_item_invoiceactions);
		$p_item_invoicepkey=explode("|",$p_item_invoicepkeys);
		$p_item_invoiceno=explode("|",$p_item_invoicenos);
		$p_item_invoicedate=explode("|",$p_item_invoicedates);
		$p_item_invoiceitem=explode("|",$p_item_invoiceitems);
		$p_item_quantity=explode("|",$p_item_quantities);
		$p_item_rate=explode("|",$p_item_rates);
		$p_item_debitnote=explode("|",$p_item_debitnotes);

		include_once('poModel.php');
		$mypoModel=new poModel;

		$objcount=0;

		foreach ($p_item_invoiceitem as $itempkey) {

			// get po item data
			$myitem = $mypoModel->getdata_poitem($itempkey);

			$mydate=converttounix($p_item_invoicedate[$objcount]);

			if (($p_item_invoicepkey[$objcount]=="")&&($p_item_invoiceno[$objcount]!="")) {

				// update..
				$new_item_pkey=md5(microtime());

				$sa = $this->add_data("Invoice Item [Add]", "invoice_items", "item_pkey, item_popkey, item_projectpkey, item_supplierpkey, item_invoiceno, item_invoicedate, item_no, item_subsubtradepkey, item_description, item_unit, item_quantity, item_rate, item_debitnote",
					"'".$new_item_pkey."','".$p_item_popkey."','".$p_item_projectpkey.
					"','".$p_item_supplierpkey.
					"','".$p_item_invoiceno[$objcount]."','".$mydate.
					"',"."0".
					",'".$myitem['item_subsubtradepkey']."','".ununescape($myitem['item_description']).
					"','".$myitem['item_unit']."',".$p_item_quantity[$objcount].
					",".$p_item_rate[$objcount].",".$p_item_debitnote[$objcount]);

			} else if (($p_item_invoicepkey[$objcount]!="")&&($p_item_invoiceno[$objcount]!="")) {

				$sa = $this->update_data("Invoice Item [Edit]", "invoice_items", "item_invoiceno = '".$p_item_invoiceno[$objcount].
					"', item_invoicedate = '".$mydate.
					"', item_quantity = ".$p_item_quantity[$objcount].
					", item_rate = ".$p_item_rate[$objcount].
					", item_debitnote = ".$p_item_debitnote[$objcount],
					"where item_pkey = '".$p_item_invoicepkey[$objcount]."'");

				$new_item_pkey = $p_item_invoicepkey[$objcount];

			} else if (($p_item_invoicepkey[$objcount]!="")&&($p_item_invoiceno[$objcount]=="")) {

				$sa = $this->delete_data("Invoice Item [Delete]", "invoice_items", "where item_pkey = '".$p_item_invoicepkey[$objcount]."'");

				$new_item_pkey = $p_item_invoicepkey[$objcount];
			}

			$objcount++;
		}

		// return result..
		if ($sa==1) {
			return $new_item_pkey;
		} else {
			return false;
		}
	}


	/* ********************************************************************************
	Insert/Create a new invoice item
	******************************************************************************** */

	function insertdata_invoiceitem($p_item_pkey, $p_item_invoicepkey, $p_item_no, $p_item_id, $p_item_subsubtradepkey, $p_item_description, $p_item_unit, $p_item_quantity, $p_item_rate) {

		// clean/convert/add params..
		$p_item_quantity=str_replace(",","",$p_item_quantity);
		$p_item_rate=str_replace(",","",$p_item_rate);

		// update..
		$new_item_pkey=md5(microtime());

		$sa = $this->add_data("Invoice Item [Add]", "invoice_items", "item_pkey, item_invoicepkey, item_no, item_id, item_subsubtradepkey, item_description, item_unit, item_quantity, item_rate",
			"'".$new_item_pkey."','".$p_item_invoicepkey."',".$p_item_no.
			",'".$p_item_id."','".$p_item_subsubtradepkey."','".$p_item_description.
			"','".$p_item_unit."',".$p_item_quantity.",".$p_item_rate);

		// return result..
		if ($sa==1) {
			return $new_item_pkey;
		} else {
			return false;
		}
	}


	/* FUNCTIONS TO UPDATE RECORDS */


	/* ********************************************************************************
	Update/Override a invoice
	> 
	******************************************************************************** */

	function updatedata_invoice($p_invoice_pkey, $p_invoice_projectpkey, $p_invoice_popkeylist, $p_invoice_dopkeylist, $p_invoice_sono, $p_invoice_no, $p_invoice_date, $p_invoice_terms, $p_invoice_salesman, $p_invoice_currency, $p_invoice_customerno) {

		// clean/convert/add params..
		$p_invoice_date=converttounix($p_invoice_date);

		// update..
		$sa = $this->update_data("Invoice [Edit]", "invoice_details", "invoice_projectpkey = '".$p_invoice_projectpkey.
			"', invoice_popkeylist = '".$p_invoice_popkeylist.
			"', invoice_dopkeylist = '".$p_invoice_dopkeylist.
			"', invoice_sono = '".$p_invoice_sono.
			"', invoice_no = '".$p_invoice_no.
			"', invoice_date = '".$p_invoice_date.
			"', invoice_terms = '".$p_invoice_terms.
			"', invoice_salesman = '".$p_invoice_salesman.
			"', invoice_currency = '".$p_invoice_currency.
			"', invoice_customerno = '".$p_invoice_customerno."'",
			"where invoice_pkey = '".$p_invoice_pkey."'");

		// return result..
		if ($sa==1) {
			return $p_invoice_pkey;
		} else {
			return false;
		}
	}


	/* ********************************************************************************
	Update/Override an existing invoice item
	******************************************************************************** */

	function updatedata_invoiceitem($p_item_pkey, $p_item_invoicepkey, $p_item_no, $p_item_id, $p_item_subsubtradepkey, $p_item_description, $p_item_unit, $p_item_quantity, $p_item_rate) {

		// clean/convert/add params..
		$p_item_quantity=str_replace(",","",$p_item_quantity);
		$p_item_rate=str_replace(",","",$p_item_rate);

		// update..
		$sa = $this->update_data("Invoice Item [Edit]", "invoice_items", "item_invoicepkey = '".$p_item_invoicepkey.
			"', item_no = ".$p_item_no.
			", item_id = '".$p_item_id.
			"', item_subsubtradepkey = '".$p_item_subsubtradepkey.
			"', item_description = '".$p_item_description.
			"', item_unit = '".$p_item_unit.
			"', item_quantity = ".$p_item_quantity.
			", item_rate = ".$p_item_rate,
			"where item_pkey = '".$p_item_pkey."'");

		// return result..
		if ($sa==1) {
			return $p_item_pkey;
		} else {
			return false;
		}
	}


	/* FUNCTIONS TO DELETE RECORDS */


	/* ********************************************************************************
	Delete an existing invoice
	******************************************************************************** */

	function deletedata_invoice($p_invoice_pkey) {

		// delete subject..
		$sa = $this->delete_data("Invoice [Delete]", "invoice_details", "where invoice_pkey = '".$p_invoice_pkey."'");

		// return result..
		if ($sa==1) {
			return $p_invoice_pkey;
		} else {
			return false;
		}
	}


	/* ********************************************************************************
	Delete an existing invoice item
	******************************************************************************** */

	function deletedata_invoiceitem($p_item_pkey, $p_item_invoicepkey) {

		// delete subject..
		$sa = $this->delete_data("Invoice Item [Delete]", "invoice_items", "where item_pkey = '".$p_item_pkey."' and item_invoicepkey = '".$p_item_invoicepkey."'");

		// return result..
		if ($sa==1) {
			return $p_item_pkey;
		} else {
			return false;
		}
	}

}
?>