<?php
include('includes/session.inc');
$Title = _('Upload ATM Loads transactions From CSV');
include('includes/header.inc');
include('includes/SQL_CommonFunctions.inc');
echo '<p class="page_title_text"><img alt="" src="' . $RootPath . '/css/' . $Theme .
		'/images/maintenance.png" title="' .
		_('Upload ATM transactions from CSV file') . '" />' . ' ' .
		_('Upload ATM transactions from CSV file') . '</p>';


$currentdate = date('Y-m-d h:i:s', time());

$user = $_SESSION['UserID'];

$FieldHeadings = array( 'type',
					   'debtorno',//  0 'STOCKID',
						'trandate',	
						'amount',	
					   	'invtext',
					   	'account_number',
					    'department');

if (isset($_FILES['CostUpdateFile']) and $_FILES['CostUpdateFile']['name']) { //start file processing
	//check file info
	$FileName = $_FILES['CostUpdateFile']['name'];
	$TempName  = $_FILES['CostUpdateFile']['tmp_name'];
	$FileSize = $_FILES['CostUpdateFile']['size'];
	$InputError = 0;

	//get file handle
	$FileHandle = fopen($TempName, 'r');

	//get the header row
	$HeadRow = fgetcsv($FileHandle, 10000, ',');

	
	//start database transaction
	DB_Txn_Begin();

	//loop through file rows
	$LineNumber = 1;
	while ( ($myrow = fgetcsv($FileHandle, 10000, ',')) !== FALSE ) {

		$id = $myrow[0];
	  $type =  $myrow[1];
		$branchcode = $myrow[2];
		$visitdate = $myrow[3];
         $packdate= $myrow[4];
		$custnum= $myrow[5];
		$reportname = $myrow[6];
		$qty100 = $myrow[9];
		$qty50 = $myrow[10];
		$qty20 = $myrow[11];
		$qty10 = $myrow[12];
		
		$vdate = date('Y-m-d', $visitdate);
		$pdate = date('Y-m-d', $packdate);
		
		
		
			
			

			// insert in atmtrans table
			$SQL = "INSERT INTO atmtrans (
			                        billing_id,
									type,
									branch_code,
									visit_date,
									pack_date,
									cust_number,
									report_name,
									qty_note100,
									qty_note50,
									qty_note20,
									qty_note10)
				VALUES (
					'" . $id . "',
					'" . $type . "',
					'" . $branchcode . "',
					'" . $visitdate . "',
					'" . $packdate . "',
					'" . $custnum. "',
					'" . mysqli_real_escape_string($reportname) . "',
					
					'" . ($qty100/100) . "',
					'" . $qty50/50 . "',
					'" . $qty20/20 . "',
					'" . $qty10/10 . "'
					)";
		$ErrMsg = _('Cannot insert a ATM transaction entry  because');
		$DbgMsg = _('The SQL that failed to insert the ATM Trans record was');
		$result = DB_query($SQL,$ErrMsg,$DbgMsg,true);
			
      
			

				

		$LineNumber++;
	}

	//upload to cit_bill_items
	
	//open atmtrans table
	
	$sql1 = " Select SUM(qty_note100) as note100total,
					SUM(qty_note50) as note50total,
					SUM(qty_note20) as note20total,
					SUM(qty_note10) as note10total
					
					FROM atmtrans where billing_id = '".$id."'
					
								
	";
	
	$result = DB_query($sql1);
	$myrow = DB_fetch_row($result);
	
	$note100 = $myrow[0];
	$note50 = $myrow[1];
	$note20 = $myrow[2];
	$note10 = $myrow[3];
	
	$totalnotes = $note10 + $note20 + $note50 + $note100;
	
	$total = $totalnotes * 0.0063;
	
	//get number of atm serviced
	$sql100 = " Select count(cust_number) as totalatm
					
					FROM atmtrans where type = 'Load' AND billing_id = '".$id."'
					
								
	";
	
	$result1 = DB_query($sql100);
	$myrow1 = DB_fetch_row($result1);
	
	
	$totalatm = $myrow1[0];
	
	$totalrepl = $totalatm * 140;
	
	// supply and return
	
			$SQL2 = "INSERT INTO  cit_bill_items (
			                        billing_id,
									category,
									description,
									qty,
									uom,
									unit_price,
								total,
								entered_by
							)
				VALUES (
					'" . $id . "',
					'CIT',
					'CIT2',
					'" . $totalnotes . "',
					'note',
					'0.0063',
					'" . $total . "',
					'System Upload'
					)";
		$ErrMsg = _('Cannot insert a ATM transaction entry  because');
		$DbgMsg = _('The SQL that failed to insert the ATM Trans record was');
		$result2 = DB_query($SQL2,$ErrMsg,$DbgMsg,true);
	
	//replishment
		$SQL3 = "INSERT INTO  cit_bill_items (
			                        billing_id,
									category,
									description,
									qty,
									uom,
									unit_price,
								total
							)
				VALUES (
					'" . $id . "',
					'CIT',
					'CIT1',
					'" . $totalatm . "',
					'ATM',
					'140.00',
					'" . $totalrepl . "'
					)";
		$ErrMsg = _('Cannot insert a ATM transaction entry  because');
		$DbgMsg = _('The SQL that failed to insert the ATM Trans record was');
		$result3 = DB_query($SQL3,$ErrMsg,$DbgMsg,true);
	
	
	//vault management
	$SQL4 = "INSERT INTO  cit_bill_items (
			                        billing_id,
									category,
									description,
									qty,
									uom,
									unit_price,
								total
							)
				VALUES (
					'" . $id . "',
					'CIT3',
					'CIT12',
					'1',
					'month',
					'700.00',
					'700.00'
					)";
		$ErrMsg = _('Cannot insert a ATM transaction entry  because');
		$DbgMsg = _('The SQL that failed to insert the ATM Trans record was');
		$result4 = DB_query($SQL4,$ErrMsg,$DbgMsg,true);
	
	
	DB_Txn_Commit();
	prnMsg( _('Batch ATM load  transactions ') .' ' . $FileName  . ' '. _('has been completed. All Load atm transactions created.'),'success');

	fclose($FileHandle);

} else { //show file upload form

	echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '" method="post" enctype="multipart/form-data">';
	echo '<div class="centre">';
	echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
	echo '<div class="page_help_text">' .
			_('This function uploads the ATM LOADS transactions  from a comma separated variable (csv) file.  ') . '<br />' .
			
			_('Enjoy!.') . '</div>';

	echo '<br /><input type="hidden" name="MAX_FILE_SIZE" value="5000000" />' ._('Upload file') . ': <input name="CostUpdateFile" type="file" />
			<input type="submit" name="submit" value="' . _('Send File') . '" />
		</div>
		</form>';
}

include('includes/footer.inc');

?>