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

namespace FrameworkDAL.Query.Checking
{
    public static class CheckingQB
    {
        public const string GET_CHECKING = @" 
         SELECT
  MC.CHECKINGID AS CheckingId,
  MC.CHECKINGCODE AS CheckingCode,
  MC.CHECKINGNAME AS CheckingName,
  MC.DISPLAYTEXT AS CheckingDisplayText,
  MC.SORTORDER AS CheckingSortOrder,
  MC.VERSION AS CheckingVersion,
  MC.STATUS AS CheckingStatus,
  MC.SOURCETYPE AS CheckingSourceType,
  MC.CREATEDBYID AS CheckingCreatedById,
  U1.USERNAME AS CheckingCreatedByName,
  MC.CREATEDON AS CheckingCreatedOn,
  MC.MODIFIEDBYID AS CheckingModifiedById,
  U2.USERNAME AS CheckingModifiedByName,
  MC.MODIFIEDON AS CheckingModifiedOn
FROM 
  MCHECKING MC
LEFT JOIN MUSER U1 ON MC.CREATEDBYID = U1.USERID
LEFT JOIN MUSER U2 ON MC.MODIFIEDBYID = U2.USERID
WHERE 
  MC.CHECKINGID = @checkingid

    ";


        public const string SAVE_CHECKING = @"INSERT INTO MCHECKING
                                            (
                                                CHECKINGID,
                                                CHECKINGCODE,
                                                CHECKINGNAME,
                                                DISPLAYTEXT,
                                                SORTORDER,
                                                VERSION,
                                                STATUS,
                                                SOURCETYPE,
                                                CREATEDBYID,
                                                CREATEDON,
                                                MODIFIEDBYID,
                                                MODIFIEDON
                                            )
                                            VALUES
                                            (
                                                @CheckingId,
                                                @CheckingCode,
                                                @CheckingName,
                                                @CheckingDisplayText,
                                                @CheckingSortOrder,
                                                @CheckingVersion,
                                                @CheckingStatus,
                                                @CheckingSourceType,
                                                @CheckingCreatedById,
                                                @CheckingCreatedOn,
                                                @CheckingModifiedById,
                                                @CheckingModifiedOn
                                            );";

        public const string UPDATE_CHECKING = @"UPDATE MCHECKING
                                                SET
                                                    CHECKINGCODE = @CheckingCode,
                                                    CHECKINGNAME = @CheckingName,
                                                    DISPLAYTEXT = @CheckingDisplayText,
                                                    SORTORDER = @CheckingSortOrder,
                                                    VERSION = @CheckingVersion,
                                                    STATUS = @CheckingStatus,
                                                    SOURCETYPE = @CheckingSourceType,
                                                    MODIFIEDBYID = @CheckingModifiedById,
                                                    MODIFIEDON = @CheckingModifiedOn
                                                WHERE
                                                    CHECKINGID = @CheckingId;";

        public const string DELETE_CHECKING = @"BEGIN TRANSACTION;
                                                DELETE FROM TCHECKINGSETTINGDETAIL
                                                WHERE CHECKINGID = @checkingid;

                                                DELETE FROM MCHECKINGOPTIONDETAIL
                                                WHERE CHECKINGID = @checkingid;

                                                DELETE FROM MCHECKING
                                                WHERE CHECKINGID = @checkingid;

                                                COMMIT TRANSACTION;
                                                ";

        public const string GET_SELECTLIST_CHECKING = @"WITH Checking AS (
                                                        SELECT
                                                            A.CHECKINGID     AS Id, 
                                                            A.CHECKINGCODE   AS Code,
                                                            A.CHECKINGNAME   AS Name,
                                                            ROW_NUMBER() OVER (ORDER BY A.CHECKINGID) AS RowNum
                                                        FROM MCHECKING A
                                                    )
                                                    SELECT Id, Code, Name
                                                    FROM Checking
                                                    WHERE 
                                                        (@firstnumber = -1 AND @maxresult = -1)
                                                        OR (RowNum BETWEEN @firstnumber AND @maxresult);";
    }
}