Bank Salary Slip Generation for Payroll Management System

Today I completed, one more section of Payroll Management System and that is Generation of Bank Salary Slip. As I am making this Web Based payroll software for a contract based firm working at Trident Group, dhaula, they need an individual slip which they ll give to Bank for payment of their employees. So, I did the same.

Here is the code for the bankslip.php to generate odt

<?php
session_start();
if($_SESSION['user']!='admin') {
    header('location:../checklogin/error.php');
}
require_once('../library/odf.php');
include '../dbfiles/config.php';
class bankslip {
    private $op;
    private $query;
    public function download_slip() {
        $odf = new odf("bankslip.odt");
        if($_POST['month']!='' && $_POST['year']=='') {
            echo "<script>alert('You forgot to submit year'); window.location='bankslip.php';</script>";
        }
        elseif ($_POST['month']=='' && $_POST['year']!='') {
            echo "<script>alert('You forgot to enter month'); window.location='bankslip.php';</script>";
        }
        elseif (($_POST['month']!='' && $_POST['year']!='') && $_POST['current']!='') {
            echo "<script>alert('Hello!! I didnt told you to select all options'); window.location='bankslip.php';</script>";
        }
        elseif (($_POST['month']=='' && $_POST['year']=='') && $_POST['current']=='') {
            echo "<script>alert('Hello!! I told you to select one option'); window.location='bankslip.php';</script>";
        }
        elseif ($_POST['month']!=''&& $_POST['year']!='') {
            $this->op= 'defined';
            $month= $_POST['month'];
            $year= $_POST['year'];

        }
        elseif ($_POST['current']!='') {
            $this->op= 'current';
            $month= date('n')-1;
            $year= date('Y');
        }
        switch ($month) {
            case 1:
                $monthname= 'January';
                break;
            case 2:
                $monthname= 'February';
                break;
            case 3:
                $monthname= 'March';
                break;
            case 4:
                $monthname= 'April';
                break;
            case 5:
                $monthname= 'May';
                break;
            case 6:
                $monthname= 'June';
                break;
            case 7:
                $monthname= 'July';
                break;
            case 8:
                $monthname= 'August';
                break;
            case 9:
                $monthname= 'September';
                break;
            case 10:
                $monthname= 'October';
                break;
            case 11:
                $monthname= 'November';
                break;
            case 12:
                $monthname= 'December';
                break;
        }
        $odf->setVars('month', $monthname);
        $odf->setVars('year', $year);
        $this->query= mysql_query("Select workers.id as id, worker_fname, worker_lname, gross, hra, rate, workdays, pf, esi, advance, accno from workers, worker_fixeds, worker_varys, worker_acc where workers.id=worker_fixeds.worker_id and workers.id=worker_varys.id and workers.id=worker_acc.id and month='$month' and year='$year'");
        $bank = $odf->setSegment('emp');
        $s=1;
        while($result= mysql_fetch_array($this->query)) {
            if(!$result) {
                header('location:qerror.php?op='.mysql_error());
            }
            $cal= sprintf('%.2f',($result['rate'])*($result['workdays']));
            $ded= $result['pf']+$result['esi']+$result['advance'];
            if($result['workdays']<20) {
                $wages= sprintf('%.2f',(($result['rate'])*($result['workdays']))+0);
                $netpay= sprintf('%.0f', ($wages-$ded));
                $total= sprintf('%.0f', ($total+$netpay));
            }
            else {
                $wages= sprintf('%.2f',(($result['rate'])*($result['workdays']))+$result['hra']);
                $netpay= sprintf('%.0f', ($wages-$ded));
                $total= sprintf('%.0f', ($total+$netpay));
            }
            $bank->s($s);
            $bank->accno($result['accno']);
            $bank->name($result['worker_fname'].' '.$result['worker_lname']);
            $bank->amount($netpay);
            $bank->merge();
            $s=$s+1;
        }
        $odf->mergeSegment($bank);
        $odf->setVars('total', $total);
        $odf->exportAsAttachedFile('Bankslip.odt');
    }
}
$bank_slip= new bankslip();
$bank_slip->download_slip();
?>

JOD Converter, a Great Utility for Conversion of the Documents

There is always a requirement for a programmer to be able to present the reports and all other important documents in various available formats and this was a requirement for me too. So, to add this ability to my existing and upcoming projects too, I looked upon the existing solution suitable for me. And thus after a lot of search I found a solution, a utility that will be helpful to me to perform the conversion of these files.

It is JOD COnverter. (Not a S/W purely, it is a library basically.)

