<?php
require('session.php');
extract($_REQUEST);
$act=$action;
  $fin = $_SESSION["Financial_id"];
// echo 'hi';
// exit;

switch ($act)
{
    case 'get_pie_chart':
       
    if(isset($_REQUEST['customer_id']) && !empty($_REQUEST['company_id']) ){
        $customer_id = $_REQUEST['customer_id'];
        $financial_id = $_REQUEST['financial_id'];
        $company_id = $_REQUEST['company_id'];
     
            
	$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 . "'");
    
    $companyRow = $db->get_rsltset("SELECT company_name 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));

    $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'];
	$customer_name = $customerRow[0]['cus_name'];
    // echo  $company_name;
    // exit;
$count_bills =  $db->get_rsltset("SELECT s_bill_no FROM t_sales_entry_main WHERE company_id='" . $company_id . "' AND cus_id='" . $customer_id . "' AND financial_id='" . $financial_id . "' AND IsActive=1 ");
// $count_bills = $count['s_bill_no'];
// print_r( $count_bills);
$total_pending = 0;
$total_cleared = 0;

foreach ($count_bills as $count_bill) {
    $bill_no = $count_bill['s_bill_no'];
    $count_sales = $db->get_rsltset("SELECT payment_status_id FROM t_sales_payment WHERE s_bill_no = '" . $bill_no . "' AND IsActive = 1");
    
    foreach ($count_sales as $sale) {
        $payment_status = $sale['payment_status_id'];
        if ($payment_status == '1') {
            $total_pending += 1; 
        }
        if ($payment_status == '2') {
            $total_cleared += 1; 
        }
    }
}
$total_bill = count($count_bills);

// echo "Total Pending Payments: $total_pending";
// echo "Total Cleared Payments: $total_cleared";


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

    $totalDebit;
    $totalCredit;
    $closingBalance = (abs($closingBalance));

    $dataPoints = array( 
        array("y" => $totalCredit,"label" => "credit" ),
        array("y" => $totalDebit,"label" => "debit" ),
        array("y" => $closingBalance,"label" => "closing balance" )
    );

    $dataPoints_1 = array(
        array("y" => $total_bill,"label" => "Total Bills" ),
        array("y" => $total_cleared,"label" => "Bills Cleared" ),
        array("y" => $total_pending,"label" => "Bills Pending" ),

    );

    echo json_encode(array('credit' => $totalCredit, 'debit' => $totalDebit, 'dataPoints' => $dataPoints, 'dataPoints_1'=>$dataPoints_1 ));
}
break;

case 'get_sales_chart':

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

        
        // print_r("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");
        // print_r("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'];


        $total_return = 0;
        $total_sales = 0;
        $return_count = 0; 
        $sale_count = 0;
        $monthly_sales = array();
        $monthly_return = array();

        foreach ($sales_query as $sale) {
            // Format the month and year as "YYYY-MM" for the JSON response
            $sales_month = sprintf('%04d-%02d', $sale['year'], $sale['month']);
            $total_sales = floatval($sale['total_sales'] ?? 0);
            $sales_no = floatval($sale['total_sales_no']);
    
            // Add the data to the associative array
            $monthly_sales[] = array(
                'month' => $sales_month,
                'total_sales' => $total_sales,
            
            );
        $sales_bill += $sales_no;
        $sales_amt += $total_sales;

        }
        
        foreach ($return_query as $return) {
            // Format the month and year as "YYYY-MM" for the JSON response
            $return_month = sprintf('%04d-%02d', $return['year'], $return['month']);
            $total_return = floatval($return['total_return'] );
            $return_no = floatval($return['total_return_no']);

            $monthly_return[] = array(
                'month' => $return_month,
                'total_return' => $total_return,
                'bill' => $return_no

            );
            $return_bill += $return_no;
            $return_amt += $total_return;
        }
    
        $credit_total = 0;
        $debit_total = 0;
        
       foreach ($transactions as $transaction) {
            $totalDebit += floatval($transaction['debit']);
            $totalCredit += floatval($transaction['credit']);
        }
        $closingBalance =  $totalDebit - $totalCredit ;
    
        $totalDebit;
        $totalCredit;
        $closingBalance = (abs($closingBalance));
        
        // Now, $credit_minus_debit contains the difference between total credit and total debit
        
        $credit_monthly_totals = [];
        $debit_monthly_totals = [];
    
        foreach ($transactions_credit as $transaction) {
            $date = date('Y-m', strtotime($transaction['date']));
            $credit = floatval($transaction['credit']);
            // $bill = floatval($transaction['total_sales_no']);


            if (!isset($credit_monthly_totals[$date])) {
                $credit_monthly_totals[$date] = 0;
            }
            $credit_monthly_totals[$date] += $credit;
        }

        foreach ($transactions_debit as $transaction) {
            $date = date('Y-m', strtotime($transaction['date']));
            $debit = floatval($transaction['debit']);
            if (!isset($debit_monthly_totals[$date])) {
                $debit_monthly_totals[$date] = 0;
            }
            $debit_monthly_totals[$date] += $debit;
        }
    // echo $sales;
$balance = $sales_amt - $return_amt;

        $resultData = array(
            'salesData' => $monthly_sales,
            'returnData' => $monthly_return,
            'balance' => $closingBalance,
            'return' => $return_amt == null ? 0 : $return_amt,
            'pending' => $pendingCount,
            'sales' => $sales_amt == null ? 0 : $sales_amt

        );
        echo json_encode($resultData);
        // break;
       
    }
    
   
    break;

    case 'get_sales_outstanding':
       
        if(isset($_REQUEST['customer_id']) || isset($_REQUEST['company_id']) ){
            $customer_id = $_REQUEST['customer_id'];
            $financial_id = $_REQUEST['financial_id'];
            $company_id = $_REQUEST['company_id'];
         
            $q = "1";
            if($_REQUEST['customer_id']){
                $q .= " AND cus_id='" . $customer_id . "'";
            }
            if($_REQUEST['financial_id']){
                $q .= " AND financial_id='" . $financial_id . "'";
            }
            if($_REQUEST['company_id']){
                $q .= " AND company_id='" . $company_id . "'";
            }
            
                
        $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 . "'");
        
        $companyRow = $db->get_rsltset("SELECT company_name 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));
    
        $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 ");
        // print_r("SELECT * FROM transaction_details WHERE $q AND IsActive=1 AND date BETWEEN '" . $from_date . "' AND '" . $to_date . "' ORDER BY date ASC ");
        $company_name = $companyRow[0]['company_name'];
        $customer_name = $customerRow[0]['cus_name'];
        // echo  $company_name;
        // exit;
        foreach ($transactions as $transaction) {
            $totalDebit += floatval($transaction['debit']);
            $totalCredit += floatval($transaction['credit']);
        }
        $closingBalance =  $totalDebit - $totalCredit ;
    
        $totalDebit;
        $totalCredit;
        $closingBalance = (abs($closingBalance));
    
        $dataPoints = array( 
            array("y" => $totalCredit == null ? 0 : $totalCredit,"label" => "Credit","color"=>"rgb(109, 120, 173)"  ),
            array("y" => $totalDebit == null ? 0 : $totalDebit,"label" => "Debit" ),
            array("y" => $closingBalance,"label" => "Overall Balance","color"=>"rgb(223, 121, 112)"  )
        );
    
        echo json_encode(array('Credit' => $totalCredit, 'Sales Outstanding' => $closingBalance, 'dataPoints' => $dataPoints));
    }
    break;


    case 'get_sales_report':
       
        $financial_id = $_REQUEST['financial_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'];
        
        $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 financial_id = '" .$financial_id. "'
                                            GROUP BY YEAR(s_bill_date), MONTH(s_bill_date)
                                            ORDER BY year, month");

        $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 financial_id = '" .$financial_id. "'
                                        GROUP BY YEAR(return_date), MONTH(return_date) ORDER BY year, month;");

        $pending = $db ->get_rsltset("SELECT COUNT(*) as total_count  FROM t_sales_payment WHERE payment_status_id = '1' AND financial_id = '".$financial_id."'   AND IsActive = 1");

        $pendingCount = $pending[0]['total_count'];
        
        $credit_query = $db->get_rsltset("SELECT * FROM transaction_details  WHERE financial_id = '".$financial_id."' AND particulars ='Sales' AND date BETWEEN '" . $from_date . "' AND '" . $to_date . "'  AND IsActive = 1");
