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 })