db

import sqlalchemy
import pandas as pd

import pyodbc
import uuid

import csv
import psycopg2

from claims.models import Claim

def fetch()
    try:
        sql_conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=192.168.5.81;DATABASE=Eremedylive;UID=sa;PWD=1234567aA')
    except Exception as e:
        print(str(e))
        raise

    print(sql_conn)
    query = "select * FROM op_billing"
    cursor = sql_conn.cursor()

    with cursor.execute(query):
        row = cursor.fetchone()
        columns = [column[0] for column in cursor.description]
        print(columns)
        while row:
            print (row)
            # Create Claim Object
            c = Claim(insurance_company=row[10], member_number=row[7], scheme_name=row[11], member=row[12], visit_number=row[4], patient_number=row[7], visit_type=row[20], provider=row[1], created=row[33], created_by=row[32], modified=row[35], payers=row[10], k_gross_amount=row[18]  )
            row = cursor.fetchone()


    """
    [u'BILL_ID', u'CENTER_ID', u'ORG_ID', u'BILLTYPE_ID', u'VISIT_ID', u'BILL_NO', u'BILL_DATE', u'PATIENT_ID', u'ADDRESS_ID', u'PAYMENT_TYPE_ID', 

    u'CREDIT_COMPANY_ID', u'CREDIT_COMPANY_SCHEME_ID', u'SCHEME_MEMBER_ID', u'EMPLOYEE_ID', u'AUTHORIZATION_NUMBER', u'AUTHORIZATION_DATE', u'RELATIONSHIP_ID', 

    u'LIMIT_AMOUNT', u'CO_PAY', u'TARIFF_CATEGORY_ID', u'VISIT_TYPE', u'CASETYPE_ID', u'BILL_AMOUNT', u'PATIENTPAID_AMOUNT', u'DUE_AMOUNT', u'DISCOUNT_AMOUNT', 

    u'CREDITNOTE_AMOUNT', u'AUT_EMP_FOR_DISCOUNT', u'AUTHORIZED_DATE', u'REMARKS', u'CASH_REQUEST_ID', u'STATUS', u'CREATED_BY', u'CREATED_DATE', u'MODIFIED_BY', 

    u'MODIFIED_DATE', u'PATIENTPAY_AMOUNT', u'COMPANYPAY_AMOUNT', u'APPOINTMENT_ID', u'DOCTOR_ID', u'COMPLAINTS', u'REF_DOCTOR_ID', u'WALKIN', u'OP_INVOICE_ID', 

    u'POSTED_TO_ACCOUNTS', u'RECIEVED_FLAG', u'JV_NUMBER', u'CLINIC_ID', u'LB_RD_ORDER_DETAIL_ID', u'PATIENT_CATEGORY_ID', u'AUT_EMP_FOR_CREDITCOMPANY_ID', u'CREDIT_REMARKS', 

    u'IS_EMERGENCY', u'TRANSFER_FLAG', u'TPA_DOCUMENT', u'WRITEOFF_AMOUNT', u'WRITEOFF_ID']
    """


    """
    (52795L, 3L, 1L, 2L, 42041L, u'OR1100012260/2', datetime.datetime(2011, 11, 17, 9, 49, 38), 
    10016L, 22091L, 2L, 77L, 1459L, 387905L, None, u'null', datetime.datetime(2011, 11, 17, 0, 0), 
    None, Decimal('9999499.00'), u'', 3L, u'N', None, Decimal('200.00'), Decimal('0.00'), Decimal('0.00'), 
    Decimal('0.00'), Decimal('0.00'), None, datetime.datetime(1900, 1, 1, 0, 0), u'', 59132L, 1, 133L, 
    datetime.datetime(2011, 11, 17, 9, 50, 20), 1000000000079L, datetime.datetime(2017, 4, 25, 11, 8, 48), 
    Decimal('0.00'), Decimal('200.00'), None, 50L, u'', None, u'1', 11284L, u'Y', u'', u'OR11OPB000177_HMS', 
    None, None, 2L, None, None, None, None, None, Decimal('771.15'), 1000000006480L)

    """

    cols = ['insurance_company','member_number','scheme_name','member','visit_number','patient_number','visit_type','provider','created',
    'created_by','modified','payers','k_gross_amount']

    # [10, 7, 11, 12, 4, 7, 20, 1, 33, 32, 35, 10, 18 ]