//Insert an item to db
private bool insert(TWISINOffclCode info, string connectionString)
{
try
{
using (var conn = new SqlConnection(connectionString))
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
using (var comm = new SqlCommand())
{
comm.Connection = conn;
comm.CommandText = "insert into ETI_TW_OFFCLCODE_ISIN(ISIN,OFFCLCODE) values(@ISIN,@OFFCLCODE)";
comm.Parameters.Add(new SqlParameter("@ISIN", info.ISIN));
comm.Parameters.Add(new SqlParameter("@OFFCLCODE", info.OffclCode));
int rowAffected = comm.ExecuteNonQuery();
if (rowAffected == 0)
{
return(false);
}
return(true);
}
}
}
catch (Exception)
{
return(false);
}
}
private List <TWISINOffclCode> GetISINList(HtmlNode rootNode)
{
var offclCodeISINList = new List <TWISINOffclCode>();
HtmlNodeCollection collection = rootNode.SelectNodes("//tr");
string kindOfOption = "";
foreach (var node in collection.Skip(1))
{
if (node.SelectSingleNode("./td").Attributes["colspan"] != null)
{
kindOfOption = MiscUtil.GetCleanTextFromHtml(node.InnerText);
continue;
}
var ISINOffclCode = new TWISINOffclCode();
HtmlNodeCollection tempCollection = node.SelectNodes("./td");
int index = tempCollection[0].InnerText.IndexOf(" ");
if (index < 0)
{
index = tempCollection[0].InnerText.IndexOf(" ");
}
ISINOffclCode.OffclCode = tempCollection[0].InnerText.Substring(0, index);
ISINOffclCode.ISIN = tempCollection[1].InnerText;
ISINOffclCode.FutureOptionType = kindOfOption;
offclCodeISINList.Add(ISINOffclCode);
}
return(offclCodeISINList);
}
//compose Ric based on the offcl code and the future/ option type
private string ComposeRic(TWISINOffclCode isinOffclCode)
{
string ric = string.Empty;
if (isinOffclCode.FutureOptionType.Contains("期貨"))
{
FutureRicStructure stru = FindFutureRicStructure(isinOffclCode.FutureOptionType, _ricStructureSummaryObj.FutureRicStructureList);
ric = ComposeFutureRic(isinOffclCode.OffclCode, stru.OffclCodeIndexes, stru.PreMonthCodeStr);
}
else if ((isinOffclCode.FutureOptionType == "股票選擇權買權") || (isinOffclCode.FutureOptionType == "股票選擇權賣權"))
{
ric = ComposeRicForSpecialCase(isinOffclCode.OffclCode, _codeRicRootMap);
}
else
{
OptionRicStructure stru = FindOptionRicStructure(isinOffclCode.FutureOptionType, _ricStructureSummaryObj.OptionRicStructureList);
ric = ComposeOptionRic(isinOffclCode.OffclCode, stru.SourcePrefix, stru.ReplacedPrefix, stru.Suffix);
}
return(ric);
}
//Get all the items in DB
private List <TWISINOffclCode> GetAll(string connectionString)
{
var infoList = new List <TWISINOffclCode>();
try
{
using (var conn = new SqlConnection(connectionString))
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
using (var comm = new SqlCommand("select * from ETI_TW_OFFCLCODE_ISIN", conn))
{
using (SqlDataReader dr = comm.ExecuteReader())
{
if (dr.HasRows)
{
while (dr.Read())
{
var info = new TWISINOffclCode
{
OffclCode = Convert.ToString(dr["offclCode"]),
ISIN = Convert.ToString(dr["ISIN"])
};
infoList.Add(info);
}
}
}
}
}
return(infoList);
}
catch (Exception ex)
{
LogMessage("Exception happens when getting TWISINOffclCode list from the DB. ex: " + ex.Message, Logger.LogType.Error);
return(null);
}
}
