Saturday, January 17, 2015

Wiki Report

Hi,

Before going to the customization part, let me explore on the folder structure in the sub site.
There is a sub site called wiki site under the site collection.
In this, there are two folders called Enabling Functions and General.
In Enabling functions folder, there are sub folders, in those sub folders there are files.
In General folder, there are some files.
For every folder, there would be a SPOC that we can get the details from DB.
SPOC details would be in the table as below
But I should access the same through one of the stored procedure 
Now coming to requirement, should generate the report through below web part
Now report should be generated in two formats i.e.
1)If we select Report type as Consolidated, report should be in the below format.
-Under Category it should display the sub folder names of Enabling Functions folder, folder name of General.
-Under No of questions added, it should display the total files count in the respective folder.
-Under New questions added, it should display the count of files within the selected dates based on the Page Created Date.
-Under SPOC column, it should display the SPOCS of that folder from DB
Before writing the code in button click event, class has been instantiated to get the SPOC data.
We are logging the logs into the DB

WikiFAQsReport_BAL objWikiFAQsReport = new WikiFAQsReport_BAL();
public class WikiFAQsReport_BAL
    {
        WikiFAQsReport_DAL dataObj = new WikiFAQsReport_DAL();
        public DataSet GetAuthorNames(string type, string businessFunction)
        {
            try
            {
                return dataObj.GetAuthorNames(type, businessFunction);
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void WriteLogStatusFile(string PageName, string MethodName, String message)
        {
            StringBuilder Message = new StringBuilder();
            Message.Append("Page Name - ");
            Message.Append(PageName);
            Message.Append(" ");
            Message.Append("PageMethod Name - ");
            Message.Append(MethodName);
            Message.Append("message");
            Message.Append(message);

            //Storing it into DB   

            dataObj.LogStatus(PageName, MethodName, message);
        }
    }
public class WikiFAQsReport_DAL
    {
        public static string NucleusConnectionString = "Data Source=domain\\connection;Initial Catalog=DBnames;User ID=userID;Password=pswd";  // QA
        public DataSet GetAuthorNames(string type, string businessFunction)
        {
            try
            {
                SqlParameter[] par = new SqlParameter[2];
                par[0] = new SqlParameter("@BUSINESS_TYPE", SqlDbType.NVarChar);
                par[0].Value = type;
                par[1] = new SqlParameter("@BUSINESS_FUNCTION", SqlDbType.NVarChar);
                par[1].Value = businessFunction;
                DataSet ds = SqlHelper.ExecuteDataset(NucleusConnectionString, CommandType.StoredProcedure, "PROC_procedure_name", par);

                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        public void LogStatus(string PageName, string MethodName, string message)
        {
            try
            {
                SqlParameter[] par = new SqlParameter[3];
                par[0] = new SqlParameter("@PageName", SqlDbType.VarChar);
                par[0].Value = PageName;
                par[1] = new SqlParameter("@MethodName", SqlDbType.VarChar);
                par[1].Value = MethodName;
                par[2] = new SqlParameter("@Error", SqlDbType.VarChar);
                par[2].Value = message;
                DataSet ds = SqlHelper.ExecuteDataset(NucleusConnectionString, CommandType.StoredProcedure, "PROC_procedurename_ErrorLog", par);
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
For this I have written the code as below in the export to excel button click event

private void ConsolidatedReport()
        {
            string strwikisiteurl = SPContext.Current.Site.Url + "/WikiSite/";
            using (SPSite wikisite = new SPSite(strwikisiteurl))
            {
                using (SPWeb wikiweb = wikisite.OpenWeb())
                {
                    SPList list = wikiweb.Lists["Pages"];
                    dt.Columns.Add("Category");
                    dt.Columns.Add("No of questions added");
                    dt.Columns.Add("New Questions added");
                    dt.Columns.Add("SPOC");
                    foreach (SPFolder folder in list.RootFolder.SubFolders)
                    {
                        if (folder.Name != "Forms")
                        {
                            if (folder.Name == “General”)
                            {
                                dr = dt.NewRow();

                                dr["Category"] = “General”;

                                dr["No of questions added"] = folder.ItemCount.ToString();
                                int filecount = 0;
                                foreach (SPFile file in folder.Files)
                                {
                                    SPListItem item = file.Item;

                                    if ((file.TimeCreated >= dtFromDate.SelectedDate.Date) && (file.TimeCreated <= dtToDate.SelectedDate.Date))
                                    {
                                        filecount++;
                                    }
                                    if (filecount >= 1)
                                    {
                                        dr["New Questions added"] = filecount.ToString();
                                    }
                                    else
                                    {
                                       dr["New Questions added"] = filecount.ToString();
                                    }
                                    DataSet ds = objWikiFAQsReport.GetAuthorNames(“General”, folder.Name.ToString());
                                    if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                                    {
                                        if (ds.Tables[0].Rows.Count == 1)
                                        {
                                            getDisplayName(ds);
                                            SPUser user = web.EnsureUser(SPOCMId);
                                            dr["SPOC"] = user.Name;
                                        }
                                        else if (ds.Tables[0].Rows.Count > 1)
                                        {
                                            getDisplayName(ds);
                                            SPUser SPOCuser = web.EnsureUser(SPOCMId);
                                            SPOCMId = SPOCuser.Name;
                                            for (int i = 1; i < ds.Tables[0].Rows.Count; i++)
                                            {
                                                SPUser user = web.EnsureUser(ds.Tables[0].Rows[i]["SPOCMid"].ToString());
                                                if (user != null)
                                                {
                                                    SPOCMlIds = user.Name;
                                                }
                                                SPOCMId = SPOCMId + Constants.Comma + SPOCMlIds; ;
                                            }
                                            dr["SPOC"] = SPOCMId.ToString();
                                        }
                                    }

                                }
                                dt.Rows.Add(dr);
                                dt.AcceptChanges();
                            }
                            else if (folder.Name == “Enabling Functions”)
                            {

                                foreach (SPFolder subfolder in folder.SubFolders)
                                {
                                    if (subfolder.Name != "Forms")
                                    {
                                        if (subfolder.Files.Count > 0)
                                        {

                                            int filecount = 0;
                                            foreach (SPFile file in subfolder.Files)
                                            {
                                                dr = dt.NewRow();

                                                dr["Category"] = subfolder.Name;

                                                dr["No of questions added"] = subfolder.ItemCount.ToString();

                                                SPListItem item = file.Item;

                                                if ((file.TimeCreated >= dtFromDate.SelectedDate.Date) && (file.TimeCreated <= dtToDate.SelectedDate.Date))
                                                {

                                                    filecount++;

                                                }
                                                if (filecount >= 1)
                                                {
                                                    dr["New Questions added"] = filecount.ToString();
                                                }
                                                else
                                                {
                                                    dr["New Questions added"] = filecount.ToString();
                                                }


                                            }

              DataSet ds = objWikiFAQsReport.GetAuthorNames(folder.Name, subfolder.Name);

                                            if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                                            {

                                                if (ds.Tables[0].Rows.Count == 1)
                                                {
                                                    getDisplayName(ds);
                                                    SPUser user = web.EnsureUser(SPOCMId);
                                                    dr["SPOC"] = user.Name;

                                                }
                                                else if (ds.Tables[0].Rows.Count > 1)
                                                {
                                                    getDisplayName(ds);
                                                    SPUser SPOCuser = web.EnsureUser(SPOCMId);
                                                    SPOCMId = SPOCuser.Name;
                                                    for (int i = 1; i < ds.Tables[0].Rows.Count; i++)
                                                    {
                                                        SPUser user = web.EnsureUser(ds.Tables[0].Rows[i]["SPOCMid"].ToString());
                                                        if (user != null)
                                                        {
                                                            SPOCMlIds = user.Name;
                                                        }
                                                        SPOCMId = SPOCMId + Constants.Comma + SPOCMlIds; ;
                                                    }
                                                    dr["SPOC"] = SPOCMId.ToString();
                                                }
                                            }
                                            dt.Rows.Add(dr);
                                            dt.AcceptChanges();
                                        }
                                    }
                                }
                            }
                        }
                    }

                      ExportToExcel(dt);            
                    
                    
                }
            }
        }
private void getDisplayName(DataSet ds)
        {
            authorName = ds.Tables[0].Rows[0]["SPOCMid"].ToString();
            SPUser user = web.EnsureUser(authorName);
            SPOCMId = authorName;
        }
private void ExportToExcel(DataTable dt)
        {
            if (dt.Rows.Count > 0)
            {

                //excel file name         
                string filename = "WikiReport" + dtFromDate.SelectedDate.ToString("dd/MM/yy") + "-" + dtToDate.SelectedDate.ToString("dd/MM/yy") + ".xls";


                DataGrid dgGrid = new DataGrid();
                dgGrid.DataSource = dt;
                dgGrid.DataBind();

                System.IO.StringWriter tw = new System.IO.StringWriter();
                System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);

                dgGrid.RenderControl(hw);            

                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", filename));
                HttpContext.Current.Response.ContentType = "application/ms-excel";
                HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);
                //render the htmlwriter into the response 
                HttpContext.Current.Response.Write(tw.ToString());               
                HttpContext.Current.Response.End();             
              
            }
           
        }
private void getDisplayName(DataSet ds)
        {
            authorName = ds.Tables[0].Rows[0]["SPOCMid"].ToString();
            SPUser user = web.EnsureUser(authorName);
            SPOCMId = authorName;
        }

I was able to get the Consolidated report as below
2)Now another format is Question Wise Report as below.
-Under Question column files of General ,Enabling Functions sub folder files should be displayed
-Under View Count column, version count within the selected dates should  be displayed
-Under Last Update at each question level column, file modified date should be displayed.
-Under SPOCS column, respective SPOCS should be displayed from the DB.

...For this I am calling method in the button click event as below
private void QuestionWiseReport()
        {
            string strwikisiteurl = SPContext.Current.Site.Url + "/WikiSite/";
            using (SPSite wikisite = new SPSite(strwikisiteurl))
            {
                using (SPWeb wikiweb = wikisite.OpenWeb())
                {
                    SPList list = wikiweb.Lists["Pages"];
                    dt.Columns.Add("Category");
                    dt.Columns.Add("Question");
                    dt.Columns.Add("View count at each question level");
                    dt.Columns.Add("Last update at each question level");
                    dt.Columns.Add("SPOC");

                    foreach (SPFolder folder in list.RootFolder.SubFolders)
                    {
                        if (folder.Name != "Forms")
                        {
                            if (folder.Name == “General”)
                            {
                                foreach (SPFile file in folder.Files)
                                {
                                    //if(file.Name=="TestQue1.aspx")
                                    //dr["Last update at each question level"] = file.TimeLastModified;
                                    GenerateRecords(file, folder.Name, folder.Name);
                                    //dr["Last update at each question level"] = file.TimeLastModified;
                                }
                            }
                            else if (folder.Name == “Enabling Functions”)
                            {
                                foreach (SPFolder subfolder in folder.SubFolders)
                                {
                                    if (subfolder.Name != "Forms")
                                    {
                                        if (subfolder.Files.Count > 0)
                                        {
                                            foreach (SPFile file in subfolder.Files)
                                            {
                                                //if (file.Name == "TestQue1.aspx")
                                                GenerateRecords(file, folder.Name, subfolder.Name);
                                                //dr["Last update at each question level"] = file.TimeLastModified;
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                    ExportToExcel(dt);
                }
            }
        }
private void GenerateRecords(SPFile file, string folder, string subfolder)
        {
            try
            {
                SPListItem item = file.Item;
                dr = dt.NewRow();              
                dr["Question"] = file.Name.ToString();
                dr["Last update at each question level"] = file.TimeLastModified;
                //dr["Last update at each question level"] = file.TimeLastModified;

                //foreach (SPFileVersion v in file.Versions)
                //{
                //    dr["Last update at each question level"] = v.Created;
                //}                

                if (folder == “General”)
                {
                    dr["Category"] = “General”;
                    dr["View count at each question level"] = GetViewCount(Convert.ToString(item["Title"]), folder);
                }
                else if ((folder == “Enabling Functions”)
                {
                    dr["Category"] = subfolder;
                    dr["View count at each question level"] = GetViewCount(subfolder + "/" + Convert.ToString(item["Title"]), folder);
                }
                //dr["Last update at each question level"] = file.TimeLastModified;
                DataSet ds = objWikiFAQsReport.GetAuthorNames(folder, subfolder);
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    if (ds.Tables[0].Rows.Count == 1)
                    {
                        getDisplayName(ds);
                        SPUser user = web.EnsureUser(SPOCMId);
                        dr["SPOC"] = user.Name;

                    }
                    else if (ds.Tables[0].Rows.Count > 1)
                    {
                        getDisplayName(ds);
                        SPUser SPOCuser = web.EnsureUser(SPOCMId);
                        SPOCMId = SPOCuser.Name;
                        for (int i = 1; i < ds.Tables[0].Rows.Count; i++)
                        {
                            SPUser user = web.EnsureUser(ds.Tables[0].Rows[i]["SPOCMid"].ToString());
                            if (user != null)
                            {
                                SPOCMlIds = user.Name;
                            }
                            SPOCMId = SPOCMId + Constants.Comma + SPOCMlIds; ;
                        }
                        dr["SPOC"] = SPOCMId.ToString();

                    }
                }
                dt.Rows.Add(dr);
            }
            catch (Exception ex)
            {
                objWikiFAQsReport.WriteLogStatusFile("Inside Generate Records", "Generate Records()", "Generate Records: " + ex.Message);
            }
        }
Posted by at No comments:
Subscribe to: Posts (Atom)