/// <summary>
/// 7/2上線前的版本 可供新需求的邏輯參考
/// </summary>
/// <returns></returns>
public string Wf40040ETF()
{
Workbook workbook = new Workbook();
try {
workbook.LoadDocument(_lsFile);
Worksheet worksheet = workbook.Worksheets[(int)SheetName.SheetTwo];
DateTime emdate = _emDateText.AsDateTime("yyyy/MM/dd");
worksheet.Cells["P1"].Value = "資料日期：" + emdate.ToLongDateString();
//前一交易日
DateTime dateLast = dao40040.GetDateLast(emdate, (int)SheetName.SheetTwo);
//讀取資料
DataTable dtETF = dao40040.ListEtfData(emdate, dateLast, $"{_oswGrpVal}%");
if (dtETF.Rows.Count <= 0)
{
return(MessageDisplay.MSG_NO_DATA);
}
//讀取資料(現貨資料)
DataTable dtEtfMg6 = dao40040.ListEtfMg6Data(emdate);
//保證金變動幅度達10%，分別為第n天
DataTable dtDay = dao40040.ListDayData(emdate);
foreach (DataRow row in dtETF.Rows)
{
int rowIndex = row["SN"].AsInt() + 10;
#region 第B、C、D、E、F欄
worksheet.Cells[$"B{rowIndex}"].Value = row["SN"].AsInt();
worksheet.Cells[$"C{rowIndex}"].Value = row["MGT2_KIND_ID_OUT"].AsString();
worksheet.Cells[$"D{rowIndex}"].Value = row["APDK_NAME"].AsString();
worksheet.Cells[$"E{rowIndex}"].Value = row["APDK_STOCK_ID"].AsString();
worksheet.Cells[$"F{rowIndex}"].Value = row["PID_NAME"].AsString();
#endregion
if (row["MG1_CHANGE_FLAG"].AsString() != "Y")
{
continue;
}
//1.保證金變動幅度之趨勢/
//15%≧X≧10%
decimal mg1ChangeRange = row["MG1_CHANGE_RANGE"].AsDecimal();
#region 第G欄
if (Math.Abs(mg1ChangeRange) >= 0.1m && Math.Abs(mg1ChangeRange) <= 0.15m)
{
worksheet.Cells[$"G{rowIndex}"].SetValue(mg1ChangeRange);
}
#endregion
//X>15%
#region 第H欄
if (Math.Abs(mg1ChangeRange) > 0.15m)
{
worksheet.Cells[$"H{rowIndex}"].SetValue(mg1ChangeRange);
}
#endregion
//前一交易日保證金變動幅度
var ldValue = row["MG1_CHANGE_RANGE_LAST"];
#region 第I欄
worksheet.Cells[$"I{rowIndex}"].SetValue(ldValue == DBNull.Value ? " " : ldValue);
#endregion
#region 第J欄
worksheet.Cells[$"J{rowIndex}"].SetValue(ldValue == DBNull.Value ? " " : OX(row));
#endregion
//達得調整標準天數
string kindID = row["MG1_KIND_ID"].AsString();
#region 第K欄
int dtDayIndex = dtDay.Rows.IndexOf(dtDay.Select($@"mg1_kind_id ='{kindID}'").FirstOrDefault());
worksheet.Cells[$"K{rowIndex}"].SetValue(dtDayIndex > -1 ? (long)dtDay.Rows[dtDayIndex]["DAY_CNT"].AsDecimal() : 1);
#endregion
//2.未沖銷部位數/
var ai2OI = row["AI2_OI"];
if (ai2OI != DBNull.Value)
{
#region 第L欄
worksheet.Cells[$"L{rowIndex}"].SetValue(ai2OI);
#endregion
#region 第M欄
var oiRate = row["OI_RATE"];
worksheet.Cells[$"M{rowIndex}"].SetValue(oiRate.AsDecimal() < 0.0001m && ai2OI.AsDecimal() > 0 ? "小於0.01%" : oiRate);
#endregion
#region 第N欄
decimal TotOIiRound = Math.Round(dtETF.Rows[0]["TOT_OI"].AsDecimal() * 0.005m, 0, MidpointRounding.AwayFromZero);
worksheet.Cells[$"N{rowIndex}"].SetValue(ai2OI.AsDecimal() >= TotOIiRound ? "O" : "X");
#endregion
//屆到期日前7個交易日
#region 第O欄
worksheet.Cells[$"O{rowIndex}"].SetValue(
row["APROD_7DATE"].AsDateTime() <= emdate && row["APROD_DELIVERY_DATE"].AsDateTime() > emdate ? "O" : "X"
);
#endregion
}
//3.現貨、期貨漲跌/
int dtMg6EtfIndex = dtEtfMg6.Rows.IndexOf(dtEtfMg6.Select($@"trim(mg6_kind_id) ='{kindID}'").FirstOrDefault());
if (dtMg6EtfIndex > -1)
{
string prodSubtype = "";
//現貨漲跌幅度
#region 第P欄
var updown = dtEtfMg6.Rows[dtMg6EtfIndex]["MG6_UP_DOWN"];
decimal rateMUL100 = dtEtfMg6.Rows[dtMg6EtfIndex]["MG6_RETURN_RATE"].AsDecimal() * 100;
string flag = FlagStr(updown.AsDecimal());
worksheet.Cells[$"P{rowIndex}"].SetValue(updown == DBNull.Value ? updown : WriteUpDownPercent(updown.AsDecimal(), rateMUL100, prodSubtype, flag));
#endregion
//現貨漲跌與保證金調整方向相同
var cmlast = row["MG1_CM_LAST"];
#region 第R欄
worksheet.Cells[$"R{rowIndex}"].SetValue(cmlast == DBNull.Value ? "" : WriteFlag(flag, row));
#endregion
//期貨漲跌幅度
if (row["APDK_PROD_TYPE"].AsString() == "O")
{
dtMg6EtfIndex = dtEtfMg6.Rows.IndexOf(dtEtfMg6.Select($@"apdk_stock_id = '{row["APDK_STOCK_ID"].AsString()}' and mg6_prod_type ='F' and kind_seq_no = 1").FirstOrDefault());
if (dtMg6EtfIndex <= -1)
{
continue;
}
}
#region 第Q欄
var fupdown = dtEtfMg6.Rows[dtMg6EtfIndex]["AI5_UP_DOWN"];
string flag2 = FlagStr(fupdown.AsDecimal());
decimal frateMUL100 = dtEtfMg6.Rows[dtMg6EtfIndex]["AI5_RETURN_RATE"].AsDecimal() * 100;
worksheet.Cells[$"Q{rowIndex}"].SetValue(WriteUpDownPercent(fupdown.AsDecimal(), frateMUL100, prodSubtype, flag2));
#endregion
//期貨漲跌與保證金調整方向相同
#region 第S欄
worksheet.Cells[$"S{rowIndex}"].SetValue(cmlast == DBNull.Value ? "" : WriteFlag(flag2, row));
#endregion
} // if (dtMg6EtfIndex > -1)
} //foreach (DataRow row in dt.Rows)
//刪除空白列
int rowCount = dtETF.Rows.Count;
if (50 > rowCount)
{
worksheet.Rows.Hide(rowCount + 10, 60 - 1);
}
//save
worksheet.ScrollTo(0, 0);
}
catch (Exception ex) {
#if DEBUG
throw new Exception($"Wf40040ETF:" + ex.Message);
#else
throw ex;
#endif
}
finally {
workbook.SaveDocument(_lsFile);
}
return(MessageDisplay.MSG_OK);
}
