Python dbmysql_connect.DBMY类代码示例

def get_1clinic_lock(id_unlock = None):

    dnow = datetime.datetime.now()

    dbmy = DBMY()
    curm = dbmy.con.cursor()

    if id_unlock is not None:
        ssql = "UPDATE insr_list SET done = %s, c_lock = Null WHERE id = %s;"
        curm.execute(ssql, (dnow, id_unlock, ))
        dbmy.con.commit()

    ssql = "SELECT id, clinic_id, mcod FROM insr_list WHERE (done is Null) AND (c_lock is Null);"
    curm.execute(ssql)
    rec = curm.fetchone()

    if rec is not None:
        _id  = rec[0]
        c_id = rec[1]
        mcod = rec[2]
        c_rec = [_id, c_id, mcod]
        ssql = "UPDATE insr_list SET c_lock = 1 WHERE id = %s;"
        curm.execute(ssql, (_id, ))
        dbmy.con.commit()
    else:
        c_rec = None

    dbmy.close()
    return c_rec

def pfile(f_id, fname):

    from reestr import REESTR, get_reestr, put_reestr
    from dbmysql_connect import DBMY
        
    import time

    localtime = time.asctime( time.localtime(time.time()) )
    log.info('------------------------------------------------------------')
    log.info('PR Import Start {0}'.format(localtime))
    
    pr_arr = get_reestr(fname)
    
    n_pr   = len(pr_arr)
    
    sout = "Input file <{0}> has got {1} lines".format(fname, n_pr)
    log.info( sout )
    
    dbmy = DBMY()
    l_ret = put_reestr(dbmy, f_id, pr_arr)
    
    dbmy.close()
    localtime = time.asctime( time.localtime(time.time()) )
    log.info('PR Import Finish  '+localtime)
    
    return l_ret

def get_clinic_id(mcod):
    from dbmysql_connect import DBMY
    dbmy = DBMY()
    cursor = dbmy.con.cursor()
    s_sql = "SELECT clinic_id FROM pn_list WHERE mcod = %s;"
    cursor.execute(s_sql, (mcod, ))
    rec = cursor.fetchone()
    if rec is None:
        clinic_id = 0
    else:
        clinic_id = rec[0]

    dbmy.close()
    return clinic_id

def clear_tfoms_peoples(clinic_id):

    dbmy = DBMY()
    curm = dbmy.con.cursor()

    s_sqlt = "DELETE FROM tfoms_peoples WHERE clinic_id = %s;"
    curm.execute(s_sqlt, (clinic_id, ))

    dbmy.con.commit()
    dbmy.close()

    log.info('-----------------------------------------------------------------------------------')
    sout = "All records for clinic_id = {0} have been deleted from the tfoms_peoples table".format(clinic_id)
    log.info(sout)

def register_done(_id):
    import datetime

    dbmy = DBMY()
    curm = dbmy.con.cursor()

    dnow = datetime.datetime.now()
    sdnow = str(dnow)

    s_sqlt = """UPDATE insr_list
    SET done = %s
    WHERE
    id = %s;"""

    curm.execute(s_sqlt,(dnow, _id, ))
    dbmy.close()

def get_data(d_begin = D_BEGIN, d_end = D_END):
    from dbmysql_connect import DBMY
    
    dbmy = DBMY()
    curr = dbmy.con.cursor()

    curr.execute(s_sql1, (d_begin, d_end, ))

    recs = curr.fetchall()

    m = []
    for rec in recs:
	n = rec[0]
	f = n*factor
	d = rec[1]
	m.append(f)
	
    dbmy.close()
    return m

def get_clist():

    dbmy = DBMY()
    curm = dbmy.con.cursor()

    ssql = "SELECT id, clinic_id, mcod FROM insr_list WHERE done is Null;"
    curm.execute(ssql)
    results = curm.fetchall()

    clist = []
    for rec in results:
        _id = rec[0]
        clinic_id = rec[1]
        mcod = rec[2]
        if (mcod is None) or (clinic_id is None): continue
        clist.append([_id, clinic_id, mcod])

    dbmy.close()
    return clist

