FloatBackOfffice/CRONS/account_process_subscription.php

<?php

echo "[" . date("Y-m-d H:i:s") . "] account_process_subscription job is starting.\n";
require('../backend.php');

// We will not limit the SQL we will exhaust the allowed memory
// we will process data in ${hard_limit} batches and hopefully catch up
$hard_limit = 550;
$span_limit = 30;


$db_host = $savvyext->cfgReadChar('database.host');
$db_name = $savvyext->cfgReadChar('database.name');
$db_user = $savvyext->cfgReadChar('database.user');
$db_pass = $savvyext->cfgReadChar('database.pass');
$db_port = $savvyext->cfgReadLong('database.port');
$connstr = "host=${db_host} port=${db_port} dbname=${db_name} user=${db_user} password=${db_pass}";
$conn = pg_connect($connstr);

$db_host = $savvyext->cfgReadChar('database_replica.host');
$db_name = $savvyext->cfgReadChar('database_replica.name');
$db_user = $savvyext->cfgReadChar('database_replica.user');
$db_pass = $savvyext->cfgReadChar('database_replica.pass');
$db_port = $savvyext->cfgReadLong('database_replica.port');
$readOnlyReplicaConnstr = "host=${db_host} port=${db_port} dbname=${db_name} user=${db_user} password=${db_pass}";
$readOnlyReplicaConn = pg_connect($readOnlyReplicaConnstr);

// Expire any late subscription here 
// Processing the transactions now 
$mysql = "SELECT ma.id AS assign_id, ma.*,mc.card_reciept AS providers FROM members_card_assign ma "
        . " LEFT JOIN main_cards mc ON mc.id = ma.card_id  "
        . " WHERE ma.subscribe IS NOT NULL AND ma.completed IS NULL AND ma.reciept_count =0 "
        . " AND mc.card_reciept IS NOT NULL AND mc.button1_action IN ('GOOFFERS') "
        . " ORDER BY ma.updated ASC LIMIT $hard_limit";
//echo $mysql;

$r0 = pg_query($readOnlyReplicaConn, $mysql);

if ($r0 && pg_num_rows($r0)) {

    while ($f0 = pg_fetch_assoc($r0)) {
        $member_id = $f0["member_id"];
        $assign_id = $f0["assign_id"];
        $providers = $f0["providers"];
        $subscribe_date = $f0["subscribe"];

        $provider_name = "";

        // let just mar trhis so that w dont over procss same trasaction
        $mysql_up = "UPDATE members_card_assign SET updated = now() WHERE id = $assign_id AND member_id = $member_id";
        pg_query($conn, $mysql_up);

        if ($providers != '' && $providers > 0) {
            // get the provider name 
            // We have to visist this again to improve the srach string
            $mysql_find = "SELECT id, LOWER(name) , LOWER(name_alias)  FROM transport_providers WHERE id = $providers";
            $alias_extra = "";

            $rf = pg_query($readOnlyReplicaConn, $mysql_find);
            if ($rf && pg_num_rows($rf)) {

                $r = pg_fetch_row($rf);
                $provider_name = $r[1]; // may be an array 
                $alias_string = $r[2];

                // $alias_string = "AA,BB,CC,";

                if (strlen($alias_string) > 0) {
                    $pieces = explode(",", $alias_string);
                    $count_item = count($pieces);
                    for ($ii = 0; $ii < $count_item; $ii++) {
                        $this_element = trim(strtolower($pieces[$ii]));
                        if (strlen($this_element) > 0) {
                            $itm_str = "'" . $this_element . "'";
                            if ($ii > 0) {
                                $alias_extra .= ",";
                            }
                            $alias_extra .= $itm_str;
                        }
                    }
                }
            }

            //  echo $provider_name;

            if ($provider_name != '') {
                $total_bank_recipts = countAccountReciepts($readOnlyReplicaConn, $member_id, $provider_name, $subscribe_date, $span_limit,$alias_extra);
                $total_email_recipts = countPearsedReciepts($readOnlyReplicaConn, $member_id, $providers, $subscribe_date, $span_limit);
                $total_bank_extra_recipts = countOtherAccountReciepts($readOnlyReplicaConn, $member_id, $provider_name, $subscribe_date, $span_limit,$alias_extra);
                $total_email_extra_recipts = countOtherPearsedReciepts($readOnlyReplicaConn, $member_id, $providers, $subscribe_date, $span_limit);
                $total_recipts = $total_bank_recipts + $total_email_recipts + $total_bank_extra_recipts + $total_email_extra_recipts;

                if ($total_recipts > 0) {
                    $mysql_up1 = "UPDATE members_card_assign SET updated = now(),reciept_count=$total_recipts WHERE id = $assign_id AND member_id = $member_id";
                    pg_query($conn, $mysql_up1);
                }
            }
        }
    }
}

