public static DataTable nextchecktime(string outd, int room)
{
commonClass obcls = new commonClass();
string check = @"(SELECT CAST(allocdate AS CHAR(20)) AS allocdate FROM t_roomallocation WHERE room_id=" + room + " AND allocdate>STR_TO_DATE('" + outd + "','%d/%m/%Y %l:%i %p')) UNION (SELECT CAST(reservedate AS CHAR(20)) AS allocdate FROM t_roomreservation WHERE room_id=" + room + " AND reservedate>STR_TO_DATE('" + outd + "','%d/%m/%Y %l:%i %p')) UNION (SELECT CAST(CONCAT(fromdate,' ',fromtime) AS CHAR(20)) AS allocdate FROM t_manage_room WHERE room_id=" + room + " AND CONCAT(fromdate,'', fromtime)>STR_TO_DATE('" + outd + "','%d/%m/%Y %l:%i %p') AND rowstatus <> 2) ORDER BY allocdate";
DataTable dt_check = obcls.DtTbl(check);
return(dt_check);
}
public static DataTable previouschecktime(string ind, int room)
{
commonClass obcls = new commonClass();
string check = @"(SELECT CAST(exp_vecatedate AS CHAR(20)) AS exp_vecatedate FROM t_roomallocation WHERE room_id=" + room + " AND exp_vecatedate<STR_TO_DATE('" + ind + "','%d/%m/%Y %l:%i %p')) UNION (SELECT CAST(expvacdate AS CHAR(20)) AS exp_vecatedate FROM t_roomreservation WHERE room_id=" + room + " AND expvacdate<STR_TO_DATE('" + ind + "','%d/%m/%Y %l:%i %p')) UNION (SELECT CAST(CONCAT(todate,' ',totime) AS CHAR(20)) AS exp_vecatedate FROM t_manage_room WHERE room_id=" + room + " AND CONCAT(todate,'', totime)<STR_TO_DATE('" + ind + "','%d/%m/%Y %l:%i %p') AND rowstatus <> 2) ORDER BY exp_vecatedate desc";
DataTable dt_check = obcls.DtTbl(check);
return(dt_check);
}
private void detailData()
{
JObject urlData = JObject.Parse(Tool.CreatePostHttpResponse(Tool.getDetailApi(this.xmlNumber), "{\"id\": \"" + id + "\"}"));
commonClass detailData = JsonConvert.DeserializeObject <commonClass>(urlData["data"].ToString());
superTabControl1.SelectedTabIndex = 2;
foreach (PropertyInfo info in typeof(commonClass).GetProperties())
{
if (info.Name == "id")
{
continue;
}
writerControl2.GetElementById(info.Name).Text = (info.GetValue(detailData, null) == null) ? "" : info.GetValue(detailData, null).ToString();
}
}
public static DataTable roomavailable(string chkin, string chkout, int cat, int build)
{
commonClass obcls = new commonClass();
string sel = @"(SELECT distinct cast(roomno AS CHAR(25)) AS 'roomno',t_roomreservation.room_id
FROM t_roomreservation,m_room
WHERE t_roomreservation.reservedate >= DATE_ADD('" + chkout + "' ,INTERVAL 2 HOUR)"
+ " AND t_roomreservation.room_id NOT IN (SELECT t_roomreservation.room_id FROM t_roomreservation WHERE reservedate between DATE_ADD('" + chkin + "',INTERVAL -2 HOUR) AND DATE_ADD('" + chkout + "',INTERVAL +2 HOUR) OR expvacdate BETWEEN DATE_ADD('" + chkin + "',INTERVAL -2 HOUR) AND DATE_ADD('" + chkout + "',INTERVAL +2 HOUR))"
+ " AND t_roomreservation.room_id=m_room.room_id"
+ " AND m_room.room_cat_id=" + cat + " AND m_room.build_id=" + build + ""
+ " ORDER BY t_roomreservation.reservedate)"
+ " UNION "
+ " (SELECT distinct CAST(m_room.roomno AS CHAR(25)) AS 'roomno',m_room.room_id FROM m_room"
+ " WHERE m_room.room_cat_id=" + cat + " AND m_room.build_id=" + build + " AND m_room.rowstatus <> 2 AND m_room.room_id NOT IN (SELECT t_roomreservation.room_id FROM t_roomreservation WHERE t_roomreservation.reservedate"
+ " BETWEEN DATE_ADD('" + chkin + "',INTERVAL -2 HOUR) AND DATE_ADD('" + chkout + "',INTERVAL +2 HOUR) OR t_roomreservation.expvacdate BETWEEN DATE_ADD('" + chkin + "',INTERVAL -2 HOUR) AND DATE_ADD('" + chkout + "',INTERVAL +2 HOUR))"
+ " AND m_room.room_id NOT IN (SELECT t_roomallocation.room_id FROM t_roomallocation WHERE t_roomallocation.allocdate"
+ " BETWEEN DATE_ADD('" + chkin + "',INTERVAL -2 HOUR) AND DATE_ADD('" + chkout + "',INTERVAL +2 HOUR) OR t_roomallocation.exp_vecatedate BETWEEN DATE_ADD('" + chkin + "',INTERVAL -2 HOUR) AND DATE_ADD('" + chkout + "',INTERVAL +2 HOUR))"
+ " AND m_room.room_id NOT IN "
+ " (SELECT room_id FROM t_manage_room WHERE DATE_FORMAT(CONCAT(fromdate,'" + " " + "',fromtime),'%Y/%m/%d %T') BETWEEN '" + chkin + "' AND '" + chkout + "' OR DATE_FORMAT(CONCAT(todate,'" + " " + "',totime),'%Y/%m/%d %T') BETWEEN '" + chkin + "' AND '" + chkout + "')"
+ " ORDER BY m_room.room_id ASC)";
DataTable dt_sel = obcls.DtTbl(sel);
return(dt_sel);
}
private static data.tmpDS.marketDataDataTable GetMarketData(DateTime frDate, DateTime toDate, commonClass.AppTypes.TimeScale timeScale, string stockCodeList,DataTypes type)
{
string sqlCond = "";
if (timeScale != commonClass.AppTypes.MainDataTimeScale)
{
sqlCond += (sqlCond == "" ? "" : " AND ") + " type='" + timeScale.Code + "'";
}
sqlCond += (sqlCond == "" ? "" : " AND ") +
"onDate BETWEEN '" + common.system.ConvertToSQLDateString(frDate) + "'" +
" AND '" + common.system.ConvertToSQLDateString(toDate) + "'";
sqlCond += (sqlCond == "" ? "" : " AND ");
switch (type)
{
case DataTypes.Advancing: sqlCond += "closePrice>openPrice"; break;
case DataTypes.Declining: sqlCond += "closePrice<openPrice"; break;
default: sqlCond += "closePrice=openPrice"; break;
}
if (stockCodeList != null && stockCodeList != "")
{
sqlCond += (sqlCond == "" ? "" : " AND ") + " stockCode IN (" + stockCodeList + ")";
}
else
{
sqlCond += (sqlCond == "" ? "" : " AND ") +
" stockCode IN (SELECT code FROM stockCode WHERE status & " + ((byte)commonClass.AppTypes.CommonStatus.Enable).ToString() + ">0)";
}
string sqlCmd =
"SELECT onDate,COUNT(*) AS val0,SUM(volume) AS val1" +
" FROM " + (timeScale == commonClass.AppTypes.MainDataTimeScale ? "priceData" : "priceDataSum") +
" WHERE " + sqlCond +
" GROUP BY onDate ORDER BY onDate";
data.tmpDS.marketDataDataTable tbl = new data.tmpDS.marketDataDataTable();
DbAccess.LoadFromSQL(tbl, sqlCmd);
return tbl;
}
private static string MakeAnalysisDataCacheKey(commonClass.BaseAnalysisData dataObj)
{
return "AnalysisData" + "-" + dataObj.DataStockCode + "-" + dataObj.DataTimeRange.ToString() + "-" +
dataObj.DataTimeScale.Code;
}
public static void ClearAnalysisDataCache(commonClass.BaseAnalysisData dataObj)
{
cacheData.Remove(MakeAnalysisDataCacheKey(dataObj));
}
private static string MakeCacheKey(string code, commonClass.DataParams dataParam)
{
return "data" + "-" + code + "-" + dataParam.ToUniqueString();
}
public static data.baseDS.lastPriceDataDataTable GetLastPrice(commonClass.PriceDataType type)
{
return myClient.GetLastPrice(type);
}
public data.baseDS.lastPriceDataDataTable GetLastPrice(commonClass.PriceDataType type)
{
string cacheName = "lastPrice-" + type.ToString();
data.baseDS.lastPriceDataDataTable dataTbl = null;
object obj = sysDataCache.Find(cacheName);
if (obj == null)
{
dataTbl = DbAccess.GetLastPrice(type);
sysDataCache.Add(cacheName, new DataCacheItem(dataTbl));
return dataTbl;
}
if ((obj as DataCacheItem).timeStamp + TimeSpan.FromSeconds(commonClass.Settings.sysDataDelayTimeInSecs).Ticks > DateTime.Now.Ticks)
{
return (data.baseDS.lastPriceDataDataTable)(obj as DataCacheItem).data;
}
dataTbl = DbAccess.GetLastPrice(type);
sysDataCache.Add(cacheName, new DataCacheItem(dataTbl));
return dataTbl;
}
public DataValues[] GetIndicatorData(string code, commonClass.DataParams dataParam, string metaName)
{
try
{
application.AnalysisData data = GetAnalysisData(LoadAnalysisData(code, dataParam, false));
if (data == null) return null;
application.Indicators.Meta meta = application.Indicators.Libs.FindMetaByName(metaName);
return application.Indicators.Libs.GetIndicatorData(data, meta);
}
catch (Exception ex)
{
WriteSysLogLocal("WS009", ex);
}
return null;
}
/// <summary>
/// Constructors to create a market indicator's data for specific stock list
/// </summary>
/// <param name="timeRange"></param>
/// <param name="timeScale"></param>
/// <param name="stockCodes"></param>
public MarketData(commonClass.AppTypes.TimeRanges timeRange, commonClass.AppTypes.TimeScale timeScale, StringCollection stockCodes)
{
DateTime startDate = DateTime.Today, endDate = DateTime.Today;
commonClass.AppTypes.GetDate(timeRange, out startDate, out endDate);
this.Init(startDate, endDate, timeScale, stockCodes);
}
private CurveItem PlotCurveLine(string curveName, Charts.Controls.baseGraphPanel graphPane, commonClass.DataSeries xSeries, commonClass.DataSeries ySeries,
Color color, int weight)
{
pricePane.myGraphObj.SetSeriesX(xSeries.Values, Charts.AxisType.DateAsOrdinal);
CurveItem curveItem = graphPane.myGraphObj.AddCurveLine(curveName, ySeries.Values, SymbolType.None, color, weight);
return curveItem;
}
private MarketData(DateTime startDateTime, DateTime endDateTime, commonClass.AppTypes.TimeScale timeScale)
{
this.Init(startDateTime, endDateTime, timeScale, null);
}
/// <summary>
/// Constructors to create a market indicator's data for all enable stock
/// </summary>
/// <param name="timeRange"></param>
/// <param name="timeScale"></param>
public MarketData(commonClass.AppTypes.TimeRanges timeRange, commonClass.AppTypes.TimeScale timeScale)
{
DateTime startDate = DateTime.Today, endDate = DateTime.Today;
commonClass.AppTypes.GetDate(timeRange, out startDate, out endDate);
this.Init(startDate, endDate, timeScale, null);
}
//Constructors
private MarketData(DateTime startDateTime, DateTime endDateTime, commonClass.AppTypes.TimeScale timeScale, StringCollection stockCodes)
{
this.Init(startDateTime, endDateTime, timeScale, stockCodes);
}
public AnalysisData(commonClass.AppTypes.TimeRanges timeRange, commonClass.AppTypes.TimeScale timeScale, string stockCode, DataAccessMode accessMode)
: base(timeRange, timeScale, stockCode, accessMode) { }
private void Init(DateTime startDateTime, DateTime endDateTime, commonClass.AppTypes.TimeScale timeScale, StringCollection stockCodes)
{
this.Cache.Clear();
this.StartDateTime = startDateTime;
this.EndDateTime = endDateTime;
if (stockCodes != null && stockCodes.Count != 0)
this.StockCodeList = common.system.MakeConditionStr(stockCodes, "'", "'", ",");
else this.StockCodeList = null;
this.TimeScale = timeScale;
}
public static bool LoadAnalysisData(commonClass.BaseAnalysisData dataObj)
{
AnalysisDataCache data;
string cacheKey = MakeAnalysisDataCacheKey(dataObj);
object obj = GetCache(cacheKey);
if (obj != null)
{
data = (AnalysisDataCache)obj;
dataObj.priceDataTbl = (data.baseDS.priceDataDataTable)data.dataTbl.Copy();
return true;
}
using (new PleaseWait())
{
data = new AnalysisDataCache();
int firstData = 0;
data.dataTbl = myClient.GetAnalysis_Data(out firstData, dataObj.DataTimeRange, dataObj.DataTimeScale.Code, dataObj.DataStockCode);
data.firstData = firstData;
AddCache(cacheKey, data);
dataObj.priceDataTbl = (data.baseDS.priceDataDataTable)data.dataTbl.Copy();
dataObj.FirstDataStartAt = firstData;
}
return true;
}
private static string MakeAnalysisDataCacheKey(commonClass.BaseAnalysisData dataObj)
{
try
{
return "AnalysisData" + "-" + dataObj.DataStockCode + "-" + dataObj.DataTimeRange.ToString() + "-" +
dataObj.DataTimeScale.Code;
}
catch (Exception er)
{
if (OnError != null) OnError(er);
}
return null;
}
//Updated data from the last read/update point
public static int UpdateAnalysisData(commonClass.BaseAnalysisData dataObj)
{
int lastDataIdx = dataObj.priceDataTbl.Count - 1;
DateTime lastDateTime;
if (lastDataIdx < 0) lastDateTime = commonClass.Settings.sysStartDataDate;
else lastDateTime = dataObj.priceDataTbl[lastDataIdx].onDate;
data.baseDS.priceDataDataTable tbl = GetData_ByTimeScale_Code_FrDate(dataObj.DataTimeScale.Code, dataObj.DataStockCode,lastDateTime);
if (tbl.Count > 0)
{
//Delete the last data because the updated data will include this one.
if (lastDataIdx >= 0)
{
dataObj.priceDataTbl[lastDataIdx].ItemArray = tbl[0].ItemArray;
commonClass.AppLibs.DataConcat(tbl, 1, dataObj.priceDataTbl);
}
else commonClass.AppLibs.DataConcat(tbl, 0, dataObj.priceDataTbl);
}
//Update cache
AnalysisDataCache data = new AnalysisDataCache();
data.dataTbl = (data.baseDS.priceDataDataTable)dataObj.priceDataTbl.Copy();
data.firstData = dataObj.FirstDataStartAt;
AddCache(MakeAnalysisDataCacheKey(dataObj), data);
return dataObj.priceDataTbl.Count - 1 - lastDataIdx;
}
public static bool LoadAnalysisData(commonClass.BaseAnalysisData dataObj)
{
try
{
AnalysisDataCache data;
string cacheKey = MakeAnalysisDataCacheKey(dataObj);
object obj = GetCache(cacheKey);
if (obj != null)
{
data = (AnalysisDataCache)obj;
dataObj.priceDataTbl = (databases.baseDS.priceDataDataTable)data.dataTbl.Copy();
return true;
}
using (new PleaseWait())
{
lock (myClient)
{
data = new AnalysisDataCache();
int firstData = 0;
DataParams dataParam = new DataParams(dataObj.DataTimeScale.Code, dataObj.DataTimeRange, dataObj.DataMaxCount);
data.dataTbl = myClient.GetAnalysis_Data(out firstData, dataObj.DataStockCode, dataParam);
data.firstData = firstData;
AddCache(cacheKey, data);
dataObj.priceDataTbl = (databases.baseDS.priceDataDataTable)data.dataTbl.Copy();
dataObj.FirstDataStartAt = firstData;
}
}
return true;
}
catch (Exception er)
{
if (OnError != null) OnError(er);
}
return false;
}
/// <summary>
/// Get data and store in cache. Depend on forceReadNew, cached data can be used to boost perfomance
/// </summary>
/// <param name="timeRange"></param>
/// <param name="timeScaleCode"></param>
/// <param name="code"></param>
/// <param name="forceReadNew"> if true always read from database, ignore the cached data</param>
/// <returns>Data key used for data accessing</returns>
public string LoadAnalysisData(string code,commonClass.DataParams dataParam, bool forceReadNew)
{
try
{
string cacheName = MakeCacheKey(code, dataParam);
if (forceReadNew || sysDataCache.Find(cacheName) == null)
{
application.AnalysisData myData = new application.AnalysisData(code, dataParam);
sysDataCache.Add(cacheName, myData);
}
return cacheName;
}
catch (Exception ex)
{
WriteSysLogLocal("WS002",ex);
}
return null;
}
public static data.baseDS.tradeAlertRow GetAlert(DateTime onDateTime, string portfolio,
string stockCode, string strategy, string timeScale,commonClass.AppTypes.CommonStatus status)
{
data.baseDS.tradeAlertDataTable tbl = tradeAlertTA.GetOne(onDateTime, onDateTime, portfolio, stockCode, strategy, timeScale,((byte)status).ToString());
if (tbl == null || tbl.Count == 0) return null;
return tbl[0];
}
public MarketData(commonClass.BaseAnalysisData data, StringCollection stockCodes)
{
DateTime startDate = DateTime.Today, endDate = DateTime.Today;
commonClass.AppTypes.GetDate(data.DataTimeRange, out startDate, out endDate);
this.Init(startDate,endDate,data.DataTimeScale, stockCodes);
}
public databases.baseDS.priceDataDataTable GetAnalysis_Data(string code,commonClass.DataParams dataParam, out int firstData)
{
string dataKey = LoadAnalysisData(code,dataParam,true);
return GetAnalysis_Data_ByKey(dataKey, out firstData);
}
public static void ClearAnalysisDataCache(commonClass.BaseAnalysisData dataObj)
{
try
{
cacheData.Remove(MakeAnalysisDataCacheKey(dataObj));
}
catch (Exception er)
{
if (OnError != null) OnError(er);
}
}
public MarketData(commonClass.BaseAnalysisData data)
{
DateTime startDate = DateTime.Today, endDate = DateTime.Today;
commonClass.AppTypes.GetDate(data.DataTimeRange, out startDate, out endDate);
this.Init(startDate, endDate, data.DataTimeScale, null);
}
//Updated data from the last read/update point
public static int UpdateAnalysisData(commonClass.BaseAnalysisData dataObj)
{
try
{
int lastDataIdx = dataObj.priceDataTbl.Count - 1;
DateTime lastDateTime;
if (lastDataIdx < 0) lastDateTime = Settings.sysStartDataDate;
else lastDateTime = dataObj.priceDataTbl[lastDataIdx].onDate;
databases.baseDS.priceDataDataTable tbl = GetData_ByTimeScale_Code_FrDate(dataObj.DataTimeScale.Code, dataObj.DataStockCode, lastDateTime);
if (tbl.Count > 0)
{
//Delete the last data because the updated data will include this one.
if (lastDataIdx >= 0)
{
dataObj.priceDataTbl[lastDataIdx].ItemArray = tbl[0].ItemArray;
common.system.Concat(tbl, 1, dataObj.priceDataTbl);
}
else common.system.Concat(tbl, 0, dataObj.priceDataTbl);
//Update data
dataObj.DateTime.UpdateLast(DataLibs.GetDataList(tbl, 0, 0, AppTypes.PriceDataType.DateTime));
dataObj.DateTime.Add2Last(DataLibs.GetDataList(tbl, 1, AppTypes.PriceDataType.DateTime));
dataObj.Open.UpdateLast(DataLibs.GetDataList(tbl, 0, 0, AppTypes.PriceDataType.Open));
dataObj.Open.Add2Last(DataLibs.GetDataList(tbl, 1, AppTypes.PriceDataType.Open));
dataObj.High.UpdateLast(DataLibs.GetDataList(tbl, 0, 0, AppTypes.PriceDataType.High));
dataObj.High.Add2Last(DataLibs.GetDataList(tbl, 1, AppTypes.PriceDataType.High));
dataObj.Low.UpdateLast(DataLibs.GetDataList(tbl, 0, 0, AppTypes.PriceDataType.Low));
dataObj.Low.Add2Last(DataLibs.GetDataList(tbl, 1, AppTypes.PriceDataType.Low));
dataObj.Close.UpdateLast(DataLibs.GetDataList(tbl, 0, 0, AppTypes.PriceDataType.Close));
dataObj.Close.Add2Last(DataLibs.GetDataList(tbl, 1, AppTypes.PriceDataType.Close));
dataObj.Volume.UpdateLast(DataLibs.GetDataList(tbl, 0, 0, AppTypes.PriceDataType.Volume));
dataObj.Volume.Add2Last(DataLibs.GetDataList(tbl, 1, AppTypes.PriceDataType.Volume));
}
return dataObj.priceDataTbl.Count - 1 - lastDataIdx;
}
catch (Exception er)
{
if (OnError != null) OnError(er);
}
return -1;
}
public virtual void LoadData(commonClass.AppTypes.StrategyTypes type,bool onlyEnableItem,bool checkAll)
{
this.strategyClb.LoadData(type);
this.strategyCatCb.LoadData(true);
}
public static data.baseDS.lastPriceDataDataTable GetLastPrice(commonClass.PriceDataType type)
{
switch (type)
{
case PriceDataType.Close: return lastPriceDataTA.GetClosePrice();
case PriceDataType.Open: return lastPriceDataTA.GetOpenPrice();
}
return null;
}
public BaseAnalysisData(commonClass.AppTypes.TimeRanges timeRange,
commonClass.AppTypes.TimeScale timeScale,
string stockCode, DataAccessMode accessMode)
{
this.DataTimeScale = timeScale;
this.DataTimeRange = timeRange;
this.DataStockCode = stockCode;
this.AccessMode = accessMode;
ClearCache();
LoadData();
}
