public bool UpdateForeignKeys(EPMData DAO)
{
DataTable AllForeignKeysByList = GetAllForeignKeys(DAO);
//Loop thru list tables and ADD FOREIGN KEYS
foreach (DataRow AllListForeignKeys in AllForeignKeysByList.Rows)
{
//DELETE EXISTING FK's from list table
DAO.Command =
"BEGIN TRY " +
"WHILE EXISTS(select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where CONSTRAINT_NAME like 'FK_EPMLIVE_%' AND TABLE_NAME=@tableName) " +
"BEGIN " +
"DECLARE @cName nvarchar(Max) " +
"SELECT @cName = (SELECT TOP 1 [CONSTRAINT_NAME] FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE CONSTRAINT_NAME LIKE 'FK_EPMLIVE_%' AND TABLE_NAME=@tableName) " +
"DECLARE @sql nvarchar(Max) " +
"SELECT @sql = 'ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @cName " +
"EXEC sp_executesql @sql " +
"END " +
"END TRY " +
"BEGIN CATCH " +
"PRINT 'Error Detected' " +
"END CATCH";
DAO.AddParam("@tableName", AllListForeignKeys["TABLE_NAME"].ToString());
// - CAT.NET false-positive: All single quotes are escaped/removed.
DAO.ExecuteNonQuery(DAO.GetClientReportingConnection);
//DELETE EXISTING FK's from list snapshot table
DAO.Command =
"BEGIN TRY " +
"WHILE EXISTS(select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where CONSTRAINT_NAME like 'FK_EPMLIVE_%' AND TABLE_NAME=@tableName) " +
"BEGIN " +
"DECLARE @cName nvarchar(Max) " +
"SELECT @cName = (SELECT TOP 1 [CONSTRAINT_NAME] FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE CONSTRAINT_NAME LIKE 'FK_EPMLIVE_%' AND TABLE_NAME=@tableName) " +
"DECLARE @sql nvarchar(Max) " +
"SELECT @sql = 'ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @cName " +
"EXEC sp_executesql @sql " +
"END " +
"END TRY " +
"BEGIN CATCH " +
"PRINT 'Error Detected' " +
"END CATCH";
DAO.AddParam("@tableName", AllListForeignKeys["SNAPSHOT_TABLE_NAME"].ToString());
// - CAT.NET false-positive: All single quotes are escaped/removed.
DAO.ExecuteNonQuery(DAO.GetClientReportingConnection);
//ADD FK's to list table
DAO.Command = AllListForeignKeys["FK_TABLE_SCRIPT"].ToString();
// - CAT.NET false-positive: All single quotes are escaped/removed.
DAO.ExecuteNonQuery(DAO.GetClientReportingConnection);
//ADD FK's to list snapshot table
DAO.Command = AllListForeignKeys["FK_SNAPSHOT_TABLE_SCRIPT"].ToString();
// - CAT.NET false-positive: All single quotes are escaped/removed.
DAO.ExecuteNonQuery(DAO.GetClientReportingConnection);
}
return(true);
}
protected void LoadSnapshot()
{
DataTable dt;
//_DAO.Command = "SELECT * FROM RPTPeriods WHERE PeriodID = '" + Request.QueryString["uid"] + "'"; - CAT.NET
_DAO.Command = "SELECT * FROM RPTPeriods WHERE PeriodID = @uid";
_DAO.AddParam("@uid", Request.QueryString["uid"].Split(',')[0]);
dt = _DAO.GetTable(_DAO.GetClientReportingConnection);
snapShotDate.SelectedDate = DateTime.Parse(dt.Rows[0]["PeriodDate"].ToString());
title.Text = dt.Rows[0]["Title"].ToString();
activate.Checked = (bool)dt.Rows[0]["Enabled"];
}
/// <summary>
/// Refreshes all.
/// </summary>
/// <returns></returns>
public string RefreshAll()
{
try
{
using (var epmData = new EPMData(Web.Site.ID))
{
epmData.DeleteWork(Guid.Empty, -1);
epmData.Command =
"select timerjobuid from timerjobs where siteguid=@siteguid and listguid is null and jobtype=5";
epmData.AddParam("@siteguid", Web.Site.ID);
object result = epmData.ExecuteScalar(epmData.GetEPMLiveConnection);
Guid timerJobId = Guid.Empty;
if (result != null)
{
timerJobId = (Guid)result;
}
else
{
timerJobId = Guid.NewGuid();
epmData.Command =
"INSERT INTO TIMERJOBS (siteguid, jobtype, jobname, scheduletype, webguid, timerjobuid) VALUES (@siteguid, 5, 'Reporting Refresh All', 2, @webguid, @timerjobuid)";
epmData.AddParam("@siteguid", Web.Site.ID);
epmData.AddParam("@webguid", Web.ID);
epmData.AddParam("@timerjobuid", timerJobId);
epmData.ExecuteNonQuery(epmData.GetEPMLiveConnection);
}
if (timerJobId != Guid.Empty)
{
CoreFunctions.enqueue(timerJobId, 0, Web.Site);
}
}
return("<RefreshAll><Data/></RefreshAll>");
}
catch (APIException)
{
throw;
}
catch (Exception exception)
{
throw new APIException((int)Errors.RefreshAll, exception.GetBaseException().Message);
}
}
private void CleanupListAfterFieldUpdate(EPMData DAO, string sList)
{
using (SPSite site = new SPSite(DAO.SiteId))
{
using (SPWeb web = site.RootWeb)
{
Guid listID = DAO.GetListId(sList, web.ID);
//DELETE WORK
DAO.DeleteWork(listID, -1);
//END
DAO.Command =
"select timerjobuid from timerjobs where siteguid=@siteguid and listguid = @listguid and jobtype=6";
DAO.AddParam("@siteguid", site.ID.ToString());
DAO.AddParam("@listguid", listID.ToString());
object oResult = DAO.ExecuteScalar(DAO.GetEPMLiveConnection);
Guid timerjobuid = Guid.Empty;
if (oResult != null)
{
timerjobuid = (Guid)oResult;
}
else
{
timerjobuid = Guid.NewGuid();
DAO.Command =
"INSERT INTO TIMERJOBS (timerjobuid, siteguid, jobtype, jobname, scheduletype, webguid, listguid, jobdata) VALUES (@timerjobuid, @siteguid, 6, 'List Data Cleanup', 0, @webguid, @listguid, @jobdata)";
DAO.AddParam("@siteguid", site.ID.ToString());
DAO.AddParam("@webguid", web.ID.ToString());
DAO.AddParam("@listguid", listID.ToString());
DAO.AddParam("@jobdata", sList);
DAO.AddParam("@timerjobuid", timerjobuid);
DAO.ExecuteNonQuery(DAO.GetEPMLiveConnection);
}
if (timerjobuid != Guid.Empty)
{
EPMLiveCore.CoreFunctions.enqueue(timerjobuid, 0, site);
}
}
}
//--End
}
private void DeleteCheck()
{
var reportBiz = new ReportBiz(SPContext.Current.Site.ID);
EPMData _DAO = new EPMData(SPContext.Current.Site.ID);
_DAO.Command = "SELECT TableName FROM RPTList WHERE RPTListID=@RPTListID";
_DAO.AddParam("@RPTListID", _listId);
string sTableName = _DAO.ExecuteScalar(_DAO.GetClientReportingConnection).ToString();
DataTable refTables = reportBiz.GetReferencingTables(_DAO, sTableName);
if (refTables.Rows.Count == 0)
{
output = "true";
//reportBiz.GetListBiz(new Guid(Request["List"])).Delete();
}
else
{
string sLists = GetRefLists(refTables, _DAO);
//SPUtility.Redirect("epmlive/ListMappings.aspx?delete=true&id=" + param + "&name=" + sTableName, SPRedirectFlags.RelativeToLayoutsPage, HttpContext.Current);
output = "false," + sLists;
}
}
private bool DeleteList(SPListEventProperties properties)
{
bool isSuccessful = true;
var reportBiz = new ReportBiz(properties.SiteId);
Guid listId = properties.ListId;
var DAO = new EPMData(properties.SiteId);
DAO.Command = "SELECT TableName FROM RPTList WHERE RPTListID=@RPTListID";
DAO.AddParam("@RPTListID", listId);
string sTableName = string.Empty;
try
{
sTableName = DAO.ExecuteScalar(DAO.GetClientReportingConnection).ToString();
DataTable refTables = reportBiz.GetReferencingTables(DAO, sTableName);
if (refTables.Rows.Count == 0)
{
reportBiz.GetListBiz(listId).Delete();
}
else
{
isSuccessful = false;
DAO.LogStatus(listId.ToString(), sTableName,
"Database table delete attempt: Unable to delete " + sTableName + ".",
sTableName + " is referenced by other tables.", 2, 5, Guid.NewGuid().ToString());
//Logged in the RefreshAll event log.
}
}
catch (Exception ex)
{
DAO.LogStatus(listId.ToString(), sTableName,
"Database table delete attempt: Unable to delete " + sTableName + ". " + ex.Message, ex.StackTrace,
2, 5, Guid.NewGuid().ToString()); //Logged in the RefreshAll event log.
}
reportBiz = null;
DAO.Dispose();
return(isSuccessful);
}
public DataTable GetSpecificForeignKey(EPMData DAO, string listId, string tableName, string columnName)
{
// Checks if the specified column has foreign key associated with it
bool hasForeignKey = HasForeignKey(DAO, tableName, columnName);
//Initialize return value DataTable (will hold all FK update scripts for both live and snapshot tables)
var ForeignKeysByTable = new DataTable();
ForeignKeysByTable.Columns.Add("TABLE_NAME");
ForeignKeysByTable.Columns.Add("SNAPSHOT_TABLE_NAME");
ForeignKeysByTable.Columns.Add("FK_TABLE_SCRIPT");
ForeignKeysByTable.Columns.Add("FK_SNAPSHOT_TABLE_SCRIPT");
// Get script if foreign key found
if (hasForeignKey)
{
// Get specific lookup field from list
string sql =
"SELECT dbo.RPTColumn.RPTListId, dbo.RPTList.ListName, dbo.RPTList.TableName, dbo.RPTList.TableNameSnapshot, dbo.RPTColumn.InternalName , dbo.RPTColumn.ColumnName" +
" FROM dbo.RPTList INNER JOIN dbo.RPTColumn ON dbo.RPTList.RPTListId = dbo.RPTColumn.RPTListId " +
"WHERE dbo.RPTList.SiteId=@siteId AND dbo.RPTList.RPTListId=@listId AND (dbo.RPTColumn.SharePointType = N'lookup') " +
"AND (ColumnType = 'Int' OR ColumnType = 'NTEXT') AND dbo.RPTColumn.ColumnName = @columnName";
DAO.Command = sql;
DAO.AddParam("@siteId", DAO.SiteId);
DAO.AddParam("@listId", listId);
DAO.AddParam("@columnName", columnName);
DataTable ListLookupFields = DAO.GetTable(DAO.GetClientReportingConnection);
//Initialize and open topsite/web
SPWeb web = null;
SPSite site = null;
SPList childList = null;
SPSecurity.RunWithElevatedPrivileges(delegate
{
site = new SPSite(DAO.SiteId);
web = site.OpenWeb(); //IGNORE SPDispose 110, web is being disposed outside scope
childList = web.Lists[new Guid(listId)];
});
if (childList == null)
{
foreach (SPWeb w in site.AllWebs)
{
try
{
childList = w.Lists[new Guid(listId)];
}
catch { }
w.Close();
if (childList != null)
{
break;
}
}
}
try
{
if (ListLookupFields != null && ListLookupFields.Rows.Count > 0)
{
foreach (DataRow lookupField in ListLookupFields.Rows)
{
var field =
(SPFieldLookup)
childList.Fields.GetFieldByInternalName(lookupField["InternalName"].ToString());
if (field.TypeAsString.ToLower() != "filteredlookup" && field.LookupList != null &&
field.LookupList != string.Empty)
{
#region Get parent list info of lookup field
string listuid = field.LookupList;
SPList parentList = null;
try
{
parentList = web.Lists[new Guid(listuid)];
}
catch { }
DataRow parentListInfo = null;
if (parentList != null)
{
try
{
//Get All mapped lists
DAO.Command = "SELECT * FROM RPTList WHERE SiteId=@siteId AND RPTListId=@listId";
DAO.AddParam("@siteId", DAO.SiteId);
DAO.AddParam("@listId", parentList.ID);
DataTable parentListTable = DAO.GetTable(DAO.GetClientReportingConnection);
if (parentListTable != null && parentListTable.Rows.Count > 0)
{
parentListInfo = parentListTable.Rows[0];
}
}
catch { }
}
#endregion
#region Add foreign key script to table
if (parentListInfo != null)
{
string parentTableName = parentListInfo["TableName"].ToString();
//string childTableName = childListInfo[0]["TableName"].ToString();
string childTableName = lookupField["TableName"].ToString();
//string childTableSnapShotName = childListInfo[0]["TableNameSnapshot"].ToString();
string childTableSnapShotName = lookupField["TableNameSnapshot"].ToString();
string fieldName = lookupField["InternalName"].ToString();
string tableFKName = TrimLongFKID("FK_EPMLIVE_" + lookupField["InternalName"] + "_" +
childTableName.ToUpper() + "_" + parentTableName.ToUpper());
string snapshotFKName = TrimLongFKID("FK_EPMLIVE_" + lookupField["InternalName"] + "_" +
childTableSnapShotName.ToUpper() + "_" + parentTableName.ToUpper());
//Init. LST_XXXX_Table FK SCRIPT
string LST_TABLE_FK_SCRIPT =
"IF (EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = \'" +
childTableName + "\') AND OBJECT_ID('[" + tableFKName + "]','F') IS NULL) " +
"BEGIN " +
"ALTER TABLE [dbo].[" + childTableName + "] WITH NOCHECK " +
"ADD CONSTRAINT [" + tableFKName + "] FOREIGN KEY([webid], [" + lookupField["ColumnName"] + "]) REFERENCES [dbo].[" +
parentTableName + "] ([WebId], [ItemId]) NOT FOR REPLICATION " +
"ALTER TABLE [dbo].[" + childTableName + "] NOCHECK CONSTRAINT [" + tableFKName + "] " +
"END ";
//Init. LST_XXXX_Snapshot_Table FK SCRIPT
string LST_SNAPSHOT_TABLE_FK_SCRIPT =
"IF (EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = \'" +
childTableSnapShotName + "\') AND OBJECT_ID('[" + snapshotFKName + "]','F') IS NULL) " +
"BEGIN " +
"ALTER TABLE [dbo].[" + childTableSnapShotName + "] WITH NOCHECK " +
"ADD CONSTRAINT [" + snapshotFKName + "] FOREIGN KEY([webid], [" + lookupField["ColumnName"] + "]) REFERENCES [dbo].[" +
parentTableName + "] ([WebId], [ItemId]) NOT FOR REPLICATION " +
"ALTER TABLE [dbo].[" + childTableSnapShotName.ToUpper() +
"] NOCHECK CONSTRAINT [" + snapshotFKName + "] " +
"END ";
//Add FK SCRIPTS
DataRow row = ForeignKeysByTable.NewRow();
row["TABLE_NAME"] = childTableName.Replace("'", "''");
// - CAT.NET false-positive: All single quotes are escaped/removed.
row["FK_TABLE_SCRIPT"] = LST_TABLE_FK_SCRIPT;
//.Replace("'", "''"); // - CAT.NET false-positive: All single quotes are escaped/removed.
row["SNAPSHOT_TABLE_NAME"] = childTableSnapShotName.Replace("'", "''");
// - CAT.NET false-positive: All single quotes are escaped/removed.
row["FK_SNAPSHOT_TABLE_SCRIPT"] = LST_SNAPSHOT_TABLE_FK_SCRIPT;
//.Replace("'", "''"); // - CAT.NET false-positive: All single quotes are escaped/removed.
ForeignKeysByTable.Rows.Add(row);
}
#endregion
}
}
}
}
catch { }
finally
{
if (web != null)
{
web.Dispose();
}
if (site != null)
{
site.Dispose();
}
}
}
return(ForeignKeysByTable);
}
//Modules created by xjh -- START
public DataTable GetAllForeignKeys(EPMData DAO)
{
//Initialize return value DataTable (will hold all FK update scripts for both live and snapshot tables)
var AllForeignKeysByTable = new DataTable();
AllForeignKeysByTable.Columns.Add("TABLE_NAME");
AllForeignKeysByTable.Columns.Add("SNAPSHOT_TABLE_NAME");
AllForeignKeysByTable.Columns.Add("FK_TABLE_SCRIPT");
AllForeignKeysByTable.Columns.Add("FK_SNAPSHOT_TABLE_SCRIPT");
//Get All mapped lists
//DAO.Command = "SELECT * FROM RPTList WHERE SiteId='" + _siteId.ToString() + "'"; - CAT.NET
DAO.Command = "SELECT * FROM RPTList WHERE SiteId=@siteId";
DAO.AddParam("@siteId", _siteId);
DataTable AllMappedLists = DAO.GetTable(DAO.GetClientReportingConnection);
//Get All lookup fields for ALL mapped lists
string sql =
"SELECT dbo.RPTColumn.RPTListId, dbo.RPTList.ListName, dbo.RPTList.TableName, dbo.RPTList.TableNameSnapshot, dbo.RPTColumn.InternalName , dbo.RPTColumn.ColumnName" +
" FROM dbo.RPTList INNER JOIN dbo.RPTColumn ON dbo.RPTList.RPTListId = dbo.RPTColumn.RPTListId " +
"WHERE dbo.RPTList.SiteId=@siteId AND (dbo.RPTColumn.SharePointType = N'lookup') AND ColumnType = 'Int'";
DAO.Command = sql;
DAO.AddParam("@siteId", _siteId);
DataTable AllListsLookupFields = DAO.GetTable(DAO.GetClientReportingConnection);
//Initialize and open topsite/web
SPWeb web = null;
SPSite site = null;
SPSecurity.RunWithElevatedPrivileges(delegate
{
site = new SPSite(_siteId);
web = site.OpenWeb(); //IGNORE SPDispose 110, web is being disposed outside scope
});
try
{
//Loop thru all mapped lists
foreach (DataRow list in AllMappedLists.Rows)
{
//Init. Lookup fields for list
DataRow[] lookupFields = AllListsLookupFields.Select("RPTListId='" + list["RPTListId"] + "'");
//Check for lookup fields for this list
if (lookupFields != null && lookupFields.Length > 0)
{
//Init. list
SPList childList = null;
try
{
childList = web.Lists[list["ListName"].ToString()];
}
catch { }
if (childList == null)
{
foreach (SPWeb w in site.AllWebs)
{
try
{
childList = w.Lists[list["ListName"].ToString()];
}
catch { }
w.Close();
if (childList != null)
{
break;
}
}
}
//Loop thru all list lookup fields
foreach (DataRow lookupField in lookupFields)
{
var field =
(SPFieldLookup)
childList.Fields.GetFieldByInternalName(lookupField["InternalName"].ToString());
if (field.TypeAsString.ToLower() != "filteredlookup" && field.LookupList != null &&
field.LookupList != string.Empty)
{
string listuid = field.LookupList;
SPList parentList = null;
try
{
parentList = web.Lists[new Guid(listuid)];
}
catch { }
DataRow[] childListInfo =
AllMappedLists.Select("ListName='" + childList.Title.Replace("'", "") + "'");
// - CAT.NET false-positive: All single quotes are escaped/removed.
DataRow[] parentListInfo = null;
if (parentList != null)
{
try
{
parentListInfo =
AllMappedLists.Select("ListName='" + parentList.Title.Replace("'", "") + "'");
// - CAT.NET false-positive: All single quotes are escaped/removed.
}
catch { }
}
if (parentListInfo != null && parentListInfo.Length > 0)
{
//CAT.NET (issue) - Not being updated. Internal call, table name check and validation is done prior to reaching this point.
string parentTableName = parentListInfo[0]["TableName"].ToString();
string childTableName = childListInfo[0]["TableName"].ToString();
string childTableSnapShotName = childListInfo[0]["TableNameSnapshot"].ToString();
string fieldName = lookupField["InternalName"].ToString();
//Init. LST_XXXX_Table FK SCRIPT
string LST_TABLE_FK_SCRIPT =
"IF (EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = \'" +
childTableName + "\')) " +
"BEGIN " +
"ALTER TABLE [dbo].[" + childTableName + "] WITH NOCHECK " +
"ADD CONSTRAINT [FK_EPMLIVE_" + lookupField["InternalName"] + "_" +
childTableName.ToUpper() + "_" + parentTableName.ToUpper() +
"] FOREIGN KEY([webid], [" + lookupField["ColumnName"] + "]) REFERENCES [dbo].[" +
parentTableName + "] ([WebId], [ItemId]) NOT FOR REPLICATION " +
"ALTER TABLE [dbo].[" + childTableName + "] NOCHECK CONSTRAINT [FK_EPMLIVE_" +
lookupField["InternalName"] + "_" + childTableName.ToUpper() + "_" +
parentTableName.ToUpper() + "] " +
"END ";
//Init. LST_XXXX_Snapshot_Table FK SCRIPT
string LST_SNAPSHOT_TABLE_FK_SCRIPT =
"IF (EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = \'" +
childTableSnapShotName + "\')) " +
"BEGIN " +
"ALTER TABLE [dbo].[" + childTableSnapShotName + "] WITH NOCHECK " +
"ADD CONSTRAINT [FK_EPMLIVE_" + lookupField["InternalName"] + "_" +
childTableSnapShotName.ToUpper() + "_" + parentTableName.ToUpper() +
"] FOREIGN KEY([webid], [" + lookupField["ColumnName"] + "]) REFERENCES [dbo].[" +
parentTableName + "] ([WebId], [ItemId]) NOT FOR REPLICATION " +
"ALTER TABLE [dbo].[" + childTableSnapShotName.ToUpper() +
"] NOCHECK CONSTRAINT [FK_EPMLIVE_" + lookupField["InternalName"] + "_" +
childTableSnapShotName.ToUpper() + "_" + parentTableName.ToUpper() + "] " +
"END ";
//Add FK SCRIPTS
DataRow row = AllForeignKeysByTable.NewRow();
row["TABLE_NAME"] = childTableName.Replace("'", "''");
// - CAT.NET false-positive: All single quotes are escaped/removed.
row["FK_TABLE_SCRIPT"] = LST_TABLE_FK_SCRIPT;
//.Replace("'", "''"); // - CAT.NET false-positive: All single quotes are escaped/removed.
row["SNAPSHOT_TABLE_NAME"] = childTableSnapShotName.Replace("'", "''");
// - CAT.NET false-positive: All single quotes are escaped/removed.
row["FK_SNAPSHOT_TABLE_SCRIPT"] = LST_SNAPSHOT_TABLE_FK_SCRIPT;
//.Replace("'", "''"); // - CAT.NET false-positive: All single quotes are escaped/removed.
AllForeignKeysByTable.Rows.Add(row);
}
}
}
}
}
}
catch
{
if (web != null)
{
try
{
web.Dispose();
}
catch { }
}
if (site != null)
{
try
{
site.Dispose();
}
catch { }
}
}
if (web != null)
{
try
{
web.Dispose();
}
catch { }
}
if (site != null)
{
try
{
site.Dispose();
}
catch { }
}
return(AllForeignKeysByTable);
}
public bool AddTimerJob(SPWeb web, int iTime, int iScheduleType, string days, bool runNow)
{
SqlConnection con = null;
bool passed = true;
try
{
SPSecurity.RunWithElevatedPrivileges(delegate()
{
Guid timerjobuid = Guid.Empty;
if (_DAO == null)
{
_DAO = new EPMData(web.Site.ID);
}
_DAO.Command = "select timerjobuid from timerjobs where siteguid=@siteguid and jobtype=8";
_DAO.AddParam("@siteguid", web.Site.ID.ToString());
object oResult = _DAO.ExecuteScalar(_DAO.GetEPMLiveConnection);
if (oResult != null)
{
timerjobuid = (Guid)oResult;
_DAO.Command = "UPDATE TIMERJOBS SET runtime = @runtime, scheduleType = @scheduletype, days = @days WHERE jobtype=8";
_DAO.AddParam("@runtime", iTime);
_DAO.AddParam("@scheduletype", iScheduleType);
_DAO.AddParam("@days", days);
_DAO.ExecuteNonQuery(_DAO.GetEPMLiveConnection);
}
else
{
timerjobuid = Guid.NewGuid();
_DAO.Command = "INSERT INTO TIMERJOBS (timerjobuid,jobname,siteguid,webguid,listguid,jobtype,enabled,runtime,scheduletype,days,jobdata,lastqueuecheck,parentjobuid) VALUES (@timerjobuid,@jobname,@siteguid,@webguid,NULL,@jobtype,@enabled,@runtime,@scheduletype,@days,@jobdata,@lastqueuecheck,@parentjobuid)";
_DAO.AddParam("@timerjobuid", timerjobuid);
_DAO.AddParam("@jobname", "EPMLiveADSync");
_DAO.AddParam("@siteguid", web.Site.ID.ToString());
_DAO.AddParam("@webguid", web.Site.RootWeb.ID.ToString());
_DAO.AddParam("@jobtype", 8);
_DAO.AddParam("@enabled", true);
_DAO.AddParam("@runtime", iTime);
_DAO.AddParam("@scheduletype", iScheduleType);
_DAO.AddParam("@days", days);
_DAO.AddParam("@jobdata", web.Site.RootWeb.Url);
_DAO.AddParam("@lastqueuecheck", DBNull.Value);
_DAO.AddParam("@parentjobuid", DBNull.Value);
_DAO.ExecuteNonQuery(_DAO.GetEPMLiveConnection);
}
if (runNow && timerjobuid != Guid.Empty)
{
EPMLiveCore.CoreFunctions.enqueue(timerjobuid, 0);
}
});
}
catch (Exception ex)
{
passed = false;
_ExecutionLogs.Add(" ERROR -- Location: AddTimerJob() -- Message: " + ex.Message);
_hasErrors = true;
}
finally
{
_DAO.Dispose();
}
return(passed);
}
//protected Panel pnl_results;
protected void Page_Load(object sender, EventArgs e)
{
string sListName = string.Empty;
string sEntries = string.Empty;
Literal lit;
var site = new SPSite(SPContext.Current.Site.ID);
_DAO = new EPMData(site.ID);
if (Request.QueryString["uid"] == null)
{
//_DAO.Command = string.Format("SELECT ListName, LongMessage FROM RPTLog WHERE RPTListID=@rptListID AND (Timestamp BETWEEN DATEADD(s, - 1, @timestamp) AND DATEADD(s, 2,@timestamp)) AND Type=@type AND CAST(ShortMessage as NVarchar(MAX))='{0}'", Request.QueryString["sm"]); - CAT.NET
_DAO.Command =
"SELECT ListName, LongMessage FROM RPTLog WHERE RPTListID=@rptListID AND (Timestamp BETWEEN DATEADD(s, - 1, @timestamp) AND DATEADD(s, 2,@timestamp)) AND Type=@type AND CAST(ShortMessage as NVarchar(MAX))=@sm";
// - CAT.NET false-positive: All single quotes are escaped/removed.
_DAO.AddParam("@rptListID", Request.QueryString["id"]);
_DAO.AddParam("@timestamp",
DateTime.Parse(Request.QueryString["ts"]).ToString(CultureInfo.GetCultureInfo("en-US")));
_DAO.AddParam("@type", Request.QueryString["type"]);
_DAO.AddParam("@sm", Request.QueryString["sm"]);
DataTable dt = _DAO.GetTable(_DAO.GetClientReportingConnection);
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
if (sListName != row["ListName"].ToString())
{
sListName = row["ListName"].ToString();
lit = new Literal();
lit.Text = "<b>" + sListName + "</b><br/><br/>";
pnl_results.Controls.Add(lit);
}
lit = new Literal();
lit.Text = row["LongMessage"].ToString();
pnl_results.Controls.Add(lit);
}
}
else
{
lit = new Literal();
lit.Text = "<b> No errors found.</b><br/><br/>";
pnl_results.Controls.Add(lit);
}
}
else
{
//_DAO.Command = string.Format("SELECT ListName, LongMessage, Timestamp FROM RPTLog WHERE timerjobguid='{0}'", Request.QueryString["uid"]);
_DAO.Command = "SELECT ListName, LongMessage, Timestamp FROM RPTLog WHERE timerjobguid=@uid";
_DAO.AddParam("@uid", Request.QueryString["uid"]);
DataTable dt = _DAO.GetTable(_DAO.GetClientReportingConnection);
var lists = new ArrayList();
//Looping thru all rows and adding distinct list names
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
if (!lists.Contains(row["ListName"].ToString()))
{
lists.Add(row["ListName"].ToString());
}
}
}
else
{
lit = new Literal();
lit.Text = "<b> No errors found.</b><br/><br/>";
pnl_results.Controls.Add(lit);
return;
}
//Looping thru all lists
foreach (string list in lists)
{
sListName = list;
sEntries = string.Empty;
//Looping thru all list log entries
DataRow[] lstEntries = dt.Select("ListName='" + list + "'");
foreach (DataRow lstEntry in lstEntries)
{
sEntries = sEntries + lstEntry["LongMessage"] + " Run on " + lstEntry["Timestamp"] + " <br/>";
}
lit = new Literal();
lit.Text = "<b>" + sListName + "</b><br/>";
pnl_results.Controls.Add(lit);
lit = new Literal();
lit.Text = " " + sEntries;
pnl_results.Controls.Add(lit);
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
_DAO = new EPMData(SPContext.Current.Site.ID);
string action = Request["Action"];
Guid timerjobguid = Guid.NewGuid();
//DataTable dtResults;
if (action != null && action == "SnapshotAll")
{
try
{
//Local Testing -- Start
//_DAO.SnapshotLists(timerjobguid, _DAO.GetListNames());
//dtResults = _DAO.GetSnapshotResults(timerjobguid);
// -- END
//PROD -- Start
string sListIDs = _DAO.GetAllListIDs();
using (SPSite site = SPContext.Current.Site)
{
using (SPWeb web = SPContext.Current.Web)
{
_DAO.Command =
"INSERT INTO TIMERJOBS (timerjobuid, siteguid, jobtype, jobname, scheduletype, webguid, jobdata) VALUES (@timerjobuid,@siteguid, 7, 'Reporting Snapshot All', 0, @webguid, @jobdata)";
_DAO.AddParam("@timerjobuid", timerjobguid);
_DAO.AddParam("@siteguid", site.ID.ToString());
_DAO.AddParam("@webguid", web.ID.ToString());
_DAO.AddParam("@jobdata", sListIDs);
_DAO.ExecuteNonQuery(_DAO.GetEPMLiveConnection);
}
CoreFunctions.enqueue(timerjobguid, 0);
}
//END
}
catch (Exception ex)
{
_DAO.LogStatus(string.Empty, string.Empty, "Process: SnapshotAll - " + ex.Message, ex.StackTrace, 2,
9, string.Empty);
Response.Write("Error: " + ex.Message);
return;
}
SPUtility.Redirect("epmlive/ListMappings.aspx", SPRedirectFlags.RelativeToLayoutsPage,
HttpContext.Current);
}
else if (action != null && action == "CleanupAll")
{
//Local Testing -- Start
//string sListNames = _DAO.GetListNames();
//DataTable dtWebResults = null;
//timerjobguid = new Guid();
//DataTable dtListResults = new RefreshLists().InitializeResultsDT(sListNames);
//if (sListNames != string.Empty)
//{
// RefreshLists oListRefresh = null;
// foreach (SPWeb web in SPContext.Current.Site.AllWebs)
// {
// oListRefresh = new RefreshLists(web, sListNames);
// oListRefresh.StartRefresh(timerjobguid, out dtWebResults);
// dtWebResults.TableName = web.ServerRelativeUrl;
// oListRefresh.AppendStatus(web.Name, web.ServerRelativeUrl, dtListResults, dtWebResults);
// }
// oListRefresh.SaveResults(dtListResults, timerjobguid);
//}
// -- End
//PROD -- Start
using (SPSite site = SPContext.Current.Site)
{
using (SPWeb web = SPContext.Current.Web)
{
//DELETE WORK
_DAO.DeleteWork(Guid.Empty, -1);
//END
_DAO.Command =
"select timerjobuid from timerjobs where siteguid=@siteguid and listguid is null and jobtype=6";
_DAO.AddParam("@siteguid", site.ID.ToString());
object oResult = _DAO.ExecuteScalar(_DAO.GetEPMLiveConnection);
Guid timerjobuid = Guid.Empty;
if (oResult != null)
{
timerjobuid = (Guid)oResult;
}
else
{
timerjobuid = Guid.NewGuid();
_DAO.Command =
"INSERT INTO TIMERJOBS (siteguid, jobtype, jobname, scheduletype, webguid, timerjobuid) VALUES (@siteguid, 6, 'List Data Cleanup', 2, @webguid, @timerjobuid)";
_DAO.AddParam("@siteguid", site.ID.ToString());
_DAO.AddParam("@webguid", web.ID.ToString());
_DAO.AddParam("@timerjobuid", timerjobuid);
_DAO.ExecuteNonQuery(_DAO.GetEPMLiveConnection);
}
if (timerjobuid != Guid.Empty)
{
CoreFunctions.enqueue(timerjobuid, 0);
}
}
}
// -- END
SPUtility.Redirect("epmlive/ListMappings.aspx", SPRedirectFlags.RelativeToLayoutsPage,
HttpContext.Current);
}
}
public void RaisePostBackEvent(string sAction)
{
string param = sAction.Remove(sAction.LastIndexOf("_"));
sAction = sAction.Substring(sAction.LastIndexOf("_") + 1);
switch (sAction)
{
case "cleanup":
CleanupLists(param);
break;
case "snapshot":
SnapshotLists(param);
break;
case "delete":
var reportBiz = new ReportBiz(SPContext.Current.Site.ID, SPContext.Current.Web.ID,
reportingV2Enabled);
var listId = new Guid(param);
_DAO.Command = "SELECT TableName FROM RPTList WHERE RPTListID=@RPTListID";
_DAO.AddParam("@RPTListID", param);
string sTableName = _DAO.ExecuteScalar(_DAO.GetClientReportingConnection).ToString();
DataTable refTables = reportBiz.GetReferencingTables(_DAO, sTableName);
if (refTables.Rows.Count == 0)
{
reportBiz.GetListBiz(new Guid(param)).Delete();
}
else
{
SPUtility.Redirect("epmlive/ListMappings.aspx?delete=true&id=" + param + "&name=" + sTableName,
SPRedirectFlags.RelativeToLayoutsPage, HttpContext.Current);
}
break;
}
SPUtility.Redirect("epmlive/ListMappings.aspx?", SPRedirectFlags.RelativeToLayoutsPage, HttpContext.Current);
}
protected void PopulateLists()
{
DataTable dt;
//_DAO.Command = "SELECT ListName FROM RPTList WHERE SiteId='" + SPContext.Current.Web.Site.ID + "'"; - CAT.NET
_DAO.Command = "SELECT ListName FROM RPTList WHERE SiteId=@siteId";
_DAO.AddParam("@siteId", SPContext.Current.Web.Site.ID);
dt = _DAO.GetTable(_DAO.GetClientReportingConnection);
//using (SPContext.Current.Web)
//{
foreach (DataRow row in dt.Rows)
{
var item = new ListItem();
item.Text = row["ListName"].ToString();
item.Value = row["ListName"].ToString();
ListBoxLists.Items.Add(item);
}
//}
}
