// Function definition to extract list of all strains from Genotype tbl in DB public List <Geno> GetGenoList(int?strainID) { List <int> lstGenoID = new List <int>(); List <Geno> GenoList = new List <Geno>(); string lstGenoIDCsv; if (strainID == null || strainID == 0) { return(GenoList); } lstGenoID = HelperService.GetGenoID(strainID); lstGenoIDCsv = String.Join(",", lstGenoID.Select(x => x.ToString()).ToArray()); using (DataTable dt = Dal.GetDataTable($@"Select * From Genotype where ID in ({lstGenoIDCsv})")) { foreach (DataRow dr in dt.Rows) { GenoList.Add(new Geno { ID = Int32.Parse(dr["ID"].ToString()), Genotype = Convert.ToString(dr["Genotype"].ToString()), Link = Convert.ToString(dr["Link"].ToString()), Description = Convert.ToString(dr["Description"].ToString()), }); } } return(GenoList); }
public List <Animal> GetAnimalByExpID(int expID) { List <Animal> lstAnimal = new List <Animal>(); using (DataTable dt = Dal.GetDataTable($@"SELECT Animal.*, Strain.Strain, Genotype.Genotype From Animal left join Genotype on Genotype.ID = Animal.GID left join Strain on Strain.ID = Animal.SID WHERE ExpID = {expID}")) { foreach (DataRow dr in dt.Rows) { lstAnimal.Add(new Animal { ExpID = Int32.Parse(dr["ExpID"].ToString()), AnimalID = Int32.Parse(dr["AnimalID"].ToString()), UserAnimalID = Convert.ToString(dr["UserAnimalID"].ToString()), SID = HelperService.ConvertToNullableInt(dr["SID"].ToString()), GID = HelperService.ConvertToNullableInt(dr["GID"].ToString()), Sex = Convert.ToString(dr["Sex"].ToString()), Genotype = Convert.ToString(dr["Genotype"].ToString()), Strain = Convert.ToString(dr["Strain"].ToString()), }); } } return(lstAnimal); }
// Function Definition to get Imagepath from ImageIDCsv public static string GetImagePathFromImageIdCsv(string imageIdCsv) { // todo: this shouldn't get the values from db everytime we are calling it if (string.IsNullOrEmpty(imageIdCsv)) { return(""); } string sql = $@"Select * From Image Where Id in ({imageIdCsv}) "; List <string> imageList = new List <string>(); using (DataTable dt = Dal.GetDataTable(sql)) { foreach (DataRow dr in dt.Rows) { imageList.Add( $"<img src ='{Convert.ToString(dr["ImagePath"].ToString())}' width='30' height='30' style='margin-top:15px;' />" ); } } return(string.Join(", ", imageList)); }
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 List <Experiment> GetAllExperiments(string userID) { List <Experiment> lstExperiment = new List <Experiment>(); using (DataTable dt = Dal.GetDataTable($@"select Experiment.*, task.name as TaskName, Species.Species as Species, tt2.PISiteName, STUFF((SELECT ' <br/>' + se.SubExpName FROM SubExperiment se Where se.ExpID = Experiment.ExpID order by se.SubExpName FOR XML PATH(''), type ).value('.', 'nvarchar(max)'),1,6,'') As SubExpNames from Experiment inner join task on task.id = Experiment.taskID inner join species on species.id = Experiment.SpeciesID inner join (Select PUSID, PIUserSite.PSID, tt.PISiteName From PIUserSite inner join (Select PSID, PI.PName, Site.Institution, CONCAT(PName, ' - ', Institution) as PISiteName From PISite inner join PI on PI.PID = PISite.PID inner join Site on Site.SiteID = PISite.SiteID) as tt on tt.PSID = PIUserSite.PSID) as tt2 on tt2.PUSID = Experiment.PUSID WHERE Experiment.UserID = '{userID}'")) { foreach (DataRow dr in dt.Rows) { lstExperiment.Add(new Experiment { ExpID = Int32.Parse(dr["ExpID"].ToString()), PUSID = Int32.Parse(dr["PUSID"].ToString()), ExpName = Convert.ToString(dr["ExpName"].ToString()), SubExperimentNames = Convert.ToString(dr["SubExpNames"].ToString()), StartExpDate = Convert.ToDateTime(dr["StartExpDate"].ToString()), EndExpDate = Convert.ToDateTime(dr["EndExpDate"].ToString()), TaskName = Convert.ToString(dr["TaskName"].ToString()), TaskDescription = Convert.ToString(dr["TaskDescription"].ToString()), //ErrorMessage = Convert.ToString(dr["ErrorMessage"].ToString()), TaskID = Int32.Parse(dr["TaskID"].ToString()), DOI = Convert.ToString(dr["DOI"].ToString()), //IsPostProcessingPass = bool.Parse(dr["IsPostProcessingPass"].ToString()), PISiteName = Convert.ToString(dr["PISiteName"].ToString()), Status = Convert.ToBoolean(dr["Status"]), SpeciesID = Int32.Parse(dr["SpeciesID"].ToString()), Species = Convert.ToString(dr["Species"].ToString()), TaskBattery = Convert.ToString(dr["TaskBattery"].ToString()), MultipleSessions = Convert.ToBoolean(dr["MultipleSessions"].ToString()), RepoGuid = Convert.ToString(dr["RepoGuid"].ToString()), }); } } return(lstExperiment); }
public Users GetUserInfoByID(string userID) { var user = new Users(); using (DataTable dt = Dal.GetDataTable($@"Select GivenName, FamilyName, Email From AspNetUsers Where Id = '{userID}'")) { user = new Users { GivenName = Convert.ToString(dt.Rows[0]["GivenName"].ToString()), FamilyName = Convert.ToString(dt.Rows[0]["FamilyName"].ToString()), Email = Convert.ToString(dt.Rows[0]["Email"].ToString()), }; } return(user); }
public List <Age> GetAgeList() { List <Age> AgeList = new List <Age>(); using (DataTable dt = Dal.GetDataTable($@"select * from age")) { foreach (DataRow dr in dt.Rows) { AgeList.Add(new Age { ID = Int32.Parse(dr["ID"].ToString()), AgeInMonth = Convert.ToString(dr["AgeInMonth"].ToString()), }); } } return(AgeList); }
// Function definition to get image list from DB for PAL and PD public List <Image> GetAllImagesList() { List <Image> lstImages = new List <Image>(); using (DataTable dt = Dal.GetDataTable("select * from Image")) { foreach (DataRow dr in dt.Rows) { lstImages.Add(new Image { ID = Int32.Parse(dr["ID"].ToString()), ImagePath = Convert.ToString(dr["ImagePath"].ToString()), }); } } return(lstImages); }
// Function definition to get image list from DB for PAL and PD public List <Species_> GetAllSpeciesList() { List <Species_> lstSpecies = new List <Species_>(); using (DataTable dt = Dal.GetDataTable("select * from Species")) { foreach (DataRow dr in dt.Rows) { lstSpecies.Add(new Species_ { ID = Int32.Parse(dr["ID"].ToString()), Species = Convert.ToString(dr["Species"].ToString()), }); } } return(lstSpecies); }
// Function definition to extract list of all strains from strain tbl in DB public List <Strains> GetStrainList() { List <Strains> StrainList = new List <Strains>(); using (DataTable dt = Dal.GetDataTable($@"Select * From Strain")) { foreach (DataRow dr in dt.Rows) { StrainList.Add(new Strains { ID = Int32.Parse(dr["ID"].ToString()), Strain = Convert.ToString(dr["Strain"].ToString()), Link = Convert.ToString(dr["Link"].ToString()), }); } } return(StrainList); }
public List <TaskAnalysis> GetAllTaskAnalysis() { List <TaskAnalysis> lstTaskAnalyses = new List <TaskAnalysis>(); using (DataTable dt = Dal.GetDataTable("select * from Task Where ID not in (1,6)")) { foreach (DataRow dr in dt.Rows) { lstTaskAnalyses.Add(new TaskAnalysis { ID = Int32.Parse(dr["ID"].ToString()), Name = Convert.ToString(dr["Name"].ToString()), OriginalName = Convert.ToString(dr["OriginalName"].ToString()), }); } } return(lstTaskAnalyses); }
// Function Defintion: extracting list of PI & institution From PISite tbl in Database (For sign up) public List <PISite> GetPISiteList() { List <PISite> lstPISite = new List <PISite>(); using (DataTable dt = Dal.GetDataTable($@"Select PSID, PI.PName, Site.Institution, CONCAT(PName, ' - ', Institution) as PISiteName From PISite inner join PI on PI.PID = PISite.PID inner join Site on Site.SiteID = PISite.SiteID")) { foreach (DataRow dr in dt.Rows) { lstPISite.Add(new PISite { PSID = Int32.Parse(dr["PSID"].ToString()), PName = Convert.ToString(dr["PName"].ToString()), Institution = Convert.ToString(dr["Institution"].ToString()), PISiteName = Convert.ToString(dr["PISiteName"].ToString()), }); } } return(lstPISite); }
public List <SubExperiment> GetAllSubExperimentsByExpID(int ExpID) { List <SubExperiment> SubExpList = new List <SubExperiment>(); using (DataTable dt = Dal.GetDataTable($@"Select se.SubExpID, se.ExpID, se.AgeID, se.SubExpName, se.IsPostProcessingPass, se.ErrorMessage, Age.AgeInMonth, se.IsIntervention, se.IsDrug, se.DrugName, se.DrugUnit, se.DrugQuantity, se.InterventionDescription, se.ImageIds, se.ImageDescription, se.Housing, se.LightCycle From SubExperiment se Inner Join Age On Age.ID = se.AgeID Where ExpID = {ExpID} order by Age.ID")) { foreach (DataRow dr in dt.Rows) { var imageIds = Convert.ToString(dr["ImageIds"].ToString()); SubExpList.Add(new SubExperiment { SubExpID = Int32.Parse(dr["SubExpID"].ToString()), ExpID = Int32.Parse(dr["ExpID"].ToString()), SubExpName = Convert.ToString(dr["SubExpName"].ToString()), AgeID = Int32.Parse(dr["AgeID"].ToString()), AgeInMonth = Convert.ToString(dr["AgeInMonth"].ToString()), IsPostProcessingPass = bool.Parse(dr["IsPostProcessingPass"].ToString()), ErrorMessage = Convert.ToString(dr["ErrorMessage"].ToString()), IsIntervention = bool.Parse(dr["IsIntervention"].ToString()), IsDrug = bool.Parse(dr["IsDrug"].ToString()), DrugName = Convert.ToString(dr["DrugName"].ToString()), DrugQuantity = Convert.ToString(dr["DrugQuantity"].ToString()), DrugUnit = Convert.ToString(dr["DrugUnit"].ToString()), InterventionDescription = Convert.ToString(dr["InterventionDescription"].ToString()), ImageIds = string.IsNullOrEmpty(imageIds) ? null : imageIds.Split(',').Select(s => Int32.Parse(s)).ToArray(), ImageInfo = $@"{GetImagePathFromImageIdCsv(Convert.ToString(dr["ImageIds"]))}", ImageDescription = Convert.ToString(dr["ImageDescription"].ToString()), Housing = Convert.ToString(dr["Housing"].ToString()), LightCycle = Convert.ToString(dr["LightCycle"].ToString()), }); } } return(SubExpList); }
// Function Defintion: Extracting list of PI & Institutions for the login User (for exp creation) public List <PISite> GetPISitebyUserID(string UserID) { List <PISite> lstPISite = new List <PISite>(); using (DataTable dt = Dal.GetDataTable($@"Select PUSID, PIUserSite.PSID, tt.PISiteName From PIUserSite inner join (Select PSID, PI.PName, Site.Institution, CONCAT(PName, ' - ', Institution) as PISiteName From PISite inner join PI on PI.PID = PISite.PID inner join Site on Site.SiteID = PISite.SiteID) as tt on tt.PSID = PIUserSite.PSID Where PIUserSite.UserID = '{UserID}'")) { foreach (DataRow dr in dt.Rows) { lstPISite.Add(new PISite { PUSID = Int32.Parse(dr["PUSID"].ToString()), PSID = Int32.Parse(dr["PSID"].ToString()), PISiteName = Convert.ToString(dr["PISiteName"].ToString()), }); } } return(lstPISite); }
public List <SearchExp> GetAllExpList() { List <SearchExp> lstExp = new List <SearchExp>(); using (DataTable dt = Dal.GetDataTable($@"select Experiment.ExpName, task.name as CognitiveTask, Case Experiment.Status When 1 Then 'Public' When 0 Then 'Private' End as Status, STUFF((SELECT distinct ' <br/>' + Age.AgeInMonth FROM SubExperiment inner join Age on Age.ID = SubExperiment.AgeID Where SubExperiment.ExpID = Experiment.ExpID --order by Age.AgeInMonth FOR XML PATH(''), type ).value('.', 'nvarchar(max)'),1,6,'') As Age, STUFF((SELECT distinct ' <br/>' + Strain.Strain FROM Animal inner join Strain on Strain.ID = Animal.SID Where Animal.ExpID = Experiment.ExpID FOR XML PATH(''), type ).value('.', 'nvarchar(max)'),1,6,'') As Strain, STUFF((SELECT distinct ', ' + Genotype.Genotype FROM Animal inner join Genotype on Genotype.ID = Animal.GID Where Animal.ExpID = Experiment.ExpID FOR XML PATH(''), type ).value('.', 'nvarchar(max)'),1,2,'') As Genotype, Experiment.TaskDescription, Concat(Experiment.StartExpDate, '-', Experiment.EndExpDate) as Period ,tt2.PISiteName, tt2.UserName as Username, tt2.Email, Experiment.DOI From Experiment inner join task on task.ID = Experiment.TaskID inner join (Select PUSID, PIUserSite.PSID, tt.PISiteName, CONCAT(AspNetUsers.GivenName, ' ', AspNetUsers.FamilyName) as UserName, AspNetUsers.Email as Email From PIUserSite inner join (Select PSID, PI.PName, Site.Institution, CONCAT(PName, ' - ', Institution) as PISiteName From PISite inner join PI on PI.PID = PISite.PID inner join Site on Site.SiteID = PISite.SiteID ) as tt on tt.PSID = PIUserSite.PSID inner join AspNetUsers on AspNetUsers.Id = PIUserSite.UserID) as tt2 on tt2.PUSID = Experiment.PUSID" )) { foreach (DataRow dr in dt.Rows) { lstExp.Add(new SearchExp { ExpName = Convert.ToString(dr["ExpName"].ToString()), CognitiveTask = Convert.ToString(dr["CognitiveTask"].ToString()), Status = Convert.ToString(dr["Status"].ToString()), Age = Convert.ToString(dr["Age"].ToString()), Strain = Convert.ToString(dr["Strain"].ToString()), Genotype = Convert.ToString(dr["Genotype"].ToString()), TaskDescription = Convert.ToString(dr["TaskDescription"].ToString()), Period = Convert.ToString(dr["Period"].ToString()), PISiteName = Convert.ToString(dr["PISiteName"].ToString()), Username = Convert.ToString(dr["Username"].ToString()), Email = Convert.ToString(dr["Email"].ToString()), DOI = Convert.ToString(dr["DOI"].ToString()), }); } } return(lstExp); }