Report Demo in Linq with Financial Year Checking

   public decimal CalculateCurrentOpeningBalance1()
        {
             string yr, dt;
             string[] date = TxtFrom.Text.Split(new char[] { '/' });
            string month = date[1], datee = date[0], yer = date[2];
            if (int.Parse(month) < 4)
            {
                yr = (int.Parse(yer) - 1).ToString() + "-" + yer;
                dt = "01/04/" + (int.Parse(yer) + 1);
            }
            else
            {
                yr = yer + "-" + int.Parse(yer) + 1;
                dt = "01/04/" + yer;
            }
            //***************************************
            //From Date to Date Compare
            //***************************************
            string fromdate = "";
            string todate = "";
            if (TxtFrom.Text != "")
            {
                fromdate = TxtFrom.Text;
            }
            if (TxtTo.Text == "")
            {
                DateTime dtt = DateTime.Now.Date;
                todate = dtt.ToString("dd/MM/yyyy");
            }
            else
            {
                todate = TxtTo.Text;
            }


            //if (TxtFrom.Text != "")
            //{
                IFormatProvider Culture = new System.Globalization.CultureInfo("fr-FR", true);
                DateTime startd = DateTime.Parse(dt, Culture, System.Globalization.DateTimeStyles.AssumeLocal);
                DateTime fromd = DateTime.Parse(fromdate, Culture, System.Globalization.DateTimeStyles.AssumeLocal);
                DateTime tod = DateTime.Parse(todate, Culture, System.Globalization.DateTimeStyles.AssumeLocal);

                string startyymmdd = startd.ToString("yyyy/MM/dd");
                string formdateyymmdd = fromd.ToString("yyyy/MM/dd");
                string todteyymmdd = tod.ToString("yyyy/MM/dd");

                DateTime startdyymmdd = DateTime.Parse(startyymmdd, Culture, System.Globalization.DateTimeStyles.AssumeLocal);
                DateTime fromdyymmdd = DateTime.Parse(formdateyymmdd, Culture, System.Globalization.DateTimeStyles.AssumeLocal);
                DateTime todyymmdd = DateTime.Parse(todteyymmdd, Culture, System.Globalization.DateTimeStyles.AssumeLocal);
                //***************************************************************************************************
                //   Calculate Amount between the beginning of Financial year and From date
                //**********************************************************
                decimal CurrentOB;
                var lv = from v in UERPManagement.GetInstance.GetAllLedgerReports()
                         group v by v.Ledger_ID into myGroup
                         where myGroup.ToList()[0].Ledger_ID == int.Parse(DdlLedger.SelectedValue)
                         && myGroup.ToList()[0].FromDate >= Convert.ToDateTime(fromdyymmdd)
                         && myGroup.ToList()[0].FromDate < Convert.ToDateTime(todyymmdd)

                         select new
                         {
                             LedgerId = myGroup.Key,
                             sumDrAmt = myGroup.Sum(i => i.Dr_Amount),
                             sumCrAmt = myGroup.Sum(i => i.Cr_Amount),
                             lobdr = myGroup.ToList()[0].LedgerOBDrAmount.ToString(),
                             lobCr = myGroup.ToList()[0].LedgerOBCrAmount.ToString(),
                             IsClosed = myGroup.ToList()[0].IsClosed.ToString(),
                             VoucherNo = myGroup.ToList()[0].Voucher_No.ToString()
                         };
                if (lv.ToList().Count != 0)
                {
                    CurrentOB = (decimal.Parse(lv.ToList()[0].lobdr) - decimal.Parse(lv.ToList()[0].lobCr)) + (lv.ToList()[0].sumDrAmt - lv.ToList()[0].sumCrAmt);
                }
                else
                {
                    CurrentOB = 0;
                }
                return CurrentOB;   
        }
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
   private void GenerateReport()
        {
            decimal CurrentOB = CalculateCurrentOpeningBalance1();
            string fromdate = "";
            string todate = "";
            if (TxtFrom.Text != "")
            {
                fromdate = TxtFrom.Text;
            }
            if (TxtTo.Text == "")
            {
                DateTime dt = DateTime.Now.Date;
                todate = dt.ToString("dd/MM/yyyy");
            }
            else
            {
                todate = TxtTo.Text;
            }
            IFormatProvider Culture = new System.Globalization.CultureInfo("fr-FR", true);
            DateTime fromd = DateTime.Parse(fromdate, Culture, System.Globalization.DateTimeStyles.AssumeLocal);
            DateTime tod = DateTime.Parse(todate, Culture, System.Globalization.DateTimeStyles.AssumeLocal);
            string formdateyymmdd = fromd.ToString("yyyy/MM/dd");
 
            string todteyymmdd = tod.ToString("yyyy/MM/dd");

            DateTime fromdyymmdd = DateTime.Parse(formdateyymmdd, Culture, System.Globalization.DateTimeStyles.AssumeLocal);
            DateTime todyymmdd = DateTime.Parse(todteyymmdd, Culture, System.Globalization.DateTimeStyles.AssumeLocal);

            var lv = from v in UERPManagement.GetInstance.GetAllLedgerReports()
                     where v.Ledger_ID == int.Parse(DdlLedger.SelectedValue)
                         //&& v.Approved==true
                     && v.FromDate >= Convert.ToDateTime(fromdyymmdd)
                      && v.FromDate <= Convert.ToDateTime(todyymmdd)
                     group v by v.Voucher_Date into mtdt
                     orderby mtdt.Key
                     select new
                     {
                         date=mtdt.Key,
                     };
            StringBuilder strReport = new StringBuilder();
            strReport.Append("<table style='border:ridge 1px gray;border-collapse:collapse;' width='100%' cellpadding='0' cellspacing='0'>");
            strReport.Append("<table style='border:ridge 1px gray;border-collapse:collapse;' width='100%' cellpadding='0' cellspacing='0'>");
            strReport.Append("<tr style='font-weight: bold; text-align: left; background-color: #FFFFF0;color:#000000;height:30px;font-family:Verdana;font-size:15px;'><td align='center' style='padding-right: 10px;' colspan='4'><u>" + DdlLedger.SelectedItem.ToString() + "</u></td></tr>");
            strReport.Append("<tr style='font-weight: bold; text-align: left; background-color: #FFFFF0;color:#000000;height:30px;font-family:Verdana;font-size:15px;'><td align='center' style='padding-right: 10px;' colspan='4'><u>Ledger Book Report </u>" + " (" + DdlLedger.SelectedItem.Text + ")" + "</td></tr>");
            strReport.Append("<tr style='font-family:Verdana;font-size:12px;'> <td colspan='4' border='4'> For the period of :: <b>" + TxtFrom.Text + "</b> To <b>" + TxtTo.Text + "</b></td></tr>");
            strReport.Append("</table>");
            strReport.Append("<table style='border:ridge 1px gray;border-collapse:collapse;' width='100%' cellpadding='0' cellspacing='0'>");
            strReport.Append("<tr style='font-weight: bold; text-align:left;background-color:#858981;color:#000000;height:25px;font-family:Verdana;font-size:10px;'>");
            //strReport.Append("<tr style='font-weight: bold; text-align: left; background-color: #858981;color:Black;height:25px;font-family:Verdana;font-size:13px; font-weight:bold;'");
            strReport.Append("<td align='left'>Date</td><td style='text-align:left;padding-right:30px;'>Voucher No</td><td align='left'>Voucher Type </td><td style='text-align:right;padding-right:25px;'>Debit</td><td style='text-align:right;padding-right:30px;'>Credit</td></tr>");
            decimal tcr = 0, tdr = 0, obdr = 0, obcr = 0;
            //=========================Place To Current OB===================
            if (CurrentOB > 0)
            {
                strReport.Append("<tr style='text-align:left;border:solid 1px black;background-color:#FFFFF0;color:#000000;height:25px;font-family:Verdana;font-size:10px;'>");

                strReport.Append("<td align='right' style='padding-right: 10px;' colspan='3'><b>Current Opening Balance </td><td style='text-align:right;padding-right:30px;'><b>" + Math.Abs(CurrentOB) + "</td><td></td></tr>");
                obdr = Math.Abs(CurrentOB);
            }
            else
            {
                strReport.Append("<tr style='text-align:left;background-color:#FFFFF0;color:#000000;height:25px;font-family:Verdana;font-size:10px;'>");
                //strReport.Append("<td align='left'>Date</td><td style='text-align:left;padding-right:30px;'></td><td align='left'></td><td style='text-align:right;padding-right:30px;'></td><td style='text-align:right;padding-right:30px;'>" + CurrentOB + "</td></tr>");

                strReport.Append("<td align='right' style='padding-right: 10px;' colspan='3'><b>Current Opening Balance </td><td></td><td style='text-align:right;padding-right:30px;'><b>" + Math.Abs(CurrentOB) + "</td></tr>");
                obcr = Math.Abs(CurrentOB);

                }
            //==================== End Of Place for current OB =====================
            foreach (var x in lv)
            {
                decimal cr = 0, dr = 0;

                strReport.Append("<tr style='text-align:left;background-color:#FFFFFF;font-weight:bold;height:25px;font-size:12px'>");
                // strReport.Append("<td align='left'>" + x.date.ToString() + "</td> ");
                strReport.Append("<td align='left'>" + x.date.ToString() + "</td> <td></td><td></td><td></td><td style='text-align:right;padding-right:30px;'></td>");
                var lvv = from v1 in UERPManagement.GetInstance.GetAllLedgerReports()
                          where v1.Voucher_Date == x.date.ToString() && v1.Ledger_ID == int.Parse(DdlLedger.SelectedValue.ToString())
                          orderby v1.Voucher_No ascending

                          select new
                          {

                              v1.Ledger_Name,
                              v1.Voucher_Type,
                              v1.Voucher_No,
                              v1.Dr_Amount,
                              v1.Cr_Amount,
                              v1.LedgerOBDrAmount,
                              v1.LedgerOBCrAmount
                          };
                foreach (var y in lvv)
                {
                    strReport.Append("<tr style='text-align:left;background-color:#FFFFF0;color:#000000;height:25px;font-family:Verdana;font-size:10px;'>");

                    if (decimal.Parse(y.Cr_Amount.ToString()) != 0 && decimal.Parse(y.Dr_Amount.ToString()) == 0)
                    {
                        strReport.Append("<td></td><td style='text-align:left;padding-right:30px;'>" + y.Voucher_No + "</td><td align='left'>" + y.Voucher_Type + "</td><td style='text-align:right;padding-right:25px;'></td><td style='text-align:right;padding-right:30px;'>" + y.Cr_Amount + "</td></tr>");
                    }
                    else if (decimal.Parse(y.Dr_Amount.ToString()) != 0 && decimal.Parse(y.Cr_Amount.ToString()) == 0)
                    {
                        strReport.Append("<td></td><td style='text-align:left;padding-right:30px;'>" + y.Voucher_No + "</td><td align='left'>" + y.Voucher_Type + "</td><td style='text-align:right;padding-right:25px;'>" + y.Dr_Amount + "</td><td style='text-align:right;padding-right:30px;'></td></tr>");
                    }
                    cr += y.Cr_Amount;
                    dr += y.Dr_Amount;
                    tcr += y.Cr_Amount;
                    tdr += y.Dr_Amount;
                }
            }
            strReport.Append("</table>");
            strReport.Append("<table style='border:ridge 1px gray;border-collapse:collapse;' width='100%' cellpadding='0' cellspacing='0'><tr style='font-weight: bold; text-align: left; background-color: #858981;color:White;height:25px;font-size:13px'><td width='430px' >  </td><td style='text-align'> Total Amount </td><td style='text-align: right;padding-right: 25px;'>" + tdr.ToString() + " </td><td style='text-align: right;padding-right: 30px;'>" + tcr.ToString() + "</td></tr>");
            tcr += tcr + obcr;
            tdr += tdr + obdr;
            decimal diff = 0, diff1 = 0;
            if (tdr > tcr)
            {
                diff = tdr - tcr;
                strReport.Append("<tr style='font-weight: bold; text-align: left; background-color: #858981;color:White;height:25px;font-size:13px;font-weight:bold'><td> </td><td>   Closing  Balance  (Dr)  </td><td style='text-align: right;padding-right: 25px;'> " + "   " + "</td><td style='text-align: right;padding-right: 30px;'>" + diff.ToString() + "</td></tr>");
            }
            else
            {
                diff1 = tcr - tdr;
                strReport.Append("<tr style='font-weight: bold; text-align: left; background-color: #858981;color:White;height:25px;font-size:13px;font-weight:bold'><td> </td><td>  Closing  Balance  (Cr)</td><td style='text-align: right;padding-right: 25px;'> " + diff1.ToString() + "</td><td style='text-align: right;padding-right: 30px;'>" + "  " + "</td></tr>");
            }
            strReport.Append("<tr style='font-weight: bold; text-align: left; background-color: #858981;color:White;height:25px;font-size:13px;font-weight:bold'><td> </td><td> </td><td style='text-align: right;padding-right: 25px;'> " + (diff1 + tdr).ToString() + "</td><td style='text-align: right;padding-right: 30px;'>" + (diff + tcr).ToString() + "</td></tr>");
            strReport.Append("</table>");
            if (lv.ToList().Count > 0)
            {
                LblReport.Text = strReport.ToString();
                LblEmptyMessage.Text = "";
               // Master.MasterPrint.Enabled = true;
            }
            else
            {
                LblReport.Text = "";
                LblEmptyMessage.Text = "No Record found";
            }
        }

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 private bool ValidateDate()
        {
            string fromdate = "";
            string todate = "";
            if (TxtFrom.Text != "")
            {
                fromdate = TxtFrom.Text;
            }
            if (TxtTo.Text == "")
            {
                DateTime dt = DateTime.Now.Date;
                todate = dt.ToString("dd/MM/yyyy");
            }
            else
            {
                todate = TxtTo.Text;
            }
            //if (TxtFrom.Text != "")
            //{
            IFormatProvider Culture = new System.Globalization.CultureInfo("fr-FR", true);
            DateTime fromd = DateTime.Parse(fromdate, Culture, System.Globalization.DateTimeStyles.AssumeLocal);
            DateTime tod = DateTime.Parse(todate, Culture, System.Globalization.DateTimeStyles.AssumeLocal);
            string formdateyymmdd = fromd.ToString("yyyy/MM/dd");
            string todteyymmdd = tod.ToString("yyyy/MM/dd");

            DateTime fromdyymmdd = DateTime.Parse(formdateyymmdd, Culture, System.Globalization.DateTimeStyles.AssumeLocal);
            DateTime todyymmdd = DateTime.Parse(todteyymmdd, Culture, System.Globalization.DateTimeStyles.AssumeLocal);
            return (Convert.ToDateTime(fromdyymmdd) <= Convert.ToDateTime(todyymmdd));
            //}

        }
