unit journalscripts;

interface

uses Classes, cgiDM,  SysUtils, DB;

type

  { TSQLScripts48 }

  TSQLScripts48 = class
    private
      StartDate, EndDate: String;
      gid, cid, year: integer;
      isEmpty: boolean;
      isError: boolean;
      Data: TNIDBDM;

      PeopleInfPageCount        : integer;
      MissingListenersPageCount : integer;
      SheduleTermsPageCount     : integer;
      PerfomancePageCount       : integer;
      fScript: TStrings;
      procedure RunScript(State: string);
      procedure CreateEmptyTemplate(TableName: string; RowCount: integer);
      procedure CreateEmptyCrossTab(RowTable, ColTable: string; CrossTable: String);
      procedure CreateTmpAllCourses;
      procedure CreateListCoursesPages;
      procedure CreateMainTemplTable;
      procedure CreatePeoplesOnPages;
      procedure CreatePeoplesList;
      procedure CreateTemplGrades;
      procedure CreateLeftTable;
      procedure CreateRightTable;
      procedure CreateHomeWork;
      procedure CreatePeopleInf;
      procedure CreateMissingTable;
      procedure CreateTerms;
      procedure CreateSheduleTerms;
      procedure CreatePerfomanceTable;
      procedure CratePeoplesForDO;
      procedure CreateEmptyReport;
      procedure CreateEmptycontents(emptyRows: integer);
      procedure CreateContetnts;
      procedure CreateHealthSheet;
      function CheckGroupShedule: boolean;
      procedure CreateColsRows;
      procedure ClearScript;
      procedure CreateJournalMonitoring;
    private
      sqlTimeSeconds: integer;
      step: integer;

      procedure AddSeconds(Sender: TObject);
      procedure AddToLogFile(Str: string);
    public
      OneGroup, TwoGroup, ThreeGroup, FourGroup,
      PageStart, PageContinue: string;

      constructor Create(AData: TNIDBDM; yearId, agid, acid: integer; Empty: boolean);
      destructor Destroy; override;

      function CreateReport:boolean;
      function GetSQL(ScriptNum: integer):string;
  end;

const
  EmptyContentsRowsCount = 35; //Количество строк в пустом оглавлении

implementation
uses
    extTypes;
{ SQLScripts48 }



procedure TSQLScripts48.AddSeconds(Sender: TObject);
begin
  Inc(sqlTimeSeconds);
end;



function TSQLScripts48.CheckGroupShedule: boolean;
var
  SQL: string;
begin
  SQL := 'SELECT COUNT(*) FROM nnz_schedule WHERE gid = ' + inttostr(gid);
  Result := not (Data.QueryValue(SQL) = '0');
end;



procedure TSQLScripts48.ClearScript;
begin
end;

procedure TSQLScripts48.CratePeoplesForDO;
var
  SQL: string;
begin
  //Создать 42 человека для ДО
  SQL :=
         ' DROP TABLE IF EXISTS tmp_report_journal_DO_peoples_t; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_DO_peoples_t AS '+
         '  SELECT DISTINCT row_number() over (order by p.fio) AS num, p.mid, p.fio '+
         ' FROM tmp_report_main_peoples p '+
         ' ORDER BY p.fio; '+
         ' DROP TABLE IF EXISTS tmp_report_journal_DO_peoples; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_DO_peoples AS '+
         ' SELECT e.e_num num, t.mid, t.fio '+
         ' FROM tmp_report_empty_people e '+
         ' LEFT JOIN tmp_report_journal_DO_peoples_t t ON t.num = e.e_num;';
   fScript.Add(SQL);
end;

constructor TSQLScripts48.Create(AData: TNIDBDM; yearId, agid, acid: integer;
  Empty: boolean);
begin
  Data := AData;
  self.StartDate := TNIDBDM.DateAsSQL(Data.QueryDateValue('SELECT sy.BegDate FROM school_year sy WHERE sy.xp_key = ' + IntToStr(yearId)));
  self.EndDate   := TNIDBDM.DateAsSQL(Data.QueryDateValue('SELECT sy.EndDate FROM school_year sy WHERE sy.xp_key = ' + IntToStr(yearId)));
  self.gid := agid;
  self.cid := acid;
  self.isEmpty := Empty;
  isError := False;
  self.year := yearId;
  fScript := TStringList.Create;
  step := 0;
end;


function TSQLScripts48.CreateReport: boolean;
begin
  if OneGroup = '' then OneGroup := '26';
  if TwoGroup = '' then TwoGroup := '12';
  if ThreeGroup = '' then ThreeGroup := '8';
  if FourGroup = '' then FourGroup := '6';
  if PageStart = '' then PageStart := '1';

  isError := True;
  if not(isEmpty) then
  begin

      //Создать пустой шаблон размером 42 x 26
      CreateEmptyTemplate('people',42);
      CreateEmptyTemplate('lessons',26);
      CreateEmptyCrossTab('people','lessons','grades');
      CreateEmptyTemplate('people_half',21);
      //Получить список всех занятий по датам с предметами за период
      CreateTmpAllCourses;
      //Создать список занятий с датами по страницам
      CreateListCoursesPages;
      //Сформировать основной шаблон
      CreateMainTemplTable;
      //Сформировать учеников по периодам на каждую страницу
      CreatePeoplesOnPages;
      CreatePeoplesList;
      //Соединяем учеников и добавляем оценки
      CreateTemplGrades;
      //Формирование списка учеников
      CreateLeftTable;
      //Формирование таблицы для вывода оценок
      CreateRightTable;
      //Добавить домашнее задание
      CreateHomeWork;
      //Добавить информацию об учениках

      CreatePeopleInf;
      //Создать таблицу с пропусками занятий
      CreateMissingTable;
      //Создать выборку по периодам для текущей группы
      CreateTerms;
      //Формирование сводной ведомости учета посещаемости
      CreateSheduleTerms;
      //Заполняем сводную ведомость успеваемости обучающихся по периодам
      CreatePerfomanceTable;
      //Создать 42 человека для ДО
      CratePeoplesForDO;
      //Сформировать оглавление
      CreateContetnts;
      //Заполнить листок здоровья
      CreateHealthSheet;
      //Заполенить таблицу мониторинга журнала
      CreateJournalMonitoring;

  end else
  begin
    CreateEmptyTemplate('people',42);
    CreateEmptyTemplate('lessons',26);
    CreateEmptyCrossTab('people','lessons','grades');

    CreateEmptyReport;
    //Создаёт пустое оглавление с указанным количеством строк
    CreateEmptycontents(EmptyContentsRowsCount);
    CreateTerms;
    CreateSheduleTerms;
  end;
  CreateColsRows;
  RunScript('Завершение');
  isError := False;
  Result := not isError;
end;

procedure TSQLScripts48.CreateColsRows;
var
  SQL: string;
begin
  SQL :=
    'DROP TABLE IF EXISTS tmp_journal_grades_cols; '+
    'CREATE TEMPORARY TABLE tmp_journal_grades_cols AS  '+
    'select PageNum,cid,PrintDate, date from tmp_report_journal_Right group by PageNum,cid,PrintDate,date; '+
    'DROP TABLE IF EXISTS tmp_journal_grades_rows; '+
    'CREATE TEMPORARY TABLE tmp_journal_grades_rows AS  '+
    'SELECT PageNum,cid,p_num,fio,deduction from tmp_report_journal_Right where fio<>'''' group by PageNum,cid,p_num,fio,deduction; ';
  fScript.Add(SQL);
  SQL :=
    'DROP TABLE IF EXISTS tmp_journal_1st_row; '+
    'CREATE TEMPORARY TABLE tmp_journal_1st_row AS '+
    'SELECT c.PageNum,c.cid,c.PrintDate,c.date '+
    'FROM tmp_journal_grades_cols c '+
    'WHERE not exists (select 1 from tmp_report_journal_Right d where d.pagenum=c.pagenum and d.cid=c.cid and d.printdate=c.printdate and d.p_num=1);' +
    'INSERT INTO  tmp_report_journal_Right(PageNum,cid,PrintDate,date,p_num, grade) '+
    'SELECT PageNum,cid,PrintDate,date,1,null FROM  tmp_journal_1st_row; ';
  fScript.Add(SQL);
end;

procedure TSQLScripts48.CreateContetnts;
var
  SQL: string;
  ContPage: string;
  PageShift: array[0..6] of integer;
  i: integer;
begin
  PageShift[0] := 0;
  PageShift[1] := PeopleInfPageCount;
  PageShift[2] := MissingListenersPageCount;
  PageShift[3] := SheduleTermsPageCount;
  PageShift[4] := PerfomancePageCount;
  PageShift[5] := 2;
  PageShift[6] := 1;
  for i :=1 to 6 do
    PageShift[i] := PageShift[i]+PageShift[i-1];
  //Формирование оглавления по LMS-5328
  SQL := ' DROP TABLE IF EXISTS tmp_contents_t; ' +
         ' CREATE TEMPORARY TABLE tmp_contents_t AS ' +
         ' SELECT COUNT( DISTINCT PageNum) num, cs.CID, COALESCE(co.ord,200) ord ' +
         ' FROM tmp_report_journal_dates cs' +
         ' JOIN courses c ON c.CID = cs.CID' +
         ' LEFT JOIN (SELECT DISTINCT cid, ord FROM tmp_report_journal_Left) co ON co.cid = c.cid ' +
         ' GROUP BY cs.CID ' +
         ' ORDER BY ord, cid, PageNum; '+

         ' DROP TABLE IF EXISTS tmp_contents_t2; ' +
         ' CREATE TEMPORARY TABLE tmp_contents_t2 AS ' +
         '  SELECT sum(num) over(order by ord,cid,num) as num, null as summ, CID' +
         '  FROM tmp_contents_t' +
         '  ORDER BY ord, cid, num;';

         if (PageContinue = '') or (PageContinue = '0') then
           ContPage:='(SELECT MAX(summ) FROM tmp_contents_t2) '
         else
           ContPage := PageContinue;
    SQL := SQL +
         ' DROP TABLE IF EXISTS tmp_contents;  ' +
         ' CREATE TEMPORARY TABLE tmp_contents AS ' +
         '  (SELECT concat(upper(left(xp_Subject_Without_Year(c.Title),1)),substr(xp_Subject_Without_Year(c.Title),2))  name, cnt.num ' +
         '  FROM tmp_contents_t2 cnt' +
         '  JOIN courses c ON c.CID = cnt.CID' +
         {'  LEFT JOIN tmp_courses_2 t2 ON t2.CID = cnt.CID' +
         '  LEFT JOIN tmp_courses_order co ON (UPPER(co.name) LIKE (UPPER(xp_Subject_Without_Year(c.Title))))' +
         '  ORDER BY COALESCE(co.order_num,200) + IF(cntGid = 1, 0, 100)) '+ }

         //' LEFT JOIN (SELECT DISTINCT cid, ord FROM tmp_courses_3) t3 ON t3.cid = cnt.cid'+
         ' ORDER BY num, summ) '+

         ' UNION (SELECT ''Общие сведения об обучающихся'' as name, '+ContPage+' as num)' +
         ' UNION (SELECT ''Сведения о количестве уроков, пропущенных обучающимися'' as name, '+ContPage+' + ' +inttostr(PageShift[1]) + ' as num)' +
         ' UNION (SELECT ''Сводная ведомость учета посещаемости'' as name,'+ContPage+' + ' +inttostr(PageShift[2]) + ' as num)' +
         ' UNION (SELECT ''Сводная ведомость учета успеваемости'' as name, '+ContPage+' + ' +inttostr(PageShift[3]) + ' as num)' +
         ' UNION (SELECT ''Сведения о занятиях и кружках, в факультативах и других дополнительных занятиях'' as name, '+ContPage+' + ' +inttostr(PageShift[4]) + ' as num)' +
         ' UNION (SELECT ''Замечания по ведению классного журнала'' as name, '+ContPage+' + ' +inttostr(PageShift[5]) +' as num)' +
         ' UNION (SELECT ''Листок здоровья'' as name, '+ContPage+' + ' +inttostr(PageShift[6]) +' as num); ';

  fScript.Add(SQL);
end;

procedure TSQLScripts48.CreateRightTable;
var
  SQL: string;
begin
  //Формирование таблицы для вывода оценок
  SQL :=
         ' DROP TABLE IF EXISTS tmp_report_journal_Right;'+
         ' CREATE TEMPORARY TABLE tmp_report_journal_Right AS '+
         ' SELECT DISTINCT CONCAT(p.cid,''00'',p.PageNum,''00'',p.p_num) + 0 AS ConStr, '+
         '        p.cid, p.PageNum, p.date, p.grade, p.p_grade orderStr, p.sheid, '+
         '        IF(p.sheid > 10000000,xp_fget_RM_term(p.sheid - 10000000),'+
         ' to_char(p.date, ''dd.mm'')) PrintDate,  '+
         ' CONCAT(LPAD(CAST(p.p_num AS char(2)), 2,''0''), COALESCE(p.fio,'''')) fio, '+
         ' p.p_num, CAST('''' as CHAR(100)) deduction '+
         ' FROM tmp_report_journal_main_templ_with_peoples p '+
         ' ORDER BY p.cid, p.PageNum, ISNULL(p.date), p.date;'+
         ' CREATE INDEX PAGECID ON tmp_report_journal_Right(PageNum,cid); ';
  fScript.Add(SQL);
  SQL := ' CREATE INDEX index_right_cid_pn_num ON tmp_report_journal_Right(cid, PageNum, p_num); '+
         ' CREATE INDEX index_peop_cid_pn_num ON tmp_report_journal_main_templ_with_peoples (cid, PageNum, p_num); ';
  fScript.Add(SQL);
  SQL := ' UPDATE tmp_report_journal_Right l '+
         //' JOIN tmp_report_journal_main_templ_with_peoples p ON p.cid = l.cid and p.PageNum = l.PageNum AND p.p_num = l.p_num '+
         //' SET l.deduction = p.deduction; ';
         ' SET l.deduction = (SELECT p.deduction FROM tmp_report_journal_main_templ_with_peoples p WHERE p.cid = l.cid and p.PageNum = l.PageNum AND p.p_num = l.p_num LIMIT 1);';
  fScript.Add(SQL);
  SQL := ' UPDATE tmp_report_journal_Right SET deduction = '''' WHERE deduction is NULL; ';
  fScript.Add(SQL);
