Set Console Off
Set Exclusive Off
Set Cpdialog Off
SET TEXTMERGE on

Close Databases All

Public cDir, cSep1, cSep2, cAccountNo, nLength, cMsg, nResult, cBackupFile

cSep1 = [/]  && first delimiter in style/colour/size - default is a slash ie. "/"
cSep2 = [/]

TEXT to cMsg

Some of the inventry.dbf items created by Matrix earlier than build 415 where there were similar styles, such as ABC and ABC123 and ABCD would result in the inventry.accountno become barcodes rather than the expected style/colour/size.

This script rewrites the inventry.accountno to the correct style/colour/size format but only where those items are linked to a style via the Styleinv.dbf table.

Note: the Colour Separator is set to: <<cSep1>> and the Size Separator is set to: <<cSep2>>, so that the account code in Vision is created as:

	Style<<cSep1>>Colour<<cSep1>>Size

If this is now how you would like the Vision stock code to look like, then please cancel this script now and edit the cSep1 AND cSep2 fields to the required
separator character (e.g. / or -)

You should backup your database before continuing.

Would you like to continue?

ENDTEXT

nResult = Messagebox(cMsg,4,"Axsapt - Redo Style Stock Codes in Vision")

If nResult = 7  && No
	Return
Endif


cDir = Getdir([T:\vision9testcoy])

Cd [&cDir]

If Not File("styleinv.dbf") Or Not File("inventry.dbf")
	Messagebox(cDir,0,"Not a Vision/Matrix Database Directory")
	Return
Endif

cBackupFile = "inventry_" + Timestamp()

*messagebox(cBackupFile)

*return

Set Alternate To [VisionStockCodesTidyUp.log] Additive

Use styleinv In 0
Use inventry In 0

* first backup the inventry table
Select inventry
Copy To &cBackupFile

*use &cBackupFile
*browse
*return

* find out field size of accountno in inventry table
Select inventry
nLength = Fsize('accountno')

Select styleinv
Scan
	Select styleinv
	cUniqueId = UniqueId

	cAccountNo = GetSKU(styleinv.zStyle, styleinv.zColour, styleinv.zSize, StyleInv.UniqueId)

	Select inventry
	Locate For UniqueId = cUniqueId
	cOldAccountNo = AccountNo
	*logmsg(cAccountNo + "<-> Inventry.AccountNo: " + cOldAccountNo)

	If Found()
		If AccountNo != Trim(cAccountNo)
			*? accountno + " << c.f. >> " +  cAccountno

			* first ensure this code is unique, before we change to it
			Locate For AccountNo = cAccountNo
			If Found()
				* Can't do anything
				=logmsg([Stock Code: ] + cOldAccountNo + [ cannot be changed to: "]+AccountNo + [". as this Code is not unique.])
			Else
				* go back to the original uniqueid
				Locate For UniqueId = cUniqueId
				If Found()
					=logmsg( [ Updated...] + ["]+AccountNo + [" with "] +  cAccountNo + ["])
					Replace AccountNo With cAccountNo
				Else
					=logmsg("Internal Error: No longer can find UniqueId: " + cUniqueId)
				Endif
			Endif
		Endif
	Endif

Endscan

Set Alternate To

Run /N notepad VisionStockCodesTidyUp.Log

=logmsg("========================================================================================")
* Close the log file
Set Alternate To

*****************
Function GetSKU *
*****************
Parameters tcStyle, tcColour, tcSize, tcUniqueId

* RJP 27-Jun-02 - New Product contruction
* Now construct the product code: With Vision 2.5 there is a new barcode field so we can use the product code to store style/colour/size.
* There is a limit of 25 characters available in the Vision inventory code which we plan to populate with the Noble Matrix Style/Colour/Size codes. The style code is 16 characters, Colour is 20 and Size is 4 and with the 2 separators to make it easier to read it totals 42 characters.
* To accommodate this constriction, Noble Matrix will when the total of style/colour/size is greater than 16 characters:
* 1.	remove all blank spaces in styles, colours and sizes, and if the result is still greater than 16 characters,
* 2.	truncate the colour by the required surplus, and then if the resultant product code is not unique,
* 3.	truncate the colour a further 2 characters and append to the colour component the number 00 (up to 99 if necessary), and if the resulting product code is not unique,
* 4.	store the Style Code (16 char) plus "/" plus str(recno())

