From 3bb1f0c45d308b85918310ae5cab28198bf1f68e Mon Sep 17 00:00:00 2001 From: Steve Sutton Date: Fri, 20 Sep 2013 13:00:11 -0400 Subject: [PATCH] Adding scripts for Member File Import These are taken from just using them in the Cadillac website. I did add non_memebr flag to the members class. --- .gitignore | 2 + Category.php | 179 +++++++++++++ City.php | 135 ++++++++++ FileForm.php | 149 +++++++++++ Import.php | 181 +++++++++++++ ImportForm.php | 301 +++++++++++++++++++++ Member.php | 672 +++++++++++++++++++++++++++++++++++++++++++++++ State.php | 106 ++++++++ index.php | 48 ++++ updateLatLon.php | 69 +++++ 10 files changed, 1842 insertions(+) create mode 100644 .gitignore create mode 100644 Category.php create mode 100644 City.php create mode 100644 FileForm.php create mode 100644 Import.php create mode 100644 ImportForm.php create mode 100644 Member.php create mode 100644 State.php create mode 100644 index.php create mode 100644 updateLatLon.php diff --git a/.gitignore b/.gitignore new file mode 100644 index 0000000..807e6df --- /dev/null +++ b/.gitignore @@ -0,0 +1,2 @@ +upload +fileHeaders.txt diff --git a/Category.php b/Category.php new file mode 100644 index 0000000..0336b12 --- /dev/null +++ b/Category.php @@ -0,0 +1,179 @@ + + * @license http://www.gaslightmedia.com Gaslightmedia + * @link <> + */ + +/** + * Toolkit_Members_MemberImport_Category + * + * Category class for members + * + * @category Toolkit + * @package Toolkit + * @author Steve Sutton + * @license http://www.gaslightmedia.com Gaslightmedia + * @link <> + */ +class Toolkit_Members_MemberImport_Category +{ + /** + * Description of $category_id + * @var integer + * @access protected + */ + protected $category_id; + + /** + * Description of $name + * @var string + * @access protected + */ + protected $name; + + /** + * Description of $parent_id + * @var integer + * @access protected + */ + protected $parent_id = 0; + + /** + * Helps to create functions for any class variable if the variable does + * exists + * + * @param String $name Name of variable + * @param mixed $args Argument for the varable (if set) + * + * @return Toolkit_Table + */ + public function __call($name, $args) + { + if (preg_match('/^(get|set)(\w+)/', strtolower($name), $match) + && $attribute = $this->validateAttribute($match[2]) + ) { + if ('get' == $match[1]) { + return $this->$attribute; + } else { + $this->$attribute = $args[0]; + } + } else { + throw new Exception( + 'Call to undefined method Member::' . $name + ); + } + } + /** + * insert the object + * + * @param PDO $dbh Database Connection + * + * @return Toolkit_Table + */ + private function _insert(PDO $dbh) + { + $classData = get_object_vars($this); + unset($classData['category_id']); + //echo '
'.print_r($classData, true).'
'; + //exit; + $sql = Toolkit_Common::createSQLInsert( + 'category', + array_keys($classData) + ); + $sql .= " RETURNING category_id"; + $stmt = Toolkit_Common::prepareQuery( + $dbh, + 'category', + $sql, + $classData + ); + $stmt->execute(); + $this->setCategoryId($stmt->fetchColumn()); + } + /** + * given a category name return id create category if needed + * + * @param PDO $dbh Database connection + * @param String $name Category Name + * + * @return Object + */ + function fetchByName(PDO $dbh, $name) + { + $sql = " + SELECT " . implode(',', $this->getClassVars())." + FROM category + WHERE name = :name"; + $stmt = $dbh->prepare($sql); + $stmt->bindParam(":name", $name, PDO::PARAM_STR); + $stmt->execute(); + $row = $stmt->fetch(PDO::FETCH_ASSOC); + if ($row) { + $category = new Toolkit_Members_MemberImport_Category(); + $category->setCategoryId($row['category_id']); + $category->setName($row['name']); + $category->setParent_id($row['parent_id']); + return $category; + } else { + return false; + } + } + /** + * get all clas properties as an array (just names) + * + * @return Array + */ + public function getClassVars() + { + return array_keys(get_class_vars(get_class($this))); + } + /** + * set category id + * + * @param Int $id Category id + * + * @return void + */ + public function setCategoryId($id) + { + if (!$this->category_id) { + $this->category_id = $id; + } + } + /** + * Checks the id of the object if it is set then calls update othervise + * calls insert function + * + * @param PDO $dbh Database connection + * + * @return viod + */ + public function save(PDO $dbh) + { + if ($this->category_id) { + $this->_update($dbh); + } else { + $this->_insert($dbh); + } + } + /** + * Validates the property + * + * @param String $name property name + * + * @return String + */ + protected function validateAttribute($name) + { + if (property_exists(get_class($this), $name)) { + return strtolower($name); + } + } +} diff --git a/City.php b/City.php new file mode 100644 index 0000000..d48d8b2 --- /dev/null +++ b/City.php @@ -0,0 +1,135 @@ + + * @license http://www.gaslightmedia.com Gaslightmedia + * @link <> + */ + +/** + * Toolkit_Members_MemberImport_City + * + * member city table class + * + * @category Toolkit + * @package Members + * @author Steve Sutton + * @license http://www.gaslightmedia.com Gaslightmedia + * @link <> + */ +class Toolkit_Members_MemberImport_City +{ + /** + * Description of $city_id + * @var integer + * @access protected + */ + protected $city_id; + + /** + * Description of $city_name + * @var string + * @access protected + */ + protected $city_name; + + /** + * Description of $state_id + * @var integer + * @access protected + */ + protected $state_id; + + /** + * Helps to create functions for any class variable if the variable does + * exists + * + * @param String $name Name of variable + * @param mixed $args Argument for the varable (if set) + * + * @return Toolkit_Table + */ + public function __call($name, $args) + { + if (preg_match('/^(get|set)(\w+)/', strtolower($name), $match) + && $attribute = $this->validateAttribute($match[2]) + ) { + if ('get' == $match[1]) { + return $this->$attribute; + } else { + $this->$attribute = $args[0]; + } + } else { + throw new Exception( + 'Call to undefined method Member::' . $name + ); + } + } + /** + * given a city name search for and get id create city if needed + * + * @param PDO $dbh Database connection + * @param String $name City Name + * + * @return Object + */ + public function fetchByName(PDO $dbh, $name) + { + $sql = " + SELECT city_id,city_name,state_id + FROM city + WHERE lower(city_name) = :city_name"; + $stmt = $dbh->prepare($sql); + $stmt->setFetchMode( + PDO::FETCH_CLASS, + 'Toolkit_Members_MemberImport_City' + ); + $stmt->bindParam(":city_name", strtolower($name)); + $stmt->execute(); + return $stmt->fetch(PDO::FETCH_CLASS); + } + /** + * Checks the id of the object if it is set then calls update othervise + * calls insert function + * + * @param PDO $dbh Database connection + * + * @return viod + */ + public function save(PDO $dbh) + { + $classData = get_object_vars($this); + unset($classData['city_id']); + $sql = Toolkit_Common::createSQLInsert( + 'city', + array_keys($classData) + ); + $sql .= " RETURNING city_id"; + $stmt = Toolkit_Common::prepareQuery( + $dbh, + 'city', + $sql, + $classData + ); + $stmt->execute(); + $this->setCity_id($stmt->fetchColumn()); + } + /** + * Validates the property + * + * @param String $name property name + * + * @return String + */ + protected function validateAttribute($name) + { + if (property_exists(get_class($this), $name)) { + return strtolower($name); + } + } +} diff --git a/FileForm.php b/FileForm.php new file mode 100644 index 0000000..73fcc0b --- /dev/null +++ b/FileForm.php @@ -0,0 +1,149 @@ + + * @license Gaslight Media + * @link <> + */ +/** + * Toolkit_Members_MemberImport_FileForm + * + * Build the form for uploading the file + * + * @category Toolkit_Members + * @package Members + * @author Steve Sutton + * @license Gaslight Media + * @link <> + */ +class Toolkit_Members_MemberImport_FileForm + extends Toolkit_FormBuilder implements Toolkit_Form +{ + /** + * Description of $uploadDir + * @var string + * @access protected + */ + protected $uploadDir = './upload/'; + + /** + * Description of $successMessage + * @var string + * @access protected + */ + protected $successMsg = '
file uploaded now processing
'; + + /** + * Form element definitions + * + * @return void + * @access public + */ + public function configureElements() + { + $e = array(); + + // All Elements are created here. + // This includes group element definitions. + $e[] = array( + 'type' => 'file', + 'req' => true, + 'name' => 'file_upload', + 'display' => 'File Upload' + ); + $e[] = array( + 'type' => 'submit', + 'req' => false, + 'name' => 'submit_rmv', + 'display' => 'Submit Form' + ); + + $this->setupElements($e); + } + + /** + * Helper function, configures the entire form + * + * @return void + * @access public + */ + public function configureForm() + { + $this->configureElements(); + $this->configureRules(); + } + + /** + * Form rule definitions + * + * Adds validation rules for the given fields + * + * @return void + * @access public + */ + public function configureRules() + { + $r = array(); + $this->setupRules($r); + } + + /** + * Process the form + * + * @param Array $values Array of values from the form (filtered) + * + * @return Boolean + */ + function processForm($values) + { + $uploadedFile = move_uploaded_file( + $values['file_upload']['tmp_name'], + $this->uploadDir . 'members.csv' + ); + if ($uploadedFile) { + $import = new Toolkit_Members_MemberImport_Import( + Toolkit_Database::getInstance() + ); + $data = $import->readFile($this->uploadDir . 'members.csv'); + $import->createTmpTable(); + $import->importFile(); + return true; + } else { + echo 'failed'; + } + } + + /** + * Handles how to display the current step the user is at in the form + * + * destroying and resetting the captcha value dis-allows someone from + * re-sending a form on a previous captcha. + * + * @return string form HTML state + * @access public + */ + public function toHtml() + { + $this->fileImported = false; + if ($this->validate()) { + $this->cleanForm(); + + if ($this->process(array(&$this, 'processForm'), $this->mergeFiles)) { + $this->freeze(); + $output = $this->successMsg; + $this->fileImported = true; + } + } elseif ($this->isSubmitted()) { + $output = $this->errorMsg; + $output .= parent::toHTML(); + } else { + $output = parent::toHTML(); + } + return $output; + } +} diff --git a/Import.php b/Import.php new file mode 100644 index 0000000..b9e889e --- /dev/null +++ b/Import.php @@ -0,0 +1,181 @@ + + * @license Gaslight Media + * @link <> + */ +/** + * Toolkit_Members_MemberImport_Import + * + * handles the import of the members from a temp table to the actual members + * table + * + * @category Toolkit_Members + * @package Members + * @author Steve Sutton + * @license Gaslight Media + * @link <> + */ +class Toolkit_Members_MemberImport_Import +{ + + /** + * Description for $fieldNames + * @var array + * @access public + */ + public $fieldNames = array(); + + /** + * Description for $_dropEnd + * @var boolean + * @access private + */ + private $_dropEnd = false; + + /** + * Description for $_headCount + * @var integer + * @access private + */ + private $_headCount = 0; + + /** + * Description for $tmpTableName + * @var string + * @access protected + */ + protected $tmpTableName = 'tmp_members'; + + /** + * Description for $headerFile + * @var string + * @access protected + */ + protected $headerFile = 'fileHeaders.txt'; + + /** + * Description for $dbh + * @var PDO + * @access protected + */ + protected $dbh; + + /** + * Description for $data + * @var array + * @access protected + */ + protected $data = array(); + + /** + * constructor of class Sets the pdo to protected $dbh variable + * + * @param PDO $pdo Database connection + * + * @return void + * @access public + */ + public function __construct(PDO $pdo) + { + $this->dbh = $pdo; + } + /** + * readFile + * + * Read in the file uploaded and return an array of values from each row + * + * @param String $file Name of the file uploaded + * + * @throws Exception If file is not found + * @return Array + * @access public + */ + public function readFile($file) + { + if (!is_file($file)) { + throw new Exception("File $file not found!"); + } + if (($handle = fopen($file, 'r')) !== false) { + while (($line = fgetcsv($handle, 1000, ",")) !== false) { + $this->data[] = $line; + } + } + return $this->data; + } + /** + * Create a tmp_member table + * + * @return void + * @access public + */ + public function createTmpTable() + { + $this->dbh->query("DROP TABLE IF EXISTS {$this->tmpTableName}"); + // get the first row of the csv file + $headers = array_values($this->data[0]); + $headers = array_filter($headers); + $this->fieldNames = array_map(array($this, "_cleanFieldName"), $headers); + $this->_headCount = count($headers); + file_put_contents($this->headerFile, serialize($this->fieldNames)); + $sql = " + CREATE TABLE {$this->tmpTableName}( + ".implode(" TEXT,", $this->fieldNames)." TEXT + )"; + $this->dbh->query($sql); + } + /** + * Clean the field name for creation of a tmp_member table with the + * first header row as the field names + * + * @param String $name Name of the field + * + * @return String + * @access private + */ + private function _cleanFieldName($name) + { + $name = trim(str_replace("-", "_", $name)); + return strtolower(str_replace(" ", "_", $name)); + } + /** + * importFile + * + * Takes the file uploaded and imports the rows into a tmp_member table + * + * @return void + * @access public + */ + public function importFile() + { + $sql = Toolkit_Common::createSQLInsert( + $this->tmpTableName, + $this->fieldNames + ); + try { + foreach ($this->data as $key => $row) { + // skip first row + if ($key == 0) { + continue; + } + // only get part of the row Length of headers + $row = array_slice($row, 0, $this->_headCount); + $values = array_combine($this->fieldNames, $row); + Toolkit_Common::processQuery( + $this->dbh, + $this->tmpTableName, + $sql, + $values + ); + } + } catch(PDOException $e) { + Toolkit_Common::handleError($e); + } + } +} diff --git a/ImportForm.php b/ImportForm.php new file mode 100644 index 0000000..fd99ce4 --- /dev/null +++ b/ImportForm.php @@ -0,0 +1,301 @@ + + * @license Gaslight Media + * @link <> + */ +/** + * Toolkit_Members_MemberImport_ImportForm + * + * Build the form for defining whihc fields can be imported from the file + * and to which field they get imported to + * + * @category Toolkit + * @package Members_MemberImport + * @author Steve Sutton + * @license Gaslight Media + * @link <> + */ +class Toolkit_Members_MemberImport_ImportForm + extends Toolkit_FormBuilder + implements Toolkit_Form +{ + + /** + * Description for $tmpTableName + * @var string + * @access protected + */ + protected $tmpTableName = 'tmp_members'; + + /** + * Description for $headerFile + * @var string + * @access protected + */ + protected $headerFile = 'fileHeaders.txt'; + + /** + * Description for $successMsg + * @var string + * @access protected + */ + protected $successMsg = '
Inserting new members
'; + + /** + * _cleanFieldName + * + * Cleans the fieldname takes out the spaces and puts in underscore + * + * @param String $name Field name to be cleaned + * + * @return String + */ + private function _cleanFieldName($name) + { + return ucwords(str_replace("_", " ", $name)); + } + + private function _getCategories(PDO $dbh) + { + $categories = Toolkit_Common::getHierarchicalTreeStructure( + $dbh, + 'category', + 'category_id', + 'parent_id', + 'name' + ); + // Get only the active categories from + // the nav structure for our select list. + $sql = " + SELECT category_id, name + FROM category + WHERE category_id = :id"; + + $stmt = $dbh->prepare($sql); + + foreach ($categories as $k => $v) { + unset($row, $category); + $stmt->bindParam(':id', $k, PDO::PARAM_INT); + $stmt->execute(); + + // If we actually retrieved a row, add it to the select list + // after we clean it up. + if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { + $pages[$row['name']] = array( + 'level' => $v - 1, + 'name' => $row['name'] + ); + } + } + + return $pages; + } + + /** + * Form element definitions + * + * @return void + * @access public + */ + public function configureElements() + { + $excludedFields = array( + 'member_id', + 'member_categories', + 'active', + 'non_member', + 'city_id', + 'state_id', + 'mailing_city_id', + 'mailing_state_id', + 'lat', + 'lon' + ); + $e = array(); + $e[] = array( + 'type' => 'selectglm', + 'req' => false, + 'name' => 'category', + 'display' => 'Assign to Category', + 'opts' => $this->_getCategories($this->dbh) + ); + $member = new Toolkit_Members_MemberImport_Member(); + $flds = $member->getClassVars(); + $fieldOption = array( + '' => 'Ignore', + ); + foreach ($flds as $fieldName) { + if (!in_array($fieldName, $excludedFields)) { + $fieldOption[$fieldName] = $this->_cleanFieldName($fieldName); + } + } + + // All Elements are created here. This includes group element definitions. + $headers = unserialize(file_get_contents($this->headerFile)); + foreach ($headers as $field) { + $title = ucwords(str_replace("_", " ", $field)); + $e[] = array( + 'type' => 'select', + 'req' => false, + 'name' => $field, + 'display' => $title, + 'opts' => $fieldOption + ); + } + $e[] = array( + 'type' => 'submit', + 'req' => false, + 'name' => 'submit_rmv', + 'display' => 'Submit Form' + ); + + $this->setupElements($e); + } + + /** + * Helper function, configures the entire form + * + * @return void + * @access public + */ + public function configureForm() + { + $this->configureElements(); + $this->configureRules(); + } + + /** + * Form rule definitions + * + * Adds validation rules for the given fields + * + * @return void + * @access public + */ + public function configureRules() + { + $r = array(); + $this->setupRules($r); + } + + /** + * Process the form and import members according to their placement + * + * @param Array $values Array of values from the form (filtered) + * + * @return Boolean + */ + function processForm($values) + { + $member_categories = array(); + if ($values['category']) { + $member_categories[] = $values['category']; + } + unset($values['submit_rmv'], $values['category']); + $memberCategoryFields = array(); + foreach ($values as $fromField => $toField) { + if ($toField && $toField != 'member_categories') { + $fromFieldArray[] = $fromField; + } else if ($toField == 'member_categories') { + $fromFieldArray[] = $fromField; + $memberCategoryFields[] = $fromField; + } + } + $ignore = array( + 'city', + 'state', + 'mailing_city', + 'mailing_state', + 'member_category' + ); + $sql = " + SELECT ".implode(',', $fromFieldArray)." + FROM {$this->tmpTableName}"; + $stmt = $this->dbh->query($sql); + while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { + $member = new Toolkit_Members_MemberImport_Member(); + $member->setCreate_date(date('m/d/Y')); + foreach ($row as $prop => $val) { + if (in_array($prop, $memberCategoryFields)) { + + continue; + } + // we need to get the state done first before the city + $propertyName = $values[$prop]; + if (!in_array(strtolower($propertyName), $ignore)) { + if ($val != '') { + $setprop = "set$propertyName"; + $member->$setprop($val); + } + } else { + if (strtolower($propertyName) == 'city') { + $city = $val; + } + if (strtolower($propertyName) == 'state') { + $state = $val; + } + if (strtolower($propertyName) == 'mailing_city') { + $mailing_city = $val; + } + if (strtolower($propertyName) == 'mailing_state') { + $mailing_state = $val; + } + } + } + if ($state) { + $member->setState($this->dbh, $state); + } + if ($state && $city) { + $member->setCity($this->dbh, $city); + } + + if ($mailing_state) { + $member->setMailingState($this->dbh, $mailing_state); + } + if ($mailing_city) { + $member->setMailingCity($this->dbh, $mailing_city); + } + if (!empty($member_categories)) { + $member->setMember_categories($member_categories); + } + $member->save($this->dbh); + $this->members[] = $member; + } + echo '
'.print_r($this->members, true).'
'; + return true; + } + + /** + * Handles how to display the current step the user is at in the form + * + * destroying and resetting the captcha value dis-allows someone from + * re-sending a form on a previous captcha. + * + * @return string form HTML state + * @access public + */ + public function toHtml() + { + if ($this->validate()) { + $this->cleanForm(); + + if ($this->process(array(&$this, 'processForm'), $this->mergeFiles)) { + $this->freeze(); + $output = $this->successMsg; + } + } elseif ($this->isSubmitted()) { + $output = $this->errorMsg; + $output .= parent::toHTML(); + } else { + $output = parent::toHTML(); + } + return $output; + } +} diff --git a/Member.php b/Member.php new file mode 100644 index 0000000..a5527ac --- /dev/null +++ b/Member.php @@ -0,0 +1,672 @@ + + * @license Gaslight Media + * @link <> + */ +/** + * Toolkit_Members_MemberImport_Member + * + * Member class for importing member + * + * @category Toolkit_Members + * @package Members + * @author Jamie Kahgee + * @license Gaslight Media + * @link <> + */ +class Toolkit_Members_MemberImport_Member +{ + + /** + * Description for $active + * @var boolean + * @access protected + */ + protected $active = true; + protected $non_member = true; + protected $new_member = false; + protected $create_date; + + /** + * Description for $join_date + * @var date + * @access protected + */ + protected $join_date; + + /** + * Description for $member_id + * @var integer + * @access protected + */ + protected $member_id; + + /** + * Description for $member_name + * @var string + * @access protected + */ + protected $member_name; + + /** + * Description for $street + * @var string + * @access protected + */ + protected $street; + + /** + * Description for $country + * @var string + * @access protected + */ + protected $country; + + /** + * Description for $phone_area + * @var string + * @access protected + */ + protected $phone_area; // not real field + + /** + * Description for $phone + * @var string + * @access protected + */ + protected $phone; + + /** + * Description for $fax_area + * @var string + * @access protected + */ + protected $fax_area; // not real field + + /** + * Description for $fax + * @var string + * @access protected + */ + protected $fax; + + /** + * Description for $process_email + * @var string + * @access protected + */ + protected $process_email; + + /** + * Description for $url + * @var string + * @access protected + */ + protected $url; + + /** + * Description for $city + * @var string + * @access protected + */ + protected $city; + + /** + * Description for $city_id + * @var integer + * @access protected + */ + protected $city_id; + + /** + * Description for $state + * @var string + * @access protected + */ + protected $state; + + /** + * Description for $state_id + * @var integer + * @access protected + */ + protected $state_id; + + /** + * Description for $description + * @var integer + * @access protected + */ + protected $description; + + /** + * Description for $zip + * @var string + * @access protected + */ + protected $zip; + + /** + * Description for $toll_free_area + * @var string + * @access protected + */ + protected $toll_free_area; // not real field + + /** + * Description for $toll_free + * @var string + * @access protected + */ + protected $toll_free; + + /** + * Description for $member_contact_email + * @var string + * @access protected + */ + protected $member_contact_email; + + /** + * Description for $mailing_address + * @var string + * @access protected + */ + protected $mailing_address; + + /** + * Description for $primary_contact + * @var string + * @access protected + */ + protected $primary_contact; + + /** + * Description for $primary_contact_fname + * @var string + * @access protected + */ + protected $primary_contact_fname; + + /** + * Description for $primary_contact_lname + * @var string + * @access protected + */ + protected $primary_contact_lname; + + /** + * Description for $mailing_state + * @var string + * @access protected + */ + protected $mailing_state; + + /** + * Description for $mailing_state_id + * @var integer + * @access protected + */ + protected $mailing_state_id; + + /** + * Description for $mailing_city + * @var string + * @access protected + */ + protected $mailing_city; + + /** + * Description for $mailing_city_id + * @var integer + * @access protected + */ + protected $mailing_city_id; + + /** + * Description for $mailing_zip + * @var string + * @access protected + */ + protected $mailing_zip; + + /** + * Description for $lat + * @var double + * @access protected + */ + protected $lat; + + /** + * Description for $lon + * @var double + * @access protected + */ + protected $lon; + + /** + * Description for $member_categories + * @var array + * @access protected + */ + protected $member_categories = array(); // not real field + + /** + * created setters and getters for the class properties + * checks for existance of the property before running getter/setter + * + * @param String $name Function name + * @param Mixed $args if set the arg to set + * + * @return mixed + * @throws Exception + * @access public + */ + public function __call($name, $args) + { + if (preg_match('/^(get|set)(\w+)/', strtolower($name), $match) + && $attribute = $this->validateAttribute($match[2]) + ) { + if ('get' == $match[1]) { + return $this->$attribute; + } else { + $this->$attribute = $args[0]; + } + } else { + throw new Exception( + 'Call to undefined method Member::' . $name + ); + } + } + /** + * _insert + * + * create the record in database + * + * @param PDO $dbh Databese connection + * + * @return void + * @access public + */ + private function _insert(PDO $dbh) + { + $classData = get_object_vars($this); + unset( + $classData['member_id'], + $classData['member_categories'], + $classData['phone_area'], + $classData['fax_area'], + $classData['toll_free_area'], + $classData['mailing_state'] + ); + $sql = Toolkit_Common::createSQLInsert( + 'member', + array_keys($classData) + ); + $sql .= " RETURNING member_id"; + $stmt = Toolkit_Common::prepareQuery( + $dbh, + 'member', + $sql, + $classData + ); + $stmt->execute(); + $this->setMemberId($stmt->fetchColumn()); + } + /** + * getCLassVars + * + * get all class properties + * + * @return Array Keys of the class properties + * @access public + */ + public function getClassVars() + { + return array_keys(get_class_vars(get_class($this))); + } + /** + * check the date given to see if its a valid date first before setting it + * + * @param String $date date + * + * @return void + * @access public + */ + public function setJoinDate($date) + { + if (strtotime($date)) { + $this->join_date = $date; + } + } + /** + * check the number given to make sure it is numeric + * + * @param String $lat latitude + * + * @return void + * @access public + */ + public function setLat($lat) + { + if (is_numeric($lat)) { + $this->lat = $lat; + } + } + /** + * check the number given to make sure it is numeric + * + * @param String $lon longitude + * + * @return void + * @access public + */ + public function setLon($lon) + { + if (is_numeric($lon)) { + $this->lon = $lon; + } + } + /** + * check the number given to make sure it is numeric + * + * @param Int $id member id must be numeric + * + * @throws Exception if not numeric + * @return void + * @access public + */ + public function setMemberId($id) + { + if (!is_numeric($id)) { + throw new Exception('Id must be numeric'); + } + if (!$this->member_id) { + $this->member_id = $id; + } + } + /** + * create the city if not found then set member property + * + * @param PDO $dbh Database connection + * @param String $cityName City Name + * + * @return Int + * @access public + */ + public function setCity(PDO $dbh, $cityName) + { + if (!$cityName) { + return false; + } + // update the city name so each first letter is upper case + $cityName = ucwords(strtolower($cityName)); + $city = new Toolkit_Members_MemberImport_City(); + $newCity = $city->fetchByName($dbh, $cityName); + if ($newCity) { + $cityId = $newCity->getCity_id(); + $this->setCity_id($cityId); + } else { + // need to create new city + $city->setCity_name($cityName); + $city->setState_id($this->getState_id()); + $city->save($dbh); + $this->setCity_id($city->getCity_id()); + } + } + /** + * create the city if not found then set member property + * + * @param PDO $dbh Database connection + * @param String $cityName City Name + * + * @return boolean + * @access public + */ + public function setMailingCity(PDO $dbh, $cityName) + { + if (!$cityName) { + return false; + } + $city = new Toolkit_Members_MemberImport_City(); + $newCity = $city->fetchByName($dbh, $cityName); + if ($newCity) { + $cityId = $newCity->getCity_id(); + $this->setMailing_city_id($cityId); + } else { + // need to create new city + $city->setCity_name($cityName); + $city->setState_id($this->getMailing_state_id()); + $city->save($dbh); + $this->setMailing_city_id($city->getCity_id); + } + } + /** + * create the state if not found then set member property + * + * @param PDO $dbh Database connection + * @param String $stateAbb State Abbreviation + * + * @return boolean + * @access public + */ + public function setMailingState(PDO $dbh, $stateAbb) + { + if (!$stateAbb) { + return false; + } + $state = new Toolkit_Members_MemberImport_State(); + $newState = $state->fetchByAbb($dbh, $stateAbb); + if (is_object($newState)) { + if ($stateId = $newState->getState_id()) { + $this->setMailing_state_id($stateId); + } else { + // ? + } + } else { + var_dump($stateId); + var_dump($stateAbb); + } + } + /** + * also set the member_contact_email at same time + * + * @param String $email Email address + * + * @return void + * @access public + */ + public function setProcessEmail($email) + { + if ($email != '') { + $this->process_email = $email; + $this->member_contact_email = $email; + } + } + /** + * phone field may come in two seperate fields from the file + * here it's appending the fields area and + * + * @param $phone_area phone area code + * + * @return void + * @access public + */ + public function setPhoneArea($phone_area) + { + $this->phone = $phone_area . ' '; + } + /** + * set phone + * + * @param $phone phone number + * + * @return void + * @access public + */ + public function setPhone($phone) + { + if ($phone) { + $phone = Toolkit_Common::filterPhone($phone); + } + $this->phone .= $phone; + $this->phone = trim($this->phone); + } + /** + * set fax + * + * @param String $fax_area fax area code part + * + * @return void + * @access public + */ + public function setFaxArea($fax_area) + { + $this->fax = $fax_area. ' '; + } + /** + * set fax number + * + * @param String $fax fax number part + * + * @return void + * @access public + */ + public function setFax($fax) + { + $this->fax .= $fax; + $this->fax = trim($this->fax); + } + /** + * set toll free area code + * + * @param String $toll_free_area toll free area code + * + * @return void + * @access public + */ + public function setTollFreeArea($toll_free_area) + { + $this->toll_free = $toll_free_area . ' '; + } + /** + * set toll free number + * + * @param String $toll_free Toll free number + * + * @return void + * @access public + */ + public function setTollFree($toll_free) + { + $this->toll_free .= $toll_free; + $this->toll_free = trim($this->toll_free); + } + /** + * create the state if not found then set member property + * + * @param PDO $dbh Database connection + * @param String $stateAbb State Abbreviation + * + * @return boolean + * @access public + */ + public function setState(PDO $dbh, $stateAbb) + { + if (!$stateAbb) { + return false; + } + $state = new Toolkit_Members_MemberImport_State(); + $newState = $state->fetchByAbb($dbh, strtoupper($stateAbb)); + if ($newState && $stateId = $newState->getState_id()) { + $this->setState_id($stateId); + } else { + // ? + } + } + /** + * save + * + * check to see if the id is set if it is then update else call insert + * also set the member categories + * + * @param PDO $dbh Database connection + * + * @return void + * @access public + */ + public function save(PDO $dbh) + { + if ($this->member_id) { + $this->_update($dbh); + } else { + $this->_insert($dbh); + } + // now we can add categories + if (!empty($this->member_categories)) { + $addMemberCatSql = " + INSERT INTO member_category + (member_id, category_id) + VALUES + (:member_id, :category_id)"; + $addMemberCat = $dbh->prepare($addMemberCatSql); + foreach ($this->member_categories as $catName) { + $category = new Toolkit_Members_MemberImport_Category(); + if ($catName + && $oldCategory = $category->fetchByName($dbh, $catName) + ) { + // can now add this category to this member + $addMemberCat->bindParam( + ":member_id", + $this->getMember_id(), + PDO::PARAM_INT + ); + $addMemberCat->bindParam( + ":category_id", + $oldCategory->getCategory_id(), + PDO::PARAM_INT + ); + $addMemberCat->execute(); + } else if ($catName) { + // not found must add + $category->setName($catName); + $category->save($dbh); + $addMemberCat->bindParam( + ":member_id", + $this->getMember_id(), + PDO::PARAM_INT + ); + $addMemberCat->bindParam( + ":category_id", + $category->getCategory_id(), + PDO::PARAM_INT + ); + $addMemberCat->execute(); + } + } + } + } + /** + * validateAttribute + * + * checks to see if the properties exists + * + * @param String $name property name + * + * @return String + * @access public + */ + protected function validateAttribute($name) + { + if (property_exists(get_class($this), $name)) { + return strtolower($name); + } + } +} diff --git a/State.php b/State.php new file mode 100644 index 0000000..ac1bf38 --- /dev/null +++ b/State.php @@ -0,0 +1,106 @@ + + * @license http://www.gaslightmedia.com Gaslightmedia + * @link <> + */ + +/** + * Toolkit_Members_MemberImport_State + * + * member state class + * + * @category Toolkit + * @package Toolkit + * @author Steve Sutton + * @license http://www.gaslightmedia.com Gaslightmedia + * @link <> + */ +class Toolkit_Members_MemberImport_State +{ + /** + * Description of $state_abb + * @var string + * @access protected + */ + protected $state_abb; + + /** + * Description of $city_name + * @var string + * @access protected + */ + protected $state_name; + + /** + * Description of $state_id + * @var integer + * @access protected + */ + protected $state_id; + + /** + * Helps to create functions for any class variable if the variable does + * exists + * + * @param String $name Name of variable + * @param mixed $args Argument for the varable (if set) + * + * @return Toolkit_Table + */ + public function __call($name, $args) + { + if (preg_match('/^(get|set)(\w+)/', strtolower($name), $match) + && $attribute = $this->validateAttribute($match[2]) + ) { + if ('get' == $match[1]) { + return $this->$attribute; + } else { + $this->$attribute = $args[0]; + } + } else { + throw new Exception( + 'Call to undefined method Member::' . $name + ); + } + } + /** + * given a state abbreviation search for the state id + * + * @param PDO $dbh Database connection + * @param String $abb State Abbreviation + * + * @return Object + */ + public function fetchByAbb(PDO $dbh, $abb) + { + $sql = " + SELECT state_id,state_name,state_abb + FROM state + WHERE state_abb = :abb"; + $stmt = $dbh->prepare($sql); + $stmt->setFetchMode(PDO::FETCH_CLASS, 'Toolkit_Members_MemberImport_State'); + $stmt->bindParam(":abb", $abb); + $stmt->execute(); + return $stmt->fetch(PDO::FETCH_CLASS); + } + /** + * Validates the property + * + * @param String $name property name + * + * @return String + */ + protected function validateAttribute($name) + { + if (property_exists(get_class($this), $name)) { + return strtolower($name); + } + } +} diff --git a/index.php b/index.php new file mode 100644 index 0000000..315cd02 --- /dev/null +++ b/index.php @@ -0,0 +1,48 @@ + + * @license Gaslight Media + * @link <> + */ +require_once '../../../setup.phtml'; +if (!isset($_REQUEST['_qf__import-members']) && (!$_GET['skip'] == 't')) { + $form = new Toolkit_Members_MemberImport_FileForm( + 'import-file', + 'post', + 'index.php', + null, + null, + true + ); + $form->configureForm(); + if (is_file('./upload/members.csv')) { + echo 'Use Current Uploaded File
'; + } + echo GLM_TOOLBOX::top('File Import', ''); + echo $form->toHtml(); + echo GLM_TOOLBOX::footer(); +} +if (isset($_REQUEST['_qf__import-file']) + || isset($_REQUEST['_qf__import-members']) + || $_GET['skip'] == 't' +) { + // next page + $form2 = new Toolkit_Members_MemberImport_ImportForm( + 'import-members', + 'post', + 'index.php', + null, + null, + true + ); + $form2->configureForm(); + echo GLM_TOOLBOX::top('File Import', ''); + echo $form2->toHtml(); + echo GLM_TOOLBOX::footer(); +} diff --git a/updateLatLon.php b/updateLatLon.php new file mode 100644 index 0000000..0e84d1c --- /dev/null +++ b/updateLatLon.php @@ -0,0 +1,69 @@ +prepare($sql); + $sql = " + SELECT m.member_id,m.member_name, + m.street, c.city_name, s.state_abb,m.zip + FROM member m + LEFT OUTER JOIN city c ON (m.city_id = c.city_id) + LEFT OUTER JOIN state s ON (m.state_id = s.state_id) + WHERE m.lat IS NULL + AND m.city_id IS NOT NULL + AND m.state_id IS NOT NULL + ORDER BY m.member_name"; + $stmt = $dbh->query($sql); + while ($member = $stmt->fetch(PDO::FETCH_ASSOC)) { + var_dump($member); + $address = array( + 'city' => $member['city_name'], + 'state' => $member['state_abb'], + 'zip' => $member['zip'], + ); + if (!empty($member['street'])) { + $address['street'] = $member['street']; + } + var_dump($address); + $lat = $lon = (float)0.0; + try { + $response = $geocoder->geocodeAddress($address); + $responseArray = unserialize($response); + if ($responseArray['ResultSet']['Result'][0]['Latitude']) { + $lat = $responseArray['ResultSet']['Result'][0]['Latitude']; + $lon = $responseArray['ResultSet']['Result'][0]['Longitude']; + } else { + $lat = $responseArray['ResultSet']['Result']['Latitude']; + $lon = $responseArray['ResultSet']['Result']['Longitude']; + } + var_dump($lat); + var_dump($lon); + + $updateLocation->bindParam(':lat', $lat); + $updateLocation->bindParam(':lon', $lon); + $updateLocation->bindParam(':id', $member['member_id'], PDO::PARAM_INT); + $updateLocation->execute(); +// exit; + } catch (BadMethodCallException $e) { + var_dump($e); + exit; + Toolkit_Logger::logException('Invalid Arg', $e); + } catch (Exception $e) { + var_dump($e); + exit; + Toolkit_Logger::logException('Yahoo GeoCode', $e); + } + } +} catch (PDOException $e) { + var_dump($e); + exit; + Toolkit_Common::handleError($e); +} + -- 2.17.1