def getAllData():
    try:
        connection, cursor = dbconnect.getConnection()
        statement = 'Select * from heathscoredata'
        cursor.execute(statement)

        data = cursor.fetchall();
        
        dataModel = {}
        op = []
        for d in data:
            dataModel = {
                'id': d[0],
                'timestamp': str(d[1]),
                'age': d[2],
                'gender': d[3],
                'height': d[4],
                'weight': d[5],
                'heartrate': d[6],
                'bloodpressuresys': d[7],
                'bloodpressuredia': d[8],
                'bmi': d[9],
                'cholestterollevel': d[10],
                'avgBloodSugarLevel': d[11],
                'alcoholConsumptionDaily': d[12],
                'alcoholConsumptionWeekly':d[13],
                'smoker': d[14],
                'score': d[15],
                'username': d[16]
            }
            op.append(dataModel)
        return json.dumps(op)
    except Exception as e:
        print("Error in fetching data", e)
        return json.dumps({'message':'Error in fetching data'})
Exemplo n.º 2
0
	def xmlrpc_getConnection(self,queryParams):
		"""
		* Purpose: 
			- This function is used to return the client_id for sqlite 
			  engine found in ``dbconnect.py``
		* Input: 
		 	- [organisation name,finanialyear_from,financialyear_to]
		* Output: 
			- returns the client_id integer
		"""
		self.client_id=dbconnect.getConnection(queryParams)
		return self.client_id
Exemplo n.º 3
0
	def xmlrpc_getConnection(self,queryParams):
		"""
		purpose: This function is used to return the client_id for sqlite 
			 engine found in dbconnect.py 
			 
		Input: [organisation name]	
		
		Output: Returns the client_id integer
		
		"""
		
		self.client_id=dbconnect.getConnection(queryParams)
		return self.client_id
Exemplo n.º 4
0
    def xmlrpc_getConnection(self, queryParams):
        """
		purpose: This function is used to return the client_id for sqlite 
			 engine found in dbconnect.py 
			 
		Input: [organisation name]	
		
		Output: Returns the client_id integer
		
		"""

        self.client_id = dbconnect.getConnection(queryParams)
        return self.client_id
Exemplo n.º 5
0
def sqlfromfile(schemafile):
    try:
        conn2 = getConnection()

        with open(schemafile, 'r') as schema:
            print('executing sql from', schemafile)
            command = schema.read()
            with conn2.cursor() as cur:
                cur.execute(command)
                conn2.commit()

        conn2.close()
    except:
        print('caught error')
def getValuesApp():
    age=int(request.json['age'])
    gender=request.json['gender']
    height=float(request.json['height'])
    height = height / 3.281
    weight=float(request.json['weight'])
    heartrate=int(request.json['heartrate'])
    bloodpressuresys=float(request.json['bloodpressuresys'])
    bloodpressuredia=float(request.json['bloodpressuredia'])
    cholestrol=float(request.json['cholestrol'])
    avgbloodsugar=float(request.json['avgbloodsugar'])
    alcoholconsumptiondaily=int(request.json['alcoholconsumptiondaily'])
    alocholconsumptionweekly=int(request.json['alocholconsumptionweekly'])
    smoker=request.json['smoker']
    username=request.json['username']

    score, recommendations = calculatehealthscore(age, gender, height, weight, heartrate, bloodpressuresys, bloodpressuredia, cholestrol, avgbloodsugar, alcoholconsumptiondaily, alocholconsumptionweekly, smoker)
    bmi = weight / (height**2)
    
    #DATABASE TRANSACTION
    
    timestamp = str(datetime.now(pytz.timezone('Asia/Kolkata')))[:-13]
    ts = datetime.now(pytz.timezone('Asia/Kolkata'))
    idno = str(ts.year)+str(ts.month)+str(ts.day)+str(ts.hour)+str(ts.minute)+str(ts.second)
    try:
        connection, cursor = dbconnect.getConnection()
        
        values = (idno, timestamp, age, gender, height, weight, heartrate, bloodpressuresys, bloodpressuredia, bmi, cholestrol, avgbloodsugar, alcoholconsumptiondaily, alocholconsumptionweekly, smoker, score, username)
        statement="INSERT INTO heathscoredata VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        cursor.execute(statement, values)
        connection.commit()
        connection.close()
    except Exception as e:
        connection.close()
        print(e)
    
    return json.dumps({'score': score, 'recommendations': recommendations})
