// -*- C++ -*- generated by wxGlade 0.6.3 on Fri Feb 22 21:22:02 2008

#include "Query2Excel.h"
#include "ExcelXMLcpp.h"

// begin wxGlade: ::extracode
// end wxGlade

/* Variabili globali  */
wxString DSN;        /* Nome dtabase ODBC  */
wxString UserName;
wxString Password;
wxString SQL;        /* Select generata automaticamente  */
wxString NomeTabella;          /* Nome Tabella scelta    */
wxDbConnectInf *DbConnectInf;  /* Blocco di controllo ODBC  */
wxDb *db;            /* Oggetto database connesso  */
wxDbInf *dbInf;      /* Struttura contenente informazioni del catalogo  */
wxDbTableInf *pTableInf;   /* array informazioni del catalogo delle tabelle */
wxDbColInf *pDbColInf;     /* array informazioni del catalogo delle colonne di una tabella */
int nTables;

MyFrame::MyFrame(wxWindow* parent, int id, const wxString& title, const wxPoint& pos, const wxSize& size, long style):
    wxFrame(parent, id, title, pos, size, wxCAPTION|wxCLOSE_BOX|wxMINIMIZE_BOX|wxMAXIMIZE_BOX|wxSYSTEM_MENU|wxRESIZE_BORDER|wxTAB_TRAVERSAL|wxCLIP_CHILDREN)
{
    // begin wxGlade: MyFrame::MyFrame
    frame_1_statusbar = CreateStatusBar(1, 0);
    label_1 = new wxStaticText(this, wxID_ANY, wxT("Trasforma il risultato di una Query SQL in un foglio Excel XML"));
    label_2 = new wxStaticText(this, wxID_ANY, wxT("Database ODBC (DNS name):"));
    txtDNS = new wxTextCtrl(this, 1001, wxEmptyString);
    label_3 = new wxStaticText(this, wxID_ANY, wxT("Username:"));
    txtUser = new wxTextCtrl(this, 1002, wxEmptyString);
    label_4 = new wxStaticText(this, wxID_ANY, wxT("Password:"));
    txtPassword = new wxTextCtrl(this, 1003, wxEmptyString, wxDefaultPosition, wxDefaultSize, wxTE_PASSWORD);
    Connetti = new wxButton(this, 1004, wxT("Connetti"));
    label_5 = new wxStaticText(this, wxID_ANY, wxT("Tabelle:  (doppio Click per generare la Select)"));
    label_6 = new wxStaticText(this, wxID_ANY, wxT("Select SQL di estrazione dati:  (anche richiamo di StoredProcedure o copia-incolla)"));
    const wxString *ListaTabelle_choices = NULL;
    ListaTabelle = new wxListBox(this, 1005, wxDefaultPosition, wxDefaultSize, 0, ListaTabelle_choices, wxLB_SINGLE|wxLB_SORT);
    Query = new wxTextCtrl(this, 1006, wxEmptyString, wxDefaultPosition, wxDefaultSize, wxTE_PROCESS_ENTER|wxTE_PROCESS_TAB|wxTE_MULTILINE|wxHSCROLL);
    Esporta = new wxButton(this, 1007, wxT("Esporta"));
    Vedi = new wxButton(this, 1008, wxT("Vedi"));
    Chiudi = new wxButton(this, 1009, wxT("Chiudi"));

    set_properties();
    do_layout();
    // end wxGlade
}


BEGIN_EVENT_TABLE(MyFrame, wxFrame)
    // begin wxGlade: MyFrame::event_table
    EVT_BUTTON(1004, MyFrame::Connette)
    EVT_LISTBOX_DCLICK(1005, MyFrame::GenSelect)
    EVT_BUTTON(1007, MyFrame::EsportaExcel)
    EVT_BUTTON(1008, MyFrame::EsegueSelect)
    EVT_BUTTON(1009, MyFrame::ChiudiApp)
    // end wxGlade
END_EVENT_TABLE();

/* Si connette al database ODBC */
void MyFrame::Connette(wxCommandEvent &event)
{   
    int n;
    wxString s, s1;
     
    /* recupera i parametri di connessione dalla form */
    DSN = txtDNS->GetLabel();
    UserName = txtUser->GetLabel();
    Password = txtPassword->GetLabel();
    if (DSN == "") {
       wxMessageBox("Nome Database mancante ");
       return;
    }
    /* Prepara il blocco di controllo  */
    DbConnectInf = new wxDbConnectInf(NULL, DSN, UserName, Password, "");
    /* Apre la Connessione  */
    db = wxDbGetConnection(DbConnectInf);
    if (db == NULL) {    
       wxMessageBox("Errore in apertura database '" + DSN + "'");
       return;
    }
    /* Ottiene l'elenco tabelle dal catalogo del DB  */
    dbInf = db->GetCatalog();
    nTables = dbInf->numTables;
    pTableInf = dbInf->pTableInf;
    
    /* Riempie la listbox  */
    ListaTabelle->Clear(); 
    Query->Clear(); 
    SQL = "";
    NomeTabella = "";
    for (n = 0; n < nTables; n++) { 
        s = (wxString)pTableInf[n].tableType;
        /* Scarta le tabelle di sistema  */
        if (s == "TABLE") { 
            s1 = (wxString)pTableInf[n].tableName; 
            ListaTabelle->Append(s1); 
        }   
    }   
}


