<?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')
                        ->group_by('invoice_item_detail.item_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')
                        ->group_by('invoice_item_detail.item_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_acc_id',$expenceid)
                            // ->where("expences.expence_date BETWEEN '".$start."' AND '".$end."'")
                            // ->group_by('expence_account.expence_acc_id')
                            ->get()
                            ->result();
        } else {
            return $this->db->select('expence_account.*,sum(expences.amount) paid_amount')
                            ->from('expences expences')
                            ->join('expence_account expence_account','expences.expence_acc_id = expence_account.expence_acc_id','left')
                            ->where("expences.expence_date BETWEEN '".$start."' AND '".$end."'")
                            ->group_by('expence_account.expence_acc_id')
                            ->get()
                            ->result();
        }
    }


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

    public function bank_report_summery($account_id,$start_date,$end_date){
        if ($account_id == 0) {
            $account_cond = '';
        } else {
            $account_cond = "bank_accounts.account_id = '".$account_id."' and ";
        }

        $query = "( SELECT 
                        `bank_accounts`.account_name,
                        `bank_accounts`.account_number,
                        payment_received.amount, 
                        concat('rcv_',payment_received.payment_received_id) trans_id, 
                        payment_received.payment_date as date_trans,
                        payment_received.bank_charges as trans_charges,
                        1 as type,
                        user.contact_display_name,
                        payment_received.note as description
                    from 
                        gst_payment_received payment_received 
                    join 
                        gst_bank_accounts bank_accounts 
                    on 
                        payment_received.deposit_to = bank_accounts.account_id 
                    join
                        gst_contact_user user
                    on 
                        payment_received.customer_id = user.id 
                    where
                        ".$account_cond." payment_received.payment_date between '".$start_date."' and '".$end_date."' ) 

                    UNION 

                    (  SELECT 
                        `bank_accounts`.account_name,
                        `bank_accounts`.account_number,
                        payment_made.amount,
                        concat('pay_',payment_made.payment_made_id) trans_id,
                        payment_made.payment_date as date_trans,
                        0 as trans_charges,
                        0 as type,
                        user.contact_display_name,
                        payment_made.note as description
                    from 
                        gst_payment_made payment_made 
                    join 
                        gst_bank_accounts bank_accounts 
                    on 
                        payment_made.paid_through = bank_accounts.account_id
                    join
                        gst_contact_user user
                    on 
                        payment_made.vendor_id = user.id 
                    where
                        ".$account_cond." payment_made.payment_date between '".$start_date."' and '".$end_date."')

                    UNION

                    (SELECT 
                        `bank_accounts`.account_name,
                        `bank_accounts`.account_number,
                        crd.amount,
                        concat('rcv_',crd.id) trans_id,
                        crd.tran_date as date_trans,
                        0 as trans_charges,
                        1 as type,
                        '' as contact_display_name,
                        crd.notes as description
                    from 
                        gst_bank_accounts bank_accounts 
                    left join
                        gst_bank_diposit crd
                    on 
                        bank_accounts.account_id = crd.to_acc_id
                    where
                        ".$account_cond." crd.amount is not null and crd.date_added between '".$start_date."' and '".$end_date."')

                    UNION

                    (SELECT 
                        `bank_accounts`.account_name,
                        `bank_accounts`.account_number,
                        dbt.amount,
                        concat('pay_',dbt.id) trans_id,
                        dbt.date_added as date_trans,
                        0 as trans_charges,
                        0 as type,
                        '' as contact_display_name,
                        dbt.notes as description
                    from 
                        gst_bank_accounts bank_accounts 
                    left join
                        gst_bank_diposit dbt
                    on 
                        bank_accounts.account_id = dbt.from_acc_id
                    where
                        ".$account_cond." dbt.amount is not null and dbt.date_added between '".$start_date."' and '".$end_date."')
						
						UNION

                    (SELECT 
                        `bank_accounts`.account_name,
                        `bank_accounts`.account_number,
                        exp.amount,
                        concat('pay_',exp.expence_id) trans_id,
                        exp.expence_date as date_trans,
                        0 as trans_charges,
                        0 as type,
                        '' as contact_display_name,
                        exp.note as description
                    from 
                        gst_bank_accounts bank_accounts 
                    left join
                        gst_expences exp
                    on 
                        bank_accounts.account_id = exp.paid_through_acc
                    where
                        ".$account_cond." exp.amount is not null and exp.date_added between '".$start_date."' and '".$end_date."')
                    ";
        return $this->db->query($query)->result();
    }

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

    public function production_expence($start,$end){
        return $this->db->select('sum(production_expence.manufacturing_charge) tot_manufacturing_charge,sum(production_expence.rhodium_charge) tot_rhodium_charge,sum(item_stock.qty_purchase) qty_purchase,items.item_name,items.  manufacturing_charge,items.rhodium_charge,item_stock.date_added')
                        ->from('production_expence production_expence')
                        ->join('item_stock item_stock','item_stock.item_stock_id = production_expence.item_stock_id','left')
                        ->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,item_stock.date_added')
                        ->get()
                        ->result();
    }
    
    public function item_stock($start_date,$end_date){
		
		 $query = "SELECT SUM(gst_item_stock.qty_purchase) purchase,SUM(gst_item_stock.qty_sale) sale,(SUM(gst_item_stock.qty_purchase) - SUM(gst_item_stock.qty_sale)) stock,gst_items.item_name as item_name FROM gst_item_stock,gst_items WHERE gst_item_stock.item_id=gst_items.item_id GROUP BY gst_item_stock.item_id"; 
		 
		 $result1 = $this->db->query($query)->result_array();
		
		return $result1;
						
    }
    
    

}?>