Esempio n. 1
0
        //private void GenerateTable(int colsCount, int rowsCount)
        //{
        //    try
        //    {
        //        //  timer1.Enabled = false;
        //        dt1.Clear();
        //        Session.Remove("LoanScheduleData");
        //        //Creat the Table and Add it to the Page
        //        System.Text.StringBuilder sb = new System.Text.StringBuilder();
        //        //Page.Form.Controls.Add(table);
        //        //DateTime startDate = txtCouponDate.SelectedDate.Value;        commented on 22/02
        //        DateTime startDate = txtAmortisationsStartDate.SelectedDate.Value;
        //        DateTime previousStartDate = startDate;
        //        string frequency = ddlAddCouponFrequency.SelectedValue.ToString();
        //        Decimal firstNationalRow = Convert.ToDecimal(txtNotional.Text);
        //        Decimal nationalRow = Convert.ToDecimal(txtNotional.Text);
        //        // Now iterate through the table and add your controls
        //        for (int i = 0; i < rowsCount; i++)
        //        {
        //            DataRow dr = dt1.NewRow();
        //            dr["ID"] = (i + 1).ToString();
        //            //dr["ID"] = (i).ToString();
        //            for (int j = 0; j < colsCount; j++)
        //            {
        //                TableCell cell = new TableCell();
        //                cell.CssClass = "tdBorder";
        //                RadTextBox tb = new RadTextBox();
        //                tb.Skin = "Web20";
        //                // Set a unique ID for each TextBox added
        //                tb.ID = "TextBoxRow_" + i + "Col_" + j;
        //                if (i == 0 && j == 0)
        //                {
        //                    tb.Text = startDate.ToShortDateString();
        //                    dr["StartDate"] = startDate.ToShortDateString();
        //                }
        //                else if (i > 0 && j == 0)
        //                {
        //                    switch (frequency.Trim())
        //                    {
        //                        case "Monthly":
        //                            previousStartDate = startDate;
        //                            startDate = startDate.AddMonths(1).Date;
        //                            tb.Text = startDate.ToShortDateString();
        //                            dr["StartDate"] = startDate.ToShortDateString();
        //                            break;
        //                        case "Quarterly":
        //                            previousStartDate = startDate;
        //                            startDate = startDate.AddMonths(3).Date;
        //                            tb.Text = startDate.ToShortDateString();
        //                            dr["StartDate"] = startDate.ToShortDateString();
        //                            break;
        //                        case "Semi-Annual":
        //                            previousStartDate = startDate;
        //                            startDate = startDate.AddMonths(6).Date;
        //                            tb.Text = startDate.ToShortDateString();
        //                            dr["StartDate"] = startDate.ToShortDateString();
        //                            break;
        //                        case "Annual":
        //                            previousStartDate = startDate;
        //                            startDate = startDate.AddYears(1).Date;
        //                            tb.Text = startDate.ToShortDateString();
        //                            dr["StartDate"] = startDate.ToShortDateString();
        //                            break;
        //                        default:
        //                            break;
        //                    }
        //                }
        //                if (i == 0 && j == 1)
        //                {
        //                    DateTime endDate;
        //                    switch (frequency.Trim())
        //                    {
        //                        case "Monthly":
        //                            endDate = startDate.AddMonths(1).Date;
        //                            tb.Text = endDate.ToShortDateString();
        //                            dr["EndDate"] = endDate.ToShortDateString();
        //                            break;
        //                        case "Quarterly":
        //                            endDate = startDate.AddMonths(3).Date;
        //                            tb.Text = endDate.ToShortDateString();
        //                            dr["EndDate"] = endDate.ToShortDateString();
        //                            break;
        //                        case "Semi-Annual":
        //                            endDate = startDate.AddMonths(6).Date;
        //                            tb.Text = endDate.ToShortDateString();
        //                            dr["EndDate"] = endDate.ToShortDateString();
        //                            break;
        //                        case "Annual":
        //                            endDate = startDate.AddYears(1).Date;
        //                            tb.Text = endDate.ToShortDateString();
        //                            dr["EndDate"] = endDate.ToShortDateString();
        //                            break;
        //                        default:
        //                            break;
        //                    }
        //                }
        //                else if (i > 0 && j == 1)
        //                {
        //                    DateTime endDate;
        //                    switch (frequency.Trim())
        //                    {
        //                        case "Monthly":
        //                            endDate = startDate.AddMonths(1).Date;
        //                            tb.Text = endDate.ToShortDateString();
        //                            dr["EndDate"] = endDate.ToShortDateString();
        //                            break;
        //                        case "Quarterly":
        //                            endDate = startDate.AddMonths(3).Date;
        //                            tb.Text = endDate.ToShortDateString();
        //                            dr["EndDate"] = endDate.ToShortDateString();
        //                            break;
        //                        case "Semi-Annual":
        //                            endDate = startDate.AddMonths(6).Date;
        //                            tb.Text = endDate.ToShortDateString();
        //                            dr["EndDate"] = endDate.ToShortDateString();
        //                            break;
        //                        case "Annual":
        //                            endDate = startDate.AddYears(1).Date;
        //                            tb.Text = endDate.ToShortDateString();
        //                            dr["EndDate"] = endDate.ToShortDateString();
        //                            break;
        //                        default:
        //                            break;
        //                    }
        //                }
        //                if (j == 2)
        //                {
        //                    DateTime couponDT = AddBusinessDays(DateTime.Now, 10);
        //                    if (txtCouponDate.SelectedDate.ToString() != "")
        //                    {
        //                        couponDT = Convert.ToDateTime(txtCouponDate.SelectedDate.Value);
        //                    }
        //                    DateTime endDt = Convert.ToDateTime(dr["EndDate"]);
        //                    decimal days = (endDt - couponDT).Days;
        //                    decimal totalDays = 365;
        //                    decimal coupFreqDays = days / totalDays;
        //                    if (days > 0)
        //                        dr["CoupFrac"] = Convert.ToDecimal(coupFreqDays).ToString("0.00");
        //                    else
        //                        dr["CoupFrac"] = Convert.ToDecimal(0).ToString("0.00");
        //                }
        //                if (j == 3)
        //                {
        //                    tb.Text = txtNotional.Text;
        //                    if (i == 0)
        //                    {
        //                        firstNationalRow = Convert.ToDecimal(txtNotional.Text);
        //                        nationalRow = firstNationalRow;
        //                    }
        //                    else
        //                    {
        //                        //  Convert.ToDecimal(Convert.ToDecimal(dt1.Rows[i - 1]["Notation"]) - Convert.ToDecimal(dt1.Rows[i - 1]["Amortisation"])).ToString("N");
        //                        nationalRow = Convert.ToDecimal(Convert.ToDecimal(dt1.Rows[i - 1]["Notation"]) - Convert.ToDecimal(dt1.Rows[i - 1]["Amortisation"]));
        //                    }
        //                    dr["Notation"] = nationalRow.ToString("N");
        //                }
        //                if (j == 4)
        //                {
        //                    //if (i < 1)
        //                    //    dr["Amortisation"] = 0.00;
        //                    //else
        //                    DateTime dt = Convert.ToDateTime(dr["StartDate"]);
        //                    DateTime couponDT = AddBusinessDays(DateTime.Now, 10);
        //                    if (txtCouponDate.SelectedDate.ToString() != "")
        //                    {
        //                        couponDT = Convert.ToDateTime(txtCouponDate.SelectedDate.Value);
        //                    }
        //                    if (txtCouponDate.SelectedDate != null && dt >= couponDT) // change on 09-04
        //                    {
        //                        //  int activeCoupons = Convert.ToInt16(txtActiveCoupon.Text);             //where to take this value
        //                        if (firstAmortizingRow == false)
        //                            activeCoupons = rowsCount - i;
        //                        Decimal amortisation = Convert.ToDecimal(firstNationalRow / activeCoupons);
        //                        dr["Amortisation"] = Convert.ToDecimal(amortisation).ToString("0.00");
        //                        firstAmortizingRow = true;
        //                    }
        //                    else
        //                        dr["Amortisation"] = "0.00";
        //                }
        //                if (j == 5)
        //                {
        //                    Decimal factorRow = Convert.ToDecimal(nationalRow / firstNationalRow);
        //                    tb.Text = factorRow.ToString();
        //                    dr["Factor"] = factorRow;
        //                }
        //                if (j == 6)
        //                {
        //                    dr["Spread"] = Convert.ToDecimal(txtBoxAddMargin.Text);
        //                }
        //                if (j == 7)
        //                {
        //                    dr["CouponPaymentDate"] = Convert.ToDateTime(dr["EndDate"]);
        //                }
        //                if (j == 8)
        //                {
        //                    if (ddlAddCurrency.SelectedValue.ToLower() == "eur")
        //                    {
        //                        EURCurvesBL eurCurveBL = new EURCurvesBL();
        //                        List<EURCurve> eurCurve = eurCurveBL.GetEURCurve();
        //                        Dictionary<double, double> rateVals = new Dictionary<double, double>();
        //                        foreach (var item in eurCurve)
        //                        {
        //                            rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
        //                        }
        //                        var scaler = new SplineInterpolator(rateVals);
        //                        var y = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
        //                        dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(txtBoxAddSigningDate.SelectedDate)).Days)) / 365));
        //                    }
        //                    else if (ddlAddCurrency.SelectedValue.ToLower() == "usd")
        //                    {
        //                        USDCurveBL usdCurveBL = new USDCurveBL();
        //                        List<USDCurve> usdCurve = usdCurveBL.GetUSCurve();
        //                        Dictionary<double, double> rateVals = new Dictionary<double, double>();
        //                        foreach (var item in usdCurve)
        //                        {
        //                            rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
        //                        }
        //                        var scaler = new SplineInterpolator(rateVals);
        //                        var y = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
        //                        dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(txtBoxAddSigningDate.SelectedDate)).Days)) / 365));
        //                    }
        //                    //  SplineInterpolator interPolation = new  SplineInterpolator();
        //                    // dr["RiskFreeDP1"]=  ;//EXP(-(interpolate(schedule.endDate(i),EurDB.Rates,'EurDB.Rates')/100*(schedule.endDate(i)-loan.settlementDate)/365));
        //                }
        //                if (j == 9)
        //                {
        //                    if (dr["EndDate"].ToString() != string.Empty)
        //                    {
        //                        dr["RiskFreeDP2"] = Convert.ToDouble(dr["RiskFreeDP1"]) / Convert.ToDouble((Math.Pow(Convert.ToDouble((1 + (Convert.ToDouble(txtBoxAddMargin.Text) / 20000))), ((2 * (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(txtBoxAddSigningDate.SelectedDate)).Days) / 360))));
        //                    }
        //                }
        //                if (j == 10)
        //                {
        //                    if (i == 0)
        //                    {
        //                        dr["FloatingRate"] = 0.5;
        //                    }
        //                    else
        //                    {
        //                        double d = Convert.ToDouble(dt1.Rows[i - 1]["RiskFreeDP1"]) / Convert.ToDouble(dr["RiskFreeDP1"]) - 1 / ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dt1.Rows[i - 1]["EndDate"])).Days) / 360;
        //                        dr["FloatingRate"] = Convert.ToDouble(d) / 100;
        //                    }
        //                }
        //                if (j == 11)
        //                {
        //                    dr["AllInRate"] = Convert.ToDouble(Convert.ToDouble(dr["FloatingRate"]) + Convert.ToDouble(txtBoxAddMargin.Text)) / 100;
        //                }
        //                if (j == 12)
        //                {
        //                    dr["Interest"] = ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days / 360) * Convert.ToDouble(dr["AllInRate"]) * Convert.ToDouble(dr["Notation"]);
        //                }
        //                if (j == 13)
        //                {
        //                    dr["Days"] = (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days;
        //                }
        //                if (j == 14)
        //                {
        //                    dr["AmortisationInt"] = Convert.ToDouble(dr["Amortisation"]) + Convert.ToDouble(dr["Interest"]);
        //                }
        //                // Add the control to the TableCell
        //                cell.Controls.Add(tb);
        //                // Add the TableCell to the TableRow
        //            }
        //            dt1.Rows.Add(dr);
        //            // Add the TableRow to the Table
        //            //  table1.Rows.Add(row);
        //        }
        //        //table1.Visible = true;
        //        Session.Add("LoanScheduleData", dt1);
        //        pnlAmortizing.Visible = true;
        //        grdAmortizing.Visible = true;
        //        btnCalculatSchedule.Visible = true;
        //        grdAmortizing.DataSource = dt1;
        //        grdAmortizing.DataBind();
        //        //  timer1.Enabled = true;
        //    }
        //    catch (Exception)
        //    {
        //    }
        //}
        private void GenerateTable(int colsCount, int rowsCount)
        {
            try
            {
                //  timer1.Enabled = false;
                dt1.Clear();
                Session.Remove("LoanScheduleData");
                //Creat the Table and Add it to the Page
                System.Text.StringBuilder sb = new System.Text.StringBuilder();

                //Page.Form.Controls.Add(table);
                //DateTime startDate = txtCouponDate.SelectedDate.Value;        commented on 22/02
                DateTime startDate = txtAmortisationsStartDate.SelectedDate.Value;

                DateTime previousStartDate = startDate;
                string frequency = ddlAddCouponFrequency.SelectedValue.ToString();
                Decimal firstNotionalRow = Convert.ToDecimal(txtNotional.Text);
                Decimal notionalRow = Convert.ToDecimal(txtNotional.Text);

                // Now iterate through the table and add your controls
                //for (int i = 0; i < rowsCount; i++)
                //{

                //    DataRow dr = dt1.NewRow();
                //    dr["ID"] = (i + 1).ToString();
                //    //dr["ID"] = (i).ToString();

                //    dt1.Rows.Add(dr);
                //    // Add the TableRow to the Table
                //    //  table1.Rows.Add(row);
                //}
                DateTime settlementDate;
                int i = 0;
                DateTime endDate;

                settlementDate = AddBusinessDays(DateTime.Now, 10); //changed 26-04 asked by avr
                endDate = settlementDate;
                DateTime maturityDate = txtBoxAddMaturityDate.SelectedDate.Value;
                Decimal notional = Convert.ToDecimal(txtNotional.Text);
                DateTime couponDate = txtCouponDate.SelectedDate.Value;
                string ccy = ddlAddCurrency.SelectedValue.ToString();
                DateTime tradedDate = txtBoxAddSigningDate.SelectedDate.Value;
                decimal spread = Convert.ToDecimal(txtBoxAddMargin.Text);
                int totalNoOfRows = 0;
                while (endDate < txtBoxAddMaturityDate.SelectedDate)
                {

                    System.Numeric.DayCountBasis d = System.Numeric.DayCountBasis.Actual365;
                    System.Numeric.Frequency f = new System.Numeric.Frequency();
                    if (frequency == "Annual")
                    {
                        f = System.Numeric.Frequency.Annual;
                    }
                    if (frequency == "Semi-Annual")
                    {
                        f = System.Numeric.Frequency.SemiAnnual;
                    }
                    if (frequency == "Quarterly")
                    {
                        f = System.Numeric.Frequency.Quarterly;
                    }

                    endDate = System.Numeric.Financial.CoupNCD(endDate, maturityDate, f, d); // end date is the last coupon settlement date
                    totalNoOfRows++;
                    //DateTime newEndDate = PreviousBusinessDay(endDate);
                    //endDate = newEndDate;

                }
                endDate = AddBusinessDays(txtBoxAddSigningDate.SelectedDate.Value, 10);
                while (endDate < txtBoxAddMaturityDate.SelectedDate)
                {

                    DataRow dr = dt1.NewRow();
                    dr["ID"] = (i + 1).ToString();

                    // for each column in schedule table
                    for (int j = 0; j < colsCount; j++)
                    {
                        if (i == 0 && j == 0)
                        {
                            dr["StartDate"] = settlementDate.ToShortDateString();
                        }
                        else if (i > 0 && j == 0)
                        {
                            // start date always equals the end date of previous coupon
                            dr["StartDate"] = endDate.ToShortDateString();
                        }
                        if (i == 0 && j == 1)
                        {
                            //DateTime endDate;
                            //switch (frequency.Trim())
                            //{
                            //    case "Monthly":
                            //        endDate = startDate.AddMonths(1).Date;
                            //        tb.Text = endDate.ToShortDateString();
                            //        dr["EndDate"] = endDate.ToShortDateString();
                            //        break;
                            //    case "Quarterly":
                            //        endDate = startDate.AddMonths(3).Date;
                            //        tb.Text = endDate.ToShortDateString();
                            //        dr["EndDate"] = endDate.ToShortDateString();
                            //        break;
                            //    case "Semi-Annual":
                            //        endDate = startDate.AddMonths(6).Date;
                            //        tb.Text = endDate.ToShortDateString();
                            //        dr["EndDate"] = endDate.ToShortDateString();
                            //        break;
                            //    case "Annual":
                            //        endDate = startDate.AddYears(1).Date;
                            //        tb.Text = endDate.ToShortDateString();
                            //        dr["EndDate"] = endDate.ToShortDateString();
                            //        break;
                            //    default:
                            //        break;
                            //}
                            System.Numeric.DayCountBasis d = System.Numeric.DayCountBasis.Actual365;
                            System.Numeric.Frequency f = new System.Numeric.Frequency();
                            if (frequency == "Annual")
                            {
                                f = System.Numeric.Frequency.Annual;
                            }
                            if (frequency == "Semi-Annual")
                            {
                                f = System.Numeric.Frequency.SemiAnnual;
                            }
                            if (frequency == "Quarterly")
                            {
                                f = System.Numeric.Frequency.Quarterly;
                            }

                            endDate = System.Numeric.Financial.CoupNCD(endDate, maturityDate, f, d); // end date is the last coupon settlement date
                            DateTime newEndDate = PreviousBusinessDay(endDate);
                            dr["EndDate"] = newEndDate.ToShortDateString();
                        }
                        else if (i > 0 && j == 1)
                        {
                            System.Numeric.DayCountBasis d = System.Numeric.DayCountBasis.Actual365;
                            System.Numeric.Frequency f = new System.Numeric.Frequency();
                            if (frequency == "Annual")
                            {
                                f = System.Numeric.Frequency.Annual;
                            }
                            if (frequency == "Semi-Annual")
                            {
                                f = System.Numeric.Frequency.SemiAnnual;
                            }
                            if (frequency == "Quarterly")
                            {
                                f = System.Numeric.Frequency.Quarterly;
                            }

                            endDate = System.Numeric.Financial.CoupNCD(endDate, maturityDate, f, d); // end date is the last coupon settlement date
                            DateTime newEndDate = PreviousBusinessDay(endDate);
                            dr["EndDate"] = newEndDate.ToShortDateString();

                        }

                        // coup frac calc
                        if (j == 2)
                        {

                            if (settlementDate != null) // removed maturityDate
                            {

                                decimal days = (endDate - settlementDate).Days;
                                decimal totalDays = 365;
                                decimal coupFreqDays = days / totalDays;
                                if (days > 0)
                                    dr["CoupFrac"] = Convert.ToDecimal(coupFreqDays).ToString("0.00");
                                else
                                    dr["CoupFrac"] = Convert.ToDecimal(0).ToString("0.00");
                            }
                        }

                        if (j == 3)
                        {
                            if (i == 0)
                            {
                                firstNotionalRow = Convert.ToDecimal(notional);
                                notionalRow = firstNotionalRow;
                            }
                            else
                            {
                                notionalRow = Convert.ToDecimal(Convert.ToDecimal(dt1.Rows[i - 1]["Notation"]) - Convert.ToDecimal(dt1.Rows[i - 1]["Amortisation"]));
                            }
                            dr["Notation"] = notionalRow.ToString("N");
                        }

                        if (j == 4)
                        {

                            //DateTime dt = Convert.ToDateTime(dr["StartDate"]);
                            //if (couponDate != null && dt >= couponDate)
                            //{
                            //    if (firstAmortizingRow == false)
                            //        activeCoupons = rowsCount - i;
                            //    Decimal amortisation = Convert.ToDecimal(firstNotionalRow / activeCoupons);
                            //    dr["Amortisation"] = Convert.ToDecimal(amortisation).ToString("0.00");
                            //    firstAmortizingRow = true;
                            //}
                            //else
                            //    dr["Amortisation"] = "0.00";
                            dr["Amortisation"] = Convert.ToDecimal(Convert.ToDecimal(txtNotional.Text) / totalNoOfRows).ToString("0.00");
                        }

                        if (j == 5)
                        {
                            Decimal factorRow = Convert.ToDecimal(notionalRow / firstNotionalRow);
                            dr["Factor"] = factorRow;
                        }

                        if (j == 6)
                        {
                            dr["Spread"] = Convert.ToDecimal(spread);
                        }

                        if (j == 7)
                        {
                            dr["CouponPaymentDate"] = Convert.ToDateTime(dr["EndDate"]);
                        }

                        if (j == 8)
                        {
                            if (ccy.ToLower() == "eur")
                            {
                                EURCurvesBL eurCurveBL = new EURCurvesBL();
                                List<EURCurve> eurCurve = eurCurveBL.GetEURCurve();
                                Dictionary<double, double> rateVals = new Dictionary<double, double>();
                                foreach (var item in eurCurve)
                                {
                                    rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
                                }
                                var scaler = new SplineInterpolator(rateVals);
                                var y = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
                                dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days)) / 365));
                            }
                            else if (ccy.ToLower() == "usd")
                            {
                                USDCurveBL usdCurveBL = new USDCurveBL();
                                List<USDCurve> usdCurve = usdCurveBL.GetUSCurve();
                                Dictionary<double, double> rateVals = new Dictionary<double, double>();
                                foreach (var item in usdCurve)
                                {
                                    rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
                                }
                                var scaler = new SplineInterpolator(rateVals);
                                var y = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
                                dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days)) / 365));
                            }
                            //  SplineInterpolator interPolation = new  SplineInterpolator();
                            // dr["RiskFreeDP1"]=  ;//EXP(-(interpolate(schedule.endDate(i),EurDB.Rates,'EurDB.Rates')/100*(schedule.endDate(i)-loan.settlementDate)/365));
                        }
                        if (j == 9)
                        {
                            if (dr["EndDate"].ToString() != string.Empty)
                            {
                                dr["RiskFreeDP2"] = Convert.ToDouble(dr["RiskFreeDP1"]) / Convert.ToDouble((Math.Pow(Convert.ToDouble((1 + (spread / 20000))), ((2 * (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days) / 360))));
                            }
                        }

                        if (j == 10)
                        {
                            if (i == 0)
                            {
                                dr["FloatingRate"] = 0.5;
                            }
                            else
                            {
                                double d = Convert.ToDouble(dt1.Rows[i - 1]["RiskFreeDP1"]) / Convert.ToDouble(dr["RiskFreeDP1"]) - 1 / ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dt1.Rows[i - 1]["EndDate"])).Days) / 360;
                                dr["FloatingRate"] = Convert.ToDouble(d) / 100;
                            }
                        }
                        if (j == 11)
                        {
                            dr["AllInRate"] = Convert.ToDouble(Convert.ToDouble(dr["FloatingRate"]) + Convert.ToDouble(spread)) / 100;
                        }
                        if (j == 12)
                        {
                            dr["Interest"] = ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days / 360) * Convert.ToDouble(dr["AllInRate"]) * Convert.ToDouble(dr["Notation"]);
                        }
                        if (j == 13)
                        {
                            dr["Days"] = (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days;
                        }
                        if (j == 14)
                        {
                            dr["AmortisationInt"] = Convert.ToDouble(dr["Amortisation"]) + Convert.ToDouble(dr["Interest"]);
                        }

                    }
                    if (Convert.ToDecimal(dr[4]) >= 0)
                    {
                        dt1.Rows.Add(dr);
                        i++;
                    }
                }

                //for (int x = 0; x < dt1.Rows.Count; x++)
                //{
                //    dt1.Rows[x][5] = Convert.ToDecimal(Convert.ToDecimal(txtNotional.Text) / (dt1.Rows.Count)).ToString("0.00");
                //    if (x == 0)
                //    {
                //        firstNotionalRow = Convert.ToDecimal(notional);
                //        notionalRow = firstNotionalRow;
                //    }
                //    else
                //    {
                //        notionalRow = Math.Round(Convert.ToDecimal(Math.Round(Convert.ToDecimal(dt1.Rows[x - 1]["Notation"]), 2) - Convert.ToDecimal(dt1.Rows[x - 1]["Amortisation"])), 2);
                //    }
                //    dt1.Rows[x]["Notation"] = notionalRow.ToString("N");

                //}

                //table1.Visible = true;
                Session.Add("LoanScheduleData", dt1);
                pnlAmortizing.Visible = true;
                grdAmortizing.Visible = true;
                btnCalculatSchedule.Visible = true;
                grdAmortizing.DataSource = dt1;
                grdAmortizing.DataBind();
                //  timer1.Enabled = true;
            }
            catch (Exception)
            {
            }
        }
        //public DataTable GenerateTable(int colsCount, int rowsCount, DateTime startDate, string frequency, string notional, DateTime maturityDate, DateTime couponDate, Decimal spread, string ccy, DateTime tradedDate)
        //{
        //    DataTable dtSchedule = new DataTable();

        //    try
        //    {
        //        dtSchedule = new DataTable();
        //        dtSchedule.Columns.Add("ID");
        //        dtSchedule.Columns.Add("StartDate");
        //        dtSchedule.Columns.Add("EndDate");
        //        dtSchedule.Columns.Add("CoupFrac");
        //        dtSchedule.Columns.Add("Notation");
        //        dtSchedule.Columns.Add("Amortisation");
        //        dtSchedule.Columns.Add("Factor");
        //        dtSchedule.Columns.Add("Spread");
        //        dtSchedule.Columns.Add("CouponPaymentDate");
        //        dtSchedule.Columns.Add("RiskFreeDP1");
        //        dtSchedule.Columns.Add("RiskFreeDP2");
        //        dtSchedule.Columns.Add("FloatingRate");
        //        dtSchedule.Columns.Add("AllInRate");
        //        dtSchedule.Columns.Add("Interest");
        //        dtSchedule.Columns.Add("Days");
        //        dtSchedule.Columns.Add("AmortisationInt");
        //        System.Text.StringBuilder sb = new System.Text.StringBuilder();



        //        DateTime previousStartDate = startDate;

        //        Decimal firstNationalRow = Convert.ToDecimal(notional);
        //        Decimal nationalRow = Convert.ToDecimal(notional);
        //        bool firstAmortizingRow = false;
        //        int activeCoupons = 0;

        //        // change this condition to while currCouponDate <= maturityDate

        //        for (int i = 0; i < rowsCount; i++)
        //        {

        //            DataRow dr = dtSchedule.NewRow();
        //            dr["ID"] = (i + 1).ToString();


        //            for (int j = 0; j < colsCount; j++)
        //            {

        //                if (i == 0 && j == 0)
        //                {
        //                    //tb.Text = startDate.ToShortDateString();

        //                    dr["StartDate"] = startDate.ToShortDateString();
        //                }
        //                else if (i > 0 && j == 0)
        //                {
        //                    switch (frequency.Trim())
        //                    {
        //                        case "Monthly":
        //                            previousStartDate = startDate;
        //                            startDate = startDate.AddMonths(1).Date;

        //                            //tb.Text = startDate.ToShortDateString();
        //                            dr["StartDate"] = startDate.ToShortDateString();
        //                            break;
        //                        case "Quarterly":
        //                            previousStartDate = startDate;
        //                            startDate = startDate.AddMonths(3).Date;

        //                            dr["StartDate"] = startDate.ToShortDateString();
        //                            break;
        //                        case "Semi-Annual":
        //                            previousStartDate = startDate;
        //                            startDate = startDate.AddMonths(6).Date;
        //                            dr["StartDate"] = startDate.ToShortDateString();
        //                            break;
        //                        case "Annual":
        //                            previousStartDate = startDate;
        //                            startDate = startDate.AddYears(1).Date;
        //                            dr["StartDate"] = startDate.ToShortDateString();
        //                            break;
        //                        default:
        //                            break;
        //                    }

        //                }
        //                if (i == 0 && j == 1)
        //                {
        //                    DateTime endDate;
        //                    switch (frequency.Trim())
        //                    {
        //                        case "Monthly":
        //                            endDate = startDate.AddMonths(1).Date;
        //                            dr["EndDate"] = endDate.ToShortDateString();
        //                            break;
        //                        case "Quarterly":
        //                            endDate = startDate.AddMonths(3).Date;
        //                            dr["EndDate"] = endDate.ToShortDateString();
        //                            break;
        //                        case "Semi-Annual":
        //                            endDate = startDate.AddMonths(6).Date;
        //                            dr["EndDate"] = endDate.ToShortDateString();
        //                            break;
        //                        case "Annual":
        //                            endDate = startDate.AddYears(1).Date;
        //                            dr["EndDate"] = endDate.ToShortDateString();
        //                            break;
        //                        default:
        //                            break;
        //                    }
        //                }
        //                else if (i > 0 && j == 1)
        //                {
        //                    DateTime endDate;
        //                    // Compute next coupon date using CoupNCD and correct for weekends
        //                    // f define frequency
        //                    // so now end dates should be generated for the total number of coupons
        //                    CalculatedDates cal = new CalculatedDates();
        //                    System.Numeric.DayCountBasis d = System.Numeric.DayCountBasis.Actual365;
        //                    prevCouponDate = currCouponDate;
        //                    currentCouponDate = System.Numeric.Financial.CoupNCD(currentCouponDate, maturityDate, f, d);
        //                    //DateTime previousBusinessDay = PreviousBusinessDay(currentCouponDate);
        //                    //cal.CalculatedDate = previousBusinessDay;
        //                    dr["EndDate"] = currentCouponDate.ToShortDateString();

        //                    //switch (frequency.Trim())
        //                    //{
        //                    //    case "Monthly":
        //                    //        endDate = startDate.AddMonths(1).Date;
        //                    //        dr["EndDate"] = endDate.ToShortDateString();
        //                    //        break;
        //                    //    case "Quarterly":
        //                    //        endDate = startDate.AddMonths(3).Date;
        //                    //        dr["EndDate"] = endDate.ToShortDateString();
        //                    //        break;
        //                    //    case "Semi-Annual":
        //                    //        endDate = startDate.AddMonths(6).Date;
        //                    //        dr["EndDate"] = endDate.ToShortDateString();
        //                    //        break;
        //                    //    case "Annual":
        //                    //        endDate = startDate.AddYears(1).Date;
        //                    //        dr["EndDate"] = endDate.ToShortDateString();
        //                    //        break;
        //                    //    default:
        //                    //        break;
        //                    //}
        //                }
        //                if (j == 2)
        //                {

        //                    if (maturityDate != null)
        //                    {
        //                        DateTime maturityDt = Convert.ToDateTime(maturityDate);
        //                        DateTime endDt = Convert.ToDateTime(dr["EndDate"]);
        //                        decimal days = (endDt - maturityDt).Days;
        //                        decimal totalDays = 365;
        //                        decimal coupFreqDays = days / totalDays;
        //                        if (days > 0)
        //                            dr["CoupFrac"] = Convert.ToDecimal(coupFreqDays).ToString("0.00");
        //                        else
        //                            dr["CoupFrac"] = Convert.ToDecimal(0).ToString("0.00");
        //                    }
        //                }
        //                if (j == 3)
        //                {
        //                    if (i == 0)
        //                    {
        //                        firstNationalRow = Convert.ToDecimal(notional);
        //                        nationalRow = firstNationalRow;
        //                    }
        //                    else
        //                    {
        //                        nationalRow = Convert.ToDecimal(Convert.ToDecimal(dtSchedule.Rows[i - 1]["Notation"]) - Convert.ToDecimal(dtSchedule.Rows[i - 1]["Amortisation"]));
        //                    }
        //                    dr["Notation"] = nationalRow.ToString("N");
        //                }
        //                if (j == 4)
        //                {

        //                    DateTime dt = Convert.ToDateTime(dr["StartDate"]);
        //                    if (couponDate != null && dt >= couponDate)
        //                    {
        //                        if (firstAmortizingRow == false)
        //                            activeCoupons = rowsCount - i;
        //                        Decimal amortisation = Convert.ToDecimal(firstNationalRow / activeCoupons);
        //                        dr["Amortisation"] = Convert.ToDecimal(amortisation).ToString("0.00");
        //                        firstAmortizingRow = true;
        //                    }
        //                    else
        //                        dr["Amortisation"] = "0.00";
        //                }
        //                if (j == 5)
        //                {
        //                    Decimal factorRow = Convert.ToDecimal(nationalRow / firstNationalRow);
        //                    dr["Factor"] = factorRow;
        //                }
        //                if (j == 6)
        //                {
        //                    dr["Spread"] = Convert.ToDecimal(spread);
        //                }
        //                if (j == 7)
        //                {
        //                    dr["CouponPaymentDate"] = Convert.ToDateTime(dr["EndDate"]);
        //                }
        //                if (j == 8)
        //                {
        //                    if (ccy.ToLower() == "eur")
        //                    {
        //                        EURCurvesBL eurCurveBL = new EURCurvesBL();
        //                        List<EURCurve> eurCurve = eurCurveBL.GetEURCurve();
        //                        Dictionary<double, double> rateVals = new Dictionary<double, double>();
        //                        foreach (var item in eurCurve)
        //                        {
        //                            rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
        //                        }
        //                        var scaler = new SplineInterpolator(rateVals);
        //                        var y = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
        //                        dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days)) / 365));
        //                    }
        //                    else if (ccy.ToLower() == "usd")
        //                    {
        //                          USDCurveBL usdCurveBL = new USDCurveBL();
        //                          List<USDCurve> usdCurve = usdCurveBL.GetUSCurve();
        //                          Dictionary<double, double> rateVals = new Dictionary<double, double>();
        //                          foreach (var item in usdCurve)
        //                          {
        //                              rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
        //                          }
        //                          var scaler = new SplineInterpolator(rateVals);
        //                          var y = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
        //                          dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days)) / 365));
        //                    }
        //                    //  SplineInterpolator interPolation = new  SplineInterpolator();
        //                    // dr["RiskFreeDP1"]=  ;//EXP(-(interpolate(schedule.endDate(i),EurDB.Rates,'EurDB.Rates')/100*(schedule.endDate(i)-loan.settlementDate)/365));
        //                }
        //                if (j == 9)
        //                {
        //                    if (dr["EndDate"].ToString() != string.Empty)
        //                    {
        //                        dr["RiskFreeDP2"] = Convert.ToDouble(dr["RiskFreeDP1"]) / Convert.ToDouble((Math.Pow(Convert.ToDouble((1 + (spread / 20000))), ((2 * (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days) / 360))));
        //                    }
        //                }

        //                if (j == 10)
        //                {
        //                    if (i == 0)
        //                    {
        //                        dr["FloatingRate"] = 0.5;
        //                    }
        //                    else
        //                    {
        //                        double d = Convert.ToDouble(dtSchedule.Rows[i - 1]["RiskFreeDP1"]) / Convert.ToDouble(dr["RiskFreeDP1"]) - 1 / ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dtSchedule.Rows[i - 1]["EndDate"])).Days) / 360;
        //                        dr["FloatingRate"] = Convert.ToDouble(d) / 100;
        //                    }
        //                }
        //                if (j == 11)
        //                {
        //                    dr["AllInRate"] = Convert.ToDouble(Convert.ToDouble(dr["FloatingRate"]) + Convert.ToDouble(spread)) / 100;
        //                }
        //                if (j == 12)
        //                {
        //                    dr["Interest"] = ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days / 360) * Convert.ToDouble(dr["AllInRate"]) * Convert.ToDouble(dr["Notation"]);
        //                }
        //                if (j == 13)
        //                {
        //                    dr["Days"] = (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days;
        //                }
        //                if (j == 14)
        //                {
        //                    dr["AmortisationInt"] = Convert.ToDouble(dr["Amortisation"]) + Convert.ToDouble(dr["Interest"]);
        //                }

        //            }
        //            dtSchedule.Rows.Add(dr);


        //        }
        //    }
        //    catch (Exception ex)
        //    {


        //    }
        //    return dtSchedule;
        //}


        public DataTable GenerateTable(int colsCount, int rowsCount, DateTime startDate, string frequency, string notional, DateTime maturityDate, DateTime couponDate, Decimal spread, string ccy, DateTime tradedDate, DateTime settlementDate)
        {
            DataTable dtSchedule = new DataTable();

            try
            {
                dtSchedule = new DataTable();
                dtSchedule.Columns.Add("ID");
                dtSchedule.Columns.Add("StartDate");
                dtSchedule.Columns.Add("EndDate");
                dtSchedule.Columns.Add("CoupFrac");
                dtSchedule.Columns.Add("Notation");
                dtSchedule.Columns.Add("Amortisation");
                dtSchedule.Columns.Add("Factor");
                dtSchedule.Columns.Add("Spread");
                dtSchedule.Columns.Add("CouponPaymentDate");
                dtSchedule.Columns.Add("RiskFreeDP1");
                dtSchedule.Columns.Add("RiskFreeDP2");
                dtSchedule.Columns.Add("FloatingRate");
                dtSchedule.Columns.Add("AllInRate");
                dtSchedule.Columns.Add("Interest");
                dtSchedule.Columns.Add("Days");
                dtSchedule.Columns.Add("AmortisationInt");
                System.Text.StringBuilder sb = new System.Text.StringBuilder();



                DateTime previousStartDate = startDate;

                Decimal firstNotionalRow = Convert.ToDecimal(notional);
                Decimal notionalRow = Convert.ToDecimal(notional);
                bool firstAmortizingRow = false;
                int activeCoupons = 0;

                // change this condition to while currCouponDate <= maturityDate
                int i = 0;
                DateTime endDate;
                endDate = settlementDate; // this should come from either ( compact, loan details or add new loan tab )

                int totalNoOfRows = 0;
                while (endDate < maturityDate)
                {

                    System.Numeric.DayCountBasis d = System.Numeric.DayCountBasis.Actual365;
                    System.Numeric.Frequency f = new System.Numeric.Frequency();
                    if (frequency == "Annual")
                    {
                        f = System.Numeric.Frequency.Annual;
                    }
                    if (frequency == "Semi-Annual")
                    {
                        f = System.Numeric.Frequency.SemiAnnual;
                    }
                    if (frequency == "Quarterly")
                    {
                        f = System.Numeric.Frequency.Quarterly;
                    }

                    endDate = System.Numeric.Financial.CoupNCD(endDate, maturityDate, f, d); // end date is the last coupon settlement date
                    totalNoOfRows++;
                    //DateTime newEndDate = PreviousBusinessDay(endDate);
                    //endDate = newEndDate;

                }
                endDate = settlementDate;
                while (endDate < maturityDate)
                {

                    DataRow dr = dtSchedule.NewRow();
                    dr["ID"] = (i + 1).ToString();

                    // for each column in schedule table			
                    for (int j = 0; j < colsCount; j++)
                    {
                        if (i == 0 && j == 0)
                        {
                            dr["StartDate"] = settlementDate.ToShortDateString();
                        }
                        else if (i > 0 && j == 0)
                        {
                            // start date always equals the end date of previous coupon
                            dr["StartDate"] = endDate.ToShortDateString();
                        }
                        if (i == 0 && j == 1)
                        {
                            //DateTime endDate;
                            //switch (frequency.Trim())
                            //{
                            //    case "Monthly":
                            //        endDate = startDate.AddMonths(1).Date;
                            //        tb.Text = endDate.ToShortDateString();
                            //        dr["EndDate"] = endDate.ToShortDateString();
                            //        break;
                            //    case "Quarterly":
                            //        endDate = startDate.AddMonths(3).Date;
                            //        tb.Text = endDate.ToShortDateString();
                            //        dr["EndDate"] = endDate.ToShortDateString();
                            //        break;
                            //    case "Semi-Annual":
                            //        endDate = startDate.AddMonths(6).Date;
                            //        tb.Text = endDate.ToShortDateString();
                            //        dr["EndDate"] = endDate.ToShortDateString();
                            //        break;
                            //    case "Annual":
                            //        endDate = startDate.AddYears(1).Date;
                            //        tb.Text = endDate.ToShortDateString();
                            //        dr["EndDate"] = endDate.ToShortDateString();
                            //        break;
                            //    default:
                            //        break;
                            //}
                            System.Numeric.DayCountBasis d = System.Numeric.DayCountBasis.Actual365;
                            System.Numeric.Frequency f = new System.Numeric.Frequency();
                            if (frequency == "Annual")
                            {
                                f = System.Numeric.Frequency.Annual;
                            }
                            if (frequency == "Semi-Annual")
                            {
                                f = System.Numeric.Frequency.SemiAnnual;
                            }
                            if (frequency == "Quarterly")
                            {
                                f = System.Numeric.Frequency.Quarterly;
                            }

                            endDate = System.Numeric.Financial.CoupNCD(endDate, maturityDate, f, d); // end date is the last coupon settlement date
                            DateTime newEndDate = PreviousBusinessDay(endDate);
                            dr["EndDate"] = newEndDate.ToShortDateString();
                        }
                        else if (i > 0 && j == 1)
                        {
                            System.Numeric.DayCountBasis d = System.Numeric.DayCountBasis.Actual365;
                            System.Numeric.Frequency f = new System.Numeric.Frequency();
                            if (frequency == "Annual")
                            {
                                f = System.Numeric.Frequency.Annual;
                            }
                            if (frequency == "Semi-Annual")
                            {
                                f = System.Numeric.Frequency.SemiAnnual;
                            }
                            if (frequency == "Quarterly")
                            {
                                f = System.Numeric.Frequency.Quarterly;
                            }

                            endDate = System.Numeric.Financial.CoupNCD(endDate, maturityDate, f, d); // end date is the last coupon settlement date
                            DateTime newEndDate = PreviousBusinessDay(endDate);
                            dr["EndDate"] = newEndDate.ToShortDateString();

                        }




                        // coup frac calc
                        if (j == 2)
                        {

                            if (settlementDate != null) // removed maturityDate
                            {

                                decimal days = (endDate - settlementDate).Days;
                                decimal totalDays = 365;
                                decimal coupFreqDays = days / totalDays;
                                if (days > 0)
                                    dr["CoupFrac"] = Convert.ToDecimal(coupFreqDays).ToString("0.00");
                                else
                                    dr["CoupFrac"] = Convert.ToDecimal(0).ToString("0.00");
                            }
                        }

                        if (j == 3)
                        {
                            if (i == 0)
                            {
                                firstNotionalRow = Convert.ToDecimal(notional);
                                notionalRow = firstNotionalRow;
                            }
                            else
                            {
                                notionalRow = Convert.ToDecimal(Convert.ToDecimal(dtSchedule.Rows[i - 1]["Notation"]) - Convert.ToDecimal(dtSchedule.Rows[i - 1]["Amortisation"]));
                            }
                            dr["Notation"] = notionalRow.ToString("N");
                        }

                        if (j == 4)
                        {

                            //DateTime dt = Convert.ToDateTime(dr["StartDate"]);
                            //if (couponDate != null && dt >= couponDate)
                            //{
                            //    if (firstAmortizingRow == false)
                            //        activeCoupons = rowsCount - i;
                            //    Decimal amortisation = Convert.ToDecimal(firstNotionalRow / activeCoupons);
                            //    dr["Amortisation"] = Convert.ToDecimal(amortisation).ToString("0.00");
                            //    firstAmortizingRow = true;
                            //}
                            //else
                            //    dr["Amortisation"] = "0.00";
                            dr["Amortisation"] = Convert.ToDecimal(Convert.ToDecimal(firstNotionalRow) / totalNoOfRows).ToString("0.00");
                        }

                        if (j == 5)
                        {
                            Decimal factorRow = Convert.ToDecimal(notionalRow / firstNotionalRow);
                            dr["Factor"] = factorRow;
                        }

                        if (j == 6)
                        {
                            dr["Spread"] = Convert.ToDecimal(spread);
                        }

                        if (j == 7)
                        {
                            dr["CouponPaymentDate"] = Convert.ToDateTime(dr["EndDate"]);
                        }

                        if (j == 8)
                        {
                            if (ccy.ToLower() == "eur")
                            {
                                EURCurvesBL eurCurveBL = new EURCurvesBL();
                                List<EURCurve> eurCurve = eurCurveBL.GetEURCurve();
                                Dictionary<double, double> rateVals = new Dictionary<double, double>();
                                foreach (var item in eurCurve)
                                {
                                    rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
                                }
                                var scaler = new SplineInterpolator(rateVals);
                                var y = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
                                dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days)) / 365));
                            }
                            else if (ccy.ToLower() == "usd")
                            {
                                USDCurveBL usdCurveBL = new USDCurveBL();
                                List<USDCurve> usdCurve = usdCurveBL.GetUSCurve();
                                Dictionary<double, double> rateVals = new Dictionary<double, double>();
                                foreach (var item in usdCurve)
                                {
                                    rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
                                }
                                var scaler = new SplineInterpolator(rateVals);
                                var y = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
                                dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days)) / 365));
                            }
                            //  SplineInterpolator interPolation = new  SplineInterpolator();
                            // dr["RiskFreeDP1"]=  ;//EXP(-(interpolate(schedule.endDate(i),EurDB.Rates,'EurDB.Rates')/100*(schedule.endDate(i)-loan.settlementDate)/365));
                        }
                        if (j == 9)
                        {
                            if (dr["EndDate"].ToString() != string.Empty)
                            {
                                dr["RiskFreeDP2"] = Convert.ToDouble(dr["RiskFreeDP1"]) / Convert.ToDouble((Math.Pow(Convert.ToDouble((1 + (spread / 20000))), ((2 * (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days) / 360))));
                            }
                        }

                        if (j == 10)
                        {
                            if (i == 0)
                            {
                                dr["FloatingRate"] = 0.5;
                            }
                            else
                            {
                                double d = Convert.ToDouble(dtSchedule.Rows[i - 1]["RiskFreeDP1"]) / Convert.ToDouble(dr["RiskFreeDP1"]) - 1 / ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dtSchedule.Rows[i - 1]["EndDate"])).Days) / 360;
                                dr["FloatingRate"] = Convert.ToDouble(d) / 100;
                            }
                        }
                        if (j == 11)
                        {
                            dr["AllInRate"] = Convert.ToDouble(Convert.ToDouble(dr["FloatingRate"]) + Convert.ToDouble(spread)) / 100;
                        }
                        if (j == 12)
                        {
                            dr["Interest"] = ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days / 360) * Convert.ToDouble(dr["AllInRate"]) * Convert.ToDouble(dr["Notation"]);
                        }
                        if (j == 13)
                        {
                            dr["Days"] = (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days;
                        }
                        if (j == 14)
                        {
                            dr["AmortisationInt"] = Convert.ToDouble(dr["Amortisation"]) + Convert.ToDouble(dr["Interest"]);
                        }

                    }
                    if (Convert.ToDecimal(dr[4]) >= 0)
                    {
                        dtSchedule.Rows.Add(dr);
                        i++;
                    }
                }
                //if ( dtSchedule.Rows.Count > 0)
                //{
                //    for (int x = 0; x < dtSchedule.Rows.Count; x++)
                //    {
                //        dtSchedule.Rows[x][5] = Convert.ToDecimal(Convert.ToDecimal(notional) / (dtSchedule.Rows.Count)).ToString("0.00");
                //        if (x == 0)
                //        {
                //            firstNotionalRow = Convert.ToDecimal(notional);
                //            notionalRow = firstNotionalRow;
                //        }
                //        else
                //        {
                //            notionalRow = Math.Round(Convert.ToDecimal(Math.Round(Convert.ToDecimal(dtSchedule.Rows[x - 1]["Notation"]), 2) - Convert.ToDecimal(dtSchedule.Rows[x - 1]["Amortisation"])), 2);
                //        }
                //        dtSchedule.Rows[x]["Notation"] = notionalRow.ToString("N");

                //    }
                //}

               

            }
            catch (Exception ex)
            {


            }
            return dtSchedule;
        }
        protected void ExportToCSV()
        {
            string csv_file_path = Server.MapPath("~/Temp/tempfile1.csv");
            if (ddlImportType.SelectedValue == "Loan")
            {
                LoansBLL bll = new LoansBLL();

                var loanList = bll.GetLoans().Select(x => new { LoanName = x.CodeName, Borrower = x.Borrower, Country = x.Country, Sector = x.Sector, SigingDate = x.Signing_Date, MaturityDate = x.Maturity_Date, FixedOrFloating = x.FixedOrFloating, Margin = x.Margin, Currency = x.Currency, CouponFrequency = x.CouponFrequency, FacilitySize = x.FacilitySize, Bilateral = x.Bilateral, Amortizing = x.Amortizing, CouponDate = x.CouponDate, Notional = x.Notional, AmortisationsStartPoint = x.AmortisationsStartPoint, NoOfAmortisationPoint = x.NoOfAmortisationPoint, StructureID = x.StructureID, PP = x.PP, FixedFloating = x.Fixed_Floating, CreditRatingModys = x.CreditRatingModys, CreditRatingSPs = x.CreditRatingSPs, CreditRatingFitch = x.CreditRatingFitch, CreditRatingING = x.CreditRatingING, Gurantor = x.Gurantor, Grid = x.Grid, SummitCreditEntity = x.SummitCreditEntity });
                List<LoanTable> loanTable = new List<LoanTable>();
                foreach (var item in loanList)
                {
                    loanTable.Add(new LoanTable(item.LoanName, item.Borrower, item.Country, Convert.ToDateTime(item.SigingDate), Convert.ToDateTime(item.MaturityDate), item.FixedOrFloating, item.Margin, item.Currency, item.CouponFrequency, item.FacilitySize, Convert.ToBoolean(item.Bilateral), item.Amortizing, Convert.ToDateTime(item.CouponDate), item.Notional, item.AmortisationsStartPoint, Convert.ToInt16(item.NoOfAmortisationPoint), item.StructureID, item.PP, item.FixedFloating, item.CreditRatingModys, item.CreditRatingSPs, item.CreditRatingFitch, item.CreditRatingING, item.Gurantor, item.Grid, item.SummitCreditEntity));
                }

                BLL.CsvExport<LoanTable> exportHelper = new CsvExport<LoanTable>(loanTable);

                exportHelper.ExportToFile(csv_file_path);

                LogActivity("Loans Exported", "Export the Loans", string.Empty);
            }
            else if (ddlImportType.SelectedValue == "Quotes")
            {
                QuotesAndTradesBLL bll = new QuotesAndTradesBLL();

                var quotesList = bll.GetQuotesAndTrades().Select(x => new { LoanName = x.LoanName, TimeStamp = x.TimeStamp, CounterParty = x.CounterParty, BidPrice = x.BidPrice, OfferPrice = x.OfferPrice, BidSpread = x.BidSpread, OfferSpread = x.OfferSpread, Traded = x.Traded, MarketValue = x.MarketValue, Country = x.Country, AverageLife = x.AverageLife, AvgLifeDisc = x.AvgLifeDisc, AvgLifeRiskDisc = x.AvgLifeRiskDisc, AvgLifeNonDisc = x.AvgLifeNonDisc, SettlementDate = x.SettlementDate, Margin = x.Margin });
                List<HistoricalQuote> quotes = new List<HistoricalQuote>();

                foreach (var item in quotesList)
                {
                    quotes.Add(new HistoricalQuote(item.LoanName, Convert.ToDateTime(item.TimeStamp.Value), item.CounterParty, Convert.ToDecimal(item.BidPrice), Convert.ToDecimal(item.OfferPrice), Convert.ToDecimal(item.BidSpread), Convert.ToDecimal(item.OfferSpread), Convert.ToBoolean(item.Traded), Convert.ToDecimal(item.MarketValue), item.Country, Convert.ToDecimal(item.AverageLife), Convert.ToDecimal(item.AvgLifeDisc), Convert.ToDecimal(item.AvgLifeRiskDisc), Convert.ToDecimal(item.AvgLifeNonDisc), Convert.ToDateTime(item.SettlementDate), item.Margin));
                }
                BLL.CsvExport<HistoricalQuote> exportHelper = new CsvExport<HistoricalQuote>(quotes);

                exportHelper.ExportToFile(csv_file_path);

                LogActivity("QuotesAndTrades Exported", "Export the quotesAndtrades", string.Empty);
            }
            else if (ddlImportType.SelectedValue == "EUR Curve")
            {
                EURCurvesBL bll = new EURCurvesBL();
                var list = bll.GetEURCurve().Select(x => new { UploadDate = x.UploadDate, SummitGenDate = x.SummitGenDate, CCY = x.CCY, Index = x.CurveIndex, Days = x.Days, Rate = x.Rate, DiscFreq = x.DiscFreq });

                List<EURCurveTable> eurTable = new List<EURCurveTable>();
                foreach (var item in list)
                {
                    eurTable.Add(new EURCurveTable(Convert.ToDateTime(item.UploadDate), Convert.ToDateTime(item.SummitGenDate), item.CCY, item.Index, Convert.ToInt16(item.Days), Convert.ToDecimal(item.Rate), Convert.ToDecimal(item.DiscFreq)));
                }
                BLL.CsvExport<EURCurveTable> exportHelper = new CsvExport<EURCurveTable>(eurTable);
                exportHelper.ExportToFile(csv_file_path);
                LogActivity("EURCurve Exported", "Export the EURCurve", string.Empty);
            }
            else if (ddlImportType.SelectedValue == "US Curve")
            {
                USDCurveBL bll = new USDCurveBL();
                var list = bll.GetUSCurve().Select(x => new { UploadDate = x.UploadDate, SummitGenDate = x.SummitGenDate, CCY = x.CCY, Index = x.CurveIndex, Days = x.Days, Rate = x.Rate, DiscFreq = x.DiscFreq });

                List<USDCurveTable> eurTable = new List<USDCurveTable>();
                foreach (var item in list)
                {
                    eurTable.Add(new USDCurveTable(Convert.ToDateTime(item.UploadDate), Convert.ToDateTime(item.SummitGenDate), item.CCY, item.Index, Convert.ToInt16(item.Days), Convert.ToDecimal(item.Rate), Convert.ToDecimal(item.DiscFreq)));
                }
                BLL.CsvExport<USDCurveTable> exportHelper = new CsvExport<USDCurveTable>(eurTable);
                exportHelper.ExportToFile(csv_file_path);
                LogActivity("USCurve Exported", "Export the USCurve", string.Empty);
            }

            FileInfo file = new FileInfo(csv_file_path);

            if (file.Exists)
            {
                ShowMessage("Message", "Data has been exported successfully");
                Response.Clear();
                Response.ClearHeaders();
                Response.ClearContent();
                Response.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
                Response.AddHeader("Content-Length", file.Length.ToString());
                Response.ContentType = "text/plain";
                Response.Flush();
                Response.TransmitFile(file.FullName);
                Response.End();
            }
        }
