Глава 10. СОЗДАЕМ ОТЧЕТЫ

Отчет о движении товаров

Предыдущий отчет мы построили исключительно на SQL-запросе к документам. Сейчас нам предстоит сделать отчет на основе SQL -запросов к таблице проводок ACC_TURN.

Заказчик желает видеть отчет в долларах США примерно такого вида :

Заказчик желает, чтобы создать такой отчет можно было бы для произвольного диапазона дат и для каких-то определенных товаров и/или марок, например, для всех стиральных машин с марками BOSCH и SIEMENS. Еще он хочет, чтобы отчет можно было построить для какого- то одного склада или суммарно для всех складов.

Не исключено, что «монстры SQL» могут написать такой мудреный SQL-запрос, что он вернет нам весь этот отчет целиком. Но мы будем стараться придерживаться простых запросов и следить за самым главным показателем: скорость . Ничто так не расстраивает конечного пользователя, как если машина «долго думает». Особенно если это менеджер по продажам, которому нужно прямо сейчас ответить по телефону, сколько у него на складе стиральных машин.

Поэтому мы разобьем задачу на более простые задачи:

• Первым SQL-запросом выясним начальные остатки для всех товаров
• Вторым SQL-запросом выясним приход и расход для всех товаров за указанный промежуток дат
• Затем как-то все это объединим по каждому товару .
• Вычислим конечные остатки и средние стоимости единиц

Вызовем окно Интерактивного SQL.

SQL-запрос, возвращающий начальные остатки на складе 1007 на какую-то дату выглядит очень просто:

select object_id, sum(debit-credit) a, sum(quantity_debit – quantity_credit) q
from acc_turn
where acc_id = 1007 and op_date < ’01.06.2003’
group by object_id

Так же просто выглядит запрос, возвращающий приход и расход товара за определенный промежуток дат:

select object_id, sum(debit) ad, sum(credit) ac, sum(quantity_debit) qd, sum(quantity_credit) qc
from acc_turn
where acc_id = 1007 and op_date between ’01.06.2003’ and ’15.07.2003’
group by object_id

И что очень важно – такие запросы работают быстро. Однако как объединить результаты работы этих двух запросов так, чтобы для каждого товара object_id одной строкой вернуть результаты первого и второго запросов одновременно? Здесь есть один способ, который мы назвали «диагональный метод сборки сводного отчета» и рекомендуем этот метод разработчикам. Суть этого метода в том, что мы создаем в базе данных хранимую процедуру, которая выполняет несколько SQL-запросов последовательно , один за другим, возвращая результаты суммирования в разных колонках, а id объектов группировки – в одной колонке :

Когда сканируются строки результирующего набора первого SQL-запроса, значения колонок присваиваются выходным параметрам хранимой процедуры a и q , а остальным параметрам при этом присваивается нуль. Затем сканируются строки результирующего набора второго SQL-запроса, значения колонок присваиваются выходным параметрам хранимой процедуры ad, ac, qd и qc, а нуль присваивается параметрам a и q. Хранимые процедуры сервера InterBase устроены так, что возможно осуществить еще один SQL-запрос, работающий уже с результатами, выданными этой хранимой процедурой. Это будет простой запрос, с группировкой по object_id.

Итак, создадим хранимую процедуру:

create procedure goods_flow(date1 date, date2 date)
returns(object_id integer, acc_id integer, a decimal(18,2), q integer,
        ad decimal(18,2), ac decimal(18,2), qd integer, qc integer)
as
begin
  a = 0; q = 0; ad = 0; ac = 0; qd = 0; qc = 0; /*присваиваем 0*/
  /*запрос остатков*/
  for select
    a.object_id, a.acc_id,
    sum(a.debit - a.credit) a, sum(quantity_debit - quantity_credit) q
  from acc_turn a, acc
  where
    a.acc_id = acc.acc_id and
    acc.parent_id = 1005 and
    a.op_date < :date1
  group by a.object_id, a.acc_id
  into :object_id, :acc_id, :a, :q do
  suspend;
  a = 0; q = 0; /*присваиваем 0*/
  /*запрос приходов и расходов*/
  for select a.object_id, a.acc_id,
      sum(a.debit) ad, sum(a.credit) ac, sum(a.quantity_debit) qd, sum(a.quantity_credit) qc
  from acc_turn a, acc
  where
    a.acc_id = acc.acc_id and
    acc.parent_id = 1005 and
    a.op_date between :date1 and :date2
  group by a.object_id, a.acc_id
  into :object_id, :acc_id, :ad, :ac, :qd, :qc do
  suspend;
