using System;
using System.Collections.Generic;
using System.Linq;
using System.Collections;
using System.Web;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Text;
using System.Configuration;
using MySql.Data.MySqlClient;

using CLS_BL;
using CLS_DL;
using CLS_PL;

using System.Web.UI.DataVisualization.Charting;
using System.Drawing.Printing;
using System.Drawing;
using System.Drawing.Text;
using System.Globalization;




namespace SUNWEB
{
    public partial class StockMovementNew : System.Web.UI.Page
    {

        BL_SELECT ObjSel = new BL_SELECT();
        BLCompany ObjBL = new BLCompany();
        PLCOMMON ObjPL = new PLCOMMON();
        BL_USER ObjUserBL = new BL_USER(); //This Object for All User and Page Related fuctions included 
        PLCUSTOMER ObjCustPL = new PLCUSTOMER();
        BL_ErrorLog ObjLog = new BL_ErrorLog();
        Int32 K = 0;
        Int32 L = 0;
        Int32 S = 0;
        DataTable DtK = new DataTable();
        string ConStr = ConfigurationManager.ConnectionStrings["ConMysql"].ConnectionString;
        string ConStr_Rds = ConfigurationManager.ConnectionStrings["ConRDS"].ConnectionString;
        string Curr_Date = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
        string Curr_DateOnly = System.DateTime.Now.ToString("yyyy-MM-dd");
        DateTime MonthstartDt;
        DataSet ds = new DataSet();
        MYSQLDB ObjMain = new MYSQLDB();
        DataTable DT = new DataTable();
        DataSet DS = new DataSet();
        DataTable Dt2 = new DataTable();
        DataTable DtLicense = new DataTable();
        DataTable DtSuperAdmin = new DataTable();
        DataTable DtMultiBranch = new DataTable();
        DataTable DtNormalUser = new DataTable();
        DataTable DTResult = new DataTable();
        DataTable dtTransdetailCosting = new DataTable(); //john added on 17-Jan-2025
        MySqlCommand Cmdv = new MySqlCommand();

        string ppp = string.Empty;

        string UserId = string.Empty;
        string UserName = string.Empty;
        string PortalTypeId = string.Empty;
        string cd_Id = string.Empty;
        string Datapath = string.Empty;
        string cd_BranchAlias = string.Empty;
        string IsMultiBranch = string.Empty;
        string IsSuperAdmin = string.Empty;
        string ActiveCallvalue = string.Empty;
        // License Variables ..
        string PID = string.Empty;
        string Page_Acess = string.Empty;
        string Read_Acess = string.Empty;
        string Write_Acess = string.Empty;
        string Isdowld_Acess = string.Empty;
        string Rcd_id = string.Empty;
        string RPortalid = string.Empty;
        string Useridvalue = string.Empty;
        string QueryCurr_Select = string.Empty;
        string L_MaxUserCnt = string.Empty;
        string L_Stockmovement = string.Empty;

        string Branchidval = string.Empty;
        string BranchFullName = string.Empty;
        string Curr_BranchFullName = string.Empty;
        string Cmb_BranchName = string.Empty;
        string HQ = string.Empty;
        string Cm_id = string.Empty;

        string Ins_TempQry = string.Empty;
        Int32 QRTemp = 0;
        // Index Variable 
        string Index_Qry = string.Empty;
        Int32 QR_Index = 0;

        // Index Drop 
        string DrpIndex_Qry = string.Empty;
        Int32 QR_DrpIndex = 0;
        string CQuery = string.Empty;
        string CDate = string.Empty;
        // Common Variable
        string Qrry = string.Empty;
        string Q2 = string.Empty;
        Int32 K2 = 0;
        string Q3 = string.Empty;
        Int32 K3 = 0;
        string Q4 = string.Empty;
        Int32 K4 = 0;
        string Q5 = string.Empty;
        Int32 K5 = 0;
        string Q6 = string.Empty;
        Int32 K6 = 0;
        string Q7 = string.Empty;
        Int32 K7 = 0;
        string Q8 = string.Empty;
        Int32 K8 = 0;
        string Q9 = string.Empty;
        Int32 K9 = 0;
        string Q10 = string.Empty;
        Int32 K10 = 0;
        string Q11 = string.Empty;
        Int32 K11 = 0;
        string Q12 = string.Empty;
        Int32 K12 = 0;
        string STR;
        string GroupTxt = string.Empty;
        string SubQryValue = string.Empty;
        string ClassValue = string.Empty;
        string ActiveInAct = string.Empty;
        string Vw_Chk = string.Empty;
        string RInventoryId = string.Empty;
        string BranchAlias = string.Empty;
        // Global Variable for Stock MomentReport 
        string currentId = string.Empty;

        decimal totalStockIN = 0;
        decimal SubtotalStockIN = 0;
        decimal TotalStockOUT = 0;

        decimal SubTotalStockOUT = 0;
        decimal totalStockCheck = 0;
        decimal SubtotalStockCheck = 0;
        int subTotalRowIndex = 0;
        int subTotalRowIndex1 = 0;
        string RBranchid = string.Empty;
        string Branchid = string.Empty;
        private object D1;

        // Added On 02-12-2021
        string ReadHQ = string.Empty;


        protected void Page_Load(object sender, EventArgs e)
        {
            PID = "16";    // StockMoment  : Live Testing 

            if (!IsPostBack)
            {
                txtStartDt.Text = Curr_DateOnly;
                txtEndDt.Text = Curr_DateOnly;
            }
            MonthstartDt = new DateTime(System.DateTime.Now.Year, System.DateTime.Now.Month, 1);
            if (Session["UserName"] == null)
            {
                Response.Redirect("Login.aspx");
            }
            if (((Convert.ToBoolean(Session["IsSuperAdmin"])) == true) && (Session["PortalTypeId"].ToString() == "") && ((Convert.ToBoolean(Session["IsMultiBranch"])) == true))
            {
                if (!IsPostBack)
                {
                    LoadBranchesOnID();
                    string QueryCal = "select ActiveValue from portaldb.usermaster  where Userid=" + Session["UserId"].ToString() + " ";
                    ActiveCallvalue = ObjMain.ExecuteScalar(QueryCal);
                    if (ActiveCallvalue != " " || ActiveCallvalue != "Select Branch")
                    {
                        ActiveCall();

                    }
                }
            }
            else if (((Convert.ToBoolean(Session["IsSuperAdmin"])) == true) && (Session["PortalTypeId"].ToString() == ""))
            {
                if (!IsPostBack)
                {
                    LoadBranchesOnID();
                }
                cd_BranchAlias = cmbBranches.SelectedItem.Text;
                //  Load Part : Function
            }
            else if (((Convert.ToBoolean(Session["IsMultiBranch"])) == true) && (Session["PortalTypeId"].ToString() == ""))
            {
                if (!IsPostBack)
                {
                    LoadBranchesOnID();
                    ActiveCall();
                }
            }
            else if (((Session["PortalTypeId"].ToString() != "") || (Convert.ToBoolean(Session["IsSuperAdmin"])) == false) && ((Convert.ToBoolean(Session["IsMultiBranch"])) == false))
            {
                cmbBranches.Enabled = false;
                cmbBranches.Visible = false;

                Cm_id = ObjSel.GETcmid(Session["Cd_Id"].ToString());  // Get cm_id
                HQ = ObjSel.Get_HQ(Cm_id);     // Get the HQ Value
                Curr_BranchFullName = ObjSel.GetBranchFullName(HQ, Session["Branch_HQ"].ToString());
                lblBranchAliasName.ForeColor = System.Drawing.Color.DodgerBlue;
                lblBranchAliasName.Text = "Branch Name" + "&nbsp;" + "-" + "&nbsp;" + Curr_BranchFullName + "&nbsp; ";

                cd_BranchAlias = Session["Branch_HQ"].ToString();
                Datapath = Session["DataPath"].ToString();
                PortalTypeId = Session["PortalTypeId"].ToString();

                DT = ObjUserBL.GetPageRightsDetailsPid(Session["UserId"].ToString(), PID);
                if (DT.Rows.Count > 0)
                {
                    Page_Acess = DT.Rows[0]["Acess_status"].ToString();
                    Read_Acess = DT.Rows[0]["Read_Access"].ToString();
                    Write_Acess = DT.Rows[0]["Write_Access"].ToString();
                    Isdowld_Acess = DT.Rows[0]["IDwld_Access"].ToString();

                    Session["Page_Acess"] = Page_Acess;
                    Session["Read_Acess"] = Read_Acess;
                    Session["Write_Acess"] = Write_Acess;
                    Session["Isdowld_Acess"] = Isdowld_Acess;
                }

                DT = ObjUserBL.GetPageRightsDetailsPIDNew(Session["UserId"].ToString(), PID, cmbBranches.SelectedItem.Value);
                if (DT.Rows.Count > 0)
                {
                    Page_Acess = DT.Rows[0]["Acess_status"].ToString();
                    Read_Acess = DT.Rows[0]["Read_Access"].ToString();
                    Write_Acess = DT.Rows[0]["Write_Access"].ToString();
                    Isdowld_Acess = DT.Rows[0]["IDwld_Access"].ToString();
                    Session["Page_Acess"] = Page_Acess;
                    Session["Read_Acess"] = Read_Acess;
                    Session["Write_Acess"] = Write_Acess;
                    Session["Isdowld_Acess"] = Isdowld_Acess;
                }
                BranchFullName = cmbBranches.SelectedItem.Text;    // This will Return Branch FullName 
                Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                Rcd_id = ObjMain.ExecuteScalar("select cd_id from portaldb.companydetails where cd_branchid=" + cmbBranches.SelectedItem.Value + " and cd_branchalias='" + Cmb_BranchName + "';");
                RPortalid = ObjMain.ExecuteScalar("select portaltypeid  from portaldb.invoicedetails where cd_id=" + Rcd_id + ";");
                DtLicense = ObjUserBL.GetLicenseDetails(RPortalid);
                if (DtLicense.Rows.Count > 0)
                {
                    L_MaxUserCnt = DtLicense.Rows[0]["UserCnt"].ToString();
                    L_Stockmovement = DtLicense.Rows[0]["STOCKMOVEMENT"].ToString();

                    // Create Session for License Variables 
                    Session["L_MaxUserCnt"] = L_MaxUserCnt;
                    Session["L_Stockmovement"] = L_Stockmovement;

                }

                if ((Session["L_Stockmovement"].ToString() == "N") || (Session["Page_Acess"].ToString() == "N"))
                {
                    Response.Redirect("DeniedPage.aspx");
                }
                else
                {

                    if (!IsPostBack)
                    {
                        if (Session["Isdowld_Acess"].ToString() == "N")
                        {
                            // btnExcel.Enabled = false;
                            Response.Write(@"<script language='javascript'>alert('No Access To download.')</script>");
                        }
                        else
                        {


                            if (cmbBranches.Visible == false)
                            {
                                //  LoadPart : Fuction 
                            }

                        }


                    }

                }

            }
            else
            {

            }

            //john added on 17-Jan-2025
            BranchFullName = cmbBranches.SelectedItem.Text;
            Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);
            dtTransdetailCosting = ObjSel.TransDetailCosting(Cmb_BranchName);
            string transdetailcosting = Newtonsoft.Json.JsonConvert.SerializeObject(dtTransdetailCosting);
            Session["tdc"] = transdetailcosting;
            ScriptManager.RegisterStartupScript(this, this.GetType(), "Script", " loadData('" + txtStartDt.Text + "','" + txtEndDt.Text + "','" + transdetailcosting  + "');", true); //john added on 16-Jan-2025 load data

        }