def get_d(d_start, d_finish):

    dbmy = DBMY(host = MD_HOST, db = MD_DB)
    cursor = dbmy.con.cursor()
    cursor.execute(SQLT1, (d_start, d_finish, ))

    results = cursor.fetchall()

    ddd = {}

    for rec in results:
        id_incident = rec[0]
        d_dt = rec[1]
        id_death_cause = rec[2]
        surname = rec[3]
        name = rec[4]
        patronymic = rec[5]
        birthday = rec[6]
        id_document = rec[7]
        id_type_cause = rec[8]
        id_mkb = rec[9]
        mkb_code = rec[10]
        mkb_name = rec[11]
        if ddd.has_key(id_incident):
            ddd[id_incident].ds.append(mkb_code)
        else:
            d = D_CASE()
            d.id_incident = id_incident

            d.lname = surname
            d.fname = name
            d.mname = patronymic
            d.birthday = birthday

            d.d_dt = d_dt
            d.id_document = id_document
            d.ds = [mkb_code]
            ddd[id_incident] = d

    dbmy.close()
    return ddd

def test_getrefbooklist():
    
    dbmy = DBMY()


    s_sqlt = """INSERT INTO
    cdmarf$refbooklist
    (code, name, description, table_name)
    VALUES
    ('{0}', '{1}', '{2}', '{3}');"""
    
    s_sqlft = """SELECT id
    FROM cdmarf$refbooklist
    WHERE code = '{0}';
    """

    
    cursor = dbmy.con.cursor()
    
    req = GetRefbookList()
    
    r = makeSOAPRequest(req)
    assert isinstance(r, ReferenceList)
    for elm in r.iter():
        #print elm.debugString()
        print "{0}-{1}".format(elm.code, elm.name)
        code        = elm.code
        name        = elm.name
        description = elm.description
        table_name  = elm.table_name
        
        s_sql = s_sqlft.format(code)
        cursor.execute(s_sql)
        rec = cursor.fetchone()
        
        if rec is None:
            s_sql = s_sqlt.format(code, name, description, table_name)
            cursor.execute(s_sql)
        
    dbmy.con.commit()
    dbmy.close()

def write_result(fname = FNAME):
    import xlwt

    dbmy = DBMY(host = MY_DBHOST, db = MY_DB)
    cur = dbmy.con.cursor()
    ssql = "SELECT * FROM cncl_clinics;"
    cur.execute(ssql)
    result = cur.fetchall()

    wb = xlwt.Workbook(encoding='cp1251')
    ws = wb.add_sheet('Conclusions Count')

    ws.write(0,0,"clinic_id")
    ws.write(0,1,"mcod")
    ws.write(0,2,"name")
    ws.write(0,3,"cncl_count")
    ws.write(0,4,"t_count")
    ws.write(0,5,"uid_count")

    row = 1
    for rec in result:
        clinic_id = rec[1]
        mcod = rec[2]
        name = rec[3]
        cncl_count = rec[4]
        t_count = rec[5]
        uid_count = rec[6]

        row += 1

        ws.write(row,0,clinic_id)
        ws.write(row,1,mcod)
        ws.write(row,2,name)
        ws.write(row,3,cncl_count)
        ws.write(row,4,t_count)
        ws.write(row,5,uid_count)

    wb.save(fname)
    dbmy.close()

