View job applicants

In this section of the tutorial you will build two pages: one that shows a list of all jobs posted by the currently logged in company, and one to provide details on a specific job offer, including the applicant list. These pages are located inside the company folder, and are named view_jobs and view_applicants.

The first page to be created is the one listing the job offers (expired or not) of the current company. If you've gone through the tutorial right, you might remember having already done such a page, but for the user section. During the creation of the companies' page for viewing job offers, references will be made to the user's job listing page.

To create the view_jobs page, follow the next steps:

Open the company/view_jobs page in Dreamweaver.
To retrieve the current's company list of job offers posted to the site, you must create a filtered recordset. Due to the fact that the company ID (not the user ID, but the one from the company table - generated in the company details page) is not available to the user, you will have to create an advanced recordset, implying a JOIN operation aside the filtering condition. The easiest way to achieve this is to use MX Query Builder. MX Query Builder has already been used during this tutorial for the Apply to job page.
Create a new recordset from the Bindings tab of the Application panel. Select the database connection to use, and click on the QuB3 button. This will open the Dreamweaver MX Query Builder user interface that allows creating a new query, or editing and deleting existing ones. Click the New query button, and enter a name (different from the existing saved queries) - e.g. rsJobs (it can be the same as the recordset name).
In the main MX Query Builder window, select the job_job and company_com tables.
If there is no relation defined between the two tables (if you have defined it in a past work session with MX Query Builder, it is saved), simply drag and drop the idcom_job column from the job_job table over the id_com column of the company_com table). A line will appear between the two tables, symbolizing the relation.
Select only the following columns to retrieve:id_job, idusr_com, the job title and type.
You will need to restrict the recordset to retrieve only jobs posted by the currently logged in company. To do so, add a condition for the foreign key to the user's table (idusr_com). To do so, click on the ... button next to the column name in the Columns area, and fill in the fields for the dialog box that opens as shown below:
Save the query and close the window by clicking on Query > Close. Back in Dreamweaver click the Refresh button next to the Query name drop-down menu, and after the query is displayed, click the OK button to add it to the page.
To display data retrieved by the recordset, use a dynamic table. You can apply the Dynamic Table command from the Application tab of the Insert bar (top of the screen, below the main menu). Configure it to display all records from the newly added recordset.

New you have to edit the dynamic table, in order to make it display only the data you need. Follow the instruction below to adapt the table to your particular needs:

First, remove the first two columns from the dynamic table. These columns display the user ID and the company ID, which are not used.
Add a new column at the end of the table, where a link to the page displaying the applicant details will be. To add a column, right-click in the last column, and from the pop-up menu select Table > Insert rows or columns.
Select to add a new column, after the current one. In the second row's last cell, enter the link's text (e.g. See more details). To turn it into a link to the job detail and applicant list page, select the text and right-click on it. From the pop-up menu select the Make link option.
Make the link point to the view_applicants page. In order for the detail page to display data relevant only for the job whose detail link has been clicked, you must also pass it the job ID, as an URL parameter. In the Link configuration window, click the Parameters button to define URL parameters for the current link. Add a new one, with the label id_job and the value of the job ID retrieved by the rsJobs recordset.
Click the OK button to close the dialog box and create the link.
The last thing to do on the view_jobs page is to replace the default table column titles from the dynamic table with more appropriate text (e.g title_job with Job title, type_job with Job type ). Then save the page and upload it to the server. If you log in with a company account, and you have posted job offers from the respective account, the list will be displayed properly.

The second page you must create in this tutorial section, is used to display a list of applicants for the selected job in the job listing page created before. The job to display details and applicants for is determined by its ID, which is passed as an URL parameter.

To build the page, follow the instructions below:

Open the view_applicants page in Dreamweaver.
This page will have to display two distinct things, on distinct areas:

