Esempio n. 1
0
        public static ClientClass LoadClientData(int client_id)
        {
            try
            {
                ClientClass client = new ClientClass();


                string query = $"select id," +
                               " FIO," +
                               " birthDate," +
                               " if(sex = 1, 'Мужской', if(sex = 2, 'Женский', 'Не указан')) sex," +
                               " Address" +
                               $" from client where deleted = 0 and id = {client_id}";

                using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnection())
                {
                    sqlConnection.Open();

                    using (MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection))
                    {
                        using (MySqlDataReader sqlReader = sqlCommand.ExecuteReader())
                        {
                            if (sqlReader.HasRows)
                            {
                                while (sqlReader.Read())
                                {
                                    client.id        = sqlReader.GetInt32(0);
                                    client.FIO       = sqlReader.GetString(1);
                                    client.birthDate = sqlReader.GetDateTime(2);
                                    client.sex       = sqlReader.GetString(3);
                                    client.Address   = sqlReader.GetString(4);
                                }
                            }
                        }
                    }
                }

                return(client);
            }
            catch (Exception ex)
            {
                logger.Error(ex, $"\r\n#---------#\r\n{ex.StackTrace}\r\n##---------##\r\n{ex.Message}\r\n###---------###\r\n{ex.Source}");
                MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(null);
            }
        }
Esempio n. 2
0
        public static DataTable LoadDataToCHLDReport(string areaCondition, string medOrgCondition, string eventDateCondition, string DOOCondition, string ageCondition, string TreatmentDateCondition, string HealthStatusCondition)
        {
            try
            {
                DataTable events = new DataTable();

                string query = "SELECT CASE WHEN relax_id IN (2,3) THEN 'Организованный отдых' ELSE 'Неорганизованный отдых' END relax," +
                               " COUNT(id) cnt" +
                               " FROM event e" +
                               " WHERE e.deleted = 0" +
                               " AND e.helpname = 3" +
                               areaCondition +
                               medOrgCondition +
                               eventDateCondition +
                               DOOCondition +
                               ageCondition +
                               TreatmentDateCondition +
                               HealthStatusCondition +
                               " GROUP BY relax_id";

                using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnection())
                {
                    sqlConnection.Open();

                    using (MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection))
                    {
                        events.Load(sqlCommand.ExecuteReader());
                    }
                }

                return(events);
            }
            catch (Exception ex)
            {
                logger.Error(ex, $"\r\n#---------#\r\n{ex.StackTrace}\r\n##---------##\r\n{ex.Message}\r\n###---------###\r\n{ex.Source}");
                MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(null);
            }
        }
