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

namespace MMDAL.Query.MeasureUnit
{
    public class MeasureUnitQB
    {

        public const string GET_MEASUREUNIT = @"SELECT
                                                    MU.MEASUREUNITID        AS MeasureUnitId,
                                                    MU.UNITNAME             AS MeasureUnitName,
                                                    MU.UNITTYPE             AS MeasureUnitType,
                                                    MU.MULTIPLIERTOBASE     AS MultiplierToBase,
                                                    MU.SORTORDER            AS SortOrder,
                                                    MU.STATUS               AS MeasureUnitStatus,
                                                    MU.VERSION              AS MeasureUnitVersion,
                                                    MU.SOURCETYPE           AS MeasureUnitSourceType,
                                                    MU.CREATEDBYID          AS MeasureUnitCreatedById,
                                                    MU.CREATEDON            AS MeasureUnitCreatedOn,
                                                    MU.MODIFIEDBYID         AS MeasureUnitModifiedById,
                                                    MU.MODIFIEDON           AS MeasureUnitModifiedOn
                                                FROM MMEASUREUNIT MU
                                                LEFT JOIN MUSER CU ON CU.USERID = MU.CREATEDBYID
                                                LEFT JOIN MUSER MUU ON MUU.USERID = MU.MODIFIEDBYID
                                                WHERE MU.MEASUREUNITID = @measureunitid;";

        public const string SAVE_MEASUREUNIT = @"INSERT INTO MMEASUREUNIT
                                                (
                                                    MEASUREUNITID,
                                                    UNITNAME,
                                                    UNITTYPE,
                                                    MULTIPLIERTOBASE,
                                                    SORTORDER,
                                                    STATUS,
                                                    VERSION,
                                                    SOURCETYPE,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON
                                                )
                                                VALUES
                                                (
                                                    @MeasureUnitId, 
                                                    @MeasureUnitName,
                                                    @MeasureUnitType,
                                                    @MultiplierToBase,
                                                    @SortOrder,
                                                    @MeasureUnitStatus,
                                                    @MeasureUnitVersion,
                                                    @MeasureUnitSourceType,
                                                    @MeasureUnitCreatedById,
                                                    @MeasureUnitCreatedOn,
                                                    @MeasureUnitModifiedById,
                                                    @MeasureUnitModifiedOn
                                                );";


        public const string UPDATE_MEASUREUNIT = @"UPDATE MMEASUREUNIT
                                                SET
                                                    UNITNAME         = @MeasureUnitName,
                                                    UNITTYPE         = @MeasureUnitType,
                                                    MULTIPLIERTOBASE = @MultiplierToBase,
                                                    SORTORDER        = @SortOrder,
                                                    STATUS           = @MeasureUnitStatus,
                                                    VERSION          = @MeasureUnitVersion,
                                                    SOURCETYPE       = @MeasureUnitSourceType,
                                                    MODIFIEDBYID     = @MeasureUnitModifiedById,
                                                    MODIFIEDON       = @MeasureUnitModifiedOn
                                                WHERE MEASUREUNITID = @MeasureUnitId;";

        public const string DELETE_MEASUREUNIT = @"DELETE FROM MMEASUREUNIT 
                                                    WHERE MEASUREUNITID = @measureunitid;";


        public const string GET_SELECTLIST_MEASUREUNIT = @"WITH PagedMeasureUnit AS (
                                                        SELECT
                                                            MU.MEASUREUNITID           AS Id,
                                                            MU.UNITNAME                AS Name,
                                                            MU.UNITTYPE                AS Type,
                                                            ROW_NUMBER() OVER (ORDER BY MU.MEASUREUNITID) AS RowNum
                                                        FROM 
                                                            MMEASUREUNIT MU
                                                        WHERE 
                                                            MU.STATUS = 1
                                                        )
                                                    SELECT
                                                        Id,
                                                        Name,
                                                        Type
                                                    FROM PagedMeasureUnit
                                                   WHERE 
                                                    (@firstnumber = -1 AND @maxresult = -1)
                                                    OR (RowNum BETWEEN @firstnumber AND @maxresult);";
    }
}