D7net
Home
Console
Upload
information
Create File
Create Folder
About
Tools
:
/
home
/
shubmkcj
/
urbane.createerp.com
/
Filename :
invoice-report-download.php
back
Copy
<?php // Debug - Development Mode error_reporting(E_ALL); ini_set("display_errors", 1); ob_start(); session_start(); require "config.php"; require ("PHPExcel/Classes/PHPExcel.php"); $source_id = ""; $bill_pay_name = ''; if (isset($_SESSION["source_id"]) && $_SESSION["source_id"] != "") { $source_id = $_SESSION["source_id"]; } else { die("Something went wrong.."); } function create_slug($string){ $slug=strtolower(preg_replace('/[^A-Za-z0-9-]+/', '-', $string)); return $slug; } $report_type = $_POST["report"]; $fromdate = $_POST["fromdate"]; $todate = $_POST["todate"]; $bill_pay_name = $_POST['bill_pay_name']; $from = date("Y-m-d", strtotime($fromdate)); $to = date("Y-m-d", strtotime($todate)); ////////////////////////////////////// //////////// start if invoice///////// ////////////////////////////////////// if ($report_type == 'All_Invoice'){ function numberTowords($num){ $decones = array( '01' => "One", '02' => "Two", '03' => "Three", '04' => "Four", '05' => "Five", '06' => "Six", '07' => "Seven", '08' => "Eight", '09' => "Nine", 10 => "Ten", 11 => "Eleven", 12 => "Twelve", 13 => "Thirteen", 14 => "Fourteen", 15 => "Fifteen", 16 => "Sixteen", 17 => "Seventeen", 18 => "Eighteen", 19 => "Nineteen" ); $ones = array( 0 => " ", 1 => "One", 2 => "Two", 3 => "Three", 4 => "Four", 5 => "Five", 6 => "Six", 7 => "Seven", 8 => "Eight", 9 => "Nine", 10 => "Ten", 11 => "Eleven", 12 => "Twelve", 13 => "Thirteen", 14 => "Fourteen", 15 => "Fifteen", 16 => "Sixteen", 17 => "Seventeen", 18 => "Eighteen", 19 => "Nineteen" ); $tens = array( 0 => "", 2 => "Twenty", 3 => "Thirty", 4 => "Forty", 5 => "Fifty", 6 => "Sixty", 7 => "Seventy", 8 => "Eighty", 9 => "Ninety" ); $hundreds = array( "Hundred", "Thousand", "Million", "Billion", "Trillion", "Quadrillion" ); //limit t quadrillion $num = number_format($num,2,".",","); $num_arr = explode(".",$num); $wholenum = $num_arr[0]; $decnum = $num_arr[1]; $whole_arr = array_reverse(explode(",",$wholenum)); krsort($whole_arr); $rettxt = ""; foreach($whole_arr as $key => $i){ if($i < 20){ $rettxt .= $ones[$i]; } elseif($i < 100){ $rettxt .= $tens[substr($i,0,1)]; $rettxt .= " ".$ones[substr($i,1,1)]; } else{ $rettxt .= $ones[substr($i,0,1)]." ".$hundreds[0]; $rettxt .= " ".$tens[substr($i,1,1)]; $rettxt .= " ".$ones[substr($i,2,1)]; } if($key > 0){ $rettxt .= " ".$hundreds[$key]." "; } } if($decnum > 0){ $rettxt .= " "; if($decnum < 20){ $rettxt = $rettxt." and "; $rettxt .= $decones[$decnum]; } elseif($decnum < 100){ $rettxt = $rettxt." and "; $rettxt .= $tens[substr($decnum,0,1)]; $rettxt .= " ".$ones[substr($decnum,1,1)]; } $rettxt = $rettxt. " Only"; } else{ $rettxt = $rettxt. " Only"; } return $rettxt;} $report_type = $_POST["report"]; $fromdate = $_POST["fromdate"]; $todate = $_POST["todate"]; $bill_pay_name = $_POST['bill_pay_name']; $from = date("Y-m-d", strtotime($fromdate)); $to = date("Y-m-d", strtotime($todate)); // company data fetch $companyd = mysqli_query($conn,"SELECT * FROM `company_details`"); $companyrow = mysqli_fetch_assoc($companyd); $contact_person = $companyrow['contact_person']; $company_name = $companyrow['company_name']; $company_logo = $companyrow['company_logo']; $GSTIN = $companyrow['GSTIN']; $phone = $companyrow['phone']; $company_email = $companyrow['email']; $address = $companyrow['address']; $city = $companyrow['city']; $state = $companyrow['state']; $country = $companyrow['country']; $account_name = $companyrow['account_name']; $account_no = $companyrow['account_no']; $bank_name = $companyrow['bank_name']; $ifsc = $companyrow['ifsc']; $shift = $companyrow['shift']; $branch = $companyrow['branch']; $company_color = $companyrow['company_color']; $currency = $companyrow['currency']; $invoice_prifix = $companyrow['invoice_prifix']; $default_invoice = $companyrow['default_invoice']; $signature = $companyrow['signature']; $invoice_note = $companyrow['invoice_note']; $space=''; $totatdebetAmt = 0.00; $creditAmt = 0.00; $remainAmt = 0.00; $sqlAssign1 = "SELECT * FROM generate_invoice LEFT JOIN payment ON generate_invoice.id = payment.invoiceRow_ID WHERE generate_invoice.data_delete = 'NO' AND generate_invoice.marge_status = 'no' GROUP BY generate_invoice.id ORDER BY generate_invoice.id DESC"; $resultAssign1 = mysqli_query($conn, $sqlAssign1); if (mysqli_num_rows($resultAssign1) > 0) { while($rows = mysqli_fetch_assoc($resultAssign1)) { if (date("Y-m-d", strtotime($rows["date"])) >= $from && date("Y-m-d", strtotime($rows["date"])) <= $to){ $gen_inv_id=$rows['id']; $aa=$rows['date']; $bb=$rows['proforma_id']; $cc=$rows['invoice_Id']; $dd=$rows['invoice_reference']; $ee=$rows['amount_paid']; $bank_charge=$rows['bank_charge']; $bill_pay_name=$rows['bill_pay_name']; $totatAmt=sprintf('%0.2f', ($ee)); $totatdebetAmt=sprintf('%0.2f', ($totatdebetAmt+$ee)); $inv_date_date = date_create($aa); $inv_date_date = date_format($inv_date_date,"d-m-Y"); $transaction_reference = $rows['transaction_reference_no']; $iidks_items .='<tr style="width:100%"> <td class="font12" style="padding:10px;width:20%"> '.$inv_date_date.' </td> <td class="font12" style="padding:10px;width:20%">'.$cc.'</td> <td class="font12" style="padding:10px;width:20%"> </td> <td class="font12" style="padding:10px;width:20%;text-align:right"> <strong style="font-weight:bold;color:#000">'.$totatAmt.'</strong></td> <td class="font12" style="padding:10px;width:20%"> </td> </tr>'; $sqlGetPaidAmount = "SELECT * FROM payment WHERE transaction_reference_no = '$transaction_reference' AND transaction_type = 'Credit' AND is_deleted = 'NO'"; $resultGetPaidAmount = mysqli_query($conn, $sqlGetPaidAmount); if($resultGetPaidAmount){ if(mysqli_num_rows($resultGetPaidAmount) > 0){ while($rowGetPaidAmount = mysqli_fetch_assoc($resultGetPaidAmount)){ $payment_id = $rowGetPaidAmount["id"] ; $amountq = $rowGetPaidAmount["amount"] ; $pmode = $rowGetPaidAmount["mode"] ; $payment_date = $rowGetPaidAmount["payment_date"] ; $transaction_reference_no = $rowGetPaidAmount["transaction_reference_no"] ; if(!empty($transaction_reference_no)){ $transaction_reference_no='Ref. No. '.$transaction_reference_no; }else{ $transaction_reference_no=''; } $creditAmt1=sprintf('%0.2f', ($amountq)); $creditAmt=sprintf('%0.2f', ($creditAmt+$amountq)); $pay_date_date = date_create($payment_date); $pay_date_date = date_format($pay_date_date,"d-m-Y"); $iidks_items .='<tr style="width:100%"> <td class="font12" style="padding:10px;width:20%"> '.$pay_date_date.' </td> <td class="font12" style="padding:10px;width:20%">'.$cc.'</td> <td class="font12" style="padding:10px;width:20%"> '.$pmode.' </td> <td class="font12" style="padding:10px;width:20%"> </td> <td class="font12" style="padding:10px;width:20%;font-weight:600;text-align:right"> <strong style="font-weight:bold;color:#000">'.$creditAmt1.'</strong><br><small>'.$transaction_reference_no.'</small></td> </tr>'; } } } } } } // echo"<pre>"; // print_r($iidks_items) ; // die(); //die; $remainAmt=sprintf('%0.2f', ($totatdebetAmt-$creditAmt)); // Invoice Data Fetch $html = "<!DOCTYPE html> <html lang='en'> <body> <br> <section class='invoice-page'> <div class='container-fluid'> <div class='row pt-1'> <div class='col-md-12 text-center'> <h5 class='mb-0' ><strong style='color:#000'> STATEMENT FOR THE TIME PERIOD OF $from - $to</strong></h5> </div> </div> <div class='row pt-3'> <table class='table table-striped'> <thead> <tr style='width:100%'> <th class='bac_color font14' style='border-top:1px solid #afafaf;border-bottom:1px solid #afafaf;background: #fff !important; color:#444; padding:8px;'>Date</th> <th class='bac_color font14' style='border-top:1px solid #afafaf;border-bottom:1px solid #afafaf;background: #fff !important; color:#444; padding:8px;'>Invoice Id</th> <th class='bac_color font14' style='border-top:1px solid #afafaf;border-bottom:1px solid #afafaf;background: #fff !important; color:#444; padding:8px;'>Payment Mode</th> <th class='bac_color font14' style='border-top:1px solid #afafaf;border-bottom:1px solid #afafaf;background: #fff !important; color:#444; padding:8px;text-align:center'>Debit</th> <th class='bac_color font14' style='border-top:1px solid #afafaf;border-bottom:1px solid #afafaf;background: #fff !important; color:#444; padding:8px;text-align:right'>Credit</th> </tr> </thead> <tbody> $iidks_items <tr> <td class='py-0' style='border-top:1px solid #afafaf;border-bottom:1px solid #afafaf;background: #fff !important; color:#444; padding:8px;'></td> <td class='py-0' style='border-top:1px solid #afafaf;border-bottom:1px solid #afafaf;background: #fff !important; color:#444; padding:8px;'></td> <td class='py-0' style='border-top:1px solid #afafaf;border-bottom:1px solid #afafaf;background: #fff !important; color:#444; padding:8px;'><strong >Sub Total</strong></td> <td class='font12 py-0' style='border-top:1px solid #afafaf;border-bottom:1px solid #afafaf;background: #fff !important; color:#444; padding:8px;text-align:right'><strong style='font-weight:bold;color:#000;font-size:16px'>$totatdebetAmt</strong></td> <td class='font12 py-0' style='border-top:1px solid #afafaf;border-bottom:1px solid #afafaf;background: #fff !important; color:#444; padding:8px;text-align:right'><strong style='font-weight:bold;color:#000;font-size:16px'>$creditAmt</strong></td> </tr> <tr> <td class='py-0' style='padding:8px;'></td> <td class='py-0' style='padding:8px;'></td> <td class='py-0' style='padding:8px;'></td> <td class='font12 py-0' style='padding:8px;'><strong>Balance Amount </strong><small>($currency)</small></td> <td class='font12 py-0' style='padding:8px;'><strong style='font-weight:bold;color:#000;font-size:18px'>$remainAmt</strong></td> </tr> </tbody> </table> <div class='col-md-12 p-0'> <p style='display:block;background:#afafaf;text-align:center;color:#fff;font-size:13px;'>Balance Amount in $currency - ".numberTowords("$remainAmt")."</p> </div> <div> <div class='row pt-4'> <div class='col-md-6 float-left'> </div> <div class='col-md-6 float-right'> <p class='text-center m-0 font12'>For <strong>$company_name</strong><p> <p class='text-center'><img src='signature/$signature' width='95'></p> <p class='text-center'>Authorized Signatory</p> </div> </div> </div> </section> </body> </html>"; require ("mpdf/mpdf.php"); $mpdf = new mPDF('A4'); $mpdf->allow_charset_conversion = true; $stylesheet = file_get_contents('bootstrap.min.css'); $mpdf->WriteHTML($stylesheet,1); $stylesheet1 = file_get_contents('styleInvoice3.css'); $mpdf->WriteHTML($stylesheet1,1); $mpdf->showImageErrors = true; $mpdf->SetDisplayMode('fullpage'); $mpdf->WriteHTML($html); $mpdf->Output(); //track record $logindk_usersId=$_SESSION["login_usersId"]; $logindk_emailId=$_SESSION["login_email"]; exit; /////////////////////////////////////////// ///////////// End if All invoice ////////////// /////////////////////////////////////////// } ////////////////////////////////////// //////////// start if invoice///////// ////////////////////////////////////// else if ($report_type == 'Invoice'){ $objPHPExcel = new PHPExcel(); // Set the active Excel worksheet to sheet 0 $objPHPExcel->setActiveSheetIndex(0); $style = array( 'font' => array( 'bold' => true ) , 'borders' => array( 'allborders' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '000000' ) ) ) ); $border = array( 'borders' => array( 'allborders' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '000000' ) ) ) ); $style1 = array( 'alignment' => array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, ) ); $headerStyle = array( 'font' => array( 'bold' => true, 'color' => array( 'rgb' => '000000' ) , 'size' => 20, 'name' => 'Verdana' ) ); $secondLineStyle = array( 'font' => array( 'bold' => true, 'color' => array( 'rgb' => '000000' ) , 'size' => 15, 'name' => 'Verdana' ) ); $objPHPExcel->getDefaultStyle() ->applyFromArray(array( 'borders' => array( 'top' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) , 'bottom' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) , 'left' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) , 'right' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) ) )); $objPHPExcel->getDefaultStyle() ->applyFromArray($style1); //apply the style on column A row 1 to Column B row 1 $objPHPExcel->getActiveSheet() ->getStyle('A1:M1') ->applyFromArray($style); // $objPHPExcel->getActiveSheet()->getStyle('A4:I4')->applyFromArray($style); // $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->applyFromArray($headerStyle); // $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->applyFromArray($secondLineStyle); $objPHPExcel->getActiveSheet() ->setCellValue('A1', "Date"); $objPHPExcel->getActiveSheet() ->setCellValue('B1', "Invoice Number"); $objPHPExcel->getActiveSheet() ->setCellValue('C1', "Proforma Number"); $objPHPExcel->getActiveSheet() ->setCellValue('D1', "Company Name"); $objPHPExcel->getActiveSheet() ->setCellValue('E1', "Contact Person"); $objPHPExcel->getActiveSheet() ->setCellValue('F1', "Mobile Number"); $objPHPExcel->getActiveSheet() ->setCellValue('G1', "GSTIN Number"); $objPHPExcel->getActiveSheet() ->setCellValue('H1', "Bank Charges"); $objPHPExcel->getActiveSheet() ->setCellValue('I1', "Other Charges"); $objPHPExcel->getActiveSheet() ->setCellValue('J1', "Invoice Value"); $objPHPExcel->getActiveSheet() ->setCellValue('K1', "Payment Received"); $objPHPExcel->getActiveSheet() ->setCellValue('L1', "Due Payment"); $objPHPExcel->getActiveSheet() ->setCellValue('M1', "Status"); $objPHPExcel->getActiveSheet() ->getColumnDimension('A') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('B') ->setWidth(25); $objPHPExcel->getActiveSheet() ->getColumnDimension('C') ->setWidth(25); $objPHPExcel->getActiveSheet() ->getColumnDimension('D') ->setWidth(40); $objPHPExcel->getActiveSheet() ->getColumnDimension('E') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('F') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('G') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('H') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('I') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('J') ->setWidth(30); $objPHPExcel->getActiveSheet() ->getColumnDimension('K') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('L') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('M') ->setWidth(20); $rowCount = 2; $i = 1; $totalInvoiceAmtPaid=0; $inv_due_amt=0; $inv_payment_recived=0; $inv_other_chges=0; $inv_bank_chages=0; $sql = "SELECT * FROM generate_invoice WHERE data_delete != 'yes' AND `marge_status`!='yes' AND `bill_pay_name` = $bill_pay_name"; $result = $conn->query($sql) or die(mysqli_error($conn)); if (mysqli_num_rows($result) > 0) { while ($row = mysqli_fetch_assoc($result)) { if (date("Y-m-d", strtotime($row["date"])) >= $from && date("Y-m-d", strtotime($row["date"])) <= $to) { $status = ""; if ($row["data_delete"] == "cancel") { $status = "Cancelled"; } $payment = 0.00; $tax = 0.00; $invoice = $row["invoice_Id"]; $sqlGetPayment = "SELECT amount, tax_deduction FROM payment WHERE reference = '$invoice' AND transaction_type = 'Credit' AND is_deleted !='yes'"; $resultGetPayment = $conn->query($sqlGetPayment); if ($resultGetPayment) { if (mysqli_num_rows($resultGetPayment) > 0) { while ($rowGetPayment = mysqli_fetch_assoc($resultGetPayment)) { $payment = $rowGetPayment["amount"]; $tax = $rowGetPayment["tax_deduction"]; } } } $helping_id = $row['bill_pay_name']; $get_bpn = mysqli_query($conn, "SELECT * FROM `contact` where id='$helping_id'"); $get_bpn_row = mysqli_fetch_assoc($get_bpn); $final_bill_pay_name = $get_bpn_row['bill_display_name']; $sub_total = sprintf('%0.2f', $row["sub_total"]); $b_chagred=$row["bank_charge"];; $amount_paid = sprintf('%0.2f', $row["amount_paid"]); if($b_chagred==35.00){ $amount_paid=$amount_paid-35.00; } $totalInvoiceAmtPaid+=$amount_paid; $duAmt=$amount_paid-$payment; $inv_due_amt+=$duAmt; $inv_payment_recived+=$payment; $inv_other_chges+=$row["other_charges"]; $inv_bank_chages+=$row["bank_charge"]; $objPHPExcel->getActiveSheet() ->setCellValue('A' . $rowCount, $row["date"]); $objPHPExcel->getActiveSheet() ->setCellValue('B' . $rowCount, $row["invoice_Id"]); $objPHPExcel->getActiveSheet() ->setCellValue('C' . $rowCount, $row["proforma_id"]); $objPHPExcel->getActiveSheet() ->setCellValue('D' . $rowCount, $final_bill_pay_name); $objPHPExcel->getActiveSheet() ->setCellValue('E' . $rowCount, $row["client_name"]); $objPHPExcel->getActiveSheet() ->setCellValue('F' . $rowCount, $row["mobile_no"]); $objPHPExcel->getActiveSheet() ->setCellValue('G' . $rowCount, $row["gstin"]); $objPHPExcel->getActiveSheet() ->setCellValue('H' . $rowCount, $row["bank_charge"]); $objPHPExcel->getActiveSheet() ->setCellValue('I' . $rowCount, $row["other_charges"]); $objPHPExcel->getActiveSheet() ->setCellValue('J' . $rowCount, $amount_paid ); $objPHPExcel->getActiveSheet() ->setCellValue('K' . $rowCount, $payment ); $objPHPExcel->getActiveSheet() ->setCellValue('L' . $rowCount, $amount_paid-$payment); $objPHPExcel->getActiveSheet() ->getStyle('B' . $rowCount . ':L' . $rowCount)->getNumberFormat() ->setFormatCode('0.00'); $objPHPExcel->getActiveSheet() ->setCellValue('M' . $rowCount, $status); $objPHPExcel->getActiveSheet() ->getStyle('A' . $rowCount . ':M' . $rowCount)->applyFromArray($border); $rowCount = $rowCount + 1; } } } $objPHPExcel->getActiveSheet() ->setCellValue('H' . $rowCount, $inv_bank_chages); $objPHPExcel->getActiveSheet() ->setCellValue('I' . $rowCount, $inv_other_chges); $objPHPExcel->getActiveSheet() ->setCellValue('J' . $rowCount, $totalInvoiceAmtPaid ); $objPHPExcel->getActiveSheet() ->setCellValue('K' . $rowCount, $inv_payment_recived ); $objPHPExcel->getActiveSheet() ->setCellValue('L' . $rowCount, $inv_due_amt); /////////////////////////////////////////// ///////////// End if invoice ////////////// /////////////////////////////////////////// header('Content-Type: application/vnd.ms-excel'); header("Content-Disposition: attachment;filename=invoice_report_from_" . $from. "_to_".$to . ".xls"); } else if ($report_type == 'Proforma'){ ////////////////////////////////////// //////////// strat If Proforma///////// ////////////////////////////////////// $objPHPExcel = new PHPExcel(); // Set the active Excel worksheet to sheet 0 $objPHPExcel->setActiveSheetIndex(0); $style = array( 'font' => array( 'bold' => true ) , 'borders' => array( 'allborders' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '000000' ) ) ) ); $border = array( 'borders' => array( 'allborders' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '000000' ) ) ) ); $style1 = array( 'alignment' => array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, ) ); $headerStyle = array( 'font' => array( 'bold' => true, 'color' => array( 'rgb' => '000000' ) , 'size' => 20, 'name' => 'Verdana' ) ); $secondLineStyle = array( 'font' => array( 'bold' => true, 'color' => array( 'rgb' => '000000' ) , 'size' => 15, 'name' => 'Verdana' ) ); $objPHPExcel->getDefaultStyle() ->applyFromArray(array( 'borders' => array( 'top' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) , 'bottom' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) , 'left' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) , 'right' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) ) )); $objPHPExcel->getDefaultStyle() ->applyFromArray($style1); //apply the style on column A row 1 to Column B row 1 $objPHPExcel->getActiveSheet() ->getStyle('A1:L1') ->applyFromArray($style); // $objPHPExcel->getActiveSheet()->getStyle('A4:I4')->applyFromArray($style); // $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->applyFromArray($headerStyle); // $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->applyFromArray($secondLineStyle); $objPHPExcel->getActiveSheet() ->setCellValue('A1', "Date"); $objPHPExcel->getActiveSheet() ->setCellValue('B1', "Proforma Number"); $objPHPExcel->getActiveSheet() ->setCellValue('C1', "Company Name"); $objPHPExcel->getActiveSheet() ->setCellValue('D1', "Contact Person"); $objPHPExcel->getActiveSheet() ->setCellValue('E1', "Mobile Number"); $objPHPExcel->getActiveSheet() ->setCellValue('F1', "GSTIN Number"); $objPHPExcel->getActiveSheet() ->setCellValue('G1', "Bank Charges"); $objPHPExcel->getActiveSheet() ->setCellValue('H1', "Other Charges"); $objPHPExcel->getActiveSheet() ->setCellValue('I1', "Total Amount"); $objPHPExcel->getActiveSheet() ->setCellValue('J1', "Status"); $objPHPExcel->getActiveSheet() ->getColumnDimension('A') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('B') ->setWidth(25); $objPHPExcel->getActiveSheet() ->getColumnDimension('C') ->setWidth(40); $objPHPExcel->getActiveSheet() ->getColumnDimension('D') ->setWidth(25); $objPHPExcel->getActiveSheet() ->getColumnDimension('E') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('F') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('G') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('H') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('I') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('J') ->setWidth(30); $rowCount = 2; $i = 1; $pro_totalAmt=0; $inv_other_chges=0; $inv_bank_chages=0; $sql = "SELECT * FROM proforma WHERE data_delete != 'yes' AND `bill_pay_name` = $bill_pay_name"; $result = $conn->query($sql) or die(mysqli_error($conn)); if (mysqli_num_rows($result) > 0) { while ($row = mysqli_fetch_assoc($result)) { if (date("Y-m-d", strtotime($row["date"])) >= $from && date("Y-m-d", strtotime($row["date"])) <= $to) { $status = ""; if ($row["data_delete"] == "cancel") { $status = "Cancelled"; } $payment = 0.00; $tax = 0.00; $invoice = $row["invoice_Id"]; $sqlGetPayment = "SELECT amount, tax_deduction FROM payment WHERE reference = '$invoice'"; $resultGetPayment = $conn->query($sqlGetPayment); if ($resultGetPayment) { if (mysqli_num_rows($resultGetPayment) > 0) { while ($rowGetPayment = mysqli_fetch_assoc($resultGetPayment)) { $payment = $rowGetPayment["amount"]; $tax = $rowGetPayment["tax_deduction"]; } } } $helping_id = $row['bill_pay_name']; $get_bpn = mysqli_query($conn, "SELECT * FROM `contact` where id='$helping_id'"); $get_bpn_row = mysqli_fetch_assoc($get_bpn); $final_bill_pay_name = $get_bpn_row['bill_display_name']; $sub_total = sprintf('%0.2f', $row["sub_total"]); $amount_paid = sprintf('%0.2f', $row["amount_paid"]); $pro_totalAmt+=$amount_paid; $inv_other_chges+=$row["other_charges"]; $inv_bank_chages+=$row["bank_charge"]; $objPHPExcel->getActiveSheet() ->setCellValue('A' . $rowCount, $row["date"]); $objPHPExcel->getActiveSheet() ->setCellValue('B' . $rowCount, $row["proforma_id"]); $objPHPExcel->getActiveSheet() ->setCellValue('C' . $rowCount, $final_bill_pay_name); $objPHPExcel->getActiveSheet() ->setCellValue('D' . $rowCount, $row["client_name"]); $objPHPExcel->getActiveSheet() ->setCellValue('E' . $rowCount, $row["mobile_no"]); $objPHPExcel->getActiveSheet() ->setCellValue('F' . $rowCount, $row["gstin"]); $objPHPExcel->getActiveSheet() ->setCellValue('G' . $rowCount, $row["bank_charge"]); $objPHPExcel->getActiveSheet() ->setCellValue('H' . $rowCount, $row["other_charges"]); $objPHPExcel->getActiveSheet() ->setCellValue('I' . $rowCount, $amount_paid); $objPHPExcel->getActiveSheet() ->getStyle('B' . $rowCount . ':I' . $rowCount)->getNumberFormat() ->setFormatCode('0.00'); $objPHPExcel->getActiveSheet() ->setCellValue('J' . $rowCount, $status); $objPHPExcel->getActiveSheet() ->getStyle('A' . $rowCount . ':J' . $rowCount)->applyFromArray($border); $rowCount = $rowCount + 1; } } } $objPHPExcel->getActiveSheet() ->setCellValue('G' . $rowCount, $inv_bank_chages); $objPHPExcel->getActiveSheet() ->setCellValue('H' . $rowCount, $inv_other_chges); $objPHPExcel->getActiveSheet() ->setCellValue('I' . $rowCount, $pro_totalAmt); /////////////////////////////////////////// ///////////// End If Proforma ////////////// /////////////////////////////////////////// header('Content-Type: application/vnd.ms-excel'); header("Content-Disposition: attachment;filename=proforma_report_from_" . $from. "_to_".$to . ".xls"); } else if ($report_type == 'All_Proforma'){ ////////////////////////////////////// //////////// strat If All Proforma///////// ////////////////////////////////////// $objPHPExcel = new PHPExcel(); // Set the active Excel worksheet to sheet 0 $objPHPExcel->setActiveSheetIndex(0); $style = array( 'font' => array( 'bold' => true ) , 'borders' => array( 'allborders' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '000000' ) ) ) ); $border = array( 'borders' => array( 'allborders' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '000000' ) ) ) ); $style1 = array( 'alignment' => array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, ) ); $headerStyle = array( 'font' => array( 'bold' => true, 'color' => array( 'rgb' => '000000' ) , 'size' => 20, 'name' => 'Verdana' ) ); $secondLineStyle = array( 'font' => array( 'bold' => true, 'color' => array( 'rgb' => '000000' ) , 'size' => 15, 'name' => 'Verdana' ) ); $objPHPExcel->getDefaultStyle() ->applyFromArray(array( 'borders' => array( 'top' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) , 'bottom' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) , 'left' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) , 'right' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) ) )); $objPHPExcel->getDefaultStyle() ->applyFromArray($style1); //apply the style on column A row 1 to Column B row 1 $objPHPExcel->getActiveSheet() ->getStyle('A1:L1') ->applyFromArray($style); // $objPHPExcel->getActiveSheet()->getStyle('A4:I4')->applyFromArray($style); // $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->applyFromArray($headerStyle); // $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->applyFromArray($secondLineStyle); $objPHPExcel->getActiveSheet() ->setCellValue('A1', "Date"); $objPHPExcel->getActiveSheet() ->setCellValue('B1', "Proforma Number"); $objPHPExcel->getActiveSheet() ->setCellValue('C1', "Company Name"); $objPHPExcel->getActiveSheet() ->setCellValue('D1', "Contact Person"); $objPHPExcel->getActiveSheet() ->setCellValue('E1', "Mobile Number"); $objPHPExcel->getActiveSheet() ->setCellValue('F1', "GSTIN Number"); $objPHPExcel->getActiveSheet() ->setCellValue('G1', "Bank Charges"); $objPHPExcel->getActiveSheet() ->setCellValue('H1', "Other Charges"); $objPHPExcel->getActiveSheet() ->setCellValue('I1', "Total Amount"); $objPHPExcel->getActiveSheet() ->setCellValue('J1', "Status"); $objPHPExcel->getActiveSheet() ->getColumnDimension('A') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('B') ->setWidth(25); $objPHPExcel->getActiveSheet() ->getColumnDimension('C') ->setWidth(40); $objPHPExcel->getActiveSheet() ->getColumnDimension('D') ->setWidth(25); $objPHPExcel->getActiveSheet() ->getColumnDimension('E') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('F') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('G') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('H') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('I') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('J') ->setWidth(30); $rowCount = 2; $i = 1; $pro_totalAmt=0; $inv_other_chges=0; $inv_bank_chages=0; $sql = "SELECT * FROM proforma WHERE data_delete != 'yes' "; $result = $conn->query($sql) or die(mysqli_error($conn)); if (mysqli_num_rows($result) > 0) { while ($row = mysqli_fetch_assoc($result)) { if (date("Y-m-d", strtotime($row["date"])) >= $from && date("Y-m-d", strtotime($row["date"])) <= $to) { $status = ""; if ($row["data_delete"] == "cancel") { $status = "Cancelled"; } $payment = 0.00; $tax = 0.00; $invoice = $row["invoice_Id"]; $sqlGetPayment = "SELECT amount, tax_deduction FROM payment WHERE reference = '$invoice'"; $resultGetPayment = $conn->query($sqlGetPayment); if ($resultGetPayment) { if (mysqli_num_rows($resultGetPayment) > 0) { while ($rowGetPayment = mysqli_fetch_assoc($resultGetPayment)) { $payment = $rowGetPayment["amount"]; $tax = $rowGetPayment["tax_deduction"]; } } } $helping_id = $row['bill_pay_name']; $get_bpn = mysqli_query($conn, "SELECT * FROM `contact` where id='$helping_id'"); $get_bpn_row = mysqli_fetch_assoc($get_bpn); $final_bill_pay_name = $get_bpn_row['bill_display_name']; $sub_total = sprintf('%0.2f', $row["sub_total"]); $amount_paid = sprintf('%0.2f', $row["amount_paid"]); $pro_totalAmt+=$amount_paid; $inv_other_chges+=$row["other_charges"]; $inv_bank_chages+=$row["bank_charge"]; $objPHPExcel->getActiveSheet() ->setCellValue('A' . $rowCount, $row["date"]); $objPHPExcel->getActiveSheet() ->setCellValue('B' . $rowCount, $row["proforma_id"]); $objPHPExcel->getActiveSheet() ->setCellValue('C' . $rowCount, $final_bill_pay_name); $objPHPExcel->getActiveSheet() ->setCellValue('D' . $rowCount, $row["client_name"]); $objPHPExcel->getActiveSheet() ->setCellValue('E' . $rowCount, $row["mobile_no"]); $objPHPExcel->getActiveSheet() ->setCellValue('F' . $rowCount, $row["gstin"]); $objPHPExcel->getActiveSheet() ->setCellValue('G' . $rowCount, $row["bank_charge"]); $objPHPExcel->getActiveSheet() ->setCellValue('H' . $rowCount, $row["other_charges"]); $objPHPExcel->getActiveSheet() ->setCellValue('I' . $rowCount, $amount_paid); $objPHPExcel->getActiveSheet() ->getStyle('B' . $rowCount . ':I' . $rowCount)->getNumberFormat() ->setFormatCode('0.00'); $objPHPExcel->getActiveSheet() ->setCellValue('J' . $rowCount, $status); $objPHPExcel->getActiveSheet() ->getStyle('A' . $rowCount . ':J' . $rowCount)->applyFromArray($border); $rowCount = $rowCount + 1; } } } $objPHPExcel->getActiveSheet() ->setCellValue('G' . $rowCount, $inv_bank_chages); $objPHPExcel->getActiveSheet() ->setCellValue('H' . $rowCount, $inv_other_chges); $objPHPExcel->getActiveSheet() ->setCellValue('I' . $rowCount, $pro_totalAmt); /////////////////////////////////////////// ///////////// End If All Proforma ////////////// /////////////////////////////////////////// header('Content-Type: application/vnd.ms-excel'); header("Content-Disposition: attachment;filename=proforma_report_from_" . $from. "_to_".$to . ".xls"); } else if ($report_type == 'Packing List'){ //////////////////////////////////////////// //////////// strat If Packing List ///////// //////////////////////////////////////////// $objPHPExcel = new PHPExcel(); // Set the active Excel worksheet to sheet 0 $objPHPExcel->setActiveSheetIndex(0); $style = array( 'font' => array( 'bold' => true ) , 'borders' => array( 'allborders' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '000000' ) ) ) ); $border = array( 'borders' => array( 'allborders' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '000000' ) ) ) ); $style1 = array( 'alignment' => array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, ) ); $headerStyle = array( 'font' => array( 'bold' => true, 'color' => array( 'rgb' => '000000' ) , 'size' => 20, 'name' => 'Verdana' ) ); $secondLineStyle = array( 'font' => array( 'bold' => true, 'color' => array( 'rgb' => '000000' ) , 'size' => 15, 'name' => 'Verdana' ) ); $objPHPExcel->getDefaultStyle() ->applyFromArray(array( 'borders' => array( 'top' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) , 'bottom' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) , 'left' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) , 'right' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) ) )); $objPHPExcel->getDefaultStyle() ->applyFromArray($style1); //apply the style on column A row 1 to Column B row 1 $objPHPExcel->getActiveSheet() ->getStyle('A1:f1') ->applyFromArray($style); // $objPHPExcel->getActiveSheet()->getStyle('A4:I4')->applyFromArray($style); // $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->applyFromArray($headerStyle); // $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->applyFromArray($secondLineStyle); $objPHPExcel->getActiveSheet() ->setCellValue('A1', "Date"); $objPHPExcel->getActiveSheet() ->setCellValue('B1', "Company Name"); $objPHPExcel->getActiveSheet() ->setCellValue('C1', "Mobile Number"); $objPHPExcel->getActiveSheet() ->setCellValue('D1', "Proforma Number"); $objPHPExcel->getActiveSheet() ->setCellValue('E1', "Invoice Number"); $objPHPExcel->getActiveSheet() ->setCellValue('F1', "Invoice Reference"); $objPHPExcel->getActiveSheet() ->getColumnDimension('A') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('B') ->setWidth(35); $objPHPExcel->getActiveSheet() ->getColumnDimension('C') ->setWidth(35); $objPHPExcel->getActiveSheet() ->getColumnDimension('D') ->setWidth(25); $objPHPExcel->getActiveSheet() ->getColumnDimension('E') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('F') ->setWidth(20); $rowCount = 2; $i = 1; $sql = "SELECT * FROM packing_list WHERE data_delete != 'yes' AND `bill_display_name` = ' $final_bill_pay_name'"; $result = $conn->query($sql) or die(mysqli_error($conn)); if (mysqli_num_rows($result) > 0) { while ($row = mysqli_fetch_assoc($result)) { if (date("Y-m-d", strtotime($row["date"])) >= $from && date("Y-m-d", strtotime($row["date"])) <= $to) { $final_bill_pay_name = $row['bill_display_name']; $objPHPExcel->getActiveSheet() ->setCellValue('A' . $rowCount, $row["date"]); $objPHPExcel->getActiveSheet() ->setCellValue('B' . $rowCount, $final_bill_pay_name); $objPHPExcel->getActiveSheet() ->setCellValue('C' . $rowCount, $row["mobile"]); $objPHPExcel->getActiveSheet() ->setCellValue('D' . $rowCount, $row["proforma_id"]); $objPHPExcel->getActiveSheet() ->setCellValue('E' . $rowCount, $row["invoice_id"]); $objPHPExcel->getActiveSheet() ->setCellValue('F' . $rowCount, $row["invoice_reference"]); $objPHPExcel->getActiveSheet() ->getStyle('A' . $rowCount . ':F' . $rowCount)->applyFromArray($border); $rowCount = $rowCount + 1; } } } //////////////////////////////////////////////// ///////////// End If Packing List ////////////// //////////////////////////////////////////////// header('Content-Type: application/vnd.ms-excel'); header("Content-Disposition: attachment;filename=packing_list_report_from_" . $from. "_to_".$to . ".xls"); } else if ($report_type == 'Customer'){ //////////////////////////////////////////// //////////// strat If Customer ///////// //////////////////////////////////////////// function numberTowords($num){ $decones = array( '01' => "One", '02' => "Two", '03' => "Three", '04' => "Four", '05' => "Five", '06' => "Six", '07' => "Seven", '08' => "Eight", '09' => "Nine", 10 => "Ten", 11 => "Eleven", 12 => "Twelve", 13 => "Thirteen", 14 => "Fourteen", 15 => "Fifteen", 16 => "Sixteen", 17 => "Seventeen", 18 => "Eighteen", 19 => "Nineteen" ); $ones = array( 0 => " ", 1 => "One", 2 => "Two", 3 => "Three", 4 => "Four", 5 => "Five", 6 => "Six", 7 => "Seven", 8 => "Eight", 9 => "Nine", 10 => "Ten", 11 => "Eleven", 12 => "Twelve", 13 => "Thirteen", 14 => "Fourteen", 15 => "Fifteen", 16 => "Sixteen", 17 => "Seventeen", 18 => "Eighteen", 19 => "Nineteen" ); $tens = array( 0 => "", 2 => "Twenty", 3 => "Thirty", 4 => "Forty", 5 => "Fifty", 6 => "Sixty", 7 => "Seventy", 8 => "Eighty", 9 => "Ninety" ); $hundreds = array( "Hundred", "Thousand", "Million", "Billion", "Trillion", "Quadrillion" ); //limit t quadrillion $num = number_format($num,2,".",","); $num_arr = explode(".",$num); $wholenum = $num_arr[0]; $decnum = $num_arr[1]; $whole_arr = array_reverse(explode(",",$wholenum)); krsort($whole_arr); $rettxt = ""; foreach($whole_arr as $key => $i){ if($i < 20){ $rettxt .= $ones[$i]; } elseif($i < 100){ $rettxt .= $tens[substr($i,0,1)]; $rettxt .= " ".$ones[substr($i,1,1)]; } else{ $rettxt .= $ones[substr($i,0,1)]." ".$hundreds[0]; $rettxt .= " ".$tens[substr($i,1,1)]; $rettxt .= " ".$ones[substr($i,2,1)]; } if($key > 0){ $rettxt .= " ".$hundreds[$key]." "; } } if($decnum > 0){ $rettxt .= " "; if($decnum < 20){ $rettxt = $rettxt." and "; $rettxt .= $decones[$decnum]; } elseif($decnum < 100){ $rettxt = $rettxt." and "; $rettxt .= $tens[substr($decnum,0,1)]; $rettxt .= " ".$ones[substr($decnum,1,1)]; } $rettxt = $rettxt. " Only"; } else{ $rettxt = $rettxt. " Only"; } return $rettxt;} $report_type = $_POST["report"]; $fromdate = $_POST["fromdate"]; $todate = $_POST["todate"]; $bill_pay_name = $_POST['bill_pay_name']; $from = date("Y-m-d", strtotime($fromdate)); $to = date("Y-m-d", strtotime($todate)); // company data fetch $companyd = mysqli_query($conn,"SELECT * FROM `company_details`"); $companyrow = mysqli_fetch_assoc($companyd); $contact_person = $companyrow['contact_person']; $company_name = $companyrow['company_name']; $company_logo = $companyrow['company_logo']; $GSTIN = $companyrow['GSTIN']; $phone = $companyrow['phone']; $company_email = $companyrow['email']; $address = $companyrow['address']; $city = $companyrow['city']; $state = $companyrow['state']; $country = $companyrow['country']; $account_name = $companyrow['account_name']; $account_no = $companyrow['account_no']; $bank_name = $companyrow['bank_name']; $ifsc = $companyrow['ifsc']; $shift = $companyrow['shift']; $branch = $companyrow['branch']; $company_color = $companyrow['company_color']; $currency = $companyrow['currency']; $invoice_prifix = $companyrow['invoice_prifix']; $default_invoice = $companyrow['default_invoice']; $signature = $companyrow['signature']; $invoice_note = $companyrow['invoice_note']; $space=''; $totatdebetAmt = 0.00; $creditAmt = 0.00; $remainAmt = 0.00; $sqlAssign1 = "SELECT * FROM generate_invoice LEFT JOIN payment ON generate_invoice.id = payment.invoiceRow_ID WHERE generate_invoice.bill_pay_name = '$bill_pay_name' AND generate_invoice.data_delete = 'NO' AND generate_invoice.marge_status = 'no' GROUP BY generate_invoice.id ORDER BY generate_invoice.id DESC"; $resultAssign1 = mysqli_query($conn, $sqlAssign1); if (mysqli_num_rows($resultAssign1) > 0) { while($rows = mysqli_fetch_assoc($resultAssign1)) { if (date("Y-m-d", strtotime($rows["date"])) >= $from && date("Y-m-d", strtotime($rows["date"])) <= $to){ $gen_inv_id=$rows['id']; $aa=$rows['date']; $bb=$rows['proforma_id']; $cc=$rows['invoice_Id']; $dd=$rows['invoice_reference']; $ee=$rows['amount_paid']; $bank_charge=$rows['bank_charge']; $bill_pay_name=$rows['bill_pay_name']; $totatAmt=sprintf('%0.2f', ($ee)); $totatdebetAmt=sprintf('%0.2f', ($totatdebetAmt+$ee)); $inv_date_date = date_create($aa); $inv_date_date = date_format($inv_date_date,"d-m-Y"); $transaction_reference = $rows['transaction_reference_no']; $iidks_items .='<tr style="width:100%"> <td class="font12" style="padding:10px;width:20%"> '.$inv_date_date.' </td> <td class="font12" style="padding:10px;width:20%">'.$cc.'</td> <td class="font12" style="padding:10px;width:20%"> </td> <td class="font12" style="padding:10px;width:20%;text-align:right"> <strong style="font-weight:bold;color:#000">'.$totatAmt.'</strong></td> <td class="font12" style="padding:10px;width:20%"> </td> </tr>'; $sqlGetPaidAmount = "SELECT * FROM payment WHERE transaction_reference_no = '$transaction_reference' AND company_id='$bill_pay_name' AND transaction_type = 'Credit' AND is_deleted = 'NO'"; $resultGetPaidAmount = mysqli_query($conn, $sqlGetPaidAmount); if($resultGetPaidAmount){ if(mysqli_num_rows($resultGetPaidAmount) > 0){ while($rowGetPaidAmount = mysqli_fetch_assoc($resultGetPaidAmount)){ $payment_id = $rowGetPaidAmount["id"] ; $amountq = $rowGetPaidAmount["amount"] ; $pmode = $rowGetPaidAmount["mode"] ; $payment_date = $rowGetPaidAmount["payment_date"] ; $transaction_reference_no = $rowGetPaidAmount["transaction_reference_no"] ; if(!empty($transaction_reference_no)){ $transaction_reference_no='Ref. No. '.$transaction_reference_no; }else{ $transaction_reference_no=''; } $creditAmt1=sprintf('%0.2f', ($amountq)); $creditAmt=sprintf('%0.2f', ($creditAmt+$amountq)); $pay_date_date = date_create($payment_date); $pay_date_date = date_format($pay_date_date,"d-m-Y"); $iidks_items .='<tr style="width:100%"> <td class="font12" style="padding:10px;width:20%"> '.$pay_date_date.' </td> <td class="font12" style="padding:10px;width:20%">'.$cc.'</td> <td class="font12" style="padding:10px;width:20%"> '.$pmode.' </td> <td class="font12" style="padding:10px;width:20%"> </td> <td class="font12" style="padding:10px;width:20%;font-weight:600;text-align:right"> <strong style="font-weight:bold;color:#000">'.$creditAmt1.'</strong><br><small>'.$transaction_reference_no.'</small></td> </tr>'; } } } } } } // echo"<pre>"; // print_r($iidks_items) ; // die(); //die; $remainAmt=sprintf('%0.2f', ($totatdebetAmt-$creditAmt)); // Invoice Data Fetch $sql = " SELECT * FROM generate_invoice where bill_pay_name = '$bill_pay_name' and data_delete = 'NO'"; $result = mysqli_query($conn,$sql); $row = mysqli_fetch_array($result); $bill_pay_name = $row['bill_pay_name']; $invoice_id = $row['invoice_Id']; $proforma_id = $row['proforma_id']; $pincode = $row['pincode']; $date = $row['date']; $terms = $row['terms']; $due_date = $row['due_date']; $deliver_address = $row['deliver_address']; $billingAddress = $row['billingAddress']; $client_name = $row['client_name']; $mobile_no = $row['mobile_no']; $email = $row['email']; $gstin = $row['gstin']; $type = $row['type']; $buyer_reference = $row['buyer_reference']; $delivery_type = $row['delivery_type']; $delivery_terms = $row['delivery_terms']; $mode_of_shipment = $row['mode_of_shipment']; $shipping_date = $row['shipping_date']; $port_of_loading = $row['port_of_loading']; $port_of_delivery = $row['port_of_delivery']; $payment_method = $row['payment_method']; $hs_code = $row['hs_code']; $terms_of_payment = $row['terms_of_payment']; $bank_details = $row['bank_details']; $foreign_charges = $row['foreign_charges']; $foreign_rate = $row['foreign_rate']; $discount_percent = $row['discount_percent']; $discount = $row['discount']; $other_charges = $row['other_charges']; $memo = $row['memo']; $other_price = $row['other_price']; $subtotal_price = $row['subtotal_price']; $amount_paid = $row['amount_paid']; $comment = $row['comment']; $invoice_reference = $row['invoice_reference']; $bank_charges = $row['bank_charge']; $other_charge_msg = $row['other_charge_msg']; // Getting Contact Details $contactsql = mysqli_query($conn,"SELECT * FROM `contact` where id='$bill_pay_name' and data_delete !='yes'"); $contactrow = mysqli_fetch_assoc($contactsql); $customer_type = $contactrow['customer_type']; $bill_display_name = $contactrow['bill_display_name']; $contact_person_name = $contactrow['contact_person_name']; $contact_person_designation = $contactrow['contact_person_designation']; // billing address $getad_b = mysqli_query($conn,"SELECT * FROM `contact_address` where id='$billingAddress'"); $getadrow_b = mysqli_fetch_assoc($getad_b); $address_get_b = $getadrow_b['address'] .', '. $getadrow_b['city'] .', '. $getadrow_b['state'] .', '. $getadrow_b['country']; // getting address $getad = mysqli_query($conn,"SELECT * FROM `contact_address` where id='$deliver_address'"); $getadrow = mysqli_fetch_assoc($getad); $address_get = $getadrow['address'] .', '. $getadrow['city'] .', '. $getadrow['state'] .', '. $getadrow['country']; $iecno = $getadrow['iecno']; $getitemsql = mysqli_query($conn,"SELECT * FROM invoice_item a left join inventory b on a.item_id = b.id where a.invoice_reference = '$invoice_reference'"); while($item_row = mysqli_fetch_assoc($getitemsql)){ $item_image = $item_row['item_image']; if(!empty($item_image)){ $if_image = '<img src="inventory/'.$item_image.'" width="20">'; }else{ $if_image = ''; } $ii_items .='<tr style="width:100%"> <td class="font12" style="padding:2px;width:10%"> '.$item_row['item_code'].' </td> <td class="font12" style="padding:2px;width:10%">'.$if_image.'</td> <td class="font12" style="padding:2px;width:20%"> '.$item_row['name'].' </td> <td class="font12" style="padding:2px;width:10%"> '.$item_row['size'].'</td> <td class="font12" style="padding:2px;width:15%"> '.$item_row['color'].'</td> <td class="font12" style="padding:2px;width:5%"> '.$item_row['qty'].'</td> <td class="font12" style="padding:2px;width:5%"> '.$item_row['unit_of_measure'].'</td> <td class="font12" style="padding:2px;width:15%"> '.$item_row['price'].'</td> <td class="font12" style="padding:2px;width:10%"> '.$item_row['row_total'].'</td> </tr>'; } // Other Charges Hae To $other_charges_h = ''; if(!empty($other_price)){ $other_charges_h = "<tr> <td class='py-0'></td> <td class='py-0'></td> <td class='py-0'></td> <td class='py-0'></td> <td class='py-0'></td> <td class='py-0'></td> <td class='py-0'></td> <td class='font12 py-0' style='padding:2px;'><strong>Other <small>($other_charge_msg)<small></strong></td> <td class='font12 py-0' style='padding:2px;'>$other_price</td> </tr> "; } $html = "<!DOCTYPE html> <html lang='en'> <body> <br> <section class='invoice-page'> <div class='container-fluid'> <div class='row pt-1'> <div class='col-md-12 text-center'><h3><strong style='color:#000'>".ucfirst($bill_display_name)."</strong></h3> <h6 class='mb-0' ><strong style='color:#000'>( STATEMENT FOR THE TIME PERIOD OF $from - $to )</strong></h6> <p class='mb-0 font12'> $address_get_b </p> <p class='mb-0 font12'>Zip Code : $pincode</p> <p class='mb-0 font12'>GSTIN : $gstin</p> <p class='mb-0 font12'>IEC No.: $iecno </p> <p class='mb-0 font12'>Contact Person: $contact_person_name</p> <p class='mb-0 font12'>Contact No.: $mobile_no</p> </div> </div> <div class='row pt-3'> <table class='table table-striped'> <thead> <tr style='width:100%'> <th class='bac_color font14' style='border-top:1px solid #afafaf;border-bottom:1px solid #afafaf;background: #fff !important; color:#444; padding:8px;'>Date</th> <th class='bac_color font14' style='border-top:1px solid #afafaf;border-bottom:1px solid #afafaf;background: #fff !important; color:#444; padding:8px;'>Invoice Id</th> <th class='bac_color font14' style='border-top:1px solid #afafaf;border-bottom:1px solid #afafaf;background: #fff !important; color:#444; padding:8px;'>Payment Mode</th> <th class='bac_color font14' style='border-top:1px solid #afafaf;border-bottom:1px solid #afafaf;background: #fff !important; color:#444; padding:8px;text-align:center'>Debit</th> <th class='bac_color font14' style='border-top:1px solid #afafaf;border-bottom:1px solid #afafaf;background: #fff !important; color:#444; padding:8px;text-align:right'>Credit</th> </tr> </thead> <tbody> $iidks_items <tr> <td class='py-0' style='border-top:1px solid #afafaf;border-bottom:1px solid #afafaf;background: #fff !important; color:#444; padding:8px;'></td> <td class='py-0' style='border-top:1px solid #afafaf;border-bottom:1px solid #afafaf;background: #fff !important; color:#444; padding:8px;'></td> <td class='py-0' style='border-top:1px solid #afafaf;border-bottom:1px solid #afafaf;background: #fff !important; color:#444; padding:8px;'><strong >Sub Total</strong></td> <td class='font12 py-0' style='border-top:1px solid #afafaf;border-bottom:1px solid #afafaf;background: #fff !important; color:#444; padding:8px;text-align:right'><strong style='font-weight:bold;color:#000;font-size:16px'>$totatdebetAmt</strong></td> <td class='font12 py-0' style='border-top:1px solid #afafaf;border-bottom:1px solid #afafaf;background: #fff !important; color:#444; padding:8px;text-align:right'><strong style='font-weight:bold;color:#000;font-size:16px'>$creditAmt</strong></td> </tr> <tr> <td class='py-0' style='padding:8px;'></td> <td class='py-0' style='padding:8px;'></td> <td class='py-0' style='padding:8px;'></td> <td class='font12 py-0' style='padding:8px;'><strong>Balance Amount </strong><small>($currency)</small></td> <td class='font12 py-0' style='padding:8px;'><strong style='font-weight:bold;color:#000;font-size:18px'>$remainAmt</strong></td> </tr> </tbody> </table> <div class='col-md-12 p-0'> <p style='display:block;background:#afafaf;text-align:center;color:#fff;font-size:13px;'>Balance Amount in $currency - ".numberTowords("$remainAmt")."</p> </div> <div> <div class='row pt-4'> <div class='col-md-6 float-left'> </div> <div class='col-md-6 float-right'> <p class='text-center m-0 font12'>For <strong>$company_name</strong><p> <p class='text-center'><img src='signature/$signature' width='95'></p> <p class='text-center'>Authorized Signatory</p> </div> </div> </div> </section> </body> </html>"; require ("mpdf/mpdf.php"); $mpdf = new mPDF('A4'); $mpdf->allow_charset_conversion = true; $stylesheet = file_get_contents('bootstrap.min.css'); $mpdf->WriteHTML($stylesheet,1); $stylesheet1 = file_get_contents('styleInvoice3.css'); $mpdf->WriteHTML($stylesheet1,1); $mpdf->showImageErrors = true; $mpdf->SetDisplayMode('fullpage'); $mpdf->WriteHTML($html); $mpdf->Output(); //track record $logindk_usersId=$_SESSION["login_usersId"]; $logindk_emailId=$_SESSION["login_email"]; exit; //////////////////////////////////////////////// ///////////// End If Customer ////////////// //////////////////////////////////////////////// } else if ($report_type == 'purchase_order'){ $objPHPExcel = new PHPExcel(); // Set the active Excel worksheet to sheet 0 $objPHPExcel->setActiveSheetIndex(0); $style = array( 'font' => array( 'bold' => true ) , 'borders' => array( 'allborders' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '000000' ) ) ) ); $border = array( 'borders' => array( 'allborders' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '000000' ) ) ) ); $style1 = array( 'alignment' => array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, ) ); $headerStyle = array( 'font' => array( 'bold' => true, 'color' => array( 'rgb' => '000000' ) , 'size' => 20, 'name' => 'Verdana' ) ); $secondLineStyle = array( 'font' => array( 'bold' => true, 'color' => array( 'rgb' => '000000' ) , 'size' => 15, 'name' => 'Verdana' ) ); $objPHPExcel->getDefaultStyle() ->applyFromArray(array( 'borders' => array( 'top' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) , 'bottom' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) , 'left' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) , 'right' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) ) )); $objPHPExcel->getDefaultStyle() ->applyFromArray($style1); //apply the style on column A row 1 to Column B row 1 $objPHPExcel->getActiveSheet() ->getStyle('A1:M1') ->applyFromArray($style); // $objPHPExcel->getActiveSheet()->getStyle('A4:I4')->applyFromArray($style); // $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->applyFromArray($headerStyle); // $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->applyFromArray($secondLineStyle); $objPHPExcel->getActiveSheet() ->setCellValue('A1', "Date"); $objPHPExcel->getActiveSheet() ->setCellValue('B1', "Purchase Order"); $objPHPExcel->getActiveSheet() ->setCellValue('C1', "Manufacturer"); $objPHPExcel->getActiveSheet() ->setCellValue('D1', "Bank Charges (in USD)"); $objPHPExcel->getActiveSheet() ->setCellValue('E1', "Sub Total Amount (in USD)"); $objPHPExcel->getActiveSheet() ->setCellValue('F1', "Total Bill Amount (in USD)"); $objPHPExcel->getActiveSheet() ->setCellValue('G1', "Paid Amount (in USD)"); $objPHPExcel->getActiveSheet() ->setCellValue('H1', "Remain Amount (in USD)"); $objPHPExcel->getActiveSheet() ->getColumnDimension('A') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('B') ->setWidth(25); $objPHPExcel->getActiveSheet() ->getColumnDimension('C') ->setWidth(25); $objPHPExcel->getActiveSheet() ->getColumnDimension('D') ->setWidth(40); $objPHPExcel->getActiveSheet() ->getColumnDimension('E') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('F') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('G') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('H') ->setWidth(20); $rowCount = 2; $i = 1; $totalPoAmtPaid=0; $inv_due_amt=0; $inv_payment_recived=0; $inv_other_chges=0; $inv_bank_chages=0; $subtotal_price=0; $remain_payment_amt=0; $sql = "SELECT * FROM `purchase_order` WHERE `data_delete` != 'yes' AND ORDER BY `id` DESC"; $result = $conn->query($sql) or die(mysqli_error($conn)); if (mysqli_num_rows($result) > 0) { while ($row = mysqli_fetch_assoc($result)) { if (date("Y-m-d", strtotime($row["order_date"])) >= $from && date("Y-m-d", strtotime($row["order_date"])) <= $to) { $status = ""; if ($row["data_delete"] == "yes") { $status = "Cancelled"; } $payment = 0.00; $tax = 0.00; $po_id = $row["po_id"]; $manufacturer_id = $row['manufacturer']; $amt_in_usd = $row['amt_in_usd']; $sub_total_amt_in_usd = $row['sub_total_amt_in_usd']; $sub_total = sprintf('%0.2f', $row["subtotal_price"]); $b_chagred=$row["bank_charges"]; $amount_paid = sprintf('%0.2f', $row["amount_paind"]); if($b_chagred==''){ $b_chagred=0.0; }else{ $b_chagred=$b_chagred; } $sqlGetPayment = "SELECT amount FROM `purchase_order_payments` WHERE `po_no` = '$po_id'"; $resultGetPayment = $conn->query($sqlGetPayment); if ($resultGetPayment) { if (mysqli_num_rows($resultGetPayment) > 0) { while ($rowGetPayment = mysqli_fetch_assoc($resultGetPayment)) { $payment = $rowGetPayment["amount"]; $payment_in_usd = $rowGetPayment["amount_in_usd"]; } } } $get_bpn = mysqli_query($conn, "SELECT * FROM `manufacturer` where id='$manufacturer_id'"); $get_bpn_row = mysqli_fetch_assoc($get_bpn); $final_bill_pay_name = $get_bpn_row['manufacturer_display_name']; $m_currency = $get_bpn_row['currency']; if($m_currency=='RMB'){ $query_c="SELECT * FROM `conversion_rate` ORDER BY `conversion_rate`.`id` DESC LIMIT 1"; $result_c = mysqli_query($conn, $query_c); $row_c = mysqli_fetch_assoc($result_c); $last_rmb = $row_c["rmb"]; // $payment1 = $payment/$last_rmb; // $b_chagred=$b_chagred/$last_rmb; // $sub_total=$sub_total/$last_rmb; // $amount_paid=$amount_paid/$last_rmb; }else{ $last_rmb=1; // $b_chagred=$b_chagred/$last_rmb; // $payment1 = $payment/$last_rmb; // $sub_total=$sub_total/$last_rmb; // $amount_paid=$amount_paid/$last_rmb; } if($payment_in_usd==''){ $payment=$payment; $payment1 = $payment/$last_rmb; }else{ $payment=$payment_in_usd; $payment1=$payment; } if($amt_in_usd==''){ $amount_paid=$amount_paid; $sub_total=$sub_total; $sub_total=$sub_total/$last_rmb; $amount_paid=$amount_paid/$last_rmb; }else{ $amount_paid=$amt_in_usd; $sub_total=$sub_total_amt_in_usd; } $b_chagred=$b_chagred/$last_rmb; $totalPoAmtPaid+=$amount_paid; $remain_amt=$amount_paid-$payment1; $inv_payment_recived+=$payment1; $subtotal_price+=$sub_total; $inv_bank_chages+=$b_chagred; $remain_payment_amt+=$remain_amt; $objPHPExcel->getActiveSheet() ->setCellValue('A' . $rowCount, $row["order_date"]); $objPHPExcel->getActiveSheet() ->setCellValue('B' . $rowCount, $row["po_id"]); $objPHPExcel->getActiveSheet() ->setCellValue('C' . $rowCount, $final_bill_pay_name); $objPHPExcel->getActiveSheet() ->setCellValue('D' . $rowCount, $b_chagred); $objPHPExcel->getActiveSheet() ->setCellValue('E' . $rowCount, $sub_total); $objPHPExcel->getActiveSheet() ->setCellValue('F' . $rowCount, $amount_paid ); $objPHPExcel->getActiveSheet() ->setCellValue('G' . $rowCount, $payment); $objPHPExcel->getActiveSheet() ->setCellValue('H' . $rowCount, $amount_paid-$payment); $objPHPExcel->getActiveSheet() ->getStyle('B' . $rowCount . ':H' . $rowCount)->getNumberFormat() ->setFormatCode('0.00'); $objPHPExcel->getActiveSheet() ->getStyle('A' . $rowCount . ':H' . $rowCount)->applyFromArray($border); $rowCount = $rowCount + 1; } } } $objPHPExcel->getActiveSheet() ->setCellValue('D' . $rowCount, $inv_bank_chages); $objPHPExcel->getActiveSheet() ->setCellValue('E' . $rowCount, $subtotal_price); $objPHPExcel->getActiveSheet() ->setCellValue('F' . $rowCount, $totalPoAmtPaid); $objPHPExcel->getActiveSheet() ->setCellValue('G' . $rowCount, $inv_payment_recived); $objPHPExcel->getActiveSheet() ->setCellValue('H' . $rowCount, $remain_payment_amt); /////////////////////////////////////////// ///////////// End if invoice ////////////// /////////////////////////////////////////// header('Content-Type: application/vnd.ms-excel'); header("Content-Disposition: attachment;filename=po_report_from_" . $from. "_to_".$to . ".xls"); } else if ($report_type == 'in_out_report'){ ////////////////////////////////////// //////////// strat If in out report///////// ////////////////////////////////////// $objPHPExcel = new PHPExcel(); // Set the active Excel worksheet to sheet 0 $objPHPExcel->setActiveSheetIndex(0); $style = array( 'font' => array( 'bold' => true ) , 'borders' => array( 'allborders' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '000000' ) ) ) ); $border = array( 'borders' => array( 'allborders' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '000000' ) ) ) ); $style1 = array( 'alignment' => array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, ) ); $headerStyle = array( 'font' => array( 'bold' => true, 'color' => array( 'rgb' => '000000' ) , 'size' => 20, 'name' => 'Verdana' ) ); $secondLineStyle = array( 'font' => array( 'bold' => true, 'color' => array( 'rgb' => '000000' ) , 'size' => 15, 'name' => 'Verdana' ) ); $objPHPExcel->getDefaultStyle() ->applyFromArray(array( 'borders' => array( 'top' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) , 'bottom' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) , 'left' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) , 'right' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) ) )); $objPHPExcel->getDefaultStyle() ->applyFromArray($style1); //apply the style on column A row 1 to Column B row 1 $objPHPExcel->getActiveSheet() ->getStyle('A1:P1') ->applyFromArray($style); // $objPHPExcel->getActiveSheet()->getStyle('A4:I4')->applyFromArray($style); // $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->applyFromArray($headerStyle); // $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->applyFromArray($secondLineStyle); $objPHPExcel->getActiveSheet() ->setCellValue('A1', "PI NO"); $objPHPExcel->getActiveSheet() ->setCellValue('B1', "PI Date"); $objPHPExcel->getActiveSheet() ->setCellValue('C1', "PI Value"); $objPHPExcel->getActiveSheet() ->setCellValue('D1', "Client Name"); $objPHPExcel->getActiveSheet() ->setCellValue('E1', "PO No"); $objPHPExcel->getActiveSheet() ->setCellValue('F1', "PO Date"); $objPHPExcel->getActiveSheet() ->setCellValue('G1', "PO Issue to"); $objPHPExcel->getActiveSheet() ->setCellValue('H1', "PO Value"); $objPHPExcel->getActiveSheet() ->setCellValue('I1', "Payment to Vendor"); $objPHPExcel->getActiveSheet() ->setCellValue('J1', "Payment Date"); $objPHPExcel->getActiveSheet() ->setCellValue('K1', "Invoice No"); $objPHPExcel->getActiveSheet() ->setCellValue('L1', "Invoice Date"); $objPHPExcel->getActiveSheet() ->setCellValue('M1', "Invoice Value"); $objPHPExcel->getActiveSheet() ->setCellValue('N1', "Client Name"); $objPHPExcel->getActiveSheet() ->setCellValue('O1', "Payment Received"); $objPHPExcel->getActiveSheet() ->setCellValue('P1', "Payment Date"); $objPHPExcel->getActiveSheet() ->setCellValue('Q1', "Remarks, if any"); $objPHPExcel->getActiveSheet() ->getColumnDimension('A') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('B') ->setWidth(25); $objPHPExcel->getActiveSheet() ->getColumnDimension('C') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('D') ->setWidth(40); $objPHPExcel->getActiveSheet() ->getColumnDimension('E') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('F') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('G') ->setWidth(40); $objPHPExcel->getActiveSheet() ->getColumnDimension('H') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('I') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('J') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('K') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('L') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('M') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('N') ->setWidth(40); $objPHPExcel->getActiveSheet() ->getColumnDimension('O') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('P') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('Q') ->setWidth(40); $rowCount = 2; $i = 1; $pro_totalAmt=0; $inv_other_chges=0; $inv_bank_chages=0; $totalPoAmtPaid=0; $sql = "SELECT * FROM `proforma` WHERE data_delete != 'yes' ORDER BY Date ASC"; $result = $conn->query($sql) or die(mysqli_error($conn)); if (mysqli_num_rows($result) > 0) { while ($row = mysqli_fetch_assoc($result)) { if (date("Y-m-d", strtotime($row["date"])) >= $from && date("Y-m-d", strtotime($row["date"])) <= $to) { $status = ""; if ($row["data_delete"] == "cancel") { $status = "Cancelled"; } $helping_id = $row['bill_pay_name']; $proforma_id = $row['proforma_id']; $proforma_date = $row['date']; $p_bank_chages=$row["bank_charge"]; $amount_paid = sprintf('%0.2f', $row["amount_paid"]); if($p_bank_chages>0){ $amount_paid=$amount_paid-$p_bank_chages; } $get_bpn = mysqli_query($conn, "SELECT * FROM `contact` where id='$helping_id'"); $get_bpn_row = mysqli_fetch_assoc($get_bpn); $final_bill_pay_name = $get_bpn_row['bill_display_name']; $pro_totalAmt+=$amount_paid; $objPHPExcel->getActiveSheet() ->setCellValue('A' . $rowCount, $proforma_id); $objPHPExcel->getActiveSheet() ->setCellValue('B' . $rowCount, $proforma_date); $objPHPExcel->getActiveSheet() ->setCellValue('C' . $rowCount, $amount_paid); $objPHPExcel->getActiveSheet() ->setCellValue('D' . $rowCount, $final_bill_pay_name); $objPHPExcel->getActiveSheet() ->setCellValue('E' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('F' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('G' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('H' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('I' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('J' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('K' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('L' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('M' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('N' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('O' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('P' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('Q' . $rowCount, ""); $sql_po = "SELECT * FROM `purchase_order` WHERE `purchase_order`.`proforma_id`='$proforma_id' AND `data_delete` != 'yes'"; $result_po = $conn->query($sql_po) or die(mysqli_error($conn)); if (mysqli_num_rows($result_po) > 0){ while ($row_po = mysqli_fetch_assoc($result_po)){ $po_id = $row_po["po_id"]; $po_order_date = $row_po["order_date"]; $po_manufacturer_id = $row_po['manufacturer']; $po_amt_in_usd = $row_po['amt_in_usd']; $po_sub_total_amt_in_usd = $row_po['sub_total_amt_in_usd']; $po_sub_total = sprintf('%0.2f', $row_po["subtotal_price"]); $po_b_chagred=$row_po["bank_charges"]; $po_amount_paid = sprintf('%0.2f', $row_po["amount_paind"]); $po_proforma_id = $row_po["proforma_id"]; $po_get_bpn = mysqli_query($conn, "SELECT * FROM `manufacturer` where id='$po_manufacturer_id'"); $po_get_bpn_row = mysqli_fetch_assoc($po_get_bpn); $po_final_bill_pay_name = $po_get_bpn_row['manufacturer_display_name']; $po_currency = $po_get_bpn_row['currency']; if($po_amt_in_usd==''){ $po_amount_paid=$po_amount_paid; }else{ $po_amount_paid=$po_amt_in_usd; } $totalPoAmtPaid+=$po_amount_paid; $objPHPExcel->getActiveSheet() ->setCellValue('A' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('B' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('C' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('D' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('E' . $rowCount, $po_id ." ".$po_proforma_id); $objPHPExcel->getActiveSheet() ->setCellValue('F' . $rowCount, $po_order_date); $objPHPExcel->getActiveSheet() ->setCellValue('G' . $rowCount, $po_final_bill_pay_name); $objPHPExcel->getActiveSheet() ->setCellValue('H' . $rowCount, $po_amount_paid); $objPHPExcel->getActiveSheet() ->setCellValue('I' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('J' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('K' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('L' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('M' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('N' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('O' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('P' . $rowCount, ""); $objPHPExcel->getActiveSheet() ->setCellValue('Q' . $rowCount, ""); $rowCount = $rowCount + 1; } } $objPHPExcel->getActiveSheet() ->getStyle('B' . $rowCount . ':I' . $rowCount)->getNumberFormat() ->setFormatCode('0.00'); $objPHPExcel->getActiveSheet() ->setCellValue('Q' . $rowCount, $status); $objPHPExcel->getActiveSheet() ->getStyle('A' . $rowCount . ':Q' . $rowCount)->applyFromArray($border); $rowCount = $rowCount + 1; } } } $objPHPExcel->getActiveSheet() ->setCellValue('G' . $rowCount, $inv_bank_chages); $objPHPExcel->getActiveSheet() ->setCellValue('H' . $rowCount, $inv_other_chges); $objPHPExcel->getActiveSheet() ->setCellValue('C' . $rowCount, $pro_totalAmt); /////////////////////////////////////////// ///////////// End If Proforma ////////////// /////////////////////////////////////////// header('Content-Type: application/vnd.ms-excel'); header("Content-Disposition: attachment;filename=in_out_report_from_" . $from. "_to_".$to . ".xls"); } else if ($report_type == 'all_transaction'){ ////////////////////////////////////// //////////// start payment invoice///////// ////////////////////////////////////// $objPHPExcel = new PHPExcel(); // Set the active Excel worksheet to sheet 0 $objPHPExcel->setActiveSheetIndex(0); $style = array( 'font' => array( 'bold' => true ) , 'borders' => array( 'allborders' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '000000' ) ) ) ); $border = array( 'borders' => array( 'allborders' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '000000' ) ) ) ); $style1 = array( 'alignment' => array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, ) ); $headerStyle = array( 'font' => array( 'bold' => true, 'color' => array( 'rgb' => '000000' ) , 'size' => 20, 'name' => 'Verdana' ) ); $secondLineStyle = array( 'font' => array( 'bold' => true, 'color' => array( 'rgb' => '000000' ) , 'size' => 15, 'name' => 'Verdana' ) ); $objPHPExcel->getDefaultStyle() ->applyFromArray(array( 'borders' => array( 'top' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) , 'bottom' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) , 'left' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) , 'right' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => PHPExcel_Style_Color::COLOR_WHITE ) ) ) )); $objPHPExcel->getDefaultStyle() ->applyFromArray($style1); //apply the style on column A row 1 to Column B row 1 $objPHPExcel->getActiveSheet() ->getStyle('A1:M1') ->applyFromArray($style); // $objPHPExcel->getActiveSheet()->getStyle('A4:I4')->applyFromArray($style); // $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->applyFromArray($headerStyle); // $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->applyFromArray($secondLineStyle); $objPHPExcel->getActiveSheet() ->setCellValue('A1', "Date"); $objPHPExcel->getActiveSheet() ->setCellValue('B1', "Customer Name"); $objPHPExcel->getActiveSheet() ->setCellValue('C1', "Amount"); $objPHPExcel->getActiveSheet() ->setCellValue('D1', "Tax Deduction"); $objPHPExcel->getActiveSheet() ->setCellValue('E1', "Invoice No."); $objPHPExcel->getActiveSheet() ->setCellValue('F1', "Transaction Mode"); $objPHPExcel->getActiveSheet() ->getColumnDimension('A') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('B') ->setWidth(25); $objPHPExcel->getActiveSheet() ->getColumnDimension('C') ->setWidth(25); $objPHPExcel->getActiveSheet() ->getColumnDimension('D') ->setWidth(40); $objPHPExcel->getActiveSheet() ->getColumnDimension('E') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('F') ->setWidth(20); $objPHPExcel->getActiveSheet() ->getColumnDimension('G') ->setWidth(20); $rowCount = 2; $i = 1; $sql = "SELECT payment.* ,contact.bill_display_name FROM payment left join contact on payment.company_id = contact.id WHERE payment.payment_date >= '$fromdate' AND payment.payment_date <= '$todate' order by payment_date desc "; $result = $conn->query($sql) or die(mysqli_error($conn)); if (mysqli_num_rows($result) > 0) { while ($row = mysqli_fetch_assoc($result)) { $objPHPExcel->getActiveSheet() ->setCellValue('A' . $rowCount, $row["payment_date"]); $objPHPExcel->getActiveSheet() ->setCellValue('B' . $rowCount, $row["bill_display_name"]); $objPHPExcel->getActiveSheet() ->setCellValue('C' . $rowCount, $row["amount"]); $objPHPExcel->getActiveSheet() ->setCellValue('D' . $rowCount, $row["tax_deduction"]); $objPHPExcel->getActiveSheet() ->setCellValue('E' . $rowCount, $row["reference"]); $objPHPExcel->getActiveSheet() ->setCellValue('F' . $rowCount, $row["mode"]); $rowCount ++; } } /////////////////////////////////////////// ///////////// End if All payment ////////////// /////////////////////////////////////////// header('Content-Type: application/vnd.ms-excel'); header("Content-Disposition: attachment;filename=payment_report_from_" . $from. "_to_".$to . ".xls"); } // header('Content-Type: application/vnd.ms-excel'); // header("Content-Disposition: attachment;filename=invoice_report_" . date("Y-m-d") . ".xls"); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); ob_end_clean(); $objWriter->save('php://output'); ?>