emptj_shouzhi_all.pas

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

unit EMPTJ_SHOUZHI_ALL;

interface

uses    
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB, StdCtrls, Buttons, Grids, DBGrids, ExtCtrls,
  ComCtrls, RpDefine, RpCon, RpConDS, Menus, Mask, DBCtrls, RpRave, RpBase,
  RpSystem;

type
  TFrmEMPTJ_SHOUZHI_ALL = class(TForm)
    DataSource: TDataSource;
    DBGrid: TDBGrid;
    ADOQtmp: TADOQuery;
    RvSystem1: TRvSystem;
    RvProject1: TRvProject;
    Panel1: TPanel;
    SearchBtn: TBitBtn;
    PrtBtn: TBitBtn;
    ExtBtn: TBitBtn;
    Bevel1: TBevel;
    ADOQuery1: TADOQuery;
    ADOQuery1shou: TBCDField;
    ADOQuery1fshow: TBCDField;
    ADOQuery1zhi: TBCDField;
    ADOQuery1balance: TBCDField;
    ADOQpersonshouzhi: TADOQuery;
    ADOQemp: TADOQuery;
    ADOQuery1empname: TStringField;
    ADOQuery1empid: TIntegerField;
    CheckBox1: TCheckBox;
    ProgressBar1: TProgressBar;
    Rv_TJ_EMP_ALLSZ: TRvDataSetConnection;
    CheckBox2: TCheckBox;
    ADOQupdate: TADOQuery;
    ADOQGetyearmonth: TADOQuery;
    ADOQ_TJFlag: TADOQuery;
    Label_Display: TLabel;
    procedure SearchBtnClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure DBComboBox2Change(Sender: TObject);
    procedure PrtBtnClick(Sender: TObject);
    procedure DBEdit3KeyPress(Sender: TObject; var Key: Char);
  private
    { Private declarations }
    function Get_lastbalance(empid:integer;yearmonth:string):real;
  public
    { Public declarations }
  end;

var
  FrmEMPTJ_SHOUZHI_ALL: TFrmEMPTJ_SHOUZHI_ALL;

implementation
uses data,sharevar,sharefun, ColorSetup;
{$R *.dfm}

//得到对应人员在当前日期前的第一个日期的结余
function TFrmEMPTJ_SHOUZHI_ALL.Get_lastbalance(empid:integer;yearmonth:string):real;
begin
  result:=0;
  with ADOQGetyearmonth do
  begin
    close; sql.Clear;
    sql.Add('select balance from personshouzhi where empid=:empid and yearmonth<:yearmonth order by yearmonth desc');
    parameters.ParamValues['empid']:=empid;
    parameters.ParamValues['yearmonth']:=yearmonth;
    try
      execsql; open;
    except
    end;
    first; if recordcount>0 then
    result:=fieldvalues['balance'];
    close;
  end;
end;

procedure TFrmEMPTJ_SHOUZHI_ALL.SearchBtnClick(Sender: TObject);
var inprice_num:integer;  //未指定工价的工序数量
    tmpstr:string;
    DataTJ_flag:boolean;
