ck-ntuh-net/mysite/ck_cron.py

#!/usr/bin/python
# coding=utf-8

from __future__ import unicode_literals

import datetime
import os
import re
import subprocess
import time


import django

os.environ['DJANGO_SETTINGS_MODULE'] = 'mysite.settings'
# os.environ.setdefault("DJANGO_SETTINGS_MODULE", "mysite.settings")
django.setup()


from django.db.models import Count


from import_export import resources
from import_export.fields import Field
# import rotate_backups

# from mechanize import Browser
# br = Browser()
# br.set_handle_robots(False)

from ck.models import *

from ck.common import SortNHI, UpdateNHICase, UpdateNHICase2, AddPatient, QueryMS, GetCaseForCyberKnifeList

# from ntuhgov import intra
import ntuhgov.portal_selenium as portal

from ntuhgov.portal_selenium import QueryCase, QueryCaseByPass


QUEUE_DIR='/tmp/queue'
QUEUE_DIR='/mnt/pve/Public/queue'

# MAX_PAT = 200

def PortalRefresh():

    patients = Patient.objects.filter(dead__isnull=True).order_by('timestamp')
    MAX_PAT = int(patients.count() / 30)
    # patients = Patient.objects.all()
#    patients = patients.filter(medical_records='5619422')
    SESSION = portal.Login()
    n_pat = 0
    for patient in patients:
        if n_pat>MAX_PAT:
            break

        if patient.dead:
            continue

        print(n_pat,'/',MAX_PAT)

        n_pat += 1

    # Update ElectronicMedicalReport

        patient.save() # update timestamp

        print(patient.id, patient.medical_records,  patient)
        excluded = patient.electronicmedicalreport_set.all().values_list('report_key', flat=True)
        # print(excluded)

        # reports = portal.ElectronicMedicalReportViewer(patient.medical_records, startswith='T0', excluded=excluded)
        reports = portal.ElectronicMedicalReportViewer(patient.medical_records, excluded=excluded)
        # print(len(reports))
        for report in reports:

            # 沒報告?
            if report['檢查日期'] == '*' or report['報告日期'] == '*':
                continue

            r = ElectronicMedicalReport(patient=patient)
            r.report_key = report['ReportKey']
            r.report_class = report['報告類別']
            r.check_date = datetime.datetime.strptime(report['檢查日期'], "%Y/%m/%d")
            r.report_date = datetime.datetime.strptime(report['報告日期'], "%Y/%m/%d")
            r.report_code = report['ReportCode']
            r.report = report['html']

            r.save()

        if patient.id_cards.find(u'　') != -1:
        #    print '"%s"' % patient.id_cards
            patient.id_cards = patient.id_cards.replace(u'　','')
        #    print '"%s"' % patient.id_cards
            patient.save()

        q = PACSImage.objects.filter(patient=patient)

    #Update PACSImageShowList
        # print(patient.id_cards)
        PACSList = portal.PACSImageShowList(patient.id_cards, SESSION)
        for pacs in PACSList:
            # print(pacs)
            qq = q.filter(RequestSheetNo=pacs['RequestSheetNo'])
            if len(qq) == 0:
                # print pacs
                p = PACSImage(patient=patient)
                p.PatChartNo          = pacs['PatChartNo']
                p.RequestSheetNo      = pacs['RequestSheetNo']
                try:
                    p.ExamDate            = datetime.datetime.strptime(pacs['ExamDate'],'%Y%m%d')
                except:
                    continue
                p.LinkOrderName       = pacs['LinkOrderName']
                p.Modality            = pacs['Modality']
                p.VerifiedStateString = pacs['VerifiedStateString']
                p.Saved               = 0 #Not saved
                p.save()
                
#        continue #for debug

    #Read from old intra system, Dysfunction 
