Example #1
0
        /// <summary>
        /// 按下[匯出]按鈕時
        /// </summary>
        /// <returns></returns>
        protected override ResultStatus Export()
        {
            //1.check
            if (gvKind.DataRowCount <= 0)
            {
                MessageDisplay.Normal("選擇的日期必須有契約資訊,請重新選擇日期");
                return(ResultStatus.Fail);
            }

            //1.1檢查最少必須勾選一筆商品
            gvDate.CloseEditor();      //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
            gvDate.UpdateCurrentRow(); //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
            gvKind.CloseEditor();      //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
            gvKind.UpdateCurrentRow(); //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
            DataTable dtTemp   = (DataTable)gcKind.DataSource;
            bool      haveKind = false;

            foreach (DataRow drKind in dtTemp.Rows)
            {
                if (drKind["CPR_SELECT"].AsString() == "Y")
                {
                    haveKind = true;
                    break;
                }
            }

            if (!haveKind)
            {
                MessageDisplay.Normal("必須勾選一筆契約");
                return(ResultStatus.Fail);
            }

            if (chkModel.CheckedItemsCount < 1)
            {
                MessageDisplay.Error("請至少勾選一種指標種類", GlobalInfo.ErrorText);
                return(ResultStatus.Fail);
            }

            //1.2檢查統計資料是否已經轉入完畢
            string FinishedJob = PbFunc.f_get_jsw(_ProgramID, "E", txtEndDate.Text);

            if (FinishedJob != "Y")
            {
                DialogResult chooseResult = MessageDisplay.Choose(string.Format("{0} 統計資料未轉入完畢,是否要繼續?", txtEndDate.Text), MessageBoxDefaultButton.Button2, GlobalInfo.QuestionText);
                if (chooseResult != DialogResult.Yes)
                {
                    return(ResultStatus.Fail);
                }
            }

            string   kindId   = "";
            Workbook workbook = new Workbook();

            try {
                //2.開始轉出資料
                panFilter.Enabled = panSecond.Enabled = false;
                labMsg.Visible    = true;
                labMsg.Text       = "開始轉檔...";
                this.Refresh();

                DataTable dtDate    = (DataTable)gcDate.DataSource;
                string    startDate = dtDate.Rows[dtDate.Rows.Count - 1]["SDATE"].AsDateTime().ToString("yyyyMMdd");
                string    endDate   = dtDate.Rows[dtDate.Rows.Count - 1]["EDATE"].AsDateTime().ToString("yyyyMMdd");

                #region 指標:SMA,EWMA,MAX
                foreach (CheckedListBoxItem item in chkModel.Items)
                {
                    if (item.CheckState == CheckState.Unchecked)
                    {
                        continue;
                    }

                    string modelType = "";
                    string modelName = "";
                    switch (item.Value)
                    {
                    case "chkSma":
                        modelType = "S";
                        modelName = "SMA";
                        break;

                    case "chkEwma":
                        modelType = "E";
                        modelName = "EWMA";
                        break;

                    case "chkMax":
                        modelType = "M";
                        modelName = "MAX";
                        break;
                    }

                    #region 商品
                    //每一個商品都會產生一個excel檔案
                    foreach (DataRow drKind in dtTemp.Rows)
                    {
                        if (drKind["CPR_SELECT"].AsString() != "Y")
                        {
                            continue;
                        }

                        //2.0 get some column data
                        string  effDate      = drKind["cpr_effective_date"].AsDateTime().ToString("yyyy/MM/dd");
                        Decimal lastRiskRate = drKind["last_risk_rate"].AsDecimal(0);
                        Decimal riskRateOrg  = drKind["cpr_price_risk_rate_org"].AsDecimal(0);

                        kindId = drKind["CPR_KIND_ID"].AsString();

                        //2.1 copy template xlsx to target path and open
                        string excelDestinationPath = CopyExcelTemplateFile2(_ProgramID, modelName, kindId);
                        workbook.LoadDocument(excelDestinationPath);
                        Worksheet worksheet = workbook.Worksheets["RawData"];

                        //2.3寫入檔頭[M1:M6]
                        worksheet.Cells[0, 12].Value = kindId;                                    //商品
                        worksheet.Cells[1, 12].Value = drKind["cpr_effective_date"].AsDateTime(); //日期,最近一次調整日期
                        if (drKind["cpr_price_risk_rate"] != DBNull.Value)
                        {
                            worksheet.Cells[2, 12].Value = drKind["cpr_price_risk_rate"].AsDecimal();//百分比,現行最小風險價格係數
                        }
                        if (drKind["last_risk_rate"] != DBNull.Value)
                        {
                            worksheet.Cells[3, 12].Value = drKind["last_risk_rate"].AsDecimal();//百分比,最近一次修改前之最小風險價格係數
                        }
                        if (drKind["risk_interval"] != DBNull.Value)
                        {
                            worksheet.Cells[4, 12].Value = drKind["risk_interval"].AsDecimal(); //百分比,最小風險價格係數級距
                        }
                        worksheet.Cells[5, 12].Value = DateTime.Today;                          //日期,作業日期


                        //2.4讀取子table data (mg1_ymd/mg1_risk/mg1_min_risk)
                        DataTable dtSingleKind = dao48040.ListKindByKindId(kindId, startDate, endDate, modelType);
                        if (dtSingleKind.Rows.Count <= 0)
                        {
                            File.Delete(excelDestinationPath);
                            //workbook.SaveDocument(excelDestinationPath);//存檔
                            labMsg.Text += string.Format("{0},{1}~{2}無任何資料!\r\n", kindId, startDate, endDate);
                            this.Refresh();
                            continue;
                        }

                        #region //2.5寫入五段日期基本資訊[F2:J6]
                        int rowIndex = 2;
                        int pos      = 1;
                        foreach (DataRow drDate in dtDate.Rows)
                        {
                            worksheet.Cells[pos, 5].Value = drDate["SDATE"].AsString(); //資料起日
                            worksheet.Cells[pos, 6].Value = drDate["EDATE"].AsString(); //資料迄日
                            worksheet.Cells[pos, 7].Value = drDate["DAY_CNT"].AsInt();  //天數

                            //ken,使用DataView的Find之前,要指定Sort欄位(可多個欄位)
                            int filterIndex = dtSingleKind.Rows.IndexOf(dtSingleKind.Select($"mg1_ymd >= '{drDate["SDATE"].AsString()}'").FirstOrDefault());
                            if (filterIndex >= 0)
                            {
                                worksheet.Cells[pos, 8].Value = rowIndex + filterIndex;//起日位址(FirstRowIndex)
                            }
                            else
                            {
                                worksheet.Cells[pos, 8].Value = rowIndex;//起日位址(FirstRowIndex)
                            }

                            worksheet.Cells[pos, 9].Value = rowIndex + dtSingleKind.Rows.Count - 1;//迄日位址(LastRowIndex)

                            pos++;
                        }//foreach(DataRow drDate in dtDate.Rows) {
                        #endregion

                        //2.6寫入整個子table (日期/實際風險價格係數/最小風險價格係數)
                        worksheet.Import(dtSingleKind, false, 1, 0);//dataTable, isAddHeader, RowFirstIndex, ColFirstIndex
                        worksheet.Range["A1"].Select();
                        worksheet.ScrollToRow(0);

                        //2.7刪多的圖表(共五個,起始rowIndex=24,每個高31)
                        //ken,從最後一個圖表開始刪除比較正確,才不會跑位,最後圖表rowIndex=148
                        worksheet = workbook.Worksheets["Graph"];
                        int graphRowIndex = 148;
                        int graphHeight   = 31;
                        for (int k = dtDate.Rows.Count - 1; k >= 0; k--)
                        {
                            if (dtDate.Rows[k]["AI2_SELECT"].AsString() == "N")
                            {
                                worksheet.Rows.Remove(graphRowIndex, graphHeight - 1);
                            }
                            graphRowIndex -= graphHeight;
                        }//for(int k = dtDate.Rows.Count - 1;k >= 0;k--) {


                        //2.8寫入[註3]資訊
                        riskRateOrg = Math.Round(riskRateOrg * 100, 1, MidpointRounding.AwayFromZero);
                        if (lastRiskRate == 0)
                        {
                            worksheet.Cells[18, 0].Value = string.Format("註3:上市日起至今最小風險價格係數均為{0}%", riskRateOrg.ToString());
                        }
                        else
                        {
                            lastRiskRate = Math.Round(lastRiskRate * 100, 1, MidpointRounding.AwayFromZero);
                            worksheet.Cells[18, 0].Value = string.Format("註3:最小風險價格係數自{0}起由{1}%調整為{2}",
                                                                         effDate,
                                                                         lastRiskRate.ToString(),
                                                                         riskRateOrg.ToString());
                        }

                        //2.9存檔
                        workbook.SaveDocument(excelDestinationPath);
                    }//foreach (DataRow drKind in dtTemp.Rows) 商品
                    #endregion
                }
                #endregion



                return(ResultStatus.Success);
            } catch (Exception ex) {
                WriteLog(ex, kindId);
            } finally {
                panFilter.Enabled = panSecond.Enabled = true;
                labMsg.Text       = "";
                labMsg.Visible    = false;
            }
            return(ResultStatus.Fail);
        }
