dormitory.pas

三層源碼,DELPHI寫的三層源碼,三層源碼,

unit dormitory;

interface

uses SysUtils,Classes,adodb;
//-------------基本功能
function create_dormitory(operid,params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
function modify_dormitory(operid,params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
function delete_dormitory(operid,params:WideString;out outstr:string;adoconn:TADOConnection):boolean;

function create_temp_dormitory(operid,params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
function delete_temp_dormitory(operid,params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
function clear_temp_dormitory(operid,params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
function rebuild_temp_dormitory(operid,params:WideString;out outstr:string;adoconn:TADOConnection):boolean;

//-------------查询
procedure Query_dormitoryinfo(operid,params:WideString;out querys:tadoquery);
function check_authority_dormitory(funid,authority:string):boolean;

implementation
uses shareunit;

// 宿舍管理 11-15
function check_authority_dormitory(funid,authority:string):boolean;
begin
case strtointdef(funid,public_unkonw) of
   public_unkonw:          result:=true;
   public_add:             result:=authority[26]='1';
   public_modify:          result:=authority[27]='1';
   public_del:             result:=authority[28]='1';
   public_add_temp:        result:=authority[26]='1';
   public_del_temp:        result:=authority[28]='1';
   public_clear_temp:      result:=authority[28]='1';
   public_rebuild:         result:=authority[27]='1';

   public_list_temp:       result:=authority[30]='1';
   public_list_ex01:       result:=authority[30]='1';
   public_infor:           result:=authority[30]='1';
   public_list:            result:=authority[30]='1';

   else result:=true;
   end;
end;

function create_dormitory(operid,params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
var querys:tadoquery;
    abc1,abc2:boolean;
    xs:integer;
begin
result:=false;
outstr:='记录已经存在!';
try
querys:=tadoquery.create(nil);
querys.Connection:=adoconn;

querys.close;
querys.sql.clear;
querys.sql.add('select count(*)as tmp from D_main where room_num=:ax');
querys.Parameters.parambyname('ax').value:=getparamitem(params,'tuid');
querys.open;
abc1:=querys['tmp']=0;

querys.close;
querys.sql.clear;
querys.sql.add('select count(*)as tmp from D_temp where id=:ax');
querys.parameters.parambyname('ax').value:=operid;
querys.open;
abc2:=querys['tmp']<=strtoint(getparamitem(params,'tsumqt'));
if not abc2 then outstr:='明细超过了房间总床位数!';

xs:=strtoint(getparamitem(params,'tsumqt'))-querys['tmp'];
if abc1 and abc2 then
    try
    adoconn.Connected:=true;
    adoconn.BeginTrans;

    querys.close;
    querys.sql.clear;
    querys.sql.add('insert into D_main(room_num,locus,bunk_total,bunk_residual,bunk,bureau,desk,fan,remark,sort)');
    querys.sql.add('values(:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9,:a10)');
    querys.parameters.parambyname('a1').value:=getparamitem(params,'tuid');
    querys.parameters.parambyname('a2').value:=getparamitem(params,'tlocus');
    querys.parameters.parambyname('a3').value:=getparamitem(params,'tsumqt');
    querys.parameters.parambyname('a4').value:=xs;
    querys.parameters.parambyname('a5').value:=getparamitem(params,'tbunk');
    querys.parameters.parambyname('a6').value:=getparamitem(params,'tbureau');
    querys.parameters.parambyname('a7').value:=getparamitem(params,'tdesk');
    querys.parameters.parambyname('a8').value:=getparamitem(params,'tfan');
    querys.parameters.parambyname('a9').value:=getparamitem(params,'tmemo');
    querys.parameters.parambyname('a10').value:=getparamitem(params,'tsort');
    querys.execsql;

    querys.close;
    querys.sql.clear;
    querys.sql.add('insert into D_particular(room_num,personnel_id,bunk_num)');
    querys.sql.add('select :ax,personnel_id,bunk_num from D_temp where id=:ar');
    querys.parameters.parambyname('ax').value:=getparamitem(params,'tuid');
    querys.parameters.parambyname('ar').value:=operid;
    querys.ExecSQL;

    adoconn.CommitTrans;
    result:=true;
    except
    adoconn.RollbackTrans;
    outstr:='增加宿舍资料时更新错误!';
    end;

finally
querys.close;
freeandnil(querys);
end;
end;

function modify_dormitory(operid,params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
var querys:tadoquery;
    abc1,abc2:boolean;
    xs:integer;
begin
result:=false;
outstr:='记录不存在!';
try
querys:=tadoquery.create(nil);
querys.Connection:=adoconn;

querys.close;
querys.sql.clear;
querys.sql.add('select count(*)as tmp from D_main where room_num=:ax');
querys.Parameters.parambyname('ax').value:=getparamitem(params,'tuid');
querys.open;
abc1:=querys['tmp']=1;

querys.close;
querys.sql.clear;
querys.sql.add('select count(*)as tmp from D_temp where id=:ax');
querys.parameters.parambyname('ax').value:=operid;
querys.open;
abc2:=querys['tmp']<=strtoint(getparamitem(params,'tsumqt'));
if not abc2 then outstr:='明细超过了房间总床位数!';

xs:=strtoint(getparamitem(params,'tsumqt'))-querys['tmp'];
if abc1 and abc2 then
    try
    adoconn.Connected:=true;
    adoconn.BeginTrans;

    querys.close;
    querys.sql.clear;
    querys.sql.add('delete from D_particular where room_num=:ax');
    querys.parameters.parambyname('ax').value:=getparamitem(params,'tuid');
    querys.execsql;

    querys.close;
    querys.sql.clear;
    querys.sql.add('update D_main set locus=:a2,bunk_total=:a3,bunk_residual=:a4,');
    querys.sql.add('bunk=:a5,bureau=:a6,desk=:a7,fan=:a8,remark=:a9,sort=:a10 where room_num=:a1');
    querys.parameters.parambyname('a1').value:=getparamitem(params,'tuid');
    querys.parameters.parambyname('a2').value:=getparamitem(params,'tlocus');
    querys.parameters.parambyname('a3').value:=getparamitem(params,'tsumqt');
    querys.parameters.parambyname('a4').value:=xs;
    querys.parameters.parambyname('a5').value:=getparamitem(params,'tbunk');
    querys.parameters.parambyname('a6').value:=getparamitem(params,'tbureau');
    querys.parameters.parambyname('a7').value:=getparamitem(params,'tdesk');
    querys.parameters.parambyname('a8').value:=getparamitem(params,'tfan');
    querys.parameters.parambyname('a9').value:=getparamitem(params,'tmemo');
    querys.parameters.parambyname('a10').value:=getparamitem(params,'tsort');
    querys.execsql;

    querys.close;
    querys.sql.clear;
    querys.sql.add('insert into D_particular(room_num,personnel_id,bunk_num)');
    querys.sql.add('select :ax,personnel_id,bunk_num from D_temp where id=:ar');
    querys.parameters.parambyname('ax').value:=getparamitem(params,'tuid');
    querys.parameters.parambyname('ar').value:=operid;
    querys.ExecSQL;

    adoconn.CommitTrans;
    result:=true;
    except
    adoconn.RollbackTrans;
    outstr:='修改宿舍资料时更新错误!';
    end;

finally
querys.close;
freeandnil(querys);
end;
end;

function delete_dormitory(operid,params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
var querys:tadoquery;
begin
result:=false;
outstr:='记录不存在!';
try
querys:=tadoquery.create(nil);
querys.Connection:=adoconn;

querys.close;
querys.sql.clear;
querys.sql.add('select count(*)as tmp from D_main where room_num=:ax');
querys.Parameters.parambyname('ax').value:=getparamitem(params,'tuid');
querys.open;
if  querys['tmp']=1 then
    try

    adoconn.Connected:=true;
    adoconn.BeginTrans;

    querys.close;
    querys.sql.clear;
    querys.sql.add('delete from D_main where room_num=:ax');
    querys.parameters.parambyname('ax').value:=getparamitem(params,'tuid');
    querys.execsql;

    querys.close;
    querys.sql.clear;
    querys.sql.add('delete from D_particular where room_num=:ax');
    querys.parameters.parambyname('ax').value:=getparamitem(params,'tuid');
    querys.execsql;

    adoconn.CommitTrans;
    result:=true;
    except
    adoconn.RollbackTrans;
    outstr:='删除宿舍资料时更新错误!';
    end;

finally
querys.close;
freeandnil(querys);
end;
end;

//--------------临时表
function create_temp_dormitory(operid,params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
var querys:tadoquery;
begin
result:=false;
outstr:='记录已经存在!';
try
querys:=tadoquery.create(nil);
querys.Connection:=adoconn;

querys.close;
querys.sql.clear;
querys.sql.add('select count(*)as tmp from D_temp where personnel_id=:ax and id=:ar');
querys.Parameters.parambyname('ax').value:=getparamitem(params,'tuid');
querys.Parameters.parambyname('ar').value:=operid;
querys.open;
if  querys['tmp']=0 then
    try
    adoconn.Connected:=true;
    adoconn.BeginTrans;

    querys.close;
    querys.sql.clear;
    querys.sql.add('insert into d_temp(id,personnel_id,bunk_num)');
    querys.SQL.add('values(:ax,:a1,:a2)');
    querys.Parameters.ParamByName('ax').value:=operid;
    querys.Parameters.ParamByName('a1').value:=getparamitem(params,'tuid');
    querys.Parameters.ParamByName('a2').value:=getparamitem(params,'tuid1');
    querys.execsql;

    adoconn.CommitTrans;
    result:=true;
    except
    adoconn.RollbackTrans;
    outstr:='增加时更新错误!';
    end;

finally
querys.close;
freeandnil(querys);
end;
end;

function delete_temp_dormitory(operid,params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
var querys:tadoquery;
begin
result:=false;
outstr:='记录不存在!';
try
querys:=tadoquery.create(nil);
querys.Connection:=adoconn;

querys.close;
querys.sql.clear;
querys.sql.add('select count(*)as tmp from D_temp where personnel_id=:ax and id=:ar');
querys.Parameters.parambyname('ax').value:=getparamitem(params,'tuid');
querys.Parameters.parambyname('ar').value:=operid;
querys.open;
if  querys['tmp']=1 then
    try
    adoconn.Connected:=true;
    adoconn.BeginTrans;

    querys.close;
    querys.sql.clear;
    querys.sql.add('delete from d_temp where personnel_id=:a1 and id=:ax');
    querys.Parameters.ParamByName('ax').value:=operid;
    querys.Parameters.ParamByName('a1').value:=getparamitem(params,'tuid');
    querys.execsql;

    adoconn.CommitTrans;
    result:=true;
    except
    adoconn.RollbackTrans;
    outstr:='删除时更新错误!';
    end;

finally
querys.close;
freeandnil(querys);
end;

end;

function clear_temp_dormitory(operid,params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
var querys:tadoquery;
begin
result:=false;
outstr:='记录不存在!';
try
querys:=tadoquery.create(nil);
querys.Connection:=adoconn;

    try
    adoconn.Connected:=true;
    adoconn.BeginTrans;

    querys.close;
    querys.sql.clear;
    querys.sql.add('delete from d_temp where id=:ax');
    querys.Parameters.ParamByName('ax').value:=operid;
    querys.execsql;

    adoconn.CommitTrans;
    result:=true;
    except
    adoconn.RollbackTrans;
    outstr:='清除时更新错误!';
    end;

finally
querys.close;
freeandnil(querys);
end;

end;

function rebuild_temp_dormitory(operid,params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
var querys:tadoquery;
begin
result:=false;
outstr:='记录不存在!';
try
querys:=tadoquery.create(nil);
querys.Connection:=adoconn;

    try
    adoconn.Connected:=true;
    adoconn.BeginTrans;

    querys.Close;
    querys.SQL.Clear;
    querys.sql.add('insert into d_temp(id,personnel_id,bunk_num)');
    querys.sql.add('select :ax,personnel_id,bunk_num from D_particular where room_num=:ar');
    querys.Parameters.ParamByName('ar').value:=getparamitem(params,'tuid');
    querys.Parameters.ParamByName('ax').value:=operid;
    querys.ExecSQL;

    adoconn.CommitTrans;
    result:=true;
    except
    adoconn.RollbackTrans;
    outstr:='重建时更新错误!';
    end;

finally
querys.close;
freeandnil(querys);
end;
end;

//-------------查询
procedure Query_dormitoryinfo(operid,params:WideString;out querys:tadoquery);
var funid:integer;
begin
funid:=strtointdef(getparamitem(params,'funid'),public_unkonw);
case funid of
 public_list:
    begin
    querys.Close;
    querys.SQL.clear;
    querys.SQL.Add('select room_num,sort,locus,bunk_total,');
    querys.sql.add('bunk_residual,bunk,bureau,desk,');
    querys.sql.add('fan,remark from D_main where room_num like :a1 and');
    querys.sql.add('sort like :a2 and locus like :a3');
    querys.sql.add('order by room_num');
    querys.parameters.parambyname('a1').value:=getparamitem(params,'tuid');
    querys.parameters.parambyname('a2').value:=getparamitem(params,'tsort');
    querys.parameters.parambyname('a3').value:=getparamitem(params,'tlocus');
    querys.open;
    end;

 public_infor:
    begin
    querys.Close;
    querys.SQL.clear;
    querys.SQL.Add('select * from D_main where room_num=:ax');
    querys.Parameters.ParamByName('ax').value:=getparamitem(params,'tuid');
    querys.open;
    end;

 public_list_ex01:
    begin
    querys.Close;
    querys.SQL.clear;
    querys.SQL.Add('select * from D_main');
    querys.open;
    end;

 public_list_temp:
    begin
    querys.Close;
    querys.SQL.clear;
    querys.SQL.Add('SELECT b.personnel_id,a.name,a.dept,a.work_type,b.bunk_num');
    querys.sql.add('FROM P_archives as a INNER JOIN D_temp as b ON a.personnel_id = b.personnel_id');
    querys.sql.add('where b.id=:ax order by b.personnel_id');
    querys.Parameters.ParamByName('ax').value:=operid;
    querys.open;
    end;

 end;
end;

end.