private string import2tables()
{
//todo (1), 目前趨勢感覺
//0.安全機制, 單分鐘內劇烈差距, 就立刻決定趨勢
//1.day high time, low time相比, 哪個晚就是哪個趨勢
//1.a. 也是有衰減時間，由最後一次破高破低開始算
//2.其次看high drop/low rise趨勢反轉, 主趨勢衰減才發揮次趨勢
//2.a. 看(maxHDcount+1)/(maxLRcount+1)
//2.b. 看先前到之後有沒有劇烈volume變化, 所以這得要照時間衰減
//3.則是若趨勢反轉發生, 是否會再反向
//3.a. 先做好1, 2再觀察
//3.b. 9/9觀察一，發現有20180705 093700起，highdrop/lowrise雙方都沒有創高，原作法失準，此時應照大趨勢
//3.c. 所以應該沒有所謂趨勢再反轉，只有大趨勢與反轉趨勢，雙方都會衰減，
// 大趨勢為主，衰減轉反轉趨勢，反轉趨勢也衰減，就返回大趨勢，就這麼簡單
//3.d. 可能還差一個問題，若high drop/low rise同時發生，那還是應以原趨勢為主
//3.e. 2018的，要判斷量的變化，大的變化會造成趨勢的遞延，並不只限單一分鐘
//3.f. 2018/10/5 not solved
//3.g. 2018 not bad, but 10/11 whole day -400
//3.h. 大趨勢
//3.h.1 low/high其中之一是084500 -> 1
//3.h.2 low/high其中之一變化 -> 2
//3.h.3 low/high都不變化了 -> 3
//3.h.4 如此，2與3界線很模糊，可能只能用次數與量來分
//3.h.5 10/1,2,4 等天，就是084500就已經2開始
//todo !!...(-1) !!! 感覺上這樣不行，還是把現有資料，補上所有畫面吧
//todo !!...(1) 這啦。所以有幾件事要變更，
//1. 日後要持續重新匯入，每周更新之後
//2. 只要匯入一個表，直接算出正確大趨勢，可用sp產新資料表
// 另外直接帶入pre n pre2 high/low, 只做單筆判斷，先這樣做試試
string ret = "";
AdoNet2DataTable dtdSrc = new AdoNet2DataTable(connIndices);
AdoNet2DataTable dtdTar = new AdoNet2DataTable(connEPLabDB);
string sql2a = @"
SELECT count(*) counts
FROM [indices2].[dbo].[dealdates]
where dealdate between '20180628' and '20200807'
";
string sql2b = @"
SELECT top 1000 dealdate, [close], sVolume, aVolume, lastdate, lastclose, lastSvolume, lastAvolume
FROM [indices2].[dbo].[dealdates]
where dealdate between '20180628' and '20200807'
order by dealdate
";
int counts = 0;
DataTable dt2 = dtdSrc.Select2DataTable(sql2a
, sql2b, out counts);
string err2 = dtdTar.ImportDataTableSaveas(dt2
, "dealdates");
return(ret);
}
public ActionResult Index(queryViewViewModel viewModel)
{
//todo !!....(0) 抓取前一前兩筆資料
//todo !!... (0) 不想用script寫, 所以不要在記憶體運算，例如存session, 還是要?
//若不存session, 就得存資料庫. 還是指定部分欄位存session, 計算結果才存資料庫?
//其實資料庫或session只能某時間統一用其中一種, 先線上運算, 通通放session中, 算
//好再存指定的欄位
//如此是否要一次算全部的資料?怕會太慢吃太多記憶體, 先試試看
//若是這樣, query or expression editor要一起做
//todo !!... (1) calculated field
//todo !!... (1) query editor
//todo !!... (2) csv export
//todo !!... (4) csv import from market history data
ActionResult ar;
if (viewModel.queryPara == null && TempData.ContainsKey("queryPara"))
{
viewModel.queryPara = (queryParasViewModel)TempData["queryPara"];
}
else
{
viewModel.queryPara = new queryParasViewModel();
}
viewModel.clearMsg();
ViewBag.queryIdselected = ddO.queryList();
ViewBag.tableWidthList = ddO.tableWidthList();
switch (viewModel.cmd)
{
case "selectChange":
if (string.IsNullOrWhiteSpace(viewModel.currentQuery.queryName))
{
TempData.Remove("queryPara");
ar = View(viewModel);
return(ar);
}
// load parameters
List <string> strLst = qel.formParameters(viewModel.currentQuery.queryName);
viewModel.queryPara = new queryParasViewModel();
foreach (string str1 in strLst)
{
viewModel.queryPara.queryParaInternal.Add(str1,
new queryParameterViewModel());
}
string err = loadCombo4vm(ref viewModel);
// parameter like dealdate can be a list as well
ar = View(viewModel);
break;
case "execute":
if (string.IsNullOrWhiteSpace(viewModel.currentQuery.queryName))
{
viewModel.errorMsg = "no query selected";
ar = View(viewModel);
break;
}
foreach (string key in Request.Form)
{
if (key.StartsWith("para_"))
{
string theValue = Request.Form[key];
if (string.IsNullOrWhiteSpace(theValue))
{
viewModel.errorMsg = $"parameter {key} cannot be empty!";
break;
}
queryParameterViewModel valObj =
new queryParameterViewModel(theValue);
string key2 = key.Replace("para_", "");
if (viewModel.queryPara.queryParaInternal.ContainsKey(key2))
{
viewModel.queryPara.queryParaInternal[key2] = valObj;
}
else
{
viewModel.queryPara.queryParaInternal.Add(key2, valObj);
}
err = loadCombo4vm(ref viewModel);
}
}
if (!string.IsNullOrWhiteSpace(viewModel.errorMsg))
{
ar = View(viewModel);
break;
}
// then type in parameter to execute query
string sqlCount = "";
string sql1000 = qel.finalSql4query(viewModel.currentQuery.queryName
, out sqlCount);
// @queryName passed in
// 拿掉rowId, dealMonth, section,
//從queryfield刪掉第一筆與最後兩筆，失敗後再加回第一筆，就變現在這樣
List <SqlParameter> para = new List <SqlParameter>
{
new SqlParameter
{
ParameterName = "@queryName",
SqlDbType = SqlDbType.NVarChar,
Value = viewModel.currentQuery.queryName
},
new SqlParameter
{
ParameterName = "@dealdate",
SqlDbType = SqlDbType.NVarChar,
Value = viewModel.queryPara.queryParaInternal["dealdate"].paraValue
}
};
int tmpRecords = 0;
viewModel.queryResult = a2dt.Select2DataTable(sqlCount, sql1000
, out tmpRecords, para);
viewModel.result2display = new bigQueryResult(
viewModel.queryResult);
viewModel.totalRecords = tmpRecords;
// dt render to view
ar = View(viewModel);
break;
default:
ar = View(viewModel);
break;
}
TempData["queryPara"] = viewModel.queryPara;
return(ar);
}
public DataTable mandatoryFieldValueInsufficient(
string queryName)
{
DataTable dt;
string sqlCount = string.Format(@"
with fieldsMustHaveValue(mustHaveFieldId)
as
(
{0}
union
{1}
union
{2}
union
{3}
union
{4}
union
select qf.fieldId
from queryFields qf
join fields f on qf.fieldId=f.fieldId
join queries q on qf.queryId=q.queryId
where q.queryName=@queryName and qf.orderByOrder>0
)
select count(*) counts
from queries q
join [rows] r on r.tableId=q.tableId and q.queryName=@queryName
join fields f on f.tableId=q.tableId
join fieldsMustHaveValue fmhv on fmhv.mustHaveFieldId=f.fieldId
left join fieldValues fv on fv.rowId=r.rowId and fv.fieldId=fmhv.mustHaveFieldId
where fv.fieldValueId is null
", MFVIsubSql(1), MFVIsubSql(2), MFVIsubSql(3), MFVIsubSql(4), MFVIsubSql(5)
);
string sql1000 = string.Format(@"
with fieldsMustHaveValue(mustHaveFieldId)
as
(
{0}
union
{1}
union
{2}
union
{3}
union
{4}
union
select qf.fieldId
from queryFields qf
join fields f on qf.fieldId=f.fieldId
join queries q on qf.queryId=q.queryId
where q.queryName=@queryName and qf.orderByOrder>0
)
select top 1000 fmhv.mustHaveFieldId, r.rowId
from queries q
join [rows] r on r.tableId=q.tableId and q.queryName=@queryName
join fields f on f.tableId=q.tableId
join fieldsMustHaveValue fmhv on fmhv.mustHaveFieldId=f.fieldId
left join fieldValues fv on fv.rowId=r.rowId and fv.fieldId=fmhv.mustHaveFieldId
where fv.fieldValueId is null
", MFVIsubSql(1), MFVIsubSql(2), MFVIsubSql(3), MFVIsubSql(4), MFVIsubSql(5)
);
// @queryName passed in
List <SqlParameter> para = new List <SqlParameter>
{
new SqlParameter
{
ParameterName = "@queryName",
SqlDbType = SqlDbType.NVarChar,
Value = queryName
}
};
int counts = 0;
dt = d2dt.Select2DataTable(sqlCount, sql1000, out counts, para);
return(dt);
}