Ahmed Dirié https://ahmeddirie.com Ahmed Dirié is a developer who specializes in cloud based technologies, Force.com development, web application architecture, cloud server management, reporting and intelligence with OBIEE and SAP BO. Wed, 08 Feb 2017 21:16:02 +0000 en-US hourly 1 https://wordpress.org/?v=4.0.25 Migrating Drupal 7 Forums into an Adxstudio Portal in Microsoft Dynamics – Part 3 https://ahmeddirie.com/technology/web-development/migrating-drupal-7-forums-into-an-adxstudio-portal-in-microsoft-dynamics-part-3/ https://ahmeddirie.com/technology/web-development/migrating-drupal-7-forums-into-an-adxstudio-portal-in-microsoft-dynamics-part-3/#comments Mon, 28 Sep 2015 06:13:55 +0000 http://ahmeddirie.com/?p=4601 In this final post on migrating Drupal 7 forums to an Adxstudio portal in Microsoft Dynamics, we’ll look at creating entities and workflows in DCRM to update the posts related to a thread and the total counts. This takes place with the upload of the 4th XML script we talked about in both Part 1 and Part 2.

Entities

customizations-main

Before we can create a workflow, we’ll need to create a new entity, Forum Update Thread. Click on the Customize the System link and it should open up a new window that will show you the Information and Components of your DCRM environment. Within Components, you’ll want to go to Entities and then New.

Creating the Entity

entities

Once you get the New Entity screen, set the Display Name and Plural Name. In our case, it is Forum Thread Update and Forum Thread Updates. For the remaining areas, all you need is to make sure Duplicate detection is checked under Data Services. just select Notes and Mail merge.

entity-new

Click on Save to Create the Entity and after a minute or more, it should be done and will appear in your Entities List window.

Creating Fields for the Entity

entity-fields

We need to create the fields that will capture the data we’re setting in the XML (script 4) file. We need the following fields.

1- Forum Thread

Display: Forum Thread
Field Requirement: Business Required
Searchable: Yes
Field Security: Disable
Auditing: Enable
Data Type: Lookup
Target Record Type: Forum Thread

2- First Post

Display: First Post
Field Requirement: Business Required
Searchable: Yes
Field Security: Disable
Auditing: Enable
Data Type: Lookup
Target Record Type: Forum Thread

3- Last Post

Display: Last Post
Field Requirement: Optional
Searchable: Yes
Field Security: Disable
Auditing: Enable
Data Type: Lookup
Target Record Type: Forum Thread

4- Post Count

Display: Post Count
Field Requirement: Business Required
Searchable: No
Field Security: Disable
Auditing: Enable
Data Type: Whole Number
Field Type: Simple

Once you’re done, make sure to Publish your customizations.

Processes and Workflows

processes_page

At this point, you should already have the 4th XML script, which is a Forum Thread Update (adx_forum_thread_update.php). We’ll now get to the Processes page in DCRM which is located in the Settings area. And we’ll start by creating a new process.

Creating a Workflow

create-process

Give it a name that you will remember and make sure to set the Category to Workflow and the Entity to Forum Thread Update. Once you’ve created it, it should be in the Processes List and the Status will be Draft. If you don’t have a pop-up blocker enabled, you should see a new window that allows you to configure the new workflow. Otherwise, click on it in the Processes list to continue with configuration.

workflow-configure

In the Available to Run area, chose the “As an on-demand process” option. You can also set the Scope to Organization. From there, we’ll go to Add Steps in the lower part of the page. Choose Update Record and set the field to Forum Thread and click on Set Properties.

In this window, first start by clicking on the “First Post” field. On the Form Assistant to the right, in the “Look for” drop-down, set it to First Post (Forum Post), click Add and press OK. It should insert the value in the “First Post” field. Do the same for the Last Post. For Post Count, the Look for should be set to Forum Thread Update and in the drop-down below it, you’ll see Post Count.

workflow-properties

Save and it should take you back to the initial configuration screen. Click on Activate. In the Confirmation dialogue that appears, continue with Activate. You should be returned to the Processes screen and your new process should have a status of “Activated”.

Import the XML Script 4 (Forum Thread Update)

We’re going to import the last XML script and this time, we’ll be mapping it to the record type, Forum Thread Update. Once you get to the Field Mapping, it should look like this.

map-fields-forum-thread-update

Once you’re done the upload, go back to the Processes page and click on the Forum Thread Update process. When it opens up, go to the Process Sessions section and you’ll see the threads being updated one by one. When this finishes, you can go to your actual Forum and will see all the posts, comments and counts present.

process-sessions

One thing to keep in mind is that this won’t update the total Thread Count on the Forum’s properties in DCRM. You have this information in your XML Script 1 located at the bottom in the “Number” field. Enter that information in DCRM and that should complete the process.

