Some Hardware Guy

Aras Innovator Import Orcad CIS BOM

2012-01-18T12:15:00.000-08:00

As I've been saying this is definitely a hack way of doing things, but it works. I'm sure there's a better way and if you make one let me know ;) Alright the goal in this section is to go into Orcad Capture, generate a BOM and import it into Innovator. So let's start with Orcad.

Go into Capture like you usually would and generate a CIS Bill Of materials. The only things you want in the BOM are:

Item Number
Quantity
PART_NUMBER
Part Reference

When you click OK you should get a csv file with all of your data in it. That looks something like this:

This is the file we'll load into Innovator. Make sure you close this file in excel because I had trouble opening it in innovator if it was already open in another program.

The Form
In a previous post I showed how I created a new item type CIP LOADED BOM to store this information. Now we need to go modify the form associated with this item, because that is where we'll import this file. This is kind of a hack way of doing things but at least you can see what I did and do it better on your system.

Go to Administration->Forms and find the form LOADED BOMs form that we created in the last post.

Modifying this form is just like using any gui editor, you can add fields, select, boxes, etc.

Text Fields
This is mostly for my own debug but I added four text fields for each of the columns in the CIP LOADED BOM. You can see above where I set the data source for the partID field to partID. Now when you select a row from the CIP LOADED BOM it will populate with that rows data, and you can edit it if you want to. I'll probably disable this for regular users.

Form Events
There are two form events that I used, onLoad and onResize. onLoad runs every time the form loads and gets the Innovator parts for the Assembly Part Number select box. onResize runs whenever you resize the form, it will resize the html2 element that holds the table of CIP parts in it.

Form onResize

document.getElementById("MyGrid").style.width = document.body.clientWidth - 100;

Notice that we're switching over to javascript here. All the forms are just html documents and all the active elements are done in javascript. This script just re-sizes the form whenever the window resizes.

Form onLoad
The user needs to be able to choose which Innovator part to associate this Orcad BOM with. So when the page loads I look up all the parts that are not released and put them in a select box so they can pick one. My basic assumption is you can only load a CIP Bom onto a part that is unreleased, and that part is only used for holding CIP BOM data and nothing else.

var item = document.thisItem;
// Set up the query Item.  Get all the most current part numbers that are not released
var qryItem = item.newItem("Part","get");
qryItem.loadAML(
"<Item type='Part' action='get' select='item_number,description,cost'>" +
"<item_number condition='like'>*</item_number>" +
"<is_released condition='eq'>0</is_released>" +
"<is_current condition='eq'>1</is_current>" +
"</Item>"
);
/*qryItem.loadAML(
"<Item type='Part' action='get'>" + 
"<generation condition='in'>SELECT MAX(generation) FROM [PART] WHERE is_released='0' AND is_current='1'</generation>" + 
"</Item>"
);
*/

// Perform the query.
var results = qryItem.apply();
// Test for an error.
if (results.isError()) {
top.aras.AlertError("No items maybe? Item not found: " + results.getErrorDetail());
return;
}
//debugger;
//loop through the returned items
var count = results.getItemCount();
//var select = document.getElementById("assemblyPart");
var select = document.getElementsByName("assemblyPart")[0];
select.options.length = 0;




for (i=0; i<count; ++i) {
     var rItem = results.getItemByIndex(i);
     //select.options.add(new Option(rItem.getProperty("id"), rItem.getProperty("item_number"));
     //select.options.add("Test", "test");
     select.options[select.options.length] = new Option(rItem.getProperty("item_number"), rItem.getProperty("id"));

}

The code above uses the loadAML function to create a query to get the most current version of all parts, that are also unreleased. This way once a user has uploaded and released a bom part they can only change it buy increasing the rev. That's my idea anyway. The next part runs the query and throws up a dialog box if it finds no parts to use. Finally we get how many results the query returned and stuff all those results into our select box.

Html Table
I thought it would be a good idea to first load the .csv bom file from Capture, and show it back to the user for review before we save it. That way if there's some corruption or something, you at least get a chance to cancel. The first step to that is adding an html field to the form, and putting this html code into it:

<script type="text/javascript">
top.aras.uiAddConfigLink2Doc4Assembly(document, "TreeTable");

</script>
<object id="MyGrid"
style="width:600px;height:500px;"classid="../cbin/TreeTable.dll#Aras.Client.Controls.GridContainer">
</object>

Note the object id of MyGrid, it will be used to populate the grid in the next step.

Load CSV Button
To add code to the load button I just defined a onClick function under Field Events. Then I used this javascript.

var vault = top.aras.vault;
var fileName = vault.SelectFile();
strData = vault.ReadText(fileName);
//alert(strData);



        strDelimiter = ",";

        // Create a regular expression to parse the CSV values.
        var objPattern = new RegExp(
                (
                        // Delimiters.
                        "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +

                        // Quoted fields.
                        "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +

                        // Standard fields.
                        "([^\"\\" + strDelimiter + "\\r\\n]*))"
                ),
                "gi"
                );


        // Create an array to hold our data. Give the array
        // a default empty first row.
        var arrData = [[]];

        // Create an array to hold our individual pattern
        // matching groups.
        var arrMatches = null;


        // Keep looping over the regular expression matches
        // until we can no longer find a match.
        while (arrMatches = objPattern.exec( strData )){

                // Get the delimiter that was found.
                var strMatchedDelimiter = arrMatches[ 1 ];

                // Check to see if the given delimiter has a length
                // (is not the start of string) and if it matches
                // field delimiter. If id does not, then we know
                // that this delimiter is a row delimiter.
                if (
                        strMatchedDelimiter.length &&
                        (strMatchedDelimiter != strDelimiter)
                        ){

                        // Since we have reached a new row of data,
                        // add an empty row to our data array.
                        arrData.push( [] );

                }


                // Now that we have our delimiter out of the way,
                // let's check to see which kind of value we
                // captured (quoted or unquoted).
                if (arrMatches[ 2 ]){

                        // We found a quoted value. When we capture
                        // this value, unescape any double quotes.
                        var strMatchedValue = arrMatches[ 2 ].replace(
                                new RegExp( "\"\"", "g" ),
                                "\""
                                );

                } else {

                        // We found a non-quoted value.
                        var strMatchedValue = arrMatches[ 3 ];

                }


                // Now that we have our value string, let's add
                // it to the data array.
                arrData[ arrData.length - 1 ].push( strMatchedValue );
        }

window.arrData = arrData;

        // Return the parsed data.
        //alert( arrData );
var item = document.thisItem;
// Get the relationships
var results = item.newItem("CIP LOADED BOM");
//debugger;
test = item.getNewID()
results.setID(test);
//debugger;
//qry.setAttribute("select", "quantity,related_id(item_number,name,cost)");
results.setProperty("quantity", "200");


//prepare the grid
document.getElementById("MyGrid").style.width = document.body.clientWidth - 100;

var propNameArr = new Array("quantity");
var gridXml =
"<table editable='false' draw_grid='true' justify='true'>" +
"<columns>" +
"<column width='10%' align='left' />" +
"<column width='10%' align='left' />" +
"<column width='30%' align='right' />" +
"<column width='50%' align='right' />" +
"</columns>" +
"<thead>" +
"<th>Item</th>" +
"<th>Quantity</th>" +
"<th>Part Number</th>" +
"<th>Reference Designator</th>" +
"</thead>" +
"</table>";
var inn = item.getInnovator();
var gridDom = inn.newXMLDocument();
gridDom.loadXML(gridXml);
var tableNd = gridDom.selectSingleNode("/table");


var c = arrData.length;
for (var i=0; i<c; ++i) {
//var bom = results.getItemByIndex(i);
//var part = bom.getRelatedItem();
var trNd = gridDom.createElement("tr");

trNd.setAttribute("id", i);
var tdNd;
tdNd = gridDom.createElement("td");
tdNd.text = arrData[i][0];
trNd.appendChild(tdNd);

tdNd = gridDom.createElement("td");
tdNd.text = arrData[i][1];
trNd.appendChild(tdNd);

tdNd = gridDom.createElement("td");
tdNd.text = arrData[i][2];
trNd.appendChild(tdNd);

tdNd = gridDom.createElement("td");
tdNd.text = arrData[i][3];
trNd.appendChild(tdNd);

tableNd.appendChild(trNd);
}
document.MyGrid.InitXml(gridDom.xml);

The very first part of this code opens a file explorer dialog box to let the user choose the file they want to upload. The next section is borrowed from someone on Stack Overflow. It parses the csv file and converts it into an array of array's that we can work with. I store that array as a global variable, because I don't want to have to parse it again when I click the save button. Something about duplicating the parsing code in two different functions didn't seem right to me.

The rest of the code is dedicated to creating the grid that will display all of the component information. The final action is to take that grid and stuff it into the MyGrid html object we created in the last step.

Save the bom
Now that you can upload and preview the bom, you need to save it and this is the last step for that. The onclick code for the save button is below.

var bomData = window.arrData;
//check if the global bom data is saved
if (bomData == null) {
top.aras.AlertError("You need to load a BOM before you can save.");
return;
}

if(bomData.length <= 1){
top.aras.AlertError("I think the bom you loaded is empty, is there no header column in your csv file?");
}

var select = document.getElementsByName("assemblyPart")[0];
var partID = select.options[select.selectedIndex].value;

if(partID.length != 32){
     top.aras.AlertError("ID should be 32 long");
     return;
}


var innovator = new Innovator();
//debugger;
var c = bomData.length;
for (var i=1; i<c; ++i) {
     if(bomData[i].length != 4){
          continue;
     }
     
     var partItem = innovator.newItem("CIP LOADED BOM","add");
     partItem.setProperty("partid", partID);
     partItem.setProperty("quantity", bomData[i][1]);
     partItem.setProperty("altiorpartnumber", bomData[i][2]);
     partItem.setProperty("ref_designator", bomData[i][3]); 
     
     var resultItem = partItem.apply();
     if (resultItem.isError()) {
        top.aras.AlertError(resultItem.getErrorDetail());
        return;
     }
}

The above code looks for the global array of bom items from when we loaded the csv file. If it's not found it throws up an error. If it is found it loops through all of the parts, creating a new item for each one and inserting it into the table by calling the apply() method. There's probably a more efficient way to do them all at once but I didn't know what it was. For a large bom this only takes a few seconds for me.

Putting it all together
That's all there is, if everything came together for you, you should now be able to upload a bom created in orcad into Innovator. To invoke this form you just created you need to go to CIP LOADED BOMS on the design tab and then edit one of the existing items. If you don't have any then just create a fake test one to start. I'm sure eventually I'll figure out how to just click and have this form come up but I don't know today :)

Now just upload a csv bom, choose a part to attach these parts to and save away. When it's done uploading you'll be able to select that part under Design - > Parts, click on the CIP BOM tab and see all your parts under there. Even better you can export that bom to excel to send to your CM.

The End
Hope all this helps someone else, I know it's a bit of a mess but it does work. Good Luck!

This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

CIP Innovator Association

2012-01-18T11:41:00.001-08:00

Now it's time to make an association between my Orcad CIP parts and my Innovator Parts. Let me say again this is a hack and probably not the way you should do it, etc, etc. Anyway step one is to create a new Item Type called CIP LOADED BOM. To create the fields I needed I just added them in the property section. Each property you add ends up being a column in the CIP LOADED BOM table. So all I needed were:

partID: the part id of the Innovator part we're linking to
quantity: this comes from the Orcad CIS BOM it's how many of this part you need
ref_designator: these are the reference designators for you part like, C1, C2 C3 etc.
companyPartnumber: This is our internal part number.

The relationship of partId and companyPartnumber is how I link what CIP components go with what Innovator part.

The next step is to create a form for this part, just click on the Views tab and create a new form called Loaded Bom like this:

That was pretty simple, and we'll use that form in the next post. Now all you have to do is add it to the TOC. Click the TOC Access tab and add it to the Design section.

Now if you wanted to you could go to this part under design view and add some CIP <-> Innovator part relationships. Then when you click on the Innovator part under Design->Parts and choose the CIP BOM tab you'll see your parts that have been pulled from your CIP database.

The real goal of this for me was to generate a bom from Capture and import it directly into innovator. I'll show you my hacked up way of doing that in my next post.

Linking Orcad Parts To Innovator Parts onGet Function

