$TYPECHECK ON

$INCLUDE <qDATABASESQL.inc>
	
DECLARE FUNCTION SetWindowLong LIB "User32" ALIAS "SetWindowLongA" (hwnd AS Long, nIndex AS Long, dwNewLong AS Long) AS LONG ' minimalize correction
DECLARE FUNCTION SetFocus Lib "user32" Alias "SetFocus" (hwnd As Long) As Long

DECLARE FUNCTION VerificaDB() as INTEGER
DECLARE SUB LoadData()
DECLARE SUB EnableEdits(Enabled_0_1 as INTEGER)
DECLARE SUB Button1_Click
DECLARE SUB Button2_Click
DECLARE SUB Button3_Click
DECLARE SUB Button4_Click
DECLARE SUB Button5_Click
DECLARE SUB Button6_Click
DECLARE SUB Button7_Click
DECLARE SUB Button2_CANCELAR
DECLARE SUB Button3_SALVAR
DECLARE SUB Button3_SALVAR_EDIT
DECLARE SUB cmd_OkButton_Click

CREATE Form AS QFORM
    Caption = "Exemplo do qDATABASE"
    Width = 522
    Height = 397
    Color = 13697023
    Center
    DelBorderIcons(2)
    BorderStyle = 1
        
    CREATE Label1 AS QLABEL
        Caption = "Registro Nº"
        Left = 340
        Top = 11
        Width = 70
        Transparent = 1
        Alignment = 1 
    END CREATE
    CREATE Label2 AS QLABEL
        Caption = "Nome"
        Left = 5
        Top = 46
        Width = 70
        Transparent = 1
        Alignment = 1
    END CREATE   
    CREATE Label3 AS QLABEL
        Caption = "Endereço"
        Left = 5
        Top = 75
        Width = 70
        Transparent = 1
        Alignment = 1
    END CREATE
    CREATE Label4 AS QLABEL
        Caption = "Telefone"
        Left = 5
        Top = 115
        Width = 70
        Transparent = 1
        Alignment = 1        
    END CREATE
    CREATE Label5 AS QLABEL
        Caption = "E-Mail"
        Left = 5
        Top = 155
        Width = 70
        Transparent = 1
        Alignment = 1 
    END CREATE
    CREATE Label6 AS QLABEL
        Caption = "Observações"
        Left = 5
        Top = 187
        Width = 70
        Transparent = 1
        Alignment = 1 
    END CREATE

    CREATE Edit1 AS QEDIT
        Left = 420
        Top = 6
        Width = 73
        Enabled = 0
        Color = 14935273
    END CREATE
    CREATE Edit2 AS QEDIT
        Left = 83
        Top = 38
        Width = 240
        Enabled = 0
        TabOrder = 1
        Color = 14935273
    END CREATE
    CREATE Edit3 AS QEDIT
        Left = 83
        Top = 70
        Width = 410
        Enabled = 0
        TabOrder = 2
        Color = 14935273
    END CREATE
    CREATE Edit4 AS QEDIT
        Left = 83
        Top = 110
        Enabled = 0
        TabOrder = 3
        Color = 14935273
    END CREATE
    CREATE Edit5 AS QEDIT
        Left = 83
        Top = 150
        Width = 265
        Enabled = 0
        TabOrder = 4
        Color = 14935273
    END CREATE
    CREATE Edit6 AS QRICHEDIT
        Left = 83
        Top = 191
        Width = 410
        Enabled = 0
        TabOrder = 5
        Color = 14935273
    END CREATE
    
    CREATE Button1 AS QCoolBtn
        Caption = "<< Voltar"
        Left = 8
        Top = 320
        Height = 23
        Width = 65
        flat = 1
        OnClick = Button1_Click
    END CREATE
    CREATE Button2 AS QCoolBtn
        Caption = "Avançar >>"
        Left = 81
        Top = 320
        Height = 23
        Width = 65
        flat = 1
        OnClick = Button2_Click
    END CREATE
    CREATE Button3 AS QCoolBtn
        Caption = "Adcionar"
        Left = 154
        Top = 320
        Height = 23
        Width = 65
        flat = 1
        OnClick = Button3_Click
    END CREATE
    CREATE Button4 AS QCoolBtn
        Caption = "Editar"
        Left = 227
        Top = 320
        Height = 23
        Width = 65
        flat = 1
        OnClick = Button4_Click
    END CREATE
    CREATE Button5 AS QCoolBtn
        Caption = "Localizar"
        Left = 300
        Top = 320
        Height = 23
        Width = 65
        flat = 1
        OnClick = Button5_Click
    END CREATE
    CREATE Button6 AS QCoolBtn
        Caption = "Excluir"
        Left = 373
        Top = 320
        Height = 23
        Width = 65
        flat = 1
        OnClick = Button6_Click
    END CREATE
    CREATE Button7 AS QCoolBtn
        Caption = "Fechar"
        Left = 446
        Top = 320
        Height = 23
        Width = 65
        flat = 1
        OnClick = Button7_Click
    END CREATE
