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;
}
