Enhanced PHP File Manager

⬆️ Go Up (..)

📊 Server Information

Server:

LiteSpeed

System:

Linux 4.18.0-553.54.1.lve.el8.x86_64

PHP Version:

7.3.33

User:

u435171512

Directory:

/home/u435171512/domains/demo.sigmacomputers.in/public_html/arjun_erp/application/controllers

Free Space:

4.09 TB

🔧 Quick Actions

✏️ Editing: Trialbalance.php

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
ini_set('max_execution_time',259200);
class Trialbalance extends CI_Controller {
  public function __construct()
  {
    parent::__construct();
    $this->load->library('excel');
    $this->load->model(array('Balancesheet_model'));
    $this->load->model(array('Dashboard_model'));
    $this->load->model(array('Accountjournal_model'));
    $this->load->model(array('Profitloss_model'));
    $this->load->model(array('Ledger_model','Invoice_model'));
    //if ($this->session->userdata['username'] == TRUE)
    if (isset($this->session->userdata['username']) && $this->session->userdata['username'] == TRUE)
        {
            //do something
        }else{
            redirect('login'); //if session is not there, redirect to login page
        } 
    date_default_timezone_set("Asia/Kolkata");
    $this->session->set_userdata('comtitle', 'Balance Sheet');
  }

public function index()
  {
      $data['liabilities_list'] = $this->Balancesheet_model->get_liabilities_list();
      $data['assets_list'] = $this->Balancesheet_model->get_assets_list();
      $data['location_list'] = $this->Balancesheet_model->get_location_list();

if($_SERVER['REQUEST_METHOD'] == 'POST')
    {
        $cstock = $data['cstock'] = $this->input->post('cstock');
        $ostock = $data['ostock'] = $this->input->post('ostock');
      if($this->input->post('searchChange')=='curdate')
      {
        $curdate = date('Y-m-d');
        $data['dtrange'] = '';
        $data['drnge'] = '';
        $data['purchasesearch'] = 'curdate';
        $fdate = $curdate;
        $tdate = '';
      }
      elseif($this->input->post('searchChange')=='date')
      {
        if($this->input->post('dtrange')!='')
        {
          $odt = explode('/', $this->input->post('dtrange'));
          $curdate = $odt[2].'-'.$odt[0].'-'.$odt[1];
        }
        else
        {
          $curdate = date('Y-m-d');
        }
        $data['dtrange'] = $this->input->post('dtrange');
        $data['drnge'] = '';
        $data['purchasesearch'] = 'date';
        $fdate = $curdate;
        $tdate = '';
      }
      else
      {
        $dtrange = $this->input->post('dtrangepicker');
        $dr = explode(' - ', $dtrange);

$fd = explode('/', $dr[0]);
        $td = explode('/', $dr[1]);

$fdate = $fd[2].'-'.$fd[0].'-'.$fd[1];
        $tdate = $td[2].'-'.$td[0].'-'.$td[1];
        $data['dtrange'] = '';
        $data['drnge'] = $dtrange;
        $data['purchasesearch'] = 'daterange';
      }

$company_id = $this->input->post('company_id');
      $data['company_id'] = $company_id;

if($company_id!='')
      {
        $data['cid'] = $cid = 'AND location_id='.$company_id;
        $data['decid'] = $decid = 'AND l.location_id='.$company_id;
        $data['purid'] = $purid = 'AND po.location_id='.$company_id;
        $data['ajcid'] = $ajcid = 'AND aj.location_id='.$company_id;
        $data['loc'] = $loc = 'AND po.location_id='.$company_id;
        $data['iloc'] = $iloc = 'AND ll.location_id='.$company_id;
        $data['eloc'] = $eloc = 'AND e.location_id='.$company_id;
        $data['oloc'] = $oloc = 'AND o.location_id='.$company_id;
        $data['invloc'] = $invloc = 'AND i.location_id='.$company_id;
        $data['ajloc'] = $ajloc = 'AND ar.location_id='.$company_id;
        $data['cob'] = $cob = 'AND cob.location_id='.$company_id;
        $data['vob'] = $vob = 'AND vob.location_id='.$company_id;
        $data['gloc'] = $gloc = 'AND g.location_id='.$company_id;
        $data['cdnloc'] = $cdnloc = 'AND cdn.location_id='.$company_id;
        $data['slocid'] = $slocid = 'AND e.location_id='.$company_id;
        $compDet = $this->Profitloss_model->get_location_by_id($company_id);
        $data['compDet'] = $compDet->location_name;
        $data['compqry'] = $compqry = " AND o.location_id='".$company_id."'";
        $data['dcompqry'] = $dcompqry = " AND i.location_id='".$company_id."'";
      }
      else
      {
        $data['cid'] = $cid = '';
        $data['decid'] = $decid = '';
        $data['purid'] = $purid = '';
        $data['ajcid'] = $ajcid = '';
        $data['loc'] = $loc = '';
        $data['iloc'] = $iloc = '';
        $data['eloc'] = $eloc = '';
        $data['oloc'] = $oloc = '';
        $data['invloc'] = $invloc = '';
        $data['ajloc'] = $ajloc = '';
        $data['cob'] = $cob = '';
        $data['vob'] = $vob = '';
        $data['gloc'] = $gloc = '';
        $data['cdnloc'] = $cdnloc = '';
        $data['slocid'] = $slocid = '';
        $data['compDet'] = '';
        $data['compqry'] = $compqry = "";
        $data['dcompqry'] = $dcompqry = "";
      }

if($this->input->post('searchChange')!='daterange')
        {

$data['sundry_debtors_list'] = $this->db->query("SELECT s.customer_id,sum(s.dpayment_amount) AS dpamt,sum(s.cpayment_amount) AS cpamt FROM ((SELECT '' as pdate,'' as order_id,'' as invoice_id,c.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(cob.opening_balance) as dpayment_amount,0 as cpayment_amount,0 as cheque_no,0 as bank_name,0 as branch_name,c.created_on,'Opening Balance' as particulars FROM `customer` c,customer_opening_balance cob WHERE c.customer_id = cob.customer_id AND cob.crdr=1 AND DATE(c.created_on)='$curdate' $cob GROUP BY c.customer_id)UNION(SELECT '' as pdate,'' as order_id,'' as invoice_id,c.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,0 as dpayment_amount,sum(cob.opening_balance) as cpayment_amount,0 as cheque_no,0 as bank_name,0 as branch_name,c.created_on,'Opening Balance' as particulars FROM `customer` c,customer_opening_balance cob WHERE c.customer_id = cob.customer_id AND cob.crdr=0 AND DATE(c.created_on)='$curdate' $cob GROUP BY c.customer_id)UNION(SELECT i.invoice_date as pdate,i.order_id,i.invoice_id,i.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(i.grand_total) as dpayment_amount,0 as cpayment_amount,0 as cheque_no,0 as bank_name,0 as branch_name,i.created_on,'New Invoice' as particulars FROM `invoice` i WHERE i.order_id=0 AND i.status!=20 AND i.invoice_date='$curdate' $invloc GROUP BY i.customer_id)UNION(SELECT i.invoice_date as pdate,i.order_id,i.invoice_id,o.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(i.grand_total) as dpayment_amount,0 as cpayment_amount,0 as cheque_no,0 as bank_name,0 as branch_name,i.created_on,'Order Invoice' as particulars FROM invoice i,`order` o WHERE o.order_id = i.order_id AND i.status!=20 AND i.invoice_date='$curdate' $oloc GROUP BY o.customer_id)UNION(SELECT ar.account_receipt_date as pdate,ar.user_id as order_id,0 as invoice_id,o.customer_id,0 as user_id,ar.payment_mode,ar.payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_receipt ar, `order` o WHERE ar.user_id = o.order_id AND ar.user_type='Sales' AND ar.account_receipt_date='$curdate' $oloc AND ar.status!=2 GROUP BY o.customer_id)UNION(SELECT ar.account_receipt_date as pdate,0 as order_id,ar.user_id as invoice_id,i.customer_id,0 as user_id,ar.payment_mode,ar.payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_receipt ar, `invoice` i WHERE ar.user_id = i.invoice_id AND ar.user_type='Invoice' AND i.customer_id !=0 AND ar.account_receipt_date='$curdate' $invloc AND ar.status!=2 GROUP BY i.customer_id)UNION(SELECT ar.account_receipt_date as pdate,0 as order_id,ar.user_id as invoice_id,o.customer_id,0 as user_id,ar.payment_mode,ar.payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_receipt ar, `invoice` i,`order` o WHERE ar.user_id = i.invoice_id AND i.order_id = o.order_id AND ar.user_type='Invoice' AND ar.account_receipt_date='$curdate' $oloc AND ar.status!=2 GROUP BY o.customer_id)UNION(SELECT ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,ar.ledger_list2_id as customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_journal ar WHERE ar.ledger_list2_type='customer' AND ar.account_journal_date='$curdate' $ajloc AND ar.status!=2 GROUP BY ar.ledger_list2_id)UNION(SELECT ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,ar.ledger_list1_id as customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(ar.amount) as dpayment_amount,0 as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_journal ar WHERE ar.ledger_list1_type='customer' AND ar.account_journal_date='$curdate' $ajloc AND ar.status!=2 GROUP BY ar.ledger_list1_id)UNION(SELECT ar.account_receipt_date as pdate,0 as order_id,0 as invoice_id,ar.parti_ledger_list_id as customer_id,0 as user_id,ar.payment_mode,ar.payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_receipt ar, ledger_list ll, location l WHERE ar.ledger_list_id = ll.ledger_list_id AND ll.location_id = l.location_id AND ar.parti_ledger_list_type = 'customer' AND ar.account_receipt_date='$curdate' $iloc AND ar.status!=2 GROUP BY ar.parti_ledger_list_id)UNION(SELECT credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,0 as dpayment_amount,sum(amount) as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,created_on,'' as particulars FROM credit_debit_note WHERE credit_debit='Credit' AND type_id = 0 AND status!=2 AND credit_debit_note_date='$curdate' $cid GROUP BY customer_id)UNION(SELECT credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(amount) as dpayment_amount,0 as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,created_on,'' as particulars FROM credit_debit_note WHERE credit_debit='Payment' AND type_id = 0 AND credit_debit_note_date='$curdate' $cid AND status!=2 GROUP BY customer_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,i.customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,0 as dpayment_amount,sum(amount) as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'' as particulars FROM credit_debit_note cdn, `invoice` i, customer c WHERE cdn.type_id = i.invoice_id AND i.customer_id = c.customer_id AND i.customer_id !=0 AND cdn.credit_debit='Credit' AND cdn.type_id != 0 AND cdn.credit_debit_note_date='$curdate' $cdnloc AND cdn.status!=2 GROUP BY i.customer_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,i.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(amount) as dpayment_amount,0 as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'' as particulars FROM credit_debit_note cdn, `invoice` i, customer c WHERE cdn.type_id = i.invoice_id AND i.customer_id = c.customer_id AND i.customer_id !=0 AND cdn.credit_debit='Payment' AND cdn.type_id != 0 AND cdn.credit_debit_note_date='$curdate' $cdnloc AND cdn.status!=2 GROUP BY i.customer_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,o.customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,0 as dpayment_amount,sum(amount) as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'' as particulars FROM credit_debit_note cdn, `invoice` i,`order` o, customer c WHERE cdn.type_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND i.customer_id =0 AND cdn.credit_debit='Credit' AND cdn.type_id != 0 AND cdn.credit_debit_note_date='$curdate' $cdnloc AND cdn.status!=2 GROUP BY o.customer_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,o.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(amount) as dpayment_amount,0 as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'' as particulars FROM credit_debit_note cdn, `invoice` i,`order` o, customer c WHERE cdn.type_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND i.customer_id =0 AND cdn.credit_debit='Payment' AND cdn.type_id != 0 AND cdn.credit_debit_note_date='$curdate' $cdnloc AND cdn.status!=2 GROUP BY o.customer_id)) s GROUP BY s.customer_id ORDER BY s.customer_id ASC")->result_array();

$data['sundry_creditors_list'] = $this->db->query("SELECT ven.vendor_id,sum(ven.dpayment_amount) AS dpamt,sum(ven.cpayment_amount) AS cpamt FROM ((SELECT v.created_on as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(vob.opening_balance) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,v.created_on,'Opening Balance' as particulars,'' as expense_type FROM vendor v,vendor_opening_balance vob WHERE v.vendor_id = vob.vendor_id AND vob.crdr=0 $vob GROUP BY v.vendor_id)UNION(SELECT v.created_on as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(vob.opening_balance) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,v.created_on,'Opening Balance' as particulars,'' as expense_type FROM vendor v, vendor_opening_balance vob WHERE v.vendor_id = vob.vendor_id AND vob.crdr=1 $vob GROUP BY v.vendor_id)UNION(SELECT p.purchase_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,p.vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(p.total_amount) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,p.created_on,'New Purchase' as particulars,'' as expense_type FROM purchase p,purchase_order po WHERE p.purchase_order_id = po.purchase_order_id AND p.purchase_date='$curdate' $loc AND p.status=1 AND p.status!=2 GROUP BY p.vendor_id)UNION(SELECT e.expense_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,e.vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(e.total_amount) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,e.created_on,'Expense' as particulars,'' as expense_type FROM expense e WHERE e.expense_date='$curdate' $eloc AND e.status!=2 GROUP BY e.vendor_id)UNION(SELECT ap.account_payment_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id,0 as oth_id,ap.payment_mode,ap.payment_type,0 as cpayment_amount,sum(ap.amount) as dpayment_amount,ap.cheque_no,ap.bank_name,ap.branch_name,ap.created_on,ap.particulars,ap.expense_type FROM account_payment ap,purchase p,vendor v,ledger_list ll WHERE ap.user_id = p.purchase_id AND p.vendor_id = v.vendor_id AND ap.ledger_list_id = ll.ledger_list_id $iloc AND ap.expense_type='vendor payment' AND ap.account_payment_date='$curdate' AND ap.status!=2 GROUP BY v.vendor_id)UNION(SELECT ap.account_payment_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id,0 as oth_id,ap.payment_mode,ap.payment_type,0 as cpayment_amount,sum(ap.amount) as dpayment_amount,ap.cheque_no,ap.bank_name,ap.branch_name,ap.created_on,ap.particulars,ap.expense_type FROM account_payment ap,expense e,vendor v,ledger_list ll WHERE ap.user_id = e.expense_id AND e.vendor_id = v.vendor_id AND ap.ledger_list_id = ll.ledger_list_id $iloc AND ap.expense_type='expense' AND ap.account_payment_date='$curdate' AND ap.status!=2 GROUP BY v.vendor_id)UNION(SELECT ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,ar.ledger_list2_id as vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(ar.amount) as cpayment_amount,0 as dpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars,'' as expense_type FROM account_journal ar WHERE ar.ledger_list2_type='vendor' AND ar.account_journal_date='$curdate' $ajloc AND ar.status!=2 GROUP BY ar.ledger_list2_id)UNION(SELECT ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,ar.ledger_list1_id as vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(ar.amount) as dpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars,'' as expense_type FROM account_journal ar WHERE ar.ledger_list1_type='vendor' AND ar.account_journal_date='$curdate' $ajloc AND ar.status!=2 GROUP BY ar.ledger_list1_id)UNION(SELECT ap.account_payment_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,ap.parti_ledger_list_id as vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(ap.amount) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,ap.created_on,ap.particulars,'' as expense_type FROM account_payment ap, ledger_list ll WHERE ap.ledger_list_id = ll.ledger_list_id AND ap.parti_ledger_list_type = 'vendor' AND ap.account_payment_date='$curdate' $iloc AND ap.status!=2 GROUP BY ap.parti_ledger_list_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id as vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(cdn.amount) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on, cdn.particulars,'' as expense_type FROM credit_debit_note cdn,vendor v WHERE cdn.vendor_id = v.vendor_id AND cdn.credit_debit = 'Debit' AND cdn.type_id = 0 AND cdn.credit_debit_note_date='$curdate' $cdnloc AND cdn.status!=2 GROUP BY v.vendor_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id as vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(cdn.amount) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on, cdn.particulars,'' as expense_type FROM credit_debit_note cdn,vendor v WHERE cdn.vendor_id = v.vendor_id AND cdn.credit_debit = 'Receipt' AND cdn.type_id = 0 AND cdn.credit_debit_note_date='$curdate' $cdnloc AND cdn.status!=2 GROUP BY v.vendor_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id as vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(cdn.amount) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on, cdn.particulars,'' as expense_type FROM credit_debit_note cdn,purchase p,grn g,vendor v WHERE cdn.type_id = p.purchase_id AND p.vendor_id = v.vendor_id AND p.grn_id = g.grn_id AND cdn.credit_debit = 'Debit' AND cdn.type_id != 0 AND cdn.credit_debit_note_date='$curdate' $cdnloc AND cdn.status!=2 GROUP BY v.vendor_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id as vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(cdn.amount) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on, cdn.particulars,'' as expense_type FROM credit_debit_note cdn,purchase p,grn g,vendor v WHERE cdn.type_id = p.purchase_id AND p.vendor_id = v.vendor_id AND p.grn_id = g.grn_id AND cdn.credit_debit = 'Receipt' AND cdn.type_id != 0 AND cdn.credit_debit_note_date='$curdate' $cdnloc AND cdn.status!=2 GROUP BY v.vendor_id)UNION(SELECT tp.tds_payable_line_item_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(tp.amount) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,tp.created_on,'TDS' as particulars,'' as expense_type FROM  tds_payable_line_item tp,expense e,vendor v WHERE tp.expense_id = e.expense_id AND e.vendor_id = v.vendor_id AND tp.tds_payable_line_item_date='$curdate' $eloc AND tp.status!=2 GROUP BY v.vendor_id)) ven GROUP BY ven.vendor_id ORDER BY ven.vendor_id ASC")->result_array();

$data['account_journal_list'] = $this->db->query("SELECT aj.*,aa.account_attributes, sum(aj.amount) as tamt FROM account_journal aj,account_attributes aa WHERE aj.account_attributes_id = aa.account_attributes_id AND aj.account_journal_date = '$curdate' $ajcid GROUP BY aj.account_attributes_id ORDER BY aj.account_journal_id DESC")->result_array();

$data['purchase_amt'] = $this->db->query("SELECT sum(p.taxable_amount) as amt,sum(p.other_charges) as ocharge FROM `purchase`p, purchase_order po WHERE p.purchase_order_id = po.purchase_order_id AND p.invoice_date='$curdate' AND p.status!=5 $purid")->row();

$data['in_direct_expense'] = $this->db->query("SELECT led.ledger_group,led.ledger_group_id,sum(led.tamt) as amt FROM ((SELECT e.expense_date,e.invoice_no,sum(e.taxable_value) as tamt,e.other_charges,e.sgst,e.cgst,e.igst,e.total_amount,e.tds_value,v.vendor_name,v.gst_no,lg.ledger_group,lg.ledger_group_id FROM `expense` e,vendor v,ledger_group lg,ledger_type lt WHERE e.vendor_id = v.vendor_id AND e.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND e.expense_date='$curdate' AND e.status!=2 AND lt.expense_type = 'In Direct' $slocid GROUP BY lg.ledger_group_id) UNION (SELECT l.ledger_date,l.ledger_no as invoice_no,sum(l.amount) as tamt,0 as other_charges,0 as sgst,0 as cgst,0 as igst,l.amount as total_amount,0 as tds_value,l.payee_name as vendor_name ,'' as gst_no,lg.ledger_group,lg.ledger_group_id FROM ledger l,ledger_sub_group lsg,ledger_group lg,ledger_type lt WHERE l.ledger_sub_group_id = lsg.ledger_sub_group_id AND lsg.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND l.status=0 AND lt.expense_type = 'In Direct' AND l.ledger_date='$curdate' $decid GROUP BY lg.ledger_group_id)UNION(SELECT e.staff_expense_date,e.voucher_no as invoice_no,sum(e.net_salary) as tamt,0 as other_charges,0 as sgst,0 as cgst,0 as igst,e.net_salary as total_amount,0 as tds_value,'' as vendor_name ,'' as gst_no,lg.ledger_group,lg.ledger_group_id FROM staff_expense e,ledger_group lg,ledger_type lt WHERE e.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND e.status != 2 AND lt.expense_type = 'In Direct' AND e.staff_expense_date='$curdate' $slocid GROUP BY lg.ledger_group_id)) led GROUP BY led.ledger_group_id ")->result_array();

$data['sales_amt'] = $this->db->query("SELECT sum(a.taxamt) as txamt,sum(a.pcharge) as pc, sum(a.ocharge) as oc FROM ((SELECT sum(i.taxable_amount) as taxamt,sum(i.packing_charges) as pcharge,sum(i.other_charges) as ocharge FROM invoice i,`order` o WHERE i.order_id = o.order_id AND i.invoice_date='$curdate' $compqry)UNION(SELECT sum(i.taxable_amount) as taxamt,sum(i.packing_charges) as pcharge,sum(i.other_charges) as ocharge FROM invoice i WHERE i.invoice_date='$curdate' $dcompqry)) a")->row();

$data['direct_invoice'] = $this->db->query("SELECT sum(grand_total) as amt FROM `invoice` WHERE invoice_date='$curdate' AND customer_id!=0 $cid")->row();

$data['direct_expense'] = $this->db->query("SELECT lg.ledger_group_id,lsg.ledger_sub_group,sum(l.amount) as amt FROM `ledger` l,ledger_sub_group lsg,ledger_group lg,ledger_type lt WHERE l.ledger_sub_group_id = lsg.ledger_sub_group_id AND lsg.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND l.ledger_date='$curdate' $decid AND l.status=0 AND lt.expense_type = 'Direct' GROUP BY l.ledger_sub_group_id")->result_array();

}
        else
        {
          $data['sundry_debtors_list'] = $this->db->query("SELECT s.customer_id,sum(s.dpayment_amount) AS dpamt,sum(s.cpayment_amount) AS cpamt FROM ((SELECT i.invoice_date as pdate,i.order_id,i.invoice_id,i.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(i.grand_total) as dpayment_amount,0 as cpayment_amount,0 as cheque_no,0 as bank_name,0 as branch_name,i.created_on,'New Invoice' as particulars FROM `invoice` i WHERE i.order_id=0 AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' $invloc AND i.status!=20 GROUP BY i.customer_id)UNION(SELECT i.invoice_date as pdate,i.order_id,i.invoice_id,o.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(i.grand_total) as dpayment_amount,0 as cpayment_amount,0 as cheque_no,0 as bank_name,0 as branch_name,i.created_on,'Order Invoice' as particulars FROM invoice i,`order` o WHERE o.order_id = i.order_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' $oloc AND i.status!=20 GROUP BY o.customer_id)UNION(SELECT ar.account_receipt_date as pdate,ar.user_id as order_id,0 as invoice_id,o.customer_id,0 as user_id,ar.payment_mode,ar.payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_receipt ar, `order` o WHERE ar.user_id = o.order_id AND ar.user_type='Sales' AND ar.account_receipt_date>='$fdate' AND ar.account_receipt_date<='$tdate' $oloc AND ar.status!=2 GROUP BY o.customer_id)UNION(SELECT ar.account_receipt_date as pdate,0 as order_id,ar.user_id as invoice_id,i.customer_id,0 as user_id,ar.payment_mode,ar.payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_receipt ar, `invoice` i WHERE ar.user_id = i.invoice_id AND ar.user_type='Invoice' AND i.customer_id !=0 AND ar.account_receipt_date>='$fdate' AND ar.account_receipt_date<='$tdate' $invloc AND ar.status!=2 GROUP BY i.customer_id)UNION(SELECT ar.account_receipt_date as pdate,0 as order_id,ar.user_id as invoice_id,o.customer_id,0 as user_id,ar.payment_mode,ar.payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_receipt ar, `invoice` i,`order` o WHERE ar.user_id = i.invoice_id AND i.order_id = o.order_id AND ar.user_type='Invoice' AND ar.account_receipt_date>='$fdate' AND ar.account_receipt_date<='$tdate' $oloc AND ar.status!=2 GROUP BY o.customer_id)UNION(SELECT ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,ar.ledger_list2_id as customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_journal ar WHERE ar.ledger_list2_type='customer' AND ar.account_journal_date>='$fdate' AND ar.account_journal_date<='$tdate' $ajloc AND ar.status!=2 GROUP BY ar.ledger_list2_id)UNION(SELECT ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,ar.ledger_list1_id as customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(ar.amount) as dpayment_amount,0 as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_journal ar WHERE ar.ledger_list1_type='customer' AND ar.account_journal_date>='$fdate' AND ar.account_journal_date<='$tdate' $ajloc AND ar.status!=2 GROUP BY ar.ledger_list1_id)UNION(SELECT ar.account_receipt_date as pdate,0 as order_id,0 as invoice_id,ar.parti_ledger_list_id as customer_id,0 as user_id,ar.payment_mode,ar.payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_receipt ar, ledger_list ll, location l WHERE ar.ledger_list_id = ll.ledger_list_id AND ll.location_id = l.location_id AND ar.parti_ledger_list_type = 'customer' AND ar.account_receipt_date>='$fdate' AND ar.account_receipt_date<='$tdate' $iloc AND ar.status!=2 GROUP BY ar.parti_ledger_list_id)UNION(SELECT credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,0 as dpayment_amount,sum(amount) as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,created_on,'' as particulars FROM credit_debit_note WHERE credit_debit='Credit' AND type_id = 0 AND credit_debit_note_date >= '$fdate' AND credit_debit_note_date <= '$tdate' $cid AND status!=2 GROUP BY customer_id)UNION(SELECT credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(amount) as dpayment_amount,0 as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,created_on,'' as particulars FROM credit_debit_note WHERE credit_debit='Payment' AND type_id = 0 AND credit_debit_note_date >= '$fdate' AND credit_debit_note_date <= '$tdate' $cid AND status!=2 GROUP BY customer_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,i.customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,0 as dpayment_amount,sum(amount) as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'' as particulars FROM credit_debit_note cdn, `invoice` i, customer c WHERE cdn.type_id = i.invoice_id AND i.customer_id = c.customer_id AND i.customer_id !=0 AND cdn.credit_debit='Credit' AND cdn.type_id != 0 AND cdn.credit_debit_note_date >= '$fdate' AND cdn.credit_debit_note_date <= '$tdate' $cdnloc AND cdn.status!=2 GROUP BY i.customer_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,i.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(amount) as dpayment_amount,0 as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'' as particulars FROM credit_debit_note cdn, `invoice` i, customer c WHERE cdn.type_id = i.invoice_id AND i.customer_id = c.customer_id AND i.customer_id !=0 AND cdn.credit_debit='Payment' AND cdn.type_id != 0 AND cdn.credit_debit_note_date >= '$fdate' AND cdn.credit_debit_note_date <= '$tdate' $cdnloc AND cdn.status!=2 GROUP BY i.customer_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,o.customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,0 as dpayment_amount,sum(amount) as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'' as particulars FROM credit_debit_note cdn, `invoice` i,`order` o, customer c WHERE cdn.type_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND i.customer_id =0 AND cdn.credit_debit='Credit' AND cdn.type_id != 0 AND cdn.credit_debit_note_date >= '$fdate' AND cdn.credit_debit_note_date <= '$tdate' $cdnloc AND cdn.status!=2 GROUP BY o.customer_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,o.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(amount) as dpayment_amount,0 as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'' as particulars FROM credit_debit_note cdn, `invoice` i,`order` o, customer c WHERE cdn.type_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND i.customer_id =0 AND cdn.credit_debit='Payment' AND cdn.type_id != 0 AND cdn.credit_debit_note_date >= '$fdate' AND cdn.credit_debit_note_date <= '$tdate' $cdnloc AND cdn.status!=2 GROUP BY o.customer_id)) s GROUP BY s.customer_id ORDER BY s.customer_id ASC")->result_array();

$data['sundry_creditors_list'] = $this->db->query("SELECT ven.vendor_id,sum(ven.dpayment_amount) AS dpamt,sum(ven.cpayment_amount) AS cpamt FROM ((SELECT p.purchase_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,p.vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(p.total_amount) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,p.created_on,'New Purchase' as particulars,'' as expense_type FROM purchase p,purchase_order po WHERE p.purchase_order_id = po.purchase_order_id AND p.purchase_date>='$fdate' AND p.purchase_date<='$tdate' $loc AND p.status=1 AND p.status!=2 GROUP BY p.vendor_id)UNION(SELECT e.expense_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,e.vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(e.total_amount) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,e.created_on,'Expense' as particulars,'' as expense_type FROM expense e WHERE e.expense_date>='$fdate' AND e.expense_date<='$tdate' $eloc AND e.status!=2 GROUP BY e.vendor_id)UNION(SELECT ap.account_payment_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id,0 as oth_id,ap.payment_mode,ap.payment_type,0 as cpayment_amount,sum(ap.amount) as dpayment_amount,ap.cheque_no,ap.bank_name,ap.branch_name,ap.created_on,ap.particulars,ap.expense_type FROM account_payment ap,purchase p,vendor v,ledger_list ll WHERE ap.user_id = p.purchase_id AND p.vendor_id = v.vendor_id AND ap.ledger_list_id = ll.ledger_list_id $iloc AND ap.expense_type='vendor payment' AND ap.account_payment_date>='$fdate' AND ap.account_payment_date<='$tdate' AND ap.status!=2 GROUP BY v.vendor_id)UNION(SELECT ap.account_payment_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id,0 as oth_id,ap.payment_mode,ap.payment_type,0 as cpayment_amount,sum(ap.amount) as dpayment_amount,ap.cheque_no,ap.bank_name,ap.branch_name,ap.created_on,ap.particulars,ap.expense_type FROM account_payment ap,expense e,vendor v,ledger_list ll WHERE ap.user_id = e.expense_id AND e.vendor_id = v.vendor_id AND ap.ledger_list_id = ll.ledger_list_id $iloc AND ap.expense_type='expense' AND ap.account_payment_date>='$fdate' AND ap.account_payment_date<='$tdate' AND ap.status!=2 GROUP BY v.vendor_id)UNION(SELECT ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,ar.ledger_list2_id as vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(ar.amount) as cpayment_amount,0 as dpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars,'' as expense_type FROM account_journal ar WHERE ar.ledger_list2_type='vendor' AND ar.account_journal_date>='$fdate' AND ar.account_journal_date<='$tdate' $ajloc AND ar.status!=2 GROUP BY ar.ledger_list2_id)UNION(SELECT ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,ar.ledger_list1_id as vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(ar.amount) as dpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars,'' as expense_type FROM account_journal ar WHERE ar.ledger_list1_type='vendor' AND ar.account_journal_date>='$fdate' AND ar.account_journal_date<='$tdate' $ajloc AND ar.status!=2 GROUP BY ar.ledger_list1_id)UNION(SELECT ap.account_payment_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,ap.parti_ledger_list_id as vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(ap.amount) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,ap.created_on,ap.particulars,'' as expense_type FROM account_payment ap, ledger_list ll WHERE ap.ledger_list_id = ll.ledger_list_id AND ap.parti_ledger_list_type = 'vendor' AND ap.account_payment_date>='$fdate' AND ap.account_payment_date<='$tdate' $iloc AND ap.status!=2 GROUP BY ap.parti_ledger_list_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id as vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(cdn.amount) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on, cdn.particulars,'' as expense_type FROM credit_debit_note cdn,vendor v WHERE cdn.vendor_id = v.vendor_id AND cdn.credit_debit = 'Debit' AND cdn.type_id = 0 AND cdn.credit_debit_note_date>='$fdate' AND cdn.credit_debit_note_date<='$tdate' $cdnloc AND cdn.status!=2 GROUP BY v.vendor_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id as vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(cdn.amount) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on, cdn.particulars,'' as expense_type FROM credit_debit_note cdn,vendor v WHERE cdn.vendor_id = v.vendor_id AND cdn.credit_debit = 'Receipt' AND cdn.type_id = 0 AND cdn.credit_debit_note_date>='$fdate' AND cdn.credit_debit_note_date<='$tdate' $cdnloc AND cdn.status!=2 GROUP BY v.vendor_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id as vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(cdn.amount) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on, cdn.particulars,'' as expense_type FROM credit_debit_note cdn,purchase p,grn g,vendor v WHERE cdn.type_id = p.purchase_id AND p.vendor_id = v.vendor_id AND p.grn_id = g.grn_id AND cdn.credit_debit = 'Debit' AND cdn.type_id != 0 AND cdn.credit_debit_note_date>='$fdate' AND cdn.credit_debit_note_date<='$tdate' $cdnloc AND cdn.status!=2 GROUP BY v.vendor_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id as vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(cdn.amount) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on, cdn.particulars,'' as expense_type FROM credit_debit_note cdn,purchase p,grn g,vendor v WHERE cdn.type_id = p.purchase_id AND p.vendor_id = v.vendor_id AND p.grn_id = g.grn_id AND cdn.credit_debit = 'Receipt' AND cdn.type_id != 0 AND cdn.credit_debit_note_date>='$fdate' AND cdn.credit_debit_note_date<='$tdate' $cdnloc AND cdn.status!=2 GROUP BY v.vendor_id)UNION(SELECT tp.tds_payable_line_item_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(tp.amount) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,tp.created_on,'TDS' as particulars,'' as expense_type FROM  tds_payable_line_item tp,expense e,vendor v WHERE tp.expense_id = e.expense_id AND e.vendor_id = v.vendor_id AND tp.tds_payable_line_item_date>='$fdate' AND tp.tds_payable_line_item_date<='$tdate' $eloc AND tp.status!=2 GROUP BY v.vendor_id)) ven GROUP BY ven.vendor_id ORDER BY ven.vendor_id ASC")->result_array();

$data['account_journal_list'] = $this->db->query("SELECT aj.*,aa.account_attributes, sum(aj.amount) as tamt FROM account_journal aj,account_attributes aa WHERE aj.account_attributes_id = aa.account_attributes_id AND aj.account_journal_date >= '$fdate' AND aj.account_journal_date <= '$tdate' $ajcid GROUP BY aj.account_attributes_id ORDER BY aj.account_journal_id DESC")->result_array();

$data['in_direct_expense'] = $this->db->query("SELECT led.ledger_group,led.ledger_group_id,sum(led.tamt) as amt FROM ((SELECT e.expense_date,e.invoice_no,sum(e.taxable_value) as tamt,e.other_charges,e.sgst,e.cgst,e.igst,e.total_amount,e.tds_value,v.vendor_name,v.gst_no,lg.ledger_group,lg.ledger_group_id FROM `expense` e,vendor v,ledger_group lg,ledger_type lt WHERE e.vendor_id = v.vendor_id AND e.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND e.expense_date>='$fdate' AND e.expense_date<='$tdate' AND e.status!=2 AND lt.expense_type = 'In Direct' $slocid GROUP BY lg.ledger_group_id) UNION (SELECT l.ledger_date,l.ledger_no as invoice_no,sum(l.amount) as tamt,0 as other_charges,0 as sgst,0 as cgst,0 as igst,l.amount as total_amount,0 as tds_value,l.payee_name as vendor_name ,'' as gst_no,lg.ledger_group,lg.ledger_group_id FROM ledger l,ledger_sub_group lsg,ledger_group lg,ledger_type lt WHERE l.ledger_sub_group_id = lsg.ledger_sub_group_id AND lsg.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND l.status=0 AND lt.expense_type = 'In Direct' AND l.ledger_date>='$fdate' AND l.ledger_date<='$tdate' $decid GROUP BY lg.ledger_group_id)UNION(SELECT e.staff_expense_date,e.voucher_no as invoice_no,sum(e.net_salary) as tamt,0 as other_charges,0 as sgst,0 as cgst,0 as igst,e.net_salary as total_amount,0 as tds_value,'' as vendor_name ,'' as gst_no,lg.ledger_group,lg.ledger_group_id FROM staff_expense e,ledger_group lg,ledger_type lt WHERE e.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND e.status != 2 AND lt.expense_type = 'In Direct' AND e.staff_expense_date>='$fdate' AND e.staff_expense_date<='$tdate' $slocid GROUP BY lg.ledger_group_id)) led GROUP BY led.ledger_group_id ")->result_array();

$data['sales_amt'] = $this->db->query("SELECT sum(a.taxamt) as txamt,sum(a.pcharge) as pc, sum(a.ocharge) as oc FROM ((SELECT sum(i.taxable_amount) as taxamt,sum(i.packing_charges) as pcharge,sum(i.other_charges) as ocharge FROM invoice i,`order` o WHERE i.order_id = o.order_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' $compqry)UNION(SELECT sum(i.taxable_amount) as taxamt,sum(i.packing_charges) as pcharge,sum(i.other_charges) as ocharge FROM invoice i WHERE i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' $dcompqry)) a")->row();

$data['direct_invoice'] = $this->db->query("SELECT sum(grand_total) as amt FROM `invoice` WHERE invoice_date>='$fdate' AND invoice_date<='$tdate' AND customer_id!=0 $cid")->row();

$data['direct_expense'] = $this->db->query("SELECT lg.ledger_group_id,lsg.ledger_sub_group,sum(l.amount) as amt FROM `ledger` l,ledger_sub_group lsg,ledger_group lg,ledger_type lt WHERE l.ledger_sub_group_id = lsg.ledger_sub_group_id AND lsg.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND l.ledger_date>='$fdate' AND l.ledger_date<='$tdate' $decid AND l.status=0 AND lt.expense_type = 'Direct' GROUP BY l.ledger_sub_group_id")->result_array();

$data['direct_expense_list'] = $this->db->query("SELECT s.ledger_group_id,s.ledger_group, sum(s.amt) as amount FROM ((SELECT lg.ledger_group_id, lg.ledger_group,sum(e.taxable_value) as amt FROM expense e,ledger_group lg,ledger_type lt WHERE e.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND e.status != 2 AND lt.expense_type = 'Direct' AND e.expense_date>='$fdate' AND e.expense_date<='$tdate' $slocid GROUP BY lg.ledger_group_id)UNION(SELECT lg.ledger_group_id, lg.ledger_group,sum(e.net_salary) as amt FROM staff_expense e,ledger_group lg,ledger_type lt WHERE e.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND e.status != 2 AND lt.expense_type = 'Direct' AND e.staff_expense_date>='$fdate' AND e.staff_expense_date<='$tdate' $slocid GROUP BY lg.ledger_group_id)) s GROUP BY s.ledger_group_id ")->result_array();
        }
    }
    else
    {
      $cstock = $data['cstock'] = 0;
      $ostock = $data['ostock'] = 0;
      $curdate = date('Y-m-d');
      $data['dtrange'] = '';
      $data['drnge'] = '';
      $company_id = $data['company_id'] = '';
      $data['compDet'] = '';
      $data['purchasesearch'] = 'curdate';
      $data['account_journal_list'] = $this->Balancesheet_model->get_account_journal_bs_by_date($curdate);
      $fdate = $curdate;
      $tdate = '';

$data['cid'] = $cid = '';
      $data['decid'] = $decid = '';
      $data['purid'] = $purid = '';
      $data['ajcid'] = $ajcid = '';
      $data['loc'] = $loc = '';
      $data['iloc'] = $iloc = '';
      $data['eloc'] = $eloc = '';
      $data['oloc'] = $oloc = '';
      $data['invloc'] = $invloc = '';
      $data['ajloc'] = $ajloc = '';
      $data['cob'] = $cob = '';
      $data['vob'] = $vob = '';
      $data['gloc'] = $gloc = '';
      $data['cdnloc'] = $cdnloc = '';
      $data['slocid'] = $slocid = '';
      $data['compqry'] = $compqry = "";
      $data['dcompqry'] = $dcompqry = "";

$data['sundry_creditors_list'] = $this->db->query("SELECT ven.vendor_id,sum(ven.dpayment_amount) AS dpamt,sum(ven.cpayment_amount) AS cpamt FROM ((SELECT v.created_on as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(vob.opening_balance) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,v.created_on,'Opening Balance' as particulars,'' as expense_type FROM vendor v,vendor_opening_balance vob WHERE v.vendor_id = vob.vendor_id AND vob.crdr=0 GROUP BY v.vendor_id)UNION(SELECT v.created_on as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(vob.opening_balance) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,v.created_on,'Opening Balance' as particulars,'' as expense_type FROM vendor v, vendor_opening_balance vob WHERE v.vendor_id = vob.vendor_id AND vob.crdr=1 GROUP BY v.vendor_id)UNION(SELECT p.purchase_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,p.vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(p.total_amount) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,p.created_on,'New Purchase' as particulars,'' as expense_type FROM purchase p,purchase_order po WHERE p.purchase_order_id = po.purchase_order_id AND p.purchase_date='$curdate' AND p.status=1 AND p.status!=2 GROUP BY p.vendor_id)UNION(SELECT e.expense_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,e.vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(e.total_amount) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,e.created_on,'Expense' as particulars,'' as expense_type FROM expense e WHERE e.expense_date='$curdate' AND e.status!=2 GROUP BY e.vendor_id)UNION(SELECT ap.account_payment_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id,0 as oth_id,ap.payment_mode,ap.payment_type,0 as cpayment_amount,sum(ap.amount) as dpayment_amount,ap.cheque_no,ap.bank_name,ap.branch_name,ap.created_on,ap.particulars,ap.expense_type FROM account_payment ap,purchase p,vendor v,ledger_list ll WHERE ap.user_id = p.purchase_id AND p.vendor_id = v.vendor_id AND ap.ledger_list_id = ll.ledger_list_id AND ap.expense_type='vendor payment' AND ap.account_payment_date='$curdate' AND ap.status!=2 GROUP BY v.vendor_id)UNION(SELECT ap.account_payment_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id,0 as oth_id,ap.payment_mode,ap.payment_type,0 as cpayment_amount,sum(ap.amount) as dpayment_amount,ap.cheque_no,ap.bank_name,ap.branch_name,ap.created_on,ap.particulars,ap.expense_type FROM account_payment ap,expense e,vendor v,ledger_list ll WHERE ap.user_id = e.expense_id AND e.vendor_id = v.vendor_id AND ap.ledger_list_id = ll.ledger_list_id AND ap.expense_type='expense' AND ap.account_payment_date='$curdate' AND ap.status!=2 GROUP BY v.vendor_id)UNION(SELECT ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,ar.ledger_list2_id as vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(ar.amount) as cpayment_amount,0 as dpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars,'' as expense_type FROM account_journal ar WHERE ar.ledger_list2_type='vendor' AND ar.account_journal_date='$curdate' AND ar.status!=2 GROUP BY ar.ledger_list2_id)UNION(SELECT ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,ar.ledger_list1_id as vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(ar.amount) as dpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars,'' as expense_type FROM account_journal ar WHERE ar.ledger_list1_type='vendor' AND ar.account_journal_date='$curdate' AND ar.status!=2 GROUP BY ar.ledger_list1_id)UNION(SELECT ap.account_payment_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,ap.parti_ledger_list_id as vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(ap.amount) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,ap.created_on,ap.particulars,'' as expense_type FROM account_payment ap, ledger_list ll WHERE ap.ledger_list_id = ll.ledger_list_id AND ap.parti_ledger_list_type = 'vendor' AND ap.account_payment_date='$curdate' AND ap.status!=2 GROUP BY ap.parti_ledger_list_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id as vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(cdn.amount) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on, cdn.particulars,'' as expense_type FROM credit_debit_note cdn,vendor v WHERE cdn.vendor_id = v.vendor_id AND cdn.credit_debit = 'Debit' AND cdn.type_id = 0 AND cdn.credit_debit_note_date='$curdate' AND cdn.status!=2 GROUP BY v.vendor_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id as vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(cdn.amount) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on, cdn.particulars,'' as expense_type FROM credit_debit_note cdn,vendor v WHERE cdn.vendor_id = v.vendor_id AND cdn.credit_debit = 'Receipt' AND cdn.type_id = 0 AND cdn.credit_debit_note_date='$curdate' AND cdn.status!=2 GROUP BY v.vendor_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id as vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(cdn.amount) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on, cdn.particulars,'' as expense_type FROM credit_debit_note cdn,purchase p,grn g,vendor v WHERE cdn.type_id = p.purchase_id AND p.vendor_id = v.vendor_id AND p.grn_id = g.grn_id AND cdn.credit_debit = 'Debit' AND cdn.type_id != 0 AND cdn.credit_debit_note_date='$curdate' AND cdn.status!=2 GROUP BY v.vendor_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id as vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(cdn.amount) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on, cdn.particulars,'' as expense_type FROM credit_debit_note cdn,purchase p,grn g,vendor v WHERE cdn.type_id = p.purchase_id AND p.vendor_id = v.vendor_id AND p.grn_id = g.grn_id AND cdn.credit_debit = 'Receipt' AND cdn.type_id != 0 AND cdn.credit_debit_note_date='$curdate' AND cdn.status!=2 GROUP BY v.vendor_id)UNION(SELECT tp.tds_payable_line_item_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(tp.amount) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,tp.created_on,'TDS' as particulars,'' as expense_type FROM  tds_payable_line_item tp,expense e,vendor v WHERE tp.expense_id = e.expense_id AND e.vendor_id = v.vendor_id AND tp.tds_payable_line_item_date='$curdate' AND tp.status!=2 GROUP BY v.vendor_id)) ven GROUP BY ven.vendor_id ORDER BY ven.vendor_id ASC")->result_array();

$data['sundry_debtors_list'] = $this->db->query("SELECT s.customer_id,sum(s.dpayment_amount) AS dpamt,sum(s.cpayment_amount) AS cpamt FROM ((SELECT '' as pdate,'' as order_id,'' as invoice_id,c.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(cob.opening_balance) as dpayment_amount,0 as cpayment_amount,0 as cheque_no,0 as bank_name,0 as branch_name,c.created_on,'Opening Balance' as particulars FROM `customer` c,customer_opening_balance cob WHERE c.customer_id = cob.customer_id AND cob.crdr=1 AND DATE(c.created_on)='$curdate' GROUP BY c.customer_id)UNION(SELECT '' as pdate,'' as order_id,'' as invoice_id,c.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,0 as dpayment_amount,sum(cob.opening_balance) as cpayment_amount,0 as cheque_no,0 as bank_name,0 as branch_name,c.created_on,'Opening Balance' as particulars FROM `customer` c,customer_opening_balance cob WHERE c.customer_id = cob.customer_id AND cob.crdr=0 AND DATE(c.created_on)='$curdate' GROUP BY c.customer_id)UNION(SELECT i.invoice_date as pdate,i.order_id,i.invoice_id,i.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(i.grand_total) as dpayment_amount,0 as cpayment_amount,0 as cheque_no,0 as bank_name,0 as branch_name,i.created_on,'New Invoice' as particulars FROM `invoice` i WHERE i.order_id=0 AND i.invoice_date='$curdate' AND i.status!=20 GROUP BY i.customer_id)UNION(SELECT i.invoice_date as pdate,i.order_id,i.invoice_id,o.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(i.grand_total) as dpayment_amount,0 as cpayment_amount,0 as cheque_no,0 as bank_name,0 as branch_name,i.created_on,'Order Invoice' as particulars FROM invoice i,`order` o WHERE o.order_id = i.order_id AND i.invoice_date='$curdate' AND i.status!=20 GROUP BY o.customer_id)UNION(SELECT ar.account_receipt_date as pdate,ar.user_id as order_id,0 as invoice_id,o.customer_id,0 as user_id,ar.payment_mode,ar.payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_receipt ar, `order` o WHERE ar.user_id = o.order_id AND ar.user_type='Sales' AND ar.account_receipt_date='$curdate' AND ar.status!=2 GROUP BY o.customer_id)UNION(SELECT ar.account_receipt_date as pdate,0 as order_id,ar.user_id as invoice_id,i.customer_id,0 as user_id,ar.payment_mode,ar.payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_receipt ar, `invoice` i WHERE ar.user_id = i.invoice_id AND ar.user_type='Invoice' AND i.customer_id !=0 AND ar.account_receipt_date='$curdate' AND ar.status!=2 GROUP BY i.customer_id)UNION(SELECT ar.account_receipt_date as pdate,0 as order_id,ar.user_id as invoice_id,o.customer_id,0 as user_id,ar.payment_mode,ar.payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_receipt ar, `invoice` i,`order` o WHERE ar.user_id = i.invoice_id AND i.order_id = o.order_id AND ar.user_type='Invoice' AND ar.account_receipt_date='$curdate' AND ar.status!=2 GROUP BY o.customer_id)UNION(SELECT ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,ar.ledger_list2_id as customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_journal ar WHERE ar.ledger_list2_type='customer' AND ar.account_journal_date='$curdate' AND ar.status!=2 GROUP BY ar.ledger_list2_id)UNION(SELECT ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,ar.ledger_list1_id as customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(ar.amount) as dpayment_amount,0 as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_journal ar WHERE ar.ledger_list1_type='customer' AND ar.account_journal_date='$curdate' AND ar.status!=2 GROUP BY ar.ledger_list1_id)UNION(SELECT ar.account_receipt_date as pdate,0 as order_id,0 as invoice_id,ar.parti_ledger_list_id as customer_id,0 as user_id,ar.payment_mode,ar.payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_receipt ar, ledger_list ll, location l WHERE ar.ledger_list_id = ll.ledger_list_id AND ll.location_id = l.location_id AND ar.parti_ledger_list_type = 'customer' AND ar.account_receipt_date='$curdate' AND ar.status!=2 GROUP BY ar.parti_ledger_list_id)UNION(SELECT credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,0 as dpayment_amount,sum(amount) as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,created_on,'' as particulars FROM credit_debit_note WHERE credit_debit='Credit' AND type_id = 0 AND credit_debit_note_date='$curdate' AND status!=2 GROUP BY customer_id)UNION(SELECT credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(amount) as dpayment_amount,0 as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,created_on,'' as particulars FROM credit_debit_note WHERE credit_debit='Payment' AND type_id = 0 AND credit_debit_note_date='$curdate' AND status!=2 GROUP BY customer_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,i.customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,0 as dpayment_amount,sum(amount) as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'' as particulars FROM credit_debit_note cdn, `invoice` i, customer c WHERE cdn.type_id = i.invoice_id AND i.customer_id = c.customer_id AND i.customer_id !=0 AND cdn.credit_debit='Credit' AND cdn.type_id != 0 AND cdn.credit_debit_note_date='$curdate' AND cdn.status!=2 GROUP BY i.customer_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,i.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(amount) as dpayment_amount,0 as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'' as particulars FROM credit_debit_note cdn, `invoice` i, customer c WHERE cdn.type_id = i.invoice_id AND i.customer_id = c.customer_id AND i.customer_id !=0 AND cdn.credit_debit='Payment' AND cdn.type_id != 0 AND cdn.credit_debit_note_date='$curdate' AND cdn.status!=2 GROUP BY i.customer_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,o.customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,0 as dpayment_amount,sum(amount) as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'' as particulars FROM credit_debit_note cdn, `invoice` i,`order` o, customer c WHERE cdn.type_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND i.customer_id =0 AND cdn.credit_debit='Credit' AND cdn.type_id != 0 AND cdn.credit_debit_note_date='$curdate' AND cdn.status!=2 GROUP BY o.customer_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,o.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(amount) as dpayment_amount,0 as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'' as particulars FROM credit_debit_note cdn, `invoice` i,`order` o, customer c WHERE cdn.type_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND i.customer_id =0 AND cdn.credit_debit='Payment' AND cdn.type_id != 0 AND cdn.credit_debit_note_date='$curdate' AND cdn.status!=2 GROUP BY o.customer_id)) s GROUP BY s.customer_id ORDER BY s.customer_id ASC")->result_array();

$data['purchase_amt'] = $this->Profitloss_model->get_purchase_amount_by_singledate($curdate);
        $data['direct_expense_group'] = $this->Profitloss_model->get_direct_expense_voucher_group();
        $data['in_direct_expense'] = $this->Profitloss_model->get_in_direct_expense_by_singledate($curdate);
        $data['sales_amt'] = $this->Profitloss_model->get_sales_amount_by_singledate($curdate);
        $data['direct_invoice'] = $this->Profitloss_model->get_direct_invoice_amount_by_singledate($curdate);

$data['direct_expense'] = $this->Profitloss_model->get_direct_expense_by_singledate($curdate);
        $data['direct_expense_list'] = $this->Profitloss_model->get_expense_staff_expense_by_singledate($curdate);

}

$data['direct_expense_ledger'] = $this->Profitloss_model->get_direct_expense_ledger();
      $data['indirect_expense_ledger'] = $this->Profitloss_model->get_indirect_expense_ledger();
      $data['direct_expense_group'] = $this->Profitloss_model->get_direct_expense_voucher_group();
      $data['in_direct_expense_group'] = $this->Profitloss_model->get_indirect_expense_voucher_group();

$data['direct_income_ledger'] = $this->Profitloss_model->get_direct_income_ledger_list();
      $data['indirect_income_ledger'] = $this->Profitloss_model->get_indirect_income_ledger_list();

//$data['location_list'] = $this->Ledger_model->get_location_list();

$this->load->view('trialbalance/trialbalance_list',$data);
  }

public function trialbalance_export($cid = null,$psearch=null,$dtrange=null,$drnge=null,$cstock=null,$ostock=null)
  {
      $liabilities_list = $this->Balancesheet_model->get_liabilities_list();
      $assets_list = $this->Balancesheet_model->get_assets_list();
      $location_list = $this->Balancesheet_model->get_location_list();

/*$cstock = $this->input->post('cstock');
      $ostock = $this->input->post('ostock');*/
      $cstock = $cstock;
      $ostock = $ostock;
      if($psearch=='curdate')
      {
        $curdate = date('Y-m-d');
        $fdate = $curdate;
        //$tdate = '';
        $tdate = $fdate;
      }
      elseif($psearch=='date')
      {
        if($dtrange!='')
        {
          $odt = explode('/', str_replace('_', '/', $dtrange));
          $curdate = $odt[2].'-'.$odt[0].'-'.$odt[1];
        }
        else
        {
          $curdate = date('Y-m-d');
        }
        $fdate = $curdate;
        //$tdate = '';
        $tdate = $fdate;
      }
      else
      {
        //echo $drnge;
        $dtrange = str_replace('_', '/', str_replace('-', ' - ', $drnge));
        //echo $dtrange;exit;
        $dr = explode(' - ', $dtrange);

$fd = explode('/', $dr[0]);
        $td = explode('/', $dr[1]);

$fdate = $fd[2].'-'.$fd[0].'-'.$fd[1];
        $tdate = $td[2].'-'.$td[0].'-'.$td[1];

$pDay = new DateTime(date($fdate).' - 1 day');
        $pdate = $pDay->format('Y-m-d');
        $psdate = '2019-04-01';
      }

$company_id = $cid;
      //$data['company_id'] = $company_id;

if($company_id!='-')
      {
        $cid = 'AND location_id='.$company_id;
        $decid = 'AND l.location_id='.$company_id;
        $purid = 'AND po.location_id='.$company_id;
        $ajcid = 'AND aj.location_id='.$company_id;
        $loc = 'AND po.location_id='.$company_id;
        $iloc = 'AND ll.location_id='.$company_id;
        $eloc = 'AND e.location_id='.$company_id;
        $oloc = 'AND o.location_id='.$company_id;
        $invloc = 'AND i.location_id='.$company_id;
        $ajloc = 'AND ar.location_id='.$company_id;
        $cob = 'AND cob.location_id='.$company_id;
        $vob = 'AND vob.location_id='.$company_id;
        $gloc = 'AND g.location_id='.$company_id;
        $cdnloc = 'AND cdn.location_id='.$company_id;
        $slocid = 'AND e.location_id='.$company_id;
        $compDet = $this->Profitloss_model->get_location_by_id($company_id);
        $data['compDet'] = $compDet->location_name;
        $compqry = " AND o.location_id='".$company_id."'";
        $dcompqry = " AND i.location_id='".$company_id."'";
      }
      else
      {
        $cid = '';
        $decid = '';
        $purid = '';
        $ajcid = '';
        $loc = '';
        $iloc = '';
        $eloc = '';
        $oloc = '';
        $invloc = '';
        $ajloc = '';
        $cob = '';
        $vob = '';
        $gloc = '';
        $cdnloc = '';
        $slocid = '';
        $data['compDet'] = '';
        $compqry = "";
        $dcompqry = "";
      }
        
        if($psearch!='daterange')
        {

$sundry_debtors_list = $this->db->query("SELECT s.customer_id,sum(s.dpayment_amount) AS dpamt,sum(s.cpayment_amount) AS cpamt FROM ((SELECT '' as pdate,'' as order_id,'' as invoice_id,c.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(cob.opening_balance) as dpayment_amount,0 as cpayment_amount,0 as cheque_no,0 as bank_name,0 as branch_name,c.created_on,'Opening Balance' as particulars FROM `customer` c,customer_opening_balance cob WHERE c.customer_id = cob.customer_id AND cob.crdr=1 AND DATE(c.created_on)='$curdate' $cob GROUP BY c.customer_id)UNION(SELECT '' as pdate,'' as order_id,'' as invoice_id,c.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,0 as dpayment_amount,sum(cob.opening_balance) as cpayment_amount,0 as cheque_no,0 as bank_name,0 as branch_name,c.created_on,'Opening Balance' as particulars FROM `customer` c,customer_opening_balance cob WHERE c.customer_id = cob.customer_id AND cob.crdr=0 AND DATE(c.created_on)='$curdate' $cob GROUP BY c.customer_id)UNION(SELECT i.invoice_date as pdate,i.order_id,i.invoice_id,i.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(i.grand_total) as dpayment_amount,0 as cpayment_amount,0 as cheque_no,0 as bank_name,0 as branch_name,i.created_on,'New Invoice' as particulars FROM `invoice` i WHERE i.order_id=0 AND i.status!=20 AND i.invoice_date='$curdate' $invloc GROUP BY i.customer_id)UNION(SELECT i.invoice_date as pdate,i.order_id,i.invoice_id,o.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(i.grand_total) as dpayment_amount,0 as cpayment_amount,0 as cheque_no,0 as bank_name,0 as branch_name,i.created_on,'Order Invoice' as particulars FROM invoice i,`order` o WHERE o.order_id = i.order_id AND i.status!=20 AND i.invoice_date='$curdate' $oloc GROUP BY o.customer_id)UNION(SELECT ar.account_receipt_date as pdate,ar.user_id as order_id,0 as invoice_id,o.customer_id,0 as user_id,ar.payment_mode,ar.payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_receipt ar, `order` o WHERE ar.user_id = o.order_id AND ar.user_type='Sales' AND ar.account_receipt_date='$curdate' $oloc AND ar.status!=2 GROUP BY o.customer_id)UNION(SELECT ar.account_receipt_date as pdate,0 as order_id,ar.user_id as invoice_id,i.customer_id,0 as user_id,ar.payment_mode,ar.payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_receipt ar, `invoice` i WHERE ar.user_id = i.invoice_id AND ar.user_type='Invoice' AND i.customer_id !=0 AND ar.account_receipt_date='$curdate' $invloc AND ar.status!=2 GROUP BY i.customer_id)UNION(SELECT ar.account_receipt_date as pdate,0 as order_id,ar.user_id as invoice_id,o.customer_id,0 as user_id,ar.payment_mode,ar.payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_receipt ar, `invoice` i,`order` o WHERE ar.user_id = i.invoice_id AND i.order_id = o.order_id AND ar.user_type='Invoice' AND ar.account_receipt_date='$curdate' $oloc AND ar.status!=2 GROUP BY o.customer_id)UNION(SELECT ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,ar.ledger_list2_id as customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_journal ar WHERE ar.ledger_list2_type='customer' AND ar.account_journal_date='$curdate' $ajloc AND ar.status!=2 GROUP BY ar.ledger_list2_id)UNION(SELECT ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,ar.ledger_list1_id as customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(ar.amount) as dpayment_amount,0 as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_journal ar WHERE ar.ledger_list1_type='customer' AND ar.account_journal_date='$curdate' $ajloc AND ar.status!=2 GROUP BY ar.ledger_list1_id)UNION(SELECT ar.account_receipt_date as pdate,0 as order_id,0 as invoice_id,ar.parti_ledger_list_id as customer_id,0 as user_id,ar.payment_mode,ar.payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_receipt ar, ledger_list ll, location l WHERE ar.ledger_list_id = ll.ledger_list_id AND ll.location_id = l.location_id AND ar.parti_ledger_list_type = 'customer' AND ar.account_receipt_date='$curdate' $iloc AND ar.status!=2 GROUP BY ar.parti_ledger_list_id)UNION(SELECT credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,0 as dpayment_amount,sum(amount) as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,created_on,'' as particulars FROM credit_debit_note WHERE credit_debit='Credit' AND type_id = 0 AND status!=2 AND credit_debit_note_date='$curdate' $cid GROUP BY customer_id)UNION(SELECT credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(amount) as dpayment_amount,0 as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,created_on,'' as particulars FROM credit_debit_note WHERE credit_debit='Payment' AND type_id = 0 AND credit_debit_note_date='$curdate' $cid AND status!=2 GROUP BY customer_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,i.customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,0 as dpayment_amount,sum(amount) as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'' as particulars FROM credit_debit_note cdn, `invoice` i, customer c WHERE cdn.type_id = i.invoice_id AND i.customer_id = c.customer_id AND i.customer_id !=0 AND cdn.credit_debit='Credit' AND cdn.type_id != 0 AND cdn.credit_debit_note_date='$curdate' $cdnloc AND cdn.status!=2 GROUP BY i.customer_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,i.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(amount) as dpayment_amount,0 as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'' as particulars FROM credit_debit_note cdn, `invoice` i, customer c WHERE cdn.type_id = i.invoice_id AND i.customer_id = c.customer_id AND i.customer_id !=0 AND cdn.credit_debit='Payment' AND cdn.type_id != 0 AND cdn.credit_debit_note_date='$curdate' $cdnloc AND cdn.status!=2 GROUP BY i.customer_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,o.customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,0 as dpayment_amount,sum(amount) as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'' as particulars FROM credit_debit_note cdn, `invoice` i,`order` o, customer c WHERE cdn.type_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND i.customer_id =0 AND cdn.credit_debit='Credit' AND cdn.type_id != 0 AND cdn.credit_debit_note_date='$curdate' $cdnloc AND cdn.status!=2 GROUP BY o.customer_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,o.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(amount) as dpayment_amount,0 as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'' as particulars FROM credit_debit_note cdn, `invoice` i,`order` o, customer c WHERE cdn.type_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND i.customer_id =0 AND cdn.credit_debit='Payment' AND cdn.type_id != 0 AND cdn.credit_debit_note_date='$curdate' $cdnloc AND cdn.status!=2 GROUP BY o.customer_id)) s GROUP BY s.customer_id ORDER BY s.customer_id ASC")->result_array();

$sundry_creditors_list = $this->db->query("SELECT ven.vendor_id,sum(ven.dpayment_amount) AS dpamt,sum(ven.cpayment_amount) AS cpamt FROM ((SELECT v.created_on as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(vob.opening_balance) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,v.created_on,'Opening Balance' as particulars,'' as expense_type FROM vendor v,vendor_opening_balance vob WHERE v.vendor_id = vob.vendor_id AND vob.crdr=0 $vob GROUP BY v.vendor_id)UNION(SELECT v.created_on as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(vob.opening_balance) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,v.created_on,'Opening Balance' as particulars,'' as expense_type FROM vendor v, vendor_opening_balance vob WHERE v.vendor_id = vob.vendor_id AND vob.crdr=1 $vob GROUP BY v.vendor_id)UNION(SELECT p.purchase_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,p.vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(p.total_amount) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,p.created_on,'New Purchase' as particulars,'' as expense_type FROM purchase p,purchase_order po WHERE p.purchase_order_id = po.purchase_order_id AND p.purchase_date='$curdate' $loc AND p.status=1 AND p.status!=2 GROUP BY p.vendor_id)UNION(SELECT e.expense_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,e.vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(e.total_amount) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,e.created_on,'Expense' as particulars,'' as expense_type FROM expense e WHERE e.expense_date='$curdate' $eloc AND e.status!=2 GROUP BY e.vendor_id)UNION(SELECT ap.account_payment_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id,0 as oth_id,ap.payment_mode,ap.payment_type,0 as cpayment_amount,sum(ap.amount) as dpayment_amount,ap.cheque_no,ap.bank_name,ap.branch_name,ap.created_on,ap.particulars,ap.expense_type FROM account_payment ap,purchase p,vendor v,ledger_list ll WHERE ap.user_id = p.purchase_id AND p.vendor_id = v.vendor_id AND ap.ledger_list_id = ll.ledger_list_id $iloc AND ap.expense_type='vendor payment' AND ap.account_payment_date='$curdate' AND ap.status!=2 GROUP BY v.vendor_id)UNION(SELECT ap.account_payment_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id,0 as oth_id,ap.payment_mode,ap.payment_type,0 as cpayment_amount,sum(ap.amount) as dpayment_amount,ap.cheque_no,ap.bank_name,ap.branch_name,ap.created_on,ap.particulars,ap.expense_type FROM account_payment ap,expense e,vendor v,ledger_list ll WHERE ap.user_id = e.expense_id AND e.vendor_id = v.vendor_id AND ap.ledger_list_id = ll.ledger_list_id $iloc AND ap.expense_type='expense' AND ap.account_payment_date='$curdate' AND ap.status!=2 GROUP BY v.vendor_id)UNION(SELECT ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,ar.ledger_list2_id as vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(ar.amount) as cpayment_amount,0 as dpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars,'' as expense_type FROM account_journal ar WHERE ar.ledger_list2_type='vendor' AND ar.account_journal_date='$curdate' $ajloc AND ar.status!=2 GROUP BY ar.ledger_list2_id)UNION(SELECT ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,ar.ledger_list1_id as vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(ar.amount) as dpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars,'' as expense_type FROM account_journal ar WHERE ar.ledger_list1_type='vendor' AND ar.account_journal_date='$curdate' $ajloc AND ar.status!=2 GROUP BY ar.ledger_list1_id)UNION(SELECT ap.account_payment_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,ap.parti_ledger_list_id as vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(ap.amount) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,ap.created_on,ap.particulars,'' as expense_type FROM account_payment ap, ledger_list ll WHERE ap.ledger_list_id = ll.ledger_list_id AND ap.parti_ledger_list_type = 'vendor' AND ap.account_payment_date='$curdate' $iloc AND ap.status!=2 GROUP BY ap.parti_ledger_list_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id as vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(cdn.amount) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on, cdn.particulars,'' as expense_type FROM credit_debit_note cdn,vendor v WHERE cdn.vendor_id = v.vendor_id AND cdn.credit_debit = 'Debit' AND cdn.type_id = 0 AND cdn.credit_debit_note_date='$curdate' $cdnloc AND cdn.status!=2 GROUP BY v.vendor_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id as vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(cdn.amount) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on, cdn.particulars,'' as expense_type FROM credit_debit_note cdn,vendor v WHERE cdn.vendor_id = v.vendor_id AND cdn.credit_debit = 'Receipt' AND cdn.type_id = 0 AND cdn.credit_debit_note_date='$curdate' $cdnloc AND cdn.status!=2 GROUP BY v.vendor_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id as vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(cdn.amount) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on, cdn.particulars,'' as expense_type FROM credit_debit_note cdn,purchase p,grn g,vendor v WHERE cdn.type_id = p.purchase_id AND p.vendor_id = v.vendor_id AND p.grn_id = g.grn_id AND cdn.credit_debit = 'Debit' AND cdn.type_id != 0 AND cdn.credit_debit_note_date='$curdate' $cdnloc AND cdn.status!=2 GROUP BY v.vendor_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id as vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(cdn.amount) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on, cdn.particulars,'' as expense_type FROM credit_debit_note cdn,purchase p,grn g,vendor v WHERE cdn.type_id = p.purchase_id AND p.vendor_id = v.vendor_id AND p.grn_id = g.grn_id AND cdn.credit_debit = 'Receipt' AND cdn.type_id != 0 AND cdn.credit_debit_note_date='$curdate' $cdnloc AND cdn.status!=2 GROUP BY v.vendor_id)UNION(SELECT tp.tds_payable_line_item_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(tp.amount) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,tp.created_on,'TDS' as particulars,'' as expense_type FROM  tds_payable_line_item tp,expense e,vendor v WHERE tp.expense_id = e.expense_id AND e.vendor_id = v.vendor_id AND tp.tds_payable_line_item_date='$curdate' $eloc AND tp.status!=2 GROUP BY v.vendor_id)) ven GROUP BY ven.vendor_id ORDER BY ven.vendor_id ASC")->result_array();

$account_journal_list = $this->db->query("SELECT aj.*,aa.account_attributes, sum(aj.amount) as tamt FROM account_journal aj,account_attributes aa WHERE aj.account_attributes_id = aa.account_attributes_id AND aj.account_journal_date = '$curdate' $ajcid GROUP BY aj.account_attributes_id ORDER BY aj.account_journal_id DESC")->result_array();

$purchase_amt = $this->db->query("SELECT sum(p.taxable_amount) as amt,sum(p.other_charges) as ocharge FROM `purchase`p, purchase_order po WHERE p.purchase_order_id = po.purchase_order_id AND p.invoice_date='$curdate' AND p.status!=5 $purid")->row();

$in_direct_expense = $this->db->query("SELECT led.ledger_group,led.ledger_group_id,sum(led.tamt) as amt FROM ((SELECT e.expense_date,e.invoice_no,sum(e.taxable_value) as tamt,e.other_charges,e.sgst,e.cgst,e.igst,e.total_amount,e.tds_value,v.vendor_name,v.gst_no,lg.ledger_group,lg.ledger_group_id FROM `expense` e,vendor v,ledger_group lg,ledger_type lt WHERE e.vendor_id = v.vendor_id AND e.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND e.expense_date='$curdate' AND e.status!=2 AND lt.expense_type = 'In Direct' $slocid GROUP BY lg.ledger_group_id) UNION (SELECT l.ledger_date,l.ledger_no as invoice_no,sum(l.amount) as tamt,0 as other_charges,0 as sgst,0 as cgst,0 as igst,l.amount as total_amount,0 as tds_value,l.payee_name as vendor_name ,'' as gst_no,lg.ledger_group,lg.ledger_group_id FROM ledger l,ledger_sub_group lsg,ledger_group lg,ledger_type lt WHERE l.ledger_sub_group_id = lsg.ledger_sub_group_id AND lsg.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND l.status=0 AND lt.expense_type = 'In Direct' AND l.ledger_date='$curdate' $decid GROUP BY lg.ledger_group_id)UNION(SELECT e.staff_expense_date,e.voucher_no as invoice_no,sum(e.net_salary) as tamt,0 as other_charges,0 as sgst,0 as cgst,0 as igst,e.net_salary as total_amount,0 as tds_value,'' as vendor_name ,'' as gst_no,lg.ledger_group,lg.ledger_group_id FROM staff_expense e,ledger_group lg,ledger_type lt WHERE e.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND e.status != 2 AND lt.expense_type = 'In Direct' AND e.staff_expense_date='$curdate' $slocid GROUP BY lg.ledger_group_id)) led GROUP BY led.ledger_group_id ")->result_array();

$sales_amt = $this->db->query("SELECT sum(a.taxamt) as txamt,sum(a.pcharge) as pc, sum(a.ocharge) as oc FROM ((SELECT sum(i.taxable_amount) as taxamt,sum(i.packing_charges) as pcharge,sum(i.other_charges) as ocharge FROM invoice i,`order` o WHERE i.order_id = o.order_id AND i.invoice_date='$curdate' $compqry)UNION(SELECT sum(i.taxable_amount) as taxamt,sum(i.packing_charges) as pcharge,sum(i.other_charges) as ocharge FROM invoice i WHERE i.invoice_date='$curdate' $dcompqry)) a")->row();

$direct_invoice = $this->db->query("SELECT sum(grand_total) as amt FROM `invoice` WHERE invoice_date='$curdate' AND customer_id!=0 $cid")->row();

$direct_expense = $this->db->query("SELECT lg.ledger_group_id,lsg.ledger_sub_group,sum(l.amount) as amt FROM `ledger` l,ledger_sub_group lsg,ledger_group lg,ledger_type lt WHERE l.ledger_sub_group_id = lsg.ledger_sub_group_id AND lsg.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND l.ledger_date='$curdate' $decid AND l.status=0 AND lt.expense_type = 'Direct' GROUP BY l.ledger_sub_group_id")->result_array();

$direct_expense_list = $this->db->query("SELECT s.ledger_group_id,s.ledger_group, sum(s.amt) as amount FROM ((SELECT lg.ledger_group_id, lg.ledger_group,sum(e.taxable_value) as amt FROM expense e,ledger_group lg,ledger_type lt WHERE e.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND e.status != 2 AND lt.expense_type = 'Direct' AND e.expense_date>='$fdate' AND e.expense_date<='$tdate' $slocid GROUP BY lg.ledger_group_id)UNION(SELECT lg.ledger_group_id, lg.ledger_group,sum(e.net_salary) as amt FROM staff_expense e,ledger_group lg,ledger_type lt WHERE e.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND e.status != 2 AND lt.expense_type = 'Direct' AND e.staff_expense_date='$curdate' $slocid GROUP BY lg.ledger_group_id)) s GROUP BY s.ledger_group_id ")->result_array();

}
        else
        {
          $sundry_debtors_list = $this->db->query("SELECT s.customer_id,sum(s.dpayment_amount) AS dpamt,sum(s.cpayment_amount) AS cpamt FROM ((SELECT i.invoice_date as pdate,i.order_id,i.invoice_id,i.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(i.grand_total) as dpayment_amount,0 as cpayment_amount,0 as cheque_no,0 as bank_name,0 as branch_name,i.created_on,'New Invoice' as particulars FROM `invoice` i WHERE i.order_id=0 AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' $invloc AND i.status!=20 GROUP BY i.customer_id)UNION(SELECT i.invoice_date as pdate,i.order_id,i.invoice_id,o.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(i.grand_total) as dpayment_amount,0 as cpayment_amount,0 as cheque_no,0 as bank_name,0 as branch_name,i.created_on,'Order Invoice' as particulars FROM invoice i,`order` o WHERE o.order_id = i.order_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' $oloc AND i.status!=20 GROUP BY o.customer_id)UNION(SELECT ar.account_receipt_date as pdate,ar.user_id as order_id,0 as invoice_id,o.customer_id,0 as user_id,ar.payment_mode,ar.payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_receipt ar, `order` o WHERE ar.user_id = o.order_id AND ar.user_type='Sales' AND ar.account_receipt_date>='$fdate' AND ar.account_receipt_date<='$tdate' $oloc AND ar.status!=2 GROUP BY o.customer_id)UNION(SELECT ar.account_receipt_date as pdate,0 as order_id,ar.user_id as invoice_id,i.customer_id,0 as user_id,ar.payment_mode,ar.payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_receipt ar, `invoice` i WHERE ar.user_id = i.invoice_id AND ar.user_type='Invoice' AND i.customer_id !=0 AND ar.account_receipt_date>='$fdate' AND ar.account_receipt_date<='$tdate' $invloc AND ar.status!=2 GROUP BY i.customer_id)UNION(SELECT ar.account_receipt_date as pdate,0 as order_id,ar.user_id as invoice_id,o.customer_id,0 as user_id,ar.payment_mode,ar.payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_receipt ar, `invoice` i,`order` o WHERE ar.user_id = i.invoice_id AND i.order_id = o.order_id AND ar.user_type='Invoice' AND ar.account_receipt_date>='$fdate' AND ar.account_receipt_date<='$tdate' $oloc AND ar.status!=2 GROUP BY o.customer_id)UNION(SELECT ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,ar.ledger_list2_id as customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_journal ar WHERE ar.ledger_list2_type='customer' AND ar.account_journal_date>='$fdate' AND ar.account_journal_date<='$tdate' $ajloc AND ar.status!=2 GROUP BY ar.ledger_list2_id)UNION(SELECT ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,ar.ledger_list1_id as customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(ar.amount) as dpayment_amount,0 as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_journal ar WHERE ar.ledger_list1_type='customer' AND ar.account_journal_date>='$fdate' AND ar.account_journal_date<='$tdate' $ajloc AND ar.status!=2 GROUP BY ar.ledger_list1_id)UNION(SELECT ar.account_receipt_date as pdate,0 as order_id,0 as invoice_id,ar.parti_ledger_list_id as customer_id,0 as user_id,ar.payment_mode,ar.payment_type,0 as dpayment_amount,sum(ar.amount) as cpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars FROM account_receipt ar, ledger_list ll, location l WHERE ar.ledger_list_id = ll.ledger_list_id AND ll.location_id = l.location_id AND ar.parti_ledger_list_type = 'customer' AND ar.account_receipt_date>='$fdate' AND ar.account_receipt_date<='$tdate' $iloc AND ar.status!=2 GROUP BY ar.parti_ledger_list_id)UNION(SELECT credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,0 as dpayment_amount,sum(amount) as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,created_on,'' as particulars FROM credit_debit_note WHERE credit_debit='Credit' AND type_id = 0 AND credit_debit_note_date >= '$fdate' AND credit_debit_note_date <= '$tdate' $cid AND status!=2 GROUP BY customer_id)UNION(SELECT credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(amount) as dpayment_amount,0 as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,created_on,'' as particulars FROM credit_debit_note WHERE credit_debit='Payment' AND type_id = 0 AND credit_debit_note_date >= '$fdate' AND credit_debit_note_date <= '$tdate' $cid AND status!=2 GROUP BY customer_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,i.customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,0 as dpayment_amount,sum(amount) as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'' as particulars FROM credit_debit_note cdn, `invoice` i, customer c WHERE cdn.type_id = i.invoice_id AND i.customer_id = c.customer_id AND i.customer_id !=0 AND cdn.credit_debit='Credit' AND cdn.type_id != 0 AND cdn.credit_debit_note_date >= '$fdate' AND cdn.credit_debit_note_date <= '$tdate' $cdnloc AND cdn.status!=2 GROUP BY i.customer_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,i.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(amount) as dpayment_amount,0 as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'' as particulars FROM credit_debit_note cdn, `invoice` i, customer c WHERE cdn.type_id = i.invoice_id AND i.customer_id = c.customer_id AND i.customer_id !=0 AND cdn.credit_debit='Payment' AND cdn.type_id != 0 AND cdn.credit_debit_note_date >= '$fdate' AND cdn.credit_debit_note_date <= '$tdate' $cdnloc AND cdn.status!=2 GROUP BY i.customer_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,o.customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,0 as dpayment_amount,sum(amount) as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'' as particulars FROM credit_debit_note cdn, `invoice` i,`order` o, customer c WHERE cdn.type_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND i.customer_id =0 AND cdn.credit_debit='Credit' AND cdn.type_id != 0 AND cdn.credit_debit_note_date >= '$fdate' AND cdn.credit_debit_note_date <= '$tdate' $cdnloc AND cdn.status!=2 GROUP BY o.customer_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,o.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(amount) as dpayment_amount,0 as cpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'' as particulars FROM credit_debit_note cdn, `invoice` i,`order` o, customer c WHERE cdn.type_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND i.customer_id =0 AND cdn.credit_debit='Payment' AND cdn.type_id != 0 AND cdn.credit_debit_note_date >= '$fdate' AND cdn.credit_debit_note_date <= '$tdate' $cdnloc AND cdn.status!=2 GROUP BY o.customer_id)) s GROUP BY s.customer_id ORDER BY s.customer_id ASC")->result_array();

$sundry_creditors_list = $this->db->query("SELECT ven.vendor_id,sum(ven.dpayment_amount) AS dpamt,sum(ven.cpayment_amount) AS cpamt FROM ((SELECT p.purchase_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,p.vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(p.total_amount) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,p.created_on,'New Purchase' as particulars,'' as expense_type FROM purchase p,purchase_order po WHERE p.purchase_order_id = po.purchase_order_id AND p.purchase_date>='$fdate' AND p.purchase_date<='$tdate' $loc AND p.status=1 AND p.status!=2 GROUP BY p.vendor_id)UNION(SELECT e.expense_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,e.vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(e.total_amount) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,e.created_on,'Expense' as particulars,'' as expense_type FROM expense e WHERE e.expense_date>='$fdate' AND e.expense_date<='$tdate' $eloc AND e.status!=2 GROUP BY e.vendor_id)UNION(SELECT ap.account_payment_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id,0 as oth_id,ap.payment_mode,ap.payment_type,0 as cpayment_amount,sum(ap.amount) as dpayment_amount,ap.cheque_no,ap.bank_name,ap.branch_name,ap.created_on,ap.particulars,ap.expense_type FROM account_payment ap,purchase p,vendor v,ledger_list ll WHERE ap.user_id = p.purchase_id AND p.vendor_id = v.vendor_id AND ap.ledger_list_id = ll.ledger_list_id $iloc AND ap.expense_type='vendor payment' AND ap.account_payment_date>='$fdate' AND ap.account_payment_date<='$tdate' AND ap.status!=2 GROUP BY v.vendor_id)UNION(SELECT ap.account_payment_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id,0 as oth_id,ap.payment_mode,ap.payment_type,0 as cpayment_amount,sum(ap.amount) as dpayment_amount,ap.cheque_no,ap.bank_name,ap.branch_name,ap.created_on,ap.particulars,ap.expense_type FROM account_payment ap,expense e,vendor v,ledger_list ll WHERE ap.user_id = e.expense_id AND e.vendor_id = v.vendor_id AND ap.ledger_list_id = ll.ledger_list_id $iloc AND ap.expense_type='expense' AND ap.account_payment_date>='$fdate' AND ap.account_payment_date<='$tdate' AND ap.status!=2 GROUP BY v.vendor_id)UNION(SELECT ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,ar.ledger_list2_id as vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(ar.amount) as cpayment_amount,0 as dpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars,'' as expense_type FROM account_journal ar WHERE ar.ledger_list2_type='vendor' AND ar.account_journal_date>='$fdate' AND ar.account_journal_date<='$tdate' $ajloc AND ar.status!=2 GROUP BY ar.ledger_list2_id)UNION(SELECT ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,ar.ledger_list1_id as vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(ar.amount) as dpayment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars,'' as expense_type FROM account_journal ar WHERE ar.ledger_list1_type='vendor' AND ar.account_journal_date>='$fdate' AND ar.account_journal_date<='$tdate' $ajloc AND ar.status!=2 GROUP BY ar.ledger_list1_id)UNION(SELECT ap.account_payment_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,ap.parti_ledger_list_id as vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(ap.amount) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,ap.created_on,ap.particulars,'' as expense_type FROM account_payment ap, ledger_list ll WHERE ap.ledger_list_id = ll.ledger_list_id AND ap.parti_ledger_list_type = 'vendor' AND ap.account_payment_date>='$fdate' AND ap.account_payment_date<='$tdate' $iloc AND ap.status!=2 GROUP BY ap.parti_ledger_list_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id as vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(cdn.amount) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on, cdn.particulars,'' as expense_type FROM credit_debit_note cdn,vendor v WHERE cdn.vendor_id = v.vendor_id AND cdn.credit_debit = 'Debit' AND cdn.type_id = 0 AND cdn.credit_debit_note_date>='$fdate' AND cdn.credit_debit_note_date<='$tdate' $cdnloc AND cdn.status!=2 GROUP BY v.vendor_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id as vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(cdn.amount) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on, cdn.particulars,'' as expense_type FROM credit_debit_note cdn,vendor v WHERE cdn.vendor_id = v.vendor_id AND cdn.credit_debit = 'Receipt' AND cdn.type_id = 0 AND cdn.credit_debit_note_date>='$fdate' AND cdn.credit_debit_note_date<='$tdate' $cdnloc AND cdn.status!=2 GROUP BY v.vendor_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id as vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(cdn.amount) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on, cdn.particulars,'' as expense_type FROM credit_debit_note cdn,purchase p,grn g,vendor v WHERE cdn.type_id = p.purchase_id AND p.vendor_id = v.vendor_id AND p.grn_id = g.grn_id AND cdn.credit_debit = 'Debit' AND cdn.type_id != 0 AND cdn.credit_debit_note_date>='$fdate' AND cdn.credit_debit_note_date<='$tdate' $cdnloc AND cdn.status!=2 GROUP BY v.vendor_id)UNION(SELECT cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id as vendor_id,0 as oth_id,'' as payment_mode,'credit' as payment_type,sum(cdn.amount) as cpayment_amount,0 as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on, cdn.particulars,'' as expense_type FROM credit_debit_note cdn,purchase p,grn g,vendor v WHERE cdn.type_id = p.purchase_id AND p.vendor_id = v.vendor_id AND p.grn_id = g.grn_id AND cdn.credit_debit = 'Receipt' AND cdn.type_id != 0 AND cdn.credit_debit_note_date>='$fdate' AND cdn.credit_debit_note_date<='$tdate' $cdnloc AND cdn.status!=2 GROUP BY v.vendor_id)UNION(SELECT tp.tds_payable_line_item_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id,0 as oth_id,'' as payment_mode,'debit' as payment_type,0 as cpayment_amount,sum(tp.amount) as dpayment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,tp.created_on,'TDS' as particulars,'' as expense_type FROM  tds_payable_line_item tp,expense e,vendor v WHERE tp.expense_id = e.expense_id AND e.vendor_id = v.vendor_id AND tp.tds_payable_line_item_date>='$fdate' AND tp.tds_payable_line_item_date<='$tdate' $eloc AND tp.status!=2 GROUP BY v.vendor_id)) ven GROUP BY ven.vendor_id ORDER BY ven.vendor_id ASC")->result_array();

$account_journal_list = $this->db->query("SELECT aj.*,aa.account_attributes, sum(aj.amount) as tamt FROM account_journal aj,account_attributes aa WHERE aj.account_attributes_id = aa.account_attributes_id AND aj.account_journal_date >= '$fdate' AND aj.account_journal_date <= '$tdate' $ajcid GROUP BY aj.account_attributes_id ORDER BY aj.account_journal_id DESC")->result_array();

$in_direct_expense = $this->db->query("SELECT led.ledger_group,led.ledger_group_id,sum(led.tamt) as amt FROM ((SELECT e.expense_date,e.invoice_no,sum(e.taxable_value) as tamt,e.other_charges,e.sgst,e.cgst,e.igst,e.total_amount,e.tds_value,v.vendor_name,v.gst_no,lg.ledger_group,lg.ledger_group_id FROM `expense` e,vendor v,ledger_group lg,ledger_type lt WHERE e.vendor_id = v.vendor_id AND e.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND e.expense_date>='$fdate' AND e.expense_date<='$tdate' AND e.status!=2 AND lt.expense_type = 'In Direct' $slocid GROUP BY lg.ledger_group_id) UNION (SELECT l.ledger_date,l.ledger_no as invoice_no,sum(l.amount) as tamt,0 as other_charges,0 as sgst,0 as cgst,0 as igst,l.amount as total_amount,0 as tds_value,l.payee_name as vendor_name ,'' as gst_no,lg.ledger_group,lg.ledger_group_id FROM ledger l,ledger_sub_group lsg,ledger_group lg,ledger_type lt WHERE l.ledger_sub_group_id = lsg.ledger_sub_group_id AND lsg.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND l.status=0 AND lt.expense_type = 'In Direct' AND l.ledger_date>='$fdate' AND l.ledger_date<='$tdate' $decid GROUP BY lg.ledger_group_id)UNION(SELECT e.staff_expense_date,e.voucher_no as invoice_no,sum(e.net_salary) as tamt,0 as other_charges,0 as sgst,0 as cgst,0 as igst,e.net_salary as total_amount,0 as tds_value,'' as vendor_name ,'' as gst_no,lg.ledger_group,lg.ledger_group_id FROM staff_expense e,ledger_group lg,ledger_type lt WHERE e.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND e.status != 2 AND lt.expense_type = 'In Direct' AND e.staff_expense_date>='$fdate' AND e.staff_expense_date<='$tdate' $slocid GROUP BY lg.ledger_group_id)) led GROUP BY led.ledger_group_id ")->result_array();

$sales_amt = $this->db->query("SELECT sum(a.taxamt) as txamt,sum(a.pcharge) as pc, sum(a.ocharge) as oc FROM ((SELECT sum(i.taxable_amount) as taxamt,sum(i.packing_charges) as pcharge,sum(i.other_charges) as ocharge FROM invoice i,`order` o WHERE i.order_id = o.order_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' $compqry)UNION(SELECT sum(i.taxable_amount) as taxamt,sum(i.packing_charges) as pcharge,sum(i.other_charges) as ocharge FROM invoice i WHERE i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' $dcompqry)) a")->row();

$direct_invoice = $this->db->query("SELECT sum(grand_total) as amt FROM `invoice` WHERE invoice_date>='$fdate' AND invoice_date<='$tdate' AND customer_id!=0 $cid")->row();

$direct_expense = $this->db->query("SELECT lg.ledger_group_id,lsg.ledger_sub_group,sum(l.amount) as amt FROM `ledger` l,ledger_sub_group lsg,ledger_group lg,ledger_type lt WHERE l.ledger_sub_group_id = lsg.ledger_sub_group_id AND lsg.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND l.ledger_date>='$fdate' AND l.ledger_date<='$tdate' $decid AND l.status=0 AND lt.expense_type = 'Direct' GROUP BY l.ledger_sub_group_id")->result_array();

$direct_expense_list = $this->db->query("SELECT s.ledger_group_id,s.ledger_group, sum(s.amt) as amount FROM ((SELECT lg.ledger_group_id, lg.ledger_group,sum(e.taxable_value) as amt FROM expense e,ledger_group lg,ledger_type lt WHERE e.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND e.status != 2 AND lt.expense_type = 'Direct' AND e.expense_date>='$fdate' AND e.expense_date<='$tdate' $slocid GROUP BY lg.ledger_group_id)UNION(SELECT lg.ledger_group_id, lg.ledger_group,sum(e.net_salary) as amt FROM staff_expense e,ledger_group lg,ledger_type lt WHERE e.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND e.status != 2 AND lt.expense_type = 'Direct' AND e.staff_expense_date>='$fdate' AND e.staff_expense_date<='$tdate' $slocid GROUP BY lg.ledger_group_id)) s GROUP BY s.ledger_group_id ")->result_array();
        }

$direct_expense_ledger = $this->Profitloss_model->get_direct_expense_ledger();
      $indirect_expense_ledger = $this->Profitloss_model->get_indirect_expense_ledger();
      $direct_expense_group = $this->Profitloss_model->get_direct_expense_voucher_group();
      $in_direct_expense_group = $this->Profitloss_model->get_indirect_expense_voucher_group();

$direct_income_ledger = $this->Profitloss_model->get_direct_income_ledger_list();
      $indirect_income_ledger = $this->Profitloss_model->get_indirect_income_ledger_list();

$g_settings = general_setting_details();

if($company_id!='-')
      {
        $locationlist = $this->Invoice_model->get_location_by_id($company_id);
        $locationName = $locationlist->location_name;
        $reptitle = 'Trial Balance For '.$locationName.' From '.date_format(date_create($fdate),$g_settings->date_format).' To '.date_format(date_create($tdate),$g_settings->date_format);
      }
      else
      {
        $locationName = '';
        $reptitle = 'Trial Balance for All Company From '.date_format(date_create($fdate),$g_settings->date_format).' To '.date_format(date_create($tdate),$g_settings->date_format);
      }

$objPHPExcel = new PHPExcel();
    $activeSheet = $objPHPExcel->getActiveSheet();
    $styleArray = array(
        'font' => array(
            'bold' => true,
            'color' => array('rgb' => '000000')
        ),
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )
    );

$styleArrayNormal = array(
        'font' => array(
            'color' => array('rgb' => '000000')
        ),
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )
    );

