<?
include_once('baseModelPMS.php');

class reportModel extends baseModelPMS {


	/* REPORT PROCESSING FUNCTIONS */


	/* ********************************************************************************
	 Process MBL report
	 ******************************************************************************** */
	
	function debugthis($Var)
	{
		echo '<pre>';
			var_dump($Var);
		echo '</pre>';
	}

	function processreport_mbl($rpt_projectpkey, $rpt_datefrom, $rpt_dateto) {

		// clean/convert/add params..
		$unix_datefrom=converttounix($rpt_datefrom);
		$unix_dateto=converttounix($rpt_dateto);
		$rpt_userpkey=$_SESSION['Xuserpkey'];

		// get project details..
		include_once('projectModel.php');
		$myprojectModel=new projectModelPMS;

		include_once('mblModel.php');
		$mymblModel=new mblModel;

		include_once('mroModel.php');
		$mymroModel=new mroModel;

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

		include_once('doModel.php');
		$mydoModel=new doModel;

		include_once('tradeModel.php');
		$mytradeModel=new tradeModel;

		$project_data = $myprojectModel->getdata_project($rpt_projectpkey);

		// flush old report data..
		$da1 = $this->delete_data("MBL Report [Delete]", "rpt_mbl_header", "where hdr_userpkey = '".$rpt_userpkey."'");
		$da2 = $this->delete_data("MBL Report [Delete]", "rpt_mbl_details", "where data_userpkey = '".$rpt_userpkey."'");

		// insert new header..
		$sa = $this->add_data("MBL Report [Add]", "rpt_mbl_header", "hdr_userpkey, hdr_projectname, hdr_projectdescription, hdr_datefrom, hdr_dateto",
			"'".$rpt_userpkey."','".$project_data['projectname'].
			"','".$project_data['projectdescription'].
			"','".$rpt_datefrom."','".$rpt_dateto."'");

		// process data..

		// get mbl items
		$mbl_list = $mymblModel->getlist_mblitems($rpt_projectpkey);

		$itemno=0;
		$prevsub = "";
		$prevsubsub = "";
		
		foreach ($mbl_list as $mymbl) {
			
			//Kai: added flag to check inserted
			$blnMroInserted = false;

			//Kai: Empty means lump sump.
			//alter table rpt_mbl_details add column is_lumpsump int(1) Not null; #add this to sql table
			if (empty($mymbl['item_subsubtradepkey']))
			{
				$mro_items = $mymroModel->getlist_mroitemsbysubtrade($rpt_projectpkey, $mymbl['item_subtradepkey']);
				$itemno = 0;

				//Kai: treat each mro as a entity, get PO
				foreach ($mro_items as $mroitem) {
						
					//Kai: if not previous item, increment
					if ( $mroitem['item_description'] !=  $PrevItem)
					$itemno++;

					$PrevItem = $mroitem['item_description'];

					// get dos
					$po_information = $mypoModel->getlist_poitems_mblreport($rpt_projectpkey, $mroitem['item_subtradepkey'], $mroitem['item_subsubtradepkey'], $unix_datefrom, $unix_dateto, $mroitem['item_mropkey']);
					$do_list = $mydoModel->getlist_do_mblreport($rpt_projectpkey, $po_information[0]['po_pkey'], $mroitem['item_subsubtradepkey']);

					$poquantity = (float) $po_information[0]['item_quantity'];
					$porate = (float) $po_information[0]['item_rate'];
					$poamount = ($poquantity * $porate);
					
					//Kai: if mbl rate is empty use po rate.
					$dblRate = ($mymbl['item_rate'] > 0) ? $mymbl['item_rate'] : $po_information[0]['item_rate'];
					$mblamount = ($mymbl['item_quantity']*$dblRate);
					
					$doquantity = 0;
					$dorate = $porate;
					$doamount = 0;

					// derive balance (nbmvo item - do item)
					$balquantity = 0;
					$balrate = $porate;
					$balamount = 0;

					foreach ($do_list as $mydo) {
						$doquantity += $mydo['item_quantity'];
					}
					
					$doamount = ($doquantity * $dorate);
					$balquantity = $poquantity - $doquantity;
					$balamount = ($balquantity * $balrate);
					
					$po_id = poidstr($po_information[0]['po_no']);
					if (empty($po_id))
					{
						$po_id = 'no po';
						$poquantity = 0.0000;
						$porate = $mymbl['item_rate'];
						$poamount = 0;
					}
					
					$sa = $this->add_data("MBL Report [Add]", "rpt_mbl_details", "data_userpkey, data_itemtype, data_itemno, data_itemgroup, data_itemname,
					data_itemunit, data_mblquantity, data_mblrate, data_mblamount, data_pono, data_poquantity, data_porate, data_poamount, data_deliveredquantity,
					data_deliveredrate, data_deliveredamount, data_balancequantity, data_balancerate, data_balanceamount, is_lumpsump",
						"'".$rpt_userpkey."',"."1".",".$itemno.",'".$mymbl['parentdesc']."'".
						",'".mysql_escape_string($mroitem['item_description']).
						"','".$mymbl['item_unit'].
						"',".$mymbl['item_quantity'].",".$dblRate.",".$mblamount.
						",'".$po_id.
						"',".$poquantity.",".$porate.",".$poamount.
						",".$doquantity.",".$dorate.",".$doamount.
						",".$balquantity.",".$balrate.",".$balamount.",1");	

					//Kai: added flag to check inserted
					$blnMroInserted = true;
						
				}

			} else {
				//Kai: Else treat it as normal mbl item
				//Kai: Todo:include mro pkey to get correct data, else data seem incorrect.
				//Future use.
				$mro_items = $mymroModel->getlist_mroitemsbysubsubtrade($rpt_projectpkey, $mymbl['item_subsubtradepkey']);
				$po_list = $mypoModel->getlist_poitems_mblreport_budgeted($rpt_projectpkey, $mymbl['item_subtradepkey'], $mymbl['item_subsubtradepkey'], $unix_datefrom, $unix_dateto);
					
				if ($prevsub != $mymbl['parentdesc']) $itemno=0;
				if ($prevsubsub != $mymbl['item_description']) $itemno++;
				//$this->debugthis($mro_items);
				foreach ($mro_items as $budgeted)
				{					
					//Kai: don't include 0 quantity MRO
					if ($budgeted['item_quantity'] <= 0)
					{
						continue;
					}
					
					$po_list = $mypoModel->getlist_poitems_mblreport_budgeted($rpt_projectpkey, $mymbl['item_subtradepkey'], $mymbl['item_subsubtradepkey'], $unix_datefrom, $unix_dateto, $budgeted['item_mropkey']);
					if (empty($po_list)) $po_list = array(1);
					
					foreach ($po_list as $mypo)
					{

					$poquantity = (float) $mypo['item_quantity'];
					$porate = (float) $mypo['item_rate'];
					$poamount = ($poquantity * $porate);
					
					//Kai: if mbl rate is empty use po rate.
					$dblRate = ($mymbl['item_rate'] > 0) ? $mymbl['item_rate'] : $porate;
					$mblamount = ($mymbl['item_quantity']*$dblRate);

					// get dos
					$do_list = $mydoModel->getlist_do_mblreport($rpt_projectpkey, $mypo['po_pkey'], $mymbl['item_subsubtradepkey']);
					$doquantity = 0;
					$dorate = $porate;
					$doamount = 0;

					// derive balance (mbl item - do item)
					$balquantity = 0;
					$balrate = $porate;
					$balamount = 0;

					foreach ($do_list as $mydo) {
						$doquantity += $mydo['item_quantity'];
					}
					$doamount = ($doquantity * $dorate);
					$balquantity = $poquantity - $doquantity;
					$balamount = ($balquantity * $balrate);
					
					$po_id = poidstr($mypo['po_no']);
					//Kai if no PO id, we store the mbl quantity inside PO, so report can intepret and display correctly
					if (empty($po_id))
					{
						$po_id = 'no po';
						$poquantity = 0.0000;
						$porate = $mymbl['item_rate'];
						$poamount = 0;
					}

					//tambahan
					$sa = $this->add_data("MBL Report [Add]", "rpt_mbl_details", "data_userpkey, data_itemtype, data_itemno, data_itemgroup, data_itemname, data_itemunit, data_mblquantity, data_mblrate, data_mblamount, data_pono, data_poquantity, data_porate, data_poamount, data_deliveredquantity, data_deliveredrate, data_deliveredamount, data_balancequantity, data_balancerate, data_balanceamount",
					"'".$rpt_userpkey."',"."1".",".$itemno.",'".$mymbl['parentdesc'].
					"','".mysql_escape_string($mymbl['item_description']).
					"','".$mymbl['item_unit'].
					"',".$mymbl['item_quantity'].",".$dblRate.",".$mblamount.
					",'".$po_id.
					"',".$poquantity.",".$porate.",".$poamount.
					",".$doquantity.",".$dorate.",".$doamount.
					",".$balquantity.",".$balrate.",".$balamount);
					}
					
					//Kai: added flag to check inserted
					$blnMroInserted = true;
		
				}
				
				$prevsub = $mymbl['parentdesc'];
				$prevsubsub = $mymbl['item_description'];
			}
			
			//Kai: No MRO inserted, insert MBL as data instead
			if ($blnMroInserted == false)
			{
				//Kai if no PO id, we store the mbl quantity inside PO, so report can intepret and display correctly
				$itemno = 1;
				$po_id = 'no po';
				$dblRate = $mymbl['item_rate'];
				$mblamount = $dblRate * $mymbl['item_quantity'];
				
				$sa = $this->add_data("MBL Report [Add]", "rpt_mbl_details", "data_userpkey, data_itemtype, data_itemno, data_itemgroup, data_itemname, data_itemunit, data_mblquantity, data_mblrate, data_mblamount, data_pono, data_poquantity, data_porate, data_poamount, data_deliveredquantity, data_deliveredrate, data_deliveredamount, data_balancequantity, data_balancerate, data_balanceamount",
				"'".$rpt_userpkey."',"."1".",".$itemno.",'".$mymbl['parentdesc'].
				"','".mysql_escape_string($mymbl['item_description']).
				"','".$mymbl['item_unit'].
				"',".$mymbl['item_quantity'].",".$dblRate.",".$mblamount.
				",'".$po_id.
				"',0.0000,".$dblRate.",0.00".
				",0.0000,".$dblRate.",0.00".
				",0.0000,".$dblRate.",0.00");
			}
		}

		// get mro exceeded items (vo listed)
		$bme_list = $mymroModel->getlist_mroitems_bme($rpt_projectpkey,"3");

		$bmequantity = 0;
		$bmeamount = 0;
		$itemno=0;
		$prevsub = "";
		$prevsubsub = "";

		foreach ($bme_list as $mybme) {

			// get pos
			$po_list = $mypoModel->getlist_poitems_mblreport($rpt_projectpkey, $mybme['item_subtradepkey'], $mybme['item_subsubtradepkey'], $unix_datefrom, $unix_dateto, $mybme['mro_pkey']);

			foreach ($po_list as $mypo) {

				//Kai: reset count
				if ($prevsub != $mybme['item_subtradepkey']) $itemno=0;
				if ($prevsubsub != $mybme['item_description']) $itemno=0;
				$itemno++;

				$poquantity = $mypo['item_quantity'];
				$porate = $mypo['item_rate'];
				$poamount = ($poquantity * $porate);

				// get dos

				$do_list = $mydoModel->getlist_do_mblreport($rpt_projectpkey, $mypo['po_pkey'], $mybme['item_subsubtradepkey']);

				$doquantity = 0;
				$dorate = $porate;
				$doamount = 0;

				// derive balance (bme item - do item)

				$balquantity = 0;
				$balrate = $porate;
				$balamount = 0;

				foreach ($do_list as $mydo) {
					$doquantity += $mydo['item_quantity'];
				}
				$doamount = ($doquantity * $dorate);
				$balquantity = $poquantity - $doquantity;
				$balamount = ($balquantity * $balrate);
					
				$bmequantity += $mybme['item_quantity'];
				$bmerate = $mybme['item_rate'];
				//Kai: if mbl rate is empty use po rate.
				$dblRate = ($mybme['item_rate'] > 0) ? $mybme['item_rate'] : $porate;
				$bmeamount = ($mybme['item_quantity']*$dblRate);

				$sa = $this->add_data("MBL Report [Add]", "rpt_mbl_details", "data_userpkey, data_itemtype, data_itemno, data_itemgroup, data_itemname, data_itemunit, data_mblquantity, data_mblrate, data_mblamount, data_pono, data_poquantity, data_porate, data_poamount, data_deliveredquantity, data_deliveredrate, data_deliveredamount, data_balancequantity, data_balancerate, data_balanceamount",
						"'".$rpt_userpkey."',"."2".",".$itemno.",'"."".
						"','".mysql_escape_string($mybme['item_description']).
						"','".$mybme['item_unit'].
						"',".$mybme['item_quantity'].",".$dblRate.",".$bmeamount.
						",'".poidstr($mypo['po_no']).
						"',".$poquantity.",".$porate.",".$poamount.
						",".$doquantity.",".$dorate.",".$doamount.
						",".$balquantity.",".$balrate.",".$balamount);
			}
			
			//Kai : if po_list is empty
			if ( !count($po_list) )
			{
				$bmeamount = ($mybme['item_quantity']*$mybme['item_rate']);
				
				$sa = $this->add_data("MBL Report [Add]", "rpt_mbl_details", "data_userpkey, data_itemtype, data_itemno, data_itemgroup, data_itemname, data_itemunit, data_mblquantity, data_mblrate, data_mblamount, data_pono, data_poquantity, data_porate, data_poamount, data_deliveredquantity, data_deliveredrate, data_deliveredamount, data_balancequantity, data_balancerate, data_balanceamount",
						"'".$rpt_userpkey."',"."2".",1,'"."".
						"','".mysql_escape_string($mybme['item_description']).
						"','".$mybme['item_unit'].
						"',".$mybme['item_quantity'].",".$mybme['item_rate'].",".$bmeamount.
						",'no po',0.0000,0.00,0".
						",0.0000,0.00,0".
						",0.0000,0.00,0");				
			}

			$prevsub = $mybme['item_subtradepkey'];
			$prevsubsub = $mybme['item_description'];
		}

		// get mro variance items (vo not listed)
		$nbmvo_list = $mymroModel->getlist_mroitems_nbmvo($rpt_projectpkey,"3");
		//$mymroModel->getlist_mro_nbmvo($rpt_projectpkey, 3);
		$nbmvoquantity = 0;
		$nbmvoamount = 0;
		$itemno=0;
		$prevsub = "";
		$prevsubsub = "1";
			
		foreach ($nbmvo_list as $mynbmvo) {

			// Kai : pass in mropkey, add trade model for getting item description
			// Kai : Note: The if function in this loop looks buggy.
			// get pos
			$po_list = $mypoModel->getlist_poitems_mblreport($rpt_projectpkey, $mynbmvo['item_subtradepkey'], $mynbmvo['item_subsubtradepkey'], $unix_datefrom, $unix_dateto, $mynbmvo['mro_pkey']);
			//$trade_data=$mytradeModel->getdata_trade($mynbmvo['item_subsubtradepkey']);

			foreach ($po_list as $mypo) {

				//Kai: reset count
				//if ($prevsub != $mynbmvo['item_subtradepkey']) $itemno=0;
				if ($prevsubsub != $mynbmvo['item_subsubtradepkey']) $itemno=0; 
				$itemno++;

				$poquantity = $mypo['item_quantity'];
				$porate = $mypo['item_rate'];
				$poamount = ($poquantity * $porate);

				// get dos
				$do_list = $mydoModel->getlist_do_mblreport($rpt_projectpkey, $mypo['po_pkey'], $mynbmvo['item_subsubtradepkey']);

				$doquantity = 0;
				$dorate = $porate;
				$doamount = 0;

				// derive balance (nbmvo item - do item)
				$balquantity = 0;
				$balrate = $porate;
				$balamount = 0;

				foreach ($do_list as $mydo) {
					$doquantity += $mydo['item_quantity'];
				}
				$doamount = ($doquantity * $dorate);
				$balquantity = $poquantity - $doquantity;
				$balamount = ($balquantity * $balrate);
					
				$nbmvoquantity += $mynbmvo['item_quantity'];
				$nbmvorate = $mynbmvo['item_rate'];
				//Kai: if mbl rate is empty use po rate.
				$dblRate = ($mynbmvo['item_rate'] > 0) ? $mynbmvo['item_rate'] : $porate;
				$nbmvoamount = ($mynbmvo['item_quantity']*$dblRate);

				$sa = $this->add_data("MBL Report [Add]", "rpt_mbl_details", "data_userpkey, data_itemtype, data_itemno, data_itemgroup, data_itemname,
					data_itemunit, data_mblquantity, data_mblrate, data_mblamount, data_pono, data_poquantity, data_porate, data_poamount, data_deliveredquantity,
					data_deliveredrate, data_deliveredamount, data_balancequantity, data_balancerate, data_balanceamount",
						"'".$rpt_userpkey."',"."3".",".$itemno.",''".
						",'".mysql_escape_string($mynbmvo['item_description']).
						"','".$mynbmvo['item_unit'].
						"',".$mynbmvo['item_quantity'].",".$dblRate.",".$nbmvoamount.
						",'".poidstr($mypo['po_no']).
						"',".$poquantity.",".$porate.",".$poamount.
						",".$doquantity.",".$dorate.",".$doamount.
						",".$balquantity.",".$balrate.",".$balamount);

			}
			
			//Kai : if po_list is empty
			if ( !count($po_list) )
			{
				$nbmvoamount = ($mynbmvo['item_quantity']*$mynbmvo['item_rate']);
				
				$sa = $this->add_data("MBL Report [Add]", "rpt_mbl_details", "data_userpkey, data_itemtype, data_itemno, data_itemgroup, data_itemname,
					data_itemunit, data_mblquantity, data_mblrate, data_mblamount, data_pono, data_poquantity, data_porate, data_poamount, data_deliveredquantity,
					data_deliveredrate, data_deliveredamount, data_balancequantity, data_balancerate, data_balanceamount",
						"'".$rpt_userpkey."',"."3".",1,''".
						",'".mysql_escape_string($mynbmvo['item_description']).
						"','".$mynbmvo['item_unit'].
						"',".$mynbmvo['item_quantity'].",".$mynbmvo['item_rate'].",".$nbmvoamount.
						",'no po',0.0000,0.00,0".
						",0.0000,0.00,0".
						",0.0000,0.00,0");				
			}

			$prevsub = $mynbmvo['item_subtradepkey'];
			$prevsubsub = $mynbmvo['item_subsubtradepkey'];
		}

		// get mro subcon

		$nbmsc_list = $mymroModel->getlist_mroitems_nbmsc($rpt_projectpkey,"3");

		$nbmscquantity = 0;
		$nbmscamount = 0;
		$itemno=0;
		$prevsub = '';
		$prevsubsub = '';

		foreach ($nbmsc_list as $mynbmsc) {

			// get pos
			$po_list = $mypoModel->getlist_poitems_mblreport($rpt_projectpkey, $mynbmsc['item_subtradepkey'], $mynbmsc['item_subsubtradepkey'], $unix_datefrom, $unix_dateto, $mynbmsc['mro_pkey']);

			foreach ($po_list as $mypo) {

				//Kai: reset count
				if ($prevsubsub != $mynbmsc['item_subsubtradepkey']) $itemno = 0;
				
				$itemno++;

				$poquantity = $mypo['item_quantity'];
				$porate = $mypo['item_rate'];
				$poamount = ($poquantity * $porate);

				// get dos

				$do_list = $mydoModel->getlist_do_mblreport($rpt_projectpkey, $mypo['po_pkey'], $mynbmsc['item_subsubtradepkey']);

				$doquantity = 0;
				$dorate = $porate;
				$doamount = 0;

				// derive balance (nbmsc item - do item)

				$balquantity = 0;
				$balrate = $porate;
				$balamount = 0;

				foreach ($do_list as $mydo) {
					$doquantity += $mydo['item_quantity'];
				}
				$doamount = ($doquantity * $dorate);
				$balquantity = $poquantity - $doquantity;
				$balamount = ($balquantity * $balrate);

				$nbmscquantity += $mynbmsc['item_quantity'];
				$nbmscrate = $mynbmsc['item_rate'];
				//Kai: if mbl rate is empty use po rate.
				$dblRate = ($mynbmsc['item_rate'] > 0) ? $mynbmsc['item_rate'] : $porate;
				$nbmscamount = ($mynbmsc['item_quantity']*$dblRate);

				$sa = $this->add_data("MBL Report [Add]", "rpt_mbl_details", "data_userpkey, data_itemtype, data_itemno, data_itemgroup, data_itemname, data_itemunit, data_mblquantity, data_mblrate, data_mblamount, data_pono, data_poquantity, data_porate, data_poamount, data_deliveredquantity, data_deliveredrate, data_deliveredamount, data_balancequantity, data_balancerate, data_balanceamount",
					"'".$rpt_userpkey."',"."4".",".$itemno.",'"."".
					"','".mysql_escape_string($mynbmsc['item_description']).
					"','".$mynbmsc['item_unit'].
					"',".$mynbmsc['item_quantity'].",".$dblRate.",".$nbmscamount.
					",'".poidstr($mypo['po_no']).
					"',".$poquantity.",".$porate.",".$poamount.
					",".$doquantity.",".$dorate.",".$doamount.
					",".$balquantity.",".$balrate.",".$balamount);
			}
			
			//Kai : if po_list is empty
			if ( !count($po_list) )
			{
				$nbmscquantity += $mynbmsc['item_quantity'];
				$nbmscrate = $mynbmsc['item_rate'];
				$nbmscamount = ($mynbmsc['item_quantity']*$mynbmsc['item_rate']);
				
				$sa = $this->add_data("MBL Report [Add]", "rpt_mbl_details", "data_userpkey, data_itemtype, data_itemno, data_itemgroup, data_itemname, data_itemunit, data_mblquantity, data_mblrate, data_mblamount, data_pono, data_poquantity, data_porate, data_poamount, data_deliveredquantity, data_deliveredrate, data_deliveredamount, data_balancequantity, data_balancerate, data_balanceamount",
					"'".$rpt_userpkey."',"."4".",1,'"."".
					"','".mysql_escape_string($mynbmsc['item_description']).
					"','".$mynbmsc['item_unit'].
					"',".$mynbmsc['item_quantity'].",".$mynbmsc['item_rate'].",".$nbmscamount.
					",'no po',0.0000,0.00,0".
					",0.0000,0.00,0".
					",0.0000,0.00,0");				
			}


			$prevsub = $mynbmsc['item_subtradepkey'];
			$prevsubsub = $mynbmsc['item_subsubtradepkey'];
		}

		// insert data..

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

	}



