<?
session_start();
$brcode=$_SESSION['brcode'];
$user=$_SESSION['user'];
//$br=$_SESSION['branch'];
if(strlen($user)<=0)
{
   print "<script> alert('Invalid User'); document.location='inde.php' </script>";
   return;
}
include("opendb.php");
include("test.php");
?>
<link href="css/form.css" type="text/css" rel="stylesheet" media="screen" />
<? include('includes/top.php'); ?>
<link rel="stylesheet" href="cal/jquery-ui.css" />
    <script src="cal/jquery-1.9.1.js"></script>
    <script src="cal/jquery-ui1.js"></script>
    <link rel="stylesheet" href="/resources/demos/style.css" />
<script type="text/javascript">
$(function() {
$( "#ref_date" ).datepicker("destroy");
     var fyr=document.getElementById('f_yr').value;
     var splv=fyr.explode("-");
     var syr=splv[0];
     var eyr='20'.concat(splv[1]);
     var syr1='01-04-';
     var eyr1='31-03-';
     var stfnyr=syr1.concat(syr);
     var efnyr=eyr1.concat(eyr);
     $("#ref_date").datepicker({ dateFormat: 'dd-mm-yy', minDate: stfnyr, maxDate: efnyr});

$( "#ref_date1" ).datepicker("destroy");
     var fyr=document.getElementById('f_yr').value;
     var splv=fyr.explode("-");
     var syr=splv[0];
     var eyr='20'.concat(splv[1]);
     var syr1='01-04-';
     var eyr1='31-03-';
     var stfnyr=syr1.concat(syr);
     var efnyr=eyr1.concat(eyr);
     $("#ref_date1").datepicker({ dateFormat: 'dd-mm-yy', minDate: stfnyr, maxDate: efnyr});
        });
