public static bool Run(int updatedBy)
{
var docUsers = DocUtilProcs.GetDocUsers();
var batches = docUsers.Count / BATCH_SIZE;
var now = DateTime.Now.ToString();
for (var b = 0; b < batches + 1; b++)
{
var tsql = new StringBuilder();
var firstRow = b * BATCH_SIZE;
var lastRow = (b + 1) * BATCH_SIZE > docUsers.Count ? docUsers.Count : (b + 1) * BATCH_SIZE;
// only pulling 20 users' build records each time
// to fit Azure multi-tenant limitted connection time situation
var docs = DocUtilProcs.GetDocs(docUsers.Skip(firstRow).Take(lastRow - firstRow))
.GroupBy(d => d.UserId).ToDictionary(d =>
new
{
User = d.Key,
Docs = d.Select(x => new { x.Document, x.SolutionName, x.FileName, x.BuildId, x.DocumentId }).ToList(),
});
foreach (var u in docs.Keys)
{
var orderedUserDocs = docs[u].OrderBy(d => d.FileName).OrderBy(d => d.EventDate).ToArray();
// every user's first build doc is new
tsql.AppendFormat(SQLTemplateUpdateBuildDocuments.Template, 0, 0, 0, string.Empty, now, updatedBy, orderedUserDocs[0].BuildId, orderedUserDocs[0].DocumentId);
for (var idx = 1; idx < orderedUserDocs.Count(); idx++)
{
if (string.Compare(orderedUserDocs[idx - 1].FileName, orderedUserDocs[idx].FileName, true) != 0)
{
// starting a different user build doc
tsql.AppendFormat(SQLTemplateUpdateBuildDocuments.Template, 0, 0, 0, string.Empty, now, updatedBy, orderedUserDocs[idx].BuildId, orderedUserDocs[idx].DocumentId);
}
else
{
// comparing this and the previous version of the build doc
var differ = new Differ();
var inlineBuilder = new SideBySideDiffBuilder(differ);
var result = inlineBuilder.BuildDiffModel(orderedUserDocs[idx - 1].Document, orderedUserDocs[idx].Document);
var deleted = result.OldText.Lines.Count(l => l.Type == ChangeType.Deleted);
var inserted = result.NewText.Lines.Count(l => l.Type == ChangeType.Inserted);
var modified = result.NewText.Lines.Count(l => l.Type == ChangeType.Modified);
var modifiedPositions = result.NewText.Lines.Where(l => l.Position.HasValue && l.Type == ChangeType.Modified).Select(l => l.Position.Value);
tsql.AppendFormat(SQLTemplateUpdateBuildDocuments.Template, inserted, modified, deleted, string.Join(",", modifiedPositions), now, updatedBy, orderedUserDocs[idx].BuildId, orderedUserDocs[idx].DocumentId);
}
}
}
DynamicSQLExecutor.Execute(tsql.ToString());
}
return(true);
}
public static void Upsert(string filePath, string fileExtension, string sqlTemplate, Dictionary <string, string> keyVals, int[] digitalColumns)
{
using (var xlPackage = new ExcelPackage(new FileInfo(filePath)))
{
var worksheet = xlPackage.Workbook.Worksheets[1];
var query = new StringBuilder();
var batches = worksheet.Dimension.End.Row / BATCH_SIZE;
for (var b = 0; b < batches + 1; b++)
{
var firstRow = b == 0 ? 2 : b * BATCH_SIZE;
var lastRow = (b + 1) * BATCH_SIZE > worksheet.Dimension.End.Row ? worksheet.Dimension.End.Row + 1 : (b + 1) * BATCH_SIZE;
for (var i = firstRow; i < lastRow; i++)
{
// compose a row
var sqlRow = sqlTemplate;
for (var j = 1; j < worksheet.Dimension.End.Column + 1; j++)
{
// compose a column
var column = worksheet.Cells[i, j].Value;
if (column == null)
{
sqlRow = sqlRow.Replace(string.Format("C_{0}", j), "Null");
}
else if (digitalColumns.Any(c => c == j))
{
try
{
sqlRow = sqlRow.Replace(string.Format("C_{0}", j), Convert.ToInt32(column).ToString());
}
catch (Exception ex)
{
throw new Exception(string.Format("{0} at row {1} column {2}.", ex.Message.TrimEnd('.'), i, j));
}
}
else
{
sqlRow = sqlRow.Replace(string.Format("C_{0}", j), column.ToString().Replace("'", "''"));
}
}
foreach (var key in keyVals.Keys)
{
sqlRow = sqlRow.Replace(key, keyVals[key]);
}
query.Append(sqlRow.Replace("NEW_LINE", Environment.NewLine));
}
}
DynamicSQLExecutor.Execute(query.ToString());
}
}
public static void Insert(string filePath, string fileExtension, string sqlTemplate, string paramTemplate, string[] paramList, int[] digitalColumns)
{
using (var xlPackage = new ExcelPackage(new FileInfo(filePath)))
{
var worksheet = xlPackage.Workbook.Worksheets[1];
var query = new StringBuilder(sqlTemplate);
var batches = worksheet.Dimension.End.Row / BATCH_SIZE;
for (var b = 0; b < batches + 1; b++)
{
var firstRow = b == 0 ? 2 : b * BATCH_SIZE;
var lastRow = (b + 1) * BATCH_SIZE > worksheet.Dimension.End.Row ? worksheet.Dimension.End.Row + 1 : (b + 1) * BATCH_SIZE;
for (var i = firstRow; i < lastRow; i++)
{
// compose a row
query.Append("(");
for (var j = 1; j < worksheet.Dimension.End.Column + 1; j++)
{
// compose a column
var column = worksheet.Cells[i, j].Value;
if (column == null)
{
query.Append("Null,");
}
else if (digitalColumns.Any(c => c == j))
{
try
{
query.AppendFormat("{0},", Convert.ToInt32(column));
}
catch (Exception ex)
{
throw new Exception(string.Format("{0} at row {1} column {2}.", ex.Message.TrimEnd('.'), i, j));
}
}
else
{
query.AppendFormat("'{0}',", column.ToString().Replace("'", "''"));
}
}
query.AppendFormat(paramTemplate, paramList);
query.Append("),");
}
}
DynamicSQLExecutor.Execute(query.ToString().TrimEnd(','));
}
}
public static bool Run()
{
using (var context = new OsbideProcs())
{
var prevProcessInfo = context.GetActiveSocialEventProcessInfo().ToList();
var pocessedLogId = prevProcessInfo.First().Info;
var maxUnprocessedLogId = prevProcessInfo.Last().Info;
var batches = (maxUnprocessedLogId - pocessedLogId) / BATCH_SIZE;
for (var b = 0; b < batches + 1; b++)
{
var firstRow = pocessedLogId + b * BATCH_SIZE;
var lastRow = pocessedLogId + (b + 1) * BATCH_SIZE < maxUnprocessedLogId ? pocessedLogId + (b + 1) * BATCH_SIZE : maxUnprocessedLogId + 1;
DynamicSQLExecutor.Execute(string.Format(SQLTemplateUpdateActiveSocialEvents.Template, firstRow, lastRow));
}
return(true);
}
}
public static bool Run(int schoolId)
{
using (var context = new OsbideProcs())
{
// storage tables not completely processed
foreach (var table in context.GetPassiveSocialEventProcessLog())
{
var passiveSocialEvents = DomainObjectHelpers.GetPassiveSocialActivities(table.SourceTableName, schoolId).ToList();
var totalCounts = passiveSocialEvents.Count;
var processedCounts = table.ProcessedRecordCounts.HasValue ? table.ProcessedRecordCounts.Value : 0;
var batches = (totalCounts - processedCounts) / BATCH_SIZE;
for (var b = 0; b < batches + 1; b++)
{
var tsql = new StringBuilder();
var firstRow = processedCounts + b * BATCH_SIZE;
var lastRow = processedCounts + (b + 1) * BATCH_SIZE > totalCounts ? totalCounts : processedCounts + (b + 1) * BATCH_SIZE;
for (var idx = firstRow; idx < lastRow; idx++)
{
var sql = GetSQLRowStatement(passiveSocialEvents[idx]);
if (sql.Length > 0)
{
// build batch insert statements
tsql.AppendFormat("{0}{1}", sql, Environment.NewLine);
}
}
// batch execution and log updates
DynamicSQLExecutor.Execute(tsql.ToString());
context.UpdatePassiveSocialEventProcessLog(table.Id, DESTINATIONTABLE, lastRow == totalCounts, lastRow);
}
}
return(true);
}
}
