function searchProductPricingViolations($request_info = array(), $skip_product_id = false)
{
//create range key from cron log
$from = $request_info['date_from'];
$to = $request_info['date_to'];
$crons = getLast24HoursCronIds($from, $to);
$last24HoursCronsData = getLast24HoursCronLog($crons);
foreach ($last24HoursCronsData as $l24hCD) {
if (strtotime($l24hCD->start_datetime) < $from) {
$from = strtotime($l24hCD->start_datetime);
}
if ($l24hCD->end_datetime != '0000-00-00 00:00:00' && strtotime($l24hCD->end_datetime) > $to) {
$to = strtotime($l24hCD->end_datetime);
}
}
// TODO: competitor_map
if (!empty($request_info['competitor_map'])) {
// get the intersection of the two sets of merchants
$sql = "SELECT cmn.id\n\t\t\t\tFROM " . $this->_table_products . " p\n\t\t\t\tLEFT JOIN " . $this->_table_crowl_product_list . " cpl ON cpl.upc=p.upc_code\n\t\t\t\tLEFT JOIN " . $this->_table_crowl_merchant_name . " cmn ON cmn.id=cpl.merchant_name_id\n\t\t\t\tWHERE p.id = ?";
$merchant_query = $sql . " AND cmn.id IN ({$sql})";
$merchants_intersection = array();
$lookup_merchants = array();
foreach ($request_info['competitor_map'] as $prodId => $owner_product) {
$result = $this->db->query($merchant_query, array($prodId, $owner_product['id']))->result_array();
for ($i = 0, $n = count($result); $i < $n; $i++) {
$merchant_id = $result[$i]['id'];
$merchants_intersection[$merchant_id] = true;
}
}
// get the intersection of merchants filter and the set we just made
if (isset($request_info['merchants'][0]) and $request_info['merchants'][0] !== 'all') {
foreach ($request_info['merchants'] as $merchant_id) {
if (isset($merchants_intersection[$merchant_id])) {
$lookup_merchants[$merchant_id] = true;
}
}
} else {
$lookup_merchants = $merchants_intersection;
}
$request_info['merchants'] = array_keys($lookup_merchants);
}
$where = '1=1';
$MarketFilter = array();
$orderBy = '';
$orderByTmp = 'products.id';
$whereVals = requestInfoWhereValues($request_info, $MarketFilter, $orderByTmp);
if (!$skip_product_id and isset($whereVals['products'])) {
$request_info['product_ids'] = array_filter($request_info['product_ids']);
$where .= ' AND products.id ' . $whereVals['products'];
$orderBy = " ORDER BY {$orderByTmp} ";
}
if (isset($whereVals['marketplaces'])) {
$where .= ' AND crowl_merchant_name_new.marketplace ' . $whereVals['marketplaces'];
$MarketFilter = $whereVals['marketplaces'];
}
if (isset($whereVals['merchants'])) {
$where .= ' AND crowl_merchant_name_new.id ' . $whereVals['merchants'];
}
if (isset($whereVals['store'])) {
$where .= ' AND products.store_id ' . $whereVals['store'];
}
//TODO: change hashkey to sellerId instead of merchant_name
$productQuery = "SELECT\n\t\t\t\tconcat(crowl_merchant_name_new.seller_id,'#',crowl_product_list_new.upc) as hashKey,\n\t\t\t\tcrowl_merchant_name_new.seller_id,\n\t\t\t\tproducts.upc_code,\n\t\t\t\tproducts.id,\n\t\t\t\tproducts.store_id,\n\t\t\t\tcrowl_merchant_name_new.merchant_name,\n\t\t\t\tcrowl_merchant_name_new.original_name\n\t\t\tFROM crowl_product_list_new\n\t\t\tINNER JOIN crowl_merchant_name_new ON crowl_merchant_name_new.id = crowl_product_list_new.merchant_name_id\n\t\t\tLEFT JOIN products ON products.upc_code = crowl_product_list_new.upc\n\t\t\tWHERE {$where}\n\t\t\tGROUP BY hashKey\n\t\t\t{$orderBy}";
//products.title,
//products.price_floor,
//products.retail_price,
//products.wholesale_price,
//echo "<pre>$productQuery<br>\n";exit;
$products = $this->db->query($productQuery)->result();
$finalProductsArray = array();
foreach ($products as $product) {
//TODO: store these two data inside the product_trends table instead
$retailPricePoint = getPricePoint($product->upc_code, $product->store_id, 'retail_price');
$wholesalePricePoint = getPricePoint($product->upc_code, $product->store_id, 'wholesale_price');
// get the "nosql" data
$priceTrends = $this->ProductsTrends->get_by_hashkey_and_date_range_and_marketplace($product->hashKey, $from, $to, $MarketFilter);
foreach ($priceTrends->result_object() as $priceTrend) {
//safety hack to not show incorrect violations
if ((double) $priceTrend->mpo >= (double) $priceTrend->ap) {
continue;
}
$violationTrendArray = array('productId' => (int) $product->id, 'upc_code' => (string) $product->upc_code, 'retail' => (double) $retailPricePoint, 'wholesale' => (double) $wholesalePricePoint, 'price' => (double) $priceTrend->mpo, 'map' => (double) $priceTrend->ap, 'title' => (string) $priceTrend->t, 'marketplace' => (string) $priceTrend->ar, 'url' => (string) $priceTrend->l, 'timestamp' => (int) $priceTrend->dt, 'hash_key' => (string) $product->hashKey, 'merchant_id' => (string) $product->seller_id, 'date' => (string) date('m/d/Y G:i:s', (int) $priceTrend->dt), 'shot' => (string) $priceTrend->ss);
$finalProductsArray[$product->id][] = $violationTrendArray;
}
}
return $finalProductsArray;
}
protected function _prep_report()
{
if ($this->submit) {
if (!empty($this->data->products[0])) {
$this->data->competitor_store_id = false;
if ($this->data->report === 'bycompetition') {
// this is a competitor's product
$this->data->competitor_products = $this->Product->getProductsById(null, $this->data->products);
$this->data->competitor_store_id = array();
for ($i = 0, $n = count($this->data->competitor_products); $i < $n; $i++) {
$this->data->competitor_store_id[] = $this->data->competitor_products[$i]['store_id'];
}
$this->data->competitor_store_id = array_unique($this->data->competitor_store_id);
$this->data->proMerchants = getProductMerchant($this->data->competitor_store_id);
if ($this->data->show_comparison) {
// and it's a comparison
$competitor_map = $this->Store->getCompetitorMap($this->store_id, $this->data->products);
$this->data->competitor_store_id[] = $this->store_id;
$products_wo_comparisons = $this->data->products;
$this->data->competitor_map = array();
foreach ($competitor_map as $bpp) {
$this->data->products[] = $bpp['owner_brand_product'];
$owned_product = $this->Product->getProductsById(null, $bpp['owner_brand_product']);
if (!empty($owned_product[0]['id'])) {
$this->data->competitor_map[$bpp['competitor_brand_product']] = $owned_product[0];
}
}
$products_w_comparisons = $this->data->products;
}
}
}
if ($this->data->date_from !== 'Start' && $this->data->date_to !== 'Stop') {
// TODO: set this to last crawl?
$this->data->date_from = strtotime($this->data->date_from . " 00:00:00");
$this->data->date_to = strtotime($this->data->date_to . " 23:59:59");
} else {
$tF = getTimeFrame($this->data->time_frame);
$this->data->date_from = $tF['date_from'];
$this->data->date_to = $tF['date_to'];
}
// $lookup_markets = $this->data->markets;
// if ( ! $this->data->all_markets AND $this->data->all_retailers)
// $lookup_markets = array_merge($lookup_markets, $this->data->retailerArr);
// elseif ( ! $this->data->all_retailers AND $this->data->all_markets)
// $lookup_markets = array_merge($lookup_markets, $this->data->marketplaceArr);
$lookup_markets = array_merge($this->data->marketplaceArr, $this->data->retailerArr);
$this->data->report_where = array('report_type' => $this->data->report_type, 'api_type' => $lookup_markets, 'time_frame' => $this->data->time_frame, 'date_from' => $this->data->date_from, 'date_to' => $this->data->date_to, 'cron_ids' => '', 'product_ids' => $this->data->products, 'merchants' => $this->data->merchants, 'store_id' => $this->store_id, 'competitor_store_id' => $this->data->competitor_store_id, 'violation' => true);
//get cronIds for 24 hour scatter
if ($this->data->time_frame == 24 || $this->data->flagDates24) {
$this->data->report_where['cron_ids'] = getLast24HoursCronIds($this->data->flagDates24 ? $this->data->date_from : '', '', $this->data->report_where['api_type']);
}
$response = $this->Violations->searchProductPricingViolations($this->data->report_where);
//log_message('info', '_prep_report violation bymerchant data: '. print_r($response,true));
$this->data->Data = $response;
$this->data->gData = $this->Chart->prepGoogleData($response, $this->data->report_where, $this->data->report_chart);
}
if ($this->data->show_comparison and isset($products_wo_comparisons)) {
// don't populate product fields with the compared
$this->data->products = $products_wo_comparisons;
}
$this->data->searchProducts = array();
for ($i = 0, $n = sizeof($this->data->products); $i < $n; $i++) {
$this->data->searchProducts[$this->data->products[$i]] = $this->data->productNames[$i];
}
//display data
$this->data->dateStartField = !$this->submit || $this->submit && $this->data->time_frame != '' ? 'Start' : $this->data->date_from;
$this->data->dateEndField = !$this->submit || $this->submit && $this->data->time_frame != '' ? 'Stop' : $this->data->date_to;
$this->data->time_frame = $this->submit ? $this->data->time_frame : '';
$this->data->submitted = (bool) $this->submit;
//
// Set retailer/marketplace information
//
$this->data->marketRetailer = array();
$this->data->retailersExist = array();
$this->data->marketplacesExist = array();
if ($this->data->report_chart === 'scatter') {
foreach ($this->data->Data as $prodId => $productData) {
for ($i = 0, $n = count($productData); $i < $n; $i++) {
$marketName = strtolower($productData[$i]['marketplace']);
if (!isset($this->data->marketRetailer[$marketName])) {
$this->data->marketRetailer[$marketName] = $this->Marketplace->is_retailer($marketName);
}
if ($this->data->marketRetailer[$marketName]) {
$this->data->retailersExist[$prodId] = true;
} else {
$this->data->marketplacesExist[$prodId] = true;
}
}
}
}
//
// Set up map between competitor product data and user product data
//
$this->data->comparison_data = array();
if ($this->data->show_comparison) {
$color_index = 0;
$this->data->color_index = array();
if (!empty($this->data->competitor_map)) {
//
// If this is a scatter chart we need to add the crawl id for all the data
//
if ($this->data->report_chart === 'scatter') {
foreach ($this->data->Data as $prodId => $productData) {
for ($i = 0, $n = count($productData); $i < $n; $i++) {
if (isset($productData[$i]['marketplace'])) {
$api_type = explode('.', $productData[$i]['marketplace']);
$api_type = $api_type[0];
$crawl = $this->Crawl->get_crawl_by_time($productData[$i]['timestamp'], $api_type, 'id');
$this->data->Data[$prodId][$i]['crawl_id'] = isset($crawl['id']) ? $crawl['id'] : false;
}
}
}
}
//
// Now loop through and set the comparison data
//
foreach ($products_w_comparisons as $prodId) {
$this->data->color_index[$prodId] = $color_index++;
//
// Check if this competitor product is associated with user product
//
$comparison_id = false;
if (isset($this->data->competitor_map[$prodId])) {
$comparison_id = $this->data->competitor_map[$prodId]['id'];
}
if ($comparison_id and isset($this->data->Data[$comparison_id])) {
$comparison_data = $this->data->Data[$comparison_id];
//
// Map values via productid->marketplace->crawltime
//
if (!empty($comparison_data)) {
if ($this->data->report_chart === 'line') {
foreach ($comparison_data as $market => $market_prod_data) {
for ($i = 0, $n = count($market_prod_data); $i < $n; $i++) {
if (isset($market_prod_data[$i]['dt'])) {
$this->data->comparison_data[$prodId][$market][$market_prod_data[$i]['dt']] = $market_prod_data[$i];
}
}
}
} elseif ($this->data->report_chart === 'scatter') {
for ($i = 0, $n = count($comparison_data); $i < $n; $i++) {
if (isset($comparison_data[$i]['marketplace'])) {
$market = strtolower($comparison_data[$i]['marketplace']);
$merchant_id = $comparison_data[$i]['merchant_id'];
$crawl_id = $comparison_data[$i]['crawl_id'];
$this->data->comparison_data[$prodId][$market][$crawl_id][$merchant_id] = $comparison_data[$i];
}
}
}
}
unset($this->data->Data[$comparison_id]);
}
}
}
}
//log_message('info', '_prep_report final data: '. print_r($this->data,true));
}
/**
*
* function myproducts_merchant_product_request
*
* @param <string> $marketplace
* @param <string> $merchant
* @param <string> $to
* @param <string> $from
*
*/
function myproducts_merchant_product_request($marketplace, $merchant, $product_id, $store_id, $to, $from, $time_frame = '24')
{
return array('api_type' => array($marketplace), 'merchants' => array($merchant), 'fromDate' => $from, 'toDate' => $to, 'productIds' => array($product_id), 'time_frame' => $time_frame, 'store_id' => $store_id, 'cron_ids' => getLast24HoursCronIds(strtotime($from), strtotime($to), $marketplace));
}
private function _prep_pricing_report()
{
$this->data->my = 'productpricing';
$this->data->report_chart = 'line';
//print_r($this->data); exit;
if ($this->data->time_frame == 24 || $this->data->flagDates24) {
$this->data->report_chart = 'scatter';
$this->data->request_info['cron_ids'] = getLast24HoursCronIds($this->data->flagDates24 ? $this->data->date_from : '', '', $this->data->request_info['api_type']);
$response = $this->Report->productPricingReport24($this->data->request_info);
$this->data->Data = $response['data'];
$this->data->gData = $this->Chart->prepGoogleData($response['data'], $this->data->request_info, $this->data->report_chart);
} else {
$this->data->Data = $this->Report->productPricingHistory($this->data->request_info);
$this->data->gData = $this->Chart->prepGoogleData($this->data->Data, $this->data->request_info, $this->data->report_chart);
}
}
function market($market, $prodId, $dateStart, $dateEnd = NULL, $report_type = NULL, $id = false)
{
$this->data->by_which = $report_type ? $report_type : 'bymarket';
$this->data->competitor_store_id = false;
if ($this->data->by_which === 'bycompetition') {
// this is a competitor's product
$this->data->competitor_product = $this->Product->get_by('id', $prodId);
$this->data->competitor_store_id = $this->data->competitor_product->store_id;
$this->data->proMerchants = getProductMerchant($this->data->competitor_store_id);
$lookup_id = $this->data->competitor_store_id;
} else {
$lookup_id = $this->store_id;
}
$searchProduct = $this->Product->getProductsById($lookup_id, $prodId);
if (!isset($searchProduct[0])) {
redirect('reports');
}
$this->layout = 'frontend_inner';
$this->data->time_frame = '24';
if (!$dateEnd) {
$this->data->flagDates24 = true;
}
$this->data->my = 'productpricing';
$this->data->productArr = getProductsDrp($this->store_id);
$this->data->proMerchants = getProductMerchant($this->store_id);
//$this->data->markertArr = getMarketArray();
$this->data->product_ids = array($prodId);
$this->data->searchProducts = array($prodId => $searchProduct[0]['title']);
$this->data->date_from = $dateStart;
$this->data->date_to = $dateEnd ? $dateEnd : $dateStart;
$this->data->submitted = true;
$this->data->all_markets = false;
$this->data->all_retailers = false;
if ($this->data->date_from !== 'Start' && $this->data->date_to !== 'Stop') {
$this->data->dateStartField = $this->data->date_from;
$this->data->dateEndField = $this->data->date_to;
$this->data->date_from = strtotime($this->data->date_from);
$this->data->date_to = !$dateEnd ? strtotime($this->data->date_to . ' 23:59:59') : strtotime($this->data->date_to);
$this->data->time_frame = '';
if ($this->data->date_from == $this->data->date_to) {
$this->data->flagDates24 = true;
$this->data->time_frame = '24';
}
} else {
$tF = getTimeFrame($this->data->time_frame);
$this->data->date_from = $tF['date_from'];
$this->data->date_to = $tF['date_to'];
}
//the saved report Id
if ($id) {
$this->data->report_id = $this->input->post('report_id');
}
if ($product_name = $this->input->post('product_name')) {
if (is_array($product_name)) {
for ($i = 0, $n = sizeof($product_name); $i < $n; $i++) {
$this->data->report_name .= $product_name[$i] . ' ';
}
$this->data->report_name .= 'Product Pricing';
}
$this->data->file_name = str_replace(' ', '_', $this->data->report_name . ' ' . date('Y-m-d'));
$this->data->productNames = $this->input->post('product_name');
}
$this->data->merchants = $this->input->post('merchants') ? $this->input->post('merchants') : array();
$this->data->markets = array(ucfirst($market));
$this->data->show_comparison = false;
$this->data->report_where = array('date_from' => $this->data->date_from, 'date_to' => $this->data->date_to, 'api_type' => $this->data->markets, 'product_ids' => $this->data->product_ids, 'merchants' => $this->data->merchants, 'time_frame' => $this->data->time_frame, 'store_id' => $this->store_id, 'competitor_store_id' => array($this->data->competitor_store_id), 'show_comparison' => $this->data->show_comparison);
//get cron_ids for 24 hour scatter
$this->data->report_chart = 'scatter';
if ($this->data->flagDates24) {
$this->data->report_where['cron_ids'] = getLast24HoursCronIds($this->data->date_from, '', $this->data->report_where['api_type']);
} else {
$this->data->report_where['cron_ids'] = getLast24HoursCronIds('', '', $this->data->report_where['api_type']);
}
$response = $this->Report->productPricingReport24($this->data->report_where);
$this->data->Data = $response['data'];
$this->data->gData = $this->Chart->prepGoogleData($response['data'], $this->data->report_where, $this->data->report_chart);
$this->data->submitted = true;
$this->session->set_userdata('report_where', $this->data->report_where);
$this->data->graphDataType = 'chart';
//
// Set retailer/marketplace information
//
$this->data->marketRetailer = array();
$this->data->retailersExist = array();
$this->data->marketplacesExist = array();
if ($this->data->report_chart === 'line') {
foreach ($this->data->Data as $prodId => $marketArr) {
foreach ($marketArr as $marketName => $productData) {
if (!isset($this->data->marketRetailer[$marketName])) {
$this->data->marketRetailer[$marketName] = $this->Marketplace->is_retailer($marketName);
}
if ($this->data->marketRetailer[$marketName]) {
$this->data->retailersExist[$prodId] = true;
} else {
$this->data->marketplacesExist[$prodId] = true;
}
}
}
} elseif ($this->data->report_chart === 'scatter') {
foreach ($this->data->Data as $prodId => $productData) {
for ($i = 0, $n = count($productData); $i < $n; $i++) {
$marketName = strtolower($productData[$i]['marketplace']);
if (!isset($this->data->marketRetailer[$marketName])) {
$this->data->marketRetailer[$marketName] = $this->Marketplace->is_retailer($marketName);
}
if ($this->data->marketRetailer[$marketName]) {
$this->data->retailersExist[$prodId] = true;
} else {
$this->data->marketplacesExist[$prodId] = true;
}
}
}
}
$this->_build_options_array($this->data->by_which);
//$this->data->headerHTML = $this->load->view("front/reports_new_header", $this->data, true);
}