private void CopyAssessmentData(int oldFormResultId, int copyFormResultId)
{
try
{
using (DbConnection connection = UasAdo.GetUasAdoConnection())
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText
= "SELECT SELECT IR.itemId, RV.itemVariableId, RV.rspValue from def_ItemResults IR JOIN def_FormResults FR on FR.formResultId = IR.formResultId JOIN def_ResponseVariables RV on RV.itemResultId = IR.itemResultId WHERE FR.formResultId = " + oldFormResultId + " ORDER BY itemId";
command.CommandType = CommandType.Text;
DataTable dt = new DataTable();
dt.Load(command.ExecuteReader());
if (dt != null)
{
SaveAssessmentFromDataTable(copyFormResultId, dt);
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine("* * * CreateFormResultJSON exception: " + ex.Message);
}
}
/// <summary>
/// Downloads the DEF Meta Data file in CSV format and loads into the local Venture table.
/// </summary>
/// <param name="cc"></param>
/// <param name="iFileId"></param>
/// <returns></returns>
public string ProcessMetaSync(CookieContainer cc, int iFileId)
{
string msg = String.Empty;
try
{
// Construct HTTP request to get the file
string requestUrl = sisOnlineURL + "Export/GetMetaCsv?fileId=" + iFileId.ToString() + "&ventureVersion=2";
Debug.WriteLine("DataSyncController - ProcessMetaSync requestUrl: " + requestUrl);
HttpWebRequest httpRequest = (HttpWebRequest)WebRequest.Create(Uri.EscapeUriString(requestUrl));
httpRequest.Method = WebRequestMethods.Http.Get;
httpRequest.CookieContainer = cc;
// Get back the HTTP response for web server
using (HttpWebResponse httpResponse = (HttpWebResponse)httpRequest.GetResponse())
{
using (Stream httpResponseStream = httpResponse.GetResponseStream())
{
DataTable csvData = new DataTable();
using (var reader = new CsvReader(httpResponseStream))
{
// the CSV file has a header record, so we read that first
reader.ReadHeaderRecord();
csvData.Fill(reader);
}
msg = csvData.Rows.Count.ToString();
csvData = RemoveNullString(csvData);
csvData.TableName = formsSql.GetMetaDataTableName(iFileId);
Debug.WriteLine("Table {0} contains {1} rows.", csvData.TableName, msg);
if (csvData.TableName == "def_Sections")
{
MakeReportsInvisible(ref csvData);
}
MarkDuplicates(ref csvData);
using (DbConnection connection = UasAdo.GetUasAdoConnection())
{
FillDatabaseTable(csvData, connection);
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine("* * * DataSync.ProcessMetaSync exception: " + ex.Message);
msg = ex.Message;
}
return(msg);
}
private void FilterPreExistingOnVenture(ref DataTable csvData)
{
DataTable dataTable = new DataTable();
try
{
using (DbConnection connection = UasAdo.GetUasAdoConnection())
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT " + csvData.TableName + ".* FROM " + csvData.TableName;
command.CommandText += formsSql.GenerateQuery(csvData.TableName);
command.CommandType = CommandType.Text;
using (DbDataReader reader = command.ExecuteReader())
{
dataTable.Load(reader);
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine("* * * Take out assessments with non-deleted status on Venture: " + ex.Message);
throw ex;
}
int index = 0;
if ((csvData.TableName == "Contact") || (csvData.TableName == "Recipient"))
{
index = 1;
}
foreach (DataRow row in dataTable.Rows)
{
string expression = dataTable.Columns[index].ColumnName + " = " + "'" + row[index].ToString() + "'";
DataRow[] rowsToUpdate = csvData.Select(expression);
foreach (DataRow r in rowsToUpdate)
{
r.AcceptChanges();
r.Delete();
}
}
csvData.AcceptChanges();
}
/// <summary>
/// Marks any incoming rows that are already in the local database.
/// </summary>
/// <param name="csvData"></param>
private void MarkDuplicates(ref DataTable csvData)
{
DataTable dataTable = new DataTable();
try
{
using (DbConnection connection = UasAdo.GetUasAdoConnection())
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM " + csvData.TableName;
command.CommandType = CommandType.Text;
using (DbDataReader reader = command.ExecuteReader())
{
dataTable.Load(reader);
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine("* * * Mark Duplicates: " + ex.Message);
}
int index = 0;
if (csvData.TableName == "Contact" || csvData.TableName == "Recipient")
{
index = 1;
}
foreach (DataRow row in dataTable.Rows)
{
string expression = dataTable.Columns[index].ColumnName + " = " + "'" + row[index].ToString() + "'";
DataRow[] rowsToUpdate = csvData.Select(expression);
foreach (DataRow r in rowsToUpdate)
{
r.AcceptChanges();
r.SetModified();
}
}
}
public string DownloadUasTables(CookieContainer cc)
{
// fileIds and filters
// 0 - Config - All recs
// 1 - Enterprise - a single record - the Enterprise of the User (from Session)
// 2 and 3 - GroupTypes / Groups for the Enterprise
// 4 - User - just the user logged in (from Session)
IUasSql uas = new UasSql();
string msg = String.Empty;
for (int i = 0; i < uas.GetNumberTables(); i++)
{
try
{
HttpWebRequest httpRequest = null;
httpRequest = (HttpWebRequest)WebRequest.Create(sisOnlineURL + "Export/GetUasCsv?fileId=" + i.ToString());
httpRequest.CookieContainer = cc;
httpRequest.Method = WebRequestMethods.Http.Get;
// Get back the HTTP response for web server
using (HttpWebResponse httpResponse = (HttpWebResponse)httpRequest.GetResponse())
{
using (Stream httpResponseStream = httpResponse.GetResponseStream())
{
DataTable csvData = new DataTable();
csvData.TableName = uas.GetUasTableName(i);
Debug.WriteLine("DataSync DownloadUasTables CvsReader on table: " + csvData.TableName);
// Debug.WriteLine(" httpResponseStream.Length: " + httpResponseStream.Length.ToString());
using (var reader = new CsvReader(httpResponseStream))
{
// the CSV file has a header record, so we read that first
reader.ReadHeaderRecord();
csvData.Fill(reader);
}
Debug.WriteLine(" cvsData Table {0} contains {1} rows.", csvData.TableName, csvData.Rows.Count);
msg = csvData.Rows.Count.ToString();
csvData = RemoveNullString(csvData);
Debug.WriteLine(" Modify csvData: " + csvData.TableName);
if (csvData == null)
{
Debug.WriteLine(" uh-oh csvData is Null!!!");
}
using (DbConnection connection = UasAdo.GetUasAdoConnection())
{
if (csvData.TableName == "uas_Group")
{
SetParentGroupToSelf(ref csvData);
}
SetCreatedByAndModifiedByToOne(ref csvData);
MarkDuplicates(ref csvData);
FillDatabaseTable(csvData, connection);
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine("* * * DataSync exception: " + ex.Message);
if (ex.InnerException != null && ex.InnerException.Message != null)
{
Debug.WriteLine("Inner exception: " + ex.InnerException.Message);
}
msg = ex.Message;
}
}
return(msg);
}
public string DownloadTable()
{
string paramFileId = Request["fileId"] as string;
int iFileId = Convert.ToInt32(paramFileId);
string paramTableName = Request["tableName"] as string;
Debug.WriteLine("* * * DataSync DownloadTable fileId: " + iFileId.ToString());
Debug.WriteLine("* * * DataSync DownloadTable tableName: " + paramTableName);
string msg = String.Empty;
// If the first table to download, login to the remote/master server.
if (paramTableName == "def_FormResults")
{
try
{
CookieContainer cc = new CookieContainer();
string lnk = ConfigurationManager.AppSettings["SISOnlineUrl"] + "Defws/Login?UserId=" + SessionHelper.LoginInfo.LoginID + "&pwrd=" + SessionHelper.LoginInfo.Password;
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(lnk);
request.Timeout = -1;
request.CookieContainer = cc;
Session["CookieContainer"] = cc;
using (WebResponse response = request.GetResponse())
{
Debug.WriteLine("DataSync HttpWebResponse Status: " + ((HttpWebResponse)response).StatusDescription);
using (Stream dataStream = ((HttpWebResponse)response).GetResponseStream())
{
}
}
}
catch (Exception xcptn)
{
Debug.WriteLine("DataSync.DownloadTable Defws Login HttpWebRequest: " + xcptn.Message);
}
}
try
{
// Construct HTTP request to get the file
string requestUrl = sisOnlineURL + "Export/GetResponseCsv?fileId=" + iFileId.ToString() + "&ventureVersion=2";
Debug.WriteLine("DataSyncController - DownloadTable requestUrl: " + requestUrl);
HttpWebRequest httpRequest = (HttpWebRequest)WebRequest.Create(requestUrl);
httpRequest.Method = WebRequestMethods.Http.Get;
httpRequest.CookieContainer = (CookieContainer)Session["CookieContainer"];
// Get back the HTTP response for web server
using (HttpWebResponse httpResponse = (HttpWebResponse)httpRequest.GetResponse())
{
using (Stream httpResponseStream = httpResponse.GetResponseStream())
{
DataTable csvData = new DataTable();
using (var reader = new CsvReader(httpResponseStream))
{
// the CSV file has a header record, so we read that first
reader.ReadHeaderRecord();
csvData.Fill(reader);
}
int rspCnt = csvData.Rows.Count;
msg = csvData.Rows.Count.ToString();
csvData = RemoveNullString(csvData);
csvData.TableName = formsSql.GetResultsTableName(iFileId);
Debug.WriteLine("Table {0} contains {1} rows.", csvData.TableName, csvData.Rows.Count);
if (rspCnt > 0)
{
using (DbConnection connection = UasAdo.GetUasAdoConnection())
{
FilterPreExistingOnVenture(ref csvData);
MarkDuplicateResults(ref csvData);
if (csvData.TableName == "def_FormResults")
{
SetLastModifiedByUserIdToOne(ref csvData);
}
FillDatabaseTable(csvData, connection);
}
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine("* * * DataSync.DownloadTable() exception: " + ex.Message);
msg = ex.Message;
if (ex.InnerException != null && ex.InnerException.Message != null)
{
Debug.WriteLine("* * * DataSync.DownloadTable() InnerException: " + ex.InnerException.Message);
}
return(msg);
}
if (paramTableName == "def_ResponseVariables")
{
try
{ // Construct HTTP request to get the file
HttpWebRequest httpRequest = (HttpWebRequest)
WebRequest.Create(sisOnlineURL + "Export/ChangeStatusToCheckedOut");
httpRequest.Method = WebRequestMethods.Http.Get;
httpRequest.CookieContainer = (CookieContainer)Session["CookieContainer"];
// Get back the HTTP response for web server
using (WebResponse response = httpRequest.GetResponse())
{
Debug.WriteLine("DataSync HttpWebResponse Status: " + ((HttpWebResponse)response).StatusDescription);
using (Stream dataStream = ((HttpWebResponse)response).GetResponseStream())
{
}
}
}
catch (Exception ex)
{
Debug.WriteLine("* * * DataSync.DownloadTable() exception: " + ex.Message);
//msg = ex.Message;
//if (ex.InnerException != null && ex.InnerException.Message != null)
// Debug.WriteLine("* * * DataSync.DownloadTable() InnerException: " + ex.InnerException.Message);
}
}
return(msg);
}
public static List <ItemVariableIdentifierWithPartSection> GetRequiredItemVarsWithPartsSections(IFormsRepository formsRepo, def_Forms frm, int enterpriseId)
{
//if not in venture mode, get the required item vars as normal (without touching uas_EntApConfig)
if (!SessionHelper.IsVentureMode)
{
return(ComputeRequiredItemVarsWithPartsSections(formsRepo, frm, enterpriseId));
}
string configEnumCode = "Required_ItemVariables_FormId_" + frm.formId;
//attempt to query a pre-computed list of item variabel identifiers from the local Venture DB
uas_EntAppConfig eac = null;
using (DbConnection connection = UasAdo.GetUasAdoConnection())
{
connection.Open();
eac = UasAdo.GetEntAppConfig(connection, configEnumCode, enterpriseId);
}
//if no pre-computed list was found, create it now and insert it into the local Venture DB so it can be quickly retrieved next time
if (eac == null || String.IsNullOrWhiteSpace(eac.ConfigValue))
{
List <ItemVariableIdentifierWithPartSection> requiredIvs = ComputeRequiredItemVarsWithPartsSections(formsRepo, frm, enterpriseId);
string configValueString = String.Empty;
foreach (ItemVariableIdentifierWithPartSection ivps in requiredIvs)
{
configValueString += ivps.partId + "," + ivps.sectionId + "," + ivps.itemVariableIdentifier + ";";
}
using (DbConnection connection = UasAdo.GetUasAdoConnection())
{
connection.Open();
UasAdo.AddEntAppConfig(connection, new uas_EntAppConfig()
{
EnterpriseID = enterpriseId,
ApplicationID = UAS.Business.Constants.APPLICATIONID,
EnumCode = configEnumCode,
baseTypeId = 14,
ConfigName = "Required ItemVariable Identifiers for Form " + frm.identifier,
ConfigValue = configValueString,
CreatedDate = DateTime.Now,
CreatedBy = 1,
StatusFlag = "A"
});
}
return(requiredIvs);
}
//if a pre-computed list was found in the local Venture DB, parse identifiers, partIds, sectionIds from the configValue
List <ItemVariableIdentifierWithPartSection> result = new List <ItemVariableIdentifierWithPartSection>();
string[] chunks = eac.ConfigValue.Split(';');
foreach (string chunk in chunks)
{
if (String.IsNullOrWhiteSpace(chunk))
{
continue;
}
try
{
string[] subChunks = chunk.Split(',');
result.Add(new ItemVariableIdentifierWithPartSection
{
partId = Convert.ToInt32(subChunks[0]),
sectionId = Convert.ToInt32(subChunks[1]),
itemVariableIdentifier = subChunks[2]
});
}
catch (Exception e)
{
throw new Exception("Error converting string \"" + chunk + "\" into ItemVariableIdentifierWithPartSection", e);
}
}
return(result);
}
/// <summary>
/// Processes the long running action.
/// </summary>
/// <param name="id">The id.</param>
public string ProcessDataSync(string id)
{
int rspCnt = 0;
int resultTableCnt = formsSql.GetNumberOfResultsTables();
lock (syncRoot)
{
ProcessStatus[id] = 1;
}
try
{
// Delete all result table first
for (int DeleteOrderId = resultTableCnt - 1; DeleteOrderId >= 0; DeleteOrderId--)
{
rspCnt = formsSql.DeleteTableContent(formsSql.GetResultsTableName(DeleteOrderId));
// ProcessStatus[id] = 2, 3, 4
lock (syncRoot)
{
ProcessStatus[id] += 1;
}
}
for (int fileId = 0; fileId < resultTableCnt; fileId++)
{
// 2. Execute the URL to run the method on the remote server:
// Export/GetResponsesCsv?fileId=1
// Construct HTTP request to get the file
HttpWebRequest httpRequest = (HttpWebRequest)
WebRequest.Create(sisOnlineURL + "Export/GetResponseCsv?fileId=" + fileId.ToString());
httpRequest.Method = WebRequestMethods.Http.Get;
DataTable csvData = new DataTable();
// Get back the HTTP response for web server
using (HttpWebResponse httpResponse = (HttpWebResponse)httpRequest.GetResponse())
{
Stream httpResponseStream = httpResponse.GetResponseStream();
// ProcessStatus[id] = 5, 7, 9
lock (syncRoot)
{
ProcessStatus[id] += 1;
}
using (var reader = new CsvReader(httpResponseStream))
{
// the CSV file has a header record, so we read that first
reader.ReadHeaderRecord();
csvData.Fill(reader);
Debug.WriteLine("Table contains {0} rows.", csvData.Rows.Count);
}
rspCnt = csvData.Rows.Count;
csvData = RemoveNullString(csvData);
}
// 3. Read the FileStreamResult
// - Read through the records
/* using (SqlBulkCopy bulkCopy = new SqlBulkCopy(formsSql.GetConnectionString(), SqlBulkCopyOptions.KeepNulls | SqlBulkCopyOptions.KeepIdentity))
* {
* bulkCopy.DestinationTableName = formsSql.GetResultsTableName(fileId);
* bulkCopy.ColumnMappings.Clear();
* foreach (var column in csvData.Columns)
* bulkCopy.ColumnMappings.Add(column.ToString(), column.ToString());
* bulkCopy.WriteToServer(csvData);
* }
*/
csvData.TableName = formsSql.GetResultsTableName(fileId);
DbConnection connection = UasAdo.GetUasAdoConnection();
FillDatabaseTable(csvData, connection);
// ProcessStatus[id] = 6, 8, 10
lock (syncRoot)
{
ProcessStatus[id] += 1;
}
}
}
catch (Exception ex)
{
Debug.WriteLine("* * * DataSync exception: " + ex.Message);
}
return(id);
}