import os
import io
import hashlib
import datetime
import json
from functools import lru_cache
import numpy as np
from PIL import Image
from dbmodel import *
import pydicom
import pydicom.pixel_data_handlers.pillow_handler as pillow_handler
import pydicom.pixel_data_handlers.numpy_handler as np_handler
import pydicom.pixel_data_handlers.pylibjpeg_handler as pylibjpeg_handler
import pydicom.pixel_data_handlers.rle_handler as rle_handler
from pydicom.pixel_data_handlers import apply_windowing
from pydicom.pixel_data_handlers.util import apply_voi_lut
from pydicom.encaps import defragment_data, decode_data_sequence
from pydicom.filereader import dcmread
from pynetdicom import AE, StoragePresentationContexts, build_context
from transliterate import translit
import requests
import time
from typing import BinaryIO
import redis

import logging
LOGGER = logging.getLogger('sampacs')

from config import config
dbconfig = config.mysql
from pydicom.dataelem import DataElement

# Redis connection
_redis_client = None

def get_redis_client():
    """Get or create Redis client."""
    global _redis_client

    if _redis_client is None:
        _redis_client = redis.Redis(
            host=os.environ.get('REDIS_HOST', 'localhost'),
            port=int(os.environ.get('REDIS_PORT', 6379)),
            db=0,
            decode_responses=False,
            socket_connect_timeout=5,
            socket_timeout=5,
            retry_on_timeout=True
        )
        # Test connection
        _redis_client.ping()

    return _redis_client

encodingTag = DataElement(0x00080005, 'CS', 'ISO_IR 192')

def canBeDecompressedInFrontend(uid):
    return uid in ['1.2.840.10008.1.2.4.50', '1.2.840.10008.1.2.4.70']

def formatPatientName(client):
    return client.lastName + '^%s'%(client.firstName[0] if client.firstName else '') + '^%s'%(client.patrName[0] if client.patrName else '')

def formatShortName(lastName, firstName, patrName):
    return ('^'.join([lastName.strip(), firstName.strip()[:1], patrName.strip()[:1]]))

def formatFullName(lastName, firstName, patrName):
    return ('^'.join([lastName.strip(), firstName.strip(), patrName.strip()]))

def formatSex(sex):
    if sex == 1:
        return 'M'
    elif sex == 2:
        return 'F'
    else:
        return 'U'

def getClient(patientId, modality):
    if modality and modality.snilsAsId:
        return Client.get_or_none(Client.SNILS == patientId)
    else:
        return Client.get_or_none(Client.id == int(patientId)) if patientId.isdigit() else None

def prepareDataset(ds, registrateOptions = [], modality = None):
    if not 'PatientID' in ds:
        LOGGER.error('REG      empty PatientID tag')
        return (ds, 'empty PatientID tag')
    translitLang = modality.translitLang if modality else 'ru'
    saveTags = modality.saveTags if modality else {}
    if modality and modality.accessionWorkaround:
        tagValue = getTagValue(modality.accessionWorkaround, ds)
        LOGGER.debug(f'REG      accession workaround. {modality.accessionWorkaround} {tagValue}')
        if tagValue and '_' in tagValue:
            patientId = tagValue.split('_')
            ds.PatientID = patientId[0]
            ds.AccessionNumber = patientId[1]
            LOGGER.debug(f'REG      patientID changed for {patientId[0]}, accessionNumber changed for {patientId[1]}')
    if 'translitClientInfo' in registrateOptions:
            oldName = ds.PatientName
            ds.PatientName = translit(str(ds.PatientName), translitLang)
            LOGGER.debug(f'REG      patient name transliterated from {oldName} to {ds.PatientName}')
    if registrateOptions:
        if 'updatePatientprefix' in registrateOptions:
            newPatId = '111' + ds.PatientID
            LOGGER.info('REG      patient id changed from %s to %s'%(ds.PatientID, newPatId))
            ds.PatientID = newPatId
            ds.StudyInstanceUID = ds.StudyInstanceUID + '111'
            ds.SeriesInstanceUID = ds.SeriesInstanceUID + '111'
            ds.SOPInstanceUID = ds.SOPInstanceUID + '111'
            return (ds, 'error')
    return (ds, False)