================================================================
   private bool Fyear()
        {
            string y = string.Format("{0:dd/MM/yyyy}", System.DateTime.Now);
            string[] date = y.Split(new char[] { '/' });
            string datee = date[0], month = date[1], yer = date[2];
            if (int.Parse(month) < 4)
            {
                yr = (int.Parse(yer) - 1).ToString() + "-" + yer;
            }
            else
            {
                yr = yer + "-" + (int.Parse(yer) + 1);
            }
            if (FinYear == yr)
            {
                return true;
            }
            return false;
        }
======================================================
  public void GetFinancialYear()
        {
            UFMS_FinancialYears fyrr = new UFMS_FinancialYears();
            var lnqFyer = from fyr in UERPManagement.GetInstance.GetAllFinancialYear()
                          select fyr;
            FinYear = lnqFyer.ToList()[0].FYearRange.ToString();
        }
=================================================================
 if (TxtFrom.Text != string.Empty && TxtTo.Text != string.Empty)
            {
                if (ValidateDate())
                {
                    if (Fyear()==true)
                    {
                        GenerateReport();
                    }
                    else
                    {
                        Message("Please select correct financial year........");
                    }
                }
                else
                {

                    Message("From date should be less than To date  !");
                }
            }
            else
            {
                Message("Enter both from and To date  !");
            }
