<?php 

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


class PDF_MC_Table extends FPDF
{
    protected $widths;
    protected $aligns;

    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 SetWidths($w)
    {
        // Set the array of column widths
        $this->widths = $w;
    }

    function SetAligns($a)
    {
        // Set the array of column alignments
        $this->aligns = $a;
    }

    function Row($data)
    {
        // Calculate the height of the row
        $nb = 0;
        for($i=0;$i<count($data);$i++)
            $nb = max($nb,$this->NbLines($this->widths[$i],$data[$i]));
        $h = 5*$nb;
        // Issue a page break first if needed
        $this->CheckPageBreak($h);
        // Draw the cells of the row
        for($i=0;$i<count($data);$i++)
        {
            $w = $this->widths[$i];
            $a = isset($this->aligns[$i]) ? $this->aligns[$i] : 'L';
            // Save the current position
            $x = $this->GetX();
            $y = $this->GetY();
            // Draw the border
            $this->Rect($x,$y,$w,$h);
            // Print the text
            $this->MultiCell($w,5,$data[$i],0,$a);
            // Put the position to the right of the cell
            $this->SetXY($x+$w,$y);
        }
        // Go to the next line
        $this->Ln($h);
    }

    function CheckPageBreak($h)
    {
        // If the height h would cause an overflow, add a new page immediately
        if($this->GetY()+$h>$this->PageBreakTrigger)
            $this->AddPage($this->CurOrientation);
    }

    function NbLines($w, $txt)
    {
        // Compute the number of lines a MultiCell of width w will take
        if(!isset($this->CurrentFont))
            $this->Error('No font has been set');
        $cw = $this->CurrentFont['cw'];
        if($w==0)
            $w = $this->w-$this->rMargin-$this->x;
        $wmax = ($w-2*$this->cMargin)*1000/$this->FontSize;
        $s = str_replace("\r",'',(string)$txt);
        $nb = strlen($s);
        if($nb>0 && $s[$nb-1]=="\n")
            $nb--;
        $sep = -1;
        $i = 0;
        $j = 0;
        $l = 0;
        $nl = 1;
        while($i<$nb)
        {
            $c = $s[$i];
            if($c=="\n")
            {
                $i++;
                $sep = -1;
                $j = $i;
                $l = 0;
                $nl++;
                continue;
            }
            if($c==' ')
                $sep = $i;
            $l += $cw[$c];
            if($l>$wmax)
            {
                if($sep==-1)
                {
                    if($i==$j)
                        $i++;
                }
                else
                    $i = $sep+1;
                $sep = -1;
                $j = $i;
                $l = 0;
                $nl++;
            }
            else
                $i++;
        }
        return $nl;
    }
    
}


class ConductPDF extends FPDF {
    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);}
        }
     }

     $pdf = new PDF_MC_Table();
     $pdf->AddPage();
    
    //  $pdf->Output();
     
// $pdf->AddPage();
// $pdf->SetMargins(10, 10, 10);
// $pdf->AutoPageBreak = true;

if(isset($_REQUEST['sel_customer']) && !empty($_REQUEST['sel_customer']) ){
	$customer_id = $_REQUEST['sel_customer'];
	$financial_id = $_REQUEST['financial_id'];
	$company_id = $_REQUEST['sel_company'];
    
	$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 . "'");

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

	$formatted_from_date = date("d-m-Y", strtotime($from_date));
	$formatted_to_date = date("d-m-Y", strtotime($to_date));
    $from_year = date("Y", strtotime($from_date));
    $to_year = date("Y", strtotime($to_date));

    $transactions = $db->get_rsltset("SELECT * FROM transaction_details WHERE company_id='" . $company_id . "' AND cus_id='" . $customer_id . "' AND financial_id='" . $financial_id . "' AND IsActive=1 AND date BETWEEN '" . $from_date . "' AND '" . $to_date . "' ORDER BY date ASC ");
	// print_r($transactions);
	$company_name = $companyRow[0]['company_name'];
	$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 = $customerRow[0]['cus_name'];
    $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'];

	
	//  "<h1> " . $companyRow[0]['company_name'] . "</h1>";
	//  "<h2> " . $customerRow[0]['cus_name'] . "</h2>";
    //  "<h3> " . $formatted_from_date . " to " . $formatted_to_date . "</h3>";

		//  $pdf->SetFont('Arial', 'B', 16);
		//  $pdf->Cell(20, 10,   $company_name, 0, 1);
		 
		// //  $pdf->SetFont('Arial', 'B', 14);
		//  $pdf->Cell(30, 10,   $customer_name, 5, 5);
		//  $pdf->SetFont('Arial', 'B', 12);
		//  $pdf->Cell(20, 10,   $formatted_from_date . ' to ' . $formatted_to_date, 0, 1); 

		//  $pdf->Ln(10);
         $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);

		


         $totalDebit = 0;
         $totalCredit = 0;
         $closingBalance = 0;
         $openingBalance = 0;
         $openingBalanceInDebit = false;

         $lastBillDateQuery = "SELECT MAX(date) AS last_bill_date FROM transaction_details WHERE financial_id = '$financial_id' AND company_id = '$company_id' AND cus_id = '$customer_id'";
