emptj_shouzhi_month.pas

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

unit EMPTJ_SHOUZHI_month;

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_month = class(TForm)
    DataSource: TDataSource;   
    DBGrid: TDBGrid;
    ADOQtmp: TADOQuery;
    Rv_TJ_EMP_SZ: TRvDataSetConnection;
    RvSystem1: TRvSystem;
    RvProject1: TRvProject;
    Panel1: TPanel;
    Label4: TLabel;
    Label1: TLabel;
    Label2: TLabel;
    DBComboBox2: TComboBox;
    edtname: TEdit;
    edtsex: TEdit;
    GroupBox1: TGroupBox;
    Label5: TLabel;
    Label6: TLabel;
    sDate: TDateTimePicker;
    eDate: TDateTimePicker;
    SearchBtn: TBitBtn;
    PrtBtn: TBitBtn;
    ExtBtn: TBitBtn;
    Bevel1: TBevel;
    ADOQuery1: TADOQuery;
    CheckBox1: TCheckBox;
    ADOQuery1id: TAutoIncField;
    ADOQuery1yearmonth: TStringField;
    ADOQuery1lastbalance: TBCDField;
    ADOQuery1shou: TBCDField;
    ADOQuery1fshow: TBCDField;
    ADOQuery1zhi: TBCDField;
    ADOQuery1balance: TBCDField;
    ADOQpersonshouzhi: TADOQuery;
    ADOQemp: TADOQuery;
    ProgressBar1: TProgressBar;
    ADOQupdate: TADOQuery;
    ADOQGetyearmonth: TADOQuery;
    Label_Display: TLabel;
    ADOQ_TJFlag: TADOQuery;
    procedure FormShow(Sender: TObject);
    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);
    procedure DBComboBox2KeyPress(Sender: TObject; var Key: Char);
  private
    { Private declarations }
    emp_id, emp_name, s_date, e_date: string;
    function Get_lastbalance(empid:integer;yearmonth:string):real;
  public
    { Public declarations }
  end;

var
  FrmEMPTJ_SHOUZHI_month: TFrmEMPTJ_SHOUZHI_month;

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


//得到对应人员在当前日期前的第一个日期的结余
function TFrmEMPTJ_SHOUZHI_month.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_month.FormShow(Sender: TObject);
var tmp_date:Tdatetime;
begin
  GetHostDateTime(datamod.ADOCon,tmp_date);
  edate.DateTime :=tmp_date;
  //添加人员编码
  with ADOQemp do
  begin
    close;sql.clear;
    sql.add('select empid from employee where state=1');
    try
      execsql; open;
    except
      MsgErr(handle,'添加人员信息失败!'); exit;
    end;
    if recordcount =0 then
    begin
        MsgErr(handle,'无人员信息!');
        exit;
    end;
    first;
    DBComboBox2.Items.Clear;
    while not eof do
    begin
        DBComboBox2.Items.Add(fieldvalues['empid']);
        next;
    end;
  end;
  
  //添加所有人员信息，包括非在职人员
  with ADOQemp do
  begin
    close; sql.clear;
    sql.add('select empid,empname,sex from employee');
    try
      execsql; open;
    except
      MsgErr(handle,'得到人员信息失败!'); exit;
    end;
  end;
  DBComboBox2.SetFocus;
end;

procedure TFrmEMPTJ_SHOUZHI_month.SearchBtnClick(Sender: TObject);
var inprice_num :integer; //未指定工价的工序数量
    tmpstr:string;
    DataTJ_flag:boolean;
begin
  if trim(edtname.Text)='' then
  begin
    MsgErr(handle,'待查询人员信息为空，请在员工编号文本框内输入员工编号，再按回车键！');
    DBComboBox2.SetFocus;
    exit;
  end;
  
  //如果是重新统计则执行以下操作
  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) and a.empid=:V_empid');
     sql.Add('group by  b.temp_key, a.ord_id, b.seq_name, b.seq_price, a.empid');
     parameters.ParamValues['V_empid']:=strtoint(trim(DBComboBox2.Text));
     try
        open; first;
     except
        Msgerr(handle,'统计人员月收支报表失败--工价查找！');
        exit;
     end;
      //初始化进程条
     ProgressBar1.Max:=recordcount;
     ProgressBar1.Position:=0;
     Label_Display.Visible:=true;
     application.ProcessMessages;
     DataTJ_flag:=true;
     inprice_num:=0;
     while not eof do
     begin
        ProgressBar1.Position:=ProgressBar1.Position+1;
        application.ProcessMessages;
        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);
        ProgressBar1.Position:=0;
        Label_Display.Visible:=false;
        application.ProcessMessages;
        abort; exit;
     end;
   end;//工序工价统计结束.End
      
     //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']:=strtoint(trim(DBComboBox2.Text));
        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']:=strtoint(trim(DBComboBox2.Text));
        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;
              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 personshouzhi set shou=shou+:V_total');
              sql.Add('where empid=:v_empid and yearmonth=:V_yearmonth');
              parameters.ParamValues['v_empid']:=strtoint(trim(DBComboBox2.Text));
              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;

              //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;
                  if datamod.ADOCon.InTransaction then datamod.ADOCon.RollbackTrans;
                  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']:=strtoint(trim(DBComboBox2.Text));
                parameters.ParamValues['V_yearmonth']:=adoqtmp.FieldValues['yearmonth'];
                parameters.ParamValues['v_shou']:=adoqtmp.FieldValues['total'];
                try
                  execsql;
                except
                  DataTJ_flag:=False;
                  if datamod.ADOCon.InTransaction then datamod.ADOCon.RollbackTrans;
                  break; exit;
                end;
              end//2.3.End
              //2.4如果个人收支表中已有收入记录则找到上条记录取上月结余
              else
              begin
                close; sql.Clear;