void MyFrame::GenSelect(wxCommandEvent &event)
{
   int n;
   UWORD nCols;
   wxString s1; //, typeName;
   //wxArrayInt TabSel;
   
   n = ListaTabelle->GetSelection();
   NomeTabella = ListaTabelle->GetString(n);
   SQL = "SELECT ";
   /* Recupera il nome delle colonne dal catalogo del DB  */
   pDbColInf = db->GetColumns(NomeTabella, &nCols);
   //s1.Printf(wxT("%d"), nCols);

   for (n = 0; n < nCols; n++) {
       s1 = (wxString)pDbColInf[n].colName;
       if (n > 0) SQL += ", "; 
       if ((n > 0) && (n%6) == 0) SQL += "\n";
       SQL += s1;
       
   }     
   SQL += "\nFROM " + NomeTabella;
   Query->SetValue(SQL);
   

}


void MyFrame::EsportaExcel(wxCommandEvent &event)
{
    int n, nRiga;
    short nCols;
    wxString s, s1;
    int   dbDataType;  // tipo dato della colonna 1=string, 2=int, 4=Datetime, 6=blob

    SDWORD cb;
    char Val[4000];
    
    SQL = Query->GetLabel();
    if (db == NULL || SQL == "") return;
    if (NomeTabella == "") return;
    // Esegue la query
    if (!db->ExecSql(SQL.c_str(), &pDbColInf, nCols))
    {  // ERROR
       wxMessageBox("Errore nella Query ");
       return;
    }
    /* inizializza Excel  */
    xlsExcel x1((string)NomeTabella + ".xml");
    xlsSheetStruct Sh = x1.defaultSheet;

    s = "Esportazione di: " + NomeTabella;
    strcpy(Sh.Description, s.c_str() );
    /* aggiunge uno stile per l'intestazione campi  */
    xlsStyleStruct st = x1.defaultStyle;
    strcpy(st.ID,"bold");
    st.FontBold  = '1';
    st.FontItalic  = '1';
    x1.xlsAddStyle(st);
    x1.xlsAddSheet("Query", Sh);
    x1.xlsWriteCell((string)("Esportazione di: " + NomeTabella));
    x1.xlsNewLine(1);
    /* Recupera i nomi dei campi e intesta le colonne */
    for (n = 0; n < nCols; n++) {
       x1.xlsWriteCell((string)pDbColInf[n].colName,'S',"bold");
    }
    x1.xlsNewLine();

    /* Riempie la Grid, almeno per le prime 2000 righe  */
    nRiga = 0;
    while (db->GetNext()) {
       for (n = 0; n < nCols; n++) {
          /* recupera le caratteristiche di ogni colonna  */
          dbDataType = pDbColInf[n].dbDataType;
            
          /* legge il contenuto di ogni campo e lo trasforma in stringa  */
          db->GetData(n+1, SQL_C_WXCHAR, &Val, sizeof Val, &cb);
          if (cb == SQL_NULL_DATA) strcpy(Val, "");  /* se è NULL svuota il risultato  */
          if (dbDataType == 2) x1.xlsWriteCell((string)Val, 'N');
          else x1.xlsWriteCell((string)Val, 'S');
       }
       x1.xlsNewLine();
       nRiga += 1;
       if (nRiga >= 20000) {
          wxMessageBox("E' stato raggiunto il massimo di 20000 righe esportate", "Avviso");
          break;
       }
    }
    x1.xlsClose();

    s.Printf("%d", nRiga);
    wxMessageBox("Sono state esportate in formato Excel " + s + " righe" );
}


void MyFrame::EsegueSelect(wxCommandEvent &event)
{
   
    SQL = Query->GetLabel();
    if (db == NULL || SQL == "") return;

    /* Istanzia la form di visualizzazione del risultato Query  */
    MyQuery* Query = new MyQuery(NULL, 1, wxT("Risultato della Query"));
    Query->RiempieGrid(NomeTabella);
    /* mostra la form  */
    Query->Show();
}


void MyFrame::ChiudiApp(wxCommandEvent &event)
{
    /* Prima chiude la connessione al DB  */
    if (db)
    {
        wxDbFreeConnection(db);
        db = NULL;
    }
    /* Chiude la form  */
    Close();
}

               
// wxGlade: add MyFrame event handlers