END CREATE

SetWindowLong Form.HANDLE, -8, 0					' minimalizing correction
SetWindowLong application.HANDLE, -8, Form.HANDLE 	' minimalizing correction

DIM frm_Localizar as  qFORM
DIM lbl_Nome as qLABEL
DIM txt_Nome as qEDIT
DIM cmd_CancelButton as qBUTTON
DIM cmd_OkButton as qBUTTON

WITH frm_Localizar
    .caption = "Localizar"
	.width = 218: .Height = 135
	.delbordericons 1, 2
	.borderstyle = 1
	.center
	.Color = 13697023
	SetWindowLong .HANDLE, -8, 0
	SetWindowLong application.HANDLE, -8, .HANDLE
END WITH

WITH lbl_Nome
    .PARENT = frm_Localizar
	.TOP = 13: .LEFT = 10
	.CAPTION = "Entre com o Nome"
END WITH

WITH txt_Nome
	.parent = frm_Localizar
	.left = 10: .top = 38
	.width = 195
	.MaxLength = 40  
END WITH

with cmd_CancelButton
	.parent = frm_Localizar
	.left = 120: .top = 70: .width = 80: .height = 25
	.kind = 2
end with

with cmd_OkButton
	.parent = frm_Localizar
	.left = 10: .top = 70: .width = 80: .height = 25
	.kind = 1
	.onclick = cmd_OkButton_Click
end with

DIM OpenDialog AS QOpenDialog
OpenDialog.InitialDir = CurDir$
OpenDialog.Filter = "Access Files (*.mdb)|*.mdb"
IF OpenDialog.Execute THEN
    IF FileExists(OpenDialog.FileName) = 0 THEN END
END IF
IF OpenDialog.FileName="" THEN END

DIM strTMP as STRING
DIM pntTMP as INTEGER
DIM strRes as STRING

DIM DB1 as qDATABASE 'New Object qDATABASE
DIM TB1 as qSQLTABLE 'New Object qSQLTABLE

strRes = DB1.OpenDataBase(OpenDialog.FileName, dbMSACCESS) 'CloseDataBase obligatory
IF  strRes <> "0" THEN 
    SHOWMESSAGE strRes
    END
END IF

strRes = TB1.DataBaseExtends(DB1.GetSTMT)
IF  strRes <> "0" THEN SHOWMESSAGE strRes


IF VerificaDB = -1 THEN
    strRes = TB1.ExecSQL("CREATE TABLE AddressBook (Nome TEXT(40), Endereco TEXT(40), Telefone TEXT(15), EMail TEXT(40), Observacoes TEXT(200))")
    IF  strRes <> "0" THEN SHOWMESSAGE strRes  
END IF

strRes = TB1.OpenSQL("SELECT * FROM AddressBook ORDER BY Nome")
IF  strRes <> "0" THEN SHOWMESSAGE strRes  
LoadData


Form.ShowModal
TB1.CloseSQL
TB1.CloseDatabase

DB1.CloseDataBase

