riainvoice/application/modules/payments/models/Mdl_payments.php

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

/*
 * InvoicePlane
 *
 * @author		InvoicePlane Developers & Contributors
 * @copyright	Copyright (c) 2012 - 2017 InvoicePlane.com
 * @license		https://invoiceplane.com/license.txt
 * @link		https://invoiceplane.com
 */

/**
 * Class Mdl_Payments
 */
class Mdl_Payments extends Response_Model
{
    public $table = 'ip_payments';
    public $primary_key = 'ip_payments.payment_id';
    public $validation_rules = 'validation_rules';

    public function default_select()
    {
        $this->db->select("distinct
            SQL_CALC_FOUND_ROWS
            ip_payment_methods.*,
            ip_invoice_amounts.*,
            ip_clients.client_name,
            ip_clients.client_surname,
        	  ip_clients.client_id,
            ip_invoices.invoice_number,
            ip_invoices.invoice_date_created,
            ip_payments.*", false);
    }

    public function default_order_by()
    {
        $this->db->order_by('ip_payments.receipt_no DESC');
        $this->db->group_by('receipt_no');

    }

    public function default_join()
    {
        $this->db->join('ip_invoices', 'ip_invoices.invoice_id = ip_payments.invoice_id');
        $this->db->join('ip_clients', 'ip_clients.client_id = ip_invoices.client_id');
        $this->db->join('ip_invoice_amounts', 'ip_invoice_amounts.invoice_id = ip_invoices.invoice_id');
        $this->db->join('ip_payment_methods', 'ip_payment_methods.payment_method_id = ip_payments.payment_method_id', 'left');
    }

    /**
     * @return array
     */
    public function validation_rules()
    {
        return array(
            'invoice_id' => array(
                'field' => 'invoice_id',
                'label' => trans('invoice'),
                'rules' => 'required'
            ),
            'payment_date' => array(
                'field' => 'payment_date',
                'label' => trans('date'),
                'rules' => 'required'
            ),
            'payment_amount' => array(
                'field' => 'payment_amount',
                'label' => trans('payment'),
                'rules' => 'required|callback_validate_payment_amount'
            ),
            'payment_method_id' => array(
                'field' => 'payment_method_id',
                'label' => trans('payment_method')
            ),
            'payment_note' => array(
                'field' => 'payment_note',
                'label' => trans('note')
            )
        );
    }

    /**
     * @param $amount
     * @return bool
     */
    public function validate_payment_amount($amount)
    {
        $amount = (float)standardize_amount($amount);
        $invoice_id = $this->input->post('invoice_id');
        $payment_id = $this->input->post('payment_id');

        $invoice = $this->db->where('invoice_id', $invoice_id)->get('ip_invoice_amounts')->row();

        if ($invoice == null) {
            return false;
        }

        $invoice_balance = (float)$invoice->invoice_balance;

        if ($payment_id) {
            $payment = $this->db->where('payment_id', $payment_id)->get('ip_payments')->row();

            $invoice_balance = $invoice_balance + (float)$payment->payment_amount;
        }

        $invoice_balance = (float)$invoice_balance;

        if ($amount > $invoice_balance) {
            $this->form_validation->set_message('validate_payment_amount', trans('payment_cannot_exceed_balance'));
            return false;
        }

        return true;
    }



   

    /**
     * @param null $id
     * @param null $db_array
     * @return bool|int|null
     */
    public function save($id = null, $db_array = null)
    {

        //echo "model";
        
        $db_array = ($db_array) ? $db_array : $this->db_array();
        $this->load->model('invoices/mdl_invoice_amounts');
           // echo "sd";
            // die();
        // Save the payment
        $id = parent::save($id, $db_array);

        // echo $id;
        //  die();

        // Recalculate invoice amounts
        $this->mdl_invoice_amounts->calculate($db_array['invoice_id']);

        // Set proper status for the invoice


        $invoice = $this->db->where('invoice_id', $db_array['invoice_id'])->get('ip_invoice_amounts')->row();

        // echo $invoice;
        // die();

        // Calculate sum for payments
        if ($invoice == null) {
            return false;
        }

        $paid = (float)$invoice->invoice_paid;
        $total = (float)$invoice->invoice_total;

        // echo $paid;
        // echo "fd";
        // echo $total;
        // die();

        if ($paid >= $total) {
            $this->db->where('invoice_id', $db_array['invoice_id']);
            $this->db->set('invoice_status_id', 4);
            $this->db->update('ip_invoices');
        }


        // echo $db_array['invoice_id'];
        // die();

        // Recalculate invoice amounts
        $this->mdl_invoice_amounts->calculate($db_array['invoice_id']);

        return $id;
    }

    /**
     * @return array
     */
    public function db_array()
    {
        $db_array = parent::db_array();

        $db_array['payment_date'] = date_to_mysql($db_array['payment_date']);
        $db_array['payment_amount'] = standardize_amount($db_array['payment_amount']);

        return $db_array;
    }

    /**
     * @param null $id
     */
    public function delete($id = null)
    {
        // Get the invoice id before deleting payment
        $this->db->select('invoice_id');
        $this->db->where('payment_id', $id);
        $invoice_id = $this->db->get('ip_payments')->row()->invoice_id;

        // Delete the payment
        parent::delete($id);

        // Recalculate invoice amounts
        $this->load->model('invoices/mdl_invoice_amounts');
        $this->mdl_invoice_amounts->calculate($invoice_id);

        // Change invoice status back to sent
        $this->db->select('invoice_status_id');
        $this->db->where('invoice_id', $invoice_id);
        $invoice = $this->db->get('ip_invoices')->row();

        if ($invoice->invoice_status_id == 4) {
            $this->db->where('invoice_id', $invoice_id);
            $this->db->set('invoice_status_id', 2);
            $this->db->update('ip_invoices');
        }

        $this->load->helper('orphan');
        delete_orphans();
    }

    /**
     * @param null $id
     * @return bool
     */
    public function prep_form($id = null)
    {
        if (!parent::prep_form($id)) {
            return false;
        }

        if (!$id) {
            parent::set_form_value('payment_date', date('Y-m-d'));
        }

        return true;
    }

    /**
     * @param $client_id
     * @return $this
     */
    public function by_client($client_id)
    {
        $this->filter_where('ip_clients.client_id', $client_id);
        return $this;
    }


//    public function Get_Invoicelist($client_id)
//     {
//          $subQuery = 'SELECT ip_quotes.*,ip_users.user_name,ip_users.user_company,
// ip_users.user_address_1, ip_users.user_address_2, ip_users.user_city, ip_users.user_state, 
// ip_users.user_zip, ip_users.user_country, ip_users.user_phone, ip_users.user_fax, 
// ip_users.user_mobile, ip_users.user_email, ip_users.user_web, ip_users.user_vat_id, 
// ip_users.user_tax_code, ip_users.user_subscribernumber, ip_users.user_iban, 
// ip_users.user_gln, ip_users.user_rcc, ip_clients.*, ip_invoice_sumex.*,ip_invoice_items.item_quantity,
// ip_invoice_amounts.invoice_id, ip_invoice_amounts.invoice_amount_id, 
// IFnull(ip_invoice_amounts.invoice_item_subtotal, 0.00) AS invoice_item_subtotal, 
// IFnull(ip_invoice_amounts.invoice_item_tax_total, 0.00) AS invoice_item_tax_total, 
// IFnull(ip_invoice_amounts.invoice_tax_total, 0.00) AS invoice_tax_total, 
// IFnull(ip_invoice_amounts.invoice_total, 0.00) AS invoice_total, 
// IFnull(ip_invoice_amounts.invoice_paid, 0.00) AS invoice_paid, 
// IFnull(ip_invoice_amounts.invoice_balance, 0.00) AS invoice_balance, 
// ip_invoice_amounts.invoice_sign AS invoice_sign, 
// (CASE WHEN ip_invoices.invoice_status_id NOT IN (1, 4) 
// AND DATEDIFF(NOW(), invoice_date_due) > 0 THEN 1 ELSE 0 END) is_overdue, 
// DATEDIFF(NOW(), invoice_date_due) AS days_overdue, (CASE (SELECT COUNT(*) 
// FROM ip_invoices_recurring WHERE ip_invoices_recurring.invoice_id = ip_invoices.invoice_id 
// and ip_invoices_recurring.recur_next_date <> 0000-00-00) WHEN 0 THEN 0 ELSE 1 END) 
// AS invoice_is_recurring, ip_invoices.* FROM ip_invoices 
// JOIN ip_clients ON ip_clients.client_id = ip_invoices.client_id 
// JOIN ip_users ON ip_users.user_id = ip_invoices.user_id 

// JOIN ip_invoice_items ON ip_invoice_items.invoice_id = ip_invoices.invoice_id

// LEFT JOIN ip_invoice_amounts ON ip_invoice_amounts.invoice_id = ip_invoices.invoice_id 
// LEFT JOIN ip_invoice_sumex ON sumex_invoice = ip_invoices.invoice_id 
// LEFT JOIN ip_quotes ON ip_quotes.invoice_id = ip_invoices.invoice_id 
// LEFT JOIN ip_payments ON ip_payments.invoice_id = ip_invoices.invoice_id 
// WHERE ip_invoice_amounts.invoice_balance >0  and 
// ip_clients.client_id=? ORDER BY ip_invoices.invoice_number DESC;';
                    
//              $query = $this->db->query($subQuery,array($client_id));          
        
//         return $query->result();      
// }




 public function Get_Invoicelist($client_id,$fdate,$tdate)
 {
   $this->db->distinct();
   $this->db->select('ip_invoice_amounts.invoice_id,ip_invoice_amounts.invoice_amount_id,
ip_invoice_items.item_quantity,ip_invoice_items.item_description,
IFnull(ip_invoice_amounts.invoice_item_subtotal, 0.00) AS invoice_item_subtotal, 
IFnull(ip_invoice_amounts.invoice_item_tax_total, 0.00) AS invoice_item_tax_total, 
IFnull(ip_invoice_amounts.invoice_tax_total, 0.00) AS invoice_tax_total, 
IFnull(ip_invoice_amounts.invoice_total, 0.00) AS invoice_total, 
IFnull(ip_invoice_amounts.invoice_paid, 0.00) AS invoice_paid, 
IFnull(ip_invoice_amounts.invoice_balance, 0.00) AS invoice_balance, 
ip_invoice_amounts.invoice_sign AS invoice_sign, 
(CASE WHEN ip_invoices.invoice_status_id NOT IN (1, 4) 
AND DATEDIFF(NOW(), invoice_date_due) > 0 THEN 1 ELSE 0 END) is_overdue, 
DATEDIFF(NOW(), invoice_date_due) AS days_overdue, (CASE (SELECT COUNT(*) 
FROM ip_invoices_recurring WHERE ip_invoices_recurring.invoice_id = ip_invoices.invoice_id 
and ip_invoices_recurring.recur_next_date <> 0000-00-00) WHEN 0 THEN 0 ELSE 1 END) 
AS invoice_is_recurring, ip_invoices.*');

$this->db->from('ip_invoices');
$this->db->join('ip_clients','ip_clients.client_id = ip_invoices.client_id');
$this->db->join('ip_invoice_items','ip_invoice_items.invoice_id = ip_invoices.invoice_id');
$this->db->join('ip_invoice_amounts','ip_invoice_amounts.invoice_id = ip_invoices.invoice_id','left');
$this->db->join('ip_invoice_sumex','sumex_invoice = ip_invoices.invoice_id','left');
$this->db->join('ip_quotes','ip_quotes.invoice_id = ip_invoices.invoice_id','left');
$this->db->join('ip_payments','ip_payments.invoice_id = ip_invoices.invoice_id','left');
$this->db->where('ip_invoice_amounts.invoice_balance!=',0);
$this->db->where('ip_clients.client_id',$client_id);
$this->db->where('ip_invoice_items.item_date_added >=', $fdate);
$this->db->where('ip_invoice_items.item_date_added <=',$tdate);
$this->db->where('ip_invoices.invoice_status_id',2);
$query = $this->db->get();

return $query->result();
 }



 public function Save_Balance($balance_array,$invoice_id)
 {

    $this->db->where('invoice_id', $invoice_id);
    $this->db->update('ip_invoice_amounts', $balance_array);
        
        return TRUE;

 }

 public function Save_invstatus($invstatusarray,$invoice_id)
 {

    $this->db->where('invoice_id', $invoice_id);
    $this->db->update('ip_invoices', $invstatusarray);
        
        return TRUE;
 }

 public function Save_Paytable($paymenttable)
 {

    $this->db->trans_start();
    $this->db->insert('ip_payments', $paymenttable);        
    $insert_id = $this->db->affected_rows(); 

    $this->db->trans_complete();
                   
    return TRUE;
 }


 public function Update_Paytable($paymenttable,$invoice_id)
 {

    $this->db->where('invoice_id', $invoice_id);
    $this->db->update('ip_payments', $paymenttable);
        
        return TRUE;
 }


 public function GetEarlyPayment($invoice_id)
 {
    $this->db->select('invoice_paid');
    $this->db->from('ip_invoice_amounts');
    $this->db->where('invoice_id ',$invoice_id);
    $query = $this->db->get();
   // print_r( $this->db->last_query());
    return $query->result();    
        
 }


 public function CheckInvId($invoice_id)
 {
    $this->db->select('invoice_id');
    $this->db->from('ip_payments');
    $this->db->where('invoice_id ',$invoice_id);
    $query = $this->db->get();
   // print_r( $this->db->last_query());
    return $query->result();  

 }


 public function Get_PaymentDetails($id)
    {
        $this->db->distinct();
        $this->db->select('pay.*,amt.*,inv.*,client.client_name,pmethod.payment_method_name');
        $this->db->from('ip_payments pay');
        $this->db->join('ip_invoice_amounts amt','pay.invoice_id = amt.invoice_id','left');
        $this->db->join('ip_invoices inv','pay.invoice_id = inv.invoice_id','left');
        $this->db->join('ip_clients client','inv.client_id = client.client_id','left');
        $this->db->join('ip_payment_methods pmethod','pay.payment_method_id = pmethod.payment_method_id','left');
        
        $this->db->where('pay.receipt_no',$id);
     
            $query = $this->db->get();
            
            return $query->result();
    }



    public function Get_InvoicelistPayment($id)
 {
   $this->db->distinct();
   $this->db->select('ip_invoice_amounts.invoice_id,ip_invoice_amounts.invoice_amount_id,
ip_invoice_items.item_quantity,ip_invoice_items.item_description,
IFnull(ip_invoice_amounts.invoice_item_subtotal, 0.00) AS invoice_item_subtotal, 
IFnull(ip_invoice_amounts.invoice_item_tax_total, 0.00) AS invoice_item_tax_total, 
IFnull(ip_invoice_amounts.invoice_tax_total, 0.00) AS invoice_tax_total, 
IFnull(ip_invoice_amounts.invoice_total, 0.00) AS invoice_total, 
IFnull(ip_invoice_amounts.invoice_paid, 0.00) AS invoice_paid, 
IFnull(ip_invoice_amounts.invoice_balance, 0.00) AS invoice_balance, 
ip_invoice_amounts.invoice_sign AS invoice_sign,ip_payments.total_payment, 
(CASE WHEN ip_invoices.invoice_status_id NOT IN (1, 4) 
AND DATEDIFF(NOW(), invoice_date_due) > 0 THEN 1 ELSE 0 END) is_overdue, 
DATEDIFF(NOW(), invoice_date_due) AS days_overdue, (CASE (SELECT COUNT(*) 
FROM ip_invoices_recurring WHERE ip_invoices_recurring.invoice_id = ip_invoices.invoice_id 
and ip_invoices_recurring.recur_next_date <> 0000-00-00) WHEN 0 THEN 0 ELSE 1 END) 
AS invoice_is_recurring, ip_invoices.*,ip_payments.payment_amount');

$this->db->from('ip_invoices');
$this->db->join('ip_clients','ip_clients.client_id = ip_invoices.client_id');
$this->db->join('ip_invoice_items','ip_invoice_items.invoice_id = ip_invoices.invoice_id');
$this->db->join('ip_invoice_amounts','ip_invoice_amounts.invoice_id = ip_invoices.invoice_id','left');
$this->db->join('ip_invoice_sumex','sumex_invoice = ip_invoices.invoice_id','left');
$this->db->join('ip_quotes','ip_quotes.invoice_id = ip_invoices.invoice_id','left');
$this->db->join('ip_payments','ip_payments.invoice_id = ip_invoices.invoice_id','left');
$this->db->where('ip_payments.receipt_no',$id);

$query = $this->db->get();

return $query->result();
 }



public function Get_LastPaymentid()
{
     $this->db->select('max(payment_id) as pidmax');
     $this->db->from('ip_payments');
     $query = $this->db->get();
     return $query->result();

}

public function GetReceiptCount()
{
    
    $this->db->select('count(receipt_no) as rcount');
    $this->db->from('ip_payments');
    $query = $this->db->get();
    return $query->result();
}

public function GetReceiptid()
{
    $this->db->select('max(receipt_no) as lrid');
    $this->db->from('ip_payments');
    $query = $this->db->get();
    return $query->result();
}

 public function getinvoicedetails($receiptno)
  {
     $this->db->select('ip_payments.*,ip_invoices.*,ip_invoice_amounts.*,ip_clients.*,ip_invoice_items.*,ip_payment_methods.*');
     $this->db->from('ip_payments');
     $this->db->join('ip_invoices','ip_payments.invoice_id = ip_invoices.invoice_id');
     $this->db->join('ip_invoice_amounts','ip_payments.invoice_id = ip_invoice_amounts.invoice_id');
     $this->db->join('ip_clients','ip_clients.client_id = ip_invoices.client_id');
     $this->db->join('ip_invoice_items','ip_invoice_items.invoice_id = ip_invoices.invoice_id');
     $this->db->join('ip_payment_methods','ip_payment_methods.payment_method_id = ip_payments.payment_method_id');
     $this->db->where('ip_payments.receipt_no',$receiptno);
     $query = $this->db->get();
     //print_r( $this->db->last_query());
     return $query->result();
  }


public function Get_mailid($client_id)
{

    $this->db->select('client_id,client_name,client_email');
    $this->db->from('ip_clients');
    $this->db->where('client_id ',$client_id);
    $query = $this->db->get();
   // print_r( $this->db->last_query());
    return $query->result(); 
}


public function GetuserDetails($userid)
{
    $this->db->select('user_company,user_address_1,user_address_2,user_city,user_state,user_zip,');
    $this->db->from('ip_users');
    $this->db->where('user_id ',$userid);
    $query = $this->db->get();
   // print_r( $this->db->last_query());
    return $query->result(); 
}

}