/// <summary>
/// 按下[匯出]按鈕時
/// </summary>
/// <returns></returns>
protected override ResultStatus Export()
{
//1.check
if (gvKind.DataRowCount <= 0)
{
MessageDisplay.Normal("選擇的日期必須有契約資訊,請重新選擇日期");
return(ResultStatus.Fail);
}
//1.1檢查最少必須勾選一筆商品
gvDate.CloseEditor(); //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
gvDate.UpdateCurrentRow(); //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
gvKind.CloseEditor(); //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
gvKind.UpdateCurrentRow(); //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
DataTable dtTemp = (DataTable)gcKind.DataSource;
bool haveKind = false;
foreach (DataRow drKind in dtTemp.Rows)
{
if (drKind["CPR_SELECT"].AsString() == "Y")
{
haveKind = true;
break;
}
}
if (!haveKind)
{
MessageDisplay.Normal("必須勾選一筆契約");
return(ResultStatus.Fail);
}
if (chkModel.CheckedItemsCount < 1)
{
MessageDisplay.Error("請至少勾選一種指標種類", GlobalInfo.ErrorText);
return(ResultStatus.Fail);
}
//1.2檢查統計資料是否已經轉入完畢
string FinishedJob = PbFunc.f_get_jsw(_ProgramID, "E", txtEndDate.Text);
if (FinishedJob != "Y")
{
DialogResult chooseResult = MessageDisplay.Choose(string.Format("{0} 統計資料未轉入完畢,是否要繼續?", txtEndDate.Text), MessageBoxDefaultButton.Button2, GlobalInfo.QuestionText);
if (chooseResult != DialogResult.Yes)
{
return(ResultStatus.Fail);
}
}
string kindId = "";
Workbook workbook = new Workbook();
try {
//2.開始轉出資料
panFilter.Enabled = panSecond.Enabled = false;
labMsg.Visible = true;
labMsg.Text = "開始轉檔...";
this.Refresh();
DataTable dtDate = (DataTable)gcDate.DataSource;
string startDate = dtDate.Rows[dtDate.Rows.Count - 1]["SDATE"].AsDateTime().ToString("yyyyMMdd");
string endDate = dtDate.Rows[dtDate.Rows.Count - 1]["EDATE"].AsDateTime().ToString("yyyyMMdd");
#region 指標:SMA,EWMA,MAX
foreach (CheckedListBoxItem item in chkModel.Items)
{
if (item.CheckState == CheckState.Unchecked)
{
continue;
}
string modelType = "";
string modelName = "";
switch (item.Value)
{
case "chkSma":
modelType = "S";
modelName = "SMA";
break;
case "chkEwma":
modelType = "E";
modelName = "EWMA";
break;
case "chkMax":
modelType = "M";
modelName = "MAX";
break;
}
#region 商品
//每一個商品都會產生一個excel檔案
foreach (DataRow drKind in dtTemp.Rows)
{
if (drKind["CPR_SELECT"].AsString() != "Y")
{
continue;
}
//2.0 get some column data
string effDate = drKind["cpr_effective_date"].AsDateTime().ToString("yyyy/MM/dd");
Decimal lastRiskRate = drKind["last_risk_rate"].AsDecimal(0);
Decimal riskRateOrg = drKind["cpr_price_risk_rate_org"].AsDecimal(0);
kindId = drKind["CPR_KIND_ID"].AsString();
//2.1 copy template xlsx to target path and open
string excelDestinationPath = CopyExcelTemplateFile2(_ProgramID, modelName, kindId);
workbook.LoadDocument(excelDestinationPath);
Worksheet worksheet = workbook.Worksheets["RawData"];
//2.3寫入檔頭[M1:M6]
worksheet.Cells[0, 12].Value = kindId; //商品
worksheet.Cells[1, 12].Value = drKind["cpr_effective_date"].AsDateTime(); //日期,最近一次調整日期
if (drKind["cpr_price_risk_rate"] != DBNull.Value)
{
worksheet.Cells[2, 12].Value = drKind["cpr_price_risk_rate"].AsDecimal();//百分比,現行最小風險價格係數
}
if (drKind["last_risk_rate"] != DBNull.Value)
{
worksheet.Cells[3, 12].Value = drKind["last_risk_rate"].AsDecimal();//百分比,最近一次修改前之最小風險價格係數
}
if (drKind["risk_interval"] != DBNull.Value)
{
worksheet.Cells[4, 12].Value = drKind["risk_interval"].AsDecimal(); //百分比,最小風險價格係數級距
}
worksheet.Cells[5, 12].Value = DateTime.Today; //日期,作業日期
//2.4讀取子table data (mg1_ymd/mg1_risk/mg1_min_risk)
DataTable dtSingleKind = dao48040.ListKindByKindId(kindId, startDate, endDate, modelType);
if (dtSingleKind.Rows.Count <= 0)
{
File.Delete(excelDestinationPath);
//workbook.SaveDocument(excelDestinationPath);//存檔
labMsg.Text += string.Format("{0},{1}~{2}無任何資料!\r\n", kindId, startDate, endDate);
this.Refresh();
continue;
}
#region //2.5寫入五段日期基本資訊[F2:J6]
int rowIndex = 2;
int pos = 1;
foreach (DataRow drDate in dtDate.Rows)
{
worksheet.Cells[pos, 5].Value = drDate["SDATE"].AsString(); //資料起日
worksheet.Cells[pos, 6].Value = drDate["EDATE"].AsString(); //資料迄日
worksheet.Cells[pos, 7].Value = drDate["DAY_CNT"].AsInt(); //天數
//ken,使用DataView的Find之前,要指定Sort欄位(可多個欄位)
int filterIndex = dtSingleKind.Rows.IndexOf(dtSingleKind.Select($"mg1_ymd >= '{drDate["SDATE"].AsString()}'").FirstOrDefault());
if (filterIndex >= 0)
{
worksheet.Cells[pos, 8].Value = rowIndex + filterIndex;//起日位址(FirstRowIndex)
}
else
{
worksheet.Cells[pos, 8].Value = rowIndex;//起日位址(FirstRowIndex)
}
worksheet.Cells[pos, 9].Value = rowIndex + dtSingleKind.Rows.Count - 1;//迄日位址(LastRowIndex)
pos++;
}//foreach(DataRow drDate in dtDate.Rows) {
#endregion
//2.6寫入整個子table (日期/實際風險價格係數/最小風險價格係數)
worksheet.Import(dtSingleKind, false, 1, 0);//dataTable, isAddHeader, RowFirstIndex, ColFirstIndex
worksheet.Range["A1"].Select();
worksheet.ScrollToRow(0);
//2.7刪多的圖表(共五個,起始rowIndex=24,每個高31)
//ken,從最後一個圖表開始刪除比較正確,才不會跑位,最後圖表rowIndex=148
worksheet = workbook.Worksheets["Graph"];
int graphRowIndex = 148;
int graphHeight = 31;
for (int k = dtDate.Rows.Count - 1; k >= 0; k--)
{
if (dtDate.Rows[k]["AI2_SELECT"].AsString() == "N")
{
worksheet.Rows.Remove(graphRowIndex, graphHeight - 1);
}
graphRowIndex -= graphHeight;
}//for(int k = dtDate.Rows.Count - 1;k >= 0;k--) {
//2.8寫入[註3]資訊
riskRateOrg = Math.Round(riskRateOrg * 100, 1, MidpointRounding.AwayFromZero);
if (lastRiskRate == 0)
{
worksheet.Cells[18, 0].Value = string.Format("註3：上市日起至今最小風險價格係數均為{0}%", riskRateOrg.ToString());
}
else
{
lastRiskRate = Math.Round(lastRiskRate * 100, 1, MidpointRounding.AwayFromZero);
worksheet.Cells[18, 0].Value = string.Format("註3：最小風險價格係數自{0}起由{1}%調整為{2}",
effDate,
lastRiskRate.ToString(),
riskRateOrg.ToString());
}
//2.9存檔
workbook.SaveDocument(excelDestinationPath);
}//foreach (DataRow drKind in dtTemp.Rows) 商品
#endregion
}
#endregion
return(ResultStatus.Success);
} catch (Exception ex) {
WriteLog(ex, kindId);
} finally {
panFilter.Enabled = panSecond.Enabled = true;
labMsg.Text = "";
labMsg.Visible = false;
}
return(ResultStatus.Fail);
}
protected override ResultStatus Export()
{
#region 檢查
//要先call f_get_jsw
string rtn = "";
rtn = PbFunc.f_get_jsw("60420", "E", GlobalInfo.OCF_DATE.ToString("yyyy/MM/dd"));
//ls_rtn = f_get_jsw(is_txn_id,'E',em_date.text)
if (rtn != "Y")
{
DialogResult result = MessageBox.Show(txtEndDate.Text + " 統計資料未轉入完畢,是否要繼續?",
"注意", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.No)
{
lblProcessing.Visible = false;
return(ResultStatus.Fail);
}
}
#endregion
dao60420 = new D60420();
base.Export();
lblProcessing.Visible = true;
this.Refresh();
Thread.Sleep(5);
//複製檔案
string excelDestinationPath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
//開啟檔案
Workbook workbook = new Workbook();
workbook.LoadDocument(excelDestinationPath);
#region wf_60410a (sheet 1/4/5)
string rptName, rptId;
int i, j, rowNum;
decimal value;
/*************************************
* ls_rpt_name = 報表名稱
* ls_rpt_id = 報表代號
*************************************/
rptName = "檢查表";
rptId = "60410_1a";
lblProcessing.Text = rptId + "－" + rptName + " 轉檔中...";
this.Refresh();
Thread.Sleep(5);
//讀取資料
DataTable dt60410_1a = dao60420.d_60410_1a(txtStartDate.DateTimeValue, txtEndDate.DateTimeValue);
if (dt60410_1a.Rows.Count == 0)
{
MessageDisplay.Info(string.Format("{0},{1},無任何資料!", txtEndDate.EditValue, rptId + "－" + rptName));
}
//切換Sheet
//1. 未符合「成份股檔數≧10」
Worksheet sheet1 = workbook.Worksheets[0];
//填資料
if (cbx1.Checked == false)
{
sheet1.Cells[2, 0].Value = "標準1. 全部「成份股檔數」";
}
rowNum = 3;
for (i = 0; i < dt60410_1a.Rows.Count; i++)
{
DataRow dr60410_1a = dt60410_1a.Rows[i];
if (cbx1.Checked == false || dr60410_1a["tot_cnt"].AsInt() < sleCond1.Text.AsInt())
{
rowNum = rowNum + 1;
sheet1.Cells[rowNum, 0].Value = dr60410_1a["cod_name"].AsString();
sheet1.Cells[rowNum, 1].Value = (DateTime.ParseExact(dr60410_1a["ymd"].AsString(), "yyyyMMdd", CultureInfo.InvariantCulture)).ToString("yyyy/M/d").AsDateTime();
sheet1.Cells[rowNum, 2].SetValue(dr60410_1a["tot_cnt"]);
}
}
sheet1.ScrollTo(0, 0);
//4. 未符合「最低25%權重之成份股，檔數在15檔(含)以上，過去半年每日合計成交值之平均值＞3,000萬美元」
Worksheet sheet4 = workbook.Worksheets[3];
//填資料
if (cbx4.Checked == false)
{
sheet4.Cells[2, 0].Value = "標準4. 全部「最低25%權重之成份股，檔數在15檔(含)以上，過去半年每日合計成交值」";
}
rowNum = 4;
for (i = 0; i < dt60410_1a.Rows.Count; i++)
{
DataRow dr60410_1a = dt60410_1a.Rows[i];
if (dr60410_1a["cnt25"].AsInt() >= sleCond41.Text.AsInt() &&
Math.Round(dr60410_1a["avg_amt_mth_usd"].AsDecimal() / 10000, 0) <= sleCond42.Text.AsDecimal() ||
cbx4.Checked == false)
{
rowNum = rowNum + 1;
sheet4.Cells[rowNum, 0].Value = dr60410_1a["cod_name"].AsString();
sheet4.Cells[rowNum, 1].Value = (DateTime.ParseExact(dr60410_1a["ymd"].AsString(), "yyyyMMdd", CultureInfo.InvariantCulture)).ToString("yyyy/M/d").AsDateTime();
sheet4.Cells[rowNum, 2].SetValue(dr60410_1a["cnt25"]);
sheet4.Cells[rowNum, 3].SetValue(dr60410_1a["weight25"]);
sheet4.Cells[rowNum, 4].SetValue(dr60410_1a["avg_amt_cls_usd"]);
sheet4.Cells[rowNum, 5].SetValue(dr60410_1a["avg_amt_cls_tw"]);
sheet4.Cells[rowNum, 6].SetValue(dr60410_1a["avg_amt_mth_usd"]);
sheet4.Cells[rowNum, 7].SetValue(dr60410_1a["avg_amt_mth_tw"]);
sheet4.Cells[rowNum, 8].SetValue(dr60410_1a["day_amt_cls_usd"]);
sheet4.Cells[rowNum, 9].SetValue(dr60410_1a["day_amt_cls_tw"]);
sheet4.Cells[rowNum, 10].SetValue(dr60410_1a["day_amt_mth_usd"]);
sheet4.Cells[rowNum, 11].SetValue(dr60410_1a["day_amt_mth_tw"]);
}
}
sheet4.ScrollTo(0, 0);
//5. 未符合「最低25%權重之成份股，檔數低於15檔，過去半年每日合計成交值之平均值＞5,000萬美元」
Worksheet sheet5 = workbook.Worksheets[4];
//填資料
if (cbx5.Checked == false)
{
sheet5.Cells[2, 0].Value = "標準5. 全部「最低25%權重之成份股，檔數低於15檔，過去半年每日合計成交值」";
}
rowNum = 4;
for (i = 0; i < dt60410_1a.Rows.Count; i++)
{
DataRow dr60410_1a = dt60410_1a.Rows[i];
if (dr60410_1a["cnt25"].AsInt() < sleCond41.Text.AsInt() &&
(Math.Round(dr60410_1a["avg_amt_mth_usd"].AsDecimal() / 10000, 0) <= sleCond52.Text.AsDecimal() ||
cbx5.Checked == false))
{
rowNum = rowNum + 1;
sheet5.Cells[rowNum, 0].Value = dr60410_1a["cod_name"].AsString();
sheet5.Cells[rowNum, 1].Value = (DateTime.ParseExact(dr60410_1a["ymd"].AsString(), "yyyyMMdd", CultureInfo.InvariantCulture)).ToString("yyyy/M/d").AsDateTime();
sheet5.Cells[rowNum, 2].SetValue(dr60410_1a["cnt25"]);
sheet5.Cells[rowNum, 3].SetValue(dr60410_1a["weight25"]);
sheet5.Cells[rowNum, 4].SetValue(dr60410_1a["avg_amt_cls_usd"]);
sheet5.Cells[rowNum, 5].SetValue(dr60410_1a["avg_amt_cls_tw"]);
sheet5.Cells[rowNum, 6].SetValue(dr60410_1a["avg_amt_mth_usd"]);
sheet5.Cells[rowNum, 7].SetValue(dr60410_1a["avg_amt_mth_tw"]);
sheet5.Cells[rowNum, 8].SetValue(dr60410_1a["day_amt_cls_usd"]);
sheet5.Cells[rowNum, 9].SetValue(dr60410_1a["day_amt_cls_tw"]);
sheet5.Cells[rowNum, 10].SetValue(dr60410_1a["day_amt_mth_usd"]);
sheet5.Cells[rowNum, 11].SetValue(dr60410_1a["day_amt_mth_tw"]);
}
}
sheet5.ScrollTo(0, 0);
#endregion
#region wf_60410_2 (sheet 2)
rptName = "2. 未符合「權重最大之成份股權重≦30%」";
rptId = "60410_2";
lblProcessing.Text = rptId + "－" + rptName + " 轉檔中...";
this.Refresh();
Thread.Sleep(5);
//讀取資料
DataTable dt60410_2;
if (cbx2.Checked == true)
{
dt60410_2 = dao60420.d_60410_2(txtStartDate.DateTimeValue, txtEndDate.DateTimeValue, sleCond2.Text.AsDecimal());
}
else
{
dt60410_2 = dao60420.d_60410_2(txtStartDate.DateTimeValue, txtEndDate.DateTimeValue, -1.AsDecimal());
}
if (dt60410_2.Rows.Count == 0)
{
//PB把這段註解掉，先照著寫
//MessageDisplay.Info(string.Format("{0},{1},無任何資料!", txtEndDate.EditValue, ls_rpt_id + "－" + ls_rpt_name));
}
//切換Sheet
//2. 未符合「權重最大之成份股權重≦30%」
Worksheet sheet2 = workbook.Worksheets[1];
//填資料
if (cbx2.Checked == false)
{
sheet2.Cells[2, 0].Value = "標準2. 全部「權重最大之成份股權重%」";
}
rowNum = 3;
for (i = 0; i < dt60410_2.Rows.Count; i++)
{
DataRow dr60410_2 = dt60410_2.Rows[i];
if (cbx2.Checked == true && dr60410_2["index_weight"].AsDecimal() <= 0.3.AsDecimal())
{
continue;
}
rowNum = rowNum + 1;
sheet2.Cells[rowNum, 0].Value = dr60410_2["cod_name"].AsString();
sheet2.Cells[rowNum, 1].Value = dr60410_2["TSE3_YMD"].AsDateTime().ToString("yyyy/M/d").AsDateTime();
sheet2.Cells[rowNum, 2].SetValue(dr60410_2["TSE3_SID"]);
sheet2.Cells[rowNum, 3].Value = dr60410_2["TFXMS_SNAME"].AsString();
sheet2.Cells[rowNum, 4].SetValue(dr60410_2["INDEX_WEIGHT"]);
}
sheet2.ScrollTo(0, 0);
#endregion
#region wf_60410_3 (sheet 3)
rptName = "3. 未符合「權重前五大成份股合計權重≦60%」";
rptId = "60412_3"; //PB就是這樣寫
lblProcessing.Text = rptId + "－" + rptName + " 轉檔中...";
this.Refresh();
Thread.Sleep(5);
//讀取資料
DataTable dt60412_3;
if (cbx3.Checked == true)
{
dt60412_3 = dao60420.d_60412_3(txtStartDate.DateTimeValue, txtEndDate.DateTimeValue, sleCond3.Text.AsDecimal());
}
else
{
dt60412_3 = dao60420.d_60412_3(txtStartDate.DateTimeValue, txtEndDate.DateTimeValue, -1.AsDecimal());
}
if (dt60412_3.Rows.Count == 0)
{
//PB把這段註解掉，先照著寫
//MessageDisplay.Info(string.Format("{0},{1},無任何資料!", txtEndDate.EditValue, ls_rpt_id + "－" + ls_rpt_name));
}
//切換Sheet
//3. 未符合「權重前五大成份股合計權重≦60%」
Worksheet sheet3 = workbook.Worksheets[2];
//填資料
if (cbx3.Checked == false)
{
sheet3.Cells[2, 0].Value = "標準3. 全部「權重前五大成份股合計權重%」";
}
rowNum = 3;
for (i = 0; i < dt60412_3.Rows.Count; i++)
{
DataRow dr60412_3 = dt60412_3.Rows[i];
rowNum = rowNum + 1;
sheet3.Cells[rowNum, 0].Value = dr60412_3["cod_name"].AsString();
sheet3.Cells[rowNum, 1].Value = dr60412_3["TSE3_DATE"].AsDateTime().ToString("yyyy/M/d").AsDateTime();
sheet3.Cells[rowNum, 2].SetValue(dr60412_3["TSE5_25_WEIGHT"]);
sheet3.Cells[rowNum, 3].SetValue(dr60412_3["TSE3_DESC_SEQ"]);
sheet3.Cells[rowNum, 4].SetValue(dr60412_3["TSE3_SID"]);
sheet3.Cells[rowNum, 5].SetValue(dr60412_3["TFXMS_SNAME"]);
sheet3.Cells[rowNum, 6].SetValue(dr60412_3["INDEX_WEIGHT"]);
}
sheet3.ScrollTo(0, 0);
#endregion
//預設打開第一張sheet
workbook.Worksheets.ActiveWorksheet = workbook.Worksheets[0];
workbook.SaveDocument(excelDestinationPath);
lblProcessing.Visible = false;
this.Refresh();
Thread.Sleep(5);
return(ResultStatus.Success);
}
/// <summary>
/// 按下[匯出]按鈕時
/// </summary>
/// <returns></returns>
protected override ResultStatus Export()
{
//1.check
if (gvKind.DataRowCount <= 0)
{
MessageDisplay.Normal("選擇的日期必須有契約資訊,請重新選擇日期");
return(ResultStatus.Fail);
}
//1.1檢查最少必須勾選一筆商品
gvDate.CloseEditor(); //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
gvDate.UpdateCurrentRow(); //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
gvKind.CloseEditor(); //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
gvKind.UpdateCurrentRow(); //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
DataTable dtTemp = (DataTable)gcKind.DataSource;
bool haveKind = false;
foreach (DataRow drKind in dtTemp.Rows)
{
if (drKind["CPR_SELECT"].AsString() == "Y")
{
haveKind = true;
break;
}
}
if (!haveKind)
{
MessageDisplay.Normal("必須勾選一筆契約");
return(ResultStatus.Fail);
}
if (chkModel.CheckedItemsCount < 1)
{
MessageDisplay.Error("請至少勾選一種指標種類", GlobalInfo.ErrorText);
return(ResultStatus.Fail);
}
//1.2檢查統計資料是否已經轉入完畢
string FinishedJob = PbFunc.f_get_jsw(_ProgramID, "E", txtEndDate.Text);
if (FinishedJob != "Y")
{
DialogResult chooseResult = MessageDisplay.Choose(string.Format("{0} 統計資料未轉入完畢,是否要繼續?", txtEndDate.Text), MessageBoxDefaultButton.Button2, GlobalInfo.QuestionText);
if (chooseResult != DialogResult.Yes)
{
return(ResultStatus.Fail);
}
}
try {
//2.開始轉出資料
panFilter.Enabled = panSecond.Enabled = false;
labMsg.Visible = true;
labMsg.Text = "開始轉檔...";
this.Refresh();
//2.1 copy template xlsx to target path and open
Workbook workbook = new Workbook();
string originalFilePath = Path.Combine(GlobalInfo.DEFAULT_EXCEL_TEMPLATE_DIRECTORY_PATH, _ProgramID + "." + FileType.XLSX.ToString().ToLower());
string excelDestinationPath = "";
DataTable dtDate = (DataTable)gcDate.DataSource;
#region 指標:SMA,EWMA,MAX
foreach (CheckedListBoxItem item in chkModel.Items)
{
if (item.CheckState == CheckState.Unchecked)
{
continue;
}
int sheetIndex = 0;
int flag = 0;
string modelType = "";
switch (item.Value)
{
case "chkSma":
excelDestinationPath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH,
_ProgramID + "_SMA_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss.") + FileType.XLSX.ToString().ToLower());
modelType = "S";
break;
case "chkEwma":
excelDestinationPath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH,
_ProgramID + "_EWMA_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss.") + FileType.XLSX.ToString().ToLower());
modelType = "E";
break;
case "chkMax":
excelDestinationPath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH,
_ProgramID + "_MAX_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss.") + FileType.XLSX.ToString().ToLower());
modelType = "M";
break;
}
//copy template and change filename
File.Copy(originalFilePath, excelDestinationPath, true);
workbook.LoadDocument(excelDestinationPath);
#region 時段
//每個時間區間為一個sheet,總共5個
foreach (DataRow drDate in dtDate.Rows)
{
string monDiff = drDate["MON_DIFF"].AsString(); //期間
string startDate = drDate["SDATE"].AsDateTime().ToString("yyyyMMdd"); //資料起日
string endDate = drDate["EDATE"].AsDateTime().ToString("yyyyMMdd"); //資料迄日
//DateTime startDate = drDate["SDATE"].AsDateTime();//資料起日
//DateTime endDate = drDate["EDATE"].AsDateTime();//資料迄日
int dayCount = drDate["DAY_CNT"].AsInt(); //天數
//2.2跳到指定sheet,寫檔頭
Worksheet worksheet = workbook.Worksheets[sheetIndex++];
worksheet.Cells[1, 2].Value = cbxSubType.Text;
worksheet.Cells[2, 11].Value = string.Format("列印日期：{0}", DateTime.Now.ToString("yyyy/MM/dd"));
worksheet.Cells[2, 1].Value = string.Format("{0}({1}～{2})，計{3}天",
monDiff,
drDate["SDATE"].AsDateTime().ToString("yyyy/MM/dd"),
drDate["EDATE"].AsDateTime().ToString("yyyy/MM/dd"),
dayCount.ToString());
#region //2.3分別讀取每個商品的詳細資訊
int rowIndex = 7;
int emptyRowCount = 60; //template 空白行的數量
int kindCount = 0;
//逐一看勾選的商品有哪些
foreach (DataRow drKind in dtTemp.Rows)
{
if (drKind["CPR_SELECT"].AsString() != "Y")
{
continue;
}
kindCount++;
string kindId = drKind["cpr_kind_id"].AsString(); //契約ID
Decimal riskRate = drKind["cpr_price_risk_rate"].AsDecimal(); //現行最小風險價格係數
Decimal interval = drKind["risk_interval"].AsDecimal(); //最小風險價格係數級距
//2.3.1讀取子table data
DataTable dtSingleKind = dao48030.ListKindByKindId(startDate, endDate, riskRate, interval, kindId, modelType);
if (dtSingleKind.Rows.Count <= 0)
{
labMsg.Text += string.Format("{0},{1}~{2}無任何資料!\r\n", kindId, startDate, endDate);
this.Refresh();
continue;
}
//2.3.2寫入明細
//ken,原則上一個商品只會找到一筆明細(已經group by)
DataRow drDetail = dtSingleKind.Rows[0];
int tempCount = drDetail["cnt"].AsInt();
Decimal level_1 = drDetail["level_1"].AsDecimal();
Decimal level_23 = drDetail["level_23"].AsDecimal();
Decimal level_4 = drDetail["level_4"].AsDecimal();
worksheet.Cells[rowIndex, 0].Value = drDetail["mg1_kind_id"].AsString();
worksheet.Cells[rowIndex, 1].Value = drDetail["avg_risk"].AsDecimal();
worksheet.Cells[rowIndex, 2].Value = drDetail["max_risk"].AsDecimal();
worksheet.Cells[rowIndex, 3].Value = drDetail["min_risk"].AsDecimal();
worksheet.Cells[rowIndex, 4].Value = riskRate;
worksheet.Cells[rowIndex, 6].Value = level_1;
worksheet.Cells[rowIndex, 7].Value = Math.Round(level_1 / tempCount, 4, MidpointRounding.AwayFromZero);
worksheet.Cells[rowIndex, 8].Value = level_23;
worksheet.Cells[rowIndex, 9].Value = Math.Round(level_23 / tempCount, 4, MidpointRounding.AwayFromZero);
worksheet.Cells[rowIndex, 10].Value = level_4;
worksheet.Cells[rowIndex, 11].Value = Math.Round(level_4 / tempCount, 4, MidpointRounding.AwayFromZero);
worksheet.Cells[rowIndex, 12].Value = interval;
rowIndex++;
flag++;
}//foreach (DataRow drKind in dtTemp.Rows) 商品
#endregion
//2.4刪除空白列 (結束商品foreach才刪除空白列，跑下一個sheet)
if (kindCount < emptyRowCount)
{
worksheet.Rows.Remove(rowIndex, emptyRowCount - kindCount);
}
}//foreach (DataRow drDate in dtDate.Rows) 時段
#endregion
//2.9存檔
if (flag > 0)
{
workbook.SaveDocument(excelDestinationPath);
}
else
{
File.Delete(excelDestinationPath);
}
}//foreach (CheckedListBoxItem item in chkModel.Items) 指標
#endregion
//if (FlagAdmin)
// System.Diagnostics.Process.Start(excelDestinationPath);
return(ResultStatus.Success);
} catch (Exception ex) {
WriteLog(ex);
} finally {
panFilter.Enabled = panSecond.Enabled = true;
labMsg.Text = "";
labMsg.Visible = false;
}
return(ResultStatus.Fail);
}
protected override ResultStatus Export()
{
try {
#region 輸入&日期檢核
string lsRtn = PbFunc.f_get_jsw(_ProgramID, "E", txtEndDate.Text);
DialogResult liRtn;
if (lsRtn != "Y")
{
liRtn = MessageDisplay.Choose(string.Format("{0} 統計資料未轉入完畢,是否要繼續?", txtEndDate.Text), MessageBoxDefaultButton.Button2, GlobalInfo.QuestionText);
if (liRtn == DialogResult.No)
{
labMsg.Visible = false;
Cursor.Current = Cursors.Arrow;
return(ResultStatus.Fail);
} //if (liRtn == DialogResult.Yes)
} //if (lsRtn != "Y")
//if (!txtStartDate.IsDate(txtStartDate.Text , CheckDate.Start)
// || !txtEndDate.IsDate(txtEndDate.Text , CheckDate.End)) {
// return ResultStatus.Fail; ;
//}
if (string.Compare(txtStartDate.Text, txtEndDate.Text) > 0)
{
MessageDisplay.Error(CheckDate.Datedif, GlobalInfo.ErrorText);
return(ResultStatus.Fail);;
}
#endregion
//1. ready
panFilter.Enabled = false;
labMsg.Visible = true;
labMsg.Text = "開始轉檔...";
this.Cursor = Cursors.WaitCursor;
this.Refresh();
Thread.Sleep(5);
//2. copy template xls to target path
string excelDestinationPath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
//3. open xls
Workbook workbook = new Workbook();
workbook.LoadDocument(excelDestinationPath);
//4. write data
int row;
bool res1 = false, res2 = false, res3 = false;
row = 3;
res1 = wf_30410(workbook, SheetNo.tradeSum, row);
row = 4; //PB這邊帶1，但進去後帶回4
res2 = wf_30411(workbook, SheetNo.tradeDetail, row);
row = 4; //PB這邊帶1，但進去後帶回4
res3 = wf_30412(workbook, SheetNo.oint, row);
if (!res1 && !res2 && !res3)
{
File.Delete(excelDestinationPath);
return(ResultStatus.Fail);
}
//5. save
workbook.SaveDocument(excelDestinationPath);
labMsg.Visible = false;
if (FlagAdmin)
{
System.Diagnostics.Process.Start(excelDestinationPath);
}
return(ResultStatus.Success);
} catch (Exception ex) {
WriteLog(ex);
} finally {
panFilter.Enabled = true;
labMsg.Text = "";
labMsg.Visible = false;
this.Cursor = Cursors.Arrow;
}
return(ResultStatus.Fail);
}
protected override ResultStatus Export()
{
base.Export();
#region 確認統計資料未轉入完畢
string lsRtn = PbFunc.f_get_jsw(_ProgramID, "E", txtDate.Text);
DialogResult liRtn;
if (lsRtn != "Y")
{
liRtn = MessageDisplay.Choose(String.Format("{0} 統計資料未轉入完畢,是否要繼續?", txtDate.Text));
if (liRtn == DialogResult.No)
{
labMsg.Visible = false;
Cursor.Current = Cursors.Arrow;
return(ResultStatus.Fail);
}
}//if (lsRtn != "Y")
#endregion
try {
//1. 判斷為單一商品還是全部
DataTable dt = dao40130.GetDataList(txtDate.DateTimeValue, "%");
DataTable dtSelect = new DataTable();
string kindId = dwKindId.EditValue.AsString();
if (kindId != "%")
{
dtSelect = dt.Filter("mgt2_kind_id = '" + kindId + "'");
}
else //kindId = "%"
{
dtSelect = dt.Filter("mgt2_kind_id <> '%'");
}
//1.1 準備開檔
string originalFilePath = "";
string destinationFilePath = "";
Workbook workbook = new Workbook();
//2. 填資料
foreach (DataRow dr in dtSelect.Rows)
{
string fileKind = dr["mgt2_kind_id"].AsString();
//2.1 開啟&複製檔案(因可能有多個excel所以在迴圈裡兜路徑)
originalFilePath = Path.Combine(GlobalInfo.DEFAULT_EXCEL_TEMPLATE_DIRECTORY_PATH,
string.Format("{0}.{1}", _ProgramID, FileType.XLS.ToString().ToLower()));
destinationFilePath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH,
string.Format("{0}({1})_{2}.{3}", _ProgramID, fileKind, DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss"), FileType.XLS.ToString().ToLower()));
File.Copy(originalFilePath, destinationFilePath, true);
workbook.LoadDocument(destinationFilePath);
kindId = dr["mgt2_kind_id"].AsString() + "%";
string tmpKindId = kindId.SubStr(2, 1);
if (tmpKindId == "F") //Sheet:期貨data
{
wf_40131(workbook, kindId);
}
else //Sheet:選擇權data
{
wf_40132(workbook, kindId);
}
//3. save
workbook.SaveDocument(destinationFilePath);
}//foreach (DataRow dr in dt.Rows)
labMsg.Visible = false;
return(ResultStatus.Success);
} catch (Exception ex) {
WriteLog(ex);
} finally {
panFilter.Enabled = true;
labMsg.Text = "";
labMsg.Visible = false;
this.Cursor = Cursors.Arrow;
}
return(ResultStatus.Fail);
}