Создание базы данных “Игрушки”, страница 4

sqlvar = "SELECT t.name as Игрушка, t.weight as Вес,t.cost as Цена FROM toys as t left JOIN vendors as v ON v.id=t.vendor left JOIN sales ON sales.id=t.id"

a = THISFORM.vendor_combo.value

weight1=STR(THISFORM.weight1.value)

weight2=STR(THISFORM.weight2.value)

* getting sort criteria

IF ThisForm.sort_combo.Selected(1)

ord='prod_date'

endif

IF ThisForm.sort_combo.Selected(2)

ord='v.name'

endif

IF ThisForm.sort_combo.Selected(3)

ord='t.weight'

endif

IF ThisForm.sort_combo.Selected(4)

ord='t.cost'

endif

IF ThisForm.sort_combo.Selected(5)

ord='sale_date'

endif

if (a=="") then

else

b = VAL(a)

*messagebox (str(len(sqlvar+' WHERE t.weight>='+weight1+' AND t.weight<='+weight2+' AND t.vendor='+a+' ORDER BY '+ord)))

_VFP.DoCmd(sqlvar+' WHERE t.weight>='+weight1+' AND t.weight<='+weight2+' AND t.vendor='+a+' ORDER BY '+ord)

endif

В целом

sqlvar = "SELECT t.name as Игрушка,v.name as Поставщик,t.prod_date as Выпуск,t.cost as Цена FROM toys as t left JOIN vendors as v ON v.id=t.vendor left JOIN sales ON sales.id=t.id"

a = THISFORM.vendor_combo.value

weight1=STR(THISFORM.weight1.value)

weight2=STR(THISFORM.weight2.value)

* getting sort criteria

IF ThisForm.sort_combo.Selected(1)

ord='prod_date'

endif

IF ThisForm.sort_combo.Selected(2)

ord='v.name'

endif

IF ThisForm.sort_combo.Selected(3)

ord='t.weight'

endif

IF ThisForm.sort_combo.Selected(4)

ord='t.cost'

endif

IF ThisForm.sort_combo.Selected(5)

ord='sale_date'

endif

if (a=="") then

else

b = VAL(a)

_VFP.DoCmd(sqlvar+' WHERE t.weight>='+weight1+' AND t.weight<='+weight2+' ORDER BY '+ord)

endif

Найти долю игрушек, проданных за определенный период (ввод периода) от общего времени продажи

sqlvar='SELECT sum(sales.quantity) FROM sales '

a = THISFORM.days.value

b = str(val(sys(1))-a)

dimension a1(1),a2(1)

store '100' to a2(1)

store 0 to a1(1)

*messagebox (sqlvar+' WHERE val(sys(11,DTOC(sales.sale_date)))>'+b+' into array a1')

_VFP.DoCmd(sqlvar+' WHERE val(sys(11,DTOC(sales.sale_date)))>'+b+' into array a1')

select sum(sales.quantity) from sales into array a2

THISFORM.Label11.caption = STR(INT(a1(1)/int(a2(1))*100))+'%'

Найти самый популярный вид игрушки (продано наибольшее количество) для заданного возраста (ввод) и в целом

С учетом возраста

sqlvar = "SELECT t.name as Игрушка,v.name as Поставщик,t.cost as Цена,sum(sales.quantity) as Количетво FROM toys as t left JOIN vendors as v ON v.id=t.vendor left JOIN sales ON sales.id=t.id"

age = THISFORM.age.value

dimension a1(1),a2(1)

store 0 to a1(1)

select max(quantity) from sales LEFT JOIN toys on toys.id=sales.id where toys.age_low<=age AND toys.age_high>=age into array a1

*messagebox (str(len(sqlvar+' where sales.quantity='+str(a1(1)))))

_VFP.DoCmd(sqlvar+' where sales.quantity='+str(a1(1)))

В целом

sqlvar = "SELECT t.name as Игрушка,v.name as Поставщик,t.cost as Цена,sum(sales.quantity) as Количетво FROM toys as t left JOIN vendors as v ON v.id=t.vendor left JOIN sales ON sales.id=t.id"

*messagebox (str(len(sqlvar+' where sales.quantity=(select max(quantity) from sales)')))

_VFP.DoCmd(sqlvar+' where sales.quantity=(select max(quantity) from sales)')

Заключение

Была освоена  в достаточном объеме система управления базами данных Microsoft Visual FoxPro 6.0, получены навыки по созданию баз данных и приложений.