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