Esempio n. 3
0
        public static void UpdateEvent(int event_id, int area_id, int medOrg_id, DateTime eventDate, int doo_id, int client_id, int relax_id, DateTime TreatmentDate, int HelpName, string DiagName, string DiagID, string Speciality, string Department, string TransfertedDepart, DateTime TransfertedDate, string HealthStatus, int classCase_id)
        {
            try
            {
                string query = $"update event set area_id = {area_id}, medOrg_id = {medOrg_id}, eventDate = '{eventDate:yyyy-MM-dd}', doo_id = {doo_id}, client_id = {client_id}, relax_id = {relax_id}, TreatmentDate = '{TreatmentDate:yyyy-MM-dd}', HelpName = '{HelpName}', DiagName = '{DiagName}', DiagID = '{DiagID}', Speciality = '{Speciality}', Department = '{Department}', TransfertedDepart = '{TransfertedDepart}', TransfertedDate = '{TransfertedDate:yyyy-MM-dd}', HealthStatus = '{HealthStatus}', classCase_id = {classCase_id} where id = {event_id} and deleted = 0";

                using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnection())
                {
                    sqlConnection.Open();

                    using (MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection))
                    {
                        sqlCommand.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex, $"\r\n#---------#\r\n{ex.StackTrace}\r\n##---------##\r\n{ex.Message}\r\n###---------###\r\n{ex.Source}");
                MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
Esempio n. 4
0
        //Запрос для пометки записи как удаленной
        public static void RemoveArea(int area_id)
        {
            try
            {
                string query = $"update area set deleted = 1 where id = {area_id}";

                using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnection())
                {
                    sqlConnection.Open();

                    using (MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection))
                    {
                        sqlCommand.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex, $"\r\n#---------#\r\n{ex.StackTrace}\r\n##---------##\r\n{ex.Message}\r\n###---------###\r\n{ex.Source}");
                MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
Esempio n. 5
0
        //Запрос для добавления записи в таблицу с районами
        public static void InsertArea(string NewArea)
        {
            try
            {
                string query = $"insert into area (name) value ('{NewArea}')";

                using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnection())
                {
                    sqlConnection.Open();

                    using (MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection))
                    {
                        sqlCommand.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex, $"\r\n#---------#\r\n{ex.StackTrace}\r\n##---------##\r\n{ex.Message}\r\n###---------###\r\n{ex.Source}");
                MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        public static HealthOrgClass LoadHealthOrgList(int id, int area_id)
        {
            try
            {
                HealthOrgClass healthClass = new HealthOrgClass();
                string         query       = $"select id, FullName, ShortName, Address, area_id from childrenshealthorganization where deleted = 0 and area_id = {area_id} and id = {id}";

                using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnection())
                {
                    sqlConnection.Open();

                    using (MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection))
                    {
                        using (MySqlDataReader reader = sqlCommand.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    healthClass.id        = int.Parse(reader["id"].ToString());
                                    healthClass.FullName  = reader["FullName"].ToString();
                                    healthClass.ShortName = reader["ShortName"].ToString();
                                    healthClass.Address   = reader["Address"].ToString();
                                    healthClass.area_id   = int.Parse(reader["area_id"].ToString());
                                }
                            }
                        }
                    }
                }

                return(healthClass);
            }
            catch (Exception ex)
            {
                logger.Error(ex, $"\r\n#---------#\r\n{ex.StackTrace}\r\n##---------##\r\n{ex.Message}\r\n###---------###\r\n{ex.Source}");
                MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(null);
            }
        }
        //Запрос на добавление новой ДОО в таблицу
        public static void InsertHealthOrg(string FullName, string ShortName, string Address, int area_id)
        {
            try
            {
                string query = $"insert into childrenshealthorganization (FullName, ShortName, Address, area_id) value ('{FullName}', '{ShortName}', '{Address}', {area_id})";

                using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnection())
                {
                    sqlConnection.Open();

                    using (MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection))
                    {
                        sqlCommand.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex, $"\r\n#---------#\r\n{ex.StackTrace}\r\n##---------##\r\n{ex.Message}\r\n###---------###\r\n{ex.Source}");
                MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
Esempio n. 8
0
        public static void EditClientData(string FIO, DateTime birthDate, string Address, int sex, int id)
        {
            try
            {
                string query = $"UPDATE client SET FIO = '{FIO}', birthDate = '{birthDate.ToString("yyyy-MM-dd")}', Address = '{Address}', sex = {sex} where id = {id}";

                using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnection())
                {
                    sqlConnection.Open();

                    using (MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection))
                    {
                        sqlCommand.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex, $"\r\n#---------#\r\n{ex.StackTrace}\r\n##---------##\r\n{ex.Message}\r\n###---------###\r\n{ex.Source}");
                MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
Esempio n. 9
0
        public static void InsertClientData(string FIO, DateTime birthDate, string Address, int sex)
        {
            try
            {
                string query = $"insert into client (FIO, birthDate, Address, sex) value ('{FIO}', '{birthDate.ToString("yyyy-MM-dd")}', '{Address}', {sex})";

                using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnection())
                {
                    sqlConnection.Open();

                    using (MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection))
                    {
                        sqlCommand.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex, $"\r\n#---------#\r\n{ex.StackTrace}\r\n##---------##\r\n{ex.Message}\r\n###---------###\r\n{ex.Source}");
                MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
Esempio n. 10
0
        public static DateTime[] SetMinMaxDate()
        {
            try
            {
                DateTime[] dateTimes = new DateTime[2];
                string     query     = "SELECT MIN(TreatmentDate) minDate, MAX(TreatmentDate) maxDate FROM event e";

                using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnection())
                {
                    sqlConnection.Open();

                    using (MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection))
                    {
                        using (MySqlDataReader reader = sqlCommand.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    for (int i = 0; i < 2; i++)
                                    {
                                        dateTimes[i] = reader.GetDateTime(i);
                                    }
                                }
                            }
                        }
                    }
                }

                return(dateTimes);
            }
            catch (Exception ex)
            {
                logger.Error(ex, $"\r\n#---------#\r\n{ex.StackTrace}\r\n##---------##\r\n{ex.Message}\r\n###---------###\r\n{ex.Source}");
                MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(null);
            }
        }
