//////////////////////////////////////////////////////////////////////////////////////////////
                    fishySQL.as
//////////////////////////////////////////////////////////////////////////////////////////////

package {
    import flash.display.Sprite;
    import flash.display.StageAlign;
    import flash.display.StageScaleMode;
    import flash.events.MouseEvent;

    import qnx.ui.buttons.LabelButton;
    import qnx.ui.listClasses.ListSelectionMode;
    import qnx.ui.listClasses.TileList;
    import qnx.ui.text.Label;
    import qnx.ui.text.TextInput;

    [SWF(width="1024", height="600", backgroundColor="#424242", frameRate="30")]
    public class fishySQL extends Sprite {
        private var sqlData:SQLFunctions;

        private var inputUSERNAME:TextInput;
        private var inputUSERPASSWORD:TextInput;
        private var inputUSERDESCRIPTION:TextInput;

        private var userList:TileList;

        public function fishySQL() {
            super();
            // support autoOrients
            stage.align = StageAlign.TOP_LEFT;
            stage.scaleMode = StageScaleMode.NO_SCALE;

            sqlData = new SQLFunctions();
            theInputs();
        }
        private function theInputs():void {
            inputUSERNAME = new TextInput();
            inputUSERNAME.prompt = "User Name";
            inputUSERNAME.setPosition(65, 60);
            addChild(inputUSERNAME);

            inputUSERPASSWORD = new TextInput();
            inputUSERPASSWORD.prompt = "User Password";
            inputUSERPASSWORD.setPosition(65, 100);
            addChild(inputUSERPASSWORD);

            inputUSERDESCRIPTION = new TextInput();
            inputUSERDESCRIPTION.prompt = "User Description";
            inputUSERDESCRIPTION.setPosition(65, 140);
            addChild(inputUSERDESCRIPTION);

            var submitData:LabelButton = new LabelButton();
            submitData.label = "Save";
            submitData.setPosition(600, 60);
            submitData.addEventListener(MouseEvent.CLICK, writeData);
            addChild(submitData);

            var delData:LabelButton = new LabelButton();
            delData.label = "Delete";
            delData.setPosition(600, 110);
            delData.addEventListener(MouseEvent.CLICK, deleteData);
            addChild(delData);

            var refreshTable:LabelButton = new LabelButton();
            refreshTable.label = "Refresh";
            refreshTable.setPosition(600, 175);
            refreshTable.addEventListener(MouseEvent.CLICK, makeTable);
            addChild(refreshTable);

            var useRecordBTN:LabelButton = new LabelButton();
            useRecordBTN.label = "Use";
            useRecordBTN.setPosition(800, 60);
            useRecordBTN.addEventListener(MouseEvent.CLICK, useRecord);
            addChild(useRecordBTN);

        }
        private function writeData(e:MouseEvent):void {
            sqlData.writeData(inputUSERNAME.text, inputUSERPASSWORD.text, inputUSERDESCRIPTION.text);
            inputUSERNAME.text = "";
            inputUSERPASSWORD.text = "";
            inputUSERDESCRIPTION.text = "";
        }
        private function deleteData(e:MouseEvent):void {
            sqlData.removeRecord(userList.selectedIndex);
        }
        private function makeTable(e:MouseEvent):void {
            userList = new TileList();
            userList.dataProvider = sqlData.getTableData();
            userList.setPosition(65, 225);
            userList.setSize(894, 350);
            userList.columnCount = 3;
            userList.columnWidth = 300;
            userList.rowCount = sqlData.getCount();
            userList.rowHeight = 45;
            userList.cellPadding = 1;
            userList.selectionMode = ListSelectionMode.SINGLE;
            addChild(userList);
        }
        private function useRecord(e:MouseEvent):void {
            sqlData.giveRecord(userList.selectedIndex);
            var suckaFish:Array = new Array(sqlData.recordArray.slice(0).USERNAME);
            trace(suckaFish);
            inputUSERNAME.text = sqlData.recordArray.slice(0).USERNAME;
            inputUSERPASSWORD.text = sqlData.recordArray.(1).label;
        }
    }
}


