Tabel SQL Server to Java Interface (READ)

HTML :

<c:forEach var="performance" items="${performanceMstFormatHeaderList}" >
<tr>
<td class="no-warp" style="text-align: center">${performance.ID}</td>
<td class="no-warp" style="text-align: center">${performance.format_id}</td>
<td class="no-warp">${performance.format_name}</td>
<td class="no-warp">${performance.remark}</td>
<td class="no-warp" style="text-align: center">
<a class="linkDetailFormatHeader1" href="#" id="tdViewDetailFormatHeader1" onclick="btnDetailFormatFinancial('${performance.format_id}')">${performance.countfinancial} Indikator</a>
</td>
<td class="no-warp" style="text-align: center">
<a class="linkDetailFormatHeader2" href="#" id="tdViewDetailFormatHeader2" onclick="btnDetailFormatNonFinancial('${performance.format_id}')">${performance.countnonfinancial} Indikator</a>
</td>
</tr>
</c:forEach>

Controller APP :

GenericDataGrid oFormatHeaderGrid = restTemplate.getForObject(getURLBaseFromTxt_API(currentSession) + "/performance/mstformatheaderallpaging?go=" + currentPage + "&fr=" + pageSize + "&in=" + encodedWhereCond + "&by=" + sortBy, GenericDataGrid.class);
oPerformanceMstFormatHeaderViews.addObject("performanceMstFormatHeaderList", oFormatHeaderGrid.getResultDataList());

Controller API :

@RequestMapping(value = {"/mstformatheaderallpaging"}, method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON_VALUE)
public ResponseEntity<GenericDataGrid> getMstFormatHeaderAllPagging(HttpServletRequest request, @RequestParam(required = false) String go,
@RequestParam(required = false) String fr,
@RequestParam(required = false) String in,
@RequestParam(required = false) String by) throws UnsupportedEncodingException {
LinkedHashMap<String, String> mappedParam = new LinkedHashMap<>();
PagingProperties pagingProperties = new PagingProperties();
pagingProperties.setPageCurrent(Integer.parseInt(StringUtils.isEmpty(go) ? "1" : go));
pagingProperties.setPageSize(Integer.parseInt(StringUtils.isEmpty(fr) ? "25" : fr));
pagingProperties.setWhereCond(in);
pagingProperties.setSortBy(by);
pagingProperties.setMappedCond(mappedParam);
GenericDataGrid listData = new GenericDataGrid();
try {
listData = performanceSvc.getPerformanceMstFormatHeaderGrid(pagingProperties);
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return new ResponseEntity(listData, HttpStatus.OK);
}

Svc :

public GenericDataGrid getPerformanceMstFormatHeaderGrid(PagingProperties pageProperties) throws SQLException {
String storedProcName = "spperformance_mst_format_header_getallpaging";
return genericDao.getAllDataPaging(storedProcName, pageProperties);
}

Stored Procedure :

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[spperformance_mst_format_header_getallpaging]
@intCurrentPage INT = NULL,
@intPageSize INT = NULL,
@strWhereCond VARCHAR(1000) = NULL,
@strSortBy VARCHAR(100) = NULL
AS

SET NOCOUNT ON

DECLARE @vchSQL VARCHAR(1000)
IF LOWER(@strWhereCond) <> ''
SET @vchSQL = ' Where ' + @strWhereCond

CREATE TABLE #TempTable
(
ID INT IDENTITY PRIMARY KEY ,
format_id INTEGER,
format_name VARCHAR(MAX),
remark VARCHAR(MAX),
status_active BIT,
createdby VARCHAR(50),
createddate DATETIME,
modifiedby VARCHAR(50),
modifieddate DATETIME,
countfinancial VARCHAR(50),
countnonfinancial VARCHAR(50)
)

EXEC ('INSERT INTO #TempTable (
format_id ,
format_name ,
remark ,
status_active ,
createdby ,
createddate ,
modifiedby ,
modifieddate,
countfinancial,
countnonfinancial
)
select * from (
SELECT
a.format_id ,
a.format_name ,
a.remark ,
a.status_active ,
a.createdby ,
a.createddate ,
a.modifiedby ,
a.modifieddate,
ISNULL(b.countformatid, 0) countfinancial,
ISNULL(c.countformatid, 0) countnonfinancial
FROM dbo.performance_mst_format a
LEFT JOIN (
SELECT COUNT(format_id) countformatid, format_id
FROM dbo.performance_mst_format_detail
where aspect = 1
GROUP BY format_id )
b ON b.format_id = a.format_id
LEFT JOIN (
SELECT COUNT(format_id) countformatid, format_id
FROM dbo.performance_mst_format_detail
where aspect = 2
GROUP BY format_id )
c ON c.format_id = a.format_id
)a
' + @vchSQL + ' ORDER BY a.modifieddate asc')

DECLARE @intFirstRec INT= 0 ,
@intLastRec INT= 0

IF ( @intPageSize <> 0 )
BEGIN
SET @intFirstRec = ( @intCurrentPage - 1 ) * @intPageSize + 1
SET @intLastRec = ( @intCurrentPage * @intPageSize + 1 ) - 1
END
ELSE
SELECT  @intLastRec = COUNT(*)
FROM    #TempTable

SELECT 
ID,
format_id ,
format_name ,
remark ,
status_active ,
createdby ,
createddate ,
modifiedby ,
modifieddate,
countfinancial,
countnonfinancial
FROM    #TempTable
WHERE   ID BETWEEN @intFirstRec AND @intLastRec

SELECT  COUNT(ID) AS intTotalRecord
FROM    #TempTable

GO