<?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/logo2.jpg',50,780,52);

$date1=Date("j-n-Y");
//$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(265,780,13,"<b>(GLAKB)</b>");
$pdf->ezSetDy(-100);

$pdf->addText(15, 771, 9, "Sabzal Road Block No 3 STN Colony,");
$pdf->addText(15, 758, 9, "Near Spini Road, Quetta.");
$pdf->addText(15, 745, 9, "<b>Contact @ </b> +92 81 2873538 ");
$pdf->addText(15,732,9,"<b>S.T.No:</b>         7540020");



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

$sman=$_POST['sman']; 
$province=$_POST['prov'];
$city=$_POST['city'];
$azero=$_POST['azero'];



if ($strFrom=='')
{
$nResult1 = MySQLi_Query($link, "select MIN(D_Date) as dte from ledgers ");
while($rstRow1 = mysqli_fetch_array($nResult1))
{
$strFrom=$rstRow1['dte'];
}
}

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

//$date1 = new DateTime("2023-01-01");
//$date2 = new DateTime(); // current date
//$interval = $date1->diff($date2);
//$days = $interval->days;


$result = mysqli_query($link, "delete from temp_cust ");



$result = MySQLi_Query($link, "select ledgers.Code, sum(ledgers.Debit) as deb, sum(ledgers.Credit) as cre, sum(ledgers.Debit-ledgers.Credit) as tot, acc_heads.Province, acc_heads.City, acc_heads.Salesman from ledgers, acc_heads where ledgers.Code=acc_heads.Name and ledgers.M_Head='Customer' and ledgers.D_Date between '$strFrom' and '$strTo'  group by ledgers.Code order by ledgers.Code");
while($rstRow = mysqli_fetch_array($result))
{
InsertRec("temp_cust",array(
"Province" =>$rstRow['Province'],
"City" =>$rstRow['City'],
"Salesman" =>$rstRow['Salesman'],
"Code" =>$rstRow['Code'],
"Debit" =>$rstRow['deb'],
"Credit" =>$rstRow['cre'],
"Balance" =>$rstRow['tot'],
"Status" =>$rstRow['Active'],
"addby" =>$usr,
));
}

$pdf->addText(210,755,12,"<b>(CUSTOMER STATUS REPORT)</b>");



