Кросс-запрос (“шахматный” отчёт)

"Потыркавшись" немного с кросс-запросами в foxpro, я не разобрался в них, и предпочёл написать свой алгоритм. В представленном примере из 2-х столбцов (Kol и MagZametka), а также трёх вариантов записей('Магазин Огонек', 'Магазин Белочка', 'Магазин Стрелочка') генерируются 2х3=6 столбцов. Удобно для экспорта в Excel, просмотра в Reportе, и т.п. В "девятке" работает, в "шестёрке" ругается на ascan, в других не проверял... Вполне возможно, я заново "изобретаю велосипед", но красивого решения не знаю...
DO exmaple_make_chesstable

procedure exmaple_make_chesstable

local crossfields_to_multiple,cross_count_to_multiple

CREATE CURSOR _Before(Tovar_id n(5), Tovar_n c(40), Marka c(10), Magaz c(20), kol n(8), MagZametka c(40))
INSERT INTO _before VALUES (1, 'Шоколад', 'горьк.', 'Магазин Огонек', 5, 'скоро испортится')
INSERT INTO _before VALUES (1, 'Шоколад', 'горьк.', 'Магазин Белочка', 10, 'только привезли')
INSERT INTO _before VALUES (1, 'Шоколад', 'горьк.', 'Магазин Стрелочка', 3, 'быстро кончается')
INSERT INTO _before VALUES (3, 'Поросенок', 'пряничн.', 'Магазин Огонек', 2, 'срок хранения 3 дня')
INSERT INTO _before VALUES (3, 'Поросенок', 'пряничн.', 'Магазин Стрелочка', 70, 'надо ещё подвезти "девочек"')
INSERT INTO _before VALUES (2, 'Шоколад', 'сладк.', 'Магазин Огонек', 15, 'отложили на Новый год')
INSERT INTO _before VALUES (2, 'Шоколад', 'сладк.', 'Магазин Белочка', 4, '—')
INSERT INTO _before VALUES (2, 'Шоколад', 'сладк.', 'Магазин Стрелочка', 3, 'не пользуется спросом')
INSERT INTO _before VALUES (4, 'Сливочные колбаски', '—', 'Магазин Белочка', 4, 'очень калорийны')
INSERT INTO _before VALUES (5, 'Желе', 'Plaza', 'Магазин Белочка', 20, '—')
INSERT INTO _before VALUES (5, 'Желе', 'Plaza', 'Магазин Стрелочка', 22, 'Для руководства, (с коноплей)')

browse last nowait

cross_count_to_multiple=2 && Количество полей которые исчезнут, но раздробятся на несколько
dimension crossfields_to_multiple[cross_count_to_multiple,3]
crossfields_to_multiple[1,1]='Kol'
crossfields_to_multiple[1,2]='_kol'
crossfields_to_multiple[1,3]='0'
crossfields_to_multiple[2,1]='MagZametka'
crossfields_to_multiple[2,2]='_zam'
crossfields_to_multiple[2,3]='" "'

DO make_chesstable_from_duplicate_records WITH crossfields_to_multiple,'_Before','Magaz','Mag_','Tovar_id','_After'
* Параметры: crossfields_to_multiple - массив с названиями полей, которые сами исчезнут,
* но раздробятся на поля вида: Prefix_1_FieldA, Prefix_1_FieldB, ..., Prefix_2_FieldA, Prefix_2_FieldB, ...,
* ...., Prefix_N_FieldA, Prefix_N_FieldB, ... ;
* 1-й столбец - имя поля, которое исчезнет, но раздробится на несколько,
* 2-й столбец - постфикс("напоминалка" - из каких полей создавались столбцы),
* 3-й столбец - пустые значения, если нет данных,
*
* далее (здесь через точку с запятой): Исходная таблица ;
* поле, определяющее N возможных вариантов - от его distinct значений зависит количество FieldA, FieldB, ... FieldZ ;
* префикс результирующего поля ("напоминалка" - из значений какого поля создавались столбцы) ;
* уникальный индекс записи результирующей таблицы, долен быть обязательно ;
* алиас результирующей таблицы

browse last nowait
endproc

procedure make_chesstable_from_duplicate_records

LPARAMETERS crossfields_to_multiple,cross_sourcetable,crosscolumns_field,cross_prefix, ;
cross_uniqueindex,cross_lastoutput_alias

local n,m,p,i,j,k,l, ;
cross_strucfields,cross_cur_fieldnames,crossfields_list,crossfields_list_enum, ;
cross_output_alias,cross_ordercondition,cross_unique_alias

cross_unique_alias=SYS(2015)

free_alias(cross_unique_alias)
select distinct &cross_sourcetable..&crosscolumns_field from &cross_sourcetable ;
into cursor &cross_unique_alias