end;

procedure TSQLScripts48.CreateSheduleTerms;
var
  SQL: string;
begin
  //Формирование сводной ведомости учета посещаемости
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_SheduleTerms_terms; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_SheduleTerms_terms AS '+
         ' SELECT * FROM tmp_report_journal_terms; '+

         ' INSERT INTO tmp_report_journal_SheduleTerms_terms '+
         ' SELECT MIN(BegDate), MAX(EndDate), ''За год'', 0'+
         ' FROM tmp_report_journal_terms; '+

         ' DROP TABLE IF EXISTS tmp_report_journal_terms_t2; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_terms_t2 AS '+
         ' SELECT emp.e_num, t.trmid, p.mid '+
         ' FROM tmp_report_empty_people emp '+
         ' LEFT JOIN tmp_report_journal_SheduleTerms_terms t ON 1=1 '+
         ' LEFT JOIN tmp_report_page_missing_peoples p ON p.num = emp.e_num; '+

         ' DROP TABLE IF EXISTS tmp_report_journal_terms_t3; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_terms_t3 AS '+
         ' SELECT * FROM tmp_report_journal_SheduleTerms_terms; '+

         ' DROP TABLE IF EXISTS tmp_report_journal_terms_t4; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_terms_t4 AS '+
         ' SELECT * FROM tmp_report_journal_SheduleTerms_terms; '+

         ' DROP TABLE IF EXISTS tmp_report_journal_full_date_missing; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_full_date_missing AS '+
         ' SELECT c.mid, c.date, SUM(IF(c.ConMissing = m.ConMissing,1,0)) AS ConMissing, SUM(IF(c.ConMissing = m.ConDisease,1,0)) ConDisease '+
         ' FROM tmp_report_journal_missing m '+
         '  JOIN tmp_report_page_peoples_dates_courses c ON '+
         '   c.mid = m.mid AND c.date = m.date AND c.ConMissing = m.ConMissing '+
         ' WHERE c.ConMissing = m.ConMissing OR c.ConMissing = m.ConDisease '+
         '  GROUP BY c.mid, c.date; '+

         ' DROP TABLE IF EXISTS tmp_report_journal_terms_main_t; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_terms_main_t AS '+
         ' SELECT t.e_num, t.trmid, t.mid, xp_f_get_mid_fio(t.mid,0) fio, '+
         ' ter.title AS title, a.CntMissing, ter.BegDate, a.CntDisease,   '+
         ' b.CntMissingFullDay, b.CntDiseaseFullDay '+
         ' FROM tmp_report_journal_terms_t2 t '+
         //Только пропуски
         ' LEFT JOIN (SELECT mis.MID, tt.trmid, SUM(mis.ConMissing) CntMissing, SUM(mis.ConDisease) CntDisease '+
         ' FROM tmp_report_journal_missing mis '+
         ' JOIN tmp_report_journal_SheduleTerms_terms tt ON mis.date BETWEEN tt.BegDate AND tt.EndDate '+
         ' WHERE NOT mid IS NULL '+
         ' GROUP BY mis.MID, tt.trmid) a ON a.MID = t.MID AND a.trmid = t.trmid '+
         //FullDay
         ' LEFT JOIN (SELECT mis.MID, tt2.trmid, SUM(mis.ConMissing) CntMissingFullDay, SUM(mis.ConDisease) CntDiseaseFullDay '+
         ' FROM tmp_report_journal_full_date_missing mis '+
         ' JOIN tmp_report_journal_terms_t4 tt2 ON mis.date BETWEEN tt2.BegDate AND tt2.EndDate '+
         ' WHERE NOT mid IS NULL '+
         ' GROUP BY mis.MID, tt2.trmid) b ON b.MID = t.MID AND b.trmid = t.trmid '+
         ' LEFT JOIN tmp_report_journal_terms_t3 ter ON ter.trmid = t.trmid; ';
  fScript.Add(SQL);
  SheduleTermsPageCount := 1 ;
end;




procedure TSQLScripts48.CreateEmptycontents(emptyRows: integer);
var
  i: integer;
  SQL: string;
begin
  SQL :=  ' DROP TABLE IF EXISTS tmp_contents;  ' +
         ' CREATE TEMPORARY TABLE tmp_contents AS ' +
         ' SELECT '''' as name, 0 as num; ';
  fScript.Add(SQL);
  SQL := ' INSERT INTO tmp_contents '+
         ' SELECT '''' as name, 0 as num; ';
  for I := 0 to emptyRows - 1 do
    begin
    fScript.Add(SQL);
    end;
end;

procedure TSQLScripts48.CreateEmptyCrossTab(RowTable, ColTable: string;
  CrossTable: String);
var
  SQL: string;
begin
  SQL := format(
    'DROP TABLE IF EXISTS tmp_report_empty_%0:s; '+
    'CREATE TEMPORARY TABLE tmp_report_empty_%0:s (p_num integer, p_grade integer); ',
    [CrossTable]);
  fScript.Add(SQL);
  SQL := format(
    'INSERT INTO tmp_report_empty_%0:s (p_num,p_grade) '+
    'SELECT row.e_num,col.e_num FROM tmp_report_empty_%1:s col,tmp_report_empty_%2:s row ; ',
    [CrossTable,ColTable,RowTable]);
  fScript.Add(SQL);
end;

procedure TSQLScripts48.CreateEmptyReport;
var
  SQL: string;