$lastBillDateResult = $db->get_rsltset($lastBillDateQuery);
if (!empty($lastBillDateResult) && isset($lastBillDateResult[0]['last_bill_date'])) {
    $closingBalanceDate = date('d-m-Y', strtotime($lastBillDateResult[0]['last_bill_date']));
} else {
    $closingBalanceDate = ''; // Set to empty string or a default date if no bills are found
}


          $prevYear = $db->get_rsltset("SELECT financial_id,f_start_date,f_end_date FROM m_financial_year WHERE f_end_date < '$from_date' ORDER BY f_end_date DESC LIMIT 1");
    //  echo $prevYear;
         if (!empty($prevYear)) {
             $prevYearId = $prevYear[0]['financial_id'];
             $pre_start_date = date('Y', strtotime( $prevYear[0]['f_start_date']));
             $pre_end_date = date('Y', strtotime($prevYear[0]['f_end_date']));

            $closingBalanceQuery = "SELECT 
                    SUM(IFNULL(debit, 0)) - SUM(IFNULL(credit, 0)) AS closing_balance 
                    FROM transaction_details 
                    WHERE financial_id = '$prevYearId' 
                    AND company_id = '$company_id' 
                    AND cus_id = '$customer_id'";
                
             $closingBalanceResult = $db->get_rsltset($closingBalanceQuery);
     
             if (!empty($closingBalanceResult) && isset($closingBalanceResult[0]['closing_balance'])) {
                 $openingBalance = floatval($closingBalanceResult[0]['closing_balance']);
                 $openingBalanceInDebit = $openingBalance >= 0 ? true : false;
             }
         }
//          $bill_numbers = array();
// echo    $payment_status = "SELECT bill_no FROM transaction_details WHERE particulars = 'Sales' AND financial_id = '$financial_id' AND company_id = '$company_id' AND cus_id = '$customer_id' ";
// $payment_status_result = $db->get_rsltset($payment_status);

