<?php
ob_start();
require('session.php');
require('pdf-generate/fpdf.php');
extract($_REQUEST);


$financial_id = $_REQUEST['financial_id'];
$customer_id = $_REQUEST['sel_customer'];
$company_id = $_REQUEST['sel_company'];


class ConductPDF extends FPDF {
    
    function Footer()
    {
        // Position at 1.5 cm from bottom
        $this->SetY(-15);
        // Arial italic 8
        $this->SetFont('Arial','B',8);
        // Page number
        $this->Cell(0,10,'Page '.$this->PageNo(),0,0,'C');
    }

    function vcell($c_width,$c_height,$x_axis,$text){
        $w_w=$c_height/3;
        $w_w_1=$w_w+2;
        $w_w1=$w_w+$w_w+$w_w+3;
        $len=strlen($text);// check the length of the cell and splits the text into 7 character each and saves in a array 
        
        $lengthToSplit = 7;
        if($len>$lengthToSplit){
        $w_text=str_split($text,$lengthToSplit);
        $this->SetX($x_axis);
        $this->Cell($c_width,$w_w_1,$w_text[0],'','','');
        if(isset($w_text[1])) {
            $this->SetX($x_axis);
            $this->Cell($c_width,$w_w1,$w_text[1],'','','');
        }
        $this->SetX($x_axis);
        $this->Cell($c_width,$c_height,'','LTRB',0,'L',0);
        }
        else{
            $this->SetX($x_axis);
            $this->Cell($c_width,$c_height,$text,'LTRB',0,'L',0);}
            }
    // Add a function to generate the table
    function GenerateTable($header, $data) {
        // Column widths
        $w = [50, 70, 70];
        // Header
        $this->SetFont('Arial', 'B', 14);
        $this->SetFillColor(43,61,81); // Fill color
        // $this->Rect($this->GetX(), $this->GetY(), array_sum($w), 10, 'F');
        $this->SetTextColor(255); // Text color
        $this->SetDrawColor(255,255, 255);
        // $pdf->SetTextColor(41,61,81);
        for ($i = 0; $i < count($header); $i++) {
            $this->Cell($w[$i], 10, $header[$i], 1, 0, 'C', 1);
        }
        $this->SetTextColor(0);
        $this->SetFillColor(255);
        $this->SetDrawColor(0);
        $this->Ln();
        // Data
        $this->SetFont('Arial', 'B', 10);
        foreach ($data as $row) {
            $this->Cell($w[0], 10, $row['month'], 1, 0, 'C');
            $this->Cell($w[1], 10, $row['sales'], 1, 0, 'C');
            $this->Cell($w[2], 10, $row['return'], 1, 0, 'C');
            $this->Ln();
        }
    }
    function GenerateTable_customer($header, $data) {
        // Column widths
        $w = [30, 70, 70];
        // Header
        $this->SetFont('Arial', 'B', 14);
        for ($i = 0; $i < count($header); $i++) {
            $this->Cell($w[$i], 10, $header[$i], 1, 0, 'C');
        }
        $this->Ln();
        // Data
        $this->SetFont('Arial', 'B', 10);
        foreach ($data as $row) {
            $this->Cell($w[0], 10, $row['Customer'], 1, 0, 'C');
            $this->Cell($w[1], 10, $row['Credit'], 1, 0, 'C');
            $this->Cell($w[2], 10, $row['Debit'], 1, 0, 'C');
            $this->Ln();
        }
    }
}



