ALTER TABLE Line_Prod_Details 
add Result VARCHAR(2) DEFAULT 'PA';
 
ALTER TABLE Line_Prod_Details
add CONSTRAINT CHK_Result_Values 
CHECK (Result IN ('PA', 'RJ', 'RE'));
 
update Line_Prod_Details set Result='PA'



USE [CHENNAI_ERP]
GO



CREATE TABLE [dbo].[Gut_End_line_Rejection_Details](
	[RowID] [bigint] IDENTITY(1,1) NOT NULL,
	[MasterID] [int] NOT NULL,
	[DefectID] [int] NULL,
	[Defect_Status] [varchar](3) NULL,
	[Def_Type] [varchar](3) NOT NULL,
)

ALTER TABLE [dbo].[Gut_End_line_Rejection_Details]  WITH CHECK ADD  CONSTRAINT [FK_Gut_EndLine_Master] FOREIGN KEY([MasterID])
REFERENCES [dbo].[Gut_qulty_endline_summary] ([id])
GO

ALTER TABLE [dbo].[Gut_End_line_Rejection_Details] CHECK CONSTRAINT [FK_Gut_EndLine_Master]
GO



ALTER TABLE [dbo].[Gut_qulty_endline_summary] ADD  DEFAULT ((0)) FOR [unit]
GO


ALTER TABLE [dbo].[Woven_Rej_Master]
ADD 
    [defect_tam] NVARCHAR(100) NULL,
    [prod_type] NVARCHAR(50) NULL;


      
CREATE PROCEDURE dbo.Get_Endline_Quality_Dashboard  
(  
    @Line INT = NULL,  
    @Unit INT = NULL,  
    @Empo INT = NULL  
)  
AS  
BEGIN  
    SET NOCOUNT ON;  
 -- DECLARE @Line INT = 10,        --  @Unit INT = 14,         -- @Empo INT = 61350;  
  
    DECLARE @Today DATE = CAST(GETDATE() AS DATE);  
  
    ------------------------------------------------------------  
    -- 🔥 BASE FILTER  
    ------------------------------------------------------------  
    ;WITH BaseData AS (  
        SELECT *  
        FROM Gut_qulty_endline_summary  
        WHERE CAST(created_at AS DATE) = @Today  
        AND (@Line IS NULL OR line = @Line)  
        AND (@Unit IS NULL OR unit = @Unit)  
        AND (@Empo IS NULL OR created_by = @Empo)  
    )  
  
    ------------------------------------------------------------  
    -- 🔥 FINAL OUTPUT  
    ------------------------------------------------------------  
    SELECT   
        -- ✅ ROW BASED  
        COUNT(*) AS checked,  
  
        SUM(CASE WHEN pass_status = 'PASS' THEN 1 ELSE 0 END) AS passed,  
  
        SUM(CASE WHEN pass_status = 'REJECT' THEN 1 ELSE 0 END) AS rejection,  
  
        SUM(CASE WHEN pass_status = 'REWORK' THEN 1 ELSE 0 END) AS rework_total,  
  
        --------------------------------------------------------  
        -- ✅ FIRST PASS (RFID + NULL SAFE)  
        --------------------------------------------------------  
        (  
            SELECT COUNT(*)  
            FROM BaseData t  
            WHERE t.pass_status = 'PASS'  
            AND NOT EXISTS (  
                SELECT 1   
                FROM BaseData t2  
                WHERE   
                    (  
                        (t.RFID IS NOT NULL AND t2.RFID = t.RFID)  
                        OR   
                        (t.RFID IS NULL AND t2.id = t.id)  
                    )  
                AND t2.pass_status = 'REWORK'  
            )  
        ) AS first_pass,  
  
        --------------------------------------------------------  
        -- ✅ REWORK COMPLETED (row-based better here)  
        --------------------------------------------------------  
        (  
            SELECT COUNT(*)  
            FROM BaseData t  
            WHERE t.pass_status = 'PASS'  
            AND EXISTS (  
                SELECT 1   
                FROM BaseData t2  
                WHERE t2.bundel_no = t.bundel_no  
                AND t2.pass_status = 'REWORK'  
            )  
        ) AS rework_completed,  
  
        --------------------------------------------------------  
        -- ✅ REWORK PENDING  
        --------------------------------------------------------  
        (  
            SELECT COUNT(*)  
            FROM BaseData t  
            WHERE t.pass_status = 'REWORK'  
            AND NOT EXISTS (  
                SELECT 1   
                FROM BaseData t2  
                WHERE t2.bundel_no = t.bundel_no  
                AND t2.pass_status = 'PASS'  
            )  
        ) AS rework_pending,  
  
        --------------------------------------------------------  
        -- ✅ REWORK PENDING YESTERDAY  
        --------------------------------------------------------  
        (  
            SELECT COUNT(*)  
            FROM Gut_qulty_endline_summary  
            WHERE pass_status = 'REWORK'  
            AND CAST(created_at AS DATE) = DATEADD(DAY, -1, @Today)  
            AND (@Line IS NULL OR line = @Line)  
            AND (@Unit IS NULL OR unit = @Unit)  
            AND (@Empo IS NULL OR created_by = @Empo)  
        ) AS rework_pending_yesterday  
  
    FROM BaseData;  
  
