public FormParent OpenForm(string txn_id, string txn_name)
{
//Austin 20190813 平行測試期間正式環境不開放維護功能
if (GlobalDaoSetting.GetConnectionInfo.ConnectionName == "CI")
{
if (maintainItem.Contains(txn_id))
{
MessageDisplay.Normal("維護功能暫不使用!");
accordionMenu.Focus();
accordionMenu.KeyNavHelperEx.SelectedElement = accordionMenu.SelectedElement;
return(null);
}
}
if (txn_id == "Z2010")
{
OpenForm();
return(new FormParent());
}
var dllIndividual = Assembly.LoadFile(Application.ExecutablePath);
string typeFormat = "{0}.FormUI.Prefix{1}.W{2}";
Type myType = dllIndividual.GetType(string.Format(typeFormat, Path.GetFileNameWithoutExtension(Application.ExecutablePath), txn_id.Substring(0, 1), txn_id));
if (myType == null)
{
MessageDisplay.Error("無此程式");
accordionMenu.Focus();
accordionMenu.KeyNavHelperEx.SelectedElement = accordionMenu.SelectedElement;
return(null);
}
object myObj = Activator.CreateInstance(myType, txn_id, txn_name);
FormParent formInstance = (FormParent)myObj;
//int width = SystemInformation.PrimaryMonitorSize.Width;
//if (width <= 1600) {
// formInstance.WindowState = FormWindowState.Maximized;
//}
if (formInstance.BeforeOpen() == ResultStatus.Success)
{
formInstance.MdiParent = this;
formInstance.RibbonAlwaysAtBack = false;
formInstance.FormClosed += new FormClosedEventHandler(Child_FormClosed);
formInstance.Icon = (Icon)Icon.Clone();
formInstance.StartPosition = FormStartPosition.Manual;
//formInstance.WindowState = FormWindowState.Maximized;
formInstance.Dock = DockStyle.Fill;
formInstance.AutoScaleMode = AutoScaleMode.None;
formInstance.Show();
}
return(formInstance);
}
/// <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);
}
/// <summary>
/// 寫log to db (當logType=Error或資料庫連線失敗,會額外將錯誤訊息寫到檔案)
/// </summary>
/// <param name="msg">最後儲存的長度為100字元</param>
/// <param name="logType">基本logType可定義為 Info/Operation/Error</param>
/// <param name="operationType">
/// logType=Info,此參數才有效(I = change data, E = export, R = query, P = print, X = execute)
/// </param>
/// <param name="showMsg">true=顯示錯誤訊息,false=不顯示</param>
public void WriteLog(string msg, string logType = "Info", string operationType = " ", bool showMsg = false, bool isNeedWriteFile = false)
{
//bool isNeedWriteFile = false;
string dbErrorMsg = "";
//1.write log to db
//ken,先把WriteLog集中,之後可根據不同的logType,存放不同的TABLE或檔案
//基本logType可定義為 info/operation/error
//logf_job_type value: I = change data, E = export, R = query, P = print, X = execute
try {
switch (logType)
{
case ("Info"):
operationType = "A";
break;
case ("Error"):
operationType = "Z";
isNeedWriteFile = true;
break;
}
//LOGF_KEY_DATA長度要取前100字元,但是logf.LOGF_KEY_DATA型態為VARCHAR2 (100 Byte),如果有中文會算2byte...
string msgC = "[C#]" + msg;
new LOGF().Insert(GlobalInfo.USER_ID, _ProgramID, msgC.SubStr(0, 50), operationType);
} catch (Exception ex2) {
// write log to db failed , ready write file to local
isNeedWriteFile = true;
dbErrorMsg = ex2.ToString();
MessageDisplay.Error("資料庫連線發生錯誤,先將錯誤訊息寫到檔案");
}//try {
//2.write file to local
if (isNeedWriteFile)
{
try {
string filename = "log_" + DateTime.Now.ToString("yyyyMMdd") + ".log";
string filepath = Path.Combine(Application.StartupPath, "Log", DateTime.Today.ToString("yyyyMM"));
Directory.CreateDirectory(filepath);
filepath = Path.Combine(filepath, filename);
using (StreamWriter sw = File.AppendText(filepath)) {
sw.WriteLine("");
sw.WriteLine("");
sw.WriteLine("==============================");
sw.WriteLine("datetime=" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
sw.WriteLine("userId=" + GlobalInfo.USER_ID);
sw.WriteLine("txnId=" + _ProgramID);
sw.WriteLine("logType=" + logType);
sw.WriteLine("operationType=" + operationType);
sw.Write("msg=" + msg);
sw.WriteLine("");
if (dbErrorMsg != "")
{
sw.Write("dbErrorMsg=" + dbErrorMsg);
}
}//using (StreamWriter sw = File.AppendText(filepath)) {
} catch (Exception fileEx) {
MessageDisplay.Error("將log寫入檔案發生錯誤,請聯絡管理員" + Environment.NewLine + "msg=" + fileEx.Message);
return;
}
}//if (isNeedWriteFile) {
//3.show message to UI (ken,這裡主要處理一般的訊息,Error的錯誤訊息由另外傳入Exception參數的那個來發比較詳盡)
if (showMsg)
{
switch (logType)
{
case ("Operation"):
MessageDisplay.Normal(msg);
break;
case ("Info"):
MessageDisplay.Warning(msg);
break;
case ("Error"):
MessageDisplay.Error(msg);
break;
}
}//if (showMsg) {
}
protected virtual ResultStatus RunAfter(PokeBall args)
{
MessageDisplay.Normal("執行完畢");
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);
}
