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

namespace LogisticsDAL.Query.Route
{
    public class RouteQB
    {
        public const string GET_SELECTLIST_ROUTE = @"WITH PagedRoute AS
                                                (SELECT
                                                        R.ROUTEID            AS Id,
                                                        R.ROUTECODE          AS RouteCode,
                                                        R.ROUTENAME          AS RouteName,
                                                        R.SALESPERSONID      AS SalesPersonId,
                                                        E.EMPLOYEECODE      AS SalesPersonCode,
                                                        E.EMPLOYEENAME   AS SalesPersonName,
                                                        ROW_NUMBER() OVER (ORDER BY R.ROUTENAME) AS RowNum
                                                    FROM MROUTE R
                                                    LEFT JOIN MEMPLOYEE E ON E.EMPLOYEEID = R.SALESPERSONID
                                                    WHERE R.STATUS = 1
                                                )
                                                SELECT
                                                    Id,
                                                    RouteCode,
                                                    RouteName,
                                                    SalesPersonId,
                                                    SalesPersonCode,
                                                    SalesPersonName
                                                FROM PagedRoute
                                               WHERE 
                                                    (@firstnumber = -1 AND @maxresult = -1)
                                                    OR (RowNum BETWEEN @firstnumber AND @maxresult);";
    }
}