Wednesday, January 18, 2012

Aras Innovator Import Orcad CIS BOM

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!

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

CIP Innovator Association

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

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.

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

Linking Orcad CIP parts to Innovator parts

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.






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

Tuesday, January 17, 2012

Aras Innovator Orcad CIP onGet Part 2

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.

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

Aras Innovator Orcad CIP Part onGet Part 1

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

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.