function CACF_CreateChangeLogAuditDataAndWriteToCsv(&$db, $CACFconstants, &$altiumUserNamesByGuid)
{
/* Retrieve necessary global constants. */
$ofs = $CACFconstants["ofs"];
$auditChangeLogFileName = $CACFconstants["auditChangeLogFileName"];
/** Open audit models file for writing. **/
$auditChangeLogFile = my_fopen($auditChangeLogFileName);
/** Output column headers. **/
$line = "";
$line = $line . "GUID" . $ofs . "CREATEDBY" . $ofs . "CREATEDAT" . $ofs . "LASTMODIFIEDBY" . $ofs . "LASTMODIFIEDAT" . $ofs . "LIFECYCLESTATETRANSITION" . $ofs . "LIFECYCLESTATEAFTER" . $ofs . "ITEMREVHRID" . $ofs . "CONTENTTYPEHRID" . $ofs . "COMMENT" . $ofs . "DESCRIPTION";
/* Write line to file. Note: explicitly use DOS (CR/LF) \r\n line endings! */
fputs($auditChangeLogFile, $line . "\r\n");
/** Create array to temporarily hold all lines that we generate, since we need to sort before writing to file. **/
$auditChangeLogLines = array();
/** Execute SQL query and process each row returned by said query. **/
/* Setup query SQL commands. */
/* TODO: Fix this to work with PCB footprints too! */
$queryText = '
SELECT LCSC.GUID, ITEMREV.HRID AS ITEMREVHRID, ITEMREV."COMMENT", ITEMREV.DESCRIPTION, LCSC.CREATEDBYGUID, LCSC.LASTMODIFIEDBYGUID, LCSC.CREATEDAT, LCSC.LASTMODIFIEDAT, LCST.HRID AS LIFECYCLESTATETRANSITION, LCS.HRID AS LIFECYCLESTATEAFTER, ITEMINFO.CONTENTTYPEHRID
FROM ALU_LIFECYCLESTATECHANGE LCSC
LEFT JOIN ALU_ITEMREVISION ITEMREV ON ITEMREV.GUID = LCSC.ITEMREVISIONGUID
LEFT JOIN ALU_LIFECYCLESTATETRANSITION LCST ON LCST.GUID = LCSC.LIFECYCLESTATETRANSITIONGUID
LEFT JOIN ALU_LIFECYCLESTATE LCS ON LCS.GUID = LCSC.LIFECYCLESTATEAFTERGUID
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
ORDER BY CONTENTTYPEHRID DESC, ITEMREV.HRID
;
';
echo date('H:i:s') . " Begin query to generate change log info from Vault database...\n";
/* Execute SQL query. */
$resultHandle = odbc_exec($db, $queryText);
/* Loop over all rows returned by SQL query. */
while (odbc_fetch_row($resultHandle)) {
/* Retrieve specific fields from SQL query result row. */
/* Note: Prefix the revision number with the revision separator char (eg. "-"). */
$GUID = odbc_result($resultHandle, "GUID");
$ITEMREVHRID = odbc_result($resultHandle, "ITEMREVHRID");
$COMMENT = CACF_RemapReservedChars(odbc_result($resultHandle, "COMMENT"));
/* Re-map ',' chars in COMMENT field to '|'! */
$DESCRIPTION = CACF_RemapReservedChars(odbc_result($resultHandle, "DESCRIPTION"));
/* Re-map ',' chars in DESCRIPTION field to '|'! */
$LIFECYCLESTATETRANSITION = odbc_result($resultHandle, "LIFECYCLESTATETRANSITION");
$LIFECYCLESTATEAFTER = odbc_result($resultHandle, "LIFECYCLESTATEAFTER");
$CONTENTTYPEHRID = odbc_result($resultHandle, "CONTENTTYPEHRID");
$CREATEDBYGUID = odbc_result($resultHandle, "CREATEDBYGUID");
$LASTMODIFIEDBYGUID = odbc_result($resultHandle, "LASTMODIFIEDBYGUID");
$CREATEDAT = odbc_result($resultHandle, "CREATEDAT");
$LASTMODIFIEDAT = odbc_result($resultHandle, "LASTMODIFIEDAT");
/* Lookup the usernames of the person to create and last modify this folder. */
$CREATEDBY = CACF_LookupUsername($altiumUserNamesByGuid, $CREATEDBYGUID);
$LASTMODIFIEDBY = CACF_LookupUsername($altiumUserNamesByGuid, $LASTMODIFIEDBYGUID);
/* Output the actual fields retrieved from SQL query. */
$line = "";
$line = $line . "{$GUID}" . $ofs . "{$CREATEDBY}" . $ofs . "{$CREATEDAT}" . $ofs . "{$LASTMODIFIEDBY}" . $ofs . "{$LASTMODIFIEDAT}" . $ofs . "{$LIFECYCLESTATETRANSITION}" . $ofs . "{$LIFECYCLESTATEAFTER}" . $ofs . "{$ITEMREVHRID}" . $ofs . "{$CONTENTTYPEHRID}" . $ofs . "{$COMMENT}" . $ofs . "{$DESCRIPTION}";
/* Write line to file. Note: explicitly use DOS (CR/LF) \r\n line endings! */
fputs($auditChangeLogFile, $line . "\r\n");
/* Store this line in an in-memory array, so we can sort it all just before writing to disk. */
/* Key by a concatenation of the model HRID and the component-where-used HRID. */
// $auditChangeLogLines[$ITEMREVHRID.$COMPHRID] = $line;
}
/* endwhile */
/* Free memory that was holding query results. */
odbc_free_result($resultHandle);
///* Sort all output lines here by concatenation of the model HRID and the component-where-used HRID (key). */
//$rc = ksort($auditChangeLogLines);
//if ($rc == FALSE) my_die("ksort() failed!");
//
///* Loop over all lines in what will be our output file. */
//foreach ($auditChangeLogLines as $key => $line)
//{
// /* Write line to file. Note: explicitly use DOS (CR/LF) \r\n line endings! */
// fputs($auditChangeLogFile, $line . "\r\n");
//
//} /* endforeach */
//
///** Clear array that held all lines prior to writing to file. **/
//$auditChangeLogLines = array();
/** Close the audit output file. **/
fclose($auditChangeLogFile);
echo date('H:i:s') . " Done writing change log file \"{$auditChangeLogFileName}.\"\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);
}
