Разработка модуля поддержки процессов организации повышения квалификации преподавателей в рамках АСУ МИИТ

end;

end;

procedure TForm_Event.PageControl_MainPageChanging(Sender: TObject;

NewPage: TcxTabSheet; var AllowChange: Boolean);

begin

if NewPage = cxTabSheet_R_C_Groups then

Fill_tv_r_c_groupsExecute(self);

if NewPage = cxTabSheet_Listeners then

Fill_tv_listenersExecute(self);

if NewPage = cxTabSheet_MC then

Filter_MC_Changed(self);

end;

procedure TForm_Event.PC_ApplyExecute(Sender: TObject);

var

i: Integer;

CE: TcxCustomEdit;

P, s: string;

ok: Boolean;

begin

ok := False;

MSystemServices.StartSequenceActions(False, True);

for i := 0 to cW_Contract_Params.Count - 1 do

begin // 1

P := '-1';

if VarToStr((cW_Contract_Params.Items[i] as TCustomEditWrapper).EditValue)

<> VarToStr((cW_Contract_Params.Items[i] as TCustomEditWrapper).Value)

then

begin // 2

CE := (cW_Contract_Params.Items[i] as TCustomEditWrapper).Element;

if CE = cxDateEdit_PC_D_Start then

P := '0'

else if CE = cxDateEdit_PC_D_End then

P := '1'

else if (CE = cxPopupEdit_PC_Org) or (CE = cxTextEdit_PC_Basis) or

(CE = cxTextEdit_PC_FIO) or (CE = cxTextEdit_PC_FIO_R) or

(CE = cxPopupEdit_PC_Post) then

P := '2'

else if CE = cxPopupEdit_PC_SF then

P := '3'

else if CE = cxCurrencyEdit_PC_Money then

begin

P := '4';

cxCurrencyEdit_PC_Money.Properties.EditFormat := '0.00';

cxCurrencyEdit_PC_Money.Properties.UseThousandSeparator := False;

end

else if CE = cxPopupEdit_PC_Kind_Order then

P := '5'

else if CE = cxPopupEdit_PC_Kind_Direction then

P := '6'

else if CE = CheckBox_PC_NDS then

P := '7'

else if (CE = cxDateEdit_PC_D_Start_Otrjv) or (CE = cxDateEdit_PC_D_End_Otrjv)

then

begin

P := '8';

if VarToStr(cW_Contract_Params.wrappers[cxDateEdit_PC_D_Start_Otrjv].EditValue) = ''

then s := ''

else s := VarToStr(cW_Contract_Params.wrappers[cxDateEdit_PC_D_Start_Otrjv].EditValue)

+ ' по ' + VarToStr(cW_Contract_Params.wrappers[cxDateEdit_PC_D_End_Otrjv].EditValue);

end;

end; // 2

if P <> '-1' then

begin // 3

ok := MSystemServices.ExchangeData(Update_PC,

VarArrayOf([P,

VarToStr(tv_listeners.DataController.Values

[tv_listeners.DataController.FocusedRecordIndex,

tv_listeners_id_contract.Index]),

VarToStr(cW_Contract_Params.wrappers[cxDateEdit_PC_D_Start]

.EditValue),

VarToStr(tv_listeners.DataController.Values

[tv_listeners.DataController.FocusedRecordIndex,

tv_listeners_id_contractor.Index]),

VarToStr(cW_Contract_Params.wrappers[cxDateEdit_PC_D_End]

.EditValue),

VarToStr(cW_Contract_Params.wrappers[cxPopupEdit_PC_Org].EditValue),

VarToStr(cW_Contract_Params.wrappers[cxPopupEdit_PC_SF].EditValue),

VarToStr(cxCurrencyEdit_PC_Money.EditValue),

VarToStr(cW_Contract_Params.wrappers[cxPopupEdit_PC_Kind_Order]

.EditValue), VarToStr(cW_Contract_Params.wrappers

[cxPopupEdit_PC_Kind_Direction].EditValue),

VarToStr(cW_Contract_Params.wrappers[cxTextEdit_PC_Basis]

.EditValue),

VarToStr(cW_Contract_Params.wrappers[cxTextEdit_PC_FIO].EditValue),

VarToStr(cW_Contract_Params.wrappers[cxTextEdit_PC_FIO_R].EditValue),

VarToStr(cW_Contract_Params.wrappers[cxPopupEdit_PC_Post].EditValue),

My_BoolToStr(CheckBox_PC_NDS.Checked, 1, '0'),

s])

);

if ok then

case StrToInt(P) of // 4

2:

begin

if VarToStr(cW_Contract_Params.wrappers[cxPopupEdit_PC_Org]

.EditValue) = '' then

begin // 5

cW_Contract_Params.wrappers[cxTextEdit_PC_Basis].Clear;

cW_Contract_Params.wrappers[cxTextEdit_PC_FIO].Clear;

cW_Contract_Params.wrappers[cxTextEdit_PC_FIO_R].Clear;

cW_Contract_Params.wrappers[cxPopupEdit_PC_Post].Clear;

end; // 5

tv_listeners.DataController.Values

[tv_listeners.DataController.FocusedRecordIndex,

tv_listeners_org.Index] := VarToStr

(cW_Contract_Params.wrappers[cxPopupEdit_PC_Org]

.EditValueDisplay);

end;

4:

begin

tv_listeners.DataController.Values

[tv_listeners.DataController.FocusedRecordIndex,

tv_listeners_money.Index] := VarToStr

(cW_Contract_Params.wrappers[cxCurrencyEdit_PC_Money]

.EditValueDisplay);

cxCurrencyEdit_PC_Money.Properties.EditFormat := ',0.00';

cxCurrencyEdit_PC_Money.Properties.UseThousandSeparator := True;

end;

5:

cW_Contract_Params.wrappers[cxButtonEdit_PC_Contract]

.EditValueDisplay := TButtonOrderEditWrapper

(cW_Contract_Params.wrappers[cxButtonEdit_PC_Contract])

.L_status_text + ' (' + VarToStr

(cW_Contract_Params.wrappers[cxPopupEdit_PC_Kind_Order]

.EditValueDisplay) + ')';

6:

cW_Contract_Params.wrappers[cxButtonEdit_PC_Direction]

.EditValueDisplay := TButtonOrderEditWrapper

(cW_Contract_Params.wrappers[cxButtonEdit_PC_Direction])

.L_status_text + ' (' + VarToStr

(cW_Contract_Params.wrappers[cxPopupEdit_PC_Kind_Direction]

.EditValueDisplay) + ')';

end; // 4

end; // 3

end; // 1

MSystemServices.EndSequenceActions;

if ok then

cW_Contract_Params.ChangesAcceptWrappers;

end;

procedure TForm_Event.PC_AutorepresentativeExecute(Sender: TObject);

begin

if HandymanFunctions.ShowVNDialog(vnd_Confirmation, 'Автопредставитель',

'Вы действительно хотите добавить автопредставителя?') then

begin // 1

MSystemServices.ExchangeData(Autorepr,

VarArrayOf

([VarToStr(cW_Contract_Params.wrappers

[cxButtonEdit_PC_Contract].Value)]))

end; // 1

end;

procedure TForm_Event.PC_RefuseExecute(Sender: TObject);

begin

cW_Contract_Params.ChangesRefuseWrappers;

end;

procedure TForm_Event.Plan_ApplyExecute(Sender: TObject);

var

ok: Boolean;

begin

if VarToStr(cW_Plan_Filters.wrappers[cxPopupEdit_Plan_Filters_Plan].Value)

<> '' then

begin // 1

ok := MSystemServices.ExchangeData(Update_Plan,

VarArrayOf([VarToStr(cW_Plan_Filters.wrappers

[cxTextEdit_Plan_Filters_Note].EditValue),

VarToStr(cW_Plan_Filters.wrappers

[cxTextEdit_Plan_Filters_Summary].EditValue),

VarToStr(cW_Plan_Filters.wrappers[cxPopupEdit_Plan_Filters_Plan].Value)

]));

if ok then

begin // 3

cW_Plan_Filters.ChangesAcceptWrappers;

tv_plan.DataController.Values[tv_plan.DataController.FocusedRecordIndex,

tv_plan_note.Index] := VarToStr(cW_Plan_Filters.wrappers

[cxTextEdit_Plan_Filters_Note].EditValue);

tv_plan.DataController.Values[tv_plan.DataController.FocusedRecordIndex,

tv_plan_summary.Index] := VarToStr(cW_Plan_Filters.wrappers

[cxTextEdit_Plan_Filters_Summary].EditValue);

cW_Plan_Filters.wrappers[cxPopupEdit_Plan_Filters_Plan].ValueDisplay :=

VarToStr(cW_Plan_Filters.wrappers[cxTextEdit_Plan_Filters_Note]

.EditValue);

