public int getminAge(int expID) { string sql = $@"Select Min(AgeID) as MinAge From SubExperiment Where ExpID ={expID}"; int minAge = Int32.Parse(Dal.ExecScalar(sql).ToString()); return(minAge); }
// extrcting taskname of the experiment public int GetTaskName(int expID) { string sql = $@"Select task.id as TaskID from Experiment inner join task on task.id = Experiment.taskID WHERE experiment.ExpID = {expID};"; int taskID = Int32.Parse(Dal.ExecScalar(sql).ToString()); return(taskID); }
public void AddNewAge(Request request) { string sql = $@"Insert into Request (Type, FullName, Email, Age) Values ('AddAge', '{HelperService.EscapeSql(request.FullName)}', '{request.Email}', '{HelperService.EscapeSql(request.Age)}'); SELECT @@IDENTITY AS 'Identity';"; Int32.Parse(Dal.ExecScalar(sql).ToString()); }
public int InsertExperiment(Experiment experiment, string userID, string Email) { var repoGuid = new Guid(); string repoString = "null"; if (Guid.TryParse(experiment.RepoGuid, out repoGuid)) { repoString = "'" + experiment.RepoGuid + "'"; } string sql = $"insert into Experiment " + $"(UserID, PUSID, ExpName, StartExpDate, EndExpDate, TaskID, SpeciesID, TaskDescription, DOI, Status, TaskBattery, MultipleSessions, RepoGuid) Values " + $"('{userID}', {experiment.PUSID}, '{HelperService.EscapeSql(experiment.ExpName.Trim())}', '{experiment.StartExpDate}', '{experiment.EndExpDate}', " + $"'{experiment.TaskID}', '{experiment.SpeciesID}', '{HelperService.EscapeSql(experiment.TaskDescription)}'," + $" '{HelperService.EscapeSql(experiment.DOI)}', {(experiment.Status ? 1 : 0)}, '{HelperService.EscapeSql(experiment.TaskBattery)}', {(experiment.MultipleSessions ? 1 : 0)}, " + $" {repoString} );" + $" SELECT @@IDENTITY AS 'Identity';"; // Calling function to send an email to Admin that new Exp with public Status has been added to MouseBytes string strBody = $@"Hi Admin: <br /><br /> User with Email address <b>{Email}</b> has just created the experiment: <b>{HelperService.EscapeSql(experiment.ExpName.Trim())}</b> with public Status! <br /><br /> Thanks <br /> MouseBytes"; HelperService.SendEmail("", "", "New Experiment with pubic status added!", strBody); return(Int32.Parse(Dal.ExecScalar(sql).ToString())); }
// Function definition for inserting animal info public int InsertAnimal(Animal animal) { string sql = $"Insert into Animal " + $"(ExpID, UserAnimalID, SID, GID, Sex) Values " + $"({animal.ExpID}, '{animal.UserAnimalID}', {animal.SID}, {animal.GID}, '{animal.Sex}'); SELECT @@IDENTITY AS 'Identity';"; return(Int32.Parse(Dal.ExecScalar(sql).ToString())); }
public int InsertTest(TaskAnalysis taskAnalysis) { string sql = $"insert into Test (Name, Last_name) Values ('{taskAnalysis.Name}', '{taskAnalysis.OriginalName}'); SELECT @@IDENTITY AS 'Identity';"; //Dal.ExecuteNonQuery(sql); //Dal.ExecuteScalar(sql); return(Int32.Parse(Dal.ExecScalar(sql).ToString())); }
public string GetPostProcessingResult(int subExpID) { string sql = $@"SELECT ISNULL(ErrorMessage, '') AS ErrorMessage FROM SubExperiment WHERE SubExpID = {subExpID} ;"; return(Dal.ExecScalar(sql).ToString()); }
public void AddNewMouseLine(Request request) { string sql = $@"Insert into Request (Type, FullName, Email, MouseStrain, GeneticModification, StrainReference, ControlSuggestion) Values ('AddMouseLine', '{HelperService.EscapeSql(request.FullName)}', '{request.Email}', '{HelperService.EscapeSql(request.MouseStrain)}', '{HelperService.EscapeSql(request.GeneticModification)}', '{HelperService.EscapeSql(request.StrainReference)}', '{HelperService.EscapeSql(request.ControlSuggestion)}'); SELECT @@IDENTITY AS 'Identity';"; Int32.Parse(Dal.ExecScalar(sql).ToString()); }
public string GetScheduleCount_Error(int expID, string sessionNameFilter, string SessionName, int minCount, string operand, string str) { string retVal = string.Empty; string sql = GetScheduleNameQuery_MinCount(sessionNameFilter, expID, minCount, operand); string sql2 = GetScheduleNameQuery(sessionNameFilter, expID); int cntSchedule = Int32.Parse(Dal.ExecScalar(sql2).ToString()); if (cntSchedule == 0) { retVal += $@"<tr style=""text-align: center; color:#566573;""> <td style=""text-align: center; color:#909497;"" class=""docs-markdown-td"">No file with Schedule {SessionName} has been uploaded to the database</td>"; retVal += "</tr>"; } else { using (DataTable dt = Dal.GetDataTable(sql)) { foreach (DataRow dr in dt.Rows) { string sqlInner = GetScheduleNameDetailQuery_MinCount(expID, Int16.Parse(dr["animalId"].ToString()), sessionNameFilter); using (DataTable dtInner = Dal.GetDataTable(sqlInner)) { string strFileNames = string.Empty; string strUserAnimalId = string.Empty; IList <string> lstStrFileNames = new List <string>(); IList <string> lstStrDates = new List <string>(); foreach (DataRow drInner in dtInner.Rows) { lstStrFileNames.Add(drInner["UserFileName"].ToString()); lstStrDates.Add(DateTime.Parse(drInner["DateField"].ToString()).ToString("dd/MM/yyyy")); strUserAnimalId = drInner["UserAnimalId"].ToString(); } string fileNameJoined = string.Join(", ", lstStrFileNames); string dateJoined = string.Join(", ", lstStrDates); retVal += $@"<tr style=""text-align: center; color:#566573;""> <td style=""text-align: center; color:#909497;"" class=""docs-markdown-td"">{strUserAnimalId}</td> <td style=""text-align: center; color:#909497;"" class=""docs-markdown-td"">Each animal should have {str} {minCount} {SessionName} in this experiment!</td> <td style=""text-align: center; color:#909497;"" class=""docs-markdown-td"">{fileNameJoined}</td> <td style=""text-align: center; color:#909497;"" class=""docs-markdown-td"">{dateJoined}</td>"; retVal += "</tr>"; } } } } return(retVal); }
public bool DoesExperimentNameExist(string experimentName) { string sql = $"select count(*) from Experiment where ltrim(rtrim(ExpName)) = '{HelperService.EscapeSql(experimentName.Trim())}' "; int countResult = Int32.Parse(Dal.ExecScalar(sql).ToString()); bool flag = (countResult == 0) ? false : true; return(flag); }
public bool DoesAnimalIDExist(string userAnimalId, int expID) { string sql = $"select count(*) from Animal where ltrim(rtrim(UserAnimalID)) = '{userAnimalId.Trim()}' and ExpID = {expID}"; int countResult = Int32.Parse(Dal.ExecScalar(sql).ToString()); bool flag = (countResult == 0) ? false : true; return(flag); }
public (int, bool) GetAnimalIDByUserAnimalIdAndExpId(string editedUserAnimalId, int expId) { string sql = $"Select AnimalID From Animal Where UserAnimalID= '{editedUserAnimalId}' and ExpId={expId}; "; int animalId = Int32.Parse(Dal.ExecScalar(sql).ToString()); string sql2 = $"Select AnimalID From Animal Where AnimalId = '{animalId}' and Sex is not null and gid is not null and sid is not null ; "; int count = Int32.Parse(Dal.ExecScalar(sql2).ToString()); bool isAnimalInfocompleted = (count > 0) ? true : false; return(animalId, isAnimalInfocompleted); }
public bool IsUserAnimalIDExist(string UserAnimalID, int ExpID) { bool flag = false; string sql = $"Select count(*) From Animal Where UserAnimalID= '{UserAnimalID}' and ExpID={ExpID}; "; int count = Int32.Parse(Dal.ExecScalar(sql).ToString()); if (count > 0) { flag = true; } return(flag); }
public int InsertSubExperiment(SubExperiment subexperiment) { string ImageIDcsv = ""; if (subexperiment.ImageIds != null && subexperiment.ImageIds.Length != 0) { ImageIDcsv = String.Join(",", subexperiment.ImageIds.Select(x => x.ToString()).ToArray()); } string sql = $@"Insert into SubExperiment (ExpID, AgeID, SubExpName, IsIntervention, IsDrug, DrugName, DrugUnit, DrugQuantity, InterventionDescription, ImageIds, ImageDescription, Housing, LightCycle) Values ({subexperiment.ExpID}, {subexperiment.AgeID}, '{HelperService.EscapeSql(subexperiment.SubExpName.Trim())}', {(subexperiment.IsIntervention ? 1 : 0)}, {(subexperiment.IsDrug ? 1 : 0)}, '{HelperService.EscapeSql(subexperiment.DrugName)}', '{HelperService.EscapeSql(subexperiment.DrugUnit)}', '{HelperService.EscapeSql(subexperiment.DrugQuantity)}', '{HelperService.EscapeSql(subexperiment.InterventionDescription)}', '{ImageIDcsv}', '{HelperService.EscapeSql(subexperiment.ImageDescription)}', '{HelperService.EscapeSql(subexperiment.Housing)}', '{HelperService.EscapeSql(subexperiment.LightCycle)}'); SELECT @@IDENTITY AS 'Identity';"; return(Int32.Parse(Dal.ExecScalar(sql).ToString())); }
public (bool flagSubExp, bool flagAge) DoesSubExperimentNameExist(SubExperiment subexpObj, int subExpId) { string sqlsubexp = $"select count(*) from SubExperiment where ltrim(rtrim(SubExpName)) = '{HelperService.EscapeSql(subexpObj.SubExpName.Trim())}' {(subExpId == 0 ? "" : " AND SubExpID != " + subexpObj.SubExpID)} "; string sqlage = $@"select count(*) from SubExperiment where AgeID={subexpObj.AgeID} and Isintervention={(subexpObj.IsIntervention ? 1 : 0)} and IsDrug = {(subexpObj.IsDrug ? 1 : 0)} and ltrim(rtrim(DrugName)) = '{HelperService.EscapeSql(subexpObj.DrugName.Trim())}' and DrugUnit = '{HelperService.EscapeSql(subexpObj.DrugUnit.Trim())}' and DrugQuantity = '{HelperService.EscapeSql(subexpObj.DrugQuantity.Trim())}' and CONVERT(VARCHAR, InterventionDescription) = '{HelperService.EscapeSql(subexpObj.InterventionDescription.Trim())}' and Housing = '{HelperService.EscapeSql(subexpObj.Housing.Trim())}' and LightCycle = '{HelperService.EscapeSql(subexpObj.LightCycle.Trim())}' and ExpID = {subexpObj.ExpID} {(subExpId == 0 ? "" : " AND SubExpID != " + subexpObj.SubExpID)} "; int countResultSubExpName = Int32.Parse(Dal.ExecScalar(sqlsubexp).ToString()); int countResultAge = Int32.Parse(Dal.ExecScalar(sqlage).ToString()); bool flagSubExp1 = (countResultSubExpName == 0) ? false : true; bool flagAge1 = (countResultAge == 0) ? false : true; return(flagSubExp : flagSubExp1, flagAge : flagAge1); }
public string GetTaskAnalysisByID(int expid) { string sql = ""; return(Dal.ExecScalar(sql).ToString()); }
public Int32 GetCountOfAnimals() { string sql = "select count(*) from animal Where SID is not Null and GID is not Null and Sex!=''"; return(Int32.Parse(Dal.ExecScalar(sql).ToString())); }