public void CreateSign(ADDB context, Sign sign)
{
if (String.IsNullOrWhiteSpace(sign.Name))
{
TempData["Errors"] = "Sign Name is missing";
return;
}
sign.Name = sign.Name.ToUpper();
if (context.Signs.Where(s => s.Name == sign.Name).Count() > 0)
{
TempData["Errors"] = "Sign " + sign.Name + " already exists!";
return;
}
//TO DO ADDITIONAL CHECKS
switch (sign.Type_of_Value)
{
case SignTypes.NUMERICAL:
break;
case SignTypes.OBSERVATIONAL:
break;
}
context.Signs.Add(sign);
context.SaveChanges();
TempData["Errors"] = null;
}
public JsonResult DeleteIncompleteCase(ADDB context, int caseID)
{
string r = "";
//check if there have been symptoms log but no results
var signsForCase = context.SignsForCases.Where(x => x.CaseID == caseID).ToList();
if (signsForCase.Count > 0)
{
//delete the symps
context.SignsForCases.RemoveRange(signsForCase);
context.SaveChanges();
}
//finally delete the case
context.Cases.Remove(context.Cases.Find(caseID));
context.SaveChanges();
return(Json(r));
}
private void CreateSignCoresForAnimal(ADDB context, string name, int c, int rowsLength, object[,] valueArray)
{
List <int> ids = new List <int>();
var animals = context.Animals.Where(n => n.Name.Contains(name)).ToList();
foreach (Animal a in animals)
{
for (int r = 2; r <= rowsLength; r++)
{
string signName = (string)valueArray[r, 1];
if (signName == null)
{
continue;
}
string columnValue = (string)valueArray[r, c];
if (columnValue == null)
{
continue;
}
if (columnValue.Equals("X"))
{
var signList = context.Signs.Where(s => s.Name.Contains(signName.ToUpper())).ToList();
if (signList.Count() > 0)
{
Sign sign = signList[0];
context.SignCore.Add(new SignCore(a.Id, sign.Id));
}
}
}
}
}
public ActionResult InsertDiseasePrior(ADDB context, int diseaseID, int animalID, string probability)
{
CreateDiseasePrior(context, diseaseID, animalID, probability);
return(RedirectToAction("Index"));
}
public String GetAnimalNameFromPatientID(ADDB context, int patientID)
{
string rawSql = @"Select DISTINCT Animals.Name from Animals,Patients where Animals.Id = (SELECT Patients.AnimalID FROM Patients WHERE Patients.ID=@p0)";
var query = context.Database.SqlQuery <String>(rawSql, patientID).First();
return(query);
}
protected override ReturnValue GetSymptomsForCaseAndPopulateTable(int originalCaseId, int newCaseId, string tableName)
{
bool thereWasAnError = false;
try
{
if (tableName.Equals(SET_CASE_TABLE))
{
//selected symptoms n
//get the symptoms for the specific case
List <SignForCases> syptoms = eddie.SelectedSymptomsN.Where(x => x.CaseId == originalCaseId).Select(x => new SignForCases {
CaseId = newCaseId, SignId = GetSignID(x.SymptomName), SignPresence = TranslateSymptomPresenceFromEddieToNewFrameWork(x.Selection)
}).ToList();
ADDB.SignForCases.AddRange(syptoms);
}
else if (tableName.Equals(CASE_INFO_TABLE))
{
//selected symptoms
List <SignForCases> syptoms = eddie.SelectedSymptoms.Where(x => x.CaseId == originalCaseId).Select(x => new SignForCases {
CaseId = newCaseId, SignId = GetSignID(x.SymptomName), SignPresence = TranslateSymptomPresenceFromEddieToNewFrameWork(x.Selection)
}).ToList();;
ADDB.SignForCases.AddRange(syptoms);
}
ADDB.SaveChanges();
}
catch (Exception e)
{
logFileWriter.WriteLine("Problem with {0} in method GetDiseaseSymptomsforCaseAndPopulateTable from table {1}", originalCaseId, tableName);
logFileWriter.WriteLine("Excpetion details: {0} {1}", e.Message, e.StackTrace);
}
Console.WriteLine("Succesfully added signs for case {0}: {1} ", tableName, originalCaseId);
return(new ReturnValue {
ID = 0, ThereWasAnError = thereWasAnError
});
}
public static AD CreateUser(AD User)
{
AD inserted = ADDB.CreateUser(User);
PaymentDB.CreatePayment(inserted.UserId);
SAPHRDB.CreateSAPHR(inserted.UserId, inserted.Username);
return(inserted);
}
public ActionResult RemoveLikelihood(ADDB context, int id)
{
Likelihood likelihoodToRemove = context.Likelihoods.Find(id);
context.Likelihoods.Remove(likelihoodToRemove);
return(RedirectToAction("Index"));
}
public ActionResult InsertNewDisease(ADDB context, Disease disease)
{
CreateDisease(context, disease);
TempData["Errors"] = null;
return(RedirectToAction("Index"));
}
public JsonResult DrawCasesBySpeciesAndYear(ADDB context, string year)
{
HistogramData histogramData = new HistogramData();
string[] months = { "Jan", "Feb", "Mar", "Apr", "May", "June", "July", "Aug", "Sep", "Oct", "Nov", "Dec" };
List <string> annotations = new List <string>();
histogramData.Arr = new List <List <string> >();
annotations.Add("Species");
List <string> species = context.Animals.OrderBy(x => x.Name).Select(x => x.Name).Distinct().ToList(); // very important that the order matches in both queries
annotations.AddRange(species);
for (int i = 0; i < months.Length; i++)
{
List <string> dataRow = new List <string>();
dataRow.Add(months[i]);
const string rawSql = "SELECT DISTINCT Animals.Name AS Name, COUNT(Cases.PatientID) AS Dcount" +
" FROM Animals, Patients, Cases " +
"WHERE Patients.ID = Cases.PatientID AND " +
"Patients.AnimalID = Animals.Id " +
"AND " +
"MONTH(Cases.DateOfCaseObserved) = @p0 " +
"AND " +
"YEAR(Cases.DateOfCaseObserved) = @p1 " +
"GROUP BY Animals.Name " +
"ORDER BY Animals.Name";
object[] p = { i + 1, year };
var query = context.Database.SqlQuery <CasesByMonth>(rawSql, p).ToList();
if (query.Count == 0)
{
foreach (var s in species) // if we have no results for this month set all results to 0
{
dataRow.Add("0");
}
}
else
{
foreach (CasesByMonth cbm in query) // if we have results for the month populate with real data
{
dataRow.Add(cbm.DCount.ToString());
}
}
dataRow.Add(" "); //add the annotations padding
histogramData.Arr.Add(dataRow);
} // for loop
histogramData.Annotations = annotations;
return(Json(histogramData));
}
public void LoadSignsMasterList(ADDB context)
{
string extension = ".xlsx";
string filename = "master_list";
string path = Server.MapPath(@"~/Files/" + filename + extension);
try
{
Excel.Application app = new Excel.Application();
Excel.Workbook WB = app.Workbooks.Open(path);
Excel.Worksheet signsWorkSheet = WB.Worksheets["Signs core"];
Excel.Range usedCells = signsWorkSheet.UsedRange;
object[,] valueArray = (object[, ])usedCells.get_Value(
XlRangeValueDataType.xlRangeValueDefault);
int rowsLength = valueArray.GetLength(0);
int columnsLength = valueArray.GetLength(1);
for (int c = 2; c <= columnsLength; c++)
{
string name = (string)valueArray[1, c];
if (name == null || name.Equals("Comment"))
{
continue;
}
name = name.ToUpper(); // name needs to be uppercase
if (name.Equals("Sheep".ToUpper()))
{
CreateSignCoresForAnimal(context, "SHEEP", c, rowsLength, valueArray);
CreateSignCoresForAnimal(context, "GOAT", c, rowsLength, valueArray);
}
else if (name.Equals("Equid".ToUpper()))
{
CreateSignCoresForAnimal(context, "HORSE_MULE", c, rowsLength, valueArray);
CreateSignCoresForAnimal(context, "DONKEY", c, rowsLength, valueArray);
}
else
{
CreateSignCoresForAnimal(context, name, c, rowsLength, valueArray);
}
}
//close the workbook and the app
WB.Close();
Marshal.ReleaseComObject(WB);
Marshal.ReleaseComObject(app);
context.SaveChanges();
}
catch (Exception e)
{
Console.Write(e.Message);
}
}
public ActionResult RemoveDiseasePrior(ADDB context, int id)
{
PriorsDiseases priorToRemove = context.PriorsDiseases.Find(id);
context.PriorsDiseases.Remove(priorToRemove);
context.SaveChanges();
return(RedirectToAction("Index"));
}
private float GetPriorForDisease(ADDB context, int animalID, int diseaseID)
{
float priorValue = 0.0f;
var prior = context.PriorsDiseases.Where(m => m.AnimalID == animalID && m.DiseaseID == diseaseID).First();
float.TryParse(prior.Probability, out priorValue);
return(priorValue);
}
private float GetLikelihoodValue(ADDB context, int animalID, int signID, int diseaseID)
{
float likelihoodValue = 1.0f;
var likelihood = context.Likelihoods.Where(m => m.AnimalId == animalID && m.SignId == signID && m.DiseaseId == diseaseID).First();
float.TryParse(likelihood.Value, out likelihoodValue);
likelihoodValue /= 100.0f;
return(likelihoodValue);
}
private List <string> GetDiseaseNames(ADDB context, string an)
{
string rawSql = "SELECT Diseases.Name AS userCHDisease " +
"FROM Diseases " +
"JOIN Cases ON Cases.DiseaseChosenByUserID = Diseases.Id " +
"JOIN Patients ON Patients.ID = Cases.PatientID " +
"JOIN Animals ON Patients.AnimalID = Animals.ID AND Animals.Name = @p0 " +
"GROUP BY Diseases.Name " +
"ORDER BY Diseases.Name";
return(context.Database.SqlQuery <DiseaseName>(rawSql, an).Select(x => x.userCHDisease).ToList());
}
/*REFERENCE JSON FORMAT
* "cols": [
* {"id":"","label":"Topping","pattern":"","type":"string"},
* {"id":"","label":"Slices","pattern":"","type":"number"}
* ],
* "rows": [
* {"c":[{"v":"Mushrooms","f":null},{"v":3,"f":null}]},
* {"c":[{"v":"Onions","f":null},{"v":1,"f":null}]},
* {"c":[{"v":"Olives","f":null},{"v":1,"f":null}]},
* {"c":[{"v":"Zucchini","f":null},{"v":1,"f":null}]},
* {"c":[{"v":"Pepperoni","f":null},{"v":2,"f":null}]}
* ]
* }*/
public ActionResult Index(ADDB context)
{
//comment
HomeViewModel model = new HomeViewModel();
model.SpeciesInEddie = context.Animals.Select(s => s.Name).Distinct().ToList();
Version version = System.Reflection.Assembly.GetExecutingAssembly().GetName().Version;
model.BuildVersion = version.ToString();
return(View(model));
}
public void CreateLikelihood(ADDB context, Likelihood likelihood)
{
//deal with duplicate entries
var duplicates = context.Likelihoods.Where(m => m.AnimalId == likelihood.AnimalId && m.DiseaseId == likelihood.DiseaseId && m.SignId == likelihood.SignId);
if (duplicates.Count() > 0)
{
return; // do not add anything if we already have a likelihood for the same animal, disease and sign
}
context.Likelihoods.Add(likelihood);
context.SaveChanges();
}
public JsonResult DrawTestGraph(ADDB context)
{
const string rawSql = "SELECT DISTINCT Animals.Name AS species, COUNT(Cases.PatientID) as Expr1 " +
"FROM Animals, Patients, Cases" +
" WHERE Patients.ID = Cases.PatientID AND Patients.AnimalID = Animals.Id " +
"GROUP by Animals.Name";
var query = context.Database.SqlQuery <AnimalCount>(rawSql).ToList();
return(Json(query));
}
public JsonResult DrawDiseaseByAnimal(ADDB context, string animalName)
{
string an = animalName;
string rawSql = @"SELECT DISTINCT Diseases.Name AS userCHdisease, COUNT(Cases.DiseaseChosenByUserID) as Expr1 FROM Animals, Patients, Cases,Diseases WHERE Diseases.Id= Cases.DiseaseChosenByUserID AND Patients.ID = Cases.PatientID AND Patients.AnimalID = Animals.Id AND Animals.Name= @p0 GROUP by Diseases.Name";
object[] p = { an };
var query = context.Database.SqlQuery <AnimalDisease>(rawSql, p).ToList();
return(Json(query));
}
public ActionResult RenderSignsPartial(ADDB context, int animalID)
{
var signcore = context.SignCore.Where(sc => sc.AnimalID == animalID).ToList();
List <Sign> model = new List <Sign>();
foreach (SignCore sc in signcore)
{
model.Add(context.Signs.Find(sc.SignID));
}
return(PartialView("_SignsList", model));
}
public ActionResult RemoveAnimal(ADDB context, string name)
{
var animalsToRemove = context.Animals.Where(m => m.Name == name);
//remove foreign key references before removing the animal
//foreach (var a in animalsToRemove)
// foreach (var pd in context.PriorsDiseases.Where(m => m.AnimalID == a.Id))
// context.PriorsDiseases.Remove(pd);
context.Animals.RemoveRange(animalsToRemove);
context.SaveChanges();
return(RedirectToAction("Index", "EditDB"));
}
public static bool GetUserAccount(string Username, string Password)
{
string UsernameDB = ADDB.GetUserAccount(Username);
string UsernameMerge = Username + Password;
if (UsernameDB.Equals(UsernameMerge))
{
return(true);
}
else
{
return(false);
}
}
public List <int> FindAllAnimalsIDsWithName(ADDB context, string name)
{
name = name.ToUpper(); // upcase just in case....
List <int> ids = new List <int>();
var animals = context.Animals.Where(n => n.Name == name).ToList();
foreach (Animal a in animals)
{
ids.Add(a.Id);
}
return(ids);
}
protected override ReturnValue GetResultsForCaseAndPopulateTable(int originalCaseId, int newCaseId, string tableName)
{
bool thereWasAnError = false;
try
{
if (tableName.Equals(SET_CASE_TABLE))
{
//diseaseRank n
//get the symptoms for the specific case
List <ResultForCases> results = eddie.DiseaseRankN
.Where(x => x.CaseId == originalCaseId)
.Select(x =>
new ResultForCases
{
CaseId = newCaseId,
DiseaseId = GetDiseaseID(x.DiseaseName),
PredictedLikelihoodOfDisease = ConvertLikelihoodFromStringToFloat(x.Percentage)
}).ToList();
ADDB.ResultForCases.AddRange(results);
}
else if (tableName.Equals(CASE_INFO_TABLE))
{
//diseaseRank
List <ResultForCases> results = eddie.DiseaseRank
.Where(x => x.CaseId == originalCaseId)
.Select(x => new ResultForCases
{
CaseId = newCaseId,
DiseaseId = GetDiseaseID(x.DiseaseName),
PredictedLikelihoodOfDisease = ConvertLikelihoodFromStringToFloat(x.Percentage)
}).ToList();
ADDB.ResultForCases.AddRange(results);
}
ADDB.SaveChanges();
}
catch (Exception e)
{
thereWasAnError = true;
logFileWriter.WriteLine("Problem in method GetResultsForCaseAndPopulateTable() method for case {0} in table {1}", originalCaseId, tableName);
logFileWriter.WriteLine("Excpetion details: {0} {1}", e.Message, e.StackTrace);
}
Console.WriteLine("Sucessfully added results for case {0} from table {1}", originalCaseId, tableName);
return(new ReturnValue {
ID = 0, ThereWasAnError = thereWasAnError
});
}
protected override int IdentifyOrCreateNewPatient(int animalID, int ownerID)
{
//this method tries to identify the patient to see if two cases are the same
//for the moment we'll treat each case as dealing with one patient
Patients newPatient = new Patients();
newPatient.AnimalId = animalID;
newPatient.OwnerId = ownerID;
ADDB.Patients.Add(newPatient);
ADDB.SaveChanges();
int newPatientID = ADDB.Patients.Last().Id;
return(newPatientID);
}
protected override void CleanUpNewPatientAndNewOwnerAsAResultOfAnError(int patientID, int ownerID)
{
if (patientID != -1)
{
var patientToRemove = ADDB.Patients.Find(patientID);
ADDB.Patients.Remove(patientToRemove);
}
if (ownerID != -1)
{
var ownerToRemove = ADDB.Owners.Find(ownerID);
ADDB.Owners.Remove(ownerToRemove);
}
ADDB.SaveChanges();
}
public ActionResult RemoveSign(ADDB context, int id)
{
Sign signToRemove = context.Signs.Find(id);
foreach (Likelihood l in context.Likelihoods.Where(m => m.SignId == id).ToList())
{
context.Likelihoods.Remove(l);
}
context.Signs.Remove(signToRemove);
context.SaveChanges();
return(RedirectToAction("Index"));
}
public JsonResult DrawTestGraph(ADDB context)
{
const string rawSql = "SELECT DISTINCT Animals.Name AS species, COUNT(Cases.PatientID) as Expr1 " +
"FROM Animals, Patients, Cases" +
" WHERE Patients.ID = Cases.PatientID AND Patients.AnimalID = Animals.Id " +
"GROUP by Animals.Name";
/*rawSql = "SELECT species, COUNT(species) AS Expr1 " +
* "FROM(SELECT species " +
* "FROM setCase UNION ALL SELECT species FROM caseInfo) " +
* "derivedtbl_1 " +
* "GROUP BY species";*/
var query = context.Database.SqlQuery <AnimalCount>(rawSql).ToList();
return(Json(query));
}
// GET: EditDB
public ActionResult Index(ADDB context, EditDBViewModel model)
{
if (ModelState.IsValid)
{
if (TempData["Errors"] != null)
{
ModelState.AddModelError("Animal Name", (string)TempData["Errors"]);
}
}
try
{
model.animals = context.Animals.ToList();
model.diseases = context.Diseases.ToList();
model.priorsDiseases = context.PriorsDiseases.ToList();
model.signs = context.Signs.ToList();
model.likelihoods = context.Likelihoods.ToList();
foreach (Animal n in model.animals)
{
model.animalNames.Add(n.Name);
}
if (TempData["LOG"] != null)
{
model.logMessage = (string)TempData["LOG"];
TempData["LOG"] = null;
}
}
catch (Exception ex)
{
String innerMessage = (ex.InnerException != null)
? ex.InnerException.Message
: "";
return(PartialView("_ErrorDB", new ErrorDBViewModel(innerMessage)));
}
return(View(model));
}
public void CreateDiseasePrior(ADDB context, int diseaseID, int animalID, string probability)
{
var duplicate = context.PriorsDiseases.Where(m => m.DiseaseID == diseaseID && m.AnimalID == animalID).ToList();
if (duplicate.Count > 0)
{
duplicate[0].Probability = probability; // if we do this the prior gets updated rather than added again
}
else
{
PriorsDiseases priorsDiseases = new PriorsDiseases();
priorsDiseases.AnimalID = animalID;
priorsDiseases.DiseaseID = diseaseID;
priorsDiseases.Probability = probability;
context.PriorsDiseases.Add(priorsDiseases);
}
context.SaveChanges();
}
