/// <summary>
/// 顯示選取條件
/// </summary>
/// <returns></returns>
private string ConditionText()
{
string lsText;
/*******************
* 顯示條件
*******************/
if (gbMarket.EditValue.Equals("rb_market_1"))
{
lsText = "盤後交易時段";
}
else
{
lsText = "一般交易時段";
}
if (!string.IsNullOrEmpty(_D500Xx.Sbrkno) || !string.IsNullOrEmpty(_D500Xx.Ebrkno))
{
lsText = lsText + ",造市者:";
if (_D500Xx.Sbrkno == _D500Xx.Ebrkno)
{
lsText = lsText + _D500Xx.Sbrkno + " ";
}
else
{
lsText = lsText + _D500Xx.Sbrkno + "～" + _D500Xx.Ebrkno + " ";
}
}
if (!string.IsNullOrEmpty(_D500Xx.ProdCategory))
{
lsText = lsText + ",商品群組:" + _D500Xx.ProdCategory;
}
if (!string.IsNullOrEmpty(_D500Xx.ProdKindIdSto))
{
lsText = lsText + ",2碼商品(個股):" + _D500Xx.ProdKindIdSto;
}
if (!string.IsNullOrEmpty(_D500Xx.ProdKindId))
{
lsText = lsText + ",造市商品:" + _D500Xx.ProdKindId;
}
if (PbFunc.Left(lsText, 1) == ",")
{
lsText = PbFunc.Mid(lsText, 1, 50);
}
if (!string.IsNullOrEmpty(lsText))
{
lsText = "報表條件：" + lsText;
}
return(lsText);
}
/// <summary>
/// 顯示選取條件
/// </summary>
/// <returns></returns>
private string ConditionText()
{
string lsText;
/*******************
* 顯示條件
*******************/
lsText = "一般交易時段";
if (!string.IsNullOrEmpty(_D500Xx.Sbrkno) || !string.IsNullOrEmpty(_D500Xx.Ebrkno))
{
lsText = lsText + ",造市者:";
if (_D500Xx.Sbrkno == _D500Xx.Ebrkno)
{
lsText = lsText + _D500Xx.Sbrkno + " ";
}
else
{
lsText = lsText + _D500Xx.Sbrkno + "～" + _D500Xx.Ebrkno + " ";
}
}
if (!string.IsNullOrEmpty(_D500Xx.ProdCategory))
{
lsText = lsText + ",商品群組:" + _D500Xx.ProdCategory;
}
if (!string.IsNullOrEmpty(_D500Xx.ProdKindIdSto))
{
lsText = lsText + ",2碼商品(個股):" + _D500Xx.ProdKindIdSto;
}
if (!string.IsNullOrEmpty(_D500Xx.ProdKindId))
{
lsText = lsText + ",造市商品:" + _D500Xx.ProdKindId;
}
if (PbFunc.Left(lsText, 1) == ",")
{
lsText = PbFunc.Mid(lsText, 1, 50);
}
if (!string.IsNullOrEmpty(lsText))
{
lsText = "報表條件：" + lsText;
}
lsText = lsText == "" ? $"報表條件：連續{SleCMth.Text}個月不符造市規定" : lsText + $",連續{SleCMth.Text}個月不符造市規定";
return(lsText);
}
/// <summary>
/// wf_30333()
/// 30398,30399,30393相同
/// </summary>
/// <param name="IsKindID">商品代號</param>
/// <param name="SheetName">工作表</param>
/// <param name="RowIndex">Excel的Row位置</param>
/// <param name="RowTotal">Excel的Column預留數</param>
/// <returns></returns>
public string Wf30333(string IsKindID = "GTF", string SheetName = "data_30398abc", int RowIndex = 3, int RowTotal = 12)
{
try {
//切換Sheet
Worksheet worksheet = _workbook.Worksheets[SheetName];
//總列數
int sumRowIndex = RowTotal + RowIndex + 1; //小計行數
int addRowCount = 0; //總計寫入的行數
worksheet.Rows[sumRowIndex][1 - 1].Value = $"{PbFunc.Left(_emMonthText, 4).AsInt() - 1911}小計";
string lsYMD = "";
//讀取資料
DataTable dt = daoAM2.ListAM2(IsKindID, $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", ""));
//寫入資料
foreach (DataRow row in dt.Rows)
{
if (lsYMD != row["AM2_YMD"].AsString())
{
RowIndex = RowIndex + 1;
lsYMD = row["AM2_YMD"].AsString();
//li_month_cnt = li_month_cnt + 1;
addRowCount++;
worksheet.Rows[RowIndex][1 - 1].Value = $"{PbFunc.Left(lsYMD, 4).AsInt() - 1911}/{PbFunc.Right(lsYMD, 2)}";
}
//判斷欄位
int columnIndex = IDFGtype(row);
worksheet.Rows[RowIndex][columnIndex].Value = row["AM2_M_QNTY"].AsDecimal();//買 or 賣
}
//刪除空白列
if (RowTotal > addRowCount)
{
worksheet.Rows.Hide(RowIndex + 1, RowIndex + (RowTotal - addRowCount)); //隱藏代替刪除，這樣就不用再重新抓取圖表範圍
worksheet.ScrollTo(0, 0); //直接滾動到最上面，不然看起來很像少行數
}
}
catch (Exception ex) {
#if DEBUG
throw new Exception($"Wf30333:" + ex.Message);
#else
throw ex;
#endif
}
return(MessageDisplay.MSG_OK);
}
/// <summary>
/// wf_30541
/// </summary>
/// <param name="RowIndex">Excel的Row位置</param>
/// <param name="RowTotal">Excel的Column預留數</param>
/// <returns></returns>
public string Wf30541(int RowIndex = 6, string RptName = "國內股價指數選擇權交易概況表")
{
Workbook workbook = new Workbook();
try {
//切換Sheet
workbook.LoadDocument(_lsFile);
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1"].Select();
//總列數,隱藏於A5
int rowTol = RowIndex + worksheet.Cells["A5"].Value.AsInt();
//起始年份,隱藏於B5
string firstYear = worksheet.Cells["B5"].Value.AsString();
/******************
* 讀取資料
* 分三段：
* 1.年
* 2.當年1月至當月合計
* 3.當年1月至當月明細
******************/
DataTable dt = new D30540().List30541(firstYear, PbFunc.Left(_emMonthText, 4), $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", ""));
if (dt.Rows.Count <= 0)
{
return($"{firstYear}~{PbFunc.Left(_emMonthText, 4)},{PbFunc.Left(_emMonthText, 4)}01～{_emMonthText.Replace("/", "")},30541－{RptName},無任何資料!");
}
/* 成交量 & OI */
DataTable dtAI2 = new D30540().List30541AI2(firstYear, PbFunc.Left(_emMonthText, 4), $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", ""));
if (dtAI2.Rows.Count <= 0)
{
return($"{firstYear}~{PbFunc.Left(_emMonthText, 4)},{PbFunc.Left(_emMonthText, 4)}01～{_emMonthText.Replace("/", "")},30541－{RptName},無任何資料!");
}
//寫入內容
string lsYMD = "";
int rowEndIndex = 0;
string endYMD = dt.AsEnumerable().LastOrDefault()["AM2_YMD"].AsString();
foreach (DataRow row in dt.Rows)
{
if (lsYMD != row["AM2_YMD"].AsString())
{
lsYMD = row["AM2_YMD"].AsString();
RowIndex = RowIndex + 1;
/* 最後一列時 */
if (lsYMD == endYMD)
{
rowEndIndex = RowIndex;
RowIndex = rowTol;
}
/* 年度資料 */
if (lsYMD.Length == 4)
{
worksheet.Rows[RowIndex][1 - 1].Value = PbFunc.Left(lsYMD, 4).AsInt();
}
else
{
worksheet.Rows[RowIndex][1 - 1].Value = PbFunc.f_get_month_eng_name(PbFunc.Right(lsYMD, 2).AsInt(), "1");
}//if (lsYMD.Length==4)
/* 成交量 & OI */
//ai2_pc_code=C
OImethod(RowIndex, worksheet, dtAI2, lsYMD, "C");
//ai2_pc_code=P
OImethod(RowIndex, worksheet, dtAI2, lsYMD, "P");
}//if (lsYMD != row["AM2_YMD"].AsString())
//判斷欄位
int columnIndex = IDFGtype(row);
if (row["AM2_YMD"].AsString() != endYMD)
{
worksheet.Rows[RowIndex][columnIndex].Value = row["AM2_M_QNTY"].AsDecimal();
}
else
{
worksheet.Rows[rowTol][columnIndex].Value = row["AM2_M_QNTY"].AsDecimal();
}
}//foreach (DataRow row in dt.Rows)
//刪除空白列
if (rowTol > rowEndIndex)
{
worksheet.Range[$"{rowEndIndex + 1}:{rowTol + 1 - 1}"].Delete(DeleteMode.EntireRow);
worksheet.ScrollTo(0, 0);//直接滾動到最上面，不然看起來很像少行數
}
}
catch (Exception ex) {
#if DEBUG
throw new Exception($"wf30541:" + ex.Message);
#else
throw ex;
#endif
}
finally {
workbook.SaveDocument(_lsFile);
}
return(MessageDisplay.MSG_OK);
}
protected override ResultStatus Export()
{
base.Export();
string ls_rpt_name = "造市者報表";
string ls_rpt_id = _ProgramID;
StartExport(ls_rpt_id, ls_rpt_name);
StartRetrieve("0000000", "Z999999");
_D500Xx.Filename = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH, _ProgramID + "_" + DateTime.Now.ToString("yyyy.MM.dd") + "-" + DateTime.Now.ToString("HH.mm.ss") + ".csv");
try {
/* 商品群組 */
string isKey = dwProdCt.SelectedText.Trim() + "%";
if (string.IsNullOrEmpty(isKey) || dwProdCt.Enabled == false)
{
isKey = "%";
}
/* 商品 */
string kindID2 = dwProdKdSto.SelectedText.Trim() + "%";
if (string.IsNullOrEmpty(kindID2) || dwProdKdSto.Enabled == false)
{
kindID2 = "%";
}
/*******************
* 統計子類別
*******************/
string prodSubtype = "%";
string sumSubtype = "";
if (gbGroup.EditValue.Equals("rb_gparam"))
{
sumSubtype = "3";
}
else if (gbGroup.EditValue.Equals("rb_s"))
{
sumSubtype = "S";
prodSubtype = "S";
}
else if (gbGroup.EditValue.Equals("rb_gkind2"))
{
sumSubtype = "4";
}
/*******************
* 交易時段
*******************/
int marketCode = 0;
if (gbMarket.EditValue.Equals("rb_market_1"))
{
marketCode = 1;
}
if (gbMarket.EditValue.Equals("rb_market_0"))
{
marketCode = 0;
}
/*******************
* 報表內容
*******************/
string detailType = "";
if (gbDetial.EditValue.Equals("rb_gdate"))
{
detailType = "D";
}
if (gbDetial.EditValue.Equals("rb_gnodate"))
{
detailType = "N";
}
/*******************
* 顯示條件
*******************/
string lsText = "";
if (gbMarket.EditValue.Equals("rb_market_1"))
{
lsText = "盤後交易時段";
}
else
{
lsText = "一般交易時段";
}
if (!string.IsNullOrEmpty(_D500Xx.Sdate))
{
lsText = lsText + "(" + _D500Xx.Sdate;
}
if (!string.IsNullOrEmpty(_D500Xx.Edate))
{
lsText = lsText + "~" + _D500Xx.Edate + ")";
}
if (_D500Xx.Sbrkno != "%" || _D500Xx.Ebrkno != "%")
{
lsText = lsText + ",造市者:";
if (_D500Xx.Sbrkno == _D500Xx.Ebrkno)
{
lsText = lsText + _D500Xx.Sbrkno + " ";
}
else
{
lsText = lsText + _D500Xx.Sbrkno + "~" + _D500Xx.Ebrkno + " ";
}
}
if (isKey != "%")
{
lsText = lsText + ",商品群組:" + isKey;
}
if (kindID2 != "%")
{
lsText = lsText + ",2碼商品(個股):" + kindID2;
}
if (PbFunc.Left(lsText, 1) == ",")
{
lsText = PbFunc.Mid(lsText, 1, 50);
}
if (lsText != "")
{
lsText = "報表條件：" + lsText;
}
/******************
* 讀取資料
******************/
/* 報表內容 */
_Data = dao50031.ListD50031
(marketCode, sumSubtype, detailType, _D500Xx.Sdate, _D500Xx.Edate,
_D500Xx.Sbrkno, _D500Xx.Ebrkno, isKey, prodSubtype, kindID2, lsText);
if (_Data.Rows.Count <= 0)
{
MessageDisplay.Info(MessageDisplay.MSG_NO_DATA);
return(ResultStatus.Fail);
}
/******************
* 開啟檔案
******************/
Workbook workbook = new Workbook();
//判斷檔案是否存在,不存在就開一個新檔案
if (!File.Exists(_D500Xx.Filename))
{
File.Create(_D500Xx.Filename).Close();
}
workbook.LoadDocument(_D500Xx.Filename);
/******************
* 切換Sheet
******************/
Worksheet worksheet = workbook.Worksheets[0];
workbook.Options.Export.Csv.WritePreamble = true; //不加這段中文會是亂碼
//將DataTable中的數據導入Excel中
worksheet.Import(_Data, false, 0, 0);
workbook.SaveDocument(_D500Xx.Filename, DocumentFormat.Csv);
}
catch (Exception ex) {
WfRunError();
WriteLog(ex);
}
finally {
EndExport();
}
return(ResultStatus.Success);
}
//protected override ResultStatus Run(PokeBall args)
//{
// this.BeginInvoke(new MethodInvoker(() => {
// args.GridControlMain = gcMain;
// args.GridControlSecond = gcLogsp;
// args.OcfDate = txtOcfDate.DateTimeValue;
// args.OcfType = OCF_TYPE;
// }));
// ResultStatus result = base.RunAsync(args);
// return result;
//}
/// <summary>
/// 1系列功能使用
/// </summary>
/// <param name="args"></param>
/// <returns></returns>
protected override ResultStatus Run(PokeBall args)
{
DateTime OcfDate = txtOcfDate.DateTimeValue;
this.Invoke(new MethodInvoker(() => {
FormWait formWait = new FormWait();
SplashScreenManager.ShowForm(this, typeof(FormWait), true, true);
//SplashScreenManager.ShowForm(this , typeof(FormWait) , true , true , SplashFormStartPosition.Manual , pointWait , ParentFormState.Locked);
}));
GridView gv = gvMain;
DataTable dtLOGSPForRuned = servicePrefix1.ListLogspForRunned(OcfDate, _ProgramID, OCF_TYPE);
DataView dvLOGSPForRuned = new DataView(dtLOGSPForRuned);
servicePrefix1.SetTXF1(" ", _ProgramID);
for (int i = 0; i < gv.RowCount; i++)
{
string TXF_SERVER = gv.GetRowCellValue(i, "TXF_SERVER").AsString();
string TXF_DB = gv.GetRowCellValue(i, "TXF_DB").AsString();
string TXF_TXN_ID = gv.GetRowCellValue(i, "TXF_TXN_ID").AsString();
int TXF_SEQ_NO = gv.GetRowCellValue(i, "TXF_SEQ_NO").AsInt();
string TXF_TYPE = gv.GetRowCellValue(i, "TXF_TYPE").AsString();
string TXF_TID = gv.GetRowCellValue(i, "TXF_TID").AsString();
string TXF_TID_NAME = gv.GetRowCellValue(i, "TXF_TID_NAME").AsString();
string TXF_DESC = gv.GetRowCellValue(i, "TXF_DESC").AsString();
string TXF_DEFAULT = gv.GetRowCellValue(i, "TXF_DEFAULT").AsString();
string TXF_REDO = gv.GetRowCellValue(i, "TXF_REDO").AsString();
string TXF_ARG = gv.GetRowCellValue(i, "TXF_ARG").AsString();
string TXF_PERIOD = gv.GetRowCellValue(i, "TXF_PERIOD").AsString();
string TXF_SERVICE = gv.GetRowCellValue(i, "TXF_SERVICE").AsString();
string TXF_FOLDER = gv.GetRowCellValue(i, "TXF_FOLDER").AsString();
string TXF_AP_NAME = gv.GetRowCellValue(i, "TXF_AP_NAME").AsString();
args.TXF_TID = TXF_TID;
args.TXF_TID_NAME = TXF_TID_NAME;
if (TXF_DEFAULT == "1")
{
DateTime LOGSP_DATE = OcfDate;
string LOGSP_TXN_ID = _ProgramID;
int LOGSP_SEQ_NO = TXF_SEQ_NO;
string LOGSP_TID = TXF_DESC;
string LOGSP_TID_NAME = TXF_TID_NAME;
DateTime LOGSP_BEGIN_TIME = new DateTime();
DateTime LOGSP_END_TIME = new DateTime();
string LOGSP_MSG = "";
//判斷是否可重覆執行
if (TXF_REDO == "N")
{
dvLOGSPForRuned.RowFilter = "LOGSP_TID='" + LOGSP_TID + "' AND NOT ISNULL(LOGSP_BEGIN_TIME)";
if (dvLOGSPForRuned.Count != 0)
{
if (MessageDisplay.Choose(TXF_TID + " ★★★曾經執行過且不可重覆執行，是否強迫繼續執行 ?") == DialogResult.No)
{
return(ResultStatus.Fail);
}
}
}
#region 開始執行
LOGSP_BEGIN_TIME = DateTime.Now;
string nextYmd = PbFunc.f_ocf_date(2, _DB_TYPE);
if (!string.IsNullOrEmpty(TXF_PERIOD))
{
switch (TXF_PERIOD)
{
case "M": //月底執行
if (OcfDate.ToString("yyyyMM") == PbFunc.Left(nextYmd, 6))
{
LOGSP_MSG = "完成! (今日非月底，不需執行)";
this.Invoke(new MethodInvoker(() => { gv.SetRowCellValue(i, "TXF_DEFAULT", 0); }));
}
break;
case "W": //週最後一天執行
if (Convert.ToInt32(OcfDate.DayOfWeek) < Convert.ToInt32(nextYmd.AsDateTime("yyyyMMdd").DayOfWeek))
{
LOGSP_MSG = "完成! (今日非本週最後1天，不需執行)";
this.Invoke(new MethodInvoker(() => { gv.SetRowCellValue(i, "TXF_DEFAULT", 0); }));
}
break;
case "Y": //年底執行
if (OcfDate.ToString("yyyy") == PbFunc.Left(nextYmd, 4))
{
LOGSP_MSG = "完成! (今日非本年度最後1日，不需執行)";
this.Invoke(new MethodInvoker(() => { gv.SetRowCellValue(i, "TXF_DEFAULT", 0); }));
}
break;
}
LOGSP_END_TIME = DateTime.Now;
servicePrefix1.SaveLogsp(LOGSP_DATE, LOGSP_TXN_ID, LOGSP_SEQ_NO, LOGSP_TID, LOGSP_TID_NAME, LOGSP_BEGIN_TIME, LOGSP_END_TIME, LOGSP_MSG, OCF_TYPE);
continue;
}
//開始前執行特別的Function
string rtnText = RunBeforeEveryItem(args);
if (!string.IsNullOrEmpty(rtnText))
{
if (PbFunc.Left(rtnText, 4) == "不需執行")
{
LOGSP_MSG = "完成! (" + rtnText + ")";
gv.SetRowCellValue(i, "ERR_MSG", LOGSP_MSG);
gv.SetRowCellValue(i, "TXF_DEFAULT", 0);
LOGSP_END_TIME = DateTime.Now;
servicePrefix1.SaveLogsp(LOGSP_DATE, LOGSP_TXN_ID, LOGSP_SEQ_NO, LOGSP_TID, LOGSP_TID_NAME, LOGSP_BEGIN_TIME, LOGSP_END_TIME, LOGSP_MSG, OCF_TYPE);
}
else
{
if (MessageDisplay.Choose($"{rtnText}是否強迫繼續執行?", MessageBoxDefaultButton.Button2).AsInt() == 2)
{
gv.SetRowCellValue(i, "ERR_MSG", rtnText);
continue;
}
}
}
//記錄正在執行
servicePrefix1.SetTXF1(TXF_TID, _ProgramID);
servicePrefix1.SaveLogs(LOGSP_DATE, TXF_TID, DateTime.Now, GlobalInfo.USER_ID, "開始執行");
ResultData resultData = new ResultData();
string fileName = "";
switch (TXF_TYPE)
{
//Informatica
case "I":
fileName = $@"{GlobalInfo.DEFAULT_BATCH_ErrSP_DIRECTORY_PATH}\{TXF_SERVER}_{TXF_TXN_ID}_{TXF_SEQ_NO}_infor";
resultData = serviceCommon.ExecuteInfoWorkFlow(TXF_TID, UserProgInfo, TXF_FOLDER, TXF_SERVICE, TXF_AP_NAME, fileName);
break;
//SP
case "S":
List <DbParameterEx> listParams = null;
// 如果這個SP有參數的話
if (TXF_ARG == "Y")
{
DataTable dtTXFPARM = serviceCommon.ListTXFPARM(TXF_SERVER, TXF_DB, TXF_TXN_ID, TXF_TID);
if (dtTXFPARM.Rows.Count > 0)
{
listParams = new List <DbParameterEx>();
}
foreach (DataRow row in dtTXFPARM.Rows)
{
string TXFPARM_ARG = row["TXFPARM_ARG"].AsString();
string TXFPARM_ARG_TYPE = row["TXFPARM_ARG_TYPE"].AsString();
string TXFPARM_DEFAULT = row["TXFPARM_DEFAULT"].AsString();
DbParameterEx paramEx;
switch (TXFPARM_ARG)
{
case "":
paramEx = new DbParameterEx("", TXFPARM_DEFAULT);
listParams.Add(paramEx);
break;
case "em_ymd":
paramEx = new DbParameterEx("", OcfDate.ToString("yyyyMMdd"));
listParams.Add(paramEx);
break;
case "em_ym":
paramEx = new DbParameterEx("", OcfDate.ToString("yyyyMM"));
listParams.Add(paramEx);
break;
case "em_date":
paramEx = new DbParameterEx();
paramEx.DbType = DbTypeEx.Date;
paramEx.Name = "";
paramEx.Value = OcfDate;
listParams.Add(paramEx);
break;
default:
break;
}
}
}
ConnectionInfo connectionInfo = SettingDragons.Instance.GetConnectionInfo(TXF_DB);
try
{
resultData = serviceCommon.ExecuteStoredProcedure(connectionInfo, string.Format("{0}.{1}", TXF_DB, TXF_TID), listParams, true);
}
catch (Exception ex)
{
resultData.Status = ResultStatus.Fail;
string msg =
fileName = $@"{GlobalInfo.DEFAULT_BATCH_ErrSP_DIRECTORY_PATH}\{TXF_SERVER}_{TXF_TXN_ID}_{TXF_SEQ_NO}.err";
System.IO.File.WriteAllText(fileName, ex.Message);
resultData.returnString = $"請通知「{TXF_AP_NAME}」 作業執行失敗!\n{ex.Message}";
}
break;
//視窗功能
case "W":
this.Invoke(new MethodInvoker(() => { resultData = ExecuteForm(args); }));
break;
default:
break;
}
LOGSP_END_TIME = DateTime.Now;
if (resultData.Status == ResultStatus.Success)
{
LOGSP_MSG = "執行正常完成!";
}
else
{
LOGSP_MSG = "作業執行失敗!";
servicePrefix1.SaveLogsp(LOGSP_DATE, LOGSP_TXN_ID, LOGSP_SEQ_NO, LOGSP_TID, LOGSP_TID_NAME, LOGSP_BEGIN_TIME, LOGSP_END_TIME, LOGSP_MSG, OCF_TYPE);
//MessageDisplay.Error("序號" + LOGSP_SEQ_NO + "的" + LOGSP_TID + "," + LOGSP_MSG);
MessageDisplay.Error(resultData.returnString);
this.Invoke(new MethodInvoker(() => {
SplashScreenManager.CloseForm();
gv.SetRowCellValue(i, "ERR_MSG", LOGSP_MSG);
}));
return(ResultStatus.Fail);
}
this.Invoke(new MethodInvoker(() => { gv.SetRowCellValue(i, "ERR_MSG", LOGSP_MSG); }));
servicePrefix1.SaveLogsp(LOGSP_DATE, LOGSP_TXN_ID, LOGSP_SEQ_NO, LOGSP_TID, LOGSP_TID_NAME, LOGSP_BEGIN_TIME, LOGSP_END_TIME, LOGSP_MSG, OCF_TYPE);
servicePrefix1.SaveLogs(LOGSP_DATE, TXF_TID, DateTime.Now, GlobalInfo.USER_ID, "執行完畢");
#endregion 開始執行
#region 執行特別的程式
this.Invoke(new MethodInvoker(() => {
gv.SetRowCellValue(i, "TXF_DEFAULT", 0);
RunAfterEveryItem(args);
}));
#endregion 執行特別的程式
//流程時間控制
DataTable dtJRF = servicePrefix1.ListJrf(_ProgramID, TXF_TID);
if (dtJRF.Rows.Count > 0)
{
string JRF_DO_TXN_ID = dtJRF.Rows[0]["JRF_DO_TXN_ID"].AsString();
string JRF_DO_JOB_TYPE = dtJRF.Rows[0]["JRF_DO_TXN_ID"].AsString();
string JRF_DO_SEQ_NO = dtJRF.Rows[0]["JRF_DO_TXN_ID"].AsString();
string JRF_SW_CODE = dtJRF.Rows[0]["JRF_DO_TXN_ID"].AsString();
servicePrefix1.UpdateJsw(JRF_DO_TXN_ID, JRF_DO_JOB_TYPE, JRF_DO_SEQ_NO, JRF_SW_CODE, OcfDate, DateTime.Now, GlobalInfo.USER_ID);
}
}
else
{
// 沒勾選項目的話清空狀態
this.Invoke(new MethodInvoker(() => { gv.SetRowCellValue(i, "ERR_MSG", ""); }));
}
if (i == gv.RowCount - 1 && OCF_TYPE == "D")
{
servicePrefix1.setCIOCF();
}
}
//全部結束
servicePrefix1.SetTXF1(" ", _ProgramID);
this.Invoke(new MethodInvoker(() => { SplashScreenManager.CloseForm(); }));
return(ResultStatus.Success);
}
/// <summary>
/// 寫入 Data_30333.30334 sheet
/// </summary>
/// <returns></returns>
public string Wf30333()
{
Workbook workbook = new Workbook();
try {
string SheetName = "Data_30333.30334";
string lsKindID = "GBF";
//切換Sheet
workbook.LoadDocument(_lsFile);
Worksheet worksheet = workbook.Worksheets[SheetName];
DateTime ldtYMD = new DateTime(1900, 1, 1);
//總列數
int rowIndex = 3; //int li_month_cnt=0;
int RowTotal = 12; //Excel的Column預留空白行數12
int sumRowIndex = RowTotal + rowIndex + 1; //小計行數
int addRowCount = 0; //總計寫入的行數
worksheet.Rows[sumRowIndex][1 - 1].Value = $"{PbFunc.Left(_emMonthText, 4).AsInt() - 1911}小計";
string lsYMD = "";
//讀取資料
DataTable dt = new AM2().ListAM2(lsKindID, $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", ""));//月份
foreach (DataRow row in dt.Rows)
{
if (lsYMD != row["AM2_YMD"].AsString())
{
rowIndex = rowIndex + 1;
lsYMD = row["AM2_YMD"].AsString();
//li_month_cnt = li_month_cnt + 1;
addRowCount++;
//月份
worksheet.Rows[rowIndex][1 - 1].Value = $"{PbFunc.Left(lsYMD, 4).AsInt() - 1911}/{PbFunc.Right(lsYMD, 2)}";
}
//判斷買賣欄位位置
int columnIndex = 0;
switch (row["AM2_IDFG_TYPE"].AsString())
{
case "1":
columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 2 : 3) - 1;
break;
case "2":
columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 4 : 5) - 1;
break;
case "3":
columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 6 : 7) - 1;
break;
case "5":
columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 8 : 9) - 1;
break;
case "6":
columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 10 : 11) - 1;
break;
case "8":
columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 12 : 13) - 1;
break;
case "7":
columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 14 : 15) - 1;
break;
}
//columnIndex指定要寫入的買賣欄位
worksheet.Rows[rowIndex][columnIndex].Value = row["AM2_M_QNTY"].AsDecimal();
}
//刪除空白列
if (RowTotal > addRowCount)
{
worksheet.Rows.Remove(rowIndex + 1, RowTotal - addRowCount);
}
worksheet.ScrollTo(0, 0);
}
catch (Exception ex) {
throw ex;
}
finally {
workbook.SaveDocument(_lsFile);
}
return(MessageDisplay.MSG_OK);
}
/// <summary>
/// wf_30371()
/// </summary>
/// <param name="RowIndex">Excel的Row位置</param>
/// <param name="SheetName">工作表</param>
/// <param name="RptName">作業名稱</param>
/// <returns></returns>
public string Wf30371(int RowIndex = 3, string SheetName = "30371", string RptName = "年度期間法人機構期貨交易量統計表")
{
Workbook workbook = new Workbook();
try {
//切換Sheet
workbook.LoadDocument(_lsFile);
Worksheet worksheet = workbook.Worksheets[SheetName];
//總列數,隱藏於A1
int rowTol = RowIndex + worksheet.Cells["A1"].Value.AsInt();
int rowYtolIndex = rowTol;
//起始年份,隱藏於B1
string firstYear = worksheet.Cells["B1"].Value.AsString();
/******************
* 讀取資料
* 分三段：
* 1.年
* 2.當年1月至當月明細
* 3.當年1月至當月合計
******************/
DataTable dt = dao30370.Get30371Data(firstYear, PbFunc.Left(_emMonthText, 4), $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", ""));
if (dt.Rows.Count <= 0)
{
return($"{firstYear}~{PbFunc.Left(_emMonthText, 4)},{PbFunc.Left(_emMonthText, 4)}01～{_emMonthText.Replace("/", "")},{SheetName}－{RptName},無任何資料!");
}
string endYMD = dt.AsEnumerable().LastOrDefault()["AM2_YMD"].AsString();
/* 不等於起始年,則從第2個年開始 */
if (dt.Rows[0]["AM2_YMD"].AsString() != firstYear)
{
//刪除兩列
worksheet.Range[$"{RowIndex + 1}:{RowIndex + 2}"].Delete();
rowYtolIndex = rowYtolIndex - 2;
}
int monthCnt = 0;
string lsYMD = "";
foreach (DataRow row in dt.Rows)
{
if (lsYMD != row["AM2_YMD"].AsString())
{
if (lsYMD.Length == 4)
{
/* 年與年中間跳2 row */
RowIndex = RowIndex + 1;
/* 年部份結束,則把"年"空白列刪除 */
if (row["AM2_YMD"].AsString().Length != 4)
{
try {
//worksheet.Rows.Hide(RowIndex + 1, rowYtolIndex);
worksheet.Range[$"{RowIndex + 2}:{rowYtolIndex + 1}"].Delete();
}
catch (Exception ex) {
return(ex.Message + Environment.NewLine + "30370template[B1]每新增一個年份[A1]要加3");
}
rowTol = RowIndex + 1 + 12;//12個月份
}
}
RowIndex = RowIndex + 1;
lsYMD = row["AM2_YMD"].AsString();
if (lsYMD.Length == 4)
{
if (lsYMD != endYMD)
{
worksheet.Rows[RowIndex][1 - 1].Value = $"{PbFunc.Left(lsYMD, 4).AsInt() - 1911}小計";
}
else
{
worksheet.Rows[rowTol][1 - 1].Value = $"{PbFunc.Left(lsYMD, 4).AsInt() - 1911}小計";//這在倒數第二行
}
}
else
{
monthCnt++;//li_month_cnt = li_month_cnt + 1
worksheet.Rows[RowIndex][1 - 1].Value = $"{PbFunc.Left(lsYMD, 4).AsInt() - 1911}/{PbFunc.Right(lsYMD, 2)}";
}
}//if (lsYMD != row["AM2_YMD"].AsString())
//判斷欄位
int columnIndex = IDFGtype(row);
if (row["AM2_YMD"].AsString() != endYMD)
{
worksheet.Rows[RowIndex][columnIndex].Value = row["AM2_M_QNTY"].AsDecimal();
}
else
{
worksheet.Rows[rowTol][columnIndex].Value = row["AM2_M_QNTY"].AsDecimal();//這在倒數第二行
}
}//foreach (DataRow row in dt.Rows)
//刪除剩餘空白列
try {
if (rowTol > monthCnt)
{
worksheet.Range[$"{RowIndex + 1}:{RowIndex + 12 - monthCnt}"].Delete();
}
}
catch (Exception ex) {
throw ex;
}
}
catch (Exception ex) {
throw ex;
}
finally {
workbook.SaveDocument(_lsFile);
}
return(MessageDisplay.MSG_OK);
}
/// <summary>
/// wf_30521
/// </summary>
/// <param name="RowIndex">Excel的Row位置</param>
/// <param name="RowTotal">Excel的Column預留數</param>
/// <returns></returns>
public string Wf30521(int RowIndex = 4, string RptName = "國內期貨交易概況表")
{
Workbook workbook = new Workbook();
try
{
//切換Sheet
workbook.LoadDocument(_lsFile);
Worksheet worksheet = workbook.Worksheets[0];//30520
//起始年份,隱藏於B3
string firstYear = worksheet.Cells["B3"].Value.AsString();
/******************
* 讀取資料
* 分三段：
* 1.年
* 2.當年1月至當月合計
* 3.當年1月至當月明細
******************/
DataTable dt = dao30520.List30521(firstYear, PbFunc.Left(_emMonthText, 4), $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", ""));
if (dt.Rows.Count <= 0)
{
return($"{firstYear}~{PbFunc.Left(_emMonthText, 4)},{PbFunc.Left(_emMonthText, 4)}01～{_emMonthText.Replace("/", "")},30521－{RptName},無任何資料!");
}
/* 成交量 & OI */
DataTable dtAI2 = dao30520.List30521AI2(firstYear, PbFunc.Left(_emMonthText, 4), $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", ""));
if (dtAI2.Rows.Count <= 0)
{
return($"{firstYear}~{PbFunc.Left(_emMonthText, 4)},{PbFunc.Left(_emMonthText, 4)}01～{_emMonthText.Replace("/", "")},30521－{RptName},無任何資料!");
}
//寫入內容
string lsYMD = "";
int addCount = 0;//計算新增row的數量
foreach (DataRow row in dt.Rows)
{
if (lsYMD != row["AM2_YMD"].AsString())
{
lsYMD = row["AM2_YMD"].AsString();
RowIndex = RowIndex + 1;
addCount++;
/* 年度資料 */
if (lsYMD.Length == 4)
{
worksheet.Cells[$"A{RowIndex + 1}"].Value = PbFunc.Left(lsYMD, 4).AsInt();
}
else
{
worksheet.Cells[$"A{RowIndex + 1}"].Value = PbFunc.f_get_month_eng_name(PbFunc.Right(lsYMD, 2).AsInt(), "1");
}//if (lsYMD.Length==4)
/* 成交量 & OI */
int foundIndex = dtAI2.Rows.IndexOf(dtAI2.Select($@"ai2_ymd ='{lsYMD}'").FirstOrDefault());
if (foundIndex > -1)
{
worksheet.Cells[$"F{RowIndex + 1}"].SetValue(dtAI2.Rows[foundIndex]["AI2_M_QNTY"]); //成交契約總數 Total Trading Volume
worksheet.Cells[$"G{RowIndex + 1}"].SetValue(dtAI2.Rows[foundIndex]["AI2_OI"]); //未沖銷契約數 Open Interest
}
}//if (lsYMD != row["AM2_YMD"].AsString())
//判斷欄位
int columnIndex = IDFGtype(row);
worksheet.Rows[RowIndex][columnIndex].SetValue(row["AM2_M_QNTY"]);//買進 Long or 賣出 Short
}//foreach (DataRow row in dt.Rows)
//刪除空白列
//總列數,隱藏於A3
int rowTotal = worksheet.Cells["A3"].Value.AsInt();
if (rowTotal > addCount)
{
worksheet.Rows.Remove(RowIndex + 1, rowTotal - addCount);
//表格最後一行設回原本的框線
int lineIndex = RowIndex + 1;
worksheet.Range[$"A{lineIndex}:G{lineIndex}"].Borders.BottomBorder.LineStyle = BorderLineStyle.Thin;
worksheet.Range[$"A{lineIndex}:G{lineIndex}"].Borders.BottomBorder.Color = Color.Black;
worksheet.ScrollTo(0, 0);//直接滾動到最上面，不然看起來很像少行數
}
}
catch (Exception ex)
{
#if DEBUG
throw new Exception($"wf30520:" + ex.Message);
#else
throw ex;
#endif
}
finally {
workbook.SaveDocument(_lsFile);
}
return(MessageDisplay.MSG_OK);
}
/// <summary>
/// wf_30550
/// </summary>
/// <param name="RowIndex">Excel的Row位置</param>
/// <param name="RowTotal">Excel的Column預留數</param>
/// <returns></returns>
public string Wf30550(int RowIndex = 4, string SheetName = "30550", string RptName = "國內股價指數選擇權交易概況明細表")
{
Workbook workbook = new Workbook();
try {
//切換Sheet
workbook.LoadDocument(_lsFile);
Worksheet worksheet = workbook.Worksheets[SheetName];
//總列數,隱藏於A3
int rowTotal = RowIndex + worksheet.Cells["A3"].Value.AsInt();
//起始年份,隱藏於B3
string firstYear = worksheet.Cells["B3"].Value.AsString();
/******************
* 讀取資料
* 分三段：
* 1.年
* 2.當年1月至當月合計
* 3.當年1月至當月明細
******************/
DataTable dt = new D30550().GetData(firstYear, PbFunc.Left(_emMonthText, 4), $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", ""));
if (dt.Rows.Count <= 0)
{
return($"{firstYear}~{PbFunc.Left(_emMonthText, 4)},{PbFunc.Left(_emMonthText, 4)}01～{_emMonthText.Replace("/", "")},{SheetName}－{RptName},無任何資料!");
}
string lsYMD = "";
string endYMD = dt.AsEnumerable().LastOrDefault()["AM2_YMD"].AsString();
foreach (DataRow row in dt.Rows)
{
if (lsYMD != row["AM2_YMD"].AsString())
{
lsYMD = row["AM2_YMD"].AsString();
RowIndex = RowIndex + 1;
if (lsYMD.Length == 4)
{
worksheet.Rows[RowIndex][1 - 1].Value = PbFunc.Left(lsYMD, 4).AsInt();
}
else
{
string getMonthEngName = PbFunc.f_get_month_eng_name(PbFunc.Right(lsYMD, 2).AsInt(), "1");
if (lsYMD != endYMD)
{
worksheet.Rows[RowIndex][1 - 1].Value = getMonthEngName;
}
else
{
worksheet.Rows[rowTotal][1 - 1].Value = getMonthEngName;
}
} //if (lsYMD.Length==4)
} //if (lsYMD != row["AM2_YMD"].AsString())
//判斷欄位
int columnIndex = IDFGtype(row);
if (row["AM2_YMD"].AsString() != endYMD)
{
worksheet.Rows[RowIndex][columnIndex].Value = row["AM2_M_QNTY"].AsDecimal();
}
else
{
worksheet.Rows[rowTotal][columnIndex].Value = row["AM2_M_QNTY"].AsDecimal();
}
}
//刪除空白列
if (rowTotal > RowIndex)
{
worksheet.Range[$"{RowIndex+1}:{rowTotal+1-1}"].Delete(DeleteMode.EntireRow);
worksheet.ScrollTo(0, 0);//直接滾動到最上面，不然看起來很像少行數
}
}
catch (Exception ex) {
throw ex;
}
finally {
workbook.SaveDocument(_lsFile);
}
return(MessageDisplay.MSG_OK);
}
/// <summary>
/// wf_30581
/// </summary>
/// <param name="RowIndex">Excel的Row位置</param>
/// <param name="RowTotal">Excel的Column預留數</param>
/// <returns></returns>
public string Wf30581(int RowIndex = 6, string RptName = "國內股票期貨及黃金期貨交易概況表")
{
Workbook workbook = new Workbook();
try {
//切換Sheet
workbook.LoadDocument(_lsFile);
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1"].Select();
//總列數,隱藏於A5
int rowTotal = RowIndex + worksheet.Cells["A5"].Value.AsInt();
//起始年份,隱藏於B5
string firstYear = worksheet.Cells["B5"].Value.AsString();
/******************
* 讀取資料
* 分三段：
* 1.年
* 2.當年1月至當月合計
* 3.當年1月至當月明細
******************/
DataTable dt = new D30580().List30581(firstYear, PbFunc.Left(_emMonthText, 4), $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", ""));
if (dt.Rows.Count <= 0)
{
return($"{firstYear}~{PbFunc.Left(_emMonthText, 4)},{PbFunc.Left(_emMonthText, 4)}01～{_emMonthText.Replace("/", "")},30581－{RptName},無任何資料!");
}
/* 成交量 & OI */
DataTable dtAI2 = new D30580().List30581AI2(firstYear, PbFunc.Left(_emMonthText, 4), $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", ""));
if (dtAI2.Rows.Count <= 0)
{
return($"{firstYear}~{PbFunc.Left(_emMonthText, 4)},{PbFunc.Left(_emMonthText, 4)}01～{_emMonthText.Replace("/", "")},30581－{RptName},無任何資料!");
}
//寫入內容
string lsYMD = "";
int rowCurr = 0;
string endYMD = dt.AsEnumerable().LastOrDefault()["AM2_YMD"].AsString();
foreach (DataRow row in dt.Rows)
{
if (lsYMD != row["AM2_YMD"].AsString())
{
lsYMD = row["AM2_YMD"].AsString();
RowIndex = RowIndex + 1;
if (lsYMD.Length == 4)
{
worksheet.Rows[RowIndex][1 - 1].Value = PbFunc.Left(lsYMD, 4).AsInt();
rowCurr = RowIndex;
}
else
{
rowCurr = lsYMD != endYMD ? RowIndex : rowTotal;
worksheet.Rows[rowCurr][1 - 1].Value = PbFunc.f_get_month_eng_name(PbFunc.Right(lsYMD, 2).AsInt(), "1");
}//if (lsYMD.Length==4)
/* 成交量 & OI */
int foundIndex = dtAI2.Rows.IndexOf(dtAI2.Select($@"ai2_ymd ='{lsYMD}'").FirstOrDefault());
if (foundIndex > -1)
{
//iole_1.application.activecell(li_ole_row_curr,6).value = ids_tmp.getitemdecimal(ll_found,"cp_m_qnty")
worksheet.Rows[rowCurr][6 - 1].Value = dtAI2.Compute("sum(AI2_M_QNTY)", $@"AI2_SUM_TYPE='{dtAI2.Rows[foundIndex]["AI2_SUM_TYPE"]}' and AI2_YMD='{dtAI2.Rows[foundIndex]["AI2_YMD"]}'").AsDecimal();
worksheet.Rows[rowCurr][7 - 1].Value = dtAI2.Rows[foundIndex]["AI2_OI"].AsDecimal();
}
}//if (lsYMD != row["AM2_YMD"].AsString())
//判斷欄位
int columnIndex = IDFGtype(row);
worksheet.Rows[rowCurr][columnIndex].Value = row["AM2_M_QNTY"].AsDecimal();
}//foreach (DataRow row in dt.Rows)
//刪除空白列
if (rowTotal > RowIndex)
{
worksheet.Range[$"{RowIndex+1}:{rowTotal + 1 - 1}"].Delete(DeleteMode.EntireRow);
worksheet.ScrollTo(0, 0);//直接滾動到最上面，不然看起來很像少行數
}
}
catch (Exception ex) {
#if DEBUG
throw new Exception($"wf30581:" + ex.Message);
#else
throw ex;
#endif
}
finally {
workbook.SaveDocument(_lsFile);
}
return(MessageDisplay.MSG_OK);
}