Exemplo n.º 7
0
	def xmlrpc_Deploy(self,queryParams):
		"""
		* Purpose:
			- The function will generate the database name 
			  based on the organisation name and time stap.
			- provided the name of the database is a combination of, 
			  first character of organisation name,time stap as 
			  "dd-mm-yyyy-hh-MM-ss-ms" 
			- An entry will be made in the xml file 
			  for the currosponding organisation.
			- This function deploys a database instance for
			  an organisation for a given financial year.
			- It will call to getConnection and establish the connection 
			  for created database
			- also create the metadata(tables) given in ``dbconnect`` for that organisation
			  using sqlAlchemy.
			- create the ``Views`` for the particular organisation.
			- It add manually ``groupnames`` ad ``subgroups`` to it's corresponding class
			  tables ``Groups`` and ``subGroups``
		   	
		 * Input: 
		 	- [organisation name,From date,todate,organisation_type]
		
		 * Output: 
		 	- returns boolean True and client_id
			
		"""
		queryParams = blankspace.remove_whitespaces(queryParams)
		
		# assigning client queryparams values to variables
		name_of_org = queryParams[0] # name of organisation
		db_from_date = queryParams[1]# from date
		db_to_date = queryParams[2] # to date
		organisationType = queryParams[3] # organisation type
		
		#creating database name for organisation		
		org_db_name = name_of_org[0:1]
		time = datetime.datetime.now()
		str_time = str(time.microsecond)	
		new_microsecond = str_time[0:2]		
		result_dbname = org_db_name + str(time.year) + str(time.month) + str(time.day) + str(time.hour)\
		 		+ str(time.minute) + str(time.second) + new_microsecond
		
		del queryParams[3] #delete orgtype
		queryParams.append(result_dbname) #dbname
		queryParams.append("0") #rollover flag
		
		self.xmlrpc_writeToXmlFile(queryParams,"/opt/abt/abt.xml");		
		
		# getting client_id for the given orgnisation and from and to date
		self.client_id = dbconnect.getConnection([name_of_org,db_from_date,db_to_date])
		
		try:
			metadata = dbconnect.Base.metadata
			metadata.create_all(dbconnect.engines[self.client_id])
		except:
			print "cannot create metadata"
			
		Session = scoped_session(sessionmaker(bind=dbconnect.engines[self.client_id]))
			
		dbconnect.engines[self.client_id].execute(\
			"create view view_account as \
			select groups.groupname, account.accountcode, account.accountname, account.subgroupcode\
			from groups, account where groups.groupcode = account.groupcode\
			order by groupname;")
			
		dbconnect.engines[self.client_id].execute(\
			"create view view_voucherbook as \
			select voucher_master.vouchercode,voucher_master.flag,voucher_master.reference,\
			voucher_master.voucherdate,voucher_master.reffdate,voucher_master.vouchertype,account.accountname\
			as account_name,voucher_details.typeflag,voucher_details.amount,\
			voucher_master.narration,voucher_master.projectcode,voucher_master.cheque_no\
			from voucher_master,voucher_details,account as account \
			where voucher_master.vouchercode = voucher_details.vouchercode \
			and voucher_details.accountcode = account.accountcode;")
		dbconnect.engines[self.client_id].execute(\
			"create view view_group_subgroup as \
			select groups.groupcode, groups.groupname,subgroups.subgroupcode,subgroups.subgroupname\
			from groups, subgroups where groups.groupcode = subgroups.groupcode \
			order by groupname;")
		dbconnect.engines[self.client_id].execute(\
			"create view group_subgroup_account as select groups.groupname,\
			subgroups.subgroupname,account.accountcode,account.accountname,account.openingbalance \
			from groups join account on (groups.groupcode = account.groupcode)\
			left outer join subgroups\
			on (account.subgroupcode = subgroups.subgroupcode) order by groupname;")
		connection = dbconnect.engines[self.client_id].raw_connection()
		cur = connection.cursor()
		
		if (organisationType == "Profit Making"):

			Session.add_all([
				dbconnect.Groups('Capital',''),
				dbconnect.Groups('Current Asset',''),
				dbconnect.Groups('Current Liability',''),
				dbconnect.Groups('Direct Income','Income refers to consumption\
						opportunity gained by an entity within a specified time frame.'),
				dbconnect.Groups('Direct Expense','This are the expenses to be incurred for\
						operating the buisness.'),
				dbconnect.Groups('Fixed Assets',''),
				dbconnect.Groups('Indirect Income','Income refers to consumption opportunity\
						gained by an entity within a specified time frame.'),
				dbconnect.Groups('Indirect Expense','This are the expenses to be incurred\
						for operating the buisness.'),\
				dbconnect.Groups('Investment',''),
				dbconnect.Groups('Loans(Asset)',''),
				dbconnect.Groups('Loans(Liability)',''),
				dbconnect.Groups('Reserves',''),
				dbconnect.Groups('Miscellaneous Expenses(Asset)','')
			])
			Session.commit()
		
		else:
			Session.add_all([\
				dbconnect.Groups('Corpus',''),
				dbconnect.Groups('Current Asset',''),
				dbconnect.Groups('Current Liability',''),
				dbconnect.Groups('Direct Income','Income refers to consumption\
						opportunity gained by an entity within a specified time frame.'),
				dbconnect.Groups('Direct Expense','This are the\
						expenses to be incurred for operating the buisness.'),
				dbconnect.Groups('Fixed Assets',''),
				dbconnect.Groups('Indirect Income','Income refers to consumption\
						opportunity gained by an entity within a specified time frame.'),
				dbconnect.Groups('Indirect Expense','This are the\
						expenses to be incurred for operating the buisness.'),
				dbconnect.Groups('Investment',''),
				dbconnect.Groups('Loans(Asset)',''),
				dbconnect.Groups('Loans(Liability)',''),
				dbconnect.Groups('Reserves',''),
				dbconnect.Groups('Miscellaneous Expenses(Asset)','')
			])
			Session.commit()
		
		Session.add_all([\
			dbconnect.subGroups('2','Bank'),\
			dbconnect.subGroups('2','Cash'),\
			dbconnect.subGroups('2','Inventory'),\
			dbconnect.subGroups('2','Loans & Advance'),\
			dbconnect.subGroups('2','Sundry Debtors'),\
			dbconnect.subGroups('3','Provisions'),
			dbconnect.subGroups('3','Sundry Creditors for Expense'),\
			dbconnect.subGroups('3','Sundry Creditors for Purchase'),\
			dbconnect.subGroups('6','Building'),\
			dbconnect.subGroups('6','Furniture'),\
			dbconnect.subGroups('6','Land'),\
			dbconnect.subGroups('6','Plant & Machinery'),\
			dbconnect.subGroups('9','Investment in Shares & Debentures'),\
			dbconnect.subGroups('9','Investment in Bank Deposits'),\
			dbconnect.subGroups('11','Secured'),\
			dbconnect.subGroups('11','Unsecured')\
		])
		
		Session.commit()

		Session.add_all([\
			dbconnect.Flags(None,'automatic',0),
			dbconnect.Flags(None,'manually',0)\
		])
		Session.commit()

		Session.close()
		connection.close()
		return True,self.client_id
