/// <summary>
/// Checks if there are any FormResults with Completed status.
/// </summary>
/// <returns>true / false</returns>
public bool GetNoFormResultsToBeUploaded()
{
int rowCount = 0;
try
{
using (DbConnection connection = UasAdo.GetUasAdoConnection())
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT COUNT(*) FROM [dbo]." + "def_FormResults";
command.CommandText += GenerateQuery("def_FormResults");
command.CommandText += " AND formStatus = " + (int)FormResults_formStatus.COMPLETED;
command.CommandType = CommandType.Text;
rowCount = (Int32)command.ExecuteScalar();
}
connection.Close();
}
Debug.WriteLine("* * * GetNoFormResultsToBeUploaded rowCount: " + rowCount.ToString());
}
catch (Exception ex)
{
Debug.WriteLine("* * * GetNoFormResultsToBeUploaded error: " + ex.Message);
}
return(rowCount == 0);
}
/// <summary>
///
/// </summary>
/// <param name="columnName"></param>
/// <param name="tableName"></param>
/// <returns></returns>
private int getMin(string columnName, string tableName)
{
int min = 0;
try
{
using (DbConnection connection = UasAdo.GetUasAdoConnection())
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT MIN(" + columnName + ") FROM [dbo]." + tableName;
command.CommandType = CommandType.Text;
min = (Int32)command.ExecuteScalar();
}
connection.Close();
}
}
catch (Exception ex)
{
Debug.WriteLine("FormsSql getMax exception: " + ex.Message);
}
if (min > 0)
{
min = 0;
}
return(min);
}
// Deprecated, use MarkSingleUploaded
public void MarkUploaded()
{
try
{
// SELECT COUNT(*)
FormResults_formStatus upldStatus = FormResults_formStatus.UPLOADED;
FormResults_formStatus cmpltdStatus = FormResults_formStatus.COMPLETED;
int EnterpriseId = SessionHelper.LoginStatus.EnterpriseID;
using (DbConnection connection = UasAdo.GetUasAdoConnection())
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "UPDATE [dbo].def_FormResults SET formStatus = " + (int)upldStatus + " FROM " + "[dbo].def_FormResults WHERE formStatus = " + (int)cmpltdStatus;
command.ExecuteReader();
}
connection.Close();
}
}
catch (Exception ex)
{
Debug.WriteLine("* * * GetCompletedFormResults error: " + ex.Message);
}
}
public System.Collections.Generic.List <int> GetCompletedFormResultIds()
{
List <int> results = null;
{
FormResults_formStatus status = FormResults_formStatus.COMPLETED;
using (DbConnection connection = UasAdo.GetUasAdoConnection())
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT formResultId FROM [dbo]." + "def_FormResults";
command.CommandText += GenerateQuery("def_FormResults");
command.CommandText += " AND formStatus = " + (int)status;
command.CommandType = CommandType.Text;
DataTable dt = new DataTable();
dt.Load(command.ExecuteReader());
results = dt.Rows.OfType <DataRow>().Select(dr => dr.Field <int>("formResultId")).ToList();
}
connection.Close();
}
}
return(results);
}
public int DeleteTableContent(string formTable)
{
int rowsDeleted = 0;
try
{
using (DbConnection connection = UasAdo.GetUasAdoConnection())
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "DELETE [dbo]." + formTable + " FROM [dbo]." + formTable;
command.CommandText += GenerateQuery(formTable);
command.CommandType = CommandType.Text;
rowsDeleted = command.ExecuteNonQuery();
}
connection.Close();
}
Debug.WriteLine("* * * GetTableRecordCount " + formTable + " rowsDeleted: " + rowsDeleted.ToString());
}
catch (Exception ex)
{
Debug.WriteLine("* * * DeleteResultsTableContent error. " + formTable + ": " + ex.Message);
}
return(rowsDeleted);
}
public int GetTableRecordCountVenture(string formTable)
{
int rowCount = 0;
try
{
// SELECT COUNT(*)
using (DbConnection connection = UasAdo.GetUasAdoConnection())
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT COUNT(*) FROM (SELECT DISTINCT [dbo]." + formTable + ".* FROM [dbo]." + formTable;
command.CommandText += GenerateQuery(formTable);
command.CommandText += ")";
command.CommandType = CommandType.Text;
rowCount = (Int32)command.ExecuteScalar();
}
connection.Close();
}
Debug.WriteLine("* * * GetTableRecordCount " + formTable + " rowCount: " + rowCount.ToString());
}
catch (Exception ex)
{
Debug.WriteLine("* * * GetTableRecordCount error " + formTable + ": " + ex.Message);
}
return(rowCount);
}
public List <int> GetNonNewFormResultIds()
{
int[] formResultIds;
try
{
FormResults_formStatus status = FormResults_formStatus.NEW;
using (DbConnection connection = UasAdo.GetUasAdoConnection())
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM def_FormResults" + GenerateQuery("def_FormResults");
command.CommandText += " AND formStatus <> " + (int)status;
command.CommandType = CommandType.Text;
DbDataAdapter adapter = UasAdo.CreateDataAdapter(connection);
adapter.SelectCommand = command;
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
formResultIds = new int[dataSet.Tables[0].Rows.Count];
for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
{
formResultIds[i] = Int32.Parse(dataSet.Tables[0].Rows[i]["formResultId"].ToString());
}
}
connection.Close();
return(formResultIds.ToList());
}
}
catch (Exception ex)
{
Debug.WriteLine("* * * GetCompletedFormResults error: " + ex.Message);
return(null);
}
}
public void MarkSingleUploaded(int formResultId)
{
try
{
FormResults_formStatus upldStatus = FormResults_formStatus.UPLOADED;
using (DbConnection connection = UasAdo.GetUasAdoConnection())
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "UPDATE [dbo].def_FormResults SET formStatus = " + (int)upldStatus + ", archived = 1, statusChangeDate = '" + DateTime.Now + "' FROM " + "[dbo].def_FormResults WHERE formResultId = " + formResultId;
command.ExecuteReader();
}
connection.Close();
}
}
catch (Exception ex)
{
Debug.WriteLine("* * * GetCompletedFormResults error: " + ex.Message);
}
}
/*
* Method to get the number of rows in a table.
* mainly used to see if a table is empty and needs to be downloaded from the master/remote server
*/
public static int GetTableRowCount(string tableName)
{
int rowCount = 0;
try
{
using (DbConnection connection = UasAdo.GetUasAdoConnection())
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT COUNT(*) FROM " + tableName;
command.CommandType = CommandType.Text;
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (!reader.IsDBNull(0))
{
rowCount = reader.GetInt32(0);
Debug.WriteLine("* * * GetTableRowCount: " + tableName + " - " + rowCount.ToString());
}
}
}
}
connection.Close();
}
}
catch (Exception ex)
{
Debug.WriteLine("* * * GetTableRowCount: " + ex.Message);
}
return(rowCount);
}
public void UpdateFormResultID(int formResultId, int newFormResultId)
{
// Turn off identity insert on form result
SetIdentityInsert("def_FormResults", "formResultId", true);
// Copy form result to new id
try
{
using (DbConnection connection = UasAdo.GetUasAdoConnection())
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO [dbo].def_FormResults (formResultId, formId, formStatus, sessionStatus, dateUpdated, deleted, locked, archived, EnterpriseID, GroupID, [subject], interviewer, assigned, training, reviewStatus, statusChangeDate) select " + newFormResultId + ", formId, formStatus, sessionStatus, dateUpdated, deleted, locked, archived, EnterpriseID, GroupID, [subject], interviewer, assigned, training, reviewStatus, statusChangeDate from def_FormResults where formResultId = " + formResultId;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
connection.Close();
}
}
catch (Exception ex)
{
Debug.WriteLine("FormsSql update form result Id exception: " + ex.Message);
SetIdentityInsert("def_FormResults", "formResultId", false);
return;
}
// change references in item results to form result
try
{
using (DbConnection connection = UasAdo.GetUasAdoConnection())
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "UPDATE def_ItemResults SET formResultId = " + newFormResultId + " WHERE formResultId = " + formResultId;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
connection.Close();
}
}
catch (Exception ex)
{
Debug.WriteLine("FormsSql update form result Id exception: " + ex.Message);
SetIdentityInsert("def_FormResults", "formResultId", false);
return;
}
// delete form result with old id
try
{
using (DbConnection connection = UasAdo.GetUasAdoConnection())
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "DELETE FROM def_FormResults WHERE formResultId = " + formResultId;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
connection.Close();
}
}
catch (Exception ex)
{
Debug.WriteLine("FormsSql update form result Id exception: " + ex.Message);
SetIdentityInsert("def_FormResults", "formResultId", false);
return;
}
// turn on identity insert for form result
SetIdentityInsert("def_FormResults", "formResultId", false);
}
//public string CreateFormResultJSON(int formResultId, int newFormResultId = -1)
//{
// string json = String.Empty;
// db.Configuration.LazyLoadingEnabled = false;
// // Not a new form result; don't change the id
// if (newFormResultId == -1)
// {
// newFormResultId = formResultId;
// }
// try
// {
// using (DbConnection connection = UasAdo.GetUasAdoConnection())
// {
// connection.Open();
// using (DbCommand command = connection.CreateCommand())
// {
// command.CommandText = "SELECT RV.sectionId, RV.rspValue, RV.identifier, " + newFormResultId + " AS formResultId, FR.formStatus, FR.dateUpdated FROM RspVarsWithSection RV JOIN def_FormResults FR ON FR.formResultId = RV.formResultId WHERE RV.formResultId = " + formResultId + " ORDER BY sectionId";
// command.CommandType = CommandType.Text;
// DataTable dt = new DataTable();
// dt.Load(command.ExecuteReader());
// json = fastJSON.JSON.ToJSON(dt);
// }
// }
// }
// catch (Exception ex)
// {
// Debug.WriteLine("* * * CreateFormResultJSON exception: " + ex.Message);
// return null;
// }
// return json;
//}
public string[] CreateFormResultsJSON()
{
int[] formResultIds;
try
{
FormResults_formStatus status = FormResults_formStatus.COMPLETED;
int EnterpriseId = SessionHelper.LoginStatus.EnterpriseID;
using (DbConnection connection = UasAdo.GetUasAdoConnection())
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT formResultId FROM [dbo]." + "def_FormResults WHERE formStatus = " + (int)status + " AND";
command.CommandText += GenerateQuery("def_FormResults");
command.CommandType = CommandType.Text;
DataSet dataSet = new DataSet();
DbDataAdapter adapter = UasAdo.CreateDataAdapter(connection);
adapter.SelectCommand = command;
adapter.Fill(dataSet);
formResultIds = new int[dataSet.Tables[0].Rows.Count];
for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
{
formResultIds[i] = Int32.Parse(dataSet.Tables[0].Rows[i]["formResultId"].ToString());
}
}
string[] jsonStrings = new string[formResultIds.Count()];
string json = String.Empty;
string formStatus = String.Empty;
string dateUpdated = String.Empty;
int j = 0;
foreach (int formResultId in formResultIds)
{
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT formStatus, dateUpdated FROM " + "def_FormResults WHERE formResultId = " + formResultId;
command.CommandType = CommandType.Text;
{
using (DbDataReader reader = command.ExecuteReader())
{
if (reader != null)
{
if (reader.Read())
{
formStatus = reader["formStatus"].ToString();
dateUpdated = reader["dateUpdated"].ToString();
}
}
}
}
}
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM " + "RspVarsWithSection WHERE formResultId = " + formResultId + " ORDER BY sectionId";
command.CommandType = CommandType.Text;
using (DbDataReader reader = command.ExecuteReader())
{
if (reader != null)
{
if (reader.Read())
{
int section = -1;
bool sectionChange = true;;
while (true)
{
if (String.IsNullOrEmpty(json))
{
json += "{ \"formResults\": { \"id\": \"" + reader["formResultId"]
+ "\",\"formStatus\":\"" + formStatus + "\",\"dateUpdated\":\"" + dateUpdated + "\",\"sections\": [";
}
if (sectionChange == true)
{
json += "{ \"id\": \"" + reader["sectionId"] + "\", \"responses\": [{";
section = Int32.Parse(reader["sectionId"].ToString());
}
json += "\"" + reader["identifier"] + "\": \"" + reader["rspValue"] + "\"";
if (!reader.Read())
{
break;
}
if (section != Int32.Parse(reader["sectionId"].ToString()))
{
json += "}] },";
sectionChange = true;
}
else
{
json += ", ";
sectionChange = false;
}
}
json += "}] }] } }";
}
}
}
jsonStrings[j++] = json;
json = String.Empty;
Debug.WriteLine("*** FormsSql - CreateFormResultsJSON: Creating JSON for form result id = " + formResultId);
}
}
return(jsonStrings);
}
}
catch (Exception ex)
{
Debug.WriteLine("* * * CreateFormResultsJSON error: " + ex.Message);
return(null);
}
}
public string CreateFormResultJSON(int formResultId, int newFormResultId = -1)
{
FormResultJsonContainer container = new FormResultJsonContainer();
db.Configuration.LazyLoadingEnabled = false;
// Not a new form result; don't change the id
if (newFormResultId == -1)
{
newFormResultId = formResultId;
}
try
{
DataTable dt1 = null;
DataTable dt2 = null;
using (DbConnection connection = UasAdo.GetUasAdoConnection())
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT " + newFormResultId + " as newFormResultId, fr.* from def_FormResults fr where formResultId = " + formResultId;
command.CommandType = CommandType.Text;
dt1 = new DataTable();
dt1.Load(command.ExecuteReader());
container.FormResult = new Dictionary <string, object>();
foreach (DataColumn dc in dt1.Columns)
{
container.FormResult.Add(dc.ColumnName, dt1.Rows[0][dc]);
}
}
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "select rv.responseVariableId, iv.itemId, rv.itemVariableId, rv.rspValue from [dbo].def_ResponseVariables rv join def_ItemVariables iv on rv.itemVariableId = iv.itemVariableId join def_ItemResults ir on rv.itemResultId = ir.itemResultId join def_FormResults fr on ir.formResultId = fr.formResultId where fr.formResultId = " + formResultId + " order by iv.itemId";
command.CommandType = CommandType.Text;
dt2 = new DataTable();
dt2.Load(command.ExecuteReader());
container.Data = new List <Dictionary <string, object> >();
foreach (DataRow dr in dt2.Rows)
{
Dictionary <string, object> dataDict = new Dictionary <string, object>();
foreach (DataColumn dc in dt2.Columns)
{
dataDict.Add(dc.ColumnName, dr.Field <object>(dc.ColumnName));
}
container.Data.Add(dataDict);
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine("* * * CreateFormResultJSON exception: " + ex.Message);
return(null);
}
string json = fastJSON.JSON.ToJSON(container);
return(json);
}