Example #2
0
        protected override ResultStatus Export()
        {
            #region 檢查
            //要先call f_get_jsw
            string rtn = "";
            rtn = PbFunc.f_get_jsw("60420", "E", GlobalInfo.OCF_DATE.ToString("yyyy/MM/dd"));
            //ls_rtn = f_get_jsw(is_txn_id,'E',em_date.text)
            if (rtn != "Y")
            {
                DialogResult result = MessageBox.Show(txtEndDate.Text + " 統計資料未轉入完畢,是否要繼續?",
                                                      "注意", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if (result == DialogResult.No)
                {
                    lblProcessing.Visible = false;
                    return(ResultStatus.Fail);
                }
            }
            #endregion
            dao60420 = new D60420();
            base.Export();
            lblProcessing.Visible = true;
            this.Refresh();
            Thread.Sleep(5);
            //複製檔案
            string excelDestinationPath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
            //開啟檔案
            Workbook workbook = new Workbook();
            workbook.LoadDocument(excelDestinationPath);

            #region wf_60410a (sheet 1/4/5)
            string  rptName, rptId;
            int     i, j, rowNum;
            decimal value;

            /*************************************
            *  ls_rpt_name = 報表名稱
            *  ls_rpt_id = 報表代號
            *************************************/
            rptName            = "檢查表";
            rptId              = "60410_1a";
            lblProcessing.Text = rptId + "-" + rptName + " 轉檔中...";
            this.Refresh();
            Thread.Sleep(5);
            //讀取資料
            DataTable dt60410_1a = dao60420.d_60410_1a(txtStartDate.DateTimeValue, txtEndDate.DateTimeValue);
            if (dt60410_1a.Rows.Count == 0)
            {
                MessageDisplay.Info(string.Format("{0},{1},無任何資料!", txtEndDate.EditValue, rptId + "-" + rptName));
            }

            //切換Sheet
            //1. 未符合「成份股檔數≧10」
            Worksheet sheet1 = workbook.Worksheets[0];

            //填資料
            if (cbx1.Checked == false)
            {
                sheet1.Cells[2, 0].Value = "標準1. 全部「成份股檔數」";
            }
            rowNum = 3;
            for (i = 0; i < dt60410_1a.Rows.Count; i++)
            {
                DataRow dr60410_1a = dt60410_1a.Rows[i];
                if (cbx1.Checked == false || dr60410_1a["tot_cnt"].AsInt() < sleCond1.Text.AsInt())
                {
                    rowNum = rowNum + 1;
                    sheet1.Cells[rowNum, 0].Value = dr60410_1a["cod_name"].AsString();
                    sheet1.Cells[rowNum, 1].Value = (DateTime.ParseExact(dr60410_1a["ymd"].AsString(), "yyyyMMdd", CultureInfo.InvariantCulture)).ToString("yyyy/M/d").AsDateTime();
                    sheet1.Cells[rowNum, 2].SetValue(dr60410_1a["tot_cnt"]);
                }
            }
            sheet1.ScrollTo(0, 0);

            //4. 未符合「最低25%權重之成份股,檔數在15檔(含)以上,過去半年每日合計成交值之平均值>3,000萬美元」
            Worksheet sheet4 = workbook.Worksheets[3];
            //填資料
            if (cbx4.Checked == false)
            {
                sheet4.Cells[2, 0].Value = "標準4. 全部「最低25%權重之成份股,檔數在15檔(含)以上,過去半年每日合計成交值」";
            }
            rowNum = 4;
            for (i = 0; i < dt60410_1a.Rows.Count; i++)
            {
                DataRow dr60410_1a = dt60410_1a.Rows[i];
                if (dr60410_1a["cnt25"].AsInt() >= sleCond41.Text.AsInt() &&
                    Math.Round(dr60410_1a["avg_amt_mth_usd"].AsDecimal() / 10000, 0) <= sleCond42.Text.AsDecimal() ||
                    cbx4.Checked == false)
                {
                    rowNum = rowNum + 1;
                    sheet4.Cells[rowNum, 0].Value = dr60410_1a["cod_name"].AsString();
                    sheet4.Cells[rowNum, 1].Value = (DateTime.ParseExact(dr60410_1a["ymd"].AsString(), "yyyyMMdd", CultureInfo.InvariantCulture)).ToString("yyyy/M/d").AsDateTime();
                    sheet4.Cells[rowNum, 2].SetValue(dr60410_1a["cnt25"]);
                    sheet4.Cells[rowNum, 3].SetValue(dr60410_1a["weight25"]);
                    sheet4.Cells[rowNum, 4].SetValue(dr60410_1a["avg_amt_cls_usd"]);
                    sheet4.Cells[rowNum, 5].SetValue(dr60410_1a["avg_amt_cls_tw"]);
                    sheet4.Cells[rowNum, 6].SetValue(dr60410_1a["avg_amt_mth_usd"]);
                    sheet4.Cells[rowNum, 7].SetValue(dr60410_1a["avg_amt_mth_tw"]);
                    sheet4.Cells[rowNum, 8].SetValue(dr60410_1a["day_amt_cls_usd"]);
                    sheet4.Cells[rowNum, 9].SetValue(dr60410_1a["day_amt_cls_tw"]);
                    sheet4.Cells[rowNum, 10].SetValue(dr60410_1a["day_amt_mth_usd"]);
                    sheet4.Cells[rowNum, 11].SetValue(dr60410_1a["day_amt_mth_tw"]);
                }
            }
            sheet4.ScrollTo(0, 0);

            //5. 未符合「最低25%權重之成份股,檔數低於15檔,過去半年每日合計成交值之平均值>5,000萬美元」
            Worksheet sheet5 = workbook.Worksheets[4];
            //填資料
            if (cbx5.Checked == false)
            {
                sheet5.Cells[2, 0].Value = "標準5. 全部「最低25%權重之成份股,檔數低於15檔,過去半年每日合計成交值」";
            }
            rowNum = 4;
            for (i = 0; i < dt60410_1a.Rows.Count; i++)
            {
                DataRow dr60410_1a = dt60410_1a.Rows[i];
                if (dr60410_1a["cnt25"].AsInt() < sleCond41.Text.AsInt() &&
                    (Math.Round(dr60410_1a["avg_amt_mth_usd"].AsDecimal() / 10000, 0) <= sleCond52.Text.AsDecimal() ||
                     cbx5.Checked == false))
                {
                    rowNum = rowNum + 1;
                    sheet5.Cells[rowNum, 0].Value = dr60410_1a["cod_name"].AsString();
                    sheet5.Cells[rowNum, 1].Value = (DateTime.ParseExact(dr60410_1a["ymd"].AsString(), "yyyyMMdd", CultureInfo.InvariantCulture)).ToString("yyyy/M/d").AsDateTime();
                    sheet5.Cells[rowNum, 2].SetValue(dr60410_1a["cnt25"]);
                    sheet5.Cells[rowNum, 3].SetValue(dr60410_1a["weight25"]);
                    sheet5.Cells[rowNum, 4].SetValue(dr60410_1a["avg_amt_cls_usd"]);
                    sheet5.Cells[rowNum, 5].SetValue(dr60410_1a["avg_amt_cls_tw"]);
                    sheet5.Cells[rowNum, 6].SetValue(dr60410_1a["avg_amt_mth_usd"]);
                    sheet5.Cells[rowNum, 7].SetValue(dr60410_1a["avg_amt_mth_tw"]);
                    sheet5.Cells[rowNum, 8].SetValue(dr60410_1a["day_amt_cls_usd"]);
                    sheet5.Cells[rowNum, 9].SetValue(dr60410_1a["day_amt_cls_tw"]);
                    sheet5.Cells[rowNum, 10].SetValue(dr60410_1a["day_amt_mth_usd"]);
                    sheet5.Cells[rowNum, 11].SetValue(dr60410_1a["day_amt_mth_tw"]);
                }
            }
            sheet5.ScrollTo(0, 0);
            #endregion

            #region wf_60410_2 (sheet 2)
            rptName            = "2. 未符合「權重最大之成份股權重≦30%」";
            rptId              = "60410_2";
            lblProcessing.Text = rptId + "-" + rptName + " 轉檔中...";
            this.Refresh();
            Thread.Sleep(5);
            //讀取資料
            DataTable dt60410_2;
            if (cbx2.Checked == true)
            {
                dt60410_2 = dao60420.d_60410_2(txtStartDate.DateTimeValue, txtEndDate.DateTimeValue, sleCond2.Text.AsDecimal());
            }
            else
            {
                dt60410_2 = dao60420.d_60410_2(txtStartDate.DateTimeValue, txtEndDate.DateTimeValue, -1.AsDecimal());
            }
            if (dt60410_2.Rows.Count == 0)
            {
                //PB把這段註解掉,先照著寫
                //MessageDisplay.Info(string.Format("{0},{1},無任何資料!", txtEndDate.EditValue, ls_rpt_id + "-" + ls_rpt_name));
            }

            //切換Sheet
            //2. 未符合「權重最大之成份股權重≦30%」
            Worksheet sheet2 = workbook.Worksheets[1];
            //填資料
            if (cbx2.Checked == false)
            {
                sheet2.Cells[2, 0].Value = "標準2. 全部「權重最大之成份股權重%」";
            }
            rowNum = 3;
            for (i = 0; i < dt60410_2.Rows.Count; i++)
            {
                DataRow dr60410_2 = dt60410_2.Rows[i];
                if (cbx2.Checked == true && dr60410_2["index_weight"].AsDecimal() <= 0.3.AsDecimal())
                {
                    continue;
                }
                rowNum = rowNum + 1;
                sheet2.Cells[rowNum, 0].Value = dr60410_2["cod_name"].AsString();
                sheet2.Cells[rowNum, 1].Value = dr60410_2["TSE3_YMD"].AsDateTime().ToString("yyyy/M/d").AsDateTime();
                sheet2.Cells[rowNum, 2].SetValue(dr60410_2["TSE3_SID"]);
                sheet2.Cells[rowNum, 3].Value = dr60410_2["TFXMS_SNAME"].AsString();
                sheet2.Cells[rowNum, 4].SetValue(dr60410_2["INDEX_WEIGHT"]);
            }
            sheet2.ScrollTo(0, 0);
            #endregion

            #region wf_60410_3 (sheet 3)
            rptName            = "3. 未符合「權重前五大成份股合計權重≦60%」";
            rptId              = "60412_3"; //PB就是這樣寫
            lblProcessing.Text = rptId + "-" + rptName + " 轉檔中...";
            this.Refresh();
            Thread.Sleep(5);
            //讀取資料
            DataTable dt60412_3;
            if (cbx3.Checked == true)
            {
                dt60412_3 = dao60420.d_60412_3(txtStartDate.DateTimeValue, txtEndDate.DateTimeValue, sleCond3.Text.AsDecimal());
            }
            else
            {
                dt60412_3 = dao60420.d_60412_3(txtStartDate.DateTimeValue, txtEndDate.DateTimeValue, -1.AsDecimal());
            }
            if (dt60412_3.Rows.Count == 0)
            {
                //PB把這段註解掉,先照著寫
                //MessageDisplay.Info(string.Format("{0},{1},無任何資料!", txtEndDate.EditValue, ls_rpt_id + "-" + ls_rpt_name));
            }

            //切換Sheet
            //3. 未符合「權重前五大成份股合計權重≦60%」
            Worksheet sheet3 = workbook.Worksheets[2];
            //填資料
            if (cbx3.Checked == false)
            {
                sheet3.Cells[2, 0].Value = "標準3. 全部「權重前五大成份股合計權重%」";
            }
            rowNum = 3;
            for (i = 0; i < dt60412_3.Rows.Count; i++)
            {
                DataRow dr60412_3 = dt60412_3.Rows[i];
                rowNum = rowNum + 1;
                sheet3.Cells[rowNum, 0].Value = dr60412_3["cod_name"].AsString();
                sheet3.Cells[rowNum, 1].Value = dr60412_3["TSE3_DATE"].AsDateTime().ToString("yyyy/M/d").AsDateTime();
                sheet3.Cells[rowNum, 2].SetValue(dr60412_3["TSE5_25_WEIGHT"]);
                sheet3.Cells[rowNum, 3].SetValue(dr60412_3["TSE3_DESC_SEQ"]);
                sheet3.Cells[rowNum, 4].SetValue(dr60412_3["TSE3_SID"]);
                sheet3.Cells[rowNum, 5].SetValue(dr60412_3["TFXMS_SNAME"]);
                sheet3.Cells[rowNum, 6].SetValue(dr60412_3["INDEX_WEIGHT"]);
            }
            sheet3.ScrollTo(0, 0);
            #endregion
            //預設打開第一張sheet
            workbook.Worksheets.ActiveWorksheet = workbook.Worksheets[0];
            workbook.SaveDocument(excelDestinationPath);
            lblProcessing.Visible = false;
            this.Refresh();
            Thread.Sleep(5);
            return(ResultStatus.Success);
        }
Example #3
0
        /// <summary>
        /// 按下[匯出]按鈕時
        /// </summary>
        /// <returns></returns>
        protected override ResultStatus Export()
        {
            //1.check
            if (gvKind.DataRowCount <= 0)
            {
                MessageDisplay.Normal("選擇的日期必須有契約資訊,請重新選擇日期");
                return(ResultStatus.Fail);
            }

            //1.1檢查最少必須勾選一筆商品
            gvDate.CloseEditor();      //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
            gvDate.UpdateCurrentRow(); //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
            gvKind.CloseEditor();      //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
            gvKind.UpdateCurrentRow(); //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
            DataTable dtTemp   = (DataTable)gcKind.DataSource;
            bool      haveKind = false;

            foreach (DataRow drKind in dtTemp.Rows)
            {
                if (drKind["CPR_SELECT"].AsString() == "Y")
                {
                    haveKind = true;
                    break;
                }
            }

            if (!haveKind)
            {
                MessageDisplay.Normal("必須勾選一筆契約");
                return(ResultStatus.Fail);
            }

            if (chkModel.CheckedItemsCount < 1)
            {
                MessageDisplay.Error("請至少勾選一種指標種類", GlobalInfo.ErrorText);
                return(ResultStatus.Fail);
            }

            //1.2檢查統計資料是否已經轉入完畢
            string FinishedJob = PbFunc.f_get_jsw(_ProgramID, "E", txtEndDate.Text);

            if (FinishedJob != "Y")
            {
                DialogResult chooseResult = MessageDisplay.Choose(string.Format("{0} 統計資料未轉入完畢,是否要繼續?", txtEndDate.Text), MessageBoxDefaultButton.Button2, GlobalInfo.QuestionText);
                if (chooseResult != DialogResult.Yes)
                {
                    return(ResultStatus.Fail);
                }
            }

            try {
                //2.開始轉出資料
                panFilter.Enabled = panSecond.Enabled = false;
                labMsg.Visible    = true;
                labMsg.Text       = "開始轉檔...";
                this.Refresh();

                //2.1 copy template xlsx to target path and open
                Workbook workbook             = new Workbook();
                string   originalFilePath     = Path.Combine(GlobalInfo.DEFAULT_EXCEL_TEMPLATE_DIRECTORY_PATH, _ProgramID + "." + FileType.XLSX.ToString().ToLower());
                string   excelDestinationPath = "";

                DataTable dtDate = (DataTable)gcDate.DataSource;

                #region 指標:SMA,EWMA,MAX
                foreach (CheckedListBoxItem item in chkModel.Items)
                {
                    if (item.CheckState == CheckState.Unchecked)
                    {
                        continue;
                    }

                    int    sheetIndex = 0;
                    int    flag       = 0;
                    string modelType  = "";
                    switch (item.Value)
                    {
                    case "chkSma":
                        excelDestinationPath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH,
                                                            _ProgramID + "_SMA_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss.") + FileType.XLSX.ToString().ToLower());
                        modelType = "S";
                        break;

                    case "chkEwma":
                        excelDestinationPath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH,
                                                            _ProgramID + "_EWMA_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss.") + FileType.XLSX.ToString().ToLower());
                        modelType = "E";
                        break;

                    case "chkMax":
                        excelDestinationPath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH,
                                                            _ProgramID + "_MAX_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss.") + FileType.XLSX.ToString().ToLower());
                        modelType = "M";
                        break;
                    }

                    //copy template and change filename
                    File.Copy(originalFilePath, excelDestinationPath, true);
                    workbook.LoadDocument(excelDestinationPath);

                    #region 時段
                    //每個時間區間為一個sheet,總共5個
                    foreach (DataRow drDate in dtDate.Rows)
                    {
                        string monDiff   = drDate["MON_DIFF"].AsString();                     //期間
                        string startDate = drDate["SDATE"].AsDateTime().ToString("yyyyMMdd"); //資料起日
                        string endDate   = drDate["EDATE"].AsDateTime().ToString("yyyyMMdd"); //資料迄日
                                                                                              //DateTime startDate = drDate["SDATE"].AsDateTime();//資料起日
                                                                                              //DateTime endDate = drDate["EDATE"].AsDateTime();//資料迄日
                        int dayCount = drDate["DAY_CNT"].AsInt();                             //天數

                        //2.2跳到指定sheet,寫檔頭
                        Worksheet worksheet = workbook.Worksheets[sheetIndex++];
                        worksheet.Cells[1, 2].Value  = cbxSubType.Text;
                        worksheet.Cells[2, 11].Value = string.Format("列印日期:{0}", DateTime.Now.ToString("yyyy/MM/dd"));
                        worksheet.Cells[2, 1].Value  = string.Format("{0}({1}~{2}),計{3}天",
                                                                     monDiff,
                                                                     drDate["SDATE"].AsDateTime().ToString("yyyy/MM/dd"),
                                                                     drDate["EDATE"].AsDateTime().ToString("yyyy/MM/dd"),
                                                                     dayCount.ToString());

                        #region                 //2.3分別讀取每個商品的詳細資訊
                        int rowIndex      = 7;
                        int emptyRowCount = 60; //template 空白行的數量
                        int kindCount     = 0;

                        //逐一看勾選的商品有哪些
                        foreach (DataRow drKind in dtTemp.Rows)
                        {
                            if (drKind["CPR_SELECT"].AsString() != "Y")
                            {
                                continue;
                            }

                            kindCount++;
                            string  kindId   = drKind["cpr_kind_id"].AsString();          //契約ID
                            Decimal riskRate = drKind["cpr_price_risk_rate"].AsDecimal(); //現行最小風險價格係數
                            Decimal interval = drKind["risk_interval"].AsDecimal();       //最小風險價格係數級距

                            //2.3.1讀取子table data
                            DataTable dtSingleKind = dao48030.ListKindByKindId(startDate, endDate, riskRate, interval, kindId, modelType);
                            if (dtSingleKind.Rows.Count <= 0)
                            {
                                labMsg.Text += string.Format("{0},{1}~{2}無任何資料!\r\n", kindId, startDate, endDate);
                                this.Refresh();
                                continue;
                            }

                            //2.3.2寫入明細
                            //ken,原則上一個商品只會找到一筆明細(已經group by)
                            DataRow drDetail  = dtSingleKind.Rows[0];
                            int     tempCount = drDetail["cnt"].AsInt();
                            Decimal level_1   = drDetail["level_1"].AsDecimal();
                            Decimal level_23  = drDetail["level_23"].AsDecimal();
                            Decimal level_4   = drDetail["level_4"].AsDecimal();

                            worksheet.Cells[rowIndex, 0].Value = drDetail["mg1_kind_id"].AsString();
                            worksheet.Cells[rowIndex, 1].Value = drDetail["avg_risk"].AsDecimal();
                            worksheet.Cells[rowIndex, 2].Value = drDetail["max_risk"].AsDecimal();
                            worksheet.Cells[rowIndex, 3].Value = drDetail["min_risk"].AsDecimal();
                            worksheet.Cells[rowIndex, 4].Value = riskRate;

                            worksheet.Cells[rowIndex, 6].Value  = level_1;
                            worksheet.Cells[rowIndex, 7].Value  = Math.Round(level_1 / tempCount, 4, MidpointRounding.AwayFromZero);
                            worksheet.Cells[rowIndex, 8].Value  = level_23;
                            worksheet.Cells[rowIndex, 9].Value  = Math.Round(level_23 / tempCount, 4, MidpointRounding.AwayFromZero);
                            worksheet.Cells[rowIndex, 10].Value = level_4;
                            worksheet.Cells[rowIndex, 11].Value = Math.Round(level_4 / tempCount, 4, MidpointRounding.AwayFromZero);

                            worksheet.Cells[rowIndex, 12].Value = interval;

                            rowIndex++;
                            flag++;
                        }//foreach (DataRow drKind in dtTemp.Rows) 商品
                        #endregion

                        //2.4刪除空白列 (結束商品foreach才刪除空白列,跑下一個sheet)
                        if (kindCount < emptyRowCount)
                        {
                            worksheet.Rows.Remove(rowIndex, emptyRowCount - kindCount);
                        }
                    }//foreach (DataRow drDate in dtDate.Rows) 時段
                    #endregion

                    //2.9存檔
                    if (flag > 0)
                    {
                        workbook.SaveDocument(excelDestinationPath);
                    }
                    else
                    {
                        File.Delete(excelDestinationPath);
                    }
                }//foreach (CheckedListBoxItem item in chkModel.Items) 指標
                #endregion

                //if (FlagAdmin)
                //   System.Diagnostics.Process.Start(excelDestinationPath);

                return(ResultStatus.Success);
            } catch (Exception ex) {
                WriteLog(ex);
            } finally {
                panFilter.Enabled = panSecond.Enabled = true;
                labMsg.Text       = "";
                labMsg.Visible    = false;
            }
            return(ResultStatus.Fail);
        }
