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

$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();
        }
    }
}


switch ($act)
{
    case 'get_chart':


$pdf = new ConductPDF();
$pdf->AddPage();
$pdf->SetFont('Arial', 'B', 12);
$pdf->Ln();
// $pdf->Cell(22, 10, 'Month', 1, 0, 'C');
// $pdf->Cell(60, 10, 'Sales', 1, 0, 'C');
// $pdf->Cell(85, 10, 'Return', 1, 0, 'C');
// $pdf->Ln();

$months = [
    1 => 'January',
    2 => 'February',
    3 => 'March',
    4 => 'April',
    5 => 'May',
    6 => 'June',
    7 => 'July',
    8 => 'August',
    9 => 'September',
    10 => 'October',
    11 => 'November',
    12 => 'December'
];


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";

    if ($_REQUEST['financial_id']) {
        $q .= " AND financial_id='" . $financial_id . "'";
    }
    if ($_REQUEST['sel_customer']) {
        $q .= " AND cus_id='" . $customer_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 date 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");


$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'];

    $monthly_sales = [];
    $monthly_return = [];
    $totalSalesAmount = 0;
    $totalReturnAmount = 0;

    foreach ($sales_query as $sale) {
        $sales_month = $months[$sale['month'] ];
        $monthly_sales[] = [
            'month' => $sales_month,
            'sales' => $sale['total_sales'],
            'return' => 0,
        ];
        $totalSalesAmount += $sale['total_sales'];
    }

    foreach ($return_query as $return) {
        $return_month = $months[$return['month'] ];
        $found = false;
        foreach ($monthly_sales as &$sales) {
            if ($sales['month'] === $return_month) {
                $sales['return'] = $return['total_return'];
                $found = true;
                break;
            }
        }
        if (!$found) {
            $monthly_return[] = [
                'month' => $return_month,
                'sales' => 0,
                'return' => $return['total_return'],
            ];
        }
        $totalReturnAmount += $return['total_return'];

    }
    $outstanding_balance = $totalSalesAmount - $totalReturnAmount;


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


    // Combine monthly_sales and monthly_return into a single $data array
    $data = array_merge($monthly_sales, $monthly_return);

    // Generate the table in the PDF
    // $pdf->SetFont('Arial', 'B', 12);

    $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, 6, $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->SetFont('Arial', 'B', 9);
        // $pdf->Cell(10,8,'',0);
    // $pdf->Ln(3);
    $pdf->Line(10, 55, 200, 55);


        $pdf->Cell(25,8,'Financial-Year',0);
        $pdf->Cell(30, 8,$from_year . ' to ' . $to_year, 0, 0, 'L'); 
        $pdf->Cell(40, 8, 'Outstanding Balance:', 0, 0, 'L'); 
        $pdf->Cell(35, 8, $closingBalance, 0, 0, 'L'); 
        $pdf->Cell(30, 8, 'Pending Bills:', 0, 0, 'R'); 
        $pdf->Cell(30, 8, $pendingCount, 0, 0, 'L'); 
        // $pdf->Cell(30, 8, 'Total Credit:', 0, 0, 'L'); 
        // $pdf->Cell(20, 8, number_format($Credit, 2).' Cr', 0, 0, 'L'); 
        
        
        $pdf->Ln(10);
    $pdf->Ln();
    $pdf->SetFont('Arial', 'B', 10);

    $pdf->SetTextColor(200, 0, 0);
    $pdf->GenerateTable(['Month', 'Sales', 'Return'], $data);
    $pdf->SetTextColor(0);

    $pdf->Ln();
    $pdf->SetFont('Arial', 'B', 12);
    $pdf->Cell(50, 10, 'Total:', 0,0,'C');
    $pdf->SetFont('Arial', 'B', 10);

    $pdf->Cell(70, 10, number_format($totalSalesAmount, 2), 0,0,'C');
    $pdf->Cell(70, 10, number_format($totalReturnAmount, 2), 0,0,'C');

    $pdf->Ln();
    $pdf->Line($pdf->GetX(), $pdf->GetY() -10, $pdf->GetX() + 190, $pdf->GetY() - 10); // Line above Total
$pdf->Line($pdf->GetX(), $pdf->GetY() + 2, $pdf->GetX() + 190, $pdf->GetY() + 2); // Line below Total


    ob_end_clean();
    $pdf->Output('Monthly sales.pdf', 'D');
    $pdf->Close();
} else {
    $pdf = new ConductPDF();
    $pdf->AddPage();
    $pdf->Output('Monthly sales.pdf', 'D');
    $pdf->Close();
}
break;


////-----------------------------------------   pie chart  -----------------------------------------///////////////////
case 'get_pie':


$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";

    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();
}
break;



}
