Креирање на достава

Актери

Менаџер

Чекор 1 - Најава на системот

Корисникот се најавува на системот со внесување на својот email и лозинка. Лозинката во позадина се хешира и таа се проверува во базата.

select user_id as userId,
                           user_name as userName,
                           user_surname as userSurname,
                           user_pass as userPassword,
                           user_email as userEmail,
                           user_mobile as userMobile,
                           user_salt as userSalt,
                           user_active as userActive,
                           user_image as userImage,
                           user_role as userRole,
                           clazz_
                    from users
                    where user_email = ?1

Чекор 2 - Dashboard

На почетната страница менаџерот ќе има преглед на најнови податоци и податоци од негов интерес, како што се моментална залиха во магацинот, состојба на возилата, преглед и број на новокреирани нарачки, состојба на тековните достави.

Моментална залиха во магацинот (подредени по број на производи од секој артикл)

select a.art_name as articleName,
                           man.man_name as manufacturerName,
                           count(au.unit_id) as totalUnits
                    from warehouse w
                        join manager m on m.wh_id = w.wh_id
                        join article_unit au on au.wh_id = w.wh_id
                        join unit_price up on au.unit_id = up.unit_id
                        join price p on p.price_id=up.price_id
                        join article a on p.art_id=a.art_id
                        join manufacturer man on a.man_id = man.man_id
                    where m.user_id = :manager
                    group by a.art_name, man.man_name
                    order by totalUnits

Состојба на возила

select v.veh_id as id,
                            v.veh_carry_weight as carryWeight,
                            v.veh_service_interval as serviceInterval,
                            v.veh_kilometers as kilometers,
                            v.veh_last_service as lastServiceDate,
                            v.veh_last_service_km as lastServiceKm,
                            v.veh_plate as plate,
                            v.veh_vin as vin,
                            v.veh_reg as registrationDate,
                            w.wh_id as whId,
                            c.city_name as city,
                            r.region_name as region,
                            d.user_id as driverId,
                            u1.user_name as driverName,
                            u1.user_email as driverEmail,
                            u1.user_mobile as driverPhone,
                            u1.user_image as driverImg
                     from warehouse w
                     join city c on w.city_id = c.city_id
                     join region r on c.region_id = r.region_id
                     join manager m on w.wh_id = m.wh_id
                     join users u on m.user_id = u.user_id
                     join vehicle v on w.wh_id=v.wh_id
                     join driver d on d.veh_id=v.veh_id
                     join users u1 on d.user_id=u1.user_id
                     where m.user_id = :manager
                     group by v.veh_id, v.veh_carry_weight, v.veh_service_interval, v.veh_kilometers, v.veh_last_service, v.veh_last_service_km, v.veh_plate, v.veh_vin, v.veh_reg, w.wh_id, c.city_name, r.region_name, d.user_id, u1.user_name, u1.user_email, u1.user_mobile, u1.user_image
                     order by v.veh_reg

Новокреирани нарачки

select o.ord_id as id,
                           o.ord_date as ordDate,
                           o.ord_sum as ordSum,
                           o.ord_fulfillment_date as ordFulfillmentDate,
                           c.cust_company_name as customerName,
                           o.ord_comment as ordComment,
                           o.o_status_id as oStatusId,
                           o.cust_id as customerId,
                           o.del_id as deliveryId,
                           o.pf_id as pfId
                    from warehouse w
                        join manager m on w.wh_id= m.wh_id
                        join article_unit au on au.wh_id = w.wh_id
                        join orders o on au.ord_id = o.ord_id
                        join customer c on o.cust_id = c.user_id
                        join order_status os on os.o_status_id = o.o_status_id
                    where m.user_id = ?1

Тековни достави

select d.del_id as id,
                           d.del_date_created as dateCreated,
                           d.del_date as deliveryDate,
                           d.del_start_km as delStartKm,
                           d.del_end_km as delEndKm,
                           to_char(d.del_start_time, 'HH24:MI:22') as delStartTime,
                           to_char(d.del_end_time, 'HH24:MI:SS') as delEndTime,
                           d.d_status_id as dStatusId,
                           ds.d_status_name as delStatus,
                           v.veh_id as vehId,
                           dr.user_id as driverId,
                           u.user_name as driverName,
                           u.user_image as driverImage
                    from warehouse w
                        join manager m on w.wh_id = m.wh_id
                        join article_unit au on w.wh_id = au.wh_id
                        join orders o on au.ord_id = o.ord_id
                        join delivery d on o.del_id = d.del_id
                        join delivery_status ds on d.d_status_id=ds.d_status_id
                        join vehicle v on v.veh_id=d.veh_id
                        join driver dr on dr.veh_id=v.veh_id
                        join users u on u.user_id=dr.user_id
                    where m.user_id=:manager and d.d_status_id<>4

Чекор 3 - Клика на копчето Креирај достава

Менаџерот е пренасочен кон страница каде што се прикажани сите нарачки кои не се дел од некоја достава.

select o.*
from warehouse w
         join manager m on w.warehouse_id = m.warehouse_id
         join article_unit au on au.warehouse_id = w.warehouse_id
         join orders o on au.order_id = o.order_id
where m.user_id = curr_user_id
  and o.delivery_id is null
order by o.order_date desc

Следно менаџерот може да ги групира и да ги додава во една достава која ќе ја назначи на некое возило.

Чекор 4 - Додавање на одбраните нарачки во достава и доделување на возило

Листање на возила

select v.*
from warehouse w
         join manager m on w.warehouse_id = m.warehouse_id
         join vehicle v on w.warehouse_id = v.warehouse_id
where m.user_id = curr_user_id and v.vehicle_id not in (select v.vehicle_id
                           from warehouse w
                                    join manager m on w.warehouse_id = m.warehouse_id
                                    join vehicle v on w.warehouse_id = v.warehouse_id
                                    join delivery d on v.vehicle_id = d.vehicle_id
                           where m.user_id=curr_user_id and d.delivery_date = created_delivery_date)

Креирање на достава за одбраното возило

insert into delivery(del_date_created, del_date, del_start_km, del_end_km, del_start_time, del_end_time, d_status_id, veh_id)
values (?1,?2,?3,?4,?5,?6,?7,?8)

Поврзување на одбраните нарачки со креираната достава

update orders
set ord_date=?2,ord_sum=?3,ord_fulfillment_date=?4,ord_comment=?5,o_status_id=?6,cust_id=?7,del_id=?8,pf_id=?9
where ord_id=?1