public Array Get_intex_selections(string type)
{
using (WebClient client = new ThisAddIn.MyWebClient())
{
string responsebody;
byte[] responsebytes;
string selected_deal = "";
if (type != "deals")
{
selected_deal = type;
type = "yymm";
}
var reqparm = new System.Collections.Specialized.NameValueCollection
{
{ "type", type }
};
reqparm.Add("selected_deal", selected_deal);
responsebytes = client.UploadValues(Globals.ThisAddIn.Get_base_url() + "get_intex_selections", "POST", reqparm);
responsebody = Encoding.UTF8.GetString(responsebytes);
responsebody = responsebody.Replace("[", "").Replace("]", "").Replace("\"", "").Replace(" ", "");
Array list_ = responsebody.Split(',');
return(list_);
}
}
private void button1_Click(object sender, EventArgs e)
{
if (deals.SelectedItem != null)
{
string deal_ = deals.SelectedItem.ToString();
string yymm_ = yymm.SelectedItem.ToString();
if (deal_ != "transaction" & yymm_ != "yymm")
{
var reqparm = new System.Collections.Specialized.NameValueCollection();
Excel.Application TaraApp = (Excel.Application)Globals.ThisAddIn.Application;
string responsebody;
byte[] responsebytes;
string result_path = TaraApp.Application.UserLibraryPath + "\\tara\\";
reqparm.Add("deal_", deal_);
reqparm.Add("yymm_", yymm_);
using (WebClient client = new ThisAddIn.MyWebClient())
{
responsebytes = client.UploadValues(Globals.ThisAddIn.Get_base_url() + "load_intex_data", "POST", reqparm);
responsebody = Encoding.UTF8.GetString(responsebytes);
responsebody = responsebody.Replace("|", "--").Replace("}[", "|");
String[] dropdown_body = responsebody.Split('|');
responsebody = dropdown_body[1];
String drop_downs = dropdown_body[0];
responsebody = responsebody.Replace("},{", "|").Replace("}", "").Replace("]", "").Replace("[", "").Replace("'", "").Replace("{", "");
drop_downs = drop_downs.Replace("[", "").Replace("]", "");
Array raw_loan_data = responsebody.Split('|');
Array buckets_ = drop_downs.Replace("'", "").Replace("\", \"", "|").Split('|');
string f = deal_ + "_" + yymm_ + ".csv";
string written_file = Globals.Ribbons.Ribbon1.Response_to_csv(raw_loan_data, result_path, f, false, yymm_, false);
string result_file_path = Path.Combine(result_path, written_file);
TaraApp.Workbooks.Open(@result_file_path);
Excel.Worksheet TaraSheet = TaraApp.ActiveWorkbook.ActiveSheet;
TaraSheet.Name = "tara_sheet";
TaraSheet.Activate();;
int iTotalRows = TaraSheet.UsedRange.Rows.Count;
int col_count = 0;
foreach (string bucket_col_val in buckets_)
{
string bucket = bucket_col_val.Split(':')[1].Replace("\"", "").Trim();
col_count += 1;
Excel.Range cRng = TaraSheet.Cells[1, col_count];
if (bucket != "None" & bucket != "")
{
//if (cRng.Comment != null) { cRng.ClearComments(); }
//Comment comment = cRng.AddComment(bucket);
//comment.Shape.Width = 200;
//comment.Shape.Height = 100;
var drop_list = new System.Collections.Generic.List <string>();
drop_list = bucket.Split(',').ToList();
int drop_length = bucket.Length;
var list_count = drop_list.Count;
int section_length;
string flatList = "";
if (drop_length > 250)
{
section_length = 250 / list_count - 1;
for (int spark_num = 0; spark_num < list_count; spark_num += 1)
{
flatList = flatList + drop_list[spark_num].Trim().Substring(0, Math.Min(drop_list[spark_num].Trim().Length, section_length)) + ",";
}
}
else
{
flatList = string.Join(",", drop_list.ToArray());
}
//Array flat_list = flatList.ToArray();
//var flatList = string.Join(("Spark "+Convert.ToString(Enumerable.Range(1, list_count)).ToArray(),",");
for (int row_count = 1; row_count <= iTotalRows; row_count += 1)
{
if (row_count < 250 & list_count < 40)
{
var drop_cell = TaraSheet.Cells[row_count, col_count];//.Columns[col_count];//:,col_count]#.Cells[row_count, col_count];
drop_cell.Validation.Delete();
drop_cell.Validation.Add(
XlDVType.xlValidateList,
XlDVAlertStyle.xlValidAlertInformation,
XlFormatConditionOperator.xlBetween,
flatList,
Type.Missing);
drop_cell.Validation.IgnoreBlank = false;
drop_cell.Validation.InCellDropdown = true;
drop_cell.Validation.ShowError = false;
}
}
}
}
//var in_ = System.IO.File.ReadAllText(file_path);
// var inputFile = new FileInfo(export_path); // could be .xls or .xlsx too
Excel.Workbook tara_book = (TaraApp.ActiveWorkbook);
// tara_book.SaveAs(deal_ + "_" + yymm_);
Excel.Worksheet tara_sheet = (TaraApp.ActiveSheet);
tara_sheet.Name = "tara_sheet"; //deal_ + "_" + yymm_;
Globals.Ribbons.Ribbon1.Apply_formats();
}
}
}
}
private void Compare_button_Click(object sender, RibbonControlEventArgs e)
{
string tara_sheet_ = "yes";
var reqparm = new System.Collections.Specialized.NameValueCollection();
var _data = Globals.ThisAddIn.Get_Data();
string responsebody;
byte[] responsebytes;
Excel.Application TaraApp = (Excel.Application)Globals.ThisAddIn.Application;
Excel.Worksheet tara_sheet = (Excel.Worksheet)TaraApp.ActiveSheet; //Worksheets["TARA"];
object mis = Type.Missing;
if (_data.Item1 == "no data")
{
tara_sheet_ = "skip";
}
if (tara_sheet_ != "skip")
{
using (WebClient client = new ThisAddIn.MyWebClient())
{
Excel.Worksheet work_sheet = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet);
reqparm.Add("data", _data.Item1);
reqparm.Add("type", compare_dropdown.SelectedItem.Label);
responsebytes = client.UploadValues(Globals.ThisAddIn.Get_base_url() + "load_comparison", "POST", reqparm);
responsebody = Encoding.UTF8.GetString(responsebytes);
responsebody = responsebody.Replace("{", "").Replace("}", "").Replace("\"", "");
Array ranks = responsebody.Split(',');
int eTotalColumns = tara_sheet.UsedRange.Columns.Count;
int comparison_col = 1;
string add_comparison = "yes";
for (comparison_col = 1; comparison_col <= eTotalColumns; comparison_col++)
{
string existing_header = tara_sheet.Cells[1, comparison_col].Value.ToString().Trim(' ').Trim('"');
if (Convert.ToString(existing_header).Contains("comparison"))
{
add_comparison = "no";
break;
}
}
if (add_comparison == "yes")
{
comparison_col = 3;
Excel.Range Prng = tara_sheet.UsedRange.Columns[comparison_col];
Prng.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow);
Prng.NumberFormat = "0%";
tara_sheet.Cells[1, comparison_col].Value2 = "comparison";
}
foreach (string rank in ranks)
{
int row = Convert.ToInt16(rank.Trim(' ').Split(':')[0]);
String rank_value = rank.Trim(' ').Split(':')[1];
work_sheet.Cells[row + 2, comparison_col].Value = rank_value;
if (rank_value == "")
{
rank_value = ".5";
}
if (rank_value != "")
{
Double v = Convert.ToDouble(rank_value) - .5;
int c_index = 3;
if (v < 0)
{
c_index = 23;
}
Double sensitivity = Math.Max(0, Math.Min(1, 1 - Math.Abs(v)));
Excel.Range iRng = work_sheet.Cells[row + 2, comparison_col];
if (!String.IsNullOrEmpty(Convert.ToString(iRng.Cells.Value2)))
{
iRng.Interior.ColorIndex = c_index;
iRng.Interior.TintAndShade = sensitivity;
}
}
}
}
}
tara_sheet.Outline.ShowLevels(3, 3);
}
private void Tara_ratings()
{
Excel.Application TaraApp = (Excel.Application)Globals.ThisAddIn.Application;
Excel.Worksheet tara_sheet;
string tara_sheet_ = "tara_sheet";
try
{
tara_sheet = (Excel.Worksheet)TaraApp.Worksheets["tara_sheet"];
}
catch (System.Runtime.InteropServices.COMException)
{
tara_sheet_ = "no";
}
if (tara_sheet_ != "no")
{
tara_sheet = (Excel.Worksheet)TaraApp.Worksheets[tara_sheet_];
using (WebClient client = new ThisAddIn.MyWebClient()) //client = new WebClient())
{
string server = Globals.ThisAddIn.Get_base_url();
string responsebody = "";
var reqparm = new System.Collections.Specialized.NameValueCollection();
var _data = Globals.ThisAddIn.Get_Data();
reqparm.Add("data", _data.Item1);
reqparm.Add("d_date", null);
reqparm.Add("get_sup", "no");
byte[] responsebytes = client.UploadValues(server + "ask_tara", "POST", reqparm);
responsebody = Encoding.UTF8.GetString(responsebytes);
//responsebody = responsebody.Replace("},{", "|").Replace("[", "").Replace("]", "").Replace("\"", "").Replace("}", "").Replace("{", "");
responsebody = responsebody.Replace("|", "--").Replace("____change___", "|");
String[] body_change = responsebody.Split('|');
responsebody = body_change[0];
String responsechange = body_change[1];
responsebody = responsebody.Replace("], \"", "|").Replace("[", "").Replace("]", "").Replace("{", "").Replace("}", "");
responsechange = responsechange.Replace("], \"", "|").Replace("[", "").Replace("]", "");
Excel.Range pRng = tara_sheet.Range["A1"];
Array responses = responsebody.Split('|');
String[] changes = responsechange.Split('|');
string title = "";
if (Convert.ToString(responses.GetValue(0)) != "\"could not find data\"")
{
int col_count = 0;
foreach (string r in responses)
{
// if value FALSE is in the corresponding changes array then dont' run
if (changes[col_count].Contains("false"))
{
//column has an update proceed
var column = r.Split(':')[0].Replace("\"", "");
int place_col = 0;
for (int place_col_ = 1; place_col_ <= responses.Length; place_col_ += 1)
{
title = tara_sheet.Cells[1, place_col_].Value2;
if (column == title)
{
place_col = place_col_; break;
}
}
if (place_col != 0 & title != "")
{
int row_count = r.Split(':')[1].Replace("\", ", "|").Split('|').Length + 1;
for (int place_row = 2; place_row <= row_count; place_row += 1)// can change this to only loop those fields with a FALSE value in change
{
if (changes[col_count].Split(':')[1].Split(',')[place_row - 2].Contains("false"))
{ // proceed found a change
var cell = r.Split(':')[1].Replace("\", ", "|").Split('|')[place_row - 2].Replace("\"", "").Trim();
tara_sheet.Cells[place_row, place_col].Value2 = cell;
if (title == "fy_ncf" | title == "mr_occupancy" | title == "stress")
{
tara_sheet.Cells[place_row, place_col].Interior.ColorIndex = 35;
}
}
}
}
}
col_count += 1;
}
}
}
Apply_formats();
}
}
private void Get_illumination()
{
Excel.Application TaraApp = (Excel.Application)Globals.ThisAddIn.Application;
var reqparm = new System.Collections.Specialized.NameValueCollection();
Excel.Worksheet tara_sheet = (Excel.Worksheet)TaraApp.Worksheets["tara_sheet"];
tara_sheet.UsedRange.Columns[9, Type.Missing].Interior.ColorIndex = 0;
string JSON_data = Globals.ThisAddIn.Get_Data().Item1;
using (WebClient client = new ThisAddIn.MyWebClient())
{
string server = Globals.ThisAddIn.Get_base_url();
string responsebody = "";
reqparm.Add("data", JSON_data);
byte[] responsebytes = client.UploadValues(server + "illuminate", "POST", reqparm);
responsebody = Encoding.UTF8.GetString(responsebytes);
responsebody = responsebody.Replace("|", "--").Replace("][", "|").Replace("[", "").Replace("]", "");
Excel.Range pRng = tara_sheet.Range["A1"];
Array responses = responsebody.Split('|');
Double sensitivity;
int eTotalColumns = tara_sheet.UsedRange.Columns.Count;
int base_loss_col = 1;
foreach (string c in responses)
{
int place_col = 1;
string response_header = c.Split(',')[0].Trim(' ').Trim('"');
for (int e_col = place_col; e_col <= eTotalColumns; e_col++)
{
if (tara_sheet.Cells[1, e_col].Value2 != null)
{
string existing_header = tara_sheet.Cells[1, e_col].Value.ToString().Trim(' ').Trim('"');
if (existing_header == "loss_expectation")
{
base_loss_col = e_col;
}
else
{
string r_h = response_header.ToLower().Replace(" ", "").Replace(")", "").Replace("(", "").Replace(":", "").Replace(";", "").Replace("-", "").Replace("'", "").Replace("]", "").Replace("[", "").Trim('"').Replace("\"", "").Replace(",", "");
string e_h = existing_header.ToLower().Replace(" ", "").Replace(")", "").Replace("(", "").Replace(":", "").Replace(";", "").Replace("-", "").Replace("'", "").Replace("]", "").Replace("[", "").Trim('"').Replace("\"", "").Replace(",", "");
if (r_h == "keydrivers" & e_h == "key_drivers")
{
int place_row = 1;
for (int row_c = 1; row_c < c.Split(',').Length; row_c += 1)
{
string v = c.Split(',')[row_c].Replace("\\\\r\\\\n", "\r\n").Replace("\\", "").Replace("\"", "").Replace("--", "|").Replace("*", "-");
tara_sheet.Cells[row_c + place_row, e_col].Value2 = v;
}
tara_sheet.UsedRange.Columns[e_col].WrapText = false;
}
else if (r_h == "latent_potential")
{
if (e_h == r_h)
{
int place_row = 1;
for (int row_c = 1; row_c < c.Split(',').Length; row_c += 1)
{
Double v = Convert.ToDouble(c.Split(',')[row_c]);
string v_desc = "none";
if (v > .04)
{
v_desc = "volatile";
}
else if (v > .03)
{
v_desc = "high";
}
else if (v > .015)
{
v_desc = "elevated";
}
else if (v > 0.000)
{
v_desc = "moderate";
}
else
{
v_desc = "low";
}
tara_sheet.Cells[row_c + place_row, e_col].Value2 = v_desc;
}
}
else if (e_h == "tara_rating")
{
int place_row = 1;
for (int row_c = 1; row_c < c.Split(',').Length; row_c += 1)
{
Double v = Convert.ToDouble(c.Split(',')[row_c]);
int c_index = 1;
sensitivity = 0;
c_index = 1;
if (v > .4)
{
c_index = 3; sensitivity = .9;
}
else if (v > .2)
{
c_index = 3; sensitivity = .25;
}
else if (v > -.1)
{
c_index = 3; sensitivity = 0;
}
else if (v > -.2)
{
c_index = 3; sensitivity = -0.25;
}
else
{
c_index = 3; sensitivity = -.9;
}
//sensitivity = Math.Max(0.2, Math.Min(1,Math.Abs(v)));
Excel.Range iRng = tara_sheet.Cells[row_c + place_row, e_col];
if (!String.IsNullOrEmpty(Convert.ToString(iRng.Cells.Value2)))
{
iRng.Font.Color = c_index;
iRng.Font.TintAndShade = sensitivity;
}
}
}
}
else if (e_h == r_h)
{
int place_row = 1;
for (int row_c = 1; row_c < c.Split(',').Length; row_c += 1)
{
Double v = Convert.ToDouble(c.Split(',')[row_c]);
int c_index = 3;
if (v > 0)
{
c_index = 23;
}
sensitivity = Math.Max(0, Math.Min(1, 1 - Math.Abs(v)));
Excel.Range iRng = tara_sheet.Cells[row_c + place_row, e_col];
if (!String.IsNullOrEmpty(Convert.ToString(iRng.Cells.Value2)))
{
iRng.Interior.ColorIndex = c_index;
iRng.Interior.TintAndShade = sensitivity;
}
}
e_col = eTotalColumns;
}
}
}
}
}
}
Apply_formats();
tara_sheet.Outline.ShowLevels(3, 3);
}
