function conditionPrepaymentIsPaid($detail, $is_sp = true, $return_sql = false) { /* ----------------------------------------------------------------------------------------- * !!!!!! PLEASE UPDATE ON BOTH CONDITION IF THERES ANY CHANGES ON LOGIC !!!!!! * rule1 : all preauth-uncaptured that balance indicates overpaid, paid, and small amount * rule2 : all auth-submitted, preauth-captured that status is overpaid, paid, or complete * rule3 : all auth-submitted, preauth-captured that payment_status is paid ------------------------------------------------------------------------------------------- */ //TODO : CHANGED BY USING payment_status = 1 (FOR NOW STILL USE 3 rules, PLEASE MAKE SURE FIRST FOR ALL ALLOWED SMALL PAYMENT ALSO APPLIED FOR CHANGE payment_status TO 1 if ($return_sql) { use_class('payone_invoice'); $cond = "("; $cond .= $is_sp ? "UPPER(jo.payment_method) = '" . strtoupper(PAYMENT_METHOD_PREPAYMENT) . "'" : "UPPER(o.payment_method) = '" . strtoupper(PAYMENT_METHOD_BANK_TRANSFER) . "'"; $cond .= " AND ("; //rule1 $cond .= " ( pi.is_captured IN ('0','2') AND pi.invoice_amount_paid > 0 AND (" . payone_invoice::calcBalance('', true) . "*100 <= " . PAYONE_MAX_RETURNED_OPEN_PAYMENT . ") )"; $cond .= " OR"; //rule2 $cond .= " ( (pi.is_captured IS NULL OR pi.is_captured NOT IN ('0','2')) AND pi.invoice_complete_status IN ('P','O','1') )"; $cond .= " OR"; //rule3 $cond .= $is_sp ? " ( jo.payment_status = 1 )" : " ( o.paid_status = '1' )"; $cond .= "))"; } else { if ($is_sp) { $is_vorkasse = strtoupper($detail['payment_method']) == strtoupper(PAYMENT_METHOD_PREPAYMENT); $oid = $detail['jng_sp_orders_id']; $otype = 'S'; } else { $is_vorkasse = strtoupper($detail['payment_method']) == strtoupper(PAYMENT_METHOD_BANK_TRANSFER); $oid = $detail['orders_id']; $detail['payment_status'] = $detail['paid_status']; $otype = 'J'; } if (!$is_vorkasse) { return; } if (isset($detail['invoice_current_balance'])) { //PLEASE CONSIDER ADD "invoice_current_balance" AS SELECTION FIELD ON QUERY TO POSSIBLE USED THIS $current_balance = $detail['invoice_current_balance']; } else { use_class('payone_invoice'); $inv = new payone_invoice("invoice_order_id", "{$oid}", "invoice_order_type='{$otype}'"); $current_balance = $inv->getBalance(); } $is_captured = $detail['is_captured']; /* disabled $rule1, we also set as Paid / payment_status = 1 for vorkasse with small payment <= PAYONE_MAX_RETURNED_OPEN_PAYMENT * so it would be handled by rule3 $rule1 = (in_array($is_captured, array('0', '2')) && $detail['invoice_amount_paid']>0 && $current_balance<=PAYONE_MAX_RETURNED_OPEN_PAYMENT); */ $rule2 = (!in_array($is_captured, array('0', '2')) || is_null($is_captured) || $is_captured == '') && in_array($detail['invoice_complete_status'], array('P', 'O', '1')); $rule3 = $detail['payment_status'] == 1; //$cond = $is_vorkasse && ($rule1 || $rule2 || $rule3); $cond = $is_vorkasse && ($rule2 || $rule3); //var_dump("$is_vorkasse | $rule1 | $rule2 | $cond");die(); } return $cond; }
/** * Get Payment Summary * @param String $invoice_complete_status * @param String $filter * @return Array (payment_method => saldo) */ public static function getPaymentSummaryData($invoice_complete_status, $filter = '', $order_by = 'invoice_date ASC') { use_class('payone_invoice'); $q = "SELECT " . "pi.invoice_date, pi.invoice_due_date, pi.payone_txid" . ", pi.invoice_order_type, pi.invoice_order_id" . "," . payone_invoice::calcBalance(null, true) . " AS amount_saldo" . ", IF(pi.invoice_order_type = 'S', jo.payment_method, o.payment_method ) AS payment_method" . ", IF(pi.invoice_order_type = 'S', jo.order_id, o.orders_no ) AS order_no" . ", IF(pi.invoice_order_type = 'S', CONCAT(jo.customer_billing_firstname, ' ', jo.customer_billing_lastname), o.customers_name ) AS customers_name"; $q .= " FROM payone_invoice pi"; $q .= " LEFT JOIN jng_sp_orders jo " . "ON jo.jng_sp_orders_id = pi.invoice_order_id AND pi.invoice_order_type = 'S'"; $q .= " LEFT JOIN orders o " . "ON o.orders_id = pi.invoice_order_id AND pi.invoice_order_type = 'J'"; $q .= " WHERE pi.invoice_complete_status = '{$invoice_complete_status}'"; if ($filter != '') { $q .= " AND {$filter}"; } $q .= " ORDER BY {$order_by}"; $r = tep_db_query($q); $result = array(); while ($row = tep_db_fetch_array($r)) { $result[] = $row; } return $result; }
} } else { echo "No Overpayment Refund ready to sent to payone \n"; } // 2) RETURN OPEN PAYMENT < 7.01 EUR (include MAHNGEBÜHREN) PROCESS---------------------------------------------------- $counter_small_amount = 0; $counter_returns_no_txid = 0; $base_small_amount = PAYONE_MAX_RETURNED_OPEN_PAYMENT; //700 => 7 EUR //CHANGED THIS filters TO BELOW FOR CLOSE ALL SMALL OPEN AMOUNT THAT POSSIBLE FOUND ON 'U' OR 'A' //$filter_returns = "("; //$filter_returns .= " (invoice_complete_status = 'U' AND ROUND(".(payone_invoice::calcBalance('', true))."*100)<=$base_small_amount)"; //$filter_returns .= " OR "; //$filter_returns .= " (invoice_complete_status = 'A' AND invoice_amount_fines>0 AND invoice_amount_fines*100 = ROUND(".(payone_invoice::calcBalance('', true))."*100))"; //$filter_returns .= ")"; $filter_returns .= " (invoice_complete_status IN ('A', 'U') AND ROUND(" . payone_invoice::calcBalance('', true) . "*100)<={$base_small_amount})"; $filter_returns .= " AND {$filter_only_auth_or_captured_preauth}"; $sets_of_underpaid = $class_payone->retrieveList($filter_returns); //$sets_of_underpaid = $class_payone->retrieveList("invoice_complete_status IN ('X')"); if (count($sets_of_underpaid) > 0) { $successA = array(); $successU = array(); $failedA = array(); $failedU = array(); foreach ($sets_of_underpaid as $sa) { $is_small_amount = false; if ($sa['payone_txid'] != '') { $invoice_id = $sa['invoice_id']; $order_id = $sa['invoice_order_id']; $invoice_complete_status = $sa['invoice_complete_status']; $nextsequencenumber = intval($sa['sequencenumber']) + 1;
private function queryOrdersItemsDetailSP($orders_items_id) { $return_status = in_array($this->filters_status, array('16', '17', '18')); $query = "SELECT CONCAT(jo.customer_billing_firstname,' ',jo.customer_billing_lastname) AS customer_name" . ", jo.cod_costs, jo.order_id, jo.order_date, jo.service_code, jo.customers_order_count" . ", jo.payment_method, jo.payment_status, joi.*, do.depot_orders_id" . ", do.status AS depot_orders_status, do.prod_status AS depot_orders_prod_status" . ", MAX(joih.status_date) AS status_date, MAX(psh.status_date) AS status_date_prod" . ", jcust.jng_sp_customers_id, jscs.list_type, sp.jng_sp_id, sp.package_prefix" . ", p.products_image, p.products_brand_id, p.material_expenses AS current_mat_exp" . ", ptc.categories_id, pi.invoice_complete_status" . ", " . payone_invoice::calcBalance('', true) . " AS invoice_current_balance" . ", pi.invoice_amount_paid, pi.is_captured, MAX(ope.last_sent_date) AS last_sent_date" . ", MAX(ope.sent_count) AS sent_count, pl.sold_monthly_1" . ", IFNULL(joi.leadtime_start_date, jo.order_date) AS date_for_sorting"; if ($return_status) { $query .= ", MIN(joir.return_time) AS return_time, SUM(joir.return_quantity) AS return_quantity"; } $query .= " FROM jng_sp_orders jo"; $query .= " LEFT JOIN jng_sp sp ON sp.jng_sp_id = jo.jng_sp_id"; $query .= " LEFT JOIN jng_sp_customers jcust " . " ON jcust.jng_sp_id = jo.jng_sp_id AND jcust.customer_id = jo.customer_billing_id"; $query .= " LEFT JOIN jng_sp_customers_special jscs " . " ON jscs.jng_sp_customers_id = jcust.jng_sp_customers_id"; $query .= " LEFT JOIN jng_sp_orders_items joi ON joi.jng_sp_orders_id = jo.jng_sp_orders_id"; $query .= " LEFT JOIN depot_orders do " . " ON do.trans_type = 'SP' AND do.trans_id = joi.jng_sp_orders_items_id"; $query .= " LEFT JOIN jng_sp_orders_items_status_history joih " . " ON joih.jng_sp_orders_items_id = joi.jng_sp_orders_items_id AND joih.status = joi.status"; $query .= " LEFT JOIN production_status_history psh " . " ON psh.type = 'SP' AND psh.orders_items_id = joi.jng_sp_orders_items_id " . " AND psh.status = joi.prod_status"; $query .= " LEFT JOIN products_log pl ON pl.products_id = joi.products_id"; $query .= " LEFT JOIN products p ON p.products_id = joi.products_id"; $query .= " LEFT JOIN products_to_categories ptc ON ptc.products_id = joi.products_id"; $query .= " LEFT JOIN orders_products_email ope " . " ON ope.orders_products_id = joi.jng_sp_orders_items_id AND ope.type = 'S'"; //retrieve MAX(ope.sent_count) $query .= " LEFT JOIN payone_invoice pi " . " ON pi.invoice_order_type = 'S' AND pi.invoice_order_id = jo.jng_sp_orders_id"; if ($return_status) { $query .= " LEFT JOIN jng_sp_orders_items_return joir " . " ON joir.jng_sp_orders_items_id = joi.jng_sp_orders_items_id"; } $query .= " WHERE joi.status = '{$this->filters_status}' AND joi.jng_sp_orders_items_id = '{$orders_items_id}'"; if ($this->filters_order_type > 0) { $query .= " AND jo.jng_sp_id = {$this->filters_order_type} "; } $query .= $this->eanListFilterQuery('joi', $this->filters_list_ean, 'AND', ''); $query .= $this->leadtimeFilterQuery($this->filters_status, 'joi.leadtime_start_date', 'jo.order_date', $this->filters_leadtime_date_start, $this->filters_leadtime_date_end, 'AND', ''); $query .= $this->ordersNoFilterQuery('jo.order_id', $this->filters_list_orders_no, 'AND', ''); if ($this->filters_brand_id > 0) { $query .= " AND p.products_brand_id = '{$this->filters_brand_id}' "; } if ($this->filters_cat_id > 0) { $query .= " AND ptc.categories_id = '{$this->filters_cat_id}' "; } if ($this->filters_sub_status != '') { if ($this->filters_status == '1') { //This will handle for tab Unpaid Prepayment, so far nothing todo } else { $query .= " AND joi.prod_status LIKE '{$this->filters_sub_status}%'"; } } $query .= " AND " . Order::getOldOrderFunctionSP()->filterQueryForShippingWindow(); $query .= " GROUP BY joi.jng_sp_orders_items_id"; return $query; }
/** * Retrieve order items data * @param int $jng_sp_id * @param int $status * @param string $ean_list * @param string $date_start * @param string $date_end * @param int $brand_id * @param int $cat_id * @param string $orders_no * @return array of order items data */ function retrieveOrdersItems($jng_sp_id, $status, $ean_list = null, $date_start = null, $date_end = null, $brand_id = null, $cat_id = null, $orders_no = '') { list($status, $sub_status) = explode('-', $status); //TODO: Change from using payone_invoice to jo.payment_status use_class('payone_invoice'); $class_payi = new payone_invoice(); $return_status = in_array($status, array('16', '17', '18')); $query = "SELECT CONCAT(jo.customer_billing_firstname,' ',jo.customer_billing_lastname) AS customer_name, jo.cod_costs"; $query .= ", jo.order_id, jo.order_date, jo.service_code, jo.customers_order_count, jo.payment_method, jo.payment_status, joi.*"; $query .= ", do.depot_orders_id, do.status AS depot_orders_status, do.prod_status AS depot_orders_prod_status"; $query .= ", MAX(joih.status_date) AS status_date, MAX(psh.status_date) AS status_date_prod, jcust.jng_sp_customers_id, jscs.list_type"; if ($return_status) { $query .= ", MIN(joir.return_time) AS return_time, SUM(joir.return_quantity) AS return_quantity"; } $query .= ", sp.jng_sp_id, sp.package_prefix"; $query .= ", p.products_image, p.products_brand_id, p.material_expenses AS current_mat_exp, ptc.categories_id"; $query .= ", pi.invoice_complete_status, " . payone_invoice::calcBalance('', true) . " AS invoice_current_balance, pi.invoice_amount_paid, pi.is_captured"; $query .= ", MAX(ope.last_sent_date) AS last_sent_date, MAX(ope.sent_count) AS sent_count"; //$query .= ", SUM(jc.total_sold) AS total_sold, AVG(jc.returned_rate) AS returned_rate, MAX(jc.active_age) AS active_age"; //$query .= ", jc.total_sold, jc.returned_rate, jc.active_age"; $query .= ", pl.sold_monthly_1"; $query .= ", IFNULL(joi.leadtime_start_date, jo.order_date) AS date_for_sorting"; $query .= " FROM jng_sp_orders jo"; $query .= " LEFT JOIN jng_sp sp ON sp.jng_sp_id=jo.jng_sp_id"; $query .= " LEFT JOIN jng_sp_customers jcust ON jcust.jng_sp_id=jo.jng_sp_id AND jcust.customer_id=jo.customer_billing_id"; $query .= " LEFT JOIN jng_sp_customers_special jscs ON jscs.jng_sp_customers_id=jcust.jng_sp_customers_id"; $query .= " LEFT JOIN jng_sp_orders_items joi ON joi.jng_sp_orders_id=jo.jng_sp_orders_id"; $query .= " LEFT JOIN depot_orders do ON do.trans_type='SP' AND do.trans_id=joi.jng_sp_orders_items_id"; $query .= " LEFT JOIN jng_sp_orders_items_status_history joih ON joih.jng_sp_orders_items_id=joi.jng_sp_orders_items_id AND joih.status=joi.status"; $query .= " LEFT JOIN production_status_history psh ON psh.type='SP' AND psh.orders_items_id=joi.jng_sp_orders_items_id AND psh.status=joi.prod_status"; if ($return_status) { $query .= " LEFT JOIN jng_sp_orders_items_return joir ON joir.jng_sp_orders_items_id=joi.jng_sp_orders_items_id"; } //$query .= " LEFT JOIN jng_sp_catalog jc ON jc.products_id=joi.products_id"; //$query .= " LEFT JOIN jng_sp_catalog jc ON jc.jng_sp_id = jo.jng_sp_id AND jc.products_id=joi.products_id"; $query .= " LEFT JOIN products_log pl ON pl.products_id=joi.products_id"; $query .= " LEFT JOIN products p ON p.products_id=joi.products_id"; $query .= " LEFT JOIN products_to_categories ptc ON ptc.products_id=joi.products_id"; //$query .= " LEFT JOIN orders_products_email ope ON ope.orders_products_id=joi.jng_sp_orders_items_id AND ope.status=joi.status AND type='S'"; //retrieve ope.sent_count $query .= " LEFT JOIN orders_products_email ope ON ope.orders_products_id=joi.jng_sp_orders_items_id AND ope.type='S'"; //retrieve MAX(ope.sent_count) $query .= " LEFT JOIN payone_invoice pi ON pi.invoice_order_type='S' AND pi.invoice_order_id=jo.jng_sp_orders_id"; $query .= " WHERE joi.status='{$status}'"; if ($jng_sp_id != '0') { $query .= " AND jo.jng_sp_id={$jng_sp_id} "; } $query .= $this->eanListFilterQuery($ean_list, 'AND', ''); $query .= $this->leadtimeFilterQuery($status, $date_start, $date_end, 'AND', ''); if ($brand_id > 0) { $query .= " AND p.products_brand_id='{$brand_id}' "; } if ($cat_id > 0) { $query .= " AND ptc.categories_id='{$cat_id}' "; } if ($orders_no != '') { $query .= " AND jo.order_id IN ('" . (is_array($orders_no) ? implode("','", $orders_no) : $orders_no) . "') "; } if ($sub_status != '') { $query .= " AND joi.prod_status LIKE '{$sub_status}%'"; } $query .= " AND " . $this->filterQueryForShippingWindow(); $query .= " GROUP BY joi.jng_sp_orders_items_id"; $query .= " ORDER BY "; $query .= " date_for_sorting ASC, jo.jng_sp_orders_id ASC, joi.order_item_count ASC"; //die($query); $result = tep_db_query($query); $orders_items = array(); while ($row = tep_db_fetch_array($result)) { $orders_items[$row['jng_sp_orders_items_id']] = $row; } return $orders_items; }