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 SnapshotLists(string sLists)
{
//PROD -- START
using (SPSite site = SPContext.Current.Site)
{
using (SPWeb web = SPContext.Current.Web)
{
Guid listId = _DAO.GetListId(sLists, web.ID);
_DAO.Command =
"select timerjobuid from timerjobs where siteguid=@siteguid and listguid = @listguid and jobtype=7";
_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, 7, 'Reporting Snapshot', 0, @webguid, @listguid, @jobdata)";
_DAO.AddParam("@siteguid", site.ID.ToString());
_DAO.AddParam("@webguid", web.ID.ToString());
_DAO.AddParam("@listguid", listId.ToString());
_DAO.AddParam("@jobdata", listId.ToString());
_DAO.AddParam("@timerjobuid", timerjobuid);
_DAO.ExecuteNonQuery(_DAO.GetEPMLiveConnection);
}
if (timerjobuid != Guid.Empty)
{
CoreFunctions.enqueue(timerjobuid, 0);
}
}
}
//END
}
protected void save_Click(object sender, EventArgs e)
{
var id = new Guid(Request.QueryString["uid"].Split(',')[0]);
_DAO.Command =
"UPDATE RPTPeriods SET PeriodDate=@periodDate, Title=@title, Enabled=@enabled WHERE periodid=@period";
_DAO.AddParam("@periodDate", snapShotDate.SelectedDate);
_DAO.AddParam("@title", title.Text);
_DAO.AddParam("@period", id);
_DAO.AddParam("@enabled", activate.Checked);
_DAO.ExecuteNonQuery(_DAO.GetClientReportingConnection);
SPUtility.Redirect("/epmlive/AllSnapshots.aspx", SPRedirectFlags.RelativeToLayoutsPage, HttpContext.Current);
}
/// <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 bool UpdatePercentComplete(decimal pctComplete)
{
_DAO.Command = "UPDATE QUEUE SET percentComplete = " + pctComplete.ToString() + " WHERE timerjobuid='" + _tuid + "'";
_DAO.ExecuteNonQuery(_DAO.GetEPMLiveConnection);
return(true);
}
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);
}
}
private void saveSettings(SPWeb currWeb)
{
if (ScheduleValid())
{
string sTime = DropDownListSnapshotTime.SelectedValue;
string sTitle = txtScheduleTitle.Text;
int iTime = 0;
if (sTime != string.Empty)
{
iTime = int.Parse(sTime);
}
int iScheduleType = int.Parse(DropDownListScheduleType.SelectedValue);
if (Request.QueryString["uid"] == null) //ADD NEW JOB
{
Guid timerjobuid = Guid.NewGuid();
_DAO.Command =
"INSERT INTO TIMERJOBS ([timerjobuid],[jobname],[siteguid],[webguid],[listguid],[jobtype],[enabled],[runtime],[scheduletype],[days],[jobdata],[lastqueuecheck],[parentjobuid]) " +
" VALUES(@timejobuid,@jobname,@siteguid,@webguid,@listguid,@jobtype,@enabled,@runtime,@scheduletype,@days,@jobdata,@lastqueuecheck,@parentjobuid)";
_DAO.AddParam("@timejobuid", timerjobuid);
_DAO.AddParam("@jobname", sTitle);
_DAO.AddParam("@siteguid", currWeb.Site.ID);
_DAO.AddParam("@webguid", currWeb.ID);
_DAO.AddParam("@listguid", DBNull.Value);
_DAO.AddParam("@jobtype", 7);
_DAO.AddParam("@enabled", true);
_DAO.AddParam("@runtime", iTime);
_DAO.AddParam("@scheduletype", iScheduleType);
_DAO.AddParam("@days", GetOptions());
_DAO.AddParam("@jobdata", GetLists());
_DAO.AddParam("@lastqueuecheck", DBNull.Value);
_DAO.AddParam("@parentjobuid", DBNull.Value);
_DAO.ExecuteNonQuery(_DAO.GetEPMLiveConnection);
}
else //Udate Existing Job
{
_DAO.Command =
"UPDATE TIMERJOBS SET jobname=@jobname,enabled=@enabled,scheduletype=@scheduletype,runtime=@runtime,days=@days,jobdata=@jobdata WHERE timerjobuid=@timerjobuid";
_DAO.AddParam("@jobname", sTitle);
_DAO.AddParam("@timerjobuid", Request.QueryString["uid"]);
_DAO.AddParam("@enabled", true);
_DAO.AddParam("@scheduletype", iScheduleType);
_DAO.AddParam("@runtime", iTime);
_DAO.AddParam("@days", GetOptions());
_DAO.AddParam("@jobdata", GetLists());
_DAO.ExecuteNonQuery(_DAO.GetEPMLiveConnection);
}
if (_DAO.SqlErrorOccurred)
{
_DAO.LogStatus(string.Empty, string.Empty, "Error Saving Schedule", _DAO.SqlError, 2, 1,
string.Empty);
}
lblErrorSite.Visible = false;
}
else
{
lblErrorSite.Visible = true;
lblErrorSite.Text = "Invalid value. Please check your settings and try again.";
}
}