Untitled

Title(R1 C1)
Description(R2 C1)
Offering(R3 C1)
Program(R4 C1)
Category(R5 C1)
Needed By(R6 C1)

State(R7 C1)
(R7 C) consists of all the headers like state, question 1, question 2 ...

Update the below code to implement the Check mandatory headers for title,offering,needed by and state fields. 
update Check for mandatory fields for title(R1 C2),offering(R3 C2),needed by(R6 C2)


as mentioned above the headers are present vertically in r1 r2 r3 r4 r5 r6 & r7. but the below line is picking the mandatory field values incorrectly and thus giving errors. title header is in R1 C1, whereas the value is stored in R1C2. Similarly,

Offering(R3 C1), value in R3 C2
Needed By(R6 C1) Value in R6 C2








import React, { useRef, useState } from 'react';
import { Lift } from '@d-lift/core';

import {
    SimpleFileUpload,
    Label,
    Button,
    Group,
    Section,
    ListItem,
    List,
} from '@d-lift/uxcomponents';
import * as XLSX from 'xlsx';
import moment from 'moment';
import ConstantKeys from '@/Constants/ConstantKeys';
import webService from '@/Services/WebService';

const ExcelUpload = () => {
    const [status, setStatus] = useState('initial');
    const [uploadedFiles, setUploadedFiles] = useState([]);
    const fileInputRef = useRef(null);
    const [selectedFile, setSelectedFile] = useState(null);

    const handleFileChange = (e) => {
        if (Array.isArray(e) && e.length > 0) {
            const newFile = e[0];
            setSelectedFile(newFile);
            handleUpload(newFile);
        } else {
            console.log('No file selected');
        }
    };

    const handleUpload = async (file) => {
        try {
            if (!file) {
                console.error('No file selected');
                setStatus('fail');
                return;
            }

            console.log('Processing file:', file);
            const reader = new FileReader();
            reader.onload = (event) => {
                const data = new Uint8Array(event.target.result);
                const workbook = XLSX.read(data, { type: 'array' });
                const firstSheetName = workbook.SheetNames[0];
                const worksheet = workbook.Sheets[firstSheetName];
                const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1 });

                const validationResult = validateData(jsonData);
                if (validationResult.isValid) {
                    convertToJSONAndUpload(jsonData);
                } else {
                    console.log('validationResult:', validationResult);
                    console.log('errors:', validationResult.errors);

                    Lift.Application.Notification.error(`${validationResult.errors}`);
                    setStatus('fail');
                }
            };
            reader.readAsArrayBuffer(file);

            setUploadedFiles((prevFiles) => [
                ...prevFiles,
                {
                    id: Math.random() * 100 + 1,
                    name: file.name,
                },
            ]);

            setStatus('success');
        } catch (error) {
            console.error(error);
            setStatus('fail');
        }
    };

    const validateData = (data) => {
        let isValid = true;
        let errors = [];

        const headers = data.map((row) => row[0] && row[0].toLowerCase());
        const requiredHeaders = ['title', 'offering', 'needed by', 'state'];

        // Check mandatory headers
        requiredHeaders.forEach((header) => {
            if (!headers.includes(header)) {
                isValid = false;
                errors.push(`Missing mandatory header: ${header}`);
            }
        });

        // Validate each column
        data.forEach((row, rowIndex) => {
            requiredHeaders.forEach((header) => {
                const headerIndex = headers.indexOf(header);
                const fieldValue = row[1]; // Checking value in the second column

                if (headerIndex > -1 && (fieldValue === '' || fieldValue === undefined)) {
                    isValid = false;
                    errors.push(`Error in row ${rowIndex + 1}: ${header} is a mandatory field`);
                }

                // if (header === 'needed by' && headerIndex > -1) {
                //     const dateValue = fieldValue;
                //     let date;

                // Check if the date is in Excel date format (numeric)
                // if (typeof dateValue === 'number') {
                //     date = moment('1900-01-01').add(dateValue - 2, 'days'); // Excel date system
                // } else {
                //     date = moment(dateValue, 'MM/DD/YYYY', true);
                // }

                // if (!date.isValid() || date.isBefore(moment(), 'day')) {
                //     isValid = false;
                //     errors.push(
                //         `Error in row ${
                //             rowIndex + 1
                //         }: ${header} cannot be in the past or an invalid date`,
                //     );
                // }
                // }
            });
        });

        return { isValid, errors };
    };

    const convertToJSONAndUpload = (data) => {
        const jsonData = data.map((row) => {
            const rowData = {};
            rowData['header'] = row[0];
            rowData['value'] = row[1];
            return rowData;
        });

        console.log('jsonData:', jsonData);
        //callAPIToInsertData(jsonData);
    };

    const callAPIToInsertData = async (data) => {
        try {
            Lift.spinner.show();
            //------- will need to convert "needed by" field value before service call.---------

            // const requestFromExcel = {
            //     ...data,data.needed by = moment('1900-01-01').add(dateValue - 2, 'days');
            // };

            const response = await webService.createRFIRequest({
                requestBody: data,
            });

            if (response.message.code === ConstantKeys.RESPONSE_CODES.success) {
                Lift.Application.Notification.success(
                    ConstantKeys.NOTIFICATION.MSG.SUCCESS.EXCEL_UPLOAD_SUCCESS,
                );
            }
        } catch (error) {
            Lift.Application.Notification.error(
                ConstantKeys.NOTIFICATION.MSG.ERR.EXCEL_UPLOAD_FAILURE,
            );
        } finally {
            Lift.spinner.hide();
        }
    };

    return (
        <>
            <div className="ux-rfi-attachment-container ux-rfi-green-border">
                <Group width="6,6">
                    <Label
                        className="mb-0"
                        model="AddAttachments.fileList"
                        labelKey="attachments"></Label>
                    <a
                        href="/images/sample-format.xlsx"
                        download="sample-format.xlsx"
                        style={{ textDecoration: 'none' }}>
                        <Button
                            size="small"
                            className="ux-rfi-green-button float-right"
                            labelKey="download_sample"
                            postIconClass="fa fa-download"></Button>
                    </a>
                </Group>

                <Section className="pt-4">
                    <List>
                        {uploadedFiles.length > 0 ? (
                            uploadedFiles.map((file) => (
                                <ListItem key={file.id} iconKey={null}>
                                    {file.name}
                                </ListItem>
                            ))
                        ) : (
                            <ListItem iconKey={null} labelKey="empty_err"></ListItem>
                        )}
                    </List>
                    <SimpleFileUpload
                        model="AddAttachments.uploadedFiles"
                        allowDragAndDrop={false}
                        inputDisplayType="custom"
                        ref={fileInputRef}
                        style={{ display: 'none' }}
                        onDropAccepted={handleFileChange}
                        accept=".xls,.xlsx">
                        <Button
                            size="small"
                            click={() => fileInputRef.current && fileInputRef.current.click()}
                            className="ux-rfi-green-button"
                            preIconClass="fa fa-plus"
                            labelKey="add_attachment"></Button>
                    </SimpleFileUpload>
                </Section>
            </div>
        </>
    );
};

export default ExcelUpload;