2012-01-18T11:41:00.000-08:00

In the previous step we created a part called CIP Part BOM, and my originally idea had been to copy how Aras does their Part, Part BOM relationship. I had a little trouble with that and I was pressed for time so I ended up just overriding the onGet function of the CIP Part BOM. Then I used that to return the correct list of CIP Parts associated with the Innovator Part.

Here's the complete, uncut, non-cleaned up code of my onGet function. I didn't want to strip something out and have it not work for you by accident..

 
       //C:\Program Files (x86)\Aras\Innovator\Innovator\Server\temp
    //this.dom.Save(CCO.Server.MapPath("./temp/Incoming_AML.xml"));
    //StreamWriter sw = null;
    //sw = File.CreateText("C:/Program Files (x86)/Aras/Innovator/Innovator/Server/temp/loge.txt");
    //sw.WriteLine("--- BEGIN LOG ---");
    //sw.WriteLine("source");

    //sw.Flush();
    //sw.Close();return;
    //try to get part number

    //these two items are what is going to get returned
    Item res = this.getInnovator().newResult("");
    XmlElement resultNd = (XmlElement) res.dom.SelectSingleNode("/*/*/*");

    string sSource;
    try{
         XmlNode xSource = this.dom.SelectSingleNode("/Item/source_id/text()");
         sSource = xSource.Value;
                 
        }catch (Exception ex){
             //part number missing from request
             return res;
        }
    
    //This is the SQL connection string to connect to the innovator SQL Express database.  
    System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection ();
    conn.ConnectionString = 
     "data source=MININT-PHRAUUK\\sqlexpress;User Id=innovator;Password=;" +
     "initial catalog=InnovatorSolutions;";
    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
    System.Data.SqlClient.SqlDataReader reader;
    //A58793C29830417C9938C75C031EB103
    //this query only gets parts with a quantity, we'll get the extra manufacturers in the next step
    cmd.CommandText = "WITH BOM_ITEMS AS (SELECT PARTID, companyPARTNUMBER, QUANTITY, REF_DESIGNATOR, ROW_NUMBER() "
                      + "OVER (ORDER BY companyPARTNUMBER) AS 'RowNumber' FROM CIP_LOADED_BOM WHERE PARTID = @partID) "
                      + "SELECT * FROM BOM_ITEMS "
                      + "WHERE RowNumber Between @low AND @high";
    cmd.CommandType = CommandType.Text;
    //cmd.Parameters.AddWithValue("partID", "A58793C29830417C9938C75C031EB103");
    cmd.Parameters.AddWithValue("partID", sSource);
    
    //This is the SQL connection string to connect to the CIP SQL Express database.  
    //Note the login details are the default CIP internal user according to EMA's
    //documentation.
    System.Data.SqlClient.SqlConnection cipConn = new System.Data.SqlClient.SqlConnection ();
    cipConn.ConnectionString = 
     "data source=MININT-PHRAUUK\\sqlexpress;User Id=CIP_E_Internal_User;Password=bra#ru5=phaT;" +
     "initial catalog=CIP_E;";
    System.Data.SqlClient.SqlCommand cipCmd = new System.Data.SqlClient.SqlCommand();
    System.Data.SqlClient.SqlDataReader cipReader;
    //cipCmd.Parameters.Clear 
    cipCmd.CommandText = "WITH PARTS AS(SELECT p.PART_NUMBER, p.RowID, des.description, b.manufacturer, b.[manufacturer PN], ROW_NUMBER() OVER (ORDER BY p.PART_NUMBER) AS 'RowNumber' FROM  [CIP_E].[dbo].[AllParts] AS p " 
            + "INNER JOIN [CIP_E].[dbo].[Component Manufacturer Parts] AS a ON p.RowID = a.PartGUID " 
            + "INNER JOIN [CIP_E].[dbo].[Manufacturer Part Info] AS b ON a.ManufacturerGUID = b.RowID " 
            + "Inner Join [CIP_E].[dbo].[CT_PART_DESCRIPTIONS] AS des ON des.RowID = p.RowID "
            + "WHERE p.PART_NUMBER = @partNumber )"
            + "SELECT * FROM PARTS ";
            // +"WHERE RowNumber Between @low AND @high";
    
    cipConn.Open();
    cipCmd.Connection = cipConn;

    
    System.Data.SqlClient.SqlParameter param_low  = new System.Data.SqlClient.SqlParameter();
      param_low.ParameterName = "@low";
      param_low.Value         = 0;
      cmd.Parameters.Add(param_low);

    System.Data.SqlClient.SqlParameter param_high  = new System.Data.SqlClient.SqlParameter();
      param_high.ParameterName = "@high";
      param_high.Value         = 20000;
      cmd.Parameters.Add(param_high);
    
    conn.Open();
    cmd.Connection = conn;
    reader = cmd.ExecuteReader();

//need to get ID of BOM



    //string bomID = "0FBE827DE1394675A68C13FA13C67F9C";
    //string bomID = "EE8D6833EF2A480994DC7FE3A0C1641E";
    string bomID = this.getNewID();
    int i = 0;    
    while(reader.Read() && i < 100){
         i++;
         //first create the CIP Part BOM
         //Item bom_item = this.newItem("CIP Part BOM");
         //bom.setType("CIP Part BOM");
         //bom.setID("0FBE827DE1394675A68C13FA13C67F9C");
         //bom.removeAttribute("isNew");
         //bom.removeAttribute("isTemp");
         //bom.apply();    
         
         //grab the company part number for this result
         string companyPartNumber = reader["companyPARTNUMBER"].ToString();
         cipCmd.Parameters.Clear();
         cipCmd.Parameters.AddWithValue("partNumber", companyPartNumber);
         cipReader = cipCmd.ExecuteReader();         

         bool firstTime = true;
         while(cipReader.Read()){
          
              //first create the CIP Part BOM
              Item bom_item = this.newItem("CIP Part BOM");
              bom_item.setType("CIP Part BOM");
              //bom_item.setID("0FBE827DE1394675A68C13FA13C67F9C");
              bom_item.setID(bomID);
              bom_item.removeAttribute("isNew");
              bom_item.removeAttribute("isTemp");
              bom_item.setProperty("keyed_name", bomID);
              bom_item.setAttribute("typeId", " 74B6F95348AA4DE3B84F89D45E3ED56D");
              if(firstTime == true){
               firstTime = false; 
               try{
                  bom_item.setProperty("quantity", reader["QUANTITY"].ToString());
                  bom_item.setProperty("reference_designator", reader["REF_DESIGNATOR"].ToString()); 
               }catch (Exception ex){
               
               }
            }
              bom_item.apply(); 
         
              Item part = this.newItem("CIP_PART");
              string id = cipReader["RowID"].ToString();
              id = id.Replace("-", "");
              part.setID(id);
              part = part.apply();
              part.setProperty("part_number", cipReader["PART_NUMBER"].ToString());
              part.setProperty("manf_part_number", cipReader["Manufacturer PN"].ToString());
              part.setProperty("manufacturer", cipReader["Manufacturer"].ToString());
              part.setProperty("description", cipReader["description"].ToString());
              bom_item.setRelatedItem(part);
              resultNd.AppendChild(resultNd.OwnerDocument.ImportNode(bom_item.node, true)); 
         }
 
         cipReader.Close();
         
         //if we didn't find any manufacturing data add blank part
         if(firstTime){
            //first create the CIP Part BOM
              Item bom_item = this.newItem("CIP Part BOM");
              bom_item.setType("CIP Part BOM");
              //bom_item.setID("0FBE827DE1394675A68C13FA13C67F9C");
              bom_item.setID(bomID);
              bom_item.removeAttribute("isNew");
              bom_item.removeAttribute("isTemp");
              bom_item.setProperty("keyed_name", bomID);
              bom_item.setAttribute("typeId", " 74B6F95348AA4DE3B84F89D45E3ED56D");
              try{
                  bom_item.setProperty("quantity", reader["QUANTITY"].ToString());
                  bom_item.setProperty("reference_designator", reader["REF_DESIGNATOR"].ToString()); 
            }catch (Exception ex){
               
            }
              bom_item.apply(); 
         
              Item part = this.newItem("CIP_PART");
              part.setID(getNewID());
              part = part.apply();
              part.setProperty("part_number", reader["companyPARTNUMBER"].ToString());
              part.setProperty("manf_part_number", "NOT IN DBASE");
              part.setProperty("manufacturer", "NOT IN DBASE");
              part.setProperty("description", "NOT IN DBASE");
              bom_item.setRelatedItem(part);
              resultNd.AppendChild(resultNd.OwnerDocument.ImportNode(bom_item.node, true)); 
         }
         
         }
 
    conn.Close();
    return res;




///////////////Ignore below this line, this code never runs it's just here as notes to me////////////////

    //C:\Program Files (x86)\Aras\Innovator\Innovator\Server\temp
    this.dom.Save(CCO.Server.MapPath("./temp/Incoming_AML.xml"));
    


    //get the source ID
    XmlNode xSourceId = dom.SelectSingleNode("/Item/source_id/text()");
    string sSourceId = xSourceId.Value;



    Item bom = this.newItem("CIP Part BOM");
    //bom.setAttribute("typeId", "74B6F95348AA4DE3B84F89D45E3ED56D");
    bom.setType("CIP Part BOM");
    bom.setID("0FBE827DE1394675A68C13FA13C67F9C");

    //bom.setProperty("source_id", sSourceId);
    bom.removeAttribute("isNew");
    bom.removeAttribute("isTemp");

    bom.apply();


    //create a fake CIP_PART here
    Item res5 = this.newItem("CIP_PART");
    //Item res5 = bom.createRelatedItem("CIP_PART", "");
             string newid = getNewID();
             res5.setID("A6F68F1E57C3432AA07E598F2468FFE2");
             res5 = res5.apply();
             res5.setProperty("part_number", "CT-ERIC-TEST");
             res5.setProperty("manf_part_number", "00-333-0000");
             res5.setProperty("manufacturer", "ACME");
             res5.setProperty("description", "Road Runner Bait");
  
    bom.setRelatedItem(res5);
    
    resultNd.AppendChild(resultNd.OwnerDocument.ImportNode(bom.node, true));
    //bom.apply();
    
    Item bom4 = bom.clone(false);
    
    Item res4 = this.newItem("CIP_PART");
    //Item res5 = bom.createRelatedItem("CIP_PART", "");
             newid = getNewID();
             res4.setID(newid);
             res4 = res4.apply();
             res4.setProperty("part_number", "CT-ERIC-TEST-saldklasdk");
             res4.setProperty("manf_part_number", "00-333-0000");
             res4.setProperty("manufacturer", "ACME");
             res4.setProperty("description", "Road Runner Bait");
    // don't work yet res5.appendItem(res4);
    bom4.setRelatedItem(res4);
    resultNd.AppendChild(resultNd.OwnerDocument.ImportNode(bom4.node, true));
        
/*
    StreamWriter sw = null;
    sw = File.CreateText("C:/Program Files (x86)/Aras/Innovator/Innovator/Server/temp/loge.txt");
    sw.WriteLine("--- BEGIN LOG ---");
    sw.WriteLine("bom: {0}", xNode);

    sw.Flush();
    sw.Close();
    */
    //return res4;
    //return res;
    
/**
*   Ignore below this line it was for when things weren't working
*
*/
//<Item type =" CIP Part BOM " id =" EE8D6833EF2A480994DC7FE3A0C1641E " >


Item wtf = this.newItem("CIP Part BOM");