begin
  CreateEmptyTemplate('pages',65);
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_Left; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_Left AS '+
         ' SELECT '+
         ' 0 AS ConStr, '+
         ' 0 + 0 AS fio, '+
         ' 0 AS OrderNum, '+
         ' e_num + 0 AS PageNum, '+
         ' 0 AS cid, '+
         ' ''0'' AS CourseName, '+
         ' 0 AS subGid,'+
         ' ''0'' AS mounthNames, '+
         ' 200 as ord, '+
         ' 0 AS deduction'+
         ' FROM tmp_report_empty_pages; ';
  fScript.Add(SQL);
  SQL := ' DROP TABLE IF EXISTS tmp_monitoring_L; '+
         ' CREATE TEMPORARY TABLE tmp_monitoring_L AS '+
         ' SELECT 0 as e_num, 0 as jmid, 0 as MounthDate, 0 as checkText, 0 as IsChecked ';
  fScript.Add(SQL);
  SQL := ' DROP TABLE IF EXISTS tmp_monitoring_R; '+
         ' CREATE TEMPORARY TABLE tmp_monitoring_R AS '+
         ' SELECT 0 as e_num, 0 as jmid, 0 as MounthDate, 0 as checkText, 0 as IsChecked ';
  fScript.Add(SQL);
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_Right; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_Right AS '+
         ' SELECT '+
         ' t.e_num AS PageNum, '+
         ' 0 AS ConStr, '+
         ' p_num AS fio, '+
         ' p_num AS OrderNum, '+
         //' p_num AS PrintDate, '+
         ' '' '' AS PrintDate, '+
         ' 0 AS date, '+
         ' '' '' AS grade, '+
         ' 0 AS orderStr, '+
         ' 0 AS sheid, '+
         ' 0 AS cid, '+
         ' ''0'' AS CourseName, '+
         ' 0 AS subGid, '+
         ' ''0'' AS mounthNames, '+
         ' p_num, '+
         ' '''' AS deduction ' +
         ' FROM tmp_report_empty_grades '+
         ' LEFT JOIN tmp_report_empty_pages t ON 1=1 ';
  fScript.Add(SQL);
  // Общие сведения об обучающихся
  CreateEmptyTemplate('people',42);
  SQL :=  ' DROP TABLE IF EXISTS tmp_report_page_peoples; '+
          ' CREATE TEMPORARY TABLE tmp_report_page_peoples AS '+
          ' SELECT DISTINCT '+
          ' e_num / 21 + 1 as ordPage, '+
          ' e_num % 21 + 1 as ordNum, '+
          ' e_num as num, '+
          ' '' '' personalNum, '+
          ' 0 AS mid, '' '' AS fio, '' '' AS Sex, '' '' AS Birthday,  '+
          ' '' '' as FamilyFIO, '' '' as FamilyWork, '' '' as HomeAdr  '+
          ' FROM tmp_report_empty_people p; ';
  fScript.Add(SQL);
  // Сведения о количестве пропущенных уроков
  CreateEmptyCrossTab('people','pages','main');
  CreateEmptyTemplate('terms',4);
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_missing; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_missing AS '+
         ' SELECT DISTINCT '+
         ' t.e_num + 0 AS ordPage, '+
         //' 1 AS ordPage, '+
         ' p_grade AS ordNum, '+
         ' 0 AS mid, '+
         ' ''0'' AS PrintDate1, '+
         ' p_num AS date, '+
         ' '' '' AS ConMissing, '+
         ' 0 AS ConDisease '+
         //' p_grade AS fio, '+
         //' p_num AS PrintDate, '+
         ''+
         ' FROM tmp_report_empty_grades '+
         ' LEFT JOIN tmp_report_empty_terms t ON 1=1 ';
         //' FROM tmp_report_empty_65 t;  ';
  fScript.Add(SQL);
  // Сводная ведомость успеваемости
  SQL := ' DROP TABLE IF EXISTS tmp_report_perfomance_t; '+
         ' CREATE TEMPORARY TABLE tmp_report_perfomance_t AS '+
         ' SELECT ''I четверть'' AS title, 1 AS Ord '+
         '  UNION SELECT ''II четверть'' AS title, 2 AS Ord '+
         '  UNION SELECT ''III четверть'' AS title, 3 AS Ord '+
         '  UNION SELECT ''IV четверть'' AS title, 4 AS Ord '+
         '  UNION SELECT ''Год. оценка'' AS title, 5 AS Ord '+
         '  UNION SELECT ''Экз. оценка'' AS title, 6 AS Ord '+
         '  UNION SELECT ''Итог. оценка'' AS title, 7 AS Ord ; ';
  fScript.Add(SQL);
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_perfomance_peoples; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_perfomance_peoples AS '+
         ' SELECT 0 AS mid, 0 as page; ';
  fScript.Add(SQL);
  CreateEmptyTemplate('periods',6);
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_perfomance_main_t; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_perfomance_main_t AS '+
         ' SELECT * '+
         ' FROM tmp_report_empty_periods '+
         ' LEFT JOIN tmp_report_perfomance_t ON 1=1; ';
  fScript.Add(SQL);
  CreateEmptyTemplate('courses',18);
  CreateEmptyTemplate('perf_pages',7);
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_perfomance_main; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_perfomance_main AS '+
         ' SELECT DISTINCT '+
         ' '' '' AS fio, '+
         ' m.title as termname, '+
         //' m.e_num AS OrderNum, '+
         ' 6 * (t.e_num  - 1) + m.e_num  AS OrderNum, '+
         ' m.Ord AS TermOrder, '+
         ' m.Ord AS trmtid, '+
         ' m.Ord AS trmid, '+
         ' m.e_num as mid, '+
         //' t.e_num + 0 AS page, '+
         ' t.e_num AS page, '+
         ' 0 AS PeopPage, '+
         ' 0 as cid, '+
         //' n.e_num AS CourseName, '+
         ' n.e_num AS CourseName, '+
         ' 0 AS BegDate, '+
         ' '' '' AS Grade, '+
         ' 0 AS OrderEnd, '+
         ' 0 AS councilDecisionMode, '+
         ' 0 as toYear, '+
         ' '''' as toText, '+
         ' '''' as protocolNumber, '+
         ' '''' as prikazNumber, '+
         ' CAST(NULL AS date) protocolDate, '+
         ' CAST(NULL AS date) prikazDate, '+
         ' 7 as midCount '+
         ' FROM tmp_report_journal_perfomance_main_t m'+
         ' LEFT JOIN tmp_report_empty_perf_pages t ON 1=1 '+
         ' LEFT JOIN tmp_report_empty_courses n ON 1=1 ';
  fScript.Add(SQL);
  SQL := ' DROP TABLE IF EXISTS tmp_report_page_missing_peoples; '+
         ' CREATE TEMPORARY TABLE tmp_report_page_missing_peoples AS '+
         ' SELECT DISTINCT 1 num, 0 mid; ';
  fScript.Add(SQL);
  CreateEmptyTemplate('lessons',26);

  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_homeWork_3;'+
         ' CREATE TEMPORARY TABLE tmp_report_journal_homeWork_3 AS '+
         ' SELECT 0 AS ConStr2, '' '' as date, '' '' as text, '' '' as TemaPlanText, 0 as tMain, '+
         '  CONCAT(t.e_num * 1000,0) + 0 AS ConStrPage, '+
         '  '' '' teacherFIO, '+
         '  '' '' SUBteacherFIO, 0 mainGid, 0 as SHEID,null as PrintDate, 0 as part '+
         ' FROM tmp_report_empty_lessons '+
         ' LEFT JOIN tmp_report_empty_pages t ON 1=1; '+

         ' DROP TABLE IF EXISTS tmp_report_journal_homeWork_3_group_counter;'+
         ' CREATE TEMPORARY TABLE tmp_report_journal_homeWork_3_group_counter AS '+
         ' SELECT 0 as ConStrPage, 0 as cn ';
  fScript.Add(SQL);
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_homeWork_4;'+
         ' CREATE TEMPORARY TABLE tmp_report_journal_homeWork_4 AS '+
         ' SELECT 0 AS ConStr2, '' '' as date, '' '' as text, '' '' as TemaPlanText, 0 as tMain, '+
         '  1000 AS ConStrPage, '+
         '  '' '' teacherFIO, '+
         '  '' '' SUBteacherFIO, 0 mainGid, 0 as SHEID, null as PrintDate,0 as part '+
         ' FROM tmp_report_empty_lessons t; '+

         ' DROP TABLE IF EXISTS tmp_report_journal_homeWork_4_group_counter;'+
         ' CREATE TEMPORARY TABLE tmp_report_journal_homeWork_4_group_counter AS '+
         ' SELECT 0 as ConStrPage, 0 as cn ';
  fScript.Add(SQL);
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_DO_peoples; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_DO_peoples AS '+
         '  SELECT e_num num, 0 as mid, '' '' as fio '+
         ' FROM tmp_report_empty_people; ';
  fScript.Add(SQL);
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_health_sheet;'+
         ' CREATE TEMPORARY TABLE tmp_report_journal_health_sheet AS '+
         ' SELECT DISTINCT e_num num, 0 as mid, '''' as fio, '''' as yearBirthday, '+
         '                 '''' as healthGroupTitle, '''' physicalTitle, '''' desk_number, '''' doctors_recommendation '+
         ' FROM tmp_report_empty_people; ';
  fScript.Add(SQL);
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_terms_main_t; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_terms_main_t AS '+
         ' SELECT 0 as e_num, 0 as trmid, 0 as mid, '''' as fio, '+
         ' '''' AS title, 0 as CntMissing, '''' as BegDate, '''' as CntDisease,   '+
         ' 0 as CntMissingFullDay, 0 as CntDiseaseFullDay; ';
  fScript.Add(SQL);
  SQL := ' DROP TABLE IF EXISTS tmp_report_page_peoples_dates_courses; '+
         ' CREATE TEMPORARY TABLE tmp_report_page_peoples_dates_courses AS '+
         ' SELECT DISTINCT 0 AS mid, '''' AS date, 0 as ConMissing, 0 AS num; ';
  fScript.Add(SQL);
end;


procedure TSQLScripts48.CreateEmptyTemplate(TableName: string;
  RowCount: integer);
var
  SQL: string;
  i: integer;
begin
  SQL := format(
    'DROP TABLE IF EXISTS tmp_report_empty_%0:s; '+
    'CREATE TEMPORARY TABLE tmp_report_empty_%0:s (e_num integer); ',
    [TableName]);
  fScript.Add(SQL);
  for i := 1 to RowCount do
    fScript.Add(format('INSERT INTO tmp_report_empty_%0:s (e_num) VALUES (%1:d); ',[TableName,i]));
end;

procedure TSQLScripts48.CreateHealthSheet;
var
  SQL: string;
begin
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_health_sheet_t;'+
         ' CREATE TEMPORARY TABLE tmp_report_journal_health_sheet_t AS '+
         ' SELECT DISTINCT p.mid, p.fio, year(xpf.Birthday) yearBirthday, '+
         '                 hg.title as healthGroupTitle, '+
         '                 pg.title as physicalTitle, xpf.desk_number, '+
         '                 xpf.doctors_recommendation '+
         ' FROM tmp_report_journal_peoples_pages p '+
         ' LEFT JOIN xp_personal_file xpf '+
         '   ON xpf.mid = p.mid'+
         ' LEFT JOIN HealthGroup hg '+
         '   ON hg.HID = xpf.HealthGroup '+
         ' LEFT JOIN physicalgroup pg '+
         '   ON pg.pid = xpf.PhysicalGroup '+
         ' ORDER BY p.fio; ';
  fScript.Add(SQL);
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_health_sheet;'+
         ' CREATE TEMPORARY TABLE tmp_report_journal_health_sheet AS '+
         ' SELECT a.* '+
         ' FROM tmp_report_empty_people e '+
         ' LEFT JOIN (SELECT row_number() over (order by p.fio) AS num, p.* FROM tmp_report_journal_health_sheet_t p) a '+
         '   ON e.e_num = a.num;';
  fScript.Add(SQL);
  runscript('Информация о здоровье');
end;

procedure TSQLScripts48.CreateHomeWork;
var
  SQL: string;
begin
  //Добавить домашнее задание
  SQL :=
         ' DROP TABLE IF EXISTS tmp_report_journal_homeWork_t;'+
         ' CREATE TEMPORARY TABLE tmp_report_journal_homeWork_t AS '+
         ' SELECT DISTINCT dp.PageNum, dp.cid, dp.date, dp.teacher_mid MainTeacher, '+
         '                 CONCAT(dp.cid,''00'',dp.PageNum,''001'') + 0 AS ConStr1, '+
         '                 CONCAT(dp.cid,''00'',dp.PageNum,''002'') + 0 AS ConStr2, '+
         '                 hw.teacher_mid, hw.text, ROW_NUMBER() OVER (ORDER BY dp.cid, dp.PageNum, ISNULL(dp.date),dp.date) as num, '+
         '                 CONCAT(DAY(dp.date), ''.'', MONTH(dp.date)) PrintDate '+
         ' FROM tmp_report_journal_main_templ_with_peoples dp '+
         ' LEFT JOIN nnz_homework hw ON hw.sheid = dp.sheid AND hw.week_id = dp.week_id '+
         ' ORDER BY dp.cid, dp.PageNum, ISNULL(dp.date),dp.date;';
  fScript.Add(SQL);
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_homeWork_t2;'+
         ' CREATE TEMPORARY TABLE tmp_report_journal_homeWork_t2 AS '+
         ' SELECT DISTINCT emp.e_num, t.constr1, t.constr2 '+
         ' FROM tmp_report_empty_people_half emp '+
         ' LEFT JOIN tmp_report_journal_homeWork_t t ON 1=1; ';
  fScript.Add(SQL);
  SQL := ' CREATE INDEX cons ON tmp_report_journal_homeWork_t(constr1,constr2); '+
         ' DROP TABLE IF EXISTS tmp_report_journal_homeWork;'+
         ' CREATE TEMPORARY TABLE tmp_report_journal_homeWork AS '+
         ' SELECT emp.e_num, emp.constr1, emp.constr2, ht.PageNum, ht.cid, ht.date, '+
         '        ht.teacher_mid, ht.text, ht.num, ht.PrintDate, ht.MainTeacher '+
         ' FROM tmp_report_journal_homeWork_t2 emp '+
         ' LEFT JOIN tmp_report_journal_homeWork_t ht ON ht.num = emp.e_num AND ht.constr1 = emp.constr1 AND ht.constr2 = emp.constr2;';
  fScript.Add(SQL);
  SQL := 'DROP TABLE IF EXISTS tmp_report_stat_courses_2;'+
         'CREATE TEMPORARY TABLE tmp_report_stat_courses_2 AS '+
         'SELECT * FROM tmp_report_stat_courses; ';
  fScript.Add(SQL);
  SQL := 'CREATE INDEX templSheid ON tmp_report_journal_main_templ(sheid, week_id);';
  fScript.Add(SQL);
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_homeWork_3;'+
         ' CREATE TEMPORARY TABLE tmp_report_journal_homeWork_3 AS '+
         'SELECT DISTINCT CONCAT(dp.cid,''00'',dp.PageNum,''002'') + 0 AS ConStr2, dp.date, '+
         ' to_char(dp.date, ''dd.mm'') as PrintDate,'+
         'replace(hw.text,''\r\n'','''') text, replace(o.title,''\r\n'','''') TemaPlanText, dp.teacher_mid tMain, '+
         ' CONCAT(dp.PageNum * 1000,dp.cid) + 0 AS ConStrPage, '+
         ' c.teacher_mid teacherFIO, '+
         ' xp_f_get_mid_fio(dp.subTeacher, 1) SUBteacherFIO, '+
         ' dp.mainGid, dp.cid, '+
         ' case '+
         ' when dp.week_id=9999998 then 10000000-dp.sheid '+
         ' when dp.week_id=9999999 then -1000000 '+
         ' else (SELECT period FROM nnz_schedule WHERE SHEID = dp.sheid) '+
         ' end as part, '+
         ' dp.sheid, dp.plan, dp.fact '+
         //'   FROM tmp_report_journal_main_templ_with_peoples dp '+
         ' FROM ( SELECT t.cid, t.PageNum, sc.sheid, sc.date, sc.mainGid, sc.week_Id, '+
         ' case when sc.week_id  in (9999998,9999999) then null else sc.teacher_mid end as teacher_mid, '+
         ' case when sc.week_id  in (9999998,9999999) then null else sc.subTeacher end as subTeacher, '+
         ' case when sc.week_id  in (9999998,9999999) then null else sc.plan end as plan, '+
         ' case when sc.week_id  in (9999998,9999999) then null else sc.fact end as fact '+

         '        FROM ( '+
         '               SELECT DISTINCT cid, PageNum, multySHEID, date '+
         '                 FROM tmp_report_journal_main_templ_with_peoples '+
         '                WHERE NOT date IS NULL '+
         //'                AND SUBSTRING_INDEX(SUBSTRING_INDEX(mainGid, '','', 1), '','', -1) <> 0 '+
         '            ) t '+
         '        LEFT JOIN tmp_report_stat_courses sc ON t.cid = sc.cid AND sc.date = t.date AND FIND_IN_SET(sc.SHEID ,t.multySHEID) <> 0 ' + //AND sc.sheid = t.multySHEID '+
         '      ) dp '+
         '   LEFT JOIN nnz_homework hw ON hw.sheid = dp.sheid AND hw.week_id = dp.week_id '+
         '  LEFT JOIN nnz_schedule_modules sm '+
         '       ON (sm.SHEID = dp.SHEID '+
         '       AND sm.week_id = dp.week_id) '+
         '     LEFT OUTER JOIN organizations o  '+
         '       ON (o.oid = sm.module_id)  '+
         ' JOIN (SELECT string_agg(DISTINCT xp_f_get_mid_fio(css.teacher_mid,0),'', '') as teacher_mid, t.cid,t.PageNum, css.mainGid '+
         ' FROM tmp_report_journal_main_templ t '+
         ' JOIN tmp_report_stat_courses_2 css ON css.cid = t.cid and css.week_id = t.week_id '+
         ' GROUP BY t.cid, t.PageNum, css.mainGid)  c ON c.cid = dp.cid AND c.PageNum = dp.PageNum AND c.mainGid = SUBSTRING_INDEX(SUBSTRING_INDEX(dp.mainGid, '','', 1), '','', -1) '+
         ' WHERE NOT  dp.date is NULL '+
         //'  AND dp.SHEID < 10000000 '+
         ' Order by dp.mainGid,  dp.date, part, dp.sheid; '+

         ' DROP TABLE IF EXISTS tmp_report_journal_homeWork_3_group_counter; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_homeWork_3_group_counter AS '+
         ' SELECT ConStrPage, COUNT(DISTINCT mainGid) cn '+
         ' FROM tmp_report_journal_homeWork_3 '+
         ' GROUP BY ConStrPage; ';
  fScript.Add(SQL);
  SQL := format(
    'UPDATE  tmp_report_journal_homeWork_3 t SET '+
    ' sheid = 10000000-sheid, '+
    ' PrintDate= case part when -1000000 then elt(abs(sheid) - 10000000,''Год'',''Экз'',''Итог'') else  xp_fget_RM_term(-part) end, '+
    ' plan=('+
    'select count(*) from nnz_schedule s '+
    '  join nnz_weeks w on w.sh_var_id=s.sh_var_id '+
    '    and s.day_of_week-1 between WeekDay(w.wstart_date) and WeekDay(w.wend_date) '+
    '    and w.wstart_date + (s.day_of_week - 1 -WeekDay(w.wstart_date)) * interval ''1 day'' not in (select date from holidays) '+
    '  join nnz_schedule_variants v on v.sh_var_id=s.sh_var_id '+
    '  join term_weeks tw on tw.week_id=w.week_id '+
    '  join terms t on t.trmid=tw.trmid and t.year=v.s_year_id '+
    '  join courses c on c.cid=s.cid '+
    '  join groupname g on g.gid=s.gid '+
    '  left join groupname g2 on g2.gid=g.owner_gid '+
    '  join group_history gh on gh.gid=coalesce(g2.gid,g.gid) and gh.school_year=t.year and gh.term_type_id=t.term_type_id '+
    ' where case part when -1000000 then t.year=%0:s else t.trmid=-part end '+
    '  and c.cid=t.cid and g.gid=MainGid '+
    ' ), '+
    ' fact=('+
    'select count(*) from nnz_schedule s '+
    '  join nnz_weeks w on w.sh_var_id=s.sh_var_id '+
    '    and s.day_of_week-1 between WeekDay(w.wstart_date) and WeekDay(w.wend_date) '+
    '    and  w.wstart_date + (s.day_of_week - 1 -WeekDay(w.wstart_date)) * interval ''1 day'' not in (select date from holidays) '+
    ' join nnz_sh_module_fact f on f.sheid=s.sheid and f.week_id=w.week_id '+
    '  join nnz_schedule_variants v on v.sh_var_id=s.sh_var_id '+
    '  join term_weeks tw on tw.week_id=w.week_id '+
    '  join terms t on t.trmid=tw.trmid and t.year=v.s_year_id '+
    '  join courses c on c.cid=s.cid '+
    '  join groupname g on g.gid=s.gid '+
    '  left join groupname g2 on g2.gid=g.owner_gid '+
    '  join group_history gh on gh.gid=coalesce(g2.gid,g.gid) and gh.school_year=t.year and gh.term_type_id=t.term_type_id '+
    ' where case part when -1000000 then t.year=%0:s else t.trmid=-part end and c.cid=t.cid and g.gid=MainGid '+
    ') '+
    'WHERE part<0; ',
    [self.year]);
  fScript.Add(SQL);
  SQL :=
    'UPDATE  tmp_report_journal_homeWork_3 SET '+
    ' TemaPlanText=concat(''Дано по плану - '', plan,'', по факту - '', fact ) '+
    'WHERE part<0; ';
  fScript.Add(SQL);
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_homeWork_4;'+
         ' CREATE TEMPORARY TABLE tmp_report_journal_homeWork_4 AS '+
         'SELECT DISTINCT CONCAT(dp.cid,''00'',dp.PageNum,''001'') + 0 AS ConStr1, dp.date, '+
         ' to_char(dp.date, ''dd.mm'') as PrintDate,'+
         'replace(hw.text,''\r\n'','''') text, replace(o.title,''\r\n'','''') TemaPlanText, dp.teacher_mid tMain, '+
         ' CONCAT(dp.PageNum * 1000,dp.cid) + 0 AS ConStrPage, '+
         ' c.teacher_mid teacherFIO, '+
         ' xp_f_get_mid_fio(dp.subTeacher, 1) SUBteacherFIO, '+
         ' dp.mainGid, dp.cid, '+
         ' case '+
         ' when dp.week_id=9999999 then -1000000 '+
         ' when dp.week_id=9999998 then 10000000-dp.sheid '+
         ' else (SELECT period FROM nnz_schedule WHERE SHEID = dp.sheid) '+
         ' end as part, '+
         ' dp.sheid, dp.plan, dp.fact '+
         //'   FROM tmp_report_journal_main_templ_with_peoples dp '+
         ' FROM ( SELECT t.cid, t.PageNum, sc.sheid, sc.date, sc.mainGid, sc.week_Id, '+
         ' case when sc.week_id in (9999998,9999999) then null else sc.teacher_mid end as teacher_mid, '+
         ' case when sc.week_id in (9999998,9999999) then null else sc.subTeacher end as subTeacher, '+
         ' case when sc.week_id in (9999998,9999999) then null else sc.plan end as plan, '+
         ' case when sc.week_id in (9999998,9999999) then null else sc.fact end as fact '+
         '        FROM ( '+
         '               SELECT DISTINCT cid, PageNum, multySHEID, date '+
         '                 FROM tmp_report_journal_main_templ_with_peoples '+
         '                WHERE NOT date IS NULL '+
         //'                AND SUBSTRING_INDEX(SUBSTRING_INDEX(mainGid, '','', 1), '','', -1) <> 0 '+
         '            ) t '+
         '        LEFT JOIN tmp_report_stat_courses sc ON t.cid = sc.cid AND sc.date = t.date AND FIND_IN_SET(sc.SHEID ,t.multySHEID) <> 0 ' + // AND sc.sheid = t.multySHEID '+
         '      ) dp '+
         '   LEFT JOIN nnz_homework hw ON hw.sheid = dp.sheid AND hw.week_id = dp.week_id '+
         '  LEFT JOIN nnz_schedule_modules sm '+
         '       ON (sm.SHEID = dp.SHEID '+
         '       AND sm.week_id = dp.week_id) '+
         '     LEFT OUTER JOIN organizations o  '+
         '       ON (o.oid = sm.module_id)  '+
         ' JOIN (SELECT string_agg(DISTINCT xp_f_get_mid_fio(css.teacher_mid,0),'', '') as teacher_mid, t.cid,t.PageNum, css.mainGid '+
         ' FROM tmp_report_journal_main_templ t '+
         ' JOIN tmp_report_stat_courses_2 css ON css.cid = t.cid and css.week_id = t.week_id '+
         ' GROUP BY t.cid, t.PageNum, css.mainGid)  c ON c.cid = dp.cid AND c.PageNum = dp.PageNum AND c.mainGid = SUBSTRING_INDEX(SUBSTRING_INDEX(dp.mainGid, '','', 1), '','', -1) '+
         ' WHERE NOT  dp.date is NULL and dp.PageNum = 1 AND dp.cid = (SELECT cid FROM tmp_report_journal_Left ORDER BY ord, PageNum, cid, OrderNum LIMIT 1) '+
         //'  AND dp.SHEID < 10000000 '+
         ' Order by dp.mainGid,  dp.date, part, dp.sheid; '+

         ' DROP TABLE IF EXISTS tmp_report_journal_homeWork_4_group_counter;'+
         ' CREATE TEMPORARY TABLE tmp_report_journal_homeWork_4_group_counter AS '+
         ' SELECT COUNT(DISTINCT mainGid) cn '+
         ' FROM tmp_report_journal_homeWork_4; ';
  fScript.Add(SQL);
  SQL := format(
    'UPDATE  tmp_report_journal_homeWork_4 t SET '+
    ' sheid = 10000000-sheid, '+
    ' PrintDate= case part when -1000000 then elt(abs(sheid) - 10000000,''Год'',''Экз'',''Итог'') else  xp_fget_RM_term(-part) end, '+
    ' plan=('+
    'select count(*) from nnz_schedule s '+
    '  join nnz_weeks w on w.sh_var_id=s.sh_var_id '+
    '    and s.day_of_week-1 between WeekDay(w.wstart_date) and WeekDay(w.wend_date) '+
    '    and  w.wstart_date + (s.day_of_week - 1 -WeekDay(w.wstart_date)) * interval ''1 day'' not in (select date from holidays) '+
    '  join nnz_schedule_variants v on v.sh_var_id=s.sh_var_id '+
    '  join term_weeks tw on tw.week_id=w.week_id '+
    '  join terms t on t.trmid=tw.trmid and t.year=v.s_year_id '+
    '  join courses c on c.cid=s.cid '+
    '  join groupname g on g.gid=s.gid '+
    '  left join groupname g2 on g2.gid=g.owner_gid '+
    '  join group_history gh on gh.gid=coalesce(g2.gid,g.gid) and gh.school_year=t.year and gh.term_type_id=t.term_type_id '+
    ' where case part when -1000000 then t.year=%0:s else t.trmid=-part end '+
    '  and c.cid=t.cid and g.gid=MainGid '+
    ' ), '+
    ' fact=('+
    'select count(*) from nnz_schedule s '+
    '  join nnz_weeks w on w.sh_var_id=s.sh_var_id '+
    '   and s.day_of_week-1 between WeekDay(w.wstart_date) and WeekDay(w.wend_date) '+
    '    and  w.wstart_date + (s.day_of_week - 1 -WeekDay(w.wstart_date)) * interval ''1 day'' not in (select date from holidays) '+
    ' join nnz_sh_module_fact f on f.sheid=s.sheid and f.week_id=w.week_id '+
    '  join nnz_schedule_variants v on v.sh_var_id=s.sh_var_id '+
    '  join term_weeks tw on tw.week_id=w.week_id '+
    '  join terms t on t.trmid=tw.trmid and t.year=v.s_year_id '+
    '  join courses c on c.cid=s.cid '+
    '  join groupname g on g.gid=s.gid '+
    '  left join groupname g2 on g2.gid=g.owner_gid '+
    '  join group_history gh on gh.gid=coalesce(g2.gid,g.gid) and gh.school_year=t.year and gh.term_type_id=t.term_type_id '+
    ' where case part when -1000000 then t.year=%0:s else t.trmid=-part end '+
    '  and c.cid=t.cid and g.gid=MainGid '+
    ') '+
    'WHERE part<0; ',
    [self.year]);
  fScript.Add(SQL);
  SQL :=
    'UPDATE  tmp_report_journal_homeWork_4 SET '+
    ' TemaPlanText=concat(''Дано по плану - '', plan,'', по факту - '', fact ) '+
    'WHERE part<0; ';
  fScript.Add(SQL);
  runscript('Домашняя работа');
end;

procedure TSQLScripts48.CreateJournalMonitoring;
var
  SQL: string;
  MonCount, RowsCout: integer;
  CorrectionNumber: integer;
begin
  //Опреедить количество замечаний по журналу

  MonCount := StrToInt(
    Data.QueryValue(
      Format(('SELECT COUNT(*) FROM journal_monitoring J WHERE J.gid = %0:s AND J.CheckDate BETWEEN %1:s AND %2:s; '),
              [gid, StartDate, EndDate])
      )
  );

//  if (MonCount Mod 44) = 0 then
//    CorrectionNumber := 1
//  else
//    CorrectionNumber := 1;

  CorrectionNumber := 1;

  RowsCout := ((MonCount Div 44) + CorrectionNumber) * 44;

  CreateEmptyTemplate('monitoring', RowsCout);
  CreateEmptyTemplate('monitoring_L', Round(RowsCout/2));
  CreateEmptyTemplate('monitoring_R', Round(RowsCout/2));

  SQL :=
         ' DROP TABLE IF EXISTS tmp_monitoring_main; '+
         ' CREATE TEMPORARY TABLE tmp_monitoring_main AS '+
         ' SELECT num,jmid,  MounthDate,checkText,IsChecked, '+
         ' CASE WHEN(100*num / 44 %% 100 +1) >50 THEN ''R'' ELSE ''L'' END AS table_part '+
         'FROM ( '+
         'SELECT ROW_NUMBER() OVER (ORDER BY J.CheckDate, J.CheckCid)  as num, J.jmid, '+
         ' CAST(concat( CASE MONTH(J.CheckDate) WHEN  1 THEN ''январь'' WHEN  2 THEN ''февраль'' '+
         ' WHEN  3 THEN ''март'' WHEN  4 THEN ''апрель'' WHEN  5 THEN ''май'' '+
         ' WHEN  6 THEN ''июнь'' WHEN  7 THEN ''июль'' WHEN  8 THEN ''август'' '+
         ' WHEN  9 THEN ''сентябрь'' WHEN 10 THEN ''октябрь'' WHEN 11 THEN ''ноябрь'' ELSE ''декабрь'' END, '' / '', DAY(J.CheckDate)) AS CHAR(50)) AS MounthDate, '+
         ' CONCAT(C.Name, '', '', J.CheckText) as checkText, '+
         ' xp_f_get_mid_fio(J.ExecMid, 1) AS IsChecked '+
         'FROM out_journal_monitoring J '+
         'LEFT JOIN out_courses C ON C.xp_key = J.CheckCid '+
         'WHERE J.gid = 77 AND J.CheckDate BETWEEN %1:s AND %2:s '+
         'ORDER BY  J.CheckDate, J.CheckCid '+
         ') t; ';
  SQL := Format(SQL, [gid, StartDate, EndDate]);
  fScript.Add(SQL);

  SQL := ' DROP TABLE IF EXISTS tmp_monitoring; '+
         ' CREATE TEMPORARY TABLE tmp_monitoring AS '+
         ' SELECT t.e_num, m.jmid, m.MounthDate, m.checkText, m.IsChecked, m.table_part, '+
         ' ROW_NUMBER() OVER ( PARTITION BY table_part ORDER BY m.table_part, m.MounthDate_ as table_num '+
         ' FROM tmp_report_empty_monitoring t '+
         ' LEFT JOIN tmp_monitoring_main m ON m.num = t.e_num; ';
  fScript.Add(SQL);

  SQL := ' DROP TABLE IF EXISTS tmp_monitoring_L; '+
         ' CREATE TEMPORARY TABLE tmp_monitoring_L AS '+
         ' SELECT t.e_num, m.jmid, m.MounthDate, m.checkText, m.IsChecked '+
         ' FROM tmp_report_empty_monitoring_L t '+
         ' LEFT JOIN tmp_monitoring m ON m.table_num = t.e_num AND m.table_part = ''L''; ';
  fScript.Add(SQL);

  SQL := ' DROP TABLE IF EXISTS tmp_monitoring_R; '+
         ' CREATE TEMPORARY TABLE tmp_monitoring_R AS '+
         ' SELECT t.e_num, m.jmid, m.MounthDate, m.checkText, m.IsChecked '+
         ' FROM tmp_report_empty_monitoring_R t '+
         ' LEFT JOIN tmp_monitoring m ON m.table_num = t.e_num AND m.table_part = ''R''; ';
  fScript.Add(SQL);

end;

procedure TSQLScripts48.CreateLeftTable;
var
  SQL: string;
begin
  SQL := ' CREATE INDEX PAGECID ON tmp_report_journal_main_templ_with_peoples(fio,p_num,PageNum,cid,subGid,date, SHEID); '+
         ' CREATE INDEX CONAME ON tmp_courses_order(name); '+
         ' CREATE INDEX t2CID ON tmp_courses_2(cid); '+
         ' CREATE INDEX stcid ON tmp_report_stat_courses(cid,title); ';
  fScript.Add(SQL);
  SQL := ' DROP TABLE IF EXISTS tmp_report_stat_courses_tmp;'+
         ' CREATE TEMPORARY TABLE tmp_report_stat_courses_tmp AS  '+
         ' SELECT DISTINCT cid, title FROM tmp_report_stat_courses; '+
         ' CREATE INDEX statCcid ON tmp_report_stat_courses_tmp(cid); ';
  fScript.Add(SQL);

  //Формирование списка учеников
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_Left;'+
         ' CREATE TEMPORARY TABLE tmp_report_journal_Left AS '+
         ' SELECT DISTINCT '+
         '    CONCAT(p.cid,''00'',p.PageNum,''00'',p.p_num) + 0 AS ConStr, '+
         //' p.fio, '+
         '    CONCAT(LPAD(CAST(p.p_num AS char(2)), 2,''0''), COALESCE(p.fio,'''')) fio, '+
         //'    CONCAT(LPAD(CAST(p.p_num AS char(2)), 2,''0''), COALESCE(p.fio,''''),'' <font color=red>'', COALESCE(p.deduction, ''''),''</font>'') fio, '+
         '    p.p_num OrderNum, p.PageNum, p.cid, xp_Subject_Without_Year(cu.title) as CourseName, p.subGid,  '+
         '    CAST('' '' AS char(255)) as  mounthNames, '+
         //'    200 ord '+
         //'    COALESCE(co.order_num,200) ord '+
         '    COALESCE(co.ord,200) ord '+
         ' FROM tmp_report_journal_main_templ_with_peoples p '+
         //' LEFT JOIN tmp_report_stat_courses_tmp a ON a.cid = p.cid '+
         ' LEFT JOIN courses cu ON cu.cid = p.cid '+
         ' LEFT JOIN  (SELECT DISTINCT cid, ord FROM tmp_courses_3) co ON co.cid = p.cid '+
         //' LEFT JOIN tmp_courses_order co ON UPPER(co.name) LIKE (UPPER(xp_Subject_Without_Year(cu.Title))) '+
         //' LEFT JOIN tmp_courses_2 t2 ON t2.CID = cu.CID '+
         ' GROUP BY p.fio, p.p_num, p.PageNum, p.cid, p.subGid ; ';
  fScript.Add(SQL);
  SQL := ' UPDATE tmp_report_journal_Left '+
         ' SET mounthNames = ( SELECT string_agg(DISTINCT LOWER(if (p.SHEID < 10000000, getConst(CONCAT(''Month.'',MONTH(p.date))),NULL)), '', ''  ORDER BY p.date) '+
         ' FROM tmp_report_journal_main_templ_with_peoples p WHERE CONCAT(p.cid,''00'',p.PageNum,''00'',p.p_num) + 0 = tmp_report_journal_Left.ConStr) ;';

  SQL := ' UPDATE tmp_report_journal_Left l '+
         ' JOIN ( SELECT string_agg(DISTINCT LOWER(if (p.SHEID < 10000000, getConst(CONCAT(''Month.'',MONTH(p.date))),NULL)), '', '' ORDER BY p.date) dates, '+
         '        CONCAT(p.cid,''00'',p.PageNum,''00'',p.p_num) + 0 ConStr '+
         '        FROM tmp_report_journal_main_templ_with_peoples p  '+
         '        GROUP BY p.cid,p.PageNum,p.p_num '+
         '       ) b ON b.ConStr = l.ConStr '+
         ' SET mounthNames = b.dates;';
  fScript.Add(SQL);
  SQL := ' CREATE INDEX PAGECID ON tmp_report_journal_Left(PageNum,cid); ';
  fScript.Add(SQL);
  runscript('Формирование левой части');
