namespace GB5Shared.IceMap
{
    public static class IceMapQB
    {
        public const string GET_ICEMAP = @"SELECT
                            I.ICEMAPID AS IceMapId,
                            I.ICEMAPCODE AS IceMapCode,
                            I.ICEMAPNAME AS IceMapName,
                            I.ICESOURCETYPE AS IceMapIceSourceType,
                            I.SOURCEFILENAME AS IceMapSourceFileName,
                            I.MAPTOTYPE AS IceMapMapToType,
                            I.ENTITYID AS IceMapEntityId,
                            E.ENTITYCODE AS IceMapEntityCode,
                            E.ENTITYNAME AS IceMapEntityName,
                            E.POCOTODTOLINKID AS IceMapEntityPocoToDtoLinkId,
                            I.KEYFIELD AS IceMapKeyField,
                            I.SOURCESHEETNAME AS IceMapSourceSheetName,
                            I.REMARKS AS IceMapRemarks,
                            I.SOURCETYPE AS IceMapSourceType,
                            I.SORTORDER AS IceMapSortOrder,
                            I.STATUS AS IceMapStatus,
                            I.VERSION AS IceMapVersion,
                            I.CREATEDBYID AS IceMapCreatedById,
                            I.CREATEDON AS IceMapCreatedOn,
                            I.MODIFIEDBYID AS IceMapModifiedById,
                            I.MODIFIEDON AS IceMapModifiedOn,
                            I.NOOFHEADERROWS AS IceMapNoOfHeaderRows,
                            I.NOOFHEADERCOLUMN AS IceMapNoOfHeaderColumn,
                            I.MAPTYPE AS IceMapMapType,
                            I.TEMPLATEFILEID AS TemplateFileId,
                            F.FILENAME AS TemplateFileName,
                            F.FILELOCATION AS TemplateFileLocation,

                            ID.MAPDETAILID AS IceMapDetailsId,
                            ID.ICEMAPID AS IceMapId,
                            I.ICEMAPCODE AS IceMapCode,
                            I.ICEMAPNAME AS IceMapName,
                            ID.SLNO AS IceMapDetailsSlNo,
                            ID.VALUETYPE AS IceMapDetailsValueType,
                            ID.SOURCEFIELDNAME AS IceMapDetailsSourceFieldName,
                            ID.ISFILTERFIELD AS IceMapDetailsIsFilterField,
                            ID.DESTINATIONMEMBERID AS DestinationMemberId,
                            EM.ENTITYID AS DestinationMemberEntityId,
                            EM.MEMBERNAME AS DestinationMemberMemberName,
                            EM.DATATYPE AS DestinationMemberDataType,
                            ID.ENTITYID AS EntityId,
                            EE.ENTITYCODE AS EntityCode,
                            EE.ENTITYNAME AS EntityName,
                            EE.POCOTODTOLINKID AS EntityPocoToDtoLinkId,
                            ID.ADDONFIELDNAME AS IceMapDetailsAddonFields,
                            ID.GCMTYPEID AS GcmTypeId,
                            G.GCMTYPECODE AS GcmTypeCode,
                            G.GCMTYPEDESCRIPTION AS GcmTypeDescription,
                            ID.ISOPTIONAL AS IceMapDetailsIsOptional,
                            ID.OPTIONALFIELDVALUE AS IceMapDetailsOptionalFiledValue,
                            ID.ISUNIQUEKEY AS IceMapDetailsIsUniqueKey
                            FROM MICEMAP I
                            LEFT JOIN MFILE F ON I.TEMPLATEFILEID = F.FILEID
                            LEFT JOIN MENTITY E ON I.ENTITYID = E.ENTITYID
                            LEFT JOIN MICEMAPDETAILS ID ON I.ICEMAPID = ID.ICEMAPID
                            LEFT JOIN MENTITY EE ON ID.ENTITYID = EE.ENTITYID
                            LEFT JOIN MGCMTYPE G ON ID.GCMTYPEID = G.GCMTYPEID
                            LEFT JOIN MENTITYMEMBERS EM ON ID.DESTINATIONMEMBERID = EM.ENTITYMEMBERID
                            WHERE I.ICEMAPID = @icemapid;
                            ";

        public const string SAVE_ICEMAP = @"INSERT
                            INTO MICEMAP (
                                 ICEMAPID,
                                 ICEMAPCODE,
                                 ICEMAPNAME,
                                 ICESOURCETYPE,
                                 SOURCEFILENAME,
                                 MAPTOTYPE,
                                 ENTITYID,
                                 KEYFIELD,
                                 SOURCESHEETNAME,
                                 REMARKS,
                                 SOURCETYPE,
                                 SORTORDER,
                                 STATUS,
                                 VERSION,
                                 CREATEDBYID,
                                 CREATEDON,
                                 MODIFIEDBYID,
                                 MODIFIEDON,
                                 NOOFHEADERROWS,
                                 NOOFHEADERCOLUMN,
                                 MAPTYPE,
                                 TEMPLATEFILEID
                            ) OUTPUT INSERTED.IceMapId VALUES (
                                 @IceMapId,
                                 @IceMapCode,
                                 @IceMapName,
                                 @IceMapIceSourceType,
                                 @IceMapSourceFileName,
                                 @IceMapMapToType,
                                 @IceMapEntityId,
                                 @IceMapKeyField,
                                 @IceMapSourceSheetName,
                                 @IceMapRemarks,
                                 @IceMapSourceType,
                                 @IceMapSortOrder,
                                 @IceMapStatus,
                                 @IceMapVersion,
                                 @IceMapCreatedById,
                                 @IceMapCreatedOn,
                                 @IceMapModifiedById,
                                 @IceMapModifiedOn,
                                 @IceMapNoOfHeaderRows,
                                 @IceMapNoOfHeaderColumn,
                                 @IceMapMapType,
                                 @TemplateFileId
                            );
                            ";