def parseSequence(ds, values, roundValue, conceptCode = ''):
    if ds.get('ValueType', '') == 'CONTAINER':
        conceptCodeSeq = ds.get('ConceptNameCodeSequence', [None])[0]
        conceptCode = ' '.join([conceptCode, conceptCodeSeq.CodeMeaning]).strip() if conceptCodeSeq else conceptCode
        for seq in ds.get('ContentSequence', []):
            if seq.get('ValueType', '') == 'CODE':
                conceptNameSeq = seq.get('ConceptNameCodeSequence', [None])[0]
                codeName = conceptNameSeq.CodeMeaning if conceptNameSeq else ''
                conceptCodeSeq = seq.get('ConceptCodeSequence', [None])[0]
                conceptCode = conceptCodeSeq.CodeMeaning if conceptCodeSeq and codeName != 'Subject Sex' else ''
            parseSequence(seq, values, roundValue, conceptCode)
    if ds.get('ValueType', '') == 'NUM':
        measure = ds.get('MeasuredValueSequence', [None])[0]
        code = ds.get('ConceptNameCodeSequence', [None])[0]
        method = getMeasurementMethod(ds)
        if code and 'CodeMeaning' in code and measure and 'NumericValue' in measure:
            val = float(measure.NumericValue)
            values[' '.join(filter(None, [conceptCode, code.CodeMeaning, method]))] = str(f"{val:.{roundValue}f}")
    if ds.get('ValueType', '') == 'TEXT':
        code = ds.get('ConceptNameCodeSequence', [None])[0]
        if code and 'CodeMeaning' in code and 'TextValue' in ds:
            values[' '.join(filter(None, [conceptCode, code.CodeMeaning]))] = ds.TextValue

def parseSequenceElectron(ds, values, roundValue, conceptCode = ''):
    if ds.get('ValueType', '') == 'CONTAINER':
        for seq in ds.get('ContentSequence', []):
            parseSequenceElectron(seq, values, roundValue, conceptCode)
    if ds.get('ValueType', '') == 'TEXT':
        if ds.TextValue[:9] == "Описание:":
            values['Описание'] = ds.TextValue[9:].strip()
        if ds.TextValue[:11] == "Заключение:":
            values['Заключение'] = ds.TextValue[11:].strip()


def getMeasurementMethod(ds):
    method = None
    for seq in ds.get('ContentSequence', []):
        if seq.get('ValueType', '') == 'CODE':
            conceptNameSeq = seq.get('ConceptNameCodeSequence', [None])[0]
            codeName = conceptNameSeq.CodeMeaning if conceptNameSeq else ''
            if codeName == "Measurement Method":
                conceptCodeSeq = seq.get('ConceptCodeSequence', [None])[0]
                method = conceptCodeSeq.CodeMeaning if conceptCodeSeq else None
                break
    return method


def savePropertyValue(action, aptName, value, separator):
    LOGGER.info('PROP     saving property "%s" with value "%s" for action %i'%(aptName, value, action.id))
    prop = (ActionProperty_String
                        .select(ActionProperty_String)
                        .join(ActionProperty, on=(ActionProperty.id == ActionProperty_String.id))
                        .join(ActionPropertyType)
                        .where( ActionProperty.action_id == action.id,
                                ActionProperty.deleted == 0,
                                ActionPropertyType.shortName == aptName
                              )
                        .first())
    if prop:
        LOGGER.info('PROP     found property %i with val "%s", adding'%(prop.id, prop.value))
        prop.value = f'{prop.value}{separator}{value}' if prop.value else value
        prop.save()
    else:
        apt = ActionPropertyType.get_or_none(ActionPropertyType.shortName == aptName
                                            , ActionPropertyType.actionType_id == action.actionType_id
                                            , ActionPropertyType.deleted == 0
                                            )
        if apt:
            ap = ActionProperty()
            ap.action_id = action.id
            ap.type_id = apt.id
            ap.save()
            prop = ActionProperty_String()
            prop.value = value
            prop.id = ap.id
            prop.save(force_insert=True)
        else:
            LOGGER.warning('PROP     ActionPropertyType not found. shortName = %s, type_id = %i, deleted = 0'%(aptName,action.actionType_id))

