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 MySql.Data;
using CLS_BL;
using CLS_DL;
using CLS_PL;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html;
using iTextSharp.text.html.simpleparser;
using System.Web.UI.DataVisualization.Charting;
using System.Drawing.Printing;
using System.Drawing;
using System.Drawing.Text;
using System;
using QRCoder;
using DocumentFormat.OpenXml.Bibliography;








namespace SUNWEB
{
    public partial class DetailStkReport : 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();
        Int32 K = 0;
        Int32 L = 0;
        Int32 S = 0;
        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();
        System.Data.DataTable DT = new DataTable();
        System.Data.DataTable Dt1 = new DataTable();
        System.Data.DataTable Dt2 = new DataTable();
        System.Data.DataTable DtLicense = new DataTable();
        System.Data.DataTable DtSuperAdmin = new DataTable();

        System.Data.DataTable DtNormalUser = new DataTable();

        System.Data.DataTable DTSession = new DataTable();
        DataTable DtResult=new DataTable();

        MySqlCommand Cmdv = new MySqlCommand();
        // User Variables 
        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_RetailPayment = string.Empty;
        string L_DetailStockReprt = 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 ISHQ = string.Empty;
        DataSet DS = new DataSet();
        //ReportDocument rep = new ReportDocument();
        string Vw_Chk = string.Empty;
        string BraNam = "";
        string FDate = "";
        string TDate = "";
        string SubQryValue = string.Empty;
        string SubQryValue1 = string.Empty;
        string BranchAlias = string.Empty;
        // Added On 02-12-2021
        string ReadHQ = string.Empty;
        string RBranchid = string.Empty;
        string Branchid = string.Empty;
        protected void Page_Load(object sender, EventArgs e)
        {


            PID = "20";    // This is DetailStock Report New  Page : Use Back  DetailStock PID 


            if (!IsPostBack)
            {
                
                txtFromDt.Text = Curr_DateOnly;
                txtToDt.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;
                LoadDetailedStockMainQuery(cd_BranchAlias, txtFromDt.Text, txtToDt.Text);
            }
            else if (((Convert.ToBoolean(Session["IsMultiBranch"])) == true) && (Session["PortalTypeId"].ToString() == ""))
            {
                if (!IsPostBack)
                {
                    LoadBranchesOnID();
                    LoadCategoryList(cd_BranchAlias);
                    cmbSearchBy.Items.Clear();
                    cmbSearchBy.Items.Add("InventoryID");
                    cmbSearchBy.Items.Add("Barcode");
                    cmbSearchBy.Items.Add("Description");
                    cmbSearchBy.Items.Add("Name");
                    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;
                }
                if ((Session["L_DetailStockReprt"].ToString() == "N") || (Session["Page_Acess"].ToString() == "N"))
                {
                    Response.Redirect("DeniedPage.aspx");
                }
                else
                {

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


                    lblMsgTotalSelection.Text = "Search Result - from " + "&nbsp;&nbsp;" + StartDT + "&nbsp;&nbsp;  to  &nbsp;&nbsp;" + EndDt;
                    idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White; font-size:14px;");
                    if (!IsPostBack)
                    {
                        if (Session["Isdowld_Acess"].ToString() == "N")
                        {
                            // btnExcel.Enabled = false;
                            Response.Write(@"<script language='javascript'>alert('No Access To download.')</script>");
                        }
                        else
                        {
                            btnGenerate.Visible = true;

                            if (cmbBranches.Visible == false)
                            {
                                LoadDetailedStockMainQuery(Session["Branch_HQ"].ToString(), StartDT, EndDt);
                            }

                        }


                    }

                }

            }
            else
            {

            }

            

        }