$objPHPExcel->getActiveSheet()->mergeCells('A1:C1');
    $objPHPExcel->getActiveSheet()->setCellValue('A1', $reptitle);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);

$objPHPExcel->getActiveSheet()->setCellValue('A3', 'Particulars');
    $objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleArray);

$objPHPExcel->getActiveSheet()->mergeCells('B3:C3');
    $objPHPExcel->getActiveSheet()->setCellValue('B3', 'Closing Balance');
    $objPHPExcel->getActiveSheet()->getStyle('B3')->applyFromArray($styleArray);

$objPHPExcel->getActiveSheet()->setCellValue('B4', 'Debit');
    $objPHPExcel->getActiveSheet()->getStyle('B4')->applyFromArray($styleArrayNormal);

$objPHPExcel->getActiveSheet()->setCellValue('C4', 'Credit');
    $objPHPExcel->getActiveSheet()->getStyle('C4')->applyFromArray($styleArrayNormal);

$acdeb=0;
    $accre=0;
    $var = 'A';$j=5; $gvar = 'B';$svar = 'C';$ltot=0;foreach($liabilities_list as $llist){

$objPHPExcel->getActiveSheet()->setCellValue($var.$j, $llist['account_category']);
                                $objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArray);
                                $objPHPExcel->getActiveSheet()->setCellValue($gvar.$j, '');
                                $objPHPExcel->getActiveSheet()->getStyle($gvar.$j)->applyFromArray($styleArray);
                                $objPHPExcel->getActiveSheet()->setCellValue($svar.$j, '');
                                $objPHPExcel->getActiveSheet()->getStyle($svar.$j)->applyFromArray($styleArray);
