Version: Windchill 12.1
Use Case: We have a use case where we have custom numbers stored in excel sheet and whenever a number is taken it is marked as used by engineer,engineer creates WTpart using that part(those numbers are standard to business,the sequence cannot be changed).and picking of this numbers is not in sequence,user takes whatever number they want from list.Because of this auto numbering became challenge
Description:
To address above issue, I am planning to load all the available numbers in to a table in database and create a data utility to compare numbers between WTPARTMASTER and MasterNumbers Table(Created by me) and create a JSP page with Find available numbers button that should run the data utility to show the available numbers.
1.
Database Query
1.
Database Query
Create a query to find the free numbers by comparing the
MASTERNUMBERS
table with the
WTPARTMASTER
table.
SELECT
mn.number
FROM
MASTERNUMBERS mn
LEFT
JOIN
WTPARTMASTER wp
ON
mn.number
=
wp.NUMBER
WHERE
wp.NUMBER
IS
NULL
;
2.Create a Custom Data Utility
import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.PreparedStatement;
import
java.sql.ResultSet;
import
java.util.ArrayList;
import
java.util.List;
public
class
FreeNumbersDataUtility
extends
DefaultDataUtility
{
@Override
public
Object
getDataValue
(String component_id, Object datum, ModelContext mc)
throws
WTException {
// Implement logic to fetch free numbers from the database
List<String> freeNumbers = getFreeNumbersFromDatabase();
return
freeNumbers; }
private
List<String>
getFreeNumbersFromDatabase
()
throws
WTException { List<String> freeNumbers =
new
ArrayList
<>();
Connection
connection
=
null
;
PreparedStatement
preparedStatement
=
null
;
ResultSet
resultSet
=
null
;
try
{
// Establish the database connection
connection = DriverManager.getConnection(
"jdbc:oracle:thin:@your_database_url"
,
"username"
,
"password"
);
// Prepare the SQL query
String
sql
=
"SELECT mn.number FROM MASTERNUMBERS mn LEFT JOIN WTPARTMASTER wp ON mn.number = wp.NUMBER WHERE wp.NUMBER IS NULL"
; preparedStatement = connection.prepareStatement(sql);
// Execute the query
resultSet = preparedStatement.executeQuery();
// Process the result set
while
(resultSet.next()) { freeNumbers.add(resultSet.getString(
"number"
)); } }
catch
(Exception e) {
throw
new
WTException
(e); }
finally
{
// Close the resources
try
{
if
(resultSet !=
null
) resultSet.close();
if
(preparedStatement !=
null
) preparedStatement.close();
if
(connection !=
null
) connection.close(); }
catch
(Exception e) {
// Handle exceptions during resource cleanup
} }
return
freeNumbers; }}
3.Register the Data Utility in service Properties
4.Create Custom JSP Page
Create a Custom JSP Page
Develop a JSP page to display the free numbers.
<%@ page language=
"java"
contentType=
"text/html; charset=UTF-8"
pageEncoding=
"UTF-8"
%><%@ taglib uri=
"http://www.ptc.com/taglibs/wt"
prefix=
"wt"
%><html><head> <title>Free Numbers</title></head><body> <h2>Available Numbers</h2> <ul> <wt:forEach
var
=
"number"
items=
"${freeNumbers}"
> <li>${number}</li> </wt:forEach> </ul></body></html>
I am new to customization,just want to see if this approach works,if we can any other ways to achieve this.
Thank you
