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

class Reports_model extends CI_Model {
    
    public function __construct() {
        parent::__construct();
        $this->db = $this->load->database('default', TRUE);
        $CI =& get_instance();
        $CI->db =& $this->db;
    }

    public function generate_b2b($start_date,$end_date){
        return $this->db->select('sale_invoice.*,contact_user.gstn,contact_user_shipping.shipping_state,invoice_item_detail.gst_rate,sum(invoice_item_detail.amount) taxable_amount')
                        ->from('sale_invoice sale_invoice')
                        ->join('invoice_item_detail invoice_item_detail','sale_invoice.sale_invoice_id = invoice_item_detail.sale_invoice_id')
                        ->join('contact_user contact_user','sale_invoice.customer_id = contact_user.id')
                        ->join('contact_user_shipping contact_user_shipping','contact_user.id = contact_user_shipping.id_contact_user')
                        ->where("contact_user.contact_type = 1")
                        ->where("contact_user.gstn != ''")
                        ->where("sale_invoice.invoice_date BETWEEN '".$start_date."' AND '".$end_date."'")
                        ->group_by("sale_invoice.invoice_no,invoice_item_detail.gst_rate")
                        ->get()
                        ->result();
    }

    public function generate_b2cl($start_date,$end_date){
        return $this->db->select('sale_invoice.*,contact_user.gstn,contact_user_shipping.shipping_state,invoice_item_detail.gst_rate,sum(invoice_item_detail.amount) taxable_amount')
                        ->from('sale_invoice sale_invoice')
                        ->join('invoice_item_detail invoice_item_detail','sale_invoice.sale_invoice_id = invoice_item_detail.sale_invoice_id')
                        ->join('contact_user contact_user','sale_invoice.customer_id = contact_user.id')
                        ->join('contact_user_shipping contact_user_shipping','contact_user.id = contact_user_shipping.id_contact_user')
                        ->where("contact_user.contact_type = 1")
                        ->where("contact_user.gstn = ''")
                        ->where("sale_invoice.total > 250000")
                        ->where("sale_invoice.invoice_date BETWEEN '".$start_date."' AND '".$end_date."'")
                        ->group_by("sale_invoice.invoice_no,invoice_item_detail.gst_rate")
                        ->get()
                        ->result();
    }

    public function generate_b2cs($start_date,$end_date){
    	return $this->db->select('sale_invoice.*,contact_user.gstn,contact_user_shipping.shipping_state,invoice_item_detail.gst_rate,sum(invoice_item_detail.amount) taxable_amount')
    					->from('sale_invoice sale_invoice')
                        ->join('invoice_item_detail invoice_item_detail','sale_invoice.sale_invoice_id = invoice_item_detail.sale_invoice_id')
    					->join('contact_user contact_user','sale_invoice.customer_id = contact_user.id')
    					->join('contact_user_shipping contact_user_shipping','contact_user.id = contact_user_shipping.id_contact_user')
                        ->where("contact_user.contact_type = 1")
                        ->where("contact_user.gstn = ''")
                        ->where("sale_invoice.total <= 250000")
    					->where("sale_invoice.invoice_date BETWEEN '".$start_date."' AND '".$end_date."'")
    					->group_by("sale_invoice.invoice_no,invoice_item_detail.gst_rate")
    					->get()
    					->result();
    }

    public function generate_purchase($start_date,$end_date){
        return $this->db->select('purchase_bill.*,contact_user.gstn,contact_user.contact_display_name,contact_user_shipping.shipping_state')
                        ->from('purchase_bill purchase_bill')
                        ->join('contact_user contact_user','purchase_bill.vendor_id = contact_user.id')
                        ->join('contact_user_shipping contact_user_shipping','contact_user.id = contact_user_shipping.id_contact_user')
                        ->where("contact_user.contact_type = 2")
                        ->where("contact_user.gstn != ''")
                        ->where("purchase_bill.bill_date BETWEEN '".$start_date."' AND '".$end_date."'")
                        ->group_by("purchase_bill.bill_no")
                        ->get()
                        ->result();
    }