if alen(crossfields_to_multiple,1)*reccount(cross_unique_alias)>240
messagebox('Превышен лимит столбцов',16,'')
else
SELECT(cross_unique_alias)
n=0
scan
n=n+1
cross_cur_aliasname=cross_unique_alias+'_'+alltrim(str(n))
free_alias(cross_cur_aliasname)

select * from &cross_sourcetable ;
where &cross_sourcetable..&crosscolumns_field=&cross_unique_alias..&crosscolumns_field ;
into cursor &cross_cur_aliasname
endscan

p=0
dimension cross_strucfields[1]
m=afields(cross_strucfields,(cross_sourcetable))
for i=1 to m
if (ascan(crossfields_to_multiple,cross_strucfields[i,1],1,alen(crossfields_to_multiple,1),1,7)=0) and ;
(upper(crosscolumns_field)#upper(cross_strucfields[i,1]))
p=p+1
dimension crossfields_list[p]
crossfields_list[p]=cross_strucfields[i,1]
endif
next

dimension cross_cur_fieldnames[ALEN(crossfields_to_multiple,1)]
for i=1 to ALEN(crossfields_to_multiple,1)
cross_cur_fieldnames[i]=crossfields_to_multiple[i,1]
next

cross_cur_aliasname1=cross_unique_alias+'_1'
for i=2 to n
cross_cur_aliasname2=cross_unique_alias+'_'+alltrim(str(i))
crossfields_list_enum=''
for j=1 to p
crossfields_list_enum=crossfields_list_enum+ ;
'IIf(ISNULL('+cross_cur_aliasname1+'.'+crossfields_list[j]+'),'+ ;
+cross_cur_aliasname2+'.'+crossfields_list[j]+','+ ;
cross_cur_aliasname1+'.'+crossfields_list[j]+') as '+ ;
crossfields_list[j]+','
next

for k=1 to ALEN(crossfields_to_multiple,1)

for l=1 to n
if l>i
crossfields_list_enum=crossfields_list_enum+ ;
crossfields_to_multiple[k,3]+' as '+cross_prefix+alltrim(str(l))+crossfields_to_multiple[k,2]+','
endif
if l<(i-1)
crossfields_list_enum=crossfields_list_enum+ ;
'IIf(ISNULL('+cross_cur_aliasname1+'.'+cross_prefix+alltrim(str(l))+crossfields_to_multiple[k,2]+'),'+ ;
crossfields_to_multiple[k,3]+','+ ;
cross_cur_aliasname1+'.'+cross_prefix+alltrim(str(l))+crossfields_to_multiple[k,2]+ ;
') as '+cross_prefix+alltrim(str(l))+crossfields_to_multiple[k,2]+','
endif
next

crossfields_list_enum=crossfields_list_enum+ ;
'IIf(ISNULL('+cross_cur_aliasname1+'.'+cross_cur_fieldnames[k]+'),'+ ;
crossfields_to_multiple[k,3]+','+ ;
cross_cur_aliasname1+'.'+cross_cur_fieldnames[k]+ ;
') as '+cross_prefix+alltrim(str(i-1))+crossfields_to_multiple[k,2]+','+ ;
'IIf(ISNULL('+cross_cur_aliasname2+'.'+crossfields_to_multiple[k,1]+'),'+ ;
crossfields_to_multiple[k,3]+','+ ;
cross_cur_aliasname2+'.'+crossfields_to_multiple[k,1]+ ;
') as '+cross_prefix+alltrim(str(i))+crossfields_to_multiple[k,2]+','

cross_cur_fieldnames[k]=cross_prefix+alltrim(str(i))+crossfields_to_multiple[k,2]

next
crossfields_list_enum=left(crossfields_list_enum,len(crossfields_list_enum)-1)

cross_output_alias=cross_unique_alias+'_'+alltrim(str(i-2))+'_out'

free_alias(cross_output_alias)
select &crossfields_list_enum from &cross_cur_aliasname1 ;
full join &cross_cur_aliasname2 ;
on &cross_cur_aliasname1..&cross_uniqueindex=&cross_cur_aliasname2..&cross_uniqueindex ;
into cursor &cross_output_alias

free_alias(cross_cur_aliasname1)
free_alias(cross_cur_aliasname2)
cross_cur_aliasname1=cross_output_alias
next

cross_ordercondition='Order by '+cross_output_alias+'.'+cross_uniqueindex

select &cross_output_alias..* from &cross_output_alias ;
&cross_ordercondition ;
into cursor &cross_lastoutput_alias

free_alias(cross_output_alias)

endif

endproc

procedure free_alias
lparameter _alias

if used(_alias)
use in (_alias)
endif
endproc

Автор: Er0p

Автор публикации

не в сети 3 месяца

admin

Комментарии: 1Публикации: 123Регистрация: 10-12-2000
Материалы по теме
Оставить комментарий
//////////////// ///////////////
Авторизация
*
*
Генерация пароля