public void Run_TaskB()
{
HashSet <int> fxID_hs = this.Get_currFX_ID_set("B");
Bulk_DBcmd buk_ins = new Bulk_DBcmd();
foreach (FX_Input fi in this.fi_dic.Values)
{
if (fi.FX_ReceivedDate.Value < this.startDate)
{
continue; //set a cut-off date
}
if (fi.Depositary_ReceivedDate.Value > fi.FX_NotificationDate.Value)
{
continue;
}
if ((this.today_date - fi.FX_NotificationDate.Value).TotalDays <= 1)
{
continue;
}
if (fxID_hs.Contains(fi.FX_InputID))
{
continue;
}
Task_Detail_20B t20b = new Task_Detail_20B();
t20b.SourceID.Value = fi.FX_InputID.ToString();
t20b.Country.Value = fi.CountryName.Value;
t20b.Notes.Value = "Today after notification date";
buk_ins.Add_DBcmd(t20b.Get_DBinsert());
}
buk_ins.SaveToDB(Utility.Get_DRWIN_hDB());
}
public void Run_TaskA()
{
HashSet <int> fxID_hs = this.Get_currFX_ID_set("A");//current IDs in task_detail table
Bulk_DBcmd buk_ins = new Bulk_DBcmd();
foreach (FX_Input fi in this.fi_dic.Values)
{
if (fi.Locked || fi.FX_ReceivedDate.Value < this.startDate)
{
continue;
}
if (fxID_hs.Contains(fi.FX_InputID))
{
continue; //skip existing tasks
}
int overDue_days = (this.today_date - fi.FX_ReceivedDate.Value).Days;
if (overDue_days <= Task_Detail_20A.Overdue_days)
{
continue;
}
Task_Detail_20A t20a = new Task_Detail_20A();
t20a.SourceID.Value = fi.FX_InputID.ToString();
t20a.Country.Value = fi.CountryName.Value;
t20a.Notes.Value = "Over:" + overDue_days + " days";
buk_ins.Add_DBcmd(t20a.Get_DBinsert());
}
buk_ins.SaveToDB(Utility.Get_DRWIN_hDB());
}
public void Run_TaskC()
{
HashSet <int> fxID_hs = this.Get_currFX_ID_set("C");
Bulk_DBcmd buk_ins = new Bulk_DBcmd();
foreach (FX_Input fi in this.fi_dic.Values)
{
if (fi.FX_Rate.Value >= 0 || fi.Depositary_ReceivedDate.Value == Utility.MinDate)
{
continue;
}
if (fxID_hs.Contains(fi.FX_InputID))
{
continue;
}
int overDue_days = (this.today_date - fi.Depositary_ReceivedDate.Value).Days;
if (overDue_days <= Task_Detail_20C.Overdue_days)
{
continue;
}
Task_Detail_20C t20c = new Task_Detail_20C();
t20c.SourceID.Value = fi.FX_InputID.ToString();
t20c.Notes.Value = "OverDue: " + overDue_days + " days";
t20c.Country.Value = fi.CountryName.Value;
buk_ins.Add_DBcmd(t20c.Get_DBinsert());
}
buk_ins.SaveToDB(Utility.Get_DRWIN_hDB());
}
private void JPM_refNum_button_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
string path = ofd.FileName;
DataSet ds = HssExcel.Excel_to_DS(path);
DataTable dt = ds.Tables["JPM"];
Bulk_DBcmd bk = new Bulk_DBcmd();
foreach (DataRow row in dt.Rows)
{
string JPM_refNum = row["JPM CUSTODIAN REF NUMBER"].ToString();
if (string.IsNullOrEmpty(JPM_refNum))
{
continue;
}
string shortName = row["Short\nName"].ToString();
Custodian cust = CustodianMaster.GetCustodian_name(shortName);
if (cust == null)
{
continue;
}
cust.JPM_Custodian_Ref_Num.Value = JPM_refNum;
bk.Add_DBcmd(cust.Get_DBupdate());
}
MessageBox.Show("" + bk.SaveToDB(Utility.Get_DRWIN_hDB()));
CustodianMaster.Reset();
}
}
public static int Insert_DTC_Participants(List <DTC_Participants> dp_list)
{
if (dp_list == null)
{
return(-1);
}
Bulk_DBcmd bk_cmd = new Bulk_DBcmd();
foreach (DTC_Participants dp in dp_list)
{
if (dp == null)
{
continue;
}
bk_cmd.Add_DBcmd(dp.Get_DBinsert());
}
int count = bk_cmd.SaveToDB(Utility.Get_DRWIN_hDB());
if (count > 0)
{
DTC_Participants_master.Reset();
}
return(count);
}
public static int SetTaskCompleteness(int taskID, string sourceID, string taskName, bool completed)
{
List <Task_Detail> list = TaskDetailMaster.GetTaskDetail_list(taskID, sourceID, taskName);
Bulk_DBcmd bk_cmd = new Bulk_DBcmd();
foreach (Task_Detail td in list)
{
td.Completed.Value = true;
bk_cmd.Add_DBcmd(td.Get_DBupdate());
}
int count = bk_cmd.SaveToDB(Utility.Get_DRWIN_hDB());
return(count);
}
public void Run_taskD()
{
HashSet <int> fxID_hs = this.Get_currFX_ID_set("D");
Bulk_DBcmd buk_ins = new Bulk_DBcmd();
foreach (FX_Input fi in this.fi_dic.Values)
{
if (fi.FX_ReceivedDate.Value < this.startDate)
{
continue;
}
if (fxID_hs.Contains(fi.FX_InputID))
{
continue;
}
Task_Detail_20D t20d = null;
if (fi.PayToBrokerDate.Value == Utility.MinDate && fi.ActualCheckRequestDate.Value > Utility.MinDate)
{
int overDue_days = (this.today_date - fi.ActualCheckRequestDate.Value).Days;
if (overDue_days > Task_Detail_20D.Overdue_days)
{
t20d = new Task_Detail_20D();
t20d.SourceID.Value = fi.FX_InputID.ToString();
t20d.Notes.Value = "Not to Broker, over: " + overDue_days + " days";
}
}
else if (fi.PayToBrokerDate.Value < fi.ActualCheckRequestDate.Value)
{
t20d = new Task_Detail_20D();
t20d.SourceID.Value = fi.FX_InputID.ToString();
t20d.Notes.Value = "broker date can't before actual";
}
if (t20d != null)
{
t20d.Country.Value = fi.CountryName.Value;
buk_ins.Add_DBcmd(t20d.Get_DBinsert());
}
}
buk_ins.SaveToDB(Utility.Get_DRWIN_hDB());
}
/// <summary>
/// Duplicate details for Upfront Fees (DRWIN-38)
/// </summary>
public static int Add_UfFess_claim(HashSet <int> detailID_hs)
{
List <Dividend_Detail_full> ddf_list = DividendDetail_master.Get_ddf_list(detailID_hs);
Bulk_DBcmd bkc = new Bulk_DBcmd();
foreach (Dividend_Detail_full ddf in ddf_list)
{
if (!ddf.Status.Value.StartsWith("Received", StringComparison.OrdinalIgnoreCase))
{
continue;
}
ddf.LastModifiedBy.Value = Utility.CurrentUser;
ddf.ModifiedDateTime.Value = DateTime.Now;
long new_ClaimID = DividendDetail_master.GetNewClaimID(ddf.ClaimID.Value, 6);
long old_ClaimID = ddf.ClaimID.Value;
string old_BO_EntityType = ddf.BO_EntityType.Value;
ddf.ClaimID.Value = new_ClaimID;
ddf.BO_EntityType.Value = "Pen1";
bkc.Add_DBcmd(ddf.Get_DBinsert());
//update old [Dividend_Detail]
ddf.ClaimID.Value = old_ClaimID;
ddf.BO_EntityType.Value = old_BO_EntityType;
ddf.Override_Fees.Value = 0; //set depositary_fee to 0
ddf.Override_Custodial_Fees.Value = 0; //set custodian_fee to 0
bkc.Add_DBcmd(ddf.Get_DBupdate());
}
int count = bkc.SaveToDB(Utility.Get_DRWIN_hDB());
if (count > 0)
{
MessageBox.Show("Add UpFees Finished " + count);
}
else
{
MessageBox.Show("UpFees: Nothing was added...");
}
return(count);
}
/// <summary>
/// Duplicate details for Return of Founds (DRWIN-38)
/// </summary>
public static int Add_RoF_claims(HashSet <int> detailID_hs)
{
List <Dividend_Detail_full> ddf_list = DividendDetail_master.Get_ddf_list(detailID_hs);
Bulk_DBcmd bkc = new Bulk_DBcmd();
foreach (Dividend_Detail_full ddf in ddf_list)
{
if (!ddf.Status.Value.StartsWith("Paid", StringComparison.OrdinalIgnoreCase))
{
continue;
}
long newClaimID = DividendDetail_master.GetNewClaimID(ddf.ClaimID.Value, 5);
ddf.ClaimID.Value = newClaimID;
ddf.Override_Custodial_Fees.Value = 0;
ddf.Override_Fees.Value = 0;
ddf.RecordDatePosition.Value = -ddf.RecordDatePosition.Value;
ddf.Status.Value = "RECEIVED";
ddf.Dividend_FilingID.IsNull_flag = true;
ddf.Dividend_PayID.IsNull_flag = true;
ddf.AuditStatus.Value = "Return of Funds";
ddf.LastModifiedBy.Value = Utility.CurrentUser;
ddf.ModifiedDateTime.Value = DateTime.Now;
bkc.Add_DBcmd(ddf.Get_DBinsert());
}
int count = bkc.SaveToDB(Utility.Get_DRWIN_hDB());
if (count > 0)
{
MessageBox.Show("Add RoF Finished " + count);
}
else
{
MessageBox.Show("RoF: Nothing was added...");
}
return(count);
}
/// <summary>
/// Insert DTC Position using dataSet read from DES document
/// </summary>
public void Insert_DTC_position(DataSet ds, bool createNew_flag = true)
{
if (ds == null || ds.Tables.Count < 1)
{
return;
}
if (createNew_flag)
{
this.Delete_DTC_position();
}
DataTable dt = ds.Tables[0];
HashSet <string> colName_hs = new HashSet <string>();
foreach (DataColumn col in dt.Columns)
{
colName_hs.Add(col.ColumnName);
}
DTCPositionModelNumber_Mapping posMod = this.Get_DTCpos_model();
if (posMod == null)
{
return;
}
Dictionary <int, List <string> > mapping = posMod.Get_colHeader_mapping(colName_hs);
Bulk_DBcmd buk_ins = new Bulk_DBcmd();
foreach (DataRow row in dt.Rows)
{
Position dtcPos = new Position();
dtcPos.DividendIndex = this.DividendIndex;
dtcPos.DTC_Number = row["DTC Participant No."].ToString();
if (string.IsNullOrEmpty(dtcPos.DTC_Number))
{
continue;
}
dtcPos.Company_Name = row["DTC Participant Name"].ToString();
decimal tempDecimal = -1;
if (decimal.TryParse(row["R/D Position"].ToString(), out tempDecimal))
{
dtcPos.Total_RecDate_Position = tempDecimal;
}
foreach (KeyValuePair <int, List <string> > pair in mapping)
{
List <string> lst = pair.Value;
if (lst.Count > 0)
{
dtcPos.RatePositions.Set_rateValue(pair.Key, row[lst[0]].ToString());
}
if (lst.Count > 1)
{
dtcPos.Rate_Chargebacks.Set_rateValue(pair.Key, row[lst[1]].ToString());
}
}
buk_ins.Add_DBcmd(dtcPos.Get_DBinsert());
}
int count = buk_ins.SaveToDB(Utility.Get_DRWIN_hDB());
}
/// <summary>
/// Insert DTC Position using SPR file from DTCC
/// </summary>
/// <param name="sf">SPR file</param>
/// <param name="createNew_flag">append or create new</param>
public bool Insert_DTC_position(SPR_file sf, bool createNew_flag = true)
{
if (sf == null || sf.FileBinary == null)
{
MessageBox.Show("Dividend_func4 error 0: No SPR data");
return(false);
}
MemoryStream ms = new MemoryStream(sf.FileBinary);
StreamReader sr = new StreamReader(ms);
if (createNew_flag)
{
this.Delete_DTC_position();
}
List <Position> posList = new List <Position>();
string str = null;
while ((str = sr.ReadLine()) != null)
{
if (str.Length <= 37)
{
continue;
}
char recordType_char = str[SPR_fileControl.RecordTypeChar_index];
if (recordType_char != '2')
{
continue; //'2' stands for detail records
}
if (str.StartsWith("HDR", StringComparison.OrdinalIgnoreCase))
{
continue;
}
if (str.StartsWith("TRL", StringComparison.OrdinalIgnoreCase))
{
break;
}
Position dtcPos = new Position();
dtcPos.DividendIndex = this.DividendIndex;
dtcPos.DTC_Number = str.Substring(19, 8).TrimStart('0');
dtcPos.Company_Name = str.Substring(27, 10).Trim();
string totalPos_str = str.Substring(37).Trim();
decimal tempDecimal = -1;
if (decimal.TryParse(totalPos_str, out tempDecimal))
{
dtcPos.Total_RecDate_Position = tempDecimal;
}
posList.Add(dtcPos);
}
ParticipantMaster pcMaster = new ParticipantMaster();
pcMaster.Init_from_dtcPosList(posList);
Bulk_DBcmd buk_ins = new Bulk_DBcmd();
List <DTC_Participants> missing_DCTpart_list = new List <DTC_Participants>();
foreach (Position pos in posList)
{
Participant pt = pcMaster.GetParticipant(pos.DTC_Number);
if (pt == null)
{
DTC_Participants dtcPart = new DTC_Participants();
int tempInt = -1;
if (!int.TryParse(pos.DTC_Number, out tempInt))
{
continue;
}
dtcPart.DTC = tempInt;
dtcPart.DTC_Number.Value = pos.DTC_Number;
dtcPart.Company_Name.Value = pos.Company_Name;
missing_DCTpart_list.Add(dtcPart);
}
else
{
pos.Company_Name = pt.Company_Name; //if exist, change the name to what we have in [DTC_Participants]
}
buk_ins.Add_DBcmd(pos.Get_DBinsert());
}
int count0 = buk_ins.SaveToDB(Utility.Get_DRWIN_hDB());
int count1 = DTC_Participants_master.Insert_DTC_Participants(missing_DCTpart_list);
return(true);
}
private static void ApproveXBRL3_AddFees(Dividend dvd)
{
if (dvd == null)
{
return;
}
string depo_str = dvd.Sponsored.Value ? dvd.Depositary.Value : dvd.FirstFiler.Value;
Depositary depo = DepositaryMaster.GetDepositary_by_name(depo_str);
if (depo == null)
{
return;
}
Schedule_Of_Fees_DSC sfd = DSC_master.GetDSC_from_CDS(dvd.Country.Value, depo.DepositaryName.Value, dvd.RecordDate_ADR.Value, dvd.SecurityTypeID.Value);
if (sfd == null)
{
return;
}
Dictionary <decimal, Schedule_Of_Fees_Per_Rate> Japan_atSourceFee_dic = DSC_master.Get_SFPR_dic(dvd.Country.Value, depo.DepositaryName.Value, dvd.RecordDate_ADR.Value);
Bulk_DBcmd bk_cmd = new Bulk_DBcmd();
List <DTC_Position_Headers> header_list = DTCpositionHeader_master.Get_headerList_modNum(dvd.DTCPosition_ModelNumber.Value);
HashSet <decimal> unique_WHrate_hs = new HashSet <decimal>();
foreach (DTC_Position_Headers dph in header_list)
{
unique_WHrate_hs.Add(dph.WHRate.Value);
}
foreach (decimal WHrate in unique_WHrate_hs)
{
Dividend_Schedule_Of_Fees_DSC dsfd = new Dividend_Schedule_Of_Fees_DSC();
dsfd.DividendIndex.Value = dvd.DividendIndex;
dsfd.WHRate.Value = WHrate / 100;
dsfd.LongFormFees.Value = sfd.LongFormFees.Value;
dsfd.MinLongFormFee.Value = sfd.MinLongFormFee.Value;
dsfd.Fee_Consularization.Value = dvd.ConsularizationFee.Value;
dsfd.Fee_QuickRefund.Value = sfd.ShortFormFees.Value;
/* //useless for now
* dsfd.Fee_AtSource.Value = dvd.AtSourceFee.Value;
* dsfd.Fee_Max.Value = dvd.MAX_Fees.Value;
* dsfd.ExemptAtSourceFee.Value = sfd.ExemptAtSourceFee.Value;//combined into FavAtSourceFee
* dsfd.ShortFormFees.Value = sfd.ShortFormFees.Value;
* dsfd.MinShortFormFee.Value = sfd.MinShortFormFee.Value;
*/
dsfd.MinAtSourceFee.Value = sfd.MinAtSourceFee.Value;
dsfd.MinQuickRefundFee.Value = sfd.MinQuickRefundFee.Value;
dsfd.FavTransparentEntityFee.Value = sfd.FavTransparentEntityFee.Value;
if (Japan_atSourceFee_dic.ContainsKey(WHrate))
{
Schedule_Of_Fees_Per_Rate sfpr = Japan_atSourceFee_dic[WHrate];
dsfd.FavAtSourceFee.Value = sfpr.RateWithADSC.Value;
dsfd.AdditionalDSCFee.Value = sfpr.ADSC.Value;
dsfd.AdditionalDSCFee1.Value = sfpr.ADSC.Value;
}
else
{
if (WHrate == 0)
{
dsfd.FavAtSourceFee.Value = sfd.ExemptAtSourceFee.Value;
}
else
{
dsfd.FavAtSourceFee.Value = sfd.FavAtSourceFee.Value;
}
dsfd.AdditionalDSCFee.Value = sfd.AdditionalDSCFee.Value;
dsfd.AdditionalDSCFee1.Value = sfd.AdditionalDSCFee.Value;
}
dsfd.Source_FeeID.Value = sfd.FeesID;
dsfd.LastModifiedBy.Value = "StevenXBRL-" + Utility.CurrentUser;
dsfd.ModifiedDateTime.Value = DateTime.Now;
bk_cmd.Add_DBcmd(dsfd.Get_DBinsert());
}
int count = bk_cmd.SaveToDB(Utility.Get_DRWIN_hDB());
}
private void custMapping_button_Click(object sender, EventArgs e)
{//DRWIN-467
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
string path = ofd.FileName;
DataSet ds = HssExcel.Excel_to_DS(path);
DataTable dt = ds.Tables["Export Worksheet"];
Bulk_DBcmd bk = new Bulk_DBcmd();
foreach (DataRow row in dt.Rows)
{
int cNum = -1;
if (int.TryParse(row["Custodian #"].ToString(), out cNum))
{
Custodian cust = CustodianMaster.GetCustodian_num(cNum);
if (cust == null)
{
Console.WriteLine("cNum " + cNum + " not found");
continue;
}
string str = row["Custodian"].ToString();
if (string.IsNullOrEmpty(cust.Custodian_Alias.Value))
{
cust.Custodian_Alias.Value = row["Custodian"].ToString();
}
else if (cust.Custodian_Alias.Value.ToUpper().Contains(str.ToUpper()))
{
continue;
}
else
{
cust.Custodian_Alias.Value = cust.Custodian_Alias.Value + "," + row["Custodian"].ToString();
}
bk.Add_DBcmd(cust.Get_DBupdate());
}
else
{
string custName = row["Custodian"].ToString();
if (string.IsNullOrEmpty(custName))
{
continue;
}
string ctyName = row["Country"].ToString();
Country cty = CountryMaster.GetCountry_name(ctyName);
Custodian cust = CustodianMaster.GetCustodian_name(custName);
if (cust == null)
{
cust = new Custodian();
}
cust.Custodian_ShortName.Value = custName;
cust.Custodian_FullName.Value = custName;
if (cty != null)
{
cust.Country_Code.Value = cty.cntry_cd.Value;
}
else
{
cust.Country_Code.Value = "000";
}
if (cust.Custodian_Number > 0)
{
bk.Add_DBcmd(cust.Get_DBupdate());
}
else
{
bk.Add_DBcmd(cust.Get_DBinsert());
}
}
}
MessageBox.Show("" + bk.SaveToDB(Utility.Get_DRWIN_hDB()));
CustodianMaster.Reset();
}
}