class PrescriptionsService

  def self.get_sql_for_role(user_id)
    joins = "
              --check if activate allowed
              LEFT JOIN (
                          SELECT
                            DISTINCT
                            p.id AS prescription_id
                          FROM users
                            INNER JOIN user_permissions AS up ON up.user_id = users.id
                            INNER JOIN prescription_general_permissions AS pgp ON pgp.permission_id = up.permission_id
                            INNER JOIN prescription_general_permission_actions AS pgpa
                              ON pgpa.id = pgp.prescription_general_permission_action_id
                            CROSS JOIN prescriptions AS p
                            LEFT JOIN user_business_units AS ubu ON ubu.user_id = users.id
                          WHERE users.id = #{user_id} AND pgpa.id_name = '_activate' AND ( users.limited_by_unit = 0 OR ubu.business_unit_id = p.business_unit_id )
                        ) AS perms_activate ON perms_activate.prescription_id = prescriptions.id
              --check if access allowed
              LEFT JOIN (
                          SELECT
                            DISTINCT
                            pgpa.id_name AS pgpa_id_name
                          FROM users
                            INNER JOIN user_permissions AS up ON up.user_id = users.id
                            INNER JOIN prescription_general_permissions AS pgp ON pgp.permission_id = up.permission_id
                            INNER JOIN prescription_general_permission_actions AS pgpa
                              ON pgpa.id = pgp.prescription_general_permission_action_id
                          WHERE users.id = #{user_id}
                        ) AS perms_access ON perms_access.pgpa_id_name = '_access'
              --check if create allowed
              LEFT JOIN (
                          SELECT
                            DISTINCT
                            pgpa.id_name AS pgpa_id_name
                          FROM users
                            INNER JOIN user_permissions AS up ON up.user_id = users.id
                            INNER JOIN prescription_general_permissions AS pgp ON pgp.permission_id = up.permission_id
                            INNER JOIN prescription_general_permission_actions AS pgpa
                              ON pgpa.id = pgp.prescription_general_permission_action_id
                          WHERE users.id = #{user_id}
                        ) AS perms_create ON perms_create.pgpa_id_name = '_create'
              --check if doctor_access_edit allowed
              LEFT JOIN (
                          SELECT
                            DISTINCT
                            p.id AS prescription_id
                          FROM users
                            INNER JOIN user_permissions AS up ON up.user_id = users.id
                            INNER JOIN prescription_general_permissions AS pgp ON pgp.permission_id = up.permission_id
                            INNER JOIN prescription_general_permission_actions AS pgpa
                              ON pgpa.id = pgp.prescription_general_permission_action_id
                            CROSS JOIN prescriptions AS p
                            LEFT JOIN user_business_units AS ubu ON ubu.user_id = users.id
                          WHERE users.id = #{user_id} AND pgpa.id_name = '_doctor_access_edit' AND ( users.limited_by_unit = 0 OR ubu.business_unit_id = p.business_unit_id )
                        ) AS perms_doctor_access_edit ON perms_doctor_access_edit.prescription_id = prescriptions.id
              --check if cancel allowed
              LEFT JOIN (
                          SELECT
                            DISTINCT
                            p.id AS prescription_id
                          FROM users
                            INNER JOIN user_permissions AS up ON up.user_id = users.id
                            INNER JOIN prescription_general_permissions AS pgp ON pgp.permission_id = up.permission_id
                            INNER JOIN prescription_general_permission_actions AS pgpa
                              ON pgpa.id = pgp.prescription_general_permission_action_id
                            INNER JOIN user_options AS uo ON uo.id = users.user_option_id
                            INNER JOIN prescription_statuses AS ps ON ps.id = pgp.prescription_status_id
                            INNER JOIN prescriptions AS p ON p.status_id = ps.id
                            LEFT JOIN prescription_shares AS pshare ON pshare.prescription_id = p.id
                            LEFT JOIN prescription_permission_group_types AS ppgt ON ppgt.id = pgp.group_type_id
                            LEFT JOIN user_business_units AS ubu ON ubu.user_id = users.id
                            LEFT JOIN user_option_employees AS uoe ON uoe.user_id = users.id
                          WHERE
                            users.id = #{user_id} AND pgpa.id_name = '_cancel' AND (
                              (ppgt.id_name = '_full' OR uo.id_name = 'all_employees') AND (users.limited_by_unit = 0 OR ubu.business_unit_id = p.business_unit_id) OR
                              ppgt.id_name = '_by_access' AND (
                                (uo.id_name = 'own' OR uo.id_name = 'all_employees') AND pshare.doctor_id = users.employee_id OR
                                uo.id_name = 'employees_list' AND uoe.employee_id = pshare.doctor_id
                              ) OR
                              ppgt.id_name = '_by_doctor_prescription' AND (
                                (uo.id_name = 'own' OR uo.id_name = 'all_employees') AND p.doctor_id = users.employee_id AND
                                pshare.doctor_id = users.employee_id AND (users.limited_by_unit = 0 OR ubu.business_unit_id = p.business_unit_id) OR
                                uo.id_name = 'employees_list' AND p.doctor_id = uoe.employee_id AND pshare.doctor_id = uoe.employee_id AND
                                (users.limited_by_unit = 0 OR ubu.business_unit_id = p.business_unit_id)
                              )
                            )
                        ) AS perms_cancel ON perms_cancel.prescription_id = prescriptions.id
              --check if edit allowed
              LEFT JOIN (
                          SELECT
                            DISTINCT
                            p.id AS prescription_id
                          FROM users
                            INNER JOIN user_permissions AS up ON up.user_id = users.id
                            INNER JOIN prescription_general_permissions AS pgp ON pgp.permission_id = up.permission_id
                            INNER JOIN prescription_general_permission_actions AS pgpa
                              ON pgpa.id = pgp.prescription_general_permission_action_id
                            INNER JOIN user_options AS uo ON uo.id = users.user_option_id
                            INNER JOIN prescription_statuses AS ps ON ps.id = pgp.prescription_status_id
                            INNER JOIN prescriptions AS p ON p.status_id = ps.id
                            LEFT JOIN prescription_shares AS pshare ON pshare.prescription_id = p.id
                            LEFT JOIN prescription_permission_group_types AS ppgt ON ppgt.id = pgp.group_type_id
                            LEFT JOIN user_business_units AS ubu ON ubu.user_id = users.id
                            LEFT JOIN user_option_employees AS uoe ON uoe.user_id = users.id
                          WHERE
                            users.id = #{user_id} AND pgpa.id_name = '_edit' AND (
                              (ppgt.id_name = '_full' OR uo.id_name = 'all_employees') AND (users.limited_by_unit = 0 OR ubu.business_unit_id = p.business_unit_id) OR
                              ppgt.id_name = '_by_access' AND (
                                (uo.id_name = 'own' OR uo.id_name = 'all_employees') AND pshare.doctor_id = users.employee_id OR
                                uo.id_name = 'employees_list' AND uoe.employee_id = pshare.doctor_id
                              ) OR
                              ppgt.id_name = '_by_doctor_prescription' AND (
                                (uo.id_name = 'own' OR uo.id_name = 'all_employees') AND p.doctor_id = users.employee_id AND
                                pshare.doctor_id = users.employee_id AND (users.limited_by_unit = 0 OR ubu.business_unit_id = p.business_unit_id) OR
                                uo.id_name = 'employees_list' AND p.doctor_id = uoe.employee_id AND pshare.doctor_id = uoe.employee_id AND
                                (users.limited_by_unit = 0 OR ubu.business_unit_id = p.business_unit_id)
                              )
                            )
                        ) AS perms_edit ON perms_edit.prescription_id = prescriptions.id
              --check if view allowed
              LEFT JOIN (
                          SELECT
                            DISTINCT
                            p.id AS prescription_id
                          FROM users
                            INNER JOIN user_permissions AS up ON up.user_id = users.id
                            INNER JOIN prescription_general_permissions AS pgp ON pgp.permission_id = up.permission_id
                            INNER JOIN prescription_general_permission_actions AS pgpa
                              ON pgpa.id = pgp.prescription_general_permission_action_id
                            INNER JOIN user_options AS uo ON uo.id = users.user_option_id
                            INNER JOIN prescription_statuses AS ps ON ps.id = pgp.prescription_status_id
                            INNER JOIN prescriptions AS p ON p.status_id = ps.id
                            LEFT JOIN patients ON patients.id = p.patient_id
                            LEFT JOIN prescription_shares AS pshare ON pshare.prescription_id = p.id
                            LEFT JOIN prescription_permission_group_types AS ppgt ON ppgt.id = pgp.group_type_id
                            LEFT JOIN user_business_units AS ubu ON ubu.user_id = users.id
                            LEFT JOIN user_option_employees AS uoe ON uoe.user_id = users.id
                          WHERE
                            users.id = #{user_id} AND pgpa.id_name = '_view' AND (
                              patients.patient_consent = 1 OR
                              (ppgt.id_name = '_full' OR uo.id_name = 'all_employees') AND (users.limited_by_unit = 0 OR ubu.business_unit_id = p.business_unit_id) OR
                              ppgt.id_name = '_by_access' AND (
                                (uo.id_name = 'own' OR uo.id_name = 'all_employees') AND pshare.doctor_id = users.employee_id OR
                                uo.id_name = 'employees_list' AND uoe.employee_id = pshare.doctor_id
                              ) OR
                              ppgt.id_name = '_by_doctor_prescription' AND (
                                (uo.id_name = 'own' OR uo.id_name = 'all_employees') AND p.doctor_id = users.employee_id AND
                                  pshare.doctor_id = users.employee_id AND (users.limited_by_unit = 0 OR ubu.business_unit_id = p.business_unit_id) OR
                                uo.id_name = 'employees_list' AND p.doctor_id = uoe.employee_id AND pshare.doctor_id = uoe.employee_id AND
                                (users.limited_by_unit = 0 OR ubu.business_unit_id = p.business_unit_id)
                              )
                            )
                        ) AS perms_view ON perms_view.prescription_id = prescriptions.id
              --check if manual_cancel allowed
              LEFT JOIN (
                          SELECT
                            DISTINCT
                            p.id AS prescription_id
                          FROM users
                            INNER JOIN user_permissions AS up ON up.user_id = users.id
                            INNER JOIN prescription_general_permissions AS pgp ON pgp.permission_id = up.permission_id
                            INNER JOIN prescription_general_permission_actions AS pgpa
                              ON pgpa.id = pgp.prescription_general_permission_action_id
                            CROSS JOIN prescriptions AS p
                            LEFT JOIN user_business_units AS ubu ON ubu.user_id = users.id
                          WHERE users.id = #{user_id} AND pgpa.id_name = '_manual_cancel' AND ( users.limited_by_unit = 0 OR ubu.business_unit_id = p.business_unit_id )
                        ) AS perms_manual_cancel ON perms_manual_cancel.prescription_id = prescriptions.id

      "

    columns = '
              CASE WHEN perms_activate.prescription_id IS NOT NULL THEN 1 ELSE 0 END AS _activate,
              CASE WHEN perms_access.pgpa_id_name IS NOT NULL THEN 1 ELSE 0 END AS _access,
              CASE WHEN perms_create.pgpa_id_name IS NOT NULL THEN 1 ELSE 0 END AS _create,
              CASE WHEN perms_cancel.prescription_id IS NOT NULL THEN 1 ELSE 0 END AS _cancel,
              CASE WHEN perms_edit.prescription_id IS NOT NULL THEN 1 ELSE 0 END AS _edit,
              CASE WHEN perms_view.prescription_id IS NOT NULL THEN 1 ELSE 0 END AS _view,
              CASE WHEN perms_doctor_access_edit.prescription_id IS NOT NULL THEN 1 ELSE 0 END AS _doctor_access_edit,
              CASE WHEN perms_manual_cancel.prescription_id IS NOT NULL THEN 1 ELSE 0 END AS _manual_cancel'


    { joins: joins, columns: columns }

  end

  def self.get_only_own(user_id, remote_ip)
    user = User.find(user_id)
    user.request_ip = remote_ip

    business_units_by_ip =
      if user.business_and_legal_unit_by_ip.present?
        if bu_unit = user.business_and_legal_unit_by_ip[:business_unit]
          bu_unit[:id]
        end
      else
        ''
      end

    join_string = "LEFT JOIN (
              SELECT
                DISTINCT
                p.id AS prescription_id
              FROM users
                INNER JOIN user_permissions AS up ON up.user_id = users.id
                INNER JOIN prescription_general_permissions AS pgp ON pgp.permission_id = up.permission_id
                INNER JOIN user_options AS uo ON uo.id = users.user_option_id
                INNER JOIN prescription_statuses AS ps ON ps.id = pgp.prescription_status_id
                INNER JOIN prescriptions AS p ON p.status_id = ps.id
                LEFT JOIN prescription_shares AS pshare ON pshare.prescription_id = p.id
                LEFT JOIN user_option_employees AS uoe ON uoe.user_id = users.id
              WHERE
                users.id = #{user_id} AND(
                    (uo.id_name = 'own' OR uo.id_name = 'all_employees') AND pshare.doctor_id = users.employee_id AND p.doctor_id = users.employee_id OR
                    uo.id_name = 'employees_list' AND uoe.employee_id = pshare.doctor_id AND p.doctor_id = uoe.employee_id
                )
            ) AS own_only ON own_only.prescription_id = prescriptions.id"

    { filter_string: "prescriptions.business_unit_id = N'#{business_units_by_ip}' AND own_only.prescription_id IS NOT NULL", join_string: join_string  }
  end

  def self.user_is_allowed(user_id, action_name, prescription_id = nil, is_view = false)
    if prescription_id.nil?
      User.
          joins('INNER JOIN user_permissions AS up ON up.user_id = users.id').
          joins('INNER JOIN prescription_general_permissions AS pgp ON pgp.permission_id = up.permission_id').
          joins('INNER JOIN prescription_general_permission_actions AS pgpa ON pgpa.id = pgp.prescription_general_permission_action_id').
          where(id: user_id).
          where('pgpa.id_name = :action_name', action_name: action_name).
          select('pgpa.id_name').
          distinct.
          any?
    elsif is_view
      User.
          joins('INNER JOIN user_permissions AS up ON up.user_id = users.id').
          joins('INNER JOIN prescription_general_permissions AS pgp ON pgp.permission_id = up.permission_id').
          joins('INNER JOIN prescription_general_permission_actions AS pgpa ON pgpa.id = pgp.prescription_general_permission_action_id').
          joins('INNER JOIN user_options AS uo ON uo.id = users.user_option_id').
          joins('INNER JOIN prescription_statuses AS ps ON ps.id = pgp.prescription_status_id').
          joins('INNER JOIN prescriptions AS p ON p.status_id = ps.id').
          joins('LEFT JOIN patients ON patients.id = p.patient_id').
          joins('LEFT JOIN prescription_shares AS pshare ON pshare.prescription_id = p.id').
          joins('LEFT JOIN prescription_permission_group_types AS ppgt ON ppgt.id = pgp.group_type_id').
          joins('LEFT JOIN user_business_units AS ubu ON ubu.user_id = users.id').
          joins('LEFT JOIN user_option_employees AS uoe ON uoe.user_id = users.id').
          where(id: user_id).
          where('pgpa.id_name = :action_name', action_name: action_name).
          where('p.id = :prescription_id', prescription_id: prescription_id).
          where("patients.patient_consent = 1 OR
                    (ppgt.id_name = '_full' OR uo.id_name = 'all_employees') AND (users.limited_by_unit = 0 OR ubu.business_unit_id = p.business_unit_id) OR
                    ppgt.id_name = '_by_access' AND (
                                                     (uo.id_name = 'own' OR uo.id_name = 'all_employees') AND pshare.doctor_id = users.employee_id OR
                                                     uo.id_name = 'employees_list' AND uoe.employee_id = pshare.doctor_id
                                                     ) OR
                    ppgt.id_name = '_by_doctor_prescription' AND (
                                                                  (uo.id_name = 'own' OR uo.id_name = 'all_employees') AND p.doctor_id = users.employee_id AND
                                                                  pshare.doctor_id = users.employee_id AND (users.limited_by_unit = 0 OR ubu.business_unit_id = p.business_unit_id) OR
                                                                  uo.id_name = 'employees_list' AND p.doctor_id = uoe.employee_id AND pshare.doctor_id = uoe.employee_id AND
                                                                  (users.limited_by_unit = 0 OR ubu.business_unit_id = p.business_unit_id)
                                                                  )").
          select('p.id').
          distinct.
          any?

    else
      if action_name == '_activate' || action_name == '_doctor_access_edit'
        User.
            joins('INNER JOIN user_permissions AS up ON up.user_id = users.id').
            joins('INNER JOIN prescription_general_permissions AS pgp ON pgp.permission_id = up.permission_id').
            joins('INNER JOIN prescription_general_permission_actions AS pgpa ON pgpa.id = pgp.prescription_general_permission_action_id').
            joins('CROSS JOIN prescriptions AS p').
            joins('LEFT JOIN user_business_units AS ubu ON ubu.user_id = users.id').
            where(id: user_id).
            where('pgpa.id_name = :action_name', action_name: action_name).
            where('users.limited_by_unit = 0 OR ubu.business_unit_id = p.business_unit_id').
            where('p.id = :prescription_id', prescription_id: prescription_id).
            select('p.id').
            distinct.
            any?
      else
        User.
            joins('INNER JOIN user_permissions AS up ON up.user_id = users.id').
            joins('INNER JOIN prescription_general_permissions AS pgp ON pgp.permission_id = up.permission_id').
            joins('INNER JOIN prescription_general_permission_actions AS pgpa ON pgpa.id = pgp.prescription_general_permission_action_id').
            joins('INNER JOIN user_options AS uo ON uo.id = users.user_option_id').
            joins('INNER JOIN prescription_statuses AS ps ON ps.id = pgp.prescription_status_id').
            joins('INNER JOIN prescriptions AS p ON p.status_id = ps.id').
            joins('LEFT JOIN prescription_shares AS pshare ON pshare.prescription_id = p.id').
            joins('LEFT JOIN prescription_permission_group_types AS ppgt ON ppgt.id = pgp.group_type_id').
            joins('LEFT JOIN user_business_units AS ubu ON ubu.user_id = users.id').
            joins('LEFT JOIN user_option_employees AS uoe ON uoe.user_id = users.id').
            where(id: user_id).
            where('pgpa.id_name = :action_name', action_name: action_name).
            where('p.id = :prescription_id', prescription_id: prescription_id).
            where("
                    (ppgt.id_name = '_full' OR uo.id_name = 'all_employees') AND (users.limited_by_unit = 0 OR ubu.business_unit_id = p.business_unit_id) OR
                    ppgt.id_name = '_by_access' AND (
                                                     (uo.id_name = 'own' OR uo.id_name = 'all_employees') AND pshare.doctor_id = users.employee_id OR
                                                     uo.id_name = 'employees_list' AND uoe.employee_id = pshare.doctor_id
                                                     ) OR
                    ppgt.id_name = '_by_doctor_prescription' AND (
                                                                  (uo.id_name = 'own' OR uo.id_name = 'all_employees') AND p.doctor_id = users.employee_id AND
                                                                  pshare.doctor_id = users.employee_id AND (users.limited_by_unit = 0 OR ubu.business_unit_id = p.business_unit_id) OR
                                                                  uo.id_name = 'employees_list' AND p.doctor_id = uoe.employee_id AND pshare.doctor_id = uoe.employee_id AND
                                                                  (users.limited_by_unit = 0 OR ubu.business_unit_id = p.business_unit_id)
                                                                  )").
            select('p.id').
            distinct.
            any?
      end
    end

  end

  def self.prescription_json_object
    {
        user_name: { only: [:id, :name] },
        medical_card: { only: [:id, :number] },
        patient: { only: [:id, :name, :pin ] },
        prescription_preliminary_icd10s: {only: [ :id, :icd10_id], include: { icd10: { only: [:id, :name, :code] }}},
        prescription_type: { only: [:id, :name, :id_name]},
        prescription_medications: {
          only: [:id, :quantity, :pharmacy_medication_id, :method_of_use, :need_recipe, :print_title_by_market_name, :recipe_number, :recipe_date, :valid_until, :dose, :form, :can_change_dose,
                 :transferred_to_paper, :transferred_count, :cancelled, :cancelled_count, :recipe_generated_by_clinic, :recipe_number],
          include: { pharmacy_medication: { only: [:id, :name] },
                     prescription_appointment_type: { only: [:id, :name] }
          },
          methods: [:get_moh_integration]
        },
        prescription_products: { only: [ :id, :code_id, :code_type, :other_business_unit, :business_unit_id, :product_id ], include: { product: { only: [:id, :name] }, code: {only: [:id, :name] } }, methods: [:business_unit]},
        status: { only: [ :id, :name, :id_name]},
        prescription_shares: { only: [ :id, :doctor_id, :created_at ] }
    }
  end

  def self.get_ncsp_lab_ncsp_elements(filter)
    sql = <<~SQL
      SELECT (CAST(id AS NVARCHAR(16)) + '_' + type) AS item_id, id, name, type
        FROM (
               SELECT id, name, 'NcspElement' AS type, code
               FROM ncsp_elements
               WHERE mark_for_deletion = 0 AND (is_group = 0 OR group_available = 1)
               UNION ALL
               SELECT id, name, 'Lab' AS type, code
               FROM labs
               WHERE mark_for_deletion = 0 AND (is_group = 0 OR group_available = 1)
              )
          AS codes
        WHERE name LIKE N'%#{filter[:name]}%'
        ORDER BY CASE WHEN type = 'NcspElement' THEN 1
                      WHEN type = 'Lab' THEN 2
                      ELSE 3 END, code, name
      OFFSET #{ (filter[:page] - 1) * filter[:page_limit] } ROWS FETCH NEXT #{ filter[:page_limit] } ROWS ONLY
    SQL

    Ncsp.find_by_sql(sql)

  end

  def self.get_business_units(filter)
    base_name = Rails.configuration.database_configuration['hr_backend']['database']
    table_name = '_' + TABLE['Справочник.Подразделения']
    code_id = filter[:code_id]
    code_table = filter[:code_type].tableize
      sql = <<~SQL
          SELECT DISTINCT business_unit_id, business_unit._Description AS name
          FROM business_unit_settings
          LEFT JOIN [#{base_name}].[dbo].[#{table_name}] AS business_unit ON convert(char(34), business_unit._IDRRef,1) = business_unit_settings.business_unit_id
          WHERE price_type_id IN (
                SELECT DISTINCT price_type_id
                FROM product_prices
                INNER JOIN products ON products.id = product_prices.product_id
                LEFT JOIN ncsps ON ncsps.id = products.ncsp_id
                LEFT JOIN labs ON labs.id = products.lab_id
                LEFT JOIN ncsp_analytics AS ncsp_many ON ncsp_many.id = products.ncsp_analytic_id
                LEFT JOIN ncsp_analytics_elements ON ncsp_many.id = ncsp_analytics_elements.ncsp_analytic_id
                LEFT JOIN ncsp_elements ON ncsp_elements.id = ncsp_analytics_elements.ncsp_element_id
                INNER JOIN (
                              SELECT MAX(record_date) AS r_date, price_type_id AS price_type, product_id AS product
                              FROM product_prices
                              WHERE record_date <= '#{filter[:prescription_date].to_s(:db)}'
                              GROUP BY price_type_id, product_id
                           ) AS a ON price_type_id = a.price_type AND product_id = a.product AND record_date = a.r_date
                WHERE products.mark_for_deletion = 0 AND #{code_table}.id = #{code_id} AND product_prices.price_not_used = 0
                ) AND business_unit._Description LIKE N'%#{filter[:name]}%'
      ORDER BY business_unit_id
      OFFSET #{ (filter[:page] - 1) * filter[:page_limit]} ROWS FETCH NEXT #{filter[:page_limit]} ROWS ONLY
      SQL

    BusinessUnitSetting.find_by_sql(sql)

  end

  def self.get_products(filter)
    code_id = filter[:code_id]
    code_table = filter[:code_type].tableize
    sql = <<~SQL
      SELECT *
      FROM products
        INNER JOIN
        (
          SELECT
            DISTINCT products.id, product_prices.price_type_id
          FROM product_prices
            INNER JOIN products ON products.id = product_prices.product_id
            LEFT JOIN ncsps ON ncsps.id = products.ncsp_id
            LEFT JOIN labs ON labs.id = products.lab_id
            LEFT JOIN ncsp_analytics AS ncsp_many ON ncsp_many.id = products.ncsp_analytic_id
            LEFT JOIN ncsp_analytics_elements ON ncsp_many.id = ncsp_analytics_elements.ncsp_analytic_id
            LEFT JOIN ncsp_elements ON ncsp_elements.id = ncsp_analytics_elements.ncsp_element_id
            INNER JOIN (
                         SELECT
                           MAX(record_date) AS r_date,
                           price_type_id AS price_type,
                           product_id AS product
                         FROM product_prices
                         WHERE record_date <= '#{filter[:prescription_date].to_s(:db)}'
                         GROUP BY price_type_id, product_id
                       ) AS a ON price_type_id = a.price_type AND product_id = a.product AND record_date = a.r_date
              WHERE products.mark_for_deletion = 0 AND #{code_table}.id = #{code_id} AND product_prices.price_not_used = 0
        ) AS priced_products ON priced_products.id = products.id
      WHERE priced_products.price_type_id IN (
        SELECT price_type_id
        FROM business_unit_settings
        WHERE business_unit_id = '#{filter[:business_unit_id]}'
      ) AND name LIKE N'%#{filter[:name]}%'
      ORDER BY products.id
      OFFSET #{ (filter[:page] - 1) * filter[:page_limit]} ROWS FETCH NEXT #{filter[:page_limit]} ROWS ONLY
    SQL

    Product.find_by_sql(sql)

  end

  def self.get_prescription_shares(prescription_id)

    base_name = Rails.configuration.database_configuration['hr_backend']['database']
    table_name_doctor = '_' + TABLE['Справочник.Сотрудники']

    data = PrescriptionShare.
        joins("LEFT JOIN [#{base_name}].[dbo].[#{table_name_doctor}] AS doctor ON convert(char(34), doctor._IDRRef,1) = prescription_shares.doctor_id").
        where(prescription_id: prescription_id).as_json(
        only: [:id, :prescription_id, :doctor_id, :created_at],
        include: {user_name: { only: [:id, :name]} },
        methods: [:doctor]
        )

    { data: data }
  end

  def self.add_prescription_share(prescription_id, doctor_ids, user_id)
    ActiveRecord::Base.transaction do
      doctor_ids.each do |id|
        next if PrescriptionShare.where(prescription_id: prescription_id, doctor_id: id).any?
        PrescriptionShare.create!(prescription_id: prescription_id, doctor_id: id, created_by: user_id)
      end
    end
    { result: 'ok' }
  rescue
    { result: 'failed' }
  end

  def self.remove_prescription_share(prescription_id, doctor_ids)
    ActiveRecord::Base.transaction do
      PrescriptionShare.where(prescription_id: prescription_id, doctor_id: doctor_ids).delete_all
    end
    { result: 'ok' }
  rescue
    { result: 'failed' }
  end

  def self.remove_prescription_share_by_id(prescription_share_id)
    ActiveRecord::Base.transaction do
      PrescriptionShare.where(id: prescription_share_id).delete_all
    end
    { result: 'ok' }
  rescue
    { result: 'failed' }
  end

  def self.check_moh_sync(prescription)
    if self.prescription_sync_done(prescription)
      # Rails.logger.warn("checking sync time ")
      seconds_to_wait = self.sync_time_left(prescription)
      if seconds_to_wait > 0
        # Rails.logger.warn("ERROR - seconds to wait #{seconds_to_wait}")
        prescription.errors[:base] << I18n.t("wait_for_sync", seconds: (seconds_to_wait.to_int).to_s)
        return  { error: prescription.errors }
      end
    else
      # Rails.logger.warn("prescription sync not done ")
      prescription.errors[:base] << I18n.t('not_all_medicament_in_sync')
      return  { error: prescription.errors }
    end
  end

  def self.check_sync(prescription)
    unless prescription.transferred_to_paper
      result = check_moh_sync(prescription)
      if result.present?
        return  { error: prescription.errors }
      end
    end

    moh_integration = IntegrationService.get_enum('moh').id
    prescription_medications = prescription.prescription_medications.where(need_recipe: 1, cancelled: 0).select('prescription_medications.*, medication_integrations.integration_status_id, medication_integrations.rec_n')
                                   .joins("LEFT JOIN medication_integrations ON medication_integrations.prescription_medication_id = prescription_medications.id AND medication_integrations.integration_service_id = #{moh_integration.to_s}")

    has_prescription_medication = false
    if prescription_medications.detect{|e| e.need_recipe}
      has_prescription_medication = true
    end
    unless has_prescription_medication
      prescription.errors[:base] << I18n.t('no_recipe_needed')

      return { error: prescription.errors }
    end

    nil
  end

  def self.transfer_to_paper(prescription, current_user)

    result =  self.check_sync(prescription)
    if result.present?
        return { error: prescription.errors } #added return errors because at the end of this method someone returned nil (instead of prescription)
    end

    integration_id = IntegrationService.find_by(id_name: 'moh').try('id')
    integration_status_id = IntegrationStatus.find_by(id_name: 'success').try('id')

    medication_for_tranfer = prescription.prescription_medications
                                 .joins("LEFT JOIN medication_integrations ON prescription_medications.id = medication_integrations.prescription_medication_id
                                                      AND integration_service_id = #{integration_id}")
                                 .where('prescription_medications.need_recipe = 1 AND prescription_medications.transferred_to_paper = 0 AND prescription_medications.cancelled = 0')
                                 .select('prescription_medications.id,
                                          prescription_medications.quantity,
                                          prescription_medications.recipe_number,
                                          prescription_medications.transferred_to_paper,
                                          prescription_medications.prescription_appointment_type_id,
                                          prescription_medications.recipe_date,
                                          prescription_medications.valid_until,
                                          medication_integrations.rec_n,
                                          medication_integrations.unid,
                                          medication_integrations.integration_status_id')
                                 .order("CASE WHEN medication_integrations.integration_status_id = #{integration_status_id} THEN 0 ELSE 1 END")

    medication_with_success = []
    medication_for_tranfer.each do |medication|
      if medication['integration_status_id'] == integration_status_id
        medication_with_success << {medicament: medication, rec_n: medication['rec_n'], unid: medication['unid']}
      else
        begin
          ActiveRecord::Base.transaction do
            PrescriptionNumerator.generate_prescription_number(prescription, medication)
            medication.update!(transferred_to_paper: true, transferred_count: medication['quantity'], recipe_generated_by_clinic: true)
            set_transferred_to_paper_in_prescription(prescription) if medication_with_success.empty?
            #call moh integration for create_prescription_copy, rabbit mq
            Integrations::Moh.call_prescription_copy(medication.id, prescription.confirmation_user_id)
          end
        rescue => e
          Rails.logger.warn("ERROR - transfer_to_paper_not_sync: #{e}")
          return { error: prescription.errors }
        end
      end
    end

    unless medication_with_success.empty?
      moh_service_result = get_recipe_from_moh(prescription, medication_with_success)

      if moh_service_result[:errs].present?
        prescription.errors[:base] << moh_service_result[:errs]
        return { error: prescription.errors }
      end

      operation_type_id = MedicamentSynchronizationOperation.find_by(id_name: 'transfer_to_paper').try('id')
      integration_status_id = IntegrationStatus.find_by(id_name: 'needed').try('id')
      integration_service_id = IntegrationService.find_by(id_name: 'moh').try('id')

      moh_service_result[:data].each_with_index do |med_el, index|
        begin
          ActiveRecord::Base.transaction do
            if med_el[:status] == 'აქტიური'
              create_attr = {
                              operation_id: operation_type_id,
                              prescription_medication: med_el[:medicament],
                              cancelled_amount: med_el[:prod_left],
                              synch_status_id: integration_status_id,
                              integration_service_id: integration_service_id,
                              doctor_id: prescription.doctor_id,
                              created_at: DateTime.server_time,
                              user: current_user
                            }
              medicament_synchronization_log = MedicamentSynchronizationLog.create!(create_attr)

              result = materialize_recipe_from_moh(prescription, med_el[:rec_n], medicament_synchronization_log)
              if result[:errs].present?
                prescription.errors[:base] << result[:errs]
                return { error: prescription.errors }
              end
            end

            prescription_medication = med_el[:medicament]
            prescription_medication.update!(transferred_to_paper: true, transferred_count: med_el[:prod_left])
            set_transferred_to_paper_in_prescription(prescription) if index == (moh_service_result[:data].length - 1)
          end
        rescue => e
          Rails.logger.warn("ERROR - transfer_to_paper_sync: #{e}")
          prescription.errors[:base] << I18n.t('error_in_moh_transfer_to_paper')
          return { error: prescription.errors }
        end
      end
    end

    nil
  end

  def self.set_transferred_to_paper_in_prescription(prescription)

    unless PrescriptionMedication.where(prescription: prescription, need_recipe: 1, transferred_to_paper: 0, cancelled: 0).any?
      prescription.update!(transferred_to_paper: true)
    end
  end

  def self.prescription_data(id, prescription_medication_ids, prescription)
    #used to be generate_medication_number

    # prescription = Prescription.find(id)
    # result =  self.check_sync(prescription)
    # unless result.nil?
    #   return result
    # end
    # sync_status = IntegrationStatus.get_enum('success').id
    moh_integration = IntegrationService.get_enum('moh').id
    prescription_medications = PrescriptionMedication.where(:id => prescription_medication_ids, cancelled: false).select('prescription_medications.*, medication_integrations.integration_status_id, medication_integrations.rec_n')
                                   .joins("LEFT JOIN medication_integrations ON medication_integrations.prescription_medication_id = prescription_medications.id AND medication_integrations.integration_service_id = #{moh_integration.to_s}")

    has_prescription_medication = false


    # if prescription_medications.detect{|e| e.integration_status_id != sync_status}
    #   prescription.errors[:base] << 'მედიკამენტის სინქრონიზაცია პროცესშია. სცადეთ რეცეპტის ბეჭდვა მოგვიანებით'
    #   return { error: prescription.errors }
    # end
    #
    if prescription_medications.detect{|e| e.need_recipe}
      has_prescription_medication = true
    end
    unless has_prescription_medication
      prescription.errors[:base] << I18n.t('no_recipe_needed')
      return { error: prescription.errors }
    end

    # ActiveRecord::Base.transaction do
    #   prescription_medications.each do |prescription_medication|
    #     PrescriptionNumerator.generate_prescription_number(prescription, prescription_medication)
    #   end
    # end

    base_name = Rails.configuration.database_configuration['hr_backend']['database']
    table_name = "_#{ TABLE['Справочник.Подразделения'] }"
    doctor_table_name  = "_#{ TABLE['Справочник.Сотрудники'] }"
    doctor_pin_column = "_#{ COLUMN['Справочник.Сотрудники.ЛичныйНомер'] }"
    description_column = COLUMN["Справочник.Подразделения.ОфициальноеНазвание"]

    prescription_info = Prescription.
          select("legal_unit._#{description_column} as legal_unit_name,
            doctor._Description as doctor_name,
            doctor.#{ doctor_pin_column } as doctor_pid,
            patients.name as patient_name,
            patients.pin as patient_pin,
            patients.birth_date as patient_birth_date
          ").
          joins('LEFT JOIN patients ON patients.id = prescriptions.patient_id').
          joins("LEFT JOIN [#{ base_name }].[dbo].[#{ table_name }] as legal_unit ON convert(char(34), legal_unit._IDRRef, 1) = prescriptions.legal_unit_id").
          joins("LEFT OUTER JOIN [#{base_name}].[dbo].[#{ doctor_table_name }] as doctor on convert(char(34), doctor._IDRRef, 1) = prescriptions.doctor_id").
          where('prescriptions.id = ?', id).first

    # updated_prescription_medications = PrescriptionMedication.where(:id => prescription_medications.ids)

    result = {
      legal_unit_name: prescription_info.legal_unit_name,
      doctor_name: prescription_info.doctor_name,
      doctor_pid: prescription_info.doctor_pid,
      patient_pin: prescription_info.patient_pin,
      patient_name: prescription_info.patient_name,
      patient_birth_date: prescription_info.patient_birth_date,
      prescription_medications: prescription_medications.as_json(:include => {
          pharmacy_medication: { only: [:id, :name, :dose, :form, :generic], methods: [:current_name]},
          prescription_appointment_type: { only: [:id, :name, :id_name] }
      }, methods: [:medication_name,  :get_moh_integration])
    }

    result
  rescue => e
    { error: prescription.errors }
  end

  def self.check_permission_and_update(prescription, user_id)

    model_changes = prescription.changed
    model_changes.delete('updated_by')

    prescription_status = PrescriptionStatus.find(prescription.status_id)
    cancel_allowed = PrescriptionsService.user_is_allowed(user_id, '_cancel', prescription.id)
    activate_allowed = PrescriptionsService.user_is_allowed(user_id, '_activate', prescription.id)
    edit_allowed = PrescriptionsService.user_is_allowed(user_id, '_edit', prescription.id)

    if model_changes.size > 1 || model_changes.size == 0 || (model_changes.size == 1 && model_changes[0] != 'status_id')
      unless edit_allowed
        return { success: false, errs: [I18n.t(:prescription_not_allow_access)], status: :forbidden }
      end

      if model_changes.include?('status_id')

        if prescription_status[:id_name] == 'canceled'
          unless cancel_allowed
            raise 'rollback'
          end
        else
          unless activate_allowed
            raise 'rollback'
          end
        end
      end

      return { success: prescription.save, prescription: prescription }
    end

    if model_changes[0].inquiry.status_id?
      ActiveRecord::Base.transaction do
        prescription.save
        what_changed = prescription.what_changed?
        if what_changed.changes.size > 1
          unless edit_allowed
            raise 'rollback'
          end
          return { success: true, prescription: prescription }
        end

        if prescription_status[:id_name] == 'canceled'
          unless cancel_allowed
            raise 'rollback'
          end
        else
          unless activate_allowed
            raise 'rollback'
          end
        end

        { success: true, prescription: prescription }
      end
    end

  rescue ActiveRecord::StaleObjectError
    prescription.reload

    { success: false, errs: [ I18n.t(:prescription_edited_locked) ], status: :internal_server_error }
  rescue ActiveRecord::RecordNotUnique
    { success: false, errs: [I18n.t('record_is_not_unique')], status: :internal_server_error}
  rescue => e
    if e.to_s == 'rollback'
      return { success: false, errs: [I18n.t(:prescription_not_allow_access)], status: :forbidden }
    else
      return { success: false, errs: [e.to_s], status: :internal_server_error }
    end
  end

  def self.prescription_sync_done(prescription)
    ActiveRecord::Base.transaction do
      status_id = IntegrationStatus.get_enum('no_need').id
      not_synced_medications = PrescriptionMedication.where(prescription_id: prescription.id).
          select('prescription_medications.*, medication_integrations.integration_status_id, medication_integrations.rec_n')
          .joins("LEFT JOIN medication_integrations
                  ON medication_integrations.prescription_medication_id = prescription_medications.id")
                                   .where("prescription_medications.need_recipe = 1 AND (medication_integrations.id IS NULL OR integration_status_id = #{status_id})")
      if not_synced_medications.present?
        # Rails.logger.warn("ERROR - not synced medications: ")
        return false
      end
      # medications = prescription.prescription_medications
      # synced = medications.inject(true) do |result, medication|
      #   is_sync = (medication.need_recipe and medication.medication_integration.present? and medication.medication_integration.integration_status != 'no_need')
      #   result and is_sync
    end
    # Rails.logger.warn("every medication is synced")
    return true

  end

  def self.sync_time_left(prescription)
    medication_integrations = MedicationIntegration.
        joins('INNER JOIN prescription_medications ON prescription_medications.id = prescription_medication_id
               INNER JOIN integration_statuses ON medication_integrations.integration_status_id = integration_statuses.id').
        where("prescription_medications.prescription_id = #{prescription.id} AND integration_statuses.id_name IN ('in_progress', 'needed')").order('medication_integrations.created_at')

    if medication_integrations.present?
      last_integration = medication_integrations.last
      last_integration_time = last_integration.created_at
      time = SystemSetting.moh_synchronize_waiting_time.try(:first).try(:number_value)
      waiting_time = time.present? ? time : 120
      end_time = last_integration_time + waiting_time.seconds
      # Rails.logger.warn("end time is: #{end_time}")
      if end_time > DateTime.server_time
        return end_time - DateTime.server_time
      end
    end
    # Rails.logger.warn("medications not present")
    return 0
  end

  def self.get_recipe_from_moh(prescription, object)

    errors = []

    integrated_user = MohIntegration.first_employee_moh_user(prescription.doctor_id)

    patient = Patient.find(prescription.patient_id)

    if integrated_user.present?
      body = {
          UserName: integrated_user.user_name,
          UserPass: integrated_user.integration_password,
          UserType: 0,
          PatientID: patient.pin,
          BDate: patient.birth_date.to_s,
          Status: 1
      }
    else
      return { errs: I18n.t('user_is_not_integrated'), status_code: 500, has_error: true }
    end

    log_object = {
        id: prescription.id,
        type: "Prescription"
    }

    result = MohIntegration.web_service(:get_recipes_by_id, body, prescription.doctor_id, log_object)
    if result[:diffgram].present? && result[:diffgram][:document_element].blank?
      return { errs: I18n.t('there_is_not_medicaments_for_patient'), status_code: 500, has_error: true}
    elsif result[:error_message]
      return { errs: result[:error_message].as_json, status_code: 500, has_error: true }
    end

    result = result[:diffgram][:document_element][:recipes]

    if result.class == Hash
      object.each do |obj|
        if obj[:unid] == result[:unid] && obj[:rec_n] == result[:recipe_no]
          obj[:prod_left] = result[:prod_left]
          obj[:status] = result[:status]
        end
      end
    elsif result.class == Array
      object.each do |obj|
        result.each do |result_obj|
          if obj[:unid] == result_obj[:unid] && obj[:rec_n] == result_obj[:recipe_no]
            obj[:prod_left] = result_obj[:prod_left]
            obj[:status] = result_obj[:status]
          end
        end
      end
    end

    object.each do |obj|
      unless obj[:prod_left].present?
        errors << I18n.t('one_of_the_medicaments_doesnt_exist')
        break
      end
    end



    if errors.present?
      { errs: errors.as_json, status_code: 500, has_error: true}
    else
      { data: object }
    end
  end

  def self.materialize_recipe_from_moh(prescription, rec_no, synch_log_object)
    integrated_user = MohIntegration.first_employee_moh_user(prescription.doctor_id)

    if integrated_user.present?
      body = {
          UserName: integrated_user.user_name,
          UserPass: integrated_user.integration_password,
          RecipeNo: rec_no
      }
    else
      return { errs: I18n.t('user_is_not_integrated'), status_code: 500, has_error: true }
    end


    synch_status_id = IntegrationStatus.find_by_id_name(:in_progress).try(:id)
    synch_log_object.update(synch_start_date: DateTime.server_time, synch_status_id: synch_status_id)

    log_object = {
        id: prescription.id,
        type: "Prescription"
    }

    result = MohIntegration.web_service(:materialize_recipe, body, prescription.doctor_id, log_object)
    if result.class == Hash
       synch_status_id = IntegrationStatus.find_by_id_name(:error).try(:id)
       error = I18n.t('moh_error_while_transferring_to_paper', error: result[:error_message] )
       synch_log_object.update(error_message: error, synch_status_id: synch_status_id )
       return { errs: error , status_code: 500, has_error: true }
    else
      result_hash = Hash.from_xml(result)
      if result_hash['Result']['Status'] == "0"
        synch_status_id = IntegrationStatus.find_by_id_name(:success).try(:id)
        synch_log_object.update(synch_date: DateTime.server_time, synch_status_id: synch_status_id )
        return { status: true , message: result_hash['Result']['Description'] }
      else
        synch_status_id = IntegrationStatus.find_by_id_name(:error).try(:id)
        synch_log_object.update(error_message: result_hash['Result']['Description'] , synch_status_id: synch_status_id )
        return { status: false, errs: result_hash['Result']['Description'] }
      end
    end
  end

  def self.disable_recipe_in_moh(prescription, rec_no, synch_log_object)
    integrated_user = MohIntegration.first_employee_moh_user(prescription.doctor_id)

    if integrated_user.present?
      body = {
          UserName: integrated_user.user_name,
          UserPass: integrated_user.integration_password,
          UserType: 0,
          RecipeNo: rec_no,
          DisableReason: 'reccured'
      }
    else
      return { errs: I18n.t('user_is_not_integrated'), status_code: 500, has_error: true }
    end


    synch_status_id = IntegrationStatus.find_by_id_name(:in_progress).try(:id)
    synch_log_object.update(synch_start_date: DateTime.server_time, synch_status_id: synch_status_id)

    log_object = {
        id: prescription.id,
        type: "Prescription"
    }

    result = MohIntegration.web_service(:disable_recipe, body, prescription.doctor_id, log_object)
    if result.class == Hash
      synch_status_id = IntegrationStatus.find_by_id_name(:error).try(:id)
      error = I18n.t('moh_error_while_transferring_to_paper', error: result[:error_message] )
      synch_log_object.update(error_message: error, synch_status_id: synch_status_id )
      return { errs: error , status_code: 500, has_error: true }
    else
      result_hash = Hash.from_xml(result)
      if result_hash['Result']['Status'] == "0"
        synch_status_id = IntegrationStatus.find_by_id_name(:success).try(:id)
        synch_log_object.update(synch_date: DateTime.server_time, synch_status_id: synch_status_id )
        return { status: true , message: result_hash['Result']['ResultValue'] }
      else
        synch_status_id = IntegrationStatus.find_by_id_name(:error).try(:id)
        synch_log_object.update(error_message: result_hash['Result']['ResultValue'] , synch_status_id: synch_status_id )
        return { status: false, errs: result_hash['Result']['ResultValue'] }
      end
    end
  end

  def self.disable_recipe_in_gepha(prescription, recipe_no, synch_log_object)

    doctor = Employee1c.employee_info(prescription.doctor_id)


    body = {
        receptis_nomeri: recipe_no,
        eq_k: 0,
        eq_pn: doctor.pin
    }

    synch_status_id = IntegrationStatus.find_by_id_name(:in_progress).try(:id)
    synch_log_object.update(synch_start_date: DateTime.server_time, synch_status_id: synch_status_id)

    log_object = {
        id: prescription.id,
        type: "Prescription",
        doctor_id: prescription.doctor_id
    }

    result = GephaIntegration.web_service(:er_recc_gauq_wb, body, log_object)

    if result == "ok"
      synch_status_id = IntegrationStatus.find_by_id_name(:success).try(:id)
      synch_log_object.update(synch_date: DateTime.server_time, synch_status_id: synch_status_id )
      return { status: true , message: result }
    else
      synch_status_id = IntegrationStatus.find_by_id_name(:error).try(:id)
      error = I18n.t('moh_error_while_transferring_to_paper', error: result.class == Hash ? result[:error_message] : nil )
      synch_log_object.update(error_message: result.class == Hash ? error : result, synch_status_id: synch_status_id )
      return { errs: error , status_code: 500, has_error: true }
    end
  end

  def self.get_user_employees(user)
    UserOptionEmployee.select(:employee_id).where(user_id: user.id)
  end

  def self.collect_all_users(user_id)
    user = User.find_by_id(user_id)
    case user.user_option.id_name
      when 'employees_list'
        get_user_employees(user).pluck(:employee_id)
      when 'all_employees'
        nil
      else [user.employee_id]
    end
  end

  def self.is_user_eligible_doctor(user_id, doctor_id)
    my_users = collect_all_users(user_id)
    if my_users.nil?
      return true
    end
    return my_users.include? doctor_id
  end

  def self.manual_cancel(prescription, prescription_medication, current_user)

    return { success: false, errs: [I18n.t(:prescription_medication_is_cancelled)], status_code: 500 } if prescription_medication.cancelled
    return { success: false, errs: [I18n.t(:prescription_not_allow_access)], status_code: 500 } unless user_has_permission_manual_cancel(prescription, prescription_medication, current_user)

    result =  self.check_moh_sync(prescription)
    if result.present?
      return result
    end

    moh_integration = prescription_medication.get_moh_integration
    cancelled_count = prescription_medication.quantity
    if prescription_medication.transferred_to_paper
      cancelled_count = prescription_medication.transferred_count
    elsif moh_integration.present? && moh_integration.id_name == 'success'
      medication_with_success = [{medicament: prescription_medication, rec_n: moh_integration['rec_n'], unid: moh_integration['unid']}]
      moh_service_result = get_recipe_from_moh(prescription, medication_with_success)

      if moh_service_result[:errs].present?
        moh_service_result[:success] = false
        return moh_service_result
      end

      if moh_service_result[:data][0][:status] == 'აქტიური'
        operation_type_id = MedicamentSynchronizationOperation.find_by(id_name: 'cancellation').try('id')
        integration_status_id = IntegrationStatus.find_by(id_name: 'needed').try('id')
        integration_service_id = IntegrationService.find_by(id_name: 'moh').try('id')

        create_attr = {
            operation_id: operation_type_id,
            prescription_medication: prescription_medication,
            cancelled_amount: moh_service_result[:data][0][:prod_left],
            synch_status_id: integration_status_id,
            integration_service_id: integration_service_id,
            doctor_id: prescription.doctor_id,
            created_at: DateTime.server_time,
            user: current_user
        }
        medicament_synchronization_log = MedicamentSynchronizationLog.create!(create_attr)

        result = disable_recipe_in_moh(prescription, moh_service_result[:data][0][:rec_n], medicament_synchronization_log)
        if result[:errs].present?
          result[:success] = false
          return result
        end
      end

      cancelled_count = moh_service_result[:data][0][:prod_left]
    end

    prescription_medication.update!(cancelled: true, cancelled_count: cancelled_count)
    Thread.new { PrescriptionsService.manual_cancel_gepha(prescription, prescription_medication, current_user) }

    return { success: true, cancelled: true, cancelled_count: cancelled_count }
  end

  def self.user_has_permission_manual_cancel(prescription, prescription_medication, current_user)

    return false if prescription.status.nil? || prescription.status.id_name != 'printed' || !is_user_eligible_doctor(current_user.id, prescription.doctor_id)

    sql = <<~SQL
            SELECT
              DISTINCT
              p.id AS prescription_id
            FROM users
              INNER JOIN user_permissions AS up ON up.user_id = users.id
              INNER JOIN prescription_general_permissions AS pgp ON pgp.permission_id = up.permission_id
              INNER JOIN prescription_general_permission_actions AS pgpa
                ON pgpa.id = pgp.prescription_general_permission_action_id
              CROSS JOIN prescriptions AS p
              LEFT JOIN user_business_units AS ubu ON ubu.user_id = users.id
            WHERE users.id = #{current_user.id} AND pgpa.id_name = '_manual_cancel' AND ( users.limited_by_unit = 0 OR ubu.business_unit_id = p.business_unit_id ) AND p.id = #{prescription.id}
          SQL

    Prescription.find_by_sql(sql).any?

  end

  def self.manual_cancel_gepha(prescription, prescription_medication, current_user)

    gepha_integration = prescription_medication.get_gepha_integration
    return if gepha_integration.nil? || gepha_integration.id_name != 'success'

    operation_type_id = MedicamentSynchronizationOperation.find_by(id_name: 'cancellation').try('id')
    integration_status_id = IntegrationStatus.find_by(id_name: 'needed').try('id')
    integration_service_id = IntegrationService.find_by(id_name: 'GEPHA').try('id')

    create_attr = {
        operation_id: operation_type_id,
        prescription_medication: prescription_medication,
        cancelled_amount: prescription_medication.cancelled_count,
        synch_status_id: integration_status_id,
        integration_service_id: integration_service_id,
        doctor_id: prescription.doctor_id,
        created_at: DateTime.server_time,
        user: current_user
    }
    medicament_synchronization_log = MedicamentSynchronizationLog.create!(create_attr)
    disable_recipe_in_gepha(prescription, gepha_integration.unid, medicament_synchronization_log)

  end
end