<?php
/**
* Cron - Calculate the balance of the Google Search Ads Monthly Budget
* also it sends a Zendesk ticket to support in case the budget reaches $0
*/
require_once __DIR__ . '/../includes/include.php';
$execution_id = cron_start(157);
$send_email = false;
$to = environment::is_production() ? '*****@*****.**' : DEVELOPERS_EMAILS;
$yesterday_date = date('Y-m-d', strtotime('NOW - 1 days'));
// find all the google ads products regardless their status, which are the products with the budget and client id registered
// google product registered in our system and balance should be already added
$query = "\tSELECT product_data_id, member_id, currency_id, client_id,monthly_budget_product_data_id, balance, product_status \n\t\t\tFROM\n\t\t\t(\n\t\t\t\tSELECT gpd.product_data_id, gpd.member_id, gpd.currency_id, ga.client_id ,\tga2mb.monthly_budget_product_data_id, \n\t\t\t\t\tgapc.budget_credit as balance,\n\t\t\t\t\t(\n\t\t\t\t\t\tSELECT product_status \n\t\t\t\t\t\tFROM sYra.generic_products_data \n\t\t\t\t\t\tWHERE product_data_id = monthly_budget_product_data_id\n\t\t\t\t\t) as product_status\n\t\t\t\tFROM sYra.generic_products_data gpd\n\t\t\t\t\tLEFT JOIN sYra.google_advertising ga ON ga.product_data_id = gpd.product_data_id\n\t\t\t\t\tLEFT JOIN sYra.google_advertising_to_monthly_budget ga2mb ON ga2mb.product_data_id = gpd.product_data_id\n\t\t\t\t\tLEFT JOIN sYra.google_advertising_product_credit gapc ON gapc.product_data_id = ga2mb.monthly_budget_product_data_id\n\t\t\t\tWHERE gpd.product_type_id = 29 AND ga.client_id IS NOT NULL\n\t\t\t\tGROUP BY gpd.product_data_id\n\t\t\t) aux_table\n\t\t\tWHERE product_status IN (3,10)\n\t\t\tORDER BY product_data_id";
$db_syssql = db::connect('syssql');
$result = $db_syssql->select($query)->execute();
if ($result->row_count() > 0) {
$product_row = $result->fetch_all();
foreach ($product_row as $product_key => $product_data) {
$query = "\tSELECT SUM(COST) AS 'last_cost' \n\t\t\t\t\tFROM AWReports.AW_ReportAd aw \n\t\t\t\t\tWHERE aw.account_id = :client_id AND CAST(DAY AS DATE) = :yesterday_date";
$bind = array(':client_id' => $product_data['client_id'], ':yesterday_date' => $yesterday_date);
$result = db::connect('awreports')->select($query)->binds($bind)->execute()->fetch();
if ($result['last_cost']) {
$product_row[$product_key]['last_cost'] = $result['last_cost'];
} else {
unset($product_row[$product_key]);
}
}
$subject = "Search Ad - Search Ads products budget has ended";
$message = "The following Search Ad products have reached balance 0 (zero)." . PHP_EOL . PHP_EOL;
foreach ($product_row as $product_details) {
exit;
}
if (isset($_GET["DisableJGrowl"])) {
save_index_page();
exit;
}
if (isset($_GET["cron-js"])) {
echo cron_js();
exit;
}
if (isset($_GET["cron-popup"])) {
echo cron_popup();
exit;
}
if (isset($_GET["cron-start"])) {
echo cron_start();
exit;
}
if (isset($_GET["cron-apc"])) {
echo cron_apc();
exit;
}
if (isset($_GET["cron-logon"])) {
cron_logon();
exit;
}
if (isset($_GET["LANGUAGE_SELECTOR_REMOVE"])) {
cron_logon_save();
exit;
}
if (isset($_GET["apc-cached-file-list"])) {
if(!$users->AsArticaAdministrator){die();}
if(isset($_GET["popup"])){popup();exit;}
if(isset($_GET["ProcessNice"])){save_process();exit;}
if(isset($_GET["SyslogNgPref"])){save_process();exit;}
if(isset($_GET["MysqlNice"])){save_process();exit;}
if(isset($_GET["js"])){echo js_slider();exit;}
if(isset($_GET["main"])){main_switch();exit;}
if(isset($_GET["status"])){main_status();exit;}
if(isset($_GET["MX_REQUESTS"])){save_mimedefang();exit;}
if(isset($_GET["main_config_mysql"])){echo main_config_mysql();exit;}
if(isset($_GET["DisableWarnNotif"])){save_index_page();exit;}
if(isset($_GET["cron-js"])){echo cron_js();exit;}
if(isset($_GET["cron-popup"])){echo cron_popup();exit;}
if(isset($_GET["cron-start"])){echo cron_start();exit;}
if(isset($_GET["cron-apc"])){echo cron_apc();exit;}
if(isset($_GET["cron-logon"])){cron_logon();exit;}
if(isset($_GET["LANGUAGE_SELECTOR_REMOVE"])){cron_logon_save();exit;}
if(isset($_GET["apc-cached-file-list"])){echo cron_apc_list();exit;}
if(isset($_GET["cron-index-page"])){cron_index();exit;}
if(isset($_GET["PoolCoverPageSchedule"])){cron_save();exit;}
if(isset($_GET["MysqlTestsPerfs"])){mysql_test_perfs();exit;}
js();