Query Raw Data from Reedholm.jsl

//!

Names Default To Here(1);

// Define the Reedholm database connection string
conn = "DSN=Reedholm_x64;UID=owt_read;PWD=owtread;APP=JMP;WSID=THORIUM;DATABASE=RIWEBSQL";

// Prompt user for Lot IDs
a =	New Window(
		"Enter the Reedholm Lot ID:",
		<< Modal,
		Text Box(
		    "Enter the Reedholm Lot ID:
		    ",
		    Justify Text ("center"),
		    Set Width(250)
		),
		Text Box(
			"(Separate multiple Lot IDs with a comma ',')
			",
			Justify Text ("center"),
		    Set Width(250)
		),
		editBox = Text Edit Box(),
		editBox << Set Width(250),
		Button Box( "OK" ),
		Button Box( "Cancel" )
);
// Stop the script if the user cancels
If( a["Button"] == -1,
	Stop();
);

// Get the contents of the Number Edit Box
lot_id = editBox << get text;
Show(lot_id);

// Convert the string to a list
lot_ids = Words(lot_id, ",");
Show(lot_ids);

///////////////////////////////////////////////////////////////
// Loop over each lot, executing the DB action.
///////////////////////////////////////////////////////////////
length = N Items(lot_ids);
Show(length);
For(i = 1, i <= length, i++,
    // Some debugging info.
	Write("\!N", "lot_ids[", i, "]: ", lot_ids[i]);

	// Create the SQL string that we're going to execute
	sqlString2 = Concat Items(
		{"EXECUTE dbo.[_TPH_ExportLotData]
		@Lot_ID = ", Char(lot_ids[i])},
		"");

	Show(sqlString2);
	Write("\!N");

	// Execute the string into a table with the tablename = lot_ids[i]
	if (
		// Case 1: 1st iteration of the loop - open initial data table
		i == 1,
		dt1 = Open Database(conn, sqlString2, Char(lot_ids[i]));
		dt1 << Delete Table Property("Update From DB"),
		
		// Case 2: all subsequent iterations - concatenate
		i > 1,
		dt2 = Open Database(conn, sqlString2, Char(lot_ids[i]));
		dt2 << Delete Table Property("Update From DB");
		dt1 << Concatenate(dt2, Append to first table);
		Close(dt2, NoSave);
	);
);
///////////////////////////////////////////////////////////////
// End Loop
///////////////////////////////////////////////////////////////
Write("\!N");

// Rename the 1st table
dt1 << Set Name(lot_id);

// Take the Die_Name column and parse it for die coords
xCoordCol = New Column("X Coord (Column)");
xCoordCol << Set Formula(
	Num(Substr(:Die_Name, 2, Munger(:Die_Name, 0, "y") - 2))
);
xCoordCol << EvalFormula;

yCoordCol = New Column("Y Coord (Row)");
yCoordCol << Set Formula(
	Num(Substr(:Die_Name, Munger(:Die_Name, 0, "y") + 1))
);
yCoordCol << EvalFormula;

// Figure out what mask we're using and then set the equation for Radius.
// No need to create new columns - we should only have 1 mask per lot.
// Take the 7th through 11th characters of the Device_Name 
// (D.SIL607G13TPJ.Product.Ron) to get the mask.
mask = Substr( Column( "Device_Name" )[1], 7, 5);
Show(mask);

// Lookup the Die Size (X, Y). Assume 07G11 if not found.
dieSize = Match(
	mask,
	"MDH27", {4.34, 6.44},
	"07G11", {2.43, 3.3},
	"07G13", {4.37, 6.47},
	"07G14", {2.33, 2.07},
	"07G16", {5.02, 8.49},
	"07G17", {2.9, 3.3},
	"ECM01", {12.0, 11.0},
	{2.43, 3.3}
);

// Look up the CenterXY coord. Assume 07G11 if not found.
centerXY = Match(
	mask,
	"MDH27", {18.5, 12.5},
	"07G11", {29, 21},
	"07G13", {16.5, 9.5},
	"07G14", {30.5, 33.5},
	"07G16", {14.3386, 8.5589},
	"07G17", {24.431, 20.894},
	"ECM01", {6, 6.5},
	{2.43, 3.3}
);

// Add a Radius (mm) column.
radiusCol = New Column("Radius (mm)");
radiusCol << Set Formula(
	Root(
		(dieSize[1] * (:Name("X Coord (Column)") - centerXY[1])) ^ 2
		+ 
		(dieSize[2] * (:Name("Y Coord (Row)") - centerXY[2])) ^ 2,
		Empty()
	)
);
radiusCol << EvalFormula;

// Add a Radius^2 column.
radius2Col = New Column("Radius^2");
radius2Col << Set Formula(
	:Name( "Radius (mm)" )^2 
);
radius2Col << EvalFormula;

// Add a gate length Column.
gateLen = Match(
	mask,
	"MDH27", 300,
	"07G11", 120,
	"07G13", 324.3,
	"07G14", 65,
	"07G16", 510,
	"07G17", 161.7,
	"ECM01", 1,
	120
);

gateLenCol = New Column("GateLen");
gateLenCol << Set Each Value(gateLen);
gateLenCol << EvalFormula;

// Add a Rdson Column.
rdsonCol = New Column("Rdson");
rdsonCol << Set Formula(
	:Result * :gateLen
);
rdsonCol << EvalFormula;

/////////////////////////////////////////////////////////////////////////////
// Get the Growth IDs (assume things are in WIPTrac)
/////////////////////////////////////////////////////////////////////////////

// First, get a unique list of substrate IDs
unique_ids = Associative Array(dt1:Wafer_Name);
unique_ids = unique_ids << Get Keys;

// Convert the unique ids to a string for SQL
unique_ids_string = Concat Items(unique_ids, "','");
unique_ids_string = Concat("('", unique_ids_string, "')");
Show(unique_ids_string);

// Define the WIPTrac database connection string
conn = "DSN=PostgreSQL30_x64;
DATABASE=wiptrac;
SERVER=192.168.11.30;
PORT=5432;
UID=wiptracreader;
PWD=Trans4m%21;
CA=d;
A6=;
A7=100;
A8=4096;
B0=255;
B1=8190;
BI=0;
C2=dd_;
CX=1c502bb;
A1=7.4";

// Create the SQL string to execute
sqlString2 = Concat Items(
	{"SELECT
		a.serial_id,
		a.serial_num,
		a.vendorserial_num
	FROM public.serial AS a
	WHERE a.serial_num IN ",
	unique_ids_string},
	""
	);
Show(sqlString2);

// Query the database
dt2 = Open Database(conn, sqlString2, "Wafer IDs");

// Update the 1st table with the growht ID from the query
dt1 << Update(
	With( dt2 ),
	Match Columns( :Wafer_Name = :serial_num ),
	Add Columns from Update table( :vendorserial_num )
);

// Rename a column to make things easier.
dt1:vendorserial_num << Set Name("GrowthID");

// Close the extra table
Close(dt2, NoSave);


/////////////////////////////////////////////////////////////////////////////
// Split the Data - Generally easier to work with.
/////////////////////////////////////////////////////////////////////////////

// First, create a new column so that the test order is kept
testNameCol = dt1 << New Column("TestName");
testNameCol << Set Formula(
	Char(:TestOrder) || "~" || :Test_Name
);
testNameCol << EvalFormula;

// Now Split the data
dt2 = dt1 << Split(
	Split By( :TestName ),
	Split( :Result ),
	Output Table( lot_id || " Split" ),
	Remaining Columns(
		Drop(
			:Test_Name,
			:Test_Version,
			:Test_ID,
			:TestOrder,
			:DataPointID,
			:ResultStatus,
			:FailCode,
			:SysState
		)
	)
);