unit_salarymanager.pas

管理软件/可以用于有会员积分管理的场所,支持会员卡管理

begin

  temp:= trim(cbb_year.Text+'-'+cbb_month.Text);
  search_year_month:=trim(cbb_year.Text+'-'+cbb_month.Text);
  if temp ='' then exit;
  
  //首先查找该年月的员工工资是否已经保存在数据库中
  {with dmod.qrydata do
  begin
    Close;
    SQL.Text :='select count(*) as count from salarybasic where year_month='+#39+search_year_month+#39;
    Open;
    if FieldByName('count').AsInteger >0 then
    begin
      SearchExistedEmployeeSalary(search_year_month);
      StatusBar1.Panels[0].Text :='统计月份:'+trim(edt_SalaryStatistic.Text);
      StatusBar1.Panels[1].Text :='提示:工资数据直接从数据库取得';
      exit;
    end;
  end; }

  temp_mindate:= trim(cbb_year.Text+'-'+cbb_month.Text)+'-01';
  temp_year:=StringGetChar(temp,'-');
  temp_maxdate:=GetMaxDate(StrToInt(temp_year),StrToInt(copy(temp,2,length(temp))));

  with dmod.qrydata do
  begin
    Close;
    SQL.Text :='select emp_no,emp_name from employee order by emp_no';
    Open;
    arow:=1;
    while not eof do
    begin
      //设置最基本两项
      temp_empno:=FieldByName('emp_no').AsString;
      sg_salary.Cells[0,arow]:=FieldByName('emp_no').AsString ;
      sg_salary.Cells[1,arow]:=FieldByName('emp_name').AsString ;

      //查找基本工资
      dmod.qrydata2.Close ;
      dmod.qrydata2.SQL.Text :='select b_salary from EmployeeBasicSalary where emp_no='+#39+temp_empno+#39;
      dmod.qrydata2.Open ;
      sg_salary.Cells[2,arow]:=FloatToStr(dmod.qrydata2.fieldbyname('b_salary').asfloat) ;

      //计算员工提成---仅针对单个服务
      tc_items:=0.0;
      dmod.qrydata2.Close ;
      dmod.qrydata2.SQL.Text :='select ccd.item_id,cc.cost_date from customercost cc,customercostdetail ccd '+
                               ' where cc.cost_no=ccd.cost_no and cost_date between '+#39+temp_mindate+#39+' and '+
                               #39+temp_maxdate+#39+' and ccd.emp_no='+#39+temp_empno+#39+' and ccd.itemortype="0"'+
                               ' order by cc.cost_no';
      dmod.qrydata2.Open ;   //得到这个月以来该员工所有提供的服务列表
      while not dmod.qrydata2.Eof do
      begin
        temp_itemid:=dmod.qrydata2.fieldbyname('item_id').AsString ;

        dmod.qrydata3.Close;
        dmod.qrydata3.SQL.Text :='select distinct conf_type from serviceitemextra';
        dmod.qrydata3.Open ;
        if dmod.qrydata3.FieldByName('conf_type').AsString ='0' then        //如果采用统一设置
           temp_sql:='select tc_type,tc_value,item_price from serviceitemextra,serviceitem where serviceitemextra.item_id=serviceitem.item_id and serviceitemextra.item_id='+#39+temp_itemid+#39
        else  if dmod.qrydata3.FieldByName('conf_type').AsString ='1' then  //如果采用分别设置
           temp_sql:='select tc_type,tc_value,item_price from serviceitemextra,serviceitem where serviceitemextra.item_id=serviceitem.item_id and serviceitemextra.item_id='+#39+temp_itemid+#39+' and emp_no='+#39+temp_empno+#39;

        dmod.qrydata3.Close ;
        dmod.qrydata3.SQL.Text :=temp_sql;
        dmod.qrydata3.Open ;
        if dmod.qrydata3.FieldByName('tc_type').AsString ='0' then         //如果采用固定提成
          tc_items:=tc_items+dmod.qrydata3.fieldbyname('tc_value').AsFloat
        else if dmod.qrydata3.FieldByName('tc_type').AsString ='0' then    //如果采用按百分比提成
          tc_items:=tc_items+dmod.qrydata3.fieldbyname('tc_value').AsFloat*dmod.qrydata3.fieldbyname('item_price').AsFloat;

        dmod.qrydata2.next;
      end;

      //计算员工提成---仅针对套餐服务
      tc_meals:=0.0;
      dmod.qrydata2.Close ;
      dmod.qrydata2.SQL.Text :='select ccd.type_id,cc.cost_date from customercost cc,customercostdetail ccd '+
                               ' where cc.cost_no=ccd.cost_no and cost_date between '+#39+temp_mindate+#39+' and '+
                               #39+temp_maxdate+#39+' and ccd.emp_no='+#39+temp_empno+#39+' and ccd.itemortype="1"'+
                               ' order by cc.cost_no';
      dmod.qrydata2.Open ;   //得到这个月以来该员工所有提供的服务列表
      while not dmod.qrydata2.Eof do
      begin
        temp_typeid:=dmod.qrydata2.fieldbyname('type_id').AsString ;

        dmod.qrydata3.Close;
        dmod.qrydata3.SQL.Text :='select distinct conf_type from servicemealextra';
        dmod.qrydata3.Open ;
        if dmod.qrydata3.FieldByName('conf_type').AsString ='0' then        //如果采用统一设置
           temp_sql:='select tc_type,tc_value,promotion_sum from servicemealextra,servicemealtype where servicemealextra.type_id=servicemealtype.type_id and servicemealextra.type_id='+#39+temp_typeid+#39
        else  if dmod.qrydata3.FieldByName('conf_type').AsString ='1' then  //如果采用分别设置
           temp_sql:='select tc_type,tc_value,promotion_sum from servicemealextra,servicemealtype where servicemealextra.type_id=servicemealtype.type_id and servicemealextra.type_id='+#39+temp_typeid+#39+' and emp_no='+#39+temp_empno+#39;

        dmod.qrydata3.Close ;
        dmod.qrydata3.SQL.Text :=temp_sql;
        dmod.qrydata3.Open ;
        if dmod.qrydata3.FieldByName('tc_type').AsString ='0' then         //如果采用固定提成
          tc_meals:=tc_meals+dmod.qrydata3.fieldbyname('tc_value').AsFloat
        else if dmod.qrydata3.FieldByName('tc_type').AsString ='0' then    //如果采用按百分比提成
          tc_meals:=tc_meals+dmod.qrydata3.fieldbyname('tc_value').AsFloat*dmod.qrydata3.fieldbyname('promotion_sum').AsFloat;

        dmod.qrydata2.next;
      end;
      
      sg_salary.Cells[3,arow]:=FloatToStr(tc_items+tc_meals);

      //得到其他工资增加项目
      for i:=0 to addcount-1 do
      begin
        sg_salary.cells[4+i,arow]:=copy(SalaryAddItems[i],2,length(SalaryAddItems[i]));
      end;
      //得到其他工资减少项目
      for i:=0 to reducedcount-1 do
      begin
        sg_salary.cells[4+addcount+i,arow]:=copy(SalaryReducedItems[i],2,length(SalaryReducedItems[i]));
      end;

      //计算应该发的工资
      totalsalary:=0.0;
      totalsalary:=StrToFloat(sg_salary.cells[2,arow])+StrToFloat(sg_salary.cells[3,arow]);
      for i:=0 to addcount-1 do
      begin
        totalsalary:=totalsalary+StrToFloat(sg_salary.Cells[4+i,arow]);
      end;
      for i:=0 to reducedcount-1 do
      begin
        totalsalary:=totalsalary-StrToFloat(sg_salary.Cells[4+addcount+i,arow]);
      end;
      sg_salary.cells[4+addcount+reducedcount,arow]:=FloatToStr(totalsalary);

      inc(arow);
      next;//员工记录下移一条;
    end;
  end;
  StatusBar1.Panels[0].Text :='统计月份:'+trim(cbb_year.Text+'-'+cbb_month.Text);
  //StatusBar1.Panels[1].Text :='提示:工资数据未保存到数据库';
  if arow<>1 then
  begin
    sg_salary.RowCount :=arow+1;
    sbtn_edit.Enabled:=true;
    sbtn_save.Enabled:=true;
    sbtn_edit.Enabled := GetPower(SysUserId,'工资管理','修改权');
    sbtn_save.Enabled := sbtn_edit.Enabled ;
  end;