end; // 3

end // 1

else

begin // 2

cxTextEdit_Plan_Filters_Note.Clear;

cxTextEdit_Plan_Filters_Summary.Clear;

cW_Plan_Filters.ChangesAcceptWrappers;

end; // 2

end;

procedure TForm_Event.Plan_RefuseExecute(Sender: TObject);

begin

cW_Plan_Filters.ChangesRefuseWrappers;

end;

{ Доступность создания запсией в специальности подразделения }

procedure TForm_Event.DoChanged_cW_Spec_Div(AWrapper: TCustomEditWrapper);

begin

MakeEnable_OkButton(Panel_New_Spec_Div, cW_New_Spec_Div.IsNotEmptyMandatory)

end;

{ Доступность создания запсией в импорте}

procedure TForm_Event.PopupEdit_Import_SpecPropertiesEditValueChanged

(Sender: TObject);

var i: integer;

begin

if Sender = PopupEdit_Import_Spec then

ButtonEdit_Import_Spec.Enabled := VarToStr

(cW_Import.wrappers[PopupEdit_Import_Spec].Value) = '';

if Sender = PopupEdit_Import_Spec_Div

then

begin

ButtonEdit_Import_Spec_Div.Enabled := VarToStr

(cW_Import.wrappers[PopupEdit_Import_Spec_Div].Value) = '';

// Если есть выбранная запись, обновим по ней УМК!

if not ButtonEdit_Import_Spec_Div.Enabled

then cxPopupEdit_New_R_C_Group_Met_ComplexPropertiesInitPopup(PopupEdit_Import_UMK)

else

begin

if Get_SqlData_cxGrid(tv_r_c_groups).Params[0] <> VarToStr

(cW_Import.wrappers[PopupEdit_Import_Spec_Div].Value)

then begin

Get_SqlData_cxGrid(tv_r_c_groups).Params[0] := VarToStr

(cW_Import.wrappers[PopupEdit_Import_Spec_Div].Value);

Get_SqlData_cxGrid(tv_r_c_groups).Read_Data;

end;

end;

end;

if (Sender = PopupEdit_Import_UMK) or (Sender = PopupEdit_Import_Spec_Div) then

ButtonEdit_Import_UMK.Enabled := (VarToStr

(cW_Import.wrappers[PopupEdit_Import_UMK].Value) = '')

// Организатор должен быть задан!

And ((cW_Import.wrappers[PopupEdit_Import_Spec_Div].Value) <> '');

if (Sender = PopupEdit_Import_Plan) or ( Sender = PopupEdit_Import_Spec_Div)

then

begin

ButtonEdit_Import_Plan.Enabled := (VarToStr

(cW_Import.wrappers[PopupEdit_Import_Plan].Value) = '')

// Организатор должен быть задан!

And ((cW_Import.wrappers[PopupEdit_Import_Spec_Div].Value) <> '');

// Если позиция плана выбрана, то поищем ее в списке и выберем!

if VarToStr(cW_Import.wrappers[PopupEdit_Import_Plan].Value) <> ''

then

begin

i := FindGridRecordIndex(tv_r_c_plan, [tv_r_c_plan_id_r_c_plan]

, CreateVarAr([VarToStr(cW_Import.wrappers[PopupEdit_Import_Plan].Value)]));

if i >= 0

then Get_SqlData_cxGrid(tv_r_c_plan).LastChoiceIndex := i;

end;

end;

if (Sender = PopupEdit_Import_EG) or (Sender = PopupEdit_Import_Spec_Div)

or (Sender = PopupEdit_Import_UMK) then

ButtonEdit_Import_EG.Enabled := (VarToStr

(cW_Import.wrappers[PopupEdit_Import_EG].Value) = '')

// Организатор должен быть задан!

And ((cW_Import.wrappers[PopupEdit_Import_Spec_Div].Value) <> '')

// УМК должно быть задано!

And ((cW_Import.wrappers[PopupEdit_Import_UMK].Value) <> '');

if VarToStr(cW_Import.Wrappers[PopupEdit_Import_Plan].Value) <> '' then

if Get_SqlData_cxGrid(tv_r_c_groups).Params[0] <> VarToStr(cW_Import.Wrappers[PopupEdit_Import_Plan].Value)

then

begin

Get_SqlData_cxGrid(tv_r_c_groups).Params[0] := VarToStr(cW_Import.Wrappers[PopupEdit_Import_Plan].Value);

Get_SqlData_cxGrid(tv_r_c_groups).IsFirstRead := True;

end;

if VarToStr(cW_Import.Wrappers[PopupEdit_Import_Spec_Div].Value) <> '' then

if Get_SqlData_cxGrid(tv_r_c_groups).Params[0] <> VarToStr(cW_Import.Wrappers[PopupEdit_Import_Spec_Div].Value)

then

begin

Get_SqlData_cxGrid(tv_ed_group).Params[0] := VarToStr(cW_Import.Wrappers[PopupEdit_Import_Spec_Div].Value);

Get_SqlData_cxGrid(tv_ed_group).IsFirstRead := True;

end;

end;

procedure TForm_Event.Before_Update_Exchange_tv_listeners

(Sender: TSqlData_cxGridTableView; UpdateValue: Variant;

AItem: TcxCustomGridTableItem; ARecordIndex: Integer;

var ExchangeParameters: OleVariant; var Allow: Boolean);

var

FC: TcxGridColumn;

DC: TcxGridDataController;

begin

FC := Sender.GridTableView.Controller.FocusedColumn;

{ Параметры для изменения Ф.И.О. }

if FC = tv_listeners_fio then

begin // 1

DC := Current_Form_SE.cxGrid_FIO.DataController;

ExchangeParameters := CreateVarAr([DC.Values[DC.FocusedRecordIndex,

Current_Form_SE.cxGrid_FIO_ID_E.Index],

Sender.GridTableView.DataController.Values[ARecordIndex,

tv_listeners_id_student.Index],

Sender.GridTableView.DataController.Values[ARecordIndex,

tv_listeners_id_contract.Index]]);

end; // 1

{ Параметры для изменения учебной группы и даты начала }

if (FC = tv_listeners_ed_group) or (FC = tv_listeners_d_start) then

begin // 2

DC := tv_r_c_groups.DataController;

ExchangeParameters := CreateVarAr([DC.Values[DC.FocusedRecordIndex,

tv_r_c_groups_d_start.Index], DC.Values[DC.FocusedRecordIndex,

tv_r_c_groups_id_ed_group.Index],

Sender.GridTableView.DataController.Values[ARecordIndex,

tv_listeners_id_student.Index],

Sender.GridTableView.DataController.Values[ARecordIndex,

tv_listeners_id_contract.Index]]);

end; // 2

{ Параметры для изменения даты окончания }

if (FC = tv_listeners_d_end) then

begin // 3

ExchangeParameters := CreateVarAr

([Sender.GridTableView.DataController.Values[ARecordIndex,

tv_listeners_id_student.Index], VarToStr(UpdateValue)]);

end; // 3

end;

{ Добавление группы обучения}

procedure TForm_Event.ButtonEdit_Import_EGClick(Sender: TObject);

var i: integer;

begin

if Import_ID_EG = '' then

begin

MSystemServices.InsertData(d_P_EDUCATIONAL_GROUP

, CreateVarAr(['1', '2', '3', '4'])

, CreateVarAr([Import_EG_Name

, VarToStr(cW_Import.Wrappers[PopupEdit_Import_Spec_Div].Value)

, '3' // Уровень группы

, VarToStr(cW_Import.Wrappers[DateEdit_Import_Start].Value)]));

Get_SqlData_cxGrid(tv_ed_group).Read_Data;

i := FindGridRecordIndex(tv_ed_group, [tv_ed_group_name]

, CreateVarAr([Import_EG_Name]));

if i >= 0 then

begin

Import_ID_EG := VarToStr(tv_ed_group.DataController.Values[i, tv_ed_group_id_ed_group.Index]);

cW_New_R_C_Group.Wrappers[cxPopupEdit_New_R_C_Group_Ed_Group].Value := Import_ID_EG;

cW_New_R_C_Group.Wrappers[cxPopupEdit_New_R_C_Group_Ed_Group].ValueDisplay := Import_EG_Name;

end;

end

else

begin

cW_New_R_C_Group.Wrappers[cxPopupEdit_New_R_C_Group_Ed_Group].Value := Import_ID_EG;

cW_New_R_C_Group.Wrappers[cxPopupEdit_New_R_C_Group_Ed_Group].ValueDisplay := Import_EG_Name;

end;

cW_New_R_C_Group.Wrappers[cxPopupEdit_New_R_C_Group_Met_Complex].Value :=

cW_Import.Wrappers[PopupEdit_Import_UMK].Value;