	/* ********************************************************************************
	 Process Quantity Variance report
	 ******************************************************************************** */

	function processreport_qv($rpt_projectpkey, $rpt_datefrom, $rpt_dateto) {

		// clean/convert/add params..
		$unix_datefrom=converttounix($rpt_datefrom);
		$unix_dateto=converttounix($rpt_dateto);
		$rpt_userpkey=$_SESSION['Xuserpkey'];

		// get project details..
		include_once('projectModel.php');
		$myprojectModel=new projectModelPMS;

		$project_data = $myprojectModel->getdata_project($rpt_projectpkey);

		// flush old report data..
		//$da1 = $this->delete_data("QV Report [Delete]", "rpt_qv_header", "where hdr_userpkey = '".$rpt_userpkey."'");
		$da1 = $this->delete_data("QV Report [Delete]", "rpt_qv_header");
		//$da2 = $this->delete_data("QV Report [Delete]", "rpt_qv_details", "where data_userpkey = '".$rpt_userpkey."'");
                $da2 = $this->delete_data("QV Report [Delete]", "rpt_qv_details");

		// insert new header..

		$sa = $this->add_data("QV Report [Add]", "rpt_qv_header", "hdr_userpkey, hdr_projectname, hdr_projectdescription, hdr_datefrom, hdr_dateto",
			"'".$rpt_userpkey."','".$project_data['projectname'].
			"','".$project_data['projectdescription'].
			"','".$rpt_datefrom."','".$rpt_dateto."'");

		// process data..

		// TODO: date range for MRO

		include_once('mblModel.php');
		$mymblModel=new mblModel;

		include_once('mroModel.php');
		$mymroModel=new mroModel;

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

		$mroqty = 0;
		$varqty = 0;
		$percentage = 0;

		$prevsub = "";
		$curgroup = "";
		$itemno=0;
		$subsubno=0;	//"a";

		// mbl

		$mbl_items = $mymblModel->getlist_mblitems($rpt_projectpkey);

		foreach ($mbl_items as $mblitem) {
			if ($mblitem['parentcode']!=$prevsub) {
				$prevsub = $mblitem['parentcode'];
				$curgroup = $mblitem['parentdesc'];
				$itemno++;
				$subsubno=0;	//"a";

				if ($mblitem['item_subsubtradepkey']=="") {

					// get mro quantities by subtrade

					$mroqty=0;
					//$mro_list = $mymroModel->getlist_mroitemsbysubtrade($rpt_projectpkey, $mblitem['item_subtradepkey']);
					// myapit 2010-07-28
					$mro_list = $mymroModel->getlist_mroitemsbysubtrade_withdate_arie($rpt_projectpkey, $mblitem['item_subtradepkey'],$unix_datefrom,$unix_dateto);
						
					foreach ($mro_list as $mro) {
						$mroqty += $mro['item_quantity'];
					}
					$varqty = $mroqty - $mblitem['item_quantity'];
					$percentage = ($varqty / $mblitem['item_quantity']) * 100;

					$sa = $this->add_data("QV Report [Add]", "rpt_qv_details", "data_userpkey, data_itemtype, data_itemno, data_itemgroup, data_itemname, data_itemunit, data_estimatedquantity, data_orderquantity, data_quantityvariance, data_percentage, data_remarks",
						"'".$rpt_userpkey."',"."1".",'".$itemno."','".$mblitem['parentdesc'].
						"','"."".
						"','".$mblitem['item_unit']."',".$mblitem['item_quantity'].
						",".$mroqty.",".$varqty.",".$percentage.",''");
				} 
				else 
				{

					$sa = $this->add_data("QV Report [Add]", "rpt_qv_details", "data_userpkey, data_itemtype, data_itemno, data_itemgroup, data_itemname, data_itemunit, data_estimatedquantity, data_orderquantity, data_quantityvariance, data_percentage, data_remarks",
						"'".$rpt_userpkey."',"."1".",'".$itemno."','".$mblitem['parentdesc'].
						"','"."".
						"','".$mblitem['item_unit']."',"."0".
						","."0".","."0".","."0".",''");

					// get mro quantities

					$mroqty=0;
					$mro_list = $mymroModel->getlist_mroitemsbysubsubtrade_arie2($rpt_projectpkey, $mblitem['item_subsubtradepkey'],$unix_datefrom,$unix_dateto);
					foreach ($mro_list as $mro) {
						$mroqty += $mro['item_quantity'];
					}

                                       //arie add - add exceeded item
					$mroqty2 = 0;
					$mro_list2 = $mymroModel->getlist_mroitemsbysubsubtrade_arie($rpt_projectpkey, $mblitem['item_subsubtradepkey'],$unix_datefrom,$unix_dateto);
					foreach ($mro_list2 as $mro2) {
						$mroqty2 += $mro2['item_quantity'];
					}					
					
                                       if ($mroqty2!='')
					{$mroqty = $mroqty  + $mroqty2;}    					
					//end arie add	   


					$varqty = $mroqty - $mblitem['item_quantity'];
					$percentage = ($varqty / $mblitem['item_quantity']) * 100;

					$subsubno++;
					$sa = $this->add_data("QV Report [Add]", "rpt_qv_details", "data_userpkey, data_itemtype, data_itemno, data_itemgroup, data_itemname, data_itemunit, data_estimatedquantity, data_orderquantity, data_quantityvariance, data_percentage, data_remarks",
						"'".$rpt_userpkey."',"."1".",'".$itemno.".".$subsubno."','".$curgroup.
						"','".$mblitem['item_description'].
						"','".$mblitem['item_unit']."',".$mblitem['item_quantity'].
						",".$mroqty.",".$varqty.",".$percentage.",''");
					//$subsubno++;

				}
			} 
			else 
			{

				// get mro quantities

				$mroqty=0;
				$mro_list = $mymroModel->getlist_mroitemsbysubsubtrade_arie2($rpt_projectpkey, $mblitem['item_subsubtradepkey'], $unix_datefrom,$unix_dateto);
				foreach ($mro_list as $mro) {
					$mroqty += $mro['item_quantity'];
				}
				
                                //arie add - add exceeded item	
				$mroqty2 = 0;
				$mro_list2 = $mymroModel->getlist_mroitemsbysubsubtrade_arie($rpt_projectpkey, $mblitem['item_subsubtradepkey']);
				foreach ($mro_list2 as $mro2) 
                                {$mroqty2 += $mro2['item_quantity'];}
					
				$mroqty =  $mroqty + $mroqty2;
				//end arie add	


                                $varqty = $mroqty - $mblitem['item_quantity'];
				$percentage = ($varqty / $mblitem['item_quantity']) * 100;

				$subsubno++;
				$sa = $this->add_data("QV Report [Add]", "rpt_qv_details", "data_userpkey, data_itemtype, data_itemno, data_itemgroup, data_itemname, data_itemunit, data_estimatedquantity, data_orderquantity, data_quantityvariance, data_percentage, data_remarks",
					"'".$rpt_userpkey."',"."1".",'".$itemno.".".$subsubno."','".$curgroup.
					"','".$mblitem['item_description'].
					"','".$mblitem['item_unit']."',".$mblitem['item_quantity'].
					",".$mroqty.",".$varqty.",".$percentage.",''");
				//$subsubno++;

			}

		}

		// non-mbl
		$mroqty = 0;
		$varqty = 0;
		$percentage = 0;
		$itemno = 0;
		$subsubno = 0;
		$prevsub = "";
		$prevsubsub = "";

		$nbmitems_list = $mymroModel->getlist_mroitems_nbm_arie($rpt_projectpkey,"3", $unix_datefrom,$unix_dateto);

		foreach ($nbmitems_list as $nbmitem) {

			if ($prevsub != $nbmitem['mro_subtradepkey']) {
				$itemno++;
				$subsubno=0;
				$sa = $this->add_data("QV Report [Add]", "rpt_qv_details", "data_userpkey, data_itemtype, data_itemno, data_itemgroup, data_itemname, data_itemunit, data_estimatedquantity, data_orderquantity, data_quantityvariance, data_percentage, data_remarks",
					"'".$rpt_userpkey."',"."2".",'".$itemno."','".$nbmitem['tradedescription'].
					"','"."".
					"','".""."',"."0".
					","."0".","."0".","."0".",''");
				$prevsub = $nbmitem['mro_subtradepkey'];
			}

			if ($prevsubsub != $nbmitem['item_subsubtradepkey']) {

				$poqty = $mypoModel->getquantity_poitemsbysubsubtrade_arie($rpt_projectpkey, $nbmitem['item_subsubtradepkey'], "3");				
				$varqty = $poqty;
				$percentage = 100;
				if ($poqty==0)	$percentage=0;
				$remarks = "";
				//$itemno++;
				$subsubno++;

				$sa = $this->add_data("QV Report [Add]", "rpt_qv_details", "data_userpkey, data_itemtype, data_itemno, data_itemgroup, data_itemname, data_itemunit, data_estimatedquantity, data_orderquantity, data_quantityvariance, data_percentage, data_remarks",
					"'".$rpt_userpkey."',"."2".",'".$itemno.".".$subsubno."','".$nbmitem['tradedescription'].
					"','".$nbmitem['item_description'].
					"','".$nbmitem['item_unit']."',"."0.000".
					",".$poqty.",".$varqty.",".$percentage.",''");
			}
			$prevsubsub = $nbmitem['item_subsubtradepkey'];
		}

		// insert data..

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

	}


