Crafting an innovative application

Working on a user-friendly Windows application for manufacturing, focusing on interfaces for operators, technicians, and team leaders, using C# .NET Framework, DevExpress, and SQL Server database.

Crafting a personalized solution

I’m developing a comprehensive design approach to leverage C#, DevExpress, and SQL Server for a shift management application, including detailed workflows, data handling, and user role management.

Designing the data schema

I’m detailing a SQL Server data schema with tables for users, machines, shifts, and production orders, emphasizing real-time feedback and best practices like normalization and DevExpress controls.

Crafting interfaces and tracking

I’m working on a versatile UI, integrating badge scanning for login, and a streamlined team leader interface for shift management and tracking production, consumption, and troubleshooting insights.

Thought about production data tracking system pendant 18 secondes

Below is a comprehensive proposal and high-level design for an innovative, simple, and practical Windows application to track production data in a manufacturing environment. The solution uses C# (.NET Framework), DevExpress UI components, and a SQL Server database. The focus is on an operator/technician input interface and team leader configuration interface, both kept straightforward and efficient.

1. Core Goals and Features

1. Easy Data Entry: Operators/technicians quickly enter production information (start times, stop times, downtime causes, consumption, etc.) without overwhelming them.
2. Real-Time Feedback: Provide immediate status updates on machine activities and shift overviews.
3. Role-Based Access:
   • Operators/Technicians: Limited to data entry for assigned machines.
   • Team Leaders: Configuration controls, validation, and overview dashboards.
4. Scalability: Support multiple machines, 3-shift operations, multiple team leaders, and multiple lines.
5. Security: Ensure only authorized users (via badge scan or login) can enter or validate data.
6. Traceability: Generate lot numbers, track downtime cause/durations, and record consumption linked to specific production orders.

2. Architecture Overview

2.1 Layers and Technologies

1. Presentation Layer:
   • Built using C# .NET WinForms with DevExpress controls (e.g., XtraForm, GridControl, ChartControl, LayoutControl) for a polished and user-friendly UI.
2. Business Logic Layer:
   • Contains the core logic for validating inputs, generating lot numbers, and applying business rules (e.g., shift timing rules, user permission checks).
3. Data Access Layer:
   • Uses SQL Server for data storage.
   • Data retrieval and manipulation can use ADO.NET or an ORM like Entity Framework or Dapper for simpler CRUD operations.
4. Database:
   • Stored on a local or network-based SQL Server instance.

2.2 Proposed Database Schema (Simplified)

Below is a simplified depiction of the core tables needed (additional fields may be added as required):

1. Users

   • UserID (PK)
   • FullName
   • UserRole (Operator, Technician, TeamLeader, Manager)
   • BadgeID (for scanning)
   • Active (bool)

2. Machines

   • MachineID (PK)
   • MachineName
   • MachineType (Printing, Laminating, Complexing, etc.)
   • Active (bool)

3. Shifts

   • ShiftID (PK)
   • ShiftName (Morning, Day, Night)
   • StartTime
   • EndTime
   • TeamLeaderID (FK to Users)
   • Active (bool)

4. ProductionOrders

   • OrderID (PK)
   • OrderNumber
   • TargetQuantity
   • Active (bool)

5. ProductionRecords

   • ProductionRecordID (PK)
   • MachineID (FK)
   • ShiftID (FK)
   • OrderID (FK)
   • OperatorID (FK)
   • StartTime
   • EndTime (nullable if still running)
   • ProducedQuantity
   • LotNumber (auto-generated)
   • RecordStatus (Running, Stopped, Completed, etc.)

6. DowntimeRecords

   • DowntimeID (PK)
   • MachineID (FK)
   • ShiftID (FK)
   • OperatorID (FK)
   • StartTime
   • EndTime
   • DowntimeCause
   • Duration

7. ConsumptionRecords

   • ConsumptionID (PK)
   • OrderID (FK)
   • MachineID (FK)
   • MaterialCode
   • ConsumedQuantity
   • ConsumptionTime

3. Detailed Workflow (Step-by-Step)

Below is a typical daily workflow highlighting how each role (Team Leader, Operator/Technician) interacts with the application:

3.1 Pre-shift Configuration (Team Leader)

1. Team Leader Login

   • Team Leader launches the application and logs in with username/password (or scans badge with Manager/TeamLeader privileges).
   • System validates credentials and retrieves active shift settings.

2. Assign Resources

   • The Team Leader opens the Configuration Interface.
   • Selects the active shift (Morning, Day, Night).
   • Associates Operators/Technicians to their machines for the shift. (e.g., Machine 1 -> Operator A, Operator B).
   • Verifies that the machines are ready (no conflicts with maintenance schedule, etc.).
   • Confirms the Production Orders to be processed during the shift (e.g., linking OrderID to MachineID).

3. Authorize Declarations

   • The Team Leader enables the “Record Declarations” button for each assigned operator.
   • Only authorized users (Operator/Technician) for that shift will be permitted to record data in the next steps.

4. Check Tools / Inventory (optional)

   • Team Leader ensures required materials are available and integrated into the system (inventory checks).
   • The application can display remaining stock levels and pending consumption from previous shift.

5. Shift Start Confirmation

   • Once all configurations are confirmed, the Team Leader clicks “Start Shift” in the system.
   • The system automatically sets ShiftID as active, logs the timestamp in the database, and opens up data entry for Operators/Technicians.

3.2 Data Entry During Shift (Operators / Technicians)

1. Operator/Technician Badge Scan

   • The operator approaches the workstation and scans their badge.
   • The system verifies that the user is assigned to the active shift and the selected machine.

2. Machine Status Selection

   • On the Input Interface (simple form with large buttons and clear labels), the operator can choose the current machine activity:
     1. Production Start (begin recording production for a specific order)
     2. Production Stop (end recording and capture final quantity produced)
     3. Downtime (machine stops due to breakdown or planned stop)
     4. Consumption Declaration (record materials used for the ongoing order)

3. Production Start

   • The operator selects the active Production Order from a dropdown or typed order number.
   • The system auto-generates a Lot Number for traceability.
   • The system sets StartTime in ProductionRecords for that machine/order, status = “Running”.
   • Real-time feedback: A small indicator (DevExpress gauge or label) turns green on the supervisor screen to show the machine is in production.

4. Production Stop

   • The operator again scans badge (to ensure correct user).
   • Enters the final Produced Quantity.
   • The system calculates total duration (EndTime - StartTime) and updates ProductionRecords status to “Stopped” or “Completed”.
   • Real-time feedback: The machine indicator changes color to indicate production has ended.

5. Downtime

   • The operator/technician selects “Downtime”, chooses a Downtime Cause from a predefined list (maintenance, breakdown, changeover, etc.).
   • System records DowntimeRecords with start time.
   • When downtime ends, the operator must close the record (end time).
   • Duration is automatically computed.
   • Real-time feedback: The machine indicator changes color to red (or another color for downtime). The shift leader sees downtime in a separate summary panel.

