public void Init_from_XMLobj(Hss_XML_obj hxo)
{
if (hxo == null)
{
return;
}
this.colKey_list.Clear();
string name = hxo.Get_attr("Key");
if (!string.IsNullOrEmpty(name))
{
this.Key = name;
}
List <Hss_XML_obj> list = hxo.Get_all_obj();
for (int i = 0; i < list.Count; ++i)
{
Hss_XML_obj xo = list[i];
string colKey = xo.value;
bool hidden = HssStr.True_or_False(xo.Get_attr("Hidden"));
HssGridColumn hgc = new HssGridColumn(colKey, hidden, i);
this.colKey_list.Add(hgc);
}
}
private void Parse_ElementName()
{
while (this.ReadNext_char())
{
if (this.blankChars_hs.Contains(this.currChar))
{
string eleName = HssStr.SafeXML_to_ori(this.sb.ToString());
this.element_st.Push(new Hss_XML_obj(eleName, this.ElementType));
this.sb.Clear();
this.ParsingState = XML_ParseState.InElement;
break;
}
else if (this.currChar == '>')
{
string eleName = HssStr.SafeXML_to_ori(this.sb.ToString());
this.element_st.Push(new Hss_XML_obj(eleName, this.ElementType));
this.sb.Clear();
this.ParsingState = XML_ParseState.OutElement;
break;
}
else
{
this.sb.Append(this.currChar);
}
}
}
/// <summary>
/// Create workbook.xml
/// </summary>
private void Create_WB_fromDS(DataSet ds, string fileName)
{
if (string.IsNullOrEmpty(fileName) || ds == null)
{
return;
}
if (ds.Tables.Count < 1)
{
return;
}
string header_str = "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" + HssStr.WinNextLine +
"<workbook xmlns='http://schemas.openxmlformats.org/spreadsheetml/2006/main' " + HssStr.WinNextLine +
"xmlns:r='http://schemas.openxmlformats.org/officeDocument/2006/relationships'><sheets>" + HssStr.WinNextLine;
FileStream fs = new FileStream(fileName, FileMode.Create);
byte[] header_bts = Encoding.UTF8.GetBytes(header_str);
fs.Write(header_bts, 0, header_bts.Length);
for (int i = 1; i <= ds.Tables.Count; ++i)
{
DataTable dt = ds.Tables[i - 1];
string str = "<sheet name=\"" + HssStr.ToSafeXML_str(dt.TableName) +
"\" sheetId='" + i + "' r:id='hssSheet" + i + "'/>" + HssStr.WinNextLine;
byte[] arr = Encoding.UTF8.GetBytes(str);
fs.Write(arr, 0, arr.Length);
++this.hLog.RecordNum;
}
byte[] tail_bts = Encoding.UTF8.GetBytes("</sheets></workbook>");
fs.Write(tail_bts, 0, tail_bts.Length);
fs.Close();
}
private StringBuilder Get_groupBy_sb(HashSet <ColumnTemplate> hs)
{
StringBuilder groupBy_sb = new StringBuilder();
if (!this.HasAggregateFunction(hs))
{
return(groupBy_sb);
}
groupBy_sb.Append(" Group by ");
int non_aggCount = 0;
foreach (ColumnTemplate ct in hs)
{
if (ct.AggregateFunc != AggregateFunction.None)
{
continue;
}
string colName = ct.ColName.Replace("]", "]]");
groupBy_sb.Append("[").Append(colName).Append("]").Append(',');
non_aggCount++;
}
HssStr.RemoveLastChar(groupBy_sb, ',');
if (non_aggCount < 1)
{
groupBy_sb.Clear();
}
return(groupBy_sb);
}
/// <summary>
/// Get the index of [Table_column]
/// </summary>
public void Calculate()
{
string tc_str = this.Table_Column.Value;
if (string.IsNullOrEmpty(tc_str))
{
return;
}
int index = 0, baseTen = 1, checkCount = 0;
for (int i = tc_str.Length - 1; i >= 0; --i)
{
char c = tc_str[i];
if (!HssStr.IsNum(c))
{
break;
}
index += (c - '0') * baseTen;
baseTen *= 10;
if (++checkCount >= DTC_Position_Headers.max_charCheckCount)
{
break;
}
}
if (checkCount > 0)
{
this.TableCol_index = index;
}
}
private void find_textBox_KeyPress(object sender, KeyPressEventArgs e)
{
if (e.KeyChar == 13 || e.KeyChar == 10)
{
string toFind = this.find_textBox.Text;
if (toFind.Length < 1)
{
return;
}
string allText = this.main_richTextBox.Text;
if (this.findText_list == null)
{
this.findText_list = HssStr.FindAll_substrings(allText, toFind, false);
}
if (this.findText_list.Count < 1)
{
MessageBox.Show("No Match");
}
else
{
this.main_richTextBox.Select(this.findText_list[this.currFind_index], toFind.Length);
if (++this.currFind_index >= this.findText_list.Count)
{
this.currFind_index = 0;
}
}
}
}
public string ToXML()
{
StringBuilder sb = new StringBuilder();
sb.Append("<col Hidden='").Append(this.hidden_flag).Append("'>");
sb.Append(HssStr.ToSafeXML_str(this.column_key)).Append("</col>");
return(sb.ToString());
}
public SqlCommand GetSQL_cmd()
{
if (this.template == null)
{
Console.WriteLine("DB_insert error 0: no template");
return(null);
}
if (string.IsNullOrEmpty(this.DBname) || string.IsNullOrEmpty(this.schema) || string.IsNullOrEmpty(this.tableName))
{
Console.WriteLine("DB_insert error 1: no name");
return(null);
}
if (col_val_dic.Count < 1)
{
Console.WriteLine("DB_insert error 2: no column");
return(null);
}
StringBuilder sql_sb = new StringBuilder("Insert into ");
sql_sb.Append("[" + this.DBname.Replace("]", "]]") + "]");
sql_sb.Append(".");
sql_sb.Append("[" + this.schema.Replace("]", "]]") + "]");
sql_sb.Append(".");
sql_sb.Append("[" + this.tableName.Replace("]", "]]") + "] ");
int tempID = 0;
SqlCommand cmd = new SqlCommand();
StringBuilder col_sb = new StringBuilder(), val_sb = new StringBuilder();
foreach (KeyValuePair <string, SqlParameter> pair in this.col_val_dic)
{
string colName = pair.Key.Replace("]", "]]");
string valName = "@Val" + tempID;
col_sb.Append("[").Append(colName).Append("]").Append(',');
val_sb.Append(valName).Append(',');
SqlParameter val_sp = new SqlParameter();
val_sp.Value = pair.Value.Value;
val_sp.SqlDbType = pair.Value.SqlDbType;
val_sp.ParameterName = valName;
cmd.Parameters.Add(val_sp);
++tempID;
}
HssStr.RemoveLastChar(col_sb, ',');
HssStr.RemoveLastChar(val_sb, ',');
sql_sb.Append("(").Append(col_sb).Append(")");
sql_sb.Append(" values ");
sql_sb.Append("(").Append(val_sb).Append(") ");
cmd.CommandText = sql_sb.ToString();
return(cmd);
}
private void Parse_ElementStart()
{
if (!this.ReadNext_char())
{
return;
}
if (this.currChar == '?')
{
this.sb.Clear();
this.ParsingState = XML_ParseState.ElementName;
this.ElementType = XML_ElementType.Declare;
}
else if (this.currChar == '!')
{
this.sb.Clear();
this.ParsingState = XML_ParseState.CommentValue;
this.ElementType = XML_ElementType.Comment;
}
else if (this.currChar == '>')
{
Hss_XML_obj xo = this.element_st.Peek();
xo.Add_obj(new Hss_XML_obj(null, this.ElementType));
this.ParsingState = XML_ParseState.OutElement;
}
else if (this.currChar == '/')
{
Hss_XML_obj xo = this.element_st.Pop();
xo.value = HssStr.SafeXML_to_ori(this.sb.ToString().Trim());
this.sb.Clear();
this.element_st.Peek().Add_obj(xo);
//xo.Show();//test output
this.ParsingState = XML_ParseState.ElementEnd;
}
else
{
this.sb.Clear();
this.sb.Append(this.currChar);
this.ParsingState = XML_ParseState.ElementName;
this.ElementType = XML_ElementType.Normal;
}
}
public string GetXML()
{
StringBuilder sb = new StringBuilder("<EVENT_INFORMATION>").Append(HssStr.WinNextLine);
sb.Append("<UniqueUniversalEventIdentifier>").Append(this.UniqueUniversalEventIdentifier).Append("</UniqueUniversalEventIdentifier>").Append(HssStr.WinNextLine);
sb.Append("<SECURITY_IDENTIFIER_CUSIP>").Append(this.SECURITY_IDENTIFIER_CUSIP).Append("</SECURITY_IDENTIFIER_CUSIP>").Append(HssStr.WinNextLine);
sb.Append("<ISSUER_NAME>").Append(HssStr.ToSafeXML_str(this.ISSUER_NAME)).Append("</ISSUER_NAME>").Append(HssStr.WinNextLine);
sb.Append("<ADR_RECORD_DATE>").Append(this.ADR_RECORD_DATE.ToString("yyyy-MM-dd")).Append("</ADR_RECORD_DATE>").Append(HssStr.WinNextLine);
sb.Append("<RATIO>").Append(HssStr.WinNextLine);
sb.Append("<ADRBase>").Append((int)this.ADRBase).Append("</ADRBase>").Append(HssStr.WinNextLine);
sb.Append("<OrdinaryShare>").Append((int)this.OrdinaryShare).Append("</OrdinaryShare>").Append(HssStr.WinNextLine);
sb.Append("</RATIO>").Append(HssStr.WinNextLine);
sb.Append("</EVENT_INFORMATION>").Append(HssStr.WinNextLine);
return(sb.ToString());
}
/*--------------------------------------------------------------------------------------------------------*/
/// <summary>
/// For in statement. e.g. "where [ID] in ('1','2')"
/// </summary>
/// <param name="colName">column name</param>
/// <param name="inFlag">true for in, false for not in</param>
/// <param name="val_hs">list of values</param>
private string Init_in(string colName, bool inFlag, HashSet <string> val_hs)
{
if (string.IsNullOrEmpty(colName))
{
return(null);
}
if (val_hs == null)
{
return(null);
}
StringBuilder list_sb = new StringBuilder();
foreach (string str in val_hs)
{
if (str == null)
{
continue;
}
string val_str = str.Replace("'", "''");
list_sb.Append("'").Append(val_str).Append("'").Append(',');
}
HssStr.RemoveLastChar(list_sb, ',');
StringBuilder sql_sb = new StringBuilder();
sql_sb.Append('[').Append(colName.Replace("]", "]]")).Append(']');
sql_sb.Append(' ');
if (inFlag)
{
sql_sb.Append("in");
}
else
{
sql_sb.Append("not in");
}
sql_sb.Append(' ');
sql_sb.Append('(').Append(list_sb).Append(')');
return(sql_sb.ToString());
}
/// <summary>
/// Create sharedStrings.xml
/// </summary>
private void Create_SharedString_fromDic(Dictionary <string, int> dic, string fileName)
{
if (string.IsNullOrEmpty(fileName) || dic == null)
{
return;
}
string header_str = "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" + HssStr.WinNextLine +
"<sst xmlns='http://schemas.openxmlformats.org/spreadsheetml/2006/main' " +
"count='" + dic.Count + "' uniqueCount='" + dic.Count + "'>" + HssStr.WinNextLine;
FileStream fs = new FileStream(fileName, FileMode.Create);
byte[] header_bts = Encoding.UTF8.GetBytes(header_str);
fs.Write(header_bts, 0, header_bts.Length);
StringBuilder sb = new StringBuilder();
int count = 0;
foreach (string s in dic.Keys)
{
sb.Append("<si><t>" + HssStr.ToSafeXML_str(s) + "</t></si>" + HssStr.WinNextLine);
if (++count % HssRawExcel.OneTimeAmount == 0)
{
byte[] arr = Encoding.UTF8.GetBytes(sb.ToString());
fs.Write(arr, 0, arr.Length);
sb.Clear();
}
++this.hLog.RecordNum;
}
if (sb.Length > 0)
{
byte[] arr = Encoding.UTF8.GetBytes(sb.ToString());
fs.Write(arr, 0, arr.Length);
}
byte[] tail_bts = Encoding.UTF8.GetBytes("</sst>");
fs.Write(tail_bts, 0, tail_bts.Length);
fs.Close();
}
private StringBuilder Get_selectCol_sb(HashSet <ColumnTemplate> hs)
{
StringBuilder col_sb = new StringBuilder();
if (hs == null || hs.Count == 0)
{
return(col_sb);
}
foreach (ColumnTemplate ct in hs)
{
string colName = "[" + ct.ColName.Replace("]", "]]") + "]";
string alia = ct.aliaName;
if (ct.AggregateFunc == AggregateFunction.Sum)
{
if (string.IsNullOrEmpty(alia))
{
alia = "sum_" + ct.ColName;
}
alia = "[" + alia.Replace("]", "]]") + "]";
col_sb.Append("Sum(").Append(colName).Append(") as ").Append(alia).Append(',');
}
else
{
if (string.IsNullOrEmpty(alia))
{
col_sb.Append(colName).Append(',');
}
else
{
alia = "[" + alia.Replace("]", "]]") + "]";
col_sb.Append(colName).Append(" as ").Append(alia).Append(',');
}
}
}
HssStr.RemoveLastChar(col_sb, ',');
return(col_sb);
}
public string Create_dividendID(int dvdID_offset = 0)
{
if (dvdID_offset > 49 || dvdID_offset < 0)
{
dvdID_offset = 0; //only support offset range 0 ~ 49 for now
}
Depositary depo = null;
int sponserID = 99;
string depoID = "00";
if (this.Sponsored.Value)
{
sponserID = 0 + dvdID_offset;
depo = DepositaryMaster.GetDepositary_by_name(this.Depositary.Value);
}
else
{
sponserID -= dvdID_offset;
depo = DepositaryMaster.GetDepositary_by_name(Depositaries.Depositary.Unsponsored);
}
if (depo != null)
{
depoID = depo.DepositaryID.Value;
}
StringBuilder dvdID_sb = new StringBuilder();
dvdID_sb.Append(HssStr.Trim_to_size(sponserID, 2));//sponsered or not
dvdID_sb.Append(HssStr.Trim_to_size(depoID, 2));
dvdID_sb.Append(HssStr.Trim_to_size(this.CUSIP.Value, 9));
dvdID_sb.Append(this.RecordDate_ADR.Value.ToString("MMddyyyy"));
dvdID_sb.Append(HssStr.Trim_to_size(this.IncomeEventID.Value, 2, '0'));
return(dvdID_sb.ToString());
}
/// <summary>
/// Map header_hs values to Rate_Position_1,2,3... in "Dividend_DTC_Position" table
/// Using [SortOrder] as reference to get the value
/// </summary>
/// <param name="header_hs">column header names in dataTable from excel</param>
/// <returns>Dictionary with [Table_Column] index as key</returns>
public Dictionary <int, List <string> > Get_colHeader_mapping(HashSet <string> header_hs)
{
Dictionary <int, List <string> > mapping = new Dictionary <int, List <string> >();
if (header_hs == null)
{
return(mapping);
}
//Rate_Position_1,2,3 as key
Dictionary <int, DTC_Position_Headers> dic = new Dictionary <int, DTC_Position_Headers>();
foreach (DTC_Position_Headers mi in this.all_headers_list)
{
dic[mi.TableCol_index] = mi;
}
List <DTC_Position_Headers> distinct_TC_list = new List <DTC_Position_Headers>();//we may have duplicate [Table_Column] in [DTC_Position_Headers]
foreach (DTC_Position_Headers mi in dic.Values)
{
distinct_TC_list.Add(mi);
}
distinct_TC_list.Sort((m1, m2) => (m1.SortOrder.Value - m2.SortOrder.Value));//excel file is created based on sort order
string header_keyWord = "Option";
foreach (string header in header_hs)
{
List <string> word_list = HssStr.Str_to_wordList(header);
if (word_list.Count < 2)
{
continue;
}
if (!word_list[0].Equals(header_keyWord, StringComparison.OrdinalIgnoreCase))
{
continue;
}
int index = -1;
if (!int.TryParse(word_list[1], out index))
{
continue;
}
if (index <= 0 || index > distinct_TC_list.Count)
{
continue;
}
else
{
--index;
}
int keyVal = distinct_TC_list[index].TableCol_index;
if (mapping.ContainsKey(keyVal))
{
mapping[keyVal].Add(header);
}
else
{
List <string> tempList = new List <string>();
tempList.Add(header);
mapping[keyVal] = tempList;
}
}
return(mapping);
}
/// <summary>
/// Create worksheets\sheet1.xml, sheet2.xml, sheet3.xml...
/// </summary>
private void Create_Sheet_fromDT(DataTable dt, string fileName, Dictionary <string, int> dic)
{
if (string.IsNullOrEmpty(fileName) || dt == null || dic == null)
{
return;
}
string header_str = "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" + HssStr.WinNextLine +
"<worksheet xmlns='http://schemas.openxmlformats.org/spreadsheetml/2006/main'><sheetData>" + HssStr.WinNextLine;
FileStream fs = new FileStream(fileName, FileMode.Create);
byte[] header_bts = Encoding.UTF8.GetBytes(header_str);
fs.Write(header_bts, 0, header_bts.Length);
int colCount = dt.Columns.Count;
//data columns
StringBuilder col_sb = new StringBuilder("<row r='1'>").Append(HssStr.WinNextLine);
for (int c = 0; c < colCount; ++c)
{
DataColumn col = dt.Columns[c];
string val_str = col.ColumnName;
if (string.IsNullOrEmpty(val_str))
{
continue;
}
if (!dic.ContainsKey(val_str))
{
continue;
}
int sharedStrID = dic[val_str];
string colHeader_str = HssStr.Excel_ID_to_Title(c);
col_sb.Append("<c r='" + colHeader_str + "1' t='s'><v>");
col_sb.Append(sharedStrID);
col_sb.Append("</v></c>").Append(HssStr.WinNextLine);
++this.hLog.RecordNum;
}
col_sb.Append("</row>").Append(HssStr.WinNextLine);
byte[] col_arr = Encoding.UTF8.GetBytes(col_sb.ToString());
fs.Write(col_arr, 0, col_arr.Length);
//data rows
StringBuilder row_sb = new StringBuilder();
for (int i = 0, r = 2; i < dt.Rows.Count; ++r, ++i)
{
DataRow row = dt.Rows[i];
StringBuilder sb = new StringBuilder("<row r='" + r + "'>").Append(HssStr.WinNextLine);
for (int c = 0; c < colCount; ++c)
{
string val_str = row[c].ToString();
if (string.IsNullOrEmpty(val_str))
{
continue;
}
if (!dic.ContainsKey(val_str))
{
continue;
}
int sharedStrID = dic[val_str];
string colHeader_str = HssStr.Excel_ID_to_Title(c);
sb.Append("<c r='" + colHeader_str + r + "' t='s'><v>");
sb.Append(sharedStrID);
sb.Append("</v></c>").Append(HssStr.WinNextLine);
}
sb.Append("</row>").Append(HssStr.WinNextLine);
row_sb.Append(sb);
if (r % HssRawExcel.OneTimeAmount == 0)
{
byte[] arr = Encoding.UTF8.GetBytes(row_sb.ToString());
fs.Write(arr, 0, arr.Length);
row_sb.Clear();
}
++this.hLog.RecordNum;
}
if (row_sb.Length > 0)
{
byte[] arr = Encoding.UTF8.GetBytes(row_sb.ToString());
fs.Write(arr, 0, arr.Length);
}
//last part
byte[] tail_bts = Encoding.UTF8.GetBytes("</sheetData></worksheet>");
fs.Write(tail_bts, 0, tail_bts.Length);
fs.Close();
}
public SqlCommand GetSQL_cmd()
{
if (this.template == null)
{
Console.WriteLine("DB_update error 0: no template");
return(null);
}
if (string.IsNullOrEmpty(this.DBname) || string.IsNullOrEmpty(this.schema) || string.IsNullOrEmpty(this.tableName))
{
Console.WriteLine("DB_update error 1: no name");
return(null);
}
if (col_val_dic.Count < 1)
{
Console.WriteLine("DB_update error 2: no column");
return(null);
}
if (!this.ExistCondition())
{
if (MessageBox.Show("No condition in update SQL", "Warning", MessageBoxButtons.YesNo) != DialogResult.Yes)
{
return(null);
}
}
StringBuilder sql_sb = new StringBuilder("Update ");
sql_sb.Append("[" + this.DBname.Replace("]", "]]") + "]");
sql_sb.Append(".");
sql_sb.Append("[" + this.schema.Replace("]", "]]") + "]");
sql_sb.Append(".");
sql_sb.Append("[" + this.tableName.Replace("]", "]]") + "]");
sql_sb.Append(" set ");
StringBuilder val_sb = new StringBuilder();
SqlCommand cmd = new SqlCommand();
int tempID = 0;
foreach (KeyValuePair <string, SqlParameter> pair in this.col_val_dic)
{
string colName = pair.Key.Replace("]", "]]");
string valName = "@Val" + tempID;
val_sb.Append("[").Append(colName).Append("]");
val_sb.Append(" = ");
val_sb.Append(valName).Append(", ");
SqlParameter val_sp = new SqlParameter();
val_sp.Value = pair.Value.Value;
val_sp.SqlDbType = pair.Value.SqlDbType;
val_sp.ParameterName = valName;
cmd.Parameters.Add(val_sp);
++tempID;
}
HssStr.RemoveLastChar(val_sb, ',');
sql_sb.Append(val_sb);
if (this.ExistCondition())
{
sql_sb.Append(" where ").Append(this.condition.SQL_str);
}
cmd.CommandText = sql_sb.ToString();
return(cmd);
}
