 <?php include('Class-EmpMaster.php');
 include('./includes/fun-php.php');
 $PF_data=array("AC1"=>"A/C 1 (Employee Provident Fund Account,EPF)","AC10"=>"A/c 10(Employee Pension Fund)","AC2"=>"A/c2 (Administration Charges)","AC21"=>"A/c21 (Employee Developed Linked Insurance Charges)","AC22"=>"A/c22(EDLIC)","Net_pf"=>"Net Payable PF");
 $EsI_data=array("ESI_2"=>"ESI Contribution (Employer)","Net_Esi"=>"Net Payable ESIC");
 $pd_data_percentage=array("AC1"=>"3.67","AC10"=>"8.33","AC2"=>"1","AC21"=>"0.5","AC22"=>"0.01");



if(isset($_POST['Save']))
{
 $month_data=array("1"=>"January","2"=>"February","3"=>"March","4"=>"April","5"=>"May","6"=>"June","7"=>"July","8"=>"August","9"=>"September","10"=>"October","11"=>"November","12"=>"December");



  date_default_timezone_set('Asia/Calcutta'); 
  $prefix=$s->getField('companies','CompanyId',$_SESSION['ACGCompanyId'],'company_code');
  $prefix_sql=DB_query("Select * from prefix_tbl where prefix_name='".$_REQUEST['frmid']."'",$db);
  $prefix_row=DB_fetch_array($prefix_sql);
  $prefix1=$prefix_row['prefix_code'];
  
  $financial=substr($_SESSION['YearStart'],-2)."-".substr($_SESSION['YearEnd1'],-2);
    $sql="Select voucher_no from hr_posting_phase2 where site='".$_REQUEST['site']."' 
  and INSTR(`voucher_no`, '$financial') > 0 order by id desc limit 0,1";

  $result=DB_query($sql,$db);
  $myrow=DB_fetch_array($result);
  //echo DB_num_rows($result);
  if(DB_num_rows($result)>0)
  {
  //$Reqno=$myrow['Reqno'];
  //$req_part1=substr($Reqno,-4);
         if($prefix!='')
      {
      $ExplBill=explode("/",$myrow['voucher_no']);
      $maxval = $ExplBill[4];
        }
        else
        {
        $ExplBill=explode("/",$myrow['voucher_no']);
      $maxval = $ExplBill[3];
        }     
  //$ExplBill=explode("/",$myrow['Reqno']);
  //$maxval = $ExplBill[3];
  //echo $req_part1;
  $req_final=$maxval+1;
  }else
  {
  $req_final="1";
  }
          if($prefix!='')
       $string=$prefix."/".$_REQUEST['site']."/".$financial."/".$prefix1."/";
         else
         $string=$_REQUEST['site']."/".$financial."/".$prefix1."/";

  //$string=$prefix."/".$_REQUEST['site']."/".$financial."/";
   $final_string1=$string.str_pad($req_final,4,"0",STR_PAD_LEFT);

   $data1['site']=$_REQUEST['site'];
   $data1['voucher_no']=$final_string1;
   $data1['posting_date']=FormatDateForSQL($_REQUEST['doc_date']);
   $data1['month']=$_REQUEST['paymonth'];
   $data1['year']=$_REQUEST['payyear'];  
   $data1[created_date]=date('Y-m-d');
   $data1['created_by']=$_SESSION['UserID'];
   $data1['company_id']=$_SESSION['ACGCompanyId'];
   $s->RecordInsert('hr_posting_phase2',$data1);
   $lastid=DB_Last_Insert_ID($db,'hr_posting_phase2',$ErrMsg);

   foreach($_REQUEST['desc'] as $key=>$value)
       {

                $data_hr['hr_posting_ph_id']=$lastid;
                $data_hr['ledger']=$_REQUEST['ledger'][$value];
                $data_hr['ledger_amt']=$_REQUEST['amt'][$value];                
                $data_hr['parameter_type']=$value;  
                $data_hr['parameter_id']=$_REQUEST['parameter_id'][$value];   
               $s->RecordInsert('hr_posting_phase2_dtls', $data_hr); 

           //echo "<pre>";
          // print_r($data3);

       }



   $voucher_id=DB_query("select Voucher_NO as ID from account_book where Vprefix='8' and Branch='".$_REQUEST['site']."' 
      AND dates BETWEEN '".$_SESSION['financialYrStart']."' and '".$_SESSION['financialYrEnd']."'   order by id desc limit 0,1",$db);
      if(DB_num_rows($voucher_id)>0)
      {
      $row=DB_fetch_array($voucher_id);
      $voucher_no=$row['ID'];
      
      $XEP=explode("-",$voucher_no);
      
      $new=$XEP[2]+1;
      }
      else
      {
      $new='1';
      }
      
      $location=$s->getField('locations','loccode', $_REQUEST['site'],'prefix');
      $location1=$s->getField('voucher_master','VoucherType','8','VPrefix');
      $string=$location.'-'.$location1.'-';
      $final_string1=$string.str_pad($new,5,"0",STR_PAD_LEFT);


 //  End of Prefix code generation       



        $dataArray['Branch']= $_REQUEST['site'] ;
        $dataArray['VPrefix']= '8' ;
        $dataArray['Voucher_NO']= $final_string1;  
        $dataArray['voucher_type']='Auto';     
        $dataArray['Dates']= date('Y-m-d'); 
        $dataArray['conv_rate']='1' ;  
        $dataArray['posted_date']= date('Y-m-d');
        $dataArray['AccountBookType']= 'voucher';   
        $dataArray['created_date']= date('Y-m-d');
        $dataArray['created_by']= $_SESSION['UserID'];   
        $dataArray['narration']="PF ESI Posting For month" .$month_data[$_REQUEST['paymonth']];  ;  
        $s->RecordInsert('account_book', $dataArray);
       $getlastid=DB_Last_Insert_ID($db,'account_book', 'id');

       foreach($_REQUEST['desc'] as $key=>$value)
       {

            	$data3['account_bookid']=$getlastid;
                $data3['ledgerid']=$_REQUEST['ledger'][$value];
                $data3['amount']=$_REQUEST['amt'][$value];
                $data3['converted_amount']=$_REQUEST['amt'][$value];
                $data3['currency']=$s->getField('currency_master','abbreviation','INR','id');
                $data3['conv_currency']=$s->getField('currency_master','abbreviation','INR','id');
                $data3['amount_type']=$_REQUEST['ac_type'][$value];
                if($_REQUEST['ac_type'][$value]=='DR')
                {  $total_debit[]=$_REQUEST['amt'][$value];  }
                else if($_REQUEST['ac_type'][$value]=='CR')
                {  $total_credit[]=$_REQUEST['amt'][$value]; }      
               $s->RecordInsert('account_book_details', $data3); 

           //echo "<pre>";
          // print_r($data3);

       }

      if(!empty($total_credit)) $credit=array_sum($total_credit); else $credit=0;
      if(!empty($total_debit)) $debit=array_sum($total_debit); else $debit=0;

      $update=DB_query("update account_book set total_cramount='".$credit."',total_dramount='".$debit."',total_cramount_conv='".$credit."',total_dramount_conv='".$debit."' where id='".$getlastid."'",$db);
      


}



 ?>
 <link rel="stylesheet" href="css/new_mxerp.css">
<div class="body_contain">
<div class="tab_div">
<h1><?php echo $s->getField('modules_form','id',$_REQUEST['frmid'],'frm_name'); ?></h1>
	<form action="" method="post">
	<table border="0" cellpadding="3" cellspacing="0" width="100%">	
	<tr class="row1">
	<td width="13%" align="right"><strong>Branch</strong></td>
	<td width="20%"><select name="site" id="site" style="width:98%;">
<?php 
  $site_sql=DB_query("Select loccode, locationname from locations  where CompanyId='".$_SESSION['ACGCompanyId']."' and loccode in('".$_SESSION['AssignedBranch']."')",$db);
while($site_row=DB_fetch_array($site_sql)){
?>
<option value="<?php echo $site_row['loccode'];?>"<?php echo ($_REQUEST['site']==$site_row['loccode']?'selected':'');?>><?php echo $site_row['locationname'];?></option>
<?php } ?>
</select></td>
	<!--<td>Employment Type</td>
	<td>Employee</td>-->
	<td width="13%" align="right"><strong>Month</strong></td>
	<td width="20%"><select name="paymonth" style="width:98%;"> 
 <?php
 $arr=array(1=>"Jan","Feb","Mar","Apr","May","June","July","Aug","Sept","Oct","Nov","Dec");
 foreach ($arr as $key => $value) {
 	$p='';
 	if( $key==$_REQUEST['paymonth'])
	$p='selected';
   ?><option value="<?php echo $key;?>" <?php echo $p;?> ><?php echo $value;?></option>
 <?php } ?></select>
	  </td> 
	<td width="13%" align="right"><strong>Year</strong></td>
	 <td><select name="payyear" style="width:98%;">
 <?php
 
 for($i=date('Y');$i>2000;$i--)
 {
 	$p='';
 	if( $i==$_REQUEST['payyear'])
	$p='selected';
 ?><option value="<?php echo $i;?>" <?php echo $p;?>><?php echo $i;?></option>
 <?php 
 }
 ?></select>
	  </td>
</tr>
<tr class="row1">       
	<td colspan="6" align="center"><input type="submit" name="Search" class="search"   value="Search" onclick="return validate();"   /></td>
  </tr>
</table>
 <br>	
 	
	<?php 
	if($_REQUEST['Search'])
	{	

 $sql_check=DB_query("select id from hr_posting_phase2 where site='".$_REQUEST['site']."'
  and month='".$_REQUEST['paymonth']."' and year='".$_REQUEST['payyear']."'",$db);
 if(DB_num_rows($sql_check)==0)
 {


		$total_amt=array();
	 	$years=$_REQUEST['payyear'];
	 	$month=$_REQUEST['paymonth'];	   
	    $empcode=$_REQUEST['empcode'];
		$emptype=$_REQUEST['emptype'];
		$site=$_REQUEST['site'];		
	
	  $employer_esi=DB_fetch_array(DB_query("select * from esi_master where site='".$_REQUEST['site']."' and salto<='25000'",$db));	  
	  $Employer_per=$employer_esi['employerper'];
	  $Employee_per=$employer_esi['esiper']; 
		$EsI_data_percentage=array("ESI_2"=>$Employer_per);
		$filter=array();
		
		if($site!='')
		$filter[]="C.emp_site = '".$site."'";	
		if($month!='')
		$filter[]="A.payslip_month = '".$month."'";	
		if($years!='')
		$filter[]="A.payslip_year = '".$years."'";	
		
		if($filter!='')
		{$filter=implode(" and ",$filter);}

		$allowance_id_esi=$s->GetFieldValue("deduction_master","name like'%ESI%'","id");
		 $allowance_id_PF=$s->GetFieldValue("deduction_master","name like'%PF%'","id");
		
		$query_esi=DB_fetch_array(DB_query("SELECT SUM(A.gross_salary) AS gross_salary,sum(B.allowance_amount) as amt FROM `master_process_salary` A INNER JOIN   payslip_salary B
		  ON A.id=B.mst_prosal INNER JOIN empmaster C ON A.emp_id=C.id      WHERE B.allowance_id='".$allowance_id_esi."' AND B.allowance_type='D' 
		 AND B.allowance_amount!='0.00'   and $filter",$db));

       
		$query_pf=DB_fetch_array(DB_query("SELECT SUM(A.basic_salary) AS basic_salary,sum(B.allowance_amount) as amt  FROM `master_process_salary` A INNER JOIN   payslip_salary B
		  ON A.id=B.mst_prosal INNER JOIN empmaster C ON A.emp_id=C.id      WHERE B.allowance_id='".$allowance_id_PF."' AND B.allowance_type='D' 
		 AND B.allowance_amount!='0.00'   and $filter",$db));

	 
	    $Esi_employee_ledger=DB_fetch_array(DB_query("SELECT B.parameter_val,parameter_id FROM hr_posting_master A INNER JOIN `hr_posting_master_details` B
          ON A.id=B.hr_posting_id where B.parameter_type='D' and parameter_id='".$allowance_id_esi."'",$db));
		$pf_employee_ledger=DB_fetch_array(DB_query("SELECT B.parameter_val,parameter_id FROM hr_posting_master A INNER JOIN `hr_posting_master_details` B
          ON A.id=B.hr_posting_id where B.parameter_type='D' and parameter_id='".$allowance_id_PF."'",$db));
			
			?>
	
	<table width="100%" align="center" border="1" cellpadding="3" cellspacing="0" style="border:solid 1px #808080; border-collapse:collapse;">
	   <tr>
        <th>Descriptions</th>
        <th width="18%">Ledger</th>      
        <th width="12%">Debit</th>
        <th width="12%">Credit</th>
      </tr>	
      <tr>
      <td colspan="4"><h3 align="center">PF Summary For The Month Of <?PHP echo GetMonth($month);?> &nbsp;<?php echo $years;?></h3></td>
      </tr>
      <tr class="text" bgcolor="#FFFFFF">
        <td><b>Salary on which PF is being deducted :<b></td>
        <td></td>
        <td><?php  echo  $total_basic1=$query_pf['basic_salary'];
            $total_basic=round($query_pf['amt']*100/12);        
        ?></td>
        <td></td>
      </tr>
      <tr class="text" bgcolor="#FFFFFF">
        <td>A/C 1 (Employee Provident Fund Account,EPF)-12%</td>
        <td><select name="ledger[pf]" style="width:98%;">        
         <option value="<?php echo $pf_employee_ledger['parameter_val'];  ?>"><?php echo $s->getField('accountledger','AccountsID',$pf_employee_ledger['parameter_val'],'AccountName'); ?></option>
          </select></td>         
        <td><?php  $pf_amt= round($total_basic*.12); //$query_pf['amt'];
		$total_amt[]=($total_basic*.12);
		?>
		<input type="text" readonly name="amt[pf]" value="<?php echo $pf_amt; ?>" style="width:98%;"></td>
		 <td><input type="hidden" name="desc[]" value="pf">
         <input type="hidden" name="ac_type[pf]" value="DR">
         <input type="hidden" name="parameter_id[pf]" value="<?php echo $pf_employee_ledger['parameter_id'] ?>">
		 	</td>
      </tr>
      <?php foreach($PF_data as $key=>$value)
      {?>
      <tr class="text" bgcolor="#FFFFFF">
        <td><?php echo $value; ?><?php  if(isset($pd_data_percentage[$key]))  echo "--(". $pd_data_percentage[$key]. "%)";  ?></td>
        <td><select name="ledger[<?php echo $key; ?>]" style="width:98%;">
        <?php
         $query1=DB_query("SELECT B.parameter_val FROM hr_posting_master A INNER JOIN `hr_posting_master_details` B
          ON A.id=B.hr_posting_id where B.parameter_type='".$key."' ",$db);
         while($row1=DB_fetch_array($query1))
         {
         ?>
<option value="<?php echo $row1['parameter_val']; ?>"><?php echo $s->getField('accountledger','AccountsID',$row1['parameter_val'],'AccountName'); ?></option>
<?php } ?>
        </select>
        <input type="hidden" name="parameter_id[<?php echo $key; ?>]" value="">
      </td>
        <td><?php   $totalpf= round(($total_basic*$pd_data_percentage[$key])/100); $total_amt[]=$totalpf; ?>
        <?php if(isset($pd_data_percentage[$key])) { ?>
        	<input type="text" readonly name="amt[<?php echo $key; ?>]" value="<?php echo $totalpf; ?>" style="width:98%;">
             <input type="hidden" name="ac_type[<?php echo $key; ?>]" value="DR">
        	 <?php } ?>
        </td>
         <td><?php if(!isset($pd_data_percentage[$key])) { 
          $total_pf_amt=round(array_sum($total_amt));
         	?>
         	<input type="text" readonly name="amt[<?php echo $key; ?>]" value="<?php echo $total_pf_amt; ?>" style="width:98%;">
         	<input type="hidden" name="ac_type[<?php echo $key; ?>]" value="CR">
         	<?php } ?>
         	<input type="hidden" name="desc[]" value="<?php echo $key; ?>">        
         </td>		
      </tr>
      <?php } ?>          
    
	<tr>
  <td colspan="4"><h3 align="center">ESI Summary For The Month Of <?PHP echo GetMonth($month);?> &nbsp;<?php echo $years;?></h3></td>
  </tr>
	
      <tr class="text" bgcolor="#FFFFFF">
        <td><b>Salalry on which ESI deducted <b></td>
        <td></td>
        <td><?php  echo $total_gross1=$query_esi['gross_salary'];
                   $total_gross=round($query_esi['amt']*100/$Employee_per);
        ?></td>
        <td></td>
      </tr>
      <tr class="text" bgcolor="#FFFFFF">
        <td>ESI Contribution (<?php echo $Employee_per; ?>%) </td>
        <td><select name="ledger[esi]" style="width:98%;">
         <option value="<?php echo $Esi_employee_ledger['parameter_val'];  ?>"><?php echo $s->getField('accountledger','AccountsID',$Esi_employee_ledger['parameter_val'],'AccountName'); ?></option>
        </select>
        <input type="hidden" name="parameter_id[esi]" value="<?php echo $Esi_employee_ledger['parameter_id'];  ?>">
      </td>
        <td><?php //echo  $query_esi['amt'];
		$total_esiamt[]=($total_gross*$Employee_per)/100;
		?>
		<input type="text" readonly name="amt[esi]" value="<?php echo $query_esi['amt']; ?>" style="width:98%;"></td>
		<td><input type="hidden" name="desc[]" value="esi">
        <input type="hidden" name="ac_type[esi]" value="DR">
		</td>
      </tr>
       <?php foreach($EsI_data as $key=>$value)
      {?>
      <tr class="text" bgcolor="#FFFFFF">
        <td><?php echo $value; ?><?php  if(isset($EsI_data_percentage[$key]))  echo "--(". $EsI_data_percentage[$key]. "%)";  ?></td>
        <td><select name="ledger[<?php echo $key; ?>]" style="width:98%;">
       <?php
         $query2=DB_query("SELECT B.parameter_val FROM hr_posting_master A INNER JOIN `hr_posting_master_details` B
          ON A.id=B.hr_posting_id where B.parameter_type='".$key."' ",$db);
         while($row2=DB_fetch_array($query2))
         {
         ?>
<option value="<?php echo $row2['parameter_val']; ?>"><?php echo $s->getField('accountledger','AccountsID',$row2['parameter_val'],'AccountName'); ?></option>
<?php } ?>
        </select>
<input type="hidden" name="parameter_id[<?php echo $key; ?>]" value="">
      </td>
        <td><?php   $totalesi=round(($total_gross*$EsI_data_percentage[$key])/100);$total_esiamt[]=$totalesi; ?>
        	<?php if(isset($EsI_data_percentage[$key])) { ?>
        	<input type="text" readonly name="amt[<?php echo $key; ?>]" value="<?php echo $totalesi; ?>" style="width:98%;">  
        	<input type="hidden" name="ac_type[<?php echo $key; ?>]" value="DR"><?php } ?>
        	</td>
		<td><?php if(!isset($EsI_data_percentage[$key])) {
          $total_esi_amt=round(array_sum($total_esiamt));
		 ?>
			<input type="text" readonly name="amt[<?php echo $key; ?>]" value="<?php echo $total_esi_amt; ?>" style="width:98%;">
			<input type="hidden" name="ac_type[<?php echo $key; ?>]" value="CR">
			<?php } ?><input type="hidden" name="desc[]" value="<?php echo $key; ?>">       
		</td>
      </tr>
      <?php } ?>
 <tr class="row1">
 <td colspan="4" style="text-align:center;"><input type="submit" name="Save" class="search" value="Save"></td>
 </td>
    </table>
   
	<?php }  else { ?>
  <h3>Posting Already done for this month and year</h3>
   <?php } } ?>

</form> 
 </div>
</div>
<script>
function validate()
{
if(document.getElementById("site").value=='0')
{
alert("Must Select Site");
return false;
}
if(document.getElementById("paymonth").value=='0')
{
alert("Must Select Site");
return false;
}
if(document.getElementById("payyear").value=='0')
{
alert("Must Select Site");
return false;
}
return true;

}
</script>