==============================================================
   protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindLedger();
                GetFinancialYear();
            }
        }

===================================================================

   #region VARIABLES
        string yr;
        static string FinYear;
        #endregion

========================================================================

using System.IO;
using System.Text;
using System.Globalization;
using System.Threading;
using System.Data;

using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html.simpleparser;

     private void ExportToExcel(string dataToExport)
        {
            try
            {
                string DesktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
                string FilePath = DesktopPath + "\\LeadReport-" + DateTime.Now.ToString("dd-MM-yyyy hh-mm-ss") + ".xls";
                             
                FileStream fs = new FileStream(FilePath, FileMode.OpenOrCreate, FileAccess.Write);
                StreamWriter sw = new StreamWriter(fs);
                sw.WriteLine(dataToExport.Trim());
                sw.Close();
                fs.Close();
                Response.ClearContent();
                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("content-disposition", "attachment;filename=" + FilePath);
                Response.WriteFile(FilePath);
                Response.End();
                string script = "alert('File Saved to your Desktop with path : " + FilePath;
                ScriptManager.RegisterStartupScript(this, this.GetType(), "", script, true);
             
            }
            catch (Exception ex)
            {
                string msg = ex.Message;
            }
        }

==============================================================
        private string ExportToPDFNew(string HtmldataToExport)
        {
            Document document = new Document(PageSize.A4, 80, 50, 30, 65);

            //___________FIND DESKTOP PATH_______________________________________
            string DesktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            string FileName = "LeadReport-" + DateTime.Now.ToString("dd-MM-yyyy hh-mm-ss") + ".pdf";
            string FilePath = DesktopPath + "\\" + FileName;

            using (FileStream fs = new FileStream(FilePath, FileMode.Create))
            {
                PdfWriter.GetInstance(document, fs);
                using (StringReader stringReader = new StringReader(HtmldataToExport))
                {
                    List<IElement> parsedList = new List<IElement>();
                    parsedList = HTMLWorker.ParseToList(stringReader, null);
                    document.Open();
                    foreach (object item in parsedList)
                    {
                        document.Add((IElement)item);
                    }
                    document.Close();
                }

            }
            return FileName;

        }

