import flash.events.*;
import mx.events.*;
import mx.collections.ArrayCollection;
import org.libspark.snippets.controls.SQLComboBox;
import org.libspark.snippets.controls.SQLTextInput;
import org.libspark.snippets.controls.SQLList;
import org.libspark.snippets.controls.SQLLabel;

private function bOpenDB_click(event:MouseEvent):void {
    var file:File = new File();
    var filter:FileFilter = new FileFilter("DB File", "*.db");
    file.browseForOpen("Open Database file (*.db)", [filter]);
    file.addEventListener(Event.SELECT, dbFileSelected);
}

private function dbFileSelected(event:Event):void {
    //trace(event.target.nativePath);
    title = "ZIP Search - " + event.target.nativePath;
    GetTableList(File(event.target), SQLMode.READ);
}

private var connection:SQLConnection = null;

private function GetTableList(file:File, mode:String):void {
    if (connection != null) {
        if (connection.connected) {
            try {
                connection.close();
            } catch (error:Error) {
                trace("Failed:", error.message);
            }
        }
    }

    connection = new SQLConnection();
    try {
        connection.open(file, mode);
        cbPrefecture.sqlConnection = connection;
        cbCity.sqlConnection = connection;
        cbArea.sqlConnection = connection;  
        tZipCode.sqlConnection = connection;
        lAddress.sqlConnection = connection;
        lPrefecture.sqlConnection = connection;
        lCity.sqlConnection = connection;
        lArea.sqlConnection = connection;
        Records.sqlConnection = connection;
        cbPrefecture.executeQuery("select distinct prefecture as label from postcode");
        lPrefecture.executeQuery("select distinct prefecture as label from postcode");
    } catch (error:Error) {
        trace("Failed:", error.message);
    }
}

private function cbPrefecture_close(event:DropdownEvent):void {
    //trace("cbPrefecture close", event.target.selectedLabel);
    var sql:String = "select distinct city from postcode";
    sql += " where prefecture == '" + cbPrefecture.selectedLabel + "'";
    //trace(sql);
    cbCity.executeQuery(sql);
    cbArea.dataProvider = new Array();
    tZipCode.text = "";
    lAddress.dataProvider = new Array();
}

private function cbCity_close(event:DropdownEvent):void {
    //trace("cbCity close", event.target.selectedLabel);
    var sql:String = "select distinct area from postcode";
    sql += " where prefecture == '" + cbPrefecture.selectedLabel + "'";
    sql += " and city == '" + cbCity.selectedLabel + "'";
    //trace(sql);
    cbArea.executeQuery(sql);
    tZipCode.text = "";
    lAddress.dataProvider = new Array();
}

private function cbArea_close(event:DropdownEvent):void {
    //trace("cbArea close", event.target.selectedLabel);
    var sql:String = "select zip_code from postcode";
    sql += " where prefecture == '" + cbPrefecture.selectedLabel + "' ";
    sql += " and city == '" + cbCity.selectedLabel + "'";
    sql += " and area == '" + cbArea.selectedLabel + "'";
    //trace(sql);
    tZipCode.executeQuery(sql);
    lAddress.dataProvider = new Array();
}

private function lPrefecture_change(event:ListEvent):void {
    //trace("lPrefecture change", event.target.selectedItem);
    var sql:String = "select distinct city from postcode";
    sql += " where prefecture == '" + lPrefecture.selectedItem + "'";
    //trace(sql);
    lCity.executeQuery(sql);
    lArea.dataProvider = new Array();
    tZipCode.text = "";
    lAddress.dataProvider = new Array();    
}

private function lCity_change(event:ListEvent):void {
    //trace("lCity change", event.target.selectedItem);
    var sql:String = "select distinct area from postcode";
    sql += " where prefecture == '" + lPrefecture.selectedItem + "'";
    sql += " and city == '" + lCity.selectedItem + "'";
    //trace(sql);
    lArea.executeQuery(sql);
    tZipCode.text = "";
    lAddress.dataProvider = new Array();
}

private function lArea_change(event:ListEvent):void {
    //trace("lArea change", event.target.selectedItem);
    var sql:String = "select zip_code from postcode";
    sql += " where prefecture == '" + lPrefecture.selectedItem + "' ";
    sql += " and city == '" + lCity.selectedItem + "'";
    sql += " and area == '" + lArea.selectedItem + "'";
    //trace(sql);
    tZipCode.executeQuery(sql);
    lAddress.dataProvider = new Array();
}

private function onZIPChange(event:KeyboardEvent):void {
    if (tZipCode.text == "") {
        lAddress.dataProvider = new Array();
        return;
    }
    var sql:String = "select zip_code, prefecture, city, area from postcode";
    sql += " where zip_code like '" + tZipCode.text + "%'";
    sql += " order by zip_code";
    //trace(sql);
    lAddress.delimiter = " ";
    lAddress.executeQuery(sql);
    sql = "select count(*) from postcode";
    sql += " where zip_code like '" + tZipCode.text + "%'";
    Records.frontCaption = "Total: ";
    Records.rearCaption = " record(s)";
    Records.executeQuery(sql);
}

private function onAppClosing(event:Event):void {
    trace("Application closing...");
    connectionClose();
}

private function connectionClose():void {
    if (connection != null)
        if(connection.connected)
            connection.close();
}

private var cbPrefecture:SQLComboBox = new SQLComboBox();
private var cbCity:SQLComboBox = new SQLComboBox();
private var cbArea:SQLComboBox = new SQLComboBox();
private var tZipCode:SQLTextInput = new SQLTextInput();
private var tAddress:SQLTextInput = new SQLTextInput();
private var lPrefecture:SQLList = new SQLList();
private var lCity:SQLList = new SQLList();
private var lArea:SQLList = new SQLList();
private var lAddress:SQLList = new SQLList();
private var Records:SQLLabel = new SQLLabel;

private function init():void {
   addEventListener(Event.CLOSING, onAppClosing);
   cbPrefecture.x = 26; cbPrefecture.y = 90; cbPrefecture.width = 105;
   cbPrefecture.addEventListener(DropdownEvent.CLOSE, cbPrefecture_close);
   addChild(cbPrefecture);
   cbCity.x = 156; cbCity.y = 90; cbCity.width = 129;
   cbCity.addEventListener(DropdownEvent.CLOSE, cbCity_close);
   addChild(cbCity);
   cbArea.x = 305; cbArea.y = 90; cbArea.width = 303;
   cbArea.addEventListener(DropdownEvent.CLOSE, cbArea_close);
   addChild(cbArea);
   tZipCode.x = 90; tZipCode.y = 324; tZipCode.width = 80; tZipCode.height = 22; tZipCode.text = "";
   tZipCode.addEventListener(KeyboardEvent.KEY_UP, onZIPChange);
   addChild(tZipCode);
   lPrefecture.x = 26; lPrefecture.y = 132; lPrefecture.width = 105; lPrefecture.height = 160;
   lPrefecture.addEventListener(ListEvent.CHANGE, lPrefecture_change);
   addChild(lPrefecture);
   lCity.x = 156; lCity.y = 132; lCity.width = 129; lCity.height = 160;
   lCity.addEventListener(ListEvent.CHANGE, lCity_change);
   addChild(lCity);
   lArea.x = 305; lArea.y = 132; lArea.width = 303; lArea.height = 160;
   lArea.addEventListener(ListEvent.CHANGE, lArea_change);
   addChild(lArea);
   lAddress.x = 90; lAddress.y = 360; lAddress.width = 518; lAddress.height = 150;
   addChild(lAddress);
   Records.x = 468; Records.y = 520; Records.width = 140;
   addChild(Records);
}