public int Revise(result FOSresult, string FinalRating, int TradeID, bool RerunMacro, out htmlresult htmlResult)
{
htmlResult = new htmlresult();
//List<int> listOld = new List<int>();
//List<int> listNew = new List<int>();
//foreach (Process process in Process.GetProcessesByName("Excel"))
//{
// listOld.Add(process.Id);
//}
System.DateTime lastmodifytime;
string filename = GetFileName(parentpath, out lastmodifytime);
if (impersonateValidUser(username, Domain, password))
//if(1==1)
{
int i;
Range r;
int j;
Range rr;
int paramstartindex = 0;
int paramendindex = 0;
//string[] IssueRatings = IssueRating.Split(new char[] { '/' });
//string SPRating = IssueRatings[0];
//string MoodyRating = IssueRatings[1];
//string FitchRating = IssueRatings[2];
//string RTG1 = this.GetRTG1(SPRating, MoodyRating, FitchRating);
//string RTG2 = this.GetRTG2(SPRating, MoodyRating, FitchRating);
//Dictionary<string, string> insertresult = new Dictionary<string, string>();
//string filename = @"D:\ACR Portfolio - 120229.xlsm";
try
{
#region Revise
Application celapp = new Application();
object mss = Missing.Value;
Workbook celwbook = null;
FileInfo fi1 = new FileInfo(parentpath + @"\" + filename);
if (this.Application.AllKeys.Contains("ExcelInstance") && this.Application["ExcelInstance"] != null)
{
celapp = ((Workbook)(this.Application["ExcelInstance"])).Application;
celwbook = (Workbook)(this.Application["ExcelInstance"]);
}
else
{
FileInfo fireadonly = new FileInfo(parentpath + @"\~$" + filename);
if (fireadonly.Exists)
return 1;
fireadonly = new FileInfo(tempworkpath + @"\~$" + filename);
if (fireadonly.Exists)
return 2;
fi1.CopyTo(tempworkpath + @"\" + filename, true);
Workbook celwbook1 = celapp.Workbooks.Open(tempworkpath + @"\" + "Book1.xls", mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss);
//Worksheet celsh = celwbook.Sheets["Live"] as Worksheet;
Worksheet celsh1 = celwbook1.ActiveSheet as Worksheet;
//celsh.Select(Missing.Value);
celapp.Visible = true;
AddIn addin = celapp.AddIns.Add(@"C:\blp\API\Office Tools\BloombergUI.xla", Missing.Value);
addin.Installed = false;
addin.Installed = true;
celwbook = celapp.Workbooks.Open(tempworkpath + @"\" + filename, mss, mss, mss, mss, mss, mss, mss, mss, mss, false, mss, mss, mss, mss);
this.Application["ExcelInstance"] = celwbook;
}
//foreach (System.Diagnostics.Process process in System.Diagnostics.Process.GetProcessesByName("Excel"))
//{
// listNew.Add(process.Id);
//}
Worksheet celsh = celwbook.Sheets["Live"] as Worksheet;
celsh.Select(Missing.Value);
celapp.Visible = true;
string s1 = string.Empty;
Dictionary<int, string> columnname = new Dictionary<int, string>();
int rowindex = 0;
int tradeIDcolumnindex = 0;
for (i = 1; i < celsh.UsedRange.Rows.Count; i++)
{
r = (Range)celsh.Cells[i, 1];
s1 = r.Text.ToString();
if (s1.Trim() == "Mkt Value (US$)")
{
paramstartindex = i;
}
else if (s1.Trim() == "Ave Credit Quality")
{
paramendindex = i;
}
else if (s1.Trim() == "Acc #")
{
rowindex = i;
break;
}
}
for (i = 1; i <= celsh.UsedRange.Rows.Columns.Count; i++)
{
r = (Range)celsh.Cells[rowindex, i];
if (r.Text.ToString().Trim() == "TRADE ID")
{
tradeIDcolumnindex = i;
break;
}
}
for (i = rowindex; i <= celsh.UsedRange.Rows.Count; i++)
{
r = (Range)celsh.Cells[i, tradeIDcolumnindex];
if (r.Text.ToString().Trim() == TradeID.ToString())
{
((Range)(celsh.Cells[i, 4])).Formula = FOSresult.ISIN;
((Range)(celsh.Cells[i, 6])).Formula = FOSresult.SECURITY_DES;
((Range)(celsh.Cells[i, 7])).Formula = FOSresult.Quatity.Replace(",", "");
((Range)(celsh.Cells[i, 12])).Formula = FOSresult.CRNCY;
((Range)(celsh.Cells[i, 13])).Formula = FOSresult.PX_BID;
((Range)(celsh.Cells[i, 14])).Formula = FOSresult.PX_BID;
((Range)(celsh.Cells[i, 23])).Formula = FOSresult.YLD_YTM_BID;
}
}
#endregion
#region runmacro
if (RerunMacro)
{
string sectorissuer = ((Range)(celsh.Cells[rowindex + 1, 39])).Text.ToString();
//celwbook.Save();
bool flag = true;
while (flag)
{
flag = false;
i = paramstartindex;
while (i <= (paramendindex + 1))
{
j = 2;
while (j <= 29)
{
if (((Range)(celsh.Cells[i, j])).Text.ToString().IndexOf("#V") != -1)
{
flag = true;
break;
}
j++;
}
i++;
}
}
celwbook.RefreshAll();
this.RunMacro(celapp, new object[] { "RefreshSummary" });
StringBuilder html = new StringBuilder();
StringBuilder html2 = new StringBuilder();
StringBuilder html3 = new StringBuilder();
StringBuilder html4 = new StringBuilder();
celsh = celwbook.Sheets["IssuerExp"] as Worksheet;
for (int iii = 1; iii < celsh.UsedRange.Rows.Count; iii++)
{
r = (Range)celsh.Cells[iii, 1];
if (r.Text.ToString() == FOSresult.ISSUER)
{
htmlResult.issuer = FOSresult.ISSUER;
}
}
celsh = celwbook.Sheets["GUIDELINES CHECK"] as Worksheet;
//transfer data from datatable to html
int step1 = 0;
int step2 = 0;
int step3 = 0;
int row = 0;
int column = 0;
int BBBGuidlineCheck = 0;
//Range r;
//Range rr;
for (int iii = 1; iii < celsh.UsedRange.Rows.Count; iii++)
{
for (int jjj = 1; jjj < celsh.UsedRange.Columns.Count; jjj++)
{
r = (Range)celsh.Cells[iii, jjj];
switch ((string)(r.Text.ToString()))
{
case "LIMITS":
column = jjj;
step1 = iii;
break;
case "LIMITS BY SECURITY TYPES":
step2 = iii;
break;
case "LIMITS BY ISSUER":
step3 = iii;
break;
case "Issuer/Issue Rating":
BBBGuidlineCheck = iii;
break;
}
row++;
if (step3 > 0)
break;
}
}
if (RTG_FITCHlist.Contains(FinalRating))
celsh.Cells[BBBGuidlineCheck, column + 5] = "YES";
else
{
if (CRNCY == "IDR" || CRNCY == "INR" || CRNCY == "PHP")
celsh.Cells[BBBGuidlineCheck, column + 5] = "YES";
else
celsh.Cells[BBBGuidlineCheck, column + 5] = "NO";
}
html.Append("<tr>");
for (int kk = column; kk <= 8; kk++)
{
r = (Range)celsh.Cells[step1 + 1, kk];
html.Append("<th>" + r.Text.ToString() + "</th>");
}
for (int jj = step1 + 3; jj < celsh.UsedRange.Rows.Count; jj++)
{
r = (Range)celsh.Cells[jj, column];
if (r.Text.ToString() == "")
{
break;
}
else
{
html.Append("<tr>");
for (int k = column; k <= 8; k++)
{
rr = (Range)celsh.Cells[jj, k];
html.Append("<td>" + rr.Text.ToString() + "</td>");
}
html.Append("</tr>");
}
}
html2.Append("<tr>");
for (int kk = column; kk <= 8; kk++)
{
if (kk == 3 || kk == 5)
continue;
r = (Range)celsh.Cells[step2 + 1, kk];
html2.Append("<th>" + r.Text.ToString() + "</th>");
}
html2.Append("</tr>");
for (int jj = step2 + 2; jj < celsh.UsedRange.Rows.Count; jj++)
{
r = (Range)celsh.Cells[jj, column];
if (r.Text.ToString() == "")
break;
else
{
html2.Append("<tr>");
for (int k = column; k <= 8; k++)
{
if (k == 3 || k == 5)
continue;
rr = (Range)celsh.Cells[jj, k];
html2.Append("<td>" + rr.Text.ToString() + "</td>");
if (k == 2 || k == 4)
k++;
}
html2.Append("</tr>");
}
}
html3.Append("<tr>");
for (int kk = column; kk <= 8; kk++)
{
if (kk == 3 || kk == 5)
continue;
r = (Range)celsh.Cells[step3 + 1, kk];
html3.Append("<th>" + r.Text.ToString() + "</th>");
}
html3.Append("</tr>");
for (int jj = step3 + 2; jj < celsh.UsedRange.Rows.Count; jj++)
{
r = (Range)celsh.Cells[jj, column];
if (r.Text.ToString().Trim() == "Grand Total" || r.Text.ToString() == "")
break;
else
{
string stemp = r.Text.ToString();
if (stemp.Trim().ToUpper() == sectorissuer.Trim().ToUpper())
//if (stemp.Substring((temp.IndexOf('-') + 1), (stemp.ToString().Length - stemp.ToString().IndexOf('-') - 1)) == sectorissuer.ToUpper())
{
html3.Append("<tr>");
for (int k = column; k <= 8; k++)
{
if (k == 3 || k == 5)
continue;
rr = (Range)celsh.Cells[jj, k];
html3.Append("<td>" + rr.Text.ToString() + "</td>");
if (k == 2 || k == 4)
k++;
}
html3.Append("</tr>");
}
}
}
html4.Append("<tr>");
for (int kk = column; kk <= 8; kk++)
{
if (kk == 3 || kk == 5)
continue;
r = (Range)celsh.Cells[step3 + 1, kk];
html4.Append("<th>" + r.Text.ToString() + "</th>");
}
html4.Append("</tr>");
for (int jj = step3 + 2; jj < celsh.UsedRange.Rows.Count; jj++)
{
r = (Range)celsh.Cells[jj, column];
if (r.Text.ToString().Trim() == "Grand Total" || r.Text.ToString() == "")
break;
else
{
rr = (Range)celsh.Cells[jj, 4];
string stemp = rr.Text.ToString();
if (float.Parse(stemp.Substring(0, stemp.Length - 1)) > 3.5)
{
html4.Append("<tr>");
for (int k = column; k <= 8; k++)
{
if (k == 3 || k == 5)
continue;
rr = (Range)celsh.Cells[jj, k];
html4.Append("<td>" + rr.Text.ToString() + "</td>");
if (k == 2 || k == 4)
k++;
}
html4.Append("</tr>");
}
}
}
celwbook.Save();
fi1 = new FileInfo(tempworkpath + @"\" + filename);
fi1.CopyTo(parentpath + @"\" + filename, true);
undoImpersonation();
//impersonateValidUser("bbuser1", "WSG053", "bbUser01");
//if (celapp != null)
//{
// Process process = new Process();
// foreach (int pID in listNew.Except(listOld))
// {
// process = System.Diagnostics.Process.GetProcessById(pID);
// process.Kill();
// }
// //Process.GetProcessesByName("Excel")[0].Kill();
//}
//undoImpersonation();
StringBuilder finalhtml = new StringBuilder();
finalhtml.Append("<div><h2>SUMMARY OF GUIDELINES CHECK</h2><h4>LIMITS</h4><table border='1'>");
finalhtml.Append(html.ToString());
finalhtml.Append("</table><p>*Note: Exception for Guideline 5 applies only if Guideline 4 is met</p><h4>LIMITS BY SECURITY TYPES</h4><table border='1'>");
finalhtml.Append(html2.ToString());
finalhtml.Append("</table><h4>LIMITS BY ISSUER</h4><table border='1'>");
finalhtml.Append(html3.ToString());
finalhtml.Append("</table><h4>LIMITS BY ISSUER(>3.5%)</h4><table border='1'>");
finalhtml.Append(html4.ToString());
finalhtml.Append("</table></div>");
htmlResult.htmlfortable = finalhtml.ToString();
return 0;
}
#endregion
#region norunmacro
else
{
celwbook.Save();
fi1 = new FileInfo(tempworkpath + @"\" + filename);
fi1.CopyTo(parentpath + @"\" + filename, true);
undoImpersonation();
//impersonateValidUser("bbuser1", "WSG053", "bbUser01");
//if (celapp != null)
//{
// Process process = new Process();
// foreach (int pID in listNew.Except(listOld))
// {
// process = System.Diagnostics.Process.GetProcessById(pID);
// process.Kill();
// }
// //Process.GetProcessesByName("Excel")[0].Kill();
//}
//undoImpersonation();
return 0;
}
#endregion
}
catch (Exception e)
{
return -1;
}
finally
{
// impersonateValidUser("bbuser1", "WSG053", "bbUser01");
// if (celapp != null)
// {
// Process process = new Process();
// foreach (int pID in listNew.Except(listOld))
// {
// process = System.Diagnostics.Process.GetProcessById(pID);
// process.Kill();
// }
// //Process.GetProcessesByName("Excel")[0].Kill();
// }
// undoImpersonation();
}
}
else
{
return -1;
}
}
public int Insert(result FOSresult, string FinalRating, int TradeID, out htmlresult htmlResult)
{
htmlResult = new htmlresult();
List<int> listOld = new List<int>();
List<int> listNew = new List<int>();
foreach (Process process in Process.GetProcessesByName("Excel"))
{
listOld.Add(process.Id);
}
System.DateTime lastmodifytime;
string filename = GetFileName(parentpath, out lastmodifytime);
if (impersonateValidUser(username, Domain, password))
//if(1==1)
{
int i;
Range r;
int j;
Range rr;
int paramstartindex = 0;
int paramendindex = 0;
//string[] IssueRatings = IssueRating.Split(new char[] { '/' });
//string SPRating = IssueRatings[0];
//string MoodyRating = IssueRatings[1];
//string FitchRating = IssueRatings[2];
//string RTG1 = this.GetRTG1(SPRating, MoodyRating, FitchRating);
//string RTG2 = this.GetRTG2(SPRating, MoodyRating, FitchRating);
//Dictionary<string, string> insertresult = new Dictionary<string, string>();
//string filename = @"D:\ACR Portfolio - 120229.xlsm";
try
{
#region insert
Application celapp = new Application();
object mss = Missing.Value;
Workbook celwbook = null;
FileInfo fi1 = new FileInfo(parentpath + @"\" + filename);
if (this.Application.AllKeys.Contains("ExcelInstance") && this.Application["ExcelInstance"] != null)
{
celapp = ((Workbook)(this.Application["ExcelInstance"])).Application;
celwbook = (Workbook)(this.Application["ExcelInstance"]);
}
else
{
FileInfo fireadonly = new FileInfo(parentpath + @"\~$" + filename);
if (fireadonly.Exists)
return 1;
fireadonly = new FileInfo(tempworkpath + @"\~$" + filename);
if (fireadonly.Exists)
return 2;
fi1.CopyTo(tempworkpath + @"\" + filename, true);
Workbook celwbook1 = celapp.Workbooks.Open(tempworkpath + @"\" + "Book1.xls", mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss);
//Worksheet celsh = celwbook.Sheets["Live"] as Worksheet;
Worksheet celsh1 = celwbook1.ActiveSheet as Worksheet;
//celsh.Select(Missing.Value);
celapp.Visible = true;
AddIn addin = celapp.AddIns.Add(@"C:\blp\API\Office Tools\BloombergUI.xla", Missing.Value);
addin.Installed = false;
addin.Installed = true;
celwbook = celapp.Workbooks.Open(tempworkpath + @"\" + filename, mss, mss, mss, mss, mss, mss, mss, mss, mss, false, mss, mss, mss, mss);
}
//foreach (System.Diagnostics.Process process in System.Diagnostics.Process.GetProcessesByName("Excel"))
//{
// listNew.Add(process.Id);
//}
Worksheet celsh = celwbook.Sheets["Live"] as Worksheet;
celsh.Select(Missing.Value);
celapp.Visible = true;
string s1 = string.Empty;
Dictionary<int, string> columnname = new Dictionary<int, string>();
int rowindex = 0;
for (i = 1; i < celsh.UsedRange.Rows.Count; i++)
{
r = (Range)celsh.Cells[i, 1];
s1 = r.Text.ToString();
if (s1.Trim() == "Mkt Value (US$)")
{
paramstartindex = i;
}
else if (s1.Trim() == "Ave Credit Quality")
{
paramendindex = i;
}
else if (s1.Trim() == "Acc #")
{
rowindex = i;
break;
}
}
float ftap = 0;
if (float.TryParse(FOSresult.TAP, out ftap))
celsh.Cells[rowindex - 1, 19] = ftap * 1000000;
else
return -1;
float ftfa = 0;
if (float.TryParse(FOSresult.TFA, out ftfa))
celsh.Cells[rowindex - 2, 20] = ftfa * 1000000;
else
return -1;
Range oldrange = (Range)celsh.Rows[rowindex + 2, mss];
((Range)(celsh.Rows[rowindex + 1, mss])).Insert(XlDirection.xlDown, Missing.Value);
Range newrange = (Range)celsh.Rows[rowindex + 1, mss];
oldrange.Copy(newrange);
for (i = 1; i <= celsh.UsedRange.Columns.Count; i++)
{
r = (Range)celsh.Cells[rowindex, i];
columnname.Add(i, r.Text.ToString());
}
string temp = string.Empty;
i = 1;
if (FOSresult.BORS == "S" && FOSresult.Quatity != "")
{
FOSresult.Quatity = "-" + FOSresult.Quatity;
FOSresult.MVLCY = "-" + FOSresult.MVLCY;
}
while (i <= celsh.UsedRange.Columns.Count)
{
temp = this.updateValue(columnname, i, BondLife(FOSresult), FinalRating, FOSresult.FundType, FOSresult.ISIN, FOSresult.SECURITY_DES, FOSresult.CRNCY, FOSresult.ISSUER, FOSresult.COUNTRY_FULL_NAME, FOSresult.MARKET_SECTOR_DES, FOSresult.INDUSTRY_SECTOR, FOSresult.Quatity.Replace(",", ""), FOSresult.PX_BID, FOSresult.COUPON, FOSresult.YLD_YTM_BID, FOSresult.DUR_BID, TradeID.ToString(), FOSresult.MVLCY, "pending for approval");
if (temp != "")
{
((Range)(celsh.Cells[rowindex + 1, i])).Formula = temp;
}
i++;
}
#endregion
#region runmacro
string sectorissuer = ((Range)(celsh.Cells[rowindex + 1, 39])).Text.ToString();
//celwbook.Save();
bool flag = true;
while (flag)
{
flag = false;
i = paramstartindex;
while (i <= (paramendindex + 1))
{
j = 2;
while (j <= 29)
{
if (((Range)(celsh.Cells[i, j])).Text.ToString().IndexOf("#V") != -1)
{
flag = true;
break;
}
j++;
}
i++;
}
}
celwbook.RefreshAll();
this.RunMacro(celapp, new object[] { "RefreshSummary" });
/*StringBuilder html = new StringBuilder();
StringBuilder html2 = new StringBuilder();
StringBuilder html3 = new StringBuilder();
StringBuilder html4 = new StringBuilder();
celsh = celwbook.Sheets["IssuerExp"] as Worksheet;
for (int iii = 1; iii < celsh.UsedRange.Rows.Count; iii++)
{
r = (Range)celsh.Cells[iii, 1];
if (r.Text.ToString() == FOSresult.ISSUER)
{
htmlResult.issuer = FOSresult.ISSUER;
}
}
celsh = celwbook.Sheets["GUIDELINES CHECK"] as Worksheet;
//transfer data from datatable to html
int step1 = 0;
int step2 = 0;
int step3 = 0;
int row = 0;
int column = 0;
int BBBGuidlineCheck = 0;
//Range r;
//Range rr;
for (int iii = 1; iii < celsh.UsedRange.Rows.Count; iii++)
{
for (int jjj = 1; jjj < celsh.UsedRange.Columns.Count; jjj++)
{
r = (Range)celsh.Cells[iii, jjj];
switch ((string)(r.Text.ToString()))
{
case "LIMITS":
column = jjj;
step1 = iii;
break;
case "LIMITS BY SECURITY TYPES":
step2 = iii;
break;
case "LIMITS BY ISSUER":
step3 = iii;
break;
case "Issuer/Issue Rating":
BBBGuidlineCheck = iii;
break;
}
row++;
if (step3 > 0)
break;
}
}
if (RTG_FITCHlist.Contains(FinalRating))
celsh.Cells[BBBGuidlineCheck, column + 5] = "YES";
else
{
if (CRNCY == "IDR" || CRNCY == "INR" || CRNCY == "PHP")
celsh.Cells[BBBGuidlineCheck, column + 5] = "YES";
else
celsh.Cells[BBBGuidlineCheck, column + 5] = "NO";
}
for (int jj = step1 + 3; jj < celsh.UsedRange.Rows.Count; jj++)
{
r = (Range)celsh.Cells[jj, column];
if (r.Text.ToString() == "")
{
break;
}
else
{
html.Append("<tr>");
for (int k = column; k <= 8; k++)
{
rr = (Range)celsh.Cells[jj, k];
html.Append("<td>" + rr.Text.ToString() + "</td>");
}
html.Append("</tr>");
}
}
for (int jj = step2 + 2; jj < celsh.UsedRange.Rows.Count; jj++)
{
r = (Range)celsh.Cells[jj, column];
if (r.Text.ToString() == "")
break;
else
{
html2.Append("<tr>");
for (int k = column; k <= 8; k++)
{
if (k == 3 || k == 5)
continue;
rr = (Range)celsh.Cells[jj, k];
html2.Append("<td>" + rr.Text.ToString() + "</td>");
if (k == 2 || k == 4)
k++;
}
html2.Append("</tr>");
}
}
for (int jj = step3 + 2; jj < celsh.UsedRange.Rows.Count; jj++)
{
r = (Range)celsh.Cells[jj, column];
if (r.Text.ToString() == "")
break;
else
{
string stemp = r.Text.ToString();
if (stemp.Trim().ToUpper() == sectorissuer.Trim().ToUpper())
//if (stemp.Substring((temp.IndexOf('-') + 1), (stemp.ToString().Length - stemp.ToString().IndexOf('-') - 1)) == sectorissuer.ToUpper())
{
html3.Append("<tr>");
for (int k = column; k <= 8; k++)
{
if (k == 3 || k == 5)
continue;
rr = (Range)celsh.Cells[jj, k];
html3.Append("<td>" + rr.Text.ToString() + "</td>");
if (k == 2 || k == 4)
k++;
}
html3.Append("</tr>");
}
}
}
for (int jj = step3 + 2; jj < celsh.UsedRange.Rows.Count; jj++)
{
r = (Range)celsh.Cells[jj, column];
if (r.Text.ToString() == "")
break;
else
{
rr = (Range)celsh.Cells[jj, 4];
string stemp = rr.Text.ToString();
float fstemp = 0;
if (float.TryParse(stemp.Substring(0, stemp.Length - 1), out fstemp))
{
if (fstemp > 3.5)
{
html4.Append("<tr>");
for (int k = column; k <= 8; k++)
{
if (k == 3 || k == 5)
continue;
rr = (Range)celsh.Cells[jj, k];
html4.Append("<td>" + rr.Text.ToString() + "</td>");
if (k == 2 || k == 4)
k++;
}
html4.Append("</tr>");
}
}
}
}*/
celwbook.Save();
fi1 = new FileInfo(tempworkpath + @"\" + filename);
fi1.CopyTo(parentpath + @"\" + filename, true);
undoImpersonation();
/*StringBuilder finalhtml = new StringBuilder();
finalhtml.Append("<div><h2>SUMMARY OF GUIDELINES CHECK</h2><h4>LIMITS</h4><table border='1'><tr><th>S/N</th><th>GUIDELINE</th><th>Official Guidelines</th><th>Exception (Jan 2012 - Jun 2012)</th><th>ACR PORTFOLIO</th><th>MET GUIDELINE?</th><th>BUFFER BEFORE HITTING LIMIT</th></tr>");
finalhtml.Append(html.ToString());
finalhtml.Append("</table><p>*Note: Exception for Guideline 5 applies only if Guideline 4 is met</p><h4>LIMITS BY SECURITY TYPES</h4><table border='1'><tr><th>Security Types</th><th>% Weight of Target Asian Bond Portfolio</th><th>Maximum Limits(Guidelines)</th><th>Met Guidelines?</th><th>Buffer Before Hitting Limit</th></tr>");
finalhtml.Append(html2.ToString());
finalhtml.Append("</table><h4>LIMITS BY ISSUER</h4><table border='1'><tr><th>Security Types</th><th>% Weight of Target Asian Bond Portfolio</th><th>Maximum Limits(Guidelines)</th><th>Met Guidelines?</th><th>Buffer Before Hitting Limit</th></tr>");
finalhtml.Append(html3.ToString());
finalhtml.Append("</table><h4>LIMITS BY ISSUER(>3.5%)</h4><table border='1'><tr><th>Security Types</th><th>% Weight of Target Asian Bond Portfolio</th><th>Maximum Limits(Guidelines)</th><th>Met Guidelines?</th><th>Buffer Before Hitting Limit</th></tr>");
finalhtml.Append(html4.ToString());
finalhtml.Append("</table></div>");
htmlResult.htmlfortable = finalhtml.ToString();*/
return 0;
#endregion
//#region norunmacro
//else
//{
// celwbook.Save();
// celapp.DisplayAlerts = false;
// celapp.Quit();
// fi1 = new FileInfo(tempworkpath + @"\" + filename);
// fi1.CopyTo(parentpath + @"\" + filename, true);
// undoImpersonation();
// impersonateValidUser("bbuser1", "WSG053", "bbUser01");
// if (celapp != null)
// {
// Process process = new Process();
// foreach (int pID in listNew.Except(listOld))
// {
// process = System.Diagnostics.Process.GetProcessById(pID);
// process.Kill();
// }
// //Process.GetProcessesByName("Excel")[0].Kill();
// }
// undoImpersonation();
// return 0;
//}
//#endregion
}
catch (Exception e)
{
return -1;
}
finally
{
//impersonateValidUser("bbuser1", "WSG053", "bbUser01");
//if (celapp != null)
//{
// Process process = new Process();
// foreach (int pID in listNew.Except(listOld))
// {
// process = System.Diagnostics.Process.GetProcessById(pID);
// process.Kill();
// }
// //Process.GetProcessesByName("Excel")[0].Kill();
//}
//undoImpersonation();
}
}
else
{
return -1;
}
}
public int PTCCheck(result FOSresult, string FinalRating, out htmlresult htmlResult)
{
impersonateValidUser("bbuser1", "WSG053", "bbUser01");
StreamWriter tw = new StreamWriter("d:\\Temp\\Miao\\log.txt");
undoImpersonation();
htmlResult = new htmlresult();
/*List<int> listOld = new List<int>();
List<int> listNew = new List<int>();
foreach (Process process in Process.GetProcessesByName("Excel"))
{
listOld.Add(process.Id);
}*/
System.DateTime lastmodifytime;
string filename = GetFileName(parentpath, out lastmodifytime);
if (impersonateValidUser(username, Domain, password))
//if(1==1)
{
int i;
Range r;
int j;
Range rr;
int paramstartindex = 0;
int paramendindex = 0;
//string[] IssueRatings = IssueRating.Split(new char[] { '/' });
//string SPRating = IssueRatings[0];
//string MoodyRating = IssueRatings[1];
//string FitchRating = IssueRatings[2];
//string RTG1 = this.GetRTG1(SPRating, MoodyRating, FitchRating);
//string RTG2 = this.GetRTG2(SPRating, MoodyRating, FitchRating);
//Dictionary<string, string> insertresult = new Dictionary<string, string>();
//string filename = @"D:\ACR Portfolio - 120229.xlsm";
//impersonateValidUser("bbuser1", "WSG053", "bbUser01");
try
{
Application celapp = new Application();
object mss = Missing.Value;
Workbook celwbook = null;
FileInfo fi1 = new FileInfo(parentpath + @"\" + filename);
bool isOpen = false;
bool book1isOpen = false;
if (Process.GetProcessesByName("Excel").Count() > 0)
{
impersonateValidUser("bbuser1", "WSG053", "bbUser01");
tw.WriteLine("Begin to get obj");
object o=null;
try
{
o = Marshal.GetActiveObject("Excel.Application");
}
catch(Exception e)
{
tw.WriteLine("null");
o=null;
}
tw.WriteLine("End to get obj");
undoImpersonation();
if (o != null)
{
impersonateValidUser("bbuser1", "WSG053", "bbUser01");
tw.WriteLine("Begin to get app");
undoImpersonation();
celapp = (Microsoft.Office.Interop.Excel.Application)o;
Microsoft.Office.Interop.Excel.Workbooks s = celapp.Workbooks;
foreach (Microsoft.Office.Interop.Excel.Workbook ss in s)
{
string excelfilename = ss.FullName;
if (excelfilename == tempworkpath + "\\" + filename)
{
isOpen = true;
celwbook = ss;
}
if (excelfilename == tempworkpath + "\\Book1.xls")
{
book1isOpen = true;
}
}
}
}
if(!isOpen)
{
impersonateValidUser("bbuser1", "WSG053", "bbUser01");
tw.WriteLine("IsOpen: "+isOpen);
undoImpersonation();
//Process[] pr = Process.GetProcessesByName("EXCEL");
impersonateValidUser(username, Domain, password);
FileInfo fireadonly = new FileInfo(parentpath + @"\~$" + filename);
if (fireadonly.Exists)
return 1;
fireadonly = new FileInfo(tempworkpath + @"\~$" + filename);
if (fireadonly.Exists)
{
tw.Close();
return 2;
}
fi1.CopyTo(tempworkpath + @"\" + filename, true);
if (!book1isOpen)
{
Workbook celwbook1 = celapp.Workbooks.Open(tempworkpath + @"\" + "Book1.xls", mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss);
//Worksheet celsh = celwbook.Sheets["Live"] as Worksheet;
Worksheet celsh1 = celwbook1.ActiveSheet as Worksheet;
//celsh.Select(Missing.Value);
celapp.Visible = true;
AddIn addin = celapp.AddIns.Add(@"C:\blp\API\Office Tools\BloombergUI.xla", Missing.Value);
addin.Installed = false;
addin.Installed = true;
//celwbook = celapp.Workbooks.Open(tempworkpath + @"\" + filename, mss, mss, mss, mss, mss, mss, mss, mss, mss, false, mss, mss, mss, mss);
}
celwbook = celapp.Workbooks.Open(tempworkpath + @"\" + filename, mss, mss, mss, mss, mss, mss, mss, mss, mss, false, mss, mss, mss, mss);
//foreach (System.Diagnostics.Process process in System.Diagnostics.Process.GetProcessesByName("Excel"))
//{
// if (process.MainWindowTitle.IndexOf(filename) > 0)
// {
// this.Application["ProcessID"] = process.Id;
// break;
// }
//}
//this.Application["ExcelInstance"] = celwbook;
}
impersonateValidUser("bbuser1", "WSG053", "bbUser01");
tw.Close();
undoImpersonation();
Worksheet celsh = celwbook.Sheets["IssuerExp"] as Worksheet;
celsh.Select(Missing.Value);
celapp.Visible = true;
for (i = 1; i < celsh.UsedRange.Rows.Count; i++)
{
r = (Range)celsh.Cells[i, 1];
if (r.Text.ToString() == FOSresult.ISSUER)
{
htmlResult.SumofMV = ((Range)celsh.Cells[i, 2]).Text.ToString();
break;
}
}
celsh = celwbook.Sheets["Live"] as Worksheet;
celsh.Select(Missing.Value);
string s1 = string.Empty;
Dictionary<int, string> columnname = new Dictionary<int, string>();
int rowindex = 0;
for (i = 1; i < celsh.UsedRange.Rows.Count; i++)
{
r = (Range)celsh.Cells[i, 1];
s1 = r.Text.ToString();
if (s1.Trim() == "Mkt Value (US$)")
{
paramstartindex = i;
}
else if (s1.Trim() == "Ave Credit Quality")
{
paramendindex = i;
}
else if (s1.Trim() == "Acc #")
{
rowindex = i;
break;
}
}
float ftap = 0;
if (float.TryParse(FOSresult.TAP, out ftap))
celsh.Cells[rowindex - 1, 19] = ftap * 1000000;
else
return -1;
float ftfa = 0;
if (float.TryParse(FOSresult.TFA, out ftfa))
celsh.Cells[rowindex - 2, 20] = ftfa * 1000000;
else
return -1;
Range oldrange = (Range)celsh.Rows[rowindex + 2, mss];
((Range)(celsh.Rows[rowindex + 1, mss])).Insert(XlDirection.xlDown, Missing.Value);
Range newrange = (Range)celsh.Rows[rowindex + 1, mss];
oldrange.Copy(newrange);
for (i = 1; i < celsh.UsedRange.Columns.Count; i++)
{
r = (Range)celsh.Cells[rowindex, i];
columnname.Add(i, r.Text.ToString());
}
string temp = string.Empty;
i = 1;
if (FOSresult.BORS == "S" && FOSresult.Quatity != "")
{
FOSresult.Quatity = "-" + FOSresult.Quatity;
FOSresult.MVLCY = "-" + FOSresult.MVLCY;
}
while (i < celsh.UsedRange.Columns.Count)
{
temp = this.updateValue(columnname, i, BondLife(FOSresult), FinalRating, FOSresult.FundType, FOSresult.ISIN, FOSresult.SECURITY_DES, FOSresult.CRNCY, FOSresult.ISSUER, FOSresult.COUNTRY_FULL_NAME, FOSresult.MARKET_SECTOR_DES, FOSresult.INDUSTRY_SECTOR, FOSresult.Quatity.Replace(",", ""), FOSresult.PX_BID, FOSresult.COUPON, FOSresult.YLD_YTM_BID, FOSresult.DUR_BID, "", FOSresult.MVLCY, "");
if (temp != "")
{
((Range)(celsh.Cells[rowindex + 1, i])).Formula = temp;
}
i++;
}
string sectorissuer = ((Range)(celsh.Cells[rowindex + 1, 39])).Text.ToString();
//celwbook.Save();
bool flag = true;
while (flag)
{
flag = false;
i = paramstartindex;
while (i <= (paramendindex + 1))
{
j = 2;
while (j <= 29)
{
if (((Range)(celsh.Cells[i, j])).Text.ToString().IndexOf("#") != -1)
{
flag = true;
break;
}
j++;
}
i++;
}
}
celwbook.RefreshAll();
this.RunMacro(celapp, new object[] { "RefreshSummary" });
StringBuilder html = new StringBuilder();
StringBuilder html2 = new StringBuilder();
StringBuilder html3 = new StringBuilder();
StringBuilder html4 = new StringBuilder();
/*celsh = celwbook.Sheets["IssuerExp"] as Worksheet;
for (int iii = 1; iii < celsh.UsedRange.Rows.Count; iii++)
{
r = (Range)celsh.Cells[iii, 1];
if (r.Text.ToString() == FOSresult.ISSUER)
{
htmlResult.issuer = FOSresult.ISSUER;
}
}*/
bool AllYes = true;
htmlResult.issuer = FOSresult.ISSUER;
celsh = celwbook.Sheets["GUIDELINES CHECK"] as Worksheet;
//transfer data from datatable to html
int step1 = 0;
int step2 = 0;
int step3 = 0;
int row = 0;
int column = 0;
int BBBGuidlineCheck = 0;
//Range r;
//Range rr;
for (int iii = 1; iii < celsh.UsedRange.Rows.Count; iii++)
{
for (int jjj = 1; jjj < celsh.UsedRange.Columns.Count; jjj++)
{
r = (Range)celsh.Cells[iii, jjj];
switch ((string)(r.Text.ToString()))
{
case "LIMITS":
column = jjj;
step1 = iii;
break;
case "LIMITS BY SECURITY TYPES":
step2 = iii;
break;
case "LIMITS BY ISSUER":
step3 = iii;
break;
case "Issuer/Issue Rating":
BBBGuidlineCheck = iii;
break;
}
row++;
if (step3 > 0)
break;
}
}
if (FOSresult.BORS == "B")
{
if (RTG_FITCHlist.Contains(FinalRating))
celsh.Cells[BBBGuidlineCheck, column + 5] = "YES";
else
{
if (CRNCY == "IDR" || CRNCY == "INR" || CRNCY == "PHP")
celsh.Cells[BBBGuidlineCheck, column + 5] = "YES";
else
celsh.Cells[BBBGuidlineCheck, column + 5] = "NO";
}
}
else
celsh.Cells[BBBGuidlineCheck, column + 5] = "-";
html.Append("<tr>");
for (int kk = column; kk <= 8; kk++)
{
r = (Range)celsh.Cells[step1 + 1, kk];
html.Append("<th>" + r.Text.ToString() + "</th>");
}
html.Append("</tr>");
for (int jj = step1 + 3; jj < celsh.UsedRange.Rows.Count; jj++)
{
r = (Range)celsh.Cells[jj, column];
if (r.Text.ToString() == "")
{
break;
}
else
{
html.Append("<tr>");
for (int k = column; k <= 8; k++)
{
rr = (Range)celsh.Cells[jj, k];
switch (rr.Text.ToString().Trim().ToUpper())
{
case "YES":
html.Append("<td style='color:GREEN;'>" + rr.Text.ToString() + "</td>");
break;
case "NO":
html.Append("<td style='color:RED;'>" + rr.Text.ToString() + "</td>");
AllYes = false;
break;
default:
html.Append("<td>" + rr.Text.ToString() + "</td>");
break;
}
}
html.Append("</tr>");
}
}
html2.Append("<tr>");
for (int kk = column; kk <= 8; kk++)
{
if (kk == 3 || kk == 5)
continue;
r = (Range)celsh.Cells[step2 + 1, kk];
html2.Append("<th>" + r.Text.ToString() + "</th>");
}
html2.Append("</tr>");
for (int jj = step2 + 2; jj < celsh.UsedRange.Rows.Count; jj++)
{
r = (Range)celsh.Cells[jj, column];
if (r.Text.ToString() == "")
break;
else
{
html2.Append("<tr>");
for (int k = column; k <= 8; k++)
{
if (k == 3 || k == 5)
continue;
rr = (Range)celsh.Cells[jj, k];
switch (rr.Text.ToString().Trim().ToUpper())
{
case "YES":
html2.Append("<td style='color:GREEN;'>" + rr.Text.ToString() + "</td>");
break;
case "NO":
html2.Append("<td style='color:RED;'>" + rr.Text.ToString() + "</td>");
AllYes = false;
break;
default:
html2.Append("<td>" + rr.Text.ToString() + "</td>");
break;
}
if (k == 2 || k == 4)
k++;
}
html2.Append("</tr>");
}
}
html3.Append("<tr>");
for (int kk = column; kk <= 8; kk++)
{
if (kk == 3 || kk == 5)
continue;
r = (Range)celsh.Cells[step3 + 1, kk];
html3.Append("<th>" + r.Text.ToString() + "</th>");
}
html3.Append("</tr>");
for (int jj = step3 + 2; jj < celsh.UsedRange.Rows.Count; jj++)
{
r = (Range)celsh.Cells[jj, column];
if (r.Text.ToString().Trim() == "Grand Total" || r.Text.ToString() == "")
break;
else
{
string stemp = r.Text.ToString();
if (stemp.Trim().ToUpper() == sectorissuer.Trim().ToUpper())
//if (stemp.Substring((temp.IndexOf('-') + 1), (stemp.ToString().Length - stemp.ToString().IndexOf('-') - 1)) == sectorissuer.ToUpper())
{
html3.Append("<tr>");
for (int k = column; k <= 8; k++)
{
if (k == 3 || k == 5)
continue;
rr = (Range)celsh.Cells[jj, k];
switch (rr.Text.ToString().Trim().ToUpper())
{
case "YES":
html3.Append("<td style='color:GREEN;'>" + rr.Text.ToString() + "</td>");
break;
case "NO":
html3.Append("<td style='color:RED;'>" + rr.Text.ToString() + "</td>");
AllYes = false;
break;
default:
html3.Append("<td>" + rr.Text.ToString() + "</td>");
break;
}
if (k == 2 || k == 4)
k++;
}
html3.Append("</tr>");
}
}
}
html4.Append("<tr>");
for (int kk = column; kk <= 8; kk++)
{
if (kk == 3 || kk == 5)
continue;
r = (Range)celsh.Cells[step3 + 1, kk];
html4.Append("<th>" + r.Text.ToString() + "</th>");
}
html4.Append("</tr>");
for (int jj = step3 + 2; jj < celsh.UsedRange.Rows.Count; jj++)
{
r = (Range)celsh.Cells[jj, column];
if (r.Text.ToString().Trim() == "Grand Total" || r.Text.ToString() == "")
break;
else
{
rr = (Range)celsh.Cells[jj, 4];
string stemp = rr.Text.ToString();
float fstemp = 0;
if (float.TryParse(stemp.Substring(0, stemp.Length - 1), out fstemp))
{
if (fstemp > 3.5)
{
html4.Append("<tr>");
for (int k = column; k <= 8; k++)
{
if (k == 3 || k == 5)
continue;
rr = (Range)celsh.Cells[jj, k];
switch (rr.Text.ToString().Trim().ToUpper())
{
case "YES":
html4.Append("<td style='color:GREEN;'>" + rr.Text.ToString() + "</td>");
break;
case "NO":
html4.Append("<td style='color:RED;'>" + rr.Text.ToString() + "</td>");
AllYes = false;
break;
default:
html4.Append("<td>" + rr.Text.ToString() + "</td>");
break;
}
if (k == 2 || k == 4)
k++;
}
html4.Append("</tr>");
}
}
}
}
//celapp.DisplayAlerts = false;
//celapp.Quit();
celwbook.Save();
//celwbook.Close();
impersonateValidUser(username, Domain, password);
fi1 = new FileInfo(tempworkpath +@"\"+ filename);
fi1.CopyTo(parentpath +@"\"+ filename, true);
undoImpersonation();
//impersonateValidUser("bbuser1", "WSG053", "bbUser01");
//if (celapp != null)
//{
// Process process = new Process();
// foreach (int pID in listNew.Except(listOld))
// {
// process = System.Diagnostics.Process.GetProcessById(pID);
// process.Kill();
// }
// //Process.GetProcessesByName("Excel")[0].Kill();
//}
//undoImpersonation();
StringBuilder finalhtml = new StringBuilder();
finalhtml.Append("<div><h2>SUMMARY OF GUIDELINES CHECK</h2><h4>LIMITS</h4><table border='1'>");
finalhtml.Append(html.ToString());
finalhtml.Append("</table><p>*Note: Exception for Guideline 5 applies only if Guideline 4 is met</p><h4>LIMITS BY SECURITY TYPES</h4><table border='1'>");
finalhtml.Append(html2.ToString());
finalhtml.Append("</table><h4>LIMITS BY ISSUER</h4><table border='1'>");
finalhtml.Append(html3.ToString());
finalhtml.Append("</table><h4>LIMITS BY ISSUER(>3.5%)</h4><table border='1'>");
finalhtml.Append(html4.ToString());
finalhtml.Append("</table></div>");
htmlResult.htmlfortable = finalhtml.ToString();
htmlResult.Flag = AllYes;
impersonateValidUser("bbuser1", "WSG053", "bbUser01");
tw.WriteLine("Done");
undoImpersonation();
return 0;
}
catch (Exception e)
{
return -1;
}
finally
{
impersonateValidUser("bbuser1", "WSG053", "bbUser01");
tw.Close();
undoImpersonation();
//impersonateValidUser("bbuser1", "WSG053", "bbUser01");
//if (celapp != null)
//{
// Process process = new Process();
// foreach (int pID in listNew.Except(listOld))
// {
// process = System.Diagnostics.Process.GetProcessById(pID);
// process.Kill();
// }
// //Process.GetProcessesByName("Excel")[0].Kill();
//}
//undoImpersonation();
}
}
else
return 3;
}