#         Xray = XrayExam(patient.medical_records)
#         for x in Xray:
#             qq = q.filter(RequestSheetNo=x['ReferNo'])
#             if len(qq) == 0:
#                 print x
#                 p = PACSImage(patient=patient)
#                 p.PatChartNo          = x['ChartNo']
#                 p.RequestSheetNo      = x['ReferNo']
#                 p.ExamDate            = datetime.datetime.strptime(x['ExamDate'],'%Y-%m-%d')
#                 p.LinkOrderName       = x['LinkOrderName']
#                 p.Modality            = x['Modality']
#                 p.VerifiedStateString = x['status']
#                 p.Saved               = 0 #Not saved
#                 p.save()
        
    #Update MedicalRecord
        print(patient.medical_records)
        MR = portal.PatientMedicalRecordListQuery(patient.medical_records, SESSION=SESSION)
        try:
            print(MR['Description'])
            print('%d %s %s %s'% (patient.id, patient.medical_records, MR['extra'], MR['dead']))
        except:
            print('************************************************************')
        # exit()
        q = MedicalRecord.objects.filter(patient=patient)
    
        for mr in MR['InPat']:
            qq = q.filter(InDate=mr['InDate'])
            if len(qq)==0 or qq[0].OutDate==None:
                # print mr
                if len(qq)==0:
                    m = MedicalRecord(patient=patient)
                else:
                    m = qq[0]
                m.Record            = '住' # 住急門 
                m.HospName          = mr['HospName'] 
                m.DeptName          = mr['DeptName'] 
                m.InDate            = mr['InDate'] 
                m.OutDate           = mr['OutDate'] 
                m.WardName          = mr['WardName'] 
                m.RoomName          = mr['RoomName'] 
                m.BedName           = mr['BedName'] 
                m.MainDrName        = mr['MainDrName'] 
                m.MainDiagnosisName = mr['MainDiagnosisName'] 
                m.StatusName        = mr['StatusName']
                m.save()
        
        for mr in MR['Emergency']:
            qq = q.filter(InDate=mr['ComeClinicDate'])
            if len(qq)==0 or qq[0].OutDate==None:
                if len(qq)==0:
                    m = MedicalRecord(patient=patient)
                else:
                    if mr['DischargeDate'] == None:
                        continue
                    m = qq[0]
                # print mr
                m.Record            = '急' # 住急門 
                m.HospName          = mr['HospName'] 
                m.DeptName          = mr['DeptName'] 
                m.InDate            = mr['ComeClinicDate'] 
                m.OutDate           = mr['DischargeDate'] 
                m.MainDrName        = mr['MainDrName'] 
                m.MainDiagnosisName = mr['MainDiagnosisName'] 
                m.StatusName        = mr['StatusName']
                m.BedName           = mr['TempBedID']
                m.save()
    
        for mr in MR['OutPat']:
            qq = q.filter(InDate=mr['ComeClinicDate'])
            if len(qq) == 0:
                # print mr
                m = MedicalRecord(patient=patient)
                m.Record            = '門' # 住急門 
                m.HospName          = mr['HospName'] 
                m.DeptName          = mr['DeptName'] 
                m.InDate            = mr['ComeClinicDate'] 
                m.SpecialCureName   = mr['SpecialCureName']
                m.MainDrName        = mr['MainDrName'] 
                m.MainDiagnosisName = mr['MainDiagnosisName'] 
                m.StatusName        = mr['AccountStatusName']
                m.save()
    
        if MR['dead']:
            patient.dead = MR['dead']
            patient.save()

         
def PatientsRefresh():
    url = "https://content.wuala.com/contents/xiao/Shared/patients.txt?key=IKlcEMNcgZZg"
    response = br.open(url)
    body = response.read()
#    print body
#    print dir(PACSImageShowList)
    images = PACSImage.objects.all()
    for image in images:
        pattern = ".*%s/.*%s" % (image.PatChartNo, image.ExamDate.strftime('%Y%m%d'))
#        print pattern
        matches = re.findall(pattern, body)
        if len(matches):
#            print matches
            image.Saved = 10 #Saved
            image.save()
            
#        ImageDir = '%s/%s' % (image.PatChartNo, image.ExamDate.strftime('%Y%m%d'))
##        print ImageDir
#        if body.find(ImageDir) != -1:
##            print ImageDir
#            image.Saved = 10 #Saved
#            image.save()

o = Oncologist.objects.values_list('name', flat=True)
s = Surgeon.objects.values_list('name', flat=True)
AllDr = set(o)|set(s)

def GetMainDrName(ChartNo):
    records = portal.PatientMedicalRecordListQuery(ChartNo)
    records = records['Emergency']+records['InPat']+records['OutPat']

    doctors = set()
    for record in records:
        MainDrName = record['MainDrName']
        if MainDrName is not None:
            MainDrName = MainDrName.replace('\ue09b', '峯') #'許峯銘'
            doctors.add(MainDrName) 

    return ' '.join(list(doctors&AllDr))

def UpdateNHI():

    for case in GetCaseForCyberKnifeList():
        print(case)
        UpdateNHICase2(case)

    # for case in QueryCase(ApplySDate='2020/02/07'):
    for case in QueryCase():
        print(case)
        UpdateNHICase(case)

    # for case in QueryCaseByPass('018522','ah651223'):
    #     print(case)
    #     UpdateNHICase(case)

    for order in NHIOrder.objects.filter(ApplyDoctor__isnull=True, MainDrName__isnull=True):
    # for order in NHIOrder.objects.filter(MainDrName__isnull=True):
        order.MainDrName = GetMainDrName(order.ChartNo)
        print(order.ChartNo, order.MainDrName )
        order.save()


