private void ChineseNameCapture(int lastRickIndex)
{
//Get Chinese Content
HKRicTemplate chineseRic = new HKRicTemplate();
int start_position = lastRickIndex - ricList.Count;
selenium.Click("link=繁體");
selenium.WaitForPageToLoad("600000");
for (int i = 0; i < ricList.Count; i++)
{
//Get Chinese Name
chineseRic.ricCHNNameStr = selenium.GetText("//table[@id='bodytable']/tbody/tr/td/table/tbody[1]/tr[" + start_position + "]/td[3]");
chineseList.Add(chineseRic);
start_position++;
}
//close chinese window
selenium.Close();
selenium.Stop();
}
/**
* Additional method, apply correct format for IDN longname
* Retrun: String
* Parameter: String sourceStr
*/
public String IDNLongNameFormat(HKRicTemplate ricObj, bool isIndex, bool isBull, bool isHKD)
{
String idnLongName = "";
//For Index
if (isIndex)
{
if (ricObj.underlyingStr == "HSI")
{
idnLongName = "HANG SENG@";
}
if (ricObj.underlyingStr == "HSCEI")
{
idnLongName = "HANG SENG C E I@";
}
if (ricObj.underlyingStr == "DJI")
{
idnLongName = "DJ INDU AVERAGE@";
}
}
//For Stock
else
{
idnLongName += ricObj.underlyingNameForStockStr + "@";
}
idnLongName += ((IssuerCode)issuerCodeHT[ricObj.issuerIDStr]).shortName + " ";
idnLongName += ricObj.maturityDateDT.ToString("MMMyy", new CultureInfo("en-US")).ToUpper() + " ";
//Attach Strike Price from Strike Level
//For HKD
if (isHKD)
{
idnLongName += ricObj.strikeLevelStr.Substring(4);
}
else
{
idnLongName += ricObj.strikeLevelStr;
}
idnLongName += " ";
//For Bull
if (isBull)
{
idnLongName += "C";
}
//For Bear
else
{
idnLongName += "P";
}
if (isIndex)
{
idnLongName += "IR";
}
else
{
idnLongName += "R";
}
return(idnLongName);
}
}//end CBBCDataCapture()
private void CaptureRetry(int start_position)
{
selenium.Start();
selenium.UseXpathLibrary("javascript-xpath");
try
{
selenium.OpenWindow("/eng/cbbc/newissue/newlaunch.htm", "CBBCListWindow");
selenium.WaitForPopUp("CBBCListWindow", "30000");
selenium.SelectWindow("CBBCListWindow");
//Get English Content
while (selenium.IsElementPresent("//table[@id='bodytable']/tbody/tr/td/table/tbody[1]/tr[" + start_position + "]/td[1]"))
{
String launchDateStr = selenium.GetText("//table[@id='bodytable']/tbody/tr/td/table/tbody[1]/tr[" + start_position + "]/td[12]");
DateTime tempLaunchDateDT = DateTime.ParseExact(launchDateStr, "dd-MM-yyyy", null);
DateTime temp = coreObj.DateCalculate(scheduleDate, tempLaunchDateDT, holidayCount);
if (temp.Date == scheduleDate.Date)
{
HKRicTemplate hkRic = new HKRicTemplate();
hkRic.launchDateDT = DateTime.ParseExact(launchDateStr, "dd-MM-yyyy", null);
hkRic.ricCodeStr = selenium.GetText("//table[@id='bodytable']/tbody/tr/td/table/tbody[1]/tr[" + start_position + "]/td[2]");
hkRic.ricNameStr = selenium.GetText("//table[@id='bodytable']/tbody/tr/td/table/tbody[1]/tr[" + start_position + "]/td[3]");
hkRic.issuerIDStr = selenium.GetText("//table[@id='bodytable']/tbody/tr/td/table/tbody[1]/tr[" + start_position + "]/td[4]");
hkRic.underlyingStr = selenium.GetText("//table[@id='bodytable']/tbody/tr/td/table/tbody[1]/tr[" + start_position + "]/td[5]");
hkRic.bullBearStr = selenium.GetText("//table[@id='bodytable']/tbody/tr/td/table/tbody[1]/tr[" + start_position + "]/td[6]");
hkRic.boardLotStr = selenium.GetText("//table[@id='bodytable']/tbody/tr/td/table/tbody[1]/tr[" + start_position + "]/td[7]");
hkRic.strikeLevelStr = selenium.GetText("//table[@id='bodytable']/tbody/tr/td/table/tbody[1]/tr[" + start_position + "]/td[8]");
hkRic.callLevelStr = selenium.GetText("//table[@id='bodytable']/tbody/tr/td/table/tbody[1]/tr[" + start_position + "]/td[9]");
hkRic.entitlementRatioStr = selenium.GetText("//table[@id='bodytable']/tbody/tr/td/table/tbody[1]/tr[" + start_position + "]/td[10]");
hkRic.issueSizeStr = selenium.GetText("//table[@id='bodytable']/tbody/tr/td/table/tbody[1]/tr[" + start_position + "]/td[11]");
hkRic.clearingCommDateDT = DateTime.ParseExact(selenium.GetText("//table[@id='bodytable']/tbody/tr/td/table/tbody[1]/tr[" + start_position + "]/td[13]"), "dd-MM-yyyy", null);
hkRic.listingDateDT = DateTime.ParseExact(selenium.GetText("//table[@id='bodytable']/tbody/tr/td/table/tbody[1]/tr[" + start_position + "]/td[14]"), "dd-MM-yyyy", null);
hkRic.maturityDateDT = DateTime.ParseExact(selenium.GetText("//table[@id='bodytable']/tbody/tr/td/table/tbody[1]/tr[" + start_position + "]/td[15]"), "dd-MM-yyyy", null);
//Get issue price
String url = selenium.GetAttribute("//table[@id='bodytable']/tbody/tr/td/table/tbody[1]/tr[" + start_position + "]/td[2]/a@href");
String id = url.Substring(url.IndexOf('=') + 1, 5);
String summaryUrl = "http://www.hkex.com.hk/eng/cbbc/cbbcsummary.asp?id=" + id;
selenium.OpenWindow(summaryUrl, "CBBCSummaryWindow" + start_position);
selenium.WaitForPopUp("CBBCSummaryWindow" + start_position, "30000");
selenium.SelectWindow("CBBCSummaryWindow" + start_position);
//For Equity Get underlying name
if (Char.IsDigit(hkRic.underlyingStr, 0))
{
hkRic.underlyingNameForStockStr = selenium.GetText("//table[@id='bodytable']/tbody/tr[3]/td[2]").Substring(8);
}
hkRic.issuePriceStr = selenium.GetText("//table[@id='bodytable']/tbody/tr[12]/td[2]").Substring(4);
selenium.Close();
ricList.Add(hkRic);
selenium.SelectWindow("CBBCListWindow");
}//end if
start_position++;
}//end while
ChineseNameCapture(start_position);
//Get gearing and premium
//search on page http://www.hkexnews.hk/listedco/listconews/advancedsearch/search_active_main.asp
DataCaptureFromPDF(0);
}//end try
catch (SeleniumException ex)
{
String errLog = ex.ToString();
selenium.Close();
selenium.Stop();
CaptureRetry(start_position);
}
}
private void GenerateCBBCTemplate(int start, List <RicInfo> ricInfoList)
{
List <HKRicTemplate> ricTemplateList = new List <HKRicTemplate>();
foreach (RicInfo ricInfo in ricInfoList)
{
HKRicTemplate ric = new HKRicTemplate(ricInfo, FMType.Cbbc);
ricTemplateList.Add(ric);
}
try
{
using (ExcelApp app = new ExcelApp(false, false))
{
app.ExcelAppInstance.AlertBeforeOverwriting = false;
Workbook workbook = app.ExcelAppInstance.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet worksheet = (Worksheet)workbook.Worksheets[1];
if (worksheet == null)
{
logger.LogErrorAndRaiseException("Worksheet could not be created. Check that your office installation and project references are correct.");
}
worksheet.Name = "FM_HKCBBC";
((Range)worksheet.Columns["A", System.Type.Missing]).ColumnWidth = 36;
((Range)worksheet.Columns["B", System.Type.Missing]).ColumnWidth = 42;
((Range)worksheet.Columns["A:B", System.Type.Missing]).Font.Name = "Courier";
app.ExcelAppInstance.Cells.Columns.HorizontalAlignment = XlHAlign.xlHAlignLeft;
worksheet.Cells[1, 1] = "Please action the add of the following HK stock on TQS**";
worksheet.Cells[3, 1] = "FM Serial Number:";
//Serial Number
worksheet.Cells[3, 2] = "HK" + DateTime.Now.Year.ToString().Substring(2) + "-" + fmSerialNumberCBBC;
worksheet.Cells[4, 1] = "Effective Date:";
((Range)worksheet.Cells[4, 2]).NumberFormat = "@";
worksheet.Cells[4, 2] = ricTemplateList[0].EffectiveDate;
int startLine = 6;
int end = 0;
if (start + 20 >= ricTemplateList.Count)
{
end = ricTemplateList.Count;
}
else
{
end = start + 20;
}
#region writer template file for each ric
for (int i = start; i < end; i++)
{
HKRicTemplate ricTemplate = ricTemplateList[i];
worksheet.Cells[startLine++, 1] = "+ADDITION+";
worksheet.Cells[startLine++, 1] = "---------------------------------------------------------------------------------------------------";
((Range)worksheet.Cells[startLine, 1]).Font.Bold = true;
((Range)worksheet.Cells[startLine, 1]).NumberFormat = "@";
worksheet.Cells[startLine++, 1] = "(" + (i + 1) + ")";//dynamic increase
worksheet.Cells[startLine++, 1] = "**For TQS**";
startLine++;
worksheet.Cells[startLine, 1] = "Underlying RIC:";
worksheet.Cells[startLine++, 2] = ricTemplate.UnderLyingRic;
worksheet.Cells[startLine, 1] = "Composite chain RIC:";
worksheet.Cells[startLine++, 2] = ricTemplate.CompositeChainRic;
worksheet.Cells[startLine, 1] = "Broker page RIC:";
worksheet.Cells[startLine++, 2] = ricTemplate.BrokerPageRic;
worksheet.Cells[startLine, 1] = "Misc.Info page RIC:";
worksheet.Cells[startLine++, 2] = ricTemplate.MiscInfoPageRic;
worksheet.Cells[startLine, 1] = "Displayname:";
worksheet.Cells[startLine++, 2] = ricTemplate.DisplayName;
worksheet.Cells[startLine, 1] = "Official Code:";
worksheet.Cells[startLine++, 2] = ricTemplate.OfficicalCode;
worksheet.Cells[startLine, 1] = "Exchange Symbol:";
worksheet.Cells[startLine++, 2] = ricTemplate.ExchangeSymbol;
worksheet.Cells[startLine, 1] = "Currency:";
worksheet.Cells[startLine++, 2] = ricTemplate.Currency;
((Range)worksheet.Cells[startLine, 2]).NumberFormat = "@";
worksheet.Cells[startLine, 1] = "Recordtype:";
worksheet.Cells[startLine++, 2] = ricTemplate.RecordType;
worksheet.Cells[startLine, 1] = "Spare_Ubytes8:";
worksheet.Cells[startLine++, 2] = ricTemplate.SpareUbytes8;
worksheet.Cells[startLine, 1] = "Underlying Chain RIC:";
worksheet.Cells[startLine++, 2] = ricTemplate.UnderlyingChainRic1;
worksheet.Cells[startLine, 1] = "Underlying Chain RIC:";
worksheet.Cells[startLine++, 2] = ricTemplate.UnderlyingChainRic2;
worksheet.Cells[startLine, 1] = "Warrant Type:";
worksheet.Cells[startLine++, 2] = ricTemplate.WarrantType;
worksheet.Cells[startLine, 1] = "Misc Info page Chain RIC:";
worksheet.Cells[startLine++, 2] = ricTemplate.MiscInfoPageChainRic;
worksheet.Cells[startLine, 1] = "Lot Size:";
((Range)worksheet.Cells[startLine, 2]).NumberFormat = "0";
worksheet.Cells[startLine++, 2] = ricTemplate.LotSize;
startLine++;
worksheet.Cells[startLine, 1] = "COI DSPLY_NMLL:";
((Range)worksheet.Cells[startLine, 2]).NumberFormat = "@";
worksheet.Cells[startLine++, 2] = ricTemplate.ColDsplyNmll;
worksheet.Cells[startLine++, 2] = "-----------------------------------------------------";
worksheet.Cells[startLine, 1] = "BCAST_REF:";
worksheet.Cells[startLine++, 2] = ricTemplate.BcastRef;
worksheet.Cells[startLine++, 1] = "---------------------------------------------------------------------------------------------------";
worksheet.Cells[startLine, 1] = "WNT_RATIO:";
((Range)worksheet.Cells[startLine, 2]).NumberFormat = "0.0000000";
worksheet.Cells[startLine++, 2] = ricTemplate.WntRation;
worksheet.Cells[startLine, 1] = "STRIKE_PRC:";
((Range)worksheet.Cells[startLine, 2]).NumberFormat = "0.000";
worksheet.Cells[startLine++, 2] = ricTemplate.StrikPrc;
worksheet.Cells[startLine, 1] = "MATUR_DATE:";
((Range)worksheet.Cells[startLine, 2]).NumberFormat = "@";
worksheet.Cells[startLine++, 2] = ricTemplate.MaturDate;
worksheet.Cells[startLine, 1] = "LONGLINK3:";
worksheet.Cells[startLine++, 2] = ricTemplate.LongLink3;
worksheet.Cells[startLine, 1] = "SPARE_SNUM13:";
worksheet.Cells[startLine++, 2] = ricTemplate.SpareSnum13;
worksheet.Cells[startLine, 1] = "GN_TX20_3:";
worksheet.Cells[startLine++, 2] = ricTemplate.GNTX20_3;
worksheet.Cells[startLine, 1] = "GN_TX20_6:";
worksheet.Cells[startLine++, 2] = ricTemplate.GNTX20_6;
worksheet.Cells[startLine, 1] = "GN_TX20_7:";
worksheet.Cells[startLine++, 2] = ricTemplate.GNTX20_7;
worksheet.Cells[startLine, 1] = "GN_TX20_10:";
worksheet.Cells[startLine++, 2] = ricTemplate.GNTX20_10;
worksheet.Cells[startLine, 1] = "GN_TX20_11:";
((Range)worksheet.Cells[startLine, 2]).NumberFormat = "0";
worksheet.Cells[startLine++, 2] = ricTemplate.GNTX20_11;
worksheet.Cells[startLine, 1] = "GN_TX20_12 (Misc.Info):";
worksheet.Cells[startLine++, 2] = ricTemplate.GNTX20_12;
worksheet.Cells[startLine, 1] = "COUPON RATE:";
worksheet.Cells[startLine++, 2] = ricTemplate.CouponRate;
worksheet.Cells[startLine, 1] = "ISSUE PRICE:";
((Range)worksheet.Cells[startLine, 2]).NumberFormat = "0.000";
worksheet.Cells[startLine++, 2] = ricTemplate.IssuePrice;
worksheet.Cells[startLine++, 1] = "---------------------------------------------------------------------------------------------------";
startLine++;
worksheet.Cells[startLine, 1] = "ROW80_13:";
worksheet.Cells[startLine++, 2] = ricTemplate.Row80_13;
//**************************For AFE***********************************
worksheet.Cells[startLine++, 1] = "---------------------------------------------------------------------------------------------------";
worksheet.Cells[startLine++, 1] = "**FOR AFE**";
worksheet.Cells[startLine, 1] = "GV1_FLAG:";
worksheet.Cells[startLine++, 2] = ricTemplate.GVFlag;
worksheet.Cells[startLine, 1] = "ISS_TP_FLG:";
worksheet.Cells[startLine++, 2] = ricTemplate.IssTpFlg;
worksheet.Cells[startLine, 1] = "RDM_CUR:";
worksheet.Cells[startLine++, 2] = ricTemplate.RdmCur;
worksheet.Cells[startLine, 1] = "LONGLINK14:";
worksheet.Cells[startLine++, 2] = ricTemplate.LongLink14;
worksheet.Cells[startLine, 1] = "BOND_TYPE:";
worksheet.Cells[startLine++, 2] = "WARRANTS";
worksheet.Cells[startLine++, 1] = "LEG1_STR:";
worksheet.Cells[startLine++, 1] = "LEG2_STR:";
worksheet.Cells[startLine++, 1] = "GN_TXT24_1:";
worksheet.Cells[startLine++, 1] = "GN_TXT24_2:";
//**************************For NDA***********************************
worksheet.Cells[startLine++, 1] = "---------------------------------------------------------------------------------------------------";
worksheet.Cells[startLine++, 1] = "**For NDA**";
worksheet.Cells[startLine++, 1] = "New Organisation listing";
startLine++;
worksheet.Cells[startLine++, 1] = "Primary Listing (RIC):";
worksheet.Cells[startLine, 1] = "IDN Longname:";
((Range)worksheet.Cells[startLine, 2]).NumberFormat = "@";
worksheet.Cells[startLine++, 2] = ricTemplate.IdnLongName;
worksheet.Cells[startLine++, 1] = "Organisation Name (DIRNAME):";
worksheet.Cells[startLine++, 1] = "Geographical Entity:";
worksheet.Cells[startLine++, 1] = "Organisation Type:";
worksheet.Cells[startLine++, 1] = "Alias (Previous Name):";
worksheet.Cells[startLine++, 1] = "Alias (General):";
worksheet.Cells[startLine++, 1] = "Issue Classification:";
worksheet.Cells[startLine++, 1] = "MSCI code:";
worksheet.Cells[startLine++, 1] = "Business Activity:";
startLine++;
worksheet.Cells[startLine++, 1] = "Existing Organisation listing";
startLine++;
worksheet.Cells[startLine++, 1] = "Primary Listing (RIC):";
worksheet.Cells[startLine, 1] = "Organisation Name (DIRNAME):";
worksheet.Cells[startLine++, 2] = ricTemplate.OrgnizationName2;
worksheet.Cells[startLine, 1] = "Issue Classification:";
worksheet.Cells[startLine++, 2] = "WNT";
//**************************For WRT_CNR***********************************
worksheet.Cells[startLine++, 1] = "---------------------------------------------------------------------------------------------------";
worksheet.Cells[startLine++, 1] = "**For WRT_CNR**";
worksheet.Cells[startLine, 1] = "Gearing:";
((Range)worksheet.Cells[startLine, 2]).NumberFormat = "0.00";
worksheet.Cells[startLine++, 2] = ricTemplate.Gearing;
worksheet.Cells[startLine, 1] = "Premium:";
((Range)worksheet.Cells[startLine, 2]).NumberFormat = "0.00";
worksheet.Cells[startLine++, 2] = ricTemplate.Premium;
worksheet.Cells[startLine, 1] = "Announcement Date:";
((Range)worksheet.Cells[startLine, 2]).NumberFormat = "@";
worksheet.Cells[startLine++, 2] = ricTemplate.AnnouncementDate;
worksheet.Cells[startLine, 1] = "Payment Date:";
((Range)worksheet.Cells[startLine, 2]).NumberFormat = "@";
worksheet.Cells[startLine++, 2] = ricTemplate.PaymentDate;
worksheet.Cells[startLine, 1] = "Call Level:";
((Range)worksheet.Cells[startLine, 2]).NumberFormat = "0.000";
worksheet.Cells[startLine++, 2] = ricTemplate.CallLevel;
worksheet.Cells[startLine++, 1] = "---------------------------------------------------------------------------------------------------";
startLine++;
}
#endregion
string fullPath = outputPath + "\\" + parent.RicTemplatePath + "\\" + parent.CbbcSubDir + "\\" + "HK" + (DateTime.Now.Year).ToString().Substring(2) + "-" + fmSerialNumberCBBC + "_SEC";
if (!Directory.Exists(Path.GetDirectoryName(fullPath)))
{
Directory.CreateDirectory(Path.GetDirectoryName(fullPath));
}
for (int j = start; j < end; j++)
{
fullPath += "_" + ricInfoList[j].Code;
}
fullPath += ".xls";
fullPath += "_" + DateTime.ParseExact(ricInfoList[0].ListingDate, "dd-MM-yyyy", null).ToString("ddMMMyyyy", new CultureInfo("en-US")) + ".xls";
workbook.SaveCopyAs(fullPath);
taskResultList.Add(new TaskResultEntry("CBBC FM File", "CBBC FM File Path", fullPath));
}
}
catch (Exception ex)
{
logger.LogErrorAndRaiseException("Generate CBBC FM Files Error: " + ex.Message);
}
}