@extends('layouts.excel')
@push('style')
<style>
    .x-spreadsheet-toolbar{
        width:100% !important;
    }
    /* .jss_worksheet > tbody > tr > td.readonly {
        color: #000 !important;
    } */
    .jss_worksheet > tfoot > tr > td {
        text-align: center !important;
        font-weight: bold;
        background-color: transparent !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>SoW Upload EXL<h3></div>
                    <div class="col">
                        <button id="save" class="btn btn-primary float-end">Save</button>
                        {{--<a href="{{route('showUploadExl')}}" 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>
                        <th>D</th>
                    </thead>
                    <tbody>
                        <tr>
                            @foreach($headers as $header)
                                <td style="font-weight:bold;color:#000;" class="readonly">{{$header}}</td>
                            @endforeach
                        </tr>
                        @php
                            $sum = 0;
                            $groupheading = 0;
                        @endphp
                        @foreach($pricingData as $key=>$col)
                            <tr>
                                <td class="readonly" style="color:#000;" width="90">{{$col->reference}}</td>
                                @if($col->line_type == 'group_heading')
                                    <td style="font-weight:bold;text-align:left;white-space:normal;line-height: normal;color:#000;" class="readonly">{!!str_replace("&","&#65286",$col->item)!!}
                                    </td>
                                    <td class="readonly" style="font-weight:bold;text-align:center;white-space:normal;line-height: normal;background-color:#eee;color:#ddd !important;"> {{$grouptotal[$key] !=0 ? Illuminate\Support\Number::format($grouptotal[$key],precision:2) : '0.00'}}</td>
                                    <td>{{$col->line_type}}</td>
                                @elseif($col->line_type == 'fixed_priced_item')

                                    <td style="font-style:italic;text-align:left;white-space:normal;line-height: normal;color:#000;" class="readonly">{!!str_replace("&","&#65286",$col->item)!!}
                                    </td>
                                    <td class="readonly" style="font-style:italic;text-align:right;white-space:normal;line-height: normal;width:10%;color:#000;">{{Illuminate\Support\Number::format($col->total,precision:2)}}</td>
                                    <td>{{$col->line_type}}</td>
                                @else

                                    <td class="readonly" style="text-align:left;white-space:normal;line-height: normal;color:#000;">{!!str_replace("&","&#65286",$col->item)!!}</td>
                                    <td width="100" style="text-align:right;color:#000;" width="100">0.00</td>
                                    <td>{{$col->line_type}}</td>
                                @endif
                            </tr>
                            @php
                                $sum = $sum+$col->total;
                            @endphp
                        @endforeach
                        <tr>
                                <td class="readonly" width="90" style="border-bottom:1px solid #ccc;"></td>
                                <td class="readonly" style="font-weight:bold;text-align:center;white-space:normal;line-height: normal;color:#000;border-bottom:1px solid #ccc;" >
                                    Tender Sum (carried to Form of Tender)
                                </td>
                                <td class="readonly" id="total" style="font-weight:bold;text-align:center;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;color:#000;">{{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){
    console.log(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
// 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;
}
// Run the function when pricing column change
let changeColumn = (instance, cell, col, row, val) => {
    let worksheet =  table[0];
    let lineTypeColumnName = jspreadsheet.helpers.getCellNameFromCoords(3,row);
    let lineTypeColumnValue = worksheet.getValue(lineTypeColumnName);
    if(col==2 && lineTypeColumnValue!="group_heading" && lineTypeColumnValue!=""){
        console.log('val',val);
        let formattedValue = val==""?formatNumberPricing(''):formatNumberPricing(val);
        console.log('formattedValue',formattedValue);
        cell.innerHTML = formatPricing(formattedValue);
        // get the reference of the changed cell
        let getReferenceCellName = jspreadsheet.helpers.getCellNameFromCoords(0,row);
        let getReference = worksheet.getValue(getReferenceCellName);
        console.log('getReference',getReference);
        updateGroupHeadingValues(getReference);
    }
}
// update groupHeadingTotal
let updateGroupHeadingValues = (reference) => {
    let instance = jspreadsheet.current
    let worksheet =  table[0];
    let total = 0;
    // intialized the group heading reference
    let groupHeadingRow = 0;

    // get all the rows of spreadsheet
    for(i=0;i<instance.rows.length-1;i++){
        let getRowReferenceCellName = jspreadsheet.helpers.getCellNameFromCoords(0,i);
        let getRowReferenceCellValue = worksheet.getValue(getRowReferenceCellName);
        // Check of the row reference is equal to the group_heading reference the group_heading reference like(0,1,2)
        if(getRowReferenceCellValue == parseInt(reference)) {
            groupHeadingRow = i;
            continue;
        }
        //check if the other references value before decimal is same as give reference value(before decimal)
        if(parseInt(reference) == parseInt(getRowReferenceCellValue)){
            // get the pricing columns of the current row
            let pricingCellName = jspreadsheet.helpers.getCellNameFromCoords(2,i);
            let pricingCellValue = worksheet.getValue(pricingCellName);
            console.log('CellPricingValueForGroupTotal',pricingCellValue);
            let pricing = pricingCellValue!=''?formatNumberPricing(pricingCellValue):formatNumberPricing("0.00");
            total+= pricing
            console.log('total',total)
        }
    }
    let groupHeadingCellName =  jspreadsheet.helpers.getCellNameFromCoords(2,groupHeadingRow);
    worksheet.setValue(groupHeadingCellName,formatPricing(total),true);
    updateFooter();
    return false;
}
// 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=0;i<instance.rows.length-1;i++){
        let getRowGroupHeadingCellName = jspreadsheet.helpers.getCellNameFromCoords(3,i);
        let getRowGroupHeadingCellValue = worksheet.getValue(getRowGroupHeadingCellName);

       // Check if the row is group_heading then calcuate the group_heading
        if(getRowGroupHeadingCellValue == 'group_heading') {
            let getRowGroupHeadingPricingCellName = jspreadsheet.helpers.getCellNameFromCoords(2,i);
            let getRowGroupHeadingPricingCellValue = worksheet.getValue(getRowGroupHeadingPricingCellName);
            console.log('getRowGroupHeadingPricingCellValue',getRowGroupHeadingPricingCellValue.length);
            let pricing = getRowGroupHeadingPricingCellValue.length>1?formatNumberPricing(getRowGroupHeadingPricingCellValue):0;
            console.log('pricing',parseFloat(pricing));
            total+= parseFloat(pricing)
        }
    }

    let getRowGroupHeadingPricingCellName = jspreadsheet.helpers.getCellNameFromCoords(2,instance.rows.length-1);
    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,
})
table[0].hideColumn([3]);
</script>
@endpush