	/* ********************************************************************************
	 Process Purchase Order report
	 ******************************************************************************** */

	function processreport_po($rpt_projectpkey, $rpt_supplierpkey, $rpt_pono, $rpt_podate, $rpt_subtradepkey, $rpt_subsubtradepkey, $rpt_datefrom, $rpt_dateto) {

		// clean/convert/add params..
		$unix_podate=converttounix($rpt_podate);
		$unix_datefrom=converttounix($rpt_datefrom);
		$unix_dateto=converttounix($rpt_dateto);
		$rpt_userpkey=$_SESSION['Xuserpkey'];

		// get project,po,supplier details..
		include_once('projectModel.php');
		$myprojectModel=new projectModelPMS;

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

		include_once('doModel.php');
		$mydoModel=new doModel;

		include_once('supplierModel.php');
		$mysupplierModel=new supplierModel;

		$project_data = $myprojectModel->getdata_project($rpt_projectpkey);
		$po_data = $mypoModel->getdata_po($rpt_pono);
		$supplier_data = $mysupplierModel->getdata_supplier($rpt_supplierpkey);

		// flush old report data..
		$da1 = $this->delete_data("PO Report [Delete]", "rpt_po_header", "where hdr_userpkey = '".$rpt_userpkey."'");
		$da2 = $this->delete_data("PO Report [Delete]", "rpt_po_details", "");

		// insert new header..
		

		$sa = $this->add_data("PO Report [Add]", "rpt_po_header", "hdr_userpkey, hdr_projectname, hdr_projectdescription, hdr_suppliername, hdr_pono, hdr_podate, hdr_subtrade, hdr_subsubtrade, hdr_datefrom, hdr_dateto",
			"'".$rpt_userpkey."','".$project_data['projectname'].
			"','".$project_data['projectdescription']."','".$supplier_data['supplier_name'].
			"','".poidstr($rpt_pono)."','".$rpt_podate.
			"','".$subtrade_data['tradedescription']."','".$subsubtrade_data['tradedescription'].
			"','".$rpt_datefrom."','".$rpt_dateto."'");

		// process data..

		// get pos..

		$po_list = $mypoModel->getlist_poitems_poreport_yus($rpt_projectpkey, $rpt_supplierpkey, $rpt_pono, $rpt_subtradepkey, $rpt_subsubtradepkey, $unix_datefrom, $unix_dateto);

		foreach ($po_list as $mypo) {

			// get do data

			$do_list = $mydoModel->getlist_do_poreport($rpt_projectpkey, $mypo['po_pkey'], $mypo['po_supplierpkey'], $mypo['item_subsubtradepkey']);

			if ($rpt_supplierpkey!="") {
				$mysuppname = $supplier_data['supplier_name'];
			} else {
				$mysupp = $mysupplierModel->getdata_supplier($mypo['po_supplierpkey']);
				$mysuppname = $mysupp['supplier_name'];
			}
                        $varDONo ="";
                        $varPOTotal = $mypo['item_quantity'] * $mypo['item_rate'];
		        $varPOQty = $mypo['item_quantity'];
                        $myamount = 0;

			foreach ($do_list as $mydo) {

				
				$myamount = $mydo['item_quantity'] * $mypo['item_rate'];
				$varPOTotal = $mypo['item_quantity'] * $mypo['item_rate'];
				$varPOQty = $mypo['item_quantity'];
                                $varDONo = $mydo['item_dono'];				



				$sa = $this->add_data("PO Report [Add]", "rpt_po_details", "data_userpkey, data_pono, data_podate, data_suppliername, data_itemname, data_dono, data_dodate, data_quantity, data_rate, data_poqty, data_pototal, data_amount",

					"'".$rpt_userpkey."','".poidstr($mypo['po_no']).

					"','".unconverttounixslash($mypo['po_date'])."','".$mysuppname.

					"','".ununescape($mypo['item_description']).

					"','".$mydo['item_dono']."','".unconverttounixslash($mydo['item_dodate']).

					"',".$mydo['item_quantity'].",".$mypo['item_rate'].",$varPOQty,$varPOTotal,".$myamount);


			}
                         if ($varDONo =="")//klu tak ada DO
			{
			  $sa = $this->add_data("PO Report [Add]", "rpt_po_details", "data_userpkey, data_pono, data_podate, data_suppliername, data_itemname,    data_rate, data_poqty, data_pototal, data_amount",
					"'".$rpt_userpkey."','".poidstr($mypo['po_no']).
					"','".unconverttounixslash($mypo['po_date'])."','".$mysuppname.
					"','".ununescape($mypo['item_description']).
					"',".$mypo['item_rate'].",$varPOQty,$varPOTotal,".$myamount);
			}
		}

		// insert data..

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

	}