        public void LoadBranchesOnID()
        {
            DT = ObjUserBL.GetBranchIDList(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Session["CM_ID"].ToString());
            StringBuilder StrBObj = new StringBuilder();

            for (int i = 0; i < DT.Rows.Count; i++)
            {
                DataRow dr = DT.Rows[i];
                RBranchid = dr["Branchid"].ToString();
                if (StrBObj.Length != 0)
                {

                    RBranchid = "," + RBranchid;
                    StrBObj.Append("  " + RBranchid + "    ");
                }
                else
                {
                    Branchid = RBranchid;
                    StrBObj.Append("SELECT  Branchid ,BranchName,branchfullname,datapath FROM " + Session["Branch_HQ"].ToString() + ".branches where branchid in (1 , " + Branchid + " ");

                }


            }
            StrBObj.Append(")");
            Dt2 = ObjMain.GetData_DT(StrBObj.ToString());

            if (Dt2.Rows.Count > 0)
            {
                cmbBranches.DataSource = Dt2;
                cmbBranches.DataTextField = "branchfullname";
                cmbBranches.DataValueField = "Branchid";
                cmbBranches.DataBind();
                cmbBranches.Items.Insert(0, "Select Branch");
            }

        }


        protected void cmbBranches_SelectedIndexChanged(object sender, EventArgs e)
        {

            if (cmbBranches.SelectedIndex == 0)
            {
                Response.Write("ItemSale.aspx");
            }
            else
            {
                DT = ObjUserBL.GetPageRightsDetailsPIDNew(Session["UserId"].ToString(), PID, cmbBranches.SelectedItem.Value);
                if (DT.Rows.Count > 0)
                {
                    Page_Acess = DT.Rows[0]["Acess_status"].ToString();
                    Read_Acess = DT.Rows[0]["Read_Access"].ToString();
                    Write_Acess = DT.Rows[0]["Write_Access"].ToString();
                    Isdowld_Acess = DT.Rows[0]["IDwld_Access"].ToString();
                    Session["Page_Acess"] = Page_Acess;
                    Session["Read_Acess"] = Read_Acess;
                    Session["Write_Acess"] = Write_Acess;
                    Session["Isdowld_Acess"] = Isdowld_Acess;
                }
                if (cmbBranches.SelectedIndex == 1)
                {

                    BranchFullName = cmbBranches.SelectedItem.Text;    // This will Return Branch FullName 
                    Cmb_BranchName = ObjSel.GetBranchDBNamebyFullName(Session["Branch_HQ"].ToString(), BranchFullName);
                    UPDATEBRANCH(Cmb_BranchName);

                }
                else
                {
                    BranchFullName = cmbBranches.SelectedItem.Text;    // This will Return Branch FullName 
                    Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                    Rcd_id = ObjMain.ExecuteScalar("select cd_id from portaldb.companydetails where cd_branchid=" + cmbBranches.SelectedItem.Value + " and cd_branchalias='" + Cmb_BranchName + "';");
                    RPortalid = ObjMain.ExecuteScalar("select portaltypeid  from portaldb.invoicedetails where cd_id=" + Rcd_id + ";");
                    DtLicense = ObjUserBL.GetLicenseDetails(RPortalid);

                }
                if (DtLicense.Rows.Count > 0)
                {
                    L_MaxUserCnt = DtLicense.Rows[0]["UserCnt"].ToString();
                    L_Stockmovement = DtLicense.Rows[0]["STOCKMOVEMENT"].ToString();

                    // Create Session for License Variables 
                    Session["L_MaxUserCnt"] = L_MaxUserCnt;
                    Session["L_Stockmovement"] = L_Stockmovement;

                }

                if ((Session["L_Stockmovement"] == null) || Session["L_Stockmovement"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
                {
                    if (cmbBranches.SelectedItem.Text == "HQ")
                    {
                        cd_BranchAlias = Cmb_BranchName;

                    }
                    else
                    {
                        Response.Redirect("DeniedPage.aspx");
                    }



                    
                }
                else
                {
                    //btnExcel.Enabled = true;
                    cd_BranchAlias = Cmb_BranchName;
                    // Load Part: Function 

                }
            }
            UPDATEBRANCH(cd_BranchAlias);


        }


        public void UPDATEBRANCH(string ActiveCallvalue)
        {
            if (!string.IsNullOrEmpty(Session["UserId"] as string))
            {
                Useridvalue = Session["UserId"].ToString();
                QueryCurr_Select = "Update portaldb.usermaster set ActiveValue='" + ActiveCallvalue + "' where Userid=" + Useridvalue + "";
                S = ObjMain.ExecuteNonQuery(QueryCurr_Select);
            }
            else
            {
                Response.Redirect("Login.aspx");

            }

        }

        public void LoadBranchName()
        {
            DtNormalUser = ObjUserBL.GetBranchOnLoginValues8(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Session["CM_ID"].ToString());
            if (DtNormalUser.Rows.Count > 0)
            {
                cmbBranches.DataSource = DtNormalUser;
                cmbBranches.DataTextField = "branchfullname";
                cmbBranches.DataValueField = "Branchid";
                cmbBranches.DataBind();
                cmbBranches.Items.Insert(0, "Select Branch");
            }
        }
        public void LoadBranchName4SuperAdmin()
        {
            DtSuperAdmin = ObjUserBL.GetBranchOnSuperAdmin8(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Session["CM_ID"].ToString());
            if (DtSuperAdmin.Rows.Count > 0)
            {
                cmbBranches.DataSource = DtSuperAdmin;
                cmbBranches.DataTextField = "branchfullname";
                cmbBranches.DataValueField = "Branchid";
                cmbBranches.DataBind();
                cmbBranches.Items.Insert(0, "Select Branch");
            }
        }
        public void LoadBranchNamewithPortalTypeid()
        {
            DtMultiBranch = ObjUserBL.GetBranchOnLoginPortalid(Session["Branch_HQ"].ToString());
            if (DtMultiBranch.Rows.Count > 0)
            {
                cmbBranches.DataSource = DtMultiBranch;
                cmbBranches.DataTextField = "BranchName";
                cmbBranches.DataValueField = "Branchid";
                cmbBranches.DataBind();
                cmbBranches.Items.Insert(0, "Select Branch");
            }
        }



        public void ActiveCall()
        {
            string QueryCal = "select ActiveValue from portaldb.usermaster  where Userid=" + Session["UserId"].ToString() + " ";
            ActiveCallvalue = ObjMain.ExecuteScalar(QueryCal);
            if (ActiveCallvalue == "")
            {
                ActiveCallvalue = ObjSel.GetFirstBranch(Session["Branch_HQ"].ToString());
            }

            #region  HQ Validation in ActiveCall Function : Starts 
            cd_BranchAlias = ActiveCallvalue;
            // HQ Validation For Loading Branch Name : Dropdown  Added Karthik  On 02-12-21 

            ReadHQ = ObjUserBL.GetBranchFullNameByBranchName(Session["Branch_HQ"].ToString(), cd_BranchAlias);

            if (ReadHQ == "HQ")
            {
                cmbBranches.Items.FindByText(ReadHQ).Selected = true;

            }
            else
            {
                Curr_BranchFullName = ObjUserBL.GetBranchCurr_BranchVal(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), cd_BranchAlias);
                Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), Curr_BranchFullName);  // This will Return BranchName( Actual Database Name )
                cmbBranches.Items.FindByText(Curr_BranchFullName).Selected = true;
                Branchidval = ObjUserBL.GetBranchIDbyName(ActiveCallvalue, ActiveCallvalue);
                BranchAlias = Cmb_BranchName;
                lblcurrentdb.Text = Cmb_BranchName.ToString().Trim(); //john added on 14-Jan-2025
            }

            #endregion

            DT = ObjUserBL.GetPageRightsDetailsPIDNew(Session["UserId"].ToString(), PID, Branchidval);
            if (DT.Rows.Count > 0)
            {
                Page_Acess = DT.Rows[0]["Acess_status"].ToString();
                Read_Acess = DT.Rows[0]["Read_Access"].ToString();
                Write_Acess = DT.Rows[0]["Write_Access"].ToString();
                Isdowld_Acess = DT.Rows[0]["IDwld_Access"].ToString();

                Session["Page_Acess"] = Page_Acess;
                Session["Read_Acess"] = Read_Acess;
                Session["Write_Acess"] = Write_Acess;
                Session["Isdowld_Acess"] = Isdowld_Acess;
            }

            Rcd_id = ObjMain.ExecuteScalar("select cd_id from portaldb.companydetails where cd_branchid=" + cmbBranches.SelectedItem.Value + " and cd_branchalias='" + ActiveCallvalue + "';");
            RPortalid = ObjMain.ExecuteScalar("select portaltypeid  from portaldb.invoicedetails where cd_id=" + Rcd_id + ";");
            DtLicense = ObjUserBL.GetLicenseDetails(RPortalid);
            if (DtLicense.Rows.Count > 0)
            {
                L_MaxUserCnt = DtLicense.Rows[0]["UserCnt"].ToString();
                L_Stockmovement = DtLicense.Rows[0]["STOCKMOVEMENT"].ToString();

                // Create Session for License Variables 
                Session["L_MaxUserCnt"] = L_MaxUserCnt;
                Session["L_Stockmovement"] = L_Stockmovement;

            }
            
