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

namespace FrameworkDAL.Query.Lov
{
    public class LovQB
    {
        public const string GET_LOV = @"SELECT
                                        L.LOVID               AS LovId,
                                        L.LOVCODE             AS LovCode,
                                        L.LOVNAME             AS LovName,
                                        L.LOVTYPEID           AS LovTypeId,
                                        LT.LOVTYPECODE        AS LovTypeCode,
                                        LT.LOVTYPENAME        AS LovTypeName,
                                        LT.SCOREREQUIRED      AS LovTypeScoreRequired,
                                        L.SCORE               AS LovScore,
                                        L.CREATEDBYID         AS LovCreatedById,
                                        L.CREATEDON           AS LovCreatedOn,
                                        U.USERNAME            AS LovCreatedByName,
                                        L.MODIFIEDBYID        AS LovModifiedById,
                                        L.MODIFIEDON          AS LovModifiedOn,
                                        MU.USERNAME           AS LovModifiedByName,
                                        L.SORTORDER           AS LovSortOrder,
                                        L.STATUS              AS LovStatus,
                                        L.VERSION             AS LovVersion,
                                        L.SOURCETYPE          AS LovSourceType,
                                        L.PARENTID            AS ParentId,
                                        P.LOVCODE             AS ParentCode,
                                        P.LOVNAME             AS ParentName
                                    FROM MLOV L
                                    LEFT JOIN MLOVTYPE LT ON L.LOVTYPEID = LT.LOVTYPEID
                                    LEFT JOIN MUSER U ON L.CREATEDBYID = U.USERID
                                    LEFT JOIN MUSER MU ON L.MODIFIEDBYID = MU.USERID
                                    LEFT JOIN MLOV P ON L.PARENTID = P.LOVID
                                    WHERE L.LOVID = @lovid;";

        public const string GET_SELECTLIST_LOV = @"SELECT
                                                    L.LOVID AS Id,
                                                    L.LOVCODE AS Code,
                                                    L.LOVNAME AS Name,
                                                    L.SCORE AS Score
                                                FROM MLOV L
                                                INNER JOIN MLOVTYPE LT
                                                    ON LT.LOVTYPEID = L.LOVTYPEID WHERE 1 = 1";
        public const string SAVE_LOV = @"INSERT INTO MLOV (
                                            LOVID,
                                            LOVCODE,
                                            LOVNAME,
                                            LOVTYPEID,
                                            SCORE,
                                            SORTORDER,
                                            STATUS,
                                            VERSION,
                                            PARENTID,
                                            CREATEDBYID,
                                            CREATEDON,
                                            MODIFIEDBYID,
                                            MODIFIEDON
                                        )
                                        VALUES (
                                            @LovId,
                                            @LovCode,
                                            @LovName,
                                            @LovTypeId,
                                            @LovScore,
                                            @LovSortOrder,
                                            @LovStatus,
                                            @LovVersion,
                                            @ParentId,
                                            @LovCreatedById,
                                            @LovCreatedOn,
                                            @LovModifiedById,
                                            @LovModifiedOn
                                        );";

        public const string UPDATE_LOV = @"UPDATE MLOV
                                            SET
                                                LOVCODE = @LovCode,
                                                LOVNAME = @LovName,
                                                LOVTYPEID = @LovTypeId,
                                                SCORE = @LovScore,
                                                SORTORDER = @LovSortOrder,
                                                STATUS = @LovStatus,
                                                VERSION = @LovVersion,
                                                PARENTID = @ParentId,
                                                MODIFIEDBYID = @LovModifiedById,
                                                MODIFIEDON = @LovModifiedOn
                                            WHERE LOVID = @LovId;";

        public const string DELETE_LOV = @"DELETE FROM MLOV
                                        WHERE LOVID = @lovid;";
    }
}