Exemplo n.º 8
0
 def __init__(self):
     self.connection = getConnection()
Exemplo n.º 9
0
def main():

    epd = epd2in9.EPD()
    epd.init(epd.lut_full_update)

    # For simplicity, the arguments are explicit numerical coordinates
    image = Image.new('1', (epd2in9.EPD_WIDTH, epd2in9.EPD_HEIGHT),
                      255)  # 255: clear the frame

    db = dbconnect.getConnection()
    ##
    # there are 2 memory areas embedded in the e-paper display
    # and once the display is refreshed, the memory area will be auto-toggled,
    # i.e. the next action of SetFrameMemory will set the other memory area
    # therefore you have to set the frame memory twice.
    ##
    epd.clear_frame_memory(0xFF)
    epd.display_frame()
    epd.clear_frame_memory(0xFF)
    epd.display_frame()

    # for partial update
    epd.init(epd.lut_partial_update)
    #  image = Image.open('monocolor.bmp')
    ##
    # there are 2 memory areas embedded in the e-paper display
    # and once the display is refreshed, the memory area will be auto-toggled,
    # i.e. the next action of SetFrameMemory will set the other memory area
    # therefore you have to set the frame memory twice.
    ##

    #Image.new(mode,size,colour)  size is w,h tuple
    time_image = Image.new('1', (96 * 2, 32 * 2), 255)  # 255: clear the frame

    #Create a drawing object
    draw = ImageDraw.Draw(time_image)

    temp_image = Image.new('1', (96, 32), 255)  # 255: clear the frame
    draw_temp = ImageDraw.Draw(temp_image)

    hum_image = Image.new('1', (96, 32), 255)  # 255: clear the frame
    draw_hum = ImageDraw.Draw(hum_image)

    db_image = Image.new('1', (96, 32), 255)
    draw_db = ImageDraw.Draw(db_image)

    small_font = ImageFont.truetype(
        '/usr/share/fonts/truetype/droid/DroidSans.ttf', 32)

    #font = ImageFont.truetype('/usr/share/fonts/truetype/freefont/FreeMonoBold.ttf', 32) #font file, size
    font = ImageFont.truetype('/usr/share/fonts/truetype/droid/DroidSans.ttf',
                              64)  #font file, size

    image_width, image_height = time_image.size
    temp_width, temp_height = temp_image.size
    hum_width, hum_height = hum_image.size
    db_width, db_height = db_image.size

    readings = 0
    readingsBeforeSaving = 6 * 5  #we're waiting 10 secs, so 6 per min x 5  = 5 mins
    lastDBWrite = True

    readings = readingsBeforeSaving  #Trigger immediate db write

    while (True):
        #TIME
        # draw a rectangle to clear the image
        draw.rectangle((0, 0, image_width, image_height), fill=255)
        draw.text((0, 0), time.strftime('%H:%M'), font=font, fill=0)
        epd.set_frame_memory(time_image.rotate(270), 50, 80)

        #TEMP
        humidity, temperature = Adafruit_DHT.read_retry(
            Adafruit_DHT.AM2302, 4)  #sensor type, gpio pin
        draw_temp.rectangle((0, 0, temp_width, temp_height), fill=255)
        if humidity is not None and temperature is not None:
            draw_temp.text((0, 0),
                           '{0:0.1f}*'.format(temperature),
                           font=small_font,
                           fill=0)
        else:
            draw_temp.text((0, 0), '??', font=small_font, fill=0)
        epd.set_frame_memory(temp_image.rotate(270), 10, 10)

        #HUMIDITY
        draw_hum.rectangle((0, 0, hum_width, hum_height), fill=255)
        if humidity is not None and temperature is not None:
            draw_hum.text((0, 0),
                          '{0:0.1f}%'.format(humidity),
                          font=small_font,
                          fill=0)
        else:
            draw_hum.text((0, 0), '??', font=small_font, fill=0)
        epd.set_frame_memory(hum_image.rotate(270), 10, 200)

        if readings >= readingsBeforeSaving:
            if db is not None and db.is_connected():
                lastDBWrite = dbconnect.saveTempHumid(db, temperature,
                                                      humidity)
                readings = 0
                #clear error on display
                draw_db.rectangle((0, 0, db_width, db_height), fill=255)
                epd.set_frame_memory(db_image.rotate(270), 10, 100)
            else:
                log.error(
                    "No connection, or last write failed - attempting reconnect"
                )
                db = dbconnect.getConnection(
                )  #no db connection, so try and get one
                #display error on display
                draw_db.rectangle((0, 0, db_width, db_height), fill=255)
                draw_db.text((0, 0), 'No DB', font=small_font, fill=0)
                epd.set_frame_memory(db_image.rotate(270), 10, 100)

        epd.display_frame()
        time.sleep(10)
        readings = readings + 1