	/* ********************************************************************************
	 Process Supplier Performance report
	 ******************************************************************************** */

	function processreport_sp($rpt_projectpkey, $rpt_datefrom, $rpt_dateto, $rpt_supplierpkey) {

		// clean/convert/add params..
		$unix_datefrom=converttounix($rpt_datefrom);
		$unix_dateto=converttounix($rpt_dateto);
		$rpt_userpkey=$_SESSION['Xuserpkey'];

		// get project & supplier details..
		include_once('projectModel.php');
		$myprojectModel=new projectModelPMS;

		include_once('supplierModel.php');
		$mysupplierModel=new supplierModel;

		$project_data = $myprojectModel->getdata_project($rpt_projectpkey);
		$supplier_data = $mysupplierModel->getdata_supplier($rpt_supplierpkey);

		// flush old report data..
		$da1 = $this->delete_data("SP Report [Delete]", "rpt_sp_header", "where hdr_userpkey = '".$rpt_userpkey."'");
		$da2 = $this->delete_data("SP Report [Delete]", "rpt_sp_details", "where data_userpkey = '".$rpt_userpkey."'");

		// insert new header..

		$sa = $this->add_data("SP Report [Add]", "rpt_sp_header", "hdr_userpkey, hdr_projectname, hdr_projectdescription, hdr_datefrom, hdr_dateto, hdr_suppliername",
			"'".$rpt_userpkey."','".$project_data['projectname'].
			"','".$project_data['projectdescription'].
			"','".$rpt_datefrom."','".$rpt_dateto."','".$supplier_data['supplier_name']."'");

		// process data..

		// insert data..

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

	}


