public static ColumnDefCollection GetDefaultColumnsSnapshot()
{
var columns = new ColumnDefCollection(GetDefaultColumns());
columns.Insert(0,
new ColumnDef("PeriodId", "PeriodId", "PeriodId", SPFieldType.Guid, SqlDbType.UniqueIdentifier));
return(columns);
}
public static void AddColumn(ColumnDefCollection columns, SPField field)
{
if ((from c in columns
where c.InternalName.Equals(field.InternalName, StringComparison.CurrentCultureIgnoreCase)
select c).Any())
{
return;
}
SqlParameter p = GetParameterFromSPField(field);
string sType = field.TypeAsString.ToLower();
switch (field.Type)
{
case SPFieldType.Lookup:
case SPFieldType.User:
if (sType != "lookupmulti" && sType != "usermulti")
{
columns.Add(new ColumnDef(field.InternalName + "ID", field.InternalName, field.Title, field.Type,
SqlDbType.Int));
}
else
{
columns.Add(new ColumnDef(field.InternalName + "ID", field.InternalName, field.Title, field.Type,
p.SqlDbType, p.Size));
}
columns.Add(new ColumnDef(field.InternalName + "Text", field.InternalName, field.Title, field.Type,
SqlDbType.NVarChar, 8001));
//columns.Add(new ColumnDef(field.InternalName + "Text", field.InternalName, field.Title, field.Type, SqlDbType.NText));
break;
case SPFieldType.Invalid:
switch (sType)
{
case "filteredlookup":
columns.Add(new ColumnDef(field.InternalName + "ID", field.InternalName, field.Title,
SPFieldType.Lookup, SqlDbType.Int));
columns.Add(new ColumnDef(field.InternalName + "Text", field.InternalName, field.Title,
SPFieldType.Lookup, SqlDbType.NVarChar, 256));
break;
default:
columns.Add(new ColumnDef(field));
break;
}
break;
default:
columns.Add(new ColumnDef(field));
break;
}
}
/* Static Methods */
public static ColumnDefCollection DiffColumns(List <ColumnDef> existingSet, List <ColumnDef> newSet)
{
var diff = new ColumnDefCollection();
foreach (ColumnDef columnDef in newSet)
{
ColumnDef def = columnDef;
if (existingSet.Find(c => c.InternalName == def.InternalName) == null)
{
diff.Add(columnDef);
}
}
return(diff);
}
public static ColumnDefCollection GetDefaultColumns()
{
var columns = new ColumnDefCollection
{
new ColumnDef("SiteId", "SiteId", "SiteId", SPFieldType.Guid, SqlDbType.UniqueIdentifier),
new ColumnDef("WebId", "WebId", "WebId", SPFieldType.Guid, SqlDbType.UniqueIdentifier),
new ColumnDef("ListId", "ListId", "ListId", SPFieldType.Guid, SqlDbType.UniqueIdentifier),
new ColumnDef("ItemId", "ItemId", "ItemId", SPFieldType.Integer, SqlDbType.Int),
new ColumnDef("WebUrl", "WebUrl", "WebUrl", SPFieldType.Text, SqlDbType.VarChar, 256),
new ColumnDef("Commenters", "Commenters", "Commenters", SPFieldType.Note, SqlDbType.NVarChar, 8001),
new ColumnDef("CommentersRead", "CommentersRead", "CommentersRead", SPFieldType.Note, SqlDbType.NVarChar,
8001),
new ColumnDef("CommentCount", "CommentCount", "CommentCount", SPFieldType.Number, SqlDbType.Int),
new ColumnDef("WorkspaceUrl", "WorkspaceUrl", "WorkspaceUrl", SPFieldType.Text, SqlDbType.NVarChar, 8001)
};
return(columns);
}
public bool RefreshTimesheetBatch(out string message, Guid jobUid, int pageSize = 0)
{
using (var rd = new ReportData(_siteId))
{
int pageNo = 0;
pageSize = pageSize == 0 ? 500000 : pageSize;
int recCount = 0;
message = string.Empty;
bool hasErrors = false;
try
{
rd.LogStatus("",
"TimeSheet",
"Begin refreshing time sheet data for web: " + WebTitle,
"Begin refreshing time sheet data for web: " + WebTitle,
0, 1, jobUid.ToString());
DataSet dsTSData = rd.GetTSAllDataBatchWithSchema(pageNo, pageSize);
if (dsTSData != null && dsTSData.Tables.Count > 0)
{
DataTable tblTSData = dsTSData.Tables[0];
recCount = Convert.ToInt32(dsTSData.Tables[1].Rows[0][0]);
//Delete Timesheetdata start
rd.LogStatus("",
"TimeSheet",
"Begin deleting existing time sheet data for web: " + WebTitle,
"Begin deleting existing time sheet data for web: " + WebTitle,
0, 1, jobUid.ToString());
rd.DeleteExistingTSData();
rd.LogStatus("",
"TimeSheet",
"Finished deleting existing time sheet data for web: " + WebTitle,
"Finished deleting existing time sheet data for web: " + WebTitle,
0, 1, jobUid.ToString());
if (tblTSData.Columns.Contains("SNO"))
{
tblTSData.Columns.Remove("SNO");
}
var columns = new ColumnDefCollection(tblTSData.Columns);
string sTableName = rd.GetSafeTableName("RPTTSData");
rd.LogStatus("",
"TimeSheet",
"Recreating RPTTSData for web: " + WebTitle,
"Recreating RPTTSData for web: " + WebTitle,
0, 1, jobUid.ToString());
if (!rd.CreateTable(sTableName, columns, true, out message))
{
hasErrors = true;
rd.LogStatus("",
"TimeSheet",
"Error occured while recreating RPTTSData for web: " + WebTitle + ".",
message,
0, 1, jobUid.ToString());
}
rd.LogStatus("",
"TimeSheet",
"Finished recreating RPTTSData for web: " + WebTitle,
"Finished recreating RPTTSData for web: " + WebTitle,
0, 1, jobUid.ToString());
DataTable dtTabletoInsert = tblTSData.Copy();
ProcessRRInBatch(ref message, ref jobUid, rd, ref hasErrors, dtTabletoInsert);
if (recCount > pageSize)
{
int pageCount = 0;
if (recCount % pageSize > 0)
{
pageCount = recCount / pageSize + 1;
}
else
{
pageCount = recCount / pageSize;
}
for (int i = 1; i < pageCount; i++)
{
rd.LogStatus("",
"TimeSheet",
$"Processing data for batch no {2} for {WebTitle}",
$"Processing data for batch no {2} for {WebTitle}",
0, 1, jobUid.ToString());
dsTSData = rd.GetTSAllDataBatchWithSchema(i, pageSize);
if (dsTSData != null && dsTSData.Tables.Count > 0)
{
tblTSData = dsTSData.Tables[0];
if (tblTSData.Columns.Contains("SNO"))
{
tblTSData.Columns.Remove("SNO");
}
dtTabletoInsert = tblTSData.Copy();
ProcessRRInBatch(ref message, ref jobUid, rd, ref hasErrors, dtTabletoInsert);
}
rd.LogStatus("",
"TimeSheet",
$"Finished Processing data for batch no {2} for {WebTitle}",
$"Finished Processing data for batch no {2} for {WebTitle}",
0, 1, jobUid.ToString());
}
}
//message = "Successfully refreshed timesheet data.";
rd.LogStatus("",
"TimeSheet",
"Finished refreshing time sheet data for web: " + WebTitle,
"Finished refreshing time sheet data for web: " + WebTitle,
0, 1, jobUid.ToString());
}
else
{
message = "No timesheet data exists.";
hasErrors = true;
return(hasErrors);
}
}
catch (Exception ex)
{
message = string.Format("Refresh not completed due errors. {0} ", ex.ToString());
rd.LogStatus("",
"TimeSheet",
$"Reporting Refresh Failed {WebTitle}",
ex.ToString(),
0, 3, jobUid.ToString());
hasErrors = true;
}
return(hasErrors);
}
}
public bool RefreshTimesheet(out string message, Guid jobUid)
{
using (var rd = new ReportData(_siteId))
{
message = string.Empty;
bool hasErrors = false;
try
{
rd.LogStatus("",
"TimeSheet",
"Begin refreshing time sheet data for web: " + WebTitle,
"Begin refreshing time sheet data for web: " + WebTitle,
0, 1, jobUid.ToString());
DataTable tblTSData = rd.GetTSAllDataWithSchema();
if (tblTSData == null)
{
message = "No timesheet data exists.";
hasErrors = true;
return(hasErrors);
}
//Delete Timesheetdata start
rd.LogStatus("",
"TimeSheet",
"Begin deleting existing time sheet data for web: " + WebTitle,
"Begin deleting existing time sheet data for web: " + WebTitle,
0, 1, jobUid.ToString());
rd.DeleteExistingTSData();
rd.LogStatus("",
"TimeSheet",
"Finished deleting existing time sheet data for web: " + WebTitle,
"Finished deleting existing time sheet data for web: " + WebTitle,
0, 1, jobUid.ToString());
//End
//IF performance becomes an issue, change rpttsduid to int and auto-increment.
//Thus eliminating the need for the population routine below. xjh
tblTSData.Columns.Add("rpttsduid", Type.GetType("System.Guid"));
//Populate rpttsduid column -- Start
foreach (DataRow TSItem in tblTSData.Rows)
{
Guid rptuid = Guid.NewGuid();
TSItem["rpttsduid"] = rptuid;
}
// -- End
var columns = new ColumnDefCollection(tblTSData.Columns);
string sTableName = rd.GetSafeTableName("RPTTSData");
rd.LogStatus("",
"TimeSheet",
"Recreating RPTTSData for web: " + WebTitle,
"Recreating RPTTSData for web: " + WebTitle,
0, 1, jobUid.ToString());
if (!rd.CreateTable(sTableName, columns, true, out message))
{
hasErrors = true;
rd.LogStatus("",
"TimeSheet",
"Error occured while recreating RPTTSData for web: " + WebTitle + ".",
message,
0, 1, jobUid.ToString());
}
rd.LogStatus("",
"TimeSheet",
"Finished recreating RPTTSData for web: " + WebTitle,
"Finished recreating RPTTSData for web: " + WebTitle,
0, 1, jobUid.ToString());
rd.LogStatus("", "TimeSheet",
"Inserting data to RPTTSData for web: " + WebTitle,
"Inserting data to RPTTSData for web: " + WebTitle,
0, 1, jobUid.ToString());
if (!rd.InsertTSAllData(tblTSData, out message))
{
hasErrors = true;
rd.LogStatus("",
"TimeSheet",
"Error occurred while inserting data into RPTTSData for web: " + WebTitle,
message, 0, 3, jobUid.ToString());
}
rd.LogStatus("", "TimeSheet",
"Finished inserting data to RPTTSData for web: " + WebTitle,
"Finished inserting data to RPTTSData for web: " + WebTitle,
0, 1, jobUid.ToString());
//message = "Successfully refreshed timesheet data.";
rd.LogStatus("",
"TimeSheet",
"Finished refreshing time sheet data for web: " + WebTitle,
"Finished refreshing time sheet data for web: " + WebTitle,
0, 1, jobUid.ToString());
}
catch (Exception ex)
{
message = string.Format("Refresh not completed due errors. {0} ", ex.ToString());
hasErrors = true;
}
return(hasErrors);
}
}
public static void AddColumn(ColumnDefCollection columns, DataColumn column)
{
columns.Add(new ColumnDef(column));
}