end;

procedure TSQLScripts48.CreateListCoursesPages;
var
  SQL: string;
begin
  //Тут сортировка по LMS-5328
  SQL := ' DROP TABLE IF EXISTS tmp_courses_order;' +
         ' CREATE TEMPORARY TABLE tmp_courses_order' +
         ' ( order_num int,' +
         ' name varchar(255) );';
  fScript.Add(SQL);
  fScript.Add(
          ' INSERT INTO tmp_courses_order VALUES '+
          '(1,''Русский язык''),'+
          '(2,''Литература''),'+
          '(3,''Иностранный язык''),'+
          '(3,''Английский язык''),'+
          '(3,''Французский язык''),'+
          '(3,''Китайский язык''),'+
          '(3,''Немецкий язык''),'+
          '(3,''Английский''),'+
          '(3,''Французский''),'+
          '(3,''Китайский''),'+
          '(3,''Немецкий''),'+
          '(4,''Математика''),'+
          '(5,''Алгебра''),'+
          '(6,''Геометрия''),'+
          '(7,''Информатика''),'+
          '(8,''История России''),'+
          '(9,''Всеобщая история''),'+
          '(10,''Обществознание''),'+
          '(11,''География''),'+
          '(12,''Физика''),'+
          '(13,''Химия''),'+
          '(14,''Биология''),'+
          '(15,''Музыка''),'+
          '(16,''Изобразительное искусств''),'+
          '(17,''Технология''),'+
          '(18,''Основы безопасности жизнедеятельности''),'+
          '(19,''Физическая культура'');');
  fScript.Add(
          ' DROP TABLE IF EXISTS tmp_courses_2;' +
          ' CREATE TEMPORARY TABLE tmp_courses_2 AS' +
          ' SELECT CID, COUNT(DISTINCT mainGid) cntGid' +
          ' FROM tmp_report_stat_courses' +
          ' WHERE mainGid <> 0 '+
          ' GROUP BY CID;');
  fScript.Add(
          ' DROP TABLE IF EXISTS tmp_courses_3; '+
          ' CREATE TEMPORARY TABLE tmp_courses_3 AS' +
          ' SELECT cs.date, cs.SHEID, cs.week_id, cs.teacher_mid, '+
          '   cs.subTeacher, cs.mainGid, cs.CID,' +
          '   (COALESCE(co.order_num,200) + IF(cntGid = 1, 0, 100)) ord' +
          ' FROM tmp_report_stat_courses cs' +
          '   JOIN courses c ON c.CID = cs.CID' +
          '   LEFT JOIN tmp_courses_2 t2 ON t2.CID = cs.CID' +
          '   LEFT JOIN tmp_courses_order co ON UPPER(co.name) LIKE (UPPER(c.Title)) OR UPPER(co.name) LIKE (UPPER(cs.title));');
  fScript.Add( ' DROP TABLE IF EXISTS tmp_report_stat_courses_d; '+
               ' CREATE TEMPORARY TABLE tmp_report_stat_courses_d AS '+
               ' SELECT '+
               '   xp_part, '+
               '   MIN(SHEID) SHEID, MAX(SHEID) SHEID_MAX,'+
               '   title, '+
               '   CID,  '+
               '   array_agg(teacher_mid) teacher_mid,  '+
               '   week_id, '+
               '   string_agg(subTeacher, '','') subTeacher, '+
               '   date, '+
               '   array_agg(mainGid) mainGid, '+
               '   array_agg(SHEID) multySHEID '+
               '   FROM tmp_report_stat_courses tmp  '+
               ' GROUP BY xp_part, title, cid, week_id, date; ');
  //Создать список занятий с датами по страницам
  fScript.Add(

         ' DROP TABLE IF EXISTS tmp_report_journal_dates; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_dates AS '+
         ' SELECT @max_h := CASE t.cntGid WHEN 1 THEN '+OneGroup+' WHEN 2 THEN '+TwoGroup+' WHEN 3 THEN '+ThreeGroup+' WHEN 4 THEN '+FourGroup+' ELSE '+OneGroup+' END AS MaxH, '+
         '  @hw_count := (SELECT COUNT(*) FROM nnz_schedule sh JOIN nnz_weeks w ON (sh.sh_var_id = w.sh_var_id) WHERE '+
         '  (sh.gid = cs.mainGid OR sh.gid in (SELECT gid FROM groupname WHERE owner_gid = '+inttostr(gid)+')) AND sh.cid = cs.cid '+
         '  AND cs.xp_part = sh.period '+
         '  AND  w.wstart_date + (sh.day_of_week - 1 -WeekDay(w.wstart_date)) * interval ''1 day'' = cs.date '+
         '  ) as HwCount, '+
         '        CASE WHEN (NOT (@cid_t = cs.CID)) THEN @page_t:=1 '+
         '        ELSE (CASE WHEN ((@hw_i >= @max_h) AND (not(cs.date = @last_date) OR (t.cntGid = 1))) THEN @page_t:=@page_t+1 ELSE @page_t END) END AS PageNum, '+
         '        CASE WHEN ((@cid_t=cs.CID) AND ((@hw_i < @max_h) OR ((cs.date = @last_date) AND (t.cntGid <> 1)))) THEN @hw_i:=@hw_i+1 ELSE @hw_i:=0 END hw_num, '+
         '        CASE WHEN (@hw_i = 0) THEN @i:=1 ELSE @i:=@i+1 END num, '+
         '        @cid_t:=cs.CID as CID, '+
         '        @last_date := cs.date as m_date, '+
         '        cs.date, '+
         '        cs.SHEID,cs.SHEID_MAX, cs.week_id, cs.teacher_mid, cs.subTeacher, cs.mainGid, cs.multySHEID '+
         ' FROM (SELECT * FROM tmp_report_stat_courses_d ORDER BY cid,date) cs '+
         ' JOIN tmp_courses_2 t ON t.cid = cs.cid '+
         //' JOIN nnz_schedule sh ON sh.sheid = cs.sheid AND sh.st_state_id  <> 2 '+
         ' ORDER BY cid, date; ');
  RunScript('Список дисциплин');