cAccountNo = ""

* now create a unique product code
Do Case
Case Alltrim(tcColour) = "~" And Alltrim(tcSize) = "~"
	cAccountNo = Alltrim(tcStyle)
Case Alltrim(tcColour) != "~" And Alltrim(tcSize) = "~"
	*jcAccountNo = Alltrim(tcStyle)+"/"+Alltrim(tcColour)
	cAccountNo = Alltrim(tcStyle) + cSep1 + Alltrim(tcColour)
Case Alltrim(tcColour) = "-" And Alltrim(tcSize) = "-"
	cAccountNo = Alltrim(tcStyle)
Case Alltrim(tcColour) != "-" And Alltrim(tcSize) = "-"
	cAccountNo = Alltrim(tcStyle)+ cSep1 +Alltrim(tcColour)
Otherwise
	cAccountNo = Alltrim(tcStyle)+ cSep1 +Alltrim(tcColour)+ cSep2 +Alltrim(tcSize)
Endcase

nAcctLen = Len(cAccountNo)

If nAcctLen > 25
	cAccountNo = Strtran(cAccountNo, ' ', '')  && remove all blanks and try again
Endif

nAcctLen = Len(cAccountNo)

If nAcctLen > 25
	cStyPart = Alltrim(Strtran(tcStyle,' ',''))
	cColPart = Alltrim(Strtran(tcColour,' ',''))
	cSizPart = Alltrim(Strtran(tcSize,' ',''))
	nStyPart = Len(cStyPart)
	nColPart = Len(cColPart)
	nSizPart = Len(cSizPart)

	Do Case
	Case (nAcctLen - 25) < nColPart
		cAccountNo = cStyPart + cSep1 + Left(cColPart,nColPart - (nAcctLen - 25)) + cSep2 + cSizPart
	Otherwise
		cAccountNo = Alltrim(Strtran(tcStyle,' ','')) + cSep1 + Right("000000000000000"+Alltrim(Str(Recno("inventry"),12,0)),24-nStyPart)
	Endcase
Endif

* now search Inventry table to ensure generated SKU is unique
Select inventry
LOCATE FOR accountno = cAccountno AND UniqueId != tcUniqueid
IF FOUND()  && can't use this accountno, generate a SKU with the recordno in it to guarantee uniqueness
	cAccountNo = Alltrim(Strtran(tcStyle,' ','')) + cSep1 
	nAccLen = LEN(cAccountNo)
	cAccountNo = cAccountNo + Right("000000000000000"+Alltrim(Str(Recno("inventry"),12,0)),25-nAccLen)
Endif
Return cAccountNo


Function logmsg
Parameters tnMsg
Set Alternate On
cMsg = Dtoc(Date())+ " " + Time() + " " + tnMsg
? cMsg
Set Alternate Off
Return

Function TrimStr
Parameter tStr

Do Case
Case Type("tStr") = 'N'
	cStr = Alltrim(Str(tStr))
Case Type("tstr") = 'C'
	cStr = Alltrim(tStr)
Otherwise
	Messagebox(Type("tstr"))
Endcase

Return cStr
Endfunc


Function Timestamp
Return TrimStr(Year(Date()))+Right("0"+TrimStr(Month(Date())),2)+Right("0"+TrimStr(Day(Date())),2) +"_"+Right("0"+TrimStr(Hour(Datetime())),2)+Right("0"+TrimStr(Minute(Datetime())),2)+Right("0"+TrimStr(Sec(Datetime())),2)
cFullBackupPath = cBackup_Path+"\"+cDateTimeStamp