--[[ 20c10fbaba399154 ]]

local caption = 'Rozpis zálohovych platieb bytov'
if not db.Opened
then
  MessageBox('Otvorte prosím účtovníctvo a spustite tento skript znovu.', caption, 64)

  return false

else

  local frm = New 'TForm'
  frm.Caption = caption
  frm.Position = 'poMainFormCenter'
  frm.Width = 360

  std_gui:Init(frm)
  std_gui:Add('edtObd1', 'TObdobieEdit', 'Z obdobia').Obdobie = Settings.Obdobie
  std_gui:Add('edtObd2', 'TObdobieEdit', 'Do obdobia').Obdobie = obdobie.offset(Settings.Obdobie, 3, 'm')
  local d = std_gui:Add('edtDoklad', 'TCiselnikEdit', '&Druh dokladu', 'doklad')
  d.UpperCase = true
  d.Text = 'E'
  std_gui:Add('', 'TOkCancelButtons', '')

  frm.ClientHeight = std_gui.Y

  local obd1 = 0
  local obd2 = 0
  local druhi = 0

  frm.btnOk.OnClick = function(Sender)

    -- zadane spravne obdobie?
    if not misc.ValidObdobie(Sender.Parent.edtObd1)
    or not misc.ValidObdobie(Sender.Parent.edtObd2)
    or not misc.ValidCiselnikID(Sender.Parent.edtDoklad, 'doklad', false, false)
    then
      Sender.Parent.ModalResult = 0
      return nil
    end

    obd1 = Sender.Parent.edtObd1.Obdobie
    obd2 = Sender.Parent.edtObd2.Obdobie
    druhi = Sender.Parent.edtDoklad.CiselnikID
  end

  if frm:ShowModal() == 1 then

    local eid = "select rowid from obrat where id_obdobie between " .. obd1 .. " and " .. obd2 .. " and id_dokladu = " .. druhi
    local u = sql("select distinct r.id_uctu, u.nazov from rozuctovanie r inner join ucet u on r.id_uctu = u.rowid where id_obratu in (" .. eid .. ")")
    local rh = ""
    local rl = ""
    while not u.Eof do
      local id = u:Field(0)
      local me = u:Field(1)
      rh = rh .. ", sum(case when r.id_uctu = " .. id .. " then r.Suma else 0 end) as u" .. id
      rl = rl .. ", " .. misc.SqlString(me)
      u:Next()
    end
    local r = sql("select * from (select b.cislo c, p.meno m" .. rh .. " " ..
      "from rozuctovanie r " ..
      "inner join obrat o on r.id_obratu = o.rowid " ..
      "inner join najomca n on o.id_najomcu = n.rowid " ..
      "inner join partner p on n.id_partnera = p.rowid " ..
      "inner join byt b on n.id_bytu = b.rowid " ..
      "where id_obratu in (" .. eid .. ") group by id_najomcu " ..
      "union all select '', ''" .. rl .. ") order by c")

    local s = ""
    while not r.Eof do
      s = s .. table.concat(r.IRow, '\t'):gsub('%.',',') .. '\n'
      r:Next()
    end

    local fname = caption .. ' od ' .. obd1 .. ' do ' .. obd2 .. '.xls'
    local path = business.OpenDialog(fname, fname, '')
    if path then
      local f = io.open(path, 'w+')
      f:write(s)
      f:close()

      return true
    end

  end

  return false
end