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

namespace FrameworkDAL.Query.WebForm
{
    public static class WebFormQB
    {
        public const string GET_WEB_FORM = @"SELECT
                                            wf.WEBFORMID           AS WebFormId,
                                            wf.WEBFORMNAME         AS WebFormName,
                                            wf.WEBFORMURL          AS WebFormURL,
                                            wf.TEMPLATE            AS WebFormTemplate,
                                            wf.WEBFORMSECONDURL    AS WebFormSecondURL,
                                            wf.OBJECTMODELIDS      AS WebFormObjectModelIds,
                                            wf.MODULEID            AS ModuleId,
                                            m.MODULECODE           AS ModuleCode,
                                            m.MODULENAME           AS ModuleName,
                                            wf.OBJECTMODELID       AS ObjectModelId,
                                            f.FILENAME             AS ObjectModelName,
                                            f.FILELOCATION         AS ObjectModelLocation,
                                            wf.CREATEDBYID         AS WebFormCreatedById,
                                            wf.CREATEDON           AS WebFormCreatedOn,
                                            wf.MODIFIEDBYID        AS WebFormModifiedById,
                                            wf.MODIFIEDON          AS WebFormModifiedOn,
                                            cb.USERNAME            AS WebFormCreatedByName,
                                            mb.USERNAME            AS WebFormModifiedByName,
                                            wf.SORTORDER           AS WebFormSortOrder,
                                            wf.STATUS              AS WebFormStatus,
                                            wf.VERSION             AS WebFormVersion,
                                            wf.SOURCETYPE          AS WebFormSourceType
                                        FROM MWEBFORM wf
                                        LEFT JOIN MMODULE m ON wf.MODULEID = m.MODULEID
                                        LEFT JOIN MFILE f ON wf.OBJECTMODELID = f.FILEID
                                        LEFT JOIN MUSER cb ON wf.CREATEDBYID = cb.USERID
                                        LEFT JOIN MUSER mb ON wf.MODIFIEDBYID = mb.USERID
                                        WHERE wf.WEBFORMID = @webformid;";

        public const string SAVE_WEB_FORM = @"INSERT INTO MWEBFORM
    (
        WEBFORMID,
        WEBFORMNAME,
        WEBFORMURL,
        TEMPLATE,
        MODULEID,
        SORTORDER,
        STATUS,
        VERSION,
        SOURCETYPE,
        CREATEDBYID,
        CREATEDON,
        MODIFIEDBYID,
        MODIFIEDON,
        WEBFORMSECONDURL,
        OBJECTMODELID,
        OBJECTMODELIDS
    )
    VALUES
    (
        @WebFormId,
        @WebFormName,
        @WebFormURL,
        @WebFormTemplate,
        @ModuleId,
        @WebFormSortOrder,
        @WebFormStatus,
        @WebFormVersion,
        @WebFormSourceType,
        @WebFormCreatedById,
        @WebFormCreatedOn,
        @WebFormModifiedById,
        @WebFormModifiedOn,
        @WebFormSecondURL,
        @ObjectModelId,
        @WebFormObjectModelIds
    );";

        public const string UPDATE_WEB_FORM = @"UPDATE MWEBFORM
SET
    WEBFORMNAME = @WebFormName,
    WEBFORMURL = @WebFormURL,
    TEMPLATE = @WebFormTemplate,
    MODULEID = @ModuleId,
    SORTORDER = @WebFormSortOrder,
    STATUS = @WebFormStatus,
    VERSION = @WebFormVersion,
    SOURCETYPE = @WebFormSourceType,
    MODIFIEDBYID = @WebFormModifiedById,
    MODIFIEDON = @WebFormModifiedOn,
    WEBFORMSECONDURL = @WebFormSecondURL,
    OBJECTMODELID = @ObjectModelId,
    OBJECTMODELIDS = ISNULL(@WebFormObjectModelIds, '-1')
WHERE
    WEBFORMID = @WebFormId;";

        public const string GET_SELECTLIST_WEB_FORM = @"WITH WebForm AS (
                                            SELECT
                                                A.WEBFORMID     AS Id, 
                                                A.WEBFORMNAME   AS Name,
                                                ROW_NUMBER() OVER (ORDER BY A.WEBFORMID) AS RowNum
                                            FROM MWEBFORM A
                                        )
                                        SELECT Id, Name
                                        FROM WebForm
                                        WHERE 
                                             (@firstnumber = -1 AND @maxresult = -1)
                                             OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string DELETE_WEB_FORM = @"DELETE FROM MWEBFORM
                                            WHERE WEBFORMID = @webformid;";
    }
}