END



 CREATE PROCEDURE dbo.Get_Endline_Hourly_Summary  
(  
    @Unit INT,  
    @Line INT,  
    @FromDate DATE = NULL,  
    @ToDate DATE = NULL  
)  
AS  
BEGIN  
    SET NOCOUNT ON;  
  
    -- Default: Today  
    IF @FromDate IS NULL SET @FromDate = CAST(GETDATE() AS DATE);  
    IF @ToDate IS NULL SET @ToDate = CAST(GETDATE() AS DATE);  
  
    ;WITH BaseData AS (  
        SELECT   
            Rfid,  
            MAX(pass_status) AS pass_status,  
            MAX(created_at) AS created_at,  
            DATEPART(HOUR, MAX(created_at)) AS Hr  
        FROM CHENNAI_ERP.dbo.Gut_qulty_endline_summary  
        WHERE   
            CAST(created_at AS DATE) BETWEEN @FromDate AND @ToDate  
            AND unit = @Unit  
            AND line = @Line  
        GROUP BY Rfid  
    )  
  
    SELECT   
        'CHECKED' AS Metric,  
        COUNT(CASE WHEN Hr = 9 THEN 1 END) AS H1,  
        COUNT(CASE WHEN Hr = 10 THEN 1 END) AS H2,  
        COUNT(CASE WHEN Hr = 11 THEN 1 END) AS H3,  
        COUNT(CASE WHEN Hr = 12 THEN 1 END) AS H4,  
        COUNT(CASE WHEN Hr = 13 THEN 1 END) AS H5,  
        COUNT(CASE WHEN Hr = 14 THEN 1 END) AS H6,  
        COUNT(CASE WHEN Hr = 15 THEN 1 END) AS H7,  
        COUNT(CASE WHEN Hr = 16 THEN 1 END) AS H8  
    FROM BaseData  
  
    UNION ALL  
  
    SELECT   
        'PASSED',  
        COUNT(CASE WHEN Hr = 9 AND pass_status='PASS' THEN 1 END),  
        COUNT(CASE WHEN Hr = 10 AND pass_status='PASS' THEN 1 END),  
        COUNT(CASE WHEN Hr = 11 AND pass_status='PASS' THEN 1 END),  
        COUNT(CASE WHEN Hr = 12 AND pass_status='PASS' THEN 1 END),  
        COUNT(CASE WHEN Hr = 13 AND pass_status='PASS' THEN 1 END),  
        COUNT(CASE WHEN Hr = 14 AND pass_status='PASS' THEN 1 END),  
        COUNT(CASE WHEN Hr = 15 AND pass_status='PASS' THEN 1 END),  
        COUNT(CASE WHEN Hr = 16 AND pass_status='PASS' THEN 1 END)  
    FROM BaseData  
  
    UNION ALL  
  
    SELECT   
        'REWORK',  
        COUNT(CASE WHEN Hr = 9 AND pass_status='REWORK' THEN 1 END),  
        COUNT(CASE WHEN Hr = 10 AND pass_status='REWORK' THEN 1 END),  
        COUNT(CASE WHEN Hr = 11 AND pass_status='REWORK' THEN 1 END),  
        COUNT(CASE WHEN Hr = 12 AND pass_status='REWORK' THEN 1 END),  
        COUNT(CASE WHEN Hr = 13 AND pass_status='REWORK' THEN 1 END),  
        COUNT(CASE WHEN Hr = 14 AND pass_status='REWORK' THEN 1 END),  
        COUNT(CASE WHEN Hr = 15 AND pass_status='REWORK' THEN 1 END),  
        COUNT(CASE WHEN Hr = 16 AND pass_status='REWORK' THEN 1 END)  
    FROM BaseData  
  
    UNION ALL  
  
    SELECT   
        'REJECTED',  
        COUNT(CASE WHEN Hr = 9 AND pass_status='REJECT' THEN 1 END),  
        COUNT(CASE WHEN Hr = 10 AND pass_status='REJECT' THEN 1 END),  
        COUNT(CASE WHEN Hr = 11 AND pass_status='REJECT' THEN 1 END),  
        COUNT(CASE WHEN Hr = 12 AND pass_status='REJECT' THEN 1 END),  
        COUNT(CASE WHEN Hr = 13 AND pass_status='REJECT' THEN 1 END),  
        COUNT(CASE WHEN Hr = 14 AND pass_status='REJECT' THEN 1 END),  
        COUNT(CASE WHEN Hr = 15 AND pass_status='REJECT' THEN 1 END),  
        COUNT(CASE WHEN Hr = 16 AND pass_status='REJECT' THEN 1 END)  
    FROM BaseData;  
  
