<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Reports extends MY_Controller {
	public function __construct(){
		parent::__construct();
		$this->output->disable_cache();
		$this->load_global();
		$this->load->model('reports_model','reports');
	}	
		
	//Sales Report 
	public function sales(){
		$this->permission_check('sales_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('sales_report');
		$this->load->view('report-sales', $data);
	}
	public function show_sales_report(){
		echo $this->reports->show_sales_report();
	}

	//Sales Return Report 
	public function sales_return(){
		$this->permission_check('sales_return_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('sales_return_report');
		$this->load->view('report-sales-return', $data);
	}
	public function show_sales_return_report(){
		echo $this->reports->show_sales_return_report();
	}

	//Purchase report
	public function purchase(){
		$this->permission_check('purchase_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('purchase_report');
		$this->load->view('report-purchase', $data);
	}
	public function show_purchase_report(){
		echo $this->reports->show_purchase_report();
	}

	//Purchase Return report
	public function purchase_return(){
		$this->permission_check('purchase_return_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('purchase_return_report');
		$this->load->view('report-purchase-return', $data);
	}
	public function show_purchase_return_report(){
		echo $this->reports->show_purchase_return_report();
	}

	//Expense report
	public function expense(){
		$this->permission_check('expense_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('expense_report');
		$this->load->view('report-expense', $data);
	}

	public function revenue(){
		$this->permission_check('expense_report');
		$data=$this->data;
		$data['page_title']="Daraz Order Revenue History";
		$this->load->view('report-revenue', $data);
	}


	public function show_expense_report(){
		echo $this->reports->show_expense_report();
	}
	//Profit report
	public function profit_loss(){
		$this->permission_check('profit_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('profit_and_loss_report');
		$this->load->view('report-profit-loss', $data);
	}
	//agent commision
	public function agent_commision(){
		$this->permission_check('profit_report');
		$data=$this->data;
		$data['page_title']='Agent Commissions';
		$this->load->view('report-profit-loss', $data);
	}

	public function order_revenue_load_details_get(){
		$to_date = $this->input->post('to_date');
		$from_date = $this->input->post('from_date');

		$query=$this->db->query("SELECT db_daraz_orders.id, db_daraz_orders.OrderNumber , db_daraz_orders.SellerSKU , IFNULL(db_items.sku, ' - ') AS barcode , db_items.item_name , IFNULL(db_color.color, ' - ') AS item_colour , IFNULL(db_size.size, ' - ') AS item_size , IFNULL(db_category.category_name, ' - ') AS cat_name , db_daraz_orders.issueStatus , db_daraz_orders.Createdat , IFNULL( CASE WHEN db_daraz_orders.issueStatus = 'ReturnReceived' THEN 0 WHEN db_daraz_orders.issueStatus = 'Returned' THEN db_stock.cost_price * - 1 WHEN (db_daraz_orders.issueStatus = 'Issued' AND db_daraz_orders.Paid > 0 ) THEN db_daraz_orders.Paid - db_stock.cost_price WHEN (db_daraz_orders.issueStatus = 'Issued' AND (IFNULL(db_daraz_orders.Paid, 0)= 0 )) THEN db_stock.cost_price * - 1 END, 0 ) AS profitNew , IFNULL(db_daraz_orders.Paid, 0) AS Paid FROM db_daraz_orders INNER JOIN db_items ON (db_daraz_orders.SellerSKU = db_items.sku) INNER JOIN db_stock ON (db_items.id = db_stock.item_id) INNER JOIN db_category ON (db_category.id = db_items.category_id) INNER JOIN db_color ON (db_color.id = db_items.color_id) INNER JOIN db_size ON (db_size.id = db_items.size_id) WHERE (DATE(db_daraz_orders.Createdat) BETWEEN '$from_date' AND '$to_date') ORDER BY Createdat DESC;");
		$res['ord_info'] = $query->result();

		echo json_encode($res);
	}

	public function load_all_details_get(){
		$query=$this->db->query("SELECT db_daraz_orders.id, db_daraz_orders.OrderNumber, db_daraz_orders.SellerSKU, IFNULL(db_items.sku, ' - ') AS barcode, db_items.item_name, IFNULL(db_color.color, ' - ') AS item_colour, IFNULL(db_size.size, ' - ') AS item_size, IFNULL( db_category.category_name, ' - ' ) AS cat_name, db_daraz_orders.issueStatus, db_daraz_orders.Createdat, IFNULL( CASE WHEN db_daraz_orders.issueStatus = 'ReturnReceived' THEN 0 WHEN db_daraz_orders.issueStatus = 'Returned' THEN db_stock.cost_price * - 1 WHEN (db_daraz_orders.issueStatus = 'Issued' AND db_daraz_orders.Paid > 0 ) THEN db_daraz_orders.Paid - db_stock.cost_price WHEN (db_daraz_orders.issueStatus = 'Issued' AND (IFNULL(db_daraz_orders.Paid, 0)= 0 )) THEN db_stock.cost_price * - 1 END, 0 ) AS profitNew, IFNULL(db_daraz_orders.Paid, 0) AS Paid FROM db_daraz_orders INNER JOIN db_items ON ( db_daraz_orders.SellerSKU = db_items.sku ) INNER JOIN db_stock ON (db_items.id = db_stock.item_id) INNER JOIN db_category ON ( db_category.id = db_items.category_id ) INNER JOIN db_color ON (db_color.id = db_items.color_id) INNER JOIN db_size ON (db_size.id = db_items.size_id) ORDER BY Createdat DESC ;");
		$res['ord_info'] = $query->result();

		echo json_encode($res);
	}

	public function order_revenue_return_details_get(){
		$to_date = $this->input->post('to_date');
		$from_date = $this->input->post('from_date');

		$query=$this->db->query("SELECT db_daraz_orders.id, db_daraz_orders.OrderNumber , db_daraz_orders.SellerSKU , IFNULL(db_items.sku, ' - ') AS barcode , db_items.item_name , IFNULL(db_color.color, ' - ') AS item_colour , IFNULL(db_size.size, ' - ') AS item_size , IFNULL(db_category.category_name, ' - ') AS cat_name , db_daraz_orders.issueStatus , db_daraz_orders.Createdat , IFNULL( CASE WHEN db_daraz_orders.issueStatus = 'ReturnReceived' THEN 0 WHEN db_daraz_orders.issueStatus = 'Returned' THEN db_stock.cost_price * - 1 WHEN (db_daraz_orders.issueStatus = 'Issued' AND db_daraz_orders.Paid > 0 ) THEN db_daraz_orders.Paid - db_stock.cost_price WHEN (db_daraz_orders.issueStatus = 'Issued' AND (IFNULL(db_daraz_orders.Paid, 0)= 0 )) THEN db_stock.cost_price * - 1 END, 0 ) AS profitNew , IFNULL(db_daraz_orders.Paid, 0) AS Paid FROM db_daraz_orders INNER JOIN db_items ON (db_daraz_orders.SellerSKU = db_items.sku) INNER JOIN db_stock ON (db_items.id = db_stock.item_id) INNER JOIN db_category ON (db_category.id = db_items.category_id) INNER JOIN db_color ON (db_color.id = db_items.color_id) INNER JOIN db_size ON (db_size.id = db_items.size_id) WHERE return_status = '1' AND (DATE(db_daraz_orders.Createdat) BETWEEN '$from_date' AND '$to_date') ORDER BY Createdat DESC;");
		$res['ord_info'] = $query->result();	
		echo json_encode($res);
	}

	public function order_number_details_get(){
		$input_text = $this->input->post('input_text');

		$query=$this->db->query("SELECT db_daraz_orders.id, db_daraz_orders.OrderNumber , db_daraz_orders.SellerSKU , IFNULL(db_items.sku, ' - ') AS barcode , db_items.item_name , IFNULL(db_color.color, ' - ') AS item_colour , IFNULL(db_size.size, ' - ') AS item_size , IFNULL(db_category.category_name, ' - ') AS cat_name , db_daraz_orders.issueStatus , db_daraz_orders.Createdat , IFNULL( CASE WHEN db_daraz_orders.issueStatus = 'ReturnReceived' THEN 0 WHEN db_daraz_orders.issueStatus = 'Returned' THEN db_stock.cost_price * - 1 WHEN (db_daraz_orders.issueStatus = 'Issued' AND db_daraz_orders.Paid > 0 ) THEN db_daraz_orders.Paid - db_stock.cost_price WHEN (db_daraz_orders.issueStatus = 'Issued' AND (IFNULL(db_daraz_orders.Paid, 0)= 0 )) THEN db_stock.cost_price * - 1 END, 0 ) AS profitNew , IFNULL(db_daraz_orders.Paid, 0) AS Paid FROM db_daraz_orders INNER JOIN db_items ON (db_daraz_orders.SellerSKU = db_items.sku) INNER JOIN db_stock ON (db_items.id = db_stock.item_id) INNER JOIN db_category ON (db_category.id = db_items.category_id) INNER JOIN db_color ON (db_color.id = db_items.color_id) INNER JOIN db_size ON (db_size.id = db_items.size_id) WHERE db_daraz_orders.OrderNumber='$input_text' ORDER BY Createdat DESC;");
		$res['ord_info'] = $query->result();

		echo json_encode($res);
	}
	

	public function get_profit_loss_report(){
		echo json_encode($this->reports->get_profit_loss_report());
	}
	public function get_profit_by_item(){
		echo $this->reports->get_profit_by_item();
	}
	public function get_profit_by_invoice(){
		echo $this->reports->get_profit_by_invoice();
	}

	//Summary report
	public function stock(){
		$this->permission_check('stock_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('stock_report');
		$this->load->view('report-stock', $data);
	}
	/*Stock Report*/
	public function show_stock_report(){
		echo $this->reports->show_stock_report();
	}
	public function brand_wise_stock(){
		echo $this->reports->brand_wise_stock();
	}
	//Item Sales Report 
	public function item_sales(){
		$this->permission_check('item_sales_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('item_sales_report');
		$this->load->view('report-sales-item', $data);
	}
	public function show_item_sales_report(){
		echo $this->reports->show_item_sales_report();
	}
	//Item purchase Report 
	public function item_purchase(){
		$this->permission_check('item_purchase_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('item_purchase_report');
		$this->load->view('report-purchase-item', $data);
	}
	public function show_item_purchase_report(){
		echo $this->reports->show_item_purchase_report();
	}
	//Purchase Payments report
	public function purchase_payments(){
		$this->permission_check('purchase_payments_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('purchase_payments_report');
		$this->load->view('report-purchase-payments', $data);
	}
	public function show_purchase_payments_report(){
		echo $this->reports->show_purchase_payments_report();
	}
	public function supplier_payments_report(){
		echo $this->reports->supplier_payments_report();
		echo $this->reports->supplier_payments_report_2();
	}

	//Sales Payments report
	public function sales_payments(){
		$this->permission_check('sales_payments_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('sales_payments_report');
		$this->load->view('report-sales-payments', $data);
	}
	public function show_sales_payments_report(){
		echo $this->reports->show_sales_payments_report();
	}
	public function customer_payments_report(){
		echo $this->reports->customer_payments_report();
		echo $this->reports->customer_payments_report_2();
	}
	//Expired Items Report 
	public function expired_items(){
		$this->permission_check('expired_items_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('expired_items_report');
		$this->load->view('report-expired-items', $data);
	}
	public function show_expired_items_report(){
		echo $this->reports->show_expired_items_report();
	}

	public function stock_analysis()
	{
		$this->permission_check('stock_report');
		$data=$this->data;
		$data['page_title']='Stock Analysis';
		$this->load->view('stock_analysis',$data);
	}

	public function stock_details_get(){
		$category_id = $this->input->post('category_id');
		$filter_none_qty = $this->input->post('filter_none_qty');
		if($filter_none_qty=='0'){
			if($category_id=='0'){
				$query=$this->db->query("SELECT db_purchase.id, db_purchase.purchase_code, db_items.item_code, db_items.item_name, db_items.sku, db_category.category_code, db_category.category_name, SUM(db_purchaseitems.purchase_qty) AS grn_qty, db_stock.stock_qty, ( SUM(db_purchaseitems.purchase_qty) - db_stock.stock_qty ) AS sold_qty, db_purchase.purchase_date, DATEDIFF( CURDATE(), db_purchase.purchase_date ) AS StockinDays FROM db_purchase INNER JOIN db_purchaseitems ON ( db_purchase.id = db_purchaseitems.purchase_id ) INNER JOIN db_category ON ( db_category.id = db_purchaseitems.category_id ) INNER JOIN db_stock ON ( db_purchaseitems.item_id = db_stock.item_id ) INNER JOIN db_items ON ( db_items.id = db_purchaseitems.item_id ) WHERE db_stock.stock_qty > 0 GROUP BY db_purchaseitems.sku ORDER BY DATEDIFF( CURDATE(), db_purchase.purchase_date ) DESC, db_items.item_name ASC");
			} else {
				$query=$this->db->query("SELECT db_purchase.id , db_purchase.purchase_code , db_items.item_code , db_items.item_name , db_items.sku , db_category.category_code , db_category.category_name , SUM(db_purchaseitems.purchase_qty) AS grn_qty , db_stock.stock_qty , (SUM(db_purchaseitems.purchase_qty)- db_stock.stock_qty) AS sold_qty , db_purchase.purchase_date , DATEDIFF(CURDATE(), db_purchase.purchase_date) AS StockinDays FROM db_purchase INNER JOIN db_purchaseitems ON (db_purchase.id = db_purchaseitems.purchase_id) INNER JOIN db_category ON (db_category.id = db_purchaseitems.category_id) INNER JOIN db_stock ON (db_purchaseitems.item_id = db_stock.item_id) INNER JOIN db_items ON (db_items.id = db_purchaseitems.item_id) INNER JOIN db_color ON (db_color.id = db_purchaseitems.color_id) INNER JOIN db_size ON (db_size.id = db_purchaseitems.size_id) WHERE db_stock.stock_qty>0 AND db_purchaseitems.category_id='$category_id' GROUP BY db_purchaseitems.sku ORDER BY DATEDIFF(CURDATE(), db_purchase.purchase_date) DESC, db_items.item_name ASC");
			}
		}else{
			if($category_id=='0'){
				$query=$this->db->query("SELECT db_purchase.id , db_purchase.purchase_code , db_items.item_code , db_items.item_name , db_items.sku , db_category.category_code , db_category.category_name , SUM(db_purchaseitems.purchase_qty) AS grn_qty , db_stock.stock_qty , (SUM(db_purchaseitems.purchase_qty)- db_stock.stock_qty) AS sold_qty , db_purchase.purchase_date , DATEDIFF(CURDATE(), db_purchase.purchase_date) AS StockinDays FROM db_purchase INNER JOIN db_purchaseitems ON (db_purchase.id = db_purchaseitems.purchase_id) INNER JOIN db_category ON (db_category.id = db_purchaseitems.category_id) INNER JOIN db_stock ON (db_purchaseitems.item_id = db_stock.item_id) INNER JOIN db_items ON (db_items.id = db_purchaseitems.item_id) INNER JOIN db_color ON (db_color.id = db_purchaseitems.color_id) INNER JOIN db_size ON (db_size.id = db_purchaseitems.size_id) GROUP BY db_purchaseitems.sku ORDER BY DATEDIFF(CURDATE(), db_purchase.purchase_date) DESC, db_items.item_name ASC");
			} else {
				$query=$this->db->query("SELECT db_purchase.id , db_purchase.purchase_code , db_items.item_code , db_items.item_name , db_items.sku , db_category.category_code , db_category.category_name , SUM(db_purchaseitems.purchase_qty) AS grn_qty , db_stock.stock_qty , (SUM(db_purchaseitems.purchase_qty)- db_stock.stock_qty) AS sold_qty , db_purchase.purchase_date , DATEDIFF(CURDATE(), db_purchase.purchase_date) AS StockinDays FROM db_purchase INNER JOIN db_purchaseitems ON (db_purchase.id = db_purchaseitems.purchase_id) INNER JOIN db_category ON (db_category.id = db_purchaseitems.category_id) INNER JOIN db_stock ON (db_purchaseitems.item_id = db_stock.item_id) INNER JOIN db_items ON (db_items.id = db_purchaseitems.item_id) INNER JOIN db_color ON (db_color.id = db_purchaseitems.color_id) INNER JOIN db_size ON (db_size.id = db_purchaseitems.size_id) WHERE db_purchaseitems.category_id='$category_id' GROUP BY db_purchaseitems.sku ORDER BY DATEDIFF(CURDATE(), db_purchase.purchase_date) DESC, db_items.item_name ASC");
			}
		}
		$res['st_info'] = $query->result();

		echo json_encode($res);
	}

	
}

