72 lines
2.3 KiB
PL/PgSQL
72 lines
2.3 KiB
PL/PgSQL
set search_path = app, public;
|
|
|
|
create or replace function app.open_shift(
|
|
p_till_id uuid,
|
|
p_opening_usd numeric,
|
|
p_opening_lbp numeric,
|
|
p_assigned_user_id uuid default null
|
|
)
|
|
returns uuid
|
|
language plpgsql
|
|
security definer
|
|
set search_path = app, public
|
|
as $$
|
|
declare
|
|
v_shop uuid;
|
|
v_shift uuid;
|
|
v_target_user uuid;
|
|
begin
|
|
select shop_id into v_shop from app.tills where id = p_till_id and is_active;
|
|
if v_shop is null then
|
|
raise exception 'till % not found or inactive', p_till_id;
|
|
end if;
|
|
|
|
if p_assigned_user_id is null then
|
|
if not app.has_any_role_in_shop(v_shop, array['cashier','manager']::app.business_role[]) then
|
|
raise exception 'not authorized to open a shift on this till';
|
|
end if;
|
|
v_target_user := auth.uid();
|
|
else
|
|
if not app.has_any_role_in_shop(v_shop, array['owner','manager']::app.business_role[]) then
|
|
raise exception 'only managers or owners can assign shifts to other users';
|
|
end if;
|
|
v_target_user := p_assigned_user_id;
|
|
if not exists (
|
|
select 1 from app.user_shop_assignments
|
|
where user_id = v_target_user and shop_id = v_shop
|
|
) then
|
|
raise exception 'target user does not have a role in this shop';
|
|
end if;
|
|
end if;
|
|
|
|
if exists (select 1 from app.shifts where till_id = p_till_id and status <> 'closed') then
|
|
raise exception 'till % already has an active shift; close it first', p_till_id;
|
|
end if;
|
|
|
|
insert into app.shifts(till_id, shop_id, user_id, opened_by, opening_usd, opening_lbp)
|
|
values (p_till_id, v_shop, v_target_user, auth.uid(), p_opening_usd, p_opening_lbp)
|
|
returning id into v_shift;
|
|
|
|
insert into app.cash_movements(shift_id, type, currency, amount, note)
|
|
select v_shift, 'opening_float', x.currency, x.amount, 'opening float'
|
|
from (values ('USD'::app.currency_code, p_opening_usd), ('LBP'::app.currency_code, p_opening_lbp))
|
|
as x(currency, amount)
|
|
where x.amount > 0;
|
|
|
|
perform app.log_auth_event(
|
|
'shift_opened',
|
|
v_shop,
|
|
null,
|
|
jsonb_build_object(
|
|
'shift_id', v_shift,
|
|
'till_id', p_till_id,
|
|
'assigned_user_id', v_target_user
|
|
)
|
|
);
|
|
return v_shift;
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function app.open_shift(uuid, numeric, numeric, uuid) from public;
|
|
grant execute on function app.open_shift(uuid, numeric, numeric, uuid) to authenticated;
|