public async Task <string> CreateCopyVersionAsync(string classCode, string versionCode, string newVersion, string userId, bool copyRelations)
{
if (await this.IsClassVersionExistAsync(classCode, versionCode) == false)
{
return($"Грешка!!! Няма версия източник с код: {classCode} {versionCode}");
}
if (await this.IsClassVersionExistAsync(classCode, newVersion) == true)
{
return($"Грешка!!! Вече има версия с код: {classCode} {newVersion}");
}
var sourceVersion = await this.db.ClassVersions.Where(v => v.Classif == classCode && v.Version == versionCode).FirstOrDefaultAsync();
var brandNewVersion = new TC_Classif_Vers()
{
Classif = sourceVersion.Classif,
Version = newVersion,
Remarks = sourceVersion.Remarks,
ByLow = sourceVersion.ByLow,
Publications = sourceVersion.Publications,
UseAreas = sourceVersion.UseAreas,
Parent = sourceVersion.Parent,
Valid_From = sourceVersion.Valid_From,
Valid_To = sourceVersion.Valid_To
};
var connectionString = Configuration.GetSection("ConnectionStrings:DefaultConnection").Value;
SqlConnection con = new SqlConnection(connectionString);
SqlCommand com = new SqlCommand("ALTER TABLE ClassItems NOCHECK CONSTRAINT FK_ClassItems_ClassItems_Classif_Version_ParentItemCode");
SqlCommand comend = new SqlCommand("ALTER TABLE ClassItems CHECK CONSTRAINT FK_ClassItems_ClassItems_Classif_Version_ParentItemCode");
com.Connection = con;
comend.Connection = con;
con.Open();
com.ExecuteNonQuery();
await this.db.Database.BeginTransactionAsync();
try
{
await this.db.ClassVersions.AddAsync(brandNewVersion);
await this.db.SaveChangesAsync();
var items = await this.db.ClassItems.Where(i => i.Classif == classCode && i.Version == versionCode).ToListAsync();
foreach (var item in items)
{
var newItem = new TC_Classif_Items()
{
Classif = item.Classif,
Version = brandNewVersion.Version,
Description = item.Description,
DescriptionShort = item.DescriptionShort,
DescriptionEng = item.DescriptionEng,
Includes = item.Includes,
IncludesMore = item.IncludesMore,
IncludesNo = item.IncludesNo,
IsLeaf = item.IsLeaf,
ItemCode = item.ItemCode,
ItemLevel = item.ItemLevel,
EntryTime = DateTime.Now,
IsDeleted = item.IsDeleted,
OrderNo = item.OrderNo,
OtherCode = item.OtherCode,
ParentItemCode = item.ParentItemCode,
EnteredByUserId = userId,
};
await this.db.ClassItems.AddAsync(newItem);
}
await this.db.SaveChangesAsync();
}
catch (Exception)
{
this.db.Database.RollbackTransaction();
comend.ExecuteNonQuery();
con.Close();
return($"Грешка!!! Възникна проблем при създаването на новата версия.");
}
this.db.Database.CommitTransaction();
comend.ExecuteNonQuery();
con.Close();
var result = $"Версия с код: {classCode} {newVersion} беше създадена успешно \n";
if (copyRelations)
{
await this.db.Database.BeginTransactionAsync();
try
{
var sourceRelations = await this.db.ClassRelationsTypes.Where(r => r.SrcClassifId == classCode && r.SrcVersionId == versionCode).ToListAsync();
var destRelations = await this.db.ClassRelationsTypes.Where(r => r.DestClassifId == classCode && r.DestVersionId == versionCode).ToListAsync();
foreach (var rel in sourceRelations)
{
var newRelationType = new TC_Classif_Rel_Types()
{
SrcClassifId = classCode,
SrcVersionId = newVersion,
DestClassifId = rel.DestClassifId,
DestVersionId = rel.DestVersionId,
Description = $"Копие от релация \"{rel.Description}\". Сменена версия \"{versionCode}\" с версия \"{newVersion}\"",
Valid_From = rel.Valid_From,
Valid_To = rel.Valid_To
};
await this.db.ClassRelationsTypes.AddAsync(newRelationType);
await this.db.SaveChangesAsync();
var relItemsList = await this.db.ClassRelations.Where(ri => ri.RelationTypeId == rel.Id).ToListAsync();
foreach (var relItem in relItemsList)
{
var newRelation = new TC_Classif_Rels()
{
RelationTypeId = newRelationType.Id,
SrcClassif = relItem.SrcClassif,
SrcVer = newRelationType.SrcVersionId,
SrcItemId = relItem.SrcItemId,
DestClassif = relItem.DestClassif,
DestVer = relItem.DestVer,
DestItemId = relItem.DestItemId,
EntryTime = DateTime.Now,
EnteredByUserId = userId
};
await this.db.ClassRelations.AddAsync(newRelation);
}
await this.db.SaveChangesAsync();
}
foreach (var rel in destRelations)
{
var newRelationType = new TC_Classif_Rel_Types()
{
SrcClassifId = classCode,
SrcVersionId = rel.SrcVersionId,
DestClassifId = rel.DestClassifId,
DestVersionId = newVersion,
Description = $"Копие от релация \"{rel.Description}\". Сменена версия \"{versionCode}\" с версия \"{newVersion}\"",
Valid_From = rel.Valid_From,
Valid_To = rel.Valid_To
};
await this.db.ClassRelationsTypes.AddAsync(newRelationType);
await this.db.SaveChangesAsync();
var relItemsList = await this.db.ClassRelations.Where(ri => ri.RelationTypeId == rel.Id).ToListAsync();
foreach (var relItem in relItemsList)
{
var newRelation = new TC_Classif_Rels()
{
RelationTypeId = newRelationType.Id,
SrcClassif = relItem.SrcClassif,
SrcVer = relItem.SrcVer,
SrcItemId = relItem.SrcItemId,
DestClassif = relItem.DestClassif,
DestVer = newRelationType.DestVersionId,
DestItemId = relItem.DestItemId,
EntryTime = DateTime.Now,
EnteredByUserId = userId
};
await this.db.ClassRelations.AddAsync(newRelation);
}
await this.db.SaveChangesAsync();
}
}
catch (Exception)
{
this.db.Database.RollbackTransaction();
return(result + $"Грешка!!! Възникна проблем при копирането релационните таблици.");
}
this.db.Database.CommitTransaction();
result = result + Environment.NewLine + $"Копирането на релационните таблици беше успешно.";
}
return(result);
}
private async Task AddSingleItem(SqlConnection con, SqlCommand com, SqlCommand comend, TC_Classif_Items item)
{
con.Open();
com.Connection = con;
comend.Connection = con;
com.ExecuteNonQuery();
await this.db.ClassItems.AddAsync(item);
await this.db.SaveChangesAsync();
comend.ExecuteNonQuery();
comend.Dispose();
com.Dispose();
con.Close();
}
public async Task <string> AddNewItemAsync(string classCode, string versionCode, string newItemId, string description, string parentItemCode, bool isLeaf, string userId)
{
var checkMetaData = await this.versions.IsClassVersionExistAsync(classCode, versionCode);
if (!checkMetaData)
{
return($"Грешка! Няма такава версия {classCode} {versionCode}");
}
checkMetaData = await this.ItemExistAsync(classCode, versionCode, newItemId);
if (checkMetaData)
{
return($"Грешка! Вече има елемент с този код: {newItemId}");
}
int maxLevel = this.db.ClassItems.Where(i => i.Classif == classCode && i.Version == versionCode).Select(i => i.ItemLevel).DefaultIfEmpty(0).Max();
var newItem = new TC_Classif_Items();
try
{
newItem.Classif = classCode;
newItem.Version = versionCode;
newItem.ItemCode = newItemId;
newItem.Description = description;
newItem.DescriptionShort = description;
newItem.EnteredByUserId = userId;
if (!string.IsNullOrEmpty(parentItemCode))
{
var parentItem = await this.db.ClassItems.Where(i => i.Classif == classCode && i.Version == versionCode && i.ItemCode == parentItemCode).FirstOrDefaultAsync();
if (parentItem != null)
{
newItem.ParentItemCode = parentItemCode;
newItem.ItemLevel = parentItem.ItemLevel + 1;
}
else
{
return($"Няма родителски елемент с код {parentItemCode}");
}
}
else
{
newItem.ItemLevel = 1;
}
newItem.EntryTime = DateTime.Now;
if (isLeaf && newItem.ItemLevel < maxLevel && maxLevel >= 1)
{
return($"Има листа с по-висок ранг! Листата трябва да са с еднакъв ранг.");
}
newItem.IsLeaf = isLeaf;
await this.db.ClassItems.AddAsync(newItem);
await this.db.SaveChangesAsync();
return("Създаването на елемент беше успешно.");
}
catch (Exception)
{
return("Грешка! Наличие на дълго поле.");
}
}
public async Task <string> AddItemsCollection(List <AddNewClassItemsServiceModel> items)
{
if (!IsClassificationExist(items[0].Classif))
{
return($"Няма класификация с код {items[0].Classif}");
}
var connectionString = Configuration.GetSection("ConnectionStrings:DefaultConnection").Value;
SqlConnection con = new SqlConnection(connectionString);
SqlCommand com = new SqlCommand("ALTER TABLE ClassItems NOCHECK CONSTRAINT FK_ClassItems_ClassItems_Classif_Version_ParentItemCode");
SqlCommand comend = new SqlCommand("ALTER TABLE ClassItems CHECK CONSTRAINT FK_ClassItems_ClassItems_Classif_Version_ParentItemCode");
com.Connection = con;
comend.Connection = con;
con.Open();
com.ExecuteNonQuery();
//SqlTransaction transaction = con.BeginTransaction(IsolationLevel.Serializable);
int i = 0;
await this.db.Database.BeginTransactionAsync();
try
{
for (i = 0; i <= items.Count() - 1; i++)
{
var newItemDb = new TC_Classif_Items()
{
Classif = items[i].Classif,
Version = items[i].Version,
ItemCode = items[i].ItemCode,
Description = items[i].Description,
DescriptionShort = items[i].DescriptionShort,
DescriptionEng = items[i].DescriptionEng,
Includes = items[i].Includes,
IncludesMore = items[i].IncludesMore,
IncludesNo = items[i].IncludesNo,
EnteredByUserId = items[i].EnteredByUserId,
EntryTime = items[i].EntryTime,
IsLeaf = items[i].IsLeaf,
ItemLevel = items[i].ItemLevel,
OrderNo = items[i].OrderNo,
ParentItemCode = items[i].ParentItemCode,
IsDeleted = false
};
await this.db.ClassItems.AddAsync(newItemDb);
}
await this.db.SaveChangesAsync();
}
catch (Exception)
{
this.db.Database.RollbackTransaction();
comend.ExecuteNonQuery();
con.Close();
return(string.Concat($"Ред N:{i}", " ", items[i].Classif, " ", items[i].Version, " ", items[i].ItemCode, " има грешка (плюс минус един ред!)"));
}
this.db.Database.CommitTransaction();
//transaction.Commit();
comend.ExecuteNonQuery();
con.Close();
return("success");
}