$j++;
                                $aattri = explode(',', $llist['account_attributes']);
                                $aattriid = explode(',', $llist['account_attributes_id']);
                                $bs_status = explode(',', $llist['bs_status']);
                                $lhcre=$lhdeb=0;for($i=0;$i<count($aattri);$i++)
                                {

$totcre=0;
                                       $totdeb=0;
                                
                                  if($bs_status[$i]==0)
                                  {
                                      if($company_id=='-')
                                        $ledger_list = $this->Balancesheet_model->get_ledger_list_by_account_attributes_id($aattriid[$i]);
                                      else
                                        $ledger_list = $this->Balancesheet_model->get_ledger_list_by_account_attributes_company_id($aattriid[$i],$company_id);
                                      if(count($ledger_list)>0)
                                      {
                                        $ledgerAmt = 0;
                                        foreach($ledger_list as $u_list)
                                        {
                                          $ledgcre=0;
                                          $ledgdeb=0;
                                          if($u_list['crdr']==1)
                                          {
                                            $opdeb = abs($u_list['opening_balance']);
                                            $opcre = 0;
                                          }
                                          else
                                          {
                                            $opdeb = 0;
                                            $opcre = abs($u_list['opening_balance']);
                                          }

if($company_id=='-')
                                          {
                                            if($tdate=='')
                                            {
                                              
                                                    $current_ledger_details_view_list = $this->Ledger_model->get_ledger_details_view_by_custom_date($fdate,$fdate,$u_list['ledger_list_id']);

if(count($current_ledger_details_view_list>0))
                                              {
                                                foreach($current_ledger_details_view_list as $opdet)
                                                {
                                                  if($opdet['payment_type']=='credit')
                                                  {
                                                    if($opdet['user_type'] == 'Sales')
                                                    {
                                                      $opcre+= 0;
                                                      $opdeb+= $opdet['amount'];
                                                    }
                                                    else if($opdet['user_type']=='Invoice')
                                                    {
                                                      $opcre+= 0;
                                                      $opdeb+= $opdet['amount'];
                                                    }
                                                    else if($opdet['user_type']=='transfer')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='withdraw')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='deposit')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='cash transfer')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Purchase Tax' || $opdet['user_type']=='Expense Tax')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'CN INV' || $opdet['user_type'] == 'RCN INV')  
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'CN OTH')                  
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'RCN OTH')                 
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    } 
                                                    else if($opdet['user_type']=='STF Deduct')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }   
                                                    else if($opdet['user_type']=='Fixed Asset')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Journal')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    } 
                                                    else if($opdet['user_type']=='Debit Receipt for P&L')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }   
                                                    else
                                                    {
                                                      $opcre+= 0;
                                                      $opdeb+= $opdet['amount'];
                                                    }
                                                  }
                                                  else
                                                  {
                                                    if($opdet['expense_type'] == 'vendor payment')
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                    else if($opdet['expense_type'] == 'expense')
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                    else if($opdet['expense_type'] == 'staff expense')
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                    else if($opdet['user_type']=='deposit')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='withdraw')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='transfer')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='cash transfer')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Voucher')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Invoice Tax')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='TDS')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='STF EXP')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='STF Allow')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'DN PUR' || $opdet['user_type'] == 'PDN PUR')  
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'DN OTH')                  
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'PDN OTH')                 
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }   
                                                    else if($opdet['user_type']=='Journal')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    } 
                                                    else if($opdet['user_type']=='Credit Receipt for P&L')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    } 
                                                    else
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                  }
                                                 }
                                              }