def saveFile(ds, aetitle, saveFile = True):
    hash = ''
    file_size = 0
    if saveFile:
        hash_md5 = hashlib.md5()
        studyMonth = ds.StudyDate[:6]
        filename = ds.SOPInstanceUID + '.dcm'
        uidhash = hashlib.md5(ds.SOPInstanceUID.encode('utf8')).hexdigest()[:2]
        dir = os.path.join(config.storageDir, studyMonth, uidhash)
        os.makedirs(dir, exist_ok=True)
        try:
            ds.save_as(os.path.join(dir, filename), write_like_original = False)
        except Exception as e:
            LOGGER.error('FILE     save error: %s'%str(e))
            ds.save_as(os.path.join(dir, filename), write_like_original = True)
        with open(os.path.join(dir, filename), "rb") as f:
            for chunk in iter(lambda: f.read(4096), b""):
                hash_md5.update(chunk)
        hash = hash_md5.hexdigest()
        # Получаем размер DICOM файла
        file_size = os.path.getsize(os.path.join(dir, filename))
        if ds.Modality not in ['SR'] and 'PixelData' in ds and ds.PixelData:
            try:
                previewdir = os.path.join(config.storageDir, studyMonth, 'preview')
                os.makedirs(previewdir, exist_ok=True)
                jpg_path = os.path.join(previewdir, ds.SeriesInstanceUID + '.jpg')
                # Generate preview only if it doesn't exist yet
                if not os.path.exists(jpg_path):
                    image = toImage(ds, 0)
                    if image:
                        image.save(jpg_path, format='JPEG', quality=50)
                        # Получаем размер JPEG файла
                        file_size += os.path.getsize(jpg_path)


            except:
                LOGGER.error('FILE     error while rendering preview for %s'%os.path.join(dir, filename))

    saveIndex(ds, hash, aetitle, file_size)

def toImage(ds, index = 0):
    image = None
    if pillow_handler.supports_transfer_syntax(
            ds.file_meta.TransferSyntaxUID) or pylibjpeg_handler.supports_transfer_syntax(
            ds.file_meta.TransferSyntaxUID):
        if getattr(ds, 'NumberOfFrames', 1) > 1:
            pixel_data = decode_data_sequence(ds.PixelData)[index]
        else:
            pixel_data = defragment_data(ds.PixelData)
        image = Image.open(io.BytesIO(pixel_data))
    elif np_handler.supports_transfer_syntax(ds.file_meta.TransferSyntaxUID):
        if getattr(ds, 'NumberOfFrames', 1) > 1:
            pixelArray = ds.pixel_array[index]
        else:
            pixelArray = ds.pixel_array
        if "VOILUTSequence" in ds and ds.VOILUTSequence:
            pixelArray = apply_voi_lut(pixelArray, ds)
        pixelArray = apply_windowing(pixelArray, ds)

        # Handle photometric interpretation
        photometric_interpretation = getattr(ds, 'PhotometricInterpretation', 'MONOCHROME2')
        if photometric_interpretation == 'MONOCHROME1':
            # Invert for MONOCHROME1 (min is white, max is black)
            pixelArray = np.max(pixelArray) - pixelArray

        # Normalize to 0-255
        pixelArray = (np.maximum(pixelArray, 0) / pixelArray.max()) * 255.0
        pixelArray = np.uint8(pixelArray)
        image = Image.fromarray(pixelArray)
    return image


