Exemplo n.º 1
0
        private void GenerateIARI()
        {
            ExcelApp excelApp = new ExcelApp(false, false);

            if (excelApp.ExcelAppInstance == null)
            {
                string msg = "Excel could not be started. Check that your office installation and project reference are correct !!!";
                Logger.Log(msg, Logger.LogType.Error);
                return;
            }

            try
            {
                Workbook  wBook  = ExcelUtil.CreateOrOpenExcelFile(excelApp, issueAssetReIssue);
                Worksheet wSheet = wBook.Worksheets[1] as Worksheet;

                if (wSheet == null)
                {
                    string msg = "Excel Worksheet could not be started. Check that your office installation and project reference are correct !!!";
                    Logger.Log(msg, Logger.LogType.Error);
                    return;
                }

                FillExcelTitleIARI(wSheet);
                FillExcelBodyIARI(wSheet, listQuaNot);
                excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
                wBook.Save();
                TaskResultList.Add(new TaskResultEntry("HKWarrantReIssueHistory ", "FutrueIssue", issueAssetReIssue));
            }
            catch (Exception ex)
            {
                string msg = "Error found Generate IARA file :" + ex.ToString();
                Logger.Log(msg, Logger.LogType.Error);
            }
            finally
            {
                excelApp.Dispose();
            }
        }
        //Generate broker3h file
        public void GenerateBroker3hFile(List <BrokerInfo> brokerList)
        {
            string broker3hFilePath = MiscUtil.BackupFileWithNewName(configObj.BROKER3H_FILE_PATH);

            if (File.Exists(configObj.CHINESE_VERSION_FILE_PATH))
            {
                string ChineseVersionFileBakFilePath = MiscUtil.BackupFileWithNewName(configObj.CHINESE_VERSION_FILE_PATH);
            }
            using (ExcelApp app = new ExcelApp(false, false))
            {
                var workbook = ExcelUtil.CreateOrOpenExcelFile(app, broker3hFilePath);
                if (workbook.ReadOnly == true)
                {
                    workbook.ReadOnlyRecommended = false;
                }
                int brokerRowsSum = 0;
                Logger.Log("**************************************************************************");

                //Copy the content in Master sheet in Insert sheet
                Logger.Log("Begin to Generate the Insert Sheet");
                GenerateInsertSheet(workbook, brokerRowsSum, brokerList);

                Logger.Log("**************************************************************************");

                //Generate Chinese version File
                workbook.SaveCopyAs(configObj.CHINESE_VERSION_FILE_PATH);
                TaskResultList.Add(new TaskResultEntry("Broker3h_Chinese.xls", "Chinese Version Broker3h file", configObj.CHINESE_VERSION_FILE_PATH));
                UpdateCColAsEnglish(brokerList, ExcelUtil.GetWorksheet(configObj.MASTER_WORKSHEET_NAME, workbook));
                UpdateDColAsEnglish(brokerList, ExcelUtil.GetWorksheet(configObj.MASTER_WORKSHEET_NAME, workbook));
                UpdateCColAsEnglish(brokerList, ExcelUtil.GetWorksheet(configObj.INSERT_WORKSHEET_NAME, workbook));
                UpdateDColAsEnglish(brokerList, ExcelUtil.GetWorksheet(configObj.INSERT_WORKSHEET_NAME, workbook));
                UpdatePageId(ExcelUtil.GetWorksheet(configObj.INSERT_WORKSHEET_NAME, workbook));

                workbook.SaveCopyAs(configObj.BROKER3H_FILE_PATH);
                TaskResultList.Add(new TaskResultEntry("Broker3h.xls", "English Version Broker3h file", configObj.BROKER3H_FILE_PATH));
                workbook.Close(false, workbook.FullName, false);
            }
        }
Exemplo n.º 3
0
 private void GenerateFile(List <List <string> > listList, string strSaveFilePath)
 {
     if (listList != null)
     {
         string content = string.Empty;
         foreach (var str in listList)
         {
             content += string.Format("{0}\t", str[0]);
             content += string.Format("{0}\t", str[1]);
             content += string.Format("{0}\t", str[2]);
             content += "\r\n";
         }
         try
         {
             File.WriteAllText(strSaveFilePath, content);
             TaskResultList.Add(new TaskResultEntry("IDN file", "IDN", strSaveFilePath));
         }
         catch (Exception ex)
         {
             Logger.Log(string.Format("Error happens when generating file. Ex: {0} .", ex.Message));
         }
     }
 }
Exemplo n.º 4
0
        private void GenerateFile(List <string> list, string path)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("MissingRIC\r\n");

            foreach (var str in list)
            {
                sb.Append(str);
                sb.Append("\r\n");
            }

            try
            {
                File.WriteAllText(path, sb.ToString());
                TaskResultList.Add(new TaskResultEntry("TWipoQC", "MissingRIC", path));
            }
            catch (Exception ex)
            {
                string msg = string.Format("Error happens when generating file. Ex: {0} .", ex.Message);
                Logger.Log(msg, Logger.LogType.Error);
            }
        }
Exemplo n.º 5
0
        private void GenerateGedaFile(List <string> rics)
        {
            try
            {
                string fileName = string.Format("KR_DROP_{0}.txt", DateTime.Today.ToString("yyyyMMdd"));

                string filePath = Path.Combine(configObj.BulkFile, fileName);

                FileUtil.WriteOutputFile(filePath, rics, "RIC", WriteMode.Overwrite);

                TaskResultList.Add(new TaskResultEntry("Output Folder", "Output Folder", configObj.BulkFile));

                TaskResultList.Add(new TaskResultEntry(fileName, fileName, filePath, FileProcessType.GEDA_BULK_CHAIN_RIC_DELETION));

                Logger.Log("Generate drop GEDA file. OK!");
            }
            catch (Exception ex)
            {
                string msg = "Error found in generating GEDA drop file. Error:" + ex.Message;
                Logger.Log(msg, Logger.LogType.Error);
                throw ex;
            }
        }