Esempio n. 11
0
        public static void InsertEvent(int area_id, int medOrg_id, DateTime eventDate, int doo_id, int client_id, int relax_id, DateTime TreatmentDate, int HelpName, string DiagName, string DiagID, string Speciality, string Department, string TransfertedDepart, DateTime TransfertedDate, string HealthStatus, int classCase_id)
        {
            try
            {
                string query = "insert into event (area_id, medOrg_id, eventDate, doo_id, client_id, relax_id, TreatmentDate, HelpName, DiagName, DiagID, Speciality, Department, TransfertedDepart, TransfertedDate, HealthStatus, classCase_id)" +
                               $" value ({area_id}, {medOrg_id}, '{eventDate:yyyy-MM-dd}', {doo_id}, {client_id}, {relax_id}, '{TreatmentDate:yyyy-MM-dd}', '{HelpName}', '{DiagName}', '{DiagID}', '{Speciality}', '{Department}', '{TransfertedDepart}', '{(TransfertedDate.ToString("yyyy-MM-dd") == DateTime.MinValue.ToString("yyyy-MM-dd") ? "0000-00-00" : TransfertedDate.ToString("yyyy-MM-dd"))}', '{HealthStatus}', {classCase_id})";

                using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnection())
                {
                    sqlConnection.Open();

                    using (MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection))
                    {
                        sqlCommand.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex, $"\r\n#---------#\r\n{ex.StackTrace}\r\n##---------##\r\n{ex.Message}\r\n###---------###\r\n{ex.Source}");
                MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
Esempio n. 12
0
        public static List <MKBClass> LoadMKB_L()
        {
            try
            {
                List <MKBClass> mkb   = new List <MKBClass>();
                string          query = $"select DiagID, DiagName from mkb";

                using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnection())
                {
                    sqlConnection.Open();

                    using (MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection))
                    {
                        using (MySqlDataReader reader = sqlCommand.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    mkb.Add(new MKBClass {
                                        DiagID = reader["DiagID"].ToString(), DiagName = reader["DiagName"].ToString()
                                    });
                                }
                            }
                        }
                    }
                }

                return(mkb);
            }
            catch (Exception ex)
            {
                logger.Error(ex, $"\r\n#---------#\r\n{ex.StackTrace}\r\n##---------##\r\n{ex.Message}\r\n###---------###\r\n{ex.Source}");
                MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(null);
            }
        }
