<?php
$link = mssql_connect('192.168.1.30\SAP', 'sa', 'sap123');

$_SESSION['itemcode'] = $_GET['itemcode'];
 
if (!$link)
    die('Unable to connect!');
 
if (!mssql_select_db('btocumen', $link))
    die('Unable to select database!');
 
$result = mssql_query("SELECT oitw.itemcode,
 oitw.whscode,owhs.whsname,onhand,a.Price,b.Price as venta,convert(varchar,isnull(c.docdate,'2000-01-01'),103) as fecha,isnull(c.quantity,0) as cant
FROM oitw
LEFT OUTER JOIN owhs ON oitw.whscode=owhs.whscode


LEFT OUTER JOIN itm1 a ON oitw.itemcode=a.itemcode AND a.PriceList=1
LEFT OUTER JOIN itm1 b ON oitw.itemcode=b.itemcode AND b.PriceList=4

left outer join (
select   owtr.docdate,(WTR1.docentry),'01' as whscode,(WTR1.Quantity) from dbo.OWTR 

left outer join dbo.WTR1 on owtr.docentry=wtr1.docentry

where WTR1.docentry in (select top 1 max(WTR1.docentry) from dbo.OWTR 

left outer join dbo.WTR1 on owtr.docentry=wtr1.docentry

where owtr.docdate>'2015-01-01' and itemcode='".$_SESSION['itemcode']."'

and whscode='01' and quantity>0) and itemcode='".$_SESSION['itemcode']."'
union

select   owtr.docdate,(WTR1.docentry),'02' as whscode,(WTR1.Quantity) from dbo.OWTR 

left outer join dbo.WTR1 on owtr.docentry=wtr1.docentry

where WTR1.docentry in (select top 1 max(WTR1.docentry) from dbo.OWTR 

left outer join dbo.WTR1 on owtr.docentry=wtr1.docentry

where owtr.docdate>'2015-01-01' and itemcode='".$_SESSION['itemcode']."'

and whscode='02' and quantity>0) and itemcode='".$_SESSION['itemcode']."'
union

select   owtr.docdate,(WTR1.docentry),'03' as whscode,(WTR1.Quantity) from dbo.OWTR 

left outer join dbo.WTR1 on owtr.docentry=wtr1.docentry

where WTR1.docentry in (select top 1 max(WTR1.docentry) from dbo.OWTR 

left outer join dbo.WTR1 on owtr.docentry=wtr1.docentry

where owtr.docdate>'2015-01-01' and itemcode='".$_SESSION['itemcode']."'

and whscode='03' and quantity>0) and itemcode='".$_SESSION['itemcode']."'

union

select   owtr.docdate,(WTR1.docentry),'04' as whscode,(WTR1.Quantity) from dbo.OWTR 

left outer join dbo.WTR1 on owtr.docentry=wtr1.docentry

where WTR1.docentry in (select top 1 max(WTR1.docentry) from dbo.OWTR 

left outer join dbo.WTR1 on owtr.docentry=wtr1.docentry

where owtr.docdate>'2015-01-01' and itemcode='".$_SESSION['itemcode']."'

and whscode='04' and quantity>0) and itemcode='".$_SESSION['itemcode']."'

union

select   owtr.docdate,(WTR1.docentry),'05' as whscode,(WTR1.Quantity) from dbo.OWTR 

left outer join dbo.WTR1 on owtr.docentry=wtr1.docentry

where WTR1.docentry in (select top 1 max(WTR1.docentry) from dbo.OWTR 

left outer join dbo.WTR1 on owtr.docentry=wtr1.docentry

where owtr.docdate>'2015-01-01' and itemcode='".$_SESSION['itemcode']."'

and whscode='05' and quantity>0) and itemcode='".$_SESSION['itemcode']."') c on oitw.whscode=c.whscode


WHERE oitw.itemcode='".$_SESSION['itemcode']."' AND oitw.whscode IN ('01','02','03','04','05')");

$return_arr = array();
while ($row = mssql_fetch_array($result)) {
    
   
    $row_array['itemcode'] = $row['itemcode'];
    $row_array['whscode'] = $row['whscode'];
    $row_array['whsname'] = $row['whsname'];
    $row_array['onhand'] = $row['onhand'];
    $row_array['Price'] = $row['Price'];
    $row_array['venta'] = $row['venta'];

    $row_array['fecha'] = $row['fecha'];
    $row_array['cant'] = $row['cant'];


    array_push($return_arr,$row_array);

//$msg = array("itemcode" => $row['itemcode'], "whscode" => $row['whscode'],"whsname" => $row['whsname'],"onhand" => $row['onhand']);
}

$json = "SELECT oitw.itemcode, oitw.whscode,owhs.whsname,onhand,a.Price,b.Price as venta,convert(varchar,isnull(c.docdate,'2000-01-01'), 103) as fecha,isnull(c.quantity,0) as cant
FROM oitw
LEFT OUTER
JOIN owhs ON oitw.whscode=owhs.whscode
LEFT OUTER JOIN itm1 a ON oitw.itemcode=a.itemcode AND a.PriceList=1
LEFT OUTER JOIN itm1 b ON oitw.itemcode=b.itemcode AND b.PriceList=4

left outer join (
select   owtr.docdate,(WTR1.docentry),'01' as whscode,(WTR1.Quantity) from dbo.OWTR 

left outer join dbo.WTR1 on owtr.docentry=wtr1.docentry

where WTR1.docentry in (select top 1 max(WTR1.docentry) from dbo.OWTR 

left outer join dbo.WTR1 on owtr.docentry=wtr1.docentry

where owtr.docdate>'2015-01-01' and itemcode='".$_SESSION['itemcode']."'

and whscode='01' and quantity>0) and itemcode='".$_SESSION['itemcode']."'
union

select   owtr.docdate,(WTR1.docentry),'02' as whscode,(WTR1.Quantity) from dbo.OWTR 

left outer join dbo.WTR1 on owtr.docentry=wtr1.docentry

where WTR1.docentry in (select top 1 max(WTR1.docentry) from dbo.OWTR 

left outer join dbo.WTR1 on owtr.docentry=wtr1.docentry

where owtr.docdate>'2015-01-01' and itemcode='".$_SESSION['itemcode']."'

and whscode='02' and quantity>0) and itemcode='".$_SESSION['itemcode']."'
union

select   owtr.docdate,(WTR1.docentry),'03' as whscode,(WTR1.Quantity) from dbo.OWTR 

left outer join dbo.WTR1 on owtr.docentry=wtr1.docentry

where WTR1.docentry in (select top 1 max(WTR1.docentry) from dbo.OWTR 

left outer join dbo.WTR1 on owtr.docentry=wtr1.docentry

where owtr.docdate>'2015-01-01' and itemcode='".$_SESSION['itemcode']."'

and whscode='03' and quantity>0) and itemcode='".$_SESSION['itemcode']."'

union

select   owtr.docdate,(WTR1.docentry),'05' as whscode,(WTR1.Quantity) from dbo.OWTR 

left outer join dbo.WTR1 on owtr.docentry=wtr1.docentry

where WTR1.docentry in (select top 1 max(WTR1.docentry) from dbo.OWTR 

left outer join dbo.WTR1 on owtr.docentry=wtr1.docentry

where owtr.docdate>'2015-01-01' and itemcode='".$_SESSION['itemcode']."'

and whscode='05' and quantity>0) and itemcode='".$_SESSION['itemcode']."') c on oitw.whscode=c.whscode


WHERE oitw.itemcode='".$_SESSION['itemcode']."' AND oitw.whscode IN ('01','02','03','04','05')";

 header('content-type: application/json');
echo json_encode($return_arr);
mssql_free_result($result);
 
?>