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: Balancesheet.php

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
ini_set('max_execution_time',259200);
class Balancesheet 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'));
    //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');
        $net_profit = $data['net_profit'] = $this->input->post('net_profit');
      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;
      }
      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'] = '';
      }

/*if($this->input->post('account_type')!='')
      {
        $data['atype'] = $this->input->post('account_type');
        $aaid = $this->input->post('account_attributes_list_id');
        if($aaid!='')
        {
          //$data['account_journal_list'] = $this->Accountjournal_model->get_account_journal_by_date_and_aaid($curdate,$aaid);

if($this->input->post('searchChange')!='daterange')
            $data['account_journal_list'] = $this->Accountjournal_model->get_account_journal_by_date_and_aaid($curdate,$aaid);
          else
            $data['account_journal_list'] = $this->Accountjournal_model->get_account_journal_by_daterange_and_aaid($fdate,$tdate,$aaid);

//print_r($data['day_book_list']);exit;
          $data['aatri'] = $this->input->post('account_attributes_list_id');
        }
        else
        {
          //$data['account_journal_list'] = $this->Accountjournal_model->get_account_journal_by_date($curdate);
          if($this->input->post('searchChange')!='daterange')
            $data['account_journal_list'] = $this->Accountjournal_model->get_account_journal_by_date($curdate);
          else
            $data['account_journal_list'] = $this->Accountjournal_model->get_account_journal_by_daterange($fdate,$tdate);
          $data['aatri'] = '';
        }

if($this->input->post('account_type') == 'assets')
        {
          $atid=1;
        }
        else
        {
          $atid=2;
        }
        $asscat = $this->Daybook_model->get_account_category_by_atype_id($atid);
        $option = "<option value=''>Select Attributes</option>";
        foreach($asscat as $acat)
        {
          $assattri = $this->Daybook_model->get_account_attributes_by_acat_id($acat['account_category_id']);
          $option.="<optgroup label='".$acat['account_category']."'>";
          foreach($assattri as $aattri)
          {
            $sel = $aaid==$aattri['account_attributes_id']?'selected':'';
            $option.="<option value=".$aattri['account_attributes_id']." ".$sel.">".$aattri['account_attributes']."</option>";
          }
          $option.="</optgroup>";
        }
        $data['opt'] = $option;
        //$data['opt'] = $atid;
        //print_r($data);exit;
      }
      else
      {*/
        //$data['atype'] = '';
        //$data['aatri'] = '';
        //$data['opt']= '';
        //$data['account_journal_list'] = $this->Accountjournal_model->get_account_journal_by_date($curdate);
        
        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();
          //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 = dte GROUP BY aj.account_attributes_id ORDER BY aj.account_journal_id DESC
          //$data['account_journal_list'] = $this->Balancesheet_model->get_account_journal_bs_by_date($curdate);

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

