TOPIC: jnews with DTMyScript

//First we i´ve created on two new fields (checkbox) in DT Fieldmanager (filednames nl1 and nl2)

// We only can grab $user->confirmNum Var
// so we have to get all data by using this var asking jos_dtregister_user for the email adress
// for testing $user->confirmNum = "111111"; 

$db = JFactory::getDBO();
$query = "SELECT userId FROM jos_dtregister_user WHERE confirmNum = '$user->confirmNum'";
$db->setQuery($query);
$result = $db->loadObjectList();
$user_id = $result[0]->userId;

// get email by userId
$db = JFactory::getDBO();
$query = "SELECT value FROM jos_dtregister_user_field_values WHERE user_Id = '$user_id' AND field_id = '10'";
$db->setQuery($query);
$result = $db->loadObjectList();
$useremail = $result[0]->value;

// get name by userId
$db = JFactory::getDBO();
$query = "SELECT value FROM jos_dtregister_user_field_values WHERE user_Id = '$user_id' AND field_id = '3'";
$db->setQuery($query);
$result = $db->loadObjectList();
$username = $result[0]->value;

// get nl1 field by userId --> check your field_id
$db = JFactory::getDBO();
$query = "SELECT value FROM jos_dtregister_user_field_values WHERE user_Id = '$user_id' AND field_id = '14'";
$db->setQuery($query);
$result = $db->loadObjectList();
$usernl1 = $result[0]->value;

// get nl2 field by userId --> check your field_id
$db = JFactory::getDBO();
$query = "SELECT value FROM jos_dtregister_user_field_values WHERE user_Id = '$user_id' AND field_id = '15'";
$db->setQuery($query);
$result = $db->loadObjectList();
$usernl2 = $result[0]->value;

// if you have more fields for jnews lists you can add them here


// Timestamp
$date_aktuell = time();

// -----------------------


// check if there is a record with this email in jos_jnews_subscribers

if ($usernl1 == "0" || $usernl2 == "0") {
	$db = JFactory::getDBO();
	$query = "SELECT value FROM jos_jnews_subscribers WHERE email = '$useremail'";
	$db->setQuery($query);
	$result = $db->loadObjectList();
	
// if there is none - create one
	
	if (empty($result)){
		$query = "INSERT INTO jos_jnews_subscribers (name, email, receive_html, confirmed, subscribe_date) VALUES ('$username','$useremail','1','1','$date_aktuell')";
		$db->setQuery($query);
		$db->query();

// for the next step we need the id		
		$query = "SELECT id FROM jos_jnews_subscribers WHERE email = '$useremail'";
		$db->setQuery($query);
		$result = $db->loadObjectList();
		$s_id = $result[0]->id;		
// now we need to create the entrys in jos_jnews_listssubscribers if newsletter 1 was checked 			
		if ($usernl1 == "0") {
			$query = "INSERT INTO jos_jnews_listssubscribers (list_id, subscriber_id, subdate, unsubdate, unsubscribe) VALUES ('17','$s_id','$date_aktuell','0','0')";
			$db->setQuery($query);
			$db->query();
			} 
// now we need to create the entrys in jos_jnews_listssubscribers if newsletter 1 was checked 			
		if ($usernl2 == "0") {
			$query = "INSERT INTO jos_jnews_listssubscribers (list_id, subscriber_id, subdate, unsubdate, unsubscribe) VALUES ('18','$s_id','$date_aktuell','0','0')";
			$db->setQuery($query);
			$db->query();
			}
		}
		else 
		{
// if there is already a subscriber with this email we only need to update the entrys in jos_jnews_listssubscribers
// there is no way to unssubscribe from a list when going through the registration			
		
			$query = "SELECT id FROM jos_jnews_subscribers WHERE email = '$useremail'";
			$db->setQuery($query);
			$result = $db->loadObjectList();
			$s_id = $result[0]->subscriber_id;
			
			if ($usernl1 == "0") {
				$query = "UPDATE jos_jnews_listssubscribers SET list_id = '17', subscriber_id = '$s_id', subdate = '$date_aktuell', unsubdate = '0', unsubscribe = '0'";
				$db->setQuery($query);
				$db->query();
			}
			if ($usernl2 == "0") {
				$query = "UPDATE jos_jnews_listssubscribers SET list_id = '18', subscriber_id = '$s_id', subdate = '$date_aktuell', unsubdate = '0', unsubscribe = '0'";
				$db->setQuery($query);
				$db->query();
			}
		}
	}
	
// done