Skip to content
Permalink
master
Switch branches/tags

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

UConnJobSearch/generateReportNew.php

Go to file
 
 
Cannot retrieve contributors at this time
633 lines (559 sloc) 22 KB
Raw Blame
Open in GitHub Desktop
<!-- Authored by Everyone? -->
<?php
session_start();
$con = connectDB();
//-- Get user id
$user = $_SESSION["myusername"];
$sql = "SELECT SSkillName FROM resume, skillset, skill WHERE UName='" . $user . "'"
. " AND resume.ResumeID=skillset.ResumeID AND skillset.SSkillID=skill.SSkillID";
$result = mysqli_query($con, $sql);
$skills = array();
while($row = mysqli_fetch_array($result)) {
array_push($skills, $row['SSkillName']);
}
$_SESSION['skills'] = $skills;
disconnectDB($con);
function connectDB(){
$con = mysqli_connect("localhost", "root", "", "databasesproject") or die(mysql_error());
return $con;
}
function disconnectDB($con){
mysqli_close($con);
}
?>
<!DOCTYPE html>
<html>
<head>
<!-- META -->
<title>Report Summary</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
<meta name="description" content="" />
<!-- CSS -->
<link rel="stylesheet" type="text/css" href="css/kickstart.css" media="all" />
<link rel="stylesheet" type="text/css" href="style.css" media="all" />
<link rel="stylesheet" type="text/css" href="list.css" media="all" />
<!-- Javascript -->
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script type="text/javascript" src="js/kickstart.js"></script>
<link href="http://s3.amazonaws.com/codecademy-content/courses/ltp/css/shift.css" rel="stylesheet">
<link rel="stylesheet" href="http://s3.amazonaws.com/codecademy-content/courses/ltp/css/bootstrap.css">
<link rel="stylesheet" href="generateReport_formatting.css">
<div class="nav">
<div class="container">
<ul class="pull-left">
<!-- <li><a href=<img><></li> -->
<li><a href="http://localhost/UConnJobSearch/index.php">UConnJobSearch</a></li>
<li>|</li>
<li><a href="http://localhost/UConnJobSearch/about.php">About</a></li>
</ul>
<ul class="pull-right">
<?php if ($_SESSION): ?>
<?php if ($_SESSION['type'] == "Admin"): ?>
<li><a href="http://localhost/UConnJobSearch/admin_dashboard.php">Dashboard</a></li>
<li>|</li>
<li><a href="http://localhost/UConnJobSearch/admin.php">My Account</a></li>
<li>|</li>
<li><a href="http://localhost/UConnJobSearch/logout.php">Log Out</a></li>
<li>|</li>
<li><a href="http://localhost/UConnJobSearch/faq.php">Help</a></li>
<?php elseif ($_SESSION['type'] == "Poster"): ?>
<li><a href="http://localhost/UConnJobSearch/posterDashboard.php">Dashboard</a></li>
<li>|</li>
<li><a href="http://localhost/UConnJobSearch/poster.php">My Account</a></li>
<li>|</li>
<li><a href="http://localhost/UConnJobSearch/logout.php">Log Out</a></li>
<li>|</li>
<li><a href="http://localhost/UConnJobSearch/faq.php">Help</a></li>
<?php elseif ($_SESSION['type'] == "Seeker"): ?>
<li><a href="http://localhost/UConnJobSearch/dashboard.php">Dashboard</a></li>
<li>|</li>
<li><a href="http://localhost/UConnJobSearch/seeker.php">My Account</a></li>
<li>|</li>
<li><a href="http://localhost/UConnJobSearch/logout.php">Log Out</a></li>
<li>|</li>
<li><a href="http://localhost/UConnJobSearch/faq.php">Help</a></li>
<?php endif; ?> <!-- not logged in -->
<?php else: header("location:error.php"); ?>
<li><a href="http://localhost/UConnJobSearch/register.php">Register</a></li>
<li>|</li>
<li><a href="http://localhost/UConnJobSearch/login.php">Log In</a></li>
<li>|</li>
<li><a href="http://localhost/UConnJobSearch/faq.php">Help</a></li>
<?php endif; ?>
</ul>
</div>
</div>
<div class="jumbotron">
<div class="container">
<h1>Generate Reports</h1>
</div>
</div>
</head>
<body>
<div class="grid">
<ul class="tabs">
<li><a href="#q1"><i class="fa fa-folder-open"></i> Search Jobs</a></li>
<li><a href="#q2"><i class="fa fa-flash"></i> Search Seekers</a></li>
<li><a href="#q3"><i class="fa fa-fire"></i> View Payment Report</a></li>
</ul>
<div class = "col_12">
<style type="text/css">
#q1 a.button, #q1 button{margin-bottom:10px;}
</style>
<div id="q1" class="tab-content">
<div class = "col_12">
<label for="text26">Search Jobs</label><br><br>
<table class='table table-striped'>
<tr><td>
<input type="checkbox" id="CBLastName">
<label for="text26">Seeker's Last Name: </label>
<input type="text" id="lastName" style="color:#000" placeholder="Last Name" /><br>
</td></tr>
<tr><td>
<input type="checkbox" id="CBCompanyName">
<label for="text26">Company Name: </label>
<input type="text" id="companyName" style="color:#000" placeholder="Company Name" /><br>
</td></tr>
<!-- Pair 0 -->
<tr><td>
<input type="checkbox" id="CBStartDate" onclick="checkPair(0, 0)">
<label for="text26">Start Date: </label>
<input type="text" id="startDate" style="color:#000"placeholder="mm/dd/yyyy" /><br><br>
<input type="checkbox" id="CBEndDate" onclick="checkPair(0, 1)">
<label for="text26">End Date: </label>
<input type="text" id="endDate" style="color:#000" placeholder="mm/dd/yyyy" /><br>
</td><tr>
<!-- Pair 1 -->
<tr><td>
<input type="checkbox" id="CBSalary" onclick="checkPair(1,0)">
<label for="text26">Salary:</label>
<input type="text" id="salary" style="color:#000" placeholder="Salary" /><br><br>
<input type="checkbox" id="CBJobTitle" onclick="checkPair(1,1)">
<label for="text26">Job Title:</label>
<input type="text" id="jobTitle" style="color:#000" placeholder="Job Title" /><br>
</td></tr>
<tr><td>
<input type="checkbox" id="CBSkills9">
<label for="text26">Require the following skills:</label><br>
<ul id='skillListJob' class='skill-list'></ul>
<input type="text" id="newSkillJob" placeholder="New Skill"/>
<button onclick="addSkill(0)">Add Skill</button><br>
<select name='removeSkillJob' id='selectRemoveJob'></select>
<button onclick="removeSkill(0)">Remove Skill</button><br>
</td><tr>
</table>
Check the fields that you would like to use in searching available jobs.<br>
<button class="medium blue" onclick="query1()"><i class="fa fa-star"></i> Generate Report</button><br>
</div>
</div>
<div id="q2" class="tab-content">
<div class = "col_12">
<label for="text26">Search Seekers</label><br><br>
<table class='table table-striped'>
<tr><td>
<input type="checkbox" id="CBGeneralReport">
<label for="text26">View All Seekers</label><br> <!-- Q1 -->
</td><tr>
<tr><td>
<input type="checkbox" id="CBJobID">
<label for="text26">Job ID: </label> <!-- Q6 -->
<input type="text" id="jobID" style="color:#000" placeholder="Job ID" /><br>
</td><tr>
<tr><td>
<input type="checkbox" id="CBCollege">
<label for="text26">College: </label>
<input type="text" id="college" style="color:#000" placeholder="College" /><br>
</td><tr>
<tr><td>
<input type="checkbox" id="CBSkills10">
<label for="text26">Must have the following skills:</label><br>
<ul id='skillListSeeker' class='skill-list'></ul><br>
<input type="text" id="newSkillSeeker" placeholder="New Skill"/>
<button onclick="addSkill(1)">Add Skill</button><br>
<select name='removeSkillSeeker' id='selectRemoveSeeker'></select>
<button onclick="removeSkill(1)">Remove Skill</button><br>
</td><tr>
</table>
Check the fields that you would like to use in looking up seekers.<br>
<button class="medium blue" onclick="query2()"><i class="fa fa-star"></i> Generate Report</button> <br>
</div>
</div>
<div id="q3" class="tab-content">
<div class = "col_12">
<label for="text26">Payment Start Date</label>
<input type="text" id="pStartDate" style="color:#000" placeholder="mm/dd/yyyy" /><br><br>
<label for="text26">Payment End Date</label>
<input type="text" id="pEndDate" style="color:#000" placeholder="mm/dd/yyyy" /><br><br>
<button class="medium blue" onclick="query3()"><i class="fa fa-star"></i> Generate Report</button>
</div>
</div>
<div id='tableContent'></div>
</div>
</div>
</div>
</div>
<div class="credits">
<footer> <!-- site wide footer -->
<nav>
<p><a href="http://localhost/UConnJobSearch/credits.php">Credits</a>
<a>|</a>
<a href="">Terms of Service</a>
<a>|</a>
<a herf="">Blog Index</a>
</p>
</nav>
<p>Copyright &#169 2015 UConn</p>
</footer>
</div> <!-- End Credits -->
</body>
<head>
<!-- JQuery Reference -->
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script> <!-- Javascript functions here -->
var q = select = from = where = groupby = "";;
var listJob = document.getElementById('skillListJob');
var listSeeker = document.getElementById('skillListSeeker');
var dropJob = document.getElementById('selectRemoveJob');
var dropSeeker = document.getElementById('selectRemoveSeeker');
initSkills();
function queryForm(){
$.ajax({
url: "query.php",
data: {
query: q
},
type: "POST",
dataType: "html",
success: function (data) {
document.getElementById("tableContent").innerHTML = data;
$('#stateTable').show();
},
error: function (xhr, status) {
alert("There was a problem with the request!");
}
});
}
function checkPair(whichPair, whichOne){
if(whichPair == 0){
var cb = document.getElementById("CBStartDate");
if(whichOne == 1)
cb = document.getElementById("CBEndDate");
if(cb.checked){
document.getElementById("CBStartDate").checked = true;
document.getElementById("CBEndDate").checked = true;
}
else{
document.getElementById("CBStartDate").checked = false;
document.getElementById("CBEndDate").checked = false;
}
}
if(whichPair == 1){
var cb = document.getElementById("CBSalary");
if(whichOne == 1)
cb = document.getElementById("CBJobTitle");
if(cb.checked){
document.getElementById("CBSalary").checked = true;
document.getElementById("CBJobTitle").checked = true;
}
else{
document.getElementById("CBSalary").checked = false;
document.getElementById("CBJobTitle").checked = false;
}
}
}
//-- Manually remove duplicates in an array in concatenating
Array.prototype.unique = function() {
var a = this.concat();
for(var i=0; i<a.length; ++i) {
for(var j=i+1; j<a.length; ++j) {
if(a[i] === a[j])
a.splice(j--, 1);
}
}
return a;
};
function query1(){ //-- Search Jobs
//-- Determine which checkboxes are checked
var curSelect = curFrom = curWhere = q = groupby = "";
var selectArray = fromArray = new Array();
if(document.getElementById('CBLastName').checked){ //-- Q2
//-- Display all jobs a seeker has applied for by last name
var lastName = document.getElementById("lastName").value;
if(lastName.length == 0){
alert("You must enter a last name!");
return;
}
selectArray = new Array("user.UFName", "user.ULName", "company.CName", "job.JobID", "job.JListDate", "job.jobtitle");
fromArray = new Array("seeker", "user", "company", "job", "applies");
curWhere = "seeker.UName=user.UName "
+ "AND job.CName=company.CName "
+ "AND applies.UName=seeker.UName "
+ "AND applies.JobID=job.JobID "
+ "AND user.ULName='" + lastName + "'";
}
if(document.getElementById('CBCompanyName').checked){ //-- Q3
//-- Display all jobs for a company
var companyName = document.getElementById("companyName").value;
if(companyName.length == 0){
alert("You must enter a Company Name!");
return;
}
if(curWhere.length > 1)
curWhere += " AND ";
//-- Display all jobs that a seeker has applied for, for a specific company
curWhere += " seeker.UName=user.UName AND job.CName=company.CName AND applies.UName=seeker.UName"
+ " AND applies.JobID=job.JobID AND user.ULName='" + lastName + "'" + " AND job.CName='" + companyName + "'";
groupby = " GROUP BY jobtitle";
selectArray = selectArray.concat(new Array("job.JobID", "job.JListDate", "job.jobtitle", "job.JFillStatus")).unique();
fromArray = fromArray.concat(new Array("job", "company")).unique();
}
if(document.getElementById('CBStartDate').checked){ //-- Q4
var startDate = document.getElementById("startDate").value;
var endDate = document.getElementById("endDate").value;
if(startDate.length == 0){
alert("You must enter a starting date!");
return;
}
if(endDate.length == 0){
alert("You must enter an ending date!");
return;
}
if(curWhere.length > 1){
curWhere += " AND '" + startDate + "' < JListDate "
+ "AND JListDate < '" + endDate + "'";
}
else{
curWhere = "'" + startDate + "' < JListDate "
+ "AND JListDate < '" + endDate + "'";
//+ "for date > '1992-07-01';";
}
selectArray = selectArray.concat(new Array("job.JobID", "job.JListDate", "job.jobtitle", "job.CName")).unique();
fromArray = fromArray.concat(new Array("job")).unique();
}
if(document.getElementById('CBSalary').checked){ //-- Q5
var jobTitle = document.getElementById("jobTitle").value;
var salary = document.getElementById("salary").value;
if(jobTitle.length == 0){
alert("You must enter a job title!");
return;
}
if(salary.length == 0){
alert("You must enter a salary!");
return;
}
if(curWhere.length > 1){ //-- Natural joins
curWhere += " AND jobTitle='" + jobTitle + "' AND jlowrange <= '"
+ salary + "' AND '" + salary + "' <= jhighrange";
}
else{
curWhere = "jobTitle='" + jobTitle + "' AND jlowrange <= '"
+ salary + "' AND '" + salary + "' <= jhighrange";
}
selectArray = selectArray.concat(new Array("job.JobID", "job.JListDate", "job.JLowRange", "job.JHighRange", "job.CName")).unique();
fromArray = fromArray.concat(new Array("job")).unique();
}
if(document.getElementById('CBSkills9').checked){ //-- Q9
var tempList = document.getElementById("skillListJob").getElementsByTagName("li");
var add = 0;
if(curWhere.length > 1)
add = 1;
if(add == 1)
curWhere += " AND ";
selectArray = selectArray.concat(new Array("job.JobID", "job.JListDate", "job.jobtitle")).unique();
fromArray = fromArray.concat(new Array("job", "job_skills", "skill")).unique();
curWhere += "job.JobID=job_skills.JobID AND job_skills.SSkillID=skill.SSkillID ";
for(var i = 0; i < tempList.length; i++){
var option = tempList[i].innerHTML;
if(i==0)
curWhere += " AND ((skill.SSkillName='" + option + "')";
else
curWhere += " OR (skill.SSkillName='" + option + "')";
}
curWhere += ")";
}
if(selectArray.length < 1)
alert("You must select a field!");
else{
q = "SELECT DISTINCT " + selectArray.join() + " FROM " + fromArray.join() + " WHERE " + curWhere + groupby;
console.log(q);
queryForm();
}
}
function query2(){ //-- Search Seekers
var curSelect = curFrom = curWhere = q = groupby = "";
var selectArray = fromArray = new Array();
if(document.getElementById('CBGeneralReport').checked){ //-- Q1
selectArray = new Array("UFName", "ULName", "UEmail", "StateName");
fromArray = new Array("state", "user", "seeker");
curWhere = "seeker.UName=user.UName AND user.StateID = state.StateID";
groupby = " GROUP BY StateName";
}
if(document.getElementById('CBJobID').checked){ //-- Q6
var id = document.getElementById("jobID").value;
if(id.length == 0){
alert("You must enter a Job ID!");
return;
}
selectArray = selectArray.concat(new Array("user.UFName","User.ULName","User.UStreet1","user.UStreet2")).unique();
selectArray = selectArray.concat(new Array("user.UCity", "state.StateName", "user.UEmail")).unique();
fromArray = fromArray.concat(new Array("user","state","seeker","job","applies")).unique();
if(curWhere.length > 1) //-- Natural Join
curWhere += " AND ";
curWhere += "user.UName=seeker.UName "
+ "AND state.stateID=user.StateID "
+ "AND applies.JobID=job.JobID "
+ "AND applies.UName=seeker.UName "
+ "AND job.jobID='" + id + "'";
}
if(document.getElementById('CBCollege').checked){ //-- Q7
var college = document.getElementById("college").value;
if(college.length == 0){
alert("You must enter a university!");
return;
}
selectArray = selectArray.concat(new Array("UniversityName","user.ULName","user.UFName","User.UStreet1")).unique();
selectArray = selectArray.concat(new Array("user.UStreet2","user.UCity","state.StateName","user.UEmail")).unique();;
fromArray = fromArray.concat(new Array("seeker","user","university","education","state","resume")).unique();
if(curWhere.length > 1)
curWhere += " AND ";
curWhere += "education.DegreeTypeID=3 "
+ "AND seeker.UName=user.UName "
+ "AND seeker.UName=resume.UName "
+ "AND resume.ResumeID=education.ResumeID "
+ "AND education.EUniversityID=university.UniversityID "
+ "AND university.UniversityName='" + college + "'"
}
if(document.getElementById('CBSkills10').checked){ //-- Q10
selectArray = selectArray.concat(new Array("user.UFName","User.ULName","User.UStreet1")).unique();
selectArray = selectArray.concat(new Array("user.UStreet2","user.UCity","state.StateName","user.UEmail"));
fromArray = fromArray.concat(new Array("user","state","seeker","job","applies","job_skills","skill","resume","skillset")).unique();
if(curWhere.length > 3)
curWhere += " AND ";
curWhere += "user.UName = seeker.UName "
+ "AND state.stateID = user.StateID "
+ "AND user.UName = resume.UName "
+ "AND skillset.ResumeID = resume.ResumeID "
+ "AND skillset.SSkillID = skill.SSkillID "
+ "AND applies.JobID = job.JobID "
+ "AND applies.UName = seeker.UName "
+ "AND job.JobID = job_skills.JobID "
+ "AND job_skills.SSkillID = skill.SSkillID ";
var tempList = document.getElementById("skillListSeeker").getElementsByTagName("li");
if(tempList.length == 0){
alert("You must enter some skills!");
return;
}
for(var i = 0; i < tempList.length; i++){
var option = tempList[i].text;
curWhere += " AND (skill.SSkillName='" + option + "')";
}
}
if(selectArray.length < 1)
alert("You must select something to search!");
else{
q = "SELECT DISTINCT " + selectArray.join() + " FROM " + fromArray.join() + " WHERE " + curWhere + groupby;
console.log(q);
queryForm();
}
}
function query3(){ //-- View Payment Report
var start = document.getElementById("paymentStartDate").value;
var end = document.getElementById("paymentEndDate").value;
if(start.length == 0){
alert("You must enter a starting date!");
return;
}
if(end.length == 0){
alert("You must enter an end date!");
return;
}
q = "SELECT payment.PaymentID, PAmount, pstatus.Name, PDate, INumber, CCNumber, BVNumber "
+ "FROM payment, pstatus, bankpayment, invoice, creditcard "
+ "WHERE payment.PaymentID = bankpayment.PaymentID "
+ "AND payment.PaymentID = creditcard.PaymentID "
+ "AND payment.PaymentID = invoice.PaymentID "
+ "AND '" + start + "'<= pdate AND pdate <= '" + end + "';";
queryForm();
}
function initSkills(){
var arr = <?php echo json_encode($_SESSION['skills']); ?>;
for(var i = 0; i < arr.length; i++){
listJob.appendChild(makeSkill(arr[i]));
dropJob.add(new Option(arr[i]));
}
if(arr.length == 0){
listJob.appendChild(makeSkill("(none)"));
}
}
function addSkill(which){
if(which == 0){
var newSkill = document.getElementById("newSkillJob").value;
if(newSkill.length < 1){
alert("You must enter a skill before adding it!");
return;
}
listJob.appendChild(makeSkill(newSkill));
dropJob.add(new Option(newSkill));
}
else{
var newSkill = document.getElementById("newSkillSeeker").value;
if(newSkill.length < 1){
alert("You must enter a new skill before adding it!");
return;
}
listSeeker.appendChild(makeSkill(newSkill));
dropSeeker.add(new Option(newSkill));
}
}
function removeSkill(which){
var curDrop;
if(which == 0){
curDrop = dropJob;
var skill = dropJob.options[dropJob.selectedIndex].value;
}
else{
curDrop = dropSeeker;
var skill = dropSeeker.options[dropSeeker.selectedIndex].value;
}
for(var i = 0; i < curDrop.options.length; i++){
if(curDrop.options[i].text == skill){
curDrop.remove(i);
if(which == 0)
$("#q1 ul li:contains('" + skill + "')").remove();
else
$("#q2 ul li:contains('" + skill + "')").remove();
break;
}
}
}
function makeSkill(name){
var entry = document.createElement('li');
entry.appendChild(document.createTextNode(name));
return entry;
}
function saveFile(){
var csvContent = 'data:text/csv;charset=UTF-8,';
var table = document.getElementById("stateTable");
var totalRows = table.rows.length;
var dataString = "";
for(var i = 0; i < table.rows.length; i++){
dataString = "";
var row = table.rows[i];
for(var j = 0, col; col = row.cells[j]; j++){
var value = col.innerHTML;
var val = value.replace(",", "").replace("amp;", "");
dataString += val + ","
}
dataString += "\n"
csvContent += dataString;
}
var encodedURI = encodeURI(csvContent);
var link = document.createElement("a");
link.setAttribute('href', encodedURI);
link.setAttribute('download', 'JobSearchTable.csv');
link.click();
}
</script>
</head>
</html>