VbzCart/tables/cat items - Revision history

Woozle: /* SQL */ updated hilite tag

2020-01-02T13:55:23Z

SQL: updated hilite tag

← Older revision		Revision as of 13:55, 2 January 2020
Line 50:		Line 50:
	* '''2017-07-02''' Changing length of '''GrpSort''' from 7 to 31 to match {{l/vbzcart/table\|ctg_groups}}.Sort		* '''2017-07-02''' Changing length of '''GrpSort''' from 7 to 31 to match {{l/vbzcart/table\|ctg_groups}}.Sort
	==SQL==		==SQL==
	<mysql>CREATE TABLE `cat_items` (		<source lang=mysql>CREATE TABLE `cat_items` (
	`ID` INT NOT NULL AUTO_INCREMENT,		`ID` INT NOT NULL AUTO_INCREMENT,
	`CatNum` VARCHAR(64) COMMENT "catalog number: must be unique or null; can be changed",		`CatNum` VARCHAR(64) COMMENT "catalog number: must be unique or null; can be changed",
Line 82:		Line 82:
	PRIMARY KEY(`ID`)		PRIMARY KEY(`ID`)
	)		)
	ENGINE = InnoDB;</~~mysql~~>		ENGINE = InnoDB;</source>

Woozle at 12:17, 2 July 2017

2017-07-02T12:17:49Z

