incometax.pas

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

unit incometax;

interface

uses SysUtils,Classes,adodb;



//-------------基本功能
function create_incometax(params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
function modify_incometax(params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
function delete_incometax(params:WideString;out outstr:string;adoconn:TADOConnection):boolean;

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


implementation
uses shareunit;

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

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

   else result:=true;
   end;
end;


function create_incometax(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 incometax where personnel_id=:ax and');
querys.sql.add('taxdate>=:d1 and taxdate<=: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 incometax(personnel_id,declare_salary,tax_free,residence_free,declare_month,taxdate,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_incometax(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 incometax where personnel_id=:ax and');
querys.sql.add('taxdate>=:d1 and taxdate<=: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 incometax set declare_salary=:a1,tax_free=:a2,');
    querys.sql.add('residence_free=:a3,remark=:a4');
    querys.sql.add('where personnel_id=:ax and taxdate>=:d1 and taxdate <=: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,'ttax_free');
    querys.parameters.parambyname('a3').value:=getparamitem(params,'tresidence_free');
    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_incometax(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 incometax where personnel_id=:ax and');
querys.sql.add('taxdate>=:d1 and taxdate<=: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 incometax where personnel_id=:ax and');
    querys.sql.add('taxdate>=:d1 and taxdate<=: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_incometaxinfo(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 f.personnel_id,f.name,f.dept,f.work_type,f.declare_month,f.declare_salary,');
    querys.sql.add('f.tax_free,f.residence_free,f.residence_freeqt,f.medicqreqt,f.insuranceqt,f.taxsum,');
    querys.sql.add('(iif(isnull(g.rate),0,g.rate)*f.taxsum-(iif(isnull(g.deduct),0,g.deduct)))as taxqt,f.taxdate,f.start_date');
    querys.sql.add('from(select d.personnel_id,e.name,e.dept,e.work_type,d.declare_month,d.declare_salary,');
    querys.sql.add('d.tax_free,d.residence_free,d.residence_freeqt,d.medicqreqt,d.insuranceqt,');
    querys.sql.add('d.taxsum,d.taxdate,e.start_date');
    querys.sql.add('from(select a.personnel_id,a.declare_salary,a.tax_free,a.residence_free,');
    querys.sql.add('(a.residence_free*a.declare_salary)as residence_freeqt,a.declare_month,a.taxdate,');
    querys.sql.add('iif(isnull(b.declare_salary),0,b.declare_salary*b.individual)as medicqreqt,');
    querys.sql.add('iif(isnull(c.declare_salary),0,c.declare_salary*c.individual)as insuranceqt,');
    querys.sql.add('(a.declare_salary-a.tax_free-residence_freeqt-medicqreqt-insuranceqt)as taxsum');
    querys.sql.add('from (incometax as a left join medicare as b on b.personnel_id=a.personnel_id and b.declare_month=a.declare_month)');
    querys.sql.add('left join insurance as c on c.personnel_id=a.personnel_id and c.declare_month=a.declare_month');
    querys.sql.add('where a.declare_month>=:d1 and a.declare_month<=:d2)as d');
    querys.sql.add('INNER JOIN P_archives as e on e.personnel_id=d.personnel_id)as f');
    querys.sql.add('left join cess as g on g.cmin<f.taxsum and g.cmax>f.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;

 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('tax_free,residence_free,declare_month,taxdate,b.remark');
    querys.sql.add('FROM P_archives as a INNER JOIN incometax as b ON a.personnel_id = b.personnel_id');
    querys.sql.add('where b.taxdate>=:d1 and b.taxdate<=: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.