First, some more details regarding the job - e.g. the details, offered salary and the title. This will have to be retrieved by a filtered recordset, which must contain only one record.
Second, the list of users that applied for the position. To obtain the list, a JOIN operation must be performed on the application submission table (job_user_jbu) and the user's resume table ( not the user table, as the company is interested in what the user can do, and not the user's account). This is a more complex recordset, that will be handled with the MX Query Builder

First create a filtered recordset that will retrieve job details for the currently selected job. Add a new recordset from the Bindings tab, and configure it to use the database connection created at the beginning of this tutorial, the job_job table, and to have as filter condition the job ID column equal to the id_job parameter passed by the view_jobs page:
Once the recordset is completely configured, click the OK button to close the dialog box.
Next you will have to create the recordset that retrieves applicants with MX Query Builder. To start MX Query Builder, add a regular recordset from the Bindings tab. In the dialog box, select the database connection, and click the QuB3 button to switch to the Dreamweaver MX Query Builder user interface. In this interface, you can add, edit or delete queries. For now, let's add a new query. Click the New Query button, and enter its name in the dialog box that pops up.
In the MX Query Builder main interface, select the tables to use in the query: job_user_jbu and resume_rsm by clicking their names in the Tables area.
Now define the relation between the tables: drag and drop the idrsm_jbu column from the job_user_jbu table over the id_rsm column of the resume_rsm table. An arrow will be displayed to mark the relation, and the SQL code will contain the JOIN operation.
For the columns to retrieve, from the resume_rsm table select idusr_rsm, name_usr_rsm and phone_usr_rsm columns, and only the idjob_jbu column from the job_user_jbu table. This last column will be used to filter the recordset, in order to retrieve only the relevant data.
To define the filtering condition, click on the ... button next to the idjob_jbu row in the columns area. In the dialog box, define the condition: the column must equal the id_job URL parameter.
After adding the condition, you can save the query, and close MX Query Builder, by clicking on the Query menu > Close. Back in Dreamweaver click the Refresh button before closing the recordset user interface and adding it to the page.
Display the job details you retrieved through the first recordset (rsJob) into the page. A dynamic table would be too much, as only the job title and details are needed. Therefore, type in the labels for the dynamic texts, and drag & drop the dynamic fields from the Bindings panel into the page. You can add even more details: simply add labels, and drag and drop them onto the page.
Add a dynamic table that lists details regarding the applicants. To add a dynamic table into the page, select its button from the Application tab of the Insert panel. Configure it to display all records from the rsApplicants recordset:
In the dynamic table keep only the name_usr_rsm and phone_usr_rsm columns, and delete the rest. Also change the title into Name and Phone, and select the Header option for the entire first row:
At this point you can see what people applied for which jobs. All that lacks is a way to call them up for an interview, but this is what you'll solve next. Add a new column at the end of the dynamic table. In the cell next to the dynamic text, enter the "Invite to interview" text.
Make this a link to the applicant_detail page, and also pass it an URL parameter containing the user id. To do so, in the Browse for page dialog box, select the applicant_detail page, and then click on the Parameters button. Enter the id_usr name, and select for the value the idusr_rsm field from the rsApplicants recordset.
The second URL parameter that needs to be passed to the page is the job ID. While in the Parameters dialog box, add another parameter, named id_job, with the id_job field from the rsJob recordset as value.
Note: For ASP users, enter the following code for the parameter Value fields: id_user:
<%=(rsApplicants.Fields.Item("idusr_rsm").Value)%> id_job: <%=(rsJob.Fields.Item("id_job").Value)%>
If you save the page, upload it to the server, and log in with a company account, when you access the view applicants area for a specific job ad, you'll see something like the following:

If no applicants are available, the table header or the Invite to Interview link do not have to show up. To hide them, you should use the Show If Recordset is Not Empty server behavior , as for the company job listing page's last step.

In the last section of the company page tutorial you will implement a view of all details: the job details, and the applicants detail, as well as adding the actual sending of the e-mail containing the invitation to an interview. The page you will create next is the one used in the Invite to interview link.