/*ALIAS>prop_db*/
/*Hist_TBL>SQL_UPDATES_INCOMES*/

/*SQLStart>7050*/

UPDATE
    taking
SET
    taking.t_state = 3
WHERE
    taking.t_dutysum < 0.001
    AND taking.t_state = 0
;

/*SQLStop*/

/*SQLStart>7051*/
CREATE OR ALTER procedure TAKINGUPDATESTATE (
    TAKINGID integer)
as
declare variable DIFF numeric(15,3);
declare variable PAYSUM numeric(15,3);
declare variable EXPECTEDSUM numeric(15,3);
declare variable SIGN char(1);
declare variable PAYCNT integer;
declare variable TAKINGEXPECTEDSUM numeric(15,3);
declare variable TAKINGPAYSUM numeric(15,3);
declare variable TAKINGEXPECTEDDDS numeric(15,3);
declare variable TAKINGPAYDDS numeric(15,3);
declare variable RECFOUND smallint;
begin

  select sum(PT_ExpectedSum), sum(PT_PaySum), sum(PT_ExpectedDDS), sum(PT_PayDDS)
  from PayTaking
  where PT_ID = :TAKINGID
  into :TAKINGEXPECTEDSUM, :TAKINGPAYSUM, :TAKINGEXPECTEDDDS, :TAKINGPAYDDS;

  select count(TakingID) from TakingSums
  where TakingID = :TakingID
  into :recFound;

  if (recFound = 0) then
  begin
    insert into TakingSums (TakingID, TakingExpectedSum, TakingPaySum, TakingExpectedDDS, TakingPayDDS)
    values (:TakingID, :TakingExpectedSum, :TakingPaySum, :TakingExpectedDDS, :TakingPayDDS);
  end
  else
  begin
    update TakingSums set
      TakingExpectedSum = :TakingExpectedSum,
      TakingPaySum = :TakingPaySum,
      TakingExpectedDDS = :TakingExpectedDDS,
      TakingPayDDS = :TakingPayDDS
    where
      TakingID = :TakingID;
  end


  select T_OnlySign from Taking where T_ID = :TAKINGID into :sign;
  if (sign = "F") then
  begin
    select sum(PT_ExpectedSum), sum(PT_PaySum) from PayTaking where PT_ID = :TakingID into :expectedsum , :paysum;
    diff = expectedsum - paysum;
    if ((paysum = 0.00) and (expectedsum = 0.00)) then
      update Taking set T_STATE = 0 where T_ID = :TakingID;
    else if ((paysum >= 0.00) and (paysum < 0.01)) then
      update Taking set T_STATE = 0 where T_ID = :TakingID;
    else if ((diff >= 0.00) and (diff < 0.01)) then
      update Taking set T_STATE = 2 where T_ID = :TakingID;
    else if ((diff > -0.01) and (diff <= 0.00)) then
      update Taking set T_STATE = 2 where T_ID = :TakingID;
    else if (paysum < expectedsum) then
      update Taking set T_STATE = 0 where T_ID = :TakingID;
    else if (diff < 0.001)  then
      update Taking set T_STATE = 3 where T_ID = :TakingID;
  end else
  begin
    select count(*) from PayTaking where PT_ID = :TakingID into :paycnt;
    if (paycnt = 1) then
      update Taking set T_STATE = 0 where T_ID = :TakingID;
    else if (paycnt = 2) then
      update Taking set T_STATE = 2 where T_ID = :TakingID;
  end
end
;
/*SQLStop*/
