535 lines
24 KiB
ObjectPascal
535 lines
24 KiB
ObjectPascal
unit applicantresult;
|
||
|
||
{$mode ObjFPC}{$H+}
|
||
|
||
interface
|
||
|
||
uses
|
||
Classes, SysUtils, cgiReport,xpMemParamManagerUnit;
|
||
const
|
||
ApplicantExtraParamCnt=4;
|
||
ApplicantExtraFields: array[1..ApplicantExtraParamCnt] of string = ('tabel_mode','olympic_mode','portfolio_mode','techno_mode');
|
||
type
|
||
|
||
{ TRepApplicantList }
|
||
|
||
{ TRepApplicantResult }
|
||
|
||
TRepApplicantResult=class(TReportCommand)
|
||
private
|
||
edtMode: integer;
|
||
cbStream: integer;
|
||
idYear: integer;
|
||
ColNames: array[1..12] of string;
|
||
ColCount: integer;
|
||
function MakeCols(): string;
|
||
function UpdateEnrollStatus(): integer;
|
||
procedure MakeProblemsTable();
|
||
public
|
||
class function CommandSubClass: string; override;
|
||
procedure Prepare; override;
|
||
procedure OnFillVariables(AVariables: TxpMemParamManager); override;
|
||
end;
|
||
|
||
implementation
|
||
uses
|
||
cgiDM,dateutils,commandcol;
|
||
|
||
{ TRepApplicantResult }
|
||
|
||
function TRepApplicantResult.MakeCols: string;
|
||
var
|
||
SQL: string;
|
||
i,j: integer;
|
||
exams: string;
|
||
l: TStrings;
|
||
begin
|
||
exams := '';
|
||
for i := 1 to 6 do
|
||
exams := exams + format(
|
||
'UNION SELECT COALESCE(NULLIF(Exam%0:dColName,''''),EnterExam%0:dName) as ExamName, coalesce(d.sorting, %0:d) as sorting '+
|
||
'FROM applicant_group g '+
|
||
' LEFT JOIN (SELECT d.school_year, COALESCE(d.stream,0) as stream, l.name as exam_name, d.sorting,d.kurs '+
|
||
' FROM enroll_disciplines d '+
|
||
' JOIN lessons l ON l.lid=d.lid '+
|
||
' WHERE d.school_year=%1:d AND COALESCE(d.stream,0)=%2:d '+
|
||
' ) d ON d.school_year=g.school_year AND d.stream=COALESCE(g.stream) AND d.exam_name=g.EnterExam%0:dName '+
|
||
'WHERE g.school_year=%1:d AND (%2:d=COALESCE(g.stream,0)) '+
|
||
'AND NULLIF(EnterExam%0:dName,'''') IS NOT NULL '+
|
||
'AND EXISTS (SELECT 1 FROM xp_applicant a WHERE a.applicant_group=g.id AND a.Child_Class=coalesce(d.kurs,a.Child_Class) ) ',
|
||
[i,idYear,cbStream]);
|
||
SQL := format(
|
||
'DROP TABLE IF EXISTS tmpExams; '+
|
||
'CREATE TEMPORARY TABLE tmpExams AS '+
|
||
'SELECT trim(t.ExamName) as ExamName, '+
|
||
'CASE t.ExamName '+
|
||
' WHEN ''Русский язык'' THEN 1 '+
|
||
' WHEN ''Математика'' THEN 2 '+
|
||
' WHEN ''Иностранный язык'' THEN 3 '+
|
||
' ELSE min(t.sorting)+6 '+
|
||
'END as sorting '+
|
||
'FROM ( '+
|
||
' SELECT '''' as ExamName, 0 as sorting '+
|
||
' %2:s '+
|
||
' ) t '+
|
||
'WHERE t.sorting>0 '+
|
||
'GROUP BY t.ExamName; ',
|
||
[idYear,cbStream,Exams]);
|
||
processor.ExecuteSQL(SQL);
|
||
|
||
ColCount := 0;
|
||
with processor.getData('SELECT ExamName,sorting FROM tmpExams ORDER BY sorting,ExamName ') do
|
||
try
|
||
while not eof and (ColCount<8) do
|
||
begin
|
||
if (pos('физ',AnsiLowerCase(FieldByName('ExamName').AsString))=0) or (pos('физика',AnsiLowerCase(FieldByName('ExamName').AsString))>0) then
|
||
begin
|
||
inc(ColCount);
|
||
ColNames[ColCount] := FieldByName('ExamName').AsString;
|
||
end;
|
||
Next;
|
||
end;
|
||
finally
|
||
Free;
|
||
end;
|
||
result := '';
|
||
for I := 1 to ColCount do
|
||
begin
|
||
result := result + 'CASE ';
|
||
for j := 1 to 6 do
|
||
result := result + format(
|
||
' WHEN a1.Col%0:d = %1:s THEN a1.Grade%0:d ',
|
||
[j,TNIDBDM.StringAsSQL(ColNames[i])]);
|
||
result := result + format(' ELSE NULL END AS Exam%d, ',[i]);
|
||
end;
|
||
for I := ColCount+1 to 12 do
|
||
begin
|
||
result := result+ Format('NULL AS Exam%d, ',[i]);
|
||
end;
|
||
end;
|
||
|
||
|
||
function TRepApplicantResult.UpdateEnrollStatus: integer;
|
||
var
|
||
SQL: string;
|
||
separate_enroll: boolean;
|
||
Z2: string;
|
||
ColSorter: string;
|
||
I,j: Integer;
|
||
SParts: array[1..10] of string;
|
||
DateFilter: string;
|
||
FS:TStrings;
|
||
begin
|
||
DateFilter := '';
|
||
if cbStream > 0 then
|
||
DateFilter := ' AND cast(a1.Date as DATE) BETWEEN %10:s AND %11:s '#13#10;
|
||
|
||
for i := 1 to 10 do
|
||
sParts[i] := '';
|
||
for i := 1 to 8 do
|
||
begin
|
||
sParts[1] := sParts[1] + format(
|
||
'+case when COALESCE(ep.psycho_mode%0:d,0)>=0 then (COALESCE(ep.psycho_mode%0:d,0)+1)*asnumeric(a1.EnterTest%0:dGrade) else 0 end '#13#10,[i]);
|
||
sParts[6] := sParts[6] + format(' and case coalesce(ep.psycho_mode%0:d,0) '+
|
||
'when -3 then true '+
|
||
'when -2 then coalesce(a1.EnterTest%0:dGrade,'''')=''да'' '+
|
||
'when -1 then coalesce(a1.EnterTest%0:dGrade,'''')=''нет'' '+
|
||
'else ROUND(10000*coalesce(asnumeric(a1.EnterTest%0:dGrade),0))>=ROUND(10000*coalesce(ep.psycho_pass%0:d,ep.psycho_min%0:d,-10)) '+
|
||
'end '#13#10,
|
||
[i]);
|
||
sParts[8] := sParts[8] + format(
|
||
', case when ep.psycho_mode%0:d=-1 AND coalesce(a1.EnterTest%0:dGrade,'''')<>''нет'' then ep.psycho_name%0:d else null end '#13#10,[i]);
|
||
sParts[8] := sParts[8] + format(
|
||
', case when ep.psycho_mode%0:d=-2 AND coalesce(a1.EnterTest%0:dGrade,'''')<>''да'' then concat(''не пройден тест: ('',ep.psycho_name%0:d,'')'') else null end '#13#10,[i]);
|
||
sParts[8] := sParts[8] + format(
|
||
', case when ep.psycho_mode%0:d in (0,1,2,3,4,5,6,7) '+
|
||
' AND ROUND(10000*coalesce(asnumeric(a1.EnterTest%0:dGrade),0))<ROUND(10000*coalesce(ep.psycho_pass%0:d,ep.psycho_min%0:d,-10)) '+
|
||
' then concat(''не пройден тест: ('',ep.psycho_name%0:d,'')'') else null end '#13#10,[i]);
|
||
if i <= 6 then
|
||
begin
|
||
sParts[2] := sParts[2] + format(
|
||
' COALESCE(NULLIF(g.Exam%0:dColName,''''),g.EnterExam%0:dName,a1.EnterExam%0:dName) as Col%0:d, '#13#10,[i]);
|
||
sParts[3] := sParts[3] + format(
|
||
' a1.EnterExam%0:dGrade as Grade%0:d, '#13#10,[i]);
|
||
sParts[4] := sParts[4] + format(
|
||
' and (coalesce(gradevalue(a1.EnterExam%0:dGrade,1)>= coalesce(g.Pass%0:dGrade,0),false) or NULLIF(a1.EnterExam%0:dName,'''') IS NULL) '#13#10,[i]);
|
||
sParts[5] := sParts[5] + format(
|
||
' and coalesce(a1.EnterExam%0:dGrade NOT LIKE ''н%%'',true) '#13#10,[i]);
|
||
|
||
sParts[7] := sParts[7] + format(
|
||
', case when NULLIF(a1.EnterExam%0:dName,'''') IS NOT NULL '+
|
||
' AND (coalesce(gradevalue(a1.EnterExam%0:dGrade,1)< coalesce(g.Pass%0:dGrade,0),false) '+
|
||
' OR coalesce(a1.EnterExam%0:dGrade LIKE ''незачет%%'',false)) then a1.EnterExam%0:dName else null end '#13#10,[i]);
|
||
|
||
end;
|
||
end;
|
||
|
||
SParts[9] := 'true and (coalesce(gradevalue(a1.EnterExam1Grade,1)>=coalesce(g.Pass1Grade,0),false) '+
|
||
'or coalesce(gradevalue(a1.EnterExam2Grade,1)>=coalesce(g.Pass2Grade,0),false) '+
|
||
'or coalesce(gradevalue(a1.EnterExam3Grade,1)>=coalesce(g.Pass3Grade,0),false) '+
|
||
'or coalesce(gradevalue(a1.EnterExam4Grade,1)>=coalesce(g.Pass4Grade,0),false) '+
|
||
'or coalesce(gradevalue(a1.EnterExam5Grade,1)>=coalesce(g.Pass5Grade,0),false) '+
|
||
'or coalesce(gradevalue(a1.EnterExam6Grade,1)>=coalesce(g.Pass6Grade,0),false)) '#13#10;
|
||
|
||
SQL := format(
|
||
'DROP TABLE IF EXISTS tmp_rpt_applicant_us; '#13#10+
|
||
'CREATE TEMPORARY TABLE tmp_rpt_applicant_us AS '#13#10+
|
||
'SELECT '+
|
||
' a.xp_key, '+
|
||
' a.s_year_id, '+
|
||
' a.trajectory, '+
|
||
' subj.Subject, '+
|
||
' CONCAT_WS('' '',UPPER(a.Child_LastName),a.Child_FirstName,a.Child_MidName) AS FIO, '#13#10+
|
||
' a.Child_Birth, '+
|
||
' a.Child_Class, '+
|
||
' CASE WHEN a.testready<>0 and coalesce(a.scr_fail,0) = 0 THEN ''годен'' ELSE ''не годен'' END AS isready, '+
|
||
' COALESCE(a.coeff,1) as coeff, a.sex, '+
|
||
' COALESCE(NULLIF(a2.Privilege,''''),''нет'') as Privilege , '+
|
||
' a2.Priv_Count, a2.priv_super, a2.Priv_M > 0 and priv_with_exam and a2.TestPassed and a.testready<>0 as priv_m, '#13#10+
|
||
' a.scr_fail, '+
|
||
' a2.Grade_RUS, '+
|
||
' a2.Grade_MATH, '+
|
||
' a2.Grade_INO, '+
|
||
' a2.Grade_PHYS, '+
|
||
' CASE WHEN COALESCE(ep.psychomode,0)=0 THEN '+
|
||
' ROUND(a2.psycho_grade,2)::character varying '+
|
||
' ELSE CASE WHEN a.Psychologist<>0 THEN ''Зачет'' ELSE ''Незачет'' END '+
|
||
' END as Psycho, '#13#10+
|
||
|
||
' a.applicant_status_id NOT IN (5,8) '+
|
||
' AND COALESCE(a.scr_fail,0)=0 '+
|
||
' AND ( a2.ExamPassed AND a2.TestPassed '+
|
||
' AND (COALESCE(ep.psychomode,0)=0 AND ROUND(10000*a2.Psycho_grade)>=ROUND(10000*coalesce(ep.psycho_pass,0)) OR a.Psychologist<>0 AND ep.psychomode<>0) '+
|
||
' OR a2.Priv_super<>0 OR (a2.priv_m > 0 and priv_with_exam AND a2.TestPassed and a.testready<>0)) '+
|
||
'as ExamOK, '#13#10+
|
||
' calc_applicant_ball(a.xp_key) AS Ball, '+
|
||
' calc_applicant_ball_priv_m(a.xp_key) as Ball_m, '#13#10+
|
||
' CASE '+
|
||
' WHEN a.absent <> 0 THEN CASE a.Sex WHEN ''женский'' THEN ''не явилась'' ELSE ''не явился'' END '+
|
||
' WHEN a.scr_fail <> 0 THEN a.screening '+
|
||
' WHEN a.applicant_status_id = 5 THEN ''Отказано'' '+
|
||
' WHEN a.applicant_status_id = 8 THEN ''Отказ от обучения'' '+
|
||
' WHEN a2.priv_super>0 THEN '''' '+
|
||
' WHEN a2.priv_m > 0 THEN TRIM(CONCAT(IF(COALESCE(ep.psychomode,0)=0 AND ROUND(10000*a2.Psycho_grade)<ROUND(10000*coalesce(ep.psycho_pass,0)) ' +
|
||
' OR coalesce(a.Psychologist,0)=0 AND ep.psychomode<>0,''Не рекомендуется к обучению по результатам психологического отбора'', ''''), ' +
|
||
' CASE WHEN position(''физическая культура'' in a2.FailedExams) > 0 THEN '' Не сданы: физическая культура'' ELSE '''' END)) '+
|
||
' WHEN nd.errors<>'''' THEN nd.errors '+
|
||
' WHEN NOT a2.TestPassed or NOT a2.ExamPassed '+
|
||
' or COALESCE(ep.psychomode,0)=0 AND ROUND(10000*a2.Psycho_grade)<ROUND(10000*coalesce(ep.psycho_pass,0)) OR coalesce(a.Psychologist,0)=0 AND ep.psychomode<>0 '+
|
||
' THEN CONCAT_WS('',\n '','+
|
||
' IF (COALESCE(ep.psychomode,0)=0 AND ROUND(10000*a2.Psycho_grade)<ROUND(10000*coalesce(ep.psycho_pass,0)) OR coalesce(a.Psychologist,0)=0 AND ep.psychomode<>0,''Не рекомендуется к обучению по результатам психологического отбора'',NULL), '+
|
||
' IF(NOT a2.TestPassed AND a2.Priv_super=0, a2.FailedTests,NULL), '+
|
||
' IF(NOT a2.ExamPassed AND a2.Priv_super=0, CONCAT(''Не сданы: \n'',a2.FailedExams),NULL)'+
|
||
' ) '+
|
||
' END AS Prim, '#13#10+
|
||
' a2.FailedExams, a2.FailedTests, a2.ExamPassed,a2.TestPassed, '+
|
||
' CASE WHEN a.absent<>0 THEN 1 ELSE 0 END as absent, '+
|
||
' a2.col1,a2.col2,a2.col3,a2.col4,a2.col5,a2.col6, '+
|
||
' a2.grade1,a2.grade2,a2.grade3,a2.grade4,a2.grade5,a2.grade6 '#13#10+
|
||
'FROM ( '+
|
||
' SELECT a1.xp_key, '+
|
||
' get_applicant_grade(a1.xp_key,''%%русский%%'','''') AS Grade_RUS, '+
|
||
' get_applicant_grade(a1.xp_key,''%%математика%%'','''') AS Grade_MATH, '+
|
||
' get_applicant_grade(a1.xp_key,''%%язык%%'',''%%русский%%'') AS Grade_INO, '+
|
||
' get_applicant_grade(a1.xp_key,''%%физ%%'',''физика'') AS Grade_PHYS, '#13#10+
|
||
' %3:s '+
|
||
' %4:s '+
|
||
' (0.0 %2:s )/COALESCE(ep.psycho_denom,5) as Psycho_grade, '+
|
||
' (TRUE %5:s %6:s) as ExamPassed, '+
|
||
' (TRUE %7:s ) as TestPassed, '+
|
||
' %12:s as priv_with_exam, '+
|
||
' priv.Privilege, priv.Priv_Count, priv.Priv_super, priv.Priv_M, '+
|
||
' CONCAT_WS('', '' %8:s ) as FailedExams, '+
|
||
' CONCAT_WS('', '' %9:s ) as FailedTests '#13#10+
|
||
' FROM xp_applicant a1 '+
|
||
' LEFT JOIN ( '#13#10+
|
||
' SELECT priv.mid, '+
|
||
' string_agg(distinct CASE '+
|
||
' WHEN np.code = ''Л'' THEN ''Указ Президента РФ от 09.05.2022 г. №268 п.3/ч.1.'' '+
|
||
' WHEN np.code = ''Л1'' THEN ''Указ Президента РФ от 09.05.2022 г. №268 п.3/ч.2.'' '+
|
||
' WHEN np.code = ''М'' THEN ''Федеральный закон от 29 декабря 2022 года «641-ФЗ, статья 86, часть 6.1'' '+
|
||
' WHEN np.code IS NOT NULL THEN priv.PrivilegeCode '+
|
||
' ELSE priv.PrivilegeName END , '', '' ) AS Privilege, '#13#10+
|
||
' COUNT(priv.PrivilegeCode) AS Priv_Count, '+
|
||
' SUM(if(np.code in ( ''Л''),1, 0) ) AS Priv_super, '+
|
||
' SUM(if(np.code in (''М''),1,0)) as Priv_M '#13#10+
|
||
|
||
' FROM xp_applicant_file_privilege priv '+
|
||
' JOIN c_privilege np ON np.code=priv.PrivilegeCode '+
|
||
' WHERE priv.PrivilegeCode <> ''-'' '+
|
||
' GROUP BY priv.mid '#13#10+
|
||
') priv ON priv.mid=a1.xp_key '+
|
||
' JOIN applicant_group g ON g.id=a1.applicant_group '+
|
||
' LEFT JOIN enroll_params ep ON ep.school_year=a1.s_year_id '#13#10+
|
||
' WHERE a1.s_year_id=%0:d AND a1.Child_Class>0 AND a1.testready <> 0 AND applicant_status_id <> 4 '+
|
||
' AND (%1:d=COALESCE(a1.stream,0)) '+
|
||
DateFilter +
|
||
') a2 '#13#10+
|
||
' JOIN xp_applicant a ON a.xp_key = a2.xp_key '+
|
||
' LEFT JOIN enroll_params ep ON ep.school_year=a.s_year_id '+
|
||
' LEFT JOIN tmp_rpt_problems nd ON nd.xp_key=a.xp_key '+
|
||
' LEFT JOIN xp_subjects subj ON subj.Subject = a.Subject; ',
|
||
[idyear,cbStream,
|
||
sParts[1],
|
||
sParts[2],
|
||
sParts[3],
|
||
sParts[4],
|
||
sParts[5],
|
||
sParts[6],
|
||
sParts[7],
|
||
sParts[8],
|
||
TNIDBDM.StringAsSQL(Arguments.Keys.Values['fromdate']),
|
||
TNIDBDM.StringAsSQL(Arguments.Keys.Values['todate']),
|
||
sParts[9]]);
|
||
// FS:= TstringList.create;
|
||
// fs.Add(sql);
|
||
// fs.SaveToFile('sqlappldebug.sql');
|
||
//xpInformation(sql);
|
||
processor.ExecuteSQL(SQL);
|
||
|
||
colSorter := MakeCols();
|
||
SQL := format(
|
||
'DROP TABLE IF EXISTS tmp_rpt_applicant; '#13#10+
|
||
'CREATE TEMPORARY TABLE tmp_rpt_applicant AS '#13#10+
|
||
' SELECT a1.*, e.places, e.places_male, e.places_female, '+
|
||
ColSorter+
|
||
'CASE '+
|
||
' WHEN COALESCE(a1.absent,0)=0 THEN ' +
|
||
' CASE ' +
|
||
' WHEN a1.undefined<>'''' THEN 5 '+
|
||
' WHEN NOT ExamOK AND priv_super=0 THEN 3 '+
|
||
' WHEN (a1.row<=a1.place_limit) THEN 0 '+
|
||
|
||
' ELSE 3 ' +
|
||
' END ' +
|
||
' ELSE 4 ' +
|
||
'END as GroupID '#13#10 +
|
||
' FROM ( '+
|
||
' SELECT t.*, '#13#10+
|
||
' ROW_NUMBER() OVER (PARTITION BY t.Child_Class, t.gender, t.track '+
|
||
' ORDER BY t.child_class,t.gender,t.track, COALESCE(t.scr_fail,0), t.absent,t.has_errors, '+
|
||
' t.priv_super desc, t.priv_m desc,t.priv_ball desc, ExamOK desc, t.ball, t.has_priv, t.fio ) as row '#13#10+
|
||
' FROM '+
|
||
' (SELECT '+
|
||
' CASE '+
|
||
' WHEN et.places>0 THEN et.places '+
|
||
' WHEN e.places_female>0 AND a.Sex=''женский'' THEN e.places_female '+
|
||
' WHEN e.places_male>0 AND a.Sex=''мужской'' THEN e.places_male '+
|
||
' ELSE e.places '+
|
||
' END as place_limit, '#13#10+
|
||
' CASE '+
|
||
' WHEN a.sex=''женский'' AND e.places_female>0 THEN 2 '+
|
||
' WHEN a.Sex=''мужской'' AND e.places_male>0 THEN 1 '+
|
||
' ELSE 0 '+
|
||
' END as gender, '+
|
||
' CASE '+
|
||
' WHEN et.trajectory>0 THEN a.trajectory '+
|
||
' ELSE 0 '+
|
||
' END as track, '+
|
||
' a.*,nd.errors as undefined, '+
|
||
' IF(nd.errors<>'''',1,0) as has_errors,'+
|
||
' case when a.priv_m then a.Ball_m else 0 end as priv_ball,'+
|
||
' case a.Priv_Count when 0 then 1 else 0 end as has_priv '#13#10+
|
||
' from tmp_rpt_applicant_us a '+
|
||
' join xp_enroll e ON e.school_year=%0:d and e.kurs=a.child_class AND e.trajectory=0 AND e.places>0 '+
|
||
' and (e.places_female>0 and a.Sex=''женский'' OR e.places_male>0 and a.Sex=''мужской'' OR e.places_female IS NULL AND e.places_male IS NULL) '+
|
||
' LEFT JOIN xp_enroll et ON et.school_year=%0:d AND et.kurs=a.Child_Class AND et.trajectory=a.trajectory '+
|
||
' LEFT JOIN tmp_rpt_problems nd ON nd.xp_key=a.xp_key '#13#10+
|
||
' WHERE 1=1 '+
|
||
' AND (et.trajectory>0 OR NOT EXISTS (SELECT 1 FROM xp_enroll WHERE school_year=a.s_year_id AND kurs=a.Child_Class AND trajectory>0)) '#13#10+
|
||
' ORDER BY a.Child_Class,COALESCE(a.scr_fail,0), absent,IF(nd.errors<>'''',1,0),gender,'+
|
||
' track,priv_super desc,a.priv_m desc, case when a.priv_m then a.Ball_m else 0 end desc,ExamOK DESC, ball desc ,case a.Priv_Count when 0 then 1 else 0 end, a.fio '+
|
||
' ) t '+
|
||
{
|
||
' SELECT IF(Child_Class<>@class OR use_sex AND sex <> @sex,@i:=1,@i:=@i+1) as Row, '+
|
||
' (@class:=Child_Class) as ClassCopy,(@sex:=sex) as SexCopy, a.* '+
|
||
' FROM (SELECT u.*,(e.places_male IS NOT NULL OR e.places_female IS NOT NULL) as use_sex,nd.errors as undefined '+
|
||
' FROM tmp_rpt_applicant_us u '+
|
||
' LEFT JOIN xp_enroll e ON e.kurs=u.Child_Class AND e.school_year=u.s_year_id '+
|
||
' LEFT JOIN tmp_rpt_problems nd ON nd.xp_key=u.xp_key '+
|
||
' ORDER BY Child_Class,absent,ExamOK DESC,IF(nd.errors<>'''',1,0), '+
|
||
' CASE WHEN e.places_male IS NOT NULL OR e.places_female IS NOT NULL THEN Sex ELSE 0 END, '+
|
||
' coalesce(Ball,0) DESC, CASE COALESCE(Priv_Count,0) WHEN 0 THEN 0 ELSE 1 END DESC, fio) a, '+
|
||
' (select @i:=0,@class:=null,@sex:=null) as z '+ }
|
||
' ) a1 '+
|
||
//' LEFT JOIN tmp_rpt_problems nd ON nd.xp_key=a1.xp_key '+
|
||
' LEFT JOIN xp_enroll e ON e.kurs=a1.Child_Class AND e.school_year=%0:d AND e.trajectory=0; '{+
|
||
' LEFT JOIN tmpExams e1 ON e1.ExamName = a1.Col1 '+
|
||
' LEFT JOIN tmpExams e2 ON e2.ExamName = a1.Col2 '+
|
||
' LEFT JOIN tmpExams e3 ON e3.ExamName = a1.Col3 '+
|
||
' LEFT JOIN tmpExams e4 ON e4.ExamName = a1.Col4 '+
|
||
' LEFT JOIN tmpExams e5 ON e5.ExamName = a1.Col5 '+
|
||
' LEFT JOIN tmpExams e6 ON e6.ExamName = a1.Col6 '},
|
||
[idYear]);
|
||
//xpInformation(sql);
|
||
processor.ExecuteSQL(SQL);
|
||
|
||
SQL :=
|
||
'UPDATE xp_applicant a '+
|
||
'SET passed = r.GroupID IN (0,1,2), ball = r.ball '+
|
||
'FROM tmp_rpt_applicant r '+
|
||
'WHERE r.xp_key = a.xp_key; ';
|
||
// connect.processor.ExecuteSQL(SQL);
|
||
|
||
result := colcount;
|
||
end;
|
||
|
||
procedure TRepApplicantResult.MakeProblemsTable;
|
||
var
|
||
SQL: string;
|
||
DateFilter: string;
|
||
clsError: string;
|
||
Exams,Psycho: array[1..4] of string;
|
||
i: integer;
|
||
FS:TStrings;
|
||
begin
|
||
if cbStream > 0 then
|
||
DateFilter := Format(' AND cast(date as DATE) BETWEEN %0:s AND %1:s '#13#10,
|
||
[TNIDBDM.StringAsSQL(Arguments.Keys.Values['fromdate']), TNIDBDM.StringAsSQL(Arguments.Keys.Values['todate'])]);
|
||
Psycho[1] := 'false';
|
||
for i := 1 to 8 do
|
||
psycho[1] := psycho[1]+ format(' OR IF(coalesce(ep.psycho_mode%0:d,0)>=-2, NULLIF(a.EnterTest%0:dGrade,'''') IS NULL, FALSE)'#13#10,[i]);
|
||
Exams[1] := 'NULL';
|
||
for i := 1 to 6 do
|
||
Exams[1] := Exams[1] + format(', case when COALESCE(g.EnterExam%0:dName,'''') <> '''' AND COALESCE(a.EnterExam%0:dGrade,'''') = '''' then g.EnterExam%0:dName else null end '#13#10,[i]);
|
||
Psycho[2] := 'false';
|
||
for i := 1 to 8 do
|
||
Psycho[2] := psycho[2] + format(
|
||
' OR coalesce(ep.psycho_mode%0:d,0)=-1 AND a.EnterTest%0:dGrade=''да'' '+
|
||
' OR coalesce(ep.psycho_mode%0:d,0)=-2 AND a.EnterTest%0:dGrade=''нет'''+
|
||
' OR coalesce(ep.psycho_mode%0:d,0) IN (0,1,2,3,4,5,6,7) AND ROUND(10000*COALESCE(asnumeric(a.EnterTest%0:dGrade),100)) < ROUND(10000*coalesce(ep.psycho_pass%0:d,ep.psycho_min%0:d,-10)) '#13#10,
|
||
[i]);
|
||
SQL := format(
|
||
'DROP TABLE IF EXISTS tmp_rpt_problems; '#13#10+
|
||
'CREATE TEMPORARY TABLE tmp_rpt_problems AS '#13#10+
|
||
'SELECT xp_key,child_class,xp_format_fio(child_lastname,child_firstname,child_midname,1) as fio, '#13#10+
|
||
'CONCAT_WS('', '', '+
|
||
' e.errGroup,errPsycho,errExams,errPortfolio,errTabAvg,errOlympic,errTechno '+
|
||
') as errors '#13#10+
|
||
'FROM ( '+
|
||
'SELECT a.xp_key,a.child_class, a.child_lastname,a.child_firstname,a.child_midname, '+
|
||
' IF(g.id IS NULL,''не назначена экзаменационная группа'',null) as errGroup, '+
|
||
' IF(coalesce(ep.Psychomode,0)=0 AND (%2:s),''не рассчитано итоговое заключение по псих. тестам'',null) as errPsycho, '+
|
||
' IF(NOT (%4:s),CONCAT(''не заполнены результаты испытаний('',NULLIF(CONCAT_WS('', '', %3:s),''''),'')''),null) as errExams, '+
|
||
' IF(coalesce(ep.portfolio_mode,0)<>0 AND a.portfoliograde IS NULL,''нет балла за портфолио'',null) as errPortfolio, '+
|
||
' IF(coalesce(ep.tabel_mode,1)<>0 AND a.tabel_avg IS NULL,''нет ср. балла по табелю'',null) as errTabAvg, '+
|
||
' IF(coalesce(ep.olympic_mode,1)<>0 AND a.olympic_grade IS NULL,''нет балла за олимпиады'',null) as errOlympic, '+
|
||
' IF(coalesce(ep.techno_mode,0)<>0 AND a.techno IS NULL,''нет оценки тех./физ. подготовки'',null) as errTechno '+
|
||
'FROM '#13#10+
|
||
'(SELECT a.xp_key FROM xp_applicant a '+
|
||
'WHERE a.testready<>0 AND coalesce(a.absent,0)=0 and coalesce(a.scr_fail,0)=0 '+
|
||
' AND a.applicant_status_id NOT IN (4,5,8) '+
|
||
' AND a.s_year_id=%0:d AND a.Child_Class>0 '+
|
||
DateFilter +
|
||
' AND (%1:d=COALESCE(a.stream,0)) '+
|
||
' and a.xp_key NOT IN (SELECT priv.mid FROM xp_applicant_file_privilege priv JOIN c_privilege np ON np.code=priv.PrivilegeCode WHERE np.code in (''Л'', ''М'')) '+
|
||
') u '#13#10+
|
||
' JOIN xp_applicant a ON a.xp_key=u.xp_key '+
|
||
' LEFT JOIN applicant_group g ON g.id=a.applicant_group '+
|
||
' LEFT JOIN enroll_params ep ON ep.school_year=a.s_year_id '+
|
||
'WHERE 1=1 '+
|
||
') e '#13#10+
|
||
'WHERE '+
|
||
' e.errGroup IS NOT NULL '+
|
||
' OR e.errPsycho IS NOT NULL '+
|
||
' OR e.errExams IS NOT NULL '+
|
||
' OR e.errPortfolio IS NOT NULL '+
|
||
' OR e.errTabAvg IS NOT NULL '+
|
||
' OR e.errOlympic IS NOT NULL '+
|
||
' OR e.errTechno IS NOT NULL '+
|
||
'ORDER BY child_class,2; ',
|
||
[idYear,cbStream, Psycho[1],Exams[1], Psycho[2]]);
|
||
|
||
Processor.ExecuteSQL(SQL);
|
||
end;
|
||
|
||
|
||
class function TRepApplicantResult.CommandSubClass: string;
|
||
begin
|
||
Result:='applicant_result';
|
||
end;
|
||
|
||
procedure TRepApplicantResult.Prepare;
|
||
var
|
||
SQL: string;
|
||
begin
|
||
inherited Prepare;
|
||
idYear := getInt('schoolyear');
|
||
cbStream := getInt('stream');
|
||
MakeProblemsTable;
|
||
UpdateEnrollStatus();
|
||
SQL := format(
|
||
'DROP TABLE IF EXISTS tmp_members; '+
|
||
'CREATE TEMPORARY TABLE tmp_members AS '+
|
||
' SELECT xp_f_get_mid_fio(m.mid,0) as member FROM enroll_comitet c '+
|
||
' JOIN enroll_comitet_members m ON m.enroll_comitet = c.id '+
|
||
'WHERE c.school_year=%0:d AND coalesce(c.stream,0)=%1:d ORDER BY 1; ' ,
|
||
[idYear,cbStream]);
|
||
processor.ExecuteSQL(SQL);
|
||
end;
|
||
|
||
procedure TRepApplicantResult.OnFillVariables(AVariables: TxpMemParamManager);
|
||
var
|
||
i: integer;
|
||
ColSorter: string;
|
||
SQL: string;
|
||
extra_params: array [1..ApplicantExtraParamCnt] of boolean;
|
||
Z2: string;
|
||
separate_enroll: boolean;
|
||
|
||
begin
|
||
ColSorter:='';
|
||
for I := 1 to ColCount do
|
||
AVariables['Grade'+inttostr(i)] := (ColNames[i]);
|
||
for I := ColCount+1 to 12 do
|
||
begin
|
||
ColSorter := ColSorter+ Format('NULL AS Exam%d, ',[i]);
|
||
AVariables['Grade'+inttostr(i)] := ('');
|
||
end;
|
||
for i := 1 to ApplicantExtraParamCnt do
|
||
begin
|
||
extra_params[i] := processor.QueryIntValue(format('SELECT coalesce(%s,0) FROM enroll_params WHERE school_year=%d',[ApplicantExtraFields[i], idYear]))=1;
|
||
if extra_params[i] then
|
||
AVariables['ball_extra_'+inttostr(i)] := 1
|
||
else
|
||
AVariables['ball_extra_'+inttostr(i)] := 0;
|
||
end;
|
||
if (processor.QueryIntValue('SELECT COUNT(*) as cnt FROM tmp_rpt_problems')>0) then
|
||
AVariables['rpt_ready'] := 'ПРЕДВАРИТЕЛЬНЫЕ'#13#10'результаты'
|
||
else
|
||
AVariables['rpt_ready'] := ('Результаты');
|
||
SQL := format(
|
||
'SELECT xp_f_get_mid_fio(c.chairman,0) as chairman , xp_f_get_mid_fio(c.deputy,0) as deputy, xp_f_get_mid_fio(c.deputy2,0) as deputy2, xp_f_get_mid_fio(c.secretary,0) as secretary '+
|
||
'FROM enroll_comitet c WHERE c.school_year = %0:d AND coalesce(c.stream,0)=%1:d; ',
|
||
[idYear,cbStream]);
|
||
with Processor.getData(SQL) do
|
||
try
|
||
if Not eof then
|
||
begin
|
||
AVariables['Председатель'] := (FieldByName('Chairman').AsString);
|
||
AVariables['Заместитель'] := (FieldByName('Deputy').AsString);
|
||
Z2 := FieldByName('Deputy2').AsString;
|
||
AVariables['Секретарь'] := (FieldByName('Secretary').AsString);
|
||
end
|
||
else
|
||
begin
|
||
AVariables['Председатель'] := ('');
|
||
AVariables['Заместитель'] := ('');
|
||
Z2 := '';
|
||
AVariables['Секретарь'] := ('');
|
||
end;
|
||
AVariables['Заместитель2'] := (Z2);
|
||
if Z2<>'' then AVariables['zam2'] := 1 else AVariables['zam2'] := 0;
|
||
finally
|
||
Free;
|
||
end;
|
||
//Variables['Year'] := YearOf(Date);
|
||
AVariables['Year'] := processor.QueryValue('SELECT YEAR(begdate) FROM school_year WHERE xp_key = ' + inttostr(idyear));
|
||
|
||
end;
|
||
Initialization
|
||
TCommandCollection.Register(TRepApplicantResult);
|
||
|
||
end.
|
||
|