#@pacs_db.atomic()
def saveIndex(ds, md5 = '', aetitle = None, file_size = 0):
    if pacs_db.is_closed():
        pacs_db.connect()
    try:
        p = Patient.get_or_none(Patient.id == ds.PatientID)
        if not p:
            try:
                bdate = datetime.datetime.strptime(ds.PatientBirthDate, "%Y%m%d")
            except:
                bdate = None
            p = Patient()
            p.id = ds.PatientID
            p.name = ds.PatientName
            p.birthday = bdate
            p.sex = ds.PatientSex
            p.save(force_insert=True)

        st = Study.get_or_none(Study.uid == ds.StudyInstanceUID)
        if not st:
            st = Study()
            st.uid = ds.StudyInstanceUID
            st.id = getTagValue('StudyID', ds)
            st.nodeCode = config.nodeCode
            st.date = datetime.datetime.strptime(ds.StudyDate, "%Y%m%d")
            st.time = getTagValue('StudyTime', ds)
            st.accession_number = getTagValue('AccessionNumber', ds)
            st.creator_aetitle = aetitle
            st.manufacturer_model = ds.Manufacturer if 'Manufacturer' in ds else None
            st.description = ds.StudyDescription if 'StudyDescription' in ds else None
            st.patient = ds.PatientID
            st.save(force_insert=True)

        try:
            Series[ds.SeriesInstanceUID]
        except DoesNotExist:
            s = Series()
            s.uid = ds.SeriesInstanceUID
            s.modality = ds.Modality
            number = getTagValue('SeriesNumber', ds)
            s.number = number if number else 0
            s.description = ds.SeriesDescription if 'SeriesDescription' in ds else None
            s.date = getTagValue('SeriesDate', ds)
            s.time = getTagValue('SeriesTime', ds)
            s.study = ds.StudyInstanceUID
            s.size = file_size
            s.save(force_insert=True)
        else:
            # Update series size
            s = Series[ds.SeriesInstanceUID]
            s.size += file_size
            s.save()

        try:
            Instance[ds.SOPInstanceUID]
        except DoesNotExist:
            i = Instance()
            i.uid = ds.SOPInstanceUID
            i.study_date = datetime.datetime.strptime(ds.StudyDate, "%Y%m%d")
            number = getTagValue('InstanceNumber', ds)
            i.number = number if number else 0
            i.frames = getattr(ds, 'NumberOfFrames', 1)
            i.sopclass = ds.SOPClassUID
            i.md5 = md5
            i.series = ds.SeriesInstanceUID
            if ds.Modality not in ['SR', 'PR'] and 'PixelData' in ds and ds.PixelData:
                i.bits_stored = getTagValue('BitsStored', ds)
                i.high_bit = getTagValue('HighBit', ds)
                i.image_laterality = ds.ImageLaterality if 'ImageLaterality' in ds else None
                i.patient_orientation = getTagValue('PatientOrientation', ds)
                i.photometric_interpretation = ds.PhotometricInterpretation if 'PhotometricInterpretation' in ds else None
                i.pixel_representation = ds.PixelRepresentation if 'PixelRepresentation' in ds else None
                i.pixel_spacing = getTagValue('PixelSpacing', ds)
                i.rescale_intercept = ds.RescaleIntercept if 'RescaleIntercept' in ds else None
                i.rescale_slope = ds.RescaleSlope if 'RescaleSlope' in ds else None
                i.rows = ds.Rows
                i.columns = ds.Columns
                i.view_position = ds.ViewPosition if 'ViewPosition' in ds else None
                i.window_center = getTagValue('WindowCenter', ds)
                i.window_width = getTagValue('WindowWidth', ds)
                i.transfer_syntax = ds.file_meta.TransferSyntaxUID if 'TransferSyntaxUID' in ds.file_meta else "1.2.840.10008.1.2"
                i.slice_location = getTagValue('SliceLocation', ds)
                i.slice_thickness = getTagValue('SliceThickness', ds)
                i.image_orientation_patient = getTagValue('ImageOrientationPatient', ds)
                i.image_position_patient = getTagValue('ImagePositionPatient', ds)
            i.size = file_size
            i.save(force_insert=True)

            # Update study size
            st = Study[ds.StudyInstanceUID]
            st.size += file_size
            st.save()
    except:
        pacs_db.close()
        raise
    pacs_db.close()


def getTagValue(tagName: str, ds: pydicom.Dataset) -> str:
    if tagName not in ds:
        return None
    tag = ds.get(tagName)
    if type(tag) == pydicom.multival.MultiValue:
        return '\\'.join([str(x) for x in tag])
    else:
        return str(tag)

