Code: Select all
put "CREATE TABLE inventory(itemID INTEGER PRIMARY KEY AUTOINCREMENT,itemDistributor TEXT,itemBarcode TEXT NOT NULL UNIQUE,itemQuantity TEXT
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
Code: Select all
put "CREATE TABLE inventory(itemID INTEGER PRIMARY KEY AUTOINCREMENT,itemDistributor TEXT,itemBarcode TEXT NOT NULL UNIQUE,itemQuantity TEXT
Code: Select all
## This is a table for keeping sales receipts
put "CREATE TABLE salesReceipts(receiptID INTEGER PRIMARY KEY AUTOINCREMENT,itemSerialNum TEXT,itemDescription TEXT,itemBarcode TEXT,CustID TEXT,repairRecptNum TEXT,"&\
"totalCost TEXT,itemQuantity TEXT,itemCost TEXT)" into tMyFiguresStatement6
revExecuteSQL sDatabaseID,tMyFiguresStatement6
if the result is not an integer then
answer "Error creating salesReceipts table"
else
answer information the result && "salesReceipts table created"
end if
## This is a table for keeping repair receipts
put "CREATE TABLE repairReceipts(receiptID INTEGER PRIMARY KEY AUTOINCREMENT,itemSerialNum TEXT,itemDescription TEXT,itemBarcode TEXT,CustID TEXT,repairRecptNum TEXT"&\
",totalCost TEXT,itemQuantity TEXT,itemCost TEXT)" into tMyFiguresStatement6a
revExecuteSQL sDatabaseID,tMyFiguresStatement6a
if the result is not an integer then
answer "Error creating repairReceipts table"
else
answer information the result && "repairReceipts table created"
end if
## Create table inventory and tracking itemSerialNum
put "CREATE TABLE inventory(itemID INTEGER PRIMARY KEY AUTOINCREMENT,theCount TEXT,itemDistributor TEXT,itemBarcode TEXT NOT NULL UNIQUE,itemQuantity TEXT,itemDescription TEXT,"&\
"itemCost TEXT,itemPrice TEXT,taxableLocal TEXT,taxableState TEXT,taxableFed TEXT,itemManufacturer TEXT,Catagory1 TEXT,Catagory2 TEXT,Catagory3 TEXT,itemSerialNum TEXT,itemNotes TEXT,"&\
"localTaxAmpunt TEXT,stateTaxAmount TEXT,fedTaxAmount TEXT, FOREIGN KEY (theCount) REFERENCES repairReceipts(itemQuantity),FOREIGN KEY (theCount) REFERENCES salesReceipts(itemQuantity))" into tMyFiguresStatement3
revExecuteSQL sDatabaseID,tMyFiguresStatement3
if the result is not an integer then
answer "Error creating inventory table" && the result
else
answer information the result && "inventory table created"
end if
Code: Select all
########################
## This is a header table for keeping track of sales receipts. The receiptNumber is defined in the app.
put "CREATE TABLE salesReceiptsHeader(headerID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,receiptNum TEXT NOT NULL UNIQUE,CustID TEXT,transactionDate Date,"&\
"PaymentMethod TEXT,salesPerson TEXT,"&\
"FOREIGN KEY (CustID) REFERENCES Customers(CustID),"&\
"FOREIGN KEY (salesPerson) REFERENCES Employees(employeeID))" into tSalesHeaderStatement
## This is a table for keeping sales receipt details. ReceiptNum is the same as the Header table.
put "CREATE TABLE salesReceiptsDetails(receiptID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,receiptNum TEXT,lineNumber TEXT,itemSerialNum TEXT,itemBarcode INTEGER,"&\
"itemQuantitySold TEXT,itemPrice TEXT,localTaxPaid TEXT,stateTaxPaid TEXT,fedTaxPaid TEXT,"&\ -- into tSalesReceiptsStatement
"FOREIGN KEY (itemBarcode) REFERENCES purchasedItems(itemBarcode))" into tSalesReceiptsStatement
########################
########################
## Create table items and tracking purchased items
put "CREATE TABLE purchasedItems(itemBarcode INTEGER PRIMARY KEY NOT NULL UNIQUE,distID TEXT,itemDescription TEXT,itemCost TEXT,itemPrice TEXT,taxableLocal TEXT,"&\
"taxableState TEXT,taxableFed TEXT,itemManufacturer TEXT,Catagory1 TEXT,Catagory2 TEXT,Catagory3 TEXT,itemSerialNum TEXT,itemNotes TEXT,"&\
"FOREIGN KEY (distID) REFERENCES Distributors(distID))" into tPurchasedItemsStatement
## Create table inventory itemBarcode are inserted when purchasedItems are inserted
put "CREATE TABLE inventory(itemBarcode INTEGER PRIMARY KEY NOT NULL UNIQUE,itemQuantity TEXT)" into tinventoryStatement
########################