begin
mCrLf = ' ';
mSQL = 'SELECT ' || mCrLf ||
' A.StoreBatch_ID AS ID ' || mCrLf ||
'FROM StoreSubBatches A ' || mCrLf ||
'JOIN StoreBatches B ON B.ID = A.StoreBatch_ID ' || mCrLf ||
'WHERE{WHERE} ' || mCrLf ||
'{GROUPBY} ' || mCrLf ||
'{HAVING}';
mWhereSQL = ' ';
mGroupBySQL = ' ';
mHavingSQL = ' ';
-- omezeni za sklad
if (AStore_ID = '__________') then begin
if (AAvailableOnly = 'A') then begin
mGroupBySQL = 'A.StoreBatch_ID';
mHavingSQL = 'SUM(A.Quantity) > 0';
end
end else begin
mWhereSQL = mWhereSQL || mCrlf || ' ' || 'A.Store_ID = ' || '''' || AStore_ID || '''';
end
-- omezeni za skladovou kartu
if (AStoreCard_ID <> '__________') then begin
if (mWhereSQL <> ' ') then begin
mWhereSQL = mWhereSQL || mCrlf || ' AND ';
end
mWhereSQL = mWhereSQL || 'A.StoreCard_ID = ' || '''' || AStoreCard_ID || '''';
end
-- omezeni za nenulove sarze
if ((AAvailableOnly = 'A') and (AStore_ID <> '__________')) then begin
if (mWhereSQL <> ' ') then begin
mWhereSQL = mWhereSQL || mCrlf || ' AND ';
end
mWhereSQL = mWhereSQL || 'A.Quantity > 0';
end
-- where
if (mWhereSQL <> ' ') then begin
mWhereSQL = mWhereSQL || mCrlf || ' AND ';
end
mWhereSQL = mWhereSQL || 'B.Hidden = ''N''';
mSQL = ib_string_replace(mSQL, '{WHERE}', mWhereSQL);
-- group by
if (mGroupBySQL <> ' ') then begin
mGroupBySQL = 'GROUP BY ' || mCrlf || mGroupBySQL;
end
mSQL = ib_string_replace(mSQL, '{GROUPBY}', mGroupBySQL);
-- having
if (mHavingSQL <> ' ') then begin
mHavingSQL = 'HAVING ' || mCrlf || mHavingSQL;
end
mSQL = ib_string_replace(mSQL, '{HAVING}', mHavingSQL);
-- otevreni cursoru a vraceni radek
for
EXECUTE STATEMENT mSQL into ID
do begin
suspend;
end
end