ITrack/Appraisal Extension

From ISoft Wiki
Jump to: navigation, search

The Appraisal Extension is used to allow our products to give per-item appraisals by evaluating the item's performance to Berryhill Auctioneers' previous auctions.

This page doesn't help you set up appraisals for a customer

Before you dig too deeply into this page, this page lists technical specifications of the Appraisal Extension. If you are ISoft Tech Support looking to turn on (or fix) Appraisals for a customer, try these articles instead:

General

In general, the Appraisal Extension requires the following:

  • ITrack. It can be Pro, AX, Enterprise, or anything that uses ITrack.exe
    • OR any specially-build applications designed by ISoft.
  • The AppraisalExtension.dll file.
  • In Pro, the user must have htmode set to true in the general section of their host.ini for the Appraise... button to appear
  • Connection configuration data. This is usually located in the host.ini file provided with ITrack.
  • Company permission. This is controlled by ISoft, and those interested in being added should contact our Sales department.
  • The company also needs to have its product code set in the the database.

All of these can be arranged by contacting ISoft at 1-800-929-1829.

Usage

Once the Appraisal Extension is installed and configured, most appraisals will be done with a list of parts (or, less commonly, a single part) and a button press. ITrack users should look to their Search screens. When the button is pressed, the screen will look up the appraisal data, which may take a few moments, and then report it on your screen.

Methodology

Appraisals are done using Berryhill Auctioneers' auction results. These allow us to get and keep current frames of reference for car and truck parts. ISoft keeps a special database that is maintained by function calls and software interaction so that administrating this data is mostly an automatic process.

Recently, an appraisal method that uses HeavyTruckParts as its data source has been created. It uses all available items listed on heavytruckparts.net, and also includes the number of relevant searches done in the last year to give an idea of how popular the item is.

A great effort is made to provide backwards compatibility in allowing users to define their own usemanmod field settings.

Making Appraisal Data

Whenever a Berryhill Auction is closed, the results from the auction are incorporated into a cache using the database function `f_generate_full_cache`. This is a safe function call, and if no work needs to be done, it won't do anything. Doing this will not interrupt appraisals in progress, but it can end up having different results sent back. We usually update the cache at night for this reason.

Not all origins are generated by `f_generate_full_cache`. Some origins, such as ORIGIN_CUSTOM and ORIGIN_HTP_LIVE are on their own schedules (custom is generated when it gets called, and htp_live is generated on a weekly basis).

Using Appraisal Data

In order to appraise an item or set of items, the following function calls should be used. Order is important!

  1. Grab a session ID using `f_open_session`.
  2. Insert items into toappraise using your sessionid, a unique key for the inventoryid, and fill out as much of the rest as you can, placing NULL values where you don't have any info.
  3. Appraise your inserted data using `p_appraise_session` with your sessionid and origin code.
  4. Read back your appraised values from toappraise.value.
  5. From here you can add or remove items and appraise again using any origin as many times as you like.
  6. Close your session using `f_close_session` with your sessionid.

Don't worry about cleaning your results, as closing your session does it automatically. If you accidentally disconnect or lose your sessionid without cleaning up, don't fret! Rows are cleaned up on a timed basis, as well.

In calling code, no exceptions should ever make it out of the Appraisal extension. There is logging functionality in Get/SetLastError(), as well as TriggerFailure(). Automatic failure display in general can be toggled on and off using Get/SetSuppressErrors(), and connection has had special boolean inputs to its related functions (mostly Connect()) to provide the same functionality in a limited setting.

Manually Retrieving Appraisals

Example queries for manual appraisal: SET @strProductCode = <their_product_code>; SET @strOriginCode = <desired_origin>; # good examples: ORIGIN_HTP_LIVE, ORIGIN_ALL_AUCTIONS, ORIGIN_LAST_YEAR

SELECT f_open_session(@strProductCode) INTO @strSession;

INSERT INTO `toappraise` (`company`, `session`, `timestamp`, `inventoryid`, `inventorytypeid`, `category`, `make`, `model`, `pmanufacturer`, `pmodel`, `year`) VALUES (@strProductCode, @strSession, NOW(), <inventoryid>, <inventorytypeid>, <category>, <make>, <model>, <manufacturer>, <partmodel>, <year>);

