def data_rollback2(request):
post_data = list(request.POST)
post_data = str(''.join(map(str, post_data)))
post_data = post_data.split('-')
dataType = post_data[0]
upload_id = post_data[1]
upload_id = int(''.join(map(str, upload_id)))
if dataType == 'Expenses':
qry = "SELECT av_engine.av_trxn_expenses_rollback_fn(%s, '%s', '%s', %s)" % (
request.user.company_id, request.user.company.short_name,
request.user.user_id, upload_id)
else:
qry = "SELECT av_engine.av_trxn_company_employees_rollback_fn(%s, '%s', '%s', %s)" % (
request.user.company_id, request.user.company.short_name,
request.user.user_id, upload_id)
utilb.customQryExc(qry)
AvLogDataUploads.objects.filter(data_upload_id=upload_id).update(
status='2 of 3 Master Updates In Progress')
json_data = json.dumps({
'status': 'success',
'message': '2 of 3 Master Updates In Progress'
})
utilb.logManage(request.user.company.id, request.user.company.short_name,
datetime.now(), 'User Activity', 'Rollback', 'Information',
'Progress', request.user.user_id, '1',
'Data Rollback In Progress')
return HttpResponse(json_data, content_type='application/json')
def upload_employeedata_step5(request):
req=request.POST
records = pe.get_records(file_name=req['file_name'])
chk=insertData(records,req,request.user.user_id,req['file_name'])
stat=None
if req['data_type']=='Employee':
sqlqry="SELECT av_engine.av_trxn_company_employees_load_fn(%s, '%s', '%s','%s')"%(req['comp_id'],req['comp_name'],request.user.user_id,req['upload_id'])
ret_msg=custom_query(sqlqry)
if ret_msg=='SUCCESS':
stat='success'
AvLogDataUploads.objects.filter(data_upload_id=req['upload_id']).update(no_of_records=chk,status='5 to 6 Master Updates Done',upload_or_rollback='Upload to Rollback')
else:
AvLogDataUploads.objects.filter(data_upload_id=req['upload_id']).update(no_of_records=chk,status='5 to 6 Master Updates Error',upload_or_rollback='Upload to Rollback')
stat='error'
elif req['data_type']=='Expenses':
sqlqry="SELECT av_engine.av_trxn_expenses_load_fn(%s, '%s', '%s','%s')"%(req['comp_id'],req['comp_name'],request.user.user_id,req['upload_id'])
ret_msg=custom_query(sqlqry)
if ret_msg=='SUCCESS':
stat='success'
AvLogDataUploads.objects.filter(data_upload_id=req['upload_id']).update(no_of_records=chk,status='5 to 6 Master Updates Done',upload_or_rollback='Upload to Rollback')
else:
stat='error'
AvLogDataUploads.objects.filter(data_upload_id=req['upload_id']).update(no_of_records=chk,status='5 to 6 Master Updates Error',upload_or_rollback='Upload to Rollback')
json_data = json.dumps( { 'status':stat,'message':'All records successfully inserted', 'chk':chk, 'file_name':req['file_name'], 'upload_id':req['upload_id'] } )
utilb.logManage(request.user.company.id,request.user.company.short_name,datetime.now(),'Data Load','Records Inserted','Information','Records Inserted',request.user.user_id,req['upload_id'],'uploadData')
return HttpResponse( json_data , content_type='application/json')
def upload_employeedata_step3(request):
req=request.POST
records = pe.get_records(file_name=req['file_name'])
stat=True
if req['data_type']=='Employee':
ls=[]
for record in records:
if record['Employee ID'] in ls:
stat=False
ls.append(record['Employee ID'])
elif req['data_type']=='Expenses':
ls=[]
for record in records:
if record['Report Line ID'] in ls:
stat=False
ls.append(record['Report Line ID'])
if stat==True:
status='success'
msg='All records is unique'
AvLogDataUploads.objects.filter(data_upload_id=req['upload_id']).update(status='4 of 6 File Formate Check')
utilb.logManage(request.user.company.id,request.user.company.short_name,datetime.now(),'Data Load','Duplicate Records','Information','Duplicate Records checked',request.user.user_id,req['upload_id'],'uploadData')
else:
status='danger'
msg='Some records are duplicate'
AvLogDataUploads.objects.filter(data_upload_id=req['upload_id']).update(status='2 of 6 File Formate Check')
utilb.logManage(request.user.company.id,request.user.company.short_name,datetime.now(),'Data Load','Duplicate Records','Information','Duplicate Records checked failed',request.user.user_id,req['upload_id'],'uploadData')
json_data = json.dumps( { 'status':status,'message':msg, 'file_name':req['file_name'], 'upload_id':req['upload_id'] } )
return HttpResponse( json_data , content_type='application/json')
def upload_employeedata_step5(request):
req = request.POST
records = pe.get_records(file_name=req['file_name'])
chk = insertData(records, req, request.user.user_id, req['file_name'])
stat = None
if req['data_type'] == 'Employee':
sqlqry = "SELECT av_engine.av_trxn_company_employees_load_fn(%s, '%s', '%s','%s')" % (
req['comp_id'], req['comp_name'], request.user.user_id,
req['upload_id'])
ret_msg = custom_query(sqlqry)
if ret_msg == 'SUCCESS':
stat = 'success'
AvLogDataUploads.objects.filter(
data_upload_id=req['upload_id']).update(
no_of_records=chk,
status='5 to 6 Master Updates Done',
upload_or_rollback='Upload to Rollback')
else:
AvLogDataUploads.objects.filter(
data_upload_id=req['upload_id']).update(
no_of_records=chk,
status='5 to 6 Master Updates Error',
upload_or_rollback='Upload to Rollback')
stat = 'error'
elif req['data_type'] == 'Expenses':
sqlqry = "SELECT av_engine.av_trxn_expenses_load_fn(%s, '%s', '%s','%s')" % (
req['comp_id'], req['comp_name'], request.user.user_id,
req['upload_id'])
ret_msg = custom_query(sqlqry)
if ret_msg == 'SUCCESS':
stat = 'success'
AvLogDataUploads.objects.filter(
data_upload_id=req['upload_id']).update(
no_of_records=chk,
status='5 to 6 Master Updates Done',
upload_or_rollback='Upload to Rollback')
else:
stat = 'error'
AvLogDataUploads.objects.filter(
data_upload_id=req['upload_id']).update(
no_of_records=chk,
status='5 to 6 Master Updates Error',
upload_or_rollback='Upload to Rollback')
json_data = json.dumps({
'status': stat,
'message': 'All records successfully inserted',
'chk': chk,
'file_name': req['file_name'],
'upload_id': req['upload_id']
})
utilb.logManage(request.user.company.id, request.user.company.short_name,
datetime.now(), 'Data Load', 'Records Inserted',
'Information', 'Records Inserted', request.user.user_id,
req['upload_id'], 'uploadData')
return HttpResponse(json_data, content_type='application/json')
def data_rollback(request):
post_data=list(request.POST)
post_data=str(''.join(map(str,post_data)))
post_data=post_data.split('-')
dataType=post_data[0]
upload_id=post_data[1]
upload_id=int(''.join(map(str,upload_id)))
AvLogDataUploads.objects.filter(data_upload_id=upload_id).update(status='1 of 3 Rollback In Progress')
json_data = json.dumps( { 'status':'success','message':'1 of 3 Rollback In Progress'} )
utilb.logManage(request.user.company.id,request.user.company.short_name,datetime.now(),'User Activity','Rollback','Information','Start',request.user.user_id,'1','Data Rollback Start')
return HttpResponse( json_data , content_type='application/json')
def upload_employeedata_step2(request):
req=request.POST
records = pe.get_records(file_name=req['file_name'])
if ('Employee ID' in records[0] and req['data_type']=='Employee') or ('Report ID' in records[0] and req['data_type']=='Expenses'):
json_data = json.dumps( { 'status':'success','message':'File formate is ok.', 'file_name':req['file_name'], 'upload_id':req['upload_id'] } )
AvLogDataUploads.objects.filter(data_upload_id=req['upload_id']).update(status='2 of 6 File Formate Check')
utilb.logManage(request.user.company.id,request.user.company.short_name,datetime.now(),'Data Load','Records Processed','Information','Checked file formate',request.user.user_id,req['upload_id'],'uploadData')
else:
json_data = json.dumps( { 'status':'danger','message':'File formate is not valid.', 'file_name':req['file_name'], 'upload_id':req['upload_id'] } )
AvLogDataUploads.objects.filter(data_upload_id=req['upload_id']).update(status='1 of 6 File Formate Check')
utilb.logManage(request.user.company.id,request.user.company.short_name,datetime.now(),'Data Load','Records Processed','Information','Checked file formate failed',request.user.user_id,req['upload_id'],'uploadData')
return HttpResponse( json_data , content_type='application/json')
def upload_employeedata_step3(request):
req = request.POST
records = pe.get_records(file_name=req['file_name'])
stat = True
if req['data_type'] == 'Employee':
ls = []
for record in records:
if record['Employee ID'] in ls:
stat = False
ls.append(record['Employee ID'])
elif req['data_type'] == 'Expenses':
ls = []
for record in records:
if record['Report Line ID'] in ls:
stat = False
ls.append(record['Report Line ID'])
if stat == True:
status = 'success'
msg = 'All records is unique'
AvLogDataUploads.objects.filter(
data_upload_id=req['upload_id']).update(
status='4 of 6 File Formate Check')
utilb.logManage(request.user.company.id,
request.user.company.short_name, datetime.now(),
'Data Load', 'Duplicate Records', 'Information',
'Duplicate Records checked', request.user.user_id,
req['upload_id'], 'uploadData')
else:
status = 'danger'
msg = 'Some records are duplicate'
AvLogDataUploads.objects.filter(
data_upload_id=req['upload_id']).update(
status='2 of 6 File Formate Check')
utilb.logManage(request.user.company.id,
request.user.company.short_name, datetime.now(),
'Data Load', 'Duplicate Records', 'Information',
'Duplicate Records checked failed',
request.user.user_id, req['upload_id'], 'uploadData')
json_data = json.dumps({
'status': status,
'message': msg,
'file_name': req['file_name'],
'upload_id': req['upload_id']
})
return HttpResponse(json_data, content_type='application/json')
def data_rollback3(request):
post_data=list(request.POST)
post_data=str(''.join(map(str,post_data)))
post_data=post_data.split('-')
dataType=post_data[0]
upload_id=post_data[1]
upload_id=int(''.join(map(str,upload_id)))
qry="SELECT av_engine.av_check_exp_engine_fn(%s, '%s', '%s', %s)"%(request.user.company_id,request.user.company.short_name,request.user.user_id,upload_id)
utilb.customQryExc(qry)
AvLogDataUploads.objects.filter(data_upload_id=upload_id).update(status='3 of 3 Rollback Done', upload_or_rollback='Rollbacked')
json_data = json.dumps( { 'status':'success','message':'3 of 3 Rollback Done'} )
utilb.logManage(request.user.company.id,request.user.company.short_name,datetime.now(),'User Activity','Rollback','Information','Done',request.user.user_id,'1','Data Rollback Done')
return HttpResponse( json_data , content_type='application/json')
def data_rollback(request):
post_data = list(request.POST)
post_data = str(''.join(map(str, post_data)))
post_data = post_data.split('-')
dataType = post_data[0]
upload_id = post_data[1]
upload_id = int(''.join(map(str, upload_id)))
AvLogDataUploads.objects.filter(data_upload_id=upload_id).update(
status='1 of 3 Rollback In Progress')
json_data = json.dumps({
'status': 'success',
'message': '1 of 3 Rollback In Progress'
})
utilb.logManage(request.user.company.id, request.user.company.short_name,
datetime.now(), 'User Activity', 'Rollback', 'Information',
'Start', request.user.user_id, '1', 'Data Rollback Start')
return HttpResponse(json_data, content_type='application/json')
def forgotPassword(request):
log_message= None
if request.user.is_authenticated():
return HttpResponseRedirect('/')
if request.method == "POST":
username = request.POST.get('username')
cmp = request.POST.get('companyname').strip()
parm = [username,cmp]
cursor = connection.cursor()
cursor.execute('SELECT u.*,c.short_name FROM av_master_company_users u LEFT JOIN av_master_company c ON u.company_id=c.id WHERE u.user_id = %s AND c.short_name = %s ', parm)
result=cursor.fetchone()
cursor.close()
if result!=None:
signer = Signer()
value = signer.sign(result[0])
v=value.split(':')
unique_id=get_random_string(length=16)
unqvalue = v[-1]+unique_id
AvMasterCompanyUsers.objects.filter(id=result[0]).update(token_id=unqvalue, token_date=datetime.now())
from_email = '*****@*****.**'
msg = """<p><strong style="color:#565252;">Dear </strong> %s,</p>
<p>We got the word that you forgot your Password. No problem, these things happen.</p>
<p>If this was a mistake, just ignore this email and nothing will happen.</p>
<p><br/> Please click on the below link for reset your password <br/><a href="%s">/%s</a></p>
<p><br/><br/><br/><strong>Best,</strong> <br/> Audvantage Team</p>
""" %(result[4],'http://'+request.META['HTTP_HOST']+'/reset-password/'+unqvalue, unqvalue)
ctx = { 'body': msg }
message = get_template('admin/email-template/email.html').render(Context(ctx))
msg = EmailMessage('Audvantage::Forgot Password', message, to=[result[7]], from_email=from_email)
msg.content_subtype = 'html'
msg.send()
log_message = '<div class="alert alert-success" role="alert">We have sent a link on your email Address, Please check your mail</div>'
utilb.logManage(1,cmp,datetime.now(),'User Activity','Forgot Password','Information','Forgot Password',result[7],'','Forgot Password')
else:
log_message = '<div class="alert alert-danger" role="alert">Wrong Company Name or UserName </div>'
context={
'log_message': log_message
}
return render(request,'admin/forgot-password.html',context)
def data_rollback2(request):
post_data=list(request.POST)
post_data=str(''.join(map(str,post_data)))
post_data=post_data.split('-')
dataType=post_data[0]
upload_id=post_data[1]
upload_id=int(''.join(map(str,upload_id)))
if dataType=='Expenses':
qry="SELECT av_engine.av_trxn_expenses_rollback_fn(%s, '%s', '%s', %s)"%(request.user.company_id,request.user.company.short_name,request.user.user_id,upload_id)
else:
qry="SELECT av_engine.av_trxn_company_employees_rollback_fn(%s, '%s', '%s', %s)"%(request.user.company_id,request.user.company.short_name,request.user.user_id,upload_id)
utilb.customQryExc(qry)
AvLogDataUploads.objects.filter(data_upload_id=upload_id).update(status='2 of 3 Master Updates In Progress')
json_data = json.dumps( { 'status':'success','message':'2 of 3 Master Updates In Progress'} )
utilb.logManage(request.user.company.id,request.user.company.short_name,datetime.now(),'User Activity','Rollback','Information','Progress',request.user.user_id,'1','Data Rollback In Progress')
return HttpResponse( json_data , content_type='application/json')
def upload_employeedata_step6(request):
req=request.POST
records = pe.get_records(file_name=req['file_name'])
if req['data_type']=='Employee':
AvLogDataUploads.objects.filter(data_upload_id=req['upload_id']).update(status='6 of 6 Audvantage Done')
msg='6 to 6 Audvantage Done'
elif req['data_type']=='Expenses':
sqlqry="SELECT av_engine.av_check_exp_engine_fn(%s, '%s', '%s','%s')"%(req['comp_id'],req['comp_name'],request.user.user_id,req['upload_id'])
ret_msg=custom_query(sqlqry)
if ret_msg=='SUCCESS':
msg='6 to 6 Audvantage Done'
AvLogDataUploads.objects.filter(data_upload_id=req['upload_id']).update(status='6 of 6 Audvantage Done')
else:
AvLogDataUploads.objects.filter(data_upload_id=req['upload_id']).update(status='6 of 6 Audvantage Error')
msg='6 to 6 Audvantage Error'
json_data = json.dumps( { 'status':'success','message':msg, 'file_name':req['file_name'], 'upload_id':req['upload_id'] , 'logData': getLogData(request.user.company.id) } )
utilb.logManage(request.user.company.id,request.user.company.short_name,datetime.now(),'Data Load','Load End','Information','Load End',request.user.user_id,req['upload_id'],'uploadData')
return HttpResponse( json_data , content_type='application/json')
def login(request):
log_message= None
if request.user.is_authenticated():
return HttpResponseRedirect('/')
if request.method == "POST":
username = request.POST.get('username')
password = request.POST.get('password')
cmp = request.POST.get('companyname').strip()
cmpData = AvMasterCompany.objects.filter(short_name=cmp)
if len(cmpData)>0:
cmpid=cmpData[0].id
else:
cmpid=0;
user = authenticate(user_id = username , password = password)
if user:
if user.company_id==cmpid:
auth_login(request, user)
utilb.logManage(request.user.company.id,request.user.company.short_name,datetime.now(),'User Activity','User Login','Information','User Login',request.user.user_id,'','User Login')
return HttpResponseRedirect('/')
else:
log_message = 'Wrong company name'
utilb.logManage(1,cmp,datetime.now(),'User Activity','Incorrect Company Name','Information','Incorrect Company Name',username,'','Incorrect Company Name')
else:
log_message = 'Wrong username or password'
utilb.logManage(1,cmp,datetime.now(),'User Activity','Incorrect Username','Information','Incorrect Username',username,'','Incorrect Username Password')
context={
'log_message': log_message
}
return render(request,'admin/login.html',context)
def data_rollback3(request):
post_data = list(request.POST)
post_data = str(''.join(map(str, post_data)))
post_data = post_data.split('-')
dataType = post_data[0]
upload_id = post_data[1]
upload_id = int(''.join(map(str, upload_id)))
qry = "SELECT av_engine.av_check_exp_engine_fn(%s, '%s', '%s', %s)" % (
request.user.company_id, request.user.company.short_name,
request.user.user_id, upload_id)
utilb.customQryExc(qry)
AvLogDataUploads.objects.filter(data_upload_id=upload_id).update(
status='3 of 3 Rollback Done', upload_or_rollback='Rollbacked')
json_data = json.dumps({
'status': 'success',
'message': '3 of 3 Rollback Done'
})
utilb.logManage(request.user.company.id, request.user.company.short_name,
datetime.now(), 'User Activity', 'Rollback', 'Information',
'Done', request.user.user_id, '1', 'Data Rollback Done')
return HttpResponse(json_data, content_type='application/json')
def uploadEmployeeData(request):
json_data=None
DataType=AvMasterUploadDataType.objects.order_by('data_type')
DataType_json = serializers.serialize('json', DataType )
DataType_json_list = json.loads( DataType_json )
cursor = connection.cursor()
rolePermisn=utilb.userRolePermission(request.user.company.id,request.user.user_id)
compData=''
if 'View All Companies' in rolePermisn:
compData=AvMasterCompany.objects.values('id','short_name').order_by('short_name')
elif 'View Company' in rolePermisn:
compData=AvMasterCompany.objects.values('id','short_name').filter(id=request.user.company.id).order_by('short_name')
if request.method == "POST":
data=request.FILES['file']
path = default_storage.save('tmp/import.xls', ContentFile(data.read()))
tmp_file = os.path.join(settings.MEDIA_ROOT, path)
records = pe.get_records(file_name=tmp_file)
req=request.POST
cursor.execute("SELECT nextval('av_log_data_upload_id_seq')")
upload_id=cursor.fetchone()
cursor.close()
uploadID=int(''.join(map(str,upload_id)))
c=AvLogDataUploads(data_upload_id=uploadID,company_id=request.user.company.id,company_name=request.user.company.short_name,data_type=req['data_type'],uploaded_by=request.user.user_id,uploaded_date=datetime.now(),no_of_records='0',status='1 of 6 - File Upload',upload_or_rollback='File Upload')
c.save()
json_data = json.dumps( { 'status':'success','message':'File uploaded successfully.', 'file_name':tmp_file, 'upload_id':uploadID ,'logData': getLogData(request.user.company.id) } )
utilb.logManage(request.user.company.id,request.user.company.short_name,datetime.now(),'Data Load','Load Start','Information','File Uploaded',request.user.user_id,uploadID,'uploadData')
else:
json_data = json.dumps( { 'DataType':DataType_json_list, 'compData':list(compData) ,'UserCompId':request.user.company.id, 'logData': getLogData(request.user.company.id), 'rolePermission':utilb.userRolePermission(request.user.company.id,request.user.user_id)} )
return HttpResponse( json_data , content_type='application/json')
def upload_employeedata_step6(request):
req = request.POST
records = pe.get_records(file_name=req['file_name'])
if req['data_type'] == 'Employee':
AvLogDataUploads.objects.filter(
data_upload_id=req['upload_id']).update(
status='6 of 6 Audvantage Done')
msg = '6 to 6 Audvantage Done'
elif req['data_type'] == 'Expenses':
sqlqry = "SELECT av_engine.av_check_exp_engine_fn(%s, '%s', '%s','%s')" % (
req['comp_id'], req['comp_name'], request.user.user_id,
req['upload_id'])
ret_msg = custom_query(sqlqry)
if ret_msg == 'SUCCESS':
msg = '6 to 6 Audvantage Done'
AvLogDataUploads.objects.filter(
data_upload_id=req['upload_id']).update(
status='6 of 6 Audvantage Done')
else:
AvLogDataUploads.objects.filter(
data_upload_id=req['upload_id']).update(
status='6 of 6 Audvantage Error')
msg = '6 to 6 Audvantage Error'
json_data = json.dumps({
'status': 'success',
'message': msg,
'file_name': req['file_name'],
'upload_id': req['upload_id'],
'logData': getLogData(request.user.company.id)
})
utilb.logManage(request.user.company.id, request.user.company.short_name,
datetime.now(), 'Data Load', 'Load End', 'Information',
'Load End', request.user.user_id, req['upload_id'],
'uploadData')
return HttpResponse(json_data, content_type='application/json')
def forgotUserName(request):
log_message= None
if request.user.is_authenticated():
return HttpResponseRedirect('/')
if request.method == "POST":
email_address = request.POST.get('email_address')
cmp = request.POST.get('companyname').strip()
parm = [email_address,cmp]
cursor = connection.cursor()
cursor.execute('SELECT u.*,c.short_name FROM av_master_company_users u LEFT JOIN av_master_company c ON u.company_id=c.id WHERE u.email_address = %s AND c.short_name = %s ', parm)
result=cursor.fetchone()
cursor.close()
if result!=None:
from_email = '*****@*****.**'
msg = """<p><strong style="color:#565252;">Dear </strong> %s,</p>
<p>We got the word that you forgot your Username. No problem, these things happen.</p>
<p>If this was a mistake, just ignore this email and nothing will happen.</p>
<p><br/><strong style="color:#565252;">Your Username is:</strong> %s</p>
<p><br/><br/><br/><strong>Best,</strong> <br/> Audvantage Team</p>
""" %(result[4],result[1])
ctx = { 'body': msg }
message = get_template('admin/email-template/email.html').render(Context(ctx))
msg = EmailMessage('Audvantage::Forgot UserName', message, to=[result[7]], from_email=from_email)
msg.content_subtype = 'html'
msg.send()
log_message = '<div class="alert alert-success" role="alert">We have sent mail on your email Address, Please check your mail</div>'
utilb.logManage(1,cmp,datetime.now(),'User Activity','Forgot Username','Information','Forgot Username',result[1],'','Forgot Username')
else:
log_message = '<div class="alert alert-danger" role="alert">Wrong Company Name or Email Address </div>'
context={
'log_message': log_message
}
return render(request,'admin/forgot-username.html',context)
def upload_employeedata_step2(request):
req = request.POST
records = pe.get_records(file_name=req['file_name'])
if ('Employee ID' in records[0] and req['data_type'] == 'Employee') or (
'Report ID' in records[0] and req['data_type'] == 'Expenses'):
json_data = json.dumps({
'status': 'success',
'message': 'File formate is ok.',
'file_name': req['file_name'],
'upload_id': req['upload_id']
})
AvLogDataUploads.objects.filter(
data_upload_id=req['upload_id']).update(
status='2 of 6 File Formate Check')
utilb.logManage(request.user.company.id,
request.user.company.short_name, datetime.now(),
'Data Load', 'Records Processed', 'Information',
'Checked file formate', request.user.user_id,
req['upload_id'], 'uploadData')
else:
json_data = json.dumps({
'status': 'danger',
'message': 'File formate is not valid.',
'file_name': req['file_name'],
'upload_id': req['upload_id']
})
AvLogDataUploads.objects.filter(
data_upload_id=req['upload_id']).update(
status='1 of 6 File Formate Check')
utilb.logManage(request.user.company.id,
request.user.company.short_name, datetime.now(),
'Data Load', 'Records Processed', 'Information',
'Checked file formate failed', request.user.user_id,
req['upload_id'], 'uploadData')
return HttpResponse(json_data, content_type='application/json')
def uploadEmployeeData(request):
json_data = None
DataType = AvMasterUploadDataType.objects.order_by('data_type')
DataType_json = serializers.serialize('json', DataType)
DataType_json_list = json.loads(DataType_json)
cursor = connection.cursor()
rolePermisn = utilb.userRolePermission(request.user.company.id,
request.user.user_id)
compData = ''
if 'View All Companies' in rolePermisn:
compData = AvMasterCompany.objects.values(
'id', 'short_name').order_by('short_name')
elif 'View Company' in rolePermisn:
compData = AvMasterCompany.objects.values('id', 'short_name').filter(
id=request.user.company.id).order_by('short_name')
if request.method == "POST":
data = request.FILES['file']
path = default_storage.save('tmp/import.xls', ContentFile(data.read()))
tmp_file = os.path.join(settings.MEDIA_ROOT, path)
records = pe.get_records(file_name=tmp_file)
req = request.POST
cursor.execute("SELECT nextval('av_log_data_upload_id_seq')")
upload_id = cursor.fetchone()
cursor.close()
uploadID = int(''.join(map(str, upload_id)))
c = AvLogDataUploads(data_upload_id=uploadID,
company_id=request.user.company.id,
company_name=request.user.company.short_name,
data_type=req['data_type'],
uploaded_by=request.user.user_id,
uploaded_date=datetime.now(),
no_of_records='0',
status='1 of 6 - File Upload',
upload_or_rollback='File Upload')
c.save()
json_data = json.dumps({
'status': 'success',
'message': 'File uploaded successfully.',
'file_name': tmp_file,
'upload_id': uploadID,
'logData': getLogData(request.user.company.id)
})
utilb.logManage(request.user.company.id,
request.user.company.short_name, datetime.now(),
'Data Load', 'Load Start', 'Information',
'File Uploaded', request.user.user_id, uploadID,
'uploadData')
else:
json_data = json.dumps({
'DataType':
DataType_json_list,
'compData':
list(compData),
'UserCompId':
request.user.company.id,
'logData':
getLogData(request.user.company.id),
'rolePermission':
utilb.userRolePermission(request.user.company.id,
request.user.user_id)
})
return HttpResponse(json_data, content_type='application/json')
def auditlog(request):
json_data=None
if request.method == "POST":
reqdata=dict(request.POST)
if 'comp_name' or 'event_category' or 'user' or 'start_date' or 'event_sub_category' or 'keyword' or 'end_date' or 'event_type' in reqdata and (reqdata['comp_name']!=[''] or reqdata['event_category']!=[''] or reqdata['user']!=[''] or reqdata['start_date']!=[''] or reqdata['event_sub_category']!=[''] or reqdata['keyword']!=[''] or reqdata['end_date']!=[''] or reqdata['event_type']!=[''] ):
if 'comp_name' in reqdata and reqdata['comp_name']!=['']:
compID=int(''.join(map(str,reqdata['comp_name'])))
SqlQuery=Q(company_id=compID )
if 'event_category' in reqdata and reqdata['event_category']!=['']:
SqlQuery &= Q(event_category__in=reqdata['event_category'] )
if 'user' in reqdata and reqdata['user']!=['']:
userName=''.join(map(str, reqdata['user']))
SqlQuery &= Q(event_user__icontains=userName.strip())
if 'start_date' and 'end_date' in reqdata and (reqdata['start_date']!=[''] and reqdata['end_date']!=['']):
start_date=''.join(map(str, reqdata['start_date']))
end_date=''.join(map(str, reqdata['end_date']))
SqlQuery &= Q(event_date__range=[start_date,end_date] )
if 'start_date' in reqdata and reqdata['start_date']!=[''] and reqdata['end_date']==['']:
start_date=''.join(map(str, reqdata['start_date']))
SqlQuery &= Q(event_date__date=start_date)
if 'event_sub_category' in reqdata and reqdata['event_sub_category']!=['']:
SqlQuery &= Q(event_sub_category__in=reqdata['event_sub_category'] )
if 'event_type' in reqdata and reqdata['event_type']!=['']:
SqlQuery &= Q(event_type__in=reqdata['event_type'] )
# if 'keyword' in reqdata and reqdata['keyword']!=['']:
# dta_keyword=''.join(map(str, reqdata['keyword']))
# SqlQuery &= Q(event_user__icontains=dta_keyword.strip())
LogData = AvLogAuditTrail.objects.filter( SqlQuery ).order_by("-event_date")
expt_file_name=utilb.export_auditlog(LogData)
LogData_json = serializers.serialize('json', LogData )
LogData_json_list = json.loads( LogData_json )
json_data = json.dumps( { 'logData':LogData_json_list, 'expt_file_name':expt_file_name } )
utilb.logManage(request.user.company.id,request.user.company.short_name,datetime.now(),'User Activity','Audit Log','Information','Audit Log',request.user.user_id,'1','Search data')
else:
print 'No- data'
else:
rolePermisn=utilb.userRolePermission(request.user.company.id,request.user.user_id)
compData=''
if 'View All Companies' in rolePermisn:
compData=AvMasterCompany.objects.values('id','short_name').order_by('short_name')
elif 'View Company' in rolePermisn:
compData=AvMasterCompany.objects.values('id','short_name').filter(id=request.user.company.id).order_by('short_name')
eventCategory=AvMasterEvents.objects.values('event_category').annotate(event_category_name=Count('event_category')).order_by('event_category')
eventType=AvMasterEvents.objects.values('event_type').annotate(event_type_name=Count('event_type')).order_by('event_type')
json_data = json.dumps( { 'compData':list(compData) ,'UserCompId':request.user.company.id, 'eventCategory':list(eventCategory) , 'eventSubCategory':'', 'eventType': list(eventType), 'rolePermission':utilb.userRolePermission(request.user.company.id,request.user.user_id)} )
utilb.logManage(request.user.company.id,request.user.company.short_name,datetime.now(),'User Activity','Audit Log','Information','Audit Log',request.user.user_id,'1','Visiting on page')
return HttpResponse( json_data , content_type='application/json')
def getSearchSummary(request):
json_data=None
if request.method == "POST":
#reqdata=json.loads(request.body)
reqdata=dict(request.POST)
if 'countryItem' or 'QuarterItem' or 'categoryItem' or 'subCategoryItem' or 'paymentItem' or 'functionItem' or 'employeeItem' or 'thresholdItem' in reqdata and (reqdata['countryItem']!=[''] or reqdata['QuarterItem']!=[''] or reqdata['categoryItem']!=[''] or reqdata['subCategoryItem']!=[''] or reqdata['paymentItem']!=[''] or reqdata['functionItem']!=[''] or reqdata['employeeItem']!=[''] or reqdata['thresholdItem']!=[''] ):
SqlQuery=Q(company_id=request.user.company_id)
if 'QuarterItem' in reqdata and reqdata['QuarterItem']!=['']:
SqlQuery &= Q(c_fiscal_quarter__in=reqdata['QuarterItem'] )
if 'countryItem' in reqdata and reqdata['countryItem']!=['']:
SqlQuery &= Q(location_country__in=reqdata['countryItem'] )
if 'categoryItem' in reqdata and reqdata['categoryItem']!=['']:
SqlQuery &= Q(expense_type_name__in=reqdata['categoryItem'] )
if 'subCategoryItem' in reqdata and reqdata['subCategoryItem']!=['']:
SqlQuery &= Q(spend_category_name__in=reqdata['subCategoryItem'] )
if 'paymentItem' in reqdata and reqdata['paymentItem']!=['']:
SqlQuery &= Q(payment_type_name__in=reqdata['paymentItem'] )
if 'functionItem' in reqdata and reqdata['functionItem']!=['']:
SqlQuery &= Q(e_function_name__in=reqdata['functionItem'] )
if 'employeeItem' in reqdata and reqdata['employeeItem']!=['']:
employeeItem=''.join(map(str, reqdata['employeeItem']))
SqlQuery &= Q(e_employee_name__icontains=employeeItem.strip())
if 'thresholdItem' in reqdata and reqdata['thresholdItem']!=['']:
thresholdItem=int(''.join(map(str,reqdata['thresholdItem'])))
SqlQuery &= Q(transaction_amount__gte= thresholdItem )
if 'start_date' and 'end_date' in reqdata and (reqdata['start_date']!=[''] and reqdata['end_date']!=['']):
start_date=''.join(map(str, reqdata['start_date']))
end_date=''.join(map(str, reqdata['end_date']))
SqlQuery &= Q(transaction_date__range=[start_date,end_date] )
if 'start_date' in reqdata and reqdata['start_date']!=[''] and reqdata['end_date']==['']:
start_date=''.join(map(str, reqdata['start_date']))
SqlQuery &= Q(transaction_date=start_date)
PDid=utilb.getUserProcessDashboardId(request,'Summary')
Dashboard=utilb.userDashboard(request.user.company_id,request.user.user_id, PDid[0], PDid[1])
ScopeData=AvMasterUserDatascope.objects.values('data_scope_type','company_id', 'process_id').filter(user_id=request.user.user_id, company_id=request.user.company.id , process_id = PDid[0] ).annotate( Count('company_id',distinct=True), Count('process_id',distinct=True), Count('data_scope_type',distinct=True))
data_scope_flag=str(Dashboard[4])
rolePerm=utilb.userRolePermission(request.user.company.id,request.user.user_id)
if 'View All Records' in rolePerm:
print 'View All Records'
elif 'View Company Records' in rolePerm:
if len(Dashboard) >0:
if data_scope_flag=='Y':
for scope in ScopeData:
if scope['data_scope_type']=='Country':
SqlQuery &= Q(location_country__in= utilb.getDataScope(request,PDid[0],'Country') )
if scope['data_scope_type']=='Hierarchy':
SqlQuery &= Q(e_employee_number__in= utilb.getDataScope(request,PDid[0],'Hierarchy') )
else:
SqlQuery &= Q(e_employee_number__in= utilb.getDataScope(request,PDid[0],'N') )
SqlQuery &= Q(process_id=PDid[0], dashboard_id=PDid[1])
if 'viewBy':
if 'viewBy' in reqdata and reqdata['viewBy']!=[]:
viewBy=''.join(map(str, reqdata['viewBy']))
if viewBy=='transaction_amount':
minsql=Sum('transaction_amount')
else:
minsql=Count('report_line_id')
else:
minsql=Sum('transaction_amount')
Employee = AvOutExpDashSummary.objects.values('e_employee_name').filter( SqlQuery ).annotate(amount=Sum('transaction_amount'), expenses=Count('report_line_id')).order_by("e_employee_name")
Department = AvOutExpDashSummary.objects.values('company_id', 'dashboard_id', 'e_department_name', 'location_country').filter( SqlQuery ).annotate(amount=Sum('transaction_amount'), expenses=Count('report_line_id')).order_by("e_department_name")
#GraphMonth = AvOutExpDashSummary.objects.values('c_month_name').filter( SqlQuery ).annotate(amount=minsql).order_by("c_month_id")
GraphPaymentType = AvOutExpDashSummary.objects.values('c_month_name','payment_type_name','c_month_id').filter( SqlQuery ).annotate(amount=minsql).order_by("c_month_id")
payment = defaultdict(list)
for res in GraphPaymentType:
payment[res['payment_type_name']].append([res['amount']])
payment_month = defaultdict(list)
for res in GraphPaymentType:
payment_month[res['c_month_id']].append([res['amount']])
Categories = AvOutExpDashSummary.objects.values('expense_type_name').filter( SqlQuery ).annotate(category=Count('expense_type_name'),amount=minsql).order_by('expense_type_name')
GraphCategories = AvOutExpDashSummary.objects.values('spend_category_name').filter( SqlQuery ).annotate(amount=minsql).order_by("spend_category_name")
GraphCountries = AvOutExpDashSummary.objects.values('location_country').filter( SqlQuery ).annotate(amount=minsql).order_by("location_country")
SearchQuery = AvOutExpDashSummary.objects.values('company_id','job_id','dashboard_id').filter( SqlQuery ).annotate( total_amount=Sum('transaction_amount'),no_of_exp_reports=Count('report_id',distinct=True), no_of_exp_lines=Count('report_line_id',distinct=True), no_of_employees=Count('report_owner_id',distinct=True), avg_exp_per_report=Sum('transaction_amount')/Count('report_id',distinct=True), avg_exp_per_employee=Sum('transaction_amount')/Count('report_owner_id',distinct=True) )
BucketAnalysis = AvOutExpDashSummary.objects.values('job_id','company_id','dashboard_id','c_amount_bucket').filter( SqlQuery ).filter(company_id=request.user.company_id).annotate(expenses=minsql).order_by("expenses")
QuarterItems = AvOutExpDashSummary.objects.values('c_fiscal_quarter').filter( SqlQuery ).annotate(category=Count('c_fiscal_quarter')).order_by('c_fiscal_quarter')
functionItems = AvOutExpDashSummary.objects.values('e_function_name').filter( SqlQuery ).annotate(category=Count('e_function_name')).order_by('e_function_name')
json_data = json.dumps( { 'BucketAnalysis':list(BucketAnalysis),'Employee':list(Employee), 'Department':list(Department) , 'GraphMonth':payment , 'PaymentMonth':payment_month, 'GraphCategories':list(GraphCategories) ,'Categories_list':list(Categories), 'GraphCountries':list(GraphCountries) ,'JobStatistics':list(SearchQuery) ,'QuarterItems':list(QuarterItems),'functionItems':list(functionItems), 'DataType':'search'} )
utilb.logManage(request.user.company.id,request.user.company.short_name,datetime.now(),'User Activity','Filter','Information','Filter',request.user.user_id,'','Expense Summary')
else:
json_data=getSummary(request)
else:
json_data=getSummary(request)
return HttpResponse( json_data , content_type='application/json')
def logout_page(request):
utilb.logManage(request.user.company.id,request.user.company.short_name,datetime.now(),'User Activity','User Logout','Information','User Logout',request.user.user_id,'','User Logout')
logout(request)
return HttpResponseRedirect('/login/')
def auditlog(request):
json_data = None
if request.method == "POST":
reqdata = dict(request.POST)
if 'comp_name' or 'event_category' or 'user' or 'start_date' or 'event_sub_category' or 'keyword' or 'end_date' or 'event_type' in reqdata and (
reqdata['comp_name'] != ['']
or reqdata['event_category'] != ['']
or reqdata['user'] != [''] or reqdata['start_date'] != ['']
or reqdata['event_sub_category'] != ['']
or reqdata['keyword'] != [''] or reqdata['end_date'] != ['']
or reqdata['event_type'] != ['']):
if 'comp_name' in reqdata and reqdata['comp_name'] != ['']:
compID = int(''.join(map(str, reqdata['comp_name'])))
SqlQuery = Q(company_id=compID)
if 'event_category' in reqdata and reqdata['event_category'] != [
''
]:
SqlQuery &= Q(event_category__in=reqdata['event_category'])
if 'user' in reqdata and reqdata['user'] != ['']:
userName = ''.join(map(str, reqdata['user']))
SqlQuery &= Q(event_user__icontains=userName.strip())
if 'start_date' and 'end_date' in reqdata and (
reqdata['start_date'] != ['']
and reqdata['end_date'] != ['']):
start_date = ''.join(map(str, reqdata['start_date']))
end_date = ''.join(map(str, reqdata['end_date']))
SqlQuery &= Q(event_date__range=[start_date, end_date])
if 'start_date' in reqdata and reqdata['start_date'] != [
''
] and reqdata['end_date'] == ['']:
start_date = ''.join(map(str, reqdata['start_date']))
SqlQuery &= Q(event_date__date=start_date)
if 'event_sub_category' in reqdata and reqdata[
'event_sub_category'] != ['']:
SqlQuery &= Q(
event_sub_category__in=reqdata['event_sub_category'])
if 'event_type' in reqdata and reqdata['event_type'] != ['']:
SqlQuery &= Q(event_type__in=reqdata['event_type'])
# if 'keyword' in reqdata and reqdata['keyword']!=['']:
# dta_keyword=''.join(map(str, reqdata['keyword']))
# SqlQuery &= Q(event_user__icontains=dta_keyword.strip())
LogData = AvLogAuditTrail.objects.filter(SqlQuery).order_by(
"-event_date")
expt_file_name = utilb.export_auditlog(LogData)
LogData_json = serializers.serialize('json', LogData)
LogData_json_list = json.loads(LogData_json)
json_data = json.dumps({
'logData': LogData_json_list,
'expt_file_name': expt_file_name
})
utilb.logManage(request.user.company.id,
request.user.company.short_name, datetime.now(),
'User Activity', 'Audit Log', 'Information',
'Audit Log', request.user.user_id, '1',
'Search data')
else:
print 'No- data'
else:
rolePermisn = utilb.userRolePermission(request.user.company.id,
request.user.user_id)
compData = ''
if 'View All Companies' in rolePermisn:
compData = AvMasterCompany.objects.values(
'id', 'short_name').order_by('short_name')
elif 'View Company' in rolePermisn:
compData = AvMasterCompany.objects.values(
'id', 'short_name').filter(
id=request.user.company.id).order_by('short_name')
eventCategory = AvMasterEvents.objects.values(
'event_category').annotate(event_category_name=Count(
'event_category')).order_by('event_category')
eventType = AvMasterEvents.objects.values('event_type').annotate(
event_type_name=Count('event_type')).order_by('event_type')
json_data = json.dumps({
'compData':
list(compData),
'UserCompId':
request.user.company.id,
'eventCategory':
list(eventCategory),
'eventSubCategory':
'',
'eventType':
list(eventType),
'rolePermission':
utilb.userRolePermission(request.user.company.id,
request.user.user_id)
})
utilb.logManage(request.user.company.id,
request.user.company.short_name, datetime.now(),
'User Activity', 'Audit Log', 'Information',
'Audit Log', request.user.user_id, '1',
'Visiting on page')
return HttpResponse(json_data, content_type='application/json')