end;

procedure TSQLScripts48.CreateMainTemplTable;
var
  SQL: string;
begin
  //Сформировать основной шаблон
  SQL := ' CREATE INDEX index_cid ON tmp_report_journal_dates(cid); '+
         ' DROP TABLE IF EXISTS tmp_report_journal_main_templ_t; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_main_templ_t AS '+
         ' SELECT m.p_num, m.p_grade, d.cid, d.PageNum '+
         ' FROM tmp_report_empty_grades m '+
         ' LEFT JOIN (SELECT DISTINCT CID, PageNum FROM tmp_report_journal_dates) d ON 1=1; ';
  fScript.Add(SQL);
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_main_templ; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_main_templ AS '+
         ' SELECT mt.p_num, mt.p_grade, mt.cid, mt.PageNum, '+
         '        d.date, d.SHEID,d.SHEID_MAX, d.week_id, d.teacher_mid, d.subTeacher, d.mainGid, d.multySHEID  '+
         ' FROM tmp_report_journal_main_templ_t mt '+
         ' LEFT JOIN tmp_report_journal_dates d ON d.cid = mt.cid AND d.PageNum = mt.PageNum AND d.num = mt.p_grade; ';
  fScript.Add(SQL);
  RunScript('Формирование сетки');
end;

procedure TSQLScripts48.CreateMissingTable;
var
  SQL: string;
begin
  //Создадим таблицу с пропусками занятий
  SQL := ' DROP TABLE IF EXISTS tmp_report_page_missing_peoples; '+
         ' CREATE TEMPORARY TABLE tmp_report_page_missing_peoples AS '+
         ' SELECT row_number() over (order by fio) as num, p.mid  '+
         ' FROM tmp_report_main_peoples p; ';
  fScript.Add(SQL);
  SQL := ' DROP TABLE IF EXISTS tmp_report_page_missing_peoples_dates; '+
         ' CREATE TEMPORARY TABLE tmp_report_page_missing_peoples_dates AS '+
         ' SELECT DISTINCT p.mid, p.date, COUNT(p.grade) ConMissing, SUM(if(sgv.isdisease <> 0,1,0)) ConDisease, mp.num '+
         ' FROM tmp_report_journal_main_templ_with_peoples p '+
         ' JOIN tmp_report_page_missing_peoples mp ON mp.mid = p.mid '+
         ' LEFT JOIN schedule_grades_values sgv ON xp_fget_not_grade(grade) LIKE CONCAT(''%'',TRIM(sgv.Value),''%'') '+// TRIM(sgv.Value) = Trim(grade) '+
         ' WHERE xp_fget_not_grade(grade) LIKE CONCAT(''%'',getConst(''n''),''%'') OR sgv.isdisease <> 0 '+
         ' GROUP BY p.mid, p.date, mp.num '+
         ' ORDER BY p.date; ';
  fScript.Add(SQL);
  SQL := ' DROP TABLE IF EXISTS tmp_report_page_peoples_dates_courses; '+
         ' CREATE TEMPORARY TABLE tmp_report_page_peoples_dates_courses AS '+
         ' SELECT DISTINCT p.mid, p.date, COUNT(*) ConMissing, mp.num '+
         ' FROM tmp_report_journal_main_templ_with_peoples p '+
         ' JOIN tmp_report_page_missing_peoples mp ON mp.mid = p.mid '+
         ' LEFT JOIN schedule_grades_values sgv ON TRIM(sgv.Value) = Trim(grade) '+
         ' GROUP BY p.mid, p.date, mp.num '+
         ' ORDER BY p.date; ';
  fScript.Add(SQL);
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_missing_dates; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_missing_dates AS '+
         ' SELECT DISTINCT date '+
         ' FROM tmp_report_journal_main_templ_with_peoples '+
         ' LEFT JOIN schedule_grades_values sgv ON xp_fget_not_grade(grade) LIKE CONCAT(''%'',TRIM(sgv.Value),''%'') '+// TRIM(sgv.Value) = Trim(grade) '+
         ' WHERE xp_fget_not_grade(grade) LIKE CONCAT(''%'',getConst(''n''),''%'') OR sgv.isdisease <> 0 '+
         ' ORDER BY date; ';
  fScript.Add(SQL);
  SQL := ' SET @i := 0;'+
         ' SET @page := 1;'+
         ' DROP TABLE IF EXISTS tmp_report_journal_missing_t1; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_missing_t1 AS '+
         ' SELECT DISTINCT IF(@i = 63, @page := @page + 1, @page) page, '+
         '      IF(@i = 63, @i := 1, @i := @i + 1 ) num,  date '+
         ' FROM tmp_report_journal_missing_dates  '+
         ' ORDER BY date; ';
  fScript.Add(SQL);
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_missing_t2; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_missing_t2 AS '+
         ' SELECT DISTINCT t.e_num, m.page, m.date FROM tmp_report_empty_people t '+
         ' LEFT JOIN tmp_report_journal_missing_t1 m ON 1=1;  ';
  fScript.Add(SQL);
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_missing; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_missing AS '+
         ' SELECT t2.e_num ordNum, t2.page ordPage, t2.date, p.mid, pd.ConMissing, pd.ConDisease '+
         ' FROM tmp_report_journal_missing_t2 t2 '+
         ' LEFT JOIN tmp_report_page_missing_peoples p ON p.num = t2.e_num '+
         ' LEFT JOIN tmp_report_page_missing_peoples_dates pd ON pd.num = t2.e_num AND pd.date = t2.date '+
         ' ORDER BY t2.date; ';
  fScript.Add(SQL);
  RunScript('Пропуски занятий');
  SQL := ' SELECT COUNT(DISTINCT page) FROM tmp_report_journal_missing_t1; ';
  MissingListenersPageCount := Data.QueryIntValue(SQL);

  if (Data.QueryValue('SELECT COUNT(date) FROM tmp_report_journal_missing') = '0') then
  begin
    SQL := ' UPDATE tmp_report_journal_missing '+
           ' SET date = ''1900-01-01'' , ordPage = 1; ';
           //' VALUES (0,''1900-01-01'',0,0)';
    fScript.Add(SQL);
    {SQL := ' UPDATE tmp_report_journal_missing '+
           ' SET ordPage = 1';
           //' VALUES (0,''1900-01-01'',0,0)';
    Data.ExecuteSQL(SQL);     }
  end;