Good luck with your new forums on Adxstudio!

]]> https://ahmeddirie.com/technology/web-development/migrating-drupal-7-forums-into-an-adxstudio-portal-in-microsoft-dynamics-part-3/feed/ 0 Migrating Drupal 7 Forums into an Adxstudio Portal in Microsoft Dynamics – Part 2 https://ahmeddirie.com/technology/web-development/migrating-drupal-7-forums-into-an-adxstudio-portal-in-microsoft-dynamics-part-2/ https://ahmeddirie.com/technology/web-development/migrating-drupal-7-forums-into-an-adxstudio-portal-in-microsoft-dynamics-part-2/#comments Fri, 25 Sep 2015 05:28:39 +0000 http://ahmeddirie.com/?p=4567 Continuing on from Part 1 of this post, we’re going to create the PHP scripts that will pull all of your threads, posts and comments into its own XML files. These files will then be imported into DCRM through the Import interface.

Excel XML Structure

The XML files have to be recognized as an Excel XML (XML Spreadsheet 2003) document. Otherwise, it just won’t work. So, we’ll go ahead and create this using some straightforward PHP. I’ve broken this up into multiple files, but you are able to do this in single files for each type (thread, post, comment, update). It’s set up so that once you visit the page through your browser, it automatically starts the download of the XML document.

The Header – header.php

This header is generic and you’ll be using it for all 4 scripts.

<?php
$xml_content = '<?xml version="1.0" encoding="utf-8"?>'."\xA";
$xml_content .= '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"'."\xA";
$xml_content .= 'xmlns:o="urn:schemas-microsoft-com:office:office"'."\xA";
$xml_content .= 'xmlns:x="urn:schemas-microsoft-com:office:excel"'."\xA";
$xml_content .= 'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"'."\xA";
$xml_content .= 'xmlns:html="http://www.w3.org/TR/REC-html40">'."\xA";
$xml_content .= '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">'."\xA";
$xml_content .= '<Author>'.$author.'</Author>'."\xA";
$xml_content .= '<LastAuthor>'.$author.'</LastAuthor>'."\xA";
$xml_content .= '<Created>'.date('Y-m-d').'T'.date('H:i:s').'Z</Created>'."\xA";
$xml_content .= '<Company>'.$company.'</Company>'."\xA";
$xml_content .= '<Version>14.0</Version>'."\xA";
$xml_content .= '</DocumentProperties>'."\xA";
$xml_content .= '<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">'."\xA";
$xml_content .= '<AllowPNG/>'."\xA";
$xml_content .= '</OfficeDocumentSettings>'."\xA";
$xml_content .= '<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">'."\xA";
$xml_content .= '<WindowHeight>15260</WindowHeight>'."\xA";
$xml_content .= '<WindowWidth>25600</WindowWidth>'."\xA";
$xml_content .= '<WindowTopX>14120</WindowTopX>'."\xA";
$xml_content .= '<WindowTopY>2480</WindowTopY>'."\xA";
$xml_content .= '<ProtectStructure>False</ProtectStructure>'."\xA";
$xml_content .= '<ProtectWindows>False</ProtectWindows>'."\xA";
$xml_content .= '</ExcelWorkbook>'."\xA";
$xml_content .= '<Styles>'."\xA";
$xml_content .= '<Style ss:ID="Default" ss:Name="Normal">'."\xA";
$xml_content .= '<Alignment ss:Vertical="Bottom"/>'."\xA";
$xml_content .= '<Borders/>'."\xA";
$xml_content .= '<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>'."\xA";
$xml_content .= '<Interior/>'."\xA";
$xml_content .= '<NumberFormat/>'."\xA";
$xml_content .= '<Protection/>'."\xA";
$xml_content .= '</Style>'."\xA";
$xml_content .= '</Styles>'."\xA";
$xml_content .= '<Worksheet ss:Name="Sheet1">'."\xA";
?>

The Footer – footer.php

Similar to the header, the footer is also generic. However, we are inserting the total number of records we obtained from the SQL query.

<?php
$xml_content .= '<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">'."\xA";
$xml_content .= '<Print>'."\xA";
$xml_content .= '<ValidPrinterInfo/>'."\xA";
$xml_content .= '<HorizontalResolution>-4</HorizontalResolution>'."\xA";
$xml_content .= '<VerticalResolution>-4</VerticalResolution>'."\xA";
$xml_content .= '</Print>'."\xA";
$xml_content .= '<PageLayoutZoom>0</PageLayoutZoom>'."\xA";
$xml_content .= '<Selected/>'."\xA";
$xml_content .= '<Panes>'."\xA";
$xml_content .= '<Pane>'."\xA";
$xml_content .= '<Number>'.$num_rows.'</Number>'."\xA";
$xml_content .= '<ActiveRow>2</ActiveRow>'."\xA";
$xml_content .= '<ActiveCol>1</ActiveCol>'."\xA";
$xml_content .= '</Pane>'."\xA";
$xml_content .= '</Panes>'."\xA";
$xml_content .= '<ProtectObjects>False</ProtectObjects>'."\xA";
$xml_content .= '<ProtectScenarios>False</ProtectScenarios>'."\xA";
$xml_content .= '</WorksheetOptions>'."\xA";
$xml_content .= '</Worksheet>'."\xA";
$xml_content .= '</Workbook>'."\xA";
echo $xml_content;
?>