void MyFrame::set_properties()
{
    // begin wxGlade: MyFrame::set_properties
    SetTitle(wxT("Query2Excel"));
    SetSize(wxSize(800, 600));
    SetBackgroundColour(wxColour(208, 208, 208));
    int frame_1_statusbar_widths[] = { -1 };
    frame_1_statusbar->SetStatusWidths(1, frame_1_statusbar_widths);
    const wxString frame_1_statusbar_fields[] = {
        wxEmptyString
    };
    for(int i = 0; i < frame_1_statusbar->GetFieldsCount(); ++i) {
        frame_1_statusbar->SetStatusText(frame_1_statusbar_fields[i], i);
    }
    label_1->SetBackgroundColour(wxColour(208, 208, 208));
    label_1->SetFont(wxFont(12, wxDEFAULT, wxNORMAL, wxBOLD, 0, wxT("")));
    txtDNS->SetMinSize(wxSize(140, 24));
    txtDNS->SetFont(wxFont(10, wxDEFAULT, wxNORMAL, wxNORMAL, 0, wxT("")));
    txtDNS->SetToolTip(wxT("Nome del driver ODBC"));
    txtDNS->SetFocus();
    txtUser->SetFont(wxFont(10, wxDEFAULT, wxNORMAL, wxNORMAL, 0, wxT("")));
    txtUser->SetToolTip(wxT("Utente di accesso al database"));
    txtPassword->SetFont(wxFont(10, wxDEFAULT, wxNORMAL, wxNORMAL, 0, wxT("")));
    txtPassword->SetToolTip(wxT("Password di accesso al database"));
    Connetti->SetFont(wxFont(8, wxDEFAULT, wxNORMAL, wxBOLD, 0, wxT("")));
    Connetti->SetToolTip(wxT("Si connette al Database"));
    label_5->SetMinSize(wxSize(250, 13));
    ListaTabelle->SetMinSize(wxSize(250, 420));
    Query->SetFont(wxFont(12, wxMODERN, wxNORMAL, wxNORMAL, 0, wxT("")));
    Esporta->SetFont(wxFont(8, wxDEFAULT, wxNORMAL, wxBOLD, 0, wxT("")));
    Esporta->SetToolTip(wxT("Esegue la Query ed esporta il risultato in formato Excel"));
    Vedi->SetFont(wxFont(8, wxDEFAULT, wxNORMAL, wxBOLD, 0, wxT("")));
    Vedi->SetToolTip(wxT("Mostra i dati risultanti della Select"));
    Chiudi->SetFont(wxFont(8, wxDEFAULT, wxNORMAL, wxBOLD, 0, wxT("")));
    Chiudi->SetToolTip(wxT("Termina l'applicazione"));
    // end wxGlade
}


void MyFrame::do_layout()
{
    // begin wxGlade: MyFrame::do_layout
    wxBoxSizer* sizer_1 = new wxBoxSizer(wxVERTICAL);
    wxBoxSizer* sizer_5 = new wxBoxSizer(wxHORIZONTAL);
    wxBoxSizer* sizer_4 = new wxBoxSizer(wxHORIZONTAL);
    wxBoxSizer* sizer_6 = new wxBoxSizer(wxHORIZONTAL);
    wxBoxSizer* sizer_2 = new wxBoxSizer(wxHORIZONTAL);
    sizer_1->Add(label_1, 0, wxALL, 4);
    sizer_2->Add(label_2, 0, wxLEFT|wxTOP|wxBOTTOM, 10);
    sizer_2->Add(txtDNS, 0, wxALL, 4);
    sizer_2->Add(label_3, 0, wxLEFT|wxTOP|wxBOTTOM, 10);
    sizer_2->Add(txtUser, 0, wxALL, 4);
    sizer_2->Add(label_4, 0, wxLEFT|wxTOP|wxBOTTOM, 10);
    sizer_2->Add(txtPassword, 0, wxALL, 4);
    sizer_2->Add(80, 20, 1, wxEXPAND|wxALIGN_RIGHT, 0);
    sizer_2->Add(Connetti, 0, wxALL, 6);
    sizer_1->Add(sizer_2, 1, wxEXPAND, 0);
    sizer_6->Add(label_5, 0, wxALL|wxALIGN_BOTTOM, 4);
    sizer_6->Add(label_6, 0, wxALL|wxALIGN_BOTTOM, 4);
    sizer_1->Add(sizer_6, 1, wxEXPAND, 0);
    sizer_4->Add(ListaTabelle, 0, wxALL|wxEXPAND, 4);
    sizer_4->Add(Query, 2, wxALL|wxEXPAND, 4);
    sizer_1->Add(sizer_4, 14, wxEXPAND, 0);
    sizer_5->Add(Esporta, 0, wxALL, 4);
    sizer_5->Add(20, 20, 1, wxEXPAND, 0);
    sizer_5->Add(Vedi, 0, wxALL, 4);
    sizer_5->Add(20, 20, 1, wxEXPAND, 0);
    sizer_5->Add(Chiudi, 0, wxALL, 4);
    sizer_1->Add(sizer_5, 1, wxEXPAND, 0);
    SetSizer(sizer_1);
    Layout();
    Centre();
    // end wxGlade
}


