<?php

    require_once __DIR__ . '/catchexception.php';
    // include db connect class
    require_once __DIR__ . '/db_config.php';
    // array for JSON response
    $response = array();
    if (!isset($_GET['branchcode']) || !isset($_GET['fromdate']) || !isset($_GET['todate'])) {
        $response["success"] = 0;
        $response["message"] = "Required field(s) is missing";

        echo json_encode($response, JSON_UNESCAPED_UNICODE);
        die;
    }

    $branchcode = ($_GET['branchcode']);
    $fromdate = ($_GET['fromdate']);
    $todate = ($_GET['todate']);
    date_default_timezone_set('Asia/Kolkata');

    try{
		
        if ($branchcode != "" && $fromdate != "" && $todate != "") {

			$paraquery = "SELECT a.voucherno,a.transactiondate,
coalesce(CAST((a.basicamount) AS DECIMAL(1000, 2)),0)  as gross_sales_amount,
coalesce(CAST((netamount) AS DECIMAL(1000, 2)),0)  as netamount,salestime,
coalesce(CAST((discamount) AS DECIMAL(1000, 2)),0) as discount,
coalesce(customermobileno,'') as mobilenumber,coalesce(customername,'') as customername,
coalesce(cgstamount,0) as cgst,coalesce(sgstamount,0) as sgst,
(coalesce(cgstamount,0) + coalesce(sgstamount,0)) as totalTaxAmount,
sum(b.qty) as totalqty,c.branchname, c.branchcode, a.createddate, a.transactionno,
json_agg(
    json_build_object(
        'shortName', d.itemname,
        'longName', d.itemname,
        'quantity', b.qty,
        'unitPrice', b.price, 
        'grossAmount', b.amount,
        'netDiscountAmount', 0,
        'netAmount', b.amount,
        'taxAmountIncluded', LTRIM(TO_CHAR((b.amount - b.basicamount),'0.00')),
        'taxAmountExcluded', 0,
        'taxAmount', LTRIM(TO_CHAR((b.amount - b.basicamount),'0.00')),
        'itemCost', b.amount,
        'createdTime', to_char(
            to_timestamp(b.createddate / 1000) AT TIME ZONE 'Asia/Kolkata',
            'YYYY-MM-DD\"T\"HH24:MI:SSOF'
        )
    )
) AS items_details

from tblsales as a
inner join tblsalesitemdetails as b on b.transactiondate=a.transactiondate and b.voucherno=a.voucherno and b.branchcode=a.branchcode
inner join tblbranchmaster as c on c.branchcode=a.branchcode
inner join tblitemmaster as d on d.itemcode=b.itemcode
where a.branchcode = $branchcode and 
to_char(to_timestamp(a.transactiondate/1000),'yyyy-mm-dd') between '$fromdate' and '$todate' 
group by a.voucherno, a.transactiondate,gross_sales_amount,netamount,salestime,discount,mobilenumber,
customername,cgst,sgst,totalTaxAmount,c.branchname, c.branchcode, a.createddate, a.transactionno
order by a.transactiondate desc, voucherno desc";

            $result = pg_query($con, $paraquery);
            if (!empty($result)) {
                if (pg_num_rows($result) > 0) {
                    // user node
                    $response["data"] = array();
                
                    while ($row = pg_fetch_assoc($result)) {
                        $mil = $row["transactiondate"];
                        $seconds = ($mil / 1000);
                        $createdmil = $row["createddate"];
                        $createdseconds = ($createdmil / 1000);

                        $tax = $row["sgst"] + $row["cgst"];
                        $grand_total =$row["netamount"] - $tax;
                        $report = array();
                        
                        $report["branchName"] = $row["branchname"];
                        $report["branchCode"] = $row["branchcode"];
                        $report["brandName"] = "Madras Coffee";
                        $report["invoiceNumber"] = $row["voucherno"];

                        /* 
                        $timestamp_ms = 1747309723000;

                        // Convert milliseconds to seconds
                        $timestamp_s = $timestamp_ms / 1000;

                        // Create DateTime object with timezone
                        $date = new DateTime("@$timestamp_s");  // "@" indicates UNIX timestamp in seconds
                        $date->setTimezone(new DateTimeZone("Asia/Kolkata")); // +05:30 timezone

                        // Format the date as required
                        $formatted = $date->format("Y-m-d\TH:i:sP");
                        */

                        $report["invoiceDate"] = date("Y-m-d\TH:i:sP",$seconds);
                        $report["invoiceDay"] = date("Y-m-d",$seconds);

                        $report["createdDate"] = date("Y-m-d\TH:i:sP",$createdseconds);
                        $report["modifiedDate"] = date("Y-m-d\TH:i:sP",$createdseconds);
                        $report["Receipt number"] = $row["transactionno"];
                        $report["Receipt Date"] = date("Y-m-d",$createdseconds);
                        $report["Transaction Time"] = date("H:i:s",$createdseconds);

                        $report["Invoice amount"] = $row["gross_sales_amount"];
                        $report["Discount amount"] = $row["discount"];
                        $report["Tax amount"] = sprintf('%0.2f', $tax);
                        $report["Round Off"] = "0.00";
                        $report["Net sale"] = $row["netamount"];

                        $report["Payment Mode"] = "Cash";
                        $report["Order Type"] = "Dine-In";
                        $report["Transaction status"] = "SALE";
                        $report["order_status"] = "Success";
                        $report["packing_charge"] = "0";

                        $report["delivery_charge"] = "0";
                        $report["service_charge"] = "0";
                        $report["addtional_charges"] = "0";

                        $items = [];
                        $items = json_decode($row["items_details"], true);
                        $report["items"] = $items;

                        // push single product into final response array
                        array_push($response["data"], $report);
                    }
                    // success
                    $response["success"] = 1;

                    // echoing JSON response
                    echo json_encode($response, JSON_UNESCAPED_SLASHES);
                } else {
                    // no user found
                    $response["success"] = 0;
                    $response["message"] = "No Report found";

                    // echo no users JSON
                    echo json_encode($response);
                }
            } else {
                // no user found
                $response["success"] = 0;
                $response["message"] = "No found";

                // echo no users JSON
                echo json_encode($response);
            }
        } else {
            // required field is missing
            $response["success"] = 0;
            $response["message"] = "Required field(s) is missing";

            echo json_encode($response, JSON_UNESCAPED_UNICODE);
        }
        mch_db_release($con);
    } catch(Exception $e) {
        mch_db_release($con);
        echo 'Message: ' .$e->getMessage();
    }
?>