end;

procedure Tfrm_salarymanager.sbtn_saveClick(Sender: TObject);
var
  arow,i:integer;
begin
  try
    dmod.Database.StartTransaction ;
    with dmod.qrydata do
    begin
      Close;
      SQL.Text :='select count(*) as count from salarybasic where year_month='+#39+Search_Year_Month+#39;
      Open;
      if FieldByName('count').AsInteger >0 then
      begin
        Close;
        SQL.Text :='delete from salarybasic where year_month='+#39+Search_Year_Month+#39;
        ExecSQL;

        Close;
        SQL.Text :='delete from salarydynamic where year_month='+#39+Search_Year_month+#39;
        ExecSQL;
      end;

      for arow:=1 to sg_salary.RowCount-2 do
      begin
        Close;
        SQL.Text :='Insert into salarybasic(emp_no,b_salary,s_extra,t_salary,o_add,o_reduced,year_month) '+
                   ' values(:empno,:bsalary,:sextra,:tsalary,:oadd,:oreduced,:yearmonth)';
        ParamByName('empno').AsString :=sg_salary.Cells[0,arow];
        ParamByName('bsalary').AsFloat :=StrToFloat(sg_salary.Cells[2,arow]);
        ParamByName('sextra').AsFloat :=StrToFloat(sg_salary.Cells[3,arow]);
        ParamByName('tsalary').AsFloat :=StrToFloat(sg_salary.Cells[4+addcount+reducedcount,arow]);
        addamount:=0.0;
        for i:=0 to addcount-1 do
        begin
          addamount:=addamount+StrToFloat(sg_salary.Cells[4+i,arow]);
        end;
        ParamByName('oadd').AsFloat    := addamount;
        reducedamount:=0.0;
        for i:=0 to reducedcount-1 do
        begin
          reducedamount:=reducedamount+ StrToFloat(sg_salary.Cells[4+addcount+i,arow]);
        end;
        ParamByName('oreduced').AsFloat:= reducedamount;
        ParamByName('yearmonth').AsString := Search_Year_Month;
        ExecSQL;

        for i:=0 to addcount-1 do
        begin
          Close;
          SQL.Text :='insert into salarydynamic(emp_no,item_name,item_value,year_month) '+
                   ' values(:empno,:itemname,:itemvalue,:yearmonth)';
          ParamByName('empno').AsString :=sg_salary.Cells[0,arow];
          ParamByName('itemname').AsString :=sg_salary.Cells[4+i,0];
          ParamByName('itemvalue').AsFloat :=StrToFloat(sg_salary.Cells[4+i,arow]);
          ParamByName('yearmonth').AsString := Search_Year_Month;
          ExecSQL;
        end;

        for i:=0 to reducedcount-1 do
        begin
          Close;
          SQL.Text :='insert into salarydynamic(emp_no,item_name,item_value,year_month) '+
                   ' values(:empno,:itemname,:itemvalue,:yearmonth)';
          ParamByName('empno').AsString :=sg_salary.Cells[0,arow];
          ParamByName('itemname').AsString :=sg_salary.Cells[4+addcount+i,0];
          ParamByName('itemvalue').AsFloat :=StrToFloat(sg_salary.Cells[4+addcount+i,arow]);
          ParamByName('yearmonth').AsString := Search_Year_Month;
          ExecSQL;
        end;

      end;
    end;
  finally
    try
      dmod.Database.Commit ;
      MessageBox(handle,'员工该月份的工资已经保存到数据库!','提示',mb_ok+mb_iconinformation);
      sg_salary.Options := [goFixedVertLine,goFixedHorzLine,goVertLine,goHorzLine,goRangeSelect,goColSizing,goTabs,goRowSelect];
      sbtn_save.Enabled :=false;
      sbtn_edit.Enabled :=false;
    except
      dmod.Database.Rollback ;
      MessageBox(handle,'员工该月份的工资保存到数据库失败!','提示',mb_ok+mb_iconinformation);
    end;
  end;
end;

procedure Tfrm_salarymanager.sbtn_editClick(Sender: TObject);
begin
  sg_salary.Options:=[goFixedVertLine,goFixedHorzLine,goVertLine,goHorzLine,goRangeSelect,goColSizing,goTabs,goEditing];
end;

procedure Tfrm_salarymanager.sbtn_printClick(Sender: TObject);
begin
  DoReportSalary;
end;

end.