sql 收藏

/*计算个人所得税函数20050930    */
/*输入参数为：第一个参数为字符一位：'1' 正常全额计税　'0'不计税   第二个参数为金额*/
/* 返回应交税额*/
/*作者：*** 调用方法:dbo.jstax('1',900) */

CREATE FUNCTION jstax (@jsfs int ,@je decimal(10,2))
RETURNS decimal(10,2) AS
BEGIN

DECLARE @jshj decimal(10,2)
if @jsfs=1   /*  工资薪金计税    */
set @jshj=
CASE
    WHEN @je<0                     THEN  0
    WHEN @je>0 and @je<500         THEN  @je*0.05
    WHEN @je>=500 and @je<2000     THEN  @je*0.10-25
    WHEN @je>=2000 and @je<5000    THEN  @je*0.15-125
    WHEN @je>=5000 and @je<20000   THEN  @je*0.20-375
    WHEN @je>=20000 and @je<40000  THEN  @je*0.25-1375
    WHEN @je>=40000 and @je<60000  THEN  @je*0.3-3375
    WHEN @je>=60000 and @je<80000  THEN  @je*0.35-6375
    WHEN @je>=80000 and @je<100000 THEN  @je*0.4-10375
    WHEN @je>=100000               THEN  @je*0.45-15375
END
if @jsfs=2   /* 不扣税  */
    set @jshj=0
if @jsfs=3   /*  年终奖计税  */
    set @jshj=
CASE
    WHEN @je<0                     THEN  0
    WHEN @je>0 and @je<500         THEN  @je*0.05
    WHEN @je>=500 and @je<2000     THEN  @je*0.10-25
    WHEN @je>=2000 and @je<5000    THEN  @je*0.15-125
    WHEN @je>=5000 and @je<20000   THEN  @je*0.20-375
    WHEN @je>=20000 and @je<40000  THEN  @je*0.25-1375
    WHEN @je>=40000 and @je<60000  THEN  @je*0.3-3375
    WHEN @je>=60000 and @je<80000  THEN  @je*0.35-6375
    WHEN @je>=80000 and @je<100000 THEN  @je*0.4-10375
    WHEN @je>=100000               THEN  @je*0.45-15375
END
RETURN @jshj
END
\\\\
/*当月已扣除税合计*/
CREATE FUNCTION jsget_kshj (@ygbh int,@jdate char(15))
RETURNS decimal(10,2) AS
BEGIN

DECLARE @returnstring  decimal(10,2)
DECLARE @CountItems int

SELECT
    @CountItems = Count(GoodsID)
FROM
    ordercontent
WHERE
    GoodsID = @ygbh
  AND
    jsdate = @jdate
and cwsh=1


if @CountItems>0
set @returnstring=(select sum(kosui)+sum(czks) from ordercontent
where   GoodsID = @ygbh AND jsdate = @jdate and jsfs=1 and cwsh=1)
 
else
set @returnstring=0


RETURN @returnstring
END

\\\\\\
/*函数20051101    */
/*输入参数为：当前日期*/ 
/* 返回200511*/
/*作者：*** 调用方法:dbo.jsgetdate(getdate()) */

CREATE FUNCTION jsgetdate (@js_date datetime)
RETURNS nvarchar(6) AS
BEGIN

DECLARE @returnstring  nvarchar(6)

set @returnstring=convert(nvarchar(4),datepart(year,@js_date))+convert(nvarchar(2),datepart(month,@js_date))

RETURN @returnstring
END

\\\\
/*根据指定的职工编号和月份
取得当月工资性收入的合计
*/

CREATE FUNCTION jsgetjehj (@ygbh int,@jdate char(15))
RETURNS decimal(10,2) AS
BEGIN

DECLARE @returnstring  decimal(10,2)
DECLARE @CountItems int

SELECT
    @CountItems = Count(GoodsID)
FROM
    ordercontent
WHERE
    GoodsID = @ygbh
  AND
    jsdate = @jdate
  and  cwsh=1


if @CountItems>0
set @returnstring=(select sum(GoodsQuantity) from ordercontent
where   GoodsID = @ygbh AND jsdate = @jdate and jsfs=1 and cwsh=1)
 
else
set @returnstring=0


RETURN @returnstring
END

\\\\\\
/*根据指定的职工编号和12月份
工资性收入的合计 作为计算年终奖的依据

*/

CREATE FUNCTION jsget12jehj (@ygbh int,@jdate char(15))
RETURNS decimal(10,2) AS
BEGIN

DECLARE @returnstring  decimal(10,2)
DECLARE @CountItems int

SELECT
    @CountItems = Count(GoodsID)
FROM
    ordercontent
WHERE
    GoodsID = @ygbh
  AND
    jsdate = @jdate


