C# DatabaseWrapper类代码示例

        public async Task Execute_HappyPath_ReturnsProducerEeeDataHistory()
        {
            using (DatabaseWrapper db = new DatabaseWrapper())
            {
                //Arrange
                ModelHelper helper = new ModelHelper(db.Model);
                var scheme1 = helper.CreateScheme();
                scheme1.ApprovalNumber = "WEE/TE0000ST/SCH";
                var memberUpload1 = helper.CreateSubmittedMemberUpload(scheme1);
                memberUpload1.ComplianceYear = 2000;

                var producer1 = helper.CreateProducerAsCompany(memberUpload1, "PRN123");
                producer1.ObligationType = "B2B";
                var dataReturnVersion1 = helper.CreateDataReturnVersion(scheme1, 2000, 1);
                dataReturnVersion1.SubmittedDate = new DateTime(2015, 1, 6);
                var dataReturnVersion2 = helper.CreateDataReturnVersion(scheme1, 2000, 2);
                dataReturnVersion2.SubmittedDate = new DateTime(2015, 1, 8);

                helper.CreateEeeOutputAmount(dataReturnVersion1, producer1.RegisteredProducer, "B2B", 1, 100);
                helper.CreateEeeOutputAmount(dataReturnVersion2, producer1.RegisteredProducer, "B2B", 2, 200);

                var scheme2 = helper.CreateScheme();
                scheme2.ApprovalNumber = "WEE/TE0000S1/SCH";
                var memberUpload2 = helper.CreateSubmittedMemberUpload(scheme2);
                memberUpload2.ComplianceYear = 2000;

                var producer2 = helper.CreateProducerAsCompany(memberUpload2, "PRN123");
                producer2.ObligationType = "B2C";
                var dataReturnVersion3 = helper.CreateDataReturnVersion(scheme2, 2000, 1);
                dataReturnVersion1.SubmittedDate = new DateTime(2015, 1, 9);
                var dataReturnVersion4 = helper.CreateDataReturnVersion(scheme2, 2000, 2);
                dataReturnVersion4.SubmittedDate = new DateTime(2015, 1, 10);

                helper.CreateEeeOutputAmount(dataReturnVersion3, producer2.RegisteredProducer, "B2C", 1, 40);
                helper.CreateEeeOutputAmount(dataReturnVersion4, producer2.RegisteredProducer, "B2C", 2, 1000);

                db.Model.SaveChanges();

                // Act
                var results = await db.StoredProcedures.SpgProducerEeeHistoryCsvData("PRN123");

                //Assert
                Assert.NotNull(results);

                ProducerEeeHistoryCsvData.ProducerInReturnsResult b2cProducer = results.ProducerReturnsHistoryData.Find(x => (x.ApprovalNumber == "WEE/TE0000S1/SCH"));
                Assert.NotNull(b2cProducer);
                Assert.Equal(2000, b2cProducer.ComplianceYear);
                Assert.Equal(1000, b2cProducer.Cat2B2C);
                Assert.Equal("Yes", b2cProducer.LatestData);

                ProducerEeeHistoryCsvData.ProducerInReturnsResult b2bProducer = results.ProducerReturnsHistoryData.Find(x => (x.ApprovalNumber == "WEE/TE0000ST/SCH"));
                Assert.NotNull(b2bProducer);
                Assert.Equal(2000, b2bProducer.ComplianceYear);
                Assert.Equal(200, b2bProducer.Cat2B2B);
                Assert.Null(b2bProducer.Cat2B2C);
                Assert.Equal("Yes", b2bProducer.LatestData);

                Assert.Equal(4, results.ProducerReturnsHistoryData.Count);
            }
        }

        public async Task HandleAsync_CreatesUserWithStandardUserRole()
        {
            using (DatabaseWrapper database = new DatabaseWrapper())
            {
                // Arrange
                ModelHelper helper = new ModelHelper(database.Model);
                DomainHelper domainHelper = new DomainHelper(database.WeeeContext);

                var user = helper.GetOrCreateUser("TestUser");
                user.Email = "[email protected]";

                database.Model.SaveChanges();

                var handler = new AddCompetentAuthorityUserHandler(database.WeeeContext, A.Dummy<ITestUserEmailDomains>());

                // Act
                var competentAuthorityUserId = await handler.HandleAsync(new AddCompetentAuthorityUser(user.Id));
                var competentAuthorityUser = domainHelper.GetCompetentAuthorityUser(competentAuthorityUserId);

                // Assert
                Assert.Equal(user.Id, competentAuthorityUser.UserId);
                Assert.NotNull(competentAuthorityUser.Role);
                Assert.Equal("InternalUser", competentAuthorityUser.Role.Name);
            }
        }

        public async Task MemberUploadSubmissionHandler_SubmitMemberUpload_NewRegisteredProducer_ContainsCorrectValueFor_CurrentSubmission()
        {
            using (var database = new DatabaseWrapper())
            {
                var helper = new ModelHelper(database.Model);

                var scheme = helper.CreateScheme();
                string registrationNumber = "AAAA";
                int complianceYear = 2016;

                var registeredProducer = helper.GetOrCreateRegisteredProducer(scheme, complianceYear, registrationNumber);

                var memberUpload = helper.CreateMemberUpload(scheme);
                memberUpload.ComplianceYear = complianceYear;
                memberUpload.IsSubmitted = false;

                var producerSubmission = helper.CreateProducerAsCompany(memberUpload, registrationNumber);

                // At least one user is required in the database.
                helper.GetOrCreateUser("A user");

                database.Model.SaveChanges();

                User user = await database.WeeeContext.Users.FirstAsync();
                IDomainUserContext domainUserContext = A.Fake<IDomainUserContext>();
                A.CallTo(() => domainUserContext.GetCurrentUserAsync()).Returns(user);

                var handler = new MemberUploadSubmissionHandler(A.Dummy<IWeeeAuthorization>(), database.WeeeContext, domainUserContext);
                await handler.HandleAsync(new MemberUploadSubmission(scheme.OrganisationId, memberUpload.Id));

                var registeredProducerDb = FindRegisteredProducer(database, registeredProducer.Id);

                Assert.Equal(registeredProducerDb.CurrentSubmission.Id, producerSubmission.Id);
            }
        }

        public async Task GetComplianceYears_DoesNotReturnUnsubmittedUploads()
        {
            using (DatabaseWrapper database = new DatabaseWrapper())
            {
                // Arrange
                ModelHelper helper = new ModelHelper(database.Model);

                var scheme = helper.CreateScheme();
                var memberUpload = helper.CreateMemberUpload(scheme);
                memberUpload.ComplianceYear = 2016;
                memberUpload.IsSubmitted = false;

                var producer1 = helper.CreateProducerAsCompany(memberUpload, "AAA");

                database.Model.SaveChanges();

                var dataAccess = new GetProducerComplianceYearDataAccess(database.WeeeContext);

                // Act
                var result = await dataAccess.GetComplianceYears("AAA");

                // Assert
                Assert.Empty(result);
            }
        }

        public async void SpgSubmissionChangesCsvData_ReturnsProducerAsNew_WhenExistingProducerUploadButNoSubmissionForSameYearAndScheme()
        {
            using (var database = new DatabaseWrapper())
            {
                // Arrange
                ModelHelper modelHelper = new ModelHelper(database.Model);

                var scheme = modelHelper.CreateScheme();

                var memberUpload1 = modelHelper.CreateSubmittedMemberUpload(scheme);
                memberUpload1.ComplianceYear = 2016;
                memberUpload1.IsSubmitted = false;

                modelHelper.CreateProducerAsCompany(memberUpload1, "1111");

                var memberUpload2 = modelHelper.CreateSubmittedMemberUpload(scheme);
                memberUpload2.ComplianceYear = 2016;

                modelHelper.CreateProducerAsCompany(memberUpload2, "1111");

                database.Model.SaveChanges();

                StoredProcedures storedProcedures = new StoredProcedures(database.WeeeContext);

                // Act
                var result = await storedProcedures.SpgSubmissionChangesCsvData(memberUpload2.Id);

                // Assert
                Assert.Single(result);
                Assert.Equal("New", result[0].ChangeType);
            }
        }

        public async Task GetPreviousSubmission_ReturnsNullIfPreviousUploadButNoSubmission()
        {
            using (var database = new DatabaseWrapper())
            {
                // Arrange
                DataReturnSubmissionsDataAccess dataAccess = new DataReturnSubmissionsDataAccess(database.WeeeContext);

                ModelHelper modelHelper = new ModelHelper(database.Model);
                DomainHelper domainHelper = new DomainHelper(database.WeeeContext);

                var scheme = modelHelper.CreateScheme();

                var dataReturnVersion = modelHelper.CreateDataReturnVersion(scheme, 2015, 1, false);

                var dataReturnVersion2 = modelHelper.CreateDataReturnVersion(scheme, 2015, 1, true);
                dataReturnVersion2.SubmittedDate = new DateTime(2015, 2, 1);

                database.Model.SaveChanges();

                var domainDataReturnVersion = domainHelper.GetDataReturnVersion(dataReturnVersion2.Id);

                // Act
                var result = await dataAccess.GetPreviousSubmission(domainDataReturnVersion);

                // Assert
                Assert.Null(result);
            }
        }

        public async Task GetPreviousSubmission_ReturnsPreviousSubmissionForSameSchemeOnly()
        {
            using (var database = new DatabaseWrapper())
            {
                // Arrange
                DataReturnSubmissionsDataAccess dataAccess = new DataReturnSubmissionsDataAccess(database.WeeeContext);

                ModelHelper modelHelper = new ModelHelper(database.Model);
                DomainHelper domainHelper = new DomainHelper(database.WeeeContext);

                var scheme1 = modelHelper.CreateScheme();

                var dataReturnVersion = modelHelper.CreateDataReturnVersion(scheme1, 2015, 1, true);
                dataReturnVersion.SubmittedDate = new DateTime(2015, 1, 1);

                var dataReturnVersion2 = modelHelper.CreateDataReturnVersion(scheme1, 2015, 1, true);
                dataReturnVersion2.SubmittedDate = new DateTime(2015, 3, 1);

                var scheme2 = modelHelper.CreateScheme();

                var dataReturnVersion3 = modelHelper.CreateDataReturnVersion(scheme2, 2015, 1, true);
                dataReturnVersion3.SubmittedDate = new DateTime(2015, 2, 1);

                database.Model.SaveChanges();

                var domainDataReturnVersion = domainHelper.GetDataReturnVersion(dataReturnVersion2.Id);

                // Act
                var result = await dataAccess.GetPreviousSubmission(domainDataReturnVersion);

                // Assert
                Assert.NotNull(result);
                Assert.Equal(dataReturnVersion.Id, result.Id);
            }
        }

        public async Task RemoveRegisteredProducer_ProducerIsMarkedAsRemovedAndRemovedDateIsSet()
        {
            using (DatabaseWrapper database = new DatabaseWrapper())
            {
                var context = database.WeeeContext;

                var organisation = Organisation.CreateSoleTrader("My trading name");
                context.Organisations.Add(organisation);
                await context.SaveChangesAsync();

                var scheme = new Scheme(organisation.Id);
                context.Schemes.Add(scheme);
                await context.SaveChangesAsync();

                var producer = new RegisteredProducer("ABC12345", 2017, scheme);
                context.AllRegisteredProducers.Add(producer);

                SystemTime.Freeze(new DateTime(2016, 10, 1));

                producer.Remove();

                SystemTime.Unfreeze();

                await context.SaveChangesAsync();

                producer = context.AllRegisteredProducers
                    .SingleOrDefault(p => p.Id == producer.Id);

                Assert.NotNull(producer);
                Assert.True(producer.Removed);
                Assert.NotNull(producer.RemovedDate);
                Assert.Equal(new DateTime(2016, 10, 1), producer.RemovedDate);
            }
        }

        public async Task CreateRegisteredProducer_ProducerIsNotRemoved()
        {
            using (DatabaseWrapper database = new DatabaseWrapper())
            {
                var context = database.WeeeContext;

                var organisation = Organisation.CreateSoleTrader("My trading name");
                context.Organisations.Add(organisation);
                await context.SaveChangesAsync();

                var scheme = new Scheme(organisation.Id);
                context.Schemes.Add(scheme);
                await context.SaveChangesAsync();

                var producer = new RegisteredProducer("ABC12345", 2017, scheme);
                context.AllRegisteredProducers.Add(producer);
                await context.SaveChangesAsync();

                producer = context.RegisteredProducers
                    .SingleOrDefault(p => p.Id == producer.Id);

                Assert.NotNull(producer);
                Assert.False(producer.Removed);
                Assert.Null(producer.RemovedDate);
            }
        }

        public async void FetchSchemeInfo_WithValidOrganisationId_ReturnsScheme()
        {
            using (var database = new DatabaseWrapper())
            {
                // Arrange
                Guid organisationId = Guid.NewGuid();
                ModelHelper modelHelper = new ModelHelper(database.Model);

                var scheme = modelHelper.CreateScheme();
                scheme.ApprovalNumber = "1234";
                scheme.Organisation.Id = organisationId;
                scheme.Organisation.TradingName = "TradingName";

                database.Model.SaveChanges();

                ProducerListFactoryDataAccess dataAccess = new ProducerListFactoryDataAccess(database.WeeeContext);

                // Act
                var result = await dataAccess.FetchSchemeInfo(organisationId);

                // Assert
                Assert.Equal("1234", result.First().ApprovalNumber);
                Assert.Equal("TradingName", result.First().TradingName);
            }
        }

        public async Task Fetch_ReturnsDataForSpecifiedComplianceYearOnly()
        {
            using (DatabaseWrapper database = new DatabaseWrapper())
            {
                // Arrange
                ModelHelper helper = new ModelHelper(database.Model);

                var scheme = helper.CreateScheme();
                var memberUpload1 = helper.CreateSubmittedMemberUpload(scheme);
                memberUpload1.ComplianceYear = 2016;

                var producer1 = helper.CreateProducerAsCompany(memberUpload1, "AAA");

                var memberUpload2 = helper.CreateSubmittedMemberUpload(scheme);
                memberUpload2.ComplianceYear = 2017;
                var producer2 = helper.CreateProducerAsCompany(memberUpload2, "AAA");

                database.Model.SaveChanges();

                var dataAccess = new GetProducerDetailsDataAccess(database.WeeeContext);

                // Act
                var result = await dataAccess.Fetch("AAA", 2016);

                // Assert
                Assert.Single(result);
                Assert.Equal(2016, result.Single().RegisteredProducer.ComplianceYear);
            }
        }

        public async void GetOrganisationUsers_SameUserRejectedMultipleTimesForOneOrganisation_ButAlsoHasAnotherStatus_ReturnsTheOtherStatus(Core.Shared.UserStatus status)
        {
            using (var dbWrapper = new DatabaseWrapper())
            {
                // Add AspNet user user
                var modelHelper = new ModelHelper(dbWrapper.Model);
                var aspNetUser = modelHelper.CreateUser("My username", IdType.Guid);
                dbWrapper.Model.SaveChanges();

                // Add organisation
                var uniqueOrgName = "Test Org " + Guid.NewGuid();
                var country = dbWrapper.WeeeContext.Countries.First();
                var organisation = Domain.Organisation.Organisation.CreateSoleTrader(uniqueOrgName);
                organisation.AddOrUpdateMainContactPerson(new Domain.Organisation.Contact("First name", "Last name", "Developer"));
                organisation.AddOrUpdateAddress(AddressType.OrganisationAddress, new Domain.Organisation.Address(
                    "Address line 1",
                    string.Empty,
                    "Town",
                    string.Empty,
                    string.Empty,
                    country,
                    "01234567890",
                    "[email protected]"));
                organisation.CompleteRegistration();
                dbWrapper.WeeeContext.Organisations.Add(organisation);
                dbWrapper.WeeeContext.SaveChanges();

                // Add same organisation user multiple times, rejected
                var rejectedOrganisationUsers = new List<Domain.Organisation.OrganisationUser>
                {
                    new Domain.Organisation.OrganisationUser(Guid.Parse(aspNetUser.Id),
                        organisation.Id,
                        UserStatus.Rejected),
                    new Domain.Organisation.OrganisationUser(Guid.Parse(aspNetUser.Id),
                        organisation.Id,
                        UserStatus.Rejected),
                    new Domain.Organisation.OrganisationUser(Guid.Parse(aspNetUser.Id),
                        organisation.Id,
                        UserStatus.Rejected)
                };

                var otherOrganisationUser = new Domain.Organisation.OrganisationUser(Guid.Parse(aspNetUser.Id),
                    organisation.Id,
                    status.ToDomainEnumeration<UserStatus>());

                dbWrapper.WeeeContext.OrganisationUsers.AddRange(rejectedOrganisationUsers);
                dbWrapper.WeeeContext.OrganisationUsers.Add(otherOrganisationUser);
                dbWrapper.WeeeContext.SaveChanges();

                var dataAccess = new FindMatchingUsersDataAccess(dbWrapper.WeeeContext);

                var result = (await dataAccess.GetOrganisationUsers())
                    .Where(ou => ou.OrganisationName == uniqueOrgName);

                Assert.Single(result);

                var organisationUser = result.Single();
                Assert.Equal(status, organisationUser.Status);
            }
        }

        public async Task FetchDataReturnOrDefault_ReturnsDataReturnForSpecifiedYearOnly()
        {
            using (DatabaseWrapper database = new DatabaseWrapper())
            {
                // Arrange
                ModelHelper helper = new ModelHelper(database.Model);
                DomainHelper domainHelper = new DomainHelper(database.WeeeContext);

                var scheme = helper.CreateScheme();
                helper.CreateDataReturnVersion(scheme, 2016, 1, true);

                var dataReturnVersion = helper.CreateDataReturnVersion(scheme, 2017, 1, true);

                database.Model.SaveChanges();

                var dataAccess = new DataReturnVersionBuilderDataAccess(domainHelper.GetScheme(scheme.Id), new Quarter(2017, QuarterType.Q1), database.WeeeContext);

                // Act
                var result = await dataAccess.FetchDataReturnOrDefault();

                // Assert
                Assert.Equal(2017, result.Quarter.Year);
                Assert.Equal(dataReturnVersion.DataReturn.Id, result.Id);
            }
        }

        public async Task Submit_SetsDataReturnVersionAsSubmitted()
        {
            using (DatabaseWrapper database = new DatabaseWrapper())
            {
                // Arrange
                ModelHelper helper = new ModelHelper(database.Model);
                DomainHelper domainHelper = new DomainHelper(database.WeeeContext);

                var scheme = helper.CreateScheme();
                var dataReturnVersion = helper.CreateDataReturnVersion(scheme, 2016, 1, false);

                database.Model.SaveChanges();

                var dbDataReturnVersion = domainHelper.GetDataReturnVersion(dataReturnVersion.Id);
                var dataAccess = new SubmitReturnVersionDataAccess(database.WeeeContext);

                // Act
                await dataAccess.Submit(dbDataReturnVersion);

                // Assert
                Assert.Equal(dataReturnVersion.Id, dbDataReturnVersion.Id);
                Assert.True(dbDataReturnVersion.IsSubmitted);
                Assert.NotNull(dbDataReturnVersion.SubmittedDate);
                Assert.Equal(database.WeeeContext.GetCurrentUser(), dbDataReturnVersion.SubmittingUserId);
                Assert.Equal(dataReturnVersion.Id, dbDataReturnVersion.DataReturn.CurrentVersion.Id);
            }
        }

        public async Task Execute_WithNonSubmittedMemberUpload_IgnoresProducer()
        {
            using (DatabaseWrapper db = new DatabaseWrapper())
            {
                // Arrange
                ModelHelper helper = new ModelHelper(db.Model);

                Scheme scheme1 = helper.CreateScheme();

                MemberUpload memberUpload1 = helper.CreateMemberUpload(scheme1);
                memberUpload1.ComplianceYear = 2016;
                memberUpload1.IsSubmitted = false;

                helper.CreateProducerAsCompany(memberUpload1, "WEE/11AAAA11");

                db.Model.SaveChanges();

                // Act
                List<MembersDetailsCsvData> results = await db.StoredProcedures.SpgCSVDataBySchemeComplianceYearAndAuthorisedAuthority(2016, false, false, scheme1.Id, null);

                // Assert
                Assert.NotNull(results);
                Assert.Equal(0, results.Count);
            }
        }