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