<?
session_start();
$brcode=$_SESSION['brcode'];
$user=$_SESSION['user'];
if(strlen($brcode)<=0)
{
 print "<script> alert('Session timed out'); parent.document.location='index.php'; </script>";
 return;
}
include("includes/top.php");
include("opendb.php");
$sql1=mysqli_query($db,"select distinct invno,cname,date,msno,model,user from jobcard where ac_postdt IS NULL and brcode='$brcode' and date<'2017-07-01' order by invno")or die('Led Post Err3'.mysqli_error($db));

$dt_tm=date("Y-m-d");
if(mysqli_num_rows($sql1)<=0)
{
print "<script> alert('Posting Already Done...!!!'); </script>";
return;
}
else
{
while($row=mysqli_fetch_array($sql1))
{
       $invno = $row[0];
      $customer = ucwords($row[1]);
      $user = ucwords($row[5]);
      
       $entrydate = $row[2];
       $saleordno = $row[0];
       $re1=mysqli_query($db,"select max(jvno) from journal2 where brcode='$brcode'");
       $jvrow=mysqli_fetch_row($re1);
       $jvno = $jvrow[0]+1;
       
      /* $chre=mysqli_query($db,"select * from ledger where code='$row[3]' ");
       if(mysqli_num_rows($chre)<=0)
       {
          $code=$row[3];
          $code=str_replace(' ','-',$code);
          $sql="insert into ledger values('$customer','SUNDRY DEBTORS','0','Dr','$code','$brcode','2000-01-01')";
          echo $sql."<br>";
          mysqli_query($db,$sql);
       }*/
       
       //*****************
       
       

$res=mysqli_query($db,"select * from customer where code='$row[3]'") or die(mysqli_error($db));
 if(mysqli_num_rows($res)>0)
    {

$reacd=mysqli_query($db,"select * from ledger where code='$row[3]' and grp='Sundry Debtors' and brcode='$brcode' ") or die(mysqli_error($db));
  $rowacd=mysqli_fetch_array($reacd);

    if(mysqli_num_rows($reacd)<=0)
            {

 $reac=mysqli_query($db,"select * from ledger where code='$row[3]' and grp='Sundry Debtors' ") or die(mysqli_error($db));
 
      
       if(mysqli_num_rows($reac)>0)
             {
           $rowac=mysqli_fetch_array($reac); 
           $acname=$rowac[led];
           
$sqlop="insert into ledger values('".addslashes(trim($acname))."','Sundry Debtors','0','Dr','$row[3]','$brcode','2017-01-01')";
mysqli_query($db,$sqlop) or die('<b>Ledger creation error:'.mysqli_error($db));
             }
             else
             {
                $acname=$customer;
            $rea=mysqli_query($db,"select * from ledger where led='$acname' and grp='Sundry Debtors' and brcode='$brcode' ") or die(mysqli_error($db));
  $r=mysqli_fetch_array($rea);
  if(mysqli_num_rows($rea)>0)
             {
                 
               $sqlop="update ledger set code='$row[3]' where led='$acname' and brcode='$brcode' and grp='Sundry Debtors'";
              mysqli_query($db,$sqlop) or die('<b>Ledger updation error:'.mysqli_error($db));
}
else
{
    $sqlop="insert into ledger values('".addslashes(trim($acname))."','Sundry Debtors','0','Dr','$row[3]','$brcode','2017-01-01')";
mysqli_query($db,$sqlop) or die('<b>Ledger creation error:'.mysqli_error($db)); 
    
}


             }
        }
        else
        {
            $acname = trim($rowacd[led], '"');

            
        }
    }
    
  else
   {
       
$acname=$customer;

$sqlc="insert into customer values('$brcode','$row[3]', '2017-01-01','".addslashes(trim($user))."','','".addslashes(trim($acname))."','', '','','','','','','','','','','', '','0000-00-00','','new','','','','0','0','0')";
//echo $sqlc;
mysqli_query($db,$sqlc)or die("customer err".mysqli_error($db));


$res11=mysqli_query($db,"select * from ledger where led='".addslashes(trim($acname))."' and brcode='$brcode' ") or die(mysqli_error($db));
 if(mysqli_num_rows($res11)<=0)
{
$sqlop="insert into ledger values('".addslashes(trim($acname))."','Sundry Debtors','0','Dr','$row[3]','$brcode','2017-01-01')";
mysqli_query($db,$sqlop) or die('<b>Ledger creation error:'.mysqli_error($db));
}

  }


//*************

// $acname = trim($customer, '"');
//print "ok";
  // return;
   

       $jpres=mysqli_query($db,"select sum(gtot) from jcardparts where invno='$invno' and brcode='$brcode'")or die('jpres err'.mysqli_error($db));
       $jprow=mysqli_fetch_row($jpres);
       if($jprow[0]>0)
           $x=1;
       $spq="select sum(servamt+((servamt*servtx/100)+(servamt*edutx/100)+(servamt*hredutx/100))) from jcardserv where invno='$invno' and brcode='$brcode'";
       $jservres=mysqli_query($db,$spq)or die('jservres err'.mysqli_error($db));
       $jservrow=mysqli_fetch_row($jservres);
       if($jservrow[0]>0)
           $x=1;
       $gtot=($jprow[0]+$jservrow[0]);
       $sql="insert into journal2(date, jvno, type, partic, dramt, cramt, narr, brcode, j_type, against, no, tr_no,prftcntr) values('$entrydate','$jvno','Dr','".addslashes(trim($acname))."','".round($gtot)."','0','JC Spare Invno: $invno $row[4]','$brcode','JC Spare Invoice','Job Crd','$invno','$invno','$row[3]')";
       mysqli_query($db,$sql) or die('jv1 err:'.mysqli_error($db));
       //******** service
       $taxres11=mysqli_query($db,"select servtx, sum(servamt) from jcardserv where invno='$invno' and brcode='$brcode' group by servtx")or die('Led Post Err3'.mysqli_error($db));
       while($taxrow1=mysqli_fetch_array($taxres11))
       {
              if($taxrow1[1]>0)
              {
                    //$ledger='Labour (JC)'.$taxrow1[servtx].'%';
                    $ledger='LABOUR CHARGES';
                    $sql="insert into journal2(date, jvno, type, partic, dramt, cramt, narr, brcode, j_type, against, no, tr_no,prftcntr) values('$entrydate','$jvno','Cr','$ledger','0','$taxrow1[1]','JC Spare Invno: $invno $row[4]','$brcode','JC Spare Invoice','Job Crd','$invno','$invno','$row[3]')";
                    mysqli_query($db,$sql) or die('Led Post Err2'.mysqli_error($db));
              }
       }
//******** service2
       $taxres12=mysqli_query($db,"select servtx, sum((servamt*servtx)/100) from jcardserv where invno='$invno' and brcode='$brcode' group by servtx")or die('Led Post Err3'.mysqli_error($db));
       while($taxrow1=mysqli_fetch_array($taxres12))
       {      if($taxrow1[1]>0)
              {
              $ledger='SERVICE TAX 15.00';
              $sql="insert into journal2(date, jvno, type, partic, dramt, cramt, narr, brcode, j_type, against, no, tr_no,prftcntr) values('$entrydate','$jvno','Cr','$ledger','0','$taxrow1[1]','JC Spare Invno: $invno $row[4]','$brcode','JC Spare Invoice','Job Crd','$invno','$invno','$row[3]')";
              mysqli_query($db,$sql) or die('Led Post Err2'.mysqli_error($db));
              }
       }
//******** service3
       $taxres13=mysqli_query($db,"select edutx, sum((servamt*edutx)/100) from jcardserv where invno='$invno' and brcode='$brcode' group by edutx")or die('Led Post Err3'.mysqli_error($db));
       while($taxrow1=mysqli_fetch_array($taxres13))
       {
              if($taxrow1[1]>0)
              {
              $ledger='SWACHH BHARATH CESS 0.5%';
              $sql="insert into journal2(date, jvno, type, partic, dramt, cramt, narr, brcode, j_type, against, no, tr_no,prftcntr) values('$entrydate','$jvno','Cr','$ledger','0','$taxrow1[1]','JC Spare Invno: $invno $row[4]','$brcode','JC Spare Invoice','Job Crd','$invno','$invno','$row[3]')";
              mysqli_query($db,$sql) or die('Led Post Err2'.mysqli_error($db));
              }
       }
//******** service3 HR
       $taxres14=mysqli_query($db,"select hredutx, sum((servamt*hredutx)/100) from jcardserv where invno='$invno' and brcode='$brcode' group by hredutx")or die('Led Post Err3'.mysqli_error($db));
       while($taxrow1=mysqli_fetch_array($taxres14))
       {
             if($taxrow1[1]>0)
             {
             $ledger='KRISHI KALYAN CESS 0.5%';
             $sql="insert into journal2(date, jvno, type, partic, dramt, cramt, narr, brcode, j_type, against, no, tr_no,prftcntr) values('$entrydate','$jvno','Cr','$ledger','0','$taxrow1[1]','JC Spare Invno: $invno $row[4]','$brcode','JC Spare Invoice','Job Crd','$invno','$invno','$row[3]')";
             mysqli_query($db,$sql) or die('Led Post Err2'.mysqli_error($db));
             }
       }
//***************
       $sql="select sum(amt+(amt*hnd/100)),vat from jcardparts where invno='$invno' and brcode='$brcode' and itemtype='Spares' group by vat";
       $bfrres=mysqli_query($db,$sql) or die(mysqli_error($db));
       while($brrrow=mysqli_fetch_array($bfrres))
       {
              if($brrrow[0]>0)
              {
             $ledger='SPARES SALES '.$brrrow[vat];
             $sql="insert into journal2(date, jvno, type, partic, dramt, cramt, narr, brcode, j_type, against, no, tr_no,prftcntr) values('$entrydate','$jvno','Cr','$ledger','0','$brrrow[0]','JC Spare Invno: $invno $row[4]','$brcode','JC Spare Invoice','Job Crd','$invno','$invno','$row[3]')";
             mysqli_query($db,$sql) or die('Led Post Err1'.mysqli_error($db));
             }
       }
       //------------------------------
       $sql11="select sum(amt+(amt*hnd/100)),vat from jcardparts where invno='$invno' and brcode='$brcode' and itemtype!='Spares' group by vat";
       $bfrres1=mysqli_query($db,$sql11) or die(mysqli_error($db));
       while($brrrow1=mysqli_fetch_array($bfrres1))
       {
              if($brrrow1[0]>0)
              {
             $ledger='LUBRICANT SALES '.$brrrow1[vat];
             $sql="insert into journal2(date, jvno, type, partic, dramt, cramt, narr, brcode, j_type, against, no, tr_no,prftcntr) values('$entrydate','$jvno','Cr','$ledger','0','$brrrow1[0]','JC Spare Invno: $invno $row[4]','$brcode','JC Spare Invoice','Job Crd','$invno','$invno','$row[3]')";
             mysqli_query($db,$sql) or die('Led Post Err1'.mysqli_error($db));
             }
       }
//************vat
       $taxres=mysqli_query($db,"select vat, sum(taxamt) from jcardparts where invno='$invno' and brcode='$brcode' group by vat")or die('Led Post Err3'.mysqli_error($db));
       while($taxrow=mysqli_fetch_array($taxres))
       {
             if($taxrow[1]>0)
             {
             $ledger='LOCAL SALES TAX RECEIVED '.$taxrow[vat];
             $sql="insert into journal2(date, jvno, type, partic, dramt, cramt, narr, brcode, j_type, against, no, tr_no,prftcntr) values('$entrydate','$jvno','Cr','$ledger','0','$taxrow[1]','JC Spare Invno: $invno $row[4]','$brcode','JC Spare Invoice','Job Crd','$invno','$invno','$row[3]')";
             mysqli_query($db,$sql) or die('Led Post Err2'.mysqli_error($db));
             }
       }
//*************** cess
       $taxcessres=mysqli_query($db,"select sum(cessamt),cessp from jcardparts where invno='$invno' and brcode='$brcode' group by invno")or die('cess err.'.mysqli_error($db));
       while($taxcessrow=mysqli_fetch_array($taxcessres))
       {
             if($taxcessrow[0]>0)
             {
             $ledger='Output Cess (JC)'.$taxcessrow[1];
             $sql="insert into journal2(date, jvno, type, partic, dramt, cramt, narr, brcode, j_type, against, no, tr_no,prftcntr) values('$entrydate','$jvno','Cr','$ledger','0','$taxcessrow[0]','JC Spare Invno: $invno $row[4]','$brcode','JC Spare Invoice','Job Crd','$invno','$invno','$row[3]')";
             mysqli_query($db,$sql) or die('Led Post Err6'.mysqli_error($db));
             }
       }
       $jvdifres=mysqli_query($db,"select sum(dramt)-sum(cramt) from journal2 where jvno='$jvno' and brcode='$brcode'");
       $jvdifrow=mysqli_fetch_row($jvdifres);
       $dif=$jvdifrow[0];
       if($dif<0)
       {
             $sql="insert into journal2(date, jvno, type, partic, dramt, cramt, narr, brcode, j_type, against, no, tr_no,prftcntr) values('$entrydate','$jvno','Dr','Round Off','".abs($dif)."', '0','JC Spare Invno: $invno $row[4]','$brcode','JC Spare Invoice','Job Crd','$invno','$invno','$row[3]')";
             mysqli_query($db,$sql) or die("J V Write error dif1:".mysqli_error($db));
       }
       else if($dif>0)
       {
             $sql="insert into journal2(date, jvno, type, partic, dramt, cramt, narr, brcode, j_type, against, no, tr_no,prftcntr) values('$entrydate','$jvno','Cr','Round Off','0','".abs($dif)."','JC Spare Invno: $invno $row[4]','$brcode','JC Spare Invoice','Job Crd','$invno','$invno','$row[3]')";
             mysqli_query($db,$sql) or die("J V Write error dif:".mysqli_error($db));
       }
       $jdres=mysqli_query($db,"select sum(dramt),sum(cramt) from journal2 where jvno='$jvno' and brcode='$brcode'");
       $jdiff=mysqli_fetch_row($jdres);
       if($jdiff[0]!=$jdiff[1]||$jdiff[0]==0)
       {
             print "<sctipt> alert('Error in posting. Please check the data now');</script>";
       }
       mysqli_query($db,"update jobcard set jvno='$jvno',ac_postdt='$dt_tm',dcomm='$acname' where invno='$invno' and brcode='$brcode'");
       mysqli_query($db,"update jcardparts set jvno='$jvno' where invno='$invno' and brcode='$brcode'");
       mysqli_query($db,"update jcardserv set jvno='$jvno' where invno='$invno' and brcode='$brcode'");
}
print "<script> alert('Posting Done Successfully...!!!'); </script>";
}
?>