Exemplo n.º 6
0
        public void GenerateIndiceRicFile(Dictionary <string, List <string> > indicesRicDic)
        {
            using (ExcelApp app = new ExcelApp(false, false))
            {
                int startPos = 0;
                var workbook = ExcelUtil.CreateOrOpenExcelFile(app, configObj.TargetIndicesAdjustmentFileName);
                int sheetNum = (indicesRicDic.Keys.Count + configObj.ChainNumPerSheet - 1) / configObj.ChainNumPerSheet;
                if (sheetNum > workbook.Worksheets.Count)
                {
                    workbook.Worksheets.Add(Missing.Value, Missing.Value, sheetNum - workbook.Worksheets.Count, Missing.Value);
                }

                for (int i = 0; i < sheetNum; i++)
                {
                    var worksheet = workbook.Worksheets[i + 1] as Worksheet;
                    startPos = i * configObj.ChainNumPerSheet;
                    int endPos = indicesRicDic.Keys.Count < (startPos + configObj.ChainNumPerSheet) ? indicesRicDic.Keys.Count : (startPos + configObj.ChainNumPerSheet);
                    WriterWorksheet(worksheet, indicesRicDic, startPos, endPos);
                }

                TaskResultList.Add(new TaskResultEntry("New Indices File", "", workbook.FullName));
                workbook.Close(true, workbook.FullName, true);
            }
        }
        //Get the official code of which ISIN needs updated
        public List <RicISINInfo> GetAllNewGenerateISINList()
        {
            List <RicISINInfo> ricInfoList = new List <RicISINInfo>();
            Range nameRange;
            Range isinRange;
            Range ricRange;
            int   lastUsedRow;

            File.Copy(configObj.SourceFilePath, NewTargetFilePath(configObj.SourceFilePath));

            using (ExcelApp app = new ExcelApp(false, false))
            {
                var workbook = ExcelUtil.CreateOrOpenExcelFile(app, configObj.SourceFilePath);

                //Get Equity ric from "Equity" worksheet
                var equityWorksheet = ExcelUtil.GetWorksheet(configObj.EquityWorksheet, workbook);
                if (equityWorksheet == null)
                {
                    Logger.LogErrorAndRaiseException(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.EquityWorksheet, workbook.Name));
                }

                lastUsedRow = equityWorksheet.UsedRange.Row + equityWorksheet.UsedRange.Rows.Count - 1;
                for (int i = 1; i <= lastUsedRow; i++)
                {
                    nameRange = ExcelUtil.GetRange(i, 20, equityWorksheet);
                    isinRange = ExcelUtil.GetRange(i, 19, equityWorksheet);
                    ricRange  = ExcelUtil.GetRange(i, 3, equityWorksheet);
                    if ((nameRange.Value2 != null && nameRange.Value2.ToString() != string.Empty) && (isinRange.Value2 == null || isinRange.Value2.ToString() == string.Empty))
                    {
                        RicISINInfo ricInfo = new RicISINInfo
                        {
                            type         = "Equity",
                            OfficialCode = ExcelUtil.GetRange(i, 6, equityWorksheet).Value2.ToString(),
                            Name         = nameRange.Value2.ToString()
                        };
                        ricInfo.ISIN = GetISINFromCode(ricInfo.OfficialCode);
                        ricInfo.ric  = ricRange.Value2.ToString();
                        if (ricInfo.ISIN != string.Empty)
                        {
                            isinRange.Value2 = ricInfo.ISIN;
                            ricInfoList.Add(ricInfo);
                        }
                    }
                }



                //Get bond ric from "Bond" worksheet

                List <RicISINInfo> allBondISINList = GetAllISINForGoverBond();
                var bondWorksheet = ExcelUtil.GetWorksheet(configObj.BondWorksheet, workbook);
                if (bondWorksheet == null)
                {
                    Logger.LogErrorAndRaiseException(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.BondWorksheet, workbook.Name));
                }

                lastUsedRow = bondWorksheet.UsedRange.Row + bondWorksheet.UsedRange.Rows.Count - 1;
                for (int j = 1; j <= lastUsedRow; j++)
                {
                    nameRange = ExcelUtil.GetRange(j, 22, bondWorksheet);
                    isinRange = ExcelUtil.GetRange(j, 21, bondWorksheet);
                    ricRange  = ExcelUtil.GetRange(j, 3, bondWorksheet);
                    if ((nameRange.Value2 != null && nameRange.Value2.ToString() != string.Empty) && ((isinRange.Value2 == null) || (isinRange.Value2.ToString() == string.Empty)))
                    {
                        RicISINInfo ricInfo = new RicISINInfo
                        {
                            type         = "Bond",
                            Name         = nameRange.Value2.ToString(),
                            OfficialCode = ExcelUtil.GetRange(j, 5, bondWorksheet).Value2.ToString(),
                            ric          = ricRange.Value2.ToString()
                        };
                        ricInfo.ISIN = !ricInfo.Name.Contains("國債") ? GetISINFromCode(ricInfo.OfficialCode) : GetISINFromName(ricInfo.Name, allBondISINList);

                        if (ricInfo.ISIN != string.Empty)
                        {
                            ricInfoList.Add(ricInfo);
                            isinRange.Value2 = ricInfo.ISIN;
                        }
                    }
                }
                TaskResultList.Add(new TaskResultEntry("Updated Source File", "All the newly generated ISIN had been updated into the file.", workbook.FullName));
                workbook.Close(true, workbook.FullName, true);
            }

            return(ricInfoList);
        }
Exemplo n.º 8
0
        private void GenerateMAINSSExcelAndXmlFile(string xlsFilePath, TradingInfo tradingInfo)
        {
            xlsFilePath = MiscUtil.BackUpFileWithDateFolder(xlsFilePath, false); //MiscUtil.BackupFileWithNewName(xlsFilePath);
            List <string> linesToWrite = new List <string>();

            foreach (StockInfo stockInfo in tradingInfo.StockList)
            {
                linesToWrite.Add(stockInfo.ToSingleLine());
            }
            linesToWrite.Add("");
            if (!string.IsNullOrEmpty(tradingInfo.DesignatedSecuritiesRecordingSum))
            {
                linesToWrite.Add(tradingInfo.DesignatedSecuritiesRecordingSum.PadLeft(recordAlignrightLength));
            }
            if (!string.IsNullOrEmpty(tradingInfo.DesignatedSharesShortSoldSum))
            {
                linesToWrite.Add(tradingInfo.DesignatedSharesShortSoldSum.PadLeft(recordAlignrightLength));
            }
            if (!string.IsNullOrEmpty(tradingInfo.DesignatedShortSellTurnoverShares))
            {
                linesToWrite.Add(tradingInfo.DesignatedShortSellTurnoverShares.PadLeft(recordAlignrightLength));
            }
            if (!string.IsNullOrEmpty(tradingInfo.DesignatedShortSellTurnoverValue))
            {
                linesToWrite.Add(tradingInfo.DesignatedShortSellTurnoverValue.PadLeft(recordAlignrightLength));
            }
            if (!string.IsNullOrEmpty(tradingInfo.HKDTurnoverValue))
            {
                linesToWrite.Add(tradingInfo.HKDTurnoverValue.PadLeft(recordAlignrightLength));
            }

            using (ExcelApp app = new ExcelApp(false, false))
            {
                var workbook  = ExcelUtil.CreateOrOpenExcelFile(app, xlsFilePath);
                var worksheet = ExcelUtil.GetWorksheet(configObj.HKMAINSS_CONFIG_Worksheet, workbook);
                if (worksheet == null)
                {
                    LogMessage(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.HKGEMSS_Worksheet, workbook.Name));
                }

                //Generate MAINSSExcelFile
                using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Down))
                {
                    // Allocate spaces
                    int startOffset = (configObj.HKMAINSS_CONFIG_StartPage - 1) * configObj.HKMAINSS_CONFIG_LineNum;
                    int startRow    = startOffset + 1;
                    int curLineNum  = 0;

                    int insertBlankPageNum = (linesToWrite.Count - 1) / configObj.HKMAINSS_CONFIG_LineEach + 1;
                    ExcelUtil.InsertBlankRows(ExcelUtil.GetRange(startRow, 1, worksheet), insertBlankPageNum * 24);
                    writer.PlaceNext(startRow, 1);

                    // Form 1st line and write
                    string        line           = string.Empty;
                    List <string> headerLineList = new List <string>();
                    headerLineList.Add(tradingInfo.DateStr);
                    line = "RIC         STOCK NAME                          SHARES        TURNOVER ($)";
                    headerLineList.Add(line);
                    line = "=========   ====================       ===============     ===============";
                    headerLineList.Add(line);

                    for (int i = 0; i < insertBlankPageNum; i++)
                    {
                        int curStartRow  = startOffset + i * configObj.HKMAINSS_CONFIG_LineNum + 1;
                        int nextStartRow = curStartRow + 24;
                        writer.WriteLine(headerLineList[0]);
                        writer.WriteLine(headerLineList[1]);
                        writer.WriteLine(headerLineList[2]);
                        //keep a line without text.
                        writer.MoveNext();

                        while (writer.Row < nextStartRow && curLineNum < linesToWrite.Count)
                        {
                            writer.WriteLine(linesToWrite[curLineNum]);
                            curLineNum++;
                        }

                        writer.PlaceNext(nextStartRow, 1);
                    }

                    // Fill designated securities summary with page no. and background color.
                    for (int page = configObj.HKMAINSS_CONFIG_StartPage; page <= configObj.HKMAINSS_CONFIG_TotalPage; page++)
                    {
                        string id      = "HK/MAINSS" + page.ToString("D2");
                        int    upRow   = configObj.HKMAINSS_CONFIG_LineNum * (page - 1) + 1;
                        int    downRow = upRow + (configObj.HKMAINSS_CONFIG_LineNum - 1);
                        writer.PlaceNextAndWriteLine(upRow, 2, id);
                        writer.PlaceNextAndWriteLine(downRow, 2, id);
                        Range pageRange = ExcelUtil.GetRange(upRow, 2, downRow, 2, worksheet);
                        if (page % 2 == 0)
                        {
                            pageRange.Interior.Color = 16777164.0;
                        }
                        else
                        {
                            pageRange.Interior.Color = 10092543.0;
                        }
                    }

                    Range rangToRemove = ExcelUtil.GetRange(writer.Row, 1, writer.Row + 24 * insertBlankPageNum, 2, worksheet);
                    rangToRemove.Clear();
                }

                //Generate MAINSSXmlFile
                ProductionXmlFileTemplate xmlFileTem = GetHKMAINSSXmlFileContent(worksheet);
                ConfigUtil.WriteXml(configObj.HKMAINSS_CONFIG_XmlPath, xmlFileTem);
                TaskResultList.Add(new TaskResultEntry("XML file for HKMAINSS", "", configObj.HKMAINSS_CONFIG_XmlPath));


                if (File.Exists(configObj.HKMAINSS_CONFIG_Workbook))
                {
                    File.Delete(configObj.HKMAINSS_CONFIG_Workbook);
                }
                workbook.SaveCopyAs(configObj.HKMAINSS_CONFIG_Workbook);
                TaskResultList.Add(new TaskResultEntry("HKMAINSS", "", configObj.HKMAINSS_CONFIG_Workbook));
                workbook.Close(false, xlsFilePath, true);
            }
        }
