event.pas

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

unit event;

interface

uses SysUtils,Classes,adodb;

//-------------基本功能
function create_event(params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
function modify_event(params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
function delete_event(params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
function clear_event(params:WideString;out outstr:string;adoconn:TADOConnection):boolean;

//-------------查询
procedure Query_eventinfo(params:WideString;out querys:tadoquery);

function check_authority_event(funid,authority:string):boolean;

implementation
uses shareunit;

function check_authority_event(funid,authority:string):boolean;
begin
case strtointdef(funid,public_unkonw) of
   public_unkonw:          result:=true;
   public_add:             result:=authority[46]='1';
   public_modify:          result:=authority[47]='1';
   public_del:             result:=authority[48]='1';
   public_clear_temp:      result:=authority[48]='1';

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

   else result:=true;
   end;
end;

//-------------基本功能
function create_event(params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
var querys:tadoquery;
    x1:string;
begin
result:=false;
outstr:='记录已经存在!';
x1:=XorDecode(floattostr(pi),getparamitem(params,'tremark'));
try
querys:=tadoquery.create(nil);
querys.Connection:=adoconn;

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

    querys.close;
    querys.sql.clear;
    querys.sql.add('insert into event(personnel_id,repeat,inqt,filter,remark,inure_date,extday)');
    querys.sql.add('values(:a1,:a2,:a3,:a4,:a5,:a6,:a7)');
    querys.parameters.ParamByName('a1').value := getparamitem(params,'tuid');
    querys.parameters.ParamByName('a2').value := getparamitem(params,'trepeat');
    querys.parameters.ParamByName('a3').value := getparamitem(params,'tinqt');
    querys.parameters.ParamByName('a4').value := getparamitem(params,'tfilter');
    querys.parameters.ParamByName('a5').value := x1;
    querys.parameters.ParamByName('a6').value := getparamitem(params,'tinure_date');
    querys.parameters.ParamByName('a7').value := getparamitem(params,'textday');
    querys.ExecSQL;

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

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

function modify_event(params:WideString;out outstr:string;adoconn:TADOConnection):boolean;
var querys:tadoquery;
    x1:string;
begin
result:=false;
outstr:='记录不存在!';
x1:=XorDecode(floattostr(pi),getparamitem(params,'tremark'));
try
querys:=tadoquery.create(nil);
querys.Connection:=adoconn;

querys.close;
querys.sql.clear;
querys.sql.add('select count(*)as tmp from event where autoid=:ax');
querys.parameters.parambyname('ax').value:=getparamitem(params,'tautoid');
querys.open;

if querys['tmp']=1 then
    try
    adoconn.Connected:=true;
    adoconn.BeginTrans;

    querys.close;
    querys.sql.clear;
    querys.sql.add('update event set repeat=:a1,inqt=:a2,filter=:a3,remark=:a4,');
    querys.sql.add('personnel_id=:a5,inure_date=:a6,extday=:a8');
    querys.sql.add('where autoid=:a7');
    querys.parameters.ParamByName('a1').value := getparamitem(params,'trepeat');
    querys.parameters.ParamByName('a2').value := getparamitem(params,'tinqt');
    querys.parameters.ParamByName('a3').value := getparamitem(params,'tfilter');
    querys.parameters.ParamByName('a4').value := x1;
    querys.parameters.ParamByName('a5').value := getparamitem(params,'tuid');
    querys.parameters.ParamByName('a6').value := getparamitem(params,'tinure_date');
    querys.parameters.ParamByName('a7').value := getparamitem(params,'tautoid');
    querys.parameters.ParamByName('a8').value := getparamitem(params,'textday');
    querys.ExecSQL;

    adoconn.CommitTrans;
    result:=true;
    except
    adoconn.RollbackTrans;
    outstr:='修改事件提醒时更新错误!';
    end;

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


function delete_event(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 event where autoid=:ax');
querys.parameters.parambyname('ax').value:=getparamitem(params,'tautoid');
querys.open;

if querys['tmp']=1 then
    try
    adoconn.Connected:=true;
    adoconn.BeginTrans;

    querys.close;
    querys.sql.clear;
    querys.sql.add('delete from event where autoid=:ax');
    querys.parameters.ParamByName('ax').value := getparamitem(params,'tautoid');
    querys.ExecSQL;

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

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

function clear_event(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 event where repeat=:ar and date()>inure_date and');
querys.sql.add('date()>iif(filter=:ax,inure_date+round(inqt*365.25)+extday,inure_date+round(inqt*30.4375)+extday)');
querys.parameters.ParamByName('ax').value :='年';
querys.parameters.ParamByName('ar').value :='单次提醒';
querys.open;

if querys['tmp']>0 then
    try
    adoconn.Connected:=true;
    adoconn.BeginTrans;

    querys.close;
    querys.sql.clear;
    querys.sql.add('Delete FROM event where repeat=:ar and date()>inure_date and');
    querys.sql.add('date()>iif(filter=:ax,inure_date+round(inqt*365.25)+extday,inure_date+round(inqt*30.4375)+extday)');
    querys.parameters.ParamByName('ax').value :='年';
    querys.parameters.ParamByName('ar').value :='单次提醒';
    querys.ExecSQL;

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

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

//-------------查询
procedure Query_eventinfo(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 autoid,a.personnel_id,name,dept,work_type,repeat,inqt,filter,extday,a.remark,inure_date,date()as cur_date,start_date');
     querys.sql.add('FROM event as a INNER JOIN P_archives as b ON a.personnel_id = b.personnel_id');
     querys.sql.add('where a.personnel_id like :a1 and name like :a2 and dept like :a3 and work_type like :a4');
     querys.sql.add('order by inure_date desc');
     querys.parameters.parambyname('a1').value:=getparamitem(params,'tuid');
     querys.parameters.parambyname('a2').value:=getparamitem(params,'tname');
     querys.parameters.parambyname('a3').value:=getparamitem(params,'tdept');
     querys.parameters.parambyname('a4').value:=getparamitem(params,'twork');
     querys.open;
     end;

 public_infor:
     begin
     querys.close;
     querys.sql.clear;
     querys.sql.add('select * from event where autoid=:ax');
     querys.parameters.parambyname('ax').value:=getparamitem(params,'tautoid');
     querys.open;
     end;

 public_list_ex01:
     begin
      querys.Close;
      querys.sql.clear;
      querys.sql.add('select autoid,a.personnel_id,name,dept,work_type,repeat,inqt,filter,extday,');
      querys.sql.add('a.remark,inure_date,date()as cur_date,start_date');
      querys.sql.add('from(select autoid,a.personnel_id,name,dept,work_type,repeat,inqt,filter,extday,');
      querys.sql.add('a.remark,inure_date,date()as cur_date,start_date,');
      querys.sql.add('(DATEPART('+chr(39)+'yyyy'+chr(39)+',cur_date)-DATEPART('+chr(39)+'yyyy'+chr(39)+',inure_date))as ryear,');
      querys.sql.add('(ryear*12+DATEPART('+chr(39)+'m'+chr(39)+',cur_date)-DATEPART('+chr(39)+'m'+chr(39)+',inure_date))as rmonth');
      querys.sql.add('FROM event as a INNER JOIN P_archives as b ON a.personnel_id = b.personnel_id)');
      querys.sql.add('where date()>inure_date and date()>=iif(filter=:ax,');
      querys.sql.add('inure_date+iif(repeat=:a1,round(inqt*365.25),round(round(ryear/inqt)*365.25*inqt)),');
      querys.sql.add('inure_date+iif(repeat=:a2,round(inqt*30.4375),round(round(rmonth/inqt)*30.4375*inqt))) and');
      querys.sql.add('date()<=iif(filter=:ar,');
      querys.sql.add('inure_date+iif(repeat=:a3,round(inqt*365.25),round(round(ryear/inqt)*365.25*inqt))+extday,');
      querys.sql.add('inure_date+iif(repeat=:a4,round(inqt*30.4375),round(round(rmonth/inqt)*30.4375*inqt))+extday)');
      querys.parameters.parambyname('ax').value:='年';
      querys.parameters.parambyname('ar').value:='年';
      querys.parameters.parambyname('a1').value:='单次提醒';
      querys.parameters.parambyname('a2').value:='单次提醒';
      querys.parameters.parambyname('a3').value:='单次提醒';
      querys.parameters.parambyname('a4').value:='单次提醒';
      querys.open;
     end;

 public_list_ex02:
     begin
     querys.close;
     querys.sql.clear;
     querys.sql.add('select top 15 * from (select distinct remark from event)');
     querys.open;
     end;
 end;

end;

end.