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.
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);
}
}
.jpg)
.jpg)
.jpg)
.jpg)
.jpg)
.jpg)
.jpg)
.jpg)
.jpg){kind=small}
.jpg)