string aml = "<Result>"
+"<Item type =\" CIP Part BOM \" typeId =\" 74B6F95348AA4DE3B84F89D45E3ED56D \" id =\" 0FBE827DE1394675A68C13FA13C67F98 \" >"
//+"<Item type =\" CIP Part BOM \" id =\" EE8D6833EF2A480994DC7FE3A0C1641E \" >"
//+"<created_by_id keyed_name =\" Innovator Admin \" type =\" User \" >30B991F927274FA3829655F50C99472E</created_by_id>"
//+"<created_on>2012-01-11T11:48:15</created_on>"
//+"<css is_null =\" 1 \" />"
//+"<current_state is_null =\" 1 \" />"
//+"<id keyed_name =\" 0FBE827DE1394675A68C13FA13C67F9C \" type =\" CIP Part BOM \" >0FBE827DE1394675A68C13FA13C67F9A</id>"
//+"<keyed_name>0FBE827DE1394675A68C13FA13C67F9C</keyed_name>"
//+"<locked_by_id is_null =\" 1 \" />"
//+"<major_rev>A</major_rev>"
//+"<modified_by_id keyed_name =\" Innovator Admin \" type =\" User \" >30B991F927274FA3829655F50C99472E</modified_by_id>"
//+"<modified_on>2012-01-11T11:48:15</modified_on>"
//+"<quantity>1</quantity>"
//+"<reference_designator is_null =\" 1 \" />"
//+"<related_id is_null =\" 0 \" >" me testing if is_null needed
+"<related_id>"
+"<Item type =\" CIP_PART \" typeId =\" 56D225FB95344D5C87128E28A366C307 \" id =\" A6F68F1E57C3432AA07E598F2468FFE2 \" >"
+"<part_number>CT-ERIC-TEST</part_number>"
+"<manf_part_number>00-333-0000</manf_part_number>"//-
+"<manufacturer>ACME</manufacturer>"
+"<description>Road Runner Bait</description>"
+"</Item>"
+"</related_id>"
//+"<source_id keyed_name =\" ct-test \" type =\" Part \" >5616EF9A14E248C99930A458F760C23B</source_id>"
+"</Item>"
+"</Result>";
    
    wtf.loadAML(aml);
    
    return wtf;

I won't go over all the parts above that I covered for creating a CIP Item in an earlier post, but I will go over the important sections.

    //This is the SQL connection string to connect to the innovator SQL Express database.  
    System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection ();
    conn.ConnectionString = 
     "data source=MININT-PHRAUUK\\sqlexpress;User Id=innovator;Password=;" +
     "initial catalog=InnovatorSolutions;";
    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
    System.Data.SqlClient.SqlDataReader reader;

The above is the SQL connection to the innovator database. There's other ways to get data out of Innovator using their IOM api, but I was comfortable with this. I did not have a password set for my connection string, that's why it's blank.

    cmd.CommandText = "WITH BOM_ITEMS AS (SELECT PARTID, companyPARTNUMBER, QUANTITY, REF_DESIGNATOR, ROW_NUMBER() "
                      + "OVER (ORDER BY companyPARTNUMBER) AS 'RowNumber' FROM CIP_LOADED_BOM WHERE PARTID = @partID) "
                      + "SELECT * FROM BOM_ITEMS "
                      + "WHERE RowNumber Between @low AND @high";
    cmd.CommandType = CommandType.Text;
    //cmd.Parameters.AddWithValue("partID", "A58793C29830417C9938C75C031EB103");
    cmd.Parameters.AddWithValue("partID", sSource);

I store the relationship between an Innovator Part and a CIP part in another Item Type called CIP LOADED BOM. That means there is a table created for that Item Type called CIP_LOADED_BOM. So in the above query I'm selecting all the CIP components that are tied to the partID. The partID has been passed to us by Innovator when the user clicked on the CIP Part BOM tab. It sends an AML request that basically says get me the CIP Part BOM for partID: blah blah blah. Then I use that to look up the CIP components I need.

    cipCmd.CommandText = "WITH PARTS AS(SELECT p.PART_NUMBER, p.RowID, des.description, b.manufacturer, b.[manufacturer PN], ROW_NUMBER() OVER (ORDER BY p.PART_NUMBER) AS 'RowNumber' FROM  [CIP_E].[dbo].[AllParts] AS p " 
            + "INNER JOIN [CIP_E].[dbo].[Component Manufacturer Parts] AS a ON p.RowID = a.PartGUID " 
            + "INNER JOIN [CIP_E].[dbo].[Manufacturer Part Info] AS b ON a.ManufacturerGUID = b.RowID " 
            + "Inner Join [CIP_E].[dbo].[CT_PART_DESCRIPTIONS] AS des ON des.RowID = p.RowID "
            + "WHERE p.PART_NUMBER = @partNumber )"
            + "SELECT * FROM PARTS ";

