/// <summary> /// 7/2上線前的版本 可供新需求的邏輯參考 /// </summary> /// <returns></returns> public string Wf40040() { Workbook workbook = new Workbook(); try { workbook.LoadDocument(_lsFile); Worksheet worksheet = workbook.Worksheets[(int)SheetName.SheetOne]; DateTime emdate = _emDateText.AsDateTime("yyyy/MM/dd"); worksheet.Cells["O1"].Value = "資料日期:" + emdate.ToLongDateString(); //前一交易日 DateTime dateLast = dao40040.GetDateLast(emdate, (int)SheetName.SheetOne); //讀取資料 DataTable dt = dao40040.ListData(emdate, $"{_oswGrpVal}%"); if (dt.Rows.Count <= 0) { return(MessageDisplay.MSG_NO_DATA); } //讀取資料(現貨資料) DataTable dtMg6 = dao40040.ListMg6Data(emdate, dateLast, "%"); //讀取Mg8資料 DataTable dtMg8 = dao40040.ListMg8Data(emdate, "%"); //保證金變動幅度達10%,分別為第n天 DataTable dtDay = dao40040.ListDayData(emdate); foreach (DataRow row in dt.Rows) { int rowIndex = row["RPT_SEQ_NO"].AsInt(); if (rowIndex <= 0) { continue; } #region 第B欄 worksheet.Cells[$"B{rowIndex}"].Value = row["MGT2_KIND_ID_OUT"].AsString(); #endregion if (row["MG1_CHANGE_FLAG"].AsString() != "Y") { continue; } //1.保證金變動幅度之趨勢/ //15%≧X≧10% decimal mg1ChangeRange = row["MG1_CHANGE_RANGE"].AsDecimal(); decimal Percent10 = row["MG1_PROD_SUBTYPE"].AsString() == "E" ? 0.05m : 0.1m; decimal Percent15 = row["MG1_PROD_SUBTYPE"].AsString() == "E" ? 0.1m : 0.15m; #region 第C欄 if (Math.Abs(mg1ChangeRange) >= Percent10 && Math.Abs(mg1ChangeRange) < Percent15) { worksheet.Cells[$"C{rowIndex}"].SetValue(mg1ChangeRange); } #endregion //X≧15% #region 第D欄 if (Math.Abs(mg1ChangeRange) >= Percent15) { worksheet.Cells[$"D{rowIndex}"].SetValue(mg1ChangeRange); } #endregion //前一交易日保證金變動幅度 var ldValue = row["MG1_CHANGE_RANGE_LAST"]; #region 第E欄 worksheet.Cells[$"E{rowIndex}"].SetValue(ldValue == DBNull.Value ? "▲" : ldValue); #endregion #region 第F欄 worksheet.Cells[$"F{rowIndex}"].SetValue(ldValue == DBNull.Value ? "▲" : OX(row)); #endregion //達得調整標準天數 string kindID = row["MG1_KIND_ID"].AsString(); #region 第G欄 int dtDayIndex = dtDay.Rows.IndexOf(dtDay.Select($@"mg1_kind_id ='{kindID}'").FirstOrDefault()); worksheet.Cells[$"G{rowIndex}"].SetValue(dtDayIndex > -1 ? (long)dtDay.Rows[dtDayIndex]["DAY_CNT"].AsDecimal() : 1); #endregion //2.未沖銷部位數/ var ai2OI = row["AI2_OI"]; if (ai2OI != DBNull.Value) { #region 第H欄 worksheet.Cells[$"H{rowIndex}"].SetValue(ai2OI); #endregion #region 第I欄 var oiRate = row["OI_RATE"]; worksheet.Cells[$"I{rowIndex}"].SetValue(oiRate.AsDecimal() < 0.0001m && ai2OI.AsDecimal() > 0 ? "小於0.01%" : oiRate); #endregion #region 第J欄 decimal TotOIiRound = Math.Round(dt.Rows[0]["TOT_OI"].AsDecimal() * 0.005m, 0, MidpointRounding.AwayFromZero); worksheet.Cells[$"J{rowIndex}"].SetValue(ai2OI.AsDecimal() >= TotOIiRound ? "O" : "X"); #endregion //屆到期日前7個交易日 #region 第K欄 worksheet.Cells[$"K{rowIndex}"].SetValue( row["APROD_7DATE"].AsDateTime() <= emdate && row["APROD_DELIVERY_DATE"].AsDateTime() > emdate ? "O" : "X" ); #endregion } //3.現貨、期貨漲跌/ int dtMg6Index = dtMg6.Rows.IndexOf(dtMg6.Select($@"F_KIND_ID ='{kindID}' or O_KIND_ID='{kindID}'").FirstOrDefault()); if (dtMg6Index > -1) { string colTxt = dtMg6.Rows[dtMg6Index]["O_KIND_ID"] == DBNull.Value ? "PDK" : "O"; string prodSubtype = dtMg6.Rows[dtMg6Index]["APDK_PROD_SUBTYPE"].AsString(); //現貨 #region 第L欄 var updown = dtMg6.Rows[dtMg6Index][colTxt + "_UP_DOWN"]; decimal rateMUL100 = dtMg6.Rows[dtMg6Index][colTxt + "_RETURN_RATE"].AsDecimal() * 100; string flag = FlagStr(updown.AsDecimal()); worksheet.Cells[$"L{rowIndex}"].SetValue(updown == DBNull.Value ? "-" : WriteUpDownPercent(updown.AsDecimal(), rateMUL100, prodSubtype, flag)); #endregion //現貨漲跌與保證金調整方向相同 #region 第N欄 switch (kindID) { case "GDF": case "TGF": case "TGO": case "GBF": case "CPF": flag = "-"; break; default: break; } if (flag == "-") { //ls_flag = '-' then 後面沒有要做什麼 只是做個條件區分 } else if (row["MG1_CM_LAST"] == DBNull.Value) { flag = ""; } else { flag = WriteFlag(flag, row); } worksheet.Cells[$"N{rowIndex}"].SetValue(flag); #endregion //期貨 #region 第M欄 var fupdown = dtMg6.Rows[dtMg6Index]["F_UP_DOWN"]; string flag2 = FlagStr(fupdown.AsDecimal()); decimal frateMUL100 = dtMg6.Rows[dtMg6Index]["F_RETURN_RATE"].AsDecimal() * 100; worksheet.Cells[$"M{rowIndex}"].SetValue(WriteUpDownPercent(fupdown.AsDecimal(), frateMUL100, prodSubtype, flag2)); #endregion //期貨漲跌與保證金調整方向相同 #region 第O欄 var cmlast = row["MG1_CM_LAST"]; worksheet.Cells[$"O{rowIndex}"].SetValue(cmlast == DBNull.Value ? "" : WriteFlag(flag2, row)); #endregion }// if (dtMg6Index > -1) //4.與國外水準相較/ int dtMg8Index = dtMg8.Rows.IndexOf(dtMg8.Select($"mg1_kind_id ='{kindID}' and com ='TAIFEX'").FirstOrDefault()); if (dtMg8Index > -1) { #region 第P欄 worksheet.Cells[$"P{rowIndex}"].SetValue(Foreign(kindID, "BEF", dtMg8, dtMg8Index)); #endregion #region 第Q欄 worksheet.Cells[$"Q{rowIndex}"].SetValue(Foreign(kindID, "AFT", dtMg8, dtMg8Index)); #endregion } }//foreach (DataRow row in dt.Rows) //重大事件 StringBuilder sb = new StringBuilder(""); DataTable dtMgt3 = dao40040.ListMgt3Data(emdate); int mgt3Count = dtMgt3.Rows.Count; for (int k = 0; k < mgt3Count; k++) { DataRow dr = dtMgt3.Rows[k]; string memo = PbFunc.f_conv_date(dr["MGT3_DATE_TO"].AsDateTime(), 3) + dr["MGT3_MEMO"].AsString(); sb.Append(memo); sb.Append(FlagMerge(mgt3Count, k)); } if (sb.ToString() != "") { int mg1flagYcount = dt.Select("mg1_change_flag = 'Y'").Length; if (mg1flagYcount > 0) { worksheet.Cells[$"R16"].SetValue(sb.ToString()); } } //save worksheet.ScrollTo(0, 0); } catch (Exception ex) { #if DEBUG throw new Exception($"Wf40040:" + ex.Message); #else throw ex; #endif } finally { workbook.SaveDocument(_lsFile); } return(MessageDisplay.MSG_OK); }
protected override ResultStatus Export() { try { //1.1 ready panFilter.Enabled = false; labMsg.Visible = true; labMsg.Text = "訊息:資料轉出中........"; this.Refresh(); //1.2 get data DataTable dt = new D40120().ListData(txtStartDate.DateTimeValue); if (dt.Rows.Count <= 0) { MessageDisplay.Info(string.Format("{0},{1}文字說明,讀取「案由一契約名稱」無任何資料!", txtStartDate.Text, _ProgramID), GlobalInfo.ResultText); return(ResultStatus.Fail); } //1.3 表頭 string source = "本公司保證金調整影響分析" + Environment.NewLine; source += "一、調整之合理性:" + Environment.NewLine; //1.4一、調整之合理性 mgt2_prod_type='F' string resFuture = ""; string tempFuture = ""; string ls_txt_end = ""; int pos = 0; DataTable dtFuture = dt.Filter("mgt2_prod_type = 'F'"); if (dtFuture.Rows.Count > 0) { resFuture = "依結算保證金訂定公式計算,"; //1.4.1抓TX轉字串 foreach (DataRow dr in dtFuture.Rows) { pos++; string abbrName = dr["mgt2_abbr_name"].AsString(); string kindIdOut = dr["mgt2_kind_id_out"].AsString(); Decimal changeRange = Math.Round(dr["mgd2_adj_rate"].AsDecimal() * 100, 1, MidpointRounding.AwayFromZero); string kindId = dr["mgd2_kind_id"].AsString(); resFuture += abbrName; tempFuture += kindIdOut + ":" + changeRange.ToString() + "%"; //台指期貨(taiwanFuture) if (kindIdOut == taiwanFuture) { //dtMgt2 return mgt2_group_kind_id/mgt2_prod_type/mgt2_kind_id/mgt2_kind_id_out/mgt2_abbr_name/mgt2_name DataTable dtMgt2 = new D40120().ListMgt2ByKindId(kindIdOut); DataTable dttempFuture = dtMgt2.Filter(string.Format("mgt2_prod_type='F' and mgt2_kind_id <> '{0}'", kindId)); foreach (DataRow drtempFuture in dttempFuture.Rows) { string abbrName2 = drtempFuture["mgt2_abbr_name"].AsString(); if (pos == dtFuture.Rows.Count) { resFuture += "及"; } else { resFuture += "、"; } resFuture += abbrName2; } //foreach (DataRow drtempFuture in dttempFuture.Rows) { } //if (kindIdOut == taiwanFuture) { if (pos == dtFuture.Rows.Count - 1) { string lastRowKindIdOut = dtFuture.Rows[pos]["mgt2_kind_id_out"].AsString(); if (lastRowKindIdOut == taiwanFuture) { resFuture += "、"; tempFuture += "、"; } else { resFuture += "及"; tempFuture += "及"; } } else if (pos == dtFuture.Rows.Count) { // } else { resFuture += "、"; tempFuture += "、"; } //if (pos == dtFuture.Rows.Count - 1) { } //foreach(DataRow dr in dtFuture.Rows) { resFuture = resFuture + ','; #region //1.4.2分日期轉字串 DateTime ldt_date = dtFuture.Rows[0]["mgd2_ymd"].AsDateTime(); DataTable dtDate = dtFuture.Filter(string.Format("mgt2_prod_type='F' and mgd2_ymd =#{0}#", ldt_date.ToString("yyyy/MM/dd"))); //mm/dd/yyyy while (ldt_date != DateTime.MinValue) { DateTime lastMg2Date = dtFuture.Rows[dtFuture.Rows.Count - 1]["mgd2_ymd"].AsDateTime(); tempFuture = PbFunc.f_conv_date(lastMg2Date, 3) + "前揭商品之結算保證金變動幅度"; if (dtFuture.Rows.Count >= 2) { tempFuture += "分別為 "; } else { tempFuture += "為 "; } for (int k = 0; k < dtFuture.Rows.Count; k++) { string kindIdOut = dtFuture.Rows[k]["mgt2_kind_id_out"].AsString(); Decimal changeRange = Math.Round(dtFuture.Rows[k]["mgd2_adj_rate"].AsDecimal() * 100, 1, MidpointRounding.AwayFromZero); tempFuture += kindIdOut + ":" + changeRange + "%"; if (k == dtFuture.Rows.Count - 2) { tempFuture += "及"; } else if (k == dtFuture.Rows.Count - 1) { // } else { tempFuture += "、"; } } resFuture += tempFuture + ","; DataTable dtDate2 = dtFuture.Filter(string.Format("mgt2_prod_type='F' and mgd2_ymd > #{0}#", ldt_date.ToString("yyyy/MM/dd")));//mm/dd/yyyy if (dtDate2.Rows.Count > 0) { ldt_date = dtDate2.Rows[0]["mgd2_ymd"].AsDateTime(); } else { break;// ldt_date = DateTime.MinValue; } }//while (ldt_date != DateTime.MinValue) { #endregion Decimal mg1_change_cond = Math.Round(dtFuture.Rows[dtFuture.Rows.Count - 1]["mg1_change_cond"].AsDecimal() * 100, 0, MidpointRounding.AwayFromZero); ls_txt_end = string.Format("其變動幅分達{0}%以上且進位後金額改變,依本公司結算保證金收取方式及標準規定,已達得調整保證金之標準。", mg1_change_cond.ToString()); }//if (dtFuture.Rows.Count > 0) { //1.5一、調整之合理性 mgt2_prod_type='O' DataTable dtOption = dt.Filter("mgt2_prod_type = 'O'"); string resOption = ""; string tempFutureOption = ""; if (dtOption.Rows.Count > 0) { pos = 0; foreach (DataRow dr in dtOption.Rows) { pos++; string abbrName = dr["mgt2_abbr_name"].AsString(); string kindIdOut = dr["mgt2_kind_id_out"].AsString(); Decimal changeRange = Math.Round(dr["mgd2_adj_rate"].AsDecimal() * 100, 1, MidpointRounding.AwayFromZero); resOption += abbrName; tempFutureOption += kindIdOut + ":" + changeRange.ToString() + "%"; if (pos == dtOption.Rows.Count - 1) { resOption += "及"; tempFutureOption += "及"; } else if (pos == dtOption.Rows.Count) { // } else { resOption += "、"; tempFutureOption += "、"; } //if (pos == dtOption.Rows.Count - 1) { } //foreach(DataRow dr in dtOption.Rows) { resOption += "風險保證金(A值)之變動幅度"; if (dtOption.Rows.Count >= 2) { resOption += "分別為 "; } else { resOption += "為 "; } resOption += tempFutureOption + ","; Decimal mg1_change_cond = Math.Round(dtOption.Rows[dtOption.Rows.Count - 1]["mg1_change_cond"].AsDecimal() * 100, 0, MidpointRounding.AwayFromZero); ls_txt_end = string.Format("其變動幅分達{0}%以上且進位後金額改變,依本公司結算保證金收取方式及標準規定,已達得調整保證金之標準。", mg1_change_cond.ToString()); }//if (dtOption.Rows.Count > 0) { //1.6 save string to txt source += resFuture + resOption + ls_txt_end + Environment.NewLine;//ken,有四個部分組合而成 string fileName = _ProgramID + "_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss") + ".txt"; string filePath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH, fileName); bool IsSuccess = ToText(source, filePath, System.Text.Encoding.GetEncoding(950)); if (!IsSuccess) { MessageDisplay.Error("文字檔「" + filePath + "」Open檔案錯誤!", GlobalInfo.ErrorText); return(ResultStatus.Fail); } if (FlagAdmin) { System.Diagnostics.Process.Start(filePath); } return(ResultStatus.Success); } catch (Exception ex) { WriteLog(ex); } finally { panFilter.Enabled = true; labMsg.Text = ""; labMsg.Visible = false; } return(ResultStatus.Fail); }
protected override ResultStatus Export() { try { //1.1 ready panFilter.Enabled = false; labMsg.Visible = true; labMsg.Text = "訊息:資料轉出中........"; this.Refresh(); //1.2 get data DataTable dt = new D40122().ListData(txtStartDate.DateTimeValue); if (dt.Rows.Count <= 0) { MessageDisplay.Info(string.Format("{0},{1},讀取「SPAN參數調整資料」無任何資料!", txtStartDate.Text, this.Text), GlobalInfo.ResultText); return(ResultStatus.Fail); } string source = ""; //1.3 sp2_type='SV' string resSV = ""; DataTable dtSV = dt.Filter("sp2_type='SV'"); if (dtSV.Rows.Count > 0) { resSV = "依"; for (int k = 0; k < dtSV.Rows.Count; k++) { resSV += dtSV.Rows[k]["spt1_abbr_name"].AsString() + ConnString(k, dtSV.Rows.Count); }//for (int k = 0;k < dtSV.Rows.Count;k++) { DateTime lastSp2Date = dtSV.Rows[dtSV.Rows.Count - 1]["sp2_date"].AsDateTime(); resSV += string.Format("波動度偵測全距訂定公式計算,{0}前揭商品之波動度偵測全距變動幅度分別為", PbFunc.f_conv_date(lastSp2Date, 3)); for (int k = 0; k < dtSV.Rows.Count; k++) { string kindIdOut = dtSV.Rows[k]["spt1_kind_id1_out"].AsString(); Decimal changeRange = Math.Round(dtSV.Rows[k]["sp1_change_range"].AsDecimal() * 100, 1, MidpointRounding.AwayFromZero); resSV += kindIdOut + ":" + changeRange + "%" + ConnString(k, dtSV.Rows.Count); }//for (int k = 0;k < dtSV.Rows.Count;k++) { string temp = new D40122().GetRate("SV"); decimal rate = Math.Round(temp.AsDecimal(0) * 100, 0, MidpointRounding.AwayFromZero); resSV += string.Format(",其變動幅度達{0}%以上,依本公司「結算保證金收取方式及標準」規定,已達得調整前揭契約波動度偵測全距之標準。", rate.ToString()) + Environment.NewLine; }//if (dtSV.Rows.Count > 0) { //1.4 sp2_type='SD' string resSD = ""; DataTable dtSD = dt.Filter("sp2_type='SD'"); if (dtSD.Rows.Count > 0) { resSD = "依兩商品組合間契約價值耗用比率訂定公式計算,"; for (int k = 0; k < dtSD.Rows.Count; k++) { resSD += dtSD.Rows[k]["spt1_com_id"].AsString() + ConnString(k, dtSD.Rows.Count); }//for (int k = 0;k < dtSD.Rows.Count;k++) { DateTime lastSp2Date = dtSD.Rows[dtSD.Rows.Count - 1]["sp2_date"].AsDateTime(); resSD += string.Format(",{0}前揭兩兩商品間契約價值耗用比率變動幅度分別為", PbFunc.f_conv_date(lastSp2Date, 3)); for (int k = 0; k < dtSD.Rows.Count; k++) { Decimal changeRange = Math.Round(dtSD.Rows[k]["sp1_change_range"].AsDecimal() * 100, 1, MidpointRounding.AwayFromZero); resSD += changeRange + "%" + ConnString(k, dtSD.Rows.Count); }//for (int k = 0;k < dtSD.Rows.Count;k++) { string temp = new D40122().GetRate("SD"); decimal rate = Math.Round(temp.AsDecimal(0) * 100, 0, MidpointRounding.AwayFromZero); resSD += string.Format(",其變動幅度達{0}%以上,依本公司「結算保證金收取方式及標準」規定,已達得調整前揭兩商品組合間契約價值耗用比率之標準。", rate.ToString()) + Environment.NewLine; }//if (dtSD.Rows.Count > 0) { //1.5 sp2_type='SS' string resSS = ""; DataTable dtSS = dt.Filter("sp2_type='SS'"); if (dtSS.Rows.Count > 0) { resSS = "依兩商品組合間跨商品價差折抵率訂定公式計算,"; for (int k = 0; k < dtSS.Rows.Count; k++) { resSS += dtSS.Rows[k]["spt1_com_id"].AsString() + ConnString(k, dtSS.Rows.Count); }//for (int k = 0;k < dtSS.Rows.Count;k++) { DateTime lastSp2Date = dtSS.Rows[dtSS.Rows.Count - 1]["sp2_date"].AsDateTime(); resSS += string.Format(",{0}前揭兩商品組合間跨商品價差折抵率變動幅度分別為", PbFunc.f_conv_date(lastSp2Date, 3)); for (int k = 0; k < dtSS.Rows.Count; k++) { Decimal changeRange = Math.Round(dtSS.Rows[k]["sp1_change_range"].AsDecimal() * 100, 1, MidpointRounding.AwayFromZero); resSS += changeRange + "%" + ConnString(k, dtSS.Rows.Count); }//for (int k = 0;k < dtSS.Rows.Count;k++) { string temp = new D40122().GetRate("SS"); decimal rate = Math.Round(temp.AsDecimal(0) * 100, 0, MidpointRounding.AwayFromZero); resSS += string.Format(",其變動幅度達{0}%以上,依本公司「結算保證金收取方式及標準」規定,已達得調整前揭兩商品組合間跨商品價差折抵率之標準。", rate.ToString()) + Environment.NewLine; }//if (dtSS.Rows.Count > 0) { //1.6 save string to txt source += resSV + resSD + resSS;//ken,有3個部分組合而成 string fileName = _ProgramID + "_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss") + ".txt"; string filePath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH, fileName); bool IsSuccess = ToText(source, filePath, System.Text.Encoding.GetEncoding(950)); if (!IsSuccess) { MessageDisplay.Error("文字檔「" + filePath + "」Open檔案錯誤!", GlobalInfo.ErrorText); return(ResultStatus.Fail); } //if (FlagAdmin) // System.Diagnostics.Process.Start(filePath); return(ResultStatus.Success); } catch (Exception ex) { WriteLog(ex); } finally { panFilter.Enabled = true; labMsg.Text = ""; labMsg.Visible = false; } return(ResultStatus.Fail); }
private void wf_Export(Workbook workbook, SheetNo sheetNo, DataTable dt, string rptId, string rptName) { try { //切換Sheet Worksheet worksheet = workbook.Worksheets[(int)sheetNo]; if (dwParamKey.EditValue.AsString() != "%") { worksheet.Cells[1, 0].Value = "商品:" + dwParamKey.Text; } if (txtAftStartYM.Text == txtAftEndYM.Text) { worksheet.Cells[2, 2].Value = PbFunc.f_conv_date(txtAftStartYM.DateTimeValue, 6); } else { worksheet.Cells[2, 2].Value = PbFunc.f_conv_date(txtAftStartYM.DateTimeValue, 6) + " ~ " + PbFunc.f_conv_date(txtAftEndYM.DateTimeValue, 6); } int tmp = ((int)sheetNo == 0 ? 5 : 4); if (txtPrevStartYM.Text == txtPrevEndYM.Text) { worksheet.Cells[2, tmp].Value = PbFunc.f_conv_date(txtPrevStartYM.DateTimeValue, 6); } else { worksheet.Cells[2, tmp].Value = PbFunc.f_conv_date(txtPrevStartYM.DateTimeValue, 6) + " ~ " + PbFunc.f_conv_date(txtPrevEndYM.DateTimeValue, 6); } //只有成交量(Sheet1)需要執行這段 if (sheetNo == 0) { if (gbMarket.EditValue.ToString() == "rb_market_0") { worksheet.Cells[1, 0].Value = "一般交易時段"; } else if (gbMarket.EditValue.ToString() == "rb_market_1") { worksheet.Cells[1, 0].Value = "盤後交易時段"; } for (int i = 0; i < dt.Rows.Count; i++) { int ii_ole_row = dt.Rows[i]["RPT_SEQ_NO"].AsInt(); worksheet.Cells[ii_ole_row - 1, 2].Value = dt.Rows[i]["AM21_M_QNTY_AFT"].AsDecimal(); if (dt.Rows[i]["TRADE_DAYS_AFT"].AsDecimal() > 0) { worksheet.Cells[ii_ole_row - 1, 4].Value = dt.Rows[i]["AM21_M_QNTY_AFT"].AsDecimal() / dt.Rows[i]["TRADE_DAYS_AFT"].AsDecimal(); } worksheet.Cells[ii_ole_row - 1, 5].Value = dt.Rows[i]["AM21_M_QNTY_PREV"].AsDecimal(); if (dt.Rows[i]["TRADE_DAYS_PREV"].AsDecimal() > 0) { worksheet.Cells[ii_ole_row - 1, 7].Value = dt.Rows[i]["AM21_M_QNTY_PREV"].AsDecimal() / dt.Rows[i]["TRADE_DAYS_PREV"].AsDecimal(); } } } else { for (int i = 0; i < dt.Rows.Count; i++) { int ii_ole_row = dt.Rows[i]["RPT_SEQ_NO"].AsInt(); if (dt.Rows[i]["TRADE_DAYS_AFT"].AsDecimal() > 0) { worksheet.Cells[ii_ole_row - 1, 2].Value = dt.Rows[i]["AM21_OI_QNTY_AFT"].AsDecimal() / dt.Rows[i]["TRADE_DAYS_AFT"].AsDecimal(); } if (dt.Rows[i]["TRADE_DAYS_PREV"].AsDecimal() > 0) { worksheet.Cells[ii_ole_row - 1, 4].Value = dt.Rows[i]["AM21_OI_QNTY_PREV"].AsDecimal() / dt.Rows[i]["TRADE_DAYS_PREV"].AsDecimal(); } } } worksheet.Range["A1"].Select(); worksheet.ScrollToRow(0); } catch (Exception ex) { WriteLog(ex); } return; }
/// <summary> /// 寫資料到文字檔 /// </summary> /// <returns></returns> protected bool wf_40140_1() { try { //1.1.ready panFilter.Enabled = false; labMsg.Visible = true; labMsg.Text = "40141-文字說明 轉檔中..."; this.Cursor = Cursors.WaitCursor; this.Refresh(); Thread.Sleep(5); //1.2 get txt data string txt = ""; int pos = 0; string strIm = ""; //dtGold 黃金期貨 DataTable dtGold = new D40140().GetGoldData(txtStartDate.DateTimeValue); //d_40140_1_mg1 if (dtGold.Rows.Count <= 0) { MessageDisplay.Info(string.Format("{0},無任何「調整黃金期貨契約」資料!", txtStartDate.Text), GlobalInfo.ResultText); return(false); } int dataGoldNum = dtGold.Rows.Count; //dt 國外保證金 DataTable dt = new D40140().ListMoneyData(txtStartDate.DateTimeValue); if (dt.Rows.Count <= 0) { MessageDisplay.Info(string.Format("{0},讀取「國外保證金資料」無任何資料!", txtStartDate.Text), GlobalInfo.ResultText); return(false); } foreach (DataRow dr in dt.Rows) { pos++; string com = dr["com"].AsString(); decimal outIm = dr["out_im"].AsDecimal(); decimal exchangeRate = dr["exchange_rate"].AsDecimal(); decimal mg1Im = dr["mg1_im"].AsDecimal(); dr.BeginEdit(); if (com == "TOC01" && exchangeRate > 0) { dr["out_im"] = Math.Round((mg1Im / exchangeRate * 3.11m), 0, MidpointRounding.AwayFromZero); } else { dr["out_im"] = mg1Im; } //if (com == "TOC01" && exchangeRate > 0) dr.BeginEdit(); } //foreach (DataRow dr in dtGold.Rows) dt.AcceptChanges(); //這樣才會更新修改的dt資料 txt += "二、保證金調整之合理性" + Environment.NewLine + "依結算保證金訂定公式計算," + PbFunc.f_conv_date(txtStartDate.DateTimeValue, 3); pos = 0; foreach (DataRow drGold in dtGold.Rows) { pos++; string abbrName = drGold["mgt2_abbr_name"].AsString(); decimal mgd2ChangeRate = drGold["mgd2_adj_rate"].AsDecimal(); if (pos > 1) { txt += ";"; } txt += abbrName + "結算保證金之變動幅度為" + Math.Round(mgd2ChangeRate * 100, 1, MidpointRounding.AwayFromZero).AsString() + "%"; }//foreach (DataRow dr in dtGold.Rows) txt += ",其變動幅度達百分之十以上且進位後金額改變,依上開規定,經保證金調整審議會議決議後調整保證金。" + Environment.NewLine; //1.3 GDF if (dtGold.Rows.Count > 0) { DataTable dtGDF = dtGold.Filter("mgd2_kind_id='GDF'"); strIm = dtGold.Rows[0]["mgd2_im"].AsDecimal().AsString(); if (dtGDF.Rows.Count > 0) { decimal mgd2Im = dtGDF.Rows[0]["mgd2_im"].AsDecimal(); decimal mgd2CurCm = dtGDF.Rows[0]["mgd2_cur_cm"].AsDecimal(); decimal mgd2Cm = dtGDF.Rows[0]["mgd2_cm"].AsDecimal(); string mgt2AbbrName = dtGDF.Rows[0]["mgt2_abbr_name"].AsString(); txt += "本次調整將美元計價" + mgt2AbbrName + "結算保證金金額由原先" + string.Format("{0:N0}", Math.Round(mgd2CurCm, 0, MidpointRounding.AwayFromZero)) + "美元向"; if (mgd2CurCm > mgd2Cm) { txt += "下"; } else { txt += "上"; } //if (mg1CurCm > mg1Cm) txt += "調整為" + string.Format("{0:N0}", mgd2Cm) + "美元,比例約為"; if (mgd2CurCm > 0) { txt += (Math.Round((mgd2Cm - mgd2CurCm) / mgd2CurCm * 100, 1, MidpointRounding.AwayFromZero)).AsString() + "%"; } //if (mg1CurCm > 0) } //if (dtGold.Rows.Count > 0) } //1.4 TGF if (dtGold.Rows.Count > 0) { DataTable dtTGF = dtGold.Filter("mgd2_kind_id='TGF'"); if (dtTGF.Rows.Count > 0) { decimal mgd2CurCm = dtTGF.Rows[0]["mgd2_cur_cm"].AsDecimal(); decimal mgd2Cm = dtTGF.Rows[0]["mgd2_cm"].AsDecimal(); string mgt2AbbrName = dtTGF.Rows[0]["mgt2_abbr_name"].AsString(); if (dataGoldNum > 0) { txt += ";"; } txt += "新臺幣計價" + mgt2AbbrName + "結算保證金金額由原先" + string.Format("{0:N0}", Math.Round(mgd2CurCm, 0, MidpointRounding.AwayFromZero)) + "元向"; if (mgd2CurCm > mgd2Cm) { txt += "下"; } else { txt += "上"; } //if (mg1CurCm > mg1Cm) txt += "調整為" + string.Format("{0:N0}", mgd2Cm) + "元,比例約為"; if (mgd2CurCm > 0) { txt += (Math.Round((mgd2Cm - mgd2CurCm) / mgd2CurCm * 100, 1, MidpointRounding.AwayFromZero)).AsString() + "%"; } //if (mg1CurCm > 0) } //if (dtGold.Rows.Count > 0) txt += "。茲將調整後本公司黃金期貨契約結算保證金金額,相較於世界主要交易所黃金期貨保證金金額分析如下:(詳如后附表)" + Environment.NewLine; } //1.5 if (dt.Rows.Count > 0) { txt += "1.本公司黃金期貨契約保證金"; DataTable dtSmallstrIm = dt.Filter("out_im <" + strIm + " and data_type='2'"); if (dtSmallstrIm.Rows.Count > 0) { txt += "較"; pos = 0; foreach (DataRow dr in dtSmallstrIm.Rows) { pos++; string fName = dr["f_name"].AsString(); txt += fName; if (pos == dtSmallstrIm.Rows.Count - 1) { txt += "及"; } else if (pos == dtSmallstrIm.Rows.Count) { // } else { txt += "、"; } //if (pos == dt.Rows.Count - 1) } //foreach (DataRow dr in dt.Rows) txt += "略高,"; }//if (dt.Rows.Count > 0) DataTable dtBigstrIm = dt.Filter("out_im >" + strIm + " and data_type='2'"); if (dtBigstrIm.Rows.Count > 0) { txt += "較"; pos = 0; foreach (DataRow dr in dtBigstrIm.Rows) { pos++; string fName = dr["f_name"].AsString(); txt += fName; if (pos == dtBigstrIm.Rows.Count - 1) { txt += "及"; } else if (pos == dtBigstrIm.Rows.Count) { // } else { txt += "、"; } //if (pos == dt.Rows.Count - 1) } //foreach (DataRow dr in dt.Rows) txt += "略低,"; }//if (dt.Rows.Count > 0) txt += "應和各交易所風控機制與交易時間等因素相關" + Environment.NewLine; DataTable dtNYM01 = dt.Filter("com='NYM01'"); if (dtNYM01.Rows.Count > 0) { string fName = dtNYM01.Rows[0]["f_name"].AsString(); Decimal mg1Cm = dtNYM01.Rows[0]["mg1_cm"].AsDecimal(); Decimal mg1Im = dtNYM01.Rows[0]["mg1_im"].AsDecimal(); txt += "經查目前" + fName + "黃金期貨契約(契約規格100盎司)結算保證金為" + string.Format("{0:N0}", Math.Round(mg1Cm, 0, MidpointRounding.AwayFromZero)) + "美元,原始保證金為" + string.Format("{0:N0}", Math.Round(mg1Im, 0, MidpointRounding.AwayFromZero)) + "美元;"; } //if (dt.Rows.Count > 0) DataTable dtCME01 = dt.Filter("com='CME01'"); if (dtCME01.Rows.Count > 0) { string fName = dtCME01.Rows[0]["f_name"].AsString(); Decimal mg1Cm = dtCME01.Rows[0]["mg1_cm"].AsDecimal(); Decimal mg1Im = dtCME01.Rows[0]["mg1_im"].AsDecimal(); txt += "而" + fName + "黃金期貨契約(契約規格100盎司)結算保證金為" + string.Format("{0:N0}", Math.Round(mg1Cm, 0, MidpointRounding.AwayFromZero)) + "美元,原始保證金為" + string.Format("{0:N0}", Math.Round(mg1Im, 0, MidpointRounding.AwayFromZero)) + "美元;"; } //if (dt.Rows.Count > 0) DataTable dtTOC01 = dt.Filter("com='TOC01'"); if (dtTOC01.Rows.Count > 0) { string fName = dtTOC01.Rows[0]["f_name"].AsString(); Decimal exchangeRate = dtTOC01.Rows[0]["exchange_rate"].AsDecimal(); Decimal mg1Im = dtTOC01.Rows[0]["mg1_im"].AsDecimal(); txt += "另" + fName + "所收取之原始保證金金額,倘調整契約規格並依匯率(原契約規格1公斤並以" + string.Format("{0:N0}", Math.Round(exchangeRate, 0, MidpointRounding.AwayFromZero)) + "日圓對1美元計算)及重量單位(100盎司=3.11公斤)換算後,約為"; if (exchangeRate > 0) { txt += string.Format("{0:N0}", Math.Round((mg1Im / exchangeRate * 3.11m), 0, MidpointRounding.AwayFromZero)).AsString(); }//if (exchangeRate > 0) txt += "美元。"; } //if (dt.Rows.Count > 0) txt += "有關各交易所保證金占契約總值比率詳附表。" + Environment.NewLine; } //2.save string to txt string fileName = "40141_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss") + ".txt"; string filePath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH, fileName); bool IsSuccess = ToText(txt, filePath, System.Text.Encoding.GetEncoding(950)); if (!IsSuccess) { MessageDisplay.Error("文字檔「" + filePath + "」Open檔案錯誤!", GlobalInfo.ErrorText); return(false); } if (FlagAdmin) { System.Diagnostics.Process.Start(filePath); } return(true); } catch (Exception ex) { WriteLog(ex); return(false); } }