📄 emptj_shouzhi_all.pas
字号:
sql.Add('values(:v_empid,:v_yearmonth,:v_shou,:V_lastbalance)');
parameters.ParamValues['v_empid']:=adoqemp.FieldValues['empid'];
parameters.ParamValues['V_yearmonth']:=adoqtmp.FieldValues['yearmonth'];
parameters.ParamValues['v_shou']:=adoqtmp.FieldValues['money'];
parameters.ParamValues['v_lastbalance']:=get_lastbalance(adoqemp.FieldValues['empid'],adoqtmp.FieldValues['yearmonth']);
try
execsql;
except
DataTJ_flag:=False; break; exit;
end;
end;//3.4.End
//3.5.2如果员工工序收入入总账成功则修改[TJ_flag]字段值
with adoq_tjflag do
begin
close; sql.Clear;
sql.Add('update emp_payout set Tjflag=1 where log_id=:v_log_id');
parameters.ParamValues['v_log_id']:=adoqtmp.FieldValues['log_id'];
try
execsql;
except
DataTJ_flag:=False; break; exit;
end;
end;//3.5.2.End
end;
end;
//3.提交数据
if not DataTJ_flag then
begin
if datamod.ADOCon.InTransaction then datamod.ADOCon.RollbackTrans;
MsgErr(handle,'统计人员收支总账报表失败--月薪收入！');
break; exit;
end;
if datamod.ADOCon.InTransaction then
begin
try
datamod.ADOCon.CommitTrans;
except
MsgErr(handle,'统计人员收支总账报表失败--月薪收入！');
if datamod.ADOCon.InTransaction then datamod.ADOCon.RollbackTrans;
break; exit;
end;
end;
next;
end;
end;//3.计算员工月薪收入结束.End
//4.计算附加收入（A~当前日期）************************************************************
//4.1得到当前人员收支表所有已统计的记录(刷新)
with ADOQpersonshouzhi do
begin
//取出当前表内所有与empid有关的数据
close;sql.Clear;
sql.add('select * from personshouzhi where empid=:v_empid order by yearmonth desc');
parameters.ParamValues['v_empid']:=adoqemp.fieldvalues['empid'];
open;
end;
//4.1 得到当前时间段内的附收入
with adoqtmp do
begin
close;sql.clear;
sql.add('select empid, year_month,sum(money) as total from v_earn_abs');
sql.add('where TJflag=0 and empid=:v_empid');
sql.add('group by year_month,empid order by year_month');
parameters.ParamValues['v_empid']:=adoqemp.fieldvalues['empid'];
open; first;
while not eof do
begin
DataTJ_flag:=true; //初始化标量
//4.2 数据开始提交
datamod.ADOCon.BeginTrans;
with ADOQupdate do
begin
//4.2.1得到当前人员当月的收支总表的记录
close; sql.Clear;
sql.Add('select * from personshouzhi where empid=:v_empid and yearmonth=:V_yearmonth');
parameters.ParamValues['v_empid']:=adoqemp.FieldValues['empid'];
parameters.ParamValues['V_yearmonth']:=adoqtmp.FieldValues['year_month'];
try
execsql; open;
except
datatj_flag:=false; break; exit;
end;//4.2.1.End
//4.2.2如果该员工该月存在收支记录，则修改收入
if recordcount>0 then
begin
close; sql.Clear;
sql.Add('update personshouzhi set fshou=fshou+:v_total');
sql.Add('where empid=:v_empid and yearmonth=:V_yearmonth');
parameters.ParamValues['v_empid']:=adoqemp.FieldValues['empid'];
parameters.ParamValues['V_yearmonth']:=adoqtmp.FieldValues['year_month'];
parameters.ParamValues['v_total']:=adoqtmp.FieldValues['total'];
try
execsql;
except
DataTJ_flag:=false; break; exit;
end;
//4.2.2.1如果员工附加收入入总账成功则修改[Tjflag]字段值
with ADOQ_TJFlag do
begin
close; sql.Clear;
sql.Add('update earn_abs set Tjflag=1 where empid=:v_empid');
parameters.ParamValues['v_empid']:=adoqtmp.FieldValues['empid'];
try
execsql;
except
DataTJ_flag:=False; break; exit;
end;
end;//4.2.2.1.End
end//2.2.End
else
//如果没找到则添加.Else
begin
//2.3如果个人月收支表中没找到记录则直接新增
if ADOQpersonshouzhi.RecordCount =0 then
begin
close; sql.Clear;
sql.Add('insert into personshouzhi(empid,yearmonth,fshou)');
sql.Add('values(:v_empid,:v_yearmonth,:v_fshou)');
parameters.ParamValues['v_empid']:=adoqemp.FieldValues['empid'];
parameters.ParamValues['V_yearmonth']:=adoqtmp.FieldValues['year_month'];
parameters.ParamValues['v_fshou']:=adoqtmp.FieldValues['total'];
try
execsql;
except
DataTJ_flag:=False; break; exit;
end;
end//2.3.End
//2.4如果个人收支表中已有收入记录则找到上条记录取上月结余
else
begin
close; sql.Clear;
sql.Add('insert into personshouzhi(empid,yearmonth,fshou,lastbalance)');
sql.Add('values(:v_empid,:v_yearmonth,:v_fshou,:V_lastbalance)');
parameters.ParamValues['v_empid']:=adoqemp.FieldValues['empid'];
parameters.ParamValues['V_yearmonth']:=adoqtmp.FieldValues['year_month'];
parameters.ParamValues['v_fshou']:=adoqtmp.FieldValues['total'];
parameters.ParamValues['v_lastbalance']:=get_lastbalance(adoqemp.FieldValues['empid'],adoqtmp.FieldValues['year_month']);
try
execsql;
except
DataTJ_flag:=False; break; exit;
end;
end;//2.4.End
//2.5.2如果员工附加收入入总账成功则修改[Tjflag]字段值
with ADOQ_TJFlag do
begin
close; sql.Clear;
sql.Add('update earn_abs set Tjflag=1 where empid=:v_empid');
parameters.ParamValues['v_empid']:=adoqtmp.FieldValues['empid'];
try
execsql;
except
DataTJ_flag:=False; break; exit;
end;
end;//2.5.2.End
end;//如果没找到则添加.Else.End
end;//while not eof do.End.2
//2.提交数据
if not DataTJ_flag then
begin
if datamod.ADOCon.InTransaction then datamod.ADOCon.RollbackTrans;
MsgErr(handle,'统计人员收支总账报表失败--附加收入！');
break; exit;
end;
if datamod.ADOCon.InTransaction then
begin
try
datamod.ADOCon.CommitTrans;
except
MsgErr(handle,'统计人员收支总账报表失败--附加收入！');
if datamod.ADOCon.InTransaction then datamod.ADOCon.RollbackTrans;
break; exit;
end;
end;
next;
end;
end;
//5.计算支出（A~当前日期）************************************************************
//5.1得到当前人员收支表所有已统计的记录(刷新)
with ADOQpersonshouzhi do
begin
//取出当前表内所有与empid有关的数据
close;sql.Clear;
sql.add('select * from personshouzhi where empid=:v_empid order by yearmonth desc');
parameters.ParamValues['v_empid']:=adoqemp.fieldvalues['empid'];
try
open;
except
end;
end;
with adoqtmp do
begin
close;sql.clear;
sql.add('select empid, year_month,sum(money) as total from v_borrow');
sql.add('where TJflag=0 and empid=:v_empid');
sql.add('group by year_month,empid order by year_month');
parameters.ParamValues['v_empid']:=adoqemp.fieldvalues['empid'];
open;
while not eof do
begin
DataTJ_flag:=true; //初始化标量
//4.2 数据开始提交
datamod.ADOCon.BeginTrans;
with ADOQupdate do
begin
//4.2.1得到当前人员当月的收支总表的记录
close; sql.Clear;
sql.Add('select * from personshouzhi where empid=:v_empid and yearmonth=:V_yearmonth');
parameters.ParamValues['v_empid']:=adoqemp.FieldValues['empid'];
parameters.ParamValues['V_yearmonth']:=adoqtmp.FieldValues['year_month'];
try
execsql; open;
except
datatj_flag:=false; break; exit;
end;//4.2.1.End
//4.2.2如果该员工该月存在收支记录，则修改收入
if recordcount>0 then
begin
close; sql.Clear;
sql.Add('update personshouzhi set zhi=zhi+:v_total');
sql.Add('where empid=:v_empid and yearmonth=:V_yearmonth');
parameters.ParamValues['v_empid']:=adoqemp.FieldValues['empid'];
parameters.ParamValues['V_yearmonth']:=adoqtmp.FieldValues['year_month'];
parameters.ParamValues['v_total']:=adoqtmp.FieldValues['total'];
try
execsql;
except
DataTJ_flag:=false; break; exit;
end;
//4.2.2.1如果员工工序收入入总账成功则修改[Tjflag]字段值
with ADOQ_TJFlag do
begin
close; sql.Clear;
sql.Add('update brrow set Tjflag=1 where empid=:v_empid');
parameters.ParamValues['v_empid']:=adoqtmp.FieldValues['empid'];
try
execsql;
except
DataTJ_flag:=False; break; exit;
end;
end;//4.2.2.1.End
end//2.2.End
else
//如果没找到则添加.Else
begin
//2.3如果个人月收支表中没找到记录则直接新增
if ADOQpersonshouzhi.RecordCount =0 then
begin
close; sql.Clear;
sql.Add('insert into personshouzhi(empid,yearmonth,zhi)');
sql.Add('values(:v_empid,:v_yearmonth,:v_zhi)');
parameters.ParamValues['v_empid']:=adoqemp.FieldValues['empid'];
parameters.ParamValues['V_yearmonth']:=adoqtmp.FieldValues['year_month'];
parameters.ParamValues['v_zhi']:=adoqtmp.FieldValues['total'];
try
execsql;
except
DataTJ_flag:=False; break; exit;
end;
end//2.3.End
//2.4如果个人收支表中已有收入记录则找到上条记录取上月结余
else
begin
close; sql.Clear;
sql.Add('insert into personshouzhi(empid,yearmonth,zhi,lastbalance)');
sql.Add('values(:v_empid,:v_yearmonth,:v_zhi,:V_lastbalance)');
parameters.ParamValues['v_empid']:=adoqemp.FieldValues['empid'];
parameters.ParamValues['V_yearmonth']:=adoqtmp.FieldValues['year_month'];
parameters.ParamValues['v_zhi']:=adoqtmp.FieldValues['total'];
parameters.ParamValues['v_lastbalance']:=get_lastbalance(adoqemp.FieldValues['empid'],adoqtmp.FieldValues['year_month']);
try
execsql;
except
DataTJ_flag:=False; break; exit;
end;
end;//2.4.End
//2.5.2如果员工工序收入入总账成功则修改[Tjflag]字段值
with ADOQ_TJFlag do
begin
close; sql.Clear;
sql.Add('update brrow set Tjflag=1 where empid=:v_empid');
parameters.ParamValues['v_empid']:=adoqtmp.FieldValues['empid'];
try
execsql;
except
DataTJ_flag:=False; break; exit;
end;
end;//2.5.2.End
end;//如果没找到则添加.Else.End
end;//while not eof do.End.2
//2.提交数据
if not DataTJ_flag then
begin
if datamod.ADOCon.InTransaction then datamod.ADOCon.RollbackTrans;
MsgErr(handle,'统计人员收支总账报表失败--员工支出！');
break; exit;
end;
if datamod.ADOCon.InTransaction then
begin
try
datamod.ADOCon.CommitTrans;
except
MsgErr(handle,'统计人员收支总账报表失败--员工支出！');
if datamod.ADOCon.InTransaction then datamod.ADOCon.RollbackTrans;
break; exit;
end;
end;
next;
end; //while not eof do
end;//计算支出结束
adoqemp.next;
end;
ProgressBar1.Position:=0;
Label_Display.Visible:=false;
application.ProcessMessages;
end; //如果CheckBox1.checked为真则统计人员收支表.End
//查讯对应时间段的人员的工序收入信息
with adoquery1 do
begin
close;sql.clear;
sql.add('SELECT a.empid, b.empname, SUM(a.shou) AS shou, SUM(a.fshou) AS fshou, SUM(a.zhi) AS zhi,');
sql.add('(SUM(a.shou) + SUM(a.fshou) - SUM(a.zhi)) AS balance');
sql.Add('FROM PersonShouZhi a INNER JOIN employee b ON a.empid = b.empid');
if not CheckBox2.Checked then
sql.Add('WHERE (b.state = 1)');
sql.Add('group by a.empid,b.empname');
try
execsql; open;
except
MsgErr(handle,'查询人员收支总账失败！');
exit;
end;
if recordcount=0 then MsgOK(handle,'当前数据表内没找到数据！');
prtbtn.Enabled:=recordcount>0;
end;
end;
procedure TFrmEMPTJ_SHOUZHI_ALL.FormClose(Sender: TObject;
var Action: TCloseAction);
begin
Action:=Cafree;
end;
procedure TFrmEMPTJ_SHOUZHI_ALL.DBComboBox2Change(Sender: TObject);
begin
adoqemp.fieldvalues['empid']:=trim(adoqemp.fieldvalues['empid']);
end;
procedure TFrmEMPTJ_SHOUZHI_ALL.PrtBtnClick(Sender: TObject);
begin
InitPage(RvProject1,RvSystem1,apppath+'\'+ReportName,apppath+'\'+ReportNdrName,0);
SelectPage(RvProject1,'Report1',4);
//传递参数
AddParameter(RvProject1,'UserName',curAdmin.user_name);
AddParameter(RvProject1,'companyname',sysconfig.COMPANYANME);
//显示页
PageShow(RvProject1);
end;
procedure TFrmEMPTJ_SHOUZHI_ALL.DBEdit3KeyPress(Sender: TObject; var Key: Char);
begin
if not ( key in ['0'..'9',chr(8),chr(13)]) then abort;
end;
end.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -