medicare.pas

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

unit medicare;

interface

uses SysUtils,Classes,adodb;



//-------------基本功能
function create_medicare(params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
function modify_medicare(params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
function delete_medicare(params:WideString;out outstr:string;adoconn:TADOConnection):boolean;

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


implementation
uses shareunit;

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

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

   else result:=true;
   end;
end;


function create_medicare(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 medicare where personnel_id=:ax and');
querys.sql.add('meddate>=:d1 and meddate<=: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 medicare(personnel_id,declare_salary,individual,company,declare_month,meddate,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_medicare(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 medicare where personnel_id=:ax and');
querys.sql.add('meddate>=:d1 and meddate<=: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 medicare set declare_salary=:a1,individual=:a2,');
    querys.sql.add('company=:a3,remark=:a4');
    querys.sql.add('where personnel_id=:ax and meddate>=:d1 and meddate <=: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_medicare(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 medicare where personnel_id=:ax and');
querys.sql.add('meddate>=:d1 and meddate<=: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 medicare where personnel_id=:ax and');
    querys.sql.add('meddate>=:d1 and meddate<=: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_medicareinfo(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,start_date,meddate,b.remark');
    querys.sql.add('FROM P_archives as a INNER JOIN medicare as b ON a.personnel_id = b.personnel_id');
    querys.sql.add('where b.meddate>=:d1 and b.meddate<=: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,meddate,b.remark');
    querys.sql.add('FROM P_archives as a INNER JOIN medicare as b ON a.personnel_id = b.personnel_id');
    querys.sql.add('where b.meddate>=:d1 and b.meddate<=: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;

 public_list_ex01:
    begin
    dat:=strtodate(getparamitem(params,'tdate'));
    querys.close;
    querys.sql.clear;
    querys.sql.add('select * from');
    querys.sql.add('(select personnel_id,name,dept,work_type,medqt,insqt,taxsum,');
    querys.sql.add('(iif(isnull(rate),0,rate)*taxsum-(iif(isnull(deduct),0,deduct)))as taxqt,');
    querys.sql.add('declare_month,start_date from');
    querys.sql.add('(SELECT a.personnel_id,d.name,d.dept,d.work_type,(a.declare_salary*a.individual)as medqt,');
    querys.sql.add('(iif(isnull(b.declare_salary),0,b.declare_salary)*iif(isnull(b.individual),0,b.individual))as insqt,');
    querys.sql.add('(iif(isnull(c.declare_salary),0,c.declare_salary)-iif(isnull(c.tax_free),0,c.tax_free)-');
    querys.sql.add('iif(isnull(c.declare_salary),0,c.declare_salary)*iif(isnull(c.residence_free),0,c.residence_free)-medqt-insqt)as taxsum,');
    querys.sql.add('a.declare_month,d.start_date');
    querys.sql.add('FROM ((medicare as a left JOIN insurance as b ON');
    querys.sql.add('(a.declare_month = b.declare_month) AND (a.personnel_id = b.personnel_id))');
    querys.sql.add('left JOIN incometax as c ON');
    querys.sql.add('(a.declare_month = c.declare_month) AND(a.personnel_id = c.personnel_id))');
    querys.sql.add('left join P_archives as d ON a.personnel_id=d.personnel_id');
    querys.sql.add('where a.declare_month>=:d1 and a.declare_month<=:d2) as e');
    querys.sql.add('left join cess as f ON f.cmin<e.taxsum and f.cmax>e.taxsum)');
    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;

 end; 
   
end;


end.