begin
   //添加人员编码
  with ADOQemp do
  begin
    close;sql.clear;
    sql.add('select empid,empname,sex from employee ');
    if not CheckBox2.Checked then
    sql.Add('where state=1');
    try
      execsql; open;
    except
      MsgErr(handle,'统计人员收支总账报表失败！');
      exit;
    end;
    if recordcount =0 then
    begin
      MsgErr(handle,'无人员信息!');
      exit;
    end;
  end;
  
  //如果CheckBox1.checked为真则统计人员收支表
  if CheckBox1.Checked then
  begin
   //检查是否给当前所有已验收未入总账的工序定工价
   with adoqtmp do
   begin
     close; sql.Clear;
     sql.add('select a.ord_id, b.seq_name, b.seq_price from v_outbom_log2 a,sequence_price b');
     sql.add('where a.flag=1 and a.TJflag=0 and (a.temp_key=b.temp_key)');
     sql.Add('group by  b.temp_key, a.ord_id, b.seq_name, b.seq_price');
     try
        open; first;
     except
        Msgerr(handle,'统计人员收支总账报表失败--工价查找！');
        exit;
     end;
     DataTJ_flag:=true;
     inprice_num:=0;
     while not eof do
     begin
        if (fieldvalues['seq_price']=0) or (fieldvalues['seq_price']=null) then
        begin
          tmpstr:='订单批号：['+fieldvalues['ord_id']+'下的工序 ['+fieldvalues['seq_name']+'] 还没有给工价';
          msgerr(handle, tmpstr);
          DataTJ_flag:=false; Inc(inprice_num);
        end;
        next;
     end;
     if not DataTJ_flag then
     begin
        tmpstr:='统计人员收支总账报表失败--共有 ['+inttostr(inprice_num)+'] 个工序工价未指定！';
        tmpstr:=tmpstr+#13+#13+'     请先到工序工价表指定工价再作统计！';
        msgerr(handle,tmpstr);
        abort; exit;
     end;
   end;//工序工价统计结束.End

   //初始化进程条
   ProgressBar1.Max:=adoqemp.RecordCount;
   ProgressBar1.Position:=0;
   Label_Display.Visible:=true;
   application.ProcessMessages;
   adoqemp.First;
   while not adoqemp.Eof do
   begin
     ProgressBar1.Position:=ProgressBar1.Position+1;

     //1.得到当前人员收支表所有已统计的记录(刷新)
     with ADOQpersonshouzhi do
     begin
        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;

     //2.计算工序收入（A~当前日期）************************************************************
     with adoqtmp do
     begin
        close;sql.clear;
        sql.add('select a.empid,a.yearmonth,(sum(b.seq_price*a.num)) as total from v_outbom_log2  a,sequence_price b');
        sql.add('where a.flag=1 and a.TJflag=0 and (a.temp_key=b.temp_key) and (a.empid=:v_empid)');
        sql.add('group by a.yearmonth, a.empid order by  a.yearmonth');
        parameters.ParamValues['v_empid']:=adoqemp.fieldvalues['empid'];
        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 personshouzhi where empid=:v_empid and yearmonth=:V_yearmonth');
            parameters.ParamValues['v_empid']:=adoqemp.FieldValues['empid'];
            parameters.ParamValues['V_yearmonth']:=adoqtmp.FieldValues['yearmonth'];
            try
              execsql; open;
            except
              datatj_flag:=false; break; exit;
            end;//2.1.End
            //2.2如果该员工该月存在收支记录，则修改收入
            if recordcount>0 then
            begin
              close; sql.Clear;
              sql.Add('update personshouzhi set shou=shou+: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['yearmonth'];
              parameters.ParamValues['v_total']:=adoqtmp.FieldValues['total'];
              try
                execsql;
              except
                DataTJ_flag:=false; break; exit;
              end;

              //2.5.1如果员工工序收入入总账成功则修改[Tjflag]字段值
              with ADOQ_TJFlag do
              begin
                close; sql.Clear;
                sql.Add('update outbom_log 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.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,shou)');
                sql.Add('values(:v_empid,:v_yearmonth,:v_shou)');
                parameters.ParamValues['v_empid']:=adoqemp.FieldValues['empid'];
                parameters.ParamValues['V_yearmonth']:=adoqtmp.FieldValues['yearmonth'];
                parameters.ParamValues['v_shou']:=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,shou,lastbalance)');
                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['total'];
                parameters.ParamValues['v_lastbalance']:=get_lastbalance(adoqemp.FieldValues['empid'],adoqtmp.FieldValues['yearmonth']);
                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 outbom_log 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;//2.计算工序主收入结束.End
     

     //3.计算月薪收入（A~当前日期）************************************************************
     //3.1得到当前人员收支表所有已统计的记录(刷新)
     with ADOQpersonshouzhi do
     begin
        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 log_id, yearmonth, money from emp_payout');
        sql.add('where TJflag=0 and empid=:v_empid');
        sql.add('order by yearmonth');
        parameters.ParamValues['v_empid']:=adoqemp.fieldvalues['empid'];
        open; first;

        while not eof do
        begin
          //3.数据开始提交
          DataTJ_flag:=true;
          datamod.ADOCon.BeginTrans;
          with ADOQupdate do
          begin
            //3.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['yearmonth'];
            try
              execsql; open;
            except
              datatj_flag:=false; break; exit;
            end;//3.1.End
            //3.2如果该员工该月存在收支记录，则修改收入
            if recordcount>0 then
            begin
              close; sql.Clear;
              sql.Add('update personshouzhi set shou=shou+:V_money');
              sql.Add('where empid=:v_empid and yearmonth=:V_yearmonth');
              parameters.ParamValues['v_empid']:=adoqemp.FieldValues['empid'];
              parameters.ParamValues['V_yearmonth']:=adoqtmp.FieldValues['yearmonth'];
              parameters.ParamValues['V_money']:=adoqtmp.FieldValues['money'];
              try
                execsql;
              except
                DataTJ_flag:=false; break; exit;
              end;
              //3.5.1如果员工工序收入入总账成功则修改[Tjflag]字段值
              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.1.End
            end//3.2.End
            else
            begin
              //3.3如果个人月收支表中没找到记录则直接新增
              if ADOQpersonshouzhi.RecordCount =0 then
              begin
                close; sql.Clear;
                sql.Add('insert into personshouzhi(empid,yearmonth,shou)');
                sql.Add('values(:v_empid,:v_yearmonth,:v_shou)');
                parameters.ParamValues['v_empid']:=adoqemp.FieldValues['empid'];
                parameters.ParamValues['V_yearmonth']:=adoqtmp.FieldValues['yearmonth'];
                parameters.ParamValues['v_shou']:=adoqtmp.FieldValues['money'];
                try
                  execsql;
                except
                  DataTJ_flag:=False; break; exit;
                end;
              end//3.3.End
              //3.4如果个人收支表中已有收入记录则找到上条记录取上月结余
              else
              begin
                close; sql.Clear;
                sql.Add('insert into personshouzhi(empid,yearmonth,shou,lastbalance)');