cW_New_R_C_Group.Wrappers[cxPopupEdit_New_R_C_Group_Met_Complex].ValueDisplay :=

cW_Import.Wrappers[PopupEdit_Import_UMK].ValueDisplay;

cW_New_R_C_Group.Wrappers[cxDateEdit_New_R_C_Group_D_Start].Value :=

cW_Import.Wrappers[DateEdit_Import_Start].Value;

cW_New_R_C_Group.Wrappers[cxDateEdit_New_R_C_Group_D_End].Value :=

cW_Import.Wrappers[DateEdit_Import_End].Value;

// Откроем добавление группы обучения

if Action_Insert_tv_r_c_group.Execute

then

begin

i := FindGridRecordIndex(tv_r_c_groups, [tv_r_c_groups_name]

, CreateVarAr([Import_EG_Name]));

if i >= 0 then

begin

cW_Import.Wrappers[PopupEdit_Import_EG].Value :=

VarToStr(tv_r_c_groups.DataController.Values[i, tv_r_c_groups_id_ed_group.Index]);

cW_Import.Wrappers[PopupEdit_Import_EG].ValueDisplay :=

VarToStr(tv_r_c_groups.DataController.Values[i, tv_r_c_groups_name.Index]);

end;

end;

SetForegroundWindow(GetParentForm(ButtonEdit_Import_Spec).Handle);

end;

procedure TForm_Event.ButtonEdit_Import_OrderPropertiesEditValueChanged(

Sender: TObject);

begin

if (Sender = ButtonEdit_Import_Order)

then TButtonOrderEditWrapper(cW_Import.Wrappers[ButtonEdit_Import_Order]).IsEmpty := False

else if (Sender = ButtonEdit_Import_Nach)

then TButtonOrderEditWrapper(cW_Import.Wrappers[ButtonEdit_Import_Nach]).IsEmpty := False

end;

{ Добавление плана обучения}

procedure TForm_Event.ButtonEdit_Import_PlanClick(Sender: TObject);

begin

cW_New_R_C_Plan.Wrappers[cxPopupEdit_New_R_C_Plan_Spec_Div].Value :=

cW_Import.Wrappers[PopupEdit_Import_Spec_Div].Value;

cW_New_R_C_Plan.Wrappers[cxPopupEdit_New_R_C_Plan_Spec_Div].ValueDisplay :=

cW_Import.Wrappers[PopupEdit_Import_Spec_Div].ValueDisplay;

cW_New_R_C_Plan.Wrappers[cxDateEdit_New_R_C_Plan_D_Start].Value :=

cW_Import.Wrappers[DateEdit_Import_Start].Value;

if Action_Insert_tv_r_c_plan.Execute

then begin

cW_Import.Wrappers[PopupEdit_Import_Plan].Value :=

VarToStr(tv_r_c_plan.DataController.Values[tv_r_c_plan.DataController.FocusedRecordIndex

, tv_r_c_plan_id_r_c_plan.Index]);

cW_Import.Wrappers[PopupEdit_Import_Plan].ValueDisplay :=

cW_Import.wrappers[PopupEdit_Import_Spec_Div].ValueDisplay;

end;

SetForegroundWindow(GetParentForm(ButtonEdit_Import_Spec).Handle);

end;

{ Добавление программы (специальности)}

procedure TForm_Event.ButtonEdit_Import_SpecClick(Sender: TObject);

var

mf: TSForm;

i: Integer;

begin

try // 1

mf := CreateSForm(self, 'fNew_Spec', TabSheet_New_Spec.Caption,

Add_Path + TabSheet_New_Spec.Caption, Panel_New_Spec, tdb_Apply);

cW_New_Spec.wrappers[cxTextEditNew_Spec_Name].Value := Import_Name_Spec;

mf.Position := poScreenCenter;

if mf.ShowModal = mrok then

begin // 2

FSqlData_TableView := Get_SqlData_cxGrid(tv_Spec);

if FSqlData_TableView <> nil then

begin // 3

if MSystemServices.ExchangeData(Add_Spec,

CreateVarAr([cW_New_Spec.wrappers[cxTextEditNew_Spec_Name].Value,

cW_New_Spec.wrappers[cxTextEditNew_Spec_Quality].Value,

IDK_Level_Spec, IDK_Discipline]), 'Добавление программы обучения.')

then

begin // 4

FSqlData_TableView.Read_Data;

i := FindGridRecordIndex(tv_Spec, [tv_Spec_Name, tv_Spec_Q],

VarArrayOf([cW_New_Spec.wrappers[cxTextEditNew_Spec_Name].Value,

cW_New_Spec.wrappers[cxTextEditNew_Spec_Quality].Value]));

if i >= 0 then

begin

cW_Import.wrappers[PopupEdit_Import_Spec].Value := VarToStr

(tv_Spec.DataController.Values[i, tv_Spec_ID.Index]);

cW_Import.wrappers[PopupEdit_Import_Spec].ValueDisplay := VarToStr

(tv_Spec.DataController.Values[i, tv_Spec_Name.Index]);

end;

end; // 4

end; // 3

end; // 2

finally // 1

if assigned(mf) then

FreeAndNil(mf);

SetForegroundWindow(GetParentForm(ButtonEdit_Import_Spec).Handle);

end; // 1

end;

procedure TForm_Event.After_Read_tv_listeners(Sender: TSqlData_cxGridTableView);

begin

if Sender.GridTableView.DataController.RecordCount = 0 then

begin // 1

if cxPageControl_Student.ActivePage = cxTabSheet_Orders then

Get_SqlData_cxGrid(tv_orders).ClearData;

if cxPageControl_Student.ActivePage = cxTabSheet_Anketa then

Fill_AnketaExecute(self);

if cxPageControl_Student.ActivePage = cxTabSheet_Contracts then

Fill_Contract_ParamsExecute(self);

Label_FIO.Caption := '';

end; // 1

end;



Приложение В

Текст запросов

Настройка модуля (Get_Params_Module )

Select t.value_variable

From values_variables_env_rs t

Where t.id_variable_environment = %p1[i]%

And Sysdate Between t.d_start And nvl(t.d_end, Sysdate)

And t.use_sign = 1

And t.id_project = 1

Вид уровня образования (K_L_F)

Select klf.Name

,klf.idk_level_formation

,klf.qualifier

From kind_level_formation_ klf

Группы повышения квалификации (R_C_Groups)

Select eg.Name

,rcg.d_start

,rcg.d_end

,ps.Status_Text

,el.hours

,rcg.id_rc_group

,rcg.id_ed_group

,rcg.id_methodical_complex

,ps.id_paragraph

,ps.id_order

,ps.idk_order

,ps.Status_order

,rcg.id_refresher_course_plan

,ps.id_erp_user

From refresher_course_group_ rcg

,educational_group_ eg

,complex_specialities_ cs

,paragraphs_status_ ps

,methodical_complex_ mc

,educational_load_umk_ el

Where eg.id_ed_group = rcg.id_ed_group

And rcg.id_refresher_course_plan = %p1[i]%

And cs.id_methodical_complex = rcg.id_methodical_complex

And cs.id_p_dp = ps.id_paragraph

And rcg.id_methodical_complex = mc.id_methodical_complex

And el.id_methodical_complex = mc.id_methodical_complex

Добавление спец.мет.компл. (Add_Com_Spec)

Declare

id_m_c Number;

Begin

Insert Into methodical_complex_

(id_d, id_discipline)

Values

(%p1[i]%

,(Select d.id_discipline

From disciplines_ d

,specialities_division_ sd

Where d.Name Is Null

And d.id_speciality = sd.id_speciality

And sd.id_d_specialitie = %p2[i]%))

Returning id_methodical_complex Into id_m_c;

Insert Into complex_specialities_

(id_methodical_complex, id_d_specialitie, idk_discipline, id_p_dp)

Values

(id_m_c, %p2[i]%, %p6[i]%, %p3[i]%);

Insert Into Educational_Load_Umk_

(Id_Methodical_Complex, Idk_Training, Idk_Lesson, Semester, Hours)

Values

(id_m_c, %p4[i]%, 1, 1, %p5[s]%);

-- Самостоятельная работа

If %p7[i]% Is Not Null

And %p7[i]% > 0

Then

Insert Into Educational_Load_Umk_

(Id_Methodical_Complex, Idk_Training, Idk_Lesson, Semester, Hours)

Values

(id_m_c, %p4[i]%, 20, 1, %p7[i]%);

End If;

End;

Добавление специальности (Add_Spec)

Declare

id_spec Number;

Begin

Insert Into Speciality_

(Name, Qualifier, Idk_Level_Speciality)

Values

(%p1[s]%, %p2[s]%, %p3[s]%)

Returning Id_Speciality Into id_spec;

Insert Into Disciplines_

