예제 #1
0
        internal List<AssayGroupTestResult> GetDuplicateResult(Dictionary<Guid, AssayGroupTest> assayGroups, Guid sampleID, Guid assayGroupTestID)
        {
            using (var entityObj = new XODBC(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;
            }
        }
예제 #2
0
        public void TestSpatial()
        {
           
            using (new TransactionScope(TransactionScopeOption.Suppress))
            {
                using (var context = new XODBC(_users.ApplicationConnectionString,null))
                {
                    var s = (from o in context.Locations select o).FirstOrDefault();
                    
                    //context.Universities.Add(new University()
                    //{
                    //    Name = "Graphic Design Institute",
                    //    Location = DbGeography.FromText("POINT(-122.336106 47.605049)"),
                    //});
                    //context.SaveChanges();

                    //var myLocation = DbGeography.FromText("POINT(-122.296623 47.640405)");

                    //var university = (from u in context.Universities
                    //                  orderby u.Location.Distance(myLocation)
                    //                  select u).FirstOrDefault();

                }                
            }
        }      
예제 #3
0
        internal static List<CollarInfo> FindCollarsForProject(Guid currentSelectedProject)
        {
            List<CollarInfo> ss = new List<CollarInfo>();
            using (var entityObj = new XODBC(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;
            }
        }
예제 #4
0
        internal DictionaryUnit FindUnits(string theUnit)
        {
            var entityObj = new XODBC(BaseImportTools.XSTRING, null);
            
            DictionaryUnit xd = (from c in entityObj.DictionaryUnits where c.StandardUnitName.Trim().Equals(theUnit) select c).FirstOrDefault();
            return xd;

        }
예제 #5
0
        internal List<AssayGroupTestResult> GetDuplicateResult(Guid sampleID, string columnName)
        {
            using (var entityObj = new XODBC(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;
            }
        }
예제 #6
0
        internal static Guid FindHeaderGuid(string headerNameItem, Guid currentSelectedProject)
        {
            Guid resHole = new Guid();
            using (var entityObj = new XODBC(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;
            }
        }
예제 #7
0
        internal List<Guid> CheckForDuplicate(Guid holeID, decimal depth, XODBC 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;
        }
예제 #8
0
 internal List<Sample> CheckForDuplicate(Guid holeID, decimal fromDepth, decimal toDepth)
 {
     using (var entityObj = new XODBC(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;
     }
 }
예제 #9
0
        internal static Dictionary<string, Guid> FindHeaderGuidsForProject(Guid XODBProjectID)
        {
            Dictionary<string, Guid> holeIDLookups = new Dictionary<string, Guid>();

            using (var entityObj = new XODBC(BaseImportTools.XSTRING, null))
            {

                IQueryable<Header> res = entityObj.Headers.Where(c => (c.ProjectID == XODBProjectID));
                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;
            }
        }
예제 #10
0
        private AssayGroupTest FindExistingAssayGroupTest(string p)
        {
            using (var entityObj = new XODBC(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;
            }

        }
예제 #11
0
 private Parameter GetParameterIDFor(XODBC entityObj, string paramType, string paramName)
 {
     Parameter res = null;
     IQueryable<Parameter> resGP = entityObj.Parameters.Where(c => c.ParameterType.Trim().Equals(paramType) && c.ParameterName.Trim().Equals(paramName));
     foreach (Parameter xx in resGP)
     {
         res = xx;
         break;
     }
     return res;
 }
예제 #12
0
        internal void AddSurveyData(ModelImportStatus mos, Stream fileStream, FormatSpecification.ImportDataMap importMap, int batchSize, Action<string, double> UpdateStatus, int approxNumLines, string connectionString, Guid XODBProjectID, 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 XODBC(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", XODBProjectID);
                                    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, XODBProjectID, UpdateStatus, holeWarningMessages);
                }
                foreach (KeyValuePair<string, string> kvp in holeWarningMessages)
                {
                    string v = kvp.Value;
                    mos.AddWarningMessage(v);
                }

                mos.linesReadFromSource = linesRead;
            }
         
        }
예제 #13
0
        private AssayGroupWorkflow GetAssayGroupWorkflow(XODBC entityObj, string programType, Guid? assayGroupID)
        {
            AssayGroupWorkflow agw = null;
            IQueryable<AssayGroupWorkflow> res = entityObj.AssayGroupWorkflows.Where(c => c.WorkflowName.Trim().Equals(programType.Trim()) && c.AssayGroupID == assayGroupID);
            foreach (AssayGroupWorkflow xx in res)
            {
                agw = xx;
            }
            if (agw == null) {
                agw = new AssayGroupWorkflow();
                agw.AssayGroupID = assayGroupID;
                agw.AssayGroupWorkflowID = Guid.NewGuid();
                agw.WorkflowName = programType;                
                entityObj.AssayGroupWorkflows.AddObject(agw);
                entityObj.SaveChanges();

            }
            return agw;
        }
예제 #14
0
        internal void AddCoalQualityData(ModelImportStatus mos, Stream fileStream, FormatSpecification.ImportDataMap importMap,
                                    int batchSize, Action<string, double> UpdateStatus, int approxNumLines, 
                                    string connectionString, Guid XODBProjectID, bool checkForDuplicates, bool doImportOverwrite)
        {
            WorkflowProcedureSequenceNumber = 1;
            Guid? lastHoleID = new Guid();
            string lastStage = "";
            decimal lastFromDepth = -999999;
            decimal lastToDepth = -999999;
            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 XODBC(connectionString, null))
            {
                Guid agGuid = Guid.NewGuid();
                AssayGroup ag = new AssayGroup();
                ag.AssayGroupID = agGuid;
                ag.ProjectID = XODBProjectID;
                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>();
                ColumnMap cmProgram = null;
                ColumnMap cmStage = null;
                ColumnMap cmSizeFraction = null;
                ColumnMap cmWashFraction = null;
                foreach (ColumnMap cim in importMap.columnMap)
                {
                    if (cim.targetColumnName.Trim().StartsWith("[RESULT"))
                    {
                        // this is a test category
                        resultsColumns.Add(cim, Guid.NewGuid());
                    }
                    else if (cim.targetColumnName.Trim().StartsWith("[PROGRAM"))
                    {
                        cmProgram = cim;
                    }
                    else if (cim.targetColumnName.Trim().StartsWith("[STAGE"))
                    {
                        cmStage = cim;
                    }
                    else if (cim.targetColumnName.Trim().StartsWith("[SIZE FRACTION"))
                    {
                        cmSizeFraction = cim;
                    }
                    else if (cim.targetColumnName.Trim().StartsWith("[WASH FRACTION"))
                    {
                        cmWashFraction = cim;
                    }
                    
                }
                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 = FindParameter("AssayTypeName", 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
                try
                {
                    connection = new SqlConnection(connectionString);
                    connection.Open();

                    secondaryConnection = new SqlConnection(connectionString);
                    secondaryConnection.Open();
                    bool hasDuplicateIntervals = false;

                    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;
                    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 pct = 0;
                    float bct = 1;

                    // report every X records
                    int repCount = 0;
                    float fNumLines = (float)approxNumLines;


                    Dictionary<string, Guid> holeIDLookups = new Dictionary<string, Guid>();
                    Dictionary<string, int> columnIDX = new Dictionary<string, int>();
                    int fkLookupCount = 0;

                    PopulateCMapShortcut("HeaderID", importMap, columnIDX);
                    PopulateCMapShortcut("FromDepth", importMap, columnIDX);
                    PopulateCMapShortcut("ToDepth", importMap, columnIDX);
                    PopulateCMapShortcut("SampleNumber", importMap, columnIDX);
                    PopulateCMapShortcut("SampleName", importMap, columnIDX);
                    PopulateCMapShortcut("LabSampleNumber", importMap, columnIDX);
                    PopulateCMapShortcut("LabBatchNumber", importMap, columnIDX);
                    ColumnMap headerCmap = importMap.FindItemsByTargetName("HeaderID");
                    AssayQueries assayQueries = new AssayQueries();
                    int seqNum = 1;
                    if (sr != null)
                    {
                        while ((line = sr.ReadLine()) != null)
                        {

                            repCount++;

                            pct = ((float)linesRead / (float)approxNumLines) * 100.0f;
                            bct++;
                            linesRead++;
                            if (ct >= importMap.dataStartLine)
                            {

                                // digest a row of input data 
                                List<string> items = parseTestLine(line, importMap.inputDelimiter);


                                Guid holeID = new Guid();
                                Decimal fromDepth = new Decimal(-9999999999);
                                Decimal toDepth = new Decimal(-9999999999);
                                string sampleNumber = null;
                                string sampleName = null;
                                string labBatchNumber = null;
                                string labsampleNumber = null;

                                // find mapped values by name
                                int idxVal = 0;
                                // -- Get the hole ID foreign key relation
                                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, XODBProjectID);
                                        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)
                                    {
                                        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)
                                    {
                                        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;
                                    //        }
                                    //    }
                                    //}

                                    idxVal = 0;
                                    foundEntry = columnIDX.TryGetValue("SampleNumber", out idxVal);
                                    if (foundEntry)
                                    {
                                        string ii = items[idxVal];
                                        sampleNumber = ii;

                                    }
                                    idxVal = 0;
                                    foundEntry = columnIDX.TryGetValue("SampleName", out idxVal);
                                    if (foundEntry)
                                    {
                                        string ii = items[idxVal];
                                        sampleName = ii;

                                    }
                                    idxVal = 0;
                                    foundEntry = columnIDX.TryGetValue("SampleNumber", out idxVal);
                                    if (foundEntry)
                                    {
                                        string ii = items[idxVal];
                                        labsampleNumber = ii;

                                    }

                                    idxVal = 0;
                                    foundEntry = columnIDX.TryGetValue("SampleNumber", out idxVal);
                                    if (foundEntry)
                                    {
                                        string ii = items[idxVal];
                                        labBatchNumber = ii;
                                    }

                                    Sample xs = new Sample();
                                    if (isDuplicateInterval == true)
                                    {
                                        xs = duplicateList.First();
                                    }
                                    else
                                    {
                                        xs.SampleID = Guid.NewGuid();                                        
                                        xs.SampleName = sampleName;
                                        xs.SampleNumber = sampleNumber;
                                        xs.FromDepth = fromDepth;
                                        xs.ToDepth = toDepth;
                                        xs.HeaderID = holeID;
                                        xs.VersionUpdated = currentUpdateTimestamp;

                                        entityObj.Samples.AddObject(xs);
                                    }
                                    // Now iddentify the  program, Stage, Size fraction and wash fraction

                                    // get the program text
                                    string programType = null;
                                    if (cmProgram != null) {
                                        programType = items[cmProgram.sourceColumnNumber];
                                    }
                                    string stage = null;
                                    if (cmStage != null)
                                    {
                                        stage = items[cmStage.sourceColumnNumber];
                                    }
                                    string sizeFraction = null;
                                    if (cmSizeFraction  != null)
                                    {
                                        sizeFraction = items[cmSizeFraction.sourceColumnNumber];
                                    }
                                    string washFraction = null;
                                    if (cmWashFraction != null)
                                    {
                                        washFraction = items[cmWashFraction.sourceColumnNumber];
                                    }

                                    // see if the interfal has changed, wherby we will need to reset the sequence ID
                                    if (holeID != lastHoleID)
                                    {
                                        if (fromDepth != lastFromDepth && toDepth != lastToDepth)
                                        {
                                            // new interval
                                            WorkflowProcedureSequenceNumber = 1;
                                        }
                                        
                                    }
                                    if (!stage.Trim().Equals(lastStage))
                                    {
                                        WorkflowProcedureSequenceNumber = 1;
                                    }
                                    lastHoleID = holeID;
                                    lastFromDepth = fromDepth;
                                    lastToDepth = toDepth;
                                    lastStage = stage;
                                    AssayGroupWorkflow agWorkflowProgram = GetAssayGroupWorkflow(entityObj, programType, agGuid);
                                    AssayGroupWorkflowProcedure agWorkflowStage = GetAssayGroupWorkflowProcedure(entityObj, stage, agWorkflowProgram);


                                    AssayGroupSubsample agSS = new AssayGroupSubsample();
                                    agSS.AssayGroupID = agGuid;
                                    agSS.Sequence = seqNum;
                                    agSS.AssayGroupSubsampleID = Guid.NewGuid();
                                    agSS.SampleAntecedentID = xs.SampleID;
                                    agSS.OriginalSample = xs;
                                    agSS.AssayGroupWorkflowProcedureID = agWorkflowStage.AssayGroupWorkflowProcedureID;
                                    agSS.AssayGroupWorkflowProcedure = agWorkflowStage;
                                    entityObj.AssayGroupSubsamples.AddObject(agSS);
                                    entityObj.SaveChanges();
                                    seqNum++;
                                    AssayGroupSubsamplePrecondition agSizeFraction = GetAssayGroupPrecondition(entityObj, sizeFraction, "Size fraction", agSS.AssayGroupSubsampleID);
                                    
                                    AssayGroupSubsamplePrecondition agWashFraction = GetAssayGroupPrecondition(entityObj, washFraction, "Wash fraction",  agSS.AssayGroupSubsampleID);

                                    // now pick out all the mapped values
                                    // iterate over all [ASSAY RESULT] columns
                                    bool assayUpdated = false;
                                    bool assayAdded = false;
                                    foreach (KeyValuePair<ColumnMap, Guid> kvp in resultsColumns)
                                    {
                                        ColumnMap cm = kvp.Key;
                                        Guid g = kvp.Value; // this is the AssayGroupTestID

                                        AssayGroupTestResult testResult = new AssayGroupTestResult();
                                        testResult.AssayGroupSubsampleID = agSS.AssayGroupSubsampleID;
                                        testResult.AssayGroupTestResultID = Guid.NewGuid();
                                        testResult.AssayGroupTestID = g;
                                        testResult.SampleID = xs.SampleID;
                                        testResult.VersionUpdated = currentUpdateTimestamp;
                                        testResult.LabBatchNumber = labBatchNumber;
                                        //testResult.LabSampleNumber = labsampleNumber;
                                        Decimal result = new Decimal();
                                        if (items.Count >= cm.sourceColumnNumber)
                                        {
                                            bool parsedOK = Decimal.TryParse(items[cm.sourceColumnNumber], out result);
                                            if (parsedOK)
                                            {
                                                testResult.LabResult = result;
                                            }
                                            else
                                            {
                                                testResult.LabResultText = items[cm.sourceColumnNumber];
                                            }
                                        }
                                        else
                                        {
                                            mos.AddWarningMessage("Line " + linesRead + " contains too few columns to read " + cm.sourceColumnName);
                                        }

                                        entityObj.AssayGroupTestResults.AddObject(testResult);
                                        assayAdded = true;
                                    }

                                    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++;                            
                        }
                        entityObj.SaveChanges();

                    }
                    if (hasDuplicateIntervals)
                    {
                        mos.finalErrorCode = ModelImportStatus.DATA_CONSISTENCY_ERROR;
                    }
                    string numFKLookups = "FK lookups " + fkLookupCount;
                    mos.linesReadFromSource = ct - 1;
                    UpdateStatus("Finished writing coal quality data to database.", 0);
                }
                catch (Exception ex)
                {
                    UpdateStatus("Error writing qualities to database ", 0);
                    mos.AddErrorMessage("Error writing 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;
                    }
                }


                mos.linesReadFromSource = linesRead;

            }
        }
예제 #15
0
 public bool CheckOwnership(ISecured secured, ActionPermission permission)
 {
     using (new TransactionScope(TransactionScopeOption.Suppress))
     {
         var c = new XODBC(ApplicationConnectionString,null);
         var verified = new System.Data.Objects.ObjectParameter("verified", typeof(byte));
         c.X_SP_GetSecuredRight(secured.OwnerContactID, secured.OwnerApplicationID, secured.OwnerTableType, secured.OwnerReferenceID, secured.OwnerField,
             secured.CanRead || ((ActionPermission.Read & permission) == ActionPermission.Read)
             , secured.CanCreate || ((ActionPermission.Create & permission) == ActionPermission.Create)
             , secured.CanUpdate || ((ActionPermission.Update & permission) == ActionPermission.Update)
             , secured.CanDelete || ((ActionPermission.Delete & permission) == ActionPermission.Delete)
             , verified);
         return (bool)verified.Value;
     }
 }
예제 #16
0
 public bool IsValidInXODB(string username)
 {
     if (string.IsNullOrEmpty(username))
         return false;
     using (new TransactionScope(TransactionScopeOption.Suppress))
     {
         var c = new XODBC(ApplicationConnectionString,null);
         if (default(Guid) == (from u in c.Users join contacts in c.Contacts on u.UserId equals contacts.AspNetUserID where u.UserName == username && contacts.Username == username && contacts.Version == 0 && contacts.VersionDeletedBy == null select contacts.ContactID).SingleOrDefault())
             return false;
         else
             return true;
     }
     
 }
예제 #17
0
        private Authority AdminAuthority = new Authority(new Guid("370846E4-36DC-4BAC-8FB5-C788C730BB45"), "admin"); //Admin service account id, different from contact who is admin
        public Authority BuildAuthority(Guid? contactID)
        {            
            if (!IsValidInXODB() || !contactID.HasValue)
                throw new AuthorityException("No authority to connect to XODB.");

            using (new TransactionScope(TransactionScopeOption.Suppress))
            {
                var c = new XODBC(ApplicationConnectionString,null);

                var username = (from o in c.Contacts where o.ContactID == contactID && o.Version==0 && o.VersionDeletedBy==null select o.Username).Single();
                var userID = (from o in c.Users where o.ApplicationId == ApplicationID && o.UserName == username select o.UserId).Single();
                var r = (from o in c.Users
                         from x in o.aspnet_Roles where o.UserId==userID select x.RoleId);
                var myCompanies = (from o in c.Experiences where o.ContactID==contactID && o.CompanyID!=null select o.CompanyID).ToArray();
                var allCompanies = new List<Guid>();
                var rootCompanies = new List<Guid>();
                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);
                    }

                    //Get the roots that the company is in and the children
                    foreach (DataRow row in table.Rows)
                    {
                        var found = false;
                        var rowRoot = (Guid)row[1];
                        for (int i=1; i < table.Columns.Count; i+=2)
                        {
                            var checking = (Guid)row[i];
                            if (found && checking==allCompanies.Last())
                                break;
                            if (!found && myCompanies.Contains(checking))
                            {
                                found = true;
                                rootCompanies.Add(rowRoot);
                            }
                            if (found)
                                allCompanies.Add(checking);
                        }
                    }
                }

                //Get my licenses & applications, assets, models, parts
                var licenses = (from o in c.Licenses where o.ContactID==contactID && o.LicenseID!=null select o);
                var assets = (from o in c.LicenseAssets where !(from x in c.Licenses where x.ContactID==contactID select x.LicenseID).Contains(o.LicenseID.Value) select o);
                var parts = (from o in c.LicenseAssetModelParts where !(from x in assets select x.LicenseAssetID).Contains(o.LicenseAssetID.Value) select o);
                var users = (from o in c.Users where o.UserName==username select o.UserId);
                var applications = (from o in c.Applications join x in c.Users on o.ApplicationId equals x.ApplicationId where x.UserName == username select o.ApplicationId);
                var experiences = (from o in c.Experiences where o.ContactID == contactID select o);
                //TODO: Do this based on experience instead of all!!! HACK!
                var bl = (from o in c.SecurityBlacklists select o);
                var wl = (from o in c.SecurityWhitelists select o);
                return new Authority(contactID.Value, username, userID, ApplicationID, bl, wl, applications, r, experiences, licenses, assets, parts, users, allCompanies, rootCompanies);
            }
            

        }
예제 #18
0
        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 XODBC(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.");

        }
예제 #19
0
        public void UpdateSecurity(ISecured secured)
        {
            //TODO!!!: When writing security check for antecedentid = referenceid &/or version=0
            //First check user and owner rights, black list and white list against record
            //Then get ok
            if (secured.SecurityID.HasValue)
            {
                //Call Edit
                if (CheckOwnership(secured, ActionPermission.Read | ActionPermission.Update))
                {
                    using (new TransactionScope(TransactionScopeOption.Suppress))
                    {
                        var c = new XODBC(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();
                            s.AccessorContactID = secured.AccessorContactID;
                            s.AccessorApplicationID = secured.AccessorApplicationID;
                            s.AccessorCompanyID = secured.AccessorCompanyID;
                            s.AccessorProjectID = secured.AccessorProjectID;
                            s.AccessorRoleID = secured.AccessorRoleID;
                            s.CanCreate = secured.CanCreate;
                            s.CanRead = secured.CanRead;
                            s.CanDelete = secured.CanDelete;
                            s.CanUpdate = secured.CanUpdate;
                            s.VersionUpdated = DateTime.UtcNow;
                            s.VersionUpdatedBy = secured.OwnerContactID;
                        }
                        else
                        {
                            var s = (from o in c.SecurityWhitelists where o.SecurityWhitelistID == secured.SecurityID && o.Version== 0 && o.VersionDeletedBy == null select o).Single();
                            s.AccessorContactID = secured.AccessorContactID;
                            s.AccessorApplicationID = secured.AccessorApplicationID;
                            s.AccessorCompanyID = secured.AccessorCompanyID;
                            s.AccessorProjectID = secured.AccessorProjectID;
                            s.AccessorRoleID = secured.AccessorRoleID;
                            s.CanCreate = secured.CanCreate;
                            s.CanRead = secured.CanRead;
                            s.CanDelete = secured.CanDelete;
                            s.CanUpdate = secured.CanUpdate;
                            s.VersionUpdated = DateTime.UtcNow;
                            s.VersionUpdatedBy = secured.OwnerContactID;
                        }
                        c.SaveChanges();
                    }
                }
                else
                    throw new AuthorityException(string.Format("Incorrect permission for action: \"Update\" Contact: {0} Record: {1}", secured.AccessorContactID, secured.OwnerReferenceID));
                
            }
            else
            {
                //Call New
                if (CheckOwnership(secured, ActionPermission.Read | ActionPermission.Create))
                {
                    using (new TransactionScope(TransactionScopeOption.Suppress))
                    {
                        var c = new XODBC(ApplicationConnectionString,null);
                        if (secured.IsBlack)
                        {
                            var s = new SecurityBlacklist
                            {
                                SecurityBlacklistID = Guid.NewGuid(),
                                AccessorContactID = secured.AccessorContactID,
                                AccessorApplicationID = secured.AccessorApplicationID,
                                AccessorCompanyID = secured.AccessorCompanyID,
                                AccessorProjectID = secured.AccessorProjectID,
                                AccessorRoleID = secured.AccessorRoleID,
                                OwnerApplicationID = secured.OwnerApplicationID,
                                OwnerCompanyID = secured.OwnerCompanyID,
                                OwnerContactID = secured.OwnerContactID,
                                OwnerEntitySystemType = secured.OwnerEntitySystemType,
                                OwnerField = secured.OwnerField,
                                OwnerProjectID = secured.OwnerProjectID,
                                OwnerReferenceID = secured.OwnerReferenceID,
                                OwnerTableType = secured.OwnerTableType,
                                CanCreate = secured.CanCreate,
                                CanRead = secured.CanRead,
                                CanDelete = secured.CanDelete,
                                CanUpdate = secured.CanUpdate,
                                VersionOwnerContactID = secured.OwnerContactID,
                                VersionOwnerCompanyID = secured.OwnerCompanyID,
                                VersionUpdated = DateTime.UtcNow,
                                VersionUpdatedBy = secured.OwnerContactID
                            };
                            c.SecurityBlacklists.AddObject(s);
                        }
                        else
                        {
                            var s = new SecurityWhitelist
                            {
                                SecurityWhitelistID = Guid.NewGuid(),
                                AccessorContactID = secured.AccessorContactID,
                                AccessorApplicationID = secured.AccessorApplicationID,
                                AccessorCompanyID = secured.AccessorCompanyID,
                                AccessorProjectID = secured.AccessorProjectID,
                                AccessorRoleID = secured.AccessorRoleID,
                                OwnerApplicationID = secured.OwnerApplicationID,
                                OwnerCompanyID = secured.OwnerCompanyID,
                                OwnerContactID = secured.OwnerContactID,
                                OwnerEntitySystemType = secured.OwnerEntitySystemType,
                                OwnerField = secured.OwnerField,
                                OwnerProjectID = secured.OwnerProjectID,
                                OwnerReferenceID = secured.OwnerReferenceID,
                                OwnerTableType = secured.OwnerTableType,
                                CanCreate = secured.CanCreate,
                                CanRead = secured.CanRead,
                                CanDelete = secured.CanDelete,
                                CanUpdate = secured.CanUpdate,
                                VersionOwnerContactID = secured.OwnerContactID,
                                VersionOwnerCompanyID = secured.OwnerCompanyID,
                                VersionUpdated = DateTime.UtcNow,
                                VersionUpdatedBy = secured.OwnerContactID
                            };
                            c.SecurityWhitelists.AddObject(s);
                        }
                        c.SaveChanges();
                    }
                }
                else
                    throw new AuthorityException(string.Format("Incorrect permission for action: \"Create\" Contact: {0} Record: {1}", secured.AccessorContactID, secured.OwnerReferenceID));
            }

        }
예제 #20
0
        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 XODBC(ApplicationConnectionString,null);
                    var r = from o in c.Roles.Include("aspnet_Users") where o.ApplicationId == ApplicationID select o;
                    var u = from o in c.Users.Include("aspnet_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("Orchard 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();
                    foreach (var role in r)
                    {
                        foreach (var user in role.aspnet_Users)
                        {
                            //Remove
                            if (!orchardUserRoles.Any(f => f.RoleName == role.RoleName && f.UserName == user.UserName))
                                user.aspnet_Roles.Remove(role);
                        }
                        foreach (var user in u)
                        {
                            //Add
                            if (orchardUserRoles.Any(f => f.RoleName == role.RoleName && f.UserName == user.UserName) && !role.aspnet_Users.Any(f=>f.UserName == user.UserName))
                                user.aspnet_Roles.Add(role);
                        }
                    }
                    c.SaveChanges();
                    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);
                    }
                    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 XODB Users
                Contact[] xodbusers;
                using (new TransactionScope(TransactionScopeOption.Suppress))
                {
                    var d = new XODBC(ApplicationConnectionString,null);
                    xodbusers = (from o in d.Contacts select o).ToArray();

                    //Sync AD, Orchard, XODB
                    //New into XODB
                    //We need firstname, surname
                    var ad_new = (from o in adusers where o.givenName != null && o.sn != null && (o.guid.HasValue && !(from x in xodbusers select x.ContactID).Contains((Guid)o.guid)) || (!o.guid.HasValue && !(from x in xodbusers 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 XODB
                    var ad_diff = from o in adusers
                                  from x in xodbusers
                                  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 = xodbusers.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();

                }
            }


        }
예제 #21
0
        //private AssayGroupSubsample GetAssayGroupSubSample(XODBC entityObj, Guid agGuid, Guid? workflowID, Sample originalSample, int seqNum)
        //{
            
            

            
        //    return agw;
        //}

        private AssayGroupSubsamplePrecondition GetAssayGroupPrecondition(XODBC entityObj, string preconditionName, string preconditionType, Guid ssGuid)
        {
            AssayGroupSubsamplePrecondition agw = null;
            //IQueryable<AssayGroupSubsamplePrecondition> res = entityObj.AssayGroupSubsamplePreconditions.Where(c => c.PreconditionName.Trim().Equals(preconditionName.Trim()) && c.AssayGroupSubsampleID == ssGuid);
            //foreach (AssayGroupSubsamplePrecondition xx in res)
            //{
            //    agw = xx;
            //}
            if (agw == null)
            {
                agw = new AssayGroupSubsamplePrecondition();
                agw.PreconditionName = preconditionName;
                agw.AssayGroupSubsampleID = ssGuid;
                agw.AssayGroupSubsamplePreconditionID = Guid.NewGuid();
                //TODO - make this more efficient by storing the Parameters in a dicitonary so lookup is fast rather than 
                // hitting the DB for every record
                Guid gParam = this.FindParameter("AssayPrecondition", preconditionType);
                agw.PreconditionParameterID = gParam;
                //agw.PreconditionParameterID = new Guid("6f49ded6-fe9b-487f-be48-eb8c88d9beef"); //Sixe mm TODO FIX
                
                //+32 bigger than 32 (Size fractions)
                //-32 smaller than 32
                //+16 bigger than 16
                //-16+8 smaller than 16, bigger than 8
                //-8+4
                //-4+2
                //-2+1
                //-1+0.5

                //F1.45 Floatation (density floats)
                //F1.45
                //S1.45-F1.50
                //S1.50-F1.55
                //S1.55-F1.60
                //S1.60-F1.70
                //S1.70-F1.80
                //S1.80-F2.00
                //S2.00 (sinks)

                //P2 Froth duration (30 sec)
                //P3
                //P4
                //P5
                //T2
                //T1


                entityObj.AssayGroupSubsamplePreconditions.AddObject(agw);
                entityObj.SaveChanges();

            }
            return agw;
        }
예제 #22
0
 public IEnumerable<Contact> GetContacts()
 {
     using (new TransactionScope(TransactionScopeOption.Suppress))
     {
         var dataContext = new XODBC(ApplicationConnectionString,null);
         return dataContext.Contacts.OrderBy(x=>x.ContactName).ToArray();
     }
 }
예제 #23
0
        private AssayGroupWorkflowProcedure GetAssayGroupWorkflowProcedure(XODBC entityObj, string stage, AssayGroupWorkflow assayGroupWorkflow)
        {
            AssayGroupWorkflowProcedure agw = null;
            //IQueryable<AssayGroupWorkflowProcedure> res = entityObj.AssayGroupWorkflowProcedures.Where(c => c.WorkflowStateName.Trim().Equals(stage.Trim()) && c.AssayGroupWorkflowID == assayGroupWorkflow.AssayGroupWorkflowID);
            //foreach (AssayGroupWorkflowProcedure xx in res)
            //{
            //    agw = xx;
            //}
            //if (agw == null)
            //{
                
                agw = new AssayGroupWorkflowProcedure();
                agw.AssayGroupWorkflowID = assayGroupWorkflow.AssayGroupWorkflowID;
                agw.AssayGroupWorkflow = assayGroupWorkflow;
                agw.AssayGroupWorkflowProcedureID = Guid.NewGuid();
                agw.WorkflowStateName = stage;
                agw.Sequence = WorkflowProcedureSequenceNumber;
                
                WorkflowProcedureSequenceNumber++;
                entityObj.AssayGroupWorkflowProcedures.AddObject(agw);
                entityObj.SaveChanges();

            //}
            return agw;
        }
예제 #24
0
 public Dictionary<Guid, string> GetRoles()
 {
     using (new TransactionScope(TransactionScopeOption.Suppress))
     {
         var c = new XODBC(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);
     }
 }
예제 #25
0
        internal List<object> ImportLASFile(Xstract.Import.LAS.LASFile lasFile, string origFilename, ModelImportStatus mos, Guid currentProject, Action<string, double> UpdateStatus)
        {
            int li = origFilename.LastIndexOf("\\");
            string tempHoleIDa = origFilename.Substring(li);
            li = tempHoleIDa.LastIndexOf(".");
            string tempHoleID = tempHoleIDa.Substring(1, li - 1);
            int rowCounter = 0;
            // now try and get the hole name from a header item.  Typically the header name might be in
            // WELL in the Well information header section
            string res = lasFile.LookupWellHeaderSection("WELL");
            if (res != null && res.Trim().Length > 0) {

                tempHoleID = res;
            }

            List<object> dataList = new List<object>();
            try
            {
                // here we need to create the Geophyiscs data row item
                var entityObj = new XODBC(BaseImportTools.XSTRING, null, false);
                //entityObj.AutoDetectChangesEnabled = false; //TODO: Exhaust this, should be faster now

                var physDataList = new List<Geophysics>();
                //var fDataList = new List<XODB.Module.BusinessObjects.File>();
                var fdDataList = new List<FileData>();
                
                Geophysics xG = new Geophysics();
                xG.FileName = origFilename;
                Guid gg = Guid.NewGuid();
                xG.GeophysicsID = gg;

                Guid holeGuid = CollarQueries.FindHeaderGuid(tempHoleID, currentProject);
                Guid resHole = new Guid();
                if (!holeGuid.ToString().Equals(resHole.ToString()))
                {
                    xG.HeaderID = holeGuid;
                }
                Guid unitGuid = new Guid("2395DE56-8F6F-4B0C-806C-DD2606B9902B"); //FIXME: Magic Number
                UnitQueries uq = new UnitQueries();
                DictionaryUnit xu = uq.FindUnits("m");
                if (xu != null)
                {
                    unitGuid = xu.UnitID;
                }
                xG.DimensionUnitID = unitGuid;
                xG.LasVersion = string.Format("{0:N1}",lasFile.versionValue);
                xG.LasWrap = lasFile.versionWrap;
                xG.LasNullValue = string.Format("{0:N2}",lasFile.nullValue);

                FileStream sr = null;
                try
                {
                    sr = new FileStream(lasFile.filePath, FileMode.Open);
                }
                catch (FileNotFoundException fex)
                {
                    Console.WriteLine("FileNotFoundException:" + fex.ToString());
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.ToString());
                }

                XODB.Module.BusinessObjects.File F = new XODB.Module.BusinessObjects.File();
                F.LoadFromStream(lasFile.FileName(), sr);
                sr = null;
                Guid fdGUID = Guid.NewGuid();
                var fD = new FileData
                {
                    Author = default(string),
                    FileDataID = fdGUID,
                    ReferenceID = xG.GeophysicsID,
                    TableType = "X_Geophysics",
                    FileName = F.FileName,
                    FileBytes = F.FileBytes,
                    FileChecksum = Hash.ComputeHash(F.FileBytes),
                    MimeType = MimeTypes.MimeTypeHelper.GetMimeTypeByFileName(F.FileName)
                };
                xG.OriginalFileDataID = fD.FileDataID;
                physDataList.Add(xG);
                fdDataList.Add(fD);
                F = null;

                // here we need to add a GeophysicsMetadata item for each column
                Dictionary<string, Guid> metaDataIDLookup = new Dictionary<string, Guid>();
                var unitDataList = new List<DictionaryUnit>();
                var paramDataList = new List<Parameter>();
                var metaDataList = new List<GeophysicsMetadata>();

                foreach (string s in lasFile.columnHeaders)
                {
                    Parameter xp = null;
                    xp = GetParameterIDFor(entityObj, "LAS data column", s);

                    //test to see if we already have the unit
                    string splitter = s.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries).LastOrDefault().Trim();
                    uq = new UnitQueries();
                    xu = uq.FindUnits(splitter);

                    if (xp != null && xu != null)
                    {
                        xp.UnitID = xu.UnitID;
                        //xp.Unit = xu;
                    }
                    else
                    {
                        bool xpnull = false;
                        if (xp == null)
                        {
                            xpnull = true;
                            xp = new Parameter();
                            Guid pg = Guid.NewGuid();
                            xp.ParameterID = pg;
                            xp.ParameterType = "LAS data column";
                            xp.ParameterName = s;
                        }

                        //test to see if we already have added a unit into the list
                        if (unitDataList.Count > 0 && xu == null)
                        {
                            xu = unitDataList.Where(c => c.StandardUnitName == splitter).FirstOrDefault();
                        }
                        if (xu == null)
                        {
                            //create new unit here store it and pass to parameters
                            Guid ug = Guid.NewGuid();
                            xu = new DictionaryUnit
                            {
                                UnitID = ug,
                                StandardUnitName = splitter,
                                CoalUnitName = splitter,
                                StrictlySI = false
                            };

                            unitDataList.Add(xu);
                        }
                        
                        xp.UnitID = xu.UnitID;
                        if (xpnull)
                        {
                            paramDataList.Add(xp);
                        }
                    }

                    Guid gmid = Guid.NewGuid();
                    GeophysicsMetadata xgm = new GeophysicsMetadata
                    {
                        GeophysicsID = gg,
                        GeophysicsMetadataID = gmid,
                        Unit = xu.StandardUnitName,
                        Mnemonic = s,
                        ParameterID = xp.ParameterID
                    };

                    metaDataList.Add(xgm);
                    metaDataIDLookup.Add(s, gmid);
                }
                
                int insertCounter = 0;
                var geoDataList = new List<GeophysicsData>();
                foreach (LASDataRow ldr in lasFile.dataRows)
                {
                    double depth = ldr.depth;
                    
                    for (int i = 0; i < ldr.rowData.Count(); i++)
                    {
                        GeophysicsData xd1 = new GeophysicsData();
                        string s = lasFile.columnHeaders[i];
                        xd1.GeophysicsDataID = Guid.NewGuid();
                        Guid g = new Guid();
                        bool found = metaDataIDLookup.TryGetValue(s, out g);
                        if (found)
                        {
                            xd1.GeophysicsMetadataID = g;
                        }
                        xd1.Dimension = (decimal)depth;
                        xd1.MeasurementValue = (decimal)ldr.rowData[i];

                        geoDataList.Add(xd1);
                    }
                    insertCounter++;
                    rowCounter++;
                }

                lasFile = null;

                dataList.Add(fdDataList);
                dataList.Add(physDataList);
                dataList.Add(unitDataList);
                dataList.Add(paramDataList);
                dataList.Add(metaDataList);
                dataList.Add(geoDataList);

                fdDataList = null;
                physDataList = null;
                unitDataList = null;
                paramDataList = null;
                metaDataIDLookup = null;
                metaDataList = null;
                geoDataList = null;
            }
            catch (Exception ex) {
                mos.errorMessages.Add("Failed to complete import of LAS file: " + origFilename);
                mos.errorMessages.Add("Details: " + ex.Message.ToString());
                if (ex.InnerException != null)
                    mos.errorMessages.Add("Inner Exception: " + ex.InnerException.Message.ToString());
                mos.errorMessages.Add("Row: " + rowCounter);
            }
            mos.recordsAdded = rowCounter;
            return dataList;
        }