END;  
    
    
       
CREATE PROCEDURE dbo.InsertSewingProduction    
(    
    @Unit        INT,    
    @LineNo      INT,    
    @EmpNo       INT,    
    @BundleNo    INT,    
    @BuyerPoNo   VARCHAR(50),    
    @CutOrdNo    VARCHAR(50),    
    @Sizeid      INT,    
    @Bal_Qty     INT,    
    @RFID        VARCHAR(100),    
    @EanCode     VARCHAR(100),    
    @Type        INT ,    
    @Reject_id   int    
)    
AS    
BEGIN    
    SET NOCOUNT ON;    
    
    DECLARE @MasterID BIGINT;    
    DECLARE @Sew_ID   BIGINT;    
    DECLARE @SlNo     INT;    
    DECLARE @Today    DATE       = CAST(GETDATE() AS DATE);    
    DECLARE @Result   VARCHAR(2) = NULL;    
    DECLARE @NextEno  INT;    
    
    IF      @Type = 1 SET @Result = 'PA'    
    ELSE IF @Type = 2 SET @Result = 'RE'    
    ELSE              SET @Result = 'RJ'    
    
    IF EXISTS (    
        SELECT 1    
        FROM   woven.dbo.Line_Prod_Details    
        WHERE  RFID = @RFID    
    )    
    BEGIN    
        SELECT 0    AS status,    
               'RFID already scanned (PASS)' AS message,    
               NULL AS master_id,    
               NULL AS sew_id;    
        RETURN;    
    END    
    
    /* ── TYPE 1 : PASS ─────────────────────────────────────────────────── */    
    IF (@Type = 1)    
    BEGIN    
        SELECT @MasterID = Rowid    
        FROM   woven.dbo.Sewing_Prod_Master_New    
        WHERE  Unit_Code              = @Unit    
          AND  Line_No                = @LineNo    
          AND  Chk_emplno             = @EmpNo    
          AND  CAST(Entry_Date AS DATE) = @Today;    
    
        IF @MasterID IS NULL    
        BEGIN    
            SELECT @NextEno = ISNULL(MAX(Eno), 0) + 1    
            FROM   woven.dbo.Sewing_Prod_Master_New WITH (UPDLOCK, HOLDLOCK);    
    
            INSERT INTO woven.dbo.Sewing_Prod_Master_New    
            (    
                Eno, Edate, Unit_Code, Line_No, Internal,    
                Entry_Date, Entry_System, User_Code,    
                Company_Code, Incentive_flag,    
                Chk_emplno, Scan, Eby_Emplno    
            )    
            VALUES    
            (    
                @NextEno, GETDATE(), @Unit, @LineNo, 'Y',    
                GETDATE(), 'TECHNOTEK-AUDIT-CH', 2,    
                8, 'N',    
                @EmpNo, 'C', @EmpNo    
            );    
    
            SET @MasterID = SCOPE_IDENTITY();    
        END    
    
        IF NOT EXISTS (    
            SELECT 1    
            FROM   woven.dbo.Sewing_Prod_Details_New    
            WHERE  Master_Id    = @MasterID    
              AND  Bundle_No    = @BundleNo    
              AND  BuyerPoNo    = @BuyerPoNo    
              AND  Cut_Order_No = @CutOrdNo    
              AND  Sizeid       = @Sizeid    
        )    
        BEGIN    
            SELECT @SlNo = ISNULL(MAX(Sl_No), 0) + 1    
            FROM   woven.dbo.Sewing_Prod_Details_New    
            WHERE  Master_Id = @MasterID;    
    
            INSERT INTO woven.dbo.Sewing_Prod_Details_New    
            (    
                Master_Id, Sl_No, Bundle_No, Sizeid,    
                BuyerPoNo, Cut_Order_No,    
                Iss_Qty, Rej_Qty, Remarks    
            )    
            VALUES    
            (    
                @MasterID, @SlNo, @BundleNo, @Sizeid,    
                @BuyerPoNo, @CutOrdNo,    
                1, 0, 'CH'    
            );    
    
            SET @Sew_ID = SCOPE_IDENTITY();    
    
            UPDATE woven.dbo.Woven_Production_Constraint_Tbl    
            SET    Sew_Ok_Qty = Sew_Ok_Qty + 1,    
                   Sew_ID     = @Sew_ID    
            WHERE  Bundle_No  = @BundleNo    
              AND  BuyerPoNo  = @BuyerPoNo    
              AND  Cut_Ord_No = @CutOrdNo;    
    
            UPDATE woven.dbo.Line_Sewing_Constraint_Tbl    
            SET    Sew_Ok_Qty = Sew_Ok_Qty + 1    
            WHERE  Bundle_No  = @BundleNo    
              AND  BuyerPoNo  = @BuyerPoNo    
              AND  Cut_Ord_No = @CutOrdNo;    
        END    
        ELSE    
        BEGIN    
            SELECT @MasterID = master_id,    
                   @Sew_ID   = Rowid    
            FROM   woven.dbo.Sewing_Prod_Details_New    
            WHERE  Bundle_No    = @BundleNo    
              AND  BuyerPoNo    = @BuyerPoNo    
              AND  Cut_Order_No = @CutOrdNo    
              AND  Sizeid       = @Sizeid;    
    
            UPDATE woven.dbo.Sewing_Prod_Details_New    
            SET    Iss_Qty = ISNULL(Iss_Qty, 0) + 1    
            WHERE  Master_Id    = @MasterID    
              AND  Bundle_No    = @BundleNo    
              AND  BuyerPoNo    = @BuyerPoNo    
              AND  Cut_Order_No = @CutOrdNo    
              AND  Sizeid       = @Sizeid;    
    
            UPDATE woven.dbo.Woven_Production_Constraint_Tbl    
            SET    Sew_Ok_Qty = Sew_Ok_Qty + 1,    
                   Sew_ID     = @Sew_ID    
            WHERE  Bundle_No  = @BundleNo    
              AND  BuyerPoNo  = @BuyerPoNo    
              AND  Cut_Ord_No = @CutOrdNo;    
    
            UPDATE woven.dbo.Line_Sewing_Constraint_Tbl    
            SET    Sew_Ok_Qty = Sew_Ok_Qty + 1    
            WHERE  Bundle_No  = @BundleNo    
              AND  BuyerPoNo  = @BuyerPoNo    
              AND  Cut_Ord_No = @CutOrdNo;    
        END    
    END    
    
       
    
       
    IF (@Type = 3)    
    BEGIN    
        SELECT @MasterID = Rowid    
        FROM   woven.dbo.Sewing_Prod_Master_New    
        WHERE  Unit_Code              = @Unit    
          AND  Line_No                = @LineNo    
          AND  Chk_emplno             = @EmpNo    
          AND  CAST(Entry_Date AS DATE) = @Today;    
    
        IF @MasterID IS NULL    
        BEGIN    
            SELECT @NextEno = ISNULL(MAX(Eno), 0) + 1    
            FROM   woven.dbo.Sewing_Prod_Master_New WITH (UPDLOCK, HOLDLOCK);    
    
            INSERT INTO woven.dbo.Sewing_Prod_Master_New    
            (    
                Eno, Edate, Unit_Code, Line_No, Internal,    
                Entry_Date, Entry_System, User_Code,    
                Company_Code, Incentive_flag,    
                Chk_emplno, Scan, Eby_Emplno    
            )    
            VALUES    
            (    
                @NextEno, GETDATE(), @Unit, @LineNo, 'Y',    
                GETDATE(), 'TECHNOTEK-AUDIT-CH', 2,    
                8, 'N',    
                @EmpNo, 'C', @EmpNo    
            );    
    
            SET @MasterID = SCOPE_IDENTITY();    
        END    
    
        IF NOT EXISTS (    
            SELECT 1    
            FROM   woven.dbo.Sewing_Prod_Details_New    
            WHERE  Master_Id    = @MasterID    
              AND  Bundle_No    = @BundleNo    
              AND  BuyerPoNo    = @BuyerPoNo    
              AND  Cut_Order_No = @CutOrdNo    
              AND  Sizeid       = @Sizeid    
        )    
        BEGIN    
            SELECT @SlNo = ISNULL(MAX(Sl_No), 0) + 1    
            FROM   woven.dbo.Sewing_Prod_Details_New    
            WHERE  Master_Id = @MasterID;    
    
            INSERT INTO woven.dbo.Sewing_Prod_Details_New    
            (    
                Master_Id, Sl_No, Bundle_No, Sizeid,    
                BuyerPoNo, Cut_Order_No,    
                Iss_Qty, Rej_Qty, Remarks    
            )    
            VALUES    
            (    
                @MasterID, @SlNo, @BundleNo, @Sizeid,    
                @BuyerPoNo, @CutOrdNo,    
                0, 1, 'CH'    
            );    
            UPDATE woven.dbo.Woven_Production_Constraint_Tbl    
            SET    Sew_Rej_Qty = Sew_Rej_Qty + 1,    
                   Sew_ID     = @Sew_ID    
            WHERE  Bundle_No  = @BundleNo    
              AND  BuyerPoNo  = @BuyerPoNo    
              AND  Cut_Ord_No = @CutOrdNo;    
    
            UPDATE woven.dbo.Line_Sewing_Constraint_Tbl    
            SET    Sew_Rej_Qty = Sew_Rej_Qty + 1    
            WHERE  Bundle_No  = @BundleNo    
              AND  BuyerPoNo  = @BuyerPoNo    
              AND  Cut_Ord_No = @CutOrdNo;    
    
             INSERT INTO woven.dbo.Line_Rej_details    
            (rej_type_id, Qty, bundle_no, master_id, Rfid, EanCode)    
            VALUES    
            (@Reject_id, 1, @BundleNo, @MasterID, @RFID, @EanCode);    
        END    
        else    
        BEGIN    
        SELECT @MasterID = master_id,    
                   @Sew_ID   = Rowid    
            FROM   woven.dbo.Sewing_Prod_Details_New    
            WHERE  Bundle_No    = @BundleNo    
              AND  BuyerPoNo    = @BuyerPoNo    
              AND  Cut_Order_No = @CutOrdNo    
              AND  Sizeid = @Sizeid;    
    
            UPDATE woven.dbo.Sewing_Prod_Details_New    
            SET    Rej_Qty = ISNULL(Rej_Qty, 0) + 1    
            WHERE  Master_Id    = @MasterID    
              AND  Bundle_No    = @BundleNo    
              AND  BuyerPoNo    = @BuyerPoNo    
              AND  Cut_Order_No = @CutOrdNo    
              AND  Sizeid       = @Sizeid;    
              UPDATE woven.dbo.Woven_Production_Constraint_Tbl    
            SET    Sew_Rej_Qty = Sew_Rej_Qty + 1,    
                   Sew_ID     = @Sew_ID    
            WHERE  Bundle_No  = @BundleNo    
              AND  BuyerPoNo  = @BuyerPoNo    
              AND  Cut_Ord_No = @CutOrdNo;    
    
            UPDATE woven.dbo.Line_Sewing_Constraint_Tbl    
            SET    Sew_Rej_Qty = Sew_Rej_Qty + 1    
            WHERE  Bundle_No  = @BundleNo    
              AND  BuyerPoNo  = @BuyerPoNo    
              AND  Cut_Ord_No = @CutOrdNo;    
   
            INSERT INTO woven.dbo.Line_Rej_details    
            (Rej_Type_id, Qty, bundle_no, master_id, Rfid, EanCode)    
            VALUES    
            (@Reject_id, 1, @BundleNo, @MasterID, @RFID, @EanCode);    
        End    
    END    
    
     
    SELECT TOP 1 @Sew_ID = Rowid    
    FROM   woven.dbo.Sewing_Prod_Details_New    
    WHERE  Master_Id    = @MasterID    
      AND  Bundle_No    = @BundleNo    
      AND  BuyerPoNo    = @BuyerPoNo    
      AND  Cut_Order_No = @CutOrdNo    
      AND  Sizeid       = @Sizeid    
    ORDER BY Rowid DESC;    
    
    INSERT INTO woven.dbo.Line_Prod_Details    
    (    
        Master_id,    
        Bundle_No,    
        EAN_Code,    
        RFID,    
        Qty,    
        Entry_Time,    
        Entry_At,    
        User_Code,    
        Result    
    )    
    VALUES    
    (    
        @MasterID,    
        @BundleNo,    
        @EanCode,    
        @RFID,    
        1,    
        GETDATE(),    
        'CH',    
        @EmpNo,    
        @Result    
    );    
    
    SELECT 1         AS status,    
           'Success' AS message,    
           @MasterID AS master_id,    
           @Sew_ID   AS sew_id;    
