<?
session_start();
$brcode=$_SESSION['brcode'];
$user=$_SESSION['user'];
if(strlen($brcode)<=0)
{
    print "<script> alert('Session timed out'); parent.document.location='inde.php'; </script>";
    return;
}
include("includes/top.php");
include("opendb.php");
$acpd=mysqli_query($db,"select * from acperiod") or die ('err'.mysqli_error($db));
     $acrow=mysqli_fetch_row($acpd);

      $acf= $acrow[0];
      $act= $acrow[1];
if(isset($_POST['upd']))
{
    $br_arr[]='';
    $brres=mysqli_query($db,"select brcode from branch") or die('post err'.mysqli_error($db));
    $a=1;
    while($brrow=mysqli_fetch_array($brres))
    {
        $br_arr[$a]=$brrow[0];
        $a++;
    }
    $logdt=date("Y-m-d");
    $logtime=date("H:i:s");
    $ff=$_REQUEST['ff'];
    if($ff !="none")
    {
        $imagename=$_FILES['ff']['name'];
        $filepath= "";
        $tmp = $_FILES['ff']['tmp_name'];
        move_uploaded_file($tmp, $filepath . $imagename) or die(' err:could not copy file');
        // print "File copied :".$fl;
    }
    $filepathval=$filepath.$imagename;
    $fp=fopen($filepath.$imagename,"r") or die("Cant open file");
    $ctr=1;
    $j=1;
    $invno_arr[]='';
    $invno_prv='';
    while(!feof($fp))
    {
        $line=fgets($fp,1024);
        $spline=explode("\t",$line);
        if($j==1)
        {
            //print_r($spline);
            $no_col=sizeof($spline);
            for($x=0;$x<=$no_col;$x++)
            {
                //if($spline[$x]=='Dealer Invoice no')
                if($spline[$x]=='SAP Invoice no')
                    $invno_no1 = $x;
                if($spline[$x]=='Purchase order Type')
                    $pord_type_no1 = $x;
            }
        }
        if($j>1)
        {
            if(trim($spline[$pord_type_no1])=='ZACR' || trim($spline[$pord_type_no1])=='ZVCO')
            {
                $invno1 = trim($spline[$invno_no1]);
                if($invno1!=$invno_prv)
                {
                    $query="select saprefno from sparetransout where saprefno ='$invno1'";
                    //print $query;
                    $rs=mysqli_query($db,$query);
                    if(mysqli_num_rows($rs)>0)
                    {
                        $invno_arr[$ctr++]=$invno1;
                    }
                }
                $invno_prv=$invno1;
            }
        }$j++;
    }
    //mysqli_query($db,"delete from sparetransout_test");
    //-------------------------------------------------------------
    $fp=fopen($filepath.$imagename,"r") or die("Cant open file");
    $invpr='';
    $i=1;
    while(!feof($fp))
    {
        $line=fgets($fp,1024);
        //print $line.$i++."<br>";
        $spline=explode("\t",$line);
        //print_r($spline);
        if($i==1)
        {
            // print_r($spline);
            $no_col=sizeof($spline);
            for($x=0;$x<=$no_col;$x++)
            {
                if($spline[$x]=='SO No.')
                    $tr_no_no = $x;
                if($spline[$x]=='Invoice date')
                    $entrydate_no = $x;
                if($spline[$x]=='SAP Invoice no')
                    $saprefno_no = $x;
                if($spline[$x]=='Material')
                    $partcode_no = $x;
                if($spline[$x]=='Material Desc.')
                    $partname_no = $x;
                if($spline[$x]=='Delivered Qty')
                    $qty_no = $x;
                if($spline[$x]=='Customer Code')
                    $to_branch_no = $x;
                if($spline[$x]=='Dealer Plant')
                    $brcode_no = $x; 
                if($spline[$x]=='NDP Per Unit')
                    $amount_no = $x;
                if($spline[$x]=='Billed Value NDP')
                    $aprice_no = $x;
                if($spline[$x]=='Purchase order Type')
                    $pord_type_no = $x;
            }
        }
        if($i>1)
        {
            $tr_no     = trim($spline[$tr_no_no]);
            $entrydate = trim($spline[$entrydate_no]);
            $saprefno  = trim($spline[$saprefno_no]);
            $partcode  = trim($spline[$partcode_no]);
            $partname  = trim($spline[$partname_no]);
            $partname  = remove_special($partname);
            $qty       = trim($spline[$qty_no]); 
            $to_branch = substr(trim($spline[$to_branch_no]),-4);
            $brcode1   = trim($spline[$brcode_no]);
            $amount    = trim($spline[$amount_no]);
            $aprice    = trim($spline[$aprice_no]);
            $pord_type = trim($spline[$pord_type_no]);
            $amount    = str_replace(",","",$amount);
            $amount    = str_replace('"',"",$amount);
            $aprice    = str_replace(",","",$aprice);
            $aprice    = str_replace('"',"",$aprice);
            $entrydate = str_replace('/', '-', $entrydate);
            if(strpos($entrydate,"-")<4)
                $entrydate =implode('-', array_reverse(explode('-', $entrydate)));
        }   
        
        if($i>1)
        {
            if(array_search($to_branch,$br_arr)>0)
            {
                $msaprefno =$saprefno;
                if(strlen($tr_no_no)<=0 || $saprefno_no<='' || $qty_no<='' || $amount_no<='' || $partcode_no<='')
                {
                    print $entrydate."<br>1";
                    print $partcode."<br>2";
                    print $qty."<br>3";
                    print $amount."<br>4";
                    print $to_branch."<br>5";
                    print "$tr_no_no<='' || $saprefno_no<='' || $qty_no<='' || $amount_no<='' || $partcode_no<=''  XX";
                    print "<script>alert('You $invdate_no have $itemno_no Selected $qty_no ab $total_no Wrong File...!!! $grno_no');</script>";
                    return;
                }
                //print $saprefno ;
                //print $invno_arr;
                if(array_search($saprefno,$invno_arr)>0)
                {
                    print "<font color='red'>Already Inserted.....!!!--".$saprefno."<br></font>";
                }
                else
                {
                    if(($pord_type=='ZACR' || $pord_type=='ZVCO') && $entrydate>=$acf && $entrydate<=$act)
                    {
                        $sql="insert into sparetransout (tr_no,trdate,entrydate,partcode,partname,pord_no,qty,to_branch,brcode,amount,saprefno,taxamt,aprice,taxp,gtot,grdate,gramount,trfrtype) values('$msaprefno','$entrydate','$entrydate','$partcode','".addslashes($partname)."','$tr_no','$qty','$to_branch','$brcode1','$amount','$msaprefno','0','$aprice','0','$aprice','0000-00-00','$aprice','agt')";
                        print $i.". ".$sql."<br>";
                        //$saprefno = $ptype;
                        //$msaprefno =$saprefno;
                        print "<font color='blue'>Successfuly Inserted.....!!!--".$invno."<br></font>";
                        mysqli_query($db,$sql) or die('Insert Err:'.mysqli_error($db));
                    }
                }
            }
        }
        $i++; 
    }
    print "<script> alert('Successfully Uploaded....!!!');</script>";
    //return;
    //**** Accounts posting.....
    $sql="select sum(qty*amount),sum(taxamt),sum(gtot),brcode,saprefno,entrydate,to_branch,grno,taxamt,taxp,tr_no from sparetransout where jvno<=0 and trfrtype='agt' and brcode='$brcode'  group by tr_no";
    
    //print $sql;
    $pres=mysqli_query($db,$sql) or die('post err'.mysqli_error($db));
    while($row23=mysqli_fetch_array($pres))
    {
        
        $jvres=mysqli_query($db,"select max(jvno) from journal2 where brcode='$brcode'")or die('err'.mysqli_error($db));
    $jvrow=mysqli_fetch_array($jvres)or die('err1'.mysqli_error($db));
    if($jvrow[0]>0)
    {
        $jvno=($jvrow[0]+1);
    }
    else
    {
        $jvno=1;
    } 
    	$re=mysqli_query($db,"select brled from branch where brcode='$row23[to_branch]'")or die('err2'.mysqli_error($db));
        $rr=mysqli_fetch_row($re);
        $branch_na=$rr[0];
        $sql="update sparetransout set jvno='$jvno' where tr_no='$row23[tr_no]' and brcode = '$row23[brcode]'";
        print $sql."<br>";
        mysqli_query($db,$sql) or die('Insert Err:'.mysqli_error($db));
        //*************** posting
	    $ledger1='STOCK TRANSFER OUT IGST '.$row23[9];
        $ledger2='STOCK TRANSFER OUT';
        $ledger3='OUTPUT IGST STOCK TRANSFER OUT '.$row23[9]; 
        
        if($row23['taxamt']<=0)
        {
		    mysqli_query($db,"insert into journal2(date, jvno, type, partic, dramt, cramt, narr, brcode, j_type, against, no, tr_no,prftcntr,docdt) values('$row23[entrydate]','$jvno','Cr','STOCK TRANSFER OUT','0','$row23[0]','Stock Transfer Out No:$row23[tr_no]','$row23[brcode]','Spare trans out agt','Trans no','$row23[grno]','$row23[tr_no]','$logtime $user','$logdt')");
		    //print "insert into journal2(date, jvno, type, partic, dramt, cramt, narr, brcode, j_type, against, no, tr_no,prftcntr,docdt) values('$row23[entrydate]','$jvno','Cr','STOCK TRANSFER OUT','0','$row23[0]','Stock Transfer Out No:$row23[saprefno]','$row23[brcode]','Spare trans out','Trans no','$row23[grno]','$row23[saprefno]','$logtime $user','$logdt')";
        }
        else
        {
    		mysqli_query($db,"insert into journal2(date, jvno, type, partic, dramt, cramt, narr, brcode, j_type, against, no, tr_no,prftcntr,docdt) values('$row23[entrydate]','$jvno','Cr','$ledger1','0','$row23[0]','Stock Transfer Out No:$row23[tr_no]','$row23[brcode]','Spare trans out agt','Trans no','$row23[grno]','$row23[tr_no]','$logtime $user','$logdt')");  
		    mysqli_query($db,"insert into journal2(date, jvno, type, partic, dramt, cramt, narr, brcode, j_type, against, no, tr_no,prftcntr,docdt) values('$row23[entrydate]','$jvno','Cr','$ledger3','0','$row23[1]','Stock Transfer Out No:$row23[tr_no]','$row23[brcode]','Spare trans out agt','Trans no','$row23[grno]','$row23[tr_no]','$logtime $user','$logdt')");  
        }
        mysqli_query($db,"insert into journal2(date, jvno, type, partic, dramt, cramt, narr, brcode, j_type, against, no, tr_no,prftcntr,docdt) values('$row23[entrydate]','$jvno','Dr','$branch_na','$row23[2]','0','Stock Transfer Out No::$row23[tr_no]','$row23[brcode]','Spare trans out agt','Trans no','$row23[grno]','$row23[tr_no]','$logtime $user','$logdt')")or die('err2br'.mysqli_error($db));
    }
   // exit;
    //****
}

function remove_special($str){
     $newstr = "";
     for ($ctr=0;$ctr<=strlen($str);$ctr++)
     {
         if(substr($str,$ctr,1)>=' ' && substr($str,$ctr,1)<='}')
             $newstr=$newstr.substr($str,$ctr,1);
     }
     return $newstr;
}
?>
<link rel="stylesheet" href="style.css" type="text/css" />
<style>
body,td{font-family:'calibri';letter-spacing:1;}
tr{background-color:rgb(95, 198, 547);}
.mnu{margin-left:5}
</style>
<center>
<br><br><br><br><br><br>
<form action='' method='post' ENCTYPE="multipart/form-data">
<table border=1 width=60%>
<tr><td> 
<table width=100%>
<th colspan=2><h1>Upload Aggregate Stock Transfer </h1>
<tr><td>File <input type=file name='ff'><input type=submit value='Upload' name='upd'></td></tr>
<tr><td>(Convert it to TAB TEXT Format and upload)
 </td></tr>
</table>
</td></tr>
</table>
</form>