procedure TConsumption.TConsumeOw.CreateSQL;
begin
inherited;
FqryUI.SQL.Text := FqryUI.SQL.Text + N_Purge;
end;
function TConsumption.TConsumeOw.N_Final: string;
begin
Result :=
'(SELECT C.FKOWNER_FLAT, DOT.FKSERVICEPARAMETERS, DOT.FKCAANALYTICOPTIONS, DOT.FKSUBACCOUNTS, C.FKMETERS,'+
' MP.FKMETERTYPES, C.READING AS READFINAL, C.NUMBER AS N_FINAL, C.CORRECTION, C.KINDSTATE, M.COEFMETER,'+
' M.COEFLOCATION,'+
' CASE'+
' WHEN C.KINDSTATE = :WITHOUTREADING THEN MP.WITHOUTREADING'+
' WHEN C.KINDSTATE = :TAMPERING THEN MP.TAMPERING'+
' ELSE 1'+
' END AS PENALTY'+
' FROM CONSUMPTIONS C'+
' INNER JOIN METERS M ON (C.FKMETERS = M.IDMETERS)'+
' INNER JOIN METERPENALTIES MP ON (M.FKMETERTYPES = MP.FKMETERTYPES) AND (M.FKFOCS = MP.FKFOCS)'+
' INNER JOIN OWNER_FLAT ON (C.FKOWNER_FLAT = OWNER_FLAT.IDOWNER_FLAT) AND (OWNER_FLAT.FKFLAT = M.FKFLATS)'+
' INNER JOIN DAY_COST_OW_TOTALS DOT ON (OWNER_FLAT.IDOWNER_FLAT = DOT.FKOWNER_FLAT)'+
' INNER JOIN SEPA_ANALS SA ON (DOT.FKSERVICEPARAMETERS = SA.FKSERVICEPARAMETERS)'+
' AND (M.FKSERVICES = SA.FKSERVICES) AND (DOT.FKCAANALYTICOPTIONS = SA.FKCAANALYTICOPTIONS)'+
' WHERE'+
' ((C.READING BETWEEN :UPPERBEGIN AND :UPPEREND)'+
' OR'+
' ((C.READING BETWEEN :LOWEREND1 AND :UPPERBEGIN) AND (C.KINDSTATE = :KS2)))'+
' AND (MP.YEARFROM <= :YEARS) AND ((MP.YEARTO >= :YEARS) OR (MP.YEARTO IS NULL))) F';
end;
function TConsumption.TConsumeOw.N_Initial: string;
begin
Result :=
'(SELECT C.FKFOCS, SA.YEARS, C.FKOWNER_FLAT, DOT.FKSERVICEPARAMETERS,'+
' DOT.FKCAANALYTICOPTIONS, DOT.FKSUBACCOUNTS, C.FKMETERS, C.READING READINITIAL, C.NUMBER N_INITIAL'+
' FROM CONSUMPTIONS C'+
' INNER JOIN METERS M ON (C.FKMETERS = M.IDMETERS)'+
' INNER JOIN METERPENALTIES MP ON (M.FKMETERTYPES = MP.FKMETERTYPES)'+
' AND (M.FKFOCS = MP.FKFOCS)'+
' INNER JOIN OWNER_FLAT ON (C.FKOWNER_FLAT = OWNER_FLAT.IDOWNER_FLAT)'+
' AND (OWNER_FLAT.FKFLAT = M.FKFLATS)'+
' INNER JOIN DAY_COST_OW_TOTALS DOT ON (OWNER_FLAT.IDOWNER_FLAT = DOT.FKOWNER_FLAT)'+
' INNER JOIN SEPA_ANALS SA ON (DOT.FKSERVICEPARAMETERS = SA.FKSERVICEPARAMETERS)'+
' AND (M.FKSERVICES = SA.FKSERVICES) AND (DOT.FKCAANALYTICOPTIONS = SA.FKCAANALYTICOPTIONS)'+
' WHERE'+
' ((C.READING BETWEEN :LOWERBEGIN AND :LOWEREND)'+
' OR'+
' ((C.READING BETWEEN :LOWEREND1 AND :UPPERBEGIN) AND (C.KINDSTATE = :KS1))'+
' AND (MP.YEARFROM <= :YEARS) AND ((MP.YEARTO >= :YEARS) OR (MP.YEARTO IS NULL)))) I';
end;
function TConsumption.TConsumeOw.N_Purge: string;
begin
Result :=
'SELECT GEN_ID(SETTLEMENT_ID, 1), I.FKFOCS, I.YEARS, F.FKOWNER_FLAT, F.FKSERVICEPARAMETERS,'+
' F.FKCAANALYTICOPTIONS, F.FKSUBACCOUNTS, F.FKMETERTYPES, F.FKMETERS, I.READINITIAL DATEFROM, I.N_INITIAL,'+
' CASE'+
' WHEN F.READFINAL > :LASTDATE THEN :LASTDATE'+
' ELSE F.READFINAL'+
' END AS DATETO,'+
' F.N_FINAL, F.N_FINAL - I.N_INITIAL AS N_READING, F.COEFMETER, F.COEFLOCATION,'+
' CASE'+
' WHEN F.KINDSTATE > :KS2 THEN 0'+
' ELSE F.N_FINAL - I.N_INITIAL'+
' END * F.COEFMETER * F.COEFLOCATION AS N_COEF,'+
' CASE'+
' WHEN F.KINDSTATE > :KS2 THEN 0'+
' ELSE F.CORRECTION'+
' END AS CORRECTION,'+
' CASE'+
' WHEN F.KINDSTATE > :KS2 THEN 0'+
' ELSE F.N_FINAL - I.N_INITIAL + F.CORRECTION'+
' END * F.COEFMETER * F.COEFLOCATION AS N_CORR,'+
' F.KINDSTATE'+
' FROM ' +
N_Initial +
' INNER JOIN '+
N_Final+
' ON (I.FKOWNER_FLAT = F.FKOWNER_FLAT) AND (I.FKSERVICEPARAMETERS = F.FKSERVICEPARAMETERS)'+
' AND (I.FKCAANALYTICOPTIONS = F.FKCAANALYTICOPTIONS) AND (I.FKSUBACCOUNTS = F.FKSUBACCOUNTS)'+
' AND (I.FKMETERS = F.FKMETERS)';
end;
