<?php
include ('class.ezpdf.php');
include 'includes/config.php';
include 'defs.php';
$pdf = new Cezpdf('A4','portrait');
$pdf->selectFont('fonts/Times-Roman.afm');
$pdf->addJpegFromFile('images/logo1.jpg',30,750,70);
$date1=Date("j-n-Y");
$pdf->ezSetDy(-105);
$pdf->ezStartPageNumbers(526, 16, 10, 'right', '', 1);
$pdf->setColor(0.72, 0.72, 0.72);
$pdf->filledRectangle(220, 770, 160, 30, 'rgb');
$pdf->setColor(0,0,0);
$pdf->addText(270,780,13,"<b>(GLAKB)</b>");
$pdf->addText(163,755,12,"<b>(NATIONWIDE WAREHOUSES STOCK REPORT)</b>");

$usr=$_GET['usr'];
$strFrom=$_POST['strFrom'];
$strTo=$_POST['strTo'];
$ware=$_POST['sman']; 



if ($strFrom=='')
{
$nResult = MySQLi_Query($link, "select MIN(Inv_Date) as dte from purchases");
while($rstRow = mysqli_fetch_array($nResult))
{
$strFrom=$rstRow['dte'];
}
}

$fdate=date('d-m-Y',strtotime($strFrom));
$tdate=date('d-m-Y',strtotime($strTo));

$header = $pdf->openObject();
$pdf->addText(20, 16, 10, "<b>Contact us @</b> Sabzal Road - Block No 3, STN Colony, Near Spini Road, Quetta (+92 81 2873538)");
$pdf->addText(20, 30, 10, "<b>_________________________________________________________________________________________________________________________________________________________________</b>");

$pdf->closeObject();
$pdf->addObject($header, "all");

$result42 = mysqli_query($link,"delete from stock");
$rst1 = MySQLi_Query($link, "Select Full_Name, API, Grade, Size, Carton_Qty, Unit_Liter, Article, Category2 from products order by Article");
while($rstRow = mysqli_fetch_array($rst1))
{
///$code1=$rstRow['Sufix']."-".$rstRow['Item_Code'];
InsertRec("stock",array(
"Code" => $code1,
"Name" =>$rstRow['Full_Name'],
"SKU" => $rstRow['Unit_Liter'],
"API" => $rstRow['API'],
"Grade" => $rstRow['Grade'],
"Size" => $rstRow['Size'],
"C_Qty" => $rstRow['Carton_Qty'],
"addby" => $usr,
"Article" => $rstRow['Article'],
"Category" => $rstRow['Category2'],

));
}


