public ResultDto PostScholasticData(ScholasticDto scholasticDto)
{
scholasticDto.GuidedReading = scholasticDto.GuidedReading.Trim();
scholasticDto.Dra = scholasticDto.Dra.Trim();
var verificationResult = VerifyScholasticDto(scholasticDto);
//if (verificationResult.ResultCode < 0)
// return verificationResult;
var result = new ResultDto { ResultDescription = "" };
using (var context = new BookcaveEntities())
{
var bookRecords = context.Database.SqlQuery<BookRecord>("Select * from BookRecords where Isbn13 = {0}", scholasticDto.Isbn13);
if (bookRecords.FirstOrDefault() == null)
return new ResultDto { ResultDescription = "no general info exists yet for " + scholasticDto.Isbn13 };
var skillRecords = context.Database.SqlQuery<SkillRecord>("Select * from SkillRecords where Isbn13 = {0}", scholasticDto.Isbn13);
var currentSkillRecord = skillRecords.FirstOrDefault();
Mapper.CreateMap<ScholasticDto, SkillRecord>();
if (currentSkillRecord != null)
{
var modifiedSkillRecord = Mapper.Map<ScholasticDto, SkillRecord>(scholasticDto, currentSkillRecord);
var averageSkillAge = DataFunctions.GetAverageSkillAge(modifiedSkillRecord);
context.Database.ExecuteSqlCommand("update SkillRecords set ScholasticGrade={0},Dra={1},GuidedReading={2},AverageSkillAge={3} where Isbn13={4}",
scholasticDto.ScholasticGrade,
scholasticDto.Dra,
scholasticDto.GuidedReading,
averageSkillAge,
scholasticDto.Isbn13);
result.ResultDescription += scholasticDto.Isbn13 + " skills updated ";
}
else
{
var newSkillRecord = Mapper.Map<ScholasticDto, SkillRecord>(scholasticDto);
newSkillRecord.AverageSkillAge = DataFunctions.GetAverageSkillAge(newSkillRecord);
context.SkillRecords.Add(newSkillRecord);
result.ResultDescription += scholasticDto.Isbn13 + " skills added ";
}
var contentRecords = context.Database.SqlQuery<ContentRecord>("Select * from ContentRecords where Isbn13 = {0}", scholasticDto.Isbn13);
var currentContentRecord = contentRecords.FirstOrDefault();
Mapper.CreateMap<ScholasticDto, ContentRecord>();
if (currentContentRecord != null)
{
var modifiedContentRecord = Mapper.Map<ScholasticDto, ContentRecord>(scholasticDto, currentContentRecord);
var averageContentAge = DataFunctions.GetAverageContentAge(modifiedContentRecord);
context.Database.ExecuteSqlCommand("update ContentRecords set ScholasticGradeLower={0}, ScholasticGradeHigher={1},AverageContentAge={2} where Isbn13={3}",
scholasticDto.ScholasticGradeLower,
scholasticDto.ScholasticGradeHigher,
averageContentAge,
scholasticDto.Isbn13);
context.SaveChanges();
result.ResultDescription += scholasticDto.Isbn13 + " contents updated";
}
else
{
var newContentRecord = Mapper.Map<ScholasticDto, ContentRecord>(scholasticDto);
context.ContentRecords.Add(newContentRecord);
context.SaveChanges();
result.ResultDescription += scholasticDto.Isbn13 + " contents added";
}
//try
//{
//}
//catch (DbEntityValidationException e) { Console.WriteLine(e.Message); }
//catch (DbUpdateException e) { Console.WriteLine(e.Message); }
}
return result;
}
public ResultDto PostCommonSenseData(CommonSenseDto commonSenseDto)
{
var result = new ResultDto { ResultDescription = "" };
using (var context = new BookcaveEntities())
{
var bookRecords = context.Database.SqlQuery<BookRecord>("Select * from BookRecords where Isbn13 = {0}", commonSenseDto.Isbn13);
if (bookRecords.FirstOrDefault() == null)
return new ResultDto { ResultDescription = "no general info exists yet for " + commonSenseDto.Isbn13 };
var contentRecords = context.Database.SqlQuery<ContentRecord>("Select * from ContentRecords where Isbn13 = {0}", commonSenseDto.Isbn13);
var currentContentRecord = contentRecords.FirstOrDefault();
Mapper.CreateMap<CommonSenseDto, ContentRecord>();
if (currentContentRecord != null)
{
var modifiedContentRecord = Mapper.Map<CommonSenseDto, ContentRecord>(commonSenseDto, currentContentRecord);
var aggregateContent = DataFunctions.GetAverageContentAge(modifiedContentRecord);
context.Database.ExecuteSqlCommand
(
"update ContentRecords set CommonSensePause={0},CommonSenseOn={1},CommonSenseNoKids={2},AverageContentAge={3} where Isbn13={4}",
commonSenseDto.CommonSensePause,
commonSenseDto.CommonSenseOn,
commonSenseDto.CommonSenseNoKids,
aggregateContent,
commonSenseDto.Isbn13
);
context.SaveChanges();
result.ResultDescription += commonSenseDto.Isbn13 + " contents updated";
}
else
{
var newContentRecord = Mapper.Map<CommonSenseDto, ContentRecord>(commonSenseDto);
newContentRecord.AverageContentAge = DataFunctions.GetAverageContentAge(newContentRecord);
context.ContentRecords.Add(newContentRecord);
context.SaveChanges();
result.ResultDescription += commonSenseDto.Isbn13 + " contents added";
}
}
return result;
}
/// <summary>
/// Adds or updates a single book in the books database
/// </summary>
/// <param name="lexileDto">book to add</param>
/// <returns></returns>
public ResultDto PostLexileData(LexileDto lexileDto)
{
if (!LexileRecordVerified(lexileDto))
return new ResultDto { ResultDescription = "bad object" };
var result = new ResultDto { ResultDescription = "" };
Mapper.CreateMap<LexileDto, SkillRecord>();
using (var context = new BookcaveEntities())
{
//var recordQuery = context.SkillRecords.Where(book => book.Isbn13.Equals(lexileDto.Isbn13));
var recordQuery = context.Database.SqlQuery<SkillRecord>("Select * from SkillRecords where Isbn13 = {0}", lexileDto.Isbn13);
//var currentSkillRecord = skillRecords.FirstOrDefault();
var skillRecords = new List<SkillRecord>(recordQuery);
SkillRecord currentSkillRecord = null;
if (skillRecords.Count > 0)
currentSkillRecord = skillRecords.First();
if (currentSkillRecord != null)
{
var modifiedSkillRecord = Mapper.Map<LexileDto, SkillRecord>(lexileDto, currentSkillRecord);
var aggregateSkill = DataFunctions.GetAverageSkillAge(modifiedSkillRecord);
context.Database.ExecuteSqlCommand
(
"update SkillRecords set LexScore={0},LexCode={1},LexUpdate={2},AverageSkillAge={3} where Isbn13={4}",
lexileDto.LexScore,
lexileDto.LexCode,
lexileDto.LexUpdate,
aggregateSkill,
lexileDto.Isbn13
);
result.ResultDescription += lexileDto.Isbn13 + " skills updated ";
}
else
{
var newSkillRecord = Mapper.Map<LexileDto, SkillRecord>(lexileDto);
newSkillRecord.AverageSkillAge = DataFunctions.GetAverageSkillAge(newSkillRecord);
context.SkillRecords.Add(newSkillRecord);
result.ResultDescription += lexileDto.Isbn13 + " skills added ";
}
Mapper.CreateMap<LexileDto, BookRecord>();
var bookQuery = context.Database.SqlQuery<BookRecord>("Select * from BookRecords where Isbn13 = {0}", lexileDto.Isbn13);
//var bookQuery = context.BookRecords.Where(book => book.Isbn13.Equals(lexileDto.Isbn13));
var bookRecords = new List<BookRecord>(bookQuery);
BookRecord currentBookRecord = null;
if (bookRecords.Count > 0)
currentBookRecord = bookRecords[0];
//var currentBookRecord = bookRecords.FirstOrDefault();
if (currentBookRecord != null)
{
//currentBookRecord = Mapper.Map<LexileDto, BookRecord>(lexileDto, currentBookRecord);
context.Database.ExecuteSqlCommand
(
"update BookRecords set Title={0},Author={1},Publisher={2},PageCount={3},DocType={4},Series={5},Awards={6},Summary={7} where Isbn13={8}",
lexileDto.Title,
lexileDto.Author,
lexileDto.Publisher,
lexileDto.PageCount,
lexileDto.DocType,
lexileDto.Series,
lexileDto.Awards,
lexileDto.Summary,
lexileDto.Isbn13
);
context.SaveChanges();
result.ResultDescription += lexileDto.Isbn13 + " books added ";
}
else
{
var newBookRecord = Mapper.Map<LexileDto, BookRecord>(lexileDto);
context.BookRecords.Add(newBookRecord);
context.SaveChanges();
result.ResultDescription += lexileDto.Isbn13 + " books added ";
}
//try
//{
//}
//catch (DbEntityValidationException e) { Console.WriteLine(e.Message); }
//catch (DbUpdateException e) { Console.WriteLine(e.Message); }
}
return result;
}
public ResultDto PostBarnesData(BarnesDto barnesDto)
{
ResultDto result = new ResultDto { ResultDescription = "" };
using (var context = new BookcaveEntities())
{
var bookRecords = context.Database.SqlQuery<BookRecord>("Select * from BookRecords where Isbn13 = {0}", barnesDto.Isbn13);
if (bookRecords.FirstOrDefault() == null)
return new ResultDto { ResultDescription = "no general info exists yet for " + barnesDto.Isbn13 };
var ratingRecords = context.Database.SqlQuery<RatingRecord>("Select * from RatingRecords where Isbn13 = {0}", barnesDto.Isbn13);
RatingRecord currentRatingRecord = null;
if (ratingRecords.Count() > 0) currentRatingRecord = ratingRecords.FirstOrDefault();
Mapper.CreateMap<BarnesDto, RatingRecord>();
if (currentRatingRecord != null)
{
var modifiedRatingRecord = Mapper.Map<BarnesDto, RatingRecord>(barnesDto, currentRatingRecord);
context.Database.ExecuteSqlCommand("update RatingRecords set BarnesAvg={0} where Isbn13={1}",
barnesDto.BarnesAvg,
barnesDto.Isbn13);
result.ResultDescription += barnesDto.Isbn13 + " ratings updated ";
}
else
{
var newRatingRecord = Mapper.Map<BarnesDto, RatingRecord>(barnesDto);
context.RatingRecords.Add(newRatingRecord);
result.ResultDescription += barnesDto.Isbn13 + " ratings added ";
}
var contentRecords = context.Database.SqlQuery<ContentRecord>("Select * from ContentRecords where Isbn13 = {0}", barnesDto.Isbn13);
ContentRecord currentContentRecord = null;
if (contentRecords.Count() > 0)
currentContentRecord = contentRecords.FirstOrDefault();
Mapper.CreateMap<BarnesDto, ContentRecord>();
if (currentContentRecord != null)
{
var modifiedContentRecord = Mapper.Map(barnesDto, currentContentRecord);
var averageContentAge = DataFunctions.GetAverageContentAge(modifiedContentRecord);
context.Database.ExecuteSqlCommand
(
"update ContentRecords set BarnesAgeYoung={0},BarnesAgeOld={1},AverageContentAge={2} where Isbn13={3}",
barnesDto.BarnesAgeYoung,
barnesDto.BarnesAgeOld,
averageContentAge,
barnesDto.Isbn13
);
context.SaveChanges();
result.ResultDescription += barnesDto.Isbn13 + " contents updated";
}
else
{
var newContentRecord = Mapper.Map<BarnesDto, ContentRecord>(barnesDto);
newContentRecord.AverageContentAge = DataFunctions.GetAverageContentAge(newContentRecord);
context.ContentRecords.Add(newContentRecord);
context.SaveChanges();
result.ResultDescription += barnesDto.Isbn13 + " contents added";
}
//try
//{
//}
//catch (DbUpdateException e)
//{
// Console.WriteLine("book metadata wasn't found in primary table.");
// return new ResultDto { ResultDescription = e.Message };
//}
}
return result;
}
