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

class Bills_management {

  function __construct()
  {
    ini_set('max_execution_time', 5000);
    ini_set("memory_limit", "-1");


    $CI =& get_instance();
    $this->CI =& get_instance();
    $this->CI->load->library('session');
    $CI->load->model('User_model', 'User');
    $CI->load->model('Work_model', 'Work');
    $CI->load->model('Client_model', 'Client');
    $CI->load->model('Supplier_model', 'Supplier');
    $CI->load->model('Purchase_bill_model', 'Purchases');
    $CI->load->model('Purchase_order_model', 'Purchase_order');
    $CI->load->model('Purchase_request_model', 'Purchase_request');
    $CI->load->model('Material_model', 'Material');
    $CI->load->model('Subcontracts_model', 'Subcontracts');
    $CI->load->model('Labour_model', 'Labour');
    $CI->load->model('Labour_wages_model', 'Labour_wages');
    $CI->load->model('Account_model', 'Account');
    $CI->load->model('Bills_model', 'Bills');
    $CI->load->model('Salary_slips_model', 'Salary_slips');
    $CI->load->model('Vehicle_model', 'Vehicle');
    $CI->load->model('Vehicle_work_slips_model', 'Vehicle_work_slips');
    $CI->load->model('Invoices_model', 'Invoices');
    $CI->load->model('Sales_invoice_model','Sales_invoice');

  }