Esempio n. 13
0
        public static DataTable LoadEvent()
        {
            try
            {
                DataTable events = new DataTable();

                string query = "select e.id," +
                               " a.name as AreaName," +
                               " mo.name as medOrgName," +
                               " e.eventDate," +
                               " doo.FullName as DOOName," +
                               " doo.Address as DOOAddress," +
                               " c.FIO as clientFIO," +
                               " c.birthDate as clientBirthDate," +
                               " CONCAT((YEAR(current_date())-YEAR(c.birthDate)) - if(DAYOFYEAR(current_date()) > DAYOFYEAR(c.birthDate),0,1),' лет '," +
                               " (IF(MONTH(CURDATE()) - MONTH(c.birthDate) < 0, MONTH(CURDATE()) - MONTH(c.birthDate) + 12, MONTH(CURDATE()) - MONTH(c.birthDate))),' мес.') AS clientAge," +
                               " age(c.birthDate, current_date()) as age," +
                               " c.Address as clientAddress," +
                               " CONCAT(r1.Name, ' (', r.Name, ')') as relaxName," +
                               " e.TreatmentDate," +
                               " e.HelpName," +
                               " hc.FullName," +
                               " e.DiagName," +
                               " e.DiagID," +
                               " e.Speciality," +
                               " e.Department," +
                               " e.TransfertedDepart," +
                               " if (e.TransfertedDate = '0000-00-00', \"\", e.TransfertedDate) as TransfertedDate," +
                               " cc.name as ClassCaseName," +
                               " e.HealthStatus" +
                               " from event as e" +
                               " join area as a on e.area_id = a.id" +
                               " join medorganisation as mo on e.medOrg_id = mo.id" +
                               " join childrenshealthorganization as doo on e.doo_id = doo.id" +
                               " join client as c on e.client_id = c.id" +
                               " join relaxinfo as r on e.relax_id = r.id" +
                               " join relaxinfo as r1 on r.parent_id = r1.id" +
                               " join HelpCare as hc on e.HelpName = hc.id" +
                               " left join classCase as cc on cc.id = e.classCase_id" +
                               " where e.deleted = 0" +
                               "   and doo.deleted = 0" +
                               "   and c.deleted = 0" +
                               "   and a.deleted = 0" +
                               "   and mo.deleted = 0" +
                               " order by e.id asc";

                using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnection())
                {
                    sqlConnection.Open();

                    using (MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection))
                    {
                        events.Load(sqlCommand.ExecuteReader());
                    }
                }

                return(events);
            }
            catch (Exception ex)
            {
                logger.Error(ex, $"\r\n#---------#\r\n{ex.StackTrace}\r\n##---------##\r\n{ex.Message}\r\n###---------###\r\n{ex.Source}");
                MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(null);
            }
        }
Esempio n. 14
0
        public static DataTable LoadDataToTableReport(string areaCondition, string medOrgCondition, string eventDateCondition, string DOOCondition, string ageCondition, string TreatmentDateCondition, string relaxCondition, string medHelpCondition, string HealthStatusCondition)
        {
            try
            {
                DataTable events = new DataTable();

                string query = "SELECT a.name," +
                               " travm," +
                               " utopl," +
                               " infekc," +
                               " hirurg," +
                               " somat," +
                               " neiro," +
                               " nevro," +
                               " lor," +
                               " otrav," +
                               " alko," +
                               " narko," +
                               " other" +
                               " FROM(" +
                               " SELECT area_id," +
                               " SUM(CASE WHEN classCase_id = 1 then 1 ELSE 0 END) travm," +
                               " SUM(CASE WHEN classCase_id = 2 then 1 ELSE 0 END) utopl," +
                               " SUM(CASE WHEN classCase_id = 3 then 1 ELSE 0 END) infekc," +
                               " SUM(CASE WHEN classCase_id = 4 then 1 ELSE 0 END) hirurg," +
                               " SUM(CASE WHEN classCase_id = 5 then 1 ELSE 0 END) somat," +
                               " SUM(CASE WHEN classCase_id = 6 then 1 ELSE 0 END) neiro," +
                               " SUM(CASE WHEN classCase_id = 7 then 1 ELSE 0 END) nevro," +
                               " SUM(CASE WHEN classCase_id = 8 then 1 ELSE 0 END) lor," +
                               " SUM(CASE WHEN classCase_id = 9 then 1 ELSE 0 END) otrav," +
                               " SUM(CASE WHEN classCase_id = 10 then 1 ELSE 0 END) alko," +
                               " SUM(CASE WHEN classCase_id = 11 then 1 ELSE 0 END) narko," +
                               " SUM(CASE WHEN classCase_id = 12 then 1 ELSE 0 END) other" +
                               " FROM event e" +
                               " WHERE e.deleted = 0" +
                               " AND classCase_id IS NOT null" +
                               areaCondition +
                               medOrgCondition +
                               eventDateCondition +
                               DOOCondition +
                               ageCondition +
                               TreatmentDateCondition +
                               relaxCondition +
                               medHelpCondition +
                               HealthStatusCondition +
                               " GROUP BY area_id) z1" +
                               " JOIN area a ON z1.area_id = a.id";

                using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnection())
                {
                    sqlConnection.Open();

                    using (MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection))
                    {
                        events.Load(sqlCommand.ExecuteReader());
                    }
                }

                return(events);
            }
            catch (Exception ex)
            {
                logger.Error(ex, $"\r\n#---------#\r\n{ex.StackTrace}\r\n##---------##\r\n{ex.Message}\r\n###---------###\r\n{ex.Source}");
                MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(null);
            }
        }
