FloatBackOfffice/CRONS/send_email_parse_fail_report.php

<?php

// require_once( '/Users/osx/Sites/Float/adminsavvy/backend.php' );
include '../backend.php';
require_once('../vendor/autoload.php');
require_once( 'common/Logger.php' );

use \SendGrid\Mail\Mail;

/**
 * Send list email parsed fail report
 */
class SendEmailReceiptFail {
	
	public $conn;
	public $repConn;

	public $sendgrid_api;
	public $sender;
	public $sender_name;
	public $receivers;

	protected $hard_limit = 550;

	function __construct() {
		global $savvyext;
		$this->conn = self::__cnn( $savvyext );
		$this->repConn = self::__repCnn( $savvyext );

		$this->sendgrid_api = $savvyext->cfgReadChar('mailsend.api_key');
		$this->sender = $savvyext->cfgReadChar('mailsend.from');
		$this->sender_name = $savvyext->cfgReadChar('mailsend.name');
		$this->receivers = array(
			'stew@float.sg' => 'Stew',
	        'olu@float.sg'  => 'Olu',
	        'chelsea@float.sg' => 'Chelsea',
	        'cornel@float.sg' => 'Cornel',
	        'lviet@float.sg' => 'Le Viet',
	        'tnguyen@float.sg' => 'Tri Nguyen',
	        'helen@goldenowl.asia' => 'Helen',
	        'george@goldenowl.asia' => 'George',
		);
	}

	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;
	}

	/**
	 * Send email report via SendGrid
	 * @param  array $data list email fail
	 * @return mixed       
	 */
	protected function send( $data ) {
		// create email layout
		ob_start();
		require_once( __DIR__ . '/email/email-parsed-fail.php' );
		$layout = ob_get_clean();
		// create sendgrid instance
		$sendgrid = new \SendGrid\Mail\Mail;
		$sendgrid->setFrom( $this->sender, $this->sender_name );
		$currentDate = date('D M j G:i');
		$last7days = date('D M j', strtotime('-7 days'));
		$sendgrid->setSubject("Report: Failed email parsers last 7 days - From $last7days to $currentDate UTC");
		$sendgrid->addTos( $this->receivers );
		$sendgrid->addContent( 'text/html', $layout );

		// send
		$send = new \SendGrid( $this->sendgrid_api );
		$response = $send->send( $sendgrid );

		if ( $response->statusCode() !== 202 ) {
			throw new Exception( 'Send email via Sendgrid get errors!' );
		}
	}

	/**
	 * Get all email receipt parsed fail
 * @return array
	 */
	public function getemails() {
		try {
			$sql = "
			SELECT pf.name AS merchant_name, ti.id AS trackedemail_item_id, m.email, m.id AS user_id, ti.subject, ti.message_date,
			CASE WHEN pf.reported_via_email IS TRUE THEN 'Yes' ELSE 'No' END AS reported
			FROM trackedemail_item ti
			INNER JOIN members m ON m.id = ti.member_id 
			INNER JOIN (
				SELECT trackedemail_item_id, tp.name, reported_via_email
				FROM parsedemail_item_failed pi
				LEFT JOIN transport_providers tp ON tp.id = pi.transport_provider_id
				WHERE pi.parsed_status = 0 AND pi.added > CURRENT_DATE - 7
			) pf ON pf.trackedemail_item_id = ti.id
			WHERE ti.parsed_status = 0 AND date_parsed IS NOT NULL";

			$results = pg_query( $this->repConn, $sql );

			if ( $error = pg_last_error( $this->repConn ) ) {
				throw new Exception( $error );
			}

			$results = pg_fetch_all( $results );

			if (!$results) {
				return null;
			}

			$new_parsed_fail_email_arr = [];

			foreach ($results as &$item) {
				// Save trackedemail item id for updating col 'reported_via_email' in table 'parsedemail_item_failed'
				if ($item['reported'] === 'No') {
					array_push($new_parsed_fail_email_arr, $item['trackedemail_item_id']);
				}
			}

			return [$results, $new_parsed_fail_email_arr];

		} catch (Exception $e) {
			Logger::debug( $e->getMessage() );
		}
	}

	/**
	 * Excute jobs
	 * @return mixed
	 */
	public function excute() {
		try {
			list($data['emails'], $data['new_parsed_fail_email']) = $this->getemails();
			$data['summary']= $this->getReportByMerchantName();

			if (empty($data['emails']) && empty($data['summary'])) {
				throw new Exception( 'No Email Receipt which parsed failed!' );
			}

			$send = $this->send( $data );

			// After report email has been sent, we will change 'reported_via_email' status
			if (!empty($data['new_parsed_fail_email'])) {
				foreach ($data['new_parsed_fail_email'] ?? [] as $email_id) {
					$sql = "UPDATE parsedemail_item_failed SET reported_via_email = TRUE WHERE trackedemail_item_id = $email_id";
					pg_query( $this->repConn, $sql );
				}
			}

		} catch (Exception $e) {
			Logger::debug( $e->getMessage() );
		}
	}

	public function getReportByMerchantName() {
		$results = [];

		$sql = "
		SELECT count(*) AS success, tp.name, tp.category
		FROM parsedemail_item pi
		LEFT JOIN transport_providers tp ON tp.id = pi.transport_provider_id
		WHERE pi.transport_provider_id IS NOT NULL AND pi.updated > CURRENT_DATE - 7
		GROUP BY pi.transport_provider_id, tp.name, tp.category";

		$q = pg_query($this->repConn, $sql);
		$parsedSuccessSummary = pg_fetch_all($q);

		if (!$parsedSuccessSummary) {
			$parsedSuccessSummary = [];
		}

		$sql = "
		SELECT count(*) AS fail, tp.name, tp.category
		FROM parsedemail_item_failed pi
		LEFT JOIN transport_providers tp ON tp.id = pi.transport_provider_id
		WHERE pi.transport_provider_id IS NOT NULL AND pi.added > CURRENT_DATE - 7
		GROUP BY pi.transport_provider_id, tp.name, tp.category";

		$q = pg_query($this->repConn, $sql);
		$parsedFailSummary = pg_fetch_all($q);
		
		if (!$parsedFailSummary) {
			$parsedFailSummary = [];
		}

		if (!$parsedSuccessSummary && !$parsedFailSummary) {
			return null;
		}

		// Combine success and fail summary
		foreach ($parsedFailSummary as $item) {
			$isExistTranportProvider = false;

			foreach ($parsedSuccessSummary as $item2) {
				if ($item2['name'] === $item['name']) {
					$isExistTranportProvider = true;
					break;
				}
			}

			if (!$isExistTranportProvider) {
				array_push($parsedSuccessSummary, $item);
			}
		}

		// Combine success and fail summary
		foreach ($parsedSuccessSummary as $item) {
			foreach ($parsedFailSummary as $item2) {
				if ($item2['name'] === $item['name']) {
					$item['fail'] = $item2['fail'];
				}
			}

			$item['fail'] = $item['fail'] ?? 0;
			$item['success'] = $item['success'] ?? 0;

			// Set transport
			if ($item['category'] !== 'Retail' && $item['category'] !== null) {
				$item['transport'] = 'Yes';
			} else {
				$item['transport'] = 'No';
			}

			unset($item['category']);

			array_push($results, $item);
		}

		// Calculate total for each merchant name
		foreach ($results as &$result) {
			$result['total'] = intval($result['success']) + intval($result['fail']);
		}

		return $results;
	}
}

$instace = new SendEmailReceiptFail;
$instace->excute();