    public function generate_customer_sales($start_date,$end_date,$customer=null) {
        if (!empty($customer)) {
            return $this->db->select('sale_invoice.*,sum(invoice_payment.amount) payment,contact_user.name')
                        ->from('sale_invoice as sale_invoice')
                        ->join('invoice_payment as invoice_payment','sale_invoice.sale_invoice_id = invoice_payment.sale_invoice_id','left')
                        ->join('contact_user as contact_user','sale_invoice.customer_id = contact_user.id')
                        ->where('sale_invoice.customer_id',$customer)
                        ->where("sale_invoice.invoice_date BETWEEN '".$start_date."' AND '".$end_date."'")
                        ->group_by('sale_invoice.sale_invoice_id')
                        ->order_by('sale_invoice.due_date','DESC')
                        ->get()
                        ->result();
        } else {
            return $this->db->select('sale_invoice.*,sum(invoice_payment.amount) payment,contact_user.name')
                        ->from('sale_invoice as sale_invoice')
                        ->join('invoice_payment as invoice_payment','sale_invoice.sale_invoice_id = invoice_payment.sale_invoice_id','left')
                        ->join('contact_user as contact_user','sale_invoice.customer_id = contact_user.id')
                        ->where("sale_invoice.invoice_date BETWEEN '".$start_date."' AND '".$end_date."'")
                        ->group_by('sale_invoice.sale_invoice_id')
                        ->order_by('sale_invoice.due_date','DESC')
                        ->get()
                        ->result();
        }
        
    }

    public function generate_item_sales($start_date,$end_date,$item=null) {
        if (!empty($item)) {
            return $this->db->select('invoice_item_detail.*,contact_user.name,sale_invoice.invoice_date,sale_invoice.invoice_no')
                        ->from('invoice_item_detail as invoice_item_detail')
                        ->join('sale_invoice as sale_invoice','invoice_item_detail.sale_invoice_id = sale_invoice.sale_invoice_id','left')
                        ->join('contact_user as contact_user','sale_invoice.customer_id = contact_user.id')
                        ->where("sale_invoice.invoice_date BETWEEN '".$start_date."' AND '".$end_date."'")
                        ->where('invoice_item_detail.item_id',$item)
                        ->group_by('sale_invoice.sale_invoice_id')
                        ->order_by('sale_invoice.due_date','DESC')
                        ->get()
                        ->result();
        } else {
             return $this->db->select('invoice_item_detail.*,contact_user.name,sale_invoice.invoice_date,sale_invoice.invoice_no')
                        ->from('invoice_item_detail as invoice_item_detail')
                        ->join('sale_invoice as sale_invoice','invoice_item_detail.sale_invoice_id = sale_invoice.sale_invoice_id','left')
                        ->join('contact_user as contact_user','sale_invoice.customer_id = contact_user.id')
                        ->where("sale_invoice.invoice_date BETWEEN '".$start_date."' AND '".$end_date."'")
                        ->group_by('sale_invoice.sale_invoice_id')
                        ->order_by('sale_invoice.due_date','DESC')
                        ->get()
                        ->result();
        }
        
    }