               if ((Session["L_Stockmovement"] == null) || Session["L_Stockmovement"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
                {

                if (cmbBranches.SelectedItem.Text == "HQ")
                {
                    BranchAlias = Session["Branch_HQ"].ToString();
                    LoadStockMovement();
                }
                else
                {
                    Response.Redirect("DeniedPage.aspx");
                }

                    
            }
            else
            {

                cd_BranchAlias = cmbBranches.SelectedItem.Text;
                // Load Part : Function

                LoadStockMovement();

            }

        }

        protected void cmbBasicSelection_SelectedIndexChanged(object sender, EventArgs e)
        {

            #region  Validation Between HQ Branch and Normal Branch 
            BranchFullName = cmbBranches.SelectedItem.Text;      // This will Return Branch FullName 
            if (BranchFullName == "HQ")
            {
                BranchAlias = Session["Branch_HQ"].ToString();
            }
            else
            {
                BranchFullName = cmbBranches.SelectedItem.Text;     // This will Return Branch FullName 
                Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                BranchAlias = Cmb_BranchName;

            }
            #endregion

            // Make Dropdown Null 
            cmbSubSelection.DataSource = "";
            cmbSubSelection.DataBind();

            if (cmbBasicSelection.SelectedItem.Text == "All Inventory")
            {

                cmbSubSelection.DataSource = null;
                cmbSubSelection.DataBind();


            }
            else if (cmbBasicSelection.SelectedItem.Text == "Category")
            {
                #region  Validation Between HQ Branch and Normal Branch 
                BranchFullName = cmbBranches.SelectedItem.Text;      // This will Return Branch FullName 
                if (BranchFullName == "HQ")
                {
                    BranchAlias = Session["Branch_HQ"].ToString();
                }
                else
                {
                    BranchFullName = cmbBranches.SelectedItem.Text;     // This will Return Branch FullName 
                    Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                    BranchAlias = Cmb_BranchName;

                }
                #endregion
                DT = ObjSel.Inv_CategoryList(BranchAlias);
                if (DT.Rows.Count > 0)
                {
                    cmbSubSelection.DataSource = DT;
                    cmbSubSelection.DataTextField = "Description";
                    cmbSubSelection.DataValueField = "CategoryID";
                    cmbSubSelection.DataBind();
                    txtSingleInventory.Text = "";
                }

            }
            else if (cmbBasicSelection.SelectedItem.Text == "Group1")
            {
                #region  Validation Between HQ Branch and Normal Branch 
                BranchFullName = cmbBranches.SelectedItem.Text;      // This will Return Branch FullName 
                if (BranchFullName == "HQ")
                {
                    BranchAlias = Session["Branch_HQ"].ToString();
                }
                else
                {
                    BranchFullName = cmbBranches.SelectedItem.Text;     // This will Return Branch FullName 
                    Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                    BranchAlias = Cmb_BranchName;

                }
                #endregion
                txtSingleInventory.Text = "";
                DT = ObjSel.Inv_Grp1List(Cmb_BranchName);

                if (DT.Rows.Count > 0)
                {

                    cmbSubSelection.DataSource = null;
                    cmbSubSelection.DataSource = DT;
                    cmbSubSelection.DataTextField = "GroupName1";
                    cmbSubSelection.DataValueField = "Groupid";
                    cmbSubSelection.DataBind();
                    txtSingleInventory.Text = "";
                }
                else
                {
                    cmbSubSelection.DataSource = null;
                    cmbSubSelection.DataBind();
                    txtSingleInventory.Text = "";
                }

            }
            else if (cmbBasicSelection.SelectedItem.Text == "Group2")
            {
                #region  Validation Between HQ Branch and Normal Branch 
                BranchFullName = cmbBranches.SelectedItem.Text;      // This will Return Branch FullName 
                if (BranchFullName == "HQ")
                {
                    BranchAlias = Session["Branch_HQ"].ToString();
                }
                else
                {
                    BranchFullName = cmbBranches.SelectedItem.Text;     // This will Return Branch FullName 
                    Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                    BranchAlias = Cmb_BranchName;

                }
                #endregion

                txtSingleInventory.Text = "";
                DT = ObjSel.Inv_Grp2List(Cmb_BranchName);
                if (DT.Rows.Count > 0)
                {
                    cmbSubSelection.DataSource = null;
                    cmbSubSelection.DataSource = DT;
                    cmbSubSelection.DataTextField = "GroupName2";
                    cmbSubSelection.DataValueField = "Groupid";
                    cmbSubSelection.DataBind();
                }
                else
                {
                    cmbSubSelection.DataSource = null;
                    cmbSubSelection.DataBind();
                }

            }
            else if (cmbBasicSelection.SelectedItem.Text == "Group3")
            {
                txtSingleInventory.Text = "";
                DT = ObjSel.Inv_Grp3List(Cmb_BranchName);
                if (DT.Rows.Count > 0)
                {
                    cmbSubSelection.DataSource = DT;
                    cmbSubSelection.DataTextField = "GroupName3";
                    cmbSubSelection.DataValueField = "Groupid";
                    cmbSubSelection.DataBind();
                }
                else
                {
                    cmbSubSelection.DataSource = null;
                    cmbSubSelection.DataBind();
                }

            }
            else if (cmbBasicSelection.SelectedItem.Text == "Group4")
            {
                txtSingleInventory.Text = "";
                DT = ObjSel.Inv_Grp4List(Cmb_BranchName);
                if (DT.Rows.Count > 0)
                {
                    cmbSubSelection.DataSource = DT;
                    cmbSubSelection.DataTextField = "GroupName4";
                    cmbSubSelection.DataValueField = "Groupid";
                    cmbSubSelection.DataBind();
                }
                else
                {
                    cmbSubSelection.DataSource = null;
                    cmbSubSelection.DataBind();
                }

            }
            else if (cmbBasicSelection.SelectedItem.Text == "Group5")
            {
                txtSingleInventory.Text = "";
                DT = ObjSel.Inv_Grp5List(Cmb_BranchName);
                if (DT.Rows.Count > 0)
                {
                    cmbSubSelection.DataSource = null;
                    cmbSubSelection.DataSource = DT;
                    cmbSubSelection.DataTextField = "GroupName5";
                    cmbSubSelection.DataValueField = "Groupid";
                    cmbSubSelection.DataBind();
                }
                else
                {
                    DT = null;
                    cmbSubSelection.DataSource = DT;
                    cmbSubSelection.DataTextField = "GroupName5";
                    cmbSubSelection.DataValueField = "Groupid";
                    cmbSubSelection.DataBind();
                }


            }


        }

        protected void txtSingleInventory_TextChanged(object sender, EventArgs e)
        {
            #region  Validation Between HQ Branch and Normal Branch 
            BranchFullName = cmbBranches.SelectedItem.Text;      // This will Return Branch FullName 
            if (BranchFullName == "HQ")
            {
                BranchAlias = Session["Branch_HQ"].ToString();
            }
            else
            {
                BranchFullName = cmbBranches.SelectedItem.Text;     // This will Return Branch FullName 
                Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                BranchAlias = Cmb_BranchName;

            }
            #endregion


            if (cmbBasicSelection.SelectedItem.Text == "Single Inventory")
            {
                RInventoryId = ObjSel.GetInvenID(BranchAlias, Microsoft.Security.Application.Encoder.HtmlEncode(txtSingleInventory.Text.Trim()));
                hidInven.Value = RInventoryId;
            }

        }


        public void GetConsilidatedRPT()
        {
            // XSS Attack Prevention 
            string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtStartDt.Text.Trim());
            string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtEndDt.Text.Trim());


            #region  Validation Between HQ Branch and Normal Branch 
            BranchFullName = cmbBranches.SelectedItem.Text;      // This will Return Branch FullName 
            if (BranchFullName == "HQ")
            {
                BranchAlias = Session["Branch_HQ"].ToString();
            }
            else
            {
                BranchFullName = cmbBranches.SelectedItem.Text;     // This will Return Branch FullName 
                Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                BranchAlias = Cmb_BranchName;

            }
            #endregion

