using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MMDAL.Query.InspectionArea
{
    public class InspectionAreaQB
    {
        public const string GET_INSPECTIONAREA = @"SELECT
                                                    IA.INSPECTIONAREAID       AS InspectionAreaId,
                                                    IA.WORKCENTERID           AS WorkCenterId,
                                                    WC.WORKCENTERCODE         AS WorkCenterCode,
                                                    WC.WORKCENTERNAME         AS WorkCenterName,
                                                    IA.INSPECTIONTYPE         AS InspectionAreaType,
                                                    IA.INSPECTIONAREA         AS InspectionAreaArea,
                                                    IA.REMARKS                AS InspectionAreaRemarks,
                                                    IA.SORTORDER              AS InspectionAreaSortOrder,
                                                    IA.STATUS                 AS InspectionAreaStatus,
                                                    IA.VERSION                AS InspectionAreaVersion,
                                                    IA.CREATEDBYID            AS InspectionAreaCreatedById,
                                                    IA.CREATEDON              AS InspectionAreaCreatedOn,
                                                    C.USERNAME                AS InspectionAreaCreatedByName,
                                                    IA.MODIFIEDBYID           AS InspectionAreaModifiedById,
                                                    IA.MODIFIEDON             AS InspectionAreaModifiedOn,
                                                    M.USERNAME                AS InspectionAreaModifiedByName,  
                                                    IA.SOURCETYPE             AS InspectionAreaSourceType
                                                FROM MINSPECTIONAREA IA
                                                LEFT JOIN MWORKCENTER WC ON IA.WORKCENTERID = WC.WORKCENTERID
                                                LEFT JOIN MUSER C ON IA.CREATEDBYID = C.USERID
                                                LEFT JOIN MUSER M ON IA.MODIFIEDBYID = M.USERID
                                                WHERE IA.INSPECTIONAREAID = @inspectionareaid;";

        public const string SAVE_INSPECTIONAREA = @" INSERT INTO MINSPECTIONAREA
                                                        (
                                                            INSPECTIONAREAID,
                                                            WORKCENTERID,
                                                            INSPECTIONTYPE,
                                                            INSPECTIONAREA,
                                                            REMARKS,
                                                            SORTORDER,
                                                            STATUS,
                                                            VERSION,
                                                            CREATEDBYID,
                                                            CREATEDON,
                                                            MODIFIEDBYID,
                                                            MODIFIEDON,
                                                            SOURCETYPE
                                                        )
                                                        VALUES
                                                        (
                                                            @InspectionAreaId,
                                                            @WorkCenterId,
                                                            @InspectionAreaType,
                                                            @InspectionAreaArea,
                                                            @InspectionAreaRemarks,
                                                            @InspectionAreaSortOrder,
                                                            @InspectionAreaStatus,
                                                            @InspectionAreaVersion,
                                                            @InspectionAreaCreatedById,
                                                            @InspectionAreaCreatedOn,
                                                            @InspectionAreaModifiedById,
                                                            @InspectionAreaModifiedOn,
                                                            @InspectionAreaSourceType
                                                        );";

        public const string UPDATE_INSPECTIONAREA = @" UPDATE MINSPECTIONAREA
                                                        SET
                                                            WORKCENTERID = @WorkCenterId,
                                                            INSPECTIONTYPE = @InspectionAreaType,
                                                            INSPECTIONAREA = @InspectionAreaArea,
                                                            REMARKS = @InspectionAreaRemarks,
                                                            SORTORDER = @InspectionAreaSortOrder,
                                                            STATUS = @InspectionAreaStatus,
                                                            VERSION = @InspectionAreaVersion,
                                                            MODIFIEDBYID = @InspectionAreaModifiedById,
                                                            MODIFIEDON = @InspectionAreaModifiedOn,
                                                            SOURCETYPE = @InspectionAreaSourceType
                                                        WHERE
                                                            INSPECTIONAREAID = @InspectionAreaId;";   

        public const string DELETE_INSPECTIONAREA = @" DELETE FROM MINSPECTIONAREA
                                                       WHERE INSPECTIONAREAID = @inspectionareaid;";

        public const string GET_SELECTLIST_INSPECTIONAREA = @"WITH InspectionArea AS
                                                            (
                                                                SELECT
                                                                    ia.INSPECTIONAREAID   AS Id,
                                                                    ia.INSPECTIONAREA     AS Area,

                                                                    ROW_NUMBER() OVER (ORDER BY ia.INSPECTIONAREA) AS RowNum

                                                                FROM dbo.MINSPECTIONAREA ia
                                                            )

                                                            SELECT *
                                                            FROM InspectionArea
                                                            WHERE
                                                                (@firstnumber = -1 AND @maxresult = -1)
                                                                OR (RowNum BETWEEN @firstnumber AND @maxresult);";
    }
}