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.
No comments:
Post a Comment