// search for imposition method (for T230 RBA Branch)
internal static string impoMethod(string machine, string itemCode)
{
string sql = "SELECT imposition.method "
+ "FROM imposition "
+ "RIGHT JOIN process "
+ "ON CAST(COALESCE(imposition.process,'0') AS INTEGER ) = process.id "
+ "RIGHT JOIN productgroup "
+ "ON imposition.id = productgroup.impid "
+ "RIGHT JOIN productgroupmembers "
+ "ON productgroup.id = productgroupmembers.groupid "
+ "WHERE process.process = :machine "
+ "AND productgroupmembers.stockcode = :item_code; ";
string[] parameterNames = { "machine", "item_code" };
string[] parameterVals = { machine, itemCode };
string scalarRequestString = AWFPostgresDataLayer.SelectScalar(sql, parameterNames, parameterVals);
if (!string.IsNullOrEmpty(scalarRequestString))
{
return(scalarRequestString);
}
else
{
throw new FormatException("Can't find Imposition stepping for: " + itemCode);
}
}
// update aaudit
internal static int updateAaudit(AWF myAWF, string actionString, string eventId, int itemSequence, RBA_Variables variables)
{
asset myAsset = getAsset(myAWF, itemSequence, variables);
switch (getProductType(myAWF, itemSequence, variables))
{
case productType.Static:
AWF_DB_Log.insertAauditRecordInDb(myAsset.Id, eventId, string.Format("{0}: {1} | Job Number: {2}", actionString, myAWF.Products.Item[itemSequence].ItemCode, myAWF.JobNumber.ToString()));
break;
case productType.Variable:
AWF_DB_Log.insertAauditRecordInDb(myAsset.Id, eventId, string.Format("{0}: {1}", actionString, myAsset.Name));
break;
}
string sql = "UPDATE asset "
+ "SET tlc=(SELECT DISTINCT tc "
+ "FROM aaudit WHERE id = :asset_id "
+ "AND seq = (SELECT DISTINCT MAX(seq) "
+ "FROM aaudit WHERE id = :asset_id ) LIMIT 1) "
+ (actionString.Contains("DESTROY/CANCEL") ? ", status = 3" : String.Empty)
+ "WHERE id = :asset_id; ";
string[] parameterNames = { "asset_id" };
string[] parameterVals = { myAsset.Id };
return(AWFPostgresDataLayer.ExecuteNonQuery(sql, parameterNames, parameterVals));
}
internal static itemSize getSize(string stockCode)
{
string sql = @"SELECT imposition.sizex As ""SizeX"", imposition.sizey As ""SizeY"" FROM imposition " +
"LEFT JOIN productgroup " +
"ON imposition.id = productgroup.impid " +
"LEFT JOIN productgroupmembers " +
"ON productgroup.id = productgroupmembers.groupid " +
"WHERE stockcode = :item_code " +
"LIMIT 1; ";
string[] parameterNames = { "item_code" };
string[] parameterVals = { stockCode };
DataTable rtTable;
try
{
rtTable = AWFPostgresDataLayer.GetDataTable(sql, parameterNames, parameterVals);
}
catch
{
throw;
}
if (rtTable.Rows.Count == 1)
{
return(new itemSize {
width = new Size(Convert.ToInt32((rtTable.Rows[0]["SizeX"]))), height = new Size(Convert.ToInt32((rtTable.Rows[0]["SizeY"])))
});
}
else
{
throw new Exception(String.Format("No Sizes found for the item: {0}", stockCode));
}
}
// update Asset
internal static int updateApprovedAsset(String itemCode)
{
string sql = "SELECT update_asset( :item_code )";
string[] parameterNames = { "item_code" };
string[] parameterVals = { itemCode };
return(AWFPostgresDataLayer.updateTransaction(sql, parameterNames, parameterVals));
}
static internal int insertAauditRecordInDb(string assetId, string eventId, string updateInfo)
{
string sql = "INSERT INTO aaudit "
+ "VALUES (:asset_id, coalesce((select max(seq)+1 "
+ "FROM aaudit where id = :asset_id),1), :event_id, :update_info); ";
string[] parameterNames = { "asset_id", "event_id", ":update_info" };
string[] parameterVals = { assetId, eventId, updateInfo };
return(AWFPostgresDataLayer.updateTransaction(sql, parameterNames, parameterVals));
}
// Asset Exists boolean method
static internal bool assetExists(string itemCode)
{
string sql = "SELECT id FROM asset where name= :stock_code";
string[] parameterNames = new string[] { "stock_code" };
string [] parameterVals = new string[] { itemCode };
string scalarRequestString = AWFPostgresDataLayer.SelectScalar(sql, parameterNames, parameterVals);
return(!string.IsNullOrEmpty(scalarRequestString));
}
private static productType productTypeStaticSearch(string sql, string[] parameterNames, AWF myAWF, int itemSequence)
{
string[] parameterVals = { myAWF.Products.Item[itemSequence].ItemCode };
string scalarRequestString = AWFPostgresDataLayer.SelectScalar(sql, parameterNames, parameterVals);
if (!string.IsNullOrEmpty(scalarRequestString))
{
return((productType)(Convert.ToInt32(scalarRequestString)));
}
else
{
throw new FormatException(string.Format("job/pd#: {0}, item: {1}, returns no 'type' value from DB", myAWF.JobNumber.ToString(), myAWF.Products.Item[itemSequence].ItemCode));
}
}
//check if static item is active, i.e. exists or not disabled
private static bool isStaticActive(string sql, string[] parameterNames, AWF myAWF, int itemSequence)
{
string[] parameterVals = { myAWF.Products.Item[itemSequence].ItemCode };
string scalarRequestString = AWFPostgresDataLayer.SelectScalar(sql, parameterNames, parameterVals);
if (!string.IsNullOrEmpty(scalarRequestString))
{
return(0 == Convert.ToInt32(scalarRequestString));
}
else
{
throw new FormatException(string.Format("job/pd#: {0}, item: {1}, returns no 'status' value from prinlut 'asset' table", myAWF.JobNumber.ToString(), myAWF.Products.Item[itemSequence].ItemCode));
}
}
// Create new Asset, return asset id
static internal string createNewAsset(string itemName, string prismCust, string itemType)
{
string sql = "INSERT INTO asset(name, type, status, tlc, custid ) "
+ "VALUES ( :item_code , :item_type, 0, current_timestamp, "
+ "(SELECT id FROM customer WHERE rparent = :prism_customer)); "
+ "SELECT currval('aid'::regclass) as asset_id;";
string[] parameterNames = new string[] { "item_code", "item_type", "prism_customer" };
string[] parameterVals = new string[] { itemName, itemType, prismCust };
string assetId = AWFPostgresDataLayer.insertSelectTransaction(sql, parameterNames, parameterVals);
string eventID = "1"; // create event
insertAauditRecordInDb(assetId, eventID, itemName + ": CREATE");
return(assetId);
}
// updateItemRecordInDb for variable item only!!! AWFAWFBD static class contains combined method for static and variable
static internal int updateItemRecordInDb(string assetId, string updateInfo, string eventId)
{
string sql = "UPDATE asset "
+ "SET tlc=(SELECT DISTINCT tc "
+ "FROM aaudit WHERE id = :asset_id "
+ "AND seq = (SELECT DISTINCT MAX(seq) "
+ "FROM aaudit WHERE id = :asset_id ) LIMIT 1) "
+ (updateInfo.Contains("DESTROY/CANCEL") ? ", status = 3" : String.Empty)
+ "WHERE id = :asset_id; ";
string[] parameterNames = { "asset_id" };
string[] parameterVals = { assetId };
insertAauditRecordInDb(assetId, eventId, updateInfo);
return(AWFPostgresDataLayer.ExecuteNonQuery(sql, parameterNames, parameterVals));
}
// product type
internal static productType getProductType(AWF myAWF, int itemSequence, RBA_Variables variables)
{
string sql = "SELECT type " +
"FROM asset " +
"WHERE name = :asset_name; ";
string[] parameterNames = { "asset_name" };
string[] parameterVals = { string.Format("{0}{1} {2} {3}", variables.VASSET_PREFIX,
myAWF.Products.Item[itemSequence].SourcePDNumber,
myAWF.Products.Item[itemSequence].SourcePDLine,
myAWF.Products.Item[itemSequence].ItemCode) };
string scalarRequestString = AWFPostgresDataLayer.SelectScalar(sql, parameterNames, parameterVals);
return(String.IsNullOrEmpty(scalarRequestString) ? productTypeStaticSearch(sql, parameterNames, myAWF, itemSequence)
: (productType)(Convert.ToInt32(scalarRequestString)));
}
private static asset getAsset(AWF myAWF, int itemSequence, RBA_Variables variables)
{
string sql = string.Empty;
string itemCode = string.Format("%{0}%", myAWF.Products.Item[itemSequence].ItemCode);
string PDNumber = string.Format("{0}%", variables.VASSET_PREFIX + myAWF.Products.Item[itemSequence].SourcePDNumber);
string[] parameterNames = new string[1];
string[] parameterVals = new string[1];
switch (getProductType(myAWF, itemSequence, variables))
{
case productType.Static:
sql = "SELECT id, name "
+ "FROM asset "
+ "WHERE name LIKE :stock_code "
+ "LIMIT 1";
parameterNames = new string[] { "stock_code" };
parameterVals = new string[] { itemCode };
break;
case productType.Variable:
sql = "SELECT id, name "
+ "FROM asset "
+ "WHERE name LIKE :printdirect_number "
+ "AND name LIKE :stock_code "
+ "LIMIT 1";
parameterNames = new string[] { "printdirect_number", "stock_code" };
parameterVals = new string[] { PDNumber, itemCode };
break;
}
DataTable rtTable = AWFPostgresDataLayer.GetDataTable(sql, parameterNames, parameterVals);
if (rtTable.Rows.Count == 1)
{
return(new asset {
Id = Convert.ToString(rtTable.Rows[0]["id"]), Name = Convert.ToString(rtTable.Rows[0]["name"])
});
}
else
{
throw new Exception("Asset not found in Asset Table");
}
}
// Path to job in Workshop
internal static string newJobPath(string prismCustomer)
{
string sql = "SELECT prin_path "
+ "FROM customer "
+ "WHERE rparent = :prism_customer; ";
string[] parameterNames = { "prism_customer" };
string[] parameterVals = { prismCustomer };
string scalarRequestString = AWFPostgresDataLayer.SelectScalar(sql, parameterNames, parameterVals);
if (!string.IsNullOrEmpty(scalarRequestString))
{
return(scalarRequestString);
}
else
{
throw new Exception("Customer: " + prismCustomer + " returns no 'prin_path' value from DB");
}
}
internal static string processType(string machine)
{
string sql = "SELECT type "
+ "FROM process "
+ "WHERE process = :machine";
string[] parameterNames = { "machine" };
string[] parameterVals = { machine };
string scalarRequestString = AWFPostgresDataLayer.SelectScalar(sql, parameterNames, parameterVals);
if (!string.IsNullOrEmpty(scalarRequestString))
{
return(scalarRequestString);
}
else
{
throw new Exception("Can't find process type for: " + machine);
}
}