(Name, Abbreviation, Idk_Discipline, Id_Speciality)

Values

('', '', %p4[s]%, id_spec);

End;

Должности в комиссии (Get_Posts_For_Members)

Select p.Name

,p.id_post

From post_ p

Where p.id_post In (55005, 55003)

Изменение спец.мет.компл. (Update_Com_Spec)

Begin

Update complex_specialities_

Set id_d_specialitie = %p4[i]%

Where Id_Methodical_Complex = %p1[i]%

And id_d_specialitie = %p2[i]%

And id_p_dp = %p3[i]%;

Update educational_load_umk_

Set idk_training = %p5[i]%

Where Id_Methodical_Complex = %p1[i]%;

Update methodical_complex_

Set id_d = %p6[i]%

Where Id_Methodical_Complex = %p1[i]%;

End;

Источники финансирования (S_F)

Select sf.abbreviation

,sf.Name

,ts_.Get_DivisionName(sf.id_d, Sysdate, 1, 1, 2, 2)

,ts_.Get_DivisionName(sf.id_d, Sysdate, 1, 1)

,ks.Name

,ka.Name

,sf.d_end

,sf.id_source

,sf.id_d

,sf.idk_source

,sf.idk_activity

From source_financing_ sf

,attributes_divisions_ d

,kind_source_ ks

,kind_activity_ ka

,kind_activity_ kai

,kind_charges_ kc

,declination_ dc

,

-- Проверим - нужны ли закрытые источники?

(Select Case

When Exists (Select 'x'

From p_roles_current_user_ ru

Where ru.qualifier = '130') Then

'1'

Else

'0'

End qualifier

From dual) ru

Where sf.id_d = d.id_d

And (sf.d_end Is Null Or ru.qualifier = '1')

And (d.d_start = (Select Max(ad.d_start)

From attributes_divisions_ ad

Where ad.id_d = d.id_d

And ad.d_start <= Sysdate))

And sf.idk_source = ks.idk_source

And sf.idk_activity = ka.idk_activity

And sf.idk_activity_income = kai.idk_activity(+)

And sf.idk_charges = kc.idk_charges(+)

And sf.id_declination = dc.id_declination(+)

And ka.qualifier in ('2', '3')

And (%p1[i]% is null or sf.id_d = %p1[i]%)

And ks.qualifier = '2'

Комиссии (Get_List_Commissions_By_ID_D_And_ID_Spec)

Select ps.Status_Text

,ps.name_Kind_paragraph

,eh.full_fio

,c.d_start

,c.d_end

,ps.id_paragraph

,ps.id_order

,ps.idk_order

,ps.Status_order

,ps.id_erp_user

,ps.id_metaterm

,ps.id_metaobject

,c.id_speciality

,c.id_d

,c.id_commission

,c.idk_commission

From commission_ c

,paragraphs_status_ ps

,who_is_who_ wiw

,employees_history_ eh

Where c.id_speciality = %p2[i]%

And c.id_paragraph = ps.id_paragraph

And ps.id_erp_user = wiw.id_erp_user

And wiw.id_e = eh.id_e

And c.d_start Between eh.d_start And nvl(eh.d_end, c.d_start)

And c.id_d = %p1[i]%

Кураторы группы (Get_List_Curators_By_ID_EG)

Select c.d_start

,c.d_end

,e.surname || ' ' || e.Name || ' ' || e.patronymic || ', ' ||

to_char(e.date_of_birth) || ' г.р.'

,c.id_e

,c.id_ed_group

From curator_group_ c

,employees_ e

Where c.id_ed_group = %p1[i]%

And c.id_e = e.id_e

Методический комплекс (Met_Complex)

Select s.Name

,ts_.Get_DivisionName(sd.id_d_institute, Sysdate, 1, 1)

,ts_.Get_DivisionName(sd.id_d, Sysdate, 1, 1)

,kt.Name

,ps.Status_Text

,el.hours

,sd.id_d_specialitie

,sd.id_speciality

,sd.id_d_institute

,sd.id_d

,sd.idk_training

,ps.Status_order

,cs.id_p_dp

,ps.id_order

,ps.idk_order

,cs.id_methodical_complex

--,el.id_educational_load_umk

,el.sh

From complex_specialities_ cs

,specialities_division_ sd

,speciality_ s

,paragraphs_status_ ps

,kind_training_ kt

,(Select el.id_methodical_complex

--,el.id_educational_load_umk

,Sum(el.hours) hours

,Sum(Decode(el.idk_lesson, 1, 0, el.hours)) sh

From educational_load_umk_ el

Group By el.id_methodical_complex

--,el.id_educational_load_umk

) el

Where cs.id_d_specialitie = sd.id_d_specialitie

And sd.id_speciality = s.id_speciality

And kt.idk_training = sd.idk_training

And (%p1[i]% Is Null Or %p1[i]% = sd.id_d_specialitie)

And cs.id_p_dp = ps.id_paragraph

And el.id_methodical_complex = cs.id_methodical_complex

And sd.id_d_institute In

(

-- всё, если необычный пользователь

Select dL.id_d

From division_ dL

Where bs_.isUnusual > 0

Union All

-- по ролям, если обычный пользователь

Select dL.id_d

From division_ dL

Where bs_.isUnusual = 0

And dL.id_metaterm In

(Select r.id_metaterm

From Table(access_data_.Get_Roles('1403', 1)) r)

Union All

-- по ролям, если обычный пользователь

Select dL.id_d

From division_ dL

Where bs_.isUnusual = 0

And Exists (Select r.id_metaterm

From Table(access_data_.Get_Roles('1403', 1)) r

Where r.id_metaterm Is Null))

Организаторы обучения (Spec_div)

Select s.Name

,ts_.Get_DivisionName(sd.id_d, Sysdate, 1, 1)

,kt.Name

,ts_.Get_DivisionName(sd.id_d_institute, Sysdate, 1, 1, 2, 2)

,sf.Name

,sd.d_start

,sd.d_end

,sd.id_d_specialitie

,sd.id_speciality

,sd.id_d

,sd.idk_training

,sd.id_d_institute

,sd.id_source

,sd.idk_level_formation

From specialities_division_ sd

,speciality_ s

,kind_training_ kt

,source_financing_ sf

Where s.idk_level_speciality = %p1[i]%

And sd.id_speciality = s.id_speciality

And kt.idk_training = sd.idk_training

And sf.id_source(+) = sd.id_source

And (%p2[i]% Is Null Or %p2[i]% = sd.id_d_institute)

And sd.id_d_institute in

(

-- всё, если необычный пользователь

select dL.id_d from division_ dL where bs_.isUnusual > 0

union all

-- по ролям, если обычный пользователь

select dL.id_d from division_ dL

where bs_.isUnusual = 0 and dL.id_metaterm in (select r.id_metaterm from table(access_data_.Get_Roles('1402, 1403, 1404', 0)) r )

union all

-- по ролям, если обычный пользователь

select dL.id_d from division_ dL

where bs_.isUnusual = 0

and exists (select r.id_metaterm from table(access_data_.Get_Roles('1402, 1403, 1404', 0)) r where r.id_metaterm is null)

)

План пк (отчет) (Get_Data_For_Plan)

Select nvl(to_char(ps.hisdate, 'dd'), '___') День --1

,nvl(wordsv_.Month_Decline(extract(Month From ps.hisdate), 2, 1)

,'__________') Месяц --2

,to_char(nvl(ps.hisdate, Sysdate), 'yyyy') Год --3

,s.Name Программа --4

,el.hours Часы --5

,kt.Name Форма --6

,wordsv_.Month_Decline(extract(Month From rcp.d_start), 1, 1) ||

Decode(extract(Month From rcp.d_end), extract(Month From rcp.d_start), ''

, ' - ' || wordsv_.Month_Decline(extract(Month From rcp.d_end), 1, 1)) Срок --7

,wordsv_.Month_Decline(extract(Month From rcp.d_end), 1, 1) Окончание --8

,ts_.Get_DivisionName(sd.id_d, Sysdate, 1, 1) Подразделение --9

,rcp.id_paragraph --10

,ts_.Get_DivisionName(sd.id_d_institute, Sysdate, 1, 1) Организатор --11

,extract(Year From (Select Max(pod.d_start)

From (Select rcp2.d_start

From refresher_course_plan_ rcp2

Where rcp2.id_paragraph = %p1[i]%) pod)) Год --12

,replace(replace(ps.summary, 'План ', ''), 'ПЛАН ', '') -- 13

From refresher_course_plan_ rcp

,specialities_division_ sd

,speciality_ s

,kind_training_ kt

,paragraphs_status_ ps

,complex_specialities_ cs

,educational_load_umk_ el

Where rcp.id_paragraph = %p1[i]%

And sd.id_d_specialitie = rcp.id_d_specialitie