$opbal = $opdeb-$opcre;

}
                                            else
                                            {

if($fdate>'2019-04-01')
                                                    $previous_ledger_details_view_list = $this->Ledger_model->get_ledger_details_view_by_custom_date($psdate,$pdate,$u_list['ledger_list_id']);

if(count($previous_ledger_details_view_list>0))
                                              {
                                                foreach($previous_ledger_details_view_list as $opdet)
                                                {
                                                  if($opdet['payment_type']=='credit')
                                                  {
                                                    if($opdet['user_type'] == 'Sales')
                                                    {
                                                      $opcre+= 0;
                                                      $opdeb+= $opdet['amount'];
                                                    }
                                                    else if($opdet['user_type']=='Invoice')
                                                    {
                                                      $opcre+= 0;
                                                      $opdeb+= $opdet['amount'];
                                                    }
                                                    else if($opdet['user_type']=='transfer')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='withdraw')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='deposit')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='cash transfer')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Purchase Tax' || $opdet['user_type']=='Expense Tax')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'CN INV' || $opdet['user_type'] == 'RCN INV')  
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'CN OTH')                  
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'RCN OTH')                 
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    } 
                                                    else if($opdet['user_type']=='STF Deduct')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }   
                                                    else if($opdet['user_type']=='Fixed Asset')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Journal')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    } 
                                                    else if($opdet['user_type']=='Debit Receipt for P&L')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }   
                                                    else
                                                    {
                                                      $opcre+= 0;
                                                      $opdeb+= $opdet['amount'];
                                                    }
                                                  }
                                                  else
                                                  {
                                                    if($opdet['expense_type'] == 'vendor payment')
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                    else if($opdet['expense_type'] == 'expense')
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                    else if($opdet['expense_type'] == 'staff expense')
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                    else if($opdet['user_type']=='deposit')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='withdraw')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='transfer')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='cash transfer')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Voucher')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Invoice Tax')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='TDS')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='STF EXP')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='STF Allow')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'DN PUR' || $opdet['user_type'] == 'PDN PUR')  
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'DN OTH')                  
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'PDN OTH')                 
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }   
                                                    else if($opdet['user_type']=='Journal')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }   
                                                    else if($opdet['user_type']=='Credit Receipt for P&L')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                  }
                                                 }
                                              }

