Search options (Page 1 of 6)

procedure balans (Sender: TObject; var Cancel: boolean);
var
  frxDBDataset1,frxDBDataset2: TfrxDBDataset;
  ResultO, ResultZ: TDataSet;
  strokaO,strokaZ, L1,L2,L3,L4,L5,L6,L7,L8,L9,L10,L11,L12: string;
  sFile, sDest, pahtOtch,nameF: string;
 begin
   pathOtch:='D:\MySQL_Balans\_Otchet';
   monthN:=SQLEXecute('SELECT month.nMonth FROM memory LEFT JOIN month ON memory.id_month=month.id');
   god:=SQLEXecute('SELECT spr_year.year FROM memory LEFT JOIN spr_year ON memory.id_spr_year=spr_year.id');
//ShowMessage(monthN);
//ShowMessage(god);
    Menu.frxReport.Variables.Clear;
    Menu.frxReport.Variables['Components']:=Null;
    Menu.frxReport.Variables['IdMes']:=mes;
    Menu.frxReport.Variables['IdYear']:=year;
    Menu.frxReport.Variables['MesText']:=''''+monthN+'''';
    Menu.frxReport.Variables['YearText']:=''''+god+'''';
L1:='SELECT DISTINCT filiali.nfilial,klass.nKlass AS nKlass,balans.id_klass AS id_klass,balans.nSubs AS nSubs,balans.ODebNm AS ODebNm,balans.OKreNm AS OKreNm,balans.ObD AS ObD,balans.ObK AS ObK,';
L2:='balans.ODebKm AS ODebKm,balans.OKreKm AS OKreKm,balans.grup1 AS grup1,balans.grup2 AS grup2,balans.grup4 AS grup4,balans.grup5 AS grup5,balans.grup3 AS grup3,balans.npp AS npp,';
L3:='balans.klass_npp AS klass_npp,gruppa1_.uroven1 AS uroven1,gruppa2_.uroven2 AS uroven2,';
L4:='gruppa4_.uroven4 AS uroven4,gruppa5_.uroven5 AS uroven5,gruppa3_.uroven3 AS uroven3,gruppa6_.uroven6 AS uroven6, month.nMonth,spr_year.year FROM balans';
L5:=' LEFT OUTER JOIN klass ON balans.id_klass=klass.id LEFT OUTER JOIN sub_schet ON balans.nSubs = sub_schet.nSubs ';
L6:=' LEFT OUTER JOIN gruppa1_ ON gruppa1_.id=sub_schet.id_gruppa1_ LEFT OUTER JOIN gruppa2_ ON gruppa2_.id=sub_schet.id_gruppa2_ ';
L7:=' LEFT OUTER JOIN gruppa3_ ON gruppa3_.id=sub_schet.id_gruppa3_ LEFT OUTER JOIN gruppa4_ ON gruppa4_.id=sub_schet.id_gruppa4_ ';
L8:=' LEFT OUTER JOIN gruppa5_ ON gruppa5_.id=sub_schet.id_gruppa5_ LEFT OUTER JOIN gruppa6_ ON gruppa6_.id=sub_schet.id_gruppa6_ ';
L9:=' LEFT OUTER JOIN filiali ON filiali.id = sub_schet.id_filiali LEFT OUTER JOIN spr_year ON spr_year.id = balans.id_spr_year LEFT OUTER JOIN month ON month.id = balans.id_month ';
L10:=' WHERE sub_schet.isActual=1 and sub_schet.isDelete=0 and gruppa6_.uroven6=1 ';           //основной баланс
L12:=' WHERE sub_schet.isActual=1 and sub_schet.isDelete=0 and sub_schet.id_gruppa6_ IS NULL ';           //забаланс
L11:=' ORDER BY gruppa1_.uroven1 ASC, klass.npp ASC, sub_schet.npp ASC, gruppa2_.uroven2 ASC, gruppa4_.uroven4 ASC, gruppa5_.uroven5 ASC';

strokaO:=L1+L2+L3+L4+L5+L6+L7+L8+L9+L10+L11;
strokaZ:=L1+L2+L3+L4+L5+L6+L7+L8+L9+L12+L11;

