<?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