The XML Scripts

We’re going to dive in a little deeper into the XML scripts we talked about in Part 1. We went over the initial layout and what data each one will have. Now, we’re going to build out each script along with its respective data.

XML Script 1 (Forum Threads) – adx_forum_threads.php

In this file, we’re running a query to pull all of the forum threads from the database. It’s grabbing the name of the thread and the last post date. We’re manually specifying the Forum that the thread belongs to, as well as the type of thread.

<?php
//Start the Table Header
$xml_content .= '<Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="65" ss:DefaultRowHeight="15">'."\xA";
$xml_content .= '<Row>'."\xA";
$xml_content .= '<Cell><Data ss:Type="String">Forum</Data></Cell>'."\xA";
$xml_content .= '<Cell><Data ss:Type="String">Last Post Date</Data></Cell>'."\xA";
$xml_content .= '<Cell><Data ss:Type="String">Name</Data></Cell>'."\xA";
$xml_content .= '<Cell><Data ss:Type="String">Type</Data></Cell>'."\xA";
$xml_content .= '</Row>'."\xA";

//Create Rows of Records
$query = 'SELECT Thread_Name, Last_Post_Date FROM forum_threads;';
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);
while ($row = mysql_fetch_object($result)) {
	$xml_content .= '<Row>'."\xA";
	$xml_content .= '<Cell><Data ss:Type="String">NAME OF YOUR FORUM IN DCRM</Data></Cell>'."\xA";
	$xml_content .= '<Cell><Data ss:Type="String">'.$row->Last_Post_Date.'</Data></Cell>'."\xA";
	$xml_content .= '<Cell><Data ss:Type="String">'."\xA";
	$xml_content .= '<![CDATA['."\xA";
	$xml_content .= $row->Thread_Name."\xA";
	$xml_content .= ']]>'."\xA";
	$xml_content .= '</Data></Cell>'."\xA";
	$xml_content .= '<Cell><Data ss:Type="String">NAME OF FORUM THREAD TYPE</Data></Cell>'."\xA";
	$xml_content .= '</Row>'."\xA";
}
mysql_close();

//End the Table
$xml_content .= '</Table>'."\xA";
?>

XML Script 2 (Forum Posts) – adx_forum_posts.php

With out threads set up, we now need the forum posts. We’ll running a query to pull all of the forum posts from the database. To be more specific, we’re actually pulling in the initial posts that were created for the thread. The way this works in Adxstudio is that when you are creating a new thread, it creates the thread record first and then adds the post (which is no different than a comment) to that thread.

So, we’re naming our first post the same as the Thread’s name. You’ll also notice that for the Author, we’re using the email address. This ensures that the post is assigned correctly (make sure the user already exists as a Contact), otherwise the import will fail if you happen to have two people with the exact same name.

<?php
//Start the Table Header
$xml_content .= '<Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="65" ss:DefaultRowHeight="15">'."\xA";
$xml_content .= '<Row>'."\xA";
$xml_content .= '<Cell><Data ss:Type="String">Author</Data></Cell>'."\xA";
$xml_content .= '<Cell><Data ss:Type="String">Date</Data></Cell>'."\xA";
$xml_content .= '<Cell><Data ss:Type="String">Forum Thread</Data></Cell>'."\xA";
$xml_content .= '<Cell><Data ss:Type="String">Name</Data></Cell>'."\xA";
$xml_content .= '<Cell><Data ss:Type="String">Content</Data></Cell>'."\xA";
$xml_content .= '</Row>'."\xA";

