Database Access For Payment Gateway

[Black-Xstar]

Since blesta doesn't offer unique ID for each payment. I have to use another database to save the unique ID.

So, how to do it? I need create a table when payment gateway activated and run query inside the gateway functions.

 

Here is my scene:

1) buildProcess() will generate unique ID and save (id, invoices array) pair to database as well as send unique ID to payment gateway.

2) User pay at gateway's website and back to blesta. Gateway will send data to blesta return_url.

3) success() will receive GET data from payment gateway. The data included same unique ID above and transaction identifier generated by gateway.

4) To get the invoices array from database by using unique ID mark those invoices as paid.

Edited August 17, 2015 by Black-Xstar

[serge]

I do not understand 3)

 

You say "unique ID form payment gateway" but I have felling it's a transaction status like (success, pending, VOID, etc).

Maybe you mean "from" vs "form"

 

------------

 

I think Blesta orderID or invoice is just what you need to use as order identifier to send to payment gateway.

 

and transaction identifier is originated from the payment gateway not from blesta.

 

So when an order status back from gateway to blesta, gateway send back order ID + gateway generated transaction identifier

 

 

So when you submit a transaction creation order to gateway, you just need blesta order or invoice id.

 

and when transaction status is updated by gateway, blesta receive notification from gateway.

 

And blesta ever have storage of receive transaction id,

 

So I do not understand why you could need to create new database table

[Blesta Addons]

every transaction is stored with it own ID .

 

so is already in blesta system a unique transaction .

[Black-Xstar]

@serge: you are right, sorry for my typo.

 

 

Blesta only provides amount and invoices array to buildProcess():

* @param float $amount The amount to charge this contact
* @param array $invoice_amounts An array of invoices

I can't serialize the array and send to gateway because gateway only accept integers.

So I need a DB/table to save (id, invoices array) pair. When the blesta receive id from gateway, I can mark those invoices as paid.

 

Using order ID is a good idea. But if people renew their services or just add credit to account, how can I get this order ID?

 

PS: I know what is transaction identifier but I am not talking about the transaction identifier.

 

Thanks.

[serge]

Blesta gateway function should give you back a field/variable regarding the considered invoice or order ID, blesta use when sending to gateway the transaction request

 

it's seem to me blesta order id is just numeric and without letter or decimal, so by the way an integer of that do not change the value.

  when you check blesta database order id it's ever stored as a integer, so this id you get from blesta is just ready to use with your gateway.

 

  But not certain but I think it's maybe more invoice ID to pass to gateway and not order id, but same here, it's ever an integer.

 

 

But if you have very specific need, check if your gateway allow custom parameter(field), so when gateway send it back to you, you can use it's as an other identifier.

[Max]

Blesta gateway function should give you back a field/variable regarding the considered invoice or order ID, blesta use when sending to gateway the transaction request

 

it's seem to me blesta order id is just numeric and without letter or decimal, so by the way an integer of that do not change the value.

  when you check blesta database order id it's ever stored as a integer, so this id you get from blesta is just ready to use with your gateway.

 

  But not certain but I think it's maybe more invoice ID to pass to gateway and not order id, but same here, it's ever an integer.

 

 

But if you have very specific need, check if your gateway allow custom parameter(field), so when gateway send it back to you, you can use it's as an other identifier.

 

Order id? Didn't knew Blesta understood the concept of orders.

 

Only thing that gets passed to payment gateways is an array of invoices.

