public function admin_getNetsuiteData()
{
$result = [];
$result['meta'] = [];
$warehouse_names = [];
$result['meta']['market'] = $this->Market->find('list');
$result['meta']['status'] = $this->OrderShipmentStatus->find('list');
//Hash::extract($this->OrderShipmentStatus->find('all'), '{n}.OrderShipmentStatus');
$result['meta']['status'][9] = '*Order Closed';
$result['meta']['status'][10] = '*In snap';
$result['meta']['status'][11] = '*Backorder reset';
$result['meta']['status'][12] = '*Has replacement';
$result['meta']['status'][13] = '*Prekit reset';
$result['meta']['status'][0] = 'No Status';
$result['total_open'] = $this->Order->find('count', ['conditions' => ['order_status_id' => [Order::STATUS_ENTERED, Order::STATUS_PROCESSING]]]);
$result['not_in_ns'] = $this->Order->notInNetSuite(true);
$result['no_ship_method'] = $this->Order->noShipmentMethod(true);
$result['compliance_hold'] = $this->Order->complianceHold(true);
$result['ns_error_import'] = $this->Order->find('count', ['conditions' => ['order_status_id' => Order::STATUS_ENTERED, 'secondary_order_status_id' => Order::SECONDARY_NETSUITE_ERROR_SUBMIT]]);
$result['ns_error_stale'] = $this->Order->staleNetSuite(false, true);
// $result['ns_backorder'] = $this->Order->staleNetSuite(true,true);
$result['ns_error_other'] = $this->Order->find('count', ['conditions' => ['order_status_id' => Order::STATUS_ENTERED, 'secondary_order_status_id' => Order::SECONDARY_NETSUITE_ERROR_OTHER]]);
$in_snap = $this->Order->find('all', ['fields' => ['order_market_id', 'count(id) as qty'], 'conditions' => ['order_status_id' => Order::STATUS_PROCESSING, 'order_market_id > 0'], 'group' => ['order_market_id']]);
$total = 0;
foreach ($in_snap as $data_set) {
$total += $data_set[0]['qty'];
$wms = WarehouseUtil::nameByMarket($data_set['Order']['order_market_id']);
$warehouse_names[$data_set['Order']['order_market_id']] = $wms;
array_key_exists($wms, $result['in_snap']) ? $result['in_snap'][$wms] += $data_set[0]['qty'] : ($result['in_snap'][$wms] = $data_set[0]['qty']);
}
$result['in_snap']['total'] = $total;
unset($total);
unset($data_set);
unset($in_snap);
$in_snap_stale = $this->Order->find('all', ['fields' => ['order_market_id', 'count(id) as qty'], 'conditions' => ['order_status_id' => Order::STATUS_PROCESSING, 'secondary_order_status_id' => Order::SECONDARY_NETSUITE_ERROR_RETURN], 'group' => ['order_market_id']]);
$total = 0;
foreach ($in_snap_stale as $data_set) {
$total += $data_set[0]['qty'];
$wms = array_key_exists($data_set['Order']['order_market_id'], $warehouse_names) ? $warehouse_names[$data_set['Order']['order_market_id']] : WarehouseUtil::nameByMarket($data_set['Order']['order_market_id']);
array_key_exists($wms, $result['in_snap_stale']) ? $result['in_snap_stale'][$wms] += $data_set[0]['qty'] : ($result['in_snap_stale'][$wms] = $data_set[0]['qty']);
}
$result['in_snap_stale']['total'] = $total;
unset($total);
unset($data_set);
unset($in_snap);
// Days
$days = 3;
$result['sla_lehi'] = $this->Order->outOfWindow(1, true, $days);
$result['sla_netherlands'] = $this->Order->outOfWindow(2, true, $days);
$result['sla_mexico'] = $this->Order->outOfWindow(3, true, $days);
$result['sla_total'] = array_sum([$result['sla_lehi'], $result['sla_netherlands'], $result['sla_mexico']]);
$days = 5;
$result['sla_lehi_5'] = $this->Order->outOfWindow(1, true, $days);
$result['sla_netherlands_5'] = $this->Order->outOfWindow(2, true, $days);
$result['sla_mexico_5'] = $this->Order->outOfWindow(3, true, $days);
$result['sla_total_5'] = array_sum([$result['sla_lehi_5'], $result['sla_netherlands_5'], $result['sla_mexico_5']]);
$days = 10;
$result['sla_lehi_10'] = $this->Order->outOfWindow(1, true, $days);
$result['sla_netherlands_10'] = $this->Order->outOfWindow(2, true, $days);
$result['sla_mexico_10'] = $this->Order->outOfWindow(3, true, $days);
$result['sla_total_10'] = array_sum([$result['sla_lehi_10'], $result['sla_netherlands_10'], $result['sla_mexico_10']]);
$shipped = $this->Order->find('all', ['fields' => ['order_market_id', 'count(id) as qty'], 'conditions' => ['order_status_id' => [Order::STATUS_SHIPPED], 'date_completed >= DATE_SUB( NOW(), INTERVAL 3 DAY)'], 'group' => ['order_market_id']]);
foreach ($shipped as $data_set) {
$wms = array_key_exists($data_set['Order']['order_market_id'], $warehouse_names) ? $warehouse_names[$data_set['Order']['order_market_id']] : WarehouseUtil::nameByMarket($data_set['Order']['order_market_id']);
array_key_exists($wms, $result['shipped']) ? $result['shipped'][$wms] += (int) $data_set[0]['qty'] : ($result['shipped'][$wms] = (int) $data_set[0]['qty']);
}
unset($data_set);
unset($in_snap);
// $result['ns_import_errors'] = $this->Order->find('all', [
// 'conditions' => [
// 'secondary_order_status_id' => Order::SECONDARY_NETSUITE_ERROR_SUBMIT,
// ],
// 'contain' => ['OrderShipment']
// ]);
//
// foreach($result['ns_import_errors'] as &$error)
// {
// // check fail points
//
// // char encoding
// $input_fields = [
// "first_name" => $error['OrderShipment'][0]['first_name'],
// "last_name" => $error['OrderShipment'][0]['last_name'],
// "address1" => $error['OrderShipment'][0]['address1'],
// "address2" => $error['OrderShipment'][0]['address2'],
// "address3" => $error['OrderShipment'][0]['address3'],
// "city" => $error['OrderShipment'][0]['city'],
// "phone" => $error['OrderShipment'][0]['phone'],
// "postal_code" => $error['OrderShipment'][0]['postal_code'],
// "email_address" => $error['OrderShipment'][0]['email_address']
// ];
// foreach($input_fields as $field => $value)
// {
// if(mb_detect_encoding($value) == 'UTF-8')
// {
// $error['errors'][] = "Encoding: " . $field . " - " . $value;
// }
// }
// // address field length
// foreach($input_fields as $field => $value)
// {
// if(strlen($value) > 30)
// {
// $error['errors'][] = "Address: " . $field . " - " . $value;
// }
// }
// $error['market'] = $result['meta']['market'][$error['Order']['order_market_id']];
// }
//
// $result['snap_export_errors'] = $this->Order->find('all', [
// 'conditions' => [
// 'secondary_order_status_id' => Order::SECONDARY_NETSUITE_ERROR_RETURN,
// ],
// 'fields' => [
// 'date(min(date_completed)) oldest, count(id) as qty, order_market_id'
// ],
// 'group' => 'order_market_id'
//
// ]);
//
// $result['other_errors'] = $this->Order->find('all', [
// 'conditions' => [
// 'secondary_order_status_id' => Order::SECONDARY_NETSUITE_ERROR_OTHER,
// ]
// ]);
//
// $result['backorder_items'] = $this->Order->query("
// SELECT count(oi.id) count, i.sku, i.name
// FROM order_items oi
// LEFT JOIN items i ON oi.item_id = i.id
// LEFT JOIN order_shipment_items osi ON oi.id = osi.order_item_id
// LEFT JOIN order_customers oc ON oi.order_customer_id = oc.id
// LEFT JOIN orders o ON oc.order_id = o.id
// WHERE i.id IN (
// SELECT id as item_id
// FROM items
// )
// AND (
// osi.order_shipment_id = 0
// OR
// (
// osi.order_shipment_id IS NULL
// AND
// osi.id IS NOT NULL
// )
// )
// AND order_status_id in (3,8)
// GROUP BY i.id;
// ");
//
// $result['backorder_orders'] = $this->Order->query("
// SELECT *
// FROM order_items oi
// LEFT JOIN items i ON oi.item_id = i.id
// LEFT JOIN order_shipment_items osi ON oi.id = osi.order_item_id
// LEFT JOIN order_customers oc ON oi.order_customer_id = oc.id
// LEFT JOIN orders o ON oc.order_id = o.id
// WHERE i.id IN (
// SELECT id as item_id
// FROM items
// )
// AND (
// osi.order_shipment_id = 0
// OR
// (
// osi.order_shipment_id IS NULL
// AND
// osi.id IS NOT NULL
// )
// )
// AND order_status_id in (3,8)
// ;
// ");
//
// $backorder_ids = [];
// foreach($result['backorder_orders'] as $backorder)
// $backorder_ids[] = $backorder['o']['id'];
//
// // orders not in snap
// $result['snap_import_errors'] = $this->Order->find('all', [
// 'conditions' => [
// 'order_status_id' => [
// Order::STATUS_ENTERED,
// Order::STATUS_PROCESSING
// ],
// 'secondary_order_status_id' => Order::SECONDARY_NETSUITE_ERROR_SNAP,
// 'NOT' => [
// 'id' => $backorder_ids
// ]
// ],
// 'fields' => [
// 'date(min(date_completed)) oldest, count(id) as qty, order_market_id'
// ],
// 'group' => 'order_market_id'
// ]);
//
$this->sendSuccess($result);
}
