protected void CopySQLTableData()
{
SPSecurity.RunWithElevatedPrivileges(delegate()
{
try
{
HVEFiles.dbConnection conn = new HVEFiles.dbConnection();
LogAction_SQLDataInsert("Before starting foreach of SQL");
string sclearsql = string.Concat(@"[dbo].[CopySearchData]");
//DataTable tempTable = null;
//tempTable = conn.executeSelectNoParameter(sclearsql);
conn.executeSelectNoParameter(sclearsql);
}
catch (Exception Exception)
{
LogAction_SQLDataInsert(Exception.Message);
}
});
}
protected void DocViewCounts1(string siteObj, string libName)
{
#region t1
SPSecurity.RunWithElevatedPrivileges(delegate()
{
try
{
using (SPSite site = new SPSite(siteObj))
{
using (SPWeb web = site.OpenWeb())
{
SPList list = web.Lists[libName];
SPListItemCollection coll = list.GetItems();
foreach (SPListItem item in coll)
{
SPAuditQuery spQuery = new SPAuditQuery(site);
spQuery.RestrictToListItem(item);
SPAuditEntryCollection auditCol = site.Audit.GetEntries(spQuery);
string docName = "";
int counter = 0;
foreach (SPAuditEntry entry in auditCol)
{
if (entry.ItemType == SPAuditItemType.Document && entry.Event == SPAuditEventType.View)
{
try
{
var1 = entry.DocLocation.Substring(entry.DocLocation.LastIndexOf("/"));
var2 = var1.Substring(var1.LastIndexOf("/"));
var3 = var2.Substring(1);
var4 = var3.Substring(var3.LastIndexOf('.') + 1);
if (var4 != "aspx")
{
if (entry.EventSource == SPAuditEventSource.SharePoint)
{
if (docName != var3)
{
docName = var3;
counter = 1;
DataRow drRow = dtAudit.NewRow();
drRow["DocName"] = var3;
drRow["DocLocation"] = entry.DocLocation;
drRow["DownloadCount"] = 1;
dtAudit.Rows.Add(drRow);
}
else
{
DataRow[] drExists = dtAudit.Select("DocName = '" + var3 + "' AND DocLocation = '" + entry.DocLocation + "'");
if (drExists != null && drExists.Length > 0)
{
drExists[0]["DownloadCount"] = Convert.ToInt32(drExists[0]["DownloadCount"]) + 1;
}
counter = counter + 1;
}
}
}
}
catch (Exception ee)
{
LogAction(ee.Message);
}
}
}
}
}
}
using (SPSite docSite = new SPSite("http://win-njfp7te48bn/sites/HVEDev"))
{
using (SPWeb docWeb = docSite.OpenWeb())
{
SPList docList = docWeb.Lists["TopViewedDocs"];
foreach (DataRow rr in dtAudit.Rows)
{
try
{
#region SQL Command for select from [AAES Home].[dbo].[TopViewedDocsTable11]
HVEFiles.dbConnection conn = new HVEFiles.dbConnection();
sqlDocName = rr["DocName"].ToString();
sqlDocLocation = rr["DocLocation"].ToString();
string sclearsql = string.Concat("SELECT * FROM [AAES Home].[dbo].[TopViewedDocsTable11] " +
"WHERE DocName = @DocName AND DocLocation = @DocLocation");
SqlParameter[] parameter =
{
new SqlParameter("@DocName", SqlDbType.VarChar)
{
Value = sqlDocName
},
new SqlParameter("@DocLocation", SqlDbType.VarChar)
{
Value = sqlDocLocation
}
};
DataTable tempTable = null;
tempTable = conn.executeSelectQuery(sclearsql, parameter);
if ((tempTable == null) || (tempTable.Rows.Count == 0))
{
//insert
string sclearsqlIns = string.Concat("INSERT INTO [AAES Home].[dbo].[TopViewedDocsTable11] " +
"(DocName, DocLocation, DownloadCount) " +
"VALUES(@DocName, @DocLocation, @DownloadCount)");
SqlParameter[] parameterUpd =
{
new SqlParameter("@DocName", SqlDbType.VarChar)
{
Value = rr["DocName"].ToString()
},
new SqlParameter("@DocLocation", SqlDbType.VarChar)
{
Value = rr["DocLocation"].ToString()
},
new SqlParameter("@DownloadCount", SqlDbType.Int)
{
Value = Convert.ToInt32(rr["DownloadCount"])
}
};
bool isInsert = conn.executeInsertQuery(sclearsqlIns, parameterUpd);
}
else
{
//update
string sclearsqlUpd = string.Concat("UPDATE [AAES Home].[dbo].[TopViewedDocsTable11] " +
"SET DownloadCount = @DownloadCount " +
"WHERE DocName = @DocName AND DocLocation = @DocLocation");
SqlParameter[] parameterUpd =
{
new SqlParameter("@DocName", SqlDbType.VarChar)
{
Value = rr["DocName"].ToString()
},
new SqlParameter("@DocLocation", SqlDbType.VarChar)
{
Value = rr["DocLocation"].ToString()
},
new SqlParameter("@DownloadCount", SqlDbType.Int)
{
Value = Convert.ToInt32(rr["DownloadCount"])
}
};
bool isInsert = conn.executeUpdateQuery(sclearsqlUpd, parameterUpd);
}
#endregion
}
catch (Exception Exception1)
{
LogAction(Exception1.Message);
}
}
}
}
}
catch (Exception eee)
{
LogAction(eee.Message);
}
});
#endregion
}
protected void DocViewCounts(string siteObj, string libName)
{
#region t1
SPSecurity.RunWithElevatedPrivileges(delegate()
{
using (SPSite siteLogList = new SPSite("http://win-njfp7te48bn/sites/HVEDev"))
{
using (SPWeb webLogList = siteLogList.OpenWeb())
{
SPList logList = webLogList.Lists["TimerLogList"];
SPListItem logListItems = null;
try
{
using (SPSite site = new SPSite(siteObj))
{
using (SPWeb web = site.OpenWeb())
{
//lbltest.Text += "<br/>" + web.Title.ToString();
SPList list = web.Lists[libName]; //Documents
SPListItemCollection coll = list.GetItems();
//Dictionary<string, int> dictionary = new Dictionary<string, int>();
//dtAudit.Columns.Add("DocName");
//dtAudit.Columns.Add("DocLocation");
//dtAudit.Columns.Add("DownloadCount");
foreach (SPListItem item in coll)
{
SPAuditQuery spQuery = new SPAuditQuery(site);
spQuery.RestrictToListItem(item);
SPAuditEntryCollection auditCol = site.Audit.GetEntries(spQuery);
string docName = "";
int counter = 0;
foreach (SPAuditEntry entry in auditCol)
{
if (entry.ItemType == SPAuditItemType.Document && entry.Event == SPAuditEventType.View)
{
try
{
var1 = entry.DocLocation.Substring(entry.DocLocation.LastIndexOf("/"));
var2 = var1.Substring(var1.LastIndexOf("/"));
var3 = var2.Substring(1);
var4 = var3.Substring(var3.LastIndexOf('.') + 1);
if (var4 != "aspx")
{
if (entry.EventSource == SPAuditEventSource.SharePoint)
{
if (docName != var3)
{
docName = var3;
counter = 1;
DataRow drRow = dtAudit.NewRow();
drRow["DocName"] = var3;
drRow["DocLocation"] = entry.DocLocation;
drRow["DownloadCount"] = 1;
//dictionary.Add(var3, 1);
dtAudit.Rows.Add(drRow);
}
else
{
DataRow[] drExists = dtAudit.Select("DocName = '" + var3 + "' AND DocLocation = '" + entry.DocLocation + "'");
if (drExists != null && drExists.Length > 0)
{
//int cont = drExists[0]["DownloadCount"];
drExists[0]["DownloadCount"] = Convert.ToInt32(drExists[0]["DownloadCount"]) + 1;
//drExists[0]["DownloadCount"];
}
//if (dictionary.TryGetValue(var3, out counter))
//{
// dictionary[var3] = counter + 1;
//}
counter = counter + 1;
}
}
}
}
catch (Exception ee)
{
// SPSecurity.RunWithElevatedPrivileges(delegate()
// {
//lbltest.Text += "<br/>" + web.Title.ToString();
//SPList logList = webLogList.Lists["TimerLogList"]; //Documents
//SPListItem logListItems =
logList.Items.Add();
logListItems["Title"] = "2 :- SPAuditEntry :- " + ee.Message;
logListItems.Update();
// }
// }
//});
}
}
}
}
}
}
using (SPSite docSite = new SPSite("http://win-njfp7te48bn/sites/HVEDev"))
{
using (SPWeb docWeb = docSite.OpenWeb())
{
SPList docList = docWeb.Lists["TopViewedDocs"];
//foreach (KeyValuePair<string, int> pair in dictionary)
foreach (DataRow rr in dtAudit.Rows)
{
#region List Data Insert
////////htmlStr.Append(("Document Name: " + pair.Key.ToString() + " - " + "Views Count: " + pair.Value.ToString()) + "<br>");
//////SPListItem li = docList.Items.Add();
//////li["DocName"] = rr["DocName"].ToString();// pair.Value.ToString();
//////li["DocLocation"] = rr["DocLocation"].ToString();// pair.Value.ToString();
//////li["DocViewCount"] = Convert.ToInt32(rr["DownloadCount"]);// pair.Value.ToString();
//////li["Title"] = "Data Entered at :- " + DateTime.Now.ToString();
////////li["DocViewCount"] = pair.Value.ToString();
////////li["Title"] = "Data Entered at :- " + DateTime.Now.ToString();
//////li.Update();
#endregion
try
{
#region SQL Command for select from [AAES Home].[dbo].[TopViewedDocsTable11]
HVEFiles.dbConnection conn = new HVEFiles.dbConnection();
//CustCode conn = new CustCode();
sqlDocName = rr["DocName"].ToString();
sqlDocLocation = rr["DocLocation"].ToString();
string sclearsql = string.Concat("SELECT * FROM [AAES Home].[dbo].[TopViewedDocsTable11] " +
"WHERE DocName = @DocName AND DocLocation = @DocLocation");
SqlParameter[] parameter =
{
new SqlParameter("@DocName", SqlDbType.VarChar)
{
Value = sqlDocName
},
new SqlParameter("@DocLocation", SqlDbType.VarChar)
{
Value = sqlDocLocation
}
};
DataTable tempTable = null;
//SPListItem logListItems = logList.Items.Add();
logList.Items.Add();
logListItems["Title"] = "3 :- SQL FUnctions :- " + "before executeSelectQuery";
logListItems.Update();
tempTable = conn.executeSelectQuery(sclearsql, parameter);
//SPListItem logListItems = logList.Items.Add();
logList.Items.Add();
logListItems["Title"] = "3 :- SQL FUnctions :- " + "After executeSelectQuery";
logListItems.Update();
if ((tempTable == null) || (tempTable.Rows.Count == 0))
{
//insert
string sclearsqlIns = string.Concat("INSERT INTO [AAES Home].[dbo].[TopViewedDocsTable11] " +
"(DocName, DocLocation, DownloadCount) " +
"VALUES(@DocName, @DocLocation, @DownloadCount)");
SqlParameter[] parameterUpd =
{
new SqlParameter("@DocName", SqlDbType.VarChar)
{
Value = rr["DocName"].ToString()
},
new SqlParameter("@DocLocation", SqlDbType.VarChar)
{
Value = rr["DocLocation"].ToString()
},
new SqlParameter("@DownloadCount", SqlDbType.Int)
{
Value = Convert.ToInt32(rr["DownloadCount"])
}
};
bool isInsert = conn.executeInsertQuery(sclearsqlIns, parameterUpd);
}
else
{
//update
string sclearsqlUpd = string.Concat("UPDATE [AAES Home].[dbo].[TopViewedDocsTable11] " +
"SET DownloadCount = @DownloadCount " +
"WHERE DocName = @DocName AND DocLocation = @DocLocation");
SqlParameter[] parameterUpd =
{
new SqlParameter("@DocName", SqlDbType.VarChar)
{
Value = rr["DocName"].ToString()
},
new SqlParameter("@DocLocation", SqlDbType.VarChar)
{
Value = rr["DocLocation"].ToString()
},
new SqlParameter("@DownloadCount", SqlDbType.Int)
{
Value = Convert.ToInt32(rr["DownloadCount"])
}
};
bool isInsert = conn.executeUpdateQuery(sclearsqlUpd, parameterUpd);
}
#endregion
}
catch (Exception Exception1)
{
//SPSecurity.RunWithElevatedPrivileges(delegate()
//{
// using (SPSite siteLogList = new SPSite("http://win-njfp7te48bn/sites/HVEDev"))
// {
// using (SPWeb webLogList = siteLogList.OpenWeb())
// {
//lbltest.Text += "<br/>" + web.Title.ToString();
// SPList logList = webLogList.Lists["TimerLogList"]; //Documents
// SPListItem logListItems = logList.Items.Add();
logList.Items.Add();
logListItems["Title"] = "3 :- SQL FUnctions :- " + Exception1.Message;
logListItems.Update();
// }
// }
//});
// Exception1Label.Text = Exception1.Message; //Label1
}
#region SQL Command for insert into SP
// string sclearsql = "sp_TopViewedDocs"; /*"INSERT INTO TopViewedDocsTable (TraineeName, LoginID,LearnerDSId, TraineeOnboardingDate,SupervisorLogin,FunctionalArea1,TraineeOrganization,RoleFamily1,Role1,FunctionalSpeciality1,ManagerRightsNeeded,ArchiveRole1,ArchiveRole2,ArchiveRole3,TraineeLocation,RoleFamily2,Role2,FunctionalArea2,FunctionalSpeciality2,RoleFamily3,Role3,FunctionalArea3,FunctionalSpeciality3) " +
//"VALUES (@TraineeName, @LoginID,@LearnerDSId, @TraineeOnboardingDate,@SupervisorLogin, @FunctionalArea1, @TraineeOrganization,@RoleFamily1,@Role1,@FunctionalSpeciality1,@ManagerRightsNeeded,@ArchiveRole1,@ArchiveRole2,@ArchiveRole3,@TraineeLocation,@RoleFamily2, @Role2, @FunctionalArea2,@FunctionalSpeciality2,@RoleFamily3,@Role3,@FunctionalArea3,@FunctionalSpeciality3)";*/
// SqlParameter[] parameter = {
// new SqlParameter("@DocName", SqlDbType.VarChar) { Value = rr["DocName"].ToString() },
// new SqlParameter("@DocLocation", SqlDbType.VarChar) { Value = rr["DocLocation"].ToString() },
// new SqlParameter("@DownloadCount", SqlDbType.Int) { Value = Convert.ToInt32(rr["DownloadCount"]) }
// };
// HVE.Files.dbConnection conn = new HVE.Files.dbConnection();
// bool isInsert = conn.executeInsertQuery(sclearsql, parameter);
#endregion
}
}
}
}
catch (Exception eee)
{
//Console.WriteLine(eee.Message);
//SPSecurity.RunWithElevatedPrivileges(delegate()
//{
// using (SPSite siteLogList1 = new SPSite("http://win-njfp7te48bn/sites/HVEDev"))
// {
// using (SPWeb webLogList1 = siteLogList1.OpenWeb())
// {
//lbltest.Text += "<br/>" + web.Title.ToString();
//SPList logList1 = webLogList1.Lists["TimerLogList"]; //Documents
//SPListItem logListItems = logList.Items.Add();
logList.Items.Add();
logListItems["Title"] = eee.Message;
logListItems.Update();
// }
// }
//});
}
//LiteralText.Text = htmlStr.ToString();
}
}
});
#endregion
}
