protected override ResultStatus Export()
{
try {
dao30050 = new D30050();
daoRPT = new RPT();
string rptId = "30050", file;
string asYmd = txtSDate.Text.Replace("/", "");
// 1. 複製檔案
file = PbFunc.wf_copy_file(rptId, rptId);
if (file == "")
{
return(ResultStatus.Fail);
}
// 2. 開啟檔案
Workbook workbook = new Workbook();
workbook.LoadDocument(file);
// 3. 匯出資料
int rowNum = 1;
string rptName, dataType, ymd;
int g, rowNumRPT, found, colNum;
#region wf_30051
rptName = "最大交易量及未平倉數";
rptId = "30051";
// 切換Sheet
Worksheet ws30050 = workbook.Worksheets[0];
// 讀取並填入資料
for (g = 1; g < 3; g++)
{
if (g == 1)
{
dataType = "M";
colNum = 1;
}
else
{
dataType = "OI";
colNum = 3;
}
DataTable dt30051 = dao30050.d_30051(asYmd, dataType);
if (dt30051.Rows.Count == 0)
{
MessageDisplay.Info(txtSDate.Text + "," + rptId + '－' + rptName + ",無任何資料!");
return(ResultStatus.Fail);
}
dt30051.Filter("RPT_SEQ_NO > 0");
foreach (DataRow dr in dt30051.Rows)
{
rowNumRPT = dr["RPT_SEQ_NO"].AsInt() - 1;
ws30050.Cells[rowNumRPT, colNum].SetValue(dr["AI4_QNTY"]);
ymd = dr["AI4_MAX_YMD"].AsString();
ymd = ymd.SubStr(0, 4) + "/" + ymd.SubStr(4, 2) + "/" + ymd.SubStr(6, 2);
ws30050.Cells[rowNumRPT, colNum + 1].Value = ymd;
}
}
#endregion
rowNum = rowNum + 3;
#region wf_30052
string sumType = "", sumSubtype = "", prodType = "", paramKey = "";
rptName = "交易量及未平倉數排序";
rptId = "30052";
/*******************
* RPT
*******************/
daoRPT = new RPT();
DataTable dtRPT = daoRPT.ListAllByTXD_ID(rptId);
if (dtRPT.Rows.Count == 0)
{
MessageDisplay.Error(rptId + '－' + "RPT無任何資料!");
return(ResultStatus.Fail);
}
for (g = 1; g < 9; g++)
{
if (g == 2 || g == 4 || g == 6)
{
dataType = "OI";
colNum = 3;
}
else
{
dataType = "M";
colNum = 1;
}
switch (g)
{
case 1:
case 2:
sumType = "D";
sumSubtype = "1";
prodType = "F%";
paramKey = "%";
break;
case 3:
case 4:
sumType = "D";
sumSubtype = "3";
prodType = "O%";
paramKey = "TXO%";
break;
case 5:
case 6:
sumType = "D";
sumSubtype = "0";
prodType = "%";
paramKey = "%";
break;
case 7:
sumType = "Y";
sumSubtype = "0";
prodType = "%";
paramKey = "%";
break;
case 8:
sumType = "M";
sumSubtype = "0";
prodType = "%";
paramKey = "%";
colNum = 3;
break;
}
DataTable dt30052 = dao30050.d_30052(asYmd, sumType, sumSubtype, prodType, paramKey, dataType);
if (dt30052.Rows.Count == 0)
{
return(ResultStatus.Fail);
}
DataRow[] find = dtRPT.Select("rpt_seq_no=" + g.AsString());
if (find.Length != 0)
{
found = dtRPT.Rows.IndexOf(find[0]);
}
else
{
continue;
}
rowNum = dtRPT.Rows[found]["RPT_VALUE"].AsString().AsInt() - 1;
rowNum = rowNum - 1;
foreach (DataRow dr in dt30052.Rows)
{
rowNum = rowNum + 1;
ws30050.Cells[rowNum, colNum].SetValue(dr["AI4_QNTY"]);
ymd = dr["AI4_MAX_YMD"].AsString();
if (g == 7)
{
ymd = ymd;
}
else if (g == 8)
{
ymd = ymd.SubStr(0, 4) + "/" + ymd.SubStr(4, 2);
}
else
{
ymd = ymd.SubStr(0, 4) + "/" + ymd.SubStr(4, 2) + "/" + ymd.SubStr(6, 2);
}
ws30050.Cells[rowNum, colNum + 1].Value = ymd;
}
}
// g = 9
DataRow[] findG9 = dtRPT.Select("rpt_seq_no=" + g.AsString());
if (findG9.Length != 0)
{
found = dtRPT.Rows.IndexOf(findG9[0]);
rowNum = dtRPT.Rows[found]["RPT_VALUE"].AsString().AsInt() - 1;
ws30050.Cells[rowNum, 1].Value = txtSDate.Text;
}
#endregion
// 4. 存檔
ws30050.ScrollToRow(0);
workbook.SaveDocument(file);
}
catch (Exception ex) {
MessageDisplay.Error("輸出錯誤");
throw ex;
}
return(ResultStatus.Success);
}
protected override ResultStatus Export()
{
// 1. ue_export_before
if (txtEDate.DateTimeValue.Year != txtSDate.DateTimeValue.Year)
{
MessageDisplay.Error("不可跨年度查詢!");
txtSDate.Focus();
return(ResultStatus.Fail);
}
// 2. ue_export
string rptId = "30030", file;
// 2.1 複製檔案
file = PbFunc.wf_copy_file(rptId, rptId);
if (file == "")
{
return(ResultStatus.Fail);
}
// 2.2 開啟檔案
Workbook workbook = new Workbook();
workbook.LoadDocument(file);
flag = 0;
// 2.3 匯出Excel
int rowKeep, rowNum;
rowNum = 1;
#region wf_30031
string rptName, kindId;
int f, g, colNum, rowTol, found;
/*************************************
* ls_rpt_name = 報表名稱
* ls_rpt_id = 報表代號
* li_ole_col = Excel的Column位置
* li_ole_row_tol = Excel的Column預留數
* ls_kind_id = param_key
*************************************/
rptName = "當年每月日均量統計表";
rptId = "30031";
string symd = txtSDate.Text.Replace("/", "").Trim();
string eymd = txtEDate.Text.Replace("/", "").Trim();
string ymd = "";
// 切換Sheet，總共只有一個sheet
Worksheet ws30030 = workbook.Worksheets[0];
// RPT
daoRPT = new RPT();
DataTable dtRPT = daoRPT.ListAllByTXD_ID(rptId);
if (dtRPT.Rows.Count <= 0)
{
MessageDisplay.Error(rptId + '－' + "RPT無任何資料!");
//return ResultStatus.Fail;
}
else
{
// 讀取資料
dao30030 = new D30030();
DataTable dt30031 = dao30030.d_30031(symd + "01", eymd + "31");
dt30031.Filter("RPT_SEQ_NO > 0");
if (dt30031.Rows.Count <= 0)
{
MessageDisplay.Info(eymd + "," + rptId + '－' + rptName + ",無任何資料!");
//return ResultStatus.Fail;
}
else
{
// 填入資料
rowTol = rowNum + 302;
ws30030.Cells[rowTol + 2, 0].Value = txtEDate.Text.SubStr(5, 2) + "月百分比(%)";
string rptType, sumType;
rptType = "";
g = 0;
for (f = 0; f < dt30031.Rows.Count; f++)
{
DataRow dr = dt30031.Rows[f];
if (ymd != dr["AI2_YMD"].AsString())
{
ymd = dr["AI2_YMD"].AsString();
rowNum = rowNum + 1;
ws30030.Cells[rowNum, 0].Value = ymd.SubStr(4, 2) + "/" + ymd.SubStr(6, 2);
}
kindId = dr["AI2_PARAM_KEY"].AsString();
colNum = dr["RPT_SEQ_NO"].AsInt() - 1;
ws30030.Cells[rowNum, colNum].SetValue(dr["AI2_M_QNTY"]);
/* 未沖銷量 */
DataRow[] find = dtRPT.Select("rpt_value like 'OI%'");
if (find.Length != 0)
{
found = dtRPT.Rows.IndexOf(find[0]);
}
else
{
found = -1;
}
if (found >= 0)
{
found = dtRPT.Rows[found]["RPT_SEQ_NO"].AsInt();
ws30030.Cells[rowNum, found - 1].SetValue(dt30031.Compute("sum(AI2_OI)", $@"AI2_YMD='{ymd}'"));
}
}
ws30030.Cells[rowTol + 1, 0].Value = "合計(" + (txtEDate.DateTimeValue.Year - 1911).AsString() + '/' + txtEDate.DateTimeValue.Month.AsString() + ')';
// 刪除空白列
if (rowTol > rowNum)
{
ws30030.Rows.Remove(rowNum + 1, rowTol - rowNum);
}
flag++;
}
}
#endregion
rowNum = rowNum + 6;
#region wf_30032
rptName = "當年每月日均量統計表";
rptId = "30032";
// 讀取資料
DataTable dt30032 = dao30030.d_30032("M", symd.SubStr(0, 4) + "01", eymd);
if (dt30032.Rows.Count == 0)
{
//PB這邊註解掉
//MessageDisplay.Info(eymd + "," + rptId + '－' + rptName + ",無任何資料!");
//return ResultStatus.Fail;
}
dt30032.Filter("RPT_SEQ_NO > 0");
/* OI */
DataTable dt30032OI = dao30030.d_30032_OI(symd.SubStr(0, 4) + "01", eymd);
if (dt30032OI.Rows.Count == 0)
{
//PB這邊註解掉
//MessageDisplay.Info(eymd + "," + rptId + '－' + rptName + ",無任何資料!");
//return ResultStatus.Fail;
}
int colTol = dao30030.colTol() - 1;
rowTol = rowNum + 12;
//填入資料
int row = 0;
ymd = "";
g = 0;
found = 0;
for (f = 0; f < dt30032.Rows.Count; f++)
{
DataRow dr = dt30032.Rows[f];
if (ymd != dr["AI2_YMD"].AsString())
{
ymd = dr["AI2_YMD"].AsString();
if (ymd == "999999")
{
row = rowTol + 1;
}
else
{
rowNum = rowNum + 1;
row = rowNum;
ws30030.Cells[row, 0].Value = (ymd.SubStr(0, 4).AsInt() - 1911).AsString() + "/" + ymd.SubStr(4, 2);
}
/* 未沖銷量 */
if (ymd == "999999")
{
colNum = dt30032OI.Rows[found]["RPT_SEQ_NO"].AsInt() - 1;
decimal sumAI2OI = dt30032OI.Compute("sum(AI2_OI)", "").AsDecimal();
decimal sumAI2DayCount = dt30032OI.Compute("sum(AI2_DAY_COUNT)", "").AsDecimal();
if (sumAI2DayCount == 0)
{
ws30030.Cells[row, colNum].Value = 0;
}
else
{
decimal sumAvgOI = Math.Round(sumAI2OI / sumAI2DayCount, MidpointRounding.AwayFromZero);
ws30030.Cells[row, colNum].Value = sumAvgOI;
}
}
else
{
DataRow[] find = dt30032OI.Select("ai2_ymd ='" + ymd + "'");
if (find.Length != 0)
{
found = dt30032OI.Rows.IndexOf(find[0]);
}
else
{
found = -1;
}
if (found >= 0)
{
colNum = dt30032OI.Rows[found]["RPT_SEQ_NO"].AsInt() - 1;
decimal AI2OI = dt30032OI.Rows[found]["AI2_OI"].AsDecimal();
decimal AI2DayCount = dt30032OI.Rows[found]["AI2_DAY_COUNT"].AsDecimal();
if (AI2DayCount == 0)
{
ws30030.Cells[row, colNum].Value = 0;
}
else
{
decimal avgOI = Math.Round(AI2OI / AI2DayCount, MidpointRounding.AwayFromZero);
ws30030.Cells[row, colNum].Value = avgOI;
}
}
}
colNum = colTol;
if (colNum > 0)
{
decimal mQntyYM = dt30032.Compute($@"sum(ai2_m_qnty)", $@"AI2_YMD='{ymd}'").AsDecimal() -
dt30032.Compute($@"sum(ai2_m_qnty)", $@"AI2_YMD='{ymd}' and substring(AI2_PARAM_KEY,1,3)='SUM'").AsDecimal();
decimal dayCountYM = dt30032.Compute("max(ai2_day_count)", $@"AI2_YMD='{ymd}'").AsDecimal();
ws30030.Cells[row, colNum].Value = Math.Round(mQntyYM / dayCountYM, MidpointRounding.AwayFromZero);
}
}
kindId = dr["AI2_PARAM_KEY"].AsString();
colNum = dr["RPT_SEQ_NO"].AsInt() - 1;
if (kindId == "RHF")
{
colNum = colNum;
}
decimal ai2MQnty = dr["AI2_M_QNTY"].AsDecimal();
decimal ai2DayCount = dr["AI2_DAY_COUNT"].AsDecimal();
ws30030.Cells[row, colNum].Value = Math.Round(ai2MQnty / ai2DayCount, MidpointRounding.AwayFromZero);
}
// 刪除空白列
if (rowTol > rowNum)
{
ws30030.Rows.Remove(rowNum + 1, rowTol - rowNum);
}
#endregion
rowNum = rowNum + 4;
rowKeep = rowNum;
#region wf_30033
int dayCount;
decimal taifex, tse, sgxDt;
rowTol = 0;
rptName = "當年每月TAIFEX期貨與TSE成交值之比較表";
rptId = "30033";
// 讀取資料
DataTable dt30033 = dao30030.d_30033(symd.SubStr(0, 4) + "01", eymd);
if (dt30033.Rows.Count <= 0)
{
MessageDisplay.Info(symd.SubStr(0, 4) + "01～" + eymd + "," + rptId + '－' + rptName + ",無任何資料!");
//return ResultStatus.Fail;
}
else
{
// 填入資料
rowTol = rowNum + 1 + 11;
/* 明細 */
for (f = 0; f < dt30033.Rows.Count; f++)
{
DataRow dr = dt30033.Rows[f];
rowNum = rowNum + 1;
taifex = dr["AA1_TAIFEX"].AsDecimal();
tse = dr["AA1_TSE"].AsDecimal();
sgxDt = dr["AA1_SGX_DT"].AsDecimal();
dayCount = dr["AA1_DAY_COUNT"].AsInt();
ws30030.Cells[rowNum, 0].Value = dr["AA1_YM"].AsString();
ws30030.Cells[rowNum, 1].Value = taifex;
ws30030.Cells[rowNum, 2].Value = tse;
ws30030.Cells[rowNum, 4].Value = dayCount;
}
// 刪除空白列
if (rowTol > rowNum)
{
ws30030.Rows.Remove(rowNum + 1, rowTol - rowNum);
}
flag++;
}
#endregion
rowTol = rowNum + 1;
rowNum = rowKeep;
#region wf_30034
string ym;
decimal value;
rptName = "當年每月TAIFEX期貨與TSE成交值之比較表";
rptId = "30034";
// 讀取資料
// 計算當月最後一日
if (eymd.SubStr(4, 2) == "12")
{
ym = (eymd.SubStr(0, 4).AsInt() + 1).AsString() + "01";
}
else
{
ym = eymd.SubStr(0, 4) + ("0" + (eymd.SubStr(4, 2).AsInt() + 1).AsString()).PadLeft(3, '0').SubStr(1, 2);
}
ym = PbFunc.relativedate((ym.SubStr(0, 4) + "/" + ym.SubStr(4, 2) + "/01").AsDateTime("yyyy/MM/dd"), -1).ToString("yyyyMMdd");
DataTable dt30034 = dao30030.d_30034(txtSDate.Text.SubStr(0, 4) + "0101",
ym,
txtSDate.Text.SubStr(0, 4) + "01",
eymd);
if (dt30034.Rows.Count <= 0)
{
MessageDisplay.Info(eymd.SubStr(0, 4) + "0101～" + ym + "," + rptId + '－' + rptName + ",無任何資料!");
//return ResultStatus.Fail;
}
else
{
// 每月
for (f = rowNum + 1; f <= rowTol - 1; f++)
{
ym = ws30030.Cells[f, 0].Value.AsString();
DataRow[] find = dt30034.Select("trim(stw_ymd) ='" + ym + "'");
if (find.Length != 0)
{
found = dt30034.Rows.IndexOf(find[0]);
}
else
{
found = -1;
}
if (found >= 0)
{
decimal stwAmt = dt30034.Rows[found]["STW_AMT"].AsDecimal();
decimal aa1UsRate = dt30034.Rows[found]["AA1_US_RATE"].AsDecimal();
decimal stwDays = dt30034.Rows[found]["STW_DAYS"].AsDecimal();
ws30030.Cells[f, 6].Value = stwAmt * aa1UsRate / stwDays / 100000000;
}
}
// 本年度
decimal sumC = dt30034.Compute("sum(cp_c)", "").AsDecimal();
decimal sumStwDays = dt30034.Compute("sum(stw_days)", "").AsDecimal();
value = sumC / sumStwDays / 100000000;
ws30030.Cells[rowTol, 6].Value = value;
flag++;
}
#endregion
if (flag <= 0)
{
File.Delete(file);
return(ResultStatus.Fail);
}
else
{
workbook.SaveDocument(file);
return(ResultStatus.Success);
}
}
/// <summary>
/// 期貨市場動態報導－期貨
/// </summary>
/// <param name="rptId"></param>
/// <param name="rptName"></param>
/// <param name="ws30011"></param>
private void wf_30011(string rptId, string rptName, Worksheet ws30011)
{
try {
rptName = "期貨市場動態報導－期貨";
rptId = "30011";
ShowMsg(rptId + "－" + rptName + " 轉檔中...");
string kindID = "", settleDate = "", index = "", indexStr = "", kindID2 = "";
int rowIndex = 0, mxwCnt = 0, mxwRow = 0, rowCnt = 0;
decimal value, mQnty, value2;
Range delRange;
indexStr = "000000";
ws30011.Cells[0, 10].Value = "民國" + (txtSDate.DateTimeValue.Year - 1911)
+ "年" + txtSDate.DateTimeValue.Month + "月" + txtSDate.DateTimeValue.Day + "日";
//讀取資料
DataTable dt30011 = dao30010.d_30011(txtSDate.DateTimeValue);
if (dt30011.Rows.Count == 0)
{
MessageDisplay.Info(txtSDate.Text + "," + rptId + '－' + rptName + ",無任何資料!");
labMsg.Visible = false;
return;
}
//RPT
daoRPT = new RPT();
DataTable dtRPT = daoRPT.ListAllByTXD_ID(rptId);
if (dtRPT.Rows.Count == 0)
{
MessageDisplay.Info(rptId + '－' + "RPT無任何資料!");
labMsg.Visible = false;
return;
}
flag++;
//填資料
mxwCnt = 0;
foreach (DataRow dr in dt30011.Rows)
{
mQnty = dr["AMIF_M_QNTY_TAL"].AsDecimal();
if (kindID2 != dr["AMIF_KIND_ID2"].AsString())
{
kindID2 = dr["AMIF_KIND_ID2"].AsString();
rowIndex = dr["RPT_SEQ_NO"].AsInt() - 1 - 1;
rowCnt = 0;
index = dr["RPT_VALUE_3"].AsString();
}
kindID = dr["AMIF_KIND_ID"].AsString();
rowIndex = rowIndex + 1;
if (kindID2 == "MXW")
{
mxwRow = rowIndex;
mxwCnt = mxwCnt + 1;
}
settleDate = dr["AMIF_SETTLE_DATE"].AsString();
if (settleDate == indexStr)
{
settleDate = "指數";
}
else
{
if (kindID == "STW" && rowCnt >= 2)
{
rowCnt = rowCnt + 1;
continue;
}
/* 第一筆不是指數,則跳一列 */
if (rowCnt == 0 && index == indexStr)
{
rowIndex = rowIndex + 1;
rowCnt = rowCnt + 1;
}
if (dr["AMIF_EXPIRY_TYPE"].AsString() == "W")
{
ws30011.Cells[rowIndex, 1].Value = settleDate.SubStr(settleDate.Length - 2, 2).AsInt() + "W" + kindID.SubStr(2, 1);
}
else
{
ws30011.Cells[rowIndex, 1].Value = settleDate.SubStr(settleDate.Length - 2, 2).AsInt();
}
}
value = dr["AMIF_OPEN_PRICE"].AsDecimal();
if ((value != 0 && mQnty > 0) || (settleDate == "指數" && value != 0))
{
ws30011.Cells[rowIndex, 2].Value = value;
}
value = dr["AMIF_HIGH_PRICE"].AsDecimal();
if (value != 0)
{
ws30011.Cells[rowIndex, 3].Value = value;
}
value = dr["AMIF_LOW_PRICE"].AsDecimal();
if (value != 0 || (settleDate == "指數" && value != 0))
{
ws30011.Cells[rowIndex, 4].Value = value;
}
value = dr["AMIF_CLOSE_PRICE"].AsDecimal();
if ((value != 0 && mQnty > 0) || (settleDate == "指數" && value != 0))
{
ws30011.Cells[rowIndex, 5].Value = value;
}
value2 = dr["AMIF_UP_DOWN_VAL"].AsDecimal();
if ((value != 0 && mQnty > 0) || (settleDate == "指數" && value != 0))
{
ws30011.Cells[rowIndex, 6].Value = value2;
if ((dr["AMIF_CLOSE_PRICE"].AsDecimal() - dr["AMIF_UP_DOWN_VAL"].AsDecimal()) == 0)
{
MessageDisplay.Error(kindID + " 收盤價-漲跌幅=0 ,計算漲跌點%造成除數為0");
return;
}
value2 = Math.Round((dr["AMIF_UP_DOWN_VAL"].AsDecimal() / (dr["AMIF_CLOSE_PRICE"].AsDecimal() - dr["AMIF_UP_DOWN_VAL"].AsDecimal())) * 100,
3, MidpointRounding.AwayFromZero);
ws30011.Cells[rowIndex, 7].Value = value2;
}
ws30011.Cells[rowIndex, 8].SetValue(dr["AMIF_M_QNTY_TAL"]);
if (settleDate != "指數")
{
if (kindID.SubStr(0, 3) != "STW")
{
ws30011.Cells[rowIndex, 9].SetValue(dr["AMIF_SETTLE_PRICE"]);
}
ws30011.Cells[rowIndex, 10].SetValue(dr["AMIF_OPEN_INTEREST"]);
}
rowCnt = rowCnt + 1;
}//foreach (DataRow dr in dt30011.Rows)
if (mxwCnt == 0)
{
mxwRow = dao30010.getMxwRow() - 1;
//delRange = ws30011.Range[(li_mxw_row).ToString() + ":" + (li_mxw_row + 1).ToString()];
//delRange.Delete(DeleteMode.EntireRow);
ws30011.Rows.Hide(mxwRow, mxwRow + 1);
}
else if (mxwCnt < 2)
{
//delRange = ws30011.Rows[(li_mxw_row + 1).ToString()];
//delRange.Delete(DeleteMode.EntireRow);
ws30011.Rows.Hide(mxwRow + 1, mxwRow + 1);
}
} catch (Exception ex) {
throw ex;
}
}
