public static async Task StartUpMode()
{
//Load data into the new collection.
IEnumerable <Recommendation> libraryBooks = new List <Recommendation>()
{
new Recommendation()
{
author = "Johnathan Baier",
description = "Learn Kubernetes the Right Way.",
id = "1", isbn = "01234",
title = "Get Started with Kubernetes",
imageURL = "https://mtchouimages.blob.core.windows.net/books/Kubernetes.jpg"
},
new Recommendation()
{
author = "Rajdeep Das",
description = "Docker Networking Deep Dive",
id = "2", isbn = "95201",
title = "Learn Docker Networking",
imageURL = "https://mtchouimages.blob.core.windows.net/books/DockerNetworking.jpg"
},
new Recommendation()
{
author = "Rajesh RV",
description = "Build scalable microservices with Spring and Docker",
id = "3", isbn = "090923",
title = "Spring Microservices",
imageURL = "https://mtchouimages.blob.core.windows.net/books/SpringMicroServices.jpg"
},
new Recommendation()
{
author = "Aleksandar Prokopec",
description = "Learn the art of building concurrent applications!",
id = "4", isbn = "342421",
title = "Learn Concurrent Programming in Scala",
imageURL = "https://mtchouimages.blob.core.windows.net/books/Scala.jpg"
},
new Recommendation()
{
author = "Vitorrio Bertocci",
description = "Azure Active Directory capabilities from the master!",
id = "5", isbn = "472891",
title = "Modern Authentication with AzureAD",
imageURL = "https://mtchouimages.blob.core.windows.net/books/AzureAD.jpg"
},
new Recommendation()
{
author = "Leoanrd G. Lobel",
description = "Step by step guide for developers!",
id = "6", isbn = "291920",
title = "Microsoft Azure SQL",
imageURL = "https://mtchouimages.blob.core.windows.net/books/AzureSQL.jpg"
},
new Recommendation()
{
author = "Rajdeep Das",
description = "Exam Ref 70-487",
id = "7", isbn = "9788652",
title = "Developing Azure and Web Services",
imageURL = "https://mtchouimages.blob.core.windows.net/books/AzureCert.jpg"
},
new Recommendation()
{
author = "Haishi Bai",
description = "Service fabric for developers!",
id = "8", isbn = "667263",
title = "Programming Microsoft Azure Service Fabric",
imageURL = "https://mtchouimages.blob.core.windows.net/books/ServiceFabric.jpg"
},
} as IEnumerable <Recommendation>;
foreach (var doc in libraryBooks)
{
try
{
await client.UpsertDocumentAsync(
UriFactory.CreateDocumentCollectionUri(DatabaseId, CollectionId),
doc);
}
catch (Exception ex)
{
}
}
}
public static async Task <Document> CreateItemAsync(T item)
{
return(await client.CreateDocumentAsync(UriFactory.CreateDocumentCollectionUri(DatabaseId, CollectionId), item));
}
public async Task CreatePostAsync(Post post, IEnumerable <string> tags)
{
await this.tagService.CreateTagsAsync(tags);
await this.documentClient.CreateDocumentAsync(UriFactory.CreateDocumentCollectionUri("DBNAME", "COLLECTIONNAME"), post);
}
public void TestSetup()
{
databaseName = ConfigurationManager.AppSettings["DatabaseAccountId"];
collectionName = Guid.NewGuid().ToString();
partitionedCollectionName = Guid.NewGuid().ToString();
databaseUri = UriFactory.CreateDatabaseUri(databaseName);
collectionUri = UriFactory.CreateDocumentCollectionUri(databaseName, collectionName);
partitionedCollectionUri = UriFactory.CreateDocumentCollectionUri(databaseName, partitionedCollectionName);
Database database = documentClient.CreateDatabaseIfNotExistsAsync(new Database()
{
Id = databaseName
}).Result.Resource;
DocumentCollection newCollection = new DocumentCollection()
{
Id = collectionName, PartitionKey = defaultPartitionKeyDefinition
};
try
{
documentClient.CreateDocumentCollectionAsync(databaseUri, newCollection, new RequestOptions {
OfferThroughput = 400
}).Wait();
}
catch (DocumentClientException ex)
{
if (ex.StatusCode == System.Net.HttpStatusCode.ServiceUnavailable)
{
// Emulator con sometimes fail under load, so we retry
Task.Delay(1000);
documentClient.CreateDocumentCollectionAsync(databaseUri, newCollection, new RequestOptions {
OfferThroughput = 400
}).Wait();
}
}
DocumentCollection partitionedCollection = new DocumentCollection()
{
Id = partitionedCollectionName,
PartitionKey = new PartitionKeyDefinition()
{
Paths = new Collection <string> {
"/pk"
},
}
};
try
{
documentClient.CreateDocumentCollectionAsync(databaseUri, partitionedCollection, new RequestOptions {
OfferThroughput = 10000
}).Wait();
}
catch (DocumentClientException ex)
{
if (ex.StatusCode == System.Net.HttpStatusCode.ServiceUnavailable)
{
// Emulator con sometimes fail under load, so we retry
Task.Delay(1000);
documentClient.CreateDocumentCollectionAsync(databaseUri, partitionedCollection, new RequestOptions {
OfferThroughput = 10000
}).Wait();
}
}
}
public static async Task <Document> CreateItemAsync(T item)
{
return
(await
_client.CreateDocumentAsync(UriFactory.CreateDocumentCollectionUri(DatabaseId, CollectionId), item, disableAutomaticIdGeneration : true));
}
public DocumentsController(IDocumentClient client) : base(client)
{
CollectionUri = UriFactory.CreateDocumentCollectionUri(DatabaseName, CollectionName);
}
public static async Task <IActionResult> Run(
[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = null)] HttpRequestMessage req,
ExecutionContext context,
ILogger log)
{
log.LogInformation("Getting list of books.");
if (req.Method == HttpMethod.Post)
{
return((ActionResult) new StatusCodeResult(405));
}
var config = new ConfigurationBuilder()
.SetBasePath(context.FunctionAppDirectory)
.AddJsonFile("local.settings.json", optional: true, reloadOnChange: true)
.AddEnvironmentVariables()
.Build();
log.LogInformation("Config >>> " + config);
//storage variables for secrets
SecretBundle secrets;
String cosmosEndpointUrl = String.Empty;
String cosmosAuthorizationKey = String.Empty;
String databaseId = String.Empty;
String collection = String.Empty;
try
{
var serviceTokenProvider = new AzureServiceTokenProvider();
log.LogInformation("serviceTokenProvider >>> " + serviceTokenProvider);
var keyVaultClient = new KeyVaultClient(new KeyVaultClient.AuthenticationCallback(serviceTokenProvider.KeyVaultTokenCallback));
log.LogInformation("keyVaultClient >>> " + keyVaultClient);
secrets = await keyVaultClient.GetSecretAsync($"{config["KEY_VAULT_URI"]}secrets/{config["COSMOS_NAME"]}/");
log.LogInformation("Secrets retrieved... ");
//parse json stored.
JObject details = JObject.Parse(secrets.Value.ToString());
cosmosEndpointUrl = (string)details["COSMOS_URI"];
cosmosAuthorizationKey = (string)details["COSMOS_KEY"];
databaseId = (string)details["COSMOS_DB"];
collection = (string)details["COSMOS_COLLECTION"];
} catch (Exception ex) {
log.LogError(ex.Message);
return(new ForbidResult("Unable to access secrets in vault!" + ex.Message));
}
// Connect to the cosmos endpoint with authorization key.
try
{
DocumentClient client = new DocumentClient(new Uri(cosmosEndpointUrl), cosmosAuthorizationKey);
log.LogInformation("new DocumentClient created... ");
// Set some common query options
FeedOptions queryOptions = new FeedOptions {
MaxItemCount = -1, EnableCrossPartitionQuery = true
};
log.LogInformation("queryOptions... ");
IQueryable <Book> queryBooks = client.CreateDocumentQuery <Book>(UriFactory.CreateDocumentCollectionUri(databaseId, collection),
queryOptions);
log.LogInformation("queryBooks reached here... ");
List <Book> bookList = queryBooks.ToList <Book>();
string allBooks = JsonConvert.SerializeObject(bookList, Formatting.Indented);
log.LogInformation("Now returning all books...");
return((ActionResult) new OkObjectResult(allBooks));
}
catch (Exception ex)
{
log.LogError("ERROR: " + ex.Message);
return((ActionResult) new StatusCodeResult(500));
}
}
static void AddDoc(DocumentClient client)
{
var price = new PriceList
{
CustomerCode = "ASC",
Prices = new List <PriceListItem>
{
new PriceListItem
{
ProductCode = "A",
Gender = Gender.Male,
AgeFrom = 0,
AgeTo = 65,
Price = 200
},
new PriceListItem
{
ProductCode = "A",
Gender = Gender.Male,
AgeFrom = 66,
AgeTo = 100,
Price = 250
},
new PriceListItem
{
ProductCode = "A",
Gender = Gender.Female,
AgeFrom = 0,
AgeTo = 65,
Price = 190
},
new PriceListItem
{
ProductCode = "A",
Gender = Gender.Female,
AgeFrom = 66,
AgeTo = 100,
Price = 240
},
new PriceListItem
{
ProductCode = "B",
Gender = Gender.Male,
AgeFrom = 0,
AgeTo = 65,
Price = 200
},
new PriceListItem
{
ProductCode = "B",
Gender = Gender.Male,
AgeFrom = 66,
AgeTo = 100,
Price = 250
},
new PriceListItem
{
ProductCode = "B",
Gender = Gender.Female,
AgeFrom = 0,
AgeTo = 65,
Price = 190
},
new PriceListItem
{
ProductCode = "B",
Gender = Gender.Female,
AgeFrom = 66,
AgeTo = 100,
Price = 240
},
}
};
client.CreateDocumentAsync(
UriFactory.CreateDocumentCollectionUri("crm", "prices"),
price);
}
public async Task Teardown()
{
await _documentClient.DeleteDocumentCollectionAsync(UriFactory.CreateDocumentCollectionUri(_databaseName, _collectionName)).ConfigureAwait(false);
await _documentClient.DeleteDatabaseAsync(UriFactory.CreateDatabaseUri(_databaseName)).ConfigureAwait(false);
}
public static async Task Run(
string input, // Work around https://github.com/Azure/azure-functions-vs-build-sdk/issues/168
[Inject] IConfigurationRoot configuration,
[Inject] ICosmosDbHelper cosmosDbHelper,
[Inject] IVenueCollectionService venueCollectionService,
[Inject] ILarsSearchService larsSearchService,
[Inject] IBlobStorageHelper blobHelper)
{
var databaseId = configuration["CosmosDbSettings:DatabaseId"];
var coursesCollectionId = "courses";
var logFileName = $"CourseMigrator-{DateTime.Now.ToString("dd-MM-yy HHmm")}";
var blobContainer = configuration["BlobStorageSettings:Container"];
var whitelistFileName = "ProviderWhiteList.txt";
var connectionString = configuration.GetConnectionString("TribalRestore");
var cosmosDbClient = cosmosDbHelper.GetClient();
using (var logStream = new MemoryStream())
using (var logStreamWriter = new StreamWriter(logStream))
using (var logCsvWriter = new CsvWriter(logStreamWriter, CultureInfo.InvariantCulture))
using (var conn1 = new SqlConnection(connectionString))
using (var conn2 = new SqlConnection(connectionString))
{
// Log CSV headers
logCsvWriter.WriteField("CourseId");
logCsvWriter.WriteField("UKPRN");
logCsvWriter.WriteField("Success");
logCsvWriter.WriteField("Status");
logCsvWriter.WriteField("Course instances");
logCsvWriter.WriteField("Error list");
logCsvWriter.NextRecord();
var whitelist = await GetProviderWhiteList();
await conn1.OpenAsync();
await conn2.OpenAsync();
using (var coursesCmd = conn1.CreateCommand())
using (var coursesInstancesCmd = conn2.CreateCommand())
{
coursesCmd.CommandTimeout = 60 * 60; // 1 hour
coursesInstancesCmd.CommandTimeout = 60 * 60; // 1 hour
coursesCmd.CommandText = @"
SELECT
c.CourseId,
c.CourseTitle,
c.CourseSummary,
c.LearningAimRefId,
c.QualificationLevelId,
c.EntryRequirements,
c.ProviderOwnCourseRef,
c.Url,
p.UKPRN,
c.EquipmentRequired,
c.AssessmentMethod,
p.Loans24Plus
FROM Course c
JOIN Provider p ON c.ProviderId = p.ProviderId
WHERE c.RecordStatusId = 2 --Live
--Last updated within 24 months of data freeze 28/02
AND (c.ModifiedDateTimeUtc >= '2018-02-28' OR EXISTS (
SELECT 1 FROM CourseInstance ci
WHERE ci.CourseId = c.CourseId
AND ci.RecordStatusId = 2
AND ci.ModifiedDateTimeUtc >= '2018-02-28'
))
ORDER BY c.CourseId, c.ProviderId";
coursesInstancesCmd.CommandText = @"
SELECT
ci.CourseInstanceId,
ci.CourseId,
ci.ProviderOwnCourseInstanceRef,
ci.StudyModeId,
ci.AttendanceTypeId,
ci.AttendancePatternId,
ci.DurationUnit,
ci.DurationUnitId,
ci.DurationAsText,
ci.StartDateDescription,
cisd.StartDate,
ci.Price,
ci.PriceAsText,
ci.Url,
civ.VenueId,
ci.VenueLocationId
FROM CourseInstance ci
LEFT JOIN CourseInstanceVenue civ ON ci.CourseInstanceId = civ.CourseInstanceId
LEFT JOIN CourseInstanceStartDate cisd ON ci.CourseInstanceId = cisd.CourseInstanceId
WHERE ci.RecordStatusId = 2 --Live
ORDER BY ci.CourseId, ci.OfferedByProviderId";
using (var coursesReader = coursesCmd.ExecuteReader())
using (var courseInstanceReader = coursesInstancesCmd.ExecuteReader())
{
var instanceReader = new CourseInstanceReader(courseInstanceReader);
var instanceEnumerator = instanceReader.ProcessReader().GetEnumerator();
var courseRowReader = coursesReader.GetRowParser <CourseResult>();
while (await coursesReader.ReadAsync())
{
var course = courseRowReader(coursesReader);
// If provider is not on whitelist - skip this course
if (!whitelist.Contains(course.UKPRN))
{
continue;
}
var instances = instanceReader.ConsumeReader(instanceEnumerator, course.CourseId);
var errors = new List <string>();
CourseMigrationResult result;
try
{
// Tribal don't have any Courses with zero CourseInstances...
if (instances.Count == 0)
{
errors.Add("Found zero CourseInstances.");
}
// Check LARS
var larsSearchResults = !string.IsNullOrEmpty(course.LearningAimRefId) ?
await QueryLars(course.LearningAimRefId) :
Array.Empty <LarsSearchResultItem>();
// Check the venues exist
Dictionary <int, Guid> venueIdMap = new Dictionary <int, Guid>();
foreach (var venueId in instances.Where(i => i.VenueId.HasValue).Select(i => i.VenueId.Value))
{
if (venueIdMap.ContainsKey(venueId))
{
continue;
}
var cosmosVenue = await venueCollectionService.GetDocumentByVenueId(venueId);
if (cosmosVenue == null)
{
errors.Add($"Missing venue {venueId}.");
}
else
{
venueIdMap.Add(venueId, Guid.Parse(cosmosVenue.ID));
}
}
if (errors.Count == 0)
{
// Got the course in Cosmos already?
var existingCourseRecord = await GetExistingCourse(course.CourseId, course.UKPRN, cosmosDbClient);
var mappedCourseRuns = instances
.Select(i =>
{
Guid?venueId = null;
if (i.VenueId.HasValue)
{
venueId = venueIdMap[i.VenueId.Value];
}
// Retain the existing Cosmos ID if there is one
// N.B. We can have more than one match on CourseInstanceId since we 'explode' on multiple start dates
var courseRunId =
existingCourseRecord?.CourseRuns.SingleOrDefault(r => r.CourseInstanceId == i.CourseInstanceId && r.StartDate == i.StartDate)?.id ??
Guid.NewGuid();
return(MapCourseInstance(course, i, courseRunId, venueId, errors));
})
.ToList();
var courseId = existingCourseRecord?.id ?? Guid.NewGuid();
var mappedCourse = MapCourse(course, mappedCourseRuns, larsSearchResults, courseId, errors);
var added = await UpsertCourse(mappedCourse, cosmosDbClient);
result = added ? CourseMigrationResult.Inserted : CourseMigrationResult.Updated;
}
else
{
result = CourseMigrationResult.SkippedDueToErrors;
}
}
catch (Exception ex)
{
errors.Add(ex.ToString().Replace("\n", " "));
result = CourseMigrationResult.Exception;
}
// Write to log
logCsvWriter.WriteField(course.CourseId);
logCsvWriter.WriteField(course.UKPRN);
logCsvWriter.WriteField(result == CourseMigrationResult.Inserted || result == CourseMigrationResult.Updated);
logCsvWriter.WriteField(result.ToString());
logCsvWriter.WriteField(instances.Count);
logCsvWriter.WriteField(string.Join(", ", errors));
logCsvWriter.NextRecord();
}
}
}
// Upload log CSV to blob storage
{
logStreamWriter.Flush();
logStream.Seek(0L, SeekOrigin.Begin);
var blob = blobHelper.GetBlobContainer(blobContainer).GetBlockBlobReference(logFileName);
await blob.UploadFromStreamAsync(logStream);
}
}
async Task <ISet <int> > GetProviderWhiteList()
{
var blob = blobHelper.GetBlobContainer(blobContainer).GetBlockBlobReference(whitelistFileName);
var ms = new MemoryStream();
await blob.DownloadToStreamAsync(ms);
ms.Seek(0L, SeekOrigin.Begin);
var results = new HashSet <int>();
string line;
using (var reader = new StreamReader(ms))
{
while ((line = reader.ReadLine()) != null)
{
if (string.IsNullOrEmpty(line))
{
continue;
}
var ukprn = int.Parse(line);
results.Add(ukprn);
}
}
return(results);
}
async Task <IReadOnlyCollection <LarsSearchResultItem> > QueryLars(string learningAimRef)
{
var result = await larsSearchService.SearchAsync(new LarsSearchCriteria(learningAimRef, top : 1, skip : 0));
if (result.IsFailure)
{
throw new Exception($"LARS search failed:\n{result.Error}");
}
return(result.Value.Value.ToList());
}
async Task <bool> UpsertCourse(Course course, IDocumentClient documentClient)
{
var collectionLink = UriFactory.CreateDocumentCollectionUri(databaseId, coursesCollectionId);
var result = await documentClient.UpsertDocumentAsync(collectionLink, course, new RequestOptions()
{
PartitionKey = new Microsoft.Azure.Documents.PartitionKey(course.ProviderUKPRN)
});
return(result.StatusCode == HttpStatusCode.Created);
}
async Task <Course> GetExistingCourse(int courseId, int ukprn, IDocumentClient documentClient)
{
var collectionLink = UriFactory.CreateDocumentCollectionUri(databaseId, coursesCollectionId);
var query = documentClient
.CreateDocumentQuery <Course>(collectionLink, new FeedOptions()
{
PartitionKey = new Microsoft.Azure.Documents.PartitionKey(ukprn)
})
.Where(d => d.CourseId == courseId)
.AsDocumentQuery();
return((await query.ExecuteNextAsync()).FirstOrDefault());
}
AttendancePattern MapAttendancePattern(DeliveryMode deliveryMode, int?attendancePatternId, out bool hasError)
{
if (deliveryMode != DeliveryMode.ClassroomBased)
{
hasError = false;
return(AttendancePattern.Undefined);
}
if (!attendancePatternId.HasValue)
{
hasError = true;
return(AttendancePattern.Undefined);
}
switch (attendancePatternId.Value)
{
case 1:
hasError = false;
return(AttendancePattern.Daytime);
case 2:
hasError = false;
return(AttendancePattern.DayOrBlockRelease);
case 3:
case 4:
hasError = false;
return(AttendancePattern.Evening);
case 5:
hasError = false;
return(AttendancePattern.Weekend);
case 6:
case 7:
case 8:
default:
hasError = true;
return(AttendancePattern.Undefined);
}
}
DeliveryMode MapDeliveryMode(int?attendanceTypeId, out bool hasError)
{
if (!attendanceTypeId.HasValue)
{
hasError = true;
return(DeliveryMode.Undefined);
}
switch (attendanceTypeId.Value)
{
case 1:
hasError = false;
return(DeliveryMode.ClassroomBased);
case 2:
case 3:
hasError = false;
return(DeliveryMode.WorkBased);
case 7:
case 8:
hasError = false;
return(DeliveryMode.Online);
case 4:
case 5:
case 6:
case 9:
default:
hasError = true;
return(DeliveryMode.Undefined);
}
}
StudyMode MapStudyMode(DeliveryMode deliveryMode, int?studyModeId, out bool hasError)
{
if (deliveryMode != DeliveryMode.ClassroomBased)
{
hasError = false;
return(StudyMode.Undefined);
}
if (!studyModeId.HasValue)
{
hasError = true;
return(StudyMode.Undefined);
}
switch (studyModeId.Value)
{
case 1:
hasError = false;
return(StudyMode.FullTime);
case 2:
hasError = false;
return(StudyMode.PartTime);
case 3:
hasError = true;
return(StudyMode.Undefined);
case 4:
hasError = false;
return(StudyMode.Flexible);
default:
hasError = true;
return(StudyMode.Undefined);
}
}
(DurationUnit, int?) MapDuration(int?durationUnit, int?durationValue, out bool hasError)
{
if (!durationUnit.HasValue)
{
hasError = true;
return(DurationUnit.Undefined, null);
}
switch (durationUnit.Value)
{
case 1:
hasError = false;
return(DurationUnit.Hours, durationValue);
case 2:
hasError = false;
return(DurationUnit.Days, durationValue);
case 3:
hasError = false;
return(DurationUnit.Weeks, durationValue);
case 4:
hasError = false;
return(DurationUnit.Months, durationValue);
case 5:
hasError = false;
return(DurationUnit.Months, 3);
case 7:
hasError = false;
return(DurationUnit.Years, durationValue);
case 6:
default:
hasError = true;
return(DurationUnit.Undefined, null);
}
}
CourseRun MapCourseInstance(
CourseResult course,
CourseInstanceResult courseInstance,
Guid id,
Guid?venueId,
List <string> errors)
{
var deliveryMode = MapDeliveryMode(courseInstance.AttendanceTypeId, out var deliveryModeError);
var attendancePattern = MapAttendancePattern(deliveryMode, courseInstance.AttendancePatternId, out var attendancePatternError);
var studyMode = MapStudyMode(deliveryMode, courseInstance.StudyModeId, out var studyModeError);
var(durationUnit, durationValue) = MapDuration(courseInstance.DurationUnitId, courseInstance.DurationUnit, out var durationError);
var hasErrors = false;
if (attendancePatternError)
{
errors.Add($"Invalid AttendancePattern");
hasErrors = true;
}
if (deliveryModeError)
{
errors.Add($"Invalid DeliveryMode");
hasErrors = true;
}
if (studyModeError)
{
errors.Add($"Invalid StudyMode");
hasErrors = true;
}
if (durationError)
{
errors.Add($"Invalid Duration");
hasErrors = true;
}
bool flexibleStartDate = default;
DateTime?startDate = default;
if (deliveryMode == DeliveryMode.Online)
{
flexibleStartDate = true;
}
else if (courseInstance.StartDate.HasValue)
{
flexibleStartDate = false;
startDate = courseInstance.StartDate;
}
else if (string.IsNullOrEmpty(courseInstance.StartDateDescription))
{
errors.Add($"Empty StartDateDescription");
hasErrors = true;
}
else if (DateTime.TryParseExact(courseInstance.StartDateDescription, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out var sd))
{
flexibleStartDate = false;
startDate = sd;
}
else
{
flexibleStartDate = true;
}
if (deliveryMode == DeliveryMode.ClassroomBased && !venueId.HasValue)
{
errors.Add($"No venue");
hasErrors = true;
}
// Work-based should have regions(s) or be national
bool?national = null;
IEnumerable <string> regions = Array.Empty <string>();
IEnumerable <SubRegionItemModel> subRegions = Array.Empty <SubRegionItemModel>();
if (deliveryMode == DeliveryMode.WorkBased)
{
if (!courseInstance.VenueLocationId.HasValue)
{
errors.Add("No region found");
hasErrors = true;
}
else
{
if (RegionLookup.IsNational(courseInstance.VenueLocationId.Value))
{
national = true;
}
else
{
var lookupResult = RegionLookup.FindRegions(courseInstance.VenueLocationId.Value);
if (!lookupResult.HasValue)
{
errors.Add($"Cannot find sub-region(s) for VenueLocationId {courseInstance.VenueLocationId.Value}");
hasErrors = true;
}
else
{
regions = lookupResult.Value.regionIds;
subRegions = lookupResult.Value.subRegions;
national = false;
}
}
}
}
var recordStatus = hasErrors ? RecordStatus.MigrationPending : RecordStatus.Live;
return(new CourseRun()
{
AttendancePattern = attendancePattern,
Cost = courseInstance.Price,
CostDescription = courseInstance.PriceAsText,
CourseInstanceId = courseInstance.CourseInstanceId,
CourseName = course.CourseTitle,
CourseURL = courseInstance.Url,
CreatedBy = "CourseMigrator",
CreatedDate = DateTime.Now,
DeliveryMode = deliveryMode,
DurationUnit = durationUnit,
DurationValue = durationValue,
FlexibleStartDate = flexibleStartDate,
id = id,
ProviderCourseID = courseInstance.ProviderOwnCourseInstanceRef,
RecordStatus = recordStatus,
National = national,
Regions = regions,
StartDate = startDate,
StudyMode = studyMode,
SubRegions = subRegions,
//UpdatedBy
UpdatedDate = DateTime.Now,
VenueId = venueId
});
}
Course MapCourse(
CourseResult course,
IReadOnlyCollection <CourseRun> courseRuns,
IReadOnlyCollection <LarsSearchResultItem> larsSearchResults,
Guid id,
List <string> errors)
{
var isValid = courseRuns.All(r => r.RecordStatus.HasFlag(RecordStatus.Live));
LarlessReason?larlessReason = string.IsNullOrEmpty(course.LearningAimRefId) ?
LarlessReason.NoLars : larsSearchResults.Count == 0 ?
LarlessReason.UnknownLars : larsSearchResults.Count > 1 ?
LarlessReason.MultipleMatchingLars : // TODO Consider expired LARS
LarlessReason.Undefined;
var qualification = larsSearchResults.Count == 1 ? larsSearchResults.Single() : null;
if (qualification == null)
{
foreach (var cr in courseRuns)
{
cr.RecordStatus = RecordStatus.MigrationPending;
}
errors.Add("LARS lookup failed");
isValid = false;
}
return(new Course()
{
AdultEducationBudget = default,
public Uri GetCollectionUri()
{
return(UriFactory.CreateDocumentCollectionUri(_databaseName, _collectionName));
}
public Statistics Statistics()
{
Statistics statistics = new Statistics();
statistics.Men = this._client.CreateDocumentQuery <SocialAnalytics>(
UriFactory.CreateDocumentCollectionUri(Config.DataBaseId, Config.DatabaseCollectionName))
.SelectMany(s => s.FaceResult).Where(s => s.FaceAttributes.Gender == "male").ToList().Count;
statistics.Women = this._client.CreateDocumentQuery <SocialAnalytics>(
UriFactory.CreateDocumentCollectionUri(Config.DataBaseId, Config.DatabaseCollectionName))
.SelectMany(s => s.FaceResult).Where(s => s.FaceAttributes.Gender == "female").ToList().Count;
var ages = this._client.CreateDocumentQuery <SocialAnalytics>(
UriFactory.CreateDocumentCollectionUri(Config.DataBaseId, Config.DatabaseCollectionName))
.SelectMany(s => s.FaceResult).Select(s => s.FaceAttributes.Age).ToList();
statistics.Children = ages.Count(a => a < 18);
statistics.Age = (int)ages.Average();
var lastItem = this._client.CreateDocumentQuery <SocialAnalytics>(
UriFactory.CreateDocumentCollectionUri(Config.DataBaseId, Config.DatabaseCollectionName))
.OrderByDescending(c => c.CreatedAtTimeStamp).Take(1).ToList();
statistics.From = lastItem.FirstOrDefault()?.CreatedAt;
var emotions = this._client.CreateDocumentQuery <SocialAnalytics>(
UriFactory.CreateDocumentCollectionUri(Config.DataBaseId, Config.DatabaseCollectionName))
.SelectMany(s => s.EmotionResult).Select(s => s.Scores).ToList();
var emotionStat = new[]
{
new { Name = "Anger", Value = emotions.Sum(t => t.Anger) },
new { Name = "Contempt", Value = emotions.Sum(t => t.Contempt) },
new { Name = "Disgust", Value = emotions.Sum(t => t.Disgust) },
new { Name = "Fear", Value = emotions.Sum(t => t.Fear) },
new { Name = "Happiness", Value = emotions.Sum(t => t.Happiness) },
new { Name = "Neutral", Value = emotions.Sum(t => t.Neutral) },
new { Name = "Sadness", Value = emotions.Sum(t => t.Sadness) },
new { Name = "Surprise", Value = emotions.Sum(t => t.Surprise) }
};
var topEmotion = emotionStat.OrderByDescending(e => e.Value).FirstOrDefault();
if (topEmotion != null)
{
statistics.Emotion = new NamePercent {
Name = topEmotion.Name, Percent = (int)Math.Round(topEmotion.Value / emotionStat.Sum(t => t.Value) * 100)
}
}
;
var categories = this._client.CreateDocumentQuery <SocialAnalytics>(
UriFactory.CreateDocumentCollectionUri(Config.DataBaseId, Config.DatabaseCollectionName))
.SelectMany(s => s.AnalysisResult.Categories).ToList();
var predicateCat = categories.GroupBy(s => s.Name).Select(s => new { Name = s.Key, Value = s.Sum(g => g.Score) });
var cat = predicateCat.OrderByDescending(s => s.Value).FirstOrDefault();
statistics.Category = new NamePercent
{
Name = cat.Name,
Percent = (int)Math.Round(cat.Value / predicateCat.Sum(s => s.Value) * 100)
};
return(statistics);
}
void ICosmosRepositoryBase <TEntity> .Insert(TEntity obj)
{
var document1 = this.cosmosClient.CreateDocumentAsync(UriFactory.CreateDocumentCollectionUri(databaseId, docCollectionId), obj).Result;
}
IEnumerable <TEntity> ICosmosRepositoryBase <TEntity> .GetAll()
{
return(this.cosmosClient.CreateDocumentQuery <TEntity>(UriFactory.CreateDocumentCollectionUri(databaseId, docCollectionId), "select * from c")
.ToList());
}
public KeyRepository(string endpointUrl, string authorizationKey) :
base(endpointUrl, authorizationKey)
{
_documentCollectionUri = UriFactory.CreateDocumentCollectionUri(DatabaseId, CollectionId);
}
protected Uri EventCollectionUri(Type aggregateType)
{
return(UriFactory.CreateDocumentCollectionUri(DatabaseId, aggregateType.Name));
}
public static async Task Run(
string input, // Work around https://github.com/Azure/azure-functions-vs-build-sdk/issues/168
[Inject] IConfigurationRoot configuration,
[Inject] ICosmosDbHelper cosmosDbHelper,
[Inject] IBlobStorageHelper blobHelper,
[Inject] ILoggerFactory loggerFactory,
[Inject] IUkrlpApiService ukrlpApiService)
{
var blobContainer = configuration["BlobStorageSettings:Container"];
var databaseId = configuration["CosmosDbSettings:DatabaseId"];
var coursesCollectionId = "courses";
var providerCollectionId = "ukrlp";
var documentClient = cosmosDbHelper.GetClient();
var coursesCollectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, coursesCollectionId);
var providerCollectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, providerCollectionId);
var logger = loggerFactory.CreateLogger(typeof(ArchiveCourses));
var count = 0;
var logFileName = $"CoursesWithMissingLarsCodes";
string continuation = null;
using (var logStream = new MemoryStream())
using (var logStreamWriter = new StreamWriter(logStream))
using (var logCsvWriter = new CsvWriter(logStreamWriter, CultureInfo.InvariantCulture))
{
// Log CSV headers
logCsvWriter.WriteField("UKPRN");
logCsvWriter.WriteField("ProviderName");
logCsvWriter.WriteField("CourseId");
logCsvWriter.WriteField("Provider course ID");
logCsvWriter.WriteField("Course name");
logCsvWriter.WriteField("Start date");
logCsvWriter.WriteField("Cost");
logCsvWriter.WriteField("Cost description");
logCsvWriter.WriteField("Delivery mode");
logCsvWriter.WriteField("Attendance mode");
logCsvWriter.NextRecord();
do
{
try
{
var feedOptions = new FeedOptions()
{
RequestContinuation = continuation,
EnableCrossPartitionQuery = true
};
var queryResponse = await documentClient.CreateDocumentQuery <Course>(coursesCollectionUri, feedOptions)
.Where(p => (p.LearnAimRef == null || p.QualificationCourseTitle == null) && p.CourseStatus != CourseDirectory.Models.Enums.RecordStatus.Archived)
.AsDocumentQuery()
.ExecuteNextAsync <Course>();
foreach (var doc in queryResponse)
{
var providers = ukrlpApiService.GetAllProviders(new List <string> {
doc.ProviderUKPRN.ToString()
});
var provider = providers.FirstOrDefault();
foreach (var courserun in doc.CourseRuns)
{
logCsvWriter.WriteField(doc.ProviderUKPRN);
logCsvWriter.WriteField(provider?.ProviderName);
logCsvWriter.WriteField(courserun.CourseInstanceId);
logCsvWriter.WriteField(doc.CourseId);
logCsvWriter.WriteField(courserun.CourseName);
logCsvWriter.WriteField(courserun.StartDate);
logCsvWriter.WriteField(courserun.Cost);
logCsvWriter.WriteField(courserun.CostDescription);
logCsvWriter.WriteField(courserun.DeliveryMode);
logCsvWriter.WriteField(courserun.AttendancePattern);
logCsvWriter.NextRecord();
courserun.RecordStatus = CourseDirectory.Models.Enums.RecordStatus.Archived;
count++;
}
count++;
var documentLink = UriFactory.CreateDocumentUri(databaseId, coursesCollectionId, doc.id.ToString());
await documentClient.ReplaceDocumentAsync(documentLink, doc, new RequestOptions()
{
PartitionKey = new Microsoft.Azure.Documents.PartitionKey(doc.ProviderUKPRN)
});
}
continuation = queryResponse.ResponseContinuation;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
logger.LogError(e.Message);
continuation = null;
}
}while (continuation != null);
// Upload log CSV to blob storage
{
logStreamWriter.Flush();
logStream.Seek(0L, SeekOrigin.Begin);
var blob = blobHelper.GetBlobContainer(blobContainer).GetBlockBlobReference(logFileName);
await blob.UploadFromStreamAsync(logStream);
}
}
Console.WriteLine($"{count} courses have been archived");
}
protected Uri SnapshotCollectionUri(Type aggregateType)
{
return(UriFactory.CreateDocumentCollectionUri(DatabaseId, SnapshotCollectionName(aggregateType)));
}
private static async Task Cleanup2(DocumentClient client)
{
// Delete documents created by demos
Console.WriteLine("Deleting documents created by demos...");
var sql = @"
SELECT c._self, c.address.postalCode
FROM c
WHERE
STARTSWITH(c.name, 'New Customer') OR
STARTSWITH(c.id, '_meta') OR
IS_DEFINED(c.weekdayOff) OR
STARTSWITH(c.id, 'DUPEJ') OR
STARTSWITH(c.name, 'Bulk inserted doc ')
" ;
// WHERE
// c.id IN('NEWDOC', '_metadata') OR
// c.name IN('John Doe') OR
// STARTSWITH(c.id, 'DUPEJ') = true OR
// STARTSWITH(c.id, 'MUFASA') = true OR
// STARTSWITH(c.name, 'New Customer') = true
// OR STARTSWITH(c.name, 'Bulk inserted doc ') = true
var collectionUri = UriFactory.CreateDocumentCollectionUri("mydb", "mystore");
var feedOptions = new FeedOptions {
EnableCrossPartitionQuery = true
};
var documentKeys = client.CreateDocumentQuery(collectionUri, sql, feedOptions).AsEnumerable();
foreach (var documentKey in documentKeys)
{
var requestOptions = new RequestOptions {
PartitionKey = new PartitionKey(documentKey.postalCode)
};
await client.DeleteDocumentAsync(documentKey._self, requestOptions);
}
var sprocs = client.CreateStoredProcedureQuery(collectionUri).AsEnumerable();
// Delete all stored procedures
Console.WriteLine("Deleting all stored procedures...");
foreach (var sproc in sprocs)
{
await client.DeleteStoredProcedureAsync(sproc.SelfLink);
}
// Delete all user defined functions
Console.WriteLine("Deleting all user defined functions...");
var udfs = client.CreateUserDefinedFunctionQuery(collectionUri).AsEnumerable();
foreach (var udf in udfs)
{
await client.DeleteUserDefinedFunctionAsync(udf.SelfLink);
}
// Delete all triggers
Console.WriteLine("Deleting all triggers...");
var triggers = client.CreateTriggerQuery(collectionUri).AsEnumerable();
foreach (var trigger in triggers)
{
await client.DeleteTriggerAsync(trigger.SelfLink);
}
// Delete all users
Console.WriteLine("Deleting all users...");
var databaseUri = UriFactory.CreateDatabaseUri("mydb");
var users = client.CreateUserQuery(databaseUri).AsEnumerable();
foreach (var user in users)
{
await client.DeleteUserAsync(user.SelfLink);
}
}
public static async Task RunAsync([ServiceBusTrigger("sampletopic", "SampleSubscription", AccessRights.Manage, Connection = "ServicebusConnectionString")] BrokeredMessage mySbMsg, TraceWriter log)
{
// URI Of The Cosmos DB Account
string EndpointUrI = Environment.GetEnvironmentVariable(Constants.FunctionConfiguration.Cosmos_Db_URI);
// Primary Key of the Cosmos DB Account
string PrimaryKey = Environment.GetEnvironmentVariable(Constants.FunctionConfiguration.Primary_Key);
// Database Name inside Cosmos DB
string DatabaseId = Environment.GetEnvironmentVariable(Constants.FunctionConfiguration.Database_Id);
// ContainerID
string ContainerId = Environment.GetEnvironmentVariable(Constants.FunctionConfiguration.Container_Id);
// ConnectionPolicy connectionPolicy = new ConnectionPolicy { UserAgentSuffix = "samples-net/3" };
// Creates a client of DocumentClient Class
DocumentClient client;
// Converting the Brokered message into Stream
Stream msgStream = mySbMsg.GetBody <Stream>();
// Creating an instance of StreamReader of type msgStream
StreamReader reader = new StreamReader(msgStream);
// Converting the stream into string
string queueMessage = reader.ReadToEnd();
string JsonAsText = string.Empty;
CloudBlockBlob blockBlob;
dynamic msg = JsonConvert.DeserializeObject(queueMessage);
string fileName = msg.FileName;
var sessionID = msg.SessionId;
var connectionString = Environment.GetEnvironmentVariable(Constants.FunctionConfiguration.Connection_String);
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connectionString);
// Create Blob service client
CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
// Get Container Reference
CloudBlobContainer container = blobClient.GetContainerReference(Environment.GetEnvironmentVariable(Constants.FunctionConfiguration.Container_Name));
// Get Blob Reference
blockBlob = container.GetBlockBlobReference(fileName);
// Download Blob as Text.
JsonAsText = blockBlob.DownloadText();
JObject newJson = JObject.Parse(JsonAsText);
try
{
using (client = new DocumentClient(new Uri(EndpointUrI), PrimaryKey))
{
var collectionLink = UriFactory.CreateDocumentCollectionUri(DatabaseId, ContainerId);
Document created = await client.CreateDocumentAsync(collectionLink, newJson);
if (created != null)
{
blockBlob.DeleteIfExists();
}
}
}
catch (DocumentClientException de)
{
Exception baseException = de.GetBaseException();
log.Error($"{de.StatusCode} error occurred: {de.Message}, Message: {baseException.Message}");
}
catch (System.Exception e)
{
Exception baseException = e.GetBaseException();
log.Error(string.Format("Error: {0}, Message: {1}", e.Message, baseException.Message));
throw;
}
//async Task RunDocumentsDemo()
//{
// await BasicCRUDAsync();
//}
//async Task BasicCRUDAsync()
//{
// var collectionLink = UriFactory.CreateDocumentCollectionUri(databaseId, containerID);
// Document created = await client.CreateDocumentAsync(collectionLink, newJson);
// Console.WriteLine(created);
//}
}
private async Task TestOrderyByQueryAsync()
{
var jsonSerializerSettings = new JsonSerializerSettings
{
ConstructorHandling = ConstructorHandling.AllowNonPublicDefaultConstructor,
Converters =
{
new ObjectStringJsonConverter <SerializedObject>(_ => _.Name, _ => SerializedObject.Parse(_))
}
};
var connectionPolicy = new ConnectionPolicy();
ConsistencyLevel defaultConsistencyLevel = ConsistencyLevel.Session;
DocumentClient client = this.CreateDocumentClient(
this.hostUri,
this.masterKey,
jsonSerializerSettings,
connectionPolicy,
defaultConsistencyLevel);
var collectionUri = UriFactory.CreateDocumentCollectionUri(this.databaseName, this.collectionName);
// Create a few test documents
int documentCount = 3;
var numberFieldName = "NumberField";
for (int i = 0; i < documentCount; ++i)
{
var newDocument = new MyObject(i);
var createdDocument = client.CreateDocumentAsync(collectionUri, newDocument, ApplyRequestOptions(new RequestOptions(), jsonSerializerSettings)).Result.Resource;
}
FeedOptions feedOptions = this.ApplyFeedOptions(
new FeedOptions
{
EnableCrossPartitionQuery = true,
MaxBufferedItemCount = -1,
MaxDegreeOfParallelism = -1,
MaxItemCount = -1
},
jsonSerializerSettings);
var orderedQuery = client.CreateDocumentQuery <MyObject>(
collectionUri,
new SqlQuerySpec(
"SELECT * FROM root"
),
feedOptions);
var orderedQuery2 = client.CreateDocumentQuery <MyObject>(
collectionUri,
new SqlQuerySpec(
"SELECT * FROM root ORDER BY root[\"" + numberFieldName + "\"] DESC"
),
feedOptions);
var documentQuery = orderedQuery.AsDocumentQuery();
var documentQuery2 = orderedQuery2.AsDocumentQuery();
var results = await documentQuery.ExecuteNextAsync <MyObject>();
var results2 = await documentQuery2.ExecuteNextAsync <MyObject>();
var list1 = results.ToList();
var list2 = results2.ToList();
Assert.AreEqual(documentCount, list1.Count);
Assert.AreEqual(documentCount, list2.Count);
for (int i = 0; i < documentCount; ++i)
{
Assert.AreEqual("Name: " + (documentCount - i - 1), list2[i].SerializedObject.Name);
}
}
private static async Task RunDemoAsync(string databaseId, string collectionId)
{
Database database = await client.CreateDatabaseIfNotExistsAsync(new Database { Id = databaseId });
DocumentCollection collection = await GetOrCreateCollectionAsync(databaseId, collectionId);
Uri collectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, collectionId);
await CreateDocuments(collectionUri);
//--------------------------------------------------------------------------------------------------------
// There are three ways of writing queries in the .NET SDK for DocumentDB,
// using the SQL Query Grammar, using LINQ Provider with Query and with Lambda.
// This sample will show each query using all methods.
// It is entirely up to you which style of query you write as they result in exactly the same query being
// executed on the service.
//
// There are some occasions when one syntax has advantages over others, but it's your choice which to use when
//--------------------------------------------------------------------------------------------------------
// Querying for all documents
QueryAllDocuments(collectionUri);
// Querying for equality using ==
QueryWithEquality(collectionUri);
// Querying for inequality using != and NOT
QueryWithInequality(collectionUri);
// Querying using range operators like >, <, >=, <=
QueryWithRangeOperatorsOnNumbers(collectionUri);
// Querying using range operators against strings. Needs a different indexing policy or the EnableScanInQuery directive.
QueryWithRangeOperatorsOnStrings(collectionUri);
QueryWithRangeOperatorsDateTimes(collectionUri);
// Querying with order by
QueryWithOrderBy(collectionUri);
// Query with aggregate operators - Sum, Min, Max, Average, and Count
QueryWithAggregates(collectionUri);
// Work with subdocuments
QueryWithSubdocuments(collectionUri);
// Query with Intra-document Joins
QueryWithJoins(collectionUri);
// Query with string, math and array operators
QueryWithStringMathAndArrayOperators(collectionUri);
// Query with parameterized SQL using SqlQuerySpec
QueryWithSqlQuerySpec(collectionUri);
// Query with explict Paging
await QueryWithPagingAsync(collectionUri);
// Query across multiple partitions in parallel
await QueryPartitionedCollectionInParallelAsync(collectionUri);
// Query using order by across multiple partitions
await QueryWithOrderByForPartitionedCollectionAsync(collectionUri);
// Uncomment to Cleanup
// await client.DeleteDatabaseAsync(UriFactory.CreateDatabaseUri(databaseId));
}
public static async Task <Document> CreateStudent(T student)
{
return(await client.CreateDocumentAsync(UriFactory.CreateDocumentCollectionUri(DatabaseId, CollectionId), student));
}
public DocumentDBService()
{
collectionLink = UriFactory.CreateDocumentCollectionUri(Constants.DatabaseName, Constants.CollectionName);
}
public static async Task DeleteLogs()
{
await _client.DeleteDocumentCollectionAsync(
UriFactory.CreateDocumentCollectionUri(DatabaseId, CollectionId));
}
//GETALL
public static async Task <List <CosmosDBPrayerRequest> > GetAllCosmosPrayerRequests()
{
MyListOfCosmosDogs = new List <CosmosDBPrayerRequest>();
try
{
var query = myDocumentClient.CreateDocumentQuery <CosmosDBPrayerRequest>(UriFactory.CreateDocumentCollectionUri(DatabaseId, CollectionId))
.AsDocumentQuery();
while (query.HasMoreResults)
{
MyListOfCosmosDogs.AddRange(await query.ExecuteNextAsync <CosmosDBPrayerRequest>());
}
}
catch (DocumentClientException ex)
{
Debug.WriteLine("Error: ", ex.Message);
}
return(MyListOfCosmosDogs);
}
public async Task <string> CreateItemAsync(T item)
{
var document = await _client.CreateDocumentAsync(UriFactory.CreateDocumentCollectionUri(_databaseId, _collectionId), item);
return(document.Resource.Id);
}
//POST
public static async Task PostCosmosPrayerRequestsAsync(CosmosDBPrayerRequest cosmosDBPrayerRequest)
{
await myDocumentClient.CreateDocumentAsync(UriFactory.CreateDocumentCollectionUri(DatabaseId, CollectionId), cosmosDBPrayerRequest);
}
protected override Task <IEnumerable <ListDTO> > InternalExecute(GetListsQuery query)
{
var uri = UriFactory.CreateDocumentCollectionUri(db, collection);
return(Task.FromResult(documentClient.CreateDocumentQuery <ListDTO>(uri).AsEnumerable()));
}
public async Task <Document> CreateItemAsync(T item)
{
return(await client.CreateDocumentAsync(UriFactory.CreateDocumentCollectionUri(_databaseSettings.Database, _databaseSettings.Collection), item));
}
