<?
session_start();
$brcode = $_SESSION['brcode'];
if(strlen($brcode)<=0)
{
 print "<script> alert('Session timed out'); parent.document.location='../index.html'; </script>";
 return;
}
$dt=date('Y-m-d');

include("../opendb.php");
include("../test.php");
?>
<script>
var pc=[];
var pn=[];

function handleKeyUp(maxNumToShow)
{
    var selectObj, textObj, functionListLength;
    var i, searchPattern, numShown;

    // Set references to the form elements
    selectObj = document.forms[0].functionselect;
    textObj = document.forms[0].pcode;

    // Remember the function list length for loop speedup
    functionListLength = pc.length;

    // Set the search pattern depending
/*    if(document.forms[0].functionradio[0].checked == true)
    {
        searchPattern = "^"+textObj.value;
    }
    else
    {
        searchPattern = textObj.value;
    }
*/
        searchPattern = "^"+textObj.value;

    // Create a regulare expression
    re = new RegExp(searchPattern,"gi");
    // Clear the options list
    selectObj.length = 0;

    // Loop through the array and re-add matching options
    numShown = 0;
    for(i = 0; i < functionListLength; i++)
    {
        if(pc[i].search(re) != -1)
        {
            selectObj[numShown] = new Option(pc[i],pc[i]);
            numShown++;
        }
        // Stop when the number to show is reached
        if(numShown == maxNumToShow)
        {
            break;
        }
    }
    // When options list whittled to one, select that entry
    if(selectObj.length == 1)
    {
        selectObj.options[0].selected = true;
    }
}

// this function gets the selected value and loads the appropriate
// php reference page in the display frame
// it can be modified to perform whatever action is needed, or nothing
function handleSelectClick()
{
//    document.f1.pname.value=pn[document.f1.functionselect.selectedIndex];
    document.f1.pcode.value=document.f1.functionselect.value;

}

</script>
<link rel="stylesheet" href="../style.css" type="text/css" />
<style>
th{background-color:steelblue;color:white}
</style>

<center>
<form name='f1' action='<?print $PHP_SELF?>' method='post'>
<table>
<th colspan=4>TRACE AN ITEM</th>
<tr><td>Part Code </td><td><input type="text" name="pcode" VALUE="<?print $pcode?>" style="font-size:10pt;width:34ex;"><br>

</td></tr>
<!-- <tr><td>Part Name </td>
    <td><input type=text name='pname' size=32></td></tr> -->
