elninjagaiden / dapper.datarepositories Goto Github PK

`public Task<IEnumerable<TEntity>> GetAllPagedAsync(string customSql, int pageNumber, int itemsPerPage, string orderby, object parameters = null, bool buffered = true)
        {
            string pagedListSql = @$"select * from ({customSql}) pagetable ORDER BY {orderby} OFFSET { (pageNumber - 1) * itemsPerPage  } ROWS FETCH NEXT { itemsPerPage} ROWS only ";
            return Connection.QueryAsync<TEntity>(pagedListSql, param: parameters, transaction: ActiveTransaction, commandTimeout: Timeout);
        }`

var sb = new SqlBuilder();
            var builder = sb.AddTemplate(@"select * from (select ROW_NUMBER()over(partition by c.ProcessId order by c.Process_ProcessCode desc) rowId,c.* from(SELECT 
                            ptd.*,
                            p.ProcessCode as Process_ProcessCode,
                            p.ProcessName as Process_ProcessName,
                            p.ProcessDesc as Process_ProcessDesc,
                            p.ProcessNameEnglish,p.ProcessDescEnglish, 
                            p.CarType as Process_CarType,
                            ct.BaseDataValue as Process_CarTypeValue,
                            gv.BaseDataValue as Process_GradeVale,
                            p.SAM as Process_SAM,
                            p.Wages as Process_UnitPrice,
                            p.SingleHourOutput as Process_HourlyOutput,
                            p.CodeType as Process_CodeType,
                            pdt.BaseDataValue as ProductValue,
                            p.grade,p.departmentCode,
                            pat.BaseDataValue as PartValue,dc.BaseDataValue as DepartmentCodeName,p.SourceStyleNumber,ft.FtyCode,ft.FactoryName,p.Description     
                            FROM ProcessTemplateDetail ptd 
                            LEFT JOIN Process p on CONVERT(VARCHAR(50),ptd.ProcessId) = p.Id   
                            left join [dbo].[BaseData] ct on CONVERT(VARCHAR(50),ct.id) = p.cartype   
                            left join [dbo].[BaseData] gv on CONVERT(VARCHAR(50),gv.id) = p.Grade   
                            left join[dbo].[BaseData] pdt on CONVERT(VARCHAR(50),pdt.id) = p.ProductType   
                            left join[dbo].[BaseData] pat on CONVERT(VARCHAR(50),pat.id) = p.PartType   
                            left join[dbo].[BaseData] dc on CONVERT(VARCHAR(50),dc.id) = p.DepartmentCode
                            left join Factory ft on ft.id = p.SourceFactory
                            /**where**/ ) as c) 
                            as AuctionRecords ");

            if (!string.IsNullOrWhiteSpace(query.MenuName))
                sb.Where("ProcessCode like CONCAT('%',@MenuName,'%')", new { query.MenuName });
            if (!string.IsNullOrWhiteSpace(query.Url))
                sb.Where("Url like CONCAT('%',@Url,'%')", new { query.Url });

            var items = await _repository.GetAllPagedAsync(builder.RawSql, query.pageIndex, query.PageSize, query.OrderBy, builder.Parameters);