/*$opening_stock = $this->db->query("SELECT sum(opening_stock) as opening_stock FROM `stock_ledger` WHERE stock_ledger_date='$curdate' $cid")->row();
          $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();

$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();
          //$in_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.ledger_type_id!=1 GROUP BY l.ledger_sub_group_id")->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='$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(grand_total) as amt FROM `order` WHERE order_date='$curdate' $cid")->row();

$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' $oloc)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' $invloc)) 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();
          $other_receipt = $this->db->query("SELECT ar.particulars,sum(ar.amount) as amt FROM `account_receipt` ar, ledger_list l WHERE l.ledger_list_id = ar.ledger_list_id AND ar.account_receipt_date='$curdate' AND ar.expense_type='others' $decid GROUP BY ar.particulars")->result_array();
          $closing_stock = $this->db->query("SELECT sum(closing_stock) as closing_stock FROM `stock_ledger` WHERE stock_ledger_date='$curdate' $cid")->row();

}
        else
        {
          //$data['account_journal_list'] = $this->Balancesheet_model->get_account_journal_bs_by_daterange($fdate,$tdate);

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

//echo "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 AND DATE(v.created_on)>='$fdate' AND DATE(v.created_on)<='$tdate' $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 AND DATE(v.created_on)>='$fdate' AND DATE(v.created_on)<='$tdate' $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>='$fdate' AND p.purchase_date<='$tdate' $loc AND p.status=1 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.status=0 AND e.expense_date>='$fdate' AND e.expense_date<='$tdate' $eloc 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' 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' 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 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 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 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 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 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 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 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 GROUP BY v.vendor_id)) ven GROUP BY ven.vendor_id ORDER BY ven.vendor_id ASC";exit;

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

/*$opening_stock = $this->db->query("SELECT sum(opening_stock) as opening_stock FROM `stock_ledger` WHERE stock_ledger_date='$fdate' $cid")->row();
          $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>='$fdate' AND p.invoice_date<='$tdate' AND p.status!=5 $purid")->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();
          //$in_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.ledger_type_id!=1 GROUP BY l.ledger_sub_group_id")->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(grand_total) as amt FROM `order` WHERE order_date>='$fdate' AND order_date<='$tdate' $cid")->row();

$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' $oloc)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' $invloc)) 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();
          $other_receipt = $this->db->query("SELECT ar.particulars,sum(ar.amount) as amt FROM `account_receipt` ar, ledger_list l WHERE l.ledger_list_id = ar.ledger_list_id AND ar.account_receipt_date>='$fdate' AND ar.account_receipt_date<='$tdate' AND ar.expense_type='others' $decid GROUP BY ar.particulars")->result_array();
          $closing_stock = $this->db->query("SELECT sum(closing_stock) as closing_stock FROM `stock_ledger` WHERE stock_ledger_date='$tdate' $cid")->row();

$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;
      $net_profit = $data['net_profit'] = 0;
      $curdate = date('Y-m-d');
      $data['dtrange'] = '';
      //$data['atype'] = '';
      //$data['aatri'] = '';
      //$data['opt']= '';
      $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 = '';

/*$opening_stock = $this->Profitloss_model->get_opening_stock_by_singledate($curdate);
      $purchase_amt = $this->Profitloss_model->get_purchase_amount_by_singledate($curdate);
      $direct_expense = $this->Profitloss_model->get_direct_expense_by_singledate($curdate);
      $in_direct_expense = $this->Profitloss_model->get_in_direct_expense_by_singledate($curdate);
      $sales_amt = $this->Profitloss_model->get_sales_amount_by_singledate($curdate);
      $direct_invoice = $this->Profitloss_model->get_direct_invoice_amount_by_singledate($curdate);
      $other_receipt = $this->Profitloss_model->get_other_receipt_by_singledate($curdate);
      $closing_stock = $this->Profitloss_model->get_closing_stock_by_singledate($curdate);

$direct_expense_list = $this->Profitloss_model->get_expense_staff_expense_by_singledate($curdate);*/

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

}
//echo "FDATE : ".$fdate;exit;
      /*$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();*/

//$styear = $sfyear = '2019-03';

/*$etot=0; foreach($direct_expense_group as $degroup){
  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'];
    }
  }
  $etot+=($deamt+$detot+$dseamt);
}*/

/*$diamt = 0;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;

$diamt+=$ledgerAmt;
    }

$gp = ($sales_amt->txamt+$sales_amt->pc+$sales_amt->oc+$cstock+$diamt)-($purchase_amt->amt-$purchase_amt->ocharge+$etot+$opening_stock->opening_stock);
if($gp>=0){
  $gpamt = $gp;
  $glamt = 0;
  $lgp = $gp;
}
else
{
  $glamt = abs($gp);
  $gpamt = 0;
  $lgp=0;
}

$ietot=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'];
      }
      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'];
    }
  }
  $ietot+=($ideamt+$idetot);
}
$pprof = $glamt+$ietot;

$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;
      }

$artot = $orec;

$sprof = $gpamt+$artot;

$nprof = $sprof-$pprof;*/

//$data['net_profit'] = $nprof;
//echo $nprof;exit;

$this->load->view('balancesheet/balancesheet_list',$data);
  }

public function openingbalancesheet()
  {
      $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')
      {
        $data['cstock'] = $this->input->post('cstock');
        $company_id = $this->input->post('company_id');
        $data['company_id'] = $company_id;
        if($company_id!='')
        {
          $cloc = 'AND vob.location_id='.$company_id;
          $dloc = 'AND cob.location_id='.$company_id;
        }
        else
        {
          $cloc = '';
          $dloc = '';
        }
      }
      else
      {
        $data['cstock'] = 0;
        $data['company_id'] = '';
        $cloc = '';
        $dloc = '';
      }

$this->load->view('balancesheet/opening_balancesheet_list',$data);
  }

