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

С учетом типа

sqlvar='SELECT max(toys.cost),min(toys.cost),avg(toys.cost) FROM  toys left JOIN vendors    left JOIN sales    ON  vendors.id = toys.vendor    ON  sales.id = toys.id  into array a1'

a = THISFORM.type_combo.value

if (a=="") then

else

b = VAL(a)

_VFP.DoCmd(sqlvar+' WHERE toys.type='+a)

_VFP.DoCmd('SELECT toys.name FROM  toys left JOIN vendors    left JOIN sales    ON  vendors.id = toys.vendor    ON  sales.id = toys.id where toys.cost='+STR(a1(1))+' into array a2' )

_VFP.DoCmd('SELECT toys.name FROM  toys left JOIN vendors    left JOIN sales    ON  vendors.id = toys.vendor    ON  sales.id = toys.id where toys.cost='+STR(a1(2))+' into array a3' )

_str = 'MAX - ' + alltrim(a2(1))+' ('+alltrim(str(a1(1)))+'p.)     MIN - '+alltrim(a3(1))+' ('+alltrim(str(a1(2)))+'p.)      AVERAGE - '+alltrim(str(a1(3)))+'p.'

wait window _str

endif

В целом

sqlvar='SELECT max(toys.cost),min(toys.cost),avg(toys.cost) FROM  toys left JOIN vendors    left JOIN sales    ON  vendors.id = toys.vendor    ON  sales.id = toys.id  into array a1'

_VFP.DoCmd(sqlvar)

_VFP.DoCmd('SELECT toys.name FROM  toys left JOIN vendors    left JOIN sales    ON  vendors.id = toys.vendor    ON  sales.id = toys.id where toys.cost='+STR(a1(1))+' into array a2' )

_VFP.DoCmd('SELECT toys.name FROM  toys left JOIN vendors    left JOIN sales    ON  vendors.id = toys.vendor    ON  sales.id = toys.id where toys.cost='+STR(a1(2))+' into array a3' )

_str = 'MAX - ' + alltrim(a2(1))+' ('+alltrim(str(a1(1)))+'p.)     MIN - '+alltrim(a3(1))+' ('+alltrim(str(a1(2)))+'p.)      AVERAGE - '+alltrim(str(a1(3)))+'p.'

wait window _str

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

С учнтом типа

sqlvar = "SELECT t.name as Игрушка,v.name 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.type_combo.value

price1 = STR(THISFORM.price1.value)

price2 = STR(THISFORM.price2.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.type='+a+' AND t.cost>='+price1+' AND t.cost<='+price2+' 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.type_combo.value

price1 = str(THISFORM.price1.value)

price2 = str(THISFORM.price2.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.cost>='+price1+' AND t.cost<='+price2+' ORDER BY '+ord)

endif

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