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

/*SQLStart>7032*/
drop index TAKINGAPPIDX;
/*SQLStop>7032*/

/*ManyStart>7032*/
CREATE INDEX TAKINGAPPIDX ON TAKING (T_APPID1, T_APPID2, T_APPID3, T_APPID4);
CREATE INDEX TAKINGAPPCODEIDX ON TAKING (T_APPCODE);
/*ManyStop>7032*/

/*SQLStart>7033*/
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.00)  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>7033*/