            try
            {
                // Prevents SQL Injection 
                // Parameterized Approach 

                string drpqry = "DROP TABLE IF EXISTS {0}.T1InvMove ; ";
                string command = string.Format(drpqry, BranchAlias);
                int ChkDb = ObjMain.ExecuteNonQuery(command);

                Ins_TempQry = "CREATE TABLE  {0}.`t1invmove` "
                    + "("
                       + " `InventoryID` double default NULL,`CategoryID` double default NULL,`Barcode` varchar(128) default NULL,"
                       + " `Description` varchar(60) default NULL,`RefDate` datetime default NULL,`RefType` varchar(30) default NULL,"
                       + " `RefNo` varchar(250) default NULL,`StockInQty` double default NULL,`StockOutQty` double(19,4) default NULL,"
                       + " `StockCheck` double default NULL,KEY `T1IND1` (`InventoryID`)"
                       + " ) ENGINE=InnoDB ;";

                string command1 = string.Format(Ins_TempQry, BranchAlias);
                QRTemp = ObjMain.ExecuteNonQuery(command1);

                string Vw_Chk_Qry = "SHOW FULL TABLES IN {0} WHERE TABLE_TYPE LIKE 'VIEW' and Tables_in_{0}='common_stkmovement';";
                string command2 = string.Format(Vw_Chk_Qry, BranchAlias);
                Vw_Chk = ObjMain.ExecuteScalar(command2);

                if (Vw_Chk == "")
                {
                    string Ins_Vw = "Create view {0}.Common_StkMovement as SELECT t.InventoryID,INV.InventoryName, t.CategoryID,I.CategoryName,t.Barcode,t.Description,t.RefDate,t.RefType,t.RefNo,t.StockInQty,"
                                             + " t.StockOutQty,t.StockCheck FROM {0}.t1invmove t inner join {0}.inventorycategories AS I ON t.CategoryID=I.CategoryID"
                                             + " inner join {0}.Inventory as INV on  t.InventoryId = INV.Inventoryid;";


                    string command3 = string.Format(Ins_Vw, BranchAlias);

                    L = ObjMain.ExecuteNonQuery(command3);


                }
                // Create Index on T1
                string QR1 = "Drop INDEX T1IND1 ON  {0}.T1InvMove";
                DrpIndex_Qry = string.Format(QR1, BranchAlias);
                QR_DrpIndex = ObjMain.ExecuteNonQuery(DrpIndex_Qry);
                string QR2 = "CREATE INDEX T1IND1 ON  {0}.T1InvMove(InventoryID)";
                Index_Qry = string.Format(QR2, BranchAlias);
                QR_Index = ObjMain.ExecuteNonQuery(Index_Qry);
                // Common Query for Insert to  T1InvMove
                CQuery = "INSERT INTO  {0}.T1InvMove(InventoryID,CategoryID,Barcode,Description,RefDate,RefType,RefNo,StockInQty, StockOutQty,StockCheck)";
                // Date Selection : Between two Dates


                // Get Class Value & Active nd InActive 


                int SelIndex = 0;
                if (cmbInventoryClass.SelectedIndex == 0)
                {
                    CDate = " between  @StartDT and  @EndDt  ";
                }
                else
                {
                    CDate = " between  @StartDT and  @EndDt  ";
                    SelIndex = cmbInventoryClass.SelectedIndex - 1;
                    ClassValue = "AND  IV.Class =" + SelIndex + " ";
                    CDate += ClassValue;

                }

                int SetIndex1 = 0;
                int SetIndex2 = 0;

                if (cmbActiveInactive.SelectedIndex == 0)
                {

                }
                else if (cmbActiveInactive.SelectedIndex == 1)
                {
                    SetIndex1 = cmbActiveInactive.SelectedIndex;
                    ActiveInAct = "AND  IV.Active = " + SetIndex1 + "";
                    CDate += ActiveInAct;
                }
                else if (cmbActiveInactive.SelectedIndex == 2)
                {
                    SetIndex1 = cmbActiveInactive.SelectedIndex - 3;
                    SetIndex2 = cmbActiveInactive.SelectedIndex - 2;

                    ActiveInAct = "AND  IV.Active IN  (" + SetIndex1 + " ," + SetIndex2 + ") ";
                    CDate += ActiveInAct;
                }

                // Category & Group Condition 

                if (cmbBasicSelection.SelectedIndex == 1)
                {
                    SubQryValue = " AND IV.CategoryID='" + cmbSubSelection.SelectedItem.Value + "' ";
                }
                else if (cmbBasicSelection.SelectedIndex == 2)
                {
                    SubQryValue = " AND IV.Group1='" + cmbSubSelection.SelectedItem.Text + "' ";
                }

                else if (cmbBasicSelection.SelectedIndex == 3)
                {
                    SubQryValue = " AND IV.Group2='" + cmbSubSelection.SelectedItem.Text + "' ";
                }
                else if (cmbBasicSelection.SelectedIndex == 4)
                {
                    SubQryValue = " AND IV.Group3='" + cmbSubSelection.SelectedItem.Text + "' ";
                }
                else if (cmbBasicSelection.SelectedIndex == 5)
                {
                    SubQryValue = " AND IV.Group4='" + cmbSubSelection.SelectedItem.Text + "' ";
                }
                else if (cmbBasicSelection.SelectedIndex == 6)
                {
                    SubQryValue = " AND IV.Group5='" + cmbSubSelection.SelectedItem.Text + "' ";
                }
                else if (cmbBasicSelection.SelectedIndex == 7)
                {
                    SubQryValue = " AND IV.InventoryID = " + hidInven.Value + " ";
                }
                if (cmbBasicSelection.SelectedIndex == 0)
                {
                    if (chkPurchaseInvoice.Checked == true)
                    {

                        // Stock In Queries 
                        // Q1- Purchase Invoice
                        Qrry = "SELECT B.InventoryID,IV.CategoryID,Barcode,IV.Description,A.PInvoiceDate As RefDate,'Purchase Invoice' AS RefType,A.PInvoiceNumber AS RefNo,"
                      + "(PackCapacity*PackQuantity)+LO+FOC As StockInQty, 0.00 AS StockOutQty, 0 As StockCheck "
                      + "FROM (( {0}.PurchaseInvoice A INNER JOIN   {0}.PurchaseInvoiceDetail B "
                      + "ON A.InvoiceID = B.InvoiceID) Inner Join  {0}.Inventory IV "
                      + "ON B.InventoryID = IV.InventoryID) "
                      + "WHERE ( A.PInvoiceDate  " + CDate + ")  "
                      + "Order By B.InventoryID   ";
                        Qrry = CQuery + Qrry;

                        // Append the DatabaseName's Variable with the Query 
                        string command4 = string.Format(Qrry, BranchAlias);
                        MySqlParameter[] parameters =
                         {
                         new MySqlParameter("@StartDT",StartDT),
                         new MySqlParameter("@EndDt",EndDt),
                         };

                        K = ObjMain.ExecuteQuery(command4, parameters);


                    }

                }
                else
                {

                    // Stock In Queries 
                    // Q1- Purchase Invoice



                    if (chkPurchaseInvoice.Checked == true)
                    {
                        Qrry = "SELECT B.InventoryID,IV.CategoryID,Barcode,IV.Description,A.PInvoiceDate As RefDate,'Purchase Invoice' AS RefType,A.PInvoiceNumber AS RefNo,"
                      + "(PackCapacity*PackQuantity)+LO+FOC As StockInQty, 0.00 AS StockOutQty, 0 As StockCheck "
                      + "FROM (( {0}.PurchaseInvoice A INNER JOIN   {0}.PurchaseInvoiceDetail B "
                      + "ON A.InvoiceID = B.InvoiceID) Inner Join  {0}.Inventory IV "
                      + "ON B.InventoryID = IV.InventoryID) "
                      + "WHERE ( A.PInvoiceDate  " + CDate + ") " + SubQryValue + "  "
                      + "Order By B.InventoryID   ";
                        Qrry = CQuery + Qrry;


                        // Append the DatabaseName's Variable with the Query 
                        string command5 = string.Format(Qrry, Cmb_BranchName);
                        MySqlParameter[] parameters1 =
                         {
                         new MySqlParameter("@StartDT",StartDT),
                         new MySqlParameter("@EndDt",EndDt),
                         };

                        K = ObjMain.ExecuteQuery(command5, parameters1);

                    }

                }

                if (cmbBasicSelection.SelectedIndex == 0)
                {

                    if (chkInventoryAdjust.Checked == true)
                    {

                        // Q2 -  Inventory Adjustment (In)

                        Q2 = "SELECT A.InventoryID,IV.CategoryID,Barcode,IV.Description,DateAdjusted AS RefDate,'Inventory Adjustment' AS RefType,CONCAT('IN ',trim(A.Notes)) AS RefNo, "
                                  + "AdjustQuantity As StockInQty, 0.00 AS StockOutQty, 0 As StockCheck  "
                                  + "FROM  {0}.InventoryAdjust A Inner Join  {0}.Inventory IV On A.InventoryID=IV.InventoryID  "
                                  + "WHERE AdjustQuantity>0 and   "
                                  + "(date(DateAdjusted) " + CDate + ")  "
                                  + " Order By A.InventoryID ";
                        Q2 = CQuery + Q2;

                        // Append the DatabaseName's Variable with the Query 
                        string command6 = string.Format(Q2, BranchAlias);
                        MySqlParameter[] parameters2 =
                         {
                         new MySqlParameter("@StartDT",StartDT),
                         new MySqlParameter("@EndDt",EndDt),
                         };
                        K2 = ObjMain.ExecuteQuery(command6, parameters2);

                    }
                }
                else
                {

                    if (chkInventoryAdjust.Checked == true)
                    {
                        // Q2 -  Inventory Adjustment (In)

                        Q2 = "SELECT A.InventoryID,IV.CategoryID,Barcode,IV.Description,DateAdjusted AS RefDate,'Inventory Adjustment' AS RefType,CONCAT('IN ',trim(A.Notes)) AS RefNo, "
                                   + "AdjustQuantity As StockInQty, 0.00 AS StockOutQty, 0 As StockCheck  "
                                   + "FROM  {0}.InventoryAdjust A Inner Join  {0}.Inventory IV On A.InventoryID=IV.InventoryID  "
                                   + "WHERE AdjustQuantity>0  And  "
                                   + "(date(DateAdjusted) " + CDate + ")  " + SubQryValue + "  "
                                   + " Order By A.InventoryID ";
                        Q2 = CQuery + Q2;


                        // Append the DatabaseName's Variable with the Query 
                        string command7 = string.Format(Q2, BranchAlias);
                        MySqlParameter[] parameters3 =
                         {
                         new MySqlParameter("@StartDT",StartDT),
                         new MySqlParameter("@EndDt",EndDt),
                         };
                        K2 = ObjMain.ExecuteQuery(command7, parameters3);

                    }

                }


                if (cmbBasicSelection.SelectedIndex == 0)
                {


                    if (chkReceiveStock.Checked == true)
                    {
                        // Q3 -  Receive Stock

                        Q3 = "SELECT IV.InventoryID,IV.CategoryID,IV.BarCode,IV.Description,date(ConfirmDateTime) As RefDate, "
                            + "'Receive Stock' As RefType,ConfirmNo As RefNo, B.DeliveryQuantity As StockInQty, 0.00 AS StockOutQty, 0 As StockCheck  "
                            + "FROM ({0}.ReceiveStock A INNER JOIN {0}.ReceiveStockDetail B ON  "
                            + "A.ReceiveID = B.ReceiveID ) INNER JOIN  {0}.Inventory IV ON  "
                            + "IV.BarCode = B.BarCode  "
                            + " WHERE A.ConfirmFlag And  "
                            + "(A.TransferDate " + CDate + ")  "
                            + "Order By IV.InventoryID ";
                        Q3 = CQuery + Q3;


                        // Append the DatabaseName's Variable with the Query 
                        string command8 = string.Format(Q3, BranchAlias);
                        MySqlParameter[] parameters4 =
                         {
                         new MySqlParameter("@StartDT",StartDT),
                         new MySqlParameter("@EndDt",EndDt),
                         };
                        K3 = ObjMain.ExecuteQuery(command8, parameters4);

                    }

                }
                else
                {
                    if (chkReceiveStock.Checked == true)
                    {
                        // Q3 -  Receive Stock

                        Q3 = "SELECT IV.InventoryID,IV.CategoryID,IV.BarCode,IV.Description,ConfirmDateTime As RefDate, "
                            + "'Receive Stock' As RefType,ConfirmNo As RefNo, B.DeliveryQuantity As StockInQty, 0.00 AS StockOutQty, 0 As StockCheck  "
                            + "FROM ({0}.ReceiveStock A INNER JOIN {0}.ReceiveStockDetail B ON  "
                            + "A.ReceiveID = B.ReceiveID ) INNER JOIN  {0}.Inventory IV ON  "
                            + "IV.BarCode = B.BarCode  "
                            + " WHERE A.ConfirmFlag And  "
                            + "(A.TransferDate " + CDate + ") " + SubQryValue + " "
                            + "Order By IV.InventoryID ";
                        Q3 = CQuery + Q3;

                        // Append the DatabaseName's Variable with the Query 
                        string command9 = string.Format(Q3, BranchAlias);
                        MySqlParameter[] parameters5 =
                         {
                         new MySqlParameter("@StartDT",StartDT),
                         new MySqlParameter("@EndDt",EndDt),
                         };
                        K3 = ObjMain.ExecuteQuery(command9, parameters5);


                    }

                }


                if (cmbBasicSelection.SelectedIndex == 0)
                {


                    if (chkSalesInvoiceReturn.Checked == true)
                    {
                        // Q4-  Sales Return

                        Q4 = "SELECT B.InventoryID,IV.CategoryID,Barcode,IV.Description,A.SReturnDate As RefDate,'Sales Return' AS RefType,A.SReturnNumber AS RefNo,  "
                       + "(PackCapacity*PackQuantity)+LO+FOC As StockInQty, 0.00 AS StockOutQty, 0 As StockCheck  "
                       + "FROM (({0}.SalesReturn A INNER JOIN {0}.SalesReturnDetail B  "
                       + "ON A.ReturnID = B.ReturnID) Inner Join {0}.Inventory IV  "
                       + "ON B.InventoryID = IV.InventoryID)  "
                       + "WHERE ( A.SReturnDate " + CDate + ")  "
                      + " Order By B.InventoryID ";

                        Q4 = CQuery + Q4;

                        // Append the DatabaseName's Variable with the Query 
                        string command10 = string.Format(Q4, BranchAlias);
                        MySqlParameter[] parameters6 =
                         {
                         new MySqlParameter("@StartDT",StartDT),
                         new MySqlParameter("@EndDt",EndDt),
                         };
                        K4 = ObjMain.ExecuteQuery(command10, parameters6);

                    }


                }
                else
                {

                    if (chkSalesInvoiceReturn.Checked == true)
                    {
                        // Q4-  Sales Return

                        Q4 = "SELECT B.InventoryID,IV.CategoryID,Barcode,IV.Description,A.SReturnDate As RefDate,'Sales Return' AS RefType,A.SReturnNumber AS RefNo,  "
                       + "(PackCapacity*PackQuantity)+LO+FOC As StockInQty, 0.00 AS StockOutQty, 0 As StockCheck  "
                       + "FROM (({0}.SalesReturn A INNER JOIN {0}.SalesReturnDetail B  "
                       + "ON A.ReturnID = B.ReturnID) Inner Join {0}.Inventory IV  "
                       + "ON B.InventoryID = IV.InventoryID)  "
                       + "WHERE ( A.SReturnDate " + CDate + ") " + SubQryValue + "  "
                      + " Order By B.InventoryID ";

                        Q4 = CQuery + Q4;

                        // Append the DatabaseName's Variable with the Query 
                        string command11 = string.Format(Q4, BranchAlias);
                        MySqlParameter[] parameters7 =
                         {
                         new MySqlParameter("@StartDT",StartDT),
                         new MySqlParameter("@EndDt",EndDt),
                         };
                        K4 = ObjMain.ExecuteQuery(command11, parameters7);

                    }


                }

                if (cmbBasicSelection.SelectedIndex == 0)
                {

                    if (chkSalesInvoice.Checked == true)
                    {

                        // For Stock Out
                        // Q5 -  Sales Invoice

                        Q5 = "SELECT B.InventoryId,IV.CategoryID,Barcode,IV.Description,sInvDt AS RefDate,'Sales Invoice' AS RefType,SInvoiceNo AS REfNo, 0.00 AS StockInQty,  "
                          + "(PackCapacity * PackQuantity) + LooseQuantity+FreeOfCharge As StockOutQty, 0 As StockCheck  "
                          + " FROM (({0}.SalesInvoice A INNER JOIN {0}.SalesInvoiceDetail B ON  "
                          + " A.InvoiceId = B.InvoiceId) Inner Join {0}.Inventory IV  "
                          + "ON B.InventoryID = IV.InventoryID)  "
                          + "Where (A.sInvDt " + CDate + ")  "
                          + " Order By B.InventoryId  ";

                        Q5 = CQuery + Q5;



                        // Append the DatabaseName's Variable with the Query 
                        string command12 = string.Format(Q5, BranchAlias);
                        MySqlParameter[] parameters8 =
                         {
                         new MySqlParameter("@StartDT",StartDT),
                         new MySqlParameter("@EndDt",EndDt),
                         };
                        K5 = ObjMain.ExecuteQuery(command12, parameters8);

                    }
                }
                else
                {


                    if (chkSalesInvoice.Checked == true)
                    {

                        // For Stock Out
                        // Q5 -  Sales Invoice

                        Q5 = "SELECT B.InventoryId,IV.CategoryID,Barcode,IV.Description,sInvDt AS RefDate,'Sales Invoice' AS RefType,SInvoiceNo AS REfNo, 0.00 AS StockInQty,  "
                          + "(PackCapacity * PackQuantity) + LooseQuantity+FreeOfCharge As StockOutQty, 0 As StockCheck  "
                          + " FROM (({0}.SalesInvoice A INNER JOIN {0}.SalesInvoiceDetail B ON  "
                          + " A.InvoiceId = B.InvoiceId) Inner Join {0}.Inventory IV  "
                          + "ON B.InventoryID = IV.InventoryID)  "
                          + "Where (A.sInvDt " + CDate + ")  " + SubQryValue + " "
                          + " Order By B.InventoryId  ";

                        Q5 = CQuery + Q5;

                        // Append the DatabaseName's Variable with the Query 
                        string command13 = string.Format(Q5, BranchAlias);
                        MySqlParameter[] parameters9 =
                         {
                         new MySqlParameter("@StartDT",StartDT),
                         new MySqlParameter("@EndDt",EndDt),
                         };
                        K5 = ObjMain.ExecuteQuery(command13, parameters9);

                    }

                }



                if (cmbBasicSelection.SelectedIndex == 0)
                {



                    if (chkRetailSale.Checked == true)
                    {
                        // Q6 -Retail Sale 

                        Q6 = " SELECT IV.InventoryID,IV.CategoryID,B.BarCode,IV.Description,A.TransDate As RefDate, "
                        + " IF(B.Qty>0,'Retail Sales','Exchange/Return') As RefType, "
                        + " Concat(A.TransID,'(',Trim(A.TransNo),')') AS RefNo, 0.00 AS StockInQty, B.Qty As StockOutQty, 0 As StockCheck  "
                        + "From ({0}.Trans A Inner Join {0}.TransDetail B on A.TransID=B.TransID) "
                        + "INNER JOIN {0}.Inventory IV ON B.BarCode = IV.BarCode  "
                        + "WHERE A.TransStatus=2 And B.ItemStatus<>1 And "
                        + " (A.BusinessDate  " + CDate + " )  "
                        + "Union  All "
                        + "Select D.inventoryID,D.CategoryID,D.BarCode,D.Description,A.TransDate As RefDate, "
                         + "Concat(TRIM(B.BarCode),' X ',B.Qty) As RefType, "
                         + "Concat(A.TransID,'(',Trim(A.TransNo),')') AS RefNo, 0.00 AS StockInQty, B.Qty * C.Quantity  As StockOutQty, 0 As StockCheck "
                         + "From ({0}.Trans A Inner Join {0}.TransDetail B on A.TransID=B.TransID) "
                         + "INNER JOIN {0}.Inventory IV ON B.BarCode = IV.BarCode "
                         + "INNER JOIN {0}.inventoryassembly C ON C.InventoryID = IV.InventoryID "
                         + "INNER JOIN {0}.Inventory D on D.InventoryID=C.AssemblyInventoryID  "
                         + "WHERE A.TransStatus=2 And B.ItemStatus<>1 And B.Class=3 And  "
                         + "(A.BusinessDate " + CDate + " ) ";
                        Q6 = CQuery + Q6;


                        // Append the DatabaseName's Variable with the Query 
                        string command14 = string.Format(Q6, BranchAlias);
                        MySqlParameter[] parameters10 =
                         {
                         new MySqlParameter("@StartDT",StartDT),
                         new MySqlParameter("@EndDt",EndDt),
                         };
                        K6 = ObjMain.ExecuteQuery(command14, parameters10);


                    }
                }
                else
                {
                    if (chkRetailSale.Checked == true)
                    {
                        // Q6 -Retail Sale 

                        Q6 = " SELECT IV.InventoryID,IV.CategoryID,B.BarCode,IV.Description,A.TransDate As RefDate, "
                        + " IF(B.Qty>0,'Retail Sales','Exchange/Return') As RefType, "
                        + " Concat(A.TransID,'(',Trim(A.TransNo),')') AS RefNo, 0.00 AS StockInQty, B.Qty As StockOutQty, 0 As StockCheck  "
                        + "From ({0}.Trans A Inner Join {0}.TransDetail B on A.TransID=B.TransID) "
                        + "INNER JOIN {0}.Inventory IV ON B.BarCode = IV.BarCode  "
                        + "WHERE A.TransStatus=2 And B.ItemStatus<>1 And "
                        + " (A.BusinessDate  " + CDate + "  " + SubQryValue + " )  "
                        + "Union  All "
                        + "Select D.inventoryID,D.CategoryID,D.BarCode,D.Description,A.TransDate As RefDate, "
                         + "Concat(TRIM(B.BarCode),' X ',B.Qty) As RefType, "
                         + "Concat(A.TransID,'(',Trim(A.TransNo),')') AS RefNo, 0.00 AS StockInQty, B.Qty * C.Quantity  As StockOutQty, 0 As StockCheck "
                         + "From ({0}.Trans A Inner Join {0}.TransDetail B on A.TransID=B.TransID) "
                         + "INNER JOIN {0}.Inventory IV ON B.BarCode = IV.BarCode "
                         + "INNER JOIN {0}.inventoryassembly C ON C.InventoryID = IV.InventoryID "
                         + "INNER JOIN {0}.Inventory D on D.InventoryID=C.AssemblyInventoryID  "
                         + "WHERE A.TransStatus=2 And B.ItemStatus<>1 And B.Class=3 And  "
                         + "(A.BusinessDate " + CDate + " ) " + SubQryValue + " ";
                        Q6 = CQuery + Q6;



                        // Append the DatabaseName's Variable with the Query 
                        string command15 = string.Format(Q6, BranchAlias);
                        MySqlParameter[] parameters11 =
                         {
                         new MySqlParameter("@StartDT",StartDT),
                         new MySqlParameter("@EndDt",EndDt),
                         };
                        K6 = ObjMain.ExecuteQuery(command15, parameters11);

                    }


                }


                if (cmbBasicSelection.SelectedIndex == 0)
                {


                    if (chkInventoryAdjust.Checked == true)
                    {
                        // Q7  -  Inventory Adjustment (Out)


                        Q7 = "SELECT A.InventoryID,IV.CategoryID,Barcode,IV.Description,DateAdjusted AS RefDate,"
                       + "'Inventory Adjustment' AS RefType,CONCAT('OUT ',trim(A.Notes)) AS RefNo, 0.00 AS StockInQty, Abs(AdjustQuantity) As StockOutQty, 0 As StockCheck  "
                       + "FROM {0}.InventoryAdjust A Inner Join {0}.Inventory IV On A.InventoryID=IV.InventoryID  "
                       + "WHERE AdjustQuantity<0 And A.Notes<>'*STOCKCHECK*' And  "
                       + "(date(DateAdjusted) " + CDate + ")  "
                       + " Order By A.InventoryID ";

                        Q7 = CQuery + Q7;
                        // Append the DatabaseName's Variable with the Query 
                        string command16 = string.Format(Q7, BranchAlias);
                        MySqlParameter[] parameters12 =
                         {
                         new MySqlParameter("@StartDT",StartDT),
                         new MySqlParameter("@EndDt",EndDt),
                         };
                        K7 = ObjMain.ExecuteQuery(command16, parameters12);

                    }

                }

                else
                {
                    if (chkInventoryAdjust.Checked == true)
                    {
                        // Q7  -  Inventory Adjustment (Out)


                        Q7 = "SELECT A.InventoryID,IV.CategoryID,Barcode,IV.Description,DateAdjusted AS RefDate,"
                       + "'Inventory Adjustment' AS RefType,CONCAT('OUT ',trim(A.Notes)) AS RefNo, 0.00 AS StockInQty, Abs(AdjustQuantity) As StockOutQty, 0 As StockCheck  "
                       + "FROM {0}.InventoryAdjust A Inner Join {0}.Inventory IV On A.InventoryID=IV.InventoryID  "
                       + "WHERE AdjustQuantity<0 And A.Notes<>'*STOCKCHECK*' And  "
                       + "(date(DateAdjusted) " + CDate + ")  " + SubQryValue + " "
                       + " Order By A.InventoryID ";

                        Q7 = CQuery + Q7;

                        // Append the DatabaseName's Variable with the Query 
                        string command17 = string.Format(Q7, BranchAlias);
                        MySqlParameter[] parameters13 =
                         {
                         new MySqlParameter("@StartDT",StartDT),
                         new MySqlParameter("@EndDt",EndDt),
                         };
                        K7 = ObjMain.ExecuteQuery(command17, parameters13);


                    }

                }


                if (cmbBasicSelection.SelectedIndex == 0)
                {



                    if (chkTransferStock.Checked == true)
                    {

                        // Q8 -  Transfer Stock 

                        Q8 = "SELECT IV.InventoryID,IV.CategoryID,B.Barcode,IV.Description,date(SendDateTime) As RefDate,'Transfer Stock' As RefType,TransferNo As RefNo, 0.00 AS StockInQty,  "
                         + "((PackCapacity * PackQuantity)+ LooseQuantity+FreeOfCharge-RejectQuantity) As StockOutQty, 0 As StockCheck  "
                         + "FROM (({0}.TransferStock A INNER JOIN {0}.TransferStockDetail B ON A.TransferID = B.TransferID) Inner  Join {0}. Inventory IV  "
                         + "On B.Barcode=IV.Barcode)  "
                         + "Where A.SendFlag = 1 And  "
                         + "(A.TransferDate " + CDate + ")  "
                         + " Order By B.InventoryID ";

                        Q8 = CQuery + Q8;

                        // Append the DatabaseName's Variable with the Query 
                        string command17 = string.Format(Q8, BranchAlias);
                        MySqlParameter[] parameters15 =
                         {
                         new MySqlParameter("@StartDT",StartDT),
                         new MySqlParameter("@EndDt",EndDt),
                         };
                        K8 = ObjMain.ExecuteQuery(command17, parameters15);


                    }


                }

                else
                {
                    if (chkTransferStock.Checked == true)
                    {

                        // Q8 -  Transfer Stock 

                        Q8 = "SELECT IV.InventoryID,IV.CategoryID,B.Barcode,IV.Description,SendDateTime As RefDate,'Transfer Stock' As RefType,TransferNo As RefNo, 0.00 AS StockInQty,  "
                         + "((PackCapacity * PackQuantity)+ LooseQuantity+FreeOfCharge-RejectQuantity) As StockOutQty, 0 As StockCheck  "
                         + "FROM (({0}.TransferStock A INNER JOIN {0}.TransferStockDetail B ON A.TransferID = B.TransferID) Inner  Join {0}. Inventory IV  "
                         + "On B.Barcode=IV.Barcode)  "
                         + "Where A.SendFlag = 1 And  "
                         + "(A.TransferDate " + CDate + ")  " + SubQryValue + " "
                         + " Order By B.InventoryID ";

                        Q8 = CQuery + Q8;

                        // Append the DatabaseName's Variable with the Query 
                        string command18 = string.Format(Q8, BranchAlias);
                        MySqlParameter[] parameters16 =
                         {
                         new MySqlParameter("@StartDT",StartDT),
                         new MySqlParameter("@EndDt",EndDt),
                         };
                        K8 = ObjMain.ExecuteQuery(command18, parameters16);


                    }


                }


                if (cmbBasicSelection.SelectedIndex == 0)
                {


                    if (chkPurchaseInvoiceReturn.Checked == true)
                    {
                        // Q9  -   Purchase Return

                        Q9 = "SELECT B.InventoryID,IV.CategoryID,Barcode,IV.Description,A.PReturnDate As RefDate,'Purchase Return' AS RefType,A.PReturnNumber AS RefNo,  "
                        + "0.00 As StockInQty, (PackCapacity*PackQuantity)+LO+FOC AS StockOutQty, 0 As StockCheck  "
                        + "FROM (({0}.PurchaseReturn A INNER JOIN {0}.PurchaseReturnDetail B  "
                        + "ON A.ReturnID = B.ReturnID) Inner Join  {0}.Inventory IV  "
                        + "ON B.InventoryID = IV.InventoryID)  "
                        + "WHERE ( A.PReturnDate " + CDate + ")   "
                        + " Order By B.InventoryID  ";

                        Q9 = CQuery + Q9;

                        // Append the DatabaseName's Variable with the Query 
                        string command19 = string.Format(Q9, BranchAlias);
                        MySqlParameter[] parameters17 =
                         {
                         new MySqlParameter("@StartDT",StartDT),
                         new MySqlParameter("@EndDt",EndDt),
                         };
                        K9 = ObjMain.ExecuteQuery(command19, parameters17);



                    }

                }
                else
                {
                    if (chkPurchaseInvoiceReturn.Checked == true)
                    {
                        // Q9  -   Purchase Return

                        Q9 = "SELECT B.InventoryID,IV.CategoryID,Barcode,IV.Description,A.PReturnDate As RefDate,'Purchase Return' AS RefType,A.PReturnNumber AS RefNo,  "
                        + "0.00 As StockInQty, (PackCapacity*PackQuantity)+LO+FOC AS StockOutQty, 0 As StockCheck  "
                        + "FROM (({0}.PurchaseReturn A INNER JOIN {0}.PurchaseReturnDetail B  "
                        + "ON A.ReturnID = B.ReturnID) Inner Join  {0}.Inventory IV  "
                        + "ON B.InventoryID = IV.InventoryID)  "
                        + "WHERE ( A.PReturnDate " + CDate + ") " + SubQryValue + " "
                        + " Order By B.InventoryID  ";

                        Q9 = CQuery + Q9;

                        // Append the DatabaseName's Variable with the Query 
                        string command20 = string.Format(Q9, BranchAlias);
                        MySqlParameter[] parameters18 =
                         {
                         new MySqlParameter("@StartDT",StartDT),
                         new MySqlParameter("@EndDt",EndDt),
                         };
                        K9 = ObjMain.ExecuteQuery(command20, parameters18);


                    }

                }

                if (cmbBasicSelection.SelectedIndex == 0)
                {


                    if (chkStockCheck.Checked == true)
                    {
                        // Q10 - Stock Check 
                        Q10 = " SELECT B.InventoryID,IV.CategoryID,Barcode,IV.Description,A.TransDate As RefDate,'Stock Check' AS RefType,A.StockCheckNo AS RefNo, "
                         + "0.00 As StockInQty, 0.00 AS StockOutQty,CheckedQty AS Stockcheck  "
                         + "FROM (({0}.StockCheckMaster A INNER JOIN {0}.StockCheckDetail B  "
                         + "ON A.StockCheckId = B.StockCheckId) Inner Join {0}.Inventory IV  "
                         + "ON B.InventoryID = IV.InventoryID)  "
                         + "WHERE ( Confirmed=True And A.TransDate " + CDate + ") "
                         + " Order By B.InventoryID ";
                        Q10 = CQuery + Q10;
                        // Append the DatabaseName's Variable with the Query 
                        string command21 = string.Format(Q10, BranchAlias);
                        MySqlParameter[] parameters19 =
                         {
                         new MySqlParameter("@StartDT",StartDT),
                         new MySqlParameter("@EndDt",EndDt),
                         };
                        K10 = ObjMain.ExecuteQuery(command21, parameters19);
                    }
                }
                else
                {
                    if (chkStockCheck.Checked == true)
                    {
                        // Q10 - Stock Check 
                        Q10 = " SELECT B.InventoryID,IV.CategoryID,Barcode,IV.Description,A.TransDate As RefDate,'Stock Check' AS RefType,A.StockCheckNo AS RefNo, "
                         + "0.00 As StockInQty, 0.00 AS StockOutQty,CheckedQty AS Stockcheck  "
                         + "FROM (({0}.StockCheckMaster A INNER JOIN {0}.StockCheckDetail B  "
                         + "ON A.StockCheckId = B.StockCheckId) Inner Join {0}.Inventory IV  "
                         + "ON B.InventoryID = IV.InventoryID)  "
                         + "WHERE ( Confirmed=True And A.TransDate " + CDate + ") " + SubQryValue + "  "
                         + " Order By B.InventoryID ";
                        Q10 = CQuery + Q10;

                        // Append the DatabaseName's Variable with the Query 
                        string command22 = string.Format(Q10, BranchAlias);
                        MySqlParameter[] parameters19 =
                         {
                         new MySqlParameter("@StartDT",StartDT),
                         new MySqlParameter("@EndDt",EndDt),
                         };
                        K10 = ObjMain.ExecuteQuery(command22, parameters19);
                        K10 = ObjMain.ExecuteNonQuery(Q10);

                    }

                }

                if (cmbBasicSelection.SelectedIndex == 0) 
                {
                    if(chkReturnStock.Checked == true)
                    {
                        //Q11
                        Q11 = "SELECT IV.InventoryID,IV.CategoryID,B.Barcode,IV.Description,date(ReturnDate) As RefDate,'Return Stock' As RefType, " +
                                " ReturnNo as RefNo, ReturnQuantity as StockInQty,0.00 as StockQutQty, 0 as StockCheck " +
                                " FROM(({0}.returnstock A " +
                                " INNER JOIN {0}.returnstockdetail B ON A.ReturnID = B.ReturnID) " +
                                " Inner Join {0}.Inventory IV  On B.Barcode = IV.Barcode)  " +
                                " where A.ReturnFlag = 1 " +
                                " and(date(A.ReturnDate)   " + CDate + ")Order By IV.InventoryID; ";
                        Q11 = CQuery + Q11;

                        string command23 = string.Format(Q11, BranchAlias);
                        MySqlParameter[] parameters23 =
                        {
                            new MySqlParameter("@StartDT",StartDT),
                            new MySqlParameter("@EndDt",EndDt),
                        };
                        K11 = ObjMain.ExecuteQuery(command23, parameters23);
                        //K11 = ObjMain.ExecuteNonQuery(Q11);
                    }
                }
                else
                {
                    if (chkReturnStock.Checked == true)
                    {
                        Q11 = "SELECT IV.InventoryID,IV.CategoryID,B.Barcode,IV.Description,ReturnDate As RefDate,'Return Stock' As RefType, " +
                              " ReturnNo as RefNo, ReturnQuantity as StockInQty,0.00 as StockQutQty, 0 as StockCheck " +
                              " FROM(({0}.returnstock A " +
                              " INNER JOIN {0}.returnstockdetail B ON A.ReturnID = B.ReturnID) " +
                              " Inner Join {0}.Inventory IV  On B.Barcode = IV.Barcode)  " +
                              " where A.ReturnFlag = 1 " +
                              " and(date(A.ReturnDate)   " + CDate + ") " + SubQryValue + "  " +
                              " Order By IV.InventoryID; ";
                        Q11 = CQuery + Q11;

                        string command23 = string.Format(Q11, BranchAlias);
                        MySqlParameter[] parameters23 =
                        {
                            new MySqlParameter("@StartDT",StartDT),
                            new MySqlParameter("@EndDt",EndDt),
                        };
                        K11 = ObjMain.ExecuteQuery(command23, parameters23);
                       // K11 = ObjMain.ExecuteNonQuery(Q11);
                    }
                }



            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }
        protected void btnSaveReport_Click(object sender, EventArgs e)
        {

            GetConsilidatedRPT();
            LoadStockMovement();

            ScriptManager.RegisterStartupScript(this, this.GetType(), "Script", " loadData('" + txtStartDt.Text + "','" + txtEndDt.Text + "','" + Session["tdc"].ToString().Trim() + "');", true); //john added on 16-Jan-2025 load data

            //loadcry();
            //(Currently Not in use)
        }