//Create Rows of Records
$query = 'SELECT Email_Address, Thread_Name, Last_Post_Date, Content FROM forum_threads;';
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);
while ($row = mysql_fetch_object($result)) {
	$xml_content .= '<Row>'."\xA";
	$xml_content .= '<Cell><Data ss:Type="String">'.$row->Email_Address.'</Data></Cell>'."\xA";
	$xml_content .= '<Cell><Data ss:Type="String">'.$row->Last_Post_Date.'</Data></Cell>'."\xA";
	$xml_content .= '<Cell><Data ss:Type="String">'."\xA";
	$xml_content .= '<![CDATA['."\xA";
	$xml_content .= $row->Thread_Name."\xA";
	$xml_content .= ']]>'."\xA";
	$xml_content .= '</Data></Cell>'."\xA";
	$xml_content .= '<Cell><Data ss:Type="String">'."\xA";
	$xml_content .= '<![CDATA['."\xA";
	$xml_content .= $row->Thread_Name."\xA";
	$xml_content .= ']]>'."\xA";
	$xml_content .= '</Data></Cell>'."\xA";
	$xml_content .= '<Cell><Data ss:Type="String">'."\xA";
	$xml_content .= '<![CDATA['."\xA";
	$xml_content .= nl2p($row->Content, false, true)."\xA";
	$xml_content .= ']]>'."\xA";
	$xml_content .= '</Data></Cell>'."\xA";
	$xml_content .= '</Row>'."\xA";
}
mysql_close();

//End the Table
$xml_content .= '</Table>'."\xA";
?>

XML Script 3 (Forum Comments) – adx_forum_comments.php

What’s a forum post without comments? Now, we’ll run another query to pull all of the forum comments from the database. In essence, this isn’t any different than the initial post. The main difference is time (since the comment came after the first post), and that we’ve given it a unique string as a Name.

<?php
//Start the Table Header
$xml_content .= '<Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="65" ss:DefaultRowHeight="15">'."\xA";
$xml_content .= '<Row>'."\xA";
$xml_content .= '<Cell><Data ss:Type="String">Author</Data></Cell>'."\xA";
$xml_content .= '<Cell><Data ss:Type="String">Date</Data></Cell>'."\xA";
$xml_content .= '<Cell><Data ss:Type="String">Forum Thread</Data></Cell>'."\xA";
$xml_content .= '<Cell><Data ss:Type="String">Name</Data></Cell>'."\xA";
$xml_content .= '<Cell><Data ss:Type="String">Content</Data></Cell>'."\xA";
$xml_content .= '</Row>'."\xA";

//Create Rows of Records
$query = 'SELECT Email_Address, Last_Post_Date, Thread_Name, unique_string, Content FROM forum_posts WHERE uid!=0;';
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);
while ($row = mysql_fetch_object($result)) {
	$xml_content .= '<Row>'."\xA";
	$xml_content .= '<Cell><Data ss:Type="String">'.$row->Email_Address.'</Data></Cell>'."\xA";
	$xml_content .= '<Cell><Data ss:Type="String">'.$row->Last_Post_Date.'</Data></Cell>'."\xA";
	$xml_content .= '<Cell><Data ss:Type="String">'."\xA";
	$xml_content .= '<![CDATA['."\xA";
	$xml_content .= $row->Thread_Name."\xA";
	$xml_content .= ']]>'."\xA";
	$xml_content .= '</Data></Cell>'."\xA";
	$xml_content .= '<Cell><Data ss:Type="String">'."\xA";
	$xml_content .= '<![CDATA['."\xA";
	$xml_content .= $row->unique_string."\xA";
	$xml_content .= ']]>'."\xA";
	$xml_content .= '</Data></Cell>'."\xA";
	$xml_content .= '<Cell><Data ss:Type="String">'."\xA";
	$xml_content .= '<![CDATA['."\xA";
	$xml_content .= nl2p($row->Content, false, true)."\xA";
	$xml_content .= ']]>'."\xA";
	$xml_content .= '</Data></Cell>'."\xA";
	$xml_content .= '</Row>'."\xA";
}
mysql_close();

//End the Table
$xml_content .= '</Table>'."\xA";
?>

XML Script 4 (Forum Thread Update) – adx_forum_thread_update.php

In this file, we’re running a query to pull all of the forum posts along with the first post, last post and post counts. Essentially, we’re going to “update” the threads with this information. It couldn’t be done in the beginning because the First and Last Post ID’s don’t exist – so this part would fail.

<?php
//Start the Table Header
$xml_content .= '<Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="65" ss:DefaultRowHeight="15">'."\xA";
$xml_content .= '<Row>'."\xA";
$xml_content .= '<Cell><Data ss:Type="String">Name</Data></Cell>'."\xA";
$xml_content .= '<Cell><Data ss:Type="String">Forum Thread</Data></Cell>'."\xA";
$xml_content .= '<Cell><Data ss:Type="String">First Post</Data></Cell>'."\xA";
$xml_content .= '<Cell><Data ss:Type="String">Last Post</Data></Cell>'."\xA";
$xml_content .= '<Cell><Data ss:Type="String">Post Count</Data></Cell>'."\xA";
$xml_content .= '</Row>'."\xA";

