// pull data from AWReports
$query = "\tSELECT account_descriptive_name,\n\t\t\t\tSUM(cost) AS monthly_spend,\n\t\t\t\tSUM(clicks) AS total_clicks,\n\t\t\t\tSUM(impressions) AS total_impressions\n\t\t\tFROM AWReports.AW_ReportAd awr\n\t\t\tWHERE awr.account_id = :client_id\n\t\t\t\tAND MONTH(day) = :report_month\n\t\t\t\tAND YEAR(day) = :report_year\n\t\t\t\tAND awr.DEVICE IS NOT NULL";
$bind = array(':client_id' => $original_row['client_id'], ':report_month' => $report_month, ':report_year' => $report_year);
try {
$result = db::connect('awreports')->select($query)->binds($bind)->execute();
} catch (DatabaseException $e) {
$_SESSION['members']['temp']['error']['check'][] = array('', 'Reports currently unavailable');
redirect('/members/ad-campaign/');
}
if ($result->row_count() > 0) {
$row = $result->fetch();
$contain_google_data = true;
}
$original_row = array_merge($original_row, $row);
$original_row['currency_id'] = !empty($original_row['currency_id']) ? $original_row['currency_id'] : $_SESSION['members']['currency']['id'];
$currency_data = get_currency_information($original_row['currency_id']);
$client_id = $original_row['client_id'];
$account_descriptive_name = $original_row['account_descriptive_name'];
$template_data['product_data_id'] = $product_data_id;
$template_data['year_period_start_date'] = $year_period_start_date;
$template_data['year_period_end_date'] = $year_period_end_date;
$template_data['client_id'] = $client_id;
$template_data['report_month'] = $report_month;
$template_data['report_year'] = $report_year;
$template_data['base_product_link'] = "../details/?id={$product_data_id}";
// header information
$template_data['domain_name'] = $original_row['ad_links'];
$template_data['ad_links'] = add_http($original_row['ad_links']);
$template_data['monthly_budget'] = $original_row['monthly_budget'] > 0 ? number_format($original_row['monthly_budget'], 2, '.', ',') : ($original_row['current_monthly_budget'] > 0 ? number_format($original_row['current_monthly_budget'], 2, '.', ',') : '0.00');
$template_data['monthly_balance'] = date('Y-m') == date('Y-m', mktime(0, 0, 0, $report_month, 1, $report_year)) ? number_format($original_row['balance'], 2, '.', ',') : '0.00';
$template_data['currency_symbol'] = $currency_data['symbol'];
$period = new DatePeriod(new DateTime($year_period_start_date), $interval, new DateTime($last_year . '-' . $last_month . '-01'));
}
// json encoded data to be sent to google chart api
$measurement_json_data = array();
foreach ($measurement_array as $label) {
if ($result->row_count() < 13) {
$missing_months_campaign_summary = array();
foreach ($period as $dt) {
$missing_months_campaign_summary[] = array(substr($month_name[$dt->format("n")], 0, 3) . '/' . $dt->format("Y"), 0);
}
${$label} = array_merge($missing_months_campaign_summary, ${$label});
}
$measurement_json_data[$label] = array_merge(array(array('Period', $label)), array_reverse(${$label}));
}
$currency['currency_id'] = $currency_id != '' ? $currency_id : $_SESSION['members']['currency']['id'];
$currency_data = get_currency_information($currency['currency_id']);
$template_data['currency_symbol'] = $currency_data['symbol'];
if (date("m") == $report_month && date("Y") == $report_year) {
$date_budget = date("Y-m-d");
} else {
$date_budget = date('Y-m-t', mktime(0, 0, 0, $report_month + 1, 0, $report_year));
}
// calculates the budget/day
$query = "\tSELECT budget/:quantity_days AS budget\n\t\t\t\tFROM sYra_logging.google_advertising_budget gabl\n\t\t\t\t\tLEFT JOIN sYra.google_advertising_to_monthly_budget ga2mb ON gabl.product_data_id = ga2mb.monthly_budget_product_data_id\n\t\t\t\t\tLEFT JOIN sYra.generic_products_data gpd ON gpd.product_data_id = gabl.product_data_id\n\t\t\t\tWHERE :date_budget >= start_date\n\t\t\t\t\tAND :date_budget <= end_date\n\t\t\t\t\tAND IF(gpd.product_id IN (4171, 4172, 4173, 4174, 4175, 4180), ga2mb.monthly_budget_product_data_id = :product_data_id, ga2mb.product_data_id = :product_data_id)";
$bind = array(':quantity_days' => $quantity_days, ':date_budget' => $date_budget, ':product_data_id' => $product_data_id);
$row = $db_syssql->select($query)->binds($bind)->execute()->fetch();
$template_data['campaign_summary_budget'] = number_format($row['budget'], 2, ".", ",");
$template_data['campaign_summary_json_data'] = json_encode($measurement_json_data);
$contain_google_data = true;
} else {
$template->hideBlock("campaign_summary_google_data");