def pfile(fname):
    from dbmis_connect2 import DBMIS
    from dbmysql_connect import DBMY
    from PatientInfo2 import PatientInfo2
        
    import time

    localtime = time.asctime( time.localtime(time.time()) )
    log.info('------------------------------------------------------------')
    log.info('DVN List Processing Start {0}'.format(localtime))
    
    
    ppp = plist_in(fname)
    
    sout = "Totally {0} lines have been read from file <{1}>".format(len(ppp), fname)
    log.info(sout)

    i1 = fname.find("22M")
    if i1 < 0:
	sout = "Wrong file name <{0}>".format(fname)
	log.warn(sout)
	return
    
    s_mcod = fname[i1+3:i1+9]
    mcod   = int(s_mcod)

    try:
	mo = modb[mcod]
	clinic_id = mo.mis_code
	sout = "clinic_id: {0} MO Code: {1}".format(clinic_id, mcod) 
	log.info(sout)
    except:
	sout = "Clinic not found for mcod = {0}".format(s_mcod)
	log.warn(sout)
	return

    p_obj = PatientInfo2()

    dbc = DBMIS(clinic_id)
    if dbc.ogrn == None:
        CLINIC_OGRN = u""
    else:
        CLINIC_OGRN = dbc.ogrn

    cogrn = CLINIC_OGRN.encode('utf-8')
    cname = dbc.name.encode('utf-8')
    
    if SKIP_OGRN: CLINIC_OGRN = u""
    
    sout = "clinic_id: {0} clinic_name: {1} clinic_ogrn: {2} cod_mo: {3}".format(clinic_id, cname, cogrn, mcod)
    log.info(sout)
    
    if dbc.clinic_areas == None:
        sout = "Clinic has not got any areas"
        log.warn(sout)
        dbc.close()
        return
    else:
        nareas = len(dbc.clinic_areas)
        area_id = dbc.clinic_areas[0][0]
        area_nu = dbc.clinic_areas[0][1]
        sout = "Clinic has got {0} areas".format(nareas)
        log.info(sout)
        sout = "Using area_id: {0} area_number: {1}".format(area_id, area_nu)
        log.info(sout)

    wrong_clinic = 0
    wrong_insorg = 0
    ncount = 0
    dbc2 = DBMIS(clinic_id)
    cur2 = dbc2.con.cursor()
    dbmy = DBMY()

    dvn_number = 0
    
    for prec in ppp:
        ncount += 1
        people_id = prec[0]
        insorg_mcod = prec[2]
        if insorg_mcod == "":
            insorg_id = 0
        else:
            insorg_id = int(insorg_mcod) - 22000
        medical_insurance_series = prec[4]
        medical_insurance_number = prec[5]
        p_obj.initFromDb(dbc, people_id)
	
	s_mcod = prec[7]
	if s_mcod == "\r\n": continue
	f_mcod = int(s_mcod)
	try:
	    mo = modb[f_mcod]
	    f_clinic_id = mo.mis_code
	except:
	    sout = "People_id: {0}. Clinic was not found for mcod = {1}.".format(people_id, f_mcod)
	    log.warn(sout)
	    continue

        if ncount % STEP == 0:
            sout = " {0} people_id: {1} clinic_id: {2} dvn_number: {3}".format(ncount, people_id, p_obj.clinic_id, dvn_number)
#.........这里部分代码省略.........

def pfile(fname):
    from dbmis_connect2 import DBMIS
    from dbmysql_connect import DBMY
    from PatientInfo2 import PatientInfo2
        
    import time

    localtime = time.asctime( time.localtime(time.time()) )
    log.info('------------------------------------------------------------')
    log.info('VT Processing Start {0}'.format(localtime))
    
    i1 = fname.find("VT22M")
    if i1 < 0:
	sout = "Wrong file name <{0}>".format(fname)
	log.warn(sout)
	return

    s_mcod = fname[i1+5:i1+11]
    mcod   = int(s_mcod)

    try:
	mo = modb[mcod]
	clinic_id = mo.mis_code
	sout = "clinic_id: {0} MO Code: {1}".format(clinic_id, mcod) 
	log.info(sout)
    except:
	sout = "Clinic not found for mcod = {0}".format(s_mcod)
	log.warn(sout)
	return

    
    ppp = plist_in(fname)
    
    sout = "Totally {0} lines have been read from file <{1}>".format(len(ppp), fname)
    log.info(sout)

    

    p_obj = PatientInfo2()

    dbc = DBMIS(clinic_id, mis_host = HOST, mis_db = DB)
    cname = dbc.name.encode('utf-8')

    sout = "cod_mo: {0} clinic_id: {1} clinic_name: {2} ".format(mcod, clinic_id, cname)
    log.info(sout)

    wrong_clinic = 0
    wrong_insorg = 0
    ncount = 0
    dbc2 = DBMIS(clinic_id, mis_host = HOST, mis_db = DB)
    cur2 = dbc2.con.cursor()
    dbmy = DBMY()

    double_number = 0
    dd_number     = 0
    condidate_number = 0
    commentl_number = 0
    
    for prec in ppp:
        ncount += 1
        people_id = prec[0]
        err_code  = prec[1]
	comment   = prec[2]

        if ncount % STEP == 0:
            sout = " {0} people_id: {1}".format(ncount, people_id)
            log.info(sout)
	# err_code in (54, 57) 06/12/2013 - skip 57
	# (err_code == 54) or ((err_code == 53) and (comment in COMMENTL))
	# 16/01/2014 check only 54
	if (err_code == 54):
	    if comment in COMMENTL: commentl_number += 1
	    
	    cc_lines = get_cc_lines(dbc, people_id)
	    
	    for cc_id in cc_lines:
		s_sqlt = """UPDATE clinical_checkups
		SET
		people_status_code = 1
		WHERE clinical_checkup_id = {0}"""
		s_sql = s_sqlt.format(cc_id)
		dbc2.con.execute_immediate(s_sql)
		dbc2.con.commit()
		condidate_number += 1
		
	if err_code in (70,71):
	    double_number += 1
	    cc_lines = get_cc_lines(dbc, people_id)
	    if len(cc_lines) > 1:
		dd_number += 1
		iii = 0
		for cc_id in cc_lines:
		    iii += 1
		    sout = "people_id: {0} cc_id: {1}".format(people_id, cc_id)
		    log.info(sout)
		    if iii == 1: continue
		    s_sqlt = """UPDATE clinical_checkups
		    SET
		    people_status_code = 2
		    WHERE clinical_checkup_id = {0}"""