Serializing that, and passing that to a gateway is asking for trouble, because many gateways impose a character limit (especially European gateways, because they tend to include the transaction ID on customer's bank statement)

It may work fine during your testing while paying single invoices, but you risk that if a customer has a longer list of overdue invoices that he finally wants to pay, it will fail.

 

I proposed introducing a common database table for storing payment transaction information for this earlier, but the team didn't feel anything for that.

So yes, anyone wanting to properly implement a non-merchant payment gateway module will need to create their own...

 

 

Can use functions like this:

 

    protected $gwname = "My gateway";
     function install()    {        $r = new Record();        $r->setField("id", array('type' => "varchar", 'size' => 255))          ->setField("gateway", array('type' => "varchar", 'size' => 255))          ->setField("expire", array('type' => "datetime"))          ->setField("value", array('type' => "text", 'is_null' => true))          ->setKey(array("id","gateway"), "primary")          ->create("gateway_sessions", true);        }        protected function getSession($id)    {        $r = new Record();        $row = $r->select("value")->from("gateway_sessions")            ->where("id", "=", $id)            ->where("gateway", "=", $this->gwname)->fetch(PDO::FETCH_ASSOC);         if ($row)            return unserialize($row["value"]);        else            return false;    }        protected function putSession($id, $data)    {        $r = new Record();        $r->where("id", "=", $id)          ->where("gateway", "=", $this->gwname)          ->update("gateway_sessions", array("value" => serialize($data)));    }     protected function createSessionID()    {        $r = new Record();        $expires = date("Y-m-d H:i:s", time() + 86400 * 14);                for ($try = 0; $try < 10; $try++)        {            try            {                $key = $this->_generateSessionKey();                $r->insert("gateway_sessions", array('id' => $key, 'gateway' => $this->gwname, 'expire' => $expires));                return $key;            }            catch (PDOException $e) { }        }                throw new Exception("Error creating session");    }        protected function _generateSessionKey()    {        return dechex(time()).dechex(crypt_random()).dechex(crypt_random());    }

 

(Using an ID based on time, rather than an auto-increment, so that the number is unique, and doesn't start at 1 again if you reinstall Blesta, or if you use the same gateway with other software)

[Max]

every transaction is stored with it own ID .

 

so is already in blesta system a unique transaction .

 

Only afterwards.

Not when a transaction is being initiated at a non-merchant gateway.

[Black-Xstar]

 

Order id? Didn't knew Blesta understood the concept of orders.

 

Only thing that gets passed to payment gateways is an array of invoices.

Serializing that, and passing that to a gateway is asking for trouble, because many gateways impose a character limit (especially European gateways, because they tend to include the transaction ID on customer's bank statement)

It may work fine during your testing while paying single invoices, but you risk that if a customer has a longer list of overdue invoices that he finally wants to pay, it will fail.

 

I proposed introducing a common database table for storing payment transaction information for this earlier, but the team didn't feel anything for that.

So yes, anyone wanting to properly implement a non-merchant payment gateway module will need to create their own...

 

 

Can use functions like this:

 

    protected $gwname = "My gateway";
     function install()    {        $r = new Record();        $r->setField("id", array('type' => "varchar", 'size' => 255))          ->setField("gateway", array('type' => "varchar", 'size' => 255))          ->setField("expire", array('type' => "datetime"))          ->setField("value", array('type' => "text", 'is_null' => true))          ->setKey(array("id","gateway"), "primary")          ->create("gateway_sessions", true);        }        protected function getSession($id)    {        $r = new Record();        $row = $r->select("value")->from("gateway_sessions")            ->where("id", "=", $id)            ->where("gateway", "=", $this->gwname)->fetch(PDO::FETCH_ASSOC);         if ($row)            return unserialize($row["value"]);        else            return false;    }        protected function putSession($id, $data)    {        $r = new Record();        $r->where("id", "=", $id)          ->where("gateway", "=", $this->gwname)          ->update("gateway_sessions", array("value" => serialize($data)));    }     protected function createSessionID()    {        $r = new Record();        $expires = date("Y-m-d H:i:s", time() + 86400 * 14);                for ($try = 0; $try < 10; $try++)        {            try            {                $key = $this->_generateSessionKey();                $r->insert("gateway_sessions", array('id' => $key, 'gateway' => $this->gwname, 'expire' => $expires));                return $key;            }            catch (PDOException $e) { }        }                throw new Exception("Error creating session");    }        protected function _generateSessionKey()    {        return dechex(time()).dechex(crypt_random()).dechex(crypt_random());    }

 

(Using an ID based on time, rather than an auto-increment, so that the number is unique, and doesn't start at 1 again if you reinstall Blesta, or if you use the same gateway with other software)

 

 

Thank you! That is exactly I am asking.

I will try you code snippet. Thanks again!

[Cody]

 

Order id? Didn't knew Blesta understood the concept of orders.

 

Only thing that gets passed to payment gateways is an array of invoices.

Serializing that, and passing that to a gateway is asking for trouble, because many gateways impose a character limit (especially European gateways, because they tend to include the transaction ID on customer's bank statement)

It may work fine during your testing while paying single invoices, but you risk that if a customer has a longer list of overdue invoices that he finally wants to pay, it will fail.

 

I proposed introducing a common database table for storing payment transaction information for this earlier, but the team didn't feel anything for that.

So yes, anyone wanting to properly implement a non-merchant payment gateway module will need to create their own...

 

 

Can use functions like this:

 

 

 

Your solution suffers from the same serialization problem.

 

A better solution would be something like:

 

gateway_sessions

uuid, gateway_id, expire_date

 

gateway_session_invoices

gateway_session_uuid, invoice_id, amount

 

Then you simply pass the uuid to the gateway and read it back on the response.

[Max]

Your solution suffers from the same serialization problem.

 

 

TS may indeed need to remove the dechex() if his gateway does not support hexadecimal digits in the transaction id either.

 

 

>A better solution would be something like:

>

>gateway_sessions

>uuid, gateway_id, expire_date

>

>gateway_session_invoices

>gateway_session_uuid, invoice_id, amount

>

>Then you simply pass the uuid to the gateway and read it back on the response.

 

My code is esentially using shortened 96-bit UUIDs by default. (32 bit time + 64 bit random)

Using a flexible varchar(255) as id in the database though, so that descendant classes can override the protected _generateSessionKey() method and use a different scheme, if there is a need to.

 

 

>gateway_session_invoices

>gateway_session_uuid, invoice_id, amount

 

 

Prefer supporting storing arbritrary data using serialize() (or json_encode()) to a TEXT column.

While Blesta only needs the array of invoice_ids, some gateway modules may have a need to store extra information as well.

 

Many modern non-merchant gateways require that you initiate the transaction with an API call, before redirecting the user to the gateway.

Sometimes that call returns extra information you need later on to verify the transaction.

[activa]

i have a similar case , just my case is for token .

 

the gateway class generate a token based in client data and current time (so is impossible to be a duplicated or we can re-generate it ) , this toked sended via post to the gateway . when the client complete the payment , the gateway post a data to the blesta return_url , the data returned as json and is encrypted with the token already sent .

 

we can't decrypt the data until we have the token used first time .

 

in the return back i have resolved this by storing the token in the sessions .

 

but in callback notify url is not possible to use sessions , and we will get a empty token from session .

 

what type of fields i should  store in database to use it later ?

 

token , client_id ?

[serge]

can you give the full way the token is generated please (I mean method the gateway documentation is giving)?

 

So token generation do not use a merchant password or merchant gateway secret key?

[Blesta Addons]

is like the paypal checkout express ? you send a data to the gateway and then it return a hached code to add it to a url ?

[Max]

the gateway class generate a token based in client data and current time (so is impossible to be a duplicated or we can re-generate it ) , this toked sended via post to the gateway . when the client complete the payment , the gateway post a data to the blesta return_url , the data returned as json and is encrypted with the token already sent .

 

 

Does the gateway sends anything else upon return, besides the encrypted data?

E.g., an unencrypted transaction id you can set?

 

If not you will need to append a sessionid to the callback URL:

$callbackurl = Configure::get("Blesta.gw_callback_url").Configure::get("Blesta.company_id")."/".strtolower(get_class($this))."/".$sessionid;

And access the sessionid later with $get[2]

Which works for gateway notifications, but not for the client redirect (because Blesta redirects the user to a different page without preserving the added /$sessionid) for which you have to use a normal session as workaround.

 

 

Store the sessionid, token, invoice_amounts and client_id in the database.

Either as separate fields (which would require a separate table for invoice_amounts like in Cody's database scheme), or by putting all data in an array and serializing that to a TEXT field with serialize(), like in my example code.

[activa]

can you give the full way the token is generated please (I mean method the gateway documentation is giving)?

 

So token generation do not use a merchant password or merchant gateway secret key?

 

 

i send the client and order data to the marchent , it return me a token_id , marchent_url  and crypted data of order . then i should send the client to pay with a url that use this toekn as a complete link , like ;

 

https://gateway.com/pay/marchent_url .

 

after the payment , it return me a transaction data without the token_id  , this transaction shoulb decrypted by the token_id sended first time .

 

 

 

is like the paypal checkout express ? you send a data to the gateway and then it return a hached code to add it to a url ?

 

the same way , just paypal return the transaction data in plain .

 

 

Does the gateway sends anything else upon return, besides the encrypted data?

E.g., an unencrypted transaction id you can set?

 

If not you will need to append a sessionid to the callback URL:

$callbackurl = Configure::get("Blesta.gw_callback_url").Configure::get("Blesta.company_id")."/".strtolower(get_class($this))."/".$sessionid;

And access the sessionid later with $get[2]

Which works for gateway notifications, but not for the client redirect (because Blesta redirects the user to a different page without preserving the added /$sessionid) for which you have to use a normal session as workaround.

 

 

Store the sessionid, token, invoice_amounts and client_id in the database.

Either as separate fields (which would require a separate table for invoice_amounts like in Cody's database scheme), or by putting all data in an array and serializing that to a TEXT field with serialize(), like in my example code.

 

 

we can't do it , because we should send the callback_url before we get the token . so i think the only way is storing the token in

 

1 - sessions

2 - cokies

3 - database

[serge]

do the gateway allow to send custom parameters not being encrypted by the token, and could send back these custom parameters when there is a transaction change status notification?

[Black-Xstar]

Your solution suffers from the same serialization problem.

 

A better solution would be something like:

 

gateway_sessions

uuid, gateway_id, expire_date

 

gateway_session_invoices

gateway_session_uuid, invoice_id, amount

 

Then you simply pass the uuid to the gateway and read it back on the response.

Thanks.

How do I access database? Can I use install() method to init the DB?

[Max]

we can't do it , because we should send the callback_url before we get the token . so i think the only way is storing the token in

 

1 - sessions

2 - cokies

3 - database

 

You misunderstood me.

I suggested storing the token in the database, but set an id in the callback URL so you know which database record to fetch..

 

 

With my example methods mentioned above.

function buildProcess(array $contact_info, $amount, array $invoice_amounts=null, array $options=null)
{
  $sessionid = $this->createSessionID();
  $callbackurl = Configure::get("Blesta.gw_callback_url").Configure::get("Blesta.company_id")."/".strtolower(get_class($this))."/".$sessionid;
  
  //
  // ...add code to initiate transaction with gateway using their API here...
  //
  
  $data_you_want_to_store = array('client_id' => $contact_info['client_id'],  'invoice_amounts' => $invoice_amounts, 'token' => $token_received_from_gateway);
  $this->putSession($sessionid, $data_you_want_to_store);
 
  // redirect user to gateway
  header("Location: $url_received_from_gateway");
  exit(0);
}
 
function validate(array $get, array $post)
{
   // fetch information from database using sessionid from URL
   $data = $this->getSession($get[2]);
 
   if (!$data)
      return;
 
   //
   // ...decrypt response from gateway with $data['token']...
   //
 
}

[activa]

 

You store the token in the database, but set an id in the callback URL so you know which database record to fetch..

 

 

With my example methods mentioned above.

function buildProcess(array $contact_info, $amount, array $invoice_amounts=null, array $options=null)
{
  $sessionid = $this->createSessionID();
  $callbackurl = Configure::get("Blesta.gw_callback_url").Configure::get("Blesta.company_id")."/".strtolower(get_class($this))."/".$sessionid;
  
  //
  // ...add code to initiate transaction with gateway using their API here...
  //
  
  $data_you_want_to_store = array('client_id' => $contact_info['client_id'],  'invoice_amounts' => $invoice_amounts, 'token' => $token_received_from_gateway);
  $this->putSession($sessionid, $data_you_want_to_store);
 
  // redirect user to gateway
  header("Location: $url_received_from_gateway");
  exit(0);
}
 
function validate(array $get, array $post)
{
   // fetch information from database using sessionid from URL
   $data = $this->getSession($get[2]);
 
   if (!$data)
      return;
 
   //
   // ...decrypt response from gateway with $data['token']...
   //
 
}

 

Thanks , max , your idea is brillant , unless this stupid gateway is sending all the response crypted , even if the custom data .

 

look my code

        //redirection URL
        $redirect_url = Configure::get("Blesta.gw_callback_url") . Configure::get("Blesta.company_id") . "/payzone/".$this->ifSet($contact_info['client_id']);
        $merchant_id =  $this->ifSet($this->merchant_id);

        // Filling the request parameters
        $fields = array(
            // Client Info
            'apiVersion' => $this->apiVersion,
            'shopperID' => $this->ifSet($client->id),
            'shopperEmail' => $this->ifSet($client->email),
            'shopperFirstName' => $this->ifSet($client->first_name),
            'shopperLastName' => $this->ifSet($client->last_name),
            'shopperPhone' => $this->getContact($client),
            'shopperAddress' => $this->ifSet($client->address1) . ' ' . $this->ifSet($client->address2),
            'shopperState' => $this->ifSet($client->state),
            'shopperCity' => $this->ifSet($client->city),
            'shopperZipcode' => $this->ifSet($client->zip),
            'shopperCountryCode' => $this->ifSet($client->country),
            // Global Orders
            'orderID' => $order_id,
            'currency' =>  $this->currency,
            'amount' => ($amount * 100)  ,
            'shippingType' =>  $this->shipping_type ,
            'paymentType' =>  $this->payment_type ,
            'paymentMode' =>  $this->payment_mode ,                 
            'customerIP' =>  $_SERVER['REMOTE_ADDR'] ,    
            'orderDescription' =>  $this->ifSet($options['description']),                
            'ctrlRedirectURL' =>   $redirect_url,
            'ctrlCallbackURL' =>  $redirect_url,
            'merchantNotification' =>  true ,
            'merchantNotificationTo' =>  $this->ifSet($this->merchant_notification_to) ,    
            'merchantNotificationLang' =>  "fr" ,
            'ctrlCustomData' =>  $this->serializeInvoices($invoice_amounts)
        );
        
        
        $response = $this->execute_request("/transaction/prepare", json_encode($fields));
        
        if (!isset($this->Session))
            Loader::loadComponents($this, array("Session"));

        $this->Session->write("merchantToken", $response['merchantToken']);        
        
        // print_r($response);
        Loader::loadHelpers($this, array("Form", "Html"));
        $this->view = $this->makeView("process", "default", str_replace(ROOTWEBDIR, "", dirname(__FILE__) . DS));
        $this->view->set("post_to", $this->payzone_url . "/transaction/". $response['customerToken']."/dopay" );

after the payment the gateway return a reponse all are creypted , and i should decrypt it with

    // Decrypting
    $json = mcrypt_decrypt(MCRYPT_RIJNDAEL_128, $merchantToken, $Data, MCRYPT_MODE_ECB);    


        if ($json) {
            // Remove PKCS#5 padding
            $json = $this->pkcs5_unpad($json);
            $response = json_decode($json, true);
        }
        else {
            $this->Input->setErrors($this->getCommonError("invalid"));
            // Log error response
            $this->log($this->ifSet($_SERVER['REQUEST_URI']), serialize($response), "output", false);
            return;
        }

then i should work with $response .

 

my probleme and obstacle that the gateway is not returing any thing in plain , that i will use it to identify the transaction in my database . and the big obstacle are in the notifification , because ican't use iether the sessions or the database to identify the transaction .

 

the gateway is payxpert .

[serge]

ok, in my opinion, so no other way than store the token in the blesta database, and it's will serve you to identify the transaction when the gateway notification come to Blesta.

 

And store token in the session could not be fine to receive notification of VOID or CHARGEBACK etc, that can come long time after and not from a web redirection

[activa]

ok, in my opinion, so no other way than store the token in the blesta database, and it's will serve you to identify the transaction when the gateway notification come to Blesta.

 

And store token in the session could not be fine to receive notification of VOID or CHARGEBACK etc, that can come long time after and not from a web redirection

 

the probleme is how to identify the transaction and the token , 

 

let say this example .

 

client A order service A and has paid .

client A order service B and has paid .

 

client B order service A and has paid .

 

we have here 3 token , when we store this in database , how to identify the token that match the gateway response ?

 

the only solution is to make a loop for all the token until we get a valid response .

[Max]

$redirect_url = Configure::get("Blesta.gw_callback_url") . Configure::get("Blesta.company_id") . "/payzone/".$this->ifSet($contact_info['client_id']);

 

Would append an unique ID for the transaction (like in my example), not client_id.

 

my probleme and obstacle that the gateway is not returing any thing in plain , that i will use it to identify the transaction in my database . and the big obstacle are in the notifification , because ican't use iether the sessions or the database to identify the transaction .

 

 

As long as it is using the callback URL you set when initiating the transaction (including the unique id you appended) for notifications I do not see the problem.

Or is it using a global notification URL that cannot be set on a per transaction basis?

[serge]

yes it was my idea also : In the native blesta transaction table you create a new colunm "Token"

 

and token serve you as unique id for the transaction, untill it's that way your gateway work

[Max]

yes it was my idea also : In the native blesta transaction table you create a new colunm "Token"

 

 

I would be relucant to add columns to Blesta's own tables, as that could potentially give problems with updates, and other third party modules.

Safer to create a table of your own for that (like the gateway_sessions table in my example)

[activa]

I would be relucant to add columns to Blesta's own tables, as that could potentially give problems with updates, and other third party modules.

Safer to create a table of your own for that (like the gateway_sessions table in my example)

 

 

i will try it .