Code Samples

Thursday, July 20, 2017

Grails | Groovy | Hibernate | Query Builder | HibernateCriteriaBuilder | Criteria Builder | Criteria Builder To SQL | Grails Create Criteria To SQL Conversion | Grails Convert Query Builder To MySQL | Criteria Builder SQL Restriction | SQLRestriction | Grails SQL Restriction

Grails | Groovy | Hibernate | Query Builder | HibernateCriteriaBuilder | Criteria Builder | Criteria Builder To SQL | Grails Create Criteria To SQL Conversion | Grails Convert Query Builder To MySQL | Criteria Builder SQL Restriction | SQLRestriction | Grails SQL Restriction.

Below a query | criteria builder. We will convert below hibernate criteria builder to SQL our-self. So with this feature we can check each and every query generated by hibernate query builder and can take necessary step if we need any. With this feature we also can retrieve query binding parameters. At first we will see our grails domain structure I used in my example codes. Below example also to show how we can use SQL restriction in criteria builder. In SQL Restriction section we can write plain SQL.

class Subscription {
    Long id
    Account account
}

class Account {
    Long id
    User createdBy
}

class User {
    Long id
    String name
}

Now below is our criteria builder (as well as we called it closure will pass to criteria builder to execute it):

def test() {
    Closure closure = {
        HibernateCriteriaBuilder builder = delegate

        setMaxResults(2)
        String a1 = CriteriaToSql.createAlias(builder, "account")
        String a2 = CriteriaToSql.createAlias(builder, "createdBy", "${a1}.createdBy")

        println(builder.properties.sql_aliases)


        gt("id", -1L)
        ne("code", "NOT_EQ_CODE")
        gt("${a1}.id", -2L)
        gt("${a2}.id", -3L)

        sqlRestriction("exists (select a.id from account AS a where a.id=this_.id)")

        projections {
            property("code")
        }

        order("updated", "desc")
        order("id", "desc")
    }
    CriteriaToSql.check(Subscription.class, closure)
    render("")
}

And finally our desired class which will product SQL from given criteria builder:

import grails.orm.HibernateCriteriaBuilder
import org.apache.commons.lang.StringEscapeUtils
import org.hibernate.Criteria
import org.hibernate.engine.spi.LoadQueryInfluencers
import org.hibernate.engine.spi.SessionFactoryImplementor
import org.hibernate.internal.CriteriaImpl
import org.hibernate.internal.SessionImpl
import org.hibernate.loader.OuterJoinLoader
import org.hibernate.loader.OuterJoinableAssociation
import org.hibernate.loader.PropertyPath
import org.hibernate.loader.criteria.CriteriaJoinWalker
import org.hibernate.loader.criteria.CriteriaLoader
import org.hibernate.loader.criteria.CriteriaQueryTranslator
import org.hibernate.persister.entity.OuterJoinLoadable
import org.hibernate.sql.JoinType

import java.lang.reflect.Field
/**
 * Created by pritom on 17/07/2017.
 */
class CriteriaToSql {
    static int counter = 0

    static void check(Class domainClass, Closure closure) {
        Criteria criteria = domainClass.createCriteria().buildCriteria {
            and closure
        }

        CriteriaImpl c = (CriteriaImpl) criteria
        SessionImpl s = (SessionImpl) c.getSession()

        SessionFactoryImplementor factory = (SessionFactoryImplementor) s.getSessionFactory()
        String[] implementors = factory.getImplementors(c.getEntityOrClassName())
        LoadQueryInfluencers loadQueryInfluencers = new LoadQueryInfluencers()
        CriteriaLoader loader = new CriteriaLoader((OuterJoinLoadable) factory.getEntityPersister(implementors[0]), factory, c, implementors[0], loadQueryInfluencers)
        CriteriaQueryTranslator translator = new CriteriaQueryTranslator(factory, c, domainClass.canonicalName, "this_")
        CriteriaJoinWalker walker = new CriteriaJoinWalker(
                (OuterJoinLoadable) factory.getEntityPersister(implementors[0]),
                translator, factory, criteria, domainClass.canonicalName, loadQueryInfluencers
        )

        Field f = OuterJoinLoader.class.getDeclaredField("sql")
        f.setAccessible(true)
        String sql = (String) f.get(loader), printable = ""
        int index = 0, max = 250
        while (true) {
            if (sql.length() > (index * max) + max) {
                printable = printable + "\r\n" + sql.substring(index * max, max + (index * max))
                index++
            }
            else {
                printable = printable + "\r\n" + sql.substring(index * max)
                break
            }
        }
        println("SQL_QUERY=${printable}")

        f = CriteriaLoader.class.getDeclaredField("querySpaces")
        f.setAccessible(true)
        println("SQL_ALIASES=${walker.getAssociations()*.rhsAlias}")

        println("PARAMETERS=${translator.getQueryParameters().positionalParameterValues}")
    }