end

В этой процедуре мы объединили таблицу проводок ACC_TURN с таблицей счетов ACC по полю ACC_ID для того, чтобы получить результаты для всех складов. Склады у нас являются дочерними счетами к регистру «Товары», у которого ACC_ID=1005. Поэтому мы и записали условие PARENT_ID = 1005.

Теперь у нас есть хранимая процедура goods_flow, и мы можем сделать такой SQL-запрос:

select object_id,
  sum(a) a, sum(q) q, sum(ad) ad, sum(ac) ac, sum(qd) qd, sum(qc) qc
from goods_flow('01.06.2003', '15.07.2003')
where acc_id = 1007
group by object_id

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

Попробуем выполнить в окне Интерактивного SQL усовершенствованный запрос:

select
  gf.object_id,
  gk.name goods_kind_name,
  gm.name goods_mark_name,
  g.article,
  sum(gf.a) a, sum(gf.q) q, sum(gf.ad) ad, sum(gf.ac) ac, sum(gf.qd) qd, sum(gf.qc) qc,
  g.price_w, g.price_r
from
  goods_flow('01.06.2003', '15.07.2003') gf,
  goods g,
  goods_kind gk,
  goods_mark gm
where
  gf.acc_id = 1007 and
  gf.object_id = g.id and
  g.goods_kind = gk.id and
  g.goods_mark = gm.id
group by
  gf.object_id, gk.name, gm.name, g.article, g.price_w, g.price_r
order by 2,3,4

Мы решили, что данный отчет будет смотреться нагляднее, если вместо сборных наименований товаров изобразить три отдельные колонки: вид, марка и артикул. Поэтому мы не стали объединять набор с таблицей OBJECT_NAMES, и вместо этого объединили его с таблицами GOODS_KIND и GOODS _MARK. К тому же нам удалось при таком подходе упорядочить результирующий набор по наименованию вида, марки и артикулу при помощи выражения:

order by 2,3,4

Обратим внимание на время выполнения запроса. Как видим, оно не превышает долей секунды.

Нам осталось создать интерфейс отчета и еще немного усовершенствовать SQL -запрос, чтобы получить фильтрацию по складам, а также видам и маркам товаров.

Включим режим «Дизайнер» и создадим новый проект.

Сохраним модуль формы под названием goods_flow.pas , а проект под названием goods_flow_project .ipr.

Придадим форме свойства:

Добавим к форме компонент IBQuery с палитры InterBase и придадим ему свойства:

Скопируем запрос из окна ISQL в свойство SQL компонента qryReport . Для этого растянем главное окно, вызовем окно ISQL , если оно не на экране, нажмем Ctrl+ P, чтобы показать текст последнего SQL-запроса, выделим этот текст и скопируем в буфер обмена Windows при помощи клавиш Ctrl+C. Сожмем главное окно, выберем компонент qryReport, дважды щелкнем на его свойства SQL и в появившемся редакторе запроса вставим из буфера обмена наш запрос с помощью клавиш Ctrl+V.

Дважды щелкнем на компоненте qryReport и в Редакторе полей добавим все поля в список.

Так мы создали «постоянные поля», которым теперь можно назначить русские заголовки и способы форматирования. Назначим полям свойства в Инспекторе объектов:

Расположим поля прихода сумм и количеств друг за другом. Для этого поменяем местами поля QD и AC, перетащив при помощи мыши поле QD на одну позицию вверх в редакторе полей, или просто уменьшив на единицу его свойство Index в Инспекторе объектов.