if(!$sman && !$province && !$city)
{
$pdf->addText(20,700,11,"<b>Nation-Wise Overall Customers.</b>");

$pdf->addText(220,740,11,"<b>From </b> $fdate <b> To </b> $tdate");

$count=0;
$one=1;
$deb=0;
$cr=0;
$nbal=0;
$pbal=0;
$tnbal = 0;

if($azero=="yes")
{
$nResult = MySQLi_Query($link, "SELECT * FROM temp_cust having Balance!= 0 ORDER BY Salesman, Code ");
}
else
{
$nResult = MySQLi_Query($link, "SELECT * FROM temp_cust ORDER BY Salesman, Code ");
}

$currentSalesman = '';
$data = [];
$grandBal = 0;
$grandSales = 0;
$grandRecovery = 0;
$salesmanBal = 0;
$salesmanSales = 0;
$salesmanRecovery = 0;
$count = 0;

while ($rstRow = mysqli_fetch_array($nResult)) {
    $salesman = $rstRow['Salesman'];
    $deb = $rstRow['Debit'];
    $cr = $rstRow['Credit'];
    $nbal = $rstRow['Balance'];

    // Check for group change (new salesman)
    if ($currentSalesman != '' && $salesman != $currentSalesman) {
        // Add subtotal row for previous salesman
        $data[] = array(
            'num' => '',
            'name' => "<b>Subtotal for $currentSalesman</b>",
         
            'db' => "<b>" . number_format($salesmanSales, 2) . "</b>",
            'cr' => "<b>" . number_format($salesmanRecovery, 2) . "</b>",
            'bal' => "<b>" . number_format($salesmanBal, 2) . "</b>",
            'shade' => 1
        );

     
        $pdf->ezTable(
            $data, 
            array(
                'num'=>'<b>S.No</b>',
                'name'=>'<b>Customer Name</b>',
              
                'db'=>'<b>Cr - Sales</b>',
                'cr'=>'<b>Cr - Recovery</b>',
                'bal'=>'<b>Net Balance</b>'
            ), 
            "<b>Salesman: $currentSalesman</b>",
            array(
                'xPos'=>20,
                'xOrientation'=>'right',
                'fontSize'=>10,
                'width'=>680,
                'showHeadings'=>1,
                'shaded'=>0,
                'showLines'=>1,
                'shadeCol'=>array(0.9,0.9,0.9),
                  'titleJustification'=>'left',   // 👈 added this line
                'cols'=>array(
                    'num'=>array('justification'=>'center','width'=>45),
                    'name'=>array('justification'=>'left','width'=>250),
                  
                    'db'=>array('justification'=>'right','width'=>85),
                    'cr'=>array('justification'=>'right','width'=>85),
                    'bal'=>array('justification'=>'right','width'=>85),
                )
            )
        );

        // Reset data and subtotals for next salesman
        $data = [];
        $salesmanBal = 0;
        $salesmanSales = 0;
        $salesmanRecovery = 0;
    }

    $currentSalesman = $salesman;
    $count++;

    $debFmt = number_format($deb, 2);
    $crFmt = number_format($cr, 2);
    $balFmt = number_format($nbal, 2);
    $pbal = '0.00';

    $data[] = array(
        'num' => $count,
        'name' => $rstRow['Code'],
      
        'db' => $debFmt,
        'cr' => $crFmt,
        'bal' => $balFmt
    );

    // Add to totals
    $salesmanSales += $deb;
    $salesmanRecovery += $cr;
    $salesmanBal += $nbal;

    $grandSales += $deb;
    $grandRecovery += $cr;
    $grandBal += $nbal;
}

// Print last salesman group if any
if (!empty($data)) {
    // Add last salesman subtotal
    $data[] = array(
        'num' => '',
        'name' => "<b>Subtotal for $currentSalesman</b>",
       
        'db' => "<b>" . number_format($salesmanSales, 2) . "</b>",
        'cr' => "<b>" . number_format($salesmanRecovery, 2) . "</b>",
        'bal' => "<b>" . number_format($salesmanBal, 2) . "</b>",
        'shade' => 1
    );

    // Add grand total row
    $data[] = array(
        'num' => '',
        'name' => "<b>Grand Total</b>",
     
        'db' => "<b>" . number_format($grandSales, 2) . "</b>",
        'cr' => "<b>" . number_format($grandRecovery, 2) . "</b>",
        'bal' => "<b>" . number_format($grandBal, 2) . "</b>",
        'shade' => 1
    );


    $pdf->ezTable(
        $data, 
        array(
            'num'=>'<b>S.No</b>',
            'name'=>'<b>Customer Name</b>',
         
            'db'=>'<b>Cr - Sales</b>',
            'cr'=>'<b>Cr - Recovery</b>',
            'bal'=>'<b>Net Balance</b>'
        ), 
          "<b>Salesman: $currentSalesman</b>",
        array(
            'xPos'=>20,
            'xOrientation'=>'right',
            'fontSize'=>10,
            'width'=>680,
            'showHeadings'=>1,
            'shaded'=>0,
            'showLines'=>1,
            'shadeCol'=>array(0.9,0.9,0.9),

            'cols'=>array(
                'num'=>array('justification'=>'center','width'=>45),
                'name'=>array('justification'=>'left','width'=>250),
              
                'db'=>array('justification'=>'right','width'=>85),
                'cr'=>array('justification'=>'right','width'=>85),
                'bal'=>array('justification'=>'right','width'=>85),
            )
        )
    );
}


}