public function openingbalancesheet_export($cstock = null,$cid = 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();
    $company_id = $cid;
    $data['company_id'] = $company_id;
    if($company_id!='')
    {
      $cloc = 'AND vob.location_id='.$company_id;
      $dloc = 'AND cob.location_id='.$company_id;
    }
    else
    {
      $cloc = '';
      $dloc = '';
    }

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

$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Liabilities');
    $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('F1', 'Amount');
    $objPHPExcel->getActiveSheet()->getStyle('F1')->applyFromArray($styleArray);

$var = 'B';$j=2; $gvar = 'B';$svar = 'E';$ltot=0;foreach($liabilities_list as $llist){
        $objPHPExcel->getActiveSheet()->setCellValue($var.$j, $llist['account_category']);
        $objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArray);
        $aattri = explode(',', $llist['account_attributes']);
        $aattriid = explode(',', $llist['account_attributes_id']);
        $bs_status = explode(',', $llist['bs_status']);

$gvar++;$svar++;
        for($i=0;$i<count($aattri);$i++)
        {
          $s=$j;
          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)
            {
              $lcheck=1;
            }
            else
            {
              $lcheck=0;
            }
            $objPHPExcel->getActiveSheet()->setCellValue($gvar.$s, $aattri[$i]);
            $objPHPExcel->getActiveSheet()->getStyle($gvar.$s)->applyFromArray($styleArray);

$ledgerAmt = 0;
            if($lcheck>0){
              foreach($ledger_list as $u_list)
              {
                $lamt = $u_list['opening_balance'];
                $ledgerAmt+= $u_list['opening_balance'];

$j++;
                $objPHPExcel->getActiveSheet()->setCellValue('D'.$j, $u_list['ledger_list']);
                $objPHPExcel->getActiveSheet()->getStyle('D'.$j)->applyFromArray($styleArray);
                $objPHPExcel->getActiveSheet()->setCellValue('E'.$j, $lamt);
                $objPHPExcel->getActiveSheet()->getStyle('E'.$j)->applyFromArray($styleArray);
              }

$objPHPExcel->getActiveSheet()->setCellValue($svar.$s, $ledgerAmt);
              $objPHPExcel->getActiveSheet()->getStyle($svar.$s)->applyFromArray($styleArray);
            }
            else
            {
              $objPHPExcel->getActiveSheet()->setCellValue($svar.$s, '-');
              $objPHPExcel->getActiveSheet()->getStyle($svar.$s)->applyFromArray($styleArray);
            }
            $ltot+=$ledgerAmt;
          }
          else if($bs_status[$i]==1){
            $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)
              {
                $bal = $sc_list['cpamt']-$sc_list['dpamt'];
                if($bal!=0)
                {
                  $j++;                
                  $objPHPExcel->getActiveSheet()->setCellValue('D'.$j, $vendorDetails->vendor_name.' - '.$vendorDetails->contact_no);
                  $objPHPExcel->getActiveSheet()->getStyle('D'.$j)->applyFromArray($styleArray);
                  $objPHPExcel->getActiveSheet()->setCellValue('E'.$j, $bal);
                  $objPHPExcel->getActiveSheet()->getStyle('E'.$j)->applyFromArray($styleArray);
                }
                $sctot+=$bal;
              }
            }
            $objPHPExcel->getActiveSheet()->setCellValue($gvar.$s, $aattri[$i]);
            $objPHPExcel->getActiveSheet()->getStyle($gvar.$s)->applyFromArray($styleArray);

$gvar = 'B';$svar = 'E';}
    $j++;
    $objPHPExcel->getActiveSheet()->setCellValue('C'.$j, 'Total');
    $objPHPExcel->getActiveSheet()->getStyle('C'.$j)->applyFromArray($styleArray);

$objPHPExcel->getActiveSheet()->setCellValue('E'.$j, $ltot);
    $objPHPExcel->getActiveSheet()->getStyle('E'.$j)->applyFromArray($styleArray);

$objPHPExcel->getActiveSheet()->setCellValue('H1', 'Assets');
    $objPHPExcel->getActiveSheet()->getStyle('H1')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('M1', 'Amount');
    $objPHPExcel->getActiveSheet()->getStyle('M1')->applyFromArray($styleArray);