class NHIOrderResource(resources.ModelResource):

    # order = Field(column_name='順序')


    # def dehydrate_order(self, nhi_order):
    #     return '%s by %s' % (book.name, book.author.name)


    def get_export_headers(self):
        headers = []
        for field in self.get_export_fields():
            headers.append(self.Meta.model._meta.get_field(field.column_name).verbose_name)
        return headers

    class Meta:
        model = NHIOrder
        fields = ('Priority', 'ChartNo', 'PatientName', 'ExamineDate', 'Quantity', 'ApplyDoctor', 'other_diagnosis', 'num_sessions', 'Memo',)
        export_order = fields

def ExportExcel():
    queryset = NHIOrder.objects.filter().order_by('-Priority', 'PackageTime')
    dataset = NHIOrderResource().export(queryset)

    # TODAY_DIR = os.path.join(QUEUE_DIR, datetime.datetime.today().strftime('%Y-%m-%d'))
    # print(time.strftime('%Y-%m-%d %H:%M:%S'))
    TODAY_DIR = os.path.join(QUEUE_DIR, time.strftime('%Y-%m-%d'))
    print(TODAY_DIR)

    os.makedirs(TODAY_DIR, exist_ok=True)
  
    with open(os.path.join(TODAY_DIR, 'All.xls'), 'wb') as f:
        f.write(dataset.export('xls'))

    for v in NHIOrder.objects.values('ApplyDoctor').distinct():

        queryset = NHIOrder.objects.filter(ApplyDoctor=v['ApplyDoctor'],Priority__gt=0).order_by('-Priority', 'PackageTime')
        dataset = NHIOrderResource().export(queryset)

        if v['ApplyDoctor'] is None:
            output = os.path.join(TODAY_DIR, 'None.xls')
        else:
            output = os.path.join(TODAY_DIR, v['ApplyDoctor']+'.xls')


        with open(output, 'wb') as f:
            f.write(dataset.export('xls'))

        # print(type(dataset))
        # print(dataset.csv)
        # exit()

def CountICD10():
    # qs = Treatment.objects.all().values('icd10cm').annotate(count=Count('icd10cm')).order_by('-count')
    # for i in qs:
    #     print(i)
    #     if i['icd10cm']:
    #         # c = ICD10Count(icd10cm=ICD10CMfinal.objects.get(pk=i['icd10cm']))
    #         c, created = ICD10Count.objects.get_or_create(icd10cm_id=i['icd10cm'])
    #         c.count=i['count']
    #         c.save()

    # count icd9 and return icd10
    # qs = Treatment.objects.all().values('icd9').annotate(count=Count('icd9')).order_by('-count')
    qs = Treatment.objects.all().values('icd9').annotate(count=Count('icd9')).order_by('count')
    for i in qs:
        # print(i)
        if i['icd9']:
            ICD9CM_code = i['icd9'][:3]+'.'+i['icd9'][3:]
            # icd10 = ICD10CMfinal.objects.filter(ICD9CM_code=ICD9CM_code).first()
            qs10 = Treatment.objects.filter(icd9=i['icd9'],icd10cm__isnull=False).values('icd10cm').annotate(count=Count('icd10cm')).order_by('-count')
            # print(ICD9CM_code, qs10)
            icd10 = qs10.first()
            print(ICD9CM_code, i['count'], icd10)
            if icd10 is not None:
                # c = ICD10Count(icd10cm=ICD10CMfinal.objects.get(pk=i['icd10cm']))
                c, created = ICD10Count.objects.get_or_create(icd10cm_id=icd10['icd10cm'])
                c.count=i['count']
                c.save()


def BackupMSSQL():
    TODAY_FILENAME = f'mssql-ntuh-{time.strftime("%Y-%m-%d")}.bak'

    subprocess.run(["/opt/mssql-tools/bin/sqlcmd", "-S", "172.16.40.43", "-U", "SA", "-P", "!QAZ2wsx", "-Q",
    f"BACKUP DATABASE [ntuh] TO DISK = N'P:\dump\{TODAY_FILENAME}' WITH NOFORMAT, NOINIT, NAME = 'demodb-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10"])

if __name__ == "__main__":


    # UpdateNHI()
    # exit()

    CountICD10()

    UpdateNHI()
    SortNHI()
    ExportExcel()

    # this has been firewalled sometimes
    BackupMSSQL()
    # QueryMS()   

    PortalRefresh()
#     PatientsRefresh()