FUNCTION VerificaDB() as INTEGER
    DIM i as INTEGER
    
    FOR i = 0 TO DB1.TableCount - 1
        IF DB1.Table.Name(i) = "AddressBook" THEN
            VerificaDB = i
            EXIT FUNCTION
        END IF
    NEXT i
    
    VerificaDB = -1
END FUNCTION

SUB LoadData
    IF TB1.RecCount = 0 THEN
        Edit1.Text = "  0 / 0"
        Edit2.Text = ""
        Edit3.Text = ""
        Edit4.Text = ""
        Edit5.Text = ""
        Edit6.Text = ""
        EXIT SUB
    END IF
    Edit1.Text = "  " + STR$(TB1.PointerPosition + 1) + " / " + STR$(TB1.RecCount)
    Edit2.Text = TB1.strData(1)
    Edit3.Text = TB1.strData(2)
    Edit4.Text = TB1.strData(3)
    Edit5.Text = TB1.strData(4)
    Edit6.Text = TB1.strData(5)
END SUB

SUB ClearData
    Edit1.Text = ""
    Edit2.Text = ""
    Edit3.Text = ""
    Edit4.Text = ""
    Edit5.Text = ""
    Edit6.Text = ""
END SUB

SUB Button1_Click ' Voltar
    IF TB1.PointerPosition > 0 THEN
        strRes = TB1.MovePreview
        IF strRes = "0" THEN 
            LoadData 
        ELSE 
            SHOWMESSAGE strRes
        END IF
    END IF
END SUB

SUB Button2_Click ' Avancar
    IF TB1.PointerPosition < (TB1.RecCount - 1) THEN
        strRes = TB1.MoveNext
        IF strRes = "0" THEN 
            LoadData 
        ELSE 
            SHOWMESSAGE strRes
        END IF
    END IF
END SUB

SUB Button3_Click ' Adcionar
    ClearData
    EnableEdits(1)
    Button1.Visible = 0
    Button4.Visible = 0
    Button5.Visible = 0
    Button6.Visible = 0
    Button2.Caption = "CANCELAR"
    Button2.OnClick = Button2_CANCELAR
    Button3.Caption = "SALVAR"
    Button3.OnClick = Button3_SALVAR
    SetFocus(Edit2.Handle)
END SUB

SUB Button4_Click ' Editar
    IF TB1.RecCount = 0 THEN EXIT SUB
    strTMP = "WHERE(Nome = '" + Edit2.Text + "' AND Endereco = '" + Edit3.Text + "')"
    pntTMP = TB1.PointerPosition
    EnableEdits(1)
    Button1.Visible = 0
    Button4.Visible = 0
    Button5.Visible = 0
    Button6.Visible = 0
    Button2.Caption = "CANCELAR"
    Button2.OnClick = Button2_CANCELAR
    Button3.Caption = "SALVAR"
    Button3.OnClick = Button3_SALVAR_EDIT
    SetFocus(Edit2.Handle)
END SUB

SUB Button5_Click ' Localizar
    txt_Nome.TEXT = ""
    SetFocus(txt_Nome.HANDLE)
    frm_Localizar.SHOWMODAL
END SUB

SUB Button6_Click ' Excluir
    IF TB1.RecCount = 0 THEN EXIT SUB
    TB1.CloseSQL
    strRes = TB1.ExecSQL("DELETE FROM AddressBook WHERE(Nome = '" + Edit2.Text + "' AND Endereco = '" + Edit3.Text + "')")
    IF strRes <> "0" THEN SHOWMESSAGE strRes
    strRes = TB1.OpenSQL("SELECT * FROM AddressBook ORDER BY Nome")
    IF strRes <> "0" THEN SHOWMESSAGE strRes
    LoadData
END SUB

SUB Button7_Click ' Fechar
    Form.Close
END SUB

SUB Button2_CANCELAR
    EnableEdits(0)
    Button1.Visible = 1
    Button4.Visible = 1
    Button5.Visible = 1
    Button6.Visible = 1
    Button2.Caption = "Avançar >>"
    Button2.OnClick = Button2_Click
    Button3.Caption = "Adcionar"
    Button3.OnClick = Button3_Click
    LoadData