end;

procedure TSQLScripts48.CreatePeopleInf;
var
  SQL: string;
begin
  //Добавить информацию об учениках
  SQL := '  SET @i := 0; SET @page := 1;'+
         ' DROP TABLE IF EXISTS tmp_report_page_peoples_t; '+
         ' CREATE TEMPORARY TABLE tmp_report_page_peoples_t AS '+
         '  SELECT DISTINCT '+
         '  IF(@i = 22, @page := 2, @page) page, '+
         '  IF(@i = 22, @i := 1, @i := @i + 1 ) num, '+
         '  CONCAT_WS(''-'',xp.Liter,xp.Number) personalNum, '+
         '  p.mid, fio, xp.Sex, xp.Birthday, fam.FamilyFIO,fam.FamilyWork, '+
         '  replace(CONCAT_WS('', '',xp.RegistrationAddress, COALESCE(xp.`Phone home`,xp.`Phone cell`)),''\r\n'','''') HomeAdr '+
         '  FROM tmp_report_journal_peoples_pages p '+
         '  LEFT JOIN xp_Personal_File xp ON xp.mid = p.mid '+
         ' LEFT JOIN ( SELECT mid, '+
          '  STRING_AGG(DISTINCT xp_format_fio(LastName,FirstName,Patronymic,0) , '',\r\n'') FamilyFIO, '+
          '  STRING_AGG(DISTINCT CONCAT_WS('', '',NULLIF(TRIM(WorkPlace),''''),Speciality,WorkPhone) , '',\r\n'') FamilyWork '+
          '  FROM  xp_personal_file_family '+
          '  WHERE LegalRepresentative = -1 '+
          '  GROUP BY mid '+
          ' )  fam ON fam.mid = p.mid '+
         '  ORDER BY fio; ';
  fScript.Add(SQL);
  {SQL := ' DROP TABLE IF EXISTS tmp_report_empty_21; '+
         ' CREATE TEMPORARY TABLE tmp_report_empty_21 '+
         ' SELECT e_num FROM tmp_report_empty_50 LIMIT 21; ';}

  SQL := ' DROP TABLE IF EXISTS tmp_report_page_peoples_t2; '+
         ' CREATE TEMPORARY TABLE tmp_report_page_peoples_t2 AS '+
         '  SELECT DISTINCT empt.e_num, p.page '+
         ' FROM tmp_report_empty_people_half empt '+
         ' LEFT JOIN tmp_report_page_peoples_t p ON 1=1; ';
  fScript.Add(SQL);
  SQL := ' DROP TABLE IF EXISTS tmp_report_page_peoples; '+
         ' CREATE TEMPORARY TABLE tmp_report_page_peoples AS '+
         ' SELECT t1.e_num ordNum, t1.page ordPage, t.*'+
         ' FROM tmp_report_page_peoples_t2 t1 '+
         ' LEFT JOIN tmp_report_page_peoples_t t ON t.num = t1.e_num AND t.page = t1.page; ';
   fScript.Add(SQL);
   RunScript('Список учащихся');
   SQL := ' SELECT COUNT(DISTINCT page) FROM tmp_report_page_peoples_t; ';
   PeopleInfPageCount := Data.QueryIntValue(SQL);
end;

procedure TSQLScripts48.CreatePeoplesList;
var
  SQL: string;
begin
  //Список всех MID
  SQL := ' DROP TABLE IF EXISTS tmp_report_main_peoples; '+
         ' CREATE TEMPORARY TABLE tmp_report_main_peoples AS '+
         '  SELECT DISTINCT mid, xp_f_get_mid_fio(mid,0) as fio FROM tmp_report_journal_peoples_pages '+
         '  ORDER BY xp_f_get_mid_fio(mid,0);';
  fScript.Add(SQL);
end;

procedure TSQLScripts48.CreatePeoplesOnPages;
var
  ds: TDataSet;
  SQL: string;
begin
  //Сформировать учеников по периодам на каждую страницу
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_peoples_pages; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_peoples_pages '+
         ' (mid int, fio varchar(255), cid int, PageNum int, OrderNum int, subGid int); ';
  fScript.Add(SQL);
  RunScript('Заполнение страниц');
  SQL := ' SELECT t2.cid, t2.PageNum, MIN(t2.date) stDate, MAX(t2.date) endDate '+
         ' FROM tmp_report_journal_main_templ t2 '+
         ' GROUP BY cid, PageNum ORDER BY cid, PageNum ';
  with Data.GetData(SQL) do
    try
      while not eof do
      begin
        SQL := Format(' CALL xp_fill_groupuser_period(%s, %s, %s); ',
                      [TNIDBDM.DateAsSQL(FieldByName('stDate').AsVariant),
                       TNIDBDM.DateAsSQL(FieldByName('endDate').AsVariant),
                       gid]);
        fScript.Add(SQL);
           {
        SQL :=  Format(

               ' SET @i := 0; SET @gid_t := 0; '+
               ' INSERT INTO tmp_report_journal_peoples_pages '+
               ' SELECT DISTINCT gp.mid, xp_f_get_mid_fio(gp.MID,0) AS fio, '+
               ' %s as cid, %s as PageNum, IF(gp.gid = @gid, @i := @i + 1, IF(@i = 0, @i := 1, @i := 22)) as OrderNum, '+
               ' (@gid := gp.gid) as subGid  '+
               ' FROM groupuser_period gp '+
               //' WHERE CASE WHEN (SELECT )(cid =  '+ FieldByName('cid').asString + ') ' +
               ' WHERE CASE WHEN (SELECT COUNT(*) FROM groupuser_period_t WHERE cid = '+FieldByName('cid').asString+') = 0 '+
               ' THEN cid = -1 ELSE cid = '+FieldByName('cid').asString+' END '+
               ' ORDER BY gp.gid; ',
               [FieldByName('cid').AsString,
               FieldByName('PageNum').AsString]);
        Data.ExecuteSQL(SQL);
                       }
        SQL := Format(
               ' DROP TABLE IF EXISTS groupuser_period_t; '+
               ' CREATE TEMPORARY TABLE groupuser_period_t AS '+
               ' SELECT * FROM groupuser_period; '+
               ' DROP TABLE IF EXISTS groupuser_period_t2; '+
               '   CREATE TEMPORARY TABLE groupuser_period_t2 AS '+
               '    SELECT DISTINCT gp.mid, xp_f_get_mid_fio(gp.MID,0) AS fio, '+
               '                    %s as cid, %s as PageNum, gp.gid '+
               ' FROM groupuser_period gp '+
               ' WHERE CASE WHEN (SELECT COUNT(*) FROM groupuser_period_t WHERE cid = '+FieldByName('cid').asString+') = 0 '+
               ' THEN cid = -1 ELSE cid = '+FieldByName('cid').asString+' END '+
               ' ORDER BY gp.gid; ',
               [FieldByName('cid').AsString,
               FieldByName('PageNum').AsString]);
        fScript.Add(SQL);
        SQL := ' SET @i := 0; SET @gid_t := 0; '+
               ' SET @def_count = 0; '+
               ' SET @gid_count = '+
               '  (SELECT CASE (SELECT COUNT(DISTINCT gid) FROM groupuser_period_t2) ' +
               '  WHEN 2 THEN 22 '+
               '  WHEN 3 THEN 15 '+
               '  WHEN 4 THEN 10 '+
               '  WHEN 5 THEN 8 '+
               '  ELSE 1 END); ' +
               ' INSERT INTO tmp_report_journal_peoples_pages '+
               ' SELECT mid, fio, cid, PageNum, '+
               ' IF(gid = @gid_t or (@gid_count = 1 AND @i > 0), @i := @i + 1, IF(@i = 0, @i := 1, @i := @def_count := @def_count + @gid_count)) as OrderNum, '+
               ' (@gid_t := gid) as subGid '+
               ' FROM groupuser_period_t2 '+
               ' ORDER BY subGid, fio; ';
        fScript.Add(SQL);
        Next;
      end;
    Finally
      Free;
    end;
  runscript('Заполнение страниц');
end;

procedure TSQLScripts48.CreatePerfomanceTable;
var
  SQL: string;
  tmpstr: string;
  i: integer;
