diff -r 574434cd03c4 tryton/health/core.py
--- a/tryton/health/core.py	Wed Aug 03 06:28:29 2022 +0200
+++ b/tryton/health/core.py	Thu Aug 04 15:30:13 2022 +0200
@@ -111,19 +111,22 @@
 def get_institution():
     # Retrieve the institution associated to this GNU Health instance
     # That is associated to the Company.
-    company = Transaction().context.get('company')
+    pool = Pool()
+    Company = pool.get('company.company')
+    Institution = pool.get('gnuhealth.institution')
+    company = Company.__table__()
+    institution = Institution.__table__()
+
+    company_id = Transaction().context.get('company')
 
     cursor = Transaction().connection.cursor()
-    cursor.execute('SELECT party FROM company_company WHERE id=%s \
-        LIMIT 1', (company,))
-    party_id = cursor.fetchone()
-    if party_id:
-        cursor = Transaction().connection.cursor()
-        cursor.execute('SELECT id FROM gnuhealth_institution WHERE \
-            name = %s LIMIT 1', (party_id[0],))
-        institution_id = cursor.fetchone()
-        if (institution_id):
-            return int(institution_id[0])
+    cursor.execute(*company.join(institution, condition=(
+                institution.name == company.party)).select(
+            institution.id,
+            where=(company.id == company_id)))
+    institution_id = cursor.fetchone()
+    if institution_id:
+        return int(institution_id[0])
 
 
 def get_health_professional(required=True):
@@ -132,20 +135,22 @@
     # If the method is called with the arg "required" as False, then
     # the error message won't be shown in the case of not finding
     # the corresponding healthprof (eg, creating a new appointment)
+    pool = Pool()
+    Party = pool.get('party.party')
+    Professional = pool.get('gnuhealth.healthprofessional')
+    party = Party.__table__()
+    professional = Professional.__table__()
+
     cursor = Transaction().connection.cursor()
