function productPricingHistory($report_where = array())
{
$api_typeArr = array();
$where = $merchantJoin = '';
$whereVals = requestInfoWhereValues($report_where, $api_typeArr);
if (isset($whereVals['marketplaces'])) {
$where .= ' AND d.marketplace ' . $whereVals['marketplaces'];
}
if (isset($whereVals['merchants'])) {
$merchantJoin = " LEFT JOIN crowl_merchant_name_new cm ON cm.marketplace=d.marketplace ";
$where .= ' AND cm.merchant_name ' . $whereVals['merchants'];
}
if (isset($whereVals['store'])) {
$where .= ' AND products.store_id ' . $whereVals['store'];
}
for ($i = 0; $i < sizeof($report_where['product_ids']); $i++) {
$curProd = getProductUPCByID($report_where['product_ids'][$i]);
$productUPC = $curProd['upc_code'];
$lookup_store_id = $curProd['store_id'];
// TODO: delete this line eventually
if ($report_where['date_from'] < strtotime('2014-04-01')) {
$report_where['date_from'] = strtotime('2014-04-01');
}
// query two times: once in the big archive table ...
$this->_getDailyPriceAverage($this->_dynamo_daily_price_average_archive, $merchantJoin, $report_where, $i, $where, $productUPC, $lookup_store_id);
// ... and query a 2nd time in the little daily table
if (date('Y-m-d', $report_where['date_to']) == date('Y-m-d', time())) {
$this->_getDailyPriceAverage($this->_dynamo_daily_price_average, $merchantJoin, $report_where, $i, $where, $productUPC, $lookup_store_id);
}
}
return $this->finalData;
}
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;
}