<th colspan=3><input type=submit value='Search' name='sea'>
</td></tr>
</table>
</form>
<?
$sea = $_REQUEST['sea'];
$pcode = $_REQUEST['pcode'];
if($sea)
{
    
    mysqli_query($db,"drop table stockledger");
    $sq="create table stockledger (dat date ,partic varchar(250),inv double(12,2) default 0,outv double(12,2) default 0, brcode varchar(25),led varchar(240),rate double(10,2), partcode varchar(50))";
    // print $sq;
mysqli_query($db,$sq);
mysqli_query($db,"CREATE INDEX partcode on stockledger(partcode)");
mysqli_query($db,"delete from stockledger where brcode='$brcode'");

 $sql="select * from spareopstock where itemcode='$pcode' and brcode='$brcode'";
 $re1=mysqli_query($db,$sql);
 //print $pcode;
// print "<table border=1 width=70%><th>       $pcode <th>Qty";
 while($row=mysqli_fetch_array($re1))
 {
     mysqli_query($db,"insert into stockledger values('0000-00-00','Opening Stock','$row[qty]','0','$brcode','Opening','$row[ndpvalue]','$pcode')")or die('op err11'.mysqli_error($db));

   //print "<tr><td>Opening Stock </td><td align=right>$row[qty]</td></tr>";
   //$mcl = $row['qty'];
 }
 //print "</table><br>";
 //*********

 $sql="select * from sparepurchase where partcode='$pcode' and brcode='$brcode'";
 $re2=mysqli_query($db,$sql)or die(mysqli_error($db));
 if(mysqli_num_rows($re2)>0)
 { 
  while($row=mysqli_fetch_array($re2))
  {
      if($pcode=='IM300858L')
      {
          $qty1=$row['supqty']*20;
         
      }
      else if ($pcode=='IM300885L')
      {
        $qty1=$row['supqty']*11;  
      }
      else
      {
        $qty1=$row['supqty'];  
      }
    $sq="insert into stockledger values('$row[entrydate]','P.Bill No:-$row[pbillno],Pdate=".datetohtml($row['purdate']).", entrydate=".datetohtml($row['entrydate']).",GR No=$row[grno],Rate=$row[price],discount%=$row[disc],tax%=$row[tax],taxtype=$row[taxtype],-','$qty1','0','$brcode','$row[suplier]','$row[price]','$pcode')";
   //print $sq;
  mysqli_query($db,$sq) or die('pur err22'.mysqli_error($db));
  }
 }


 //*********
/*$t=0;
 $sql="select * from sparepurreturn where partcode='$pcode' and brcode='$brcode'";
 $re2=mysqli_query($db,$sql)or die(mysqli_error($db));
 if(mysqli_num_rows($re2)>0)
 { print "<table border=1 width=70%><th colspan=4>PURCHASE RETURN<tr><th>PBill No <th> Date <th>Qty";
  while($row=mysqli_fetch_array($re2))
  {
    print "<tr><td>$row[pbillno] </td><td>".datetohtml($row['entrydate'])."</td><td align=right>$row[retqty]</td></tr>";
   $t+=$row['retqty'];
  }
  print "<tr><td colspan=2 align=right><b>Total</td><td align=right><b>".putComa($t)."</td></tr>";
 print "</table><br>";
 }
 $mcl = $mcl - $t;*/
 //*********


 //*********
/*
 $sql="select * from spareinvoice where partcode='$pcode' and brcode='$brcode'";
 $re3=mysqli_query($db,$sql)or die(mysqli_error($db));
 if(mysqli_num_rows($re3)>0)
 { print "<table border=1 width=70%><th colspan=4>PURCHASE<tr><th>Inv No <th>P Date <th>Customer <th>Qty";
  while($row=mysqli_fetch_array($re3))
  {
    print "<tr><td align=center>$row[invno] </td><td>".datetohtml($row[entrydate])."</td>";
    print "<td>$row[customer]</td><td align=right>$row[qty]</td></tr>";
  }
 print "</table><br>";
 }
*/
 //*********
$t=0;
 $sql="select * from sparetransout where partcode='$pcode' and brcode='$brcode' and jvno>0";
 $re4=mysqli_query($db,$sql)or die(mysqli_error($db));
 $re4=mysqli_query($db,$sql)or die(mysqli_error($db));
 if(mysqli_num_rows($re4)>0)
 { 
  while($row=mysqli_fetch_array($re4))
  {
    $brname=getBranch($row['to_branch'],$db);
    $sq="insert into stockledger values('$row[entrydate]','Transfer out to $brname No:$row[tr_no]','0','$row[qty]','$brcode','$brname','$row[amount]','$pcode')";
    //print $sq;
  mysqli_query($db,$sq) or die('tr out err22'.mysqli_error($db));

  }
 }
 //*********
 $sql="select * from sparetransin where partcode='$pcode' and brcode='$brcode'";
 $re42=mysqli_query($db,$sql)or die(mysqli_error($db));
 if(mysqli_num_rows($re42)>0)
 { 
  while($row=mysqli_fetch_array($re42))
  {
    $brname=getBranch($row['from_branch'],$db);
    $sq="insert into stockledger values('$row[entrydate]','Transfer in from $brname No:$row[tr_no]','$row[qty]','0','$brcode','$brname','$row[amount]','$pcode')";
    // print $sq;
  mysqli_query($db,$sq) or die('pur err33'.mysqli_error($db));

  }
 }
 
 // other item veh pur
 
 $t=0;
 //*********
 $sql="select * from item_purchase_veh where item_code='$pcode' and brcode='$brcode'";
 $re42=mysqli_query($db,$sql)or die(mysqli_error($db));
 if(mysqli_num_rows($re42)>0)
 { while($row=mysqli_fetch_array($re42))
  {
    $sq="insert into stockledger values('$row[curdate]','P.Bill No:-$row[purinvoice],Pdate=".datetohtml($row['pdate']).", entrydate=".datetohtml($row['curdate']).",P.No=$row[purno],Rate=$row[uni_amt],discount%=$row[disc],tax%=$row[tax],taxtype=$row[taxtype],- $row[suplier]','$row[qty]','0','$brcode','$suplier','$row[uni_amt]','$pcode')";
  mysqli_query($db,$sq) or die('pur err44'.mysqli_error($db));
  }
 }
 //$mcl = $mcl + $t;
 
 //other item veh pur end
 
 //other item veh inv
 
  
$t=0;
 $sql="select * from spareinvoice_oth_veh where partcode='$pcode' and brcode='$brcode' and qty>0";
 $re5=mysqli_query($db,$sql)or die(mysqli_error($db));
 if(mysqli_num_rows($re5)>0)
 { while($row=mysqli_fetch_array($re5))
  {
    $sq="insert into stockledger values('$row[entrydate]','Spare Invoice to $row[customer] No:$row[invno] - MSNO:$row[msno]','0','$row[qty]','$brcode','$row[customer]','$row[aprice]','$pcode')";
  mysqli_query($db,$sq) or die('tr out err44'.mysqli_error($db));

  }
 }
 //$mcl = $mcl - $t;
 //other item veh inv end
 
 $t=0;
 //*********
 $sql="select * from sparetransinexcess where partcode='$pcode' and brcode='$brcode'";
 $re42=mysqli_query($db,$sql)or die(mysqli_error($db));
 if(mysqli_num_rows($re42)>0)
 { while($row=mysqli_fetch_array($re42))
  {
    $brname=getBranch($row['from_branch'],$db);
    $sq="insert into stockledger values('$row[entrydate]','Transfer in from $brname No:$row[tr_no]','$row[qty]','0','$brcode','$brname','$row[amount]','$pcode')";
  mysqli_query($db,$sq) or die('pur err55'.mysqli_error($db));

  }
 }
 //$mcl = $mcl + $t;
 
 
 $t=0;
 //********* excess out
 $sql="select * from sparetransoutexcess where partcode='$pcode' and brcode='$brcode'";
 $re42=mysqli_query($db,$sql)or die(mysqli_error($db));
 if(mysqli_num_rows($re42)>0)
 { while($row=mysqli_fetch_array($re42))
  {
    $brname=getBranch($row['to_branch'],$db);
    $sq="insert into stockledger values('$row[entrydate]','Transfer out to $brname No:$row[tr_no]','0','$row[qty]','$brcode','$brname','$row[amount]','$pcode')";
  mysqli_query($db,$sq) or die('tr out err55'.mysqli_error($db));

  }
 }
 //$mcl = $mcl - $t;
 
 
$t=0;
 $sql="select * from spareinvoice where partcode='$pcode' and brcode='$brcode' and qty>0";
 $re5=mysqli_query($db,$sql)or die(mysqli_error($db));
 if(mysqli_num_rows($re5)>0)
 { while($row=mysqli_fetch_array($re5))
  {
      
      if($pcode=='IM300858L')
      {
          $qty1=$row['qty']*20;
         
      }
      else if ($pcode=='IM300885L')
      {
        $qty1=$row['qty']*11;  
      }
      else
      {
        $qty1=$row['qty'];  
      }
      
    $sq="insert into stockledger values('$row[entrydate]','Spare Invoice to $row[customer] No:$row[invno] - MSNO:$row[msno]','0','$qty1','$brcode','$row[customer]','$row[aprice]','$pcode')";
  mysqli_query($db,$sq) or die('tr out err66'.mysqli_error($db));

  }
 }
// $mcl = $mcl - $t;

// purchase return

$t=0;
 $sql="select * from sparepurreteich  where partcode='$pcode' and brcode='$brcode' and qty>0";
 $re5=mysqli_query($db,$sql)or die(mysqli_error($db));
 if(mysqli_num_rows($re5)>0)
 { while($row=mysqli_fetch_array($re5))
  {
    $sq="insert into stockledger values('$row[entrydate]','Spare Invoice to $row[customer] No:$row[invno] - MSNO:$row[msno]','0','$row[qty]','$brcode','$row[customer]','$row[aprice]','$pcode')";
  mysqli_query($db,$sq) or die('tr out err77'.mysqli_error($db));

  }
 }
 
 // sales ret
 
 $sql="select * from salesretnew where partcode='$pcode' and brcode='$brcode'";
 $re511=mysqli_query($db,$sql)or die(mysqli_error($db));
 if(mysqli_num_rows($re511)>0)
 { while($row=mysqli_fetch_array($re511))
  {
      $retqty=$row['qty']*-1;
    $sq="insert into stockledger values('$row[entrydate]','Sales return from $row[customer] No:$row[invno] - MSNO:$row[msno]','$retqty','0','$brcode','$row[customer]','$row[aprice]','$pcode')";
  mysqli_query($db,$sq) or die('tr out err88'.mysqli_error($db));
  }
 }

//spare inv others


$t=0;
 $sql="select * from spareinvoice_oth where partcode='$pcode' and brcode='$brcode' and qty>0";
 $re5=mysqli_query($db,$sql)or die(mysqli_error($db));
 if(mysqli_num_rows($re5)>0)
 { while($row=mysqli_fetch_array($re5))
  {
    $sq="insert into stockledger values('$row[entrydate]','Spare Invoice to $row[customer] No:$row[invno] - MSNO:$row[msno]','0','$row[qty]','$brcode','$row[customer]','$row[aprice]','$pcode')";
  mysqli_query($db,$sq) or die('tr out err67'.mysqli_error($db));

  }
 }
// $mcl = $mcl - $t;
 
 //spare inv other end
$t=0;
//*********************** jcard

$jre=mysqli_query($db,"select distinct(invno) from jobcard where  brcode='$brcode' and cname not like '%ASSURANT%' and invno in(select invno from jcardparts where partcode='$pcode' and brcode='$brcode')")or die('Err j:'.mysqli_error($db));
//print "select distinct(invno) from jobcard where  brcode='$brcode' and cname not like '%ASSURANT%' and invno in(select invno from jcardparts where partcode='$pcode' and brcode='$brcode')";
//date>='$dfrom'
while($jrow=mysqli_fetch_array($jre))
{
    //$sq="";
 $sql="select * from jcardparts where partcode='$pcode' and brcode='$brcode'  and invno='$jrow[0]'";
 $re511=mysqli_query($db,$sql)or die(mysqli_error($db));
 if(mysqli_num_rows($re511)>0)
 { 
      while($row=mysqli_fetch_array($re511))
  {

 $sql="select * from jobcard where invno='$row[invno]' and brcode='$brcode'";
//print $sql;

 $r1=mysqli_query($db,$sql)or die(mysqli_error($db));
$r=mysqli_fetch_array($r1);

if($pcode=='IM300858L')
      {
          $qty1=$row['qty']*20;
         
      }
      else if ($pcode=='IM300885L')
      {
        $qty1=$row['qty']*11;  
      }
      else
      {
        $qty1=$row['qty'];  
      }
    $sq="insert into stockledger values('$row[date]','Jobcard InvNo:$row[invno], jcno:$row[jcno] - MSNO:$r[msno]','0','$qty1','$brcode','$r[cname]','$row[rate]','$pcode')";
//print $sq;
  mysqli_query($db,$sq) or die('jcard'.mysqli_error($db));
 }
 }
}
 //$mcl = $mcl - $t;
//*********************** Warranty jcard
/*$t=0;
 $sql="select * from warcouponinvoicepassey  where partcode='$pcode' and brcode='$brcode' and qty>0";
 $re5=mysqli_query($db,$sql)or die(mysqli_error($db));
 if(mysqli_num_rows($re5)>0)
 { print "<table border=1 width=70%><th colspan=5>WARRANTY INVOICE<tr><th>Inv No <th>Date <th>Customer <th>Qty <th>MSNO";
  while($row=mysqli_fetch_array($re5))
  {
    print "<tr><td align=center>$row[invno] </td><td>".datetohtml($row['entrydate'])."</td>";
    print "<td>$row[customer]</td><td align=right>$row[qty]</td><td>$row[jcno]</td></tr>";
    $t+=$row['qty'];
  }
 print "<tr><td colspan=3 align=right><b>Total</td><td align=right><b>".putComa($t)."</td></tr>";
 print "</table><br>";
 }
 $mcl = $mcl - $t;*/
//*********************** adjstock
 $sql="select * from adjstock where partcode='$pcode' and brcode='$brcode'";
///print $sql;
 $re512=mysqli_query($db,$sql)or die(mysqli_error($db));
 if(mysqli_num_rows($re512)>0)
 { while($row=mysqli_fetch_array($re512))
  {
    $sq="insert into stockledger values('$row[entrydate]','Stock ADJ No:$row[refno]','$row[qty]','$row[outqty]','$brcode','aa','$row[rate]','$pcode')";
    //print $sq;
  mysqli_query($db,$sq) or die('tr adj err'.mysqli_error($db));
  }
 }

$res=mysqli_query($db,"select * from stockledger where brcode='$brcode' order by dat");
print "<table border=1 cellspacing=1 cellpadding=5> 
<th colspan=7>Stock ledger of the item : $pcode<br>Branch $brcode <br>";
print "as on ".date("d-m-Y")."</th>
<tr><th>Date <th>Partic<th>Legder<th>Rate <th>In <th>Out<th>Balance";
$tin=0;$tout=0;$bal1=0;
while($row=mysqli_fetch_array($res))
{
   $bal1= $bal1+=$row['inv'];
$bal1=$bal1-=$row['outv'];
 print "<tr><td>".datetohtml($row['dat'])."</td><td>$row[partic]</td><td>$row[led]</td><td>$row[rate]</td><td align=right>".nozero($row['inv'])."</td> <td align=right>".nozero($row['outv'])."</td><td align=right>$bal1</td></tr>";
$tin+=$row['inv'];
$tout+=$row['outv'];

}

$bal=($tin-$tout);
if($bal>=0)
{
//print "<tr><td colspan=4 align=center><b>Balance </td><td align=right>$nbsp;</td><td align=right>".number_format($bal,2)."</td></tr>";
print "<tr><td colspan=4 align=center><b>Total </td><td align=right>".number_format($tin,2)."</td><td align=right>".number_format($tout,2)."</td><td align=right>".number_format($bal,2)."</td></tr>";

}
else
{
//print "<tr><td colspan=4 align=center><b>Balance </td><td align=right>$nbsp;</td><td align=right>".number_format($bal,2)."</td></tr>";
print "<tr><td colspan=4 align=center><b>Total </td><td align=right>".number_format($tin,2)."</td><td align=right>".number_format($tout,2)."</td><td align=right>".number_format($bal,2)."</td></tr>";

}
print "</table>";
return;

}// end if sea
   /* $mcl = $mcl + $tph;
$part=mysqli_query($db,"select lstprice from spareprmast where partcode = '$pcode' order by effectfrom desc limit 1");
$part1=mysqli_fetch_row($part);
$unit_price = $part1[0];
$tot = $mcl * $unit_price;*/
print  "<tr><td colspan=2 align=right><b>Closing Balance : </td><td align=right><b>$mcl  List Price : $unit_price  Total : $tot</td></tr>";


function getBranch($cde,$db)
{
 $brre=mysqli_query($db,"select brname from branch where brcode='$cde'");
 $brrow=mysqli_fetch_row($brre);
 return $brrow[0];
}

function getsupname($cde,$db)
{
 $brres=mysqli_query($db,"select supname from sup_master where icode='$cde'");
 $brrows=mysqli_fetch_row($brres);
 return $brrows[0];
}

?>
