public void UpdateExp(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 = $@"UPDATE Experiment " +
$"SET PUSID = {experiment.PUSID}, ExpName = '{HelperService.EscapeSql(experiment.ExpName)}', StartExpDate = '{experiment.StartExpDate}'," +
$"EndExpDate = '{experiment.EndExpDate}', SpeciesID = {experiment.SpeciesID}, TaskDescription = '{HelperService.EscapeSql(experiment.TaskDescription)}'," +
$" DOI = '{HelperService.EscapeSql(experiment.DOI)}', TaskBattery = '{HelperService.EscapeSql(experiment.TaskBattery)}', Status = {(experiment.Status ? 1 : 0)}," +
$" MultipleSessions = {(experiment.MultipleSessions ? 1 : 0)}, RepoGuid = {repoString}" +
$" WHERE ExpID = {experiment.ExpID} AND UserID = '{userID}';";
if (experiment.Status)
{
string strBody = $@"Hi Admin: <br /><br /> User with Email address <b>{Email}</b> has just changed the status of the experiment: <b>{HelperService.EscapeSql(experiment.ExpName.Trim())}</b>
to public! <br /><br /> Thanks <br /> MouseBytes";
HelperService.SendEmail("", "", "Status of experiment changed to public!", strBody);
}
Dal.ExecuteNonQuery(sql);
}
private void SetPostProcessingPassed(int SubxpID)
{
string sql = $@"UPDATE SubExperiment
SET IsPostProcessingPass = 1, ErrorMessage = ''
WHERE SubExpID = {SubxpID} ;";
Dal.ExecuteNonQuery(sql);
}
private void SetPostProcessingError(int SubxpID, string error)
{
string sql = $@"UPDATE SubExperiment
SET IsPostProcessingPass = 0, ErrorMessage = '{error}'
WHERE SubExpID = {SubxpID} ;";
Dal.ExecuteNonQuery(sql);
}
//Function Definition for updating Animal info
public void UpdateAnimal(Animal animal)
{
// For Daniel (UserAnimalID = '{animal.UserAnimalID}' should be removed from the sql query)
string sql = $"UPDATE Animal " +
$"SET Sex = '{animal.Sex}'," +
$"GID = '{animal.GID}', SID = '{animal.SID}' WHERE AnimalID = {animal.AnimalID} ;";
Dal.ExecuteNonQuery(sql);
}
public bool ReplaceAnimalId(int oldAnimalId, int existingAnimalIdToUse)
{
string sql = $"Update Upload Set AnimalId = {existingAnimalIdToUse} Where AnimalId= {oldAnimalId}; ";
Dal.ExecuteNonQuery(sql);
DeleteAnimalByAnimalID(oldAnimalId);
return(true);
}
// Function Definition: Insert the PI and institution that user selected in "sign up" to tbl PIUSERSite
internal void InsertPiSiteIds(string userId, List <int> selectedPiSiteIds)
{
// add selectedPiSiteIds to this user
foreach (var PiSiteID in selectedPiSiteIds)
{
string sql = $@"Insert into PIUserSite (PSID, UserID) Values ({PiSiteID}, '{userId}'); ";
Dal.ExecuteNonQuery(sql);
}
}
public void DeleteExpByUploadID(int uploadID)
{
string sql = $@"Delete From RBT_TouchScreen_Features Where SessionID in (Select SessionID From SessionInfo Where UploadID = {uploadID});
Delete From rbt_data_cached Where SessionID in (Select SessionID From SessionInfo Where UploadID = {uploadID});
Delete From SessionInfo_Dynamic Where SessionID in (Select SessionID From SessionInfo Where UploadID = {uploadID});
Delete From SessionInfo Where UploadID = {uploadID};
Delete From Upload Where UploadID = {uploadID};";
Dal.ExecuteNonQuery(sql);
}
public void DeleteExpByExpID(int expID)
{
string sql = $@"Delete From RBT_TouchScreen_Features Where SessionID in (Select SessionID From SessionInfo Where ExpID = {expID});
Delete From rbt_data_cached Where SessionID in (Select SessionID From SessionInfo Where ExpID = {expID});
Delete From SessionInfo_Dynamic Where SessionID in (Select SessionID From SessionInfo Where ExpID = {expID});
Delete From SessionInfo Where ExpID = {expID};
Delete From Upload Where ExpID ={expID};
Delete From Animal Where ExpID ={expID};
Delete From UploadErrorLog Where ExpID ={expID};
Delete From SubExperiment Where ExpId = {expID};
Delete From Experiment Where ExpID={expID};";
Dal.ExecuteNonQuery(sql);
}
//Function Definition for updating the user profile
public void UpdateProfile(CreateViewModel model, string userID)
{
// For Daniel (UserAnimalID = '{animal.UserAnimalID}' should be removed from the sql query)
string sql = $@"UPDATE AspNetUsers set GivenName = '{model.givenName}', FamilyName = '{model.familyName}' Where Id = '{userID}'";
Dal.ExecuteNonQuery(sql);
if (model.selectedPiSiteIds != null)
{
foreach (var PiSiteID in model.selectedPiSiteIds)
{
string sql2 = $@"Insert into PIUserSite (PSID, UserID) Values ({PiSiteID}, '{userID}'); ";
Dal.ExecuteNonQuery(sql2);
}
}
}
public string CheckPostProcessingQC(SubExperiment subExp)
{
if (string.IsNullOrEmpty(subExp.ErrorMessage))
{
return(RunPostProcessing(subExp));
}
else
{
// connect to DB, empty Erroressage field in SubExsp table, then call RunPostProcessin Function
string sql = $@"UPDATE SubExperiment
SET ErrorMessage = ''
WHERE ExpID = {subExp.SubExpID} ;";
Dal.ExecuteNonQuery(sql);
// Run post processing
return(RunPostProcessing(subExp));
}
}
public void UpdateSubExperiment(SubExperiment subexperiment)
{
string ImageIDcsv = "";
if (subexperiment.ImageIds != null && subexperiment.ImageIds.Length != 0)
{
ImageIDcsv = String.Join(",", subexperiment.ImageIds.Select(x => x.ToString()).ToArray());
}
string sql = $@"UPDATE SubExperiment
SET SubExpName = '{HelperService.EscapeSql(subexperiment.SubExpName.Trim())}', AgeID = {subexperiment.AgeID},
IsIntervention = {(subexperiment.IsIntervention ? 1 : 0)}, IsDrug = {(subexperiment.IsDrug ? 1 : 0)}, DrugName = '{HelperService.EscapeSql(subexperiment.DrugName)}',
DrugUnit = '{HelperService.EscapeSql(subexperiment.DrugUnit)}', DrugQuantity= '{HelperService.EscapeSql(subexperiment.DrugQuantity)}',
InterventionDescription = '{HelperService.EscapeSql(subexperiment.InterventionDescription)}',
ImageIds = '{ImageIDcsv}', ImageDescription = '{subexperiment.ImageDescription}',
Housing = '{subexperiment.Housing}', LightCycle = '{subexperiment.LightCycle}'
WHERE SubExpID = {subexperiment.SubExpID} ;";
Dal.ExecuteNonQuery(sql);
}