JODConverter, the Java OpenDocument Converter, converts documents between different office formats. It leverages OpenOffice.org, which provides arguably the best import/export filters for OpenDocument and Microsoft Office formats available today.

JODConverter automates all conversions supported by OpenOffice.org, including

• Microsoft Office to OpenDocument, and viceversa
  
  
  
  • Word to OpenDocument Text (odt); OpenDocument Text (odt) to Word
  
  
  • Excel to OpenDocument Spreadsheet (ods); OpenDocument Spreadsheet (ods) to Excel
  
  
  • PowerPoint to OpenDocument Presentation (odp); OpenDocument Presentation (odp) to PowerPoint


• Any format to PDF
  
  
  
  • OpenDocument (Text, Spreadsheet, Presentation) to PDF
  
  
  • Word to PDF; Excel to PDF; PowerPoint to PDF
  
  
  • RTF to PDF; WordPerfect to PDF; ...


• And more




• OpenDocument Presentation (odp) to Flash; PowerPoint to Flash


• RTF to OpenDocument; WordPerfect to OpenDocument


• Any format to HTML (with limitations)


• Support for OpenOffice.org 1.0 and old StarOffice formats


• ..

And for Rest information..    http://www.artofsolving.com/opensource/jodconverter

Branch/Dept Detail entering Section for CMS

R&D is also going hand in hand with the Project Development of S/W. Today I worked on a new section for Complaint Management system. This section was of Entering new details of New Branch/Dept into the DB, to whom complaint has been not directed so far. By doing so they ll make an entry which they will going to refer late on while creating dispatch and entering complaints.

Take a look below on the section designed:-

Though Javascript validations is still to be done...

[caption id="attachment_59" align="aligncenter" width="1024" caption="Branc/Dept detail entering section"][/caption]

PHP explode function on array

Today while working on the project of PHP-Reportica , I felt need of  applying php explode function on some php variable. Though usage of explode function was bit easy and I already used it, but this time I need to use it in much complicated way.

I was having a field with value of  selected_table_name.select_field_name. Now I had easily separated these two using function as:-

$explode_data= explode(".",  $tab_field);

Two arrays are formed now viz.  $explode_data[0] and $explode_data[1]. But issue was that first array might have redundant entries of same table names. Now, I stuck at this point, How to do it???

I find solution after about one hour spending one this thing. I came to know about a PHP function called unique array which removes redundant entries from an array.  Here below is what I needed:-

 foreach ($_POST['selfield'] as $mystring) {
$rs=explode(",",$mystring);
$first[]= $rs[0];
}
$uniqtab= array_unique($first);
$count= count($uniqtab);
$i=0;
foreach ($uniqtab as $table){
    echo $table;
     if ($i < ($count - 1)) {
      echo ', ';
   }
   $i=$i+1;
}

Salary Slip Generation for Payroll System

Today I am Done with generation of salary slip for Workers in Payroll Management System. for the time I am done with only the phase of downloading complete slips for every employee.

Here Below is snapshot:-

[caption id="attachment_45" align="aligncenter" width="1024" caption="Salary Slip Generation"][/caption]

And below is the Complete Coding for Generating the Odt file

<?php
require_once('../library/odf.php');
include_once '../dbfiles/config.php';
class salaryslip {
    private $query= array();
    private $result= array();
    private $calsal;
    private $acc;
    private $ded;
    private $totwages;
    private $netpay;
    private $month;
    public function slip() {
        $odf = new odf("slip.odt");
        $article = $odf->setSegment('articles');
        $this->month= date('n')-1;
        if($_GET['op']=='comp') {
            $this->query[0]= mysql_query("Select workers.id as id, worker_fname, worker_lname, gross, hra, rate, workdays, pf, esi, advance, accno from workers, worker_fixeds, worker_varys, worker_acc where workers.id=worker_fixeds.worker_id and workers.id=worker_varys.id and workers.id=worker_acc.id and month='$this->month'");
            if(!$this->query[0]) {
                header('location:qerror.php?op=uperror');
            }
            while($this->result[0]=mysql_fetch_array($this->query[0])) {
                $comp_name= $this->result[0]['worker_fname'].' '.$this->result[0]['worker_lname'];
                $this->ded=($this->result[0]['pf']+$this->result[0]['esi']+$this->result[0]['advance']);
                $article->name($comp_name);
                $article->accno($this->result[0]['accno']);
                $article->ecode($this->result[0]['id']);
                $article->basic($this->result[0]['gross']);
                $article->days($this->result[0]['workdays']);
                $this->calsal= sprintf('%.2f',($this->result[0]['rate'])*($this->result[0]['workdays']));
                $article->calsal($this->calsal);
                if($this->result[0]['workdays']<20) {
                    $article->hra('0');
                    $this->totwages=sprintf('%.2f',($this->calsal)+0);
                    $article->total($this->totwages);
                }
                else {
                    $article->hra($this->result[0]['hra']);
                    $this->totwages=sprintf('%.2f',($this->calsal)+$this->result[0]['hra']);
                    $article->total($this->totwages);
                }
                $article->pf($this->result[0]['pf']);
                $article->esi($this->result[0]['esi']);
                $article->advance($this->result[0]['advance']);
                $article->totalded($this->ded);
                $this->netpay= sprintf('%.2f',($this->totwages)-($this->ded));
                $article->netpay($this->netpay);
                $article->merge();
            }
        }
        $odf->mergeSegment($article);
        $odf->exportAsAttachedFile('CompleteSlip.odt');
    }
}
$sh= new salaryslip();
$sh->slip();
?>

