Exemple #1
0
    public void bindDataPurReturn()
    {
        // DateTime startDate, endDate;
        DataSet ds       = new DataSet();
        DataSet dstt     = new DataSet();
        double  tot      = 0;
        double  tot1     = 0;
        string  intTrans = "";
        string  salesRet = "";
        string  delNote  = "";

        string condi = "";

        intTrans = "NO";
        salesRet = "YES";
        delNote  = "NO";

        string   Branch    = Convert.ToString(Request.QueryString["Branch"].ToString());
        DateTime startDate = Convert.ToDateTime(Request.QueryString["startdate"].ToString());
        DateTime endDate   = Convert.ToDateTime(Request.QueryString["enddate"].ToString());

        condi = Request.QueryString["condi"].ToString();
        condi = Server.UrlDecode(condi);

        if (condi == "5%")
        {
            condi = " And tblSalesitems.vat = 5";
        }
        else if (condi == "14.5%")
        {
            condi = " And tblSalesitems.vat = 14.5";
        }
        if (condi == "All")
        {
            condi = "";
        }

        // startDate = Convert.ToDateTime(txtStrtDt.Text.Trim());
        // endDate = Convert.ToDateTime(txtEdDt.Text.Trim());

        // string Branch = DropDownList2.SelectedValue;

        objBL = new BusinessLogic(ConfigurationManager.ConnectionStrings[Request.Cookies["Company"].Value].ToString());
        ds    = objBL.SalesPurRetAnnuxere(startDate, endDate, salesRet, intTrans, delNote, condi, Branch);
        Double serialno = 1;

        if (ds != null)
        {
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataTable dt = new DataTable();
                dt.Columns.Add(new DataColumn("SNo"));
                dt.Columns.Add(new DataColumn("Name of the buyer"));
                dt.Columns.Add(new DataColumn("Buyer Tin"));
                dt.Columns.Add(new DataColumn("Commodity Code"));
                dt.Columns.Add(new DataColumn("Invoice No"));
                dt.Columns.Add(new DataColumn("Invoice Date"));
                dt.Columns.Add(new DataColumn("Sales Value"));
                dt.Columns.Add(new DataColumn("Tax Rate"));
                dt.Columns.Add(new DataColumn("Vat CST Paid"));
                dt.Columns.Add(new DataColumn("Category"));
                dt.Columns.Add(new DataColumn("Branchcode"));

                DataRow dr_export1 = dt.NewRow();
                dt.Rows.Add(dr_export1);

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    DataRow dr_export = dt.NewRow();
                    dr_export["SNo"] = serialno;
                    dr_export["Name of the buyer"] = dr["LinkName"];
                    dr_export["Buyer Tin"]         = dr["Tinnumber"];
                    dr_export["Commodity Code"]    = "";
                    dr_export["Invoice No"]        = dr["BillNo"];

                    string aa   = dr["BillDate"].ToString().ToUpper().Trim();
                    string dtaa = Convert.ToDateTime(aa).ToString("dd/MM/yyyy");
                    dr_export["Invoice Date"] = dtaa;

                    tot = tot + Convert.ToDouble(dr["NetRate"]);

                    tot1 = tot1 + Convert.ToDouble(dr["ActualVAT"]);

                    dr_export["Branchcode"]   = dr["Branchcode"];
                    dr_export["Sales Value"]  = dr["NetRate"];
                    dr_export["Tax Rate"]     = dr["vat"];
                    dr_export["Vat CST Paid"] = dr["ActualVAT"];
                    dr_export["Category"]     = "";
                    dt.Rows.Add(dr_export);

                    serialno = serialno + 1;
                }

                DataRow dr_export2 = dt.NewRow();
                dr_export2["SNo"] = "";
                dr_export2["Name of the buyer"] = "";
                dr_export2["Buyer Tin"]         = "";
                dr_export2["Commodity Code"]    = "";
                dr_export2["Invoice No"]        = "";
                dr_export2["Invoice Date"]      = "Total =";
                dr_export2["Sales Value"]       = tot;
                dr_export2["Tax Rate"]          = "Total = ";
                dr_export2["Vat CST Paid"]      = tot1;
                dr_export2["Category"]          = "";
                dt.Rows.Add(dr_export2);

                // ExportToExcel(dt);
                dstt.Tables.Add(dt);
            }
        }
        else
        {
            ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
        }
        ReportsBL.ReportClass rptStock = new ReportsBL.ReportClass();
        // DataSet ds = new DataSet(sDataSource);
        //  DataSet ds = rptStock.getCategory(sDataSource);
        Grdreport.Visible    = true;
        Grdreport.DataSource = dstt;
        Grdreport.DataBind();
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            sDataSource = ConfigurationManager.ConnectionStrings[Request.Cookies["Company"].Value].ToString();
            Connection  = Request.Cookies["Company"].Value;
            if (!IsPostBack)
            {
                lblHeading.Text = " Purchase Comprehency Report";

                DataSet       companyInfo = new DataSet();
                BusinessLogic bl          = new BusinessLogic(sDataSource);
                if (Request.Cookies["Company"] != null)
                {
                    companyInfo = bl.getCompanyInfo(Request.Cookies["Company"].Value);

                    if (companyInfo != null)
                    {
                        if (companyInfo.Tables[0].Rows.Count > 0)
                        {
                            foreach (DataRow dr in companyInfo.Tables[0].Rows)
                            {
                                lblTNGST.Text   = Convert.ToString(dr["TINno"]);
                                lblCompany.Text = Convert.ToString(dr["CompanyName"]);
                                lblPhone.Text   = Convert.ToString(dr["Phone"]);
                                lblGSTno.Text   = Convert.ToString(dr["GSTno"]);

                                lblAddress.Text = Convert.ToString(dr["Address"]);
                                lblCity.Text    = Convert.ToString(dr["city"]);
                                lblPincode.Text = Convert.ToString(dr["Pincode"]);
                                lblState.Text   = Convert.ToString(dr["state"]);
                            }
                        }
                    }
                    DataSet ds1 = bl.getImageInfo();
                    if (ds1 != null)
                    {
                        if (ds1.Tables[0].Rows.Count > 0)
                        {
                            for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
                            {
                                Image1.ImageUrl = "App_Themes/NewTheme/images/" + ds1.Tables[0].Rows[i]["img_filename"];
                                Image1.Height   = 95;
                                Image1.Width    = 114;
                            }
                        }
                        else
                        {
                            Image1.Height   = 95;
                            Image1.Width    = 114;
                            Image1.ImageUrl = "App_Themes/NewTheme/images/TESTLogo.png";
                        }
                    }
                }
            }

            lblBillDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
            //txtStartDate.Text = DateTime.Now.ToString("dd/MM/yyyy");

            DateTime indianStd = TimeZoneInfo.ConvertTimeBySystemTimeZoneId(DateTime.Now, "India Standard Time");
            string   dtaa      = Convert.ToDateTime(indianStd).ToString("dd/MM/yyyy");
            // txtStartDate.Text = dtaa;

            //  lblHeadDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
            //string sDataSource = Server.MapPath("App_Data\\Store0910.mdb");
            //string sDataSource = Server.MapPath(ConfigurationSettings.AppSettings["DataSource"].ToString());



            divPrint.Visible = true;
            divPr.Visible    = true;
            DataSet dstt = new DataSet();

            DataSet   ds = new DataSet();
            DataTable dt = new DataTable();
            // DateTime startDate, endDate;
            double brandTotal    = 0;
            double catTotal      = 0;
            double total         = 0;
            double producttot    = 0;
            string fLvlValueTemp = string.Empty;
            string fLvlValue     = string.Empty;
            string tLvlValueTemp = string.Empty;
            string tLvlValue     = string.Empty;
            string sLvlValueTemp = string.Empty;
            string sLvlValue     = string.Empty;
            string connection    = Request.Cookies["Company"].Value;

            string brand    = Convert.ToString(Request.QueryString["brand"].ToString());
            string Category = Convert.ToString(Request.QueryString["category"].ToString());
            string product  = Convert.ToString(Request.QueryString["product"].ToString());
            string Branch   = Convert.ToString(Request.QueryString["Branch"].ToString());

            DateTime startDate = Convert.ToDateTime(Request.QueryString["startDate"].ToString());
            DateTime endDate   = Convert.ToDateTime(Request.QueryString["enddate"].ToString());


            objBL = new BusinessLogic(ConfigurationManager.ConnectionStrings[Request.Cookies["Company"].Value].ToString());

            dt.Columns.Add(new DataColumn("Category"));
            dt.Columns.Add(new DataColumn("Brand"));
            dt.Columns.Add(new DataColumn("Product Name"));
            dt.Columns.Add(new DataColumn("Model"));
            dt.Columns.Add(new DataColumn("Branchcode"));
            dt.Columns.Add(new DataColumn("Qty"));
            dt.Columns.Add(new DataColumn("Rate"));
            dt.Columns.Add(new DataColumn("Amount"));

            ds = objBL.getPurchasereport(startDate, endDate, Category, brand, product, Branch);
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr_final11 = dt.NewRow();
                dt.Rows.Add(dr_final11);

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    fLvlValueTemp = dr["CategoryName"].ToString().ToUpper().Trim();
                    sLvlValueTemp = dr["Brand"].ToString().ToUpper().Trim();
                    tLvlValueTemp = dr["ProductName"].ToString().ToUpper().Trim();

                    if ((fLvlValue != "" && fLvlValue != fLvlValueTemp) ||
                        (sLvlValue != "" && sLvlValue != sLvlValueTemp) ||
                        (tLvlValue != "" && tLvlValue != tLvlValueTemp))
                    {
                        DataRow dr_final889 = dt.NewRow();
                        dt.Rows.Add(dr_final889);

                        DataRow dr_final8 = dt.NewRow();
                        dr_final8["Category"]     = "";
                        dr_final8["Brand"]        = "";
                        dr_final8["Product Name"] = "Total : " + tLvlValue;
                        dr_final8["Model"]        = "";
                        dr_final8["Qty"]          = "";
                        dr_final8["Rate"]         = "";
                        dr_final8["Amount"]       = Convert.ToString(Convert.ToDecimal(producttot));
                        producttot = 0;
                        dt.Rows.Add(dr_final8);

                        DataRow dr_final888 = dt.NewRow();
                        dt.Rows.Add(dr_final888);
                    }

                    if ((fLvlValue != "" && fLvlValue != fLvlValueTemp) ||
                        (sLvlValue != "" && sLvlValue != sLvlValueTemp))
                    {
                        DataRow dr_final8 = dt.NewRow();
                        dr_final8["Category"]     = "";
                        dr_final8["Brand"]        = "Total : " + sLvlValue;
                        dr_final8["Product Name"] = "";
                        dr_final8["Model"]        = "";
                        dr_final8["Qty"]          = "";
                        dr_final8["Rate"]         = "";
                        dr_final8["Amount"]       = Convert.ToString(Convert.ToDecimal(brandTotal));
                        brandTotal = 0;
                        dt.Rows.Add(dr_final8);

                        DataRow dr_final888 = dt.NewRow();
                        dt.Rows.Add(dr_final888);
                    }

                    if (fLvlValue != "" && fLvlValue != fLvlValueTemp)
                    {
                        //DataRow dr_final889 = dt.NewRow();
                        //dt.Rows.Add(dr_final889);

                        DataRow dr_final8 = dt.NewRow();
                        dr_final8["Category"]     = "Total : " + fLvlValue;
                        dr_final8["Brand"]        = "";
                        dr_final8["Product Name"] = "";
                        dr_final8["Model"]        = "";
                        dr_final8["Qty"]          = "";
                        dr_final8["Rate"]         = "";
                        dr_final8["Amount"]       = Convert.ToString(Convert.ToDecimal(catTotal));
                        catTotal = 0;
                        dt.Rows.Add(dr_final8);

                        DataRow dr_final888 = dt.NewRow();
                        dt.Rows.Add(dr_final888);
                    }
                    fLvlValue = fLvlValueTemp;
                    sLvlValue = sLvlValueTemp;
                    tLvlValue = tLvlValueTemp;

                    DataRow dr_final12 = dt.NewRow();
                    dr_final12["Category"]     = dr["Categoryname"];
                    dr_final12["Brand"]        = dr["Brand"];
                    dr_final12["Branchcode"]   = dr["Branchcode"];
                    dr_final12["Product Name"] = dr["ProductName"];
                    dr_final12["Model"]        = dr["model"];
                    dr_final12["Qty"]          = dr["Qty"];
                    dr_final12["Rate"]         = dr["purchaseRate"];
                    dr_final12["Amount"]       = Convert.ToDouble(dr["Amount"]);
                    brandTotal = brandTotal + (Convert.ToDouble(dr["Amount"]));
                    catTotal   = catTotal + (Convert.ToDouble(dr["Amount"]));
                    producttot = producttot + (Convert.ToDouble(dr["Amount"]));
                    total      = total + (Convert.ToDouble(dr["Amount"]));
                    dt.Rows.Add(dr_final12);
                }
            }

            DataRow dr_final879 = dt.NewRow();
            dt.Rows.Add(dr_final879);

            DataRow dr_final88 = dt.NewRow();
            dr_final88["Category"]     = "";
            dr_final88["Brand"]        = "";
            dr_final88["Product Name"] = "Total : " + tLvlValueTemp;
            dr_final88["Model"]        = "";
            dr_final88["Qty"]          = "";
            dr_final88["Rate"]         = "";
            dr_final88["Amount"]       = Convert.ToString(Convert.ToDecimal(producttot));
            dt.Rows.Add(dr_final88);

            DataRow dr_final79 = dt.NewRow();
            dt.Rows.Add(dr_final79);

            DataRow dr_final89 = dt.NewRow();
            dr_final88["Category"]     = "";
            dr_final89["Brand"]        = "Total : " + sLvlValueTemp;
            dr_final89["Product Name"] = "";
            dr_final89["Model"]        = "";
            dr_final89["Qty"]          = "";
            dr_final89["Rate"]         = "";
            dr_final89["Amount"]       = Convert.ToString(Convert.ToDecimal(brandTotal));
            dt.Rows.Add(dr_final89);

            DataRow dr_final8879 = dt.NewRow();
            dt.Rows.Add(dr_final8879);

            DataRow dr_final869 = dt.NewRow();
            dr_final869["Category"]     = "Total : " + fLvlValueTemp;
            dr_final869["Brand"]        = "";
            dr_final869["Product Name"] = "";
            dr_final869["Model"]        = "";
            dr_final869["Qty"]          = "";
            dr_final869["Rate"]         = "";
            dr_final869["Amount"]       = Convert.ToString(Convert.ToDecimal(catTotal));
            dt.Rows.Add(dr_final869);

            DataRow dr_final9 = dt.NewRow();
            dt.Rows.Add(dr_final9);

            DataRow dr_final789 = dt.NewRow();
            dr_final88["Category"]      = "";
            dr_final789["Brand"]        = "Grand Total : ";
            dr_final789["Product Name"] = "";
            dr_final789["Model"]        = "";
            dr_final789["Qty"]          = "";
            dr_final789["Rate"]         = "";
            dr_final789["Amount"]       = Convert.ToString(Convert.ToDecimal(total));
            dt.Rows.Add(dr_final789);

            if (ds.Tables[0].Rows.Count > 0)
            {
                // ExportToExcel(dt);
                dstt.Tables.Add(dt);
                ReportsBL.ReportClass rptStock = new ReportsBL.ReportClass();
                // DataSet ds = new DataSet(sDataSource);
                //  DataSet ds = rptStock.getCategory(sDataSource);
                Grdreport.Visible    = true;
                Grdreport.DataSource = dstt;
                Grdreport.DataBind();
            }
            else
            {
                ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
            }
        }
        catch (Exception ex)
        {
            TroyLiteExceptionManager.HandleException(ex);
        }
    }
    protected void Page_Load(object sender, EventArgs e)
    {

        try
        {
            sDataSource = ConfigurationManager.ConnectionStrings[Request.Cookies["Company"].Value].ToString();
            Connection = Request.Cookies["Company"].Value;
            if (!IsPostBack)
            {
                lblHeading.Text = "Zero / 1 Rupee Value Sales Report";
                DataSet companyInfo = new DataSet();
                BusinessLogic bl = new BusinessLogic(sDataSource);
                if (Request.Cookies["Company"] != null)
                {
                    companyInfo = bl.getCompanyInfo(Request.Cookies["Company"].Value);

                    if (companyInfo != null)
                    {
                        if (companyInfo.Tables[0].Rows.Count > 0)
                        {
                            foreach (DataRow dr in companyInfo.Tables[0].Rows)
                            {
                                lblTNGST.Text = Convert.ToString(dr["TINno"]);
                                lblCompany.Text = Convert.ToString(dr["CompanyName"]);
                                lblPhone.Text = Convert.ToString(dr["Phone"]);
                                lblGSTno.Text = Convert.ToString(dr["GSTno"]);

                                lblAddress.Text = Convert.ToString(dr["Address"]);
                                lblCity.Text = Convert.ToString(dr["city"]);
                                lblPincode.Text = Convert.ToString(dr["Pincode"]);
                                lblState.Text = Convert.ToString(dr["state"]);

                            }
                        }
                    }
                    DataSet ds1 = bl.getImageInfo();
                    if (ds1 != null)
                    {
                        if (ds1.Tables[0].Rows.Count > 0)
                        {
                            for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
                            {
                                Image1.ImageUrl = "App_Themes/NewTheme/images/" + ds1.Tables[0].Rows[i]["img_filename"];
                                Image1.Height = 95;
                                Image1.Width = 95;
                            }
                        }
                        else
                        {
                            Image1.Height = 95;
                            Image1.Width = 95;
                            Image1.ImageUrl = "App_Themes/NewTheme/images/TESTLogo.png";
                        }
                    }
                }
            }

            lblBillDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
            //txtStartDate.Text = DateTime.Now.ToString("dd/MM/yyyy");

            DateTime indianStd = TimeZoneInfo.ConvertTimeBySystemTimeZoneId(DateTime.Now, "India Standard Time");
            string dtaa = Convert.ToDateTime(indianStd).ToString("dd/MM/yyyy");
            // txtStartDate.Text = dtaa;

            //  lblHeadDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
            //string sDataSource = Server.MapPath("App_Data\\Store0910.mdb");
            //string sDataSource = Server.MapPath(ConfigurationSettings.AppSettings["DataSource"].ToString());



            divPrint.Visible = true;
            divPr.Visible = true;
            double tot = 0;
            DataSet dstt = new DataSet();
            DataSet ds = new DataSet();
            DataSet dsttt = new DataSet();
            string connection = Request.Cookies["Company"].Value;

            string Method = Convert.ToString(Request.QueryString["Method"].ToString());
            DateTime startdate = Convert.ToDateTime(Request.QueryString["startdate"].ToString());
            DateTime enddate = Convert.ToDateTime(Request.QueryString["enddate"].ToString());
            string branch = Convert.ToString(Request.QueryString["Branch"].ToString());
            int value = Convert.ToInt32(Request.QueryString["Value"].ToString());

            ds = objBL.getzerosalesreport(connection,sDataSource, startdate, enddate, Method, value, branch);

            DataTable dt = new DataTable("Sales");

            if (ds != null)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    dt.Columns.Add(new DataColumn("Brand"));
                    dt.Columns.Add(new DataColumn("ProductName"));
                    dt.Columns.Add(new DataColumn("Model"));
                    dt.Columns.Add(new DataColumn("ItemCode"));
                    dt.Columns.Add(new DataColumn("BillNo"));
                    dt.Columns.Add(new DataColumn("BillDate"));
                    dt.Columns.Add(new DataColumn("LedgerName"));
                    dt.Columns.Add(new DataColumn("LedgerDetails"));
                    dt.Columns.Add(new DataColumn("Internal Transfer"));
                    dt.Columns.Add(new DataColumn("Delivery Note"));
                    dt.Columns.Add(new DataColumn("Purchase Return"));
                    dt.Columns.Add(new DataColumn("Qty"));
                  //  dt.Columns.Add(new DataColumn("Total"));

                    DataRow dr_export1 = dt.NewRow();
                    dt.Rows.Add(dr_export1);

                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        DataRow dr_final6 = dt.NewRow();
                        dr_final6["Brand"] = dr["brand"];
                        dr_final6["ProductName"] = dr["ProductName"];
                        dr_final6["Model"] = dr["Model"];
                        dr_final6["ItemCode"] = dr["Itemcode"];
                        dr_final6["BillNo"] = dr["BillNo"];
                        dr_final6["BillDate"] = dr["BillDate"];
                        dr_final6["LedgerName"] = dr["LedgerName"];
                        dr_final6["LedgerDetails"] = dr["customeraddress"];
                        dr_final6["Internal Transfer"] = dr["InternalTransfer"];
                        dr_final6["Delivery Note"] = dr["DeliveryNote"];
                        dr_final6["Purchase Return"] = dr["PurchaseReturn"];
                        dr_final6["Qty"] = dr["Qty"];
                      //  dr_final6["Total"] = dr["Qty"];

                        tot = tot + Convert.ToDouble(dr["Qty"]);
                        dt.Rows.Add(dr_final6);
                    }

                        DataRow dr_export2 = dt.NewRow();
                        dr_export2["Brand"] = "Total";
                        dr_export2["ProductName"] = "";
                        dr_export2["Model"] = "";
                        dr_export2["ItemCode"] = "";
                        dr_export2["BillNo"] = "";
                        dr_export2["BillDate"] = "";
                        dr_export2["LedgerName"] = "";
                        dr_export2["LedgerDetails"] = "";
                        dr_export2["Internal Transfer"] = "";
                        dr_export2["Delivery Note"] = "";
                        dr_export2["Purchase Return"] = "";
                        dr_export2["Qty"] = tot;

                        dt.Rows.Add(dr_export2);

                       
                    

                  
                  //  ExportToExcel(dt);
                    dstt.Tables.Add(dt);
                    Grdreport.Visible = true;
                    Grdreport.DataSource = dt;
                    Grdreport.DataBind();
                }
                else
                {
                    ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
                }
            }
            else
            {
                ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
            }


        }

        catch (Exception ex)
        {
            TroyLiteExceptionManager.HandleException(ex);
        }
    }
    ////   private string sDataSource = string.Empty;
    //   private string Connection = string.Empty;
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            sDataSource = ConfigurationManager.ConnectionStrings[Request.Cookies["Company"].Value].ToString();
            Connection  = Request.Cookies["Company"].Value;
            if (!IsPostBack)
            {
                DataSet       companyInfo = new DataSet();
                BusinessLogic bl          = new BusinessLogic(sDataSource);
                if (Request.Cookies["Company"] != null)
                {
                    companyInfo = bl.getCompanyInfo(Request.Cookies["Company"].Value);

                    if (companyInfo != null)
                    {
                        if (companyInfo.Tables[0].Rows.Count > 0)
                        {
                            foreach (DataRow dr in companyInfo.Tables[0].Rows)
                            {
                                lblTNGST.Text   = Convert.ToString(dr["TINno"]);
                                lblCompany.Text = Convert.ToString(dr["CompanyName"]);
                                lblPhone.Text   = Convert.ToString(dr["Phone"]);
                                lblGSTno.Text   = Convert.ToString(dr["GSTno"]);

                                lblAddress.Text = Convert.ToString(dr["Address"]);
                                lblCity.Text    = Convert.ToString(dr["city"]);
                                lblPincode.Text = Convert.ToString(dr["Pincode"]);
                                lblState.Text   = Convert.ToString(dr["state"]);
                            }
                        }
                    }
                    DataSet ds1 = bl.getImageInfo();
                    if (ds1 != null)
                    {
                        if (ds1.Tables[0].Rows.Count > 0)
                        {
                            for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
                            {
                                Image1.ImageUrl = "App_Themes/NewTheme/images/" + ds1.Tables[0].Rows[i]["img_filename"];
                                Image1.Height   = 95;
                                Image1.Width    = 114;
                            }
                        }
                        else
                        {
                            Image1.Height   = 95;
                            Image1.Width    = 114;
                            Image1.ImageUrl = "App_Themes/NewTheme/images/TESTLogo.png";
                        }
                    }
                }
            }

            lblBillDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
            //txtStartDate.Text = DateTime.Now.ToString("dd/MM/yyyy");

            DateTime indianStd = TimeZoneInfo.ConvertTimeBySystemTimeZoneId(DateTime.Now, "India Standard Time");
            string   dtaa      = Convert.ToDateTime(indianStd).ToString("dd/MM/yyyy");
            // txtStartDate.Text = dtaa;

            //  lblHeadDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
            //string sDataSource = Server.MapPath("App_Data\\Store0910.mdb");
            //string sDataSource = Server.MapPath(ConfigurationSettings.AppSettings["DataSource"].ToString());



            divPrint.Visible = true;
            divPr.Visible    = true;
            DataSet dstt       = new DataSet();
            string  connection = Request.Cookies["Company"].Value;

            string branch = Convert.ToString(Request.QueryString["Branch"].ToString());
            string method = Convert.ToString(Request.QueryString["Method"].ToString());

            if (method == "All")
            {
                lblHeading.Text = "All ItemList";
            }
            else if (method == "Absolute")
            {
                lblHeading.Text = "Obsolete ItemList";
            }
            else
            {
                lblHeading.Text = "Other Than Obsolete";
            }

            cond1 = Request.QueryString["cond1"].ToString();
            cond1 = Server.UrlDecode(cond1);
            string field = string.Empty;

            //  DataSet ds = objBL.GetAbsoluteProductlist(sDataSource, field, method, branch);
            //   bool IsPro= = Convert.ToBoolean(Request.QueryString["Method"].ToString());

            DataSet ds = new DataSet();


            ds = objBL.GetAbsoluteProductlist(sDataSource, connection, field, method, branch);

            DataTable dt = new DataTable();

            if (ds != null)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    dt.Columns.Add(new DataColumn("Brand"));
                    dt.Columns.Add(new DataColumn("ProductName"));
                    dt.Columns.Add(new DataColumn("ItemCode"));
                    dt.Columns.Add(new DataColumn("Model"));
                    dt.Columns.Add(new DataColumn("Rol"));
                    dt.Columns.Add(new DataColumn("Stock"));
                    dt.Columns.Add(new DataColumn("Branchcode"));

                    char[]   commaSeparator = new char[] { ',' };
                    string[] result;
                    result = cond1.Split(commaSeparator, StringSplitOptions.None);

                    foreach (string str in result)
                    {
                        dt.Columns.Add(new DataColumn(str));
                    }
                    dt.Columns.Remove("Column1");



                    DataRow dr_final123 = dt.NewRow();
                    dt.Rows.Add(dr_final123);

                    DataSet dst = new DataSet();

                    string itemcode = "";

                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        itemcode = Convert.ToString(dr["itemcode"]);

                        dst = objBL.GetAbsoluteProductpricelist(sDataSource, connection, itemcode, branch);


                        DataRow dr_final6 = dt.NewRow();
                        dr_final6["Brand"]       = dr["brand"];
                        dr_final6["ProductName"] = dr["ProductName"];
                        dr_final6["Model"]       = dr["Model"];
                        dr_final6["ItemCode"]    = dr["Itemcode"];

                        dr_final6["Rol"] = dr["Rol"];


                        if (dst != null)
                        {
                            if (dst.Tables[0].Rows.Count > 0)
                            {
                                foreach (DataRow drt in dst.Tables[0].Rows)
                                {
                                    dr_final6["Stock"]      = drt["Stock"];
                                    dr_final6["Branchcode"] = drt["Branchcode"];


                                    char[]   commaSeparator2 = new char[] { ',' };
                                    string[] result2;
                                    result2 = cond1.Split(commaSeparator, StringSplitOptions.None);

                                    foreach (string str2 in result2)
                                    {
                                        string item1   = str2;
                                        string item123 = Convert.ToString(drt["pricename"]);

                                        if (item123 == item1)
                                        {
                                            dr_final6[item1] = drt["price"];
                                        }
                                    }
                                }
                            }
                        }


                        dt.Rows.Add(dr_final6);
                    }



                    dstt.Tables.Add(dt);
                }
                else
                {
                    ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
                }
            }
            else
            {
                ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
            }



            ReportsBL.ReportClass rptStock = new ReportsBL.ReportClass();
            // DataSet ds = new DataSet(sDataSource);
            //  DataSet ds = rptStock.getCategory(sDataSource);
            Grdreport.Visible    = true;
            Grdreport.DataSource = dstt;
            Grdreport.DataBind();

            // div1.Visible = false;
        }
        catch (Exception ex)
        {
            TroyLiteExceptionManager.HandleException(ex);
        }
    }
    public void bindDatabrand(string sDataSource)
    {
        string trange  = string.Empty;
        string toption = string.Empty;

        DataSet ds = new DataSet();

        string   Branch   = Convert.ToString(Request.QueryString["Branch"].ToString());
        int      ttrange  = Convert.ToInt32(Request.QueryString["range"].ToString());
        int      ttoption = Convert.ToInt32(Request.QueryString["option"].ToString());
        DateTime refDate  = Convert.ToDateTime(Request.QueryString["refdate"].ToString());

        int tstock = 0;
        int trol   = 0;

        //  string Branch = drpBranchAdd.SelectedValue;

        ds = objBL.getstocklevelbrand(sDataSource, refDate, trange, toption, Branch);

        DataSet dst = new DataSet();

        string itemcode = string.Empty;

        DataTable dt = new DataTable();

        if (ds != null)
        {
            if (ds.Tables[0].Rows.Count > 0)
            {
                //dt.Columns.Add(new DataColumn("Category"));
                dt.Columns.Add(new DataColumn("Brand"));
                //dt.Columns.Add(new DataColumn("ProductName"));
                //dt.Columns.Add(new DataColumn("Model"));
                //dt.Columns.Add(new DataColumn("ItemCode"));
                dt.Columns.Add(new DataColumn("Brand %"));
                dt.Columns.Add(new DataColumn("Stock"));

                dt.Columns.Add(new DataColumn("Branchcode"));
                //  dt.Columns.Add(new DataColumn("Stock Value"));

                //foreach (ListItem listItem1 in lstPricelist.Items)
                //{
                //    if (listItem1.Selected)
                //    {
                //        string item1 = listItem1.Value + " Value %";

                //        dt.Columns.Add(new DataColumn(item1));
                //    }
                //}


                cond1 = Request.QueryString["cond1"].ToString();
                cond1 = Server.UrlDecode(cond1);

                char[]   commaSeparator = new char[] { ',' };
                string[] result;
                result = cond1.Split(commaSeparator, StringSplitOptions.None);

                foreach (string str in result)
                {
                    string strr = str + " Value %";

                    dt.Columns.Add(new DataColumn(strr));
                }
                dt.Columns.Remove(" Value %");

                DataRow dr_final111 = dt.NewRow();
                dt.Rows.Add(dr_final111);

                //if (ds.Tables[0].Rows.Count > 0)
                //{
                //    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                //    {
                //        tstock = Convert.ToInt32(ds.Tables[0].Rows[i]["Stock"]);
                //        if (tstock == 0)
                //        {
                //            ds.Tables[0].Rows[i].Delete();
                //        }
                //    }
                //    ds.Tables[0].AcceptChanges();
                //}



                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        brand = Convert.ToString(dr["BrandName"]);

                        //dst = objBL.GetAbsoluteProductpricelist(sDataSource, itemcode, Branch);

                        DataRow dr_final6 = dt.NewRow();
                        //dr_final6["Category"] = dr["Categoryname"];
                        dr_final6["Brand"] = dr["brandname"];
                        //dr_final6["ProductName"] = dr["ProductName"];
                        //dr_final6["Model"] = dr["Model"];
                        dr_final6["BranchCode"] = Branch;
                        dr_final6["Brand %"]    = dr["brandlevel"];
                        dr_final6["Stock"]      = dr["Stock"];

                        //if (dst != null)
                        //{
                        //    if (dst.Tables[0].Rows.Count > 0)
                        //    {
                        //        foreach (DataRow drt in dst.Tables[0].Rows)
                        //        {
                        //            dr_final6["Branchcode"] = drt["Branchcode"];

                        //            foreach (ListItem listItem1 in lstPricelist.Items)
                        //            {
                        //                if (listItem1.Selected)
                        //                {
                        //                    string item1 = listItem1.Value;
                        //                    string item123 = Convert.ToString(drt["pricename"]);

                        //                    if (item123 == item1)
                        //                    {
                        //                        dr_final6[item1] = drt["price"];
                        //                    }

                        //                }
                        //            }
                        //        }
                        //    }
                        //}
                        string connection = Request.Cookies["Company"].Value;

                        char[]   commaSeparator2 = new char[] { ',' };
                        string[] result2;
                        result2 = cond1.Split(commaSeparator, StringSplitOptions.None);

                        foreach (string str2 in result2)
                        {
                            //if (str2)
                            //{
                            dst = objBL.GetAbsoluteCategorypricelist1(connection, sDataSource, brand, Branch, "brand", str2);

                            if (dst != null)
                            {
                                if (dst.Tables[0].Rows.Count > 0)
                                {
                                    foreach (DataRow drt in dst.Tables[0].Rows)
                                    {
                                        string item1 = str2 + " Value %";
                                        //string item123 = Convert.ToString(drt["pricename"]);

                                        //if (item123 == item1)
                                        //{
                                        dr_final6[item1] = Convert.ToDouble(drt["price"]) / 100;
                                        //}
                                    }
                                }
                                //}
                            }
                        }

                        dt.Rows.Add(dr_final6);
                    }
                    DataSet dstt = new DataSet();
                    dstt.Tables.Add(dt);
                    double troll;


                    if (dstt.Tables[0].Rows.Count > 0)
                    {
                        if (ttrange == 1)
                        {
                            for (int i = 0; i < dstt.Tables[0].Rows.Count; i++)
                            {
                                tstock = Convert.ToInt32(dstt.Tables[0].Rows[i]["Stock"]);
                                trol   = Convert.ToInt32(dstt.Tables[0].Rows[i]["brand %"]);

                                troll = Convert.ToDouble(dstt.Tables[0].Rows[i]["price"]) / 100;
                                if (tstock > trol)
                                {
                                    dstt.Tables[0].Rows[i].Delete();
                                }
                            }
                            dstt.Tables[0].AcceptChanges();
                        }
                        else if (ttrange == 2)
                        {
                            for (int i = 0; i < dstt.Tables[0].Rows.Count; i++)
                            {
                                tstock = Convert.ToInt32(dstt.Tables[0].Rows[i]["Stock"]);
                                trol   = Convert.ToInt32(dstt.Tables[0].Rows[i]["brand %"]);

                                troll = Convert.ToDouble(dstt.Tables[0].Rows[i]["price"]) / 100;
                                if (tstock < trol)
                                {
                                    dstt.Tables[0].Rows[i].Delete();
                                }
                            }
                            dstt.Tables[0].AcceptChanges();
                        }
                        else if (ttrange == 3)
                        {
                            for (int i = 0; i < dstt.Tables[0].Rows.Count; i++)
                            {
                                tstock = Convert.ToInt32(dstt.Tables[0].Rows[i]["Stock"]);
                                trol   = Convert.ToInt32(dstt.Tables[0].Rows[i]["brand %"]);

                                troll = Convert.ToDouble(dstt.Tables[0].Rows[i]["price"]) / 100;
                                if ((trol < 0) || (trol > 0))
                                {
                                    dstt.Tables[0].Rows[i].Delete();
                                }
                            }
                            dstt.Tables[0].AcceptChanges();
                        }
                    }
                    //DataTable dtt = dstt.Tables[0];
                    //DataSet dsttt = new DataSet();
                    //dsttt.Tables.Add(dtt);
                    // ExportToExcel(dt);
                    Grdreport.Visible    = true;
                    Grdreport.DataSource = dstt;
                    Grdreport.DataBind();
                    //  ExportToExcel(dtt);
                }
                else
                {
                    ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
                }
            }
            else
            {
                ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
            }
        }
        else
        {
            ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
        }
    }
    public void bindDataSubTotBranch(string cond)
    {
        DateTime startDate, endDate, Transdt;

        startDate = Convert.ToDateTime(Request.QueryString["startdate"].ToString());
        endDate   = Convert.ToDateTime(Request.QueryString["enddate"].ToString());
        string condtion = "";

        condtion = getCond();
        DataSet   ds = new DataSet();
        DataTable dt = new DataTable();

        objBL = new BusinessLogic(ConfigurationManager.ConnectionStrings[Request.Cookies["Company"].Value].ToString());
        //DataSet dsGird = GenerateGridColumns();
        DataSet dst   = new DataSet();
        DataSet dts   = new DataSet();
        DataSet dstt  = new DataSet();
        DataSet dsGir = new DataSet();
        DataSet dsG   = new DataSet();

        dst = objBL.gettotalBranchsales(startDate, endDate);

        dts = objBL.gettotalBranchsalesdate(startDate, endDate);


        if (dts.Tables[0].Rows.Count > 0)
        {
            dstt = objBL.ListBranch();
            DataTable  dtt = new DataTable();
            DataColumn dc;
            if (dstt.Tables[0].Rows.Count > 0)
            {
                dc = new DataColumn("Date");
                dtt.Columns.Add(dc);
                for (int i = 0; i < dstt.Tables[0].Rows.Count; i++)
                {
                    string ledger = dstt.Tables[0].Rows[i]["Branchcode"].ToString();
                    dc = new DataColumn(ledger);
                    dtt.Columns.Add(dc);
                }
                dc = new DataColumn("Total");
                dtt.Columns.Add(dc);
            }
            dsGir.Tables.Add(dtt);


            DataRow dr_final14 = dtt.NewRow();
            dtt.Rows.Add(dr_final14);

            double credit = 0.00;
            double Tottot = 0.00;

            DateTime Transd = Convert.ToDateTime(Request.QueryString["startdate"].ToString());


            foreach (DataRow dre in dts.Tables[0].Rows)
            {
                DataRow dr_final12 = dtt.NewRow();
                Transdt = Convert.ToDateTime(dre["BillDate"]);

                credit = 0.00;

                foreach (DataRow dr in dst.Tables[0].Rows)
                {
                    Transd = Convert.ToDateTime(dr["BillDate"]);
                    if (Transdt == Transd)
                    {
                        string aa   = dr["BillDate"].ToString().ToUpper().Trim();
                        string dtaa = Convert.ToDateTime(aa).ToString("dd/MM/yyyy");
                        dr_final12["Date"] = dtaa;

                        string ledgernam = dr["Branchcode"].ToString().ToUpper().Trim();
                        //  Tottot = Tottot + double.Parse(dr["Quantity"].ToString());
                        for (int ii = 1; ii < dsGir.Tables[0].Columns.Count; ii++)
                        {
                            string ledgerna = dsGir.Tables[0].Columns[ii].ToString();

                            if (ledgernam == ledgerna)
                            {
                                dr_final12[ledgerna] = double.Parse(dr["Quantity"].ToString());
                                credit = credit + double.Parse(dr["Quantity"].ToString());
                                Tottot = Tottot + double.Parse(dr["Quantity"].ToString());
                            }
                            else
                            {
                                if (dr_final12[ledgerna] == null)
                                {
                                    dr_final12[ledgerna] = "";
                                }
                            }
                        }
                        dr_final12["Total"] = credit;
                    }
                }
                dtt.Rows.Add(dr_final12);
            }

            DataRow dr_final11 = dtt.NewRow();
            dtt.Rows.Add(dr_final11);

            DataRow dr_final88 = dtt.NewRow();
            dr_final88["Date"]  = "Total";
            dr_final88["Total"] = Tottot;
            dtt.Rows.Add(dr_final88);

            Grdreport.Visible    = true;
            Grdreport.DataSource = dtt;
            Grdreport.DataBind();

            // ExportToExcel(dtt);
        }
        else
        {
            ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
        }
    }
    ////   private string sDataSource = string.Empty;
    //   private string Connection = string.Empty;
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            sDataSource = ConfigurationManager.ConnectionStrings[Request.Cookies["Company"].Value].ToString();
            Connection  = Request.Cookies["Company"].Value;
            if (!IsPostBack)
            {
                DataSet       companyInfo = new DataSet();
                BusinessLogic bl          = new BusinessLogic(sDataSource);
                if (Request.Cookies["Company"] != null)
                {
                    lblHeading.Text = "Bank Reconciliation Report ";
                    companyInfo     = bl.getCompanyInfo(Request.Cookies["Company"].Value);

                    if (companyInfo != null)
                    {
                        if (companyInfo.Tables[0].Rows.Count > 0)
                        {
                            foreach (DataRow dr in companyInfo.Tables[0].Rows)
                            {
                                lblTNGST.Text   = Convert.ToString(dr["TINno"]);
                                lblCompany.Text = Convert.ToString(dr["CompanyName"]);
                                lblPhone.Text   = Convert.ToString(dr["Phone"]);
                                lblGSTno.Text   = Convert.ToString(dr["GSTno"]);

                                lblAddress.Text = Convert.ToString(dr["Address"]);
                                lblCity.Text    = Convert.ToString(dr["city"]);
                                lblPincode.Text = Convert.ToString(dr["Pincode"]);
                                lblState.Text   = Convert.ToString(dr["state"]);
                            }
                        }
                    }
                    DataSet ds1 = bl.getImageInfo();
                    if (ds1 != null)
                    {
                        if (ds1.Tables[0].Rows.Count > 0)
                        {
                            for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
                            {
                                Image1.ImageUrl = "App_Themes/NewTheme/images/" + ds1.Tables[0].Rows[i]["img_filename"];
                                Image1.Height   = 95;
                                Image1.Width    = 114;
                            }
                        }
                        else
                        {
                            Image1.Height   = 95;
                            Image1.Width    = 114;
                            Image1.ImageUrl = "App_Themes/NewTheme/images/TESTLogo.png";
                        }
                    }
                }
            }

            lblBillDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
            //txtStartDate.Text = DateTime.Now.ToString("dd/MM/yyyy");

            DateTime indianStd = TimeZoneInfo.ConvertTimeBySystemTimeZoneId(DateTime.Now, "India Standard Time");
            string   dtaa      = Convert.ToDateTime(indianStd).ToString("dd/MM/yyyy");
            // txtStartDate.Text = dtaa;

            //  lblHeadDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
            //string sDataSource = Server.MapPath("App_Data\\Store0910.mdb");
            //string sDataSource = Server.MapPath(ConfigurationSettings.AppSettings["DataSource"].ToString());



            divPrint.Visible = true;
            divPr.Visible    = true;
            DataSet dstt       = new DataSet();
            DataSet ds         = new DataSet();
            DataSet dsttt      = new DataSet();
            string  connection = Request.Cookies["Company"].Value;

            string btnlist   = Convert.ToString(Request.QueryString["list"].ToString());
            int    iLedgerID = Convert.ToInt32(Request.QueryString["ledger"].ToString());
            string Types     = Convert.ToString(Request.QueryString["type"].ToString());


            objBL = new BusinessLogic(ConfigurationManager.ConnectionStrings[Request.Cookies["Company"].Value].ToString());

            //startDate = Convert.ToDateTime(txtSrtDate.Text);

            string usernam = Request.Cookies["LoggedUserName"].Value;

            if (btnlist == "All")
            {
                dsttt = objBL.checkbankreconciliation1(iLedgerID, sDataSource, usernam, Types);

                if (dsttt != null)
                {
                    if (dsttt.Tables[0].Rows.Count > 0)
                    {
                        dsttt = objBL.getbankreconciliation2(iLedgerID, sDataSource, usernam);
                        ds    = objBL.getbankrecon1(iLedgerID, sDataSource, usernam, Types);

                        if (dsttt != null)
                        {
                            if (dsttt.Tables[0].Rows.Count > 0)
                            {
                                ds.Tables[0].Merge(dsttt.Tables[0]);
                            }
                        }
                    }
                    else
                    {
                        ds = objBL.getbankreconciliation3(iLedgerID, sDataSource, usernam);
                    }
                }
                else
                {
                    ds = objBL.getbankreconciliation3(iLedgerID, sDataSource, usernam);
                }

                if (ds != null)
                {
                    DataTable dt = new DataTable("bank ercon");
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        dt.Columns.Add(new DataColumn("TransNo"));
                        dt.Columns.Add(new DataColumn("TransDate"));
                        dt.Columns.Add(new DataColumn("Name"));
                        dt.Columns.Add(new DataColumn("Ledger Name"));
                        dt.Columns.Add(new DataColumn("Branch"));
                        dt.Columns.Add(new DataColumn("Amount"));
                        dt.Columns.Add(new DataColumn("Narration"));
                        dt.Columns.Add(new DataColumn("Voucher Type"));
                        dt.Columns.Add(new DataColumn("ChequeNo"));
                        dt.Columns.Add(new DataColumn("Reconcilated By"));
                        dt.Columns.Add(new DataColumn("Reconcilated Date"));
                        dt.Columns.Add(new DataColumn("Remarks"));

                        DataRow dr_final679 = dt.NewRow();
                        dt.Rows.Add(dr_final679);

                        foreach (DataRow dr in ds.Tables[0].Rows)
                        {
                            DataRow dr_final6 = dt.NewRow();
                            dr_final6["TransNo"]           = dr["TransNo"];
                            dr_final6["TransDate"]         = dr["TransDate"];
                            dr_final6["Name"]              = dr["Debtor"];
                            dr_final6["Ledger Name"]       = dr["Creditor"];
                            dr_final6["Branch"]            = dr["BranchCode"];
                            dr_final6["Amount"]            = dr["Amount"];
                            dr_final6["Narration"]         = dr["Narration"];
                            dr_final6["ChequeNo"]          = dr["ChequeNo"];
                            dr_final6["Voucher Type"]      = dr["VoucherType"];
                            dr_final6["Reconcilated By"]   = dr["ReconcilatedBy"];
                            dr_final6["Reconcilated Date"] = dr["Reconcilateddate"];
                            dr_final6["Remarks"]           = dr["Result"];
                            dt.Rows.Add(dr_final6);
                        }
                        // ExportToExcel(dt);
                        dstt.Tables.Add(dt);
                    }
                    else
                    {
                        ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
                    }
                }
                else
                {
                    ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
                }
            }
            else if (btnlist == "Pending")
            {
                dsttt = objBL.getbankreconciliation2(iLedgerID, sDataSource, usernam);

                if (dsttt != null)
                {
                    DataTable dt = new DataTable("Bank recon");
                    if (dsttt.Tables[0].Rows.Count > 0)
                    {
                        dt.Columns.Add(new DataColumn("TransNo"));
                        dt.Columns.Add(new DataColumn("TransDate"));
                        dt.Columns.Add(new DataColumn("Name"));
                        dt.Columns.Add(new DataColumn("Ledger Name"));
                        dt.Columns.Add(new DataColumn("Branch"));
                        dt.Columns.Add(new DataColumn("Amount"));
                        dt.Columns.Add(new DataColumn("Narration"));
                        dt.Columns.Add(new DataColumn("Voucher Type"));
                        dt.Columns.Add(new DataColumn("ChequeNo"));
                        dt.Columns.Add(new DataColumn("Reconcilated By"));
                        dt.Columns.Add(new DataColumn("Reconcilated Date"));
                        dt.Columns.Add(new DataColumn("Remarks"));

                        DataRow dr_final679 = dt.NewRow();
                        dt.Rows.Add(dr_final679);

                        foreach (DataRow dr in dsttt.Tables[0].Rows)
                        {
                            DataRow dr_final6 = dt.NewRow();
                            dr_final6["TransNo"]           = dr["TransNo"];
                            dr_final6["TransDate"]         = dr["TransDate"];
                            dr_final6["Name"]              = dr["Debtor"];
                            dr_final6["Ledger Name"]       = dr["Creditor"];
                            dr_final6["Branch"]            = dr["BranchCode"];
                            dr_final6["Amount"]            = dr["Amount"];
                            dr_final6["Narration"]         = dr["Narration"];
                            dr_final6["ChequeNo"]          = dr["ChequeNo"];
                            dr_final6["Voucher Type"]      = dr["VoucherType"];
                            dr_final6["Reconcilated By"]   = dr["ReconcilatedBy"];
                            dr_final6["Reconcilated Date"] = dr["Reconcilateddate"];
                            dr_final6["Remarks"]           = dr["Result"];
                            dt.Rows.Add(dr_final6);
                        }
                        // ExportToExcel(dt);
                        dstt.Tables.Add(dt);
                    }
                    else
                    {
                        ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
                    }
                }
                else
                {
                    ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
                }
            }
            else if (btnlist == "Reconciliated")
            {
                ds = objBL.getbankrecon1(iLedgerID, sDataSource, usernam, Types);

                if (ds != null)
                {
                    DataTable dt = new DataTable("Bank recon");
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        dt.Columns.Add(new DataColumn("TransNo"));
                        dt.Columns.Add(new DataColumn("TransDate"));
                        dt.Columns.Add(new DataColumn("Name"));
                        dt.Columns.Add(new DataColumn("Ledger Name"));
                        dt.Columns.Add(new DataColumn("BranchCode"));
                        dt.Columns.Add(new DataColumn("Amount"));
                        dt.Columns.Add(new DataColumn("Narration"));
                        dt.Columns.Add(new DataColumn("Voucher Type"));
                        dt.Columns.Add(new DataColumn("ChequeNo"));
                        dt.Columns.Add(new DataColumn("Reconcilated By"));
                        dt.Columns.Add(new DataColumn("Reconcilated Date"));
                        dt.Columns.Add(new DataColumn("Remarks"));

                        DataRow dr_final679 = dt.NewRow();
                        dt.Rows.Add(dr_final679);

                        foreach (DataRow dr in ds.Tables[0].Rows)
                        {
                            DataRow dr_final6 = dt.NewRow();
                            dr_final6["TransNo"]           = dr["TransNo"];
                            dr_final6["TransDate"]         = dr["TransDate"];
                            dr_final6["Name"]              = dr["Debtor"];
                            dr_final6["Ledger Name"]       = dr["Creditor"];
                            dr_final6["Branch"]            = dr["BranchCode"];
                            dr_final6["Amount"]            = dr["Amount"];
                            dr_final6["Narration"]         = dr["Narration"];
                            dr_final6["ChequeNo"]          = dr["ChequeNo"];
                            dr_final6["Voucher Type"]      = dr["VoucherType"];
                            dr_final6["Reconcilated By"]   = dr["ReconcilatedBy"];
                            dr_final6["Reconcilated Date"] = dr["Reconcilateddate"];
                            dr_final6["Remarks"]           = dr["Result"];
                            dt.Rows.Add(dr_final6);
                        }
                        // ExportToExcel(dt);
                        dstt.Tables.Add(dt);
                    }
                    else
                    {
                        ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
                    }
                }
                else
                {
                    ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
                }
            }
            if (dstt.Tables[0].Rows.Count > 0)
            {
                // ReportsBL.ReportClass rptStock = new ReportsBL.ReportClass();
                Grdreport.Visible    = true;
                Grdreport.DataSource = dstt;
                Grdreport.DataBind();
            }
            else
            {
                ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
            }
        }


        catch (Exception ex)
        {
            TroyLiteExceptionManager.HandleException(ex);
        }
    }