This next query is a modified version of the query from the CIP PART post. The thing to notice here is the line WHERE p.PART_NUMBER = @partNumber. If you're not familiar with this kind of notation it's not that hard to follow. Basically I've created this query and the @partNumber is a place holder for the actual CIP part number. There are all sorts of good reasons to do your queries like this, but in this case I'm going to re-use this query over and over again in a loop changing that partNumber each time. It will make things a little easier.

    string bomID = this.getNewID();
    int i = 0;    
    while(reader.Read() && i < 100){
         i++;
         //grab the company part number for this result
         string companyPartNumber = reader["companyPARTNUMBER"].ToString();
         cipCmd.Parameters.Clear();
         cipCmd.Parameters.AddWithValue("partNumber", companyPartNumber);
         cipReader = cipCmd.ExecuteReader();

This is the first part of the loop to create the response. I'm looping through all the CIP parts associated with this Innovator part. I grab the company part number from each result and store it as the @partNumber parameter from the previous step. Also notice that I call Parameters.Clear, that will remove the last parameter you set for each loop iteration. At the end I execute the SQL query.

bool firstTime = true;
         while(cipReader.Read()){
          
              //first create the CIP Part BOM
              Item bom_item = this.newItem("CIP Part BOM");
              bom_item.setType("CIP Part BOM");
              //bom_item.setID("0FBE827DE1394675A68C13FA13C67F9C");
              bom_item.setID(bomID);
              bom_item.removeAttribute("isNew");
              bom_item.removeAttribute("isTemp");
              bom_item.setProperty("keyed_name", bomID);
              bom_item.setAttribute("typeId", " 74B6F95348AA4DE3B84F89D45E3ED56D");
              if(firstTime == true){
               firstTime = false; 
               try{
                  bom_item.setProperty("quantity", reader["QUANTITY"].ToString());
                  bom_item.setProperty("reference_designator", reader["REF_DESIGNATOR"].ToString()); 
               }catch (Exception ex){
               
               }
            }
              bom_item.apply(); 
         
              Item part = this.newItem("CIP_PART");
              string id = cipReader["RowID"].ToString();
              id = id.Replace("-", "");
              part.setID(id);
              part = part.apply();
              part.setProperty("part_number", cipReader["PART_NUMBER"].ToString());
              part.setProperty("manf_part_number", cipReader["Manufacturer PN"].ToString());
              part.setProperty("manufacturer", cipReader["Manufacturer"].ToString());
              part.setProperty("description", cipReader["description"].ToString());
              bom_item.setRelatedItem(part);
              resultNd.AppendChild(resultNd.OwnerDocument.ImportNode(bom_item.node, true)); 
         }
 
         cipReader.Close();

This is the sub loop that creates the actual response. I read each returned value from the CIP Loaded BOM and a new Item CIP Part BOM part for each one. If this is the first time through this loop I'll add the quantity and and reference designator. If not it will be blank. This supports CIP having multiple manufacturers per part number. You don't want to produce a bom that says you need twice as many CT-CAP-00001s just because you have two manufacturing sources for it.

Next we get the actual CIP component data from the CIP database, create a new part for it, and append it to the CIP Part Bom Item we just created. We'll go through this loop until we get through all of the CIP part numbers associated with the Innovator Part.

    conn.Close();
    return res;

That's it, now we just return the res result set. If all went well you should now see the list of CIP Components associated with your innovator part. Well actually at this point you won't see anything because we still need to create that CIP LOADED BOM association. I'll do that in my next post.

This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Linking Orcad CIP parts to Innovator parts

2012-01-18T11:38:00.000-08:00

OK in the last post I showed you how I connected to the CIP database and got my parts into Innovator. My next step was to try to attach those parts to a regular innovator part. Originally I thought I'd use those CIP PARTs we just created and make my own CIP BOM to contain all of them. Then I'd like the CIP BOM to an Innovator Part.

Instead I ended up creating a CIP BOM, and overriding it's onGet function. Then whenever a part called get for it's CIP BOM I return it the list of CIP PARTS that belong to it. I keep that association in another table/item I created called CIP LOADED BOM (terrible naming conventions I used). I use that CIP LOADED BOM part's form to load in a .csv bom file exported from Orcad Capture.

The downside of this approach, among other things, is that you can only update the Innovator part you attach your CIP parts to when you want to add a new BOM. You can go from preliminary to released, but you can't say add an additional Innovator part to it because that will create a new version of your part, with a new id and that breaks my approach. So in short I decided I'll always create a part like CT-BOM-000001 and it's only job will be to hold my Orcad BOM. Hack hack hackity hack :)

First Steps
Alright my first step was to create a new item called CIP BOM, I just made a copy of Innovator's Part BOM item. Then I added the CIP_BOM_GET method to the onGet Event.

Part Relationship
The next step was to create a relationship with an Innovator Part and my brand new CIP Part BOM. To do that go edit the Part Item Type. Then click on the RelationshipTypes tab. Next select pick related and click the new button. Now search for the CIP Part Bom part, add it and your window should look like this.

CIP PART BOM Relationship
I can't remember if this part is absolutely needed or not, but I'll include it here. I edited the CIP Part BOM relationship to point to my CIP PARTS I created in another post. The important part was to set the Source Item Type and Related Item Type correctly.

Finally

Now that all that is done you can go select an Innovator Part and you'll see a new tab called CIP Part BOM in the bottom pane. This is where we'll see, but not add the associated CIP Parts for this Innovator Part. You'll notice I made it the second tab, and I did that mainly for development work. Innovator only calls the AML function to load that tab when you click on it. So if you're trying to isolate why something doesn't work you can just load the part, click the tab and then you'll be able to see all the requests that were called for that tab.

This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Aras Innovator Orcad CIP onGet Part 2

2012-01-17T14:13:00.002-08:00

Now in the last part I had included all the code that was right out of our Innovator function. Including somethings I stopped working on like the get_single path. So we're going to skip ahead to the list_all path which is what I use most.

 
    //open the sql connection     
    conn.Open();

Just before the switch statement I call conn.Open which opens the connection to the CIP database

     case "list_all":
         /*
            *   Here we check to see if the user is searching for anything
            */
             try{
                  XmlNode xPart = this.dom.SelectSingleNode("/Item/part_number/text()");
                  XmlNode xCond = this.dom.SelectSingleNode("/Item/part_number/@condition");

                  string sPart = xPart.Value;
                  System.Data.SqlClient.SqlParameter param_part  = new System.Data.SqlClient.SqlParameter();
                  param_part.ParameterName = "@part";
                  if(xCond.Value == "like"){
                       //where += " AND p.PART_NUMBER LIKE '" + sPart.Replace("*", "%") + "'";
                       where += " AND p.PART_NUMBER LIKE @part";
                 param_part.Value = sPart.Replace("*", "%");
                 cmd.Parameters.Add(param_part);
                  }else{
                       //where += " AND p.PART_NUMBER = '" + sPart + "'";
                       where += " AND p.PART_NUMBER = @part";
                       param_part.Value = sPart;
                 cmd.Parameters.Add(param_part);
                  }
             }catch (Exception ex){

             }

             try{
                  XmlNode xManfNum = this.dom.SelectSingleNode("/Item/manf_part_number/text()");
                  XmlNode xCond = this.dom.SelectSingleNode("/Item/manf_part_number/@condition");
                  string sManfNum = xManfNum.Value;
                  System.Data.SqlClient.SqlParameter param_manfnum  = new System.Data.SqlClient.SqlParameter();
                  param_manfnum.ParameterName = "@manfnum";
                  if(xCond.Value == "like"){
                       //where += " AND b.[Manufacturer PN] LIKE '" + sManfNum.Replace("*", "%") + "'";
                       where += " AND b.[Manufacturer PN] LIKE @manfnum";
                 param_manfnum.Value = sManfNum.Replace("*", "%");
                 cmd.Parameters.Add(param_manfnum);
                  }else{
                       //where += " AND b.[Manufacturer PN] = '" + sManfNum + "'";
                       where += " AND b.[Manufacturer PN] = @manfnum";
                 param_manfnum.Value = sManfNum;
                 cmd.Parameters.Add(param_manfnum);
                  }
             }catch (Exception ex){

             }
             
             try{
                  XmlNode xManf = this.dom.SelectSingleNode("/Item/manufacturer/text()");
                  XmlNode xCond = this.dom.SelectSingleNode("/Item/manufacturer/@condition");
                  string sManf = xManf.Value;
                  System.Data.SqlClient.SqlParameter param_manf  = new System.Data.SqlClient.SqlParameter();
                  param_manf.ParameterName = "@manf";
                  if(xCond.Value == "like"){
                       //where += " AND b.[Manufacturer] LIKE '" + sManf.Replace("*", "%") + "'";
                       where += " AND b.[Manufacturer] LIKE @manf";
                 param_manf.Value = sManf.Replace("*", "%");
                 cmd.Parameters.Add(param_manf);
                  }else{
                       //where += " AND b.[Manufacturer] = '" + sManf + "'";
                       where += " AND b.[Manufacturer] = @manf";
                 param_manf.Value = sManf;
                 cmd.Parameters.Add(param_manf);
                  }
             }catch (Exception ex){
                  //sw.WriteLine("No Manf found Found");
             }


             try{
                  XmlNode xDesc = this.dom.SelectSingleNode("/Item/description/text()");
                  XmlNode xCond = this.dom.SelectSingleNode("/Item/description/@condition");
                  string sDesc = xDesc.Value;
                  System.Data.SqlClient.SqlParameter param_desc  = new System.Data.SqlClient.SqlParameter();
                  param_desc.ParameterName = "@desc";
                  if(xCond.Value == "like"){
                       //where += " AND des.[Description] LIKE '" + sDesc.Replace("*", "%") + "'";
                       where += " AND des.[Description] LIKE @desc";
                 param_desc.Value = sDesc.Replace("*", "%");
                 cmd.Parameters.Add(param_desc);
                  }else{
                       //where += " AND des.[Description] = '" + sDesc + "'";
                       where += " AND des.[Description] = @desc";
                 param_desc.Value = sDesc.Replace("*", "%");
                 cmd.Parameters.Add(param_desc);
                  }
             }catch (Exception ex){
                  //sw.WriteLine("No description Found");
             }

In the section above I'm parsing that AML request to see if there are query options set in it. In clearer terms I'm looking to see if the user tried to run a search instead of listing all the results. When you're in Innovator and you have selected say parts, you'll see a blue highlighted line at the top of the part table in the right hand pane. If you go in to one of those fields, say part number and type CT-CAP* and hit enter that will return all the parts with the part number like CT-CAP*. Like CT-CAP-000001, CT-CAP-000002.

So by searching the AML query for all those fields we can create a query that will return only the parts the user is searching for. So for each of the query options above I add some SQL to the variable where. Later I'll tie that variable into my main SQL request.

Now comes the fun part, the main SQL request that is going to get all these parts for us. I ran into a little trouble at first because CIP keeps part descriptions in separate tables for each part type. So capacitor descriptions are in the capacitor table, switch descriptions in the switch table etc. To make my life easier I went into SQL Express manager and created a view to do a union of all the tables. Someone on stack overflow suggested this approach to me.

Creating a view is pretty easy, just go into MS SQL Server Managment studio and login. Then find the CIP_E database, select the view tab, right click and create new. Then give it a name and use this as the SQL that describes it.

 
SELECT PART_NUMBER, [Part Type], Description, RowID
FROM  dbo.Capacitors
WHERE PART_NUMBER LIKE 'CT-%'
UNION
SELECT PART_NUMBER, [Part Type], Description, RowID
FROM  dbo.Connectors
WHERE PART_NUMBER LIKE 'CT-%'
UNION
SELECT PART_NUMBER, [Part Type], Description, RowID
FROM  dbo.[Crystals and Oscillators]
WHERE PART_NUMBER LIKE 'CT-%'
UNION
SELECT PART_NUMBER, [Part Type], Description, RowID
FROM  dbo.Diodes
WHERE PART_NUMBER LIKE 'CT-%'
UNION
SELECT PART_NUMBER, [Part Type], Description, RowID
FROM  dbo.ICS
WHERE PART_NUMBER LIKE 'CT-%'
UNION
SELECT PART_NUMBER, [Part Type], Description, RowID
FROM  dbo.inductors
WHERE PART_NUMBER LIKE 'CT-%'
UNION
SELECT PART_NUMBER, [Part Type], Description, RowID
FROM  dbo.misc
WHERE PART_NUMBER LIKE 'CT-%'
UNION
SELECT PART_NUMBER, [Part Type], Description, RowID
FROM  dbo.Relays
WHERE PART_NUMBER LIKE 'CT-%'
UNION
SELECT PART_NUMBER, [Part Type], Description, RowID
FROM  dbo.Resistors
WHERE PART_NUMBER LIKE 'CT-%'
UNION
SELECT PART_NUMBER, [Part Type], Description, RowID
FROM  dbo.Switches
WHERE PART_NUMBER LIKE 'CT-%'
UNION
SELECT PART_NUMBER, [Part Type], Description, RowID
FROM  dbo.Transformers
WHERE PART_NUMBER LIKE 'CT-%'
UNION
SELECT PART_NUMBER, [Part Type], Description, RowID
FROM  dbo.Transistors
WHERE PART_NUMBER LIKE 'CT-%'

Now if you want you can select the new view and select show top 1000 rows and you'll see that it combines all of the tables into one for our query.

 
            cmd.CommandText = "WITH PARTS AS(";
          cmd.CommandText += "SELECT *, ROW_NUMBER() OVER (ORDER BY p.PART_NUMBER) AS 'RowNumber'  FROM  dbo.AllParts AS p WHERE p.PART_NUMBER LIKE 'CT%'"; 
            cmd.CommandText += ")";
            cmd.CommandText += "SELECT COUNT(*) FROM PARTS as p "
            + "INNER JOIN dbo.[Component Manufacturer Parts] AS a ON p.RowID = a.PartGUID "
            + "INNER JOIN dbo.[Manufacturer Part Info] AS b ON a.ManufacturerGUID = b.RowID " 
            + "LEFT Join dbo.[CT_PART_DESCRIPTIONS] AS des ON des.RowID = p.RowID ";                      

            if(where != ""){
                 cmd.CommandText += "WHERE 'monkey' = 'monkey' " + where;
            }
            
            
            //sw.WriteLine("sql: {0}", cmd.CommandText);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;
         
            int count = (int) cmd.ExecuteScalar();           
         //sw.WriteLine("Found: {0} parts", count);
         
         //calculate the start and stop
         int iStart = (iPage - 1) * iPageSize + 1;
         int iEnd = iPage * iPageSize;
         int iPageMax = count / iPageSize;
         
         //is there an extra non full page?
         if(iPageMax * iPageSize < count){
              iPageMax++; 
         }
         
         //looking for more records than exist
         if(iEnd > count){
             iEnd = count;
             //iStart = iEnd - iPageSize;
             
             if(iStart <0){
              iStart = 0;
             } 
         }
         
         //now execute query for real

            
            cmd.CommandText = "WITH PARTS AS(SELECT p.PART_NUMBER, p.RowID, des.description, b.manufacturer, b.[manufacturer PN], ROW_NUMBER() OVER (ORDER BY p.PART_NUMBER) AS 'RowNumber' FROM  [CIP_E].[dbo].[AllParts] AS p " 
            + "INNER JOIN [CIP_E].[dbo].[Component Manufacturer Parts] AS a ON p.RowID = a.PartGUID " 
            + "INNER JOIN [CIP_E].[dbo].[Manufacturer Part Info] AS b ON a.ManufacturerGUID = b.RowID " 
            + "Inner Join [CIP_E].[dbo].[CT_PART_DESCRIPTIONS] AS des ON des.RowID = p.RowID "
            + "WHERE 'monkey' = 'monkey' " + where + ") "
            + "SELECT * FROM PARTS WHERE RowNumber Between @low AND @high";
            
            
      System.Data.SqlClient.SqlParameter param_low  = new System.Data.SqlClient.SqlParameter();
      param_low.ParameterName = "@low";
      param_low.Value         = iStart;
      cmd.Parameters.Add(param_low);

      System.Data.SqlClient.SqlParameter param_high  = new System.Data.SqlClient.SqlParameter();
      param_high.ParameterName = "@high";
      param_high.Value         = iEnd;
      cmd.Parameters.Add(param_high);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;

         //damn you for having no LIMIT function MS SQL

         reader = cmd.ExecuteReader();

The first thing I do above is run a count query to find out how many records our query will return. I'll use this to let the user interface know how many more pages of results there are. After that query you'll see that I use that data to calculate the start, end and pagemax values.

Let's skip ahead to the main query and try to break it down. Here I'll take a moment to say how much I did not like working with MS SQL. Compared to say MySQL with PHP it felt clumsy, and more difficult than it need to be. Especially since there's no LIMIT clause to limit the number of results. Instead I have to do what you see above, using a main query to select the row numbers and then using a Between statement to limit the returned results. Such a pain, and if you want to know more just google MS SQL Limit function...

Anyway the query says Select everything from the CIP AllParts table, which contains all of the part numbers in our CIP system. Then join that with the Component Manufacturer Parts table which has all the manufacturer data. Then finally join that with the view we just made to get the part descriptions. The whole where 'monkey' = 'monkey' thing is just a statement that will always be true so I can just insert those where clauses we generated a few steps ago without having to do anything fancy.

Next we select from Parts which is not a real table we just created it in the query by saying WITH PARTS AS(....). We use the Between statement combined with the start and end values we previously calculated to just get the few rows that we want.

Finally we set the connection to our database connection and execute the query using ExecuteReader. That will return a reader that we'll use to read the result data from the query.

         i = 0;    
            while(reader.Read() && i < iPageSize){
          i++;

          Item res5 = this.newItem("CIP_PART");
             string newid = getNewID();
             string id = reader["RowID"].ToString();
             id = id.Replace("-", "");
             res5.setID(id);
             res5 = res5.apply();
             res5.setProperty("part_number", reader["PART_NUMBER"].ToString());
             res5.setProperty("start", iStart.ToString());
             res5.setProperty("pageSize", iPageSize.ToString());
             res5.setProperty("end", iEnd.ToString());
             res5.setProperty("manf_part_number", reader["Manufacturer PN"].ToString());
             res5.setProperty("manufacturer", reader["Manufacturer"].ToString());
             res5.setProperty("description", reader["description"].ToString());
             res5.setAttribute("page", iPage.ToString());
             res5.setAttribute("pagemax", iPageMax.ToString());
             res5.setAttribute("itemmax", count.ToString());            
             resultNd.AppendChild(resultNd.OwnerDocument.ImportNode(res5.node, true));
             }

Almost Done! In the above code I repeatedly call the reader.Read() function to get the result rows from the CIP database. For each result I create a new Item, and since CIP has 32 bit IDs but uses several '-' characters between them I strip out the dash character and use that for the ID. Then I apply the item, which I'll be honest I'm not sure I have to do but I saw it in the docs and it seems to work. Next I just set the properties for each CIP PART. Finally I use resultND to append this new item to res, the result set.

If you don't know already, this setProperty, setAttribute is basically setting up the AML ( XML) inside this item. If you print out the res you'll see that you've just created a large AML response containing all of the CIP PARTS you requested.

   

    //close db connection
    conn.Close();
      
    return res;

As a last step we return the res result set, and that's it! Now you can go into innovator, select Design from the TOC menu. Now select CIP_PART, right click it and choose list all. If everything went well you should see a list of all of your parts.

This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Aras Innovator Orcad CIP Part onGet Part 1

2012-01-17T14:13:00.001-08:00

First things first you need to open up the method we created in the last step. So in the TOC menu on the left choose Administration->methods

Now you should be in the method editor, note if you can't edit anything you probably just double clicked it or selected view. Go back, right click the method name and select edit and you should be ok.

Now here's the full code for my method, it's straight out of my system and not cleaned up at all:

/**
*    This function gets our Orcad CIP/CIS parts out of our database and returns them to 
*    Aras Innovator
*/

    //This is the SQL connection string to connect to our SQL Express database.  
    //Note the login details are the default CIP internal user according to EMA's
    //documentation.
    System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection ();
    conn.ConnectionString = 
     "data source=MININT-PHRAUUK\\sqlexpress;User Id=CIP_E_Internal_User;Password=bra#ru5=phaT;" +
     "initial catalog=CIP_E;";
    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
    System.Data.SqlClient.SqlDataReader reader;
    
    //these two items are what is going to get returned
    Item res = this.getInnovator().newResult("");
    XmlElement resultNd = (XmlElement) res.dom.SelectSingleNode("/*/*/*");

    //this is a handy debug statement htat saves the "Incoming AML Request" to the temp directory
    //of the Innovator server.  For us that is:
    //C:\Program Files (x86)\Aras\Innovator\Innovator\Server\temp
    //this.dom.Save(CCO.Server.MapPath("./temp/Incoming_AML.xml"));
    /*StreamWriter sw = null;
    sw = File.CreateText("C:/Program Files (x86)/Aras/Innovator/Innovator/Server/temp/loge.txt");
    sw.WriteLine("--- BEGIN LOG ---");
    sw.Flush();
    */
    string action = "nothing";

    //check if this is a List All
    int iPage = 1;
    int iPageSize = 25;
    try{
         XmlNode xPage = this.dom.SelectSingleNode("/Item/@page"); 
         XmlNode xPageSize = this.dom.SelectSingleNode("/Item/@pagesize"); 
    
         if(xPage.Value != "" && xPageSize.Value != ""){
             iPage = int.Parse(xPage.Value);
             iPageSize = int.Parse(xPageSize.Value);
         //action = "list_all"; 
         } 
         
         XmlNode xAction = this.dom.SelectSingleNode("/Item/@select");
         string sAction = xAction.Value;
         if(sAction != ""){
              action = "list_all";
         }
    }catch (Exception ex){
        
            
    }
    //check if it is a single get
    // <Item type="CIP_PART" levels="1" action="get" idlist="3FF86EECC96342C7825197321EFCC40D" /> 
    string sID = "";
    try{
         XmlNode xAction = this.dom.SelectSingleNode("/Item/@action");
         XmlNode xIdlist = this.dom.SelectSingleNode("/Item/@idlist");
    
         if(xAction.Value == "get" && xIdlist.Value != "")
         {
             //convert ID
             string sIdlist = xIdlist.Value;
             if(sIdlist.Length == 32){

                 sID = sIdlist.Substring(0, 8) + sIdlist.Substring(8,4) + sIdlist.Substring(12,4) + sIdlist.Substring(16,4) + sIdlist.Substring(20,12);      
                 action = "get_single";
             }
         }
    }catch (Exception ex){

    }
    //open the sql connection     
    conn.Open();
    //action = "list_all";
    string where = "";
    switch(action){
     case "get_single":
          //sw.WriteLine("action_single");
         cmd.CommandText = "WITH PARTS AS(SELECT p.PART_NUMBER, p.RowID, des.description, b.manufacturer, b.[manufacturer PN], ROW_NUMBER() OVER (ORDER BY p.PART_NUMBER) AS 'RowNumber' FROM  [CIP_E].[dbo].[AllParts] AS p " 
            + "INNER JOIN [CIP_E].[dbo].[Component Manufacturer Parts] AS a ON p.RowID = a.PartGUID " 
            + "INNER JOIN [CIP_E].[dbo].[Manufacturer Part Info] AS b ON a.ManufacturerGUID = b.RowID " 
            + "Inner Join [CIP_E].[dbo].[CT_PART_DESCRIPTIONS] AS des ON des.RowID = p.RowID) "
            + "SELECT * FROM PARTS WHERE RowID = @rowid";
            //WITH PARTS AS(SELECT p.PART_NUMBER, p.RowID, des.description, b.manufacturer, b.[manufacturer PN], ROW_NUMBER() OVER (ORDER BY p.PART_NUMBER) AS 'RowNumber' FROM  [CIP_E].[dbo].[AllParts] AS p 
            //INNER JOIN [CIP_E].[dbo].[Component Manufacturer Parts] AS a ON p.RowID = a.PartGUID 
            //INNER JOIN [CIP_E].[dbo].[Manufacturer Part Info] AS b ON a.ManufacturerGUID = b.RowID 
            //Inner Join [CIP_E].[dbo].[CT_PART_DESCRIPTIONS] AS des ON des.RowID = p.RowID) 
            //SELECT * FROM PARTS WHERE RowID = '0BF585C8-14D8-4248-BDA0-4D7EDBB7BAB2'
            //set the rowid      
      System.Data.SqlClient.SqlParameter param_rowid  = new System.Data.SqlClient.SqlParameter();
      param_rowid.ParameterName = "@rowid";
      param_rowid.Value         = "0BF585C8-14D8-4248-BDA0-4D7EDBB7BAB2";
      cmd.Parameters.Add(param_rowid);
            cmd.CommandType = CommandType.Text;

            //sw.WriteLine("single sql: {0}", cmd.CommandText);

            cmd.Connection = conn;
         reader = cmd.ExecuteReader();

         int i = 0;    
            while(reader.Read() && i < 100){
          i++;

          Item res5 = this.newItem("CIP_PART");
             string newid = getNewID();
             string id = reader["RowID"].ToString();
             id = id.Replace("-", "");
             res5.setID(id);
             res5 = res5.apply();
             res5.setProperty("part_number", reader["PART_NUMBER"].ToString());
             res5.setProperty("manf_part_number", reader["Manufacturer PN"].ToString());
             res5.setProperty("manufacturer", reader["Manufacturer"].ToString());
             res5.setProperty("description", reader["description"].ToString());          
             resultNd.AppendChild(resultNd.OwnerDocument.ImportNode(res5.node, true));
             }
     break;
     case "list_all":
         /*
            *   Here we check to see if the user is searching for anything
            */
             try{
                  XmlNode xPart = this.dom.SelectSingleNode("/Item/part_number/text()");
                  XmlNode xCond = this.dom.SelectSingleNode("/Item/part_number/@condition");

                  string sPart = xPart.Value;
                  System.Data.SqlClient.SqlParameter param_part  = new System.Data.SqlClient.SqlParameter();
                  param_part.ParameterName = "@part";
                  if(xCond.Value == "like"){
                       //where += " AND p.PART_NUMBER LIKE '" + sPart.Replace("*", "%") + "'";
                       where += " AND p.PART_NUMBER LIKE @part";
                 param_part.Value = sPart.Replace("*", "%");
                 cmd.Parameters.Add(param_part);
                  }else{
                       //where += " AND p.PART_NUMBER = '" + sPart + "'";
                       where += " AND p.PART_NUMBER = @part";
                       param_part.Value = sPart;
                 cmd.Parameters.Add(param_part);
                  }
             }catch (Exception ex){

             }

             try{
                  XmlNode xManfNum = this.dom.SelectSingleNode("/Item/manf_part_number/text()");
                  XmlNode xCond = this.dom.SelectSingleNode("/Item/manf_part_number/@condition");
                  string sManfNum = xManfNum.Value;
                  System.Data.SqlClient.SqlParameter param_manfnum  = new System.Data.SqlClient.SqlParameter();
                  param_manfnum.ParameterName = "@manfnum";
                  if(xCond.Value == "like"){
                       //where += " AND b.[Manufacturer PN] LIKE '" + sManfNum.Replace("*", "%") + "'";
                       where += " AND b.[Manufacturer PN] LIKE @manfnum";
                 param_manfnum.Value = sManfNum.Replace("*", "%");
                 cmd.Parameters.Add(param_manfnum);
                  }else{
                       //where += " AND b.[Manufacturer PN] = '" + sManfNum + "'";
                       where += " AND b.[Manufacturer PN] = @manfnum";
                 param_manfnum.Value = sManfNum;
                 cmd.Parameters.Add(param_manfnum);
                  }
             }catch (Exception ex){

             }
             
             try{
                  XmlNode xManf = this.dom.SelectSingleNode("/Item/manufacturer/text()");
                  XmlNode xCond = this.dom.SelectSingleNode("/Item/manufacturer/@condition");
                  string sManf = xManf.Value;
                  System.Data.SqlClient.SqlParameter param_manf  = new System.Data.SqlClient.SqlParameter();
                  param_manf.ParameterName = "@manf";
                  if(xCond.Value == "like"){
                       //where += " AND b.[Manufacturer] LIKE '" + sManf.Replace("*", "%") + "'";
                       where += " AND b.[Manufacturer] LIKE @manf";
                 param_manf.Value = sManf.Replace("*", "%");
                 cmd.Parameters.Add(param_manf);
                  }else{
                       //where += " AND b.[Manufacturer] = '" + sManf + "'";
                       where += " AND b.[Manufacturer] = @manf";
                 param_manf.Value = sManf;
                 cmd.Parameters.Add(param_manf);
                  }
             }catch (Exception ex){
                  //sw.WriteLine("No Manf found Found");
             }


             try{
                  XmlNode xDesc = this.dom.SelectSingleNode("/Item/description/text()");
                  XmlNode xCond = this.dom.SelectSingleNode("/Item/description/@condition");
                  string sDesc = xDesc.Value;
                  System.Data.SqlClient.SqlParameter param_desc  = new System.Data.SqlClient.SqlParameter();
                  param_desc.ParameterName = "@desc";
                  if(xCond.Value == "like"){
                       //where += " AND des.[Description] LIKE '" + sDesc.Replace("*", "%") + "'";
                       where += " AND des.[Description] LIKE @desc";
                 param_desc.Value = sDesc.Replace("*", "%");
                 cmd.Parameters.Add(param_desc);
                  }else{
                       //where += " AND des.[Description] = '" + sDesc + "'";
                       where += " AND des.[Description] = @desc";
                 param_desc.Value = sDesc.Replace("*", "%");
                 cmd.Parameters.Add(param_desc);
                  }
             }catch (Exception ex){
                  //sw.WriteLine("No description Found");
             }
     
         //cmd.CommandText = "SELECT COUNT(*) FROM  dbo.AllParts WHERE PART_NUMBER LIKE 'CT%'" + where;
            cmd.CommandText = "WITH PARTS AS(";
          cmd.CommandText += "SELECT *, ROW_NUMBER() OVER (ORDER BY p.PART_NUMBER) AS 'RowNumber'  FROM  dbo.AllParts AS p WHERE p.PART_NUMBER LIKE 'CT%'"; 
            cmd.CommandText += ")";
            cmd.CommandText += "SELECT COUNT(*) FROM PARTS as p "
            + "INNER JOIN dbo.[Component Manufacturer Parts] AS a ON p.RowID = a.PartGUID "
            + "INNER JOIN dbo.[Manufacturer Part Info] AS b ON a.ManufacturerGUID = b.RowID " 
            + "LEFT Join dbo.[CT_PART_DESCRIPTIONS] AS des ON des.RowID = p.RowID ";                      
            //+ "WHERE 1" + where;
            if(where != ""){
                 cmd.CommandText += "WHERE 'monkey' = 'monkey' " + where;
            }
            
            
            //sw.WriteLine("sql: {0}", cmd.CommandText);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;
         
            int count = (int) cmd.ExecuteScalar();           
         //sw.WriteLine("Found: {0} parts", count);
         
         //calculate the start and stop
         int iStart = (iPage - 1) * iPageSize + 1;
         int iEnd = iPage * iPageSize;
         int iPageMax = count / iPageSize;
         
         //is there an extra non full page?
         if(iPageMax * iPageSize < count){
              iPageMax++; 
         }
         
         //looking for more records than exist
         if(iEnd > count){
             iEnd = count;
             //iStart = iEnd - iPageSize;
             
             if(iStart <0){
              iStart = 0;
             } 
         }
         
         //now execute query for real
         /* cmd.CommandText = "WITH PARTS AS(";
          cmd.CommandText += "SELECT *, ROW_NUMBER() OVER (ORDER BY p.PART_NUMBER) AS 'RowNumber'  FROM  dbo.AllParts AS p WHERE p.PART_NUMBER LIKE 'CT%'"; 
            cmd.CommandText += ")";
            cmd.CommandText += "SELECT * FROM PARTS as p "
            + "INNER JOIN dbo.[Component Manufacturer Parts] AS a ON p.RowID = a.PartGUID "
            + "INNER JOIN dbo.[Manufacturer Part Info] AS b ON a.ManufacturerGUID = b.RowID " 
            + "LEFT Join dbo.[CT_PART_DESCRIPTIONS] AS des ON des.RowID = p.RowID "                      
            + "WHERE RowNumber Between @low AND @high" + where;
          */
         /* cmd.CommandText = "SELECT *, ROW_NUMBER() OVER (ORDER BY p.PART_NUMBER) AS 'RowNumber'  FROM  dbo.AllParts AS p "; 
            cmd.CommandText += ""
            + "INNER JOIN dbo.[Component Manufacturer Parts] AS a ON p.RowID = a.PartGUID "
            + "INNER JOIN dbo.[Manufacturer Part Info] AS b ON a.ManufacturerGUID = b.RowID " 
            + "INNER Join dbo.[CT_PART_DESCRIPTIONS] AS des ON des.RowID = p.RowID "                      
            + "WHERE RowNumber Between @low AND @high" + where;*/
            
            cmd.CommandText = "WITH PARTS AS(SELECT p.PART_NUMBER, p.RowID, des.description, b.manufacturer, b.[manufacturer PN], ROW_NUMBER() OVER (ORDER BY p.PART_NUMBER) AS 'RowNumber' FROM  [CIP_E].[dbo].[AllParts] AS p " 
            + "INNER JOIN [CIP_E].[dbo].[Component Manufacturer Parts] AS a ON p.RowID = a.PartGUID " 
            + "INNER JOIN [CIP_E].[dbo].[Manufacturer Part Info] AS b ON a.ManufacturerGUID = b.RowID " 
            + "Inner Join [CIP_E].[dbo].[CT_PART_DESCRIPTIONS] AS des ON des.RowID = p.RowID "
            + "WHERE 'monkey' = 'monkey' " + where + ") "
            + "SELECT * FROM PARTS WHERE RowNumber Between @low AND @high";
            
            
      System.Data.SqlClient.SqlParameter param_low  = new System.Data.SqlClient.SqlParameter();
      param_low.ParameterName = "@low";
      param_low.Value         = iStart;
      cmd.Parameters.Add(param_low);

      System.Data.SqlClient.SqlParameter param_high  = new System.Data.SqlClient.SqlParameter();
      param_high.ParameterName = "@high";
      param_high.Value         = iEnd;
      cmd.Parameters.Add(param_high);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;
         //sw.WriteLine("Low: {0} High: {1}", iStart, iEnd);
         //damn you for having no LIMIT function MS SQL
         //sw.WriteLine("query: {0}", cmd.CommandText);
         reader = cmd.ExecuteReader();

         i = 0;    
            while(reader.Read() && i < iPageSize){
          i++;

          Item res5 = this.newItem("CIP_PART");
             string newid = getNewID();
             string id = reader["RowID"].ToString();
             id = id.Replace("-", "");
             res5.setID(id);
             res5 = res5.apply();
             res5.setProperty("part_number", reader["PART_NUMBER"].ToString());
             res5.setProperty("start", iStart.ToString());
             res5.setProperty("pageSize", iPageSize.ToString());
             res5.setProperty("end", iEnd.ToString());
             res5.setProperty("manf_part_number", reader["Manufacturer PN"].ToString());
             res5.setProperty("manufacturer", reader["Manufacturer"].ToString());
             res5.setProperty("description", reader["description"].ToString());
             res5.setAttribute("page", iPage.ToString());
             res5.setAttribute("pagemax", iPageMax.ToString());
             res5.setAttribute("itemmax", count.ToString());            
             resultNd.AppendChild(resultNd.OwnerDocument.ImportNode(res5.node, true));
             }
         
         break;
        default:
            break; 
    }



    ////// I used to have everything in a try catch but it's hard to debug that way ;)  So left this here
    /////  as a place holder.
    try{

    }
        catch (Exception ex)
    {

    }
    finally
    {
     //close db connection
        conn.Close();
    }
    
    //sw.Flush();
    //sw.Close();
    return res;

Alright now I'll try to break it down a little bit to explain what I did here. The first part is the sql connection to the CIP database. For me the CIP database resides on the same MS SQL EXPRESS 2008 server as the CIP database does.

    //This is the SQL connection string to connect to our SQL Express database.  
    //Note the login details are the default CIP internal user according to EMA's
    //documentation.
    System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection ();
    conn.ConnectionString = 
     "data source=MININT-PHRAUUK\\sqlexpress;User Id=CIP_E_Internal_User;Password=bra#ru5=phaT;" +
     "initial catalog=CIP_E;";
    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
    System.Data.SqlClient.SqlDataReader reader;

The above code use the C# .net SqlConnection class to make a connection to our CIP database. Note the connection string which is the most important part. MINIT-PHRAUUK is the name IT gave my machine and sqlexpress is the instance of my sqlexpress server. The user ID and Password are the default user ID and password for the internal CIP user from EMAs setup docs. If you changed this you'll need to enter your info. Note this is not the same as the CIP user login, this is the login CIP itself uses. Finally initial catalog is set to CIP_E which is the default name of your CIP database.

The last two lines just create the sql command we'll use later, and the reader we'll use to read data out of the database.

    //these two items are what is going to get returned
    Item res = this.getInnovator().newResult("");
    XmlElement resultNd = (XmlElement) res.dom.SelectSingleNode("/*/*/*");

The above two lines create a new Item, called res and an XmlElement that points to res. Res will be what we end up returning when we are call, and resultNd will help us write other Items into the Res result set.

Before the next part I'll go off track a little in case you're totally new to Aras. Everything that happens in Aras happens with an AML request and response. Something that helped me a lot was when someone on the forum pointed out to me that you can save all the AML requests that Innovator sends, even the ones for the standard built in functions. Just go to Tools->Admin in the top menu bar and select "save all requests". Now all your requests will be saved in your working directly. Which incidentally you can set under Tools -> preferences

Now you can do something in Innovator and see what kind of requests it makes. As an example here's the request that gets sent when I try to List All CIP Parts in Innovator:

 
    int iPage = 1;
    int iPageSize = 25;
    try{
         XmlNode xPage = this.dom.SelectSingleNode("/Item/@page"); 
         XmlNode xPageSize = this.dom.SelectSingleNode("/Item/@pagesize"); 
    
         if(xPage.Value != "" && xPageSize.Value != ""){
             iPage = int.Parse(xPage.Value);
             iPageSize = int.Parse(xPageSize.Value);
         //action = "list_all"; 
         } 
         
         XmlNode xAction = this.dom.SelectSingleNode("/Item/@select");
         string sAction = xAction.Value;
         if(sAction != ""){
              action = "list_all";
         }
    }catch (Exception ex){

    }

So every time your onGet function gets called it is passed an AML request like we just talked about. The code above is using Xpath notation to parse that request and get the page and pagesize variables. These are sent from the UI if you choose to have say only 5 records returned per page or use the little up and down arrows to move through the page results.

 
 string sID = "";
    try{
         XmlNode xAction = this.dom.SelectSingleNode("/Item/@action");
         XmlNode xIdlist = this.dom.SelectSingleNode("/Item/@idlist");
    
         if(xAction.Value == "get" && xIdlist.Value != "")
         {
             //convert ID
             string sIdlist = xIdlist.Value;
             if(sIdlist.Length == 32){

                 sID = sIdlist.Substring(0, 8) + sIdlist.Substring(8,4) + sIdlist.Substring(12,4) + sIdlist.Substring(16,4) + sIdlist.Substring(20,12);      
                 action = "get_single";
             }
         }
    }catch (Exception ex){

    }

The code above is doing something similar, but here we're getting the action and id list variables. Then in a bit of a hack I'm checking to see if the id list only has one id in it and if it does I decide this is a single get request.

Aras Innovator Orcad CIP Part Federation

2012-01-17T14:13:00.000-08:00

Ok step one for me was to try to figure out how to get all those CIP PARTS into the ARAS user interface.

Step 1 Create a Federated Part
First make sure you are logged in as an Administrator, then in the Table of Contents, select Administration->Item Types->New Item Type

Step 2 Fill out form
Now you need to fill out the form that appears, all I filled out in the top form was the name as CIP_PART, and then make sure you click the Federated radio button. Federated basically means "comes from some other source or database". Now that I think about it I might have just made a copy of the standard Innovator PART and then modified it to be like this.

Now I also had to setup a few things in the lower section, first I wanted to make sure I could get to these parts from the table of contents (what they call the main menu on the left hand side of innovator). So I went to the TOC Access tab and added CIP_PART to the Design menu.

Next I wanted to make sure no one could try to add one of these parts manually, and just to basically disable an field editing. So I went into Can Add and unchecked the checkbox there.

Finally since I'm going to be providing the CIP PART data from our CIP database I need to add an event handler for the onGet event. Basically this means whenever Innovator tries to get a CIP PART, it will call my code instead of their standard code. Oh and ignore the onUpdate event handler, I never got around to that ;)

To create a new event handler just choose Create Related from the select box and click the new button right next to it (see the red arrow above). Give the method a name, mine is CIP_GET, choose CSharp, and select onGet as the event type.

That's it, save your new part and in the next step I'll go through my onGet function.

Aras Innovator Orcad CIP CIS Integration

2012-01-17T14:12:00.000-08:00

While looking around for a cheaper PLM solution I came across ARAS's open source model. The tool itself looked great, but I needed it to integrate with our Orcad CIP system we use from EMA. I quickly found out that there were commercial solutions to this in the $15k range, a lot more than I wanted to spend just for that feature. The other problem I'd encountered with this, and other PLM systems is that often they expect to be the center of the world when it comes to part data management. If you're a CIP user though then I bet like me you don't want to give up CIP's direct integration into Digikey, or Arrow, or your favorite vendor's catalog. I mean it's nice to be able to look up a part, make sure I can buy it at Arrow and Digikey for both large and small runs, then automatically have all that data populated in our database.

The goal of this project was to bring the component data from CIP into ARAS, be able to assign it to other parts in ARAS based on the BOMS coming out of Orcad CIS. All while having CIP be the only source of the actual component data. I should warn you that the following is likely the wrong way to do things, a massive hack, and quite possibly a mess. I only put about two to three weeks worth of effort into this before I was pulled off. I should also warn you that, like the blog title says, I am an electrical engineer. People come to me when they want to know why they're seeing cross talk, or a board won't boot properly, not when they want to know the right design pattern, or syntax to use.

I put this together by basically reverse engineering how ARAS worked, asking questions on their forum, and a lot of trial and error. Saying reverse engineering makes it sound like I did something ARAS didn't want me to do, on the contrary they were very helpful to me on the forum, and in fact they pointed me to the tools and methods to do the reverse engineering in the first place. Below is the list of things I did to make this work.

All disclaimers aside here's what I did:

Brought the CIP component data into Innovator as federated "CIP PARTS"
Made a custom database view in the MS SQL CIP database to get all the part descriptions into one database.
Made a fake part in Innovator called CIP PART BOM to act as the container for the CIP PARTS and overloaded it's onGet function to run my own code.
Made a new item in Innovator called CIP LOADED BOM to keep track of the relationship between CIP PARTS and Innovator Parts
Some other things I've likely forgotten at the moment but should come out in the following posts.

Flex As3crypto + php + rsa + openssl

2009-07-16T13:27:00.000-07:00

Flex As3crypto + php + rsa + openssl
Well damn, all I wanted to do was encrypt a little data in my flex application upload it to php and decrypt it there. Of course that turns out to be incredibly complicated. So to all those who will come after me here’s an example of how I generated a 1024 bit RSA key pair with openssl, and used the public key to encrypt a phrase in flex, and the private key to decrypt the phrase in php. First a disclaimer though, I don’t know a lot about encryption or even a lot about what I’m doing but it seems to work and maybe you’ll find it useful.

PHP and openssl
If you want to play along you’re going to need php installed with openssl support. If you make a php file and put this in it:

phpinfo();

and you scroll through the php info page and you don’t see this: Then you don’t have php and openssl working together, if you want to continue you’ll need to figure that out on your own. It’s not too hard though.

As3Crypto Build and Install
I’ll assume you’ve figured out your php and openssl configuration, so now it’s time to get the as3crypto library. DON’T JUST DOWNLOAD THE BINARY VERSION. I can’t stress that enough don’t just go to Hurlant’s website and get he latest binary version. Instead use svn to get the latest code base:

svn checkout http://as3crypto.googlecode.com/svn/trunk/ as3crypto-read-only

If you don’t have svn or you don’t want to compile it yourself… you’ll be sad that you didn’t. Believe me I tried the easy way and it just didn’t work.

Once you get it downloaded get a command line or terminal window and go to the download directory. You can compile with this command:

compc -load-config=build-swc.xml

You’ll probably have to find out where your compc is located if it’s not in your path, and instead run something like:

/pathToCompC/compc –load-config=build-swc.xml

I also ran into the problem of it not knowing where playerglobal.swc was so I ended up editing that line in build-swc.xml to fit my installation. For me it was:

${flexlib}/libs/player/10/playerglobal.swc

When you finally get it to compile it will put the file as3crypto.swc in the bin folder where you found build-swc.xml. Either leave that file there or copy it somewhere you like to keep these things.

Make yourself some keys
The next thing you’re going to want is a key pair. I made mine on my linux server using openssl. For me this is the same server I’m running php on. I’m sure there are a bunch of ways to generate the key pair but I followed the advice found here:

http://www.devco.net/archives/2006/02/13/public_-_private_key_encryption_using_openssl.php

To make a private key just do:

openssl genrsa -out private.pem 1024

Then to extract the public key out of that private key do this:
openssl rsa -in private.pem -out public.pem -outform PEM -pubout

Now you have two files private.pem, and public.pem that we’ll use in the next section.

Flex Code
All right enough BS here’s the code I used to encrypt a small phrase in my Flex-Air application.

            import com.hurlant.crypto.symmetric.ICipher;
            import com.hurlant.crypto.prng.Random;
            import com.hurlant.crypto.Crypto;
            //import com.hurlant.util.Base64;
            import mx.utils.Base64Encoder;
   
            import com.hurlant.util.Hex;
            import com.hurlant.util.der.PEM;
            import com.hurlant.crypto.rsa.RSAKey;


            private function sign():void
            {
             
             var publicKey:String = "-----BEGIN PUBLIC KEY-----\n" +
                 "MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDIyrrhK7L1JQATG1zu/4ktyDdj\n" +
                 "Y41EIZxa5vUiq+ejUlQXNWmQZw8ATDGV2x/xJEcsZSbPqcxgyqDPQ8kGjQkv1kS/\n" +
                 "5ZP3oru5WSEypVPq1nfMCBoGTspyYIRpS+MjhhhN2X5y0NzR08kQQA81GRvBpmxM\n" +
                 "Ymy1r50CmtcVuIbuKwIDAQAB\n"+
                 "-----END PUBLIC KEY-----\n";
                         

                //var rsa:RSAKey = RSAKey.parsePublicKey(getModulus(), exponent.text);
                var rsaPublicKey:RSAKey = PEM.readRSAPublicKey(publicKey);
                var data:ByteArray = new ByteArray;

                data.writeUTFBytes("fuck this crazy ass crap");
                var dst:ByteArray = new ByteArray;
                rsaPublicKey.encrypt(data, dst, data.length);
               
                var Enc64:Base64Encoder = new Base64Encoder;
                Enc64.encodeBytes(dst);
                trace(Enc64.toString());
    
                //use this on linux side to test
                //openssl rsautl -decrypt -inkey mykey.pem -in test.out -out decrypted.txt
                ///usr/bin/openssl enc -base64 -d -in test.64 -out test.out



            }

The first thing you’ll notice is my public key that I got in the previous step. I have no idea if the \n’s are necessary but I put them there because I saw other people do that so monkey see monkey do.

Next I read the key into an RSAKey.

Then you make yourself a new Byte array to store the phrase you want to encrypt . In this example it’s called data.

Then you write the phrase into the byte array.

Now you make yourself another byte array called dst, this is where the encrypted data will go.

Next you base64 encode the data so you can easily copy and paste it. Base64 encoding just encodes all that encrypted data into normal text characters that you can safely drag around.

Finally you output that data with a trace so you can get it easily. For me the trace outputs:

XTLpqt9mNtyFchsjvFnffEplqaa+LHXCh52DpLi9CxvED4fUq5cWPuTX/1tHeG7deNBiGBiRW02r +LfW3JreVSkkc+sH6rq4ggKWi+1iPZoQd45f2VmwHVTvgC/IQX4eRuyFtSVoheHSLRL8zRO2/M27 K2jx49V+4JFVhtFAbQw=

This is your encrypted base64 encoded data, that we’ll decrypt in the next section.

PHP Time
I don’t know how you plan on getting your encrypted phrase to php, and frankly I don’t care. For me I just wanted to see it work so all I did was copy and paste. Here’s the PHP code I used to decrypt the phrase:


$privkey3 = "-----BEGIN RSA PRIVATE KEY-----
MIICXAIBAAKBgQDIyrrhK7L1JQATG1zu/4ktyDdjY41EIZxa5vUiq+ejUlQXNWmQ
Zw8ATDGV2x/xJEcsZSbPqcxgyqDPQ8kGjQkv1kS/5ZP3oru5WSEypVPq1nfMCBoG
TspyYIRpS+MjhhhN2X5y0NzR08kQQA81GRvBpmxMYmy1r50CmtcVuIbuKwIDAQAB
AoGAbMucEqGQ589SiQfMhRskgw/3Iv0v7/ieNYrqzAyFCDN1qNWiC0IsGKcwPthq
KEjV37I3I1IHkXJ5S5SY+dm1XaSrM0XpycCCz/OYYZMJ4Z5qQNyeCIDt69UXHlaz
AIITK7rLcGIOERVp80ZtTXfVv+2oLo7IQLHxntQaP2sOIQECQQD/v7QdW8Zm2Zmr
jAAcnmvGdctPKZmg2fsembqI8xS3QjsPK4Fky2r4CWFu9V+pfIQH2i6L0P0SDZ5n
wHMcg9LrAkEAyP01wruazceeh2LIMAKsdtOudXCYv/Hm9utdTGTPWDBdAKFFJ2O5
GuicqUXeVqP1DjZTtpQ6MWBEKekXN3YBwQJBAOeW3zFes/Dax08Svd8sjgfvDyYm
U5nXCpGRP9tX08CHVxfLm6Z8leb6B6MEy5WczaqWpmOx4hkBABqPqS1/KfUCQHIh
j0iwPZzC0RuyFVll+0dStwuLT8IXfH8UchtyV2eNtxIngdx3PWKxWlypBzON0Rcr
9GeGTnFBaBOggAWHZkECQCGou7Uu8jWPZZ3tFgtAGV+W5iPdj5LGoGu6L7EYAy6u
6jAj8SDB6UrTPDMx1Jjv87fJPCJy93CJI4PRWTu078Q=
-----END RSA PRIVATE KEY-----";



$result = openssl_pkey_get_private($privkey3);

if ($result)
{
        print "\nPrivate Key OK\n\n";
} else {
        print "\nPrivate key NOT OK\n\n";
}

$crypttext = "XTLpqt9mNtyFchsjvFnffEplqaa+LHXCh52DpLi9CxvED4fUq5cWPuTX/1tHeG7deNBiGBiRW02r
+LfW3JreVSkkc+sH6rq4ggKWi+1iPZoQd45f2VmwHVTvgC/IQX4eRuyFtSVoheHSLRL8zRO2/M27
K2jx49V+4JFVhtFAbQw=";

$crypttext = base64_decode($crypttext);


openssl_private_decrypt($crypttext,$newsource,$result);
echo "
String decrypt : $newsource 
";

while ($msg = openssl_error_string())
    echo $msg . "
\n";

First you’ll see that I took the private key from the keys we made earlier and put it in this file. Make sure yours looks the same, if you paste your private key without the right new lines it won’t work. At least it didn’t for me so must make sure you format it like you see in the code.

Next we call openssl_pkey_get_private on that private key.

Now you’ll see the encrypted phrase from our Air application in the $crypttext variable.

We base64 decode that to get back to the raw data.

Then we run it through openssl_private_decrypt to decrypt the data.

Finally we just output the data to the screen.

That last little while loop will output any openssl errors that might have occurred, and can be at least a little useful in debugging.

The End
Well I hope that helps someone else who comes along and tries to do the same thing. It was a pain in the ass to figure this all out especially because as3crypto has no documentation. I’m not knocking it though I mean you just got a completely free cryptography library for your flash project right.

One last thing if you, like me, know about as much about cryptography as you do about how to win the lottery, then you may be disappointed to find out you can only use RSA to encrypt a relatively small amount of data (it’s dependant on your key size). So most people use this to either exchange keys, or make a hash of what they’re sending and encrypt that.

Of course if you happen to know a lot about how to win the lottery... drop me an email with some tips.

datepicker is not a function

2009-03-30T08:46:00.001-07:00

AUGGGHH! Why doesn't this work! Such is the cry of any hacky developer running around cobbling together pieces of code and examples to get something to work. All while trying to learn whatever he's working on at the same time. Anyway recently I tried to use jQuery's datepicker function in one of my pages. I consistently got the error datepicker is not a function. I tried all sorts of things but nothing worked. Well it turns out I was using prototype and another javascript library called tablekit. Now they both use the $ as a shortcut to refer to themselves, and so does jquery. So when you try to use them together there's a conflict. Fortunately jQuery provides a very nice solution to this (you can read more about it here.

Here's what I ended up doing:

<!-- jQuery JS Includes -->
<script src="javascripts/prototype.js" type="text/javascript"></script>
<script type="text/javascript" src="jquery/jquery-1.3.2.js"></script>

<script>
  var $j = jQuery.noConflict();
</script>

<script type="text/javascript" src="jquery/ui.core.js"></script>
<script type="text/javascript" src="jquery/ui.datepicker.js"></script>
<script src="javascripts/scriptaculous.js?load=effects,builder,dragdrop" type="text/javascript"></script>

Now to you use $j instead of $ in all your jQuery code.

     // Use jQuery via $j(...)
     $j(document).ready(function(){
       $j("div").hide();
     });
     
     // Use Prototype with $(...), etc.
     $('someid').hide();

Part 3 LCDS Insync Example, it works and I'm fed up.

2009-03-15T06:44:00.000-07:00

Ok so I got this example to work and there was much rejoicing, followed by much disappointment but I'll save that for the end. There are a ton of steps, and missing one means it's not going to work so I'll try to lay it out for you.

Step One: First you need to create a project in flex builder. This really isn't that hard, just start a new air project and call it insync. When it's done just drag everything from the flex source into the src directory of your new project. Let it overwrite insync.mxml. There now you should have your very own project to play with.
Step Two: It's been a while since the original author wrote this insync sample so if you don't want an error that says:"error while loading initial content" then you need to go into your insync-app.xml. At the second line there is an application block, change it to say .
Step Two:Ok now you need the lcds libraries in your path or else you won't be able to compile against them. If you try you'll just get a bunch of errors which I forget at the moment but they basically say "GET THE LIBRARIES" :) So where are they? If you're like me they're hidden in the war files. Go to your lcds-samples.war file, and unzip it again. Now under the WEB-INF directory you just made by unzipping the file, there is a flex directory. Under that directory are two folders libs and locale. That's where your libraries are. You can copy them someplace else or leave them where they are that's up to you. Next you need to add them to your path. In Flex Builder go to project properties, then go to the Flex Build Path tab. Now you need to add two SWC folders, just click on the SWC folder button and add libs and locale. Now you need to add two SWC files. The first is the airfds.swc file, it's in that libs folder you just added, in the air directory. The last file you need to add is in the locale directory, under the en_US directory, and it's called fds_rb.swc. Just add that file and you're good to go
Step Three: At this point you should be able to build. So give it a shot. If you start up your jboss server from part two and start the sample database, then try to run your application you'll get this error: Error: Unable to initialize destinations on server: ["nameOfDataService"] Sigh more errors, unfortunately this one is a bug in lcds, and it's documented here You'll notice there are others that feel our pain, and at the bottom Jeff from Adobe invites people to email him to receive a fixed copy of a library. Which I did yesterday, which was Saturday so we'll see what I get back. In the meantime his advice to compile in your services descriptions led me to come up with the following modifications to the compiler command line. Just go back to Project --> properties in Flex Builder. Now under the Flex Compiler tab, add this to Additional Compiler Arguments: Fill in your directory paths: -locale en_US -services {your path to jboss}/jboss-4.2.2.GA/server/default/deploy/{your directory you unziped lcds-samples.war to}/WEB-INF/flex/services-config.xml -context-root=http://localhost/ Mine looks like this: -locale en_US -services /Users/Downloads/jboss-4.2.2.GA/server/default/deploy/test26/WEB-INF/flex/services-config.xml -context-root=http://localhost/

Ok well now at this point you should be able to compile and run the program yourself. You should see the data populate into the application, and if you see the little green online light in the bottom corner you should be able to make changes. I was pretty happy at this point so I decided to try a little testing, and of course more issues. I don't think it's the original authors design, but more some lingering issues with LCDS but that's just one mans opinion. Try this little test, start your application with the server running and get the contact data, then shutdown the server to simulate a loss of connection. Now add a contact in the insyc application and close it. Now according to the theory when we start up I'd think it would load the contacts from cache since it's offline and if the server is online it should update the sampledb. Not so, if you try to startup with the server started you'll get a couldn't fill message. If you're inclined to dig a little further and debug it you'll see messages about it not being able to initialize the datasource. If you want to fix your problem just go delete the sqlite database it's using. On a mac that's under. /Users/username/Library/Preferences/insync/Local Store/_ds_localstore You'll see the file you want is named after the datacache id set in your insync program. So this is where I stopped, lcds was incredibility complicated to setup, and it seems like there are still bugs to be worked out. These are exactly the type of problems I'd hoped to avoid by using it. So my conclusion is that for what I want to do it's a lot easier to just write it myself. I'm thinking I'll just post signed XML requests to a PHP script for proof of concept, then when I want it to go faster I'll just write a server in C. I hope this helps at least someone out there. Good Luck

Using a Wii with a PC monitor and keeping your PC sound

2009-03-14T11:02:00.000-07:00

Recently a friend of mine presented me with a problem and asked me to help. He had and his wife were both using the Wii and Wii fit to exercise. They had moved their Wii to their upstairs office / exercise room and wanted to hook it up to their PC monitor. Then they'd gone to Best Buy who sold them a video capture card and told them they could use that to hook it up to their PC. That's where I came into the situation. Well you probably won't be surprised to learn that that worked about as well as a marketing guy trying to use an oscilloscope. The quality was terrible, there was significant lag from the time it took the card to process the video, the resolution was poor and finally the program that displayed the video was complicated and crashed a lot. The first part of the solution was to take that video card back where it came from. Next we ordered a Wii cable that converted the component video output to a vga output. I didn't take it apart since it wasn't mine but there isn't much too it, and you could probably make your own but for $35 it's not worth the effort. Plus it was for non technical people so the easier the better. The only issue we had was the cable only worked in 480p mode. Not a big deal but if you don't have a component cable already then the Wii won't go into 480p mode. They didn't have a cable, but I did so with two more trips the problem was solved. The nice thing was they had an lcd monitor with two inputs which meant I could leave both hooked up and they could just switch them whenever they wanted. Of course they only had one set of speakers so I left that hooked up to the Wii. It wasn't long before I got the call, "so how do we get sound out of the PC and the Wii?". Now the simple answer is swap plugs, but that's a pain. I want this to be easy. My next instinct was, "ah we just need to buy a y adapter". When you think about it that's a pretty bad idea too. Yeah it will work, there's a pretty good chance you'll get some feed back noise. Even worse is now you have the possibility of two output drivers competing with one another. That should work for a while, heck it might even work forever, but if a year from now the output driver on the Wii dies we'll all know who's fault it is. Also what if you want to exercise but also listen to some mp3s at the same time? Well I thought back to the first electronic product I ever made and tried to sell myself (note the tried part :). It was called the Audiovive and the idea was simple. I was in college and I wanted to play video games and listen to my music at the same time. Like most techy guys I had a tv, a receiver, some surround sound and more importantly a soldering iron. The solution was the little guy below: It's nothing more than a passive two channel mixer, nothing fancy and it gets the job done. I was going to just give them this, but it was the only one I had left and being a little sentimental I decided I'd rather just build them another one. So for a few bucks in parts from digikey I whipped up a simpler version that I'll go through below. First off I ordered the following:

3 of these headphone jacks CP1-3523N-ND
1 of these pots P2B6203-ND

I chose headphone jacks because I have a ton of 3.5mm jack to RCA converters in my garage from the Audiovive project. I chose that pot because it was cheap, had two elements, and was logarithmic so we'd have better volume control. This is just your basic resistive mixer, no input caps no nothing. Here's the schematic. That's about it. It's pretty simple to wire up. When you're done you can turn the pot and it will balance the amount of sound sent from the two inputs to the output.

Part Three Getting a sample flex app to work

2009-03-14T08:31:00.000-07:00

Well prior to all this some googling had brought me to Christophe Coenraets great page on getting LCDS to work with a Flex application. He was nice enough to write an AIR based contact management application that uses LCDS to connect to the sample database. He includes all his source code, a compiled version of the application, as well as the server side code and compiled java classes. I highly recommend reading through all of this as it helped me understand how all this works. Check it out here. Now download all the project files, and install the air application. We'll use that as our litmus test to whether this works or not. Since I'm using JBoss my setup was a bit different than his. I don't have any of the directories he's talking about and just dumping the files into the deploy directory doesn't work. This brings me to the point of actually learning what a .war file is. I'm sure there's a complex explination but it turns out it's just a zipped up collection of files. In there are the java classes, some configuration xml files, you name it. JBoss seems to like to use those so that's what we'll have to do. Now since you've been following along you have an lcds-samples.war file in your deploy directory. This part took a while for me to understand, maybe I was just slow that day. What the example wants you to do is take his files and put them in the classes directory. But where is that directory? Try searching for it you won't find it. The ah ha moment for me was when I realized that it was "inside" the lcds-samples.war file. Maybe that's obvious to some people but not to me. So how to we get in there? It's pretty simple just unzip the .war file. Watch it thought it just expands into whatever directory you're in. What I did was create another directory called test, still under the JBoss default/deploy directory and then I moved lcds-samples.ware into test. Then I opened up another termial window and navigated to that directory. Now just type unzip lcds-samples.war and behold it creates the whole directory structure before your very eyes. You'll find the folder to put Christophe's example under WEB-INF-->flex-->classes. Just drag his lcds folder into here. But wait you're not done yet, go back up one directory level to WEB-INF-->flex. Here you'll find the data-managment-config.xml file that he and most other examples are asking you to add some description code to. If you don't add this when you run his program you'll get an error saying can't fill, and you'll see an exception on your JBoss terminal window. I don't know too much about this but it appears to be a description that tells JBoss how to handle an incoming request and what program to pass it on to. Once you've done all that you need to pack it back up into a war file. I removed the original lcds-samples.war file, you may want to move it to lcds-samples.bak. Just go up to the test directory in your terminal window and type "jar -cvf lcds-samples.war *". That should create the new lcds-samples.war file that you need. Think you're done? Not so fast, this example also needs some data. That data is put in the same sample database application that we started up before. Just take the files in his sampledb and copy them into yours. It will want to overwrite server.properties which is ok. If you compare the files you'll see his contains everything the default one does plus his example. So do that restart the database, restart jboss, and try to run his air application. If you see names and contact information appear in the application, then congratulations you got it to work. Next up is getting the flex application code to compile so I can make changes. When I figure that out I'll add it here.

Part Two Getting LCDS

2009-03-14T08:04:00.000-07:00

At this point I really didn't quite get what LCDS was. I mean I know what it does, I know it's some sort of libraries, but what "is" it. So far I'd gathered that it was a set of libraries and some example java applications that run on your JAVA application server. Those JAVA applications can have access to server resources like my MySQL server, and they can run as little servers that respond to request from my Flex applications. So a little complicated but not to bad. The first nice surprise about LCDS is although it isn't free, Adobe does have a non expiring one CPU license for developers to use. So that's what you're going to want to get. Now the first time around I installed this on my Ubuntu box, only to find that all of the sample code was set to connect to local host. The next logical step was to install it on my Mac Book but, ahhhhhggg! Adobe doesn't offically support it for the Mac platform. Now I won't rant and rave about that I know there aren't as many Macs out there etc etc, and I do have Windows installed here as a Virtual Machine, but did I really want to do that? No, and thankfully nickul over at adobe was nice enough to post this article on how to install LCDS on a MAC anyway. You can read the original article here, although it didn't quite work for me it inspired me to try. First you need to get LCDS from Adobe and as Nick points out you need the AIX version (the Java Version). I got mine here http://www.adobe.com/products/livecycle/dataservices/. You'll need a free Adobe ID which you should already have if you've downloaded Flex Builder. Make sure you remember to copy down the serial number they give you, it's the first thing you'll need at install. When the file is done downloading just double click on it to start the install, enter your serial number, agree to it's terms and it should finish up on it's own. It will install your new LCDS files in a folder called Adobe, but it puts that folder at the root of your filesystem. To find it just click on your hard drive icon in finder and you'll see it there at the very top level. Well now you've installed it, now what? Well now you need to "deploy" it to your jboss folder. Remember before when I said I have no idea what I'm doing? Yeah well here we are. What you need to do is copy the .war files that are in your new Adobe/LiveCycle Data Services ES 2.5 folder to your JBOSS deploy folder. As I found out JBOSS has several folders where it goes to run java applications with the default one being called... well default. You can find this folder under your root JBOSS folder: JBOSS FOLDER | |---Server| |--default| |--deploy If you put those .war files in that folder and then start up your server you can then go to http://localhost:8080/lcds-samples/ and you should see the LCDS samples page. At this point you can look at the samples but they don't work. That's because they're trying to connect to the sample database that comes with LCDS, and we haven't started it up yet. It wasn't really clearly explained to me that the sample database was actually a sample database server that you need to run but it is, and you'll find docs telling you it's no where close to being the kind of thing you'd want to base a product on but it works. Anyway just open up another terminal window or tab and go back to the Adobe/LiveCycle Data Services ES 2.5 directory. Under here you'll find a directory called sampledb, and inside sampledb is a script called startdb.sh. That's what you're looking for, just start that with a ./startdb.sh, then restart your jboss server. Now you should be able to go to http://localhost:8080/lcds-samples/ and all the samples should be working for you.

Part One Installing JBOSS

2009-03-14T07:50:00.000-07:00

Well the very first thing I did was read through the docs on LCDS on Adobe's website. That convinced me that I needed a JBOSS server if this was going to work. Now later I found that's not entirely true, but I'm not sad that I did it since eventually I want this application to be able to server thousands of people. The next thing I did was to install JBOSS on my UBUNTU server, but that's another story. Before we start you should realize I have absolutely no idea what I'm doing. I can't remember when the last time I wrote JAVA was. Heck I didn't even know what JBOSS was two days ago, and I only vaguely remembered the idea of what a JAVA applications server was. Why let that stop us though? The first step is to go out and get JBOSS for your mac. At the time I'm writing this you can find it here http://www.jboss.org/jbossas/downloads/. It's pretty easy, you just download it, let stuffit automatically decompress it and you've got JBOSS. In fact I'm so lazy I'm still running it out of my Download folder. So this is great now you have jboss but how the heck do you run it? Well in your new jboss directory (mines jboss-4.2.2.GA) there are a lot of directories. The one you're looking for is the "bin" directory. Inside the bin directory are several scripts, and if you just run the run.sh file, your new jboss server will begin to start up. Just do a "./run.sh". You should now see a bunch of console output from the server and at the very end it will say something like: "19:12:57,095 INFO [Server] JBoss (MX MicroKernel) [4.2.2.GA (build: SVNTag=JBoss_4_2_2_GA date=200710221139)] Started in 21s:313ms" That means you're good to go. Now open up your favorite browser and go to http://localhost:8080 and you should see the JBoss welcome page.

Installing Adobe Live Cycle Data Services, with JBOSS, and getting an example to work in FlexBuilder All while on a MAC Introduction

2009-03-14T07:33:00.000-07:00

Ok, first the disclaimer. I'm a hardware engineer, I shouldn't be writing code to begin with, but I do on the side. Now if you're like me whenever you're trying to learn something new it's a mixture of trying, and googling, lots of googling. So I decided that after years of leaching off of others posts and instructions that I'd start writing down what worked for me. At worst it's just a record for me, at best maybe it helps someone. I'd been learning to write AIR applications using Adobe's Flex Builder and I was just at the point where I wanted to store things in my Mysql database on my server. My first instinct was to just write some PHP code and call it from the air app, but I wanted more than that. I wanted everything to work while I was offline too, and thats when I came across Adobe Live Cycle Data Services. I even found some great sample code out there that should run in flex buiilder. So I embarked on a journey to get this all installed.