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