$current_ledger_details_view_list = $this->Ledger_model->get_ledger_details_view_by_custom_date($fdate,$tdate,$u_list['ledger_list_id']);

if(count($current_ledger_details_view_list>0))
                                              {
                                                foreach($current_ledger_details_view_list as $opdet)
                                                {
                                                  if($opdet['payment_type']=='credit')
                                                  {
                                                    if($opdet['user_type'] == 'Sales')
                                                    {
                                                      $opcre+= 0;
                                                      $opdeb+= $opdet['amount'];
                                                    }
                                                    else if($opdet['user_type']=='Invoice')
                                                    {
                                                      $opcre+= 0;
                                                      $opdeb+= $opdet['amount'];
                                                    }
                                                    else if($opdet['user_type']=='transfer')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='withdraw')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='deposit')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='cash transfer')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Purchase Tax' || $opdet['user_type']=='Expense Tax')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'CN INV' || $opdet['user_type'] == 'RCN INV')  
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'CN OTH')                  
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'RCN OTH')                 
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    } 
                                                    else if($opdet['user_type']=='STF Deduct')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }   
                                                    else if($opdet['user_type']=='Fixed Asset')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Journal')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    } 
                                                    else if($opdet['user_type']=='Debit Receipt for P&L')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }   
                                                    else
                                                    {
                                                      $opcre+= 0;
                                                      $opdeb+= $opdet['amount'];
                                                    }
                                                  }
                                                  else
                                                  {
                                                    if($opdet['expense_type'] == 'vendor payment')
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                    else if($opdet['expense_type'] == 'expense')
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                    else if($opdet['expense_type'] == 'staff expense')
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                    else if($opdet['user_type']=='deposit')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='withdraw')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='transfer')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='cash transfer')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Voucher')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Invoice Tax')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='TDS')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='STF EXP')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='STF Allow')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'DN PUR' || $opdet['user_type'] == 'PDN PUR')  
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'DN OTH')                  
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'PDN OTH')                 
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }   
                                                    else if($opdet['user_type']=='Journal')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Credit Receipt for P&L')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                  }
                                                 }
                                              }

$opbal = $opdeb-$opcre;

}
                                          }
                                          else
                                          {
                                            if($tdate=='')
                                            {
                                              
                                                    $current_ledger_details_view_list = $this->Balancesheet_model->get_ledger_details_view_by_custom_date_cid($fdate,$fdate,$u_list['ledger_list_id'],$company_id);

if(count($current_ledger_details_view_list>0))
                                              {
                                                foreach($current_ledger_details_view_list as $opdet)
                                                {
                                                  if($opdet['payment_type']=='credit')
                                                  {
                                                    if($opdet['user_type'] == 'Sales')
                                                    {
                                                      $opcre+= 0;
                                                      $opdeb+= $opdet['amount'];
                                                    }
                                                    else if($opdet['user_type']=='Invoice')
                                                    {
                                                      $opcre+= 0;
                                                      $opdeb+= $opdet['amount'];
                                                    }
                                                    else if($opdet['user_type']=='transfer')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='withdraw')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='deposit')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='cash transfer')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Purchase Tax' || $opdet['user_type']=='Expense Tax')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'CN INV' || $opdet['user_type'] == 'RCN INV')  
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'CN OTH')                  
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'RCN OTH')                 
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    } 
                                                    else if($opdet['user_type']=='STF Deduct')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }   
                                                    else if($opdet['user_type']=='Fixed Asset')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Journal')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    } 
                                                    else if($opdet['user_type']=='Debit Receipt for P&L')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }   
                                                    else
                                                    {
                                                      $opcre+= 0;
                                                      $opdeb+= $opdet['amount'];
                                                    }
                                                  }
                                                  else
                                                  {
                                                    if($opdet['expense_type'] == 'vendor payment')
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                    else if($opdet['expense_type'] == 'expense')
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                    else if($opdet['expense_type'] == 'staff expense')
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                    else if($opdet['user_type']=='deposit')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='withdraw')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='transfer')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='cash transfer')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Voucher')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Invoice Tax')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='TDS')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='STF EXP')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='STF Allow')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'DN PUR' || $opdet['user_type'] == 'PDN PUR')  
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'DN OTH')                  
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'PDN OTH')                 
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }   
                                                    else if($opdet['user_type']=='Journal')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Credit Receipt for P&L')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                  }
                                                 }
                                              }

$opbal = $opdeb-$opcre;

}
                                            else
                                            {

if($fdate>'2019-04-01')
                                                    $previous_ledger_details_view_list = $this->Balancesheet_model->get_ledger_details_view_by_custom_date_cid($psdate,$pdate,$u_list['ledger_list_id'],$company_id);

if(count($previous_ledger_details_view_list>0))
                                              {
                                                foreach($previous_ledger_details_view_list as $opdet)
                                                {
                                                  if($opdet['payment_type']=='credit')
                                                  {
                                                    if($opdet['user_type'] == 'Sales')
                                                    {
                                                      $opcre+= 0;
                                                      $opdeb+= $opdet['amount'];
                                                    }
                                                    else if($opdet['user_type']=='Invoice')
                                                    {
                                                      $opcre+= 0;
                                                      $opdeb+= $opdet['amount'];
                                                    }
                                                    else if($opdet['user_type']=='transfer')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='withdraw')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='deposit')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='cash transfer')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Purchase Tax' || $opdet['user_type']=='Expense Tax')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'CN INV' || $opdet['user_type'] == 'RCN INV')  
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'CN OTH')                  
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'RCN OTH')                 
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    } 
                                                    else if($opdet['user_type']=='STF Deduct')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }   
                                                    else if($opdet['user_type']=='Fixed Asset')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Journal')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    } 
                                                    else if($opdet['user_type']=='Debit Receipt for P&L')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }   
                                                    else
                                                    {
                                                      $opcre+= 0;
                                                      $opdeb+= $opdet['amount'];
                                                    }
                                                  }
                                                  else
                                                  {
                                                    if($opdet['expense_type'] == 'vendor payment')
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                    else if($opdet['expense_type'] == 'expense')
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                    else if($opdet['expense_type'] == 'staff expense')
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                    else if($opdet['user_type']=='deposit')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='withdraw')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='transfer')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='cash transfer')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Voucher')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Invoice Tax')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='TDS')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='STF EXP')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='STF Allow')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'DN PUR' || $opdet['user_type'] == 'PDN PUR')  
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'DN OTH')                  
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'PDN OTH')                 
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }   
                                                    else if($opdet['user_type']=='Journal')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Credit Receipt for P&L')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                  }
                                                 }
                                              }

