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

sqlvar = "SELECT t.name as Игрушка,y.name as Тип,t.prod_date as Выпуск,t.cost as Цена FROM toys as t left join types as y on y.id=t.type left JOIN vendors as v ON v.id=t.vendor left JOIN sales ON sales.id=t.id"

a = THISFORM.vendor_combo.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=="" or c=="") then

else

b = VAL(a)

_VFP.DoCmd(sqlvar+' WHERE t.vendor='+a+' ORDER BY '+ord)

endif

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

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

sqlvar='SELECT count(*) FROM  toys '

a = THISFORM.type_combo.value

age = STR(THISFORM.age.value)

price2 = STR(THISFORM.price2.value)

* getting sort criteria

IF ThisForm.sort_combo.Selected(1)

ord='toys.prod_date'

endif

IF ThisForm.sort_combo.Selected(2)

ord='vendors.name'

endif

IF ThisForm.sort_combo.Selected(3)

ord='toys.weight'

endif

IF ThisForm.sort_combo.Selected(4)

ord='toys.cost'

endif

IF ThisForm.sort_combo.Selected(5)

ord='sales.sale_date'

endif

if (a=="" or age=="") then

else

b = VAL(a)

* ? sqlvar+' WHERE toys.cost<='+price2+' INTO ARRAY a1'

_VFP.DoCmd(sqlvar+' WHERE toys.age_low<='+age+' AND toys.age_high<='+age+' AND toys.cost<='+price2+' INTO ARRAY a1')

_VFP.DoCmd(sqlvar+' INTO ARRAY a2')

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

endif

В целом

sqlvar='SELECT count(*) FROM  toys '

a = THISFORM.type_combo.value

price1 = str(THISFORM.price1.value)

price2 = str(THISFORM.price2.value)

* getting sort criteria

IF ThisForm.sort_combo.Selected(1)

ord='toys.prod_date'

endif

IF ThisForm.sort_combo.Selected(2)

ord='vendors.name'

endif

IF ThisForm.sort_combo.Selected(3)

ord='toys.weight'

endif

IF ThisForm.sort_combo.Selected(4)

ord='toys.cost'

endif

IF ThisForm.sort_combo.Selected(5)

ord='sales.sale_date'

endif

if (a=="") then

else

b = VAL(a)

* ? sqlvar+' WHERE toys.cost<='+price2+' INTO ARRAY a1'

_VFP.DoCmd(sqlvar+' WHERE toys.cost<='+price2+' INTO ARRAY a1')

_VFP.DoCmd(sqlvar+' INTO ARRAY a2')

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

endif

Найти все игрушки с заданной датой выпуска (ввод даты)

sqlvar = "SELECT t.name as Игрушка, t.prod_date as Выпуск,t.weight as Вес,t.cost as Цена,sales.sale_date 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.datebox.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

b = DTOC(a)

*messagebox (str(len(sqlvar+' WHERE prod_date=CTOD("'+b+'") ORDER BY '+ord)))

_VFP.DoCmd(sqlvar+' WHERE prod_date=CTOD("'+b+'") ORDER BY '+ord)

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

С учетом производителя