' Gambas class file
PRIVATE conn AS NEW Connection
PRIVATE resManager AS Result
PRIVATE resNewOffice AS Result 'Global variable

PUBLIC SUB Form_Open()
  IF make_connection()=TRUE THEN
    load_combo
    ComboBox1_Click
    load_new_office
  END IF
END

PRIVATE FUNCTION make_connection() AS Boolean
  WITH conn
    .Type = "mysql"
    .Host = "localhost"
    .Login = "bainm"
    .Password = "mypassword"
    .Name = "customers"
  END WITH
  TRY conn.Open
  IF ERROR THEN
    Message ("Cannot Open Database. Error = " & Error.Text)
    RETURN FALSE
  END IF
  RETURN TRUE
END

PRIVATE SUB load_combo()
  DIM sql AS String
  sql="select id, surname,firstname from manager"
  resManager= conn.Exec(sql)
  FOR EACH resManager
    ComboBox1.Add (resManager!firstname & " " & resManager!surname)
  NEXT
END

PRIVATE SUB load_new_office ()
  DIM sql AS String
  sql="select id,city from office where manager_id is NULL"
  resNewOffice=conn.Exec(sql)
  combobox2.Clear
  FOR EACH resNewOffice
    combobox2.Add(resNewOffice!city)
  NEXT
END

PUBLIC SUB ComboBox1_Click()
  DIM res AS Result
  DIM sql AS String
  
  resManager.MoveFirst
  resManager.MoveTo( ComboBox1.Index)
  sql = "select city from office" &
  " where manager_id=" & resManager!id
  res = conn.Exec(sql)
  textbox1.Text=res!city
END

PUBLIC SUB Button1_Click()
  DIM res AS Result
  DIM sql AS String
  IF textbox2.text <> "" THEN
    sql="insert into office (city) values ('" & textbox2.text &"')"
    res=conn.Exec(sql)
    load_new_office
  ELSE 
    message ("Input required")
  END IF
END

PUBLIC SUB Button2_Click()
  DIM res AS Result
  DIM sql AS String
  resNewOffice.MoveFirst
  resNewOffice.MoveTo (combobox2.Index)
  resManager.MoveFirst
  resManager.MoveTo (combobox1.Index)
  sql="update office set manager_id=NULL" &
  " where manager_id=" & resManager!id
  res=conn.Exec(sql)
  sql="update office set manager_id=" & resManager!id & 
  " where id=" & resNewOffice!id
  res=conn.Exec(sql)
  ComboBox1_Click
  load_new_office
END