#.........这里部分代码省略.........

def do_card_n(clinic_id = CLINIC_ID):

    from dbmis_connect2 import DBMIS
    import time
    import datetime

    dbmy = DBMY()
    curm_card = dbmy.con.cursor()

    localtime = time.asctime( time.localtime(time.time()) )
    log.info('-----------------------------------------------------------------------------------')
    log.info('Prof Exam Export Start {0}'.format(localtime))

    sout = "Database: {0}:{1}".format(HOST, DB)
    log.info(sout)

    mcod = modb.moCodeByMisId(clinic_id)

    dbc = DBMIS(clinic_id, mis_host = HOST, mis_db = DB)

    cname = dbc.name.encode('utf-8')
    caddr = dbc.addr_jure.encode('utf-8')

    sout = "clinic_id: {0} mcod: {1} clinic_name: {2}".format(clinic_id, mcod, cname)
    log.info(sout)
    sout = "address: {0}".format(caddr)
    log.info(sout)

    arr_arr = getC_list(dbc, clinic_id)
    nnn = 0
    nout_all = 0
    for arr in arr_arr:
        nnn += 1
        c_list = arr
        s_nnn = "%03d" % (nnn)
        f_fname = FPATH + "/" + FNAME.format(mcod, s_nnn)

        fo = open(f_fname, "wb")

        sout = """<?xml version="1.0" encoding="UTF-8"?>
        <children>"""
        fo.write(sout)

        iii = 0
        nout = 0
        for ccc in c_list:
            iii += 1
            e_id = ccc[0]
            p_id = ccc[1]
            d_bg = ccc[2]

            docTXT = getCard(dbc, e_id, p_id, AGE_MODE)
            if len(docTXT) > 0:
                fo.write(docTXT)
                fo.flush()
                os.fsync(fo.fileno())
                nout += 1
                if REGISTER_CARDS:
                    dnow = datetime.datetime.now()
                    sdnow = str(dnow)
                    try:
                        curm_card.execute(SQLT_REGISTER_CARD, (e_id, sdnow, ))
                    except:
                        pass

        sout = '</children>'
        fo.write(sout)
        fo.close()
        sout = "Output to file: {0} {1} cards".format(f_fname, nout)
        log.info(sout)
        nout_all += nout

    dbc.close()
    dbmy.close()
    sout = "Altogether cards has been written: {0}".format(nout_all)
    log.info(sout)
    localtime = time.asctime( time.localtime(time.time()) )
    log.info('Prof Exam Export Finish  '+localtime)
    return nout_all

def process_p_list(clinic_id, p_list):
    from dbmis_connect2 import DBMIS
    from dbmysql_connect import DBMY
    from PatientInfo2 import PatientInfo2

    import time

    localtime = time.asctime( time.localtime(time.time()) )
    log.info('------------------------------------------------------------')
    log.info('DVN List Processing Start {0}'.format(localtime))


    ppp = p_list

    p_obj = PatientInfo2()

    dbc = DBMIS(clinic_id, mis_host = HOST, mis_db = DB)
    if dbc.ogrn == None:
        CLINIC_OGRN = u""
    else:
        CLINIC_OGRN = dbc.ogrn

    cogrn = CLINIC_OGRN.encode('utf-8')
    cname = dbc.name.encode('utf-8')

    if SKIP_OGRN: CLINIC_OGRN = u""

    if dbc.clinic_areas == None:
        sout = "Clinic has not got any areas"
        log.warn(sout)
        dbc.close()
        return
    else:
        nareas = len(dbc.clinic_areas)
        area_id = dbc.clinic_areas[0][0]
        area_nu = dbc.clinic_areas[0][1]
        sout = "Clinic has got {0} areas".format(nareas)
        log.info(sout)
        sout = "Using area_id: {0} area_number: {1}".format(area_id, area_nu)
        log.info(sout)

    wrong_clinic = 0
    wrong_insorg = 0
    ncount = 0
    dbc2 = DBMIS(clinic_id, mis_host = HOST, mis_db = DB)
    cur2 = dbc2.con.cursor()
    dbmy = DBMY()

    dvn_number = 0

    for prec in ppp:
        ncount += 1
        people_id = prec[0]
        insorg_id = prec[1]
        if insorg_id is None:
            insorg_id = 0

        p_obj.initFromDb(dbc, people_id)

        if ncount % STEP == 0:
            sout = " {0} people_id: {1} clinic_id: {2} dvn_number: {3}".format(ncount, people_id, p_obj.clinic_id, dvn_number)
            log.info(sout)

	# check if person already
	# has got a record in the clinical_checkups table
	if person_in_cc(dbc, people_id): continue

	lname = p_obj.lname
	fname = p_obj.fname
	mname = p_obj.mname
	bd    = p_obj.birthday

        if check_person(dbc, people_id):

            # check if clinical_checkups table

            cc_id = add_cc(dbc2, clinic_id, people_id)

            register_cc(dbmy, cc_id, people_id, clinic_id)

            dvn_number += 1

    sout = "DVN cases number: {0}".format(dvn_number)
    log.info(sout)

    dbc.close()
    dbc2.close()
    dbmy.close()
    localtime = time.asctime( time.localtime(time.time()) )
    log.info('DVN List Processing Finish  '+localtime)

