function CACF_AnalyzeVaultItemUserParameters(&$db, &$CACFconstants, &$altiumUserParmNames, &$altiumItemUserParmValuesByGuid)
{
/* Setup query SQL commands. */
$queryText = '
SELECT ITEMREV.GUID, IRP.HRID AS PARAMETERNAME, IRP.PARAMETERVALUE
FROM ALU_ITEMREVISION ITEMREV
LEFT JOIN
(SELECT ITEM.GUID, ITEM.HRID, ITEM.SHARINGCONTROL, ITEM.HRID AS FOLDERHRID, CONTENTTYPE.HRID AS CONTENTTYPEHRID
FROM ALU_ITEM ITEM
LEFT JOIN ALU_CONTENTTYPE CONTENTTYPE ON ITEM.CONTENTTYPEGUID = CONTENTTYPE.GUID
) ITEMINFO ON ITEMREV.ITEMGUID = ITEMINFO.GUID
LEFT JOIN ALU_ITEMREVISIONPARAMETER IRP ON ITEMREV.GUID = IRP.ITEMREVISIONGUID
WHERE ITEMINFO.CONTENTTYPEHRID = \'altium-component\'
;
';
echo date('H:i:s') . " Begin query to read in all item user parameters from Vault database...\n";
/* Execute SQL query. */
$resultHandle = odbc_exec($db, $queryText);
/* Clear array that will cache all Altium item user parameter names and values, indexed by item revision GUID. */
$altiumItemUserParmValuesByGuid = array();
/* Loop over all rows returned by SQL query. */
while (odbc_fetch_row($resultHandle)) {
/* Extract the field named "PARAMETERNAME" from query results. */
/* Do a regex substiution on one particular parameter name to transform 'Supplier Part Number 1' to 'Supplier 1 Part Number', etc. */
$PARAMETERNAME = CACF_AlterAltiumUserParmName(odbc_result($resultHandle, "PARAMETERNAME"));
/* Store this Altium user parameter name in array. */
$altiumUserParmNames[$PARAMETERNAME] = 1;
/* Extract the "GUID" and "PARAMETERVALUE" fields as well. */
$GUID = odbc_result($resultHandle, "GUID");
$PARAMETERVALUE = odbc_result($resultHandle, "PARAMETERVALUE");
/* See if there is already an array setup for this GUID. */
if (isset($altiumItemUserParmValuesByGuid[$GUID])) {
// echo "Already have an entry at GUID $GUID.\n";
$altiumItemUserParmValuesByGuid[$GUID][$PARAMETERNAME] = $PARAMETERVALUE;
} else {
// echo "Need to make an entry at GUID $GUID.\n";
$altiumItemUserParmValuesByGuid[$GUID] = array($PARAMETERNAME => $PARAMETERVALUE);
}
}
/* endwhile */
/* Free memory that was holding query results. */
odbc_free_result($resultHandle);
/* Examine all $altiumItemUserParmValuesByGuid */
foreach ($altiumItemUserParmValuesByGuid as $GUID => $value) {
foreach ($value as $PARAMETERNAME => $PARAMETERVALUE) {
// print "GUID is $GUID, PARAMETERNAME is $PARAMETERNAME, PARAMETERVALUE is $PARAMETERVALUE\n";
}
}
//print "\n";
/* Add placeholders for some user parameters that may not exist just yet. */
CACF_ReserveGlobalUserParmNames(&$altiumUserParmNames);
/* Sort all Altium user parameter names stored in the array, by array key. */
$rc = ksort($altiumUserParmNames);
if ($rc == FALSE) {
my_die("ksort() failed!");
}
/* Loop over all the defined Altium user parameter names. */
//echo "About to list all defined Altium user parameter names:\n";
foreach ($altiumUserParmNames as $key => $value) {
// print "$key\n";
}
//print "\n";
}
function UCTCF_ExtractComponentsFromExcel(&$CACFconstants, &$altiumItemsByHrid, &$altiumItemRevsByHrid, &$altiumModelDataByItemRevHrid, &$objPHPExcel, &$excelUserParmsByItemHrid, &$excelSysParmsByItemHrid)
{
/* Retrieve necessary global constants. */
$pathSep = $CACFconstants["pathSep"];
$revSep = $CACFconstants["revSep"];
$cRevFormatString = $CACFconstants["cRevFormatString"];
/* Retrieve reference to current Excel worksheet. */
$objWorksheet = $objPHPExcel->getActiveSheet();
/* Allocate arrays to hold user and sys parameters for all components in excel file. */
$excelUserParmsByItemHrid = array();
$excelSysParmsByItemHrid = array();
/* Set state as "search for command". */
$state = "00_search_for_command";
/* Declare $value. */
$value = "";
/* Clear ItemHRID */
$ItemHRID = "";
/* Clear flag that tells us to skip a given line. */
$skipLine = false;
/* Loop over all Excel worksheet rows that have data. */
foreach ($objWorksheet->getRowIterator() as $row) {
// echo "Row: " . $row->getRow() . "\n";
/* Get an iterator for this row. */
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
/* This loops all cells, even if it is not set. By default, only cells that are set will be iterated. */
/* Loop over all columns in this row. */
foreach ($cellIterator as $cell) {
/* Get column letter for future use. */
$rowNum = $cell->getRow();
$colNum = $cell->getColumn();
/* Get datatype of this cell. */
$datatype = $cell->getDataType();
// echo "At row=" . $rowNum . ", col=" . $colNum . ", value=\"" . $cell->getValue() . "\", datatype=\"" . $datatype . "\".\n";
/* If the data type is formula, then let's just go ahead and evaluate it now. */
if ($cell->getDataType() == "f") {
$value = $cell->getCalculatedValue();
} else {
$value = $cell->getValue();
}
// echo "At row=" . $rowNum . ", col=" . $colNum . ", value=\"" . $cell->getValue() . "\", datatype=\"" . $datatype . "\", value=\"" .$value ."\".\n";
// print_r(PHPExcel_Calculation::getInstance()->debugLog);
/* Examine current state and act appropriately. */
switch ($state) {
/* In state "00_search_for_command", search for a recognized command in column A. */
case "00_search_for_command":
/* Look for a valid command in column A. */
if ($colNum == "A") {
/* Look for a "Define components" command. */
if ($value == "Define components") {
echo "Found \"Define components\" command!\n";
/* Clear the column headers array, since we're starting a new component group. */
$colHeadersByCol = array();
/* Advance state. */
$state = "01_get_col_headers";
}
}
/* endif col A */
break;
/* endcase */
/* In state "01_get_col_headers", search for column headers in this row. */
/* endcase */
/* In state "01_get_col_headers", search for column headers in this row. */
case "01_get_col_headers":
/* Make sure we have a non-null value in this cell. */
if ($value != "") {
/* Store this column header. */
$colHeadersByCol[$colNum] = $value;
echo "Found col header: \"" . $value . "\", at column " . $colNum . ".\n";
}
break;
/* endcase */
/* In state "02_get_component_parms", get component path from column A and user/sys parms from any other
column that had a defined column header. */
/* endcase */
/* In state "02_get_component_parms", get component path from column A and user/sys parms from any other
column that had a defined column header. */
case "02_get_component_parms":
/* Look for a valid path in column A. */
if ($colNum == "A") {
// echo "In column A, value is \"$value\".\n";
/* Look for an Excel string that tells us to skip this line. */
/* Also look for a string "#N/A" indicating that some part of the path couldn't be evaluated. */
$pos = strpos($value, "#N/A");
if ($value == "(skip)" || $pos >= 0 && !($pos === false)) {
/* Set flag that tells us to skip a given line. */
$skipLine = true;
echo "Skipping component with column A set to \"{$value}\".\n";
} else {
if ($value != "") {
echo "Found comp path: \"" . $value . "\", at column " . $colNum . ".\n";
/* Strip off any leading path separators for compatibility with Vault & CmpLib audit files. */
$value = preg_replace("/^" . $pathSep . "\\+/", "", $value);
/* Store component path. */
$compProps["sysParms"][CACF_AlterAltiumSysParmName("COMPONENTPATH")] = $value;
} else {
echo "No comp path! Transitioning back to idle state!\n";
/* Set state as "search for command". */
$state = "00_search_for_command";
}
}
} else {
if (isset($colHeadersByCol[$colNum]) && !$skipLine) {
/* Extract column header name (typically user/sys parm name). */
$colHeader = $colHeadersByCol[$colNum];
// echo "Found user/sys parm value: \"" . $value . "\", for user/sys parm name \"" . $colHeader . "\", at column " . $colNum . ".\n";
/* Strip off any numeric suffixes from a "PCBLIB x" entry. */
$colHeaderStripped = preg_replace('/PCBLIB [0-9]+/', 'PCBLIB', $colHeader);
/* Strip out space and number suffix from PCBLIB. */
/* Store this user/sys parm in the $compProps array. */
switch ($colHeaderStripped) {
/* Handle special case of ItemHRID. */
case "ItemHRID":
/* Store ItemHRID. */
$ItemHRID = $value;
$compProps["ItemHRID"] = $value;
/* Reserve for all models for which we allocate space in our csv file. */
/* Those models actually defined in this component will override the placeholders shortly. */
$sysParms =& $compProps["sysParms"];
CACF_ReserveForModelsInSysParms($CACFconstants, $sysParms);
/* No break! Handling for Comment and Description must immediately follow this code! */
/* Handle system parameters. */
/* Handle system parameters. */
case "Comment":
case "Description":
/* Alter sys parameter name as needed. */
$colHeader = CACF_AlterAltiumSysParmName($colHeader);
/* Store sys parameter name/value pair. */
$compProps["sysParms"][$colHeader] = $value;
break;
/* Handle models. */
/* Handle models. */
case "SCHLIB":
case "PCBLIB":
/* Get a shortcut to ModelItemRevHrid. */
$ModelItemRevHrid = $value;
$ModelKind = $colHeaderStripped;
/* Strip out space and number suffix from PCBLIB. */
$modelTypeWithNum = $colHeader;
/* Leave the number suffixes on PCBLIB. */
/* PCBLIB is allowed to be null. */
if (!($ModelKind == "PCBLIB" && ($ModelItemRevHrid == "" || $ModelItemRevHrid == "#N/A"))) {
/* Make sure that this given model ModelItemRevHrid is valid. */
if (!isset($altiumItemRevsByHrid[$ModelItemRevHrid]) || !isset($altiumModelDataByItemRevHrid[$ModelItemRevHrid])) {
echo "Model \"{$ModelItemRevHrid}\" does not exist as an ItemRevHrid. Proceeding to try to look it up by ItemHrid.\n";
// my_die("Specified model ItemRevHrid \"$ModelItemRevHrid\" does not exist in Vault!");
/* Posit that the given value may be an ModelItemHrid. */
$ModelItemHrid = $value;
/* See if our given value is a plausibly-valid ModelItemHrid. */
if (isset($altiumItemsByHrid[$ModelItemHrid])) {
/* Lookup latest item revision number so that we can eventually get model info. */
UCTCF_GetLatestRevisionNumberOfGivenItem($altiumItemsByHrid, $altiumItemRevsByHrid, $ModelItemHrid, $REVISIONID);
/* Now that we have an ModelItemHrid and a RevisionID, combine them to get an ItemRevHrid. */
$ModelItemRevHrid = $ModelItemHrid . $revSep . $REVISIONID;
echo "Model ItemHrid \"{$ModelItemHrid}\" translates into model ItemRevHrid \"{$ModelItemRevHrid}\".\n";
/* Make sure that this constructed model ModelItemRevHrid is valid. */
if (!isset($altiumItemRevsByHrid[$ModelItemRevHrid]) || !isset($altiumModelDataByItemRevHrid[$ModelItemRevHrid])) {
my_die("Constructed model ItemRevHrid \"{$ModelItemRevHrid}\" does not exist in Vault!");
}
} else {
$ModelLib = CACF_RemapReservedChars($value);
/* Remap any ',' chars in cell to '|' chars! */
echo "Model \"{$value}\" does not exist as an ItemHrid. Proceeding to try to look it up as a ModelLib.\n";
UCTCF_GetLatestItemRevOfGivenModelLib($altiumModelDataByItemRevHrid, $altiumItemRevsByHrid, $ModelKind, $ModelLib, $ModelItemRevHrid);
echo "Got Model ModelItemRevHrid \"{$ModelItemRevHrid}\".\n";
}
/* endelse */
}
/* endif is NULL */
/* Lookup MODELPATH and MODELLIB for this model. */
$MODELPATH = $altiumModelDataByItemRevHrid[$ModelItemRevHrid]["MODELPATH"];
$MODELLIB = $altiumModelDataByItemRevHrid[$ModelItemRevHrid]["MODELLIB"];
/* Add information about this model to $compProps. **/
$sysParms =& $compProps["sysParms"];
$modelHRID = $ModelItemRevHrid;
$modelPath = $MODELPATH;
$modelLib = $MODELLIB;
CACF_AddModelInfoToSysParms($CACFconstants, $sysParms, $modelTypeWithNum, $modelHRID, $modelPath, $modelLib);
}
/* endif */
break;
/* Fix up certain EIA case codes that get mangled by PHPExcel (but not by Excel itself). */
/* Fix up certain EIA case codes that get mangled by PHPExcel (but not by Excel itself). */
case "Case-EIA":
/* Account for the inability of PHPExcel to properly treat a number as text.
Specifically, I cannot find a way to convince it to represent "0603" as "0603" and
not as "603". So I'm kludging things here and tacking on the leading 0 that PHPExcel
is stripping off. */
/* TODO: This is a huge kludge! Find a better way to do this!! */
if ($value == "603" || $value == "805" || $value == "402") {
/* Prepend the leading 0. */
echo "Prepending leading 0!\n";
$value = "0" . $value;
// my_die('This should no longer be necessary! Improve the .xlsx file by using TEXT(foo,"0000")!');
}
/* No break! */
/* endcase */
/* Handle user parameters. */
/* endcase */
/* Handle user parameters. */
default:
/* Alter user parameter name as needed. */
$colHeader = CACF_AlterAltiumUserParmName($colHeader);
/* Store user parameter name/value pair. */
$compProps["userParms"][$colHeader] = $value;
break;
}
/* endswitch */
}
}
/* end elsif */
break;
/* endcase */
}
/* endswitch */
}
/* end foreach cell in column */
/** Perform any needed operations before starting a new row. **/
switch ($state) {
/* Perform end-of-line operations for "01_get_col_headers". */
case "01_get_col_headers":
/* Clear the $compProps array, since we're starting a new component. */
$compProps = array();
/* Advance state now that we're about to start a new row. */
$state = "02_get_component_parms";
break;
/* Perform end-of-line operations for "02_get_component_parms". */
/* Perform end-of-line operations for "02_get_component_parms". */
case "02_get_component_parms":
// print_r($compProps);
// echo "Processing ItemHRID " . $compProps["ItemHRID"] ."\n";
/* Make sure we weren't flagged to skip this line. */
if (!$skipLine) {
/* Make sure we found an ItemHRID field. */
if (!isset($compProps["ItemHRID"])) {
my_die('Did not find ItemHRID defined for this component!');
}
/* Make sure we found at least one userParm field. */
if (!isset($compProps["userParms"])) {
my_die('Did not find any userParms defined for this component!');
}
/* Make sure we found at least one sysParm field. */
if (!isset($compProps["sysParms"])) {
my_die('Did not find any sysParms defined for this component!');
}
/* Lookup latest item revision number for compatibility with Vault & CmpLib audit files. */
UCTCF_GetLatestRevisionNumberOfGivenItem($altiumItemsByHrid, $altiumItemRevsByHrid, $ItemHRID, $REVISIONID);
/* Store revision number. */
$compProps["sysParms"][CACF_AlterAltiumSysParmName("REVISIONID")] = $revSep . $REVISIONID;
/* Get component's ItemHRID. */
$itemHRID = $compProps["ItemHRID"];
/* Make sure that the itemHRID doesn't contain "#N/A" indicating that some part of the part number couldn't be evaluated. */
$pos = strpos($itemHRID, "#N/A");
if ($pos >= 0 && !($pos === false)) {
/* Set flag that tells us to skip a given line. */
echo "Skipping line!\n";
$skipLine = true;
}
}
/* endif */
/* Make sure we weren't flagged to skip this line. */
if (!$skipLine) {
/* Initialize supplier data that we will populate. */
$supplierIndex = 1;
/* Initialize supplier keys. */
$supplierKeys = array("Supplier_Pn_Digi-Key_1", "Supplier_Pn_Digi-Key_2", "Supplier_Pn_Mouser_1", "Supplier_Pn_Mouser_2", "Supplier_Pn_Newark_1", "Supplier_Pn_Newark_2", "Supplier_Pn_Arrow_1", "Supplier_Pn_Arrow_2", "Supplier_Pn_Allied_1", "Supplier_Pn_Avnet_1", "Supplier_Name_Other_1", "Supplier_Pn_Other_1", "Supplier_Name_Other_2", "Supplier_Pn_Other_2");
/* Iterate and look for all of our secret supplier keys in the Excel userParms. */
foreach ($supplierKeys as $foo => $key) {
// echo "Checking for secret supplier key $key!\n";
/* See if this key exists in our data. */
if (isset($compProps["userParms"][$key])) {
// echo "Found secret supplier data as key $key!\n";
/* Extract supplier name from the key. */
$supplierName = preg_replace("/_[0-9]+/", "", $key);
$supplierName = preg_replace("/^Supplier_Pn_/", "", $supplierName);
// echo "supplierName is \"$supplierName\"\n";
/* Convert this supplier secret key into a standard "Supplier x" / "Supplier Part Number x" pair. */
$supplierPartNum = $compProps["userParms"][$key];
// echo "supplierPartNum is \"$supplierPartNum\"\n";
/* If this is an "other" supplier name, then store supplier name for next time. */
if ($supplierName == "Supplier_Name_Other") {
$supplierNameOther = $compProps["userParms"][$key];
} else {
/* Retrieve previously stored other supplier name, if needed. */
if ($supplierName == "Other") {
$supplierName = $supplierNameOther;
}
/* Make sure we have a non-null supplier part number. */
if ($supplierPartNum != "") {
/* Convert this supplier secret key into a standard "Supplier x" / "Supplier Part Number x" pair. */
$compProps["userParms"]["Supplier {$supplierIndex}"] = $supplierName;
$compProps["userParms"]["Supplier {$supplierIndex} Part Number"] = $supplierPartNum;
/* Increment the index into suppliers. */
$supplierIndex++;
}
}
/* endelse */
}
/* endif */
/* Remove the secret key from user parameters. */
unset($compProps["userParms"][$key]);
}
/* end foreach */
// print_r($compProps["userParms"]);
/* Sanity check. */
if (isset($excelUserParmsByItemHrid[$itemHRID]) || isset($excelSysParmsByItemHrid[$itemHRID])) {
echo "Before dying, excelSysParmsByItemHrid is:\n";
print_r($excelSysParmsByItemHrid);
echo "excelUserParmsByItemHrid is:\n";
print_r($excelUserParmsByItemHrid);
print_r($compProps);
my_die("Error in processing ItemHRID " . $compProps["ItemHRID"] . ". It seems to not be unique in this file!");
}
/* endif */
/* Add this component's info to arrays holding info for all components. */
$excelUserParmsByItemHrid[$itemHRID] = $compProps["userParms"];
$excelSysParmsByItemHrid[$itemHRID] = $compProps["sysParms"];
/** Create "TRT Part Number" user parm. **/
/* Add a hidden user parameter called "TRT Part Number" that will essentially
mirror the ItemHrid. TRT Part Number will be what actually appears on
BOMs, etc. It must be a user parameter because we cannot override
the LibRef (ItemRevHrid) with variant operations. */
/* NOTE: This operation is highly TRT-specific! */
$nextRevId = sprintf($cRevFormatString, $REVISIONID + 1);
// $excelUserParmsByItemHrid[$itemHRID]["TRT Part Number"] = $ItemHRID.$revSep.$nextRevId;
$excelUserParmsByItemHrid[$itemHRID]["TRT Part Number"] = $ItemHRID;
/** Create "TRT Path" user parm. **/
/* Add a hidden user parameter called "TRT Path" that will essentially
mirror the Vault database path to this component. The idea is to include the
information about where in the component tree this component came from within
the component itself. */
/* NOTE: This operation is highly TRT-specific! */
$excelUserParmsByItemHrid[$itemHRID]["TRT Path"] = $compProps["sysParms"][CACF_AlterAltiumSysParmName("COMPONENTPATH")];
/** Create "Value" user parm. **/
/* Add a hidden user parameter called "Value" that will essentially
mirror the component Comment. This may be helpful/necessary down the road
in dealing with varied or overridden component values. */
/* NOTE: This operation is highly TRT-specific! */
$excelUserParmsByItemHrid[$itemHRID]["Value"] = $compProps["sysParms"][CACF_AlterAltiumSysParmName("Comment")];
/* Sort the list of Excel system parms used by this component. */
$rc = ksort($excelSysParmsByItemHrid[$itemHRID]);
if ($rc == FALSE) {
my_die("ksort() failed!");
}
}
/* endif !skipline */
/* Clear ItemHRID */
$ItemHRID = "";
/* Clear the $compProps array, since we're starting a new component. */
$compProps = array();
break;
/* endcase 02 */
}
/* endswitch */
/* Clear flag that tells us to skip a given line. */
$skipLine = false;
}
/* end foreach row */
/* Sort the components in the Excel file by ITEMHRID. */
$rc = ksort($excelSysParmsByItemHrid);
if ($rc == FALSE) {
my_die("ksort() failed!");
}
// echo "excelSysParmsByItemHrid is:\n";
// print_r($excelSysParmsByItemHrid);
// echo "excelUserParmsByItemHrid is:\n";
// print_r($excelUserParmsByItemHrid);
}