$rst2 = MySQLi_Query($link, "Select Name from stock order by Name");
while($rstRow = mysqli_fetch_array($rst2))
{
$fname=$rstRow['Name'];


$rst3 = MySQLi_Query($link, "Select Name, sum(Carton) as sctn from purchases where Name='$fname' and Inv_Date between '$strFrom' and '$strTo' and Warehouse='Karachi'  group by Name");
while($rstRow = mysqli_fetch_array($rst3))
{
$sctn=$rstRow['sctn'];


$result = mysqli_query($link, "update stock set K_Sin='$sctn' where Name = '$fname' ");
}

$rst4 = MySQLi_Query($link, "Select Name, sum(Carton) as sctn from purchases where Name='$fname' and Inv_Date between '$strFrom' and '$strTo' and Warehouse='Lahore'  group by Name");
while($rstRow = mysqli_fetch_array($rst4))
{
$sctn=$rstRow['sctn'];
$result = mysqli_query($link, "update stock set L_Sin='$sctn' where Name = '$fname' ");
}

$rst5 = MySQLi_Query($link, "Select Name, sum(Carton) as sctn from purchases where Name='$fname' and Inv_Date between '$strFrom' and '$strTo' and Warehouse='Quetta'  group by Name");
while($rstRow = mysqli_fetch_array($rst5))
{
$sctn=$rstRow['sctn'];
$result = mysqli_query($link, "update stock set Q_Sin='$sctn' where Name = '$fname' ");
}


$rst6 = MySQLi_Query($link, "Select Name, sum(Carton) as sctn from cust_sales where Name='$fname' and Inv_Date between '$strFrom' and '$strTo' and Warehouse='Karachi'  group by Name");
while($rstRow = mysqli_fetch_array($rst6))
{
$sctn=$rstRow['sctn'];
$result = mysqli_query($link, "update stock set K_Sout='$sctn' where Name = '$fname' ");
}

$rst7 = MySQLi_Query($link, "Select Name, sum(Carton) as sctn from cust_sales where Name='$fname' and Inv_Date between '$strFrom' and '$strTo' and Warehouse='Lahore'  group by Name");
while($rstRow = mysqli_fetch_array($rst7))
{
$sctn=$rstRow['sctn'];
$result = mysqli_query($link, "update stock set L_Sout='$sctn' where Name = '$fname' ");
}


$rst8 = MySQLi_Query($link, "Select Name, sum(Carton) as sctn from cust_sales where Name='$fname' and Inv_Date between '$strFrom' and '$strTo' and Warehouse='Quetta'  group by Name");
while($rstRow = mysqli_fetch_array($rst8))
{
$sctn=$rstRow['sctn'];
$result = mysqli_query($link, "update stock set Q_Sout='$sctn' where Name = '$fname' ");
}

}