Esempio n. 15
0
        public static DataTable LoadDataToReport(string areaCondition, string medOrgCondition, string eventDateCondition, string DOOCondition, string ageCondition, string TreatmentDateCondition, string relaxCondition, string medHelpCondition, string HealthStatusCondition)
        {
            try
            {
                DataTable events = new DataTable();

                string query = "SELECT a.name," +
                               " IF(_all.AllEvent IS NULL, 0, _all.AllEvent) AllEvent," +
                               " IF(orgRelax.samost IS NULL, 0, orgRelax.samost) orgSamost," +
                               " IF(orgRelax.MotherAndChild IS NULL, 0, orgRelax.MotherAndChild) MotherAndChild," +
                               " IF(norgRelax.samost IS NULL, 0, norgRelax.samost) noorgSamost," +
                               " IF(norgRelax._WithParent IS NULL, 0, norgRelax._WithParent) WithParent," +
                               " IF(medHelp.PMSP IS NULL, 0, medHelp.PMSP) PMSP," +
                               " IF(medHelp.PSMSP IS NULL, 0, medHelp.PSMSP) PSMSP," +
                               " IF(medHelp.SMP IS NULL, 0, medHelp.SMP) SMP," +
                               " IF(reanim.ambulance IS NULL, 0, reanim.ambulance) ambulance" +
                               " FROM(" +
                               " SELECT e.area_id," +
                               " COUNT(e.id) AllEvent" +
                               " FROM event e" +
                               " join client c on e.client_id = c.id" +
                               " WHERE e.deleted = 0" +
                               areaCondition +
                               medOrgCondition +
                               eventDateCondition +
                               DOOCondition +
                               ageCondition +
                               TreatmentDateCondition +
                               relaxCondition +
                               medHelpCondition +
                               HealthStatusCondition +
                               " GROUP BY 1) _all" +
                               " LEFT JOIN (select z0.area_id," +
                               " sum(z0.samost) samost," +
                               " sum(z0.MotherAndChild) MotherAndChild" +
                               " from(" +
                               " SELECT e.area_id," +
                               " CASE WHEN ri.name = 'Самостоятельно' THEN COUNT(e.id) ELSE 0 end samost," +
                               " CASE WHEN ri.name = 'По путевке Мать и дитя' THEN COUNT(e.id) ELSE 0 end MotherAndChild" +
                               " FROM event e" +
                               " JOIN relaxinfo ri ON e.relax_id = ri.id" +
                               " JOIN relaxinfo ri1 ON ri.parent_id = ri1.id" +
                               " join client c on e.client_id = c.id" +
                               " WHERE e.deleted = 0" +
                               " AND ri1.id = 1" +
                               areaCondition +
                               medOrgCondition +
                               eventDateCondition +
                               DOOCondition +
                               ageCondition +
                               TreatmentDateCondition +
                               relaxCondition +
                               medHelpCondition +
                               HealthStatusCondition +
                               " GROUP BY e.area_id, ri.name) z0" +
                               " group by 1) orgRelax ON orgRelax.area_id = _all.area_id" +
                               " LEFT JOIN(select z2.area_id," +
                               " sum(z2.samost) samost," +
                               " sum(z2._WithParent) _WithParent" +
                               " from (" +
                               " SELECT e.area_id," +
                               " CASE WHEN ri.name = 'Самостоятельно' THEN COUNT(e.id) ELSE 0 end samost," +
                               " CASE WHEN ri.name = 'С законным представителем' THEN COUNT(e.id) ELSE 0 end _WithParent" +
                               " FROM event e" +
                               " JOIN relaxinfo ri ON e.relax_id = ri.id" +
                               " JOIN relaxinfo ri1 ON ri.parent_id = ri1.id" +
                               " join client c on e.client_id = c.id" +
                               " WHERE e.deleted = 0" +
                               " AND ri1.id = 4" +
                               areaCondition +
                               medOrgCondition +
                               eventDateCondition +
                               DOOCondition +
                               ageCondition +
                               TreatmentDateCondition +
                               relaxCondition +
                               medHelpCondition +
                               HealthStatusCondition +
                               " GROUP BY e.area_id, ri.name) z2" +
                               " group by 1) norgRelax ON norgRelax.area_id = _all.area_id" +
                               " LEFT JOIN(SELECT z1.area_id," +
                               " SUM(z1.SMP) SMP," +
                               " SUM(z1.PMSP) PMSP," +
                               " SUM(z1.PSMSP) PSMSP" +
                               " FROM(" +
                               " SELECT e.area_id," +
                               " CASE WHEN HelpName = 3 THEN COUNT(e.id) ELSE 0 END SMP," +
                               " CASE WHEN HelpName = 1 THEN COUNT(e.id) ELSE 0 END PMSP," +
                               " CASE WHEN HelpName = 2 THEN COUNT(e.id) ELSE 0 END PSMSP" +
                               " FROM event e" +
                               " join client c on e.client_id = c.id" +
                               " WHERE e.deleted = 0" +
                               areaCondition +
                               medOrgCondition +
                               eventDateCondition +
                               DOOCondition +
                               ageCondition +
                               TreatmentDateCondition +
                               relaxCondition +
                               medHelpCondition +
                               HealthStatusCondition +
                               " GROUP BY e.area_id, HelpName) z1" +
                               " GROUP BY 1) medHelp ON medHelp.area_id = _all.area_id" +
                               " LEFT JOIN(SELECT e.area_id," +
                               " CASE WHEN (TransfertedDepart IS NOT NULL AND TransfertedDepart<> '') THEN COUNT(e.id) ELSE 0 END ambulance" +
                               " FROM event e" +
                               " join client c on e.client_id = c.id" +
                               " WHERE e.deleted = 0" +
                               " AND (TransfertedDepart IS NOT NULL AND TransfertedDepart <> '')" +
                               areaCondition +
                               medOrgCondition +
                               eventDateCondition +
                               DOOCondition +
                               ageCondition +
                               TreatmentDateCondition +
                               relaxCondition +
                               medHelpCondition +
                               HealthStatusCondition +
                               " GROUP BY 1) reanim ON _all.area_id = reanim.area_id" +
                               " JOIN area a ON _all.area_id = a.id";

                using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnection())
                {
                    sqlConnection.Open();

                    using (MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection))
                    {
                        events.Load(sqlCommand.ExecuteReader());
                    }
                }

                return(events);
            }
            catch (Exception ex)
            {
                logger.Error(ex, $"\r\n#---------#\r\n{ex.StackTrace}\r\n##---------##\r\n{ex.Message}\r\n###---------###\r\n{ex.Source}");
                MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(null);
            }
        }