Total For Final Salary sheet of Project

One thing which I forgot was total of each field  i.e gross, hra, cal salary, total wages, pf, esi etc. What I did, I declared an array named total in class finalsheet of page salarysheet.php

So Code Snippet got modified as below:-

<?php class finalsheet{
                      private $query= array();
                      private $result= array();
                      private $total= array();
                      private $calsal;
                      private $ded;
                      private $totwages;
                      private $netpay;
                      private $month;
                      public function seedata(){
                          $this->total[1]=0;
                          $this->total[2]=0;
                          $this->total[3]=0;
                          $this->total[4]=0;
                          $this->total[5]=0;
                          $this->total[6]=0;
                          $this->total[7]=0;
                          $this->total[8]=0;
                          $this->total[9]=0;
                          $this->total[10]=0;
                          $this->total[11]=0;
                          $this->month= date('n')-1;
                          $this->query[0]=mysql_query("Select id from worker_varys where month='$this->month'");
                          $count= mysql_num_rows($this->query[0]);
                          if($count==0){
                              echo "<script>var check= confirm('No data for updation');
                               if(check==true){
                               window.location='newsheet.php';}
                               else{
                               window.location='adminhome.php';}
                                </script>";
                          }
                          else{
                          $this->query[1]= mysql_query("Select workers.id as id, worker_fname, worker_lname, gross, hra, rate, workdays, pf, esi, advance from workers, worker_fixeds, worker_varys where workers.id=worker_fixeds.worker_id and workers.id=worker_varys.id and month='$this->month'");
                          if(!$this->query[1]){
                              header('location:qerror.php?op=uperror');
                          }
                         $this->i=1;
                         while($this->result[2]=mysql_fetch_array($this->query[1])){
                             if(($this->i)%2==0){
                             echo "<tr>";
                                 }
                              else{
                                  echo "<tr>";
                              }
                              echo "<input type='hidden' name=id[] value=".$this->result[2]['id']." />";
                              echo "<td>".$this->i."</td>";
                              echo "<td style='width:120px;'>".$this->result[2]['worker_fname'].' '.$this->result[2]['worker_lname']."</td>";
                              echo "<td>".$this->result[2]['gross']."</td>";
                              echo "<td>".$this->result[2]['rate']."</td>";
                              echo "<td>".$this->result[2]['workdays']."</td>";
                              $this->calsal= sprintf('%.2f',($this->result[2]['rate'])*($this->result[2]['workdays']));
                              echo "<td>".$this->calsal."</td>";
                              if($this->result[2]['workdays']<20){
                              echo "<td>0</td>";
                              $this->total[5]= sprintf('%.2f', ($this->total[5]) + 0);
                              $this->totwages=sprintf('%.2f',($this->calsal)+0);
                              echo "<td>".$this->totwages."</td>";
                              }
                              else{
                                 echo "<td>".$this->result[2]['hra']."</td>";
                                 $this->total[5]= sprintf('%.2f', ($this->total[5]) + ($this->result[2]['hra']));
                                 $this->totwages=sprintf('%.2f',($this->calsal)+$this->result[2]['hra']);
                                 echo "<td>".$this->totwages."</td>";
                              }
                              echo "<td>".$this->result[2]['pf']."</td>";
                              echo "<td>".$this->result[2]['esi']."</td>";
                              echo "<td>".$this->result[2]['advance']."</td>";
                              $this->ded=($this->result[2]['pf']+$this->result[2]['esi']+$this->result[2]['advance']);
                              echo "<td>".$this->ded."</td>";
                              $this->netpay= sprintf('%.2f',($this->totwages)-($this->ded));
                              echo "<td>".$this->netpay."</td>";
                              $this->total[1]= $this->total[1] + $this->result[2]['gross'];
                              $this->total[2]= sprintf('%.2f', ($this->total[2]) + ($this->result[2]['rate']));
                              $this->total[3]= sprintf('%.2f', ($this->total[3]) + ($this->result[2]['workdays']));
                              $this->total[4]= sprintf('%.2f', ($this->total[4]) + ($this->calsal));
                              $this->total[6]= sprintf('%.2f', ($this->total[6]) + ($this->totwages));
                              $this->total[7]= sprintf('%.2f', ($this->total[7]) + ($this->result[2]['pf']));
                              $this->total[8]= sprintf('%.2f', ($this->total[8]) + ($this->result[2]['esi']));
                              $this->total[9]= sprintf('%.2f', ($this->total[9]) + ($this->result[2]['advance']));
                              $this->total[10]= sprintf('%.2f', ($this->total[10]) + ($this->ded));
                              $this->total[11]= sprintf('%.2f', ($this->total[11]) + ($this->netpay));
                              echo "</tr>";
                              $this->i=$this->i+1;
                        }
                        echo '<tr><td colspan="2" align="right" style="font-size:medium;">Total
                      </td>';
                        echo '<td>'.$this->total[1].'</td>';
                        echo '<td>'.$this->total[2].'</td>';
                        echo '<td>'.$this->total[3].'</td>';
                        echo '<td>'.$this->total[4].'</td>';
                        echo '<td>'.$this->total[5].'</td>';
                        echo '<td>'.$this->total[6].'</td>';
                        echo '<td>'.$this->total[7].'</td>';
                        echo '<td>'.$this->total[8].'</td>';
                        echo '<td>'.$this->total[9].'</td>';
                        echo '<td>'.$this->total[10].'</td>';
                         echo '<td>'.$this->total[11].'</td></tr>';
                          }
                      }
                  }
                  $sh= new finalsheet();
                  $sh->seedata();
                 ?>