    static Map getSql(Class domainClass, Closure closure) {
        Criteria criteria = domainClass.createCriteria().buildCriteria {
            and closure
        }

        CriteriaImpl c = (CriteriaImpl) criteria
        SessionImpl s = (SessionImpl) c.getSession()

        SessionFactoryImplementor factory = (SessionFactoryImplementor) s.getSessionFactory()
        String[] implementors = factory.getImplementors(c.getEntityOrClassName())
        LoadQueryInfluencers loadQueryInfluencers = new LoadQueryInfluencers()
        CriteriaLoader loader = new CriteriaLoader((OuterJoinLoadable) factory.getEntityPersister(implementors[0]), factory, c, implementors[0], loadQueryInfluencers)
        CriteriaQueryTranslator translator = new CriteriaQueryTranslator(factory, c, domainClass.canonicalName, "this_")
        CriteriaJoinWalker walker = new CriteriaJoinWalker(
                (OuterJoinLoadable) factory.getEntityPersister(implementors[0]),
                translator, factory, criteria, domainClass.canonicalName, loadQueryInfluencers
        )

        Field f = OuterJoinLoader.class.getDeclaredField("sql")
        f.setAccessible(true)
        String sql = (String) f.get(loader)

        Map parts = [
                select: sql.substring(7, sql.indexOf("from")),
                from: sql.substring(sql.indexOf("from") + 5, sql.indexOf("where")),
                where: sql.substring(sql.indexOf("where") + 6),
                joins: [:]
        ]
        walker.getAssociations().each { OuterJoinableAssociation assoc ->
            String key = assoc.propertyPath.property
            PropertyPath propertyPath = assoc.propertyPath
            while (true) {
                if (propertyPath.parent?.property) {
                    key = propertyPath.parent.property + "." + key
                    propertyPath = propertyPath.parent
                }
                else {
                    break
                }
            }
            parts.joins[key] = [domain: assoc.propertyPath.property, alias: assoc.rhsAlias]
        }

        int lastIndex = -1
        translator.getQueryParameters().positionalParameterValues.each { param1 ->
            int index = parts.where.indexOf("?", lastIndex)
            if (index >= 0) {
                param1 = param1.rawValue()
                param1 = param1 instanceof String ?  "'${StringEscapeUtils.escapeSql(param1)}'" : param1
                parts.where = parts.where.substring(0, index) + param1 + parts.where.substring(index + 1)
                lastIndex = index + param1.toString().length()
            }
        }
        return parts
    }

    static String createAlias(HibernateCriteriaBuilder criteriaBuilder, String field, String aliasPath = field, JoinType joinType = JoinType.INNER_JOIN) {
        if (!criteriaBuilder.properties.lixt) {
            criteriaBuilder.metaClass.lixt = [:]
        }
        Criteria subCriteria = criteriaBuilder.criteria.subcriteriaList.find { it.path == aliasPath }

        if(subCriteria) {
            return subCriteria.alias
        }
        else {
            String alias = "${field}xxxxxxxx${++counter}"
            criteriaBuilder.createAlias(aliasPath, alias, joinType)
            /* The below variable is SQL alias path */
            String sqlAlias = alias.substring(0, 10) + (criteriaBuilder.properties.lixt.size() + 1) + "_"
            criteriaBuilder.properties.lixt[alias] = sqlAlias
            return alias
        }
    }
}

And finally we would have below output:

[PV4736-12429, MS2431-12503]

[PV4736-12429, MS2431-12503]

SQL_QUERY=
select this_.code as y0_ from subscription this_
inner join account accountxxx1_ on this_.account_id=accountxxx1_.id 
inner join user createdbyx2_ on accountxxx1_.created_by_id=createdbyx2_.id 
where (this_.id>? and this_.code<>? and accountxxx1_.id>? and createdbyx2_.id>?
and exists (select a.id from account AS a where a.id=this_.id))
order by this_.updated desc, this_.id desc

