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

namespace MMDAL.Query.Terms
{
    public static class TermsQB
    {

        public const string GET_SELECTLIST_TERMS = @"SELECT
                                                   T.TERMSID           AS Id,
                                                   T.TERMSSECTION         AS Section,
                                                   T.TERMSDESCRIPTION  AS Description,
												   T.TERMSTYPE  AS TermsType
                                               FROM MTERMS T
                                               WHERE T.STATUS = 1
                                                  
                                                   AND (@section        = '' OR T.TERMSSECTION LIKE '%' + @section        + '%')
                                                   AND (@description = '' OR T.TERMSDESCRIPTION LIKE '%' + @description + '%');";



       



        public const string SAVE_TERMS = @"INSERT INTO MTERMS (
                                            TERMSID,
                                            TERMSSECTION,
                                            TERMSDESCRIPTION, 
                                            TERMSTYPE,											
                                            SORTORDER,
                                            STATUS,
                                            VERSION,
                                            CREATEDBYID,
                                            CREATEDON,
                                            MODIFIEDBYID,
                                            MODIFIEDON,
                                            SOURCETYPE,                                           
                                            TENANTID
                                            
                                        )
                                        VALUES (
                                            @TermsId,
                                            @TermsSection,
                                            @TermsDescription,
                                            @TermsType,
                                            @TermsSortOrder,
                                            @TermsStatus,
                                            @TermsVersion,
                                            @TermsCreatedById,
                                            @TermsCreatedOn,
                                            @TermsModifiedById,
                                            @TermsModifiedOn,
                                            @TermsSourceType,                                           
                                            @TenantId
                                            
                                        );";



        public const string UPDATE_TERMS = @"UPDATE MTERMS
                                     SET
                                         TERMSSECTION = @TermsSection,
                                         TERMSDESCRIPTION = @TermsDescription,
                                         TERMSTYPE = @TermsType,                                       
                                         SORTORDER = @TermsSortOrder,
                                         STATUS = @TermsStatus,
                                         VERSION = @TermsVersion,
										 CREATEDBYID= @TermsCreatedById,
										 CREATEDON= @TermsCreatedOn,
                                         MODIFIEDBYID = @TermsModifiedById,
                                         MODIFIEDON = @TermsModifiedOn,
                                         SOURCETYPE = @TermsSourceType, 
                                         TENANTID = @TenantId
                                         WHERE TERMSID = @TermsId;";





        public const string GET_TERMS = @"SELECT
                                     T.TERMSID AS TermsId,
                                     T.TERMSSECTION AS TermsSection,
                                     T.TERMSDESCRIPTION AS TermsDescription,
                                     T.TERMSTYPE AS TermsType,                                  
                                     T.SORTORDER AS TermsSortOrder,
                                     T.STATUS AS TermsStatus,
                                     T.VERSION AS TermsVersion,
                                     T.SOURCETYPE AS TermsSourceType,                                    
                                     T.CREATEDBYID AS TermsCreatedById,
                                     UC.USERNAME AS TermsCreatedByName,
                                     T.CREATEDON AS TermsCreatedOn,
                                     T.MODIFIEDBYID AS TermsModifiedById,
                                     UM.USERNAME AS TermsModifiedByName,
                                     T.MODIFIEDON AS TermsModifiedOn,
                                     T.TENANTID AS TenantId
                                 FROM MTERMS T
                                 LEFT JOIN MUSER UC ON T.CREATEDBYID = UC.USERID
                                 LEFT JOIN MUSER UM ON T.MODIFIEDBYID = UM.USERID
                                 WHERE T.TERMSID = @TermsId;";


        public const string DELETE_TERMS = @"DELETE FROM MTERMS 
                                            WHERE TERMSID = @TermsId;";

    }
}