6. Consumption Declaration

   • For each machine, the operator can declare raw material usage (ink, film, adhesives, etc.).
   • The application captures MaterialCode, ConsumedQuantity, ConsumptionTime.
   • Links the consumption record to the active ProductionOrder.

7. Shift Transition

   • At shift end, the Team Leader can close the shift. The system ensures any open production or downtime must be stopped before finalizing.

3.3 Post-shift Review (Team Leader)

1. Data Validation

   • Team Leader reviews all ProductionRecords, DowntimeRecords, and ConsumptionRecords.
   • Has the option to correct mistakes or add notes if an operator forgot to stop the machine or entered a wrong quantity.

2. Approval

   • Confirms the shift data is correct and locks records from further modifications.
   • The system updates the shift status to “Completed.”

3. Reporting and Handover

   • Team Leader prints or exports a shift summary (production vs. targets, downtime analysis, consumption details).
   • Handover to next shift’s leader if any unresolved items need attention.

4. Innovative Elements

1. Badge Scan with Role-Aware UI
   • Simplifies authentication. Once scanned, the interface instantly shows relevant machines and tasks based on role (operator, technician, or team leader).
2. Real-Time Dashboard
   • Using DevExpress Charts and Gauges to display live statuses of machines:
     • Green = Running (production)
     • Yellow = Running but approaching planned stop
     • Red = Downtime
   • Team Leaders view the summary across all machines in one place.
3. Auto-Generated Lot Numbers
   • Ensures robust traceability from raw materials to finished goods.
   • Combines shift ID, machine ID, timestamp, or a unique sequence for easy tracking.
4. Downtime Cause Analysis
   • A pop-up or color-coded list for quick selection of the downtime cause, encouraging operators to classify downtime events accurately.
5. Flexible Configuration
   • Team Leaders can adjust machine-operator assignments on the fly, define new downtime causes, or update production orders if priorities shift.

5. Interface Design Recommendations

1. Operator/Technician Interface

   • DevExpress XtraTabControl or NavigationFrame for different actions (Production Start/Stop, Downtime, Consumption).
   • Large, clearly labeled buttons (e.g., “Start Production,” “Stop Production,” “Declare Downtime”).
   • Minimal text input—use dropdowns for selecting order numbers, downtime causes, or materials.
   • Visual cues (color-coded statuses, progress bars, or dashboards).

2. Team Leader Interface

   • Ribbon Control or Navigation Pane grouping functionality:
     • Shift Setup: select shift, assign machines, operators, etc.
     • Real-Time Monitoring: see machine statuses in a grid or chart.
     • Data Validation: separate tab to list open production records or downtime.
     • Reporting: generate shift summaries, production logs, consumption usage, etc.

3. Responsiveness and Scaling

   • DevExpress controls provide auto-scaling layouts.
   • Keep forms uncluttered; logically group fields and use tabs to separate different operations.

4. Error Prevention and Best Practices

   • Prevent partial or invalid records by requiring essential fields (order, machine, quantity) before saving.
   • Provide immediate feedback if an operator tries to stop production before starting, or start production again without finalizing an active production, etc.
   • Store logs for changes in case a record must be audited or rolled back.

6. Technical Tools and Best Practices

1. Version Control: Use Git or Azure DevOps to track changes in the codebase.
2. Continuous Integration/Delivery: Automated builds to validate each commit (using Azure DevOps, Jenkins, or GitHub Actions).
3. Database Schema:
   • Well-indexed (especially on MachineID, ShiftID, OrderID).
   • Use foreign keys and constraints to maintain referential integrity.
   • Employ stored procedures or parameterized queries for safer, quicker data operations.
4. Logging & Auditing:
   • Keep a log of all user actions (start/stop production, downtime, consumption) in a separate audit table or log framework (e.g., NLog, log4net).
   • Vital for compliance and troubleshooting.
5. Security:
   • Enforce strong credentials for team leaders/managers.
   • Validate BadgeID carefully with assigned roles.
   • Use row-level security (where appropriate) or clear role-based logic in code for data visibility.

7. Example Usage Walkthrough

• Morning Shift begins:
  1. Team Leader logs in, sets shift as “Morning Shift,” assigns Operator 1 & Operator 2 to Machine 1 and Machine 2, etc.
  2. Operator 1 scans badge at Machine 1, starts Production for Order #1234. The system logs “Production Start” (Machine 1, 08:05).
  3. Operator 2 at Machine 2 logs “Downtime” due to a mechanical issue (08:10–08:30).
  4. Technician fixes Machine 2, scans badge to end downtime (08:30).
  5. Operator 1 stops Production (Machine 1, 08:35), enters the produced quantity.
  6. Operator 3 at Machine 3 declares consumption of 10 reels of material code X (08:40).
  7. Shift ends at 16:00. Team Leader closes shift, verifies all open records are stopped/ended, and finalizes the day’s data.

8. Conclusion

This proposal outlines a simple yet comprehensive approach to building a production tracking application using C# .NET (WinForms), DevExpress for UI, and SQL Server as a robust database backend. It focuses on:

• Simplicity for Operators (intuitive, minimal clicks, badge scan for security).
• Real-Time Feedback (instant machine status updates for all stakeholders).
• Robust Configuration options for Team Leaders (machine-operator assignments, shift management, downtime definitions).
• Data Integrity and Traceability (lot numbers, consumption records, downtime tracking).

By following these workflows, designs, and best practices, you can deliver an innovative, practical manufacturing application that boosts productivity, ensures accurate data collection, and provides valuable insights into production efficiency and downtime causes.

Crafting production reporting

I'm tasked with designing SQL queries and procedures for a production reporting system. This involves creating 8 tables, including Users and ProductionRecords, and developing various stored procedures for data operations like InsertProductionRecord and StartProduction.

Focusing on key tables

I’m pinpointing essential tables such as Users, Machines, and ProductionOrders, ensuring clarity and brevity with precise T-SQL syntax and concise comments.

Crafting SQL solutions

I’m compiling a comprehensive SQL script for 8 tables and 13 procedures, including sample usage and indexing best practices, to facilitate effective production reporting scenario handling.

Thought about production reporting SQL design pendant 28 secondes

Below is an illustrative SQL Server script that demonstrates how you might implement the core tables and stored procedures for the proposed production tracking application. This includes the creation of tables, constraints, indexes, and essential stored procedures (for starting/stopping production, recording downtime, etc.). Feel free to adapt the schema and procedures to your organization’s specific naming conventions and requirements.

Note: This script is meant as a foundation or starting template. In a real-world system, you would add more validations, error handling, security measures, and transaction logic as needed.

1. Database Schema

Below are the core tables needed to capture users, shifts, machines, orders, production records, downtime events, and consumption.

--------------------------------------------------------------------------------
-- 1) Create Tables
--------------------------------------------------------------------------------