SQL_ALIASES=[accountxxx1_, createdbyx2_]

PARAMETERS=[-1, NOT_EQ_CODE, -2, -3]

How to change collation of database, table, column | Alter charset and collation in all columns in all tables in MySQL | MySQL Collation - Setting Character Sets and Collations in MySQL

How to change collation of database, table, column | Alter charset and collation in all columns in all tables in MySQL | MySQL Collation - Setting Character Sets and Collations in MySQL.

Now the database is latin1_general_ci and I want to change collation to utf8_general_ci. Is there any setting in PhpMyAdmin to change collation of database, table, column? Rather than changing one by one?

(this will convert the columns just as well), or export the database with latin1 and import it back with utf8.

Changing it database wise:

ALTER DATABASE <database_name> CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Changing it per table:

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Good practice is to change it at table level as it'll change it for columns as well. Changing for specific column is for any specific case.

Changing collation for a specific column:

ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' charset=utf8 COLLATE utf8_unicode_ci;')

from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema' limit 100;

Sunday, July 16, 2017

PayWay Payment GateWay | Hosted Payment Page Setup | PayWay Hosted Payment | Hosted Payment

It's easy to integrate PayWay hosted payment system. First we need an account. If you have not any PayWay account yet, go to https://www.payway.com.au/core/LoginView and create an account first.

Now visit to https://www.payway.com.au/net/HostedPaymentPageSetupView and follow below screen:

Next step is to collect PayWay biller code:

Next step is to setup some notification configuration:

Below screen for notification panel, the marked url will be notified once a payment made via POST method:

I used "http://luckyorange.net/payway-hosted/callback.php?ThisPartWould0=&SecurityCheck=" as "Browser Return URL".

Where SecurityCheck field will be filled up when return to our working server.

Now from below page we will collect decryption key and security username and password. You have to add your server ip address here.

Now we will setup customer reference field as below:

Next we will setup minimum and maximum payment amount.

Next we will setup surcharge configuration:

And now finally we will do implementation.

First we will create a payment token and then redirect to PayWay payment page, below is a PHP script:

<?php
include_once "CurlExecutor.php";

$token_url = "https://www.payway.com.au/RequestToken";
$payment_url = "https://www.payway.com.au/MakePayment";
$redirect_uri = "http://luckyorange.net/payway-hosted/callback.php?ThisPartWould0=&SecurityCheck=";

define("BILLER_CODE", "10...6");
define("USERNAME", "T1...");
define("PASSWORD", "N........");

$post_data = "biller_code=" . BILLER_CODE;
$post_data = $post_data . "&username=" . USERNAME;
$post_data = $post_data . "&password=" . PASSWORD;

/* CUSTOMER INFORMATION */
$post_data = $post_data . "&information_fields=Name,InvoiceNO,Address";
$post_data = $post_data . "&Name=" . urlencode("Pritom Kumar");
$post_data = $post_data . "&InvoiceNO=" . strtoupper(substr(md5(time()), 0, 10));
$post_data = $post_data . "&Address=" . urlencode("Some Address");

/* HIDDEN FIELDS */
$post_data = $post_data . "&hidden_fields=SecurityCheck";
$post_data = $post_data . "&SecurityCheck=" . urlencode("SECURE TEXT");

/* PRODUCT DETAILS */
$post_data = $post_data . "&Shampoo=1,0.10";
$post_data = $post_data . "&Soap=2,0.20";

/* REQUEST HEADERS */
$headers[] = "Content-type: application/x-www-form-urlencoded";

$response = CurlExecutor::execute($token_url, "POST", $post_data, null, $headers);
if ($response["code"] == 200) {
    $token = substr($response["response"], 6);
    header("Refresh:0; url=$payment_url?biller_code=" . BILLER_CODE . "&token=" . $token);
}
CurlExecutor::prettyPrint($response);

Which will redirect to PayWay payment page as below screenshot:

After successful payment PayWay will make a redirection to our server (redirect url we provided) as below format:

http://luckyorange.net/payway-hosted/callback.php?EncryptedParameters=...&Signature=...

We will decrypt "EncryptedParameters" using AES 128 algorithm with PKCS7 padding. Key is "Encryption Key" on "Security Information" page.

