<?php
public function getVoucher($accountid, $offerid, $userid){ // this function is used for creating voucher for selected offers
	
		$sql = "SELECT * FROM offers where offerId = '".$offerid."'";
		$stmt = $this->db->query($sql);
		$offer_results = $stmt->fetchall();
		//check if offer valid
		$offer_status = $offer_results['0']['status']; 
		$points_needed = $offer_results['0']['pointsNeeded'];//from offers table
				
		if($offer_status != 1){//TODO:check this comparison 
			$aResultData = array('data'=>array('error'=>'Offer Not valid'), 'headerMsg'=>'HTTP/1.1 400 Offer Not valid');
			return $aResultData;			
		}
		//block credits table for user
		
			$credit_block_check_stmt = $this->db->query("SELECT max(status) FROM credits where accountId = '".$accountid."'");
			$credit_block_check_results = $credit_block_check_stmt->fetchall();
			$block_status = $credit_block_check_results['0']['max(status)'];//from offers table 
		
		
		if($block_status == 2){ 
			$aResultData = array('data'=>array(), 'headerMsg'=>'HTTP/1.1 400 Another transaction in progress for user');
			return $aResultData;
		}
		
		
			$credit_block_stmt = $this->db->query("Update credits set status = 2 where accountId = '".$accountid."'");
			$sSqlCheckStmt = "SELECT sum(availablePoints) as availPoints FROM credits where accountId ='$accountid' and expiryDate >= sysdate()";
			error_log(__FUNCTION__.' : line '.__LINE__.' : ' .$sSqlCheckStmt);
			$credit_check_stmt = $this->db->query($sSqlCheckStmt);
			$credit_check_results = $credit_check_stmt->fetchall();
			$pointsAvailable = $credit_check_results['0']['availPoints']; 
		

		error_log("Points needed ".$points_needed ."  Points avail : ".$pointsAvailable);
		
		#$pointsAvailable = 155554;
		if($points_needed > $pointsAvailable){ 
			//unblock credits before exit
			$credit_unblock_stmt = $this->db->query("Update credits set status = 1 where accountId = '".$accountid."'"); // will be used either when user does not have sufficient points or when points have been succesfully deducted
			//check if user has points for offer  
			$aResultData = array('data'=>array('error'=>'Insufficient points accrued'), 'headerMsg'=>'HTTP/1.1 400 Insufficient points accrued');
			return $aResultData;
		}
 
		//create voucher -- start 
		$voucher_store = $offer_results['0']['storeId'];
		$voucher_name = $offer_results['0']['name'];
		$voucher_desc = $offer_results['0']['description'];
		$validity = $offer_results['0']['voucherSpan'];
		$redeemTime = $offer_results['0']['redeemInterval'];
		$partnerId = $offer_results['0']['partnerId'];
		$pointsProgramId = $offer_results['0']['pointsProgramId'];
		$pointsNeeded = $offer_results['0']['pointsNeeded'];
		
		
			$sSql1 = "Insert into vouchers (voucherId, offerId, accountId, validStoreid, name, description,  dateCreated, expiryDate, validity, redeemInterval, status) VALUES (uuid(), '$offerid', '$accountid','$voucher_store','$voucher_name','$voucher_desc', current_date(), date_add(current_date(), INTERVAL $validity DAY), $validity, $redeemTime, 1)";			
			$create_voucher_stmt = $this->db->query($sSql1);
			error_log(__FUNCTION__.' : line '.__LINE__.' : ' .$sSql1); 
		
		
			$voucherid_get_stmt = $this->db->query("SELECT max(voucherId) as id FROM vouchers where accountId = '".$accountid."'");
			$voucherid_get_results = $voucherid_get_stmt->fetchall();
			$voucherid = $voucherid_get_results['0']['id'];  
				
		//create voucher -- end
		
		// creating transaction --- start
		
			$sSqlT1 = "Insert into transactions (txnId, txnDateTime, partnerId, pointsProgramId, points, extTxnId) VALUES (uuid(), now(), '$partnerId', '$pointsProgramId',-$pointsNeeded, '$voucherid')";
			$create_voucher_txn_stmt = $this->db->query($sSqlT1);
			error_log(__FUNCTION__.' : line '.__LINE__.' : ' .$sSqlT1); 
			
			try{ // If transactions successfully added...
				$sSqlT2 = "select max(txnId) as txnId from transactions where extTxnId='$voucherid'";
				$select_Txn_stmt = $this->db->query($sSqlT2);
				$txnid_get_results = $select_Txn_stmt->fetchall();
				$txnId = $txnid_get_results['0']['txnId'];
				error_log(__FUNCTION__.' : line '.__LINE__.' : ' .$sSqlT2); 
			}
			catch(Exception $e){
				error_log('Exception in '.__FUNCTION__.' : line '.__LINE__.' : '.$e->getMessage()); 
			} 		
		
		
		 
		
			$sSqlT3 = "Insert into transfers (transferId, txnId, accountId, points) VALUES (uuid(), '$txnId', '$accountid', -$pointsNeeded)";
			$create_transfer_stmt = $this->db->query($sSqlT3);
			error_log(__FUNCTION__.' : line '.__LINE__.' : ' .$sSqlT3); 
			
			try{ // If transfers successfully added...
				$sSqlT4 = "select max(transferId) as transferId from transfers where accountId='$accountid'";
				$select_Transfer_stmt = $this->db->query($sSqlT4);
				$transferid_get_results = $select_Transfer_stmt->fetchall();
				$transferId = $transferid_get_results['0']['transferId'];
				error_log(__FUNCTION__.' : line '.__LINE__.' : ' .$sSqlT4); 
			}
			catch(Exception $e){
				error_log('Exception in '.__FUNCTION__.' : line '.__LINE__.' : '.$e->getMessage()); 
			}		
		
		// creating transaction ---  end
		  
		//use points - creating creadits
		try{
			$sSqlCredits = "SELECT creditId , pointsProgramId, availablePoints, txnStoreId FROM credits where accountId = '".$accountid."' and expiryDate >= current_date() and availablePoints > 0 order by expiryDate asc";
			error_log(__FUNCTION__.' : line '.__LINE__.' : ' .$sSqlCredits);	
			$valid_credit_list_stmt = $this->db->query($sSqlCredits);
			$valid_credit_list_results = $valid_credit_list_stmt->fetchall();
		}
		catch(Exception $e){
			error_log('Exception in '.__FUNCTION__.' : line '.__LINE__.' : '.$e->getMessage()); 
		}
		
		$count = 0;
		while($points_needed > 0){
			$avPts = $valid_credit_list_results[$count]['availablePoints'];
			$creditId = $valid_credit_list_results[$count]['creditId'];
			$programID =  $valid_credit_list_results[$count]['pointsProgramId'];
			$txnStoreId =  $valid_credit_list_results[$count]['txnStoreId'];
						
			if($points_needed >= $avPts){
				$debitAmt = 0 - $avPts;
				$pointsAvailable = $pointsAvailable - $avPts;
				$points_needed = $points_needed - $avPts;
				$updatedAvailablePts = 0; 
			}
			else{
				$debitAmt = 0 - $points_needed;
				$pointsAvailable = $pointsAvailable - $points_needed;
				$updatedAvailablePts = $avPts - $points_needed;
				$points_needed = 0;  
			} 
			try{
				$sSql2 = "Update credits set availablePoints = '".$updatedAvailablePts."' where creditId = '".$creditId."'";
				$credit_update_stmt = $this->db->query($sSql2);
				error_log(__FUNCTION__.' : line '.__LINE__.' : ' .$sSql2);
			}
			catch(Exception $e){
				error_log('Exception in '.__FUNCTION__.' : line '.__LINE__.' : '.$e->getMessage());  
			}
			
			try{
				$sSql3 = "Insert into credits (creditId, transferId, accountId, points, currBalance, creditDate, pointsProgramId, txnStoreId, linkedCreditId, status) VALUES (uuid(), '$transferId', '$accountid', '$debitAmt', '$pointsAvailable', current_date(), '$programID', '$txnStoreId', '$creditId', '1')";
				$debit_insert_stmt = $this->db->query($sSql3);
				error_log(__FUNCTION__.' : line '.__LINE__.' : ' .$sSql3);
			}
			catch(Exception $e){
				error_log('Exception in '.__FUNCTION__.' : line '.__LINE__.' : '.$e->getMessage());  
			}		
			$count = $count+1; 
		} 
		
		try{
			//unblock credits before exit
			$credit_unblock_stmt = $this->db->query("Update credits set status = 1 where accountId = '".$accountid."'"); 
		}
		catch(Exception $e){
			error_log('Exception in '.__FUNCTION__.' : line '.__LINE__.' : '.$e->getMessage()); 
		}	
			
		$aResultData = array('data'=>array('status' => '1'), 'headerMsg'=>'HTTP/1.1 200 buy Voucher Response');
		return $aResultData; 
	}//end of getVoucher function