Esempio n. 16
0
        public static DataTable SetDuplicate()
        {
            try
            {
                DataTable DataTable = new DataTable();

                string query = "SELECT e.area_id," +
                               " e.doo_id," +
                               " e.relax_id," +
                               " e.client_id," +
                               " COUNT(e.id) cntEvent" +
                               " FROM event e" +
                               " JOIN client c ON e.client_id = c.id" +
                               " WHERE e.deleted = 0" +
                               " GROUP BY 1,2,3,4" +
                               " HAVING cntEvent > 1";

                using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnection())
                {
                    sqlConnection.Open();

                    using (MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection))
                    {
                        DataTable.Load(sqlCommand.ExecuteReader());
                    }
                }

                string area   = " and e.area_id in (";
                string doo    = " and e.doo_id in (";
                string relax  = " and e.relax_id in (";
                string client = " and e.client_id in (";

                for (int i = 0; i < DataTable.Rows.Count; i++)
                {
                    if (i != DataTable.Rows.Count - 1)
                    {
                        area   += $"{DataTable.Rows[i][0].ToString()}, ";
                        doo    += $"{DataTable.Rows[i][1].ToString()}, ";
                        relax  += $"{DataTable.Rows[i][2].ToString()}, ";
                        client += $"{DataTable.Rows[i][3].ToString()}, ";
                    }
                    else
                    {
                        area   += $"{DataTable.Rows[i][0].ToString()})";
                        doo    += $"{DataTable.Rows[i][1].ToString()})";
                        relax  += $"{DataTable.Rows[i][2].ToString()})";
                        client += $"{DataTable.Rows[i][3].ToString()})";
                    }
                }

                query = "select e.id," +
                        " a.name as AreaName," +
                        " mo.name as medOrgName," +
                        " e.eventDate," +
                        " doo.FullName as DOOName," +
                        " doo.Address as DOOAddress," +
                        " c.FIO as clientFIO," +
                        " c.birthDate as clientBirthDate," +
                        " CONCAT((YEAR(current_date()) - YEAR(c.birthDate)) - if (DAYOFYEAR(current_date()) > DAYOFYEAR(c.birthDate),0,1),' лет ', (IF(MONTH(CURDATE()) - MONTH(c.birthDate) < 0, MONTH(CURDATE()) - MONTH(c.birthDate) + 12, MONTH(CURDATE()) - MONTH(c.birthDate))),' мес.') AS clientAge," +
                        " c.Address as clientAddress," +
                        " CONCAT(r1.Name, ' (', r.Name, ')') as relaxName," +
                        " e.TreatmentDate," +
                        " e.HelpName," +
                        " hc.FullName," +
                        " e.DiagName," +
                        " e.DiagID," +
                        " e.Speciality," +
                        " e.Department," +
                        " e.TransfertedDepart," +
                        " if (e.TransfertedDate = '0000-00-00', \"\", e.TransfertedDate) as TransfertedDate," +
                        " e.HealthStatus" +
                        " from event as e" +
                        " join area as a on e.area_id = a.id" +
                        " join medorganisation as mo on e.medOrg_id = mo.id" +
                        " join childrenshealthorganization as doo on e.doo_id = doo.id" +
                        " join client as c on e.client_id = c.id" +
                        " join relaxinfo as r on e.relax_id = r.id" +
                        " join relaxinfo as r1 on r.parent_id = r1.id" +
                        " join HelpCare as hc on e.HelpName = hc.id" +
                        " where e.deleted = 0" +
                        " and doo.deleted = 0" +
                        " and c.deleted = 0" +
                        " and a.deleted = 0" +
                        " and mo.deleted = 0" +
                        area +
                        doo +
                        relax +
                        client;

                DataTable = new DataTable();

                using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnection())
                {
                    sqlConnection.Open();

                    using (MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection))
                    {
                        DataTable.Load(sqlCommand.ExecuteReader());
                    }
                }

                return(DataTable);
            }
            catch (Exception ex)
            {
                logger.Error(ex, $"\r\n#---------#\r\n{ex.StackTrace}\r\n##---------##\r\n{ex.Message}\r\n###---------###\r\n{ex.Source}");
                MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(null);
            }
        }
