@extends('layouts.excel')
@push('style')
<style>
    .x-spreadsheet-toolbar{
        width:100% !important;
    }
    .jss_worksheet > tbody > tr > td.readonly {
        color: #000 !important;
    }
    .jss_worksheet > thead > tr > td {
        display:none;
    }
    .jss_worksheet.jss_overflow{
        border-bottom:none;
    }
    .jss_row{
        opacity:0;
    }
</style>
@endpush
@section('content')
<div class="row mt-4">
    <div class="col-md-12">
        <div class="card">
            <div class="card-header">
                <div class="row">
                    <div class="col"><h3>TS Upload ECK<h3></div>
                    <div class="col">
                        <button class="btn btn-primary float-end">Save</button>
                        {{--<a href="{{route('showExcel')}}" class="btn btn-danger mx-2 float-end">Cancel</a> --}}
                    </div>
                </div>
            </div>
            <div class="card-body">
                <table id='spreadsheet'>
                    <thead>
                        <th>A</th>
                        <th>B</th>
                        <th>C</th>
                    </thead>
                    <tbody>
                        <tr>

                            @foreach($headers as $header)
                                <td style="font-weight:bold" class="readonly">{{$header}}</td>
                            @endforeach
                        </tr>
                        @php
                            $sum = 0;
                        @endphp
                        @foreach($pricingData as $col)
                            <tr>
                                <td width="90" class="readonly">{{$col->reference}}</td>
                                @if($col->line_type == 'group_heading')
                                    <td style="font-weight:bold;text-align:left;width:500px;white-space: normal;line-height: normal" class="readonly">{!!str_replace("&","&#65286",$col->item)!!}</td>
                                @else
                                    <td class="readonly" style="text-align:left;line-height: normal;width:500px;white-space: normal;">{!!str_replace("&","&#65286",$col->item)!!}</td>
                                @endif
                                <td style="width:15%;text-align:right">0.00</td>
                            </tr>
                            @php
                                $sum = $sum+$col->total;
                            @endphp
                        @endforeach
                        <tr>
                            <td style="border-bottom:1px solid #ccc;" class="readonly"></td>
                            <td class="readonly" style="font-weight:bold;text-align:center;white-space:normal;line-height: normal;border-bottom:1px solid #ccc;">
                                Tender Sum (carried to Form of Tender)
                            </td>
                            <td class="readonly" style="font-weight:bold;text-align:right;white-space:normal;line-height: normal;width:10%;border-top: 1px solid #000;border-bottom: 1px solid #000;border-bottom: 3px solid #000;border-bottom-style: double;">{{Illuminate\Support\Number::format($sum,precision:2)}}</td>
                        </tr>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>
@endsection
@push('script')
<script>

    // format pricing to comma-seprated
    function formatPricing(value){
        let price = value == '' ? 0:value;
        let formatted = parseFloat(price).toFixed(2).replace(/\d(?=(\d{3})+\.)/g, '$&,');
        return formatted;
    }
    // format comma-seprated number to number format
    function formatNumberPricing(value) {
        console.log('formatNumberPricing', value);

        // Trim the value to remove leading or trailing spaces
        value = value.trim();

        // Remove any surrounding quotes (single or double) if they exist
        value = value.replace(/^['"]|['"]$/g, '');

        // Check if the value contains any invalid characters (not digits, commas, or dots)
        if (/[^0-9.,]/.test(value)) {
            return 0;  // If it contains invalid characters, return 0
        }

        // If the value is empty or 'NaN', treat it as 0.00
        let price = value === '' || value === 'NaN' ? "0.00" : value;

        // Remove commas before parsing the number to ensure correct parsing
        let formatted = parseFloat(price.replace(/,/g, ''));

        // Return the formatted number or 0 if the value is invalid (NaN)
        return isNaN(formatted) ? 0 : formatted;
    }
    let changeColumn = (instance, cell, col, row, val) => {
        if(col==2 && row!=instance.rows.length-1){

            let formattedValue = val==""?formatNumberPricing(''):formatNumberPricing(val);
            console.log(formattedValue);
            cell.innerHTML = formatPricing(formattedValue);
            updateFooter();
        }
    }
    // update the footer total
    let updateFooter = () =>  {
        let instance = jspreadsheet.current
        let worksheet =  table[0];
        let total = 0;
            // get all the rows of spreadsheet
        for(i=1;i<instance.rows.length-1;i++){
            let getRowGroupHeadingPricingCellName = jspreadsheet.helpers.getCellNameFromCoords(2,i);
            let getRowGroupHeadingPricingCellValue = worksheet.getValue(getRowGroupHeadingPricingCellName);
            let pricing = getRowGroupHeadingPricingCellValue.length>1?formatNumberPricing(getRowGroupHeadingPricingCellValue):0;
            total+= parseFloat(pricing)
        }
        let getRowGroupHeadingPricingCellName = jspreadsheet.helpers.getCellNameFromCoords(2,instance.rows.length-1);
        console.log('getRowGroupHeadingPricingCellName',getRowGroupHeadingPricingCellName);
        worksheet.setValue(getRowGroupHeadingPricingCellName,formatPricing(total),true)
    }
    const table = jspreadsheet(document.getElementById('spreadsheet'),{
    worksheets: [{
        allowInsertColumn:false,
        allowInsertRow:false,
        allowDeleteRow:false,
        allowDeleteColumn:false
    }],
    contextMenu: function() {
        return false;
    },
    onchange:changeColumn
    })

</script>
@endpush