elseif(!empty($province))
{
$pdf->addText(220,740,11,"<b>From </b> $fdate <b> To </b> $tdate");
$pdf->addText(20,700,11,"<b>Province = </b> $province");

$count=0;
$one=1;
$deb=0;
$cr=0;
$nbal=0;
$pbal=0;
$tnbal = 0;


if($azero=="yes")
{
$nResult = MySQLi_Query($link, "SELECT * FROM temp_cust where Province='$province' having Balance!= 0 ORDER BY Salesman, Code");

}
else
{
$nResult = MySQLi_Query($link, "SELECT * FROM temp_cust where Province='$province' ORDER BY Salesman, Code");
}


$currentSalesman = '';
$data = [];
$grandBal = 0;
$grandSales = 0;
$grandRecovery = 0;
$salesmanBal = 0;
$salesmanSales = 0;
$salesmanRecovery = 0;
$count = 0;

while ($rstRow = mysqli_fetch_array($nResult)) {
    $salesman = $rstRow['Salesman'];
    $deb = $rstRow['Debit'];
    $cr = $rstRow['Credit'];
    $nbal = $rstRow['Balance'];

    // Check for group change (new salesman)
    if ($currentSalesman != '' && $salesman != $currentSalesman) {
        // Add subtotal row for previous salesman
        $data[] = array(
            'num' => '',
            'name' => "<b>Subtotal for $currentSalesman</b>",
            'pbal' => '',
            'db' => "<b>" . number_format($salesmanSales, 2) . "</b>",
            'cr' => "<b>" . number_format($salesmanRecovery, 2) . "</b>",
            'bal' => "<b>" . number_format($salesmanBal, 2) . "</b>",
            'shade' => 1
        );

        // Print table for that salesman
        $pdf->ezTable(
            $data, 
            array(
                'num'=>'<b>S.No</b>',
                'name'=>'<b>Customer Name</b>',
                'pbal'=>'<b>Previous Dues</b>',
                'db'=>'<b>Cr - Sales</b>',
                'cr'=>'<b>Cr - Recovery</b>',
                'bal'=>'<b>Net Balance</b>'
            ), 
            "<b>Salesman: $currentSalesman</b>",
            array(
                'xPos'=>20,
                'xOrientation'=>'right',
                'fontSize'=>10,
                'width'=>680,
                'showHeadings'=>1,
                'shaded'=>0,
                'showLines'=>1,
                'shadeCol'=>array(0.9,0.9,0.9),
                'cols'=>array(
                    'num'=>array('justification'=>'center','width'=>38),
                    'name'=>array('justification'=>'left','width'=>230),
                    'pbal'=>array('justification'=>'right','width'=>75),
                    'db'=>array('justification'=>'right','width'=>75),
                    'cr'=>array('justification'=>'right','width'=>75),
                    'bal'=>array('justification'=>'right','width'=>75),
                )
            )
        );

        // Reset data and subtotals for next salesman
        $data = [];
        $salesmanBal = 0;
        $salesmanSales = 0;
        $salesmanRecovery = 0;
    }

    $currentSalesman = $salesman;
    $count++;

    $debFmt = number_format($deb, 2);
    $crFmt = number_format($cr, 2);
    $balFmt = number_format($nbal, 2);
    $pbal = '0.00';

    $data[] = array(
        'num' => $count,
        'name' => $rstRow['Code'],
        'pbal' => $pbal,
        'db' => $debFmt,
        'cr' => $crFmt,
        'bal' => $balFmt
    );

    // Add to totals
    $salesmanSales += $deb;
    $salesmanRecovery += $cr;
    $salesmanBal += $nbal;

    $grandSales += $deb;
    $grandRecovery += $cr;
    $grandBal += $nbal;
}

// Print last salesman group if any
if (!empty($data)) {
    // Add last salesman subtotal
    $data[] = array(
        'num' => '',
        'name' => "<b>Grand Total of $currentSalesman</b>",
        'pbal' => '',
        'db' => "<b>" . number_format($salesmanSales, 2) . "</b>",
        'cr' => "<b>" . number_format($salesmanRecovery, 2) . "</b>",
        'bal' => "<b>" . number_format($salesmanBal, 2) . "</b>",
        'shade' => 1
    );

    

    // Final table for last salesman (with grand total)
    $pdf->ezTable(
        $data, 
        array(
            'num'=>'<b>S.No</b>',
            'name'=>'<b>Customer Name</b>',
            'pbal'=>'<b>Previous Dues</b>',
            'db'=>'<b>Cr - Sales</b>',
            'cr'=>'<b>Cr - Recovery</b>',
            'bal'=>'<b>Net Balance</b>'
        ), 
        "<b>Salesman: $currentSalesman</b>",
        array(
            'xPos'=>20,
            'xOrientation'=>'right',
            'fontSize'=>10,
            'width'=>680,
            'showHeadings'=>1,
            'shaded'=>0,
            'showLines'=>1,
            'shadeCol'=>array(0.9,0.9,0.9),
            'cols'=>array(
                'num'=>array('justification'=>'center','width'=>38),
                'name'=>array('justification'=>'left','width'=>230),
                'pbal'=>array('justification'=>'right','width'=>75),
                'db'=>array('justification'=>'right','width'=>75),
                'cr'=>array('justification'=>'right','width'=>75),
                'bal'=>array('justification'=>'right','width'=>75),
            )
        )
    );
}}



