public static List <string> GetMissingPageTitles(string pageContent) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <string> >(MethodBase.GetCurrentMethod(), pageContent)); } List <string> listInvalidLinks = new List <string>(); MatchCollection mc = Regex.Matches(pageContent, @"\[\[.+?\]\]");//Find [[ and ]] pairs in the pageContent string. List <string> listWikiLinks = new List <string>(); foreach (Match match in mc) { string val = match.Value; if (!IsWikiLink(val) || val.Contains("INVALID WIKIPAGE LINK")) { continue; } val = val.TrimStart('[').TrimEnd(']'); listWikiLinks.Add(val); } if (listWikiLinks.Count == 0) { return(listInvalidLinks); } string command = @"SELECT PageTitle FROM wikipage WHERE IsDraft=0 AND PageTitle IN('" + string.Join("','", listWikiLinks.Select(x => POut.String(x))) + "')"; HashSet <string> setValidLinks = new HashSet <string>(Db.GetListString(command)); listInvalidLinks = listWikiLinks.FindAll(x => !setValidLinks.Contains(x)); return(listInvalidLinks); }
///<summary>Queries information_schema.COLUMNS and returns all column names of given table.</summary> public static List <string> GetColumnNamesFromTableMySql(string tableName) { return(Db.GetListString(@" SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '" + DataConnection.GetDatabaseName() + "' AND TABLE_NAME='" + tableName + "'")); }
///<summary>Gets all distinct field names used by any ortho chart. Useful for displaying the "available" display fields.</summary> public static List <string> GetDistinctFieldNames() { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <string> >(MethodBase.GetCurrentMethod())); } string command = "SELECT FieldName FROM orthochart GROUP BY FieldName"; return(Db.GetListString(command)); }
///<summary>Gets list of CodeValue strings from interventions with DateEntry in the last year and CodeSet equal to the supplied codeSet. ///Result list is grouped by CodeValue, CodeSystem even though we only return the list of CodeValues. However, there are no codes in the ///EHR intervention code list that conflict between code systems, so we should never have a duplicate code in the returned list.</summary> public static List <string> GetAllForCodeSet(InterventionCodeSet codeSet) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <string> >(MethodBase.GetCurrentMethod(), codeSet)); } string command = "SELECT CodeValue FROM intervention WHERE CodeSet=" + POut.Int((int)codeSet) + " " + "AND " + DbHelper.DtimeToDate("DateEntry") + ">=" + POut.Date(MiscData.GetNowDateTime().AddYears(-1)) + " " + "GROUP BY CodeValue,CodeSystem"; return(Db.GetListString(command)); }
///<summary>Gets a list of unique proc codes for all eService code links in the table.</summary> public static List <string> GetProcCodesForAll() { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <string> >(MethodBase.GetCurrentMethod())); } string command = "SELECT procedurecode.ProcCode FROM procedurecode " + "INNER JOIN eservicecodelink ON procedurecode.CodeNum=eservicecodelink.CodeNum " + "GROUP BY eservicecodelink.CodeNum"; return(Db.GetListString(command)); }
///<summary></summary> public static List <string> GetFilterPhrases() { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <string> >(MethodBase.GetCurrentMethod())); } List <string> listPhrases = null; //Query the database for all phrases that we don't accept and see if the exception text passed in contains any of the phrases from the database. DataAction.RunBugsHQ(() => { listPhrases = Db.GetListString("SELECT Phrase FROM bugsubmissionfilter WHERE Phrase!=''"); }); return(listPhrases); }
///<summary>Gets codes (SNOMEDCT) from CodeValueResult for EhrMeasureEvents with DateTEvent within the last year for the given EhrMeasureEventType. ///Result list is grouped by code.</summary> public static List <string> GetListCodesUsedForType(EhrMeasureEventType eventType) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <string> >(MethodBase.GetCurrentMethod(), eventType)); } string command = "SELECT CodeValueResult FROM ehrmeasureevent " + "WHERE EventType=" + POut.Int((int)eventType) + " " + "AND CodeValueResult!='' " + "AND " + DbHelper.DtimeToDate("DateTEvent") + ">=" + POut.Date(MiscData.GetNowDateTime().AddYears(-1)) + " " + "GROUP BY CodeValueResult"; return(Db.GetListString(command)); }
///<summary>Gets list of RxCui code strings for medications with RxCui in the supplied list ordered for patients in the last year. ///"Ordered" is based on there being a DateStart. Result list is grouped by RxCui.</summary> public static List <string> GetAllForRxCuis(List <string> listRxCuis) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <string> >(MethodBase.GetCurrentMethod(), listRxCuis)); } if (listRxCuis == null || listRxCuis.Count == 0) { return(new List <string>()); } string command = "SELECT RxCui FROM medicationpat WHERE RxCui IN(" + string.Join(",", listRxCuis) + ") " + "AND " + DbHelper.DtimeToDate("DateStart") + ">=" + POut.Date(MiscData.GetNowDateTime().AddYears(-1)) + " " + "GROUP BY RxCui"; return(Db.GetListString(command)); }
///<summary>Gets all manual page names from database. Excludes entries in the manualpage table that are used for the manual index.</summary> public static List <string> GetAllManualPageNames() { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <string> >(MethodBase.GetCurrentMethod())); } string command = $@" SELECT manualpage.FileName FROM manualpage WHERE FileName NOT LIKE '+%'; " ; List <string> retVal = new List <string>(); DataAction.RunManualPublisherHQ(() => { retVal = Db.GetListString(command).Distinct().ToList(); }); return(retVal); }
///<summary>Returns a list of manual page names that the given FaqNum is currently linked to.</summary> public static List <string> GetLinkedManualPages(long faqNum) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <string> >(MethodBase.GetCurrentMethod(), faqNum)); } string command = $@" SELECT manualpage.FileName FROM faqmanualpagelink INNER JOIN manualpage ON manualpage.ManualPageNum=faqmanualpagelink.ManualPageNum WHERE faqmanualpagelink.FaqNum={faqNum} " ; List <string> retVal = new List <string>(); DataAction.RunManualPublisherHQ(() => { retVal = Db.GetListString(command); }); return(retVal); }
///<summary>Creates actions that load batches of data into the queue for inserting by the insert threads and runs them with ///QUEUE_BATCHES_THREAD_COUNT number of parallel threads. The threads will wait for the queue to drop below MAX_QUEUE_COUNT number of items ///before queuing another item.</summary> public static void QueueBatches(ODThread odThread) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { Meth.GetVoid(MethodBase.GetCurrentMethod(), odThread); return; } #if DEBUG Stopwatch s = new Stopwatch(); s.Start(); #endif int queueCount = 0; try { string dbName = GetCurrentDatabase(); string cmd = "SELECT COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS " + "WHERE TABLE_SCHEMA='" + POut.String(dbName) + "' AND TABLE_NAME='" + POut.String(_tempTableName) + "'"; //Dictionary of key=column name, value=data type for the current table. Used to determine whether a row value will need to have special //characters escaped and will need to be surrounded with quotes (using the MySql QUOTE() method). Dictionary <string, string> dictColNamesAndTypes = Db.GetTable(cmd).Select() .ToDictionary(x => PIn.String(x["COLUMN_NAME"].ToString()), x => PIn.String(x["DATA_TYPE"].ToString())); if (dictColNamesAndTypes.Count < 1) { return; //table doesn't have any columns? nothing to do? } #region Get Query Strings //data types that require special characters escaped and will be surrounded by quotes (using the MySql QUOTE() method). string[] dataTypeQuotesArr = new[] { "date", "datetime", "timestamp", "time", "char", "varchar", "text", "mediumtext", "longtext", "blob", "mediumblob", "longblob" }; StringBuilder sbGetSelectCommand = new StringBuilder(@"SELECT CONCAT('('"); List <string> listWheres = new List <string>(); int index = 0; foreach (KeyValuePair <string, string> kvp in dictColNamesAndTypes) { sbGetSelectCommand.Append(@","); if (index > 0) { sbGetSelectCommand.Append(@"',',"); } if (dataTypeQuotesArr.Contains(kvp.Value)) { sbGetSelectCommand.Append(@"QUOTE(" + kvp.Key + @")"); } else { sbGetSelectCommand.Append(POut.String(kvp.Key)); } index++; } sbGetSelectCommand.Append(@",')') vals FROM `" + _tempTableName + "` "); for (int i = 0; i < _listPriKeyMaxPerBatch.Count; i++) { string where = "WHERE " + POut.String(_tablePriKeyField) + "<=" + POut.Long(_listPriKeyMaxPerBatch[i]); if (i > 0) { where += " AND " + POut.String(_tablePriKeyField) + ">" + POut.Long(_listPriKeyMaxPerBatch[i - 1]); } listWheres.Add(where); } #endregion Get Query Strings #region Run Commands and Queue Results #region Create List of Actions List <Action> listActions = new List <Action>(); string colNames = string.Join(",", dictColNamesAndTypes.Keys.Select(x => POut.String(x))); foreach (string whereStr in listWheres) { listActions.Add(new Action(() => { List <string> listRowVals = Db.GetListString(sbGetSelectCommand.ToString() + whereStr); if (listRowVals == null || listRowVals.Count == 0) { return; } string commandValuesInsert = "REPLACE INTO `" + _tableName + "` (" + colNames + ") VALUES " + string.Join(",", listRowVals); string commandBulkInsert = "REPLACE INTO `" + _tableName + "` (" + colNames + ") SELECT " + colNames + " FROM `" + _tempTableName + "` " + whereStr + " " + "AND " + POut.String(_tablePriKeyField) + " NOT IN (SELECT " + POut.String(_tablePriKeyField) + " FROM `" + _tableName + "` " + whereStr + ")"; bool isDataQueued = false; while (!isDataQueued) { lock (_lockObjQueueBatchQueries) { if (_queueBatchQueries.Count < MAX_QUEUE_COUNT) //Wait until queue is a reasonable size before queueing more. { _queueBatchQueries.Enqueue(new BatchQueries(commandValuesInsert, commandBulkInsert)); isDataQueued = true; queueCount++; } } if (!isDataQueued) { Thread.Sleep(100); } } })); } //end of command loop #endregion Create List of Actions ODThread.RunParallel(listActions, TimeSpan.FromHours(12), QUEUE_BATCHES_THREAD_COUNT, new ODThread.ExceptionDelegate((ex) => { ODEvent.Fire(ODEventType.ConvertDatabases, new ProgressBarHelper("Error queuing batch: " + ex.Message, progressBarEventType: ProgBarEventType.TextMsg)); })); #endregion Run Commands and Queue Results } catch (Exception ex) { //Don't pass along any exceptions because the main thread will validate that the table was successfully copied and will throw for us. ODEvent.Fire(ODEventType.ConvertDatabases, new ProgressBarHelper("Error queuing batch: " + ex.Message, progressBarEventType: ProgBarEventType.TextMsg)); } finally { //always make sure to notify the main thread that the thread is done so the main thread doesn't wait for eternity _areQueueBatchThreadsDone = true; #if DEBUG s.Stop(); Console.WriteLine("QueueQueryBatches - Done, queued " + queueCount + " out of " + _listPriKeyMaxPerBatch.Count + " batches of " + _rowsPerBatch + " rows: " + (s.Elapsed.Hours > 0?(s.Elapsed.Hours + " hours "):"") + (s.Elapsed.Minutes > 0?(s.Elapsed.Minutes + " min "):"") + (s.Elapsed.TotalSeconds - (s.Elapsed.Hours * 60 * 60) - (s.Elapsed.Minutes * 60)) + " sec"); #endif } }