if(!$ware)
{
//$pdf->addText(20,725,11,"<b>STOCK IN-HAND REPORT (IN CARTONS)</b>");
$pdf->addText(20,735,11,"<b>From = </b>$fdate <b> To: </b> $tdate ");
//$pdf->addText(410,725,11,"<b>WAREHOUSES</b>");



$count = 0;
$one   = 1;

$pdf->setColor(0.72, 0.72, 0.72);
$pdf->filledRectangle(15, 705, 577, 15, 'rgb');
$pdf->setColor(0,0,0);
$pdf->addText(16,722,12,"________________________________________________________________________________________________");
$pdf->addText(16,722,12,"_______________________________________________________________________________________________");

$pdf->addText(18, 710, 9, "<b>S.No</b>");
$pdf->addText(46, 710, 9, "<b>PRODUCT NAME</b>");
$pdf->addText(215, 710, 9, "<b>API</b>");
$pdf->addText(255, 710, 9, "<b>SIZE</b>");
$pdf->addText(300, 710, 9, "<b>CTN</b>");
$pdf->addText(340, 710, 9, "<b>LTR</b>");
$pdf->addText(375, 710, 9, "<b>CTN</b>");
$pdf->addText(415, 710, 9, "<b>LTR</b>");
$pdf->addText(450, 710, 9, "<b>CTN</b>");
$pdf->addText(490, 710, 9, "<b>LTR</b>");

$pdf->addText(528, 710, 9, "<b>CTN</b>");
$pdf->addText(565, 710, 9, "<b>LTR</b>");

$pdf->addText(305, 722, 9, "<b>KARACHI</b>");
$pdf->addText(381, 722, 9, "<b>LAHORE</b>");
$pdf->addText(456, 722, 9, "<b>QUETTA</b>");
$pdf->addText(531, 722, 9, "<b>G-TOTAL</b>");


$nResult = mysqli_query($link, "
    SELECT Name, API, Size, Grade, SKU, C_Qty,
           SUM(K_Sin-K_Sout) AS kstk,
           SUM(L_Sin-L_Sout) AS lstk,
           SUM(Q_Sin-Q_Sout) AS qstk
    FROM stock GROUP BY Name
    ORDER BY Article
");

$data = [];
$grandKstk = $grandQstk = $grandLstk = 0;
$grandKtlit = $grandQtlit = $grandLtlit = 0;
$grandTotc = $grandTotl = 0;

while($rstRow = mysqli_fetch_array($nResult)) {

    $ktlit = $rstRow['kstk'] * $rstRow['C_Qty'] * $rstRow['SKU'];
    $qtlit = $rstRow['qstk'] * $rstRow['C_Qty'] * $rstRow['SKU'];
    $ltlit = $rstRow['lstk'] * $rstRow['C_Qty'] * $rstRow['SKU'];

    $totc = $rstRow['kstk'] + $rstRow['qstk'] + $rstRow['lstk'];
    $totl = $ktlit + $qtlit + $ltlit;

    $count++;

    $data[] = array(
        'num'   => $count,
        'name'  => $rstRow['Name'],
        'api'   => $rstRow['API'],
        'size'  => $rstRow['Size'],
        'kstk'  => $rstRow['kstk'],
        'ktlit' => $ktlit,
        'lstk'  => $rstRow['lstk'],
        'ltlit' => $ltlit,
        'qstk'  => $rstRow['qstk'],
        'qtlit' => $qtlit,
        'totc'  => $totc,
        'totl'  => $totl
    );

    // accumulate grand totals
    $grandKstk  += $rstRow['kstk'];
    $grandKtlit += $ktlit;
    $grandLstk  += $rstRow['lstk'];
    $grandLtlit += $ltlit;
    $grandQstk  += $rstRow['qstk'];
    $grandQtlit += $qtlit;
    $grandTotc  += $totc;
    $grandTotl  += $totl;
}

// add grand total row
$data[] = array(
    'num'   => '',
    'name'  => '<b>GRAND TOTAL</b>',
    'api'   => '',
    'size'  => '',
    'kstk'  => '<b>' . $grandKstk  . '</b>',
    'ktlit' => '<b>' . $grandKtlit . '</b>',
    'lstk'  => '<b>' . $grandLstk  . '</b>',
    'ltlit' => '<b>' . $grandLtlit . '</b>',
    'qstk'  => '<b>' . $grandQstk  . '</b>',
    'qtlit' => '<b>' . $grandQtlit . '</b>',
    'totc'  => '<b>' . $grandTotc  . '</b>',
    'totl'  => '<b>' . $grandTotl  . '</b>'
);



$pdf->ezTable(
    $data,
    $cols,
    '',
    array(
        'xPos'         => 20,
        'xOrientation' => 'right',
        'fontSize'     => 7,
        'width'        => 680,
        'showHeadings' => 0,
        'shaded'       => 0,
        'showLines'    => 2,
        'cols'         => array(
            'num'   => array('justification'=>'center','width'=>25),
            'name'  => array('justification'=>'left','width'=>162),
            'api'   => array('justification'=>'center','width'=>44),
            'size'  => array('justification'=>'center','width'=>44),
            'kstk'  => array('justification'=>'right','width'=>35),
            'ktlit' => array('justification'=>'right','width'=>40),
            'lstk'  => array('justification'=>'right','width'=>35),
            'ltlit' => array('justification'=>'right','width'=>40),
            'qstk'  => array('justification'=>'right','width'=>35),
            'qtlit' => array('justification'=>'right','width'=>40),
            'totc'  => array('justification'=>'right','width'=>36),
            'totl'  => array('justification'=>'right','width'=>40),
        )
    )
);

}



elseif($ware=='KARACHI')
{
$pdf->addText(20,725,12,"<b>Warehouse = </b>    $ware");
$pdf->addText(20,710,12,"<b>From = </b>             $fdate <b> To: </b> $tdate ");
//$pdf->addText(410,690,11,"<b>WAREHOUSES</b>");
$pdf->ezSetDy(-15);

$count=0;
$one=1;

$nResult = MySQLi_Query($link, "
    SELECT Category, Name, SKU, C_Qty, API, Size, SUM(K_Sin - K_Sout) AS kstk 
    FROM stock 
    GROUP BY Name 
    ORDER BY Article
");

$count = 0;
$totalCTN = 0;
$totalLTR = 0;
$data = [];

while($rstRow = mysqli_fetch_array($nResult))
{
    $skun = $rstRow['SKU']."L";
    $ltr = $rstRow['SKU'] * $rstRow['C_Qty'] * $rstRow['kstk'];

    $count++;
    $totalCTN += $rstRow['kstk'];
    $totalLTR += $ltr;

    $data[] = array(
        'num'=>$count,
        'name'=>$rstRow['Name'],
        'sku'=>$skun,
        'size'=>$rstRow['Size'],
        'api'=>$rstRow['API'],
        'kstk'=>number_format($rstRow['kstk'], 2),
        'ltr'=>number_format($ltr, 2)
    );
}

// Add grand total row
$data[] = array(
    'num'=>'',
    'name'=>'<b>Grand Total</b>',
    'sku'=>'',
    'size'=>'',
    'api'=>'',
    'kstk'=>'<b>'.number_format($totalCTN, 2).'</b>',
    'ltr'=>'<b>'.number_format($totalLTR, 2).'</b>'
);

$cols = array(
    'num'=>'<b>S. No</b>',
    'name'=>'<b>Product Full Name</b>',
    'sku'=>'<b>SKU</b>',
    'size'=>'<b>Size</b>',
    'api'=>'<b>API</b>',
    'kstk'=>'<b>Stock (CTN)</b>',
    'ltr'=>'<b>Stock (LTR)</b>'
);

$pdf->ezTable($data, $cols, '',
    array(
        'xPos'=>20,
        'xOrientation'=>'right',
        'fontSize'=>9,
        'width'=>680,
        'showHeadings'=>1,
        'shaded'=>0,
        'showLines'=>2,
        'cols'=>array(
            'num'=>array('justification'=>'center','width'=>45),
            'name'=>array('justification'=>'left','width'=>210),
            'sku'=>array('justification'=>'center','width'=>50),
            'size'=>array('justification'=>'center','width'=>60),
            'api'=>array('justification'=>'center','width'=>60),
            'kstk'=>array('justification'=>'right','width'=>70),
            'ltr'=>array('justification'=>'right','width'=>70),
        )
    )
);


}





elseif($ware=='LAHORE')
{
$pdf->addText(20,725,12,"<b>Warehouse = </b>    $ware");
$pdf->addText(20,710,12,"<b>From = </b>             $fdate <b> To: </b> $tdate ");
//$pdf->addText(410,690,11,"<b>WAREHOUSES</b>");
$pdf->ezSetDy(-15);

$count=0;
$one=1;

$nResult = MySQLi_Query($link, "
    SELECT Category, Name, SKU, C_Qty, API, Size, SUM(L_Sin - L_Sout) AS kstk 
    FROM stock 
    GROUP BY Name 
    ORDER BY Article
");

$count = 0;
$totalCTN = 0;
$totalLTR = 0;
$data = [];

while($rstRow = mysqli_fetch_array($nResult))
{
     $skun = $rstRow['SKU']."L";
    $ltr = $rstRow['SKU'] * $rstRow['C_Qty'] * $rstRow['kstk'];

    $count++;
    $totalCTN += $rstRow['kstk'];
    $totalLTR += $ltr;

    $data[] = array(
        'num'=>$count,
        'name'=>$rstRow['Name'],
        'sku'=>$skun,
        'size'=>$rstRow['Size'],
        'api'=>$rstRow['API'],
        'kstk'=>number_format($rstRow['kstk'], 2),
        'ltr'=>number_format($ltr, 2)
    );
}

// Add grand total row
$data[] = array(
    'num'=>'',
    'name'=>'<b>Grand Total</b>',
    'sku'=>'',
    'size'=>'',
    'api'=>'',
    'kstk'=>'<b>'.number_format($totalCTN, 2).'</b>',
    'ltr'=>'<b>'.number_format($totalLTR, 2).'</b>'
);

$cols = array(
    'num'=>'<b>S. No</b>',
    'name'=>'<b>Product Full Name</b>',
    'sku'=>'<b>SKU</b>',
    'size'=>'<b>Size</b>',
    'api'=>'<b>API</b>',
    'kstk'=>'<b>Stock (CTN)</b>',
    'ltr'=>'<b>Stock (LTR)</b>'
);

$pdf->ezTable($data, $cols, '',
    array(
        'xPos'=>20,
        'xOrientation'=>'right',
        'fontSize'=>9,
        'width'=>680,
        'showHeadings'=>1,
        'shaded'=>0,
        'showLines'=>2,
        'cols'=>array(
            'num'=>array('justification'=>'center','width'=>45),
            'name'=>array('justification'=>'left','width'=>210),
            'sku'=>array('justification'=>'center','width'=>50),
            'size'=>array('justification'=>'center','width'=>60),
            'api'=>array('justification'=>'center','width'=>60),
            'kstk'=>array('justification'=>'right','width'=>70),
            'ltr'=>array('justification'=>'right','width'=>70),
        )
    )
);

}




elseif($ware=='QUETTA')
{
$pdf->addText(20,725,12,"<b>Warehouse = </b>    $ware");
$pdf->addText(20,710,12,"<b>From = </b>             $fdate <b> To: </b> $tdate ");
//$pdf->addText(410,690,11,"<b>WAREHOUSES</b>");
$pdf->ezSetDy(-15);

$count=0;
$one=1;

$nResult = MySQLi_Query($link, "
    SELECT Category, Name, SKU, C_Qty, API, Size, SUM(Q_Sin - Q_Sout) AS kstk 
    FROM stock 
    GROUP BY Name 
    ORDER BY Article
");

$count = 0;
$totalCTN = 0;
$totalLTR = 0;
$data = [];

while($rstRow = mysqli_fetch_array($nResult))
{
     $skun = $rstRow['SKU']."L";
    $ltr = $rstRow['SKU'] * $rstRow['C_Qty'] * $rstRow['kstk'];

    $count++;
    $totalCTN += $rstRow['kstk'];
    $totalLTR += $ltr;

    $data[] = array(
        'num'=>$count,
        'name'=>$rstRow['Name'],
        'sku'=>$skun,
        'size'=>$rstRow['Size'],
        'api'=>$rstRow['API'],
        'kstk'=>number_format($rstRow['kstk'], 2),
        'ltr'=>number_format($ltr, 2)
    );
}

// Add grand total row
$data[] = array(
    'num'=>'',
    'name'=>'<b>Grand Total</b>',
    'sku'=>'',
    'size'=>'',
    'api'=>'',
    'kstk'=>'<b>'.number_format($totalCTN, 2).'</b>',
    'ltr'=>'<b>'.number_format($totalLTR, 2).'</b>'
);

$cols = array(
    'num'=>'<b>S. No</b>',
    'name'=>'<b>Product Full Name</b>',
    'sku'=>'<b>SKU</b>',
    'size'=>'<b>Size</b>',
    'api'=>'<b>API</b>',
    'kstk'=>'<b>Stock (CTN)</b>',
    'ltr'=>'<b>Stock (LTR)</b>'
);

$pdf->ezTable($data, $cols, '',
    array(
        'xPos'=>20,
        'xOrientation'=>'right',
        'fontSize'=>9,
        'width'=>680,
        'showHeadings'=>1,
        'shaded'=>0,
        'showLines'=>2,
        'cols'=>array(
            'num'=>array('justification'=>'center','width'=>45),
            'name'=>array('justification'=>'left','width'=>210),
            'sku'=>array('justification'=>'center','width'=>50),
            'size'=>array('justification'=>'center','width'=>60),
            'api'=>array('justification'=>'center','width'=>60),
            'kstk'=>array('justification'=>'right','width'=>70),
            'ltr'=>array('justification'=>'right','width'=>70),
        )
    )
);


}


ob_clean();
$pdf->ezStream();
?>



