} else {
$invoice = $d['sale_master_id'];
$partyName = $d['party_name'];
$partyCity = $d['party_city'];
$billno = $d['billno'];
$date = $d['sale_date_fmt'];
$billteNo = $d['billte_no'];
$transporter = $d['transporter_name'];
$address = $d['party_address'];
$voucher = $d['voucher_id'];
$godown = $d['g_name'];
$packing = $d['packing_charges'] * 1;
$aob = 0;
if ($partyName != 'Cash') {
$aob1 = getIVal("IFNULL(opening_balance, 0)", "account_opening_balance", "account_ref_id = {$d['account_ref_id']} AND year_id = {$year_id}") * 1;
$aob2 = getIVal("IFNULL(SUM(vd.debit_amount - vd.credit_amount),0) bal", "voucher_master vm INNER JOIN voucher_detail vd ON vd.voucher_id = vm.voucher_id", "vd.account_ref_id = {$d['account_ref_id']} AND vm.year_id = {$year_id} AND vm.voucher_id < {$voucher}") * 1;
$aob = $aob1 + $aob2;
}
$sql = "SELECT * FROM vu_saledetailsreport WHERE sale_master_id = {$d['sale_master_id']}";
$result = $mysqli->query($sql);
$ddata = array();
$amt = 0;
$dis = 0;
$net = 0;
$sno = 1;
while ($row = $result->fetch_assoc()) {
$row['sno'] = $sno++;
$amt += $row['amount'] * 1;
$dis += $row['discount_value'] * 1;
$net += $row['net'] * 1;
$row['raw_qty'] = $row['raw_qty'] * 1;
buttonImageOnly: true,
constrainInput: true,
disableInput: true,
minDate: <?php
echo getIVal("minDate", "vu_minMaxDate", "company_ref_id = {$_SESSION['company_id']} AND is_active = 'Yes'");
?>
});
$("#date2").datepicker({
showOn: 'button',
buttonImage: '../images/calendar.gif',
buttonImageOnly: true,
constrainInput: true,
disableInput: true,
minDate: <?php
echo getIVal("minDate", "vu_minMaxDate", "company_ref_id = {$_SESSION['company_id']} AND is_active = 'Yes'");
?>
});
});
$(function() {
populate('Godown', 'godown');
$('#category').change(function() {
$('#item').html('');
getPopulateSelect($(this).val());
});
$('#chkDetail').click(function() {
$('#vendor').attr('disabled', 'disabled');
$('#category').attr('disabled', '');
<?php
session_start();
require_once '../includes/connecti.php';
/** @var $mysqli mysqli */
/** @var $result mysqli_result */
require_once '../includes/funcs.inc.php';
$_title = getReportTitle();
if (isset($_GET['report'])) {
$vehicle = "";
$where = "";
if (strlen($_GET['vehicle']) > 0) {
$where = " AND im.vehicle_id = {$_GET['vehicle']}\n";
}
$vendor = getIVal("CASE WHEN p.party_name = 'DEPO AUTO PARTS' THEN 'DEPO' WHEN p.party_name = 'TIK ZENITH TROOP' THEN 'TIK' WHEN p.party_name = 'FORTUNE PARTS INDUSTRY' THEN 'FPI' ELSE p.party_name END party_name", "party p", "party_code = {$_GET['party']}");
$title = $_title;
$sql = "" . "SELECT im.i_code, im.cno, im.description, im.model, im.oem, IFNULL(im.circular_rate,0) rate, CONCAT(iu.description, ' RATE') unit,\n" . " im.fix_rate, im.vehicle_id, s.code vehicle\n" . " FROM item_master im\n" . " INNER JOIN item_category ic ON ic.cat_id = im.cat_id\n" . " INNER JOIN item_unit iu ON iu.unit_id = ic.unit_id\n" . " INNER JOIN sys_codes s ON s.sys_codes_ref_id = im.vehicle_id\n" . " WHERE IFNULL(im.circular_rate,0) > 0\n" . $where . " AND im.party_code = {$_GET['party']}\n" . " order by im.description\n";
$data = array();
$result = $mysqli->query($sql);
while ($row = $result->fetch_assoc()) {
$vid = $row['vehicle_id'];
$row['rate'] = $row['rate'] * 1;
$row['fix_rate'] = $row['fix_rate'] * 1;
if (!isset($data["{$vid}"])) {
$data["{$vid}"] = array();
$data["{$vid}"]['vehicle'] = $row['vehicle'];
$data["{$vid}"]['data'] = array();
}
$data["{$vid}"]['data'][] = $row;
}
$mysqli->close();
$step = $_POST['step'] * 1;
$data = array();
$data['error'] = true;
$data['error_msg'] = "Invalid";
switch ($step) {
case 1:
$p_comp = addslashes(trim($_POST['new_comp']));
$company = getIVal("company_ref_id", "sys_companies", "name = '{$p_comp}'");
dump($company);
if ($company != null) {
$data['error'] = true;
$data['error_msg'] = "Company Name already exists";
break;
}
$p_email = addslashes(trim($_POST['new_email']));
$company = getIVal("company_ref_id", "sys_companies", "email = '{$p_email}'");
dump($company);
if ($company != null) {
$data['error'] = true;
$data['error_msg'] = "E-Mail already exists";
break;
}
if ($company == null) {
$p_showCaptcha = 'No';
if (isset($_POST['show_captcha'])) {
$p_showCaptcha = $_POST['show_captcha'];
}
/* Create New Company */
$ret = newcompany_data($p_comp, $p_email, $p_showCaptcha);
dump($ret);
$data['error'] = $ret['error'];
$result = $mysqli->query($sql);
while ($row = $result->fetch_assoc()) {
$taxes[] = $row;
}
?>
<div id="tabCountrySetContainer">
<ul>
<li><a href="#tabCountrySetCountry"><span title="World Countries">Country</span></a></li>
<li><a href="#tabCountrySetRegion"><span title="Provinces">Region</span></a></li>
<li><a href="#tabCountrySetCity"><span title="Cities">City</span></a></li>
</ul>
<div id="tabCountrySetCountry">
<form id="formCountrySetCountry" autocomplete="off" method="post" action="settings_cud.php">
<input type="hidden" name="form" value="countrySetCountry" />
<input type="hidden" name="parentCountryCode" value="<?php
echo getIVal('sys_codes_ref_id', 'sys_codes', "code = 'Country' AND company_ref_id = {$selected_company_id}");
?>
" />
<table border="0" width="100%" align="center">
<colgroup>
<col width="40%" />
<col width="40%" />
<col width="19%" />
<col width="1%" />
</colgroup>
<thead>
<tr>
<th class="tableth"><div class="tableheader">Name</div></th>
<th class="tableth"><div class="tableheader">Description</div></th>
<th class="tableth" colspan="2"><div class="tableheader">Active</div></th>
</tr>
buttonImageOnly: true,
constrainInput: true,
disableInput: true,
minDate: <?php
echo getIVal("minDate", "vu_minMaxDate", "company_ref_id = {$_SESSION['company_id']}");
?>
});
$("#date_end").datepicker({
showOn: 'button',
buttonImage: '../images/calendar.gif',
buttonImageOnly: true,
constrainInput: true,
disableInput: true,
minDate: <?php
echo getIVal("minDate", "vu_minMaxDate", "company_ref_id = {$_SESSION['company_id']}");
?>
});
});
$(function() {
});
function onGenerate(btn) {
if ($('#account_class').val() == '') {
alert("Please select an account class first.");
document.getElementById('account_class').focus();
return;
}
setTimeout("ref()", 1000);
if (strlen($voucher_detail_id) > 0) {
d_mysql_query("UPDATE voucher_detail SET debit_amount = {$taxamt} WHERE voucher_id = {$voucher_id} AND account_ref_id = {$account_ref_id}");
} else {
d_mysql_query("INSERT INTO voucher_detail SET debit_amount = {$taxamt}, voucher_id = {$voucher_id}, account_ref_id = {$account_ref_id}, credit_amount = 0, remarks = 'Tax'");
}
}
}
} else {
$parent_key = $_POST['dpurchase_id'];
}
/* start auditing on add, update, delete */
$voucher_id = getValue("voucher_id", "purchase_master", "p_id = {$parent_key}", true);
$voucher_aud_id = d_mysql_query("INSERT INTO voucher_master_aud SELECT NULL, vm.*, '{$oper}', {$_SESSION['user_id']}, now() FROM voucher_master vm WHERE vm.voucher_id = {$voucher_id}", true);
d_mysql_query("INSERT INTO voucher_detail_aud SELECT NULL, vd.*, {$voucher_aud_id} FROM voucher_detail vd WHERE vd.voucher_id = {$voucher_id}");
echo "Purchase Import Exp";
$evoucher_id = getIVal("voucher_id", "purchase_import_exp", "p_id = {$parent_key}", true);
if ($evoucher_id != null) {
$evoucher_aud_id = d_mysql_query("INSERT INTO voucher_master_aud SELECT NULL, vm.*, '{$oper}', {$_SESSION['user_id']}, now() FROM voucher_master vm WHERE vm.voucher_id = {$evoucher_id}", true);
d_mysql_query("INSERT INTO voucher_detail_aud SELECT NULL, vd.*, {$evoucher_aud_id} FROM voucher_detail vd WHERE vd.voucher_id = {$evoucher_id}");
}
$purchase_aud_id = d_mysql_query("INSERT INTO purchase_master_aud SELECT NULL, pm.*, {$voucher_aud_id}, '{$oper}', {$_SESSION['user_id']}, now() FROM purchase_master pm WHERE p_id = {$parent_key}", true);
d_mysql_query("INSERT INTO purchase_detail_aud SELECT NULL, pd.*, {$purchase_aud_id} FROM purchase_detail pd WHERE pd.p_id = {$parent_key}");
/* end auditing on add, update, delete */
if ($oper == 'delete') {
d_mysql_query("DELETE FROM purchase_import_exp WHERE p_id = {$parent_key}");
if ($evoucher_id != null) {
d_mysql_query("DELETE FROM voucher_detail WHERE voucher_id = {$evoucher_id}");
d_mysql_query("DELETE FROM voucher_master WHERE voucher_id = {$evoucher_id}");
}
d_mysql_query("DELETE FROM purchase_detail WHERE p_id = {$parent_key}");
d_mysql_query("DELETE FROM purchase_master WHERE p_id = {$parent_key}");
<?php
require_once '../includes/connecti.php';
/** @var $mysqli mysqli */
/** @var $result mysqli_result */
/** @var $_title string */
require_once '../includes/funcs.inc.php';
$title = $_title . "({$_GET['date1']} - {$_GET['date2']})";
$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";
}
*/
require_once '../includes/funcs.inc.php';
include '../includes/JSON.php';
include '../includes/connecti.php';
/** @var $mysqli mysqli */
/** @var $result mysqli_result */
@session_start();
ob_start();
dump($_POST);
$step = $_POST['step'] * 1;
$data = array();
$data['error'] = true;
$data['error_msg'] = "Invalid";
switch ($step) {
case 1:
$p_comp = addslashes(trim($_POST['new_comp']));
$company = getIVal("company_ref_id", "sys_companies", "name = '{$p_comp}'");
dump($company);
if ($company != null) {
$data['error'] = true;
$data['error_msg'] = "Company Name already exists";
break;
}
$p_email = addslashes(trim($_POST['new_email']));
/*$company = getIVal("company_ref_id", "sys_companies", "email = '$p_email'");
dump($company);
if ($company != null) {
$data['error'] = true;
$data['error_msg'] = "E-Mail already exists";
break;
}*/
if ($company == null) {
$sql = "exp_code = '{$e_code}', amount = '{$amt}', account_ref_id = '{$cacc}', remarks = '{$remarks}'";
if (strlen($import_id) > 0) {
$sql = " UPDATE purchase_import_exp SET {$sql} WHERE pi_id = {$import_id}";
} else {
$sql = " INSERT INTO purchase_import_exp SET p_id = {$parent_key}, {$sql}";
}
}
echo $sql . "<br/>";
mysql_query($sql) or die("Couldn t execute query." . mysql_error());
if (strlen($_POST["expense_" . $i]) < 1) {
$_key = 0;
} else {
$dr_acc = getIVal("code", "sys_codes", "sys_codes_ref_id = {$e_code}");
if (strlen($import_id) > 0) {
$_key = $import_id;
$voucher_id = getIVal("voucher_id", "purchase_import_exp", "pi_id = {$_key}");
d_mysql_query("UPDATE voucher_detail SET debit_amount = {$amt}, account_ref_id = {$dr_acc} WHERE voucher_id = {$voucher_id} AND remarks = 'Debit'");
d_mysql_query("UPDATE voucher_detail SET credit_amount = {$amt}, account_ref_id = {$cacc} WHERE voucher_id = {$voucher_id} AND remarks = 'Credit'");
} else {
$_key = mysql_insert_id();
$createVoucher = createVoucher($selected_company_id, $year_id, $dr_acc, $cacc, $amt, $remarks, $_key, 'PI', my_sql_date($_POST['purchase_receive_date']), $_SESSION['user_id']);
dump($createVoucher);
}
}
$data["iid_" . $i] = $_key;
}
}
$sum = getValue("sum((ship_quantity * ship_rate) - IFNULL(discount_rate, 0)) amt", "purchase_detail", "p_id = {$parent_key}", true);
$dr_acc = -1;
$cr_acc = -1;
$purchase_exchange_rate = 0;
<?php
session_start();
require_once '../includes/connecti.php';
/** @var $mysqli mysqli */
/** @var $result mysqli_result */
require_once '../includes/funcs.inc.php';
$_title = getReportTitle();
if (isset($_GET['report'])) {
$where = "";
if (strlen($_GET['party']) > 0) {
$where = " AND cat.cat_id = {$_GET['party']}\n";
}
$vendor = getIVal("cat_name", "item_category ", "cat_id = {$_GET['party']}");
$title = $_title;
$sql = "" . "SELECT cat.cat_id cat_id, cat.cat_name catname,im.i_code ,im.cno, im.manufacturer_no, im.description ,\n" . " ROUND(IFNULL(AVG(sd.sales_rate), 0)) sale_rate, ROUND(IFNULL(sum(sd.sales_qty), 0)) sale_qty\n" . " FROM item_master im\n" . " INNER JOIN item_category cat ON cat.cat_id = im.cat_id\n" . "\t INNER JOIN sales_detail sd on sd.i_code = im.i_code\n" . " where im.company_ref_id = {$_SESSION['company_id']}\n" . $where . " group by cat.cat_id,cat.cat_name,im.cno,\n" . " im.i_code, im.manufacturer_no, im.description \n" . " order by im.cno\n";
$data = array();
$result = $mysqli->query($sql);
while ($row = $result->fetch_assoc()) {
$vid = $row['catname'];
$cost = icost($row['i_code']);
$row['Cost'] = $cost;
$row['Diff'] = $row['sale_rate'] - $cost;
$row['Amount'] = $row['sale_qty'] * $row['Diff'];
//$data["$vid"]['data'] = array();
$data["{$vid}"]['data'][] = $row;
}
if ($_GET['report'] == 'pdf') {
require_once '../includes/PDFReport.class.php';
class PDF extends PDFReport
{
}
$accounts[] = $account;
}
if (count($accounts) == 0) {
//all was selected
require_once '../includes/connect.php';
$items = mysql_query("SELECT coa.account_ref_id\n" . " FROM chart_of_account coa\n" . " INNER JOIN party p ON p.account_ref_id = coa.account_ref_id\n" . " WHERE p.party_city = '{$_GET['city']}'\n" . " AND coa.company_ref_id = {$_SESSION['company_id']}\n" . "ORDER BY account_name");
while ($row = mysql_fetch_assoc($items)) {
$accounts[] = $row['account_ref_id'];
}
}
$ndata = array();
foreach ($accounts as $account) {
$party = getIVal("party_name", "party", "account_ref_id = {$account}");
$party_hidden = getIVal("party_code", "party", "account_ref_id = {$account}");
$days_limit = getIVal("days_limit", "party", "account_ref_id = {$account}");
$aob = _getOpeningBalance($account, $date_start);
$pay = getPayments($account, $date_start, $date_end);
$balance = $aob - $pay;
if ($balance < 0) {
$balance = $balance * -1;
} else {
$balance = 0;
}
$sql = "" . "SELECT sa.invoice,\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";
}
<?php
session_start();
include '../includes/connecti.php';
include '../includes/JSON.php';
include '../includes/funcs.inc.php';
$data = array();
$data['count'] = 0;
if (isset($_GET['start_date']) && isset($_GET['end_date']) && isset($_GET['id'])) {
$data['count'] = getIVal("count(*) cnt", "voucher_master vm", "(vm.voucher_date < '{$_GET['start_date']}' OR vm.voucher_date > '{$_GET['end_date']}') AND vm.year_id = {$_GET['id']}");
}
$json = new Services_JSON();
$output = $json->encode($data);
print $output;
}
if (count($accounts) == 0) {
//all was selected
require_once '../includes/db.php';
$items = oci_parse($conn, "SELECT coa.account_ref_id\n" . " FROM chart_of_account coa\n" . " INNER JOIN party p ON p.account_ref_id = coa.account_ref_id\n" . " WHERE p.party_city = '{$_GET['city']}'\n" . " AND coa.company_ref_id = {$_SESSION['company_id']}\n" . "ORDER BY account_name");
oci_execute($items);
while ($row = oci_fetch_assoc($items)) {
$accounts[] = $row['account_ref_id'];
}
}
$ndata = array();
foreach ($accounts as $account) {
$party = getIVal("party_name", "party", "account_ref_id = {$account}");
$party_hidden = getIVal("party_code", "party", "account_ref_id = {$account}");
$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";
}
function getFinancialDate($col, $year_id = "")
{
$where = "AND is_active = 'Yes'";
if ($year_id != "") {
$where = "AND year_id = {$year_id}";
}
return getIVal($col, "sys_financial_year", "company_ref_id = {$_SESSION['company_id']} {$where}");
}
function getProfitLostForYear($year_id, $forPl = "No")
{
global $mysqli;
/** @var $result mysqli_result */
$rows = array("Cash Purchase" => 0, "Cash Sales" => 0, "Credit Purchase" => 0, "Credit Sales" => 0, "Purchase Discount" => 0, "Purchase Return" => 0, "Sales Discount" => 0, "Sales Return" => 0, "Open Inventory" => 0, "Close Inventory" => 0, "Total Expenses" => 0, "Other Income" => array());
if ($year_id > 0) {
$sql = "" . "SELECT description, abs(debit-credit) amt\n" . " FROM vu_profit_loss\n" . " WHERE year_id = {$year_id}";
$result = $mysqli->query($sql);
while ($row = $result->fetch_assoc()) {
$rows["{$row['description']}"] = $row['amt'] * 1;
}
$result->close();
$rows["Open Inventory"] = getIVal("IFNULL(sum(opening_balance*rate),0)", "item_opening_balance", "year_id = {$year_id}") * 1;
$sql = "" . "SELECT sum(x.ob * x.cost_rate) amt FROM (\n" . "SELECT s.i_code,sum(s.rec_qty - s.iss_qty) ob, ROUND(sum((case when s.cost_rate = 0.00 then 0 else s.rec_qty end) *s.cost_rate)/sum((case when s.cost_rate = 0.00 then 0 else s.rec_qty end)),3) cost_rate\n" . " FROM vu_stock s\n" . " WHERE s.company_ref_id = {$_SESSION['company_id']}\n" . " AND s.year_id = {$year_id}\n" . " AND s.is_active = '{$forPl}'\n" . "GROUP BY s.i_code\n" . ") x";
$result = $mysqli->query($sql);
if ($row = $result->fetch_assoc()) {
$rows["Close Inventory"] = $row['amt'] * 1;
}
$sql = "" . "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_ref_id IN (\n" . " SELECT c.account_ref_id\n" . " FROM chart_of_account c\n" . " WHERE c.company_ref_id = vm.company_ref_id\n" . " AND c.account_category in (SELECT sys_codes_ref_id FROM sys_codes WHERE code in('Expense','Other Expense') AND company_ref_id = {$_SESSION['company_id']})\n" . " )\n" . " AND vm.year_id = {$year_id}\n" . " AND vm.voucher_type NOT IN ('CO')";
$result = $mysqli->query($sql);
$row = $result->fetch_assoc();
$result->close();
$rows["Total Expenses"] = $row['amt'] * 1;
$sql = "" . "SELECT coa.account_name, sum(vd.credit_amount - vd.debit_amount) amt,\n" . " (SELECT aob.opening_balance FROM account_opening_balance aob WHERE aob.account_ref_id = coa.account_ref_id AND aob.year_id = vm.year_id) aobal\n" . " FROM chart_of_account coa\n" . " INNER JOIN voucher_detail vd ON vd.account_ref_id = coa.account_ref_id\n" . " INNER JOIN voucher_master vm ON vm.voucher_id = vd.voucher_id\n" . " WHERE coa.account_category = (SELECT s.sys_codes_ref_id FROM sys_codes s WHERE s.code = 'Other Income' AND s.company_ref_id = coa.company_ref_id)\n" . " AND coa.company_ref_id = vm.company_ref_id\n" . " AND vm.year_id = {$year_id}\n" . "GROUP BY coa.account_ref_id";
$result = $mysqli->query($sql);
while ($row = $result->fetch_assoc()) {
$rows["Other Income"][] = array("name" => $row['account_name'], "amt" => $row['amt'] * 1 + $row['aobal'] * 1);
}
$result->close();
}
return $rows;
}
function getOutstandingData($account_hidden, $report = "pdf", $date_start = "", $date_end = "", $party = false, $aclass = false)
{
global $mysqli;
/** @var $result mysqli_result */
$account_name = "";
$psql = "q.account_category = {$account_hidden}";
if ($party === true) {
$account_name = getIVal("account_name", "chart_of_account", "account_ref_id = {$account_hidden}");
$psql = "q.account_ref_id = {$account_hidden}";
} else {
if ($aclass === true) {
$psql = "q.account_class = '{$account_hidden}'";
}
}
$sql = "" . " SELECT q.account_ref_id,\n" . " q.account_name,\n" . " q.account_type,\n" . " p.party_city,\n" . " p.payment_term,\n" . " p.days_limit\n" . " FROM chart_of_account q\n" . " LEFT OUTER JOIN party p ON p.account_ref_id = q.account_ref_id\n" . " WHERE q.company_ref_id = {$_SESSION['company_id']}\n" . " AND {$psql}\n" . "ORDER BY q.account_name";
//dump($sql);
$result = $mysqli->query($sql);
$data = array();
$dr = 0;
$cr = 0;
$aob = 0;
while ($row = $result->fetch_assoc()) {
$aob = getOpeningBalance($row['account_ref_id'], $date_start);
$trans = getOutstandingDataTransactions($row['account_ref_id'], $date_start, $date_end);
$row['debit'] = $trans['debit'];
$row['credit'] = $trans['credit'];
$dr += $row['debit'];
$cr += $row['credit'];
if ($report == 'pdf') {
if ($row['account_type'] == 'Dr') {
$row['balance'] = number_format($aob + $row['debit'] - $row['credit']);
} else {
if ($row['account_type'] == 'Cr') {
$row['balance'] = number_format($aob + $row['credit'] - $row['debit']);
}
}
$row['debit'] = number_format($row['debit']);
$row['credit'] = number_format($row['credit']);
} else {
$row['balance'] = $aob;
}
$data[] = $row;
}
$result->close();
/*if (count($data) == 0) {
$sql = "" .
"SELECT coa.account_ref_id, coa.account_name, coa.account_type, p.party_city, p.payment_term\n" .
" FROM chart_of_account coa\n" .
" INNER JOIN party p ON p.account_ref_id = coa.account_ref_id\n" .
" WHERE coa.account_ref_id = $account_hidden";
$result = $mysqli->query($sql);
if($row = $result->fetch_assoc()) {
$data[] = array(
'account_ref_id' => $row['account_ref_id'],
'account_name' => $row['account_name'],
'account_type' => $row['account_type'],
'party_city' => $row['party_city'],
'debit' => '0',
'credit' => '0',
'payment_term' => $row['payment_term'],
'balance' => getOpeningBalance($row['account_ref_id'], $date_start)
);
}
$result->close();
}*/
$ret = array();
$ret['account_name'] = $account_name;
$ret['aob'] = $aob;
$ret['dr'] = $dr;
$ret['cr'] = $cr;
$ret['data'] = $data;
return $ret;
}
if ($result === true) {
$data['cmpidOk_name'] = true;
} else {
$data['cmpidNt_name'] = true;
}
$sql = sprintf("UPDATE sys_companies SET currency_code = %s WHERE company_ref_id = {$_SESSION['company_id']}", addslashes($_POST['cmpcurrency']));
$queries[] = $sql;
$result = $mysqli->query($sql);
if ($result === true) {
$data['cmpidOk_currency'] = true;
} else {
$data['cmpidNt_currency'] = true;
}
} else {
$parentImpExp = getIVal('sys_codes_ref_id', 'sys_codes', "code = 'IMP_EXP' AND company_ref_id = {$selected_company_id}");
$parentVouchers = getIVal('sys_codes_ref_id', 'sys_codes', "code = 'Vouchers' AND company_ref_id = {$selected_company_id}");
$counter = $_POST['counter'] * 1;
for ($i = 1; $i <= $counter; $i++) {
if ($_POST['form'] == 'reports') {
$id = $_POST["rptid_" . $i];
$name = $_POST["rptname_" . $i];
$sql = "UPDATE sys_screen SET options = '{$name}' WHERE screen_id = {$id}";
$queries[] = $sql;
$result = $mysqli->query($sql);
$data["rpt_{$i}"] = $result === true ? 'Ok' : 'Nt';
} else {
if ($_POST['form'] == 'coa') {
$id = $_POST["coaid_" . $i];
$code = $_POST["coacode_" . $i . "_hidden"];
$str = $_POST["coacode_" . $i];
if (strlen($str) < 1) {
$data['seq'] = getSequenceNextVal('purchase_master', 3);
$sql = "" . "INSERT INTO purchase_master(shipping_date,party_code,godown_code,p_remarks,receive_date,year_id,purchase_import,company_ref_id,seq,sale_master_id,is_cash,billno)\n" . "SELECT sm.sale_date, 634, 3, 'Purchased from MERAJ', sm.sale_date, {$year_id}, 'No', 3, {$data['seq']}, {$_GET['sales_master_id']}, sm.is_cash, billno FROM sales_master sm WHERE sm.sale_master_id = {$_GET['sales_master_id']}";
$mysqli->query($sql);
$data['purchase_id'] = $mysqli->insert_id;
$sql = "" . "INSERT INTO purchase_detail(i_code,ship_quantity,ship_rate,p_id,cost_rate)\n" . "SELECT (SELECT im.i_code FROM item_master im WHERE im.cno = (SELECT i.cno FROM item_master i WHERE i.i_code = sd.i_code AND i.company_ref_id = 1) AND im.company_ref_id = 3), sd.sales_qty, sd.sales_rate, {$data['purchase_id']}, 0 FROM sales_detail sd WHERE sd.sale_master_id = {$_GET['sales_master_id']}";
$mysqli->query($sql);
$ship_date = getIVal("shipping_date", "purchase_master", "p_id = {$data['purchase_id']}", true);
$purchase = getIVal("sum(ship_quantity * ship_rate) amt", "purchase_detail", "p_id = {$data['purchase_id']}", true);
$sum = getIVal("sum((ship_quantity * ship_rate) - IFNULL(discount_rate, 0)) amt", "purchase_detail", "p_id = {$data['purchase_id']}", true);
$dr_acc = getIVal("code", "sys_codes", "description = 'Credit Purchase' AND company_ref_id = 3", true);
$cr_acc = getIVal("account_ref_id", "party", "party_code = 634", true);
dump($purchase);
dump($sum);
$createVoucher = createVoucher(3, $year_id, $dr_acc, $cr_acc, $purchase, 'Purchased from MERAJ', $data['purchase_id'], 'P', $ship_date, $_SESSION['user_id']);
dump($createVoucher);
$voucher_id = getIVal("voucher_id", "purchase_master", "p_id = {$data['purchase_id']}", true);
if ($sum == $purchase) {
$mysqli->query("UPDATE voucher_detail SET debit_amount = 0 WHERE voucher_id = {$voucher_id} AND remarks = 'Discount'");
}
/* start auditing on add, update, delete */
$voucher_aud_id = d_mysql_query("INSERT INTO voucher_master_aud SELECT NULL, vm.*, '{$oper}', {$_SESSION['user_id']}, now() FROM voucher_master vm WHERE vm.voucher_id = {$voucher_id}", true);
d_mysql_query("INSERT INTO voucher_detail_aud SELECT NULL, vd.*, {$voucher_aud_id} FROM voucher_detail vd WHERE vd.voucher_id = {$voucher_id}");
$purchase_aud_id = d_mysql_query("INSERT INTO purchase_master_aud SELECT NULL, pm.*, {$voucher_aud_id}, '{$oper}', {$_SESSION['user_id']}, now() FROM purchase_master pm WHERE p_id = {$data['purchase_id']}", true);
d_mysql_query("INSERT INTO purchase_detail_aud SELECT NULL, pd.*, {$purchase_aud_id} FROM purchase_detail pd WHERE pd.p_id = {$data['purchase_id']}");
/* end auditing on add, update, delete */
} else {
if ($oper == 'update') {
//do nothing in case of update
}
}
$mysqli->close();
$first_part = " INSERT INTO sales_master SET company_ref_id = {$_SESSION['company_id']}, year_id = {$year_id}, seq = {$data['seq']}, ";
$first_tab_ins = $first_part . $first_colval;
} else {
if ($oper == 'update') {
$first_part = " UPDATE sales_master SET ";
$last_part = " WHERE sale_master_id = {$_POST['sales_id']}";
$first_tab_ins = $first_part . $first_colval . $last_part;
}
}
$url = "../transaction/sales.php";
$parent_key = -1;
if ($oper != 'delete') {
$result = d_mysql_query($first_tab_ins) or die("Couldn't execute query1." . mysql_errno() . ": " . mysql_error());
if ($_POST['oper'] == 'update') {
$parent_key = $_POST['sales_id'];
$data['seq'] = getIVal("seq", "sales_master", "sale_master_id = {$_POST['sales_id']}", true);
} else {
if ($_POST['oper'] == 'add') {
$parent_key = mysql_insert_id();
}
}
echo "Parent: " . $parent_key . "<br/>";
echo "Sequence: " . $data['seq'] . "<br/>";
$data['key'] = $parent_key;
$data['counter'] = $_POST['counter'] * 1;
$counter = $_POST['counter'] * 1;
for ($i = 1; $i <= $counter; $i++) {
$i_code = $_POST["item_" . $i . "_hidden"];
if (strlen($i_code) > 0) {
$sales_detail_id = $_POST["id_" . $i];
$sales_qty = stripFormat($_POST["inp2_{$i}"]);
<?php
include '../includes/connecti.php';
require_once '../includes/funcs.inc.php';
@session_start();
$company = explode("|", getIVal("CONCAT(company_ref_id, '|', name, '|', email)", "sys_companies", sprintf("md5(company_ref_id) = '%s'", $_GET['token'])));
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<link rel="shortcut icon" href="../images/logo_icon.gif">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>.: neXexcel :. Signup</title>
<link rel="stylesheet" type="text/css" href="../stylesheets/style.css"/>
<link rel="stylesheet" type="text/css" media="screen" href="../stylesheets/jquery-ui-1.7.1.custom.css" />
<link rel="stylesheet" type="text/css" media="screen" href="../stylesheets/ui.dialog.css" />
<style type="text/css">
label { color: black; }
#errors {
height: 58px;
}
#sign-results {
text-align: center;
-webkit-border-radius: 5px;
-moz-border-radius: 5px;
border-radius: 5px;
padding: 5px;
color: #fff;
width:50%;
margin-bottom:10px;
<?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'];
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}')";
<col width="30%" />
<col width="69%" />
<col width="1%" />
</colgroup>
<thead>
<tr>
<th class="tableth"><div class="tableheader">Name</div></th>
<th class="tableth" colspan="2"><div class="tableheader">Value</div></th>
</tr>
</thead>
<tbody id="tBodyCompany">
<tr>
<td class="tablebody"><label for="cmpname">Name</label></td>
<td class="tablebody">
<input type="text" id="cmpname" name="cmpname" value="<?php
echo getIVal("name", "sys_companies", "company_ref_id = {$selected_company_id}");
?>
" style="width:99%;" />
</td>
<td class="tablebody">
<div id="cmpidOk_name" class="btnStat"><img src="../images/accept.png" width="16" height="16" alt="ok" border="0" /></div>
<div id="cmpidNt_name" class="btnStat"><img src="../images/delete.png" width="16" height="16" alt="ok" border="0" /></div>
</td>
</tr>
<tr>
<td class="tablebody"><label for="cmpcurrency">Currency</label></td>
<td class="tablebody">
<select id="cmpcurrency" name="cmpcurrency" size="1" style="width:100%;"><?php
foreach ($ccodes as $code) {
?>
<option value="<?php
$num3 = $num1;
$num1 = $num2;
}
for ($k = $num1; $k <= $num3; $k++) {
$billnos[] = $k;
}
}
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";
}
}
$voucher_id = getIVal("voucher_id", "voucher_detail", "voucher_detail_id = {$voucher_detail_id}", true);
$first_part = " UPDATE voucher_master SET ";
$last_part = " WHERE voucher_id = {$voucher_id}";
$first_tab_ins = $first_part . $first_colval . $last_part;
echo $first_tab_ins . "<br/>";
$mysqli->query($first_tab_ins);
$sql = "UPDATE voucher_detail SET account_ref_id = '{$cheque_id}', debit_amount = '{$amt}', remarks = '{$cheque_name}' WHERE voucher_id = {$voucher_id} AND credit_amount = '0'";
echo $sql . "<br/>";
$mysqli->query($sql);
$sql = "UPDATE voucher_detail SET account_ref_id = '{$cr_acc}', credit_amount = '{$amt}', remarks = '{$remarks}' WHERE voucher_id = {$voucher_id} AND debit_amount = '0'";
echo $sql . "<br/>";
$mysqli->query($sql);
$voucher_aud_id = di_mysql_query("INSERT INTO voucher_master_aud SELECT NULL, vm.*, 'update', {$_SESSION['user_id']}, now() FROM voucher_master vm WHERE vm.voucher_id = {$voucher_id}", true);
di_mysql_query("INSERT INTO voucher_detail_aud SELECT NULL, vd.*, {$voucher_aud_id} FROM voucher_detail vd WHERE vd.voucher_id = {$voucher_id}");
if (strlen($bank_ref_id) > 0) {
$bank_name = getIVal("account_name", "chart_of_account", "account_ref_id = {$bank_ref_id}", true);
if (strlen($bvoucher_id) > 0) {
$first_part = " UPDATE voucher_master SET ";
$last_part = " WHERE voucher_id = {$bvoucher_id}";
$first_tab_ins = $first_part . "voucher_date = '{$value_date}', voucher_remarks = '{$voucher_id}', voucher_type = 'CBV', voucher_cheque = '{$cheque}'" . $last_part;
echo $first_tab_ins . "<br/>";
$mysqli->query($first_tab_ins);
$sql = "UPDATE voucher_detail SET account_ref_id = '{$bank_ref_id}', debit_amount = '{$amt}', remarks = '{$bank_name}' WHERE voucher_id = {$bvoucher_id} AND credit_amount = '0'";
echo $sql . "<br/>";
$mysqli->query($sql);
$sql = "UPDATE voucher_detail SET account_ref_id = '{$cheque_id}', credit_amount = '{$amt}', remarks = '{$cheque_name}' WHERE voucher_id = {$bvoucher_id} AND debit_amount = '0'";
echo $sql . "<br/>";
$mysqli->query($sql);
$voucher_aud_id = di_mysql_query("INSERT INTO voucher_master_aud SELECT NULL, vm.*, 'update', {$_SESSION['user_id']}, now() FROM voucher_master vm WHERE vm.voucher_id = {$bvoucher_id}", true);
di_mysql_query("INSERT INTO voucher_detail_aud SELECT NULL, vd.*, {$voucher_aud_id} FROM voucher_detail vd WHERE vd.voucher_id = {$bvoucher_id}");
} else {