<?php
function decrypt($key, $to_decrypt)
{
    $iv_size = mcrypt_get_iv_size(MCRYPT_RIJNDAEL_128, MCRYPT_MODE_CBC);
    $iv = substr($to_decrypt, 0, $iv_size);
    $to_decrypt = mcrypt_decrypt(MCRYPT_RIJNDAEL_128, $key, substr($to_decrypt, $iv_size), MCRYPT_MODE_CBC, $iv);
    $pad = ord($to_decrypt[strlen($to_decrypt) - 1]);
    return substr($to_decrypt, 0, -$pad);
}

$key = base64_decode("NI+YM.............ojAIQ==");
$params = decrypt($key, base64_decode($_GET["EncryptedParameters"]));
echo "<pre>";
print_r(explode("&", $params));
echo "</pre>";

And would be like below:

Array
(
    [0] => SecurityCheck=SECURE+TEXT
    [1] => payment_reference=E4DF550696
    [2] => payment_amount=0.50
    [3] => payment_date=20170717
    [4] => payment_time=17+Jul+2017+01%3A33%3A53
    [5] => payment_number=1979758473
    [6] => bank_reference=1979758473
    [7] => remote_ip=103.59.179.132
    [8] => card_type=VISA
    [9] => response_code=08
    [10] => summary_code=0
    [11] => response_text=Honour+with+identification
    [12] => payment_status=approved
)

And the payment in PayWay below screenshot:

So it's all now. Pure implementation. Step by step description. Simple coding. At last you can download PayWay hosted payment documentation from link next PayWay Hosted Payment Guide.

Saturday, July 15, 2017

BrainTree Payment API | Get Payment | Retrieve Payment | Get Payment Details

For merchant ID, public key and private key you can have a look at below link:

http://pritomkumar.blogspot.com/2017/07/braintree-create-payment-token-store.html

Full example to get payment/sale details from BrainTree given below:

<?php
require_once "CurlExecutor.php";
require_once "BrainTreeUtils.php";

define("MERCHANT_ID", "k9y........xj6");
define("PUBLIC_KEY", "85d........hh7");
define("PRIVATE_KEY", "510d.........................8a00");

$base = "/transactions/1689k3ra";
$test = "https://api.sandbox.braintreegateway.com:443/merchants/" . MERCHANT_ID . $base;
$live = "https://api.braintreegateway.com:443/merchants/" . MERCHANT_ID . $base;

$headers[] = "Accept: application/xml";
$headers[] = "Content-Type: application/xml";
$headers[] = "X-ApiVersion: 4";
$closure = function(&$curl) {
    curl_setopt($curl, CURLOPT_HTTPAUTH, CURLAUTH_BASIC);
    curl_setopt($curl, CURLOPT_USERPWD, PUBLIC_KEY . ':' . PRIVATE_KEY);
};
$result = CurlExecutor::execute(
    $test, "GET", null, null, $headers, $closure
);
CurlExecutor::prettyPrint(BrainTreeUtils::arrayFromXml($result["response"]));

And response from BrainTree API end:

BrainTree Payment API | Create Refund | Refund Payment

For merchant ID, public key and private key you can have a look at below link:

http://pritomkumar.blogspot.com/2017/07/braintree-create-payment-token-store.html

Below is full example given:

<?php
require_once "CurlExecutor.php";
require_once "BrainTreeUtils.php";

define("MERCHANT_ID", "k9y......xj6");
define("PUBLIC_KEY", "85....hh7");
define("PRIVATE_KEY", "510d5......................a00");

$array = array(
    "transaction" => array(
        'amount' => "1.00",
        'orderId' => htmlentities("REFUND_ID_" . time())
    )
);

$base = "/transactions/6wex6xr6/refund";
$test = "https://api.sandbox.braintreegateway.com:443/merchants/" . MERCHANT_ID . $base;
$live = "https://api.braintreegateway.com:443/merchants/" . MERCHANT_ID . $base;

$headers[] = "Accept: application/xml";
$headers[] = "Content-Type: application/xml";
$headers[] = "X-ApiVersion: 4";
$closure = function(&$curl) {
    curl_setopt($curl, CURLOPT_HTTPAUTH, CURLAUTH_BASIC);
    curl_setopt($curl, CURLOPT_USERPWD, PUBLIC_KEY . ':' . PRIVATE_KEY);
};
$result = CurlExecutor::execute(
    $test, "POST", BrainTreeUtils::arrayToXml($array),
    null, $headers, $closure
);
CurlExecutor::prettyPrint(BrainTreeUtils::arrayFromXml($result["response"]));