And sd.id_speciality = s.id_speciality

And kt.idk_training = sd.idk_training

And ps.id_paragraph = rcp.id_paragraph

And rcp.id_d_specialitie = cs.id_d_specialitie

And el.id_methodical_complex = cs.id_methodical_complex

Позиции плана ПК (R_C_Plan)

Select s.Name

,ts_.Get_DivisionName(sd.id_d_institute, Sysdate, 1, 1)

,ts_.Get_DivisionName(sd.id_d, Sysdate, 1, 1)

,kt.Name

,rcp.d_start

,rcp.d_end

,sf.Name

,(Select npt.money

From norms_payment_training_ npt

Where npt.id_d_specialitie = rcp.id_d_specialitie

And npt.type_value = 1

And npt.d_start Between sd.d_start And rcp.d_start)

,ps.Status_Text

,rcp.id_refresher_course_plan

,rcp.id_d_specialitie

,sd.id_speciality

,sd.id_d_institute

,sd.id_d

,sd.idk_training

,rcp.id_paragraph

,ps.id_order

,ps.idk_order

,ps.Status_order

,sd.id_source

,ps.id_erp_user

From refresher_course_plan_ rcp

,specialities_division_ sd

,speciality_ s

,kind_training_ kt

,paragraphs_status_ ps

,source_financing_ sf

Where rcp.id_d_specialitie = sd.id_d_specialitie

And sd.id_speciality = s.id_speciality

And kt.idk_training = sd.idk_training

And sd.id_d_institute in

(

-- всё, если необычный пользователь

select dL.id_d from division_ dL where bs_.isUnusual > 0

union all

-- по ролям, если обычный пользователь

select dL.id_d from division_ dL

where bs_.isUnusual = 0 and dL.id_metaterm in (select r.id_metaterm from table(access_data_.Get_Roles('1403', 1)) r )

union all

-- по ролям, если обычный пользователь

select dL.id_d from division_ dL

where bs_.isUnusual = 0

and exists (select r.id_metaterm from table(access_data_.Get_Roles('1403', 1)) r where r.id_metaterm is null)

)

And ps.id_paragraph = rcp.id_paragraph

And sd.id_source = sf.id_source(+)

And (rcp.id_paragraph = %p1[i]% Or

((%p1[s]% Is Null) And

ps.id_erp_user =

(Select w.id_erp_user From who_is_who_ w Where w.login = User)))

Программы (Get_List_Spec_By_Params)

with res_ad as

(

Select /*+no_merge*/

d.id_d

From (Select d.id_d

From (Select d.id_d

From division_ d

,Table(access_data_.Get_Roles('1401,1402,1403,1404', 0)) pr

Where pr.id_metaterm = d.id_metaterm

-- Без ограничений

Union

Select d.id_d

From division_ d

,(Select 'x'

From dual

Where (bs_.isUnusual > 0)

Or (Exists (Select 'x'

From Table(access_data_.Get_Roles('1401,1402,1403,1404'

,0))) And

Not Exists

(Select 'x'

From division_ d

,(Select id_metaterm

From Table(access_data_.Get_Roles('1401,1402,1403,1404'

,0))) pr

Where pr.id_metaterm = d.id_metaterm)))) d) d

)

Select s.Name

,s.qualifier

,s.id_speciality

,s.idk_level_speciality

,di.id_discipline

,di.idk_discipline

From speciality_ s

,specialities_division_ sd

,res_ad

,disciplines_ di

Where s.idk_level_speciality = %p1[i]% --70

And s.id_speciality = sd.id_speciality(+)

And sd.id_d_institute = res_ad.id_d(+)

And (sd.id_d_institute Is Null Or res_ad.id_d Is Not Null)

And (%p2[s]% Is Null Or %p2[s]% = sd.id_d_institute)

And di.id_speciality(+) = s.id_speciality

Group By s.Name

,s.qualifier

,s.id_speciality

,s.idk_level_speciality

,di.id_discipline

,di.idk_discipline

Специальности мет.комл. (Com_Spec)

Select s.Name

,ts_.Get_DivisionName(sd.id_d, Sysdate, 1, 1)

,kt.Name

,ps.Status_Text

,el.hours

,sd.id_d_specialitie

,sd.id_speciality

,sd.id_d

,sd.idk_training

,ps.Status_order

,cs.id_p_dp

,ps.id_order

,ps.idk_order

,cs.id_methodical_complex

,el.id_educational_load_umk

,ps.id_erp_user

From complex_specialities_ cs

,specialities_division_ sd

,speciality_ s

,paragraphs_status_ ps

,kind_training_ kt

,educational_load_umk_ el

Where cs.id_d_specialitie = sd.id_d_specialitie

And sd.id_speciality = s.id_speciality

And kt.idk_training = sd.idk_training

And (%p1[i]% Is Null Or %p1[i]% = sd.id_d_institute)

And cs.id_p_dp = ps.id_paragraph

And el.id_methodical_complex = cs.id_methodical_complex

Список институтов (Get_List_Institutes)

with res_ad as

(

Select /*+no_merge*/

d.id_d

From (Select d.id_d

From (Select d.id_d

From division_ d

,Table(access_data_.Get_Roles('1401,1402,1403,1404', 1)) pr

Where pr.id_metaterm = d.id_metaterm

-- Без ограничений

Union

Select d.id_d

From division_ d

,(Select 'x'

From dual

Where (bs_.isUnusual > 0)

Or (Exists (Select 'x'

From Table(access_data_.Get_Roles('1401,1402,1403,1404'

,1))) And

Not Exists

(Select 'x'

From division_ d

,(Select id_metaterm

From Table(access_data_.Get_Roles('1401,1402,1403,1404'

,1))) pr

Where pr.id_metaterm = d.id_metaterm)))) d) d

)

Select ts_.Get_DivisionName(res.id_d, Sysdate, 1, 1, 2, 2)

,ts_.Get_DivisionName(res.id_d, Sysdate, 1, 1)

,res.id_d

From res_ad res

Where %p1[i]% = %p1[i]%

Список организаторов(подр) (Get_List_Divisions)

with res_ad as

(

Select /*+no_merge*/

d.id_d

From (Select d.id_d

From (Select d.id_d

From division_ d

/*,Table(access_data_.Get_Roles('1401,1402,1403,1404', 0)) pr

Where pr.id_metaterm = d.id_metaterm

-- Без ограничений

Union

Select d.id_d

From division_ d

,(Select 'x'

From dual

Where (bs_.isUnusual > 0)

Or (Exists (Select 'x'

From Table(access_data_.Get_Roles('1401,1402,1403,1404'

,1))) And

Not Exists

(Select 'x'

From division_ d

,(Select id_metaterm

From Table(access_data_.Get_Roles('1401,1402,1403,1404'

,1))) pr

Where pr.id_metaterm = d.id_metaterm)))*/) d) d

)

Select ts_.Get_DivisionName(res.id_d, Sysdate, 1, 1, 2, 2)

,ts_.Get_DivisionName(res.id_d, Sysdate, 1, 1)

,res.id_d

From res_ad res

Where %p1[i]% = %p1[i]%

Удаление спец.мет.компл. (Delete_Com_Spec)

Begin

Delete From complex_specialities_

Where Id_Methodical_Complex = %p1[i]%

And id_d_specialitie = %p2[i]%

And id_p_dp = %p3[i]%;