Exemplo n.º 9
0
        /// <summary>
        /// Generate the HKMAIN01_10Excel file and the HKMAIN01_10Xml file.
        /// </summary>
        /// <param name="main01_10TradingInfo">the Main01_10 trading News Info.</param>
        public void GenerateMain01_10ExcelAndXMLFile(TradingInfo main01_10TradingInfo)
        {
            string main01_10FilePath = MiscUtil.BackUpFileWithDateFolder(configObj.HKMAIN01_10_Workbook, false); //MiscUtil.BackupFileWithNewName(configObj.HKMAIN01_10_Workbook);

            using (ExcelApp app = new ExcelApp(true, false))
            {
                var workbook  = ExcelUtil.CreateOrOpenExcelFile(app, main01_10FilePath);
                var worksheet = ExcelUtil.GetWorksheet(configObj.HKMAIN01_10_Worksheet, workbook);
                if (worksheet == null)
                {
                    LogMessage(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.HKMAIN01_10_Worksheet, workbook.Name));
                }

                using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right))
                {
                    // Update the date
                    string line = string.Empty;
                    writer.WriteLine(main01_10TradingInfo.DateStr);

                    //Copy from line 4
                    writer.PlaceNext(4, 1);
                    foreach (StockInfo stock in main01_10TradingInfo.StockList)
                    {
                        writer.WriteLine(stock.Ric);
                        writer.WriteLine(stock.StockName);
                        writer.WriteLine(stock.Shares);
                        writer.WriteLine(stock.Turnover);
                        writer.PlaceNext(writer.Row + 1, 1);
                    }
                    int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
                    ExcelUtil.GetRange(writer.Row, writer.Col, lastUsedRow, writer.Col + 4, worksheet).Clear();

                    writer.PlaceNext(writer.Row + 2, 1);
                    writer.WriteLine(main01_10TradingInfo.DesignatedSecuritiesRecordingSum);

                    writer.PlaceNext(writer.Row + 1, 1);
                    writer.WriteLine(main01_10TradingInfo.DesignatedSharesShortSoldSum);

                    writer.PlaceNext(writer.Row + 1, 1);
                    writer.WriteLine(main01_10TradingInfo.DesignatedShortSellTurnoverShares);

                    writer.PlaceNext(writer.Row + 1, 1);
                    writer.WriteLine(main01_10TradingInfo.DesignatedShortSellTurnoverValue);

                    writer.PlaceNext(writer.Row + 1, 1);
                    writer.WriteLine(main01_10TradingInfo.HKDTurnoverValue);

                    ExcelUtil.GetRange(1, 1, writer.Row - 1, 4, worksheet).Borders.LineStyle = 0;
                }

                ProductionXmlFileTemplate xmlFileTem = GetHKMAINXmlFileContent(worksheet);
                ConfigUtil.WriteXml(configObj.HKMAIN01_10_XmlPath, xmlFileTem);
                TaskResultList.Add(new TaskResultEntry("XML file for HK-MAIN01-10", "", configObj.HKMAIN01_10_XmlPath));

                //Save the HK-MAIN01-10 file as a copy

                if (File.Exists(configObj.HKMAIN01_10_Workbook))
                {
                    File.Delete(configObj.HKMAIN01_10_Workbook);
                }
                app.ExcelAppInstance.AlertBeforeOverwriting = false;
                workbook.SaveCopyAs(configObj.HKMAIN01_10_Workbook);

                TaskResultList.Add(new TaskResultEntry("HKMAINSS01-10", "", configObj.HKMAIN01_10_Workbook));
                workbook.Close(false, main01_10FilePath, true);
            }
        }
Exemplo n.º 10
0
        /// <summary>
        /// Update HK-GEMSS.xls file and create HK-GEMSS.xml
        /// </summary>
        /// <param name="gemTradingInfo">the GEM trading News Info.</param>
        public void UpdateGemssFileAndGenerateXMLFile(TradingInfo gemTradingInfo)
        {
            string gemssFilePath = MiscUtil.BackUpFileWithDateFolder(configObj.HKGEMSS_Workbook, false);

            using (ExcelApp app = new ExcelApp(false, false))
            {
                var workbook  = ExcelUtil.CreateOrOpenExcelFile(app, gemssFilePath);
                var worksheet = ExcelUtil.GetWorksheet(configObj.HKGEMSS_Worksheet, workbook);
                if (worksheet == null)
                {
                    LogMessage(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.HKGEMSS_Worksheet, workbook.Name));
                }

                using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Down))
                {
                    // Allocate spaces
                    ExcelUtil.InsertBlankRows(ExcelUtil.GetRange("A1", worksheet), 24);
                    writer.PlaceNext(1, 1);

                    // Form 1st line and write
                    string line = string.Empty;
                    writer.WriteLine(gemTradingInfo.DateStr);

                    // Copy 2nd and 3rd line from line 26 and line 27
                    writer.WriteLine(worksheet.Cells[writer.Row + 24, writer.Col]);
                    writer.WriteLine(worksheet.Cells[writer.Row + 24, writer.Col]);

                    if (gemTradingInfo.StockList == null)
                    {
                        writer.WriteLine("NIL");
                        while (writer.Row < 19)
                        {
                            writer.WriteLine(" ");
                        }
                    }
                    else
                    {
                        // Fill stock info
                        foreach (StockInfo stockInfo in gemTradingInfo.StockList)
                        {
                            StringBuilder sb = new StringBuilder();
                            sb.Append(stockInfo.Ric.PadRight(12));
                            sb.Append(stockInfo.StockName.PadRight(27));
                            sb.Append(stockInfo.Shares.PadLeft(15));
                            sb.Append(stockInfo.Turnover.PadLeft(20));
                            line = sb.ToString();
                            writer.WriteLine(line);
                        }
                        while (writer.Row < 19)
                        {
                            writer.WriteLine(" ");
                        }

                        //Fill non-designated securities summary if non-designated securities exist
                        writer.PlaceNext(19, 1);
                        if (!string.IsNullOrEmpty(gemTradingInfo.NonDesignatedSecuritiesRecordingSum) && gemTradingInfo.NonDesignatedSecuritiesRecordingSum != "0")
                        {
                            writer.WriteLine(gemTradingInfo.NonDesignatedSecuritiesRecordingSum.PadLeft(recordAlignrightLength));
                            writer.WriteLine(gemTradingInfo.NonDesignatedSharesShortSoldSum.PadLeft(recordAlignrightLength));
                            writer.WriteLine(gemTradingInfo.NonDesignatedShortSellTransactionSum.PadLeft(recordAlignrightLength));
                        }
                        // Fill designated securities summary
                        writer.WriteLine(gemTradingInfo.DesignatedSecuritiesRecordingSum.PadLeft(recordAlignrightLength));
                        writer.WriteLine(gemTradingInfo.DesignatedSharesShortSoldSum.PadLeft(recordAlignrightLength));
                        writer.WriteLine(gemTradingInfo.DesignatedShortSellTurnoverShares.PadLeft(recordAlignrightLength));
                        writer.WriteLine(gemTradingInfo.DesignatedShortSellTurnoverValue.PadLeft(recordAlignrightLength));
                        if (!string.IsNullOrEmpty(gemTradingInfo.HKDTurnoverValue))
                        {
                            writer.WriteLine(gemTradingInfo.HKDTurnoverValue.PadLeft(recordAlignrightLength));
                        }
                    }

                    for (int page = 1; page <= 25; page++)
                    {
                        string id      = "HK/GEMSS" + page.ToString("D2");
                        int    upRow   = 24 * (page - 1) + 1;
                        int    downRow = upRow + 23;
                        writer.PlaceNextAndWriteLine(upRow, 3, id);
                        writer.PlaceNextAndWriteLine(downRow, 3, id);
                    }

                    //Fill color for C columns
                    Range range = ExcelUtil.GetRange(1, 3, 24, 3, worksheet);
                    range.Interior.Color = ExcelUtil.GetRange(49, 3, worksheet).Interior.Color;

                    ProductionXmlFileTemplate xmlFileTem = GetGemssXmlFileContent(worksheet);
                    ConfigUtil.WriteXml(configObj.HKGEMSS_XmlPath, xmlFileTem);
                    TaskResultList.Add(new TaskResultEntry("XML file for HK-GEMSS", "", configObj.HKGEMSS_XmlPath));


                    if (File.Exists(configObj.HKGEMSS_Workbook))
                    {
                        File.Delete(configObj.HKGEMSS_Workbook);
                    }
                    //Save files as a copy
                    workbook.SaveCopyAs(configObj.HKGEMSS_Workbook);
                    TaskResultList.Add(new TaskResultEntry("HK-GEMSS", "", configObj.HKGEMSS_Workbook));
                    //Close current opend excel file
                    workbook.Close(false, gemssFilePath, false);
                }
            }
        }