MyQuery::MyQuery(wxWindow* parent, int id, const wxString& title, const wxPoint& pos, const wxSize& size, long style):
    wxFrame(parent, id, title, pos, size, wxDEFAULT_FRAME_STYLE)
{
    int nRighe, nCols;
    
    // begin wxGlade: MyQuery::MyQuery
    TitoloTabella = new wxStaticText(this, wxID_ANY, wxT("Tabella"));
    QueryGrid = new wxGrid(this, wxID_ANY);

    set_properties();
    do_layout();
    // end wxGlade
    
    nRighe = QueryGrid->GetNumberRows();
    nCols = QueryGrid->GetNumberCols();
    /* svuota la grid esistente  */
    QueryGrid->DeleteRows(0,nRighe);
    QueryGrid->DeleteCols(0,nCols);
    QueryGrid->SetRowLabelSize(50);
    
}

void MyQuery::RiempieGrid(wxString Titolo) {
    int n, nRiga;
    short nCols;
    wxString s, s1;
    int dbDataType;  // tipo dato della colonna 1=string, 2=int, 4=Datetime, 6=blob

    SDWORD cb;
    char Val[256];
    
    TitoloTabella->SetLabel(Titolo);
    // Esegue la query
    if (!db->ExecSql(SQL.c_str(), &pDbColInf, nCols))
    {  // ERROR
       wxMessageBox("Errore nella Query ");
       return;
    }
    
    /* Inserisce le colonne della Grid */
    QueryGrid->InsertCols(0, nCols);

    /* Recupera i nomi dei campi e intesta le colonne */
    for (n = 0; n < nCols; n++) {
       /* recupera le caratteristiche di ogni colonna  */
       dbDataType  = pDbColInf[n].dbDataType;
       QueryGrid->SetColLabelValue(n,(wxString)pDbColInf[n].colName);
    }
    /* Riempie la Grid, almeno per le prime 2000 righe  */
    nRiga = 0;     
    while (db->GetNext()) {
       QueryGrid->InsertRows(nRiga, 1);
       for (n = 0; n < nCols; n++) {
          /* legge il contenuto di ogni campo e lo trasforma in stringa  */
          db->GetData(n+1, SQL_C_WXCHAR, &Val, sizeof Val, &cb);
          if (cb == SQL_NULL_DATA) strcpy(Val, "");  /* se è NULL svuota il risultato  */
          //wxMessageBox((wxString)pDbColInf[n].colName + " - " + (wxString)Val);
          QueryGrid->SetCellValue(nRiga, n, (wxString)Val);
       }
       nRiga += 1;
       if (nRiga >= 2000) {
          wxMessageBox("E' stato raggiunto il massimo di 2000 righe caricate", "Avviso");
          break;
       }   
    }
}

void MyQuery::set_properties()
{
    // begin wxGlade: MyQuery::set_properties
    SetTitle(wxT("Risultato della Query"));
    SetSize(wxSize(750, 570));
    SetBackgroundColour(wxColour(208, 208, 208));
    TitoloTabella->SetForegroundColour(wxColour(255, 0, 0));
    TitoloTabella->SetFont(wxFont(10, wxDEFAULT, wxNORMAL, wxBOLD, 0, wxT("")));
    QueryGrid->CreateGrid(1, 2);
    QueryGrid->EnableEditing(false);
    QueryGrid->SetMinSize(wxSize(713, 494));
    // end wxGlade
}


void MyQuery::do_layout()
{
    // begin wxGlade: MyQuery::do_layout
    wxBoxSizer* sizer_3 = new wxBoxSizer(wxVERTICAL);
    wxBoxSizer* sizer_7 = new wxBoxSizer(wxHORIZONTAL);
    sizer_7->Add(TitoloTabella, 0, wxALL|wxEXPAND, 4);
    sizer_7->Add(20, 20, 1, wxALL|wxEXPAND, 4);
    sizer_3->Add(sizer_7, 0, wxEXPAND, 0);
    sizer_3->Add(QueryGrid, 6, wxALL|wxEXPAND, 4);
    SetSizer(sizer_3);
    Layout();
    Centre();
    // end wxGlade
}