$var = 'I';$j=2; $gvar = 'I';$svar = 'L';$ltot=0;foreach($assets_list as $llist){
        $objPHPExcel->getActiveSheet()->setCellValue($var.$j, $llist['account_category']);
        $objPHPExcel->getActiveSheet()->getStyle($var.$j)->applyFromArray($styleArray);
        $aattri = explode(',', $llist['account_attributes']);
        $aattriid = explode(',', $llist['account_attributes_id']);
        $bs_status = explode(',', $llist['bs_status']);

$ledgerAmt = 0;
            if($lcheck>0){
              foreach($ledger_list as $u_list)
              {
                $lamt = $u_list['opening_balance'];
                $ledgerAmt+= $u_list['opening_balance'];
                $j++;

$objPHPExcel->getActiveSheet()->setCellValue('K'.$j, $u_list['ledger_list']);
                $objPHPExcel->getActiveSheet()->getStyle('K'.$j)->applyFromArray($styleArray);
                $objPHPExcel->getActiveSheet()->setCellValue('L'.$j, $lamt);
                $objPHPExcel->getActiveSheet()->getStyle('L'.$j)->applyFromArray($styleArray);
              }

$objPHPExcel->getActiveSheet()->setCellValue($svar.$s, $ledgerAmt);
              $objPHPExcel->getActiveSheet()->getStyle($svar.$s)->applyFromArray($styleArray);
            }
            else
            {
              $objPHPExcel->getActiveSheet()->setCellValue($svar.$s, '-');
              $objPHPExcel->getActiveSheet()->getStyle($svar.$s)->applyFromArray($styleArray);
            }
            $ltot+=$ledgerAmt;
          }
          else if($bs_status[$i]==1){
            $sctot = 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']);
              $bal = $sd_list['dpamt']-$sd_list['cpamt'];
              if($bal!=0)
              {                
                $j++;
                $objPHPExcel->getActiveSheet()->setCellValue('K'.$j, $customerDetails->customer_name);
                $objPHPExcel->getActiveSheet()->getStyle('K'.$j)->applyFromArray($styleArray);
                $objPHPExcel->getActiveSheet()->setCellValue('L'.$j, $bal);
                $objPHPExcel->getActiveSheet()->getStyle('L'.$j)->applyFromArray($styleArray);
              }
              $sctot+=$bal;
            }
            $objPHPExcel->getActiveSheet()->setCellValue($gvar.$s, $aattri[$i]);
            $objPHPExcel->getActiveSheet()->getStyle($gvar.$s)->applyFromArray($styleArray);

$objPHPExcel->getActiveSheet()->setCellValue($svar.$s, $sctot);
            $objPHPExcel->getActiveSheet()->getStyle($svar.$s)->applyFromArray($styleArray);
            $ltot+=$sctot;
          }

else
          {
            $objPHPExcel->getActiveSheet()->setCellValue($gvar.$s, $aattri[$i]);
            $objPHPExcel->getActiveSheet()->getStyle($gvar.$s)->applyFromArray($styleArray);

$objPHPExcel->getActiveSheet()->setCellValue($svar.$s, $cstock);
            $objPHPExcel->getActiveSheet()->getStyle($svar.$s)->applyFromArray($styleArray);
            $ltot+=$cstock;
          }
        $j++;}

$gvar = 'I';$svar = 'L';}
    $j++;
    $objPHPExcel->getActiveSheet()->setCellValue('J'.$j, 'Total');
    $objPHPExcel->getActiveSheet()->getStyle('J'.$j)->applyFromArray($styleArray);

$objPHPExcel->getActiveSheet()->setCellValue('M'.$j, $ltot);
    $objPHPExcel->getActiveSheet()->getStyle('M'.$j)->applyFromArray($styleArray);

$filename='Opening_Balancesheet.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');

}