def getImage(instanceUid: str, studyDate: str, nodeCode: str, buffer: bool = False) -> pydicom.Dataset | BinaryIO:
    """Получение изображения из хранилища. Изображение хешируется с помощью lru_cache

        Parameters
        ----------
            instanceUid : str
                UID
            studyDate : str
                Дата исследования
            nodeCode: str
                Код сервиса.

        Returns
        -------
            pydicom.Dataset
                pydicom изображение
    """
    uidhash = hashlib.md5(instanceUid.encode('utf8')).hexdigest()[:2]
    if nodeCode != config.nodeCode:
        node = config.nodes.get(nodeCode, None)
        if not node:
            LOGGER.error('GETIMG   node %s not found in config'%nodeCode)
            #abort(500, 'node %s not found in config'%instance.series.study.nodeCode)
            return None
        url = "http://%s/images/%s/%s/%s.dcm"%(node.address, studyDate, uidhash, instanceUid)
        LOGGER.debug('GETIMG   getting image from %s'%url)
        r = requests.get(url, stream=True)
        b = io.BytesIO(r.content)
        if buffer:
            return b
        ds = dcmread(b, force=True)
    else:
        file_path = os.path.join(config.storageDir, studyDate, uidhash, instanceUid + '.dcm')
        LOGGER.debug('GETIMG   getting image from %s'%file_path)
        if buffer:
            b = io.BytesIO(open(file_path, 'rb').read())
            return b
        ds = dcmread(file_path, force=True)
    return ds

def getPreview(seriesID, studyDate, nodeCode):
    if nodeCode != config.nodeCode:
        node = config.nodes.get(nodeCode, None)
        if not node:
            LOGGER.error('GETIMG   node %s not found in config'%nodeCode)
            #abort(500, 'node %s not found in config'%instance.series.study.nodeCode)
            return None
        url = "http://%s/images/%s/preview/%s.jpg"%(node.address, studyDate, seriesID)
        LOGGER.debug('GETIMG   getting preview from %s'%url)
        r = requests.get(url, stream=True)
        b = io.BytesIO(r.content)
        return b
    else:
        file_path = os.path.join(config.storageDir, studyDate, 'preview', seriesID + '.jpg')
        LOGGER.debug('GETIMG   getting preview from %s'%file_path)
        b = io.BytesIO(open(file_path, 'rb').read())
        return b
    return ds

def is_time_between(begTime, endTime, checkTime=None):
    checkTime = checkTime or datetime.datetime.now().time()
    if begTime <= endTime:
        return checkTime >= begTime and checkTime < endTime
    else:
        return checkTime >= begTime or checkTime < endTime

def thread_send_images():
    while True:
        if is_time_between(datetime.time(config.sendStartHour, 0), datetime.time(config.sendEndHour, 0)):
            try:
                pacs_db.connect()
                failedDestList = []
                studyMap = {}
                needBreak = True
                while is_time_between(datetime.time(config.sendStartHour, 0), datetime.time(config.sendEndHour, 0)):
                    for slog in SendLog.select().where(SendLog.sent.is_null()).limit(1000):
                        needBreak = False
                        if slog.destination in failedDestList:
                            continue
                        try:
                            instance = Instance.select(Instance, Study.uid, Study.nodeCode, Study.creator_aetitle).join(Series).join(Study).where(
                                Instance.uid == slog.instance_uid).get()
                            if config.autorouteSelfNode and instance.series.study.nodeCode != config.nodeCode:
                                continue
                            try:
                                ds = getImage(slog.instance_uid, instance.study_date.strftime("%Y%m"),
                                              instance.series.study.nodeCode)
                            except Exception as e:
                                LOGGER.error('SEND     image for instance uid "%s" not found. %s' % (slog.instance_uid, e))
                                slog.delete_instance()
                            else:
                                if not instance.series.study.uid in studyMap:
                                    LOGGER.info(f'SEND     sending study uid {instance.series.study.uid} to {slog.destination}')
                                    studyMap[instance.series.study.uid] = 1
                                if sendImage(slog.destination, ds, instance.series.study.creator_aetitle):
                                    slog.sent = fn.now()
                                    slog.save()
                                    if config.exportSystem:
                                        if ds.AccessionNumber.isdigit():
                                            samson_db.connect()
                                            if isActionExists(ds.AccessionNumber, ds.PatientID) and not existsExportRecord(ds.AccessionNumber, config.exportSystem):
                                                exportRecord = ActionExport()
                                                exportRecord.system_id = config.exportSystem
                                                exportRecord.master_id = int(ds.AccessionNumber)
                                                exportRecord.externalId = ds.StudyInstanceUID
                                                exportRecord.save()
                                                LOGGER.info(
                                                    'SEND     Action_Export added, id %i' % (exportRecord.id))
                                            samson_db.close()
                                else:
                                    failedDestList.append(slog.destination)
                                    needBreak = True
                        except Exception as e:
                            LOGGER.error(f'SEND     instance uid "{slog.instance_uid}" not found ({e})')
                            slog.delete_instance()
                    if needBreak:
                        break
            except Exception as e:
                LOGGER.error('SEND     error for instance uid "%s": %s' % (slog.instance_uid, e))
            pacs_db.close()
        time.sleep(180)