Exemplo n.º 11
0
        private void PrintFurtherIssueToExcel(List <FurtherIssueModel> fList)
        {
            System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
            ExcelApp excelApp = new ExcelApp(false, false);

            if (excelApp.ExcelAppInstance == null)
            {
                string msg = "Excel could not be started. Check that your office installation and project reference correct!!!";
                Logger.Log(msg, Logger.LogType.Error);
                return;
            }

            try
            {
                string filename = fList.Aggregate("KR FM (Further Issue) _ ", (current, item) => current + (item.New_Ric + ","));
                filename = filename.Trim(new[] { ' ', ',' }) + "(wef " + Convert.ToDateTime(fList[0].Effective_Date).ToString("yyyy-MMM-dd", new CultureInfo("en-US")) + ").xls";

                //rename file if the length of file name more than 218 chars
                if (filename.Length > 218)
                {
                    filename = "KR FM (Further Issue) _ (wef " + Convert.ToDateTime(fList[0].Effective_Date).ToString("yyyy-MMM-dd", new CultureInfo("en-US")) + ").xls";
                }

                string    ipath  = Path.Combine(configObj.FM_FurtherIssuer, filename);
                Workbook  wBook  = ExcelUtil.CreateOrOpenExcelFile(excelApp, ipath);
                Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
                if (wSheet == null)
                {
                    string msg = "Error found in PrintFurtherIssueToExcel :(WorkSheet could not be created. Check that your office installation and project reference correct!!!)";
                    Logger.Log(msg, Logger.LogType.Error);
                    return;
                }

                GenerateExcelFileTitle(wSheet);

                int startLine = 5;
                foreach (var item in fList)
                {
                    ((Range)wSheet.Cells[startLine, 1]).NumberFormat = "@";
                    wSheet.Cells[startLine, 1] = item.Updated_Date;
                    ((Range)wSheet.Cells[startLine, 2]).NumberFormat = "@";
                    wSheet.Cells[startLine, 2] = Convert.ToDateTime(item.Effective_Date).ToString("dd-MMM-yy", new CultureInfo("en-US"));
                    wSheet.Cells[startLine, 3] = item.Old_Ric;
                    wSheet.Cells[startLine, 4] = item.New_Ric;
                    wSheet.Cells[startLine, 5] = item.Old_Isin;
                    wSheet.Cells[startLine, 6] = item.New_Isin;
                    ((Range)wSheet.Cells[startLine, 7]).NumberFormat = "@";
                    wSheet.Cells[startLine, 7] = item.Old_Ticker;
                    ((Range)wSheet.Cells[startLine, 8]).NumberFormat = "@";
                    wSheet.Cells[startLine, 8]  = item.New_Ticker;
                    wSheet.Cells[startLine, 9]  = item.Old_Quanity;
                    wSheet.Cells[startLine, 10] = item.New_Quanity;
                    startLine++;
                }
                excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
                wBook.Save();
                TaskResultList.Add(new TaskResultEntry(Path.GetFileNameWithoutExtension(ipath), "", ipath, creatMail(fList)));
            }
            catch (Exception ex)
            {
                string msg = "Error found in PrintFurtherIssueToExcel : " + ex.ToString();
                Logger.Log(msg, Logger.LogType.Error);
            }
            finally
            {
                excelApp.Dispose();
            }
        }
Exemplo n.º 12
0
        private void GenerateFiles(List <ETFinsertTaskTemplate> res)
        {
            DateTime      lastTradingDay = MiscUtil.GetLastTradingDay(DateTime.Now, _holidayList, 1);
            StringBuilder sb             = new StringBuilder();
            string        fileContents   = "";

            sb.Append("TQSK.DAT;21-MAR-2012 15:00:00;TPS;\r\n");
            sb.Append("GEN_VAL6;GV6_TEXT;\r\n");
            foreach (var term in res)
            {
                if (term.Code.Equals("006201"))
                {
                    sb.AppendFormat("{0}.TWO;{1};144700;\r\n", term.Code, term.EstimatedValue);
                }
                else
                {
                    sb.AppendFormat("{0}.TW;{1};144700;\r\n", term.Code, term.EstimatedValue);
                }
            }
            sb.Append("\r\n\r\n\r\n\r\n\r\n");
            fileContents += sb.ToString();
            sb.Remove(0, sb.Length);
            //file one

            sb.Append("TAM.DAT;21-MAR-2012 15:00:00;TPS;\r\n");
            sb.Append("GEN_VAL7;\r\n");
            foreach (var term in from term in res
                     let @where = string.Format("where Code='{0}' and GrabTime='{1}' and EstimatedValue='{2}'", term.Code, lastTradingDay.ToString("yyyy/MM/dd", new CultureInfo("en-US")), term.EstimatedValue)
                                  let table = ManagerBase.Select(TableName, new[] { "*" }, @where)
                                              where table.Rows.Count == 0
                                              select term)
            {
                if (term.Code.Equals("006201"))
                {
                    sb.AppendFormat("{0}.TWO;{1};\r\n", term.Code, term.ReferenceValue);
                }
                else
                {
                    sb.AppendFormat("{0}.TW;{1};\r\n", term.Code, term.ReferenceValue);
                }
            }
            sb.Append("\r\n\r\n\r\n\r\n\r\n");
            fileContents += sb.ToString();
            string file2 = sb.ToString();

            sb.Remove(0, sb.Length);
            //file two

            sb.Append("TAM.DAT;21-MAR-2012 15:00:00;TPS;\r\n");
            sb.Append("SPARE_DATE5;\r\n");
            foreach (var term in res)
            {
                if (term.Code.Equals("006201"))
                {
                    sb.AppendFormat("{0}.TWO;{1};\r\n", term.Code, DateTime.Now.ToString("dd/MM/yyyy"));
                }
                else
                {
                    sb.AppendFormat("{0}.TW;{1};\r\n", term.Code, DateTime.Now.ToString("dd/MM/yyyy"));
                }
            }
            sb.Append("\r\n\r\n\r\n\r\n\r\n");
            fileContents += sb.ToString();
            sb.Remove(0, sb.Length);
            //file three

            sb.Append("TAM.DAT;21-MAR-2012 15:00:00;TPS;\r\n");
            sb.Append("SPARE_DATE5;\r\n");

            sb.AppendFormat("006201.TWO;{0};\r\n", lastTradingDay.ToString("dd/MM/yyyy"));
            //sb.AppendFormat("006202.TWO;{0};\r\n", lastTradingDay.ToString("dd/MM/yyyy"));
            sb.Append("\r\n\r\n\r\n\r\n\r\n");
            fileContents += sb.ToString();
            sb.Remove(0, sb.Length);
            //file four

            string path = string.Format("{0}\\TQSK RICs.txt", _configObj.ResultFileDir);

            File.WriteAllText(path, fileContents, Encoding.UTF8);
            AddResult(Path.GetFileNameWithoutExtension(path), path, "file");
            TaskResultList.Add(new TaskResultEntry(Path.GetFileNameWithoutExtension(path), path, path, CreateMailOne(file2)));
        }