    public function generate_customer_balance($start_date,$end_date,$customer=null) {
        if (!empty($customer)) {
            return $this->db->select('payment_received.*,GROUP_CONCAT(sale_invoice.invoice_no) invoices,bank_accounts.account_name')
                        ->from('payment_received as payment_received')
                        ->join('invoice_payment as invoice_payment','payment_received.payment_received_id = invoice_payment.payment_received_id','left')
                        ->join('sale_invoice as sale_invoice','sale_invoice.sale_invoice_id = invoice_payment.sale_invoice_id','left')
                        ->join('bank_accounts as bank_accounts','payment_received.deposit_to = bank_accounts.account_id','left')
                        ->where("payment_received.payment_date BETWEEN '".$start_date."' AND '".$end_date."'")
                        ->where("payment_received.customer_id='".$customer."'")
                        ->group_by('payment_received.payment_received_id')
                        ->get()
                        ->result();
        } else {
            return $this->db->select('payment_received.*,GROUP_CONCAT(sale_invoice.invoice_no) invoices,bank_accounts.account_name')
                        ->from('payment_received as payment_received')
                        ->join('invoice_payment as invoice_payment','payment_received.payment_received_id = invoice_payment.payment_received_id','left')
                        ->join('sale_invoice as sale_invoice','sale_invoice.sale_invoice_id = invoice_payment.sale_invoice_id','left')
                        ->join('bank_accounts as bank_accounts','payment_received.deposit_to = bank_accounts.account_id','left')
                        ->where("payment_received.payment_date BETWEEN '".$start_date."' AND '".$end_date."'")
                        ->group_by('payment_received.payment_received_id')
                        ->get()
                        ->result();
        }
        
    }

    public function customer_invoice($start_date,$end_date,$customer=null){
        if (!empty($customer)) {
            return $this->db->select('sale_invoice.*,invoice_payment.sale_invoice_id paid_invoice')
                        ->from('sale_invoice as sale_invoice')
                        ->join('invoice_payment as invoice_payment','sale_invoice.sale_invoice_id = invoice_payment.sale_invoice_id','left')
                        ->where("sale_invoice.invoice_date BETWEEN '".$start_date."' AND '".$end_date."'")
                        ->where("sale_invoice.customer_id='".$customer."'")
                        ->group_by('sale_invoice.invoice_no')
                        ->get()
                        ->result();
        } else {
            return $this->db->select('sale_invoice.*,invoice_payment.sale_invoice_id paid_invoice')
                        ->from('sale_invoice as sale_invoice')
                        ->join('invoice_payment as invoice_payment','sale_invoice.sale_invoice_id = invoice_payment.sale_invoice_id','left')
                        ->where("sale_invoice.invoice_date BETWEEN '".$start_date."' AND '".$end_date."'")
                        ->group_by('sale_invoice.invoice_no')
                        ->get()
                        ->result();
        }
    }

    public function customer_payment($start_date,$end_date,$customer=null){
        if (!empty($customer)) {
            return $this->db->select('payment_received.*,bank_accounts.account_name')
                        ->from('payment_received as payment_received')
                        ->join('bank_accounts as bank_accounts','payment_received.deposit_to = bank_accounts.account_id','left')
                        ->where("payment_received.payment_date BETWEEN '".$start_date."' AND '".$end_date."'")
                        ->where("payment_received.customer_id='".$customer."'")
                        ->get()
                        ->result();
        } else {
            return $this->db->select('payment_received.*,bank_accounts.account_name')
                        ->from('payment_received as payment_received')
                        ->join('bank_accounts as bank_accounts','payment_received.deposit_to = bank_accounts.account_id','left')
                        ->where("payment_received.payment_date BETWEEN '".$start_date."' AND '".$end_date."'")
                        ->get()
                        ->result();
        }
    }

    public function invoice_details($start_date,$end_date){
        return $this->db->select('sale_invoice.*,sum(invoice_payment.amount) payment,contact_user.name')
                        ->from('sale_invoice as sale_invoice')
                        ->join('invoice_payment as invoice_payment','sale_invoice.sale_invoice_id = invoice_payment.sale_invoice_id','left')
                        ->join('contact_user as contact_user','sale_invoice.customer_id = contact_user.id')
                        ->where("sale_invoice.invoice_date BETWEEN '".$start_date."' AND '".$end_date."'")
                        ->group_by('sale_invoice.sale_invoice_id')
                        ->get()
                        ->result();
    }

}?>