Delete From educational_load_umk_ Where Id_Methodical_Complex = %p1[i%;

Delete From methodical_complex_ Where Id_Methodical_Complex = %p1[i]%;

End;

Удаление специальности (Delete_Spec)

Begin

Delete From Disciplines_ d

Where d.id_speciality = %p1[i]%

And d.Name Is Null;

Delete From Speciality_ s Where s.id_speciality = %p1[i]%;

End;

Учебные группы (Ed_Groups)

Select eg.Name

,kg.Name

,eg.d_start

,eg.d_end

,eg.id_ed_group

,eg.id_d_specialitie

,eg.idk_group

,eg.level_group

From educational_group_ eg

,kind_group_ kg

Where eg.id_d_specialitie = %p1[i]%

And kg.idk_group(+) = eg.idk_group

Члены комиссии (Get_Members_Commission_By_ID)

Select ps.Status_Text -- 1

,ps.name_Kind_paragraph -- 2

,p.name -- 3

,eh.full_fio -- 4

,ehw.full_fio -- 5

,cm.d_start -- 6

,cm.d_end -- 7

,ps.id_paragraph -- 8

,ps.id_order -- 9

,ps.idk_order -- 10

,ps.Status_order -- 11

,ps.id_erp_user -- 12

,ps.id_metaterm -- 13

,ps.id_metaobject -- 14

,cm.id_post -- 15

,cm.id_commission -- 16

,cm.id_e -- 17

,cm.id_commission_member -- 18

From commission_members_ cm

,paragraphs_status_ ps

,who_is_who_ wiw

,employees_history_ ehw

,employees_history_ eh

,post_ p

Where cm.id_commission = %p1[i]%

And cm.id_paragraph_start = ps.id_paragraph

And ps.id_erp_user = wiw.id_erp_user

And wiw.id_e = ehw.id_e

And cm.d_start Between ehw.d_start And nvl(ehw.d_end, cm.d_start)

And cm.id_post = p.id_post

And cm.id_e = eh.id_e

And cm.d_start Between eh.d_start And nvl(eh.d_end, cm.d_start)

Должности (Posts)

Select p.Name

,p.id_post

From post_ p

Group By p.id_post

,p.Name

Изменение группы(даты) (Update_Ed_Group)

Begin

Update Students_ s

Set s.d_start = %p1[d]%, s.id_ed_group = %p2[i]%

Where s.id_student = %p3[i]%;

Update contracts_ c

Set c.d_start = %p1[d]%

Where c.id_contract = %p4[i]%

And c.d_start != %p1[d]%;

Update stages_ s

Set s.d_start = %p1[d]%

Where s.id_contract = %p3[i]%

And s.d_start != %p1[d]%;

Update contract_sources_ cs

Set cs.d_start = %p1[d]%

Where cs.id_contract = %p2[i]%

And cs.d_start != %p1[d]%;

End;

Изменение даты оконч. (Update_End_Date)

Begin

Update orders_students_ os

Set os.d_start = %p2[d]%

Where os.id_paragraph =

(Select ps.id_paragraph

From orders_students_ os2, paragraphs_status_ ps

Where os2.id_student = %p1[i]%

And os2.id_paragraph = ps.id_paragraph

And ps.idk_order In (201, 207));

End;

Изменение плана (Update_Plan)

Update orders_ o

Set o.note = %p1[s]%, o.summary = %p2[s]%

Where o.id_order =

(Select p.id_order From paragraphs_ p Where p.id_paragraph = %p3[i]%)

Изменение ФИО (Update_FIO)

Declare

is_nach Number;

Begin

Select Min(1)

Into is_nach

From dual

Where Exists (Select 'x'

From accounts_contract_ ac

,stages_ st

,salary_account_ sa

,paragraphs_status_ ps

Where ac.id_stage = st.id_stage

And ac.id_basis_sal = sa.id_salary_account

And st.id_contract = %p3[i]%

And ac.id_paragraph = ps.id_paragraph

And ps.Status_order <> 2);

If is_nach Is Not Null

Then

raise_application_error(-20101

,'В договоре обнаружены подписанные проводки!');

End If;

Update Students_ s Set s.id_e = %p1[i]% Where s.id_student = %p2[i]%;

Update Contracts_ c

Set c.id_with_whom = (Select e.id_contractor

From employees_ e

Where e.id_e = %p1[i]%)

Where c.id_contract = %p3[i]%;

Update account_ a

Set a.id_e = %p1[i]%

Where a.id_account In (Select a.id_account

From accounts_contract_ ac

,stages_ st

,salary_account_ sa

,paragraphs_status_ ps

,account_ a

Where ac.id_stage = st.id_stage

And ac.id_basis_sal = sa.id_salary_account

And st.id_contract = %p3[i]%

And ac.id_paragraph = ps.id_paragraph

And sa.id_account = a.id_account

And ps.Status_order <> 2);

End;

Основания зачисления (Kind_Basis_Action)

Select k.Name

,k.idk_basis_action

From kind_basis_action_ k

Where k.idk_basis_action = 1700

Основания окончания (End_Education_KBA)

Select k.Name

,k.idk_basis_action

From kind_basis_action_ k

Where k.idk_b_a_main = Decode(%p1[i]%, 201, 2000, 6000)

Параграфы планов ПК (Plan_Paragraphs)

Select ps.note

,ps.summary

,ps.Status_Text

,ps.hisdate

,ps.d_creation

,ps.d_modification

,ps.id_paragraph

,ps.id_order

,ps.idk_order

,ps.Status_order

,ps.id_erp_user

From paragraphs_status_ ps

Where ps.idk_order = 902

And ps.d_creation >= to_date(concat('01.01.', %p1[s]%), 'dd.mm.yyyy')

And ps.d_creation <= to_date(concat('31.12.', %p1[s]%), 'dd.mm.yyyy')

Приказы человека (Orders)

Select

ps.name_kind_paragraph

,ps.Status_Text

,eg.Name

,Case

When sp.idk_level_speciality In (6, 24, 34) Then

spm.Name || '. '

End || sp.Name Name

,os.d_start

,kt.Name

,ts_.Get_DivisionName(sd.id_d_institute, Sysdate, 1, 1)

,ps2.Status_Text

,ps2.name_Kind_paragraph

,ps.d_creation

,ps.d_modification

,ps.id_order

,ps.id_paragraph

,ps.idk_order

,ps.status_order

From students_ s

,orders_students_ os

,paragraphs_status_ ps

,paragraphs_status_ ps2

,student_groups_ eg

,speciality_ sp

,speciality_ spm

,specialities_division_ sd

,kind_training_ kt

,contracts_ c

Where s.id_e = %p1[i]%

And os.id_student = s.id_student

And ps.id_paragraph = os.id_paragraph

And eg.id_ed_group = s.id_ed_group

And eg.id_d_specialitie = sd.id_d_specialitie

And sp.id_speciality = sd.id_speciality

And sp.idk_level_speciality = 70

And kt.idk_training = sd.idk_training

And c.id_paragraph(+) = s.id_order_contract

And c.id_paragraph = ps2.id_paragraph(+)

And sp.parent_kind_speciality = spm.id_speciality(+)

Union

Select

ps.name_kind_paragraph

,ps.Status_Text

,eg.Name

,Case

When sp.idk_level_speciality In (6, 24, 34) Then

spm.Name || '. '

End || sp.Name Name

,s.d_start

,kt.Name

,ts_.Get_DivisionName(sd.id_d_institute, Sysdate, 1, 1)

,ps2.Status_Text

,ps2.name_Kind_paragraph

,ps.d_creation

,ps.d_modification

,ps.id_order

,ps.id_paragraph

,ps.idk_order

,ps.status_order

From students_ s

,paragraphs_status_ ps

,paragraphs_status_ ps2

,student_groups_ eg

,speciality_ sp

,speciality_ spm

,specialities_division_ sd

,kind_training_ kt

,contracts_ c

Where s.id_e = %p1[i]%

And ps.id_paragraph = s.id_order_start

And eg.id_ed_group = s.id_ed_group

And eg.id_d_specialitie = sd.id_d_specialitie

And sp.id_speciality = sd.id_speciality

And sp.idk_level_speciality = 70

And kt.idk_training = sd.idk_training

And c.id_paragraph(+) = s.id_order_contract

And c.id_paragraph = ps2.id_paragraph(+)

And sp.parent_kind_speciality = spm.id_speciality(+)

UNION ALL

Select ps.name_kind_paragraph

,ps.Status_Text

,null

,null

,o.d_end

,null

,null

,null

,null

,ps.d_creation

,ps.d_modification

,ps.id_order

,ps.id_paragraph

,ps.idk_order

,ps.status_order

From old_surnames_ o

,paragraphs_status_ ps

Where o.id_e = %p1[i]%

And o.id_paragraph = ps.id_paragraph

Ссылка на договор (Contracts)

Select ps.Status_Text

,ko.Name

,c.d_start

,c.d_end

,c.id_contract

,c.id_paragraph

,ps.id_order

,ps.idk_order

,ps.Status_order

From contracts_ c

,employees_ e

,paragraphs_status_ ps

,kind_order_ ko

Where c.id_with_whom = e.id_contractor

And ps.id_paragraph = c.id_paragraph

And ps.idk_order = %p1[i]%

And ps.idk_order = ko.idk_order

And e.id_e = %p2[i]%

Сформировать начисление (Create_New_Nach)

Declare

i Number;

Begin

Select nvl(pay_education_.Go_Account_Contract(st.id_contract

,st.d_start

,st.d_end

,%p2[d]%

,%p3[i]%

,st.id_stage

,0

,0)

,0)

Into i

From stages_ st

Where st.id_contract = %p1[i]%;

End;

Удаление студента (Delete_Student)

Declare

id_p Number;

id_o Number;

Begin

-- Удаляем договор, если он есть

If %p1[i]% Is Not Null

Then

Select c.id_paragraph

Into id_p

From Contracts_ c

Where c.id_contract = %p1[i]%;

Select p.id_order Into id_o From Paragraphs_ p Where p.id_paragraph = id_p;

Delete From specialities_contract_ sc Where sc.id_contract = %p1[i]%;

Delete From contract_sources_ cs Where cs.id_contract = %p1[i]%;

-- Попытаемся удалить все сформированные начисления (если они не подписаны)

For cur In (Select sa.id_account

,sa.id_salary_account

From salary_account_ sa

,accounts_contract_ ac

,stages_ st

Where sa.id_basis_sal_charge = ac.id_basis_sal

And ac.id_stage = st.id_stage

And st.id_contract = %p1[i]%) Loop

-- Удаляем проводки

Delete From spent_ s

Where s.id_salary_account = cur.id_salary_account;

-- Удаляем позицию расчёта

Delete From salary_account_ sa

Where sa.id_salary_account = cur.id_salary_account;

-- В расчёте может быть ещё 1 запись в позициях расчёта по НДСу

Delete From account_ a

Where a.id_account = cur.id_account

And Not Exists (Select 'x'

From salary_account_ sa

Where sa.id_account = cur.id_account);

End Loop;

-- Теперь удаляем все направления и основания начисления!

For cur In (Select ac.id_basis_sal

,ac.id_account_contract

From accounts_contract_ ac

,stages_ st

Where st.id_contract = %p1[i]%

And st.id_stage = ac.id_stage) Loop

-- счёт по этапу договору

Delete From accounts_contract_ ac

Where ac.id_account_contract = cur.id_account_contract;

-- основание по этому счёту

Delete From basis_salary_ bs

Where bs.id_basis_sal = cur.id_basis_sal;

End Loop;

-- Этапы в договоре

Delete From stages_ s Where s.id_contract = %p1[i]%;

-- Представители в договоре

Delete From representatives_ r Where r.id_paragraph = id_p;

-- Наконец, сам договор

Delete From contracts_ c Where c.id_contract = %p1[i]%;

-- Обнуляем ссылку на договор, иначе удалить документ договора нельзя

Update Students_ s Set s.id_order_contract = null Where s.id_order_contract = id_p;

Delete From paragraphs_ p Where p.id_paragraph = id_p;

Delete From orders_ o Where o.id_order = id_o;

End If;

--Удаляем студента и все его приказы

For cur In (Select os.id_paragraph

,p.id_order

From Orders_Students_ os

,paragraphs_ p

Where os.id_student = %p2[i]%

And p.id_paragraph = os.id_paragraph)

Loop

Begin

Delete From orders_students_ os

Where os.id_paragraph = cur.id_paragraph;

Delete From paragraphs_ p Where p.id_paragraph = cur.id_paragraph;

Delete From orders_ o Where o.id_order = cur.id_order;

End;

End Loop;

Delete From students_ s Where s.id_student = %p2[i]%;

End;

Участники (Listeners)

Select e.full_fio

,eg.Name

,s.d_start

,(Select os2.d_start

From orders_students_ os2

,paragraphs_ p

Where os2.id_student = s.id_student

And os2.id_paragraph = p.id_paragraph

And p.idk_order In (201, 207))

,rcg.d_end

,kba.Name

,ps.Status_Text

,(Select ps2.Status_Text

From orders_students_ os2

,paragraphs_status_ ps2

Where os2.id_student = s.id_student

And os2.id_paragraph = ps2.id_paragraph

And ps2.idk_order In (201, 207))

,psc.status_text

,(Select Max(ts_.Get_Org_Name(o.id_organization, Sysdate))

From representatives_ r

,organizations_ o

Where r.id_represented = c.id_with_whom

And r.id_representative = o.id_contractor)

,/*nvl(*/(Select Sum(sa.money)

From stages_ st

,accounts_contract_ ac

,salary_account_ sa

,spent_ s

,paragraphs_ p

Where st.id_contract = c.id_contract

And st.id_stage = ac.id_stage

And ac.id_basis_sal = sa.id_basis_sal_charge

And sa.id_salary_account = s.id_salary_account

And ac.id_paragraph = p.id_paragraph

And p.idk_order In (444, 436))

/*,(Select Sum(st.money)

From stages_ st

Where st.id_contract = c.id_contract))*/

,d.series || Decode(d.series, '', '', ' ') || d.identifier

,ct.heard_hours

,rcg.hours

,(Select Max(ct2.when_given1)

From certificate_ ct2

,kind_level_formation_ klf

Where ct2.id_e1 = s.id_e

And ct2.idk_level_formation = klf.idk_level_formation

And klf.qualifier = 96

And ct2.when_given1 <= s.d_start)

,s.id_student

,e.id_e

,eg.id_ed_group

,s.idkba_transfer

,s.id_order_start

,ps.id_order

,ps.idk_order

,c.id_contract

,ps.Status_order

,s.id_order_contract

,ps.id_erp_user

,e.id_contractor

-- Рамочный договор

,decode(cm.id_contract

,''

,''

,nvl((Select pc.Value

From parameters_contract_ pc

Where pc.idk_parameter_contract = 4

And pc.id_contract = cm.id_contract)

,psm.Status_Text)) Номер_рд

,cm.id_contract

,decode(cm.id_contract

,''

,''

,(Select ts_.Get_Org_Name(o.id_organization, c.d_start)

From organizations_ o

Where o.id_contractor = cm.id_with_whom))

,(Select Sum(sa.money)

From stages_ st

,accounts_contract_ ac

,salary_account_ sa

,spent_ s

,paragraphs_ p

Where st.id_contract = c.id_contract

And st.id_stage = ac.id_stage

And ac.id_basis_sal = sa.id_basis_sal_charge

And sa.id_salary_account = s.id_salary_account

And ac.id_paragraph = p.id_paragraph

And sa.id_charge = 40811 -- НДС

And p.idk_order In (444, 436))

,(Select ps2.id_paragraph

From orders_students_ os2

,paragraphs_status_ ps2

Where os2.id_student = s.id_student

And os2.id_paragraph = ps2.id_paragraph

And ps2.idk_order In (201, 207))

,(Select ps2.id_erp_user

From orders_students_ os2

,paragraphs_status_ ps2

Where os2.id_student = s.id_student

And os2.id_paragraph = ps2.id_paragraph

And ps2.idk_order In (201, 207))

,(Select ps2.Status_order

From orders_students_ os2

,paragraphs_status_ ps2

Where os2.id_student = s.id_student

And os2.id_paragraph = ps2.id_paragraph

And ps2.idk_order In (201, 207))

,ct.id_certificate

,d.id_doc

,kd.name

,kd.idk_doc

,d.when_given

,m.registration_number

,m.id_mag_r_diplom

From students_ s

,employees_history_ e

,educational_group_ eg

,kind_basis_action_ kba

,paragraphs_status_ ps

,paragraphs_status_ psc

,contracts_ c

,contracts_ cm

,paragraphs_status_ psm

,magazine_registr_diplomas_ m

,documents_ d

,kind_document_ kd

,certificate_ ct

-- Сроки обучения

,(Select rcg2.id_ed_group

,Min(rcg2.d_start) d_start

,Max(rcg2.d_end) d_end

,sum(elumk.hours) hours

From refresher_course_group_ rcg2

,educational_load_umk_ elumk

Where rcg2.id_refresher_course_plan = %p1[i]%

And elumk.id_methodical_complex(+) = rcg2.id_methodical_complex

Group By rcg2.id_ed_group

--,elumk.hours

) rcg

Where e.id_e = s.id_e

And s.d_start Between e.d_start And nvl(e.d_end, s.d_start)

And s.id_ed_group = rcg.id_ed_group

And s.d_start Between rcg.d_start And nvl(rcg.d_end, s.d_start)

And eg.id_ed_group = s.id_ed_group

And kba.idk_basis_action = s.idkba_transfer

And ps.id_paragraph = s.id_order_start

And psc.id_paragraph(+) = s.id_order_contract

And c.id_paragraph(+) = psc.id_paragraph

And c.id_contract_main = cm.id_contract(+)

And cm.id_paragraph = psm.id_paragraph(+)

And m.id_student(+) = s.id_student

And m.id_certificate = ct.id_certificate(+)

And d.id_doc(+) = ct.id_doc

And d.idk_doc = kd.idk_doc(+)

Виды сертификатов (Get_Kind_Documents)

Select kd.Name

,kd.idk_doc

From kind_document_ kd

Where kd.idk_doc In (474151, 474152, 474153)

Выдать сертификат об окончании (Make_New_Cert_By_Params)

Declare

new_id_doc Number;

new_id_cert Number;

Begin

-- Добавляем документ

Insert Into documents_

(idk_doc, when_given, identifier, who_given, id_e)

Values

(%p4[i]%

,(Select Max(os.d_start)

From orders_students_ os

Where os.id_student = %p1[i]%)

,%p5[i]%

,ts_.Get_Header(13

,(Select Max(os.d_start)

From orders_students_ os

Where os.id_student = %p1[i]%))

,(Select st.id_e From students_ st Where st.id_student = %p1[i]%))

Returning id_doc Into new_id_doc;

-- Добавляем в него сертификат

Insert Into certificate_

(d_start

,d_end

,id_speciality

,id_doc

,idk_level_formation

,heard_hours)

Values

((Select st.d_start From students_ st Where st.id_student = %p1[i]%)

,(Select Max(os.d_start)

From orders_students_ os

Where os.id_student = %p1[i]%)

,(Select sg.id_speciality

From students_ s

,student_groups_ sg

Where s.id_student = %p1[i]%

And s.id_ed_group = sg.id_ed_group)

,new_id_doc

,(Select sg.idk_level_formation

From students_ s

,student_groups_ sg

Where s.id_student = %p1[i]%

And s.id_ed_group = sg.id_ed_group)

,%p6[i]%)

Returning id_certificate Into new_id_cert;

-- Теперь - регистрируем его в журнале выдачи дипломов

Insert Into magazine_registr_diplomas_

(id_paragraph_magazine

,id_certificate

,registration_number

,id_student

,id_e)

Values

(%p2[i]%

,new_id_cert

,%p3[i]%

,%p1[i]%

,(Select st.id_e From students_ st Where st.id_student = %p1[i]%));

End;

Забрать сертификат (Delete_Certificate)

Declare

id_d Number;

Begin

Select cr.id_doc

Into id_d

From certificate_ cr

Where cr.id_certificate = %p1[i]%;

Delete From magazine_registr_diplomas_ m Where m.id_certificate = %p1[i]%;

Delete From certificate_ cr Where cr.id_certificate = %p1[i]%;

Delete From documents_ d Where d.id_doc = id_d;

End;

Поиск журнала (Find_Magazine_Reg_Dip)

Select ps.id_paragraph

,ps.idk_order

,ps.name_Kind_paragraph

,ps.id_metaterm

,ps.id_order

,ps.temp_identifier

,ps.identifier

,ps.hisdate

,ps.Status_order

,ps.Status_Text

,ps.d_creation

,ps.id_erp_user

,''

From magazine_registr_diplomas_ m

,paragraphs_status_ ps

,students_ st

,student_groups_ eg

Where m.id_paragraph_magazine = ps.id_paragraph

And m.id_student = st.id_student

And st.id_ed_group = eg.id_ed_group

And eg.idk_level_speciality = 70

And rownum = 1

Регистрационный номер в журнале (Get_Reg_Number_By_ID_Paragraph)

Select (Select Max(m.registration_number)

From magazine_registr_diplomas_ m

Where m.id_paragraph_magazine = %p1[i]%)

,(Select Max(to_number(doc.identifier))

From magazine_registr_diplomas_ m

,students_ st

,student_groups_ eg

,certificate_ cr

,documents_ doc

Where m.id_paragraph_magazine = %p1[i]%--2434125

And m.id_student = st.id_student

And st.id_ed_group = eg.id_ed_group

And eg.idk_level_speciality = 70

And m.id_certificate = cr.id_certificate

And doc.idk_doc = %p2[i]%--474152

And cr.id_doc = doc.id_doc)

From dual

Автопредставитель (Autorepr)

begin

-- Сперва удалим того представителя

delete from representatives_ r

where r.id_contract = %p1[i]%

and r.id_represented = (Select c.id_who From contracts_ c Where c.id_contract = %p1[i]%)

and r.d_start = (Select c.d_start From contracts_ c Where c.id_contract = %p1[i]%);

-- Потом - добавим нового

insert into representatives_ r

(id_contract,

id_represented,

id_representative,

d_start,

id_paragraph,

power_of_attorney,

id_post)

(select c.id_contract,

c.id_who,

emp.id_contractor,

c.d_start,

c.id_paragraph,

nvl((Select 'доверенности от ' ||

to_char(doc.when_given, 'dd.mm.yyyy') || 'г. № ' ||

doc.identifier

From documents_ doc, kind_document_ kd

Where kd.idk_doc = doc.idk_doc

and doc.idk_doc = 26

and doc.id_e = emp.id_e

and doc.when_given =

(Select max(d.when_given)

From documents_ d

Where d.idk_doc = doc.idk_doc

and d.id_e = doc.id_e

and d.when_given <= sysdate)

and rownum = 1),

'Устава университета'),

pc.id_post

from contracts_ c,

paragraphs_ p,

who_signed_ ws,

staff_ st,

employees_ emp,

units_staff_ us,

posts_categories_ pc

where c.id_contract = %p1[i]%

and c.id_paragraph = p.id_paragraph

and p.idk_order >= 400

and p.id_order = ws.id_order

and ws.id_staff = st.id_staff

and ws.index_record = 1

and emp.id_e = st.id_e

and st.id_units = us.id_units

and us.idg_post = pc.idg_post

and rownum = 1);

end;

Виды договоров (Kind_Order)

Select ko.Name

,ko.idk_order

From kind_order_ ko

Where ko.idk_order = 68924

Виды направлений (Kind_Direction)

Select ko.Name, ko.idk_order

From kind_order_ ko

Where ko.idk_order In (431, 433, 434, 62)

Добавить направление (Add_Direction)

Insert Into Accounts_Contract_

(Id_Paragraph, Id_Stage, Money, d_Start, d_End)

(Select %p1[i]%

,s.id_stage

,s.money

,s.d_start

,s.d_end

From Stages_ s

Where s.id_contract = %p2[i]%)

Добавление договора (Add_Contract)

Declare

id_c Number;

Begin

-- Договор

Insert Into contracts_

(id_with_whom, id_who, d_start, d_end, id_paragraph, id_source)

Values

((Select e.id_contractor From employees_ e Where e.id_e = %p1[i]%)

,(Select o.id_contractor

From values_setup_ vs

,organizations_ o

Where vs.id_manual = 100

And vs.Value = o.id_organization

And vs.d_start =

(Select Max(vs2.d_start)

From values_setup_ vs2

Where vs2.d_start <= Sysdate

And vs2.id_manual = vs.id_manual))

,%p3[d]%

,%p4[d]%

,%p5[i]%

,%p6[i]%)

Returning id_contract Into id_c;

-- Этап с суммой

Insert Into stages_

(id_contract, id_paragraph, d_start, d_end, d_opening, subject, money, identifier)

Values

(id_c

,%p5[i]%

,%p3[d]%

,%p4[d]%

,%p3[d]%

,'Повышение квалификации'

,%p11[s]%

,1);

Insert Into contract_sources_

(id_contract, id_type_value, id_source, d_start, Value)

Values

(id_c, 2, %p6[i]%, %p3[d]%, 100);

Insert Into specialities_contract_

(id_contract, id_d_specialitie, idk_status_trainee, d_start)

Values

(id_c, %p7[i]%, 1, %p3[d]%);

-- Представитель студента - организация

If %p2[i]% is not null

Then

Insert Into representatives_ r

(id_contract

,id_represented

,id_representative

,d_start

,id_paragraph

,r.power_of_attorney

,r.representative_organization

,r.representative_organization_r

,r.id_post)

Select c.id_contract

,c.id_with_whom

,%p2[i]%

,%p3[d]%

,c.id_paragraph

,%p13[s]%

,%p14[s]%

,%p15[s]%

,%p16[i]%

From contracts_ c

Where c.id_contract = id_c;

End If;

-- Представитель МИИТа по первой подписи в договоре

Insert Into representatives_ r

(id_contract

,id_represented

,id_representative

,d_start

,id_paragraph

,power_of_attorney

,id_post)

(Select c.id_contract

,c.id_who

,emp.id_contractor

,c.d_start

,c.id_paragraph

,nvl((Select 'доверенности от ' ||

to_char(doc.when_given, 'dd.mm.yyyy') || 'г. № ' ||

doc.identifier

From documents_ doc

,kind_document_ kd

Where kd.idk_doc = doc.idk_doc

And doc.idk_doc = 26

And doc.id_e = emp.id_e

And doc.when_given =

(Select Max(d.when_given)

From documents_ d

Where d.idk_doc = doc.idk_doc

And d.id_e = doc.id_e

And d.when_given <= Sysdate)

And rownum = 1)

,'Устава университета')

,pc.id_post

From contracts_ c

,paragraphs_ p

,who_signed_ ws

,staff_ st

,employees_ emp

,units_staff_ us

,posts_categories_ pc

Where c.id_contract = id_c

And c.id_paragraph = p.id_paragraph

And p.idk_order >= 400

And p.id_order = ws.id_order

And ws.id_staff = st.id_staff

And ws.index_record = 1

And emp.id_e = st.id_e

And st.id_units = us.id_units

And us.idg_post = pc.idg_post

And rownum = 1);

If %p12[s]% is not null

Then

Update students_ s Set s.id_order_contract = %p5[i]% Where s.id_student = %p12[s]% ;