<?
include_once('baseModelPMS.php');

class mblModel extends baseModelPMS {


	/* QUERYING FUNCTIONS */


	/* ********************************************************************************
	Select mbl list
	******************************************************************************** */

	function getlist_mblitems($projectpkey) {

		$listdata=array();

		$sql="Select MBL.*, ST1.tradecode as parentcode, ST1.tradedescription as parentdesc 
		from 
			mbl_items MBL inner join settings_trades ST1 on MBL.item_subtradepkey=ST1.tradepkey 
			left join settings_trades ST2 on MBL.item_subsubtradepkey=ST2.tradepkey 
		where 
			MBL.item_projectpkey = '".$projectpkey."' 
		order by 
			ST1.tradecode, ST2.tradecode"; 
					
		$listdata=$this->getextendedrows($sql);
		return $listdata;
	}


	/* ********************************************************************************
	Select a particular mbl item
	******************************************************************************** */

	function getdata_mblitem($itempkey){

		$data=array();

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


	/* ********************************************************************************
	Select a particular mbl item by subtrade (subsub sometimes blank)
	******************************************************************************** */

	function getdata_mblitembysubtrade($subtradepkey){

		$data=array();

		$sql="Select MBL.* from mbl_items MBL where MBL.item_subtradepkey='".$subtradepkey."'";
		$data=$this->getextendedrow($sql);
		return $data;
	}


	/* ********************************************************************************
	Select a particular itq (mbl) item by subtrade (where subsub is blank)
	******************************************************************************** */

	function getdata_itqitembysubtrade($projectpkey, $subtradepkey){

		$data=array();

		$sql="Select MBL.* from mbl_items MBL where MBL.item_projectpkey='".$projectpkey."' and MBL.item_subtradepkey='".$subtradepkey."' and MBL.item_subsubtradepkey=''";
		$data=$this->getextendedrow($sql);
		return $data;
	}


	/* ********************************************************************************
	Select a particular itq (mbl) item by subsubtrade (sometimes blank)
	******************************************************************************** */

	function getdata_itqitembysubsubtrade($projectpkey, $subsubtradepkey){

		$data=array();

		$sql="Select MBL.* from mbl_items MBL where MBL.item_projectpkey='".$projectpkey."' and MBL.item_subsubtradepkey='".$subsubtradepkey."'";
		$data=$this->getextendedrow($sql);
		return $data;
	}


	/* ********************************************************************************
	get total amount for an mbl subtrade
	******************************************************************************** */

	function gettotal_mblsubtrade($projectpkey, $subtradepkey){

		$data=array();

		$sql="Select sum(MBL.item_quantity*MBL.item_rate) as mbl_subtotal from mbl_items MBL where MBL.item_projectpkey='".$projectpkey."' and MBL.item_subtradepkey='".$subtradepkey."'";
		$data=$this->getextendedrow($sql);
		return $data['mbl_subtotal'];
	}


	/* ********************************************************************************
	get total quantity for an mbl subtrade
	(only where subsub is blank!)
	******************************************************************************** */

	function getquantity_mblsubtrade($projectpkey, $subtradepkey){

		$data=array();

		$sql="Select MBL.item_quantity from mbl_items MBL where MBL.item_projectpkey='".$projectpkey."' and MBL.item_subtradepkey='".$subtradepkey."' and MBL.item_subsubtradepkey=''";
		$data=$this->getextendedrow($sql);
		if ($data['item_quantity']=="") $data['item_quantity']="0";
		return $data['item_quantity'];
	}


	/* ********************************************************************************
	get total quantity for an mbl subsubtrade
	******************************************************************************** */

	function getquantity_mblsubsubtrade($projectpkey, $subsubtradepkey){

		$data=array();

		$sql="Select MBL.item_quantity from mbl_items MBL where MBL.item_projectpkey='".$projectpkey."' and MBL.item_subsubtradepkey='".$subsubtradepkey."'";
		$data=$this->getextendedrow($sql);
		if ($data['item_quantity']=="") $data['item_quantity']="0";
		return $data['item_quantity'];
	}


	/* ********************************************************************************
	Select list of mbl subtrades
	Used to limit subsubtrade selection during creation of normal/exceeded MRO
	******************************************************************************** */

	function getlist_mblsubtrades($projectpkey) {

		$listdata=array();

		$sql="Select distinct MBL.item_subtradepkey from mbl_items MBL where MBL.item_projectpkey = '".$projectpkey."'";
		$listdata=$this->getextendedrows($sql);
		return $listdata;
	}


	/* ********************************************************************************
	Select count of mbl subsubs from subtrades except specified subsub
	******************************************************************************** */

	function getcount_mblsubsubbysubtrade($projectpkey, $subtradepkey, $subsubtradepkey) {

		$data=array();

		$sql="Select count(MBL.item_subsubtradepkey) as subsubcount from mbl_items MBL where MBL.item_projectpkey = '".$projectpkey."' and MBL.item_subtradepkey = '".$subtradepkey."' and MBL.item_subsubtradepkey <> '".$subsubtradepkey."' and MBL.item_subsubtradepkey <> ''";
		$data=$this->getextendedrow($sql);
		return $data['subsubcount'];
	}


	/* ********************************************************************************
	Select a mbl's latest submission/approval
	******************************************************************************** */

	function getdata_lastapproval() {

		$data=array();

		$sql="Select MBA.* from mbl_approvals MBA order by MBA.approval_date DESC, MBA.approval_type DESC limit 0,1";
		$data=$this->getextendedrow($sql);
		return $data;
	}


	/* FUNCTIONS TO INSERT RECORDS */


	/* ********************************************************************************
	Insert/Create a new mbl item
	Duplicate issue?
	******************************************************************************** */

	function insertdata_mblitem($p_item_pkey, $p_item_projectpkey, $p_item_subtradepkey, $p_item_subsubtradepkey, $p_item_level, $p_item_code, $p_item_description, $p_item_unit, $p_item_quantity, $p_item_rate, $p_item_amount) {

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

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

		$sa = $this->add_data("MBL Item [Add]", "mbl_items", "item_pkey, item_projectpkey, item_subtradepkey, item_subsubtradepkey, item_level, item_code, item_description, item_unit, item_quantity, item_rate, item_amount",
			"'".$new_item_pkey."','".$p_item_projectpkey.
			"','".$p_item_subtradepkey."','".$p_item_subsubtradepkey.
			"',".$p_item_level.
			",'".$p_item_code.
			"','".$p_item_description.
			"','".$p_item_unit."',".$p_item_quantity.",".$p_item_rate.
			",".$p_item_amount);

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


	/* ********************************************************************************
	Insert/Create a new mbl approval (cumulative)
	Type: 1 = Submission, 2 = Recommendation, 3 = Approval
	Status: 0 = Unprocessed/Unknown, 1 = Recommended/Approved, 2 = Cancelled, 3 = Rejected
	******************************************************************************** */

	function insertdata_approval($p_approval_pkey, $p_approval_type, $p_approval_userpkey, $p_approval_officername, $p_approval_date, $p_approval_status, $p_approval_remarks) {

		// clean/convert/add params..
		$p_approval_date=converttounixfull($p_approval_date." ".date('H:i:s'));	// use convertfull coz timestamp;

		$p_mbl_approved="0";
		if ($p_approval_status=="1") $p_mbl_approved = $p_approval_type;

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

		$sa = $this->add_data("MBL -> Approval [Add]", "mbl_approvals", "approval_pkey, approval_type, approval_userpkey, approval_officername, approval_date, approval_status, approval_remarks",
			"'".$new_approval_pkey."',".$p_approval_type.
			",'".$p_approval_userpkey."','".$p_approval_officername."','".$p_approval_date.
			"',".$p_approval_status.",'".$p_approval_remarks."'");

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


	/* FUNCTIONS TO UPDATE RECORDS */


	/* ********************************************************************************
	Update/Override an existing mbl item
	> 
	******************************************************************************** */

	function updatedata_mblitem($p_item_pkey, $p_item_projectpkey, $p_item_subtradepkey, $p_item_subsubtradepkey, $p_item_level, $p_item_code, $p_item_description, $p_item_unit, $p_item_quantity, $p_item_rate, $p_item_amount) {

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

		// update..
		$sa = $this->update_data("MBL Item [Edit]", "mbl_items", "item_projectpkey = '".$p_item_projectpkey.
			"', item_subtradepkey = '".$p_item_subtradepkey.
			"', item_subsubtradepkey = '".$p_item_subsubtradepkey.
			"', item_level = ".$p_item_level.
			", item_code = '".$p_item_code.
			"', item_description = '".$p_item_description.
			"', item_unit = '".$p_item_unit.
			"', item_quantity = ".$p_item_quantity.
			", item_rate = ".$p_item_rate.
			", item_amount = ".$p_item_amount,
			"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 mbl item
	******************************************************************************** */

	function deletedata_mblitem($p_item_pkey, $p_item_projectpkey) {

		// delete subject..
		$sa = $this->delete_data("MBL Item [Delete]", "mbl_items", "where item_pkey = '".$p_item_pkey."' and item_projectpkey = '".$p_item_projectpkey."'");

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

}
?>