Stock Count | 4Site Commercial

Upload Items

Upload the Items export from Wiise. Only items with Type = Inventory are kept.

Click to choose Items.xlsx

.xlsx or .csv

Confirm all entered quantities?

Archive & Start New Round

Counting period Round name

Who is counting?

Enter your name. It is saved on this device.

Admin Access

Enter the admin password.

Supabase setup needed

Run this SQL in the Supabase SQL Editor, then click Done. It can be run again safely; existing data is kept.

create table if not exists stock_counts (
  variant_key text primary key,
  qty numeric not null,
  counted_by text,
  counted_at timestamptz default now()
);
alter table stock_counts add column if not exists contributions_json text;

create table if not exists inventory_items (
  variant_key text primary key,
  item_no text not null,
  code text not null,
  description text,
  uom text,
  location text,
  system_qty numeric default 0,
  updated_at timestamptz default now()
);
alter table inventory_items add column if not exists extra_json text;

create table if not exists app_state (
  key text primary key,
  value text
);

create table if not exists item_uom (
  item_no text primary key,
  base_uom text
);

create table if not exists count_batches (
  id bigint generated always as identity primary key,
  name text not null,
  period text,
  started_at timestamptz,
  archived_at timestamptz default now(),
  archived_by text,
  variant_count int,
  counted_count int,
  discrepancy_count int,
  excluded_json text
);

create table if not exists count_archive (
  batch_id bigint references count_batches(id) on delete cascade,
  variant_key text,
  item_no text,
  code text,
  description text,
  uom text,
  location text,
  system_qty numeric,
  extra_json text,
  counted_qty numeric,
  counted_by text,
  counted_at timestamptz,
  contributions_json text,
  primary key (batch_id, variant_key)
);

do $$ declare t text; begin
  foreach t in array array['stock_counts','inventory_items','app_state','item_uom','count_batches','count_archive'] loop
    execute format('alter table %I enable row level security', t);
    execute format('drop policy if exists "allow all" on %I', t);
    execute format('create policy "allow all" on %I for all using (true) with check (true)', t);
  end loop;
end $$;

create or replace function add_count(p_key text, p_qty numeric, p_by text)
returns setof stock_counts language sql as $$
  insert into stock_counts (variant_key, qty, counted_by, counted_at, contributions_json)
  values (p_key, p_qty, p_by, now(),
    jsonb_build_array(jsonb_build_object('by', p_by, 'qty', p_qty, 'ts', now()))::text)
  on conflict (variant_key) do update set
    qty = stock_counts.qty + excluded.qty,
    counted_at = now(),
    contributions_json = (coalesce(nullif(stock_counts.contributions_json, ''), '[]')::jsonb
      || jsonb_build_array(jsonb_build_object('by', p_by, 'qty', p_qty, 'ts', now())))::text
  returning *;
$$;

create or replace function undo_count(p_key text, p_by text)
returns setof stock_counts language plpgsql as $$
declare
  r stock_counts;
  arr jsonb;
  idx int;
  removed numeric;
begin
  select * into r from stock_counts where variant_key = p_key for update;
  if not found then return; end if;
  arr := coalesce(nullif(r.contributions_json, ''), '[]')::jsonb;
  if jsonb_array_length(arr) = 0 then
    if lower(trim(coalesce(r.counted_by, ''))) = lower(trim(p_by)) then
      delete from stock_counts where variant_key = p_key;
      return;
    end if;
    raise exception 'NO_CONTRIBUTION';
  end if;
  select max(e.ord) - 1 into idx
    from jsonb_array_elements(arr) with ordinality as e(v, ord)
    where coalesce(e.v->>'type', '') <> 'edit'
      and lower(trim(e.v->>'by')) = lower(trim(p_by));
  if idx is null then raise exception 'NO_CONTRIBUTION'; end if;
  removed := coalesce((arr->idx->>'qty')::numeric, 0);
  arr := arr - idx;
  if not exists (select 1 from jsonb_array_elements(arr) v where coalesce(v->>'type', '') <> 'edit') then
    delete from stock_counts where variant_key = p_key;
    return;
  end if;
  update stock_counts
     set qty = greatest(qty - removed, 0), contributions_json = arr::text, counted_at = now()
   where variant_key = p_key
  returning * into r;
  return next r;
end $$;

create or replace function archive_batch(p_name text, p_period text, p_by text)
returns count_batches language plpgsql as $$
declare b count_batches;
begin
  insert into count_batches (name, period, started_at, archived_by, excluded_json)
  values (p_name, p_period,
    least((select min(updated_at) from inventory_items), (select min(counted_at) from stock_counts)),
    p_by, (select value from app_state where key = 'excluded_upload'))
  returning * into b;

  insert into count_archive (batch_id, variant_key, item_no, code, description, uom, location,
    system_qty, extra_json, counted_qty, counted_by, counted_at, contributions_json)
  select b.id, coalesce(i.variant_key, s.variant_key), i.item_no, i.code, i.description, i.uom,
    i.location, i.system_qty, i.extra_json, s.qty, s.counted_by, s.counted_at, s.contributions_json
  from inventory_items i full outer join stock_counts s on s.variant_key = i.variant_key;

  update count_batches set
    variant_count = (select count(*) from count_archive where batch_id = b.id and item_no is not null),
    counted_count = (select count(*) from count_archive where batch_id = b.id and item_no is not null and counted_qty is not null),
    discrepancy_count = (select count(*) from count_archive where batch_id = b.id and item_no is not null
      and counted_qty is not null and counted_qty <> coalesce(system_qty, 0))
  where id = b.id
  returning * into b;

  delete from stock_counts where true;
  delete from inventory_items where true;
  insert into app_state (key, value) values
    ('locked', 'false'), ('excluded_upload', '[]'), ('inv_version', now()::text)
  on conflict (key) do update set value = excluded.value;
  return b;
end $$;

Upload Item Variants

Click or drag the Item Variants file here