And Below is the screen shot of fields that get total. It is clearly visible total being performed at last row.

[caption id="attachment_41" align="aligncenter" width="1024" caption="Total of Every Field of Salary sheet"][/caption]

Field Listing Section of First Part of Reporting Tool

The day quite went tough as I had to struggle a lot while outputting the Field list on each selection of table via AJAX in Reporting Tool Project. But as is said, "All Well that Ends Well". Same happened with me. After doing so much struggle, I was able to resolve the issues.

Continuing to the previous Post's code,

If user selects the table, (I am showing a code snippet)

index.php

<h2><span>Please Select the </span>Credentials</h2><br/>
            <form>
                <label>Select The Table</label>
                <?php
                include_once 'config.php';
                if($op=='mysql'){

                }
                elseif ($op=='postgresql') {
                    $query=pg_query("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';");
                    echo '<select name="table" onchange="return showfield(this.value);">';
                    while($result=pg_fetch_array($query)){
                        echo'<option value="'.$result['table_name'].'">'.$result['table_name'].'</option>';
                    }
                }
                echo '</select>';
                ?>
            </form>

After that is shown

ajax.js

function showfield(str)
{
var xmlhttp;
if (str=="")
  {
  document.getElementById("result").innerHTML="";
  return;
  }
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("result").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","showfield.php?table="+str,true);
xmlhttp.send();
}

This above ajax.js script which is issuing an ajax call for every selection of new table from the list.

and after that is

showfield.php

<?php
include_once 'config.php';
$t=$t.$_GET['table'];
    $query= pg_query("SELECT column_name FROM information_schema.columns WHERE table_name ='$t';");
    echo '<table border=1>';
    echo '<tr><th colspan="5">Select Field</th></tr><tr>';
    $i=1;
    while($result=pg_fetch_array($query)){
    echo '<td><input type="checkbox" name="field[]" value="'.$result['column_name'].'"/>'.$result[column_name].'</td>';
    $i=$i+1;
    if($i>5){
    echo '</tr><tr>';
    $i=1;
}
}
echo '</tr><tr><td colspan="5"><input type="submit" value="Enter fields" onclick="enterfield();" /></td>';
echo '<td colspan="5"><input type="submit" value="Clear Selected Fields" onclick="clear();" /></td></tr>';
echo '</table>';
?>

Which will List the all fields for a table. On the Selection of which and after submission will added to another table and user will again allowed to check whether they are sure to opt for all these fields. So Next Task after this will be Selection of Fields and Confirmation and Submission for Query Generation.

