internal static List<CollarInfo> FindCollarsForProject(Guid currentSelectedProject)
{
List<CollarInfo> ss = new List<CollarInfo>();
using (var entityObj = new NKDC(BaseImportTools.XSTRING, null))
{
IQueryable<Header> res = entityObj.Headers.Where(c => c.ProjectID == currentSelectedProject);
foreach (Header xx in res)
{
CollarInfo ci = new CollarInfo();
ci.Name = xx.HoleName;
if (xx.EastingUtm != null)
{
ci.Easting = (double)xx.EastingUtm;
}
if (xx.NorthingUtm != null)
{
ci.Northing = (double)xx.NorthingUtm;
}
if (xx.Elevation != null)
{
ci.RL = (double)xx.Elevation;
}
ss.Add(ci);
}
return ss;
}
}
internal List<AssayGroupTestResult> GetDuplicateResult(Dictionary<Guid, AssayGroupTest> assayGroups, Guid sampleID, Guid assayGroupTestID)
{
using (var entityObj = new NKDC(BaseImportTools.XSTRING, null))
{
List<AssayGroupTestResult> resultData = new List<AssayGroupTestResult>();
AssayGroupTest xx = assayGroups[assayGroupTestID];
string testName = xx.AssayTestName;
Guid testParamID = (Guid)xx.ParameterID;
IQueryable<AssayGroupTestResult> res = entityObj.AssayGroupTestResults.Where(c => c.SampleID == sampleID);
bool foundDupe = false;
foreach (AssayGroupTestResult xx2 in res)
{
IQueryable<AssayGroupTest> res2 = entityObj.AssayGroupTests.Where(c => c.AssayGroupID == xx2.AssayGroupTestID);
foreach (AssayGroupTest agt in res2)
{
if (agt.ParameterID == testParamID)
{
foundDupe = true;
break;
}
}
if (foundDupe)
{
resultData.Add(xx2);
break;
}
}
return resultData;
}
}
internal DictionaryUnit FindUnits(string theUnit)
{
var entityObj = new NKDC(BaseImportTools.XSTRING, null);
DictionaryUnit xd = (from c in entityObj.DictionaryUnits where c.StandardUnitName.Trim().Equals(theUnit) select c).FirstOrDefault();
return xd;
}
internal List<AssayGroupTestResult> GetDuplicateResult(Guid sampleID, string columnName)
{
using (var entityObj = new NKDC(BaseImportTools.XSTRING, null))
{
List<AssayGroupTestResult> resultData = new List<AssayGroupTestResult>();
bool foundDupe = false;
IQueryable<AssayGroupTestResult> res = null;
if (resultsCache1.ContainsKey(sampleID))
{
res = resultsCache1[sampleID];
}
else
{
res = entityObj.AssayGroupTestResults.Where(c => c.SampleID == sampleID);
resultsCache1.Add(sampleID, res);
}
foreach (AssayGroupTestResult xx2 in res)
{
Guid assayGroupTestOfSample = xx2.AssayGroupTestID;
// now query the assay groups tests for this sample
IQueryable<AssayGroupTest> res2 = null;
if (resultsCache2.ContainsKey(assayGroupTestOfSample))
{
res2 = resultsCache2[assayGroupTestOfSample];
}
else
{
res2 = entityObj.AssayGroupTests.Where(c => c.AssayGroupTestID == assayGroupTestOfSample);
resultsCache2.Add(assayGroupTestOfSample, res2);
}
foreach (AssayGroupTest agt in res2)
{
// these are teh assay test groups
if (agt.AssayTestName.Trim().Equals(columnName))
{
foundDupe = true;
break;
}
}
if (foundDupe)
{
resultData.Add(xx2);
break;
}
}
return resultData;
}
}
internal List<Guid> CheckForDuplicate(Guid holeID, decimal depth, NKDC eo)
{
List<Guid> results = new List<Guid>();
IQueryable<Survey> res = eo.Surveys.Where(c => c.HeaderID == holeID && c.Depth == depth);
foreach (Survey xs in res) {
results.Add(xs.SurveyID);
}
return results;
}
internal static Guid FindHeaderGuid(string headerNameItem, Guid currentSelectedProject)
{
Guid resHole = new Guid();
using (var entityObj = new NKDC(BaseImportTools.XSTRING, null))
{
IQueryable<Header> res = entityObj.Headers.Where(c => (c.ProjectID == currentSelectedProject) && (c.HoleName.Equals(headerNameItem)));
foreach (Header xx in res)
{
resHole = xx.HeaderID;
}
return resHole;
}
}
public string TestConnection(string connString) {
using (var entityObj = new NKDC(connString, null))
{
// talk to the import lib to do the import
var query = from BlockModel in entityObj.BlockModels select new { BlockModel.BlockModelID, BlockModel.OriginX, BlockModel.OriginY, BlockModel.OriginZ, BlockModel.ProjectID };
foreach (BlockModel bm in entityObj.BlockModels)
{
Guid gu = bm.BlockModelID;
string alias = bm.Alias;
int proj = bm.Version;
}
return "In NKD.Import";
}
}
internal List<Sample> CheckForDuplicate(Guid holeID, decimal? fromDepth, decimal? toDepth)
{
using (var entityObj = new NKDC(BaseImportTools.XSTRING, null))
{
List<Sample> resultList = new List<Sample>();
bool found = false;
IQueryable<Sample> res = entityObj.Samples.Where(c => c.HeaderID == holeID && c.FromDepth == fromDepth && c.ToDepth == toDepth);
if (res != null && res.Count() > 0)
{
found = true;
}
foreach (Sample xx in res)
{
found = true;
resultList.Add(xx);
break;
}
return resultList;
}
}
internal static Dictionary<string, Guid> FindHeaderGuidsForProject(Guid NKDProjectID)
{
Dictionary<string, Guid> holeIDLookups = new Dictionary<string, Guid>();
using (var entityObj = new NKDC(BaseImportTools.XSTRING, null))
{
IQueryable<Header> res = entityObj.Headers.Where(c => (c.ProjectID == NKDProjectID));
foreach (Header xx in res)
{
Guid resHole = xx.HeaderID;
string ss = xx.HoleName;
// only add if it does not exist
bool exists = holeIDLookups.ContainsKey(ss);
if (!exists)
{
holeIDLookups.Add(ss, resHole);
}
}
return holeIDLookups;
}
}
private static int? getVersion()
{
try
{
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var x = new NKDC(XStringDefault, EF_METADATA, false);
var v = (from o in x.PrivateDatas where o.UniqueID=="NKDSchemaVersion" orderby o.VersionUpdated select o.Value).FirstOrDefault();
int i;
if (int.TryParse(v, out i))
return i;
else
return 0; //Invalid, Bad Result
}
}
catch { }
finally
{
//xStringDefault = null; //Reset this for incoming application //TODO Check
}
return null;
}
/// <summary>
/// Filters sender.
/// </summary>
/// <param name="from">Sender.</param>
/// <param name="api">Reference to server API.</param>
/// <param name="session">Reference to SMTP session.</param>
/// <param name="errorText">Filtering error text what is returned to client. ASCII text, 100 chars maximum.</param>
/// <returns>Returns true if sender is ok or false if rejected.</returns>
public bool Filter(string from, IMailServerApi api, SMTP_Session session, out string errorText)
{
errorText = null;
bool ok = false;
// Don't check authenticated users or LAN IP
if (session.IsAuthenticated || IsPrivateIP(session.RemoteEndPoint.Address))
{
return true;
}
try
{
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var d = new NKDC(ApplicationConnectionString, null);
if (!(from o in d.ContactEmailsViews where o.Email == @from select o).Any())
{
errorText = "You must be a registered user to use the email support service.";
WriteFilterLog("Sender:" + from + " IP:" + session.RemoteEndPoint.Address.ToString() + " unregistered.\r\n");
return false;
}
else
{
return true;
}
}
}
catch (Exception ex)
{
WriteFilterLog(string.Format("Sender:{0} IP:{1} caused exception.\r\nEX:{2}{3}\r\n__\r\n", from, session.RemoteEndPoint.Address, ex, ex.Message));
}
return ok;
}
public List<string> PerformBMImport(ModelImportStatus mos, Guid blockModelGUID, System.IO.Stream bmFileStream, System.IO.Stream ffFileStream, ImportDataMap importMap, double xOrigin, double yOrigin, double zOrigin, System.ComponentModel.BackgroundWorker worker, int approxNumLines, string NKDProjectID, string alias, Guid authorGuid, string connString)
{
this.currentWorker = worker;
using (var entityObj = new NKDC(connString, null))
{
// talk to the import lib to do the import
DateTime startTime = DateTime.Now;
int batchSize = 1000;
//UpdateStatus("Creating new NKD block model", 20.0);
ImportUtils.BlockImport dbIm = null;
try
{
dbIm = new ImportUtils.BlockImport();
//ImportDataMap importMapLoaded = FormatSpecificationIO.ImportMapIO.LoadImportMap(ffFileStream);
BlockModel xAdd = new BlockModel();
xAdd.OriginX = (Decimal)xOrigin; // TO-DO
xAdd.OriginY = (Decimal)yOrigin; // TO-DO
xAdd.OriginZ = (Decimal)zOrigin; // TO-DO
xAdd.Alias = alias;
// when on server, automatically pick up the author GUID and apply it to the model.
if (currentWorker == null)
{
xAdd.AuthorContactID = authorGuid;
xAdd.ResponsibleContactID = authorGuid;
}
xAdd.VersionUpdated = DateTime.UtcNow;
xAdd.BlockModelID = blockModelGUID;
xAdd.ProjectID = new Guid(NKDProjectID); // TODO - allow user to pick size
entityObj.BlockModels.AddObject(xAdd);
entityObj.SaveChanges();
UpdateStatus("Setting model meta data", 25.0);
// add the meta data to identify all of the oclumns etc.
}
catch (Exception ex)
{
mos.AddErrorMessage("Error setting block model defintion data. " + ex.ToString());
}
List<string> domains = new List<string>();
if (dbIm != null)
{
try
{
List<BlockModelMetadata> blockColumnMetaData = dbIm.SetBlockModelMetaData(blockModelGUID, importMap, connString);
}
catch (Exception ex)
{
mos.AddErrorMessage("Error setting block model meta data:\n" + ex.ToString());
}
try
{
// add the new BM guid to the column map as a default so that it is always entered
importMap.columnMap.Add(new ColumnMap("", -1, "BlockModelBlock", "BlockModelID", ImportDataMap.TEXTDATATYPE, blockModelGUID.ToString(), blockModelGUID.ToString(), ImportDataMap.UNIT_NONE));
// add the individual blocks
domains = dbIm.AddBlockData(mos, bmFileStream, importMap, blockModelGUID, batchSize, UpdateStatus, approxNumLines, connString);
// run this only if in wonows client (determined by the status of the worker thread at this stage)
if (currentWorker != null)
{
List<Tuple<string, string>> doms = new List<Tuple<string, string>>();
string domainColumnName = "Domain";
foreach (string ss in domains)
{
doms.Add(new Tuple<string, string>(domainColumnName, ss));
}
dbIm.UpdateDomains(doms, blockModelGUID);
}
}
catch (Exception ex)
{
mos.AddErrorMessage("Error adding block data:\n" + ex.ToString());
}
}
return domains;
}
}
//private Object syncUsersLock = new Object();
//private static Guid syncUsersFirstThread = Guid.Empty;
//private static Guid syncUsersLastThread = Guid.Empty;
//private static ManualResetEvent mre = new ManualResetEvent(false);
//public void SyncUsersManual()
//{
// //Thread t = new Thread(new ParameterizedThreadStart(this.SyncUsersManualThread));
// //t.Start(Guid.NewGuid());
// bool acquiredLock = false;
// Guid syncThread = Guid.NewGuid();
// Guid syncThreadGroup;
// try
// {
// lock (syncUsersLock)
// {
// syncUsersLastThread = syncThread;
// Monitor.TryEnter(syncUsersFirstThread, ref acquiredLock);
// }
// if (acquiredLock)
// {
// syncUsersFirstThread = syncThread;
// SyncUsers();
// lock (syncUsersLock)
// {
// Monitor.Exit(syncUsersFirstThread);
// mre.Set();
// mre.Reset();
// }
// }
// else
// {
// lock (syncUsersLock)
// {
// syncThreadGroup = syncUsersFirstThread;
// }
// mre.WaitOne();
// bool repeat = false;
// lock (syncUsersLock)
// {
// if (syncUsersLastThread == syncThread)
// Monitor.Enter(syncUsersFirstThread, ref acquiredLock);
// }
// if (repeat)
// SyncUsersManual();
// }
// }
// catch
// {
// if (acquiredLock)
// {
// Monitor.Exit(syncUsersFirstThread);
// }
// }
//}
public void SyncUsers()
{
//Get Orchard Users & Roles
var orchardUsers = _contentManager.Query<UserPart, UserPartRecord>().List();
var orchardRoles = _roleService.GetRoles().ToArray();
var orchardUserRoles = (from xur in _userRolesRepository.Table.ToArray()
join xu in orchardUsers on xur.UserId equals xu.Id
join xr in orchardRoles on xur.Role.Id equals xr.Id
select new {xu.UserName, RoleName=xr.Name}).ToArray();
//Get Authmode & Then Update
if (AuthenticationMode == System.Web.Configuration.AuthenticationMode.Forms)
{
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var c = new NKDC(ApplicationConnectionString,null);
c.Users.Where(f => !(from o in c.Contacts select o.AspNetUserID).Contains(f.UserId)).Delete();
var r = from o in c.Roles.Include("Users") where o.ApplicationId == ApplicationID select o;
var u = from o in c.Users.Include("Roles") where o.ApplicationId == ApplicationID select o;
var updated = DateTime.UtcNow;
//New User
var nu = (from o in orchardUsers where !(from ou in u select ou.UserName).Contains(o.UserName) select o);
foreach (var n in nu)
{
var user = new Users();
user.UserId = Guid.NewGuid();
user.UserName = n.UserName;
user.ApplicationId = ApplicationID;
user.LoweredUserName = n.UserName.ToLower();
user.LastActivityDate = updated;
c.Users.AddObject(user);
var contacts = (from o in c.Contacts where o.Username == user.UserName select o);
foreach (var nc in contacts)
{
nc.AspNetUserID = user.UserId;
}
if (!contacts.Any())
{
var contact = new Contact();
contact.ContactID = Guid.NewGuid();
contact.Username = user.UserName;
contact.AspNetUserID = user.UserId;
contact.DefaultEmail = n.Email;
contact.ContactName = string.Format("Site User: {0}", user.UserName);
contact.VersionUpdated = updated;
contact.Surname = "";
contact.Firstname = "";
c.Contacts.AddObject(contact);
}
}
//New Role
var nr = (from o in orchardRoles where !(from or in r select or.RoleName).Contains(o.Name) select o);
foreach (var n in nr)
{
var role = new Roles();
role.RoleName = n.Name;
role.ApplicationId = ApplicationID;
role.RoleId = Guid.NewGuid();
role.LoweredRoleName = n.Name.ToLower();
c.Roles.AddObject(role);
}
c.SaveChanges();
var users = c.Users.Include("Roles").Where(f => f.ApplicationId == ApplicationID).ToArray();
var roles = c.Roles.Where(f => f.ApplicationId == ApplicationID).ToArray();
foreach (var user in users)
{
foreach (var role in user.Roles.AsEnumerable())
{
//Remove
if (!orchardUserRoles.Any(f => f.RoleName == role.RoleName && f.UserName == user.UserName))
{
c.E_SP_DropUserRole(user.UserId, role.RoleId);
}
}
var newRoleIds = (from o in orchardUserRoles where !user.Roles.Select(f=>f.RoleName).Contains(o.RoleName) && o.UserName==user.UserName
join m in roles on o.RoleName equals m.RoleName select m);
foreach (var newRoleId in newRoleIds)
{
c.E_SP_AddUserRole(user.UserId, newRoleId.RoleId);
//user.Roles.Add(newRoleId);
}
}
c.SaveChanges();
//TODO Update per application
//var ru = (from o in u.ToArray() where !(from ou in orchardUsers select ou.UserName).Contains(o.UserName) select o); //can just delete from users table
//foreach (var rem in ru)
//{
// //c.Users.DeleteObject(rem); //Doesn't work for multitenancy
//}
//c.SaveChanges();
}
}
else if (AuthenticationMode == System.Web.Configuration.AuthenticationMode.Windows)
{
//Module syncs only users - only all admin for now
//Get AD Users
// throw new NotImplementedException();
// get a DirectorySearcher object
DirectorySearcher search = new DirectorySearcher();
// specify the search filter
search.Filter = "(&(objectCategory=person)(objectClass=user))";
//search.Filter = "(&(objectClass=user)(anr=agrosser))"; //TEST
//// specify which property values to return in the search
search.PropertiesToLoad.Add("name"); // first name
search.PropertiesToLoad.Add("givenName"); // first name
search.PropertiesToLoad.Add("sn"); // last name
search.PropertiesToLoad.Add("mail"); // smtp mail address
search.PropertiesToLoad.Add("samaccountname"); // account name
search.PropertiesToLoad.Add("memberof"); // groups
search.PropertiesToLoad.Add("objectsid");
search.PropertiesToLoad.Add("objectguid");
search.PropertiesToLoad.Add("title");
// perform the search
SearchResultCollection results = search.FindAll(); //.FindOne();
var sessionRoleCache = new Dictionary<string, string>();
var adusers = from SearchResult o in results
select new
{
name = o.Properties["name"] != null && o.Properties["name"].Count > 0 ? string.Format("{0}", o.Properties["name"][0]) : null,
givenName = o.Properties["givenName"] != null && o.Properties["givenName"].Count > 0 ? string.Format("{0}", o.Properties["givenName"][0]) : null,
sn = o.Properties["sn"] != null && o.Properties["sn"].Count > 0 ? string.Format("{0}", o.Properties["sn"][0]) : null,
email = o.Properties["mail"] != null && o.Properties["mail"].Count > 0 ? string.Format("{0}", o.Properties["mail"][0]) : null,
samaccountname = o.Properties["samaccountname"] != null && o.Properties["samaccountname"].Count > 0 ? string.Format("{0}", o.Properties["samaccountname"][0]) : null,
username = o.Properties["objectsid"] != null && o.Properties["objectsid"].Count > 0 ? ((NTAccount)(new SecurityIdentifier((byte[])o.Properties["objectsid"][0], 0)).Translate(typeof(NTAccount))).ToString() : null,
guid = o.Properties["objectguid"] != null && o.Properties["objectguid"].Count > 0 ? new Guid((byte[])o.Properties["objectguid"][0]) : (Guid?)null,
title = o.Properties["title"] != null && o.Properties["title"].Count > 0 ? string.Format("{0}", o.Properties["title"][0]) : null,
roles = o.Properties["memberof"] != null ? (from string m in o.Properties["memberof"] select getNameFromFQDN(m, sessionRoleCache)).ToArray() : new string[] { }
};
//Get NKD Users
Contact[] nkdusers;
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var d = new NKDC(ApplicationConnectionString,null);
nkdusers = (from o in d.Contacts select o).ToArray();
//Sync AD, Orchard, NKD
//New into NKD
//We need firstname, surname
var ad_new = (from o in adusers where o.givenName != null && o.sn != null && (o.guid.HasValue && !(from x in nkdusers select x.ContactID).Contains((Guid)o.guid)) || (!o.guid.HasValue && !(from x in nkdusers select x.Username.ToLowerInvariant()).Contains(o.username.ToLowerInvariant())) select o);
foreach (var o in ad_new)
{
Contact c = new Contact();
c.ContactID = o.guid.HasValue ? o.guid.Value : Guid.NewGuid();
c.Username = o.username;
c.Firstname = o.givenName;
c.ContactName = string.Join(string.Empty, string.Format("{0} [{1}]", o.name, o.username).Take(120));
c.Surname = o.sn;
c.DefaultEmail = o.email;
d.Contacts.AddObject(c);
}
//Updates into NKD
var ad_diff = from o in adusers
from x in nkdusers
where ((o.guid.HasValue && o.guid.Value == x.ContactID) || (o.username != null && x.Username != null && o.username.ToLowerInvariant() == x.Username.ToLowerInvariant()))
//Things to update
&& (
o.givenName != x.Firstname
|| o.sn != x.Surname
|| o.email != x.DefaultEmail
|| o.name != x.ContactName
)
select new { x.ContactID, o.givenName, o.sn, o.email, o.name, o.username };
foreach (var o in ad_diff)
{
var c = nkdusers.First(x => x.ContactID == o.ContactID);
c.Firstname = o.givenName;
c.ContactName = string.Join(string.Empty, string.Format("{0} [{1}]", o.name, o.username).Take(120));
c.Surname = o.sn;
c.DefaultEmail = o.email;
}
d.SaveChanges();
}
}
}
public IEnumerable<Contact> GetContacts()
{
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var dataContext = new NKDC(ApplicationConnectionString,null);
return dataContext.Contacts.OrderBy(x=>x.ContactName).ToArray();
}
}
internal void AddSurveyData(ModelImportStatus mos, Stream fileStream, FormatSpecification.ImportDataMap importMap, int batchSize, Action<string, double> UpdateStatus, int approxNumLines, string connectionString, Guid NKDProjectID, bool overwrite, bool checkForDuplicates)
{
bool duplicateFound = false;
// iterate through the data lines
int ct = 1;
int linesRead = 0;
SqlConnection connection = null;
SqlConnection secondaryConnection = null;
Dictionary<Guid, List<string>> rejectedLines = new Dictionary<Guid, List<string>>();
Dictionary<string, string> holeWarningMessages = new Dictionary<string, string>();
using (var entityObj = new NKDC(connectionString, null))
{
//entityObj.Configuration.AutoDetectChangesEnabled = false;
SurveyQueries sq = new SurveyQueries();
// get a connection to the database
try
{
connection = new SqlConnection(connectionString);
connection.Open();
secondaryConnection = new SqlConnection(connectionString);
secondaryConnection.Open();
int numCommits = 0;
SqlTransaction trans;
//trans = connection.BeginTransaction(System.Data.IsolationLevel.Snapshot);
List<SqlCommand> commands = new List<SqlCommand>();
int tb = 0;
int transactionBatchLimit = batchSize;
// open the filestream and read the first line
StreamReader sr = null;
try
{
sr = new StreamReader(fileStream);
}
catch (Exception ex)
{
mos.AddErrorMessage("Error getting data stream for input data:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_LOADING_FILE;
}
string line = null;
float bct = 1;
// report every X blocks
int repCount = 0;
//int reportOnBlock = 1000;
float fNumLines = (float)approxNumLines;
// get the column containing the hole name
Dictionary<string, Guid> holeIDLookups = new Dictionary<string, Guid>();
int numberOfHolesAdded = 0;
ColumnMap headerCmap = importMap.FindItemsByTargetName("HeaderID");
ColumnMap depthCmap = importMap.FindItemsByTargetName("Depth");
float percentComplete = 0;
int headerIDX = headerCmap.sourceColumnNumber;
if (sr != null)
{
while ((line = sr.ReadLine()) != null)
{
repCount++;
percentComplete = ((float)ct / approxNumLines) * 100.0f;
bct++;
linesRead++;
if (ct >= importMap.dataStartLine)
{
string statementPart1 = "INSERT INTO " + importMap.mapTargetPrimaryTable + " ";
string clauseValues = "";
string clauseParameters = "";
List<string> items = parseTestLine(line, importMap.inputDelimiter);
// using the column map, pick out the hole name field and see if it is in the database already
string headerNameItem = items[headerIDX];
// check if this holename is a duplicate in the file
bool foundHole = false;
Guid holeID = new Guid();
bool lv = holeIDLookups.ContainsKey(headerNameItem);
if (!lv)
{
string headerGUID = ForeignKeyUtils.FindFKValueInOther(headerNameItem, headerCmap, secondaryConnection, false, "HoleName", NKDProjectID);
if (headerGUID == null)
{
// this means we have not found the specified records in the header table
// Report on issue and skip line
}
else
{
foundHole = true;
holeID = new Guid(headerGUID);
holeIDLookups.Add(headerNameItem, holeID);
}
}
else
{
holeIDLookups.TryGetValue(headerNameItem, out holeID);
foundHole = true;
}
if (!foundHole)
{
mos.AddWarningMessage("Failed to find hole " + headerNameItem + ". Skipping record at line " + linesRead + ".");
mos.finalErrorCode = ModelImportStatus.DATA_CONSISTENCY_ERROR;
mos.recordsFailed++;
continue;
}
if (checkForDuplicates == true && depthCmap != null)
{
// check for duplicate depths
string d = items[depthCmap.sourceColumnNumber];
decimal dt = 0;
bool isParsed = decimal.TryParse(d, out dt);
if (isParsed)
{
List<Guid> rr = sq.CheckForDuplicate(holeID, dt, secondaryConnection);
//List<Guid> rr = sq.CheckForDuplicate(holeID, dt, entityObj);
if (rr.Count > 0)
{
duplicateFound = true;
if (!rejectedLines.ContainsKey(rr.First()))
{
rejectedLines.Add(rr.First(), items);
mos.AddWarningMessage("Duplicate depth found in survey data for hole " + headerNameItem + " at depth " + d + " on line " + linesRead);
UpdateStatus("Duplicate depth found in survey data for hole " + headerNameItem + " at depth " + d, percentComplete);
}
else
{
mos.AddWarningMessage("Duplicate depth found in survey data file for hole " + headerNameItem + " at depth " + d + " on line " + linesRead);
UpdateStatus("Duplicate depth found in survey data file for hole " + headerNameItem + " at depth " + d, percentComplete);
rejectedLines[rr.First()] = items;
}
if (!overwrite)
{
mos.recordsFailed++;
}
continue;
}
}
}
#region mappsearch
// now pick out all the mapped values
foreach (ColumnMap cmap in importMap.columnMap)
{
if (cmap.targetColumnName.Trim().Equals("HeaderID"))
{
string targetCol = cmap.targetColumnName;
string targetTable = cmap.targetColumnTable;
clauseValues += "" + targetTable + "." + targetCol + ",";
clauseParameters += "\'" + holeID.ToString() + "\',";
}
else
{
bool isFKColumn = cmap.hasFKRelation;
int colID = cmap.sourceColumnNumber;
string columnValue = cmap.defaultValue;
if (colID >= 0)
{
columnValue = items[colID];
}
string targetCol = cmap.targetColumnName;
string targetTable = cmap.targetColumnTable;
clauseValues += "" + targetTable + "." + targetCol + ",";
if (isFKColumn)
{
// go and search for the appropriate value from the foreign key table
string newValue = ForeignKeyUtils.FindFKValueInDictionary(columnValue, cmap, secondaryConnection, true);
columnValue = newValue;
if (newValue != null && newValue.Trim().Length > 0)
{
clauseParameters += "\'" + columnValue + "\',";
}
else
{
clauseParameters += "NULL,";
}
}
else
{
if (cmap.importDataType.Equals(ImportDataMap.NUMERICDATATYPE))
{
if (columnValue.Equals("-") || columnValue.Equals(""))
{
if (cmap.defaultValue != null && cmap.defaultValue.Length > 0)
{
columnValue = cmap.defaultValue;
}
else
{
columnValue = "NULL";
}
}
clauseParameters += columnValue + ",";
}
else
{
//if (columnValue.Equals("-"))
//{
// if (cmap.defaultValue != null && cmap.defaultValue.Length > 0)
// {
// columnValue = cmap.defaultValue;
// }
//}
clauseParameters += "\'" + columnValue + "\',";
}
}
}
}
#endregion
// now just a hack to remove the final coma from the query
clauseParameters = clauseParameters.Substring(0, clauseParameters.Length - 1);
clauseValues = clauseValues.Substring(0, clauseValues.Length - 1);
string commandText = statementPart1 + "(" + clauseValues + ") VALUES (" + clauseParameters + ")";
//SqlCommand sqc = new SqlCommand(commandText, connection, trans);
SqlCommand sqc = new SqlCommand(commandText, connection);
numberOfHolesAdded++;
if (commitToDB)
{
try
{
sqc.ExecuteNonQuery();
}
catch (Exception ex)
{
mos.AddErrorMessage("Failed to insert items on line " + linesRead + ".");
UpdateStatus("Failed to insert items on line " + linesRead + ".", percentComplete);
mos.recordsFailed++;
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
UpdateStatus("Updating from line " + linesRead, percentComplete);
tb++;
//if (tb == transactionBatchLimit)
//{
// // commit batch, then renew the transaction
// if (commitToDB)
// {
// trans.Commit();
// numCommits++;
// // trans = null;
// trans = connection.BeginTransaction(System.Data.IsolationLevel.Snapshot);
// }
// // reset counter
// tb = 0;
//}
}
ct++;
}
}
if (tb > 0)
{
//if (commitToDB)
//{
// trans.Commit();
//}
numCommits++;
}
mos.recordsAdded = numberOfHolesAdded;
UpdateStatus("Finished writing records to database ", 100.0);
}
catch (Exception ex)
{
UpdateStatus("Error writing records to database ", 0);
mos.AddErrorMessage("Error writing records data at line " + linesRead + ":\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
finally
{
try
{
connection.Close();
secondaryConnection.Close();
fileStream.Close();
}
catch (Exception ex)
{
mos.AddErrorMessage("Error closing conenction to database:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
if (duplicateFound == true && overwrite == true)
{
OverwriteSurveyRecord(mos, rejectedLines, importMap, connectionString, NKDProjectID, UpdateStatus, holeWarningMessages);
}
foreach (KeyValuePair<string, string> kvp in holeWarningMessages)
{
string v = kvp.Value;
mos.AddWarningMessage(v);
}
mos.linesReadFromSource = linesRead;
}
}
public Guid? GetContactID(string username)
{
if (string.IsNullOrWhiteSpace(username))
return null;
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var d = new NKDC(ApplicationConnectionString,null);
return (from c in d.Contacts join u in d.Users on c.AspNetUserID equals u.UserId where u.ApplicationId == ApplicationID where username==c.Username && c.Version == 0 && c.VersionDeletedBy == null select c.ContactID).SingleOrDefault();
//return d.Contacts.Where(x=>x.Username == username).Select(x=>x.ContactID).FirstOrDefault();
}
}
public Dictionary<Guid, string> GetRoles()
{
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var c = new NKDC(ApplicationConnectionString,null);
var r = (from o in c.Applications
join a in c.Roles on o.ApplicationId equals a.ApplicationId
select new { RoleName = a.RoleName + " (" + o.ApplicationName + ")", a.RoleId });
return r.ToDictionary(f=>f.RoleId, f=>f.RoleName);
}
}
private AssayGroupTest FindExistingAssayGroupTest(string p)
{
using (var entityObj = new NKDC(BaseImportTools.XSTRING, null))
{
AssayGroupTest resAssGroup = null;
IQueryable<AssayGroupTest> res = entityObj.AssayGroupTests.Where(c => c.AssayTestName.Trim().Equals(p.Trim()));
foreach (AssayGroupTest xx in res)
{
resAssGroup = xx;
}
return resAssGroup;
}
}
public void WarnAdmins(IEnumerable<string> warnings)
{
try
{
Logger.Warning(string.Join("\r\n\r\n", warnings));
}
catch { }
var adminRoles = new string[] {"Administrator"};
var m = _contentManager.Query<UserPart, UserPartRecord>().Where(f => f.UserName == "admin").List();
var application = ApplicationID;
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var c = new NKDC(ApplicationConnectionString, null);
var e = (from o in c.ApplicationUserRoles where o.ApplicationId==application && o.RoleName == "Administrator" select o.DefaultEmail).ToArray()
.Union(m.Select(f=>f.Email))
.Where(f=>!string.IsNullOrWhiteSpace(f)).ToArray();
EmailUsers(e,
string.Format("WARNING! SITE: {0} USER: {1} EMAIL: {2}"
, _orchardServices.WorkContext.CurrentSite.SiteName
, _orchardServices.WorkContext.CurrentUser.UserName
, _orchardServices.WorkContext.CurrentUser.Email)
, string.Format("Warnings [UTC:{0}]: \r\n\r\n {1}", DateTime.UtcNow, string.Join("\r\n\r\n", warnings))
);
}
}
public void Created(UserContext context) {
_contentManagerSession.Store(context.User.ContentItem);
SyncUsers();
var contact = GetContactID(context.User.UserName);
if (contact == null)
return;
//Add user to default company
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var d = new NKDC(ApplicationConnectionString, null, false);
if (!(from o in d.Experiences where o.ContactID == contact && o.CompanyID != null select o).Any())
{
var e = new Experience
{
ExperienceID = Guid.NewGuid(),
ContactID = contact,
CompanyID = COMPANY_DEFAULT,
ExperienceName = string.Format("User - {0}", context.User.UserName),
VersionUpdated = DateTime.UtcNow
};
d.Experiences.AddObject(e);
d.SaveChanges();
}
}
}
internal void AddAssayData(ModelImportStatus mos, Stream fileStream, FormatSpecification.ImportDataMap importMap,
int batchSize, Action<string, double> UpdateStatus, int approxNumLines,
string connectionString, Guid NKDProjectID, bool checkForDuplicates, bool doImportOverwrite)
{
bool commitToDB = true;
DateTime currentUpdateTimestamp = DateTime.UtcNow;
// first set up an assay group object - we can do this through the edm
using (var entityObj = new NKDC(connectionString, null))
{
//entityObj.Configuration.AutoDetectChangesEnabled = false;
Guid agGuid = Guid.NewGuid();
AssayGroup ag = new AssayGroup();
ag.AssayGroupID = agGuid;
ag.ProjectID = NKDProjectID;
ag.AssayGroupName = "Manual import";
ag.Comment = "From file " + importMap.mapOriginalDataFile;
ag.Entered = currentUpdateTimestamp;
ag.VersionUpdated = currentUpdateTimestamp;
entityObj.AssayGroups.AddObject(ag);
if (commitToDB)
{
entityObj.SaveChanges();
}
// set up the assay test columns - one of these for each test type
Dictionary<ColumnMap, Guid> resultsColumns = new Dictionary<ColumnMap, Guid>();
Dictionary<Guid, AssayGroupTest> assayGroups = new Dictionary<Guid, AssayGroupTest>();
foreach (ColumnMap cim in importMap.columnMap)
{
if (cim.targetColumnName.Trim().StartsWith("[ASSAY"))
{
// this is a test category
resultsColumns.Add(cim, Guid.NewGuid());
}
}
UpdateStatus("Setting up assay tests ", 2);
foreach (KeyValuePair<ColumnMap, Guid> kvp in resultsColumns)
{
ColumnMap cm = kvp.Key;
Guid g = kvp.Value;
AssayGroupTest xt = new AssayGroupTest();
string ss1 = "";
if (cm.sourceColumnName != null && cm.sourceColumnName.Length > 15)
{
ss1 = cm.sourceColumnName.Substring(0, 16);
}
else
{
ss1 = cm.sourceColumnName;
}
Guid pid = FindParameterForAssayTypeName(cm.sourceColumnName);
xt.ParameterID = pid;
xt.AssayTestName = ss1;
xt.AssayGroupID = agGuid;
xt.AssayGroupTestID = g;
xt.VersionUpdated = currentUpdateTimestamp;
entityObj.AssayGroupTests.AddObject(xt);
assayGroups.Add(g, xt);
if (commitToDB)
{
entityObj.SaveChanges();
}
}
// iterate through the data lines
int ct = 1;
int linesRead = 0;
SqlConnection connection = null;
SqlConnection secondaryConnection = null;
//List<string> uniqueDomains = new List<string>();
// get a connection to the database
string line = null;
try
{
int domainColIDX = -1;
connection = new SqlConnection(connectionString);
connection.Open();
secondaryConnection = new SqlConnection(connectionString);
secondaryConnection.Open();
bool hasDuplicateIntervals = false;
int numCommits = 0;
SqlTransaction trans;
trans = connection.BeginTransaction();
List<SqlCommand> commands = new List<SqlCommand>();
int tb = 0;
int transactionBatchLimit = batchSize;
// open the filestream and read the first line
StreamReader sr = null;
FileStream fs = null;
try
{
//fs = new FileStream(textInputDataFile, FileMode.Open, FileAccess.Read, FileShare.Read);
sr = new StreamReader(fileStream);
}
catch (Exception ex)
{
mos.AddErrorMessage("Error getting data stream for input data:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_LOADING_FILE;
}
line = null;
float pct = 0;
float bct = 1;
// report every X blocks
int repCount = 0;
//int reportOnBlock = 1000;
float fNumLines = (float)approxNumLines;
Dictionary<string, Guid> holeIDLookups = new Dictionary<string, Guid>();
Dictionary<string, int> columnIDX = new Dictionary<string, int>();
int fkLookupCount = 0;
BaseImportTools.PopulateCMapShortcut(importMap, columnIDX);
ColumnMap headerCmap = importMap.FindItemsByTargetName("HeaderID");
AssayQueries assayQueries = new AssayQueries();
List<string> items = new List<string>();
if (sr != null)
{
while ((line = sr.ReadLine()) != null)
{
repCount++;
pct = ((float)linesRead / (float)approxNumLines) * 100.0f;
bct++;
linesRead++;
if (ct >= importMap.dataStartLine)
{
var append = BaseImportTools.ParseTestLine(line, importMap.inputDelimiter);
if (items.Count == 0 || append.Count == importMap.MaxColumns)
items = append;
else if (items.Count < importMap.MaxColumns)
{
items[items.Count - 1] = items[items.Count - 1] + append[0];
items.AddRange(append.Skip(1));
}
if (items.Count < importMap.MaxColumns)
{
mos.AddWarningMessage(string.Format("Bad CSV file, attempted to join....{0}", linesRead));
continue;
}
else if (items.Count > importMap.MaxColumns)
{
mos.AddWarningMessage(string.Format("FAILED! Line {0}. Bad CSV file, attempted to join.", linesRead));
items.Clear();
continue;
}
// digest a row of input data
Guid holeID = new Guid();
Decimal? fromDepth = null;
Decimal? toDepth = null;
string sampleNumber = null;
string labBatchNumber = null;
string labsampleNumber = null;
Decimal? sampleMassKg = null;
Decimal? dryMassKg = null;
string standardSampleTypeName = null;
// find mapped values by name
//ColumnMap cmap = importMap.FindItemsByTargetName("HeaderID");
int idxVal = 0;
bool foundEntry = columnIDX.TryGetValue("HeaderID", out idxVal);
bool foundHole = false;
string holeName = "";
if (foundEntry)
{
string lookupByName = "HoleName";
string lookupValue = items[idxVal];
holeName = lookupValue;
bool lv = holeIDLookups.ContainsKey(lookupValue);
if (!lv)
{
string headerGUID = ForeignKeyUtils.FindFKValueInOther(lookupValue, headerCmap, secondaryConnection, false, lookupByName, NKDProjectID);
if (headerGUID == null)
{
// this means we have not found the specified records in the header table
// Report on issue and skip line
}
else
{
foundHole = true;
holeID = new Guid(headerGUID);
holeIDLookups.Add(lookupValue, holeID);
fkLookupCount++;
}
}
else
{
holeIDLookups.TryGetValue(lookupValue, out holeID);
foundHole = true;
}
}
if (!foundHole)
{
mos.AddErrorMessage("Failed to find hole " + holeName + ". Skipping record at line " + linesRead + ".");
mos.finalErrorCode = ModelImportStatus.DATA_CONSISTENCY_ERROR;
mos.recordsFailed++;
continue;
}
else
{
bool hasFrom = false;
idxVal = 0;
foundEntry = columnIDX.TryGetValue("FromDepth", out idxVal);
if (foundEntry)
//cmap = importMap.FindItemsByTargetName();
//if (cmap != null)
{
string ii = items[idxVal];
Decimal val = 0;
bool isOk = Decimal.TryParse(ii, out val);
if (isOk)
{
fromDepth = val;
hasFrom = true;
}
}
bool hasTo = false;
idxVal = 0;
foundEntry = columnIDX.TryGetValue("ToDepth", out idxVal);
if (foundEntry)
//cmap = importMap.FindItemsByTargetName("ToDepth");
//if (cmap != null)
{
string ii = items[idxVal];
Decimal val = 0;
bool isOk = Decimal.TryParse(ii, out val);
if (isOk)
{
toDepth = val;
hasTo = true;
}
}
List<Sample> duplicateList = null;
bool isDuplicateInterval = false;
if (checkForDuplicates)
{
if (hasFrom && hasTo)
{
// here we need to check that the hole is not duplicated
duplicateList = assayQueries.CheckForDuplicate(holeID, fromDepth, toDepth);
if (duplicateList.Count > 0)
{
isDuplicateInterval = true;
}
}
if (isDuplicateInterval)
{
hasDuplicateIntervals = true;
mos.AddWarningMessage("Duplicate interval for hole " + holeName + " at depth " + fromDepth + " to " + toDepth);
UpdateStatus("Duplicate interval at " + holeName + " " + fromDepth + ", " + toDepth, pct);
if (!doImportOverwrite)
{
mos.recordsFailed++;
continue;
}
}
}
//cmap = importMap.FindItemsByTargetName("SampleNumber");
idxVal = 0;
foundEntry = columnIDX.TryGetValue("SampleNumber", out idxVal);
if (foundEntry)
// if (cmap != null)
{
string ii = items[idxVal];
sampleNumber = ii;
}
//cmap = importMap.FindItemsByTargetName("LabSampleNumber");
//if (cmap != null)
idxVal = 0;
foundEntry = columnIDX.TryGetValue("LabSampleName", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
labsampleNumber = ii;
}
//cmap = importMap.FindItemsByTargetName("LabBatchNumber");
idxVal = 0;
foundEntry = columnIDX.TryGetValue("LabBatchNumber", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
labBatchNumber = ii;
}
idxVal = 0;
foundEntry = columnIDX.TryGetValue("SampleMassKg", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
Decimal val = 0;
bool isOk = Decimal.TryParse(ii, out val);
if (isOk)
{
sampleMassKg = val;
}
}
idxVal = 0;
foundEntry = columnIDX.TryGetValue("DryMassKg", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
Decimal val = 0;
bool isOk = Decimal.TryParse(ii, out val);
if (isOk)
{
dryMassKg = val;
}
}
idxVal = 0;
foundEntry = columnIDX.TryGetValue("StandardSampleTypeName", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
standardSampleTypeName = ii;
}
Sample xs = new Sample();
if (isDuplicateInterval == true)
{
xs = duplicateList.First();
}
else
{
xs.SampleID = Guid.NewGuid();
xs.FromDepth = fromDepth;
xs.ToDepth = toDepth;
xs.HeaderID = holeID;
xs.VersionUpdated = currentUpdateTimestamp;
xs.SampleNumber = sampleNumber;
xs.SampleMassKg = sampleMassKg;
if (!string.IsNullOrWhiteSpace(standardSampleTypeName))
{
var t = entityObj.DictionarySampleTypes.Select(f=>new {f.SampleTypeID, f.StandardSampleTypeName}).FirstOrDefault(f => f.StandardSampleTypeName == standardSampleTypeName);
if (t != null)
xs.SampleTypeID = t.SampleTypeID;
}
xs.DryMassKg = dryMassKg;
}
// now pick out all the mapped values
// iterate over all [ASSAY RESULT] columns
bool assayUpdated = false;
bool assayAdded = false;
var results = new List<AssayGroupTestResult>();
foreach (KeyValuePair<ColumnMap, Guid> kvp in resultsColumns)
{
ColumnMap cm = kvp.Key;
Guid g = kvp.Value; // this is the AssayGroupTestID
AssayGroupTestResult testResult = new AssayGroupTestResult();
/*bool assayResFound = false;
if (isDuplicateInterval)
{
List<AssayGroupTestResult> testResults = assayQueries.GetDuplicateResult(xs.SampleID, cm.sourceColumnName);
if (testResults.Count > 0)
{
testResult = testResults.First();
assayResFound = true;
}
}*/
//if(!assayResFound)
// {
testResult.AssayGroupTestResultID = Guid.NewGuid();
testResult.AssayGroupTestID = g;
testResult.SampleID = xs.SampleID;
testResult.VersionUpdated = currentUpdateTimestamp;
//}
testResult.LabBatchNumber = labBatchNumber;
testResult.LabSampleName = labsampleNumber;
Decimal result = new Decimal();
if (items.Count >= cm.sourceColumnNumber)
{
bool parsedOK = Decimal.TryParse(items[cm.sourceColumnNumber], out result);
if (parsedOK)
{
testResult.LabResult = result;
}
testResult.LabResultText = items[cm.sourceColumnNumber];
}
else
{
mos.AddWarningMessage("Line " + linesRead + " contains too few columns to read " + cm.sourceColumnName);
}
results.Add(testResult);
//if (isDuplicateInterval == false)
//{
assayAdded = true;
//}else{
// if (!assayResFound)
// {
// entityObj.AssayGroupTestResult.Add(testResult);
// assayAdded = true;
// }
// else {
// assayUpdated = true;
// }
//}
}
var resultsToSave = (from o in results where !string.IsNullOrWhiteSpace(o.LabResultText) select o);
if (!resultsToSave.Any())
continue;
if (!isDuplicateInterval)
entityObj.Samples.AddObject(xs);
foreach (var save in resultsToSave)
entityObj.AssayGroupTestResults.AddObject(save);
if (assayAdded == true)
{
mos.recordsAdded++;
}
if (assayUpdated)
{
mos.recordsUpdated++;
}
tb++;
}
}
if (commitToDB)
{
if (tb == transactionBatchLimit)
{
entityObj.SaveChanges();
UpdateStatus("Writing assays to DB (" + ct + " entries)", pct);
tb = 0;
}
}
ct++;
//Console.WriteLine("Processing line "+ct);
items.Clear();
}
entityObj.SaveChanges();
}
if (hasDuplicateIntervals)
{
mos.finalErrorCode = ModelImportStatus.DATA_CONSISTENCY_ERROR;
}
string numFKLookups = "FK lookups " + fkLookupCount;
mos.linesReadFromSource = ct - 1;
UpdateStatus("Finished writing assays to database.", 0);
}
catch (Exception ex)
{
UpdateStatus("Error writing assays to database ", 0);
mos.AddErrorMessage("Error writing assay data at line " + linesRead + ":\n" + line + "\n\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
finally
{
try
{
connection.Close();
secondaryConnection.Close();
fileStream.Close();
}
catch (Exception ex)
{
mos.AddErrorMessage("Error closing connection to database:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
mos.linesReadFromSource = linesRead;
}
}
public Dictionary<Guid,string> GetCompanies()
{
var allCompanies = new Dictionary<Guid,string>();
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var c = new NKDC(ApplicationConnectionString,null);
using (DataTable table = new DataTable())
{
using (var con = new SqlConnection(ApplicationConnectionString))
using (var cmd = new SqlCommand("X_SP_GetCompanies", con))
using (var da = new SqlDataAdapter(cmd))
{
cmd.CommandType = CommandType.StoredProcedure;
da.Fill(table);
}
foreach (DataRow row in table.Rows)
{
var rowRoot = (Guid)row[1];
var companyName = "";
Guid? lastKey = null;
for (int i = 2; i < table.Columns.Count; i += 2)
{
companyName += (string)row[i];
var checking = (Guid)row[i+1];
if (lastKey.HasValue && lastKey.Value == checking)
break;
lastKey = checking;
if (!allCompanies.ContainsKey(checking))
allCompanies.Add(checking, companyName);
companyName += " - ";
}
}
}
}
return (from o in allCompanies orderby o.Value select o).ToDictionary(f=>f.Key, f=>f.Value);
}
public string[] GetUserEmails(Guid[] users)
{
if (users == null || users.Length == 0)
return new string[] { };
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var d = new NKDC(ApplicationConnectionString,null);
var o = from c in d.Contacts where users.Contains(c.ContactID) && c.DefaultEmail != null
select c.DefaultEmail;
return o.ToArray();
}
}
public ContactViewModel GetMyInfo()
{
string [] roles = new string[] {};
if (_orchardServices.WorkContext.CurrentUser != null)
{
roles = ((ContentItem)_orchardServices.WorkContext.CurrentUser.ContentItem).As<IUserRoles>().Roles.ToArray();
}
var application = ApplicationID;
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var d = new NKDC(ApplicationConnectionString, null);
var m = (from o in d.E_SP_GetUserInfo(application, Username, null) select o).FirstOrDefault();
if (m == null)
return null;
else
{
var emptyco = new[] { new { id = "", name = "" } };
var companies = emptyco.Where(f => 1 == 0);
if (m.companies != null)
companies = JsonConvert.DeserializeAnonymousType(m.companies,emptyco);
var emptyli = new[] { new { id = default(Guid?), expiry = default(DateTime?), modelid = default(Guid?), modelname = "", modelrestrictions = "", partid = default(Guid?), partname = "", partrestrictions = "" } };
var licenses = emptyli.Where(f => 1 == 0);
if (m.Licenses != null)
licenses = JsonConvert.DeserializeAnonymousType(m.Licenses, emptyli);
return new ContactViewModel
{
UserName = m.Username,
UserID = m.AspNetUserID,
CurrentCompanyID = m.CompanyID,
CurrentCompany = m.CompanyName,
ContactID = m.ContactID,
ContactName = m.ContactName,
IsPartner = (m.IsPartner > 0) ? true : false,
IsSubscriber = (m.IsSubscriber > 0) ? true : false,
Companies = (from o in companies select new SelectListItem { Text = o.name, Value = o.id, Selected = (string.Format("{0}", m.CompanyID).ToLower() == string.Format("{0}", o.id).ToLower()) }).ToArray(),
Licenses = (from o in licenses select new LicenseViewModel {
LicenseID = o.id,
Expiry = o.expiry,
ModelID = o.modelid,
ModelName = o.modelname,
ModelRestrictions = o.modelrestrictions,
PartID = o.partid,
PartName = o.partname,
PartRestrictions = o.partrestrictions
}).AsEnumerable(),
Roles = roles
};
}
}
}
public ModelImportStatus PerformBMAppend(System.IO.Stream bmStream, Guid bmGuid, string alias, string columnNameToImport, int columnIndexToImport, string connString, char delimiter)
{
// TODO: read stream and write updates to database
// get the next column to write to - search meta data to get the list of occupied columns
using (var entityObj = new NKDC(connString, null))
{
List<BlockModelMetadata> d = new List<BlockModelMetadata>();
var o = entityObj.BlockModelMetadatas.Where(f => f.BlockModelID == bmGuid && f.IsColumnData == true).Select(f => (string)f.BlockModelMetadataText).ToArray();
// yuk, ugly hack to get the next column to update into. In the long run, use normalised data as it will be much easier
int lastIndex = 0;
foreach (string s in o)
{
if (s.StartsWith("Numeric"))
{
string endBit = s.Substring(7);
int ival = -1;
bool parsed = int.TryParse(endBit, out ival);
if (parsed)
{
lastIndex = Math.Max(ival, lastIndex);
}
}
}
string colToInsertTo = "Numeric" + (lastIndex + 1);
//TODO: add this new meta data item into the database
//TODO: update the data within the database itself
ImportUtils.BlockImport dbIm = new ImportUtils.BlockImport();
ImportDataMap idm = new ImportDataMap();
idm.columnMap = new List<ColumnMap>();
idm.inputDelimiter = delimiter;
idm.columnMap.Add(new ColumnMap(columnNameToImport, columnIndexToImport, "BlockModelBlock", colToInsertTo, ImportDataMap.NUMERICDATATYPE, null, null, null));
dbIm.SetBlockModelMetaData(bmGuid, idm, connString);
return dbIm.UpdateBlockData(bmStream, bmGuid, colToInsertTo, connString, delimiter);
}
}
private Guid FindParameterForAssayTypeName(string pName)
{
Guid pid = new Guid();
Parameter xp = new Parameter();
using (var entityObj = new NKDC(BaseImportTools.XSTRING, null))
{
bool found = false;
IQueryable<Parameter> res = entityObj.Parameters.Where(c => c.ParameterType.Equals("AssayTypeName") && c.ParameterName.Equals(pName));
foreach (Parameter xx in res)
{
found = true;
pid = xx.ParameterID;
break;
}
if (!found)
{
Parameter pp = new Parameter();
pid = Guid.NewGuid();
pp.ParameterID = pid;
pp.ParameterType = "AssayTypeName";
pp.ParameterName = pName;
pp.Description = pName;
pp.VersionUpdated = DateTime.UtcNow;
entityObj.Parameters.AddObject(pp);
entityObj.SaveChanges();
}
return pid;
}
}
/// <summary>
///
/// </summary>
/// <param name="bmDataFile"></param>
/// <param name="selectedFormatBMFile"></param>
/// <param name="importMap"></param>
/// <param name="xOrigin"></param>
/// <param name="yOrigin"></param>
/// <param name="zOrigin"></param>
/// <param name="worker"></param>
/// <param name="approxNumLines"></param>
/// <param name="NKDProjectID"></param>
/// <param name="units"></param>
/// <param name="connString"></param>
/// <returns></returns>
public string PerformBMImport(string bmDataFile, string selectedFormatBMFile, ImportDataMap importMap, double xOrigin, double yOrigin, double zOrigin, System.ComponentModel.BackgroundWorker worker, int approxNumLines, string NKDProjectID, string units, string connString)
{
this.currentWorker = worker;
UpdateStatus("Connecting to NKD", 10.0);
using (var entityObj = new NKDC(connString, null))
{
// talk to the import lib to do the import
var query = from BlockModel in entityObj.BlockModels select new { BlockModel.BlockModelID, BlockModel.OriginX, BlockModel.OriginY, BlockModel.OriginZ, BlockModel.ProjectID };
List<string> cn = new List<string>();
//For each field in the database (or property in Linq object)
BlockModel ob = new BlockModel();
foreach (PropertyInfo pi in ob.GetType().GetProperties())
{
Type ty = pi.GetType();
String name = pi.Name;
cn.Add(name);
}
DateTime startTime = DateTime.Now;
int batchSize = 100;
UpdateStatus("Creating new NKD block model", 20.0);
ImportUtils.BlockImport dbIm = new ImportUtils.BlockImport();
Guid blockModelGUID = Guid.NewGuid();
BlockModel xAdd = new BlockModel();
xAdd.OriginX = (Decimal)xOrigin; // TO-DO
xAdd.OriginY = (Decimal)yOrigin; // TO-DO
xAdd.OriginZ = (Decimal)zOrigin; // TO-DO
xAdd.BlockModelID = blockModelGUID;
xAdd.ProjectID = new Guid(NKDProjectID); // TODO - allow user to pick size
entityObj.BlockModels.AddObject(xAdd);
entityObj.SaveChanges();
UpdateStatus("Setting model meta data", 25.0);
// add the meta data to identify all of the oclumns etc.
List<BlockModelMetadata> blockColumnMetaData = dbIm.SetBlockModelMetaData(blockModelGUID, importMap, connString);
// add the new BM guid to the column map as a default so that it is always entered
importMap.columnMap.Add(new ColumnMap("", -1, "BlockModelBlock", "BlockModelID", ImportDataMap.TEXTDATATYPE, blockModelGUID.ToString(), null, units));
// add the individual blocks
dbIm.AddBlockData(bmDataFile, importMap, blockModelGUID, batchSize, UpdateStatus, approxNumLines, connString);
//dbIm.AddBlockDataNorm(bmDataFile, importMap, blockModelGUID, batchSize, UpdateStatus, approxNumLines, blockColumnMetaData);
DateTime endTime = DateTime.Now;
long compVal = (endTime.Ticks - startTime.Ticks) / 1000;
string message = "" + startTime.ToShortTimeString() + " Ended: " + endTime.ToShortTimeString();
long xval = compVal;
return "";
}
}
public void DeleteSecurity(ISecured secured)
{
//TODO!!!: When writing security check for antecedentid = referenceid &/or version=0
if (secured.SecurityID.HasValue)
{
if (CheckOwnership(secured, ActionPermission.Read | ActionPermission.Delete))
{
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var c = new NKDC(ApplicationConnectionString,null);
if (secured.IsBlack)
{
var s = (from o in c.SecurityBlacklists where o.SecurityBlacklistID == secured.SecurityID && o.Version == 0 && o.VersionDeletedBy == null select o).Single();
c.SecurityBlacklists.DeleteObject(s);
}
else
{
var s = (from o in c.SecurityWhitelists where o.SecurityWhitelistID == secured.SecurityID && o.Version == 0 && o.VersionDeletedBy == null select o).Single();
c.SecurityWhitelists.DeleteObject(s);
}
c.SaveChanges();
}
}
else
throw new AuthorityException(string.Format("Incorrect permission for action: \"Delete\" Contact: {0} Record: {1}", secured.AccessorContactID, secured.OwnerReferenceID));
}
else
throw new NotSupportedException("Can not delete a security record without an ID.");
}
//Events
public void Creating(UserContext context) {
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var d = new NKDC(ApplicationConnectionString, null, false);
var exists = (from o in d.Contacts where (o.Username==context.UserParameters.Username && o.DefaultEmail != context.UserParameters.Email) select o).Any();
if (exists)
{
context.Cancel = true;
}
}
}
public Guid? GetEmailContactID(string email, bool validated = true)
{
if (email == null)
return null;
using (new TransactionScope(TransactionScopeOption.Suppress))
{
var d = new NKDC(ApplicationConnectionString, null);
if (!validated)
{
var id = (from c in d.Contacts join u in d.Users on c.AspNetUserID equals u.UserId
where u.ApplicationId == ApplicationID && email == c.DefaultEmail && c.Version == 0 && c.VersionDeletedBy == null
select c.ContactID).FirstOrDefault();
if (id != default(Guid))
return id;
id = (from c in d.Contacts
join u in d.Users on c.AspNetUserID equals u.UserId
join e in d.ContactEmailsViews on c.ContactID equals e.ContactID
where u.ApplicationId == ApplicationID && email == e.Email && c.Version == 0 && c.VersionDeletedBy == null
select c.ContactID).FirstOrDefault();
if (id != default(Guid))
return id;
else
return null;
}
else
return (from c in d.Contacts join u in d.Users on c.AspNetUserID equals u.UserId
where u.ApplicationId == ApplicationID && email == c.DefaultEmail && c.DefaultEmailValidated != null && c.Version == 0 && c.VersionDeletedBy == null select c.ContactID).Single();
}
}