Exemplo n.º 10
0
	def xmlrpc_Deploy(self,queryParams):
		"""
		Purpose: This function deploys a database instance for
			an organisation for a given financial year.
			The function will generate the database name 
			based on the organisation name provided The name 
			of the database is a combination of, 
			First character of organisation name,
			time stap as "dd-mm-yyyy-hh-MM-ss-ms" 
			An entry will be made in the xml file 
			for the currosponding organisation.
			
		Input: [organisation name,From date,to
			date,organisation type] 
			
		Output: Returns boolean True and client_id
			
		"""
		queryParams = blankspace.remove_whitespaces(queryParams)
		abtconf=et.parse("/opt/abt/abt.xml")
		abtroot = abtconf.getroot()	
		org = et.SubElement(abtroot,"organisation") #creating an organisation tag
		org_name = et.SubElement(org,"orgname")
		# assigning client queryparams values to variables
		name_of_org = queryParams[0] # name of organisation
		db_from_date = queryParams[1]# from date
		db_to_date = queryParams[2] # to date
		organisationType = queryParams[3] # organisation type
		org_name.text = name_of_org #assigning orgnisation name value in orgname tag text of abt.xml
		financial_year_from = et.SubElement(org,"financial_year_from") #creating a new tag for financial year from-to	
		financial_year_from.text = db_from_date
		financial_year_to = et.SubElement(org,"financial_year_to")
		financial_year_to.text = db_to_date
		dbname = et.SubElement(org,"dbname") 
		
		#creating database name for organisation		
		org_db_name = name_of_org[0:1]
		time = datetime.datetime.now()
		str_time = str(time.microsecond)	
		new_microsecond = str_time[0:2]		
		result_dbname = org_db_name + str(time.year) + str(time.month) + str(time.day) + str(time.hour)\
		 		+ str(time.minute) + str(time.second) + new_microsecond
			
		dbname.text = result_dbname #assigning created database name value in dbname tag text of abt.xml
		
		abtconf.write("/opt/abt/abt.xml")
		# getting client_id for the given orgnisation and from and to date
		self.client_id = dbconnect.getConnection([name_of_org,db_from_date,db_to_date])
		
		try:
			metadata = dbconnect.Base.metadata
			metadata.create_all(dbconnect.engines[self.client_id])
		except:
			print "cannot create metadata"
			
		Session = scoped_session(sessionmaker(bind=dbconnect.engines[self.client_id]))
			
		dbconnect.engines[self.client_id].execute(\
			"create view view_account as \
			select groups.groupname, account.accountcode, account.accountname, account.subgroupcode\
			from groups, account where groups.groupcode = account.groupcode\
			order by groupname;")
			
		dbconnect.engines[self.client_id].execute(\
			"create view view_voucherbook as \
			select voucher_master.vouchercode,voucher_master.flag,voucher_master.reference,\
			voucher_master.voucherdate,voucher_master.reffdate,voucher_master.vouchertype,account.accountname\
			as account_name,voucher_details.typeflag,voucher_details.amount,\
			voucher_master.narration,voucher_master.projectcode\
			from voucher_master,voucher_details,account as account \
			where voucher_master.vouchercode = voucher_details.vouchercode \
			and voucher_details.accountcode = account.accountcode;")
		dbconnect.engines[self.client_id].execute(\
			"create view view_group_subgroup as \
			select groups.groupcode, groups.groupname,subgroups.subgroupcode,subgroups.subgroupname\
			from groups, subgroups where groups.groupcode = subgroups.groupcode \
			order by groupname;")
		dbconnect.engines[self.client_id].execute(\
			"create view group_subgroup_account as select groups.groupname,\
			subgroups.subgroupname,account.accountcode,account.accountname,account.openingbalance,\
			account.balance\
			from groups join account on (groups.groupcode = account.groupcode)\
			left outer join subgroups\
			on (account.subgroupcode = subgroups.subgroupcode) order by groupname;")
		connection = dbconnect.engines[self.client_id].raw_connection()
		cur = connection.cursor()
		
		if (organisationType == "Profit Making"):

			Session.add_all([\
				dbconnect.Groups('Capital',''),\
				dbconnect.Groups('Current Asset',''),\
				dbconnect.Groups('Current Liability',''),\
				dbconnect.Groups('Direct Income','Income refers to consumption\
		opportunity gained by an entity within a specified time frame.'),\
				dbconnect.Groups('Direct Expense','This are the expenses to be incurred for\
		operating the buisness.'),\
				dbconnect.Groups('Fixed Assets',''),\
				dbconnect.Groups('Indirect Income','Income refers to consumption opportunity\
		gained by an entity within a specified time frame.'),\
				dbconnect.Groups('Indirect Expense','This are the expenses to be incurred\
		for operating the buisness.'),\
				dbconnect.Groups('Investment',''),\
				dbconnect.Groups('Loans(Asset)',''),\
				dbconnect.Groups('Loans(Liability)',''),\
				dbconnect.Groups('Reserves',''),\
				dbconnect.Groups('Miscellaneous Expenses(Asset)','')\
			])
			Session.commit()
		
		else:
			Session.add_all([\
				dbconnect.Groups('Corpus',''),\
				dbconnect.Groups('Current Asset',''),\
				dbconnect.Groups('Current Liability',''),\
				dbconnect.Groups('Direct Income','Income refers to consumption\
		opportunity gained by an entity within a specified time frame.'),\
				dbconnect.Groups('Direct Expense','This are the \
		expenses to be incurred for operating the buisness.'),\
				dbconnect.Groups('Fixed Assets',''),\
				dbconnect.Groups('Indirect Income','Income refers to consumption \
		opportunity gained by an entity within a specified time frame.'),\
				dbconnect.Groups('Indirect Expense','This are the \
		expenses to be incurred for operating the buisness.'),\
				dbconnect.Groups('Investment',''),\
				dbconnect.Groups('Loans(Asset)',''),\
				dbconnect.Groups('Loans(Liability)',''),\
				dbconnect.Groups('Reserves',''),\
				dbconnect.Groups('Miscellaneous Expenses(Asset)','')\
			])
			Session.commit()
		
		Session.add_all([\
			dbconnect.subGroups('2','Bank'),\
			dbconnect.subGroups('2','Cash'),\
			dbconnect.subGroups('2','Inventory'),\
			dbconnect.subGroups('2','Loans & Advance'),\
			dbconnect.subGroups('2','Sundry Debtors'),\
			dbconnect.subGroups('3','Provisions'),
			dbconnect.subGroups('3','Sundry Creditors for Expense'),\
			dbconnect.subGroups('3','Sundry Creditors for Purchase'),\
			dbconnect.subGroups('6','Building'),\
			dbconnect.subGroups('6','Furniture'),\
			dbconnect.subGroups('6','Land'),\
			dbconnect.subGroups('6','Plant & Machinery'),\
			dbconnect.subGroups('9','Investment in Shares & Debentures'),\
			dbconnect.subGroups('9','Investment in Bank Deposits'),\
			dbconnect.subGroups('11','Secured'),\
			dbconnect.subGroups('11','Unsecured')\
		])
		
		Session.commit()

		Session.add_all([\
			dbconnect.Flags(None,'mandatory'),\
			dbconnect.Flags(None,'automatic')\
		])
		Session.commit()

		Session.close()
		connection.close()
		return True,self.client_id
