/// <summary>
/// wf_30412 (sheet3 data)
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheetNo">SheetNo.tradeDetail</param>
/// <param name="row"> 4 </param>
/// <returns></returns>
protected bool wf_30412(Workbook workbook, SheetNo sheetNo, int row)
{
string rptName = "股票期貨各標的未平倉量分佈明細統計表"; //報表標題名稱
string rptId = "30412";
ShowMsg(string.Format("{0}－{1} 轉檔中...", rptId, rptName));
try {
//1. 取得資料最大日期, 抓取OI用 (在wf_30410取得)
string maxDate = new D30410().GetMaxDate(StartDate, EndDate);
if (string.IsNullOrEmpty(maxDate))
{
MessageDisplay.Info(string.Format("{0}~{1},{2} - {3},無任何資料!", StartDate, EndDate, rptId, rptName), GlobalInfo.ResultText);
return(false);
}//if (string.IsNullOrEmpty(maxDate))
DateTime eDate = DateTime.ParseExact(maxDate, "yyyyMMdd", null); //yyyy/MM/dd
//2. 讀取資料
DataTable dt30412 = new D30410().ListData2(txtStartDate.DateTimeValue, eDate);
if (dt30412.Rows.Count <= 0)
{
MessageDisplay.Info(string.Format("{0}~{1},{2} - {3},無任何資料!", txtStartDate.Text, txtEndDate.Text, rptId, rptName), GlobalInfo.ResultText);
return(false);
} //if (dt.Rows.Count <= 0 )
//3. 切換Sheet
Worksheet ws30412 = workbook.Worksheets[(int)sheetNo];
ws30412.Range["A1"].Select();
ws30412.Cells[1, 0].Value = txtStartDate.Text;
ws30412.Cells[1, 1].Value = txtEndDate.Text;
//3.1 撈資料列總數
//PB這邊帶入參數為txnId = 30410 , txdId = 30410,兩者撈出皆為500
int rowCnt = new RPT().DataByRptId("30410", "30411").AsInt();
int rowTotal = row + rowCnt;
//4.填資料(交割年月)
int found = 0;
int maxSeqNo = dt30412.Compute("Max(seq_no)", "").AsInt(); //取得seq_no欄位的最大值
for (int w = 1; w <= maxSeqNo; w++)
{
if (dt30412.Select("seq_no =" + w).Length != 0)
{
found = dt30412.Rows.IndexOf(dt30412.Select(string.Format("seq_no ={0}", w))[0]);
}
if (found >= 0)
{
string text = dt30412.Rows[found]["amif_settle_date"].AsString();
ws30412.Cells[3, w + 2].Value = text.SubStr(0, 4) + "/" + text.SubStr(4, 2);
}
}
string kindId = "";
foreach (DataRow dr in dt30412.Rows)
{
string amifKindId = dr["amif_kind_id"].AsString();
string apdkName = dr["apdk_name"].AsString();
decimal amifOpenInt = dr["amif_open_interest"].AsDecimal();
found = dr["seq_no"].AsInt();
if (kindId != amifKindId)
{
kindId = amifKindId;
row++;
ws30412.Cells[row - 1, 0].Value = kindId;
ws30412.Cells[row - 1, 1].Value = apdkName;
}
ws30412.Cells[row - 1, found + 2].Value = amifOpenInt;
}
//4. 刪除空白列
Range ra = ws30412.Range[string.Format("{0}:504", row + 1)];
ra.Delete(DeleteMode.EntireRow);
ws30412.Range["A1"].Select();
ws30412.ScrollToRow(0);
return(true);
} catch (Exception ex) {
WriteLog(ex);
return(false);
}
}
/// <summary>
/// wf_30410 (sheet1 data)
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheetNo">SheetNo.tradeSum</param>
/// <param name="row"> 3 </param>
/// <returns></returns>
protected bool wf_30410(Workbook workbook, SheetNo sheetNo, int row)
{
string rptName = "股票期貨各標的交易概況統計表"; //報表標題名稱
labMsg.Text = _ProgramID + "－" + rptName + " 轉檔中...";
try {
//1. 取得資料最大日期, 抓取OI用
string maxDate = new D30410().GetMaxDate(StartDate, EndDate);
if (string.IsNullOrEmpty(maxDate))
{
MessageDisplay.Info(string.Format("{0}~{1},{2} - {3},無任何資料!", StartDate, EndDate, _ProgramID, rptName), GlobalInfo.ResultText);
return(false);
}//if (string.IsNullOrEmpty(maxDate))
//2. 讀取資料
string eDate = maxDate;
DataTable dt30410 = new D30410().ListData(StartDate, eDate);
if (dt30410.Rows.Count <= 0)
{
MessageDisplay.Info(string.Format("{0}~{1},{2} - {3},無任何資料!", StartDate, eDate, _ProgramID, rptName), GlobalInfo.ResultText);
} //if (dt.Rows.Count <= 0 )
//3. 切換Sheet
Worksheet ws = workbook.Worksheets[(int)sheetNo];
ws.Cells[1, 0].Value = txtStartDate.Text;
ws.Cells[1, 1].Value = txtEndDate.Text;
int rowTotal = new RPT().DataByRptId("30410", "30410").AsInt(); //將rowTotal轉為int使用
rowTotal += row;
string KindId = "19000101";
foreach (DataRow dr in dt30410.Rows)
{
row++;
string ai2KindId = dr["ai2_kind_id"].AsString();
string apdkName = dr["apdk_name"].AsString();
int exRow = row - 1;
if (KindId != ai2KindId)
{
KindId = ai2KindId;
ws.Cells[exRow, 0].Value = KindId;
ws.Cells[exRow, 1].Value = apdkName;
}
ws.Cells[exRow, 2].Value = dr["ai2_m_qnty"].AsDecimal();
ws.Cells[exRow, 3].Value = dr["ai2_oi"].AsDecimal();
ws.Cells[exRow, 4].Value = dr["am10_cnt"].AsDecimal();
ws.Cells[exRow, 6].Value = dr["am9_acc_cnt"].AsDecimal();
ws.Cells[exRow, 8].Value = dr["ab4_id_cnt"].AsDecimal();
}
//4. 刪除空白列
if (dt30410.Rows.Count < rowTotal)
{
ws.Rows.Remove(row, rowTotal - row);
}
ws.Range["A1"].Select();
ws.ScrollToRow(0);
return(true);
} catch (Exception ex) {
WriteLog(ex);
return(false);
}
}