$credit_total = 0;
        $debit_query = $db->get_rsltset("SELECT * FROM transaction_details  WHERE financial_id = '".$financial_id."' AND particulars !='Sales' AND date BETWEEN '" . $from_date . "' AND '" . $to_date . "'  AND IsActive = 1");
$debit_total = 0;

foreach($credit_query as $row ){
        $credit_amt = floatval($row['credit']); 
        $credit_total += $credit_amt;
}
foreach($debit_query as $row ){
        $debit_amt = floatval($row['debit']); 
        $debit_total += $debit_amt;
}
$overall_balance = abs($credit_total - $debit_total);

$transactions = $db->get_rsltset("SELECT * FROM transaction_details  WHERE financial_id = '".$financial_id."'  AND IsActive=1 AND date BETWEEN '" . $from_date . "' AND '" . $to_date . "' ORDER BY date ASC ");
// print_r("SELECT * FROM transaction_details WHERE $q AND IsActive=1 AND date BETWEEN '" . $from_date . "' AND '" . $to_date . "' ORDER BY date ASC ");
$company_name = $companyRow[0]['company_name'];
$customer_name = $customerRow[0]['cus_name'];
// echo  $company_name;
// exit;
foreach ($transactions as $transaction) {
    $totalDebit += floatval($transaction['debit']);
    $totalCredit += floatval($transaction['credit']);
}
$closingBalance = abs( $totalDebit - $totalCredit );