CALL `p_appraise_session`(@strSession, @strOriginCode);

SELECT `toappraise`.`appraisalid`, `toappraise`.`inventoryid`, `toappraise`.`itemsaffected`, `toappraise`.`value`, `toappraise`.`min`, `toappraise`.`max`, `toappraise`.`relevance`, `toappraise`.`deviance`, `metadata`.`metadataid` FROM `toappraise` LEFT JOIN `metadata` USING (`appraisalid`) WHERE `session` = @strSession GROUP BY `inventoryid`;

SELECT f_close_session(@strSession);

Code

You can use the Appraisal Extension interface, or make database calls directly.

Appraisal Extension - External Calls

The easiest way to get appraisals is to use the following sequence of remote calls:

  1. Check to see that the appraisal extension is valid by calling something like
    • ITAppraisal::GetInstance()->IsEnabled()
  2. Create an APPRAISALMESSAGE object and set APPRAISALMESSAGE.itData.itOrigin to an appropriate origin. (Use APPRAISALMESSAGESINGLEITEM if you only have a single item)
  3. Optional - Ask the user for the appraisal type
    1. Set APPRAISALMESSAGE.itData.nAuctionID to the current auction you're appraising at (leave it alone if you don't know what this should be)
    2. Call the following to run the dialog:
      • CallExternalFunction(<master window handle>, "Appraisal", "AppraisalOriginDialog", <extension name>, &APPRAISALMESSAGE.itData, NULL)
    3. The user will use the dialog that pops up, and control of the program will be returned.
  4. Create an ITAppraisalItem object.
  5. For each item in the array, set the following values in your ITAppraisalItem (skip if you don't have them)
    • Unique Identifier (integer) (partnum / inventoryid is a good choice)
    • Inventory Type ID (integer)
    • Category (string)
    • Make (string)
    • Model (string)
    • Part Manufacturer (string)
    • Part Model (string)
    • Year (int)
  6. Add that item to the appraisal array by calling the following:
    • APPRAISALMESSAGE.mapItems[itItem.GetID()] = ITAppraisalItem
  7. When you've added all the items to appraise, call:
    • Multiple items: CallExternalFunction(<master window handle>, "Appraisal", "AppraiseItems", <extension name>, &APPRAISALMESSAGE, NULL);
    • Single item: CallExternalFunction(<master window handle>, "Appraisal", "AppraiseItem", <extension name>, &APPRAISALMESSAGESINGLEITEM , NULL);
  8. When control is returned, you can retrieve your appraisal values by calling:
    • APPRAISALMESSAGE.mapItems[<unique id>].GetAppraisedValue()
    • OR MapFind(itMessage.mapItems, <unique id>, itItem); itItem.GetAppraisedValue();

Appraisal Extension - Direct Object Calls

In some cases, you'll want to appraise the same data set more than twice, and in this case you can avoid the setup overhead of the objects by calling the appraisal object directly:

  1. Check to see that the appraisal extension is valid by calling something like
    • ITAppraisal::GetInstance()->IsEnabled()
  2. Get an instance of the appraisal object:
    • ITAppraisal *pitAppraisal = ITAppraisal::GetInstance(pwndMaster);
  3. Create an empty CString for your session ID
  4. Create an APPRAISALDATA object and set APPRAISALMESSAGE.itData.itOrigin to an appropriate origin
    • If it's your first call, you *must* call ITAppraisalExtension::Initialize(m_pwndMaster) OR set itData.pMasterWnd to Master's window handle
  5. Optional - Ask the user for the appraisal type
    1. Call pitAppraisal->DoAppraisalOriginDialog(APPRAISALMESSAGE.itData);
    2. The user will use the dialog that pops up, and control of the program will be returned
  6. Create an ITAppraisalItem object
  7. For each item in the array, set the following values in your ITAppraisalItem (skip if you don't have them)
    • Unique Identifier (integer) (partnum / inventoryid is a good choice)
    • Inventory Type ID (integer)
    • Category (string)
    • Make (string)
    • Model (string)
    • Part Manufacturer (string)
    • Part Model (string)
    • Year (int)
  8. Add that item to the appraisal array by calling the following:
    • APPRAISALMESSAGE.mapItems[itItem.GetID()] = ITAppraisalItem
  9. When you've added all the items to appraise, call:
    1. pitAppraisal->OpenSession(sessionID) to get your session id
    2. pitAppraisal->AddItemsToSession(sessionID, APPRAISALMESSAGE.mapItems) to add your items
    3. pitAppraisal->AppraiseSession(sessionID, APPRAISALMESSAGE.itData)
    4. pitAppraisal->GetAppraisedValues(sessionID, APPRAISALMESSAGE.mapItems) to fill out your values
    5. If you want to appraise different items, call pitAppraisal->CleanSession(sessionID) to clean out the old session items
    6. If you wish, alter APPRAISALMESSAGE.itData and go back to step 3 to do a different appraisal
    7. pitAppraisal->CloseSession(sessionID) to clean up your appraisal session
  10. Let your APPRAISALMESSAGE object go out of scope or deallocate it
  11. When you are done using the module, call `delete pitAppraisal` to avoid memory leaks. This is per-extension because of the way stack space works

Caveats

You can use the APPRAISALMESSAGE multiple times, and when you're done with it, just let it go out of scope or deallocate it. You never need to worry about cleaning up the ITAppraisal object either.

Don't mess around with the memory location of pitAppraisal. It's a singleton, and anything you do to it will be reflected any place its code is called.

ITAppraisal is *not* thread-safe at the moment.

Database

The following are nifty rules for altering the way that appraisals are done, or need to be done in order for things to work.

Permissions

The following permissions must be granted to the 'appraisals' user in order for it to function properly:

  • `appraisals`
    • Reads from:
      • cache
      • metadata
      • origin
      • toappraise
      • v_auctions
    • Insert, Update, Delete from:
      • toappraise
    • Execute:
      • f_clean_old_entries
      • f_close_session
      • f_get_auction_list
      • f_log_event
      • f_open_session
      • p_appraise_session
      • p_build_appraisal
      • p_build_custom
      • p_generate_full_cache
  • `customerdb` OR `customer_stub`
    • CALL p_company_enable_appraisal(<companyID>, ('Full','HTP','Auction','None') to turn a company on or off

Adding an Origin

Origins have become much more dynamic than their original enumerated values.

All Origins need you to do the following:

  • Add an entry into `origin`
    • The name is what users see
    • The code is what gets referenced in ITrack and database function code, and does not change after creation
    • The function is the suffix of the function that builds the appraisal data from scratch
  • Create a new procedure `p_build_<function>`, where function is the value in origin.function. This procedure will enter in data to the appraisal table, and must match the inputs (origin INT, nAuctionList TEXT)

If your Origin has a way to cache results (such as a static auction list or special number):

  • Add an entry to `f_get_auction_list` that returns the cache check value for your Origin (this can be a list of auctions, or a date, or hash, or whatever)

If your Origin uses a list of auctions:

  • A call to `p_build_appraisal_base` will do all the appraisal data generation for you, so just put that in `p_build_<function>`

If your Origin has a cache check value AND you want the cache to be generated automatically:

  • Add a call to `p_build_appraisal_real` in `p_generate_full_cache` for your Origin.

Changing Field Priority

The fields that have data extracted from them are based on a priority system and appraisal methods. Fields are still a pretty hard-coded thing (which will change if we have to add more than one in the future), but if you change their priorities, you can change their importance in generating the cache and selecting their values for appraising.

Priorities

In the `sourcefield` table, change the `priority` field. It's an abstract scale that simply uses addition, and is currently based on 0-10, where 0 means it's not important at all and 10 means it's all but required. To find an appraisal method's importance, sum together the priorities of its source fields.

Sources

You can find the database creation script for Appraisals in trunk/General/Appraisal_Extension/Database/Appraisals.sql. It must be placed into a server that has databases for itrackax and itrackproht (which may be mirrored from elsewhere, but they must be directly accessible for Appraisal to work).

After you run Appraisals.sql, you must run Update.sql to bring it in line with the current updates.