etp_month_acc.pas

服装厂管理系统源程序压缩包, 请耐心阅读提供的文件包含的内容

            end//2.2.End
            else
            //如果没找到则添加.Else
            begin
              //2.3如果个人月收支表中没找到记录则直接新增
                close; sql.Clear;
                sql.Add('insert into Etp_ShouZhi(yearmonth,yinzhi)');
                sql.Add('values(:v_yearmonth,:v_yinzhi)');

                parameters.ParamValues['V_yearmonth']:=adoqtmp.FieldValues['yearmonth'];
                parameters.ParamValues['v_yinzhi']:=adoqtmp.FieldValues['total'];
                try
                  execsql;
                except
                  DataTJ_flag:=false;
                  if datamod.ADOCon.InTransaction then datamod.ADOCon.RollbackTrans;
                  break; exit;
                end;
            end;//如果没找到则添加.Else.End
          end;//with ADOQupdate do.End
          
          //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;



     //4.计算企业月实支工资的金额数（从个人收支表中得到）************************************************************
     DataTJ_flag:=true;    //初始化标量，用以控制循环的进度
     with adoqtmp do
     begin
        close; sql.clear;
        sql.add('select yearmonth,sum(zhi) as total from personshouzhi');
        sql.add('group by yearmonth order by yearmonth');
        open;
        
        first;
        while not eof do
        begin
          //2.数据开始提交
          DataTJ_flag:=true;
          datamod.ADOCon.BeginTrans;
          with ADOQupdate do
          begin
            //2.1得到当月的收支总表的记录
            close; sql.Clear;
            sql.Add('select * from Etp_ShouZhi where yearmonth=:V_yearmonth');
            parameters.ParamValues['V_yearmonth']:=adoqtmp.FieldValues['yearmonth'];
            try
              execsql; open;
            except
              DataTJ_flag:=false;
              if datamod.ADOCon.InTransaction then datamod.ADOCon.RollbackTrans;
              break; exit;
            end;//2.1.End
            //2.2如果该员工该月存在收支记录，则修改收入
            if recordcount>0 then
            begin
              close; sql.Clear;
              sql.Add('update Etp_ShouZhi set shizhi=:v_shizhi');
              sql.Add('where yearmonth=:V_yearmonth');
              parameters.ParamValues['V_yearmonth']:=adoqtmp.FieldValues['yearmonth'];
              parameters.ParamValues['v_shizhi']:=adoqtmp.FieldValues['total'];
              try
                execsql;
              except
                DataTJ_flag:=false;
                if datamod.ADOCon.InTransaction then datamod.ADOCon.RollbackTrans;
                break; exit;
              end;

            end//2.2.End
            else
            //如果没找到则添加.Else
            begin
              //2.3如果个人月收支表中没找到记录则直接新增
                close; sql.Clear;
                sql.Add('insert into Etp_ShouZhi(yearmonth,shizhi)');
                sql.Add('values(:v_yearmonth,:v_shizhi)');
                parameters.ParamValues['V_yearmonth']:=adoqtmp.FieldValues['yearmonth'];
                parameters.ParamValues['v_shizhi']:=adoqtmp.FieldValues['total'];
                try
                  execsql;
                except
                  DataTJ_flag:=false;
                  if datamod.ADOCon.InTransaction then datamod.ADOCon.RollbackTrans;
                  break; exit;
                end;
            end;//如果没找到则添加.Else.End
          end;//with ADOQupdate do.End
          
          //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~当前日期）************************************************************
     with adoqtmp do
     begin
        close; sql.clear;
        sql.add('select yearmonth, sum(money) as total from v_payout where tjflag=0');
        sql.add('group by yearmonth order by yearmonth');
        open;
        
        first;
        while not eof do
        begin
          //2.数据开始提交
          DataTJ_flag:=true;  //初始化标量，用以控制循环的进度
          datamod.ADOCon.BeginTrans;
          with ADOQupdate do
          begin
            //2.1得到当月的收支总表的记录
            close; sql.Clear;
            sql.Add('select * from Etp_ShouZhi where yearmonth=:V_yearmonth');
            parameters.ParamValues['V_yearmonth']:=adoqtmp.FieldValues['yearmonth'];
            try
              execsql; open;
            except
              DataTJ_flag:=false;
              if datamod.ADOCon.InTransaction then datamod.ADOCon.RollbackTrans;
              break; exit;
            end;//2.1.End
            //2.2如果该员工该月存在收支记录，则修改收入
            if recordcount>0 then
            begin
              close; sql.Clear;
              sql.Add('update Etp_ShouZhi set Zhiself=zhiself+:v_total');
              sql.Add('where yearmonth=:V_yearmonth');
              parameters.ParamValues['V_yearmonth']:=adoqtmp.FieldValues['yearmonth'];
              parameters.ParamValues['v_total']:=adoqtmp.FieldValues['total'];
              try
                execsql;
              except
                DataTJ_flag:=false;
                if datamod.ADOCon.InTransaction then datamod.ADOCon.RollbackTrans;
                break; exit;
              end;

            end//2.2.End
            else
            //如果没找到则添加.Else
            begin
              //2.3如果个人月收支表中没找到记录则直接新增
                close; sql.Clear;
                sql.Add('insert into Etp_ShouZhi(yearmonth,zhiself)');
                sql.Add('values(:v_yearmonth,:v_zhiself)');

                parameters.ParamValues['V_yearmonth']:=adoqtmp.FieldValues['yearmonth'];
                parameters.ParamValues['v_zhiself']:=adoqtmp.FieldValues['total'];
                try
                  execsql;
                except
                  DataTJ_flag:=false;
                  if datamod.ADOCon.InTransaction then datamod.ADOCon.RollbackTrans;
                  break; exit;
                end;
            end;//如果没找到则添加.Else.End

            //2.2.1如果入总账成功则修改[Tjflag]字段值
            with ADOQ_TJFlag do
            begin
                close; sql.Clear;
                sql.Add('update payout set Tjflag=1');
                try
                  execsql;
                except
                  DataTJ_flag:=False;
                  if datamod.ADOCon.InTransaction then datamod.ADOCon.RollbackTrans;
                  break; exit;
                end;
            end;//2.2.1.End
          end;//with ADOQupdate do.End
          
          //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;
     ProgressBar1.Position:=0;
     Label_Display.Visible:=false;
     application.ProcessMessages;
   end;//if CheckBox1.Checked then


  //查讯对应时间段的收入信息
  with ADOQ_Etp_shouzhi do
  begin
    close;sql.clear;
    sql.add('select id,yearmonth, shou, yinzhi, shizhi,zhiself,balance from Etp_shouzhi');
    sql.add('where (yearmonth between :v_sd and :v_ed) order by yearmonth ');
    parameters.ParamValues['v_sd']:=formatdatetime('yyyy-mm',strtodate(sdate+'-01'));
    parameters.ParamValues['v_ed']:=formatdatetime('yyyy-mm',strtodate(edate+'-01'));
    execsql; open;
{    (Fields.FieldByName('crsum') as TNumericField).DisplayFormat:='￥,0.00';
    (Fields.FieldByName('desum') as TNumericField).DisplayFormat:='￥,0.00';
    (Fields.FieldByName('sasum') as TNumericField).DisplayFormat:='￥,0.00';
    (Fields.FieldByName('btsum') as TNumericField).DisplayFormat:='￥,0.00';
    (Fields.FieldByName('adsum') as TNumericField).DisplayFormat:='￥,0.00'; }
    if recordcount=0 then MsgOK(handle,'当前时间段内没找到数据！');
    prtbtn.Enabled:=recordcount>0;
    if recordcount>0 then
    begin
      sdate:=sdate+'月';
      edate:=edate+'月';
    end;
  end;
end;

procedure TForm_Etp_Month_ACC.PrtBtnClick(Sender: TObject);
begin
  InitPage(RvProject1,RvSystem1,apppath+'\'+ReportName,apppath+'\'+ReportNdrName,0);
  SelectPage(RvProject1,'Report1',8);
  //传递参数
  AddParameter(RvProject1,'UserName',curAdmin.user_name);
  AddParameter(RvProject1,'companyname',sysconfig.COMPANYANME);
  AddParameter(RvProject1,'sdate',sdate);
  AddParameter(RvProject1,'edate',edate);
  //显示页
  PageShow(RvProject1);  
end;

end.