//$opbal = $opdeb-$opcre;

$current_ledger_details_view_list = $this->Balancesheet_model->get_ledger_details_view_by_custom_date_cid($fdate,$tdate,$u_list['ledger_list_id'],$company_id);

if(count($current_ledger_details_view_list>0))
                                              {
                                                foreach($current_ledger_details_view_list as $opdet)
                                                {
                                                  if($opdet['payment_type']=='credit')
                                                  {
                                                    if($opdet['user_type'] == 'Sales')
                                                    {
                                                      $opcre+= 0;
                                                      $opdeb+= $opdet['amount'];
                                                    }
                                                    else if($opdet['user_type']=='Invoice')
                                                    {
                                                      $opcre+= 0;
                                                      $opdeb+= $opdet['amount'];
                                                    }
                                                    else if($opdet['user_type']=='transfer')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='withdraw')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='deposit')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='cash transfer')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Purchase Tax' || $opdet['user_type']=='Expense Tax')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'CN INV' || $opdet['user_type'] == 'RCN INV')  
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'CN OTH')                  
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'RCN OTH')                 
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    } 
                                                    else if($opdet['user_type']=='STF Deduct')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }   
                                                    else if($opdet['user_type']=='Fixed Asset')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Journal')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    } 
                                                    else if($opdet['user_type']=='Debit Receipt for P&L')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }   
                                                    else
                                                    {
                                                      $opcre+= 0;
                                                      $opdeb+= $opdet['amount'];
                                                    }
                                                  }
                                                  else
                                                  {
                                                    if($opdet['expense_type'] == 'vendor payment')
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                    else if($opdet['expense_type'] == 'expense')
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                    else if($opdet['expense_type'] == 'staff expense')
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                    else if($opdet['user_type']=='deposit')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='withdraw')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='transfer')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='cash transfer')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Voucher')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Invoice Tax')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='TDS')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='STF EXP')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type']=='STF Allow')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'DN PUR' || $opdet['user_type'] == 'PDN PUR')  
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'DN OTH')                  
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else if($opdet['user_type'] == 'PDN OTH')                 
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }   
                                                    else if($opdet['user_type']=='Journal')
                                                    {
                                                      $opdeb+= $opdet['amount'];
                                                      $opcre+= 0;
                                                    }
                                                    else if($opdet['user_type']=='Credit Receipt for P&L')
                                                    {
                                                      $opcre+= $opdet['amount'];
                                                      $opdeb+= 0;
                                                    }
                                                    else
                                                    {
                                                      $opdeb+= 0;
                                                      $opcre+= $opdet['amount'];
                                                    }
                                                  }
                                                 }
                                              }

$opbal = $opdeb-$opcre;

}
                                          }
                                        /*$totcre+=$opcre;
                                        $totdeb+=$opdeb;*/
                                        $ledgcre+=$opcre;
                                        $ledgdeb+=$opdeb;

if($opbal>0)
                                          {

$lamt= $opbal;
                                          }
                                          else
                                          {
                                            $lamt= $opbal;
                                          }

$ledgbal = $ledgcre-$ledgdeb;
                                          if($ledgbal>0)
                                          {
                                            $ledtype = 'CR';
                                            $totcre+=$ledgbal;
                                          }
                                          else
                                          {
                                            $ledtype = 'DR';
                                            $totdeb+=abs($ledgbal);
                                          }

if($opbal>0)
                                            $ledgerAmt+= $opbal;
                                          else
                                            $ledgerAmt+= $opbal;
                                        }
                                      $lcheck=1; $ltot+=abs($ledgerAmt);}
                                      else
                                      {
                                        $lopt = '';
                                        $lcheck=0;
                                      } 
                                      $objPHPExcel->getActiveSheet()->setCellValue($var.$j, $aattri[$i]);
                                      $objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArrayNormal);
                                      $objPHPExcel->getActiveSheet()->setCellValue($gvar.$j, number_format($totdeb,2));
                                      $objPHPExcel->getActiveSheet()->getStyle($gvar.$j)->applyFromArray($styleArrayNormal);
                                      $objPHPExcel->getActiveSheet()->setCellValue($svar.$j, number_format($totcre,2));
                                      $objPHPExcel->getActiveSheet()->getStyle($svar.$j)->applyFromArray($styleArrayNormal);

$acdeb+=$totdeb;$accre+=$totcre;$j++;}else if($bs_status[$i]==1){
                                    //$lopt = '<table>';
                                    $sctot = 0;
                                    foreach($sundry_creditors_list as $sc_list){
                                      $vendorDetails = $this->Ledger_model->get_vendor_by_id($sc_list['vendor_id']);
                                      if($vendorDetails->status==0)
                                      {

/*$loc = '';
                                            $iloc = '';
                                            $eloc = '';
                                            $ajloc = '';
                                            $vob = '';
                                            $gloc = '';
                                            $cdnloc = '';*/
                                            $id=$sc_list['vendor_id'];

$opening_balance_details = $this->db->query("(SELECT v.vendor_id as scld_id, v.created_on as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id as user_id,'' as payment_mode,'credit' as payment_type,sum(vob.opening_balance) as payment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,v.created_on,'Opening Balance' as particulars,'' as expense_type,'' as ledger_list,'' as location_name, 0 as op FROM vendor v,vendor_opening_balance vob WHERE v.vendor_id = vob.vendor_id AND vob.crdr=0 AND v.vendor_id=$id $vob)UNION(SELECT v.vendor_id as scld_id, v.created_on as pdate,0 as order_id,0 as invoice_id,0 as customer_id,v.vendor_id as user_id,'' as payment_mode,'debit' as payment_type,sum(vob.opening_balance) as payment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,v.created_on,'Opening Balance' as particulars,'' as expense_type,'' as ledger_list,'' as location_name, 0 as op FROM vendor v, vendor_opening_balance vob WHERE v.vendor_id = vob.vendor_id AND vob.crdr=1 AND v.vendor_id=$id $vob)")->result_array();

if($fdate>'2019-04-01')
                                            {
                                              $previous_ledger_list = $this->db->query("(SELECT p.purchase_id as scld_id, p.purchase_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,p.vendor_id as user_id,'' as payment_mode,'credit' as payment_type,p.total_amount as payment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,p.created_on,'New Purchase' as particulars,'' as expense_type,'' as ledger_list,'' as location_name, 1 as op FROM purchase p, purchase_order po WHERE  p.purchase_order_id = po.purchase_order_id $loc AND p.status=1 AND p.purchase_date>='$psdate' AND p.purchase_date<='$pdate' AND p.vendor_id=$id AND p.status!=2)UNION(SELECT e.expense_id as scld_id, e.expense_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,e.vendor_id as user_id,'' as payment_mode,'credit' as payment_type,e.total_amount as payment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,e.created_on,'Expense' as particulars,'' as expense_type,'' as ledger_list,'' as location_name, 1 as op FROM expense e WHERE e.expense_date>='$psdate' AND e.expense_date<='$pdate' AND e.vendor_id=$id $eloc AND e.status!=2)UNION(SELECT ap.account_payment_id as scld_id, ap.account_payment_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,ap.user_id,ap.payment_mode,ap.payment_type,ap.amount as payment_amount,ap.cheque_no,ap.bank_name,ap.branch_name,ap.created_on,ap.particulars,ap.expense_type,ll.ledger_list,l.location_name, 1 as op FROM account_payment ap,purchase p,vendor v,ledger_list ll,location l WHERE ap.user_id = p.purchase_id AND p.vendor_id = v.vendor_id AND ap.expense_type='vendor payment' AND ll.ledger_list_id = ap.ledger_list_id AND ll.location_id = l.location_id AND ap.account_payment_date>='$psdate' AND ap.account_payment_date<='$pdate' AND v.vendor_id=$id $iloc AND ap.status!=2)UNION(SELECT ap.account_payment_id as scld_id, ap.account_payment_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,ap.user_id,ap.payment_mode,ap.payment_type,ap.amount as payment_amount,ap.cheque_no,ap.bank_name,ap.branch_name,ap.created_on,ap.particulars,ap.expense_type,ll.ledger_list,l.location_name, 1 as op FROM account_payment ap,expense e,vendor v,ledger_list ll,location l WHERE ap.user_id = e.expense_id AND e.vendor_id = v.vendor_id AND ap.expense_type='expense' AND ll.ledger_list_id = ap.ledger_list_id AND ll.location_id = l.location_id AND ap.account_payment_date>='$psdate' AND ap.account_payment_date<='$pdate' AND v.vendor_id=$id $iloc AND ap.status!=2)UNION(SELECT ar.account_journal_id as scld_id, ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,ar.ledger_list2_id as user_id,'' as payment_mode,'credit' as payment_type,ar.amount as payment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,'Journal' as particulars,'' as expense_type,'' as ledger_list,'' as location_name, 1 as op FROM account_journal ar,location l WHERE ar.location_id = l.location_id AND ar.ledger_list2_type='vendor' AND ar.account_journal_date>='$psdate' AND ar.account_journal_date<='$pdate' AND ar.ledger_list2_id=$id $ajloc AND ar.status!=2)UNION(SELECT ar.account_journal_id as scld_id, ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,ar.ledger_list1_id as user_id,'' as payment_mode,'debit' as payment_type,ar.amount as payment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,'Journal' as particulars,'' as expense_type,'' as ledger_list,'' as location_name, 1 as op FROM account_journal ar,location l WHERE ar.location_id = l.location_id AND ar.ledger_list1_type='vendor' AND ar.account_journal_date>='$psdate' AND ar.account_journal_date<='$pdate' AND ar.ledger_list1_id=$id $ajloc AND ar.status!=2)UNION(SELECT ap.account_payment_id as scld_id, ap.account_payment_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,ap.amount as payment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,ap.created_on,particulars,'' as expense_type,'' as ledger_list,'' as location_name, 1 as op FROM account_payment ap, ledger_list ll WHERE ap.ledger_list_id = ll.ledger_list_id AND ap.parti_ledger_list_type = 'vendor' AND ap.account_payment_date>='$psdate' AND ap.account_payment_date<='$pdate' AND ap.parti_ledger_list_id=$id $iloc AND ap.status!=2)UNION(SELECT tp.tds_payable_line_item_id as scld_id, tp.tds_payable_line_item_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,tp.expense_id as user_id,'' as payment_mode,'debit' as payment_type,tp.amount as payment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,tp.created_on,'TDS' as particulars,'' as expense_type,'' as ledger_list,'' as location_name, 1 as op FROM  tds_payable_line_item tp,expense e,vendor v WHERE tp.expense_id = e.expense_id AND e.vendor_id = v.vendor_id AND v.vendor_id=$id AND tp.tds_payable_line_item_date>='$psdate' AND tp.tds_payable_line_item_date<='$pdate' $eloc AND tp.status!=2)   UNION(SELECT cdn.credit_debit_note_id as scld_id, cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,cdn.type_id as user_id,'' as payment_mode,'debit' as payment_type,cdn.amount as payment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'DN PUR' as particulars,'' as expense_type,'' as ledger_list,'' as location_name, 1 as op FROM  credit_debit_note cdn,purchase p,vendor v WHERE cdn.type_id = p.purchase_id AND p.vendor_id = v.vendor_id AND cdn.credit_debit = 'Debit' AND v.vendor_id=$id AND cdn.type_id !=0 AND cdn.credit_debit_note_date>='$psdate' AND cdn.credit_debit_note_date<='$pdate' $cdnloc AND cdn.status!=2)UNION(SELECT cdn.credit_debit_note_id as scld_id, cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,cdn.type_id as user_id,'' as payment_mode,'credit' as payment_type,cdn.amount as payment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'RDN PUR' as particulars,'' as expense_type,'' as ledger_list,'' as location_name, 1 as op FROM  credit_debit_note cdn,purchase p,vendor v WHERE cdn.type_id = p.purchase_id AND p.vendor_id = v.vendor_id AND cdn.credit_debit = 'Receipt' AND v.vendor_id=$id AND cdn.type_id!=0 AND cdn.credit_debit_note_date>='$psdate' AND cdn.credit_debit_note_date<='$pdate' $cdnloc AND cdn.status!=2)UNION(SELECT cdn.credit_debit_note_id as scld_id, cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,cdn.vendor_id as user_id,'' as payment_mode,'debit' as payment_type,cdn.amount as payment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'DN OTH' as particulars,'' as expense_type,'' as ledger_list,'' as location_name, 1 as op FROM  credit_debit_note cdn,vendor v WHERE cdn.vendor_id = v.vendor_id AND cdn.credit_debit = 'Debit' AND v.vendor_id=$id AND cdn.type_id=0 AND cdn.credit_debit_note_date>='$psdate' AND cdn.credit_debit_note_date<='$pdate' $cdnloc AND cdn.status!=2)UNION(SELECT cdn.credit_debit_note_id as scld_id, cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,0 as customer_id,cdn.vendor_id as user_id,'' as payment_mode,'credit' as payment_type,cdn.amount as payment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'RDN OTH' as particulars,'' as expense_type,'' as ledger_list,'' as location_name, 1 as op FROM  credit_debit_note cdn,vendor v WHERE cdn.vendor_id = v.vendor_id AND cdn.credit_debit = 'Receipt' AND v.vendor_id=$id AND cdn.type_id=0 AND cdn.credit_debit_note_date>='$psdate' AND cdn.credit_debit_note_date<='$pdate' $cdnloc AND cdn.status!=2)ORDER BY op ASC, pdate ASC")->result_array();
                                            }

$opcre=$opdeb=0;
                                        foreach($opening_balance_details as $opdet)
                                        {
                                          if($opdet['payment_type']=='credit')
                                          {
                                            $opdeb+= 0;
                                            $opcre+= $opdet['payment_amount']!=''?$opdet['payment_amount']:0;
                                          }
                                          else
                                          {
                                            $opcre+= 0;
                                            $opdeb+= $opdet['payment_amount']!=''?$opdet['payment_amount']:0;
                                          }
                                        }

if(count($previous_ledger_list>0))
                                        {
                                          foreach($previous_ledger_list as $opdet)
                                          {
                                            if($opdet['payment_type']=='credit')
                                            {
                                              $opdeb+= 0;
                                              $opcre+= $opdet['payment_amount'];
                                            }
                                            else
                                            {
                                              $opcre+= 0;
                                              $opdeb+= $opdet['payment_amount'];
                                            }
                                           }
                                        }

$bal = ($opcre+$sc_list['cpamt'])-($opdeb+$sc_list['dpamt']);
                                      /*$totcre+=$opcre+$sc_list['cpamt'];
                                      $totdeb+=$opdeb+$sc_list['dpamt'];*/

$ledgcre=$opcre+$sc_list['cpamt'];
                                      $ledgdeb=$opdeb+$sc_list['dpamt'];
                                          
                                      $ledgbal = $ledgcre-$ledgdeb;
                                      if($ledgbal>0)
                                      {
                                        $ledtype = 'CR';
                                        $totcre+=$ledgbal;
                                      }
                                      else
                                      {
                                        $ledtype = 'DR';
                                        $totdeb+=abs($ledgbal);
                                      }

$sctot+=$bal;}}
                                    
                                    
                                    $objPHPExcel->getActiveSheet()->setCellValue($var.$j, $aattri[$i]);
                                    $objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArrayNormal);
                                    $objPHPExcel->getActiveSheet()->setCellValue($gvar.$j, number_format($totdeb,2));
                                    $objPHPExcel->getActiveSheet()->getStyle($gvar.$j)->applyFromArray($styleArrayNormal);
                                    $objPHPExcel->getActiveSheet()->setCellValue($svar.$j, number_format($totcre,2));
                                    $objPHPExcel->getActiveSheet()->getStyle($svar.$j)->applyFromArray($styleArrayNormal);
                                    $j++;
                                  $ltot+=$sctot;$acdeb+=$totdeb;$accre+=$totcre;}
                                 $lhcre+=$totcre;$lhdeb+=$totdeb;}

$objPHPExcel->getActiveSheet()->setCellValue($var.$j, 'Total');
                                $objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArray);
                                $objPHPExcel->getActiveSheet()->setCellValue($gvar.$j, number_format($lhdeb,2));
                                $objPHPExcel->getActiveSheet()->getStyle($gvar.$j)->applyFromArray($styleArray);
                                $objPHPExcel->getActiveSheet()->setCellValue($svar.$j, number_format($lhcre,2));
                                $objPHPExcel->getActiveSheet()->getStyle($svar.$j)->applyFromArray($styleArray);
                              $j++;}

