dev-chiefworks
258 lines
7.3 KiB
PHP
258 lines
7.3 KiB
PHP
<?php
|
|
|
|
// require_once( '/Users/osx/Sites/Float/adminsavvy/backend.php' );
|
|
include '../backend.php';
|
|
require_once( 'common/Logger.php' );
|
|
|
|
/**
|
|
* Transfer Subscription Report
|
|
*/
|
|
class SubscribedSummary {
|
|
|
|
public $conn;
|
|
public $repConn;
|
|
|
|
protected $hard_limit = 550;
|
|
|
|
function __construct() {
|
|
global $savvyext;
|
|
$this->conn = self::__cnn( $savvyext );
|
|
$this->repConn = self::__repCnn( $savvyext );
|
|
}
|
|
|
|
private static function __cnn( $savvyext ) {
|
|
$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 = sprintf( 'host=%s port=%s dbname=%s user=%s password=%s', $db_host, $db_port, $db_name, $db_user, $db_pass );
|
|
$conn = pg_connect($connstr);
|
|
|
|
return $conn;
|
|
}
|
|
|
|
private static function __repCnn( $savvyext ) {
|
|
$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');
|
|
$connstr = sprintf( 'host=%s port=%s dbname=%s user=%s password=%s', $db_host, $db_port, $db_name, $db_user, $db_pass );
|
|
$conn = pg_connect($connstr);
|
|
|
|
return $conn;
|
|
}
|
|
|
|
/**
|
|
* Get all members_card_assign items
|
|
* @return array
|
|
*/
|
|
public function getDeals() {
|
|
try {
|
|
// get all deals card
|
|
$sql = sprintf( "
|
|
SELECT
|
|
mc.id as cardid,
|
|
case when mc.card_country is null then 'Worldwide' else mc.card_country end as country
|
|
from main_cards mc
|
|
where mc.button1_action in ('GOOFFERS')
|
|
and mc.status = 1
|
|
" );
|
|
|
|
$results = pg_query( $this->repConn, $sql );
|
|
|
|
if ( $error = pg_last_error( $this->repConn ) ) {
|
|
throw new Exception( $error );
|
|
}
|
|
|
|
$results = pg_fetch_all( $results );
|
|
|
|
return $results;
|
|
|
|
} catch (Exception $e) {
|
|
Logger::debug( $e->getMessage() );
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Get total redeem by cards
|
|
* @param array $card_ids list card ids
|
|
* @return array
|
|
*/
|
|
private function getRedeem( $card_ids ) {
|
|
$redeem = sprintf("
|
|
SELECT
|
|
mca.card_id as cardid,
|
|
count( mca ) as redeem
|
|
from members_card_assign mca
|
|
left join main_cards mc ON mc.id = mca.card_id
|
|
where
|
|
mca.completed is not null
|
|
and mca.status = 1
|
|
and mca.card_id in (%s)
|
|
group by cardid
|
|
", $card_ids);
|
|
$results = pg_query( $this->repConn, $redeem );
|
|
|
|
if ( $error = pg_last_error( $this->repConn ) ) {
|
|
throw new Exception( $error );
|
|
}
|
|
|
|
$redeem = pg_fetch_all( $results );
|
|
|
|
return $redeem;
|
|
}
|
|
|
|
/**
|
|
* Get total report
|
|
* @param array $card_ids list card ids
|
|
* @return array
|
|
*/
|
|
private function getReport( $card_ids ) {
|
|
$report = sprintf( "
|
|
SELECT
|
|
mca.card_id as cardid,
|
|
sum( case when mca.added between now() - interval '24 hours' and now() then 1 else 0 end ) as last_24hours,
|
|
sum( case when mca.added between now() - interval '7 days' and now() then 1 else 0 end ) as last_7days,
|
|
sum( case when mca.added between now() - interval '14 days' and now() then 1 else 0 end ) as last_14days,
|
|
sum( case when mca.added between now() - interval '30 days' and now() then 1 else 0 end ) as last_30days,
|
|
count( mca.id ) as total_sub
|
|
from members_card_assign mca
|
|
left join main_cards mc ON mc.id = mca.card_id
|
|
where
|
|
mc.card_reciept is not null
|
|
and mca.subscribe is not null
|
|
and mca.completed is null
|
|
and mca.reciept_count = 0
|
|
and mca.card_id in (%s)
|
|
group by cardid
|
|
", $card_ids );
|
|
$results = pg_query( $this->repConn, $report );
|
|
|
|
if ( $error = pg_last_error( $this->repConn ) ) {
|
|
throw new Exception( $error );
|
|
}
|
|
|
|
$report = pg_fetch_all( $results );
|
|
|
|
return $report;
|
|
}
|
|
|
|
/**
|
|
* Get all subscribed report
|
|
* @return array
|
|
*/
|
|
public function getSubscripbed() {
|
|
$deals = $this->getDeals();
|
|
try {
|
|
if ( !$deals ) {
|
|
throw new Exception( 'Deals card not found!' );
|
|
}
|
|
|
|
$card_ids = implode( ', ', array_column( $deals, 'cardid' ) );
|
|
// get subscribed total redeem
|
|
$redeem = $this->getRedeem( $card_ids );
|
|
// get subscribed report
|
|
$report = $this->getReport( $card_ids );
|
|
|
|
// combine report and redeem
|
|
if ( $report ) {
|
|
foreach ( $report as $key => $value ) {
|
|
$report[ $key ]['total_redeem'] = 0;
|
|
if ( $redeem ) {
|
|
foreach ( $redeem as $subkey => $subvalue ) {
|
|
if ( $subvalue['cardid'] == $value['cardid'] ) {
|
|
$report[ $key ]['total_redeem'] = $subvalue['redeem'];
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
$results = array();
|
|
array_walk( $report, function( $value, $key ) use (&$results) {
|
|
$results[ $value['cardid'] ] = $value;
|
|
} );
|
|
foreach ( $deals as $key => $value ) {
|
|
$cardid = $value['cardid'];
|
|
$deals[ $key ]['last_24hours'] = isset( $results[ $cardid ] ) ? $results[ $cardid ]['last_24hours'] : 0;
|
|
$deals[ $key ]['last_7days'] = isset( $results[ $cardid ] ) ? $results[ $cardid ]['last_7days'] : 0;
|
|
$deals[ $key ]['last_14days'] = isset( $results[ $cardid ] ) ? $results[ $cardid ]['last_14days'] : 0;
|
|
$deals[ $key ]['last_30days'] = isset( $results[ $cardid ] ) ? $results[ $cardid ]['last_30days'] : 0;
|
|
$deals[ $key ]['total_sub'] = isset( $results[ $cardid ] ) ? $results[ $cardid ]['total_sub'] : 0;
|
|
$deals[ $key ]['total_redeem'] = isset( $results[ $cardid ] ) ? $results[ $cardid ]['total_redeem'] : 0;
|
|
}
|
|
}
|
|
|
|
return $deals;
|
|
|
|
} catch (Exception $e) {
|
|
Logger::debug( $e->getMessage() );
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Update report after get all subscribed
|
|
* @return mixed
|
|
*/
|
|
public function updateSubscribedReport() {
|
|
$results = $this->getSubscripbed();
|
|
try {
|
|
|
|
if ( !$results ) {
|
|
throw new Exception( 'Subscribed not found!' );
|
|
}
|
|
|
|
$table_columns = array(
|
|
'card_id',
|
|
'country',
|
|
'last_24hours',
|
|
'last_7days',
|
|
'last_14days',
|
|
'last_30days',
|
|
'total_sub',
|
|
'total_redeem'
|
|
);
|
|
$table_results = array();
|
|
array_filter( $results, function( $var ) use ( &$table_results ) {
|
|
$content = array();
|
|
array_walk( $var, function( $value, $key ) use ( &$content ) {
|
|
$value = $value ?? 0;
|
|
array_push( $content, $value );
|
|
} );
|
|
$table_results[] = sprintf( '(%s)', implode( ', ', $content ) );
|
|
} );
|
|
|
|
$sql = sprintf( "
|
|
begin;
|
|
truncate table subscription_summary;
|
|
insert into subscription_summary (%s)
|
|
values %s;
|
|
commit;
|
|
rollback;
|
|
",
|
|
// insert
|
|
implode( ', ', $table_columns ),
|
|
preg_replace( '/([a-zA-Z]+)/m', '\'$0\'', implode( ', ', $table_results ) ) );
|
|
|
|
$results = pg_query( $this->conn, $sql );
|
|
|
|
if ( $error = pg_last_error( $this->repConn ) ) {
|
|
throw new Exception( $error );
|
|
}
|
|
|
|
return 'success';
|
|
} catch (Exception $e) {
|
|
Logger::debug( $e->getMessage() );
|
|
}
|
|
}
|
|
}
|
|
|
|
echo "[" . date("Y-m-d H:i:s") . "] subscribed_summary job is starting.\n";
|
|
|
|
$instance = new SubscribedSummary();
|
|
// $instance->getSubscripbed();
|
|
echo $instance->updateSubscribedReport() . PHP_EOL;
|
|
|
|
echo "[" . date("Y-m-d H:i:s") . "] subscribed_summary job is ending.\n";
|