private void GetInfoFromExcel(result FOSresult)
{
List<int> listOld = new List<int>();
List<int> listNew = new List<int>();
foreach (Process process in Process.GetProcessesByName("Excel"))
{
listOld.Add(process.Id);
}
string filename = GetFileName(parentpath);
impersonateValidUser(username, Domain, password);
//End -Get IssuerList From FOS
DirectoryInfo di = new DirectoryInfo(databasepathG1);
foreach (FileInfo f in di.GetFiles().Where(s => s.Name.IndexOf('~') != 0))
{
f.CopyTo(databasepath1 + @"\" + f.Name, true);
}
int year = DateTime.Now.Year;
CopyDirectory(databasepathG2 + @"\" + year, databasepath2 + @"\" + year);
Application celapp = new Application();
foreach (System.Diagnostics.Process process in System.Diagnostics.Process.GetProcessesByName("Excel"))
{
listNew.Add(process.Id);
}
object mss = Missing.Value;
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;
Workbook celwbook = celapp.Workbooks.Open(tempworkpath + @"\" + filename, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss);
Worksheet celsh = celwbook.Sheets["Live"] as Worksheet;
celsh.Select(Missing.Value);
celapp.Visible = true;
Range r;
string s1 = string.Empty;
for (int i = 1; i < celsh.UsedRange.Rows.Count; i++)
{
r = (Range)celsh.Cells[i, 1];
s1 = r.Text.ToString();
if (s1.Trim() == "Acc #")
{
r = (Range)celsh.Cells[i - 1, 19];
s1 = r.Text.ToString();
break;
}
}
FOSresult.TAP = s1.Substring(0, s1.LastIndexOf(",000,000")).Trim();
celsh = celwbook.Sheets["Curncy"] as Worksheet;
celsh.Select(Missing.Value);
bool flag = true;
while (flag)
{
flag = false;
for (int icurncy = 1; icurncy <= celsh.UsedRange.Rows.Count; icurncy++)
{
if (((Range)(celsh.Cells[icurncy, 1])).Text.ToString() == FOSresult.CRNCY)
{
if (((Range)(celsh.Cells[icurncy, 3])).Text.ToString().IndexOf("#") != -1)
{
flag = true;
break;
}
else
FOSresult.Exchange = ((Range)(celsh.Cells[icurncy, 3])).Text.ToString();
}
}
}
celapp.Quit();
undoImpersonation();
impersonateValidUser("bbuser1", "WSG053", "bbUser01");
if (celapp != null)
{
int processID = listNew.Except(listOld).FirstOrDefault();
Process process = System.Diagnostics.Process.GetProcessById(processID);
process.Kill();
//Process.GetProcessesByName("Excel")[0].Kill();
}
}
public result Submit(string ISIN)
{
result FOSresult = new result();
//open excel to get two parameter
SessionOptions sessionOptions = new SessionOptions();
sessionOptions.ServerHost = "WSG053";
sessionOptions.ServerPort = 0x2002;
Session session = new Session(sessionOptions);
if (!session.Start())
{
Console.WriteLine("Could not start session.");
Environment.Exit(1);
}
if (!session.OpenService("//blp/refdata"))
{
Console.WriteLine("Could not open service //blp/refdata");
Environment.Exit(1);
}
CorrelationID requestID = new CorrelationID(1L);
Request request = session.GetService("//blp/refdata").CreateRequest("ReferenceDataRequest");
request.GetElement("securities").AppendValue(ISIN + " corp");
Element fields = request.GetElement("fields");
fields.AppendValue("SECURITY_DES");
fields.AppendValue("CRNCY");
fields.AppendValue("ISSUER");
fields.AppendValue("COUNTRY_FULL_NAME");
fields.AppendValue("MARKET_SECTOR_DES");
fields.AppendValue("INDUSTRY_SECTOR");
fields.AppendValue("PX_BID");
fields.AppendValue("COUPON");
fields.AppendValue("YLD_YTM_BID");
fields.AppendValue("DUR_BID");
fields.AppendValue("RTG_SP");
fields.AppendValue("RTG_SP_LT_LC_ISSUER_CREDIT");
fields.AppendValue("RTG_MOODY");
fields.AppendValue("RTG_MOODY_LONG_TERM");
fields.AppendValue("RTG_MDY_LC_CURR_ISSUER_RATING");
fields.AppendValue("RTG_FITCH");
fields.AppendValue("RTG_FITCH_LT_ISSUER_DEFAULT");
fields.AppendValue("COLLAT_TYP");
fields.AppendValue("MTY_YEARS_TDY");
fields.AppendValue("NXT_CALL_DT");
fields.AppendValue("MATURITY");
fields.AppendValue("GUARANTOR");
Console.WriteLine("Sending Request:" + request);
session.SendRequest(request, null);
while (true)
{
Event eventObj = session.NextEvent();
this.processEvent(eventObj, session);
if (eventObj.Type == Event.EventType.RESPONSE)
{
IssueRating = RTG_SP + "/" + this.GetMoody(RTG_MOODY, RTG_MOODY_LONG_TERM) + "/" + RTG_FITCH;
IssuerRating = RTG_SP_LT_LC_ISSUER_CREDIT + "/" + RTG_MDY_LC_CURR_ISSUER_RATING + "/" + RTG_FITCH_LT_ISSUER_DEFAULT;
if (CallDate != "NA")
{
DateTime newdate = DateTime.Parse(CallDate);
DateTime nowdate = DateTime.Now;
decimal dCallDate = (decimal)(((TimeSpan)(newdate - nowdate)).Days) / 365;
CallDate = StandardFormat(Math.Round(dCallDate, 2).ToString(), 2);
}
FOSresult.CallDate = CallDate;
FOSresult.COLLATERAL_TYPE = COLLATERAL_TYPE;
FOSresult.COUNTRY_FULL_NAME = COUNTRY_FULL_NAME;
FOSresult.COUPON = COUPON;
FOSresult.CRNCY = CRNCY;
FOSresult.DUR_BID = DUR_BID;
FOSresult.INDUSTRY_SECTOR = INDUSTRY_SECTOR.ToUpper();
FOSresult.PX_BID = PX_BID;
FOSresult.YLD_YTM_BID = YLD_YTM_BID;
FOSresult.SECURITY_DES = SECURITY_DES;
FOSresult.IssueRating = IssueRating;
FOSresult.IssuerRating = IssuerRating;
FOSresult.MARKET_SECTOR_DES = MARKET_SECTOR_DES;
FOSresult.YearToMaturity = Maturity;
FOSresult.ISSUER = ISSUER;
GetInfoFromExcel(FOSresult);
//undoImpersonation();
return FOSresult;
//return base.Json(new { SECURITY_DES = SECURITY_DES, CRNCY = CRNCY, ISSUER = ISSUER, COUNTRY_FULL_NAME = COUNTRY_FULL_NAME, MARKET_SECTOR_DES = MARKET_SECTOR_DES, INDUSTRY_SECTOR = INDUSTRY_SECTOR.ToUpper(), PX_BID = PX_BID, COUPON = COUPON, YLD_YTM_BID = YLD_YTM_BID, DUR_BID = DUR_BID, IssueRating = IssueRating, IssuerRating = IssuerRating, COLLATERAL_TYPE = COLLATERAL_TYPE, YearToMaturity = Maturity, CallDate = CallDate });
}
}
}
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;
}
}
private string BondLife(result FOSresult)
{
string year = "";
float YTM = 0;
float CD = 0;
float.TryParse(FOSresult.YearToMaturity, out YTM);
float.TryParse(FOSresult.CallDate, out CD);
if (YTM == 0)
year = FOSresult.CallDate;
else
{
if (CD == 0)
year = FOSresult.YearToMaturity;
else
{
if (YTM <= CD)
{
year = FOSresult.YearToMaturity;
}
else
year = FOSresult.CallDate;
}
}
return year;
}
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 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;
}