-    User = Pool().get('res.user')
-    user = User(Transaction().user)
-    login_user_id = int(user.id)
-    cursor.execute('SELECT id FROM party_party WHERE is_healthprof=True \
-        AND internal_user = %s LIMIT 1', (login_user_id,))
-    partner_id = cursor.fetchone()
-    if partner_id:
-        cursor = Transaction().connection.cursor()
-        cursor.execute('SELECT id FROM gnuhealth_healthprofessional WHERE \
-            name = %s LIMIT 1', (partner_id[0],))
-        healthprof_id = cursor.fetchone()
-        if (healthprof_id):
-            return int(healthprof_id[0])
+    cursor.execute(*party.join(professional,
+            condition=(professional.name == party.id)).select(
+            professional.id,
+            where=(
+                (party.is_healthprof == True)
+                & (party.internal_user == Transaction().user))))
+    healthprof_id = cursor.fetchone()
+    if healthprof_id:
+        return int(healthprof_id[0])
     else:
         if required:
             raise NoAssociatedHealthProfessional(gettext(
diff -r 574434cd03c4 tryton/health_icu/health_icu.py
--- a/tryton/health_icu/health_icu.py	Wed Aug 03 06:28:29 2022 +0200
+++ b/tryton/health_icu/health_icu.py	Thu Aug 04 15:30:13 2022 +0200
@@ -82,11 +82,12 @@
     def check_patient_admitted_at_icu(self):
         # Verify that the patient is not at ICU already
         cursor = Transaction().connection.cursor()
-        cursor.execute("SELECT count(name) "
-            "FROM " + self._table + "  \
-            WHERE (name = %s AND admitted)",
-            (str(self.name.id),))
-        if cursor.fetchone()[0] > 1:
+        table = self.__class__.__table__()
+        cursor.execute(*table.select(
+                table.name, where=(
+                    (table.name == self.name.id)
+                    & (table.admitted == True))))
+        if cursor.fetchone():
             raise PatientAlreadyInICU(
                 gettext('health_icu.msg_patient_already_in_icu'))
 
@@ -451,11 +452,12 @@
     def check_patient_current_mv(self):
         # Check for only one current mechanical ventilation on patient
         cursor = Transaction().connection.cursor()
-        cursor.execute("SELECT count(name) "
-            "FROM " + self._table + "  \
-            WHERE (name = %s AND current_mv)",
-            (str(self.name.id),))
-        if cursor.fetchone()[0] > 1:
+        table = self.__class__.__table__()
+        cursor.execute(*table.select(
+                table.name, where=(
+                    (table.name == self.name.id)
+                    & (table.current_mv == True))))
+        if cursor.fetchone():
             raise PatientAlreadyOnMV(
                 gettext('health_icu.msg_patient_already_on_mv'))
 
diff -r 574434cd03c4 tryton/health_inpatient/health_inpatient.py
--- a/tryton/health_inpatient/health_inpatient.py	Wed Aug 03 06:28:29 2022 +0200
+++ b/tryton/health_inpatient/health_inpatient.py	Thu Aug 04 15:30:13 2022 +0200
@@ -219,33 +219,39 @@
     @classmethod
     @ModelView.button
     def confirmed(cls, registrations):
-        registration_id = registrations[0]
-        Bed = Pool().get('gnuhealth.hospital.bed')
+        pool = Pool()
+        Bed = pool.get('gnuhealth.hospital.bed')
         cursor = Transaction().connection.cursor()
-        bed_id = registration_id.bed.id
-        cursor.execute(
-            "SELECT COUNT(*) \
-            FROM gnuhealth_inpatient_registration \
-            WHERE (hospitalization_date::timestamp,discharge_date::timestamp) \
-                OVERLAPS (timestamp %s, timestamp %s) \
-              AND (state = %s or state = %s or state = %s) \
-              AND bed = CAST(%s AS INTEGER) ",
-            (registration_id.hospitalization_date,
-                registration_id.discharge_date,
-                'confirmed', 'hospitalized', 'done', str(bed_id)))
+        table = cls.__table__()
 
-        res = cursor.fetchone()
-        if (registration_id.discharge_date.date() <
-                registration_id.hospitalization_date.date()):
-            raise DischargeBeforeAdmission(
-                gettext('health_inpatient.msg_discharge_befor_admission'))
-
-        if res[0] > 0:
-            raise BedIsNotAvailable(
-                gettext('health_inpatient.msg_bed_is_not_available'))
-        else:
-            cls.write(registrations, {'state': 'confirmed'})
-            Bed.write([registration_id.bed], {'state': 'reserved'})
+        beds = []
+        for registration in registrations:
+            if (registration.discharge_date.date() <
+                    registration.hospitalization_date.date()):
+                raise DischargeBeforeAdmission(
+                    gettext('health_inpatient.msg_discharge_befor_admission'))
+            cursor.execute(*table.select(
+                    table.id,
+                    where=(((table.hospitalization_date
+                                <= registration.hospitalization_date)
+                            & (table.discharge_date
+                                >= registration.hospitalization_date))
+                        | ((table.hospitalization_date
+                                <= registration.discharge_date)
+                            & (table.discharge_date
+                                >= registration.discharge_date))
+                        | ((table.hospitalization_date
+                                >= registration.hospitalization_date)
+                            & (table.discharge_date
+                                <= registration.discharge_date)))
+                    & table.state.in_(['confirmed', 'hospitalized', 'done'])
+                    & (table.bed == registration.bed.id)))
+            if cursor.fetchone():
+                raise BedIsNotAvailable(
+                    gettext('health_inpatient.msg_bed_is_not_available'))
+            beds.append(registration.bed)
+        cls.write(registrations, {'state': 'confirmed'})
+        Bed.write(beds, {'state': 'reserved'})
 
     @classmethod
     @ModelView.button
diff -r 574434cd03c4 tryton/health_reporting/report/epidemics_report.py
--- a/tryton/health_reporting/report/epidemics_report.py	Wed Aug 03 06:28:29 2022 +0200
+++ b/tryton/health_reporting/report/epidemics_report.py	Thu Aug 04 15:30:13 2022 +0200
@@ -19,6 +19,8 @@
 #    along with this program.  If not, see <http://www.gnu.org/licenses/>.
 #
 ##############################################################################
+from sql.aggregate import Count
+from sql.functions import DateTrunc
 from datetime import date, datetime
 from trytond.report import Report
 from trytond.pool import Pool
@@ -42,84 +44,77 @@
     def get_population_with_no_dob(cls):
         """ Return Total Number of living people in the system
         without a date of birth"""
-        cursor = Transaction().connection.cursor()
+        pool = Pool()
+        Party = pool.get('party.party')
 
-        # Check for entries without date of birth
-        cursor.execute("SELECT COUNT(id) \
-            FROM party_party WHERE is_person is TRUE and \
-            deceased is not TRUE and dob is null")
-
-        res = cursor.fetchone()[0]
-
-        return(res)
+        return Party.search([
+                ('is_person', '=', True),
+                ('decesased', '!=', True),
+                ('dob', '=', None),
+                ], count=True)
 
     @classmethod
     def get_population(cls, date1, date2, gender, total):
         """ Return Total Number of living people in the system
         segmented by age group and gender"""
-        cursor = Transaction().connection.cursor()
-
-        if (total):
-            cursor.execute("SELECT COUNT(id) \
-                FROM party_party WHERE \
-                gender = %s and deceased is not TRUE", (gender))
+        pool = Pool()
+        Party = pool.get('party.party')
 
-        else:
-            cursor.execute("SELECT COUNT(id) \
-                FROM party_party \
-                WHERE dob BETWEEN %s and %s AND \
-                gender = %s  \
-                and deceased is not TRUE", (date2, date1, gender))
+        domain = [
+            ('deceased', '!=', True),
+            ('gender', '=', gender),
+            ]
 
-        res = cursor.fetchone()[0]
+        if not total:
+            domain.append(('dob', '>=', date2))
+            domain.append(('dob', '<=', date1))
 
-        return(res)
+        return Party.search(domain, count=True)
 
     @classmethod
     def get_new_people(cls, start_date, end_date, in_health_system):
         """ Return Total Number of new registered persons alive """
+        pool = Pool()
+        Party = pool.get('party.party')
 
-        query = "SELECT COUNT(activation_date) \
-            FROM party_party \
-            WHERE activation_date BETWEEN \
-            %s AND %s and is_person=True and deceased is not TRUE"
-        if (in_health_system):
-            query = query + " and is_patient=True"
-        cursor = Transaction().connection.cursor()
-        cursor.execute(query, (start_date, end_date))
-        res = cursor.fetchone()
-        return(res)
+        domain = [
+            ('activation_date', '>=', start_date),
+            ('activation_date', '<=', end_date),
+            ('decesased', '!=', True),
+            ('is_person', '=', True),
+            ]
+
+        if in_health_system:
+            domain.append(('is_patient', '=', True))
+
+        return Party.search(domain, count=True)
 
     @classmethod
     def get_new_births(cls, start_date, end_date):
         """ Return birth certificates within that period """
-
-        query = "SELECT COUNT(dob) \
-            FROM gnuhealth_birth_certificate \
-            WHERE dob BETWEEN \
-            %s AND %s"
+        pool = Pool()
+        BirthCertificate = pool.get('gnuhealth.birth_certificate')
 
-        cursor = Transaction().connection.cursor()
-        cursor.execute(query, (start_date, end_date))
-
-        res = cursor.fetchone()
-        return(res)
+        return BirthCertificate.search([
+                ('dob', '>=', start_date),
+                ('dob', '<=', end_date),
+                ], count=True)
 
     @classmethod
     def get_new_deaths(cls, start_date, end_date):
         """ Return death certificates within that period """
         """ Truncate the timestamp of DoD to match a whole day"""
+        pool = Pool()
+        DeathCertificate = pool.get('gnuhealth.death_certificate')
+        table = DeathCertificate.__table__()
 
-        query = "SELECT COUNT(dod) \
-            FROM gnuhealth_death_certificate \
-            WHERE date_trunc('day', dod) BETWEEN \
-            %s AND %s"
+        dod = DateTrunc('day', table.dod)
 
         cursor = Transaction().connection.cursor()
-        cursor.execute(query, (start_date, end_date))
-
-        res = cursor.fetchone()
-        return(res)
+        cursor.execute(*table.select(
+                Count(table.dod),
+                where=((dod >= start_date) & (dod <= end_date))))
+        return cursor.fetchone()
 
     @classmethod
     def get_confirmed_cases(cls, start_date, end_date, dx):
diff -r 574434cd03c4 tryton/health_reporting/report/summary_report.py
--- a/tryton/health_reporting/report/summary_report.py	Wed Aug 03 06:28:29 2022 +0200
+++ b/tryton/health_reporting/report/summary_report.py	Thu Aug 04 15:30:13 2022 +0200
@@ -18,6 +18,8 @@
 #    along with this program.  If not, see <http://www.gnu.org/licenses/>.
 #
 ##############################################################################
+from sql.aggregate import Count
+from sql.functions import DateTrunc
 from datetime import date, datetime
 from trytond.report import Report
 from trytond.pool import Pool
@@ -34,93 +36,84 @@
     def get_population_with_no_dob(cls):
         """ Return Total Number of living people in the system
         without a date of birth"""
-        cursor = Transaction().connection.cursor()
+        pool = Pool()
+        Party = pool.get('party.party')
 
-        # Check for entries without date of birth
-        cursor.execute("SELECT COUNT(id) \
-            FROM party_party WHERE is_person is TRUE and \
-            deceased is not TRUE and dob is null")
-
-        res = cursor.fetchone()[0]
-
-        return(res)
+        return Party.search([
+                ('is_person', '=', True),
+                ('decesased', '!=', True),
+                ('dob', '=', None),
+                ], count=True)
 
     @classmethod
     def get_population(cls, date1, date2, gender, total):
         """ Return Total Number of living people in the system
         segmented by age group and gender"""
-        cursor = Transaction().connection.cursor()
-
-        if (total):
-            cursor.execute("SELECT COUNT(id) \
-                FROM party_party WHERE \
-                gender = %s and deceased is not TRUE", (gender))
+        pool = Pool()
+        Party = pool.get('party.party')
 
-        else:
-            cursor.execute("SELECT COUNT(id) \
-                FROM party_party \
-                WHERE dob BETWEEN %s and %s AND \
-                gender = %s  \
-                and deceased is not TRUE", (date2, date1, gender))
+        domain = [
+            ('deceased', '!=', True),
+            ('gender', '=', gender),
+            ]
 
-        res = cursor.fetchone()[0]
+        if not total:
+            domain.append(('dob', '>=', date2))
+            domain.append(('dob', '<=', date1))
 
-        return(res)
+        return Party.search(domain, count=True)
 
     @classmethod
     def get_new_people(cls, start_date, end_date, in_health_system):
         """ Return Total Number of new registered persons alive """
-
-        query = "SELECT COUNT(activation_date) \
-            FROM party_party \
-            WHERE activation_date BETWEEN \
-            %s AND %s and is_person=True and deceased is not TRUE"
+        pool = Pool()
+        Party = pool.get('party.party')
 
-        if (in_health_system):
-            query = query + " and is_patient=True"
+        domain = [
+            ('activation_date', '>=', start_date),
+            ('activation_date', '<=', end_date),
+            ('decesased', '!=', True),
+            ('is_person', '=', True),
+            ]
 
-        cursor = Transaction().connection.cursor()
-        cursor.execute(query, (start_date, end_date))
+        if in_health_system:
+            domain.append(('is_patient', '=', True))
 
-        res = cursor.fetchone()
-        return(res)
+        return Party.search(domain, count=True)
 
     @classmethod
     def get_new_births(cls, start_date, end_date):
         """ Return birth certificates within that period """
-
-        query = "SELECT COUNT(dob) \
-            FROM gnuhealth_birth_certificate \
-            WHERE dob BETWEEN \
-            %s AND %s"
+        pool = Pool()
+        BirthCertificate = pool.get('gnuhealth.birth_certificate')
 
-        cursor = Transaction().connection.cursor()
-        cursor.execute(query, (start_date, end_date))
-
-        res = cursor.fetchone()
-        return(res)
+        return BirthCertificate.search([
+                ('dob', '>=', start_date),
+                ('dob', '<=', end_date),
+                ], count=True)
 
     @classmethod
     def get_new_deaths(cls, start_date, end_date):
         """ Return death certificates within that period """
         """ Truncate the timestamp of DoD to match a whole day"""
+        pool = Pool()
+        DeathCertificate = pool.get('gnuhealth.death_certificate')
+        table = DeathCertificate.__table__()
 
-        query = "SELECT COUNT(dod) \
-            FROM gnuhealth_death_certificate \
-            WHERE date_trunc('day', dod) BETWEEN \
-            %s AND %s"
+        dod = DateTrunc('day', table.dod)
 
         cursor = Transaction().connection.cursor()
-        cursor.execute(query, (start_date, end_date))
-
-        res = cursor.fetchone()
-        return(res)
+        cursor.execute(*table.select(
+                Count(table.dod),
+                where=((dod >= start_date) & (dod <= end_date))))
+        return cursor.fetchone()
 
     @classmethod
-    def get_evaluations(cls, start_date, end_date, dx):
+    def get_evaluations(cls, start_date, end_date, dx, count=False):
         """ Return evaluation info """
+        pool = Pool()
 
-        Evaluation = Pool().get('gnuhealth.patient.evaluation')
+        Evaluation = pool.get('gnuhealth.patient.evaluation')
         start_date = datetime.strptime(str(start_date), '%Y-%m-%d')
         end_date = datetime.strptime(str(end_date), '%Y-%m-%d')
         end_date += relativedelta(hours=+23, minutes=+59, seconds=+59)
@@ -133,28 +126,12 @@
         if dx:
             clause.append(('diagnosis', '=', dx))
 
-        res = Evaluation.search(clause)
-
-        return(res)
+        return Evaluation.search(clause, count=count)
 
     @classmethod
     def count_evaluations(cls, start_date, end_date, dx):
         """ count diagnoses by groups """
-
-        Evaluation = Pool().get('gnuhealth.patient.evaluation')
-        start_date = datetime.strptime(str(start_date), '%Y-%m-%d')
-        end_date = datetime.strptime(str(end_date), '%Y-%m-%d')
-        end_date += relativedelta(hours=+23, minutes=+59, seconds=+59)
-
-        clause = [
-            ('evaluation_start', '>=', start_date),
-            ('evaluation_start', '<=', end_date),
-            ('diagnosis', '=', dx),
-            ]
-
-        res = Evaluation.search_count(clause)
-
-        return(res)
+        return cls.get_evaluation(start_date, end_date, dx, count=True)
 
     @classmethod
     def get_context(cls, records, header, data):
diff -r 574434cd03c4 tryton/health_surgery/health_surgery.py
--- a/tryton/health_surgery/health_surgery.py	Wed Aug 03 06:28:29 2022 +0200
+++ b/tryton/health_surgery/health_surgery.py	Thu Aug 04 15:30:13 2022 +0200
@@ -370,9 +370,9 @@
 
     institution = fields.Many2One('gnuhealth.institution', 'Institution')
 
-    report_surgery_date = fields.Function(fields.Date('Surgery Date'), 
+    report_surgery_date = fields.Function(fields.Date('Surgery Date'),
         'get_report_surgery_date')
-    report_surgery_time = fields.Function(fields.Time('Surgery Time'), 
+    report_surgery_time = fields.Function(fields.Time('Surgery Time'),
         'get_report_surgery_time')
 
     surgery_team = fields.One2Many(
@@ -413,7 +413,7 @@
         return res
 
 
-    # Show the gender and age upon entering the patient 
+    # Show the gender and age upon entering the patient
     # These two are function fields (don't exist at DB level)
     @fields.depends('patient')
     def on_change_patient(self):
@@ -468,7 +468,7 @@
                 },
 
             })
-        
+
     @classmethod
     def validate(cls, surgeries):
         super(Surgery, cls).validate(surgeries)
@@ -491,61 +491,64 @@
 
     ## Method to check for availability and make the Operating Room reservation
      # for the associated surgery
-     
+
     @classmethod
     @ModelView.button
     def confirmed(cls, surgeries):
-        surgery_id = surgeries[0]
         Operating_room = Pool().get('gnuhealth.hospital.or')
+        table = cls.__table__()
         cursor = Transaction().connection.cursor()
 
-        # Operating Room and end surgery time check
-        if (not surgery_id.operating_room or not surgery_id.surgery_end_date):
-            raise OperatingRoomAndDateRequired(
-                    gettext('health_surgery.msg_or_and_time_needed'))
+        for surgery in surgeries:
+            # Operating Room and end surgery time check
+            if (not surgery.operating_room or not surgery.surgery_end_date):
+                raise OperatingRoomAndDateRequired(
+                        gettext('health_surgery.msg_or_and_time_needed'))
+            if surgery.surgery_end_date <  surgery.surgery_date:
+                    raise EndDateBeforeStart(
+                        gettext('health_surgery.msg_end_date_before_start'))
+            cursor.execute(*table.select(
+                    table.id,
+                    where=(((table.surgery_date
+                                <= surgery.surgery_date)
+                            & (table.surgery_end_date
+                                >= surgery.surgery_date))
+                        | ((table.surgery_date
+                                <= surgery.surgery_end_date)
+                            & (table.surgery_end_date
+                                >= surgery.surgery_end_date))
+                        | ((table.surgery_date
+                                >= surgery.surgery_date)
+                            & (table.surgery_end_date
+                                <= surgery.surgery_end_date)))
+                    & table.state.in_(['confirmed', 'in_progress'])
+                    & (table.operating_room == surgery.operating_room.id)))
+            if cursor.fetchone():
+                raise ORNotAvailable(
+                        gettext('health_surgery.msg_or_is_not_available'))
 
-        or_id = surgery_id.operating_room.id
-        cursor.execute("SELECT COUNT(*) \
-            FROM gnuhealth_surgery \
-            WHERE (surgery_date::timestamp,surgery_end_date::timestamp) \
-                OVERLAPS (timestamp %s, timestamp %s) \
-              AND (state = %s or state = %s) \
-              AND operating_room = CAST(%s AS INTEGER) ",
-            (surgery_id.surgery_date,
-            surgery_id.surgery_end_date,
-            'confirmed', 'in_progress', str(or_id)))
-        res = cursor.fetchone()
-        if (surgery_id.surgery_end_date <
-            surgery_id.surgery_date):
-                raise EndDateBeforeStart(
-                    gettext('health_surgery.msg_end_date_before_start'))
-        if res[0] > 0:
-            raise ORNotAvailable(
-                    gettext('health_surgery.msg_or_is_not_available'))
+        cls.write(surgeries, {'state': 'confirmed'})
 
-        else:
-            cls.write(surgeries, {'state': 'confirmed'})
- 
     # Cancel the surgery and set it to draft state
     # Free the related Operating Room
-    
+
     @classmethod
     @ModelView.button
     def cancel(cls, surgeries):
         surgery_id = surgeries[0]
         Operating_room = Pool().get('gnuhealth.hospital.or')
-        
+
         cls.write(surgeries, {'state': 'cancelled'})
 
     # Start the surgery
-    
+
     @classmethod
     @ModelView.button
     def start(cls, surgeries):
         surgery_id = surgeries[0]
         Operating_room = Pool().get('gnuhealth.hospital.or')
 
-        cls.write(surgeries, 
+        cls.write(surgeries,
             {'state': 'in_progress',
              'surgery_date': datetime.now(),
              'surgery_end_date': datetime.now()})
@@ -554,16 +557,16 @@
 
     # Finnish the surgery
     # Free the related Operating Room
-    
+
     @classmethod
     @ModelView.button
     def done(cls, surgeries):
         surgery_id = surgeries[0]
         Operating_room = Pool().get('gnuhealth.hospital.or')
-        
+
         cls.write(surgeries, {'state': 'done',
                               'surgery_end_date': datetime.now()})
-                              
+
         Operating_room.write([surgery_id.operating_room], {'state': 'free'})
 
 
@@ -656,11 +659,11 @@
         'product.product', 'Supply', required=True,
         domain=[('is_medical_supply', '=', True)],
         help="Supply to be used in this surgery")
-   
+
     notes = fields.Char('Notes')
     qty_used = fields.Numeric('Used', required=True,
         help="Actual amount used")
-    
+
 class SurgeryTeam(ModelSQL, ModelView):
     'Team Involved in the surgery'
     __name__ = 'gnuhealth.surgery_team'
@@ -674,7 +677,7 @@
         'gnuhealth.hp_specialty', 'Role',
         domain=[('name', '=', Eval('team_member'))],
         depends=['team_member'])
-    
+
     notes = fields.Char('Notes')
 
 class PatientData(ModelSQL, ModelView):