  public function calculate_bill_total($bill_id){
    $CI =& get_instance();
    $bill_details = $CI->Bills->select_bills("b.*",array("bill_id"=>$bill_id),'','','',$CI->config->item('select_data_entry_approval'),$CI->config->item('select_data_entry_approval'));

    if($bill_details->num_rows()==1){
      $bill_details = $bill_details->row();
      // Get Transactions on this bill
      $transaction_data['ats.reference_id'] = $bill_id;
      $transaction_data['ats.reference_type'] = $bill_details->bill_type.'_bill';
      $total_paid_amount	= $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$transaction_data,$transaction_data['ats.reference_type'],'','','','','','all')->row()->total_amount;

      $bill_data['bill_id'] = $bill_id;
      $bill_data['bill_total_paid'] = $total_paid_amount;
      $bill_payable_amount = $bill_details->bill_payable_amount;
      $bill_data['bill_balance_amount'] = $bill_payable_amount-$total_paid_amount;
      if($bill_data['bill_balance_amount']>0){
        if($bill_data['bill_balance_amount']==$bill_payable_amount){
          $bill_data['bill_payment_status'] = 0;
        }else{
          $bill_data['bill_payment_status'] = 2;
        }
      }else{
        $bill_data['bill_payment_status'] = 1;
      }
      $result = $CI->Bills->update_bills($bill_data);

      return $result;
    }
  }
  public function calculate_purchase_bill_total($purchase_id){
    $CI =& get_instance();
    $total_item_amount = $total_paid_amount = 0;
    //       Get Purchase Data
    $purchase_data	= $CI->Purchases->select_purchase("",array("p.purchase_id"=>$purchase_id),$CI->config->item('select_data_entry_approval'),'','',$CI->config->item('select_data_entry_approval'));
    if($purchase_data->num_rows()==1) {
      $purchase_data = $purchase_data->row();
      // $bill_discount = $purchase_data->bill_discount_amount;
      // $bill_tax_amount = $purchase_data->bill_tax_amount;
      // $bill_tds_amount = $purchase_data->bill_tds_amount;
      // $bill_commission = $purchase_data->bill_commission_amount;
      // //          Get Items Data
      // $total_item_amount = $CI->Purchases->select_purchase_items("COALESCE(SUM(pi.total_amount-pi.tax_amount),0) as total_item_amount", array("pi.purchase_id" => $purchase_id, "pi.delete_status" => 0))->row()->total_item_amount;
      //          Get Payments
      $total_paid_amount = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_paid_amount", array("ats.reference_id" => $purchase_id, "ats.reference_type" => 'purchase_bill'),'purchase_bill','','','','','','all')->row()->total_paid_amount;
      $total_paid_from_supplier_advance = $CI->Account->select_advance_return_transaction("COALESCE(SUM(art.tr_amount),0) as total_amount",array("art.reference_id" => $purchase_id,"art.tr_type" => 'purchase_bill_transaction',"art.reference_type" => 'purchase_bill'),'','','purchase_bill')->row()->total_amount;
      $data['purchase_id']=$purchase_id;
      // $data['bill_total_amount']=$total_item_amount;
      // $data['bill_payable_amount']=round(($data['bill_total_amount']-$bill_discount-$bill_commission+$bill_tax_amount-$bill_tds_amount),$CI->config->item('Currency_amount_decimals_2'));
      $data['total_paid']=$total_paid_amount+$total_paid_from_supplier_advance;
      $data['balance_amount']=round(($purchase_data->bill_payable_amount-$data['total_paid']),$CI->config->item('Currency_amount_decimals_2'));
      if($data['balance_amount']<=0){
        $data['payment_status']=1;
      }else{
        $data['payment_status']=0;
      }

      // Check for Other Charges Bill
      $bill_select_data = $CI->Bills->select_bills("b.*",array("b.purchase_id"=>$purchase_id),'expense_bill');
      if($bill_select_data->num_rows()>0){
        $other_charges = array();
        foreach($bill_select_data->result() as $key=>$value){
          $other_charges[$key]['type']=$value->bill_consignee_name;
          $other_charges[$key]['account']=$value->bill_credit_ac;
          $other_charges[$key]['amount']=$value->bill_payable_amount;
        }
        $data['other_charges'] = serialize($other_charges);
      }
      $result1 = $CI->Purchases->update_purchase($data);
    }
  }
  public function calculate_salary_slip_total($slip_id='',$employee_id='',$month=''){
    $CI =& get_instance();
    $total_paid_amount = 0;
    //       Get Salary Slip
    $where_data = array();
    if($slip_id!=''){
      $where_data['ss.salary_id'] = $slip_id;
    }
    if($employee_id!=''){
      $where_data['ss.employee_id'] = $employee_id;
    }
    if($month!=''){
      $where_data['ss.month'] = $month."-01";
    }
    if(!empty($where_data)){
      $salary_slips	= $CI->Salary_slips->select_salary_slips("ss.*",$where_data,'','','','','','all')->result();
      foreach ($salary_slips as $key => $value) {
        //          Get Arrear Payments
        $total_arrear_paid_amount = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_paid_amount", array("ats.reference_id" => $value->salary_id, "ats.reference_type" => 'salary_slip'),'salary_slip')->row()->total_paid_amount;
        //          Get Advance Payments
        $CI->db->where('ats.tr_type','salary_advance_transaction');
        $CI->db->where('ats.tr_other_data',date('Y-m',strtotime($value->month)));
        $total_advance_paid_amount = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_paid_amount", array("ats.reference_id" => $value->employee_id, "ats.reference_type" => 'employee'),'employee')->row()->total_paid_amount;

        $bill_payable_amount = $value->net_salary_amount;
        $update_data['salary_id']=$value->salary_id;
        $update_data['advance_paid_amount'] = $total_advance_paid_amount;
        $update_data['total_paid_amount'] = $total_arrear_paid_amount;
        $update_data['total_balance_amount'] = $bill_payable_amount-$update_data['total_paid_amount']-$update_data['advance_paid_amount'];
        $result = $CI->Salary_slips->update_salary_slip($update_data);
      }
    }
  }
  public function calculate_vehicle_work_slip_total($slip_id){
    $CI =& get_instance();
    $total_paid_amount = 0;
    //       Get vehicle Slip
    $slip_data  = $CI->Vehicle_work_slips->select_vehicle_work_slips("vws.*",array("vws.work_slip_id"=>$slip_id,"vws.delete_status"=>0));
    if($slip_data->num_rows()==1) {
      $slip_data = $slip_data->row();
      //          Get Payments
      $total_paid_amount = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_paid_amount", array("ats.reference_id" => $slip_id, "ats.reference_type" => 'vehicle_work_slip'),'vehicle_work_slip')->row()->total_paid_amount;

      $data['work_slip_id']=$slip_id;
      $bill_payable_amount=$slip_data->total_payable_amount;
      $data['total_paid']=$total_paid_amount;
      $data['balance']=$bill_payable_amount-$data['total_paid'];
      $result1 = $CI->Vehicle_work_slips->update_vehicle_work_slip($data);
    }

  }
  public function calculate_invoice_payment_total($invoice_id){
    $CI =& get_instance();
    $invoice_payments_details = $CI->Invoices->select_invoices("i.*",array("invoice_id"=>$invoice_id),'all');
    if($invoice_payments_details->num_rows()==1){
      $invoice_payments_details = $invoice_payments_details->row();
      // Get Transactions on this Invoice
      $transaction_data['ats.invoice_id'] = $invoice_id;
      // $transaction_data['ats.reference_type_2'] = 'invoice';
      $total_paid_amount  = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$transaction_data,$transaction_data['ats.invoice_id'])->row()->total_amount;
      $invoice_payments_data['invoice_id'] = $invoice_id;
      $invoice_payments_data['total_paid'] = $total_paid_amount;
      $invoice_total_amount = $invoice_payments_details->invoice_total_amount;
      $invoice_payments_data['balance_amount'] = round($invoice_total_amount-$total_paid_amount,$CI->config->item('Currency_amount_decimals_2'));
      if($invoice_payments_data['balance_amount']>0){
        if($invoice_payments_data['balance_amount']==$invoice_total_amount){
          $invoice_payments_data['payment_status'] = 0;//not paid
        }else{
          $invoice_payments_data['payment_status'] = 2;//partially paid
        }
      }else{
        $invoice_payments_data['payment_status'] = 1;//paid
      }
      $result = $CI->Invoices->update_invoice($invoice_payments_data);

      return $result;
    }
  }
  public function calculate_project_commission($project_id){
    $CI =& get_instance();
    $transaction_where['tr_work_id'] = $project_id;
    $transaction_where['reference_type'] = "user_account_head";
    $transaction_where['tr_type'] = "user_commission_transaction";
    $assigned_users = $CI->Work->select_work_assign_log("u.*,wp.project_commission_amount,wp.assign_log_id",array("wp.work_id"=>$project_id))->result();
    foreach ($assigned_users as $key => $value) {
      $account_data = $CI->Account->select_account_heads("",array("acc_user_id"=>$value->user_id));
      $transaction_where['reference_id'] = $account_data->row()->acc_head_id;
      // check already Exist
      $update_data['total_paid'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$transaction_where,'user_commission')->row()->total_amount;
      $update_data['total_balance'] = $value->project_commission_amount-$update_data['total_paid'];
      $update_data['assign_log_id'] = $value->assign_log_id;
      $CI->Work->update_work_assign_log($update_data);
    }
  }
  public function calculate_sales_invoice_total($invoice_id){
    $CI =& get_instance();
    $invoice_details = $CI->Sales_invoice->select_invoices("si.*",array("si.invoice_id"=>$invoice_id),'all');
    if($invoice_details->num_rows()==1){
      $invoice_details = $invoice_details->row();
      // Get Transactions on this Invoice
      $transaction_data['ats.reference_id'] = $invoice_id;
      $transaction_data['ats.reference_type'] = 'sales_invoice';
      $transaction_data['ats.tr_type'] = 'sales_invoice_transaction';
      $total_received_amount  = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$transaction_data)->row()->total_amount;

      $invoice_update_data['invoice_id'] = $invoice_id;
      $invoice_update_data['total_received'] = $total_received_amount;
      $invoice_total_amount = $invoice_details->receivable_amount;
      $invoice_update_data['balance_amount'] = round($invoice_total_amount-$total_received_amount,$CI->config->item('Currency_amount_decimals_2'));
      $result = $CI->Sales_invoice->update_sale_invoice($invoice_update_data);

      return $result;
    }
  }
  public function update_purchase_request_closed_status($request_id){
    $CI =& get_instance();
    $request_data = $CI->Purchase_request->select_purchase_request("*,pr.created_by as request_created_by,u2.user_alias as approved_user,u.user_alias as created_user,u1.user_alias as approved_1_user,u2.user_alias as approved_2_user", array("pr.request_id" =>$request_id ));
    if($request_data->num_rows()==1){
      $request_items = $CI->Purchase_request->select_purchase_request_items("", array("pri.request_id" =>$request_id ))->result();
      $request_update_data['request_id'] = $request_data->row()->request_id;
      $request_update_data['closed_status'] = 1;
      foreach ($request_items as $key => $value) {
        $order_item_where['po.request_id'] = $request_id;
        $order_item_where['poi.material_id'] = $value->material_id;
        $order_item_where['poi.unit_id'] = $value->unit_id;
        // Check item ordered
        $order_item_qty = $CI->Purchase_order->select_purchase_order_items("COALESCE(SUM(poi.quantity),0) as total",$order_item_where)->row()->total;
        if($order_item_qty<$value->quantity){
          $request_update_data['closed_status'] = 0;
        }
      }
      $result = $CI->Purchase_request->update_purchase_request($request_update_data);
    }
  }
}
?>