예제 #26
0
        public Dictionary<Guid,string> GetCompanies()
        {
            var allCompanies = new Dictionary<Guid,string>();
            using (new TransactionScope(TransactionScopeOption.Suppress))
            {
                var c = new XODBC(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);
        }
예제 #27
0
        internal void AddAssayData(ModelImportStatus mos, Stream fileStream, FormatSpecification.ImportDataMap importMap,
                                    int batchSize, Action<string, double> UpdateStatus, int approxNumLines, 
                                    string connectionString, Guid XODBProjectID, 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 XODBC(connectionString, null))
            {

                //entityObj.Configuration.AutoDetectChangesEnabled = false;
                Guid agGuid = Guid.NewGuid();
                AssayGroup ag = new AssayGroup();
                ag.AssayGroupID = agGuid;
                ag.ProjectID = XODBProjectID;
                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
                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;
                    }
                    string 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;

                    PopulateCMapShortcut("HeaderID", importMap, columnIDX);
                    PopulateCMapShortcut("FromDepth", importMap, columnIDX);
                    PopulateCMapShortcut("ToDepth", importMap, columnIDX);
                    PopulateCMapShortcut("SampleNumber", importMap, columnIDX);
                    PopulateCMapShortcut("LabSampleNumber", importMap, columnIDX);
                    PopulateCMapShortcut("LabBatchNumber", importMap, columnIDX);
                    ColumnMap headerCmap = importMap.FindItemsByTargetName("HeaderID");
                    AssayQueries assayQueries = new AssayQueries();

                    if (sr != null)
                    {
                        while ((line = sr.ReadLine()) != null)
                        {

                            repCount++;

                            pct = ((float)linesRead / (float)approxNumLines) * 100.0f;
                            bct++;
                            linesRead++;
                            if (ct >= importMap.dataStartLine)
                            {

                                // digest a row of input data 
                                List<string> items = parseTestLine(line, importMap.inputDelimiter);


                                Guid holeID = new Guid();
                                Decimal fromDepth = new Decimal(-9999999999);
                                Decimal toDepth = new Decimal(-9999999999);
                                string sampleNumber = null;
                                string labBatchNumber = null;
                                string labsampleNumber = 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, XODBProjectID);
                                        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("SampleNumber", out idxVal);
                                    if (foundEntry)
                                    {
                                        string ii = items[idxVal];
                                        labsampleNumber = ii;

                                    }

                                    //cmap = importMap.FindItemsByTargetName("LabBatchNumber");
                                    idxVal = 0;
                                    foundEntry = columnIDX.TryGetValue("SampleNumber", out idxVal);
                                    if (foundEntry)
                                    {
                                        string ii = items[idxVal];
                                        labBatchNumber = 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;

                                        entityObj.Samples.AddObject(xs);
                                    }

                                    // now pick out all the mapped values
                                    // iterate over all [ASSAY RESULT] columns
                                    bool assayUpdated = false;
                                    bool assayAdded = false;
                                    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;
                                            }
                                            else
                                            {
                                                testResult.LabResultText = items[cm.sourceColumnNumber];
                                            }
                                        }
                                        else
                                        {
                                            mos.AddWarningMessage("Line " + linesRead + " contains too few columns to read " + cm.sourceColumnName);
                                        }

                                        //if (isDuplicateInterval == false)
                                        //{                                       
                                        entityObj.AssayGroupTestResults.AddObject(testResult);
                                        assayAdded = true;

                                        //}else{
                                        //    if (!assayResFound)
                                        //    {
                                        //        entityObj.AssayGroupTestResult.Add(testResult);
                                        //        assayAdded = true;


                                        //    }
                                        //    else {
                                        //        assayUpdated = true;
                                        //    }
                                        //}

                                    }

                                    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);
                        }
                        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" + 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;
                    }
                }


                mos.linesReadFromSource = linesRead;

            }
        }
예제 #28
0
 public string[] GetUserEmails(Guid[] users)
 {
     if (users == null || users.Length == 0)
         return new string[] { };
     using (new TransactionScope(TransactionScopeOption.Suppress))
     {
         var d = new XODBC(ApplicationConnectionString,null);
         var o = from c in d.Contacts where users.Contains(c.ContactID) && c.DefaultEmail != null
                 select c.DefaultEmail;
         return o.ToArray();
     }
 }
예제 #29
0
        private Guid FindParameterForAssayTypeName(string pName)
        {
            Guid pid = new Guid();
            Parameter xp = new Parameter();

            using (var entityObj = new XODBC(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;
            }
        }
예제 #30
0
 public Guid? GetContactID(string username)
 {
     if (username == null)
         return null;
     using (new TransactionScope(TransactionScopeOption.Suppress))
     {
         var d = new XODBC(ApplicationConnectionString,null);
         return (from c in d.Contacts join u in d.Users on c.AspNetUserID equals u.UserId where u.ApplicationId == ApplicationID select c.ContactID).Single();
         //return d.Contacts.Where(x=>x.Username == username).Select(x=>x.ContactID).FirstOrDefault();
     }
 }