/// <summary>
/// Processes the source data full.
/// </summary>
/// <param name="sourceDb">The source db.</param>
/// <remarks>Use this method for connecting to Full SQL</remarks>
public void CEtoFull(string sourceDb, string targetDb, List<string> tables)
{
var approvedSurveysID = new DataTable();
var filteredSurveys = string.Empty;
var filteredOffices = string.Empty;
var sqlCommands = new List<string>();
var ignoreList = new SyncUpdateIgnore();
var _buildQuerySync = new BuildQuerySync();
IList<string> _tables = tables;
using (var _sourceConn = new SqlCeConnection(sourceDb))
using (var _fTargetConn = new SqlConnection(targetDb))
{
_fTargetConn.Open();
var selectSubmitedSurveys = "select surveyID, officeID from surveys where surveystatus in (5,6)";
using (var getSurveysID = new SqlCeDataAdapter(selectSubmitedSurveys, _sourceConn))
{
getSurveysID.Fill(approvedSurveysID);
}
if(approvedSurveysID.Rows.Count == 0) return;
foreach (DataRow row in approvedSurveysID.Rows)
{
filteredSurveys = filteredSurveys + "'"+row[0] + "'" + ",";
filteredOffices = filteredOffices + "'" + row[1] + "'" + ",";
}
filteredSurveys = filteredSurveys.TrimEnd(',');
filteredOffices = filteredOffices.TrimEnd(',');
foreach (var table in _tables)
{
if (table == "Surveys")
{
var s = "";
}
using (var _fInsertCommand = new SqlCommand(_buildQuerySync.BuildInsertQuery(table, sourceDb),
_fTargetConn))
using (var _fUpdateCommand = new SqlCommand(_buildQuerySync.BuildUpdateQuery(table, sourceDb),
_fTargetConn))
{
var selectTable = string.Empty;
switch (table)
{
case "MitigationMeasures":
selectTable = "select * from " + table + " where officeid in ("+filteredOffices+")";
break;
case "SystemLogs":
selectTable = "select * from " + table;
break;
default:
selectTable = "select * from " + table + " where surveyid in ("+filteredSurveys+")";
break;
}
var _dt = new DataTable();
using (var _getSourceData = new SqlCeDataAdapter(selectTable, _sourceConn))
{
_getSourceData.Fill(_dt);
}
foreach (DataRow row in _dt.Rows)
{
var reRunCommand = _fInsertCommand.CommandText;
foreach (var column in _dt.Columns)
{
var columnName = column.ToString();
var value = row[Convert.ToString(column)];
_fInsertCommand.Parameters.AddWithValue("@" + columnName, value);
_fUpdateCommand.Parameters.AddWithValue("@" + columnName, value);
if (Convert.ToString(value) == "")
value = "null";
else
{
value = "'" + value + "'";
}
reRunCommand = reRunCommand.Replace("@" + columnName, Convert.ToString(value));
}
try
{
_fInsertCommand.ExecuteScalar();
}
catch (Exception ex)
{
//Use SyncUpdateIgnore list here to only allow inserting new rows into static tables
if (!ignoreList.IgnoreList.Contains(table))
{
if (ex.Message.Contains(
"A duplicate value cannot be inserted into a unique index.")
|| ex.Message.Contains("Violation of PRIMARY KEY constraint"))
{
if (_fUpdateCommand.CommandText.Contains("Update SurveyVersions")) break;
_fUpdateCommand.ExecuteScalar();
}
else
{
sqlCommands.Add(reRunCommand);
}
}
}
_fInsertCommand.Parameters.Clear();
_fUpdateCommand.Parameters.Clear();
}
}
sqlCommands.Clear();
}
_fTargetConn.Close();
_sourceConn.Close();
}
}
/// <summary>
/// Processes the source data.
/// </summary>
/// <param name="sourceDb">The source db.</param>
/// <remarks>This will loop over each table specified in the list. Builds both an insert query & update query
/// Attempts to insert first, if fails on duplicate it will update (SurveyVersions is the exception, it's only one column GUID)
/// Usce this method to connect to SQL CE</remarks>
public void CEtoCe(string sourceDb, string targetDb, List<string> tables)
{
var sqlCommands = new List<string>();
var buildQuerySync = new BuildQuerySync();
IList<string> _tables = tables;
var ignoreList = new SyncUpdateIgnore();
using (var _sourceConn = new SqlCeConnection(sourceDb))
using(var _targetConn = new SqlCeConnection(targetDb))
{
_targetConn.Open();
_sourceConn.Open();
foreach (var table in _tables)
{
string insertCommandText = buildQuerySync.BuildInsertQuery(table, sourceDb);
string updateCommandText = buildQuerySync.BuildUpdateQuery(table, sourceDb);
using (var insertCommand = new SqlCeCommand(insertCommandText, _targetConn))
using (var updateCommand = new SqlCeCommand(updateCommandText, _targetConn))
{
var selectTable = "select * from " + table;
var _dt = new DataTable();
using (var _getSourceData = new SqlCeDataAdapter(selectTable, _sourceConn))
{
_getSourceData.Fill(_dt);
}
foreach (DataRow row in _dt.Rows)
{
var reRunCommand = insertCommand.CommandText;
foreach (var column in _dt.Columns)
{
var columnName = column.ToString();
var value = row[Convert.ToString(column)];
insertCommand.Parameters.AddWithValue("@" + columnName, value);
updateCommand.Parameters.AddWithValue("@" + columnName, value);
if (Convert.ToString(value) == "")
value = "null";
else
{
value = "'" + value + "'";
}
reRunCommand = reRunCommand.Replace("@" + columnName, Convert.ToString(value));
}
try
{
insertCommand.ExecuteScalar();
}
catch (Exception ex)
{
//Use SyncUpdateIgnore list here to only allow inserting new rows into static tables
if (!ignoreList.IgnoreList.Contains(table))
{
if (ex.Message.Contains("A duplicate value cannot be inserted into a unique index."))
{
if (updateCommand.CommandText.Contains("Update SurveyVersions")) break;
updateCommand.ExecuteScalar();
}
else
{
sqlCommands.Add(reRunCommand);
}
}
}
insertCommand.Parameters.Clear();
updateCommand.Parameters.Clear();
}
foreach (var sqlCeCommand in sqlCommands)
{
try
{
insertCommand.CommandText = sqlCeCommand;
insertCommand.ExecuteScalar();
}
catch (Exception)
{
}
}
sqlCommands.Clear();
}
}
_targetConn.Close();
_sourceConn.Close();
}
}
public void FulltoCE(string sourceDb, string targetDb, List<string> tables, Guid userID, List<string> criteriaTable )
{
var sqlCommands = new List<string>();
var _buildQuerySync = new BuildQuerySync();
List<string> _tables = tables;
using (var _fSourceConn = new SqlConnection(sourceDb))
using(var _targetConn = new SqlCeConnection(targetDb))
{
_targetConn.Open();
_fSourceConn.Open();
foreach (var table in _tables)
{
using (var insertCommand = new SqlCeCommand(_buildQuerySync.BuildFullSqlInsertQuery(table, sourceDb),
_targetConn))
using (var updateCommand =
new SqlCeCommand(_buildQuerySync.BuildFullSqlUpdateQuery(table, sourceDb),
_targetConn))
{
var selectTable = "select * from " + table;
var selectID = "select surveyid from surveys where assessorid = '" + userID +
"' and surveystatus in (4,7,8)";
foreach (var criteria in criteriaTable)
{
if (criteria == table)
{
switch (criteria)
{
case "ResponseValues":
selectTable = "select * from " + table + " where surveyid in (" + selectID +
")";
break;
case "Surveys":
selectTable = "select * from " + table + " where surveyid in (" + selectID +
")";
break;
case "Documents":
selectTable = "select * from " + table + " where surveyid in (" + selectID +
")";
break;
case "HasardMatrix":
selectTable = "select * from " + table + " where surveyid in (" + selectID +
")";
break;
case "FnrValues":
selectTable = "select * from " + table + " where surveyid in (" + selectID +
")";
break;
case "Notifications":
selectTable = "select * from " + table + " where recipientID = '" + userID +
"'";
break;
default:
selectTable = "select * from " + table;
break;
}
}
}
using (var _fGetSourceData = new SqlDataAdapter(selectTable, _fSourceConn))
{
var _dt = new DataTable();
_fGetSourceData.Fill(_dt);
foreach (DataRow row in _dt.Rows)
{
var reRunCommand = insertCommand.CommandText;
foreach (var column in _dt.Columns)
{
var columnName = column.ToString();
var value = row[Convert.ToString(column)];
insertCommand.Parameters.AddWithValue("@" + columnName, value);
updateCommand.Parameters.AddWithValue("@" + columnName, value);
if (Convert.ToString(value) == "")
value = "null";
else
{
value = "'" + value + "'";
}
reRunCommand = reRunCommand.Replace("@" + columnName, Convert.ToString(value));
}
try
{
insertCommand.ExecuteScalar();
}
catch (Exception ex)
{
if (
ex.Message.Contains(
"A duplicate value cannot be inserted into a unique index."))
{
if (updateCommand.CommandText.Contains("Update SurveyVersions")) break;
updateCommand.ExecuteScalar();
}
else
{
sqlCommands.Add(reRunCommand);
}
}
insertCommand.Parameters.Clear();
updateCommand.Parameters.Clear();
}
}
foreach (var sqlCeCommand in sqlCommands)
{
try
{
insertCommand.CommandText = sqlCeCommand;
insertCommand.ExecuteScalar();
}
catch (Exception)
{
}
}
sqlCommands.Clear();
}
}
_targetConn.Close();
_fSourceConn.Close();
}
}
private void GenerateChromatograms(MsDataFile msDataFile, List<AnalysisChromatograms> analyses)
{
int totalAnalyses = analyses.Count;
if (totalAnalyses == 0)
{
return;
}
if (!UpdateProgress(0))
{
return;
}
analyses = new List<AnalysisChromatograms>(analyses);
using (var pwizMsDataFileImpl = new MsDataFileImpl(msDataFile.Path))
{
var completeAnalyses = new List<AnalysisChromatograms>();
int totalScanCount = pwizMsDataFileImpl.SpectrumCount;
double minTime = msDataFile.GetTime(msDataFile.GetSpectrumCount() - 1);
double maxTime = msDataFile.GetTime(0);
foreach (var analysis in analyses)
{
minTime = Math.Min(minTime, analysis.FirstTime);
maxTime = Math.Max(maxTime, analysis.LastTime);
}
int firstScan = msDataFile.FindScanIndex(minTime);
for (int iScan = firstScan; analyses.Count > 0 && iScan < totalScanCount; iScan++)
{
double time = msDataFile.GetTime(iScan);
int progress = (int)(100 * (time - minTime) / (maxTime - minTime));
progress = Math.Min(progress, 100);
progress = Math.Max(progress, 0);
if (!UpdateProgress(progress))
{
return;
}
List<AnalysisChromatograms> activeAnalyses = new List<AnalysisChromatograms>();
double nextTime = Double.MaxValue;
if (msDataFile.GetMsLevel(iScan, pwizMsDataFileImpl) != 1)
{
continue;
}
foreach (var analysis in analyses)
{
nextTime = Math.Min(nextTime, analysis.FirstTime);
if (analysis.FirstTime <= time)
{
activeAnalyses.Add(analysis);
}
}
if (activeAnalyses.Count == 0)
{
int nextScan = msDataFile.FindScanIndex(nextTime);
iScan = Math.Max(iScan, nextScan - 1);
continue;
}
double[] mzArray, intensityArray;
pwizMsDataFileImpl.GetSpectrum(iScan, out mzArray, out intensityArray);
foreach (var analysis in activeAnalyses)
{
var points = new List<MsDataFileUtil.ChromatogramPoint>();
foreach (var chromatogram in analysis.Chromatograms)
{
points.Add(MsDataFileUtil.GetIntensity(chromatogram.Mz, mzArray, intensityArray));
}
analysis.AddPoints(iScan, time, points);
}
var incompleteAnalyses = new List<AnalysisChromatograms>();
foreach (var analysis in analyses)
{
if (analysis.LastTime <= time)
{
completeAnalyses.Add(analysis);
}
else
{
incompleteAnalyses.Add(analysis);
}
}
if (completeAnalyses.Count > 10)
{
SaveChromatograms(completeAnalyses, null);
completeAnalyses.Clear();
}
analyses = incompleteAnalyses;
}
completeAnalyses.AddRange(analyses);
SaveChromatograms(completeAnalyses, msDataFile);
lock(this)
{
_activePeptideFileAnalysisIds.Clear();
}
}
}