END 


CREATE PROCEDURE dbo.GetBundleByEAN
    @EAN VARCHAR(50),
    @UnitCode VARCHAR(50),
    @LineNo VARCHAR(50),
    @RFID VARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE 
        @IMMAN VARCHAR(50),
        @Model VARCHAR(100),
        @Size VARCHAR(50),
        @Result VARCHAR(10),
        @Message VARCHAR(100);

   
    SELECT TOP 1
        @IMMAN = A.[cc],
        @Model = A.[Model Code],
        @Size = C.[Size]
    FROM woven.dbo.Decathlon_Artical_Excel A
    LEFT JOIN woven.dbo.Garments_item_master B 
        ON A.[Item Code] = B.itemcode
    LEFT JOIN woven.dbo.Size C 
        ON B.sizeid = C.sizeid
    WHERE A.[EAN Code] = @EAN;

    SELECT TOP 1 
        @Result = Result
    FROM woven.dbo.Line_Prod_Details
    WHERE RFID = @RFID;

    IF (@Result IS NOT NULL AND LTRIM(RTRIM(@Result)) = 'PA')
        SET @Message = 'RFID already scanned (PASS)';
    ELSE IF (@Result IS NOT NULL)
        SET @Message = 'RFID already scanned (FAIL)';
    ELSE
        SET @Message = 'RFID not scanned';

    SELECT @Message AS RFID_Status;
    EXEC dbo.GetBundleWiseProduction
        @UnitCode = @UnitCode,
        @LineNo = @LineNo,
        @CCNo = @IMMAN,
        @Model = @Model,
        @Size = @Size;
END