=========================================================

   void Message(string msg)
        {
            ContentPlaceHolder cp = (ContentPlaceHolder)this.Master.FindControl("ContentPlaceHolder1");
            UserControl uc = (UserControl)cp.FindControl("MessagePop");
            if (uc != null)
            {
                Label lb = (Label)uc.FindControl("LblModalMessage");
                lb.Text = msg;
                MessagePop.Visible = true;
                AjaxControlToolkit.ModalPopupExtender modal = (AjaxControlToolkit.ModalPopupExtender)uc.FindControl("modalMessage");
                modal.Show();
            }
        }

====================OR===============Total Report==================

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Swash.BusinessLayer;
using Swash.Frameworks;
using Swash.Objects;
using System.IO;
using System.Text;
using System.Globalization;
using System.Threading;
using System.Data;

using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html.simpleparser;


namespace Website.UERP.UFMS
{
    public partial class UFMS_ReportCashBook : System.Web.UI.Page
    {
        string yrr;
        static string FinYear;
        #region Events
        protected void Page_Load(object sender, EventArgs e)
        {
            DdlLedger.Focus();
            if (!IsPostBack)
            {
                BindLedger();
                GetFinancialYear();    
            }
        }
        protected void ImgBtnView_Click(object sender, ImageClickEventArgs e)
        {
            if (TxtFrom.Text != string.Empty && TxtTo.Text != string.Empty)
            {
                if (Validate())
                {
                    if (Fyear() == true)
                    {
                        GenerateReport();
                    }
                    else
                    {
                        // lb.Text = " Please select correct financial year........";
                        Message(" Please select correct financial year........");
                    }
                }
                else
                {
                    Message("From date should be less than To date  !");
                }
            }
            else
            {
                Message("Enter both From and To date  !");
            }
        }
        protected void ImgBtnExportToExcel_Click(object sender, ImageClickEventArgs e)
        {
            try
            {
                if (LblReport.Text.ToString().Trim() != "")
                {
                    StringBuilder s2 = new StringBuilder();
                    s2.Append("<table>");
                    s2.Append("<tr>");
                    s2.Append("<td align='left' colspan='4'>");
                    //s2.Append("DayBook Report details");
                    s2.Append("</td>");
                    s2.Append("</tr>");
                    s2.Append("<table>");

                    //this.ExportToExcel(s2.ToString().Trim() + LblReport.Text.ToString().Trim());
                    this.ExportToExcel(LblReport.Text.ToString().Trim());
                }
                else
                {
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "", "alert('No Data To Export!')", true);
                }
            }
            catch (Exception ex)
            {
                ex.ToString();
            }
        }

