unit Control_StudentU;

interface
uses DAHelper,ClassesU,ADODB,SysUtils;
//添加學生信息
function AddStudent(Student:TStudent):boolean;
//修改學生信息
function EditStudent(Student:TStudent;stdNo:string):boolean;
//刪除學生信息
function DelStudent(stdNo:string):boolean;
//得到學生信息
function GetStudentArray:TADOQuery;
//根據學生號查找學生信息
function GetStudentByStudentNo(StudentNo:string):TStudent;
//根據學生號查找學生信息，返回TADOQuery類型
function GetStudentQueryByStudentNo(StudentNo:string):TADOQuery;
//根據學生姓名查找學生信息
function GetStudentByName(studentName:string):TADOQuery;
implementation
function GetStudentQueryByStudentNo(StudentNo:string):TADOQuery;
var
  sql:string;
begin
  sql:='select 學生證號,姓名,照片,性別,民族,生日,籍貫,身份證號,isnull(家庭電話,'''') 家庭電話,'+
     '居住地址,郵政編碼,入校日期,所在院系'+
     ',isnull(職務,'''') 職務,isnull(備注,'''') 備注 from 學生信息 '+
     ' where 學生證號='+''''+studentNo+'''';
  result:=DAHelper.ExeSqlQuery(sql);
end;
function GetStudentByName(studentName:string):TADOQuery;
var
  sql:string;
begin
  sql:='select 學生證號,姓名,照片,性別,民族,生日,籍貫,身份證號,isnull(家庭電話,'''') 家庭電話,'+
       '居住地址,郵政編碼,入校日期,所在院系'+
       ',isnull(職務,'''') 職務,isnull(備注,'''') 備注 from 學生信息 '+
       ' where 姓名='+''''+studentName+'''';
  result:=DAHelper.ExeSqlQuery(sql);
end;
function GetStudentByStudentNo(StudentNo:string):TStudent;
var
  sql:string;
  Student:TStudent;
  ADOQuery:TADOQuery;
begin
  ADOQuery:=TADOQuery.Create(nil);
  sql:='select 學生證號,姓名,照片,性別,民族,生日,籍貫,身份證號,isnull(家庭電話,'''') 家庭電話,'+
       '居住地址,郵政編碼,入校日期,所在院系'+
       ',isnull(職務,'''') 職務,isnull(備注,'''') 備注 from 學生信息 '+
       ' where 學生證號='+''''+StudentNo+'''';
  ADOQuery:=DAHelper.ExeSqlQuery(sql);
  if(ADOQuery.RecordCount<1) then
    begin
      result:=nil;
    end
  else
      begin
        Student:=TStudent.Create;
        Student.stdNo:=ADOQuery.Fields[0].Value;
        Student.stdName:=ADOQuery.Fields[1].Value;
        Student.picPath:=ADOQuery.Fields[2].Value;
        Student.sex:=ADOQuery.Fields[3].Value;
        Student.nation:=ADOQuery.Fields[4].Value;
        Student.birthday:=DateToStr(ADOQuery.Fields[5].Value);
        Student.native:=ADOQuery.Fields[6].Value;
        Student.IDCard:=ADOQuery.Fields[7].Value;
        Student.homeTel:=ADOQuery.Fields[8].Value;
        Student.address:=ADOQuery.Fields[9].Value;
        Student.postalcode:=ADOQuery.Fields[10].Value;
        Student.inDate:=DateToStr(ADOQuery.Fields[11].Value);
        Student.ClassName:=ADOQuery.Fields[12].Value;
        Student.duty:=ADOQuery.Fields[13].Value;
        Student.remark:=ADOQuery.Fields[14].Value;
        result:=Student;
      end;
  ADOQuery:=nil;
end;
function GetStudentArray:TADOQuery;
var
  sql:string;
begin
  sql:='select 學生證號,姓名,照片,性別,民族,生日,籍貫,身份證號,isnull(家庭電話,'''') 家庭電話,'+
       '居住地址,郵政編碼,入校日期,所在院系'+
       ',isnull(職務,'''') 職務,isnull(備注,'''') 備注 from 學生信息 ';
  result:=DAHelper.ExeSqlQuery(sql);
end;
function DelStudent(stdNo:string):boolean;
var
  sql:string;
begin
  sql:='delete from 學生信息 where 學生證號='+''''+stdNo+'''';
  result:=DAHelper.ExeSqlNoQuery(sql);
end;
function EditStudent(Student:TStudent;stdNo:string):boolean;
var
  sql:string;
begin
  sql:='update 學生信息 set 學生證號='+''''+Student.stdNo+''''+
       ',姓名='+''''+Student.stdName +''''+
       ',照片='+''''+Student.picPath+''''+
       ',性別='+''''+Student.sex +''''+
       ',民族='+''''+Student.nation+''''+
       ',生日='+''''+Student.birthday+''''+
       ',籍貫='+''''+Student.native+''''+
       ',身份證號='+''''+Student.IDCard+''''+
       ',家庭電話='+''''+Student.homeTel+''''+
       ',居住地址='+''''+Student.address+''''+
       ',郵政編碼='+''''+Student.postalcode+''''+
       ',入校日期='+''''+Student.inDate+''''+
       ',所在院系='+''''+Student.ClassName+''''+
       ',職務='+''''+Student.duty+''''+
       ',備注='+''''+Student.remark+''''+
       ' where 學生證號='+''''+stdNo+'''';
  result:=DAHelper.ExeSqlNoQuery(sql);
end;
function AddStudent(Student:TStudent):boolean;
var
  sql:string;
begin
  sql:='insert into 學生信息(學生證號,姓名,照片,性別,民族,生日,籍貫,身份證號,家庭電話,'+
       '居住地址,郵政編碼,入校日期,所在院系,職務,'+
       '備注)'+' values('+''''+Student.stdNo+''''+','+''''+Student.stdName+''''+
       ','+''''+Student.picPath+''''+','+''''+Student.sex+''''+','+
       ''''+Student.nation+''''+','+
       ''''+Student.birthday+''''+','+''''+Student.native+''''+
       ','+''''+Student.IDCard+''''+','+''''+Student.homeTel+''''+
       ','+''''+Student.address+''''+
       ','+''''+Student.postalcode+''''+','+''''+Student.inDate+''''+
       ','+''''+Student.ClassName+''''+','+''''+Student.duty+''''+
       ','+''''+Student.remark+''''+ ')';
  result:=DAHelper.ExeSqlNoQuery(sql);
end;

end.