From 12c45750173b7ad18213aded76b8c9ec935a94ef Mon Sep 17 00:00:00 2001 From: marcinzelent Date: Sun, 4 Jun 2017 18:44:55 +0200 Subject: Renamed ApartmentChange to just Change, added local database. --- .../ApartmentManager/Model/ApartmentChange.cs | 50 -------- ApartmentManager/ApartmentManager/Model/Change.cs | 50 ++++++++ .../Controllers/ApartmentChangesController.cs | 141 --------------------- .../Controllers/ChangeCommentsController.cs | 41 ++---- .../Controllers/ChangeDocumentsController.cs | 38 ++---- .../HousingWebApi/Controllers/ChangesController.cs | 128 +++++++++++++++++++ ApartmentManager/HousingWebApi/DataModel.cs | 24 ++-- .../HousingWebApi/HousingWebApi.csproj | 10 +- ApartmentManager/HousingWebApi/Models/Apartment.cs | 4 +- .../HousingWebApi/Models/ApartmentChange.cs | 43 ------- .../HousingWebApi/Models/ApartmentResident.cs | 2 +- ApartmentManager/HousingWebApi/Models/Change.cs | 42 ++++++ .../HousingWebApi/Models/ChangeComment.cs | 2 +- .../HousingWebApi/Models/ChangeDocument.cs | 2 +- ApartmentManager/HousingWebApi/Web.config | 3 +- Query.sql | 116 +++++++++++++++++ 16 files changed, 382 insertions(+), 314 deletions(-) delete mode 100644 ApartmentManager/ApartmentManager/Model/ApartmentChange.cs create mode 100644 ApartmentManager/ApartmentManager/Model/Change.cs delete mode 100644 ApartmentManager/HousingWebApi/Controllers/ApartmentChangesController.cs create mode 100644 ApartmentManager/HousingWebApi/Controllers/ChangesController.cs delete mode 100644 ApartmentManager/HousingWebApi/Models/ApartmentChange.cs create mode 100644 ApartmentManager/HousingWebApi/Models/Change.cs create mode 100644 Query.sql diff --git a/ApartmentManager/ApartmentManager/Model/ApartmentChange.cs b/ApartmentManager/ApartmentManager/Model/ApartmentChange.cs deleted file mode 100644 index 608db89..0000000 --- a/ApartmentManager/ApartmentManager/Model/ApartmentChange.cs +++ /dev/null @@ -1,50 +0,0 @@ -using ApartmentManager.Annotations; -using ApartmentManager.Model; -using System; -using System.Collections.ObjectModel; -using System.ComponentModel; -using System.Runtime.CompilerServices; - -namespace ApartmentManager -{ - public class ApartmentChange : INotifyPropertyChanged - { - public int ChangeId { get; set; } - public int ApartmentId { get; set; } - public string Name { get; set; } - public DateTime UploadDate { get; set; } - public string Description { get; set; } - public string Status { get; set; } - private ObservableCollection _documents; - private ObservableCollection _comments; - - public ObservableCollection Documents - { - get => _documents; - set - { - _documents = value; - OnPropertyChanged(nameof(Documents)); - } - } - - public ObservableCollection Comments - { - get => _comments; - set - { - _comments = value; - OnPropertyChanged(nameof(Comments)); - } - } - - public event PropertyChangedEventHandler PropertyChanged; - - [NotifyPropertyChangedInvocator] - protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null) - { - PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName)); - - } - } -} diff --git a/ApartmentManager/ApartmentManager/Model/Change.cs b/ApartmentManager/ApartmentManager/Model/Change.cs new file mode 100644 index 0000000..466d85c --- /dev/null +++ b/ApartmentManager/ApartmentManager/Model/Change.cs @@ -0,0 +1,50 @@ +using ApartmentManager.Annotations; +using ApartmentManager.Model; +using System; +using System.Collections.ObjectModel; +using System.ComponentModel; +using System.Runtime.CompilerServices; + +namespace ApartmentManager +{ + public class Change : INotifyPropertyChanged + { + public int ChangeId { get; set; } + public int ApartmentId { get; set; } + public string Name { get; set; } + public DateTime UploadDate { get; set; } + public string Description { get; set; } + public string Status { get; set; } + private ObservableCollection _documents; + private ObservableCollection _comments; + + public ObservableCollection Documents + { + get => _documents; + set + { + _documents = value; + OnPropertyChanged(nameof(Documents)); + } + } + + public ObservableCollection Comments + { + get => _comments; + set + { + _comments = value; + OnPropertyChanged(nameof(Comments)); + } + } + + public event PropertyChangedEventHandler PropertyChanged; + + [NotifyPropertyChangedInvocator] + protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null) + { + PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName)); + + } + } +} diff --git a/ApartmentManager/HousingWebApi/Controllers/ApartmentChangesController.cs b/ApartmentManager/HousingWebApi/Controllers/ApartmentChangesController.cs deleted file mode 100644 index eadd910..0000000 --- a/ApartmentManager/HousingWebApi/Controllers/ApartmentChangesController.cs +++ /dev/null @@ -1,141 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Data; -using System.Data.Entity; -using System.Data.Entity.Infrastructure; -using System.Linq; -using System.Net; -using System.Net.Http; -using System.Web.Http; -using System.Web.Http.Description; -using HousingWebApi; - -namespace HousingWebApi.Controllers -{ - public class ApartmentChangesController : ApiController - { - private DataModel db = new DataModel(); - - // GET: api/ApartmentChanges - public IQueryable GetApartmentChanges() - { - return db.ApartmentChanges; - } - [Route("api/ApartmentChangesByid/{id}")] - public IQueryable GetApartmentChangesByid(int id) - { - var changetlist = from change in db.ApartmentChanges - where (change.ApartmentId == id) - orderby change.ChangeId descending - select change; - return changetlist; - } - // GET: api/ApartmentChanges/5 - [ResponseType(typeof(ApartmentChange))] - public IHttpActionResult GetApartmentChange(int id) - { - ApartmentChange apartmentChange = db.ApartmentChanges.Find(id); - if (apartmentChange == null) - { - return NotFound(); - } - - return Ok(apartmentChange); - } - - // PUT: api/ApartmentChanges/5 - [ResponseType(typeof(void))] - public IHttpActionResult PutApartmentChange(int id, ApartmentChange apartmentChange) - { - if (!ModelState.IsValid) - { - return BadRequest(ModelState); - } - - if (id != apartmentChange.ChangeId) - { - return BadRequest(); - } - - db.Entry(apartmentChange).State = EntityState.Modified; - - try - { - db.SaveChanges(); - } - catch (DbUpdateConcurrencyException) - { - if (!ApartmentChangeExists(id)) - { - return NotFound(); - } - else - { - throw; - } - } - - return StatusCode(HttpStatusCode.NoContent); - } - - // POST: api/ApartmentChanges - [ResponseType(typeof(ApartmentChange))] - public IHttpActionResult PostApartmentChange(ApartmentChange apartmentChange) - { - if (!ModelState.IsValid) - { - return BadRequest(ModelState); - } - - db.ApartmentChanges.Add(apartmentChange); - - try - { - db.SaveChanges(); - } - catch (DbUpdateException) - { - if (ApartmentChangeExists(apartmentChange.ChangeId)) - { - return Conflict(); - } - else - { - throw; - } - } - - return CreatedAtRoute("DefaultApi", new { id = apartmentChange.ChangeId }, apartmentChange); - } - - // DELETE: api/ApartmentChanges/5 - [ResponseType(typeof(ApartmentChange))] - public IHttpActionResult DeleteApartmentChange(int id) - { - ApartmentChange apartmentChange = db.ApartmentChanges.Find(id); - if (apartmentChange == null) - { - return NotFound(); - } - - db.ApartmentChanges.Remove(apartmentChange); - db.SaveChanges(); - - return Ok(apartmentChange); - } - - protected override void Dispose(bool disposing) - { - if (disposing) - { - db.Dispose(); - } - base.Dispose(disposing); - } - - private bool ApartmentChangeExists(int id) - { - return db.ApartmentChanges.Count(e => e.ChangeId == id) > 0; - } - } -} \ No newline at end of file diff --git a/ApartmentManager/HousingWebApi/Controllers/ChangeCommentsController.cs b/ApartmentManager/HousingWebApi/Controllers/ChangeCommentsController.cs index 6026938..ef90861 100644 --- a/ApartmentManager/HousingWebApi/Controllers/ChangeCommentsController.cs +++ b/ApartmentManager/HousingWebApi/Controllers/ChangeCommentsController.cs @@ -16,18 +16,6 @@ namespace HousingWebApi.Controllers { private DataModel db = new DataModel(); - //GET: api/ChangeCommentsById - [Route("api/ChangeCommentsById/{id}")] - public IQueryable GetChangeCommentsById(int id) - { - var commentsList = from changeComment in db.ChangeComments - where (changeComment.ChangeId == id) - orderby changeComment.CommentId descending - - select changeComment; - return commentsList; - } - // GET: api/ChangeComments public IQueryable GetChangeComments() { @@ -47,6 +35,18 @@ namespace HousingWebApi.Controllers return Ok(changeComment); } + //GET: api/CommentsByChangeId + [Route("api/CommentsByChangeId/{id}")] + public IQueryable GetCommentsByChangeId(int id) + { + var commentsList = from changeComment in db.ChangeComments + where (changeComment.ChangeId == id) + orderby changeComment.CommentId descending + + select changeComment; + return commentsList; + } + // PUT: api/ChangeComments/5 [ResponseType(typeof(void))] public IHttpActionResult PutChangeComment(int id, ChangeComment changeComment) @@ -92,22 +92,7 @@ namespace HousingWebApi.Controllers } db.ChangeComments.Add(changeComment); - - try - { - db.SaveChanges(); - } - catch (DbUpdateException) - { - if (ChangeCommentExists(changeComment.CommentId)) - { - return Conflict(); - } - else - { - throw; - } - } + db.SaveChanges(); return CreatedAtRoute("DefaultApi", new { id = changeComment.CommentId }, changeComment); } diff --git a/ApartmentManager/HousingWebApi/Controllers/ChangeDocumentsController.cs b/ApartmentManager/HousingWebApi/Controllers/ChangeDocumentsController.cs index 0ffaec0..ae5ff81 100644 --- a/ApartmentManager/HousingWebApi/Controllers/ChangeDocumentsController.cs +++ b/ApartmentManager/HousingWebApi/Controllers/ChangeDocumentsController.cs @@ -16,16 +16,6 @@ namespace HousingWebApi.Controllers { private DataModel db = new DataModel(); - //GET: api/ChangeDocumentsById - [Route("api/ChangeDocumentsById/{id}")] - public IQueryable GetChangeDocumentsById(int id) - { - var documentsList = from changeDocument in db.ChangeDocuments - where (changeDocument.ChangeId == id) - select changeDocument; - return documentsList; - } - // GET: api/ChangeDocuments public IQueryable GetChangeDocuments() { @@ -45,6 +35,17 @@ namespace HousingWebApi.Controllers return Ok(changeDocument); } + //GET: api/DocumentsByChangeId + [Route("api/DocumentsByChangeId/{id}")] + public IQueryable GetDocumentsByChangeId(int id) + { + var documentsList = from changeDocument in db.ChangeDocuments + where (changeDocument.ChangeId == id) + select changeDocument; + return documentsList; + } + + // PUT: api/ChangeDocuments/5 [ResponseType(typeof(void))] public IHttpActionResult PutChangeDocument(int id, ChangeDocument changeDocument) @@ -90,22 +91,7 @@ namespace HousingWebApi.Controllers } db.ChangeDocuments.Add(changeDocument); - - try - { - db.SaveChanges(); - } - catch (DbUpdateException) - { - if (ChangeDocumentExists(changeDocument.DocumentId)) - { - return Conflict(); - } - else - { - throw; - } - } + db.SaveChanges(); return CreatedAtRoute("DefaultApi", new { id = changeDocument.DocumentId }, changeDocument); } diff --git a/ApartmentManager/HousingWebApi/Controllers/ChangesController.cs b/ApartmentManager/HousingWebApi/Controllers/ChangesController.cs new file mode 100644 index 0000000..55096b7 --- /dev/null +++ b/ApartmentManager/HousingWebApi/Controllers/ChangesController.cs @@ -0,0 +1,128 @@ +using System; +using System.Collections.Generic; +using System.Data; +using System.Data.Entity; +using System.Data.Entity.Infrastructure; +using System.Linq; +using System.Net; +using System.Net.Http; +using System.Web.Http; +using System.Web.Http.Description; +using HousingWebApi; + +namespace HousingWebApi.Controllers +{ + public class ChangesController : ApiController + { + private DataModel db = new DataModel(); + + // GET: api/Changes + public IQueryable GetChanges() + { + return db.Changes; + } + + // GET: api/Changes/5 + [ResponseType(typeof(Change))] + public IHttpActionResult GetChange(int id) + { + Change change = db.Changes.Find(id); + if (change == null) + { + return NotFound(); + } + + return Ok(change); + } + + [Route("api/ChangesByApartmentId/{id}")] + public IQueryable GetChangesByApartmentId(int id) + { + var changesList = from change in db.Changes + where (change.ApartmentId == id) + orderby change.ChangeId descending + select change; + return changesList; + } + + // PUT: api/Changes/5 + [ResponseType(typeof(void))] + public IHttpActionResult PutChange(int id, Change change) + { + if (!ModelState.IsValid) + { + return BadRequest(ModelState); + } + + if (id != change.ChangeId) + { + return BadRequest(); + } + + db.Entry(change).State = EntityState.Modified; + + try + { + db.SaveChanges(); + } + catch (DbUpdateConcurrencyException) + { + if (!ChangeExists(id)) + { + return NotFound(); + } + else + { + throw; + } + } + + return StatusCode(HttpStatusCode.NoContent); + } + + // POST: api/Changes + [ResponseType(typeof(Change))] + public IHttpActionResult PostChange(Change change) + { + if (!ModelState.IsValid) + { + return BadRequest(ModelState); + } + + db.Changes.Add(change); + db.SaveChanges(); + + return CreatedAtRoute("DefaultApi", new { id = change.ChangeId }, change); + } + + // DELETE: api/Changes/5 + [ResponseType(typeof(Change))] + public IHttpActionResult DeleteChange(int id) + { + Change change = db.Changes.Find(id); + if (change == null) + { + return NotFound(); + } + + db.Changes.Remove(change); + db.SaveChanges(); + + return Ok(change); + } + + protected override void Dispose(bool disposing) + { + if (disposing) + { + db.Dispose(); + } + base.Dispose(disposing); + } + + private bool ChangeExists(int id) + { + return db.Changes.Count(e => e.ChangeId == id) > 0; + } + } +} \ No newline at end of file diff --git a/ApartmentManager/HousingWebApi/DataModel.cs b/ApartmentManager/HousingWebApi/DataModel.cs index aa2721f..2e60842 100644 --- a/ApartmentManager/HousingWebApi/DataModel.cs +++ b/ApartmentManager/HousingWebApi/DataModel.cs @@ -14,7 +14,7 @@ namespace HousingWebApi } public virtual DbSet Apartments { get; set; } - public virtual DbSet ApartmentChanges { get; set; } + public virtual DbSet Changes { get; set; } public virtual DbSet ChangeComments { get; set; } public virtual DbSet ChangeDocuments { get; set; } public virtual DbSet Defects { get; set; } @@ -36,7 +36,7 @@ namespace HousingWebApi .IsUnicode(false); modelBuilder.Entity() - .HasMany(e => e.ApartmentChanges) + .HasMany(e => e.Changes) .WithRequired(e => e.Apartment) .WillCascadeOnDelete(false); @@ -60,28 +60,18 @@ namespace HousingWebApi .WithRequired(e => e.Apartment) .WillCascadeOnDelete(false); - modelBuilder.Entity() + modelBuilder.Entity() .Property(e => e.Name) .IsUnicode(false); - modelBuilder.Entity() + modelBuilder.Entity() .Property(e => e.Description) .IsUnicode(false); - modelBuilder.Entity() + modelBuilder.Entity() .Property(e => e.Status) .IsUnicode(false); - modelBuilder.Entity() - .HasMany(e => e.ChangeComments) - .WithRequired(e => e.ApartmentChange) - .WillCascadeOnDelete(false); - - modelBuilder.Entity() - .HasMany(e => e.ChangeDocuments) - .WithRequired(e => e.ApartmentChange) - .WillCascadeOnDelete(false); - modelBuilder.Entity() .Property(e => e.Comment) .IsUnicode(false); @@ -209,6 +199,10 @@ namespace HousingWebApi modelBuilder.Entity() .Property(e => e.Email) .IsUnicode(false); + + modelBuilder.Entity() + .Property(e => e.Picture) + .IsUnicode(false); } } } diff --git a/ApartmentManager/HousingWebApi/HousingWebApi.csproj b/ApartmentManager/HousingWebApi/HousingWebApi.csproj index 779dd08..b94fb4b 100644 --- a/ApartmentManager/HousingWebApi/HousingWebApi.csproj +++ b/ApartmentManager/HousingWebApi/HousingWebApi.csproj @@ -151,9 +151,13 @@ + + + + - + @@ -189,10 +193,6 @@ - - - - diff --git a/ApartmentManager/HousingWebApi/Models/Apartment.cs b/ApartmentManager/HousingWebApi/Models/Apartment.cs index 9e703bd..c114d09 100644 --- a/ApartmentManager/HousingWebApi/Models/Apartment.cs +++ b/ApartmentManager/HousingWebApi/Models/Apartment.cs @@ -12,7 +12,7 @@ namespace HousingWebApi [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")] public Apartment() { - ApartmentChanges = new HashSet(); + Changes = new HashSet(); Defects = new HashSet(); PastUsers = new HashSet(); Residents = new HashSet(); @@ -36,7 +36,7 @@ namespace HousingWebApi public string PlanPicture { get; set; } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] - public virtual ICollection ApartmentChanges { get; set; } + public virtual ICollection Changes { get; set; } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] public virtual ICollection Defects { get; set; } diff --git a/ApartmentManager/HousingWebApi/Models/ApartmentChange.cs b/ApartmentManager/HousingWebApi/Models/ApartmentChange.cs deleted file mode 100644 index a3308f3..0000000 --- a/ApartmentManager/HousingWebApi/Models/ApartmentChange.cs +++ /dev/null @@ -1,43 +0,0 @@ -namespace HousingWebApi -{ - using System; - using System.Collections.Generic; - using System.ComponentModel.DataAnnotations; - using System.ComponentModel.DataAnnotations.Schema; - using System.Data.Entity.Spatial; - - [Table("ApartmentChange")] - public partial class ApartmentChange - { - [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")] - public ApartmentChange() - { - ChangeComments = new HashSet(); - ChangeDocuments = new HashSet(); - } - - [Key] - public int ChangeId { get; set; } - - public int ApartmentId { get; set; } - - [StringLength(50)] - public string Name { get; set; } - - [Column(TypeName = "date")] - public DateTime? UploadDate { get; set; } - - public string Description { get; set; } - - [StringLength(50)] - public string Status { get; set; } - - public virtual Apartment Apartment { get; set; } - - [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] - public virtual ICollection ChangeComments { get; set; } - - [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] - public virtual ICollection ChangeDocuments { get; set; } - } -} diff --git a/ApartmentManager/HousingWebApi/Models/ApartmentResident.cs b/ApartmentManager/HousingWebApi/Models/ApartmentResident.cs index 9936a5e..e4d3fed 100644 --- a/ApartmentManager/HousingWebApi/Models/ApartmentResident.cs +++ b/ApartmentManager/HousingWebApi/Models/ApartmentResident.cs @@ -27,6 +27,6 @@ namespace HousingWebApi [StringLength(50)] public string Email { get; set; } - public byte[] Picture { get; set; } + public string Picture { get; set; } } } diff --git a/ApartmentManager/HousingWebApi/Models/Change.cs b/ApartmentManager/HousingWebApi/Models/Change.cs new file mode 100644 index 0000000..de7eef7 --- /dev/null +++ b/ApartmentManager/HousingWebApi/Models/Change.cs @@ -0,0 +1,42 @@ +namespace HousingWebApi +{ + using System; + using System.Collections.Generic; + using System.ComponentModel.DataAnnotations; + using System.ComponentModel.DataAnnotations.Schema; + using System.Data.Entity.Spatial; + + [Table("Change")] + public partial class Change + { + [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")] + public Change() + { + ChangeComments = new HashSet(); + ChangeDocuments = new HashSet(); + } + + public int ChangeId { get; set; } + + public int ApartmentId { get; set; } + + [StringLength(50)] + public string Name { get; set; } + + [Column(TypeName = "date")] + public DateTime? UploadDate { get; set; } + + public string Description { get; set; } + + [StringLength(50)] + public string Status { get; set; } + + public virtual Apartment Apartment { get; set; } + + [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] + public virtual ICollection ChangeComments { get; set; } + + [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] + public virtual ICollection ChangeDocuments { get; set; } + } +} diff --git a/ApartmentManager/HousingWebApi/Models/ChangeComment.cs b/ApartmentManager/HousingWebApi/Models/ChangeComment.cs index 954d348..83b205f 100644 --- a/ApartmentManager/HousingWebApi/Models/ChangeComment.cs +++ b/ApartmentManager/HousingWebApi/Models/ChangeComment.cs @@ -21,6 +21,6 @@ namespace HousingWebApi public DateTime? Date { get; set; } - public virtual ApartmentChange ApartmentChange { get; set; } + public virtual Change Change { get; set; } } } diff --git a/ApartmentManager/HousingWebApi/Models/ChangeDocument.cs b/ApartmentManager/HousingWebApi/Models/ChangeDocument.cs index 320686f..31b6437 100644 --- a/ApartmentManager/HousingWebApi/Models/ChangeDocument.cs +++ b/ApartmentManager/HousingWebApi/Models/ChangeDocument.cs @@ -17,6 +17,6 @@ namespace HousingWebApi [StringLength(50)] public string Document { get; set; } - public virtual ApartmentChange ApartmentChange { get; set; } + public virtual Change Change { get; set; } } } diff --git a/ApartmentManager/HousingWebApi/Web.config b/ApartmentManager/HousingWebApi/Web.config index d4ce313..e610a4b 100644 --- a/ApartmentManager/HousingWebApi/Web.config +++ b/ApartmentManager/HousingWebApi/Web.config @@ -80,5 +80,6 @@ - + + \ No newline at end of file diff --git a/Query.sql b/Query.sql new file mode 100644 index 0000000..5ca9c02 --- /dev/null +++ b/Query.sql @@ -0,0 +1,116 @@ +CREATE TABLE [dbo].[Apartment] ( + [ApartmentId] INT NOT NULL, + [Size] FLOAT (53) NULL, + [NumberOfRooms] INT NULL, + [MonthlyCharge] FLOAT (53) NULL, + [Floor] INT NULL, + [Address] VARCHAR (100) NULL, + [PlanPicture] VARCHAR (MAX) NULL, + PRIMARY KEY CLUSTERED ([ApartmentId] ASC) +); + +CREATE TABLE [dbo].[Change] ( + [ChangeId] INT IDENTITY (0, 1) NOT NULL, + [ApartmentId] INT NOT NULL, + [Name] VARCHAR (50) NULL, + [UploadDate] DATE NULL, + [Description] VARCHAR (MAX) NULL, + [Status] VARCHAR (50) NULL, + PRIMARY KEY CLUSTERED ([ChangeId] ASC), + CONSTRAINT [FK_Change_ToApartment] FOREIGN KEY ([ApartmentId]) REFERENCES [dbo].[Apartment] ([ApartmentId]) +); + +CREATE TABLE [dbo].[ChangeComment] ( + [CommentId] INT IDENTITY (0, 1) NOT NULL, + [ChangeId] INT NOT NULL, + [Comment] VARCHAR (MAX) NULL, + [Name] VARCHAR (50) NULL, + [Date] DATETIME NULL, + PRIMARY KEY CLUSTERED ([CommentId] ASC), + CONSTRAINT [FK_ChangeComment_ToChange] FOREIGN KEY ([ChangeId]) REFERENCES [dbo].[Change] ([ChangeId]) ON DELETE CASCADE +); + +CREATE TABLE [dbo].[ChangeDocument] ( + [DocumentId] INT IDENTITY (0, 1) NOT NULL, + [ChangeId] INT NOT NULL, + [Document] VARCHAR (50) NULL, + PRIMARY KEY CLUSTERED ([DocumentId] ASC), + CONSTRAINT [FK_ChangeDocument_ToChange] FOREIGN KEY ([ChangeId]) REFERENCES [dbo].[Change] ([ChangeId]) ON DELETE CASCADE +); + +CREATE TABLE [dbo].[Defect] ( + [DefectId] INT IDENTITY (0, 1) NOT NULL, + [ApartmentId] INT NOT NULL, + [Name] VARCHAR (50) NULL, + [UploadDate] DATE NULL, + [Description] VARCHAR (MAX) NULL, + [Status] VARCHAR (50) NULL, + PRIMARY KEY CLUSTERED ([DefectId] ASC), + CONSTRAINT [FK_Defect_ToApartment] FOREIGN KEY ([ApartmentId]) REFERENCES [dbo].[Apartment] ([ApartmentId]) +); + +CREATE TABLE [dbo].[DefectComment] ( + [CommentId] INT IDENTITY (0, 1) NOT NULL, + [DefectId] INT NOT NULL, + [Comment] VARCHAR (MAX) NULL, + [Name] VARCHAR (50) NULL, + [Date] DATETIME NULL, + PRIMARY KEY CLUSTERED ([CommentId] ASC), + CONSTRAINT [FK_DefectComment_ToDefect] FOREIGN KEY ([DefectId]) REFERENCES [dbo].[Defect] ([DefectId]) ON DELETE CASCADE +); + +CREATE TABLE [dbo].[DefectPicture] ( + [PictureId] INT IDENTITY (0, 1) NOT NULL, + [DefectId] INT NOT NULL, + [Picture] VARCHAR (MAX) NULL, + PRIMARY KEY CLUSTERED ([PictureId] ASC), + CONSTRAINT [FK_DefectPicture_ToDefect] FOREIGN KEY ([DefectId]) REFERENCES [dbo].[Defect] ([DefectId]) ON DELETE CASCADE +); + +CREATE TABLE [dbo].[PastUser] ( + [Username] VARCHAR (30) NOT NULL, + [ApartmentId] INT NOT NULL, + [Password] VARCHAR (30) NOT NULL, + [IsBm] BIT NOT NULL, + [FirstName] VARCHAR (30) NULL, + [LastName] VARCHAR (30) NULL, + [BirthDate] DATE NULL, + [Phone] VARCHAR (20) NULL, + [Email] VARCHAR (50) NULL, + [Picture] VARCHAR (50) NULL, + [MoveInDate] DATE NULL, + [MoveOutDate] DATE NULL, + PRIMARY KEY CLUSTERED ([Username] ASC), + CONSTRAINT [FK_PastUser_ToApartment] FOREIGN KEY ([ApartmentId]) REFERENCES [dbo].[Apartment] ([ApartmentId]) +); + +CREATE TABLE [dbo].[Resident] ( + [ResidentId] INT IDENTITY (0, 1) NOT NULL, + [ApartmentId] INT NOT NULL, + [FirstName] VARCHAR (30) NULL, + [LastName] VARCHAR (30) NULL, + [BirthDate] DATE NULL, + [Phone] VARCHAR (20) NULL, + [Email] VARCHAR (50) NULL, + [Picture] VARCHAR (MAX) NULL, + PRIMARY KEY CLUSTERED ([ResidentId] ASC), + CONSTRAINT [FK_Resident_ToApartment] FOREIGN KEY ([ApartmentId]) REFERENCES [dbo].[Apartment] ([ApartmentId]) +); + +CREATE TABLE [dbo].[User] ( + [Username] VARCHAR (30) NOT NULL, + [ApartmentId] INT NOT NULL, + [Password] VARCHAR (30) NOT NULL, + [IsBm] BIT NOT NULL, + [FirstName] VARCHAR (30) NULL, + [LastName] VARCHAR (30) NULL, + [BirthDate] DATE NULL, + [Phone] VARCHAR (20) NULL, + [Email] VARCHAR (50) NULL, + [Picture] VARCHAR (MAX) NULL, + [MoveInDate] DATE NULL, + [MoveOutDate] DATE NULL, + PRIMARY KEY CLUSTERED ([Username] ASC), + CONSTRAINT [FK_User_ToApartment] FOREIGN KEY ([ApartmentId]) REFERENCES [dbo].[Apartment] ([ApartmentId]) +); + -- cgit v1.2.3