/*public function index()
  {
    $sdate = $this->Dashboard_model->get_start_date();
      $diff = date('Y') - $sdate->year;
      $opt = "";
      for($i=$diff;$i>=0;$i--)
      {
        $y = date('Y') - $i;
        if($y==date('Y'))
          $opt.="<option value=".$y." selected>".$y."</option>";
        else
          $opt.="<option value=".$y.">".$y."</option>";
      }
      $data['fyearList'] = $opt;
      $data['tyearList'] = $opt;

if($sdate->year == date('Y'))
      {
        $syearmonth = $this->Dashboard_model->get_start_year_months($sdate->month);
      }
      else
      {
        $syearmonth = $this->Dashboard_model->get_other_year_months();
      }

$option = "";
      foreach($syearmonth as $schart){
          if($schart['month'] == date('m')){
          $option.="<option value=".$schart['month']." selected>".$schart['name']."</option>";
      }else{
          $option.="<option value=".$schart['month'].">".$schart['name']."</option>";
      }}

$data['fmonthList'] = $option;
      $data['tmonthList'] = $option;

$data['liabilities_list'] = $this->Balancesheet_model->get_liabilities_list();
      $data['assets_list'] = $this->Balancesheet_model->get_assets_list();

if($_SERVER['REQUEST_METHOD'] == 'POST')
      {
        $fyear = $this->input->post('fromYear');
        $fmonth = $this->input->post('fromMonth');
        $tyear = $this->input->post('toYear');
        $tmonth = $this->input->post('toMonth');

if(strlen($fmonth)==1)
          $fmonth = '0'.$fmonth;
        if(strlen($tmonth)==1)
          $tmonth = '0'.$tmonth;

$sfyear = $fyear.'-'.$fmonth;
        $styear = $tyear.'-'.$tmonth;

$opt = "";
        for($i=$diff;$i>=0;$i--)
        {
          $y = date('Y') - $i;
          if($y==$fyear)
            $opt.="<option value=".$y." selected>".$y."</option>";
          else
            $opt.="<option value=".$y.">".$y."</option>";
        }
        $data['fyearList'] = $opt;

$opt = "";
        for($i=$diff;$i>=0;$i--)
        {
          $y = date('Y') - $i;
          if($y==$tyear)
            $opt.="<option value=".$y." selected>".$y."</option>";
          else
            $opt.="<option value=".$y.">".$y."</option>";
        }
        $data['tyearList'] = $opt;

if($sdate->year == $fyear)
        {
          $fsyearmonth = $this->Dashboard_model->get_start_year_change_months($sdate->month);
        }
        else if($fyear<date('Y'))
        {
          $fsyearmonth = $this->Dashboard_model->get_other_year_change_months();
        }
        else
        {
          $fsyearmonth = $this->Dashboard_model->get_other_year_months();
        }

$option = "";
        foreach($fsyearmonth as $schart){
            if($schart['month'] == $fmonth){
            $option.="<option value=".$schart['month']." selected>".$schart['name']."</option>";
        }else{
            $option.="<option value=".$schart['month'].">".$schart['name']."</option>";
        }}

$data['fmonthList'] = $option;

if($sdate->year == $tyear)
        {
          $tsyearmonth = $this->Dashboard_model->get_start_year_change_months($sdate->month);
        }
        else if($tyear<date('Y'))
        {
          $tsyearmonth = $this->Dashboard_model->get_other_year_change_months();
        }
        else
        {
          $tsyearmonth = $this->Dashboard_model->get_other_year_months();
        }

$option = "";
        foreach($tsyearmonth as $schart){
            if($schart['month'] == $tmonth){
            $option.="<option value=".$schart['month']." selected>".$schart['name']."</option>";
        }else{
            $option.="<option value=".$schart['month'].">".$schart['name']."</option>";
        }}

$data['tmonthList'] = $option;
        //print_r($data);exit;
      }
      else
      {
        $fyear = date('Y');
        $fmonth = date('m');
        $tyear = date('Y');
        $tmonth = date('m');

if(strlen($fmonth)==1)
          $fmonth = '0'.$fmonth;
        if(strlen($tmonth)==1)
          $tmonth = '0'.$tmonth;

$sfyear = $fyear.'-'.$fmonth;
        $styear = $tyear.'-'.$tmonth;

$data['liabilities_amount'] = $this->Balancesheet_model->get_liabilities_amount_form_day_book($sfyear,$styear);
        $data['assets_amount'] = $this->Balancesheet_model->get_assets_amount_form_day_book($sfyear,$styear);
      }
      $fmonthName = date('M', mktime(0, 0, 0, $fmonth, 10));
      $tmonthName = date('M', mktime(0, 0, 0, $tmonth, 10));
      $data['fmy'] = $fmonthName.' '.$fyear;
      $data['tmy'] = $tmonthName.' '.$tyear;

$data['fyear'] = $fyear;
      $data['fmonth'] = $fmonth;
      $data['tyear'] = $tyear;
      $data['tmonth'] = $tmonth;

$this->load->view('balancesheet/balancesheet_list',$data);
  }*/