Exemplo n.º 13
0
        private void GeneratePageUpdateFile(string templateFilePath, System.Data.DataTable dt)
        {
            using (ExcelApp app = new ExcelApp(false, false))
            {
                var workbook  = ExcelUtil.CreateOrOpenExcelFile(app, templateFilePath);
                var worksheet = ExcelUtil.GetWorksheet("INPUT SHEET", workbook);
                if (worksheet == null)
                {
                    Logger.LogErrorAndRaiseException(string.Format("There's no worksheet: {0}", worksheet.Name));
                }
                ((Range)worksheet.Columns["D"]).NumberFormat = "@";
                using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 3, 1, ExcelLineWriter.Direction.Right))
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        writer.WriteLine("Create");
                        writer.WriteLine("");
                        writer.WriteLine(Convert.ToString(dr["ChangeType"]));
                        string effectiveDate = Convert.ToDateTime(dr["EffectiveDate"]).ToString("ddMMMyy");
                        writer.WriteLine(effectiveDate);
                        writer.WriteLine(Convert.ToString(dr["DescriptionWas"]));
                        writer.WriteLine(Convert.ToString(dr["DescriptionNow"]));
                        string ricWas = (Convert.ToString(dr["RICWas"])).Replace("D^", "").Replace("^", "");
                        writer.WriteLine(ricWas);
                        writer.WriteLine(Convert.ToString(dr["RICNow"]));
                        writer.WriteLine(Convert.ToString(dr["ISINWas"]));
                        writer.WriteLine(Convert.ToString(dr["ISINNow"]));
                        writer.WriteLine("Official Code");
                        writer.WriteLine(Convert.ToString(dr["SecondWas"]));
                        writer.WriteLine(Convert.ToString(dr["SecondNow"]));
                        writer.WriteLine("");
                        writer.WriteLine("");
                        writer.WriteLine("");
                        //Exchange
                        string exchange = Convert.ToString(dr["RICNow"]);
                        if (string.IsNullOrEmpty(exchange))
                        {
                            exchange = Convert.ToString(dr["RICWas"]);
                        }
                        if (exchange.Contains(".KS"))
                        {
                            exchange = "KSC";
                        }
                        else if (exchange.Contains(".KQ"))
                        {
                            exchange = "KOE";
                        }
                        else if (exchange.Contains(".KN"))
                        {
                            exchange = "KNX";
                        }
                        writer.WriteLine(exchange);
                        string asset = Convert.ToString(dr["Asset"]);
                        if (asset.Equals("KDR"))
                        {
                            asset = "DRC";
                        }
                        writer.WriteLine(asset);
                        writer.PlaceNext(writer.Row + 1, 1);
                    }
                }

                //Run Macros
                app.ExcelAppInstance.GetType().InvokeMember("Run",
                                                            BindingFlags.Default | BindingFlags.InvokeMethod,
                                                            null,
                                                            app.ExcelAppInstance,
                                                            new object[] { "FormatData" });

                string targetFilePath = Path.GetDirectoryName(templateFilePath);
                targetFilePath = Path.Combine(targetFilePath, DateTime.Today.ToString("yyyy-MM-dd"));
                if (!Directory.Exists(targetFilePath))
                {
                    Directory.CreateDirectory(targetFilePath);
                }

                targetFilePath += "\\Result_Add_Drop_Upload_File.xls";
                workbook.SaveCopyAs(targetFilePath);
                TaskResultList.Add(new TaskResultEntry("Result File", "", targetFilePath));
                workbook.Close(false, templateFilePath, false);
            }
        }
Exemplo n.º 14
0
        private void GenerateXls(List <ELWExtractEntity> listELWEntity, string strOutPutPath)
        {
            //System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
            ExcelApp excelApp = new ExcelApp(false, false);

            if (excelApp.ExcelAppInstance == null)
            {
                string msg = "Excel could not be started. Check that your office installation and project reference are correct !!!";
                Logger.Log(msg, Logger.LogType.Error);
            }
            try
            {
                string    strFileName = DateTime.Today.ToString("yyyy-MM-dd") + "_ELW.xls";
                string    strFilePath = Path.Combine(strOutPutPath, strFileName);
                Workbook  wBook       = ExcelUtil.CreateOrOpenExcelFile(excelApp, strFilePath);
                Worksheet wSheet      = wBook.Worksheets[1] as Worksheet;
                if (wSheet == null)
                {
                    string msg = "Excel Worksheet could not be started. Check that your office installation and project reference are correct !!!";
                    Logger.Log(msg, Logger.LogType.Error);
                }
                //first table left on ELW website
                wSheet.Cells[1, 1]  = "발행기관코드";
                wSheet.Cells[1, 2]  = "표준코드";
                wSheet.Cells[1, 3]  = "한글종목명";
                wSheet.Cells[1, 4]  = "금융상품구분";
                wSheet.Cells[1, 5]  = "상장여부";
                wSheet.Cells[1, 6]  = "상장일";
                wSheet.Cells[1, 7]  = "발행수량(워런트)";
                wSheet.Cells[1, 8]  = "발행단가";
                wSheet.Cells[1, 9]  = "발행통화";
                wSheet.Cells[1, 10] = "발행형태";
                wSheet.Cells[1, 11] = "표준/비표준";
                //first table right on ELW website
                wSheet.Cells[1, 12] = "발행기관명";
                wSheet.Cells[1, 13] = "단축코드";
                wSheet.Cells[1, 14] = "영문종목명";
                wSheet.Cells[1, 15] = "발행회차(회)";
                wSheet.Cells[1, 16] = "활성여부";
                wSheet.Cells[1, 17] = "상장폐지일";
                wSheet.Cells[1, 18] = "발행일";
                wSheet.Cells[1, 19] = "만기일";
                wSheet.Cells[1, 20] = "발행구분";
                wSheet.Cells[1, 21] = "전환비율";
                wSheet.Cells[1, 22] = "권리형태";
                //second table on ELW website
                wSheet.Cells[1, 23] = "기초자산종류";
                wSheet.Cells[1, 24] = "주권발행기관";
                wSheet.Cells[1, 25] = "주가지수종류";
                wSheet.Cells[1, 26] = "기초자산기타";
                //third table on ELW website
                wSheet.Cells[1, 27] = "권리유형";
                wSheet.Cells[1, 28] = "특이발행조건";
                wSheet.Cells[1, 29] = "권리행사방식";
                //fourth table on ELW website
                wSheet.Cells[1, 30] = "CFI";

                int startLine = 2;
                foreach (var tmp in listELWEntity)
                {
                    //first table left on ELW website
                    wSheet.Cells[startLine, 1]  = tmp.IssuingAuthorityCongenial;
                    wSheet.Cells[startLine, 2]  = tmp.StandardCongenial;
                    wSheet.Cells[startLine, 3]  = tmp.KoreanProjectName;
                    wSheet.Cells[startLine, 4]  = tmp.FinancialProducts;
                    wSheet.Cells[startLine, 5]  = tmp.ListedOrNot;
                    wSheet.Cells[startLine, 6]  = tmp.Listed;
                    wSheet.Cells[startLine, 7]  = tmp.IssueNumber;
                    wSheet.Cells[startLine, 8]  = tmp.ReleaseTheUnitPrice;
                    wSheet.Cells[startLine, 9]  = tmp.Money;
                    wSheet.Cells[startLine, 10] = tmp.ReleaseForm;
                    wSheet.Cells[startLine, 11] = tmp.StandardNonStandard;
                    //first table left on ELW website
                    wSheet.Cells[startLine, 12] = tmp.IssuingAuthority;
                    wSheet.Cells[startLine, 13] = tmp.ShortenTheCongenial;
                    wSheet.Cells[startLine, 14] = tmp.TheProjectNameEnglish;
                    wSheet.Cells[startLine, 15] = tmp.ToIssue;
                    wSheet.Cells[startLine, 16] = tmp.WhetherTheActivity;
                    wSheet.Cells[startLine, 17] = tmp.ListedUntil;
                    wSheet.Cells[startLine, 18] = tmp.TheDate;
                    wSheet.Cells[startLine, 19] = tmp.TheExpirationOfThe;
                    wSheet.Cells[startLine, 20] = tmp.IssueToDistinguish;
                    wSheet.Cells[startLine, 21] = tmp.ConversionRatio;
                    wSheet.Cells[startLine, 22] = tmp.ThePowerForm;
                    //second table on ELW website
                    wSheet.Cells[startLine, 23] = tmp.UnderlyingAssetTypes;
                    wSheet.Cells[startLine, 24] = tmp.SovereignIssuingAuthority;
                    wSheet.Cells[startLine, 25] = tmp.StockIndexTypes;
                    wSheet.Cells[startLine, 26] = tmp.UnderlyingAssetGuitar;
                    //third table on ELW website
                    wSheet.Cells[startLine, 27] = tmp.TheRightType;
                    wSheet.Cells[startLine, 28] = tmp.TheIssueOfTheSpecialConditions;
                    wSheet.Cells[startLine, 29] = tmp.TheExerciseOfTheRightWay;
                    //fourth table on ELW website
                    wSheet.Cells[1, 30] = tmp.CFI;

                    startLine++;
                }
                excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
                wBook.Save();
                TaskResultList.Add(new TaskResultEntry("ELWExtractData", "XLS", strFilePath));
            }
            catch (Exception ex)
            {
                string msg = "Error found in NDA T&C file :" + ex.ToString();
                Logger.Log(msg, Logger.LogType.Error);
            }
            finally
            {
                excelApp.Dispose();
            }
        }
