4,328
社区成员
发帖
与我相关
我的任务
分享
package com.ccac.messageReceiver.singletons
{
import com.ccac.messageReceiver.events.DataQueryEvent;
import flash.data.SQLConnection;
import flash.data.SQLResult;
import flash.data.SQLStatement;
import flash.errors.SQLError;
import flash.events.EventDispatcher;
import flash.events.IEventDispatcher;
import flash.events.SQLErrorEvent;
import flash.events.SQLEvent;
import flash.filesystem.File;
import flash.net.Responder;
import flash.utils.setTimeout;
import mx.utils.UIDUtil;
public class DataAccess extends EventDispatcher
{
public static const DB_FILE:String = "data.db";
private static var instance:DataAccess = null;
public static function getInstance():DataAccess
{
if (!instance)
instance = new DataAccess();
return instance;
}
public function DataAccess(target:IEventDispatcher = null)
{
if (!instance)
{
super(target);
this.addEventListener(DataQueryEvent.PAGINATION, paginationHandler);
}
else
{
throw new Error("DataAccess is a SINGLETON!");
}
}
protected var connection:SQLConnection = null;
protected var sqlStat:SQLStatement = null;
protected var messages:Array = new Array();
private var pageNo:int = 0;
private var pageSize:int = 0;
private var currentPageCount:int = 1;
private var currentPageNo:int = 1;
public function get connected():Boolean
{
if (!connection)
return false;
return connection.connected;
}
private var _ready:Boolean = false;
public function get ready():Boolean
{
return _ready;
}
public function openConnection():void
{
if (!connection)
{
connection = new SQLConnection();
connection.addEventListener(SQLEvent.OPEN, sqlOpenHandler);
connection.addEventListener(SQLEvent.CLOSE, sqlCloseHandler);
connection.addEventListener(SQLErrorEvent.ERROR, sqlErrorHandler);
}
if (connected)
return;
var dbFile:File = File.applicationStorageDirectory.resolvePath(DB_FILE);
connection.open(dbFile);
}
public function closeConnection():void
{
if (connection.connected)
connection.close();
_ready = false;
}
public function getMessages(startDate:Date, endDate:Date):void
{
startDate = (startDate ? startDate : new Date(1900, 0, 1));
endDate = (endDate ? endDate : new Date(9999, 11, 31));
queryData(startDate.time, endDate.time);
}
public function getMessagesByPageNo(startDate:Date, endDate:Date,
pageNo:int, pageSize:int):void
{
startDate = (startDate ? startDate : new Date(1900, 0, 1));
endDate = (endDate ? endDate : new Date(9999, 11, 31));
queryDataByPageNo(startDate.time, endDate.time, pageNo, pageSize);
}
public function addMessage(title:String, content:String):void
{
var message:Object = {"title": title, "content": content};
message.id = UIDUtil.createUID();
message.date = new Date();
if (ready)
insertData(message);
else
messages.push(message);
}
public function deleteMessage(id:String):void
{
deleteData(id);
}
private function createTables():void
{
var sql:String = "CREATE TABLE IF NOT EXISTS MESSAGES " +
"(ID TEXT, DATE NUMERIC, TITLE TEXT, CONTENT TEXT)";
sqlStat = new SQLStatement();
sqlStat.sqlConnection = connection;
sqlStat.text = sql;
sqlStat.addEventListener(SQLEvent.RESULT, sqlResultHandler);
sqlStat.addEventListener(SQLErrorEvent.ERROR, sqlErrorHandler);
sqlStat.execute();
}
private function queryData(startDate:Number, endDate:Number):void
{
var sql:String = "SELECT ID, DATE, TITLE, CONTENT FROM MESSAGES WHERE " +
"DATE >= " + startDate + " AND DATE <= " + endDate + " ORDER BY DATE DESC";
sqlStat.text = sql;
sqlStat.execute(-1, new Responder(queryResultFunction, errorFunction));
}
private function queryDataByPageNo(startDate:Number, endDate:Number,
pageNo:int, pageSize:int):void
{
var sql:String = "SELECT ID, DATE FROM MESSAGES WHERE " +
"DATE >= " + startDate + " AND DATE <= " + endDate + " ORDER BY DATE DESC";
this.pageNo = pageNo;
this.pageSize = pageSize;
sqlStat.text = sql;
sqlStat.execute(-1, new Responder(paginationResultFunction, errorFunction));
}
private function insertData(data:Object):void
{
var sql:String = "INSERT INTO MESSAGES (ID, DATE, TITLE, CONTENT) VALUES " +
"('" + data.id + "', " + data.date.time + ", '" + data.title.replace("'", "''") +
"', '" + data.content.replace("'", "''") + "')";
sqlStat.text = sql;
sqlStat.execute();
}
private function deleteData(id:String):void
{
var sql:String = "DELETE FROM MESSAGES WHERE ID = '" + id.replace("'", "''") + "'";
sqlStat.text = sql;
sqlStat.execute();
}
private function sqlOpenHandler(event:SQLEvent):void
{
this.dispatchEvent(event);
createTables();
}
private function sqlCloseHandler(event:SQLEvent):void
{
this.dispatchEvent(event);
}
private function sqlErrorHandler(event:SQLErrorEvent):void
{
this.dispatchEvent(event);
}
private function sqlResultHandler(event:SQLEvent):void
{
this.dispatchEvent(event);
if (!_ready)
{
_ready = true;
for (var i:int = 0; i < messages.length; i++)
insertData(messages[i]);
messages = new Array();
}
}
private function queryResultFunction(sqlResult:SQLResult):void
{
if (!sqlResult)
return;
var currentEvent:DataQueryEvent = null;
if (sqlResult.data == null || (sqlResult.data != null && sqlResult.data.length == 0))
{
currentPageNo = 1;
currentPageCount = 1;
}
currentEvent = new DataQueryEvent(DataQueryEvent.DATA_QUERY);
currentEvent.data = (sqlResult.data == null ? [] : sqlResult.data);
currentEvent.pageNo = currentPageNo;
currentEvent.pageCount = currentPageCount;
this.dispatchEvent(currentEvent);
}
private function paginationResultFunction(sqlResult:SQLResult):void
{
var currentEvent:DataQueryEvent = null;
if (sqlResult.data == null)
{
currentPageNo = 1;
currentPageCount = 1;
currentEvent = new DataQueryEvent(DataQueryEvent.PAGINATION);
currentEvent.startDate = (new Date(2000, 0, 1)).time;
currentEvent.endDate = (new Date(1900, 0, 1)).time;
this.dispatchEvent(currentEvent);
return;
}
if (sqlResult.data.length > 0)
{
var startIndex:int = pageSize * (pageNo - 1);
var endIndex:int = Math.min(startIndex + pageSize - 1, sqlResult.data.length - 1);
currentPageCount = Math.ceil(sqlResult.data.length / pageSize);
currentPageNo = pageNo;
if (startIndex < 0 || startIndex > sqlResult.data.length - 1 ||
endIndex < 0 || endIndex > sqlResult.data.length - 1)
{
startIndex = 0;
endIndex = Math.min(pageSize - 1, sqlResult.data.length);
currentPageNo = 1;
}
var startDate:Number = sqlResult.data[endIndex].DATE;
var endDate:Number = sqlResult.data[startIndex].DATE;
currentEvent = new DataQueryEvent(DataQueryEvent.PAGINATION);
currentEvent.startDate = startDate;
currentEvent.endDate = endDate;
this.dispatchEvent(currentEvent);
}
else
{
currentPageNo = 1;
currentPageCount = 1;
currentEvent = new DataQueryEvent(DataQueryEvent.PAGINATION);
currentEvent.startDate = (new Date(2000, 0, 1)).time;
currentEvent.endDate = (new Date(1900, 0, 1)).time;
this.dispatchEvent(currentEvent);
}
}
private function errorFunction(error:SQLError):void
{
}
private function paginationHandler(event:DataQueryEvent):void
{
setTimeout(queryData, 500, event.startDate, event.endDate);
}
}
}