        protected void ImgBtnExportToPDF_Click(object sender, ImageClickEventArgs e)
        {
            try
            {
                if (LblReport.Text.ToString().Trim() != "")
                {
                    StringBuilder s2 = new StringBuilder();
                    s2.Append("<table>");
                    s2.Append("<tr>");
                    s2.Append("<td align='left' colspan='4'>");
                    //s2.Append("DayBook Report details");
                    s2.Append("</td>");
                    s2.Append("</tr>");
                    s2.Append("<table>");

                    //this.ExportToPDF(s2.ToString().Trim() + LblReport.Text.ToString().Trim());
                    string fileName = this.ExportToPDFNew(LblReport.Text.ToString().Trim());
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "", "alert('File " + "+fileName+" + " Has Been Successfully Exported To Your Desktop !')", true);
                }
                else
                {
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "", "alert('No Data To Export!')", true);
                }
            }
            catch (Exception ex)
            {
                ex.ToString();
            }
        }
        #endregion
        #region Methods
        public void BindLedger()
        {
            var LnqLedger = UERPManagement.GetInstance.GetAllLedgersCash();
            DdlLedger.DataSource = LnqLedger;
            DdlLedger.DataTextField = "Ledger_Name";
            DdlLedger.DataValueField = "Ledger_ID";
            DdlLedger.DataBind();
            DdlLedger.Items.Insert(0, UFMS_Enumeration.Select.ToString());

        }
        private bool Validate()
        {
            string fromdate = "";
            string todate = "";
            if (TxtFrom.Text != "")
            {
                fromdate = TxtFrom.Text;
            }
            if (TxtTo.Text == "")
            {
                DateTime dt = DateTime.Now.Date;
                todate = dt.ToString("dd/MM/yyyy");
            }
            else
            {
                todate = TxtTo.Text;
            }

            IFormatProvider Culture = new System.Globalization.CultureInfo("fr-FR", true);
            DateTime fromd = DateTime.Parse(fromdate, Culture, System.Globalization.DateTimeStyles.AssumeLocal);
            DateTime tod = DateTime.Parse(todate, Culture, System.Globalization.DateTimeStyles.AssumeLocal);
            string formdateyymmdd = fromd.ToString("yyyy/MM/dd");
            string todteyymmdd = tod.ToString("yyyy/MM/dd");

            DateTime fromdyymmdd = DateTime.Parse(formdateyymmdd, Culture, System.Globalization.DateTimeStyles.AssumeLocal);
            DateTime todyymmdd = DateTime.Parse(todteyymmdd, Culture, System.Globalization.DateTimeStyles.AssumeLocal);
            return (Convert.ToDateTime(fromdyymmdd) <= Convert.ToDateTime(todyymmdd));
        }
        void Message(string msg)
        {
            ContentPlaceHolder cp = (ContentPlaceHolder)this.Master.FindControl("ContentPlaceHolder1");
            UserControl uc = (UserControl)cp.FindControl("MessagePop");
            if (uc != null)
            {
                Label lb = (Label)uc.FindControl("LblModalMessage");
                lb.Text = msg;
                MessagePop.Visible = true;
                AjaxControlToolkit.ModalPopupExtender modal = (AjaxControlToolkit.ModalPopupExtender)uc.FindControl("modalMessage");
                modal.Show();
            }
        }
        public void GenerateReport()
        {
            StringBuilder strReport = new StringBuilder();
            string yr, dt;
            decimal OBRunYrDr = 0, OBRunYrCr = 0, OBcl = 0, OBal = 0, Obdr = 0, Obcr = 0;
            string[] date = TxtFrom.Text.Split(new char[] { '/' });
            string month = date[1], datee = date[0], yer = date[2];
            if (int.Parse(month) < 4)
            {
                yr = (int.Parse(yer) - 1).ToString() + "-" + yer;
                dt = "01/04/" + (int.Parse(yer) + 1);
            }
            else
            {
                yr = yer + "-" + int.Parse(yer) + 1;
                dt = "01/04/" + yer;
            }
            //**********************************************
            //Calculating Opening Balance
            //***********************************************
            if (DdlLedger.SelectedIndex == 0)
            {
                var OpBl = from ob in UERPManagement.GetInstance.GetAllLedgersCash()

                           select ob;
                if (OpBl.Count() > 0)
                {
                    foreach (var Tob in OpBl)
                    {
                        OBal += decimal.Parse(Tob.LedgerOBDrAmount.ToString()) - decimal.Parse(Tob.LedgerOBCrAmount.ToString());
                    }
                }
            }
            else
            {
                var OpBl = from ob in UERPManagement.GetInstance.GetAllLedgersCash()
                           where ob.Ledger_ID == int.Parse(DdlLedger.SelectedValue)
                           select ob;
                if (OpBl.Count() > 0)
                {
                    foreach (var Tob in OpBl)
                    {
                        OBal += decimal.Parse(Tob.LedgerOBDrAmount.ToString()) - decimal.Parse(Tob.LedgerOBCrAmount.ToString());
                    }
                }
            }
            //***************************************
            //From Date to Date Compare
            //***************************************
            string fromdate = "";
            string todate = "";
            if (TxtFrom.Text != "")
            {
                fromdate = TxtFrom.Text;
            }
            if (TxtTo.Text == "")
            {
                DateTime dtt = DateTime.Now.Date;
                todate = dtt.ToString("dd/MM/yyyy");
            }
            else
            {
                todate = TxtTo.Text;
            }


            if (TxtFrom.Text != "")
            {
                IFormatProvider Culture = new System.Globalization.CultureInfo("fr-FR", true);
                DateTime startd = DateTime.Parse(dt, Culture, System.Globalization.DateTimeStyles.AssumeLocal);
                DateTime fromd = DateTime.Parse(fromdate, Culture, System.Globalization.DateTimeStyles.AssumeLocal);
                DateTime tod = DateTime.Parse(todate, Culture, System.Globalization.DateTimeStyles.AssumeLocal);

                string startyymmdd = startd.ToString("yyyy/MM/dd");
                string formdateyymmdd = fromd.ToString("yyyy/MM/dd");
                string todteyymmdd = tod.ToString("yyyy/MM/dd");

                DateTime startdyymmdd = DateTime.Parse(startyymmdd, Culture, System.Globalization.DateTimeStyles.AssumeLocal);
                DateTime fromdyymmdd = DateTime.Parse(formdateyymmdd, Culture, System.Globalization.DateTimeStyles.AssumeLocal);
                DateTime todyymmdd = DateTime.Parse(todteyymmdd, Culture, System.Globalization.DateTimeStyles.AssumeLocal);


                //***************************************************************************************************
                //   Calculate Amount between the beginning of Financial year and From date
                //****************************************************************************************************
                if (Convert.ToDateTime(fromdyymmdd) <= Convert.ToDateTime(todyymmdd))
                {
                    if (DdlLedger.SelectedIndex == 0)
                    {
                        var ledger = from led in UERPManagement.GetInstance.GetAllLedgersCash()
                                     select led;
                        if (ledger.Count() > 0)
                        {
                            foreach (var lg in ledger)
                            {
                                var ledgers = from lgetd in UERPManagement.GetInstance.GetAllCash()
                                              where lgetd.FromDate >= Convert.ToDateTime(startdyymmdd) && lgetd.FromDate <= Convert.ToDateTime(fromdyymmdd)
                                              // && lgDtl.Approved == true
                                              select lgetd;
                                if (ledgers.Count() > 0)
                                {
                                    foreach (var l in ledgers)
                                    {
                                        OBRunYrDr += decimal.Parse(l.Dr_Amount.ToString());
                                        OBRunYrCr += decimal.Parse(l.Cr_Amount.ToString());
                                    }
                                }
                            }
                        }
                        else
                        {
                            LblReport.Text = "No Cash ledger found......";
                            return;
                        }
                    }
                    else
                    {
                        var ledgerDetails = from lgetd in UERPManagement.GetInstance.GetAllCash()
                                            where lgetd.Ledger_ID == int.Parse(DdlLedger.SelectedValue)
                                           && lgetd.FromDate >= Convert.ToDateTime(startdyymmdd) && lgetd.FromDate <= Convert.ToDateTime(fromdyymmdd)

                                            select lgetd;
                        if (ledgerDetails.Count() > 0)
                        {
                            foreach (var lds in ledgerDetails)
                            {
                                OBRunYrDr += decimal.Parse(lds.Dr_Amount.ToString());
                                OBRunYrCr += decimal.Parse(lds.Cr_Amount.ToString());
                            }
                        }
                    }
                    OBcl = OBal + (OBRunYrDr - OBRunYrCr);
                    string Rep;
                    if (DdlLedger.SelectedIndex == 0)
                    {
                        Rep = "All CashBook Report";
                    }
                    else
                    {
                        Rep = DdlLedger.SelectedItem.Text;
                    }
                    strReport.Append("<table style='border:ridge 1px gray;border-collapse:collapse;' width='100%' cellpadding='0' cellspacing='0'>");
                    strReport.Append("<table style='border:ridge 1px gray;border-collapse:collapse;' width='100%' cellpadding='0' cellspacing='0'><tr style='font-weight: bold; text-align: center; background-color: #FFFFF0;color:#000000;height:30px;font: arial;font-size:18px;'><td></td><td></td><td></td><td align='center' style='padding-right: 12px;'><u>" + DdlLedger.SelectedItem.ToString() + "</u> </td></tr>");
                    strReport.Append("<tr style='font-weight: bold; text-align: center; background-color: #FFFFF0;color:#000000;height:30px;font: arial;font-size:18px;'><td></td><td></td><td></td><td align='center' style='padding-right: 12px;'><u>CashBook Report</u>" + "          (" + Rep + ")" + " </td></tr>");
                    strReport.Append("<tr style='font-weight: bold; text-align: left; background-color: #FFFFFFS;color:#000000;height:30px;font: arial;font-size:12px;'><td></td><td></td><td></td><td align='left' style='padding-right: 10px;'align='center'> For the period of :: " + TxtFrom.Text + " To " + TxtTo.Text + "  </td><td></td><td></td><td></td></tr>");
                    strReport.Append("</table>");
                    strReport.Append("<table style='border:ridge 1px gray;border-collapse:collapse;' width='100%' cellpadding='0' cellspacing='0'><tr style='font-weight: bold; text-align: left; background-color: #858981;color:White;height:25px;font-size:15px'><td align='left'>Date.</td><td align='left'>Sl No.</td><td align='left'>Details</td><td align='left'>Voucher Type</td><td align='left'>Voucher No.</td><td style='text-align: right;padding-right: 25px;'>Amount (Dr)</td><td style='text-align: right;padding-right: 25px;'>Amount (Cr)</td></tr>");
                    strReport.Append("<tr style='text-align:left;background-color:#FFFFFF;font-weight:bold;height:25px;font-size:14px'>");
                    if (OBcl > 0)
                    {
                        OBcl = OBcl * 1;
                        strReport.Append("<td align='left' colspan='3' > Opening Balance </td><td></td><td></td><td  style='text-align: right;padding-right: 25px;'>" + OBcl.ToString() + " </td><td></td>");
                        Obdr = OBcl;
                    }
                    else
                    {
                        OBcl = OBcl * (-1);
                        strReport.Append("<td align='left' colspan='3'> Opening Balance </td><td></td><td></td><td></td><td style='text-align: right;padding-right: 25px;'>" + OBcl.ToString() + " </td>");
                        Obcr = OBcl;
                    }
                    //***************************************************************************************************
                    //   Getting transaction  between From and To date
                    //********************************************************
                    if (DdlLedger.SelectedIndex == 0)
                    {//2
                        var details = from dtls in UERPManagement.GetInstance.GetAllCash()
                                      where dtls.FromDate >= Convert.ToDateTime(fromdyymmdd)
                                            && dtls.FromDate <= Convert.ToDateTime(todyymmdd)
                                      group dtls by dtls.Voucher_Date into mydate
                                      orderby mydate.Key
                                      select new
                                      {
                                          VoucherDate = mydate.Key
                                      };
                        decimal ca, da;
                        decimal totQtyCa = 0, totCr = 0;
                        decimal totQtyDa = 0, totDr = 0;
                        int slno = 0;
                        if (details.Count() > 0)
                        { //1
                            foreach (var x in details)
                            {
                                strReport.Append("<tr style='text-align:left;background-color:#FFFFFF;font-weight:bold;height:25px;font-size:13px'>");
                                strReport.Append("<td align='left' colspan='7'>" + x.VoucherDate.ToString() + "</td> ");


                                var linqCSdetails = from b in UERPManagement.GetInstance.GetAllCash()
                                                    where b.Voucher_Date == x.VoucherDate.ToString()
                                                    select b;

                                totCr = 0; totDr = 0;
                                foreach (var y in linqCSdetails)
                                {
                                    ca = decimal.Parse(y.Cr_Amount.ToString());
                                    da = decimal.Parse(y.Dr_Amount.ToString());
                                    strReport.Append("<tr style='text-align:left;background-color:#FFFFFF;font-weight:italic;height:25px;font-size:12px'>");
                                    slno++;

                                    if (decimal.Parse(ca.ToString()) != 0 && decimal.Parse(da.ToString()) == 0)
                                    {
                                        strReport.Append("<td></td><td align='centre'>" + slno.ToString() + "</td> <td>" + y.Ledger_Name.ToString() + " </td>" + "<td>" + y.Voucher_Type.ToString() + " </td>" + "<td>" + y.Voucher_No.ToString() + " </td>" + "<td style='text-align: right;padding-right: 25px;'></td>" + "<td style='text-align: right;padding-right: 25px;'>" + ca.ToString() + "</td>");
                                    }
                                    else if (decimal.Parse(da.ToString()) != 0 && decimal.Parse(ca.ToString()) == 0)
                                    {
                                        strReport.Append("<td></td><td align='centre'>" + slno.ToString() + "</td> <td>" + y.Ledger_Name.ToString() + " </td>" + "<td>" + y.Voucher_Type.ToString() + " </td>" + "<td>" + y.Voucher_No.ToString() + " </td>" + "<td style='text-align: right;padding-right: 25px;'>" + da.ToString() + "</td>" + "<td style='text-align: right;padding-right: 25px;'></td>");
                                    }
                                    totQtyCa += decimal.Parse(ca.ToString().Trim());
                                    totQtyDa += decimal.Parse(da.ToString().Trim());
                                    totCr += decimal.Parse(ca.ToString().Trim());
                                    totDr += decimal.Parse(da.ToString().Trim());
                                }
                                strReport.Append("<tr style='font-weight: bold; text-align: left; background-color: #858981;color:White;height:25px;font-size:13px;font-weight:bold'><td> </td><td> </td><td> </td><td> </td><td>Total Amount </td><td style='text-align: right;padding-right: 25px;'>" + totDr.ToString() + " </td><td style='text-align: right;padding-right: 25px;'>" + totCr.ToString() + "</td></tr>");
                            }
                            strReport.Append("</table>");
                            totQtyDa += Obdr;
                            totQtyCa += Obcr;
                            strReport.Append("<table style='border:ridge 1px gray;border-collapse:collapse;' width='100%' cellpadding='0' cellspacing='0'><tr style='font-weight: bold; text-align: left; background-color: #858981;color:White;height:25px;font-size:14px'><td width='60px' >  </td><td style='text-align: right;padding-right: 150px;'>Total Amount </td><td style='text-align: right;padding-right: 25px;'>" + totQtyDa.ToString() + " </td><td style='text-align: right;padding-right: 25px;'>" + totQtyCa.ToString() + "</td></tr>");
                            strReport.Append("</table>");
                            LblReport.Text = strReport.ToString();

                        }//1
                        else
                        {
                            if (OBcl == 0)
                            {
                                LblReport.Text = "No Data found......";
                            }
                        }
                        strReport.Append("</table>");
                        LblReport.Text = strReport.ToString();
                    }//2
                    else
                    {
                        var details = from dtls in UERPManagement.GetInstance.GetAllCash()
                                      where dtls.Ledger_ID == int.Parse(DdlLedger.SelectedValue) && dtls.FromDate >= Convert.ToDateTime(fromdyymmdd)
                                      && dtls.FromDate <= Convert.ToDateTime(todyymmdd)// && dtls.Approved == true
                                      group dtls by dtls.Voucher_Date into mydate
                                      orderby mydate.Key
                                      select new
                                      {
                                          VoucherDate = mydate.Key
                                      };
                        decimal ca, da;
                        decimal totQtyCa = 0, totCr = 0;
                        decimal totQtyDa = 0, totDr = 0;
                        int slno = 0;
                        if (details.Count() > 0)
                        {
                            foreach (var x in details)
                            {
                                strReport.Append("<tr style='text-align:left;background-color:#FFFFFF;font-weight:bold;height:25px;font-size:13px'>");
                                strReport.Append("<td align='left' colspan='7'>" + x.VoucherDate.ToString() + "</td> ");
                                var linqCSdetails = from b in UERPManagement.GetInstance.GetAllCash()
                                                    where b.Ledger_ID == int.Parse(DdlLedger.SelectedValue)
                                                    && b.Voucher_Date == x.VoucherDate.ToString() //&& b.Approved == true
                                                    select b;

                                totCr = 0; totDr = 0;
                                //
                                foreach (var y in linqCSdetails)
                                {
                                    ca = decimal.Parse(y.Cr_Amount.ToString());
                                    da = decimal.Parse(y.Dr_Amount.ToString());
                                    strReport.Append("<tr style='text-align:left;background-color:#FFFFFF;font-weight:italic;height:25px;font-size:12px'>");
                                    slno++;
                                    if (decimal.Parse(ca.ToString()) != 0 && decimal.Parse(da.ToString()) == 0)
                                    {
                                        strReport.Append("<td></td><td align='centre'>" + slno.ToString() + "</td> <td>" + y.Ledger_Name.ToString() + " </td>" + "<td>" + y.Voucher_Type.ToString() + " </td>" + "<td>" + y.Voucher_No.ToString() + " </td>" + "<td style='text-align: right;padding-right: 25px;'></td>" + "<td style='text-align: right;padding-right: 25px;'>" + ca.ToString() + "</td>");
                                    }
                                    else if (decimal.Parse(da.ToString()) != 0 && decimal.Parse(ca.ToString()) == 0)
                                    {
                                        strReport.Append("<td></td><td align='centre'>" + slno.ToString() + "</td> <td>" + y.Ledger_Name.ToString() + " </td>" + "<td>" + y.Voucher_Type.ToString() + " </td>" + "<td>" + y.Voucher_No.ToString() + " </td>" + "<td style='text-align: right;padding-right: 25px;'>" + da.ToString() + "</td>" + "<td style='text-align: right;padding-right: 25px;'></td>");
                                    }
                                    //strReport.Append("<td></td><td align='centre'>" + slno.ToString() + "</td> <td>" + y.LedgerName.ToString() + " </td>" + "<td>" + y.VoucherType.ToString() + " </td>" + "<td>" + y.VoucherNo.ToString() + " </td>" + "<td style='text-align: right;padding-right: 25px;'>" + da.ToString() + "</td>" + "<td style='text-align: right;padding-right: 25px;'>" + ca.ToString() + "</td>");
                                    totQtyCa += decimal.Parse(ca.ToString().Trim());
                                    totQtyDa += decimal.Parse(da.ToString().Trim());
                                    totCr += decimal.Parse(ca.ToString().Trim());
                                    totDr += decimal.Parse(da.ToString().Trim());
                                    //strReport.Append("<tr><td></td></tr>");
                                }
                                strReport.Append("<tr style='font-weight: bold; text-align: left; background-color: #858981;color:White;height:25px;font-size:13px;font-weight:bold'><td> </td><td> </td><td> </td><td> </td><td>Total Amount </td><td style='text-align: right;padding-right: 25px;'>" + totDr.ToString() + " </td><td style='text-align: right;padding-right: 25px;'>" + totCr.ToString() + "</td></tr>");
                            }
                            strReport.Append("</table>");
                            totQtyDa += Obdr;
                            totQtyCa += Obcr;
                            strReport.Append("<table style='border:ridge 1px gray;border-collapse:collapse;' width='100%' cellpadding='0' cellspacing='0'><tr style='font-weight: bold; text-align: left; background-color: #858981;color:White;height:25px;font-size:14px'><td width='60px' >  </td><td style='text-align: right;padding-right: 150px;'>Total Amount </td><td style='text-align: right;padding-right: 25px;'>" + totQtyDa.ToString() + " </td><td style='text-align: right;padding-right: 25px;'>" + totQtyCa.ToString() + "</td></tr>");
                            strReport.Append("</table>");

                            LblReport.Text = strReport.ToString();
                        }
                        else
                        {
                            if (OBcl == 0)
                            {
                                LblReport.Text = "";
                                LblEmptyMessage.Text = "No Record found";
                            }
                        }
                        strReport.Append("</table>");
                        LblReport.Text = strReport.ToString();
                    }
                }
                strReport.Append("</table>");

            }
        }

        //private void ExportToExcel(string dataToExport)
        //{
        //    try
        //    {
        //        string filepath = Server.MapPath("~/UERP/UFMS/Exported_Files/" + DateTime.Now.ToString("dd-MM-yyyy hh-mm-") + ".xls");
        //        FileStream fs = new FileStream(filepath, FileMode.OpenOrCreate, FileAccess.Write);
        //        StreamWriter sw = new StreamWriter(fs);
        //        sw.WriteLine(dataToExport.ToString().Trim());
        //        sw.Close();
        //        fs.Close();
        //        Response.ClearContent();
        //        Response.ContentType = "application/vnd.ms.excel";
        //        Response.AddHeader("content-disposition", "attachment;filename=" + filepath);
        //        Response.WriteFile(filepath);
        //        Response.End();
        //    }
        //    catch (Exception ex)
        //    {
        //        string msg = ex.Message;
        //    }
        //}
        private void ExportToExcel(string dataToExport)
        {
            try
            {
                string DesktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
                string FilePath = DesktopPath + "\\LeadReport-" + DateTime.Now.ToString("dd-MM-yyyy hh-mm-ss") + ".xls";
                             
                FileStream fs = new FileStream(FilePath, FileMode.OpenOrCreate, FileAccess.Write);
                StreamWriter sw = new StreamWriter(fs);
                sw.WriteLine(dataToExport.Trim());
                sw.Close();
                fs.Close();
                Response.ClearContent();
                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("content-disposition", "attachment;filename=" + FilePath);
                Response.WriteFile(FilePath);
                Response.End();
                string script = "alert('File Saved to your Desktop with path : " + FilePath;
                ScriptManager.RegisterStartupScript(this, this.GetType(), "", script, true);
             
            }
            catch (Exception ex)
            {
                string msg = ex.Message;
            }
        }
        private string ExportToPDFNew(string HtmldataToExport)
        {
            Document document = new Document(PageSize.A4, 80, 50, 30, 65);

            //___________FIND DESKTOP PATH_______________________________________
            string DesktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            string FileName = "LeadReport-" + DateTime.Now.ToString("dd-MM-yyyy hh-mm-ss") + ".pdf";
            string FilePath = DesktopPath + "\\" + FileName;

            using (FileStream fs = new FileStream(FilePath, FileMode.Create))
            {
                PdfWriter.GetInstance(document, fs);
                using (StringReader stringReader = new StringReader(HtmldataToExport))
                {
                    List<IElement> parsedList = new List<IElement>();
                    parsedList = HTMLWorker.ParseToList(stringReader, null);
                    document.Open();
                    foreach (object item in parsedList)
                    {
                        document.Add((IElement)item);
                    }
                    document.Close();
                }

            }
            return FileName;

        }
        public void GetFinancialYear()
        {
            UFMS_FinancialYears fyrr = new UFMS_FinancialYears();
            var lnqFyr = from fyr in UERPManagement.GetInstance.GetAllFinancialYear()
                         select fyr;
            FinYear = lnqFyr.ToList()[0].FYearRange.ToString();
        }
        private bool Fyear()
        {
            string y = string.Format("{0:dd/MM/yyyy}", System.DateTime.Now);
            string[] date = y.Split(new char[] { '/' });
            string datee = date[0], month = date[1], yer = date[2];
            if (int.Parse(month) < 4)
            {
                yrr = (int.Parse(yer) - 1).ToString() + "-" + yer;
                //dt = "01/04/" + (int.Parse(yer) - 1).ToString();
            }
            else
            {
                yrr = yer + "-" + (int.Parse(yer) + 1);
                //dt = "01/04/" + yer;
            }

            if (FinYear == yrr)
            {
                return true;
            }
            return false;
        }

        private void Reset()
        {
            if (DdlLedger.SelectedIndex != -1)
            {
                DdlLedger.SelectedIndex = 0;
            }

            TxtFrom.Text = "";
            TxtTo.Text = "";
        }
        #endregion
        protected void Print(object sender, EventArgs e)
        {
            Session["CashBookData"] = LblReport.Text;
            Response.Redirect("Print.aspx?Page=CashBook");
        }    


    }
}