//Create Rows of Records
$query = 'SELECT Thread_Name, First_Post_Id, Last_Post_Id, Post_Count FROM forum_threads;';
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);
while ($row = mysql_fetch_object($result)) {
	$xml_content .= '<Row>'."\xA";
	
	$xml_content .= '<Cell><Data ss:Type="String">'."\xA";
	$xml_content .= '<![CDATA['."\xA";
	$xml_content .= $row->Thread_Name."\xA";
	$xml_content .= ']]>'."\xA";
	$xml_content .= '</Data></Cell>'."\xA";
	
	$xml_content .= '<Cell><Data ss:Type="String">'."\xA";
	$xml_content .= '<![CDATA['."\xA";
	$xml_content .= $row->Thread_Name."\xA";
	$xml_content .= ']]>'."\xA";
	$xml_content .= '</Data></Cell>'."\xA";
	
	$xml_content .= '<Cell><Data ss:Type="String">'."\xA";
	$xml_content .= '<![CDATA['."\xA";
	$xml_content .= $row->First_Post_Id."\xA";
	$xml_content .= ']]>'."\xA";
	$xml_content .= '</Data></Cell>'."\xA";
	
	$xml_content .= '<Cell><Data ss:Type="String">'.$row->Last_Post_Id.'</Data></Cell>'."\xA";
	$xml_content .= '<Cell><Data ss:Type="String">'.$row->Post_Count.'</Data></Cell>'."\xA";
	$xml_content .= '</Row>'."\xA";
}
mysql_close();

//End the Table
$xml_content .= '</Table>'."\xA";
?>

Note: This is the last script we’re going to import. However, a workflow has to be set up. This will be covered in Part 3.

Bringing it all together

Now that we have our header, footer and 4 different files, we just need to write a PHP script that will call each of the 4 scripts individually. I’ll use the threads as an example.

Forum Threads XML – adx_forum_threads_xml.php

The only thing to keep note of here is the file. Make sure you set the right name for your filename. It makes it easier to know what each XML file is later. You’ll notice that we added time to the filename. It keeps the filename unique in the event you were to download this more than once.

<?php
$current_time = date('h_i_s');
$filename = "adx_forum_threads_".$current_time.".xml";
header('Content-type: text/xml');
header('Content-Disposition: attachment; filename="'.$filename.'"');
$author = 'YOUR NAME';
$company = 'YOUR COMPANY NAME';

//Changes new lines to paragraphs
//Found on Stack Overflow - http://stackoverflow.com/questions/7409512/new-line-to-paragraph-function
function nl2p($string, $line_breaks = true, $xml = true) {

	$string = str_replace(array('<p>', '</p>', '<br>', '<br />'), '', $string);

	// It is conceivable that people might still want single line-breaks
	// without breaking into a new paragraph.
	if ($line_breaks == true)
		return '<p>'.preg_replace(array("/([\n]{2,})/i", "/([^>])\n([^<])/i"), array("</p>\n<p>", '$1<br'.($xml == true ? ' /' : '').'>$2'), trim($string)).'</p>';
	else
		return '<p>'.preg_replace(
				array("/([\n]{2,})/i", "/([\r\n]{3,})/i","/([^>])\n([^<])/i"),
				array("</p>\n<p>", "</p>\n<p>", '$1<br'.($xml == true ? ' /' : '').'>$2'),

				trim($string)).'</p>';
}

require_once 'header.php';
require_once 'adx_forum_threads.php';
require_once 'footer.php';
?>

Running the Import

We’ll start by going to the Forums section in DCRM and then clicking on Import Data.

top-forums

This should bring up an Upload dialogue window. Select your XML script, e.g. forum_threads, and start the upload. Once the upload completes, you should see the following dialogue window.

review-file-upload

Once you press Next, you’ll get the following dialogue window asking you to Select a Data Map. If this is your first import, stick with the default and choose Automatic Mapping. You’ll get the option to set the mapping of the data yourself. Once you’re complete, you can save this Data Map for future use so you don’t have to go through the mapping process again.

select-data-map

The next dialogue window will more than likely tell you it couldn’t determine what CRM Record type you’re going to be importing the forum threads to.

map-record-types

From the drop down list, choose Forum Thread and proceed to the next dialogue which should have your columns in your XML file mapped to the field in the DCRM record.

map-fields

You’re now done with mapping the fields in the DCRM record type and can start importing your data. Make sure to go through the same steps with XML Scripts 1, 2 and 3. We’re skipping 4 as that involves a few extra steps.

Modifying the Mapping of the Author

In the XML Scripts for 2 and 3 (threads and posts), we’ve set up our script to use an email address since this is more unique than a full name. If you stick to using a full name and you have two contacts by the name of “John Doe”, the import script will fail because it won’t know who to associate the forum post to.

In this case, you simply change the mapping of the Author to the email address in the Contact record.

map-author

