<?
include_once('baseModelPMS.php');

class doModel extends baseModelPMS {


	/* QUERYING FUNCTIONS */


	/* ********************************************************************************
	Select list of dos by po
	Note: exclude cancelled POs?
	******************************************************************************** */

	/*
	function getlist_dobypo($popkey, $sortby="do_no", $sortdir="ASC") {

		$listdata=array();

		$sql="Select DO.* from do_details DO where DO.do_popkey = '".$popkey."' order by DO.".$sortby." ".$sortdir;
		$listdata=$this->getextendedrows($sql);
		return $listdata;
	}
	*/


	/* ********************************************************************************
	Select list of dos
	******************************************************************************** */

	function getlist_do($projectpkey, $sortby="do_date", $sortdir="DESC") {

		$listdata=array();

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


	/* ********************************************************************************
	Select list of do items
	******************************************************************************** */

	function getlist_doitems($dopkey, $sortby="item_no", $sortdir="ASC") {

		$listdata=array();

		$sql="Select DOI.* from do_items DOI where DOI.item_dopkey = '".$dopkey."' order by DOI.".$sortby." ".$sortdir;
		$listdata=$this->getextendedrows($sql);
		return $listdata;
	}


	/* ********************************************************************************
	Select list of dos by po and item subsub
	******************************************************************************** */

	function getlist_dobypoitem($popkey, $subsubtradepkey, $sortby="item_dodate", $sortdir="ASC") {

		$listdata=array();

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


	/* ********************************************************************************
	Select list of dos by po and subsub
	******************************************************************************** */

	function getlist_do_mblreport($projectpkey, $popkey, $subsubtradepkey) {

		$listdata=array();

		$sql="Select DOI.* from do_items DOI where DOI.item_projectpkey = '".$projectpkey."' and DOI.item_popkey = '".$popkey."' and DOI.item_subsubtradepkey = '".$subsubtradepkey."' order by DOI.item_dono";
		//echo $sql;
		$listdata=$this->getextendedrows($sql);
		return $listdata;
	}


	/* ********************************************************************************
	Select list of dos by po, supplier and subsub
	******************************************************************************** */

	function getlist_do_poreport($projectpkey, $popkey, $supplierpkey, $subsubtradepkey) {

		$listdata=array();

		$sql="Select DOI.* from do_items DOI where DOI.item_projectpkey = '".$projectpkey."' and DOI.item_popkey = '".$popkey."' and DOI.item_supplierpkey = '".$supplierpkey."' and DOI.item_subsubtradepkey = '".$subsubtradepkey."' order by DOI.item_dodate";
		//echo $sql;
		$listdata=$this->getextendedrows($sql);
		return $listdata;
	}


	/* ********************************************************************************
	Select list of dos by po and supplier
	******************************************************************************** */

	function getlist_do_spsreport($projectpkey, $popkey, $supplierpkey) {

		$listdata=array();

		$sql="Select DOI.* from do_items DOI where DOI.item_projectpkey = '".$projectpkey."' and DOI.item_popkey = '".$popkey."' and DOI.item_supplierpkey = '".$supplierpkey."' order by DOI.item_dodate";
		//echo $sql;
		//exit;
		$listdata=$this->getextendedrows($sql);
		return $listdata;
	}


	/* ********************************************************************************
	Select list of dos by po and subsub
	******************************************************************************** */

	function getlist_do_pmcreport($projectpkey, $popkey, $subsubtradepkey) {

		$listdata=array();

		$sql="Select DOI.* from do_items DOI where DOI.item_projectpkey = '".$projectpkey."' and DOI.item_popkey = '".$popkey."' and DOI.item_subsubtradepkey = '".$subsubtradepkey."' order by DOI.item_dodate";
		//echo $sql;
		$listdata=$this->getextendedrows($sql);
		return $listdata;
	}


	/* ********************************************************************************
	Select list of dos by po
	******************************************************************************** */

	function getlist_doitemsbypo($popkey) {

		$listdata=array();

		$sql="Select DO.* from do_details DO where DO.do_popkey = '".$popkey."' order by DO.".$sortby." ".$sortdir;
		$listdata=$this->getextendedrows($sql);
		return $listdata;
	}


	/* ********************************************************************************
	Select a particular do
	******************************************************************************** */

	function getdata_do($dopkey){

		$data=array();

		$sql="Select DO.* from do_details DO where DO.do_pkey='".$dopkey."'";
		$data=$this->getextendedrow($sql);
		return $data;
	}


	/* ********************************************************************************
	Select a particular do item
	******************************************************************************** */

	function getdata_doitem($itempkey){

		$data=array();

		$sql="Select DOI.* from do_items DOI where DOI.item_pkey='".$itempkey."'";
		$data=$this->getextendedrow($sql);
		return $data;
	}


	/* ********************************************************************************
	Select total quantity of do items accepted (all items) by do
	******************************************************************************** */

	function getquantity_doitems($dopkey) {

		$data=array();

		$sql="Select sum(DOI.item_accepted) as do_quantity from do_items DOI where DOI.item_dopkey = '".$dopkey."'";
		$data=$this->getextendedrow($sql);
		return $data['do_quantity'];
	}


	/* ********************************************************************************
	Select total quantity of do items delievered (all items) by do
	******************************************************************************** */

	function getdelivered_doitems($dopkey) {

		$data=array();

		$sql="Select sum(DOI.item_quantity) as do_quantity from do_items DOI where DOI.item_dopkey = '".$dopkey."'";
		$data=$this->getextendedrow($sql);
		return $data['do_quantity'];
	}


	/* ********************************************************************************
	Select total quantity of do items (all items) by PO
	******************************************************************************** */

	function getquantity_doitemsbypo($popkey) {

		$data=array();

		$sql="Select sum(DOI.item_accepted) as do_quantity from do_items DOI inner join do_details DO on DOI.item_dopkey=DO.do_pkey where DOI.item_popkey = '".$popkey."'";
		$data=$this->getextendedrow($sql);
		return $data['do_quantity'];
	}


	/* ********************************************************************************
	Select total quantity of items delivered/accepted by subsubtrade
	******************************************************************************** */

	function getdelivered_doitemsbysubsubtrade($projectpkey, $subsubtradepkey) {

		$data=array();

		$sql="Select sum(DOI.item_accepted) as accepted_quantity from do_items DOI inner join po_details PO on DOI.item_popkey=PO.po_pkey where PO.po_projectpkey = '".$projectpkey."' and DOI.item_subsubtradepkey = '".$subsubtradepkey."'";
		$data=$this->getextendedrow($sql);
		return $data['accepted_quantity'];
	}



	/* ********************************************************************************
	Get qty of accepted items of a specific subsubtrade assigned to a specific PO
	******************************************************************************** */

	function getaccepted_doitems($subsubtradepkey, $popkey) {

		$data=array();

		$sql="Select sum(DOI.item_accepted) as do_quantity from do_items DOI where DOI.item_subsubtradepkey='".$subsubtradepkey."' and DOI.item_popkey = '".$popkey."'";
		$data=$this->getextendedrow($sql);
		return $data['do_quantity'];
	}


	/* ********************************************************************************
	Select total quantity of do items (all items) by PO
	******************************************************************************** */

	function getquantity_received($popkey,$subsubtradepkey) {

		$data=array();

		$sql="Select sum(DOI.item_quantity) as do_quantity from do_items DOI where DOI.item_popkey = '".$popkey."' and DOI.item_subsubtradepkey = '".$subsubtradepkey."'";
		$data=$this->getextendedrow($sql);
                if ($data['do_quantity']==null) {
			return 0;
                } else {
			return $data['do_quantity'];
		}
	}


	/* ********************************************************************************
	Returns true if do items equal or exceed po quantity
	******************************************************************************** */

	function getstatus_pocomplete($popkey) {

		$iscomplete = true;

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

		$po_data = $mypoModel->getdata_po($popkey);
		$po_items = $mypoModel->getlist_poitems($popkey);

		foreach ($po_items as $myitem) {

			$doquantity=$this->getquantity_received($popkey,$myitem['item_subsubtradepkey']);

			if ($doquantity<$myitem['item_quantity']) {
				$iscomplete = false;
			}
		}

		// return result..
		return $iscomplete;

	}


	/* FUNCTIONS TO INSERT RECORDS */


	/* ********************************************************************************
	Insert/Create a new do
	******************************************************************************** */

	/*
	function insertdata_do($p_do_pkey, $p_do_projectpkey, $p_do_supplierpkey, $p_do_no, $p_do_date, $p_do_sono, $p_do_accountno, $p_do_address, $p_do_lorryno, $p_do_transportco, $p_do_drivername, $p_do_drivericno, $p_do_receiverco, $p_do_receivername, $p_do_receivericno, $p_do_remarks) {

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

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

		$sa = $this->add_data("DO [Add]", "do_details", "do_pkey, do_projectpkey, do_supplierpkey, do_no, do_date, do_sono, do_accountno, do_address, do_lorryno, do_transportco, do_drivername, do_drivericno, do_receiverco, do_receivername, do_receivericno, do_remarks, do_status",
			"'".$new_do_pkey."','".$p_do_projectpkey."','".$p_do_supplierpkey.
			"','".$p_do_no.
			"','".$p_do_date."','".$p_do_sono."','".$p_do_accountno.
			"','".$p_do_address."','".$p_do_lorryno."','".$p_do_transportco.
			"','".$p_do_drivername."','".$p_do_drivericno."','".$p_do_receiverco.
			"','".$p_do_receivername."','".$p_do_receivericno."','".$p_do_remarks."',0");

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

	function insertdata_do($p_item_popkey, $p_item_projectpkey, $p_item_supplierpkey, $p_item_doactions, $p_item_dopkeys, $p_item_donos, $p_item_dodates, $p_item_doitems, $p_item_quantities, $p_item_remarks) {

		// 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_doaction=explode("|",$p_item_doactions);
		$p_item_dopkey=explode("|",$p_item_dopkeys);
		$p_item_dono=explode("|",$p_item_donos);
		$p_item_dodate=explode("|",$p_item_dodates);
		$p_item_doitem=explode("|",$p_item_doitems);
		$p_item_quantity=explode("|",$p_item_quantities);
		$p_item_remark=explode("|",$p_item_remarks);

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

		$objcount=0;

		foreach ($p_item_doitem as $itempkey) {

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

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

			if (($p_item_dopkey[$objcount]=="")&&($p_item_doaction[$objcount]=="1")) {

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

				$sa = $this->add_data("DO Item [Add]", "do_items", "item_pkey, item_popkey, item_projectpkey, item_supplierpkey, item_dono, item_dodate, item_no, item_subsubtradepkey, item_description, item_unit, item_quantity, item_remarks",
					"'".$new_item_pkey."','".$p_item_popkey."','".$p_item_projectpkey.
					"','".$p_item_supplierpkey.
					"','".$p_item_dono[$objcount]."','".$mydate.
					"',"."0".
					",'".$myitem['item_subsubtradepkey']."','".ununescape($myitem['item_description']).
					"','".$myitem['item_unit']."',".$p_item_quantity[$objcount].
					",'".$p_item_remark[$objcount]."'");

			} else if (($p_item_dopkey[$objcount]!="")&&($p_item_doaction[$objcount]=="2")) {

				$sa = $this->update_data("DO Item [Edit]", "do_items", "item_dono = '".$p_item_dono[$objcount].
					"', item_dodate = '".$mydate.
					"', item_quantity = ".$p_item_quantity[$objcount].
					", item_remarks = '".$p_item_remark[$objcount]."'",
					"where item_pkey = '".$p_item_dopkey[$objcount]."'");

				$new_item_pkey = $p_item_dopkey[$objcount];

			}

			$objcount++;
		}

		// return result..
		if ($sa==1) {
			$opresult = $this->getstatus_pocomplete($p_item_popkey);
			if ($opresult==true) {
				$opresult=$mypoModel->updatestatus_po($p_item_popkey, "3");
				return "complete";
			} else {
				return $new_item_pkey;
			}
		} else {
			return false;
		}
	}

	/* ********************************************************************************
	Insert/Create a new do item
	NOTE: Item No no longer significant!!
	******************************************************************************** */

	function insertdata_doitem($p_item_pkey, $p_item_dopkey, $p_item_popkey, $p_item_no, $p_item_subsubtradepkey, $p_item_description, $p_item_unit, $p_item_quantity, $p_item_accepted) {

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

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

		// get outstanding POs of the same subsubtrade..
		//$maxqty = $mypoModel->getmaximum_poitemsbysubsubtrade($_SESSION['Xprojectpkey'], $p_item_subsubtradepkey);
		//$accqty = $this->getdelivered_doitemsbysubsubtrade($_SESSION['Xprojectpkey'], $p_item_subsubtradepkey);
		
		// TODO: WARNING if amount exceed total in PO!!
		// KALAU DELETE ITEM, make sure adjust PO status!! (revert to partial)

		// get list of all POs containing the subsubtrade item
		$po_list = $mypoModel->getlist_posets($_SESSION['Xprojectpkey'], $p_item_subsubtradepkey);
		foreach ($po_list as $mypo) {
			// get any DO items assigned to each of the expected POs
			$received = $this->getaccepted_doitems($p_item_subsubtradepkey, $mypo['po_pkey']);
			// proceed if amount received is less than expected
			if ($received < $mypo['item_quantity']) {
				$outstanding = $mypo['item_quantity']-$received;
				// proceed depending on newly delivered item quantity..
				if ($p_item_accepted < $outstanding) {

					// if new delivery less than outstanding for the PO, add full qty under it
					$new_item_pkey=md5(microtime());
					$sa = $this->add_data("DO Item [Add]", "do_items", "item_pkey, item_dopkey, item_popkey, item_no, item_subsubtradepkey, item_description, item_unit, item_quantity, item_accepted",
						"'".$new_item_pkey."','".$p_item_dopkey."','".$mypo['po_pkey'].
						"',0".
						",'".$p_item_subsubtradepkey."','".$p_item_description.
						"','".$p_item_unit."',".$p_item_quantity.",".$p_item_accepted);

					$sa2 = $mypoModel->updatestatus_po($mypo['po_pkey'], "2");
					break;

				} else if ($p_item_accepted == $outstanding) {

					// if new delivery just enough, add full qty under it and CLOSE PO!

					$new_item_pkey=md5(microtime());
					$sa = $this->add_data("DO Item [Add]", "do_items", "item_pkey, item_dopkey, item_popkey, item_no, item_subsubtradepkey, item_description, item_unit, item_quantity, item_accepted",
						"'".$new_item_pkey."','".$p_item_dopkey."','".$mypo['po_pkey'].
						"',0".
						",'".$p_item_subsubtradepkey."','".$p_item_description.
						"','".$p_item_unit."',".$p_item_quantity.",".$p_item_accepted);

					$sa2 = $mypoModel->updatestatus_po($mypo['po_pkey'], "3");
					break;

				} else if ($p_item_accepted > $outstanding) {

					// if new delivery more than enough, add the outstanding under it, CLOSE PO and continue

					$p_item_accepted = $p_item_accepted - $outstanding;

					$new_item_pkey=md5(microtime());
					$sa = $this->add_data("DO Item [Add]", "do_items", "item_pkey, item_dopkey, item_popkey, item_no, item_subsubtradepkey, item_description, item_unit, item_quantity, item_accepted",
						"'".$new_item_pkey."','".$p_item_dopkey."','".$mypo['po_pkey'].
						"',0".
						",'".$p_item_subsubtradepkey."','".$p_item_description.
						"','".$p_item_unit."',".$p_item_quantity.",".$outstanding);

					$sa2 = $mypoModel->updatestatus_po($mypo['po_pkey'], "3");
				}
			}
		}

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

		//$sa = $this->add_data("DO Item [Add]", "do_items", "item_pkey, item_dopkey, item_popkey, item_no, item_subsubtradepkey, item_description, item_unit, item_quantity, item_accepted",
		//	"'".$new_item_pkey."','".$p_item_dopkey."','".$p_item_popkey.
		//	"',".$p_item_no.
		//	",'".$p_item_subsubtradepkey."','".$p_item_description.
		//	"','".$p_item_unit."',".$p_item_quantity.",".$p_item_accepted);

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


	/* FUNCTIONS TO UPDATE RECORDS */


	/* ********************************************************************************
	Update/Override a do
	> 
	******************************************************************************** */

	function updatedata_do($p_do_pkey, $p_do_projectpkey, $p_do_supplierpkey, $p_do_no, $p_do_date, $p_do_sono, $p_do_accountno, $p_do_address, $p_do_lorryno, $p_do_transportco, $p_do_drivername, $p_do_drivericno, $p_do_receiverco, $p_do_receivername, $p_do_receivericno, $p_do_remarks) {

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

		// update..
		$sa = $this->update_data("DO [Edit]", "do_details", "do_projectpkey = '".$p_do_projectpkey.
			"', do_supplierpkey = '".$p_do_supplierpkey.
			"', do_no = '".$p_do_no.
			"', do_date = '".$p_do_date.
			"', do_sono = '".$p_do_sono.
			"', do_accountno = '".$p_do_accountno.
			"', do_address = '".$p_do_address.
			"', do_lorryno = '".$p_do_lorryno.
			"', do_transportco = '".$p_do_transportco.
			"', do_drivername = '".$p_do_drivername.
			"', do_drivericno = '".$p_do_drivericno.
			"', do_receiverco = '".$p_do_receiverco.
			"', do_receivername = '".$p_do_receivername.
			"', do_receivericno = '".$p_do_receivericno.
			"', do_remarks = '".$p_do_remarks.
			"', do_lorryno = '".$p_do_lorryno."'",
			"where do_pkey = '".$p_do_pkey."'");

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


	/* ********************************************************************************
	Cancel a do or otherwise update status
	******************************************************************************** */

	function updatestatus_do($p_do_pkey, $p_do_status) {

		// clean/convert/add params..

		// update..
		$sa = $this->update_data("DO [Edit]", "do_details", "do_status = ".$p_do_status,
			"where do_pkey = '".$p_do_pkey."'");

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


	/* ********************************************************************************
	Update/Override an existing do item
	Not used: For DO item, user can only add or delete!!
	(so as not to mess up PO quantity calculations..
	******************************************************************************** */

	function updatedata_doitem($p_item_pkey, $p_item_dopkey, $p_item_popkey, $p_item_no, $p_item_subsubtradepkey, $p_item_description, $p_item_unit, $p_item_quantity, $p_item_accepted) {

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

		// update..
		$sa = $this->update_data("DO Item [Edit]", "do_items", "item_dopkey = '".$p_item_dopkey.
			"', item_popkey = '".$p_item_popkey.
			"', item_no = ".$p_item_no.
			", item_subsubtradepkey = '".$p_item_subsubtradepkey.
			"', item_description = '".$p_item_description.
			"', item_unit = '".$p_item_unit.
			"', item_quantity = ".$p_item_quantity.
			", item_accepted = ".$p_item_accepted,
			"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 do
	******************************************************************************** */

	// function deletedata_do($p_do_pkey) {
	function deletedata_do($p_item_popkey, $p_item_projectpkey, $p_item_supplierpkey, $p_item_doactions, $p_item_dopkeys, $p_item_donos, $p_item_dodates, $p_item_doitems, $p_item_quantities, $p_item_remarks) {

		/*
		// delete subject..
		$sa = $this->delete_data("DO [Delete]", "do_details", "where do_pkey = '".$p_do_pkey."'");

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

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

		$p_item_doaction=explode("|",$p_item_doactions);
		$p_item_dopkey=explode("|",$p_item_dopkeys);
		$p_item_dono=explode("|",$p_item_donos);
		$p_item_dodate=explode("|",$p_item_dodates);
		$p_item_doitem=explode("|",$p_item_doitems);
		$p_item_quantity=explode("|",$p_item_quantities);
		$p_item_remark=explode("|",$p_item_remarks);

		$objcount=0;

		foreach ($p_item_doitem as $itempkey) {

			if (($p_item_dopkey[$objcount]!="")&&($p_item_doaction[$objcount]=="3")) {

				$sa = $this->delete_data("DO Item [Delete]", "do_items", "where item_pkey = '".$p_item_dopkey[$objcount]."'");

				$new_item_pkey = $p_item_dopkey[$objcount];

			}

			$objcount++;
		}

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


	/* ********************************************************************************
	Delete an existing do item
	******************************************************************************** */

	function deletedata_doitem($p_item_pkey, $p_item_dopkey) {

		// delete subject..
		$sa = $this->delete_data("DO Item [Delete]", "do_items", "where item_pkey = '".$p_item_pkey."' and item_dopkey = '".$p_item_dopkey."'");

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

}
?>