unit update_sql_readme;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB, ComCtrls, Grids, DBGrids, ExtCtrls, DBCtrls,IniFiles,
  StdCtrls, Mask, dxExEdtr, dxTL, dxDBCtrl, dxDBGrid, dxCntner, bsSkinCtrls;

type
  Tfrmupdatesql = class(TForm)
    ADOConnection1: TADOConnection;
    t_sql1: TDataSource;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    Panel1: TPanel;
    DBMemo1: TDBMemo;
    t_readme1: TDataSource;
    DBGrid2: TDBGrid;
    Panel2: TPanel;
    DBMemo2: TDBMemo;
    Panel3: TPanel;
    Label1: TLabel;
    DBEdit1: TDBEdit;
    Label2: TLabel;
    DBEdit2: TDBEdit;
    DBNavigator1: TDBNavigator;
    Panel4: TPanel;
    Label3: TLabel;
    DBEdit3: TDBEdit;
    DBNavigator2: TDBNavigator;
    t_sql: TADOQuery;
    t_readme: TADOQuery;
    Button1: TButton;
    t_view_update: TADOQuery;
    t_view_update1: TDataSource;
    TabSheet3: TTabSheet;
    DBGrid3: TDBGrid;
    Panel5: TPanel;
    DBMemo3: TDBMemo;
    Panel6: TPanel;
    Label5: TLabel;
    DBEdit5: TDBEdit;
    DBNavigator3: TDBNavigator;
    Button2: TButton;
    Panel7: TPanel;
    dxDBGrid1: TdxDBGrid;
    dxDBGrid1Column1: TdxDBGridColumn;
    dxDBGrid1Column2: TdxDBGridColumn;
    bsSkinPanel1: TbsSkinPanel;
    Edit1: TEdit;
    Label4: TLabel;
    Label6: TLabel;
    Edit2: TEdit;
    bsSkinSpeedButton1: TbsSkinSpeedButton;
    bsSkinSpeedButton2: TbsSkinSpeedButton;
    Label7: TLabel;
    Edit3: TEdit;
    procedure t_sqlBeforeInsert(DataSet: TDataSet);
    procedure t_sqlAfterInsert(DataSet: TDataSet);
    procedure ADOConnection1BeforeConnect(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure bsSkinSpeedButton2Click(Sender: TObject);
    procedure bsSkinSpeedButton1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmupdatesql: Tfrmupdatesql;
  banben,shunxu:integer;

implementation

uses u_update;

{$R *.dfm}

procedure Tfrmupdatesql.t_sqlBeforeInsert(DataSet: TDataSet);
begin
  banben:=t_sql.fieldbyname('版本号').AsInteger;
  shunxu:=t_sql.fieldbyname('顺序').AsInteger;
end;

procedure Tfrmupdatesql.t_sqlAfterInsert(DataSet: TDataSet);
begin
  t_sql['版本号']:=banben;
  t_sql['顺序']:=shunxu+1;
end;

procedure Tfrmupdatesql.ADOConnection1BeforeConnect(Sender: TObject);
begin
  ADOConnection1.ConnectionString:='FILE NAME='+ExtractFilePath(ParamStr(0))+'sql.udl';
end;

procedure Tfrmupdatesql.FormShow(Sender: TObject);
var
  str:string;
  table:Tadoquery;
begin
      table:=Tadoquery.Create(application);
  
    table.Connection:=frmsoftupdate.db;
    table.SQL.text:='select top 1 版本号 from t_sys_soft order by 版本号 desc';
    table.open;
  str:=table.fieldbyname('版本号').AsString;
  str:=StringReplace(str,'.','',[rfReplaceAll]);
  t_sql.close;
  t_sql.sql.Clear;
  t_sql.sql.add('select * from t_sql where 版本号>='+str+' order by 版本号,顺序 ');
  t_sql.open;
  t_readme.open;
  t_view_update.Open;
end;

procedure Tfrmupdatesql.Button1Click(Sender: TObject);
var
  table:Tadoquery;
  inifile1:Tinifile;
  str:string;
  j,i:integer;
  if_add:boolean;
  TempStr,Stemp:TStringlist;
begin
//  frmsoftupdate.Memo1.Lines.Clear;
  frmsoftupdate.Memo1.Lines.Add('开始执行语句'+DateTimeToStr(NOW));

{
  try
    frmsoftupdate.db.Connected:=true;
  except
    showmessage('服务器连接失败！！');
    frmsoftupdate.Memo1.Lines.Add('服务器连接失败！！');
  end;
 }
     TempStr:=TStringList.Create;
     Stemp:=TStringList.Create;
  try
      {
    table:=Tadoquery.Create(application);
    table.Connection:=frmsoftupdate.db;
    table.SQL.text:='select 版本号,版本1,版本2,版本3 from t_sys_soft';
    table.open;

    j:=strtoint(table.fieldbyname('版本1').asstring+table.fieldbyname('版本2').asstring+table.fieldbyname('版本3').asstring);
    frmsoftupdate.Memo1.Lines.Add('当前程序版本号:'+table.fieldbyname('版本1').asstring+table.fieldbyname('版本2').asstring+table.fieldbyname('版本3').asstring);
       }
    if frmsoftupdate.Edit5.Text<>'' then begin
      frmsoftupdate.Memo1.Lines.Add('升级程序版本号:'+frmsoftupdate.Edit5.Text);
    end;  

    try
      frmsoftupdate.Memo1.Lines.Add('执行版本号'+t_sql.FieldByName('版本号').AsString+'顺序'+t_sql.FieldByName('版本号').AsString+'。');
      TempStr.Text:=t_sql.fieldbyname('执行').asstring;

    for i:=0 to TempStr.Count-1 do begin
      if (TRIM(UpperCase(TempStr[i]))<>'GO')  then
        stemp.add(TempStr[i])
      else begin
        if (Stemp.Text)<>'' then begin
          frmsoftupdate.p_update.close;
           frmsoftupdate.p_update.Parameters.ParamByName('@sSQL').value:=Stemp.Text;
            frmsoftupdate.p_update.Parameters.ParamByName('@pi_Result').value:=0;
            frmsoftupdate.p_update.Parameters.ParamByName('@ps_Message').value:='';
          frmsoftupdate.p_update.ExecProc;
          stemp.Clear;
        end;

      end;

    end;

            if (Stemp.Text)<>'' then begin
          frmsoftupdate.p_update.close;
           frmsoftupdate.p_update.Parameters.ParamByName('@sSQL').value:=Stemp.Text;
            frmsoftupdate.p_update.Parameters.ParamByName('@pi_Result').value:=0;
            frmsoftupdate.p_update.Parameters.ParamByName('@ps_Message').value:='';           
          frmsoftupdate.p_update.ExecProc;
          stemp.Clear;
        end;

    except
      frmsoftupdate.Memo1.Lines.Add('执行版本号'+t_sql.FieldByName('版本号').AsString+'顺序'+t_sql.FieldByName('版本号').AsString+'时出错。');
       ;
    end;



    showmessage('升级完毕！');
    frmsoftupdate.Memo1.Lines.Add('升级完毕,时间'+DateTimeToStr(Now));
    FreeAndNil(TempStr);
    FreeAndNil(Stemp);
//    close;
  except
    showmessage('升级失败！');
    frmsoftupdate.Memo1.Lines.Add('升级失败,时间'+DateTimeToStr(Now));
    FreeAndNil(TempStr);
    FreeAndNil(Stemp);
  end;
//  frmsoftupdate.Memo1.Lines.SaveToFile(ExtractFilePath(Application.ExeName)+FormatDateTime('YYYYMMDDHHNNSS',NOW)+'.txt');
end;

procedure Tfrmupdatesql.Button2Click(Sender: TObject);
var
  table:Tadoquery;
  inifile1:Tinifile;
  str:string;
  j,i:integer;
  if_add:boolean;
begin
//  frmsoftupdate.Memo1.Lines.Clear;
  frmsoftupdate.Memo1.Lines.Add('开始更新VIEW'+DateTimeToStr(NOW));

  try


//    try


      frmsoftupdate.t_update.Close;
      frmsoftupdate.Memo1.Lines.Add('执行版本号'+'顺序'+t_view_update.FieldByName('顺序').AsString+'。');
      frmsoftupdate.p_update.Parameters.ParamByName('SQLStr').value:=t_view_update.fieldbyname('执行').asstring;
      frmsoftupdate.p_update.ExecProc;


//    except
//      frmsoftupdate.Memo1.Lines.Add('执行版本号'+'顺序'+t_view_update.FieldByName('顺序').AsString+'时出错。');
       ;
//    end;



    showmessage('升级完毕！');
    frmsoftupdate.Memo1.Lines.Add('升级完毕,时间'+DateTimeToStr(Now));
//    close;

  except
    showmessage('升级失败！');
    frmsoftupdate.Memo1.Lines.Add('升级失败,时间'+DateTimeToStr(Now));
  end;

//  frmsoftupdate.Memo1.Lines.SaveToFile(ExtractFilePath(Application.ExeName)+FormatDateTime('YYYYMMDDHHNNSS',NOW)+'.txt');
end;

procedure Tfrmupdatesql.bsSkinSpeedButton2Click(Sender: TObject);
begin
  t_sql.close;
  t_sql.sql.Clear;
  t_sql.sql.add('select * from t_sql order by 版本号,顺序 ');
  t_sql.open;
end;

procedure Tfrmupdatesql.bsSkinSpeedButton1Click(Sender: TObject);
begin
  t_sql.close;
  t_sql.sql.Clear;
  t_sql.sql.add('select * from t_sql where 1=1 ');
  if Edit1.Text<>'' then begin
    t_sql.sql.add(' and 版本号='+Edit1.Text);
  end;
  if Edit2.Text<>'' then begin
    t_sql.sql.add(' and 执行 like ''%'+Edit2.Text+'%''');
  end;
  if Edit3.Text<>'' then begin
    t_sql.sql.add(' and 执行 like ''%'+Edit3.Text+'%''');
  end;
  t_sql.sql.add(' order by 版本号,顺序 ');
  t_sql.open;
  
end;

end.