package org.pwd.domain.audit;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * @author bartosz.walacik
 */
public interface WebsiteAuditRepository extends JpaRepository<WebsiteAudit, Integer> {

    List<WebsiteAudit> findByAudit(Audit audit);

    WebsiteAudit findByAuditAndWebsiteId(Audit audit, int websiteId);

    List<WebsiteAudit> findByWebsiteId(int websiteId);

    @Query(nativeQuery = true, value = "SELECT * " +
            " FROM   website_audit wa, website w" +
            " WHERE  w.id = wa.website_fk" +
            " AND    wa.audit_fk = (SELECT MAX(id) FROM audit where process_status = 'DONE')" +
            " ORDER BY to_number(json_extract_path_text(audit_report,'score'),'999') DESC" +
            " LIMIT :maxRecords")
    List<WebsiteAudit> getTop(@Param("maxRecords") Integer maxRecords);

    @Query(nativeQuery = true, value = "SELECT * " +
            " FROM   website_audit wa, website w" +
            " WHERE  w.id = wa.website_fk" +
            " AND    wa.audit_fk = (SELECT MAX(id) FROM audit where process_status = 'DONE')" +
            " ORDER BY to_number(json_extract_path_text(audit_report,'score'),'999') DESC")
    List<WebsiteAudit> getSorted();

    @Query(nativeQuery = true, value = "SELECT * " +
            " FROM   website_audit wa, website w" +
            " WHERE  w.id = wa.website_fk" +
            " AND    wa.audit_fk = (SELECT MAX(id) FROM audit where process_status = 'DONE')" +
            " AND    w.id = :websiteId")
    WebsiteAudit getCurrentScore(@Param("websiteId") int websiteId);
}

@@ -759,32 +759,118 @@
 us = 'DONE')%22 +%0A
+            %22 AND    json_extract_path_text(audit_report,'httpStatusCode') = '200'%22 +%0A
             %22 OR
@@ -942,24 +942,24 @@
 9') DESC%22 +%0A
-
             
@@ -1291,32 +1291,118 @@
 us = 'DONE')%22 +%0A
+            %22 AND    json_extract_path_text(audit_report,'httpStatusCode') = '200'%22 +%0A
             %22 OR
@@ -1656,32 +1656,32 @@
 a.website_fk%22 +%0A
-
             %22 AN
@@ -1752,32 +1752,118 @@
 us = 'DONE')%22 +%0A
+            %22 AND    json_extract_path_text(audit_report,'httpStatusCode') = '200'%22 +%0A
             %22 AN

Update WebsiteAuditRepository.java