// print_r("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 financial_id = '" .$financial_id. "'
// GROUP BY YEAR(s_bill_date), MONTH(s_bill_date)
// ORDER BY year, month");

        // Create an associative array to store the monthly sales data
        $monthly_sales = array();
        $monthly_return = array();
        
        foreach ($sales_query as $sale) {
            
            // Format the month and year as "YYYY-MM" for the JSON response
            $sales_month = sprintf('%04d-%02d', $sale['year'], $sale['month']);
            $total_sales = floatval($sale['total_sales']);
            $sales_no = floatval($sale['total_sales_no']);
    
            // Add the data to the associative array
            $monthly_sales[] = array(
                'month' => $sales_month,
                'total_sales' => $total_sales,
               
            );
        $sales_bill += $sales_no;
        $sales_amt += $total_sales;
        
        }
        
        foreach ($return_query as $return) {
            // Format the month and year as "YYYY-MM" for the JSON response
            $return_month = sprintf('%04d-%02d', $return['year'], $return['month']);
            $total_return = floatval($return['total_return']);
            $return_no = floatval($return['total_return_no']);

            $monthly_return[] = array(
                'month' => $return_month,
                'total_return' => $total_return,
                'bill' => $return_no

            );
            $return_bill += $return_no;
            $return_amt += $total_return;
        }

$balance = $sales_amt - $return_amt;

$dataPoints_pie = array(
    array("y" => $totalCredit, "label" => "Credit"),
    array("y" => $totalDebit, "label" => "Debit"),
    array("y" => $closingBalance, "label" => "Overall Balance")
);

    // print_r($sales_query[0]['total_sales_no']);
        $resultData = array(
            'salesData' => $monthly_sales,
            'returnData' => $monthly_return,
            'sales' => $sales_bill,
            'sales_amt' => $sales_amt == null ? 0 : $sales_amt,
            'return' => $return_bill,
            'return_amt' => $return_amt == null ? 0 : $return_amt,
            'balance' => $closingBalance,
            'pending' => $pendingCount,
            'dataPoints_pie' => $dataPoints_pie
        );
    
        // Send the combined data as JSON
        echo json_encode($resultData);
        break;

    case 'get_company_details':
        $company_id = $_REQUEST['company_id'];
        $financial_id = $_REQUEST['financial_id'];

        $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 financial_id = '" .$financial_id. "'
                                GROUP BY YEAR(s_bill_date), MONTH(s_bill_date)
                                ORDER BY year, month");

// print_r("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 financial_id = '" .$financial_id. "' 
// GROUP BY YEAR(s_bill_date), MONTH(s_bill_date)
// ORDER BY year, month");
$transactions_credit = $db->get_rsltset("SELECT * FROM transaction_details WHERE company_id='" . $company_id . "'  AND financial_id='" . $financial_id . "' AND particulars ='Sales' AND IsActive=1  ");
    
