public void ApproveModel(Guid modelID, Guid approverID, string note)
{
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var d = new ModelsDataContext();
var o = d.BlockModels.Where(f => f.BlockModelID == modelID).Single();
o.ApproverContactID = approverID;
if (o.AuthorContactID.HasValue)
_userService.EmailUsers(_userService.GetUserEmails(new Guid[] { o.AuthorContactID.Value }), "Model approved", note);
var n = new BlockModelMetadata();
n.BlockModelMetadataID = Guid.NewGuid();
n.BlockModelID = modelID;
n.ParameterID = _privateServices.XODB_GUID_LOG;
var oc = new Occurrence();
oc.ID = Guid.NewGuid();
oc.ContactID = o.ApproverContactID.Value;
oc.Occurred = DateTime.UtcNow;
oc.Status = (uint)Occurrence.StatusCode.OK;
n.BlockModelMetadataText = oc.ToJson();
d.BlockModelMetadatas.InsertOnSubmit(n);
d.SubmitChanges();
}
}
public void DenyModel(Guid modelID, Guid denierID, string error)
{
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var d = new ModelsDataContext();
var o = d.BlockModels.Where(f => f.BlockModelID == modelID && f.AuthorContactID != null).Select(f => (Guid)f.AuthorContactID).ToArray();
_userService.EmailUsers(_userService.GetUserEmails(o), "Model not accepted", error);
var n = new BlockModelMetadata();
n.BlockModelMetadataID = Guid.NewGuid();
n.BlockModelID = modelID;
n.ParameterID = _privateServices.XODB_GUID_LOG;
var oc = new Occurrence();
oc.ID = Guid.NewGuid();
oc.ContactID = denierID;
oc.Occurred = DateTime.UtcNow;
oc.Status = (uint)Occurrence.StatusCode.Notified;
n.BlockModelMetadataText = oc.ToJson();
d.BlockModelMetadatas.InsertOnSubmit(n);
d.SubmitChanges();
}
}
public static decimal GetMultiplierForField(Guid convertFromFieldGuid, string targetStandardUnitName)
{
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var d = new ModelsDataContext();
//////Get Curves
var cmd = d.Connection.CreateCommand();
cmd.CommandText = "[dbo].[X_SP_GetConversionFactorForField]";
cmd.CommandType = CommandType.StoredProcedure;
var parm1 = cmd.CreateParameter();
parm1.ParameterName = "@field_guid";
parm1.DbType = DbType.Guid;
parm1.Value = convertFromFieldGuid;
cmd.Parameters.Add(parm1);
var parm2 = cmd.CreateParameter();
parm2.ParameterName = "@target";
parm2.DbType = DbType.String;
parm2.Value = targetStandardUnitName;
cmd.Parameters.Add(parm2);
var output = new System.Data.SqlClient.SqlParameter();
output.ParameterName = "@multiplier";
output.DbType = DbType.Decimal;
output.Direction = ParameterDirection.Output;
output.Precision = 38;
output.Scale = 20;
cmd.Parameters.Add(output);
try
{
//Let's actually run the queries
d.Connection.Open();
var reader = cmd.ExecuteReader();
return (decimal)output.Value;
}
catch
{
return 0;
}
finally
{
d.Connection.Close();
}
}
}
//Static Methods
public static DataSet CompareModelsResult(BlockModelCompareViewModel m)
{
DataSet ds = new DataSet("CompareModelsResult");
var y = m.FilterString.Deserialize<dynamic>();
var filter = new List<Tuple<string, string, string, string, string>>();
foreach (object[] k in y)
{
filter.Add(new Tuple<string, string, string, string, string>(
k[0] as string, //1 Model# (1 or 2)
k[1] as string, //2 Boolean Comparator (AND, OR)
k[2] as string, //3 Guid (FieldID)
k[3] as string, //4 Math Comparator (>=,<=,=)
k[4] as string //5 Value (0.05)
));
}
string filter1 = string.Join(";", (from o in filter
where o.Item1 == "1"
select
o.Item2.CleanTokenForSQL() + "," +
o.Item3.CleanTokenForSQL() + "," +
o.Item4.CleanTokenForSQL() + "," +
o.Item5.CleanTokenForSQL()).ToArray());
filter1 = string.IsNullOrEmpty(filter1) ? filter1 : filter1 + ";";
string filter2 = string.Join(";", (from o in filter
where o.Item1 == "2"
select
o.Item2.CleanTokenForSQL() + "," +
o.Item3.CleanTokenForSQL() + "," +
o.Item4.CleanTokenForSQL() + "," +
o.Item5.CleanTokenForSQL()).ToArray());
filter2 = string.IsNullOrEmpty(filter2) ? filter2 : filter2 + ";";
string domains1 = m.SelectedDomainsModel1Compact != null ? m.SelectedDomainsModel1Compact.CleanTokenForSQL() + ";" : "";
string domains2 = m.SelectedDomainsModel2Compact !=null ? m.SelectedDomainsModel2Compact.CleanTokenForSQL() + ";": "";
//Test DataSet
var p = new DataTable("Test1Text");
var x = new string[] { "asdasd", "asdasd", "asss", "asddasd", "reerrr" };
var s = from i in x select new { Test = i };
p.Columns.Add("Test");
s.CopyToDataTable(p);
ds.Tables.Add(p);
//Test Dataset
double[][] z = new double[][] { new[] { 0.5, 0.7 }, new[] { 40.5, 60.7 } };
var t = from i in z select new { Argument = i[0], Value = i[1] };
var g = new DataTable("Test2Tuple");
g.Columns.Add("Argument", typeof(double));
g.Columns.Add("Value", typeof(double));
t.CopyToDataTable(g);
ds.Tables.Add(g);
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var d = new ModelsDataContext();
//////Get Curves
var cmd = d.Connection.CreateCommand();
cmd.CommandText = "[dbo].[X_SP_GradeTonnage]";
cmd.CommandType = CommandType.StoredProcedure;
var parm1 = cmd.CreateParameter();
parm1.ParameterName = "@gt_guid";
parm1.DbType = DbType.Guid;
parm1.Value = m.GradeTonnageFieldID;
cmd.Parameters.Add(parm1);
var parm2 = cmd.CreateParameter();
parm2.ParameterName = "@bm1_guid";
parm2.DbType = DbType.Guid;
parm2.Value = m.Model1;
cmd.Parameters.Add(parm2);
var parm3 = cmd.CreateParameter();
parm3.ParameterName = "@bm2_guid";
parm3.DbType = DbType.Guid;
parm3.Value = m.Model2;
cmd.Parameters.Add(parm3);
var parm4 = cmd.CreateParameter();
parm4.ParameterName = "@gt_increment";
parm4.DbType = DbType.Decimal;
parm4.Value = m.GradeTonnageIncrement;
cmd.Parameters.Add(parm4);
var parm5 = cmd.CreateParameter();
parm5.ParameterName = "@filter1";
parm5.DbType = DbType.String;
parm5.Value = filter1;
cmd.Parameters.Add(parm5);
var parm6 = cmd.CreateParameter();
parm6.ParameterName = "@filter2";
parm6.DbType = DbType.String;
parm6.Value = filter2;
cmd.Parameters.Add(parm6);
var parm7 = cmd.CreateParameter();
parm7.ParameterName = "@domains1";
parm7.DbType = DbType.String;
parm7.Value = domains1;
cmd.Parameters.Add(parm7);
var parm8 = cmd.CreateParameter();
parm8.ParameterName = "@domains2";
parm8.DbType = DbType.String;
parm8.Value = domains2;
cmd.Parameters.Add(parm8);
var output = cmd.CreateParameter();
output.ParameterName = "@filterString";
output.DbType = DbType.String;
output.Size = 4000;
output.Direction = ParameterDirection.Output;
cmd.Parameters.Add(output);
/////Get Table
var cmdc = d.Connection.CreateCommand();
cmdc.CommandText = "[dbo].[X_SP_GradeTonnageComparison]";
cmdc.CommandType = CommandType.StoredProcedure;
var parmc1 = cmdc.CreateParameter();
parmc1.ParameterName = "@gt_guid";
parmc1.DbType = DbType.Guid;
parmc1.Value = m.GradeTonnageFieldID;
cmdc.Parameters.Add(parmc1);
var parmc2 = cmdc.CreateParameter();
parmc2.ParameterName = "@bm1_guid";
parmc2.DbType = DbType.Guid;
parmc2.Value = m.Model1;
cmdc.Parameters.Add(parmc2);
var parmc3 = cmdc.CreateParameter();
parmc3.ParameterName = "@bm2_guid";
parmc3.DbType = DbType.Guid;
parmc3.Value = m.Model2;
cmdc.Parameters.Add(parmc3);
var parmc5 = cmdc.CreateParameter();
parmc5.ParameterName = "@filter1";
parmc5.DbType = DbType.String;
parmc5.Value = filter1;
cmdc.Parameters.Add(parmc5);
var parmc6 = cmdc.CreateParameter();
parmc6.ParameterName = "@filter2";
parmc6.DbType = DbType.String;
parmc6.Value = filter2;
cmdc.Parameters.Add(parmc6);
var parmc7 = cmdc.CreateParameter();
parmc7.ParameterName = "@domains1";
parmc7.DbType = DbType.String;
parmc7.Value = domains1;
cmdc.Parameters.Add(parmc7);
var parmc8 = cmdc.CreateParameter();
parmc8.ParameterName = "@domains2";
parmc8.DbType = DbType.String;
parmc8.Value = domains2;
cmdc.Parameters.Add(parmc8);
var outputc = cmdc.CreateParameter();
outputc.ParameterName = "@filterString";
outputc.DbType = DbType.String;
outputc.Size = 4000;
outputc.Direction = ParameterDirection.Output;
cmdc.Parameters.Add(outputc);
//Slicer
var cmds = d.Connection.CreateCommand();
cmds.CommandText = "[dbo].[X_SP_SlicingTool]";
cmds.CommandType = CommandType.StoredProcedure;
var parms1 = cmds.CreateParameter();
parms1.ParameterName = "@st_guid";
parms1.DbType = DbType.Guid;
parms1.Value = m.GradeTonnageFieldID; //TODO: might need to separate this out in the UI one day (we are assuming XYZ values are xmax, ymax, zmax respectively now)
cmds.Parameters.Add(parms1);
var parms2 = cmds.CreateParameter();
parms2.ParameterName = "@bm1_guid";
parms2.DbType = DbType.Guid;
parms2.Value = m.Model1;
cmds.Parameters.Add(parms2);
var parms3 = cmds.CreateParameter();
parms3.ParameterName = "@bm2_guid";
parms3.DbType = DbType.Guid;
parms3.Value = m.Model2;
cmds.Parameters.Add(parms3);
var parms5 = cmds.CreateParameter();
parms5.ParameterName = "@filter1";
parms5.DbType = DbType.String;
parms5.Value = filter1;
cmds.Parameters.Add(parms5);
var parms6 = cmds.CreateParameter();
parms6.ParameterName = "@filter2";
parms6.DbType = DbType.String;
parms6.Value = filter2;
cmds.Parameters.Add(parms6);
var parms7 = cmds.CreateParameter();
parms7.ParameterName = "@domains1";
parms7.DbType = DbType.String;
parms7.Value = domains1;
cmds.Parameters.Add(parms7);
var parms8 = cmds.CreateParameter();
parms8.ParameterName = "@domains2";
parms8.DbType = DbType.String;
parms8.Value = domains2;
cmds.Parameters.Add(parms8);
var outputs = cmds.CreateParameter();
outputs.ParameterName = "@filterString";
outputs.DbType = DbType.String;
outputs.Size = 4000;
outputs.Direction = ParameterDirection.Output;
cmds.Parameters.Add(outputs);
try
{
var gt = new DataTable("gt");
var gtm = gt.Columns.Add("m", typeof(string));
var gtg = gt.Columns.Add("grade", typeof(decimal));
var gtt = gt.Columns.Add("tonnes", typeof(decimal));
var gttg = gt.Columns.Add("tonnage", typeof(decimal));
var gfs = new DataTable("gfs");
gfs.Columns.Add("FilterString", typeof(string));
var gfc = new DataTable("gfc");
gfc.Columns.Add("Resource Category", typeof(string));
gfc.Columns.Add("Model 1 Tonnes", typeof(decimal));
gfc.Columns.Add("Model 1 Grade", typeof(decimal));
gfc.Columns.Add("Model 2 Tonnes", typeof(decimal));
gfc.Columns.Add("Model 2 Grade", typeof(decimal));
gfc.Columns.Add("Absolute Difference", typeof(decimal));
var st = new DataTable("st");
st.Columns.Add("xyz", typeof(decimal));
st.Columns.Add("m", typeof(decimal));
st.Columns.Add("slice", typeof(decimal));
st.Columns.Add("samples", typeof(decimal));
st.Columns.Add("grade", typeof(decimal));
//Let's actually run the queries
d.Connection.Open();
if (m.GradeTonnageIncrement != 0)
{
cmd.CommandTimeout = DBHelper.DefaultTimeout;
var reader = cmd.ExecuteReader();
gt.Load(reader, LoadOption.OverwriteChanges);
decimal cumulative1 = 0, cumulative2 = 0;
decimal gtFieldMultiplier = GetMultiplierForField(m.GradeTonnageFieldID, "%");
foreach (DataRow r in gt.Rows)
{
decimal tonnes;
decimal grade;
if (!decimal.TryParse(string.Format("{0}", r[gtg]), out grade) && grade >= 0)
continue;
if (!decimal.TryParse(string.Format("{0}", r[gtt]), out tonnes) && tonnes >= 0)
continue;
//Cumulative needs to be in grade ascending order
if ((string)r[gtm] == "1")
{
//cumulative1 += ((decimal)r[gtg] * (decimal)r[gtt] * gtFieldMultiplier);
cumulative1 += (tonnes);
r[gttg] = cumulative1;
}
else
{
cumulative2 += (tonnes);
r[gttg] = cumulative2;
}
r[gtg] = grade * gtFieldMultiplier;
}
//reader.NextResult(); // Only 1 Resultset
//var gt2 = new DataTable("gt2");
//gt2.Load(reader, LoadOption.OverwriteChanges);
//ds.Tables.Add(gt2);
}
{
cmds.CommandTimeout = DBHelper.DefaultTimeout;
var reader = cmds.ExecuteReader();
st.Load(reader, LoadOption.OverwriteChanges);
}
{
cmdc.CommandTimeout = DBHelper.DefaultTimeout;
var reader = cmdc.ExecuteReader();
gfc.Load(reader, LoadOption.OverwriteChanges);
}
gfs.Rows.Add(outputc.Value as string); //filterString
ds.Tables.Add(st);
ds.Tables.Add(gt);
ds.Tables.Add(gfs);
ds.Tables.Add(gfc);
}
finally
{
d.Connection.Close();
}
return ds;
}
}
public IEnumerable<Tuple<Parameter, BlockModelMetadata>> GetModelParameters(Guid modelID)
{
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var d = new ModelsDataContext();
var o = from m in d.BlockModelMetadatas where m.BlockModelID == modelID
join p in d.Parameters on m.ParameterID equals p.ParameterID
select new { m, p }
;
return o.OrderBy(f=>f.p.Description).ToArray().Select(f=>new Tuple<Parameter,BlockModelMetadata>(f.p, f.m));
}
}
public void CheckModels()
{
//TODO:
// Removes old files, removes old models, checks for new files (future - automatically add model?)
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var d = new ModelsDataContext();
d.DropOldModels();
}
}
public string GetModelAlias(Guid modelID)
{
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var d = new ModelsDataContext();
return (from o in d.BlockModels where o.BlockModelID == modelID select o.Alias).FirstOrDefault();
}
}
public void UpdateModelParameter(BlockModelParameterViewModel m)
{
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var d = new ModelsDataContext();
var x = from p in d.Parameters where p.ParameterID == m.ParameterID select p;
var o = x.First();
o.UnitID = m.UnitID;
d.SubmitChanges();
}
}
public string GetBlockModelInfo(Guid bm) {
string res = "";
using (new TransactionScope(TransactionScopeOption.Suppress))
{
try
{
BaseImportTools bit = new BaseImportTools();
string ss1 = bit.TestConnection(global::System.Configuration.ConfigurationManager.ConnectionStrings["XODBConnectionString"].ConnectionString);
var d = new ModelsDataContext();
d.Connection.ConnectionString = global::System.Configuration.ConfigurationManager.ConnectionStrings["XODBConnectionString"].ConnectionString;
List<BlockModel> bl = d.BlockModels.ToList();
foreach (BlockModel aModel in bl)
{
res += aModel.Alias;
}
}
catch (Exception ex) {
res += "Error " + ex.ToString();
}
}
return res;
}
public IEnumerable<BlockModel> GetModelsCurrent()
{
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var d = new ModelsDataContext();
var b = from m in d.BlockModels join p in d.ModelStatus on m.BlockModelID equals p.BlockModelID select m;
return b.ToArray();
}
}
public IEnumerable<BlockModel> GetModels()
{
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var d = new ModelsDataContext();
return d.BlockModels.ToArray();
}
}