	/* ********************************************************************************
	 Process Supplier Performance Summary report
	 ******************************************************************************** */

	function processreport_sps($rpt_projectpkey, $rpt_datefrom, $rpt_dateto, $rpt_supplierpkey) {

		// clean/convert/add params..
		$unix_datefrom=converttounix($rpt_datefrom);
		$unix_dateto=converttounix($rpt_dateto);
		$rpt_userpkey=$_SESSION['Xuserpkey'];

		// get project & supplier details..
		include_once('projectModel.php');
		$myprojectModel=new projectModelPMS;

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

		include_once('doModel.php');
		$mydoModel=new doModel;

		include_once('supplierModel.php');
		$mysupplierModel=new supplierModel;

		$project_data = $myprojectModel->getdata_project($rpt_projectpkey);
		$supplier_data = $mysupplierModel->getdata_supplier($rpt_supplierpkey);

		// flush old report data..
		$da1 = $this->delete_data("SPS Report [Delete]", "rpt_sps_header", "where hdr_userpkey = '".$rpt_userpkey."'");
		$da2 = $this->delete_data("SPS Report [Delete]", "rpt_sps_details", "where data_userpkey = '".$rpt_userpkey."'");

		// insert new header..

		$sa = $this->add_data("SP Report [Add]", "rpt_sps_header", "hdr_userpkey, hdr_projectname, hdr_projectdescription, hdr_datefrom, hdr_dateto, hdr_suppliername",
			"'".$rpt_userpkey."','".$project_data['projectname'].
			"','".$project_data['projectdescription'].
			"','".$rpt_datefrom."','".$rpt_dateto."','".$supplier_data['supplier_name']."'");

		// process data..

		// get pos..

		$po_list = $mypoModel->getlist_poitems_spsreport($rpt_projectpkey, $rpt_supplierpkey, $unix_datefrom, $unix_dateto);

		$pono=0;
		$myno=0;

		foreach ($po_list as $mypo) {

			if ($pono != $mypo['po_no']) {

				// current problem is we get multiple records of the same po with
				// different items

				// NOTE: If report is modified to cater for different suppliers, then
				// supplierpkey in the next sql query becomes important!

				// get do data

				$do_list = $mydoModel->getlist_do_spsreport($rpt_projectpkey, $mypo['po_pkey'], $mypo['po_supplierpkey']);
				if (count($do_list)>0) $myno++;

				if ($rpt_supplierpkey!="") {
					$mysuppname = $supplier_data['supplier_name'];
				} else {
					$mysupp = $mysupplierModel->getdata_supplier($mypo['po_supplierpkey']);
					$mysuppname = $mysupp['supplier_name'];
				}

				foreach ($do_list as $mydo) {

					$myperiod = ($mypo['mro_deliverydate'] - $mydo['item_dodate'])/86400;

					$sa = $this->add_data("SPS Report [Add]", "rpt_sps_details", "data_userpkey, data_no, data_suppliername, data_pono, data_podate, data_dono, data_dodate, data_expecteddate, data_deliveryperiod",
						"'".$rpt_userpkey."',".$myno.",'".$mysuppname.
						"','".poidstr($mypo['po_no']).
						"','".unconverttounixslash($mypo['po_date']).
						"','".$mydo['item_dono']."','".unconverttounixslash($mydo['item_dodate']).
						"','".unconverttounixslash($mypo['mro_deliverydate'])."',".$myperiod);

				}
				$pono = $mypo['po_no'];
			}
		}

		// insert data..

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

	}


	/* ********************************************************************************
	 Process Budget Monitoring report
	 ******************************************************************************** */

	function processreport_bm($rpt_projectpkey, $rpt_datefrom, $rpt_dateto) {

		// clean/convert/add params..
		$unix_datefrom=converttounix($rpt_datefrom);
		$unix_dateto=converttounix($rpt_dateto);
		$rpt_userpkey=$_SESSION['Xuserpkey'];

		// get project details..
		include_once('projectModel.php');
		$myprojectModel=new projectModelPMS;

		$project_data = $myprojectModel->getdata_project($rpt_projectpkey);

		// flush old report data..
		$da1 = $this->delete_data("BM Report [Delete]", "rpt_bm_header", "where hdr_userpkey = '".$rpt_userpkey."'");
		$da2 = $this->delete_data("BM Report [Delete]", "rpt_bm_details", "where data_userpkey = '".$rpt_userpkey."'");

		// insert new header..

		$sa = $this->add_data("BM Report [Add]", "rpt_bm_header", "hdr_userpkey, hdr_projectname, hdr_projectdescription, hdr_datefrom, hdr_dateto",
			"'".$rpt_userpkey."','".$project_data['projectname'].
			"','".$project_data['projectdescription'].
			"','".$rpt_datefrom."','".$rpt_dateto."'");

		// process data..

		include_once('mroModel.php');
		$mymroModel=new mroModel;

		include_once('itqModel.php');
		$myitqModel=new itqModel;

		// get rfq amounts based on mro rates..

		$rfq_list = $myitqModel->getlist_itq_bmreport_arie($rpt_projectpkey, $unix_datefrom, $unix_dateto);

		foreach ($rfq_list as $myrfq) {

			$rfqno = itqidstr($myrfq['itq_rfqno']);
			$subtrade = $myrfq['tradedescription'];
			$subsubtrade = "";
			$budgetamount = 0;
			$actualamount = 0;

			$mro_items = $mymroModel->getlist_mroitems($myrfq['mro_pkey']);

			foreach ($mro_items as $mymroitem) {

				$budgetamount += ($mymroitem['item_quantity']*$mymroitem['item_rate']);

				// get rfq amounts based on rfq (evaluation table)..

				$rfq_items = $myitqModel->getlist_itqitems_bmreport($myrfq['itq_pkey'], $mymroitem['item_pkey']);

				foreach ($rfq_items as $myrfqitem) {

					$actualamount += ($myrfqitem['approval_quantity']*$myrfqitem['eval_supplierrate']);
				}
			}

			$difference = $budgetamount - $actualamount;
			$percentage = ($difference / $budgetamount) * 100;

			$sa = $this->add_data("BM Report [Add]", "rpt_bm_details", "data_userpkey, data_rfqno, data_subtrade, data_subsubtrade, data_budgetamount, data_actualamount, data_difference, data_percentage",
				"'".$rpt_userpkey."','".$rfqno."','".$subtrade.
				"','".$subsubtrade.
				"',".$budgetamount.",".$actualamount.
				",".$difference.",".$percentage);
		}

		// insert data..

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

	}


	/* ********************************************************************************
	 Process Project Material Cost report
	 ******************************************************************************** */