elseif(!empty($sman))
{
$pdf->addText(220,740,11,"<b>From </b> $fdate <b> To </b> $tdate");
$pdf->addText(20,700,11,"<b>Salesman = </b> $sman");

$count=0;
$one=1;
$deb=0;
$cr=0;
$nbal=0;
$pbal=0;
$tnbal = 0;


if($azero=="yes")
{
$nResult = MySQLi_Query($link, "SELECT * FROM temp_cust where Salesman='$sman' having Balance!= 0 ORDER BY Code");

}
else
{
$nResult = MySQLi_Query($link, "SELECT * FROM temp_cust where Salesman='$sman' ORDER BY Code");
}


$currentSalesman = '';
$data = [];
$grandBal = 0;
$grandSales = 0;
$grandRecovery = 0;
$salesmanBal = 0;
$salesmanSales = 0;
$salesmanRecovery = 0;
$count = 0;

while ($rstRow = mysqli_fetch_array($nResult)) {
    $salesman = $rstRow['Salesman'];
    $deb = $rstRow['Debit'];
    $cr = $rstRow['Credit'];
    $nbal = $rstRow['Balance'];

    // Check for group change (new salesman)
    if ($currentSalesman != '' && $salesman != $currentSalesman) {
        // Add subtotal row for previous salesman
        $data[] = array(
            'num' => '',
            'name' => "<b>Subtotal for $currentSalesman</b>",
         
            'db' => "<b>" . number_format($salesmanSales, 2) . "</b>",
            'cr' => "<b>" . number_format($salesmanRecovery, 2) . "</b>",
            'bal' => "<b>" . number_format($salesmanBal, 2) . "</b>",
            'shade' => 1
        );

     
        $pdf->ezTable(
            $data, 
            array(
                'num'=>'<b>S.No</b>',
                'name'=>'<b>Customer Name</b>',
              
                'db'=>'<b>Cr - Sales</b>',
                'cr'=>'<b>Cr - Recovery</b>',
                'bal'=>'<b>Net Balance</b>'
            ), 
            "<b>Salesman: $currentSalesman</b>",
            array(
                'xPos'=>20,
                'xOrientation'=>'right',
                'fontSize'=>10,
                'width'=>680,
                'showHeadings'=>1,
                'shaded'=>0,
                'showLines'=>1,
                'shadeCol'=>array(0.9,0.9,0.9),
                  'titleJustification'=>'left',   // 👈 added this line
                'cols'=>array(
                    'num'=>array('justification'=>'center','width'=>45),
                    'name'=>array('justification'=>'left','width'=>250),
                  
                    'db'=>array('justification'=>'right','width'=>85),
                    'cr'=>array('justification'=>'right','width'=>85),
                    'bal'=>array('justification'=>'right','width'=>85),
                )
            )
        );

        // Reset data and subtotals for next salesman
        $data = [];
        $salesmanBal = 0;
        $salesmanSales = 0;
        $salesmanRecovery = 0;
    }

    $currentSalesman = $salesman;
    $count++;

    $debFmt = number_format($deb, 2);
    $crFmt = number_format($cr, 2);
    $balFmt = number_format($nbal, 2);
    $pbal = '0.00';

    $data[] = array(
        'num' => $count,
        'name' => $rstRow['Code'],
      
        'db' => $debFmt,
        'cr' => $crFmt,
        'bal' => $balFmt
    );

    // Add to totals
    $salesmanSales += $deb;
    $salesmanRecovery += $cr;
    $salesmanBal += $nbal;

    $grandSales += $deb;
    $grandRecovery += $cr;
    $grandBal += $nbal;
}

// Print last salesman group if any
if (!empty($data)) {
    // Add last salesman subtotal
    $data[] = array(
        'num' => '',
        'name' => "<b>Subtotal for $currentSalesman</b>",
       
        'db' => "<b>" . number_format($salesmanSales, 2) . "</b>",
        'cr' => "<b>" . number_format($salesmanRecovery, 2) . "</b>",
        'bal' => "<b>" . number_format($salesmanBal, 2) . "</b>",
        'shade' => 1
    );

    // Add grand total row
    $data[] = array(
        'num' => '',
        'name' => "<b>Grand Total</b>",
     
        'db' => "<b>" . number_format($grandSales, 2) . "</b>",
        'cr' => "<b>" . number_format($grandRecovery, 2) . "</b>",
        'bal' => "<b>" . number_format($grandBal, 2) . "</b>",
        'shade' => 1
    );


    $pdf->ezTable(
        $data, 
        array(
            'num'=>'<b>S.No</b>',
            'name'=>'<b>Customer Name</b>',
         
            'db'=>'<b>Cr - Sales</b>',
            'cr'=>'<b>Cr - Recovery</b>',
            'bal'=>'<b>Net Balance</b>'
        ), 
          "<b>Salesman: $currentSalesman</b>",
        array(
            'xPos'=>20,
            'xOrientation'=>'right',
            'fontSize'=>10,
            'width'=>680,
            'showHeadings'=>1,
            'shaded'=>0,
            'showLines'=>1,
            'shadeCol'=>array(0.9,0.9,0.9),

            'cols'=>array(
                'num'=>array('justification'=>'center','width'=>45),
                'name'=>array('justification'=>'left','width'=>250),
              
                'db'=>array('justification'=>'right','width'=>85),
                'cr'=>array('justification'=>'right','width'=>85),
                'bal'=>array('justification'=>'right','width'=>85),
            )
        )
    );
}



}