Exemplo n.º 15
0
        /// <summary>
        /// Generate FM file for Drop.
        /// </summary>
        private void GenerateFMFiles()
        {
            foreach (CompanyWarrantDropTemplate item in waDrop)
            {
                ExcelApp excelApp = new ExcelApp(false, false);
                if (excelApp.ExcelAppInstance == null)
                {
                    string msg = "Excel could not be started. Check that your office installation and project reference are correct!";
                    Logger.Log(msg, Logger.LogType.Error);
                    return;
                }
                string fileName = "KR FM (Company Warrant Drop) Request_" + item.RIC + " (wef " + item.EffectiveDate + ").xls";
                string filePath = Path.Combine(configObj.FM, fileName);
                try
                {
                    Workbook  wBook  = ExcelUtil.CreateOrOpenExcelFile(excelApp, filePath);
                    Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
                    if (wSheet == null)
                    {
                        string msg = "Worksheet could not be started. Check that your office installation and project reference are correct!";
                        Logger.Log(msg, Logger.LogType.Error);
                        return;
                    }
                    wSheet.Name = "DROP";
                    ((Range)wSheet.Columns["A", Type.Missing]).ColumnWidth = 20;
                    ((Range)wSheet.Columns["B", Type.Missing]).ColumnWidth = 2;
                    ((Range)wSheet.Columns["C", Type.Missing]).ColumnWidth = 30;
                    ((Range)wSheet.Columns["A:C", Type.Missing]).Font.Name = "Arial";
                    ((Range)wSheet.Rows[1, Type.Missing]).Font.Bold        = FontStyle.Bold;
                    ((Range)wSheet.Rows[1, Type.Missing]).Font.Color       = ColorTranslator.ToOle(Color.Black);

                    wSheet.Cells[1, 1] = "FM Request";
                    wSheet.Cells[1, 2] = " ";
                    wSheet.Cells[1, 3] = "Deletion";
                    ((Range)wSheet.Cells[3, 1]).Font.Bold  = FontStyle.Bold;
                    ((Range)wSheet.Cells[3, 1]).Font.Color = ColorTranslator.ToOle(Color.Black);
                    wSheet.Cells[3, 1] = "Effective Date";
                    wSheet.Cells[3, 2] = ":";
                    ((Range)wSheet.Cells[3, 3]).NumberFormat = "@";
                    wSheet.Cells[3, 3] = item.EffectiveDate;
                    ((Range)wSheet.Cells[4, 1]).Font.Bold  = FontStyle.Bold;
                    ((Range)wSheet.Cells[4, 1]).Font.Color = ColorTranslator.ToOle(Color.Black);
                    wSheet.Cells[4, 1] = "RIC";
                    wSheet.Cells[4, 2] = ":";
                    wSheet.Cells[4, 3] = item.RIC;
                    ((Range)wSheet.Cells[5, 1]).Font.Bold  = FontStyle.Bold;
                    ((Range)wSheet.Cells[5, 1]).Font.Color = ColorTranslator.ToOle(Color.Black);
                    wSheet.Cells[5, 1] = "ISIN";
                    wSheet.Cells[5, 2] = ":";
                    wSheet.Cells[5, 3] = item.ISIN;
                    wSheet.Cells[6, 1] = "QA Short Name";
                    wSheet.Cells[6, 2] = ":";
                    ((Range)wSheet.Cells[6, 3]).Font.Color     = ColorTranslator.ToOle(Color.Blue);
                    ((Range)wSheet.Cells[6, 3]).Font.Underline = true;
                    wSheet.Cells[6, 3] = item.QAShortName;
                    excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
                    wBook.Save();

                    MailToSend mail = new MailToSend();
                    mail.ToReceiverList.AddRange(configObj.MailTo);
                    mail.CCReceiverList.AddRange(configObj.MailCC);
                    mail.MailSubject = Path.GetFileNameWithoutExtension(fileName);
                    mail.AttachFileList.Add(filePath);
                    mail.MailBody = "Company Warrant Drop:\t\t" + item.RIC + "\r\n\r\n"
                                    + "Effective Date:\t\t" + item.EffectiveDate + "\r\n\r\n\r\n\r\n";
                    string signature = string.Join("\r\n", configObj.MailSignature.ToArray());
                    mail.MailBody += signature;

                    TaskResultList.Add(new TaskResultEntry(fileName, "FM File", filePath, mail));
                    Logger.Log("Generate FM file successfully. Filepath is " + filePath);
                }
                catch (Exception ex)
                {
                    String msg = "Error found in generating FM file for RIC:" + item.RIC + " \r\n" + ex.Message;
                    Logger.Log(msg, Logger.LogType.Error);
                }
                finally
                {
                    excelApp.Dispose();
                }
            }
        }