        public void ActiveCall()
        {
            string QueryCal = "select ActiveValue from portaldb.usermaster  where Userid=" + Session["UserId"].ToString() + " ";
            ActiveCallvalue = ObjMain.ExecuteScalar(QueryCal);
            if (ActiveCallvalue == "")
            {
                ActiveCallvalue = ObjSel.GetFirstBranch7(Session["Branch_HQ"].ToString(), Session["UserId"].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);
                lblcurrentdb.Text = Cmb_BranchName.ToString().Trim(); //john added on 14-Jan-2025

            }

            #endregion


            if (Branchidval != "")
            {
                DT = ObjUserBL.GetPageRightsDetailsPIDNew(Session["UserId"].ToString(), PID, Branchidval);
            }
            else
            {
                DT = ObjUserBL.GetPageRightsDetailsPIDNew(Session["UserId"].ToString(), PID, Session["Fistbranch"].ToString());

            }

            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_DetailStockReprt = DtLicense.Rows[0]["DETAILSTOCKREPORT"].ToString();
                Session["L_DetailStockReprt"] = L_DetailStockReprt;
            }
            if ((Session["L_DetailStockReprt"] == null) || Session["L_DetailStockReprt"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
            {

                if (cmbBranches.SelectedItem.Text == "HQ")
                {

                    // XSS Attack Prevention 
                    string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text.Trim());
                    string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.Text.Trim());
                    BranchAlias = Session["Branch_HQ"].ToString();
                    lblMsgTotalSelection.Text = "Search Result - from " + "&nbsp;&nbsp;" + StartDT + "&nbsp;&nbsp;  to  &nbsp;&nbsp;" + EndDt;
                    idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White; font-size:14px;");

                    FDate = txtFromDt.Text;
                    TDate = txtToDt.Text;
                    LoadCategoryList(BranchAlias);
                    cmbSearchBy.Items.Clear();
                    cmbSearchBy.Items.Add("InventoryID");
                    cmbSearchBy.Items.Add("Barcode");
                    cmbSearchBy.Items.Add("Description");
                    cmbSearchBy.Items.Add("Name");
                    LoadDetailedStockMainQuery(BranchAlias, StartDT, EndDt);
                    // BindGridview(ActiveCallvalue, txtFromDt.Text, txtToDt.Text);
                    System.Threading.Thread.Sleep(2000);

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




            }
            else
            {

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


                //btnExcel.Enabled = true;
                //lblMsgTotalSelection.Text = "Search Result - Detailed Stock Report Between" + "&nbsp;&nbsp;&nbsp;" + txtFromDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtToDt.Text;
                lblMsgTotalSelection.Text = "Search Result - from " + "&nbsp;&nbsp;" + StartDT + "&nbsp;&nbsp;  to  &nbsp;&nbsp;" + EndDt;
                idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White; font-size:14px;");
                BranchAlias = cd_BranchAlias;
                FDate = txtFromDt.Text;
                TDate = txtToDt.Text;
                LoadCategoryList(BranchAlias);
                cmbSearchBy.Items.Clear();
                cmbSearchBy.Items.Add("InventoryID");
                cmbSearchBy.Items.Add("Barcode");
                cmbSearchBy.Items.Add("Description");
                cmbSearchBy.Items.Add("Name");
                LoadDetailedStockMainQuery(BranchAlias, StartDT, EndDt);
                // BindGridview(ActiveCallvalue, txtFromDt.Text, txtToDt.Text);
                System.Threading.Thread.Sleep(2000);

            }

        }


        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 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", false);
            }
        }


        protected void cmbBranches_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmbBranches.SelectedIndex == 0)
            {
                Response.Redirect("DetailstockReport.aspx", false);
            }
            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_DetailStockReprt = DtLicense.Rows[0]["DETAILSTOCKREPORT"].ToString();
                    // Create Session for License Variables 
                    Session["L_MaxUserCnt"] = L_MaxUserCnt;
                    Session["L_DetailStockReprt"] = L_DetailStockReprt;
                }
                if ((Session["L_DetailStockReprt"] == null) || Session["L_DetailStockReprt"].ToString() == "N" || Session["Page_Acess"].ToString() == "N")
                {
                    if (cmbBranches.SelectedItem.Text == "HQ")
                    {
                        // XSS Attack Prevention 
                        string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text.Trim());
                        string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.Text.Trim());

                        lblMsgTotalSelection.Text = "Search Result - from " + "&nbsp;&nbsp;" + StartDT + "&nbsp;&nbsp;  to  &nbsp;&nbsp;" + EndDt;
                        idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White; font-size:14px;");
                        BranchAlias = Session["Branch_HQ"].ToString();

                        FDate = txtFromDt.Text;
                        TDate = txtToDt.Text;
                        LoadCategoryList(BranchAlias);
                        cmbSearchBy.Items.Clear();
                        cmbSearchBy.Items.Add("InventoryID");
                        cmbSearchBy.Items.Add("Barcode");
                        cmbSearchBy.Items.Add("Description");
                        cmbSearchBy.Items.Add("Name");
                        LoadDetailedStockMainQuery(BranchAlias, StartDT, EndDt);


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



                }
                else
                {
                    //btnExcel.Enabled = true;

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

                    lblMsgTotalSelection.Text = "Search Result - from " + "&nbsp;&nbsp;" + StartDT + "&nbsp;&nbsp;  to  &nbsp;&nbsp;" + EndDt;
                    idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White; font-size:14px;");
                    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;
                    FDate = txtFromDt.Text;
                    TDate = txtToDt.Text;
                    LoadCategoryList(BranchAlias);
                    cmbSearchBy.Items.Clear();
                    cmbSearchBy.Items.Add("InventoryID");
                    cmbSearchBy.Items.Add("Barcode");
                    cmbSearchBy.Items.Add("Description");
                    cmbSearchBy.Items.Add("Name");
                    LoadDetailedStockMainQuery(BranchAlias, StartDT, EndDt);
                    //BindGridview(cd_BranchAlias, txtFromDt.Text, txtToDt.Text);
                }
                UPDATEBRANCH(Cmb_BranchName);
                lblcurrentdb.Text = Cmb_BranchName.ToString().Trim(); //john added on 14-Jan-2025
            }

        }

        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");
            }

        }



        //from here onwards report code begins
        protected void btnGenerate_Click(object sender, EventArgs e)
        {

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


            if (cmbBranches.Visible == false)
            {
                btnGenerate.Visible = true;
                // lblMsgTotalSelection.Text = "Search Result - Detailed Stock Between" + "&nbsp;&nbsp;&nbsp;" + txtFromDt.Text + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + txtToDt.Text;
                lblMsgTotalSelection.Text = "Search Result - from " + "&nbsp;&nbsp;" + StartDT + "&nbsp;&nbsp;  to  &nbsp;&nbsp;" + EndDt;
                idGenReport.Attributes.Add("style", "background-color: #4c4c4c; color: White; font-size:14px;");
                LoadDetailedStockMainQuery(Session["Branch_HQ"].ToString(), StartDT, EndDt);
                System.Threading.Thread.Sleep(2000);
            }
            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



                lblMsgTotalSelection.Text = " Search Result - Detailed Stock Between" + "&nbsp;&nbsp;&nbsp;" + StartDT + "&nbsp;&nbsp;&nbsp;  And &nbsp;&nbsp;&nbsp;" + EndDt;
                FDate = txtFromDt.Text;
                TDate = txtToDt.Text;
                LoadDetailedStockMainQuery(Microsoft.Security.Application.Encoder.HtmlEncode(BranchAlias), StartDT, EndDt);
            }
            //ScriptManager.RegisterStartupScript(this, this.GetType(), "Script", " loadDate('" + FDate + "','" + TDate + "','" + cmbCategoryname.SelectedItem.Text + "','" + cmbSearchBy.SelectedItem.Text + "','" + txtBarcode.Text + "');", true); //john added on 03-Jan-2025
        }
        protected void LoadDetailedStockMainQuery(string BranchAlias, string D1, string D2)
        {

            // SQL Injection Prevention :04-10-21
            // Parameterized Approach 

            System.Threading.Thread.Sleep(1000);

            string sCmd;

            if (cmbCategoryname.SelectedItem.Text == "Select Category")
            {
                SubQryValue = "";
            }
            else
            {
                SubQryValue = " And ICA.CategoryName='" + cmbCategoryname.SelectedItem.Text + "' ";
            };

            switch (cmbSearchBy.SelectedItem.Text)
            {
                case "InventoryID":
                    if (txtBarcode.Text == "")
                    {
                        SubQryValue1 = "";
                    }
                    else
                    {
                        SubQryValue1 = " And IT.InventoryID like  CONCAT('%', @Barcode, '%') ";
                    };
                    break;
                case "Barcode":
                    if (txtBarcode.Text == "")
                    {
                        SubQryValue1 = "";
                    }
                    else
                    {
                        SubQryValue1 = " And IT.Barcode like CONCAT('%', @Barcode, '%') ";
                    };
                    break;
                case "Description":
                    if (txtBarcode.Text == "")
                    {
                        SubQryValue1 = "";
                    }
                    else
                    {
                        SubQryValue1 = " And IT.Description like CONCAT('%', @Barcode, '%') ";
                    };
                    break;
                case "Name":
                    if (txtBarcode.Text == "")
                    {
                        SubQryValue1 = "";
                    }
                    else
                    {
                        SubQryValue1 = " And IT.InventoryName like CONCAT('%', @Barcode, '%') ";
                    };
                    break;
                default:
                    SubQryValue1 = "";
                    break;
            }
            //Nid's Existing Query 
            //sCmd = " Select IT.InventoryID, IT.Barcode,IT.InventoryName,IT.Description,ICA.CategoryNumber,ICA.CategoryName," +
            //    " format(OP.OPENINGBALANCE,2) as OPENINGBALANCE, " +
            //    " if(RT.qty is null,0,-RT.QTY) as RetailSales,if(SI.QTY is null,0,SI.QTY) as StockIn,if(SO.QTY is null,0,SO.QTY) as StockOut, " +
            //    " if(PI.QTY is null,0,PI.QTY) as PInvoice,if(PR.QTY is null,0,-PR.QTY) as PReturn, " +

            //    " if(TR.QTY is null,0,TR.QTY) as Transfer,if(RS.QTY is null,0,RS.QTY) as Receive,if(TRR.QTY is null,0,TRR.QTY) as TransferRet,if(WT.QTY is null,0,WT.QTY) as Wastage,if(ST.QTY is null,0,ST.QTY) as StockCheck," +    //Removed Negative Value(required benny)
            //    " OP.OPENINGBALANCE+(if(SI.QTY is null,0,SI.QTY) -if(RT.qty is null,0,RT.QTY)+if(SO.QTY is null,0,SO.QTY) +if(PI.QTY is null,0,PI.QTY) -if(PR.QTY is null,0,PR.QTY) -if(TR.QTY is null,0,TR.QTY) +if(RS.QTY is null,0,RS.QTY)+ if(TRR.QTY is null,0,TRR.QTY) + if(WT.QTY is null,0,WT.QTY) + if(ST.QTY is null,0,ST.QTY)) as ClosingBalance" +
            //    " From {0}.Inventory IT  Left Join {0}.InventoryCategories ICA ON IT.CATEGORYID=ICA.CATEGORYID" +
            //    " Left Join (Select BarCode,Sum(qty) as Qty From (Select b.barcode,b.qty AS qty From {0}.trans A join {0}.TransDetail B on A.transid=B.transid Where A.TransStatus in (2,3,4) and b.itemstatus<>1 and A.businessdate  Between @D1 and @D2 " +
            //    " Union All" +
            //    " SELECT D.BarCode, B.Qty * C.Quantity  As StockOutQty 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 in (2,3,4) And B.ItemStatus<>1 And B.Class=3 and A.businessdate  Between @D1 and @D2 " +
            //    " )T Group by T.BARCODE) RT on IT.BARCODE=RT.BARCODE" +
            //    " LEFT JOIN (SELECT InventoryID,sum(Adjustquantity) AS QTY FROM {0}.Inventoryadjust Where DateAdjusted  Between @D1 and @D2 and adjustquantity>0 And If(notes is null,'',notes) not like '%STOCKCHECK%' And If(notes is null,'',notes) not like '%Wastage%' Group by inventoryid) SI on IT.INVENTORYID=SI.INVENTORYID" +
            //    " LEFT JOIN (SELECT InventoryID,sum(Adjustquantity) as qty FROM {0}.Inventoryadjust Where DateAdjusted   Between @D1 and @D2 and adjustquantity<0  And If(notes is null,'',notes) not like '%STOCKCHECK%' And If(notes is null,'',notes) not like '%Wastage%' Group by inventoryid) SO ON IT.INVENTORYID=SO.INVENTORYID" +
            //    " LEFT JOIN (SELECT D.InventoryID,sum((D.PackCapacity*D.PackQuantity)+D.LO+D.FOC) as qty  FROM {0}.Purchaseinvoice C JOIN {0}.Purchaseinvoicedetail D ON C.InvoiceID=D.Invoiceid Where C.PinvoiceDate   Between @D1 and @D2  group by D.inventoryid)PI ON IT.INVENTORYID=PI.INVENTORYID " +
            //    " LEFT JOIN (SELECT Q.InventoryID,sum((Q.PackCapacity*Q.PackQuantity)+Q.LO+Q.FOC) as qty  FROM {0}.Purchasereturn P JOIN {0}.Purchasereturndetail Q on P.ReturnID=Q.ReturnID Where PReturnDate   Between @D1 and @D2  group by Q.inventoryid)PR ON IT.INVENTORYID=PR.INVENTORYID " +
            //    " LEFT JOIN (SELECT H.InventoryID,sum((H.PackCapacity * H.PackQuantity)+ H.LooseQuantity+H.FreeOfCharge-H.RejectQuantity) as qty  FROM {0}.Transferstock G JOIN {0}.Transferstockdetail H On G.TransferID=H.TransferID Where Mid(G.sendDateTime,1,10)   Between @D1 and @D2 AND G.sendFlag=1 and G.Confirmflag = 1 and G.senddatetime is not null group by H.inventoryid) TR on IT.INVENTORYID=TR.INVENTORYID" +
            //    " LEFT JOIN (SELECT J.Barcode,sum(J.AcceptQuantity) as qty FROM {0}.Receivestock I JOIN {0}.Receivestockdetail J on I.receiveid=J.ReceiveID Where Mid(I.sendDateTime,1,10)  Between @D1 and @D2 and ConfirmFlag = 1 and J.barcode is not null Group by J.barcode) RS ON IT.BARCODE=RS.BARCODE" +
            //    " LEFT JOIN (SELECT L.Barcode,sum(L.ReturnQuantity) as qty FROM {0}.ReturnStock K JOIN {0}.ReturnStockDetail L on K.ReturnID=L.ReturnID Where Mid(K.ReturnDate,1,10)  Between @D1 and @D1  and L.barcode is not null Group by L.barcode) TRR ON IT.BARCODE=TRR.BARCODE" +
            //    " LEFT JOIN (SELECT InventoryID,sum(Adjustquantity)as qty FROM {0}.inventoryadjust Where DateAdjusted   Between @D1 and @D2 And If(notes is null,'',notes) like '%Wastage%' Group by inventoryid) WT ON IT.INVENTORYID=WT.INVENTORYID" +
            //    " LEFT JOIN (SELECT InventoryID,sum(Adjustquantity)as qty FROM {0}.inventoryadjust Where DateAdjusted   Between @D1 and @D2 And If(notes is null,'',notes) like '%STOCKCHECK%' Group by inventoryid) ST ON IT.INVENTORYID=ST.INVENTORYID" +
            //    " LEFT JOIN (Select IT.INVENTORYID, IT.barcode,IT.DESCRIPTION, if(SI.QTY is null,0,SI.QTY) -if(RT.qty is null,0,RT.QTY)+if(SO.QTY is null,0,SO.QTY) +if(PI.QTY is null,0,PI.QTY) -if(PR.QTY is null,0,PR.QTY) -if(TR.QTY is null,0,TR.QTY) +if(RS.QTY is null,0,RS.QTY)+ if(TRR.QTY is null,0,TRR.QTY) + if(WT.QTY is null,0,WT.QTY) + if(ST.QTY is null,0,ST.QTY) as OpeningBalance From {0}.Inventory IT" +
            //    " LEFT JOIN (Select BarCode,Sum(qty) as Qty From (Select b.barcode,b.qty AS qty From {0}.trans A join {0}.TransDetail B on A.transid=B.transid Where A.TransStatus in (2,3,4) and b.itemstatus<>1 and A.businessdate  < @D1 " +
            //    " Union All" +
            //    " SELECT D.BarCode, B.Qty * C.Quantity  As StockOutQty   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 in (2,3,4) And B.ItemStatus<>1 And B.Class=3 and A.businessdate  < @D1 " +
            //    " )T Group by T.BARCODE) RT on IT.BARCODE=RT.BARCODE" +
            //    " LEFT Join (SELECT InventoryID,sum(Adjustquantity) AS QTY FROM {0}.Inventoryadjust Where DateAdjusted  < @D1  and adjustquantity>0 And If(notes is null,'',notes) not like '%STOCKCHECK%' Group by inventoryid) SI on IT.INVENTORYID=SI.INVENTORYID" +
            //    " LEFT JOIN (SELECT InventoryID,sum(Adjustquantity) as qty FROM {0}.Inventoryadjust Where DateAdjusted   < @D1   and adjustquantity<0 And If(notes is null,'',notes) not like '%STOCKCHECK%' Group by inventoryid) SO ON IT.INVENTORYID=SO.INVENTORYID" +
            //    " LEFT JOIN (SELECT D.InventoryID,sum((D.PackCapacity*D.PackQuantity)+D.LO+D.FOC) as qty FROM {0}.Purchaseinvoice C JOIN {0}.Purchaseinvoicedetail D ON C.invoiceID=D.invoiceid Where C.PinvoiceDate   < @D1   group by D.inventoryid)PI ON IT.INVENTORYID=PI.INVENTORYID " +
            //    " LEFT JOIN (SELECT Q.InventoryID,sum((Q.PackCapacity*Q.PackQuantity)+Q.LO+Q.FOC) as qty FROM {0}.Purchasereturn P JOIN {0}.Purchasereturndetail Q on P.ReturnID=Q.ReturnID Where PReturnDate < @D1   Group by Q.inventoryid)PR ON IT.INVENTORYID=PR.INVENTORYID " +

            //    " LEFT JOIN (SELECT H.InventoryID,sum((H.PackCapacity * H.PackQuantity)+ H.LooseQuantity+H.FreeOfCharge-H.RejectQuantity) as qty FROM {0}.Transferstock G JOIN {0}.Transferstockdetail H On G.TransferID=H.TransferID Where Mid(G.sendDateTime,1,10)  < @D1  AND G.sendFlag=1 and G.Confirmflag = 1 and G.senddatetime is not null group by H.inventoryid) TR on IT.INVENTORYID=TR.INVENTORYID" +

            //    " LEFT JOIN (SELECT J.Barcode,sum(J.AcceptQuantity) as qty FROM {0}.Receivestock I JOIN {0}.Receivestockdetail J on I.receiveid=J.ReceiveID Where Mid(I.sendDateTime,1,10)   < @D1 and ConfirmFlag = 1 And J.barcode is not null Group by J.barcode) RS ON IT.BARCODE=RS.BARCODE" +
            //    " LEFT JOIN (SELECT L.Barcode,sum(L.ReturnQuantity) as qty FROM {0}.ReturnStock K JOIN {0}.ReturnStockDetail L on K.ReturnID=L.ReturnID Where Mid(K.ReturnDate,1,10)  < @D1  and L.barcode is not null Group by L.barcode) TRR ON IT.BARCODE=TRR.BARCODE" +
            //    " LEFT JOIN (SELECT InventoryID,sum(Adjustquantity)as qty FROM {0}.inventoryadjust Where DateAdjusted   < @D1  And If(notes is null,'',notes) like '%Wastage%' Group by inventoryid) WT ON IT.INVENTORYID=WT.INVENTORYID" +
            //    " LEFT JOIN (SELECT InventoryID,sum(Adjustquantity)as qty FROM {0}.inventoryadjust where DateAdjusted < @D1 and If(notes is null,'',notes) like '%STOCKCHECK%' Group by inventoryid) ST ON IT.INVENTORYID=ST.INVENTORYID Order by IT.INVENTORYID) OP ON IT.INVENTORYID=OP.INVENTORYID" +
            //    " Where IT.Class In (0,2,9) " + SubQryValue + SubQryValue1 +
            //    " Order by IT.CATEGORYID, IT.INVENTORYID";


            sCmd = " Select IT.InventoryID, IT.Barcode,IT.InventoryName,IT.Description,ICA.CategoryNumber,ICA.CategoryName,format(OP.OPENINGBALANCE,2) as OPENINGBALANCE, "
                 + " if (RT.qty is null,0,-RT.QTY) as RetailSales,if (SI.QTY is null,0,SI.QTY) as StockIn,if (SO.QTY is null,0,SO.QTY) as StockOut, "
                 + " if (PI.QTY is null,0,PI.QTY) as PInvoice , if (PR.QTY is null,0,-PR.QTY) as PReturn, if (TR.QTY is null,0,TR.QTY) as Transfer, "
                 + " if (RS.QTY is null,0,RS.QTY) as Receive ,if (TRR.QTY is null,0,TRR.QTY) as TransferRet,if (WT.QTY is null,0,WT.QTY) as Wastage, "
                 + " if (ST.QTY is null,0,ST.QTY) as StockCheck , OP.OPENINGBALANCE + (if (SI.QTY is null,0,SI.QTY) -if (RT.qty is null,0,RT.QTY)+if (SO.QTY is null,0,SO.QTY) +if (PI.QTY is null,0,PI.QTY) "
                 + " -if (PR.QTY is null,0,PR.QTY) -if (TR.QTY is null,0,TR.QTY) +if (RS.QTY is null,0,RS.QTY)+if (TRR.QTY is null,0,TRR.QTY) + if (WT.QTY is null,0,WT.QTY) + if (ST.QTY is null,0,ST.QTY)) as ClosingBalance "
                 + " From {0}.Inventory IT "
                 + " Left Join {0}.InventoryCategories ICA ON IT.CATEGORYID = ICA.CATEGORYID "
                 + " Left Join(Select BarCode, Sum(qty) as Qty From(Select b.barcode, b.qty AS qty "
                 + " From {0}.trans A join {0}.TransDetail B on A.transid = B.transid "
                 + " INNER JOIN "
                 + " {0}.transdetailcosting as C "
                 + " ON C.Transid = A.Transid AND C.Itemid = B.Itemid "
                 + " Where A.TransStatus in (2, 3, 4) and b.itemstatus<>1 and date(A.businessdate) "
                 + " Between @D1 and @D2 "
                 + " Union All SELECT D.BarCode, B.Qty * C.Quantity  As StockOutQty "
                 + " 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 "
                 + " INNER JOIN "
                 + " {0}.transdetailcosting as CT "
                 + " ON CT.Transid = A.Transid AND CT.Itemid = B.Itemid "
                 + " WHERE A.TransStatus in (2, 3, 4) And B.ItemStatus<>1 And B.Class = 3 "
                 + " and date(A.businessdate)  Between @D1 and @D2 )T Group by T.BARCODE) RT on IT.BARCODE = RT.BARCODE "
                 + " LEFT JOIN "
                 + " (SELECT AD.InventoryID, sum(Adjustquantity) AS QTY "
                 + " FROM {0}.Inventoryadjust as AD "
                 + " left JOIN {0}.inventorycostaveragedetail ic ON ic.Inventoryid = AD.inventoryid "
                 + " Where date(DateAdjusted) "
                 + " Between @D1 and @D2 and adjustquantity> 0 "
                 + " And If(AD.notes is null,'',AD.notes) not like '%STOCKCHECK%' "
                 + " And If(AD.notes is null,'',AD.notes) not like '%Wastage%' "
                 + " AND Ad.DateAdjusted = ic.StockDate "
                 + " Group by inventoryid) SI on IT.INVENTORYID = SI.INVENTORYID "
                 + " LEFT JOIN(SELECT AD.InventoryID, sum(AD.Adjustquantity) as qty "
                 + " FROM {0}.InventoryAdjust AD Inner Join {0}.Inventory IV On AD.InventoryID = IV.InventoryID "
                 + " INNER JOIN {0}.inventorycostaveragedetail ic "
                 + " ON AD.InventoryID = ic.InventoryID AND AD.DateAdjusted = ic.StockDate "
                 + " WHERE "
                 + " AD.AdjustQuantity < 0 And AD.Notes<>'*STOCKCHECK*' "
                 + " AND AD.Notes = 'Adj Out' "
                 + " AND DATE(DateAdjusted) Between @D1 and @D2) SO "
                 + " ON IT.INVENTORYID = SO.INVENTORYID "
                 + " LEFT JOIN(SELECT D.InventoryID, sum((D.PackCapacity* D.PackQuantity)+D.LO + D.FOC) as qty "
                 + " FROM {0}.Purchaseinvoice C JOIN {0}.Purchaseinvoicedetail D ON C.InvoiceID = D.Invoiceid "
                 + " Where C.PinvoiceDate "
                 + " Between @D1 and @D2 "
                 + " group by D.inventoryid)PI ON IT.INVENTORYID = PI.INVENTORYID "
                 + " LEFT JOIN(SELECT Q.InventoryID, sum((Q.PackCapacity* Q.PackQuantity)+Q.LO + Q.FOC) as qty "
                 + " FROM {0}.Purchasereturn P "
                 + " JOIN {0}.Purchasereturndetail Q on P.ReturnID = Q.ReturnID "
                 + " Where date(PReturnDate) "
                 + " Between @D1 and @D2  group by Q.inventoryid)PR ON IT.INVENTORYID = PR.INVENTORYID"
                 + " LEFT JOIN(SELECT IV.InventoryID, "
                 + " Sum((Qty-rejectedqty)) As qty "
                 + " FROM(({0}.TransferStock A "
                 + " INNER JOIN {0}.transferstockdetailedcost B ON A.TransferID = B.TransferID and A.confirmflag = 1) "
                 + " Inner Join {0}.Inventory IV "
                 + " On B.InventoryID = IV.InventoryID) "
                 + " WHERE DATE(A.TransferDate)    Between @D1 and @D2 GROUP BY B.Inventoryid) TR on IT.INVENTORYID = TR.INVENTORYID "
                 + " LEFT JOIN "
                 + " (SELECT I.InventoryID as InventoryID,"
                 + " sum(B.AcceptedQty) AS qty "
                 + " FROM {0}.receivestock A "
                 + " INNER JOIN "
                 + " {0}.receivestockdetailedcost as B ON A.ReceiveID = B.ReceiveID and A.Confirmflag = 1 "
                 + " Inner Join {0}.Inventory I "
                 + " On B.InventoryID = I.InventoryID "
                 + " WHERE DATE(A.TransferDate)   Between @D1 and @D2 Group By I.InventoryID) RS "
                 + " ON IT.InventoryID = RS.InventoryID "
                 + " LEFT JOIN(SELECT* "
                 + " FROM (SELECT ic.InventoryID, "
                 + " SUM(ic.inQty) AS qty "
                 + " FROM "
                 + " {0}.inventorycostaveragedetail ic "
                 + " Inner Join {0}.Inventory IV On ic.InventoryID= IV.InventoryID "
                 + " where ic.notes= '(TrR) Stock In' "
                 + " And "
                 + " date(ic.StockDate) Between @D1 and @D2 Group by IV.Inventoryid) T) TRR "
                 + " ON IT.InventoryID = TRR.InventoryID "
                 + " LEFT JOIN(SELECT InventoryID, sum(Adjustquantity)as qty "
                 + " FROM {0}.inventoryadjust "
                 + " Where date(DateAdjusted)  "
                 + " Between @D1 and @D2 And If(notes is null,'',notes) like '%Wastage%' Group by inventoryid) WT "
                 + " ON IT.INVENTORYID = WT.INVENTORYID "
                 + " LEFT JOIN(SELECT InventoryID, sum(Adjustquantity)as qty "
                 + " FROM {0}.inventoryadjust "
                 + " Where date(DateAdjusted) "
                 + " Between @D1 and @D2 And If(notes is null,'',notes) like '%STOCKCHECK%' "
                 + " Group by inventoryid) ST ON IT.INVENTORYID = ST.INVENTORYID "
                 + " LEFT JOIN(Select IT.INVENTORYID, IT.barcode, IT.DESCRIPTION,"
                 + " if (SI.QTY is null,0,SI.QTY) -if (RT.qty is null,0,RT.QTY)+if (PI.QTY is null,0,PI.QTY) -if (PR.QTY is null,0,PR.QTY) -if (TR.QTY is null,0,TR.QTY) +if (RS.QTY is null,0,RS.QTY) + if (TRR.QTY is null,0,TRR.QTY) + if (WT.QTY is null,0,WT.QTY) + if (ST.QTY is null,0,ST.QTY) as OpeningBalance "
                 + " From {0}.Inventory IT "
                 + " LEFT JOIN(Select BarCode, Sum(qty) as Qty From(Select b.barcode, b.qty AS qty "
                 + " From {0}.trans A join {0}.TransDetail B on A.transid = B.transid"
                 + " INNER JOIN "
                 + " {0}.transdetailcosting as CT "
                 + " ON CT.Transid = A.Transid AND CT.Itemid = B.Itemid "
                 + " Where A.TransStatus in (2, 3, 4) "
                 + " and b.itemstatus<>1 and A.businessdate <  @D1 "
                 + " Union All "
                 + " SELECT D.BarCode, B.Qty * C.Quantity  As StockOutQty   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 "
                 + " INNER JOIN "
                 + " {0}.transdetailcosting as CT "
                 + " ON CT.Transid = A.Transid AND CT.Itemid = B.Itemid "
                 + " WHERE A.TransStatus in (2, 3, 4) " 
                 + " And B.ItemStatus<>1 And B.Class = 3 and A.businessdate < @D1)T "
                 + " Group by T.BARCODE) RT on IT.BARCODE = RT.BARCODE "
                 + " LEFT Join(SELECT AD.InventoryID, sum(Adjustquantity) AS QTY "
                 + " FROM {0}.Inventoryadjust as AD "
                 + " left JOIN {0}.inventorycostaveragedetail ic ON ic.Inventoryid = AD.inventoryid "
                 + " Where date(DateAdjusted) < @D1 "
                 + " and adjustquantity> 0 "
                 + " And If(AD.notes is null,'',AD.notes) not like '%STOCKCHECK%' "
                 + " And If(AD.notes is null,'',AD.notes) not like '%Wastage%' "
                 + " AND Ad.DateAdjusted = ic.StockDate "
                 + " Group by inventoryid order by Inventoryid) SI on IT.INVENTORYID = SI.INVENTORYID "
                 + " LEFT JOIN( SELECT AD.InventoryID, sum(AD.Adjustquantity) as qty "
                 + " FROM {0}.InventoryAdjust AD Inner Join {0}.Inventory IV On AD.InventoryID = IV.InventoryID "
                 + " INNER JOIN {0}.inventorycostaveragedetail ic "
                 + " ON AD.InventoryID = ic.InventoryID AND AD.DateAdjusted = ic.StockDate "
                 + " WHERE "
                 + " AD.AdjustQuantity < 0 And AD.Notes<>'*STOCKCHECK*' "
                 + " AND AD.Notes = 'Adj Out' "
                 + " AND DATE(DateAdjusted) <  @D1 Group by inventoryid) SO ON IT.INVENTORYID = SO.INVENTORYID "
                 + " LEFT JOIN(SELECT D.InventoryID, sum((D.PackCapacity* D.PackQuantity)+D.LO + D.FOC) as qty "
                 + " FROM {0}.Purchaseinvoice C JOIN {0}.Purchaseinvoicedetail D ON C.invoiceID = D.invoiceid Where C.PinvoiceDate < @D1 "
                 + " group by D.inventoryid)PI ON IT.INVENTORYID = PI.INVENTORYID "
                 + " LEFT JOIN(SELECT Q.InventoryID, sum((Q.PackCapacity* Q.PackQuantity)+Q.LO + Q.FOC) as qty FROM {0}.Purchasereturn P "
                 + " JOIN {0}.Purchasereturndetail Q on P.ReturnID = Q.ReturnID Where PReturnDate<@D1 " 
                 + " Group by Q.inventoryid)PR ON IT.INVENTORYID = PR.INVENTORYID "
                 + " LEFT JOIN(SELECT IV.InventoryID, Sum((Qty-rejectedqty)) As qty "
                 + " FROM(({0}.TransferStock A "
                 + " INNER JOIN {0}.transferstockdetailedcost B ON A.TransferID = B.TransferID and A.confirmflag = 1) "
                 + " Inner Join {0}.Inventory IV "
                 + " On B.InventoryID = IV.InventoryID) "
                 + " WHERE date(A.TransferDate)<@D1"
                 + " GROUP BY B.Inventoryid)TR on IT.INVENTORYID = TR.INVENTORYID"
                 + " LEFT JOIN(SELECT I.InventoryID as InventoryID, "
                 + " sum(B.AcceptedQty) AS qty "
                 + " FROM {0}.receivestock A "
                 + " INNER JOIN "
                 + " {0}.receivestockdetailedcost as B ON A.ReceiveID = B.ReceiveID and A.Confirmflag = 1"
                 + " Inner Join {0}.Inventory I "
                 + " On B.InventoryID = I.InventoryID "
                 + " WHERE DATE(A.TransferDate) < @D1 "
                 + " Group By I.InventoryID) "
                 + " RS ON IT.INVENTORYID = RS.INVENTORYID "
                 + " LEFT JOIN(SELECT L.Barcode, sum(L.ReturnQuantity) as qty FROM {0}.ReturnStock K "
                 + " JOIN {0}.ReturnStockDetail L on K.ReturnID = L.ReturnID "
                 + " Where Mid(K.ReturnDate,1,10)  <@D1  and L.barcode is not null Group by L.barcode) TRR ON IT.BARCODE = TRR.BARCODE "
                 + " LEFT JOIN(SELECT InventoryID, sum(Adjustquantity)as qty FROM {0}.inventoryadjust Where DateAdjusted < @D1  And If(notes is null,'',notes) like '%Wastage%' "
                 + " Group by inventoryid) WT ON IT.INVENTORYID = WT.INVENTORYID "
                 + " LEFT JOIN(SELECT InventoryID, sum(Adjustquantity)as qty "
                 + " FROM {0}.inventoryadjust where  date(DateAdjusted) <@D1 and If(notes is null,'',notes) like '%STOCKCHECK%' Group by inventoryid) " 
                 + " ST ON IT.INVENTORYID = ST.INVENTORYID " 
                 + " Order by IT.INVENTORYID) OP ON IT.INVENTORYID = OP.INVENTORYID "
                 + " Where IT.Class In(0,2,9)  " + SubQryValue + SubQryValue1 
                 + " Order by IT.CATEGORYID, IT.INVENTORYID; ";


            string newquery = string.Format(sCmd, BranchAlias);
            MySqlParameter[] parameters =
            {
                new MySqlParameter("@Barcode",txtBarcode.Text.ToString().ToString()),
                new MySqlParameter("@D1",D1),
                new MySqlParameter("@D2",D2)
            };
            Dt1 = ObjMain.NewGetData_DT(newquery, parameters);
            Session["StockReport"] = Dt1;
            GridView1.DataSource = Dt1;
            //gridview totals
            GridView1.DataBind();
        }
        protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            // XSS Attack Prevention 
            string StartDT = Microsoft.Security.Application.Encoder.HtmlEncode(txtFromDt.Text.Trim());
            string EndDt = Microsoft.Security.Application.Encoder.HtmlEncode(txtToDt.Text.Trim());
            if (cmbBranches.Visible == false)
            {
                GridView1.PageIndex = e.NewPageIndex;
                LoadDetailedStockMainQuery(Session["Branch_HQ"].ToString(), StartDT, EndDt);
            }
            else
            {

                #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

                GridView1.PageIndex = e.NewPageIndex;
                LoadDetailedStockMainQuery(BranchAlias, StartDT, EndDt);
            }
        }
        protected void btnExcel_Click(object sender, EventArgs e)
        {
            DTSession = (DataTable)Session["StockReport"];
            //Create a dummy GridView
            GridView GridView2 = new GridView();
            GridView2.AllowPaging = false;
            GridView2.DataSource = DTSession;
            GridView2.DataBind();
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition",
            "attachment;filename=StockExport.xls");
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            for (int i = 0; i < GridView2.Rows.Count; i++)
            {
                //Apply text style to each Row
                GridView2.Rows[i].Attributes.Add("class", "textmode");
            }
            GridView2.RenderControl(hw);
            string style = @"<style> td { mso-number-format:\@; } </style>";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
        }
        public override void VerifyRenderingInServerForm(Control control)
        {
            /* Confirms that an HtmlForm control is rendered for the specified ASP.NET
               server control at run time. */
        }
        public void cmbCategoryname_SelectedIndexChanged(object sender, EventArgs e)
        {
        }
        public void cmbSearchBy_SelectedIndexChanged(object sender, EventArgs e)
        {
        }
        public void LoadCategoryList(string BranchAlias)
        {
            cmbCategoryname.DataSource = ObjSel.Inv_CategoryList(BranchAlias);
            cmbCategoryname.DataTextField = "Description";
            cmbCategoryname.DataValueField = "CategoryID";
            cmbCategoryname.DataBind();
            cmbCategoryname.Items.Insert(0, "Select Category");
        }
        public DataTable LoadChild(string InventroyID, string Branhc_Nam)
        {
            string CDate = "  between  @StartDT and  @EndDt  ";
            string StartDT =txtFromDt.Text.Trim();
            string EndDt = txtToDt.Text.Trim();
            string QryLoadChild = "SELECT "
            + " A.InventoryID, "
            + " IV.CategoryID, "
            + " ic.BatchId, "
            + " Barcode,InventoryName, "
            + " 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 "
            + " INNER JOIN {0}.inventorycostaveragedetail ic "
            + " ON A.InventoryID = ic.InventoryID "
            + " AND A.DateAdjusted = ic.StockDate  "
            + " WHERE AdjustQuantity > 0 "
            + " AND DATE(DateAdjusted) " + CDate + " And A.Inventoryid =@Inventoryid "
            + " UNION ALL"
            + " SELECT A.InventoryID,IV.CategoryID,ic.BatchID,Barcode,InventoryName,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 "
            + " INNER JOIN {0}.inventorycostaveragedetail ic "
            + " ON A.InventoryID = ic.InventoryID AND A.DateAdjusted = ic.StockDate "
            + " WHERE "
            + " AdjustQuantity < 0 And A.Notes<>'*STOCKCHECK*' "
            + " AND DATE(DateAdjusted) "+ CDate + " AND A.Inventoryid =@Inventoryid "
            + " UNION ALL "
            + " SELECT * "
            + " FROM(SELECT ic.InventoryID, IV.CategoryID, ic.BatchID, Barcode,InventoryName,IV.Description, ic.StockDate AS RefDate, "
            + " '(TrR) Stock In' AS RefType, CONCAT('', trim(ic.Notes)) AS RefNo, Abs(ic.inQty) AS StockInQty, 0.00 As StockOutQty, 0 As StockCheck "
            + " FROM "
            + " {0}.inventorycostaveragedetail ic "
            + " Inner Join {0}.Inventory IV On ic.InventoryID = IV.InventoryID "
            + " where ic.notes = '(TrR) Stock In' "
            + " And "
            + " date(ic.StockDate) "+ CDate + " AND  IV.Inventoryid =@Inventoryid "
            + " Union All "
            + " SELECT I.InventoryID, I.CategoryID, C.Batchid as BatchID,I.BarCode,InventoryName,I.Description, A.TransDate As RefDate, "
            + " IF(C.SQty > 0, 'Retail Sales', 'Exchange/Return') As RefType, "
            + " Concat(A.TransID, '(', Trim(A.TransNo), ')') AS RefNo, 0.00 AS StockInQty, C.SQty As StockOutQty, 0 As StockCheck "
            + " FROM {0}.trans as A "
            + " inner join "
            + " {0}.Transdetail as B "
            + " ON A.Transid = B.Transid "
            + " INNER JOIN "
            + " {0}.transdetailcosting as C "
            + " ON C.Transid = A.Transid AND C.Itemid = B.Itemid "
            + " INNER JOIN "
            + " {0}.Inventory as I  on I.Inventoryid = C.Inventoryid "
            + " where "
            + " A.TransStatus = 2 and b.ItemStatus<>1 AND "
            + " date(A.Businessdate) "+ CDate + " AND  I.Inventoryid =@Inventoryid "
            + " UNION  ALL "
            + " SELECT I.InventoryID,I.CategoryID,iC.Batchid as BatchID,I.BarCode,InventoryName,I.Description,A.TransDate As RefDate," 
            + " IF(ic.inQty < 0, 'Cancel Transaction', 'Sales-Cancel') As RefType, "
            + " Concat(A.TransID, '(', Trim(A.TransNo), ')') AS RefNo, iC.iNQty As StockInQty, 0.00 AS StockOutQty, 0 As StockCheck "
            + " FROM {0}.trans as A "
            + " inner join "
            + " {0}.Transdetail as B "
            + " ON A.Transid = B.Transid "
            + " INNER JOIN "
            + " {0}.Inventory as I  on I.Barcode = B.Barcode "
            + " INNER JOIN "
            + " {0}.inventoryCostaveragedetail as ic on I.Inventoryid = ic.Inventoryid "
            + " where "
            + " A.TransStatus = 5 and b.ItemStatus<>1 AND ic.notes = 'RSC' AND "
            + " date(A.Businessdate) " + CDate + " AND  I.Inventoryid =@Inventoryid " 
            + " UNION  ALL "
            + " SELECT IV.InventoryID, IV.CategoryID, B.BatchID,IV.Barcode,InventoryName,IV.Description,date(SendDateTime) As RefDate, '(Tr) Stock Out' As RefType, TransferNo As RefNo, 0.00 AS StockInQty, "
            + " (Qty - rejectedqty) As StockOutQty, 0 As StockCheck "
            + " FROM(({0}.TransferStock A INNER JOIN {0}.transferstockdetailedcost B ON A.TransferID = B.TransferID and A.confirmflag = 1) "
            + " Inner  Join {0}.Inventory IV "
            + " On B.InventoryID = IV.InventoryID) "
            + " WHERE DATE(A.TransferDate)  "+ CDate + " AND IV.Inventoryid = @Inventoryid "
            + " UNION ALL "
            + " SELECT I.InventoryID,I.CategoryID, B.BatchID,I.Barcode,InventoryName,I.Description,date(SendDateTime) As RefDate,'Receive Stock IN' As RefType, "
            + " TransferNo As RefNo, B.AcceptedQty AS StockInQty, "
            + " 0.00 AS StockOutQty, 0 As StockCheck "
            + " FROM {0}.receivestock A "
            + " INNER JOIN "
            + " {0}.receivestockdetailedcost B ON A.ReceiveID = B.ReceiveID and A.Confirmflag = 1 "
            + " Inner Join {0}.Inventory I "
            + " On B.InventoryID = I.InventoryID "
            + " WHERE DATE(A.TransferDate) "+ CDate + ") as U WHERE U.Inventoryid =@Inventoryid; ";
            string command = string.Format(QryLoadChild, Branhc_Nam);
            MySqlParameter[] parameters =
            {
                new MySqlParameter("@StartDT",StartDT),
                new MySqlParameter("@EndDt",EndDt),
                new MySqlParameter("@Inventoryid", InventroyID),
            };
            DtResult = ObjMain.NewGetData_DT(command, parameters);
            return DtResult;
        }
        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            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)
            {
                string Inventoryid = GridView1.DataKeys[e.Row.RowIndex].Value.ToString();
                GridView gvstockdetails = e.Row.FindControl("gvchild") as GridView;
                gvstockdetails.DataSource = LoadChild(Convert.ToString(Inventoryid), BranchAlias);
                gvstockdetails.DataBind();
            }


        }
    }
}