elseif(!empty($city))
{
$pdf->addText(220,740,11,"<b>From </b> $fdate <b> To </b> $tdate");
$pdf->addText(20,700,11,"<b>City = </b> $city");

$count=0;
$one=1;
$deb=0;
$cr=0;
$nbal=0;
$pbal=0;
$tnbal = 0;


if($azero=="yes")
{
$nResult = MySQLi_Query($link, "SELECT * FROM temp_cust where City='$city' having Balance!= 0 ORDER BY Salesman, Code");

}
else
{
$nResult = MySQLi_Query($link, "SELECT * FROM temp_cust where City='$city' ORDER BY Salesman, Code");
}

$currentSalesman = '';
$data = [];
$grandBal = 0;
$grandSales = 0;
$grandRecovery = 0;
$salesmanBal = 0;
$salesmanSales = 0;
$salesmanRecovery = 0;
$count = 0;

while ($rstRow = mysqli_fetch_array($nResult)) {
    $salesman = $rstRow['Salesman'];
    $deb = $rstRow['Debit'];
    $cr = $rstRow['Credit'];
    $nbal = $rstRow['Balance'];

    // Check for group change (new salesman)
    if ($currentSalesman != '' && $salesman != $currentSalesman) {
        // Add subtotal row for previous salesman
        $data[] = array(
            'num' => '',
            'name' => "<b>Subtotal for $currentSalesman</b>",
         
            'db' => "<b>" . number_format($salesmanSales, 2) . "</b>",
            'cr' => "<b>" . number_format($salesmanRecovery, 2) . "</b>",
            'bal' => "<b>" . number_format($salesmanBal, 2) . "</b>",
            'shade' => 1
        );

     
        $pdf->ezTable(
            $data, 
            array(
                'num'=>'<b>S.No</b>',
                'name'=>'<b>Customer Name</b>',
              
                'db'=>'<b>Cr - Sales</b>',
                'cr'=>'<b>Cr - Recovery</b>',
                'bal'=>'<b>Net Balance</b>'
            ), 
            "<b>Salesman: $currentSalesman</b>",
            array(
                'xPos'=>20,
                'xOrientation'=>'right',
                'fontSize'=>10,
                'width'=>680,
                'showHeadings'=>1,
                'shaded'=>0,
                'showLines'=>1,
                'shadeCol'=>array(0.9,0.9,0.9),
                  'titleJustification'=>'left',   // 👈 added this line
                'cols'=>array(
                    'num'=>array('justification'=>'center','width'=>45),
                    'name'=>array('justification'=>'left','width'=>250),
                  
                    'db'=>array('justification'=>'right','width'=>85),
                    'cr'=>array('justification'=>'right','width'=>85),
                    'bal'=>array('justification'=>'right','width'=>85),
                )
            )
        );

        // Reset data and subtotals for next salesman
        $data = [];
        $salesmanBal = 0;
        $salesmanSales = 0;
        $salesmanRecovery = 0;
    }

    $currentSalesman = $salesman;
    $count++;

    $debFmt = number_format($deb, 2);
    $crFmt = number_format($cr, 2);
    $balFmt = number_format($nbal, 2);
    $pbal = '0.00';

    $data[] = array(
        'num' => $count,
        'name' => $rstRow['Code'],
      
        'db' => $debFmt,
        'cr' => $crFmt,
        'bal' => $balFmt
    );

    // Add to totals
    $salesmanSales += $deb;
    $salesmanRecovery += $cr;
    $salesmanBal += $nbal;

    $grandSales += $deb;
    $grandRecovery += $cr;
    $grandBal += $nbal;
}

// Print last salesman group if any
if (!empty($data)) {
    // Add last salesman subtotal
    $data[] = array(
        'num' => '',
        'name' => "<b>Subtotal for $currentSalesman</b>",
       
        'db' => "<b>" . number_format($salesmanSales, 2) . "</b>",
        'cr' => "<b>" . number_format($salesmanRecovery, 2) . "</b>",
        'bal' => "<b>" . number_format($salesmanBal, 2) . "</b>",
        'shade' => 1
    );

    // Add grand total row
    $data[] = array(
        'num' => '',
        'name' => "<b>Grand Total</b>",
     
        'db' => "<b>" . number_format($grandSales, 2) . "</b>",
        'cr' => "<b>" . number_format($grandRecovery, 2) . "</b>",
        'bal' => "<b>" . number_format($grandBal, 2) . "</b>",
        'shade' => 1
    );


    $pdf->ezTable(
        $data, 
        array(
            'num'=>'<b>S.No</b>',
            'name'=>'<b>Customer Name</b>',
         
            'db'=>'<b>Cr - Sales</b>',
            'cr'=>'<b>Cr - Recovery</b>',
            'bal'=>'<b>Net Balance</b>'
        ), 
          "<b>Salesman: $currentSalesman</b>",
        array(
            'xPos'=>20,
            'xOrientation'=>'right',
            'fontSize'=>10,
            'width'=>680,
            'showHeadings'=>1,
            'shaded'=>0,
            'showLines'=>1,
            'shadeCol'=>array(0.9,0.9,0.9),

            'cols'=>array(
                'num'=>array('justification'=>'center','width'=>45),
                'name'=>array('justification'=>'left','width'=>250),
              
                'db'=>array('justification'=>'right','width'=>85),
                'cr'=>array('justification'=>'right','width'=>85),
                'bal'=>array('justification'=>'right','width'=>85),
            )
        )
    );
}



}


$pdf->ezSetDy(-40);
$pdf->ezText("<b>Owner GLAKB</b>", 12, array("justification" => 'right'));
ob_clean();
$pdf->ezStream();
?>



