USE [SG3HRM]

GO

/****** Object:  UserDefinedFunction [dbo].[fx_NgayBatDauThang]    Script Date: 11/02/2011 14:59:24 ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

/*

Ngay bat dau tinh luong

*/

ALTER FUNCTION [dbo].[fx_NgayBatDauThang]

 (@Thang  TINYINT,

 @Nam   INT) 

RETURNS Datetime AS 

BEGIN

 DECLARE @NgayBDThang  TINYINT

 Declare @NgayKTThang    tinyint

 DECLARE @NgayBDTinhLuong  datetime

 SELECT TOP 1 @NgayBDThang = nNgayBatDauThang FROM TBh_THONGSOHETHONG

 -- Luong thay bang dong duoi

 --SELECT TOP 1 @NgayKTThang = NgayBDThang FROM TB_THONGSOHETHONG

 SELECT TOP 1 @NgayKTThang = nNgayKetThucThang FROM TBh_THONGSOHETHONG

 If @NgayBDThang = 1 and @NgayKTThang = 31

 --Luong rem thay bang dong duoi

  --SET @NgayBDTinhLuong = CAST((@Nam ) AS VARCHAR) + cast(@Thang as varchar) + CAST(@NgayBDThang AS VARCHAR)

  SET @NgayBDTinhLuong = CAST((@Nam ) AS VARCHAR) + '/' + cast(@Thang as varchar) + '/' + CAST(@NgayBDThang AS VARCHAR)

 else

 begin

  IF (@Thang = 1)

   SET @NgayBDTinhLuong = CAST((@Nam -1) AS VARCHAR) + '/12/' + CAST(@NgayBDThang AS VARCHAR)

  ELSE

   SET @NgayBDTinhLuong = CAST(@Nam AS VARCHAR) + '/' + CAST((@Thang -1) AS VARCHAR) + '/' + CAST(@NgayBDThang AS VARCHAR)

 end

 RETURN @NgayBDTinhLuong

END

----------------------------------

USE [SG3HRM]

GO

/****** Object:  StoredProcedure [dbo].[splMain_TinhLuongSanPham]    Script Date: 11/02/2011 14:59:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[splMain_TinhLuongSanPham]

 @Thang tinyint,

 @Nam  int,

 @MaCoCauToChuc  nvarchar(20)=''

AS

 -- TINH LUONG CHO CONG NHAN AN THEO LUONG SAN PHAM

 UPDATE tbl_BangLuongSanPham

 SET nLuongSanPham =isnull( nLuongSanPham,0) +isnull( lg.nLuongTheoSanPham,0)

 FROM tbl_BangLuongSanPham  a,

  ( 

  SELECT a.MaNhanVien, SUM( a.SanLuong * a.DonGiaVND )  AS nLuongTheoSanPham

  FROM  tbl_NhapSanLuongThucTe   a,

   tbL_CachTinhLuongTheoPhongBan c,

   tbh_CoCauToChuc   d,

   tbh_NhanVien    f

  WHERE

   a.MaCoCauToChuc = c.cMaCoCauToChuc

  AND a.MaCoCauToChuc = d.cMaCoCauToChuc

  AND a.MaNhanVien  = f.MaNhanVien

  AND MONTH( ThangTinhLuong ) = @Thang AND YEAR( ThangTinhLuong) = @Nam

  AND c.nMaHinhThucTinhLuong IN(  5)

  And (( d.cMaDuongDan  = @MaCoCauToChuc ) or ( a.MaCoCauToChuc = @MaCoCauToChuc )  or

  (d.cMaDuongDan like '%|'+@MaCoCauToChuc+'|%')   or (d.cMaDuongDan like '%'+@MaCoCauToChuc+'%' ) or (@MaCoCauToChuc= '' ))

  GROUP BY a.MaNhanVien   ) lg

 WHERE

   a.cMaNhanVien = lg.MaNhanVien

  AND a.nThang  = @Thang AND nNam = @Nam

  AND bDaTraLuong = 0

  And nLuongVanPhong=2