скрыть

скрыть

  Форум  

Delphi FAQ - Часто задаваемые вопросы

| Базы данных | Графика и Игры | Интернет и Сети | Компоненты и Классы | Мультимедиа |
| ОС и Железо | Программа и Интерфейс | Рабочий стол | Синтаксис | Технологии | Файловая система |



Google  
 

SQL-менеджер Хортица



Автор: Рачек Е.А.

SQL-менеджер Хортица

Менеджер предназначен для управления данными при помощи SQL-запросов и операторов с упрощенным синтаксисом. С его помощью Вы сможете делать выборки из одной или нескольких таблиц, записывать результаты в новые таблицы или добавлять в существующие, легко и быстро создавать из выборки простые табличные отчеты, отчеты в виде «шахматок», сложные отчеты на основе результатов нескольких SQL-запросов и промежуточных расчетов, передавать их в MS Word, сохранять в текстовых файлах, и многое другое. Встроенный язык с синтаксисом Pascal позволит создавать вычисляемые поля, определять параметры для SQL-запросов, производить различные промежуточные расчеты. Дополнительно менеджер позволяет определять формы ввода, для занесения пользователем исходных данных. Разработанные и отлаженные процедуры (скрипты) можно в дальнейшем использовать под управлением менеджера или интегрировать в Delphi (C++Builder) проекты при помощи специального компонента. Для работы со скриптами менеджер использует многооконный текстовый редактор с удобной помощью (включающей примеры), подсветкой синтаксиса и рядом дополнительных возможностей. Менеджер работает с базами данных форматов Paradox и Dbase. Также его можно использовать с любыми другими базами данных через BDE (в т.ч. Access). Для его использования необходим компьютер с установленным BDE, версии не ниже 3.0 .

Работа с менеджером

Менеджер работает с процедурами, представляющими собой текстовые документы (скрипты). Для работы с документами в программу встроен специальный текстовый редактор, позволяющий одновременно работать с несколькими процедурами. Текущей всегда является одна процедура, окно которой в настоящий момент активно в редакторе. Создать новую процедуру, сохранить, переименовать, удалить, закрыть окно можно при помощи меню Файл. Для выполнения процедуры применяется пункт меню Выполнить (удобнее «горячей клавишей» F9). Открыть существующую процедуру можно при помощи меню Файл->Открыть или горячей клавишей ctrl+O (O латинское от слова Open). Для подсветки синтаксиса следует использовать Редактор ->Подсветка синтаксиса, или Ctrl+F1.

Простой скрипт

select *
from zsEmpl

Данный скрипт выводит на экран список сотрудников. Простым называется скрипт, состоящий из одного SQL-запроса и не содержащий макрокоманд. Если в скрипте имеются макрокоманды, то вывода на экран не происходит.

ShowGrid

select tn,f,DataRogd
from zsEmpl
@@ShowGrid

Если в запросе имеются дополнительные макрокоманды, то для вывода результатов запроса на экран необходимо использовать команду ShowGrid. Возможно использование нескольких таких команд в одном запросе.

Alias

@@Alias=dbDemos
select *
from clients
@@ShowGrid

Обычно SQL-запрос работает с таблицами, находящимися в текущей папке. Иногда возникает необходимость работать с таблицами, находящимися в другой папке. В специальной терминологии папки, содержащие таблицы, называются базами данных и имеют уникальное имя. Сделать текущей одну из определенных в системе баз данных можно при помощи команды Alias. Если на Вашем компьютере установлен один из продуктов Borland вместе с примерами, то приведенный скрипт выводит содержимое таблицы Clients.dbf.

Param

@@Param sumaMin:f=100
select tn,f,knu,vo.name as NameKnu,god,mes,suma
from zaSal sl
left join zsEmpl se
  on se.tn=sl.tn
left join zsVidOpl vo
  on vo.knu=sl.knu
where suma>:SumaMin
order by f
@@ShowGrid

Для определения параметров, передаваемых SQL-запросу, служит команда Param. Синтаксис команды следующий:

@@Param ParamName:ParamType=ParamValue
ParamName-имя параметра
ParamType-буква, определяющая тип параметра:
i-целое число (integer)
f-число с плавающей точкой (float)
d-дата (data)
a-строка (alpha)
ParamValue-значение, присваиваемое параметру.

Razdel
Команда предназначена для удобства навигации по программе. После команды следует указывать название раздела. По нажатию клавиши Alt+R на экране появляется список разделов, к началу любого из которых легко перейти посредством выбора.

SaveDsToFile, AppendDsToFile, Razdelitel

@@Param god:i=2002
@@Param mes:i=7
//Выбор начислений
select tn,sum(suma) as SumaNach,0 as sumaUd
from zaSal
where god=:god and mes=:mes
group by tn
@@SaveDsToFile c:\q1
//Выбор удержаний
select tn,sum(suma) as sumaUd
from zaUd
where god=:god and mes=:mes
group by tn
@@AppendDsToFile c:\q1
//Рассчет итогов
select tn,
       sum(SumaNach) as SumaNach,
       sum(SumaUd)   as SumaUd
from "c:\q1"
group by tn
@@SaveDsToFile c:\q2
//Рассчет суммы к выплате
select tn,sumaNach,sumaUd,
       (sumaNach-sumaUd) as kVypl,
//Здесь может произойти ошибка!
       0 as dolg 
from "c:\q2"
@@SaveDsToFile c:\q3
//Рассчет долга
update "c:\q3"
set dolg=0-kVypl,kVypl=null
where kVypl<0
@@Razdelitel
//Замена нулевых значений на "пусто"
update "c:\q3"
set dolg=null
where dolg=0
@@Razdelitel
select tn,f,SumaNach,SumaUd,kVypl,dolg
from "c:\q3" q3
left join zsEmpl se
  on se.tn=q3.tn