if @CountItems>0
set @returnstring=(select sum(GoodsQuantity) from ordercontent
where   GoodsID = @ygbh AND jsdate = @jdate and jsfs=1)
 
else
set @returnstring=0
RETURN @returnstring
END

\\\\\
/*
用于在财务个人项目中查询收入明细情况
输入参数为员工编号
*/
CREATE Procedure srmx
(
    @IDCardNumber  int
)
As
SELECT 
    orderid,
    GZBASE.GZBASE_YGBH,
    GZBASE.GZBASE_YGXM,
    GoodsQuantity,
    kosui,
    sifa,
    czks,
   jsfs,
  jsdate
   
FROM
    OrderContent
  INNER JOIN GZBASE ON OrderContent.GoodsID = GZBASE.GZBASE_YGBH
 
where OrderContent.GoodsID=@IDCardNumber and cwsh=1

order by  jsdate,orderid
GO

\\\\
/*
此存储过程用于将工资数据从cur_gzbase读入ordercontent表
不需要参数
*/

CREATE Procedure curgz_read

AS
DECLARE @OrderID int
DECLARE @tmp  Decimal(10,2)
DECLARE @yhhj  Decimal(10,2)

BEGIN TRAN OrderAdd1


INSERT INTO Orders
(
    UserID,
    OrderDate,
    zy
  
)
VALUES
(  
    20,
    getdate() ,
   '工资'
)



SELECT
    @OrderID = @@Identity   
--select  @yhhj=[GZBASE_XM040]+[GZBASE_XM032]-[GZBASE_XM021]-[GZBASE_XM045]  from cur_gzbase
--select   @tmp= dbo.jstax(1,dbo.jsgetjehj(GZBASE_YGBH,dbo.jsgetdate(getdate()))+@yhhj-1600)-dbo.jsget_kshj(GZBASE_YGBH,dbo.jsgetdate(getdate()))

from cur_gzbase




INSERT INTO OrderContent
(
     [OrderID],
    [GoodsID],
    [GoodsQuantity],
    [GZBASE_YGXM],
    [kosui],
    [sifa],
    [jsfs],
   [czks],
    [gzyn] 
 
   
)


SELECT
   @OrderID,
    [GZBASE_YGBH],
    [GZBASE_XM040]+[GZBASE_XM032]-[GZBASE_XM021]-[GZBASE_XM045],
    "",
case
   when substring(GZBASE_BMBH,1,2)='02'  THEN 0
   ELSE
    dbo.jstax(1,dbo.jsgetjehj(GZBASE_YGBH,dbo.jsgetdate(getdate()))+([GZBASE_XM040]+[GZBASE_XM032]-[GZBASE_XM021]-[GZBASE_XM045])-1652)-dbo.jsget_kshj(GZBASE_YGBH,dbo.jsgetdate(getdate()))-[GZBASE_XM022]
END,

   0,
    1,
     [GZBASE_XM022],
    1
  

FROM
   cur_gzbase


COMMIT TRAN OrderAdd1
GO

\\\\\\
CREATE Procedure js_orderdetal
(
    @orderid int
)
As
SELECT 
    GZBASE.GZBASE_YGBH,
    GZBASE.GZBASE_YGXM,
    GoodsQuantity,
    kosui,
    sifa
   
FROM
    OrderContent
  INNER JOIN GZBASE ON OrderContent.GoodsID = GZBASE.GZBASE_YGBH
 
where orderid=@orderid
GO
\\\\
CREATE Procedure js_orderdetal
(
    @orderid int
)
As
SELECT 
    GZBASE.GZBASE_YGBH,
    GZBASE.GZBASE_YGXM,
    GoodsQuantity,
    kosui,
    sifa
   
FROM
    OrderContent
  INNER JOIN GZBASE ON OrderContent.GoodsID = GZBASE.GZBASE_YGBH
 
where orderid=@orderid
GO


\\\\

CREATE Procedure Updateordercontent_hz
(
    @GoodsID int,
    @GoodsQuantity Decimal(10,2)
   
 
)
As

DECLARE @CountItems int

SELECT
    @CountItems = Count(GoodsID)
FROM
    ordercontent_hz
WHERE
    GoodsID = @GoodsID
  AND
    jsdate = dbo.jsgetdate(getdate())

IF @CountItems > 0  /* There are items - update the current quantity */

    UPDATE
        ordercontent_hz
    SET
        GoodsQuantity = (@GoodsQuantity + ordercontent_hz.GoodsQuantity)
    WHERE
        GoodsID = @GoodsID
      AND
        jsdate = dbo.jsgetdate(getdate())

ELSE  /* New entry for this Cart.  Add a new record */

    INSERT INTO ordercontent_hz
    (
       
        GoodsQuantity,
        GoodsID   
    )
    VALUES
    (
       
        @GoodsQuantity,
        @GoodsID
   
    )
GO