function display($tpl = null)
{
$this->setLayout('default');
$message = OSMembershipHelper::getConfigValue('cancel_message');
$this->assignRef('message', $message);
parent::display($tpl);
}
function _buildListArray(&$lists, $state)
{
$db = JFactory::getDbo();
// Build plans dropdown
$options = array();
$options[] = JHtml::_('select.option', 0, JText::_('OSM_PLAN'), 'id', 'title');
$sql = 'SELECT id, title FROM #__osmembership_plans WHERE published=1 ORDER BY ordering ';
$db->setQuery($sql);
$options = array_merge($options, $db->loadObjectList());
$lists['plan_id'] = JHtml::_('select.genericlist', $options, 'plan_id', ' class="inputbox" onchange="submit();"', 'id', 'title', $state->plan_id);
// Build countries dropdown
$options = array();
$options[] = JHtml::_('select.option', 0, JText::_('OSM_SELECT_COUNTRY'));
$sql = 'SELECT `name` AS value, `name` AS text FROM `#__osmembership_countries` WHERE `published`=1';
$db->setQuery($sql);
$options = array_merge($options, $db->loadObjectList());
$lists['country'] = JHtml::_('select.genericlist', $options, 'country', ' class="inputbox" onchange="submit();" ', 'value', 'text', $state->country);
$defaultCountry = OSMembershipHelper::getConfigValue('default_country');
$countryCode = OSmembershipHelper::getCountryCode($defaultCountry);
if (OSMembershipHelperEuvat::isEUCountry($countryCode)) {
$this->showVies = true;
$options = array();
$options[] = JHtml::_('select.option', -1, JText::_('OSM_VIES'));
$options[] = JHtml::_('select.option', 0, JText::_('OSM_NO'));
$options[] = JHtml::_('select.option', 1, JText::_('OSM_YES'));
$lists['vies'] = JHtml::_('select.genericlist', $options, 'vies', ' class="inputbox" onchange="submit();" ', 'value', 'text', $state->vies);
} else {
$this->showVies = false;
}
}
function _buildListArray(&$lists, $state)
{
$db = JFactory::getDbo();
$discountTypes = array(0 => '%', 1 => OSMembershipHelper::getConfigValue('currency_symbol'));
$nullDate = $db->getNullDate();
$sql = 'SELECT id, title FROM #__osmembership_plans WHERE published=1 ORDER BY ordering ';
$db->setQuery($sql);
$options = array();
$options[] = JHtml::_('select.option', 0, JText::_('OSM_PLAN'), 'id', 'title');
$options = array_merge($options, $db->loadObjectList());
$lists['plan_id'] = JHtml::_('select.genericlist', $options, 'plan_id', ' class="inputbox" onchange="submit();"', 'id', 'title', $state->plan_id);
$this->dateFormat = OSMembershipHelper::getConfigValue('date_format');
$this->nullDate = '0000-00-00';
$this->discountTypes = $discountTypes;
}
function _buildListArray(&$lists, $item)
{
$db = JFactory::getDbo();
$sql = 'SELECT id, title FROM #__osmembership_plans WHERE published = 1 ORDER BY ordering ';
$db->setQuery($sql);
$options = array();
$options[] = JHtml::_('select.option', 0, JText::_('OSM_ALL_PLANS'), 'id', 'title');
$options = array_merge($options, $db->loadObjectList());
$lists['plan_id'] = JHtml::_('select.genericlist', $options, 'plan_id', ' class="inputbox" ', 'id', 'title', $item->plan_id);
$options = array();
$options[] = JHtml::_('select.option', '', JText::_('OSM_ALL_COUNTRIES'));
$sql = 'SELECT `name` AS value, `name` AS text FROM `#__osmembership_countries` WHERE `published`=1';
$db->setQuery($sql);
$options = array_merge($options, $db->loadObjectList());
$lists['country'] = JHtml::_('select.genericlist', $options, 'country', ' class="inputbox" ', 'value', 'text', $item->country, 'country');
$defaultCountry = OSMembershipHelper::getConfigValue('default_country');
$countryCode = OSMembershipHelper::getCountryCode($defaultCountry);
if (OSMembershipHelperEuvat::isEUCountry($countryCode)) {
$lists['vies'] = JHtml::_('select.booleanlist', 'vies', ' class="inputbox" ', $item->vies);
}
return true;
}
function _buildListArray(&$lists, $item)
{
$db = JFactory::getDbo();
$fieldTypes = array('Text', 'Textarea', 'List', 'Checkboxes', 'Radio', 'Date', 'Heading', 'Message', 'File', 'Countries', 'State', 'SQL');
$options = array();
$options[] = JHtml::_('select.option', -1, JText::_('OSM_FIELD_TYPE'));
foreach ($fieldTypes as $fieldType) {
$options[] = JHtml::_('select.option', $fieldType, $fieldType);
}
if ($item->is_core) {
$readOnly = ' readonly="true" ';
} else {
$readOnly = '';
}
$lists['fieldtype'] = JHtml::_('select.genericlist', $options, 'fieldtype', ' class="inputbox" ' . $readOnly, 'value', 'text', $item->fieldtype);
$sql = 'SELECT id, title FROM #__osmembership_plans WHERE published = 1 ORDER BY ordering ';
$db->setQuery($sql);
$options = array();
$options[] = JHtml::_('select.option', 0, JText::_('OSM_ALL_PLANS'), 'id', 'title');
$options = array_merge($options, $db->loadObjectList());
if ($item->id) {
$sql = 'SELECT plan_id FROM #__osmembership_field_plan WHERE field_id=' . $item->id;
$db->setQuery($sql);
$planIds = $db->loadColumn();
if (count($planIds) == 0) {
$planIds[] = 0;
}
} else {
$planIds = array();
$planIds[] = 0;
}
$lists['plan_id'] = JHtml::_('select.genericlist', $options, 'plan_id[]', ' class="inputbox" multiple="multiple" ', 'id', 'title', $planIds);
$options = array();
$options[] = JHtml::_('select.option', 1, JText::_('Yes'));
$options[] = JHtml::_('select.option', 2, JText::_('No'));
$lists['required'] = JHtml::_('select.booleanlist', 'required', ' class="inputbox" ', $item->required);
$lists['multiple'] = JHtml::_('select.booleanlist', 'multiple', ' class="inputbox" ', $item->multiple);
$options = array();
$options[] = JHtml::_('select.option', 0, JText::_('None'));
$options[] = JHtml::_('select.option', 1, JText::_('Integer Number'));
$options[] = JHtml::_('select.option', 2, JText::_('Number'));
$options[] = JHtml::_('select.option', 3, JText::_('Email'));
$options[] = JHtml::_('select.option', 4, JText::_('Url'));
$options[] = JHtml::_('select.option', 5, JText::_('Phone'));
$options[] = JHtml::_('select.option', 6, JText::_('Past Date'));
$options[] = JHtml::_('select.option', 7, JText::_('Ip'));
$options[] = JHtml::_('select.option', 8, JText::_('Min size'));
$options[] = JHtml::_('select.option', 9, JText::_('Max size'));
$options[] = JHtml::_('select.option', 10, JText::_('Min integer'));
$options[] = JHtml::_('select.option', 11, JText::_('Max integer'));
$lists['datatype_validation'] = JHtml::_('select.genericlist', $options, 'datatype_validation', 'class="inputbox"', 'value', 'text', $item->datatype_validation);
$integration = OSMembershipHelper::getConfigValue('cb_integration');
// Trigger plugins to get list of fields for mapping
JPluginHelper::importPlugin('osmembership');
$dispatcher = JDispatcher::getInstance();
$results = $dispatcher->trigger('onGetFields', array());
$fields = array();
if (count($results)) {
foreach ($results as $res) {
if (is_array($res) && count($res)) {
$fields = $res;
break;
}
}
}
if (count($fields)) {
$options = array();
$options[] = JHtml::_('select.option', '', JText::_('Select Field'));
$options = array_merge($options, $fields);
$lists['field_mapping'] = JHtml::_('select.genericlist', $options, 'field_mapping', ' class="inputbox" ', 'value', 'text', $item->field_mapping);
}
$lists['fee_field'] = JHtml::_('select.booleanlist', 'fee_field', ' class="inputbox" ', $item->fee_field);
$lists['show_on_members_list'] = JHtml::_('select.booleanlist', 'show_on_members_list', ' class="inputbox" ', $item->show_on_members_list);
$lists['hide_on_membership_renewal'] = JHtml::_('select.booleanlist', 'hide_on_membership_renewal', ' class="inputbox" ', $item->hide_on_membership_renewal);
if (JPluginHelper::isEnabled('osmembership', 'userprofile')) {
$options = array();
$options[] = JHtml::_('select.option', '', JText::_('Select Field'));
$fields = array('address1', 'address2', 'city', 'region', 'country', 'postal_code', 'phone', 'website', 'favoritebook', 'aboutme', 'dob');
foreach ($fields as $field) {
$options[] = JHtml::_('select.option', $field);
}
$lists['profile_field_mapping'] = JHtml::_('select.genericlist', $options, 'profile_field_mapping', ' class="inputbox" ', 'value', 'text', $item->profile_field_mapping);
}
return true;
}
/**
* Get list of states for the selected country, using in AJAX request
*/
public function get_states()
{
$app = JFactory::getApplication();
$countryName = $app->input->get('country_name', '', 'string');
$fieldName = $app->input->get('field_name', 'state', 'string');
$stateName = $app->input->get('state_name', '', 'string');
if (!$countryName) {
$countryName = OSMembershipHelper::getConfigValue('default_country');
}
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->clear();
$query->select('required')->from('#__osmembership_fields')->where('name=' . $db->quote('state'));
$db->setQuery($query);
$required = $db->loadResult();
$required ? $class = 'validate[required]' : ($class = '');
$query->clear();
$query->select('country_id')->from('#__osmembership_countries')->where('name=' . $db->quote($countryName));
$db->setQuery($query);
$countryId = $db->loadResult();
//get state
$query->clear();
$query->select('state_2_code AS value, state_name AS text')->from('#__osmembership_states')->where('country_id=' . (int) $countryId)->where('published=1');
$db->setQuery($query);
$states = $db->loadObjectList();
$options = array();
if (count($states)) {
$options[] = JHtml::_('select.option', '', JText::_('OSM_SELECT_STATE'));
$options = array_merge($options, $states);
} else {
$options[] = JHtml::_('select.option', 'N/A', JText::_('OSM_NA'));
}
echo JHtml::_('select.genericlist', $options, $fieldName, ' class="input-large ' . $class . '" id="' . $fieldName . '"', 'value', 'text', $stateName);
$app->close();
}
/**
* Store subscriber data into database
*
* @param int $recordId
* Subscriber ID
* @param array $data
*/
public function storeData($recordId, $data)
{
jimport('joomla.filesystem.folder');
$db = JFactory::getDbo();
// Don't delete the file upload custom fields
$fileFieldIds = array(0);
foreach ($this->fields as $field) {
$fieldType = strtolower($field->type);
if ($fieldType == 'file') {
$fileFieldIds[] = $field->id;
}
}
$sql = 'DELETE FROM #__osmembership_field_value WHERE subscriber_id=' . (int) $recordId . ' AND field_id NOT IN (' . implode(',', $fileFieldIds) . ')';
$db->setQuery($sql);
$db->execute();
$rowFieldValue = JTable::getInstance('OsMembership', 'FieldValue');
foreach ($this->fields as $field) {
$fieldType = strtolower($field->type);
if ($field->is_core || $fieldType == 'heading' || $fieldType == 'message') {
continue;
}
$name = $field->name;
if ($fieldType == 'file') {
// If there are field, we need to upload the file to server and save it !
if (isset($_FILES[$name])) {
if ($_FILES[$name]['name'] != '') {
$pathUpload = JPATH_ROOT . '/media/com_osmembership/upload';
if (!JFolder::exists($pathUpload)) {
JFolder::create($pathUpload, 0777);
}
$allowedExtensions = OSMembershipHelper::getConfigValue('allowed_file_types');
if (!$allowedExtensions) {
$allowedExtensions = 'doc, docx, ppt, pptx, pdf, zip, rar, jpg, jepg, png, zipx';
}
$allowedExtensions = explode(',', $allowedExtensions);
$allowedExtensions = array_map('trim', $allowedExtensions);
$fileName = $_FILES[$field->name]['name'];
$fileExt = JFile::getExt($fileName);
if (in_array(strtolower($fileExt), $allowedExtensions)) {
$fileName = JFile::makeSafe($fileName);
if (JFile::exists($pathUpload . '/' . $fileName)) {
$targetFileName = time() . '_' . $fileName;
} else {
$targetFileName = $fileName;
}
JFile::upload($_FILES[$field->name]['tmp_name'], $pathUpload . '/' . $targetFileName);
$data[$field->name] = $targetFileName;
}
}
}
}
$fieldValue = isset($data[$field->name]) ? $data[$field->name] : '';
if (!empty($fieldValue)) {
if (in_array($field->id, $fileFieldIds)) {
// Need to delete the old file
$sql = 'DELETE FROM #__osmembership_field_value WHERE subscriber_id=' . (int) $recordId . ' AND field_id=' . $field->id;
$db->setQuery($sql);
$db->execute();
}
$rowFieldValue->id = 0;
$rowFieldValue->field_id = $field->id;
$rowFieldValue->subscriber_id = $recordId;
if (is_array($fieldValue)) {
$rowFieldValue->field_value = json_encode($fieldValue);
} else {
$rowFieldValue->field_value = $fieldValue;
}
$rowFieldValue->store();
}
}
return true;
}
/**
* Change the db structure of the previous version
*
*/
function com_install()
{
error_reporting(0);
$db = JFactory::getDbo();
require_once JPATH_ROOT . '/components/com_osmembership/helper/helper.php';
//First, we will need to create additional database tables which was not available in old version
$prefix = $db->getPrefix();
$tables = $db->getTableList();
if (!in_array($prefix . 'osmembership_categories', $tables)) {
//Create the categories table, added in version 1.1.1
$sql = "CREATE TABLE IF NOT EXISTS `#__osmembership_categories` (\n `id` INT NOT NULL AUTO_INCREMENT,\n `title` VARCHAR(255) NULL,\n `description` TEXT NULL,\n `published` TINYINT UNSIGNED NULL,\n PRIMARY KEY(`id`)\n ) DEFAULT CHARSET=utf8 ;";
$db->setQuery($sql);
$db->execute();
}
if (!in_array($prefix . 'osmembership_field_plan', $tables)) {
//Create the categories table, added in version 1.1.1
$sql = "CREATE TABLE IF NOT EXISTS `#__osmembership_field_plan` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `field_id` int(11) DEFAULT NULL,\n `plan_id` int(11) DEFAULT NULL,\n PRIMARY KEY (`id`)\n ) DEFAULT CHARSET=utf8;";
$db->setQuery($sql);
$db->execute();
//Need to migrate data here
$sql = 'INSERT INTO #__osmembership_field_plan(field_id, plan_id)
SELECT id, plan_id FROM #__osmembership_fields WHERE plan_id > 0
';
$db->setQuery($sql);
$db->execute();
$sql = 'UPDATE #__osmembership_fields SET plan_id=1 WHERE plan_id > 0';
$db->setQuery($sql);
$db->execute();
}
if (!in_array($prefix . 'osmembership_messages', $tables)) {
$sql = 'CREATE TABLE IF NOT EXISTS `#__osmembership_messages` (
`id` INT NOT NULL AUTO_INCREMENT,
`message_key` VARCHAR(50) NULL,
`message` TEXT NULL,
PRIMARY KEY(`id`)
) CHARACTER SET `utf8`;';
$db->setQuery($sql);
$db->execute();
}
if (!in_array($prefix . 'osmembership_states', $tables)) {
$statesSql = JPATH_ADMINISTRATOR . '/components/com_osmembership/sql/states.osmembership.sql';
$sql = JFile::read($statesSql);
$queries = $db->splitSql($sql);
if (count($queries)) {
foreach ($queries as $query) {
$query = trim($query);
if ($query != '' && $query[0] != '#') {
$db->setQuery($query);
$db->execute();
}
}
}
}
$sql = 'SELECT COUNT(*) FROM #__osmembership_configs';
$db->setQuery($sql);
$total = $db->loadResult();
if (!$total) {
$configSql = JPATH_ADMINISTRATOR . '/components/com_osmembership/sql/config.osmembership.sql';
$sql = JFile::read($configSql);
$queries = $db->splitSql($sql);
if (count($queries)) {
foreach ($queries as $query) {
$query = trim($query);
if ($query != '' && $query[0] != '#') {
$db->setQuery($query);
$db->execute();
}
}
}
}
//Change coupon code data type
$sql = 'ALTER TABLE `#__osmembership_coupons` CHANGE `valid_from` `valid_from` datetime DEFAULT NULL;';
$db->setQuery($sql);
$db->execute();
$sql = "ALTER TABLE `#__osmembership_coupons` CHANGE `valid_to`\t`valid_to` datetime DEFAULT NULL;";
$db->setQuery($sql);
$db->execute();
$sql = 'SELECT COUNT(*) FROM #__osmembership_plugins';
$db->setQuery($sql);
$total = $db->loadResult();
if (!$total) {
$pluginsSql = JPATH_ADMINISTRATOR . '/components/com_osmembership/sql/plugins.osmembership.sql';
$sql = JFile::read($pluginsSql);
$queries = $db->splitSql($sql);
if (count($queries)) {
foreach ($queries as $query) {
$query = trim($query);
if ($query != '' && $query[0] != '#') {
$db->setQuery($query);
$db->execute();
}
}
}
}
//Invoice data
$sql = 'SELECT COUNT(*) FROM #__osmembership_configs WHERE config_key="invoice_format"';
$db->setQuery($sql);
$total = $db->loadResult();
if (!$total) {
$configSql = JPATH_ADMINISTRATOR . '/components/com_osmembership/sql/config.invoice.sql';
$sql = JFile::read($configSql);
$queries = $db->splitSql($sql);
if (count($queries)) {
foreach ($queries as $query) {
$query = trim($query);
if ($query != '' && $query[0] != '#') {
$db->setQuery($query);
$db->execute();
}
}
}
}
$sql = "SELECT COUNT(*) FROM #__osmembership_currencies WHERE currency_code='RUB'";
$db->setQuery($sql);
$total = $db->loadResult();
if (!$total) {
$sql = "INSERT INTO #__osmembership_currencies(currency_code, currency_name) VALUES('RUB', 'Russian Rubles')";
$db->setQuery($sql);
$db->execute();
}
$fields = array_keys($db->getTableColumns('#__osmembership_countries'));
if (!in_array('id', $fields)) {
//Change the name of the name of column from country_id to ID
$sql = 'ALTER TABLE `#__osmembership_countries` CHANGE `country_id` `id` INT(11) NOT NULL AUTO_INCREMENT;';
$db->setQuery($sql);
$db->execute();
//Add country ID column back for BC
$sql = "ALTER TABLE `#__osmembership_countries` ADD `country_id` INT(11) NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
//Set country_id value the same with id
$sql = 'UPDATE #__osmembership_countries SET country_id=id';
$db->setQuery($sql);
$db->execute();
}
$fields = array_keys($db->getTableColumns('#__osmembership_states'));
if (!in_array('published', $fields)) {
$db->setQuery("ALTER TABLE `#__osmembership_states` ADD `published` TINYINT( 4 ) NOT NULL DEFAULT '1'");
$db->execute();
$db->setQuery("UPDATE `#__osmembership_states` SET `published` = 1");
$db->execute();
}
if (!in_array('id', $fields)) {
//Change the name of the name of column from country_id to ID
$sql = 'ALTER TABLE `#__osmembership_states` CHANGE `state_id` `id` INT(11) NOT NULL AUTO_INCREMENT;';
$db->setQuery($sql);
$db->execute();
//Add country ID column back for BC
$sql = "ALTER TABLE `#__osmembership_states` ADD `state_id` INT(11) NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
//Set country_id value the same with id
$sql = 'UPDATE #__osmembership_states SET state_id=id';
$db->setQuery($sql);
$db->execute();
}
#Custom Fields table
$fields = array_keys($db->getTableColumns('#__osmembership_fields'));
if (!in_array('hide_on_membership_renewal', $fields)) {
$sql = "ALTER TABLE `#__osmembership_fields` ADD `hide_on_membership_renewal` TINYINT NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('show_on_members_list', $fields)) {
$sql = "ALTER TABLE `#__osmembership_fields` ADD `show_on_members_list` TINYINT NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
$defaultShowedFields = array("first_name", "last_name", "email", "organization");
$sql = 'UPDATE #__osmembership_fields SET show_on_members_list = 1 WHERE name IN ("' . implode('","', $defaultShowedFields) . '")';
$db->setQuery($sql);
$db->execute();
}
if (!in_array('fee_field', $fields)) {
$sql = "ALTER TABLE `#__osmembership_fields` ADD `fee_field` TINYINT NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('fee_values', $fields)) {
$sql = "ALTER TABLE `#__osmembership_fields` ADD `fee_values` TEXT NULL;";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('fee_formula', $fields)) {
$sql = "ALTER TABLE `#__osmembership_fields` ADD `fee_formula` VARCHAR( 255 ) NULL;";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('profile_field_mapping', $fields)) {
$sql = "ALTER TABLE `#__osmembership_fields` ADD `profile_field_mapping` VARCHAR( 50 ) NULL;";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('depend_on_field_id', $fields)) {
$sql = "ALTER TABLE `#__osmembership_fields` ADD `depend_on_field_id` INT NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('depend_on_options', $fields)) {
$sql = "ALTER TABLE `#__osmembership_fields` ADD `depend_on_options` TEXT NULL;";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('max_length', $fields)) {
$sql = "ALTER TABLE `#__osmembership_fields` ADD `max_length` INT NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('place_holder', $fields)) {
$sql = "ALTER TABLE `#__osmembership_fields` ADD `place_holder` VARCHAR( 255 ) NULL;";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('multiple', $fields)) {
$sql = "ALTER TABLE `#__osmembership_fields` ADD `multiple` TINYINT NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('validation_rules', $fields)) {
$sql = "ALTER TABLE `#__osmembership_fields` ADD `validation_rules` VARCHAR( 255 ) NULL;";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('validation_error_message', $fields)) {
$sql = "ALTER TABLE `#__osmembership_fields` ADD `validation_error_message` VARCHAR( 255 ) NULL;";
$db->setQuery($sql);
$db->execute();
}
$replace = false;
if (!in_array('fieldtype', $fields)) {
$replace = true;
$sql = "ALTER TABLE `#__osmembership_fields` ADD `fieldtype` VARCHAR( 50 ) NULL;";
$db->setQuery($sql);
$db->execute();
//Update field type , change it to something meaningful
$typeMapping = array(0 => 'Text', 1 => 'Textarea', 2 => 'List', 3 => 'Checkboxes', 4 => 'Radio', 5 => 'Date', 6 => 'Heading', 7 => 'Message', 9 => 'File');
foreach ($typeMapping as $key => $value) {
$sql = "UPDATE #__osmembership_fields SET fieldtype='{$value}' WHERE field_type='{$key}'";
$db->setQuery($sql);
$db->execute();
}
$sql = "UPDATE #__osmembership_fields SET fieldtype='List', multiple=1 WHERE field_type='8'";
$db->setQuery($sql);
$db->execute();
$sql = 'UPDATE #__osmembership_fields SET fieldtype="countries" WHERE name="country"';
$db->setQuery($sql);
$db->execute();
//MySql, convert data to Json
$sql = 'SELECT id, field_value FROM #__osmembership_field_value WHERE field_id IN (SELECT id FROM #__osmembership_fields WHERE field_type=3 OR field_type=8)';
$db->setQuery($sql);
$rowFieldValues = $db->loadObjectList();
if (count($rowFieldValues)) {
foreach ($rowFieldValues as $rowFieldValue) {
$fieldValue = $rowFieldValue->field_value;
if (strpos($fieldValue, ',') !== false) {
$fieldValue = explode(',', $fieldValue);
}
$fieldValue = json_encode($fieldValue);
$sql = 'UPDATE #__osmembership_field_value SET field_value=' . $db->quote($fieldValue) . ' WHERE id=' . $rowFieldValue->id;
$db->setQuery($sql);
$db->execute();
}
}
}
########1.6.3, migrate data to new fields API ###############################################
$sql = 'SELECT COUNT(*) FROM #__osmembership_fields';
$db->setQuery($sql);
$total = $db->loadResult();
if ($total) {
$sql = 'SELECT name, published FROM #__osmembership_fields WHERE is_core=1';
$db->setQuery($sql);
$coreFields = $db->loadObjectList('name');
}
if (!$total || $replace) {
$coreFieldsSql = JPATH_ADMINISTRATOR . '/components/com_osmembership/sql/fields.osmembership.sql';
$sql = JFile::read($coreFieldsSql);
$queries = $db->splitSql($sql);
if (count($queries)) {
foreach ($queries as $query) {
$query = trim($query);
if ($query != '' && $query[0] != '#') {
$db->setQuery($query);
$db->execute();
}
}
}
}
if ($replace && $total) {
foreach ($coreFields as $name => $field) {
$sql = 'UPDATE #__osmembership_fields SET published=' . (int) $field->published . ' WHERE name=' . $db->quote($name);
$db->setQuery($sql);
$db->execute();
}
}
$sql = "SELECT id, validation_rules FROM #__osmembership_fields WHERE required = 1";
$db->setQuery($sql);
$fields = $db->loadObjectList();
foreach ($fields as $field) {
if (empty($field->validation_rules)) {
$sql = 'UPDATE #__osmembership_fields SET validation_rules = "validate[required]" WHERE id=' . $field->id;
$db->setQuery($sql);
$db->execute();
}
}
// Allow access level for custom field
$fields = array_keys($db->getTableColumns('#__osmembership_fields'));
if (!in_array('access', $fields)) {
$sql = "ALTER TABLE `#__osmembership_fields` ADD `access` TINYINT NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
$sql = 'UPDATE #__osmembership_fields SET `access` = 1';
$db->setQuery($sql);
$db->execute();
}
####This code below is used for fixing the bugs in with not required fields in initial released of version 1.6.3##########
$sql = "SELECT id, validation_rules FROM #__osmembership_fields WHERE required = 0";
$db->setQuery($sql);
$fields = $db->loadObjectList();
foreach ($fields as $field) {
if ($field->validation_rules == 'validate[required]') {
$sql = 'UPDATE #__osmembership_fields SET validation_rules = "" WHERE id=' . $field->id;
$db->setQuery($sql);
$db->execute();
}
}
$fields = array_keys($db->getTableColumns('#__osmembership_categories'));
if (!in_array('access', $fields)) {
$sql = "ALTER TABLE `#__osmembership_categories` ADD `access` TINYINT NOT NULL DEFAULT '1';";
$db->setQuery($sql);
$db->execute();
$sql = 'UPDATE #__osmembership_categories SET `access`=1';
$db->setQuery($sql);
$db->execute();
}
if (!in_array('ordering', $fields)) {
$sql = "ALTER TABLE `#__osmembership_categories` ADD `ordering` INT NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
$sql = 'UPDATE #__osmembership_categories SET `ordering`=id';
$db->setQuery($sql);
$db->execute();
}
if (!in_array('alias', $fields)) {
$sql = "ALTER TABLE `#__osmembership_categories` ADD `alias` varchar(255) NOT NULL DEFAULT '';";
$db->setQuery($sql);
$db->execute();
$sql = 'SELECT id, title FROM #__osmembership_categories';
$db->setQuery($sql);
$rows = $db->loadObjectList();
if (count($rows)) {
foreach ($rows as $row) {
$alias = JApplication::stringURLSafe($row->title);
$sql = 'UPDATE #__osmembership_categories SET `alias`="' . $alias . '" WHERE id=' . $row->id;
$db->setQuery($sql);
$db->execute();
}
}
}
#Subscription plans table
$fields = array_keys($db->getTableColumns('#__osmembership_plans'));
if (!in_array('subscription_length_unit', $fields)) {
$sql = "ALTER TABLE `#__osmembership_plans` ADD `subscription_length_unit` CHAR(1) NULL;";
$db->setQuery($sql);
$db->execute();
//Need to update the length to reflect new unit
$sql = 'SELECT id, subscription_length FROM #__osmembership_plans';
$db->setQuery($sql);
$rowPlans = $db->loadObjectList();
for ($i = 0, $n = count($rowPlans); $i < $n; $i++) {
$rowPlan = $rowPlans[$i];
list($frequency, $length) = OSMembershipHelper::getRecurringSettingOfPlan($rowPlan->subscription_length);
$sql = 'UPDATE #__osmembership_plans SET subscription_length=' . (int) $length . ', subscription_length_unit="' . $frequency . '" WHERE id=' . $rowPlan->id;
$db->setQuery($sql);
$db->execute();
}
}
if (!in_array('access', $fields)) {
$sql = "ALTER TABLE `#__osmembership_plans` ADD `access` TINYINT NOT NULL DEFAULT '1';";
$db->setQuery($sql);
$db->execute();
$sql = 'UPDATE #__osmembership_plans SET `access`=1';
$db->setQuery($sql);
$db->execute();
}
if (!in_array('lifetime_membership', $fields)) {
$sql = "ALTER TABLE `#__osmembership_plans` ADD `lifetime_membership` TINYINT NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('expired_date', $fields)) {
$sql = "ALTER TABLE `#__osmembership_plans` ADD `expired_date` DATETIME NULL AFTER `price` ;";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('recurring_subscription', $fields)) {
$sql = "ALTER TABLE `#__osmembership_plans` ADD `recurring_subscription` TINYINT NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('enable_renewal', $fields)) {
$sql = "ALTER TABLE `#__osmembership_plans` ADD `enable_renewal` TINYINT NOT NULL DEFAULT '1';";
$db->setQuery($sql);
$db->execute();
$sql = 'UPDATE `#__osmembership_plans` SET `enable_renewal`=1 ';
$db->setQuery($sql);
$db->execute();
}
if (!in_array('trial_amount', $fields)) {
$sql = "ALTER TABLE `#__osmembership_plans` ADD `trial_amount` DECIMAL( 10, 2 ) NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('trial_duration', $fields)) {
$sql = "ALTER TABLE `#__osmembership_plans` ADD `trial_duration` INT NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('trial_duration_unit', $fields)) {
$sql = "ALTER TABLE `#__osmembership_plans` ADD `trial_duration_unit` CHAR(1) NULL;";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('number_payments', $fields)) {
$sql = "ALTER TABLE `#__osmembership_plans` ADD `number_payments` INT NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('subscription_complete_url', $fields)) {
$sql = "ALTER TABLE `#__osmembership_plans` ADD `subscription_complete_url` TEXT NULL ;";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('subscription_form_message', $fields)) {
$sql = "ALTER TABLE `#__osmembership_plans` ADD `subscription_form_message` TEXT NULL ;";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('category_id', $fields)) {
$sql = "ALTER TABLE `#__osmembership_plans` ADD `category_id` INT NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('alias', $fields)) {
$sql = "ALTER TABLE `#__osmembership_plans` ADD `alias` varchar(255) NOT NULL DEFAULT '';";
$db->setQuery($sql);
$db->execute();
$sql = 'SELECT id, title FROM #__osmembership_plans';
$db->setQuery($sql);
$rows = $db->loadObjectList();
if (count($rows)) {
foreach ($rows as $row) {
$alias = JApplication::stringURLSafe($row->title);
$sql = 'UPDATE #__osmembership_plans SET `alias`="' . $alias . '" WHERE id=' . $row->id;
$db->setQuery($sql);
$db->execute();
}
}
}
if (!in_array('tax_rate', $fields)) {
$sql = "ALTER TABLE `#__osmembership_plans` ADD `tax_rate` DECIMAL( 10, 2 ) NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
//Set tax rate for the plan from configuration
$taxRate = (double) OSMembershipHelper::getConfigValue('tax_rate');
if ($taxRate > 0) {
$sql = 'UPDATE #__osmembership_plans SET tax_rate=' . $taxRate;
$db->setQuery($sql);
$db->execute();
}
}
if (!in_array('notification_emails', $fields)) {
$sql = "ALTER TABLE `#__osmembership_plans` ADD `notification_emails` varchar(255) NOT NULL DEFAULT '';";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('paypal_email', $fields)) {
$sql = "ALTER TABLE `#__osmembership_plans` ADD `paypal_email` varchar(255) NOT NULL DEFAULT '';";
$db->setQuery($sql);
$db->execute();
}
//Change data type of short description to text, avoid support
$sql = 'ALTER TABLE `#__osmembership_plans` CHANGE `short_description` `short_description` MEDIUMTEXT NULL DEFAULT NULL';
$db->setQuery($sql);
$db->execute();
$sql = 'ALTER TABLE `#__osmembership_fields` CHANGE `description` `description` MEDIUMTEXT NULL DEFAULT NULL';
$db->setQuery($sql);
$db->execute();
#Subscribers table
$fields = array_keys($db->getTableColumns('#__osmembership_subscribers'));
if (!in_array('payment_made', $fields)) {
$sql = "ALTER TABLE `#__osmembership_subscribers` ADD `payment_made` INT NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('params', $fields)) {
$sql = "ALTER TABLE `#__osmembership_subscribers` ADD `params` TEXT NULL;";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('recurring_profile_id', $fields)) {
$sql = "ALTER TABLE `#__osmembership_subscribers` ADD `recurring_profile_id` varchar(255) NOT NULL DEFAULT '';";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('membership_id', $fields)) {
$sql = "ALTER TABLE `#__osmembership_subscribers` ADD `membership_id` INT NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
//Update membership Id field
$sql = 'SELECT id FROM #__osmembership_subscribers ORDER BY id';
$db->setQuery($sql);
$rows = $db->loadObjectList();
if (count($rows)) {
$start = 1000;
foreach ($rows as $row) {
$sql = 'UPDATE #__osmembership_subscribers SET membership_id=' . $start . ' WHERE id=' . $row->id;
$db->setQuery($sql);
$db->execute();
$start++;
}
}
}
if (!in_array('invoice_year', $fields)) {
$sql = "ALTER TABLE `#__osmembership_subscribers` ADD `invoice_year` INT NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
$sql = 'UPDATE #__osmembership_subscribers SET `invoice_year` = YEAR(`created_date`)';
$db->setQuery($sql);
$db->execute();
}
if (!in_array('is_profile', $fields)) {
$sql = "ALTER TABLE `#__osmembership_subscribers` ADD `is_profile` TINYINT NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
$sql = 'SELECT MIN(id) AS id FROM #__osmembership_subscribers WHERE user_id > 0 GROUP BY user_id';
$db->setQuery($sql);
$profileIds = $db->loadColumn();
if (count($profileIds)) {
$sql = 'UPDATE #__osmembership_subscribers SET is_profile=1 WHERE id IN (' . implode(',', $profileIds) . ')';
$db->setQuery($sql);
$db->execute();
}
$sql = 'SELECT MIN(id) AS id FROM #__osmembership_subscribers WHERE user_id = 0 AND is_profile=0 GROUP BY email';
$db->setQuery($sql);
$profileIds = $db->loadColumn();
if (count($profileIds)) {
$sql = 'UPDATE #__osmembership_subscribers SET is_profile=1 WHERE id IN (' . implode(',', $profileIds) . ')';
$db->setQuery($sql);
$db->execute();
}
}
if (!in_array('invoice_number', $fields)) {
$sql = "ALTER TABLE `#__osmembership_subscribers` ADD `invoice_number` INT NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
//Update membership Id field
$sql = 'SELECT id FROM #__osmembership_subscribers ORDER BY id';
$db->setQuery($sql);
$rows = $db->loadObjectList();
if (count($rows)) {
$start = 1;
foreach ($rows as $row) {
$sql = 'UPDATE #__osmembership_subscribers SET invoice_number=' . $start . ' WHERE id=' . $row->id;
$db->setQuery($sql);
$db->execute();
$start++;
}
}
}
if (!in_array('profile_id', $fields)) {
$sql = "ALTER TABLE `#__osmembership_subscribers` ADD `profile_id` INT NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
$sql = 'SELECT id, user_id, email FROM #__osmembership_subscribers WHERE is_profile=1';
$db->setQuery($sql);
$rowSubscribers = $db->loadObjectList();
if (count($rowSubscribers)) {
foreach ($rowSubscribers as $rowSubscriber) {
if ($rowSubscriber->user_id > 0) {
$sql = 'UPDATE #__osmembership_subscribers SET profile_id=' . $rowSubscriber->id . ' WHERE email=' . $db->quote($rowSubscriber->email) . ' OR user_id=' . $rowSubscriber->user_id;
} else {
$sql = 'UPDATE #__osmembership_subscribers SET profile_id=' . $rowSubscriber->id . ' WHERE email=' . $db->quote($rowSubscriber->email);
}
$db->setQuery($sql);
$db->execute();
}
}
}
if (!in_array('language', $fields)) {
$sql = "ALTER TABLE `#__osmembership_subscribers` ADD `language` VARCHAR( 10 ) NULL;";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('username', $fields)) {
$sql = "ALTER TABLE `#__osmembership_subscribers` ADD `username` VARCHAR( 50 ) NULL;";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('user_password', $fields)) {
$sql = "ALTER TABLE `#__osmembership_subscribers` ADD `user_password` VARCHAR(255) NULL;";
$db->setQuery($sql);
$db->execute();
}
if (!in_array('payment_processing_fee', $fields)) {
$sql = "ALTER TABLE `#__osmembership_subscribers` ADD `payment_processing_fee` DECIMAL( 10, 2 ) NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
}
#Payment Plugins table
$fields = array_keys($db->getTableColumns('#__osmembership_plugins'));
if (!in_array('support_recurring_subscription', $fields)) {
$sql = "ALTER TABLE `#__osmembership_plugins` ADD `support_recurring_subscription` TINYINT NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
}
$recurringSupportedPlugins = array('os_paypal', 'os_authnet');
$sql = 'UPDATE #__osmembership_plugins SET support_recurring_subscription=1 WHERE name IN ("' . implode('","', $recurringSupportedPlugins) . '")';
$db->setQuery($sql);
$db->execute();
$sql = 'SELECT COUNT(*) FROM #__osmembership_messages';
$db->setQuery($sql);
$total = $db->loadResult();
if (!$total) {
$pluginsSql = JPATH_ADMINISTRATOR . '/components/com_osmembership/sql/install.messages.sql';
$sql = JFile::read($pluginsSql);
$queries = $db->splitSql($sql);
if (count($queries)) {
foreach ($queries as $query) {
$query = trim($query);
if ($query != '' && $query[0] != '#') {
$db->setQuery($query);
$db->execute();
}
}
}
}
//Delete some files
if (JFolder::exists(JPATH_ROOT . '/administrator/components/com_osmembership/libraries/legacy')) {
JFolder::delete(JPATH_ROOT . '/administrator/components/com_osmembership/libraries/legacy');
}
if (JFile::exists(JPATH_ROOT . '/administrator/components/com_osmembership/libraries/factory.php')) {
JFile::delete(JPATH_ROOT . '/administrator/components/com_osmembership/libraries/factory.php');
}
$publishedItems = array('osmembership' => array('user', 'invoice'), 'system' => array('osmembershipreminder', 'osmembershipupdatestatus'));
foreach ($publishedItems as $folder => $plugins) {
foreach ($plugins as $plugin) {
$query = "SELECT COUNT(*) FROM #__extensions WHERE element=" . $db->Quote($plugin) . " AND folder=" . $db->Quote($folder);
$db->setQuery($query);
$count = $db->loadResult();
if ($count) {
$query = "UPDATE #__extensions SET enabled=1 WHERE element=" . $db->Quote($plugin) . " AND folder=" . $db->Quote($folder);
$db->setQuery($query);
$db->execute();
}
}
}
$sql = "CREATE TABLE IF NOT EXISTS `#__osmembership_sefurls` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `md5_key` text,\n `query` text,\n PRIMARY KEY (`id`)\n ) DEFAULT CHARSET=utf8;\n ";
$db->setQuery($sql);
$db->execute();
$db->truncateTable('#__osmembership_sefurls');
if (!in_array($prefix . 'osmembership_taxes', $tables)) {
// Tax rules table
$sql = "CREATE TABLE IF NOT EXISTS `#__osmembership_taxes` (\n\t\t `id` int(11) NOT NULL AUTO_INCREMENT,\n\t\t `plan_id` int(11) DEFAULT NULL,\n\t\t `country` varchar(255) DEFAULT NULL,\n\t\t `rate` decimal(10,2) DEFAULT NULL,\n\t\t `vies` tinyint(3) unsigned DEFAULT 0,\n\t\t `published` tinyint(3) unsigned DEFAULT 0,\n\t\t PRIMARY KEY (`id`)\n\t\t) DEFAULT CHARSET=utf8;\n\t\t";
$db->setQuery($sql);
$db->execute();
$sql = 'SELECT id, tax_rate FROM #__osmembership_plans WHERE tax_rate > 0';
$db->setQuery($sql);
$taxRates = $db->loadObjectList();
if (count($taxRates) > 0) {
foreach ($taxRates as $taxRate) {
$sql = "INSERT INTO #__osmembership_taxes(plan_id, country, rate, vies, published) VALUES({$taxRate->id}, '', {$taxRate->tax_rate}, 0, 1)";
$db->setQuery($sql);
$db->execute();
}
}
}
$fields = array_keys($db->getTableColumns('#__osmembership_taxes'));
if (!in_array('vies', $fields)) {
$sql = "ALTER TABLE `#__osmembership_taxes` ADD `vies` TINYINT NOT NULL DEFAULT '0';";
$db->setQuery($sql);
$db->execute();
}
}
/**
* Create an useraccount based on the entered data
*
* @param array $data
*
* @return number|boolean|mixed
*/
public static function saveRegistration($data)
{
//Need to load com_users language file
$lang = JFactory::getLanguage();
$tag = $lang->getTag();
if (!$tag) {
$tag = 'en-GB';
}
$lang->load('com_users', JPATH_ROOT, $tag);
$data['name'] = $data['first_name'] . ' ' . $data['last_name'];
$data['password'] = $data['password2'] = $data['password'] = $data['password1'];
$data['email1'] = $data['email2'] = $data['email'];
$sendActivationEmail = OSMembershipHelper::getConfigValue('send_activation_email');
if ($sendActivationEmail) {
require_once JPATH_ROOT . '/components/com_users/models/registration.php';
$model = new UsersModelRegistration();
$ret = $model->register($data);
} else {
$params = JComponentHelper::getParams('com_users');
$useractivation = $params->get('useractivation');
if ($useractivation == 1 || $useractivation == 2) {
$data['activation'] = JApplication::getHash(JUserHelper::genRandomPassword());
$data['block'] = 1;
}
$data['groups'] = array();
$data['groups'][] = $params->get('new_usertype', 2);
$user = new JUser();
if (!$user->bind($data)) {
die(JText::sprintf('COM_USERS_REGISTRATION_BIND_FAILED', $user->getError()));
return false;
}
// Store the data.
if (!$user->save()) {
die(JText::sprintf('COM_USERS_REGISTRATION_SAVE_FAILED', $user->getError()));
return false;
}
}
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('id')->from('#__users')->where('username='******'username']));
//Need to get the user ID based on username
$db->setQuery($query);
return (int) $db->loadResult();
}
