$godown_transfer_master['gto'] = ""; $godown_transfer_master['gto_hidden'] = ""; $godown_transfer_master['gfrom'] = ""; $godown_transfer_master['gfrom_hidden'] = ""; $godown_transfer_master['remarks'] = ""; $godown_transfer_detail = array(); $orderby = ""; $sortorder = ""; require_once 'paginator.class.php'; $pages = new Paginator(); $accordion_active = "1"; if ($_SERVER['REQUEST_METHOD'] == 'POST' || isset($_GET['ipp'])) { require_once 'paginator.class.php'; $vdate = ""; if (isset($_POST['godown_transfer_date']) && strlen($_POST['godown_transfer_date']) > 0) { $vdate = "gtm.trans_date = '" . my_sql_date($_POST['godown_transfer_date']) . "'"; } else { if (isset($_GET['godown_transfer_date']) && strlen($_GET['godown_transfer_date']) > 0) { $vdate = "gtm.trans_date = '{$_GET['godown_transfer_date']}'"; } } $vfgodown = ""; if (isset($_POST['godown_transfer_from1_hidden']) && strlen($_POST['godown_transfer_from1_hidden']) > 0 && strlen($_POST['godown_transfer_from1']) > 0) { $vfgodown = "gtm.from_godown = '{$_POST['godown_transfer_from1_hidden']}'"; } else { if (isset($_GET['godown_transfer_from1_hidden']) && strlen($_GET['godown_transfer_from1_hidden']) > 0 && strlen($_GET['godown_transfer_from1']) > 0) { $vfgodown = "gtm.from_godown = '{$_GET['godown_transfer_from1_hidden']}'"; } } $vtgodown = ""; if (isset($_POST['godown_transfer_to1_hidden']) && strlen($_POST['godown_transfer_to1_hidden']) > 0 && strlen($_POST['godown_transfer_to1']) > 0) {
session_start(); $selected_company_id = $_SESSION['company_id']; ob_start(); dump($_POST); $data = array(); updateDate('voucher', $_POST['voucher_date']); $year_id = getValue("year_id", "sys_financial_year", "company_ref_id = {$selected_company_id} AND is_active = 'Yes'", true); $first_colval = ""; $seprator = ""; $last_part = ""; $first_part = ""; $first_tab_ins = ""; foreach ($_POST as $key => $val) { if ($key == "voucher_date" || $key == "voucher_remarks" || $key == "voucher_type" || $key == "voucher_cheque") { if ($key == 'voucher_date') { $val = my_sql_date($val); } $first_colval = $first_colval . $seprator . $key . " = '" . $val . "'"; $seprator = ','; } else { if ($key == "voucher_id") { $last_part = " WHERE voucher_id = " . $val; } } } $oper = $_POST['oper']; if ($oper == 'add') { $data['seq'] = getSequenceNextVal('voucher_master', $_SESSION['company_id']); $first_part = " INSERT INTO voucher_master SET company_ref_id = {$_SESSION['company_id']}, year_id = {$year_id}, seq = {$data['seq']}, voucher_by = {$_SESSION['user_id']}, "; $first_tab_ins = $first_part . $first_colval; } else {
continue; } else { $billnos[] = $range * 1; } } } $billnos = implode("','", $billnos); $where .= " AND m.seq IN ('{$billnos}')\n"; $year_id = getIVal("year_id", "sys_financial_year", "company_ref_id = {$_SESSION['company_id']} AND is_active = 'Yes'"); $where .= " AND m.year_id = {$year_id}\n"; } else { if (strlen($_GET['date_start']) > 0) { $where .= " AND TO_DAYS(m.pur_ret_date) >= TO_DAYS('" . my_sql_date($_GET['date_start']) . "')\n"; } if (strlen($_GET['date_end']) > 0) { $where .= " AND TO_DAYS(m.pur_ret_date) <= TO_DAYS('" . my_sql_date($_GET['date_end']) . "')\n"; } if (strlen($_GET['bill']) > 0) { $where .= " AND m.billno = '{$_GET['bill']}'\n"; } if (strlen($_GET['supplier']) > 0 && strlen($_GET['supplier_hidden']) > 0) { $where .= " AND pm.party_code = " . $_GET['supplier_hidden'] . "\n"; } } $sql = "" . "SELECT m.pur_ret_master_id,\n" . " DATE_FORMAT(m.pur_ret_date, '%d-%b-%y') pur_ret_date,\n" . " m.billno,\n" . " p.party_name\n" . " FROM purchase_return_master m\n" . " INNER JOIN purchase_return_detail d ON d.pur_ret_master_id = m.pur_ret_master_id\n" . " LEFT OUTER JOIN party p ON p.party_code = m.party_code\n" . " WHERE m.company_ref_id = {$_SESSION['company_id']}\n" . $where . "ORDER BY m.pur_ret_date, p.party_name"; //dump($sql); $result = $mysqli->query($sql); $ddata = array(); while ($row = $result->fetch_assoc()) { $ddata[] = $row; }
$_title = getReportTitle(); if (isset($_GET['report'])) { updateDate('rpt_inventory_movement_start', $_GET['date1']); updateDate('rpt_inventory_movement_end', $_GET['date2']); $item = array(); $result = $mysqli->query("SELECT im.i_code, im.cno, im.manufacturer_no, im.description, im.company_ref_id FROM item_master im WHERE i_code = {$_GET['item_hidden']}"); if ($row = $result->fetch_assoc()) { $item = $row; } $date1 = $_GET['date1']; if (strlen($date1) < 1) { $date1 = getFinancialStartDate(); } $where = " AND TO_DAYS(trans_date) >= TO_DAYS('" . my_sql_date($date1) . "')\n"; if (strlen($_GET['date2']) > 0) { $where .= " AND TO_DAYS(trans_date) <= TO_DAYS('" . my_sql_date($_GET['date2']) . "')\n"; } $data = array(); $result = $mysqli->query("SELECT g.g_name FROM godown g WHERE g.company_ref_id = {$item['company_ref_id']}"); while ($row = $result->fetch_assoc()) { $gname = $row['g_name']; $data["{$gname}"] = array(); $data["{$gname}"]['total'] = (double) 0; $data["{$gname}"]['opening_balance'] = (double) 0; $data["{$gname}"]['rate'] = (double) 0; $data["{$gname}"]['item'] = $item; $data["{$gname}"]['data'] = array(); } $sql = "" . "SELECT g.g_name, ob.opening_balance, ob.rate\n" . " FROM item_opening_balance ob\n" . " LEFT OUTER JOIN godown g ON g.g_code = ob.g_code\n" . " WHERE ob.item_code = {$item['i_code']}\n" . " AND ob.year_id = (SELECT year_id FROM sys_financial_year WHERE STR_TO_DATE('{$date1}', '%d-%m-%Y') BETWEEN start_date AND end_date AND company_ref_id = {$item['company_ref_id']})\n"; $result = $mysqli->query($sql); while ($row = $result->fetch_assoc()) {
$dr_acc = getValue("code", "sys_codes", "description = 'Cash in Hand' AND company_ref_id = {$selected_company_id}", true); $cr_acc = getValue("code", "sys_codes", "description = 'Cash Sales' AND company_ref_id = {$selected_company_id}", true); } else { //Credit Sale //Debit - Party //Credit - Credit Sales $dr_acc = getValue("account_ref_id", "party", "party_code = {$_POST['sales_party_hidden']}", true); $cr_acc = getValue("code", "sys_codes", "description = 'Credit Sales' AND company_ref_id = {$selected_company_id}", true); } if ($_POST['oper'] == 'update') { $voucher_id = getValue("voucher_id", "sales_master", "sale_master_id = {$_POST['sales_id']}", true); d_mysql_query("UPDATE voucher_detail SET debit_amount = {$sale}, account_ref_id = {$dr_acc} WHERE voucher_id = {$voucher_id} AND remarks = 'Debit'"); d_mysql_query("UPDATE voucher_detail SET credit_amount = {$sale}, account_ref_id = {$cr_acc} WHERE voucher_id = {$voucher_id} AND remarks = 'Credit'"); d_mysql_query("UPDATE voucher_master SET voucher_date = '" . my_sql_date($_POST['sales_date']) . "', voucher_remarks = '{$_POST['sales_remarks']}' WHERE voucher_id = {$voucher_id}"); } else { $createVoucher = createVoucher($selected_company_id, $year_id, $dr_acc, $cr_acc, $sale, $_POST['sales_remarks'], $parent_key, 'S', my_sql_date($_POST['sales_date']), $_SESSION['user_id']); dump($createVoucher); $mysqli->close(); } $voucher_id = getValue("voucher_id", "sales_master", "sale_master_id = {$parent_key}", true); d_mysql_query("UPDATE voucher_detail SET debit_amount = {$sum} WHERE voucher_id = {$voucher_id} AND remarks = 'Debit'"); d_mysql_query("UPDATE voucher_detail SET debit_amount = {$disc} WHERE voucher_id = {$voucher_id} AND remarks = 'Discount'"); //reset all taxes d_mysql_query("UPDATE voucher_detail SET credit_amount = 0 WHERE voucher_id = {$voucher_id} AND remarks = 'Tax'"); if ($tax > 0) { $taxes = array(); $counter = $_POST['counter'] * 1; for ($i = 1; $i <= $counter; $i++) { $i_code = $_POST["item_" . $i . "_hidden"]; if (strlen($i_code) < 1) { continue;
<?php include '../includes/connect.php'; require_once '../includes/connecti.php'; require_once '../includes/funcs.inc.php'; /** @var $mysqli mysqli */ /** @var $result mysqli_result */ include '../includes/JSON.php'; @session_start(); $selected_company_id = $_SESSION['company_id']; $login_user_name = $_SESSION['user_name']; ob_start(); dump($_POST); $data = array(); updateDate('cash_book', $_POST['cash_book_date']); $cash_book_date = my_sql_date($_POST['cash_book_date']); if ($_POST['oper'] == 'delete') { $cash_book_id = $_POST['cb_id']; $voucher_id = getValue("voucher_id", "voucher_detail", "voucher_id = (SELECT voucher_id FROM cash_book WHERE cb_id = {$cash_book_id})", true); d_mysql_query("DELETE FROM voucher_detail WHERE voucher_id = {$voucher_id}"); d_mysql_query("DELETE FROM voucher_master WHERE voucher_id = {$voucher_id}"); d_mysql_query("DELETE FROM cash_book WHERE cb_id = {$cash_book_id}"); } else { $v_cash_acc_id = $_POST['cash_book_id']; $year_id = getValue("year_id", "sys_financial_year", "company_ref_id = {$selected_company_id} AND is_active = 'Yes'", true); $counter = $_POST['counter'] * 1; for ($i = 1; $i <= $counter; $i++) { $account_code = $_POST["account_" . $i . "_hidden"]; $cash_book_ammount = 0; $cash_book_type = ""; if (isset($_POST["inp3_{$i}"])) {
require_once 'paginator.class.php'; $pages = new Paginator(); $accordion_active = "1"; if ($_SERVER['REQUEST_METHOD'] == 'POST' || isset($_GET['ipp'])) { $year_id = getValue("year_id", "sys_financial_year", "company_ref_id = {$selected_company_id} AND is_active = 'Yes'"); $s_sale_id = ""; if (isset($_POST['s_sale_id']) && strlen($_POST['s_sale_id']) > 0) { $s_sale_id = "srm.seq = {$_POST['s_sale_id']}"; } else { if (isset($_GET['s_sale_id']) && strlen($_GET['s_sale_id']) > 0) { $s_sale_id = "srm.seq = {$_GET['s_sale_id']}"; } } $s_sdate = ""; if (isset($_POST['s_sale_date']) && strlen($_POST['s_sale_date']) > 0) { $s_sdate = "srm.rsale_date = '" . my_sql_date($_POST['s_sale_date']) . "'"; } else { if (isset($_GET['s_sale_date']) && strlen($_GET['s_sale_date']) > 0) { $s_sdate = "srm.rsale_date = '{$_GET['s_sale_date']}'"; } } $s_supplier = ""; if (isset($_POST['s_supplier_hidden']) && strlen($_POST['s_supplier_hidden']) > 0 && strlen($_POST['s_supplier']) > 0) { $s_supplier = "srm.party_code = '{$_POST['s_supplier_hidden']}'"; } else { if (isset($_GET['s_supplier_hidden']) && strlen($_GET['s_supplier_hidden']) > 0 && strlen($_GET['s_supplier']) > 0) { $s_supplier = "srm.party_code = '{$_GET['s_supplier_hidden']}'"; } } $s_billno = ""; if (isset($_POST['s_billno']) && strlen($_POST['s_billno']) > 0) {
$v_cr_sal_acc_id = getValue("code", "sys_codes", "description = 'CREDIT SALES'"); $v_party_acc_code = getValue("account_code", "party", "party_code = {$_POST['sales_party_hidden']}"); dd_mysql_query("UPDATE voucher_detail SET account_code = {$v_party_acc_code}, remarks = '2-1' WHERE voucher_id = {$voucher_id} AND debit_amount > 0"); dd_mysql_query("UPDATE voucher_detail SET account_code = {$v_cr_sal_acc_id}, remarks = '3-1' WHERE voucher_id = {$voucher_id} AND credit_amount > 0"); } dd_mysql_query("UPDATE voucher_master SET voucher_date = '" . my_sql_date($_POST['sales_date']) . "' WHERE voucher_id = {$voucher_id}"); } else { $v_ch_sale_acc_id = getValue("code", "sys_codes", "description = 'CASH SALES'"); $v_cash_acc_id = getValue("code", "sys_codes", "description = 'CASH ACCOUNT'"); $v_cr_sale_acc_id = getValue("code", "sys_codes", "description = 'CREDIT SALES'"); $v_party_acc_code = getValue("account_code", "party", "party_code = {$_POST['sales_party_hidden']}"); require_once '../includes/connecti.php'; if (isset($_POST['sales_type']) && $_POST['sales_type'] == 'C') { createVoucher($selected_company_id, $year_id, $v_cash_acc_id, $v_ch_sale_acc_id, $sum, $_POST['sales_remarks'], $parent_key, 'S', my_sql_date($_POST['sales_date'])); } else { createVoucher($selected_company_id, $year_id, $v_party_acc_code, $v_cr_sale_acc_id, $sum, $_POST['sales_remarks'], $parent_key, 'S', my_sql_date($_POST['sales_date'])); } $mysqli->close(); } $url .= "?sales_id={$parent_key}"; } echo "<a href='{$url}'>{$url}</a><br/>"; $json = new Services_JSON(); $output = $json->encode($data); print $output; $out = ob_get_contents(); ob_end_clean(); if (!isLive()) { $fp = fopen('sales_cud.htm', 'w'); fwrite($fp, "<html><head><title>SALES CUD</title></head><body>"); fwrite($fp, $out);
/** * @param int $account_ref_id Chart of Account Reference ID * @param string $date_start Start Date * @param string $date_end End Date * * @return array of float containing debit amount and credit amount */ function getOutstandingDataTransactions($account_ref_id, $date_start = "", $date_end = "") { global $mysqli; /** @var $result mysqli_result */ $sql = "" . " SELECT IFNULL(sum(vd.debit_amount), 0) debit,\n" . " IFNULL(sum(vd.credit_amount), 0) credit\n" . " FROM voucher_detail vd\n" . " INNER JOIN voucher_master vm ON vm.voucher_id = vd.voucher_id\n" . " WHERE vd.account_ref_id = {$account_ref_id}\n" . " AND vm.voucher_type <> 'CO'\n"; if (strlen($date_start) > 0) { $sql .= " AND TO_DAYS(vm.voucher_date) >= TO_DAYS('" . my_sql_date($date_start) . "')\n"; } if (strlen($date_end) > 0) { $sql .= " AND TO_DAYS(vm.voucher_date) <= TO_DAYS('" . my_sql_date($date_end) . "')\n"; } if (strlen($date_start) + strlen($date_end) < 1) { $d = date('Y') . "-01-01"; $sql .= " AND TO_DAYS(vm.voucher_date) >= TO_DAYS('{$d}')\n"; } //dump($sql); $result = $mysqli->query($sql); $data = array('debit' => (double) 0, 'credit' => (double) 0); if ($row = $result->fetch_assoc()) { $data['debit'] = $row['debit'] * 1; $data['credit'] = $row['credit'] * 1; } $result->close(); return $data; }
require_once '../includes/funcs.inc.php'; $conn = oci_connect($db_user, $db_pass, $db_host); $selected_company_id = $_SESSION['company_id']; $year_id = getValue("year_id", "sys_financial_year", "company_ref_id = {$selected_company_id} AND is_active = 'Yes'"); $login_user_name = $_SESSION['user_name']; $v_ch_id = null; if (isset($_GET['cash_book_id'])) { $v_ch_id = $_GET['cash_book_id']; } if (is_null($v_ch_id)) { $v_ch_id = getValue("account_ref_id", "sys_users", "user_name = '{$login_user_name}' AND company_ref_id = {$selected_company_id}"); } if (is_null($v_ch_id)) { $v_ch_id = getValue("code", "sys_codes", "description = 'Cash in Hand' AND company_ref_id = {$selected_company_id}"); } $cash_book_date = my_sql_date($_SESSION['dates']['cash_book']); $cash_book_detail = array(); if (isset($_GET['cash_date'])) { $cash_book_date = $_GET['cash_date']; } $sql = "" . " SELECT cb.cb_id,\n" . " cb.account_ref_id,\n" . " cb.cb_type,\n" . " cb.cb_amount,\n" . "\t cb.cb_remarks,\n" . " coa.account_name\n" . " FROM cash_book cb\n" . " INNER JOIN chart_of_account coa ON coa.account_ref_id = cb.account_ref_id\n" . " INNER JOIN sys_financial_year y ON y.year_id = cb.year_id AND NVL(y.is_close, 'No') = 'No'\n" . " WHERE to_char(cb.cb_date, 'RRRR-MM-DD') = '{$cash_book_date}'\n" . " AND cb.book_id = {$v_ch_id}\n" . "ORDER BY cb_id"; //echo $sql; $cash_book_search = oci_parse($conn, $sql); oci_execute($cash_book_search); while ($row = oci_fetch_array($cash_book_search)) { $arr = array(); $arr['cash_book_id'] = $row[0]; $arr['account_code_hidden'] = $row[1]; $arr['cash_book_type'] = $row[2]; $arr['cash_book_amount'] = $row[3]; $arr['cash_book_remarks'] = $row[4];
$days_limit = getIVal("days_limit", "party", "account_ref_id = {$account}"); $payment_term = getIVal("payment_term", "party", "account_ref_id = {$account}") * 1; $aob = getInitialOpeningBalance($account); $pay = getPayments($account, $date_start, $date_end, true); $balance = $aob - $pay; if ($balance < 0) { $balance = $balance * -1; } else { $balance = 0; } $sql = "" . "SELECT sa.invoice, sa.seq,\n" . " sa.remarks,\n" . " DATE_FORMAT(sa.sale_date, '%d-%b-%y') sale_date,\n" . " DATEDIFF(now(), sa.sale_date) days,\n" . " sa.net\n" . " FROM vu_sales_age sa\n" . " WHERE sa.party_code = {$party_hidden}\n" . " AND sa.company_ref_id = {$_SESSION['company_id']}\n"; if (strlen($date_start) > 0) { $sql .= " AND TO_DAYS(sa.sale_date) >= TO_DAYS('" . my_sql_date($date_start) . "')\n"; } if (strlen($date_end) > 0) { $sql .= " AND TO_DAYS(sa.sale_date) <= TO_DAYS('" . my_sql_date($date_end) . "')\n"; } if (strlen($date_start) + strlen($date_end) < 1) { $d = date('Y') . "-01-01"; $sql .= " AND TO_DAYS(sa.sale_date) >= TO_DAYS('{$d}')\n"; } $sql .= "ORDER BY sa.party_code, sa.sale_date"; //dump($sql); $result = $mysqli->query($sql); $data = array(); $lastBalance = (double) 0; while ($row = $result->fetch_assoc()) { $row['net'] = $row['net'] * 1; $net = $row['net']; if ($balance >= $net) { $balance -= $net;
<?php include '../includes/db.php'; include '../includes/JSON.php'; include '../includes/funcs.inc.php'; $data = array(); $data['valid'] = false; if (isset($_GET['date'])) { $items = oci_parse($conn, "SELECT TO_DAYS(start_date) s, TO_DAYS(end_date) e, TO_DAYS('" . my_sql_date($_GET['date']) . "') u\n" . " FROM sys_financial_year\n" . " WHERE is_active = 'Yes'\n" . " AND company_ref_id = {$_GET['company']}"); oci_execute($items); if ($row = oci_fetch_assoc($items)) { $s = $row['s'] * 1; $e = $row['e'] * 1; $u = $row['u'] * 1; $data['valid'] = $s <= $u && $u <= $e; } } $json = new Services_JSON(); $output = $json->encode($data); print $output;
$result = $mysqli->query($sql); $data = array(); while ($row = $result->fetch_assoc()) { $city = $row['party_city']; $party = $row['party_name']; if (!isset($data[$city])) { $data[$city] = array(); } $data[$city][$party] = array('city' => $row['party_city'], 'party' => $row['party_name'], 'DEPO' => (double) 0, 'TIK' => (double) 0, 'FPI' => (double) 0, 'Local' => (double) 0, 'Total' => (double) 0, 'Collection' => $row['amt'] * 1); } $sql = "" . "SELECT p.party_city, p.party_name, p.vendor_name, p.net\n" . " FROM vu_vendor_sales_amount p\n" . " WHERE p.company_ref_id = {$_SESSION['company_id']}\n" . " AND p.vendor_name IS NOT NULL\n" . $where; if (strlen($_GET['date_start']) > 0) { $sql .= " AND TO_DAYS(IFNULL(p.trans_date, '" . my_sql_date($_GET['date_start']) . "')) >= TO_DAYS('" . my_sql_date($_GET['date_start']) . "')\n"; } if (strlen($_GET['date_end']) > 0) { $sql .= " AND TO_DAYS(IFNULL(p.trans_date, '" . my_sql_date($_GET['date_end']) . "')) <= TO_DAYS('" . my_sql_date($_GET['date_end']) . "')\n"; } if (strlen($_GET['date_start']) + strlen($_GET['date_end']) < 1) { $d = date('Y') . "-01-01"; $sql .= " AND TO_DAYS(IFNULL(p.trans_date, '{$d}')) >= TO_DAYS('{$d}')\n"; } $result = $mysqli->query($sql); while ($row = $result->fetch_assoc()) { $city = $row['party_city']; $party = $row['party_name']; $vendor = $row['vendor_name']; $net = $row['net'] * 1; $data[$city][$party][$vendor] += $net; $data[$city][$party]['Total'] += $net; } $mysqli->close();
continue; } else { $billnos[] = $range * 1; } } } $billnos = implode("','", $billnos); $where .= " AND m.seq IN ('{$billnos}')\n"; $year_id = getIVal("year_id", "sys_financial_year", "company_ref_id = {$_SESSION['company_id']} AND is_active = 'Yes'"); $where .= " AND m.year_id = {$year_id}\n"; } else { if (strlen($_GET['date1']) > 0) { $where .= " AND TO_DAYS(m.rsale_date) >= TO_DAYS('" . my_sql_date($_GET['date1']) . "')\n"; } if (strlen($_GET['date2']) > 0) { $where .= " AND TO_DAYS(m.rsale_date) <= TO_DAYS('" . my_sql_date($_GET['date2']) . "')\n"; } if (strlen($_GET['bill']) > 0) { $where .= " AND m.billno = '{$_GET['bill']}'\n"; } if (strlen($_GET['party_hidden']) > 0) { $where .= " AND m.party_code = {$_GET['party_hidden']} \n"; } } $sql = "" . "SELECT distinct m.rsale_master_id,\n" . " DATE_FORMAT(m.rsale_date, '%d-%b-%y') rsale_date,\n" . " m.billno,\n" . " p.party_name\n" . " FROM sales_return_master m\n" . " INNER JOIN sales_return_detail d ON d.rsale_master_id = m.rsale_master_id\n" . " LEFT OUTER JOIN party p ON p.party_code = m.party_code\n" . " WHERE m.company_ref_id = {$_SESSION['company_id']}\n" . $where . "ORDER BY m.rsale_date, p.party_name"; //dump($sql); $result = $mysqli->query($sql); $ddata = array(); while ($row = $result->fetch_assoc()) { $ddata[] = $row; }
<?php session_start(); include '../includes/connect.php'; require_once '../includes/funcs.inc.php'; $selected_company_id = $_SESSION['company_id']; $cash_book_date = date("Y-m-d"); $cash_book_detail = array(); if (isset($_GET['cash_date'])) { $cash_book_date = my_sql_date($_GET['cash_date']); } $sql = "" . " SELECT cb_id, " . " cash_book.account_code, " . " cb_type, " . " cb_amount, " . "\t cb_remarks, " . " account_name " . " FROM cash_book " . " LEFT OUTER JOIN chart_of_account ON chart_of_account.account_code = cash_book.account_code " . " WHERE cb_date = '{$cash_book_date}' " . "ORDER BY cb_id"; $cash_book_search = mysql_query($sql); while ($row = mysql_fetch_array($cash_book_search)) { $arr = array(); $arr['cash_book_id'] = $row[0]; $arr['account_code_hidden'] = $row[1]; $arr['cash_book_type'] = $row[2]; $arr['cash_book_amount'] = $row[3]; $arr['cash_book_remarks'] = $row[4]; $arr['account_code'] = $row[5]; $cash_book_detail[] = $arr; } $cb_balance = '<div id="cb_balance"'; $cb = getValue("IFNULL(sum(debit_amount - credit_amount), 0)", "voucher_detail", "account_code = (SELECT code FROM sys_codes WHERE description = 'CASH ACCOUNT' AND company_ref_id = {$selected_company_id})"); if ($cb < 0) { $cb_balance .= ' style="color:red;"'; } $cb_balance .= '>' . number_format($cb, 2, ".", ",") . '</div>'; $cb_balance_todate = '<div id="cb_balance"'; $cb = getValue("IFNULL(sum(vd.debit_amount - vd.credit_amount), 0)", "voucher_detail vd INNER JOIN voucher_master vm ON vm.voucher_id = vd.voucher_id", "vd.account_code = (SELECT code FROM sys_codes WHERE description = 'CASH ACCOUNT' AND company_ref_id = {$selected_company_id}) AND TO_DAYS(vm.voucher_date) <= TO_DAYS('{$cash_book_date}')");
$godowns = array(); $sql = "SELECT g_name FROM godown WHERE company_ref_id = {$_SESSION['company_id']}"; $result = $mysqli->query($sql); while ($row = $result->fetch_assoc()) { $godowns[] = $row['g_name']; } $isStartDate = getIVal("TO_DAYS(start_date) < TO_DAYS('" . my_sql_date($_GET['date1']) . "')", "sys_financial_year", "company_ref_id = {$_SESSION['company_id']} AND is_active = 'Yes'"); $oOper = "<"; if ($isStartDate == '0') { $oOper = "<="; } $swhere = ""; $swhere .= " AND TO_DAYS(s.trans_date) >= TO_DAYS('" . my_sql_date($_GET['date1']) . "')\n"; $swhere .= " AND TO_DAYS(s.trans_date) <= TO_DAYS('" . my_sql_date($_GET['date2']) . "')\n"; $owhere = " AND s.trans_date >= (SELECT y.start_date FROM sys_financial_year y WHERE y.company_ref_id = s.company_ref_id AND y.is_active = 'Yes')\n"; $owhere .= " AND TO_DAYS(s.trans_date) {$oOper} TO_DAYS('" . my_sql_date($_GET['date1']) . "')\n"; $where = ""; /* -- Remark By Khurram Adding Multiply Selection of vendor in below block of code. if (strlen($_GET['vendor_hidden']) > 0) { $where .= " AND s.party_code = $_GET[vendor_hidden]\n"; } */ /* Remark By Khurram Adding Multiply Selection of vendor in below block of code. */ if (isset($_GET['supplier']) && count($_GET['supplier']) > 0) { if (count($_GET['supplier']) == 1 && $_GET['supplier'][0] == "#ALL#") { //Select All was selected. } else { $where .= " AND p.party_code IN ('" . join("','", $_GET['supplier']) . "')\n"; } }
$accordion_active = "1"; if ($_SERVER['REQUEST_METHOD'] == 'POST' || isset($_GET['ipp'])) { $s_sale_id = ""; if (isset($_POST['s_sale_id']) && strlen($_POST['s_sale_id']) > 0) { $s_sale_id = "sm.sale_master_id = {$_POST['s_sale_id']}"; } else { if (isset($_GET['s_sale_id']) && strlen($_GET['s_sale_id']) > 0) { $s_sale_id = "sm.sale_master_id = {$_GET['s_sale_id']}"; } } $s_sdate = ""; if (isset($_POST['s_sale_date']) && strlen($_POST['s_sale_date']) > 0) { $s_sdate = "sm.sale_date = '" . my_sql_date($_POST['s_sale_date']) . "'"; } else { if (isset($_GET['s_sale_date']) && strlen($_GET['s_sale_date']) > 0) { $s_sdate = "sm.sale_date = '" . my_sql_date($_GET['s_sale_date']) . "'"; } } $s_supplier = ""; if (isset($_POST['s_supplier_hidden']) && strlen($_POST['s_supplier_hidden']) > 0) { $s_supplier = "sm.party_code = '{$_POST['s_supplier_hidden']}'"; } else { if (isset($_GET['s_supplier_hidden']) && strlen($_GET['s_supplier_hidden']) > 0) { $s_supplier = "sm.party_code = '{$_GET['s_supplier_hidden']}'"; } } $s_billno = ""; if (isset($_POST['s_billno']) && strlen($_POST['s_billno']) > 0) { $s_billno = "sm.billno = '{$_POST['s_billno']}'"; } else { if (isset($_GET['s_billno']) && strlen($_GET['s_billno']) > 0) {
if (isset($_POST['s_voucher_id']) && strlen($_POST['s_voucher_id']) > 0) { $vid = "vm.seq = '{$_POST['s_voucher_id']})'"; $search['s_voucher_id'] = $_POST['s_voucher_id']; } else { if (isset($_GET['s_voucher_id']) && strlen($_GET['s_voucher_id']) > 0) { $vid = "vm.seq = '{$_GET['s_voucher_id']}'"; $search['s_voucher_id'] = $_GET['s_voucher_id']; } } $vdate = ""; if (isset($_POST['voucher_date']) && strlen($_POST['voucher_date']) > 0) { $vdate = "vm.voucher_date = '" . my_sql_date($_POST['voucher_date']) . "'"; $search['voucher_date'] = $_POST['voucher_date']; } else { if (isset($_GET['voucher_date']) && strlen($_GET['voucher_date']) > 0) { $vdate = "vm.voucher_date = '" . my_sql_date($_GET['voucher_date']) . "'"; $search['voucher_date'] = $_GET['voucher_date']; } } $vtype = ""; if (isset($_POST['voucher_type']) && strlen($_POST['voucher_type']) > 0) { $vtype = "vm.voucher_type = '{$_POST['voucher_type']}'"; $search['voucher_type'] = $_POST['voucher_type']; } else { if (isset($_GET['voucher_type']) && strlen($_GET['voucher_type']) > 0) { $vtype = "vm.voucher_type = '{$_GET['voucher_type']}'"; $search['voucher_type'] = $_GET['voucher_type']; } } $vaccount = ""; if (isset($_POST['account_hidden']) && strlen($_POST['account_hidden']) > 0 && strlen($_POST['account']) > 0) {
if (strlen($orderby) < 1) { $orderby = "pm.shipping_date"; } $sql = "" . "SELECT DISTINCT " . " pm.p_id, " . " date_format(pm.shipping_date, '%d-%b-%Y'), " . " date_format(pm.receive_date, '%d-%b-%Y'), " . " p.party_name, " . " pm.billno " . " FROM purchase_master pm " . " LEFT OUTER JOIN party p ON p.party_code = pm.party_code " . " WHERE pm.company_ref_id = {$selected_company_id} " . " {$whereAnd} " . "ORDER BY {$orderby} {$sortorder}"; $count = mysql_query("SELECT count(*) FROM ({$sql}) x") or die(mysql_error()); $count = mysql_fetch_array($count); $pages->items_total = $count[0]; $pages->mid_range = 9; $pages->paginate(); $purchase_search = mysql_query($sql . $pages->limit); $accordion_active = "0"; } else { if (isset($_POST['searchBy']) && $_POST['searchBy'] == 'order' || isset($_GET['searchBy']) && $_GET['searchBy'] == 'order') { $vdate = ""; if (isset($_POST['order_date']) && strlen($_POST['order_date']) > 0) { $vdate = "om.o_date = '" . my_sql_date($_POST['order_date']) . "'"; } else { if (isset($_GET['order_date']) && strlen($_GET['order_date']) > 0) { $vdate = "om.o_date = '{$_GET['order_date']}'"; } } $vparty = ""; if (isset($_POST['order_party1_hidden']) && strlen($_POST['order_party1_hidden']) > 0) { $vparty = "p.party_code = '{$_POST['order_party1_hidden']}'"; } else { if (isset($_GET['order_party1_hidden']) && strlen($_GET['order_party1_hidden']) > 0) { $vparty = "p.party_code = '{$_GET['order_party1_hidden']}'"; } } $whereAnd = ""; $separator = "";
foreach ($arr_account as $arr) { $account = $arr[0]; $account_hidden = $arr[1]; $sql = "" . "SELECT IFNULL(opening_balance,0) + IFNULL(\n" . " (\n" . " SELECT sum(vd.debit_amount - vd.credit_amount) amt\n" . " FROM voucher_detail vd\n" . " INNER JOIN voucher_master vm ON vm.voucher_id = vd.voucher_id\n" . " WHERE vd.account_code = p.account_code\n" . " AND TO_DAYS(vm.voucher_date) >= TO_DAYS('{$dss}')\n" . " AND TO_DAYS(vm.voucher_date) < TO_DAYS('{$ds}')\n" . " ),0) opening_balance\n" . " FROM party p\n" . " WHERE p.account_code = {$account_hidden}"; //dump($sql, "Opening Balance"); $result = $mysqli->query($sql); $row = $result->fetch_assoc(); $aob = $row['opening_balance'] * 1; $result->close(); $title = "Account Ledger"; $sql = "" . " SELECT z.account_code, \n" . " q.account_name, \n" . " DATE_FORMAT(voucher_date, '%d-%b-%y') AS voucher_date, \n" . " CASE t.voucher_type \n" . " WHEN 'PV' THEN CONCAT_WS(' - ', 'Purchase', (select billno from purchase_master where voucher_id = t.voucher_id), t.voucher_remarks) \n" . " WHEN 'SV' THEN CONCAT_WS(' - ', 'Sale', (select billno from sales_master e where e.voucher_id = t.voucher_id)) \n" . " WHEN 'SR' THEN CONCAT_WS(' - ', 'Sale Return', (select billno from sales_return_master e where e.voucher_id = t.voucher_id)) \n" . " WHEN 'PR' THEN CONCAT_WS(' - ', 'Purchase Return', (select billno from purchase_return_master o where o.voucher_id = t.voucher_id)) \n" . " WHEN 'CR' THEN CONCAT_WS(' - ', 'Payment Receive', z.remarks) \n" . " WHEN 'CP' THEN CONCAT_WS(' - ', 'Payment Paid', z.remarks) \n" . " WHEN 'BV' THEN CONCAT_WS(' - ', 'Bank Payment', z.remarks) \n" . " WHEN 'JV' THEN CONCAT_WS(' - ', 'Journal Voucher', z.remarks) \n" . " END comments, \n" . " z.debit_amount debit, \n" . " z.credit_amount credit, \n" . " CASE t.voucher_type \n" . " WHEN 'PV' THEN (select p_id from purchase_master where voucher_id = t.voucher_id) \n" . " WHEN 'SV' THEN (select sale_master_id from sales_master e where e.voucher_id = t.voucher_id) \n" . " WHEN 'SR' THEN (select rsale_master_id from sales_return_master e where e.voucher_id = t.voucher_id) \n" . " WHEN 'PR' THEN (select pur_ret_master_id from purchase_return_master o where o.voucher_id = t.voucher_id) \n" . " ELSE t.voucher_id \n" . " END sale_master_id \n" . " FROM voucher_detail z \n" . " INNER JOIN voucher_master t on z.voucher_id = t.voucher_id \n" . " INNER JOIN chart_of_account q on q.account_code = z.account_code \n" . " INNER JOIN sys_financial_year a on a.year_id = t.year_id \n" . " LEFT OUTER JOIN sales_master sm on sm.voucher_id = t.voucher_id \n" . " WHERE z.account_code = {$account_hidden} \n" . " AND a.is_active = 'Yes' \n"; if (strlen($_GET['date_start']) > 0) { $sql .= " AND TO_DAYS(voucher_date) >= TO_DAYS('" . my_sql_date($_GET['date_start']) . "') \n"; } if (strlen($_GET['date_end']) > 0) { $sql .= " AND TO_DAYS(voucher_date) <= TO_DAYS('" . my_sql_date($_GET['date_end']) . "') \n"; } if (strlen($_GET['date_start']) + strlen($_GET['date_end']) < 1) { $dss = date('Y') . "-01-01"; $sql .= " AND TO_DAYS(voucher_date) >= TO_DAYS('{$dss}') \n"; } $sql .= "ORDER BY z.account_code, t.voucher_date "; //dump($sql, "Query"); $result = $mysqli->query($sql); $data = array(); $bal = $aob; $dr = 0; $cr = 0; while ($row = $result->fetch_assoc()) { $d = $row['debit'] * 1; $c = $row['credit'] * 1;
require_once '../includes/connecti.php'; /** @var $mysqli mysqli */ require_once '../includes/funcs.inc.php'; /** @var $result mysqli_result */ $_title = getReportTitle(); if (isset($_GET['report'])) { updateDate('rpt_sales_vendor_party_start', $_GET['date_start']); updateDate('rpt_sales_vendor_party_end', $_GET['date_end']); $title = $_title; $partyName = getIVal("party_name", "party", "party_code = {$_GET['party_hidden']}"); $sql = "" . "SELECT sm.seq, sm.billno, im.cno, concat(IFNULL(CONCAT(manufacturer_no, ' -- '), ''), description) as description,\n" . " sd.sales_qty, sd.sales_rate, im.circular_rate, im.fix_rate, (sd.sales_qty)*(sd.sales_rate-IFNULL(im.fix_rate,0)) diff\n" . " FROM sales_master sm\n" . " INNER JOIN sales_detail sd ON sd.sale_master_id = sm.sale_master_id\n" . " INNER JOIN item_master im ON im.i_code = sd.i_code\n" . " WHERE sm.party_code = {$_GET['party_hidden']}\n"; if (strlen($_GET['date_start']) > 0) { $sql .= " AND TO_DAYS(sm.sale_date) >= TO_DAYS('" . my_sql_date($_GET['date_start']) . "')\n"; } if (strlen($_GET['date_end']) > 0) { $sql .= " AND TO_DAYS(sm.sale_date) <= TO_DAYS('" . my_sql_date($_GET['date_end']) . "')\n"; } if (strlen($_GET['date_start']) + strlen($_GET['date_end']) < 1) { $d = date('Y') . "-01-01"; $sql .= " AND TO_DAYS(sm.sale_date) >= TO_DAYS('{$d}')\n"; } $result = $mysqli->query($sql); $data = array(); $tdata = array('title' => 'Total', 'rate' => (double) 0); while ($row = $result->fetch_assoc()) { $row['sales_qty'] = $row['sales_qty'] * 1; $row['sales_rate'] = $row['sales_rate'] * 1; $row['circular_rate'] = $row['circular_rate'] * 1; $row['fix_rate'] = $row['fix_rate'] * 1; $row['diff'] = $row['diff'] * 1; $tdata['rate'] += $row['diff'];
$sum = getValue("sum(pr_qty * pr_rate) amt", "purchase_return_detail", "pur_ret_master_id = {$parent_key}"); $voucher_id = getValue("voucher_id", "purchase_return_master", "pur_ret_master_id = {$parent_key}"); dd_mysql_query("UPDATE voucher_detail SET debit_amount = {$sum} WHERE voucher_id = {$voucher_id} AND debit_amount > 0"); dd_mysql_query("UPDATE voucher_detail SET credit_amount = {$sum} WHERE voucher_id = {$voucher_id} AND credit_amount > 0"); if ($_POST['purchase_return_type'] == 'C') { $v_cash_acc_id = getValue("code", "sys_codes", "description = 'CASH ACCOUNT'"); $v_ch_pur_acc_id = getValue("code", "sys_codes", "description = 'PURCHASE RETURN'"); dd_mysql_query("UPDATE voucher_detail SET account_code = {$v_cash_acc_id}, remarks = '0' WHERE voucher_id = {$voucher_id} AND debit_amount > 0"); dd_mysql_query("UPDATE voucher_detail SET account_code = {$v_ch_pur_acc_id}, remarks = '1' WHERE voucher_id = {$voucher_id} AND credit_amount > 0"); } else { $v_cr_pur_acc_id = getValue("code", "sys_codes", "description = 'CREDIT PURCHASE'"); $v_party_acc_code = getValue("account_code", "party", "party_code = {$_POST['purchase_return_party_hidden']}"); dd_mysql_query("UPDATE voucher_detail SET account_code = {$v_cr_pur_acc_id}, remarks = '2-1' WHERE voucher_id = {$voucher_id} AND debit_amount > 0"); dd_mysql_query("UPDATE voucher_detail SET account_code = {$v_party_acc_code}, remarks = '3-1' WHERE voucher_id = {$voucher_id} AND credit_amount > 0"); } } else { $sum = getValue("sum(pr_qty * pr_rate) amt", "purchase_return_detail", "pur_ret_master_id = {$parent_key}"); $v_ch_pur_acc_id = getValue("code", "sys_codes", "description = 'PURCHASE RETURN'"); $v_cash_acc_id = getValue("code", "sys_codes", "description = 'CASH ACCOUNT'"); $v_party_acc_code = getValue("account_code", "party", "party_code = {$_POST['purchase_return_party_hidden']}"); require_once '../includes/connecti.php'; if ($_POST['purchase_return_type'] == 'C') { createVoucher($selected_company_id, $year_id, $v_cash_acc_id, $v_ch_pur_acc_id, $sum, $_POST['purchase_return_remarks'], $parent_key, 'PR', my_sql_date($_POST['purchase_return_date'])); } else { createVoucher($selected_company_id, $year_id, $v_ch_pur_acc_id, $v_party_acc_code, $sum, $_POST['purchase_return_remarks'], $parent_key, 'PR', my_sql_date($_POST['purchase_return_date'])); } $mysqli->close(); } $url = "../transaction/purchase_return.php?purchase_return_id={$parent_key}"; //echo "<a href='$url'>$url</a><br/>"; header("Location: {$url}");
<?php session_start(); $conn = oci_connect($db_user, $db_pass, $db_host); include '../includes/connecti.php'; /** @var $mysqli mysqli */ /** @var $result mysqli_result*/ require_once '../includes/funcs.inc.php'; $selected_company_id = $_SESSION['company_id']; $year_id = getIVal("year_id", "sys_financial_year", "company_ref_id = {$selected_company_id} AND is_active = 'Yes'"); $cheque_id = 0; $value_date = my_sql_date($_SESSION['dates']['cheque_voucher']); $voucher_detail = array(); $banks = array(); if (isset($_GET['value_date'])) { $value_date = my_sql_date($_GET['value_date']); } if (isset($_GET['cheque_id'])) { $cheque_id = $_GET['cheque_id']; } if ($cheque_id != 0) { $tvoucher_detail = array(); $sql = "" . "SELECT vd.voucher_detail_id, vd.account_ref_id, coa.account_name, vd.credit_amount, vd.remarks, vm.voucher_cheque, vm.voucher_id\n" . " FROM voucher_detail vd\n" . " INNER JOIN voucher_master vm ON vm.voucher_id = vd.voucher_id\n" . " INNER JOIN chart_of_account coa ON coa.account_ref_id = vd.account_ref_id\n" . " WHERE vd.voucher_id IN (\n" . " SELECT vm.voucher_id\n" . " FROM voucher_master vm\n" . " INNER JOIN voucher_detail vd ON vd.voucher_id = vm.voucher_id\n" . " WHERE vd.account_ref_id = {$cheque_id}\n" . " AND vm.voucher_date = '{$value_date}'\n" . " AND vm.year_id = {$year_id}\n" . " )\n" . " AND vd.account_ref_id <> {$cheque_id}\n" . " AND vm.voucher_type = 'CJV'\n"; $result = $mysqli->query($sql); while ($row = $result->fetch_assoc()) { $row['credit_amount'] = $row['credit_amount'] * 1; $row['bank_detail'] = array("voucher_id" => "", "account_ref_id" => ""); $tvoucher_detail[] = $row; } foreach ($tvoucher_detail as $vd) { $sql = "" . "SELECT vm.voucher_id, vd.account_ref_id\n" . " FROM voucher_master vm\n" . " INNER JOIN voucher_detail vd ON vd.voucher_id = vm.voucher_id\n" . " WHERE vm.voucher_remarks = '{$vd['voucher_id']}'\n" . " AND vm.voucher_type = 'CBV'\n" . " AND vd.credit_amount = 0\n";
while ($row = $result->fetch_assoc()) { $aob = getOpeningBalance($row['account_ref_id'], $_GET['date_start']); $openingBalance += $aob; } $sdata[] = array('account_name' => $accounts_class . " OPEN BAL", 'amount' => $openingBalance); $runningBalance = (double) 0; $sql = "" . "SELECT IFNULL(SUM(vd.debit_amount), 0) bal\n" . " FROM voucher_detail vd\n" . " INNER JOIN voucher_master vm ON vm.voucher_id = vd.voucher_id\n" . " WHERE TO_DAYS(vm.voucher_date) >= TO_DAYS('" . my_sql_date($_GET['date_start']) . "')\n" . " AND TO_DAYS(vm.voucher_date) <= TO_DAYS('" . my_sql_date($_GET['date_end']) . "')\n" . " AND vd.account_ref_id IN (SELECT coa.account_ref_id FROM chart_of_account coa WHERE coa.account_class = '{$accounts_class}' AND coa.company_ref_id = vm.company_ref_id)\n" . " AND vm.company_ref_id = {$_SESSION['company_id']}\n"; $result = $mysqli->query($sql); if ($row = $result->fetch_assoc()) { $runningBalance += $row['bal'] * 1; } $sdata[] = array('account_name' => $accounts_class, 'amount' => $runningBalance); $sdata[] = array('account_name' => 'Total Available Cash', 'amount' => $openingBalance + $runningBalance); $sdata[] = array('account_name' => '', 'amount' => ''); $cashOut = (double) 0; $sql = "" . "SELECT c.account_name, IFNULL(SUM(v.debit_amount),0) bal\n" . " FROM voucher_detail v\n" . " INNER JOIN chart_of_account c ON c.account_ref_id = v.account_ref_id\n" . " INNER JOIN sys_codes sc ON sc.sys_codes_ref_id = c.account_category\n" . " WHERE v.voucher_id IN (\n" . " SELECT vd.voucher_id\n" . " FROM voucher_detail vd\n" . " INNER JOIN voucher_master vm ON vm.voucher_id = vd.voucher_id\n" . " INNER JOIN chart_of_account coa ON coa.account_ref_id = vd.account_ref_id\n" . " WHERE TO_DAYS(vm.voucher_date) >= TO_DAYS('" . my_sql_date($_GET['date_start']) . "')\n" . " AND TO_DAYS(vm.voucher_date) <= TO_DAYS('" . my_sql_date($_GET['date_end']) . "')\n" . " AND coa.account_class = '{$accounts_class}'\n" . " AND vm.company_ref_id = {$_SESSION['company_id']}\n" . " AND vd.debit_amount = 0\n" . " )\n" . " AND v.credit_amount = 0\n" . "GROUP BY c.account_ref_id\n" . "ORDER BY c.account_name\n"; $result = $mysqli->query($sql); while ($row = $result->fetch_assoc()) { $bal = $row['bal'] * 1; $cashOut += $bal; $sdata[] = array('account_name' => $row['account_name'], 'amount' => $bal); } $data[] = $sdata; $sdata = array(); $sdata[] = array('account_name' => 'Total Cash Out', 'amount' => $cashOut); /*$sdata[] = array( 'account_name' => '', 'amount' => '' );*/ $sdata[] = array('account_name' => 'Current Available Cash', 'amount' => $openingBalance + $runningBalance - $cashOut); $tdata[] = $sdata;
session_start(); $selected_company_id = $_SESSION['company_id']; ob_start(); $data = array(); dump($_POST); $account_id = $_POST['account_id']; $account_name = getIVal("account_name", "chart_of_account", "account_ref_id = {$account_id}", true); $year_id = getIVal("year_id", "sys_financial_year", "company_ref_id = {$selected_company_id} AND is_active = 'Yes'", true); $counter = $_POST['counter'] * 1; for ($i = 1; $i <= $counter; $i++) { if (strlen($_POST["account_" . $i]) > 0) { $cr_acc = $_POST["account_" . $i . "_hidden"]; $amt = stripFormat($_POST["amt_" . $i]) * 1; $remarks = $_POST["account_" . $i] . " " . $_POST["remarks_" . $i]; $cheque = $_POST["cheque_" . $i]; $vdate = my_sql_date($_POST["date_" . $i]); $first_colval = "voucher_date = '{$vdate}', voucher_remarks = '{$remarks}', voucher_type = 'JV', voucher_cheque = '{$cheque}'"; $seq = getSequenceNextVal('voucher_master', $selected_company_id); $first_part = "INSERT INTO voucher_master SET company_ref_id = {$selected_company_id}, year_id = {$year_id}, seq = {$seq}, voucher_by = {$_SESSION['user_id']}, "; $first_tab_ins = $first_part . $first_colval; echo $first_tab_ins . "<br/>"; $mysqli->query($first_tab_ins); $parent_key = $mysqli->insert_id; $sql = " INSERT INTO voucher_detail SET voucher_id = {$parent_key}, account_ref_id = '{$account_id}', debit_amount = '{$amt}', credit_amount = '0', remarks = concat('{$account_name}',' ','{$remarks}')"; echo $sql . "<br/>"; $mysqli->query($sql); $sql = " INSERT INTO voucher_detail SET voucher_id = {$parent_key}, account_ref_id = '{$cr_acc}', debit_amount = '0', credit_amount = '{$amt}', remarks = concat('{$account_name}',' ','{$remarks}')"; echo $sql . "<br/>"; $mysqli->query($sql); $voucher_aud_id = di_mysql_query("INSERT INTO voucher_master_aud SELECT NULL, vm.*, 'add', {$_SESSION['user_id']}, now() FROM voucher_master vm WHERE vm.voucher_id = {$parent_key}", true); di_mysql_query("INSERT INTO voucher_detail_aud SELECT NULL, vd.*, {$voucher_aud_id} FROM voucher_detail vd WHERE vd.voucher_id = {$parent_key}");
$dr_acc = getValue("code", "sys_codes", "description = 'Purchase Return' AND company_ref_id = {$selected_company_id}", true); $cr_acc = getValue("code", "sys_codes", "description = 'Cash in Hand' AND company_ref_id = {$selected_company_id}", true); } else { //Credit Purchase Return //Debit - Party //Credit - Purchase Return $dr_acc = getValue("account_ref_id", "party", "party_code = {$_POST['purchase_return_party_hidden']}", true); $cr_acc = getValue("code", "sys_codes", "description = 'Purchase Return' AND company_ref_id = {$selected_company_id}", true); } if ($_POST['oper'] == 'update') { $voucher_id = getValue("voucher_id", "purchase_return_master", "pur_ret_master_id = {$parent_key}", true); d_mysql_query("UPDATE voucher_detail SET debit_amount = {$sum}, account_ref_id = {$dr_acc} WHERE voucher_id = {$voucher_id} AND remarks = 'Debit'"); d_mysql_query("UPDATE voucher_detail SET credit_amount = {$sum}, account_ref_id = {$cr_acc} WHERE voucher_id = {$voucher_id} AND remarks = 'Credit'"); d_mysql_query("UPDATE voucher_master SET voucher_date = '" . my_sql_date($_POST['purchase_return_date']) . "', voucher_remarks = '{$_POST['purchase_return_remarks']}' WHERE voucher_id = {$voucher_id}"); } else { createVoucher($selected_company_id, $year_id, $dr_acc, $cr_acc, $sum, $_POST['purchase_return_remarks'], $parent_key, 'PR', my_sql_date($_POST['purchase_return_date']), $_SESSION['user_id']); $mysqli->close(); } $voucher_id = getValue("voucher_id", "purchase_return_master", "pur_ret_master_id = {$parent_key}", true); d_mysql_query("UPDATE voucher_detail SET credit_amount = {$purchase} WHERE voucher_id = {$voucher_id} AND remarks = 'Credit'"); //reset all taxes d_mysql_query("UPDATE voucher_detail SET debit_amount = 0 WHERE voucher_id = {$voucher_id} AND remarks = 'Tax'"); if ($tax > 0) { $taxes = array(); $counter = $_POST['counter'] * 1; for ($i = 1; $i <= $counter; $i++) { $i_code = $_POST["item_" . $i . "_hidden"]; if (strlen($i_code) < 1) { continue; } $purchase_value = stripFormat($_POST["inp2_{$i}"]) * stripFormat($_POST["inp3_{$i}"]);
} $pdf = new PDF(); $pdf->AliasNbPages(); $pdf->SetAuthor("*****@*****.**"); $pdf->SetSubject("Purchase Return"); $pdf->SetTitle("Purchase Return"); $pdf->SetCreator("Imran Zahid"); $prop = array('HeaderColor' => array(0, 0, 0), 'HeaderTextColor' => array(255, 255, 255), 'color1' => array(217, 217, 217), 'color2' => array(255, 255, 255), 'textcolor1' => array(0, 0, 0), 'textcolor2' => array(0, 0, 0), 'padding' => 2, 'font' => array('Arial', '', 8), 'thfont' => array('Arial', 'B', 10)); $title = $_title; $pdf->AddPage(); $where = ""; if (strlen($_GET['date_start']) > 0) { $where .= " AND (TO_DAYS(pm.shipping_date) >= TO_DAYS('" . my_sql_date($_GET['date_start']) . "') OR TO_DAYS(pm.receive_date) >= TO_DAYS('" . my_sql_date($_GET['date_start']) . "'))\n"; } if (strlen($_GET['date_end']) > 0) { $where .= " AND (TO_DAYS(pm.shipping_date) <= TO_DAYS('" . my_sql_date($_GET['date_end']) . "') OR TO_DAYS(pm.receive_date) <= TO_DAYS('" . my_sql_date($_GET['date_end']) . "'))\n"; } if (strlen($_GET['purchase_id']) > 0) { $where .= " AND pm.p_id = '{$_GET['purchase_id']}'\n"; } if (strlen($_GET['supplier']) > 0 && strlen($_GET['supplier_hidden']) > 0) { $where .= " AND pm.party_code = " . $_GET['supplier_hidden'] . "\n"; } $sql = "" . "SELECT pm.p_id,\n" . " pm.shipping_date,\n" . " pm.receive_date,\n" . " p.party_name,\n" . " g.g_name,\n" . " pm.billno\n" . " FROM purchase_master pm\n" . " LEFT OUTER JOIN party p ON p.party_code = pm.party_code\n" . " LEFT OUTER JOIN godown g ON g.g_code = pm.godown_code\n" . " WHERE pm.company_ref_id = {$_SESSION['company_id']}\n" . $where . "ORDER BY pm.p_id"; //dump($sql); $result = $mysqli->query($sql); $ddata = array(); while ($row = $result->fetch_assoc()) { $ddata[] = $row; } $result->close();
require_once 'paginator.class.php'; $pages = new Paginator(); $accordion_active = "1"; if ($_SERVER['REQUEST_METHOD'] == 'POST' || isset($_GET['ipp'])) { $year_id = getValue("year_id", "sys_financial_year", "company_ref_id = {$selected_company_id} AND is_active = 'Yes'"); $s_sid = ""; if (isset($_POST['s_purchase_id']) && strlen($_POST['s_purchase_id']) > 0) { $s_sid = "prm.seq = {$_POST['s_purchase_id']}"; } else { if (isset($_GET['s_purchase_id']) && strlen($_GET['s_purchase_id']) > 0) { $s_sid = "prm.seq = '{$_GET['s_purchase_id']}'"; } } $s_rdate = ""; if (isset($_POST['s_return_date']) && strlen($_POST['s_return_date']) > 0) { $s_rdate = "prm.pur_ret_date = '" . my_sql_date($_POST['s_return_date']) . "'"; } else { if (isset($_GET['s_return_date']) && strlen($_GET['s_return_date']) > 0) { $s_rdate = "prm.pur_ret_date = '{$_GET['s_return_date']}'"; } } $s_supplier = ""; if (isset($_POST['s_supplier_hidden']) && strlen($_POST['s_supplier_hidden']) > 0) { $s_supplier = "prm.party_code = '{$_POST['s_supplier_hidden']}'"; } else { if (isset($_GET['s_supplier_hidden']) && strlen($_GET['s_supplier_hidden']) > 0) { $s_supplier = "prm.party_code = '{$_GET['s_supplier_hidden']}'"; } } $s_billno = ""; if (isset($_POST['s_billno']) && strlen($_POST['s_billno']) > 0) {
$where .= " AND d.i_code IN ('" . join("','", $_GET['item']) . "')\n"; } } if (isset($_GET['party']) && count($_GET['party']) > 0) { if (count($_GET['party']) == 1 && $_GET['party'][0] == "#ALL#") { //Select All was selected, filter result based on city then $where .= " AND d.party_code IN (SELECT party_code FROM party WHERE party_city = '{$_GET['city']}')\n"; } else { $where .= " AND d.party_code IN ('" . join("','", $_GET['party']) . "')\n"; } } if (isset($_GET['nodist']) && $_GET['nodist'] == 'Y') { $where .= " AND d.distributor_id IS NULL\n"; } $data = array(); $sql = "" . "SELECT d.party_name, d.vendor, d.cat_name,\n" . " d.description, sum(d.cs) cs, sum(d.pc) pc\n" . " FROM vu_daily_sales d\n" . " WHERE d.company_ref_id = {$_SESSION['company_id']}\n" . " AND cat_name IS NOT NULL\n" . " AND TO_DAYS(d.sale_date) >= TO_DAYS('" . my_sql_date($_GET['date_start']) . "')\n" . $where . "GROUP BY d.party_name, d.vendor, d.cat_name, d.description\n" . "ORDER BY d.party_name, d.vendor, d.cat_name, d.description"; //dump($sql); $result = $mysqli->query($sql); $data = array(); $totals = array(); $totals['cs'] = (double) 0; $totals['pc'] = (double) 0; while ($row = $result->fetch_assoc()) { $totals['cs'] += (double) $row['cs']; $totals['pc'] += (double) $row['pc']; $row['cs'] = number_format($row['cs']); $row['pc'] = number_format($row['pc']); $data[] = $row; } $totals['cs'] = number_format($totals['cs']); $totals['pc'] = number_format($totals['pc']);
$accordion_active = "1"; if ($_SERVER['REQUEST_METHOD'] == 'POST' || isset($_GET['ipp'])) { $s_delivery_id = ""; if (isset($_POST['s_delivery_id']) && strlen($_POST['s_delivery_id']) > 0) { $s_delivery_id = "dm.seq = {$_POST['s_delivery_id']}"; } else { if (isset($_GET['s_delivery_id']) && strlen($_GET['s_delivery_id']) > 0) { $s_delivery_id = "dm.seq = {$_GET['s_delivery_id']}"; } } $s_sdate = ""; if (isset($_POST['s_delivery_date']) && strlen($_POST['s_delivery_date']) > 0) { $s_sdate = "dm.delivery_date = '" . my_sql_date($_POST['s_delivery_date']) . "'"; } else { if (isset($_GET['s_delivery_date']) && strlen($_GET['s_delivery_date']) > 0) { $s_sdate = "dm.delivery_date = '" . my_sql_date($_GET['s_delivery_date']) . "'"; } } $s_supplier = ""; if (isset($_POST['s_supplier_hidden']) && strlen($_POST['s_supplier_hidden']) > 0) { $s_supplier = "dm.party_code = '{$_POST['s_supplier_hidden']}'"; } else { if (isset($_GET['s_supplier_hidden']) && strlen($_GET['s_supplier_hidden']) > 0) { $s_supplier = "dm.party_code = '{$_GET['s_supplier_hidden']}'"; } } $s_billno = ""; if (isset($_POST['s_billno']) && strlen($_POST['s_billno']) > 0) { $s_billno = "dm.billno = '{$_POST['s_billno']}'"; } else { if (isset($_GET['s_billno']) && strlen($_GET['s_billno']) > 0) {