$atot=0;foreach($assets_list as $llist){

$aattri = explode(',', $llist['account_attributes']);
    $aattriid = explode(',', $llist['account_attributes_id']);
    $bs_status = explode(',', $llist['bs_status']);
  $ahcre=$ahdeb=0;for($i=0;$i<count($aattri);$i++)
  {

$totcre=0;
         $totdeb=0;

if($bs_status[$i]==0)
    {

if($company_id=='-')
          $ledger_list = $this->Balancesheet_model->get_ledger_list_by_account_attributes_id($aattriid[$i]);
        else
          $ledger_list = $this->Balancesheet_model->get_ledger_list_by_account_attributes_company_id($aattriid[$i],$company_id);
         
        if(count($ledger_list)>0)
        {
          $ledgerAssAmt = 0;
          //$lopt = '<table>';
          foreach($ledger_list as $u_list)
          {
                                          $ledgcre=0;
                                          $ledgdeb=0;
            if($u_list['crdr']==1)
            {
              $opdeb = abs($u_list['opening_balance']);
              $opcre = 0;
            }
            else
            {
              $opdeb = 0;
              $opcre = abs($u_list['opening_balance']);
            }

$opbal = $opdeb-$opcre;
              }
              else
              {

if($fdate>'2019-04-01')
                    $previous_ledger_details_view_list = $this->Ledger_model->get_ledger_details_view_by_custom_date($psdate,$pdate,$u_list['ledger_list_id']);

$opbal = $opdeb-$opcre;
              }
            }
            else
            {
              if($tdate=='')
              {
                
                    $current_ledger_details_view_list = $this->Balancesheet_model->get_ledger_details_view_by_custom_date_cid($fdate,$fdate,$u_list['ledger_list_id'],$company_id);

$opbal = $opdeb-$opcre;
              }
              else
              {

if($fdate>'2019-04-01')
                    $previous_ledger_details_view_list = $this->Balancesheet_model->get_ledger_details_view_by_custom_date_cid($psdate,$pdate,$u_list['ledger_list_id'],$company_id);

$opbal = $opdeb-$opcre;
              }
            }
          /*$totcre+=$opcre;
          $totdeb+=$opdeb;*/
          $ledgcre+=$opcre;
          $ledgdeb+=$opdeb;
            if($opbal>0)
              $lamt = $opbal;
            else
              $lamt = $opbal;

$ledgbal = $ledgcre-$ledgdeb;
          if($ledgbal>0)
          {
            $ledtype = 'CR';
            $ledgcre+=$ledgbal;
          }
          else
          {
            $ledtype = 'DR';
            $totdeb+=abs($ledgbal);
          }

if($opbal>0)
              $ledgerAssAmt+= $opbal;
            else
              $ledgerAssAmt+= $opbal;
          }

$acheck=1; $atot+=$ledgerAssAmt;}
        else
        {
          $lopt = '';
          $acheck=0;
        }

$objPHPExcel->getActiveSheet()->setCellValue($var.$j, $aattri[$i]);
                                      $objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArrayNormal);
                                      $objPHPExcel->getActiveSheet()->setCellValue($gvar.$j, number_format($totdeb,2));
                                      $objPHPExcel->getActiveSheet()->getStyle($gvar.$j)->applyFromArray($styleArrayNormal);
                                      $objPHPExcel->getActiveSheet()->setCellValue($svar.$j, number_format($totcre,2));
                                      $objPHPExcel->getActiveSheet()->getStyle($svar.$j)->applyFromArray($styleArrayNormal);
     $j++;}else if($bs_status[$i]==1){
      //$lopt = '<table>';
      $sdtot = 0;
      foreach($sundry_debtors_list as $sd_list){
        $customerDetails = $this->Ledger_model->get_customer_view_by_id($sd_list['customer_id']);
        $cusDetails = $this->Ledger_model->get_customer_contact_details($sd_list['customer_id']);

/*$loc = '';
            $iloc = '';        
            $ajloc = '';
            $cob = '';
            $llloc = '';
            $cdnloc = '';*/
            $id = $sd_list['customer_id'];

$opening_balance_details = $this->db->query("(SELECT c.customer_id as scld_id,c.created_on as pdate,'' as order_id,'' as invoice_id,c.customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,sum(cob.opening_balance) as payment_amount,0 as cheque_no,0 as bank_name,0 as branch_name,c.created_on,'Opening Balance' as particulars,'' as ledger_list,'' as location_name, 0 as op FROM `customer` c,customer_opening_balance cob WHERE c.customer_id = cob.customer_id AND cob.crdr=0 AND c.customer_id=$id $cob)UNION(SELECT c.customer_id as scld_id,c.created_on as pdate,'' as order_id,'' as invoice_id,c.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,sum(cob.opening_balance) as payment_amount,0 as cheque_no,0 as bank_name,0 as branch_name,c.created_on,'Opening Balance' as particulars,'' as ledger_list,'' as location_name, 0 as op FROM `customer` c,customer_opening_balance cob WHERE c.customer_id = cob.customer_id AND cob.crdr=1 AND c.customer_id=$id $cob)")->result_array();

if($fdate>'2019-04-01')
              {
                $previous_ledger_list = $this->db->query("(SELECT i.invoice_id as scld_id,i.invoice_date as pdate,i.order_id,i.invoice_id,i.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,i.grand_total as payment_amount,0 as cheque_no,0 as bank_name,0 as branch_name,i.created_on,'New Invoice' as particulars,'' as ledger_list,'' as location_name, 1 as op FROM `invoice` i WHERE i.order_id=0 AND i.invoice_date>='$psdate' AND i.invoice_date<='$pdate' AND i.customer_id=$id $invloc AND i.status!=20)UNION(SELECT i.invoice_id as scld_id,i.invoice_date as pdate,i.order_id,i.invoice_id,o.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,i.grand_total as payment_amount,0 as cheque_no,0 as bank_name,0 as branch_name,i.created_on,'Order Invoice' as particulars,'' as ledger_list,'' as location_name, 1 as op FROM invoice i,`order` o WHERE o.order_id = i.order_id AND i.invoice_date>='$psdate' AND i.invoice_date<='$pdate' AND o.customer_id=$id $oloc AND i.status!=20)UNION(SELECT ar.account_receipt_id as scld_id,ar.account_receipt_date as pdate,ar.user_id as order_id,0 as invoice_id,o.customer_id,0 as user_id,ar.payment_mode,ar.payment_type,ar.amount as payment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars,ll.ledger_list,l.location_name, 1 as op FROM account_receipt ar, `order` o,ledger_list ll,location l WHERE ar.user_id = o.order_id AND ar.user_type='Sales' AND ar.ledger_list_id = ll.ledger_list_id AND ll.location_id = l.location_id AND ar.account_receipt_date>='$psdate' AND ar.account_receipt_date<='$pdate' AND o.customer_id=$id AND ar.status!=2 $oloc)UNION(SELECT ar.account_receipt_id as scld_id,ar.account_receipt_date as pdate,ar.user_id as order_id,0 as invoice_id,o.customer_id,0 as user_id,ar.payment_mode,ar.payment_type,ar.amount as payment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars,ll.ledger_list,l.location_name, 1 as op FROM account_receipt ar, `order` o, invoice i, ledger_list ll, location l WHERE ar.user_id = i.invoice_id AND i.order_id = o.order_id AND ar.user_type='Invoice' AND ar.ledger_list_id = ll.ledger_list_id AND ll.location_id = l.location_id AND ar.account_receipt_date>='$psdate' AND ar.account_receipt_date<='$pdate' AND o.customer_id=$id AND ar.status!=2 $oloc)UNION(SELECT ar.account_receipt_id as scld_id,ar.account_receipt_date as pdate,0 as order_id,ar.user_id as invoice_id,i.customer_id,0 as user_id,ar.payment_mode,ar.payment_type,ar.amount as payment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars,ll.ledger_list,l.location_name, 1 as op FROM account_receipt ar, `invoice` i, ledger_list ll,location l WHERE ar.user_id = i.invoice_id AND ar.user_type='Invoice' AND ar.ledger_list_id = ll.ledger_list_id AND ll.location_id = l.location_id AND ar.account_receipt_date>='$psdate' AND ar.account_receipt_date<='$pdate' AND i.customer_id=$id AND ar.status!=2 $iloc)UNION(SELECT ar.account_journal_id as scld_id,ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,ar.ledger_list2_id as customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,ar.amount as payment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,'Journal' as particulars,'' as ledger_list,'' as location_name, 1 as op FROM account_journal ar,location l WHERE ar.location_id = l.location_id AND ar.ledger_list2_type='customer' AND ar.account_journal_date>='$psdate' AND ar.account_journal_date<='$pdate' AND ar.ledger_list2_id=$id AND ar.status!=2 $ajloc)UNION(SELECT ar.account_journal_id as scld_id,ar.account_journal_date as pdate,0 as order_id,0 as invoice_id,ar.ledger_list1_id as customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,ar.amount as payment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,'Journal' as particulars,'' as ledger_list,'' as location_name, 1 as op FROM account_journal ar,location l WHERE ar.location_id = l.location_id AND ar.ledger_list1_type='customer' AND ar.account_journal_date>='$psdate' AND ar.account_journal_date<='$pdate' AND ar.ledger_list1_id=$id AND ar.status!=2 $ajloc)UNION(SELECT ar.account_receipt_id as scld_id,ar.account_receipt_date as pdate,0 as order_id,0 as invoice_id,ar.parti_ledger_list_id as customer_id,0 as user_id,ar.payment_mode,ar.payment_type,ar.amount as payment_amount,ar.cheque_no,ar.bank_name,ar.branch_name,ar.created_on,ar.remarks as particulars,'' as ledger_list,'' as location_name, 1 as op FROM account_receipt ar, ledger_list ll WHERE ar.ledger_list_id = ll.ledger_list_id AND ar.parti_ledger_list_type = 'customer' AND ar.account_receipt_date>='$psdate' AND ar.account_receipt_date<='$pdate' AND ar.parti_ledger_list_id=$id AND ar.status!=2 $llloc)UNION(SELECT cdn.credit_debit_note_id as scld_id,cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,cdn.customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,cdn.amount as payment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'CN OTH' as particulars,'' as ledger_list,'' as location_name, 1 as op FROM credit_debit_note cdn, ledger_list ll WHERE cdn.ledger_list_id = ll.ledger_list_id AND cdn.credit_debit='Credit' AND cdn.type_id = 0 AND cdn.customer_id=$id AND cdn.credit_debit_note_date>='$psdate' AND cdn.credit_debit_note_date<='$pdate' AND cdn.status!=2 $cdnloc)UNION(SELECT cdn.credit_debit_note_id as scld_id,cdn.credit_debit_note_date as pdate,0 as order_id,0 as invoice_id,cdn.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,cdn.amount as payment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'PCN OTH' as particulars,'' as ledger_list,'' as location_name, 1 as op FROM credit_debit_note cdn, ledger_list ll WHERE cdn.ledger_list_id = ll.ledger_list_id AND cdn.credit_debit='Payment' AND cdn.type_id = 0 AND cdn.customer_id=$id AND cdn.credit_debit_note_date>='$psdate' AND cdn.credit_debit_note_date<='$pdate' AND cdn.status!=2 $cdnloc)UNION(SELECT cdn.credit_debit_note_id as scld_id,cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,i.customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,cdn.amount as payment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'CN INV' as particulars,'' as ledger_list,'' as location_name, 1 as op FROM credit_debit_note cdn,invoice i,customer c WHERE cdn.type_id = i.invoice_id AND i.customer_id = c.customer_id AND i.customer_id !=0 AND cdn.credit_debit='Credit' AND cdn.type_id != 0 AND i.customer_id=$id AND cdn.credit_debit_note_date>='$psdate' AND cdn.credit_debit_note_date<='$pdate' AND cdn.status!=2 $cdnloc)UNION(SELECT cdn.credit_debit_note_id as scld_id,cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,i.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,cdn.amount as payment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'PCN INV' as particulars,'' as ledger_list,'' as location_name, 1 as op FROM credit_debit_note cdn,invoice i,customer c WHERE cdn.type_id = i.invoice_id AND i.customer_id = c.customer_id AND i.customer_id !=0 AND cdn.credit_debit='Payment' AND cdn.type_id != 0 AND i.customer_id=$id AND cdn.credit_debit_note_date>='$psdate' AND cdn.credit_debit_note_date<='$pdate' AND cdn.status!=2 $cdnloc)UNION(SELECT cdn.credit_debit_note_id as scld_id,cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,o.customer_id,0 as user_id,'' as payment_mode,'credit' as payment_type,cdn.amount as payment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'CN INV' as particulars,'' as ledger_list,'' as location_name, 1 as op FROM credit_debit_note cdn,invoice i,`order` o,customer c WHERE cdn.type_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND i.customer_id =0 AND cdn.credit_debit='Credit' AND cdn.type_id != 0 AND o.customer_id=$id AND cdn.credit_debit_note_date>='$psdate' AND cdn.credit_debit_note_date<='$pdate' AND cdn.status!=2 $cdnloc)UNION(SELECT cdn.credit_debit_note_id as scld_id,cdn.credit_debit_note_date as pdate,0 as order_id,cdn.type_id as invoice_id,o.customer_id,0 as user_id,'' as payment_mode,'debit' as payment_type,cdn.amount as payment_amount,'' as cheque_no,'' as bank_name,'' as branch_name,cdn.created_on,'PCN INV' as particulars,'' as ledger_list,'' as location_name, 1 as op FROM credit_debit_note cdn,invoice i,`order` o,customer c WHERE cdn.type_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND i.customer_id =0 AND cdn.credit_debit='Payment' AND cdn.type_id != 0 AND o.customer_id=$id AND cdn.credit_debit_note_date>='$psdate' AND cdn.credit_debit_note_date<='$pdate' AND cdn.status!=2 $cdnloc)ORDER BY op ASC, pdate ASC")->result_array();
              }

$opdeb=$opcre=0;
              foreach($opening_balance_details as $opdet)
          {
            if($opdet['payment_type']=='credit')
            {
              $opdeb+= 0;
              $opcre+= $opdet['payment_amount']!=''?$opdet['payment_amount']:0;
            }
            else
            {
              $opcre+= 0;
              $opdeb+= $opdet['payment_amount']!=''?$opdet['payment_amount']:0;
            }
          }

$bal = ($opdeb+$sd_list['dpamt'])-($opcre+$sd_list['cpamt']);

/*$totcre+=$opcre+$sd_list['cpamt'];
        $totdeb+=$opdeb+$sd_list['dpamt'];*/
        $ledgcre=$opcre+$sd_list['cpamt'];
        $ledgdeb=$opdeb+$sd_list['dpamt'];

$ledgbal = $ledgcre-$ledgdeb;
        if($ledgbal>0)
        {
          $ledtype = 'CR';
          $totcre+=$ledgbal;
        }
        else
        {
          $ledtype = 'DR';
          $totdeb+=abs($ledgbal);
        }
      $sdtot+=$bal;}
      $objPHPExcel->getActiveSheet()->setCellValue($var.$j, $aattri[$i]);
            $objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArrayNormal);
            $objPHPExcel->getActiveSheet()->setCellValue($gvar.$j, number_format($totdeb,2));
            $objPHPExcel->getActiveSheet()->getStyle($gvar.$j)->applyFromArray($styleArrayNormal);
            $objPHPExcel->getActiveSheet()->setCellValue($svar.$j, number_format($totcre,2));
            $objPHPExcel->getActiveSheet()->getStyle($svar.$j)->applyFromArray($styleArrayNormal);
            $j++;

$atot+=$sdtot;}
   $acdeb+=$totdeb;$accre+=$totcre;$ahcre+=$totcre;$ahdeb+=$totdeb;}

$objPHPExcel->getActiveSheet()->setCellValue($var.$j, 'Total');
        $objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArray);
        $objPHPExcel->getActiveSheet()->setCellValue($gvar.$j, number_format($ahdeb,2));
        $objPHPExcel->getActiveSheet()->getStyle($gvar.$j)->applyFromArray($styleArray);
        $objPHPExcel->getActiveSheet()->setCellValue($svar.$j, number_format($ahcre,2));
        $objPHPExcel->getActiveSheet()->getStyle($svar.$j)->applyFromArray($styleArray);
 $j++;}

$objPHPExcel->getActiveSheet()->setCellValue($var.$j, 'Opening Stock');
$objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->setCellValue($gvar.$j, number_format($ostock,2));
$objPHPExcel->getActiveSheet()->getStyle($gvar.$j)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->setCellValue($svar.$j, number_format(0,2));
$objPHPExcel->getActiveSheet()->getStyle($svar.$j)->applyFromArray($styleArray);
$j++;
$acdeb+=$ostock;

$objPHPExcel->getActiveSheet()->setCellValue($var.$j, 'Purchase');
$objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->setCellValue($gvar.$j, number_format($purchase_amt->amt-$purchase_amt->ocharge,2));
$objPHPExcel->getActiveSheet()->getStyle($gvar.$j)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->setCellValue($svar.$j, number_format(0,2));
$objPHPExcel->getActiveSheet()->getStyle($svar.$j)->applyFromArray($styleArray);
$j++;
$acdeb+=($purchase_amt->amt-$purchase_amt->ocharge);

if(count($direct_expense)>0 || count($direct_expense_ledger)>0 || count($direct_expense_group)>0){
$objPHPExcel->getActiveSheet()->setCellValue($var.$j, 'Direct Expense');
$objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->setCellValue($gvar.$j, '');
$objPHPExcel->getActiveSheet()->getStyle($gvar.$j)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->setCellValue($svar.$j, '');
$objPHPExcel->getActiveSheet()->getStyle($svar.$j)->applyFromArray($styleArray);
$j++;
}

$de=1; $etot=0; $decre=$dedeb=0; foreach($direct_expense_group as $degroup){

$detot=0;
  if($company_id=='-')
  {
    $direct_expense_ledger = $this->Profitloss_model->get_direct_expense_ledger_by_group_id($degroup['ledger_group_id']);
  }
  else
  {
    $direct_expense_ledger = $this->Profitloss_model->get_direct_expense_ledger_by_group_comp_id($degroup['ledger_group_id'],$company_id);
  }

$detot=0;foreach($direct_expense_ledger as $del){
  if($company_id=='-')
  {
    if($fdate=='')
    {
      $ledgdet = $this->Profitloss_model->get_de_ledger_details_view_by_lid_date($del['ledger_list_id'],$curdate);
    }
    else
    {
      $ledgdet = $this->Profitloss_model->get_de_ledger_details_view_by_lid_daterange($del['ledger_list_id'],$fdate,$tdate);
    }
  }
  else
  {
    if($fdate=='')
    {
      $ledgdet = $this->Profitloss_model->get_de_ledger_details_view_by_lid_cid_date($del['ledger_list_id'],$company_id,$curdate);
    }
    else
    {
      $ledgdet = $this->Profitloss_model->get_de_ledger_details_view_by_lid_cid_daterange($del['ledger_list_id'],$company_id,$fdate,$tdate);
    }
  }

$deltot = 0;
  foreach($ledgdet as $dldet)
  {
    if($dldet['payment_type']=='credit')
    {
      $deltot+=$dldet['amount'];
    }
    else
    {
      $deltot-=$dldet['amount'];
    }
  }
  $detot+=$deltot;

}

$deamt = 0;foreach($direct_expense as $llist){
    if($llist['ledger_group_id'] == $degroup['ledger_group_id']){
  $deamt+=$llist['amt'];}}

$dseamt = 0;foreach($direct_expense_list as $llist){
    if($llist['ledger_group_id'] == $degroup['ledger_group_id']){
  $dseamt+=$llist['amount'];}}

$objPHPExcel->getActiveSheet()->setCellValue($var.$j, $degroup['ledger_group']);
$objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArrayNormal);
$objPHPExcel->getActiveSheet()->setCellValue($gvar.$j, number_format(($deamt+$detot+$dseamt),2));
$objPHPExcel->getActiveSheet()->getStyle($gvar.$j)->applyFromArray($styleArrayNormal);
$objPHPExcel->getActiveSheet()->setCellValue($svar.$j, number_format(0,2));
$objPHPExcel->getActiveSheet()->getStyle($svar.$j)->applyFromArray($styleArrayNormal);
$j++;
$de++;$etot+=($deamt+$detot+$dseamt);$acdeb+=($deamt+$detot+$dseamt);$decre+=0;$dedeb+=($deamt+$detot+$dseamt);}

if(count($direct_expense)>0 || count($direct_expense_ledger)>0 || count($direct_expense_group)>0){

$objPHPExcel->getActiveSheet()->setCellValue($var.$j, 'Total');
$objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->setCellValue($gvar.$j, number_format($dedeb,2));
$objPHPExcel->getActiveSheet()->getStyle($gvar.$j)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->setCellValue($svar.$j, number_format($decre,2));
$objPHPExcel->getActiveSheet()->getStyle($svar.$j)->applyFromArray($styleArray);
$j++;
}

if(count($in_direct_expense_group)>0){
$objPHPExcel->getActiveSheet()->setCellValue($var.$j, 'In Direct Expense');
$objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->setCellValue($gvar.$j, '');
$objPHPExcel->getActiveSheet()->getStyle($gvar.$j)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->setCellValue($svar.$j, '');
$objPHPExcel->getActiveSheet()->getStyle($svar.$j)->applyFromArray($styleArray);
$j++;
}

$ide=1; $ietot=0;$idecre=$idedeb=0;foreach($in_direct_expense_group as $idegroup){
 
        if($company_id=='-')
        {
          $indirect_expense_ledger = $this->Profitloss_model->get_indirect_expense_ledger_by_group_id($idegroup['ledger_group_id']);
        }
        else
        {
          $indirect_expense_ledger = $this->Profitloss_model->get_indirect_expense_ledger_by_group_comp_id($idegroup['ledger_group_id'],$company_id);
        }
        $idetot=0;foreach($indirect_expense_ledger as $idel)
        {
          if($company_id=='-')
          {
            if($fdate=='')
            {
              $ledgdet = $this->Profitloss_model->get_de_ledger_details_view_by_lid_date($idel['ledger_list_id'],$curdate);
            }
            else
            {
              $ledgdet = $this->Profitloss_model->get_de_ledger_details_view_by_lid_daterange($idel['ledger_list_id'],$fdate,$tdate);
            }
          }
          else
          {
            if($fdate=='')
            {
              $ledgdet = $this->Profitloss_model->get_de_ledger_details_view_by_lid_cid_date($idel['ledger_list_id'],$company_id,$curdate);
            }
            else
            {
              $ledgdet = $this->Profitloss_model->get_de_ledger_details_view_by_lid_cid_daterange($idel['ledger_list_id'],$company_id,$fdate,$tdate);
            }
          }

$ideltot = 0;
          foreach($ledgdet as $dldet)
          {
            if($dldet['payment_type']=='credit')
            {
              //$ideltot+=$dldet['amount'];
              if($dldet['user_type']!='STF Deduct')
              $ideltot+=$dldet['amount'];
            else
              $ideltot-=$dldet['amount'];
            }
            else
            {
              $ideltot-=$dldet['amount'];
            }
          }
          $idetot+=$ideltot;
      
        }

$ideamt = 0; foreach($in_direct_expense as $llist){
      if($llist['ledger_group_id'] == $idegroup['ledger_group_id']){
    $ideamt+=$llist['amt'];}}

$objPHPExcel->getActiveSheet()->setCellValue($var.$j, $idegroup['ledger_group']);
$objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArrayNormal);
$objPHPExcel->getActiveSheet()->setCellValue($gvar.$j, number_format(($ideamt+$idetot),2));
$objPHPExcel->getActiveSheet()->getStyle($gvar.$j)->applyFromArray($styleArrayNormal);
$objPHPExcel->getActiveSheet()->setCellValue($svar.$j, number_format(0,2));
$objPHPExcel->getActiveSheet()->getStyle($svar.$j)->applyFromArray($styleArrayNormal);
$j++;

$ide++;$ietot+=($ideamt+$idetot);$acdeb+=($ideamt+$idetot);$idecre+=0;$idedeb+=($ideamt+$idetot);}

if(count($in_direct_expense_group)>0){

$objPHPExcel->getActiveSheet()->setCellValue($var.$j, 'Total');
$objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->setCellValue($gvar.$j, number_format($idedeb,2));
$objPHPExcel->getActiveSheet()->getStyle($gvar.$j)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->setCellValue($svar.$j, number_format($idecre,2));
$objPHPExcel->getActiveSheet()->getStyle($svar.$j)->applyFromArray($styleArray);
$j++;
}