	function processreport_pmc($rpt_projectpkey, $rpt_pono, $rpt_datefrom, $rpt_dateto) {


		// clean/convert/add params..
		$unix_datefrom=converttounix($rpt_datefrom);
		$unix_dateto=converttounix($rpt_dateto);
		$rpt_userpkey=$_SESSION['Xuserpkey'];

		// get project details..
		include_once('projectModel.php');
		$myprojectModel=new projectModelPMS;

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

		include_once('doModel.php');
		$mydoModel=new doModel;

		include_once('invoiceModel.php');
		$myinvoiceModel=new invoiceModel;


		$project_data = $myprojectModel->getdata_project($rpt_projectpkey);
		$po_data = $mypoModel->getdata_po($rpt_pono);


		// flush old report data..
		$da1 = $this->delete_data("PMC Report [Delete]", "rpt_pmc_header", "where hdr_userpkey = '".$rpt_userpkey."'");
		$da2 = $this->delete_data("PMC Report [Delete]", "rpt_pmc_details", "");

		// insert new header..

		if ($rpt_pono!=""){
			$po_id=poidstr($rpt_pono);
		}else{
			$po_id="All";
		}


		$sa = $this->add_data("PMC Report [Add]", "rpt_pmc_header", "hdr_userpkey, hdr_projectname, hdr_projectdescription, hdr_pono, hdr_datefrom, hdr_dateto",
			"'".$rpt_userpkey."','".$project_data['projectname'].
			"','".$project_data['projectdescription']."','".$po_id.
			"','".$rpt_datefrom."','".$rpt_dateto."'");

		// process data..

		// get pos..

if ($rpt_pono==''){
		$sql="select POItem.item_quantity as poqty,DT.item_dodate,DT.item_dono,IV.item_invoiceno,IV.item_description,
				IV.item_quantity,IV.item_rate,(IV.item_quantity*IV.item_rate) as amount,IV.item_debitnote,IV.item_invoicedate,IV.item_supplierpkey, PO.po_no,PO.po_date
					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 po_items as POItem 
						on POItem.item_popkey =PO.po_pkey
						and POItem.item_description = IV.item_description
					where 
						IV.item_projectpkey='$rpt_projectpkey' ";
		}
		else
		{

		$sql="select POItem.item_quantity as poqty,DT.item_dodate,DT.item_dono,IV.item_invoiceno,IV.item_description,
				IV.item_quantity,IV.item_rate,(IV.item_quantity*IV.item_rate) as amount,IV.item_debitnote,IV.item_invoicedate,IV.item_supplierpkey, PO.po_no,PO.po_date
					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 po_items as POItem 
						on POItem.item_popkey =PO.po_pkey
						and POItem.item_description = IV.item_description
					where 
						PO.po_no = '$rpt_pono'
						and IV.item_projectpkey='$rpt_projectpkey' ";
}

	 if (($unix_datefrom!="")&&($unix_dateto!="")) {
	 	$sql.="and PO.po_date >= '$unix_datefrom' and PO.po_date <= '$unix_dateto' ";
	 }
	 //$sql.="order by IV.item_description, PO.po_date";
$sql.="order by PO.po_no";



	 $item_list = $myinvoiceModel->getextendedrows($sql);
	 foreach ($item_list as $myitem) {

               // arie add 25052011 - get supplier name
		include_once('supplierModel.php');
		$mysupplierModel=new supplierModel;
		
		$supplier_data = $mysupplierModel->getdata_supplier($myitem['item_supplierpkey']);
		
		//end arie add 25052011 -get supplier name

	 	//$myamount = $mydo['item_quantity'] * $mypo['item_rate'];
	 	// insert data..
	 	$sa = $this->add_data("PMC Report [Add]", "rpt_pmc_details", "data_userpkey, data_pono, data_podate, data_itemname, data_date,  data_invoicedate,data_invoiceno, data_dono, data_total_qty_inv, data_quantity, data_rate, data_debitnote, data_supplier, data_amount",
						"'".$rpt_userpkey."','".poidstr($myitem['po_no']).
						"','".unconverttounixslash($myitem['po_date']).
						"','".ununescape($myitem['item_description']).
						"','".unconverttounixslash($myitem['item_dodate']).
                                                "','".unconverttounixslash($myitem['item_invoicedate']).
						"','".$myitem['item_invoiceno'].
						"','".$myitem['item_dono'].
						"',".$myitem['poqty'].",".$myitem['item_quantity'].",".$myitem['item_rate'].",".$myitem['item_debitnote'].",'".$supplier_data['supplier_name'].						"',".$myitem['amount']);
	 }


	 /*

		$po_list = $mypoModel->getlist_poitems_pmcreport($rpt_projectpkey, $rpt_pono, $unix_datefrom, $unix_dateto);



		foreach ($po_list as $mypo) {


		// get invoice data (1 invoice per po for now)
		$invoice_list = $myinvoiceModel->getlist_invoice_pmcreport($rpt_projectpkey, $mypo['po_pkey']);

		// get do data
		$do_list = $mydoModel->getlist_do_pmcreport($rpt_projectpkey, $mypo['po_pkey'], $mypo['item_subsubtradepkey']);

		foreach ($invoice_list as $myinvoice) {

		foreach ($do_list as $mydo) {

		$myamount = $mydo['item_quantity'] * $mypo['item_rate'];

		// insert data..

		$sa = $this->add_data("PMC Report [Add]", "rpt_pmc_details", "data_userpkey, data_pono, data_podate, data_itemname, data_date, data_invoiceno, data_dono, data_quantity, data_rate, data_supplier, data_amount",
		"'".$rpt_userpkey."','".poidstr($mypo['po_no']).
		"','".unconverttounixslash($mypo['po_date']).
		"','".ununescape($mypo['item_description']).
		"','".unconverttounixslash($mydo['item_dodate']).
		"','".$myinvoice['item_invoiceno'].
		"','".$mydo['item_dono'].
		"',".$mydo['item_quantity'].",".$mypo['item_rate'].",'".$supplier_data['supplier_name'].
		"',".$myamount);

		}
		reset($do_list);
		}
		}
		*/


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

	}


	/* ********************************************************************************
	 Process Subcon Material report
	 ******************************************************************************** */

	function processreport_scm($rpt_projectpkey, $rpt_datefrom, $rpt_dateto, $rpt_subconpkey) {

		// clean/convert/add params..
		$unix_datefrom=converttounix($rpt_datefrom);
		$unix_dateto=converttounix($rpt_dateto);
		$rpt_userpkey=$_SESSION['Xuserpkey'];

		// get project & supplier details..
		include_once('projectModel.php');
		$myprojectModel=new projectModelPMS;

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

		include_once('doModel.php');
		$mydoModel=new doModel;

		include_once('invoiceModel.php');
		$myinvoiceModel=new invoiceModel;

		include_once('subconModel.php');
		$mysubconModel=new subconModel;

		include_once('supplierModel.php');
		$mysupplierModel=new supplierModel;

		$project_data = $myprojectModel->getdata_project($rpt_projectpkey);
		$subcon_data = $mysubconModel->getdata_subcon($rpt_subconpkey);

		// flush old report data..
		$da1 = $this->delete_data("SCM Report [Delete]", "rpt_scm_header", "where hdr_userpkey = '".$rpt_userpkey."'");
		$da2 = $this->delete_data("SCM Report [Delete]", "rpt_scm_details", "");

		// insert new header..

		$sa = $this->add_data("SCM Report [Add]", "rpt_scm_header", "hdr_userpkey, hdr_projectname, hdr_projectdescription, hdr_datefrom, hdr_dateto, hdr_subconname",
			"'".$rpt_userpkey."','".$project_data['projectname'].
			"','".$project_data['projectdescription'].
			"','".$rpt_datefrom."','".$rpt_dateto."','".$subcon_data['subconname']."'");

		// process data..

		// get pos (not po items)..
		# myapit - getlist_po_scmreport to getlist_po_scmreport2
		$po_list = $mypoModel->getlist_po_scmreport2($rpt_projectpkey, $rpt_subconpkey, $unix_datefrom, $unix_dateto);
		$myno=0;

		foreach ($po_list as $mypo) {

			$myno++;

			// get supplier data

			$supplier_data = $mysupplierModel->getdata_supplier($mypo['po_supplierpkey']);

			// get po amount

			$poamount = $mypoModel->getamount_poitems($mypo['po_pkey']);

			// get invoice data (1 invoice per po for now)

			$invoice_list = $myinvoiceModel->getlist_invoice_scmreport($rpt_projectpkey, $mypo['po_pkey']);

			foreach ($invoice_list as $myinvoice) {

				$invamount = ($myinvoice['item_quantity']*$myinvoice['item_rate']);

				$sa = $this->add_data("SCM Report [Add]", "rpt_scm_details", "data_userpkey, data_no, data_suppliername, data_pono, data_podate, data_poamount, data_invoiceno, data_invoicedate, data_amount",
					"'".$rpt_userpkey."',".$myno.",'".$supplier_data['supplier_name'].
					"','".poidstr($mypo['po_no']).
					"','".unconverttounixslash($mypo['po_date']).
					"',".$poamount.
					",'".$myinvoice['item_invoiceno'].
					"','".unconverttounixslash($myinvoice['item_invoicedate']).
					"',".$invamount);

			}
		}
		//print_r($po_list);
		//die();
		// insert data..

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

	}


	/* ********************************************************************************
	 Process IQA report
	 ******************************************************************************** */

	function processreport_iqa() {

		//include_once('supplierModel.php');
		//$mysupplierModel=new supplierModel;

		return true;

	}


	/* ********************************************************************************
	 Process Supplier List report
	 ******************************************************************************** */