-- USERS TABLE
IF OBJECT_ID('[dbo].[Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users];
GO

CREATE TABLE [dbo].[Users]
(
    UserID        INT           IDENTITY(1,1) PRIMARY KEY,
    FullName      VARCHAR(100)  NOT NULL,
    UserRole      VARCHAR(50)   NOT NULL,   -- e.g., 'Operator', 'Technician', 'TeamLeader', 'Manager'
    BadgeID       VARCHAR(50)   NOT NULL,   -- Unique badge or employee ID
    Active        BIT           NOT NULL DEFAULT (1),   -- 1=Active, 0=Inactive
    CreatedDate   DATETIME      NOT NULL DEFAULT (GETDATE())
);
GO

-- MACHINES TABLE
IF OBJECT_ID('[dbo].[Machines]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Machines];
GO

CREATE TABLE [dbo].[Machines]
(
    MachineID     INT           IDENTITY(1,1) PRIMARY KEY,
    MachineName   VARCHAR(100)  NOT NULL,
    MachineType   VARCHAR(50)   NOT NULL,    -- e.g., 'Printing', 'Laminating', 'Complexing'
    Active        BIT           NOT NULL DEFAULT (1),
    CreatedDate   DATETIME      NOT NULL DEFAULT (GETDATE())
);
GO

-- SHIFTS TABLE
IF OBJECT_ID('[dbo].[Shifts]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Shifts];
GO

CREATE TABLE [dbo].[Shifts]
(
    ShiftID       INT           IDENTITY(1,1) PRIMARY KEY,
    ShiftName     VARCHAR(50)   NOT NULL,   -- e.g., 'Morning', 'Day', 'Night'
    StartTime     DATETIME      NOT NULL,
    EndTime       DATETIME      NOT NULL,
    TeamLeaderID  INT           NOT NULL,   -- FK to Users table
    Active        BIT           NOT NULL DEFAULT (1),
    CreatedDate   DATETIME      NOT NULL DEFAULT (GETDATE())
);
GO

-- FK: Shifts -> Users
ALTER TABLE [dbo].[Shifts]
    ADD CONSTRAINT FK_Shifts_TeamLeader
        FOREIGN KEY (TeamLeaderID) REFERENCES [dbo].[Users](UserID);
GO

-- PRODUCTION ORDERS
IF OBJECT_ID('[dbo].[ProductionOrders]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProductionOrders];
GO

CREATE TABLE [dbo].[ProductionOrders]
(
    OrderID       INT           IDENTITY(1,1) PRIMARY KEY,
    OrderNumber   VARCHAR(50)   NOT NULL,   -- e.g., 'MO-1001'
    TargetQuantity INT          NOT NULL DEFAULT (0),
    Active        BIT           NOT NULL DEFAULT (1),
    CreatedDate   DATETIME      NOT NULL DEFAULT (GETDATE())
);
GO

-- PRODUCTION RECORDS
IF OBJECT_ID('[dbo].[ProductionRecords]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProductionRecords];
GO

CREATE TABLE [dbo].[ProductionRecords]
(
    ProductionRecordID INT      IDENTITY(1,1) PRIMARY KEY,
    MachineID          INT      NOT NULL,  -- FK to Machines
    ShiftID            INT      NOT NULL,  -- FK to Shifts
    OrderID            INT      NOT NULL,  -- FK to ProductionOrders
    OperatorID         INT      NOT NULL,  -- FK to Users
    StartTime          DATETIME NOT NULL,
    EndTime            DATETIME NULL,
    ProducedQuantity   INT      NOT NULL DEFAULT (0),
    LotNumber          VARCHAR(50) NOT NULL,  -- e.g., auto-generated
    RecordStatus       VARCHAR(20) NOT NULL,  -- e.g., 'Running', 'Stopped', 'Completed'
    CreatedDate        DATETIME NOT NULL DEFAULT (GETDATE())
);
GO

-- FKs: ProductionRecords -> Machines, Shifts, Orders, Users
ALTER TABLE [dbo].[ProductionRecords]
    ADD CONSTRAINT FK_ProdRecords_Machines
        FOREIGN KEY (MachineID) REFERENCES [dbo].[Machines](MachineID);

ALTER TABLE [dbo].[ProductionRecords]
    ADD CONSTRAINT FK_ProdRecords_Shifts
        FOREIGN KEY (ShiftID) REFERENCES [dbo].[Shifts](ShiftID);

ALTER TABLE [dbo].[ProductionRecords]
    ADD CONSTRAINT FK_ProdRecords_Orders
        FOREIGN KEY (OrderID) REFERENCES [dbo].[ProductionOrders](OrderID);

ALTER TABLE [dbo].[ProductionRecords]
    ADD CONSTRAINT FK_ProdRecords_Users
        FOREIGN KEY (OperatorID) REFERENCES [dbo].[Users](UserID);
GO

-- DOWNTIME RECORDS
IF OBJECT_ID('[dbo].[DowntimeRecords]', 'U') IS NOT NULL
    DROP TABLE [dbo].[DowntimeRecords];
GO

CREATE TABLE [dbo].[DowntimeRecords]
(
    DowntimeID    INT       IDENTITY(1,1) PRIMARY KEY,
    MachineID     INT       NOT NULL,  -- FK to Machines
    ShiftID       INT       NOT NULL,  -- FK to Shifts
    OperatorID    INT       NOT NULL,  -- FK to Users
    StartTime     DATETIME  NOT NULL,
    EndTime       DATETIME  NULL,
    DowntimeCause VARCHAR(100) NOT NULL,  -- e.g., 'Breakdown', 'Maintenance', 'Changeover'
    Duration      INT       NULL,         -- in minutes (or seconds)
    CreatedDate   DATETIME  NOT NULL DEFAULT (GETDATE())
);
GO

-- FKs: DowntimeRecords -> Machines, Shifts, Users
ALTER TABLE [dbo].[DowntimeRecords]
    ADD CONSTRAINT FK_DowntimeRecords_Machines
        FOREIGN KEY (MachineID) REFERENCES [dbo].[Machines](MachineID);

ALTER TABLE [dbo].[DowntimeRecords]
    ADD CONSTRAINT FK_DowntimeRecords_Shifts
        FOREIGN KEY (ShiftID) REFERENCES [dbo].[Shifts](ShiftID);

ALTER TABLE [dbo].[DowntimeRecords]
    ADD CONSTRAINT FK_DowntimeRecords_Users
        FOREIGN KEY (OperatorID) REFERENCES [dbo].[Users](UserID);
GO

-- CONSUMPTION RECORDS
IF OBJECT_ID('[dbo].[ConsumptionRecords]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ConsumptionRecords];
GO

CREATE TABLE [dbo].[ConsumptionRecords]
(
    ConsumptionID  INT       IDENTITY(1,1) PRIMARY KEY,
    OrderID        INT       NOT NULL,  -- FK to ProductionOrders
    MachineID      INT       NOT NULL,  -- FK to Machines
    MaterialCode   VARCHAR(50) NOT NULL,
    ConsumedQuantity INT     NOT NULL DEFAULT (0),
    ConsumptionTime DATETIME NOT NULL,
    CreatedDate    DATETIME  NOT NULL DEFAULT (GETDATE())
);
GO

-- FKs: ConsumptionRecords -> ProductionOrders, Machines
ALTER TABLE [dbo].[ConsumptionRecords]
    ADD CONSTRAINT FK_ConsumptionRecords_Orders
        FOREIGN KEY (OrderID) REFERENCES [dbo].[ProductionOrders](OrderID);

ALTER TABLE [dbo].[ConsumptionRecords]
    ADD CONSTRAINT FK_ConsumptionRecords_Machines
        FOREIGN KEY (MachineID) REFERENCES [dbo].[Machines](MachineID);
GO

--------------------------------------------------------------------------------
-- 2) Recommended Indexes
--------------------------------------------------------------------------------
-- Add indexes to improve common query performance. Adjust as needed.

CREATE INDEX IDX_ProductionRecords_MachineID ON [dbo].[ProductionRecords](MachineID);
CREATE INDEX IDX_ProductionRecords_ShiftID   ON [dbo].[ProductionRecords](ShiftID);
CREATE INDEX IDX_ProductionRecords_OrderID   ON [dbo].[ProductionRecords](OrderID);

CREATE INDEX IDX_DowntimeRecords_MachineID   ON [dbo].[DowntimeRecords](MachineID);
CREATE INDEX IDX_DowntimeRecords_ShiftID     ON [dbo].[DowntimeRecords](ShiftID);

CREATE INDEX IDX_ConsumptionRecords_OrderID  ON [dbo].[ConsumptionRecords](OrderID);
GO

2. Core Stored Procedures

Below are examples of the main procedures you might need for a typical production workflow.

2.1 Shift Management

usp_Shift_Start

Marks a shift as started and sets it to active. Typically, the shift is already pre-defined with StartTime and EndTime, so this procedure might just confirm or update the shift’s status.

--------------------------------------------------------------------------------
-- Start Shift
--------------------------------------------------------------------------------
IF OBJECT_ID('[dbo].[usp_Shift_Start]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[usp_Shift_Start];
GO

CREATE PROCEDURE [dbo].[usp_Shift_Start]
(
    @ShiftID INT
)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE [dbo].[Shifts]
    SET Active = 1
    WHERE ShiftID = @ShiftID;

    -- Optional: Log any shift start event if needed
    SELECT 'Shift ' + CAST(@ShiftID AS VARCHAR) + ' started successfully.' AS Result;
END;
GO

usp_Shift_End

Finalizes or ends a shift. Ensures no open production or downtime records remain active for that shift (in a real system, you’d enforce or check that logic here).

--------------------------------------------------------------------------------
-- End Shift
--------------------------------------------------------------------------------
IF OBJECT_ID('[dbo].[usp_Shift_End]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[usp_Shift_End];
GO

CREATE PROCEDURE [dbo].[usp_Shift_End]
(
    @ShiftID INT
)
AS
BEGIN
    SET NOCOUNT ON;

    -- Example check: ensure no 'Running' ProductionRecords remain for this shift
    IF EXISTS(
       SELECT 1
       FROM [dbo].[ProductionRecords]
       WHERE ShiftID = @ShiftID
         AND RecordStatus = 'Running'
    )
    BEGIN
        RAISERROR('Cannot end shift: There are active production records.', 16, 1);
        RETURN;
    END

    -- Mark shift as inactive
    UPDATE [dbo].[Shifts]
    SET Active = 0
    WHERE ShiftID = @ShiftID;

    SELECT 'Shift ' + CAST(@ShiftID AS VARCHAR) + ' ended successfully.' AS Result;
END;
GO

2.2 Production Management

usp_Production_Start

Starts a production record for a given machine, shift, order, and operator. Generates a lot number automatically (simple example).

--------------------------------------------------------------------------------
-- Start Production
--------------------------------------------------------------------------------
IF OBJECT_ID('[dbo].[usp_Production_Start]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[usp_Production_Start];
GO

CREATE PROCEDURE [dbo].[usp_Production_Start]
(
    @MachineID INT,
    @ShiftID INT,
    @OrderID INT,
    @OperatorID INT
)
AS
BEGIN
    SET NOCOUNT ON;

    -- Validate if there is already an active production for this machine
    IF EXISTS(
       SELECT 1
       FROM [dbo].[ProductionRecords]
       WHERE MachineID = @MachineID
         AND RecordStatus = 'Running'
    )
    BEGIN
        RAISERROR('This machine is already in Running status. Stop previous production first.', 16, 1);
        RETURN;
    END

    DECLARE @LotNumber VARCHAR(50);
    SET @LotNumber = 'LOT-' + CONVERT(VARCHAR(8), GETDATE(), 112) 
                            + '-' + CAST(@MachineID AS VARCHAR) 
                            + '-' + CAST(@OrderID AS VARCHAR);

    INSERT INTO [dbo].[ProductionRecords]
    (
        MachineID,
        ShiftID,
        OrderID,
        OperatorID,
        StartTime,
        EndTime,
        ProducedQuantity,
        LotNumber,
        RecordStatus
    )
    VALUES
    (
        @MachineID,
        @ShiftID,
        @OrderID,
        @OperatorID,
        GETDATE(),
        NULL,
        0,
        @LotNumber,
        'Running'
    );

    SELECT 'Production started on Machine ' + CAST(@MachineID AS VARCHAR) 
           + ' with LotNumber: ' + @LotNumber + '.' AS Result;
END;
GO

usp_Production_Stop

Stops the active production record, calculates total produced quantity, and closes the record.

--------------------------------------------------------------------------------
-- Stop Production
--------------------------------------------------------------------------------
IF OBJECT_ID('[dbo].[usp_Production_Stop]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[usp_Production_Stop];
GO

CREATE PROCEDURE [dbo].[usp_Production_Stop]
(
    @ProductionRecordID INT,
    @ProducedQuantity   INT
)
AS
BEGIN
    SET NOCOUNT ON;

    -- Validate if record is in Running status
    IF NOT EXISTS(
       SELECT 1
       FROM [dbo].[ProductionRecords]
       WHERE ProductionRecordID = @ProductionRecordID
         AND RecordStatus = 'Running'
    )
    BEGIN
        RAISERROR('No active production found for the provided ID.', 16, 1);
        RETURN;
    END

    UPDATE [dbo].[ProductionRecords]
    SET 
        EndTime          = GETDATE(),
        ProducedQuantity = @ProducedQuantity,
        RecordStatus     = 'Stopped'
    WHERE ProductionRecordID = @ProductionRecordID;

    SELECT 'Production record ' + CAST(@ProductionRecordID AS VARCHAR) 
           + ' has been stopped successfully.' AS Result;
END;
GO

Tip: You could also have a separate “Completed” status if “Stopped” is for partial stops, or combine them as needed.

2.3 Downtime Management

usp_Downtime_Start

Records the start of a downtime event for a given machine, operator, and shift.

--------------------------------------------------------------------------------
-- Start Downtime
--------------------------------------------------------------------------------
IF OBJECT_ID('[dbo].[usp_Downtime_Start]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[usp_Downtime_Start];
GO

CREATE PROCEDURE [dbo].[usp_Downtime_Start]
(
    @MachineID INT,
    @ShiftID INT,
    @OperatorID INT,
    @DowntimeCause VARCHAR(100)
)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[DowntimeRecords]
    (
        MachineID,
        ShiftID,
        OperatorID,
        StartTime,
        EndTime,
        DowntimeCause,
        Duration
    )
    VALUES
    (
        @MachineID,
        @ShiftID,
        @OperatorID,
        GETDATE(),
        NULL,
        @DowntimeCause,
        NULL
    );

    SELECT 'Downtime started for Machine ' + CAST(@MachineID AS VARCHAR) 
           + ' due to ' + @DowntimeCause + '.' AS Result;
END;
GO

usp_Downtime_Stop

Stops the downtime record by providing the downtime ID, calculating the duration, and setting the end time.

--------------------------------------------------------------------------------
-- Stop Downtime
--------------------------------------------------------------------------------
IF OBJECT_ID('[dbo].[usp_Downtime_Stop]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[usp_Downtime_Stop];
GO

CREATE PROCEDURE [dbo].[usp_Downtime_Stop]
(
    @DowntimeID INT
)
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS(
       SELECT 1
       FROM [dbo].[DowntimeRecords]
       WHERE DowntimeID = @DowntimeID
         AND EndTime IS NULL
    )
    BEGIN
        RAISERROR('No active downtime found for the provided ID.', 16, 1);
        RETURN;
    END

    UPDATE [dbo].[DowntimeRecords]
    SET 
        EndTime = GETDATE(),
        Duration = DATEDIFF(MINUTE, StartTime, GETDATE())
    WHERE DowntimeID = @DowntimeID;

    SELECT 'Downtime record ' + CAST(@DowntimeID AS VARCHAR) 
           + ' has been closed successfully.' AS Result;
END;
GO

2.4 Consumption Declaration

usp_Consumption_Declare

Records the consumption of raw materials for a given production order and machine.

--------------------------------------------------------------------------------
-- Declare Consumption
--------------------------------------------------------------------------------
IF OBJECT_ID('[dbo].[usp_Consumption_Declare]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[usp_Consumption_Declare];
GO

CREATE PROCEDURE [dbo].[usp_Consumption_Declare]
(
    @OrderID         INT,
    @MachineID       INT,
    @MaterialCode    VARCHAR(50),
    @ConsumedQuantity INT
)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[ConsumptionRecords]
    (
        OrderID,
        MachineID,
        MaterialCode,
        ConsumedQuantity,
        ConsumptionTime
    )
    VALUES
    (
        @OrderID,
        @MachineID,
        @MaterialCode,
        @ConsumedQuantity,
        GETDATE()
    );

    SELECT 'Consumption declared: ' + CAST(@ConsumedQuantity AS VARCHAR) 
           + ' units of ' + @MaterialCode 
           + ' for OrderID ' + CAST(@OrderID AS VARCHAR) + '.' AS Result;
END;
GO

3. Example Reporting Queries

Below are a few sample SELECT queries that demonstrate how to retrieve relevant production data for dashboards or shift summaries.

3.1 Production Summary by Shift and Machine

--------------------------------------------------------------------------------
-- Production Summary by Shift and Machine
--------------------------------------------------------------------------------
SELECT 
    s.ShiftName,
    m.MachineName,
    p.OrderID,
    SUM(p.ProducedQuantity) AS TotalProduced,
    COUNT(*) AS ProductionRuns
FROM [dbo].[ProductionRecords] p
INNER JOIN [dbo].[Machines] m ON p.MachineID = m.MachineID
INNER JOIN [dbo].[Shifts]  s ON p.ShiftID   = s.ShiftID
GROUP BY 
    s.ShiftName, 
    m.MachineName,
    p.OrderID
ORDER BY 
    s.ShiftName, 
    m.MachineName;

3.2 Downtime by Cause and Duration

--------------------------------------------------------------------------------
-- Downtime by Cause and Duration
--------------------------------------------------------------------------------
SELECT 
    d.DowntimeCause,
    m.MachineName,
    s.ShiftName,
    SUM(d.Duration) AS TotalDowntimeMinutes,
    COUNT(*) AS DowntimeEvents
FROM [dbo].[DowntimeRecords] d
INNER JOIN [dbo].[Machines] m ON d.MachineID = m.MachineID
INNER JOIN [dbo].[Shifts]  s ON d.ShiftID   = s.ShiftID
WHERE d.Duration IS NOT NULL
GROUP BY 
    d.DowntimeCause,
    m.MachineName,
    s.ShiftName
ORDER BY 
    TotalDowntimeMinutes DESC;

3.3 Consumption Report by Order

--------------------------------------------------------------------------------
-- Consumption Report by Order
--------------------------------------------------------------------------------
SELECT 
    c.OrderID,
    p.OrderNumber,
    c.MaterialCode,
    SUM(c.ConsumedQuantity) AS TotalConsumed,
    COUNT(*) AS ConsumptionDeclarations
FROM [dbo].[ConsumptionRecords] c
INNER JOIN [dbo].[ProductionOrders] p ON c.OrderID = p.OrderID
GROUP BY 
    c.OrderID,
    p.OrderNumber,
    c.MaterialCode
ORDER BY 
    c.OrderID,
    c.MaterialCode;

4. Usage Flow Examples

Below is a sample scenario showing how you might call these stored procedures in sequence:

1. Team Leader starts a shift

   EXEC [dbo].[usp_Shift_Start] @ShiftID = 1;
   

2. Operator starts production on Machine 1

   EXEC [dbo].[usp_Production_Start] 
       @MachineID   = 1,
       @ShiftID     = 1,
       @OrderID     = 100,
       @OperatorID  = 10;  -- (User ID for Operator)
   

3. Operator stops production

   -- Suppose the newly created ProductionRecordID = 500 from the step above
   EXEC [dbo].[usp_Production_Stop]
       @ProductionRecordID = 500,
       @ProducedQuantity   = 2000;
   

4. Operator declares downtime

   EXEC [dbo].[usp_Downtime_Start]
       @MachineID = 2,
       @ShiftID   = 1,
       @OperatorID = 11,
       @DowntimeCause = 'Maintenance';
   -- ... later ...
   -- Suppose the DowntimeID = 300
   EXEC [dbo].[usp_Downtime_Stop]
       @DowntimeID = 300;
   

5. Operator declares consumption

   EXEC [dbo].[usp_Consumption_Declare]
       @OrderID         = 100,
       @MachineID       = 1,
       @MaterialCode    = 'FILM-001',
       @ConsumedQuantity = 10;
   

6. Team Leader ends the shift

   EXEC [dbo].[usp_Shift_End] @ShiftID = 1;
   

5. Final Notes and Best Practices

1. Transactions: In production environments, wrap critical inserts/updates in transactions to ensure data consistency, particularly for multi-step operations (e.g., start then stop production).
2. Security:
   • Implement SQL Server roles or table-level permissions to ensure that only authorized roles can execute certain procedures (e.g., only TeamLeader/Manager can start/end shifts).
   • Consider using application-layer authentication and role checks before calling these stored procedures.
3. Audit Trails: For compliance or troubleshooting, store a history of changes (who did what, when) in dedicated audit tables or via a logging framework.
4. Performance: The sample indexes provided are minimal. Profile your queries using SQL Server Profiler or Query Store to determine if additional or different indexes are necessary.
5. Scalability: If usage grows, consider partitioning or archiving older data to maintain performance.

With these scripts and procedures, you have a robust starting point for implementing a production reporting and tracking system in SQL Server. The schema is adaptable, and you can expand it with additional tables, fields, and procedures (e.g., for scheduling, tool management, or operator assignment) as your requirements evolve.

Gathering SQL insights

I’m honing in on creating comprehensive SQL queries and procedures for production reporting. Seeking user feedback to refine or offer advanced options.

Refining my approach

I'm working on scaling SQL queries to manage assignments, shifts, access, production summaries, downtime analysis, data cleansing, and user management. This is a solid step forward.

Refining SQL queries

I'm focusing on refining SQL queries for production reporting, ensuring they are labeled, documented, and include advanced procedures and roles for clarity and efficiency.

Thought about production reporting design in SQL pendant 12 secondes

Below is a comprehensive SQL Server script that creates the core database schema (tables, foreign keys, indexes) and stored procedures to manage the typical production reporting operations described previously. This script is intended as a starting template—you’ll likely extend, adapt, or refine it for your specific manufacturing environment (e.g., additional columns, advanced validations, security settings, etc.).

1. Database Schema

--------------------------------------------------------------------------------
-- 1) Create or Recreate Core Tables
--------------------------------------------------------------------------------

-- USERS TABLE
IF OBJECT_ID('[dbo].[Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users];
GO

CREATE TABLE [dbo].[Users]
(
    UserID       INT           IDENTITY(1,1) PRIMARY KEY,
    FullName     VARCHAR(100)  NOT NULL,
    UserRole     VARCHAR(50)   NOT NULL,  -- e.g.: Operator, Technician, TeamLeader, Manager
    BadgeID      VARCHAR(50)   NOT NULL,  -- For scanning / identification
    Active       BIT           NOT NULL DEFAULT (1),
    CreatedDate  DATETIME      NOT NULL DEFAULT (GETDATE())
);
GO


-- MACHINES TABLE
IF OBJECT_ID('[dbo].[Machines]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Machines];
GO

CREATE TABLE [dbo].[Machines]
(
    MachineID     INT           IDENTITY(1,1) PRIMARY KEY,
    MachineName   VARCHAR(100)  NOT NULL,
    MachineType   VARCHAR(50)   NOT NULL,  -- e.g.: Printing, Laminating, Complexing
    Active        BIT           NOT NULL DEFAULT (1),
    CreatedDate   DATETIME      NOT NULL DEFAULT (GETDATE())
);
GO


-- SHIFTS TABLE
IF OBJECT_ID('[dbo].[Shifts]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Shifts];
GO

CREATE TABLE [dbo].[Shifts]
(
    ShiftID       INT           IDENTITY(1,1) PRIMARY KEY,
    ShiftName     VARCHAR(50)   NOT NULL,  -- e.g.: Morning, Day, Night
    StartTime     DATETIME      NOT NULL,
    EndTime       DATETIME      NOT NULL,
    TeamLeaderID  INT           NOT NULL,  -- FK to Users table (TeamLeader)
    Active        BIT           NOT NULL DEFAULT (1),
    CreatedDate   DATETIME      NOT NULL DEFAULT (GETDATE())
);
GO

-- Foreign key: Shifts -> Users
ALTER TABLE [dbo].[Shifts]
    ADD CONSTRAINT FK_Shifts_TeamLeader
        FOREIGN KEY (TeamLeaderID) REFERENCES [dbo].[Users](UserID);
GO


-- PRODUCTION ORDERS TABLE
IF OBJECT_ID('[dbo].[ProductionOrders]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProductionOrders];
GO

CREATE TABLE [dbo].[ProductionOrders]
(
    OrderID         INT           IDENTITY(1,1) PRIMARY KEY,
    OrderNumber     VARCHAR(50)   NOT NULL,   -- e.g.: MO-1001
    TargetQuantity  INT           NOT NULL DEFAULT (0),
    Active          BIT           NOT NULL DEFAULT (1),
    CreatedDate     DATETIME      NOT NULL DEFAULT (GETDATE())
);
GO


-- PRODUCTION RECORDS TABLE
IF OBJECT_ID('[dbo].[ProductionRecords]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProductionRecords];
GO

CREATE TABLE [dbo].[ProductionRecords]
(
    ProductionRecordID INT        IDENTITY(1,1) PRIMARY KEY,
    MachineID          INT        NOT NULL,  -- FK to Machines
    ShiftID            INT        NOT NULL,  -- FK to Shifts
    OrderID            INT        NOT NULL,  -- FK to ProductionOrders
    OperatorID         INT        NOT NULL,  -- FK to Users
    StartTime          DATETIME   NOT NULL,
    EndTime            DATETIME   NULL,
    ProducedQuantity   INT        NOT NULL DEFAULT (0),
    LotNumber          VARCHAR(50) NOT NULL,      -- Auto-generated
    RecordStatus       VARCHAR(20) NOT NULL,      -- e.g.: Running, Stopped, Completed
    CreatedDate        DATETIME   NOT NULL DEFAULT (GETDATE())
);
GO

-- Foreign keys for ProductionRecords
ALTER TABLE [dbo].[ProductionRecords]
    ADD CONSTRAINT FK_ProdRecords_Machines
        FOREIGN KEY (MachineID) REFERENCES [dbo].[Machines](MachineID);

ALTER TABLE [dbo].[ProductionRecords]
    ADD CONSTRAINT FK_ProdRecords_Shifts
        FOREIGN KEY (ShiftID) REFERENCES [dbo].[Shifts](ShiftID);

ALTER TABLE [dbo].[ProductionRecords]
    ADD CONSTRAINT FK_ProdRecords_Orders
        FOREIGN KEY (OrderID) REFERENCES [dbo].[ProductionOrders](OrderID);

ALTER TABLE [dbo].[ProductionRecords]
    ADD CONSTRAINT FK_ProdRecords_Users
        FOREIGN KEY (OperatorID) REFERENCES [dbo].[Users](UserID);
GO


-- DOWNTIME RECORDS TABLE
IF OBJECT_ID('[dbo].[DowntimeRecords]', 'U') IS NOT NULL
    DROP TABLE [dbo].[DowntimeRecords];
GO

CREATE TABLE [dbo].[DowntimeRecords]
(
    DowntimeID     INT            IDENTITY(1,1) PRIMARY KEY,
    MachineID      INT            NOT NULL,  -- FK to Machines
    ShiftID        INT            NOT NULL,  -- FK to Shifts
    OperatorID     INT            NOT NULL,  -- FK to Users
    StartTime      DATETIME       NOT NULL,
    EndTime        DATETIME       NULL,
    DowntimeCause  VARCHAR(100)   NOT NULL,  -- e.g.: Breakdown, Maintenance, Changeover
    Duration       INT            NULL,      -- in minutes (or seconds)
    CreatedDate    DATETIME       NOT NULL DEFAULT (GETDATE())
);
GO

-- Foreign keys for DowntimeRecords
ALTER TABLE [dbo].[DowntimeRecords]
    ADD CONSTRAINT FK_DowntimeRecords_Machines
        FOREIGN KEY (MachineID) REFERENCES [dbo].[Machines](MachineID);

ALTER TABLE [dbo].[DowntimeRecords]
    ADD CONSTRAINT FK_DowntimeRecords_Shifts
        FOREIGN KEY (ShiftID) REFERENCES [dbo].[Shifts](ShiftID);

ALTER TABLE [dbo].[DowntimeRecords]
    ADD CONSTRAINT FK_DowntimeRecords_Users
        FOREIGN KEY (OperatorID) REFERENCES [dbo].[Users](UserID);
GO


-- CONSUMPTION RECORDS TABLE
IF OBJECT_ID('[dbo].[ConsumptionRecords]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ConsumptionRecords];
GO

CREATE TABLE [dbo].[ConsumptionRecords]
(
    ConsumptionID     INT            IDENTITY(1,1) PRIMARY KEY,
    OrderID           INT            NOT NULL,  -- FK to ProductionOrders
    MachineID         INT            NOT NULL,  -- FK to Machines
    MaterialCode      VARCHAR(50)    NOT NULL,  -- e.g.: Ink-001, Film-ABC
    ConsumedQuantity  INT            NOT NULL DEFAULT (0),
    ConsumptionTime   DATETIME       NOT NULL,
    CreatedDate       DATETIME       NOT NULL DEFAULT (GETDATE())
);
GO

-- Foreign keys for ConsumptionRecords
ALTER TABLE [dbo].[ConsumptionRecords]
    ADD CONSTRAINT FK_ConsumptionRecords_Orders
        FOREIGN KEY (OrderID) REFERENCES [dbo].[ProductionOrders](OrderID);

ALTER TABLE [dbo].[ConsumptionRecords]
    ADD CONSTRAINT FK_ConsumptionRecords_Machines
        FOREIGN KEY (MachineID) REFERENCES [dbo].[Machines](MachineID);
GO


--------------------------------------------------------------------------------
-- 2) Recommended Indexes (Adjust or add more based on usage)
--------------------------------------------------------------------------------
CREATE INDEX IDX_ProductionRecords_MachineID ON [dbo].[ProductionRecords](MachineID);
CREATE INDEX IDX_ProductionRecords_ShiftID   ON [dbo].[ProductionRecords](ShiftID);
CREATE INDEX IDX_ProductionRecords_OrderID   ON [dbo].[ProductionRecords](OrderID);

CREATE INDEX IDX_DowntimeRecords_MachineID   ON [dbo].[DowntimeRecords](MachineID);
CREATE INDEX IDX_DowntimeRecords_ShiftID     ON [dbo].[DowntimeRecords](ShiftID);

CREATE INDEX IDX_ConsumptionRecords_OrderID  ON [dbo].[ConsumptionRecords](OrderID);
GO

2. Core Stored Procedures

Below are the core stored procedures to manage shifts, start/stop production, record downtime, and declare consumptions. Each includes basic validations and error handling.

2.1 Shift Management

A) usp_Shift_Start

Marks a shift as started by setting it active. You could enhance this with more checks (e.g., verifying that the shift times are correct, ensuring the team leader is valid, etc.).

--------------------------------------------------------------------------------
-- Start Shift
--------------------------------------------------------------------------------
IF OBJECT_ID('[dbo].[usp_Shift_Start]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[usp_Shift_Start];
GO

CREATE PROCEDURE [dbo].[usp_Shift_Start]
(
    @ShiftID INT
)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE [dbo].[Shifts]
    SET Active = 1
    WHERE ShiftID = @ShiftID;

    SELECT 'Shift ' + CAST(@ShiftID AS VARCHAR) + ' started successfully.' AS Result;
END;
GO

B) usp_Shift_End

Ends a shift by marking it inactive. We also verify that no production records are still in “Running” status. In a real system, you might automatically stop them or prompt the user.

--------------------------------------------------------------------------------
-- End Shift
--------------------------------------------------------------------------------
IF OBJECT_ID('[dbo].[usp_Shift_End]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[usp_Shift_End];
GO

CREATE PROCEDURE [dbo].[usp_Shift_End]
(
    @ShiftID INT
)
AS
BEGIN
    SET NOCOUNT ON;

    -- Example check: Ensure no active production is still running on this shift
    IF EXISTS
    (
        SELECT 1
        FROM [dbo].[ProductionRecords]
        WHERE ShiftID = @ShiftID
          AND RecordStatus = 'Running'
    )
    BEGIN
        RAISERROR('Cannot end shift: There are still active production records.', 16, 1);
        RETURN;
    END

    UPDATE [dbo].[Shifts]
    SET Active = 0
    WHERE ShiftID = @ShiftID;

    SELECT 'Shift ' + CAST(@ShiftID AS VARCHAR) + ' ended successfully.' AS Result;
END;
GO

2.2 Production Management

A) usp_Production_Start

Creates a new row in ProductionRecords to indicate that production is starting. It automatically generates a lot number (in a simple format) for traceability. Also checks that the machine isn’t already in a “Running” state.

--------------------------------------------------------------------------------
-- Start Production
--------------------------------------------------------------------------------
IF OBJECT_ID('[dbo].[usp_Production_Start]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[usp_Production_Start];
GO

CREATE PROCEDURE [dbo].[usp_Production_Start]
(
    @MachineID   INT,
    @ShiftID     INT,
    @OrderID     INT,
    @OperatorID  INT
)
AS
BEGIN
    SET NOCOUNT ON;

    -- Validate if there's already an active production on this machine
    IF EXISTS
    (
        SELECT 1
        FROM [dbo].[ProductionRecords]
        WHERE MachineID = @MachineID
          AND RecordStatus = 'Running'
    )
    BEGIN
        RAISERROR('Machine is already in Running status. Stop previous production first.', 16, 1);
        RETURN;
    END

    DECLARE @LotNumber VARCHAR(50);
    SET @LotNumber = 'LOT-' + CONVERT(VARCHAR(8), GETDATE(), 112) 
                     + '-' + CAST(@MachineID AS VARCHAR) 
                     + '-' + CAST(@OrderID AS VARCHAR);

    INSERT INTO [dbo].[ProductionRecords]
    (
        MachineID,
        ShiftID,
        OrderID,
        OperatorID,
        StartTime,
        EndTime,
        ProducedQuantity,
        LotNumber,
        RecordStatus
    )
    VALUES
    (
        @MachineID,
        @ShiftID,
        @OrderID,
        @OperatorID,
        GETDATE(),
        NULL,
        0,
        @LotNumber,
        'Running'
    );

    SELECT 'Production started on Machine ' + CAST(@MachineID AS VARCHAR) 
           + ', LotNumber: ' + @LotNumber + '.' AS Result;
END;
GO

B) usp_Production_Stop

Updates the relevant ProductionRecords row to mark the production as stopped. You supply the ProductionRecordID and the final ProducedQuantity.

--------------------------------------------------------------------------------
-- Stop Production
--------------------------------------------------------------------------------
IF OBJECT_ID('[dbo].[usp_Production_Stop]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[usp_Production_Stop];
GO

CREATE PROCEDURE [dbo].[usp_Production_Stop]
(
    @ProductionRecordID INT,
    @ProducedQuantity   INT
)
AS
BEGIN
    SET NOCOUNT ON;

    -- Check the record is indeed in a Running status
    IF NOT EXISTS
    (
        SELECT 1
        FROM [dbo].[ProductionRecords]
        WHERE ProductionRecordID = @ProductionRecordID
          AND RecordStatus = 'Running'
    )
    BEGIN
        RAISERROR('No active (Running) production found for the provided ID.', 16, 1);
        RETURN;
    END

    UPDATE [dbo].[ProductionRecords]
    SET 
        EndTime = GETDATE(),
        ProducedQuantity = @ProducedQuantity,
        RecordStatus = 'Stopped'
    WHERE ProductionRecordID = @ProductionRecordID;

    SELECT 'Production record ' + CAST(@ProductionRecordID AS VARCHAR) 
           + ' has been stopped with quantity ' 
           + CAST(@ProducedQuantity AS VARCHAR) + '.' AS Result;
END;
GO

You could introduce a “Completed” status if your process differentiates between “Stopped” and “Fully Completed.” Adjust as needed.

2.3 Downtime Management

A) usp_Downtime_Start

Inserts a new downtime record for the specified machine, shift, and operator, with a given downtime cause.

--------------------------------------------------------------------------------
-- Start Downtime
--------------------------------------------------------------------------------
IF OBJECT_ID('[dbo].[usp_Downtime_Start]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[usp_Downtime_Start];
GO

CREATE PROCEDURE [dbo].[usp_Downtime_Start]
(
    @MachineID       INT,
    @ShiftID         INT,
    @OperatorID      INT,
    @DowntimeCause   VARCHAR(100)
)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[DowntimeRecords]
    (
        MachineID,
        ShiftID,
        OperatorID,
        StartTime,
        EndTime,
        DowntimeCause,
        Duration
    )
    VALUES
    (
        @MachineID,
        @ShiftID,
        @OperatorID,
        GETDATE(),
        NULL,
        @DowntimeCause,
        NULL
    );

    SELECT 'Downtime started for Machine ' + CAST(@MachineID AS VARCHAR)
           + ' due to ' + @DowntimeCause + '.' AS Result;
END;
GO

B) usp_Downtime_Stop

Completes the downtime event by updating the EndTime and calculating the Duration in minutes.

--------------------------------------------------------------------------------
-- Stop Downtime
--------------------------------------------------------------------------------
IF OBJECT_ID('[dbo].[usp_Downtime_Stop]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[usp_Downtime_Stop];
GO

CREATE PROCEDURE [dbo].[usp_Downtime_Stop]
(
    @DowntimeID INT
)
AS
BEGIN
    SET NOCOUNT ON;

    -- Check that downtime record is still open (EndTime is NULL)
    IF NOT EXISTS
    (
        SELECT 1
        FROM [dbo].[DowntimeRecords]
        WHERE DowntimeID = @DowntimeID
          AND EndTime IS NULL
    )
    BEGIN
        RAISERROR('No active downtime found for the provided ID.', 16, 1);
        RETURN;
    END

    UPDATE [dbo].[DowntimeRecords]
    SET 
        EndTime  = GETDATE(),
        Duration = DATEDIFF(MINUTE, StartTime, GETDATE())
    WHERE DowntimeID = @DowntimeID;

    SELECT 'Downtime record ' + CAST(@DowntimeID AS VARCHAR) 
           + ' has been closed.' AS Result;
END;
GO

2.4 Consumption Declaration

usp_Consumption_Declare

Records consumption of materials for a particular production order (linked to a specific machine). It logs the consumed quantity and a timestamp.

--------------------------------------------------------------------------------
-- Declare Consumption
--------------------------------------------------------------------------------
IF OBJECT_ID('[dbo].[usp_Consumption_Declare]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[usp_Consumption_Declare];
GO

CREATE PROCEDURE [dbo].[usp_Consumption_Declare]
(
    @OrderID          INT,
    @MachineID        INT,
    @MaterialCode     VARCHAR(50),
    @ConsumedQuantity INT
)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[ConsumptionRecords]
    (
        OrderID,
        MachineID,
        MaterialCode,
        ConsumedQuantity,
        ConsumptionTime
    )
    VALUES
    (
        @OrderID,
        @MachineID,
        @MaterialCode,
        @ConsumedQuantity,
        GETDATE()
    );

    SELECT 'Consumption declared: ' 
        + CAST(@ConsumedQuantity AS VARCHAR) 
        + ' of ' + @MaterialCode
        + ' for OrderID ' + CAST(@OrderID AS VARCHAR) + '.' AS Result;
END;
GO

3. Example Usage Flow

Below is a short demonstration of how one might use these procedures sequentially in a typical production shift scenario:

--------------------------------------------------------------------------------
-- EXAMPLE USAGE
--------------------------------------------------------------------------------

-- 1. Team Leader starts the shift (ShiftID=1)
EXEC [dbo].[usp_Shift_Start] @ShiftID = 1;

-- 2. Operator starts production on MachineID=1 for OrderID=100
EXEC [dbo].[usp_Production_Start] 
     @MachineID   = 1,
     @ShiftID     = 1,
     @OrderID     = 100,
     @OperatorID  = 10;   -- (Example Operator)

-- 3. Operator declares consumption
EXEC [dbo].[usp_Consumption_Declare]
     @OrderID         = 100,
     @MachineID       = 1,
     @MaterialCode    = 'INK-BLUE',
     @ConsumedQuantity = 5;

-- 4. Operator stops production (assume ProductionRecordID=501 was returned or known)
EXEC [dbo].[usp_Production_Stop]
     @ProductionRecordID = 501,
     @ProducedQuantity   = 1500;

-- 5. Technician/Operator starts downtime on MachineID=2
EXEC [dbo].[usp_Downtime_Start]
     @MachineID     = 2,
     @ShiftID       = 1,
     @OperatorID    = 11,
     @DowntimeCause = 'Maintenance';

-- (Later, after maintenance finished, say DowntimeID=300)
EXEC [dbo].[usp_Downtime_Stop]
     @DowntimeID    = 300;

-- 6. Team Leader ends the shift
EXEC [dbo].[usp_Shift_End] @ShiftID = 1;

4. Reporting Queries (Examples)

Below are a few example queries (not procedures) that showcase how to generate typical reports:

4.1 Production Summary by Shift & Machine

SELECT 
    s.ShiftName,
    m.MachineName,
    p.OrderID,
    SUM(p.ProducedQuantity) AS TotalProduced,
    COUNT(*) AS ProductionRuns
FROM [dbo].[ProductionRecords] p
INNER JOIN [dbo].[Machines] m ON p.MachineID = m.MachineID
INNER JOIN [dbo].[Shifts]  s ON p.ShiftID   = s.ShiftID
GROUP BY 
    s.ShiftName, 
    m.MachineName,
    p.OrderID
ORDER BY 
    s.ShiftName, 
    m.MachineName;