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