def isActionExists(accessionNumber, patientId, modality = None):
    if modality and modality.snilsAsId:
        return bool(Action.select(Action.id).join(Event).join(Client).where(Action.id == accessionNumber, Client.SNILS == str(patientId)))
    else:
        return bool(Action.select(Action.id).join(Event).where(Action.id == accessionNumber, Event.client_id == patientId))

def existsExportRecord(accessionNumber, exportSystem):
    return bool(ActionExport.get_or_none(ActionExport.master_id == accessionNumber, ActionExport.system_id == exportSystem))


import pynetdicom.sop_class as sop_class
from pydicom import Dataset
def sendImage(destName : str, ds : pydicom.Dataset, srcAetitle : str = None) -> bool:
    """Отправка изображения

    Parameters
    ----------
        destName : str
            Название модальности, куда отправляем
        ds : pydicom.Dataset
            pydicom Dataset для отправки

    Returns
    -------
        bool
            Успешно или нет произошла отправка
    """
    dest = None
    for modality in config.modalities:
        if modality.name == destName:
            dest = modality
            break
    if dest is None:
        LOGGER.warning('ROUTE    Route destination %s not found'%destName)
        return True

    ourAetitle = dest.pacsaetitle or config.pacs_aetitle
    if srcAetitle:
        src = None
        for modality in config.modalities:
            if modality.aetitle == srcAetitle:
                src = modality
                break
        if src:
            ourAetitle = src.pacsaetitle or ourAetitle

    LOGGER.debug('ROUTE    Sending image to %s with our aetitle %s'%(destName, ourAetitle))
    mod_ae = AE(ae_title=ourAetitle)
    mod_ae.requested_contexts = MyStoragePresentationContexts
    mod_ae.add_requested_context(sop_class.StudyRootQueryRetrieveInformationModelFind)
    mod_ae.dimse_timeout = 300
    assoc = mod_ae.associate(dest.host, dest.port, ae_title = dest.aetitle)
    if assoc.is_established:
        try:
            status = assoc.send_c_store(ds)
        except Exception as e:
            LOGGER.error('ROUTE    Routing failed with "%s"'%str(e))
            status = None

        if not status:
            LOGGER.warning('ROUTE    Connection to %s timed out, was aborted or received invalid response'%destName)
            assoc.release()
            return False
        assoc.release()
        return True
    else:
        LOGGER.warning('ROUTE    Association with %s rejected, aborted or never connected'%destName)
        return False


