protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Assembly assembly = Assembly.Load("Reports, Version=1.0.0.0, Culture=neutral, PublicKeyToken=f51c855eec632536");
            Stream stream = assembly.GetManifestResourceStream("Reports.Reports.rdlc");
            MemoryStream rdl = new MemoryStream();
            CopyTo(stream, rdl);

            rptInspectorPlan.Reset();
            rptInspectorPlan.LocalReport.LoadReportDefinition(rdl);
            rptInspectorPlan.LocalReport.DataSources.Clear();

            rptInspectorPlan.LocalReport.DataSources.Add(new ReportDataSource("MyData", finalData(getInspectionPlan())));
            rptInspectorPlan.LocalReport.Refresh();
        }
    }

    protected DataTable getInspectionPlan()
    {
        DataTable dt = new DataTable();
        DataTable dtCurrentUser = getCurrentUser();
        using (SPSite site = new SPSite(getMarketSurveillanceURL()))
        {
            using (SPWeb web = site.OpenWeb())
            {
                SPList list = web.Lists.TryGetList("InspectionPlan");
                SPQuery oQuery = new SPQuery();

                string query = "<Where><Eq><FieldRef Name='Title'/><Value Type='Text'>" + dtCurrentUser.Rows[0]["ID"].ToString() + "</Value></Eq></Where>";//getCurrentUserID()
                oQuery.Query = query;
                dt = list.GetItems(oQuery).GetDataTable();
            }
        }
        return dt;
    }

    protected string getMarketSurveillanceURL()
    {
        string URL = ConfigurationManager.AppSettings["MarketSurveillanceURL"].ToString();
        return URL;
    }

    protected string getMainSiteURL()
    {
        string URL = ConfigurationManager.AppSettings["MainSiteURL"].ToString();
        return URL;
    }

    protected DataTable getCurrentUser()
    {
        SPUser user = SPContext.Current.Web.CurrentUser;
        DataTable currentUser = new DataTable();
        using (SPSite site = new SPSite(getMarketSurveillanceURL()))
        {
            using (SPWeb web = site.OpenWeb())
            {
                SPList list = web.Lists.TryGetList("Inspector");
                SPQuery oQuery = new SPQuery();

                string query = "<Query><Where><Eq><FieldRef Name='LoginID'/><Value Type='Text'>" + user.LoginName + "</Value></Eq></Where></Query>";
                oQuery.ViewFields = "<FieldRef Name='ID'/><FieldRef Name='Title'/>";
                oQuery.Query = query;
                currentUser = list.GetItems(oQuery).GetDataTable();
            }
        }
        return currentUser;
    }

    protected DataTable finalData(DataTable inspectionPlan)
    {
        DataTable dt = inspectionPlan;
        DataTable dtCurrentUser = getCurrentUser();

        dt.Columns.Add("InspectorName");
        dt.Columns.Add("ProductName");
        dt.Columns.Add("CategoryName");
        dt.Columns.Add("InspectionTypeName");
        foreach (DataRow dr in dt.Rows)
        {
            if (dr["Title"].ToString() == dtCurrentUser.Rows[0]["ID"].ToString()) dr["InspectorName"] = dtCurrentUser.Rows[0]["Title"].ToString();
            dr["ProductName"] = getProductName(dr["ProductID"].ToString());
            dr["CategoryName"] = getCategoryName(dr["ProductID"].ToString());
            switch (dr["InspectionType"].ToString())
            {
                case "0":
                    dr["InspectionTypeName"] = "Periodic";
                    break;
                case "1":
                    dr["InspectionTypeName"] = "ComplainFollowup";
                    break;
            }
        }
        dt.AcceptChanges();
        return dt;
    }

    protected string getProductName(string id)
    {
        string productName = "";
        DataTable dt = new DataTable();
        using (SPSite site = new SPSite(getMarketSurveillanceURL()))
        {
            using (SPWeb web = site.OpenWeb())
            {
                SPList list = web.Lists.TryGetList("Product");
                SPQuery oQuery = new SPQuery();

                string query = "<Where><Eq><FieldRef Name='ID'/><Value Type='Text'>" + id + "</Value></Eq></Where>";//getCurrentUserID()
                oQuery.ViewFields = "<FieldRef Name='ProductEN'/><FieldRef Name='ProductAr'/>";
                oQuery.Query = query;
                dt = list.GetItems(oQuery).GetDataTable();
            }
        }
        foreach (DataRow dr in dt.Rows)
        {
            productName = dr["ProductEN"].ToString();
        }
        return productName;
    }

    protected string getCategoryName(string productID)
    {
        string categoryName = "", CategoryID = "";
        DataTable dtCategory = new DataTable();
        DataTable dtProduct = new DataTable();
        using (SPSite site = new SPSite(getMarketSurveillanceURL()))
        {
            using (SPWeb web = site.OpenWeb())
            {
                SPList list = web.Lists.TryGetList("Product");
                SPQuery oQuery = new SPQuery();

                string query = "<Where><Eq><FieldRef Name='ID'/><Value Type='Counter'>" + productID + "</Value></Eq></Where>";
                oQuery.ViewFields = "<FieldRef Name='CategoryID'/>";
                oQuery.Query = query;
                dtProduct = list.GetItems(oQuery).GetDataTable();
            }
        }
        foreach (DataRow dr in dtProduct.Rows)
        {
            CategoryID = dr["CategoryID"].ToString();
        }
        using (SPSite site = new SPSite(getMainSiteURL()))
        {
            using (SPWeb web = site.OpenWeb())
            {
                SPList list = web.Lists.TryGetList("SAS_Categories");
                SPQuery oQuery = new SPQuery();

                string query = "<Where><Eq><FieldRef Name='ID'/><Value Type='Text'>" + CategoryID + "</Value></Eq></Where>";
                oQuery.ViewFields = "<FieldRef Name='Category_Title_EN'/><FieldRef Name='Category_Title_Ar'/>";
                oQuery.Query = query;
                dtCategory = list.GetItems(oQuery).GetDataTable();
            }
        }
        foreach (DataRow dr in dtCategory.Rows)
        {
            categoryName = dr["Category_Title_EN"].ToString();
        }
        return categoryName;
    }

    public static void CopyTo(Stream input, MemoryStream output)
    {
        byte[] buffer = new byte[16 * 1024]; // Fairly arbitrary size
        int bytesRead;

        while ((bytesRead = input.Read(buffer, 0, buffer.Length)) > 0)
        {
            output.Write(buffer, 0, bytesRead);
        }
    }