order by f
@@ShowGrid

Команда SaveDsToFile применяется для сохранения в файле формата Db результатов запроса. Команду AppendDsToFile следует использовать для добавления результатов запроса к существующему Db или Dbf файлу. Добавление записей происходит по именам полей. Порядок их следования не имеет значения. Если типы полей не совпадают, то происходит попытка автоматического преобразования типов. В обеих командах имя файла необходимо указывать без кавычек. Для хранения временных файлов лучше использовать специально отведенную папку, а не корневой каталог. Команда Razdelitel никаких действий не производит. Она предназначена для разделения двух последовательно идущих SQL-запросов. В данном примере, на основании массивов начислений и удержаний рассчитывается сумма к выплате для каждого сотрудника за месяц. В данном примере может произойти ошибка вычислений (см.комментарии). Это связано с тем, что при сложении значений двух полей в запросе, если одно из них не определено, то результат тоже не определен. Т.е., если у сотрудника не было удержаний (к примеру, он получает только детское пособие), то сумма к выплате будет не определена. Для исключения подобных ошибок удобнее использовать поля, вычисляемые при помощи встроенного языка, о котором речь пойдет далее.

SaveDsToTxt

select *
from zaSal
@@SaveDsToTxt c:\q1.txt

Команда аналогична команде SaveDsToFile, но сохраняет данные в текстовом файле с разделителями символом “]”. В первую строку выводятся наименования полей с разделителями. Команду можно использовать для передачи больших объемов данных в другие приложения (для небольших объемов удобнее использовать меню Утилиты-> Экспорт в Word).

EmptyTable

select *
from zasal
@@SaveDsToFile c:\q1
select *
from "c:\q1"
@@ShowGrid
@@EmptyTable c:\q1
select *
from "c:\q1"
@@ShowGrid

Команда предназначена для удаления всех данных из таблицы. Имя файла следует указывать без кавычек. Для успешного выполнения команды необходимо, чтобы таблица не была открыта другими приложениями, т.к. в ходе ее выполнения происходит открытие таблицы в монопольном режиме. Приведенный пример создает новую таблицу и передает в нее данные из архива начислений, выводит на экран полученную таблицу с данными, удаляет из нее данные и показывает на экране пустую таблицу.

Exit

Команда применяется для прекращения выполнения скрипта. Ее применяют для отладки.

Использование встроенного языка

В основу встроенного языка положен синтаксис языка Pascal. Встроенный язык используется для добавления к результатам SQL-запроса вычисляемых полей командой InsertField и для реализации различных вычислений. Строка, написанная на встроенном языке, должна начинаться с идентификатора “ps” и подсвечивается в редакторе зеленым цветом. Язык поддерживает ряд стандартных процедур и функций, а также специальные процедуры и функции, описание которых будет представлено ниже. Список специальных функций можно вызвать при работе в редакторе клавишей Ctrl+P.

ShowMessage

ps ShowMessage('Встроенные функции')

Эта функция предназначена для вывода в диалоговом окне передаваемого ей строкового параметра.

DefineGlobal, DefineTemp

ps DefineGlobal('s','string')
ps DefineTemp  ('i','integer')
ps i:=100
ps s:=IntToStr(i)
@@Razdelitel
ps showMessage(s)
@@Exit
ps s:=IntToStr(i)

Обе функции предназначены для декларирования переменных. В качестве параметров функции передаются имя переменной и ее тип. Оба параметра указываются в виде строки. Отличие функций заключается в том, что переменная, определенная первой функцией, действует во всем скрипте, а переменная, определенная второй функцией, действует только в непрерывном блоке встроенного языка (до появления SQL-запроса или макрокоманды). В приведенном примере, после команды Razdelitel, переменная I прекращает существовать. Если убрать команду Exit, то при выполнении следующей строки будет выдано сообщение об ошибке.

Работа с результатами запроса, RecNo, RecordCount, SumField

select tn,
       max(f) as f,
       sum(suma) as suma
from zaSal sl
left join zsEmpl se
  on se.tn=sl.tn
where god=2002 and Mes=7
group by tn
order by f
@@ShowGrid
ps showMessage('Запись № '+IntToStr(RecNo)+'  Фамилия- '+f)
ps showMessage('Всего записей-'+IntToStr(RecordCount)+ '  общая сумма '+FloatToStr(SumField('Suma')))

Если блок, написанный на встроенном языке, находится после SQL-запроса, то к полям текущей записи запроса можно обратиться по имени, как к переменной. В приведенном примере, после выполнения запроса на экран выводится его результат (ShowGrid). При просмотре результатов Вы можете сделать текущей любую запись. После выхода из режима просмотра на экран последовательно выводится два диалоговых окна. В первом указывается номер текущей записи запроса (ф-ция RecNo) и фамилия сотрудника (обращение по полю F). Во втором окне показывается общее количество записей (ф-ция RecordCount) и сумма всех записей по полю Suma (ф-ция SumField).

Alias, SetAlias

@@Alias=dbDemos
ps ShowMessage(Alias)
ps SetAlias('')
ps ShowMessage(Alias)

Первая функция возвращает имя текущей базы данных (см. описание команды Alias), вторая позволяет изменить текущую базу данных (аналогично команде Alias).

Param, SetParam

@@Param data:d=01/02/03
ps ShowMessage(param('data'))
ps SetParam('data','15/12/94','d')
ps ShowMessage(param('data'))

