<?
/*================================================================
If Your Information Is Dynamic, Use This Header To Clear any Cache
================================================================*/
header("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1
include_once('hdr_general.php');


//if (!isset($_REQUEST["page"])) $_REQUEST["page"] = "general";
//if (!isset($_REQUEST["task"])) $_REQUEST["task"] = "main";


	//Navigation Process
	if(isset($_REQUEST["task"])){
		$task=$_REQUEST["task"];
		switch($task){
			case "save_invoice":
				$param1=$_REQUEST["param1"]; //supplier_key
				$param2=$_REQUEST["param2"]; //po_pkey
				$param3=&$_REQUEST["param3"].""; // remove_do
				$param4=&$_REQUEST["param4"].""; // new_do

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

				//Remove DO In Invoice First	
				if(strlen($param3)!=0){
						$param_remove=explode(",",str_replace("-x01-"," ",$param3));
						$sql="";
						foreach ($param_remove as $myremove){
							$remove_dtl= explode("_",$myremove);
							$do_no= $remove_dtl[1];
							$invoice_no=$remove_dtl[2];
							if(strlen($do_no)!=0){
								echo "</br>Removing => DO : $do_no , Invoice : $invoice_no";
							
								$sql="select IV.item_pkey,IV.item_subsubtradepkey 
											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
											where DT.item_dono='$do_no' and IV.item_invoiceno='$invoice_no'";
								
								$remove_list = $myinvoiceModel->getextendedrows($sql);
								$sql="";
								$bStatus=":: Succeed!";
								foreach ($remove_list as $my_ivkey){
									$sql="delete from invoice_items where		item_pkey='".$my_ivkey['item_pkey']."' and item_subsubtradepkey='".$my_ivkey['item_subsubtradepkey']."'";
									
									if ($myinvoiceModel->execute_nonquery($sql)==FALSE){
										$bStatus= ":: Failed!";
									}
								}
								echo $bStatus;
							}
						}
				} 


				//Adding New Do In Invoice
				if(strlen($param4)!=0){
				
						$param_add=explode(",",str_replace("-x01-"," ",$param4));
						$sql="";
						foreach ($param_add as $myadd){
							$add_dtl= explode("_",$myadd);
							$do_no= $add_dtl[1];
							$invoice_no=$add_dtl[2];
							$invoice_date=@hexstr($add_dtl[3]);
							$invoice_addcharge=@hexstr($add_dtl[4]);
							
							if(strlen($do_no)!=0){
								echo "</br>Adding New => DO : $do_no , Invoice : $invoice_no";


								//Check For Existing Invoice If Available
								$sql=sprintf("Select item_invoicedate,item_debitnote from invoice_items	where item_projectpkey='%s' and item_popkey='%s' and item_invoiceno='%s' LIMIT 1",$pubprojectpkey,$param2,$invoice_no);
								$exist_data = $myinvoiceModel->getextendedrows($sql);
								if(count($exist_data)==1){
									$invoice_date=$exist_data[0]['item_invoicedate'];
									$invoice_addcharge=$exist_data[0]['item_debitnote'];
								}else{
									if(strlen($invoice_date)==0){
										$invoice_date=getdatestamp();
									}else{
										$invoice_date=converttounix($invoice_date);
									}
									if(strlen($invoice_addcharge)==0){
										$invoice_addcharge=0;
									}
								}

								$sql="select DT.item_pkey as item_dopkey,DT.item_popkey,DT.item_dono,
									 DT.item_subsubtradepkey,DT.item_description,DT.item_unit,
									 DT.item_quantity,PO.item_rate from do_items as DT 
									 inner join po_items as PO 
										on DT.item_popkey=PO.item_popkey
										and DT.item_subsubtradepkey =PO.item_subsubtradepkey
										where DT.item_dono='$do_no'";
								
								$new_list = $myinvoiceModel->getextendedrows($sql);
								$sql="";
								$bStatus=":: Succeed!";
								foreach ($new_list as $do_item){
									
									//UNIX_TIMESTAMP()
									//$timestamp = strtotime($mysqltime);
									//echo date("Y-m-d H:i:s", $timestamp);
								
									$sql=sprintf("insert into			invoice_items(item_pkey,item_popkey,item_dopkey,item_projectpkey,
									item_supplierpkey,item_invoiceno,item_invoicedate,item_no,
									item_subsubtradepkey,item_description,item_unit,item_quantity,
									item_rate,item_debitnote)
									values('%s','%s','%s','%s','%s','%s',$invoice_date,'%s','%s','%s','%s','%s','%s','%s')",
										md5(microtime()),
										$do_item['item_popkey'],
										$do_item['item_dopkey'],
										$pubprojectpkey,
										$param1,
										mysql_real_escape_string($invoice_no),
										0,
										$do_item['item_subsubtradepkey'],
										mysql_real_escape_string($do_item['item_description']),
										mysql_real_escape_string($do_item['item_unit']),
										$do_item['item_quantity'],
										$do_item['item_rate'],
										$invoice_addcharge); 
	
									//echo "<br>$sql";

									
									if ($myinvoiceModel->execute_nonquery($sql)==FALSE){
										$bStatus= ":: Failed!";
									}
								
									
								}
								echo $bStatus;
							}
						}
				} 


			case "po_list":
				include_once('models/poModel.php');
				$mypoModel=new poModel;
				$param1=$_REQUEST["param1"];	

				 //$do_list = $mydoModel->getlist_do($pubprojectpkey);
				 echo "<option value='' selected>--Select PO--</option>";
				 $po_list = $mypoModel->getlist_pobyproject_y($pubprojectpkey, "1", "3", "","",$param1);
				 foreach ($po_list as $mypo){
					echo('<option value="'.$mypo['po_pkey'].'">'.poidstr($mypo['po_no']).'</option>');
				 }
				break;
			
			case "do_list":
				include_once('models/doModel.php');
				$mydoModel=new doModel;
				$param1=$_REQUEST["param1"]; //supplier_key
				$param2=$_REQUEST["param2"]; //po_pkey
				$param3=&$_REQUEST["param3"]; // filter only without Issued Invoice ? (True/False)

				echo("<option value='' selected>--Select DO--</option>");
				if($param3==True){
				
					$sql="select item_pkey,item_dono from do_items where
						item_projectpkey='$pubprojectpkey' and item_supplierpkey='$param1'
						and item_popkey='$param2' and item_dono not in(
						 select do_items.item_dono from invoice_items inner join do_items
						 on invoice_items.item_popkey=do_items.item_popkey
						 and invoice_items.item_projectpkey =do_items.item_projectpkey 
						 and invoice_items.item_supplierpkey=do_items.item_supplierpkey
						 and invoice_items.item_dopkey =do_items.item_pkey
						 where invoice_items.item_projectpkey='$pubprojectpkey'
						 and invoice_items.item_supplierpkey='$param1' and invoice_items.item_popkey='$param2' 
						 group by item_dono)
						 group by item_dono";

				}else{
					$sql="select item_pkey,item_dono from do_items 
						where item_projectpkey='$pubprojectpkey'
						and item_supplierpkey='$param1'
						and item_popkey='$param2' group by item_dono";
				}
				
				$do_list = $mydoModel->getextendedrows($sql);
				 foreach ($do_list as $mydo){
					echo('<option value="'.$mydo['item_pkey'].'">'.$mydo['item_dono'].'</option>');
				 }
				break;

			case "po_listinvoice":
				include_once('models/poModel.php');
				$mypoModel=new poModel;
				$param1=$_REQUEST["param1"]; //supplier_key
				$param2=$_REQUEST["param2"]; //po_pkey

				echo("<option value='' selected>--Select INVOICE--</option>");

				$sql="select distinct invoice_items.item_invoiceno
					  from invoice_items inner join do_items
						on invoice_items.item_popkey=do_items.item_popkey
						and invoice_items.item_projectpkey =do_items.item_projectpkey 
						and invoice_items.item_supplierpkey=do_items.item_supplierpkey
						and invoice_items.item_subsubtradepkey=do_items.item_subsubtradepkey
						and invoice_items.item_dopkey =do_items.item_pkey
					  where invoice_items.item_supplierpkey='$param1' 
						and invoice_items.item_popkey='$param2'";

				$po_list = $mypoModel->getextendedrows($sql);
				 foreach ($po_list as $mypo){
					echo('<option value="'.$mypo['item_invoiceno'].'">'.$mypo['item_invoiceno'].'</option>');
				 }
				break;
			
			case "do_listtable":
				
				include_once('models/doModel.php');
				$mydoModel=new doModel;	
				$param1= &$_REQUEST["param1"]; //DO no.
				$param2= &$_REQUEST["param2"]; //supplier_key
				$param3= &$_REQUEST["param3"]; //po_pkey
				
				$sql="select do_items.item_dono,do_items.item_subsubtradepkey,do_items.item_description,
					do_items.item_quantity,po_items.item_rate,(do_items.item_quantity*po_items.item_rate) as amount from do_items inner join po_items
					on do_items.item_popkey=po_items.item_popkey
					and do_items.item_subsubtradepkey =po_items.item_subsubtradepkey
					where do_items.item_dono='$param1' AND do_items.item_projectpkey='$pubprojectpkey'";
				// aku dah edit kat atas ni 10-4-2011
				$do_list = $mydoModel->getextendedrows($sql);
				if (count($do_list)==0){
					break;
				}

				echo("[DO : $param1]</br><table id='tbl_doview' name=id='tbl_doview' width='945' border='1'>
							  <tr>
								<td width='324' class=listheader>Item</td>
								<td width='98' class=listheader>Qty</td>
								<td width='129' class=listheader>Rate (RM)</td>
								<td width='149' class=listheader>Amount (RM)</td>
							  </tr>");
				
				 $amount=0;	
				 foreach ($do_list as $mydo){
						echo ("<tr>
								<td>".$mydo['item_description']."</td>
								<td>".$mydo['item_quantity']."</td>
								<td>".number_format($mydo['item_rate'],2)."</td>
								<td>".number_format($mydo['amount'],2)."</td>
							  </tr>");
						$amount= $amount +$mydo['amount'];
				 }
				 
				 echo ("<tr>
								<td></td>
								<td></td>
								<td class=listheader>TOTAL</td>
								<td id='td_dototal' name='td_dototal'>".number_format($amount,2)."</td>
						</tr>");
				 
				 echo("</table><hr/>");
				break;

			case "invoice_listtable":

				$cmsDB=$dsn_cms['database'];
				$pmsDB=$dsn_pms['database'];

				$param1=$_REQUEST["param1"]; //supplier_key
				$param2=$_REQUEST["param2"]; //po_no
				$param3=$_REQUEST["param3"]; //invoice_no
			
				include_once('models/invoiceModel.php');
				$myinvoiceModel=new invoiceModel;
				$invoice_list = $myinvoiceModel->getlist_invoicetable($pubprojectpkey,$param1,$param2,$param3);
				
				echo("
						<table width='947' border='1'>
						<tr>
						  <td width='176' class=listheader>INVOICE NO</td>
						  <td width='465' class=listheader>VENDOR NAME</td>
						  <td width='133' class=listheader>PO</td>
						  <td width='145' class=listheader>AMOUNT</td>
						</tr>");
				
				foreach ($invoice_list as $myinvoice){
					echo("
						<tr>
						  <td><a href='#invoice_detail' onclick=\"Ajax_Request(invoice_detail,'page=invoice&task=invoice_detailtable&param1=".$myinvoice['item_invoiceno']."')\">".$myinvoice['item_invoiceno']."</a></td>
						  <td>".$myinvoice['supplier_name']."</td>
						  <td>".poidstr($myinvoice['po_no'])."</td>
						  <td>".number_format($myinvoice['amount'],2)."</td>
						</tr>");
				}
				echo("</table>");
				break;
			
			case "invoice_listdetail":

				$param1=$_REQUEST["param1"]; //supplier_key
				$param2=$_REQUEST["param2"]; //po_no
				
				include_once('models/invoiceModel.php');
				$myinvoiceModel=new invoiceModel;
				
				$sql="select  IV.item_debitnote,IV.item_invoiceno,DT.item_dono 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
					  where IV.item_projectpkey='$pubprojectpkey'
						and IV.item_supplierpkey='$param1'
						and IV.item_popkey='$param2'
						group by item_invoiceno,item_dono
						order by item_dono ASC";


				$sql_detail="select 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
					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
					where IV.item_projectpkey='$pubprojectpkey'
						and IV.item_supplierpkey='$param1'
						and IV.item_popkey='$param2'
						and DT.item_dono=";
 					 

				$do_list = $myinvoiceModel->getextendedrows($sql);
				//$debit_note=$do_list[0]['item_debitnote'];
				echo("
					<table width='945' border='1' id='tbl_issuedinvoice' name=id='tbl_issuedinvoice'>
						<tr>
						  <td width='55' class=listheader>.</td>
						  <td width='143' class=listheader>DO No</td>
						  <td width='723'><table width='728' border='1'>
							  <tr>
								<td width='324' class=listheader>Item</td>
								<td width='98' class=listheader>Qty</td>
								<td width='129' class=listheader>Rate (RM)</td>
								<td width='149' class=listheader>Amount (RM)</td>
							  </tr>
						  </table></td>
						</tr>
					");		
				
				$amount=0;
				$do_currentlist="";
				foreach ($do_list as $mydo){
					$unique_id=str_replace(" ","-x01-",$mydo['item_dono']."_".$mydo['item_invoiceno']);
					$do_currentlist .="tr_$unique_id,";
					echo("<tr id='tr_$unique_id' name='tr_$unique_id'>
						  <td align='left' valign='top'><input type='button' name='btndelete_do' id='btndelete_do' value='Delete' onclick='remove_element_do(\"tr_$unique_id\")'/></td>
						  <td align='left' valign='top'>".$mydo['item_dono']."(".$mydo['item_invoiceno'].     ")</td>
						  <td><table width='727' border='1'>");
					//DO Details Here
					
					
					$sql=$sql_detail."'".$mydo['item_dono']."' and
						IV.item_invoiceno='".$mydo['item_invoiceno']."' order by item_dono ASC";
					$do_details= $myinvoiceModel->getextendedrows($sql);
					foreach ($do_details as $mydetail){
						$amount=$amount+$mydetail['amount'];
						echo ("<tr>
								<td>".$mydetail['item_description']."</td>
								<td width='98'>".$mydetail['item_quantity']."</td>
								<td width='129'>".number_format($mydetail['item_rate'],2)."</td>
								<td width='149'>".number_format($mydetail['amount'],2)."</td>
							  </tr>");
					}
					echo(" </table></td>
						</tr>");
				
				}
					echo("</table><table width='945' border='1'>
							<tr>
							  <td colspan='2' rowspan='2'>&nbsp;</td>
							  <td width='129'>&nbsp;</td>
							  <td width='154' name='td_invoicetotal' id='td_invoicetotal'>".number_format($amount,2)."</td>
							</tr>
							<tr>
							  <td class=listheader>TOTAL</td>
							  <td name='td_invoicetotal2' id='td_invoicetotal2'>".number_format($amount,2)."</td>
							</tr>
						  </table>");

				echo("<input type='hidden' name='txtdo_removelist' id='txtdo_removelist' value='' />
					  <input type='hidden' name='txtdo_newlist' id='txtdo_newlist' value='' />
					  <input type='hidden' name='txtdo_currentlist' id='txtdo_currentlist' value='$do_currentlist' />
					 ");
				
				break;


			case "invoice_detailtable":
				$param1=$_REQUEST["param1"]; //Invoice No
				
				include_once('models/invoiceModel.php');
				$myinvoiceModel=new invoiceModel;
				
				$sql="select  IV.item_invoiceno,IV.item_debitnote,DT.item_dono from invoice_items  as IV
						inner join do_items as DT 
						on DT.item_projectpkey=IV.item_projectpkey
						and DT.item_supplierpkey =IV.item_supplierpkey
						and DT.item_popkey=IV.item_popkey
						and DT.item_pkey=IV.item_dopkey
						where IV.item_invoiceno='$param1'
						group by IV.item_invoiceno,DT.item_dono";
			
				$sql_detail="
						select 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
						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
						where DT.item_dono=";


				$do_list = $myinvoiceModel->getextendedrows($sql);
				$debit_note=$do_list[0]['item_debitnote'];
				echo("<p>Invoice :".$param1."</p>
					<table width='945' border='1'>
						<tr>
						  <td width='55' class=listheader>&nbsp;</td>
						  <td width='143' class=listheader>DO No</td>
						  <td width='723'><table width='728' border='1'>
							  <tr>
								<td width='324' class=listheader>Item</td>
								<td width='98' class=listheader>Qty</td>
								<td width='129' class=listheader>Rate (RM)</td>
								<td width='149' class=listheader>Amount (RM)</td>
							  </tr>
						  </table></td>
						</tr>
					");		
				
				$amount=0;
				foreach ($do_list as $mydo){
					echo("<tr>
						  <td align='left' valign='top'>&nbsp;</td>
						  <td align='left' valign='top'>".$mydo['item_dono']."</td>
						  <td><table width='727' border='1'>");
					//DO Details Here
					$sql=$sql_detail."'".$mydo['item_dono']."' and IV.item_invoiceno='".$param1."'";
					$do_details= $myinvoiceModel->getextendedrows($sql);
					foreach ($do_details as $mydetail){
						$amount=$amount+$mydetail['amount'];
						echo ("<tr>
								<td>".$mydetail['item_description']."</td>
								<td width='98'>".$mydetail['item_quantity']."</td>
								<td width='129'>".number_format($mydetail['item_rate'],2)."</td>
								<td width='149'>".number_format($mydetail['amount'],2)."</td>
							  </tr>");
					}
					echo(" </table></td>
						</tr>");
				}
				
				$total_amount=$amount+$debit_note;
				// tambahan 14-4
				//yus
				//$ysql = "select SUM(item_debitnote) as sum_item_debitnote from invoice_items where item_invoiceno ='$param1'";
				$ysql = "select item_debitnote as sum_item_debitnote from invoice_items where item_invoiceno ='$param1'";
				$query = mysql_query($ysql);
				$mdebit = mysql_fetch_array($query); 
				$mdebit = $mdebit['sum_item_debitnote'];
				$mtot = $total_amount;
				$mxtotal = $mdebit + $mtot;
				 //
				echo("</table>
					<table width='945' border='1'>
						<tr>
						  <td colspan='2' rowspan='3'>&nbsp;</td>
						  <td width='233'>&nbsp;</td>
						  <td width='155'>".number_format($amount,2)."</td>
						</tr>
						<tr>
						  <td class=listheader>Additional Charges</td>
						  <td>".number_format($mdebit,2)."</td>
						</tr>
						<tr>
						  <td class=listheader>Total</td>
						  <td>".number_format($total_amount,2)."</td>
						</tr>
					</table>");
					/*
					echo("</table>
					<table width='945' border='1'>
						<tr>
						  <td colspan='2' rowspan='3'>&nbsp;</td>
						  <td width='233'>&nbsp;</td>
						  <td width='155'>".number_format($amount,2)."</td>
						</tr>
						<tr>
						  <td class=listheader>Additional Charges</td>
						  <td>".number_format($debit_note,2)."</td>
						</tr>
						<tr>
						  <td class=listheader>Total</td>
						  <td>".number_format($total_amount,2)."</td>
						</tr>
					</table>");
					*/
			break;
					
			default:
			  break;
		}
		exit;	
	}


	Function hexstr($hexstr) {
		$hexstr = str_replace(' ', '', $hexstr);
		$hexstr = str_replace('\x', '', $hexstr);
		$retstr = pack('H*', $hexstr);
		return $retstr;
	}

	Function strhex($string) {
		$hexstr = unpack('H*', $string);
		return array_shift($hexstr);
	}


?>
 
