insurance.pas

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

unit insurance;

interface

uses SysUtils,Classes,adodb;



//-------------基本功能
function create_insurance(params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
function modify_insurance(params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
function delete_insurance(params:WideString;out outstr:string;adoconn:TADOConnection):boolean;

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


implementation
uses shareunit;

function check_authority_insurance(funid,authority:string):boolean;
begin
case strtointdef(funid,public_unkonw) of
   public_unkonw:          result:=true;
   public_modify:          result:=authority[77]='1';
   public_del:             result:=authority[78]='1';

   public_infor:           result:=authority[80]='1';
   public_list:            result:=authority[80]='1';

   else result:=true;
   end;
end;


function create_insurance(params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
var querys:tadoquery;
    abc1,abc2:boolean;
    dat:tdatetime;
begin
result:=true;
outstr:='记录已经存在!';
dat:=strtodate(getparamitem(params,'tdate'));
try
querys:=tadoquery.create(nil);
querys.Connection:=adoconn;

querys.Close;
querys.sql.clear;
querys.sql.add('select count(*)as tmp from insurance where personnel_id=:ax and');
querys.sql.add('insdate>=:d1 and insdate<=:d2');
querys.parameters.parambyname('ax').value:=getparamitem(params,'tuid');
querys.parameters.parambyname('d1').value:=makemothday(dat,true);
querys.parameters.parambyname('d2').value:=makemothday(dat,false);
querys.open;
abc1:=querys['tmp']=0;

querys.close;
querys.sql.clear;
querys.sql.add('select count(*)as tmp from P_archives where dimission<>0 and');
querys.sql.add('personnel_id=:ax and over_date<:d1');
querys.Parameters.parambyname('ax').value := getparamitem(params,'tuid');
querys.Parameters.parambyname('d1').value := makemothday(dat, true);
querys.open;
abc2:=querys['tmp']=0;

if  abc1 and abc2 then
    try
    adoconn.Connected:=true;
    adoconn.BeginTrans;

    querys.close;
    querys.sql.clear;
    querys.sql.add('insert into insurance(personnel_id,declare_salary,individual,company,declare_month,insdate,remark)');
    querys.sql.add('values(:a1,0,0,0,:d1,:d2,:a2)');
    querys.parameters.parambyname('a1').value:=getparamitem(params,'tuid');
    querys.parameters.parambyname('a2').value:='-';
    querys.parameters.parambyname('d1').value:=makemothday(dat,true);
    querys.parameters.parambyname('d2').value:=getparamitem(params,'tdate');
    querys.execsql;

    adoconn.CommitTrans;
    except
    adoconn.RollbackTrans;
    end;

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


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

querys.Close;
querys.sql.clear;
querys.sql.add('select count(*)as tmp from insurance where personnel_id=:ax and');
querys.sql.add('insdate>=:d1 and insdate<=:d2');
querys.parameters.parambyname('ax').value:=getparamitem(params,'tuid');
querys.parameters.parambyname('d1').value:=makemothday(dat,true);
querys.parameters.parambyname('d2').value:=makemothday(dat,false);
querys.open;
if querys['tmp'] = 1 then
    try
    adoconn.Connected:=true;
    adoconn.BeginTrans;

    querys.close;
    querys.sql.clear;
    querys.sql.add('update insurance set declare_salary=:a1,individual=:a2,');
    querys.sql.add('company=:a3,remark=:a4');
    querys.sql.add('where personnel_id=:ax and insdate>=:d1 and insdate <=:d2');
    querys.parameters.parambyname('ax').value:=getparamitem(params,'tuid');
    querys.parameters.parambyname('a1').value:=getparamitem(params,'tdeclare_salary');
    querys.parameters.parambyname('a2').value:=getparamitem(params,'tindividual');
    querys.parameters.parambyname('a3').value:=getparamitem(params,'tcompany');
    querys.parameters.parambyname('a4').value:=getparamitem(params,'tremark');
    querys.parameters.parambyname('d1').value:=makemothday(dat,true);
    querys.parameters.parambyname('d2').value:=makemothday(dat,false);
    querys.execsql;
    
    adoconn.CommitTrans;
    result:=true;
    except
    adoconn.RollbackTrans;
    outstr:='修改养老金记录时更新错误!';
    end;

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

end;


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

querys.Close;
querys.sql.clear;
querys.sql.add('select count(*)as tmp from insurance where personnel_id=:ax and');
querys.sql.add('insdate>=:d1 and insdate<=:d2');
querys.parameters.parambyname('ax').value:=getparamitem(params,'tuid');
querys.parameters.parambyname('d1').value:=makemothday(dat,true);
querys.parameters.parambyname('d2').value:=makemothday(dat,false);
querys.open;
if querys['tmp'] = 1 then
    try
    adoconn.Connected:=true;
    adoconn.BeginTrans;

    querys.Close;
    querys.sql.clear;
    querys.sql.add('delete from insurance where personnel_id=:ax and');
    querys.sql.add('insdate>=:d1 and insdate<=:d2');
    querys.parameters.parambyname('ax').value:=getparamitem(params,'tuid');
    querys.parameters.parambyname('d1').value:=makemothday(dat,true);
    querys.parameters.parambyname('d2').value:=makemothday(dat,false);
    querys.execsql;
    
    adoconn.CommitTrans;
    result:=true;
    except
    adoconn.RollbackTrans;
    outstr:='删除养老金记录时更新错误!';
    end;

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


procedure Query_insuranceinfo(params:WideString;out querys:tadoquery);
var funid:integer;
    dat:tdatetime;
begin
funid:=strtointdef(getparamitem(params,'funid'),public_unkonw);
case funid of
 public_list:
    begin
    dat:=strtodate(getparamitem(params,'tdate'));
    querys.close;
    querys.sql.clear;
    querys.sql.add('select * from');
    querys.sql.add('(SELECT a.personnel_id,name,dept,work_type,declare_month,declare_salary,');
    querys.sql.add('individual,(declare_salary*individual)as individualqt,company,');
    querys.sql.add('(declare_salary*company)as companyqt,insdate,start_date,b.remark');
    querys.sql.add('FROM P_archives as a INNER JOIN insurance as b ON a.personnel_id = b.personnel_id');
    querys.sql.add('where b.insdate>=:d1 and b.insdate<=:d2)');
    querys.sql.add('where personnel_id like :a3 and name like :a4 and dept like :a5 and work_type like :a6');
    querys.Parameters.ParamByName('a3').value:=getparamitem(params,'tuid');
    querys.Parameters.ParamByName('a4').value:=getparamitem(params,'tname');
    querys.Parameters.ParamByName('a5').value:=getparamitem(params,'tdept');
    querys.Parameters.ParamByName('a6').value:=getparamitem(params,'twork');
    querys.Parameters.ParamByName('d1').value:=makemothday(dat,true);
    querys.Parameters.ParamByName('d2').value:=makemothday(dat,false);
    querys.open;
    end;

 public_infor:
    begin
    dat:=strtodate(getparamitem(params,'tdate'));
    querys.close;
    querys.sql.clear;
    querys.sql.add('SELECT a.personnel_id,name,dept,work_type,start_date,declare_salary,');
    querys.sql.add('individual,company,declare_month,insdate,b.remark');
    querys.sql.add('FROM P_archives as a INNER JOIN insurance as b ON a.personnel_id = b.personnel_id');
    querys.sql.add('where b.insdate>=:d1 and b.insdate<=:d2 and a.personnel_id=:ax');
    querys.parameters.parambyname('ax').value:=getparamitem(params,'tuid');
    querys.Parameters.ParamByName('d1').value:=makemothday(dat,true);
    querys.Parameters.ParamByName('d2').value:=makemothday(dat,false);
    querys.open;
    end;

 end; 
   
end;


end.