Ejemplo n.º 1
0
        /// <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);
        }
Ejemplo n.º 2
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);
        }
Ejemplo n.º 3
0
        // 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);
            }
        }
Ejemplo n.º 4
0
        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);
        }
Ejemplo n.º 5
0
        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);
        }
Ejemplo n.º 6
0
        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);
        }
Ejemplo n.º 7
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);
            }
        }
Ejemplo n.º 8
0
        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);
            }
        }
Ejemplo n.º 9
0
        /*
         * 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);
        }
Ejemplo n.º 10
0
        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);
        }
Ejemplo n.º 11
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);
            }
        }
Ejemplo n.º 12
0
        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);
        }