Re: [Linux-aus] Salary Survey

[Morgan Tocker <tocker@gmail.com>]

On 6/7/05, Ian Wienand <ianw@ieee.org> wrote:
> On Mon, Jun 06, 2005 at 09:11:09PM +1000, Pia Waugh wrote:
> > I think it'd be interesting, and also having a polling functionality
> > would be cool at any rate. Want to set it up? ;)
> 
> I couldn't commit time to develop a custom solution; but if someone
> knows of a good open source polling/questionnaire builder that has
> some sort of minimal level of anti-spoofing and security I'd be
> willing to set it up and admin it.  Alternatively it could be part of
> MemberDB?
> 
> -i


Perhaps a quick hack, but if this meets the complexity of what you
require, I'd be happy to implement it.  There'd have to be a bit of
thought into the categories, and who gets to vote (and when).  I think
a vote should last 6 months.

Median is also a better AVG than mean in this case.


<?php

mysql_connect('localhost','ted','somethinghere'); // no, not my real password.
mysql_select_db('salary');

function get_results_box($cid) {

	$cities = array("Brisbane", "Sydney", "Melbourne",
		"Perth", "Hobart", "Canberra", "Adelaide", "Darwin");
	
	// first get the number of positions in this cat.
	$result = mysql_query("SELECT * FROM positions where cid = '$cid'");
	$return .= "<table border=1><tr><td>&nbsp;</td>";
	$num = mysql_numrows($result);

	while($r = mysql_fetch_array($result)) {
		$return .="<td>".$r['label']."</td>";
	}

	$return .= "</tr>";
	
	foreach($cities as $city) {
		
		$return .= "<tr><td>$city</td>";

		// @todo: show the avg for that city for that position
		// from the survey results.  You get the idea.
		
		for($i=0;$i<$num;$i++)
			$return .= "<td>???</td>";
		$return .= "</tr>";
	}
	
	$return .= "</table>";
	
	return $return;
}

?>

<h2>Salary Survey</h2>

<?php

$result = mysql_query("SELECT * from categories order by sid");

while($r = mysql_fetch_array($result, MYSQL_ASSOC)) {
	
	print "<h3>".$r['label']."</h3>\n";
	
	print get_results_box($r['id']);
	
}

?>


-- phpMyAdmin SQL Dump
-- version 2.6.0-pl2
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Jun 07, 2005 at 11:57 AM
-- Server version: 4.1.12
-- PHP Version: 5.0.4
-- 
-- Database: `salary`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `categories`
-- 

CREATE TABLE `categories` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `sid` tinyint(3) unsigned NOT NULL default '0',
  `class` enum('INFRASTRUCTURE','DEVELOPMENT','BUSINESS','EXECUTIVE')
NOT NULL default 'INFRASTRUCTURE',
  `label` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='parent categories for
job positions' AUTO_INCREMENT=7 ;

-- 
-- Dumping data for table `categories`
-- 

INSERT INTO `categories` VALUES (1, 1, 'INFRASTRUCTURE', 'LAN Support');
INSERT INTO `categories` VALUES (2, 2, 'INFRASTRUCTURE', 'WAN Support');
INSERT INTO `categories` VALUES (3, 3, 'INFRASTRUCTURE', 'Senior');
INSERT INTO `categories` VALUES (4, 4, 'INFRASTRUCTURE', 'Technical
Specialtists');
INSERT INTO `categories` VALUES (5, 5, 'INFRASTRUCTURE', 'Technical
Specialtists');
INSERT INTO `categories` VALUES (6, 6, 'INFRASTRUCTURE', 'Management Roles');

-- --------------------------------------------------------

-- 
-- Table structure for table `positions`
-- 

CREATE TABLE `positions` (
  `id` int(100) unsigned NOT NULL auto_increment,
  `sid` int(3) unsigned NOT NULL default '0',
  `cid` int(100) unsigned NOT NULL default '0',
  `label` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `cid` (`cid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='positions nested
within a category' AUTO_INCREMENT=8 ;

-- 
-- Dumping data for table `positions`
-- 

INSERT INTO `positions` VALUES (1, 1, 1, 'Operators');
INSERT INTO `positions` VALUES (2, 2, 1, 'Helpdesk Support (Level 1)');
INSERT INTO `positions` VALUES (3, 3, 1, 'Desktop Support (Level 2)');
INSERT INTO `positions` VALUES (4, 4, 1, 'Network Engineer (Level 3)');
INSERT INTO `positions` VALUES (5, 1, 2, 'Cisco Engineer');
INSERT INTO `positions` VALUES (6, 2, 2, 'Voice/Comms Engineer');
INSERT INTO `positions` VALUES (7, 3, 2, 'Network Designer');

-- --------------------------------------------------------

-- 
-- Table structure for table `survey_results`
-- 

CREATE TABLE `survey_results` (
  `id` int(10) NOT NULL auto_increment,
  `sdate` date NOT NULL default '0000-00-00',
  `pid` int(10) unsigned NOT NULL default '0',
  `salary` decimal(10,2) NOT NULL default '0.00',
  `city` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='The results that the
respondents have given.' AUTO_INCREMENT=1 ;

-- 
-- Dumping data for table `survey_results`
-- 


-- --------------------------------------------------------

-- 
-- Table structure for table `users`
-- 

CREATE TABLE `users` (
  `id` int(100) unsigned NOT NULL auto_increment,
  `first_name` varchar(50) NOT NULL default '',
  `last_name` varchar(50) NOT NULL default '',
  `email` varchar(255) NOT NULL default '',
  `last_surveyed` datetime default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='record of people
allowed to submit a salary survey.' AUTO_INCREMENT=1 ;

-- 
-- Dumping data for table `users`
--