<?xml version="1.0" encoding="utf-8"?>
<s:WindowedApplication xmlns:fx="http://ns.adobe.com/mxml/2009" 
                       xmlns:s="library://ns.adobe.com/flex/spark" 
                       xmlns:mx="library://ns.adobe.com/flex/mx">
    <fx:Declarations>
        <!-- 将非可视元素（例如服务、值对象）放在此处 -->
    </fx:Declarations>
    <fx:Script>
        <![CDATA[
            import mx.collections.ArrayCollection;
            private var con:SQLConnection;
            private var stmt:SQLStatement;
            private var dbFile:File = File.documentsDirectory.resolvePath("MacroEconomicResearchData.sqlite.db");
            
            /**
             * 打开文件选择对话框，选择 DB 文件
             */
            protected function selectDBFile(root:File):void 
            { 
                try
                { 
                    root.browseForSave("打开 DB 文件"); 
                    root.addEventListener(Event.SELECT, checkDBFileFormat);
                }
                catch(error:Error)
                {
                    trace("打开失败：" + error.message);
                }
            } 
            
            /**
             * 以字节流方式读取文件内容，检查文件是否 SQLite 格式
             */
            protected function checkDBFileFormat(event:Event):void 
            { 
                if(this.dbFile.exists)
                {
                    var stream:FileStream = new FileStream(); 
                    stream.open(this.dbFile, FileMode.READ);
                    var s:String = stream.readUTFBytes(20);
                    if(s.substr(0,16) == "SQLite format 3"){
                        stream.close();
                        trace("db file format ok fit");
                        
                        this.openDBFile();
                    }else{
                        stream.close();
                        trace("db file format not fit");
                        this.dbFile = File.documentsDirectory.resolvePath("MacroEconomicResearchData.sqlite.db");
                    }
                }
                else
                {
                    trace("new file");
                    this.openDBFile();
                }
                
            }
            
            /**
             * 以数据库方式异步打开文件
             */
            private function openDBFile():void{
                
                
                con = new SQLConnection();
                
                con.addEventListener(SQLEvent.OPEN, dbInitStruct);
                con.addEventListener(SQLErrorEvent.ERROR, dbErrorHandler);
                
                con.openAsync(this.dbFile);
            }
            
            /**
             * 数据库访问错误处理程式
             */
            private function dbErrorHandler(event:SQLErrorEvent):void 
            { 
                trace("Error message:", event.error.message); 
                trace("error Details:", event.error.details); 
            }
            
            /**
             * 批量SQL集合的游标指针
             */
            private var _execSQLCount:int = 0;
            /**
             * 批量SQL集合数组，不能用 ArrayList
             */
            private var _execSQLBatch:Array = new Array();
            /**
             * 执行完毕全部SQL语句后要调用的函数（无参数）
             */
            private var _execSQLCallLatency:Function;
            
            /**
             * 批量执行SQL语句
             */
            private function dbExecuteBatchSQL(evt:SQLEvent):void{
                this._execSQLCount++;
                
                trace(this._execSQLCount , this._execSQLBatch.length);
                
                if(this._execSQLCount > this._execSQLBatch.length){
                    
                    this._execSQLCount = 0;
                    this.stmt.removeEventListener(SQLEvent.RESULT, dbExecuteBatchSQL);
                    
                    if(this._execSQLCallLatency != null)
                    {
                        this._execSQLCallLatency.call();
                        //this._execSQLCallLatency = null;  DO NOT SET TO NULL, OR IT WILL NOT RUN
                    }
                }else{
                    var sql:String = this._execSQLBatch[this._execSQLCount-1] as String;
                    trace(sql);
                    this.stmt.text = sql;
                    this.stmt.execute();
                }
            }
            
            /**
             * 数据结构初始化，建立三个数据表
             */
            private function dbInitStruct(evt:SQLEvent):void
            {
                
                
                this._execSQLBatch = new Array();
                this._execSQLBatch.push("CREATE TABLE IF NOT EXISTS stockdaily (" +  
                    "    ID INTEGER PRIMARY KEY AUTOINCREMENT, " +  
                    "    StatDate DATE," + 
                    "    Symbol VARCHAR(30)," +
                    "    FinalPrice FLOAT," + 
                    "    HighPrice FLOAT," + 
                    "    LowPrice FLOAT," + 
                    "    DealVolume FLOAT," + 
                    "    DealMoney FLOAT," + 
                    "    RunDate DATETIME" +   
                    ");");
                this._execSQLBatch.push("CREATE TABLE IF NOT EXISTS stockdailywatch (" +  
                    "    ID INTEGER PRIMARY KEY AUTOINCREMENT, " +  
                    "    Symbol VARCHAR(30)," +
                    "    Name VARCHAR(30)" +
                    ");");
                this._execSQLBatch.push("CREATE TABLE IF NOT EXISTS stockmarketdaily (" +  
                    "    ID INTEGER PRIMARY KEY AUTOINCREMENT, " +  
                    "    StatDate DATE," + 
                    "    SHDealMoney FLOAT(8,2)," +
                    "    SZDealMoney FLOAT(8,2)," + 
                    "    SHDealMoneyComp FLOAT(6,2)," + 
                    "    SZDealMoneyComp FLOAT(6,2)," + 
                    "    SHAvgPE FLOAT(6,2)," + 
                    "    SZAvgPE FLOAT(6,2)," + 
                    "    SHAvgPB FLOAT(6,2)," + 
                    "    SZAvgPB FLOAT(6,2)," + 
                    "    SHAvgPrice FLOAT(7,2)," + 
                    "    SZAvgPrice FLOAT(7,2)," + 
                    "    RunDateTime DATETIME" +   
                    ");");
                
                
                this.stmt = new SQLStatement(); 
                this.stmt.sqlConnection = con;
                this.stmt.addEventListener(SQLErrorEvent.ERROR, dbErrorHandler); 
                
                this.stmt.addEventListener(SQLEvent.RESULT, dbExecuteBatchSQL); 
                
                this._execSQLCallLatency = this.dbInitData;
                
                this.dbExecuteBatchSQL(null);
                
            }
            
            /**
             * 数据初始化，插入两条记录
             */
            private function dbInitData(event:SQLEvent=null):void 
            { 
                trace("SQLite 数据库初始化完成"); 
                
                
                stmt.addEventListener(SQLEvent.RESULT, dbExecuteBatchSQL);
                
                this._execSQLBatch = new Array();
                
                this._execSQLBatch.push("INSERT INTO stockdailywatch (Symbol,Name) VALUES ('1A0001','上证指数')");
                this._execSQLBatch.push("INSERT INTO stockdaily (StatDate,Symbol,FinalPrice,RunDate) VALUES (date('2010-7-26'),'600066',16.00,date('now'))");
                
                this._execSQLCallLatency = this.dbQueryData;
                
                this.dbExecuteBatchSQL(null);
                
            }
            
            /**
             * 从两个数据表中查询内容
             */
            private function dbQueryData(event:SQLEvent=null):void
            {
                trace("SQLite 数据插入完成");
                
                this.dbQueryONE();
                this.dbQueryTWO();
                
            }
            
            /**
             * 发起查询第一个数据表的内容
             */
            private function dbQueryONE():void{
                var stmt:SQLStatement = new SQLStatement();
                stmt.sqlConnection = this.con;
                
                stmt.text = "SELECT Symbol,Name FROM stockdailywatch";
                stmt.execute(-1, new Responder(dbShowDataONE, dbErrorHandler));
            }
            
            /**
             * 发起查询第二个数据表的内容
             */
            private function dbQueryTWO():void{
                var stmt:SQLStatement = new SQLStatement();
                stmt.sqlConnection = this.con;
                
                stmt.text = "SELECT Symbol,FinalPrice,RunDate FROM stockdaily";
                stmt.execute(-1, new Responder(dbShowDataTWO, dbErrorHandler));
            }
            /**
             * 获得第一个数据表的查询结果，导入 datagrid
             */
            private function dbShowDataONE(result:SQLResult):void 
            { 
                trace("ONE 查询成功, 影响"+result.rowsAffected+"条记录");
                
                
                if(result.data!=null )
                {
                    trace("ONE 得到"+result.data.length + "个结果");
                    var c:ArrayCollection = new ArrayCollection(result.data);
                    var numResults:int =result.data.length;
                    this.dgOne.dataProvider = c;
                }
                
            }/**
             * 获得第二个数据表的查询结果，导入 datagrid
             */
            private function dbShowDataTWO(result:SQLResult):void 
            { 
                trace("TWO 查询成功, 影响"+result.rowsAffected+"条记录");
                
                
                if(result.data!=null )
                {
                    trace("TWO 得到"+result.data.length + "个结果");
                    var c:ArrayCollection = new ArrayCollection(result.data);
                    var numResults:int =result.data.length;
                    this.dgTwo.dataProvider = c;
                }
                
            }
            /**
             * 按钮点击事件，触发以上全部过程
             */
            protected function btnOK_clickHandler(event:MouseEvent):void
            {
                this.selectDBFile(this.dbFile);
            }

        ]]