|
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 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, то в отчете появится одна ячейка с суммой всех таковых записей. Поля 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, для определения параметров отчета.
Получить программу для ознакомления Вы можете на сайте http://amigonet.narod.ru. Вместе с программой Вы получите учебную базу данных и примеры скриптов, подробный разбор которых приведен в статье. |
Автор проекта: ___Nikolay