def plist(dbc, clinic_id, mcod, patient_list):
    import xlwt
    from dbmysql_connect import DBMY
    from PatientInfo import p1, p2
    from insorglist import InsorgInfoList
    
    import os    
    import datetime
    import time

    dbmy = DBMY()
    curr = dbmy.con.cursor()
    
    s_sqlf = """SELECT oms_series, oms_number, enp, mcod, ocato, smo_code
    FROM
    sm
    WHERE people_id = %s"""

    cur = dbc.con.cursor()

    s_sql_ap = """SELECT 
ap.area_people_id, ap.area_id_fk, ap.date_beg, ap.motive_attach_beg_id_fk,
ca.clinic_id_fk
FROM area_peoples ap
LEFT JOIN areas ar ON ap.area_id_fk = ar.area_id
LEFT JOIN clinic_areas ca ON ar.clinic_area_id_fk = ca.clinic_area_id
WHERE ap.people_id_fk = ? AND ca.basic_speciality = 1
AND ap.date_end is Null
ORDER BY ap.date_beg DESC;"""
    
    s_sql_enp = """SELECT enp FROM peoples WHERE people_id = ?;"""
    
    insorgs = InsorgInfoList()

    stime  = time.strftime("%Y%m%d")
    fnameb = FNAMEb.format(mcod, stime)
    fnamex = FNAMEx.format(mcod, stime)
    sout = "Output to files: {0} | {1}".format( fnameb, fnamex )
    log.info(sout)

    f_fnameb = IN_PATH + "/" + fnameb
    fob = open(f_fnameb, "wb")

    wb = xlwt.Workbook(encoding='cp1251')
    ws = wb.add_sheet('Patients')
    
    sout = u"clinic_id: {0}".format(clinic_id)
    ws.write(0,0,sout)
    ws.write(2,0,u"id")
    ws.write(2,1,u"date_beg")
    ws.write(2,2,u"motive")
    ws.write(2,3,u"clinic_id")
    
    ws_row = 3

    count    = 0
    count_a  = 0
    count_m  = 0
    count_np = 0
    noicc    = 0

    p_ids    = []
    for p_obj in patient_list:

	p_id = p_obj.people_id
	
	count += 1
	
	insorg_id   = p_obj.insorg_id
	try:
	    insorg = insorgs[insorg_id]
	except:
	    sout = "People_id: {0}. Have not got insorg_id: {1}".format(p_id, insorg_id)
	    log.debug(sout)
	    insorg = insorgs[0]
	    noicc += 1

	if count % STEP == 0:
	    sout = " {0} people_id: {1}".format(count, p_id)
	    log.info(sout)

	curr.execute(s_sqlf,(p_id,))
	rec = curr.fetchone()
	if rec is None:
	    count_a += 1
	    cur.execute(s_sql_enp,(p_id, ))
	    rec_enp = cur.fetchone()
	    if rec_enp is None:
		p_obj.enp = None
		count_np += 1
		continue
	    if rec_enp[0] is None:
		p_obj.enp = None
		count_np += 1
		continue
	    p_obj.enp = rec_enp[0]
	else:
	    f_oms_series = rec[0]
	    f_oms_number = rec[1]
	    f_enp        = rec[2]
#.........这里部分代码省略.........