def ret_data(dbtype, rec_data,user_id=None, tenant=None):
df = pd.DataFrame()
for i in range(0,len(rec_data)):
tables = rec_data[i].keys()
fields = rec_data[i].values()
fields = fields[0]
fields_str = ', '.join(fields)
tables_str = ', '.join(tables)
if dbtype.lower() == 'mssql':
try:
query = 'SELECT {0} FROM {1}'.format(fields_str, tables_str)
result = mssql.execute_query(query)
except Exception, err:
result = cmg.format_response(False, err, 'Error occurred while getting data from MSSQL!', sys.exc_info())
return result
elif dbtype.lower() == 'bigquery':
try:
query = 'SELECT {0} FROM {1}'.format(fields_str, tables_str)
result = BQ.execute_query(query, user_id=user_id, tenant=tenant)
except Exception, err:
result = cmg.format_response(False, err, 'Error occurred while getting data from BigQuery Handler!', sys.exc_info())
return result
def file_upload(params, file_obj,data_set_name, user_id, domain):
start_time = datetime.datetime.now()
print "File received.. Uploading started.."
o_data = params.other_data
if o_data == 'logo':
upload_path = conf.get_conf('FilePathConfig.ini', 'User Files')[
'Path'] + '/digin_user_data/' + user_id + '/' + domain + '/logos'
try:
os.makedirs(upload_path)
except OSError:
if not os.path.isdir(upload_path):
raise
if 'file' in file_obj: # to check if the file-object is created
try:
filename = file_obj.file.filename.replace('\\',
'/') # replaces the windows-style slashes with linux ones.
# filename=filepath.split('/')[-1] # splits the and chooses the last part (the filename with extension)
fout = open(upload_path + '/' + filename,
'wb') # creates the file where the uploaded file should be stored
fout.write(file_obj.file.file.read()) # writes the uploaded file to the newly created file.
fout.close() # closes the file, upload complete.
except Exception, err:
print err
return cmg.format_response(False, err, "Error occurred while uploading file", sys.exc_info())
uploaded_time = datetime.datetime.now()
time_taken = uploaded_time - start_time
print "Upload completed! Time taken - " + str(time_taken)
return cmg.format_response(True, 1, "File Upload successful!")
def fb_posts_with_summary(params):
token = params.token
limit = ''
since = ''
until = ''
page = 'me'
try:
page = str(params.page)
except AttributeError:
pass
try:
limit = params.limit
except AttributeError:
pass
try:
since = params.since
except AttributeError:
pass
try:
until = params.until
except AttributeError:
pass
logger.info('Request received: %s' % params.values())
try:
data_ = FB.get_page_posts(token, limit, since, until, page=page)
data = cmg.format_response(True,data_,'Data successfully processed!')
except ValueError, err:
data = cmg.format_response(False,err,'Error validating access token: This may be because the user logged out or may be due to a system error.',sys.exc_info())
def fb_promotional_info(params):
token = params.token
promotional_name = params.metric_name
try:
data_ = FB.get_promotional_info(token, promotional_name)
data = cmg.format_response(True,data_,'Data successfully processed!')
except ValueError, err:
data = cmg.format_response(False,err,'Error validating access token: This may be because the user logged out or may be due to a system error.',sys.exc_info())
def twitter_acc_info(params):
tokens = ast.literal_eval(params.tokens)
id_list = ast.literal_eval(params.ids)
try:
api = SMAuth.tweepy_auth(tokens['consumer_key'], tokens['consumer_secret'], tokens['access_token'], tokens['access_token_secret'])
data_ = Tw.get_account_summary(api, id_list)
data = cmg.format_response(True,data_,'Data successfully processed!')
except Exception, err:
data = cmg.format_response(False,err,'Error occurred while getting data from Twitter API',sys.exc_info())
def get_tables(params):
datasetID = params.dataSetName
db = params.db
if db.lower() == 'bigquery':
try:
result = bqhandler.get_tables(datasetID)
except Exception, err:
return comm.format_response(False,err,"Error Occurred when retrieving tables!",exception=sys.exc_info())
return comm.format_response(True,result,"Tables retrieved!",exception=None)
def fb_page_user_locations(params):
token = params.token
logger.info('Requested received: %s' % params.values())
try:
data_ = FB.get_page_fans_city(token)
data = cmg.format_response(True,data_,'Data successfully processed!')
except ValueError, err:
data = cmg.format_response(False,err,'Error validating access token: This may be because the user logged out or may be due to a system error.',sys.exc_info())
return data
def build_word_cloud(params):
tokens = ast.literal_eval(params.tokens)
hash_tag = params.hash_tag
try:
api = SMAuth.tweepy_auth(tokens['consumer_key'], tokens['consumer_secret'], tokens['access_token'], tokens['access_token_secret'])
data_ = Tw.hashtag_search(api, hash_tag)
wc_data = json.loads(wc.wordcloud_json(data_))
data = cmg.format_response(True,wc_data,'Data successfully processed!')
except ValueError, err:
data = cmg.format_response(False,err,'Error validating access token: This may be because the user logged out or may be due to a system error.',sys.exc_info())
def clear_cache():
if cache_state == 0: return True
with get_connection() as conn:
for table in ast.literal_eval(tables):
try:
conn.query("TRUNCATE TABLE {0}".format(table))
except Exception, err:
print "Error clearing cache"
print err
return cmg.format_response(False,None,"Error Occurred while clearing cache!", exception = sys.exc_info())
return cmg.format_response(True,None,"Cache cleared successfully!")
def get_report_names(params):
names = []
for name in os.listdir(Reports_path):
if os.path.isdir(os.path.join(Reports_path, name)):
names.append(name)
result = cmg.format_response(True,names,'Data successfully processed!',None)
return result
def get_user_settings(user_id, domain):
query = "SELECT components, user_role, cache_lifetime, widget_limit, " \
"query_limit, logo_path, dp_path, theme_config, modified_date_time, created_date_time, " \
"domain FROM digin_user_settings WHERE user_id = '{0}' AND domain = '{1}'".format(user_id, domain)
logo_path = conf.get_conf('FilePathConfig.ini','User Files')['Path']
document_root = conf.get_conf('FilePathConfig.ini','Document Root')['Path']
path = re.sub(document_root, '', logo_path)
try:
user_data = CC.get_data(query)
if user_data['rows'] == ():
logger.info('No user settings saved for given user ' + user_id)
return cmg.format_response(True,user_id,"No user settings saved for given user and domain")
data ={
'components': user_data['rows'][0][0],
'user_role': user_data['rows'][0][1],
'cache_lifetime': int(user_data['rows'][0][2]),
'widget_limit': int(user_data['rows'][0][3]),
'query_limit': int(user_data['rows'][0][4]),
'logo_path': path+user_data['rows'][0][5],
'dp_path': path+user_data['rows'][0][6],
'theme_config': user_data['rows'][0][7],
'modified_date_time': user_data['rows'][0][8],
'created_date_time': user_data['rows'][0][9],
'domain': user_data['rows'][0][10]
}
except Exception, err:
logger.error("Error retrieving user settings")
logger.error(err)
print "Error retrieving user settings"
print err
raise
def build_word_cloud_fb(params):
#source = str(params.source)
try:
limit = params.limit
except AttributeError:
limit = ''
try:
since = params.since
except AttributeError:
since = ''
try:
until = params.until
except AttributeError:
until = ''
try:
post_ids = ast.literal_eval(params.post_ids)
except AttributeError:
post_ids = None
page = 'me'
try:
page = str(params.page)
except AttributeError:
pass
token = params.token
try:
data = FB.get_page_posts_comments(token, limit, since, until, page, post_ids)
except ValueError, err:
return cmg.format_response(False,err,'Error validating access token: This may be because the user logged out or may be due to a system error.',sys.exc_info())
def get_package_detail(self):
query = "SELECT " \
"a.package_id, " \
"a.package_name, " \
"a.package_attribute, " \
"SUM(a.package_value), " \
"SUM(a.package_price), " \
"b.expiry_datetime, " \
"TIMESTAMPDIFF(DAY, CURRENT_TIMESTAMP, expiry_datetime) as remaining_days, " \
"CURRENT_TIMESTAMP > b.expiry_datetime " \
"FROM digin_packagedetails a " \
"INNER JOIN digin_tenant_package_details b " \
"ON a.package_id = b.package_id " \
"WHERE b.tenant_id = '{0}' AND b.package_status = 'current_package' " \
"GROUP BY a.package_id, a.package_name, a.package_attribute, b.expiry_datetime, remaining_days".format(self.tenant)
try:
result = db.get_data(query)['rows']
data_list = []
for row in result:
data = {'package_id': row[0],
'package_name': row[1],
'package_attribute': row[2],
'package_value_sum': row[3],
'package_price_sum': row[4],
'expiry_datetime': row[5],
'remaining_days': row[6],
'is_expired': bool(row[7])}
data_list.append(data)
except Exception, err:
print err
return cmg.format_response(False, err, "Error occurred while getting data", exception=sys.exc_info())
def delete_component(params, user_id, domain):
for comp in params:
print "Deleting component %s ..." % comp
if comp['permanent_delete']:
try:
p1 = threading.Thread(target=_permanent_delete_components,args=(comp['comp_id'],'digin_component_header',
user_id, domain))
p2 = threading.Thread(target=_permanent_delete_components,args=(comp['comp_id'],'digin_component_page_detail',
user_id, domain))
p3 = threading.Thread(target=_permanent_delete_components,args=(comp['comp_id'],'digin_component_detail',
user_id, domain))
p1.start()
p2.start()
p3.start()
p1.join()
p2.join()
p3.join()
except Exception, err:
print err
logger.error("Permanent deletion failed. %s" % err)
return cmg.format_response(False,0,"Error Occurred in deletion!", exception = sys.exc_info())
else:
try:
p1 = threading.Thread(target=_temporary_delete_components,args=(comp['comp_id'],'digin_component_header',
user_id, domain))
p2 = threading.Thread(target=_temporary_delete_components,args=(comp['comp_id'],'digin_component_page_detail',
user_id, domain))
p3 = threading.Thread(target=_temporary_delete_components,args=(comp['comp_id'],'digin_component_detail',
user_id, domain))
p1.start()
p2.start()
p3.start()
p1.join()
p2.join()
p3.join()
except Exception, err:
print err
logger.error("Temporary deletion failed. %s" % err)
return cmg.format_response(False,0,"Error Occurred in deletion!", exception = sys.exc_info())
def create_Dataset(params):
datasetID = params.dataSetName
db = params.db
if db.lower() == 'bigquery':
try:
result = bqhandler.create_dataset(datasetID)
return comm.format_response(True,result,"",exception=None)
except Exception, err:
print err
return False
def deactivate_packages(self):
""
if self.is_default:
try:
db.update_data('digin_tenant_package_details'," WHERE tenant_id = '{0}' AND package_id IN ({1},{2},{3},{4})".format(self.tenant,int(free_package),int(default_1),int(default_2),int(default_3)),
package_status = 'deactivated')
except Exception, err:
print "Error inserting to DB!"
return cmg.format_response(False, err, "Error occurred while deactivate_packages.. \n" + str(err),
exception=sys.exc_info())
def get_usage(self):
if self.attribute == "data":
try:
summary = db.get_data(" SELECT sum(value) FROM digin_usage_summary "
" WHERE tenant = '{0}' AND parameter = 'download_bq' OR 'upload_size_bq' ".format(self.tenant))['rows']
return summary[0]
except Exception, err:
print "Error inserting to DB!"
return cmg.format_response(False, err, "Error occurred while getting packages details .. \n" + str(err),
exception=sys.exc_info())
def executeKTR(params):
eportName = params.ReportName
paramaeters = ast.literal_eval(params.parameters)
strJSON = json.dumps(paramaeters)
reportName = 'C:\\Reports\\' + eportName + "\\" +eportName
if(os.path.isfile(reportName+'.html')):
os.remove(reportName+'.html')
renderedReport = 'http://104.131.48.155/reports/' +eportName + '/' + eportName
args = ['ktrjob.jar',reportName,strJSON]
result = cmg.format_response(True,jarWrapper(*args),renderedReport +'.html',None)
return result
def slr_get(dbtype, db, table,x,y,predict):
#http://localhost:8080/linear?dbtype=MSSQL&db=Demo&table=OrdersDK&x=Unit_Price&y=Sales&predict=[5,8]
if dbtype == 'MSSQL':
try:
query = 'SELECT {0} as x, {1} as y From {2}'.format(x, y, table)
result = mssql.execute_query(query)
except Exception, err:
result = cmg.format_response(False, None, 'Error occurred while getting data from MSSQL!', sys.exc_info())
return result
def do_share(self):
self._users_groups_segregator()
print "Component sharing UserIDs: {0}, GroupIds: {1}, Tenant: {2}, ComponentIds: {3}".format(self.user_ids,self.group_ids,self.tenant, self.component_ids)
if self.group_ids:
result = self._set_group_user_ids()
if self.is_success:
data = []
for user in self.user_ids:
for component in self.component_ids:
d = {'component_id': component,
'user_id':user,
'type':'dashboard',
'domain':self.tenant}
data.append(d)
try:
db.CacheController.insert_data(data,'digin_component_access_details')
except Exception, err:
print err
return cmg.format_response(False, err, "Component already shared!",exception=sys.exc_info())
return cmg.format_response(True,0,"Components shared successfully")
def get_fields(params):
tablename = params.tableName
db = params.db
if db.lower() == 'bigquery':
datasetname = params.dataSetName
fields = bqhandler.get_fields(datasetname, tablename)
return comm.format_response(True,fields,"",exception=None)
elif db.lower() == 'mssql':
fields = mssqlhandler.get_fields(tablename)
return comm.format_response(True,fields,"",exception=None)
elif db.lower() == 'postgresql':
schema_name = params.schema
colnames = pgsqlhandler.get_fields(tablename,schema_name)
return comm.format_response(True,colnames,"",exception=None)
elif db.lower() == 'mysql':
colnames = mysqlhandler.get_fields(params.tableName)
return comm.format_response(True,colnames,"",exception=None)
else:
return comm.format_response(False,db,"DB not implemented!",exception=None)
def get_all_components(params, user_id, domain):
try:
data = CC.get_data("SELECT h.digin_comp_id, h.digin_comp_name, h.digin_comp_type "
"FROM digin_component_access_details a "
"INNER JOIN digin_component_header h "
"ON a.component_id = h.digin_comp_id "
"WHERE h.is_active = TRUE AND a.domain = '{0}' AND a.user_id = '{1}' "
"AND a.type IN ('dashboard','report')".format(domain, user_id))
print data["rows"]
comps = []
for comp in data["rows"]:
comp_dict ={}
comp_dict["compID"]= comp[0]
comp_dict["compName"]=comp[1]
comp_dict["compType"]=comp[2]
comps.append(comp_dict)
return cmg.format_response(True,comps,"Successful!")
except Exception, err:
logger.error("Error getting data from cache. %s" % err)
return cmg.format_response(False,0,"Error Occurred!", exception = sys.exc_info())
def fb_overview(params):
token = params.token
metric_names = None
try:
metric_names = ast.literal_eval(params.metric_names)
except AttributeError:
pass
since = None
until = None
try:
since = params.since
until = params.until
except AttributeError:
pass
logger.info('Requested received: %s' % params.values())
# data = json.dumps(fb.insight_metric(token, metric_name, since, until))
try:
data_ = FB.get_overview(token, metric_names, since, until)
data = cmg.format_response(True,data_,'Data successfully processed!')
except ValueError, err:
data = cmg.format_response(False,err,'Error validating access token: This may be because the user logged out or may be due to a system error.',sys.exc_info())
def _set_group_user_ids(self):
user_emails = []
for _id in self.group_ids:
user_emails.append(auth.get_group_users(self.tenant, _id))
for email in user_emails[0]:
query = "SELECT user_id, email FROM digin_user_settings WHERE email = '{0}'".format(email['Id'])
user_id = db.CacheController.get_data(query)['rows']
if user_id != ():
self.user_ids.append(user_id[0][0])
else:
self.is_success = False
return cmg.format_response(False, email, "Error sharing components no user registered in DigIn", exception=None)
def get_rating_summary(self):
user_dict = {}
if self.security_level == 'admin':
summary = db.get_data("SELECT user_id, parameter, SUM(value) as value FROM digin_usage_summary "
"WHERE tenant = '{0}' GROUP BY user_id, parameter".format(self.tenant))['rows']
else:
summary = db.get_data("SELECT user_id, parameter, value FROM digin_usage_summary "
"WHERE user_id = '{0}' AND tenant = '{1}'".format(self.user_id, self.tenant))['rows']
for parameter in summary:
if parameter[0] in user_dict:
user_dict[parameter[0]][parameter[1]]=parameter[2]
else:
user_dict[parameter[0]] = {parameter[1]:parameter[2]}
rated_dict = {'usage': [{self.tenant:user_dict}],
'exceed_blocked': euc.ExceedUsageCalculator(tenant=self.tenant, attribute=None).calculation()}
return cmg.format_response('True',rated_dict,"Usage data retrieved")
def get_ledger(self):
# query = "SELECT " \
# "a.package_id, " \
# "a.package_name, " \
# "a.package_attribute, " \
# "a.package_value, " \
# "a.package_price, " \
# "b.expiry_datetime, " \
# "TIMESTAMPDIFF(DAY, CURRENT_TIMESTAMP, expiry_datetime) as remaining_days, " \
# "b.package_status, " \
# "b.created_datetime " \
# "FROM digin_packagedetails a " \
# "INNER JOIN digin_tenant_package_details b " \
# "ON a.package_id = b.package_id " \
# "WHERE b.tenant_id = '{0}' " \
# "AND b.created_datetime >= TIMESTAMP('{1}') AND b.created_datetime <= TIMESTAMP('{2}') " \
# "GROUP BY a.package_id, a.package_name, a.package_attribute, b.expiry_datetime, remaining_days " \
# "ORDER BY b.created_datetime ".format(self.tenant, self.start_date, self.end_date)
query = "SELECT " \
"package_id, " \
"expiry_datetime, " \
"TIMESTAMPDIFF(DAY, CURRENT_TIMESTAMP, expiry_datetime) as remaining_days, " \
"package_status, " \
"created_datetime " \
"FROM digin_tenant_package_details " \
"WHERE tenant_id = '{0}' " \
"AND created_datetime >= TIMESTAMP('{1}') AND created_datetime <= TIMESTAMP('{2}') " \
"ORDER BY created_datetime ".format(self.tenant, self.start_date, self.end_date)
try:
result = db.get_data(query)['rows']
data_list = []
for row in result:
data = {'package_id': row[0],
'package_Details': PackageProcessor(package_name=None, package_attribute=None, package_value=None, package_price=None,is_default=False, tenant =self.tenant,package_id=int(row[0])).get_package_attributes(),
'expiry_datetime': row[1],
'remaining_days': row[2],
'package_status': row[3],
'created_datetime': row[4]}
data_list.append(data)
except Exception, err:
print err
return cmg.format_response(False, err, "Error occurred while getting data", exception=sys.exc_info())
def Forecasting(params):
try:
fcast_days = int(params.fcast_days)
timesteps_per_day = int(params.steps_pday)
pred_error_level = float(params.pred_error_level)
model = str(params.model)
m = int(params.m)
#alpha = params.alpha
#beta = params.beta
#gamma = params.gamma
table_name = params.table_name
field_name_date = params.field_name_d
field_name_forecast = params.field_name_f
interval = str(params.interval)
db_type = params.dbtype
except Exception, err:
return cmg.format_response(False, err, 'Input parameters caused the service to raise an error',
sys.exc_info())
def set_initial_user_env(params,email,user_id,domain):
default_sys_settings = conf.get_conf('DefaultConfigurations.ini','System Settings')
dataset_name = email.replace(".", "_").replace("@","_")
if ast.literal_eval(default_sys_settings['signup_dataset_creation']):
db = params['db']
if db.lower() == 'bigquery':
logger.info("Creation of dataset started!")
print "Creation of dataset started!"
try:
result_ds= bq.create_dataset(dataset_name)
print result_ds
logger.info("Creation of dataset status " + str(result_ds))
print "Creation of dataset " + str(result_ds)
except Exception, err:
print err
print "Creation of dataset failed!"
return cmg.format_response(False,err,"Error Occurred while creating dataset in bigquery!",exception=sys.exc_info())
else:
raise
def set_packages(self):
time_now = datetime.datetime.now()
_, num_days = calendar.monthrange(time_now.year, time_now.month)
free_package = conf.get_conf('DefaultConfigurations.ini', 'Package Settings')['Free']
if self.package_id == int(free_package):
last_day = time_now + datetime.timedelta(days=30)
else:
last_day = datetime.datetime(time_now.year, time_now.month, num_days, 23, 59, 59)
tenant_package_mapping = [{'tenant_id': self.tenant,
'package_id': self.package_id,
'created_datetime': time_now,
'modified_datetime': time_now,
'expiry_datetime': last_day,
'package_status':'current_package'}]
try:
db.insert_data(tenant_package_mapping, 'digin_tenant_package_details')
except Exception, err:
print "Error inserting to cacheDB!"
return cmg.format_response(False, err, "Error occurred while inserting.. \n" + str(err),
exception=sys.exc_info())
def get_package_summary(self):
query = "SELECT " \
"a.package_attribute, " \
"SUM(a.package_value), " \
"SUM(a.package_price) " \
"FROM digin_packagedetails a " \
"INNER JOIN digin_tenant_package_details b " \
"ON a.package_id = b.package_id " \
"WHERE b.tenant_id = '{0}' AND b.package_status = 'current_package' " \
"GROUP BY a.package_attribute".format(self.tenant)
try:
result = db.get_data(query)['rows']
data_list = []
for row in result:
data = {'package_attribute': row[0],
'package_value_sum': row[1],
'package_price_sum': row[2]}
data_list.append(data)
except Exception, err:
print err
return cmg.format_response(False, err, "Error occurred while getting data", exception=sys.exc_info())
