$activeSheet->setCellValue("E{$count}", "CS");
$activeSheet->getStyle("E{$count}")->getFont()->setBold(true);
$activeSheet->getStyle("E{$count}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->setCellValue("F{$count}", "PC");
$activeSheet->getStyle("F{$count}")->getFont()->setBold(true);
$activeSheet->getStyle("F{$count}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$count++;
$startCount = $count;
foreach ($data as $row) {
$activeSheet->setCellValue("A{$count}", $row['party_name']);
$activeSheet->setCellValue("B{$count}", $row['vendor']);
$activeSheet->setCellValue("C{$count}", $row['cat_name']);
$activeSheet->setCellValue("D{$count}", $row['description']);
$activeSheet->setCellValue("E{$count}", stripFormat($row['cs']));
$activeSheet->getStyle("E{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED4);
$activeSheet->setCellValue("F{$count}", stripFormat($row['pc']));
$activeSheet->getStyle("F{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED4);
$count++;
}
$activeSheet->getStyle("A" . ($startCount - 1) . ":F" . ($count - 1))->applyFromArray($tableBorderArray);
$activeSheet->mergeCells("A{$count}:D{$count}");
$activeSheet->setCellValue("A{$count}", "Total");
$activeSheet->getStyle("A{$count}")->getFont()->setBold(true);
$activeSheet->getStyle("A{$count}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->setCellValue("E{$count}", "=SUM(E{$startCount}:E" . ($count - 1) . ")");
$activeSheet->getStyle("E{$count}")->getFont()->setBold(true);
$activeSheet->getStyle("E{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED4);
$activeSheet->setCellValue("F{$count}", "=SUM(F{$startCount}:F" . ($count - 1) . ")");
$activeSheet->getStyle("F{$count}")->getFont()->setBold(true);
$activeSheet->getStyle("F{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED4);
$objPHPExcel->setActiveSheetIndex(0);
}
$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;
}
$sales_value = stripFormat($_POST["inp2_{$i}"]) * stripFormat($_POST["inp3_{$i}"]);
$item_discount = stripFormat($_POST["inp4_{$i}"]);
if (isset($_POST["chk4_{$i}"]) && strlen($_POST["chk4_{$i}"]) > 0) {
$sales_value -= $item_discount * $sales_value / 100;
} else {
$sales_value -= $item_discount;
}
$sql = "" . "SELECT t.account_ref_id, t.tax_rate\n" . " FROM vu_item_tax vt INNER JOIN taxonomy t ON t.tax_id = vt.tax_id\n" . " WHERE vt.i_code = {$i_code}";
$taxs = mysql_query($sql);
while ($row = mysql_fetch_assoc($taxs)) {
$account_ref_id = $row['account_ref_id'];
if (!isset($taxes["{$account_ref_id}"])) {
$taxes["{$account_ref_id}"] = (double) 0;
}
$row['tax_rate'] = $row['tax_rate'] * 1;
$row['sales_value'] = $sales_value;
$row['tax'] = $row['tax_rate'] * $row['sales_value'] / 100;
/** @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}')";
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/>";
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}"]);
$sql = "" . "SELECT t.account_ref_id, t.tax_rate\n" . " FROM vu_item_tax vt INNER JOIN taxonomy t ON t.tax_id = vt.tax_id\n" . " WHERE vt.i_code = {$i_code}";
$taxs = oci_parse($conn, $sql);
oci_execute($taxs);
while ($row = oci_fetch_assoc($taxs)) {
$account_ref_id = $row['account_ref_id'];
if (!isset($taxes["{$account_ref_id}"])) {
$taxes["{$account_ref_id}"] = (double) 0;
}
$row['tax_rate'] = $row['tax_rate'] * 1;
$row['purchase_value'] = $purchase_value;
$row['tax'] = $row['tax_rate'] * $row['purchase_value'] / 100;
$taxes["{$account_ref_id}"] += $row['tax'];
}
}
dump($taxes);
$activeSheet->getStyle("D{$count}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->setCellValue("E{$count}", "Quantity");
$activeSheet->getStyle("E{$count}")->getFont()->setBold(true);
$activeSheet->getStyle("E{$count}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->setCellValue("F{$count}", "Unit");
$activeSheet->getStyle("F{$count}")->getFont()->setBold(true);
$activeSheet->getStyle("F{$count}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$count++;
$startCount = $count;
foreach ($data as $row) {
$activeSheet->setCellValue("A{$count}", $row['sno']);
$activeSheet->getStyle("A{$count}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->setCellValue("B{$count}", $row['CNo']);
$activeSheet->setCellValue("C{$count}", $row['party_name']);
$activeSheet->setCellValue("D{$count}", $row['item']);
$activeSheet->setCellValue("E{$count}", stripFormat($row['qty']));
$activeSheet->getStyle("E{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED4);
$activeSheet->setCellValue("F{$count}", $row['unit']);
$count++;
}
$activeSheet->getStyle("A" . ($startCount - 1) . ":F" . ($count - 1))->applyFromArray($tableBorderArray);
$objPHPExcel->setActiveSheetIndex(0);
$fileName = "order_invoice.xlsx";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $fileName . '"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
}
}
} else {
$objPHPExcel->getActiveSheet()->setCellValue("D{$count}", 'Debit');
$objPHPExcel->getActiveSheet()->getStyle("D{$count}")->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle("D{$count}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue("E{$count}", 'Credit');
$objPHPExcel->getActiveSheet()->getStyle("E{$count}")->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle("E{$count}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$count++;
$startCount = $count;
foreach ($ndata as $row) {
$objPHPExcel->getActiveSheet()->setCellValue("A{$count}", $row['rownum']);
$objPHPExcel->getActiveSheet()->getStyle("A{$count}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue("B{$count}", $row['code']);
$objPHPExcel->getActiveSheet()->setCellValue("C{$count}", $row['account_name']);
$objPHPExcel->getActiveSheet()->setCellValue("D{$count}", stripFormat($row['debit']));
$objPHPExcel->getActiveSheet()->getStyle("D{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$objPHPExcel->getActiveSheet()->setCellValue("E{$count}", stripFormat($row['credit']));
$objPHPExcel->getActiveSheet()->getStyle("E{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$count++;
}
if ($startCount == $count) {
$count++;
}
$tableBorderArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000'))));
$objPHPExcel->getActiveSheet()->getStyle("A" . ($startCount - 1) . ":E" . ($count + 1))->applyFromArray($tableBorderArray);
$objPHPExcel->getActiveSheet()->mergeCells("A{$count}:C{$count}");
$objPHPExcel->getActiveSheet()->setCellValue("A{$count}", "Total");
$objPHPExcel->getActiveSheet()->getStyle("A{$count}")->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle("A{$count}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue("D{$count}", "=SUM(D{$startCount}:D" . ($count - 1) . ")");
$objPHPExcel->getActiveSheet()->getStyle("D{$count}")->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle("D{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$activeSheet->getStyle("G{$count}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->setCellValue("H{$count}", "Balance");
$activeSheet->getStyle("H{$count}")->getFont()->setBold(true);
$activeSheet->getStyle("H{$count}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$count++;
$startCount = $count;
foreach ($ret['data'] as $row) {
$activeSheet->setCellValue("A{$count}", $row['invoice']);
$activeSheet->getStyle("A{$count}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$activeSheet->setCellValue("B{$count}", $row['voucher_date']);
$activeSheet->mergeCells("C{$count}:D{$count}");
$activeSheet->setCellValue("C{$count}", $row['voucher_remarks']);
$activeSheet->setCellValue("E{$count}", $row['days']);
$activeSheet->setCellValue("F{$count}", stripFormat($row['debit_amount']));
$activeSheet->getStyle("F{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$activeSheet->setCellValue("G{$count}", stripFormat($row['credit_amount']));
$activeSheet->getStyle("G{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$balRow = "H{$pageCountStart}";
if ($count > $startCount) {
$balRow = 'H' . ($count - 1);
}
if ($ret['account_type'] == 'Dr') {
$balRow = "={$balRow}+F{$count}-G{$count}";
} else {
if ($ret['account_type'] == 'Cr') {
$balRow = "={$balRow}+G{$count}-F{$count}";
}
}
$activeSheet->setCellValue("H{$count}", $balRow);
$activeSheet->getStyle("H{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$count++;
$pdf->Cell($cellSize * 15, 5, number_format($amt, 2), 1, 0, 'R');
$pdf->Ln(10);
}
} else {
foreach ($outstanding as $ret) {
if ($ret == NULL) {
continue;
}
$pdf->AddPage();
$pdf->AddCol('account_name', '55%', 'Account Name', 'L');
$pdf->AddCol('debit', '15%', 'Debit', 'R');
$pdf->AddCol('credit', '15%', 'Credit', 'R');
$pdf->AddCol('balance', '15%', 'Balance', 'R');
$pdf->Table($ret['data'], $prop);
$amt = 0;
foreach ($ret['data'] as $row) {
$amt += stripFormat($row['balance']) * 1;
}
$pdf->SetFont('Arial', 'B', 10);
$cellSize = 1 / 100 * $pdf->TableWidth;
$pdf->SetX($pdf->lMargin);
$pdf->Cell($cellSize * 85, 5, "Total", 1, 0, 'C');
$pdf->Cell($cellSize * 15, 5, number_format($amt, 2), 1, 0, 'R');
$pdf->Ln(10);
}
}
if (isset($_GET['account_class']) && strlen($_GET['account_class']) > 0) {
$pdf->Output("account_class.pdf", "D");
} else {
$pdf->Output("outstanding.pdf", "D");
}
$activeSheet->getStyle("G{$count}")->getFont()->setBold(true);
$activeSheet->getStyle("G{$count}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->setCellValue("H{$count}", "Cheque");
$activeSheet->getStyle("H{$count}")->getFont()->setBold(true);
$activeSheet->getStyle("H{$count}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
$count++;
$startCount = $count;
foreach ($ret['data'] as $row) {
$activeSheet->mergeCells("A{$count}:B{$count}");
$activeSheet->setCellValue("A{$count}", $row['account_name']);
$activeSheet->setCellValue("C{$count}", stripFormat($row['debit']));
$activeSheet->getStyle("C{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$activeSheet->setCellValue("D{$count}", stripFormat($row['credit']));
$activeSheet->getStyle("D{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$activeSheet->setCellValue("E{$count}", stripFormat($row['days_limit']));
$activeSheet->getStyle("E{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
if ($row['account_type'] == 'Dr') {
$activeSheet->setCellValue("F{$count}", "=" . $row['balance'] . "+C{$count}-D{$count}");
$activeSheet->getStyle("F{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
} else {
if ($row['account_type'] == 'Cr') {
$activeSheet->setCellValue("F{$count}", "=" . $row['balance'] . "+D{$count}-C{$count}");
$activeSheet->getStyle("F{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
}
}
$count++;
}
if (isset($_GET['outstanding']) && $_GET['outstanding'] == 'party') {
$activeSheet->getStyle("A" . ($startCount - 1) . ":H" . $count)->applyFromArray($tableBorderArray);
} else {
$objPHPExcel->getActiveSheet()->getStyle("G{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$objPHPExcel->getActiveSheet()->setCellValue("H{$count}", stripFormat($row['may']));
$objPHPExcel->getActiveSheet()->getStyle("H{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$objPHPExcel->getActiveSheet()->setCellValue("I{$count}", stripFormat($row['jun']));
$objPHPExcel->getActiveSheet()->getStyle("I{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$objPHPExcel->getActiveSheet()->setCellValue("J{$count}", stripFormat($row['jul']));
$objPHPExcel->getActiveSheet()->getStyle("J{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$objPHPExcel->getActiveSheet()->setCellValue("K{$count}", stripFormat($row['aug']));
$objPHPExcel->getActiveSheet()->getStyle("K{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$objPHPExcel->getActiveSheet()->setCellValue("L{$count}", stripFormat($row['sep']));
$objPHPExcel->getActiveSheet()->getStyle("L{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$objPHPExcel->getActiveSheet()->setCellValue("M{$count}", stripFormat($row['oct']));
$objPHPExcel->getActiveSheet()->getStyle("M{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$objPHPExcel->getActiveSheet()->setCellValue("N{$count}", stripFormat($row['nov']));
$objPHPExcel->getActiveSheet()->getStyle("N{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$objPHPExcel->getActiveSheet()->setCellValue("O{$count}", stripFormat($row['dec']));
$objPHPExcel->getActiveSheet()->getStyle("O{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$count++;
}
$tableBorderArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000'))));
$objPHPExcel->getActiveSheet()->getStyle("A" . ($startCount - 1) . ":O" . $count)->applyFromArray($tableBorderArray);
$objPHPExcel->getActiveSheet()->mergeCells("A{$count}:B{$count}");
$objPHPExcel->getActiveSheet()->setCellValue("A{$count}", "Total");
$objPHPExcel->getActiveSheet()->getStyle("A{$count}")->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle("A{$count}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue("C{$count}", "=SUM(C{$startCount}:C" . ($count - 1) . ")");
$objPHPExcel->getActiveSheet()->getStyle("C{$count}")->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle("C{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$objPHPExcel->getActiveSheet()->setCellValue("D{$count}", "=SUM(D{$startCount}:D" . ($count - 1) . ")");
$objPHPExcel->getActiveSheet()->getStyle("D{$count}")->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle("D{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$sTemplate = "";
if ($startCount < $count) {
$sTemplate = "=%s%d-%s%d+";
}
$activeSheet->setCellValue("A{$count}", $row['month']);
$activeSheet->setCellValue("B{$count}", sprintf($sTemplate, "B", $count - 1, "C", $count - 1) . stripFormat($row[$key . 'DEPOI']));
$activeSheet->setCellValue("C{$count}", stripFormat($row[$key . 'DEPOS']));
$activeSheet->setCellValue("D{$count}", "=B{$count}/C{$count}");
$activeSheet->setCellValue("E{$count}", sprintf($sTemplate, "E", $count - 1, "F", $count - 1) . stripFormat($row[$key . 'TIKI']));
$activeSheet->setCellValue("F{$count}", stripFormat($row[$key . 'TIKS']));
$activeSheet->setCellValue("G{$count}", "=E{$count}/F{$count}");
$activeSheet->setCellValue("H{$count}", sprintf($sTemplate, "H", $count - 1, "I", $count - 1) . stripFormat($row[$key . 'FPII']));
$activeSheet->setCellValue("I{$count}", stripFormat($row[$key . 'FPIS']));
$activeSheet->setCellValue("J{$count}", "=H{$count}/I{$count}");
$activeSheet->setCellValue("K{$count}", sprintf($sTemplate, "K", $count - 1, "L", $count - 1) . stripFormat($row[$key . 'LocalI']));
$activeSheet->setCellValue("L{$count}", stripFormat($row[$key . 'LocalS']));
$activeSheet->setCellValue("M{$count}", "=K{$count}/L{$count}");
$activeSheet->getStyle("B{$count}:M{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED4);
$activeSheet->getStyle("D{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1_B);
$activeSheet->getStyle("G{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1_B);
$activeSheet->getStyle("J{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1_B);
$activeSheet->getStyle("M{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1_B);
$count++;
}
$activeSheet->getStyle("A" . ($startCount - 3) . ":M" . ($count - 0))->applyFromArray($tableBorderArray);
$activeSheet->setCellValue("A{$count}", "Totals");
$activeSheet->getStyle("A{$count}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->setCellValue("B{$count}", "=SUM(B{$startCount}:B" . ($count - 1) . ")");
$activeSheet->setCellValue("C{$count}", "=SUM(C{$startCount}:C" . ($count - 1) . ")");
$activeSheet->setCellValue("D{$count}", "=B{$count}/C{$count}");
$activeSheet->setCellValue("E{$count}", "=SUM(E{$startCount}:E" . ($count - 1) . ")");
foreach ($data['data'] as $row) {
$activeSheet->setCellValue("A{$count}", $row['sno']);
$activeSheet->getStyle("A{$count}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->setCellValue("B{$count}", $row['cno']);
$activeSheet->setCellValue("C{$count}", $row['manufacture']);
$activeSheet->setCellValue("D{$count}", $row['item']);
$activeSheet->setCellValue("E{$count}", stripFormat($row['opn']));
$activeSheet->getStyle("E{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED5);
$activeSheet->setCellValue("F{$count}", stripFormat($row['rec']));
$activeSheet->getStyle("F{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED5);
$activeSheet->setCellValue("G{$count}", stripFormat($row['iss']));
$activeSheet->getStyle("G{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED5);
$activeSheet->setCellValue("H{$count}", "=E{$count}+F{$count}-G{$count}");
$activeSheet->getStyle("H{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED5);
if ($showAmount) {
$activeSheet->setCellValue("I{$count}", stripFormat($row['cost_rate']));
$activeSheet->getStyle("I{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED5);
$activeSheet->setCellValue("J{$count}", "=H{$count}*I{$count}");
$activeSheet->getStyle("J{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED5);
}
$count++;
}
if ($showAmount) {
$activeSheet->getStyle("A" . ($startCount - 1) . ":J" . ($count - 1))->applyFromArray($tableBorderArray);
$activeSheet->mergeCells("A{$count}:H{$count}");
$activeSheet->setCellValue("A{$count}", "Total");
$activeSheet->getStyle("A{$count}")->getFont()->setBold(true);
$activeSheet->getStyle("A{$count}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->mergeCells("I{$count}:J{$count}");
$activeSheet->setCellValue("I{$count}", "=SUM(J{$startCount}:J" . ($count - 1) . ")");
$activeSheet->getStyle("I{$count}")->getFont()->setBold(true);
createVoucher($selected_company_id, $year_id, $dr_acc, $cr_acc, $sum, $_POST['sales_return_remarks'], $parent_key, 'SR', my_sql_date($_POST['sales_return_date']), $_SESSION['user_id']);
$mysqli->close();
}
$voucher_id = getValue("voucher_id", "sales_return_master", "rsale_master_id = {$parent_key}", true);
d_mysql_query("UPDATE voucher_detail SET credit_amount = {$sale} 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;
}
$sales_value = stripFormat($_POST["inp2_{$i}"]) * stripFormat($_POST["inp3_{$i}"]);
$sql = "" . "SELECT t.account_ref_id, t.tax_rate\n" . " FROM vu_item_tax vt INNER JOIN taxonomy t ON t.tax_id = vt.tax_id\n" . " WHERE vt.i_code = {$i_code}";
$taxs = mysql_query($sql);
while ($row = mysql_fetch_assoc($taxs)) {
$account_ref_id = $row['account_ref_id'];
if (!isset($taxes["{$account_ref_id}"])) {
$taxes["{$account_ref_id}"] = (double) 0;
}
$row['tax_rate'] = $row['tax_rate'] * 1;
$row['sales_value'] = $sales_value;
$row['tax'] = $row['tax_rate'] * $row['sales_value'] / 100;
$taxes["{$account_ref_id}"] += $row['tax'];
}
}
dump($taxes);
foreach ($taxes as $account_ref_id => $taxamt) {
}
$result = $mysqli->query(sprintf($aetsql, $row[0]));
$_row = $result->fetch_row();
if ($_row[0] != '0') {
$a = array();
$a['type'] = $uploads_var;
$a['name'] = $row[0];
$a['error'] = 'Account already defined';
$data['errors'][] = $a;
continue;
}
$sql = sprintf($atsql, $row[0], $accounts["{$row['3']}"]['type'], $accounts["{$row['3']}"]['id']);
$mysqli->query($sql);
$v_account_ref_id = $mysqli->insert_id;
if (strlen($row[2]) > 0) {
$mysqli->query(sprintf($aobtsql, $v_account_ref_id, stripFormat($row[2])));
}
if (strlen($row[1]) > 0) {
$ps = substr($row[1], 0, 1);
if ($ps == 'C') {
$ps = 'P';
}
if ($ps == 'V') {
$ps = 'S';
}
$mysqli->query(sprintf($ptsql, $row[0], $v_account_ref_id, $ps));
}
} else {
if ($uploads_var == 'items') {
$item = $row[0];
$cat = $row[1];
