|
Server : Apache/2.4.18 (Ubuntu) System : Linux canvaswebdesign 3.13.0-71-generic #114-Ubuntu SMP Tue Dec 1 02:34:22 UTC 2015 x86_64 User : oppastar ( 1041) PHP Version : 7.0.33-0ubuntu0.16.04.15 Disable Function : pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority, Directory : /proc/self/root/var/www/laciasmara.com/public_html/shop/application/models/ |
Upload File : |
<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
class Order_m extends MY_Model
{
protected $_table_name = 'orders';
protected $_primary_key = 'id_orders';
protected $_order_by = 'id_orders';
public $add_order_rules = array(
array(
'field' => 'customer_id',
'label' => 'Customer',
'rules' => 'trim|required'
),
array(
'field' => 'kurir_id',
'label' => 'Kurir',
'rules' => 'trim|required'
),
);
function __construct()
{
parent::__construct();
// Load model yang diperlukan
$this->load->model('order_detail_m');
$this->load->model('log_m');
$this->load->model('customer_m');
$this->load->model('configuration_m');
}
public function add_order($data)
{
$this->db->insert('orders', $data);
return $this->db->insert_id();
}
//function count all record for orders
public function record_count()
{
return $this->db->get('orders')->num_rows();
}
// Hitung order pending
public function get_pending_order_count($is_retailer = false)
{
$this->db->from('orders');
$this->db->join('customers', 'orders.customer_id = customers.id_customers', 'left');
// Filter berdasarkan payment_status 0, 1, dan 3
$this->db->where_in('orders.payment_status', [0, 1, 3]);
// Tentukan filter reseller_id berdasarkan parameter
$this->db->where('customers.reseller_id ' . ($is_retailer ? 'IS NOT NULL' : 'IS NULL'));
$this->db->where('orders.customer_id !=', 2615);
return $this->db->count_all_results();
}
// Hitung order dengan status proccess
public function get_processing_order_count($is_retailer = false)
{
$this->db->from('orders');
$this->db->join('customers', 'orders.customer_id = customers.id_customers', 'left');
// Filter berdasarkan payment_status 4
$this->db->where('orders.payment_status', 4);
// Tentukan filter reseller_id berdasarkan parameter
$this->db->where('customers.reseller_id ' . ($is_retailer ? 'IS NOT NULL' : 'IS NULL'));
$this->db->where('orders.customer_id !=', 2615);
return $this->db->count_all_results();
}
// lagi dikirim
public function get_shipped_order_count($is_retailer = false)
{
$this->db->from('orders');
$this->db->join('customers', 'orders.customer_id = customers.id_customers', 'left');
// Filter berdasarkan payment_status 4
$this->db->where('orders.payment_status', 4);
$this->db->where('orders.no_resi IS NOT NULL', null, false);
$this->db->where('orders.no_resi !=', '');
// Tentukan filter reseller_id berdasarkan parameter
$this->db->where('customers.reseller_id ' . ($is_retailer ? 'IS NOT NULL' : 'IS NULL'));
$this->db->where('orders.customer_id !=', 2615);
return $this->db->count_all_results();
}
// Hitung order dengan status terkirim
public function get_delivered_order_count($is_retailer = false)
{
$this->db->from('orders');
$this->db->join('customers', 'orders.customer_id = customers.id_customers', 'left');
// Filter berdasarkan payment_status 5
$this->db->where('orders.payment_status', 5);
// Tentukan filter reseller_id berdasarkan parameter
$this->db->where('customers.reseller_id ' . ($is_retailer ? 'IS NOT NULL' : 'IS NULL'));
$this->db->where('orders.customer_id !=', 2615);
return $this->db->count_all_results();
}
public function get_need_to_confirm_order_count()
{
$this->db->from('orders');
$this->db->join('customers', 'orders.customer_id = customers.id_customers', 'left');
// Filter berdasarkan payment_status 3 dan payment_confirm 1
$this->db->where('orders.payment_status', 3);
$this->db->where('orders.payment_confirm', 1);
$this->db->where('orders.customer_id !=', 2615);
return $this->db->count_all_results();
}
// Hitung order dengan status selesai
public function get_finished_order_count($is_retailer = false)
{
$this->db->from('orders');
$this->db->join('customers', 'orders.customer_id = customers.id_customers', 'left');
// Filter berdasarkan payment_status 8
$this->db->where('orders.payment_status', 8);
// Tentukan filter reseller_id berdasarkan parameter
$this->db->where('customers.reseller_id ' . ($is_retailer ? 'IS NOT NULL' : 'IS NULL'));
return $this->db->count_all_results();
}
public function fetch_order_by_id($id_order)
{
// Validasi id_order
$id_order = (int) $id_order;
if (empty($id_order)) {
return null;
}
// Build main query
$this->db->select('o.id_orders, o.customer_id, o.order_date, o.payment_status, o.payment_confirm, o.payment_confirm_details, o.grand_total_amount, o.total_amount, o.payment_type, o.payment_date, o.cancel_date, o.recipient_name, o.address, o.district, o.subdistrict, o.province, o.postcode, o.phone, o.email, o.country, o.redeemed_voucher_code, o.redeemed_voucher_type, o.redeemed_voucher_value, o.redeemed_voucher_amount, o.shipping_fee, o.plus_reward, o.minus_reward, o.minus_reward_amount, o.no_resi, o.customer_note, o.admin_note, o.plus_reward_given, o.current_reward, o.sisa_reward, o.order_language, o.doku_approval_result, o.doku_status, o.doku_payment_channel, o.doku_session_id, o.first, o.gift_receiver_name, o.gift_receiver_phone, o.insurance_status, o.insurance_cost, o.referral, o.source, o.medium, o.campaign, o.tokopedia_invoice, o.updated_at, o.created_by, o.updated_by');
$this->db->from('orders o');
$this->db->where('o.id_orders', $id_order);
// Execute query
$query = $this->db->get();
// Jika tidak ditemukan, return null
if ($query->num_rows() == 0) {
return null;
}
// Ambil data order
$order = $query->row();
$order->is_referral = false;
$order->referred_by = '';
// Check if referral exists in orders table
if (!empty($order->referral)) {
// Query to check if referral matches with any affiliator's referral code (case insensitive)
$this->db->select('nama');
$this->db->from('affiliator_register');
$this->db->where('LOWER(referral) = LOWER("' . $order->referral . '")');
$referral_query = $this->db->get();
if ($referral_query->num_rows() > 0) {
$affiliator = $referral_query->row();
$order->is_referral = true;
$order->referred_by = $affiliator->nama;
}
}
// If not found by referral, check redeemed_voucher_code
if (!$order->is_referral && !empty($order->redeemed_voucher_code)) {
$this->db->select('nama');
$this->db->from('affiliator_register');
$this->db->where('LOWER(referral) = LOWER("' . $order->redeemed_voucher_code . '")');
$voucher_referral_query = $this->db->get();
if ($voucher_referral_query->num_rows() > 0) {
$affiliator = $voucher_referral_query->row();
$order->is_referral = true;
$order->referred_by = $affiliator->nama;
}
}
// Get customer information if available
if ($order->customer_id) {
$this->db->select('c.id_customers, c.name as customer_name, c.email as customer_email, c.recipient_name, c.shipping_name, c.shipping_address, c.shipping_province, c.shipping_district, c.shipping_subdistrict, c.shipping_country, c.shipping_postcode, c.shipping_phone, c.current_pointreward as customer_points, c.reseller_id, c.is_first, c.refferal, c.source');
$this->db->from('customers c');
$this->db->where('c.id_customers', $order->customer_id);
$customer_query = $this->db->get();
if ($customer_query->num_rows() > 0) {
$order->customer_detail = $customer_query->row();
}
}
// Get order details (products ordered)
$this->db->select('
od.id_orders_detail,
od.item_id,
od.product_id,
od.item_name,
od.item_price,
od.quantity,
od.subtotal,
od.sku,
od.attributes,
od.chosen_shipping_id,
sm.name as shipping_method_name,
pi.image as product_image
');
$this->db->from('orders_detail od');
$this->db->join('shipment_method sm', 'od.chosen_shipping_id = sm.id', 'left');
$this->db->join('product_images pi', 'od.item_id = pi.product_details_id AND pi.priority = 1 AND pi.status = 1', 'left');
$this->db->where('od.orders_id', $order->id_orders);
$details_query = $this->db->get();
$order->items = $details_query->result();
// Calculate total items
$order->total_items = 0;
foreach ($order->items as $item) {
$order->total_items += $item->quantity;
}
// Format some data
$order->order_date_formatted = date('d M Y H:i', strtotime($order->order_date));
// Get shipping history if exists
$this->db->select('*');
$this->db->from('shipping');
$this->db->where('order_id', $order->id_orders);
$shipping_query = $this->db->get();
$order->shipping_history = $shipping_query->result();
// Set payment status text
switch ($order->payment_status) {
case 0:
$order->payment_status_text = 'Pending';
$order->payment_status_class = 'bg-yellow-100 text-yellow-700 px-2 py-1 rounded';
break;
case 1:
$order->payment_status_text = 'Menunggu Pembayaran';
$order->payment_status_class = 'bg-orange-100 text-orange-700 px-2 py-1 rounded';
break;
case 2:
$order->payment_status_text = 'Dibatalin';
$order->payment_status_class = 'bg-red-100 text-red-700 px-2 py-1 rounded';
break;
case 3:
$order->payment_status_text = 'Sudah Dibayar';
$order->payment_status_class = 'bg-green-100 text-green-700 px-2 py-1 rounded';
break;
case 4:
$order->payment_status_text = 'Diproses';
$order->payment_status_class = 'bg-blue-100 text-blue-700 px-2 py-1 rounded';
break;
case 5:
$order->payment_status_text = 'Selesai';
$order->payment_status_class = 'bg-gray-100 text-gray-700 px-2 py-1 rounded';
break;
case 8:
$order->payment_status_text = 'Selesai (Retailer)';
$order->payment_status_class = 'bg-purple-100 text-purple-700 px-2 py-1 rounded';
break;
default:
$order->payment_status_text = 'Unknown';
$order->payment_status_class = 'bg-gray-300 text-gray-800 px-2 py-1 rounded';
}
// Calculate total before discount
$total_before_discount = 0;
foreach ($order->items as $item) {
$total_before_discount += $item->subtotal;
}
$order->total_before_discount = $total_before_discount;
// Calculate discount amount
$order->discount_amount = 0;
if (!empty($order->redeemed_voucher_code)) {
$order->discount_amount = $order->redeemed_voucher_amount;
}
return $order;
}
public function cancel_expired_orders()
{
// Cari order yang belum dibayar (payment_confirm = 0)
$start = '2025-07-01 00:00:00';
$end = '2025-07-31 23:59:59';
$deadline = date('Y-m-d H:i:s', strtotime('-24 hours'));
$this->db->select('orders.id_orders, orders.customer_id, orders.total_amount, orders.referral, orders.redeemed_voucher_code, orders.order_language');
$this->db->from('orders');
$this->db->join('customers', 'orders.customer_id = customers.id_customers');
$this->db->where('orders.payment_confirm', 0);
$this->db->where_in('orders.payment_status', [0, 1]);
$this->db->where('orders.order_date >=', $start);
$this->db->where('orders.order_date <=', $end);
$this->db->where('orders.order_date <', $deadline);
$this->db->group_start();
$this->db->where('customers.reseller_id IS NULL', null, false);
$this->db->or_where('customers.reseller_id', '');
$this->db->or_where('customers.id_customers', 2615);
$this->db->group_end();
$expired_orders = $this->db->get()->result();
$updated_count = 0;
foreach ($expired_orders as $order) {
// Mulai transaksi untuk setiap order
$this->db->trans_begin();
try {
// Set data untuk update status order
$orders_data = [
'payment_status' => 2, // Set sebagai cancel
'payment_status_message' => 'Auto cancelled due to no payment within 24 hours',
'cancel_date' => date('Y-m-d'),
'updated_at' => date('Y-m-d H:i:s'),
'updated_by' => 'System'
];
// Update status order
$this->db->where('id_orders', $order->id_orders);
$this->db->update('orders', $orders_data);
// Handle pengurangan komisi
$this->handleCancelCommission($order->id_orders, $order);
// Return stock ke inventory
$this->returnStockToInventory($order->id_orders, $order->customer_id);
// Return customer point reward
$this->returnCustomerPointReward($order->id_orders, $order->customer_id);
// Kirim email notifikasi pembatalan
$this->sendCancelNotification($order->id_orders);
// Commit transaksi jika semua berhasil
$this->db->trans_commit();
$updated_count++;
// Log untuk debugging
log_message('info', "Order ID {$order->id_orders} berhasil dibatalkan secara otomatis setelah 24 jam tidak dibayar.");
} catch (Exception $e) {
// Rollback transaksi jika terjadi error
$this->db->trans_rollback();
log_message('error', "Gagal membatalkan Order ID {$order->id_orders}: " . $e->getMessage());
}
}
return $updated_count;
}
/**
* Menangani pengurangan komisi saat pesanan dibatalkan
*/
public function handleCancelCommission($order_id, $order_data)
{
log_message('debug', "handleCancelCommission: Memproses Order ID: {$order_id}");
$referral_code = !empty($order_data->referral) ? $order_data->referral : $order_data->redeemed_voucher_code;
log_message('debug', "Referral Code: " . ($referral_code ?: 'Tidak Ada'));
if (empty($referral_code)) {
log_message('debug', "handleCancelCommission: Tidak ada referral code, proses dihentikan.");
return;
}
// Ambil kategori affiliator berdasarkan kode
$this->db->select('kategori, id_customer');
$this->db->from('affiliator_register');
$this->db->where('referral', $referral_code);
$affiliator = $this->db->get()->row();
if (!$affiliator) {
log_message('debug', "handleCancelCommission: Affiliator tidak ditemukan.");
return;
}
log_message('debug', "Affiliator ditemukan: {$affiliator->id_customer} - Kategori: {$affiliator->kategori}");
// Hitung pengurangan komisi
$deduction = 0;
if ($affiliator->kategori == 'asmaradoor') {
$deduction = 0.10 * $order_data->total_amount;
} elseif ($affiliator->kategori == 'asmarasana') {
$deduction = 0.20 * $order_data->total_amount;
}
log_message('debug', "Pengurangan Komisi: {$deduction}");
if ($deduction > 0) {
$this->db->set('komisi_order', "komisi_order - $deduction", FALSE);
$this->db->where('customer_id', $affiliator->id_customer);
$this->db->update('affiliator_link');
log_message('debug', "Komisi di affiliator_link diperbarui.");
$this->db->set('komisi', "komisi - $deduction", FALSE);
$this->db->where('id_customer', $affiliator->id_customer);
$this->db->update('affiliator_register');
log_message('debug', "Komisi di affiliator_register diperbarui.");
}
}
/**
* Mengembalikan stok ke inventory saat pesanan dibatalkan
*/
public function returnStockToInventory($order_id, $customer_id)
{
log_message('debug', "returnStockToInventory: Memproses Order ID: {$order_id}");
$order_details = $this->order_detail_m->get_orders_detail($order_id);
log_message('debug', "Total Item: " . count($order_details));
$user_name = $this->session->userdata('name') ?? 'System';
foreach ($order_details as $item) {
log_message('debug', "Mengembalikan stok untuk produk ID: {$item->product_id} - Item ID: {$item->item_id}");
$this->db->select('stock, id')
->from('stock')
->where('id_product', $item->product_id)
->where('id_product_detail', $item->item_id)
->where('warehouse_id', $item->warehouse_id);
$current_stock = $this->db->get()->row();
if (!$current_stock) {
log_message('error', "Stock data tidak ditemukan untuk Produk ID: {$item->product_id}");
continue;
}
$qty = ($item->is_backorder == 'yes') ? 0 : $item->quantity;
if ($qty > 0) {
$old_stock = $current_stock->stock;
$new_stock = $old_stock + $qty;
log_message('debug', "Stok Sebelumnya: {$old_stock}, Stok Baru: {$new_stock}");
$this->db->where('id_product', $item->product_id);
$this->db->where('id_product_detail', $item->item_id);
$this->db->where('warehouse_id', $item->warehouse_id);
$this->db->update('stock', ['stock' => $new_stock]);
log_message('debug', "Stock diperbarui di database.");
$this->db->insert('stock_movement', [
'stock_id' => $current_stock->id,
'type' => '+',
'stock_change' => (int)$qty,
'remark' => "Pembatalan Pesanan dengan Order ID: {$order_id} - Mengembalikan {$qty} unit ke stok.",
'total' => $new_stock,
'name' => $user_name
]);
log_message('debug', "Stock movement dicatat.");
$referenceUrl = base_url('admin/orders/detail/' . $order_id);
$this->log_m->log_stock_update(
$order_id,
$item->item_id,
$item->item_name,
$old_stock,
$new_stock,
$referenceUrl,
null,
'stock',
'orders'
);
log_message('debug', "Stock update log dicatat.");
}
}
}
/**
* Mengembalikan point reward customer saat pesanan dibatalkan
*/
public function returnCustomerPointReward($order_id, $customer_id)
{
log_message('debug', "returnCustomerPointReward: Mengembalikan poin untuk Order ID: {$order_id}, Customer ID: {$customer_id}");
$this->db->select('current_pointreward')->from('customers')->where('id_customers', $customer_id);
$current_point = (int)$this->db->get()->row()->current_pointreward;
log_message('debug', "Poin saat ini: {$current_point}");
$this->db->select('current_reward, sisa_reward, minus_reward')->from('orders')->where('id_orders', $order_id);
$rewards = $this->db->get()->row();
if (!$rewards) {
log_message('error', "Data reward tidak ditemukan untuk Order ID: {$order_id}");
return;
}
$minus_point = (int) $rewards->minus_reward;
$current_reward = (int) $rewards->current_reward;
$sisa_reward = (int) $rewards->sisa_reward;
log_message('debug', "Data Reward - Current: {$current_reward}, Sisa: {$sisa_reward}, Minus: {$minus_point}");
if (($sisa_reward + $minus_point) == $current_reward) {
$updated_point = $current_point + $minus_point;
log_message('debug', "Poin yang akan ditambahkan: {$minus_point}, Poin Baru: {$updated_point}");
$this->db->where('id_customers', $customer_id);
$this->db->update('customers', ['current_pointreward' => $updated_point]);
log_message('debug', "Poin customer diperbarui di database.");
} else {
log_message('error', "Validasi perhitungan reward gagal untuk Order ID: {$order_id}");
}
}
public function sendCancelNotification($order_id)
{
log_message('debug', "sendCancelNotification: Memproses Order ID: {$order_id}");
// Get order data
$order = $this->get_order($order_id);
$customer = $this->customer_m->get_customer($order->customer_id);
// Get website configuration
$this->db->select('logo, from_email, website_name')->from('configuration')->where('id_configuration', 1);
$website_data = $this->db->get()->row();
$email_data = [
'order' => $order,
'customer' => $customer,
'logo' => $website_data->logo,
'website_name' => $website_data->website_name,
'emails' => $this->configuration_m->get_emails(),
'email' => $customer->email,
'minus_point' => $order->minus_reward,
'title' => 'Order Cancel'
];
// Tentukan template email berdasarkan bahasa
if ($order->order_language == 'english') {
$email_data['subject'] = 'Your Order Has Been Automatically Cancelled';
$view_file = 'email/english/order_cancel';
} else {
$email_data['subject'] = 'Pesanan Anda Otomatis Dibatalkan';
$view_file = 'email/indonesian/order_cancel';
}
// Catat log perubahan status
$description_notes = "Status Pesanan {$order_id} otomatis diubah menjadi 'Batal' oleh sistem setelah 24 jam tidak ada pembayaran.";
$this->log_m->log_order_update(
$order_id,
$description_notes,
'payment_status',
$order->payment_status, // Status lama
'2' // Status baru (batal)
);
// Kirim email menggunakan metode dari MY_Controller
$this->send_email($view_file, $email_data);
}
// public function get_order($order_id)
// {
// return $this->db->get_where('orders', ['id_orders' => $order_id])->row();
// }
/**
* Fungsi send_email yang diambil dari MY_Controller
*/
private function send_email($view_file, $email_data, $e_msg = false)
{
// Get website data
$website_data = $this->db
->select(
'logo, from_email, website_name, email_smtp_host, email_smtp_port, email_smtp_password, email_smtp'
)
->from('configuration')
->where('id_configuration', 1)
->get()
->row();
$email_data['website_name'] = $website_data->website_name;
$email_data['logo'] = $website_data->logo;
$this->load->library('email');
$config['protocol'] = 'smtp';
$config['smtp_host'] = $website_data->email_smtp_host;
$config['smtp_port'] = $website_data->email_smtp_port;
$config['smtp_user'] = $website_data->email_smtp;
$config['smtp_pass'] = $website_data->email_smtp_password;
$config['mailtype'] = 'html';
$config['smtp_crypto'] = 'ssl';
$config['charset'] = 'iso-8859-1';
$config['wordwrap'] = true;
$config['newline'] = "\r\n";
$this->email->set_crlf("\r\n");
$this->email->initialize($config);
$this->email->from($website_data->from_email, $website_data->website_name);
$this->email->to($email_data['email']);
$this->email->subject($email_data['subject']);
if ($e_msg === false) {
$email = $this->load->view($view_file, $email_data, true);
$this->email->message($email);
} else {
$this->email->message($view_file);
}
$success = $this->email->send();
if (!$success) {
log_message('error', "Gagal mengirim email untuk Order ID: {$email_data['order']->id_orders} - " . $this->email->print_debugger(array('headers')));
} else {
log_message('info', "Email notifikasi pembatalan otomatis berhasil dikirim untuk Order ID: {$email_data['order']->id_orders}");
}
return $success;
}
public function record_count_incoming()
{
// Join dengan tabel customers untuk memeriksa reseller_id
$this->db->select('orders.id_orders');
$this->db->from('orders');
$this->db->join('customers', 'orders.customer_id = customers.id_customers', 'left');
// Filter berdasarkan payment_status 0 dan 1
$this->db->where_in('orders.payment_status', array(0, 1, 3));
// Filter untuk reseller_id yang null
$this->db->where('customers.reseller_id IS NULL');
return $this->db->count_all_results();
}
public function record_count_incoming_retailer()
{
// Join dengan tabel customers untuk memeriksa reseller_id
$this->db->select('orders.id_orders');
$this->db->from('orders');
$this->db->join('customers', 'orders.customer_id = customers.id_customers', 'left');
// Filter berdasarkan payment_status 0 dan 1
$this->db->where_in('orders.payment_status', array(0, 1, 3));
// Filter untuk reseller_id yang null
$this->db->where('customers.reseller_id IS NOT NULL');
return $this->db->count_all_results();
}
//pagination included
function get_all_orders($limit, $start)
{
$this->db->select('*');
$this->db->from('orders');
// $this->db->join('customers', 'customers.id_customers = orders.customer_id');
$this->db->order_by('id_orders', 'desc');
$this->db->limit($limit, $start);
$query = $this->db->get();
return $query->result();
}
function get_all_orders_retailer($limit, $start)
{
$this->db->select('*');
$this->db->from('orders');
$this->db->join('customers', 'customers.id_customers = orders.customer_id');
$this->db->where('customers.reseller_id IS NOT NULL');
$this->db->order_by('id_orders', 'desc');
$this->db->limit($limit, $start);
$query = $this->db->get();
return $query->result();
}
function warestats($wareid)
{
$this->db->select('warehouse_id')->from('orders_detail')->where('orders_id', $wareid);
$warehouse_ids = $this->db->get()->result();
$warehouse_id_array = array();
foreach ($warehouse_ids as $warehouse_id) {
if (!in_array($warehouse_id->warehouse_id, $warehouse_id_array)) {
$warehouse_id_array[] = $warehouse_id->warehouse_id;
}
}
if (count($warehouse_id_array) > 1) {
return 'Iya';
} else {
return 'Tidak';
}
}
function filterdata($search, $filterkey, $dataenter, $iduser, $length, $start)
{
$this->db->select('warehouse_id,role')->from('users')->where('id', $iduser);
$user = $this->db->get()->row();
$this->db->select('*');
$this->db->from('orders');
$this->db->join('customers', 'customers.id_customers = orders.customer_id');
if (!empty($search)) {
$this->db->like("orders.id_orders", $search);
}
if ($user->role == 'apoteker') {
$this->db->join('orders_detail', 'orders_detail.orders_id = orders.id_orders');
$this->db->where('orders_detail.warehouse_id', $user->warehouse_id);
$this->db->group_by('orders.id_orders');
}
$this->db->where('customers.reseller_id IS NULL');
if ($filterkey == 'voucher') {
if ($dataenter == '1') {
$this->db->where('redeemed_voucher_code !=', null);
} else {
$this->db->where('redeemed_voucher_code', null);
}
} else if ($filterkey == 'flashsale') {
if ($user->role != 'apoteker') {
$this->db->join('orders_detail', 'orders_detail.orders_id = orders.id_orders');
}
$this->db->where('orders_detail.is_flashsale', $dataenter);
} else if ($filterkey == 'sale') {
if ($user->role != 'apoteker') {
$this->db->join('orders_detail', 'orders_detail.orders_id = orders.id_orders');
}
$this->db->where('orders_detail.is_sale', $dataenter);
} else if ($filterkey == 'date') {
$ex = explode('_', $dataenter);
$this->db->where('orders.order_date >=', $ex[0]);
$this->db->where('orders.order_date <=', $ex[1]);
} else if ($filterkey == 'totalorder') {
$ex = explode('_', $dataenter);
$this->db->where('((orders.total_amount+IFNULL(ABS(orders.sisa_kembali),0))-IFNULL(orders.redeemed_voucher_value,0))+(IFNULL(orders.shipping_fee,0)-IFNULL(orders.free_shipping_fee,0)) >=', $ex[0]);
$this->db->where('((orders.total_amount+IFNULL(ABS(orders.sisa_kembali),0))-IFNULL(orders.redeemed_voucher_value,0))+(IFNULL(orders.shipping_fee,0)-IFNULL(orders.free_shipping_fee,0)) <=', $ex[1]);
} else if ($filterkey == 'product') {
if ($user->role != 'apoteker') {
$this->db->join('orders_detail', 'orders_detail.orders_id = orders.id_orders');
}
$this->db->where('orders_detail.product_id', $dataenter);
$this->db->where('orders_detail.product_id', $dataenter);
} else if ($filterkey == 'sync') {
$this->db->where('orders.status_jurnalid', 'sync');
} else if ($filterkey == 'open') {
$this->db->where('orders.status_jurnalid', 'open');
$this->db->where('orders.payment_status', '5');
} else {
if (!empty($filterkey)) {
$this->db->like($filterkey, $dataenter);
}
}
if ($length != '' and $start != '') {
$this->db->limit($length, $start);
}
$this->db->order_by('orders.id_orders', 'desc');
return $this->db->get();
}
function filterdataretailer($search, $filterkey, $dataenter, $iduser, $length, $start)
{
$this->db->select('warehouse_id,role')->from('users')->where('id', $iduser);
$user = $this->db->get()->row();
$this->db->select('*');
$this->db->from('orders');
$this->db->join('customers', 'customers.id_customers = orders.customer_id');
if (!empty($search)) {
$this->db->like("orders.id_orders", $search);
}
if ($user->role == 'apoteker') {
$this->db->join('orders_detail', 'orders_detail.orders_id = orders.id_orders');
$this->db->where('orders_detail.warehouse_id', $user->warehouse_id);
$this->db->group_by('orders.id_orders');
}
$this->db->where('customers.reseller_id IS NOT NULL');
if ($filterkey == 'voucher') {
if ($dataenter == '1') {
$this->db->where('redeemed_voucher_code !=', null);
} else {
$this->db->where('redeemed_voucher_code', null);
}
} else if ($filterkey == 'flashsale') {
if ($user->role != 'apoteker') {
$this->db->join('orders_detail', 'orders_detail.orders_id = orders.id_orders');
}
$this->db->where('orders_detail.is_flashsale', $dataenter);
} else if ($filterkey == 'sale') {
if ($user->role != 'apoteker') {
$this->db->join('orders_detail', 'orders_detail.orders_id = orders.id_orders');
}
$this->db->where('orders_detail.is_sale', $dataenter);
} else if ($filterkey == 'date') {
$ex = explode('_', $dataenter);
$this->db->where('orders.order_date >=', $ex[0]);
$this->db->where('orders.order_date <=', $ex[1]);
} else if ($filterkey == 'totalorder') {
$ex = explode('_', $dataenter);
$this->db->where('((orders.total_amount+IFNULL(ABS(orders.sisa_kembali),0))-IFNULL(orders.redeemed_voucher_value,0))+(IFNULL(orders.shipping_fee,0)-IFNULL(orders.free_shipping_fee,0)) >=', $ex[0]);
$this->db->where('((orders.total_amount+IFNULL(ABS(orders.sisa_kembali),0))-IFNULL(orders.redeemed_voucher_value,0))+(IFNULL(orders.shipping_fee,0)-IFNULL(orders.free_shipping_fee,0)) <=', $ex[1]);
} else if ($filterkey == 'product') {
if ($user->role != 'apoteker') {
$this->db->join('orders_detail', 'orders_detail.orders_id = orders.id_orders');
}
$this->db->where('orders_detail.item_id', $dataenter);
$this->db->where('orders_detail.item_id', $dataenter);
} else if ($filterkey == 'sync') {
$this->db->where('orders.status_jurnalid', 'sync');
} else if ($filterkey == 'open') {
$this->db->where('orders.status_jurnalid', 'open');
$this->db->where('orders.payment_status', '5');
} else {
if (!empty($filterkey)) {
$this->db->like($filterkey, $dataenter);
}
}
if ($length != '' and $start != '') {
$this->db->limit($length, $start);
}
$this->db->order_by('orders.id_orders', 'desc');
return $this->db->get();
}
function filterexcel($filterkey, $dataenter, $iduser)
{
$this->db->select('warehouse_id,role')->from('users')->where('id', $iduser);
$user = $this->db->get()->row();
$this->db->select('*');
$this->db->from('orders_detail');
if ($user->role == 'apoteker') {
$this->db->join('orders', 'orders_detail.orders_id = orders.id_orders');
$this->db->where('orders_detail.warehouse_id', $user->warehouse_id);
$this->db->group_by('orders.id_orders');
} else {
$this->db->join('orders', 'orders_detail.orders_id = orders.id_orders');
}
if ($filterkey != '') {
if ($filterkey == 'voucher') {
if ($dataenter == '1') {
$this->db->where('orders.redeemed_voucher_code !=', null);
} else {
$this->db->where('orders.redeemed_voucher_code', null);
}
} else if ($filterkey == 'flashsale') {
$this->db->where('orders_detail.is_flashsale', $dataenter);
} else if ($filterkey == 'sale') {
$this->db->where('orders_detail.is_sale', $dataenter);
} else if ($filterkey == 'date') {
$ex = explode('_', $dataenter);
$this->db->where('orders.order_date >=', $ex[0]);
$this->db->where('orders.order_date <=', $ex[1]);
} else if ($filterkey == 'totalorder') {
$ex = explode('_', $dataenter);
$this->db->where('((orders.total_amount+IFNULL(ABS(orders.sisa_kembali),0))-IFNULL(orders.redeemed_voucher_value,0))+(IFNULL(orders.shipping_fee,0)-IFNULL(orders.free_shipping_fee,0)) >=', $ex[0]);
$this->db->where('((orders.total_amount+IFNULL(ABS(orders.sisa_kembali),0))-IFNULL(orders.redeemed_voucher_value,0))+(IFNULL(orders.shipping_fee,0)-IFNULL(orders.free_shipping_fee,0)) <=', $ex[1]);
} else if ($filterkey == 'product') {
$this->db->where('orders_detail.item_id', $dataenter);
} else {
if (!empty($filterkey)) {
$this->db->like($filterkey, $dataenter);
}
}
}
$this->db->order_by('orders_detail.orders_id', 'desc');
return $this->db->get()->result();
}
function paystats($statspay)
{
switch ($statspay) {
case 0:
return '<span style="color:black; font-weight:bold;">Pending</span>';
break;
case 1:
return '<span style="color:brown; font-weight:bold;">Belum bayar</span>';
break;
case 2:
return '<span style="color:red; font-weight:bold;">Batal</span>';
break;
case 3:
return '<span style="color:green; font-weight:bold;">Sudah bayar</span>';
break;
case 4:
return '<span style="color:blue; font-weight:bold;">Proses</span>';
break;
case 5:
return '<span style="color:blue; font-weight:bold;">Terkirim</span>';
break;
case 8:
return '<span style="color:blue; font-weight:bold;">Selesai</span>';
break;
case 9:
return '<span style="color:red; font-weight:bold;">Komplain</span>';
break;
case 6:
return '<span style="color:brown; font-weight:bold;">Dibayar sebagian (indent)</span>';
break;
}
}
function paymettype($paytype, $payconf, $paystatsmsg)
{
if ($paytype == 'bank transfer BCA') {
if ($payconf == 1) {
return 'Konfirmasi bayar: Sudah';
} else {
return 'Konfirmasi bayar: Belum';
}
} elseif ($paytype == 'bank transfer MANDIRI') {
if ($payconf == 1) {
return 'Konfirmasi bayar: Sudah';
} else {
return 'Konfirmasi bayar: Belum';
}
} elseif ($paytype == 'midtrans') {
return $paystatsmsg;
}
}
public function updateongkir($id, $data)
{
$this->db->where('id_orders', $id);
$this->db->update('orders', $data);
}
//pagination included lol
function report_get_all_orders()
{
$this->db->select('*');
$this->db->from('orders');
$this->db->join('customers', 'customers.id_customers = orders.customer_id');
$this->db->group_start();
$this->db->where('payment_status', 3);
$this->db->or_where('payment_status', 4);
$this->db->or_where('payment_status', 5);
$this->db->group_end();
$this->db->order_by('id_orders', 'desc');
// $this->db->limit($limit, $start);
$query = $this->db->get();
return $query->result();
}
function sales_report($mulai, $getData, $title, $startDate, $endDate)
{
$page = isset($getData) ? (int)$getData : 1;
$halaman = ($page > 1) ? ($page * $mulai) - $mulai : 0;
$this->db->select('*');
$this->db->from('orders');
$this->db->join('orders_detail', 'orders.id_orders = orders_detail.orders_id AND orders_detail.status = 2');
if (!empty($title)) {
$this->db->like('item_name', $title);
}
if (!empty($startDate) && !empty($endDate)) {
$this->db->where("orders.order_date >=", $startDate);
$this->db->where('orders.order_date <=', $endDate);
} else {
if (!empty($startDate)) {
$this->db->where("orders.order_date >=", $startDate);
} else if (!empty($endDate)) {
$this->db->where('orders.order_date <=', $endDate);
}
}
$this->db->where('orders.customer_id <>', '6292');
$this->db->group_by('orders_detail.item_id,orders_detail.item_price');
$this->db->order_by('orders_detail.item_name', 'desc');
$this->db->limit($mulai, $halaman);
$query = $this->db->get();
return $query->result();
}
function getallqty($prodid, $title, $startDate, $endDate, $price)
{
$this->db->select('sum(orders_detail.quantity) as qty');
//$this->db->select('*');
$this->db->from('orders');
$this->db->join('orders_detail', 'orders.id_orders = orders_detail.orders_id AND orders_detail.status = 2');
if (!empty($title)) {
$this->db->like('item_name', $title);
}
if (!empty($startDate) && !empty($endDate)) {
$this->db->where("orders.order_date >=", $startDate);
$this->db->where('orders.order_date <=', $endDate);
} else {
if (!empty($startDate)) {
$this->db->where("orders.order_date >=", $startDate);
} else if (!empty($endDate)) {
$this->db->where('orders.order_date <=', $endDate);
}
}
if (!empty($price)) {
$this->db->where("orders_detail.item_price", $price);
}
$this->db->where('orders.customer_id <>', '6292');
$this->db->where("orders_detail.item_id", $prodid);
$query = $this->db->get();
return $query->row();
}
function sales_report_all($title, $startDate, $endDate)
{
$this->db->select('*');
$this->db->from('orders_detail');
$this->db->where('orders_detail.status', 2);
if (!empty($title)) {
$this->db->like('item_name', $title);
}
if (!empty($startDate) && !empty($endDate)) {
$this->db->join('orders', 'orders.id_orders = orders_detail.orders_id');
$this->db->where("orders.order_date >=", $startDate);
$this->db->where('orders.order_date <=', $endDate);
} else {
$this->db->join('orders', 'orders.id_orders = orders_detail.orders_id');
if (!empty($startDate)) {
$this->db->where("orders.order_date >=", $startDate);
} else if (!empty($endDate)) {
$this->db->where('orders.order_date <=', $endDate);
}
}
$query = $this->db->get();
return $query->result();
}
function tpage_salesreport($title, $startDate, $endDate)
{
$this->db->select('*');
$this->db->from('orders');
$this->db->join('orders_detail', 'orders.id_orders = orders_detail.orders_id AND orders_detail.status = 2');
if (!empty($title)) {
$this->db->like('item_name', $title);
}
if (!empty($startDate) && !empty($endDate)) {
$this->db->where("orders.order_date >=", $startDate);
$this->db->where('orders.order_date <=', $endDate);
} else {
if (!empty($startDate)) {
$this->db->where("orders.order_date >=", $startDate);
} else if (!empty($endDate)) {
$this->db->where('orders.order_date <=', $endDate);
}
}
$this->db->where('orders.customer_id <>', '6292');
$this->db->group_by('orders_detail.item_id');
//$this->db->group_by('orders_detail.item_id,orders_detail.item_price');
$this->db->order_by('orders_detail.item_name', 'desc');
$query = $this->db->get();
return $query->result();
}
function excel_export()
{
$this->db->select('
orders_id,
item_name,
item_price,
quantity,
subtotal,
warehouse_id,
chosen_shipping_id,
shipping_fee,
is_backorder,
status,
no_resi
');
$this->db->from('orders_detail');
$this->db->order_by('orders_id', 'desc');
$query = $this->db->get();
return $query->result();
}
//get specific order with its customer details
function get_order($id)
{
$this->db->select('*');
$this->db->from('orders');
$this->db->where('id_orders', $id);
$query = $this->db->get();
return $query->row();
}
//update credit card payment status
function update_payment_status($id, $data)
{
$this->db->where('id_orders', $id);
$this->db->update('orders', $data);
}
function get_order_history($id_customer)
{
$this->db->select('*');
$this->db->from('orders');
$this->db->where('customer_id', $id_customer);
$this->db->order_by('id_orders', 'DESC');
$query = $this->db->get();
return $query->result();
}
//function count record based on chosen date range
function record_count_search_date($date_start, $date_end)
{
$this->db->select('*');
$this->db->from('orders');
$this->db->group_start();
$this->db->where('payment_status', 3);
$this->db->or_where('payment_status', 4);
$this->db->or_where('payment_status', 5);
$this->db->group_end();
$this->db->where('order_date >=', $date_start);
$this->db->where('order_date <=', $date_end);
$query = $this->db->get();
return $query->num_rows();
}
//function find store by filtering between 2 dates lol
function find_order_by_date($date_start, $date_end, $payment)
{
$this->db->select('*');
$this->db->from('orders');
$this->db->group_start();
$this->db->where('payment_status', 3);
$this->db->or_where('payment_status', 4);
$this->db->or_where('payment_status', 5);
$this->db->group_end();
$this->db->where('order_date >=', $date_start);
$this->db->where('order_date <=', $date_end);
if ($payment != 'all') $this->db->where('payment_type', $payment);
$this->db->order_by('order_date', 'DESC');
// $this->db->limit($limit, $start);
$query = $this->db->get();
return $query->result();
}
function get_notification_order($warehouse_id)
{
$ordernotif = 0;
$data = array(3, 1, 4); //Paid, Unpaid, Process
if ($warehouse_id == 0) {
$this->db->select('*');
$this->db->from('orders');
$this->db->where_in('payment_status', $data);
$ordernotif = $this->db->get()->num_rows();
} else {
$this->db->select('distinct id_orders', false);
$this->db->from('orders');
$this->db->join('orders_detail', 'orders.id_orders = orders_detail.orders_id');
$this->db->where_in('payment_status', $data);
$this->db->where('warehouse_id', $warehouse_id);
$ordernotif = $this->db->get()->num_rows();
}
return $ordernotif;
}
function get_customer_by_order($order_id)
{
$this->db->select('*');
$this->db->from('orders');
$this->db->where('id_orders', $order_id);
$id_customer = $this->db->get()->row()->customer_id;
return $id_customer;
}
//pagination included
function report_get_all_orders_count()
{
$this->db->select('*');
$this->db->from('orders');
$this->db->join('customers', 'customers.id_customers = orders.customer_id');
$this->db->group_start();
$this->db->where('payment_status', 3);
$this->db->or_where('payment_status', 4);
$this->db->or_where('payment_status', 5);
$this->db->group_end();
$this->db->order_by('id_orders', 'desc');
$query = $this->db->get();
return $query->num_rows();
}
function cek_order($id, $paymentstatus)
{
$this->db->select('*');
$this->db->from('orders');
$this->db->where('id_orders', $id);
$currpayment = $this->db->get()->row()->payment_status;
//return $currpayment." - ".$paymentstatus;
if ($paymentstatus == 2) {
return true;
} else if ($paymentstatus > $currpayment) {
return true;
} else {
return false;
}
}
function cek_orderdetail($id, $status)
{
$this->db->select('*');
$this->db->from('orders_detail');
$this->db->where('id_orders_detail', $id);
$currpayment = $this->db->get()->row()->status;
//return $currpayment." - ".$paymentstatus;
if ($status == $currpayment) {
return true;
} else {
return false;
}
}
function cek_stok($idproduct, $qty, $warehouse)
{
$wh_type = $this->db->select("*")->from("warehouse")->where('id', $warehouse)->get()->row()->warehouse_type;
$count = $this->db->select("*")->from("stock")->where("warehouse_id", $warehouse)->where("id_product", $idproduct)->get()->num_rows();
if ($count > 0) {
if ($wh_type == "virtual") {
$current_stock = (int) $this->db->select("IFNULL(stock_virtual,0) stock_virtual")->from("stock")->where("warehouse_id", $warehouse)->where("id_product", $idproduct)->get()->row()->stock_virtual;
} else {
$current_stock = (int) $this->db->select("IFNULL(stock,0) stock")->from("stock")->where("warehouse_id", $warehouse)->where("id_product", $idproduct)->get()->row()->stock;
}
if ($qty > $current_stock) {
return false;
} else {
return true;
}
} else {
return false;
}
}
}