Example #4
0
        protected override ResultStatus Export()
        {
            try {
                #region 輸入&日期檢核
                string       lsRtn = PbFunc.f_get_jsw(_ProgramID, "E", txtEndDate.Text);
                DialogResult liRtn;

                if (lsRtn != "Y")
                {
                    liRtn = MessageDisplay.Choose(string.Format("{0} 統計資料未轉入完畢,是否要繼續?", txtEndDate.Text), MessageBoxDefaultButton.Button2, GlobalInfo.QuestionText);
                    if (liRtn == DialogResult.No)
                    {
                        labMsg.Visible = false;
                        Cursor.Current = Cursors.Arrow;
                        return(ResultStatus.Fail);
                    } //if (liRtn == DialogResult.Yes)
                }     //if (lsRtn != "Y")

                //if (!txtStartDate.IsDate(txtStartDate.Text , CheckDate.Start)
                //      || !txtEndDate.IsDate(txtEndDate.Text , CheckDate.End)) {
                //   return ResultStatus.Fail; ;
                //}

                if (string.Compare(txtStartDate.Text, txtEndDate.Text) > 0)
                {
                    MessageDisplay.Error(CheckDate.Datedif, GlobalInfo.ErrorText);
                    return(ResultStatus.Fail);;
                }
                #endregion

                //1. ready
                panFilter.Enabled = false;
                labMsg.Visible    = true;
                labMsg.Text       = "開始轉檔...";
                this.Cursor       = Cursors.WaitCursor;
                this.Refresh();
                Thread.Sleep(5);

                //2. copy template xls to target path
                string excelDestinationPath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);

                //3. open xls
                Workbook workbook = new Workbook();
                workbook.LoadDocument(excelDestinationPath);

                //4. write data
                int  row;
                bool res1 = false, res2 = false, res3 = false;
                row  = 3;
                res1 = wf_30410(workbook, SheetNo.tradeSum, row);

                row  = 4; //PB這邊帶1,但進去後帶回4
                res2 = wf_30411(workbook, SheetNo.tradeDetail, row);

                row  = 4; //PB這邊帶1,但進去後帶回4
                res3 = wf_30412(workbook, SheetNo.oint, row);

                if (!res1 && !res2 && !res3)
                {
                    File.Delete(excelDestinationPath);
                    return(ResultStatus.Fail);
                }

                //5. save
                workbook.SaveDocument(excelDestinationPath);
                labMsg.Visible = false;

                if (FlagAdmin)
                {
                    System.Diagnostics.Process.Start(excelDestinationPath);
                }

                return(ResultStatus.Success);
            } catch (Exception ex) {
                WriteLog(ex);
            } finally {
                panFilter.Enabled = true;
                labMsg.Text       = "";
                labMsg.Visible    = false;
                this.Cursor       = Cursors.Arrow;
            }
            return(ResultStatus.Fail);
        }