SQLQuery(strokaO, ResultO);
SQLQuery(strokaZ, ResultZ);

    frxDBDataset1 := TfrxDBDataset.Create(Menu);
    frxDBDataset1.UserName        := 'ResultO';
    frxDBDataset1.CloseDataSource := True;
    frxDBDataset1.OpenDataSource  := True;
    frxDBDataset1.DataSet         := ResultO;

    frxDBDataset2 := TfrxDBDataset.Create(Menu);
    frxDBDataset2.UserName        := 'ResulZ';
    frxDBDataset2.CloseDataSource := True;
    frxDBDataset2.OpenDataSource  := True;
    frxDBDataset2.DataSet         := ResultZ;


    Menu.frxReport.Clear;
    Menu.frxReport.DataSets.Clear;
    Menu.frxReport.DataSets.Add(frxDBDataset1);
    Menu.frxReport.DataSets.Add(frxDBDataset2);

     if mes<10 then
     begin
        sDest:=pathOtch+'\0'+IntToStr(mes)+'\';
     end
     else begin
        sDest:=pathOtch+'\'+IntToStr(mes)+'\';
     end;
     if ForceDirectories(sDest) then
        begin
        Menu.frxReport.LoadFromFile('Report\balans4.fr3');
        Menu.frxReport.PrepareReport;

  //     Menu.frxReport.DesignReport;
 //        Menu.frxReport.ShowReport;
      if print_otch.CheckBox1.Checked = True then
      begin
      nameF:=sDest+'Баланс за '+monthN+' '+god+'.pdf';
      ExportFilter(Menu.frxPDFExport1, nameF);              //перенаправление вывода отчета в PDF
      sFile:=Menu.frxPDFExport1.Filename;
      end;
      if print_otch.CheckBox2.Checked = True then
      begin
      nameF:=sDest+'Баланс за '+monthN+' '+god+'.doc';
      ExportFilter(Menu.frxRTFExport1, nameF);              //перенаправление вывода отчета в Word
      sFile:=Menu.frxRTFExport1.Filename;
      end;
      if print_otch.CheckBox3.Checked = True then
      begin
      nameF:=sDest+'Баланс за '+monthN+' '+god+'.xls';
      ExportFilter(Menu.frxXLSExport1, nameF);              //перенаправление вывода отчета в XLS
      end;
      if print_otch.CheckBox4.Checked = True then vivod:='';
      CopyFile(sFile, nameF);
//      OpenFile(nameF);
      ShowMessage('Файл "'+nameF);
      DeleteFile(sFile);
      end;
     frxDBDataset1.DataSet.Close;
     frxDBDataset2.DataSet.Close;
     frxDBDataset1.Free;
     frxDBDataset2.Free;
end;

CREATE DEFINER=`******`@`%` TRIGGER `pk_adi`.`edit_anketa_napravl_spec` AFTER UPDATE ON pk_adi.anketa FOR EACH ROW
BEGIN
  Set @var_napravl_spec1 = Old.napravl_spec;
  IF @var_napravl_spec1 IS NULL then Set @var_napravl_spec1 = 'пусто'; End If;
  Set @var_napravl_spec2 = New.napravl_spec;
  IF @var_napravl_spec2 IS NULL then Set @var_napravl_spec2 = 'пусто'; End If;
  Set @fi= (SELECT CONCAT(fullname," ",name) FROM anketa WHERE id=Old.id);
  set @idU = New.id_users1;
  set @nKart = Old.n_abit;

  IF (old.napravl_spec != new.napravl_spec) then   //когда поле изменяет свое значение, например, 11 на 22
  INSERT INTO log_extend VALUES(NULL,@idU,SYSDATE(),CONCAT("изменение поля <<специальность документа об образовании>> в № карточки ",@nKart," ",@fi,". Было: ",@var_napravl_spec1,", стало: ",@var_napravl_spec2));
  End if;

  IF ((old.napravl_spec is null ) AND (new.napravl_spec is not null)) then  //когда поле было пустым и принимает значение 11
  INSERT INTO log_extend VALUES(NULL,@idU,SYSDATE(),CONCAT("изменение поля <<специальность документа об образовании>> в № карточки ",@nKart," ",@fi,". Было: ",@var_napravl_spec1,", стало: ",@var_napravl_spec2));
  End if;

  IF ((new.napravl_spec is null) AND (old.napravl_spec is not null)) then //когда у поля было значение 22 и стало пустым
  INSERT INTO log_extend VALUES(NULL,@idU,SYSDATE(),CONCAT("изменение поля <<специальность документа об образовании>> в № карточки ",@nKart," ",@fi,". Было: ",@var_napravl_spec1,", стало: ",@var_napravl_spec2));
  End if;
END;

   ReportMM.UserName        := 'Report';

    ReportMM.UserName        := 'Report1';