Нам нужно создать еще пять полей: «Конечный остаток , USD», «Конечный остаток, Ед.», « Средняя себестоимость единицы», «Оптовая наценка, %», « Розничная наценка, %».

Закроем редактор полей. Закроем запрос, если он у нас открыт, установив свойство Active = False. Добавлять вычисляемые поля рекомендуется при закрытом запросе, иначе компонент может выдать сообщение «Поле не найдено». Вызовем снова редактор полей и создадим два новых поля типа IBBCD:

Создадим три новых поля типа Float:

Перетащим мышью поля CA и CQ в списке так, чтобы они расположились после полей прихода:

Закроем редактор. Создадим у компонента qryReport обработчик события OnCalcFields :

procedure TGoodsFlowForm.qryReportCalcFields(DataSet: TDataSet);
begin
  with DataSet do
  begin
    {вычисляем остатки}
    FieldByName('CA').AsCurrency := FieldByName('A').AsCurrency +
      FieldByName('AD').AsCurrency - FieldByName('AC').AsCurrency;
    FieldByName('CQ').AsCurrency := FieldByName('Q').AsCurrency +
      FieldByName('QD').AsCurrency - FieldByName('QC').AsCurrency;
    {вычисляем среднюю стоимость единицы на складе}
    if FieldByName('CQ').AsCurrency <> 0 then
      FieldByName('COST').AsCurrency :=
        FieldByName('CA').AsCurrency / FieldByName('CQ').AsCurrency;
    {вычисляем наценки в процентах}
    if FieldByName('COST').AsCurrency <> 0 then
    begin
      FieldByName('W_RATE').AsCurrency :=
        (FieldByName('PRICE_W').AsCurrency /
         FieldByName('COST').AsCurrency - 1) *100;
      FieldByName('R_RATE').AsCurrency :=
        (FieldByName('PRICE_R').AsCurrency /
         FieldByName('COST').AsCurrency - 1)*100;
    end;
  end;
end;

Добавим на форму компонент Panel с палитры Standard:

Для отображения результатов запроса нам понадобится необычная сетка, так как полей очень много и в одну строку они явно не уместятся. Откроем запрос, установив у компонента qryReport свойство Active = True. Добавим в качестве сетки ячеистую сетку DbAltGrid с палитры Quasidata и придадим ей свойства в Инспекторе объектов:

Дважды щелкнем на свойстве Columns для того, чтобы вызвать редактор колонок сетки. Добавим все колонки в список при помощи соответствующего пункта контекстного меню:

Удалим из списка колонку OBJECT_ID, так как мы не хотим ее отображать и закроем редактор колонок. Мы создали «постоянные» (persistent) колонки и теперь можем установить их свойства.

Компонент DbAltGrid позволяет расположить ячейки в пределах одной строки произвольно . Увеличим размер ячеек «Вид товара», «Марка » и «Артикул». Для этого подводим курсор мыши к нижнему краю заголовка колонки, и когда вид курсора мыши изменится, нажимаем левую кнопку мыши, далее, удерживая ее, «тянем» границу заголовка вниз. Ячейка увеличивает свой размер, причем дискретно. Установим размер ячеек этих трех колонок равным двойному размеру строки сетки по умолчанию:

А теперь «перетащим» заголовки колонок количеств, расположив каждое количество под соответствующей суммой (остатки под остатками, приходы под приходами и т.д.). Для « перетаскивания» заголовка колонки подводим к его центру курсор мыши , нажимаем левую кнопку и, удерживая ее, перемещаем мышью туда, куда нам нужно, после чего кнопку мыши отпускаем. Эта технология носит название drag and drop . В результате мы должны получить примерно такой вид сетки :

