public static CmdTemplate Get_cmdTP()
{
if (Event.DBcmd_TP != null)
{
return(Event.DBcmd_TP);
}
Event.DBcmd_TP = new CmdTemplate();
Event.DBcmd_TP.tableName = "Event";
Event.DBcmd_TP.AddColumn("event_id");
Event.DBcmd_TP.AddColumn("dividend_index");
Event.DBcmd_TP.AddColumn("security_id");
Event.DBcmd_TP.AddColumn("record_date_adr");
Event.DBcmd_TP.AddColumn("pay_date_adr");
Event.DBcmd_TP.AddColumn("record_date_ord");
Event.DBcmd_TP.AddColumn("pay_date_ord");
Event.DBcmd_TP.AddColumn("deadline_at_source");
Event.DBcmd_TP.AddColumn("deadline_quick_refund");
Event.DBcmd_TP.AddColumn("deadline_long_form");
Event.DBcmd_TP.AddColumn("depositary_info_id");
Event.DBcmd_TP.AddColumn("market_statute_of_limitations");
Event.DBcmd_TP.AddColumn("enabled");
Event.DBcmd_TP.AddColumn("notes");
Event.DBcmd_TP.AddColumn("last_modified_by");
Event.DBcmd_TP.AddColumn("last_modified_at");
Event.DBcmd_TP.AddColumn("is_active");
Event.DBcmd_TP.AddColumn("dividend");
Event.DBcmd_TP.AddColumn("is_exception");
Event.DBcmd_TP.AddColumn("esp_model_id");
return(Event.DBcmd_TP);
}
public static CmdTemplate Get_cmdTP()
{
if (BD_Dividend.DBcmd_TP != null)
{
return(BD_Dividend.DBcmd_TP);
}
BD_Dividend.DBcmd_TP = new CmdTemplate();
BD_Dividend.DBcmd_TP.tableName = "Dividend_Control_Approved";
BD_Dividend.DBcmd_TP.AddColumn("DividendIndex");
BD_Dividend.DBcmd_TP.AddColumn("Issue");
BD_Dividend.DBcmd_TP.AddColumn("Country");
BD_Dividend.DBcmd_TP.AddColumn("CUSIP");
BD_Dividend.DBcmd_TP.AddColumn("DTCPosition_ModelNumber");
BD_Dividend.DBcmd_TP.AddColumn("RecordDate_ADR");
BD_Dividend.DBcmd_TP.AddColumn("PayDate_ADR");
BD_Dividend.DBcmd_TP.AddColumn("PayDate_ORD");
BD_Dividend.DBcmd_TP.AddColumn("FinalDivGrossAmount_ORD");
BD_Dividend.DBcmd_TP.AddColumn("FinalDivGrossAmount_ADR");
BD_Dividend.DBcmd_TP.AddColumn("LongFormFee");
BD_Dividend.DBcmd_TP.AddColumn("StatutoryRate");
return(BD_Dividend.DBcmd_TP);
}
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);
}
public static CmdTemplate Get_cmdTP()
{
if (BD_DTC_Position.DBcmd_TP != null)
{
return(BD_DTC_Position.DBcmd_TP);
}
BD_DTC_Position.DBcmd_TP = new CmdTemplate();
BD_DTC_Position.DBcmd_TP.tableName = "Dividend_DTC_Position";
BD_DTC_Position.DBcmd_TP.AddColumn("DTC_PositionID");
BD_DTC_Position.DBcmd_TP.AddColumn("DTC_Number");
BD_DTC_Position.DBcmd_TP.AddColumn("DividendIndex");
BD_DTC_Position.DBcmd_TP.AddColumn("Total_RecDate_Position");
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Position_1"); /*Optional 6*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Position_2"); /*Optional 7*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Position_3"); /*Optional 8*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Position_4"); /*Optional 9*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Position_5"); /*Optional 10*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Position_6"); /*Optional 11*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Position_7"); /*Optional 12*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Position_8"); /*Optional 13*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Position_9"); /*Optional 14*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Position_10"); /*Optional 15*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Position_11"); /*Optional 16*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Position_12"); /*Optional 17*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Position_13"); /*Optional 18*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Position_14"); /*Optional 19*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Position_15"); /*Optional 20*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Position_16"); /*Optional 21*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Position_17"); /*Optional 22*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Position_18"); /*Optional 23*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Chargeback_1"); /*Optional 24*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Chargeback_2"); /*Optional 25*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Chargeback_3"); /*Optional 26*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Chargeback_4"); /*Optional 27*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Chargeback_5"); /*Optional 28*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Chargeback_6"); /*Optional 29*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Chargeback_7"); /*Optional 30*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Chargeback_8"); /*Optional 31*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Chargeback_9"); /*Optional 32*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Chargeback_10"); /*Optional 33*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Chargeback_11"); /*Optional 34*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Chargeback_12"); /*Optional 35*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Chargeback_13"); /*Optional 36*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Chargeback_14"); /*Optional 37*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Chargeback_15"); /*Optional 38*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Chargeback_16"); /*Optional 39*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Chargeback_17"); /*Optional 40*/
BD_DTC_Position.DBcmd_TP.AddColumn("Rate_Chargeback_18"); /*Optional 41*/
BD_DTC_Position.DBcmd_TP.AddColumn("Exempt1"); /*Optional 42*/
BD_DTC_Position.DBcmd_TP.AddColumn("Exempt2"); /*Optional 43*/
BD_DTC_Position.DBcmd_TP.AddColumn("Exempt3"); /*Optional 44*/
BD_DTC_Position.DBcmd_TP.AddColumn("Exempt4"); /*Optional 45*/
BD_DTC_Position.DBcmd_TP.AddColumn("Exempt5"); /*Optional 46*/
return(BD_DTC_Position.DBcmd_TP);
}
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 static void Init_cmdTP()
{
if (Participant.DBcmd_TP != null)
{
return;
}
Participant.DBcmd_TP = new CmdTemplate();
Participant.DBcmd_TP.tableName = "DTC_Participants";
Participant.DBcmd_TP.AddColumn("DTC");
Participant.DBcmd_TP.AddColumn("DTC_Number");
Participant.DBcmd_TP.AddColumn("Company_Name");
Participant.DBcmd_TP.AddColumn("Type");
}
public static CmdTemplate Get_cmdTP()
{
if (BD_detailSum.DBcmd_TP != null)
{
return(BD_detailSum.DBcmd_TP);
}
BD_detailSum.DBcmd_TP = new CmdTemplate();
BD_detailSum.DBcmd_TP.tableName = "Dividend_Detail";
BD_detailSum.DBcmd_TP.AddColumn("DTC_Number");
BD_detailSum.DBcmd_TP.AddColumn("DividendIndex");
BD_detailSum.DBcmd_TP.AddColumn("RecordDatePosition", AggregateFunction.Sum, "ClaimShares_ADRS_SUM");
return(BD_detailSum.DBcmd_TP);
}
public static void Init_cmdTP()
{
if (DividendElectionOption.DBcmd_TP != null)
{
return;
}
DividendElectionOption.DBcmd_TP = new CmdTemplate();
DBcmd_TP.tableName = "Dividend_Election_Option";
DividendElectionOption.DBcmd_TP.AddColumn("Dividend_OptionID");
DividendElectionOption.DBcmd_TP.AddColumn("DividendIndex");
DividendElectionOption.DBcmd_TP.AddColumn("OptionNumber");
DividendElectionOption.DBcmd_TP.AddColumn("Sender");
DividendElectionOption.DBcmd_TP.AddColumn("OptionActionType");
}
public static CmdTemplate Get_cmdTP()
{
if (ESPsys.DBcmd_TP != null)
{
return(ESPsys.DBcmd_TP);
}
ESPsys.DBcmd_TP = new CmdTemplate();
ESPsys.DBcmd_TP.tableName = "ESPSystem";
ESPsys.DBcmd_TP.AddColumn("esp_system_id");
ESPsys.DBcmd_TP.AddColumn("coi_code");
ESPsys.DBcmd_TP.AddColumn("clearing_system_id");
ESPsys.DBcmd_TP.AddColumn("security_type_id");
ESPsys.DBcmd_TP.AddColumn("event_restriction_id");
return(ESPsys.DBcmd_TP);
}
public static CmdTemplate Get_cmdTP()
{
if (Security.DBcmd_TP != null)
{
return(Security.DBcmd_TP);
}
Security.DBcmd_TP = new CmdTemplate();
Security.DBcmd_TP.tableName = "Security";
Security.DBcmd_TP.AddColumn("security_id");
Security.DBcmd_TP.AddColumn("security_name");
Security.DBcmd_TP.AddColumn("esp_system_id");
Security.DBcmd_TP.AddColumn("cusip");
Security.DBcmd_TP.AddColumn("isin");
Security.DBcmd_TP.AddColumn("sedol");
return(Security.DBcmd_TP);
}
public static CmdTemplate Get_cmdTP()
{
if (ColumnSchema.DBcmd_TP != null)
{
return(ColumnSchema.DBcmd_TP);
}
ColumnSchema.DBcmd_TP = new CmdTemplate();
ColumnSchema.DBcmd_TP.schema = "INFORMATION_SCHEMA";
ColumnSchema.DBcmd_TP.tableName = "COLUMNS";
ColumnSchema.DBcmd_TP.AddColumn("TABLE_SCHEMA");
ColumnSchema.DBcmd_TP.AddColumn("TABLE_NAME");
ColumnSchema.DBcmd_TP.AddColumn("COLUMN_NAME");
ColumnSchema.DBcmd_TP.AddColumn("ORDINAL_POSITION");
ColumnSchema.DBcmd_TP.AddColumn("IS_NULLABLE");
ColumnSchema.DBcmd_TP.AddColumn("DATA_TYPE");
return(ColumnSchema.DBcmd_TP);
}
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 static Dividend GetDividend_XBRL_refNum(string XBRL_ReferenceNumber, DividendTable_option table = DividendTable_option.Dividend_Control_Approved)
{
if (string.IsNullOrEmpty(XBRL_ReferenceNumber))
{
return(null);
}
CmdTemplate tp = new CmdTemplate();
tp.tableName = "Dividend_XBRL";
tp.AddColumn("DividendIndex");
DB_select selt = new DB_select(tp);
SQL_relation rela = new SQL_relation("XBRL_ReferenceNumber", RelationalOperator.Equals, XBRL_ReferenceNumber);
selt.SetCondition(rela);
DB_reader reader = new DB_reader(selt, Utility.Get_DRWIN_hDB());
int dividendIndex = -1;
if (reader.Read())
{
dividendIndex = reader.GetInt("DividendIndex");
}
reader.Close();
if (dividendIndex < 0)
{
return(null);
}
Dividend dvd = new Dividend(dividendIndex);
dvd.Init_from_DB(table);
return(dvd);
}
public static int UpdateDetail_singleValue(int detailID, string colName, DateTime dt)
{
if (string.IsNullOrEmpty(colName))
{
return(-5);
}
if (dt < Utility.MinDate)
{
return(-6);
}
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_update upd = new DB_update(cmdTp);
upd.AddValue(colName, dt);
SQL_relation rela = new SQL_relation(DividendDetail_master.Detail_PK_colName, RelationalOperator.Equals, detailID);
upd.SetCondition(rela);
if (colName.Contains(" ") || colName.Contains("-"))
{
Helper_SQLserver.SetTriger(DividendDetail_master.Detail_tableName, "TR_Dividend_Detail_Audit_History", false, Utility.Get_DRWIN_hDB());
int count = upd.SaveToDB(Utility.Get_DRWIN_hDB());
Helper_SQLserver.SetTriger(DividendDetail_master.Detail_tableName, "TR_Dividend_Detail_Audit_History", true, Utility.Get_DRWIN_hDB());
return(count);
}
else
{
int count = upd.SaveToDB(Utility.Get_DRWIN_hDB());
return(count);
}
}
public static CmdTemplate Get_cmdTP()
{
if (DepositaryInfo.DBcmd_TP != null)
{
return(DepositaryInfo.DBcmd_TP);
}
DepositaryInfo.DBcmd_TP = new CmdTemplate();
DepositaryInfo.DBcmd_TP.tableName = "DepositaryInfo";
DepositaryInfo.DBcmd_TP.AddColumn("depositary_info_id");
DepositaryInfo.DBcmd_TP.AddColumn("depositary_name");
DepositaryInfo.DBcmd_TP.AddColumn("depositary_address1");
DepositaryInfo.DBcmd_TP.AddColumn("depositary_address2");
DepositaryInfo.DBcmd_TP.AddColumn("phone");
DepositaryInfo.DBcmd_TP.AddColumn("fax");
DepositaryInfo.DBcmd_TP.AddColumn("international_phone");
DepositaryInfo.DBcmd_TP.AddColumn("depositary_full_name");
DepositaryInfo.DBcmd_TP.AddColumn("depositary_index");
DepositaryInfo.DBcmd_TP.AddColumn("active");
DepositaryInfo.DBcmd_TP.AddColumn("stamp_name");
return(DepositaryInfo.DBcmd_TP);
}
public static void Init_cmdTP()
{
if (Position.DBcmd_TP != null)
{
return;
}
Position.DBcmd_TP = new CmdTemplate();
Position.DBcmd_TP.tableName = "Dividend_DTC_Position";
Position.DBcmd_TP.AddColumn("DividendIndex");
Position.DBcmd_TP.AddColumn("DTC_Number");
Position.DBcmd_TP.AddColumn("Company_Name");
Position.DBcmd_TP.AddColumn("Total_RecDate_Position");
Position.DBcmd_TP.AddColumn("CustodianID");
Position.DBcmd_TP.AddColumn("ModifiedDateTime");
Position.DBcmd_TP.AddColumn("LastModifiedBy");
Position.DBcmd_TP.AddColumn("Rate_Position_1");
Position.DBcmd_TP.AddColumn("Rate_Position_2");
Position.DBcmd_TP.AddColumn("Rate_Position_3");
Position.DBcmd_TP.AddColumn("Rate_Position_4");
Position.DBcmd_TP.AddColumn("Rate_Position_5");
Position.DBcmd_TP.AddColumn("Rate_Position_6");
Position.DBcmd_TP.AddColumn("Rate_Position_7");
Position.DBcmd_TP.AddColumn("Rate_Position_8");
Position.DBcmd_TP.AddColumn("Rate_Position_9");
Position.DBcmd_TP.AddColumn("Rate_Position_10");
Position.DBcmd_TP.AddColumn("Rate_Position_11");
Position.DBcmd_TP.AddColumn("Rate_Position_12");
Position.DBcmd_TP.AddColumn("Rate_Position_13");
Position.DBcmd_TP.AddColumn("Rate_Position_14");
Position.DBcmd_TP.AddColumn("Rate_Position_15");
Position.DBcmd_TP.AddColumn("Rate_Position_16");
Position.DBcmd_TP.AddColumn("Rate_Position_17");
Position.DBcmd_TP.AddColumn("Rate_Position_18");
Position.DBcmd_TP.AddColumn("Rate_Chargeback_1");
Position.DBcmd_TP.AddColumn("Rate_Chargeback_2");
Position.DBcmd_TP.AddColumn("Rate_Chargeback_3");
Position.DBcmd_TP.AddColumn("Rate_Chargeback_4");
Position.DBcmd_TP.AddColumn("Rate_Chargeback_5");
Position.DBcmd_TP.AddColumn("Rate_Chargeback_6");
Position.DBcmd_TP.AddColumn("Rate_Chargeback_7");
Position.DBcmd_TP.AddColumn("Rate_Chargeback_8");
Position.DBcmd_TP.AddColumn("Rate_Chargeback_9");
Position.DBcmd_TP.AddColumn("Rate_Chargeback_10");
Position.DBcmd_TP.AddColumn("Rate_Chargeback_11");
Position.DBcmd_TP.AddColumn("Rate_Chargeback_12");
Position.DBcmd_TP.AddColumn("Rate_Chargeback_13");
Position.DBcmd_TP.AddColumn("Rate_Chargeback_14");
Position.DBcmd_TP.AddColumn("Rate_Chargeback_15");
Position.DBcmd_TP.AddColumn("Rate_Chargeback_16");
Position.DBcmd_TP.AddColumn("Rate_Chargeback_17");
Position.DBcmd_TP.AddColumn("Rate_Chargeback_18");
Position.DBcmd_TP.AddColumn("Exempt1");
Position.DBcmd_TP.AddColumn("Exempt2");
Position.DBcmd_TP.AddColumn("Exempt3");
Position.DBcmd_TP.AddColumn("Exempt4");
Position.DBcmd_TP.AddColumn("Exempt5");
}