Response from BrainTree Payment Gateway:

BrainTree API | Create Payment | Create Payment Using Credit Card | Credit Card Payment

For merchant ID, public key and private key you can have a look at below link:

http://pritomkumar.blogspot.com/2017/07/braintree-create-payment-token-store.html

Below is a full PHP script:

'submitForSettlement' => true|false

The option that determines whether an authorized transaction is submitted for settlement.

<?php
require_once('CurlExecutor.php');
require_once "BrainTreeUtils.php";

define("MERCHANT_ID", "k......8xj6");
define("PUBLIC_KEY", "85......brhh7");
define("PRIVATE_KEY", "510d.............8a00");

$array = array(
    "transaction" => array(
        "type" => "sale",
        'amount' => "1.00",
        'orderId' => htmlentities("ORDER_ID_" . time()),
        'creditCard' => array(
            'cardholderName' => htmlentities("Test"),
            'number' => htmlentities("4444333322221111"),
            'expirationMonth' => htmlentities("12"),
            'expirationYear' => htmlentities("18")
        ),        'options' => array(
            'submitForSettlement' => true
        )
)
);

$test = "https://api.sandbox.braintreegateway.com:443/merchants/" . MERCHANT_ID . "/transactions";
$live = "https://api.braintreegateway.com:443/merchants/" . MERCHANT_ID . "/transactions";

$headers[] = "Accept: application/xml";
$headers[] = "Content-Type: application/xml";
$headers[] = "X-ApiVersion: 4";
$closure = function(&$curl) {
    curl_setopt($curl, CURLOPT_HTTPAUTH, CURLAUTH_BASIC);
    curl_setopt($curl, CURLOPT_USERPWD, PUBLIC_KEY . ':' . PRIVATE_KEY);
};
$result = CurlExecutor::execute(
    $test, "POST", BrainTreeUtils::arrayToXml($array),
    null, $headers, $closure
);
CurlExecutor::prettyPrint(BrainTreeUtils::arrayFromXml($result["response"]));

And output/response from BrainTree API is below:

Screenshot:

BrainTree API | Create Payment | Create Payment Using Stored Token | Token Payment

For merchant ID, public key and private key you can have a look at below link:

http://pritomkumar.blogspot.com/2017/07/braintree-create-payment-token-store.html

Below is a full PHP script:

You can also specify "customerId" instead of "paymentMethodToken" in the below variable $array (filled red color) as:

"customerId" => "brain_tree_customer_id" and then default payment method of the selected customer will be used.

'submitForSettlement' => true|false

The option that determines whether an authorized transaction is submitted for settlement.

<?php
require_once('CurlExecutor.php');
require_once "BrainTreeUtils.php";

define("MERCHANT_ID", "k9yxxxxxxj6");
define("PUBLIC_KEY", "85xxxxxxrhh7");
define("PRIVATE_KEY", "510xxxxxxxxxxxxxxxxxxxx8a00");

$array = array(
    "transaction" => array(
        "type" => "sale",
        'amount' => "1.00",
        'paymentMethodToken' => "6m76wy",
        'orderId' => htmlentities("ORDER_ID_" . time()),        'options' => array(
            'submitForSettlement' => true
        )
)
);

$test = "https://api.sandbox.braintreegateway.com:443/merchants/" . MERCHANT_ID . "/transactions";
$live = "https://api.braintreegateway.com:443/merchants/" . MERCHANT_ID . "/transactions";

$headers[] = "Accept: application/xml";
$headers[] = "Content-Type: application/xml";
$headers[] = "X-ApiVersion: 4";
$closure = function(&$curl) {
    curl_setopt($curl, CURLOPT_HTTPAUTH, CURLAUTH_BASIC);
    curl_setopt($curl, CURLOPT_USERPWD, PUBLIC_KEY . ':' . PRIVATE_KEY);
};
$result = CurlExecutor::execute(
    $test, "POST", BrainTreeUtils::arrayToXml($array),
    null, $headers, $closure
);
CurlExecutor::prettyPrint(BrainTreeUtils::arrayFromXml($result["response"]));