InfoPark

EXECUTE block
RETURNS (CRRESULT VARCHAR(32000))
AS
DECLARE variable v_id INT;
DECLARE variable v_id_bill INT;
DECLARE variable v_fid INT;
DECLARE variable v_id_table INT;
DECLARE variable v_prior INT;
DECLARE variable v_id_emp INT;
DECLARE variable v_id_ds INT;
DECLARE variable v_dt_open TIMESTAMP;
DECLARE variable v_dt_close TIMESTAMP;
BEGIN
  crresult = '';
  v_fid = 2;
  FOR SELECT fc.id, fc.id_table, fc.dt_open, fc.dt_close, fc.prior, fc.id_emp
        FROM food_checks fc
        INTO :v_id, :v_id_table, :v_dt_open, :v_dt_close, :v_prior, :v_id_emp
      do
      BEGIN
        SELECT FIRST 1 ds.id FROM desk_shift ds
        JOIN desks d ON d.id = ds.id_desk
        WHERE d.types = 5
        ORDER BY ds.dt_close DESC
        INTO :v_id_ds;
 
        INSERT INTO bills (fid, dt_pay, dt_close, id_emp, id_ds, fid_ds, id_table, prior)
        VALUES (:v_fid, :v_dt_open, :v_dt_close, :v_id_emp, :v_id_ds, :v_fid, :v_id_table, :v_prior)
        returning id INTO :v_id_bill;
 
        UPDATE food_check_dish cd
           SET cd.id_check = NULL,
               cd.id_bill = :v_id_bill,
               cd.fid_bill = :v_fid
         WHERE cd.id_check = :v_id;
 
        DELETE FROM food_checks fc WHERE fc.id = :v_id;
      END
 
END