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

namespace FrameworkDAL.Query.Region
{
    public static class RegionQB
    {
        public const string GET_REGION = @"SELECT
                                        A.REGIONID AS RegionId,
                                        A.REGIONCODE AS RegionCode,
                                        A.REGIONNAME AS RegionName,

                                        A.REGIONTYPEID AS RegionTypeId,
                                        B.REGIONTYPECODE AS RegionTypeCode,
                                        B.REGIONTYPENAME AS RegionTypeName,

                                        A.PARENTREGIONID AS ParentRegionId,
                                        C.REGIONCODE AS ParentRegionCode,
                                        C.REGIONNAME AS ParentRegionName,
                                        A.PARENTREGIONIDS AS RegionParentRegionIds,
                                        A.LEVELS AS RegionLevels,

                                        A.CREATEDBYID AS RegionCreatedById,
                                        D.EMPLOYEENAME AS RegionCreatedByName,
                                        A.CREATEDON AS RegionCreatedOn,

                                        A.MODIFIEDBYID AS RegionModifiedById,
                                        E.EMPLOYEENAME AS RegionModifiedByName,
                                        A.MODIFIEDON AS RegionModifiedOn,

                                        A.SORTORDER AS RegionSortOrder,
                                        A.STATUS AS RegionStatus,
                                        A.VERSION AS RegionVersion
                                    FROM MREGION A
                                    LEFT JOIN MREGIONTYPE B ON A.REGIONTYPEID = B.REGIONTYPEID
                                    LEFT JOIN MREGION C ON A.PARENTREGIONID = C.REGIONID
                                    LEFT JOIN MEMPLOYEE D ON A.CREATEDBYID = D.EMPLOYEEID
                                    LEFT JOIN MEMPLOYEE E ON A.MODIFIEDBYID = E.EMPLOYEEID
                                    WHERE
                                    A.REGIONID = @regionid;";

        public const string GET_SELECTLIST_REGION = @"select
                                            REGIONID AS Id,
                                            REGIONCODE AS Code,
                                            REGIONNAME AS Name 
                                            from MREGION";


        public const string SAVE_REGION = @"INSERT INTO MREGION (
                                                REGIONID,
                                                REGIONCODE,
                                                REGIONNAME,
                                                REGIONTYPEID,
                                                PARENTREGIONID,
                                                PARENTREGIONIDS,
                                                LEVELS,
                                                SORTORDER,
                                                STATUS,
                                                VERSION,
                                                CREATEDBYID,
                                                CREATEDON,
                                                MODIFIEDBYID,
                                                MODIFIEDON,
                                                SOURCETYPE
                                            )
                                            VALUES (
                                                @RegionId,
                                                @RegionCode,
                                                @RegionName,
                                                @RegionTypeId,
                                                @ParentRegionId,
                                                @RegionParentRegionIds,
                                                @RegionLevels,
                                                @RegionSortOrder,
                                                @RegionStatus,
                                                @RegionVersion,
                                                @RegionCreatedById,
                                                @RegionCreatedOn,
                                                @RegionModifiedById,
                                                @RegionModifiedOn,
                                                @SourceType
                                            );";

        public const string UPDATE_REGION = @"UPDATE MREGION
                                            SET
                                                REGIONCODE = @RegionCode,
                                                REGIONNAME = @RegionName,
                                                REGIONTYPEID = @RegionTypeId,
                                                PARENTREGIONID = @ParentRegionId,
                                                PARENTREGIONIDS = @RegionParentRegionIds,
                                                LEVELS = @RegionLevels,
                                                SORTORDER = @RegionSortOrder,
                                                STATUS = @RegionStatus,
                                                VERSION = @RegionVersion,
                                                MODIFIEDBYID = @RegionModifiedById,
                                                MODIFIEDON = @RegionModifiedOn,
                                                SOURCETYPE = @SourceType
                                            WHERE
                                                REGIONID = @RegionId;";

        public const string DELETE_REGION = @"DELETE FROM MREGION
                                            WHERE REGIONID = @regionid";

    }
}