        public void LoadCheckBox()
        {
            chkPurchaseInvoice.Checked = true;
            chkSalesInvoice.Checked = true;
            chkInventoryAdjust.Checked = true;
            chkRetailSale.Checked = true;
            chkReceiveStock.Checked = true;
            chk2InventoryadjustOut.Checked = true;
            chkSalesInvoiceReturn.Checked = true;
            chkTransferStock.Checked = true;
            chkStockCheck.Checked = true;
            chkPurchaseInvoiceReturn.Checked = true;
            chkReturnStock.Checked = true;

        }

        public void ResetCheckBox()
        {
            chkPurchaseInvoice.Checked = false;
            chkSalesInvoice.Checked = false;
            chkInventoryAdjust.Checked = false;
            chkRetailSale.Checked = false;
            chkReceiveStock.Checked = false;
            chk2InventoryadjustOut.Checked = false;
            chkSalesInvoiceReturn.Checked = false;
            chkTransferStock.Checked = false;
            chkStockCheck.Checked = false;
            chkPurchaseInvoiceReturn.Checked = false;
            chkReturnStock.Checked = false;
        }



        protected void CheckBoxList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            ResetCheckBox();

            for (int i = 0; i < CheckBoxList1.Items.Count; i++)
            {
                if (CheckBoxList1.Items[i].Selected == true) // getting selected value from CheckBox List  
                {
                    STR += CheckBoxList1.Items[i].Text;
                }


            }