Первая функция возвращает строку со значением указанного параметра (см. команда Param). Вторая позволяет создать или изменить параметр. В качестве параметров (параметры ф-ции) ей передается имя параметра (для SQL-запроса), его значение в виде строки, и буква, определяющая его тип (аналогично команде Param).

IfF(ifN), ifS

ps showMessage(ifS(3=5,'истина','ложь'))
ps showMessage(FloatToStr(ifF(3=5,10,20)))

Функции возвращают первый параметр, в случае истинности условия, и второй параметр в противном случае. Для функции ifF(ifN) параметрами и результатом являются числа, а для функции ifS – строковые переменные.

CurrentDate, CurrentTime

ps DefineGlobal('dt','integer')
ps dt:=CurrentDate
ps DefineGlobal('tm','double')
ps tm:=CurrentTime

Функции возвращают текущие дату и время.

jDateToStr, jStrToDate

ps ShowMessage(jDateToStr(CurrentDate))
ps DefineGlobal('dt','integer')
ps dt:=jStrToDate('15/12/94')

Функции преобразовывают дату в строковое значение и строковое значение в дату.

Year, Month, Day

ps DefineGlobal('s','string')
ps s:='год-'+IntToStr(year(CurrentDate))
ps s:=s+' месяц-'+IntToStr(month(CurrentDate))
ps s:=s+' число-'+IntToStr(day(CurrentDate))
ps ShowMessage(s)

Функции выделяют год, месяц и число из даты, передаваемой в качестве параметра.

FirstDayMonth, LastDayMonth

ps DefineGlobal('y','integer')
ps DefineGlobal('m','integer')
ps y:=year(CurrentDate)
ps m:=month(CurrentDate)
ps DefineGlobal('s','string')
ps s:='Первый день месяца-'+jDateToStr(FirstDayMonth(y,m))
ps s:=s+' Последний день месяца-'+jDateToStr(LastDayMonth(y,m))
ps ShowMessage(s)

Функции получают в качестве параметров номер месяца и год и возвращают первый и последний дни месяца соответственно.

SpellMonth

ps DefineGlobal('s','string')
ps DefineGlobal('i','Integer')
ps s:=''
ps For i:=1 to 12 do s:=s+SpellMonth(i)+','
ps delete(s,Length(s),1)
ps ShowMessage(s)

Функция возвращает наименование месяца, номер которого передан в качестве параметра.

Val_i

ps DefineGlobal('i','integer')
ps i:=Val_i('12345')

Функция преобразует строку к целому числу.

jRound

ps ShowMessage(FloatToStr(jRound(pi,3)))

Функция округляет передаваемое в качестве параметра число до количества знаков после запятой, указанного во втором параметре.

Podstroka

ps DefineGlobal('s','string')
ps s:='Иван,Николай,Тимофей,Петр'
ps ShowMessage(podstroka(s,',',1)) 

Функция разбивает передаваемую ей в качестве первого параметра строку на подстроки и возвращает подстроку, номер которой указан в третьем параметре. Нумерация начинается с нуля. Разделителем подстрок является второй параметр.

CalcTax

ps DefineGlobal('suma','double')
ps DefineGlobal('tax','double')
ps Suma:=500
ps tax:=CalcTax(suma,1,false)
ps DefineGlobal('s','string')
ps s:='Подоходный налог с '+FloatToStr(suma)+' грн. '
ps s:=s+'составляет '+FloatToStr(tax)+' грн.'
ps ShowMessage(s)

Функция рассчитывает подоходный налог по действующей шкале и имеет следующий синтаксис: CalcTax(suma,knp,IsNotLgota), где Suma-сумма, с которой исчисляется подоходный налог Knp-количество необлагаемых минимумов, применяемых при расчете налога, для сотрудника, не имеющего льгот, необходимо установить 1. Для совместителя – 99. IsNotLgota-Истинное значение этого параметра приводит к расчету налога без учета льготы. Параметр необходимо применять в случае, если вновь принятый (или уволенный) работник проработал на предприятии менее 15 календарных дней (в соответствии с «Декретом о подоходном налоге»)

InsertField, SaveDslToFile

select tn,
       sum(suma) as suma,
       max(f) as f,
       max(i) as i,
       max(o) as o
from zaSal sl
left join zsEmpl se
  on sl.tn=se.tn
where god=2002 and mes=7
group by tn
order by f,i,o
@@InsertField Nn:i=RecNo
@@InsertField tax:F=jRound(CalcTax(suma,1,false),2)
@@InsertField tax2:F=jRound(ifF(suma<0,0,
;                                ifF(suma<50,suma/200,suma/100)),2)
@@InsertField Fio:a20=f+' '+copy(i,1,1)+'.'+copy(o,1,1)+'.'
@@SaveDslToFile c:\q1
select nn,tn,fio,suma,tax,tax2
from "c:\q1" 
@@ShowGrid

Команда InsertField предназначена для добавления вычисляемых на встроенном языке полей к результатам выполнения SQL-запроса. Результаты выполнения запроса, вместе с добавленными полями, могут быть сохранены в таблице командой SaveDslToFile. Работа с командой идентична работе с SaveDsToFile. Команда InsertField имеет следующий синтаксис:

InsertField FieldName:FieldType=formula, где:
FieldName-имя поля
FieldType-тип поля, определяемый следующими буквами:
I-целое число
F-Число с плавающей точкой
A-строковое поле. После буквы A следует указать размерность поля (например A25)
Formula-выражение для вычисления значения поля на встроенном языке. 
При необходимости переноса выражения на следующую строку, 
ее необходимо начинать с символа «;»

Построение табличных отчетов

MakeDsChees,CheesPrint,CheesShow, CheesTable,CheesHeaderTextAdd,InputDataMonth