	function processreport_supplierlist($rpt_type, $rpt_name="", $rpt_approved="1", $rpt_status="1", $rpt_nob="") {

		// clean/convert/add params..
		$rpt_userpkey=$_SESSION['Xuserpkey'];

		include_once('supplierModel.php');
		$mysupplierModel=new supplierModel;

		// flush old report data..
		$da1 = $this->delete_data("Supplier List Report [Delete]", "rpt_sup_header", "where hdr_userpkey = '".$rpt_userpkey."'");
		$da2 = $this->delete_data("Supplier List Report [Delete]", "rpt_sup_details", "where data_userpkey = '".$rpt_userpkey."'");

		// insert new header..

		$sa = $this->add_data("Supplier List Report [Add]", "rpt_sup_header", "hdr_userpkey, hdr_name, hdr_approved, hdr_status, hdr_nob",
			"'".$rpt_userpkey."','".$rpt_name.
			"',".$rpt_approved.
			",".$rpt_status.",'".$rpt_nob."'");

		// process data..

		$supplier_list = $mysupplierModel->getlist_suppliers("supplier_regdate","ASC","","","","");
		$cindex=0;
		foreach ($supplier_list as $mysupp) {
			$approval_list[$cindex] = $mysupplierModel->getlist_approvals($mysupp['supplier_pkey']);
			$bg_list[$cindex] = $mysupplierModel->getlist_bankguarantees($mysupp['supplier_pkey']);
			$expfound=false;
			$bgtotal[$cindex]['bg_banktotal']=0;
			foreach ($bg_list[$cindex] as $mybg) {
				$bgtotal[$cindex]['bg_expired']=false;
				$bgtotal[$cindex]['bg_banktotal']=$bgtotal[$cindex]['bg_banktotal']+$mybg['bg_bankamount'];
				if (($mybg['bg_enddate']-5184000)<=getdatestamp()) {
					$expfound=true;
					$bgtotal[$cindex]['bg_expired']=true;
					if ($mybg['bg_enddate']<=getdatestamp()) {
						$bgtotal[$cindex]['bg_fullremarks']=$bgtotal[$cindex]['bg_fullremarks']."".unconverttounixslash($mybg['bg_startdate'])." - ".unconverttounixslash($mybg['bg_enddate'])." ".$mybg['bg_projectname']." ".($mybg['bg_bankamount']/1000)."K, ";
					} else {
						$bgtotal[$cindex]['bg_fullremarks']=$bgtotal[$cindex]['bg_fullremarks']."".unconverttounixslash($mybg['bg_startdate'])." - ".unconverttounixslash($mybg['bg_enddate'])." ".$mybg['bg_projectname']." ".($mybg['bg_bankamount']/1000)."K, ";
					}
				} else {
					$bgtotal[$cindex]['bg_expired']=false;
					$bgtotal[$cindex]['bg_fullremarks']=$bgtotal[$cindex]['bg_fullremarks'].unconverttounixslash($mybg['bg_startdate'])." - ".unconverttounixslash($mybg['bg_enddate'])." ".$mybg['bg_projectname']." ".($mybg['bg_bankamount']/1000)."K, ";
				}
			}
			$bgtotal[$cindex]['bg_fulltotal']=($bgtotal[$cindex]['bg_banktotal']*$mysupp['supplier_bgratio'])+$mysupp['supplier_personalamount'];
			if ($expfound==true_) {
				$bgtotal[$cindex]['bg_fullremarks']=substr($bgtotal[$cindex]['bg_fullremarks'],0,strlen($bgtotal[$cindex]['bg_fullremarks'])-2);
			} else {
				$bgtotal[$cindex]['bg_fullremarks']=substr($bgtotal[$cindex]['bg_fullremarks'],0,strlen($bgtotal[$cindex]['bg_fullremarks'])-2);
			}
			$cindex++;
		}

		// insert data..

		$cindex=0;

		foreach ($supplier_list as $mysupplier) {

			$aindex=0;
			unset($myapproval);

			foreach ($approval_list[$cindex] as $myapprovalitem) {
				$myapproval = $myapprovalitem;
				$aindex++;
			}

			$mysuppno = $mysupplier['recordno'];
			$mysuppregdate = unconverttounixslash($mysupplier['supplier_regdate']);
			if ($mysupplier['supplier_approved']==1) { $mysuppapproved = unconverttounixslash($myapproval['approval_date']); } else { $mysuppapproved = ""; }
			if ($mysupplier['supplier_regid']!=0) { $mysuppregid = idstr($mysupplier['supplier_regid']); } else { $mysuppregid = ""; }
			$mysuppfullname = $mysupplier['supplier_name']."\n".$mysupplier['supplier_address']."\n"."Tel: ".$mysupplier['supplier_phone']."\n"."Fax: ".$mysupplier['supplier_fax']."\n"."Email: ".$mysupplier['supplier_email'];
			$mysuppcontactperson = $mysupplier['supplier_pic'];
			$mysuppcontactno = $mysupplier['supplier_picphone'];
			$mysuppct = $mysupplier['supplier_ct'];
			$mysuppnob = $mysupplier['supplier_nob'];
			$mysuppproducts = $mysupplier['supplier_products'];
			if ($mysupplier['supplier_status']==1) { $mysuppstatus = "Active"; } else if($mysupplier['supplier_status']==2){ $mysuppstatus = "Blacklist"; }else{ $mysuppstatus = "Inactive"; }
			$mysuppbgratio = "1:".number_format($mysupplier['supplier_bgratio'],1);
			$mysuppbgamount = str_replace(",","",number_format($bgtotal[$cindex]['bg_banktotal'],2));
			$mysupppgamount = str_replace(",","",number_format($mysupplier['supplier_personalamount'],2));
			$mysupptotalamount = str_replace(",","",number_format($bgtotal[$cindex]['bg_fulltotal'],2));
			if ($bgtotal[$cindex]['bg_fullremarks']!="") { $mysuppbgremarks = $bgtotal[$cindex]['bg_fullremarks']; } else { $mysuppbgremarks = ""; }

			$sa = $this->add_data("Supplier List Report [Add]", "rpt_sup_details", "data_userpkey, data_no, data_regdate, data_approvedate, data_regid, data_fullname, data_contactperson, data_contactno, data_ct, data_nob, data_products, data_status, data_bgratio, data_bgamount, data_pgamount, data_totalamount, data_bgremarks",
				"'".$rpt_userpkey.
				"',".$mysuppno.
				",'".$mysuppregdate.
				"','".$mysuppapproved.
				"','".$mysuppregid.
				"','".$mysuppfullname.
				"','".$mysuppcontactperson.
				"','".$mysuppcontactno.
				"','".$mysuppct.
				"','".$mysuppnob.
				"','".$mysuppproducts.
				"','".$mysuppstatus.
				"','".$mysuppbgratio.
				"',".$mysuppbgamount.
				",".$mysupppgamount.
				",".$mysupptotalamount.
				",'".$mysuppbgremarks."'");

			$cindex++;

		}

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

	}


	/* REPORT RETRIEVAL FUNCTIONS */


	/* ********************************************************************************
	 Retrieve MBL report headers
	 ******************************************************************************** */

	function loadheader_mbl($userpkey) {

		$data=array();

		$sql="Select RPT.* from rpt_mbl_header RPT where RPT.hdr_userpkey='".$userpkey."'";
		$data=$this->getextendedrow($sql);
		return $data;

	}


	/* ********************************************************************************
	 Retrieve MBL report data
	 ******************************************************************************** */

	function loaddata_mbl($userpkey) {

		$listdata=array();

		// get report headers..ORDINARY PORTLAND CEMENT 50KG order by data_itemtype asc

		$sql="Select RPT.* from rpt_mbl_details RPT where RPT.data_userpkey='".$userpkey."'";	// order by data_itemgroup, data_itemname, data_pono";
		$listdata=$this->getextendedrows($sql);
		return $listdata;

	}


	/* ********************************************************************************
	 Retrieve QV report headers
	 ******************************************************************************** */

	function loadheader_qv($userpkey) {

		$data=array();

		$sql="Select RPT.* from rpt_qv_header RPT where RPT.hdr_userpkey='".$userpkey."'";
		$data=$this->getextendedrow($sql);
		return $data;

	}


	/* ********************************************************************************
	 Retrieve QV report data
	 ******************************************************************************** */

	function loaddata_qv($userpkey) {

		$listdata=array();

		// get report headers..
		//$sql="Select RPT.* from rpt_qv_details RPT where RPT.data_userpkey='".$userpkey."' order by data_itemtype, data_itemno";
                $sql="Select RPT.* from rpt_qv_details RPT where RPT.data_userpkey='".$userpkey."'";
		$listdata=$this->getextendedrows($sql);
		return $listdata;

	}


	/* ********************************************************************************
	 Retrieve PO report headers
	 ******************************************************************************** */

	function loadheader_po($userpkey) {

		$data=array();

		$sql="Select RPT.* from rpt_po_header RPT where RPT.hdr_userpkey='".$userpkey."'";
		$data=$this->getextendedrow($sql);
		return $data;

	}


	/* ********************************************************************************
	 Retrieve PO report data
	 ******************************************************************************** */

	function loaddata_po($userpkey) {

		$listdata=array();

		// get report headers..

		$sql="Select RPT.* from rpt_po_details RPT where RPT.data_userpkey='".$userpkey."' order by data_pono";
		$listdata=$this->getextendedrows($sql);
		return $listdata;

	}


	/* ********************************************************************************
	 Retrieve SP report headers
	 ******************************************************************************** */

	function loadheader_sp($userpkey) {

		$data=array();

		$sql="Select RPT.* from rpt_sp_header RPT where RPT.hdr_userpkey='".$userpkey."'";
		$data=$this->getextendedrow($sql);
		return $data;

	}


	/* ********************************************************************************
	 Retrieve SP report data
	 ******************************************************************************** */

	function loaddata_sp($userpkey) {

		$listdata=array();

		// get report headers..

		$sql="Select RPT.* from rpt_sp_details RPT where RPT.data_userpkey='".$userpkey."'";
		$listdata=$this->getextendedrows($sql);
		return $listdata;

	}


	/* ********************************************************************************
	 Retrieve SPS report headers
	 ******************************************************************************** */

	function loadheader_sps($userpkey) {

		$data=array();

		$sql="Select RPT.* from rpt_sps_header RPT where RPT.hdr_userpkey='".$userpkey."'";
		$data=$this->getextendedrow($sql);
		return $data;

	}


	/* ********************************************************************************
	 Retrieve SPS report data
	 ******************************************************************************** */

	function loaddata_sps($userpkey) {

		$listdata=array();

		// get report headers..

		$sql="Select RPT.* from rpt_sps_details RPT where RPT.data_userpkey='".$userpkey."'";
		$listdata=$this->getextendedrows($sql);
		return $listdata;

	}


	/* ********************************************************************************
	 Retrieve BM report headers
	 ******************************************************************************** */

	function loadheader_bm($userpkey) {

		$data=array();

		$sql="Select RPT.* from rpt_bm_header RPT where RPT.hdr_userpkey='".$userpkey."'";
		$data=$this->getextendedrow($sql);
		return $data;

	}


	/* ********************************************************************************
	 Retrieve BM report data
	 ******************************************************************************** */

	function loaddata_bm($userpkey) {

		$listdata=array();

		// get report headers..

		$sql="Select RPT.* from rpt_bm_details RPT where RPT.data_userpkey='".$userpkey."'";
		$listdata=$this->getextendedrows($sql);
		return $listdata;

	}


	/* ********************************************************************************
	 Retrieve PMC report headers
	 ******************************************************************************** */