$transactions_debit = $db->get_rsltset("SELECT * FROM transaction_details WHERE company_id='" . $company_id . "'  AND financial_id='" . $financial_id . "' AND particulars !='Sales' AND IsActive=1  ");

$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 financial_id = '" . $financial_id . "' AND company_id='" . $company_id . "'  
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 financial_id = '" . $financial_id . "' AND company_id='" . $company_id . "'  
                                    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 financial_id = '" . $financial_id . "' AND company_id='" . $company_id . "'  ) - 
(SELECT COUNT(*) FROM transaction_details WHERE payment_status = '2' AND   financial_id = '" . $financial_id . "' AND company_id='" . $company_id . "'   AND particulars = 'Sales') AS pending_count");

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


$total_return = 0;
$total_sales = 0;
$return_count = 0; 
$sale_count = 0;
$monthly_sales = array();
$monthly_return = array();

foreach ($sales_query as $sale) {
    // Format the month and year as "YYYY-MM" for the JSON response
    $sales_month = sprintf('%04d-%02d', $sale['year'], $sale['month']);
    $total_sales = floatval($sale['total_sales'] ?? 0);
    $sales_no = floatval($sale['total_sales_no']);

    // Add the data to the associative array
    $monthly_sales[] = array(
        'month' => $sales_month,
        'total_sales' => $total_sales,
    
    );
$sales_bill += $sales_no;
$sales_amt += $total_sales;

}

foreach ($return_query as $return) {
    // Format the month and year as "YYYY-MM" for the JSON response
    $return_month = sprintf('%04d-%02d', $return['year'], $return['month']);
    $total_return = floatval($return['total_return'] );
    $return_no = floatval($return['total_return_no']);

    $monthly_return[] = array(
        'month' => $return_month,
        'total_return' => $total_return,
        'bill' => $return_no

    );
    $return_bill += $return_no;
    $return_amt += $total_return;
}

$credit_total = 0;
$debit_total = 0;

foreach ($transactions_credit as $transaction) {
    $credit = floatval($transaction['credit']);
    $credit_total += $credit;
}

foreach ($transactions_debit as $transaction) {
    $debit = floatval($transaction['debit']);
    $debit_total += $debit;
}

// Calculate the difference
$credit_minus_debit = $credit_total - $debit_total;

// Now, $credit_minus_debit contains the difference between total credit and total debit

$credit_monthly_totals = [];
$debit_monthly_totals = [];

foreach ($transactions_credit as $transaction) {
    $date = date('Y-m', strtotime($transaction['date']));
    $credit = floatval($transaction['credit']);
    // $bill = floatval($transaction['total_sales_no']);


    if (!isset($credit_monthly_totals[$date])) {
        $credit_monthly_totals[$date] = 0;
    }
    $credit_monthly_totals[$date] += $credit;
}

foreach ($transactions_debit as $transaction) {
    $date = date('Y-m', strtotime($transaction['date']));
    $debit = floatval($transaction['debit']);
    if (!isset($debit_monthly_totals[$date])) {
        $debit_monthly_totals[$date] = 0;
    }
    $debit_monthly_totals[$date] += $debit;
}
// echo $sales;
$balance = $sales_amt - $return_amt;

$dataPoints = array( 
    array("y" =>  $credit_total == null ? 0 : $credit_total, "label" => "Credit","color"=>"rgb(109, 120, 173)"  ),
    array("y" => $debit_total == null ? 0 : $debit_total, "label" => "Debit" ),
    array("y" => $credit_minus_debit ,"label" => "Overall Balance","color"=>"rgb(223, 121, 112)"  )
);

$resultData = array(
    'salesData' => $monthly_sales,
    'returnData' => $monthly_return,
    'balance' => $credit_minus_debit,
    'return' => $return_amt == null ? 0 : $return_amt,
    'pending' => $pendingCount,
    'sales' => $sales_amt == null ? 0 : $sales_amt,
    'dataPoints' => $dataPoints,

);

echo json_encode($resultData);
// break;

}


?>

