Plan your work, and work your plan is good advice.
I've been having a hard time keeping this straight in my head, so I decided to start documenting it in case some volunteers want to help me code all of the screens. Decided to share it here with the foxpro group in case any of you are considering adding a database to your WordPress application as it can be confusing. Because of the way WordPress works, you need to do it in this manner. 1. Create a page, or post within WordPress 2. Create your PHP file 3. Add the /* Template Name: Jobs */ (change the portion after : to the template name you are creating) You cannot create your template within wordpress, so you need to create it using your editor and upload it using a ftp program to the appropriate template folder. Within the PHP file you specify the template name (example follows at the bottom) Once you have done this, it is easy to edit your code in WordPress, but it is also easier to set up a linux or windows box with xaamp on it and do your development and testing there before uploading your changes. This way you can use your favorite text editor (I'm not there yet, but heading that way which is why I set up the Ubuntu box) Which now gives me the following plan: ====== Where are the jobs? Is a page in Wordpress that uses the template "jobs" Job_page.php is the code for the template "jobs" This starts the LCA display by selecting the occupational codes from the occupational code file This calls the following page if you want to drill down by OCC_CODE <a href= http://keepamericaatwork.com/?page_id=187 <http://keepamericaatwork.com/?page_id=187&occ_code=> &occ_code=" . $r->occ_code . "> $r->occ_code </a> This calls "View a Occupational Code" page in wordpress and it uses the template "occ_code" The code for this is stored in job_occ_code.php This calls the following page if you want to drill down by H-1B visa quantity <a href= http://keepamericaatwork.com/?page_id=210 <http://keepamericaatwork.com/?page_id=210&occ_code=> &occ_code=" . $r->occ_code . "> $user_count </a> This calls "Visas by Company" page in wordpress and it uses the template "Jobs Company Drilldown" The code for this is stored in job_company_drilldown.php ========= As you can see, it can get confusing the more links you create within the code and I was losing track of where I was which is why I decided to share it with you guys and gals in an attempt to give something back as I really believe WordPress will give you the ability to develop database applications with a ready made format and all of the housekeeping stuff already developed for you such as logins, etc.. Virgil Bierschwale www.VirgilBierschwale.com Keep America At Work www.KeepAmericaAtWork.com Where Are The Jobs? http://keepamericaatwork.com/?page_id=53 <?php /* Template Name: Jobs */ get_header(); ?> <div id="primary" <?php mb_primary_attr(); ?> role="main"> <?php while ( have_posts() ) : the_post(); get_template_part( 'content', 'page' ); /* comments_template( '', true );*/ global $wpdb; $results = $wpdb->get_results("SELECT occ_code, occ_title,tot_emp, a_mean FROM `wp_jobs` WHERE substr(occ_code,4,4) = '0000' and substr(occ_code,1,2)<> '00'"); if(!empty($results)) { ?> <table> <tr> <th> OCC_CODE </th> <th> Category </th> <th> Average Salary for this job </th> <th> Quantity of jobs in America </th> <th> less jobs given to LCA Programs (H-1B, H-1B1, E-3) visa holders </th> <th> less jobs given to PERM Programs visa holders </th> <th> less jobs given to H-2B Program visa holders </th> <th> Jobs left for Americans in America </th> <th> Percentage of Americans displaced by Visa Holders </th> </tr> <?php foreach($results as $r) { ?> <td> <?php echo "<a href= http://keepamericaatwork.com/?page_id=187&occ_code="; . $r->occ_code . "> $r->occ_code </a>"; ?> </td> <td> <?php echo $r->occ_title; ?> </td> <td style="text-align:right"> <?php echo $r->a_mean; ?> </td> <td style="text-align:right"> <?php $input = $r->tot_emp; $vtot_emp = floatval(preg_replace("/[^-0-9\.]/","",$input)); echo $vtot_emp; ?> </td> <td style="text-align:right"> <?php $get_code = $r->occ_code; $get_code = substr($get_code,0,2); $user_count = $wpdb->get_var("select count(*) from wp_lca_2013 where left(m,2) = " . "'" . $get_code . "'"); /* echo $user_count; */ echo "<a href= http://keepamericaatwork.com/?page_id=210&occ_code="; . $r->occ_code . "> $user_count </a>"; ?> </td> <td style="text-align:right"> <?php $get_code = $r->occ_code; $get_code = substr($get_code,0,2); $perm_count = $wpdb->get_var("select count(*) from perm_2013 where left(pw_soccode,2) = " . "'" . $get_code . "'"); echo $perm_count; ?> </td> <td style="text-align:right"> <?php $get_code = $r->occ_code; $get_code = substr($get_code,0,2); $h2b_count = $wpdb->get_var("select count(*) from h2b_2013 where left(soc_code,2) = " . "'" . $get_code . "'"); echo $h2b_count; ?> </td> <td style="text-align:right"> <?php $vjb_totemp = $r->tot_emp; $input = $vjb_totemp; $vjbjobs = floatval(preg_replace("/[^-0-9\.]/","",$input)); $vjb_usrcnt = intval($user_count); $input = $vjb_usrcnt; $vjbvisas = floatval(preg_replace("/[^-0-9\.]/","",$input)); echo intval($vjbjobs) - intval($vjbvisas); ?> </td> <td style="text-align:right"> <?php $val1 = $user_count + $perm_count + h2b_count; $val2 = $vtot_emp; $res = ( $val1 / $val2) * 100; // 2 digits after the decimal point $res = round($res, 2); // 66.67 echo $res; ?> </td> </tr> <tr> <?php } } else { echo "<p>Boo, we couldn't find anything that is in all these groups. Try removing a category!</p>"; } ?> </table> <?php endwhile; // end of the loop. ?> </br> The data used to prepare this list comes from: </br> 1. Occupational Data for 2013 - http://www.bls.gov/oes/current/oes_nat.htm </br> 2. The Perm, H-2B, and LCA Programs (H-1B, H-1B1, E3) spreadsheets for the year 2013 that can be found at http://www.foreignlaborcert.doleta.gov/performancedata.cfm </br> 3. The H-2A spreadsheet shown on the link above had no meaningful way to relate the data to the occupational data so I did not include it. </br> Updated Notes: </br> On the link above you will find a list of spreadsheets that show appropriate data for each of these visa types. </br> I have downloaded each of them, and converted them in a way that allowed me to upload them to the MySQL tables at Keep America At Work so that you can view them in any way that you wish too. (I do NOT recommend that you download them as they are larger than 100 MB in many cases) </br> This gave me the following visa totals: </br> 268,246 records for 2009A</br> 87,989 records for 2009B</br> 341,969 records for 2010</br> 358,325 records for 2011</br> 414,451 records for 2012</br> 443,037 records for 2013</br> 417,991 records for 2014</br> </br> This will give us 2,062,303 records. </br> Now we need to delete all records where the work end date is older than the current date. </br> This will give us 1,265,350 records. </br> At this point we can copy these records into the system that I developed to display all occupations in America. </br> Which will give us these results: </div><!-- #primary.c8 --> <?php get_footer(); ?> --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
