2-;R$?1祧WgբM=̫LH)H(I^PzH4/#*:r5\7/gţ'oW:'&, 7oU^1J_En<滭v<=t]H"=kHRDqܷ(WY5,Pe#yS~Y$)--XofeX_-~ vQ KFlNeه7f~9>-N+nVof2Ǒo74d2i,Y.o=?:/+z4AECXO&aIRiY;'H !#D|n y&, "_oL 6+[A;:V5G q*qmS ZxJ .T'w4qF6@!PX1A~N[4X R+F/<4v( ^Ԅd\{Se>]ֿ*2geSJ*c]@ɚ. P30V=o~5HQ\8]d듖Moo#jg1P_n MΔ- c}Xc57JV=o~5HQ\8]d듖Moo#jg1VRD_ ި t !GE)ȗOHrćϧ:X|%|`FݛNa TkƵ̱8TtM ]r0S$hY"YJZțFP`(qa`AcǗYS<)KcJ?l?Abb)j0,83AXrE3VD87)@֒OpAI^TPh H}ul~<Ma1R4dj 5WޠhC9G: BpGDCيAoEzg ֨<:66-Ģef"FG6'àd*Nl* Q_HA @&6_KFoF;EM^kr5oiuj^9حx)vۖmB,-p/`-<}Z9G%!l'q$ouKCw\ŭ}MF0H3?S⻅:NW4`Nq'(Z A[ZLJvh.tA"⤛M{ ޼b("Phgd0艱 yB0Az55ㄒR4?3H>~A~}5g14"@ͷ\ qj'ºD^L^C(Z狱2w\08fv}L5;dtxV#e:d~!nR`fyNY|Bk桨^Hi1#ӹ/8!n^LsT} 7b쥔:̸W?Y644$|Ww a jU54:,8Y}' rk'6Bw+[e6B$9/3a8Ĩf̲C-^GW `x3BqEغ<4D)>b v*J鲫ƈp=œdC 5#O ̺*܆W aV!GD=4ٛdrM@ n!X@dX" [ϸ(ϻH?ڥ3 *ҭ.Ttԋ`b'CKTи4.k_m-\m%UjʙX 0K,:u{kd/6H\}=̏{"ϱcC6yf粖 s * &MXf>{*=M1Ctдa7y~~sGfVV=o~5HQ\8]d듖Moo#jg1 FyTӒii?Ka}Cr1$l`x4%[yh8^m}ÿຈNXR:Ez秵q# q[%g]|Ղ.l'zLXOV14 qhuOv] cKQ5!MJ(5U'GdBٙWN/B0D@xsG9v+8QU=*'#*מ?[i:4-~~sGfVV=o~5HQ\8]d듖Moo#jg1+dtOfoVllmyP>먪6BxdA+Ib8Vȟ@2G04C<ʃ[B.3<޺H06AYM6w+\c”z0+S 2sB8`M v<2FVw 9 @1FP Py.iVex.Ut6ж2?'.W FVw 9getActiveSheet()->SetCellValue($col.''.$rowCount,round($sTax,2)); $amount+=$sTax; $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,round($amount,2)); $incomeTax=$amount*odbc_result($cur,'incomeTax')/100; $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,round($incomeTax,2)); $amount-=$incomeTax; //$netAmount=$amount; $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,round($amount,2)); $rowCount++; } header('Content-Type: application/vnd.ms-excel'); //mime type //header('Content-Disposition: attachment;filename="you-file-name.xlsx"'); //tell browser what's the file name header("Content-Disposition: attachment;filename=$filename.xlsx"); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache //for write make a library object $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); //Or you can also use this library object for write $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); }elseif(isset($_GET['btnStockOut2xls'])){ //Payment Coal for Haadi $filename="StockOut".$_GET['btnStockOut2xls']; $from = date('Y-m-d',strtotime($_REQUEST['from'])); $to = date('Y-m-d',strtotime($_REQUEST['to'])); include('classesExcel/PHPExcel.php'); $objPHPExcel = new PHPExcel(); // set table header values $objPHPExcel->setActiveSheetIndex(0); $rowCount = 1; $col='A'; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'#'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Date'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Truck'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Supplier'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Mine'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Weight'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'f/Weight'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Freight'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Expenes'); $rowCount = 2; $query= "select * from itemInOut where (date_ between '$from' and '$to') and invno>0 and labid='".$_SESSION['labid']."' and recordType='Sale' order by date_ asc,invno asc"; $cur= odbc_exec($conn,$query ); while( odbc_fetch_row( $cur ) ) { //data print from the table $col='A'; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, $rowCount-1); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, date('d-m-Y',strtotime(odbc_result($cur,'date_')))); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,odbc_result( $cur, 'batchNo')); $accountName=getName($conn,"select stdName from student where ID='".odbc_result($cur, 'accountID')."' and recordType='Account'"); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,$accountName); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,odbc_result( $cur, 'remarks')); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,odbc_result( $cur, 'qty')); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,odbc_result( $cur, 'pPrice')); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,odbc_result( $cur, 'tPrice')); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,odbc_result( $cur, 'discount')); $rowCount++; } header('Content-Type: application/vnd.ms-excel'); //mime type //header('Content-Disposition: attachment;filename="you-file-name.xlsx"'); //tell browser what's the file name header("Content-Disposition: attachment;filename=$filename.xlsx"); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache //for write make a library object $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); //Or you can also use this library object for write $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); }elseif(isset($_GET['btnPaymentCoal2Xls'])){ //Payment Coal for Haadi $filename="Payments".$_GET['btnPaymentCoal2Xls']; $from = date('Y-m-d',strtotime($_REQUEST['from'])); $to = date('Y-m-d',strtotime($_REQUEST['to'])); include('classesExcel/PHPExcel.php'); $objPHPExcel = new PHPExcel(); // set table header values $objPHPExcel->setActiveSheetIndex(0); $rowCount = 1; $col='A'; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'#'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Date'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'V.No'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'From'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'To'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Remarks'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'In'); $rowCount = 2; $query= "select * from ledgerView where debit>0 and (date_ between '$from' and '$to') and paymentNo>0 and labid='".$_SESSION['labid']."' order by date_ asc,paymentNo asc"; $cur= odbc_exec($conn,$query ); while( odbc_fetch_row( $cur ) ) { //data print from the table $col='A'; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, $rowCount-1); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, date('d-m-Y',strtotime(odbc_result($cur,'date_')))); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,odbc_result( $cur, 'paymentNo')); $accountName=getName($conn,"select stdName from student where ID='".odbc_result($cur, 'fromActID')."' and recordType='Account'"); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,$accountName); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,odbc_result( $cur, 'stdName')); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,odbc_result( $cur, 'remarks')); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,odbc_result( $cur, 'debit')); $rowCount++; } header('Content-Type: application/vnd.ms-excel'); //mime type //header('Content-Disposition: attachment;filename="you-file-name.xlsx"'); //tell browser what's the file name header("Content-Disposition: attachment;filename=$filename.xlsx"); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache //for write make a library object $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); //Or you can also use this library object for write $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); }elseif(isset($_GET['btnStockIn2xls'])){ //invoice for Haadi $filename="Invoice".$_GET['btnStockIn2xls']; $from = date('Y-m-d',strtotime($_REQUEST['from'])); $to = date('Y-m-d',strtotime($_REQUEST['to'])); include('classesExcel/PHPExcel.php'); $objPHPExcel = new PHPExcel(); // set table header values $objPHPExcel->setActiveSheetIndex(0); $rowCount = 1; $col='A'; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'#'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Date'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Truck'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Supplier'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Mine'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Waight'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Rate'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Amount'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Fraight'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Expenes'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Total'); $rowCount = 2; //$query= "select * from itemInOut where (date_ between '$from' and '$to') and invno>0 and labid='".$_SESSION['labid']."' and recordType='stockOutInvoice' order by date_ asc,invno asc"; $query= "select * from itemInOut where (date_ between '$from' and '$to') and invno>0 and labid='".$_SESSION['labid']."' and recordType='Purchase' order by date_ asc,invno asc"; $cur= odbc_exec($conn,$query ); while( odbc_fetch_row( $cur ) ) { //data print from the table $col='A'; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, $rowCount-1); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, date('d-m-y',strtotime(odbc_result($cur,'date_')))); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,odbc_result( $cur, 'batchNo')); $accountName=getName($conn,"select stdName from student where ID='".odbc_result($cur, 'accountID')."' "); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,$accountName); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,odbc_result( $cur, 'remarks')); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,round(odbc_result( $cur, 'qty' ),2)); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,round(odbc_result( $cur, 'pPrice' ),2)); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,round((odbc_result( $cur, 'qty')*odbc_result( $cur, 'pPrice')),2)); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,round(odbc_result( $cur, 'qty')*odbc_result( $cur, 'tPrice' ),2)); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,round(odbc_result( $cur, 'discount' ),2)); //exp $amount=(odbc_result( $cur,'qty')*odbc_result( $cur,'pPrice'))+((odbc_result( $cur, 'qty')*odbc_result( $cur,'tPrice'))-odbc_result( $cur,'Discount')); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,round($amount,2)); //exp $rowCount++; } header('Content-Type: application/vnd.ms-excel'); //mime type //header('Content-Disposition: attachment;filename="you-file-name.xlsx"'); //tell browser what's the file name header("Content-Disposition: attachment;filename=$filename.xlsx"); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache //for write make a library object $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); //Or you can also use this library object for write $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); }elseif(isset($_GET['btnInvoice2xls'])){ //invoice for Haadi $filename="Invoice".$_GET['btnInvoice2xls']; $from = date('Y-m-d',strtotime($_REQUEST['from'])); $to = date('Y-m-d',strtotime($_REQUEST['to'])); include('classesExcel/PHPExcel.php'); $objPHPExcel = new PHPExcel(); // set table header values $objPHPExcel->setActiveSheetIndex(0); $rowCount = 1; $col='A'; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'#'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Date'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Supplier'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Particular'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Waight'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Rate'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Amount'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Premium'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Discount'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Total'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'salesTax'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Total'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'incomeTax'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Net-Total'); $rowCount = 2; $query= "select * from itemInOut where (date_ between '$from' and '$to') and invno>0 and labid='".$_SESSION['labid']."' and recordType='stockOutInvoice' order by date_ asc,invno asc"; $cur= odbc_exec($conn,$query ); while( odbc_fetch_row( $cur ) ) { //data print from the table $col='A'; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, $rowCount-1); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, date('d-m-y',strtotime(odbc_result($cur,'date_')))); $accountName=getName($conn,"select stdName from student where ID='".odbc_result($cur, 'accountID')."' "); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,$accountName); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,odbc_result( $cur, 'remarks')); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,round(odbc_result( $cur, 'qty' ),2)); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,round(odbc_result( $cur, 'pPrice' ),2)); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,round((odbc_result( $cur, 'qty')*odbc_result( $cur, 'pPrice')),2)); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,round(odbc_result( $cur, 'tPrice' ),2)); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,round(odbc_result( $cur, 'discount' ),2)); //exp $amount=(odbc_result( $cur,'qty')*odbc_result( $cur,'pPrice'))+(odbc_result( $cur,'tPrice')-odbc_result( $cur,'Discount')); $sTax=$amount*odbc_result($cur,'sTax')/100; //echo "".$amount; $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,round($amount,2)); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,round($sTax,2)); $amount+=$sTax; $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,round($amount,2)); $incomeTax=$amount*odbc_result($cur,'incomeTax')/100; $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,round($incomeTax,2)); $amount-=$incomeTax; $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,round($amount,2)); $rowCount++; } //$rowCount++; header('Content-Type: application/vnd.ms-excel'); //mime type //header('Content-Disposition: attachment;filename="you-file-name.xlsx"'); //tell browser what's the file name header("Content-Disposition: attachment;filename=$filename.xlsx"); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache //for write make a library object $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); //Or you can also use this library object for write $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); }elseif(isset($_GET['btnProforma'])){ $filename="closing".$_GET['btnProforma']; $date_=$_GET['date_']; //$from=$_GET['from']; //$to=$_GET['to']; include('classesExcel/PHPExcel.php'); $objPHPExcel = new PHPExcel(); // set table header values $objPHPExcel->setActiveSheetIndex(0); $rowCount = 1; $col='A'; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'#'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Date'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'YearlyNo'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'MonthlyNo'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'MR No'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Name'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Diagnose'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Consultant'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Adm Date'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'DOD'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'LOS'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Outcome'); $rowCount = 2; $balance=$total=0; $query= "select DATEDIFF(day,doa,dod) as los,consultant,doa,dod,mrno,monthlyNo,Diagnose,name,yearlyno,outcome,monthlyNo,districtID,patientID,date_,regCharges,Procedures,diseaseID from patientTab where labid='".$_SESSION['labid']."' and patientID>=0 "; if($date_!="") $query.=" and date_='$date_' "; if($_SESSION['userID']!='4183') $query.=" and userID='".$_SESSION['userID']."' "; $query.=" order by date_ desc,yearlyno desc"; //exit(); $cur= odbc_exec($conn,$query ); while( odbc_fetch_row( $cur ) ) { //data print from the table $col='A'; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, $rowCount-1); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, date('d-m-y',strtotime(odbc_result($cur,'date_')))); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, odbc_result( $cur, "yearlyno" )); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, odbc_result( $cur, "monthlyNo" )); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, odbc_result( $cur, "mrno" )); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, odbc_result( $cur, "name" )); //$col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, getHead($conn,odbc_result( $cur,'districtID'))); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, odbc_result( $cur, "diagnose" )); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, odbc_result( $cur, "consultant" )); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, date("d-m-Y", strtotime(odbc_result($cur,'doa')))); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, date("d-m-Y", strtotime(odbc_result($cur,'dod')))); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, odbc_result( $cur, "los")); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, odbc_result( $cur, "outcome" )); if(strtoupper(odbc_result($cur,"outcome"))=='EXPIRED')$objPHPExcel->getActiveSheet()->getStyle($col.''.$rowCount)->applyFromArray($styleArray); //if(strtoupper(odbc_result($cur,"outcome"))=='EXPIRED')$objPHPExcel->getActiveSheet()->getStyle($rowCount)->applyFromArray($styleArray); $rowCount++; } $rowCount++; header('Content-Type: application/vnd.ms-excel'); //mime type //header('Content-Disposition: attachment;filename="you-file-name.xlsx"'); //tell browser what's the file name header("Content-Disposition: attachment;filename=$filename.xlsx"); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache //for write make a library object $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); //Or you can also use this library object for write $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); }elseif(isset($_GET['btnInOutClosing'])){ $filename="closing".$_GET['btnInOutClosing']; $from=$_GET['from']; $to=$_GET['to']; $section_=$_GET['section_']; include('classesExcel/PHPExcel.php'); $objPHPExcel = new PHPExcel(); // set table header values $objPHPExcel->setActiveSheetIndex(0); $rowCount = 1; $col='A'; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'#'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Date'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Head'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'Description'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'IN'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'OUT'); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,'RB'); $rowCount = 2; $balance=$total=0; $query="select * from ledger where (date_ between '$from' and '$to') "; if($section_!=""){ $query.=" and accountID='$section_' "; $query2="select admFee+(select isnull(sum(debit-credit),0) from ledger where accountID='$section_' and date_<'$from') from student where ID='$section_' "; //$balance=getName($conn,"select isnull(sum(debit-credit),0) from ledger where accountID='$section_' "); $balance=getName($conn,$query2); //echo "Pre-Balance=".round($balance).""; } $query.=" order by date_,time_ asc "; $cur= odbc_exec( $conn, $query ); while( odbc_fetch_row( $cur ) ) { //data print from the table $col='A'; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, $rowCount-1); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, date('d.m.y',strtotime(odbc_result($cur,'date_')))); //$branch=getBranch(getName($conn,"select stdName from student where ID='".odbc_result($cur,'userID')."' "); //$col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,$account); $head=""; if(odbc_result($cur,'vno')>0){ $head=getName($conn,"select stdName from student where ID=(select expID from expenses where vno='".odbc_result($cur,'vno')."') "); }elseif(odbc_result($cur,'fromActID')>0){ $head=getName($conn,"select stdName from student where ID='".odbc_result($cur,'fromActID')."' "); }elseif(odbc_result($cur,'advanceNo')>0){ $head=getName($conn,"select stdName from student where ID=(select empID from Advances where advanceNo='".odbc_result($cur,'advanceNo')."') "); }elseif(odbc_result($cur,'partyNo')>0){ $head=getName($conn,"select hostname from bookingtbl where autoid='".odbc_result($cur,'partyNo')."' "); } $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, $head); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, odbc_result( $cur, "remarks" )); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, odbc_result( $cur, "debit" )); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount, odbc_result( $cur, "credit" )); $balance+=(odbc_result( $cur, "debit" )-odbc_result( $cur, "credit" )); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($col.''.$rowCount,$balance); $rowCount++; } $rowCount++; header('Content-Type: application/vnd.ms-excel'); //mime type //header('Content-Disposition: attachment;filename="you-file-name.xlsx"'); //tell browser what's the file name header("Content-Disposition: attachment;filename=$filename.xlsx"); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache //for write make a library object $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); //Or you can also use this library object for write $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); }elseif(isset($_GET['closingID'])){ //cash In $closingID=$_GET['closingID']; //$query="select * from employeeView where ID>0 and ID in (select distinct empID from empAllowancesMonthly where date_='$monthYear')"; $query="select * from cashIntbl where userID='".$_SESSION['userID']."' and closingID='$closingID' and recordTYpe='CASH_IN' order by date_ desc, section_,partyType,hostname "; $filename="Closing_$closingID";// .$monthYear; //exit(); //#DateBranchParticularsHostParty DateTimeAmountStatus"; include('classesExcel/PHPExcel.php'); $objPHPExcel = new PHPExcel(); // set table header values $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Serial'); $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Date'); $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Branch'); $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Particulars'); $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Host'); $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Party Date'); $objPHPExcel->getActiveSheet()->SetCellValue('G1', 'Time'); $objPHPExcel->getActiveSheet()->SetCellValue('H1', 'Amount'); $objPHPExcel->getActiveSheet()->SetCellValue('I1', 'Status'); $rowCount = 2; $total=0; $cur= odbc_exec( $conn, $query ); while( odbc_fetch_row( $cur ) ) { //data print from the table $date_=(date('d.m.Y',strtotime(odbc_result($cur,'date_')))); $partyDate=(date('d.m.Y',strtotime(odbc_result($cur,'partyDate')))); $Particulars=getName($conn,"select headName from AccountHeads where ID='".odbc_result($cur,'Particulars')."' "); $partyType=getName($conn,"select headName from AccountHeads where ID='".odbc_result($cur,'partyType')."' "); $paymenttype=getName($conn,"select headName from AccountHeads where ID='".odbc_result($cur,'paymenttype')."' "); $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $rowCount-1); $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount,$date_); $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount,getBranch(odbc_result($cur,"section_"))); $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount,$Particulars); $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, odbc_result( $cur, "hostname" )); $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowCount,$partyDate); $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount,$partyType); $objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowCount,odbc_result( $cur, "amount" )); $objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowCount,$paymenttype); $total+=odbc_result( $cur, "amount" ); $rowCount++; } $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount,"Total"); $objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowCount,$total); //Cash in Summary $total=0; $rowCount+=3; $rowNo=$rowCount; $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount,"CASH IN SUMMARY"); $rowCount++; $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount,'Status'); $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount,'Amount'); $rowCount++; $query="select paymenttype,sum(amount) as amount from cashIntbl where userID='".$_SESSION['userID']."' and closingID='$closingID' and recordTYpe='CASH_IN' group by paymenttype "; $cur= odbc_exec( $conn, $query ); while( odbc_fetch_row( $cur ) ) { $paymenttype=getName($conn,"select headName from AccountHeads where ID='".odbc_result($cur,'paymenttype')."' "); $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount,$paymenttype); $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount,odbc_result( $cur, "amount" )); $total+=odbc_result( $cur, "amount" ); $rowCount++; } $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount,"Total"); $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount,$total); //Cash OUT Summary $total=0; $rowCount=$rowNo; $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount,"CASH OUT SUMMARY"); $rowCount++; $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount,'Serial'); $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount,'Status'); $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowCount,'Amount'); $rowCount++; $query="select Particulars,sum(amount) as amount from cashIntbl where userID='".$_SESSION['userID']."' and closingID='$closingID' and recordTYpe='CASH_OUT' group by Particulars "; //$query="select Particulars,sum(amount) as amount from cashIntbl where userID='".$_SESSION['userID']."' and closingID='$closingID' and recordTYpe='CASH_OUT' group by Particulars "; $cur= odbc_exec( $conn, $query ); $cnt=1; while( odbc_fetch_row( $cur ) ) { $Particulars=getName($conn,"select headName from AccountHeads where ID='".odbc_result($cur,'Particulars')."' "); $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount,$cnt++); $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount,$Particulars); $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowCount,odbc_result( $cur, "amount" )); $total+=odbc_result( $cur, "amount" ); $rowCount++; } $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount,"Total"); $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowCount,$total); //Notes Count $total=0; $rowCount=$rowNo; $objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowCount,"Notes Count"); $rowCount++; $objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowCount,'Notes'); $objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowCount,'Count'); $objPHPExcel->getActiveSheet()->SetCellValue('J'.$rowCount,'Amount'); $rowCount++; $arr=array(5000,1000,500,100,50,20,10); for($i=0;$igetActiveSheet()->SetCellValue('H'.$rowCount,"$notes"); if($count!="" and $count>0){ $objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowCount,$count); $objPHPExcel->getActiveSheet()->SetCellValue('J'.$rowCount,($count*$notes)); $total+=($count*$notes); } $rowCount++; } $rowCount++; $objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowCount,"Total"); $objPHPExcel->getActiveSheet()->SetCellValue('J'.$rowCount,$total); $rowCount++; $netCash=getName($conn,"select isnull(sum(amount),0) as amount from cashIntbl where paymenttype='149' and recordTYpe='CASH_IN' "); $netCash=$netCash-getName($conn,"select isnull(sum(amount),0) as amount from cashIntbl where paymenttype='149' and recordTYpe='CASH_OUT' "); $objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowCount,"Net Cash"); $objPHPExcel->getActiveSheet()->SetCellValue('J'.$rowCount,$netCash); $rowCount++; $netCash=$total-$netCash; $objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowCount,"Difference"); $objPHPExcel->getActiveSheet()->SetCellValue('J'.$rowCount,$netCash); header('Content-Type: application/vnd.ms-excel'); //mime type header("Content-Disposition: attachment;filename=$filename.xlsx"); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache //for write make a library object $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); //Or you can also use this library object for write $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); }elseif(isset($_GET['btnSalary2xls2'])){ //Salary List 2 to XLS2 $section_=$_GET['section_']; $monthYear = date('Y-m-d',strtotime( $_GET['monthYear'])); $month = date('m',strtotime( $_GET['monthYear'])); $monthName = date('F',strtotime( $_GET['monthYear'])); $year = date('Y',strtotime( $_GET['monthYear'])); $query= "select distinct allowanceID,headName,order_ from AllowancesMonthlyView where headType='Allowances' and date_='$monthYear' order by order_ "; $cur= odbc_exec( $conn, $query ); $cnt=1;$all1=$ded1=0; $colTotal=$allID=$allName=array(); $colTotalcnt=1; while( odbc_fetch_row( $cur ) ) { $allID[$cnt]= odbc_result( $cur,'allowanceID' ); $colTotal[$colTotalcnt++]=0; $allName[$cnt++]=odbc_result($cur,'headName' ); } $query= "select distinct allowanceID,headName,order_ from AllowancesMonthlyView where headType='Deductions' and date_='$monthYear' order by order_ "; $cur= odbc_exec( $conn, $query ); $cnt=1;$all1=$ded1=0; $dedID=$dedName=array(); while( odbc_fetch_row( $cur ) ) { $dedID[$cnt]= odbc_result( $cur,'allowanceID' ); $colTotal[$colTotalcnt++]=0; $dedName[$cnt++]=odbc_result($cur,'headName' ); //$pdf->Cell(20,6,odbc_result($cur,'headName' ),1,0,'C'); } $query="select ID,stdName,dName,dptName,section_,status,date_ from employeeView where ID>0 and ID in (select distinct empID from empAllowancesMonthly where date_='$monthYear')"; if($section_>=0)$query.=" and section_='$section_'"; if(isset($_GET['EOBI']) and $_GET['EOBI']==1) $query.=" and EOBI=1"; if(isset($_GET['socialSecurity']) and $_GET['socialSecurity']==1) $query.=" and socialSecurity=1"; if(isset($_GET['Department']) and $_GET['Department']!='') $query.=" and Department in(".$_GET['Department'].")"; if(isset($_GET['Designition']) and $_GET['Designition']!='') $query.=" and Designation in(".$_GET['Designition'].")"; $query.=" order by section_,order_,dName,stdName"; $filename="Salary".$monthYear; include('classesExcel/PHPExcel.php'); $objPHPExcel = new PHPExcel(); // set table header values $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Serial'); $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Branch'); $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Name'); $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Department'); $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Designation'); $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Basic Salary'); $objPHPExcel->getActiveSheet()->SetCellValue('G1', 'Days'); $objPHPExcel->getActiveSheet()->SetCellValue('H1', 'Un-Avail Days'); $x='I'; for($i=1;$i<=count($allName);$i++){ $objPHPExcel->getActiveSheet()->SetCellValue($x.'1', $allName[$i]); $x++; } for($i=1;$i<=count($dedName);$i++){ $objPHPExcel->getActiveSheet()->SetCellValue($x.'1', $dedName[$i]); $x++; } $objPHPExcel->getActiveSheet()->SetCellValue($x.'1', 'Net'); for($i=1;$i<=100;$i++){ $colTotal[$i]=0; } $styleArray = array( 'font' => array( 'bold' => false, 'color' => array('rgb' => 'FF0000'), 'size' => 11, 'name' => 'Calibri'//'Verdana' )); $styleArrayFresh = array( 'font' => array( 'bold' => true, 'color' => array('rgb' => '006400'), //DarkGreen 'size' => 11, 'name' => 'Calibri'//'Verdana' )); $styleArrayTesting = [ 'font' => [ 'bold' => true, 'size' => 14, 'name' => 'Arial' ], 'alignment' => [ // 'horizontal' => Alignment::HORIZONTAL_CENTER, // 'vertical' => Alignment::VERTICAL_CENTER ], 'borders' => [ 'allBorders' => [ // 'borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => '000000'] ] ] ]; // $sheet->getStyle('A1')->applyFromArray($styleArray); //$phpExcel->getDefaultStyle()->applyFromArray($styleArray); apply to whole document $netTotal=0; $rowCount = 2; $cur= odbc_exec( $conn, $query ); while( odbc_fetch_row( $cur ) ) { //data print from the table $colTotalcnt=1; $empID=odbc_result( $cur, 'ID' ); $hireDate=date_01(date('Y-m-d',strtotime(odbc_result($cur,"date_")))); if(odbc_result($cur,"status")!=0) { //$objPHPExcel->getActiveSheet()->getStyle('A'.$rowCount)->getFont()->setBold(true); // give bold style to cell //$objPHPExcel->getActiveSheet()->getStyle('C'.$rowCount)->applyFromArray($styleArray); $objPHPExcel->getActiveSheet()->getStyle($rowCount)->applyFromArray($styleArray); //entire row }elseif($hireDate==$monthYear){ //freas arrival //$objPHPExcel->getActiveSheet()->getStyle('A'.$rowCount)->getFont()->setBold(true); // give bold style to cell //$objPHPExcel->getActiveSheet()->getStyle('C'.$rowCount)->applyFromArray($styleArray); $objPHPExcel->getActiveSheet()->getStyle($rowCount)->applyFromArray($styleArrayFresh); //entire row } $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $rowCount-1); $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount,getBranch(odbc_result($cur,"section_"))); $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, odbc_result($cur,"stdName")); $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount,odbc_result( $cur, "dptName" )); $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, odbc_result( $cur, "dName" )); $amt= getName($conn,"select isnull(sum(Amount),0) from allowancesView where allowanceID='5' and empID='$empID'"); if($amt>0) $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowCount,$amt); if($amt>0) $colTotal[$colTotalcnt++]+=$amt; else $colTotal[$colTotalcnt++]+=0; //$amt= getName($conn,"select count(distinct date_) from empAttendance where symbol not in ('A') and empID='$empID' and month(date_)='$month' and year(date_)='$year'"); $amt= getName($conn,"select count(distinct date_) from empAttendance where empID='$empID' and month(date_)='$month' and year(date_)='$year'"); if($amt>0)$objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount,$amt); if($amt>0) $colTotal[$colTotalcnt++]+=$amt; else $colTotal[$colTotalcnt++]+=0; $amt= getName($conn,"select REPLACE(Description,'Day','') from AllowancesMonthlyView where date_='$monthYear' and allowanceID='50' and empID='$empID'"); //Un-Avail Leaves=50 $objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowCount,trim($amt)); if($amt>0) $colTotal[$colTotalcnt++]+=$amt; else $colTotal[$colTotalcnt++]+=0; $x='I'; $allowance=$Deductions=0; for($i=1;$i<=count($allName);$i++){ $amt= getName($conn,"select isnull(sum(Amount),0) from AllowancesMonthlyView where date_='$monthYear' and allowanceID='".$allID[$i]."' and empID='$empID'"); $allowance+=$amt; if($amt>0) $objPHPExcel->getActiveSheet()->SetCellValue($x.''.$rowCount, $amt); if($amt>0) $colTotal[$colTotalcnt++]+=$amt; else $colTotal[$colTotalcnt++]+=0; $x++; } for($i=1;$i<=count($dedName);$i++){ $amt= getName($conn,"select isnull(sum(Amount),0) from AllowancesMonthlyView where date_='$monthYear' and allowanceID='".$dedID[$i]."' and empID='$empID'"); $Deductions+=$amt; if($amt>0) $objPHPExcel->getActiveSheet()->SetCellValue($x.''.$rowCount, $amt); if($amt>0) $colTotal[$colTotalcnt++]+=$amt; else $colTotal[$colTotalcnt++]+=0; $x++; } $objPHPExcel->getActiveSheet()->SetCellValue($x.''.$rowCount,($allowance)-($Deductions)); //$x++; //$objPHPExcel->getActiveSheet()->SetCellValue($x.''.$rowCount,odbc_result( $cur, "dptName" )); $netTotal+=(($allowance)-($Deductions)); $totalCols=$colTotalcnt; $colTotal[$colTotalcnt++]+=(($allowance)-($Deductions)); $rowCount++; } //$x--; $rowCount++; $x="F"; for($i=1;$i<=$totalCols;$i++){ //$objPHPExcel->getActiveSheet()->SetCellValue($x.''.$rowCount,round(($colTotal[$i]),2)); $objPHPExcel->getActiveSheet()->SetCellValue($x.''.($rowCount-1),'='."sum(".$x."2:".$x."".($rowCount-2).")"); $x++; } $objPHPExcel->getActiveSheet()->getStyle('A'.($rowCount-1).":Z".($rowCount-1))->getFont()->setBold(true); // give bold style to cell //$rowCount++; //$objPHPExcel->getActiveSheet()->SetCellValue($x.''.$rowCount,($netTotal)); $rowCount++; //$x++;$x++; $x="A"; $col1=$x++; $col2=$x++; $col3=$x++; $objPHPExcel->getActiveSheet()->SetCellValue($col1.''.$rowCount,'Serial'); $objPHPExcel->getActiveSheet()->SetCellValue($col2.''.$rowCount,'Department'); $objPHPExcel->getActiveSheet()->SetCellValue($col3.''.$rowCount,'Net Salary'); $query="select ID from employeeView where ID>0 and ID in (select distinct empID from empAllowancesMonthly where date_='$monthYear')"; if($section_>=0)$query.=" and section_='$section_'"; if(isset($_GET['EOBI']) and $_GET['EOBI']==1) $query.=" and EOBI=1"; if(isset($_GET['socialSecurity']) and $_GET['socialSecurity']==1) $query.=" and socialSecurity=1"; if(isset($_GET['Department']) and $_GET['Department']!='') $query.=" and Department in(".$_GET['Department'].")"; if(isset($_GET['Designition']) and $_GET['Designition']!='') $query.=" and Designation in(".$_GET['Designition'].")"; $ids=$query; $query= "select dptName,isnull(sum(Amount),0) as amt from AllowancesMonthlyView where empID in($ids) and date_='$monthYear' group by dptName "; $cur= odbc_exec( $conn, $query ); $total=0;$cnt=1; while( odbc_fetch_row( $cur ) ) { $rowCount++; $all1=getName($conn,"select isnull(sum(Amount),0) as amt from AllowancesMonthlyView where empID in($ids) and date_='$monthYear' and headType='Allowances' and dptName='".odbc_result( $cur, "dptName")."' "); $ded1=getName($conn,"select isnull(sum(Amount),0) as amt from AllowancesMonthlyView where empID in($ids) and date_='$monthYear' and headType='Deductions' and dptName='".odbc_result( $cur, "dptName")."' "); $objPHPExcel->getActiveSheet()->SetCellValue($col1.''.$rowCount,$cnt++); $objPHPExcel->getActiveSheet()->SetCellValue($col2.''.$rowCount,odbc_result( $cur, "dptName")); $objPHPExcel->getActiveSheet()->SetCellValue($col3.''.$rowCount,$all1-$ded1); $total=$total+($all1-$ded1); //$rowCount++; } $rowCount++; $objPHPExcel->getActiveSheet()->SetCellValue($col2.''.$rowCount,'Total'); $objPHPExcel->getActiveSheet()->SetCellValue($col3.''.$rowCount,$total); header('Content-Type: application/vnd.ms-excel'); //mime type header("Content-Disposition: attachment;filename=$filename.xlsx"); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache //for write make a library object $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); //Or you can also use this library object for write $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); }elseif(isset($_GET['btnSalary2xls'])){ //BroadSheet to XLS $section_=$_GET['section_']; $monthYear = date('Y-m-d',strtotime( $_GET['monthYear'])); $month = date('m',strtotime( $_GET['monthYear'])); $monthName = date('F',strtotime( $_GET['monthYear'])); $year = date('Y',strtotime( $_GET['monthYear'])); $query="select * from employeeView where ID>0 and ID in (select distinct empID from empAllowancesMonthly where date_='$monthYear')"; if($section_>=0)$query.=" and section_='$section_'"; if(isset($_GET['EOBI']) and $_GET['EOBI']==1) $query.=" and EOBI=1"; if(isset($_GET['socialSecurity']) and $_GET['socialSecurity']==1) $query.=" and socialSecurity=1"; if(isset($_GET['Department']) and $_GET['Department']!='') $query.=" and Department in(".$_GET['Department'].")"; if(isset($_GET['Designition']) and $_GET['Designition']!='') $query.=" and Designation in(".$_GET['Designition'].")"; $query.=" order by section_,order_,dName,stdName"; $filename="Salary".$monthYear; //exit(); include('classesExcel/PHPExcel.php'); $objPHPExcel = new PHPExcel(); // set table header values $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Serial'); $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Branch'); $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Name'); $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Designation'); $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Basic'); $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Allowances'); $objPHPExcel->getActiveSheet()->SetCellValue('G1', 'Deductions'); $objPHPExcel->getActiveSheet()->SetCellValue('H1', 'Net'); $rowCount = 2; $cur= odbc_exec( $conn, $query ); while( odbc_fetch_row( $cur ) ) { //data print from the table $empID=odbc_result( $cur, 'ID' ); $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $rowCount-1); $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount,getBranch(odbc_result($cur,"section_"))); $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, odbc_result($cur,"stdName")); $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, odbc_result( $cur, "dName" )); $Basic= getName($conn,"select sum(Amount) from AllowancesMonthlyView where date_='$monthYear' and headType='Allowances' and allowanceID=5 and empID='$empID'"); $all= getName($conn,"select isnull(sum(Amount),0) from AllowancesMonthlyView where date_='$monthYear' and headType='Allowances' and allowanceID!=5 and empID='$empID'"); $Deductions= getName($conn,"select isnull(sum(Amount),0) from AllowancesMonthlyView where date_='$monthYear' and headType='Deductions' and empID='$empID'"); //$Basic= getName($conn,"select sum(Amount) from AllowancesMonthlyView where headType='Allowances' and date_='$monthYear' and empID='$empID'"); //$Deductions= getName($conn,"select sum(Amount) from AllowancesMonthlyView where headType='Deductions' and date_='$monthYear' and empID='$empID'"); $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount,$Basic); $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowCount,$all); $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount,$Deductions); $objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowCount,($Basic+$all)-($Deductions)); $rowCount++; } header('Content-Type: application/vnd.ms-excel'); //mime type header("Content-Disposition: attachment;filename=$filename.xlsx"); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache //for write make a library object $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); //Or you can also use this library object for write $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); }elseif(isset($_GET['btnShowXls'])){ //BroadSheet to XLS $from = date_01(date('Y-m-d',strtotime($_GET['from']))); $to = date_01(date('Y-m-d',strtotime($_GET['to']))); $anyvalue= $_GET['anyvalue']; $d_head=$_GET['d_head']; $isActiveOnly=isset($_GET['isActiveOnly'])?1:0; $filename="BroadSheet".$from; $isActiveOnly=true; if(isset($_GET['isActiveOnly'])) $isActiveOnly=$_GET['isActiveOnly']; $f_ = (date('Y',strtotime($_GET['from']))); $t_ = (date('m',strtotime($_GET['from']))); if($t_<7){ $to = date_01(date('Y-m-d',strtotime("01-June-$f_"))); $f_-=1; $from = date_01(date('Y-m-d',strtotime("01-July-$f_"))); }else{ $from = date_01(date('Y-m-d',strtotime("01-July-$f_"))); $f_+=1; $to = date_01(date('Y-m-d',strtotime("01-June-$f_"))); } $f_ = date_01(date('M-Y',strtotime($from))); $t_ = date_01(date('M-Y',strtotime($to))); $cur= odbc_exec( $conn, "update monthlyemployeeallowances set date2=date_ where date2 is null" ); $cur= odbc_exec( $conn, "update monthlyemployeedeductions set date2=date_ where date2 is null" ); $d_head=$_GET['d_head']; $d_head2=$d_head+100; $query = "select employeeid,employeename,nicno,GP_fund_no from employee_personal_information where "; $query.=" employeeid in (select distinct employeeid from monthlyemployeedeductions where (date2 between '$from' and '$to') and d_head in('$d_head','$d_head2') and dAmount>0 ) "; if($isActiveOnly==true) $query.=" and salary='YES' "; if(isset($_GET['anyvalue']) and $_GET['anyvalue']!="") $query.=" and employeename like'%".$_GET['anyvalue']."%' "; $query.= " order by employeename"; //exit(); include('classesExcel/PHPExcel.php'); $objPHPExcel = new PHPExcel(); // set table header values $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Serial'); $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'PersonalNo'); $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Name'); $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'nicno'); $x='E';$cnt=1; for ($i=$from; $i<=$to; $i = date_01(date ("Y-m-d", strtotime("+31 days", strtotime($i))))){ $montName = date('M',strtotime($i)); $objPHPExcel->getActiveSheet()->SetCellValue($x.'1',$montName.'_Gross'); $x++; $objPHPExcel->getActiveSheet()->SetCellValue($x.'1',$montName.'_Tax'); $x++; } $rowCount = 2; //$query = "select employeeid,employeename,Grade from monthlySalary where date2='$from' order by grade desc"; //exit(); $cur= odbc_exec( $conn, $query ); while( odbc_fetch_row( $cur ) ) { //data print from the table $empID=odbc_result( $cur, 'employeeid' ); $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $rowCount-1); $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, odbc_result($cur,"employeeid")); $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, odbc_result( $cur, "employeename" )); $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, odbc_result($cur,"nicno")); $x = 'E';$cnt=1; for ($i=$from; $i<=$to; $i = date_01(date ("Y-m-d", strtotime("+31 days", strtotime($i))))){ $i = date_01(date('Y-m-d',strtotime($i))); $amt=getName($conn,"select sum(amount) from monthlyemployeeallowances where employeeid='$empID' and date2='$i' and adescription not like'Adj.%' " ); $objPHPExcel->getActiveSheet()->SetCellValue($x.''.$rowCount,$amt); $x++; if($d_head=="385") // GP-Advacne 1,2,3,4 $amt=getName($conn,"select sum(damount) from monthlyemployeedeductions where d_head in('$d_head','$d_head2','368','370','372','374') and employeeid='$empID' and date2='$i' " ); else $amt=getName($conn,"select sum(damount) from monthlyemployeedeductions where d_head in('$d_head','$d_head2') and employeeid='$empID' and date2='$i' " ); $objPHPExcel->getActiveSheet()->SetCellValue($x.''.$rowCount,$amt); $x++; } $rowCount++; } header('Content-Type: application/vnd.ms-excel'); //mime type header("Content-Disposition: attachment;filename=$filename.xlsx"); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache //for write make a library object $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); //Or you can also use this library object for write $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); }elseif(isset($_GET['btnBudgetProforma'])){ //Designation Salary List $monthYear = date_01(date('Y-m-d',strtotime( $_GET['btnBudgetProforma']))); $month = date('M',strtotime($monthYear))."-".date('Y',strtotime($monthYear)); //$year = date('Y',strtotime( $_GET['btnDesgSalary'])); $filename="Budget_Proforma_".$month; include('classesExcel/PHPExcel.php'); $objPHPExcel = new PHPExcel(); // set table header values $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Serial'); $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'PersonalNo'); $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Name'); $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Designation'); $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Grade'); $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'BasicDec22'); $objPHPExcel->getActiveSheet()->SetCellValue('G1', 'Incr'); $x = 'H';$cnt=1; $query = "select distinct a_head,adescription from monthlyemployeeallowances where date2='$monthYear' order by a_head asc"; $cur= odbc_exec( $conn, $query); while(odbc_fetch_row( $cur )){ $objPHPExcel->getActiveSheet()->SetCellValue($x.'1',odbc_result($cur,"adescription")); $arr[$cnt++]=odbc_result($cur,"a_head"); $x++; } //$objPHPExcel->getActiveSheet()->getStyle("A1:E1")->getFont()->setBold(true); // give bold style to cell $rowCount = 2; $total=0; $query = "select employeeid,employeename,Designation,Grade from monthlySalary where date2='$monthYear' order by grade desc"; $cur= odbc_exec( $conn, $query ); while( odbc_fetch_row( $cur ) ) { //data print from the table $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $rowCount-1); $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, odbc_result($cur,"employeeid")); $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, odbc_result( $cur, "employeename" )); $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, odbc_result($cur,"Designation")); $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, odbc_result($cur,"Grade")); $amt=getName($conn,"select incr_ from basicPayChart where year_='2022' and grade='".odbc_result($cur,"grade")."' "); $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount,$amt); $x = 'H';$cnt=1; while($cnt<=count($arr)){ $amt=getName($conn,"select amount from monthlyemployeeallowances where date2='$monthYear' and a_head='".$arr[$cnt]."' and employeeid='".odbc_result($cur,"employeeid")."' "); $objPHPExcel->getActiveSheet()->SetCellValue($x.''.$rowCount,$amt); //$arr[$cnt++]=odbc_result($cur,"adescription"); $cnt++; $x++; } $rowCount++; } $rowCount++; //$objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount,$total); //$range="E2:E100"; //$objPHPExcel->getActiveSheet()->getStyle($range)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); header('Content-Type: application/vnd.ms-excel'); //mime type //header('Content-Disposition: attachment;filename="you-file-name.xlsx"'); //tell browser what's the file name header("Content-Disposition: attachment;filename=$filename.xlsx"); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache //for write make a library object $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); //Or you can also use this library object for write $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); }elseif(isset($_GET['btnDesgSalary'])){ //Designation Salary List $monthYear = date_01(date('Y-m-d',strtotime( $_GET['btnDesgSalary']))); $month = date('M',strtotime( $_GET['btnDesgSalary']))."-".date('Y',strtotime( $_GET['btnDesgSalary'])); //$year = date('Y',strtotime( $_GET['btnDesgSalary'])); $filename="Desg_List_KICH_".$month; include('classesExcel/PHPExcel.php'); $objPHPExcel = new PHPExcel(); // set table header values $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Serial'); $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Designation'); $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'BPS'); $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Nos_of_Posts'); $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Monthly_Salary'); $objPHPExcel->getActiveSheet()->getStyle("A1:E1")->getFont()->setBold(true); // give bold style to cell $rowCount = 2; $total=0; //$query = "select employeeid as ID,employeename as Employee,Branch,account_no as AccountNo,allownces-deductions as Salary from monthlySalary where date2='$monthYear' order by branch desc,employeeid"; $query = "select Designation,Grade,count(*) as nos,sum(allownces) as salary from monthlySalary where date2='$monthYear' group by Designation,Grade"; $cur= odbc_exec( $conn, $query ); while( odbc_fetch_row( $cur ) ) { //data print from the table $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $rowCount-1); $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, odbc_result($cur,"Designation")); $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, odbc_result( $cur, "Grade" )); $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, odbc_result($cur,"nos")); $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, odbc_result($cur,"salary")); $total+=odbc_result($cur,"Salary"); $rowCount++; } $rowCount++; $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount,$total); //$range="E2:E100"; //$objPHPExcel->getActiveSheet()->getStyle($range)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); header('Content-Type: application/vnd.ms-excel'); //mime type //header('Content-Disposition: attachment;filename="you-file-name.xlsx"'); //tell browser what's the file name header("Content-Disposition: attachment;filename=$filename.xlsx"); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache //for write make a library object $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); //Or you can also use this library object for write $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); }elseif(isset($_GET['bankList'])){ $monthYear = date_01(date('Y-m-d',strtotime( $_GET['bankList']))); $month = date('M',strtotime( $_GET['bankList']))."-".date('Y',strtotime( $_GET['bankList'])); //$year = date('Y',strtotime( $_GET['bankList'])); $filename="Salary_List_KICH_".$month; include('classesExcel/PHPExcel.php'); $objPHPExcel = new PHPExcel(); // set table header values $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Serial'); $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Month'); $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Employee ID'); $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Employee Name'); $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Branch'); $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Account No'); $objPHPExcel->getActiveSheet()->SetCellValue('G1', 'Salary'); //$objPHPExcel->getActiveSheet()->SetCellValue('H1', 'BenovelenFund'); $objPHPExcel->getActiveSheet()->getStyle("A1:G1")->getFont()->setBold(true); // give bold style to cell $rowCount = 2; $total=0; $query = "select employeeid as ID,employeename as Employee,Branch,account_no as AccountNo,allownces-deductions as Salary from monthlySalary where date2='$monthYear' order by branch desc,employeeid"; //$query = "select employeeid as ID,employeename as Employee,Branch,account_no as AccountNo,allownces-deductions as Salary from monthlySalary where date2='$monthYear' and grade<15 order by branch desc,employeeid"; $cur= odbc_exec( $conn, $query ); while( odbc_fetch_row( $cur ) ) { //data print from the table $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $rowCount-1); $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $month); $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, odbc_result( $cur, "ID" )); $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, odbc_result($cur,"Employee")); $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, odbc_result($cur,"Branch")); $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowCount, odbc_result($cur,"AccountNo")); $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount, odbc_result($cur,"Salary")); //$basic=getName($conn,"select min_ from BasicPayChart where year_='2017' and Grade='".odbc_result($cur,"AccountNo")."'"); //$basic=getName($conn,"select damount from employeedeductions where d_head='381' and employeeid='".odbc_result($cur,"ID")."'"); //$objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowCount,$basic); $total+=odbc_result($cur,"Salary"); //$objPHPExcel->getActiveSheet()->getStyle('E'.$rowCount)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); $rowCount++; } $rowCount++; $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount,$total); $range="E2:E100"; $objPHPExcel->getActiveSheet()->getStyle($range)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); header('Content-Type: application/vnd.ms-excel'); //mime type //header('Content-Disposition: attachment;filename="you-file-name.xlsx"'); //tell browser what's the file name header("Content-Disposition: attachment;filename=$filename.xlsx"); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache //for write make a library object $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); //Or you can also use this library object for write $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); }elseif(isset($_GET['incomeTax'])){ $monthYear =date_01(date('Y-m-d',strtotime( $_GET['incomeTax']))); $month = date('M',strtotime( $_GET['incomeTax'])); $year = date('Y',strtotime( $_GET['incomeTax'])); $filename=$month."_".$year."_FBR_List"; include('classesExcel/PHPExcel.php'); $objPHPExcel = new PHPExcel(); //$result = $db->query("SELECT * FROM countries") or die(mysql_error()); // query for result // set table header values $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Payment Section'); $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'TaxPayer_NTN'); $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'TaxPayer_CNIC'); $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'TaxPayer_Name'); $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'TaxPayer_City'); $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'TaxPayer_Address'); $objPHPExcel->getActiveSheet()->SetCellValue('G1', 'TaxPayer_Status'); $objPHPExcel->getActiveSheet()->SetCellValue('H1', 'TaxPayer_Business_Name'); $objPHPExcel->getActiveSheet()->SetCellValue('I1', 'Taxable_Amount'); $objPHPExcel->getActiveSheet()->SetCellValue('J1', 'Tax_Amount'); $objPHPExcel->getActiveSheet()->getStyle("A1:J1")->getFont()->setBold(true); // give bold style to cell $rowCount = 2; //while($row = $result->fetch_assoc()){ $query = "select '149/2' as paymentSection,'' as ntn,nicno,employeename as Employee,'Peshawar' as City,'Peshawar' as Address,'INDIVIDUAL' as Status,'' as Business,allowances_Adj as Gross,damount as tax from incomeTaxView where date2='$monthYear' order by employeeid"; $cur= odbc_exec( $conn, $query ); $cnt=1; while( odbc_fetch_row( $cur ) ) { //$empID=odbc_result( $cur, "empID" ); //data print from the table $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount,odbc_result( $cur, "paymentSection" )); $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, odbc_result($cur,"ntn")); $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, odbc_result($cur,"nicno")); $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, odbc_result($cur,"Employee")); $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, odbc_result($cur,"city")); $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowCount, odbc_result($cur,"Address")); $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount, odbc_result($cur,"Status")); $objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowCount, odbc_result($cur,"Business")); $objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowCount, odbc_result($cur,"Gross")); $objPHPExcel->getActiveSheet()->SetCellValue('J'.$rowCount, odbc_result($cur,"tax")); $rowCount++; } header('Content-Type: application/vnd.ms-excel'); //mime type //header('Content-Disposition: attachment;filename="you-file-name.xlsx"'); //tell browser what's the file name header("Content-Disposition: attachment;filename=$filename.xlsx"); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache //for write make a library object $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); //Or you can also use this library object for write $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); } odbc_close($conn); ?>