            if (STR == "All")
            {
                LoadCheckBox();
            }


        }

        public void LoadStockMovement()
        {
            // XSS Attack Prevention 
            string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtStartDt.Text.Trim());
            string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtEndDt.Text.Trim());




            if (cmbBranches.Visible == false)
            {
                BranchAlias = Session["Branch_HQ"].ToString();


            }
            else if (cmbBranches.Visible == true)
            {
                #region  Validation Between HQ Branch and Normal Branch 
                BranchFullName = cmbBranches.SelectedItem.Text;      // This will Return Branch FullName 
                if (BranchFullName == "HQ")
                {
                    BranchAlias = Session["Branch_HQ"].ToString();
                }
                else
                {
                    BranchFullName = cmbBranches.SelectedItem.Text;     // This will Return Branch FullName 
                    Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                    BranchAlias = Cmb_BranchName;

                }
                #endregion
            }

            //string QeryStkMoment = "SELECT * FROM  " + BranchAlias + ".common_stkmovement   order by categoryid,RefDate ;"; Existing 

            // Get the CategoryList 
            string QeryStkMoment = "SElect  C.CategoryName ,T.Barcode,  C.categoryid , sum(T.StockInQty) as StockInQty , Sum(T.StockOutQty) as StockOutQty,sum(T.StockCheck) as StockCheck "
            + "  from {0}.t1invmove as T   inner join {0}.inventory  as i  "
            + "  On T.inventoryid=i.inventoryid inner join {0}.inventorycategories as C on T.Categoryid=C.Categoryid   "
            + "  where date(refdate) Between @StartDT  and @EndDt     group by C.categoryid ;";
            string command23 = string.Format(QeryStkMoment, BranchAlias);

            MySqlParameter[] parameters =
                   {
                     new MySqlParameter("@StartDT",StartDT),
                     new MySqlParameter("@EndDt",EndDt),
                    };

            DTResult = ObjMain.NewGetData_DT(command23, parameters);
            grdStockMoment.DataSource = DTResult;
            grdStockMoment.DataBind();

        }

        /// <summary>
        ///  This Function  for Excel  
        /// </summary>
        public void LoadStockMovement4Excel(string BranchName)
        {

            // Prevents SQL Injections  
            // Parameterized Approach  

            string QeryStkMoment = "SELECT * FROM  {0}.common_stkmovement   order by categoryid,RefDate ;";
            string command23 = string.Format(QeryStkMoment, BranchName);
            DTResult = ObjMain.GetData_DT(command23);
            grdStockMoment.DataSource = DTResult;
            grdStockMoment.DataBind();

        }
        public override void VerifyRenderingInServerForm(Control control)
        {
            /* Confirms that an HtmlForm control is rendered for the specified ASP.NET
               server control at run time. */
        }


        protected void btnExcel_Click(object sender, EventArgs e)
        {


            if (Session["Isdowld_Acess"].ToString() == "N")
            {
                Page page = HttpContext.Current.Handler as Page;
                string message = "No Access To download";
                System.Web.UI.ScriptManager.RegisterStartupScript(page, page.GetType(), "err_msg", "alert('" + message + "');", true);

            }
            else if (cmbBranches.SelectedIndex != 0)
            {




                MySqlConnection Cnn = new MySqlConnection(ConStr_Rds);
                Cnn.Open();

                if (cmbBranches.Visible == true)
                {
                    /* BranchFullName = cmbBranches.SelectedItem.Text;    // This will Return Branch FullName 
                     Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                     Response.Clear();
                     Response.Buffer = true;
                     Response.AddHeader("content-disposition", "attachment;filename=StockMovementReport.xls");
                     Response.Charset = "";
                     Response.ContentType = "application/vnd.ms-excel";
                     using (StringWriter sw = new StringWriter())
                     {
                         HtmlTextWriter hw = new HtmlTextWriter(sw);

                         //To Export all pages
                         grdStockMoment.AllowPaging = false;
                         LoadStockMovement4Excel(Cmb_BranchName);

                         grdStockMoment.HeaderRow.BackColor = Color.White;
                         foreach (TableCell cell in grdStockMoment.HeaderRow.Cells)
                         {
                             cell.BackColor = grdStockMoment.HeaderStyle.BackColor;
                         }
                         foreach (GridViewRow row in grdStockMoment.Rows)
                         {
                             row.BackColor = Color.White;
                             foreach (TableCell cell in row.Cells)
                             {
                                 if (row.RowIndex % 2 == 0)
                                 {
                                     cell.BackColor = grdStockMoment.AlternatingRowStyle.BackColor;
                                 }
                                 else
                                 {
                                     cell.BackColor = grdStockMoment.RowStyle.BackColor;
                                 }
                                 cell.CssClass = "textmode";
                             }
                         }

                         grdStockMoment.RenderControl(hw);

                         //style to format numbers to string
                         string style = @"<style> .textmode { mso-number-format:\@; } </style>";
                         Response.Write(style);
                         Response.Output.Write(sw.ToString());
                         Response.Flush();
                         Response.End();
                     }

                     */


                    // New Change : 

                    #region  Validation Between HQ Branch and Normal Branch 
                    BranchFullName = cmbBranches.SelectedItem.Text;      // This will Return Branch FullName 
                    if (BranchFullName == "HQ")
                    {
                        BranchAlias = Session["Branch_HQ"].ToString();
                    }
                    else
                    {
                        BranchFullName = cmbBranches.SelectedItem.Text;     // This will Return Branch FullName 
                        Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                        BranchAlias = Cmb_BranchName;

                    }
                    #endregion

                    string record = ConfigurationManager.AppSettings["UserRecord"];
                    string RecordPageNumber = ConfigurationManager.AppSettings["PageNumber"];

                    int RowsPerPage = Convert.ToInt32(record);
                    int PageNumber = 0;
                    string filename = Server.MapPath("StockMovement");

                    DataTable dt = new DataTable();
                    StringWriter writer = new StringWriter();
                    HtmlTextWriter htmlWriter = new HtmlTextWriter(writer);
                    DataTable Dt2 = new DataTable();
                    string QeryStkMoment = "SELECT Barcode,InventoryName,Description,RefDate as TransactionDate,RefType as Type , RefNo as ReceiptNo , StockInQty, StockOutQty, StockCheck   FROM  {0}.common_stkmovement   order by categoryid,InventoryName,Refdate ;";
                    string command24 = string.Format(QeryStkMoment, Cmb_BranchName);
                    Dt2 = ObjMain.GetData_DT(command24);

                    decimal stkInQty = 0;
                    decimal stkOutQty = 0;
                    decimal stockcheck = 0;

                    for (int i = 0; i <= Dt2.Rows.Count - 1; i++)
                    {
                        if (i > 0)
                        {
                            if (Dt2.Rows[i]["InventoryName"].ToString().ToLower() != Dt2.Rows[i - 1]["InventoryName"].ToString().ToLower())
                            {
                                Dt2.Rows.InsertAt(Dt2.NewRow(), i);
                                Dt2.Rows[i]["StockInQty"] = stkInQty;
                                stkInQty = 0;

                                // StockOut

                                Dt2.Rows[i]["StockOutQty"] = stkOutQty;
                                stkOutQty = 0;

                                // StockCheck 
                                Dt2.Rows[i]["StockCheck"] = stockcheck;
                                stockcheck = 0;
                                i++;



                            }




                        }
                        stkInQty += Convert.ToDecimal(Dt2.Rows[i]["StockInQty"]);
                        stkOutQty += Convert.ToDecimal(Dt2.Rows[i]["StockOutQty"]);
                        stockcheck += Convert.ToDecimal(Dt2.Rows[i]["StockCheck"]);



                    }
                    Dt2.Rows.Add(Dt2.NewRow());
                    Dt2.Rows[Dt2.Rows.Count - 1]["StockInQty"] = stkInQty;
                    Dt2.Rows[Dt2.Rows.Count - 1]["StockOutQty"] = stkOutQty;
                    Dt2.Rows[Dt2.Rows.Count - 1]["StockCheck"] = stockcheck;






                    //Create a dummy GridView
                    GridView GridView1 = new GridView();
                    GridView1.ShowFooter = true;

                    GridView1.AllowPaging = false;
                    GridView1.DataSource = Dt2;
                    GridView1.DataBind();
                    Response.Clear();
                    Response.Buffer = true;
                    Response.AddHeader("content-disposition",
                     "attachment;filename=StockMovement.xls");
                    Response.Charset = "";
                    Response.ContentType = "application/vnd.ms-excel";
                    StringWriter sw = new StringWriter();
                    HtmlTextWriter hw = new HtmlTextWriter(sw);


                    for (int i = 0; i < GridView1.Rows.Count; i++)
                    {
                        //Apply text style to each Row
                        GridView1.Rows[i].Attributes.Add("class", "textmode");
                    }
                    GridView1.RenderControl(hw);
                    //style to format numbers to string
                    //string style = @"<style> textmode  { mso-number-format:\@; } </style>";

                    string style = @"<style> TD { mso-number-format:\@;  } </style>";
                    Response.Write(style);
                    Response.Output.Write(sw.ToString());
                    Response.Flush();
                    Response.End();






                }
                else
                {

                    Response.Clear();
                    Response.Buffer = true;
                    Response.AddHeader("content-disposition", "attachment;filename=StockMovementReport.xls");
                    Response.Charset = "";
                    Response.ContentType = "application/vnd.ms-excel";
                    using (StringWriter sw = new StringWriter())
                    {
                        HtmlTextWriter hw = new HtmlTextWriter(sw);

                        //To Export all pages
                        grdStockMoment.AllowPaging = false;
                        // BindGrid(Session["Branch_HQ"].ToString(), txtStartDt.Text, txtEndDt.Text);
                        LoadStockMovement4Excel(Session["Branch_HQ"].ToString());
                        grdStockMoment.HeaderRow.BackColor = Color.White;
                        foreach (TableCell cell in grdStockMoment.HeaderRow.Cells)
                        {
                            cell.BackColor = grdStockMoment.HeaderStyle.BackColor;
                        }
                        foreach (GridViewRow row in grdStockMoment.Rows)
                        {
                            row.BackColor = Color.White;
                            foreach (TableCell cell in row.Cells)
                            {
                                if (row.RowIndex % 2 == 0)
                                {
                                    cell.BackColor = grdStockMoment.AlternatingRowStyle.BackColor;
                                }
                                else
                                {
                                    cell.BackColor = grdStockMoment.RowStyle.BackColor;
                                }
                                cell.CssClass = "textmode";
                            }
                        }

                        grdStockMoment.RenderControl(hw);

                        //style to format numbers to string
                        string style = @"<style> .textmode { mso-number-format:\@; } </style>";
                        Response.Write(style);
                        Response.Output.Write(sw.ToString());
                        Response.Flush();
                        Response.End();
                    }

                }
            }





        }








        // New Change 



        protected void grdStockMoment_RowDataBound(object sender, GridViewRowEventArgs e)
        {


            try
            {

                if (cmbBranches.Visible == true)
                {
                    #region  Validation Between HQ Branch and Normal Branch 
                    BranchFullName = cmbBranches.SelectedItem.Text;      // This will Return Branch FullName 
                    if (BranchFullName == "HQ")
                    {
                        BranchAlias = Session["Branch_HQ"].ToString();
                    }
                    else
                    {
                        BranchFullName = cmbBranches.SelectedItem.Text;     // This will Return Branch FullName 
                        Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                        BranchAlias = Cmb_BranchName;

                    }
                    #endregion
                }
                else
                {
                    cd_BranchAlias = Session["Branch_HQ"].ToString();

                }

                if (e.Row.RowType == DataControlRowType.DataRow)
                {

                    // XSS Attack Prevention 
                    string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtStartDt.Text.Trim());
                    string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtEndDt.Text.Trim());


                    GridView gv = (GridView)e.Row.FindControl("grdDetails");
                    string RCategoryId = ((DataRowView)e.Row.DataItem)["CategoryID"].ToString().Trim();
                    // string RBarcode  = ((DataRowView)e.Row.DataItem)["Barcode"].ToString().Trim();

                    string Qry2 = "Select    T.InventoryID, i.InventoryName ,C.CategoryName, T.Categoryid,T.Barcode,T.RefDate, T.RefType, T.RefNo, "
                           + "   sum(T.StockInQty) as StockInQty,  sum(T.StockOutQty) as StockOutQty,sum(T.StockCheck) as StockCheck  from {0}.t1invmove as T   inner join {0}.inventory  as i "
                           + " On T.inventoryid=i.inventoryid inner join {0}.inventorycategories as C on T.Categoryid=C.Categoryid "
                           + " where date(refdate) Between  @D1 and @D2   and  T.categoryid=@categoryid   group by i.Barcode ";

                    string Command = string.Format(Qry2, BranchAlias);


                    MySqlParameter[] parameters =
                    {
                     new MySqlParameter("@D1",StartDT),
                     new MySqlParameter("@D2",EndDt),
                     new MySqlParameter("@categoryid",RCategoryId),
                    };

                    DtK = ObjMain.NewGetData_DT(Command, parameters);

                    //DtK = ObjMain.GetData_DT(Qry2);   Existing Code 



                    gv.DataSource = DtK;
                    gv.DataBind();

                }

            }
            catch (Exception Ex)
            {
                ObjLog.Write(Ex);
            }




        }
        protected void grdDetails_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            // XSS Attack Prevention 
            string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtStartDt.Text.Trim());
            string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtEndDt.Text.Trim());


            try
            {

                if (cmbBranches.Visible == true)
                {
                    #region  Validation Between HQ Branch and Normal Branch 
                    BranchFullName = cmbBranches.SelectedItem.Text;      // This will Return Branch FullName 
                    if (BranchFullName == "HQ")
                    {
                        BranchAlias = Session["Branch_HQ"].ToString();
                    }
                    else
                    {
                        BranchFullName = cmbBranches.SelectedItem.Text;     // This will Return Branch FullName 
                        Cmb_BranchName = ObjUserBL.GetBranchRealValue(Session["Branch_HQ"].ToString(), Session["UserId"].ToString(), BranchFullName);  // This will Return BranchName( Actual Database Name )
                        BranchAlias = Cmb_BranchName;

                    }
                    #endregion

                }
                else
                {
                    BranchAlias = Session["Branch_HQ"].ToString();

                }





                if (e.Row.RowType == DataControlRowType.DataRow)
                {

                    GridView gvTransDetail = (GridView)e.Row.FindControl("grdTransDetails");
                    string RBarcode = ((DataRowView)e.Row.DataItem)["Barcode"].ToString().Trim();

                    string Qry2 = "Select * from {0}.t1invmove where date(refdate) between  @D1 and @D2 and  Barcode=@Barcode order by refdate ;";
                    string command = string.Format(Qry2, BranchAlias);

                    MySqlParameter[] parameters =
                    {
                     new MySqlParameter("@D1",StartDT),
                     new MySqlParameter("@D2",EndDt),
                     new MySqlParameter("@Barcode",RBarcode),
                    };
                    DtK = ObjMain.NewGetData_DT(command, parameters);
                    gvTransDetail.DataSource = DtK;
                    gvTransDetail.DataBind();

                }


            }
            catch (Exception Ex)
            {
                ObjLog.Write(Ex);
            }



        }

        protected void OnRowDataBoundChild(object sender, GridViewRowEventArgs e)
        {
            //if (e.Row.RowType == DataControlRowType.DataRow)
            //{
            //    decimal Gtaxableamount = Convert.ToDecimal((e.Row.FindControl("lblSales") as Label).Text);
            //    totaltaxableamount += Gtaxableamount;
            //    Gtotaltaxableamount += Gtaxableamount;

            //    decimal Gtaxamount = Convert.ToDecimal((e.Row.FindControl("lblTaxablemaount") as Label).Text);
            //    totaltaxamount += Gtaxamount;
            //    Gtotaltaxamount += Gtaxamount;

            //}
            //if (e.Row.RowType == DataControlRowType.Footer)
            //{

            //}
        }





    }
}