/*
    statement.text = "SELECT * FROM SITES WHERE SITE_ID = ?"
    statement.parameters[0] = SitePicker.selectedItem.SITE_ID;
    statement.execute();
    siteData.source = statement.getResult().data;
    SiteAddressLBL.text = siteData.getItemAt(0).SiteAddress;
    ProviderLBL.text = siteData.getItemAt(0).Provider;
    AccountNumberLBL.text = siteData.getItemAt(0).AccountNumber;
*/



//////////////////////////////////////////////////////////////////////////////////////////////
                    sqlFunction.as
//////////////////////////////////////////////////////////////////////////////////////////////

package {
    import flash.data.SQLConnection;
    import flash.data.SQLResult;
    import flash.data.SQLStatement;
    import flash.filesystem.File;

    import qnx.ui.data.DataProvider;

    public class SQLFunctions {
        private var dbName:String = "fishySQL.db";
        private var dbFile:File = File.applicationStorageDirectory.resolvePath(dbName);
        private var dbConn:SQLConnection;
        private var dbSTMT:SQLStatement;
        private var sqlResult:SQLResult;

        private var databaseDP:DataProvider;
        public var recordArray:Array;

        private var totalRows:int;

        public function SQLFunctions() {
            if (!dbFile.exists) {
                createDB()
            }
        }
        public function createDB():void {
            createConnection();
            dbSTMT.text = "CREATE TABLE IF NOT EXISTS USERTABLE (USER_ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "USERNAME TEXT, " +
                "USERPASSWORD TEXT, " +
                "USERDESCRIPTION TEXT" +
                ")";
            dbSTMT.execute();
            dbSTMT.text = "INSERT INTO USERTABLE (" +
                "USERNAME, " +
                "USERPASSWORD, " +
                "USERDESCRIPTION" +
                ") VALUES (?, ?, ?)";
            dbSTMT.parameters[0] = "John Doe";
            dbSTMT.parameters[1] = "p455w0rd";
            dbSTMT.parameters[2] = "PW IS BAD!";
            dbSTMT.execute();
            closeConnection();
        }
        public function createConnection():void {
            dbConn = new SQLConnection();
            dbConn.open(dbFile);
            dbSTMT = new SQLStatement();
            dbSTMT.sqlConnection = dbConn;
        }
        public function closeConnection():void {
            dbConn.close();
        }
        public function getTableData():DataProvider {
            createConnection();
            dbSTMT.text = "SELECT * FROM USERTABLE";
            dbSTMT.execute();
            sqlResult = dbSTMT.getResult();
            totalRows = sqlResult.data.length;
            databaseDP = new DataProvider();
            for each(var obj:Object in sqlResult.data){
                if(obj != null){
                    databaseDP.addItem({label:obj["USERNAME"]});
                    databaseDP.addItem({label:obj["USERPASSWORD"]});
                    databaseDP.addItem({label:obj["USERDESCRIPTION"]});
                }
            }
            return databaseDP;
            closeConnection();
        }
        public function getCount():int {
            return totalRows;
        }
        public function writeData(inputUN:String, inputUP:String, inputUD:String):void {
            createConnection();
            dbSTMT.text = "INSERT INTO USERTABLE (" +
                "USERNAME, " +
                "USERPASSWORD, " +
                "USERDESCRIPTION" +
                ") VALUES (?, ?, ?)";
            dbSTMT.parameters[0] = inputUN;
            dbSTMT.parameters[1] = inputUP;
            dbSTMT.parameters[2] = inputUD;
            dbSTMT.execute();
            closeConnection();
        }
        public function removeRecord(delID:int):void {
            trace("I GOT HERE");
            createConnection();
            dbSTMT.text = "DELETE FROM USERTABLE WHERE USER_ID = " + delID + "";
            dbSTMT.execute();
            closeConnection();
            trace("I FINISHED");
        }
        public function giveRecord(selectedUser:int):void {
            createConnection();
            dbSTMT.text = "SELECT * FROM USERTABLE WHERE USER_ID = " + selectedUser + "";
            dbSTMT.execute();
            recordArray = new Array();
            for each(var obj:Object in dbSTMT.getResult().data){
                if(obj != null){
                    recordArray.push({label:obj["USERNAME"]});
                    recordArray.push({label:obj["USERPASSWORD"]});
                    recordArray.push({label:obj["USERDESCRIPTION"]});
                }
            }

            recordArray = new Array(dbSTMT.getResult().data);
            closeConnection();
        }
    }
}