END SUB

SUB Button3_SALVAR 'Adcionar
    IF Len(Edit2.Text) > 0 THEN
        TB1.CloseSQL
        strRes = TB1.ExecSQL("INSERT INTO AddressBook VALUES('" + Edit2.Text + "', '" + Edit3.Text + "', '" + Edit4.Text + "', '" + Edit5.Text + "', '" + Edit6.Text + "')")
        IF strRes <> "0" THEN SHOWMESSAGE strRes
        strRes = TB1.OpenSQL("SELECT * FROM AddressBook ORDER BY Nome")
        IF strRes <> "0" THEN SHOWMESSAGE strRes
    END IF

    EnableEdits(0)
    Button1.Visible = 1
    Button4.Visible = 1
    Button5.Visible = 1
    Button6.Visible = 1
    Button2.Caption = "Avançar >>"
    Button2.OnClick = Button2_Click
    Button3.Caption = "Adcionar"
    Button3.OnClick = Button3_Click
    LoadData
END SUB

SUB Button3_SALVAR_EDIT
    IF Len(Edit2.Text) > 0 THEN
        TB1.CloseSQL
        strRes = TB1.ExecSQL("UPDATE AddressBook SET Nome = '" + Edit2.Text + "', Endereco = '" + Edit3.Text + "', Telefone = '" + Edit4.Text + "', Email = '" + Edit5.Text + "', Observacoes = '" + Edit6.Text + "' " + strTMP)
        IF strRes <> "0" THEN SHOWMESSAGE strRes
        strRes = TB1.OpenSQL("SELECT * FROM AddressBook ORDER BY Nome")
        IF strRes <> "0" THEN SHOWMESSAGE strRes
        
        DIM i as INTEGER
        FOR i = 0 TO pntTMP - 1
            TB1.MoveNext
        NEXT i
    END IF
    
    EnableEdits(0)
    Button1.Visible = 1
    Button4.Visible = 1
    Button5.Visible = 1
    Button6.Visible = 1
    Button2.Caption = "Avançar >>"
    Button2.OnClick = Button2_Click
    Button3.Caption = "Adcionar"
    Button3.OnClick = Button3_Click
    LoadData
END SUB

SUB EnableEdits(Enabled_0_1 as INTEGER)
    Edit2.Enabled = Enabled_0_1
    Edit3.Enabled = Enabled_0_1
    Edit4.Enabled = Enabled_0_1
    Edit5.Enabled = Enabled_0_1
    Edit6.Enabled = Enabled_0_1
    IF Enabled_0_1 = 0 THEN
        Edit2.Color = 14935273
        Edit3.Color = 14935273
        Edit4.Color = 14935273
        Edit5.Color = 14935273
        Edit6.Color = 14935273
    ELSE
        Edit2.Color = 16777215
        Edit3.Color = 16777215
        Edit4.Color = 16777215
        Edit5.Color = 16777215
        Edit6.Color = 16777215    
    END IF
END SUB

SUB cmd_OkButton_Click
    IF TB1.RecCount = 0 THEN EXIT SUB
    IF LEN(txt_Nome.TEXT) = 0 THEN EXIT SUB
    
    
    
    DIM i as INTEGER
    DIM pAtual as INTEGER
    DIM pAchou as BYTE
    
    pAtual = TB1.PointerPosition
    pAchou = 0
    
    TB1.MoveFirst
    
    FOR i = 0 to TB1.RecCount -1
        IF i > 0 THEN TB1.MoveNext
        IF INSTR(UCASE$(TB1.strData(1)), UCASE$(txt_Nome.TEXT)) > 0 THEN
            pAchou = 1
            EXIT FOR
        END IF
    NEXT i
    
    IF pAchou = 0 THEN
        FOR i = 1  TO (TB1.RecCount - (pAtual + 1))
            TB1.MovePreview
        NEXT i
    END IF
    LoadData
END SUB