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

namespace FrameworkDAL.Query.DataValidation
{
    public static class DataValidationQB
    {
        public const string GET_DATAVALIDATION = @"SELECT
                            DV.DATAVALIDATIONID AS DataValidationId,
                            DV.DATAVALIDATIONCODE AS DataValidationCode,
                            DV.DATAVALIDATIONNAME AS DataValidationName,
                            DV.SECTION AS DataValidationSection,
                            DV.MODULEID AS ModuleId,
                            M.MODULECODE AS ModuleCode,
                            M.MODULENAME AS ModuleName,
                            DV.DETAILS AS DataValidationDetails,
                            DV.QUERY AS DataValidationQuery,
                            DV.SOLUTION AS DataValidationSolution,
                            DV.SORTORDER AS DataValidationSortOrder,
                            DV.STATUS AS DataValidationStatus,
                            DV.VERSION AS DataValidationVersion,
                            DV.SOURCETYPE AS DataValidationSourceType,
                            DV.CREATEDBYID AS DataValidationCreatedById,
                            DV.CREATEDON AS DataValidationCreatedOn,
                            DV.MODIFIEDBYID AS DataValidationModifiedById,
                            DV.MODIFIEDON AS DataValidationModifiedOn
                            FROM MDATAVALIDATION DV
                            LEFT JOIN MMODULE M ON DV.MODULEID = M.MODULEID
                            WHERE DV.DATAVALIDATIONID = @datavalidationid;
                            ";

        public const string SAVE_DATAVALIDATION = @"INSERT 
                            INTO MDATAVALIDATION (
                                 DATAVALIDATIONID,
                                 DATAVALIDATIONCODE,
                                 DATAVALIDATIONNAME,
                                 SECTION,
                                 MODULEID,
                                 DETAILS,
                                 QUERY,
                                 SOLUTION,
                                 SORTORDER,
                                 STATUS,
                                 VERSION,
                                 CREATEDBYID,
                                 CREATEDON,
                                 MODIFIEDBYID,
                                 MODIFIEDON
                            ) VALUES (
                                 @DataValidationId,
                                 @DataValidationCode,
                                 @DataValidationName,
                                 @DataValidationSection,
                                 @ModuleId,
                                 @DataValidationDetails,
                                 @DataValidationQuery,
                                 @DataValidationSolution,
                                 @DataValidationSortOrder,
                                 @DataValidationStatus,
                                 @DataValidationVersion,
                                 @DataValidationCreatedById,
                                 @DataValidationCreatedOn,
                                 @DataValidationModifiedById,
                                 @DataValidationModifiedOn
                            );
                            ";

        public const string UPDATE_DATAVALIDATION = @"UPDATE 
                            MDATAVALIDATION SET
                            DATAVALIDATIONCODE = @DataValidationCode,
                            DATAVALIDATIONNAME = @DataValidationName,
                            SECTION = @DataValidationSection,
                            MODULEID = @ModuleId,
                            DETAILS = @DataValidationDetails,
                            QUERY = @DataValidationQuery,
                            SOLUTION = @DataValidationSolution,
                            SORTORDER = @DataValidationSortOrder,
                            STATUS = @DataValidationStatus,
                            VERSION = @DataValidationVersion,
                            MODIFIEDBYID = @DataValidationModifiedById,
                            MODIFIEDON = @DataValidationModifiedOn
                            WHERE DATAVALIDATIONID = @DataValidationId;
                            ";

        public const string DELETE_DATAVALIDATION = @"DELETE 
                            FROM MDATAVALIDATION
                            WHERE DATAVALIDATIONID = @datavalidationid;
                            ";

        public const string GET_SELECTLIST_DATAVALIDATION = @"
                            WITH PagedDataValidation AS (
                            SELECT 
                            DV.DATAVALIDATIONID AS Id,
                            DV.DATAVALIDATIONCODE AS Code,
                            DV.DATAVALIDATIONNAME AS Name,
                            ROW_NUMBER() OVER (ORDER BY DV.DATAVALIDATIONID) AS RowNum
                            FROM MDATAVALIDATION DV
                            )
                            SELECT
                            Id,
                            Code,
                            Name
                            FROM PagedDataValidation
                            WHERE 
                            (@firstnumber = -1 AND @maxresult = -1)
                            OR
                            (RowNum BETWEEN @firstnumber AND @maxresult);
                            ";
    }
}