//Update sent on if it has not already been sent
public string Final_Sent_Merge(CapitalFrm cform, int i)
{
string tprString = "";
if ((string)cform.dgvFinalView.Rows[i].Cells[(int)Capital.fTpr].Value == "PAY")
{
tprString = " t.Sent_AMT = t.Sent_AMT - " + Convert.ToDouble(cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTamount].Value);
}
else
{
tprString = " t.Sent_AMT = t.Sent_AMT + " + Convert.ToDouble(cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTamount].Value);
}
SSQL = "Merge INTO OPERATIONS.COST_OF_CAPITAL_SCF t "
+ " Using( "
+ " Select '" + (string)cform.dgvFinalView.Rows[i].Cells[(int)Capital.fTcomment].Value + "' TCOMMENT, "
+ " ' " + (string)cform.dgvFinalView.Rows[i].Cells[(int)Capital.fPortfolio].Value + "' PORTFOLIO "
+ " From DUAL ) d "
+ " on (d.TCOMMENT = t.TCOMMENT and d.PORTFOLIO = t.PORTFOLIO) "
+ " When MATCHED THEN "
+ " UPDATE SET t.Sent_ON = TO_DATE(SYSDATE), " + tprString;
return(SSQL);
}
public void MergeFeedView(CapitalFrm cform) //Display DGV on Feed tab
{
Connection connection = new Connection();
GetSQL sql = new GetSQL();
//loop through feed view and use merge to insert if unmatched
for (int i = 0; i < cform.dgvFeedView.Rows.Count; ++i)
{
connection.SendQuery(sql.CapitalCost_of_capital_Merge(cform, i));
}
}
public void DisplayComboBox(CapitalFrm cform) //Populate the Month Year Combo Box
{
DateTime myDate = DateTime.Now.Date.AddMonths(-1);
// Show last 6 months in combobox
for (int i = 1; i < 7; i++)
{
cform.dateCB.Items.Add(myDate.ToString("MMMM yyyy", CultureInfo.InvariantCulture));
myDate = myDate.AddMonths(-1);
}
// Show first index as default
cform.dateCB.SelectedIndex = 0;
}
public void AppInit(CapitalFrm cform) //Main Setup for form
{
//Variables
GetUserInfo user = new GetUserInfo();
user.SetUserInfo(signOnName: Environment.UserName, signOnTime: DateTime.Now);
//ComboBox
DisplayComboBox(cform);
//Log on info
cform.userInfoTb.Text = user.SignOnName.ToUpper() + ": " + user.SignOnTime.ToShortDateString();
}
public void GenerateFile(CapitalFrm cform)
{
// Test paths
//string archivefileloc = @"\\#\Temp\";
//string tempfileloc = @"\\#\Cost of Capital\Temp\";
//string finalfileloc = @"C:\Temp\";
string archivefileloc = @"\\#\Cost of Capital\";
string tempfileloc = @"\\#\Temp\";
string finalfileloc = @"\\#\MurexFiles\";
string finalfilename = DateTime.Now.ToString("yyyyMMdd") + DateTime.Now.ToString("HHmmss") + "_COSTCAPSCF_new.csv";
string archivefilename = DateTime.Now.ToString("MMMM") + "_" + DateTime.Now.ToString("yyyy") + "_COSTCAPSCF.xls";
Connection connection = new Connection();
GetSQL sql = new GetSQL();
//loop through feed view and use merge to update values
for (int i = 0; i < cform.dgvFeedView.Rows.Count; ++i)
{
connection.SendQuery(sql.Final_Sent_Merge(cform, i));
}
// Create and save CSV
try
{
// CreateCSV
CreateCSV(cform.dgvFinalView, Path.Combine(tempfileloc, finalfilename));
File.Move(Path.Combine(tempfileloc, finalfilename), Path.Combine(finalfileloc, finalfilename));
//Archive Data
CreateExcel(cform, archivefileloc, archivefilename);
//Clear final view to force action
cform.dgvFinalView.Rows.Clear();
cform.dgvFinalView.Refresh();
//Select tab
cform.CapitalTabs.SelectTab(1);
}
catch (Exception ex)
{
MessageBox.Show("Exception occurred while creating files " + ex.Message);
}
}
//Cost of capital view
public string Cost_of_capital_View(CapitalFrm cform)
{
// Text Formated as 'Month[0] Year[1]'
String[] dateText = cform.dateCB.Text.Split(' ');
SSQL = " SELECT C.trader_description Trader, P.Portfolio, "
+ "SUM(C.mtd_credit_charges) MTD, SUM(C.ytd_credit_charges) YTD, P.FUND_ID FUND "
+ "FROM table(RPTTRADE.populate_cost_of_cap('" + DateTime.ParseExact(dateText[0], "MMMM", CultureInfo.CurrentCulture).Month + "','" + dateText[1] + "')) C "
+ " INNER Join (Select distinct t.TRADER_ID, t.PORTFOLIO_LABEL Portfolio, trim(UPPER(t.fund_ID)) FUND_ID, F.NAME "
+ " FROM ODSTRADE.TRADER_SUB_OBJECTIVE t "
+ " Inner join ODSTRADE.fund f on F.FUND_ID= T.FUND_ID "
+ " Where t.ACTIVE_FLAG = 'Y' and T.FUND_ID in (Select FUND_ID From OPERATIONS.VW_FUND_ACTIVE) "
+ " AND t.PORTFOLIO_LABEL is not null "
+ " ) P on P.TRADER_ID=C.TRADER_ID and P.Name=C.fund_NAME "
+ " Where P.FUND_ID not in ('XH') "
+ "GROUP BY C.trader_description,P.Portfolio, P.FUND_ID "
+ "order by C.trader_description ";
return(SSQL);
}
//Cost of Capital Merge
public string CapitalCost_of_capital_Merge(CapitalFrm cform, int i)
{
//(string)cform.dgvCostView.Rows[i].Cells[(int)Capital.portfolio].Value
SSQL = "Merge INTO OPERATIONS.COST_OF_CAPITAL_SCF t "
+ " Using( "
+ " Select '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTcomment].Value + "' TCOMMENT, "
+ " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fPortfolio].Value + "' PORTFOLIO "
+ " From DUAL ) d "
+ " on (d.TCOMMENT = t.TCOMMENT and d.PORTFOLIO = t.PORTFOLIO) "
+ " When NOT MATCHED THEN "
+ " Insert ( "
+ " t.TYPOLOGY, t.PORTFOLIO, t.CPTY, t.INST, t.FX, t.TPR, "
+ " t.TDATE, t.TAMOUNT, t.TFX, t.TCOMMENT, t.STRATEGY, t.BRKSRCE, "
+ " t.BRKDEST, t.UNIQUE_ID, t.FUND, t.ACCT_NUM, t.SENT_AMT) "
+ "VALUES ( "
+ " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTypology].Value + "', " // Typology
+ " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fPortfolio].Value + "', " // Portfolio
+ " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fCpty].Value + "', " // CPTY
+ " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fInst].Value + "', " // INST
+ " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fFx].Value + "', " // FX
+ " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTpr].Value + "', " // TPR
+ " TO_DATE('" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTdate].Value + "', 'MM/DD/YYYY'), " // TDATE
+ " " + Convert.ToDouble(cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTamount].Value) + ", " // TAMOUNT
+ " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTfx].Value + "', " // TFX
+ " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTcomment].Value + "', " // TCOMMENT
+ " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fStrategy].Value + "', " // STRATEGY
+ " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fBrksrce].Value + "', " // BRKSRCE
+ " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fBrkdest].Value + "', " // BRKDEST
+ " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fUnique_Id].Value + "', " // UNIQUE_ID
+ " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fFund].Value + "', " // FUND
+ " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fAcct_Num].Value + "', " // ACCT_NUM
+ " " + Convert.ToDouble(cform.dgvFeedView.Rows[i].Cells[(int)Capital.fSent_Amt].Value) + " " // SENT_AMT
+ " ) ";
return(SSQL);
}
public void DisplayCapitalView(CapitalFrm cform) //Display DGV on Cost of Capital
{
//Clear all DGVs
cform.dgvFinalView.Rows.Clear();
cform.dgvFinalView.Refresh();
cform.dgvFeedView.Rows.Clear();
cform.dgvFeedView.Refresh();
cform.dgvCostView.Rows.Clear();
cform.dgvCostView.Refresh();
// Variables
Connection connection = new Connection();
GetSQL sql = new GetSQL();
OdbcDataReader dataReader = connection.RunQuery(sql.Cost_of_capital_View(cform));
if (dataReader.HasRows)
{
while (dataReader.Read())
{
cform.dgvCostView.Rows.Add(dataReader["TRADER"].ToString(),
dataReader["PORTFOLIO"].ToString(),
dataReader["MTD"],
dataReader["YTD"],
dataReader["FUND"].ToString()
);
}
//Add fund level totals
double ytdCI, ytdQA, ytdQE, ytdQO, ytdQM;
double mtdCI, mtdQA, mtdQE, mtdQO, mtdQM;
ytdCI = ytdQA = ytdQE = ytdQO = ytdQM = 0;
mtdCI = mtdQA = mtdQE = mtdQO = mtdQM = 0;
for (int i = 0; i < cform.dgvCostView.Rows.Count; ++i)
{
switch ((string)cform.dgvCostView.Rows[i].Cells[(int)Capital.fund].Value)
{
case "CI":
ytdCI += Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.ytd].Value);
mtdCI += Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.mtd].Value);
break;
case "QA":
ytdQA += Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.ytd].Value);
mtdQA += Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.mtd].Value);
break;
case "QE":
ytdQE += Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.ytd].Value);
mtdQE += Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.mtd].Value);
break;
case "QO":
ytdQO += Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.ytd].Value);
mtdQO += Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.mtd].Value);
break;
case "QM":
ytdQM += Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.ytd].Value);
mtdQM += Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.mtd].Value);
break;
}
}
//Display totals if MTD not 0
int nRowIndex = 0;
if (mtdCI != 0 || ytdCI != 0)
{
nRowIndex = cform.dgvCostView.Rows.Count;
cform.dgvCostView.Rows.Add("CI", "CAXTON INTL", mtdCI, ytdCI, "CI");
cform.dgvCostView.Rows[nRowIndex].DefaultCellStyle.BackColor = Color.Beige;
}
if (mtdQA != 0 || ytdQA != 0)
{
nRowIndex = cform.dgvCostView.Rows.Count;
cform.dgvCostView.Rows.Add("QA", "QA-TREASURY", mtdQA, ytdQA, "QA");
cform.dgvCostView.Rows[nRowIndex].DefaultCellStyle.BackColor = Color.Beige;
}
if (mtdQE != 0 || ytdQE != 0)
{
nRowIndex = cform.dgvCostView.Rows.Count;
cform.dgvCostView.Rows.Add("QE", "QE-TREASURY", mtdQE, ytdQE, "QE");
cform.dgvCostView.Rows[nRowIndex].DefaultCellStyle.BackColor = Color.Beige;
}
if (mtdQO != 0 || ytdQO != 0)
{
nRowIndex = cform.dgvCostView.Rows.Count;
cform.dgvCostView.Rows.Add("QO", "QO-TREASURY", mtdQO, ytdQO, "QO");
cform.dgvCostView.Rows[nRowIndex].DefaultCellStyle.BackColor = Color.Beige;
}
if (mtdQM != 0 || ytdQM != 0)
{
nRowIndex = cform.dgvCostView.Rows.Count;
cform.dgvCostView.Rows.Add("QM", "QM-TREASURY", mtdQM, ytdQM, "QM");
cform.dgvCostView.Rows[nRowIndex].DefaultCellStyle.BackColor = Color.Beige;
}
}
// close
dataReader.Close();
}
public void EnrichFinalFeed(CapitalFrm cform, string sent_unid, string fund) //Add fund level totals with updated amounts
{
double tAmount;
string tTpr;
tAmount = 0;
Connection connection = new Connection();
GetSQL sql = new GetSQL();
OdbcDataReader dataReader = connection.RunQuery(sql.Lookup_Sent_Info(sent_unid));
//If fund matches update amount for items beign sent
for (int i = 0; i < cform.dgvFinalView.Rows.Count; ++i)
{
if (fund == (string)cform.dgvFinalView.Rows[i].Cells[(int)Capital.fFund].Value)
{
if ((string)cform.dgvFinalView.Rows[i].Cells[(int)Capital.fTpr].Value == "PAY")
{
tAmount = tAmount - Convert.ToDouble(cform.dgvFinalView.Rows[i].Cells[(int)Capital.fTamount].Value);
}
else
{
tAmount = tAmount + Convert.ToDouble(cform.dgvFinalView.Rows[i].Cells[(int)Capital.fTamount].Value);
}
}
}
//Update direction of TPR
if (tAmount > 0)
{
tTpr = "PAY";
}
else
{
tTpr = "RECEIVE";
}
int nRowIndex = 0;
//loop through and update amounts
//apply to feed
if (dataReader.HasRows)
{
nRowIndex = cform.dgvFinalView.Rows.Count;
cform.dgvFinalView.Rows.Add(
(string)dataReader["TYPOLOGY"], // Typology
(string)dataReader["PORTFOLIO"], // Portfolio
(string)dataReader["CPTY"], // CPTY
(string)dataReader["INST"], // INST
(string)dataReader["FX"], // FX
tTpr, // TPR
((DateTime)dataReader["TDATE"]).ToString("MM/dd/yyyy"), // TDATE
Math.Abs(tAmount), // TAMOUNT
(string)dataReader["TFX"], // TFX
dataReader["TTRANS"].ToString(), // Ttrans
(string)dataReader["TCOMMENT"], // TCOMMENT
(string)dataReader["STRATEGY"], // STRATEGY
dataReader["BRKTYPE"].ToString(), // BRKTYPE
(string)dataReader["BRKSRCE"], // BRKSRCE
(string)dataReader["BRKDEST"], // BRKDEST
(string)dataReader["UNIQUE_ID"], // UNIQUE_ID
(string)dataReader["FUND"], // FUND
(string)dataReader["ACCT_NUM"] // ACCT_NUM
);
cform.dgvFinalView.Rows[nRowIndex].DefaultCellStyle.BackColor = Color.Beige;
}
// close
dataReader.Close();
}
public void CreateFinalFeedView(CapitalFrm cform) //Display DGV on FINAL Feed tab
{
//Clear
cform.dgvFinalView.Rows.Clear();
cform.dgvFinalView.Refresh();
//Variables
string sent_unid;
double sent_amt;
bool fundCI, fundQA, fundQE, fundQO, fundQM;
fundCI = fundQA = fundQE = fundQO = fundQM = false;
Connection connection = new Connection();
GetSQL sql = new GetSQL();
//loop through feed view and use merge to insert if unmatched
for (int i = 0; i < cform.dgvFeedView.Rows.Count; ++i)
{
//Construct Unid
sent_unid = (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.portfolio].Value + ":" + cform.dateCB.Text.ToUpper() + " CAP USAGE";
if (sent_unid.Length >= 31)
{
sent_unid = sent_unid.Substring(0, 31);
}
OdbcDataReader dataReader = connection.RunQuery(sql.Lookup_Sent_Info(sent_unid));
//Retrieve amount sent
if (dataReader.HasRows)
{
sent_amt = Convert.ToDouble(dataReader["SENT_AMT"]);
}
else
{
sent_amt = 0;
}
//If box checked and it has not been sent an amount
if ((treasArray.Contains((string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.portfolio].Value) == false) &&
((bool)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fSent_Box].Value == true) &&
sent_amt == 0
)
{
cform.dgvFinalView.Rows.Add(
(string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTypology].Value, // Typology
(string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fPortfolio].Value, // Portfolio
(string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fCpty].Value, // CPTY
(string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fInst].Value, // INST
(string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fFx].Value, // FX
(string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTpr].Value, // TPR
(string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTdate].Value, // TDATE
Convert.ToDouble(cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTamount].Value), // TAMOUNT
(string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTfx].Value, // TFX
(string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTtrans].Value, // Ttrans
(string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTcomment].Value, // TCOMMENT
(string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fStrategy].Value, // STRATEGY
(string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fBrktype].Value, // BRKTYPE
(string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fBrksrce].Value, // BRKSRCE
(string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fBrkdest].Value, // BRKDEST
(string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fUnique_Id].Value, // UNIQUE_ID
(string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fFund].Value, // FUND
(string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fAcct_Num].Value // ACCT_NUM
);
//set fund bools
switch ((string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fFund].Value)
{
case "CI":
fundCI = true;
break;
case "QA":
fundQA = true;
break;
case "QE":
fundQE = true;
break;
case "QO":
fundQO = true;
break;
case "QM":
fundQM = true;
break;
}
}
// close
dataReader.Close();
}
//Call enrich feed for treasury
if (fundCI == true)
{
sent_unid = "CAXTON INTL" + ":" + cform.dateCB.Text.ToUpper() + " CAP USAGE";
if (sent_unid.Length >= 31)
{
sent_unid = sent_unid.Substring(0, 31);
}
EnrichFinalFeed(cform, sent_unid, "CI");
}
if (fundQA == true)
{
sent_unid = "QA-TREASURY" + ":" + cform.dateCB.Text.ToUpper() + " CAP USAGE";
if (sent_unid.Length >= 31)
{
sent_unid = sent_unid.Substring(0, 31);
}
EnrichFinalFeed(cform, sent_unid, "QA");
}
if (fundQE == true)
{
sent_unid = "QE-TREASURY" + ":" + cform.dateCB.Text.ToUpper() + " CAP USAGE";
if (sent_unid.Length >= 31)
{
sent_unid = sent_unid.Substring(0, 31);
}
EnrichFinalFeed(cform, sent_unid, "QE");
}
if (fundQO == true)
{
sent_unid = "QO-TREASURY" + ":" + cform.dateCB.Text.ToUpper() + " CAP USAGE";
if (sent_unid.Length >= 31)
{
sent_unid = sent_unid.Substring(0, 31);
}
EnrichFinalFeed(cform, sent_unid, "QO");
}
if (fundQM == true)
{
sent_unid = "QM-TREASURY" + ":" + cform.dateCB.Text.ToUpper() + " CAP USAGE";
if (sent_unid.Length >= 31)
{
sent_unid = sent_unid.Substring(0, 31);
}
EnrichFinalFeed(cform, sent_unid, "QM");
}
//Select tab
cform.CapitalTabs.SelectTab(2);
}
public void DisplayFeedView(CapitalFrm cform) //Display DGV on Feed tab
{
//Clear
cform.dgvFeedView.Rows.Clear();
cform.dgvFeedView.Refresh();
Connection connection = new Connection();
GetSQL sql = new GetSQL();
//Variables
string cpty, tpr, strat, sent_unid, sent_date;
double amt, sent_amt;
bool send_bool;
//loop through capital view to create feed view
for (int i = 0; i < cform.dgvCostView.Rows.Count; ++i)
{
//If MTD has a value add to feed
if (Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.mtd].Value) != 0)
{
//----Set variables
switch ((string)cform.dgvCostView.Rows[i].Cells[(int)Capital.trader].Value)
{
case "CI":
cpty = "CXI";
strat = "CAP USAGE";
break;
case "QA":
case "QE":
case "QO":
case "QM":
cpty = "SCF";
strat = "CAP USAGE";
break;
default:
cpty = "SCF";
strat = "ADJUSTMENTS";
break;
}
sent_unid = (string)cform.dgvCostView.Rows[i].Cells[(int)Capital.portfolio].Value + ":" + cform.dateCB.Text.ToUpper() + " CAP USAGE";
if (sent_unid.Length >= 31)
{
sent_unid = sent_unid.Substring(0, 31);
}
OdbcDataReader dataReader = connection.RunQuery(sql.Lookup_Sent_Info(sent_unid));
if (dataReader.HasRows)
{
sent_date = ((DateTime)dataReader["Sent_ON"]).ToString("MM/dd/yyyy");
sent_amt = Convert.ToDouble(dataReader["SENT_AMT"]);
}
else
{
sent_date = null;
sent_amt = 0;
}
if (Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.mtd].Value) > 0)
{
tpr = "RECEIVE";
amt = Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.mtd].Value);
}
else
{
tpr = "PAY";
amt = (Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.mtd].Value) * -1);
}
if (sent_date == null)
{
send_bool = true;
}
else if ((treasArray.Contains((string)cform.dgvCostView.Rows[i].Cells[(int)Capital.portfolio].Value) == false) &&
(Math.Abs(amt) > Math.Abs(sent_amt)))
{
send_bool = true;
}
else
{
send_bool = false;
}
// Fill out feed DGV
cform.dgvFeedView.Rows.Add("IMPUTED INTEREST", //Typology
(string)cform.dgvCostView.Rows[i].Cells[(int)Capital.portfolio].Value, //Portfolio
cpty, //cpty
"IMPIT", //Inst
"USD", //FX
tpr, //TPR
DateTime.Now.ToString("MM/dd/yyyy"), //TDATE
amt, //AMOUNT
"USD", //TFX
"", //TTRANS
cform.dateCB.Text.ToUpper() + " CAP USAGE", //TCOMMENT
strat, //STRATEGY
"", //BRKTYPE
(string)cform.dgvCostView.Rows[i].Cells[(int)Capital.portfolio].Value, //BRKSRC
"ADJLAA", //BRKDEST
"COSTOFCAP_SCF_" + DateTime.Now.ToString("yymmddhhMMss") + i.ToString(), //UNID
(string)cform.dgvCostView.Rows[i].Cells[(int)Capital.fund].Value, //FUND
"CAXT", //ACCT
sent_date,
sent_amt,
send_bool
);
// close
dataReader.Close();
}
}
}
public void CreateExcel(CapitalFrm cform, string filepath, string filename)
{
object misValue = System.Reflection.Missing.Value;
Excel.Application xlexcel = new Excel.Application()
{
DisplayAlerts = false
};
//Add workbook
Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue);
// Loop through DGVS
for (int i = 1; i < 4; ++i)
{
// Copy DataGridView results to clipboard
switch (i)
{
case 1:
//cform.dgvCostView.RowHeadersVisible = false; //No hidden row
CopyAlltoClipboard(cform.dgvCostView);
break;
case 2:
CopyAlltoClipboard(cform.dgvFeedView);
break;
case 3:
CopyAlltoClipboard(cform.dgvFinalView);
break;
}
//select sheet
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(i);
// Paste clipboard results to worksheet range
xlWorkSheet.Activate();
Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
xlWorkSheet.get_Range("A2").Select();
ReleaseObject(xlWorkSheet);
ReleaseObject(CR);
}
// Save the excel file
xlWorkBook.SaveAs(Path.Combine(filepath, filename), Excel.XlFileFormat.xlWorkbookNormal,
misValue, misValue, misValue, misValue,
Excel.XlSaveAsAccessMode.xlExclusive,
misValue, misValue, misValue, misValue, misValue);
xlexcel.DisplayAlerts = true;
xlWorkBook.Close(true, misValue, misValue);
xlexcel.Quit();
ReleaseObject(xlWorkBook);
ReleaseObject(xlexcel);
// Clear Clipboard and DataGridView selection
Clipboard.Clear();
cform.dgvFinalView.ClearSelection();
cform.dgvFeedView.ClearSelection();
cform.dgvCostView.ClearSelection();
}