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