begin
  //Заполняем сводную ведомость успеваемости обучающихся по периодам
  //-- Список учеников
  SQL := ' SET @i := 0; SET @page := 0; '+
         ' DROP TABLE IF EXISTS tmp_report_journal_perfomance_peoples_t; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_perfomance_peoples_t AS '+
         ' SELECT DISTINCT p.mid, CONCAT_WS('' '',p_t.LastName,p_t.FirstName,p_t.Patronymic) as fio '+ // p.fio '+
         ' FROM tmp_report_journal_peoples_pages p '+
         ' JOIN people p_t ON p_t.mid = p.mid '+
         ' ORDER BY p.fio; ';
  fScript.Add(SQL);

  SQL :=
   //-- Список периодов
         ' DROP TABLE IF EXISTS tmp_report_journal_perfomance_templates; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_perfomance_templates AS '+
         ' SELECT t.title, t.trmid, 1 AS TermOrder, t.BegDate, t.trmtid '+
         ' FROM tmp_report_journal_terms_periods t ORDER BY t.BegDate; ';
  fScript.Add(SQL);
     //begin for
         for i := 1 to 3 do
         begin
          case i of
            1 : tmpstr := 'Год. оценка';
            2 : tmpstr := 'Экз. оценка';
            3 : tmpstr := 'Итог. оценка';
          end;
         if Data.QueryValue(
           'SELECT IF(FIND_IN_SET('+inttostr(i)+',sy.year_grade_types) <> 0,1,0) FROM school_year sy '+
           'WHERE sy.xp_key = ' + inttostr(year)) = '1' then
           begin
           SQL :=
             ' INSERT INTO tmp_report_journal_perfomance_templates VALUES '+
             ' ('+TNIDBDM.StringAsSQL(tmpstr)+',0,'+IntToStr(i + 1)+',NULL,'+IntToStr(i)+'); ';
         //' (''Экзам. оценка'',0,3,NULL,2), (''Итоговая оценка'',0,4,NULL,3); '+
           fScript.Add(SQL);
           end;
         end;
     //end for
    SQL :=
           //' SET @colP = (SELECT TRUNCATE((42 / (SELECT COUNT(title) + 3 FROM tmp_report_journal_terms_periods)),0) a);'+
           ' SET @colP = (SELECT TRUNCATE((42 / (SELECT COUNT(*) FROM tmp_report_journal_perfomance_templates)),0) a);'+

           ' DROP TABLE IF EXISTS tmp_report_journal_perfomance_peoples; '+
           ' CREATE TEMPORARY TABLE tmp_report_journal_perfomance_peoples AS '+
           //' SELECT IF(@i = @colP, @page := @page + 1, @page) page, '+
           ' SELECT IF(@i mod @colP = 0, @page := @page + 1, @page) page, '+
           //'        IF(@i = @colP, @i := 1, @i := @i + 1 ) OrderNum, '+
           '        @i := @i + 1 OrderNum, '+
           '        t.mid, t.fio '+
           ' FROM tmp_report_journal_perfomance_peoples_t t '+
           ' ORDER BY t.fio; ';
    fScript.Add(SQL);
   //-- Список курсов
   SQL :=
         ' SET @page := 1; SET @i := 0;'+
         ' DROP TABLE IF EXISTS tmp_report_journal_perfomance_courses_t; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_perfomance_courses_t AS '+
         ' SELECT DISTINCT c.cid, c.title  '+
         ' FROM tmp_report_stat_courses c '+
         ' WHERE c.SHEID < 10000000 '+
         ' ORDER BY c.title; ';
  fScript.Add(SQL);
  SQL :=
         ' DROP TABLE IF EXISTS tmp_report_journal_perfomance_courses; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_perfomance_courses AS '+
         ' SELECT DISTINCT IF(@i = 18, @page := @page + 1, @page) page, '+
         '                 IF(@i = 18, @i := 1, @i := @i + 1 ) num, '+
         '                 t.cid, t.title  '+
         ' FROM tmp_report_journal_perfomance_courses_t t; ';
  fScript.Add(SQL);
  SQL :=
   //-- Все ученики за все периоды по всем курсам
         ' DROP TABLE IF EXISTS tmp_report_journal_perfomance_main; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_perfomance_main AS '+
         ' SELECT p.mid, p.fio, p.OrderNum, t.trmid, t.title TermName, '+
         ' t.TermOrder, t.trmtid, t.BegDate, c.cid, c.title CourseName, '+
         ' c.page, CAST(0 as CHAR(50)) AS Grade, '+
         ' CASE WHEN trmtid = 7 THEN 1 '+
         '      WHEN trmtid = 8 THEN 3 '+
         '      WHEN trmtid = 9 THEN 2 '+
         ' END AS OrderEnd, '+
         ' 0 as councilDecisionMode, '+
         ' 0 as toYear, '+
         ' CAST('''' AS char(255)) toText, '+
         ' CAST('''' AS char(255)) protocolNumber, CAST('''' AS char(255)) prikazNumber, '+
         ' CAST(NULL AS date) protocolDate, CAST(NULL AS date) prikazDate, '+
         ' 0 as midCount '+
         ' FROM tmp_report_journal_perfomance_peoples p '+
         ' LEFT JOIN tmp_report_journal_perfomance_templates t ON 1=1 '+
         ' LEFT JOIN tmp_report_journal_perfomance_courses c ON 1=1; ';
  fScript.Add(SQL);
  SQL :=
         ' UPDATE tmp_report_journal_perfomance_main m '+
         ' JOIN term_grades tg ON tg.MID = m.MID AND tg.CID = m.CID AND tg.trmid = m.trmid AND tg.trmtid = m.trmtid '+
         ' SET m.grade = COALESCE(TRIM(tg.grade),0) '+  // LMS-6299/3 Выводим все проставленные оценки
         ' /* WHERE '+
         ' NOT EXISTS( '+
         ' SELECT 1 '+
         '   FROM groupuser_transfer gt '+
         '   JOIN terms t ON to_date(t.term_begin, ''dd.mm.yyyy'') >=  gt.xp_date '+
         '   JOIN school_year sy ON gt.xp_date BETWEEN sy.BegDate AND sy.EndDate AND sy.xp_key = '+ inttostr(year) +
         '   WHERE gt.mid = m.MID '+
         '   AND t.trmid = m.trmid '+
         '   AND gt.group_from = '+inttostr(gid)+') */; ';
                {
         ' UPDATE tmp_report_journal_perfomance_main m '+
         ' JOIN term_grades tg ON tg.MID = m.MID AND tg.CID = m.CID AND tg.trmid = m.trmid AND tg.trmtid = 7 '+
         ' SET m.grade = TRIM(tg.grade) '+
         //' WHERE m.grade = 0; '+
                }
  fScript.Add(SQL);
  SQL :=
         ' UPDATE tmp_report_journal_perfomance_main m '+
         ' JOIN nnz_year_grades y ON y.CID = m.cid AND y.MID = m.MID AND y.s_year_id = '+inttostr(year)+' AND y.y_grtype_id = m.trmtid  '+
         ' SET m.grade = TRIM(y.grade) '+
         ' WHERE not m.TermOrder = 1; ';
          {
         ' UPDATE tmp_report_journal_perfomance_main m '+
         ' JOIN nnz_year_grades y ON y.CID = m.cid AND y.MID = m.MID AND y.s_year_id = '+year+' AND y.y_grtype_id = 2  '+
         ' SET m.grade = TRIM(y.grade) '+
         ' WHERE m.TermOrder = 2; '+

         ' UPDATE tmp_report_journal_perfomance_main m '+
         ' JOIN nnz_year_grades y ON y.CID = m.cid AND y.MID = m.MID AND y.s_year_id = '+year+' AND y.y_grtype_id = 3 '+
         ' SET m.grade = TRIM(y.grade) '+
         ' WHERE m.TermOrder = 3; ';
         }
  fScript.Add(SQL);
  //-- Решение педагогического совета по формуле LMS-5637
  SQL := ' SET @current_group = '+  inttostr(gid) + ';'+
         ' DROP TABLE IF EXISTS tmp_journal_council_decision; '+
         ' CREATE TEMPORARY TABLE tmp_journal_council_decision AS '+
         ' SELECT '+
         '   mid, stats.toYear, '+
         '   stats.group_to_text, '+
         '   IF((stats.fromYear <> 0 AND stats.toYear <> 0) or (stats.xp_status = 1),1,NULL) transfer_to_next_or_other_group, '+
         '   IF(stats.fromYear <> 0 AND stats.toYear <> 0 AND isConditionally <> 0, 2,NULL) AS transfer_conditionally, '+
         '   IF(group_from <> @current_group AND group_to = @current_group AND fromYear = toYear, 3, NULL) to_current_group_frome_one_year, '+
         '   IF(group_from = @current_group AND group_to <> @current_group AND fromYear = toYear, 4, NULL) retraining, '+
         '   IF(fromYear = 9 AND toYear = 10, 5, NULL) from_9_to_10, '+
         '   IF(fromYear = 11 AND toYear = 0, 6, NULL) from_11_to_end, '+
         '   IF(xp_status = 11,7,NULL) AS certificated_end, '+
         '   IF((stats.fromYear <> 0 AND stats.toYear = 0 AND stats.xp_status in(5,6,8)),8,NULL) transfer_deduction, '+
         '   protocol_number, '+
         '   protocol_date, '+
         '   prikaz_number, '+
         '   prikaz_date '+
         ' FROM '+
         ' ( '+
         '   SELECT ggt.mid, '+
         '          COALESCE(xp_fget_group_year(ggt.group_from,sy.xp_key),0) fromYear, '+
         '          COALESCE(xp_fget_group_year(ggt.group_to,sy.xp_key),0) toYear, '+
         '          ggt.group_from, '+
         '          ggt.group_to, '+
         '          ggt.group_to_text, '+
         '          ggt.xp_status, '+
         '          ggt.isConditionally, '+
         '          ggt.protocol_number, '+
         '          ggt.protocol_date, '+
         '          ggt.Prikaz prikaz_number, '+
         '          ggt.xp_date prikaz_date '+
         '   FROM groupuser_transfer ggt '+
         '   JOIN '+
         '   ( '+
         '     SELECT '+
         '     MAX(gt.xp_key) xp_key, gt.mid '+
          'FROM school_year sy '+
          '  JOIN groupuser_transfer gt ON (gt.group_from = @current_group OR gt.group_to = @current_group OR gt.xp_status = 1) '+
// переводы от началп первого уч. периода года до первого уч. периода следующего года
          '    and         gt.xp_date > coalesce((select min(to_date(term_begin,''dd.mm.yyyy'')) from terms t where t.year=sy.xp_key), sy.begdate) '+
          '    and         gt.xp_date < coalesce( (select min(to_date(term_begin,''dd.mm.yyyy'')) from terms t join school_year y on  y.xp_key=t.year where y.begdate>sy.enddate), sy.enddate) '+
          'WHERE sy.xp_key= '+ inttostr(year) +
         '     GROUP BY gt.mid '+
         '   ) addGt ON addGt.xp_key = ggt.xp_key '+
         '   JOIN school_year sy ON ggt.xp_date BETWEEN sy.BegDate AND sy.EndDate '+
         ' ) stats; ';
  fScript.Add(SQL);
  SQL :=
         'UPDATE tmp_report_journal_perfomance_main m '+
         ' JOIN tmp_journal_council_decision t ON m.mid = t.mid '+
         ' SET m.councilDecisionMode = COALESCE(t.transfer_deduction, '+
         '                                      t.transfer_to_next_or_other_group,  '+
         '                                      t.transfer_conditionally, '+
         '                                      t.to_current_group_frome_one_year, '+
         '                                      t.retraining, '+
         '                                      t.from_9_to_10, '+
         '                                      t.from_11_to_end, '+
         '                                      t.certificated_end, 0), '+
         '     m.toYear = t.toYear, '+
         '     m.toText = t.group_to_text, '+
         '     m.protocolNumber = t.protocol_number, '+
         '     m.protocolDate = t.protocol_date, '+
         '     m.prikazNumber = t.prikaz_number, '+
         '     m.prikazDate = t.prikaz_date; ';
  fScript.Add(SQL);
  SQL := {' DROP TABLE IF EXISTS tmp_report_journal_perfomance_main_t; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_perfomance_main_t '+
         ' SELECT COUNT(mid) cnt, mid FROM tmp_report_journal_perfomance_main '+
         ' GROUP BY mid; '+    }

         ' UPDATE tmp_report_journal_perfomance_main m '+
         //' JOIN tmp_report_journal_perfomance_main_t t ON m.mid = t.mid '+
         ' SET m.midCount = (SELECT COUNT(*) FROM tmp_report_journal_perfomance_templates); ';
  fScript.Add(SQL);
  RunScript('Сводная таблица');
  //SQL := ' SELECT COUNT(DISTINCT page)  FROM tmp_report_journal_perfomance_peoples; ';
  SQL := ' SELECT COUNT(DISTINCT page) * (SELECT MAX(page) FROM tmp_report_journal_perfomance_main)  FROM tmp_report_journal_perfomance_peoples; ';
  PerfomancePageCount := Data.QueryIntValue(SQL);
end;

procedure TSQLScripts48.CreateTemplGrades;
var
  SQL: string;
  i: integer;
begin
  //Соединяем учеников и добавляем оценки
  SQL :=  ' CREATE INDEX index_cid_pages ON tmp_report_journal_peoples_pages(cid); ';
  fScript.Add(SQL);
  SQL :=
         ' DROP TABLE IF EXISTS tmp_report_journal_main_templ_with_peoples; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_main_templ_with_peoples AS '+
         ' SELECT t.*, p.mid, p.fio, CAST('''' AS CHAR(20)) grade, p.subGid, CAST('''' AS char(100)) as deduction '+
         ' FROM tmp_report_journal_main_templ t '+
         ' LEFT JOIN tmp_report_journal_peoples_pages p ON p.cid = t.cid AND p.PageNum = t.PageNum AND p.OrderNum = t.p_num; ';
        // ' LEFT JOIN nnz_sh_grades nsg ON nsg.student_mid = p.mid AND nsg.week_id = t.week_id AND FIND_IN_SET(nsg.SHEID ,t.multySHEID) <> 0 ';// AND t.multySHEID LIKE CONCAT(''%'',nsg.SHEID,''%''); ';
         {' LEFT JOIN nnz_weeks w ON w.week_id=t.week_id '+
         ' LEFT JOIN nnz_schedule sh ON  sh.sh_var_id=w.sh_var_id AND sh.cid=t.cid AND sh.sheid between t.sheid and t.sheid_max AND t.multySHEID LIKE CONCAT(''%'',sh.SHEID,''%'') '+
         '   LEFT JOIN  nnz_sh_grades nsg ON  nsg.week_id = w.week_id AND nsg.sheid=sh.sheid  AND nsg.student_mid=p.mid;  ';}
  fScript.Add(SQL);
  SQL := '  CREATE INDEX index_main_tmpl_week_id ON tmp_report_journal_main_templ_with_peoples(mid,week_id); ';
  fScript.Add(SQL);
  SQL := ' UPDATE tmp_report_journal_main_templ_with_peoples p '+
         ' JOIN nnz_sh_grades nsg ON nsg.student_mid = p.mid AND nsg.week_id = p.week_id AND FIND_IN_SET(nsg.SHEID ,p.multySHEID) <> 0 '+
         ' SET p.grade = nsg.grade ; ';

  fScript.Add(SQL);
  SQL := ' UPDATE tmp_report_journal_main_templ_with_peoples p '+
         ' JOIN term_grades tg ON (tg.trmid + 10000000) = p.SHEID '+
         '                     AND tg.mid = p.mid AND tg.trmtid = 7 '+
         '                     AND tg.cid = p.cid '+
         '  SET p.grade = tg.grade; ';
  fScript.Add(SQL);

  SQL := '';
  for I := 1 to 3 do
  SQL := SQL + ' UPDATE tmp_report_journal_main_templ_with_peoples p '+
               ' JOIN nnz_year_grades yg ON (/*yg.s_year_id +*/ 2000000'+IntToStr(i)+') = p.SHEID '+
               '                     AND yg.mid = p.mid AND yg.y_grtype_id = ' +IntToStr(i)+
               '                     AND yg.cid = p.cid '+
               '                     AND yg.s_year_id = '+inttostr(year) +
               '  SET p.grade = yg.grade; ';
  fScript.Add(SQL);

  //Отчисление
  SQL := ' DROP TABLE IF EXISTS tmp_dedutions;' +
         ' CREATE TEMPORARY TABLE tmp_dedutions AS ' +
         ' SELECT gt.Prikaz, gt.xp_date, p.mid, p.cid, p.PageNum' +
         ' FROM groupuser_transfer gt' +
         ' JOIN (' +
         '  SELECT MIN(t2.date) stDate, MAX(t2.date) endDate, t2.mid, t2.cid, t2.PageNum' +
         '  FROM tmp_report_journal_main_templ_with_peoples t2' +
         '  GROUP BY cid, PageNum, mid' +
         '  ) p ON p.mid = gt.mid ' +
         ' WHERE gt.xp_status = 6  ' +
         ' AND gt.cid = -1' +
         ' AND gt.xp_date BETWEEN p.stDate AND p.endDate;';
//Data.ExecuteSQL(SQL);
fScript.Add(SQL);
SQL :=
         ' UPDATE tmp_report_journal_main_templ_with_peoples p' +
         ' JOIN tmp_dedutions d ON d.MID = p.MID AND p.cid = d.cid AND p.PageNum = d.PageNum' +
         ' SET deduction = LEFT(CONCAT(''(отчислен '', to_char(d.xp_date, ''dd.mm.yyyy''), '' приказ '', COALESCE(d.Prikaz, ''''), '')''),50) '+
         ' WHERE not d.mid is null ;';
  fScript.Add(SQL);

  //Перевод
  SQL := ' DROP TABLE IF EXISTS tmp_dedutions;' +
         ' CREATE TEMPORARY TABLE tmp_dedutions AS ' +
         ' SELECT gt.Prikaz, gt.xp_date, p.mid, p.cid, p.PageNum, ' +
         ' gt.group_from_text, gt.group_to_text ' +
         ' FROM groupuser_transfer gt' +
         ' JOIN (' +
         '  SELECT MIN(t2.date) stDate, MAX(t2.date) endDate, t2.mid, t2.cid, t2.PageNum' +
         '  FROM tmp_report_journal_main_templ_with_peoples t2' +
         '  GROUP BY cid, PageNum, mid' +
         '  ) p ON p.mid = gt.mid ' +
         ' WHERE gt.xp_status = 2  ' +
         ' AND gt.cid = -1' +
         ' AND gt.xp_date BETWEEN p.stDate AND p.endDate;';
