Arjuna Aircraft Ident.: F-MBSD | Script de la base de données :
Code :
- CREATE DATABASE [bench] ON (NAME = N'bench_Data', FILENAME = N'E:\MagicLand\bench_Data.MDF' , SIZE = 1000, FILEGROWTH = 10%) LOG ON (NAME = N'bench_Log', FILENAME = N'F:\MagicLand\bench_Log.LDF' , SIZE = 176, FILEGROWTH = 10%)
- COLLATE French_CI_AS
- GO
- ALTER DATABASE [bench] ADD FILEGROUP [SECONDARY]
- GO
- ALTER DATABASE [bench] ADD FILE(NAME = N'bench_index', FILENAME = N'G:\MagicLand\bench_index_Data.NDF' , SIZE = 1000, FILEGROWTH = 10%) TO FILEGROUP [SECONDARY]
- GO
- use [bench]
- GO
- CREATE TABLE [dbo].[address] (
- [user_id] [numeric](18, 0) NOT NULL ,
- [type] [char] (3) COLLATE French_CI_AS NOT NULL ,
- [name] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
- [street] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
- [city] [numeric](18, 0) NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[award] (
- [total_order] [decimal](18, 0) NOT NULL ,
- [rate] [decimal](18, 0) NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[bank_account] (
- [warehouse_id] [numeric](18, 0) NOT NULL ,
- [bank_amount] [numeric](18, 0) NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[city] (
- [id] [numeric](18, 0) NOT NULL ,
- [name] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
- [country_id] [numeric](18, 0) NOT NULL ,
- [zipcode] [nvarchar] (50) COLLATE French_CI_AS NOT NULL ,
- [warehouse_id] [numeric](18, 0) NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[country] (
- [id] [numeric](18, 0) NOT NULL ,
- [name] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
- [currency_id] [numeric](18, 0) NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[currency] (
- [id] [numeric](18, 0) NOT NULL ,
- [name] [varchar] (50) COLLATE French_CI_AS NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[currencyrate] (
- [currency1_id] [numeric](18, 0) NOT NULL ,
- [currency2_id] [numeric](18, 0) NOT NULL ,
- [rate] [decimal](18, 0) NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[event] (
- [id] [numeric](18, 0) NOT NULL ,
- [user_id] [numeric](18, 0) NULL ,
- [type] [char] (3) COLLATE French_CI_AS NOT NULL ,
- [status] [char] (1) COLLATE French_CI_AS NOT NULL ,
- [vendor_id] [numeric](18, 0) NULL ,
- [warehouse_id] [numeric](18, 0) NULL ,
- [supplier_id] [numeric](18, 0) NULL ,
- [date_event] [int] NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[event_line] (
- [event_id] [numeric](18, 0) NOT NULL ,
- [id] [numeric](18, 0) NOT NULL ,
- [product_id] [numeric](18, 0) NOT NULL ,
- [quantity] [numeric](18, 0) NOT NULL ,
- [date_event] [int] NOT NULL ,
- [status] [char] (1) COLLATE French_CI_AS NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[internalmove] (
- [warehouse1_id] [numeric](18, 0) NOT NULL ,
- [warehouse2_id] [numeric](18, 0) NOT NULL ,
- [weight] [numeric](18, 0) NOT NULL ,
- [price] [decimal](18, 0) NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[price] (
- [country_id] [numeric](18, 0) NOT NULL ,
- [product_id] [numeric](18, 0) NOT NULL ,
- [amount] [decimal](18, 0) NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[product] (
- [id] [numeric](18, 0) NOT NULL ,
- [code] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
- [name] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
- [weight] [numeric](18, 0) NOT NULL ,
- [active] [char] (1) COLLATE French_CI_AS NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[productreplacement] (
- [product1_id] [numeric](18, 0) NOT NULL ,
- [product2_id] [numeric](18, 0) NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[stock] (
- [warehouse_id] [numeric](18, 0) NOT NULL ,
- [product_id] [numeric](18, 0) NOT NULL ,
- [stock] [numeric](18, 0) NOT NULL ,
- [reserved] [numeric](18, 0) NOT NULL ,
- [qtysupply] [numeric](18, 0) NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[suppliement] (
- [product_id] [numeric](18, 0) NOT NULL ,
- [supplier_id] [numeric](18, 0) NOT NULL ,
- [warehouse_id] [numeric](18, 0) NOT NULL ,
- [price] [decimal](18, 0) NOT NULL ,
- [delay] [numeric](18, 0) NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[supplier] (
- [id] [numeric](18, 0) NOT NULL ,
- [name] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
- [warehouse_id] [numeric](18, 0) NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[users] (
- [id] [numeric](18, 0) NOT NULL ,
- [vat_type] [numeric](18, 0) NOT NULL ,
- [firstname] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
- [lastname] [varchar] (50) COLLATE French_CI_AS NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[vat] (
- [country_id] [numeric](18, 0) NOT NULL ,
- [vat_id] [numeric](18, 0) NOT NULL ,
- [rate] [decimal](18, 0) NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[vendor] (
- [id] [numeric](18, 0) NOT NULL ,
- [name] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
- [warehouse_id] [numeric](18, 0) NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[vendor_award] (
- [vendor_id] [numeric](18, 0) NOT NULL ,
- [award_date] [int] NOT NULL ,
- [amount] [int] NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[warehouse] (
- [id] [numeric](18, 0) NOT NULL ,
- [country_id] [numeric](18, 0) NOT NULL
- ) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[address] WITH NOCHECK ADD
- CONSTRAINT [PK_address] PRIMARY KEY CLUSTERED
- (
- [user_id],
- [type]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[award] WITH NOCHECK ADD
- CONSTRAINT [PK_award] PRIMARY KEY CLUSTERED
- (
- [total_order]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[bank_account] WITH NOCHECK ADD
- CONSTRAINT [PK_bank_account] PRIMARY KEY CLUSTERED
- (
- [warehouse_id]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[city] WITH NOCHECK ADD
- CONSTRAINT [PK_city] PRIMARY KEY CLUSTERED
- (
- [id]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[country] WITH NOCHECK ADD
- CONSTRAINT [PK_country] PRIMARY KEY CLUSTERED
- (
- [id]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[currency] WITH NOCHECK ADD
- CONSTRAINT [PK_currency] PRIMARY KEY CLUSTERED
- (
- [id]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[currencyrate] WITH NOCHECK ADD
- CONSTRAINT [PK_currencyrate] PRIMARY KEY CLUSTERED
- (
- [currency1_id],
- [currency2_id]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[event] WITH NOCHECK ADD
- CONSTRAINT [PK_event] PRIMARY KEY CLUSTERED
- (
- [id]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[event_line] WITH NOCHECK ADD
- CONSTRAINT [PK_event_line] PRIMARY KEY CLUSTERED
- (
- [event_id],
- [id]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[internalmove] WITH NOCHECK ADD
- CONSTRAINT [PK_internalmove] PRIMARY KEY CLUSTERED
- (
- [warehouse1_id],
- [warehouse2_id],
- [weight]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[price] WITH NOCHECK ADD
- CONSTRAINT [PK_price] PRIMARY KEY CLUSTERED
- (
- [country_id],
- [product_id]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[product] WITH NOCHECK ADD
- CONSTRAINT [PK_product] PRIMARY KEY CLUSTERED
- (
- [id]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[productreplacement] WITH NOCHECK ADD
- CONSTRAINT [PK_productreplacement] PRIMARY KEY CLUSTERED
- (
- [product1_id]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[stock] WITH NOCHECK ADD
- CONSTRAINT [PK_stock] PRIMARY KEY CLUSTERED
- (
- [warehouse_id],
- [product_id]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[suppliement] WITH NOCHECK ADD
- CONSTRAINT [PK_suppliement] PRIMARY KEY CLUSTERED
- (
- [product_id],
- [supplier_id]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[supplier] WITH NOCHECK ADD
- CONSTRAINT [PK_supplier] PRIMARY KEY CLUSTERED
- (
- [id]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[users] WITH NOCHECK ADD
- CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
- (
- [id]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[vat] WITH NOCHECK ADD
- CONSTRAINT [PK_vat] PRIMARY KEY CLUSTERED
- (
- [vat_id],
- [country_id]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[vendor] WITH NOCHECK ADD
- CONSTRAINT [PK_vendor] PRIMARY KEY CLUSTERED
- (
- [id]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[vendor_award] WITH NOCHECK ADD
- CONSTRAINT [PK_vendor_award] PRIMARY KEY CLUSTERED
- (
- [vendor_id],
- [award_date]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[warehouse] WITH NOCHECK ADD
- CONSTRAINT [PK_warehouse] PRIMARY KEY CLUSTERED
- (
- [id]
- ) ON [PRIMARY]
- GO
- CREATE INDEX [ix_address_user] ON [dbo].[address]([user_id], [type]) ON [SECONDARY]
- GO
- CREATE INDEX [ix_event_type_status_user] ON [dbo].[event]([type], [status], [user_id] DESC ) ON [SECONDARY]
- GO
- CREATE INDEX [ix_event_type_status_vendor] ON [dbo].[event]([type], [status], [vendor_id] DESC ) ON [SECONDARY]
- GO
- CREATE INDEX [ix_event_type_status_warehouse] ON [dbo].[event]([type], [status], [warehouse_id] DESC ) ON [SECONDARY]
- GO
- CREATE INDEX [ix_event_type_status_supplier] ON [dbo].[event]([type], [status], [supplier_id] DESC ) ON [SECONDARY]
- GO
- CREATE INDEX [ix_event_date_status] ON [dbo].[event]([date_event] DESC , [status]) ON [SECONDARY]
- GO
- CREATE INDEX [ix_eventline_event_status] ON [dbo].[event_line]([event_id] DESC , [status]) ON [SECONDARY]
- GO
- CREATE INDEX [ix_product_active] ON [dbo].[product]([active]) ON [SECONDARY]
- GO
- CREATE INDEX [ix_suppliement_delay] ON [dbo].[suppliement]([delay]) ON [SECONDARY]
- GO
- CREATE INDEX [ix_suppliement_supply] ON [dbo].[suppliement]([warehouse_id], [product_id]) ON [SECONDARY]
- GO
- CREATE INDEX [ix_supplier_warehouse] ON [dbo].[supplier]([warehouse_id]) ON [SECONDARY]
- GO
- ALTER TABLE [dbo].[address] ADD
- CONSTRAINT [FK_address_city] FOREIGN KEY
- (
- [city]
- ) REFERENCES [dbo].[city] (
- [id]
- ),
- CONSTRAINT [FK_address_users] FOREIGN KEY
- (
- [user_id]
- ) REFERENCES [dbo].[users] (
- [id]
- )
- GO
- ALTER TABLE [dbo].[bank_account] ADD
- CONSTRAINT [FK_bank_account_warehouse] FOREIGN KEY
- (
- [warehouse_id]
- ) REFERENCES [dbo].[warehouse] (
- [id]
- )
- GO
- ALTER TABLE [dbo].[city] ADD
- CONSTRAINT [FK_city_country] FOREIGN KEY
- (
- [country_id]
- ) REFERENCES [dbo].[country] (
- [id]
- )
- GO
- ALTER TABLE [dbo].[country] ADD
- CONSTRAINT [FK_country_currency] FOREIGN KEY
- (
- [currency_id]
- ) REFERENCES [dbo].[currency] (
- [id]
- )
- GO
- ALTER TABLE [dbo].[currencyrate] ADD
- CONSTRAINT [FK_currencyrate_currency] FOREIGN KEY
- (
- [currency1_id]
- ) REFERENCES [dbo].[currency] (
- [id]
- ),
- CONSTRAINT [FK_currencyrate_currency1] FOREIGN KEY
- (
- [currency2_id]
- ) REFERENCES [dbo].[currency] (
- [id]
- )
- GO
- ALTER TABLE [dbo].[event] ADD
- CONSTRAINT [FK_event_supplier] FOREIGN KEY
- (
- [supplier_id]
- ) REFERENCES [dbo].[supplier] (
- [id]
- ),
- CONSTRAINT [FK_event_users] FOREIGN KEY
- (
- [user_id]
- ) REFERENCES [dbo].[users] (
- [id]
- ),
- CONSTRAINT [FK_event_vendor] FOREIGN KEY
- (
- [vendor_id]
- ) REFERENCES [dbo].[vendor] (
- [id]
- ),
- CONSTRAINT [FK_event_warehouse] FOREIGN KEY
- (
- [warehouse_id]
- ) REFERENCES [dbo].[warehouse] (
- [id]
- )
- GO
- ALTER TABLE [dbo].[event_line] ADD
- CONSTRAINT [FK_event_line_event] FOREIGN KEY
- (
- [event_id]
- ) REFERENCES [dbo].[event] (
- [id]
- ),
- CONSTRAINT [FK_event_line_product] FOREIGN KEY
- (
- [product_id]
- ) REFERENCES [dbo].[product] (
- [id]
- )
- GO
- ALTER TABLE [dbo].[internalmove] ADD
- CONSTRAINT [FK_internalmove_warehouse] FOREIGN KEY
- (
- [warehouse1_id]
- ) REFERENCES [dbo].[warehouse] (
- [id]
- ),
- CONSTRAINT [FK_internalmove_warehouse1] FOREIGN KEY
- (
- [warehouse2_id]
- ) REFERENCES [dbo].[warehouse] (
- [id]
- )
- GO
- ALTER TABLE [dbo].[price] ADD
- CONSTRAINT [FK_price_country] FOREIGN KEY
- (
- [country_id]
- ) REFERENCES [dbo].[country] (
- [id]
- ),
- CONSTRAINT [FK_price_product] FOREIGN KEY
- (
- [product_id]
- ) REFERENCES [dbo].[product] (
- [id]
- )
- GO
- ALTER TABLE [dbo].[productreplacement] ADD
- CONSTRAINT [FK_productreplacement_product] FOREIGN KEY
- (
- [product1_id]
- ) REFERENCES [dbo].[product] (
- [id]
- ),
- CONSTRAINT [FK_productreplacement_product2] FOREIGN KEY
- (
- [product2_id]
- ) REFERENCES [dbo].[product] (
- [id]
- )
- GO
- ALTER TABLE [dbo].[stock] ADD
- CONSTRAINT [FK_stock_product] FOREIGN KEY
- (
- [product_id]
- ) REFERENCES [dbo].[product] (
- [id]
- ),
- CONSTRAINT [FK_stock_warehouse] FOREIGN KEY
- (
- [warehouse_id]
- ) REFERENCES [dbo].[warehouse] (
- [id]
- )
- GO
- ALTER TABLE [dbo].[suppliement] ADD
- CONSTRAINT [FK_suppliement_product] FOREIGN KEY
- (
- [product_id]
- ) REFERENCES [dbo].[product] (
- [id]
- ),
- CONSTRAINT [FK_suppliement_supplier] FOREIGN KEY
- (
- [supplier_id]
- ) REFERENCES [dbo].[supplier] (
- [id]
- ),
- CONSTRAINT [FK_suppliement_warehouse] FOREIGN KEY
- (
- [warehouse_id]
- ) REFERENCES [dbo].[warehouse] (
- [id]
- )
- GO
- ALTER TABLE [dbo].[vat] ADD
- CONSTRAINT [FK_vat_country1] FOREIGN KEY
- (
- [country_id]
- ) REFERENCES [dbo].[country] (
- [id]
- )
- GO
- ALTER TABLE [dbo].[vendor] ADD
- CONSTRAINT [FK_vendor_warehouse] FOREIGN KEY
- (
- [warehouse_id]
- ) REFERENCES [dbo].[warehouse] (
- [id]
- )
- GO
- ALTER TABLE [dbo].[warehouse] ADD
- CONSTRAINT [FK_warehouse_country] FOREIGN KEY
- (
- [country_id]
- ) REFERENCES [dbo].[country] (
- [id]
- )
- GO
|
Script d'initialisation des données de la base :
Code :
- Option Explicit
- Dim cnxString
- Dim nbProducts
- Dim nbCountries
- Dim nbWarehousePerCountry
- Dim nbCitiesPerWarehouse
- Dim nbSuppliersPerWarehouse
- Dim nbCustomersPerCity
- Dim nbVendorPerWarehouse
- Dim nbVatPerCountry
- cnxString = "dsn=SQLBench"
- nbProducts = 10000
- nbCountries = 10
- nbWarehousePerCountry = 5
- nbCitiesPerWareHouse = 10
- nbSuppliersPerWarehouse = 3
- nbCustomersPerCity = 1000
- nbVendorPerWarehouse = 3
- nbVatPerCountry = 2
- Randomize
- Function CreateLog(name)
- Dim fso, fil
- Set fso = CreateObject("Scripting.FileSystemObject" )
- Set fil = fso.CreateTextFile(name)
- Set CreateLog = fil
- End Function
- Sub LogEntry(fil, str)
- fil.WriteLine str
- End Sub
- Sub CloseLog(fil)
- fil.Close
- Set fil = Nothing
- End Sub
- Dim logFile
- Set logFile = CreateLog("c:\create_database.log" )
- Call LogEntry(logFile, "Start on " & CStr(Now()))
- Dim cnx
- Set cnx = CreateObject("ADODB.Connection" )
- cnx.ConnectionTimeOut = 0
- cnx.CommandTimeOut = 0
- cnx.ConnectionString = cnxString
- cnx.Open
- Dim i, j, k, l
- Dim productWeight
- Dim productActive
- For i = 1 to nbProducts
- productWeight = Int(Rnd(10) * 20)
- If Rnd(10) > .1 Then productActive = "A" Else productActive = "I"
- cnx.Execute "insert into product (id, code, name, weight, active) values (" & CStr(i) & ", 'PCODE" & CStr(i) & "', 'Product " & CStr(i) & "', " & productWeight & ", '" & productActive & "')"
- Next
- For i = 1 to Int(nbProducts / 2)
- If Rnd(10) > .1 Then productActive = "A" Else productActive = "I"
- cnx.Execute "insert into productreplacement (product1_id, product2_id) values (" & CStr(i) & ", " & CStr(i + Int(nbProducts / 2)) & " )"
- Next
- Dim currencyRate
- Dim productPurchasePrice
- Dim productPurchaseDelay
- Dim productPrice
- Dim vatRate
- Dim vatType
- For i = 1 to nbCountries
- If i = 1 Then currencyRate = 1 Else currencyRate = Rnd(10)
- cnx.Execute "insert into currency (id, name) values (" & CStr(i) & ", 'Currency " & CStr(i) & "')"
- cnx.Execute "insert into country (id, name, currency_id) values (" & CStr(i) & ", 'Country " & CStr(i) & "', " & CStr(i) & " )"
- cnx.Execute "insert into currencyrate (currency1_id, currency2_id, rate) values (" & CStr(i) & ", 1, " & CStr(currencyRate) & " )"
- For j = 1 to nbVatPerCountry
- vatRate = Round(Rnd(9) / 4, 2)
- cnx.Execute "insert into vat (country_id, vat_id, rate) values (" & CStr(i) & ", " & CStr(j) & ", " & vatRate & " )"
- Next
- For j = 1 to nbProducts
- productPrice = Round(Rnd(9) * 300, 2) + 330
- cnx.Execute "insert into price (country_id, product_id, amount) values (" & CStr(i) & ", " & CStr(j) & ", " & productPrice & " )"
- Next
- For j = 1 to nbWarehousePerCountry
- cnx.Execute "insert into warehouse (id, country_id) values (" & CStr(((i - 1) * nbWarehousePerCountry) + j) & ", " & CStr(i) & " )"
- cnx.Execute "insert into bank_account (warehouse_id, bank_amount) values (" & CStr(((i - 1) * nbWarehousePerCountry) + j) & ", 0)"
- For k = 1 to nbProducts
- cnx.Execute "insert into stock (product_id, warehouse_id, stock, reserved, qtysupply) values (" & CStr(k) & ", " & CStr(((i - 1) * nbWarehousePerCountry) + j) & ", 0, 0, 0)"
- Next
- For k = 1 to nbVendorPerWarehouse
- cnx.Execute "insert into vendor (id, name, warehouse_id) values (" & CStr((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbVendorPerWarehouse) + k) & ", 'Vendor " & CStr((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbVendorPerWarehouse) + k) & "', " & CStr(((i - 1) * nbWarehousePerCountry) + j) & " )"
- Next
- For k = 1 to nbSuppliersPerWarehouse
- cnx.Execute "insert into supplier (id, name, warehouse_id) values (" & CStr((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbSuppliersPerWarehouse) + k) & ", 'Supplier " & CStr((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbSuppliersPerWarehouse) + k) & "', " & CStr(((i - 1) * nbWarehousePerCountry) + j) & " )"
- For l = 1 to nbProducts
- productPurchasePrice = Round(Rnd(9) * 300, 2)
- productPurchaseDelay = Int(Rnd(9) * 10) + 1
- cnx.Execute "insert into suppliement (product_id, supplier_id, warehouse_id, price, delay) values (" & CStr(l) & ", " & CStr((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbSuppliersPerWarehouse) + k) & ", " & CStr(((i - 1) * nbWarehousePerCountry) + j) & ", " & productPurchasePrice & ", " & productPurchaseDelay & " )"
- Next
- Next
- For k = 1 to nbCitiesPerWareHouse
- cnx.Execute "insert into city (id, name, country_id, zipcode, warehouse_id) values (" & CStr((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) & ", 'City " & CStr(k) & "', " & CStr(i) & ", 'Zip " & CStr(k) & "', " & CStr(((i - 1) * nbWarehousePerCountry) + j) & " )"
- For l = 1 to nbCustomersPerCity
- vatType = Int(Rnd(9) * nbVatPerCountry) + 1
- cnx.Execute "insert into users (id, firstname, lastname, vat_type) values (" & CStr(((((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) - 1) * nbCustomersPerCity) + l) & ", 'Mister', 'Customer " & CStr(((((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) - 1) * nbCustomersPerCity) + l) & "', " & CStr(vatType) & " )"
- cnx.Execute "insert into address (user_id, type, name, street, city) values (" & CStr(((((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) - 1) * nbCustomersPerCity) + l) & ", 'COM', 'Customer " & CStr(((((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) - 1) * nbCustomersPerCity) + l) & "', 'Street', " & CStr((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) & " )"
- cnx.Execute "insert into address (user_id, type, name, street, city) values (" & CStr(((((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) - 1) * nbCustomersPerCity) + l) & ", 'LIV', 'Customer " & CStr(((((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) - 1) * nbCustomersPerCity) + l) & "', 'Street', " & CStr((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) & " )"
- cnx.Execute "insert into address (user_id, type, name, street, city) values (" & CStr(((((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) - 1) * nbCustomersPerCity) + l) & ", 'FAC', 'Customer " & CStr(((((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) - 1) * nbCustomersPerCity) + l) & "', 'Street', " & CStr((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) & " )"
- Next
- Next
- Next
- Next
- Dim rate1, rate2, rate3
- For i = 1 to nbCountries * nbWarehousePerCountry
- For j = 1 to nbCountries * nbWarehousePerCountry
- rate1 = Round(Rnd(9) * 10, 2)
- rate2 = Round(Rnd(9) * 10, 2) + rate1
- rate3 = Round(Rnd(9) * 10, 2) + rate2
- cnx.Execute "insert into internalmove (warehouse1_id, warehouse2_id, weight, price) values (" & CStr(i) & ", " & CStr(j) & ", 5, " & CStr(rate1) & " )"
- cnx.Execute "insert into internalmove (warehouse1_id, warehouse2_id, weight, price) values (" & CStr(i) & ", " & CStr(j) & ", 10, " & CStr(rate2) & " )"
- cnx.Execute "insert into internalmove (warehouse1_id, warehouse2_id, weight, price) values (" & CStr(i) & ", " & CStr(j) & ", 15, " & CStr(rate3) & " )"
- Next
- Next
- cnx.Execute "insert into award (total_order, rate) values (10000, .01)"
- cnx.Execute "insert into award (total_order, rate) values (20000, .015)"
- cnx.Execute "insert into award (total_order, rate) values (50000, .02)"
- cnx.Execute "insert into award (total_order, rate) values (100000, .05)"
- cnx.Close
- Set cnx = Nothing
- Call LogEntry(logFile, "End on " & CStr(Now()))
- Call CloseLog(logFile)
- MsgBox("Databases are ready to bench!" )
|
Script du bench :
Code :
- Option Explicit
- Randomize
- ' Manque : dans la gestion des claim, avant le produit de remplacement, chercher si un InternalMove est possible !
- Dim nbDay
- nbDay = 360
- Dim cnxString
- Dim nbOrders
- Dim nbProductsPerOrder
- cnxString = "dsn=SQLBench;UID=sa;PWD=pdta"
- nbOrders = 100
- nbProductsPerOrder = 20
- Function CreateLog(name)
- Dim fso, fil
- Set fso = CreateObject("Scripting.FileSystemObject" )
- Set fil = fso.CreateTextFile(name)
- Set CreateLog = fil
- End Function
- Sub LogEntry(fil, str)
- fil.WriteLine str
- End Sub
- Sub CloseLog(fil)
- fil.Close
- Set fil = Nothing
- End Sub
- Dim logFile
- Set logFile = CreateLog("c:\bench.log" )
- Call LogEntry(logFile, "Start on " & CStr(Now()))
- Dim cnx
- Set cnx = CreateObject("ADODB.Connection" )
- cnx.ConnectionTimeOut = 0
- cnx.CommandTimeOut = 0
- cnx.ConnectionString = cnxString
- cnx.Open
- Dim nbCustomers
- Dim nbProducts
- Dim rs
- Set rs = CreateObject("ADODB.RecordSet" )
- Set rs.ActiveConnection = cnx
- rs.Open "select count(*) nbProducts from product"
- nbProducts = rs("nbProducts" )
- rs.Close
- rs.Open "select count(*) nbCustomers from users"
- nbCustomers = rs("nbCustomers" )
- rs.Close
- Dim dDay
- For dDay = 1 To nbDay
- Call LogEntry(logFile, "Start day " & CStr(dDay) & " " & CStr(Now()))
- Call LogEntry(logFile, "Start process claims" & " " & CStr(Now()))
- Call processClaims(dDay, cnx, rs)
- Call LogEntry(logFile, "End process claims" & " " & CStr(Now()))
- Call LogEntry(logFile, "Start make orders" & " " & CStr(Now()))
- Call makeOrders(dDay, cnx, rs)
- Call LogEntry(logFile, "End make orders" & " " & CStr(Now()))
- Call LogEntry(logFile, "Start control stock" & " " & CStr(Now()))
- Call controlStock(dDay, cnx, rs)
- Call LogEntry(logFile, "End control stock" & " " & CStr(Now()))
- Call LogEntry(logFile, "Start recieve stock" & " " & CStr(Now()))
- Call RecieveStock(dDay, cnx, rs)
- Call LogEntry(logFile, "End recieve stock" & " " & CStr(Now()))
- Call LogEntry(logFile, "Start reserve stock" & " " & CStr(Now()))
- Call ReserveStock(dDay, cnx, rs)
- Call LogEntry(logFile, "End reserve stock" & " " & CStr(Now()))
- Call LogEntry(logFile, "Start close orders" & " " & CStr(Now()))
- Call CloseOrder(dDay, cnx, rs)
- Call LogEntry(logFile, "End close orders" & " " & CStr(Now()))
- Call LogEntry(logFile, "End day " & CStr(dDay) & " " & CStr(Now()))
- Next
- Set rs = Nothing
- cnx.Close
- Set cnx = Nothing
- Call LogEntry(logFile, "End on " & CStr(Now()))
- Call CloseLog(logFile)
- MsgBox "Bench end"
- Sub makeOrders(dDay, cnx, rs)
- Dim i, j, k
- Dim user_id
- Dim vendor_id
- Dim event_id
- Dim event_line_id
- Dim product_id
- Dim quantity
- cnx.BeginTrans
- Dim rs2
- Set rs2 = CreateObject("ADODB.RecordSet" )
- Set rs2.ActiveConnection = cnx
- For i = 1 To nbOrders
- user_id = Int(Rnd(9) * nbCustomers) + 1
- rs.Open "select count(v.id) cpt from vendor v, city c, address a, users u where u.id = " & CStr(user_id) & " and a.user_id = u.id and a.type = 'LIV' and c.id = a.city and v.warehouse_id = c.warehouse_id"
- j = Int(Rnd(9) * rs("cpt" ))
- rs.Close
- rs2.Open "select v.id from vendor v, city c, address a, users u where u.id = " & CStr(user_id) & " and a.user_id = u.id and a.type = 'LIV' and c.id = a.city and v.warehouse_id = c.warehouse_id"
- rs2.Move j
- vendor_id = rs2("id" )
- rs2.Close
- rs.Open "select max(id) event_id from event"
- event_id = CInt("0" & rs("event_id" )) + 1
- rs.Close
- cnx.Execute "insert into event (id, user_id, type, status, date_event, vendor_id) values (" & event_id & ", " & CStr(user_id) & ", 'ORD', 'V', " & CStr(dDay) & ", " & CStr(vendor_id) & " )"
- For j = 1 to nbProductsPerOrder
- rs.Open "select count(id) cpt from product where active = 'A'"
- k = Int(Rnd(9) * rs("cpt" ))
- rs.Close
- rs2.Open "select id from product where active = 'A'"
- rs2.Move k
- product_id = rs2("id" )
- rs2.Close
- quantity = Int(Rnd(9) * 100) + 1
- rs.Open "select max(id) event_line_id from event_line where event_id = " & CStr(event_id)
- event_line_id = CInt("0" & rs("event_line_id" )) +1
- rs.Close
- cnx.Execute "insert into event_line (event_id, id, product_id, quantity, date_event, status) values (" & CStr(event_id) & ", " & CStr(event_line_id) & ", " & CStr(product_id) & ", " & CStr(quantity) & ", " & CStr(dDay) & ", 'V')"
- Next
- Next
- Set rs2 = Nothing
- cnx.CommitTrans
- End Sub
- Sub controlStock(dDay, cnx, rs)
- cnx.BeginTrans
- Dim rs2
- Set rs2 = CreateObject("ADODB.RecordSet" )
- Set rs2.ActiveConnection = cnx
- Dim rs3
- Set rs3 = CreateObject("ADODB.RecordSet" )
- Set rs3.ActiveConnection = cnx
- rs.Open "select (select top 1 supplier_id from suppliement su where su.warehouse_id = st.warehouse_id and su.product_id = el.product_id and (price = (select min(price) from suppliement su2 where su2.warehouse_id = su.warehouse_id and su2.product_id = su.product_id and su2.delay <= (select min(e.date_event) from city c, address a, event_line el, event e where e.type = 'ORD' and e.status = 'V' and el.event_id = e.id and el.status != 'R' and el.product_id = su2.product_id and a.user_id = e.user_id and a.type = 'LIV' and c.id = a.city and c.warehouse_id = su2.warehouse_id) + 10 and su2.max_supply >= (-1 * (st.stock - (sum(el.quantity) + st.reserved) + isNull((select sum(el2.quantity) from event_line el2, event e2 where e2.type = 'PUR' and e2.warehouse_id = st.warehouse_id and e2.date_event >= " & CStr(dDay) & " - 30 and el2.event_id = e2.id and el2.product_id = el.product_id), 0)) - st.qtysupply)) and su.max_supply >= (-1 * (st.stock - (sum(el.quantity) + st.reserved) + isNull((select sum(el2.quantity) from event_line el2, event e2 where e2.type = 'PUR' and e2.warehouse_id = st.warehouse_id and e2.date_event >= " & CStr(dDay) & " - 30 and el2.event_id = e2.id and el2.product_id = el.product_id), 0)) - st.qtysupply) or delay = (select min(delay) from suppliement su2 where su2.warehouse_id = su.warehouse_id and su2.product_id = su.product_id and su2.delay > (select min(e.date_event) from city c, address a, event_line el, event e where e.type = 'ORD' and e.status = 'V' and el.event_id = e.id and el.status != 'R' and el.product_id = su2.product_id and a.user_id = e.user_id and a.type = 'LIV' and c.id = a.city and c.warehouse_id = su2.warehouse_id) + 10 and su2.max_supply >= (-1 * (st.stock - (sum(el.quantity) + st.reserved) + isNull((select sum(el2.quantity) from event_line el2, event e2 where e2.type = 'PUR' and e2.warehouse_id = st.warehouse_id and e2.date_event >= " & CStr(dDay) & " - 30 and el2.event_id = e2.id and el2.product_id = el.product_id), 0)) - st.qtysupply) and not exists (select null from suppliement su3 where su3.warehouse_id = su2.warehouse_id and su3.product_id = su2.warehouse_id and su3.delay <= (select min(e.date_event) from city c, address a, event_line el, event e where e.type = 'ORD' and e.status = 'V' and el.event_id = e.id and el.status != 'R' and el.product_id = su3.product_id and a.user_id = e.user_id and a.type = 'LIV' and c.id = a.city and c.warehouse_id = su3.warehouse_id) + 10)) and su.max_supply >= (-1 * (st.stock - (sum(el.quantity) + st.reserved) + isNull((select sum(el2.quantity) from event_line el2, event e2 where e2.type = 'PUR' and e2.warehouse_id = st.warehouse_id and e2.date_event >= " & CStr(dDay) & " - 30 and el2.event_id = e2.id and el2.product_id = el.product_id), 0)) - st.qtysupply))) supplier_id, st.warehouse_id, el.product_id, -1 * (st.stock - (sum(el.quantity) + st.reserved) + isNull((select sum(el2.quantity) from event_line el2, event e2 where e2.type = 'PUR' and e2.status = 'V' and e2.warehouse_id = st.warehouse_id and el2.event_id = e2.id and el2.product_id = el.product_id), 0)) - st.qtysupply quantityPurchase from stock st, city ci, address a, event_line el, event e where e.status = 'V' and e.type = 'ORD' and el.event_id = e.id and el.status = 'V' and a.user_id = e.user_id and a.type = 'LIV' and ci.id = a.city and st.warehouse_id = ci.warehouse_id and st.product_id = el.product_id group by st.warehouse_id, el.product_id, st.reserved, st.stock, st.qtysupply having -1 * (st.stock - (sum(el.quantity) + st.reserved) + isNull((select sum(el2.quantity) from event_line el2, event e2 where e2.type = 'PUR' and e2.status = 'V' and e2.warehouse_id = st.warehouse_id and el2.event_id = e2.id and el2.product_id = el.product_id), 0)) - st.qtysupply > 0 order by supplier_id, st.warehouse_id", , 2
- Dim prevSupplier_id
- Dim event_id
- Dim event_line_id
- Dim remain
- prevSupplier_id = 0
- Do While Not rs.EOF
- If Not IsNull(rs("supplier_id" )) Then
- If prevSupplier_id <> CInt(rs("supplier_id" )) Then
- event_id = purchaseOrder(dDay, cnx, rs2, rs("supplier_id" ), rs("warehouse_id" ))
- event_line_id = 1
- prevSupplier_id = CInt(rs("supplier_id" ))
- End If
- Call purchaseOrderLine(dDay, cnx, event_id, event_line_id, rs("warehouse_id" ), rs("product_id" ), rs("quantitypurchase" ))
- event_line_id = event_line_id + 1
- Else
- ' Commandes spéciales pour les produits en rupture de stock : on commande à plusieurs fournisseurs, en privilégiant ceux qui offrent des délais le plus rapide possible
- remain = CLng(rs("quantityPurchase" ))
- rs3.Open "select su.supplier_id, su.max_supply - isNull((select sum(el2.quantity) from event_line el2, event e2 where e2.type = 'PUR' and e2.date_event > " & CStr(dDay) & " - 30 and e2.warehouse_id = su.warehouse_id and el2.event_id = e2.id and el2.product_id = su.product_id), 0) remain from suppliement su where su.warehouse_id = " & rs("warehouse_id" ) & " and su.product_id = " & rs("product_id" ) & " order by su.delay", , 2
- Do While Not rs3.EOF
- event_id = purchaseOrder(dDay, cnx, rs2, rs3("supplier_id" ), rs("warehouse_id" ))
- Call purchaseOrderLine(dDay, cnx, event_id, 1, rs("warehouse_id" ), rs("product_id" ), Min(remain, CLng(rs3("remain" ))))
- remain = remain - Min(remain, CLng(rs3("remain" )))
- If remain = 0 Then
- rs3.MoveLast
- End If
- rs3.MoveNext
- Loop
- rs3.Close
- End If
- rs.MoveNext
- Loop
- rs.Close
- Set rs2 = Nothing
- Set rs3 = Nothing
- cnx.CommitTrans
- End Sub
- Function purchaseOrder(dDay, cnx, rs, supplier_id, warehouse_id)
- Dim event_id
- rs.Open "select max(id) event_id from event", , 2
- event_id = CInt("0" & rs("event_id" )) + 1
- rs.Close
- cnx.Execute "insert into event (id, type, status, date_event, supplier_id, warehouse_id) values (" & CStr(event_id) & ", 'PUR', 'V', " & CStr(dDay) & ", " & CStr(supplier_id) & ", " & CStr(warehouse_id) & " )"
- purchaseOrder = event_id
- End Function
- Sub purchaseOrderLine(dDay, cnx, event_id, event_line_id, warehouse_id, product_id, quantityPurchase)
- cnx.Execute "insert into event_line (event_id, id, product_id, quantity, date_event, status) values (" & CStr(event_id) & ", " & CStr(event_line_id) & ", " & CStr(product_id) & ", " & CStr(quantityPurchase) & ", " & CStr(dDay) & ", 'V')"
- cnx.Execute "update stock set qtysupply = (qtysupply + " & CStr(quantityPurchase) & " ) * 1.1 / 2 where warehouse_id = " & CStr(warehouse_id) & " and product_id = " & CStr(product_id)
- End Sub
- Sub RecieveStock(dDay, cnx, rs)
- cnx.BeginTrans
- rs.Open "select e.id, e.warehouse_id, el.product_id, el.quantity from suppliement s, event_line el, event e where e.type = 'PUR' and e.status = 'V' and el.event_id = e.id and el.date_event - 1 > s.delay and s.supplier_id = e.supplier_id and s.product_id = el.product_id"
- Do While Not rs.EOF
- cnx.Execute "update stock set stock = " & CStr(rs("quantity" )) & " where warehouse_id = " & CStr(rs("warehouse_id" )) & " and product_id = " & CStr(rs("product_id" ))
- cnx.Execute "update event_line set status = 'C' where event_id = " & CStr(rs("id" ))
- cnx.Execute "update event set status = 'C' where id = " & CStr(rs("id" ))
- rs.MoveNext
- Loop
- rs.Close
- cnx.CommitTrans
- End Sub
- Sub ReserveStock(dDay, cnx, rs)
- cnx.BeginTrans
- Dim rs2
- Set rs2 = CreateObject("ADODB.RecordSet" )
- Set rs2.ActiveConnection = cnx
- rs.Open "select el.event_id, el.id, c.warehouse_id, el.product_id, el.quantity from city c, address a, users u, event_line el, event e where e.type = 'ORD' and e.status = 'V' and el.event_id = e.id and el.status = 'V' and u.id = e.user_id and a.user_id = u.id and a.type = 'LIV' and c.id = a.city order by e.date_event asc, el.quantity desc", , 2
- Do While Not rs.EOF
- rs2.Open "select stock - reserved available from stock where warehouse_id = " & CStr(rs("warehouse_id" )) & " and product_id = " & CStr(rs("product_id" )), , 0
- If CLng(rs2("available" )) > CLng(rs("quantity" )) Then
- cnx.Execute "update stock set reserved = reserved + " & CStr(rs("quantity" )) & " where warehouse_id = " & CStr(rs("warehouse_id" )) & " and product_id = " & CStr(rs("product_id" ))
- cnx.Execute "update event_line set status = 'R' where event_id = " & CStr(rs("event_id" )) & " and id = " & CStr(rs("id" ))
- End If
- rs2.Close
- rs.MoveNext
- Loop
- rs.Close
-
- Set rs2 = Nothing
- cnx.CommitTrans
- End Sub
- Sub CloseOrder(dDay, cnx, rs)
- cnx.BeginTrans
-
- rs.Open "select e.id from event e where not exists (select null from event_line el where el.event_id = e.id and el.status = 'V') and e.status = 'V' and e.type = 'ORD'"
- Do While Not rs.EOF
- cnx.Execute "update event_line set status = 'C' where event_id = " & CStr(rs("id" )) & " and status = 'R'"
- cnx.Execute "update event set status = 'P' where event_id = " & CStr(rs("id" )) & " and type = 'ORD'"
- rs.MoveNext
- Loop
- rs.Close
- cnx.Execute "update bank_account set bank_amount = bank_amount + tmp.amount from (select sum(p.amount / cr.rate) amount, c.warehouse_id from currencyrate cr, price p, country co, city c, address a, event_line el, event e where e.status = 'P' and e.type = 'ORD' and el.status = 'C' and el.event_id = e.id and a.user_id = e.user_id and a.type = 'LIV' and c.id = a.city and co.id = c.country_id and p.country_id = co.id and cr.currency1_id = co.currency_id and cr.currency2_id = 1 group by c.warehouse_id) tmp where bank_account.warehouse_id = tmp.warehouse_id"
- cnx.Execute "insert into vendor_award (vendor_id, award_date, amount) select tmp.vendor_id, " & CStr(dDay) & ", tmp.amount * a.rate from award a, (select sum(p.amount / cr.rate) amount, e.vendor_id from award aw, currencyrate cr, price p, country co, city c, address a, event_line el, event e where e.status = 'P' and e.type = 'ORD' and el.event_id = e.id and el.status = 'C' and a.user_id = e.user_id and a.type = 'LIV' and c.id = a.city and co.id = c.country_id and p.country_id = co.id and cr.currency1_id = co.currency_id and cr.currency2_id = 1 group by e.vendor_id) tmp where a.total_order >= tmp.amount and a.total_order = (select max(a2.total_order) from award a2 where a2.total_order >= tmp.amount)"
- cnx.Execute "update bank_account set bank_amount = bank_amount - tmp.amount from (Select Sum(va.amount) amount, v.warehouse_id from vendor_award va, vendor v where va.vendor_id = v.id group by v.warehouse_id) tmp where bank_account.warehouse_id = tmp.warehouse_id"
- cnx.Execute "update event set status = 'C' where status = 'P'"
- cnx.CommitTrans
- End Sub
- Sub ProcessClaims(dDay, cnx, rs)
- Dim event_id
- Dim killOrder
- event_id = 0
- killOrder = False
- cnx.BeginTrans
- Dim rs2
- Set rs2 = CreateObject("ADODB.RecordSet" )
- Set rs2.ActiveConnection = cnx
- rs.Open "select el.event_id, el.id, el.product_id, (select pr.product2_id from product p2, stock s, productreplacement pr where pr.product1_id = el.product_id and s.warehouse_id = c.warehouse_id and s.product_id = pr.product2_id and s.stock - s.reserved >= el.quantity and p2.id = pr.product2_id and p2.active = 'A') product2_id, el.quantity, c.warehouse_id from city c, address a, event_line el, event e where e.type = 'ORD' and e.status not in ('C', 'D') and e.date_event < 8 - 5 and el.event_id = e.id and el.status not in ('R', 'C', 'D') and a.user_id = e.user_id and a.type = 'LIV' and c.id = a.city order by el.event_id, el.id, product2_id", , 2
- Do While Not rs.EOF
- If Not IsNull(rs("product2_id" )) And Rnd(9) > .2 And (Not killOrder Or event_id <> CLng(rs("event_id" ))) Then
- killOrder = False
- cnx.Execute "update event_line set status = 'D' where event_id = " & CStr(rs("event_id" )) & " and id = " & CStr(rs("id" ))
- cnx.Execute "insert into event_line (event_id, id, product_id, quantity, date_event, status) values (" & CStr(rs("event_id" )) & ", " & CStr(rs("id" )) & ", " & CStr(rs("product2_id" )) & ", " & CStr(rs("quantity" )) & ", " & CStr(dDay) & ", 'R')"
- cnx.Execute "update stock set stock = stock - " & CStr(rs("quantity" )) & " where warehouse_id = " & CStr(rs("warehouse_id" )) & " and product_id = " & CStr(rs("product2_id" ))
- Else
- killOrder = True
- rs2.Open "select el.id, el.product_id, el.quantity from event_line el where el.event_id = " & CStr(rs("event_id" )) & " and el.status = 'R'", , 2
- Do While Not rs2.EOF
- cnx.Execute "update event_line set status = 'D' where event_id = " & CStr(rs("event_id" )) & " and id = " & CStr(rs2("id" ))
- cnx.Execute "update stock set reserved = reserved - " & CStr(rs2("quantity" )) & " where warehouse_id = " & CStr(rs("warehouse_id" )) & " and product_id = " & CStr(rs2("product_id" ))
- rs2.MoveNext
- Loop
- rs2.Close
- cnx.Execute "update event set status = 'D' where id = " & CStr(rs("event_id" ))
- End If
- event_id = CLng(rs("event_id" ))
- rs.MoveNext
- Loop
- Set rs2 = Nothing
- rs.Close
- cnx.CommitTrans
- End Sub
- Function Min(a, b)
- If a <= b Then
- Min = a
- Else
- Min = b
- End If
- End Function
|
Petit script SQL de contrôle/nettoyage de la base (listing de toutes les tables, et lots de reminse à 0 des données des deux scripts) :
Code :
- select 'products : ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from product
- union all
- select 'replacement products : ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from productreplacement
- union all
- select 'currencies ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from currency
- union all
- select 'countries ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from country
- union all
- select 'currencies rates : ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from currencyrate
- union all
- select 'vats : ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from vat
- union all
- select 'prices : ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from price
- union all
- select 'warehouses : ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from warehouse
- union all
- select 'bank accounts : ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from bank_account
- union all
- select 'stocks : ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from stock
- union all
- select 'vendors : ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from vendor
- union all
- select 'suppliers : ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from supplier
- union all
- select 'suppliements : ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from suppliement
- union all
- select 'cities : ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from city
- union all
- select 'users : ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from users
- union all
- select 'addresses : ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from address
- union all
- select 'internal moves : ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from internalmove
- union all
- select 'awards : ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from award
- union all
- select 'events : ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from event
- union all
- select 'events lines : ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from event_line
- union all
- select 'vendors awards : ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from vendor_award
- /*
- -- Run from here for "bench.vbs" data clean-up
- delete vendor_award
- delete event_line
- delete event
- update bank_account set bank_amount = 0
- update stock set stock = 0, reserved = 0, qtysupply = 0
- -- Stop here for "bench.vbs" data clean-up
-
- -- Run from here for "filldata.vbs" data clean-up (beware, fillind again database is very slow!)
- delete award
- delete internalmove
- delete address
- delete users
- delete city
- delete suppliement
- delete supplier
- delete vendor
- delete stock
- delete bank_account
- delete warehouse
- delete price
- delete vat
- delete currencyrate
- delete country
- delete currency
- delete productreplacement
- delete product
- -- Stop here for "filldata.vbs" data clean-up
- */
|
Message édité par Arjuna le 03-08-2005 à 01:01:31
|