Esempio n. 17
0
        public static DataTable ReportWord(string areaCondition, string medOrgCondition, string eventDateCondition, string DOOCondition, string ageCondition, string TreatmentDateCondition, string relaxCondition, string medHelpCondition, string HealthStatusCondition)
        {
            try
            {
                DataTable reportWord = new DataTable();

                string query = "SELECT a.name," +
                               " _area.AllEvent," +
                               " ri.name," +
                               " _relax.relaxCNT," +
                               " doo.FullName," +
                               " _doo.dooCNT," +
                               " hc.FullName," +
                               " _help.helpCNT," +
                               " _diag.DiagName," +
                               " _diag.diagCNT" +
                               " FROM(SELECT e.area_id," +
                               " COUNT(e.id) AllEvent" +
                               " FROM event e" +
                               " join client c on e.client_id = c.id" +
                               " WHERE e.deleted = 0" +
                               " AND c.deleted = 0" +
                               areaCondition +
                               medOrgCondition +
                               eventDateCondition +
                               DOOCondition +
                               ageCondition +
                               TreatmentDateCondition +
                               relaxCondition +
                               medHelpCondition +
                               HealthStatusCondition +
                               " GROUP BY 1) _area" +
                               " LEFT JOIN(SELECT e.area_id," +
                               " ri.parent_id," +
                               " COUNT(e.id) relaxCNT" +
                               " FROM event e" +
                               " JOIN client c ON e.client_id = c.id" +
                               " JOIN relaxinfo ri ON e.relax_id = ri.id" +
                               " WHERE e.deleted = 0" +
                               " AND c.deleted = 0" +
                               areaCondition +
                               medOrgCondition +
                               eventDateCondition +
                               DOOCondition +
                               ageCondition +
                               TreatmentDateCondition +
                               relaxCondition +
                               medHelpCondition +
                               HealthStatusCondition +
                               " GROUP BY 1,2) _relax ON _area.area_id = _relax.area_id" +
                               " LEFT JOIN(SELECT e.area_id," +
                               " ri.parent_id," +
                               " e.doo_id," +
                               " COUNT(e.id) dooCNT" +
                               " FROM event e" +
                               " JOIN client c ON e.client_id = c.id" +
                               " JOIN relaxinfo ri ON e.relax_id = ri.id" +
                               " WHERE e.deleted = 0" +
                               " AND c.deleted = 0" +
                               areaCondition +
                               medOrgCondition +
                               eventDateCondition +
                               DOOCondition +
                               ageCondition +
                               TreatmentDateCondition +
                               relaxCondition +
                               medHelpCondition +
                               HealthStatusCondition +
                               " GROUP BY 1,2,3)_doo ON _relax.area_id = _doo.area_id AND _relax.parent_id = _doo.parent_id" +
                               " LEFT JOIN(SELECT e.area_id," +
                               " ri.parent_id," +
                               " e.doo_id," +
                               " e.HelpName," +
                               " COUNT(e.id) helpCNT" +
                               " FROM event e" +
                               " JOIN client c ON e.client_id = c.id" +
                               " JOIN relaxinfo ri ON e.relax_id = ri.id" +
                               " WHERE e.deleted = 0" +
                               " AND c.deleted = 0" +
                               areaCondition +
                               medOrgCondition +
                               eventDateCondition +
                               DOOCondition +
                               ageCondition +
                               TreatmentDateCondition +
                               relaxCondition +
                               medHelpCondition +
                               HealthStatusCondition +
                               " GROUP BY 1,2,3,4) _help ON _doo.area_id = _help.area_id AND _doo.parent_id = _help.parent_id AND _doo.doo_id = _help.doo_id" +
                               " LEFT JOIN(SELECT e.area_id," +
                               " ri.parent_id," +
                               " e.doo_id," +
                               " e.HelpName," +
                               " e.DiagName," +
                               " COUNT(e.id) diagCNT" +
                               " FROM event e" +
                               " JOIN client c ON e.client_id = c.id" +
                               " JOIN relaxinfo ri ON e.relax_id = ri.id" +
                               " WHERE e.deleted = 0" +
                               " AND c.deleted = 0" +
                               areaCondition +
                               medOrgCondition +
                               eventDateCondition +
                               DOOCondition +
                               ageCondition +
                               TreatmentDateCondition +
                               relaxCondition +
                               medHelpCondition +
                               HealthStatusCondition +
                               " GROUP BY 1,2,3,4,5) _diag ON _diag.area_id = _help.area_id AND _diag.parent_id = _help.parent_id AND _diag.doo_id = _help.doo_id AND _help.HelpName = _diag.HelpName" +
                               " JOIN area a ON _area.area_id = a.id" +
                               " JOIN relaxinfo ri ON _relax.parent_id = ri.id" +
                               " JOIN childrenshealthorganization doo ON _doo.doo_id = doo.id" +
                               " JOIN HelpCare hc ON _help.HelpName = hc.id";

                using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnection())
                {
                    sqlConnection.Open();

                    using (MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection))
                    {
                        reportWord.Load(sqlCommand.ExecuteReader());
                    }
                }

                return(reportWord);
            }
            catch (Exception ex)
            {
                logger.Error(ex, $"\r\n#---------#\r\n{ex.StackTrace}\r\n##---------##\r\n{ex.Message}\r\n###---------###\r\n{ex.Source}");
                MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(null);
            }
        }