public function printbalancesheet()
  {
    $fyear = $this->input->post('fyear');
    $fmonth = $this->input->post('fmonth');
    $tyear = $this->input->post('tyear');
    $tmonth = $this->input->post('tmonth');
    $fmonthName = date('M', mktime(0, 0, 0, $fmonth, 10));
    $tmonthName = date('M', mktime(0, 0, 0, $tmonth, 10));
    $data['fmy'] = $fmonthName.' '.$fyear;
    $data['tmy'] = $tmonthName.' '.$tyear;
    $data['liabilities_list'] = $this->Balancesheet_model->get_liabilities_list();
    $data['assets_list'] = $this->Balancesheet_model->get_assets_list();
    $data['liabilities_amount'] = $this->Balancesheet_model->get_liabilities_amount_form_day_book($fyear,$fmonth,$tyear,$tmonth);
    $data['assets_amount'] = $this->Balancesheet_model->get_assets_amount_form_day_book($fyear,$fmonth,$tyear,$tmonth);
    $this->load->view('balancesheet/print_balancesheet',$data);
  }

public function getYearMonth()
  {
    $year = $_POST['year'];

$sdate = $this->Dashboard_model->get_start_date();

if($sdate->year == $year)
    {
      $syearmonth = $this->Dashboard_model->get_start_year_change_months($sdate->month);
    }
    else if($year<date('Y'))
    {
      $syearmonth = $this->Dashboard_model->get_other_year_change_months();
    }
    else
    {
      $syearmonth = $this->Dashboard_model->get_other_year_months();
    }

$monthList = $syearmonth;
    $option ="<option value=''>Select Month</option>";
    foreach($monthList as $schart)
    {
      $option.="<option value=".$schart['month'].">".$schart['name']."</option>";
    }
    echo $option;
  }

public function balancesheet_export($cid = null,$psearch=null,$dtrange=null,$drnge=null,$cstock=null,$net_profit=null)
  {
    /*echo "CID : ".$cid."<br>";
    echo "psearch : ".$psearch."<br>";
    echo "dtrange : ".$dtrange."<br>";
    echo "drnge : ".$drnge."<br>";*/
      $liabilities_list = $this->Balancesheet_model->get_liabilities_list();
      $assets_list = $this->Balancesheet_model->get_assets_list();
      $location_list = $this->Balancesheet_model->get_location_list();

if($psearch=='curdate')
      {
        $curdate = date('Y-m-d');
        $fdate = $curdate;
        $tdate = '';
      }
      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 = '';
      }
      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;
      }
      else
      {
        $cid = '';
        $decid = '';
        $purid = '';
        $ajcid = '';
        $loc = '';
        $iloc = '';
        $eloc = '';
        $oloc = '';
        $invloc = '';
        $ajloc = '';
        $cob = '';
        $vob = '';
        $gloc = '';
        $cdnloc = '';
        $slocid = '';
        $data['compDet'] = '';
      }
        
        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.invoice_date='$curdate' $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='$curdate' $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='$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 credit_debit_note_date='$curdate' $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='$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();

$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(grand_total) as amt FROM `order` WHERE order_date='$curdate' $cid")->row();

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

$other_receipt = $this->db->query("SELECT ar.particulars,sum(ar.amount) as amt FROM `account_receipt` ar, ledger_list l WHERE l.ledger_list_id = ar.ledger_list_id AND ar.account_receipt_date='$curdate' AND ar.expense_type='others' $decid GROUP BY ar.particulars")->result_array();

$closing_stock = $this->db->query("SELECT sum(closing_stock) as closing_stock FROM `stock_ledger` WHERE stock_ledger_date='$curdate' $cid")->row();

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

/*$opening_stock = $this->db->query("SELECT sum(opening_stock) as opening_stock FROM `stock_ledger` WHERE stock_ledger_date='$fdate' $cid")->row();

$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>='$fdate' AND p.invoice_date<='$tdate' 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>='$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(grand_total) as amt FROM `order` WHERE order_date>='$fdate' AND order_date<='$tdate' $cid")->row();

$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' $oloc)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' $invloc)) 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();

$other_receipt = $this->db->query("SELECT ar.particulars,sum(ar.amount) as amt FROM `account_receipt` ar, ledger_list l WHERE l.ledger_list_id = ar.ledger_list_id AND ar.account_receipt_date>='$fdate' AND ar.account_receipt_date<='$tdate' AND ar.expense_type='others' $decid GROUP BY ar.particulars")->result_array();

$closing_stock = $this->db->query("SELECT sum(closing_stock) as closing_stock FROM `stock_ledger` WHERE stock_ledger_date='$tdate' $cid")->row();

/*$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();

$etot=0; foreach($direct_expense_group as $degroup){
  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);
      }
    }