UML Diagram of Payroll System

Today I completed the Basic Functionality of Payroll Management System. Here Below is the UML Diagram for Payroll Management System. R&D of both projects Complaint Management system and Payroll Management System will go hand in hand.

[caption id="attachment_33" align="aligncenter" width="1024" caption="UML Diagram of Payroll system"][/caption]

Selection of Formatting Logic

Logic Behind Generating is query is clearly visible in my mind, but I was stuck with the thing that how I am going to display the O/P in which format and that too importanly how???  So Brainstorming begins and along with that searching too, so as to collect some information about Formatting of Reports. But I did not work. so Whole Day went in thinking about the formatting. Hope fully tomorrow I will get the solution

Table Listing in Reporting tool Project

So, Today I started first with preparing Front End page for Selecting out which Tables of  database you want to work on. While Selecting a table you will be prompted to select the Fields, from which you have to select the fields for your query.  Ok Leave it this          is the next part of phase 1.

Below is the code discussed which is front end and will show you the each and every table of selected database:-

index.php

<html lang="en">
    <head>
        <script class="jsbin" src="jquery.min.js"></script>
        <meta charset="utf-8" />
        <link href="tablecloth/tablecloth.css" rel="stylesheet" type="text/css" media="screen" />
<script type="text/javascript" src="tablecloth/tablecloth.js"></script>
<script type="text/javascript" src="myscript.js"></script>
<script type="text/javascript">
var t = new ScrollableTable(document.getElementById('myScrollTable'), 150,250);
</script>

        <title></title>
    <script>
        function showfield()// jQuery Function
  {
  var name = $("input[name='table']:checked").val();
    jQuery.ajax({
    url:'showfield.php',
    type:"POST",
    data: { clientdata : name },
    success:function(data){ document.getElementById("result").innerHTML= data;}
    });
    return false;
}
    </script>
    <script>
        function enterfield()// jQuery Function
  {
  var Selected = new Array();
  jQuery("input[name='field[]']:checked").each(function(){
    Selected.push(jQuery(this).val());
  });

    jQuery.ajax({
    url:'enterfield.php',
    type:"POST",
    data: { clientdata : Selected },
    success:function(data){ document.getElementById("enterfield").innerHTML= data;}
    });
    return false;
}
    </script>
         </head>
    <body>
    <form>
        <table style="margin-left: auto; margin-right: auto;" id="myScrollTable">
            <tr>
                <th>
                    Select Table
                </th>
                </tr>
                <?php
                include_once 'config.php';
                if($op=='mysql'){

                }
                elseif ($op=='postgresql') {
                    $query=pg_query("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';");
                   /*Above Query retrieve table names from selected database in config.php */ 
                   while($result=pg_fetch_array($query)){
                        echo'<tr><td>';
                        echo'<input type="radio" name="table" value="'.$result['table_name'].'"onclick="showfield();" />'.$result['table_name'].'</td>';
                        echo '</tr>';
                    }
                }
                ?>
        </table>
    </form>
    <div id="result">

    </div>
        <div id="enterfield">

        </div>
        <p><tt id="results"></tt></p>
    </body>
</html>

After That is the showfield.php from which fields for table are displayed for every click on radio button of table
showfield.php
<?php
include_once 'config.php';
$t= $_POST['clientdata'];
    $query= pg_query("SELECT column_name FROM information_schema.columns WHERE table_name ='$t';");
    echo '<table>';
    echo '<tr><th>Select Field</th></tr>';
    while($result=pg_fetch_array($query)){
    echo '<tr><td><input type="checkbox" name="field[]" value="'.$result['column_name'].'" onclick="enterfield();"/>'.$result[column_name].'</td></tr>';
    echo'</br>';
}
echo '</table>';
?>

Reporting Tool for PHP

Today I am over to my 3rd project that is the Reporting tool for PHP. As far as DIO Sir searched and also I searched we had not found any suitable tool for PHP to generate automatic reports on the basis of queries that are being generated. Those We found are not FOSS and which are FOSS were not living upto our expectations.

So An anlaysis started today regarding Generation of PHP Reporting Tool.

I divided it into 3 sections as per as following:-

1. Dynamic Query Generation


2. Display Formatted Report (preferably in Doc) on the basis of user selection of Fields formatting


3. Final PHP code generated which is report code and can be integrated with the project

So I will work on these phases one by one. And Regarding the Feasibility of this Project, This Project is quite feasible but being feasible it is quite time consuming. Because I need to work a lot on the formatting of reports.