//Data.ExecuteSQL(SQL);
fScript.Add(SQL);
SQL :=
         ' UPDATE tmp_report_journal_main_templ_with_peoples p' +
         ' JOIN tmp_dedutions d ON d.MID = p.MID AND p.cid = d.cid AND p.PageNum = d.PageNum' +
         ' SET deduction = LEFT(CONCAT(to_char(d.xp_date, ''dd.mm.yyyy''), '' Приказ № '', COALESCE(d.Prikaz, ''''), '' перевод из '', d.group_from_text, '' в '', d.group_to_text),50) '+
         ' WHERE not d.mid is null and (p.deduction = '''' or p.deduction is NULL);';
  fScript.Add(SQL);
RunScript('Оценки');
end;

procedure TSQLScripts48.CreateTerms;
var
  SQL: string;
begin
  //Создать выборку по периодам для текущей группы
  //Рассчитано по умолчанию на четверти.
  SQL := ' DROP TABLE IF EXISTS tmp_report_journal_terms; '+
         ' CREATE TEMPORARY TABLE tmp_report_journal_terms AS '+
         ' SELECT to_date(t.term_begin, ''dd.mm.yyyy'') BegDate, '+
         '        to_date(t.term_end, ''dd.mm.yyyy'') EndDate, '+
         '        t.title, t.trmid '+
         ' FROM terms t '+
         ' JOIN group_history gh ON gh.term_type_id = t.term_type_id AND gh.school_year = '+inttostr(year)+' '+
         ' WHERE t.year = '+inttostr(year)+' AND gh.gid = '+ inttostr(gid) +
         ' ORDER BY CONCAT(SUBSTR(t.term_begin, 7, 4), SUBSTR(t.term_begin, 4, 2), SUBSTR(t.term_begin, 1, 2)); ';
  fScript.Add(SQL);
  //Текущие типы оценок за учебный период
  SQL := ' DROP TABLE IF EXISTS tmp_renamed_periods; ' +
         ' CREATE TEMPORARY TABLE tmp_renamed_periods ' +
         ' ( trmtid int, title varchar(255) ); ' +
         '' +
         ' INSERT INTO tmp_renamed_periods VALUES' +
         '   (7, ''''), (9, '': Зачет/экзамен''), (8, '': Итог''), (6, '': Срез'');' +
         '' +
         ' DROP TABLE IF EXISTS tmp_report_journal_terms_periods;  ' +
         ' CREATE TEMPORARY TABLE tmp_report_journal_terms_periods AS ' +
         '  SELECT tt.BegDate, tt.EndDate, tt.trmid, ' +
         '         CONCAT(tt.title, r.title) title, gt.trmtid' +
         '  FROM  tmp_report_journal_terms tt' +
         '  JOIN terms t ON t.trmid = tt.trmid' +
         '  JOIN term_grade_types  gt ON FIND_IN_SET(gt.trmtid, t.term_grade_types)' +
         '  JOIN tmp_renamed_periods r ON r.trmtid = gt.trmtid' +
         '  ORDER BY tt.BegDate, gt.trmtid; ';
  fScript.Add(SQL);
  RunScript('Учебные периоды');
end;

procedure TSQLScripts48.CreateTmpAllCourses;
var
  SQL: string;
  I: Integer;
begin
  //Получить список всех занятий по датам с предметами за период
  SQL := Format(
         ' DROP TABLE IF EXISTS tmp_report_stat_courses;'+
         ' CREATE TEMPORARY TABLE tmp_report_stat_courses AS '+
         ' SELECT sh.SHEID, xp_Subject_Without_Year(c.title) as title, sh.CID, IF(sh.st_state_id = 1 AND sh.subst_teacher > 0 AND sh.cid = sh.sh_subst_cid, sh.subst_teacher, sh.teacher_mid) teacher_mid, w.week_id, '+
         ' if(sh.st_state_id <> 1 OR sh.st_state_id is NULL OR sh.subst_teacher <= 0 OR sh.cid <> sh.sh_subst_cid, 0, sh.teacher_mid) subTeacher, '+
         '         w.wstart_date + (sh.day_of_week - 1 -WeekDay(w.wstart_date)) * interval ''1 day'' AS date, sh.gid as mainGid,  sh.period as xp_part, '+
         '        0 as plan, '+
         '        0 as fact '+
         ' FROM nnz_weeks w '+
         '  JOIN nnz_schedule sh ON (sh.sh_var_id = w.sh_var_id AND sh.CID <> 0) '+
         '   JOIN groupname g ON g.gid=sh.gid '+
         '   JOIN courses c ON c.cid = sh.cid '+
         '    LEFT JOIN groupname g2 ON g2.gid=g.owner_gid '+
         ' WHERE COALESCE(g2.gid,g.gid) = %2:s '+
         '  AND sh.day_of_week - 1 BETWEEN WEEKDAY(w.wstart_date) AND WEEKDAY(w.wend_date) '+
         '  AND  w.wstart_date + (sh.day_of_week - 1 -WeekDay(w.wstart_date)) * interval ''1 day'' BETWEEN %0:s AND %1:s '+
         '  AND NOT  w.wstart_date + (sh.day_of_week - 1 -WeekDay(w.wstart_date)) * interval ''1 day'' in (SELECT  date FROM holidays) '+
         '  AND (sh.cid = %3:s or %3:s = 0) ' +
         '  AND (sh.st_state_id  <> 2  OR sh.st_state_id is NULL) '+
         ' ORDER BY date;',
         [StartDate, EndDate, gid, cid]);
  fScript.Add(SQL);

  SQL := ' DROP TABLE IF EXISTS tmp_report_stat_courses_t; '+
         ' CREATE TEMPORARY TABLE tmp_report_stat_courses_t AS '+
         ' SELECT * FROM tmp_report_stat_courses; '+

         ' UPDATE tmp_report_stat_courses as b '+
         ' JOIN (SELECT COUNT(*) a, cid, xp_part, mainGid FROM tmp_report_stat_courses_t GROUP BY CID, xp_part, mainGid) t '+
         ' ON b.cid = t.cid and b.xp_part = t.xp_part and t.mainGid = b.mainGid '+
         ' SET plan = t.a; '+

         ' UPDATE tmp_report_stat_courses as b '+
         ' JOIN (SELECT COUNT(*) a, tmp.cid, tmp.xp_part, tmp.mainGid FROM tmp_report_stat_courses_t tmp '+
         ' JOIN nnz_sh_module_fact f ON f.SHEID = tmp.SHEID AND f.week_id = tmp.week_id '+
         ' GROUP BY CID, xp_part, mainGid) t  ON b.cid = t.cid and b.xp_part = t.xp_part and t.mainGid = b.mainGid  '+
         ' SET fact = t.a; ';

   fScript.Add(SQL);

  SQL :=  format(
                ' DROP TABLE IF EXISTS tmp_report_stat_courses_t; '+
                ' CREATE TEMPORARY TABLE tmp_report_stat_courses_t AS '+
                '  SELECT * FROM tmp_report_stat_courses; '+

                ' INSERT INTO tmp_report_stat_courses '+
                ' SELECT DISTINCT t.trmid + 10000000 AS SHEID, '+
                '                 t.title, '+
                '                 cs.CID, '+
                '                 0 AS teachermid, '+
                '                 9999998 AS week_id, '+
                '                 0 AS sybteacher, '+
                '                 to_date(t.term_end, ''dd.mm.yyyy'') + INTERVAL ''1 DAY'' a, '+
                '                 cs.maingid, 0 as xp_part, cs.plan, cs.fact '+
                ' FROM tmp_report_stat_courses_t cs '+
                'JOIN (SELECT cid,trmid FROM term_grades WHERE trmtid=7 GROUP BY cid,trmid) tg ON tg.cid=cs.cid '+
                '  JOIN terms t on t.trmid=tg.trmid and t.year=%0:s'+
                ' JOIN group_history gh ON gh.term_type_id = t.term_type_id AND gh.school_year = %0:s AND gh.gid = %1:s; ',
                [year,gid]);
  //годовые
  for I := 1 to 3 do
  begin
  SQL := SQL +  format(
                ' INSERT INTO tmp_report_stat_courses '+
                ' SELECT DISTINCT /*t.xp_key +*/ 2000000'+IntToStr(i)+' AS SHEID, '+
                '                 t.Name, '+
                '                 cs.CID, '+
                '                 0 AS teachermid, '+
                '                 9999999 AS week_id, '+
                '                 0 AS sybteacher, '+
                '                 t.EndDate + ' +inttostr(i) + ' * interval ''1 DAY'' a, '+
                '                 cs.maingid, 0 as xp_part, cs.plan, cs.fact '+
                ' FROM tmp_report_stat_courses_t cs '+
                ' JOIN (SELECT s_year_id,cid FROM nnz_year_grades WHERE y_grtype_id =%0:d GROUP BY cid,s_year_id) yg ON yg.cid=cs.cid '+
                ' JOIN school_year t ON t.xp_key= yg.s_year_id AND t.xp_key=%1:s; ',
                [i,year]);
  end;

  fScript.Add(SQL);

  {SQL := ' SELECT '+
          CONCAT(
          (
          CASE (IF(t.term_type_id = @c, @i := @i + 1, @i := 1))
          WHEN 1 THEN 'I'
          WHEN 2 THEN 'II'
          WHEN 3 THEN 'III'
          WHEN 4 THEN 'IV'
          END), ' ',
          (CASE t.term_type_id
          WHEN 1 THEN 'с'
          WHEN 2 THEN 'т'
          WHEN 3 THEN 'ч'
          WHEN 4 THEN 'п'
          END)) AS title,
          t.trmid

          FROM terms t

          WHERE year = 25
          ORDER BY t.term_type_id, str_to_date(t.term_begin, '%d.%m.%Y')   }
  RunScript('Анализ расписания');
end;

destructor TSQLScripts48.Destroy;
begin
  fScript.Free;
  inherited;
end;

function TSQLScripts48.GetSQL(ScriptNum: integer): string;
var
  SQL: string;
begin
  case ScriptNum of
    1: SQL := '';

  end;
end;


procedure TSQLScripts48.RunScript(State: string);
var
  i: integer;
  iTimeOut: integer;

  //time: TTimer;
  outfile: TextFile;
begin

  Inc(step);

  iTimeOut := Data.Connection.TimeOut;
  Data.Connection.TimeOut := 500;

  {$IFDEF DEBUG}
    AddToLogFile('/* ' + State + ' */');
  {$ENDIF}

 //time := TTimer.Create(IndicatorForm);
 //time.interval := 1;
 //time.OnTimer := AddSeconds;
 //time.Enabled := True;


  for i := 0 to fScript.Count-1 do
    try
      begin
      sqlTimeSeconds := 0;

    {$IFDEF DEBUG}
        AddToLogFile(fScript[i]);
    {$ENDIF}

      Data.ExecuteSQL(fScript[i]);


      //writeln(outfile, ' /* Script time: '+ IntToStr(sqlTimeSeconds) + ' ms. */ ');
      end;
    except on e: Exception do
      begin
        raise;
      end;

    end;


  Data.Connection.TimeOut := iTimeOut;
  fScript.Clear;
  //time.Free;
end;

procedure TSQLScripts48.AddToLogFile(Str: string);
var
  outfile: TextFile;
  logfile: string;
begin
  Data.log(mtExtra,self,Str);
end;

end.