unit Control_companyU;

interface
uses DAHelper,ClassesU,ADODB,SysUtils;
//添加單位信息
function AddCompany(Company:TCompany):boolean;
//修改單位信息
function EditCompany(Company:TCompany):boolean;
//刪除單位信息
function DelCompany(id:integer):boolean;
//得到單位信息
function GetCompanyArray:TADOQuery;
//查找單位信息by單位ID
function GetCompanyByID(CompanyID:integer):TCompany;
//查找單位信息by單位名(模糊查詢)返回TADOQuery類型
function GetCompanyQueryByCompany(company:string):TADOQuery;
//查找單位信息by單位名稱
function GetCompanyByName(CmopanyName:string):TCompany;
implementation

function GetCompanyByName(companyName:string):TCompany;
var
  sql:string;
  Company:TCompany;
  ADOQuery:TADOQuery;
begin
sql:='select id,單位名稱,單位性質(zhì),'+
       'isnull(單位地址,'''') 單位地址,'+
       'isnull(單位聯(lián)系人,'''') 單位聯(lián)系人,'+
       'isnull(單位聯(lián)系方式,'''') 單位聯(lián)系方式,'+
       'isnull(備注,'''') 備注 from 單位信息'+
       ' where 單位名稱 = '+''''+companyName+'''';
  ADOQuery:=DAHelper.ExeSqlQuery(sql);
  if(ADOQuery.RecordCount<1) then
    begin
      result:=nil;
    end
  else
      begin
        Company:=TCompany.Create;
        Company.id:=ADOQuery.Fields[0].Value;
        Company.company:=ADOQuery.Fields[1].Value;
        Company.companyType:=ADOQuery.Fields[2].Value;
        Company.address:=ADOQuery.Fields[3].Value;
        Company.linkman:=ADOQuery.Fields[4].Value;
        Company.linkManner:=ADOQuery.Fields[5].Value;
        result:=Company;
      end;
  ADOQuery:=nil;
end;
function GetCompanyQueryByCompany(Company:string):TADOQuery;
var
  sql:string;
begin
sql:='select id,單位名稱,單位性質(zhì),'+
       'isnull(單位地址,'''') 單位地址,'+
       'isnull(單位聯(lián)系人,'''') 單位聯(lián)系人,'+
       'isnull(單位聯(lián)系方式,'''') 單位聯(lián)系方式,'+
       'isnull(備注,'''') 備注 from 單位信息'+
       ' where 單位名稱 like '+''''+'%'+company+'%'+'''';
  result:=DAHelper.ExeSqlQuery(sql);
end;

function GetCompanyByID(CompanyID:integer):TCompany;
var
  sql:string;
  Company:TCompany;
  ADOQuery:TADOQuery;
begin
  ADOQuery:=TADOQuery.Create(nil);
 sql:='select id,單位名稱,單位性質(zhì),'+
       'isnull(單位地址,'''') 單位地址,'+
       'isnull(單位聯(lián)系人,'''') 單位聯(lián)系人,'+
       'isnull(單位聯(lián)系方式,'''') 單位聯(lián)系方式,'+
       'isnull(備注,'''') 備注 from 單位信息'+
       ' where id='+inttostr(CompanyID);
  ADOQuery:=DAHelper.ExeSqlQuery(sql);
  if(ADOQuery.RecordCount<1) then
    begin
      result:=nil;
    end
  else
      begin
        Company:=TCompany.Create;
        Company.id:=ADOQuery.Fields[0].Value;
        Company.company:=ADOQuery.Fields[1].Value;
        Company.companyType:=ADOQuery.Fields[2].Value;
        Company.address:=ADOQuery.Fields[3].Value;
        Company.linkman:=ADOQuery.Fields[4].Value;
        Company.linkManner:=ADOQuery.Fields[5].Value;
        result:=Company;
      end;
  ADOQuery:=nil;
end;
function GetCompanyArray:TADOQuery;
var
  sql:string;
begin
  sql:='select id,單位名稱,單位性質(zhì),'+
       'isnull(單位地址,'''') 單位地址,'+
       'isnull(單位聯(lián)系人,'''') 單位聯(lián)系人,'+
       'isnull(單位聯(lián)系方式,'''') 單位聯(lián)系方式,'+
       'isnull(備注,'''') 備注 from 單位信息';
  result:=DAHelper.ExeSqlQuery(sql);
end;
function DelCompany(id:integer):boolean;
var
  sql:string;
begin
  sql:='delete from 單位信息 where id='+inttostr(id);
  result:=DAHelper.ExeSqlNoQuery(sql);
end;
function EditCompany(Company:TCompany):boolean;
var
  sql:string;
begin
  sql:='update 單位信息 set 單位名稱='+''''+Company.company+''''+
       ',單位性質(zhì)='+''''+Company.companyType +''''+
       ',單位地址='+''''+Company.address +''''+
       ',單位聯(lián)系人='+''''+Company.linkman+''''+
       ',單位聯(lián)系方式='+''''+Company.linkManner+''''+
       ',備注='+''''+Company.remark+''''+
       ' where id='+inttostr(Company.id);
  result:=DAHelper.ExeSqlNoQuery(sql);
end;
function AddCompany(Company:TCompany):boolean;
var
  sql:string;
begin
  sql:='insert into 單位信息(單位名稱,單位性質(zhì),單位地址,'+
       '單位聯(lián)系人,單位聯(lián)系方式,'+
       '備注)'+' values('+''''+Company.company+''''+
       ','+''''+Company.companyType+''''+
       ','+''''+Company.address+''''+
       ','+''''+Company.linkman+''''+
       ','+''''+Company.linkManner+''''+
       ','+''''+Company.remark+''''+ ')';
  result:=DAHelper.ExeSqlNoQuery(sql);
end;

end.