//@@InputDataMonth
ps if param('Data1')='' then SetParam('data1','01/07/02','d')
ps DefineGlobal('god','integer')
ps DefineGlobal('mes','integer')
ps god:=year (jStrToDate(param('data1')))
ps mes:=month(jStrToDate(param('data1')))
ps SetParam('god',IntToStr(god),'i')
ps SetParam('mes',IntToStr(mes),'i')

select tn,
       sum(suma) as suma,
       max(f) as f,
       max(i) as i,
       max(o) as o
from zaSal sl
left join zsEmpl se
  on sl.tn=se.tn
where god=:god and mes=:mes
group by tn
order by f,i,o
@@InsertField Nn:i=RecNo
@@InsertField tax:F=jRound(CalcTax(suma,1,false),2)
@@InsertField Fio:a20=f+' '+copy(i,1,1)+'.'+copy(o,1,1)+'.'
@@SaveDslToFile c:\q1
select nn,tn,fio,suma,tax
from "c:\q1" 

@@MakeDsChees(
  nn,№,1,right
  tn,Табель- ный номер,2,center,,,0000
  fio,ФИО,5
  suma,Сумма,3,right,,,0.00,sum
  tax,Налог,3,right,,,0.00,sum)

ps CheesHeaderTextAdd('Расчет начислений и подоходного налога')
ps DefineGlobal('s','string')
ps s:='за '+SpellMonth(mes)+' '+IntToStr(god)+' года'
ps CheesHeaderTextAdd(s)

@@CheesShow
//@@CheesTable
//@@CheesPrint

Для построения табличных отчетов применяется команда MakeDsChes. Синтаксис команды следующий:

MakeDsChes(  
Field1,title1,width1,Alignment1,FontSize1,FontStyle1,format1,agr1
----------------------------------------------------------------
  
FieldN,titleN,widthN,AlignmentN,FontSizeN,FontStyleN,formatN,agrN
)


