如何基于存储过程实现标签分钟级更新并按8小时班次重置

Let's break down how to implement your requirement step by step—we'll adjust your stored procedure, add real-time front-end updates, and handle shift resets automatically.

1. Modify Stored Procedure for Shift-Based & Minute-Wise Stats #

First, let's create a new stored procedure (or update your existing one) to calculate the current shift's total packed quantity and the count from the last minute. This will handle shift resets automatically based on the current time.

USE [Production]
GO

ALTER PROC [dbo].[uspGetCurrentShiftPackStats]
    @SeriesMasterId int,
    @CurrentTime DateTime = NULL
AS
SET NOCOUNT ON;

-- Default to current time if not provided
IF @CurrentTime IS NULL
    SET @CurrentTime = GETDATE();

-- Calculate start of the current shift
DECLARE @ShiftStart DateTime;
DECLARE @CurrentHour int = DATEPART(HOUR, @CurrentTime);

IF @CurrentHour >= 6 AND @CurrentHour < 14
    SET @ShiftStart = DATEADD(DAY, DATEDIFF(DAY, 0, @CurrentTime), '06:00:00');
ELSE IF @CurrentHour >=14 AND @CurrentHour <22
    SET @ShiftStart = DATEADD(DAY, DATEDIFF(DAY, 0, @CurrentTime), '14:00:00');
ELSE
    SET @ShiftStart = DATEADD(DAY, DATEDIFF(DAY, 0, @CurrentTime) -1, '22:00:00');

-- Get both total shift count and last minute count
SELECT 
    COUNT(ph.Id) AS TotalPackedInShift,
    SUM(CASE WHEN psh.DtTmEnd >= DATEADD(MINUTE, -1, @CurrentTime) THEN 1 ELSE 0 END) AS PackedInLastMinute
FROM Production.dbo.PackHistory ph 
INNER JOIN Production.dbo.PackStageHistory psh on psh.PackHistoryId = ph.Id AND psh.PackScheduleStageID=2 
INNER JOIN Production.dbo.ItemSerialNumber isn on isn.Id = ph.ItemSerialNumberId 
INNER JOIN Production.dbo.ItemMaster im on im.Id = isn.ItemMasterId 
INNER JOIN Production.dbo.ItemSeriesMaster ism on ism.Id = im.SeriesMasterId AND im.SeriesMasterId = @SeriesMasterId 
WHERE psh.DtTmEnd BETWEEN @ShiftStart AND @CurrentTime 
AND psh.Successful = 1 
AND psh.ReRun=0;

What this does:

• Automatically calculates the start time of the current shift (6-14, 14-22, 22-6 next day)
• Returns two values:
  • TotalPackedInShift: Cumulative count for the current shift (resets to 0 when the shift ends)
  • PackedInLastMinute: Number of items packed in the last 60 seconds

2. Update Front-End for Real-Time Refresh #

In your WebForms page, use an UpdatePanel and Timer to refresh the label every minute without reloading the entire page.

Add Controls to Your ASPX Page #

<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>

<!-- Real-time label update panel -->
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
    <ContentTemplate>
        <asp:Label ID="lblPackStats" runat="server" Text="Total Packed in Shift: 0"></asp:Label>
        <asp:Timer ID="Timer1" runat="server" Interval="60000" OnTick="Timer1_Tick"></asp:Timer>
    </ContentTemplate>
</asp:UpdatePanel>

<!-- Keep your existing controls -->
<asp:DropDownList ID="Dropdownlist1" runat="server" AutoPostBack="true" OnSelectedIndexChanged="Dropdownlist1_SelectedIndexChanged"></asp:DropDownList>
<asp:GridView ID="GridView1" runat="server"></asp:GridView>

Note: The Timer has an interval of 60000 milliseconds (1 minute) to trigger updates.

3. Update C# Code-Behind #

Add methods to fetch the shift stats and handle the timer tick event.

Add Method to Fetch Shift Stats #

private (int TotalShift, int LastMinute) GetShiftStats(int productId)
{
    try
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ProductionConnection"].ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand("uspGetCurrentShiftPackStats", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@SeriesMasterId", productId);
                
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        int total = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
                        int lastMinute = reader.IsDBNull(1) ? 0 : reader.GetInt32(1);
                        return (total, lastMinute);
                    }
                    return (0, 0);
                }
            }
        }
    }
    catch (Exception ex)
    {
        // Log error here (use your preferred logging framework)
        return (0, 0);
    }
}

Handle Timer Tick & Initial Load #

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindDropDownList();
        InitializeLabel();
        DateTime shiftStart = DateTime.Today;
        DateTime shiftEnd = DateTime.Today.AddDays(1).AddMinutes(-1);
        int productId = int.Parse(Dropdownlist1.SelectedValue);
        Refreshdata(productId, shiftStart, shiftEnd);
    }
}

protected void Timer1_Tick(object sender, EventArgs e)
{
    // Update label every minute
    InitializeLabel();
}

protected void Dropdownlist1_SelectedIndexChanged(object sender, EventArgs e)
{
    int productId;
    if (int.TryParse(Dropdownlist1.SelectedValue, out productId))
    {
        DateTime shiftStart = DateTime.Today;
        DateTime shiftEnd = DateTime.Today.AddDays(1).AddMinutes(-1);
        Refreshdata(productId, shiftStart, shiftEnd);
        InitializeLabel(); // Update label when product changes
    }
}

private void InitializeLabel()
{
    int productId;
    if (int.TryParse(Dropdownlist1.SelectedValue, out productId))
    {
        var stats = GetShiftStats(productId);
        // Choose which value to display (or both!)
        lblPackStats.Text = $"Total Packed in Shift: {stats.TotalShift} | Last Minute: {stats.LastMinute}";
    }
}

Key Notes #

• Shift Reset: The stored procedure automatically calculates the correct shift start time, so the total count resets to 0 when the shift ends (e.g., at 14:00, 22:00, or 6:00 next day).
• Real-Time Updates: The Timer triggers a partial postback every minute, updating the label without disrupting the user experience.
• Error Handling: Added try-catch blocks to handle database connection errors gracefully.

内容的提问来源于stack exchange，提问作者user7422837