private static DB_reader Get_DB_reader(HssStatus status, SQL_condition extraCond)
{
DB_select selt = new DB_select(XBRL_SavedFile.Get_cmdTP());
selt.IgnoreColumn("savedfile");
SQL_relation rela = null;
if (status == HssStatus.Pending || status == HssStatus.Approved)
{
rela = new SQL_relation("processState", RelationalOperator.Equals, (int)status);
}
else
{
HashSet <int> hs = new HashSet <int>();
hs.Add(0); hs.Add(1);
rela = new SQL_relation("processState", false, hs);
}
SQL_condition cond = new SQL_condition(rela, ConditionalOperator.And, extraCond);
selt.SetCondition(cond);
DB_reader reader = new DB_reader(selt, Utility.Get_XBRL_hDB());
return(reader);
}
public static List <Task_Detail> GetTaskDetail_list(int taskID, string sourceID, string taskName)
{
List <Task_Detail> list = new List <Task_Detail>();
DB_select db_sel = new DB_select(Task_Detail.Get_cmdTP());
SQL_relation rela0 = new SQL_relation("TaskID", RelationalOperator.Equals, taskID);
SQL_relation rela1 = new SQL_relation("sourceID", RelationalOperator.Equals, sourceID);
SQL_condition cond = new SQL_condition(rela0, ConditionalOperator.And, rela1);
if (taskName != null)
{
SQL_relation rela2 = new SQL_relation("TaskName", RelationalOperator.Equals, taskName);
cond = new SQL_condition(cond, ConditionalOperator.And, rela2);
}
db_sel.SetCondition(cond);
DB_reader reader = new DB_reader(db_sel, Utility.Get_DRWIN_hDB());
while (reader.Read())
{
Task_Detail dp = new Task_Detail();
dp.Init_from_reader(reader);
list.Add(dp);
}
reader.Close();
return(list);
}
public static void Delete_details(HashSet <int> detail_hs)
{
if (detail_hs == null || detail_hs.Count < 1)
{
return;
}
DB_delete del = new DB_delete();
del.tableName = DividendDetail_master.Detail_tableName;
SQL_relation rela = new SQL_relation("DetailID", true, detail_hs);
del.SetCondition(rela);
int count = del.SaveToDB(Utility.Get_DRWIN_hDB());
if (count > 0)
{
MessageBox.Show("Detail Deleted! (" + count + ")");
}
else
{
MessageBox.Show("Nothing deleted?!");
}
}
/****************************************************************************************************************/
private void CreateSqlCondition()
{
this.cond = null;
ConditionalOperator copt = this.and_radioButton.Checked ? ConditionalOperator.And : ConditionalOperator.Or;
foreach (Filter_UserControl fuc in this.ctl_list)
{
SQL_relation rela = fuc.SQL_Rela;
if (rela == null)
{
continue;
}
if (this.cond == null)
{
this.cond = new SQL_condition(rela);
}
else
{
this.cond = new SQL_condition(this.cond, copt, rela);
}
}
this.Close();
}
/// <summary>
/// Get DividendCustodian dictionary, [CustodianID] as key
/// </summary>
public Dictionary <int, DividendCustodian> Get_dvdCust_dic(string custodianType)
{
Dictionary <int, DividendCustodian> dic = new Dictionary <int, DividendCustodian>();
DB_select selt = new DB_select(DividendCustodian.Get_cmdTP());
SQL_relation rela0 = new SQL_relation("DividendIndex", RelationalOperator.Equals, this.DividendIndex);
if (string.IsNullOrEmpty(custodianType))
{
selt.SetCondition(rela0);
}
else
{
SQL_relation rela1 = new SQL_relation("Custodian_Type", RelationalOperator.Equals, custodianType);
SQL_condition cond = new SQL_condition(rela0, ConditionalOperator.And, rela1);
selt.SetCondition(cond);
}
DB_reader reader = new DB_reader(selt, Utility.Get_DRWIN_hDB());
while (reader.Read())
{
DividendCustodian dc = new DividendCustodian();
dc.Init_from_reader(reader);
dic[dc.CustodianID] = dc;
}
reader.Close();
return(dic);
}
public static List <DividendXBRL> GetList_refNo(string refNo)
{
List <DividendXBRL> dxList = new List <DividendXBRL>();
if (string.IsNullOrEmpty(refNo))
{
return(dxList);
}
DB_select selt = new DB_select(DividendXBRL.Get_cmdTP());
SQL_relation rela = new SQL_relation("XBRL_ReferenceNumber", RelationalOperator.Equals, refNo);
selt.SetCondition(rela);
DB_reader reader = new DB_reader(selt, Utility.Get_DRWIN_hDB());
while (reader.Read())
{
DividendXBRL dx = new DividendXBRL();
dx.Init_from_reader(reader);
dxList.Add(dx);
}
reader.Close();
return(dxList);
}
private HashSet <int> Get_currFX_ID_set(string taskName)
{
HashSet <int> hs = new HashSet <int>();
CmdTemplate tp = new CmdTemplate();
tp.schema = "Task";
tp.tableName = "Task_Detail";
tp.AddColumn("SourceID");
DB_select selt = new DB_select(tp);
SQL_relation rel0 = new SQL_relation("TaskID", RelationalOperator.Equals, Task20_master.TaskID_20);
SQL_relation rel1 = new SQL_relation("TaskName", RelationalOperator.Equals, taskName);
SQL_condition cond0 = new SQL_condition(rel0), cond1 = new SQL_condition(rel1);
SQL_condition cond = new SQL_condition(cond0, ConditionalOperator.And, cond1);
selt.SetCondition(cond);
DB_reader reader = new DB_reader(selt, Utility.Get_DRWIN_hDB());
while (reader.Read())
{
int tempInt = -1;
string tempStr = reader.GetString("SourceID");
if (int.TryParse(tempStr, out tempInt))
{
hs.Add(tempInt);
}
}
reader.Close();
return(hs);
}
/// <summary>
/// Get Custodian dictionary, [Custodian_Number] as key
/// </summary>
public Dictionary <int, Custodian> Get_custodian_dic()
{
HashSet <int> num_hs = new HashSet <int>();
foreach (DividendCustodian dc in this.Get_dvdCust_dic(null).Values)
{
num_hs.Add(dc.Custodian_Number.Value);
}
Dictionary <int, Custodian> dic = new Dictionary <int, Custodian>();
DB_select selt = new DB_select(Custodian.Get_cmdTP());
SQL_relation rela = new SQL_relation("Custodian_Number", true, num_hs);
selt.SetCondition(rela);
DB_reader reader = new DB_reader(selt, Utility.Get_DRWIN_hDB());
while (reader.Read())
{
Custodian cust = new Custodian();
cust.Init_from_reader(reader);
dic[cust.Custodian_Number] = cust;
}
reader.Close();
return(dic);
}
private Dictionary <int, BD_Dividend> CreateDvdDic(HashSet <int> dvdIndex_hs)
{
this.statusInfo.status = "Create Dividend Dictionary";
Dictionary <int, BD_Dividend> dic = new Dictionary <int, BD_Dividend>();
if (dvdIndex_hs == null)
{
return(dic);
}
DB_select selt = new DB_select(BD_Dividend.Get_cmdTP());
SQL_relation rela = new SQL_relation("DividendIndex", true, dvdIndex_hs);
selt.SetCondition(rela);
DB_reader reader = new DB_reader(selt, Utility.Get_DRWIN_hDB());
while (reader.Read())
{
BD_Dividend dvd = new BD_Dividend();
dvd.Init_from_reader(reader);
dic[dvd.DividendIndex] = dvd;
++this.statusInfo.recordNum;
}
reader.Close();
return(dic);
}
public static Hss_DailyJobs GetJob_class_id(string jobClass, int jobID)
{
if (string.IsNullOrEmpty(jobClass))
{
return(null);
}
DB_select selt = new DB_select(Hss_DailyJobs.Get_cmdTP());
SQL_relation rela0 = new SQL_relation("Job_Class", RelationalOperator.Equals, jobClass);
SQL_relation rela1 = new SQL_relation("Job_ID", RelationalOperator.Equals, jobID);
selt.SetCondition(new SQL_condition(rela0, ConditionalOperator.And, rela1));
Hss_DailyJobs hdj = null;
DB_reader reader = new DB_reader(selt, Utility.Get_DRWIN_hDB());
if (reader.Read())
{
hdj = new Hss_DailyJobs();
hdj.Init_from_reader(reader);
}
reader.Close();
return(hdj);
}
public static List <Schedule_Of_Fees_DSC> GetList_from_CD(string country, string depositary)
{
List <Schedule_Of_Fees_DSC> list = new List <Schedule_Of_Fees_DSC>();
if (string.IsNullOrEmpty(country) || string.IsNullOrEmpty(depositary))
{
return(list);
}
DB_select selt = new DB_select(Schedule_Of_Fees_DSC.Get_cmdTP());
SQL_relation rela1 = new SQL_relation("Country", RelationalOperator.Equals, country);
SQL_relation rela2 = new SQL_relation("Depositary", RelationalOperator.Equals, depositary);
SQL_condition cond = new SQL_condition(new SQL_condition(rela1), ConditionalOperator.And, new SQL_condition(rela2));
selt.SetCondition(cond);
DB_reader reader = new DB_reader(selt, Utility.Get_DRWIN_hDB());
while (reader.Read())
{
Schedule_Of_Fees_DSC sof = new Schedule_Of_Fees_DSC();
sof.Init_from_reader(reader);
list.Add(sof);
}
reader.Close();
list.Sort((a, b) => DateTime.Compare(b.EffectiveDate.Value, a.EffectiveDate.Value));
return(list);
}
private SQL_condition GetForm_SQLcondition()
{
HashSet <string> country_hs = new HashSet <string>(StringComparer.OrdinalIgnoreCase);
foreach (object obj in this.cty_checkedListBox.CheckedItems)
{
country_hs.Add(obj.ToString());
}
HashSet <string> taskID_hs = new HashSet <string>(StringComparer.OrdinalIgnoreCase);
foreach (object obj in this.taskID_checkedListBox.CheckedItems)
{
taskID_hs.Add(obj.ToString());
}
SQL_relation rela0 = null, rela1 = null;
if (country_hs.Count > 0)
{
rela0 = new SQL_relation("Country", true, country_hs);
}
if (taskID_hs.Count > 0)
{
rela1 = new SQL_relation("TaskID", true, taskID_hs);
}
if (rela0 == null && rela1 == null)
{
return(null);
}
return(new SQL_condition(rela0, ConditionalOperator.And, rela1));
}
public int Delete_CustodianID_dvdDetail(string reclaimFeeType)
{
DB_update upd = new DB_update(Dividend_Detail_full.Get_cmdTP());
upd.AddNull("CustodianID");
SQL_relation rela0 = new SQL_relation("DividendIndex", RelationalOperator.Equals, this.DividendIndex);
if (string.IsNullOrEmpty(reclaimFeeType))
{
upd.SetCondition(rela0);
}
else
{
SQL_relation rela1 = new SQL_relation("ReclaimFeesType", RelationalOperator.Equals, reclaimFeeType);
SQL_condition cond = new SQL_condition(rela0, ConditionalOperator.And, rela1);
upd.SetCondition(cond);
}
Helper_SQLserver.AllTrigers("Dividend_Detail", false, Utility.Get_DRWIN_hDB());
int count = upd.SaveToDB(Utility.Get_DRWIN_hDB());
Helper_SQLserver.AllTrigers("Dividend_Detail", true, Utility.Get_DRWIN_hDB());
return(count);
}
public List <Dividend_Detail_simpleAP> Get_dvdDetailAP_list(string reclaimFeeType)
{
List <Dividend_Detail_simpleAP> list = new List <Dividend_Detail_simpleAP>();
DB_select selt = new DB_select(Dividend_Detail_simpleAP.Get_cmdTP());
SQL_relation rela0 = new SQL_relation("DividendIndex", RelationalOperator.Equals, this.DividendIndex);
if (string.IsNullOrEmpty(reclaimFeeType))
{
selt.SetCondition(rela0);
}
else
{
SQL_relation rela1 = new SQL_relation("ReclaimFeesType", RelationalOperator.Equals, reclaimFeeType);
SQL_condition cond = new SQL_condition(rela0, ConditionalOperator.And, rela1);
selt.SetCondition(cond);
}
DB_reader reader = new DB_reader(selt, Utility.Get_DRWIN_hDB());
while (reader.Read())
{
Dividend_Detail_simpleAP dd = new Dividend_Detail_simpleAP();
dd.Init_from_reader(reader);
list.Add(dd);
}
reader.Close();
return(list);
}
public static List <SPR_file> Get_SRP_fileList(HssStatus status, bool all_flag = false)
{
List <SPR_file> list = new List <SPR_file>();
DB_select selt = new DB_select(SPR_file.Get_cmdTP());
selt.IgnoreColumn("FileBinary");
if (!all_flag)
{
SQL_relation rela = new SQL_relation("Status", RelationalOperator.Equals, (int)status);
selt.SetCondition(rela);
}
DB_reader reader = new DB_reader(selt, Utility.Get_DRWIN_hDB());
while (reader.Read())
{
SPR_file sf = new SPR_file();
sf.Init_from_reader(reader);
list.Add(sf);
}
reader.Close();
return(list);
}
public static List <Dividend> Get_DividendList_CUSIP(string CUSIP, DividendTable_option table = DividendTable_option.Dividend_Control_Approved)
{
List <Dividend> dvdList = new List <Dividend>();
if (string.IsNullOrEmpty(CUSIP))
{
return(dvdList);
}
DB_select selt = new DB_select(Dividend.Get_cmdTP());
selt.tableName = table.ToString();
SQL_relation rela = new SQL_relation("CUSIP", RelationalOperator.Equals, CUSIP);
selt.SetCondition(rela);
DB_reader reader = new DB_reader(selt, Utility.Get_DRWIN_hDB());
while (reader.Read())
{
Dividend dvd = new Dividend();
dvd.Init_from_reader(reader);
dvdList.Add(dvd);
}
reader.Close();
return(dvdList);
}
public void SetTable(string tableName, string schema, hssDB hDB)
{
if (hDB == null || !hDB.Connected)
{
MessageBox.Show("AutoCreatClass error 0: No DB connection");
return;
}
this.col_list.Clear();
this.tableName = tableName;
if (string.IsNullOrEmpty(this.className))
{
this.className = tableName;
}
this.schema = schema;
DB_select selt = new DB_select(ColumnSchema.Get_cmdTP());
SQL_relation rela1 = new SQL_relation("TABLE_NAME", RelationalOperator.Equals, tableName);
SQL_relation rela2 = new SQL_relation("TABLE_SCHEMA", RelationalOperator.Equals, schema);
SQL_condition cond = new SQL_condition(new SQL_condition(rela1), ConditionalOperator.And, new SQL_condition(rela2));
selt.SetCondition(cond);
DB_reader reader = new DB_reader(selt, hDB);
while (reader.Read())
{
ColumnSchema cs = new ColumnSchema();
cs.Init_from_reader(reader);
this.col_list.Add(cs);
}
reader.Close();
this.col_list.Sort((a, b) => (a.ORDINAL_POSITION - b.ORDINAL_POSITION));
}
public static Dictionary <int, DateTime> Get_DateTime_values(List <int> detailID_list, string colName)
{
Dictionary <int, DateTime> dic = new Dictionary <int, DateTime>();
if (detailID_list == null || detailID_list.Count < 1)
{
return(dic);
}
if (string.IsNullOrEmpty(colName))
{
return(dic);
}
List <List <int> > splited_list = new List <List <int> >();//split detailID_list into small lists
List <int> tempList = new List <int>();
foreach (int ID in detailID_list)
{
tempList.Add(ID);
if (tempList.Count >= 100)
{
splited_list.Add(tempList);
tempList = new List <int>();
}
}
if (tempList.Count > 0)
{
splited_list.Add(tempList);
}
CmdTemplate cmdTp = new CmdTemplate();//create template for DB
cmdTp.tableName = DividendDetail_master.Detail_tableName;
cmdTp.AddColumn(DividendDetail_master.Detail_PK_colName);
cmdTp.AddColumn(colName);
DB_select selt = new DB_select(cmdTp);
foreach (List <int> list in splited_list)
{
SQL_relation rela = new SQL_relation(DividendDetail_master.Detail_PK_colName, true, list);
selt.SetCondition(rela);
DB_reader reader = new DB_reader(selt, Utility.Get_DRWIN_hDB());
while (reader.Read())
{
int detailID = reader.GetInt(DividendDetail_master.Detail_PK_colName);
DateTime dt = reader.GetDateTime(colName);
dic[detailID] = dt;
}
reader.Close();
}
return(dic);
}
public void Delete_DTC_position()
{
if (this.DividendIndex < 0)
{
return;
}
DB_delete del = new DB_delete();
del.tableName = "Dividend_DTC_Position";
SQL_relation rela = new SQL_relation("DividendIndex", RelationalOperator.Equals, this.DividendIndex);
del.SetCondition(rela);
int count = del.SaveToDB(Utility.Get_DRWIN_hDB());
}
private void Init_from_DB(HashSet <int> dtcNum_hs)
{
this.Reset();
Participant.Init_cmdTP();
DB_select selt = new DB_select(Participant.DBcmd_TP);
SQL_relation rela = new SQL_relation("DTC", true, dtcNum_hs);
selt.SetCondition(rela);
DB_reader reader = new DB_reader(selt, Utility.Get_DRWIN_hDB());
while (reader.Read())
{
Participant pi = new Participant();
pi.Init_from_reader(reader);
this.parti_dic[pi.DTC] = pi;
}
reader.Close();
}
public static Event GetEvent_DividendIndex(int dvdIndex)
{
DB_select selt = new DB_select(Event.Get_cmdTP());
SQL_relation rela = new SQL_relation("dividend_index", RelationalOperator.Equals, dvdIndex);
selt.SetCondition(rela);
Event eve = null;
DB_reader reader = new DB_reader(selt, Utility.Get_ESP2_hDB());
if (reader.Read())
{
eve = new Event();
eve.Init_from_reader(reader);
}
reader.Close();
return(eve);
}
public void Create_headers()
{
DB_select selt = new DB_select(DTC_Position_Headers.Get_cmdTP());
SQL_relation rela = new SQL_relation("ModelNumber", RelationalOperator.Equals, this.ModelNumber.Value);
selt.SetCondition(rela);
this.all_headers_list.Clear();
DB_reader reader = new DB_reader(selt, Utility.Get_DRWIN_hDB());
while (reader.Read())
{
DTC_Position_Headers dph = new DTC_Position_Headers();
dph.Init_from_reader(reader);
dph.Calculate();
this.all_headers_list.Add(dph);
}
reader.Close();
}
private void Cal_ClaimShares_ADRS_SUM(Dictionary <int, BD_Dividend> dic)
{
this.statusInfo.status = "Calculate ClaimShares_ADRS_SUM";
DB_select selt = new DB_select(BD_detailSum.Get_cmdTP());
SQL_relation rela0 = new SQL_relation("Status", RelationalOperator.NotEqual, "REJECTED");
SQL_relation rela1 = new SQL_relation("ReceivedDate", RelationalOperator.GreaterThan, "1900-1-1");
SQL_condition cond0 = new SQL_condition(rela0, ConditionalOperator.And, rela1);
SQL_relation rela2 = new SQL_relation("RecordDatePosition", RelationalOperator.GreaterThan, 0);
selt.SetCondition(new SQL_condition(cond0, ConditionalOperator.And, rela2));
DB_reader reader = new DB_reader(selt, Utility.Get_DRWIN_hDB());
int count = 0;
while (reader.Read())
{
BD_detailSum detail = new BD_detailSum();
detail.Init_from_reader(reader);
if (!dic.ContainsKey(detail.DividendIndex))
{
continue;
}
BD_Dividend dvd = this.dvd_dic[detail.DividendIndex];
if (!dvd.bdp_dic.ContainsKey(detail.DTC_Number))
{
continue;
}
BD_DTC_Position bdp = dvd.bdp_dic[detail.DTC_Number];
bdp.ClaimShares_ADRS_SUM = detail.ClaimShares_ADRS_SUM;
++count;
++this.statusInfo.recordNum;
}
reader.Close();
this.statusInfo.additional_info_list.Add("Total BD_detailSum count=" + count);
}
public static DividendXBRL Get_dvdXBRL(string refNo, int dvdIndex)
{
DB_select selt = new DB_select(DividendXBRL.Get_cmdTP());
SQL_relation rela0 = new SQL_relation("XBRL_ReferenceNumber", RelationalOperator.Equals, refNo);
SQL_relation rela1 = new SQL_relation("DividendIndex", RelationalOperator.Equals, dvdIndex);
selt.SetCondition(new SQL_condition(rela0, ConditionalOperator.And, rela1));
DividendXBRL dx = null;
DB_reader reader = new DB_reader(selt, Utility.Get_DRWIN_hDB());
if (reader.Read())
{
dx = new DividendXBRL();
dx.Init_from_reader(reader);
}
reader.Close();
return(dx);
}
public static bool Check_ID_exist(string id, string tableName, string columnName, string schema = "dbo")
{
CmdTemplate tp = new CmdTemplate();
tp.schema = schema;
tp.tableName = tableName;
tp.AddColumn(columnName);
DB_select selt = new DB_select(tp);
SQL_relation rela = new SQL_relation(columnName, RelationalOperator.Equals, id);
selt.SetCondition(rela);
DB_reader reader = new DB_reader(selt, Utility.Get_DRWIN_hDB());
bool flag = reader.Read();
reader.Close();
return(flag);
}
public List <Dividend_DTC_Position> Get_DTCposition_list()
{
List <Dividend_DTC_Position> lst = new List <Dividend_DTC_Position>();
DB_select selt = new DB_select(Dividend_DTC_Position.Get_cmdTP());
SQL_relation rela = new SQL_relation("DividendIndex", RelationalOperator.Equals, this.DividendIndex);
selt.SetCondition(rela);
DB_reader reader = new DB_reader(selt, Utility.Get_DRWIN_hDB());
while (reader.Read())
{
Dividend_DTC_Position pos = new Dividend_DTC_Position();
pos.Init_from_reader(reader);
lst.Add(pos);
}
reader.Close();
return(lst);
}
public static List <Security> Get_secList_cusip(string cusip)
{
List <Security> secList = new List <Security>();
DB_select selt = new DB_select(Security.Get_cmdTP());
SQL_relation rela = new SQL_relation("cusip", RelationalOperator.Equals, cusip);
selt.SetCondition(rela);
DB_reader reader = new DB_reader(selt, Utility.Get_ESP2_hDB());
while (reader.Read())
{
Security sec = new Security();
sec.Init_from_reader(reader);
secList.Add(sec);
}
reader.Close();
return(secList);
}
public static List <Dividend_Filing> GetList_dvdIndex(int dvdIndex)
{
List <Dividend_Filing> df_list = new List <Dividend_Filing>();
DB_select selt = new DB_select(Dividend_Filing.Get_cmdTP());
SQL_relation rela = new SQL_relation("DividendIndex", RelationalOperator.Equals, dvdIndex);
selt.SetCondition(rela);
DB_reader reader = new DB_reader(selt, Utility.Get_DRWIN_hDB());
while (reader.Read())
{
Dividend_Filing df = new Dividend_Filing();
df.Init_from_reader(reader);
df_list.Add(df);
}
reader.Close();
return(df_list);
}
public static List <RSH_log> Get_rshList_dvdIndex(int dvdIndex)
{
List <RSH_log> RSH_list = new List <RSH_log>();
DB_select selt = new DB_select(RSH_log.Get_cmdTP());
SQL_relation rela = new SQL_relation("DividendIndex", RelationalOperator.Equals, dvdIndex);
selt.SetCondition(rela);
DB_reader reader = new DB_reader(selt, Utility.Get_DRWIN_hDB());
while (reader.Read())
{
RSH_log rsh = new RSH_log();
rsh.Init_from_reader(reader);
RSH_list.Add(rsh);
}
reader.Close();
return(RSH_list);
}
public static List <Dividend_Payment> GetPayments_FX(int fxInput_id)
{
List <Dividend_Payment> list = new List <Dividend_Payment>();
DB_select db_sel = new DB_select(Dividend_Payment.Get_cmdTP());
SQL_relation rela = new SQL_relation("FX_InputID", RelationalOperator.Equals, fxInput_id);
db_sel.SetCondition(rela);
DB_reader reader = new DB_reader(db_sel, Utility.Get_DRWIN_hDB());
while (reader.Read())
{
Dividend_Payment dp = new Dividend_Payment();
dp.Init_from_reader(reader);
list.Add(dp);
}
reader.Close();
return(list);
}