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

namespace FrameworkDAL.Query.WebServiceSetting
{
    public static class WebServiceSettingQB
    {
        public const string GET_SELECTLIST_WEBSERVICESETTING = @"
SELECT DISTINCT 
    A.WEBSERVICESETTINGID AS WebServiceSettingId,
    B.WEBSERVICEID        AS WebServiceId,
    B.WEBSERVICENAME      AS WebServiceName,
    C.MENUID              AS MenuId,
    C.MENUCODE            AS MenuCode,
    C.MENUNAME            AS MenuName,
    D.REPORTID            AS ReportId,
    D.REPORTCODE          AS ReportCode,
    D.REPORTNAME          AS ReportName
FROM 
    MWEBSERVICESETTING A
    INNER JOIN MWEBSERVICE B ON A.WEBSERVICEID = B.WEBSERVICEID
    LEFT JOIN MMENU C ON A.MENUID = C.MENUID
    LEFT JOIN MREPORT D ON A.REPORTID = D.REPORTID
WHERE 
    A.STATUS = 1
ORDER BY 
    B.WEBSERVICENAME, C.MENUNAME, D.REPORTNAME;
";

        public const string GET_WEBSERVICESETTING = @"
SELECT 
    -- =======================
    -- Parent : MWEBSERVICESETTING
    -- =======================
    WS.WEBSERVICESETTINGID                  AS WebServiceSettingId,
    WS.WEBSERVICEID                         AS WebServiceId,
    WSVC.WEBSERVICENAME                     AS WebServiceName,
    WS.SETTINGTYPE                          AS WebServiceSettingSettingType,
    WS.REPORTID                             AS ReportId,
    R.REPORTNAME                            AS ReportName,
    WS.MENUID                               AS MenuId,
    M.MENUCODE                              AS MenuCode,
    M.MENUNAME                              AS MenuName,
    WS.SORTORDER                            AS WebServiceSettingSortOrder,
    WS.STATUS                               AS WebServiceSettingStatus,
    WS.VERSION                              AS WebServiceSettingVersion,
    WS.SOURCETYPE                           AS WebServiceSettingSourceType,
    WS.CREATEDBYID                          AS WebServiceSettingCreatedById,
    WS.CREATEDON                            AS WebServiceSettingCreatedOn,
    WS.MODIFIEDBYID                         AS WebServiceSettingModifiedById,
    WS.MODIFIEDON                           AS WebServiceSettingModifiedOn,

    -- =======================
    -- Child : MWEBSERVICESETTINGDETAIL
    -- (Dapper splits here at WebServiceSettingDetailId)
    -- =======================
    WSD.WEBSERVICESETTINGDETAILID           AS WebServiceSettingDetailId,
    WSD.WEBSERVICESETTINGID                 AS ServiceSettingId,
    WSD.SLNO                                AS WebServiceSettingDetailSlno,
    WSD.ISCOMPULSORY                        AS WebServiceSettingDetailIsCompulsory,
    WSD.ISHEADER                            AS WebServiceSettingDetailIsHeader,
    WSD.ISVISIBLE                           AS WebServiceSettingDetailIsVisible,
    WSD.ISCHANGEABLE                        AS WebServiceSettingDetailIsChangeable,
    WSD.OPERATIONTYPE                       AS WebServiceSettingDetailOperationType,
    WSD.CRITERIAFIELDVALUE                  AS WebServiceSettingDetailCriteriaFieldValue,
    WSD.DISPLAYNAME                         AS WebServiceSettingDetailDisplayName,
    WSD.CRITERIAJOIN                        AS WebServiceSettingDetailCriteriaJoin,
    WSD.WEBSERVICECRITERIAID                AS WebServiceCriteriaId,

    -- =======================
    -- Criteria Lookup : MWEBSERVICECRITERIA
    -- =======================
    WC.FIELDNAME                            AS WebServiceCriteriaFieldName,

    -- =======================
    -- Criteria Attribute : MCRITERIAATTRIBUTE
    -- =======================
    CA.CRITERIAATTRIBUTEID                  AS CriteriaAttributeId,
    CA.CRITERIAATTRIBUTENAME                AS CriteriaAttributeName,
    CA.CRITERIAATTRIBUTETYPE                AS CriteriaAttributeType,
    CA.CONTEXTVALUEFIELDNAME                AS CriteriaAttributeContextValueFieldName,

    -- =======================
    -- CriteriaFieldValueName  : Readable name from APPLICABLEVALUES in MCRITERIAATTRIBUTE
    -- CriteriaOriginalFieldValue : Original raw value stored in MWEBSERVICESETTINGDETAIL
    -- =======================
    CA.APPLICABLEVALUES                     AS WebServiceSettingDetailCriteriaFieldValueName,
    WSD.CRITERIAFIELDVALUE                  AS WebServiceSettingDetailCriteriaOriginalFieldValue

FROM MWEBSERVICESETTING WS
LEFT JOIN MWEBSERVICESETTINGDETAIL WSD 
       ON WS.WEBSERVICESETTINGID = WSD.WEBSERVICESETTINGID
LEFT JOIN MWEBSERVICE WSVC 
       ON WS.WEBSERVICEID = WSVC.WEBSERVICEID
LEFT JOIN MMENU M 
       ON WS.MENUID = M.MENUID
LEFT JOIN MREPORT R 
       ON WS.REPORTID = R.REPORTID
LEFT JOIN MWEBSERVICECRITERIA WC
       ON WSD.WEBSERVICECRITERIAID = WC.WEBSERVICECRITERIAID
LEFT JOIN MCRITERIAATTRIBUTE CA
       ON WC.CRITERIAATTRIBUTEID = CA.CRITERIAATTRIBUTEID
WHERE WS.WEBSERVICESETTINGID = @WebServiceSettingId
ORDER BY WSD.SLNO ASC;
";
        public const string SAVE_WEBSERVICESETTING = @"
INSERT INTO MWEBSERVICESETTING
(
    WEBSERVICESETTINGID,
    WEBSERVICEID,
    SETTINGTYPE,
    REPORTID,
    MENUID,
    SORTORDER,
    STATUS,
    VERSION,
    SOURCETYPE,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON
)
VALUES
(
    @WebServiceSettingId,
    @WebServiceId,
    @WebServiceSettingSettingType,
    @ReportId,
    @MenuId,
    @WebServiceSettingSortOrder,
    @WebServiceSettingStatus,
    @WebServiceSettingVersion,
    @WebServiceSettingSourceType,
    @WebServiceSettingCreatedById,
    @WebServiceSettingCreatedOn,
    @WebServiceSettingModifiedById,
    @WebServiceSettingModifiedOn
);";


        public const string UPDATE_WEBSERVICESETTING = @"
UPDATE MWEBSERVICESETTING
SET
    WEBSERVICEID = @WebServiceId,
    SETTINGTYPE = @WebServiceSettingSettingType,
    REPORTID = @ReportId,
    MENUID = @MenuId,
    SORTORDER = @WebServiceSettingSortOrder,
    STATUS = @WebServiceSettingStatus,
    VERSION = @WebServiceSettingVersion,
    SOURCETYPE = @WebServiceSettingSourceType,
    MODIFIEDBYID = @WebServiceSettingModifiedById,
    MODIFIEDON = @WebServiceSettingModifiedOn
WHERE WEBSERVICESETTINGID = @WebServiceSettingId;
";


        public const string DELETE_WEBSERVICESETTING = @"
                                                        DELETE FROM MWEBSERVICESETTING
                                                        WHERE WEBSERVICESETTINGID = @WebServiceSettingId;
                                                        ";

    }
}