// 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);
}