/// <summary> /// 改變生效日期時,同步公布日期 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void txtSDate_EditValueChanged(object sender, EventArgs e) { if (txtSDate.Text.Length < 10) { return; //防止還沒輸入完就觸發事件 } string mocfYmd; ymd = txtSDate.DateTimeValue.ToString("yyyyMMdd"); //交易日+1個月 mocfYmd = PbFunc.relativedate(txtSDate.DateTimeValue, -30).ToString("yyyyMMdd"); /*前一營業日*/ preYmd = daoMOCF.GetPrevTradeDay(ymd, mocfYmd); //同步公布日期 gvMain.CloseEditor(); gvMain.UpdateCurrentRow(); DataTable dtGrid = (DataTable)gcMain.DataSource; foreach (DataRow dr in dtGrid.Rows) { if (dr["ADJ_CODE"].AsString() == "Y") { dr["PUB_YMD"] = preYmd; } if (dr["ADJ_CODE"].AsString() == "D") { dr["PUB_YMD"] = ymd; } } }
protected override ResultStatus Open() { try { base.Open(); DateTime date = DateTime.Now; //本次 date = PbFunc.relativedate(date, (date.Day * -1)); txtCurEymd.DateTimeValue = date; txtDate.DateTimeValue = date; txtCurEMonth.DateTimeValue = date; date = PbFunc.relativedate(date, (date.Day * -1)); date = PbFunc.relativedate(date, (date.Day * -1)); txtCurSMonth.DateTimeValue = date; //前次 date = PbFunc.relativedate(date, (date.Day * -1)); txtPrevEymd.DateTimeValue = date; txtEMonth.DateTimeValue = date; date = PbFunc.relativedate(date, (date.Day * -1)); date = PbFunc.relativedate(date, (date.Day * -1)); txtSMonth.DateTimeValue = date; #if DEBUG txtDate.Text = "2019/03/31"; txtSMonth.Text = "2018/10"; txtEMonth.Text = "2018/12"; txtCurSMonth.Text = "2019/01"; txtCurEMonth.Text = "2019/03"; #endif txtDate.Focus(); } catch (Exception ex) { throw ex; } return(ResultStatus.Success); }
protected override ResultStatus Open() { try { base.Open(); DateTime date = DateTime.Now; txtDate.DateTimeValue = date; txtStkoutYmd.DateTimeValue = date; date = PbFunc.relativedate(date, (date.Day * -1)); txtPrevEymd.DateTimeValue = date; txtEMonth.DateTimeValue = date; date = PbFunc.relativedate(date, (date.Day * -1)); date = PbFunc.relativedate(date, (date.Day * -1)); txtSMonth.DateTimeValue = date; cbxDB.Checked = true; #if DEBUG txtDate.Text = "2018/03/31"; txtSMonth.Text = "2018/03"; txtEMonth.Text = "2018/03"; txtStkoutYmd.Text = "2018/03/31"; #endif txtDate.Focus(); } catch (Exception ex) { throw ex; } return(ResultStatus.Success); }
/// <summary> /// 期貨市場動態報導-開戶數 /// </summary> /// <param name="ws30014"></param> private void wf_30015(Worksheet ws30014) { string rptName, rptId, date; int rowIndex; decimal value; DateTime ldtDate, ldDateFm; rptName = "期貨市場動態報導-開戶數"; rptId = "30015"; ShowMsg(rptId + "-" + rptName + " 轉檔中..."); ldtDate = txtSDate.DateTimeValue; ldDateFm = PbFunc.relativedate(txtSDate.DateTimeValue, -365); ldtDate = dao30010.checkPreviousData(ldtDate, ldDateFm); //讀取資料 DataTable dt30015 = dao30010.d_30015(ldtDate); if (dt30015.Rows.Count == 0) { MessageDisplay.Info(ldtDate.ToString("yyyy/MM/dd") + "(前營業日)開戶資料未轉入(功能28610),或請轉入後重新執行此功能!"); labMsg.Visible = false; return; } flag++; //填資料 /* 只會有1筆 */ DataRow dr = dt30015.Rows[0]; rowIndex = dao30010.get30015Row() - 1; ws30014.Cells[rowIndex, 2].SetValue(dr["AB3_COUNT"]); ws30014.Cells[rowIndex, 4].SetValue(dr["AB3_INCREASE"]); ws30014.Cells[rowIndex, 6].SetValue(dr["AB3_COUNT1"]); ws30014.Cells[rowIndex, 8].SetValue(dr["AB3_COUNT2"]); ws30014.Cells[rowIndex, 9].Value = dr["AB3_DATE"].AsDateTime().ToString("MM月dd日"); ws30014.Cells[rowIndex, 10].SetValue(dr["AB3_TRADE_COUNT"]); //成交值 rowIndex = rowIndex + 4; ldtDate = txtSDate.DateTimeValue; decimal amt1; date = txtSDate.DateTimeValue.ToString("yyyyMMdd"); amt1 = dao30010.get30015Amt_1(date); amt1 = Math.Round(amt1 / 100000000 / 2, 2, MidpointRounding.AwayFromZero); ws30014.Cells[rowIndex, 0].Value = amt1; decimal amt2; amt2 = dao30010.get30015Amt_2(ldtDate); //PB這邊看起來若傳回的值為0,就跟前面的值會一樣 if (amt2 == 0) { amt2 = amt1; } ws30014.Cells[rowIndex, 3].Value = amt2; }
protected override ResultStatus Open() { base.Open(); DateTime date = DateTime.Now; txtDate.EditValue = date.ToString("yyyy/MM/dd"); txtStkoutDate.EditValue = date.ToString("yyyy/MM/dd"); date = PbFunc.relativedate(date, date.Day * -1); txtEMonth.EditValue = date.ToString("yyyy/MM"); date = PbFunc.relativedate(date, date.Day * -1); date = PbFunc.relativedate(date, date.Day * -1); txtSMonth.EditValue = date.ToString("yyyy/MM"); txtEMonth.Focus(); return(ResultStatus.Success); }
protected override ResultStatus AfterOpen() { base.AfterOpen(); string lsYMD = new D30790().MaxDate(); DateTime date; if (!string.IsNullOrEmpty(lsYMD)) { if (lsYMD.Length == 8) { date = lsYMD.AsDateTime("yyyyMMdd"); } else if (lsYMD.Length == 6) { date = lsYMD.AsDateTime("yyyyMM"); } else { date = DateTime.MinValue; } emEndDate.DateTimeValue = date; } else { return(ResultStatus.Fail); } if (date != DateTime.MinValue) { emStartDate.DateTimeValue = PbFunc.relativedate(date, -6); } emStartDate.Focus(); emTxEndDate.DateTimeValue = date; return(ResultStatus.Success); }
protected override ResultStatus Export() { // 1. ue_export_before if (txtEDate.DateTimeValue.Year != txtSDate.DateTimeValue.Year) { MessageDisplay.Error("不可跨年度查詢!"); txtSDate.Focus(); return(ResultStatus.Fail); } // 2. ue_export string rptId = "30030", file; // 2.1 複製檔案 file = PbFunc.wf_copy_file(rptId, rptId); if (file == "") { return(ResultStatus.Fail); } // 2.2 開啟檔案 Workbook workbook = new Workbook(); workbook.LoadDocument(file); flag = 0; // 2.3 匯出Excel int rowKeep, rowNum; rowNum = 1; #region wf_30031 string rptName, kindId; int f, g, colNum, rowTol, found; /************************************* * ls_rpt_name = 報表名稱 * ls_rpt_id = 報表代號 * li_ole_col = Excel的Column位置 * li_ole_row_tol = Excel的Column預留數 * ls_kind_id = param_key *************************************/ rptName = "當年每月日均量統計表"; rptId = "30031"; string symd = txtSDate.Text.Replace("/", "").Trim(); string eymd = txtEDate.Text.Replace("/", "").Trim(); string ymd = ""; // 切換Sheet,總共只有一個sheet Worksheet ws30030 = workbook.Worksheets[0]; // RPT daoRPT = new RPT(); DataTable dtRPT = daoRPT.ListAllByTXD_ID(rptId); if (dtRPT.Rows.Count <= 0) { MessageDisplay.Error(rptId + '-' + "RPT無任何資料!"); //return ResultStatus.Fail; } else { // 讀取資料 dao30030 = new D30030(); DataTable dt30031 = dao30030.d_30031(symd + "01", eymd + "31"); dt30031.Filter("RPT_SEQ_NO > 0"); if (dt30031.Rows.Count <= 0) { MessageDisplay.Info(eymd + "," + rptId + '-' + rptName + ",無任何資料!"); //return ResultStatus.Fail; } else { // 填入資料 rowTol = rowNum + 302; ws30030.Cells[rowTol + 2, 0].Value = txtEDate.Text.SubStr(5, 2) + "月百分比(%)"; string rptType, sumType; rptType = ""; g = 0; for (f = 0; f < dt30031.Rows.Count; f++) { DataRow dr = dt30031.Rows[f]; if (ymd != dr["AI2_YMD"].AsString()) { ymd = dr["AI2_YMD"].AsString(); rowNum = rowNum + 1; ws30030.Cells[rowNum, 0].Value = ymd.SubStr(4, 2) + "/" + ymd.SubStr(6, 2); } kindId = dr["AI2_PARAM_KEY"].AsString(); colNum = dr["RPT_SEQ_NO"].AsInt() - 1; ws30030.Cells[rowNum, colNum].SetValue(dr["AI2_M_QNTY"]); /* 未沖銷量 */ DataRow[] find = dtRPT.Select("rpt_value like 'OI%'"); if (find.Length != 0) { found = dtRPT.Rows.IndexOf(find[0]); } else { found = -1; } if (found >= 0) { found = dtRPT.Rows[found]["RPT_SEQ_NO"].AsInt(); ws30030.Cells[rowNum, found - 1].SetValue(dt30031.Compute("sum(AI2_OI)", $@"AI2_YMD='{ymd}'")); } } ws30030.Cells[rowTol + 1, 0].Value = "合計(" + (txtEDate.DateTimeValue.Year - 1911).AsString() + '/' + txtEDate.DateTimeValue.Month.AsString() + ')'; // 刪除空白列 if (rowTol > rowNum) { ws30030.Rows.Remove(rowNum + 1, rowTol - rowNum); } flag++; } } #endregion rowNum = rowNum + 6; #region wf_30032 rptName = "當年每月日均量統計表"; rptId = "30032"; // 讀取資料 DataTable dt30032 = dao30030.d_30032("M", symd.SubStr(0, 4) + "01", eymd); if (dt30032.Rows.Count == 0) { //PB這邊註解掉 //MessageDisplay.Info(eymd + "," + rptId + '-' + rptName + ",無任何資料!"); //return ResultStatus.Fail; } dt30032.Filter("RPT_SEQ_NO > 0"); /* OI */ DataTable dt30032OI = dao30030.d_30032_OI(symd.SubStr(0, 4) + "01", eymd); if (dt30032OI.Rows.Count == 0) { //PB這邊註解掉 //MessageDisplay.Info(eymd + "," + rptId + '-' + rptName + ",無任何資料!"); //return ResultStatus.Fail; } int colTol = dao30030.colTol() - 1; rowTol = rowNum + 12; //填入資料 int row = 0; ymd = ""; g = 0; found = 0; for (f = 0; f < dt30032.Rows.Count; f++) { DataRow dr = dt30032.Rows[f]; if (ymd != dr["AI2_YMD"].AsString()) { ymd = dr["AI2_YMD"].AsString(); if (ymd == "999999") { row = rowTol + 1; } else { rowNum = rowNum + 1; row = rowNum; ws30030.Cells[row, 0].Value = (ymd.SubStr(0, 4).AsInt() - 1911).AsString() + "/" + ymd.SubStr(4, 2); } /* 未沖銷量 */ if (ymd == "999999") { colNum = dt30032OI.Rows[found]["RPT_SEQ_NO"].AsInt() - 1; decimal sumAI2OI = dt30032OI.Compute("sum(AI2_OI)", "").AsDecimal(); decimal sumAI2DayCount = dt30032OI.Compute("sum(AI2_DAY_COUNT)", "").AsDecimal(); if (sumAI2DayCount == 0) { ws30030.Cells[row, colNum].Value = 0; } else { decimal sumAvgOI = Math.Round(sumAI2OI / sumAI2DayCount, MidpointRounding.AwayFromZero); ws30030.Cells[row, colNum].Value = sumAvgOI; } } else { DataRow[] find = dt30032OI.Select("ai2_ymd ='" + ymd + "'"); if (find.Length != 0) { found = dt30032OI.Rows.IndexOf(find[0]); } else { found = -1; } if (found >= 0) { colNum = dt30032OI.Rows[found]["RPT_SEQ_NO"].AsInt() - 1; decimal AI2OI = dt30032OI.Rows[found]["AI2_OI"].AsDecimal(); decimal AI2DayCount = dt30032OI.Rows[found]["AI2_DAY_COUNT"].AsDecimal(); if (AI2DayCount == 0) { ws30030.Cells[row, colNum].Value = 0; } else { decimal avgOI = Math.Round(AI2OI / AI2DayCount, MidpointRounding.AwayFromZero); ws30030.Cells[row, colNum].Value = avgOI; } } } colNum = colTol; if (colNum > 0) { decimal mQntyYM = dt30032.Compute($@"sum(ai2_m_qnty)", $@"AI2_YMD='{ymd}'").AsDecimal() - dt30032.Compute($@"sum(ai2_m_qnty)", $@"AI2_YMD='{ymd}' and substring(AI2_PARAM_KEY,1,3)='SUM'").AsDecimal(); decimal dayCountYM = dt30032.Compute("max(ai2_day_count)", $@"AI2_YMD='{ymd}'").AsDecimal(); ws30030.Cells[row, colNum].Value = Math.Round(mQntyYM / dayCountYM, MidpointRounding.AwayFromZero); } } kindId = dr["AI2_PARAM_KEY"].AsString(); colNum = dr["RPT_SEQ_NO"].AsInt() - 1; if (kindId == "RHF") { colNum = colNum; } decimal ai2MQnty = dr["AI2_M_QNTY"].AsDecimal(); decimal ai2DayCount = dr["AI2_DAY_COUNT"].AsDecimal(); ws30030.Cells[row, colNum].Value = Math.Round(ai2MQnty / ai2DayCount, MidpointRounding.AwayFromZero); } // 刪除空白列 if (rowTol > rowNum) { ws30030.Rows.Remove(rowNum + 1, rowTol - rowNum); } #endregion rowNum = rowNum + 4; rowKeep = rowNum; #region wf_30033 int dayCount; decimal taifex, tse, sgxDt; rowTol = 0; rptName = "當年每月TAIFEX期貨與TSE成交值之比較表"; rptId = "30033"; // 讀取資料 DataTable dt30033 = dao30030.d_30033(symd.SubStr(0, 4) + "01", eymd); if (dt30033.Rows.Count <= 0) { MessageDisplay.Info(symd.SubStr(0, 4) + "01~" + eymd + "," + rptId + '-' + rptName + ",無任何資料!"); //return ResultStatus.Fail; } else { // 填入資料 rowTol = rowNum + 1 + 11; /* 明細 */ for (f = 0; f < dt30033.Rows.Count; f++) { DataRow dr = dt30033.Rows[f]; rowNum = rowNum + 1; taifex = dr["AA1_TAIFEX"].AsDecimal(); tse = dr["AA1_TSE"].AsDecimal(); sgxDt = dr["AA1_SGX_DT"].AsDecimal(); dayCount = dr["AA1_DAY_COUNT"].AsInt(); ws30030.Cells[rowNum, 0].Value = dr["AA1_YM"].AsString(); ws30030.Cells[rowNum, 1].Value = taifex; ws30030.Cells[rowNum, 2].Value = tse; ws30030.Cells[rowNum, 4].Value = dayCount; } // 刪除空白列 if (rowTol > rowNum) { ws30030.Rows.Remove(rowNum + 1, rowTol - rowNum); } flag++; } #endregion rowTol = rowNum + 1; rowNum = rowKeep; #region wf_30034 string ym; decimal value; rptName = "當年每月TAIFEX期貨與TSE成交值之比較表"; rptId = "30034"; // 讀取資料 // 計算當月最後一日 if (eymd.SubStr(4, 2) == "12") { ym = (eymd.SubStr(0, 4).AsInt() + 1).AsString() + "01"; } else { ym = eymd.SubStr(0, 4) + ("0" + (eymd.SubStr(4, 2).AsInt() + 1).AsString()).PadLeft(3, '0').SubStr(1, 2); } ym = PbFunc.relativedate((ym.SubStr(0, 4) + "/" + ym.SubStr(4, 2) + "/01").AsDateTime("yyyy/MM/dd"), -1).ToString("yyyyMMdd"); DataTable dt30034 = dao30030.d_30034(txtSDate.Text.SubStr(0, 4) + "0101", ym, txtSDate.Text.SubStr(0, 4) + "01", eymd); if (dt30034.Rows.Count <= 0) { MessageDisplay.Info(eymd.SubStr(0, 4) + "0101~" + ym + "," + rptId + '-' + rptName + ",無任何資料!"); //return ResultStatus.Fail; } else { // 每月 for (f = rowNum + 1; f <= rowTol - 1; f++) { ym = ws30030.Cells[f, 0].Value.AsString(); DataRow[] find = dt30034.Select("trim(stw_ymd) ='" + ym + "'"); if (find.Length != 0) { found = dt30034.Rows.IndexOf(find[0]); } else { found = -1; } if (found >= 0) { decimal stwAmt = dt30034.Rows[found]["STW_AMT"].AsDecimal(); decimal aa1UsRate = dt30034.Rows[found]["AA1_US_RATE"].AsDecimal(); decimal stwDays = dt30034.Rows[found]["STW_DAYS"].AsDecimal(); ws30030.Cells[f, 6].Value = stwAmt * aa1UsRate / stwDays / 100000000; } } // 本年度 decimal sumC = dt30034.Compute("sum(cp_c)", "").AsDecimal(); decimal sumStwDays = dt30034.Compute("sum(stw_days)", "").AsDecimal(); value = sumC / sumStwDays / 100000000; ws30030.Cells[rowTol, 6].Value = value; flag++; } #endregion if (flag <= 0) { File.Delete(file); return(ResultStatus.Fail); } else { workbook.SaveDocument(file); return(ResultStatus.Success); } }
private bool StartExport() { /* 條件值檢核*/ DateTime ldStart, ldEnd; string lsType = ""; switch (rgDate.EditValue) { case "rb_day": //週 if (!emStartDate.IsDate(emStartDate.Text, CheckDate.Start) || !emEndDate.IsDate(emEndDate.Text, CheckDate.End)) { return(false); } if (string.Compare(emStartDate.Text, emEndDate.Text) > 0) { MessageDisplay.Error(GlobalInfo.ErrorText, CheckDate.Datedif); return(false); } ldStart = Convert.ToDateTime(emStartDate.Text); ldEnd = Convert.ToDateTime(emEndDate.Text); startYMD = emStartDate.Text.Replace("/", "").SubStr(0, 8); endYMD = emEndDate.Text.Replace("/", "").SubStr(0, 8); lsType = "Day"; sumType = "D"; logText = ldStart.ToString("yyyy.MM.dd") + "至" + ldEnd.ToString("yyyy.MM.dd") + " 交易量"; break; case "rb_week": //週 if (!emStartDate1.IsDate(emStartDate1.Text, CheckDate.Start) || !emEndDate1.IsDate(emEndDate1.Text, CheckDate.End)) { return(false); } if (string.Compare(emStartDate1.Text, emEndDate1.Text) > 0) { MessageDisplay.Error(GlobalInfo.ErrorText, CheckDate.Datedif); return(false); } ldStart = Convert.ToDateTime(emStartDate1.Text); ldEnd = Convert.ToDateTime(emEndDate1.Text); startYMD = emStartDate1.Text.Replace("/", "").SubStr(0, 8); endYMD = emEndDate1.Text.Replace("/", "").SubStr(0, 8); lsType = "Week"; sumType = "D"; logText = ldStart.ToString("yyyy.MM.dd") + "至" + ldEnd.ToString("yyyy.MM.dd") + " 交易量"; break; case "rb_month": //月 string emSmth = emStartMth.Text + "/01"; string emEmth = emEndMth.Text + "/01"; if (!emStartMth.IsDate(emSmth, CheckDate.Start) || !emEndMth.IsDate(emEmth, CheckDate.End)) { return(false); } ldStart = Convert.ToDateTime(emSmth); ldEnd = PbFunc.relativedate(Convert.ToDateTime(emEmth), 31); if (ldEnd.Month != PbFunc.Right(emStartMth.Text, 2).AsInt()) { ldEnd = PbFunc.relativedate(ldEnd, -ldEnd.Day); } startYMD = emStartMth.Text.Replace("/", "").SubStr(0, 6); endYMD = emEndMth.Text.Replace("/", "").SubStr(0, 6); lsType = "Month"; sumType = "M"; logText = startYMD + "至" + endYMD + " 交易量"; break; case "rb_year": //年 startYMD = emStartYear.Text; endYMD = emEndYear.Text; lsType = "Year"; sumType = "Y"; logText = startYMD + "至" + endYMD + " 交易量"; break; default: break; } saveFilePath = _ProgramID + "_" + lsType + "(" + startYMD + "-" + endYMD + ")"; //期別 switch (rgPeriod.EditValue) { case "rb_txw"://一週到期契約 lsKindId2 = "TXW%"; saveFilePath = saveFilePath + "W"; break; case "rb_txo"://一般天期契約 lsKindId2 = "TXO%"; saveFilePath = saveFilePath + "S"; break; default://所有天期契約 lsKindId2 = "%"; break; } /*點選儲存檔案之目錄*/ saveFilePath = PbFunc.wf_GetFileSaveName(saveFilePath + ".csv"); if (string.IsNullOrEmpty(saveFilePath)) { return(false); } /******************* * Messagebox *******************/ st_msg_txt.Visible = true; st_msg_txt.Text = "開始轉檔..."; st_msg_txt.Text = logText + " 轉檔中..."; this.Cursor = Cursors.WaitCursor; this.Refresh(); Thread.Sleep(5); return(true); }
protected override ResultStatus Export() { if (!StartExport()) { return(ResultStatus.Fail); } string lsFile = PbFunc.wf_copy_file(_ProgramID, "30780"); MessageDisplay message = new MessageDisplay(); try { //交易時段 string lsMarketCode = string.Empty; switch (rgTime.EditValue.ToString()) { case "rb_market0": lsMarketCode = "0"; break; case "rb_market1": lsMarketCode = "1"; break; default: lsMarketCode = "%"; break; } //em_end_date.text = string(relativedate(date(em_month.text + "/01"), 31), 'yyyy/mm') + "/10"; DateTime endDate = PbFunc.relativedate(emMonth.Text.AsDateTime("yyyy/MM"), 31); //下個月10日 endDate = new DateTime(endDate.Year, endDate.Month, 10); b30780 = new B30780(lsFile, emMonth.Text, lsMarketCode, new D30780().MaxDate(endDate)); ShowMsg("30780_1-附表1_期貨暨選擇權最近2個月市場成交量變動比較表 轉檔中..."); message.OutputShowMessage = b30780.WF30780one(); ShowMsg("30780_2-附表2_期貨暨選擇權最近6個月市場成交量彙總表 轉檔中..."); message.OutputShowMessage = b30780.WF30780two(); ShowMsg("30780_4-附表4_國內期貨市場主要期貨商月市占率概況表(依成交量排序) 轉檔中..."); message.OutputShowMessage = b30780.WF30780four(); ShowMsg("30780_5-附表5_國內期貨市場期貨商月成交量成長暨衰退概況表 轉檔中..."); message.OutputShowMessage = b30780.WF30780five(); //沒有任何資料時刪除檔案 if (string.IsNullOrEmpty(message.OutputShowMessage)) { //要跳四次無任何資料才能刪除 if (File.Exists(lsFile)) { File.Delete(lsFile); } return(ResultStatus.Fail); } } catch (Exception ex) { if (File.Exists(lsFile)) { File.Delete(lsFile); } WriteLog(ex); return(ResultStatus.Fail); } finally { EndExport(); } return(ResultStatus.Success); }
/// <summary> /// 寫入 Data_30322ab sheet /// </summary> /// <returns></returns> public string Wf30322() { string SheetName = "Data_30322ab"; Workbook workbook = new Workbook(); try { DateTime emMonthDate = _emMonthText.AsDateTime();//輸入日期轉時間格式 //前月倒數1天交易日 DateTime ldtSdate = dao30320.GetLastTradeDate(emMonthDate.ToString("yyyyMM01")); //月底 string lsYMD = string.Empty; if (emMonthDate.Month == 12) { lsYMD = $"{emMonthDate.AddYears(1)}01"; } else { lsYMD = emMonthDate.AddMonths(1).ToString("yyyyMM"); } DateTime ldtEdate = PbFunc.relativedate(lsYMD.AsDateTime("yyyyMM"), -1); //切換Sheet workbook.LoadDocument(_lsFile); Worksheet worksheet = workbook.Worksheets[SheetName]; //讀取資料 DataTable dt = dao30320.Get30322Data(ldtSdate, ldtEdate); dt = dt.Filter("RPT_SEQ_NO > 0"); if (dt.Rows.Count <= 0) { return($"{ldtSdate.ToShortDateString()}~{_emMonthText.AsDateTime().ToString("yyyy/MM/31")},30322-指數期貨價量資料,無任何資料!"); } int rowIndex = 1; int RowTotal = 32; //Excel的Column預留數32 int addRowCount = 0; //總計寫入的行數 lsYMD = ""; foreach (DataRow row in dt.Rows) { //不同的日期就寫入新的一行 if (lsYMD != row["AI3_DATE"].AsString()) { lsYMD = row["AI3_DATE"].AsString(); rowIndex = rowIndex + 1; addRowCount++; //日期 worksheet.Rows[rowIndex][1 - 1].Value = row["AI3_DATE"].AsDateTime().ToString("MM/dd"); } //寫入RPT_SEQ_NO指定的Excel欄位 int columnIndex = row["RPT_SEQ_NO"].AsInt(); worksheet.Rows[rowIndex][columnIndex - 1].Value = row["AI3_CLOSE_PRICE"].AsDecimal(); } //刪除空白列 if (RowTotal > addRowCount) { worksheet.Rows.Remove(rowIndex + 1, RowTotal - addRowCount); //重新選取圖表範圍 ResetChartData(rowIndex + 1, workbook, worksheet, "30320a");//ex:30320a } worksheet.ScrollTo(0, 0); } catch (Exception ex) { throw ex; } finally { workbook.SaveDocument(_lsFile); } return(MessageDisplay.MSG_OK); }
protected override ResultStatus Export() { dao30020 = new D30020(); string rptId, file; rptId = "30020"; /****************** * 複製檔案 ******************/ file = PbFunc.wf_copy_file(rptId, rptId); if (file == "") { return(ResultStatus.Fail); } /****************** * 開啟檔案 ******************/ Workbook workbook = new Workbook(); workbook.LoadDocument(file); #region 30021 string rptName; string accType; DateTime date, maxDate; int i, j, rowNum, colNum, accRow, accRowTol; long found; rptName = "期貨交易累計開戶及交易戶數統計表"; rptId = "30021"; /****************** * 讀取資料 ******************/ DataTable dt30021 = dao30020.d_30021(txtSDate.DateTimeValue, txtEDate.DateTimeValue); if (dt30021.Rows.Count == 0) { MessageDisplay.Info(txtSDate.Text + "~" + txtEDate.Text + "," + rptId + '-' + rptName + ",無任何資料!"); return(ResultStatus.Fail); } //ACC_TYPE //不撈資料,只要schema DataTable dt30021_acc_type = dao30020.d_30021_acc_type(null, null); //切換sheet Worksheet ws30021 = workbook.Worksheets[0]; /****************** * 身份碼總列數 * 隱藏於A2 ******************/ accRowTol = ws30021.Cells[1, 0].Value.AsInt(); date = "1900/1/1".AsDateTime(); maxDate = dt30021.Rows[0]["AB1_DATE"].AsDateTime(); rowNum = 1; accRow = 2; colNum = 2; //ACC_TYPE for (i = accRow; i < accRowTol; i++) { dt30021_acc_type.Rows.Add(); dt30021_acc_type.Rows[dt30021_acc_type.Rows.Count - 1]["AB1_ACC_TYPE"] = ws30021.Cells[i, 0].Value; } for (j = 0; j < dt30021.Rows.Count; j++) { rowNum = rowNum + 1; /* 換日期時,Row:重頭開始& Col:加1 */ if (date != dt30021.Rows[j]["AB1_DATE"].AsDateTime()) { rowNum = 1; accRow = 2; colNum = colNum + 1; date = dt30021.Rows[j]["AB1_DATE"].AsDateTime(); /* 日期 */ ws30021.Cells[rowNum, colNum].Value = date; /* 累計開戶數日期 */ if (j == 0) { ws30021.Cells[rowNum, 2].Value = dt30021.Rows[j]["AB1_DATE"].AsDateTime(); } } accType = dt30021.Rows[j]["AB1_ACC_TYPE"].AsString(); //DataTable的Select預設不分大小寫,這邊要將它開啟 dt30021_acc_type.CaseSensitive = true; if (dt30021_acc_type.Select("AB1_ACC_TYPE = '" + accType + "'").Length > 0) { rowNum = dt30021_acc_type.Rows.IndexOf(dt30021_acc_type.Select("AB1_ACC_TYPE = '" + accType + "'")[0]); } else { rowNum = -1; } if (rowNum >= 0) { /* 每日 */ rowNum = rowNum + 2; ws30021.Cells[rowNum, colNum].SetValue(dt30021.Rows[j]["AB1_COUNT"]); /* 累計開戶數 */ if (date == maxDate) { ws30021.Cells[rowNum, 2].SetValue(dt30021.Rows[j]["AB1_ACCU_COUNT"]); } } } #endregion #region 30022 rptName = "期貨交易累計開戶及交易戶數統計表"; rptId = "30022"; //讀取資料 /* 往前追溯100天 */ DateTime relDate; relDate = PbFunc.relativedate(txtEDate.DateTimeValue, -100); DataTable dt30022 = dao30020.d_30022(relDate, txtEDate.DateTimeValue); if (dt30022.Rows.Count == 0) { MessageDisplay.Info(relDate.ToString("yyyy/MM/dd") + "~" + txtEDate.Text + "," + rptId + '-' + rptName + ",無任何資料!"); return(ResultStatus.Fail); } //切換sheet Worksheet ws30022 = workbook.Worksheets[1]; rowNum = 0; for (j = 0; j < dt30022.Rows.Count; j++) { rowNum = rowNum + 1; ws30022.Cells[rowNum, 0].Value = dt30022.Rows[j]["AB1_DATE"].AsDateTime(); ws30022.Cells[rowNum, 1].SetValue(dt30022.Rows[j]["AB1_COUNT"]); ws30022.Cells[rowNum, 2].SetValue(dt30022.Rows[j]["AB1_ACCU_COUNT"]); ws30022.Cells[rowNum, 3].SetValue(dt30022.Rows[j]["AB1_TRADE_COUNT"]); } #endregion //圖表重設 ChartObject chartObjs = ws30021.Charts[0]; string rcnt = (dt30022.Rows.Count + 1).AsString(); ChartData chartDataArgs = new ChartData(); ChartData chartData1 = new ChartData(); ChartData chartData2 = new ChartData(); Range range1 = ws30022.Range["='30022'!$D$2:$D$" + rcnt]; Range range2 = ws30022.Range["='30022'!$C$2:$C$" + rcnt]; Range args = ws30022.Range["='30022'!$A$2:$A$" + rcnt]; chartData1.RangeValue = range1; chartData2.RangeValue = range2; chartDataArgs.RangeValue = args; chartObjs.Series[0].Arguments = chartDataArgs; chartObjs.Series[0].Values = chartData1; chartObjs.Series[1].Arguments = chartDataArgs; chartObjs.Series[1].Values = chartData2; workbook.SaveDocument(file); return(ResultStatus.Success); }
private bool StartExport() { /* 條件值檢核*/ DateTime ldStart, ldEnd; string lsType = ""; switch (rgDate.EditValue) { case "rb_day": //週 if (!emStartDate.IsDate(emStartDate.Text, CheckDate.Start) || !emEndDate.IsDate(emEndDate.Text, CheckDate.End)) { return(false); } if (string.Compare(emStartDate.Text, emEndDate.Text) > 0) { MessageDisplay.Error(GlobalInfo.ErrorText, CheckDate.Datedif); return(false); } ldStart = Convert.ToDateTime(emStartDate.Text); ldEnd = Convert.ToDateTime(emEndDate.Text); lsSymd = emStartDate.Text.Replace("/", "").SubStr(0, 8); lsEymd = emEndDate.Text.Replace("/", "").SubStr(0, 8); lsType = "Daily"; sumType = "D"; logText = ldStart.ToString("yyyy.MM.dd") + "至" + ldEnd.ToString("yyyy.MM.dd") + " 交易量"; break; case "rb_week": //週 if (!emStartDate1.IsDate(emStartDate1.Text, CheckDate.Start) || !emEndDate1.IsDate(emEndDate1.Text, CheckDate.End)) { return(false); } if (string.Compare(emStartDate1.Text, emEndDate1.Text) > 0) { MessageDisplay.Error(GlobalInfo.ErrorText, CheckDate.Datedif); return(false); } ldStart = Convert.ToDateTime(emStartDate1.Text); ldEnd = Convert.ToDateTime(emEndDate1.Text); lsSymd = emStartDate1.Text.Replace("/", "").SubStr(0, 8); lsEymd = emEndDate1.Text.Replace("/", "").SubStr(0, 8); lsType = "Weekly"; sumType = "D"; logText = ldStart.ToString("yyyy.MM.dd") + "至" + ldEnd.ToString("yyyy.MM.dd") + " 交易量"; break; case "rb_month": //月 string Smth = this.emStartMth.Text + "/01"; string Emth = this.emEndMth.Text + "/01"; if (!this.emStartMth.IsDate(Smth, CheckDate.Start) || !this.emEndMth.IsDate(Emth, CheckDate.End)) { return(false); } ldStart = Convert.ToDateTime(Smth); ldEnd = PbFunc.relativedate(Convert.ToDateTime(Emth), 31); if (ldEnd.Month != PbFunc.Right(emStartMth.Text, 2).AsInt()) { ldEnd = PbFunc.relativedate(ldEnd, -ldEnd.Day); } lsSymd = emStartMth.Text.Replace("/", "").SubStr(0, 6); lsEymd = emEndMth.Text.Replace("/", "").SubStr(0, 6); lsType = "Monthly"; sumType = "M"; logText = lsSymd + "至" + lsEymd + " 交易量"; break; case "rb_year": //年 lsSymd = emStartYear.Text; lsEymd = emEndYear.Text; lsType = "Yearly"; sumType = "Y"; logText = lsSymd + "至" + lsEymd + " 交易量"; break; default: break; } //商品別 if (rbTMU.EditValue.Equals("rb_options")) { lsType = lsType + "_OPT"; lsProdType = "O"; } else { lsType = lsType + "_FUT"; lsProdType = "F"; } //ids_1.dataobject = "d_"+gs_txn_id if (cbxEng.Checked) { lsType = lsType + "_eng"; } //交易時段 switch (rgTime.EditValue) { case "rb_market0": lsMarketCode = "0"; lsType = lsType + "_day"; break; case "rb_market1": lsMarketCode = "1"; lsType = lsType + "_night"; break; default: lsMarketCode = "%"; break; } /*點選儲存檔案之目錄*/ saveFilePath = PbFunc.wf_GetFileSaveName(lsType + "(" + lsSymd + "-" + lsEymd + ").csv"); if (string.IsNullOrEmpty(saveFilePath)) { return(false); } /******************* * Messagebox *******************/ stMsgTxt.Visible = true; stMsgTxt.Text = "開始轉檔..."; stMsgTxt.Text = logText + " 轉檔中..."; this.Cursor = Cursors.WaitCursor; this.Refresh(); Thread.Sleep(5); return(true); }
/// <summary> /// 顯示明細 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnDetail_Click(object sender, EventArgs e) { try { //重設gridview gcDetail.DataSource = null; int row, col, found; string prodType, prodTypeName, kindID, stockID, paramKey, abroad, implBeginYmd, issueBeginYmd, mocfYmd; string opType; decimal ldcRate; gvMain.CloseEditor(); gvMain.UpdateCurrentRow(); DataTable dtGrid = dao40071.d_40071_detail(); //ids_tmp 空的,拿來重置gcDetail dtGrid.Columns["ADJ_TYPE"].ColumnName = "OP_TYPE"; dtGrid.Columns["DATA_YMD"].ColumnName = "YMD"; //dtTemp.Columns["CM_A * NVL(MGT6_REF_XXX,1)"].ColumnName = "CM_A"; 沒成功撈到資料的話欄位名稱不會變? DataTable dtMGD2 = dao40072.d_40072(); //ids_mgd2 空的 txtSDate.Text = "1901/01/01"; prodType = "S"; paramKey = "ST%"; abroad = "%"; kindID = "%"; //產生明細檔 DataTable dtInput = (DataTable)gcMain.DataSource; foreach (DataRow drInput in dtInput.Rows) { opType = "I"; stockID = drInput["STOCK_ID"].ToString(); implBeginYmd = drInput["IMPL_BEGIN_YMD"].ToString(); //交易日為處置期間之首日 drInput["YMD"] = implBeginYmd.Replace("/", "").AsString(); implBeginYmd = implBeginYmd.Replace("/", "").AsString(); dtMGD2 = dao40072.d_40072(drInput["YMD"].AsDateTime().ToString("yyyyMMdd"), isAdjType, stockID); if (dtMGD2.Rows.Count > 0) { DialogResult result = MessageDisplay.Choose(stockID + "資料已存在,是否重新產製資料,若不重產資料,請按「預覽」!"); if (result == DialogResult.No) { return; } opType = "U"; } //處置期間首日+1個月 DateTime tmp = DateTime.ParseExact(implBeginYmd, "yyyyMMdd", null); mocfYmd = PbFunc.relativedate(tmp, 30).ToString("yyyyMMdd"); /*次一營業日*/ //implBeginYmd = implBeginYmd.AsDateTime().ToString("yyyyMMdd"); issueBeginYmd = daoMOCF.GetNextTradeDay(implBeginYmd, mocfYmd); //終止生效日為處置期間迄日 drInput["ISSUE_END_YMD"] = drInput["IMPL_END_YMD"]; //開始生效日為處置期間首日之次一個營業日 drInput["ISSUE_BEGIN_YMD"] = issueBeginYmd; //判斷是否有空值 for (col = 0; col < dtInput.Columns.Count; col++) { if (dtInput.Columns[col].ColumnName == "CPSORT") { continue; //這欄是排序用的毋須判斷 } if (drInput[col] == DBNull.Value || drInput[col].ToString() == "") { MessageDisplay.Warning("請確認資料是否輸入完成!"); return; } } stockID = stockID + "%"; //調整倍數(計算用1+調整倍數) ldcRate = drInput["RATE"].AsDecimal() - 1; //這邊才去讀SP DataTable dtTemp = dao40071.d_40071_detail(implBeginYmd, prodType, paramKey, abroad, kindID, stockID, ldcRate); dtTemp.Columns["ADJ_TYPE"].ColumnName = "OP_TYPE"; dtTemp.Columns["DATA_YMD"].ColumnName = "YMD"; if (dtTemp.Columns["CM_A*NVL(MGT6_REF_XXX,1)"] != null) { dtTemp.Columns["CM_A*NVL(MGT6_REF_XXX,1)"].ColumnName = "CM_A"; //沒撈到值的話欄位名稱不會變,若資料為個股類也不會變 } foreach (DataRow drTemp in dtTemp.Rows) { drTemp["ISSUE_BEGIN_YMD"] = issueBeginYmd; drTemp["ISSUE_END_YMD"] = drInput["impl_end_ymd"]; drTemp["IMPL_BEGIN_YMD"] = implBeginYmd; drTemp["IMPL_END_YMD"] = drInput["impl_end_ymd"]; drTemp["PUB_YMD"] = drInput["pub_ymd"]; drTemp["YMD"] = implBeginYmd; drTemp["OP_TYPE"] = opType; drTemp["ADJ_RATE"] = drInput["RATE"]; } //將資料複製到明細表 //dtGrid = dtTemp.Clone(); foreach (DataRow drTemp in dtTemp.Rows) { dtGrid.ImportRow(drTemp); } dtGrid.AcceptChanges(); gcDetail.DataSource = dtGrid; }//foreach (DataRow drInput in dtInput.Rows) //sort("stock_id A prod_type A ") if (dtGrid.Rows.Count != 0) { dtGrid = dtGrid.AsEnumerable().OrderBy(x => x.Field <string>("STOCK_ID")) .ThenBy(x => x.Field <string>("PROD_TYPE")) .CopyToDataTable(); } gcDetail.DataSource = dtGrid; if (gvDetail.RowCount == 0) { MessageDisplay.Warning("無明細資料,請確認「交易日期」及「商品調整幅度」是否填寫正確!"); return; } } catch (Exception ex) { MessageDisplay.Error("顯示明細錯誤"); throw ex; } }
protected override ResultStatus Save(PokeBall pokeBall) { try { if (gvDetail.RowCount == 0) { MessageDisplay.Info("沒有變更資料,不需要存檔!"); return(ResultStatus.Fail); } #region ue_save_before gvMain.CloseEditor(); gvMain.UpdateCurrentRow(); gvDetail.CloseEditor(); gvDetail.UpdateCurrentRow(); string stockID, ymd, kindID, adjTypeName, opType, dbname, flag; string issueBeginYmd, issueEndYmd, implBeginYmd, implEndYmd, pubYmd, tradeYmd, mocfYmd, nextYmd; int found, count, row, col, currRow; decimal ldblRate; DateTime ldtWTIME = DateTime.Now; DataTable dtGrid = (DataTable)gcDetail.DataSource; found = dtGrid.Rows.IndexOf(dtGrid.Select("OP_TYPE <> ' '").FirstOrDefault()); if (found == -1) { MessageDisplay.Warning("沒有變更資料,不需要存檔!"); return(ResultStatus.Fail); } if (dtGrid.Rows.Count == 0) { MessageDisplay.Warning("無明細資料,請重新產生明細"); return(ResultStatus.FailButNext); } DataTable dtMGD2; //ids_mgd2 DataTable dtMGD2Log = dao40071.d_40071_log(); //ids_old dtMGD2Log.Clear(); //只取schema for (int f = 0; f < dtGrid.Rows.Count; f++) { if (dtGrid.Rows[f].RowState == DataRowState.Deleted) { continue; } DataRow dr = dtGrid.Rows[f]; opType = dr["OP_TYPE"].ToString(); flag = dr["DATA_FLAG"].AsString(); stockID = dr["STOCK_ID"].AsString(); //檢查同一標的的級距是否一致 if ((f + 1) < dtGrid.Rows.Count) { if (stockID == dtGrid.Rows[f + 1]["STOCK_ID"].AsString() && dr["M_CUR_LEVEL"].AsString() != dtGrid.Rows[f + 1]["M_CUR_LEVEL"].AsString()) { MessageDisplay.Error(stockID + "的級距不一致"); return(ResultStatus.FailButNext); } } //檢查有異動的資料 if (opType != " ") { kindID = dr["KIND_ID"].AsString(); ymd = dr["YMD"].ToString(); issueBeginYmd = dr["ISSUE_BEGIN_YMD"].ToString(); //issueEndYmd = dr["ISSUE_END_YMD"].ToString(); by tom //implBeginYmd = dr["IMPL_BEGIN_YMD"].ToString(); //implEndYmd = dr["IMPL_END_YMD"].ToString(); //pubYmd = dr["PUB_YMD"].ToString(); issueEndYmd = dr["ISSUE_END_YMD"].AsString().Replace("/", ""); implBeginYmd = dr["IMPL_BEGIN_YMD"].AsString().Replace("/", ""); implEndYmd = dr["IMPL_END_YMD"].AsString().Replace("/", ""); pubYmd = dr["PUB_YMD"].AsString().Replace("/", ""); if (ymd != implBeginYmd) { DialogResult result = MessageDisplay.Choose(stockID + "," + kindID + "交易日不等於處置起日,請問是否更新"); if (result == DialogResult.No) { return(ResultStatus.FailButNext); } } if (issueEndYmd != implEndYmd) { DialogResult result = MessageDisplay.Choose(stockID + "," + kindID + "生效迄日不等於處置迄日,請問是否更新"); if (result == DialogResult.No) { return(ResultStatus.FailButNext); } } //處置期間首日+1個月 mocfYmd = PbFunc.relativedate(implBeginYmd.AsDateTime("yyyyMMdd"), 30).ToString("yyyyMMdd"); /*次一營業日*/ nextYmd = daoMOCF.GetNextTradeDay(implBeginYmd, mocfYmd); if (issueBeginYmd != nextYmd) { DialogResult result = MessageDisplay.Choose(stockID + "," + kindID + "生效起日不等於處置起日之次一營業日,請問是否更新"); if (result == DialogResult.No) { return(ResultStatus.FailButNext); } } dtMGD2 = dao40072.d_40072(ymd, isAdjType, stockID); //資料修改,將修改前舊資料寫入log if (opType == "U") { dtMGD2.Filter("mgd2_kind_id = '" + kindID + "'"); foreach (DataRow drU in dtMGD2.Rows) { currRow = dtMGD2Log.Rows.Count; dtMGD2Log.Rows.Add(); for (col = 0; col < dtMGD2.Columns.Count; col++) { //先取欄位名稱,因為兩張table欄位順序不一致 dbname = dtMGD2.Columns[col].ColumnName; if (dbname == "CPSORT") { continue; //這個欄位是拿來排序用的,故無需複製 } dtMGD2Log.Rows[currRow][dbname] = drU[col]; } if (flag == "Y") { dtMGD2Log.Rows[currRow]["MGD2_L_TYPE"] = "U"; } if (flag == "N") { dtMGD2Log.Rows[currRow]["MGD2_L_TYPE"] = "D"; } dtMGD2Log.Rows[currRow]["MGD2_L_USER_ID"] = GlobalInfo.USER_ID; dtMGD2Log.Rows[currRow]["MGD2_L_TIME"] = ldtWTIME; } } /****************************************** * 確認商品是否在同一交易日不同情境下設定過 ******************************************/ DataTable dtSet = dao40071.IsSetOnSameDay(kindID, ymd, isAdjType); if (dtSet.Rows.Count == 0) { MessageDisplay.Info("MGD2 " + kindID + " 無任何資料!"); return(ResultStatus.FailButNext); } count = dtSet.Rows[0]["LI_COUNT"].AsInt(); adjTypeName = dtSet.Rows[0]["LS_ADJ_TYPE_NAME"].AsString(); if (count > 0) { MessageDisplay.Error(kindID + ",交易日(" + ymd + ")在" + adjTypeName + "已有資料"); return(ResultStatus.FailButNext); } /********************************* * 確認商品是否在同一生效日區間設定過 * 生效起日若與生效迄日相同,不重疊 * ex: 10/11的至10/31一般交易時段結束止,10/30的從10/31一般交易時段結束後始>>應不重疊 *************************************/ dtSet = dao40071.IsSetInSameSession(kindID, ymd, issueBeginYmd, issueEndYmd); count = dtSet.Rows[0]["LI_COUNT"].AsInt(); adjTypeName = dtSet.Rows[0]["LS_ADJ_TYPE_NAME"].AsString(); tradeYmd = dtSet.Rows[0]["LS_TRADE_YMD"].AsString(); if (count > 0) { MessageDisplay.Error(kindID + "," + adjTypeName + ",交易日(" + tradeYmd + ")在同一生效日區間內已有資料"); return(ResultStatus.FailButNext); } //判斷調整幅度是否為0 //ldblRate = dr["ADJ_RATE"].AsDecimal(); by tom ldblRate = dr["ADJ_RATE"].AsDecimal() - 1; if (ldblRate == 0) { MessageDisplay.Error("商品調整幅度不可為0"); return(ResultStatus.FailButNext); } } //if (ls_op_type != " ") } //for (int f = 0; f < dtGrid.Rows.Count; f++) #endregion string prodType; DataTable dtTemp = dao40072.d_40072(); //ids_tmp foreach (DataRow dr in dtGrid.Rows) { if (dr.RowState == DataRowState.Deleted) { continue; } opType = dr["OP_TYPE"].ToString(); //只更新有異動的資料 if (opType != " ") { kindID = dr["KIND_ID"].AsString(); stockID = dr["STOCK_ID"].AsString(); issueBeginYmd = dr["ISSUE_BEGIN_YMD"].ToString(); //issueEndYmd = dr["ISSUE_END_YMD"].ToString(); by tom //implBeginYmd = dr["IMPL_BEGIN_YMD"].ToString(); //implEndYmd = dr["IMPL_END_YMD"].ToString(); //pubYmd = dr["PUB_YMD"].ToString(); issueEndYmd = dr["ISSUE_END_YMD"].AsString().Replace("/", ""); implBeginYmd = dr["IMPL_BEGIN_YMD"].AsString().Replace("/", ""); implEndYmd = dr["IMPL_END_YMD"].AsString().Replace("/", ""); pubYmd = dr["PUB_YMD"].AsString().Replace("/", ""); ymd = dr["YMD"].ToString(); //ldblRate = dr["ADJ_RATE"].AsDecimal(); ldblRate = dr["ADJ_RATE"].AsDecimal() - 1; //刪除已存在資料 if (daoMGD2.DeleteMGD2(ymd, isAdjType, stockID, kindID) < 0) { MessageDisplay.Error("MGD2資料刪除失敗"); return(ResultStatus.FailButNext); } if (dr["DATA_FLAG"].AsString() == "Y") { currRow = dtTemp.Rows.Count; prodType = dr["PROD_TYPE"].AsString(); dtTemp.Rows.Add(); dtTemp.Rows[currRow]["MGD2_YMD"] = ymd; dtTemp.Rows[currRow]["MGD2_PROD_TYPE"] = prodType; dtTemp.Rows[currRow]["MGD2_KIND_ID"] = kindID; dtTemp.Rows[currRow]["MGD2_STOCK_ID"] = stockID; dtTemp.Rows[currRow]["MGD2_ADJ_TYPE"] = isAdjType; dtTemp.Rows[currRow]["MGD2_ADJ_RATE"] = ldblRate; dtTemp.Rows[currRow]["MGD2_ADJ_CODE"] = "Y"; dtTemp.Rows[currRow]["MGD2_ISSUE_BEGIN_YMD"] = issueBeginYmd; dtTemp.Rows[currRow]["MGD2_ISSUE_END_YMD"] = issueEndYmd; dtTemp.Rows[currRow]["MGD2_IMPL_BEGIN_YMD"] = implBeginYmd; dtTemp.Rows[currRow]["MGD2_IMPL_END_YMD"] = implEndYmd; dtTemp.Rows[currRow]["MGD2_PUB_YMD"] = pubYmd; dtTemp.Rows[currRow]["MGD2_PROD_SUBTYPE"] = dr["PROD_SUBTYPE"]; dtTemp.Rows[currRow]["MGD2_PARAM_KEY"] = dr["PARAM_KEY"]; dtTemp.Rows[currRow]["MGD2_CUR_CM"] = dr["CM_CUR_A"]; dtTemp.Rows[currRow]["MGD2_CUR_MM"] = dr["MM_CUR_A"]; dtTemp.Rows[currRow]["MGD2_CUR_IM"] = dr["IM_CUR_A"]; dtTemp.Rows[currRow]["MGD2_CUR_LEVEL"] = dr["M_CUR_LEVEL"]; dtTemp.Rows[currRow]["MGD2_CM"] = dr["CM_A"]; dtTemp.Rows[currRow]["MGD2_MM"] = dr["MM_A"]; dtTemp.Rows[currRow]["MGD2_IM"] = dr["IM_A"]; dtTemp.Rows[currRow]["MGD2_CURRENCY_TYPE"] = dr["CURRENCY_TYPE"]; dtTemp.Rows[currRow]["MGD2_SEQ_NO"] = dr["SEQ_NO"]; dtTemp.Rows[currRow]["MGD2_OSW_GRP"] = dr["OSW_GRP"]; dtTemp.Rows[currRow]["MGD2_AMT_TYPE"] = dr["AMT_TYPE"]; dtTemp.Rows[currRow]["MGD2_W_TIME"] = ldtWTIME; dtTemp.Rows[currRow]["MGD2_W_USER_ID"] = GlobalInfo.USER_ID; /****************************** * AB TYTPE: -期貨 * A選擇權A值 * B選擇權B值 *******************************/ if (prodType == "F") { dtTemp.Rows[currRow]["MGD2_AB_TYPE"] = "-"; } else { dtTemp.Rows[currRow]["MGD2_AB_TYPE"] = "A"; //複製一筆一樣的,AB Type分開存 dtTemp.ImportRow(dtTemp.Rows[currRow]); //dtTemp.Rows.Add(dtTemp.Rows[ii_curr_row]);//會跳錯 currRow = dtTemp.Rows.Count - 1; dtTemp.Rows[currRow]["MGD2_AB_TYPE"] = "B"; dtTemp.Rows[currRow]["MGD2_CUR_CM"] = dr["CM_CUR_B"]; dtTemp.Rows[currRow]["MGD2_CUR_MM"] = dr["MM_CUR_B"]; dtTemp.Rows[currRow]["MGD2_CUR_IM"] = dr["IM_CUR_B"]; dtTemp.Rows[currRow]["MGD2_CM"] = dr["CM_B"]; dtTemp.Rows[currRow]["MGD2_MM"] = dr["MM_B"]; dtTemp.Rows[currRow]["MGD2_IM"] = dr["IM_B"]; } } //if (dr["DATA_FLAG"].AsString()=="Y") } //if (ls_op_type != " ") } //foreach (DataRow dr in dtGrid.Rows) //Update DB //ids_tmp.update() if (dtTemp.Rows.Count > 0) { ResultData myResultData = daoMGD2.UpdateMGD2(dtTemp); if (myResultData.Status == ResultStatus.Fail) { MessageDisplay.Error("更新資料庫MGD2錯誤! "); return(ResultStatus.FailButNext); } } //ids_old.update() if (dtMGD2Log.Rows.Count > 0) { ResultData myResultData = daoMGD2L.UpdateMGD2L(dtMGD2Log); if (myResultData.Status == ResultStatus.Fail) { MessageDisplay.Error("更新資料庫MGD2L錯誤! "); return(ResultStatus.FailButNext); } } //Write LOGF WriteLog("變更資料 ", "Info", "I"); //報表儲存pdf ReportHelper _ReportHelper = new ReportHelper(gcDetail, _ProgramID, this.Text); CommonReportLandscapeA3 reportLandscape = new CommonReportLandscapeA3();//設定為橫向列印 reportLandscape.printableComponentContainerMain.PrintableComponent = gcDetail; reportLandscape.IsHandlePersonVisible = false; reportLandscape.IsManagerVisible = false; _ReportHelper.Create(reportLandscape); _ReportHelper.Export(FileType.PDF, _ReportHelper.FilePath); MessageDisplay.Info("報表儲存完成!"); } catch (Exception ex) { MessageDisplay.Error("儲存錯誤"); throw ex; } return(ResultStatus.Success); }
protected override ResultStatus Export() { string showMsg = ""; try { this.Cursor = Cursors.WaitCursor; this.Refresh(); Thread.Sleep(5); lblProcessing.Visible = true; ShowMsg("開始轉檔..."); dao30202 = new D30202(); //判斷是否有檔案,決定是否要寫入DB. showMsg = "讀取既有計算資料錯誤"; string cpYmd = txtDate.DateTimeValue.ToString("yyyyMMdd"); DataTable dtPL1 = dao30202.d_30202_pl1(cpYmd); if (dtPL1.Rows.Count > 0) { DialogResult result = MessageDisplay.Choose("已有計算資料,是否要更新資料庫資料?"); if (result == DialogResult.No) { cbxDB.Checked = false; } } txtPrevEymd.DateTimeValue = PbFunc.f_get_end_day("DATE", "", txtEMonth.Text); txtCurEymd.DateTimeValue = PbFunc.f_get_end_day("DATE", "", txtCurEMonth.Text); string rptId = "30202", rptName = "股價指數暨黃金類商品部位限制數檢視表", file, curSMonth = txtCurSMonth.Text.Replace("/", ""), curEMonth = txtCurEMonth.Text.Replace("/", ""), sMonth = txtSMonth.Text.Replace("/", ""), eMonth = txtEMonth.Text.Replace("/", ""); decimal natureSdt = txtMultiNature.Text.AsDecimal() / 100; decimal legalSdt = txtMultiLegal.Text.AsDecimal() / 100; //讀取資料 showMsg = "讀取資料錯誤"; DataTable dt30202 = dao30202.d_30202(cpYmd, sMonth, eMonth, curSMonth, curEMonth, natureSdt, legalSdt); if (dt30202.Rows.Count == 0) { MessageDisplay.Info(eMonth + "," + rptId + '-' + rptName + ",無任何資料!"); lblProcessing.Visible = false; return(ResultStatus.Fail); } ShowMsg(rptId + '-' + rptName + " 轉檔中..."); //複製檔案 showMsg = "複製檔案錯誤"; file = PbFunc.wf_copy_file(rptId, rptId); if (file == "") { return(ResultStatus.Fail); } //開啟檔案 showMsg = "開啟檔案錯誤"; Workbook workbook = new Workbook(); workbook.LoadDocument(file); //切換Sheet showMsg = "切換Sheet錯誤"; Worksheet ws30202 = workbook.Worksheets[0]; //寫入資料 showMsg = "寫入資料錯誤"; int rowIndex = 2; lblProcessing.Text = rptId + "-" + rptName + " 轉檔中..."; #region wf_30202 string str, natureType, legalType, col; decimal value1, changeRange, curNature, curLegal, cpNature, cpLegal, nature, legal; if (!cbxDB.Checked) { ws30202.Cells[0, 0].Value = ws30202.Cells[0, 0].Value.AsString() + "(試算)"; } //(一) ws30202.Cells[3, 1].Value = "(" + txtSMonth.Text + "/01" + "~" + txtPrevEymd.Text + ")"; //(二) ws30202.Cells[3, 3].Value = "(" + txtCurSMonth.Text + "/01" + "~" + txtCurEymd.Text + ")"; //(六) str = txtCurEMonth.Text; DateTime date = (str + "/01").AsDateTime("yyyy/MM/dd"); do { date = PbFunc.relativedate(date, date.Day * -1); str = str + "、" + date.ToString("yyyy/MM~") + txtCurEMonth.Text; } while ((date.ToString("yyyy/MM") + "/01").AsDateTime("yyyy/MM/dd") > txtSMonth.DateTimeValue);//只比年月 ws30202.Cells[3, 12].Value = str; foreach (DataRow dr in dt30202.Rows) { rowIndex = dr["RPT_SEQ_NO"].AsInt() - 1; decimal pAvgQnty = dr["P_AVG_QNTY"].AsDecimal(); decimal pAvgOi = dr["P_AVG_OI"].AsDecimal(); //前次檢視之數值 ws30202.Cells[rowIndex, 1].Value = pAvgQnty; ws30202.Cells[rowIndex, 2].Value = pAvgOi; //本次檢視之數值 ws30202.Cells[rowIndex, 3].SetValue(dr["C_AVG_QNTY"]); ws30202.Cells[rowIndex, 4].SetValue(dr["C_AVG_OI"]); //相較前次數值增減幅度 if (pAvgQnty > pAvgOi) { value1 = pAvgQnty; } else { value1 = pAvgOi; } if (value1 == 0) { changeRange = -1; } else { changeRange = Math.Round(dr["C_MAX_VALUE"].AsDecimal() / value1 - 1, 4, MidpointRounding.AwayFromZero); } ws30202.Cells[rowIndex, 5].Value = changeRange; dr["CHANGE_RANGE"] = changeRange; //現行部位限制數 curNature = dr["PL2_NATURE"] == DBNull.Value ? -1 : dr["PL2_NATURE"].AsDecimal(); //當該欄位的值為DBNull時等於-1 (權宜做法) curLegal = dr["PL2_LEGAL"] == DBNull.Value ? -1 : dr["PL2_LEGAL"].AsDecimal(); //當該欄位的值為DBNull時等於-1 (權宜做法) if (dr["PL2_NATURE"] != DBNull.Value) { ws30202.Cells[rowIndex, 6].Value = curNature; } if (dr["PL2_LEGAL"] != DBNull.Value) { ws30202.Cells[rowIndex, 7].Value = curLegal; } //按交易規則檢視後之部位限制數 //自然人 value1 = dr["PLT1_T1_MULTIPLE"] == DBNull.Value ? -1 : dr["PLT1_T1_MULTIPLE"].AsDecimal();//當該欄位的值為DBNull時等於-1 (權宜做法) if (dr["PLT1_T1_MIN_NATURE"] == DBNull.Value) { cpNature = dr["C_MAX_VALUE"].AsDecimal() * natureSdt; if (value1 > 0) { cpNature = Math.Truncate(cpNature / value1) * value1; } } else { cpNature = dr["PLT1_T1_MIN_NATURE"].AsDecimal(); } ws30202.Cells[rowIndex, 9].Value = cpNature; dr["CP_NATURE"] = cpNature; //法人 value1 = dr["PLT1_T2_MULTIPLE"] == DBNull.Value ? -1 : dr["PLT1_T2_MULTIPLE"].AsDecimal();//當該欄位的值為DBNull時等於-1 (權宜做法) if (dr["PLT1_T2_MIN_LEGAL"] == DBNull.Value) { cpLegal = dr["C_MAX_VALUE"].AsDecimal() * legalSdt; if (value1 > 0) { cpLegal = Math.Truncate(cpLegal / value1) * value1; } } else { cpLegal = dr["PLT1_T2_MIN_LEGAL"].AsDecimal(); } ws30202.Cells[rowIndex, 10].Value = cpLegal; dr["CP_LEGAL"] = cpLegal; dr["CP_999"] = cpLegal * 3; //近1~6月日均交易量與未沖銷量 /**************************** * 1. 檢視部位限制級距時,若該期間之每日平均交易量或未沖銷量與前次調整時相較,其增減未逾百分之二‧五時,雖達調整級距標準,仍不調整。 * 2.針對須降低部位限制數之商品再增加以最近1、2、4、5、6個月區間資料檢視,並取其數額大者為基準數,惟計算後不得超過前次之部位限制數。 ****************************/ //(isnull(ld_cur_nature) and isnull(ld_cur_legal))這個條件拿掉,因為不會成立 if (Math.Abs(changeRange) <= 0.025m || (curNature == -1 && curLegal == -1) || (curNature == cpNature && curLegal == cpLegal)) { str = "不適用"; if (curNature != -1 && curLegal != -1) { nature = curNature; legal = curLegal; } else { nature = cpNature; legal = cpLegal; } natureType = "不變"; legalType = "不變"; } else { if (cpNature < curNature || cpLegal < curLegal) { col = "max"; //最大者 } else { col = "min"; //最小者 } str = "近" + dr[col + "_MONTH_SEQ_NO"].AsString() + "個月"; if (dr[col + "_TYPE"].AsString() == "OI") { str = str + "未沖銷量"; } else { str = str + "交易量"; } if (col == "max") { str = str + "最大者"; } else { str = str + "最小者"; } str = str + "(" + dr[col + "_VALUE"].AsDecimal().ToString("#,##0") + ")"; //自然人 value1 = dr["PLT1_R1_MULTIPLE"] == DBNull.Value ? -1 : dr["PLT1_R1_MULTIPLE"].AsDecimal();//當該欄位的值為DBNull時等於-1 (權宜做法) if (dr["PLT1_R1_MIN_NATURE"] == DBNull.Value) { nature = dr[col + "_VALUE"].AsDecimal() * natureSdt; if (value1 > 0) { nature = Math.Truncate(nature / value1) * value1; } } else { nature = dr["PLT1_R1_MIN_NATURE"].AsDecimal(); } natureType = "不變"; if (nature < curNature) { natureType = "降低"; } else if (nature > curNature) { natureType = "調高"; } //法人 value1 = dr["PLT1_R2_MULTIPLE"] == DBNull.Value ? -1 : dr["PLT1_R2_MULTIPLE"].AsDecimal();//當該欄位的值為DBNull時等於-1 (權宜做法) if (dr["PLT1_R2_MIN_LEGAL"] == DBNull.Value) { legal = dr[col + "_VALUE"].AsDecimal() * legalSdt; if (value1 > 0) { legal = Math.Truncate(legal / value1) * value1; } } else { legal = dr["PLT1_R2_MIN_LEGAL"].AsDecimal(); } legalType = "不變"; if (legal < curLegal) { legalType = "降低"; } else if (legal > curLegal) { legalType = "調高"; } } ws30202.Cells[rowIndex, 12].Value = str; ws30202.Cells[rowIndex, 14].Value = nature; ws30202.Cells[rowIndex, 15].Value = legal; ws30202.Cells[rowIndex, 16].Value = natureType; ws30202.Cells[rowIndex, 17].Value = legalType; dr["NATURE"] = nature; dr["LEGAL"] = legal; dr["P999"] = legal * 3; switch (natureType) { case "降低": str = "-"; break; case "調高": str = "+"; break; default: str = " "; break; } dr["NATURE_ADJ"] = str; switch (legalType) { case "降低": str = "-"; break; case "調高": str = "+"; break; default: str = " "; break; } dr["LEGAL_ADJ"] = str; dr["P999_ADJ"] = str; dr["YMD"] = cpYmd; //針對須調降之商品再增加檢視標準後之部位限制數 }//foreach (DataRow dr in dt30202.Rows) //表尾 rowIndex = dao30202.row_index(); if (rowIndex > 0) { ws30202.Cells[rowIndex - 1, 0].Value = ws30202.Cells[rowIndex - 1, 0].Value.ToString() + "自然人乘以" + txtMultiNature.Text + "%,法人乘以" + txtMultiLegal.Text + "%)"; } //存檔 ws30202.ScrollToRow(0); workbook.SaveDocument(file); ShowMsg("轉檔成功"); #endregion if (!cbxDB.Checked) { return(ResultStatus.Success); } #region wf_30202_write bool dbCommit = false; //刪除PL0的資料 showMsg = "PL0刪除失敗"; dbCommit = dao30202.DeletePL0ByDate(cpYmd); if (!dbCommit) { MessageDisplay.Error(showMsg); return(ResultStatus.Fail); } //新增PL0的資料 showMsg = "PL0新增失敗"; dbCommit = dao30202.InsertPL0(cpYmd, sMonth, eMonth, curSMonth, curEMonth, GlobalInfo.USER_ID); if (!dbCommit) { MessageDisplay.Error(showMsg); return(ResultStatus.Fail); } //刪除PL2的資料 showMsg = "PL2刪除失敗"; dbCommit = dao30202.DeletePL2ByDate(cpYmd); if (!dbCommit) { MessageDisplay.Error(showMsg); return(ResultStatus.Fail); } //刪除PL1的資料 showMsg = "PL1刪除失敗"; dbCommit = dao30202.DeletePL1ByDate(cpYmd); if (!dbCommit) { MessageDisplay.Error(showMsg); return(ResultStatus.Fail); } //寫入 dtPL1.Clear(); dtPL1.AcceptChanges(); for (int f = 0; f < dt30202.Rows.Count; f++) { dtPL1.Rows.Add(); for (int g = 0; g < 24; g++) { dtPL1.Rows[dtPL1.Rows.Count - 1][g] = dt30202.Rows[f][g]; //現行自然人,現行法人PL if (g == 9 || g == 11) { if (dt30202.Rows[f][g] == DBNull.Value) { dtPL1.Rows[dtPL1.Rows.Count - 1][g] = 0; } } } dtPL1.Rows[dtPL1.Rows.Count - 1][24] = DateTime.Now; dtPL1.Rows[dtPL1.Rows.Count - 1][25] = GlobalInfo.USER_ID; } try { ResultData myResultData = dao30202.updatePL1(dtPL1); } catch (Exception ex) { MessageDisplay.Error("計算結果新增至資料庫錯誤! "); WriteLog(ex, "", false); return(ResultStatus.Fail); } #endregion } catch (Exception ex) { MessageDisplay.Error(showMsg); throw ex; } finally { this.Cursor = Cursors.Arrow; this.Refresh(); Thread.Sleep(5); } return(ResultStatus.Success); }