工時系統

[elven]

SQL view

CREATE VIEW "hours_2021" AS select auth_user.username,
wnjosma_project.project_code,
wnjosma_customer.name,
wnjworkinghours_workinghour.created_date,
wnjtask_category.category_name,
wnjtask_tasktype.task_name,
wnjworkinghours_workinghour.subject,
wnjworkinghours_workinghour.description,
wnjworkinghours_workinghour.expectation,
wnjworkinghours_workinghour.delay_reason,
wnjworkinghours_workinghour.hours
from auth_user, wnjworkinghours_workinghour, wnjtask_tasktype, wnjosma_project, wnjosma_customer, wnjtask_category
where wnjworkinghours_workinghour.created_date between "2021-01-01"  and "2021-12-31"
and wnjworkinghours_workinghour.author_id = auth_user.id
and wnjworkinghours_workinghour.project_code_id = wnjosma_project.id
and wnjosma_project.customer_id_id = wnjosma_customer.id
and (wnjworkinghours_workinghour.tasktype_id = wnjtask_tasktype.task_id)
AND wnjtask_tasktype.category_id = wnjtask_category.category_id
order by auth_user.id, wnjtask_tasktype.task_id

[elven]

切割project_code

select username, project_code, substr(project_code, instr(project_code, '_') + 1, 1) as project_type, name, sum(hours) from hours_2021
group by project_code,username
order by username, project_type