Design Requirements for digital bidding - Gallery-Momiji/momiji-web GitHub Wiki
Table of Contents
Introduction/Overview (INTRO)
- This is for a silent bidding system using tablets. browser based for portability
- All pieces for auction are listed in sql table
merchandise
- All pieces have a unique auction number (or Piece ID): letters AN, followed by 3 digits (
ArtistId
), followed by a dash, followed by three other digits (MerchId
). E.g. AN001-023. - All pieces have a minimum bid (
MerchMinBid
) - All bidders are listed in sql table
bidders
- All bidders have a unique number (
bidderno
) - When a certain number of bids are hit (
AuctionCutoff
fromoptions
table), the piece is marked for inclusion in the live auction on Sunday - Making a bid requires a piece number, a bidder number, a bidder name (used for redundancy/manual verification checks), and a bid in whole dollars
General information about SQL database tables can be found here: https://github.com/Gallery-Momiji/momiji-tables/blob/master/tables.md
UX Requirements (UXREQ)
bidding/index.php (UXREQ#1)
Optional parameters: error
- If parameter
error=1
is provided, show INFO#1 - List all items available for bidding using ouput of GENQY#1
- If
AuctionEnd
= 1, show INFO#8 - Entries will be listed as "ANXXX-YYY - TITLE", where XXX is a 3 digit
ArtistID
(zero padded), YYY is a 3 digitMerchID
(zero padded), and TITLE isMerchTitle
- Provide filter/search for faster selection
- For each item, show a select button to confirm selection and redirect to
item.php?artistid=AID&merchid=MID
, whereAID
is theArtistID
andMID
is theMerchID
from the user selection.
bidding/item.php (UXREQ#2)
Required parameters: artistid
, merchid
Optional parameters: success
, error
- If the required parameters are missing, redirect to
index.php?error=1
- Show the piece auction number (ANXXX-YYY, see INTRO),
MerchTitle
andMerchMedium
using output of GENQY#2 - Show list of bids using the ing output of GENQY#6
- If GENQY#2 fails, redirect to
index.php?error=1
- If parameter
error=1
is provided, show INFO#2 - If parameter
error=2
is provided, show INFO#3 - If parameter
error=3
is provided, show INFO#4 - If parameter
error=4
is provided, show INFO#9 - If parameter
success=1
is provided, show INFO#5 - Show input fields for submitting, including name, bidder number, bid amount (default bid amount is current bid + 1, or
MerchMinBid
if there are no bids) - Add back button to redirect to
index.php
- Add submit button to redirect to
submit.php?artistid=AID&merchid=MID
, whereAID
isartistid
parameter andMID
is themerchid
parameter, values of the input fields should also be passed over to submit.php, see BidderReg implement for implementation details. - Submit button has the following requirements:
- All input must be populated prior to submission
- Bid amount must be a whole positive non-zero number, i.e. regex:
[1-9][0-9]*
- Bid amount must be larger than last bid
- If
MerchSold
= 1, hid all bidding input and the submit button and show INFO#6 - If the bid count is equal or greater than
AuctionCutoff
, hide all bidding input and the submit button and show INFO#7 - If
AuctionEnd
= 1, hide all bidding input and the submit button and show INFO#8 - If multiple scenarios occur, all applicable informational messages will be displayed at the same time
bidding/submit.php (UXREQ#3)
Required parameters: artistid
, merchid
Input values: bidddernumber
, biddername
, bidvalue
- If
artistid
,merchid
parameters are missing, redirect toindex.php?error=1
- If
bidddernumber
,biddername
,bidvalue
parameters are missing, redirect toitem.php?error=1&artistid=AID&merchid=MID
whereAID
isartistid
parameter andMID
is themerchid
parameter - Don't accept negative or invalid bids: if
bidvalue
is not a positive integer, redirect toitem.php?error=1&artistid=AID&merchid=MID
whereAID
isartistid
parameter andMID
is themerchid
parameter - Make sure bidder number is valid and is assigned: if GENQY#4 fails or does not return any values, redirect to
item.php?error=2&artistid=AID&merchid=MID
whereAID
isartistid
parameter andMID
is themerchid
parameter - Check if bid is valid: if GENQY#3 fails or does not return any values, redirect to
item.php?error=1&artistid=AID&merchid=MID
whereAID
isartistid
parameter andMID
is themerchid
parameter - Make sure piece is not sold/auction is over: if
AuctionEnd
= 1 ORMerchSold
= 1 from GENQY#3, redirect toitem.php?error=1&artistid=AID&merchid=MID
whereAID
isartistid
parameter andMID
is themerchid
parameter - Check if they were quickly outbid: if
currentbid
from GENQY#3 output is more than or equal tobidvalue
, redirect toitem.php?error=3&artistid=AID&merchid=MID
whereAID
isartistid
parameter andMID
is themerchid
parameter - Check if going to live auction: if
bidcount
from GENQY#3 output is more than or equal toAuctionCutoff
from GENQY#3 output, redirect toitem.php?error=1&artistid=AID&merchid=MID
whereAID
isartistid
parameter andMID
is themerchid
parameter - Do not allow bidder to bid twice in a row: if
bidderno
from GENQY#7 matchesbidddernumber
, redirect toitem.php?error=4&artistid=AID&merchid=MID
whereAID
isartistid
parameter andMID
is themerchid
parameter - Submit Bit, check if it failed: if GENQY#5 fails, redirect to
item.php?error=3&artistid=AID&merchid=MID
whereAID
isartistid
parameter andMID
is themerchid
parameter - Else-wise bid is successful: redirect to
item.php?success=1&artistid=AID&merchid=MID
whereAID
isartistid
parameter andMID
is themerchid
parameter
General Queries (GENQY)
Items available for bidding (GENQY#1)
SELECT `ArtistID`,`MerchID`,`MerchTitle`,`AuctionEnd` FROM `merchandise` CROSS JOIN `options` WHERE `MerchMinBid` > 0 ORDER BY `ArtistID`,`MerchID`;
Item information for bidding (GENQY#2)
Replace $artistid
with the ArtistId
Replace $merchid
with the MerchId
SELECT `ArtistID`,`MerchID`,`MerchTitle`,`MerchMinBid`,`MerchQuickSale`,`MerchMedium`,`MerchSold`,`AuctionEnd`,`AuctionCutoff` FROM `merchandise` CROSS JOIN `options` WHERE `MerchMinBid` > 0 AND `ArtistID` = $artistid AND `MerchID` = $merchid;
Check if bid is valid (GENQY#3)
Replace $artistid
with the ArtistId
Replace $merchid
with the MerchId
Replace $bidvalue
with the bid value
SELECT `MerchSold`,MAX(`value`) AS `currentbid`,COUNT(`value`) AS `bidcount`,`AuctionEnd`,`AuctionCutoff` FROM `merchandise` LEFT JOIN `bids` USING (`ArtistID`,`MerchID`) CROSS JOIN `options` WHERE `ArtistID` = $artistid AND `MerchID` = $merchid AND `MerchMinBid` BETWEEN 1 AND $bidvalue GROUP BY `ArtistID`,`MerchID`;
Check if bidder exists (GENQY#4)
Replace $biddernumber
with the bidder number
SELECT `bidderno` FROM `bidders` WHERE `bidderno` = $biddernumber;
Submit bid (GENQY#5)
Replace $name
with the bidder name
Replace $biddernumber
with the bidder number
Replace $artistid
with the ArtistId
Replace $merchid
with the MerchId
Replace $bidvalue
with the bid value
INSERT INTO `bids` (`name`, `value`, `bidderno`, `ArtistID`, `MerchID`) VALUES ( $name, $bidvalue, $biddernumber, $artistid, $merchid );
Check bids for merch (GENQY#6)
Replace $artistid
with the ArtistId
Replace $merchid
with the MerchId
SELECT `name`,`value` FROM `bids` WHERE `ArtistID` = $artistid AND `MerchID` = $merchid ORDER BY `value`;
Check the Last bidder for merch (GENQY#7)
Replace $artistid
with the ArtistId
Replace $merchid
with the MerchId
SELECT `bidderno` FROM `bids` WHERE `ArtistID` = $artistid AND `MerchID` = $merchid ORDER BY `value` DESC LIMIT 1;
Info Output (INFO)
Failed to show item (INFO#1)
Unable to show selected item, please try again.
Invalid submission (INFO#2)
Unable to submit bid, please try again.
Invalid bidder (INFO#3)
The bidder number you provided was not found. If you have not registered yet, please contact a staff member.
Failed submission (INFO#4)
Oops, looks like you might have been out bid!
Successful submission (INFO#5)
Your bid has been submitted! Good luck!
Piece has been sold (INFO#6)
This piece has already been sold!
Piece is going to auction (INFO#7)
This piece is going to live auction! Ask the staff for when the, live auction will happen.
Auction is over (INFO#8)
Sorry! The auction is now closed!
Bidder bid twice in a row (INFO#9)
Sorry! The auction is now closed!