function loadAMVDsendListWithSSCC($spid, $destination, $ordertype_filter, $datefilter, $box_switch_time) { $box_switch_time_ts = array(); if (is_array($box_switch_time)) { foreach ($box_switch_time as $bst) { $box_switch_time_ts[] = strtotime($datefilter . ' ' . trim($bst) . ':00'); } } $q = "SELECT jo.jng_sp_id, jo.order_id, jo.order_date, jo.customer_billing_firstname, jo.customer_billing_lastname, jo.customer_shipping_id, jo.customer_shipping_address, jo.customer_shipping_address2"; $q .= ",jo.customer_shipping_postcode, jo.customer_shipping_city, jo.customer_shipping_country, jo.expected_delivery_date_from, jo.expected_delivery_date_until, jo.idcode_ship"; $q .= ",joi.jng_sp_orders_id, joi.jng_sp_orders_items_id, joi.order_quantity, joi.products_ean, joi.article_reference_number, joi.billing_text, joi.price, joi.price_received"; $q .= ", MAX(joish.status_date) AS status_date"; $q .= " FROM jng_sp_orders_items joi"; $q .= " INNER JOIN jng_sp_orders jo ON jo.jng_sp_orders_id=joi.jng_sp_orders_id"; $q .= " LEFT JOIN jng_sp_orders_items_status_history joish ON joish.jng_sp_orders_items_id=joi.jng_sp_orders_items_id AND joish.status=9"; $q .= " WHERE joi.status IN (9,11) AND jo.jng_sp_id={$spid}"; $q .= " AND joish.status_date >= '{$datefilter} 00:00:00' AND joish.status_date <= '{$datefilter} 23:59:59'"; $q .= " AND jo.customer_shipping_id = '{$destination}'"; $q .= " AND jo.customer_billing_lastname LIKE '{$ordertype_filter}'"; $q .= " GROUP BY joi.jng_sp_orders_items_id"; $q .= " ORDER BY status_date ASC"; $r = tep_db_query($q); $asns = array(); $orders = array(); $orders_data = array(); $orders_products = array(); $orders_sscc = array(); while ($row = tep_db_fetch_array($r)) { $oid = $row['order_id']; if (!in_array($oid, $orders)) { $orders[] = $oid; $orders_data[$oid] = array('jng_sp_id' => $row['jng_sp_id'], 'order_date' => $row['order_date'], 'customer_billing_name' => $row['customer_billing_firstname'] . ' ' . $row['customer_billing_lastname'], 'customer_shipping_id' => $row['customer_shipping_id'], 'customer_shipping_address' => $row['customer_shipping_address'], 'customer_shipping_address2' => $row['customer_shipping_address2'], 'customer_shipping_postcode' => $row['customer_shipping_postcode'], 'customer_shipping_city' => $row['customer_shipping_city'], 'customer_shipping_country' => $row['customer_shipping_country'], 'expected_delivery_date_from' => $row['expected_delivery_date_from'], 'expected_delivery_date_until' => $row['expected_delivery_date_until']); $orders_products[$oid] = array(); } //$orders_products[$oid][$row['products_ean']] = $row; if (!isset($orders_products[$oid][$row['products_ean']])) { $orders_products[$oid][$row['products_ean']] = array('jng_sp_orders_id' => array(), 'jng_sp_orders_items_id' => array(), 'order_quantity' => 0, 'products_ean' => $row['products_ean'], 'asin' => $row['article_reference_number'], 'billing_text' => $row['billing_text'], 'price_uvp' => $row['price'], 'price_cost' => $row['price'] - $row['price_received']); } $box_counter = 1; if (is_array($box_switch_time_ts) && count($box_switch_time_ts) > 0) { foreach ($box_switch_time_ts as $bst) { if (strtotime($row['status_date']) < $bst) { break; } else { $box_counter++; } } } $sscc = generateAmazonSSCCNumber($datefilter, $destination, $row['customer_billing_lastname'], $box_counter); if ($row['idcode_ship'] != '' && !isset($asns[$sscc])) { $asns[$sscc] = $row['idcode_ship']; } $orders_products[$oid][$row['products_ean']]['jng_sp_orders_id'][] = $row['jng_sp_orders_id']; $orders_products[$oid][$row['products_ean']]['jng_sp_orders_items_id'][] = $row['jng_sp_orders_items_id']; $orders_products[$oid][$row['products_ean']]['order_quantity'] += $row['order_quantity']; $orders_sscc[$sscc][$oid][$row['products_ean']] += $row['order_quantity']; } return array('asns' => $asns, 'orders' => $orders, 'orders_data' => $orders_data, 'orders_products' => $orders_products, 'orders_sscc' => $orders_sscc); }
$total_po = count($polist); $result = '<strong>' . $total_po . '</strong> PO found for' . ' destination <strong>' . $dest . '</strong> sending date <strong>' . $send_date . '</strong>'; if ($total_po > 0) { $result .= '<br /><br /><strong>Copy paste below PO#' . ' to AVC:</strong><br />' . implode("<br />", $polist) . '<br /><br />In AVC, go to next step'; } echo utf8_encode($result); exit; } elseif ($_POST['me_action'] == 'SSCC-GENERATE-NUMBERS') { $dest = tep_db_prepare_input($_POST['destination']); $order_type = tep_db_prepare_input($_POST['order_type']); $send_date = tep_db_prepare_input($_POST['send_date']); $box_total = tep_db_prepare_input($_POST['box_total']); $datefilter_sql = generateMysqlDateFromGerman($send_date); $sscc_nr = array(); for ($bc = 1; $bc <= $box_total; $bc++) { $sscc_nr[] = generateAmazonSSCCNumber($send_date, $dest, $order_type, $bc); } $result = implode("<br />", $sscc_nr) . '<br /><br />In AVC, go to next step'; echo utf8_encode($result); exit; } elseif ($_POST['me_action'] == 'SSCC-GENERATE-TOTAL-BOX') { $list_gap = 'style="margin-top:8px;"'; $spid = tep_db_prepare_input($_POST['spid']); $destination = tep_db_prepare_input($_POST['destination']); $order_type = tep_db_prepare_input($_POST['order_type']); $send_date = tep_db_prepare_input($_POST['send_date']); $datefilter_sql = generateMysqlDateFromGerman($send_date); $box_switch_time = array(); $box_number = array(); $amvdsl = loadAMVDsendListWithSSCC($spid, $destination, $order_type, $datefilter_sql, $box_switch_time); $polist = $amvdsl['orders'];