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); }