I have defined this function in my repository , it works fine in my database as I tested it and all the fields contains values.
@Query(value="SELECT employee_id,\n" +
"SUM(january) as january,SUM(february) as february, SUM(march) as march, SUM(april) as april, SUM(may) as may, SUM(june) as june, SUM(july) as july, SUM(august) as august, sum(september) as septebmer, sum(october) as october, sum(november)as november\n" +
"from\n" +
"(\n" +
"SELECT * FROM\n" +
"(SELECT t1.id as task_id,\n" +
" count(CASE WHEN extract(isodow from dt) BETWEEN 1 AND 5 AND EXTRACT(MONTH from dt) = 1 THEN 1 END) as january,\n" +
" count(CASE WHEN extract(isodow from dt) BETWEEN 1 AND 5 AND EXTRACT(MONTH from dt) = 2 THEN 1 END) as february,\n" +
" count(CASE WHEN extract(isodow from dt) BETWEEN 1 AND 5 AND EXTRACT(MONTH from dt) = 3 THEN 1 END) as march,\n" +
"\t count(CASE WHEN extract(isodow from dt) BETWEEN 1 AND 5 AND EXTRACT(MONTH from dt) = 4 THEN 1 END) as april,\n" +
" count(CASE WHEN extract(isodow from dt) BETWEEN 1 AND 5 AND EXTRACT(MONTH from dt) = 5 THEN 1 END) as may,\n" +
" count(CASE WHEN extract(isodow from dt) BETWEEN 1 AND 5 AND EXTRACT(MONTH from dt) = 6 THEN 1 END) as june,\n" +
" count(CASE WHEN extract(isodow from dt) BETWEEN 1 AND 5 AND EXTRACT(MONTH from dt) = 7 THEN 1 END) as july,\n" +
" count(CASE WHEN extract(isodow from dt) BETWEEN 1 AND 5 AND EXTRACT(MONTH from dt) = 8 THEN 1 END) as august,\n" +
" count(CASE WHEN extract(isodow from dt) BETWEEN 1 AND 5 AND EXTRACT(MONTH from dt) = 9 THEN 1 END) as september,\n" +
" count(CASE WHEN extract(isodow from dt) BETWEEN 1 AND 5 AND EXTRACT(MONTH from dt) = 10 THEN 1 END) as october,\n" +
"\t count(CASE WHEN extract(isodow from dt) BETWEEN 1 AND 5 AND EXTRACT(MONTH from dt) = 11 THEN 1 END) as november,\n" +
" count(CASE WHEN extract(isodow from dt) BETWEEN 1 AND 5 AND EXTRACT(MONTH from dt) = 12 THEN 1 END) as december\n" +
"FROM task as t1\n" +
"CROSS JOIN generate_series(t1.start_date,t1.end_date,cast('1 day' as interval)) dt\n" +
"group by t1.id)\n" +
"as days\n" +
"INNER JOIN \n" +
"(SELECT result2.task_id,client_id, employee_id, employee.username from\n" +
"(SELECT result.task_id, task_name, task_start_date, result.task_end_date,project_id,project_name,client_id, employee_id FROM\n" +
"(SELECT task.id as task_id, task.name as task_name, task.start_date as task_start_date, task.end_date as task_end_date, project.id as project_id, \n" +
"project.name as project_name , project.client_id\n" +
"FROM project,task\n" +
"WHERE project.id=task.project_id)\n" +
"as result, employee_task\n" +
"WHERE employee_task.task_id=result.task_id\n" +
"AND client_id= :client_id)\n" +
"as result2, employee\n" +
"WHERE employee.id=result2.employee_id)\n" +
"as info\n" +
"ON days.task_id=info.task_id)\n" +
"as final_table\n" +
"GROUP BY employee_id",nativeQuery = true)
List<WorkdaysRepartition> getWorkDaysPerClient(@Param("client_id")long client_id);
however when i try to return the result via a controller, it returns some null fields ( december and september )
this is the result in my database :
id jan feb ................................december
14 21 20 23 22 22 22 21 23 22 21 22
15 21 20 1 0 0 0 0 0 0 0 0
16 21 20 23 21 5 22 21 23 22 21 22
17 21 20 1 0 0 0 0 0 0 0 0
18 10 0 0 0 0 0 0 0 0 0 0
19 21 20 23 21 22 22 21 23 22 21 22
20 0 0 23 21 22 22 21 23 22 0 0
21 0 0 0 21 22 22 21 23 22 21 22
22 0 0 23 21 22 22 21 23 22 21 22
23 0 0 23 21 22 22 21 23 22 21 22
this is the response JSON in POSTMAN :
[
{
"december": null,
"september": null,
"october": 21,
"november": 22,
"june": 22,
"august": 23,
"july": 21,
"january": 21,
"april": 22,
"may": 22,
"february": 20,
"march": 23,
"employee_id": 14
},
{
"december": null,
"september": null,
"october": 0,
"november": 0,
"june": 0,
"august": 0,
"july": 0,
"january": 21,
"april": 0,
"may": 0,
"february": 20,
"march": 1,
"employee_id": 15
},
{
"december": null,
"september": null,
"october": 21,
"november": 22,
"june": 22,
"august": 23,
"july": 21,
"january": 21,
"april": 21,
"may": 5,
"february": 20,
"march": 23,
"employee_id": 16
},
{
"december": null,
"september": null,
"october": 0,
"november": 0,
"june": 0,
"august": 0,
"july": 0,
"january": 21,
"april": 0,
"may": 0,
"february": 20,
"march": 1,
"employee_id": 17
},
{
"december": null,
"september": null,
"october": 0,
"november": 0,
"june": 0,
"august": 0,
"july": 0,
"january": 10,
"april": 0,
"may": 0,
"february": 0,
"march": 0,
"employee_id": 18
},
{
"december": null,
"september": null,
"october": 21,
"november": 22,
"june": 22,
"august": 23,
"july": 21,
"january": 21,
"april": 21,
"may": 22,
"february": 20,
"march": 23,
"employee_id": 19
},
{
"december": null,
"september": null,
"october": 0,
"november": 0,
"june": 22,
"august": 23,
"july": 21,
"january": 0,
"april": 21,
"may": 22,
"february": 0,
"march": 23,
"employee_id": 20
},
{
"december": null,
"september": null,
"october": 21,
"november": 22,
"june": 22,
"august": 23,
"july": 21,
"january": 0,
"april": 21,
"may": 22,
"february": 0,
"march": 0,
"employee_id": 21
},
{
"december": null,
"september": null,
"october": 21,
"november": 22,
"june": 22,
"august": 23,
"july": 21,
"january": 0,
"april": 21,
"may": 22,
"february": 0,
"march": 23,
"employee_id": 22
},
{
"december": null,
"september": null,
"october": 21,
"november": 22,
"june": 22,
"august": 23,
"july": 21,
"january": 0,
"april": 21,
"may": 22,
"february": 0,
"march": 23,
"employee_id": 23
}
]
and finally : this is my WorkdaysRepartition interface
public interface WorkdaysRepartition {
Long getEmployee_id();
Long getJanuary();
Long getFebruary();
Long getMarch();
Long getApril();
Long getMay();
Long getJune();
Long getJuly();
Long getAugust();
Long getSeptember();
Long getOctober();
Long getNovember();
Long getDecember();
}
any solutions?