What to Expect in Part 3

In Part 3, we’ll go over creating Entities and Workflows within DCRM so that we could update the values on the Forum Thread (XML Script 4). This part is essential as it ties in the post with it’s comments.

]]> https://ahmeddirie.com/technology/web-development/migrating-drupal-7-forums-into-an-adxstudio-portal-in-microsoft-dynamics-part-2/feed/ 0 Migrating Drupal 7 Forums into an Adxstudio Portal in Microsoft Dynamics – Part 1 https://ahmeddirie.com/technology/web-development/migrating-drupal-7-forums-into-an-adxstudio-portal-in-microsoft-dynamics-part-1/ https://ahmeddirie.com/technology/web-development/migrating-drupal-7-forums-into-an-adxstudio-portal-in-microsoft-dynamics-part-1/#comments Mon, 21 Sep 2015 21:41:17 +0000 http://ahmeddirie.com/?p=4545 I’ve been meaning to write this post for a while. Making the move to DCRM can be time consuming and much consideration is needed on how to get your data into the system. If you’ve been looking around online, you’ve probably discovered that there are a few ways where large sets of data can be imported. I’ve looked at many resources that talk about how you can get data in, and I have to admit, some are super confusing and most only cover one tiny section. As useful as they were, you never get a complete picture.

In this three part post, I’ll go over how you can get your forum posts from Drupal 7, prepare it and get it imported into DCRM.

Getting the Forum Data from Drupal

Step 1 of course is the actual forum posts. I’m making the assumption that you are using the standard forum part of Drupal. If you’re not, the principal is the same. You can tweak the code to suit your environment. We’re going to start by getting the latest forum threads from database using an SQL query. This will include the user’s information, the forum post title, the content and the date the post was created.

SELECT n.nid AS nid, n.uid AS uid, u.mail AS 'Email_Address', n.title AS `Thread_Name`, b.body_value AS Content, n.created AS `Unix_Post_Date`
FROM node n, field_revision_body b, users u 
WHERE type="forum" AND n.nid = b.entity_id AND n.uid = u.uid;

Now, armed with your forum posts, you’ll want to store this into a separate table. Preferably, you would move this into a separate database so that we don’t interfere with the live site.

Note: I did mention that we’ll be covering the process from end to end. However, I’m assuming that if you’ve been tasked with this job, you at least know how to copy the output you got from the last query into another table. I’ve provided the Table Creation script in the next section to ease the pain. All you have to do is export/import your data.

The next set of data we need is the comments that belong to each of these posts. What is a forum without comments?

SELECT c.cid AS cid, c.nid AS nid, c.uid AS uid, u.mail AS 'Email_Address', c.created AS `Unix_Post_Date`, n.title AS `Thread_Name`, c.subject AS `Re_Thread_Name`, b.comment_body_value AS Content 
FROM `comment` c, `node` n, `field_data_comment_body` b, `users` u 
WHERE c.cid = b.entity_id AND c.nid = n.nid AND c.uid = u.uid;

Now, similar to the first output, let’s do the same with comments and store this in a separate table as well.

Storing the Threads and Posts

This is the SQL script you’ll need to create the two new tables used to store the data you obtained in the last section.

Creating the Threads Table

You’ll notice that we have four extra columns we’re creating called, “Last Post Date, First Post Id, Last Post Id, Post Count”. We’re going to need these in the following section.