← Older revision		Revision as of 12:17, 2 July 2017
Line 48:		Line 48:
	** Removing '''isCloseOut''' and '''isCurrent''': both are made unnecessary by SC_* fields (and revisions to the building process)		** Removing '''isCloseOut''' and '''isCurrent''': both are made unnecessary by SC_* fields (and revisions to the building process)
	** Removing '''Descr''' and '''QtyIn_Stk''': both are now dynamically calculated from other tables		** Removing '''Descr''' and '''QtyIn_Stk''': both are now dynamically calculated from other tables
			* '''2017-07-02''' Changing length of '''GrpSort''' from 7 to 31 to match {{l/vbzcart/table\|ctg_groups}}.Sort
	==SQL==		==SQL==
	<mysql>CREATE TABLE `cat_items` (		<mysql>CREATE TABLE `cat_items` (
Line 65:		Line 66:
	`GrpCode` VARCHAR(15) DEFAULT NULL COMMENT "grouping code - new header when this changes",		`GrpCode` VARCHAR(15) DEFAULT NULL COMMENT "grouping code - new header when this changes",
	`GrpDescr` VARCHAR(63) DEFAULT NULL COMMENT "group description - show in header, add to item description as shown in cart",		`GrpDescr` VARCHAR(63) DEFAULT NULL COMMENT "group description - show in header, add to item description as shown in cart",
	`GrpSort` VARCHAR(7) DEFAULT NULL COMMENT "sorting override (optional); sorting is by GrpSort+GrpCode",		`GrpSort` VARCHAR(31) DEFAULT NULL COMMENT "sorting override (optional); sorting is by GrpSort+GrpCode",
	`CatSfx` VARCHAR(31) DEFAULT NULL COMMENT "whatever is added to the title's catnum to make the item's catnum",		`CatSfx` VARCHAR(31) DEFAULT NULL COMMENT "whatever is added to the title's catnum to make the item's catnum",
	`ID_ShipCost` INT COMMENT "applicable shipping cost calculation data",		`ID_ShipCost` INT COMMENT "applicable shipping cost calculation data",

Woozle: fields removed

2017-06-20T11:14:59Z

fields removed

← Older revision		Revision as of 11:14, 20 June 2017
Line 4:		Line 4:
	==Fields==		==Fields==
	* '''ID_Supp''' ties the item directly back to its supplier. This should be set when new items are created during catalog building, and also when new "scratch" items are created during invoice entry.		* '''ID_Supp''' ties the item directly back to its supplier. This should be set when new items are created during catalog building, and also when new "scratch" items are created during invoice entry.
	* '''Descr''': Right now, this is just a holder for descriptions of directly-entered items. Eventually we may start storing the calculated description (from title + options) there. Until then, though, this field can be largely ignored.
	* '''CatSfx''' is basically whatever sets the item's catalog number apart from other items of the same title		* '''CatSfx''' is basically whatever sets the item's catalog number apart from other items of the same title
	* '''GrpCode''' and '''GrpDescr''' are for differentiating within an item type, e.g. a short-sleeved t-shirt which is available in both tie-dye and one or more solid colors. '''GrpCode''' becomes part of the catalog number for items in that group, and '''GrpDesc''' is the description for that group. When displaying items for a particular title, sorting should be GrpSort,GrpCode,ItOptSort. See [[VbzCart catalog building]] for more.		* '''GrpCode''' and '''GrpDescr''' are for differentiating within an item type, e.g. a short-sleeved t-shirt which is available in both tie-dye and one or more solid colors. '''GrpCode''' becomes part of the catalog number for items in that group, and '''GrpDesc''' is the description for that group. When displaying items for a particular title, sorting should be GrpSort,GrpCode,ItOptSort. See [[VbzCart catalog building]] for more.
Line 18:		Line 17:
	* '''SC_DateActive''': validity date of the current supplier catalog		* '''SC_DateActive''': validity date of the current supplier catalog
	==Future==		==Future==
	* '''isMaster, isAvail, isInPrint~~, isCloseOut, isCurrent~~, isPulled, isDumped''' should be changed from BOOL to BIT.		* '''isMaster, isAvail, isInPrint, isPulled, isDumped''' should be changed from BOOL to BIT.
	* '''QtyIn_Stk''' and '''cntCtgDup''' are obsolete and should be deprecated/removed.
	* '''Notes''' should be provided by a more efficient sparse annotation system (to be written).		* '''Notes''' should be provided by a more efficient sparse annotation system (to be written).
	==History==		==History==
Line 48:		Line 46:
	** Was tempted to rename Supp_CatNum to SC_CatNum, but decided against; it also appears on packing lists, not just catalogs		** Was tempted to rename Supp_CatNum to SC_CatNum, but decided against; it also appears on packing lists, not just catalogs
	** Removing '''cntCtgDup'''; not used in code, and 2017 building process does not set it.		** Removing '''cntCtgDup'''; not used in code, and 2017 building process does not set it.
			** Removing '''isCloseOut''' and '''isCurrent''': both are made unnecessary by SC_* fields (and revisions to the building process)
			** Removing '''Descr''' and '''QtyIn_Stk''': both are now dynamically calculated from other tables
	==SQL==		==SQL==
	<mysql>CREATE TABLE `cat_items` (		<mysql>CREATE TABLE `cat_items` (
Line 55:		Line 55:
	`isAvail` BOOL COMMENT "available from the Supplier",		`isAvail` BOOL COMMENT "available from the Supplier",
	`isInPrint` BOOL COMMENT "item is in current supplier catalog, and should be orderable",		`isInPrint` BOOL COMMENT "item is in current supplier catalog, and should be orderable",
	~~`isCloseOut` BOOL COMMENT "item is available as a close-out only; limited quantities, may be gone at any moment",~~
	~~`isCurrent` BOOL COMMENT "TRUE = isInPrint flag can be trusted; FALSE = don't assume out of print",~~
	`isPulled` BOOL COMMENT "TRUE = record is garbage or a duplicate, and nothing should refer to it",		`isPulled` BOOL COMMENT "TRUE = record is garbage or a duplicate, and nothing should refer to it",
	`isDumped` BOOL COMMENT "TRUE = pulled item which has been checked to make sure nobody is using it: ok to recycle",		`isDumped` BOOL COMMENT "TRUE = pulled item which has been checked to make sure nobody is using it: ok to recycle",
Line 77:		Line 75:
	`SC_LastUpdate` DATETIME DEFAULT NULL,		`SC_LastUpdate` DATETIME DEFAULT NULL,
	`SC_DateExpires` DATE DEFAULT NULL,		`SC_DateExpires` DATE DEFAULT NULL,
	~~`QtyIn_Stk` INT DEFAULT NULL COMMENT "total quantity of this item found across all for-sale stock locations",~~
	`QtyMin_Stk` INT COMMENT "minimum quantity to keep in stock",		`QtyMin_Stk` INT COMMENT "minimum quantity to keep in stock",
	`WhenCreated` DATETIME NOT NULL "set this to NOW() when creating new records",		`WhenCreated` DATETIME NOT NULL "set this to NOW() when creating new records",

Woozle: more field changes

2017-06-20T10:56:32Z

more field changes

← Older revision		Revision as of 10:56, 20 June 2017
Line 15:		Line 15:
	* '''cntCtgDup''' indicates which items were duplicates during the catalog building process. It may end up being unnecessary, but I put it in during debugging so I thought I should document it.		* '''cntCtgDup''' indicates which items were duplicates during the catalog building process. It may end up being unnecessary, but I put it in during debugging so I thought I should document it.
	* '''Supp_CatNum''': the supplier's catalog number for this item; used for entering packing lists		* '''Supp_CatNum''': the supplier's catalog number for this item; used for entering packing lists
	* '''~~Supp_LastUpdate~~''': when the supplier catalog record was last updated		* '''SC_LastUpdate''': when the supplier catalog record was last updated
	* '''~~Supp_CatDate~~''': validity date of the current supplier catalog		* '''SC_DateActive''': validity date of the current supplier catalog
	==Future==		==Future==
	* '''isMaster, isAvail, isInPrint, isCloseOut, isCurrent, isPulled, isDumped''' should be changed from BOOL to BIT.		* '''isMaster, isAvail, isInPrint, isCloseOut, isCurrent, isPulled, isDumped''' should be changed from BOOL to BIT.
Line 43:		Line 43:
	** Changed engine from MYISAM to INNODB.		** Changed engine from MYISAM to INNODB.
	* '''2017-06-18''' Adding '''Supp_LastUpdate''', '''Supp_CatDate'''		* '''2017-06-18''' Adding '''Supp_LastUpdate''', '''Supp_CatDate'''
			* '''2017-06-20''' More SC field changes:
			** Renaming Supp_LastUpdate -> '''SC_LastUpdate''', Supp_CatDate -> '''SC_DateActive'''
			** Adding '''SC_DateExpires'''
			** Was tempted to rename Supp_CatNum to SC_CatNum, but decided against; it also appears on packing lists, not just catalogs
			** Removing '''cntCtgDup'''; not used in code, and 2017 building process does not set it.
	==SQL==		==SQL==
	<mysql>CREATE TABLE `cat_items` (		<mysql>CREATE TABLE `cat_items` (
Line 69:		Line 74:
	`PriceList` DECIMAL(9,2) COMMENT "supplier's retail list price (if available)",		`PriceList` DECIMAL(9,2) COMMENT "supplier's retail list price (if available)",
	`Supp_CatNum` VARCHAR(32) COMMENT "supplier's catalog number, if available",		`Supp_CatNum` VARCHAR(32) COMMENT "supplier's catalog number, if available",
	`~~Supp_LastUpdate~~` DATETIME DEFAULT NULL,		`SC_DateActive` DATE DEFAULT NULL,
	`~~Supp_CatDate~~` ~~DATETIME~~ DEFAULT NULL,		`SC_LastUpdate` DATETIME DEFAULT NULL,
			`SC_DateExpires` DATE DEFAULT NULL,
	`QtyIn_Stk` INT DEFAULT NULL COMMENT "total quantity of this item found across all for-sale stock locations",		`QtyIn_Stk` INT DEFAULT NULL COMMENT "total quantity of this item found across all for-sale stock locations",
	`QtyMin_Stk` INT COMMENT "minimum quantity to keep in stock",		`QtyMin_Stk` INT COMMENT "minimum quantity to keep in stock",
	`WhenCreated` DATETIME NOT NULL "set this to NOW() when creating new records",		`WhenCreated` DATETIME NOT NULL "set this to NOW() when creating new records",
	`WhenUpdated` TIMESTAMP NULL ON UPDATE NOW() COMMENT "automatically updated when record changes",		`WhenUpdated` TIMESTAMP NULL ON UPDATE NOW() COMMENT "automatically updated when record changes",
	~~`cntCtgDup` INT COMMENT "count of duplicates caused by catalog building",~~
	`Notes` TEXT COMMENT "human-entered notes for admin use only",		`Notes` TEXT COMMENT "human-entered notes for admin use only",
	PRIMARY KEY(`ID`)		PRIMARY KEY(`ID`)
	)		)
	ENGINE = ~~INNODB~~;</mysql>		ENGINE = InnoDB;</mysql>

Woozle: battery low, saving edits

2017-06-18T16:07:35Z

battery low, saving edits

← Older revision		Revision as of 16:07, 18 June 2017
Line 9:		Line 9:
	* '''isCurrent''': If FALSE, display of item status should not make any assumptions about the correctness of the "isInPrint" flag. Instead of "out of print", display "availability unknown"; instead of "can probably get more", display "was available when last checked".		* '''isCurrent''': If FALSE, display of item status should not make any assumptions about the correctness of the "isInPrint" flag. Instead of "out of print", display "availability unknown"; instead of "can probably get more", display "was available when last checked".
	** '''TO DO''': How can we derive the date of the latest catalog showing this as available?		** '''TO DO''': How can we derive the date of the latest catalog showing this as available?
			* '''isInPrint''': FALSE = even if this item is available, it is no longer being printed
	* '''isPulled''': TRUE = the record is being retired, but isn't ready to be recycled yet. This gives us a chance to see if the pulling was done in error. The item will still show up in listings by title. Pulling typically happens when duplicate items are created by mistake. When trying to decide which of two identical records to pull, pull the higher-numbered one if there are no other considerations.		* '''isPulled''': TRUE = the record is being retired, but isn't ready to be recycled yet. This gives us a chance to see if the pulling was done in error. The item will still show up in listings by title. Pulling typically happens when duplicate items are created by mistake. When trying to decide which of two identical records to pull, pull the higher-numbered one if there are no other considerations.
	* '''isDumped''': TRUE = the record is ready to be recycled.		* '''isDumped''': TRUE = the record is ready to be recycled.
	* '''QtyIn_Stk''' is updated by the catalog building process; later on, we will want to have a finer-grained process for updating this so the entire catalog build doesn't have to be run every time an item moves in/out of stock		* '''QtyIn_Stk''' is updated by the catalog building process; later on, we will want to have a finer-grained process for updating this so the entire catalog build doesn't have to be run every time an item moves in/out of stock
	* '''cntCtgDup''' indicates which items were duplicates during the catalog building process. It may end up being unnecessary, but I put it in during debugging so I thought I should document it.		* '''cntCtgDup''' indicates which items were duplicates during the catalog building process. It may end up being unnecessary, but I put it in during debugging so I thought I should document it.
			* '''Supp_CatNum''': the supplier's catalog number for this item; used for entering packing lists
			* '''Supp_LastUpdate''': when the supplier catalog record was last updated
			* '''Supp_CatDate''': validity date of the current supplier catalog
	==Future==		==Future==
	* '''isMaster, isAvail, isInPrint, isCloseOut, isCurrent, isPulled, isDumped''' should be changed from BOOL to BIT.		* '''isMaster, isAvail, isInPrint, isCloseOut, isCurrent, isPulled, isDumped''' should be changed from BOOL to BIT.
Line 38:		Line 42:
	** Added '''WhenCreated''', '''WhenUpdated'''. Despite how promising [http://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html this] sounds, there can be only one auto-stamped field in a table. I decided it was harder to be sure that updates were recorded than creations, so I made that one auto-stamped on update.		** Added '''WhenCreated''', '''WhenUpdated'''. Despite how promising [http://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html this] sounds, there can be only one auto-stamped field in a table. I decided it was harder to be sure that updates were recorded than creations, so I made that one auto-stamped on update.
	** Changed engine from MYISAM to INNODB.		** Changed engine from MYISAM to INNODB.
			* '''2017-06-18''' Adding '''Supp_LastUpdate''', '''Supp_CatDate'''
	==SQL==		==SQL==
	<mysql>CREATE TABLE `cat_items` (		<mysql>CREATE TABLE `cat_items` (
Line 64:		Line 69:
	`PriceList` DECIMAL(9,2) COMMENT "supplier's retail list price (if available)",		`PriceList` DECIMAL(9,2) COMMENT "supplier's retail list price (if available)",
	`Supp_CatNum` VARCHAR(32) COMMENT "supplier's catalog number, if available",		`Supp_CatNum` VARCHAR(32) COMMENT "supplier's catalog number, if available",
			`Supp_LastUpdate` DATETIME DEFAULT NULL,
			`Supp_CatDate` DATETIME DEFAULT NULL,
	`QtyIn_Stk` INT DEFAULT NULL COMMENT "total quantity of this item found across all for-sale stock locations",		`QtyIn_Stk` INT DEFAULT NULL COMMENT "total quantity of this item found across all for-sale stock locations",
	`QtyMin_Stk` INT COMMENT "minimum quantity to keep in stock",		`QtyMin_Stk` INT COMMENT "minimum quantity to keep in stock",

Woozle: moved integrity check to a new subpage, added two more

2016-03-02T01:44:56Z

moved integrity check to a new subpage, added two more

← Older revision		Revision as of 01:44, 2 March 2016
Line 1:		Line 1:
	==About==		==About==
	* '''Purpose''': catalog items within a title, i.e. varieties in which the title is (or was ever) available		* '''Purpose''': catalog items within a title, i.e. varieties in which the title is (or was ever) available
			* '''Subpages''': [[/integrity checks]]
	==Fields==		==Fields==
	* '''ID_Supp''' ties the item directly back to its supplier. This should be set when new items are created during catalog building, and also when new "scratch" items are created during invoice entry.		* '''ID_Supp''' ties the item directly back to its supplier. This should be set when new items are created during catalog building, and also when new "scratch" items are created during invoice entry.
Line 14:		Line 15:
	==Future==		==Future==
	* '''isMaster, isAvail, isInPrint, isCloseOut, isCurrent, isPulled, isDumped''' should be changed from BOOL to BIT.		* '''isMaster, isAvail, isInPrint, isCloseOut, isCurrent, isPulled, isDumped''' should be changed from BOOL to BIT.
			* '''QtyIn_Stk''' and '''cntCtgDup''' are obsolete and should be deprecated/removed.
	* '''Notes''' should be provided by a more efficient sparse annotation system (to be written).		* '''Notes''' should be provided by a more efficient sparse annotation system (to be written).
	==History==		==History==
Line 33:		Line 35:
	*** Deprecated '''Descr''' (renamed it to Descr_DONT_USE in the database; removed from doc page).		*** Deprecated '''Descr''' (renamed it to Descr_DONT_USE in the database; removed from doc page).
	*** Removed '''isForSale''' from doc page.		*** Removed '''isForSale''' from doc page.
			* '''2016-03-01'''
			** Added '''WhenCreated''', '''WhenUpdated'''. Despite how promising [http://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html this] sounds, there can be only one auto-stamped field in a table. I decided it was harder to be sure that updates were recorded than creations, so I made that one auto-stamped on update.
			** Changed engine from MYISAM to INNODB.
	==SQL==		==SQL==
	<mysql>CREATE TABLE `cat_items` (		<mysql>CREATE TABLE `cat_items` (
	`ID` INT NOT NULL AUTO_INCREMENT,		`ID` INT NOT NULL AUTO_INCREMENT,
	`CatNum` VARCHAR(64) COMMENT "catalog number: must be unique or null; can be changed",		`CatNum` VARCHAR(64) COMMENT "catalog number: must be unique or null; can be changed",
	`isMaster` BOOL COMMENT "contains multiple items; split apart when moving into stock",		`isMaster` BOOL COMMENT "contains multiple items; split apart when moving into stock",
	`isAvail` BOOL COMMENT "available from the Supplier",		`isAvail` BOOL COMMENT "available from the Supplier",
	`isInPrint` BOOL COMMENT "item is in current supplier catalog, and should be orderable",		`isInPrint` BOOL COMMENT "item is in current supplier catalog, and should be orderable",
	`isCloseOut` BOOL COMMENT "item is available as a close-out only; limited quantities, may be gone at any moment",		`isCloseOut` BOOL COMMENT "item is available as a close-out only; limited quantities, may be gone at any moment",
	`isCurrent` BOOL COMMENT "TRUE = isInPrint flag can be trusted; FALSE = don't assume out of print",		`isCurrent` BOOL COMMENT "TRUE = isInPrint flag can be trusted; FALSE = don't assume out of print",
	`isPulled` BOOL COMMENT "TRUE = record is garbage or a duplicate, and nothing should refer to it",		`isPulled` BOOL COMMENT "TRUE = record is garbage or a duplicate, and nothing should refer to it",
	`isDumped` BOOL COMMENT "TRUE = pulled item which has been checked to make sure nobody is using it: ok to recycle",		`isDumped` BOOL COMMENT "TRUE = pulled item which has been checked to make sure nobody is using it: ok to recycle",
	`ID_Supp` INT NOT NULL COMMENT "cat_supp.ID",		`ID_Supp` INT NOT NULL COMMENT "cat_supp.ID",
	`ID_Title` INT NOT NULL COMMENT "cat_titles.ID",		`ID_Title` INT NOT NULL COMMENT "cat_titles.ID",
	`ID_ItTyp` INT COMMENT "cat_ittyps.ID: basic type of item",		`ID_ItTyp` INT COMMENT "cat_ittyps.ID: basic type of item",
	`ID_ItOpt` INT COMMENT "cat_itopts.ID: item option (e.g. size)",		`ID_ItOpt` INT COMMENT "cat_itopts.ID: item option (e.g. size)",
	`ItOpt_Descr` VARCHAR(63) DEFAULT NULL COMMENT "overrides cat_itopts.Descr if present",		`ItOpt_Descr` VARCHAR(63) DEFAULT NULL COMMENT "overrides cat_itopts.Descr if present",
	`ItOpt_Sort` VARCHAR(63) DEFAULT NULL COMMENT "concatenation of IGroup, Option, and Item Type sorting indexes",		`ItOpt_Sort` VARCHAR(63) DEFAULT NULL COMMENT "concatenation of IGroup, Option, and Item Type sorting indexes",
	`GrpCode` VARCHAR(15) DEFAULT NULL COMMENT "grouping code - new header when this changes",		`GrpCode` VARCHAR(15) DEFAULT NULL COMMENT "grouping code - new header when this changes",
	`GrpDescr` VARCHAR(63) DEFAULT NULL COMMENT "group description - show in header, add to item description as shown in cart",		`GrpDescr` VARCHAR(63) DEFAULT NULL COMMENT "group description - show in header, add to item description as shown in cart",
	`GrpSort` VARCHAR(7) DEFAULT NULL COMMENT "sorting override (optional); sorting is by GrpSort+GrpCode",		`GrpSort` VARCHAR(7) DEFAULT NULL COMMENT "sorting override (optional); sorting is by GrpSort+GrpCode",
	`CatSfx` VARCHAR(31) DEFAULT NULL COMMENT "whatever is added to the title's catnum to make the item's catnum",		`CatSfx` VARCHAR(31) DEFAULT NULL COMMENT "whatever is added to the title's catnum to make the item's catnum",
	`ID_ShipCost` INT COMMENT "applicable shipping cost calculation data",		`ID_ShipCost` INT COMMENT "applicable shipping cost calculation data",
	`PriceBuy` DECIMAL(9,2) COMMENT "wholesale price from supplier",		`PriceBuy` DECIMAL(9,2) COMMENT "wholesale price from supplier",
	`PriceSell` DECIMAL(9,2) COMMENT "price to customer",		`PriceSell` DECIMAL(9,2) COMMENT "price to customer",
	`PriceList` DECIMAL(9,2) COMMENT "supplier's retail list price (if available)",		`PriceList` DECIMAL(9,2) COMMENT "supplier's retail list price (if available)",
	`Supp_CatNum` VARCHAR(32) COMMENT "supplier's catalog number, if available",		`Supp_CatNum` VARCHAR(32) COMMENT "supplier's catalog number, if available",
	`QtyIn_Stk` INT DEFAULT NULL COMMENT "total quantity of this item found across all for-sale stock locations",		`QtyIn_Stk` INT DEFAULT NULL COMMENT "total quantity of this item found across all for-sale stock locations",
	`QtyMin_Stk` INT COMMENT "minimum quantity to keep in stock",		`QtyMin_Stk` INT COMMENT "minimum quantity to keep in stock",
	`cntCtgDup` INT COMMENT "count of duplicates caused by catalog building",		`WhenCreated` DATETIME NOT NULL "set this to NOW() when creating new records",
	`Notes` TEXT COMMENT "human-entered notes for admin use only",		`WhenUpdated` TIMESTAMP NULL ON UPDATE NOW() COMMENT "automatically updated when record changes",
	PRIMARY KEY(`ID`)		`cntCtgDup` INT COMMENT "count of duplicates caused by catalog building",
	)		`Notes` TEXT COMMENT "human-entered notes for admin use only",
	ENGINE = ~~MYISAM~~;</mysql>		PRIMARY KEY(`ID`)
	~~===query===~~		)
	~~This query should return with no rows, though at present there is nothing in the design or data code to absolutely prevent this from happening:~~		ENGINE = INNODB;</mysql>
	~~<mysql>SELECT~~
	~~CatNum, COUNT(CatNum) AS SameRows~~
	~~FROM~~
	~~`vbz-vc`.cat_items~~
	~~WHERE~~
	~~NOT isPulled~~
	~~GROUP BY CatNum~~
	~~HAVING SameRows > 1</mysql>~~
	The supplier catalog build process will check for duplicates to the catalog numbers it suggests for new items, but items entered manually could still have duplicate catalog numbers. Possibly there should be a unique key consisting of (CatNum,isPulled), but that would also prevent duplicate ''pulled'' and ''dumped'' items, which would be a problem.

Woozle: removed 2, added 1

2016-02-29T21:35:44Z

removed 2, added 1

← Older revision		Revision as of 21:35, 29 February 2016
Line 12:		Line 12:
	* '''QtyIn_Stk''' is updated by the catalog building process; later on, we will want to have a finer-grained process for updating this so the entire catalog build doesn't have to be run every time an item moves in/out of stock		* '''QtyIn_Stk''' is updated by the catalog building process; later on, we will want to have a finer-grained process for updating this so the entire catalog build doesn't have to be run every time an item moves in/out of stock
	* '''cntCtgDup''' indicates which items were duplicates during the catalog building process. It may end up being unnecessary, but I put it in during debugging so I thought I should document it.		* '''cntCtgDup''' indicates which items were duplicates during the catalog building process. It may end up being unnecessary, but I put it in during debugging so I thought I should document it.
			==Future==
			* '''isMaster, isAvail, isInPrint, isCloseOut, isCurrent, isPulled, isDumped''' should be changed from BOOL to BIT.
			* '''Notes''' should be provided by a more efficient sparse annotation system (to be written).
	==History==		==History==
	* '''2009-04-10''' Tentatively eliminating qtyInStock field (adding " OLD" to name) because (a) can't find any code that uses it, and (b) it really should be calculated dynamically, otherwise it's not reliable.		* '''2009-04-10''' Tentatively eliminating qtyInStock field (adding " OLD" to name) because (a) can't find any code that uses it, and (b) it really should be calculated dynamically, otherwise it's not reliable.
Line 23:		Line 26:
	* '''2011-02-24''' Extended '''Descr''' field from 63 chars to 127 chars because some descriptions ended up being longer (only by a few chars, though, which is good). '''Problem''', though, in that the build process reported no errors; only MySQL Query Browser reported errors. WTF? Further testing needed, but no time now.		* '''2011-02-24''' Extended '''Descr''' field from 63 chars to 127 chars because some descriptions ended up being longer (only by a few chars, though, which is good). '''Problem''', though, in that the build process reported no errors; only MySQL Query Browser reported errors. WTF? Further testing needed, but no time now.
	* '''2016-01-17''' Added '''Notes''' field because I needed to make notes on particular Items		* '''2016-01-17''' Added '''Notes''' field because I needed to make notes on particular Items
			* '''2016-02-29'''
			** A week or two ago:
			*** Deprecated '''isForSale''' (renamed it to isForSale_DONT_USE in the database).
			*** Added '''isAvail''' to indicate item is available from Supplier. "FOR SALE" status is now isAvail OR is in stock. Stock presence is calculated dynamically.
			** Today:
			*** Deprecated '''Descr''' (renamed it to Descr_DONT_USE in the database; removed from doc page).
			*** Removed '''isForSale''' from doc page.
	==SQL==		==SQL==
	<mysql>CREATE TABLE `cat_items` (		<mysql>CREATE TABLE `cat_items` (
	`ID` INT NOT NULL AUTO_INCREMENT,		`ID` INT NOT NULL AUTO_INCREMENT,
	`CatNum` VARCHAR(64) COMMENT "catalog number: must be unique or null; can be changed",		`CatNum` VARCHAR(64) COMMENT "catalog number: must be unique or null; can be changed",
	~~`isForSale` BOOL COMMENT "available to be sold/ordered (either in stock or in print)",~~
	`isMaster` BOOL COMMENT "contains multiple items; split apart when moving into stock",		`isMaster` BOOL COMMENT "contains multiple items; split apart when moving into stock",
			`isAvail` BOOL COMMENT "available from the Supplier",
	`isInPrint` BOOL COMMENT "item is in current supplier catalog, and should be orderable",		`isInPrint` BOOL COMMENT "item is in current supplier catalog, and should be orderable",
	`isCloseOut` BOOL COMMENT "item is available as a close-out only; limited quantities, may be gone at any moment",		`isCloseOut` BOOL COMMENT "item is available as a close-out only; limited quantities, may be gone at any moment",
Line 38:		Line 48:
	`ID_ItTyp` INT COMMENT "cat_ittyps.ID: basic type of item",		`ID_ItTyp` INT COMMENT "cat_ittyps.ID: basic type of item",
	`ID_ItOpt` INT COMMENT "cat_itopts.ID: item option (e.g. size)",		`ID_ItOpt` INT COMMENT "cat_itopts.ID: item option (e.g. size)",
	~~`Descr` VARCHAR(127) DEFAULT NULL COMMENT "item-specific description",~~
	`ItOpt_Descr` VARCHAR(63) DEFAULT NULL COMMENT "overrides cat_itopts.Descr if present",		`ItOpt_Descr` VARCHAR(63) DEFAULT NULL COMMENT "overrides cat_itopts.Descr if present",
	`ItOpt_Sort` VARCHAR(63) DEFAULT NULL COMMENT "concatenation of IGroup, Option, and Item Type sorting indexes",		`ItOpt_Sort` VARCHAR(63) DEFAULT NULL COMMENT "concatenation of IGroup, Option, and Item Type sorting indexes",

Woozle: duplicate catalog number query

2016-02-09T12:40:20Z

duplicate catalog number query

← Older revision		Revision as of 12:40, 9 February 2016
Line 57:		Line 57:
	)		)
	ENGINE = MYISAM;</mysql>		ENGINE = MYISAM;</mysql>
			===query===
			This query should return with no rows, though at present there is nothing in the design or data code to absolutely prevent this from happening:
			<mysql>SELECT
			CatNum, COUNT(CatNum) AS SameRows
			FROM
			`vbz-vc`.cat_items
			WHERE
			NOT isPulled
			GROUP BY CatNum
			HAVING SameRows > 1</mysql>
			The supplier catalog build process will check for duplicates to the catalog numbers it suggests for new items, but items entered manually could still have duplicate catalog numbers. Possibly there should be a unique key consisting of (CatNum,isPulled), but that would also prevent duplicate ''pulled'' and ''dumped'' items, which would be a problem.

Woozle: Notes field

2016-01-18T01:50:18Z

Notes field

Woozle: tidied obsolete markup

2014-07-06T17:18:16Z

tidied obsolete markup

← Older revision		Revision as of 17:18, 6 July 2014
Line 23:		Line 23:
	** '''2011-02-24''' Extended '''Descr''' field from 63 chars to 127 chars because some descriptions ended up being longer (only by a few chars, though, which is good). '''Problem''', though, in that the build process reported no errors; only MySQL Query Browser reported errors. WTF? Further testing needed, but no time now.		** '''2011-02-24''' Extended '''Descr''' field from 63 chars to 127 chars because some descriptions ended up being longer (only by a few chars, though, which is good). '''Problem''', though, in that the build process reported no errors; only MySQL Query Browser reported errors. WTF? Further testing needed, but no time now.
	==SQL==		==SQL==
	~~<section begin=sql />~~<mysql> DROP TABLE IF EXISTS `cat_items`;		<mysql> DROP TABLE IF EXISTS `cat_items`;
	CREATE TABLE `cat_items` (		CREATE TABLE `cat_items` (
	`ID` INT NOT NULL AUTO_INCREMENT,		`ID` INT NOT NULL AUTO_INCREMENT,
Line 56:		Line 56:
	)		)
	ENGINE = MYISAM;</mysql>		ENGINE = MYISAM;</mysql>
	~~<section end=sql />~~

← Older revision		Revision as of 01:50, 18 January 2016
Line 1:		Line 1:
	==About==		==About==
	* '''Purpose''': catalog items within a title, i.e. varieties in which the title is (or was ever) available		* '''Purpose''': catalog items within a title, i.e. varieties in which the title is (or was ever) available
	* '''Fields~~''':~~		==Fields==
	** '''ID_Supp''' ties the item directly back to its supplier. This should be set when new items are created during catalog building, and also when new "scratch" items are created during invoice entry.		* '''ID_Supp''' ties the item directly back to its supplier. This should be set when new items are created during catalog building, and also when new "scratch" items are created during invoice entry.
	** '''Descr''': Right now, this is just a holder for descriptions of directly-entered items. Eventually we may start storing the calculated description (from title + options) there. Until then, though, this field can be largely ignored.		* '''Descr''': Right now, this is just a holder for descriptions of directly-entered items. Eventually we may start storing the calculated description (from title + options) there. Until then, though, this field can be largely ignored.
	** '''CatSfx''' is basically whatever sets the item's catalog number apart from other items of the same title		* '''CatSfx''' is basically whatever sets the item's catalog number apart from other items of the same title
	** '''GrpCode''' and '''GrpDescr''' are for differentiating within an item type, e.g. a short-sleeved t-shirt which is available in both tie-dye and one or more solid colors. '''GrpCode''' becomes part of the catalog number for items in that group, and '''GrpDesc''' is the description for that group. When displaying items for a particular title, sorting should be GrpSort,GrpCode,ItOptSort. See [[VbzCart catalog building]] for more.		* '''GrpCode''' and '''GrpDescr''' are for differentiating within an item type, e.g. a short-sleeved t-shirt which is available in both tie-dye and one or more solid colors. '''GrpCode''' becomes part of the catalog number for items in that group, and '''GrpDesc''' is the description for that group. When displaying items for a particular title, sorting should be GrpSort,GrpCode,ItOptSort. See [[VbzCart catalog building]] for more.
	** '''isCurrent''': If FALSE, display of item status should not make any assumptions about the correctness of the "isInPrint" flag. Instead of "out of print", display "availability unknown"; instead of "can probably get more", display "was available when last checked".		* '''isCurrent''': If FALSE, display of item status should not make any assumptions about the correctness of the "isInPrint" flag. Instead of "out of print", display "availability unknown"; instead of "can probably get more", display "was available when last checked".
	*** '''TO DO''': How can we derive the date of the latest catalog showing this as available?		** '''TO DO''': How can we derive the date of the latest catalog showing this as available?
	** '''isPulled''': TRUE = the record is being retired, but isn't ready to be recycled yet. This gives us a chance to see if the pulling was done in error. The item will still show up in listings by title. Pulling typically happens when duplicate items are created by mistake. When trying to decide which of two identical records to pull, pull the higher-numbered one if there are no other considerations.		* '''isPulled''': TRUE = the record is being retired, but isn't ready to be recycled yet. This gives us a chance to see if the pulling was done in error. The item will still show up in listings by title. Pulling typically happens when duplicate items are created by mistake. When trying to decide which of two identical records to pull, pull the higher-numbered one if there are no other considerations.
	** '''isDumped''': TRUE = the record is ready to be recycled.		* '''isDumped''': TRUE = the record is ready to be recycled.
	** '''QtyIn_Stk''' is updated by the catalog building process; later on, we will want to have a finer-grained process for updating this so the entire catalog build doesn't have to be run every time an item moves in/out of stock		* '''QtyIn_Stk''' is updated by the catalog building process; later on, we will want to have a finer-grained process for updating this so the entire catalog build doesn't have to be run every time an item moves in/out of stock
	** '''cntCtgDup''' indicates which items were duplicates during the catalog building process. It may end up being unnecessary, but I put it in during debugging so I thought I should document it.		* '''cntCtgDup''' indicates which items were duplicates during the catalog building process. It may end up being unnecessary, but I put it in during debugging so I thought I should document it.
	* '''History~~''':~~		==History==
	** '''2009-04-10''' Tentatively eliminating qtyInStock field (adding " OLD" to name) because (a) can't find any code that uses it, and (b) it really should be calculated dynamically, otherwise it's not reliable.		* '''2009-04-10''' Tentatively eliminating qtyInStock field (adding " OLD" to name) because (a) can't find any code that uses it, and (b) it really should be calculated dynamically, otherwise it's not reliable.
	** '''2009-11-19''' Adding "isCurrent" field; removing "qtyInStock OLD"		* '''2009-11-19''' Adding "isCurrent" field; removing "qtyInStock OLD"
	** '''2009-12-11''' Added "cntCtgDup"; restoring "QtyIn_Stk" field (formerly "qtyInStock") because:		* '''2009-12-11''' Added "cntCtgDup"; restoring "QtyIn_Stk" field (formerly "qtyInStock") because:
	*** it is used by existing code for displaying catalog pages		** it is used by existing code for displaying catalog pages
	*** it doesn't make sense to have isForSale (which is calculated from <u>(qtyInStock > 0) \|\| isInPrint</u>) and not qtyInStock; either isForSale should be eliminated, or qtyInStock should be restored		** it doesn't make sense to have isForSale (which is calculated from <u>(qtyInStock > 0) \|\| isInPrint</u>) and not qtyInStock; either isForSale should be eliminated, or qtyInStock should be restored
	*** when we start having frequent stock adjustments it may become inefficient to recalculate qtyInStock for every stock item every time there is a change, but by that time we can write code to optimize this procedure -- i.e. only adjust qtyInStock for items which have actually been moved in or out of stock.		** when we start having frequent stock adjustments it may become inefficient to recalculate qtyInStock for every stock item every time there is a change, but by that time we can write code to optimize this procedure -- i.e. only adjust qtyInStock for items which have actually been moved in or out of stock.
	** '''2010-12-28''' Added '''Descr''' field to allow adding item records directly from invoice data, without a title		* '''2010-12-28''' Added '''Descr''' field to allow adding item records directly from invoice data, without a title
	** '''2010-12-29''' Added '''ID_Supp''' field for much the same reason (need to be able to tie new Items to a Supplier without knowing Title or Department)		* '''2010-12-29''' Added '''ID_Supp''' field for much the same reason (need to be able to tie new Items to a Supplier without knowing Title or Department)
	** '''2011-02-24''' Extended '''Descr''' field from 63 chars to 127 chars because some descriptions ended up being longer (only by a few chars, though, which is good). '''Problem''', though, in that the build process reported no errors; only MySQL Query Browser reported errors. WTF? Further testing needed, but no time now.		* '''2011-02-24''' Extended '''Descr''' field from 63 chars to 127 chars because some descriptions ended up being longer (only by a few chars, though, which is good). '''Problem''', though, in that the build process reported no errors; only MySQL Query Browser reported errors. WTF? Further testing needed, but no time now.
			* '''2016-01-17''' Added '''Notes''' field because I needed to make notes on particular Items
	==SQL==		==SQL==
	<mysql> ~~DROP TABLE IF EXISTS `cat_items`;~~		<mysql>CREATE TABLE `cat_items` (
	CREATE TABLE `cat_items` (
	`ID` INT NOT NULL AUTO_INCREMENT,		`ID` INT NOT NULL AUTO_INCREMENT,
	`CatNum` VARCHAR(64) COMMENT "catalog number: must be unique or null; can be changed",		`CatNum` VARCHAR(64) COMMENT "catalog number: must be unique or null; can be changed",
Line 53:		Line 53:
	`QtyMin_Stk` INT COMMENT "minimum quantity to keep in stock",		`QtyMin_Stk` INT COMMENT "minimum quantity to keep in stock",
	`cntCtgDup` INT COMMENT "count of duplicates caused by catalog building",		`cntCtgDup` INT COMMENT "count of duplicates caused by catalog building",
			`Notes` TEXT COMMENT "human-entered notes for admin use only",
	PRIMARY KEY(`ID`)		PRIMARY KEY(`ID`)
	)		)
	ENGINE = MYISAM;</mysql>		ENGINE = MYISAM;</mysql>