# PRD — promotions Evidence date: 2026-10-06. Source: `git@bitbucket.org:overdrive/promotions.git`, `master` at `f06ff39` (2026-08-28, "Merged in #112_Promo-Planner-bugfixes (pull request #1)"). There is no prior PRD in Silicon Ark and no README in the source repository. Requirements below are recovered from the Web Forms pages, SQL scripts, and Git history. Provenance is labelled. No live secrets are copied here. Connection strings are environment variables. `.env.example` names the catalog `Superbowl` and uses integrated security against localhost. That file is an example, not evidence of the production server. ## Product purpose **Implemented name.** RGBC Promotions Module (`Admin.Master` title and heading). Assembly `RGBC_Promotions`. **Implemented behaviour.** A Windows-authenticated user who exists in Superbowl can: - search, add, and edit promotional activities; - set monthly promotion counts on a planning grid, which inserts or deletes promotion rows; - edit account, date, duration, rep, segment, and rep manager on those rows; - import those account, date, and rep fields from Excel and export the grids; - search the promotion audit log; - assign brands and a domestic region to a user when their account allows it. **Unknown / Requires Confirmation:** the business definition of a "promotion" versus an "activity", who the business owner is, and which Superbowl environment is production. The UI label "Promotion ID" on Activity screens is `promoActivity.ActivityID` formatted `PR` plus four digits. `dwfPromo.PromoID` is a different integer, shown as `PromoID` on the Promotions grid and in the audit log. ## Users and roles Login does not collect a password. `Index.aspx` reads `Request.LogonUserIdentity.Name`, upper-cases it, and loads one row from `mstUser` joined to `mstUserGroupPromotion` (`sql_get_Users(..., "Promotion")`). A match stores the row in session and inserts `mstUsageLog` with `strSystem = 'Forecast'`. No match shows "Insufficient Access" and tells the person that login is controlled through Active Directory. Provenance: **Implemented** in `Index.aspx.vb` and `SystemFunctions.LogginUser`. The usage-log system name `Forecast` is **Implemented**. Whether that name is intentional for this module is **Unknown / Requires Confirmation**. `Web.config` sets `authentication mode="Windows"`. `roleManager enabled="false"`. | Actor | How the code treats them | Status | |---|---|---| | Windows user with a `mstUser` row | Session `USER`. Menu shows `Logout: ` plus `strUserName`. | **Implemented** | | Windows user with no row | Stays on Index. Message names Active Directory and the system administrator. | **Implemented** | | User with `blnView` not true | Master page clears the content area and shows a view-rights message. | **Implemented** | | `strUserGroup = "Dev"` | Sandbox menu item is left in the menu. The item is not selectable, and `zSandbox.aspx` is not in the repository. | **Implemented** menu rule | | `strUserGroup = "RSM"` | Region dropdown on Promotions keeps only `strRegion`, then adds `- All -`. Empty region shows a link to My Brands. | **Implemented** | | `blnSpecial` true | My Brands lists every user and allows saving another user's brands and region. | **Implemented** | | `blnSpecial` false | My Brands keeps only the signed-in user in the dropdown. Save is allowed only for that user id. | **Implemented** | | `refUserGroupPromotionID` in 4, 5, 6, 7 | May save and delete promotion rows, and may import. May mark an activity inactive and change the Active checkbox. | **Implemented** numeric check | | `refUserGroupPromotionID` in 5, 6, 7 | May save the planning grid (insert and delete counts). Group 4 is not included. | **Implemented** | | Anyone else with `blnView` | May open the pages and export after a successful load. Save, import, and planning save stay disabled or return a permission message. | **Implemented** | | Debug build, empty session | Master page assigns session user `RGBC\MAANIE` for domain `Promotion` before redirecting if that row is missing. | **Implemented** only inside `#If DEBUG` | Comments disagree about the names of groups 4–7: - `Activity.aspx.vb`: "Allow Admin (typically group 4, 5, 6, 7) and RSM. Adjust group IDs based on actual system configuration." - `ActivityDetails.aspx.vb`: "Admin (4) and RSM (5, 6, 7)". - The Active checkbox note in the markup says "(Admin/RSM only)". The names Admin and RSM for those ids are **Inferred** from comments, not from a query of `mstUserGroupPromotion`. The string `strUserGroup = "RSM"` is a separate check from the numeric ids. What each id is called in the database is **Unknown / Requires Confirmation**. `blnDelete` is passed into `Branding.colourDataRow` as the flag that enables the duration textbox on the promotions grid. Provenance: **Implemented** call. The column name `blnDelete` controlling duration editing is **Inferred** from that argument; the flag's original meaning is not documented. ## Functional requirements ### FR-001 — Sign in with the Windows identity **Who:** any person who can reach the site. **Trigger:** Login on `Index.aspx`, or a later page finding an empty session and calling `LogginUser`. **Inputs:** Windows logon name. **Behaviour:** clear any previous session on a fresh load of Index. Look up `mstUser.strDomainLogin`. On success, redirect to `Home.aspx` and write `mstUsageLog`. On failure, stay on Index. If a content page is opened with no session, `Admin.Master` redirects to `Index.aspx?M=1` (except the debug fallback above). `M=1` shows "Your Session has expired and you have been logged out." The menu item labelled Logout navigates to `Index.aspx`, which clears the session. It does not sign the user out of Windows. **Validation:** domain login is passed through `db.CleanString` (trim, double single quotes, turn double quotes into single quotes). **Errors:** lookup exception shows the exception message and stack on the page. **Evidence:** `Index.aspx.vb`, `Admin.Master.vb`, `SystemFunctions.vb`. **Implemented.** **Confidence:** High. ### FR-002 — Home **Who:** a user with `blnView`. **Behaviour:** render an empty fieldset "Home:". The forecast report that used to call `Branding` is commented out. The loading image is hidden when the page finishes. **Evidence:** `Home.aspx`, `Home.aspx.vb`. **Implemented.** The report is not current behaviour. **Confidence:** High. ### FR-003 — Search and list activities **Who:** a signed-in user. Brand dropdown is limited to brands in `mstUserBrand` for the session user id, active brands only (`sdsBrand`). **Trigger:** Search on `Activity.aspx`. Returning from Activity Details with query-string filters reloads the grid. **Inputs:** brand, channel, activity type, activity name, status (active / inactive / all). **Behaviour:** query `promoActivity` joined to `mstBrand`. Status `1` requires `blnActive = 1`. Status `0` requires inactive or null. Activity name dropdown is rebound when status changes, using the same status rule. Each row shows a running number, a link to details, and `PR` plus `ActivityID` on four digits (`PR0000` when the id cannot be read). Changing status does not by itself reload the grid until Search. **Outputs:** grid. Mark Inactive is visible only when `CanToggleActiveStatus` is true and the row is active. **Evidence:** `Activity.aspx`, `Activity.aspx.vb`. **Implemented.** **Confidence:** High. ### FR-004 — Create and update an activity **Who:** a signed-in user. Brand list is again `mstUserBrand` for active brands. The page does not check groups 4–7 before save. Only the Active flag is permission-checked. **Trigger:** Add on the list, or a row link. `ActivityID=0` means new. **Inputs:** activity name, activity type (`promoActivityType`), brand, channel (`mstChannelSegment` as `segmentPrimary\segment`, excluding Export, House, Not Allocated, Unknown, and `- Old -`), Active checkbox, tags. **Behaviour:** - New rows start Active, and the checkbox is disabled. Insert always stores `blnActive = 1`. - Required: non-blank name, type, brand other than `0`, channel. - Tags are a pipe-delimited string in a hidden field, maximum 500 characters on the server. The browser script also rejects a pipe inside one tag, rejects a case-insensitive duplicate, and warns at 400 characters. The server does not repeat the pipe or duplicate checks. - Update writes name, type, brand, channel, active, last user, `dtStamp` (`yyyyMMddHHmm`), and tags in one transaction, then updates linked `dwfPromo` rows (FR-005). - Users outside groups 4–7 cannot change `blnActive`; the previous value is kept. The note "(Admin/RSM only)" is shown for them on an existing activity. - Cancel returns to the list with the filter query string preserved. - Last edit is shown as `Last Edit: {user} - {d MMMM yyyy HH:mm}` when `dtStamp` has at least 12 digits. **Errors:** missing activity, validation text, or the exception message on the page. **Evidence:** `ActivityDetails.aspx`, `ActivityDetails.aspx.vb`. **Implemented.** **Confidence:** High. ### FR-005 — Keep promotion rows aligned when an activity changes **Who:** the user who saves an existing activity. **Behaviour:** `dwfPromo` rows with `refActivityID` equal to that activity, or with a null `refActivityID` and the same old name, brand, and channel (`strSegments`), are updated to the new name, type, brand, channel, and `refActivityID`. Activity type is not part of the legacy match. **Evidence:** `CascadeDwfPromoAfterActivityUpdate`. **Implemented.** **Confidence:** High. ### FR-006 — Mark an activity inactive **Who:** groups 4, 5, 6, 7. **Trigger:** Mark Inactive on an active row. **Behaviour:** set `blnActive = 0`, `strLastUser`, and `dtStamp`. There is no Mark Active button on the list. An already inactive row is reported and not updated. The code comment says delete was removed. No delete control is on the activity grid. **Evidence:** `Activity.aspx.vb`. **Implemented.** **Confidence:** High. ### FR-007 — Plan promotion counts **Who:** any viewer can load and export. Save requires groups 5, 6, or 7. **Trigger:** Load on `Planning.aspx` after a fiscal year (`mstPeriod.intFiscalYear > 2011`) and an active activity are selected. The activity dropdown lists `promoActivity` rows that are active, whose brand is in the user's `mstUserBrand`, and that either already have a `dwfPromo` row in that year or have no `dwfPromo` rows at all. **Behaviour:** the grid is one row per domestic rep manager (`mstBranch.strRegion = 'Domestic'`, `strRegionOrder <> '0'`, joined to `mstRepManager`) plus a Total row. Columns are Jan–Dec and Total. Each month cell is the count of `dwfPromo` rows for that period and activity. A row matches the activity when `refActivityID` equals the activity, or when `refActivityID` is null and name, brand, and channel match. Month counts are edited as text. Paging is 80 rows. Edits on the current page are copied into session before a page change. Save compares the session grid with a fresh database count: - a higher count inserts that many `dwfPromo` rows for the period, brand, rep manager, region, activity type, name, channel, and `refActivityID`, with log text "New Record from Planning"; - a lower count deletes that many matching rows whose `strCustomerNo` is null or empty, oldest `PromoID` first; - rows that already have a customer number are not deleted. The message reports how many of the requested deletes were skipped for that reason. Each insert and delete writes `logPromo` (`Promo added` or `Promo deleted`) on the same transaction. The Promotion ID label shows `PR` plus the activity id when an activity is selected. **Evidence:** `Planning.aspx`, `Planning.aspx.vb`, `PagedGridEdit.vb`. **Implemented.** **Confidence:** High for the save rules. The activity-dropdown SQL is long; the description above follows that `SelectCommand`. ### FR-008 — Set campaign duration from Planning **Who:** the same users who can save planning, because the button calls `Save()` first. **Trigger:** Set Campaign Duration. **Inputs:** `txtNewCD`. **Behaviour:** after the planning save, `UPDATE dwfPromo SET strDuration` for every row in the selected year that matches the activity filter. No separate audit row was found for this update. No length or format check was found on `txtNewCD`. **Evidence:** `btnSetCampaignDuration_Click`, `SetCampaignDuration`. **Implemented.** **Confidence:** High. ### FR-009 — List, edit, export, and delete promotion rows **Who:** viewers can load and export when year, start month, and end month are selected (not `00`). Save, import, and delete require groups 4, 5, 6, 7. **Filters:** fiscal year, start month, end month (both required; comment dated 2021-04-21), brand (user's active brands that already appear on `dwfPromo`), domestic region, channel (`strSegments` distinct from `dwfPromo`), activity name (from `promoActivity`, narrowed by status), activity type (distinct from `dwfPromo`), rep manager (those already used on `dwfPromo`), status via `promoActivity.blnActive`. RSM users lose every region except their own, then `- All -` is added back. **Grid columns used on save:** segment (`strChannelPrimary` from the segment dropdown), account number (`strCustomerNo`), date, duration, rep, rep manager id. Paging is 120 rows so the post stays under `aspnet:MaxHttpCollectionKeys` 2000. The whole grid is held in session. The current page is written back into that session before paging or save. **Validation on save (master `f06ff39`):** - account number longer than 7 characters is left unchanged and mentioned in the page message; - a date that differs from the database, is longer than 1 character, and is not length 10 is left unchanged and audited as `INVALID DATE FORMAT`; - unchanged rows are skipped; - changed rows update `dwfPromo` and append `strLog`, and write one `logPromo` row per changed field (`SegmentL3`, `AccountNo`, `Date`, `Duration`, `Rep`, `Rep Manager`). Blank account, date, or rep on `f06ff39` can be written through as an empty value. Commit `6b06854`, not on `master`, keeps the existing value when those posted fields are blank. Provenance of the blank-wipe behaviour: **Implemented** on `master`. Provenance of the keep-existing behaviour: **not on `master`**. **Delete:** confirm in the browser, then delete that `PromoID` and write `Promo deleted`. Permission matches save. **Export:** `.xlsx` worksheet "Promotions". Row 1 is year, brand, region, and channel. Row 2 is column names. Data starts at row 3. File name `Promotions_{year}_{region}_{yyyyMMddHHmm}.xlsx`. **Evidence:** `Promotions.aspx`, `Promotions.aspx.vb`. **Implemented.** **Confidence:** High. ### FR-010 — Import promotion updates from Excel **Who:** groups 4, 5, 6, 7. The Promotions page enables Import for those groups and redirects to `Import.aspx`. Import is not on the main menu. **Inputs:** one `.xlsx` file. Other extensions are rejected. The file is saved under the web app `Upload\` folder with the original name plus a timestamp. **Behaviour:** read worksheet 1 from row 3, 15 columns. Column indexes default to PromoID 0, Rep 10, AccountNo 12, Date 14. If row 2 contains headers `PROMOID`, `REP`, `ACCOUNTNO`, or `DATE`, those columns are used instead. For each data row: - skip a PromoID that is not a positive integer or that does not exist; - account number longer than 7 characters is left unchanged; - a date that would change the stored value, is longer than 1 character, and is not length 10 is left unchanged and audited as invalid; - if nothing would change, skip the row; - otherwise update `strCustomerNo`, `strDate` (separators removed), and `strRep`, prefix `strLog` with `IMPORT::`, and write `logPromo` on the same transaction. The page reports how many records were imported plus per-row messages. On `master`, an empty Excel cell is not treated as "leave unchanged" except by the length rules above. `6b06854` adds that treatment for empty account, date, and rep. That commit is not on `master`. **Evidence:** `Import.aspx.vb`, `Promotions.aspx.vb` `btnImport_Click`. **Implemented.** **Confidence:** High. ### FR-011 — Audit log **Who:** any user who can open the page. No extra group check was found. **Trigger:** Search. **Inputs:** start date (default first of the current month), end date (default today), ignore-dates checkbox, user (`mstUser.strUserName`), brand, domestic region, activity type, activity name (top 500 distinct names from `dwfPromo`), channel, free text, and "updates only" (exclude field names `Promo added` and `Promo deleted`). **Behaviour:** start and end are required unless ignore is checked. The query joins `logPromo` to `dwfPromo` and `mstBrand`, newest first, and stops at 5,000 rows (it selects 5,001 to detect the cap). The grid pages at 100. The message shows the filters and the row count, and says when the cap was hit. Export is enabled when there is at least one row and writes `.xlsx` the same way as the other exports. Free text matches `PromoID`, old value, new value, or field name with `LIKE`. **Evidence:** `AuditLog.aspx`, `AuditLog.aspx.vb`. **Implemented.** **Confidence:** High. ### FR-012 — My Brands **Who:** a user with `blnSpecial` may pick any user. Others may load and save only themselves. **Behaviour:** Load reads `mstUser.strRegion` and the `mstUserBrand` rows, and checks the matching brands and region. Save deletes that user's `mstUserBrand` rows and inserts the checked active brands, then sets `mstUser.strRegion`, in one transaction. If the target is the signed-in user, the session region is updated. Failure rolls back; the empty `Catch` does not show a message. Brand and user labels include a count of assignments. Regions are domestic branches with `strRegionOrder <> '0'`. **Evidence:** `MyBrands.aspx`, `MyBrands.aspx.vb`. **Implemented.** **Confidence:** High. ## Workflows ### Plan an activity, then fill in the accounts 1. User logs in at Index and lands on Home. 2. User opens Activity, searches, and adds an activity (name, type, brand, channel, tags). The new activity is active and receives `PR####`. 3. User opens Planning, picks the year and that activity, types monthly counts per rep manager, and saves. The application inserts one `dwfPromo` row per new count, with no customer number. 4. User opens Promotions, filters the year and months, and fills segment, account number, date, duration, rep, and rep manager. Save writes the audit log. 5. Alternatively the user exports the grid, edits account, date, and rep in Excel, and imports the file. Reducing a planning count later deletes only rows that still have no customer number. ### Review who changed a promotion 1. User opens Audit Log. 2. User sets a date range, optional brand, region, activity, and search text. 3. The grid shows date, stamp, PromoID, field, old value, new value, user, and the promotion's brand, region, type, name, and channel when the promotion row still exists. Deleted promotions remain in the log with a left join, so brand and region can be empty. ## Integrations | System | Purpose | Direction | Auth | Failure | |---|---|---|---|---| | Windows / Active Directory | Identity of the browser user. The page text says access is controlled through Active Directory. The code only reads the logon name and looks it up in `mstUser`. | Inbound identity | IIS Windows authentication | No `mstUser` row: insufficient access | | SQL Server Superbowl | All product data | Read and write | Integrated security in the example. The real account is whatever the environment variable contains. | `db.doQuery` writes the SQL and the exception into the response and returns `Nothing`. Several pages then roll back a transaction. | | Excel `.xlsx` | Import and export | File upload and download | The signed-in session | Bad extension or unreadable workbook: message on the page. Upload is stored on the web server under `Upload\`. | No HTTP API, queue, or mail integration was found. `ProjectFunctions.sql_insert_BrandQF_Job` inserts `sysCronJob` rows of type `BrandQF`. No page in this repository calls it. Provenance: **Implemented** helper, unused by the pages that were read. Whether another process runs those jobs is **Unknown / Requires Confirmation**. ## Constraints - Dates in the database are compact strings (`yyyyMM` periods, `yyyyMMdd` dates, `yyyyMMddHHmm` stamps). The UI shows `yyyy-MM-dd` and `MMM yyyy`. - Fiscal years come from `mstPeriod` where `intFiscalYear > 2011`. Company fiscal start is `mstCompany` where `CompanyID = 1`. - Domestic regions only (`mstBranch.strRegion = 'Domestic'`). - Channels offered for new activities exclude Export, House, Not Allocated, Unknown, and `- Old -`. - Grids are paged because `MaxHttpCollectionKeys` is 2000. - `customErrors` is `Off` and trace is `pageOutput="true"` with `localOnly="false"`. Error text can include SQL. Provenance: **Implemented** in `Web.config`. - SQL is concatenated. `CleanString` doubles single quotes. It is not parameterised. - jQuery 1.4.1 and jQuery UI 1.8.6 are vendored. Date pickers use `yy-mm-dd`. ## Behaviour that needs a human decision - Group id 4 may edit promotion rows and activities, and may not save planning counts. Whether that split is intended is **Unknown / Requires Confirmation**. - On `master`, a blank import cell or a blank promotions-grid field can clear account, date, or rep. The unmerged branch stops that. Which behaviour is approved is **Unknown / Requires Confirmation**. - Activity save is not limited to groups 4–7, except for the Active flag. Whether every viewer may rename an activity is **Unknown / Requires Confirmation**. - Debug builds impersonate `RGBC\MAANIE` when the session is empty. Whether debug builds are deployed anywhere is **Unknown / Requires Confirmation**.