//Loading filtered data
private void LoadData(string name)
{
using (var db = new ApirsDatabase())
{
try
{
Drillings = new BindableCollection <tblDrilling>(from drill in db.tblDrillings
where drill.drillName == name
select drill);
if (Drillings.Count == 0)
{
SelectedDrilling = new tblDrilling();
SelectedDrilling.drillName = name;
}
else if (Drillings.Count > 1)
{
SelectedDrilling = Drillings.First();
}
else
{
SelectedDrilling = Drillings.First();
}
}
catch
{
Drillings = new BindableCollection <tblDrilling>();
SelectedDrilling = new tblDrilling();
}
}
}
/// <summary>
/// Adding a country files kml to sql server
/// </summary>
public static void AddCountryKml(string kmlFile)
{
var fileStream = File.Open(kmlFile, FileMode.Open);
var mapper = new Kml2SqlMapper(fileStream);
var ap = new ApirsDatabase();
using (var connection = new SqlConnection(ap.Database.Connection.ConnectionString))
{
connection.Open();
var createTableCommand = mapper.GetCreateTableCommand(connection);
createTableCommand.ExecuteNonQuery();
foreach (var mapFeature in mapper.GetMapFeatures())
{
try
{
var command = mapFeature.GetInsertCommand();
command.Connection = connection;
command.ExecuteNonQuery();
}
catch
{
continue;
}
}
}
}
/// <summary>
/// Connects to the database, reads,
/// prints results to the console.
/// </summary>
public static bool TryAccessDatabase()
{
//throw new TestSqlException(4060); //(7654321); // Uncomment for testing.
using (var db = new ApirsDatabase(false))
{
string connectionString = db.Database.Connection.ConnectionString;
using (SqlConnection connection = new SqlConnection())
{
connection.ConnectionString = connectionString;
try
{
SqlExtensions.QuickOpen(connection, 1000);
}
catch (SqlException ex)
{
return(false);
}
catch (Exception e)
{
return(false);
}
return(true);
}
}
}
/// <summary>
/// Unsubscribing the actual user based on the project id
/// </summary>
/// <param name="id"></param>
public void Unsubscribe(int id)
{
if ((bool)((ShellViewModel)IoC.Get <IShell>()).LocalMode)
{
((ShellViewModel)IoC.Get <IShell>()).ShowInformation("You can't unsubscribe in local mode.");
return;
}
try
{
using (var db = new ApirsDatabase())
{
db.Database.ExecuteSqlCommand("DELETE FROM tblPersonsProjects WHERE persIdFk="
+ ((int)((ShellViewModel)IoC.Get <IShell>()).UserId).ToString()
+ " AND prjIdFk ="
+ id
+ ";");
}
_events.PublishOnUIThreadAsync(new ChangeUserMessage((int)((ShellViewModel)IoC.Get <IShell>()).UserId, ""));
Refresh();
}
catch
{
((ShellViewModel)IoC.Get <IShell>()).ShowError("An unexpeced error occured.");
}
}
//Adding a participant to a project
public void AddParticipant()
{
if ((bool)((ShellViewModel)IoC.Get <IShell>()).LocalMode)
{
((ShellViewModel)IoC.Get <IShell>()).ShowInformation("Users can't be added in local mode.");
return;
}
try
{
if (!DataValidation.CheckPrerequisites(CRUD.AddToObject, SelectedProject, (int)SelectedProject.prjCreatorIdFk, SelectedProject.prjIdPk))
{
return;
}
}
catch
{
return;
}
try
{
//Opening a dialog window with all available persons excluding those that already participate the project
PersonsViewModel personsViewModel = new PersonsViewModel(SelectedProject.prjIdPk);
WindowManager windowManager = new WindowManager();
windowManager.ShowDialog(personsViewModel);
if (personsViewModel.SelectedPerson != null)
{
var selectedPerson = personsViewModel.SelectedPerson;
using (var db = new ApirsDatabase())
{
db.Database.ExecuteSqlCommand("INSERT INTO tblPersonsProjects(persIdFk, prjIdFk) VALUES ("
+ selectedPerson.persIdPk.ToString()
+ ", "
+ SelectedProject.prjIdPk.ToString()
+ ");");
}
_events.PublishOnUIThreadAsync(new SendUserMessageMessage
(
26,
selectedPerson.persIdPk,
"New project", "You were added to the project " + SelectedProject.prjName + ".",
DateTime.Now
));
Refresh();
}
else
{
return;
}
}
catch (Exception e)
{
((ShellViewModel)IoC.Get <IShell>()).ShowError("An unexpeced error occured.");
}
}
/// <summary>
/// Removing a participant based on his id and the actually selected project
/// </summary>
/// <param name="id"></param>
public void RemoveParticipant(int id)
{
if ((bool)((ShellViewModel)IoC.Get <IShell>()).LocalMode)
{
((ShellViewModel)IoC.Get <IShell>()).ShowInformation("Users can't be removed in local mode.");
return;
}
try
{
if (!DataValidation.CheckPrerequisites(CRUD.AddToObject, SelectedProject, (int)SelectedProject.prjCreatorIdFk, SelectedProject.prjIdPk))
{
return;
}
}
catch
{
return;
}
if (SelectedProject != null)
{
try
{
using (var db = new ApirsDatabase())
{
db.Database.ExecuteSqlCommand("DELETE FROM tblPersonsProjects WHERE persIdFk="
+ id.ToString()
+ " AND prjIdFk ="
+ SelectedProject.prjIdPk.ToString()
+ ";");
}
_events.PublishOnUIThreadAsync(new SendUserMessageMessage
(
26,
id,
"Removed from project " + SelectedProject.prjName,
"You were removed from the project " + SelectedProject.prjName,
DateTime.Now
));
Refresh();
}
catch
{
((ShellViewModel)IoC.Get <IShell>()).ShowError("An unexpeced error occured.");
}
}
else
{
return;
}
}
private void LoadData()
{
try
{
using (var db = new ApirsDatabase())
{
var user = db.tblPersons.SqlQuery("SELECT * FROM tblPersons WHERE persIdPk=" + (int)((ShellViewModel)IoC.Get <IShell>()).UserId + ";").ToList();
SelectedPerson = user.First();
}
}
catch (Exception e)
{
selectedPerson = new tblPerson();
}
}
//Converting back again
public override object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture)
{
using (var db = new ApirsDatabase())
{
try
{
string column = (from a in db.tblAlias
where a.alAlias == (string)value
select a.alColumnName).First();
return((column != "") ? column : value);
}
catch
{
return(value);
}
}
}
//From column name to alias
public string Convert(string value)
{
using (var db = new ApirsDatabase())
{
try
{
string alias = (from a in db.tblAlias
where a.alColumnName.Contains((string)value)
select a.alAlias).First();
return((alias != "") ? alias.Replace("\r\n", "").Replace("\n", "").Replace("\r", "") : value);
}
catch (Exception e)
{
return(value);
}
}
}
// Commit changes from the new object form
// or edits made to the existing object form.
public void Update()
{
using (var db = new ApirsDatabase())
{
try
{
if (SelectedDrilling.drillIdPk == 0)
{
try
{
db.tblDrillings.Add(SelectedDrilling);
db.SaveChanges();
TryClose();
}
catch
{
((ShellViewModel)IoC.Get <IShell>()).ShowInformation("Object can't be added. Please check every field again.");
return;
}
}
else
{
tblDrilling result = db.tblDrillings.Where(drill => drill.drillName == SelectedDrilling.drillName).First();
if (result != null)
{
db.Entry <tblDrilling>(result).CurrentValues.SetValues(SelectedDrilling);
db.SaveChanges();
}
}
}
catch (SqlException ex)
{
((ShellViewModel)IoC.Get <IShell>()).ShowInformation("Please provide valid input parameters");
}
catch (Exception e)
{
((ShellViewModel)IoC.Get <IShell>()).ShowInformation("Something went wrong");
}
finally
{
}
}
}
/// <summary>
/// Uploading a filestream to the server
/// </summary>
/// <param name="fileStreamByte"></param>
/// <returns></returns>
public static Guid UploadFile(string filePath)
{
if (filePath != "")
{
//Getting file information
FileInfo fi = new FileInfo(filePath);
FileStream fs;
try
{
//Implementing a new filestream
fs = new FileStream(fi.FullName, FileMode.Open, FileAccess.Read);
}
catch
{
MessageBox.ShowError("File is being used by another process.");
return(new Guid());
}
//Transfering filestream into binary format
BinaryReader rdr = new BinaryReader(fs);
byte[] fileData = rdr.ReadBytes((int)fs.Length);
//Closing filestream
rdr.Close();
fs.Close();
try
{
//Instantiate database
using (ApirsDatabase db = new ApirsDatabase())
{
//Retrieving file meta data
string fileName = fi.Name;
string fileNameWithoutExtension = Path.GetFileNameWithoutExtension(fileName);
string extension = fi.Extension;
char charac = 'a';
//Changing File name based on the count of occurences
while (db.v_FileStore.Where(x => x.name == fileName).Count() > 0)
{
fileName = fileNameWithoutExtension + charac + extension;
charac++;
}
//Establishing a sql connection
using (SqlConnection SqlConn = new SqlConnection(db.Database.Connection.ConnectionString.ToString()))
{
SqlCommand spAddFile = new SqlCommand("dbo.spAddFile", SqlConn);
//Testing if a connection is established
//Normally: if (sv.IsNetworkAvailable() && sv.IsServerConnected("130.83.96.87"))
if (ServerInteractionHelper.IsNetworkAvailable())
{
//Preparing the stored procedure
spAddFile.CommandType = CommandType.StoredProcedure;
//Adding the parameters
spAddFile.Parameters.Add("@pName", SqlDbType.NVarChar, 255).Value = fileName;
spAddFile.Parameters.Add("@pFile_Stream", SqlDbType.Image, fileData.Length).Value = fileData;
//Opening the connection
SqlConn.Open();
Guid result = (Guid)spAddFile.ExecuteScalar();
SqlConn.Close();
db.SaveChanges();
return(result);
}
}
}
}
catch (Exception e)
{
MessageBox.ShowError(UserMessageValueConverter.ConvertBack(1));
return(new Guid());
}
}
return(new Guid());
}
//Exporting a generic list based on its type and the additional parameter provided
public static async void ExportList <T>(IList <T> List, string path, string AdditionalParameter = "")
{
//Getting the type of the generic list
Type typeParameterType = typeof(T);
//Switching the type
switch (typeParameterType.ToString())
{
case ("APIRS.tblRockSample"):
//Casing the generic list to an object list
List <tblRockSample> myList = List.Cast <tblRockSample>().ToList();
switch (AdditionalParameter)
{
case ("Plug"):
await Task.Run(() =>
{
try
{
tblPlug query;
List <tblPlug> plugs = new List <tblPlug>();
using (var db = new ApirsDatabase())
{
//The rock sample name
string label = "";
//Iterating through all participating projects and select all related rock samples and facies types
foreach (var samp in List)
{
try
{
label = samp.GetType().GetProperty("sampLabel").GetValue(samp).ToString();
query = (from plug in db.tblPlugs
where plug.plugLabel == label
select plug).First();
plugs.Add(query);
}
catch
{
plugs.Add(new tblPlug()
{
plugLabel = label
});
}
}
}
//Converting data to data tables
DataTable rockSampleTable = CollectionHelper.ConvertTo <tblRockSample>(myList);
DataTable plugTable = CollectionHelper.ConvertTo <tblPlug>(plugs);
DataTable joinPlug = CollectionHelper.JoinDataTables(rockSampleTable, plugTable, (row1, row2) => row1.Field <string>("sampLabel") == row2.Field <string>("plugLabel"));
//Exporting data to a csv
ExportHelper.ExportDataTableToCsv(joinPlug, path, false);
}
catch (Exception e)
{
MessageBox.ShowError("An unexpected error occured.");
}
});
break;
case ("Cuboid"):
await Task.Run(() =>
{
try
{
tblCuboid query;
List <tblCuboid> cubs = new List <tblCuboid>();
using (var db = new ApirsDatabase())
{
//The rock sample name
string label = "";
//Iterating through all participating projects and select all related rock samples and facies types
foreach (var samp in List)
{
try
{
label = samp.GetType().GetProperty("sampLabel").GetValue(samp).ToString();
query = (from cub in db.tblCuboids
where cub.cubLabel == label
select cub).First();
cubs.Add(query);
}
catch
{
cubs.Add(new tblCuboid()
{
cubLabel = label
});
}
}
}
//Converting data to data tables
DataTable rockSampleTable = CollectionHelper.ConvertTo <tblRockSample>(myList);
DataTable cubTable = CollectionHelper.ConvertTo <tblCuboid>(cubs);
DataTable joinCub = CollectionHelper.JoinDataTables(rockSampleTable, cubTable, (row1, row2) => row1.Field <string>("sampLabel") == row2.Field <string>("cubLabel"));
//Exporting data to a csv
ExportHelper.ExportDataTableToCsv(joinCub, path, false);
}
catch
{
MessageBox.ShowError("An unexpected error occured.");
}
});
break;
case ("Handpiece"):
await Task.Run(() =>
{
try
{
tblHandpiece query;
List <tblHandpiece> hps = new List <tblHandpiece>();
using (var db = new ApirsDatabase())
{
//The rock sample name
string label = "";
//Iterating through all participating projects and select all related rock samples and facies types
foreach (var samp in List)
{
try
{
label = samp.GetType().GetProperty("sampLabel").GetValue(samp).ToString();
query = (from hp in db.tblHandpieces
where hp.hpLabelFk == label
select hp).First();
hps.Add(query);
}
catch
{
hps.Add(new tblHandpiece()
{
hpLabelFk = label
});
}
}
}
//Converting data to data tables
DataTable rockSampleTable = CollectionHelper.ConvertTo <tblRockSample>(myList);
DataTable cubTable = CollectionHelper.ConvertTo <tblHandpiece>(hps);
DataTable joinCub = CollectionHelper.JoinDataTables(rockSampleTable, cubTable, (row1, row2) => row1.Field <string>("sampLabel") == row2.Field <string>("hpLabelFk"));
//Exporting data to a csv
ExportHelper.ExportDataTableToCsv(joinCub, path, false);
}
catch
{
MessageBox.ShowError("An unexpected error occured.");
}
});
break;
case ("All"):
case ("Sediment/Soil"):
await Task.Run(() =>
{
try
{
//Converting data to data tables
DataTable rockSampleTable = CollectionHelper.ConvertTo <tblRockSample>(myList);
//Exporting data to a csv
ExportHelper.ExportDataTableToCsv(rockSampleTable, path, false);
}
catch
{
MessageBox.ShowError("An unexpected error occured.");
}
});
break;
case ("LabMeasurement"):
await Task.Run(() =>
{
try
{
v_PetrophysicsRockSamples query;
List <v_PetrophysicsRockSamples> petrophysics = new List <v_PetrophysicsRockSamples>();
using (var db = new ApirsDatabase())
{
//The rock sample name
string label = "";
//Iterating through all participating projects and select all related rock samples and facies types
foreach (var samp in List)
{
try
{
label = samp.GetType().GetProperty("sampLabel").GetValue(samp).ToString();
query = (from pet in db.v_PetrophysicsRockSamples
where pet.labmeSampleName == label
select pet).First();
petrophysics.Add(query);
}
catch
{
petrophysics.Add(new v_PetrophysicsRockSamples()
{
labmeSampleName = label
});
}
}
}
//Converting data to data tables
DataTable rockSampleTable = CollectionHelper.ConvertTo <tblRockSample>(myList);
DataTable petrophysicsTable = CollectionHelper.ConvertTo <v_PetrophysicsRockSamples>(petrophysics);
DataTable joinPetrophysics = CollectionHelper.JoinDataTables(rockSampleTable, petrophysicsTable, (row1, row2) => row1.Field <string>("sampLabel") == row2.Field <string>("labmeSampleName"));
//Exporting data to a csv
ExportHelper.ExportDataTableToCsv(joinPetrophysics, path, false);
}
catch (Exception e)
{
MessageBox.ShowError("An unexpected error occured.");
}
});
break;
}
break;
case "APIRS.tblObjectOfInvestigation":
//Casing the generic list to an object list
List <tblObjectOfInvestigation> myOoiList = List.Cast <tblObjectOfInvestigation>().ToList();
switch (AdditionalParameter)
{
case ("Outcrop"):
await Task.Run(() =>
{
try
{
tblOutcrop query;
List <tblOutcrop> outcrops = new List <tblOutcrop>();
using (var db = new ApirsDatabase())
{
//The rock sample name
string label = "";
//Iterating through all participating projects and select all related rock samples and facies types
foreach (var samp in List)
{
try
{
label = samp.GetType().GetProperty("ooiName").GetValue(samp).ToString();
query = (from outc in db.tblOutcrops
where outc.outLocalName == label
select outc).First();
outcrops.Add(query);
}
catch
{
outcrops.Add(new tblOutcrop()
{
outLocalName = label
});
}
}
}
//Converting data to data tables
DataTable ooiTable = CollectionHelper.ConvertTo <tblObjectOfInvestigation>(myOoiList);
DataTable outTable = CollectionHelper.ConvertTo <tblOutcrop>(outcrops);
DataTable joinOut = CollectionHelper.JoinDataTables(ooiTable, outTable, (row1, row2) => row1.Field <string>("ooiName") == row2.Field <string>("outLocalName"));
//Exporting data to a csv
ExportHelper.ExportDataTableToCsv(joinOut, path, false);
}
catch
{
MessageBox.ShowError("An unexpected error occured.");
}
});
break;
case ("Drilling"):
await Task.Run(() =>
{
try
{
tblDrilling query;
List <tblDrilling> drillings = new List <tblDrilling>();
using (var db = new ApirsDatabase())
{
//The rock sample name
string label = "";
//Iterating through all participating projects and select all related rock samples and facies types
foreach (var samp in List)
{
try
{
label = samp.GetType().GetProperty("ooiName").GetValue(samp).ToString();
query = (from drill in db.tblDrillings
where drill.drillName == label
select drill).First();
drillings.Add(query);
}
catch
{
drillings.Add(new tblDrilling()
{
drillName = label
});
}
}
}
//Converting data to data tables
DataTable ooiTable = CollectionHelper.ConvertTo <tblObjectOfInvestigation>(myOoiList);
DataTable drillTable = CollectionHelper.ConvertTo <tblDrilling>(drillings);
DataTable joindrill = CollectionHelper.JoinDataTables(ooiTable, drillTable, (row1, row2) => row1.Field <string>("ooiName") == row2.Field <string>("drillName"));
//Exporting data to a csv
ExportHelper.ExportDataTableToCsv(joindrill, path, false);
}
catch
{
MessageBox.ShowError("An unexpected error occured.");
}
});
break;
case ("Transect"):
await Task.Run(() =>
{
try
{
tblTransect query;
List <tblTransect> transects = new List <tblTransect>();
using (var db = new ApirsDatabase())
{
//The rock sample name
string label = "";
//Iterating through all participating projects and select all related rock samples and facies types
foreach (var samp in List)
{
try
{
label = samp.GetType().GetProperty("ooiName").GetValue(samp).ToString();
query = (from tra in db.tblTransects
where tra.traName == label
select tra).First();
transects.Add(query);
}
catch
{
transects.Add(new tblTransect()
{
traName = label
});
}
}
}
//Converting data to data tables
DataTable ooiTable = CollectionHelper.ConvertTo <tblObjectOfInvestigation>(myOoiList);
DataTable traTable = CollectionHelper.ConvertTo <tblTransect>(transects);
DataTable joinTra = CollectionHelper.JoinDataTables(ooiTable, traTable, (row1, row2) => row1.Field <string>("ooiName") == row2.Field <string>("traName"));
//Exporting data to a csv
ExportHelper.ExportDataTableToCsv(joinTra, path, false);
}
catch
{
MessageBox.ShowError("An unexpected error occured.");
}
});
break;
case ("All"):
await Task.Run(() =>
{
try
{
//Converting data to data tables
DataTable ooiTable = CollectionHelper.ConvertTo <tblObjectOfInvestigation>(myOoiList);
//Exporting data to a csv
ExportHelper.ExportDataTableToCsv(ooiTable, path, false);
}
catch
{
MessageBox.ShowError("An unexpected error occured.");
}
});
break;
}
break;
}
}
//Exporting a generic list based on its type and the additional parameter provided
public static async void ExportList <T>(IList <T> List, string path, string AdditionalParameter = "")
{
//Getting the type of the generic list
Type typeParameterType = typeof(T);
//Switching the type
switch (typeParameterType.ToString())
{
case ("GeoReVi.UnivariateHeterogeneityMeasuresHelper"):
//Casing the generic list to an object list
List <UnivariateHeterogeneityMeasuresHelper> aList = List.Cast <UnivariateHeterogeneityMeasuresHelper>().ToList();
await Task.Run(() =>
{
try
{
DataTable statTable = CollectionHelper.ConvertTo <UnivariateHeterogeneityMeasuresHelper>(aList);
//Exporting data to a csv
ExportDataTableToCsv(statTable, path, false, false);
}
catch
{
((ShellViewModel)IoC.Get <IShell>()).ShowError("An unexpected error occured.");
}
});
break;
case ("GeoReVi.tblRockSample"):
//Casing the generic list to an object list
List <tblRockSample> myList = List.Cast <tblRockSample>().ToList();
switch (AdditionalParameter)
{
case ("Plug"):
await Task.Run(() =>
{
try
{
tblPlug query;
List <tblPlug> plugs = new List <tblPlug>();
using (var db = new ApirsDatabase())
{
//The rock sample name
string label = "";
//Iterating through all participating projects and select all related rock samples and facies types
foreach (var samp in List)
{
try
{
label = samp.GetType().GetProperty("sampLabel").GetValue(samp).ToString();
query = (from plug in db.tblPlugs
where plug.plugLabel == label
select plug).First();
plugs.Add(query);
}
catch
{
plugs.Add(new tblPlug()
{
plugLabel = label
});
}
}
}
//Converting data to data tables
DataTable rockSampleTable = CollectionHelper.ConvertTo <tblRockSample>(myList);
DataTable plugTable = CollectionHelper.ConvertTo <tblPlug>(plugs);
DataTable joinPlug = CollectionHelper.JoinDataTables(rockSampleTable, plugTable, (row1, row2) => row1.Field <string>("sampLabel") == row2.Field <string>("plugLabel"));
//Exporting data to a csv
ExportDataTableToCsv(joinPlug, path, false);
}
catch
{
throw new Exception("An unexpected error occured.");
}
});
break;
case ("Cuboid"):
await Task.Run(() =>
{
try
{
tblCuboid query;
List <tblCuboid> cubs = new List <tblCuboid>();
using (var db = new ApirsDatabase())
{
//The rock sample name
string label = "";
//Iterating through all participating projects and select all related rock samples and facies types
foreach (var samp in List)
{
try
{
label = samp.GetType().GetProperty("sampLabel").GetValue(samp).ToString();
query = (from cub in db.tblCuboids
where cub.cubLabel == label
select cub).First();
cubs.Add(query);
}
catch
{
cubs.Add(new tblCuboid()
{
cubLabel = label
});
}
}
}
//Converting data to data tables
DataTable rockSampleTable = CollectionHelper.ConvertTo <tblRockSample>(myList);
DataTable cubTable = CollectionHelper.ConvertTo <tblCuboid>(cubs);
DataTable joinCub = CollectionHelper.JoinDataTables(rockSampleTable, cubTable, (row1, row2) => row1.Field <string>("sampLabel") == row2.Field <string>("cubLabel"));
//Exporting data to a csv
ExportHelper.ExportDataTableToCsv(joinCub, path, false);
}
catch
{
((ShellViewModel)IoC.Get <IShell>()).ShowError("An unexpected error occured.");
}
});
break;
case ("Handpiece"):
await Task.Run(() =>
{
try
{
tblHandpiece query;
List <tblHandpiece> hps = new List <tblHandpiece>();
using (var db = new ApirsDatabase())
{
//The rock sample name
string label = "";
//Iterating through all participating projects and select all related rock samples and facies types
foreach (var samp in List)
{
try
{
label = samp.GetType().GetProperty("sampLabel").GetValue(samp).ToString();
query = (from hp in db.tblHandpieces
where hp.hpLabelFk == label
select hp).First();
hps.Add(query);
}
catch
{
hps.Add(new tblHandpiece()
{
hpLabelFk = label
});
}
}
}
//Converting data to data tables
DataTable rockSampleTable = CollectionHelper.ConvertTo <tblRockSample>(myList);
DataTable cubTable = CollectionHelper.ConvertTo <tblHandpiece>(hps);
DataTable joinCub = CollectionHelper.JoinDataTables(rockSampleTable, cubTable, (row1, row2) => row1.Field <string>("sampLabel") == row2.Field <string>("hpLabelFk"));
//Exporting data to a csv
ExportHelper.ExportDataTableToCsv(joinCub, path, false);
}
catch
{
((ShellViewModel)IoC.Get <IShell>()).ShowError("An unexpected error occured.");
}
});
break;
case ("All"):
case ("Sediment/Soil"):
await Task.Run(() =>
{
try
{
//Converting data to data tables
DataTable rockSampleTable = CollectionHelper.ConvertTo <tblRockSample>(myList);
//Exporting data to a csv
ExportHelper.ExportDataTableToCsv(rockSampleTable, path, false);
}
catch
{
((ShellViewModel)IoC.Get <IShell>()).ShowError("An unexpected error occured.");
}
});
break;
case ("LabMeasurement"):
await Task.Run(() =>
{
try
{
v_PetrophysicsRockSamples query;
List <v_PetrophysicsRockSamples> petrophysics = new List <v_PetrophysicsRockSamples>();
using (var db = new ApirsDatabase())
{
//The rock sample name
string label = "";
//Iterating through all participating projects and select all related rock samples and facies types
foreach (var samp in List)
{
try
{
label = samp.GetType().GetProperty("sampLabel").GetValue(samp).ToString();
query = (from pet in db.v_PetrophysicsRockSamples
where pet.labmeSampleName == label
select pet).First();
petrophysics.Add(query);
}
catch
{
continue;
}
}
}
//Converting data to data tables
DataTable petrophysicsTable = CollectionHelper.ConvertTo <v_PetrophysicsRockSamples>(petrophysics);
//Exporting data to a csv
ExportHelper.ExportDataTableToCsv(petrophysicsTable, path, false);
}
catch
{
throw new Exception("An unexpected error occured.");
}
});
break;
}
break;
case "GeoReVi.tblObjectOfInvestigation":
//Casing the generic list to an object list
List <tblObjectOfInvestigation> myOoiList = List.Cast <tblObjectOfInvestigation>().ToList();
switch (AdditionalParameter)
{
case ("Outcrop"):
await Task.Run(() =>
{
try
{
tblOutcrop query;
List <tblOutcrop> outcrops = new List <tblOutcrop>();
using (var db = new ApirsDatabase())
{
//The rock sample name
string label = "";
//Iterating through all participating projects and select all related rock samples and facies types
foreach (var samp in List)
{
try
{
label = samp.GetType().GetProperty("ooiName").GetValue(samp).ToString();
query = (from outc in db.tblOutcrops
where outc.outLocalName == label
select outc).First();
outcrops.Add(query);
}
catch
{
outcrops.Add(new tblOutcrop()
{
outLocalName = label
});
}
}
}
//Converting data to data tables
DataTable ooiTable = CollectionHelper.ConvertTo <tblObjectOfInvestigation>(myOoiList);
DataTable outTable = CollectionHelper.ConvertTo <tblOutcrop>(outcrops);
DataTable joinOut = CollectionHelper.JoinDataTables(ooiTable, outTable, (row1, row2) => row1.Field <string>("ooiName") == row2.Field <string>("outLocalName"));
//Exporting data to a csv
ExportHelper.ExportDataTableToCsv(joinOut, path, false);
}
catch
{
((ShellViewModel)IoC.Get <IShell>()).ShowError("An unexpected error occured.");
}
});
break;
case ("Drilling"):
await Task.Run(() =>
{
try
{
tblDrilling query;
List <tblDrilling> drillings = new List <tblDrilling>();
using (var db = new ApirsDatabase())
{
//The rock sample name
string label = "";
//Iterating through all participating projects and select all related rock samples and facies types
foreach (var samp in List)
{
try
{
label = samp.GetType().GetProperty("ooiName").GetValue(samp).ToString();
query = (from drill in db.tblDrillings
where drill.drillName == label
select drill).First();
drillings.Add(query);
}
catch
{
drillings.Add(new tblDrilling()
{
drillName = label
});
}
}
}
//Converting data to data tables
DataTable ooiTable = CollectionHelper.ConvertTo <tblObjectOfInvestigation>(myOoiList);
DataTable drillTable = CollectionHelper.ConvertTo <tblDrilling>(drillings);
DataTable joindrill = CollectionHelper.JoinDataTables(ooiTable, drillTable, (row1, row2) => row1.Field <string>("ooiName") == row2.Field <string>("drillName"));
//Exporting data to a csv
ExportHelper.ExportDataTableToCsv(joindrill, path, false);
}
catch
{
((ShellViewModel)IoC.Get <IShell>()).ShowError("An unexpected error occured.");
}
});
break;
case ("Transect"):
await Task.Run(() =>
{
try
{
tblTransect query;
List <tblTransect> transects = new List <tblTransect>();
using (var db = new ApirsDatabase())
{
//The rock sample name
string label = "";
//Iterating through all participating projects and select all related rock samples and facies types
foreach (var samp in List)
{
try
{
label = samp.GetType().GetProperty("ooiName").GetValue(samp).ToString();
query = (from tra in db.tblTransects
where tra.traName == label
select tra).First();
transects.Add(query);
}
catch
{
transects.Add(new tblTransect()
{
traName = label
});
}
}
}
//Converting data to data tables
DataTable ooiTable = CollectionHelper.ConvertTo <tblObjectOfInvestigation>(myOoiList);
DataTable traTable = CollectionHelper.ConvertTo <tblTransect>(transects);
DataTable joinTra = CollectionHelper.JoinDataTables(ooiTable, traTable, (row1, row2) => row1.Field <string>("ooiName") == row2.Field <string>("traName"));
//Exporting data to a csv
ExportHelper.ExportDataTableToCsv(joinTra, path, false);
}
catch
{
((ShellViewModel)IoC.Get <IShell>()).ShowError("An unexpected error occured.");
}
});
break;
case ("All"):
await Task.Run(() =>
{
try
{
//Converting data to data tables
DataTable ooiTable = CollectionHelper.ConvertTo <tblObjectOfInvestigation>(myOoiList);
//Exporting data to a csv
ExportHelper.ExportDataTableToCsv(ooiTable, path, false);
}
catch
{
((ShellViewModel)IoC.Get <IShell>()).ShowError("An unexpected error occured.");
}
});
break;
}
break;
case ("GeoReVi.tblFieldMeasurement"):
await Task.Run(() =>
{
try
{
List <v_PetrophysicsFieldMeasurements> query;
List <v_PetrophysicsFieldMeasurements> petrophysics = new List <v_PetrophysicsFieldMeasurements>();
string label = "";
label = List.FirstOrDefault().GetType().GetProperty("fimeObjectOfInvestigation").GetValue(List.FirstOrDefault()).ToString();
List <int> b = ((ShellViewModel)IoC.Get <IShell>()).Projects.Select(x => x.prjIdPk).ToList();
using (var db = new ApirsRepository <v_PetrophysicsFieldMeasurements>())
{
//Iterating through all participating projects and select all related rock samples and facies types
foreach (var samp in List)
{
try
{
//Local coordinates and OOI
double Xcoordinate = 0;
double Ycoordinate = 0;
double Zcoordinate = 0;
int ProjectID = -1;
Xcoordinate = (double)samp.GetType().GetProperty("fimeLocalCoordinateX").GetValue(samp);
Ycoordinate = (double)samp.GetType().GetProperty("fimeLocalCoordinateY").GetValue(samp);
Zcoordinate = (double)samp.GetType().GetProperty("fimeLocalCoordinateZ").GetValue(samp);
ProjectID = (int)samp.GetType().GetProperty("fimeprjIdFk").GetValue(samp);
query = db.GetModelByExpression(x => x.Local_x == Xcoordinate &&
x.Local_y == Ycoordinate &&
x.Local_z == Zcoordinate &&
x.Object_of_investigation == label &&
x.Project_ID == ProjectID).ToList();
foreach (v_PetrophysicsFieldMeasurements pet in query)
{
petrophysics.Add(pet);
}
}
catch
{
continue;
}
}
}
//Converting data to data tables
DataTable petrophysicsTable = CollectionHelper.ConvertTo <v_PetrophysicsFieldMeasurements>(petrophysics);
//Exporting data to a csv
ExportHelper.ExportDataTableToCsv(petrophysicsTable, path, false);
}
catch
{
((ShellViewModel)IoC.Get <IShell>()).ShowError("An unexpected error occured.");
}
});
break;
default:
break;
}
}
/// <summary>
/// Attempts to register the user
/// </summary>
/// <param name="parameter">The SecureString passed in from the view for the users password</param>
/// <returns></returns>
public async void Register(PasswordBox parameter, PasswordBox repeat)
{
if (this.CanRegister != true || parameter.Password != repeat.Password || parameter.Password.Length <= 7 || !parameter.Password.Any(char.IsDigit))
{
((ShellViewModel)IoC.Get <IShell>()).ShowInformation("Please validate your password correctly.");
return;
}
CommandHelper ch = new CommandHelper();
await ch.RunCommand(() => IsRegisterRunning, async() =>
{
await Task.Delay(2000);
try
{
using (var db = new ApirsDatabase())
{
var paramLoginName = new SqlParameter
{
ParameterName = "pLogin",
Value = UserName,
Direction = ParameterDirection.Input
};
var paramPass = new SqlParameter
{
ParameterName = "pPassword",
Value = parameter.Password,
Direction = ParameterDirection.Input
};
var paramMail = new SqlParameter
{
ParameterName = "pMail",
Value = Email,
Direction = ParameterDirection.Input
};
var paramResponse = new SqlParameter
{
ParameterName = "responseMessage",
Size = 250,
SqlDbType = SqlDbType.NVarChar,
Direction = ParameterDirection.Output
};
string par = db.Database.SqlQuery <string>("exec dbo.spAddUser @pLogin, @pPassword, @pMail, @responseMessage", paramLoginName, paramPass, paramMail, paramResponse).First();
//Forward the user to the home view or denying the login based on the response of the server
switch (par)
{
case "Message":
((ShellViewModel)IoC.Get <IShell>()).ShowInformation(par + ". Please try it again.");
return;
case "Success":
var paramLoginName1 = new SqlParameter
{
ParameterName = "pLogin",
Value = Email,
Direction = ParameterDirection.Input
};
var paramFirstName = new SqlParameter
{
ParameterName = "pFirstName",
Value = FirstName,
Direction = ParameterDirection.Input
};
var paramLastName = new SqlParameter
{
ParameterName = "pLastName",
Value = LastName,
Direction = ParameterDirection.Input
};
var paramAffiliation = new SqlParameter
{
ParameterName = "pAffiliation",
Value = Affiliation,
Direction = ParameterDirection.Input
};
var paramStatus = new SqlParameter
{
ParameterName = "pStatus",
SqlDbType = SqlDbType.Int,
Value = 3,
Direction = ParameterDirection.Input
};
var paramResponse1 = new SqlParameter
{
ParameterName = "responseMessage",
Size = 250,
SqlDbType = SqlDbType.NVarChar,
Direction = ParameterDirection.Output
};
string par1 = db.Database.SqlQuery <string>("exec dbo.spAddPerson @pFirstName, @pLastName, @pAffiliation, @pStatus, @pLogin, @responseMessage", paramFirstName, paramLastName, paramAffiliation, paramStatus, paramLoginName1, paramResponse1).First();
string param = par1.ToString();
switch (par1)
{
case "Success":
((ShellViewModel)IoC.Get <IShell>()).ShowInformation("You successfully created a profile for GeoReVi. You can login now with your user name and password.");
UserName = "";
Affiliation = "";
LastName = "";
FirstName = "";
Email = "";
_events.PublishOnUIThreadAsync(new ChangeViewModelMessage("LoginView"));
break;
case "Message":
default:
var paramLoginName2 = new SqlParameter
{
ParameterName = "pLogin",
Value = Email,
Direction = ParameterDirection.Input
};
var paramResponse2 = new SqlParameter
{
ParameterName = "responseMessage",
Size = 250,
SqlDbType = SqlDbType.NVarChar,
Direction = ParameterDirection.Output
};
//Triggering the delete user sp
string par2 = db.Database.SqlQuery <string>("exec dbo.spDeleteUser @pLogin, @responseMessage", paramLoginName2, paramResponse2).First();
((ShellViewModel)IoC.Get <IShell>()).ShowInformation("Please try it again.");
break;
}
break;
default:
return;
}
////Stored procedures
//SqlCommand spAddUser = new SqlCommand("dbo.spAddUser", SqlConn);
//SqlCommand spAddPerson = new SqlCommand("dbo.spAddPerson", SqlConn);
////Testing if a connection is established
//if (ServerInteractionHelper.IsNetworkAvailable() && ServerInteractionHelper.TryAccessDatabase())
//{
// //Preparing the stored procedures
// spAddUser.CommandType = System.Data.CommandType.StoredProcedure;
// spAddPerson.CommandType = System.Data.CommandType.StoredProcedure;
// //Adding the parameters
// spAddUser.Parameters.Add("@pLogin", SqlDbType.NVarChar, 50);
// spAddUser.Parameters.Add("@pPassword", SqlDbType.NVarChar, 50);
// spAddUser.Parameters.Add("@pMail", SqlDbType.NVarChar, 255);
// spAddUser.Parameters.Add("@responseMessage", SqlDbType.NVarChar, 255).Direction = ParameterDirection.Output;
// spAddUser.Parameters["@pLogin"].Value = this.UserName;
// spAddUser.Parameters["@pMail"].Value = this.Email;
// spAddUser.Parameters["@pPassword"].Value = parameter.Password;
//}
//else
//{
// return;
//}
}
}
catch (NullReferenceException ne)
{
Console.WriteLine(ne.Message);
}
catch (Exception e)
{
((ShellViewModel)IoC.Get <IShell>()).ShowInformation(e.Message);
}
});
}
/// <summary>
/// Default constructor
/// </summary>
public ApirsRepository()
{
_apirsDatabase = new ApirsDatabase();
dbEntity = _apirsDatabase.Set <T>();
}
/// <summary>
/// Attempts to log the user in
/// </summary>
/// <param name="parameter">The SecureString passed in from the view for the users password</param>
/// <returns></returns>
public async void Login(PasswordBox parameter)
{
CommandHelper ch = new CommandHelper();
await ch.RunBackgroundWorkerWithFlagHelperAsync(() => IsLoginRunning, async() =>
{
try
{
//var pwBox = (PasswordBox)parameter;
string username = this.Email ?? "";
using (var db = new ApirsDatabase())
{
var paramLoginName = new SqlParameter
{
ParameterName = "pLoginName",
Value = username,
Direction = ParameterDirection.Input
};
var paramPass = new SqlParameter
{
ParameterName = "pPassword",
Value = parameter.Password,
Direction = ParameterDirection.Input
};
var paramResponse = new SqlParameter
{
ParameterName = "responseMessage",
Size = 250,
SqlDbType = SqlDbType.NVarChar,
Direction = ParameterDirection.Output
};
string par = db.Database.SqlQuery <string>("exec dbo.spUserLogin @pLoginName, @pPassword, @responseMessage", paramLoginName, paramPass, paramResponse).First();
//Forward the user to the home view or denying the login based on the response of the server
switch (par)
{
case "Invalid login":
case "Incorrect password":
_events.PublishOnUIThreadAsync(new MessageBoxMessage("Wrong password. Please try it again", "", MessageBoxViewType.Information, MessageBoxViewButton.Ok));
break;
case "User successfully logged in":
//Get the actual user id and set it as a property in the shellview
tblPerson result = (from p in db.tblPersons
where p.persUserName == username.ToString()
select p).First();
_events.PublishOnUIThreadAsync(new ChangeUserMessage(Convert.ToInt32(result.persIdPk), result.persFullName));
//Changing the viewmodel to the homeview
_events.PublishOnUIThreadAsync(new ChangeViewModelMessage("HomeView"));
break;
default:
break;
}
}
return;
}
catch (Exception e)
{
_events.PublishOnUIThreadAsync(new MessageBoxMessage(UserMessageValueConverter.ConvertBack(1), "", MessageBoxViewType.Error, MessageBoxViewButton.Ok));
}
});
}
