public async Task <IActionResult> PutRecordLabel(int id, RecordLabel recordLabel)
{
if (id != recordLabel.RecordLabelId)
{
return(BadRequest());
}
_context.Entry(recordLabel).State = EntityState.Modified;
try
{
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!RecordLabelExists(id))
{
return(NotFound());
}
else
{
throw;
}
}
return(NoContent());
}
public TaskResult <bool> DoTask(RecordLabel recordLabel)
{
try
{
var toRemove = _dbContext.RecordLabels.SingleOrDefault(l => l.Id == recordLabel.Id);
if (toRemove == null)
{
return(new TaskResult <bool>(false));
}
var addressId = toRemove.AddressId;
_dbContext.RecordLabels.Remove(toRemove);
_dbContext.SaveChanges();
if (addressId.HasValue)
{
var addressToRemove = _dbContext.Addresses.SingleOrDefault(a => a.Id == addressId.Value);
if (addressToRemove != null)
{
_dbContext.Addresses.Remove(addressToRemove);
_dbContext.SaveChanges();
}
}
return(new TaskResult <bool>(true));
}
catch (Exception e)
{
return(new TaskResult <bool>(new TaskException(e)));
}
}
public IActionResult AddRecordLabel(RecordLabel recordLabel)
{
try
{
if (!ClientKeyIsValid())
{
return(Unauthorized());
}
if (!UserIsAuthenticatedAndAuthorized(MethodBase.GetCurrentMethod()))
{
return(Unauthorized());
}
var taskResults = _addRecordLabelTask.DoTask(recordLabel);
return(taskResults.Success ?
Json(taskResults) :
Error(taskResults.Exception));
}
catch (Exception e)
{
return(Error(e));
}
}
public TaskResult <int?> DoTask(RecordLabel recordLabel)
{
try
{
var address = recordLabel.Address;
var countryId = address.Country?.Id ?? address.CountryId;
var country = _dbContext.Countries.SingleOrDefault(c => c.Id == countryId);
address.Country = country;
_dbContext.Addresses.Add(address);
_dbContext.SaveChanges();
recordLabel.Address = null;
recordLabel.AddressId = address.Id;
recordLabel.TaxId = _formattingService.FormatTaxId(recordLabel.TaxId);
recordLabel.Phone = _formattingService.FormatPhoneNumber(recordLabel.Phone);
_dbContext.RecordLabels.Add(recordLabel);
_dbContext.SaveChanges();
recordLabel.Address = address;
return(new TaskResult <int?>(recordLabel.Id));
}
catch (Exception e)
{
return(new TaskResult <int?>(new TaskException(e)));
}
}
public async Task <ActionResult <RecordLabel> > PostRecordLabel(RecordLabel recordLabel)
{
_context.RecordLabels.Add(recordLabel);
await _context.SaveChangesAsync();
return(CreatedAtAction("GetRecordLabel", new { id = recordLabel.RecordLabelId }, recordLabel));
}
public void UpdateRecordLabelModel(RecordLabel recordLabel)
{
var stamp = DateTime.Now.Ticks;
recordLabel.Name = "Update " + stamp;
recordLabel.TaxId = stamp.ToString();
recordLabel.Email = $"test@update{stamp}.com";
recordLabel.Phone = TestsModel.PhoneNumber;
recordLabel.Address = TestsModel.Address;
}
public async Task <IActionResult> OnGetAsync(int?id)
{
if (id == null)
{
return(NotFound());
}
RecordLabel = await _context.RecordLabel.FirstOrDefaultAsync(m => m.ID == id);
if (RecordLabel == null)
{
return(NotFound());
}
return(Page());
}
public async Task <IActionResult> OnPostAsync(int?id)
{
if (id == null)
{
return(NotFound());
}
RecordLabel = await _context.RecordLabel.FindAsync(id);
if (RecordLabel != null)
{
_context.RecordLabel.Remove(RecordLabel);
await _context.SaveChangesAsync();
}
return(RedirectToPage("./Index"));
}
public TaskResult <Nothing> DoTask(RecordLabel update)
{
try
{
var recordLabel = _dbContext.RecordLabels.Where(l => l.Id == update.Id)
.Include(p => p.Address)
.SingleOrDefault();
if (recordLabel == null)
{
throw new TaskException(SystemMessage("RECORD_LABEL_NOT_FOUND"));
}
recordLabel.Name = update.Name;
recordLabel.TaxId = _formattingService.FormatTaxId(update.TaxId);
recordLabel.Email = update.Email;
recordLabel.Phone = _formattingService.FormatPhoneNumber(update.Phone);
recordLabel.Address.Street = update.Address.Street;
recordLabel.Address.City = update.Address.City;
recordLabel.Address.Region = update.Address.Region;
recordLabel.Address.PostalCode = update.Address.PostalCode;
recordLabel.Address.CountryId = update.Address.Country?.Id;
if (recordLabel.Address.CountryId.HasValue)
{
var country = _dbContext.Countries.SingleOrDefault(c => c.Id == recordLabel.Address.CountryId);
recordLabel.Address.Country = country ?? throw new TaskException(SystemMessage("COUNTRY_NOT_FOUND"));
}
_dbContext.SaveChanges();
return(new TaskResult <Nothing>(true));
}
catch (Exception e)
{
return(new TaskResult <Nothing>(new TaskException(e)));
}
}
public IActionResult UpdateRecordLabel(int id, RecordLabel recordLabel)
{
try
{
if (!ClientKeyIsValid())
{
return(Unauthorized());
}
if (!UserIsAuthenticatedAndAuthorized(MethodBase.GetCurrentMethod()))
{
return(Unauthorized());
}
if (AuthenticatedUser.Type == UserType.LabelAdministrator && AuthenticatedUser.RecordLabel?.Id != id)
{
return(Unauthorized());
}
var invalidRecordLabelPathResult = InvalidRecordLabelPathResult(id);
if (invalidRecordLabelPathResult != null)
{
return(invalidRecordLabelPathResult);
}
recordLabel.Id = id;
var taskResults = _updateRecordLabelTask.DoTask(recordLabel);
return(taskResults.Success ?
Ok() :
Error(taskResults.Exception));
}
catch (Exception e)
{
return(Error(e));
}
}
public static void Initialize(IServiceProvider serviceProvider)
{
var context = serviceProvider.GetRequiredService <MBoxDbContext>();
context.Database.EnsureCreated();
if (context.Database.GetPendingMigrations().Any())
{
context.Database.Migrate();
}
if (!context.ApplicationUsers.Any())
{
var admin = new ApplicationUser();
var passAdmin = new PasswordHasher <ApplicationUser>();
var hashedAdmin = passAdmin.HashPassword(admin, "admin!23");
admin.Id = (int)Role.SuperAdmin;
admin.Email = "*****@*****.**";
admin.Name = "Super Admin";
admin.IsActivated = true;
admin.NormalizedEmail = admin.Email.ToUpper();
admin.NormalizedUserName = admin.Email.ToUpper();
admin.SecurityStamp = "be0aefcf-bf66-40b7-a3a6-d42f58ef0beb";
admin.LockoutEnabled = true;
admin.UserName = admin.Email;
admin.PasswordHash = hashedAdmin;
admin.CreatedBy = (int)Role.SuperAdmin;
admin.EmailConfirmed = true;
context.ApplicationUsers.Add(admin);
context.UserRoles.Add(new IdentityUserRole <int>()
{
UserId = admin.Id, RoleId = 1
});
}
if (!context.Roles.Any())
{
context.ApplicationRoles.Add(new ApplicationRole()
{
CreatedBy = (int)Role.SuperAdmin, Type = Role.SuperAdmin, Id = (int)Role.SuperAdmin, Name = Role.SuperAdmin.ToString(), NormalizedName = Role.SuperAdmin.ToString().ToUpper()
});
context.ApplicationRoles.Add(new ApplicationRole()
{
CreatedBy = (int)Role.SuperAdmin, Type = Role.RecordLabel, Id = (int)Role.RecordLabel, Name = Role.RecordLabel.ToString(), NormalizedName = Role.RecordLabel.ToString().ToUpper()
});
context.ApplicationRoles.Add(new ApplicationRole()
{
CreatedBy = (int)Role.SuperAdmin, Type = Role.Artist, Id = (int)Role.Artist, Name = Role.Artist.ToString(), NormalizedName = Role.Artist.ToString().ToUpper()
});
context.ApplicationRoles.Add(new ApplicationRole()
{
CreatedBy = (int)Role.SuperAdmin, Type = Role.Listener, Id = (int)Role.Listener, Name = Role.Listener.ToString(), NormalizedName = Role.Listener.ToString().ToUpper()
});
}
if (!context.EmailTemplates.Any())
{
context.EmailTemplates.Add(new EmailTemplate()
{
CreatedBy = (int)Role.SuperAdmin, Id = (int)EmailTemplateType.SignUp, Type = EmailTemplateType.SignUp, Name = "SignUp", Subject = "Account Activation", Body = "Dear [Name], <Br> Your M Box account has been created! <br> [Link] <br><br>Regards, <br> M Box", LinkText = "Click here to set your password your account."
});
context.EmailTemplates.Add(new EmailTemplate()
{
CreatedBy = (int)Role.SuperAdmin, Id = (int)EmailTemplateType.ForgotPassword, Type = EmailTemplateType.ForgotPassword, Name = "ForgotPassword", Subject = "Forgot Password", Body = " Dear [Name], <br>You have requested a password change on M Box. <br>[Link]<br><br>If you did not request this change, please contact support in the About tab on M Box. <br><br>Regards, <br>M Box", LinkText = "Click here to change your password."
});
context.EmailTemplates.Add(new EmailTemplate()
{
CreatedBy = (int)Role.SuperAdmin, Id = (int)EmailTemplateType.DeletedRecordLabel, Type = EmailTemplateType.DeletedRecordLabel, Name = "DeletedRecordLabel", Subject = "M Box Account Terminated", Body = "Dear [Name], <br>Your M Box account has been terminated.<br> For more information, please contact us at [MBoxMail]<br><br>Regards,<br>M Box "
});
context.EmailTemplates.Add(new EmailTemplate()
{
CreatedBy = (int)Role.SuperAdmin, Id = (int)EmailTemplateType.DeletedArtist, Type = EmailTemplateType.DeletedArtist, Name = "DeletedArtist", Subject = "M Box Account Terminated", Body = "Dear [Name], <br>Your record label has been removed. <br>Your page will remain as is, but you will not be able to post new songs until you join a record label that cooperates with us. <br><br>Regards, <br>M Box"
});
context.EmailTemplates.Add(new EmailTemplate()
{
CreatedBy = (int)Role.SuperAdmin, Id = (int)EmailTemplateType.InvitedRecordLabel, Type = EmailTemplateType.InvitedRecordLabel, Name = "InvitedRecordLabel", Subject = "Create Your M Box Account", Body = "Dear [Name],<br>Your M Box account has been created! <br> [Link]<br><br>Regards, <br>M Box", LinkText = "Click here to get started"
});
context.EmailTemplates.Add(new EmailTemplate()
{
CreatedBy = (int)Role.SuperAdmin, Id = (int)EmailTemplateType.InvitedArtist, Type = EmailTemplateType.InvitedArtist, Name = "InvitedArtist", Subject = "Create Your M Box Account", Body = "Dear [Name], <br>Your Record Label has invited you to join M Box. <br> M Box is a page where you can customize a page with all your music.<br> [Link]<br><br>Regards, <br>M Box", LinkText = "Click here to get started"
});
context.EmailTemplates.Add(new EmailTemplate()
{
CreatedBy = (int)Role.SuperAdmin, Id = (int)EmailTemplateType.ContactForm, Type = EmailTemplateType.ContactForm, Name = "ContactForm", Subject = "M Box About Page Mail"
});
}
if (!context.Songs.Any())
{
for (int i = 0; i < 150; i++)
{
var song = new Song()
{
AlbumName = "DAMN",
CreatedBy = 430,
ArtistId = 430,
Genre = "HipHop",
ReleaseDate = DateTime.UtcNow,
VimeoLink = @"https://vimeo.com/252716264",
YouTubeLink = @"https://www.youtube.com/watch?v=1MGUetRPp_Y",
Name = "TestSongTest" + i
};
context.Songs.Add(song);
}
}
if (context.RecordLabels.Count() == 1)
{
var passRL = new PasswordHasher <ApplicationUser>();
for (int i = 0; i < 150; i++)
{
var tempUser = new ApplicationUser();
var tempRl = new RecordLabel();
var tempPass = new PasswordHasher <ApplicationUser>();
var hashedTempPass = passRL.HashPassword(tempUser, "qweqwe123");
tempUser.Email = "TESTRL" + i + "@gmail.com";
tempUser.Name = "TestRecordLabelTest" + i;
tempUser.IsActivated = true;
tempUser.PasswordHash = hashedTempPass;
tempUser.NormalizedUserName = "******" + i + "@GMAIL.COM";
tempUser.NormalizedEmail = "TESTRL" + i + "@GMAIL.COM";
tempUser.SecurityStamp = "415bf8f4-bc79-4ec2-8368-cf9bdd755db1";
tempUser.UserName = "******" + i + "@gmail.com";
tempUser.LockoutEnabled = true;
tempRl.User = tempUser;
context.ApplicationUsers.Add(tempUser);
context.SaveChanges();
var tempReturned = context.ApplicationUsers.FirstOrDefault(c => c.Id == tempUser.Id);
tempRl.User = tempReturned;
context.RecordLabels.Add(tempRl);
context.UserRoles.Add(new IdentityUserRole <int>()
{
UserId = tempUser.Id, RoleId = 2
});
}
}
if (context.Artists.Count() == 1)
{
var passRL = new PasswordHasher <ApplicationUser>();
for (int i = 0; i < 150; i++)
{
var tempUser = new ApplicationUser();
var tempArtist = new Artist();
var tempPass = new PasswordHasher <ApplicationUser>();
var hashedTempPass = passRL.HashPassword(tempUser, "qweqwe123");
tempUser.Email = "artist" + i + "@gmail.com";
tempUser.Name = "TestArtistTest" + i;
tempUser.IsActivated = true;
tempUser.PasswordHash = hashedTempPass;
tempUser.NormalizedUserName = "******" + i + "@GMAIL.COM";
tempUser.NormalizedEmail = "ARTIST" + i + "@GMAIL.COM";
tempUser.SecurityStamp = "415bf8f4-bc79-4ec2-8368-cf9bdd755db1";
tempUser.UserName = "******" + i + "@gmail.com";
tempUser.LockoutEnabled = true;
tempArtist.User = tempUser;
context.ApplicationUsers.Add(tempUser);
context.SaveChanges();
var tempReturned = context.ApplicationUsers.FirstOrDefault(c => c.Id == tempUser.Id);
tempArtist.User = tempReturned;
context.Artists.Add(tempArtist);
context.UserRoles.Add(new IdentityUserRole <int>()
{
UserId = tempUser.Id, RoleId = (int)Role.Artist
});
}
}
context.SaveChanges();
}
public static void Main(String[] args)
{
Storage storage = StorageFactory.Instance.CreateStorage();
storage.Open("testcodegenerator.dbs");
Database db = new Database(storage);
DateTime start = DateTime.Now;
for (int i = 0; i < nLabels; i++)
{
RecordLabel label = new RecordLabel();
label.name = "Label" + i;
label.email = "contact@" + label.name + ".com";
label.address = "Country, City, Street";
label.phone = "+1 123-456-7890";
db.AddRecord(label);
}
for (int i = 0; i < nAlbums; i++)
{
Album album = new Album();
album.name = "Album" + i;
album.label = (RecordLabel)Enumerable.First(db.Select(typeof(RecordLabel), "name='Label" + (i % nLabels) + "'"));
album.genre = "Rock";
album.release = DateTime.Now;
db.AddRecord(album);
for (int j = 0; j < nTracksPerAlbum; j++)
{
Track track = new Track();
track.no = j + 1;
track.name = "Track" + j;
track.album = album;
track.duration = 3.5f;
db.AddRecord(track);
}
}
Console.WriteLine("Elapsed time for database initialization: " + (DateTime.Now - start));
QueryExecutionListener listener = new QueryExecutionListener();
storage.Listener = listener;
Query trackQuery = db.CreateQuery(typeof(Track));
CodeGenerator code = trackQuery.GetCodeGenerator();
code.Predicate(code.And(code.Gt(code.Field("no"),
code.Literal(0)),
code.Eq(code.Field(code.Field(code.Field("album"), "label"), "name"),
code.Parameter(1, typeof(string)))));
start = DateTime.Now;
int nTracks = 0;
for (int i = 0; i < nLabels; i++)
{
trackQuery[1] = "Label" + i;
foreach (Track t in trackQuery)
{
nTracks += 1;
}
}
Console.WriteLine("Elapsed time for searching of " + nTracks + " tracks: " + (DateTime.Now - start));
Debug.Assert(nTracks == nAlbums * nTracksPerAlbum);
String prev = "";
int n = 0;
Query labelQuery = db.CreateQuery(typeof(RecordLabel));
code = labelQuery.GetCodeGenerator();
code.OrderBy("name");
foreach (RecordLabel label in labelQuery)
{
Debug.Assert(prev.CompareTo(label.name) < 0);
prev = label.name;
n += 1;
}
Debug.Assert(n == nLabels);
prev = "";
n = 0;
code = labelQuery.GetCodeGenerator();
code.Predicate(code.Like(code.Field("name"),
code.Literal("Label%")));
code.OrderBy("name");
foreach (RecordLabel label in labelQuery)
{
Debug.Assert(prev.CompareTo(label.name) < 0);
prev = label.name;
n += 1;
}
Debug.Assert(n == nLabels);
n = 0;
code = labelQuery.GetCodeGenerator();
code.Predicate(code.In(code.Field("name"),
code.List(code.Literal("Label1"), code.Literal("Label2"), code.Literal("Label3"))));
foreach (RecordLabel label in labelQuery)
{
n += 1;
}
Debug.Assert(n == 3);
n = 0;
code = labelQuery.GetCodeGenerator();
code.Predicate(code.And(code.Or(code.Eq(code.Field("name"),
code.Literal("Label1")),
code.Or(code.Eq(code.Field("name"),
code.Literal("Label2")),
code.Eq(code.Field("name"),
code.Literal("Label3")))),
code.Like(code.Field("email"),
code.Literal("contact@%"))));
foreach (RecordLabel label in labelQuery)
{
n += 1;
}
Debug.Assert(n == 3);
code = labelQuery.GetCodeGenerator();
code.Predicate(code.And(code.Like(code.Field("phone"),
code.Literal("+1%")),
code.In(code.Field("name"),
code.Parameter(1, typeof(ArrayList)))));
ArrayList list = new ArrayList(nLabels);
for (int i = 0; i < nLabels; i++)
{
list.Add("Label" + i);
}
n = 0;
labelQuery[1] = list;
foreach (RecordLabel label in labelQuery)
{
Debug.Assert(label.name == "Label" + n++);
}
Debug.Assert(n == nLabels);
n = 0;
code = trackQuery.GetCodeGenerator();
code.Predicate(code.Or(code.Eq(code.Field(code.Field(code.Field("album"), "label"), "name"),
code.Literal("Label1")),
code.Eq(code.Field(code.Field(code.Field("album"), "label"), "name"),
code.Literal("Label2"))));
foreach (Track track in trackQuery)
{
Debug.Assert(track.album.label.name == "Label1" || track.album.label.name == "Label2");
n += 1;
}
Debug.Assert(n == nAlbums * nTracksPerAlbum * 2 / nLabels);
Debug.Assert(listener.nSequentialSearches == 0);
Debug.Assert(listener.nSorts == 0);
db.DropTable(typeof(Track));
db.DropTable(typeof(Album));
db.DropTable(typeof(RecordLabel));
storage.Close();
}
public static void Main(String[] args)
{
Storage storage = StorageFactory.Instance.CreateStorage();
storage.Open("testautoindices.dbs");
Database db = new Database(storage);
db.EnableAutoIndices = true;
DateTime start = DateTime.Now;
for (int i = 0; i < nLabels; i++)
{
RecordLabel label = new RecordLabel();
label.name = "Label" + i;
label.email = "contact@" + label.name + ".com";
label.address = "Country, City, Street";
label.phone = "+1 123-456-7890";
db.AddRecord(label);
}
for (int i = 0; i < nAlbums; i++)
{
Album album = new Album();
album.name = "Album" + i;
album.label = (RecordLabel)Enumerable.First(db.Select(typeof(RecordLabel), "name='Label" + (i % nLabels) + "'"));
album.genre = "Rock";
album.release = DateTime.Now;
db.AddRecord(album);
for (int j = 0; j < nTracksPerAlbum; j++)
{
Track track = new Track();
track.no = j+1;
track.name = "Track" + j;
track.album = album;
track.duration = 3.5f;
db.AddRecord(track);
}
}
Console.WriteLine("Elapsed time for database initialization: " + (DateTime.Now - start));
QueryExecutionListener listener = new QueryExecutionListener();
storage.Listener = listener;
Query query = db.Prepare(typeof(Track), "album.label.name=?");
start = DateTime.Now;
int nTracks = 0;
for (int i = 0; i < nLabels; i++)
{
query[1] = "Label" + i;
foreach (Track t in query)
{
nTracks += 1;
}
}
Console.WriteLine("Elapsed time for searching of " + nTracks + " tracks: " + (DateTime.Now - start));
Debug.Assert(nTracks == nAlbums*nTracksPerAlbum);
String prev = "";
int n = 0;
foreach (RecordLabel label in db.Select(typeof(RecordLabel), "order by name"))
{
Debug.Assert(prev.CompareTo(label.name) < 0);
prev = label.name;
n += 1;
}
Debug.Assert(n == nLabels);
prev = "";
n = 0;
foreach (RecordLabel label in db.Select(typeof(RecordLabel), "name like 'Label%' order by name"))
{
Debug.Assert(prev.CompareTo(label.name) < 0);
prev = label.name;
n += 1;
}
Debug.Assert(n == nLabels);
n = 0;
foreach (RecordLabel label in db.Select(typeof(RecordLabel), "name in ('Label1', 'Label2', 'Label3')"))
{
n += 1;
}
Debug.Assert(n == 3);
n = 0;
foreach (RecordLabel label in db.Select(typeof(RecordLabel), "(name = 'Label1' or name = 'Label2' or name = 'Label3') and email like 'contact@%'"))
{
n += 1;
}
Debug.Assert(n == 3);
Query query2 = db.Prepare(typeof(RecordLabel), "phone like '+1%' and name in ?");
ArrayList list = new ArrayList(nLabels);
for (int i = 0; i < nLabels; i++)
{
list.Add("Label" + i);
}
n = 0;
query2[1] = list;
foreach (RecordLabel label in query2)
{
Debug.Assert(label.name == "Label" + n++);
}
Debug.Assert(n == nLabels);
n = 0;
foreach (Track track in db.Select(typeof(Track), "album.label.name='Label1' or album.label.name='Label2'"))
{
Debug.Assert(track.album.label.name == "Label1" || track.album.label.name == "Label2");
n += 1;
}
Debug.Assert(n == nAlbums*nTracksPerAlbum*2/nLabels);
Debug.Assert(listener.nSequentialSearches == 0);
Debug.Assert(listener.nSorts == 0);
db.DropTable(typeof(Track));
db.DropTable(typeof(Album));
db.DropTable(typeof(RecordLabel));
storage.Close();
}
public static void Main(String[] args)
{
Storage storage = StorageFactory.Instance.CreateStorage();
storage.Open("testjsqljoin.dbs");
Database db = new Database(storage);
DateTime start = DateTime.Now;
for (int i = 0; i < nLabels; i++)
{
RecordLabel label = new RecordLabel();
label.name = "Label" + i;
label.email = "contact@" + label.name + ".com";
label.address = "Country, City, Street";
label.phone = "+1 123-456-7890";
db.AddRecord(label);
}
for (int i = 0; i < nAlbums; i++)
{
Album album = new Album();
album.name = "Album" + i;
album.label = (RecordLabel)Enumerable.First(db.Select(typeof(RecordLabel), "name='Label" + (i % nLabels) + "'"));
album.genre = GENRES[i % GENRES.Length].ToLower();
album.release = DateTime.Now;
db.AddRecord(album);
for (int j = 0; j < nTracksPerAlbum; j++)
{
Track track = new Track();
track.no = j + 1;
track.name = "Track" + j;
track.album = album;
track.duration = 3.5f;
db.AddRecord(track);
}
}
Console.WriteLine("Elapsed time for database initialization: " + (DateTime.Now - start));
QueryExecutionListener listener = new QueryExecutionListener();
storage.Listener = listener;
Query query = db.Prepare(typeof(Track), "album.label.name=?");
start = DateTime.Now;
int nTracks = 0;
for (int i = 0; i < nLabels; i++)
{
query[1] = "Label" + i;
foreach (Track t in query)
{
nTracks += 1;
}
}
Console.WriteLine("Elapsed time for searching of " + nTracks + " tracks: " + (DateTime.Now - start));
Debug.Assert(nTracks == nAlbums * nTracksPerAlbum);
String prev = "";
int n = 0;
foreach (RecordLabel label in db.Select(typeof(RecordLabel), "order by name"))
{
Debug.Assert(prev.CompareTo(label.name) < 0);
prev = label.name;
n += 1;
}
Debug.Assert(n == nLabels);
prev = "zzz";
n = 0;
foreach (RecordLabel label in db.Select(typeof(RecordLabel), "name like 'Label%' order by name desc"))
{
Debug.Assert(prev.CompareTo(label.name) > 0);
prev = label.name;
n += 1;
}
Debug.Assert(n == nLabels);
n = 0;
foreach (RecordLabel label in db.Select(typeof(RecordLabel), "name in ('Label1', 'Label2', 'Label3')"))
{
n += 1;
}
Debug.Assert(n == 3);
n = 0;
foreach (RecordLabel label in db.Select(typeof(RecordLabel), "(name = 'Label1' or name = 'Label2' or name = 'Label3') and email like 'contact@%'"))
{
n += 1;
}
Debug.Assert(n == 3);
Query query2 = db.Prepare(typeof(RecordLabel), "phone like '+1%' and name in ?");
ArrayList list = new ArrayList(nLabels);
for (int i = 0; i < nLabels; i++)
{
list.Add("Label" + i);
}
n = 0;
query2[1] = list;
foreach (RecordLabel label in query2)
{
Debug.Assert(label.name == "Label" + n++);
}
Debug.Assert(n == nLabels);
n = 0;
string labelName = "Label2";
foreach (Track track in db.Select(typeof(Track), "album.label.name='Label1' or album.label.name='Label2' order by album.label.name desc"))
{
Debug.Assert(track.album.label.name == labelName || track.album.label.name == (labelName = "Label1"));
n += 1;
}
Debug.Assert(n == nAlbums * nTracksPerAlbum * 2 / nLabels);
Query query3 = db.Prepare(typeof(Album), "label=?");
n = 0;
foreach (RecordLabel label in db.GetRecords(typeof(RecordLabel)))
{
query3[1] = label;
foreach (Album album in query3)
{
n += 1;
}
}
Debug.Assert(n == nAlbums);
Query query4 = db.Prepare(typeof(Album), "genre in ?");
query4[1] = GENRES;
n = 0;
foreach (Album album in query4)
{
n += 1;
}
Debug.Assert(n == nAlbums);
Debug.Assert(listener.nSequentialSearches == 0);
Debug.Assert(listener.nSorts == 1);
db.DropTable(typeof(Track));
db.DropTable(typeof(Album));
db.DropTable(typeof(RecordLabel));
storage.Close();
}
public List <Festival> GetFestivals()
{
var label1 = new RecordLabel {
Id = 1, Name = "Record Label1"
};
var label2 = new RecordLabel {
Id = 2, Name = "Record Label2"
};
var label3 = new RecordLabel {
Id = 3, Name = "Record Label3"
};
var band1 = new Band {
Id = 1, Name = "Band A1", Label = label1
};
var band2 = new Band {
Id = 2, Name = "Band A2", Label = label1
};
var band3 = new Band {
Id = 3, Name = "Band B1", Label = label2
};
var band4 = new Band {
Id = 4, Name = "Band B2", Label = label2
};
var band5 = new Band {
Id = 5, Name = "Band C1", Label = label3
};
var band6 = new Band {
Id = 6, Name = "Band C2", Label = label3
};
var f1Bands = new List <Band>();
f1Bands.Add(band1);
f1Bands.Add(band4);
f1Bands.Add(band6);
var f1 = new Festival {
Id = 1, Name = "Omega-NSW"
};
f1.Bands = new List <Band> {
band1, band4, band6
};
var f2 = new Festival {
Id = 2, Name = "Theta-VIC"
};
f2.Bands = new List <Band> {
band2, band5
};
var f3 = new Festival {
Id = 3, Name = "Alpha-QLD"
};
f3.Bands = new List <Band> {
band3, band6
};
var f4 = new Festival {
Id = 4, Name = "Beta-TAS"
};
f4.Bands = new List <Band> {
band3, band5, band6
};
var festivals = new List <Festival> {
f1, f2, f3, f4
};
return(festivals);
}
public static void Main(String[] args)
{
Storage storage = StorageFactory.Instance.CreateStorage();
storage.Open("testcodegenerator.dbs");
Database db = new Database(storage);
DateTime start = DateTime.Now;
for (int i = 0; i < nLabels; i++)
{
RecordLabel label = new RecordLabel();
label.name = "Label" + i;
label.email = "contact@" + label.name + ".com";
label.address = "Country, City, Street";
label.phone = "+1 123-456-7890";
db.AddRecord(label);
}
for (int i = 0; i < nAlbums; i++)
{
Album album = new Album();
album.name = "Album" + i;
album.label = (RecordLabel)Enumerable.First(db.Select(typeof(RecordLabel), "name='Label" + (i % nLabels) + "'"));
album.genre = "Rock";
album.release = DateTime.Now;
db.AddRecord(album);
for (int j = 0; j < nTracksPerAlbum; j++)
{
Track track = new Track();
track.no = j+1;
track.name = "Track" + j;
track.album = album;
track.duration = 3.5f;
db.AddRecord(track);
}
}
Console.WriteLine("Elapsed time for database initialization: " + (DateTime.Now - start));
QueryExecutionListener listener = new QueryExecutionListener();
storage.Listener = listener;
Query trackQuery = db.CreateQuery(typeof(Track));
CodeGenerator code = trackQuery.GetCodeGenerator();
code.Predicate(code.And(code.Gt(code.Field("no"),
code.Literal(0)),
code.Eq(code.Field(code.Field(code.Field("album"), "label"), "name"),
code.Parameter(1, typeof(string)))));
start = DateTime.Now;
int nTracks = 0;
for (int i = 0; i < nLabels; i++)
{
trackQuery[1] = "Label" + i;
foreach (Track t in trackQuery)
{
nTracks += 1;
}
}
Console.WriteLine("Elapsed time for searching of " + nTracks + " tracks: " + (DateTime.Now - start));
Debug.Assert(nTracks == nAlbums*nTracksPerAlbum);
String prev = "";
int n = 0;
Query labelQuery = db.CreateQuery(typeof(RecordLabel));
code = labelQuery.GetCodeGenerator();
code.OrderBy("name");
foreach (RecordLabel label in labelQuery)
{
Debug.Assert(prev.CompareTo(label.name) < 0);
prev = label.name;
n += 1;
}
Debug.Assert(n == nLabels);
prev = "";
n = 0;
code = labelQuery.GetCodeGenerator();
code.Predicate(code.Like(code.Field("name"),
code.Literal("Label%")));
code.OrderBy("name");
foreach (RecordLabel label in labelQuery)
{
Debug.Assert(prev.CompareTo(label.name) < 0);
prev = label.name;
n += 1;
}
Debug.Assert(n == nLabels);
n = 0;
code = labelQuery.GetCodeGenerator();
code.Predicate(code.In(code.Field("name"),
code.List(code.Literal("Label1"), code.Literal("Label2"), code.Literal("Label3"))));
foreach (RecordLabel label in labelQuery)
{
n += 1;
}
Debug.Assert(n == 3);
n = 0;
code = labelQuery.GetCodeGenerator();
code.Predicate(code.And(code.Or(code.Eq(code.Field("name"),
code.Literal("Label1")),
code.Or(code.Eq(code.Field("name"),
code.Literal("Label2")),
code.Eq(code.Field("name"),
code.Literal("Label3")))),
code.Like(code.Field("email"),
code.Literal("contact@%"))));
foreach (RecordLabel label in labelQuery)
{
n += 1;
}
Debug.Assert(n == 3);
code = labelQuery.GetCodeGenerator();
code.Predicate(code.And(code.Like(code.Field("phone"),
code.Literal("+1%")),
code.In(code.Field("name"),
code.Parameter(1, typeof(ArrayList)))));
ArrayList list = new ArrayList(nLabels);
for (int i = 0; i < nLabels; i++)
{
list.Add("Label" + i);
}
n = 0;
labelQuery[1] = list;
foreach (RecordLabel label in labelQuery)
{
Debug.Assert(label.name == "Label" + n++);
}
Debug.Assert(n == nLabels);
n = 0;
code = trackQuery.GetCodeGenerator();
code.Predicate(code.Or(code.Eq(code.Field(code.Field(code.Field("album"), "label"), "name"),
code.Literal("Label1")),
code.Eq(code.Field(code.Field(code.Field("album"), "label"), "name"),
code.Literal("Label2"))));
foreach (Track track in trackQuery)
{
Debug.Assert(track.album.label.name == "Label1" || track.album.label.name == "Label2");
n += 1;
}
Debug.Assert(n == nAlbums*nTracksPerAlbum*2/nLabels);
Debug.Assert(listener.nSequentialSearches == 0);
Debug.Assert(listener.nSorts == 0);
db.DropTable(typeof(Track));
db.DropTable(typeof(Album));
db.DropTable(typeof(RecordLabel));
storage.Close();
}
public List <RecordLabel> GetRecords(string bagNo, string funCode)
{
List <RecordLabel> listRecordLabel = null;
try
{
if (bagNo == null)
{
return(null);
}
else
{
List <MOCTA> listMOCTA = null;
List <INVTBProcess> listINVTBProcess = null;
List <PURTG> listPURTG = null;
List <COPTG> listCOPTG = null;
List <COPTI> listCOPTI = null;
List <COPTN> listCOPTN = null;
List <CMSMB> listCMSMB = null;
List <CMSMC> listCMSMC = null;
List <INVTF> listINVTF = null;
List <INVTH> listINVTH = null;
List <INVTA> listINVTA = null;
if (funCode.Equals("1"))
{
listRecordLabel = (from a in _db.Records
join b in _db.ProductInfoes on a.ProductNo equals b.ProductNo
where a.BagNo == bagNo
select new RecordLabel
{
FunCode = a.FunCode,
ProductNo = a.ProductNo,
ProductName = b.ProductName,
Lot = a.Lot,
ExpiryDate = a.ExpiryDate,
WorkDate = a.WorkDate,
WorkTime = a.WorkTime,
OrderType = a.OrderType,
OrderNo = a.OrderNo,
WareHouse = a.WarehouseNo,
CarNo = a.CarNo
}).OrderBy(e => e.WorkDate).ToList();
}
else
{
listRecordLabel = (from a in _db.Records
join b in _db.ProductInfoes on a.ProductNo equals b.ProductNo
where a.BagNo == bagNo
where a.FunCode == funCode
select new RecordLabel
{
FunCode = a.FunCode,
ProductNo = a.ProductNo,
ProductName = b.ProductName,
Lot = a.Lot,
ExpiryDate = a.ExpiryDate,
WorkDate = a.WorkDate,
WorkTime = a.WorkTime,
OrderType = a.OrderType,
OrderNo = a.OrderNo,
WareHouse = a.WarehouseNo,
CarNo = a.CarNo
}).OrderBy(e => e.WorkDate).ToList();
}
foreach (RecordLabel recordLabel in listRecordLabel)
{
listCMSMB = new List <CMSMB>();
listCMSMC = new List <CMSMC>();
if (recordLabel.FunCode.Equals("2"))//包裝
{
}
else if (recordLabel.FunCode.Equals("3"))//出產入庫
{
listMOCTA = _Ldb.MOCTA.Where(e => e.TA001 == recordLabel.OrderType).Where(e => e.TA002 == recordLabel.OrderNo)
//.Where(d => d.TA003 == recordLabel.WorkDate)
.ToList();
if (listMOCTA.Count > 0)
{
recordLabel.Source = listMOCTA[0].TA019;
recordLabel.Purpose = listMOCTA[0].TA020;
//取得廠別名稱
listCMSMB = _Ldb.CMSMB.Where(e => e.MB001 == listMOCTA[0].TA019).ToList();
if (listCMSMB.Count > 0)
{
recordLabel.Source = listCMSMB[0].MB002;
}
//取得入庫庫別名稱
listCMSMC = _Ldb.CMSMC.Where(e => e.MC001 == listMOCTA[0].TA020).ToList();
if (listCMSMC.Count > 0)
{
recordLabel.Purpose = listCMSMC[0].MC002;
}
}
}
else if (recordLabel.FunCode.Equals("4"))//轉撥
{
//listINVTB = _Ldb.INVTB.Where(e => e.TB001 == recordLabel.OrderType).Where(e => e.TB002 == recordLabel.OrderNo).Where(e => e.TB014 == recordLabel.Lot).ToList();
listINVTBProcess = (from a in _Ldb.INVTA
from b in _Ldb.INVTB
where a.TA001 == b.TB001 && a.TA002 == b.TB002
where a.TA001 == recordLabel.OrderType
where a.TA002 == recordLabel.OrderNo
//where a.TA003 == recordLabel.WorkDate
select new INVTBProcess
{
Source = b.TB012,
Purpose = b.TB013,
}).ToList();
if (listINVTBProcess.Count > 0)
{
recordLabel.Source = listINVTBProcess[0].Source;
recordLabel.Purpose = listINVTBProcess[0].Purpose;
//取得轉出庫名稱
listCMSMC = _Ldb.CMSMC.Where(e => e.MC001 == listINVTBProcess[0].Source).ToList();
if (listCMSMC.Count > 0)
{
recordLabel.Source = listCMSMC[0].MC002;
}
listCMSMC.Clear();
//取得轉入庫名稱
listCMSMC = _Ldb.CMSMC.Where(e => e.MC001 == listINVTBProcess[0].Purpose).ToList();
if (listCMSMC.Count > 0)
{
recordLabel.Purpose = listCMSMC[0].MC002;
}
}
}
else if (recordLabel.FunCode.Equals("5"))//進貨
{
listPURTG = _Ldb.PURTG.Where(e => e.TG001 == recordLabel.OrderType).Where(e => e.TG002 == recordLabel.OrderNo)
//.Where(d => d.TG003 == recordLabel.WorkDate)
.ToList();
if (listPURTG.Count > 0)
{
recordLabel.Source = listPURTG[0].TG005;
recordLabel.Purpose = listPURTG[0].TG004;
//取得供應廠商名稱
listCMSMB = _Ldb.CMSMB.Where(e => e.MB001 == listPURTG[0].TG005).ToList();
if (listCMSMB.Count > 0)
{
recordLabel.Source = listCMSMB[0].MB002;
}
listCMSMB.Clear();
//取得廠別名稱
listCMSMB = _Ldb.CMSMB.Where(e => e.MB001 == listPURTG[0].TG004).ToList();
if (listCMSMB.Count > 0)
{
recordLabel.Purpose = listCMSMB[0].MB002;
}
}
}
else if (recordLabel.FunCode.Equals("6"))//銷貨
{
if (recordLabel.OrderType.Substring(1, 2).Equals("23"))
{
listCOPTG = _Ldb.COPTG.Where(e => e.TG001 == recordLabel.OrderType).Where(e => e.TG002 == recordLabel.OrderNo)
//.Where(d => d.TG003 == recordLabel.WorkDate)
.ToList();
if (listCOPTG.Count > 0)
{
recordLabel.Source = listCOPTG[0].TG010;
recordLabel.Purpose = listCOPTG[0].TG007;//客戶名稱
//取得出貨廠別名稱
listCMSMB = _Ldb.CMSMB.Where(e => e.MB001 == listCOPTG[0].TG010).ToList();
if (listCMSMB.Count > 0)
{
recordLabel.Source = listCMSMB[0].MB002;
}
}
}
else if (recordLabel.OrderType.Substring(1, 2).Equals("13"))
{
listINVTF = _Ldb.INVTF.Where(e => e.TF001 == recordLabel.OrderType).Where(e => e.TF002 == recordLabel.OrderNo)
//.Where(d => d.TG003 == recordLabel.WorkDate)
.ToList();
if (listINVTF.Count > 0)
{
recordLabel.Source = listINVTF[0].TF009;
recordLabel.Purpose = listINVTF[0].TF006;//客戶名稱
//取得出貨廠別名稱
listCMSMB = _Ldb.CMSMB.Where(e => e.MB001 == listINVTF[0].TF009).ToList();
if (listCMSMB.Count > 0)
{
recordLabel.Source = listCMSMB[0].MB002;
}
}
}
else if (recordLabel.OrderType.Substring(1, 2).Equals("16"))
{
listINVTH = _Ldb.INVTH.Where(e => e.TH001 == recordLabel.OrderType).Where(e => e.TH002 == recordLabel.OrderNo)
//.Where(d => d.TG003 == recordLabel.WorkDate)
.ToList();
if (listINVTH.Count > 0)
{
recordLabel.Source = listINVTH[0].TH009;
recordLabel.Purpose = listINVTH[0].TH006;//客戶名稱
//取得出貨廠別名稱
listCMSMB = _Ldb.CMSMB.Where(e => e.MB001 == listINVTH[0].TH009).ToList();
if (listCMSMB.Count > 0)
{
recordLabel.Source = listCMSMB[0].MB002;
}
}
}
else if (recordLabel.OrderType.Substring(1, 2).Equals("11"))
{
listINVTA = _Ldb.INVTA.Where(e => e.TA001 == recordLabel.OrderType).Where(e => e.TA002 == recordLabel.OrderNo)
//.Where(d => d.TG003 == recordLabel.WorkDate)
.ToList();
if (listINVTA.Count > 0)
{
recordLabel.Source = listINVTA[0].TA008;
recordLabel.Purpose = listINVTA[0].TA005;//客戶名稱
//取得出貨廠別名稱
listCMSMB = _Ldb.CMSMB.Where(e => e.MB001 == listINVTA[0].TA008).ToList();
if (listCMSMB.Count > 0)
{
recordLabel.Source = listCMSMB[0].MB002;
}
}
}
}
else if (recordLabel.FunCode.Equals("7"))//上車
{
recordLabel.Source = recordLabel.WareHouse;
listCMSMC = _Ldb.CMSMC.Where(x => x.MC001 == recordLabel.WareHouse).ToList();
if (listCMSMC.Count > 0)
{
recordLabel.Source = listCMSMC[0].MC002;
}
recordLabel.Purpose = recordLabel.CarNo;
//listCOPTN = _Ldb.COPTN.Where(e => e.TN001 == recordLabel.OrderType).Where(e => e.TN002 == recordLabel.OrderNo)
// //.Where(d => d.TN003 == recordLabel.WorkDate)
// .ToList();
//if (listCOPTN.Count > 0)
//{
// recordLabel.Source = listCOPTN[0].TN010;
// recordLabel.Purpose = listCOPTN[0].TN005;
// //取得廠別名稱
// listCMSMB = _Ldb.CMSMB.Where(e => e.MB001 == listCOPTN[0].TN010).ToList();
// if (listCMSMB.Count > 0)
// {
// recordLabel.Source = listCMSMB[0].MB002;
// }
//}
}
else if (recordLabel.FunCode.Equals("8"))//下車
{
if (recordLabel.OrderType.Substring(1, 2).Equals("23"))
{
recordLabel.Source = recordLabel.CarNo;
listCOPTG = _Ldb.COPTG.Where(e => e.TG001 == recordLabel.OrderType).Where(e => e.TG002 == recordLabel.OrderNo)
//.Where(d => d.TN003 == recordLabel.WorkDate)
.ToList();
if (listCOPTG.Count > 0)
{
recordLabel.Purpose = listCOPTG[0].TG007;
////取得廠別名稱
//listCMSMB = _Ldb.CMSMB.Where(e => e.MB001 == listCOPTN[0].TN010).ToList();
//if (listCMSMB.Count > 0)
//{
// recordLabel.Source = listCMSMB[0].MB002;
//}
}
}
else if (recordLabel.OrderType.Substring(1, 2).Equals("13"))
{
recordLabel.Source = recordLabel.CarNo;
listINVTF = _Ldb.INVTF.Where(e => e.TF001 == recordLabel.OrderType).Where(e => e.TF002 == recordLabel.OrderNo)
//.Where(d => d.TN003 == recordLabel.WorkDate)
.ToList();
if (listINVTF.Count > 0)
{
recordLabel.Purpose = listINVTF[0].TF006;
////取得廠別名稱
//listCMSMB = _Ldb.CMSMB.Where(e => e.MB001 == listCOPTN[0].TN010).ToList();
//if (listCMSMB.Count > 0)
//{
// recordLabel.Source = listCMSMB[0].MB002;
//}
}
}
else if (recordLabel.OrderType.Substring(1, 2).Equals("16"))
{
recordLabel.Source = recordLabel.CarNo;
listINVTH = _Ldb.INVTH.Where(e => e.TH001 == recordLabel.OrderType).Where(e => e.TH002 == recordLabel.OrderNo)
//.Where(d => d.TN003 == recordLabel.WorkDate)
.ToList();
if (listINVTH.Count > 0)
{
recordLabel.Purpose = listINVTH[0].TH006;
////取得廠別名稱
//listCMSMB = _Ldb.CMSMB.Where(e => e.MB001 == listCOPTN[0].TN010).ToList();
//if (listCMSMB.Count > 0)
//{
// recordLabel.Source = listCMSMB[0].MB002;
//}
}
}
else if (recordLabel.OrderType.Substring(1, 2).Equals("11"))
{
recordLabel.Source = recordLabel.CarNo;
listINVTA = _Ldb.INVTA.Where(e => e.TA001 == recordLabel.OrderType).Where(e => e.TA002 == recordLabel.OrderNo)
//.Where(d => d.TN003 == recordLabel.WorkDate)
.ToList();
if (listINVTA.Count > 0)
{
recordLabel.Purpose = listINVTA[0].TA005;
////取得廠別名稱
//listCMSMB = _Ldb.CMSMB.Where(e => e.MB001 == listCOPTN[0].TN010).ToList();
//if (listCMSMB.Count > 0)
//{
// recordLabel.Source = listCMSMB[0].MB002;
//}
}
}
}
else if (recordLabel.FunCode.Equals("9"))//退貨
{
if (recordLabel.OrderType.Substring(1, 2).Equals("13"))
{
listINVTF = _Ldb.INVTF.Where(e => e.TF001 == recordLabel.OrderType).Where(e => e.TF002 == recordLabel.OrderNo)
//.Where(d => d.TI003 == recordLabel.WorkDate)
.ToList();
if (listINVTF.Count > 0)
{
recordLabel.Source = listINVTF[0].TF006;//客戶名稱
recordLabel.Purpose = listINVTF[0].TF009;
//取得廠別名稱
listCMSMB = _Ldb.CMSMB.Where(e => e.MB001 == listINVTF[0].TF009).ToList();
if (listCMSMB.Count > 0)
{
recordLabel.Purpose = listCMSMB[0].MB002;
}
}
}
else if (recordLabel.OrderType.Substring(1, 2).Equals("16"))
{
listINVTH = _Ldb.INVTH.Where(e => e.TH001 == recordLabel.OrderType).Where(e => e.TH002 == recordLabel.OrderNo)
//.Where(d => d.TI003 == recordLabel.WorkDate)
.ToList();
if (listINVTH.Count > 0)
{
recordLabel.Source = listINVTH[0].TH006;//客戶名稱
recordLabel.Purpose = listINVTH[0].TH009;
//取得廠別名稱
listCMSMB = _Ldb.CMSMB.Where(e => e.MB001 == listINVTH[0].TH009).ToList();
if (listCMSMB.Count > 0)
{
recordLabel.Purpose = listCMSMB[0].MB002;
}
}
}
else
{
listCOPTI = _Ldb.COPTI.Where(e => e.TI001 == recordLabel.OrderType).Where(e => e.TI002 == recordLabel.OrderNo)
//.Where(d => d.TI003 == recordLabel.WorkDate)
.ToList();
if (listCOPTI.Count > 0)
{
recordLabel.Source = listCOPTI[0].TI004;//客戶名稱
recordLabel.Purpose = listCOPTI[0].TI007;
//取得廠別名稱
listCMSMB = _Ldb.CMSMB.Where(e => e.MB001 == listCOPTI[0].TI007).ToList();
if (listCMSMB.Count > 0)
{
recordLabel.Purpose = listCMSMB[0].MB002;
}
}
}
}
}
}
}
catch (Exception ex)
{
RecordLabel exRecordLabel = new RecordLabel();
exRecordLabel.ProductNo = ex.Message;
listRecordLabel.Clear();
listRecordLabel.Add(exRecordLabel);
}
finally
{
}
return(listRecordLabel);
}