<?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."'")
                        ->where("sale_invoice.status = 1")
                        ->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."'")
                        ->where("sale_invoice.status = 1")
                        ->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."'")
                        ->where("sale_invoice.status = 1")
    					->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 customer_combine_sale($start,$end){
        return $this->db->select('contact_user.*,count(sale_invoice.sale_invoice_id) no_invoice,sum(sale_invoice.total) invoice_amount,sum(sale_invoice.sub_total) invoice_actual_amount,sum(credit_note.total) credit_amount')
                        ->from('contact_user contact_user')
                        ->join('sale_invoice sale_invoice','contact_user.id = sale_invoice.customer_id')
                        ->join('credit_note credit_note','sale_invoice.invoice_no = credit_note.invoice_no','left')
                        ->where("sale_invoice.invoice_date BETWEEN '".$start."' AND '".$end."'")
                        ->where("sale_invoice.status = 1")
                        ->group_by('contact_user.id')
                        ->get()
                        ->result();
    }

    public function generate_customer_invoice_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."'")
                        ->where("sale_invoice.status = 1")
                        ->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."'")
                        ->where("sale_invoice.status = 1")
                        ->group_by('sale_invoice.sale_invoice_id')
                        ->order_by('sale_invoice.due_date','DESC')
                        ->get()
                        ->result();
        }
    }

    public function get_credit_note_list($start_date,$end_date,$customer=null){
        if (!empty($customer)) {
            return $this->db->select('credit_note.*,contact_user.name')
                            ->from('credit_note credit_note')
                            ->join('contact_user as contact_user','credit_note.customer_id = contact_user.id')
                            ->where('credit_note.customer_id',$customer)
                            ->where("credit_note.credit_date BETWEEN '".$start_date."' AND '".$end_date."'")
                            ->get()
                            ->result();
        } else {
            return $this->db->select('credit_note.*,contact_user.name')
                            ->from('credit_note credit_note')
                            ->join('contact_user as contact_user','credit_note.customer_id = contact_user.id')
                            ->where("credit_note.credit_date BETWEEN '".$start_date."' AND '".$end_date."'")
                            ->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)
                        ->where("sale_invoice.status = 1")
                        ->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."'")
                        ->where("sale_invoice.status = 1")
                        ->group_by('sale_invoice.sale_invoice_id')
                        ->order_by('sale_invoice.due_date','DESC')
                        ->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."'")
                        ->where("sale_invoice.status = 1")
                        ->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."'")
                        ->where("sale_invoice.status = 1")
                        ->group_by('sale_invoice.invoice_no')
                        ->get()
                        ->result();
        }
    }

    public function customer_invoice_all($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."'")
                    ->where("sale_invoice.status = 1")
                    ->group_by('sale_invoice.invoice_no')
                    ->get()
                    ->result();
    }

    public function vendor_bill_all($vendor){
        return $this->db->select('purchase_bill.*,bill_payment.purchase_bill_id paid_bills')
                    ->from('purchase_bill as purchase_bill')
                    ->join('bill_payment as bill_payment','purchase_bill.purchase_bill_id = bill_payment.purchase_bill_id','left')
                    // ->where("purchase_bill.invoice_date BETWEEN '".$start_date."' AND '".$end_date."'")
                    ->where("purchase_bill.vendor_id='".$vendor."'")
                    ->group_by('purchase_bill.bill_no')
                    ->get()
                    ->result();
    }

    public function customer_payment_all($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();
    }

    public function vendor_payment_all($vendor){
            return $this->db->select('payment_made.*,bank_accounts.account_name')
                        ->from('payment_made as payment_made')
                        ->join('bank_accounts as bank_accounts','payment_made.paid_through = bank_accounts.account_id','left')
                        // ->where("payment_made.payment_date BETWEEN '".$start_date."' AND '".$end_date."'")
                        ->where("payment_made.vendor_id='".$vendor."'")
                        ->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 customer_credit_note_all($customer){
        return $this->db->select('credit_note.*')
                        ->from('credit_note as credit_note')
                        ->where("credit_note.customer_id='".$customer."'")
                        ->get()
                        ->result();
    }

    public function vendor_debit_note_all($vendor){
        return $this->db->select('devid_note.*')
                        ->from('devid_note as devid_note')
                        ->where("devid_note.vendor_id='".$vendor."'")
                        ->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."'")
                        ->where("sale_invoice.status = 1")
                        ->group_by('sale_invoice.sale_invoice_id')
                        ->get()
                        ->result();
    }

    public function bill_details($start_date,$end_date){
        return $this->db->select('purchase_bill.*,sum(bill_payment.payment) payment,contact_user.name')
                        ->from('purchase_bill as purchase_bill')
                        ->join('bill_payment as bill_payment','purchase_bill.purchase_bill_id = bill_payment.purchase_bill_id','left')
                        ->join('contact_user as contact_user','purchase_bill.vendor_id = contact_user.id')
                        ->where("purchase_bill.bill_date BETWEEN '".$start_date."' AND '".$end_date."'")
                        ->group_by('purchase_bill.purchase_bill_id')
                        ->get()
                        ->result();
    }

    public function payment_receive_list($start_date,$end_date,$customer){
        if (!empty($customer)) {
            return $this->db->select('payment_received.*,GROUP_CONCAT(sale_invoice.invoice_no) invoices,contact_user.name,sum(invoice_payment.amount) invoice_paid_amount')
                        ->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','invoice_payment.sale_invoice_id=sale_invoice.sale_invoice_id','left')
                        ->join('contact_user as contact_user','payment_received.customer_id = contact_user.id')
                        ->where('payment_received.customer_id',$customer)
                        ->where("payment_received.payment_date BETWEEN '".$start_date."' AND '".$end_date."'")
                        ->where("sale_invoice.status = 1")
                        ->group_by('payment_received.payment_received_id')
                        ->get()
                        ->result();
        } else {
            return $this->db->select('payment_received.*,GROUP_CONCAT(sale_invoice.invoice_no) invoices,contact_user.name,sum(invoice_payment.amount) invoice_paid_amount')
                        ->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','invoice_payment.sale_invoice_id=sale_invoice.sale_invoice_id','left')
                        ->join('contact_user as contact_user','payment_received.customer_id = contact_user.id')
                        ->where("payment_received.payment_date BETWEEN '".$start_date."' AND '".$end_date."'")
                        ->where("sale_invoice.status = 1")
                        ->group_by('payment_received.payment_received_id')
                        ->get()
                        ->result();
        }
    }

    public function credit_note_list($start_date,$end_date,$customer){
        if (!empty($customer)) {
            return $this->db->select('credit_note.*,contact_user.name')
                            ->from('credit_note credit_note')
                            ->join('contact_user contact_user','credit_note.customer_id=contact_user.id')
                            ->where('credit_note.customer_id',$customer)
                            ->where("credit_note.credit_date BETWEEN '".$start_date."' AND '".$end_date."'")
                            ->group_by('credit_note.credit_note_no')
                            ->get()
                            ->result();
        } else {
            return $this->db->select('credit_note.*,contact_user.name')
                            ->from('credit_note credit_note')
                            ->join('contact_user contact_user','credit_note.customer_id=contact_user.id')
                            ->where("credit_note.credit_date BETWEEN '".$start_date."' AND '".$end_date."'")
                            ->group_by('credit_note.credit_note_no')
                            ->get()
                            ->result();
        }
    }

    public function debit_note_list($start_date,$end_date,$customer){
        if (!empty($customer)) {
            return $this->db->select('devid_note.*,contact_user.name')
                            ->from('devid_note devid_note')
                            ->join('contact_user contact_user','devid_note.vendor_id=contact_user.id')
                            ->where('devid_note.vendor_id',$customer)
                            ->where("devid_note.devid_date BETWEEN '".$start_date."' AND '".$end_date."'")
                            ->group_by('devid_note.devid_note_no')
                            ->get()
                            ->result();
        } else {
            return $this->db->select('devid_note.*,contact_user.name')
                            ->from('devid_note devid_note')
                            ->join('contact_user contact_user','devid_note.vendor_id=contact_user.id')
                            ->where("devid_note.devid_date BETWEEN '".$start_date."' AND '".$end_date."'")
                            ->group_by('devid_note.devid_note_no')
                            ->get()
                            ->result();
        }
    }

    // public function customer_balance_report($start,$end,$customer=null){
    //     return $this->db->select('contact_user.id,contact_user.name,sum(sale_invoice.total) inv_total,sum(payment_received.amount) rcv_amount,sum(credit_note.total) crdt_total')
    //                     ->from('contact_user contact_user')
    //                     ->join('sale_invoice sale_invoice','contact_user.id = sale_invoice.customer_id','left')
    //                     ->join('invoice_payment invoice_payment','sale_invoice.sale_invoice_id = invoice_payment.sale_invoice_id')
    //                     ->join('payment_received payment_received','contact_user.id = payment_received.customer_id','left')
    //                     ->join('credit_note credit_note','contact_user.id = credit_note.customer_id','left')
    //                     ->where('credit_note.is_adjusted',0)
    //                     ->where('invoice_payment.sale_invoice_id IS NULL')
    //                     ->group_by('contact_user.id')
    //                     ->get()
    //                     ->result();
    // }
    public function customer_nonpaid_invoice(){
        return $this->db->select('contact_user.id,contact_user.name,sum(IF(invoice_payment.sale_invoice_id IS NULL, sale_invoice.total,0)) due_total,sum(invoice_payment.amount) invoice_paid_total,sum(sale_invoice.total) inv_total')
                        ->from('contact_user contact_user')
                        ->join('sale_invoice sale_invoice','contact_user.id = sale_invoice.customer_id','left')
                        ->join('invoice_payment invoice_payment','sale_invoice.sale_invoice_id = invoice_payment.sale_invoice_id','left')
                        // ->where('invoice_payment.sale_invoice_id IS NULL')
                        ->where("sale_invoice.status = 1")
                        ->group_by('contact_user.id')
                        ->get()
                        ->result();
    }

    public function vendor_nonpaid_bills(){
        return $this->db->select('contact_user.id,contact_user.name,sum(IF(bill_payment.purchase_bill_id IS NULL, purchase_bill.total,0)) due_total,sum(bill_payment.payment) bill_paid_total,sum(purchase_bill.total) bill_total')
                        ->from('contact_user contact_user')
                        ->join('purchase_bill purchase_bill','contact_user.id = purchase_bill.vendor_id','left')
                        ->join('bill_payment bill_payment','purchase_bill.purchase_bill_id = bill_payment.purchase_bill_id','left')
                        // ->where('invoice_payment.sale_invoice_id IS NULL')
                        ->group_by('contact_user.id')
                        ->get()
                        ->result();
    }

    public function customer_unused_credit_note(){
        return $this->db->select('contact_user.id,contact_user.name,sum(IF(credit_note.is_adjusted =0,credit_note.total,0)) crdt_total,sum(IF(credit_note.is_adjusted =1,credit_note.total,0)) crdt_used')
                        ->from('contact_user contact_user')
                        ->join('credit_note credit_note','contact_user.id = credit_note.customer_id','left')
                        // ->where('credit_note.is_adjusted',0)
                        ->group_by('contact_user.id')
                        ->get()
                        ->result();
    }

    public function vendor_unused_dabit_note(){
        return $this->db->select('contact_user.id,contact_user.name,sum(IF(devid_note.is_adjusted =0,devid_note.total,0)) dbt_total,sum(IF(devid_note.is_adjusted =1,devid_note.total,0)) dbt_used')
                        ->from('contact_user contact_user')
                        ->join('devid_note devid_note','contact_user.id = devid_note.vendor_id','left')
                        // ->where('devid_note.is_adjusted',0)
                        ->group_by('contact_user.id')
                        ->get()
                        ->result();
    }

    public function customer_unused_payment(){
        return $this->db->select('contact_user.id,contact_user.name,sum(payment_received.amount) rcv_amount')
                        ->from('contact_user contact_user')
                        ->join('payment_received payment_received','contact_user.id = payment_received.customer_id','left')
                        ->group_by('contact_user.id')
                        ->get()
                        ->result();
    }

    public function vendor_unused_payment(){
        return $this->db->select('contact_user.id,contact_user.name,sum(payment_made.amount) paid_amount')
                        ->from('contact_user contact_user')
                        ->join('payment_made payment_made','contact_user.id = payment_made.vendor_id','left')
                        ->group_by('contact_user.id')
                        ->get()
                        ->result();
    }

    public function expence_list($start,$end,$expenceid=null){
        if (!empty($expenceid)) {
            return $this->db->select('expence_account.*,expences.*')
                            ->from('expence_account expence_account')
                            ->join('expences expences','expence_account.expence_acc_id = expences.expence_acc_id','left')
                            ->where('expences.expence_id',$expenceid)
                            ->group_by('expence_account.expence_acc_id')
                            ->get()
                            ->result();
        } else {
            return $this->db->select('expence_account.*,sum(expences.amount) paid_amount')
                            ->from('expence_account expence_account')
                            ->join('expences expences','expence_account.expence_acc_id = expences.expence_acc_id','left')
                            ->group_by('expence_account.expence_acc_id')
                            ->get()
                            ->result();
        }
    }

    public function bank_report_summery($start,$end){
        
    }

    public function metal_stock_report($start,$end){
        return $this->db->select()
                        ->from('raw_material_stock')
                        ->where('material_item_id',1)
                        ->order_by('raw_material_stock_id','DESC')
                        ->get()
                        ->result();

    }

    public function ston_stock_report($start,$end){
        return $this->db->select()
                        ->from('raw_material_stock')
                        ->where('material_item_id',2)
                        ->order_by('raw_material_stock_id','DESC')
                        ->get()
                        ->result();
    }

    public function get_single_record($table,$where,$select='*') {
        return $this->db->select($select)
                        ->from($table)
                        ->where($where)
                        ->get()
                        ->result();
    }

    public function get_closing_stock($start,$end){
        return $this->db->select('items.rate_purchase,SUM(item_stock.qty_sale) sale,SUM(item_stock.qty_purchase) buy,item_stock.item_id')
                        ->from('item_stock item_stock')
                        ->join('items items','item_stock.item_id = items.item_id','left')
                        ->where('item_stock.date_added BETWEEN "'.$start.'" AND "'.$end.'"')
                        ->group_by('item_stock.item_id')
                        ->get()
                        ->result();
    }

    public function get_opening_balance(){
        return $this->db->select('*')
                       ->from('opening_closing_balance_month')
                       ->order_by('id','DESC')
                       ->get()
                       ->row();
    }

    public function insert_data($tablename,$data){
        $this->db->insert($tablename,$data);
        return $this->db->insert_id();
    }

}?>