示例#1
0
        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);
            }
        }
示例#2
0
        //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);
            }
        }