Field-Имя поля 
Title-Заголовок колонки
Width-Ширина колонки (см.)
Alignment-способ выравнивания колонки (left,right,center)
FontSize-размер шрифта
FontStyle-не испльзуется
Format-формат вывода для числовых полей (00,0.00,0.##)
Agr-агрегатная функция для колонки (используется только Sum)   

Для формирования заглавия отчета применяется функция встроенного языка CheesHeaderTextAdd. Передаваемый ей строковый параметр помещается в заглавие отчета. Допускается несколько вызовов данной функции для отчета, заглавие которого состоит из нескольких строк. Полученный отчет можно вывести на принтер командой CheesPrint, вывести на экран в режиме предварительного просмотра командой CheesShow, просмотреть на экране в виде таблицы командой CheesTable. Из последнего режима щелчком правой кнопки мышки можно экспортировать отчет в MS Word. (в примере отчет выводится на экран в режиме предварительного просмотра, остальные режимы закоментированы). Многие бухгалтерские отчеты делаются за некоторый промежуток времени: месяц, квартал, полугодие, год. Для возможности ввода пользователем периода охвата отчета существует команда InputDataMonth. Если поместить команду в начало отчета (в примере, удалить комментарий в первой строке и закомментировать команду вывода отчета), то на экране появится диалоговая форма, позволяющая указать период отчета и имеющая несколько управляющих кнопок. Кнопка «Применить» служит для формирования отчета, кнопки «Таблица» и «Печать» говорят сами за себя. Для вывода отчета в режиме предварительного просмотра необходимо воспользоваться правой кнопкой мышки. После нажатия кнопки «Применить» начинается выполнение скрипта. При этом ему передается два параметра: Data1 и Data2, соответствующие началу и окончанию указанного периода. Если в процедуре определены одноименные параметры, то они и будут действовать, независимо от выбора пользователя. Для исключения подобных ситуаций при отладке скрипта следует пользоваться функциями встроенного языка, как это показано в начале примера.

Построение шахматки

(отчета с заранее не известным числом столбцов)

MakeChees

@@Param god:i=2002
select f as vKrt,            //Положение ячейки по вертикали
       mes+100 as gKrt,      //Положение ячейки по горизонтали
       cast(tn as char(4))+'-'+f as vKrtText,    
                             //Надпись для строки 
       cast(tn as char(4)) as vKrtText2,    
                             //Надпись для строки 2       
       mes,
       suma 
from zaSal sl
left join zsEmpl se
  on sl.tn=se.tn
where god=:god 
@@InsertField gKrtText:a15=SpellMonth(round(mes))
                           //Заголовок столбца
@@SaveDslToFile c:\q1
@@MakeChees(
  c:\q1                             //Имя файла с данными 
  orientation=poLandscape           //Ориентация бумаги  
  DefaultAlignment=taRightJustify   //Способ выравнивания
  defaultNumericFormat=0.00         //Формат ячейки
  zeroColWidthFirst=5               //Ширина первой колонки (с текстом) на левом листе
  zeroColWidthNext=1                //Ширина первой колонки на остальных листах
  AutoSumRows=true                  //Добавить итоги по строкам
  AutoSumColumns=true               //Добавить итоги по столбцам 
  defaultColWidth=2                 //Ширина столбца
)
ps CheesHeaderTextAdd('Начисленная заработная плата за '+param('god')+' год')
@@CheesShow
//@@CheesPrint
//@@CheesTable

Отчет-шахматка состоит из строк и столбцов. Каждый столбец может иметь свой заголовок. Каждая строка (в первой колонке) может иметь свою надпись. Если ширины столбца недостаточно для размещения заголовка, то высота первой строки автоматически увеличивается, и надпись размещается в несколько строк. При разделении заголовка на строки, в первую очередь программа пытается разбить его по словам (анализ-- по пробелам). Если длина слова превышает ширину столбца, то слово разделяется на части. Аналогично размещается надпись строки. Таким образом, в отчете могут быть определены первые строка и столбец. Все остальные ячейки могут состоять только из цифр. Для формирования шахматки следует воспользоваться командой MakeChees, в которую в качестве первого параметра передается имя файла с подготовленными данными. Далее могут следовать дополнительные параметры (каждый с новой строки), определяющие свойства отчета. Полный список параметров, с описанием их назначения, приведен в конце статьи, а также доступен в помощи программы. Если размер отчета больше листа (как по высоте, так и по ширине), то программа автоматически размещает его на нескольких листах.

Каждая запись файла с данными определяет ячейку отчета. Структура файла:
GKrt-(горизонтальный критерий) относительный номер столбца
VKrt-(вертикальный критерий) относительный номер строки
Suma-сумма ячейки
GkrtText-заголовок столбца
VkrtText-подпись строки
VkrtText2-подпись строки дополнительных листов

Первые два поля однозначно определяют позицию ячейки. Они могут быть как числовыми, так и текстовыми. Однако следует иметь в виду, что при определении позиции ячейки содержимое этих полей приводится к текстовому виду, и по нему производится сортировка. Если несколько записей файла с данными имеют одинаковые значения полей GKrt и VKrt, то в отчете появится одна ячейка с суммой всех таковых записей. Поля GkrtText и VkrtText определяют заголовки строки и столбца. Учитывая, что в строке или столбце может быть заполнено несколько ячеек, то вышеуказанные поля приводят к избыточности и увеличивают размер файла с данными. Для экономии места на диске в файле данных эти поля можно не определять. Для определения заголовков следует создать два дополнительных файла со структурой: GKrt и Text, и VKrt и Text. Эти файлы должны определять надписи строк и столбцов отчета и не несут избыточной информации. Имена этих файлов записываются в первой строке параметров команды MakeChees после указания файла с данными через запятую. Поле VkrtText2 будет использовано программой в том случае, если отчет будет размещаться по ширине на нескольких листах. При этом ширину колонки подписей строк можно задать отдельно для крайнего левого листа и остальных листов. В этом случае на левом листе будет выводиться текст, определенный полем VkrtTex, а на остальных листах –текст, определенный VkrtText1. Например, на левом листе в первой колонке показывается табельный номер сотрудника и его ФИО (ширина колонки 5 см), а на остальных листах только табельный номер (ширина колонки 1 см). Работа с данным отчетом идентична работе с табличным отчетом (MakeDsChees). К нему можно добавить заголовок функцией встроенного языка CheesHeaderTextAdd. Также отчет может быть распечатан командой CheesPrint, показан на экране в режиме предварительного просмотра командой CheesShow, или показан в виде таблицы командой CheesTable (из которой возможен экспорт в Word). В приведенном примере показан отчет, отражающий помесячно начисления каждого сотрудника за год. По строкам показаны сотрудники, по столбцам—месяцы. Итоги подведены по строкам и столбцам.

RepInitChees, RepInsertConst, RepInsertHeader, RepInsertItogQu, RepInsertQu, RepInsertTxt, RepPreView, RepViewTable

Построение сложных отчетов

Под термином «сложные отчеты» в данном случае понимаются отчеты, собирающие в себе разноплановую информацию – результаты нескольких SQL-запросов, различные рассчитанные величины. Таковыми являются практически все отчеты, сдаваемые в налоговую инспекцию или статистику. Для примера приведен некоторый абстрактный отчет, обрабатывающий результаты расчета зарплаты за указанный месяц и отражающий:

  • свод начислений
  • распределение зарплаты по сотрудникам (начислено, удержано, к выплате, долг)
  • сотрудник с наименьшими начислениями
  • сотрудник с наибольшими начислениями
  • разница между наименьшим и наибольшим начислением
//@@InputDataMonth
ps if param('Data1')='' then SetParam('data1','01/07/02','d')
ps DefineGlobal('god','integer')
ps DefineGlobal('mes','integer')
ps god:=year (jStrToDate(param('data1')))
ps mes:=month(jStrToDate(param('data1')))
ps SetParam('god',IntToStr(god),'i')
ps SetParam('mes',IntToStr(mes),'i')
ps if god<>2002 then ShowMessage('В баззе данных представлена информация только за 2002 год!')

@@RepInitChees 1
@@RepInsertHeader Отчет по заработной плате
ps DefineGlobal('s','string')
ps s:='за '+SpellMonth(mes)+' '+IntToStr(god)+' года'
@@RepInsertHeader global:s
@@RepInsertHeader 
@@Razdel свод начислений
@@RepInsertTxt начисление зарплаты по видам оплат, сумма
select knu,
       sum(suma) as suma,
       max(vo.name) as Name
from zaSal sl
left join zsVidOpl vo
  on vo.knu=sl.knu
where god=:god and mes=:mes
group by knu
order by knu
@@RepInsertQu name,suma

@@Razdel Распределение по сотрудникам
@@RepInsertTxt распределение по сотрудникам, сумма
select tn,sum(suma) as SumaNach,0 as sumaUd
from zaSal
where god=:god and mes=:mes
group by tn
@@SaveDsToFile c:\q1
//Выбор удержаний
select tn,sum(suma) as sumaUd
from zaUd
where god=:god and mes=:mes
group by tn
@@AppendDsToFile c:\q1
//Рассчет итогов
select tn,
       sum(SumaNach) as SumaNach,
       sum(SumaUd)   as SumaUd,
       max(f) as f,
       max(i) as i,
       max(o) as o       
from "c:\q1" q1
Left join zsEmpl se
  on q1.tn=se.tn
Group by tn
@@InsertField razn:f=SumaNach-SumaUd
@@InsertField kVypl:f=ifF(Razn>0,razn,0)
@@InsertField Dolg:f=ifF(Razn<0,-razn,0)
@@InsertField TnFio:a20=IntToStr(round(tn))+'-'+f+' '+copy(i,1,1)+'.'+copy(o,1,1)+'.'
@@SaveDslToFile c:\q2
select *
from "c:\q2"
order by TnFio
@@RepInitChees 4
@@RepInsertTxt
@@RepInsertTxt Распределение по сотрудникам, начислено, удержано, к выплате, долг
@@RepInsertQu tnFio,SumaNach,SumaUd,kVypl,Dolg
@@Razdel Поиск минимальной и максимальной зарплаты
@@RepInitChees 1
ps DefineGlobal('MinSuma','double')
ps DefineGlobal('MinF','string')
ps DefineGlobal('MaxSuma','double')
ps DefineGlobal('MaxF','string')
ps DefineGlobal('MinMaxRazn','double')
select tn,sum(suma) as suma, max(f) as f
from zaSal sl
left join zsEmpl se
  on se.tn=sl.tn
where god=:god and mes=:mes
group by tn
order by suma
//Первой (текущей) будет запись с минимальной суммой
ps MinSuma:=suma; MinF:=f
select tn,sum(suma) as suma, max(f) as f
from zaSal sl
left join zsEmpl se
  on se.tn=sl.tn
where god=:god and mes=:mes
group by tn
order by suma desc
//Первой (текущей) будет запись с максимальной суммой
ps MaxSuma:=suma; MaxF:=f
ps MinMaxRazn:=MaxSuma-MinSuma
@@RepInsertTxt
@@RepInsertTxt
ps s:='Наименьшая зарплата у '+MinF
@@RepInsertConst global:s,global:MinSuma
ps s:='Наибольшая зарплата у '+MaxF
@@RepInsertConst global:s,global:MaxSuma
@@RepInsertConst bold,Разница в зарплатах,global:MinMaxRazn
@@RepPreView

Отчет строится в виде таблицы по следующему принципу: в начале идет колонка с номером строки, следующей идет колонка с текстовой информацией, описывающей строку, затем следует несколько колонок с цифрами (суммы строки). Для определения заголовка отчета применяется команда RepInsertHeader. После команды следует текстовая информация, которая будет помещена в заголовок отчета. Параметром команды может быть строковая глобальная переменная. В этом случае в команде указывается global:VarName, где VarName – имя переменной. Для определения количества колонок с суммами в отчете используется команда RepInitChees, после которой указывается количество колонок. Эта команда может использоваться несколько раз на протяжении отчета. Команда RepInsertTxt используется для вставки текстовой информации в колонки отчета (например, наименований столбцов). После команды следует указать через запятую информацию, помещаемую в каждую колонку отчета. Здесь также можно использовать глобальные переменные. Команда RepInsertConst используется для вставки цифровой информации в отчет. После команды следует указать текстовую информацию, описывающую данную строку, затем, через запятую, содержимое числовых ячеек. В любой колонке может быть значение глобальной переменной. В случае необходимости выделить строку жирным, после команды необходимо указать ключевое слово «bold», после которого следует поставить запятую. Команда RepInsertQu используется для вставки результатов выполнения последнего SQL-запроса в отчет. После команды следует указать имя поля, значение которого следует поместить в колонку, описывающую строку. Затем, через запятую, имена полей, помещаемые в числовые колонки отчета. В отчет последовательно помещаются все записи запроса. После последней записи в отчет добавляется строка с комментарием «Итого» и итогами по каждой колонке. Эта строка выделяется жирным. Команда RepInsertItogQu аналогична предыдущей команде. В отличие от нее в отчете появляется только одна, итоговая строка. Для предварительного просмотра результатов используется команда RepPreView. Команды для печати отчета не предусмотрено. Вместо нее следует использовать в начале скрипта команду InputDataMonth, которая запрашивает входные параметры для формирования отчета, а также позволяет просматривать и распечатывать сформированный отчет (в скрипте следует удалить комментарий с первой строки и закомментировать последнюю строку).

InputUsing, Procedure, EndProcedure, RunProcedure

Команда InputUsing предназначена для определения формы ввода. Параметрами команды могут быть следующие ключевые слова:

          Label        –вывод надписи на экран
          EdYearMonth  –ввод года и месяца
          EdInteger    –ввод целого числа
          Edit         –ввод строки
          EdDate       –ввод даты 
          EdFloat      –ввод числа с плавающей точкой  
          EdData1Data2 –ввод 2-х дат (периода)
          ControlReport–вывод панели с управляющими кнопками (выполнить, таблица, просмотр, печать)

Каждое ключевое слово определяет новую строку с полем (полями) ввода и комментарием. Комментарий следует указывать через запятую после ключевого слова. Далее, снова через запятую, указывается имя поля ввода. С таким именем будет открыт параметр, имеющий тип, определяемый типом поля ввода. Присвоенное значение параметру до команды InputUsing приведет к заполнению соответствующего поля ввода присвоенным значением. Ключевые слова EdData1Data2 и EdYearMonth определяют сразу два поля ввода, и, соответственно, два параметра. Имена параметров при этом определяются предопределенными Data1, Data2 и Year,Month (соответственно) и именем, указанным в строке (в примере YearGm и MonthGm). Одним из параметров ключевого слова ControlReport является OnRun=ИмяПроцедуры

где ИмяПроцедуры – имя процедуры, выполняемой по нажатию кнопки Выполнить. Эта процедура должна содержать команду MakeDsChees, MakeChees, или MakeReport, результаты которых будут показаны на экране кнопкой Таблица, распечатаны кнопкой Печать, или показаны в режиме предварительного просмотра при помощи правой кнопки мышки.

@@Procedure расчет
select knu,vo.name as nameKnu,kolich,suma,suma as itg,'1' as nSort
from zaSal sl
left join zsVidOpl vo
  on vo.knu=sl.knu
where tn=:tn and god=:YearGm and mes=:MonthGm
@@SaveDsToFile c:\qTbl
select knu,vo.name as nameKnu,suma,0-suma as itg,'2' as nSort
from zaUd ud
left join zsVidOpl vo
  on vo.knu=ud.knu
where tn=:tn and god=:YearGm and mes=:MonthGm
@@AppendDsToFile c:\qTbl
select "*** Начислено" as NameKnu,
       sum(suma) as suma,
       '3' as nSort
from "c:\qTbl"
where knu<500
@@AppendDsToFile c:\qTbl
select "*** Удержано" as NameKnu,
       sum(suma) as suma,
       '4' as nSort
from "c:\qTbl"
where knu>=500
@@AppendDsToFile c:\qTbl
select "*** К выплате" as NameKnu,
       sum(itg) as suma,
       '5' as nSort
from "c:\qTbl"
@@AppendDsToFile c:\qTbl
select *
from "c:\qTbl"
order by nSort,knu
@@MakeDsChees(
  knu,Код,2,center,,,000
  NameKnu,Вид оплаты (удержания),7
  Kolich,Отра- ботка,2,Right,,,0.#
  suma,Сумма,2,Right,,,0.00
)
ps CheesHeaderTextAdd('Расчетный лист')
select *
from zsEmpl 
where tn=:tn
ps CheesHeaderTextAdd(f+' '+i+' '+o)
ps CheesHeaderTextAdd('за '+SpellMonth(val_i(param('MonthGm')))+' '+param('YearGm')+' года')
@@EndProcedure

ps setParam('YearGm','2002','i')
ps setParam('MonthGm','7','i')
ps setParam('tn','37','i')
@@InputUsing(
          Label        ,Печать расчетных листов
          edYearMonth  ,,gm
          edInteger,Табельный номер,tn
          ControlReport,,,onRun=расчет)

Процедура определяется в тексте скрипта командой procedure, после которой указывается имя процедуры. Окончание процедуры в теле скрипта определяется командой EndProcedure.

ResetGlobalVar

Команда предназначена для уничтожения глобальных переменных (см. DefineGlobal).

Стандартные отчеты

Начиная с версии 1.01 в программу включаются стандартные бухгалтерские отчеты. Для формирования отчета используются специальные команды, наименование которых начинается с “Z_”. Например команда Z_Forma8dr – формирует форму 8ДР. После имени команды, в качестве параметров следуют свойства отчета с соблюдением синтаксиса: Имя_свойства=Значение_свойства, В качестве значения свойства можно указать имя глобальной переменной. Для этого, перед именем переменной следует указывать ключевое слово “Global:”

Z_Forma8dr

Данная команда формирует отчет по форме 8ДР. Перед вызовом команды необходимо сформировать запрос, результат которого будет иметь следующие поля:

tin            - Идентификационный номер
  d_priyn        - Дата приема на работу 
  d_zviln        - Дата увольнения сотрудника
  S_dox          - Сумма выплаченного дохода
  S_nar          - Сумма начисленного дохода
  S_taxN         - Сумма удержанного подоходного налога
  S_taxP         - Сумма перечисленного подоходного налога
  ozn_dox        - Признак дохода
  OZN_PILG       - Признак льготы  
  // Имена полей соответствуют приказу ГНАУ № 473 от 30.12.1997 г 
  // в редакции от 08.04.2003 г.

После выполнения запроса следует вызов команды, которая имеет следующие параметры:

okpo                 -ОКПО организации
TinPrivate           -Идф.номер частного предпринимателя
TinDirector          -Идф.номер директора
TinGlBux             - Идф.номер гл.бухгалтера
FioDirector          -ФИО директора
FioGlBux             -ФИО гл.бухгалтера
FioPrivate           - ФИО частного предпринимателя
tlfDirector          - Телефон директора
tlfGlBux             - Телефон гл.бухгалтера
TlfPrivate           - Телефон частного предпринимателя
NameOrg              - Наименование организации
OKPONalogovoy        - ОКПО налоговой инспекции
NameNalogovoy        -Наименование налоговой
Kvartal              -Отчетный квартал
Year                 -Год
WorkShtat            -Работало в штате
WorkSovm             -Работало по совместительствц
Для записи на дискету:
fileName_KOdOblasti  -код области
fileName_kodNalogovoy-код налоговой инспекции

В приведенном примере показано как сформировать форму 8ДР на основании стандартной базы данных зарплаты. Следует не забывать, что в отчет необходимо включать информацию о выплатах частным предпринимателям.

@@Razdel Исходные данные
@@Param God:i=2002
@@Param Kvrt:i=2

@@Razdel Определения переменных
ps DefineGlobal('kvrt','integer')
ps DefineGlobal('god','integer')
ps DefineGlobal('TaxPerechKvartal'  ,'double')


ps DefineGlobal('mes1','integer')
ps DefineGlobal('mes2','integer')
ps DefineGlobal('Data1','integer')
ps DefineGlobal('Data2','integer')

ps god:=val_i(param('god'))
ps kvrt:=val_i(param('kvrt'))
ps mes1:=(kvrt-1)*3+1
ps mes2:=mes1+2
ps setParam('mes1',IntToStr(mes1),'i')
ps setParam('mes2',IntToStr(mes2),'i')
ps Data1:=FirstDayMonth(god,mes1)
ps Data2:=LastDayMonth(god,mes2)
ps setParam('data1',jDateToStr(data1),'d')
ps setParam('data2',jDateToStr(data2),'d')

select tn,
       sum(suma) as s_Dox,
       0 as s_Tax
from zaSal sl
left join zsVidOpl vo
  on sl.knu=vo.knu
where god=:god and mes between :mes1 and :mes2 and vo.Ozn_dox is null
group by tn
@@SaveDsToFile c:\q1
select tn,
       sum(suma) as s_Tax
from zaUd
where knu=598 and god=:god and mes between :mes1 and :mes2
group by tn
@@AppendDsToFile c:\q1
select tn,
       max(tin) as tin,
       max(d_priyn) as d_priyn,
       max(d_zviln) as d_zviln,
       max(cast(isSovm as integer)) as isSovm,
       sum(s_dox) as s_dox,
       sum(s_tax) as s_tax
from "c:\q1" q1
left join zsEmpl se
  on se.tn=q1.tn
group by tn
@@InsertField ozn_dox:i=round(ifF(isSovm<>1,1,2))
@@SaveDslToFile c:\q2
update "c:\q2"
set d_priyn=null
where d_priyn not between :data1 and :data2
@@Razdelitel
update "c:\q2"
set d_Zviln=null
where d_Zviln not between :data1 and :data2
@@Razdelitel
select tn,
       sum(suma) as s_Dox,
       max(Ozn_dox) as Ozn_dox,
       max(tin) as tin
from zaSal sl
left join zsVidOpl vo
  on sl.knu=vo.knu
left join zsEmpl se
  on se.tn=sl.tn
where god=:god and mes between :mes1 and :mes2 and not vo.Ozn_dox is null
group by tn
@@AppendDsToFile c:\q2
select Tin,
       S_dox,Ozn_dox,D_priyn,D_zviln,
       s_dox as S_nar,
       S_tax as S_taxN,
       S_tax as S_taxP
from "c:\q2"
where not tin is null

@@Z_Forma8dr(
      okpo                 =12345678
//      TinPrivate           =9876543210
      TinDirector          =1346867498
      TinGlBux             =4574946877
      FioDirector          =Петров П.Н.
      FioGlBux             =Иванов И.И.
//      FioPrivate           =Предприниматель
      tlfDirector          =12-34-56
      tlfGlBux             =78-91-23
//      TlfPrivate           =99-88-77
      NameOrg              =Звездный путь
      OKPONalogovoy        =99994321
      NameNalogovoy        =ДПI в звездном районе
      Kvartal              =global:kvrt
      Year                 =Global:god
      WorkShtat            =123
      WorkSovm             =4

      fileName_KOdOblasti  =04
      fileName_kodNalogovoy=04    
)

@@CheesShow
//@@CheesPrint //Убрать комментарий для вывода документа на печать

Свойства шахматки

Нижеследующие свойства используются после команды MakeChees, для определения параметров отчета.

orientation
Определяет ориентацию бумаги
PoPortrait-книжная
PoLandscape-альбомная
FieldLeft, fieldTop, fieldRight, fieldBottom
Определяют поля листа в сантиметрах: левое, верхнее, правое, нижнее соответственно.
titleLinesEnabled
Определяет наличие рамки вокруг заголовка (False,True).
DefaultColWidth, defaultRowHeight
Определяет ширину колонки и высоту строки по умолчанию (в сантиметрах).
DefaultFontName, defaultFontSize, defaultFontStyle
Наименование, размер и стиль шрифта по умолчанию. Стиль определяется одним или несколькими элементами множества: [fsBold, fsItalic, fsUnderLine]
defaultTitleFontName, defaultTitleFontSize, defaultTitleFontStyle
Наименование, размер и стиль шрифта для вывода заголовков столбцов.
DefaultHeaderTextFontSize, defaultHeaderTextFontStyle, defaultHeaderTextFontName
Наименование, размер и стиль шрифта для вывода заголовка отчета.
DefaultZeroColFontName, defaultZeroColFontSize, defaultZeroColFontStyle
Наименование, размер и стиль шрифта для вывода первого столбца шахматки (с комментариями строки).
DefaultZeroColFirstIndent, defaultZeroColLeftIndent, defaultZeroColRightIndent
Отступ первой колонки для крайнего правого и последующих листов слева, отступ справа.
DefaultLineColLeft, DefaultLineColRight, DefaultLineRowTop, defaultLineRowBottom
Свойства определяют наличие линии по границе каждых строки или столбца (слева, справа, сверху, снизу). Допустимые значения False, True.
DefaultAlignment
Выравнивание информации в ячейках. Допустимые значения taLeftJustify, taRightJustify, taCenter (по левому краю, по правому краю, по центру).
DefaultNumericFormat
Числовой формат по умолчанию. (например 0.00-определяет 2 знака после запятой, 0.##-после запятой выводятся только значащие цифры, но не более 2-х) .
zeroColEnabled
Наличие первого столбца с комментариями к строкам. Допустимые значения False, True.
ZeroColWidthFirst, zeroColWidthNext
Ширина первого столбца с комментариями к строкам для крайних левых листов и последующих листов.
AutoSumRows, AutoSumColumns
Автоматическое добавление итогов по строкам и по столбцам. Допустимые значения False, True.





Copyright © 2004-2016 "Delphi Sources". Delphi World FAQ




Группа ВКонтакте   Ссылка на Twitter   Группа на Facebook