def worklist_search_query(cond: [str], params: list = None, fullName = False, limit: int = None, offset: int = None):
    samson_db.connect()
    # Build the base query
    stmt = """ SELECT
        c.lastName AS lastName,
        c.firstName AS firstName,
        c.patrname AS patrName,
        c.id AS clientId,
        c.birthDate AS birthDate,
        CASE c.sex
            WHEN 1 THEN 'M'
            WHEN 2 THEN 'F'
            ELSE 'U'
        END AS sex,
        c.snils AS snils,
        eq.labCode AS aet,
        a.id AS actionId,
        a.begDate AS actionBegDate,
        at.name AS actionName,
        at.code AS actionCode,
        Job_Ticket.dateTime as ticketDate
    FROM
        rbEquipment AS eq
        INNER JOIN ActionType_Equipment AS ate ON eq.id = ate.equipment_id
        INNER JOIN ActionType AS at ON at.id = ate.master_id
        INNER JOIN Action AS a ON a.actionType_id = at.id
        INNER JOIN Event AS e ON e.id = a.event_id
        INNER JOIN Client AS c ON c.id = e.client_id
        INNER JOIN ActionProperty ON ActionProperty.action_id = a.id
        INNER JOIN ActionProperty_Job_Ticket ON ActionProperty_Job_Ticket.id = ActionProperty.id
        INNER JOIN Job_Ticket ON Job_Ticket.id = ActionProperty_Job_Ticket.value
    WHERE
        (a.status = 0 OR a.status = 5)
        AND a.deleted = 0
        AND a.begDate IS NOT NULL
        AND DATE(Job_Ticket.dateTime) >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
    """

    # Add conditions if they exist
    query_params = []
    if cond:
        stmt += " AND " + " AND ".join(cond)
        if params:
            query_params.extend(params)

    # Add ordering
    stmt += " ORDER BY c.lastName"

    # Add LIMIT and OFFSET if provided
    if limit is not None:
        stmt += " LIMIT %s"
        query_params.append(limit)
    if offset is not None:
        stmt += " OFFSET %s"
        query_params.append(offset)

    cursor = samson_db.execute_sql(stmt, tuple(query_params))

    resultList = []

    for r in cursor.fetchall():
        lastName   = r[0]
        firstName  = r[1]
        patrName   = r[2]
        clientId   = r[3]
        birthDate  = r[4]
        sex        = r[5]
        snils      = r[6]
        aet    = r[7]
        actionId   = r[8]
        aBegDate   = r[9]
        actionName = r[10]
        actionCode = r[11]
        ticketDate = r[12]
        if fullName:
            patientName = (' '.join([lastName.strip(), firstName.strip(), patrName.strip()]))
        else:
            patientName = (' '.join([lastName.strip(), firstName.strip()[:1]+'.', patrName.strip()[:1]+'.']))
        data = {'patientName': patientName,
            'sex': sex,
            'clientId': clientId,
            'birthDate': birthDate,
            'snils': snils,
            'actionId': actionId,
            'actionName': actionName,
            'actionCode': actionCode,
            'actionBegDate': aBegDate,
            'ticketDate': ticketDate,
            'lastName': lastName,
            'firstName': firstName,
            'patrName': patrName,
            'aet': aet}
        resultList.append(data)
    samson_db.close()
    return resultList

_MY_STORAGE_CLASSES = {
"ComputedRadiographyImageStorage": "1.2.840.10008.5.1.4.1.1.1",
"DigitalXRayImageStorageForPresentation": "1.2.840.10008.5.1.4.1.1.1.1",
"DigitalMammographyXRayImageStorageForPresentation": "1.2.840.10008.5.1.4.1.1.1.2",
"DigitalMammographyXRayImageStorageForProcessing": "1.2.840.10008.5.1.4.1.1.1.2.1",
"CTImageStorage": "1.2.840.10008.5.1.4.1.1.2",
"MRImageStorage": "1.2.840.10008.5.1.4.1.1.4",
"EncapsulatedPDFStorage": "1.2.840.10008.5.1.4.1.1.104.1",
"GrayscaleSoftcopyPresentationStateStorage": "1.2.840.10008.5.1.4.1.1.11.1",
"NuclearMedicineImageStorage": "1.2.840.10008.5.1.4.1.1.20",
"UltrasoundMultiFrameImageStorage": "1.2.840.10008.5.1.4.1.1.3.1",
"EnhancedMRImageStorage": "1.2.840.10008.5.1.4.1.1.4.1",
"UltrasoundImageStorage": "1.2.840.10008.5.1.4.1.1.6.1",
"RawDataStorage": "1.2.840.10008.5.1.4.1.1.66",
"SegmentationStorage": "1.2.840.10008.5.1.4.1.1.66.4",
"RealWorldValueMappingStorage": "1.2.840.10008.5.1.4.1.1.67",
"SecondaryCaptureImageStorage": "1.2.840.10008.5.1.4.1.1.7",
"BasicTextSRStorage": "1.2.840.10008.5.1.4.1.1.88.11",
"EnhancedSRStorage": "1.2.840.10008.5.1.4.1.1.88.22",
"ComprehensiveSRStorage": "1.2.840.10008.5.1.4.1.1.88.33",
"XRayRadiationDoseSRStorage": "1.2.840.10008.5.1.4.1.1.88.67",
"TwelveLeadECGWaveformStorage": "1.2.840.10008.5.1.4.1.1.9.1.1",
"DigitalIntraOralXRayImageStorageForPresentation": "1.2.840.10008.5.1.4.1.1.1.3"
}

MyStoragePresentationContexts = [
    build_context(uid) for uid in _MY_STORAGE_CLASSES.values()
]
