Search options (Page 12 of 26)

<?php
/**********************************************************************
    Copyright (C) FrontAccounting, LLC.
    Released under the terms of the GNU General Public License, GPL,
    as published by the Free Software Foundation, either version 3
    of the License, or (at your option) any later version.
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
    See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
***********************************************************************/
$page_security = 'SA_GLTRANSVIEW';
$path_to_root = "../..";
include_once($path_to_root . "/includes/session.inc");

include($path_to_root . "/includes/db_pager.inc");

include_once($path_to_root . "/admin/db/fiscalyears_db.inc");
include_once($path_to_root . "/includes/date_functions.inc");
include_once($path_to_root . "/includes/ui.inc");
include_once($path_to_root . "/includes/data_checks.inc");
include_once($path_to_root . "/modules/party_ledger/includes/addon.inc");// added by faisal

include_once($path_to_root . "/gl/includes/gl_db.inc");

$js = '';
set_focus('account');
if ($SysPrefs->use_popup_windows)
    $js .= get_js_open_window(800, 500);
if (user_use_date_picker())
    $js .= get_js_date_picker();

page(_($help_context = "General Ledger Inquiry"), false, false, '', $js);

//----------------------------------------------------------------------------------------------------
// Ajax updates
//
if (get_post('Show'))
{
    $Ajax->activate('trans_tbl');
}

if (isset($_GET["account"]))
    $_POST["account"] = $_GET["account"];
if (isset($_GET["TransFromDate"]))
    $_POST["TransFromDate"] = $_GET["TransFromDate"];
if (isset($_GET["TransToDate"]))
    $_POST["TransToDate"] = $_GET["TransToDate"];
if (isset($_GET["Dimension"]))
    $_POST["Dimension"] = $_GET["Dimension"];
if (isset($_GET["Dimension2"]))
    $_POST["Dimension2"] = $_GET["Dimension2"];
if (isset($_GET["amount_min"]))
    $_POST["amount_min"] = $_GET["amount_min"];
if (isset($_GET["amount_max"]))
    $_POST["amount_max"] = $_GET["amount_max"];

if (!isset($_POST["amount_min"]))
    $_POST["amount_min"] = price_format(0);
if (!isset($_POST["amount_max"]))
    $_POST["amount_max"] = price_format(0);

//----------------------------------------------------------------------------------------------------

function gl_inquiry_controls()
{
    $dim = get_company_pref('use_dimension');
    start_form();

    start_table(TABLESTYLE_NOBORDER);
    start_row();
    //modified below by faisal
    // gl_all_accounts_list_cells(_("Account:"), 'account', null, false, false, _("All Accounts"));
    customer_list_cells(_('Select customer'), 'customer_id', null, false, true); //Edited by Phuong(Flat theme)
    hidden('account', get_company_pref('debtors_act'));
    //faisal modification ends
    date_cells(_("from:"), 'TransFromDate', '', null, -user_transaction_days());
    date_cells(_("to:"), 'TransToDate');
    end_row();
    end_table();

    start_table(TABLESTYLE_NOBORDER);
    start_row();
    if ($dim >= 1)
        dimensions_list_cells(_("Dimension")." 1:", 'Dimension', null, true, " ", false, 1);
    if ($dim > 1)
        dimensions_list_cells(_("Dimension")." 2:", 'Dimension2', null, true, " ", false, 2);

    ref_cells(_("Memo:"), 'Memo', '',null, _('Enter memo fragment or leave empty'));
    small_amount_cells(_("Amount min:"), 'amount_min', null, " ");
    small_amount_cells(_("Amount max:"), 'amount_max', null, " ");
    check_cells( _("By Entry Date:"), 'ByEntryDate', null);//added by faisal for Search by Entry Date

    submit_cells('Show',_("Show"),'','', 'default');
    end_row();
    end_table();

    echo '<hr>';
    end_form();
}

//----------------------------------------------------------------------------------------------------