        public const string SAVE_ICEMAPDETAILS = @"INSERT
                            INTO MICEMAPDETAILS (
                                 MAPDETAILID,
                                 ICEMAPID,
                                 SLNO,
                                 VALUETYPE,
                                 SOURCEFIELDNAME,
                                 ISFILTERFIELD,
                                 DESTINATIONMEMBERID,
                                 ENTITYID,
                                 ADDONFIELDNAME,
                                 GCMTYPEID,
                                 ISOPTIONAL,
                                 OPTIONALFIELDVALUE,
                                 ISUNIQUEKEY
                            ) OUTPUT INSERTED.MapDetailId VALUES (
                                 @IceMapDetailsId,
                                 @IceMapId,
                                 @IceMapDetailsSlNo,
                                 @IceMapDetailsValueType,
                                 @IceMapDetailsSourceFieldName,
                                 @IceMapDetailsIsFilterField,
                                 @DestinationMemberId,
                                 @EntityId,
                                 @IceMapDetailsAddonFields,
                                 @GcmTypeId,
                                 @IceMapDetailsIsOptional,
                                 @IceMapDetailsOptionalFiledValue,
                                 @IceMapDetailsIsUniqueKey
                            );
                            ";

        public const string UPDATE_ICEMAP = @"UPDATE
                            MICEMAP SET
                            ICEMAPCODE = @IceMapCode,
                            ICEMAPNAME = @IceMapName,
                            ICESOURCETYPE = @IceMapIceSourceType,
                            SOURCEFILENAME = @IceMapSourceFileName,
                            MAPTOTYPE = @IceMapMapToType,
                            ENTITYID = @IceMapEntityId,
                            KEYFIELD = @IceMapKeyField,
                            SOURCESHEETNAME = @IceMapSourceSheetName,
                            REMARKS = @IceMapRemarks,
                            SOURCETYPE = @IceMapSourceType,
                            SORTORDER = @IceMapSortOrder,
                            STATUS = @IceMapStatus,
                            VERSION = @IceMapVersion,
                            MODIFIEDBYID = @IceMapModifiedById,
                            MODIFIEDON = @IceMapModifiedOn,
                            NOOFHEADERROWS = @IceMapNoOfHeaderRows,
                            NOOFHEADERCOLUMN = @IceMapNoOfHeaderColumn,
                            MAPTYPE = @IceMapMapType,
                            TEMPLATEFILEID = @TemplateFileId
                            WHERE IceMapId = @IceMapId;
                            ";

        public const string UPDATE_ICEMAPDETAILS = @"UPDATE
                            MICEMAPDETAILS SET
                            ICEMAPID = @IceMapId,
                            SLNO = @IceMapDetailsSlNo,
                            VALUETYPE = @IceMapDetailsValueType,
                            SOURCEFIELDNAME = @IceMapDetailsSourceFieldName,
                            ISFILTERFIELD = @IceMapDetailsIsFilterField,
                            DESTINATIONMEMBERID = @DestinationMemberId,
                            ENTITYID = @EntityId,
                            ADDONFIELDNAME = @IceMapDetailsAddonFields,
                            GCMTYPEID = @GcmTypeId,
                            ISOPTIONAL = @IceMapDetailsIsOptional,
                            OPTIONALFIELDVALUE = @IceMapDetailsOptionalFiledValue,
                            ISUNIQUEKEY = @IceMapDetailsIsUniqueKey
                            WHERE MapDetailId = @IceMapDetailsId AND IceMapId = @IceMapId;
                            ";

        public const string DELETE_ICEMAP = @"DELETE
                            FROM MICEMAP
                            WHERE ICEMAPID = @icemapid;
                            ";

        public const string DELETE_ICEMAPDETAILS = @"DELETE
                            FROM MICEMAPDETAILS
                            WHERE ICEMAPID = @icemapid;";

        public const string GET_MAX_ICEMAPDETAILSID = @"SELECT
                            ISNULL(MIN(MAPDETAILID), 0)
                            FROM MICEMAPDETAILS
                            WHERE ICEMAPID = @icemapid;";

        public const string GET_SELECTLIST_ICEMAP = @"SELECT
                            ICEMAPID AS Id,
                            ICEMAPCODE AS Code,
                            ICEMAPNAME AS Name,
                            SORTORDER AS SortOrder
                            FROM MICEMAP
                            ";
    }
}