CREATE TABLE `forum_threads` (
  `nid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) DEFAULT NULL,
  `Email_Address` varchar(255) NOT NULL DEFAULT '',
  `Thread_Name` varchar(255) NOT NULL DEFAULT '',
  `Content` longtext NOT NULL,
  `Unix_Post_Date` int(11) DEFAULT NULL,
  `Last_Post_Date` datetime DEFAULT NULL,
  `First_Post_Id` varchar(255) DEFAULT NULL,
  `Last_Post_Id` varchar(255) DEFAULT NULL,
  `Post_Count` int(11) DEFAULT NULL,
  PRIMARY KEY (`nid`)
) ENGINE=InnoDB AUTO_INCREMENT=1012539 DEFAULT CHARSET=utf8;

Creating the Posts Table

Like the Threads table, we have two more columns we’re creating called, “Last Post Date, Unique String”.

CREATE TABLE `forum_posts` (
  `cid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nid` int(10) unsigned NOT NULL,
  `uid` int(11) DEFAULT NULL,
  `Email_Address` varchar(255) NOT NULL,
  `Unix_Post_Date` int(11) NOT NULL,
  `Last_Post_Date` datetime DEFAULT NULL,
  `Thread_Name` varchar(255) NOT NULL DEFAULT '',
  `Re_Thread_Name` varchar(255) NOT NULL,
  `Content` longtext,
  `unique_string` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=1003445 DEFAULT CHARSET=utf8;

Preparing the Data

Of the ways that data can be imported into DCRM, we’re going to look at one of the simpler ways which is to use XML formatted as Excel XML documents. This should make the process easier and allows you to use the Import feature in the DCRM site and allow you to map your data to the end object.

Before we can continue ahead, we’ll need to fill in the new (NULL filled) columns we created earlier. We’ll start with the modifying the date of the post so that it is in a format that DCRM understands. You can use any language, but we’ll show you how to modify this using PHP.

Updating the Last Post Date in the Forum Threads table

<?php
$query = 'SELECT nid, Unix_Post_Date FROM forum_threads;';
$result = mysql_query($query);
while ($row = mysql_fetch_object($result)) {
	
	$updated_date = gmdate("Y-m-d\TH:i:s.000", $row->Unix_Post_Date);

	$query2 = 'UPDATE forum_threads SET Last_Post_Date="'.$updated_date.'" WHERE nid="'.$row->nid.'";';
	mysql_query($query2);
}
?>

Updating the Last Post Date in the Forum Posts table

<?php
$query = 'SELECT cid, Unix_Post_Date FROM forum_posts;';
$result = mysql_query($query);
while ($row = mysql_fetch_object($result)) {
	
	$updated_date = gmdate("Y-m-d\TH:i:s.000", $row->Unix_Post_Date);

	$query2 = 'UPDATE forum_posts SET Last_Post_Date="'.$updated_date.'" WHERE cid="'.$row->cid.'";';
	mysql_query($query2);
}
?>

Creating a Unique ID for each Forum Post

With Adxstudio, the forum comments works in an interesting way. This is something you probably wouldn’t have to do with Drupal since it checks what the last comment to a post is based on time. This needs to be specified in Adxstudio. We could probably get away with just using the Drupal “cid” field and check which is the last and assign it to a post. I went ahead and created a brand new unique string to attribute each post to so that there’s no confusions with other content within DCRM that may or may not have this same ID.

This next part can literally be done any way you like. We’re just creating a function (or three) to pass back a unique string.

<?php

function generateRandomString($length = 60) {
	$characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
	$charactersLength = strlen($characters);
	$randomString = '';
	for ($i = 0; $i < $length; $i++) {
		$randomString .= $characters[mt_rand(0, $charactersLength - 1)];
	}
	
	return $randomString;
}

function microtime_int() {
	list($usec, $sec) = explode(" ", microtime());
	return ((int)$usec + (int)$sec);
}

function createRandom() {
	
	$randomString = generateRandomString();
	$currentTime = date('yYzmMdDhis');
	$microTime = microtime_int();
	
	$newRandom = $microTime.$randomString.$currentTime;
	
	$shuffled = str_shuffle($newRandom);
	
	return $shuffled;
}

?>

With that out of the way, we’ll need to now update each forum post with a unique string.

<?php

$query = 'SELECT cid FROM forum_posts;';
$result = mysql_query($query);

while ($row = mysql_fetch_object($result)) {
	$unique_id = createRandom();
	
	$query2 = 'UPDATE forum_posts SET unique_string="'.$unique_id.'" WHERE cid="'.$row->cid.'";';
	mysql_query($query2);
}

?>

Updating the First Post ID, Last Post ID and Post Count for each Forum Thread

This is the last part to preparing our data and wraps up the thread table with the remaining data. We’ll be setting the “First Post ID” as the title of the thread, “Last Post ID” as the unique string of the last comment to the post, and the total comments for the post which includes the initial post. This means that if there are 11 comments, the total post count should be 12 as this includes the original thread’s post.

<?php

$query = 'SELECT nid, Thread_Name FROM forum_threads;';
$result = mysql_query($query);
while ($row = mysql_fetch_object($result)) {
	
	$query2 = 'SELECT unique_string FROM forum_posts WHERE nid="'.$row->nid.'" ORDER BY cid DESC;';
	$result2 = mysql_query($query2);
	$row2 = mysql_fetch_object($result2);
	
	for ($x = 0; $x < 1; $x++) {
		$unique_string = $row2->unique_string;
	}
	
	$total_rows = mysql_num_rows($result2);
	$total_rows++;
	
	$query3 = 'UPDATE forum_threads SET First_Post_Id="'.$row->Thread_Name.'", Last_Post_Id="'.$row2->unique_string.'", Post_Count="'.$total_rows.'" WHERE nid="'.$row->nid.'";';
	mysql_query($query3);
}

?>

Structure of the XML Import Scripts

In this section, we’re going to break up the data in both tables into 4 separate XML scripts.

XML Script 1 – Forum Threads

This is the first part that gets uploaded into DCRM. You’ll notice that no IDs are used. As such, the “Thread Name” needs to be unique. You’ll notice that we’re only listing the thread name and not the actual content yet.

<Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="65" ss:DefaultRowHeight="15">
<Row>
<Cell><Data ss:Type="String">Forum</Data></Cell>
<Cell><Data ss:Type="String">Last Post Date</Data></Cell>
<Cell><Data ss:Type="String">Name</Data></Cell>
<Cell><Data ss:Type="String">Type</Data></Cell>
</Row>
</Table>

XML Script 2 – Forum Posts

The initial content for a thread is a “Post” in DCRM. This is the second thing we import. One thing you’ll need to ensure is that you have all the author’s contact record already in DCRM. For the author field, use their email address as this is unique. For the Forum Thread field, this is referring to the “Name” field in the previous step. The “Name” field can be a unique ID as in the database.

<Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="65" ss:DefaultRowHeight="15">
<Row>
<Cell><Data ss:Type="String">Author</Data></Cell>
<Cell><Data ss:Type="String">Date</Data></Cell>
<Cell><Data ss:Type="String">Forum Thread</Data></Cell>
<Cell><Data ss:Type="String">Name</Data></Cell>
<Cell><Data ss:Type="String">Content</Data></Cell>
</Row>
</Table>

Note: Since this is XML data being imported, you’ll want to use CDATA around the content fields.

<![CDATA[  ]]>

XML Script 3 – Forum Comments

This XML is exactly like the Posts. The “Name” field will also be a unique ID as in the database. You’ll need to reference these in a fourth script where you update the main thread with first and last ID.

<Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="65" ss:DefaultRowHeight="15">
<Row>
<Cell><Data ss:Type="String">Author</Data></Cell>
<Cell><Data ss:Type="String">Date</Data></Cell>
<Cell><Data ss:Type="String">Forum Thread</Data></Cell>
<Cell><Data ss:Type="String">Name</Data></Cell>
<Cell><Data ss:Type="String">Content</Data></Cell>
</Row>
</Table>

XML Script 4 – Updating the Initial Forum Thread

For everything to work in harmony, your threads have to know what the ID of the first post and last post is, as well as the total number of posts. Once you have this information, the XML structure for this is below.

<Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="65" ss:DefaultRowHeight="15">
<Row>
<Cell><Data ss:Type="String">Name</Data></Cell>
<Cell><Data ss:Type="String">Forum Thread</Data></Cell>
<Cell><Data ss:Type="String">First Post</Data></Cell>
<Cell><Data ss:Type="String">Last Post</Data></Cell>
<Cell><Data ss:Type="String">Post Count</Data></Cell>
</Row>
</Table>

In Part 2 of this post, I’ll cover how you can get your forum data into this XML format and get it imported into DCRM.

]]> https://ahmeddirie.com/technology/web-development/migrating-drupal-7-forums-into-an-adxstudio-portal-in-microsoft-dynamics-part-1/feed/ 0 Sweet Potato & Smoked Salmon Egg Quiches https://ahmeddirie.com/media/videos/sweet-potato-smoked-salmon-egg-quiches/ https://ahmeddirie.com/media/videos/sweet-potato-smoked-salmon-egg-quiches/#comments Tue, 03 Mar 2015 20:31:05 +0000 http://ahmeddirie.com/?p=4363

Meal prep the perfect post workout kick that includes protein, complex carbs and fiber. It’s super healthy and can be done in 30 minutes flat.

In this video, I’ll show you how to quickly put together an awesome egg quiche with sweet potatoes, salmon and vegetables.

For more awesome meals and drinks, join Tackle the Kitchen at:

http://instagram.com/TackleTheKitchen
http://facebook.com/TackleTheKitchen
http://twitter.com/ahmeddirie
http://youtube.com/channel/UCEqDmDL3PcnvnofRmUO5Ylg

]]> https://ahmeddirie.com/media/videos/sweet-potato-smoked-salmon-egg-quiches/feed/ 0 Spinach and Banana Berry Smoothie https://ahmeddirie.com/media/videos/spinach-and-banana-berry-smoothie/ https://ahmeddirie.com/media/videos/spinach-and-banana-berry-smoothie/#comments Tue, 17 Feb 2015 17:55:47 +0000 http://ahmeddirie.com/?p=4359

Drinking a green smoothie loaded with spinach, bananas, strawberries, blueberries, black berries, raspberries, pomegranate and moringa powder.

For more awesome meals and drinks, join Tackle the Kitchen at:

http://instagram.com/TackleTheKitchen
http://facebook.com/TackleTheKitchen
http://twitter.com/ahmeddirie
http://youtube.com/channel/UCEqDmDL3PcnvnofRmUO5Ylg

]]> https://ahmeddirie.com/media/videos/spinach-and-banana-berry-smoothie/feed/ 0