$pdf = new ConductPDF();
$pdf->AddPage();
$pdf->SetFont('Arial', 'B', 12);
if (isset($_REQUEST['financial_id']) || isset($_REQUEST['sel_customer']) || isset($_REQUEST['sel_company'])) 
{
    $customer_id = $_REQUEST['sel_customer'];
    $financial_id = $_REQUEST['financial_id'];
    $company_id = $_REQUEST['sel_company'];
    $q = "1";
// echo  $customer_id;

    if ($_REQUEST['sel_customer']) {
        $q .= " AND cus_id='" . $customer_id . "'";
    }
    if ($_REQUEST['financial_id']) {
        $q .= " AND financial_id='" . $financial_id . "'";
    }
    if ($_REQUEST['sel_company']) {
        $q .= " AND company_id='" . $company_id . "'";
    }

    $customerRow = $db->get_rsltset("SELECT cus_id,cus_address, cus_name,cus_city,cus_state,cus_gstno,cus_postcode FROM m_customer WHERE cus_id='" . $customer_id . "'");

    $companyRow = $db->get_rsltset("SELECT company_name,c_address,c_city,c_state,c_country,c_postcode,c_mobile,gst_no FROM m_company WHERE company_id='" . $company_id . "'");

    $company_name = $_REQUEST['sel_company'] ? $companyRow[0]['company_name'] : 'All Company' ;
	$c_address =  $companyRow[0]['c_address']  ;
	$c_city = $companyRow[0]['c_city'];
	$c_state = $companyRow[0]['c_state'];
	$c_country = $companyRow[0]['c_country'];
	$c_postcode = $companyRow[0]['c_postcode'];
	$c_mobile = $companyRow[0]['c_mobile'];
	$gst_no = $companyRow[0]['gst_no'];

    $customer_name = $_REQUEST['sel_customer'] ? $customerRow[0]['cus_name'] : 'All Customers';
    $cus_address = $customerRow[0]['cus_address'];
    $cus_city = $customerRow[0]['cus_city'];
    $cus_state = $customerRow[0]['cus_state'];
    $cus_country = $customerRow[0]['cus_country'];
    $cus_postcode = $customerRow[0]['cus_postcode'];
    $cus_gstno = $customerRow[0]['cus_gstno'];

    $financial_query = "SELECT f_end_date, f_start_date FROM m_financial_year WHERE financial_id='" . $financial_id . "'";
    $financial_date = $db->get_rsltset($financial_query);
    $from_date = $financial_date[0]['f_start_date'];
    $to_date = $financial_date[0]['f_end_date'];
    $from_year = date("Y", strtotime($from_date));
    $to_year = date("Y", strtotime($to_date));

    $transactions = $db->get_rsltset("SELECT * FROM transaction_details WHERE $q AND IsActive=1 AND date BETWEEN '" . $from_date . "' AND '" . $to_date . "' ORDER BY (SELECT cus_name from m_customer WHERE cus_id=transaction_details.cus_id) ASC ");


    $return_query = $db->get_rsltset("SELECT MONTH(return_date) AS month, YEAR(return_date) AS year, SUM(final_sale_amt) AS total_return, COUNT(*) AS total_return_no 
        FROM t_return_entry_main
        WHERE  $q
        GROUP BY YEAR(return_date), MONTH(return_date) ORDER BY year, month;");
    $sales_query = $db->get_rsltset("SELECT MONTH(s_bill_date) AS month, YEAR(s_bill_date) AS year, SUM(final_sale_amt) AS total_sales, COUNT(*) AS total_sales_no
        FROM t_sales_entry_main
        WHERE  $q
        GROUP BY YEAR(s_bill_date), MONTH(s_bill_date)
        ORDER BY year, month");

// print_r("SELECT * FROM transaction_details WHERE $q AND IsActive=1 AND date BETWEEN '" . $from_date . "' AND '" . $to_date . "' ORDER BY (SELECT cus_name from m_customer WHERE cus_id=transaction_details.cus_id) ASC ");

$pending = $db->get_rsltset("SELECT (SELECT COUNT(*) FROM transaction_details  WHERE  $q  AND particulars = 'Sales') - 
(SELECT COUNT(*) FROM transaction_details WHERE  $q AND particulars = 'Sales' AND payment_status = '2') AS pending_count");

$pendingCount = $pending[0]['pending_count'];

foreach ($transactions as $transaction) {
    $totalDebit += floatval($transaction['debit']);
    $totalCredit += floatval($transaction['credit']);
}
$closingBalance =  $totalDebit - $totalCredit ;

if($closingBalance > 0){
    $closingBalance = number_format(abs($closingBalance), 2).' Dr';
}
else if ($closingBalance < 0){
    $closingBalance = number_format(abs($closingBalance), 2).' Cr';
}
// $closingBalance = (abs($closingBalance));


$pdf->SetFont('Arial', 'B', 12);
$pdf->Cell(50, 8, $company_name, 0, 0, 'L');
$pdf->Cell(90,8,'',0);
$pdf->Cell(50, 8, $customer_name, 0, 0, 'R');

$pdf->Ln();

$pdf->SetFont('Arial', 'B', 8);
$pdf->Cell(50, 8, $c_address, 0, 0, 'L');
$pdf->Cell(90,6,'',0);
$pdf->Cell(50, 6, $cus_address, 0, 0, 'R');

$pdf->Ln();

$pdf->SetFont('Arial', 'B', 8);
$pdf->Cell(50, 6, $c_city, 0, 0, 'L');
$pdf->Cell(90,6,'',0);
$pdf->Cell(50, 6, $cus_city, 0, 0, 'R');
$pdf->Ln();

$pdf->SetFont('Arial', 'B', 8);
$pdf->Cell(50, 6, $c_state.' - '.$c_postcode, 0, 0, 'L');
$pdf->Cell(90,6,'',0);
$pdf->Cell(50, 6, $cus_state.' - '.$cus_postcode, 0, 0, 'R');
$pdf->Ln();

$pdf->SetFont('Arial', 'B', 9);
$pdf->Cell(50, 6, $gst_no, 0, 0, 'L');
$pdf->Cell(90,6,'',0);
$pdf->Cell(50, 6, $cus_gstno, 0, 0, 'R');
$pdf->Ln(10);



$pdf->Line(10, 45, 200, 45);

    $pdf->Cell(25,10,'Financial-Year',0);
    $pdf->Cell(30, 10,$from_year . ' to ' . $to_year, 0, 0, 'L'); 
    $pdf->Cell(40, 10, 'Outstanding Balance:', 0, 0, 'L'); 
    $pdf->Cell(35, 10, $closingBalance, 0, 0, 'L'); 

    $pdf->Ln();
    $pdf->Line(10, 55, 200, 55);
    $pdf->Ln(5);

    $pdf->SetFont('Arial', 'B', 12);
    $pdf->SetFillColor(43,61,81);
        $pdf->SetTextColor(255, 255, 255);
        $pdf->SetDrawColor(255,255, 255);

    $pdf->Cell(12,10, 'S.no',1,0,'C',1);
    $pdf->Cell(100, 10, 'Customer Name', 1, 0, 'C',1);
    $pdf->Cell(26, 10, 'Debit (Dr)', 1, 0, 'C',1);
    $pdf->Cell(26, 10, 'Credit (Cr)', 1, 0, 'C',1);
    $pdf->Cell(26, 10, 'Balance', 1, 0, 'C',1);

    $pdf->SetFillColor(0);
    $pdf->SetTextColor(0);
    $pdf->SetDrawColor(0);
    
    $customer_totals = []; // Create an associative array to store customer totals

    foreach ($transactions as $transaction) {
        $customer = $transaction['cus_id'];
        $totalCredit = floatval($transaction['credit']);
        $totalDebit = floatval($transaction['debit']);
        
        // Check if the customer ID already exists in the array
        if (isset($customer_totals[$customer])) {
            // Add the credit and debit to the existing totals
            $customer_totals[$customer]['credit'] += $totalCredit;
            $customer_totals[$customer]['debit'] += $totalDebit;
        } else {
            // If the customer ID doesn't exist, initialize the totals
            $customer_totals[$customer] = [
                'credit' => $totalCredit,
                'debit' => $totalDebit
            ];
        }
    $credit+=$totalCredit;
    $debit+=$totalDebit;
$totalbalance = $credit - $debit;

       
    }
    $count= 0;
    // To display the final totals outside the loop
    foreach ($customer_totals as $customer_id => $totals) {
$count++;
        $customerRow = $db->get_rsltset("SELECT cus_id, cus_name, cus_city, cus_state, cus_gstno, cus_postcode FROM m_customer WHERE cus_id='" . $customer_id ."' ");
        $customer_name =  $customerRow[0]['cus_name'];
$balance = $totals['credit'] - $totals['debit'];

        $pdf->Ln();

        $pdf->SetFont('Arial', 'B', 10);
        $pdf->Cell(12, 8, $count, 1, 0, 'L');
        $pdf->Cell(100, 8, $customer_name, 1, 0, 'L');
        $pdf->Cell(26, 8, $totals['debit'], 1, 0, 'R');
        $pdf->Cell(26, 8, $totals['credit'], 1, 0, 'R');
        $pdf->Cell(26, 8, $balance, 1, 0, 'R');
    }
   
    $pdf->Ln();
$totalDebit += $totals['debit'];
$totalCredit += $totals['credit'];

$pdf->SetFont('Arial', 'B', 10);
$pdf->Cell(112, 10, 'Total:', 1);
$pdf->Cell(26, 10, number_format($debit, 2), 1,0, 'R');
$pdf->Cell(26, 10, number_format($credit, 2), 1,0, 'R');
$pdf->Cell(26, 10, number_format($totalbalance, 2), 1,0, 'R');
$pdf->Ln();
$pdf->Output('Sales statics.pdf', 'I');
}
else {
    $pdf = new ConductPDF();
    $pdf->AddPage();
    $pdf->Output('Salesstatics.pdf', 'd');
    $pdf->Close();
}