Exemplo n.º 11
0
    def xmlrpc_Deploy(self, queryParams):
        """
		Purpose: This function deploys a database instance for
			an organisation for a given financial year.
			The function will generate the database name 
			based on the organisation name provided The name 
			of the database is a combination of, 
			First character of organisation name,
			time stap as "dd-mm-yyyy-hh-MM-ss-ms" 
			An entry will be made in the xml file 
			for the currosponding organisation.
			
		Input: [organisation name,From date,to
			date,organisation type] 
			
		Output: Returns boolean True and client_id
			
		"""
        queryParams = blankspace.remove_whitespaces(queryParams)
        abtconf = et.parse("/opt/abt/abt.xml")
        abtroot = abtconf.getroot()
        org = et.SubElement(abtroot, "organisation")  # creating an organisation tag
        org_name = et.SubElement(org, "orgname")
        # assigning client queryparams values to variables
        name_of_org = queryParams[0]  # name of organisation
        db_from_date = queryParams[1]  # from date
        db_to_date = queryParams[2]  # to date
        organisationType = queryParams[3]  # organisation type
        org_name.text = name_of_org  # assigning orgnisation name value in orgname tag text of abt.xml
        financial_year_from = et.SubElement(org, "financial_year_from")  # creating a new tag for financial year from-to
        financial_year_from.text = db_from_date
        financial_year_to = et.SubElement(org, "financial_year_to")
        financial_year_to.text = db_to_date
        dbname = et.SubElement(org, "dbname")

        # creating database name for organisation
        org_db_name = name_of_org[0:1]
        time = datetime.datetime.now()
        str_time = str(time.microsecond)
        new_microsecond = str_time[0:2]
        result_dbname = (
            org_db_name
            + str(time.year)
            + str(time.month)
            + str(time.day)
            + str(time.hour)
            + str(time.minute)
            + str(time.second)
            + new_microsecond
        )

        dbname.text = result_dbname  # assigning created database name value in dbname tag text of abt.xml

        abtconf.write("/opt/abt/abt.xml")
        # getting client_id for the given orgnisation and from and to date
        self.client_id = dbconnect.getConnection([name_of_org, db_from_date, db_to_date])

        try:
            metadata = dbconnect.Base.metadata
            metadata.create_all(dbconnect.engines[self.client_id])
        except:
            print "cannot create metadata"

        Session = scoped_session(sessionmaker(bind=dbconnect.engines[self.client_id]))

        dbconnect.engines[self.client_id].execute(
            "create view view_account as \
			select groups.groupname, account.accountcode, account.accountname, account.subgroupcode\
			from groups, account where groups.groupcode = account.groupcode\
			order by groupname;"
        )

        dbconnect.engines[self.client_id].execute(
            "create view view_voucherbook as \
			select voucher_master.vouchercode,voucher_master.flag,voucher_master.reference,\
			voucher_master.voucherdate,voucher_master.reffdate,voucher_master.vouchertype,account.accountname\
			as account_name,voucher_details.typeflag,voucher_details.amount,\
			voucher_master.narration,voucher_master.projectcode\
			from voucher_master,voucher_details,account as account \
			where voucher_master.vouchercode = voucher_details.vouchercode \
			and voucher_details.accountcode = account.accountcode;"
        )
        dbconnect.engines[self.client_id].execute(
            "create view view_group_subgroup as \
			select groups.groupcode, groups.groupname,subgroups.subgroupcode,subgroups.subgroupname\
			from groups, subgroups where groups.groupcode = subgroups.groupcode \
			order by groupname;"
        )
        dbconnect.engines[self.client_id].execute(
            "create view group_subgroup_account as select groups.groupname,\
			subgroups.subgroupname,account.accountcode,account.accountname,account.openingbalance,\
			account.balance\
			from groups join account on (groups.groupcode = account.groupcode)\
			left outer join subgroups\
			on (account.subgroupcode = subgroups.subgroupcode) order by groupname;"
        )
        connection = dbconnect.engines[self.client_id].raw_connection()
        cur = connection.cursor()

        if organisationType == "Profit Making":

            Session.add_all(
                [
                    dbconnect.Groups("Capital", ""),
                    dbconnect.Groups("Current Asset", ""),
                    dbconnect.Groups("Current Liability", ""),
                    dbconnect.Groups(
                        "Direct Income",
                        "Income refers to consumption\
		opportunity gained by an entity within a specified time frame.",
                    ),
                    dbconnect.Groups(
                        "Direct Expense",
                        "This are the expenses to be incurred for\
		operating the buisness.",
                    ),
                    dbconnect.Groups("Fixed Assets", ""),
                    dbconnect.Groups(
                        "Indirect Income",
                        "Income refers to consumption opportunity\
		gained by an entity within a specified time frame.",
                    ),
                    dbconnect.Groups(
                        "Indirect Expense",
                        "This are the expenses to be incurred\
		for operating the buisness.",
                    ),
                    dbconnect.Groups("Investment", ""),
                    dbconnect.Groups("Loans(Asset)", ""),
                    dbconnect.Groups("Loans(Liability)", ""),
                    dbconnect.Groups("Reserves", ""),
                    dbconnect.Groups("Miscellaneous Expenses(Asset)", ""),
                ]
            )
            Session.commit()

        else:
            Session.add_all(
                [
                    dbconnect.Groups("Corpus", ""),
                    dbconnect.Groups("Current Asset", ""),
                    dbconnect.Groups("Current Liability", ""),
                    dbconnect.Groups(
                        "Direct Income",
                        "Income refers to consumption\
		opportunity gained by an entity within a specified time frame.",
                    ),
                    dbconnect.Groups(
                        "Direct Expense",
                        "This are the \
		expenses to be incurred for operating the buisness.",
                    ),
                    dbconnect.Groups("Fixed Assets", ""),
                    dbconnect.Groups(
                        "Indirect Income",
                        "Income refers to consumption \
		opportunity gained by an entity within a specified time frame.",
                    ),
                    dbconnect.Groups(
                        "Indirect Expense",
                        "This are the \
		expenses to be incurred for operating the buisness.",
                    ),
                    dbconnect.Groups("Investment", ""),
                    dbconnect.Groups("Loans(Asset)", ""),
                    dbconnect.Groups("Loans(Liability)", ""),
                    dbconnect.Groups("Reserves", ""),
                    dbconnect.Groups("Miscellaneous Expenses(Asset)", ""),
                ]
            )
            Session.commit()

        Session.add_all(
            [
                dbconnect.subGroups("2", "Bank"),
                dbconnect.subGroups("2", "Cash"),
                dbconnect.subGroups("2", "Inventory"),
                dbconnect.subGroups("2", "Loans & Advance"),
                dbconnect.subGroups("2", "Sundry Debtors"),
                dbconnect.subGroups("3", "Provisions"),
                dbconnect.subGroups("3", "Sundry Creditors for Expense"),
                dbconnect.subGroups("3", "Sundry Creditors for Purchase"),
                dbconnect.subGroups("6", "Building"),
                dbconnect.subGroups("6", "Furniture"),
                dbconnect.subGroups("6", "Land"),
                dbconnect.subGroups("6", "Plant & Machinery"),
                dbconnect.subGroups("9", "Investment in Shares & Debentures"),
                dbconnect.subGroups("9", "Investment in Bank Deposits"),
                dbconnect.subGroups("11", "Secured"),
                dbconnect.subGroups("11", "Unsecured"),
            ]
        )

        Session.commit()

        Session.add_all([dbconnect.Flags(None, "mandatory"), dbconnect.Flags(None, "automatic")])
        Session.commit()

        Session.close()
        connection.close()
        return True, self.client_id
Exemplo n.º 12
0
    with conn.cursor() as cur:
        cur.execute(
            'insert into ' + schema + '.version (version) values (%s);',
            (version, ))
        conn.commit()

    readassay()
    readcitation()
    readdatapoint()
    readfact()
    readtarget()
    readsdfile()


conn = getConnection()
initdb()
load()
mydir = os.path.dirname(os.path.realpath(__file__))

# apply indices
sqlfromfile(mydir + '/rmc_index')

with conn.cursor() as cur:
    print('switching schema names')
    cur.execute('drop schema if exists rmc cascade')
    conn.commit()
    cur.execute('alter schema ' + schema + ' rename to rmc')
    conn.commit()

conn.close()