Esempio n. 4
0
        //public DataTable GenerateTable(int colsCount, int rowsCount, DateTime startDate, string frequency, string notional, DateTime maturityDate, DateTime couponDate, Decimal spread, string ccy, DateTime tradedDate)
        //{
        //    DataTable dtSchedule = new DataTable();

        //    try
        //    {
        //        dtSchedule = new DataTable();
        //        dtSchedule.Columns.Add("ID");
        //        dtSchedule.Columns.Add("StartDate");
        //        dtSchedule.Columns.Add("EndDate");
        //        dtSchedule.Columns.Add("CoupFrac");
        //        dtSchedule.Columns.Add("Notation");
        //        dtSchedule.Columns.Add("Amortisation");
        //        dtSchedule.Columns.Add("Factor");
        //        dtSchedule.Columns.Add("Spread");
        //        dtSchedule.Columns.Add("CouponPaymentDate");
        //        dtSchedule.Columns.Add("RiskFreeDP1");
        //        dtSchedule.Columns.Add("RiskFreeDP2");
        //        dtSchedule.Columns.Add("FloatingRate");
        //        dtSchedule.Columns.Add("AllInRate");
        //        dtSchedule.Columns.Add("Interest");
        //        dtSchedule.Columns.Add("Days");
        //        dtSchedule.Columns.Add("AmortisationInt");
        //        System.Text.StringBuilder sb = new System.Text.StringBuilder();



        //        DateTime previousStartDate = startDate;

        //        Decimal firstNationalRow = Convert.ToDecimal(notional);
        //        Decimal nationalRow = Convert.ToDecimal(notional);
        //        bool firstAmortizingRow = false;
        //        int activeCoupons = 0;

        //        // change this condition to while currCouponDate <= maturityDate

        //        for (int i = 0; i < rowsCount; i++)
        //        {

        //            DataRow dr = dtSchedule.NewRow();
        //            dr["ID"] = (i + 1).ToString();


        //            for (int j = 0; j < colsCount; j++)
        //            {

        //                if (i == 0 && j == 0)
        //                {
        //                    //tb.Text = startDate.ToShortDateString();

        //                    dr["StartDate"] = startDate.ToShortDateString();
        //                }
        //                else if (i > 0 && j == 0)
        //                {
        //                    switch (frequency.Trim())
        //                    {
        //                        case "Monthly":
        //                            previousStartDate = startDate;
        //                            startDate = startDate.AddMonths(1).Date;

        //                            //tb.Text = startDate.ToShortDateString();
        //                            dr["StartDate"] = startDate.ToShortDateString();
        //                            break;
        //                        case "Quarterly":
        //                            previousStartDate = startDate;
        //                            startDate = startDate.AddMonths(3).Date;

        //                            dr["StartDate"] = startDate.ToShortDateString();
        //                            break;
        //                        case "Semi-Annual":
        //                            previousStartDate = startDate;
        //                            startDate = startDate.AddMonths(6).Date;
        //                            dr["StartDate"] = startDate.ToShortDateString();
        //                            break;
        //                        case "Annual":
        //                            previousStartDate = startDate;
        //                            startDate = startDate.AddYears(1).Date;
        //                            dr["StartDate"] = startDate.ToShortDateString();
        //                            break;
        //                        default:
        //                            break;
        //                    }

        //                }
        //                if (i == 0 && j == 1)
        //                {
        //                    DateTime endDate;
        //                    switch (frequency.Trim())
        //                    {
        //                        case "Monthly":
        //                            endDate = startDate.AddMonths(1).Date;
        //                            dr["EndDate"] = endDate.ToShortDateString();
        //                            break;
        //                        case "Quarterly":
        //                            endDate = startDate.AddMonths(3).Date;
        //                            dr["EndDate"] = endDate.ToShortDateString();
        //                            break;
        //                        case "Semi-Annual":
        //                            endDate = startDate.AddMonths(6).Date;
        //                            dr["EndDate"] = endDate.ToShortDateString();
        //                            break;
        //                        case "Annual":
        //                            endDate = startDate.AddYears(1).Date;
        //                            dr["EndDate"] = endDate.ToShortDateString();
        //                            break;
        //                        default:
        //                            break;
        //                    }
        //                }
        //                else if (i > 0 && j == 1)
        //                {
        //                    DateTime endDate;
        //                    // Compute next coupon date using CoupNCD and correct for weekends
        //                    // f define frequency
        //                    // so now end dates should be generated for the total number of coupons
        //                    CalculatedDates cal = new CalculatedDates();
        //                    System.Numeric.DayCountBasis d = System.Numeric.DayCountBasis.Actual365;
        //                    prevCouponDate = currCouponDate;
        //                    currentCouponDate = System.Numeric.Financial.CoupNCD(currentCouponDate, maturityDate, f, d);
        //                    //DateTime previousBusinessDay = PreviousBusinessDay(currentCouponDate);
        //                    //cal.CalculatedDate = previousBusinessDay;
        //                    dr["EndDate"] = currentCouponDate.ToShortDateString();

        //                    //switch (frequency.Trim())
        //                    //{
        //                    //    case "Monthly":
        //                    //        endDate = startDate.AddMonths(1).Date;
        //                    //        dr["EndDate"] = endDate.ToShortDateString();
        //                    //        break;
        //                    //    case "Quarterly":
        //                    //        endDate = startDate.AddMonths(3).Date;
        //                    //        dr["EndDate"] = endDate.ToShortDateString();
        //                    //        break;
        //                    //    case "Semi-Annual":
        //                    //        endDate = startDate.AddMonths(6).Date;
        //                    //        dr["EndDate"] = endDate.ToShortDateString();
        //                    //        break;
        //                    //    case "Annual":
        //                    //        endDate = startDate.AddYears(1).Date;
        //                    //        dr["EndDate"] = endDate.ToShortDateString();
        //                    //        break;
        //                    //    default:
        //                    //        break;
        //                    //}
        //                }
        //                if (j == 2)
        //                {

        //                    if (maturityDate != null)
        //                    {
        //                        DateTime maturityDt = Convert.ToDateTime(maturityDate);
        //                        DateTime endDt = Convert.ToDateTime(dr["EndDate"]);
        //                        decimal days = (endDt - maturityDt).Days;
        //                        decimal totalDays = 365;
        //                        decimal coupFreqDays = days / totalDays;
        //                        if (days > 0)
        //                            dr["CoupFrac"] = Convert.ToDecimal(coupFreqDays).ToString("0.00");
        //                        else
        //                            dr["CoupFrac"] = Convert.ToDecimal(0).ToString("0.00");
        //                    }
        //                }
        //                if (j == 3)
        //                {
        //                    if (i == 0)
        //                    {
        //                        firstNationalRow = Convert.ToDecimal(notional);
        //                        nationalRow = firstNationalRow;
        //                    }
        //                    else
        //                    {
        //                        nationalRow = Convert.ToDecimal(Convert.ToDecimal(dtSchedule.Rows[i - 1]["Notation"]) - Convert.ToDecimal(dtSchedule.Rows[i - 1]["Amortisation"]));
        //                    }
        //                    dr["Notation"] = nationalRow.ToString("N");
        //                }
        //                if (j == 4)
        //                {

        //                    DateTime dt = Convert.ToDateTime(dr["StartDate"]);
        //                    if (couponDate != null && dt >= couponDate)
        //                    {
        //                        if (firstAmortizingRow == false)
        //                            activeCoupons = rowsCount - i;
        //                        Decimal amortisation = Convert.ToDecimal(firstNationalRow / activeCoupons);
        //                        dr["Amortisation"] = Convert.ToDecimal(amortisation).ToString("0.00");
        //                        firstAmortizingRow = true;
        //                    }
        //                    else
        //                        dr["Amortisation"] = "0.00";
        //                }
        //                if (j == 5)
        //                {
        //                    Decimal factorRow = Convert.ToDecimal(nationalRow / firstNationalRow);
        //                    dr["Factor"] = factorRow;
        //                }
        //                if (j == 6)
        //                {
        //                    dr["Spread"] = Convert.ToDecimal(spread);
        //                }
        //                if (j == 7)
        //                {
        //                    dr["CouponPaymentDate"] = Convert.ToDateTime(dr["EndDate"]);
        //                }
        //                if (j == 8)
        //                {
        //                    if (ccy.ToLower() == "eur")
        //                    {
        //                        EURCurvesBL eurCurveBL = new EURCurvesBL();
        //                        List<EURCurve> eurCurve = eurCurveBL.GetEURCurve();
        //                        Dictionary<double, double> rateVals = new Dictionary<double, double>();
        //                        foreach (var item in eurCurve)
        //                        {
        //                            rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
        //                        }
        //                        var scaler = new SplineInterpolator(rateVals);
        //                        var y = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
        //                        dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days)) / 365));
        //                    }
        //                    else if (ccy.ToLower() == "usd")
        //                    {
        //                          USDCurveBL usdCurveBL = new USDCurveBL();
        //                          List<USDCurve> usdCurve = usdCurveBL.GetUSCurve();
        //                          Dictionary<double, double> rateVals = new Dictionary<double, double>();
        //                          foreach (var item in usdCurve)
        //                          {
        //                              rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
        //                          }
        //                          var scaler = new SplineInterpolator(rateVals);
        //                          var y = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
        //                          dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days)) / 365));
        //                    }
        //                    //  SplineInterpolator interPolation = new  SplineInterpolator();
        //                    // dr["RiskFreeDP1"]=  ;//EXP(-(interpolate(schedule.endDate(i),EurDB.Rates,'EurDB.Rates')/100*(schedule.endDate(i)-loan.settlementDate)/365));
        //                }
        //                if (j == 9)
        //                {
        //                    if (dr["EndDate"].ToString() != string.Empty)
        //                    {
        //                        dr["RiskFreeDP2"] = Convert.ToDouble(dr["RiskFreeDP1"]) / Convert.ToDouble((Math.Pow(Convert.ToDouble((1 + (spread / 20000))), ((2 * (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days) / 360))));
        //                    }
        //                }

        //                if (j == 10)
        //                {
        //                    if (i == 0)
        //                    {
        //                        dr["FloatingRate"] = 0.5;
        //                    }
        //                    else
        //                    {
        //                        double d = Convert.ToDouble(dtSchedule.Rows[i - 1]["RiskFreeDP1"]) / Convert.ToDouble(dr["RiskFreeDP1"]) - 1 / ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dtSchedule.Rows[i - 1]["EndDate"])).Days) / 360;
        //                        dr["FloatingRate"] = Convert.ToDouble(d) / 100;
        //                    }
        //                }
        //                if (j == 11)
        //                {
        //                    dr["AllInRate"] = Convert.ToDouble(Convert.ToDouble(dr["FloatingRate"]) + Convert.ToDouble(spread)) / 100;
        //                }
        //                if (j == 12)
        //                {
        //                    dr["Interest"] = ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days / 360) * Convert.ToDouble(dr["AllInRate"]) * Convert.ToDouble(dr["Notation"]);
        //                }
        //                if (j == 13)
        //                {
        //                    dr["Days"] = (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days;
        //                }
        //                if (j == 14)
        //                {
        //                    dr["AmortisationInt"] = Convert.ToDouble(dr["Amortisation"]) + Convert.ToDouble(dr["Interest"]);
        //                }

        //            }
        //            dtSchedule.Rows.Add(dr);


        //        }
        //    }
        //    catch (Exception ex)
        //    {


        //    }
        //    return dtSchedule;
        //}


        public DataTable GenerateTable(int colsCount, int rowsCount, DateTime startDate, string frequency, string notional, DateTime maturityDate, DateTime couponDate, Decimal spread, string ccy, DateTime tradedDate, DateTime settlementDate)
        {
            DataTable dtSchedule = new DataTable();

            try
            {
                dtSchedule = new DataTable();
                dtSchedule.Columns.Add("ID");
                dtSchedule.Columns.Add("StartDate");
                dtSchedule.Columns.Add("EndDate");
                dtSchedule.Columns.Add("CoupFrac");
                dtSchedule.Columns.Add("Notation");
                dtSchedule.Columns.Add("Amortisation");
                dtSchedule.Columns.Add("Factor");
                dtSchedule.Columns.Add("Spread");
                dtSchedule.Columns.Add("CouponPaymentDate");
                dtSchedule.Columns.Add("RiskFreeDP1");
                dtSchedule.Columns.Add("RiskFreeDP2");
                dtSchedule.Columns.Add("FloatingRate");
                dtSchedule.Columns.Add("AllInRate");
                dtSchedule.Columns.Add("Interest");
                dtSchedule.Columns.Add("Days");
                dtSchedule.Columns.Add("AmortisationInt");
                System.Text.StringBuilder sb = new System.Text.StringBuilder();



                DateTime previousStartDate = startDate;

                Decimal firstNotionalRow   = Convert.ToDecimal(notional);
                Decimal notionalRow        = Convert.ToDecimal(notional);
                bool    firstAmortizingRow = false;
                int     activeCoupons      = 0;

                // change this condition to while currCouponDate <= maturityDate
                int      i = 0;
                DateTime endDate;
                endDate = settlementDate; // this should come from either ( compact, loan details or add new loan tab )

                int totalNoOfRows = 0;
                while (endDate < maturityDate)
                {
                    System.Numeric.DayCountBasis d = System.Numeric.DayCountBasis.Actual365;
                    System.Numeric.Frequency     f = new System.Numeric.Frequency();
                    if (frequency == "Annual")
                    {
                        f = System.Numeric.Frequency.Annual;
                    }
                    if (frequency == "Semi-Annual")
                    {
                        f = System.Numeric.Frequency.SemiAnnual;
                    }
                    if (frequency == "Quarterly")
                    {
                        f = System.Numeric.Frequency.Quarterly;
                    }

                    endDate = System.Numeric.Financial.CoupNCD(endDate, maturityDate, f, d); // end date is the last coupon settlement date
                    totalNoOfRows++;
                    //DateTime newEndDate = PreviousBusinessDay(endDate);
                    //endDate = newEndDate;
                }
                endDate = settlementDate;
                while (endDate < maturityDate)
                {
                    DataRow dr = dtSchedule.NewRow();
                    dr["ID"] = (i + 1).ToString();

                    // for each column in schedule table
                    for (int j = 0; j < colsCount; j++)
                    {
                        if (i == 0 && j == 0)
                        {
                            dr["StartDate"] = settlementDate.ToShortDateString();
                        }
                        else if (i > 0 && j == 0)
                        {
                            // start date always equals the end date of previous coupon
                            dr["StartDate"] = endDate.ToShortDateString();
                        }
                        if (i == 0 && j == 1)
                        {
                            //DateTime endDate;
                            //switch (frequency.Trim())
                            //{
                            //    case "Monthly":
                            //        endDate = startDate.AddMonths(1).Date;
                            //        tb.Text = endDate.ToShortDateString();
                            //        dr["EndDate"] = endDate.ToShortDateString();
                            //        break;
                            //    case "Quarterly":
                            //        endDate = startDate.AddMonths(3).Date;
                            //        tb.Text = endDate.ToShortDateString();
                            //        dr["EndDate"] = endDate.ToShortDateString();
                            //        break;
                            //    case "Semi-Annual":
                            //        endDate = startDate.AddMonths(6).Date;
                            //        tb.Text = endDate.ToShortDateString();
                            //        dr["EndDate"] = endDate.ToShortDateString();
                            //        break;
                            //    case "Annual":
                            //        endDate = startDate.AddYears(1).Date;
                            //        tb.Text = endDate.ToShortDateString();
                            //        dr["EndDate"] = endDate.ToShortDateString();
                            //        break;
                            //    default:
                            //        break;
                            //}
                            System.Numeric.DayCountBasis d = System.Numeric.DayCountBasis.Actual365;
                            System.Numeric.Frequency     f = new System.Numeric.Frequency();
                            if (frequency == "Annual")
                            {
                                f = System.Numeric.Frequency.Annual;
                            }
                            if (frequency == "Semi-Annual")
                            {
                                f = System.Numeric.Frequency.SemiAnnual;
                            }
                            if (frequency == "Quarterly")
                            {
                                f = System.Numeric.Frequency.Quarterly;
                            }

                            endDate = System.Numeric.Financial.CoupNCD(endDate, maturityDate, f, d); // end date is the last coupon settlement date
                            DateTime newEndDate = PreviousBusinessDay(endDate);
                            dr["EndDate"] = newEndDate.ToShortDateString();
                        }
                        else if (i > 0 && j == 1)
                        {
                            System.Numeric.DayCountBasis d = System.Numeric.DayCountBasis.Actual365;
                            System.Numeric.Frequency     f = new System.Numeric.Frequency();
                            if (frequency == "Annual")
                            {
                                f = System.Numeric.Frequency.Annual;
                            }
                            if (frequency == "Semi-Annual")
                            {
                                f = System.Numeric.Frequency.SemiAnnual;
                            }
                            if (frequency == "Quarterly")
                            {
                                f = System.Numeric.Frequency.Quarterly;
                            }

                            endDate = System.Numeric.Financial.CoupNCD(endDate, maturityDate, f, d); // end date is the last coupon settlement date
                            DateTime newEndDate = PreviousBusinessDay(endDate);
                            dr["EndDate"] = newEndDate.ToShortDateString();
                        }



                        // coup frac calc
                        if (j == 2)
                        {
                            if (settlementDate != null) // removed maturityDate
                            {
                                decimal days         = (endDate - settlementDate).Days;
                                decimal totalDays    = 365;
                                decimal coupFreqDays = days / totalDays;
                                if (days > 0)
                                {
                                    dr["CoupFrac"] = Convert.ToDecimal(coupFreqDays).ToString("0.00");
                                }
                                else
                                {
                                    dr["CoupFrac"] = Convert.ToDecimal(0).ToString("0.00");
                                }
                            }
                        }

                        if (j == 3)
                        {
                            if (i == 0)
                            {
                                firstNotionalRow = Convert.ToDecimal(notional);
                                notionalRow      = firstNotionalRow;
                            }
                            else
                            {
                                notionalRow = Convert.ToDecimal(Convert.ToDecimal(dtSchedule.Rows[i - 1]["Notation"]) - Convert.ToDecimal(dtSchedule.Rows[i - 1]["Amortisation"]));
                            }
                            dr["Notation"] = notionalRow.ToString("N");
                        }

                        if (j == 4)
                        {
                            //DateTime dt = Convert.ToDateTime(dr["StartDate"]);
                            //if (couponDate != null && dt >= couponDate)
                            //{
                            //    if (firstAmortizingRow == false)
                            //        activeCoupons = rowsCount - i;
                            //    Decimal amortisation = Convert.ToDecimal(firstNotionalRow / activeCoupons);
                            //    dr["Amortisation"] = Convert.ToDecimal(amortisation).ToString("0.00");
                            //    firstAmortizingRow = true;
                            //}
                            //else
                            //    dr["Amortisation"] = "0.00";
                            dr["Amortisation"] = Convert.ToDecimal(Convert.ToDecimal(firstNotionalRow) / totalNoOfRows).ToString("0.00");
                        }

                        if (j == 5)
                        {
                            Decimal factorRow = Convert.ToDecimal(notionalRow / firstNotionalRow);
                            dr["Factor"] = factorRow;
                        }

                        if (j == 6)
                        {
                            dr["Spread"] = Convert.ToDecimal(spread);
                        }

                        if (j == 7)
                        {
                            dr["CouponPaymentDate"] = Convert.ToDateTime(dr["EndDate"]);
                        }

                        if (j == 8)
                        {
                            if (ccy.ToLower() == "eur")
                            {
                                EURCurvesBL                 eurCurveBL = new EURCurvesBL();
                                List <EURCurve>             eurCurve   = eurCurveBL.GetEURCurve();
                                Dictionary <double, double> rateVals   = new Dictionary <double, double>();
                                foreach (var item in eurCurve)
                                {
                                    rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
                                }
                                var scaler = new SplineInterpolator(rateVals);
                                var y      = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
                                dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days)) / 365));
                            }
                            else if (ccy.ToLower() == "usd")
                            {
                                USDCurveBL                  usdCurveBL = new USDCurveBL();
                                List <USDCurve>             usdCurve   = usdCurveBL.GetUSCurve();
                                Dictionary <double, double> rateVals   = new Dictionary <double, double>();
                                foreach (var item in usdCurve)
                                {
                                    rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
                                }
                                var scaler = new SplineInterpolator(rateVals);
                                var y      = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
                                dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days)) / 365));
                            }
                            //  SplineInterpolator interPolation = new  SplineInterpolator();
                            // dr["RiskFreeDP1"]=  ;//EXP(-(interpolate(schedule.endDate(i),EurDB.Rates,'EurDB.Rates')/100*(schedule.endDate(i)-loan.settlementDate)/365));
                        }
                        if (j == 9)
                        {
                            if (dr["EndDate"].ToString() != string.Empty)
                            {
                                dr["RiskFreeDP2"] = Convert.ToDouble(dr["RiskFreeDP1"]) / Convert.ToDouble((Math.Pow(Convert.ToDouble((1 + (spread / 20000))), ((2 * (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days) / 360))));
                            }
                        }

                        if (j == 10)
                        {
                            if (i == 0)
                            {
                                dr["FloatingRate"] = 0.5;
                            }
                            else
                            {
                                double d = Convert.ToDouble(dtSchedule.Rows[i - 1]["RiskFreeDP1"]) / Convert.ToDouble(dr["RiskFreeDP1"]) - 1 / ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dtSchedule.Rows[i - 1]["EndDate"])).Days) / 360;
                                dr["FloatingRate"] = Convert.ToDouble(d) / 100;
                            }
                        }
                        if (j == 11)
                        {
                            dr["AllInRate"] = Convert.ToDouble(Convert.ToDouble(dr["FloatingRate"]) + Convert.ToDouble(spread)) / 100;
                        }
                        if (j == 12)
                        {
                            dr["Interest"] = ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days / 360) * Convert.ToDouble(dr["AllInRate"]) * Convert.ToDouble(dr["Notation"]);
                        }
                        if (j == 13)
                        {
                            dr["Days"] = (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days;
                        }
                        if (j == 14)
                        {
                            dr["AmortisationInt"] = Convert.ToDouble(dr["Amortisation"]) + Convert.ToDouble(dr["Interest"]);
                        }
                    }
                    if (Convert.ToDecimal(dr[4]) >= 0)
                    {
                        dtSchedule.Rows.Add(dr);
                        i++;
                    }
                }
                //if ( dtSchedule.Rows.Count > 0)
                //{
                //    for (int x = 0; x < dtSchedule.Rows.Count; x++)
                //    {
                //        dtSchedule.Rows[x][5] = Convert.ToDecimal(Convert.ToDecimal(notional) / (dtSchedule.Rows.Count)).ToString("0.00");
                //        if (x == 0)
                //        {
                //            firstNotionalRow = Convert.ToDecimal(notional);
                //            notionalRow = firstNotionalRow;
                //        }
                //        else
                //        {
                //            notionalRow = Math.Round(Convert.ToDecimal(Math.Round(Convert.ToDecimal(dtSchedule.Rows[x - 1]["Notation"]), 2) - Convert.ToDecimal(dtSchedule.Rows[x - 1]["Amortisation"])), 2);
                //        }
                //        dtSchedule.Rows[x]["Notation"] = notionalRow.ToString("N");

                //    }
                //}
            }
            catch (Exception ex)
            {
            }
            return(dtSchedule);
        }
        protected void Import()
        {
            try
            {
                string filename = Path.GetFileName(fuFile.FileName);
                string csv_file_path = Server.MapPath("~/Temp/tempfile.csv");
                //fuFile.SaveAs(csv_file_path);
                DataTable csvData = null;
                List<string> columnNames = null;
                if (ddlImportType.SelectedValue != "EUR Curve" || ddlImportType.SelectedValue != "US Curve")
                {
                    csvData = CSVHelper.GetDataTabletFromCSVFile(csv_file_path, GetDelimeters());
                    // Get Header column names list
                    columnNames = csvData.Columns.Cast<DataColumn>().Select(c => c.ColumnName).ToList();

                    LogActivity("File Uploaded", "Upload the file to import loan/quotesAndtrades", string.Empty);
                }

                if (ddlImportType.SelectedValue == "Loan")
                {
                    //ExtractLoanList(csvData, columnNames);
                    List<Loans> importedList = ExtractLoanList(csvData, columnNames);
                    //   ExtractLoanList(csvData, columnNames);
                    // Save it to database

                    ApplicationHub.RefreshLoans(importedList);
                    LogActivity("Loans Imported", "Import the loans", string.Empty);
                }
                else if (ddlImportType.SelectedValue == "Quotes")
                {
                    List<QuotesAndTrades> importedList = ExtractQuotesAndTradesList(csvData, columnNames);

                    // Save it to database
                    if (importedList.Count > 0)
                    {
                        QuotesAndTradesBLL bll = new QuotesAndTradesBLL();
                        bll.AddImportedQuotesAndTrades(importedList);
                        ApplicationHub.RefreshQuotesAndTrade(importedList);
                        LogActivity("QuotesAndTrades Imported", "Import the quotesAndtrades", string.Empty);
                    }
                    else
                    {
                        RadWindowManager1.RadAlert("No Data found", 330, 180, "realedge associates", "alertCallBackFn");
                        return;
                    }

                }
                else if (ddlImportType.SelectedValue == "Counterparties")
                {
                    List<CounterParty> importedList = ExtractCounterPartyList(csvData, columnNames);
                    if (importedList.Count > 0)
                    {
                        CounterPartyBL bll = new CounterPartyBL();
                        bll.AddImportedQuotesAndTrades(importedList);

                        LogActivity("Counterparties Imported", "Counterparties", string.Empty);
                    }
                    else
                    {
                        RadWindowManager1.RadAlert("No Data found", 330, 180, "realedge associates", "alertCallBackFn");
                        return;
                    }
                }
                else if (ddlImportType.SelectedValue == "EUR Curve")
                {
                    //importdatafromexcel(csv_file_path);
                    List<EURCurve> importedList = ExtractEURCurveList(csvData, columnNames);
                    if (importedList.Count > 0)
                    {
                        EURCurvesBL bll = new EURCurvesBL();
                        bll.UpdateCurve();
                        bll.ImportEURCurves(importedList);

                        LogActivity("EURCurves Imported", "EURCurves", string.Empty);
                    }
                    else
                    {
                        RadWindowManager1.RadAlert("No Data found", 330, 180, "realedge associates", "alertCallBackFn");
                        return;
                    }
                }
                else if (ddlImportType.SelectedValue == "US Curve")
                {
                    List<USDCurve> importedList = ExtractUSDCurveList(csvData, columnNames);
                    if (importedList.Count > 0)
                    {
                        USDCurveBL bll = new USDCurveBL();
                        bll.UpdateCurve();
                        bll.ImportUSDCurves(importedList);

                        LogActivity("USDCurve Imported", "USDCurves", string.Empty);
                    }
                    else
                    {
                        RadWindowManager1.RadAlert("No Data found", 330, 180, "realedge associates", "alertCallBackFn");
                        return;
                    }
                }

                //ShowMessage("Message", "Data has been imported successfully");
                RadWindowManager1.RadAlert("Data has been imported successfully", 330, 180, "realedge associates", "alertCallBackFn");
            }
            catch (Exception ex)
            {
                LogActivity("File Upload(Unsuccessfull)", "Unable to upload the file", ex.Message);
                //lblImportStatus.Text = "Upload status: The file could not be uploaded. The following error occured: " + ex.Message;
                RadWindowManager1.RadAlert("Data import failed", 330, 180, "realedge associates", "alertCallBackFn");
            }
        }