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