def delete_expertise(request):
if request.method == 'POST':
if 'std_id' in request.session:
user = request.session['std_id']
form = ExpertForm(request.POST)
print(form)
if form.is_valid():
topic = form.cleaned_data['topic']
conn = db()
c = conn.cursor()
sql = """ SELECT TOPIC from EXPERTISE WHERE STD_ID = %(std_id)s AND TOPIC =%(topic)s"""
c.execute(sql, {'std_id': user, 'topic': topic})
row = c.fetchone()
print(row)
if not row is None:
sql = """ DELETE FROM EXPERTISE WHERE STD_ID =%(std_id)s AND TOPIC=%(topic)s"""
# try:
c.execute(sql, {'std_id': user, "topic": topic})
conn.commit()
print('Deleted Skill')
# except IntegrityError:
# print('Error Deleting Skill')
else:
print('Exists')
skill_error = "Not Exists"
return redirect('Profile:edit_profile')
def endorse(request, std_id, topic):
if 'std_id' in request.session:
user = request.session['std_id']
conn = db()
c = conn.cursor()
print({'user': user, 'std_id': std_id, 'topic': topic})
sql = """ SELECT * from ENDORSE WHERE GIVER_ID = %(user_id)s AND TAKER_ID =%(std_id)s AND TOPIC =%(topic)s"""
c.execute(sql, {'user_id': user, 'std_id': std_id, 'topic': topic})
row = c.fetchone()
if row is None:
sql = """ INSERT INTO ENDORSE (GIVER_ID,TAKER_ID,TOPIC)
VALUES(%(user_id)s,%(std_id)s,%(topic)s)"""
try:
c.execute(sql, {
'user_id': user,
'std_id': std_id,
'topic': topic
})
conn.commit()
print('Endorsed')
except IntegrityError:
print('Error in Endorsing')
else:
print('Endorse Exists')
skill_error = "Already Exists"
return HttpResponseRedirect(
reverse('Profile:visit_profile', args=(std_id, )))
def edit_photo(request):
if request.method == 'POST':
if 'std_id' in request.session:
user = request.session['std_id']
myfile = request.FILES['file']
fs = FileSystemStorage()
print(myfile.name)
filename = fs.save(myfile.name, myfile)
conn = db()
c = conn.cursor()
sql = """ SELECT * from PROFILE WHERE STD_ID = %(std_id)s"""
c.execute(sql, {'std_id': user})
row = c.fetchone()
if row is None:
sql = """ INSERT INTO PROFILE (STD_ID,PHOTO)
VALUES(%(std_id)s,%(photo)s)"""
try:
c.execute(sql, {'std_id': user, "photo": filename})
conn.commit()
print('Inserted DP')
except IntegrityError:
print('Error')
else:
sql = """ UPDATE PROFILE SET PHOTO=%(photo)s
WHERE STD_ID = %(std_id)s"""
try:
c.execute(sql, {'photo': filename, 'std_id': user})
conn.commit()
print('Updated DP')
except IntegrityError:
message = "User already exists ..."
print('Error Updating DP')
return redirect('Profile:profile')
def edit_expertise(request):
if request.method == 'POST':
if 'std_id' in request.session:
user = request.session['std_id']
form = ExpertForm(request.POST)
print(form)
if form.is_valid():
topic = form.cleaned_data['topic']
conn = db()
c = conn.cursor()
sql = """ SELECT TOPIC from EXPERTISE WHERE STD_ID = %(std_id)s AND TOPIC =%(topic)s"""
c.execute(sql, {'std_id': user, 'topic': topic})
row = c.fetchone()
if row is None:
sql = """ INSERT INTO EXPERTISE (STD_ID,TOPIC)
VALUES(%(std_id)s,%(topic)s)"""
try:
c.execute(sql, {'std_id': user, "topic": topic})
conn.commit()
print('Inserted Skill')
except IntegrityError:
print('Error')
else:
print('Exists')
skill_error = "Already Exists"
return redirect('Profile:edit_profile')
def delete_comment(request, post_id, comment_id):
if "std_id" in request.session:
conn = db()
c = conn.cursor()
user_id = request.session['std_id']
proper_request = False
c.execute(
"SELECT USER_ID FROM USER_REPLIES WHERE USR_REPLS_ROW = %(comment_id)s",
{"comment_id": comment_id})
rows = c.fetchall()
for row in rows:
commented_by = row[0]
if commented_by == user_id:
c.execute(
"DELETE FROM USER_REPLIES WHERE USR_REPLS_ROW = %(comment_id)s",
{"comment_id": comment_id})
c.execute("COMMIT")
return HttpResponseRedirect(
reverse('post:detail_post', args=(post_id, 1)))
else:
return HttpResponseRedirect(
reverse('post:detail_post', args=(post_id, 1)))
else:
return redirect('SignIn:signin')
def clean(self):
print('Cleaning')
cleaned_data = super().clean()
std_id = cleaned_data.get('std_id')
print(std_id)
password = cleaned_data.get('password')
password2 = cleaned_data.get('password_again')
email = cleaned_data.get('email')
print(email)
print(password)
conn = db()
c = conn.cursor()
sql = """ SELECT STD_ID from USER_TABLE WHERE STD_ID = %(std_id)s"""
c.execute(sql, {'std_id': std_id})
row = c.fetchone()
print(row)
if row is not None:
print('I am here')
raise forms.ValidationError('User Already Exists...')
if password_validator(password) != 'Success':
print('wrong pass')
raise forms.ValidationError(password_validator(password))
if password2 != password:
print('Not MAtch')
raise forms.ValidationError('Password don\'t Match.')
def index(request):
conn = db()
std_id = None
message = ""
form = SignInForm()
if request.method == 'GET':
if 'std_id' in request.session:
print('Signed In' + str(request.session['std_id']))
#return redirect('Profile:profile')
return HttpResponseRedirect(reverse('post:all_post', args=(1, 0)))
else:
return render(request, 'SignIn/index.html', {
'form': form,
'msg': 'Please Sign In',
'std_id': None
})
elif request.method == 'POST':
form = SignInForm(request.POST)
print(form.is_valid())
if form.is_valid():
std_id = form.cleaned_data['std_id']
password = form.cleaned_data['password']
c = conn.cursor()
sql = """ SELECT password from USER_TABLE WHERE STD_ID = %(std_id)s;"""
c.execute(sql, {'std_id': std_id})
row = c.fetchone()
if row is None:
message = "User Doesn\'t exists ..."
return render(request, 'SignIn/index.html', {
'form': form,
'msg': message,
'std_id': None
})
else:
if check_encrypted_password(password, row[0]):
request.session['std_id'] = std_id
print('Signed In' + str(request.session['std_id']))
#return HttpResponseRedirect('/profile')
return HttpResponseRedirect(
reverse('post:all_post', args=(1, 0)))
else:
message = "Invalid Password"
std_id = None
conn.close()
return render(request, 'SignIn/index.html', {
'form': form,
'msg': message,
'std_id': None
})
def visit_profile(request, std_id):
if 'std_id' in request.session:
user = request.session['std_id']
enable_edit = False
if user == std_id:
enable_edit = True
return redirect('Profile:profile')
data = None
conn = db()
c = conn.cursor()
sql = """SELECT * from USER_PROFILE WHERE STD_ID = %(std_id)s"""
c.execute(sql, {'std_id': std_id})
row = c.fetchone()
columnNames = [d[0].upper() for d in c.description]
print(row)
try:
data = dict(zip(columnNames, row))
except:
print('cannot Visit User')
#---------------------------------Expertise with Endorse Count----------------------------------
sql = """ SELECT EXPERTISE.TOPIC, COUNT( ENDORSE.GIVER_ID) AS C from EXPERTISE LEFT JOIN ENDORSE ON
EXPERTISE.STD_ID = ENDORSE.TAKER_ID AND EXPERTISE.TOPIC = ENDORSE.TOPIC WHERE EXPERTISE.STD_ID = %(std_id)s GROUP BY EXPERTISE.TOPIC"""
c.execute(sql, {'std_id': std_id})
rows = c.fetchall()
skills = {}
for row in rows:
skills[row[0]] = row[1]
print(skills)
#Job----------------------------------
sql = """ SELECT * from WORKS JOIN INSTITUTE USING(INSTITUTE_ID) WHERE STD_ID =%(std_id)s ORDER BY FROM_ DESC"""
c.execute(sql, {'std_id': std_id})
jobs = c.fetchall()
columnNames = [d[0].upper() for d in c.description]
job_list = []
for job in jobs:
try:
job_list.append(dict(zip(columnNames, job)))
except:
print('Cannot Parse Job')
print(job_list)
return render(request, 'Profile/profile.html', {
'data': data,
'skills': skills,
'edit': enable_edit,
'job': job_list
})
def make_post(request):
if "std_id" in request.session:
conn = db()
c = conn.cursor()
#-------------------------------------Profile Card---------------------------------
sql = """ SELECT * from USER_PROFILE WHERE STD_ID = %(std_id)s"""
c.execute(sql, {'std_id': request.session.get('std_id')})
row = c.fetchone()
columnNames = [d[0].upper() for d in c.description]
try:
data = dict(zip(columnNames, row))
except:
print('Cannot Parse Profile')
#-----------------------------------Skills------------------------------------
sql = """ SELECT EXPERTISE.TOPIC, COUNT( ENDORSE.GIVER_ID) AS C from EXPERTISE LEFT JOIN ENDORSE ON
EXPERTISE.STD_ID = ENDORSE.TAKER_ID AND EXPERTISE.TOPIC = ENDORSE.TOPIC WHERE EXPERTISE.STD_ID = %(std_id)s GROUP BY EXPERTISE.TOPIC"""
c.execute(sql, {'std_id': request.session.get('std_id')})
rows = c.fetchall()
skills = {}
for row in rows:
skills[row[0]] = row[1]
dp_form = DPForm()
#--------------------------------------Job History--------------------------------
sql = """ SELECT * from WORKS JOIN INSTITUTE USING(INSTITUTE_ID) WHERE STD_ID = %(std_id)s ORDER BY FROM_ DESC"""
c.execute(sql, {'std_id': request.session.get('std_id')})
jobs = c.fetchall()
columnNames = [d[0].upper() for d in c.description]
job_list = []
for job in jobs:
try:
job_list.append(dict(zip(columnNames, job)))
except:
print('NULL')
print(data)
context = {
'type': request.GET.get('post_type'),
'unfilled': None,
'data': data,
'skills': skills,
'edit': True,
'dp': dp_form,
'job': job_list,
}
return render(request, 'post/make_post.html', context)
else:
return redirect('SignIn:signin')
def delete_job(request):
if request.method == 'POST':
if 'std_id' in request.session:
user = request.session['std_id']
form = JobForm(request.POST)
print(form)
if form.is_valid():
name = form.cleaned_data['name']
from_ = form.cleaned_data['from_'].strftime('%Y-%m-%d')
designation = form.cleaned_data['designation']
print('Accquired Delete Req')
conn = db()
c = conn.cursor()
sql = """SELECT INSTITUTE_ID FROM INSTITUTE WHERE NAME=%(name)s"""
c.execute(sql, {'name': name})
ins_id = c.fetchone()
if ins_id is None:
print('No Institute')
return redirect('Profile:edit_profile')
else:
sql = """ SELECT STD_ID,INSTITUTE_ID,FROM_,DESIGNATION from WORKS WHERE STD_ID =%(std_id)s
AND INSTITUTE_ID = %(ins_id)s AND FROM_ =%(from_)s AND DESIGNATION=UPPER(%(designation)s)"""
c.execute(
sql, {
'std_id': user,
'ins_id': ins_id[0],
'from_': from_,
'designation': designation
})
row = c.fetchone()
print(row)
if row is not None:
sql = """ DELETE FROM WORKS WHERE STD_ID =%(std_id)s AND INSTITUTE_ID = %(ins_id)s AND FROM_ = TO_DATE(%(from_)s,'YYYY-MM-DD') AND DESIGNATION=UPPER(%(designation)s)"""
try:
c.execute(
sql, {
'std_id': user,
'ins_id': ins_id[0],
'from_': from_,
'designation': designation
})
conn.commit()
print('Deleted Job')
except IntegrityError:
print('Error')
else:
print('Don\'t Exists')
job_error = "Already Exists"
return redirect('Profile:edit_profile')
def make_event(request):
conn = db()
message = ""
if request.method == 'POST':
form = CreateEventForm(request.POST)
if form.is_valid():
info = {
'name': form.cleaned_data['name'],
'location': form.cleaned_data['location'],
'start_date':
form.cleaned_data['start_date'].strftime('%Y-%m-%d'),
'end_date': form.cleaned_data['end_date'].strftime('%Y-%m-%d'),
'description': form.cleaned_data['about']
}
print(info)
c = conn.cursor()
sql = """ INSERT INTO EVENT (EVENT_NAME,LOCATION,EVENT_START,EVENT_END,DESCRIPTION)
VALUES(%(name)s,%(location)s,to_date(%(start_date)s,'yyyy-mm-dd'),to_date(%(end_date)s,'yyyy-mm-dd'),%(description)s)"""
try:
c.execute(sql, info)
conn.commit()
print('Registered Event')
except IntegrityError:
message = "Event already exists ..."
print('"Event already exists ...')
c = conn.cursor()
sql = """ INSERT INTO EVENT_ARRANGE (EVENT_ID ,USER_ID)
VALUES((SELECT EVENT_ID FROM EVENT WHERE EVENT_NAME = %(name)s), %(std_id)s)"""
c.execute(
sql, {
'std_id': request.session['std_id'],
'name': form.cleaned_data['name']
})
conn.commit()
conn.close()
print('Registered Event')
return redirect('SignIn:signin')
else:
form = CreateEventForm()
return render(request, 'Events/create.html', {
'form': form,
'msg': message
})
def index(request):
if 'std_id' in request.session:
std_id = request.session['std_id']
data = None
conn = db()
c = conn.cursor()
#------------------------Fetch Profile Information-----------------------------
sql = """ SELECT * from USER_PROFILE WHERE STD_ID = %(std_id)s"""
c.execute(sql, {'std_id': std_id})
row = c.fetchone()
columnNames = [d[0].upper() for d in c.description]
try:
data = dict(zip(columnNames, row))
except:
print('Cannot Parse Profile')
#-----------------------------------Skills------------------------------------
sql = """ SELECT EXPERTISE.TOPIC, COUNT( ENDORSE.GIVER_ID) AS C from EXPERTISE LEFT JOIN ENDORSE ON
EXPERTISE.STD_ID = ENDORSE.TAKER_ID AND EXPERTISE.TOPIC = ENDORSE.TOPIC WHERE EXPERTISE.STD_ID = %(std_id)s GROUP BY EXPERTISE.TOPIC"""
c.execute(sql, {'std_id': std_id})
rows = c.fetchall()
skills = {}
for row in rows:
skills[row[0]] = row[1]
dp_form = DPForm()
#--------------------------------------Job History--------------------------------
sql = """ SELECT * from WORKS JOIN INSTITUTE USING(INSTITUTE_ID) WHERE STD_ID = %(std_id)s ORDER BY FROM_ DESC"""
rows = c.execute(sql, {'std_id': std_id})
jobs = c.fetchall()
columnNames = [d[0].upper() for d in c.description]
job_list = []
for job in jobs:
try:
job_list.append(dict(zip(columnNames, job)))
except:
print('NULL')
print(data)
# return render(request,'Profile/profile.html',{'data':data,'skills':skills,'edit':True,'dp':dp_form,'job':job_list})
return redirect('SignIn:signin')
else:
return redirect('SignIn:signin')
def join_event(request, event_id):
if 'std_id' in request.session:
conn = db()
message = ""
c = conn.cursor()
try:
sql = """ INSERT INTO EVENT_PARTICIPATES (EVENT_ID ,USER_ID)
VALUES(%(event_id)s, %(std_id)s)"""
c.execute(sql, {
'std_id': request.session['std_id'],
'event_id': event_id
})
conn.commit()
conn.close()
print('Joined Event')
except IntegrityError:
msg = 'Already Joined'
return HttpResponseRedirect(
reverse('Events:visit_event', args=(event_id, )))
def index(request):
conn = db()
message = ""
if request.method == 'POST':
form_signup = SignUpForm(request.POST)
if form_signup.is_valid():
info = {
'std_id':
form_signup.cleaned_data['std_id'],
'fullname':
form_signup.cleaned_data['fullname'],
'nickname':
form_signup.cleaned_data['nickname'],
'password':
encrypt_password(form_signup.cleaned_data['password']),
'email':
form_signup.cleaned_data['email'],
'mobile':
form_signup.cleaned_data['mobile'],
'birthdate':
str(form_signup.cleaned_data['birthdate'].strftime('%Y-%m-%d'))
if form_signup.cleaned_data['birthdate'] is not None else None,
}
print(info)
c = conn.cursor()
sql = """ INSERT INTO USER_TABLE (STD_ID,FULL_NAME,NICK_NAME,EMAIL,MOBILE,DATE_OF_BIRTH,PASSWORD)
VALUES(%(std_id)s,%(fullname)s,%(nickname)s,%(email)s,%(mobile)s,to_date(%(birthdate)s,'yyyy-mm-dd'),%(password)s)"""
try:
c.execute(sql, info)
conn.commit()
conn.close()
print('Registered User' + str(info['std_id']))
return redirect('SignIn:signin')
except IntegrityError:
message = "User already exists ..."
print('"User already exists ...')
else:
form_signup = SignUpForm()
return render(request, 'SignUp/index.html', {
'form': form_signup,
'msg': message
})
def upload_comment(request, post_id):
if "std_id" in request.session:
conn = db()
c = conn.cursor()
user_id = request.session.get('std_id')
comment_body = request.GET.get('comment_body')
comment_body = comment_body.replace("'", "''")
if len(comment_body) != 0:
c.execute(
"INSERT INTO USER_REPLIES (USER_ID, POST_ID, TEXT, TIMESTAMP) VALUES ('"
+ str(user_id) + "', '" + str(post_id) + "', '" +
comment_body + "', NOW())")
c.execute('COMMIT')
return HttpResponseRedirect(
reverse('post:detail_post', args=(post_id, 1)))
else:
return redirect('SignIn:signin')
def delete_post(request, post_id):
if "std_id" in request.session:
conn = db()
c = conn.cursor()
user_id = request.session['std_id']
c.execute("SELECT USER_ID FROM USER_POSTS WHERE POST_ID = %(post_id)s",
{"post_id": post_id})
rows = c.fetchall()
for row in rows:
posted_by = row[0]
if posted_by == user_id:
c.execute("DELETE FROM POST CASCADE WHERE POST_ID = %(post_id)s",
{"post_id": post_id})
c.execute("COMMIT")
return HttpResponseRedirect(reverse('post:all_post', args=(1, 0)))
else:
return HttpResponseRedirect(reverse('post:all_post', args=(1, 0)))
else:
return redirect('SignIn:signin')
def visit_event(request, event_id):
if 'std_id' in request.session:
data = None
conn = db()
c = conn.cursor()
sql = """SELECT * from EVENT WHERE EVENT_ID = %(event_id)s"""
c.execute(sql, {'event_id': event_id})
row = c.fetchone()
columnNames = [d[0].upper() for d in c.description]
print(row)
try:
data = dict(zip(columnNames, row))
except:
print('cannot Visit Event')
sql = """SELECT COUNT(USER_ID) from EVENT_PARTICIPATES WHERE EVENT_ID = %(event_id)s"""
c.execute(sql, {'event_id': event_id})
row = c.fetchone()
joined = row[0]
print(row)
sql = """SELECT FULL_NAME from USER_TABLE WHERE STD_ID = (SELECT USER_ID FROM EVENT_ARRANGE WHERE EVENT_ID = %(event_id)s)"""
c.execute(sql, {'event_id': event_id})
row = c.fetchone()
columnNames = [d[0] for d in c.description]
print(row)
sql = """SELECT * from EVENT_PARTICIPATES WHERE EVENT_ID = %(event_id)s AND USER_ID = %(std_id)s"""
c.execute(sql, {
'event_id': event_id,
'std_id': request.session['std_id']
})
user_join = c.fetchone()
return render(
request, 'Events/Events.html', {
'data': data,
'Organizer': row[0],
'Joined': joined,
'user_join': user_join
})
def edit_job(request):
if request.method == 'POST':
if 'std_id' in request.session:
print('Addding Jopbss')
user = request.session['std_id']
form = JobForm(request.POST)
print('form')
if form.is_valid():
print('validated Job')
name = form.cleaned_data['name']
from_ = form.cleaned_data['from_']
to_ = form.cleaned_data['to_']
designation = form.cleaned_data['designation']
conn = db()
c = conn.cursor()
sql = """SELECT INSTITUTE_ID FROM INSTITUTE WHERE NAME=%(name)s"""
c.execute(sql, {'name': name})
ins_id = c.fetchone()
print(ins_id)
if ins_id is None:
print('No Institute')
sql = "INSERT INTO INSTITUTE (NAME) VALUES( %(name)s);"
c.execute(sql, {'name': name})
conn.commit()
sql = """SELECT INSTITUTE_ID FROM INSTITUTE WHERE NAME=%(name)s"""
c.execute(sql, {'name': name})
ins_id = c.fetchone()
sql = """ SELECT STD_ID,INSTITUTE_ID,FROM_ from WORKS WHERE STD_ID =%(std_id)s AND INSTITUTE_ID = %(ins_id)s AND FROM_ =%(from_)s"""
c.execute(sql, {
'std_id': user,
'ins_id': ins_id,
'from_': from_
})
row = c.fetchone()
if row is None:
sql = """ INSERT INTO WORKS (STD_ID,INSTITUTE_ID,FROM_,TO_,DESIGNATION)
VALUES(%(std_id)s,%(ins_id)s,%(from_)s,%(to_)s,UPPER(%(designation)s))"""
# try:
print({
'std_id': user,
'ins_id': ins_id,
'from_': from_,
'to_': to_,
'designation': designation
})
c.execute(
sql, {
'std_id': user,
'ins_id': ins_id,
'from_': from_,
'to_': to_,
'designation': designation
})
conn.commit()
print('Inserted Job')
# except IntegrityError:
# print('Error')
else:
print('Exists')
job_error = "Already Exists"
return redirect('Profile:edit_profile')
def upload_post(request):
if "std_id" in request.session:
conn = db()
c = conn.cursor()
user_id = request.session.get('std_id')
post_attributes = {
"Help": ['type_of_help', 'reason', 'cell', 'description'],
"Career": ['topic_name', 'description'],
"Research": ["topic_name", 'journal', 'doi', 'description'],
"Job Post": [
'company_name', 'designation', 'location', 'min_requirement',
'description', 'salary'
]
}
post_class = identify_post_class(request)
unfilled_data, filled_data = find_unfilled_data(
request, post_class, post_attributes)
unfilled_data = [data for data in unfilled_data if data != "salary"]
cell_wrong_type = False
salary_wrong_type = False
if request.GET.get('cell') is not None:
cell_var = request.GET.get('cell')
for char in cell_var:
if char not in "0123456789+-":
cell_wrong_type = True
if request.GET.get('salary') is not None:
salary_var = request.GET.get('salary')
for char in salary_var:
if char not in "1234567890":
salary_wrong_type = True
if (len(unfilled_data) !=
0) or (cell_wrong_type) or (salary_wrong_type):
#-------------------------------------Profile Card---------------------------------
sql = """ SELECT * from USER_PROFILE WHERE STD_ID = %(std_id)s"""
row = c.execute(sql, {
'std_id': request.session.get('std_id')
}).fetchone()
columnNames = [d[0].upper() for d in c.description]
try:
data = dict(zip(columnNames, row))
except:
print('Cannot Parse Profile')
#-----------------------------------Skills------------------------------------
sql = """ SELECT EXPERTISE.TOPIC, COUNT( ENDORSE.GIVER_ID) AS C from EXPERTISE LEFT JOIN ENDORSE ON
EXPERTISE.STD_ID = ENDORSE.TAKER_ID AND EXPERTISE.TOPIC = ENDORSE.TOPIC WHERE EXPERTISE.STD_ID = %(std_id)s GROUP BY EXPERTISE.TOPIC"""
rows = c.execute(sql, {
'std_id': request.session.get('std_id')
}).fetchall()
skills = {}
for row in rows:
skills[row[0]] = row[1]
dp_form = DPForm()
#--------------------------------------Job History--------------------------------
sql = """ SELECT * from WORKS JOIN INSTITUTE USING(INSTITUTE_ID) WHERE STD_ID = %(std_id)s ORDER BY FROM_ DESC"""
rows = c.execute(sql, {'std_id': request.session.get('std_id')})
jobs = rows.fetchall()
columnNames = [d[0].upper() for d in c.description]
job_list = []
for job in jobs:
try:
job_list.append(dict(zip(columnNames, job)))
except:
print('NULL')
context = {
'type': post_class,
'unfilled': True,
'unfilled_list': unfilled_data,
'filled_data': filled_data,
'cell_wrong': cell_wrong_type,
'salary_wrong': salary_wrong_type,
'data': data,
'skills': skills,
'edit': True,
'dp': dp_form,
'job': job_list,
}
return render(request, 'post/make_post.html', context)
date_today = date.today().strftime('%d-%m-%Y')
if post_class == "Help":
type_of_help = request.GET.get('type_of_help')
reason = request.GET.get('reason')
deadline = request.GET.get('deadline')
cell = request.GET.get('cell')
description = request.GET.get('description')
description = description.replace("'", "''")
reason = reason.replace("'", "''")
type_of_help = type_of_help.replace("'", "''")
if deadline is None:
deadline = date_today
#c.execute("INSERT INTO POST (DATE_OF_POST, Description) VALUES (TO_DATE('"+date_today+"', 'dd-mm-yyyy'), '"+description+"')")
c.execute(
"INSERT INTO POST (DATE_OF_POST, Description) VALUES (NOW()::timestamp, '"
+ description + "')")
c.execute(
"SELECT POST_ID FROM (SELECT POST_ID FROM POST ORDER BY POST_ID DESC) AS POST_ALIAS LIMIT 1"
)
post_id = modify_c(c)[0]
c.execute("INSERT INTO USER_POSTS (USER_ID, POST_ID) VALUES ('" +
str(user_id) + "', '" + post_id + "')")
c.execute(
"INSERT INTO HELP (POST_ID, TYPE_OF_HELP, REASON, CELL_NO) VALUES ('"
+ post_id + "', '" + type_of_help + "', '" + reason + "', '" +
cell + "')")
c.execute('COMMIT')
elif post_class == "Career":
topic_name = request.GET.get('topic_name')
description = request.GET.get('description')
description = description.replace("'", "''")
topic_name = topic_name.replace("'", "''")
c.execute(
"INSERT INTO POST (DATE_OF_POST, Description) VALUES (NOW(), '"
+ description + "')")
c.execute(
"SELECT POST_ID FROM (SELECT POST_ID FROM POST ORDER BY POST_ID DESC) DUMMY_ALIAs LIMIT 1"
)
post_id = modify_c(c)[0]
c.execute("INSERT INTO USER_POSTS (USER_ID, POST_ID) VALUES ('" +
str(user_id) + "', '" + post_id + "')")
c.execute("INSERT INTO CAREER (POST_ID) VALUES ('" + post_id +
"')")
c.execute('COMMIT')
elif post_class == "Research":
topic_name = request.GET.get('topic_name')
description = request.GET.get('description')
description = description.replace("'", "''")
journal = request.GET.get('journal')
doi = request.GET.get('doi')
date_of_publication = request.GET.get('date_of_publication')
topic_name = topic_name.replace("'", "''")
journal = journal.replace("'", "''")
doi = doi.replace("'", "''")
if date_of_publication is None:
date_of_publication = date_today
else:
date_of_publication = date_of_publication[
8:10] + '-' + date_of_publication[
5:7] + '-' + date_of_publication[:4]
c.execute(
"INSERT INTO POST (DATE_OF_POST, Description) VALUES (NOW(), '"
+ description + "')")
c.execute(
"SELECT POST_ID FROM (SELECT POST_ID FROM POST ORDER BY POST_ID DESC) DUMMY LIMIT 1"
)
post_id = modify_c(c)[0]
c.execute("INSERT INTO USER_POSTS (USER_ID, POST_ID) VALUES ('" +
str(user_id) + "', '" + post_id + "')")
c.execute(
"INSERT INTO RESEARCH (POST_ID, TOPIC_NAME, DATE_OF_PUBLICATION, JOURNAL, DOI) VALUES ('"
+ post_id + "', '" + topic_name + "', TO_DATE('" +
date_of_publication + "', 'dd-mm-yyyy'), '" + journal +
"', '" + doi + "')")
c.execute('COMMIT')
else:
company_name = request.GET.get('company_name')
designation = request.GET.get('designation')
location = request.GET.get('location')
min_requirement = request.GET.get('min_requirement')
salary = request.GET.get('salary')
description = request.GET.get('description')
description = description.replace("'", "''")
c.execute(
"INSERT INTO POST (DATE_OF_POST, Description) VALUES (NOW(), '"
+ description + "')")
c.execute(
"SELECT POST_ID FROM (SELECT POST_ID FROM POST ORDER BY POST_ID DESC) DUMMY LIMIT 1"
)
post_id = modify_c(c)[0]
c.execute("INSERT INTO USER_POSTS (USER_ID, POST_ID) VALUES ('" +
str(user_id) + "', '" + post_id + "')")
if salary is '':
c.execute(
"INSERT INTO JOB_POST (POST_ID, COMPANY_NAME, DESIGNATION, LOCATION, REQUIREMENTs) VALUES ('"
+ post_id + "', '" + company_name + "', '" + designation +
"', '" + location + "', '" + min_requirement + "')")
else:
c.execute(
"INSERT INTO JOB_POST (POST_ID, COMPANY_NAME, DESIGNATION, LOCATION, REQUIREMENTs, SALARY) VALUES ('"
+ post_id + "', '" + company_name + "', '" + designation +
"', '" + location + "', '" + min_requirement + "', '" +
salary + "')")
c.execute('COMMIT')
conn.close()
return HttpResponseRedirect(reverse('post:all_post', args=(1, 0)))
else:
return redirect('SignIn:signin')
def index(request):
conn = db()
message = ""
if request.method == 'POST':
form = EventForm(request.POST)
if form.is_valid():
info = {
'text': form.cleaned_data['text'],
'name': form.cleaned_data['name'],
'location': form.cleaned_data['location'],
'time_period': form.cleaned_data['time'],
}
if not info['name'] is '':
info['name'] = '%' + info['name'].lower() + '%'
if not info['location'] is '':
info['location'] = '%' + info['location'].lower() + '%'
if not info['text'] is '':
info['text'] = '%' + info['text'].lower() + '%'
values = {}
c = conn.cursor()
results = []
sql = """
SELECT DISTINCT * FROM EVENT WHERE EVENT_ID IN (
"""
sql1 = """SELECT EVENT_ID from EVENT WHERE LOWER(DESCRIPTION) LIKE %(text)s OR LOWER(EVENT_NAME) LIKE %(text)s OR LOWER(LOCATION) LIKE %(text)s
INTERSECT """
sql2 = """SELECT EVENT_ID from EVENT WHERE LOWER(EVENT_NAME) LIKE %(name)s
INTERSECT """
sql3 = """SELECT EVENT_ID from EVENT WHERE LOWER(LOCATION) LIKE %(location)s
INTERSECT """
sql4 = """SELECT EVENT_ID from EVENT WHERE to_date(%(time_period)s,'yyyy-mm-dd') BETWEEN EVENT_START AND EVENT_END
INTERSECT """
if not info['text'] is '':
sql += sql1
values['text'] = info['text']
if not info['name'] is '':
sql += sql2
values['name'] = info['name']
if not info['location'] is '':
sql += sql3
values['location'] = info['location']
if not info['time_period'] is None:
sql += sql4
values['time_period'] = info['time_period']
if sql.endswith('INTERSECT '):
sql = sql[:-len('INTERSECT ')] + ')'
print(sql)
else:
msg = 'Please Type'
return render(request, 'Events/index.html', {
'form': form,
'msg': message
})
c.execute(sql, values)
rows = c.fetchall()
print(rows)
if len(rows) == 0:
message = "No Result Found"
return render(request, 'Events/index.html', {
'form': form,
'msg': message
})
else:
for row in rows:
print(row)
columnNames = [d[0].upper() for d in c.description]
data = (zip(columnNames, row))
results.append(dict(data))
print(results)
return render(
request, 'Events/index.html', {
'form': form,
'msg': message,
'data': results,
'count': len(results)
})
else:
form = EventForm()
sql = """SELECT DISTINCT * FROM EVENT"""
c = conn.cursor()
c.execute(sql)
rows = c.fetchall()
results = []
print("Result Found : " + str(len(rows)))
if len(rows) == 0:
message = "No Result Found"
return render(request, 'Events/index.html', {
'form': form,
'msg': message
})
else:
for row in rows:
columnNames = [d[0].upper() for d in c.description]
data = (zip(columnNames, row))
results.append(dict(data))
return render(request, 'Events/index.html', {
'form': form,
'data': results,
'msg': message
})
def detail_post(request, post_id, start_from):
if "std_id" in request.session:
conn = db()
c = conn.cursor()
user_id = request.session.get('std_id')
c.execute(
'SELECT COUNT(*) FROM USER_REPLIES WHERE POST_ID = %(post_id)s',
{'post_id': post_id})
rows = c.fetchall()
for row in rows:
num_comments = row[0]
begin_comment = start_from
end_comment = (
start_from +
10) if (start_from + 10) <= num_comments else num_comments + 1
the_end = (num_comments == end_comment - 1)
is_begin = (begin_comment == 1)
date_today = date.today().strftime('%d-%m-%Y')
sql = '''SELECT *
FROM(
SELECT A.*, ROW_NUMBER() over()
FROM (SELECT ROW_NUMBER() over() as RNUM, USR_REPLS_ROW, USER_ID, POST_ID, TEXT, TIME_DIFF(TIMESTAMP) FROM USER_REPLIES WHERE POST_ID = %(post_id)s ORDER BY TIMESTAMP DESC, POST_ID DESC) A
WHERE RNUM < %(end_comment)s
) DUMMY
WHERE RNUM >= %(begin_comment)s'''
c.execute(
sql, {
'post_id': post_id,
'end_comment': end_comment,
'begin_comment': begin_comment
})
rows = c.fetchall()
comment_dicts = []
for row in rows:
row = row[1:] #Ignore duplicate row_number()
comment_dict = {}
comment_dict['comment_id'] = row[0]
comment_dict['user_id'] = row[1]
comment_dict['post_id'] = row[2]
comment_dict['text'] = row[3]
comment_dict['timestamp'] = row[4]
comment_dict['logged_in'] = comment_dict['user_id'] == user_id
comment_dicts.append(comment_dict)
for i in range(len(comment_dicts)):
c.execute("SELECT PHOTO FROM PROFILE WHERE STD_ID = '" +
str(comment_dicts[i]['user_id']) + "' ")
rows = c.fetchall()
for row in rows:
comment_dicts[i]['photo_path'] = row[0]
c.execute("SELECT FULL_NAME FROM USER_TABLE WHERE STD_ID = '" +
str(comment_dicts[i]['user_id']) + "' ")
rows = c.fetchall()
for row in rows:
comment_dicts[i]['full_name'] = row[0]
c.execute("SELECT * FROM POST WHERE POST_ID = '" + str(post_id) + "' ")
rows = c.fetchall()
post_detail = {}
for row in rows:
post_detail['date'] = row[1]
post_detail['desc'] = row[2]
post_detail['post_id'] = post_id
c.execute("SELECT USER_ID FROM USER_POSTS WHERE POST_ID = '" +
str(post_id) + "' ")
rows = c.fetchall()
for row in rows:
post_detail['user_id'] = row[0]
c.execute("SELECT PHOTO FROM PROFILE WHERE STD_ID = '" +
str(post_detail['user_id']) + "' ")
rows = c.fetchall()
for row in rows:
post_detail['photo_path'] = row[0]
c.execute("SELECT FULL_NAME FROM USER_TABLE WHERE STD_ID = '" +
str(post_detail['user_id']) + "' ")
rows = c.fetchall()
for row in rows:
post_detail['full_name'] = row[0]
c.execute("SELECT * FROM HELP WHERE POST_ID = '" + str(post_id) + "'")
rows = c.fetchall()
for row in rows:
post_detail['type_of_help'] = row[1]
post_detail['reason'] = row[2]
post_detail['cell'] = row[3]
post_detail['class'] = 'Help'
c.execute("SELECT * FROM CAREER WHERE POST_ID = '" + str(post_id) +
"'")
rows = c.fetchall()
for row in rows:
post_detail['photo'] = row[1]
post_detail['class'] = 'Career'
c.execute("SELECT * FROM RESEARCH WHERE POST_ID = '" + str(post_id) +
"'")
rows = c.fetchall()
for row in rows:
post_detail['topic_name'] = row[1]
post_detail['date_of_publication'] = row[2]
post_detail['journal'] = row[3]
post_detail['doi'] = row[4]
post_detail['class'] = 'Research'
c.execute("SELECT * FROM JOB_POST WHERE POST_ID = '" + str(post_id) +
"'")
rows = c.fetchall()
for row in rows:
post_detail['company_name'] = row[1]
post_detail['location'] = row[2]
post_detail['requirements'] = row[3]
post_detail['designation'] = row[4]
post_detail['salary'] = row[5]
post_detail['class'] = 'Job'
c.execute("SELECT PHOTO FROM PROFILE WHERE STD_ID = %(std_id)s",
{'std_id': user_id})
rows = c.fetchall()
for row in rows:
post_detail['commenter_photo'] = row[0]
#-------------------------------------Profile Card---------------------------------
sql = """ SELECT * from USER_PROFILE WHERE STD_ID = %(std_id)s"""
c.execute(sql, {'std_id': request.session.get('std_id')})
row = c.fetchone()
columnNames = [d[0].upper() for d in c.description]
try:
data = dict(zip(columnNames, row))
except:
print('Cannot Parse Profile')
#-----------------------------------Skills------------------------------------
sql = """ SELECT EXPERTISE.TOPIC, COUNT( ENDORSE.GIVER_ID) AS C from EXPERTISE LEFT JOIN ENDORSE ON
EXPERTISE.STD_ID = ENDORSE.TAKER_ID AND EXPERTISE.TOPIC = ENDORSE.TOPIC WHERE EXPERTISE.STD_ID = %(std_id)s GROUP BY EXPERTISE.TOPIC"""
c.execute(sql, {'std_id': request.session.get('std_id')})
rows = c.fetchall()
skills = {}
for row in rows:
skills[row[0]] = row[1]
dp_form = DPForm()
#--------------------------------------Job History--------------------------------
sql = """ SELECT * from WORKS JOIN INSTITUTE USING(INSTITUTE_ID) WHERE STD_ID = %(std_id)s ORDER BY FROM_ DESC"""
c.execute(sql, {'std_id': request.session.get('std_id')})
jobs = c.fetchall()
columnNames = [d[0].upper() for d in c.description]
job_list = []
for job in jobs:
try:
job_list.append(dict(zip(columnNames, job)))
except:
print('NULL')
context = {
'detail': post_detail,
'comment_dicts': comment_dicts,
'is_begin': is_begin,
'the_end': the_end,
'next_id': end_comment,
'prev_id': (begin_comment - 10) if begin_comment > 10 else 0,
'post_id': post_id,
'data': data,
'skills': skills,
'edit': True,
'dp': dp_form,
'job': job_list,
}
return render(request, 'post/detail_post.html', context)
else:
return redirect('SignIn:signin')
def search(request):
conn = db()
message = ""
if request.method == 'POST':
form = SearchForm(request.POST)
if form.is_valid():
info = {
'name' : form.cleaned_data['name'],
'location' : form.cleaned_data['location'],
'institute': form.cleaned_data['institution'],
'interest': form.cleaned_data['interest'],
'hall':form.cleaned_data['hall'],
'term':form.cleaned_data['term'],
'dept':form.cleaned_data['dept'],
}
if not info['name'] is '':
info['name'] = '%{}%'.format(info['name'].lower()) #'%' + info['name'].lower() + '%'
if not info['location'] is '':
info['location'] = '%' + info['location'].lower() + '%'
if not info['interest'] is '':
info['interest'] = '%' + info['interest'].lower() + '%'
if not info['hall'] is '':
info['hall'] = '%' + info['hall'].lower() + '%'
if not info['term'] is '':
info['term'] = '%' + info['term'].lower() + '%'
if not info['dept'] is '':
info['dept'] = '%' + info['dept'].lower() + '%'
if not info['institute'] is '':
info['institute'] = '%' + info['institute'].lower() + '%'
values = {}
c = conn.cursor()
results = []
sql = """
SELECT DISTINCT * FROM USER_PROFILE WHERE STD_ID IN (
"""
sql1 = """SELECT STD_ID from USER_TABLE WHERE LOWER(FULL_NAME) LIKE %(name)s OR LOWER(NICK_NAME) LIKE %(name)s OR STD_ID::varchar(16) LIKE %(name)s
INTERSECT """
sql2 ="""SELECT STD_ID from PROFILE WHERE LOWER(PROFILE.COUNTRY) LIKE %(location)s OR LOWER(PROFILE.CITY) LIKE %(location)s
OR LOWER(HOME_TOWN) LIKE %(location)s
INTERSECT """
sql3 ="""SELECT STD_ID from EXPERTISE WHERE LOWER(TOPIC) LIKE LOWER(%(interest)s)
INTERSECT """
sql4 ="""SELECT STD_ID from PROFILE WHERE LOWER(DEPT) LIKE LOWER(%(dept)s)
INTERSECT """
sql5 ="""SELECT STD_ID from PROFILE WHERE LOWER(HALL) LIKE LOWER(%(hall)s)
INTERSECT """
sql6 ="""SELECT STD_ID from PROFILE WHERE LOWER(LVL::varchar(8)) LIKE LOWER(%(lvl)s)
INTERSECT """
sql7 ="""SELECT STD_ID from WORKS LEFT JOIN INSTITUTE USING(INSTITUTE_ID)
WHERE LOWER(NAME) LIKE LOWER(%(institute)s)
INTERSECT """
if not info['name'] is '':
sql += sql1
values['name'] = info['name']
if not info['location'] is '':
sql +=sql2
values['location'] =info['location']
if not info['interest'] is '':
sql+=sql3
values['interest'] = info['interest']
if not info['dept'] is '':
sql+=sql4
values['dept'] = info['dept']
if not info['hall'] is '':
sql+=sql5
values['hall'] = info['hall']
if not info['term'] is '':
sql+=sql6
values['lvl'] = info['term']
if not info['institute'] is '':
sql+=sql7
values['institute'] = info['institute']
if sql.endswith('INTERSECT '):
sql = sql[:-len('INTERSECT ')]+ ')'
print(sql)
else:
msg = 'Please Type'
return render(request,'Search/search.html',{'form':form, 'msg' : message})
c.execute(sql,values)
rows = c.fetchall()
print("Result Found : " + str(len(rows)))
if len(rows) == 0 :
message = "No Result Found"
return render(request,'Search/search.html',{'form':form, 'msg' : message})
else:
for row in rows:
columnNames = [d[0].upper() for d in c.description]
data = (zip(columnNames,row))
results.append(dict(data))
return render(request,'Search/search.html',{'form':form, 'msg' : message,'data':results,'count':len(results)})
else:
form = SearchForm()
return render(request,'Search/search.html',{'form':form, 'msg' : message})
def all_post(request, start_from, change):
print("IN ALL POST")
if "std_id" in request.session:
data = None
conn = db()
c = conn.cursor()
print("IN ALL POST")
user_id = request.session.get('std_id')
#-------------------------------------Profile Card---------------------------------
sql = """ SELECT * from USER_PROFILE WHERE STD_ID = %(std_id)s"""
c.execute(sql, {'std_id': request.session.get('std_id')})
row = c.fetchone()
columnNames = [d[0].upper() for d in c.description]
try:
data = dict(zip(columnNames, row))
except:
print('Cannot Parse Profile')
print("IN ALL POST")
#-----------------------------------Skills------------------------------------
sql = """ SELECT EXPERTISE.TOPIC, COUNT( ENDORSE.GIVER_ID) AS C from EXPERTISE LEFT JOIN ENDORSE ON
EXPERTISE.STD_ID = ENDORSE.TAKER_ID AND EXPERTISE.TOPIC = ENDORSE.TOPIC WHERE EXPERTISE.STD_ID = %(std_id)s GROUP BY EXPERTISE.TOPIC"""
c.execute(sql, {'std_id': request.session.get('std_id')})
rows = c.fetchall()
skills = {}
for row in rows:
skills[row[0]] = row[1]
dp_form = DPForm()
#--------------------------------------Job History--------------------------------
sql = """ SELECT * from WORKS JOIN INSTITUTE USING(INSTITUTE_ID) WHERE STD_ID = %(std_id)s ORDER BY FROM_ DESC"""
c.execute(sql, {'std_id': request.session.get('std_id')})
jobs = c.fetchall()
columnNames = [d[0].upper() for d in c.description]
job_list = []
for job in jobs:
try:
job_list.append(dict(zip(columnNames, job)))
except:
print('NULL')
if change == 0:
# set to default
request.session['search_std_id'] = ''
request.session['search_post_typ'] = None
search_post_typ = None
search_std_id = ''
elif change == 1:
#dont change
search_post_typ = request.session['search_post_typ']
search_std_id = request.session['search_std_id']
elif change == 2:
#update
search_std_id = request.GET.get('search_std_id')
search_post_typ = request.GET.get('search_post_typ')
request.session['search_std_id'] = search_std_id
request.session['search_post_typ'] = search_post_typ
if (search_post_typ is None) and ((search_std_id is None) or
(len(search_std_id) == 0)):
c.execute('Select count(*) from post;')
if (search_post_typ is None) and ((search_std_id is not None) and
(len(search_std_id) > 0)):
sql = '''SELECT COUNT(*) FROM POST WHERE STRPOS(DESCRIPTION, %(search_std_id)s ) > 0'''
c.execute(sql, {'search_std_id': search_std_id})
if (search_post_typ is not None) and ((search_std_id is None) or
(len(search_std_id) == 0)):
if search_post_typ == 'Help':
c.execute('SELECT COUNT(*) FROM HELP')
if search_post_typ == 'Career':
c.execute('SELECT COUNT(*) FROM CAREER')
if search_post_typ == 'Research':
c.execute('SELECT COUNT(*) FROM RESEARCH')
if search_post_typ == 'Job Post':
c.execute('SELECT COUNT(*) FROM JOB_POST')
if (search_post_typ is not None) and ((search_std_id is not None) and
(len(search_std_id) > 0)):
if search_post_typ == 'Help':
sql = '''SELECT COUNT(*) FROM POST P, HELP H WHERE (H.POST_ID = P.POST_ID) AND ( STRPOS(P.DESCRIPTION, %(search_std_id)s) > 0 )'''
c.execute(sql, {'search_std_id': search_std_id})
if search_post_typ == 'Career':
sql = '''SELECT COUNT(*) FROM POST P, CAREER C WHERE (C.POST_ID = P.POST_ID) AND ( STRPOS(P.DESCRIPTION, %(search_std_id)s) > 0 )'''
c.execute(sql, {'search_std_id': search_std_id})
if search_post_typ == 'Research':
sql = '''SELECT COUNT(*) FROM POST P, RESEARCH R WHERE (R.POST_ID = P.POST_ID) AND ( STRPOS(P.DESCRIPTION, %(search_std_id)s) > 0 )'''
c.execute(sql, {'search_std_id': search_std_id})
if search_post_typ == 'Job Post':
sql = '''SELECT COUNT(*) FROM POST P, JOB_POST J WHERE (J.POST_ID = P.POST_ID) AND ( STRPOS(P.DESCRIPTION, %(search_std_id)s) > 0 )'''
c.execute(sql, {'search_std_id': search_std_id})
num_post = modify_c(c)[0]
if num_post == '0':
context = {
#'user':user,
'no_post': True,
'data': data,
'skills': skills,
'edit': True,
'dp': dp_form,
'job': job_list,
}
print(data, skills, job_list)
return render(request, 'post/all_post.html', context)
else:
num_post = int(num_post)
begin_post = start_from
end_post = (start_from +
5) if (start_from + 5) <= num_post else num_post + 1
the_end = (num_post == end_post - 1)
is_begin = (begin_post == 1)
if (search_post_typ is None) and ((search_std_id is None) or
(len(search_std_id) == 0)):
sql = '''
SELECT *
FROM(
SELECT A.*, ROW_NUMBER() over()
FROM (SELECT ROW_NUMBER() over() AS RNUM, POST_ID, TIME_DIFF(DATE_OF_POST), DESCRIPTION FROM POST ORDER BY DATE_OF_POST DESC, POST_ID DESC) A
WHERE RNUM < %(end_post)s
) DUMMY_ALIAS
WHERE RNUM >= %(begin_post)s '''
c.execute(sql, {
'end_post': end_post,
'begin_post': begin_post
})
rows = c.fetchall()
print(rows)
all_post = [row[1:] for row in rows]
if (search_post_typ is None) and ((search_std_id is not None) and
(len(search_std_id) > 0)):
sql = '''
SELECT *
FROM(
SELECT A.*, ROW_NUMBER() over()
FROM(
SELECT ROW_NUMBER() over() AS RNUM, POST_ID, TIME_DIFF(DATE_OF_POST), DESCRIPTION
FROM POST
WHERE ( STRPOS(DESCRIPTION, %(search_std_id)s) > 0 )
ORDER BY DATE_OF_POST DESC, POST_ID DESC
) A
WHERE RNUM < %(end_post)s
) AS DUMMY
WHERE RNUM >= %(begin_post)s'''
c.execute(
sql, {
'end_post': end_post,
'begin_post': begin_post,
'search_std_id': search_std_id
})
rows = c.fetchall()
all_post = [row[1:] for row in rows]
if (search_post_typ is not None) and ((search_std_id is None) or
(len(search_std_id) == 0)):
if search_post_typ == 'Help':
sql = '''
SELECT *
FROM(
SELECT A.*, ROW_NUMBER() over()
FROM(
SELECT ROW_NUMBER() over() AS RNUM, P.POST_ID, TIME_DIFF(P.DATE_OF_POST), P.DESCRIPTION
FROM POST P, HELP H
WHERE (P.POST_ID = H.POST_ID)
ORDER BY P.DATE_OF_POST DESC, P.POST_ID DESC
) A
WHERE RNUM < %(end_post)s
) DUMMY
WHERE RNUM >= %(begin_post)s'''
c.execute(sql, {
'end_post': end_post,
'begin_post': begin_post
})
rows = c.fetchall()
all_post = [row[1:] for row in rows]
if search_post_typ == 'Career':
sql = '''
SELECT *
FROM(
SELECT A.*, ROW_NUMBER() over()
FROM(
SELECT ROW_NUMBER() over() AS RNUM, P.POST_ID, TIME_DIFF(P.DATE_OF_POST), P.DESCRIPTION
FROM POST P, CAREER C
WHERE (P.POST_ID = C.POST_ID)
ORDER BY P.DATE_OF_POST DESC, P.POST_ID DESC
) A
WHERE RNUM < %(end_post)s
)DUMMY
WHERE RNUM >= %(begin_post)s'''
c.execute(sql, {
'end_post': end_post,
'begin_post': begin_post
})
rows = c.fetchall()
all_post = [row[1:] for row in rows]
if search_post_typ == 'Research':
sql = '''
SELECT *
FROM(
SELECT A.*, ROW_NUMBER() over()
FROM(
SELECT ROW_NUMBER() over() AS RNUM, P.POST_ID, TIME_DIFF(P.DATE_OF_POST), P.DESCRIPTION
FROM POST P, RESEARCH R
WHERE (P.POST_ID = R.POST_ID)
ORDER BY P.DATE_OF_POST DESC, P.POST_ID DESC
) A
WHERE RNUM < %(end_post)s
)DUMMY
WHERE RNUM >= %(begin_post)s'''
c.execute(sql, {
'end_post': end_post,
'begin_post': begin_post
})
rows = c.fetchall()
all_post = [row[1:] for row in rows]
if search_post_typ == 'Job Post':
sql = '''
SELECT *
FROM(
SELECT A.*, ROW_NUMBER() over()
FROM(
SELECT ROW_NUMBER() over() AS RNUM, P.POST_ID, TIME_DIFF(P.DATE_OF_POST), P.DESCRIPTION
FROM POST P, JOB_POST J
WHERE (P.POST_ID = J.POST_ID)
ORDER BY P.DATE_OF_POST DESC, P.POST_ID DESC
) A
WHERE RNUM < %(end_post)s
)DUMMY
WHERE RNUM >= %(begin_post)s'''
c.execute(sql, {
'end_post': end_post,
'begin_post': begin_post
})
rows = c.fetchall()
all_post = [row[1:] for row in rows]
if (search_post_typ is not None) and (
(search_std_id is not None) and (len(search_std_id) > 0)):
if search_post_typ == 'Help':
sql = '''
SELECT *
FROM(
SELECT A.*, ROW_NUMBER() over()
FROM(
SELECT ROW_NUMBER() over() AS RNUM, P.POST_ID, TIME_DIFF(P.DATE_OF_POST), P.DESCRIPTION
FROM POST P, HELP H
WHERE (P.POST_ID = H.POST_ID) AND ( STRPOS(P.DESCRIPTION, %(search_std_id)s) > 0 )
ORDER BY P.DATE_OF_POST DESC, P.POST_ID DESC
) A
WHERE RNUM < %(end_post)s
)DUMMY
WHERE RNUM >= %(begin_post)s'''
c.execute(
sql, {
'end_post': end_post,
'begin_post': begin_post,
'search_std_id': search_std_id
})
rows = c.fetchall()
all_post = [row[1:] for row in rows]
if search_post_typ == 'Career':
sql = '''
SELECT *
FROM(
SELECT A.*, ROW_NUMBER() over()
FROM(
SELECT ROW_NUMBER() over() AS RNUM, P.POST_ID, TIME_DIFF(P.DATE_OF_POST), P.DESCRIPTION
FROM POST P, CAREER C
WHERE (P.POST_ID = C.POST_ID) AND ( STRPOS(P.DESCRIPTION, %(search_std_id)s) > 0 )
ORDER BY P.DATE_OF_POST DESC, P.POST_ID DESC
) A
WHERE RNUM < %(end_post)s
)DUMMY
WHERE RNUM >= %(begin_post)s'''
c.execute(
sql, {
'end_post': end_post,
'begin_post': begin_post,
'search_std_id': search_std_id
})
rows = c.fetchall()
all_post = [row[1:] for row in rows]
if search_post_typ == 'Research':
sql = '''
SELECT *
FROM(
SELECT A.*, ROW_NUMBER() over()
FROM(
SELECT ROW_NUMBER() over() AS RNUM, P.POST_ID, TIME_DIFF(P.DATE_OF_POST), P.DESCRIPTION
FROM POST P, RESEARCH R
WHERE (P.POST_ID = R.POST_ID) AND ( STRPOS(P.DESCRIPTION, %(search_std_id)s) > 0 )
ORDER BY P.DATE_OF_POST DESC, P.POST_ID DESC
) A
WHERE RNUM < %(end_post)s
)DUMMY
WHERE RNUM >= %(begin_post)s'''
c.execute(
sql, {
'end_post': end_post,
'begin_post': begin_post,
'search_std_id': search_std_id
})
rows = c.fetchall()
all_post = [row[1:] for row in rows]
if search_post_typ == 'Job Post':
sql = '''
SELECT *
FROM(
SELECT A.*, ROW_NUMBER() over()
FROM(
SELECT ROW_NUMBER() over() AS RNUM, P.POST_ID, TIME_DIFF(P.DATE_OF_POST), P.DESCRIPTION
FROM POST P, JOB_POST J
WHERE (P.POST_ID = J.POST_ID) AND ( STRPOS(P.DESCRIPTION, %(search_std_id)s) > 0 )
ORDER BY P.DATE_OF_POST DESC, P.POST_ID DESC
) A
WHERE RNUM < %(end_post)s
)DUMMY
WHERE RNUM >= %(begin_post)s'''
c.execute(
sql, {
'end_post': end_post,
'begin_post': begin_post,
'search_std_id': search_std_id
})
rows = c.fetchall()
all_post = [row[1:] for row in rows]
all_post_dicts = []
for post in all_post:
post_dict = {}
post_dict['post_id'] = post[0]
post_dict['date'] = post[1]
post_dict['desc'] = post[2]
c.execute("SELECT USER_ID FROM USER_POSTS WHERE POST_ID = '" +
str(post_dict['post_id']) + "' ")
rows = c.fetchall()
for row in rows:
print(f'USER ID {row[0]}')
user_id = row[0]
post_dict['user_id'] = user_id
post_dict['logged_in'] = user_id == request.session[
'std_id']
c.execute("SELECT PHOTO FROM PROFILE WHERE STD_ID = '" +
str(user_id) + "' ")
rows = c.fetchall()
for row in rows:
post_dict['photo_path'] = row[0]
c.execute("SELECT FULL_NAME FROM USER_TABLE WHERE STD_ID = '" +
str(user_id) + "' ")
rows = c.fetchall()
for row in rows:
post_dict['full_name'] = row[0]
c.execute(
"SELECT COUNT(*) FROM USER_REPLIES WHERE POST_ID = %(post_id)s",
{'post_id': post_dict['post_id']})
rows = c.fetchall()
for row in rows:
post_dict['num_comments'] = row[0]
c.execute("SELECT * FROM HELP WHERE POST_ID = '" +
str(post_dict['post_id']) + "'")
rows = c.fetchall()
for row in rows:
post_dict['class'] = 'help'
c.execute("SELECT * FROM CAREER WHERE POST_ID = '" +
str(post_dict['post_id']) + "'")
rows = c.fetchall()
for row in rows:
post_dict['class'] = 'career'
c.execute("SELECT * FROM RESEARCH WHERE POST_ID = '" +
str(post_dict['post_id']) + "'")
rows = c.fetchall()
for row in rows:
post_dict['class'] = 'research'
c.execute("SELECT * FROM JOB_POST WHERE POST_ID = '" +
str(post_dict['post_id']) + "'")
rows = c.fetchall()
for row in rows:
post_dict['class'] = 'job'
if ((search_std_id is not None) and (len(search_std_id) > 0)):
post_dict[
'desc_selected'] = description_after_text_search2(
post_dict['desc'], search_std_id)
post_dict['query'] = search_std_id
all_post_dicts.append(post_dict)
print("IN ALL POST")
print(all_post_dicts)
context = {
'no_post':
False,
'post_dicts':
all_post_dicts,
'is_begin':
is_begin,
'the_end':
the_end,
'next_id':
end_post,
'prev_id': (begin_post - 5) if begin_post > 5 else 0,
'search_post_typ':
search_post_typ,
'search_std_id':
search_std_id,
'data':
data,
'skills':
skills,
'edit':
True,
'dp':
dp_form,
'job':
job_list,
'doing_text_search':
True if ((search_std_id is not None) and
(len(search_std_id) > 0)) else False,
'orig_start':
start_from
}
print(data, skills)
return render(request, 'post/all_post.html', context)
else:
return redirect('SignIn:signin')
def edit(request):
conn = db()
c = conn.cursor()
message = ""
sql = """SELECT * from USER_PROFILE WHERE STD_ID = %(std_id)s"""
c.execute(sql, {'std_id': request.session.get('std_id')})
row = c.fetchone()
columnNames = [d[0].upper() for d in c.description]
data = dict(zip(columnNames, row))
print(data)
dp_form = DPForm()
form_signup = EditForm(
initial={
'fullname': data['FULL_NAME'],
'nickname': data['NICK_NAME'],
'email': data['EMAIL'],
'mobile': data['MOBILE'],
'birthdate': data['DATE_OF_BIRTH'],
'dept': data['DEPT'],
'hall': data['HALL'],
'level': data['LVL'],
'term': data['TERM'],
'msc': data['MSC'],
'phd': data['PHD'],
'house': data['HOUSE_NO'],
'road': data['ROAD_NO'],
'zipcode': data['ZIP_CODE'],
'city': data['CITY'],
'country': data['COUNTRY'],
'hometown': data['HOME_TOWN'],
'about': data['ABOUT'],
'fb': data['FACEBOOK'],
'twitter': data['TWITTER'],
'linkedin': data['LINKEDIN'],
'google': data['GOOGLE_SCHOLAR'],
'rg': data['RESEARCHGATE']
})
if request.method == 'POST':
form_signup = EditForm(request.POST)
if form_signup.is_valid():
user = {
'std_id':
request.session.get('std_id'),
'fullname':
form_signup.cleaned_data['fullname'],
'nickname':
form_signup.cleaned_data['nickname'],
'email':
form_signup.cleaned_data['email'],
'mobile':
form_signup.cleaned_data['mobile'],
'birthdate':
str(form_signup.cleaned_data['birthdate'].strftime('%Y-%m-%d'))
if form_signup.cleaned_data['birthdate'] is not None else None,
}
undergrad = {
'std_id': request.session.get('std_id'),
'hall': form_signup.cleaned_data['hall'],
'dept': form_signup.cleaned_data['dept'],
'lvl': form_signup.cleaned_data['level'],
'term': form_signup.cleaned_data['term']
}
postgrad = {
'std_id': request.session.get('std_id'),
'msc': form_signup.cleaned_data['msc'],
'phd': form_signup.cleaned_data['phd']
}
profile = {
'std_id': request.session.get('std_id'),
'house': form_signup.cleaned_data['house'],
'road': form_signup.cleaned_data['road'],
'zip': form_signup.cleaned_data['zipcode'],
'city': form_signup.cleaned_data['city'],
'country': form_signup.cleaned_data['country'],
'hometown': form_signup.cleaned_data['hometown'],
'about': form_signup.cleaned_data['about'],
'fb': form_signup.cleaned_data['fb'],
'twitter': form_signup.cleaned_data['twitter'],
'linkedin': form_signup.cleaned_data['linkedin'],
'rg': form_signup.cleaned_data['rg'],
'google': form_signup.cleaned_data['google'],
}
print('HEERE')
print(undergrad)
sql = """ UPDATE USER_TABLE SET FULL_NAME = %(fullname)s, NICK_NAME = %(nickname)s,EMAIL=%(email)s,MOBILE=%(mobile)s,DATE_OF_BIRTH=to_date(%(birthdate)s,'yyyy-mm-dd') WHERE STD_ID=%(std_id)s"""
try:
c.execute(sql, user)
conn.commit()
print('Updated User')
except IntegrityError:
message = "User already exists ..."
print('Error Updating User')
#-----------------Update Profile---------------------
sql = """ SELECT * from PROFILE WHERE STD_ID = %(std_id)s"""
print(profile)
c.execute(sql, {'std_id': user['std_id']})
row = c.fetchone()
if row is None:
sql = """ INSERT INTO PROFILE (STD_ID,HOUSE_NO,ROAD_NO,ZIP_CODE,CITY,COUNTRY,HOME_TOWN,ABOUT,FACEBOOK,TWITTER, LINKEDIN ,RESEARCHGATE, GOOGLE_SCHOLAR)
VALUES(%(std_id)s,%(house)s,%(road)s,%(zip)s,%(city)s,%(country)s,%(hometown)s,%(about)s,%(fb)s,%(twitter)s,%(linkedin)s,%(rg)s,%(google)s)"""
try:
c.execute(sql, profile)
conn.commit()
print('Inserted Profile')
except IntegrityError:
message = "User already exists ..."
print('Error Updating Profile 1')
else:
sql = """ UPDATE PROFILE SET HOUSE_NO = %(house)s,ROAD_NO = %(road)s, ZIP_CODE = %(zip)s,CITY = %(city)s,COUNTRY = %(country)s,HOME_TOWN = %(hometown)s,ABOUT=%(about)s,
FACEBOOK=%(fb)s,TWITTER=%(twitter)s, LINKEDIN=%(linkedin)s, RESEARCHGATE=%(rg)s,GOOGLE_SCHOLAR=%(google)s
WHERE STD_ID = %(std_id)s"""
try:
c.execute(sql, profile)
conn.commit()
print('Updated User')
except IntegrityError:
message = "User already exists ..."
print('Error Updating Profile 2')
#------------------------Update Undergrad----------------------
sql = """ SELECT * from UNDERGRAD WHERE STD_ID = %(std_id)s"""
c.execute(sql, {'std_id': user['std_id']})
row = c.fetchone()
if row is None:
sql = """ INSERT INTO UNDERGRAD (STD_ID,HALL,DEPT,LVL,TERM)
VALUES(%(std_id)s,%(hall)s,%(dept)s,%(lvl)s,%(term)s)"""
try:
c.execute(sql, undergrad)
conn.commit()
print('Inserted UnderGrad')
except IntegrityError:
message = "User already exists ..."
print('Error Updating Undergrad 1')
else:
sql = """ UPDATE UNDERGRAD SET HALL =%(hall)s,DEPT=%(dept)s,LVL=%(lvl)s,TERM=%(term)s
WHERE STD_ID =%(std_id)s"""
try:
c.execute(sql, undergrad)
conn.commit()
print('Updated Undergrad')
except IntegrityError:
message = "User already exists ..."
print('Error Updating Undergrad 2')
#-----------------Update Postgrad ----------------------------
sql = """ SELECT * from POSTGRAD WHERE STD_ID = %(std_id)s"""
c.execute(sql, {'std_id': user['std_id']})
row = c.fetchone()
if row is None:
sql = """ INSERT INTO POSTGRAD (STD_ID,MSC,PHD)
VALUES(%(std_id)s,%(msc)s,%(phd)s)"""
try:
c.execute(sql, postgrad)
conn.commit()
print('Inserted PostGrad')
except IntegrityError:
message = "User already exists ..."
print('Error Updating PostGrad 1')
else:
sql = """ UPDATE POSTGRAD SET MSC=%(msc)s,PHD=%(phd)s
WHERE STD_ID = %(std_id)s"""
try:
c.execute(sql, postgrad)
conn.commit()
print('Updated PostGrad')
except IntegrityError:
message = "User already exists ..."
print('Error Updating PostGrad 2')
return redirect('Profile:profile')
else:
print('Error While Editing Profile')
expertise = ExpertForm()
sql = """SELECT EXPERTISE.TOPIC, COUNT( ENDORSE.GIVER_ID) AS C from EXPERTISE LEFT JOIN ENDORSE ON
EXPERTISE.STD_ID = ENDORSE.TAKER_ID AND EXPERTISE.TOPIC = ENDORSE.TOPIC WHERE EXPERTISE.STD_ID = %(std_id)s GROUP BY EXPERTISE.TOPIC"""
c.execute(sql, {'std_id': request.session.get('std_id')})
rows = c.fetchall()
skills = {}
for row in rows:
skills[row[0]] = row[1]
job_form = JobForm()
#------------------------------------Job History---------------------------------
sql = """ SELECT * from WORKS JOIN INSTITUTE USING(INSTITUTE_ID) WHERE STD_ID = %(std_id)s ORDER BY FROM_ DESC"""
c.execute(sql, {'std_id': request.session.get('std_id')})
jobs = c.fetchall()
columnNames = [d[0].upper() for d in c.description]
job_list = []
for job in jobs:
try:
job_list.append(dict(zip(columnNames, job)))
except:
print('Cannot Parse Job')
return render(
request, 'Profile/edit.html', {
'form': form_signup,
'data': data,
'jobs': job_list,
'skills': skills,
'job': job_form,
'msg': message,
'dp': dp_form,
'expert': expertise,
'skill_error': skill_error
})