private function _getDailyPriceAverage($tableName, $merchantJoin, $report_where, $i, $where, $productUPC, $lookup_store_id)
{
$averageQuery = "SELECT d.*, p.title, p.id\n\t\t\tFROM {$tableName} d\n\t\t\tLEFT JOIN products p ON d.upc = p.upc_code\n\t\t\t{$merchantJoin}\n\t\t\tWHERE d.date BETWEEN '" . date("Y-m-d", $report_where['date_from']) . "' AND '" . date("Y-m-d", $report_where['date_to']) . "'\n\t\t\tAND p.id=" . $report_where['product_ids'][$i] . $where;
$average = $this->db->query($averageQuery)->result();
$range['start'] = $report_where['date_from'];
$range['end'] = $report_where['date_to'];
if (count($average) > 0) {
foreach ($average as $row) {
$wholeSale = getPricePoint($productUPC, $lookup_store_id, 'wholesale_price', '', $range);
$retail = getPricePoint($productUPC, $lookup_store_id, 'retail_price', '', $range);
$map = getPricePoint($productUPC, $lookup_store_id, 'price_floor', '', $range);
$idxMarketPlace = strtolower($row->marketplace);
$this->finalData[$report_where['product_ids'][$i]][$idxMarketPlace][] = array('marketplace' => $row->marketplace, 'upc' => $row->upc, 'price' => floatval($row->price_total / $row->seller_total), 'wholesale' => $wholeSale, 'retail' => $retail, 'title' => $row->title, 'map' => $map, 'dt' => strtotime($row->date), 'timestamp' => $row->date, 'date' => (string) $row->date, 'prod_id' => $report_where['product_ids'][$i]);
}
}
}
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;
}
private function _retailer_lookup($api, $search_url, $upcs, $floor_price, $cron_log_id, $store_id)
{
//$search_url = "http://www.amazon.com";
//$api = 'amazon';
//echo "\n-------------------------------------------------------------------------------------\n";
//echo "_retailer_lookup(".print_r($upcs,true);//.",
//floor_price: ".print_r($floor_price,true).",
//cron_log_id: $cron_log_id,
//store_id: $store_id)";
//exit;
foreach ($upcs as $key => $upc) {
/*only one index, always*/
}
if (empty($upc)) {
log_message('error', "Missing UPC, {$api} " . print_r($upcs, true));
return;
}
$retailer_data = $this->_spider_search($upc, $api, $search_url);
//echo "retailer_data: \n";
//print_r($retailer_data);
//exit;
if (empty($retailer_data)) {
$this->updateUPCFlag($upc, $api, '0');
log_message('info', "upc {$upc} not found in {$api}");
return;
}
$price_floor = getPricePoint($upc, $store_id, 'price_floor', time());
// MAP
$this->myDB = new mydb();
// only one screen_shot of each product page
$uniquArr = array();
foreach ($retailer_data as $item) {
log_message('info', 'item: ' . print_r($item, true));
if (empty($item)) {
log_message('info', "Missing result, {$api} " . print_r($upcs, true));
continue;
}
$this->stats[$api]['data_found']++;
$crawled_product = array();
$sku = $item['product.sku'];
if (empty($sku)) {
$sku = "No product SKU found.";
}
$crawled_product['ap'] = $price_floor;
$crawled_product['t'] = $item['product.name'];
$crawled_product['l'] = $item['product.url'];
$crawled_product['ar'] = $api;
$crawled_product['il'] = !empty($item['product.image_url']) ? $item['product.image_url'] : '';
// price offered
$crawled_product['mpo'] = $this->parseDollarAmount($item['product.price_listed']);
if (empty($crawled_product['mpo'])) {
log_message('error', 'Failed parse ' . print_r($item, true));
continue;
}
if (!empty($item['product.shipping'])) {
$crawled_product['msp'] = $this->parseDollarAmount($item['product.shipping']);
}
if (!empty($item['seller.logo'])) {
$crawled_product['mil'] = $item['seller.logo'];
}
// TODO: make the seller_id or merchant_id into mandatory data. Merchant name changes all the time.
// TODO: de-duplicate merchant table
$seller_name = !empty($item['seller.name']) ? trim($item['seller.name']) : "No name found.";
$sellerUrl = trim($item['seller.aboutus']);
$seller_id = !empty($item['seller.seller_id']) ? $item['seller.seller_id'] : '';
$real_name = $seller_name;
$merchant = $this->getMerchantNameForKey($seller_name, $upc, $api, $real_name, $seller_id, $sellerUrl);
$merchant_name = $merchant['merchant_name'];
if (empty($merchant_name) or strlen($merchant_name) < 2) {
log_message('error', "Missing merchant_name " . print_r($item, true));
continue;
}
$crowlMerchantNameID = $merchant['id'];
// TODO: analyze this. what's it used for? why is it updated for every item?
$stQuery = "UPDATE cron_log set google_count=(google_count+1), last_UPC='{$upc}' WHERE id= '{$cron_log_id}' ";
$this->myDB->simpleQuery($stQuery);
// TODO: change the hashkey to use merchant_id#upc#storeId
$hashKey = $merchant_name . '#' . $upc;
$crawled_product['um'] = $hashKey;
$crawled_product['dt'] = time();
$crawled_product['pid'] = $this->Products->get_product_id_from_upc($upc, $store_id);
$crawled_product['upc'] = $upc;
$crawled_product['mid'] = $crowlMerchantNameID;
$crawled_product['mu'] = $sellerUrl;
$crawled_product['rp'] = getPricePoint($upc, $store_id, 'retail_price');
$crawled_product['wp'] = getPricePoint($upc, $store_id, 'wholesale_price');
$this->stats[$api]['price_found']++;
$this->insertUPCMerchant($merchant_name, $upc, $api, $crawled_product['mpo']);
// Should we ignore violations that are less than a dollar wrong?
$dataVio = $this->Products->get_products_by_floor($upc, $crawled_product['mpo'], $store_id);
//echo "dataVio: Products->get_products_by_floor(upc_code: $upc, violatedPrice: {$crawled_product['mpo']}, store_id:$store_id)\n";
//print_r($dataVio);
//exit;
// Screen Shots
$ss = '';
$llr = $this->ProductsTrends->get_latest_by_hashkey($hashKey)->result_object();
$lastCrawlPrice = (double) 0;
if (!empty($llr->um)) {
$lastCrawlPrice = isset($llr->mpo) ? (double) $llr->mpo : (double) 0;
// Merchant Price Offered
$ss = $llr->ss;
}
$violationFlag = false;
if ($dataVio) {
$violationFlag = true;
$this->updateViolationSummary($dataVio);
// take screenshot only if unique today, price has changed, and product is in violation
$new_ss = date('Ymd', $crawled_product['dt']) . '/' . md5($crawled_product['l']) . '.png';
if ($ss != $new_ss && $lastCrawlPrice != $crawled_product['mpo']) {
$ss = $new_ss;
if (!isset($uniquArr[$ss])) {
$uniquArr[$ss] = true;
$this->addScreenShot($crawled_product['l'], $ss, false, $crawled_product['mpo']);
}
}
}
$crawled_product['ss'] = $ss;
// store the nosql data
$this->ProductsTrends->insertData($crawled_product, $api);
// update price violators for all products, if MPO data exists
$this->Violator->updatePriceViolator($crowlMerchantNameID, $upc, $violationFlag, $crawled_product['dt']);
//echo "updatePriceViolator\(crowlMerchantNameID:$crowlMerchantNameID, upc_code:$upc, ".(int)$violationFlag.", dt:{$crawled_product['dt']} \)\n";
//exit;
// do not set active=1 if existing row has active=0, the customer does not want to crawl them
$where = "crowl_merchant_name_id = {$crowlMerchantNameID} and store_id = {$store_id}";
$rrr = $this->myDB->getByTableName($this->_table_violator_notifications, $where);
if (empty($rrr[0]) or $rrr[0]['active'] == 1) {
// assure violation notification record exists and is active
$arrViolation = array('store_id' => $store_id, 'crowl_merchant_name_id' => $crowlMerchantNameID, 'active' => 1);
$this->myDB->replace($this->_table_violator_notifications, $arrViolation, $where);
}
//echo "\ncrawled_products: \n";
//print_r($crawled_product);
//exit;
}
//end iterator on returned retailer_data
$this->updateUPCFlag($upc, $api, '1');
$this->myDB->close();
}
/**
* Retrieve products by upc from products table where floor price is greater than specified value
*
* @param String $upc
* @param float $min_floor
*
* @return array
*/
public function get_products_by_floor($upc, $min_floor, $store_id)
{
$ret = false;
$map = getPricePoint($upc, $store_id, 'price_floor', time());
echo "MAP: {$map}\n";
if ($map > $min_floor) {
$result = $this->db->select('p.id, p.store_id')->where('p.upc_code', $upc)->where_in('p.store_id', getStoreIdList($store_id))->get($this->_table_products . ' p');
//echo "MAP result: ";
//print_r($result->result_array());
if ($result->num_rows() == 1) {
$ret = $result->result_array();
}
}
return $ret;
}
function loadPricePoints(array &$products, $ts)
{
if (!empty($products)) {
for ($i = 0, $n = count($products); $i < $n; $i++) {
$map = getPricePoint($products[$i]['upc_code'], $products[$i]['store_id'], 'price_floor', $ts);
$retail = getPricePoint($products[$i]['upc_code'], $products[$i]['store_id'], 'retail_price', $ts);
$wholesale = getPricePoint($products[$i]['upc_code'], $products[$i]['store_id'], 'wholesale_price', $ts);
$product[$i]['price_floor'] = $map;
$product[$i]['retail_price'] = $retail;
$product[$i]['wholesale_price'] = $wholesale;
}
}
}
/**
* This handles the export functionality just for a store's product catalog.
*
* @author unknown
* @param string $format
*/
public function export_catalog($format)
{
$catalog_columns = $this->Store->get_columns_by_store($this->store_id);
$column_order = $column_names = array();
for ($i = 0, $n = count($catalog_columns); $i < $n; $i++) {
$column_order[] = $catalog_columns[$i]->db_name;
$column_names[] = $catalog_columns[$i]->display_name;
}
$csv_data = array($column_names);
// get all non-archived products
$products = $this->Product->get_products_for_store($this->store_id);
//$products = $this->Product->getByStore($this->store_id);
for ($i = 0, $n = count($products); $i < $n; $i++) {
$row = array();
foreach ($column_order as $column) {
switch ($column) {
case 'wholesale_price':
$price = getPricePoint($products[$i]['upc_code'], $this->store_id, $column);
$price = empty($price) ? '0.00' : number_format($price, 2, '.', '');
$row[] = $price;
break;
case 'retail_price':
$price = getPricePoint($products[$i]['upc_code'], $this->store_id, $column);
$price = empty($price) ? '0.00' : number_format($price, 2, '.', '');
$row[] = $price;
break;
case 'price_floor':
$price = getPricePoint($products[$i]['upc_code'], $this->store_id, $column);
$price = empty($price) ? '0.00' : number_format($price, 2, '.', '');
$row[] = $price;
break;
default:
$row[] = isset($products[$i][$column]) ? $products[$i][$column] : '';
}
}
$csv_data[] = $row;
}
$name = 'catalog';
/*
if ($this->store_id !== 'all')
$name = str_replace(' ', '_', $this->store_data['store_name']);
*/
// get current store being viewed
$store = $this->Store->get_store_by_id_array($this->store_id);
$name = str_replace(' ', '_', $store['store_name']);
$name .= '_' . date('Y-m-d');
if ($format == 'excel') {
createCSV($csv_data, $name);
} else {
createPDF($csv_data, $name);
}
exit;
}