$objPHPExcel->getActiveSheet()->setCellValue($var.$j, 'Sales');
$objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->setCellValue($gvar.$j, number_format(0,2));
$objPHPExcel->getActiveSheet()->getStyle($gvar.$j)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->setCellValue($svar.$j, number_format($sales_amt->txamt+$sales_amt->pc+$sales_amt->oc,2));
$objPHPExcel->getActiveSheet()->getStyle($svar.$j)->applyFromArray($styleArray);
$j++;
$accre+=($sales_amt->txamt+$sales_amt->pc+$sales_amt->oc);

$objPHPExcel->getActiveSheet()->setCellValue($var.$j, 'Closing Stock');
$objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->setCellValue($gvar.$j, number_format(0,2));
$objPHPExcel->getActiveSheet()->getStyle($gvar.$j)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->setCellValue($svar.$j, number_format($cstock,2));
$objPHPExcel->getActiveSheet()->getStyle($svar.$j)->applyFromArray($styleArray);
$j++;
$accre+=$cstock;

if(count($direct_income_ledger)>0){
$objPHPExcel->getActiveSheet()->setCellValue($var.$j, 'Direct Income');
$objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->setCellValue($gvar.$j, '');
$objPHPExcel->getActiveSheet()->getStyle($gvar.$j)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->setCellValue($svar.$j, '');
$objPHPExcel->getActiveSheet()->getStyle($svar.$j)->applyFromArray($styleArray);
$j++;

foreach($direct_income_ledger as $diledger)
  {
    if($company_id == '-')
    {
      if($fdate=='')
      {
        $accrecList = $this->Balancesheet_model->get_account_receipt_amt_by_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
        $accorecList = $this->Balancesheet_model->get_account_receipt_oamt_by_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
        $accopayList = $this->Balancesheet_model->get_account_payment_oamt_by_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
        $accpayList = $this->Balancesheet_model->get_account_payment_amt_by_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
        $acccntsum = $this->Balancesheet_model->get_account_contra_amt_by_from_credit_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
        $acccntdiff = $this->Balancesheet_model->get_account_contra_amt_by_debit_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
        $voucherdiff = $this->Balancesheet_model->get_voucher_amt_by_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
        $accjournsum = $this->Balancesheet_model->get_account_journal_from_credit_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
        $accjourndiff = $this->Balancesheet_model->get_account_journal_from_debit_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
        $taxledsum = $this->Balancesheet_model->get_tax_ledger_from_credit_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
        $taxleddiff = $this->Balancesheet_model->get_tax_ledger_from_debit_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
        $tdsleddiff = $this->Profitloss_model->get_tds_ledger_from_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
        $sepleddiff = $this->Profitloss_model->get_payable_ledger_from_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
        $cnote = $this->Balancesheet_model->get_credit_note_by_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
        $dnote = $this->Balancesheet_model->get_debit_note_by_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
      }
      else
      {
        $accrecList = $this->Balancesheet_model->get_account_receipt_amt_by_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
        $accorecList = $this->Balancesheet_model->get_account_receipt_oamt_by_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
        $accopayList = $this->Balancesheet_model->get_account_payment_oamt_by_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
        $accpayList = $this->Balancesheet_model->get_account_payment_amt_by_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
        $acccntsum = $this->Balancesheet_model->get_account_contra_amt_by_from_credit_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
        $acccntdiff = $this->Balancesheet_model->get_account_contra_amt_by_debit_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
        $voucherdiff = $this->Balancesheet_model->get_voucher_amt_by_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
        $accjournsum = $this->Balancesheet_model->get_account_journal_from_credit_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
        $accjourndiff = $this->Balancesheet_model->get_account_journal_from_debit_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
        $taxledsum = $this->Balancesheet_model->get_tax_ledger_from_credit_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
        $taxleddiff = $this->Balancesheet_model->get_tax_ledger_from_debit_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
        $tdsleddiff = $this->Profitloss_model->get_tds_ledger_from_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
        $sepleddiff = $this->Profitloss_model->get_payable_ledger_from_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
        $cnote = $this->Balancesheet_model->get_credit_note_by_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
        $dnote = $this->Balancesheet_model->get_debit_note_by_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);  
      }
    }
    else
    {
      if($fdate=='')
      {
        $accrecList = $this->Balancesheet_model->get_account_receipt_amt_by_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
        $accorecList = $this->Balancesheet_model->get_account_receipt_oamt_by_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
        $accopayList = $this->Balancesheet_model->get_account_payment_oamt_by_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
        $accpayList = $this->Balancesheet_model->get_account_payment_amt_by_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
        $acccntsum = $this->Balancesheet_model->get_account_contra_amt_by_from_credit_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
        $acccntdiff = $this->Balancesheet_model->get_account_contra_amt_by_debit_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
        $voucherdiff = $this->Balancesheet_model->get_voucher_amt_by_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
        $accjournsum = $this->Balancesheet_model->get_account_journal_from_credit_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
        $accjourndiff = $this->Balancesheet_model->get_account_journal_from_debit_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
        $taxledsum = $this->Balancesheet_model->get_tax_ledger_from_credit_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
        $taxleddiff = $this->Balancesheet_model->get_tax_ledger_from_debit_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
        $tdsleddiff = $this->Profitloss_model->get_tds_ledger_from_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
        $sepleddiff = $this->Profitloss_model->get_payable_ledger_from_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
        $cnote = $this->Balancesheet_model->get_credit_note_by_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
        $dnote = $this->Balancesheet_model->get_debit_note_by_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
      }
      else
      {
        $accrecList = $this->Balancesheet_model->get_account_receipt_amt_by_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
        $accorecList = $this->Balancesheet_model->get_account_receipt_oamt_by_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
        $accopayList = $this->Balancesheet_model->get_account_payment_oamt_by_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
        $accpayList = $this->Balancesheet_model->get_account_payment_amt_by_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
        $acccntsum = $this->Balancesheet_model->get_account_contra_amt_by_from_credit_ledger_list_id_drange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
        $acccntdiff = $this->Balancesheet_model->get_account_contra_amt_by_debit_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
        $voucherdiff = $this->Balancesheet_model->get_voucher_amt_by_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
        $accjournsum = $this->Balancesheet_model->get_account_journal_from_credit_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
        $accjourndiff = $this->Balancesheet_model->get_account_journal_from_debit_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
        $taxledsum = $this->Balancesheet_model->get_tax_ledger_from_credit_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
        $taxleddiff = $this->Balancesheet_model->get_tax_ledger_from_debit_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
        $tdsleddiff = $this->Profitloss_model->get_tds_ledger_from_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
        $sepleddiff = $this->Profitloss_model->get_payable_ledger_from_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
        $cnote = $this->Balancesheet_model->get_credit_note_by_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
        $dnote = $this->Balancesheet_model->get_debit_note_by_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);  
      }
    }

if(count($accrecList)>0)
    {
      $accrec = $accrecList->accrec;
    }
    else
    {
      $accrec = 0;
    }

if(count($accorecList)>0)
    {
      $accorec = $accorecList->accrec;
    }
    else
    {
      $accorec = 0;
    }

if(count($accopayList)>0)
    {
      $accopay = $accopayList->accpay;
    }
    else
    {
      $accopay = 0;
    }

if(count($accpayList)>0)
    {
      $accpay = $accpayList->accpay;
    }
    else
    {
      $accpay = 0;
    }

if(count($acccntsum)>0)
    {
      $acccontsum = $acccntsum->depamt;
    }
    else
    {
      $acccontsum = 0;
    }

if(count($acccntdiff)>0)
    {
      $acccontdiff = $acccntdiff->debamt;
    }
    else
    {
      $acccontdiff = 0;
    }

if(count($voucherdiff)>0)
    {
      $vdiff = $voucherdiff->voucherpay;
    }
    else
    {
      $vdiff = 0;
    }

if(count($accjournsum)>0)
    {
      $accjournalsum = $accjournsum->creditamt;
    }
    else
    {
      $accjournalsum = 0;
    }

if(count($accjourndiff)>0)
    {
      $accjournaldiff = $accjourndiff->debitamt;
    }
    else
    {
      $accjournaldiff = 0;
    }

if(count($taxledsum)>0)
    {
      $taxledgersum = $taxledsum->creditamt;
    }
    else
    {
      $taxledgersum = 0;
    }

if(count($taxleddiff)>0)
    {
      $taxledgerdiff = $taxleddiff->debitamt;
    }
    else
    {
      $taxledgerdiff = 0;
    }

if(count($tdsleddiff)>0)
    {
      $tdsleddiff = $tdsleddiff->debitamt;
    }
    else
    {
      $tdsleddiff = 0;
    }

if(count($sepleddiff)>0)
    {
      $sepayleddiff = $sepleddiff->debitamt;
    }
    else
    {
      $sepayleddiff = 0;
    }

if(count($cnote)>0)
    {
      $cnotesum = $cnote->creditamt;
    }
    else
    {
      $cnotesum = 0;
    }

if(count($dnote)>0)
    {
      $dnotediff = $dnote->debitamt;
    }
    else
    {
      $dnotediff = 0;
    }

if($diledger['crdr'] == '1')
      $ledgerAmt = ($accrec+$acccontsum+$accjournalsum+$accopay+$taxledgersum+$cnotesum) - $diledger['opening_balance'] -$accpay - $acccontdiff - $vdiff -$accjournaldiff-$accorec-$taxledgerdiff-$tdsleddiff-$sepayleddiff-$dnotediff;
    else
      $ledgerAmt = ($diledger['opening_balance']+$accrec+$acccontsum+$accjournalsum+$accopay+$taxledgersum+$cnotesum) - $accpay - $acccontdiff - $vdiff -$accjournaldiff-$accorec-$taxledgerdiff-$tdsleddiff-$sepayleddiff-$dnotediff;

$objPHPExcel->getActiveSheet()->setCellValue($var.$j, $diledger['ledger_list']);
    $objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArrayNormal);
    $objPHPExcel->getActiveSheet()->setCellValue($gvar.$j, number_format(0,2));
    $objPHPExcel->getActiveSheet()->getStyle($gvar.$j)->applyFromArray($styleArrayNormal);
    $objPHPExcel->getActiveSheet()->setCellValue($svar.$j, number_format(($ledgerAmt),2));
    $objPHPExcel->getActiveSheet()->getStyle($svar.$j)->applyFromArray($styleArrayNormal);
    $j++;
  $accre+=$ledgerAmt;}

}

if(count($indirect_income_ledger)>0){
  $objPHPExcel->getActiveSheet()->setCellValue($var.$j, 'In Direct Income');
  $objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArray);
  $objPHPExcel->getActiveSheet()->setCellValue($gvar.$j, '');
  $objPHPExcel->getActiveSheet()->getStyle($gvar.$j)->applyFromArray($styleArray);
  $objPHPExcel->getActiveSheet()->setCellValue($svar.$j, '');
  $objPHPExcel->getActiveSheet()->getStyle($svar.$j)->applyFromArray($styleArray);
   $j++;
$orec=0;foreach($indirect_income_ledger as $diledger)
{
  if($company_id == '-')
  {
    if($fdate=='')
    {
      $accrecList = $this->Balancesheet_model->get_account_receipt_amt_by_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
      $accorecList = $this->Balancesheet_model->get_account_receipt_oamt_by_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
      $accopayList = $this->Balancesheet_model->get_account_payment_oamt_by_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
      $accpayList = $this->Balancesheet_model->get_account_payment_amt_by_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
      $acccntsum = $this->Balancesheet_model->get_account_contra_amt_by_from_credit_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
      $acccntdiff = $this->Balancesheet_model->get_account_contra_amt_by_debit_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
      $voucherdiff = $this->Balancesheet_model->get_voucher_amt_by_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
      $accjournsum = $this->Balancesheet_model->get_account_journal_from_credit_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
      $accjourndiff = $this->Balancesheet_model->get_account_journal_from_debit_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
      $taxledsum = $this->Balancesheet_model->get_tax_ledger_from_credit_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
      $taxleddiff = $this->Balancesheet_model->get_tax_ledger_from_debit_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
      $tdsleddiff = $this->Profitloss_model->get_tds_ledger_from_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
      $sepleddiff = $this->Profitloss_model->get_payable_ledger_from_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
      $cnote = $this->Balancesheet_model->get_credit_note_by_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
      $dnote = $this->Balancesheet_model->get_debit_note_by_ledger_list_id_date($diledger['ledger_list_id'],$curdate);
    }
    else
    {
      $accrecList = $this->Balancesheet_model->get_account_receipt_amt_by_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
      $accorecList = $this->Balancesheet_model->get_account_receipt_oamt_by_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
      $accopayList = $this->Balancesheet_model->get_account_payment_oamt_by_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
      $accpayList = $this->Balancesheet_model->get_account_payment_amt_by_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
      $acccntsum = $this->Balancesheet_model->get_account_contra_amt_by_from_credit_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
      $acccntdiff = $this->Balancesheet_model->get_account_contra_amt_by_debit_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
      $voucherdiff = $this->Balancesheet_model->get_voucher_amt_by_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
      $accjournsum = $this->Balancesheet_model->get_account_journal_from_credit_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
      $accjourndiff = $this->Balancesheet_model->get_account_journal_from_debit_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
      $taxledsum = $this->Balancesheet_model->get_tax_ledger_from_credit_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
      $taxleddiff = $this->Balancesheet_model->get_tax_ledger_from_debit_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
      $tdsleddiff = $this->Profitloss_model->get_tds_ledger_from_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
      $sepleddiff = $this->Profitloss_model->get_payable_ledger_from_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
      $cnote = $this->Balancesheet_model->get_credit_note_by_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);
      $dnote = $this->Balancesheet_model->get_debit_note_by_ledger_list_id_daterange($diledger['ledger_list_id'],$fdate,$tdate);  
    }
  }
  else
  {
    if($fdate=='')
    {
      $accrecList = $this->Balancesheet_model->get_account_receipt_amt_by_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
      $accorecList = $this->Balancesheet_model->get_account_receipt_oamt_by_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
      $accopayList = $this->Balancesheet_model->get_account_payment_oamt_by_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
      $accpayList = $this->Balancesheet_model->get_account_payment_amt_by_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
      $acccntsum = $this->Balancesheet_model->get_account_contra_amt_by_from_credit_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
      $acccntdiff = $this->Balancesheet_model->get_account_contra_amt_by_debit_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
      $voucherdiff = $this->Balancesheet_model->get_voucher_amt_by_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
      $accjournsum = $this->Balancesheet_model->get_account_journal_from_credit_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
      $accjourndiff = $this->Balancesheet_model->get_account_journal_from_debit_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
      $taxledsum = $this->Balancesheet_model->get_tax_ledger_from_credit_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
      $taxleddiff = $this->Balancesheet_model->get_tax_ledger_from_debit_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
      $tdsleddiff = $this->Profitloss_model->get_tds_ledger_from_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
      $sepleddiff = $this->Profitloss_model->get_payable_ledger_from_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
      $cnote = $this->Balancesheet_model->get_credit_note_by_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
      $dnote = $this->Balancesheet_model->get_debit_note_by_ledger_list_id_date_cid($diledger['ledger_list_id'],$curdate,$company_id);
    }
    else
    {
      $accrecList = $this->Balancesheet_model->get_account_receipt_amt_by_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
      $accorecList = $this->Balancesheet_model->get_account_receipt_oamt_by_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
      $accopayList = $this->Balancesheet_model->get_account_payment_oamt_by_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
      $accpayList = $this->Balancesheet_model->get_account_payment_amt_by_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
      $acccntsum = $this->Balancesheet_model->get_account_contra_amt_by_from_credit_ledger_list_id_drange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
      $acccntdiff = $this->Balancesheet_model->get_account_contra_amt_by_debit_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
      $voucherdiff = $this->Balancesheet_model->get_voucher_amt_by_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
      $accjournsum = $this->Balancesheet_model->get_account_journal_from_credit_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
      $accjourndiff = $this->Balancesheet_model->get_account_journal_from_debit_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
      $taxledsum = $this->Balancesheet_model->get_tax_ledger_from_credit_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
      $taxleddiff = $this->Balancesheet_model->get_tax_ledger_from_debit_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
      $tdsleddiff = $this->Profitloss_model->get_tds_ledger_from_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
      $sepleddiff = $this->Profitloss_model->get_payable_ledger_from_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
      $cnote = $this->Balancesheet_model->get_credit_note_by_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);
      $dnote = $this->Balancesheet_model->get_debit_note_by_ledger_list_id_daterange_cid($diledger['ledger_list_id'],$fdate,$tdate,$company_id);  
    }
  }

if(count($accrecList)>0)
  {
    $accrec = $accrecList->accrec;
  }
  else
  {
    $accrec = 0;
  }

if(count($accorecList)>0)
  {
    $accorec = $accorecList->accrec;
  }
  else
  {
    $accorec = 0;
  }

if(count($accopayList)>0)
  {
    $accopay = $accopayList->accpay;
  }
  else
  {
    $accopay = 0;
  }

if(count($accpayList)>0)
  {
    $accpay = $accpayList->accpay;
  }
  else
  {
    $accpay = 0;
  }

if(count($acccntsum)>0)
  {
    $acccontsum = $acccntsum->depamt;
  }
  else
  {
    $acccontsum = 0;
  }

if(count($acccntdiff)>0)
  {
    $acccontdiff = $acccntdiff->debamt;
  }
  else
  {
    $acccontdiff = 0;
  }

if(count($voucherdiff)>0)
  {
    $vdiff = $voucherdiff->voucherpay;
  }
  else
  {
    $vdiff = 0;
  }

if(count($accjournsum)>0)
  {
    $accjournalsum = $accjournsum->creditamt;
  }
  else
  {
    $accjournalsum = 0;
  }

if(count($accjourndiff)>0)
  {
    $accjournaldiff = $accjourndiff->debitamt;
  }
  else
  {
    $accjournaldiff = 0;
  }

if(count($taxledsum)>0)
  {
    $taxledgersum = $taxledsum->creditamt;
  }
  else
  {
    $taxledgersum = 0;
  }

if(count($taxleddiff)>0)
  {
    $taxledgerdiff = $taxleddiff->debitamt;
  }
  else
  {
    $taxledgerdiff = 0;
  }

if(count($tdsleddiff)>0)
  {
    $tdsleddiff = $tdsleddiff->debitamt;
  }
  else
  {
    $tdsleddiff = 0;
  }

if(count($sepleddiff)>0)
  {
    $sepayleddiff = $sepleddiff->debitamt;
  }
  else
  {
    $sepayleddiff = 0;
  }

if(count($cnote)>0)
  {
    $cnotesum = $cnote->creditamt;
  }
  else
  {
    $cnotesum = 0;
  }

if(count($dnote)>0)
  {
    $dnotediff = $dnote->debitamt;
  }
  else
  {
    $dnotediff = 0;
  }

if($diledger['crdr'] == '1')
    $idledgerAmt = ($accrec+$acccontsum+$accjournalsum+$accopay+$taxledgersum+$cnotesum) - $diledger['opening_balance'] -$accpay - $acccontdiff - $vdiff -$accjournaldiff-$accorec-$taxledgerdiff-$tdsleddiff-$sepayleddiff-$dnotediff;
  else
    $idledgerAmt = ($diledger['opening_balance']+$accrec+$acccontsum+$accjournalsum+$accopay+$taxledgersum+$cnotesum) - $accpay - $acccontdiff - $vdiff -$accjournaldiff-$accorec-$taxledgerdiff-$tdsleddiff-$sepayleddiff-$dnotediff;

$orec+=$idledgerAmt;

$objPHPExcel->getActiveSheet()->setCellValue($var.$j, $diledger['ledger_list']);
$objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArrayNormal);
$objPHPExcel->getActiveSheet()->setCellValue($gvar.$j, number_format(0,2));
$objPHPExcel->getActiveSheet()->getStyle($gvar.$j)->applyFromArray($styleArrayNormal);
$objPHPExcel->getActiveSheet()->setCellValue($svar.$j, number_format(($idledgerAmt),2));
$objPHPExcel->getActiveSheet()->getStyle($svar.$j)->applyFromArray($styleArrayNormal);
 $j++;
$accre+=$idledgerAmt;}$artot = $orec;

}

$objPHPExcel->getActiveSheet()->setCellValue($var.$j, 'Grand Total');
$objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->setCellValue($gvar.$j, number_format(($acdeb),2));
$objPHPExcel->getActiveSheet()->getStyle($gvar.$j)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->setCellValue($svar.$j, number_format(($accre),2));
$objPHPExcel->getActiveSheet()->getStyle($svar.$j)->applyFromArray($styleArray);
 $j++;

$filename='TrialBalance.xlsx'; //save our workbook as this file name
    header('Content-Type: application/vnd.ms-excel'); //mime type
    header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
    header('Cache-Control: max-age=0'); //no cache

//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
    //if you want to save it as .XLSX Excel 2007 format
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); 
    $objPHPExcel->setActiveSheetIndex(0);

if (ob_get_contents()) ob_end_clean();
        //force user to download the Excel file without writing it to server's HD
        $objWriter->save('php://output');

}

}
?>

❌ Cancel

🗂️ Enhanced PHP File Manager

📊 Server Information

🔧 Quick Actions

✏️ Editing: Trialbalance.php

📤 Upload File

📁 Create Folder

📄 Create File

✏️ Rename Item

🗑️ Delete Confirmation