function countAccountReciepts($readOnlyReplicaConn, $member_id, $provider_name, $subscribe_date, $span_limit,$alias_extra) {
    $total_rec = 0;
    
    $provider_total =  "'".$provider_name."'"; 
    if ( strlen($alias_extra) >   0 ){
        $provider_total= $provider_total.",".$alias_extra;
    }
    
    $mysql = "SELECT count(id) AS total_rec FROM members_bankimport WHERE member_id=$member_id AND lower(merchant_name) IN ($provider_total) AND time >= '$subscribe_date'";
    $rf = pg_query($readOnlyReplicaConn, $mysql);
    if ($rf && pg_num_rows($rf)) {
        $r = pg_fetch_row($rf);
        $total_rec = $r[0];
    }
    return $total_rec;
}

function countPearsedReciepts($readOnlyReplicaConn, $member_id, $provider, $subscribe_date, $span_limit) {

    $total_rec = 0;
    $mysql = "SELECT count(pi.id) AS total_rec FROM parsedemail_item pi LEFT JOIN trackedemail_item ti ON ti.id=pi.trackedemail_item_id WHERE ti.member_id=$member_id AND pi.transport_provider_id=$provider AND pi.travel_date >= '$subscribe_date'";
    $rf = pg_query($readOnlyReplicaConn, $mysql);
    if ($rf && pg_num_rows($rf)) {
        $r = pg_fetch_row($rf);
        $total_rec = $r[0];
    }
    return $total_rec;
}

function countOtherAccountReciepts($conn, $member_id, $provider_name, $subscribe_date, $span_limit,$alias_extra) {

    $total_rec = 0;
    $provider_total =  "'".$provider_name."'"; 
    if ( strlen($alias_extra) >   0 ){
        $provider_total= $provider_total.",".$alias_extra;
    }
    
    $mysql = "SELECT count(id) AS total_rec FROM members_transactions_import_raw  WHERE member_id=$member_id AND LOWER(merchant_name)  IN ($provider_total) AND time >= '$subscribe_date'";
    //echo $mysql;
    $rf = pg_query($conn, $mysql);
    if ($rf && pg_num_rows($rf)) {
        $r = pg_fetch_row($rf);
        $total_rec = $r[0];
    }
    return $total_rec;
}

function countOtherPearsedReciepts($conn, $member_id, $provider, $subscribe_date, $span_limit) {

    $total_rec = 0;
    $mysql = "SELECT count(id) AS total_rec FROM parsedemail_item_payment  WHERE member_id=$member_id AND transport_provider_id=$provider AND receipt_datetime >= '$subscribe_date'";
    //echo $mysql;
    $rf = pg_query($conn, $mysql);
    if ($rf && pg_num_rows($rf)) {
        $r = pg_fetch_row($rf);
        $total_rec = $r[0];
    }
    return $total_rec;
}

pg_close($conn);
pg_close($readOnlyReplicaConn);
echo "[" . date("Y-m-d H:i:s") . "]  account_process_subscription job complete.\n";
?>