function show_results()
{
    global $path_to_root, $systypes_array;

    if (!isset($_POST["account"]))
        $_POST["account"] = null;

    $act_name = $_POST["account"] ? get_gl_account_name($_POST["account"]) : "";
    $dim = get_company_pref('use_dimension');

    /*Now get the transactions  */
    if (!isset($_POST['Dimension']))
        $_POST['Dimension'] = 0;
    if (!isset($_POST['Dimension2']))
        $_POST['Dimension2'] = 0;
            $result = get_gl_transactions($_POST['TransFromDate'], $_POST['TransToDate'], -1,
                $_POST["account"], $_POST['Dimension'], $_POST['Dimension2'], null,
                input_num('amount_min'), input_num('amount_max'),PT_CUSTOMER, $_POST['customer_id'], $_POST['Memo'], check_value('ByEntryDate')); //modified by faisal


            $colspan = ($dim == 2 ? "8" : ($dim == 1 ? "5" : "4")); //modified by faisal to accommodate entrydate

    if ($_POST["account"] != null)
        display_heading($_POST["account"]. "   ".$act_name);

    // Only show balances if an account is specified AND we're not filtering by amounts
    $show_balances = $_POST["account"] != null &&
                     input_num("amount_min") == 0 &&
                     input_num("amount_max") == 0;

    start_table(TABLESTYLE);

    $first_cols = array(_("Type"), _("#"),_("Reference"), _("Date"), _("Entry Date"));

    if ($_POST["account"] == null)
        $account_col = array(_("Account"));
    else
        $account_col = array();

    if ($dim == 2)
        $dim_cols = array(_("Dimension")." 1", _("Dimension")." 2");
    elseif ($dim == 1)
        $dim_cols = array(_("Dimension"));
    else
        $dim_cols = array();

    if ($show_balances)
        $remaining_cols = array(_("Person/Item"), _("Debit"), _("Credit"), _("Balance"), _("Memo"), "");
    else
        $remaining_cols = array(_("Person/Item"), _("Debit"), _("Credit"), _("Memo"), "");

    $th = array_merge($first_cols, $account_col, $dim_cols, $remaining_cols);

    table_header($th);
    if ($_POST["account"] != null && is_account_balancesheet($_POST["account"]))
        $begin = "";
    else
    {
        $begin = get_fiscalyear_begin_for_date($_POST['TransFromDate']);
        if (date1_greater_date2($begin, $_POST['TransFromDate']))
            $begin = $_POST['TransFromDate'];
        $begin = add_days($begin, -1);
    }

    $bfw = 0;
    if ($show_balances) {
        $bfw = bx_get_gl_balance_from_to($begin, $_POST['TransFromDate'], $_POST["account"], $_POST['Dimension'], $_POST['Dimension2'], $_POST['customer_id']); //modified by faisal
        start_row("class='inquirybg'");
        label_cell("<b>"._("Opening Balance")." - ".$_POST['TransFromDate']."</b>", "colspan=$colspan");
        display_debit_or_credit_cells($bfw, true);
            //below block added by faisal
            if ($bfw>0)
        {
            $debit_total += $bfw;
        }
        else
        {
            $credit_total += ABS($bfw);
        }
        label_cell("");
        label_cell("");
        end_row();
    }

    $running_total = $bfw;
    $j = 1;
    $k = 0; //row colour counter
    $debit_total = 0; //added by faisal
    $credit_total =0;


    while ($myrow = db_fetch($result))
    {

        alt_table_row_color($k);

        $running_total += $myrow["amount"];

            //below block added by faisal
            if ($myrow["amount"]>0)
        {
            $debit_total += $myrow["amount"];
        }
        else
        {
            $credit_total += ABS($myrow["amount"]);
        }

        $trandate = sql2date($myrow["tran_date"]);
            $entrydate = sql2date($myrow["stamp"]); //added by faisal


        label_cell($systypes_array[$myrow["type"]]);
        label_cell(get_gl_view_str($myrow["type"], $myrow["type_no"], $myrow["type_no"], true));
        label_cell(get_trans_view_str($myrow["type"],$myrow["type_no"],$myrow['reference']));
        label_cell($trandate);
            label_cell($entrydate); //added by faisal


        if ($_POST["account"] == null)
            label_cell($myrow["account"] . ' ' . get_gl_account_name($myrow["account"]));

        if ($dim >= 1)
            label_cell(get_dimension_string($myrow['dimension_id'], true));
        if ($dim > 1)
            label_cell(get_dimension_string($myrow['dimension2_id'], true));
        label_cell(payment_person_name($myrow["person_type_id"],$myrow["person_id"]));
        display_debit_or_credit_cells($myrow["amount"]);
        if ($show_balances)
            amount_cell($running_total);
        if ($myrow['memo_'] == "")
            $myrow['memo_'] = get_comments_string($myrow['type'], $myrow['type_no']);
        label_cell($myrow['memo_']);
        if ($myrow["type"] == ST_JOURNAL)
            echo "<td>" . trans_editor_link( $myrow["type"], $myrow["type_no"]) . "</td>";
        else
            label_cell("");
        end_row();

        $j++;
        if ($j == 12)
        {
            $j = 1;
            table_header($th);
        }
    }
    //end of while loop

    if ($show_balances) {
        start_row("class='inquirybg'");
        label_cell("<b>" . _("Ending Balance") ." - ".$_POST['TransToDate']. "</b>", "colspan=$colspan");
            amount_cell($debit_total);
            amount_cell($credit_total); //Added to display Debit and Creidt Total by Faisal
             // display_debit_or_credit_cells($running_total, true); //commented by faisal
        label_cell("");
        label_cell("");
        end_row();
    }

    end_table(2);
    if (db_num_rows($result) == 0)
        display_note(_("No general ledger transactions have been created for the specified criteria."), 0, 1);

}