Example #5
0
        protected override ResultStatus Export()
        {
            base.Export();

            #region 確認統計資料未轉入完畢
            string       lsRtn = PbFunc.f_get_jsw(_ProgramID, "E", txtDate.Text);
            DialogResult liRtn;

            if (lsRtn != "Y")
            {
                liRtn = MessageDisplay.Choose(String.Format("{0} 統計資料未轉入完畢,是否要繼續?", txtDate.Text));
                if (liRtn == DialogResult.No)
                {
                    labMsg.Visible = false;
                    Cursor.Current = Cursors.Arrow;
                    return(ResultStatus.Fail);
                }
            }//if (lsRtn != "Y")
            #endregion

            try {
                //1. 判斷為單一商品還是全部
                DataTable dt       = dao40130.GetDataList(txtDate.DateTimeValue, "%");
                DataTable dtSelect = new DataTable();
                string    kindId   = dwKindId.EditValue.AsString();
                if (kindId != "%")
                {
                    dtSelect = dt.Filter("mgt2_kind_id = '" + kindId + "'");
                }
                else //kindId = "%"
                {
                    dtSelect = dt.Filter("mgt2_kind_id <> '%'");
                }

                //1.1 準備開檔
                string   originalFilePath    = "";
                string   destinationFilePath = "";
                Workbook workbook            = new Workbook();

                //2. 填資料
                foreach (DataRow dr in dtSelect.Rows)
                {
                    string fileKind = dr["mgt2_kind_id"].AsString();

                    //2.1 開啟&複製檔案(因可能有多個excel所以在迴圈裡兜路徑)
                    originalFilePath = Path.Combine(GlobalInfo.DEFAULT_EXCEL_TEMPLATE_DIRECTORY_PATH,
                                                    string.Format("{0}.{1}", _ProgramID, FileType.XLS.ToString().ToLower()));
                    destinationFilePath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH,
                                                       string.Format("{0}({1})_{2}.{3}", _ProgramID, fileKind, DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss"), FileType.XLS.ToString().ToLower()));

                    File.Copy(originalFilePath, destinationFilePath, true);
                    workbook.LoadDocument(destinationFilePath);
                    kindId = dr["mgt2_kind_id"].AsString() + "%";
                    string tmpKindId = kindId.SubStr(2, 1);
                    if (tmpKindId == "F") //Sheet:期貨data
                    {
                        wf_40131(workbook, kindId);
                    }
                    else //Sheet:選擇權data
                    {
                        wf_40132(workbook, kindId);
                    }

                    //3. save
                    workbook.SaveDocument(destinationFilePath);
                }//foreach (DataRow dr in dt.Rows)

                labMsg.Visible = false;

                return(ResultStatus.Success);
            } catch (Exception ex) {
                WriteLog(ex);
            } finally {
                panFilter.Enabled = true;
                labMsg.Text       = "";
                labMsg.Visible    = false;
                this.Cursor       = Cursors.Arrow;
            }
            return(ResultStatus.Fail);
        }