	function loadheader_pmc($userpkey) {

		$data=array();

		$sql="Select RPT.* from rpt_pmc_header RPT where RPT.hdr_userpkey='".$userpkey."'";
		$data=$this->getextendedrow($sql);
		return $data;

	}


	/* ********************************************************************************
	 Retrieve PMC report data
	 ******************************************************************************** */

	function loaddata_pmc($userpkey) {

		$listdata=array();

		// get report headers..

		$sql="Select RPT.* from rpt_pmc_details RPT where RPT.data_userpkey='".$userpkey."' order by data_pono,data_invoiceno";
		$listdata=$this->getextendedrows($sql);
		return $listdata;

	}


	/* ********************************************************************************
	 Retrieve SCM report headers
	 ******************************************************************************** */

	function loadheader_scm($userpkey) {

		$data=array();

		$sql="Select RPT.* from rpt_scm_header RPT where RPT.hdr_userpkey='".$userpkey."'";
		$data=$this->getextendedrow($sql);
		return $data;

	}


	/* ********************************************************************************
	 Retrieve SCM report data
	 ******************************************************************************** */

	function loaddata_scm($userpkey) {

		$listdata=array();

		// get report headers..

		$sql="Select RPT.* from rpt_scm_details RPT where RPT.data_userpkey='".$userpkey."' order by data_suppliername, data_pono";
		$listdata=$this->getextendedrows($sql);
		return $listdata;

	}


	/* ********************************************************************************
	 Retrieve IQA report headers
	 ******************************************************************************** */

	function loadheader_iqa($userpkey) {

		$data=array();

		$sql="Select RPT.* from rpt_iqa_header RPT where RPT.hdr_userpkey='".$userpkey."'";
		$data=$this->getextendedrow($sql);
		return $data;

	}


	/* ********************************************************************************
	 Retrieve IQA report data
	 ******************************************************************************** */

	function loaddata_iqa($userpkey) {

		$listdata=array();

		// get report headers..

		$sql="Select RPT.* from rpt_iqa_details RPT where RPT.data_userpkey='".$userpkey."'";
		$listdata=$this->getextendedrows($sql);
		return $listdata;

	}


	/* ********************************************************************************
	 Retrieve Supplier List report headers
	 ******************************************************************************** */

	function loadheader_supplierlist($userpkey) {

		$data=array();

		$sql="Select RPT.* from rpt_sup_header RPT where RPT.hdr_userpkey='".$userpkey."'";
		$data=$this->getextendedrow($sql);
		return $data;

	}


	/* ********************************************************************************
	 Retrieve Supplier List report data
	 ******************************************************************************** */

	//function loaddata_supplierlist($userpkey) {
	function loaddata_supplierlist($userpkey, $sup, $appr, $act, $nob) {
		$listdata=array();

		// get report headers..

		//$sql="Select RPT.* from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' order by RPT.data_no ASC";
		//$sql="Select RPT.* from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' order by RPT.data_regid ASC";
		//$sql="Select RPT.* from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' AND SUBSTRING(RPT.data_regid,-2) order by RPT.data_regid ASC";
		//$sql ="Select RPT.* ,SUBSTRING(RPT.data_regid ,3) AS REGID from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' order by REGID";
		//$sql ="Select RPT.* ,CAST( SUBSTRING(RPT.data_regid ,3) AS SIGNED) AS REGID from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' order by REGID";
        /*
		&sup=&appr=&act=&nob=
		$sup 
        approvestatus = $appr, 1 = YES, 0 = NO
		activestatus = any='' $act, 1 = Active, 0 = Inactive, 2 = Blacklist
		suppliernob = $nob, MANUFACTURING , TRADING, OTHERS
		
		// approved any
		
		$apr
		$act
		$nob
		*/
		
		//yusr gast editing 25-07-2011
		if($sup == NULL AND $act == NULL AND $appr == NULL AND $nob == NULL){
		$sql ="Select RPT.* ,CAST( SUBSTRING(RPT.data_regid ,3) AS SIGNED) AS REGID from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' order by REGID";
		}else{
		
		//active status check
		if($act == "1"){//any
		$xact = "AND data_status ='Active'";
		}else if($act == "0"){
		$xact = "AND data_status ='Inactive'";
		}else if($act == "2"){
		$xact = "AND data_status ='Blacklist'";
		}else{
		$xact = "";
		}
		
		//approval check
		if($appr == "1"){//any
		$xappr = "AND data_approvedate!='' ";
		}else if($appr == "0"){
		$xappr = "AND data_approvedate ='' ";
		}
		//supplier chk
		if($sup != NULL){ $xsup = "AND data_fullname LIKE '%".$sup."%'";} else {$xsupp = "";} 
		// nob check
		if($nob == NULL) { $xnob == "";}else { $xnob = "AND data_nob='$nob'"; }
		
		$sql ="Select RPT.* ,CAST( SUBSTRING(RPT.data_regid ,3) AS SIGNED) AS REGID 
		from rpt_sup_details RPT 
		where RPT.data_userpkey='".$userpkey."' 
		$xsup
		$xact
		$xnob
		$xappr
		order by REGID";			
		
		}
		
		/*
		if ($act == "1") //Active
		{
			$sql ="Select RPT.* ,CAST( SUBSTRING(RPT.data_regid ,3) AS SIGNED) AS REGID from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' AND data_status ='Active' order by REGID";
		}
		else if ($act == "0") // Inactive
		{
		    if($appr == "0"){ //no
			$sql ="Select RPT.* ,CAST( SUBSTRING(RPT.data_regid ,3) AS SIGNED) AS REGID from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' AND data_status ='Inactive' AND data_approvedate='' order by REGID";
			}else{ //yes
			$sql ="Select RPT.* ,CAST( SUBSTRING(RPT.data_regid ,3) AS SIGNED) AS REGID from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' AND data_status ='Inactive' AND data_approvedate!='' order by REGID";
			}
		}
		else if ($act == "2") // Inactive
		{
		    if($appr == "0"){ //no
			$sql ="Select RPT.* ,CAST( SUBSTRING(RPT.data_regid ,3) AS SIGNED) AS REGID from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' AND data_status ='Blacklist' AND data_approvedate='' order by REGID";
			}else{ //yes
			$sql ="Select RPT.* ,CAST( SUBSTRING(RPT.data_regid ,3) AS SIGNED) AS REGID from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' AND data_status ='Blacklist' AND data_approvedate!='' order by REGID";
			}
		}
		
		// nature of business
		else if($nob == "MANUFACTURER")
		{
			$sql ="Select RPT.* ,CAST( SUBSTRING(RPT.data_regid ,3) AS SIGNED) AS REGID from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' AND data_nob ='MANUFACTURER' order by REGID";
		}
		else if($nob == "TRADING")
		{
			$sql ="Select RPT.* ,CAST( SUBSTRING(RPT.data_regid ,3) AS SIGNED) AS REGID from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' AND data_nob ='TRADING' order by REGID";
		}
		else if($nob == "OTHERS")
		{
			$sql ="Select RPT.* ,CAST( SUBSTRING(RPT.data_regid ,3) AS SIGNED) AS REGID from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' AND data_nob ='OTHERS' order by REGID";
		}
		else if ($appr == "1") // approved yes
		{
		  
			//$sql = "SELECT RPT.* FROM RPT.rpt_sup_details WHERE RPT.data_approvedate != NULL AND RPT.data_userpkey ='".$userpkey."'";
			$sql ="Select RPT.* ,CAST( SUBSTRING(RPT.data_regid ,3) AS SIGNED) AS REGID from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' AND data_approvedate != '' AND data_bgremarks ='' order by REGID";
			
		}
		else if ($appr == "0") // approved no
		{
			$sql ="Select RPT.* ,CAST( SUBSTRING(RPT.data_regid ,3) AS SIGNED) AS REGID from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' AND data_approvedate = '' order by REGID";
		}
		// apr=1 act = 0 approve = yes & inactive have been select
		//else if ($appr == 1 && $act == 0){
		//}
		//supplier filter
		else if ($sup != "")
		{
			$sql ="Select RPT.* ,CAST( SUBSTRING(RPT.data_regid ,3) AS SIGNED) AS REGID from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' AND data_fullname LIKE '%".$sup."%' order by REGID";
		} else { //default
			$sql ="Select RPT.* ,CAST( SUBSTRING(RPT.data_regid ,3) AS SIGNED) AS REGID from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' order by REGID";
		}
       */
		$listdata=$this->getextendedrows($sql);
		return $listdata;

	}

	function loaddata_supplierlist_1($userpkey, $sup) {

		$listdata=array();

		// get report headers..

		//$sql="Select RPT.* from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' order by RPT.data_no ASC";
		//$sql="Select RPT.* from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' order by RPT.data_regid ASC";
		//$sql="Select RPT.* from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' AND SUBSTRING(RPT.data_regid,-2) order by RPT.data_regid ASC";
		//$sql ="Select RPT.* ,SUBSTRING(RPT.data_regid ,3) AS REGID from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' order by REGID";
		//$sql ="Select RPT.* ,CAST( SUBSTRING(RPT.data_regid ,3) AS SIGNED) AS REGID from rpt_sup_details RPT where RPT.data_userpkey='".$userpkey."' AND RPT.data_fullname LIKE '".$supp."' order by REGID";
		$sql = "SELECT RPT.* FROM rpt_sup_details WHERE RPT.data_fullname LIKE '%".$sup."%' AND RPT.data_userpkey ='".$userpkey."'";
		$listdata=$this->getextendedrows($sql);
		return $listdata;

	}

}
?>