Увеличим размер ячейки колонки средней себестоимости до двойной высоты ( как ячейки колонок «вид товара» и перетащим ее влево, расположив перед колонками цен.

Перетащим заголовки колонок процентов наценки, расположив каждый под соответствующей ценой.

Сохраним все изменения .

Вызовем еще раз редактор колонок сетки, выберем все колонки , кроме вида товара, марки и артикула и установим в Инспекторе объектов для них выравнивание по центру Alignment = taCenter.

Выберем в редакторе колонок все колонки количеств (в их именах присутствует буква Q) и колонки процентов наценок ( в их именах присутствует слово RATE). Одним словом, мы выбрали все колонки, ячейки которых оказались у нас в нижнем ряду. Изменим в Инспекторе объектов цвет Color колонки на серо-голубой, а цвет шрифта колонки Font.Color на clPurple (пурпурный).

Раскрасим по вкусу остальные колонки, устанавливая у них свойство Color.

Стараемся избегать кричащих цветов, так как большие поверхности ярких цветов будут утомлять глаза пользователя:

Обратите внимание, что значения вычисляемых полей не отображаются в режиме дизайна. Только при запуске проекта тот текст, что мы прописали в обработчик OnCalcFields начнет работать и вычисляемые поля получат свои значения.

Теперь займемся органами управления. Нам необходима во-первых кнопка «Запрос». Добавим с палитры Standard кнопку Button на верхнюю панель нашей формы. Назначим ей свойства Caption = Запрос и Name = btnQuery. Создадим ей обработчик события OnClick:

procedure TGoodsFlowForm.btnQueryClick(Sender: TObject);
begin
  Screen.Cursor := crHourGlass; //изменяем курсор мыши на "часы"
  try
    qryReport.Close;
    qryReport.Open;
  finally
    Screen.Cursor := crDefault; //восстанваливаем курсор мыши
  end;
end;

Мы изменяем курсор мыши на «часы» на то время, пока отрабатывается запрос, чтобы пользователь понял, что нужно подождать. Хотя ждать ему придется недолго - наш запрос работет очень быстро. Закроем запрос в режиме дизайна, установив Active = False. Запустим проект ( F9). После нажатия кнопки «Запрос», запрос должен открыться, а в вычисляемых полях - появиться значения.

Однако пока мы запрашиваем отчет на фиксированные даты '01. 06.2003', '15.07.2003' , которые мы прописали явно в тексте запроса. Нам нужно запросить отчет за произвольный период дат. Поэтому вернемся в режим дизайна и изменим текст запроса в компоненте qryReport, заменив эти две даты на параметры :DATE1 и : DATE2.

…..
  goods_flow(:DATE1, :DATE2) gf,
…..

Добавим на верхнюю панель два компонента DateEdit с палитры RX Controls, и изменим текст обработчика OnClick кнопки «Запрос ». Добавленные команды выделены здесь жирным шрифтом:

procedure TGoodsFlowForm.btnQueryClick(Sender: TObject);
begin
  Screen.Cursor := crHourGlass; //изменяем курсор мыши на "часы"
  try
    qryReport.Close;
    qryReport.ParamByName('DATE1').AsDateTime := DateEdit1.Date;
    qryReport.ParamByName('DATE2').AsDateTime := DateEdit2.Date;
    qryReport.Open;
  finally
    Screen.Cursor := crDefault; //восстанваливаем курсор мыши
  end;
end;

Создадим для формы GoodsFlowForm обработчик события OnCreate:

procedure TGoodsFlowForm.FormCreate(Sender: TObject);
begin
  DateEdit1.Date := EncodeDate(YearOf(Date), MonthOf(Date), 1);
  DateEdit2.Date := Date;
end;

Запустим проект и нажмем кнопку «Запрос»:

Нам нужно еще реализовать фильтрацию в отчете. И хорошо бы иметь возможность сортровать записи в отчете, предоставив выбор способа сортировки пользователю.

Вернемся в режим дизайна. Увеличим высоту верхней панели до 152 пикселей.

Добавим три компонента RxCheckListBox с палитры RX Controls. Назовем компоненты clbKind, clbMark и clbStock. Они будут нам служить органами управления фильтрацией по видам товаров, маркам и складам. Выберем компонент clbKind и дважды щелкнем на его свойстве Items в Инспекторе объектов. Появится специальный редактор списка , в котором добавим элемент в список, нажав клавишу New. Назовем пункт <Все виды товаров> и выберем Checked в выпадающем списке:

Аналогично создадим один элемент списка в компоненте clbMark с названием <Все марки>.

Аналогично создадим один элемент списка в компоненте clbStock с названием <Все склады>.

Добавим на панель компонент ComboBox с палитры Standard и придадим ему свойства:

Добавим два компонента Label с палитры Standard и придадим их свойствам Caption значения ‘Порядок сортировки’ и ‘Даты’.

Добавим компонент CheckBox с палитры Standart и придадим ему свойства :

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

Добавим три компонента IBQuery с палитры InterBase и придадим им свойства:

Добавим в обработчик события OnCreate формы код, выделенный жирным шрифтом:

procedure TGoodsFlowForm.FormCreate(Sender: TObject);
begin
  DateEdit1.Date := EncodeDate(YearOf(Date), MonthOf(Date), 1);
  DateEdit2.Date := Date;
  with qryGoodsKind do
  begin
    Open;
    while not Eof do //заполняем список видов товаров
    begin
      clbKind.Items.Add(Fields[1].AsString);
      Next;
    end;
  end;

  with qryGoodsMark do
  begin
    Open;
    while not Eof do //заполняем список марок
    begin
      clbMark.Items.Add(Fields[1].AsString);
      Next;
    end;
  end;

  with qryStock do
  begin
    Open;
    while not Eof do //заполняем список складов
    begin
      clbStock.Items.Add(Fields[1].AsString);
      Next;
    end;
  end;
end;

Запустим проект:

Мы видим, что списки заполнились видами товаров и их марками из соответствующих справочников, а список складов - из системной таблицы ACC. Нам осталось организовать саму фильтрацию. Если пользователь оставит птичку на пункте «Все виды товаров », то фильтрация по видам производиться не должна, если же он уберет эту птичку и отметит птичками какие- то конкретные виды товаров, то отчет должен включить только товары отмеченных видов. Аналогично должны работать фильтры марок и складов.

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

Создадим константу в разделе implementation, скопировав текст запроса из компонента qryReport и несколько его видоизменив:

const
  SQL_GOODS_FLOW_REPORT =
       'select'#13+
       '  gf.object_id,'#13+
       '  gk.name goods_kind_name,'#13+
       '  gm.name goods_mark_name,'#13+
       '  g.article,'#13+
       '  sum(gf.a) a, sum(gf.q) q, sum(gf.ad) ad, sum(gf.ac) ac,'#13+
       '  sum(gf.qd) qd, sum(gf.qc) qc,'#13+
       '  g.price_w, g.price_r'#13+
       'from'#13+
       '  goods_flow(:DATE1, :DATE2) gf,'#13+
       '  goods g,'#13+
       '  goods_kind gk,'#13+
       '  goods_mark gm'#13+
       'where'#13+
       '  gf.object_id = g.id and'#13+
       '  g.goods_kind = gk.id and'#13+
       '  g.goods_mark = gm.id'#13+
       '%s'+               //здесь будет фильтр по видам, маркам и складам
       'group by'#13+
       '  gf.object_id, gk.name, gm.name, g.article, g.price_w, g.price_r'#13+
       '  %s'+             //здесь будут отсеиваться нулевые количества
       'order by %s'; //здесь будет упорядочивание

Как можно заметить, мы исключили явное указание склада gf .acc_id = 1007 и добавили в трех местах %s.

Теперь мы создадим функцию, которая будет собирать строку фильтра , анализируя отмеченные птичками пункты определенного списка.

Добавим в секцию private класса формы объявление функции:

    { Private declarations }
    function GetFilterString(ListBox: TRxCheckListBox;
      Query: TIBQuery; const FieldName: string): string;

Добавим в раздел implementation реализацию этой функции:

{Сборка строки фильтрации вида 'and <поле> in (1,5,6,11,92)' }
function TGoodsFlowForm.GetFilterString(ListBox: TRxCheckListBox;
  Query: TIBQuery; const FieldName: string): string;
begin
  Result := '';
  if ListBox.Checked[0] then
    exit;
  with Query do //сканируем набор записей
  begin
    First;
    while not Eof do
    begin
      if ListBox.Checked[RecNo] then //если соответствующий элемент
      begin                          //в списке отмечен птичкой
        if Result <> '' then Result := Result + ','; //добавляем запятую
        Result := Result + Fields[0].AsString; //добавляем в строку
      end;
      Next;
    end;
  end;
  if Result <> '' then //окончательно форматируем условие фильтрации
    Result := Format('and %s in (%s)'#13, [FieldName, Result]);
end;

Добавим в обработчик события OnClick кнопки «Запрос» код , выделенный жирным шрифтом:

procedure TGoodsFlowForm.btnQueryClick(Sender: TObject);
var
  s_filter, s_having, s_orderby: string;
begin
  Screen.Cursor := crHourGlass; //изменяем курсор мыши на "часы"
  try
    qryReport.Close;
    {Собираем строку по всем трем фильтрам}
    s_filter := GetFilterString(clbKind, qryGoodsKind, 'g.goods_kind') +
                GetFilterString(clbMark, qryGoodsMark, 'g.goods_mark') +
                GetFilterString(clbStock, qryStock, 'gf.acc_id');
    if cbNotZero.Checked then //не показывать нулевые количества
      s_having :=  'having sum(gf.q) <> 0 or sum(gf.qd) <> 0 or sum(gf.qc) <> 0'#13
    else
      s_having := '';

    case cbOrder.ItemIndex of
    1: s_orderby := '3,2,4';
    else
      s_orderby := '2,3,4';
    end;

    qryReport.SQL.Text := Format(SQL_GOODS_FLOW_REPORT,
                          [s_filter, s_having, s_orderby]);

    // ShowMessage(qryReport.SQL.Text); //отладочный вывод
    qryReport.ParamByName('DATE1').AsDateTime := DateEdit1.Date;
    qryReport.ParamByName('DATE2').AsDateTime := DateEdit2.Date;
    qryReport.Open;
  finally
    Screen.Cursor := crDefault; //восстанваливаем курсор мыши
  end;
end;

Запустим проект. Снимем птичку с пункта «Все виды товаров» и установим птички на конкретные виды. Аналогично установим фильтр на несколько марок. Установим птичку «Не показывать нулевые количества» и поменяем порядок сортировки на « Марка, вид, артикул». Нажмем кнопку «Запрос »:

В принципе наш отчет готов (осталось реализовать лишь экспорт в Excel).

Можно подключить отчет к Главному меню Allegro, даже не выходя полностью из режима «Дизайнера». Важно лишь, чтобы главное окно было в растянутом положении.

Для подключения отчета к Главному меню вызовем окно «Команды » через меню Инструменты/Команды и создадим там новый пункт под названием «Отчет о движении товаров». Выберем какую-нибудь картинку формата BMP 16x16 и установим остальные поля диалога:

Выйдем из дизайнера и попробуем вызвать наш отчет через главное меню. Запустим отчет и вызовем окно SQL-монитора . Установим в отчете фильтрацию и посмотрим, как выглядят тексты отрабатываемых SQL-запросов в SQL-мониторе. Например, мы запросили 1 вид товара и три марки , требуя ненулевые количества:

select
  gf.object_id,
  gk.name goods_kind_name,
  gm.name goods_mark_name,
  g.article,
  sum(gf.a) a, sum(gf.q) q, sum(gf.ad) ad, sum(gf.ac) ac,
  sum(gf.qd) qd, sum(gf.qc) qc,
  g.price_w, g.price_r
from
  goods_flow(:DATE1, :DATE2) gf,
  goods g,
  goods_kind gk,
  goods_mark gm
where
  gf.object_id = g.id and
  g.goods_kind = gk.id and
  g.goods_mark = gm.id
  and g.goods_kind in (8)
  and g.goods_mark in (25,26,27)
group by
  gf.object_id, gk.name, gm.name, g.article, g.price_w, g.price_r
having sum(gf.q) <> 0 or sum(gf.qd) <> 0 or sum(gf.qc) <> 0
order by 2,3,4