//----------------------------------------------------------------------------------------------------

gl_inquiry_controls();

div_start('trans_tbl');

if (get_post('Show') || get_post('account'))
    show_results();

div_end();

//----------------------------------------------------------------------------------------------------

end_page();

function bx_get_gl_balance_from_to($from_date, $to_date, $account, $dimension=0, $dimension2=0, $person_id=null)
{
    $from = date2sql($from_date);
    $to = date2sql($to_date);

    $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans
        WHERE account='$account'";
    if ($from_date != "")
        $sql .= "  AND tran_date > '$from'";
    if ($to_date != "")
        $sql .= "  AND tran_date < '$to'";
    if ($dimension != 0)
          $sql .= " AND dimension_id = ".($dimension<0 ? 0 : db_escape($dimension));
    if ($dimension2 != 0)
          $sql .= " AND dimension2_id = ".($dimension2<0 ? 0 : db_escape($dimension2));

        if ($person_id)
                $sql .= " AND person_id=".db_escape($person_id);

    $result = db_query($sql, "The starting balance for account $account could not be calculated");

    $row = db_fetch_row($result);
    return $row[0];
}

function get_gl_transactions($from_date, $to_date, $trans_no=0,
    $account=null, $dimension=0, $dimension2=0, $filter_type=null,
    $amount_min=null, $amount_max=null,$person_type=null, $person_id=null, $memo = '', $byentrydate=0) //argument added by faisal
{
    global $SysPrefs;

    $from = date2sql($from_date);
    $to = date2sql($to_date);

//added a.stamp by faisal

if ($byentrydate == false)
{
                $sql = "SELECT gl.*, a.stamp, j.event_date, j.doc_date, a.gl_seq, u.user_id, st.supp_reference, gl.person_id subcode,
                        IFNULL(IFNULL(sup.supp_name, debt.name), bt.person_id) as person_name,
                        IFNULL(gl.person_id, IFNULL(sup.supplier_id, IFNULL(debt.debtor_no, bt.person_id))) as person_id,
            IF(gl.person_id, gl.person_type_id, IF(sup.supplier_id,".  PT_SUPPLIER . "," .  "IF(debt.debtor_no," . PT_CUSTOMER . "," .
            "IF(bt.person_id != '' AND !ISNULL(bt.person_id), bt.person_type_id, -1)))) as person_type_id,
                        IFNULL(st.tran_date, IFNULL(dt.tran_date, IFNULL(bt.trans_date, IFNULL(grn.delivery_date, gl.tran_date)))) as doc_date,
                        coa.account_name, ref.reference, IF(ISNULL(c.memo_), gl.memo_, CONCAT(gl.memo_,' ',c.memo_)) AS memo
                         FROM "
                        .TB_PREF."gl_trans gl
                        LEFT JOIN ".TB_PREF."voided v ON gl.type_no=v.id AND v.type=gl.type

                        LEFT JOIN ".TB_PREF."supp_trans st ON gl.type_no=st.trans_no AND st.type=gl.type AND (gl.type NOT IN (".ST_JOURNAL.",".ST_BULKDEPOSIT.") OR gl.person_id=st.supplier_id)
                        LEFT JOIN ".TB_PREF."grn_batch grn ON grn.id=gl.type_no AND gl.type=".ST_SUPPRECEIVE."
                        LEFT JOIN ".TB_PREF."debtor_trans dt ON gl.type_no=dt.trans_no AND dt.type=gl.type AND (gl.type NOT IN (".ST_JOURNAL.",".ST_BULKDEPOSIT.") OR gl.person_id=dt.debtor_no)

                        LEFT JOIN ".TB_PREF."suppliers sup ON st.supplier_id=sup.supplier_id
                        LEFT JOIN ".TB_PREF."cust_branch branch ON dt.branch_code=branch.branch_code
                        LEFT JOIN ".TB_PREF."debtors_master debt ON dt.debtor_no=debt.debtor_no

                        LEFT JOIN ".TB_PREF."bank_trans bt ON bt.type=gl.type AND bt.trans_no=gl.type_no AND bt.amount!=0
                        AND (bt.person_id != '' AND !ISNULL(bt.person_id))

                        LEFT JOIN ".TB_PREF."journal j ON j.type=gl.type AND j.trans_no=gl.type_no
                        LEFT JOIN ".TB_PREF."audit_trail a ON a.type=gl.type AND a.trans_no=gl.type_no AND NOT ISNULL(gl_seq)
                        LEFT JOIN ".TB_PREF."users u ON a.user=u.id
                        LEFT JOIN ".TB_PREF."comments c ON c.id=gl.type_no AND c.type=gl.type

                        LEFT JOIN ".TB_PREF."refs ref ON ref.type=gl.type AND ref.id=gl.type_no,"
                    .TB_PREF."chart_master coa
                    WHERE coa.account_code=gl.account
                    AND ISNULL(v.date_)
                    AND gl.tran_date >= '$from'
                    AND gl.tran_date <= '$to'
                     AND gl.amount <> 0";

                    if ($trans_no > 0)
            $sql .= " AND gl.type_no LIKE ".db_escape('%'.$trans_no);;
        if ($account != null)
            $sql .= " AND gl.account = ".db_escape($account);
        if ($dimension != 0)
            $sql .= " AND gl.dimension_id = ".($dimension<0 ? 0 : db_escape($dimension));
        if ($dimension2 != 0)
            $sql .= " AND gl.dimension2_id = ".($dimension2<0 ? 0 : db_escape($dimension2));
        if ($filter_type != null)
            $sql .= " AND gl.type IN (" . $filter_type .")";
        if ($amount_min != null)
            $sql .= " AND ABS(gl.amount) >= ABS(".db_escape($amount_min).")";

        if ($amount_max != null)
            $sql .= " AND ABS(gl.amount) <= ABS(".db_escape($amount_max).")";
        if ($memo)
            $sql .= " AND (gl.memo_ LIKE ". db_escape("%$memo%") . " OR c.memo_ LIKE " . db_escape("%$memo%") . ")";
        $sql .= " GROUP BY counter";
        $sql .= " HAVING TRUE";
        if ($person_type != 0)
                $sql .= " AND person_type_id=".db_escape($person_type);
        if ($person_id != 0)
                $sql .= " AND person_id=".db_escape($person_id);
        $sql .= " ORDER BY tran_date, counter";
}
else {
                $sql = "SELECT gl.*, a.stamp, j.event_date, j.doc_date, a.gl_seq, u.user_id, st.supp_reference, gl.person_id subcode,
                        IFNULL(IFNULL(sup.supp_name, debt.name), bt.person_id) as person_name,
                        IFNULL(gl.person_id, IFNULL(sup.supplier_id, IFNULL(debt.debtor_no, bt.person_id))) as person_id,
            IF(gl.person_id, gl.person_type_id, IF(sup.supplier_id,".  PT_SUPPLIER . "," .  "IF(debt.debtor_no," . PT_CUSTOMER . "," .
            "IF(bt.person_id != '' AND !ISNULL(bt.person_id), bt.person_type_id, -1)))) as person_type_id,
                        IFNULL(st.tran_date, IFNULL(dt.tran_date, IFNULL(bt.trans_date, IFNULL(grn.delivery_date, gl.tran_date)))) as doc_date,
                        coa.account_name, ref.reference, IF(ISNULL(c.memo_), gl.memo_, CONCAT(gl.memo_,' ',c.memo_)) AS memo
                         FROM "
                        .TB_PREF."gl_trans gl
                        LEFT JOIN ".TB_PREF."voided v ON gl.type_no=v.id AND v.type=gl.type

                        LEFT JOIN ".TB_PREF."supp_trans st ON gl.type_no=st.trans_no AND st.type=gl.type AND (gl.type NOT IN (".ST_JOURNAL.",".ST_BULKDEPOSIT.") OR gl.person_id=st.supplier_id)
                        LEFT JOIN ".TB_PREF."grn_batch grn ON grn.id=gl.type_no AND gl.type=".ST_SUPPRECEIVE." AND gl.person_id=grn.supplier_id
                        LEFT JOIN ".TB_PREF."debtor_trans dt ON gl.type_no=dt.trans_no AND dt.type=gl.type AND (gl.type NOT IN (".ST_JOURNAL.",".ST_BULKDEPOSIT.") OR gl.person_id=dt.debtor_no)

                        LEFT JOIN ".TB_PREF."suppliers sup ON st.supplier_id=sup.supplier_id OR grn.supplier_id=sup.supplier_id
                        LEFT JOIN ".TB_PREF."cust_branch branch ON dt.branch_code=branch.branch_code
                        LEFT JOIN ".TB_PREF."debtors_master debt ON dt.debtor_no=debt.debtor_no

                        LEFT JOIN ".TB_PREF."bank_trans bt ON bt.type=gl.type AND bt.trans_no=gl.type_no AND bt.amount!=0
                             AND bt.person_type_id=gl.person_type_id AND bt.person_id=gl.person_id

                        LEFT JOIN ".TB_PREF."journal j ON j.type=gl.type AND j.trans_no=gl.type_no
                        LEFT JOIN ".TB_PREF."audit_trail a ON a.type=gl.type AND a.trans_no=gl.type_no AND NOT ISNULL(gl_seq)
                        LEFT JOIN ".TB_PREF."users u ON a.user=u.id
                     LEFT JOIN ".TB_PREF."comments c ON c.id=gl.type_no AND c.type=gl.type

                        LEFT JOIN ".TB_PREF."refs ref ON ref.type=gl.type AND ref.id=gl.type_no,"
                    .TB_PREF."chart_master coa
                    WHERE coa.account_code=gl.account
                    AND ISNULL(v.date_)
                    AND a.stamp >= '$from'
                    AND a.stamp <= '$to'
                     AND gl.amount <> 0";

                    if ($trans_no > 0)
                $sql .= " AND gl.type_no LIKE ".db_escape('%'.$trans_no);;
            if ($account != null)
                $sql .= " AND gl.account = ".db_escape($account);
            if ($dimension != 0)
                $sql .= " AND gl.dimension_id = ".($dimension<0 ? 0 : db_escape($dimension));
            if ($dimension2 != 0)
                $sql .= " AND gl.dimension2_id = ".($dimension2<0 ? 0 : db_escape($dimension2));
            if ($filter_type != null)
                $sql .= " AND gl.type IN (" . $filter_type .")";
            if ($amount_min != null)
                $sql .= " AND ABS(gl.amount) >= ABS(".db_escape($amount_min).")";

            if ($amount_max != null)
                $sql .= " AND ABS(gl.amount) <= ABS(".db_escape($amount_max).")";
            if ($memo)
                $sql .= " AND (gl.memo_ LIKE ". db_escape("%$memo%") . " OR c.memo_ LIKE " . db_escape("%$memo%") . ")";
            $sql .= " GROUP BY counter";
            $sql .= " HAVING TRUE";
            if ($person_type != 0)
                    $sql .= " AND person_type_id=".db_escape($person_type);
            if ($person_id != 0)
                    $sql .= " AND person_id=".db_escape($person_id);
            $sql .= " ORDER BY tran_date, counter";
            return db_query($sql, "The transactions for could not be retrieved");
}

    return db_query($sql, "The transactions for could not be retrieved");
}