unit Control_classU;

interface
uses DAHelper,classesU,ADODB,SysUtils,StdCtrls;
//添加院系班級
function Addclasses(classes:Tclasses):boolean;
//修改院系班級
function Editclasses(classes:Tclasses):boolean;
//刪除院系班級
function Delclasses(classesID:integer):boolean;
//得到院系班級記錄
function GetclassesArray:TADOQuery;
//根據院系id查找院系信息
function GetclassesByclassesID(classesID:integer):Tclasses;
//根據院系名查找院系信息
function GetclassesByName(className:string):Tclasses;
//向Combobox中填充數據
procedure FillCombobox(cbx:TCombobox);
implementation
procedure FillCombobox(cbx:TCombobox);
var
  query:TADOQuery;
begin
  query:=TADOQuery.Create(nil);
  query:=GetclassesArray;
  cbx.Items.Clear;
  while not query.Eof do
    begin
      cbx.Items.Add(query.FieldValues['院系名']);
      query.Next;
    end;
  cbx.ItemIndex:=0;
  query:=nil;
end;
function GetclassesByName(className:string):Tclasses;
var
  sql:string;
  classes:Tclasses;
  ADOQuery:TADOQuery;
begin
  ADOQuery:=TADOQuery.Create(nil);
  sql:='select id,院系名,上級id from 院系班級'+
       ' where 院系名='+''''+className+''''+
       ' order by 院系名';
  ADOQuery:=DAHelper.ExeSqlQuery(sql);
  if(ADOQuery.RecordCount<1) then
    begin
      result:=nil;
    end
  else
      begin
        classes:=Tclasses.Create;
        classes.id:=ADOQuery.Fields[0].Value;
        classes.className:=ADOQuery.Fields[1].Value;
        classes.fatherID:=ADOQuery.Fields[2].Value;
        result:=classes;
      end;
  ADOQuery:=nil;

end;
function GetclassesByclassesID(classesID:integer):Tclasses;
var
  sql:string;
  classes:Tclasses;
  ADOQuery:TADOQuery;
begin
  ADOQuery:=TADOQuery.Create(nil);
  sql:='select id,院系名,上級id from 院系班級'+
       ' where id='+inttostr(classesID);
  ADOQuery:=DAHelper.ExeSqlQuery(sql);
  if(ADOQuery.RecordCount<1) then
    begin
      result:=nil;
    end
  else
      begin
        classes:=Tclasses.Create;
        classes.id:=ADOQuery.Fields[0].Value;
        classes.className:=ADOQuery.Fields[1].Value;
        classes.fatherID:=ADOQuery.Fields[2].Value;
        result:=classes;
      end;
  ADOQuery:=nil;
end;
function GetclassesArray:TADOQuery;
var
  sql:string;
begin
  sql:='select a.id,a.院系名,a.上級id,isnull(b.院系名,''本校'')  上級院系名  from 院系班級 a '+
       ' left outer join 院系班級 b on a.上級id=b.id';
  result:=DAHelper.ExeSqlQuery(sql);
end;
function Delclasses(classesID:integer):boolean;
var
  sql:string;
begin
  sql:='delete from 院系班級 where id='+inttostr(classesID);
  result:=DAHelper.ExeSqlNoQuery(sql);
end;
function Editclasses(classes:Tclasses):boolean;
var
  sql:string;
begin
  sql:='update 院系班級 set 院系名='+''''+classes.className+''''+
       ',上級id='+inttostr(classes.fatherID)+
       ' where id='+inttostr(classes.id);
  result:=DAHelper.ExeSqlNoQuery(sql);
end;
function Addclasses(classes:Tclasses):boolean;
var
  sql:string;
begin
  sql:='insert into 院系班級(院系名,上級id)'+
       ' values('+''''+classes.className+''''+
       ','+inttostr(classes.fatherID)+ ')';
  result:=DAHelper.ExeSqlNoQuery(sql);
end;

end.