</script>
<body bgcolor='lavender'>
<form name='f1' id='f1' action='<?print $PHP_SELF?>' method='POST' onSubmit="return valid()">
<br>
<CENTER>
<table border=1 cellspacing=1 cellpadding=1 class=report>
<tr bgColor='c1d7bb'><td colspan=7 align=center bgcolor='8A8D89'>
</TD></TR>
<tr><td bgcolor="8A8D89" align='left'><font color='ivory'><b>Account Head</b></td><td bgcolor="8A8D89" align='left'><font color='ivory'><b>Transaction</b></td></tr>
<?
$qry=mysqli_query($db,"select * from  transactmast order by trnstyp ASC");
?>
<tr>
<td>Report For</td>
<td><select name='jtype'>
<option value='select'>-select-</option>
<?
while($row=mysqli_fetch_array($qry))
{
?>
       <option value='<? echo $row[trnsid] ?>'><? echo $row[trnstyp] ?></option>
<?
}
?>
</select>
</td></tr>
<tr><td> From <input type=hidden name='f_yr' id='f_yr' value='<? echo $fin_year ?>'></td>
<td><input type="text" id="ref_date" value="<?echo date('d-m-Y');?>" name="fromdt" readonly></td>
</tr>
<tr><td> To </td>
<td><input type="text" id="ref_date1" value="<?echo date('d-m-Y');?>" name="todt" readonly></td>
</tr>
<tr><td colspan=6 align=center bgcolor='8A8D89'>
<input type=submit value='SHOW' name='sprint'>
<input type=reset value='CANCEL'></td></tr>
</td></tr>
</table>
</center>
<?
if(isset($_POST[sprint]))
{
   $jtype = $_REQUEST['jtype'];
   $dfrom = $_REQUEST['fromdt'];
   $dto   = $_REQUEST['todt'];
   $fromdt= implode('-', array_reverse(explode('-', $dfrom)));
   $todt  = implode('-', array_reverse(explode('-', $dto)));
   print "<table class='report' border='1'>";
   $tbl="tmp_trans1".$brcode.$user;
   mysqli_query($db,"drop table $tbl");
   mysqli_query($db,"CREATE TABLE $tbl (date date,partic varchar(200),tr_no varchar(200),dramt double(10,2) default 0,cramt double(10,2) default 0,grp varchar(200),no varchar(200),docdt date,jvno varchar(100))");
   mysqli_query($db,"delete from $tbl");
   mysqli_query($db,"insert into $tbl (date,partic,tr_no,dramt,cramt,grp,no,docdt,jvno) select date,partic,tr_no,dramt,cramt,grp,no,docdt,jvno from journal2,ledger where journal2.brcode='$brcode' and date>='$fromdt' and date<='$todt' and j_type = '$jtype' and ledger.led = journal2.partic
group by tr_no,partic") or die('err12'.mysqli_error($db));
   $qry1=mysqli_query($db,"select distinct partic from $tbl where (grp != 'Sundry Debtors' and grp != 'Sundry Creditors')");
   $mstr_sp = "";
   $mstr_tax = "";
   $mstr_cess = "";
   $mstr_oth = "";
   $colmn_sp[]='';
   $colmn_tax[]='';
   $colmn_cess[]='';
   $colmn_oth[]='';
   $ctr_sp = 0;
   $ctr_tax = 0;
   $ctr_cess = 0;
   $ctr_oth = 0;
   while($row=mysqli_fetch_array($qry1))
   {
       if(stripos(" ".$row[0],'SALES')>0)
       {
           if(stripos(" ".$row[0],'TAX')>0)
           {
               $mstr_tax .= "<td><b>".$row[0]."</td>";
               $colmn_tax[$ctr_tax++]=$row[0];
           }
           else
           {
               $mstr_sp .= "<td><b>".$row[0]."</td>";
               $colmn_sp[$ctr_sp++]=$row[0];
           }
       } 
       else if(stripos(" ".$row[0],'PURCHASE')>0)
       {
           if(stripos(" ".$row[0],'TAX')>0)
           {
               $mstr_tax .= "<td><b>".$row[0]."</td>";
               $colmn_tax[$ctr_tax++]=$row[0];
           }
           else
           {
               $mstr_sp .= "<td><b>".$row[0]."</td>";
               $colmn_sp[$ctr_sp++]=$row[0];
           }
       } 
       else if((stripos(" ".$row[0],'LABOUR')>0)||(stripos(" ".$row[0],'SERVICE')>0))
       {
           if(stripos(" ".$row[0],'TAX')>0)
           {
               $mstr_tax .= "<td><b>".$row[0]."</td>";
               $colmn_tax[$ctr_tax++]=$row[0];
           }
           else
           {
               $mstr_sp .= "<td><b>".$row[0]."</td>";
               $colmn_sp[$ctr_sp++]=$row[0];
           }
       }
       else
       {       
           if(stripos(" ".$row[0],'CESS')>0)
           {
               $mstr_cess .="<td><b>".$row[0]."</td>";
               $colmn_cess[$ctr_cess++]=$row[0];
           }
           else
           {
               $mstr_oth .="<td><b>".$row[0]."</td>";
               $colmn_oth[$ctr_oth++]=$row[0];
           }
       }
   }
   print "<tr><td><b>Date</td><td><b>Trans No</td><td><b>Doc No</td><td><b>Doc Date</td><td><b>Party</td>".$mstr_sp.$mstr_tax.$mstr_cess.$mstr_oth."</tr>";
   $qry2=mysqli_query($db,"select * from $tbl");
   $mprv_jvno = '';
   while($row2=mysqli_fetch_array($qry2))
   {
       
       $a = array_search('$row2[1]',$colmn_sp);
       print "$a = array_search('$row2[1]',$colmn_sp)<br>";
       if($row[jvno]!=$mprv_jvno)
       {
                   
       }       
   }

exit;
   $colmn[]="";
   $ctr = 0;
   $qry1=mysqli_query($db,"select * from  reportmast where trnstype = '$jtype'");
   $row1=mysqli_fetch_array($qry1);
   $colmn[0]=$row1[col3];
   $colmn[1]=$row1[col4];
   $colmn[2]=$row1[col5];
   $colmn[3]=$row1[col6];
   $colmn[4]=$row1[col7];
   $colmn[5]=$row1[col8];
   $colmn[6]=$row1[col9];
   $colmn[7]=$row1[col10];
   $mtr_no = "";
   $colname[0]=$row1[col3name];
   $colname[1]=$row1[col4name];
   $colname[2]=$row1[col5name];
   $colname[3]=$row1[col6name];
   $colname[4]=$row1[col7name];
   $colname[5]=$row1[col8name];
   $colname[6]=$row1[col9name];
   $colname[7]=$row1[col10name];
   $qry2=mysqli_query($db,"select trnstyp from  transactmast where trnsid = '$jtype'");
   $row2=mysqli_fetch_array($qry2);
   $mstr= "<tr><td><b>Date</td><td><b>Trans No</td><td><b>Doc No</td><td><b>Doc Date</td>";
   $cc=5;
   for ($c=0;$c<=7;$c++)
   {
        if($colmn[$c]!="select")
        {
            $mstr = $mstr."<td><b>$colname[$c]</td>";
            $cc++;
        }
   }
   $mstr=$mstr."<td>Total</td></tr>";
   print "<tr><td colspan=".$cc." align='center'><b>$row2[0] REGISTER FROM $dfrom TO $dto</td></tr>";
   print $mstr;
   $col3_tot = 0;
   $col4_tot = 0;
   $col5_tot = 0;
   $col6_tot = 0;
   $col7_tot = 0;
   $col8_tot = 0;
   $col9_tot = 0;
   $col10_tot = 0;
   $tcol3_tot = 0;
   $tcol4_tot = 0;
   $tcol5_tot = 0;
   $tcol6_tot = 0;
   $tcol7_tot = 0;
   $tcol8_tot = 0;
   $tcol9_tot = 0;
   $tcol10_tot = 0;
   $ctr =1;
   $qry=mysqli_query($db,"select date,partic,tr_no,sum(dramt) as dr,sum(cramt) as cr,grp,max(no),max(docdt) from  $tbl group by date,tr_no,grp");
   while($row=mysqli_fetch_array($qry))
   {
        if($mtr_no != $row[tr_no] && $ctr!= 1)
        {
             print "<tr><td>".implode('-', array_reverse(explode('-', $minv_date)))."</td>";
             print "<td>$mtr_no</td><td>$mno</td><td>$mdocdt</td><td>$mname</td>";
             if($colmn[1]!='select')
                  print "<td align='right'>".putcoma(abs($col4_tot),',','.')."</td>";
             if($colmn[2]!='select')
                  print "<td align='right'>".putcoma(abs($col5_tot),',','.')."</td>";                 
             if($colmn[3]!='select')
                  print "<td align='right'>".putcoma(abs($col6_tot),',','.')."</td>";                 
             if($colmn[4]!='select')
                  print "<td align='right'>".putcoma(abs($col7_tot),',','.')."</td>";                 
             if($colmn[5]!='select')
                  print "<td align='right'>".putcoma(abs($col8_tot),',','.')."</td>";                 
             if($colmn[6]!='select')
                  print "<td align='right'>".putcoma(abs($col9_tot),',','.')."</td>";                 
             if($colmn[7]!='select')
                  print "<td align='right'>".putcoma(abs($col10_tot),',','.')."</td>";   
             print "<td align='right'>".putcoma(abs($col3_tot),',','.')."</td>"; 
             print "</tr>";
             $tcol3_tot +=$col3_tot;
             $tcol4_tot +=$col4_tot;
             $tcol5_tot +=$col5_tot;
             $tcol6_tot +=$col6_tot;
             $tcol7_tot +=$col7_tot;
             $tcol8_tot +=$col8_tot;
             $tcol9_tot +=$col9_tot;
             $tcol10_tot +=$col10_tot;
             $col3_tot = 0;
             $col4_tot = 0;
             $col5_tot = 0;
             $col6_tot = 0;
             $col7_tot = 0;
             $col8_tot = 0;
             $col9_tot = 0;
             $col10_tot = 0;
        }
        if($row[5] == $colmn[0])
        {
             $col3_tot += $row[dr]-$row[cr];
             $mname = $row[1];
        }
        else if($row[5] == $colmn[1])
             $col4_tot += $row[dr]-$row[cr];
        else if($row[5] == $colmn[2])
             $col5_tot += $row[dr]-$row[cr];
        else if($row[5] == $colmn[3])
             $col6_tot += $row[dr]-$row[cr];
        else if($row[5] == $colmn[4])
             $col7_tot += $row[dr]-$row[cr];
        else if($row[5] == $colmn[5])
             $col8_tot += $row[dr]-$row[cr];
        else if($row[5] == $colmn[6])
             $col9_tot += $row[dr]-$row[cr];
        else if($row[5] == $colmn[7])
             $col10_tot += $row[dr]-$row[cr];
        $minv_date = $row[0];
        $mno = $row[6];
        $mdocdt = implode('-', array_reverse(explode('-', $row[7])));
        $mtr_no = $row[tr_no];
        $ctr++;
   }
   print "<tr><td>".implode('-', array_reverse(explode('-', $minv_date)))."</td>";
   print "<td>$mtr_no</td><td>$mno</td><td>$mdocdt</td><td>$mname</td>";
   if($colmn[1]!='select')
        print "<td align='right'>".putcoma(abs($col4_tot),',','.')."</td>";
   if($colmn[2]!='select')
        print "<td align='right'>".putcoma(abs($col5_tot),',','.')."</td>";                 
   if($colmn[3]!='select')
        print "<td align='right'>".putcoma(abs($col6_tot),',','.')."</td>";                 
   if($colmn[4]!='select')
        print "<td align='right'>".putcoma(abs($col7_tot),',','.')."</td>";                 
   if($colmn[5]!='select')
        print "<td align='right'>".putcoma(abs($col8_tot),',','.')."</td>";                 
   if($colmn[6]!='select')
        print "<td align='right'>".putcoma(abs($col9_tot),',','.')."</td>";                 
   if($colmn[7]!='select')
        print "<td align='right'>".putcoma(abs($col10_tot),',','.')."</td>";   
   print "<td align='right'>".putcoma(abs($col3_tot),',','.')."</td>"; 
   print "</tr>";
   $tcol3_tot +=$col3_tot;
   $tcol4_tot +=$col4_tot;
   $tcol5_tot +=$col5_tot;
   $tcol6_tot +=$col6_tot;
   $tcol7_tot +=$col7_tot;
   $tcol8_tot +=$col8_tot;
   $tcol9_tot +=$col9_tot;
   $tcol10_tot +=$col10_tot;
   print "<tr><td colspan=5 align='center'><b>T O T A L</td>";
   if($colmn[1]!='select')
        print "<td align='right'><b>".putcoma(abs($tcol4_tot),',','.')."</td>";
   if($colmn[2]!='select')
        print "<td align='right'><b>".putcoma(abs($tcol5_tot),',','.')."</td>";                 
   if($colmn[3]!='select')
        print "<td align='right'><b>".putcoma(abs($tcol6_tot),',','.')."</td>";                 
   if($colmn[4]!='select')
        print "<td align='right'><b>".putcoma(abs($tcol7_tot),',','.')."</td>";                 
   if($colmn[5]!='select')
        print "<td align='right'><b>".putcoma(abs($tcol8_tot),',','.')."</td>";                 
   if($colmn[6]!='select')
        print "<td align='right'><b>".putcoma(abs($tcol9_tot),',','.')."</td>";                 
   if($colmn[7]!='select')
        print "<td align='right'><b>".putcoma(abs($tcol10_tot),',','.')."</td>";   
   print "<td align='right'><b>".putcoma(abs($tcol3_tot),',','.')."</td>"; 
   print "</tr>";

   print "</table>";
//   mysqli_query($db,"drop table $tbl");
} // end save
?>

