text: from datetime import datetime
  from app import db
  from app.models import PrArrearsMatrix, PrBatch, PrCharge, PrHistory, Rent
  from app.modeltypes import PrDeliveryTypes
  from app.dao.database import commit_to_database
  from sqlalchemy import desc, exists, and_, select
  from sqlalchemy.orm import load_only, joinedload
  
  
  def post_pr_batch(runcode, size, status_id, is_account):
      pr_batch = PrBatch(time_date=datetime.now(), code=runcode, size=size, status_id=status_id, is_account=is_account)
      db.session.add(pr_batch)
      commit_to_database()
      return pr_batch
  
  
  def post_pr_batch_empty():
      pr_batch = PrBatch(time_date=datetime.now(), code="", size=0, status_id=0, is_account=0)
      db.session.add(pr_batch)
      db.session.flush()
      batch_id = pr_batch.id
      commit_to_database()
      return batch_id
  
  
  def add_pr_charge(pr_id, charge_id, case_created):
      pr_charge = PrCharge()
      pr_charge.id = pr_id
      pr_charge.charge_id = charge_id
      pr_charge.case_created = case_created
      db.session.add(pr_charge)
  
  
  def add_pr_history(pr_history):
      db.session.add(pr_history)
      db.session.flush()
      return pr_history.id
  
  
  def get_batch_email_pay_requests(batch_id):
      return db.session.query(PrHistory).filter(and_(PrHistory.batch_id == batch_id, PrHistory.delivery_method != 2)) \
          .options(load_only('time_date', 'summary', 'block', 'rent_date', 'total_due', 'delivery_method', 'delivered'),
                   joinedload('rent').load_only('rentcode', 'email')).all()
  
  
  def get_batch_pay_requests(batch_id):
      return db.session.query(PrHistory).filter_by(batch_id=batch_id) \
          .options(load_only('time_date', 'summary', 'rent_date', 'total_due', 'delivery_method', 'delivered'),
                   joinedload('rent').load_only('rentcode', 'email')).all()
  
  
  def get_batch_pay_request_statuses(batch_id):
      return db.session.query(PrHistory.delivered).filter_by(batch_id=batch_id).all()
  
  
  def get_batch_pr_post_html(batch_id):
      return db.session.query(PrHistory).filter_by(batch_id=batch_id).filter(PrHistory.delivery_method != 1) \
          .options(load_only('rent_id', 'block', 'delivery_method')).all()
  
  
  def get_last_arrears_level(rent_id):
      return db.session.query(PrHistory.arrears_level).filter_by(rent_id=rent_id).order_by(-PrHistory.id).first()
  
  
  def check_pr_exists(rent_id):
      return db.session.query(exists().where(and_(PrHistory.rent_id == rent_id, PrHistory.is_account == 0))).scalar()
  
  
  def get_pr_batch(batch_id):
      return PrBatch.query.get(batch_id)
  
  
  def get_pr_batches():
      return PrBatch.query.order_by(-PrBatch.time_date).all()
  
  
  def get_pr_batches_incomplete():
      stmt = select(PrBatch).filter_by(status_id=0).order_by(-PrBatch.time_date)
      return db.session.execute(stmt).scalars().all()
  
  
  def get_pr_batches_complete():
      stmt = select(PrBatch).filter_by(status_id=1).order_by(-PrBatch.time_date)
      return db.session.execute(stmt).scalars().all()
  
  
  def get_pr_block(pr_id):
      return db.session.query(PrHistory).filter_by(id=pr_id).with_entities(PrHistory.block).scalar()
  
  
  def get_pr_charge(pr_id):
      return PrCharge.query.filter_by(id=pr_id).one_or_none()
  
  
  def get_pr_file(pr_id):
      stmt = select(PrHistory).options(load_only('block', 'rent_date', 'summary', 'time_date', 'total_due'),
                                       joinedload('rent'))
      return db.session.execute(stmt.filter(PrHistory.id == pr_id)).scalar_one_or_none()
  
  
  def get_pr_history(rent_id):
      stmt = select(PrHistory).filter_by(rent_id=rent_id).order_by(desc(PrHistory.time_date))
      return db.session.execute(stmt).scalars().all()
  
  
  def get_pr_history_without_body(rent_id):
      stmt = select(PrHistory).options(load_only('rent_date', 'summary', 'time_date', 'is_account'))\
          .filter_by(rent_id=rent_id).order_by(desc(PrHistory.time_date))
      return db.session.execute(stmt).scalars().all()
  
  
  def get_last_pr_history_event(rent_id):
      stmt = select(PrHistory).options(load_only('rent_date', 'summary', 'time_date', 'is_account'))\
          .filter_by(rent_id=rent_id).order_by(desc(PrHistory.time_date))
      return db.session.execute(stmt).scalars().first()
  
  
  def get_pr_history_row(pr_id):
      return db.session.get(PrHistory, pr_id)
  
  
  def get_pr_ids_from_batch(batch_id):
      return db.session.query(PrHistory).filter_by(batch_id=batch_id).options(load_only('id')).all()
  
  
  def get_recovery_info(suffix):
      recovery_info = db.session.query(PrArrearsMatrix).filter_by(suffix=suffix).options(load_only('arrears_clause',
                                                                                                   'recovery_charge',
                                                                                                   'create_case')). \
          one_or_none()
      return recovery_info.arrears_clause, recovery_info.create_case, recovery_info.recovery_charge
  
  
  def get_recovery_info_x(suffix):
      recovery_info = db.session.query(PrArrearsMatrix).filter_by(suffix=suffix).options(load_only('recovery_charge',
                                                                                                   'create_case')). \
          one_or_none()
      create_case = recovery_info.create_case
      recovery_charge = recovery_info.recovery_charge
      return create_case, recovery_charge
  
  
  def post_emailed_payrequest_delivery(pr_file):
      rent_id = pr_file.rent_id
      # email delivery - set to delivered
      if pr_file.delivery_method == 1:
          pr_file.delivered = 2
      # email and post delivery - not posted - set to email only
      if pr_file.delivery_method == 3 and pr_file.delivered == 1:
          pr_file.delivered = 3
      # email and post delivery - already posted - set to delivered
      if pr_file.delivery_method == 3 and pr_file.delivered == 4:
          pr_file.delivered = 2
      commit_to_database()
      return rent_id
  
  
  def post_posted_payrequest_delivery(pr_file):
      rent_id = pr_file.rent_id
      # post delivery - set to delivered
      if pr_file.delivery_method == 2:
          pr_file.delivered = 2
      # email and post delivery - not emailed - set to post only
      if pr_file.delivery_method == 3 and pr_file.delivered == 1:
          pr_file.delivered = 4
      # email and post delivery - already emailed - set to delivered
      if pr_file.delivery_method == 3 and pr_file.delivered == 3:
          pr_file.delivered = 2
      commit_to_database()
      return rent_id
  
  
  def post_updated_payrequest(block, pr_id):
      pr_history = db.session.get(PrHistory, pr_id)
      rent_id = pr_history.rent_id
      pr_history.block = block
      commit_to_database()
      return rent_id
  
  
  def post_updated_payrequest_delivery(delivered, pr_file):
      rent_id = pr_file.rent_id
      pr_file.delivered = delivered
      commit_to_database()
      return rent_id
  
  
  def prepare_new_pr_history_entry(pr_history_data, rent_id, method=1, batch_id=None, is_account=False):
      summary = pr_history_data.get('pr_code') + "-" + PrDeliveryTypes.get_name(method) + "-" + pr_history_data.get(
          'mailaddr')[0:25]
      pr_history = PrHistory(block=pr_history_data.get('block').replace("£", "&pound;"), rent_id=rent_id,
                             summary=summary, time_date=datetime.now(), is_account=is_account,
                             rent_date=datetime.strptime(pr_history_data.get('rent_date'), '%Y-%m-%d'),
                             total_due=pr_history_data.get('tot_due'),
                             arrears_level=pr_history_data.get('new_arrears_level'),
                             delivery_method=method, delivered=1, batch_id=batch_id)
      return pr_history
  
  
  def subtract_from_batch_size(batch_id):
      batch = db.session.get(PrBatch, batch_id)
      batch.size = batch.size - 1
  
  
  def update_batch_size(pr_batch, size):
      pr_batch.size = size
      commit_to_database()
  
  
  def update_batch_status(batch_id, status_id):
      pr_batch = db.session.get(PrBatch, batch_id)
      pr_batch.status_id = status_id
      commit_to_database()
  
  
  def update_pr_batch(batch_id, runcode, size, status_id, is_account):
      pr_batch = db.session.get(PrBatch, batch_id)
      pr_batch.time_date = datetime.now()
      pr_batch.code = runcode
      pr_batch.size = size
      pr_batch.status_id = status_id
      pr_batch.is_account = is_account
      commit_to_database()
      return pr_batch
  