// foreach ($payment_status_result as $row) {
//     echo  $bill_numbers[] = $row['bill_no'];
//     echo $payment_details = "SELECT bill_no from transaction_details where bill_no = '" . $row['bill_no'] . "' ";
// }
         

        //  $pdf->SetFont('Arial', 'B', 10);
        //  $pdf->Cell(100, 10, 'Opening Balance:', 1);
     
        //  if ($openingBalance < 0) {
        //      // Display in "Credit" column
        //      $pdf->Cell(30, 10, '', 1); 
        //      $pdf->Cell(30, 10, number_format(abs($openingBalance), 2), 1);
        //  } elseif ($openingBalance > 0) {
        //      // Display in "Debit" column
        //      $pdf->Cell(30, 10, number_format(abs($openingBalance), 2), 1);
        //      $pdf->Cell(30, 10, '', 1); 
        //  } else {
        //      // Display as-is (empty cells)
        //      $pdf->Cell(30, 10, '', 1);
        //      $pdf->Cell(30, 10, '', 1);
        //  }
        //  $pdf->Ln();
            $total_balance = $db->get_rsltset("SELECT  * FROM transaction_details  where financial_id = '$financial_id' AND company_id = '$company_id' AND cus_id = '$customer_id'");

            foreach ($total_balance as $row) {
                $current_Debit += $row['debit'];
                $current_Credit += $row['credit'];
            }

        // if (!empty($total_balance) && isset($total_balance[0]['closing_balance']) ) {
        //     $current_closing_bal = floatval($total_balance[0]['closing_balance']);
        //     $Closing_balance_total = $current_closing_bal >= 0 ? true : false;
        // }

        $current_closing_bal  = $current_Credit - $current_Debit;
        if($current_closing_bal > 0){
            $current_balance = number_format(abs($current_closing_bal), 2).' Cr';
        }
        else if ($current_closing_bal < 0){
            $current_balance = number_format(abs($current_closing_bal), 2).' Dr';
        }
        else {
            $current_balance = '0.00';
        }

        if ($openingBalanceInDebit) {
            $opening_balance = number_format(abs($openingBalance), 2).' Dr';
        }
        else {
            $opening_balance = number_format(abs($openingBalance), 2).' Cr';
        }
        $pdf->Ln(1);
        $pdf->SetFont('Arial', 'B', 9);
        // $pdf->Cell(10,8,'',0);
        $pdf->Cell(25,8,'Financial-Year',0);
        $pdf->Cell(40, 8,$from_year . ' to ' . $to_year, 0, 0, 'L'); 
        $pdf->Cell(30, 8, 'Opening Balance:', 0, 0, 'L'); 
        $pdf->Cell(35, 8, $opening_balance, 0, 0, 'L'); 
        $pdf->Cell(30, 8, 'Closing Balance:', 0, 0, 'R'); 
        $pdf->Cell(30, 8, $current_balance, 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->Line(10, 55, 200, 55);
        $pdf->Ln(5);


        // $pdf->SetFont('Arial', 'B', 10);
        // $pdf->Cell(50, 10, 'Total Debit:', 1);
        // $pdf->Cell(20, 10, number_format($Debit, 2), 1);
        // $pdf->Cell(50, 10, 'Total Credit:', 1);
        // $pdf->Cell(20, 10, number_format($Credit, 2), 1);
        // $pdf->Ln();

        $pdf->SetFont('Arial', 'B', 12);
        $pdf->SetFillColor(43,61,81);
        $pdf->SetTextColor(255, 255, 255);
        $pdf->SetDrawColor(255,255, 255);
        $pdf->Cell(22, 10, 'Date', 1, 0, 'C',1);
        $pdf->Cell(15, 10, 'Bill No', 1, 0, 'C',1);
        $pdf->Cell(85, 10, 'Particulars', 1, 0, 'C',1);
        $pdf->Cell(25, 10, 'Debit (Dr)', 1, 0, 'L',1);
        $pdf->Cell(25, 10, 'Credit (Cr)', 1, 0, 'L',1);
        $pdf->Cell(20, 10, 'Status', 1, 0, 'L',1);
       // table
    //    $pdf->SetFont('Arial', 'B', 8);
    $pdf->SetFillColor(0);
    $pdf->SetTextColor(0);
    $pdf->SetDrawColor(0);
$pdf->Ln();
$pdf->SetFont('Arial', 'B', 10);

$pdf->Cell(122, 10, 'Opening Balance as of ' . $formatted_from_date, 1);

if ($openingBalanceInDebit) {
    // Display in "Debit" column
    $pdf->Cell(25, 10, number_format(abs($openingBalance), 2), 1,0, 'R');
    $pdf->Cell(25, 10, '', 1); 
    $pdf->Cell(25, 10, '', 1); 
} else {
    // Display in "Credit" column
    $pdf->Cell(25, 10, '', 1); 
    $pdf->Cell(25, 10, number_format(abs($openingBalance), 2), 1,0, 'R');
    $pdf->Cell(20, 10, '', 1); 
}
$pdf->Ln();

       foreach ($transactions as $transaction) {
        $formattedDate = date('d-m-Y', strtotime($transaction['date']));
        $cellWidth =80;
        $cellHeight =5;
        if ($pdf->GetStringWidth($transaction['particulars']) > $cellWidth) {
            $line = ceil($pdf->GetStringWidth($transaction['particulars']) / ($cellWidth - $errMargin));
        } else {
            $line = 1;
        }

      
    $paymentTypeQuery = "SELECT sp.payment_type 
        FROM status_payment_type sp 
        JOIN transaction_details td ON sp.payment_type_id = td.payment_type 
        WHERE td.id = " . $transaction['id'];

 $paymentTypeResult = $db->get_rsltset($paymentTypeQuery);

//  if ($transaction['particulars'] === 'Payment Received') {
//     $description = 'Payment by';
//     if (!empty($transaction['payment_type'])) {
//         $description .=  " - " . $paymentTypeResult[0]['payment_type'];
//         if (!empty($transaction['ref_no'])) {
//             $description .= " - " . $transaction['ref_no'];
//            }
//            $bill = ''; // Clear the bill since it's not needed in this case
//     }
//    }
//    else if($transaction['particulars'] === 'Sales'||$transaction['particulars'] === ' Return') {
//        $description = $transaction['particulars'];
//        if (!empty($transaction['ref_no'])) {
//            $description .= " - " . $transaction['ref_no'];
//        }
//        $bill = $transaction['bill_no'];
// }

        $description = '';
        $bill = '';
        $status = '';
    if ($transaction['particulars'] === 'Payment Received') {
        $description = 'Payment Received';
        if (!empty($transaction['payment_type'])) {
            $description .=  " - " . $paymentTypeResult[0]['payment_type'] ;
            if (!empty($transaction['ref_no'])) {
                $description .= "-  Ref No: " . $transaction['ref_no'] ." ";
        }
        $description .=  $transaction['bill_no'] ;
        }
    } else {
        $description = $transaction['particulars'];

    }   

    if ($transaction['particulars'] === 'Sales' || $transaction['particulars'] === ' Return') { 
        $bill = $transaction['bill_no'];
    }
    else {
        $bill = '';
    }
    if ($transaction['payment_status'] === '2' && $transaction['particulars'] === 'Sales') {
        $status = 'Cleared';
    }
    else if($transaction['particulars'] === 'Sales' && $transaction['payment_status'] === null ) {
        $status = 'Pending';
        
    }

    $pdf->SetFont('Arial', 'B', 10);
    // Table with 1 row and 6 columns
    // $pdf->SetTextColor(200, 150, 155);

    $pdf->SetWidths(array(22, 15, 85, 25, 25, 20));
    $alignments = array('', 'C', '', 'R', 'R','R'); 
    $pdf->SetAligns($alignments);
    if ($status === 'Pending') {
        $pdf->SetTextColor(200, 0, 0); // Set text color to red for "Pending"
    }
    $pdf->Row(
        array(
            $formattedDate, 
            $bill, 
            $description, 
            $transaction['debit'], 
            $transaction['credit'], 
            $status
        )
    );
    $pdf->SetTextColor(0);


        //    $pdf->Cell(22, ($line * $cellHeight ), $formattedDate, 1);
        //    $pdf->Cell(15,($line * $cellHeight ), $bill, 1, 0, 'C');
        
           
        //    $pdf->Cell(85,($line * $cellHeight ), $description, 1);

        //    $pdf->Cell(20, ($line * $cellHeight ), $transaction['debit'], 1);
           
        //    $pdf->Cell(25, ($line * $cellHeight ), $transaction['credit'], 1);
        //    $pdf->Cell(20, ($line * $cellHeight ), $status, 1);
        //    $pdf->Ln(); // Move to the next row

           $totalDebit += floatval($transaction['debit']);
           $totalCredit += floatval($transaction['credit']);
       }
       $totalDebit += $openingBalanceInDebit ? abs($openingBalance) : 0;
       $totalCredit += $openingBalanceInDebit ? 0 :abs($openingBalance);
       
       $pdf->SetFont('Arial', 'B', 10);
       $pdf->Cell(122, 10, 'Total:', 1);
       $pdf->Cell(25, 10, number_format($totalDebit, 2), 1,0, 'R');
       $pdf->Cell(25, 10, number_format($totalCredit, 2), 1,0, 'R');
       $pdf->Cell(20, 10, '', 1); 
       $pdf->Ln();

       $closingBalance =  $totalDebit - $totalCredit ;
       
       $pdf->Cell(122, 10, 'Closing Balance as of ' . $closingBalanceDate, 1);

       
       if ($closingBalance < 0) {
           // Display in "Credit" column
           $pdf->Cell(25, 10, '', 1); 
           $pdf->Cell(25, 10, number_format(abs($closingBalance), 2), 1,0, 'R');
           $pdf->Cell(20, 10, '', 1); 
           
       } elseif ($closingBalance > 0) {
           // Display in "Debit" column
           $pdf->Cell(25, 10, number_format(abs($closingBalance), 2), 1,0, 'R');
           $pdf->Cell(25, 10, '', 1); 
           $pdf->Cell(20, 10, '', 1); 
           
       } else {
           // Display as-is (empty cells)
           $pdf->Cell(25, 10, '', 1);
           $pdf->Cell(25, 10, '', 1);
           $pdf->Cell(20, 10, '', 1);
           
       }
       $pdf->Ln();
       $pdf->Output('output.pdf', 'I');
$pdf->Close();
}
else {
   
    $pdf = new FPDF();
    $pdf->AddPage();
    $pdf->Output('output.pdf', 'I');
$pdf->Close();

    
    
}
	


	?>
    <!-- <script language="javascript">
window.print();
</script> -->