Exemplo n.º 16
0
        private void GenerateNDAFile()
        {
            if (announcementList.Count > 0)
            {
                if (!Directory.Exists(configObj.NDA))
                {
                    Directory.CreateDirectory(configObj.NDA);
                }
                string fileName = "KR" + DateTime.Now.ToString("yyyyMMdd") + "QAADD.csv";
                string ndaFile  = Path.Combine(configObj.NDA, fileName);

                StringBuilder   kqBuilder = new StringBuilder();
                NDAFileTemplate KOSDAQ    = new NDAFileTemplate();

                kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Ric);
                kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Tag);
                kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Base_Asset);
                kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Ticker_Symbol);
                kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Asset_Short_Name);
                kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Asset_Common_Name);
                kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Type);
                kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Category);
                kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Currency);
                kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Exchange);
                kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Equity_First_Trading_Day);
                kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Round_Lot_Size);
                kqBuilder.Remove(0, 1);
                kqBuilder.AppendLine();

                foreach (var item in announcementList)
                {
                    if (item.RIC.Contains("KQ"))
                    {
                        for (int i = 0; i < 6; i++)
                        {
                            switch (i)
                            {
                            case 0:
                                kqBuilder.AppendFormat("{0}", item.RIC);
                                kqBuilder.AppendFormat(",{0}", "673");
                                break;

                            case 1:
                                kqBuilder.AppendFormat("{0}", item.RIC.Split('.')[0] + "F.KQ");
                                kqBuilder.AppendFormat(",{0}", "64399");
                                break;

                            case 2:
                                kqBuilder.AppendFormat("{0}", item.RIC.Split('.')[0] + "S.KQ");
                                kqBuilder.AppendFormat(",{0}", "60673");
                                break;

                            case 3:
                                kqBuilder.AppendFormat("{0}", item.RIC.Split('.')[0] + "stat.KQ");
                                kqBuilder.AppendFormat(",{0}", "61287");
                                break;

                            case 4:
                                kqBuilder.AppendFormat("{0}", item.RIC.Split('.')[0] + "ta.KQ");
                                kqBuilder.AppendFormat(",{0}", "64380");
                                break;

                            case 5:
                                kqBuilder.AppendFormat("{0}", item.RIC.Split('.')[0] + "bl.KQ");
                                kqBuilder.AppendFormat(",{0}", "67094");
                                break;
                            }
                            AppendOtherColumnToNDAFile(kqBuilder, item, "KOE");
                        }
                    }
                    if (item.RIC.Contains("KS"))
                    {
                        for (int i = 0; i < 6; i++)
                        {
                            switch (i)
                            {
                            case 0:
                                kqBuilder.AppendFormat("{0}", item.RIC);
                                kqBuilder.AppendFormat(",{0}", "184");
                                break;

                            case 1:
                                kqBuilder.AppendFormat("{0}", item.RIC.Split('.')[0] + "F.KS");
                                kqBuilder.AppendFormat(",{0}", "64398");
                                break;

                            case 2:
                                kqBuilder.AppendFormat("{0}", item.RIC.Split('.')[0] + "S.KS");
                                kqBuilder.AppendFormat(",{0}", "60184");
                                break;

                            case 3:
                                kqBuilder.AppendFormat("{0}", item.RIC.Split('.')[0] + "stat.KS");
                                kqBuilder.AppendFormat(",{0}", "61286");
                                break;

                            case 4:
                                kqBuilder.AppendFormat("{0}", item.RIC.Split('.')[0] + "ta.KS");
                                kqBuilder.AppendFormat(",{0}", "64379");
                                break;

                            case 5:
                                kqBuilder.AppendFormat("{0}", item.RIC.Split('.')[0] + "bl.KS");
                                kqBuilder.AppendFormat(",{0}", "67093");
                                break;
                            }
                            AppendOtherColumnToNDAFile(kqBuilder, item, "KSC");
                        }
                    }
                }
                File.WriteAllText(ndaFile, kqBuilder.ToString(), Encoding.UTF8);
                TaskResultList.Add(new TaskResultEntry(fileName, "NDA File", ndaFile, FileProcessType.NDA));
            }
        }
Exemplo n.º 17
0
        private void GenerateGEDAFile()
        {
            if (announcementList.Count > 0)
            {
                if (!Directory.Exists(configObj.GEDA))
                {
                    Directory.CreateDirectory(configObj.GEDA);
                }

                string fileName = "KR_PEO_Bulk_Creation_KOSDAQ.txt";
                string gedaFile = Path.Combine(configObj.GEDA, fileName);

                StringBuilder    kqBuilder = new StringBuilder();
                GEDAFileTemplate KOSDAQ    = new GEDAFileTemplate();

                kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Symbol);
                kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Display_Name);
                kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Ric);
                kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Official_Code);
                kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Ex_Symbol);
                kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Background_Page);
                kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Display_Nmll);
                kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Bcast_Ref);
                kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Instmod_ISIN);
                kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Instmod_Mnemonic);
                kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Instmod_Tdn_Issuer_Name);
                kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Instmod_Tdn_Symbol);
                kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Exl_Name);
                kqBuilder.Append(KOSDAQ.Geda_BCU);
                kqBuilder.AppendLine();

                foreach (var item in announcementList)
                {
                    kqBuilder.AppendFormat("{0}\t", item.RIC);
                    kqBuilder.AppendFormat("{0}\t", item.IDNDisplayName);
                    kqBuilder.AppendFormat("{0}\t", item.RIC);
                    kqBuilder.AppendFormat("{0}\t", item.Ticker);

                    if (string.IsNullOrEmpty(item.ISIN))
                    {
                        kqBuilder.AppendFormat("==NOISIN==\t");
                    }
                    else
                    {
                        kqBuilder.AppendFormat("{0}\t", item.ISIN);
                    }
                    kqBuilder.AppendFormat("{0}\t", "****");
                    kqBuilder.AppendFormat("{0}\t", item.KoreaName);
                    kqBuilder.AppendFormat("{0}\t", item.RIC);
                    kqBuilder.AppendFormat("{0}\t", item.ISIN);
                    kqBuilder.AppendFormat("{0}\t", "A" + item.Ticker);
                    kqBuilder.AppendFormat("{0}\t", item.LegalName);
                    kqBuilder.AppendFormat("{0}\t", item.Ticker);

                    if (item.Type == null)
                    {
                        kqBuilder.Append("==NOTYPE==");
                    }
                    else if (item.Type.Equals("KDR"))
                    {
                        if (item.RIC.Contains(".KQ"))
                        {
                            kqBuilder.Append("KOSDAQ_EQB_KDR");
                        }
                        else if (item.RIC.Contains(".KS"))
                        {
                            kqBuilder.Append("KSE_EQB_KDR");
                        }
                    }
                    else
                    {
                        if (item.RIC.Contains(".KQ"))
                        {
                            kqBuilder.Append("KOSDAQ_EQB_2");
                        }
                        else if (item.RIC.Contains(".KS"))
                        {
                            kqBuilder.Append("KSE_EQB_3");
                        }
                        // BCU
                        if (item.Type.Equals("ORD"))
                        {
                            if (item.RIC.Contains(".KQ"))
                            {
                                kqBuilder.Append("\tKOSDAQ_EQ_IPO");
                            }
                            else if (item.RIC.Contains(".KS"))
                            {
                                kqBuilder.Append("\tKSE_EQ_IPO");
                            }
                        }
                    }
                    kqBuilder.AppendLine();
                }
                File.WriteAllText(gedaFile, kqBuilder.ToString(), Encoding.UTF8);
                TaskResultList.Add(new TaskResultEntry(fileName, "GEDA File", gedaFile, FileProcessType.GEDA_BULK_RIC_CREATION));
            }
        }
