如何结合C#后端接口与JavaScript实现两表日期列的最小间隔天数计算及页面展示

Hey there! Let's fix up your JavaScript function to get that shortest day interval displayed. First, we need to make sure we can link each denunciation (from TR_CRIME_AUTOR) to its corresponding inquerito (from TP_INQUERITO) — right now your backend queries don't include the linking field. I'll assume that TR_CRIME_AUTOR has a COD_INQUERITO foreign key that connects it to TP_INQUERITO's COD_INQUERITO; if that's not the case, just swap it for the actual linking column in your database.

First: Update Backend Queries to Include Linking Field #

Modify your GetAllCrimeAutorModel method to fetch the key that links to the inquerito table:

public List<CrimeAutorModel> GetAllCrimeAutorModel() 
{ 
    using (IDbConnection cn = ConnectionAnaliseCriminal) 
    { 
        try 
        { 
            cn.Open(); 
            // Added COD_INQUERITO to link with TP_INQUERITO records
            string query = "SELECT COD_CRIME_AUTOR, COD_INQUERITO, DataDaDenuncia " + 
                           "FROM dbo.TR_CRIME_AUTOR " + 
                           "WHERE DT_EXCLUSAO_LOGICA IS NULL"; 
            return cn.Query<CrimeAutorModel>(query).ToList(); 
        } 
        finally 
        { 
            cn.Close(); 
        } 
    } 
}

Also ensure your CrimeAutorModel class has a COD_INQUERITO property to map this new field.

Completed JavaScript Function #

Here's the full, updated shortestTimeDays function that handles all your requirements:

async function shortestTimeDays() { 
    let crimeAutors = [];
    let inqueritos = [];

    // Fetch both datasets without overwriting variables
    try { 
        const respostaCrime = await fetch(`/Dashboard/AjaxGetAllCrimeAutorModel`); 
        crimeAutors = await respostaCrime.json(); 
    } catch (e) { 
        console.error("获取报案数据出错"); 
        return console.error(e); 
    } 

    try { 
        const respostaInquerito = await fetch(`/Dashboard/AjaxGetAllInqueritoModel`); 
        inqueritos = await respostaInquerito.json(); 
    } catch (e) { 
        console.error("获取立案数据出错"); 
        return console.error(e); 
    }

    // Create a quick lookup map for inquerito dates (faster than looping every time)
    const inqueritoDateMap = new Map();
    inqueritos.forEach(inq => {
        const instauracaoDate = new Date(inq.DataDaInstauracao);
        // Only add valid dates to the map
        if (!isNaN(instauracaoDate.getTime())) {
            inqueritoDateMap.set(inq.COD_INQUERITO, instauracaoDate);
        }
    });

    const dayIntervals = [];

    // Calculate day intervals for all valid matched pairs
    crimeAutors.forEach(autor => {
        const denunciaDate = new Date(autor.DataDaDenuncia);
        // Skip invalid dates or unpaired records
        if (!isNaN(denunciaDate.getTime()) && inqueritoDateMap.has(autor.COD_INQUERITO)) {
            const instauracaoDate = inqueritoDateMap.get(autor.COD_INQUERITO);
            // Convert time difference (ms) to full days, use absolute value to handle date order
            const timeDiff = Math.abs(denunciaDate.getTime() - instauracaoDate.getTime());
            const daysDiff = Math.ceil(timeDiff / (1000 * 3600 * 24)); // Use Math.floor/round if needed
            dayIntervals.push(daysDiff);
        }
    });

    // Find the shortest interval
    let shortestDays = dayIntervals.length > 0 ? Math.min(...dayIntervals) : null;

    // Render the result to the page
    const displaySpan = document.querySelector('.blocoProcessos span:last-child');
    if (displaySpan) {
        displaySpan.textContent = shortestDays !== null ? shortestDays : 'N/A';
    }
}

// Run the function when the page finishes loading
document.addEventListener('DOMContentLoaded', shortestTimeDays);

What This Does: #

• Dual Data Fetch: Stores both datasets separately instead of overwriting variables, so we can work with both.
• Efficient Lookup: Uses a Map to instantly find matching立案日期, avoiding redundant loops.
• Date Validation: Skips invalid date strings to prevent calculation errors.
• Interval Calculation: Converts millisecond differences to full days, and uses Math.abs to handle cases where报案日期 might come before立案日期.
• Minimum Value Extraction: Uses Math.min to grab the smallest valid interval.
• DOM Rendering: Replaces the "*" in your span with the shortest day count, or "N/A" if no valid pairs exist.

Adjust the date rounding method (Math.ceil, Math.floor, Math.round) based on how you want to count partial days, and swap COD_INQUERITO for your actual linking column if needed.

内容的提问来源于stack exchange，提问作者Mizrain Phelipe Sá