Exemplo n.º 18
0
        private void GenerateFM()
        {
            if (announcementList.Count < 1)
            {
                Logger.Log("No new added equity record", Logger.LogType.Info);
                return;
            }

            System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
            ExcelApp excelApp = new ExcelApp(false, false);

            if (excelApp.ExcelAppInstance == null)
            {
                string msg = "Excel could not be started. Check that your office installation and project reference correct!!";
                Logger.Log(msg, Logger.LogType.Error);
                return;
            }

            string rics     = CombineAllRics(announcementList);
            string filename = string.Format("KR FM (PEO)_{0}.xls", rics);
            string fmFile   = Path.Combine(configObj.FM, filename);

            try
            {
                Workbook  wBook  = ExcelUtil.CreateOrOpenExcelFile(excelApp, fmFile);
                Worksheet wSheet = ExcelUtil.GetWorksheet("Sheet1", wBook);
                if (wSheet == null)
                {
                    string msg = "Worksheet could not be created. Check that your office installation and project reference are correct!";
                    Logger.Log(msg, Logger.LogType.Error);
                    return;
                }

                int startLine = WriteFMHeader(wSheet, 3, true);

                foreach (var item in announcementList)
                {
                    WriteFMEquityItem(wSheet, startLine, item);
                    startLine++;
                }

                WriteFMFooter(wSheet, startLine + 1);

                excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
                wBook.Save();

                MailToSend mail = new MailToSend();
                mail.ToReceiverList.AddRange(configObj.MailTo);
                mail.MailSubject = Path.GetFileNameWithoutExtension(filename);
                mail.CCReceiverList.AddRange(configObj.MailCC);
                mail.AttachFileList.Add(fmFile);
                mail.MailBody = "PEO:\t" + rics + "\t\r\n\r\n\r\n";

                string signature = string.Join("\r\n", configObj.MailSignature.ToArray());

                mail.MailBody += signature;

                TaskResultList.Add(new TaskResultEntry(filename, "FM File", fmFile, mail));
            }
            catch (Exception ex)
            {
                Logger.Log("Error in GenerateFM(): " + ex, Logger.LogType.Error);
            }
            finally
            {
                excelApp.Dispose();
            }
        }
        /// <summary>
        /// 发送邮件功能。
        /// </summary>
        public void StartSendMail()
        {
            string inscrubed = "";

            inscrubed = inscrubed + "\r\n\r\n\r\n";
            //inscrubed = inscrubed + configObj.ALERT_MAIL_INSCRIBED_PERSON + "\r\n";
            //inscrubed = inscrubed + configObj.ALERT_MAIL_INSCRIBED_PSITION + "\r\n";
            //inscrubed = inscrubed + "\r\nThomson Reuters\r\n\r\nPhone: " + configObj.ALERT_MAIL_INSCRIBED_PHONE + "\r\n";
            //inscrubed = inscrubed + configObj.ALERT_MAIL_INSCRIBED_EMAIL_ADRESS + "\r\nthomsonreuters.com\r\n";
            for (int i = 0; i < configObj.ALERT_MAIL_SIGNATURE_INFORMATION_LIST.Count; i++)
            {
                inscrubed = inscrubed + configObj.ALERT_MAIL_SIGNATURE_INFORMATION_LIST[i] + "\r\n";
            }

            MailToSend mail         = new MailToSend();
            string     sendFileDate = DateTime.Today.ToString("yyyy-MM-dd", new CultureInfo("en-US"));
            string     today        = DateTime.Today.ToString("dd-MMM-yyyy", new CultureInfo("en-US"));
            string     mailSubject  = "Daily files from East Asia " + today + " (South Korea)";

            if (!string.IsNullOrEmpty(configObj.SEND_FILE_DATE))
            {
                sendFileDate = configObj.SEND_FILE_DATE;
            }

            //string emaFileDir = configObj.EMA_FILE_BASE_DIR + "\\" + sendFileDate;
            // string emaFileDir = ConfigureOperator.getEmaFileSaveDir()+"\\"  + sendFileDate;
            string        emaFileDir = Path.Combine(ConfigureOperator.GetEmaFileSaveDir(), sendFileDate);
            List <string> fileList   = GetAllTheEMAFile(emaFileDir);

            if (fileList == null)
            {
                return;
            }
            mail.ToReceiverList.AddRange(configObj.ALERT_MAIL_TO_LIST);
            mail.MailSubject = mailSubject;
            mail.CCReceiverList.AddRange(configObj.ALERT_MAIL_CC_LIST);

            string strADD        = "\nFile for ADD.\n";
            string strXLSX       = "\nThe excel file is for your checking on the mature date (compare with the date in the csv file dd/mm/yy).\n";
            string strISIN       = "\nFiles for Change such as ISIN, Issue amount, issue price and strike price change and so on.\n";
            int    countADDFile  = 0;
            int    countXLSXFile = 0;
            int    countISINFile = 0;

            foreach (string file in fileList)
            {
                string fileName      = Path.GetFileName(file);
                string fileExtension = Path.GetExtension(file);
                mail.AttachFileList.Add(file);
                if (fileExtension == ".xls" || fileExtension == ".xlsx")
                {
                    countXLSXFile++;
                    strXLSX = strXLSX + "   " + fileName + "\n";
                }
                else
                {
                    if (fileName.Split('.').Length > 1 && fileName.Split('_')[1] == "ADD")
                    {
                        countADDFile++;
                        strADD = strADD + "   " + fileName + "\n";
                    }
                    else
                    {
                        countISINFile++;
                        strISIN = strISIN + "   " + fileName + "\n";
                    }
                }
            }

            if (countADDFile == 0)
            {
                strADD = strADD + "No such files today ! \n";
            }
            if (countISINFile == 0)
            {
                strISIN = strISIN + "No such files today ! \n";
            }
            if (countXLSXFile == 0)
            {
                strXLSX = strXLSX + "No such files today ! \n";
            }
            mail.MailBody = "Hi, colleagues,\n   Please find the files for South Korea today." + "\n" + strADD + strXLSX + strISIN + "\nPlease  let me know if you have any concerns.\n\n" + inscrubed;

            //string err = "Send EMA mail error !";
            //using (OutlookApp app = new OutlookApp())
            //{
            //    OutlookUtil.CreateAndSendMail(app, mail, out err);
            //}
            // AddResult(sendFileDate,emaFileDir,"EMA File Folder Path");
            TaskResultList.Add(new TaskResultEntry("EMAFile", "EMA File Today", emaFileDir, mail));
        }
Exemplo n.º 20
0
        /// <summary>
        /// 发送邮件功能。
        /// </summary>
        public void StartSendMail()
        {
            string inscrubed = "";

            inscrubed = inscrubed + "\r\n\r\n\r\n";
            inscrubed = configObj.AlertMailSignatureInformationList.Aggregate(inscrubed, (current, t) => current + t + "\r\n");

            MailToSend mail         = new MailToSend();
            string     sendFileDate = DateTime.Today.ToString("yyyy-MM-dd", new CultureInfo("en-US"));
            string     today        = DateTime.Today.ToString("dd-MMM-yyyy", new CultureInfo("en-US"));
            string     mailSubject  = "Daily files from East Asia " + today + " (South Korea)";

            if (!string.IsNullOrEmpty(configObj.SendFileDate))
            {
                sendFileDate = configObj.SendFileDate;
            }

            string        emaFileDir = Path.Combine(ConfigureOperator.GetEmaFileSaveDir(), sendFileDate);
            List <string> fileList   = GetAllTheEMAFile(emaFileDir);

            if (fileList == null)
            {
                return;
            }
            mail.ToReceiverList.AddRange(configObj.AlertMailToList);
            mail.MailSubject = mailSubject;
            mail.CCReceiverList.AddRange(configObj.AlertMailCCList);

            string strADD        = "\nFile for ADD.\n";
            string strXLSX       = "\nThe excel file is for your checking on the mature date (compare with the date in the csv file dd/mm/yy).\n";
            string strISIN       = "\nFiles for Change such as ISIN, Issue amount, issue price and strike price change and so on.\n";
            int    countADDFile  = 0;
            int    countXLSXFile = 0;
            int    countISINFile = 0;

            foreach (string file in fileList)
            {
                string fileName      = Path.GetFileName(file);
                string fileExtension = Path.GetExtension(file);
                mail.AttachFileList.Add(file);
                if (fileExtension == ".xls" || fileExtension == ".xlsx")
                {
                    countXLSXFile++;
                    strXLSX = strXLSX + "   " + fileName + "\n";
                }
                else
                {
                    if (fileName.Split('.').Length > 1 && fileName.Split('_')[1] == "ADD")
                    {
                        countADDFile++;
                        strADD = strADD + "   " + fileName + "\n";
                    }
                    else
                    {
                        countISINFile++;
                        strISIN = strISIN + "   " + fileName + "\n";
                    }
                }
            }

            if (countADDFile == 0)
            {
                strADD = strADD + "No such files today ! \n";
            }
            if (countISINFile == 0)
            {
                strISIN = strISIN + "No such files today ! \n";
            }
            if (countXLSXFile == 0)
            {
                strXLSX = strXLSX + "No such files today ! \n";
            }
            mail.MailBody = "PEO:\t" + "Hi, colleagues,\n   Please find the files for South Korea today." + "\n" + strADD + strXLSX + strISIN + "\nPlease  let me know if you have any concerns.\n\n" + inscrubed;

            TaskResultList.Add(new TaskResultEntry("EMAFile", "EMA File Today", emaFileDir, mail));
        }