青少年编程与数学 02-002 Sql Server 数据库应用 19课题、数据库设计实例
- 课题摘要:
- 一、表
- 二、存储过程
- 三、自定义函数
本课题介绍了一个实际项目开发使用的数据库的实例,列出了其中表、存储过程和函数的SQL脚本。
课题摘要:
本课题介绍了一个实际项目开发使用的数据库的实例,列出了其中表、存储过程和函数的SQL脚本。
以下是作者在实际开发设计的一个会计软件数据库中的主要对象。
一、表
/****** Object: Table [dbo].[MLBM] Script Date: 2024-10-11 20:25:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MLBM]([BMDH] [varchar](10) NOT NULL,[BJDH] [varchar](6) NOT NULL,[SJDH] [varchar](10) NOT NULL,[BMMC] [nvarchar](30) NOT NULL,[BMQC] [nvarchar](90) NOT NULL,[SCZX] [bit] NOT NULL,[MRCN] [decimal](18, 2) NOT NULL,[CNDW] [varchar](12) NOT NULL,[PCXH] [int] NOT NULL,[FZR] [nvarchar](10) NOT NULL,[ZJM] [varchar](100) NOT NULL,[JC] [smallint] NOT NULL,[DCMX] [bit] NOT NULL,[SFQY] [bit] NOT NULL,CONSTRAINT [PK_MLBM] PRIMARY KEY CLUSTERED
([BMDH] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[MLKM] Script Date: 2024-10-11 20:25:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MLKM]([KMDH] [varchar](12) NOT NULL,[BJDH] [varchar](5) NOT NULL,[SJDH] [varchar](12) NOT NULL,[KMMC] [nvarchar](50) NOT NULL,[KMQC] [nvarchar](100) NOT NULL,[HBMC] [nvarchar](10) NOT NULL,[DWWLHS] [bit] NOT NULL,[GRWLHS] [bit] NOT NULL,[CNHS] [bit] NOT NULL,[BMHS] [bit] NOT NULL,[SLHS] [bit] NOT NULL,[WBHS] [bit] NOT NULL,[DLCX] [bit] NOT NULL,[YEFX] [varchar](10) NOT NULL,[ZJM] [varchar](100) NOT NULL,[JC] [smallint] NOT NULL,[DCMX] [bit] NOT NULL,[SFQY] [bit] NOT NULL,CONSTRAINT [PK_MLKM] PRIMARY KEY CLUSTERED
([KMDH] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[MLNY] Script Date: 2024-10-11 20:25:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MLNY]([NY] [char](6) NOT NULL,CONSTRAINT [PK_MLNY] PRIMARY KEY CLUSTERED
([NY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[MLWL] Script Date: 2024-10-11 20:25:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MLWL]([WLDH] [varchar](12) NOT NULL,[SJDH] [varchar](12) NULL,[BJDH] [varchar](6) NULL,[WLMC] [nvarchar](50) NULL,[DZ] [nvarchar](60) NULL,[LXR] [nvarchar](20) NULL,[YWY] [nvarchar](20) NULL,[YB] [varchar](6) NULL,[DH] [varchar](100) NULL,[CZ] [varchar](20) NULL,[FR] [nvarchar](10) NULL,[HY] [nvarchar](100) NULL,[DWWJ] [nvarchar](200) NULL,[TP1] [nvarchar](200) NULL,[WJ1] [nvarchar](200) NULL,[XYDJ] [int] NULL,[XYJE] [int] NULL,[XYTS] [int] NULL,[ZKL] [decimal](10, 3) NULL,[KHH] [nvarchar](30) NULL,[ZH] [varchar](30) NULL,[JSWB] [nvarchar](20) NOT NULL,[SH] [nvarchar](30) NULL,[ZJM] [varchar](100) NULL,[JC] [smallint] NULL,[DCMX] [bit] NULL,[SFQY] [bit] NULL,CONSTRAINT [PK_MLKH] PRIMARY KEY CLUSTERED
([WLDH] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MLBM] ADD CONSTRAINT [DF_MLBM_BJDH] DEFAULT ('') FOR [BJDH]
GO
ALTER TABLE [dbo].[MLBM] ADD CONSTRAINT [DF_MLBM_SJDH] DEFAULT ('') FOR [SJDH]
GO
ALTER TABLE [dbo].[MLBM] ADD CONSTRAINT [DF_MLBM_BMMC] DEFAULT ('') FOR [BMMC]
GO
ALTER TABLE [dbo].[MLBM] ADD CONSTRAINT [DF_MLBM_BMQC] DEFAULT ('') FOR [BMQC]
GO
ALTER TABLE [dbo].[MLBM] ADD CONSTRAINT [DF_MLBM_SCZX] DEFAULT ((0)) FOR [SCZX]
GO
ALTER TABLE [dbo].[MLBM] ADD CONSTRAINT [DF_MLBM_MRCN] DEFAULT ((0)) FOR [MRCN]
GO
ALTER TABLE [dbo].[MLBM] ADD CONSTRAINT [DF_MLBM_CNDW] DEFAULT ('') FOR [CNDW]
GO
ALTER TABLE [dbo].[MLBM] ADD CONSTRAINT [DF_MLBM_PCXH] DEFAULT ((0)) FOR [PCXH]
GO
ALTER TABLE [dbo].[MLBM] ADD CONSTRAINT [DF_MLBM_FZR] DEFAULT ('') FOR [FZR]
GO
ALTER TABLE [dbo].[MLBM] ADD CONSTRAINT [DF_MLBM_ZJM] DEFAULT ('') FOR [ZJM]
GO
ALTER TABLE [dbo].[MLBM] ADD CONSTRAINT [DF_MLBM_JC] DEFAULT ((0)) FOR [JC]
GO
ALTER TABLE [dbo].[MLBM] ADD CONSTRAINT [DF_MLBM_DCMX] DEFAULT ((0)) FOR [DCMX]
GO
ALTER TABLE [dbo].[MLBM] ADD CONSTRAINT [DF_MLBM_SFQY] DEFAULT ((0)) FOR [SFQY]
GO
ALTER TABLE [dbo].[MLKM] ADD CONSTRAINT [DF_MLKM_BJDH] DEFAULT ('') FOR [BJDH]
GO
ALTER TABLE [dbo].[MLKM] ADD CONSTRAINT [DF_MLKM_SJDH] DEFAULT ('') FOR [SJDH]
GO
ALTER TABLE [dbo].[MLKM] ADD CONSTRAINT [DF_MLKM_KMMC] DEFAULT ('') FOR [KMMC]
GO
ALTER TABLE [dbo].[MLKM] ADD CONSTRAINT [DF_MLKM_KMQC] DEFAULT ('') FOR [KMQC]
GO
ALTER TABLE [dbo].[MLKM] ADD CONSTRAINT [DF_MLKM_HBMC] DEFAULT ('') FOR [HBMC]
GO
ALTER TABLE [dbo].[MLKM] ADD CONSTRAINT [DF_MLKM_WLHS1] DEFAULT ((0)) FOR [DWWLHS]
GO
ALTER TABLE [dbo].[MLKM] ADD CONSTRAINT [DF_MLKM_WBHS1] DEFAULT ((0)) FOR [GRWLHS]
GO
ALTER TABLE [dbo].[MLKM] ADD CONSTRAINT [DF_MLKM_CNHS] DEFAULT ((0)) FOR [CNHS]
GO
ALTER TABLE [dbo].[MLKM] ADD CONSTRAINT [DF_MLKM_BMHS] DEFAULT ((0)) FOR [BMHS]
GO
ALTER TABLE [dbo].[MLKM] ADD CONSTRAINT [DF_MLKM_SLHS] DEFAULT ((0)) FOR [SLHS]
GO
ALTER TABLE [dbo].[MLKM] ADD CONSTRAINT [DF_MLKM_WBHS] DEFAULT ((0)) FOR [WBHS]
GO
ALTER TABLE [dbo].[MLKM] ADD CONSTRAINT [DF_MLKM_WBHS1_1] DEFAULT ((0)) FOR [DLCX]
GO
ALTER TABLE [dbo].[MLKM] ADD CONSTRAINT [DF_MLKM_YEFX] DEFAULT ('') FOR [YEFX]
GO
ALTER TABLE [dbo].[MLKM] ADD CONSTRAINT [DF_MLKM_ZJM] DEFAULT ('') FOR [ZJM]
GO
ALTER TABLE [dbo].[MLKM] ADD CONSTRAINT [DF_MLKM_JC] DEFAULT ((0)) FOR [JC]
GO
ALTER TABLE [dbo].[MLKM] ADD CONSTRAINT [DF_MLKM_DCMX] DEFAULT ((0)) FOR [DCMX]
GO
ALTER TABLE [dbo].[MLKM] ADD CONSTRAINT [DF_MLKM_SFQY] DEFAULT ((0)) FOR [SFQY]
GO
ALTER TABLE [dbo].[MLWL] ADD CONSTRAINT [DF_MLWL_TP11] DEFAULT ('') FOR [DWWJ]
GO
ALTER TABLE [dbo].[MLWL] ADD CONSTRAINT [DF_MLWL_DWWJ] DEFAULT ('') FOR [TP1]
GO
ALTER TABLE [dbo].[MLWL] ADD CONSTRAINT [DF_MLWL_TP11_1] DEFAULT ('') FOR [WJ1]
GO
ALTER TABLE [dbo].[MLWL] ADD CONSTRAINT [DF_MLWL_JSWB] DEFAULT ('') FOR [JSWB]
GO
ALTER TABLE [dbo].[MLWL] ADD CONSTRAINT [DF_MLWL_DCMX] DEFAULT ((0)) FOR [DCMX]
GO
ALTER TABLE [dbo].[MLWL] ADD CONSTRAINT [DF_MLWL_SFQY] DEFAULT ((0)) FOR [SFQY]
GO
/****** Object: StoredProcedure [dbo].[X9_DCMX] Script Date: 2024-10-11 20:25:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
二、存储过程
GO
-- =============================================
-- Author: 昆山华岳软件有限公司 岳国军
-- Create date: 2012-06-04
-- Script Date: 2020-05-30
-- Script Date: 2024-03-16
-- Description: 计算DCMX
-- 本过程在基础资料提交时执行,亦可做基础资料修改后的相关操作
-- =============================================
CREATE PROCEDURE [dbo].[X9_DCMX]@MLMC VARCHAR(12)
ASSET NOCOUNT ON;
BEGIN TRYDECLARE @SJDH VARCHAR(30),@SYSJ VARCHAR(30),@DQDH VARCHAR(30),@DCMX BIT;--部门目录IF @MLMC='MLBM'BEGINDECLARE MLBM_cursor CURSOR FOR SELECT BMDH,SJDH,DCMX FROM MLBM ORDER BY MLBM.BMDH DESC;OPEN MLBM_cursor;SET @SYSJ='Z'FETCH FROM MLBM_cursor INTO @DQDH,@SJDH,@DCMX;WHILE @@FETCH_STATUS=0BEGINIF @SYSJ =@DQDHBEGINIF @DCMX<>0UPDATE MLBM SET DCMX=0 WHERE BMDH=@DQDH;ENDELSEBEGIN IF @DCMX<>1UPDATE MLBM SET DCMX=1 WHERE BMDH=@DQDH;ENDSET @SYSJ=@SJDH;FETCH NEXT FROM MLBM_cursor INTO @DQDH,@SJDH,@DCMX;ENDCLOSE MLBM_cursor;DEALLOCATE MLBM_cursor ;RETURN;END--客户目录IF @MLMC='MLWL'BEGINDECLARE MLWL_cursor CURSOR FOR SELECT WLDH,SJDH,DCMX FROM MLWL ORDER BY WLDH DESC;OPEN MLWL_cursor;SET @SYSJ='Z'FETCH FROM MLWL_cursor INTO @DQDH,@SJDH,@DCMXWHILE @@FETCH_STATUS=0BEGINIF @SYSJ = @DQDHBEGINIF @DCMX<>0UPDATE MLWL SET DCMX=0 WHERE WLDH=@DQDH;ENDELSEBEGIN IF @DCMX<>1UPDATE MLWL SET DCMX=1 WHERE WLDH=@DQDH;ENDSET @SYSJ=@SJDH;FETCH NEXT FROM MLWL_cursor INTO @DQDH,@SJDH,@DCMXENDCLOSE MLWL_cursor;DEALLOCATE MLWL_cursor ;RETURN;END--会计科目IF @MLMC='MLKM'BEGIN--声明游标MLKM_cursorDECLARE MLKM_cursor CURSOR FOR SELECT KMDH,SJDH,DCMX FROM MLKM ORDER BY KMDH DESC;OPEN MLKM_cursor;SET @SYSJ='Z';FETCH FROM MLKM_cursor INTO @DQDH,@SJDH,@DCMXWHILE @@FETCH_STATUS=0BEGINIF @SYSJ = @DQDHBEGINIF @DCMX<>0UPDATE MLKM SET DCMX=0 WHERE KMDH=@DQDH;ENDELSEBEGIN IF @DCMX<>1UPDATE MLKM SET DCMX=1 WHERE KMDH=@DQDH;ENDSET @SYSJ=@SJDH;FETCH NEXT FROM MLKM_cursor INTO @DQDH,@SJDH,@DCMXENDCLOSE MLKM_cursor;DEALLOCATE MLKM_cursor;UPDATE MLKM SET DLCX=0 WHERE DCMX=1 AND DLCX=1;RETURN;END--资产分类IF @MLMC='MLZCKP'BEGIN--声明游标MLZCFL_cursorDECLARE MLZCFL_cursor CURSOR FOR SELECT ZCBH,SJDH,DCMX FROM MLZCKP ORDER BY ZCBH DESC;OPEN MLZCFL_cursor;SET @SYSJ='Z';FETCH FROM MLZCFL_cursor INTO @DQDH,@SJDH,@DCMXWHILE @@FETCH_STATUS=0BEGINIF @SYSJ = @DQDHBEGINIF @DCMX<>0UPDATE MLZCKP SET DCMX=0 WHERE ZCBH=@DQDH;ENDELSEBEGIN IF @DCMX<>1UPDATE MLZCKP SET DCMX=1 WHERE ZCBH=@DQDH;ENDSET @SYSJ=@SJDH;FETCH NEXT FROM MLZCFL_cursor INTO @DQDH,@SJDH,@DCMXENDCLOSE MLZCFL_cursor;DEALLOCATE MLZCFL_cursor ;RETURN;END
END TRY
BEGIN CATCHIF CURSOR_STATUS('LOCAL','MLCH_cursor')>=0 BEGINCLOSE MLCH_cursor;DEALLOCATE MLCH_cursor ;END DECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity int,@ErrorState INT;SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState );
END CATCH;GO
/****** Object: StoredProcedure [dbo].[X9_DJDJ] Script Date: 2024-10-11 20:25:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 昆山华岳软件有限公司 岳国军
-- Create date: 2012-06-10
-- Script Date: 2019-03-31
-- Script Date: 2020-08-04
-- Script Date: 2022-05-22
-- Script Date: 2022-06-07
-- Script Date: 2024-03-16
-- Description: 单据登记
-- 完成返回登记人,否则返回错误信息
-- =============================================
CREATE PROCEDURE [dbo].[X9_DJDJ](@DQYH NVARCHAR(10), --用户名称@BJMC NVARCHAR(500), --本机名称@DJMC NVARCHAR(30), --单据名称@DJOID VARCHAR(30)) --单据号
AS
BEGIN TRYDECLARE @DJJG NVARCHAR(MAX); --登记结果DECLARE @DYJG INT; --调用结果DECLARE @SFDJ BIT;IF @DJMC='收款单' OR @DJMC='付款单' BEGINSELECT @SFDJ=CASE WHEN DJR<>'' OR SHR='' THEN 1 ELSE 0 END FROM DJCNO WHERE OID=@DJOID;IF @SFDJ=1BEGINSELECT @DJMC + @DJOID +'已经登记或未经审核'; ENDELSEBEGINSET @DYJG=0;EXEC @DYJG=DBO.X9_DJDJ_06CN @DQYH,@DJMC,@DJOID; IF @DYJG=1BEGINSELECT @DQYH;END ELSESELECT @DJMC + @DJOID +'未能完成登记'; ENDRETURN;ENDIF @DJMC='记账凭证'BEGINSELECT @SFDJ=CASE WHEN DJR<>'' THEN 1 ELSE 0 END FROM DJJZPZO WHERE OID=@DJOID;IF @SFDJ=1BEGIN--反记账SET @DYJG=0;EXEC @DYJG=DBO.X9_DJDJ_05JZFX @DQYH,@DJMC,@DJOID; IF @DYJG=1BEGINSELECT @DQYH;END ELSESELECT @DJMC + @DJOID +'未能完成反记账'; ENDELSEBEGINSET @DYJG=0;EXEC @DYJG=DBO.X9_DJDJ_05JZ @DQYH,@DJMC,@DJOID; IF @DYJG=1BEGINSELECT @DQYH;END ELSESELECT @DJMC + @DJOID +'未能完成记账操作'; ENDRETURN;END
END TRY
BEGIN CATCHDECLARE @EMsg NVARCHAR(4000);SELECT @EMsg = ERROR_MESSAGE();SELECT @DJMC + @DJOID +'操作时出错,' + CHAR(13) + CHAR(10) + @EMsg ;
END CATCHGO
/****** Object: StoredProcedure [dbo].[X9_DJDJ_05JZ] Script Date: 2024-10-11 20:25:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 昆山华岳软件有限公司
-- Create date: 2012-07-12
-- Script Date: 2020-08-04
-- Script Date: 2022-05-10
-- Script Date: 2022-06-27
-- Script Date: 2022-06-28
-- Script Date: 2024-03-16
-- Description: 单据登记_记账凭证
-- =============================================
CREATE PROCEDURE [dbo].[X9_DJDJ_05JZ](@DQYH NVARCHAR(10), --用户名称@DJMC NVARCHAR(30), --单据名称@DJOID VARCHAR(30)) --单据号
AS
DECLARE @ErrorLogID INT;
BEGIN TRYSET NOCOUNT ON;BEGIN--允许登记到本月及上月凭证BEGIN TRANSACTION;--登记单据DECLARE @RQ DATE;SELECT @RQ=RQ FROM DJJZPZO WHERE OID=@DJOID ;UPDATE DJJZPZM SET RQ=@RQ WHERE OID=@DJOID;DECLARE @NY CHAR(6),@SY CHAR(6),@ZZXH INT,@MID INT;SET @SY=DBO.X9_DQSY();SELECT @NY=NY FROM DJJZPZO WHERE OID=@DJOID;--声明变量DECLARE @KMDH VARCHAR(12),@WLDW NVARCHAR(100),@WLRY NVARCHAR(30),@HSBM NVARCHAR(100),@HBMC NVARCHAR(10),@JFJE DECIMAL(16,2),@DFJE DECIMAL(16,2),@SLWB DECIMAL(16,4);SET @ZZXH=0;--声明游标JZPZ_cursorDECLARE JZPZ_cursor CURSOR LOCAL FORSELECT KMDH,WLDW,WLRY,HSBM,HBMC,JFJE, DFJE, SLWB,MIDFROM DJJZPZMWHERE (OID = @DJOID) AND SFSH=1 ORDER BY KMDH,WLDW,WLRY,HSBM,HBMC;OPEN JZPZ_cursor;FETCH FROM JZPZ_cursorINTO @KMDH,@WLDW,@WLRY,@HSBM,@HBMC,@JFJE,@DFJE,@SLWB,@MID;WHILE @@FETCH_STATUS=0BEGIN--登记会计总账IF EXISTS(SELECT * FROM UTZZKJ WHERE NY=@NY AND KMDH=@KMDH AND WLDW=@WLDW AND WLRY=@WLRY AND HSBM=@HSBM AND HBMC=@HBMC)BEGINIF @SLWB<>0 BEGIN IF @JFJE<>0 UPDATE UTZZKJ SET SLJF=SLJF+@SLWB,SLYM=SLYM+@SLWB WHERE NY=@NY AND KMDH=@KMDH AND WLDW=@WLDW AND WLRY=@WLRY AND HSBM=@HSBM AND HBMC=@HBMC;ELSEUPDATE UTZZKJ SET SLDF=SLDF+@SLWB,SLYM=SLYM-@SLWB WHERE NY=@NY AND KMDH=@KMDH AND WLDW=@WLDW AND WLRY=@WLRY AND HSBM=@HSBM AND HBMC=@HBMC;ENDUPDATE UTZZKJ SET JEJF=JEJF+@JFJE,JEDF=JEDF+@DFJE,JEYM=JEYM+@JFJE-@DFJE ,ZZXH=ZZXH+1 WHERE NY=@NY AND KMDH=@KMDH AND WLDW=@WLDW AND WLRY=@WLRY AND HSBM=@HSBM AND HBMC=@HBMC;SELECT @ZZXH=ZZXH FROM UTZZKJWHERE NY=@NY AND KMDH=@KMDH AND WLDW=@WLDW AND WLRY=@WLRY AND HSBM=@HSBM AND HBMC=@HBMC;UPDATE DJJZPZM SET ZZXH=@ZZXH WHERE MID=@MID;ENDELSEBEGINIF @JFJE<>0 INSERT INTO UTZZKJ(NY,KMDH,WLDW,WLRY,HSBM,HBMC,SLYC,SLJF,SLDF,SLYM,JEYC,JEJF,JEDF,JEYM) VALUES(@NY,@KMDH,@WLDW,@WLRY,@HSBM,@HBMC,0 ,@SLWB,0,@SLWB,0,@JFJE,0,@JFJE)ELSEINSERT INTO UTZZKJ(NY,KMDH,WLDW,WLRY,HSBM,HBMC,SLYC,SLJF,SLDF,SLYM,JEYC,JEJF,JEDF,JEYM) VALUES(@NY,@KMDH,@WLDW,@WLRY,@HSBM,@HBMC,0 ,0,@SLWB,0-@SLWB,0,0,@DFJE,0-@DFJE)ENDFETCH NEXT FROM JZPZ_cursorINTO @KMDH,@WLDW,@WLRY,@HSBM,@HBMC,@JFJE,@DFJE,@SLWB,@MID;ENDCLOSE JZPZ_cursor;DEALLOCATE JZPZ_cursor ;UPDATE DJJZPZO SET DJR=@DQYH WHERE OID=@DJOID AND SHR<>'';UPDATE DJJZPZM SET SFDJ=1 WHERE OID=@DJOID AND SFSH=1;IF @NY=@SYBEGIN--更新当月会计总账期初数及期末数,即可以登记到上月WITH SYZZ (KMDH, HSBM ,WLDW,WLRY,HBMC, SLYC, SLJF, SLDF, SLYM, JEYC, JEJF, JEDF, JEYM) AS (SELECT KMDH, HSBM ,WLDW,WLRY,HBMC, SLYC, SLJF, SLDF, SLYM, JEYC, JEJF, JEDF, JEYM FROM UTZZKJ WHERE (NY = @SY))UPDATE UTZZKJ SET SLYC=SYZZ.SLYM,JEYC=SYZZ.JEYMFROM UTZZKJ AS DYZZ INNER JOIN SYZZ ON (DYZZ.KMDH =SYZZ.KMDH AND DYZZ.HSBM=SYZZ.HSBM AND DYZZ.WLDW=SYZZ.WLDW AND DYZZ.WLRY=SYZZ.WLRY AND DYZZ.HBMC=SYZZ.HBMC)WHERE DYZZ.NY=@NY;UPDATE UTZZKJ SET SLYM=SLYC+SLJF-SLDF,JEYM=JEYC+JEJF-JEDFWHERE NY=@NY; --生成空账 INSERT INTO UTZZKJ (NY, KMDH, HSBM ,WLDW,WLRY,HBMC, SLYC, SLJF, SLDF, SLYM, JEYC, JEJF, JEDF, JEYM, ZZXH)SELECT @NY AS NY, KMDH, HSBM, WLDW,WLRY, HBMC, SLYM AS SLYC, 0 AS SLJF, 0 AS SLDF, SLYM, JEYM AS JEYC, 0 AS JEJF, 0 AS JEDF, JEYM, ZZXHFROM UTZZKJ AS SYZZWHERE (NY = @SY) AND (KMDH+HSBM+WLDW+WLRY+HBMC) NOT IN(SELECT KMDH+HSBM+WLDW+WLRY+HBMC FROM UTZZKJ AS DYZZ WHERE NY=@NY);ENDCOMMIT TRANSACTION;RETURN 1;END
END TRY
BEGIN CATCHIF XACT_STATE() <> 0BEGINIF CURSOR_STATUS('LOCAL','JZPZ_cursor')>=0 BEGINCLOSE JZPZ_cursor;DEALLOCATE JZPZ_cursor ;END ROLLBACK TRANSACTION;ENDINSERT [dbo].[X9_ERRLOG] ([ErrorTime],[UserName], [ErrorNumber],[ErrorSeverity],[ErrorState], [ErrorProcedure], [ErrorLine], [ErrorMessage]) VALUES (GETDATE(),CURRENT_USER, ERROR_NUMBER() ,ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE());RETURN 0;
END CATCH;
GO
/****** Object: StoredProcedure [dbo].[X9_DJDJ_05JZFX] Script Date: 2024-10-11 20:25:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 昆山华岳软件有限公司 岳国军
-- Create date: 2014-07-28
-- Script Date: 2020-06-17
-- Script Date: 2022-05-10
-- Script Date: 2022-06-27
-- Script Date: 2022-06-28
-- Script Date: 2024-03-16
-- Description: 单据登记_记账凭证 反记账
-- =============================================
CREATE PROCEDURE [dbo].[X9_DJDJ_05JZFX](@DQYH NVARCHAR(10), --用户名称@DJMC NVARCHAR(30), --单据名称@DJOID VARCHAR(30)) --单据号
AS
DECLARE @ErrorLogID INT;
BEGIN TRYSET NOCOUNT ON;BEGINBEGIN TRANSACTION;--登记单据DECLARE @NY CHAR(6),@ZZXH INT,@MID INT;SELECT @NY=NY FROM DJJZPZO WHERE OID=@DJOID;--声明变量DECLARE @KMDH VARCHAR(12),@WLDW NVARCHAR(100),@WLRY NVARCHAR(30),@HSBM NVARCHAR(100),@HBMC NVARCHAR(10), @JFJE DECIMAL(16,2),@DFJE DECIMAL(16,2),@SLWB DECIMAL(16,4);SET @ZZXH=0;--声明游标JZPZFX_cursorDECLARE JZPZFX_cursor CURSOR LOCAL FORSELECT KMDH,WLDW,WLRY,HSBM,HBMC,JFJE, DFJE, SLWB,MIDFROM DJJZPZMWHERE (OID = @DJOID)ORDER BY KMDH,WLDW,WLRY,HSBM,HBMC;OPEN JZPZFX_cursor;FETCH FROM JZPZFX_cursorINTO @KMDH,@WLDW,@WLRY,@HSBM,@HBMC,@JFJE,@DFJE,@SLWB,@MID;WHILE @@FETCH_STATUS=0BEGIN--登记会计总账 反向IF EXISTS(SELECT * FROM UTZZKJ WHERE NY=@NY AND KMDH=@KMDH AND WLDW=@WLDW AND WLRY=@WLRY AND HSBM=@HSBM AND HBMC=@HBMC)BEGINIF @SLWB<>0 BEGIN IF @JFJE<>0 UPDATE UTZZKJ SET SLJF=SLJF-@SLWB,SLYM=SLYM-@SLWB WHERE NY=@NY AND KMDH=@KMDH AND WLDW=@WLDW AND WLRY=@WLRY AND HSBM=@HSBM AND HBMC=@HBMC;ELSEUPDATE UTZZKJ SET SLDF=SLDF-@SLWB,SLYM=SLYM+@SLWB WHERE NY=@NY AND KMDH=@KMDH AND WLDW=@WLDW AND WLRY=@WLRY AND HSBM=@HSBM AND HBMC=@HBMC;ENDUPDATE UTZZKJ SET JEJF=JEJF-@JFJE,JEDF=JEDF-@DFJE,JEYM=JEYM-@JFJE+@DFJE ,ZZXH=ZZXH+1 WHERE NY=@NY AND KMDH=@KMDH AND WLDW=@WLDW AND WLRY=@WLRY AND HSBM=@HSBM AND HBMC=@HBMC;SELECT @ZZXH=ZZXH FROM UTZZKJWHERE NY=@NY AND KMDH=@KMDH AND WLDW=@WLDW AND WLRY=@WLRY AND HSBM=@HSBM AND HBMC=@HBMC;UPDATE DJJZPZM SET ZZXH=@ZZXH WHERE MID=@MID;ENDFETCH NEXT FROM JZPZFX_cursorINTO @KMDH,@WLDW,@WLRY,@HSBM,@HBMC,@JFJE,@DFJE,@SLWB,@MID;ENDCLOSE JZPZFX_cursor;DEALLOCATE JZPZFX_cursor ;UPDATE DJJZPZO SET DJR='' WHERE OID=@DJOID;UPDATE DJJZPZM SET SFDJ=0 WHERE OID=@DJOID;--更新以后年度总账 IF @NY<DBO.X9_DQNY()BEGINDECLARE @SYNY CHAR(6);DECLARE @DQNY CHAR(6);SET @SYNY=@NY;DECLARE KJNY_cursor CURSOR LOCAL FORSELECT NYFROM MLNYWHERE NY>@NY;OPEN KJNY_cursor;FETCH FROM KJNY_cursor INTO @DQNY;WHILE @@FETCH_STATUS=0BEGIN--更新UTZZKJWITH SYZZ (KMDH, HSBM ,WLDW,WLRY,HBMC, SLYC, SLJF, SLDF, SLYM, JEYC, JEJF, JEDF, JEYM) AS (SELECT KMDH, HSBM ,WLDW,WLRY,HBMC, SLYC, SLJF, SLDF, SLYM, JEYC, JEJF, JEDF, JEYM FROM UTZZKJ WHERE (NY = @SYNY))UPDATE UTZZKJ SET SLYC=SYZZ.SLYM,JEYC=SYZZ.JEYMFROM UTZZKJ AS DYZZ INNER JOIN SYZZ ON (DYZZ.KMDH =SYZZ.KMDH AND DYZZ.HSBM=SYZZ.HSBM AND DYZZ.WLDW=SYZZ.WLDW AND DYZZ.WLRY=SYZZ.WLRY AND DYZZ.HBMC=SYZZ.HBMC)WHERE DYZZ.NY=@DQNY;UPDATE UTZZKJ SET SLYM=SLYC+SLJF-SLDF,JEYM=JEYC+JEJF-JEDFWHERE NY=@DQNY; --生成空账 INSERT INTO UTZZKJ (NY, KMDH, HSBM ,WLDW,WLRY,HBMC, SLYC, SLJF, SLDF, SLYM, JEYC, JEJF, JEDF, JEYM, ZZXH)SELECT @DQNY AS NY, KMDH, HSBM, WLDW,WLRY, HBMC, SLYM AS SLYC, 0 AS SLJF, 0 AS SLDF, SLYM, JEYM AS JEYC, 0 AS JEJF, 0 AS JEDF, JEYM, ZZXHFROM UTZZKJ AS SYZZWHERE (NY = @SYNY) AND (KMDH+HSBM+WLDW+WLRY+HBMC) NOT IN (SELECT KMDH+HSBM+WLDW+WLRY+HBMC FROM UTZZKJ AS DYZZ WHERE NY=@DQNY);SET @SYNY=@DQNY;FETCH NEXT FROM KJNY_cursor INTO @DQNY;ENDCLOSE KJNY_cursor;DEALLOCATE KJNY_cursor ;END COMMIT TRANSACTION;RETURN 1;END
END TRY
BEGIN CATCHIF XACT_STATE() <> 0BEGINIF CURSOR_STATUS('LOCAL','JZPZFX_cursor')>=0 BEGINCLOSE JZPZFX_cursor;DEALLOCATE JZPZFX_cursor ;END ROLLBACK TRANSACTION;ENDINSERT [dbo].[X9_ERRLOG] ([ErrorTime],[UserName], [ErrorNumber],[ErrorSeverity],[ErrorState], [ErrorProcedure], [ErrorLine], [ErrorMessage]) VALUES (GETDATE(),CURRENT_USER, ERROR_NUMBER() ,ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE());RETURN 0;
END CATCH; GO
/****** Object: StoredProcedure [dbo].[X9_DJDRM] Script Date: 2024-10-11 20:25:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 昆山华岳软件有限公司 岳国军
-- Create date: 2012-02-02
-- Script Date: 2020-05-25
-- Script Date: 2020-08-07
-- Script Date: 2020-08-12
-- Script Date: 2022-05-20
-- Script Date: 2022-06-11
-- Script Date: 2022-06-26
-- Script Date: 2022-06-29
-- Script Date: 2022-06-30
-- Script Date: 2022-07-02
-- Script Date: 2024-03-16
-- Description: 单据输入调入数据 调入单据体数据
-- =============================================
CREATE PROCEDURE [dbo].[X9_DJDRM](@DQYH NVARCHAR(10), --用户名称@GNMC NVARCHAR(30), --功能名称@DRMC NVARCHAR(30), --调入名称@TJSTR NVARCHAR(MAX)) --条件字符串 由【|名称|值】组成
AS--SELECT * FROM X9_GN_0DJSR_DR ORDER BY GNBH,XH--暂无需要。RETURN;
GO
/****** Object: StoredProcedure [dbo].[X9_DJDRO] Script Date: 2024-10-11 20:25:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 昆山华岳软件有限公司 岳国军
-- Createdate: 2012-02-02
-- Script Date: 2020-05-25
-- Script Date: 2020-08-07
-- Script Date: 2022-05-20
-- Script Date: 2022-06-11
-- Script Date: 2022-06-26
-- Script Date: 2022-06-29
-- Script Date: 2022-06-30
-- Script Date: 2022-07-02
-- Script Date: 2024-03-16
-- Description: 单据输入调入数据 调入到单据头
-- =============================================
CREATE PROCEDURE [dbo].[X9_DJDRO](@DQYH NVARCHAR(10), --用户名称@GNMC NVARCHAR(30), --功能名称@DRMC NVARCHAR(30), --调入名称@TJSTR NVARCHAR(MAX)) --条件字符串 由【|名称|值】组成
AS
BEGIN RETURN;
ENDGO
/****** Object: StoredProcedure [dbo].[X9_DJHSX] Script Date: 2024-10-11 20:25:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===================================================================
-- Author: 昆山华岳软件有限公司 岳国军
-- Create date: 2012-12-20
-- Script Date: 2019-02-18
-- Script Date: 2020-08-13
-- Script Date: 2022-04-15
-- Script Date: 2022-05-07
-- Script Date: 2024-03-16
-- Description: 单据行属性
-- 用于计算公式或限制公式中引用 字段前加S. 如[S.ZSDW]
-- ===================================================================
CREATE PROCEDURE [dbo].[X9_DJHSX](@DQYH NVARCHAR(10), --用户名称@DJMC NVARCHAR(30), --单据名称@SXZD NVARCHAR(30), --属性字段@SXSTR NVARCHAR(100)) --属性字段值
AS
DECLARE @ErrorLogID INT,@SHRH NVARCHAR(50);
BEGIN TRYIF @SXZD ='KMDH' BEGINSELECT * FROM MLKM WHERE KMDH=@SXSTR ;RETURN;END
END TRY
BEGIN CATCHDECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity int,@ErrorState INT;SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState );
END CATCH;GO
/****** Object: StoredProcedure [dbo].[X9_DJSP] Script Date: 2024-10-11 20:25:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==================================================================
-- Author: 昆山华岳软件有限公司 岳国军
--Create date: 2012-12-01
-- Script Date: 2020-05-24
-- Script Date: 2020-06-28
-- Script Date: 2022-04-22
-- Script Date: 2022-06-07
-- Script Date: 2022-06-29
-- Script Date: 2022-07-02
-- Script Date: 2024-03-16
-- Description: 单据审批
-- 1、验证:对当前步骤操作前单据状态进行检验。
-- 2、运算:需要进行的计算类操作。
-- 3、正确完成后返回当前用户,否则返回提示信息。
-- 4、存货类单据验证CHXH,验证后检查是否有-1的行,有的话为非法。
-- 其中一部分单据允许新增,如销售订单、采购订单,有的单据禁止新增,如出库单等
-- ==================================================================
CREATE PROCEDURE [dbo].[X9_DJSP](@DQYH NVARCHAR(10), --用户名称@BJMC NVARCHAR(500), --本机名称@DJMC NVARCHAR(30), --单据名称@DJOID VARCHAR(30), --单据号@CZMC VARCHAR(30), --操作名称@SPFX BIT ) --审批方向 1审批 0取消
AS
DECLARE @XHFF BIT,@NRWID INT;
DECLARE @CZRY NVARCHAR(100),@NY CHAR(6),@RWGN NVARCHAR(30); --用于生成任务记录
DECLARE @HTBH NVARCHAR(30),@CKMC NVARCHAR(50);
IF @SPFX=0 --反向操作 取消BEGIN--反向操作不需要处理。SELECT @DQYH;RETURN;END
IF @CZMC='审核' BEGIN--审批前可以进行自定义验证IF @DJMC='记账凭证'BEGINIF EXISTS(SELECT OID FROM DJJZPZM PZ INNER JOIN MLKM KM ON KM.KMDH=PZ.KMDH WHERE KM.DCMX=0 AND PZ.OID=@DJOID)BEGINSELECT '使用了非明细科目,请检查单据的正确性';RETURN;ENDIF EXISTS(SELECT OID FROM DJJZPZM PZ INNER JOIN MLKM KM ON KM.KMDH=PZ.KMDH WHERE (NOT (IIF(PZ.WLRY='',0,1)=KM.GRWLHS AND IIF(PZ.HSBM='',0,1)=KM.BMHS)) AND PZ.OID=@DJOID)BEGINSELECT '辅助核算中的往来核算或部门核算项目填写有误,请检查单据的正确性';RETURN;ENDIF EXISTS(SELECT OID FROM DJJZPZM PZ INNER JOIN MLKM KM ON KM.KMDH=PZ.KMDH WHERE (NOT (IIF(PZ.SLWB=0,0,1)=IIF(KM.WBHS=1,1,0) AND IIF(PZ.HBMC='',0,1)=IIF(KM.WBHS=1,1,0))) AND CHARINDEX('汇兑损益结转',ZY)=0 AND PZ.OID=@DJOID)BEGINSELECT '辅助核算中的数量或外币核算项目填写有误,请检查单据的正确性';RETURN;ENDUPDATE KM SET SFQY=1 FROM MLKM KM INNER JOIN DJJZPZM PZ ON PZ.KMDH LIKE KM.KMDH+'%' WHERE KM.SFQY=0 AND PZ.OID=@DJOID; --启用目录SELECT @DQYH ;RETURN;ENDSELECT @DJMC+'没有对应的审核处理程序';RETURN;END --审核
GO
/****** Object: StoredProcedure [dbo].[X9_EditList] Script Date: 2024-10-11 20:25:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ========================================================================
-- Author: 昆山华岳软件有限公司
-- Create date: 2012-05-15
-- Create date: 2022-04-15
-- Script Date: 2024-03-16
-- Description: 华岳X9企业管理平台存储过程
-- Description: 输入参考项目 用于组合框 ListEdit\CheckedListEdit
-- 从分类目录等基础资料中取数
-- ========================================================================
CREATE PROCEDURE [dbo].[X9_EditList]-- Add the parameters for the stored procedure here(@DQYH NVARCHAR(20), --当前用户@GNMC NVARCHAR(30), --功能名称@FMEMO NVARCHAR(30), --字段标题@FZBH NVARCHAR(100)) --分组名称
AS
DECLARE @ErrorLogID INT;
BEGIN TRYSET NOCOUNT ON;DECLARE @MLID INT;DECLARE @SFFL BIT;SET @MLID =0;IF @FMEMO='授权范围'BEGINSELECT '角色' as 授权范围 UNION ALLSELECT '上级' as 授权范围 UNION ALLSELECT '下级' as 授权范围 UNION ALLSELECT '同级' as 授权范围 UNION ALLSELECT '发起人' as 授权范围 RETURN;ENDIF @FMEMO='授权角色'BEGINSELECT YHJS FROM X9_GNYHJS;RETURN;ENDIF @FMEMO='账项类别' BEGINSELECT '会计分类总账' as 账项类别 UNION ALLSELECT '会计数量总账' as 账项类别 UNION ALLSELECT '会计外币总账' as 账项类别 UNION ALLSELECT '会计个人往来总账' as 账项类别 UNION ALLSELECT '会计部门总账' as 账项类别 RETURN;END IF @FMEMO='明细类别' BEGINSELECT '会计分类明细账' as 明细类别 UNION ALLSELECT '会计数量明细账' as 明细类别 UNION ALLSELECT '会计外币明细账' as 明细类别 UNION ALLSELECT '会计个人往来明细账' as 明细类别 UNION ALLSELECT '会计部门明细账' as 明细类别 RETURN;END IF @FMEMO='业务期间' or @FMEMO='会计期间'BEGINSELECT TOP 12 NY from MLNY ORDER BY NY DESC;RETURN;ENDIF @FMEMO='会计处理期间'BEGINSELECT TOP 2 NY from MLNY ORDER BY NY DESC;RETURN;ENDIF @FMEMO='开始年月'BEGINSELECT TOP 12 NY from MLNY ORDER BY NYRETURN;ENDIF @FMEMO='结束年月'BEGINSELECT TOP 12 NY from MLNY ORDER BY NY DESC;RETURN;ENDiF @FMEMO='年月'BEGINSELECT NY from MLNY ORDER BY NYRETURN;ENDIF @FMEMO='所属部门'BEGINSELECT BMMC from MLBM ORDER BY BMDH;RETURN;ENDIF @FMEMO='用户角色'BEGINSELECT YHJS from X9_GNYHJS ORDER BY JSBH;RETURN;ENDIF @FMEMO='条件输入控件' BEGINSELECT 'ButtonEdit' as 条件输入控件UNION ALLSELECT 'CheckEdit' as 条件输入控件 UNION ALLSELECT 'DateEdit' as 条件输入控件 UNION ALLSELECT 'ListEdit' as 条件输入控件 UNION ALLSELECT 'TextEdit' as 条件输入控件 UNION ALLSELECT 'TextRead' as 条件输入控件 RETURN;END IF @FMEMO='结算外币' BEGINSELECT '' as 结算外币UNION ALLSELECT HBMC AS 结算外币 FROM MLWB;RETURN;END RETURN
END TRY
BEGIN CATCHDECLARE @ErrorMessage NVARCHAR(4000);DECLARE @ErrorSeverity INT;DECLARE @ErrorState INT;SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();RAISERROR (@ErrorMessage, -- Message text.@ErrorSeverity, -- Severity.@ErrorState ); -- State.
END CATCH;GO
/****** Object: StoredProcedure [dbo].[X9_EditTable] Script Date: 2024-10-11 20:25:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 昆山华岳软件有限公司 岳国军
-- Create date: 2012-12-01
-- Script date: 2020-08-02
-- Script date: 2022-04-15
-- Script Date: 2022-04-18
-- Script Date: 2022-05-07
-- Script Date: 2022-05-14
-- Script Date: 2022-06-25
-- Script Date: 2024-03-16
-- Description: 华岳X9企业管理平台存储过程 用于BUTTONEDIT控件输入
-- Description: 输入参考 使用规格型号配置输入时删除目标参照相关的字段
-- =============================================
CREATE PROCEDURE [dbo].[X9_EditTable](@DQYH NVARCHAR(20), --当前用户@GNBH VARCHAR(20), --功能编号@GNMC NVARCHAR(20), --功能名称@TNAME VARCHAR(20), --表名@FNAME VARCHAR(20), --字段名@FMEMO VARCHAR(20), --字段标题@FTEXT VARCHAR(200), --输入字符串@SXSTR VARCHAR(200), --属性字段值@TJSTR VARCHAR(MAX)) --参考条件字符串
AS
BEGIN TRY--函数dbo.x9_tjsplit(字段名,@TJSTR)可取出使用条件字段的值,--EXEC X9_EditTable 'Admin','0561','盘点入库单填制','DJCHM','XH1','型号','HYX92020PRO','','CKMC@@CHDH@010101@'SET NOCOUNT ON;-- 基础参考信息,来源于基础资料DECLARE @MLLB NVARCHAR(10);IF @FMEMO='客户代号' BEGINSELECT WLDH AS 客户代号,WLMC AS 客户名称, JSWB AS 结算外币 FROM MLWL WHERE WLDH<'50' AND (WLDH+WLMC+DZ+DH+LXR+ZJM) LIKE '%'+@FTEXT+'%' AND DCMX=1;RETURN;ENDIF @FMEMO='供应商代号' BEGINSELECT WLDH AS 供应商代号,WLMC AS 供应商名称, JSWB AS 结算外币 FROM MLWL WHERE WLDH>'50' AND (WLDH+WLMC+DZ+DH+LXR+ZJM) LIKE '%'+@FTEXT+'%' AND DCMX=1;RETURN;ENDIF @FMEMO='供货单位' BEGINSELECT WLDH+'_'+WLMC AS 供货单位, JSWB AS 结算外币 FROM MLWL WHERE WLDH>'50' AND (WLDH+WLMC+DZ+DH+LXR+ZJM) LIKE '%'+@FTEXT+'%' AND DCMX=1;RETURN;ENDIF @FMEMO ='项目代号'BEGINSELECT XMDH AS 项目代号, XMMC AS 项目名称, XMQC AS 项目全称, XMLX 项目类型, XMJLDW 项目计量单位FROM MLCBXM WHERE (XMDH+ZJM+XMMC LIKE '%'+@FTEXT+'%' ) RETURN;END---------------------------------------------------------------------公共参考信息,不区分功能------------------------------------------------------------------IF @FMEMO='部门全称'BEGINSELECT BMQC AS 部门全称 FROM MLBM WHERE BMMC+ZJM LIKE '%'+@FTEXT+'%' ORDER BY BMDH;RETURN;ENDIF @FMEMO='所属部门'BEGINSELECT BMMC AS 所属部门 FROM MLBM WHERE BMMC+ZJM LIKE '%'+@FTEXT+'%' ORDER BY BMDH;RETURN;ENDIF @FMEMO='所属单位'BEGINSELECT WLMC AS 所属单位 FROM MLWL WHERE DCMX=1 AND WLMC+ZJM LIKE '%'+@FTEXT+'%';RETURN;ENDIF @FMEMO='开始年月' BEGINSELECT NY AS 开始年月 FROM MLNY ORDER BY NY DESC;RETURN;END IF @FMEMO='结束年月' BEGINSELECT NY AS 结束年月 FROM MLNY ORDER BY NY DESC;RETURN;END IF @FMEMO='年月' BEGINSELECT NY AS 年月 FROM MLNY ORDER BY NY DESC;RETURN;END IF @FMEMO='业务期间' BEGINSELECT NY AS 业务期间 FROM MLNY ORDER BY NY DESC;RETURN;END IF (@GNMC='记账凭证填制') AND (@FMEMO='往来单位')BEGINSELECT WLDH+'_'+WLMC AS 往来单位, JSWB AS 结算外币 FROM MLWLWHERE (WLDH+'_'+WLMC+ZJM LIKE '%'+@FTEXT+'%') AND DCMX=1; ENDIF (@GNMC='记账凭证填制') AND (@FMEMO='往来人员')BEGINSELECT LTRIM(STR(RYBH))+'_'+RYXM AS 往来人员 FROM MLBMRY WHERE (STR(RYBH)+'_'+RYXM+ZJM LIKE '%'+@FTEXT+'%');END IF (@GNMC='记账凭证填制') AND (@FMEMO='核算部门')BEGINSELECT BMDH+'_'+BMMC AS 核算部门 FROM MLBMWHERE (BMDH+'_'+BMMC+ZJM LIKE '%'+@FTEXT+'%') ; ENDIF @FMEMO='现金流向'BEGINSELECT XMBH+'_'+LTRIM(XMMC) AS 现金流向 FROM MLXJLL WHERE LEN(XMBH)=4 ORDER BY XMBH;ENDIF @FMEMO='收款银行' BEGINSELECT YHMC AS 收款银行,ZHHM AS 收款账号 FROM MLYHZH ORDER BY ZHDH;RETURN;END IF @FMEMO='付款银行' BEGINSELECT YHMC AS 付款银行,ZHHM AS 付款账号 FROM MLYHZH ORDER BY ZHDH;RETURN;END DECLARE @BMDH VARCHAR(12);IF @FMEMO='部门代号' BEGINSELECT BMDH AS 部门代号,BMMC AS 部门名称FROM MLBM WHERE (BMDH+BMMC+ZJM LIKE '%'+@FTEXT+'%') RETURN;ENDIF @FMEMO='部门名称' BEGINSELECT BMMC 部门名称, BMDH AS 部门代号 FROM MLBM WHERE (BMMC+ZJM LIKE '%'+@FTEXT+'%' ) ;RETURN;END --用于多选declare @xd bit;set @xd=0;IF @FMEMO='所属行业' BEGIN--多选示例 原为comboeditSELECT @BMDH =dbo.x9_tjsplit('BMDH',@TJSTR);SELECT @xd as 选定 ,'制造业' AS 所属行业 UNION ALLSELECT @xd as 选定 ,'商品流通业' AS 所属行业 UNION ALLSELECT @xd as 选定 ,'电子商务' AS 所属行业 UNION ALLSELECT @xd as 选定 ,'服务业' AS 所属行业; ENDIF @FMEMO='规格系列' BEGINSELECT XH1 AS 规格系列 FROM MLGGWHERE (XH1+ZJM LIKE '%'+@FTEXT+'%' ) and JC=1 ;RETURN;END IF @FMEMO='产品代号'BEGINSELECT CHDH AS 产品代号 ,CHMC AS 产品名称,JLDW AS 计量单位 FROM MLCH WHERE (CHDH+ZJM+CHMC LIKE '%'+@FTEXT+'%' ) AND DCMX=1 ;RETURN;ENDDECLARE @PPZX VARCHAR(50);IF @FMEMO='人员编号'BEGINSELECT @PPZX =dbo.x9_tjsplit('ZXMC',@TJSTR);SELECT RYBH AS 人员编号,RYXM AS 人员姓名FROM MLBMRY WHERE (BMMC=@PPZX OR @PPZX='') AND CONVERT(varchar(10),RYBH)+RYXM+ZJM LIKE '%'+@FTEXT+'%';RETURN;ENDIF @FMEMO='科目代号' BEGINSELECT KMDH AS 科目代号, KMQC AS 科目全称,HBMC AS 单位或币名FROM MLKM WHERE (KMDH+KMQC+ZJM LIKE '%'+@FTEXT+'%') AND DCMX=1 ;RETURN;ENDIF @FMEMO='多栏科目代号' BEGINSELECT KMDH AS 多栏科目代号, KMQC AS 多栏科目全称 FROM MLKM WHERE DLCX=1 AND DCMX=0;RETURN;ENDIF @FMEMO='出纳科目代号' BEGINSELECT KMDH AS 出纳科目代号, KMQC AS 出纳科目名称FROM MLKM WHERE (KMDH+KMQC+ZJM LIKE '%'+@FTEXT+'%') AND DCMX=1 AND CNHS=1;RETURN;ENDIF @FMEMO='对方科目代号' BEGINSELECT KMDH AS 对方科目代号, KMQC AS 对方科目名称FROM MLKM WHERE (KMDH+KMQC+ZJM LIKE '%'+@FTEXT+'%') AND DCMX=1 AND CNHS=1;RETURN;END---------------------------------------------------------------------多用个别参考信息,先区分字段标题,再判断功能名称------------------------------------------------------------------DECLARE @ZJLZL NVARCHAR(20),@FJLZL NVARCHAR(20),@ZJLDW NVARCHAR(20),@FJLDW NVARCHAR(20);IF @FMEMO='往来单位'BEGINDECLARE @FG INT;SET @FG=0SET @FG=CHARINDEX('_',@FTEXT);IF @FG>0BEGINSET @FTEXT=SUBSTRING(@FTEXT,@FG+1,LEN(@FTEXT)-@FG);ENDIF (@GNMC='采购订单填制' or @GNMC='采购入库单填制' OR @GNMC='质量检验单填制' OR @GNMC='委外入库单填制' OR @GNMC='采购计划单填制' OR @GNMC='委外出库单填制' OR @GNMC='采购价格调整表' OR @GNMC='委外价格调整表' OR @GNMC='委外订单填制')BEGINSELECT WLDH + '_' + WLMC AS 往来单位, JSWB AS 结算外币,DZ AS 地址,LXR AS 联系人,DH 电话FROM MLWL WHERE (WLDH+WLMC+ZJM LIKE '%'+@FTEXT+'%') AND DCMX=1 AND WLDH>'50' ;RETURN;ENDIF (@GNMC='销售订单填制' or @GNMC='销售出库单填制' or @GNMC='生产计划单填制'or @GNMC='销售价格调整表')BEGINSELECT WLDH + '_' + WLMC AS 往来单位, JSWB AS 结算外币,DZ AS 地址,LXR AS 联系人,DH 电话FROM MLWL WHERE (WLDH+WLMC+ZJM LIKE '%'+@FTEXT+'%') AND DCMX=1 AND WLDH<'50' ;RETURN;ENDIF (@GNMC='收款单填制' OR @GNMC='应收账款总账查询' OR @GNMC='应收账款明细账查询')BEGINSELECT WLDH + '_' + WLMC AS 往来单位, JSWB AS 结算外币,DZ AS 地址,LXR AS 联系人,DH 电话FROM MLWL WHERE (WLDH+WLMC+ZJM LIKE '%'+@FTEXT+'%') AND DCMX=1 AND WLDH<'50';RETURN;ENDIF (@GNMC='付款单填制' OR @GNMC='应付账款总账查询' OR @GNMC='应付账款明细账查询')BEGINSELECT WLDH + '_' + WLMC AS 往来单位, JSWB AS 结算外币,DZ AS 地址,LXR AS 联系人,DH 电话FROM MLWL WHERE (WLDH+WLMC+ZJM LIKE '%'+@FTEXT+'%') AND DCMX=1 AND WLDH>'50';RETURN;ENDEND IF @FMEMO ='功能编号' AND @GNMC='查询功能显示设置'BEGINSELECT GNBH,GNMC FROM X9_GN WHERE GNBH+GNMC LIKE '%'+@FTEXT+'%' AND (GNLY='查询输入' OR GNLY='汇总查询') ORDER BY GNBH;RETURN;END
END TRY
BEGIN CATCHTHROW;
END CATCH;GO
/****** Object: StoredProcedure [dbo].[X9_TJ_1FLML] Script Date: 2024-10-11 20:25:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =================================================================
-- Author: 昆山华岳软件有限公司 岳国军
-- Create date: 2011-10-31
-- Script Date: 2022-04-10
-- Script Date: 2022-06-30
-- Script Date: 2024-03-16-- Description: 提交后处理程序 1分类目录
-- =================================================================
CREATE PROCEDURE [dbo].[X9_TJ_1FLML] (@DQYH NVARCHAR(10), --用户名称@GNNAME NVARCHAR(30), --功能名称@TBNAME NVARCHAR(30), --数据表名@BMSTR NVARCHAR(MAX)) --提交的主键字符串,使用 @BMSTR LIKE '%(' + CHDH + ')%' 表达式做为条件;
AS--分类目录 提交后主要处理底层明细和计算助记码BEGIN TRYIF @GNNAME='账套用户角色维护' OR @GNNAME='账套用户设置' OR @GNNAME='功能列表定义'BEGIN--select * from x9_gn--删除多余行DELETE X9_GNYHQX WHERE (YHJS NOT IN (SELECT YHJS FROM X9_GNYHJS ));DELETE X9_GNYHQX WHERE (GNBH NOT IN (SELECT GNBH FROM X9_GN WHERE JC=2 AND SFQY=0)) ;DELETE X9_GNYHQX WHERE (SSMK NOT IN (SELECT MKMC FROM X9_GN_GNMK WHERE SFYY=1));--DELETE X9_GNYHQX WHERE YHJS='系统管理员';--增加缺少行DECLARE @MAXID INT;SELECT @MAXID =1+MAX(QXID) FROM X9_GNYHQX ;IF @MAXID IS NULL SET @MAXID=1;WITH YH (YHJS) AS (SELECT DISTINCT YHJS FROM X9_GNYHJS where YHJS<>'系统管理员')INSERT INTO X9_GNYHQX (QXID,YHJS,GNBH,GNMC,GNLY,GNLB,SSMK,JZZD,SFSQ,Xzbj,Xzdy,Xzck,Xzwj,Xzdc,XZQX,SFYC)SELECT @MAXID+(ROW_NUMBER() OVER(ORDER BY YH.YHJS, GN.GNBH)),YH.YHJS, GN.GNBH, GN.GNMC, GN.GNLY, GN.GNLB, GN.SSMK,'' AS JZZD, 0 AS SFSQ,1 AS XZBJ,1 AS XZDY,1 AS XZCK,1 AS XZWJ,1 AS XZDC ,'111111' AS XZQX,GN.SFYCFROM YH CROSS JOIN X9_GN GNWHERE GN.JC=2 AND SFQY=0 AND GN.SSMK IN (SELECT MKMC FROM X9_GN_GNMK WHERE SFYY=1) AND (YH.YHJS+GN.GNBH) NOT IN(SELECT YHJS+GNBH FROM X9_GNYHQX);UPDATE QX SET GNMC = GN.GNMC, GNLY = GN.GNLY, SSMK = GN.SSMK, GNLB = GN.GNLB, SFYC = GN.SFYCFROM X9_GNYHQX AS QX INNER JOIN X9_GN AS GN ON QX.GNBH = GN.GNBH;SELECT '用户权限初始检查已经完成';END --SELECT DISTINCT YHJS,'' AS SJBH, ROW_NUMBER() OVER(ORDER BY YHJS) AS YHBH FROM X9_GNYH--UPDATE X9_GNYHQX SET SFSQ =1;--select * from X9_GNYHQX IF @GNNAME='功能列表定义'BEGINDELETE X9_GN WHERE SFQL=1;DELETE X9_HELP_YY WHERE LEFT(ZTBH,2)<>'00' AND (ZTBH NOT IN(SELECT GNBH FROM X9_GN WHERE SFQY=0 AND SFYC=0)); DELETE X9_GN_GNMK WHERE MKMC NOT IN (SELECT DISTINCT SSMK FROM X9_GN) ;INSERT INTO X9_GN_GNMK (MKMC) SELECT DISTINCT SSMK AS MKMCFROM X9_GN WHERE (SSMK NOT IN (SELECT MKMC FROM X9_GN_GNMK AS X9_GN_GNMK_1)) AND SSMK<>'' AND (SFQY = 0);UPDATE X9_GN_GNMK SET GNSL=DY.GNSL FROM X9_GN_GNMK MK INNER JOIN (SELECT SSMK,COUNT(GNBH) GNSL FROM X9_GN WHERE JC=2 GROUP BY SSMK) DY ON MK.MKMC=DY.SSMK ; UPDATE X9_GN SET ZJM=DBO.X9_ZJFH(GNMC+GNLY+GNLB+SSMK) ;INSERT INTO X9_HELP_YY (ZTBH,SJBH,BJBH,ZTMC,JC,SFGN) SELECT GNBH AS ZTBH,SJ AS SJBH,BJ AS BJBH,GNMC AS ZTMC,JC,1 AS SFGN FROM X9_GN WHERE (SFQY=0) AND GNBH NOT IN(SELECT ZTBH FROM X9_HELP_YY);UPDATE X9_HELP_YY SET ZTMC=GN.GNMC FROM X9_HELP_YY YY INNER JOIN X9_GN GN ON YY.ZTBH=GN.GNBH WHERE YY.ZTMC <>GN.GNMC; UPDATE X9_HELP_YY SET SFGN=1 FROM X9_HELP_YY YY INNER JOIN X9_GN GN ON YY.ZTBH=GN.GNBH WHERE YY.SFGN=0; SELECT '功能列表定义功能提交后处理,已完成帮助主题的更新。';RETURNENDIF @GNNAME='客户目录'BEGINEXEC X9_REZJM @TBNAME;EXEC X9_DCMX @TBNAME;UPDATE MLWL SET ZKL=0 WHERE ZKL<0 OR ZKL>100;SELECT '客户目录提交后处理已完成';RETURNENDIF @GNNAME='供应商目录'BEGINEXEC X9_REZJM @TBNAME;EXEC X9_DCMX @TBNAME;SELECT '供应商目录提交后处理已完成';RETURNEND IF @GNNAME='部门目录'BEGINEXEC X9_REZJM @TBNAME;EXEC X9_DCMX @TBNAME;SELECT '部门目录提交后处理已完成'RETURNEND IF @GNNAME='会计科目'BEGINEXEC X9_REZJM @TBNAME;EXEC X9_DCMX @TBNAME;SELECT '会计科目提交后处理已完成'RETURNEND IF @GNNAME='固定资产卡片管理'BEGINEXEC X9_REZJM @TBNAME;EXEC X9_DCMX @TBNAME;SELECT '固定资产档案提交后处理已完成'RETURNEND IF @GNNAME='数据字典'BEGINSELECT '数据字典提交后处理已完成'RETURNEND END TRYBEGIN CATCHDECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity int,@ErrorState INT;SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState ); END CATCH; GO
/****** Object: StoredProcedure [dbo].[X9_TJ_4DJSR] Script Date: 2024-10-11 20:25:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =================================================================
-- Author: 昆山华岳软件有限公司 岳国军
-- Create date: 2011-10-31
-- Script Date: 2019-02-23
-- Script Date: 2022-04-14
-- Script Date: 2024-03-16-- Description: 单据提交后处理程序
-- 1、可以使用表达@BMSTR LIKE '%(' + RTRIM(RK.OID) + ')%'匹配数据行,对提交后的数据进行处理。
-- 2、因匹配条件复杂和数据行可以较多,此过程效率较低,不提倡多用。
-- 3、返回提示信息。
-- =================================================================
CREATE PROCEDURE [dbo].[X9_TJ_4DJSR] (@DQYH NVARCHAR(10), --用户名称@DJMC NVARCHAR(30), --单据名称@BMSTR NVARCHAR(MAX)) --OID字符串
AS
BEGINBEGIN TRY--IF @DJMC='完工入库单'-- BEGIN-- UPDATE RK SET RKDJ = XM.JHJJ, RKJE = RK.RKSL * XM.JHJJ-- FROM DJCHM AS RK INNER JOIN MLCHXM AS XM ON RK.CHXH = XM.CHXH-- WHERE (@BMSTR LIKE '%(' + RTRIM(RK.OID) + ')%');-- SELECT @DJMC+' 提交后处理已经完成';-- END RETURN;END TRYBEGIN CATCHDECLARE @ErrorMessage NVARCHAR(4000),@ErrorState INT;SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorState = ERROR_STATE();THROW 410,@ErrorMessage,@ErrorState;END CATCH;
ENDGO
/****** Object: StoredProcedure [dbo].[X9_ZZKJ] Script Date: 2024-10-11 20:25:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 昆山华岳软件有限公司 岳国军
-- Create date: 2012-07-12
-- Script Date: 2019-03-19
-- Script Date: 2022-06-28
-- Script Date: 2024-03-16-- Description: 总账查询:会计总账
-- =============================================
CREATE PROCEDURE [dbo].[X9_ZZKJ](@KSNY char(6),@JSNY char(6),@LB NVARCHAR(20),@PP NVARCHAR(20),@JC INT )
ASIF @LB='' SET @LB='会计分类总账';IF @LB='会计分类总账'SELECT KM.KMDH , KM.KMQC , KM.YEFX , SUM(CASE NY WHEN @KSNY THEN (CASE KM.YEFX WHEN '贷' THEN (0 - ZZ.JEYC) ELSE JEYC END) ELSE 0 END) JEYC, SUM(ZZ.JEJF) JEJF, SUM(ZZ.JEDF) JEDF, SUM(CASE NY WHEN @JSNY THEN (CASE KM.YEFX WHEN '贷' THEN 0 - ZZ.JEYM ELSE JEYM END)ELSE 0 END) JEYM,'会计分类明细账查询' as 链接功能FROM MLKM KM LEFT OUTER JOINUTZZKJ ZZ ON ZZ.KMDH LIKE KM.KMDH + '%'WHERE (KM.JC <= @JC) AND (ZZ.NY >= @KSNY) AND (ZZ.NY <= @JSNY) AND (KM.KMDH LIKE @PP + '%')GROUP BY KM.KMDH, KM.KMQC, KM.YEFX ORDER BY KM.KMDH;IF @LB='会计个人往来总账'SELECT ZZ.WLRY,KM.KMDH KMDH, KM.KMQC KMQC, KM.YEFX YEFX, SUM(CASE NY WHEN @KSNY THEN (CASE KM.YEFX WHEN '贷' THEN (0 - ZZ.JEYC) ELSE JEYC END) ELSE 0 END) JEYC, SUM(ZZ.JEJF) JEJF, SUM(ZZ.JEDF) JEDF, SUM(CASE NY WHEN @JSNY THEN (CASE KM.YEFX WHEN '贷' THEN 0 - ZZ.JEYM ELSE JEYM END) ELSE 0 END) JEYM,'会计个人往来明细账查询' as 链接功能 FROM MLKM KM INNER JOIN UTZZKJ ZZ ON KM.KMDH = ZZ.KMDH WHERE (ZZ.WLRY<>'') AND (ZZ.NY >= @KSNY) AND (ZZ.KMDH LIKE @PP + '%') AND (ZZ.NY <= @JSNY) GROUP BY ZZ.WLRY,KM.KMDH,KM.KMQC, KM.YEFX ORDER BY ZZ.WLRY ;IF @LB='会计部门总账'SELECT KM.KMDH KMDH,KM.KMQC KMQC,ZZ.HSBM,KM.YEFX YEFX, SUM(CASE NY WHEN @KSNY THEN (CASE KM.YEFX WHEN '贷' THEN (0 - ZZ.JEYC) ELSE JEYC END) ELSE 0 END) JEYC, SUM(ZZ.JEJF) JEJF, SUM(ZZ.JEDF) JEDF, SUM(CASE NY WHEN @JSNY THEN (CASE KM.YEFX WHEN '贷' THEN 0 - ZZ.JEYM ELSE JEYM END) ELSE 0 END) JEYM,'会计部门明细账查询' as 链接功能FROM UTZZKJ ZZ INNER JOIN MLKM KM ON ZZ.KMDH = KM.KMDHWHERE (ZZ.HSBM <> N'') AND (ZZ.NY >= @KSNY) AND (ZZ.KMDH LIKE @PP + '%') AND (ZZ.NY <= @JSNY) AND (KM.BMHS = 1)GROUP BY KM.KMDH,KM.KMQC, ZZ.HSBM,KM.YEFX ORDER BY KM.KMDH,ZZ.HSBM;IF @LB='会计外币总账'SELECT KM.KMDH KMDH, KM.KMQC KMQC, KM.YEFX YEFX, ZZ.HBMC, SUM(CASE NY WHEN @KSNY THEN (CASE KM.YEFX WHEN '贷' THEN (0 - ZZ.SLYC) ELSE SLYC END) ELSE 0 END) SLYC, SUM(ZZ.SLJF) SLJF, SUM(ZZ.SLDF) SLDF,SUM(CASE NY WHEN @JSNY THEN (CASE KM.YEFX WHEN '贷' THEN 0 - ZZ.SLYM ELSE SLYM END) ELSE 0 END) SLYM,SUM(CASE NY WHEN @KSNY THEN (CASE KM.YEFX WHEN '贷' THEN (0 - ZZ.JEYC) ELSE JEYC END) ELSE 0 END) JEYC, SUM(ZZ.JEJF) JEJF, SUM(ZZ.JEDF) JEDF, SUM(CASE NY WHEN @JSNY THEN (CASE KM.YEFX WHEN '贷' THEN 0 - ZZ.JEYM ELSE JEYM END) ELSE 0 END) JEYM,'会计外币明细账查询' as 链接功能 FROM MLKM KM INNER JOIN UTZZKJ ZZ ON KM.KMDH = ZZ.KMDH WHERE ZZ.HBMC<>'' AND (KM.JC <= @JC) AND (ZZ.NY >= @KSNY) AND (ZZ.NY <= @JSNY) GROUP BY KM.KMDH, KM.KMQC, KM.YEFX, ZZ.HBMCHAVING (KM.KMDH LIKE @PP + '%') ORDER BY KM.KMDH,ZZ.HBMC;--IF @LB='会计单位往来总账' --SELECT ZZ.WLDW,KM.KMDH KMDH,KM.KMQC KMQC, KM.YEFX YEFX, -- SUM(CASE NY WHEN @KSNY THEN (CASE KM.YEFX WHEN '贷' THEN (0 - ZZ.JEYC) ELSE JEYC END) ELSE 0 END) JEYC, -- SUM(ZZ.JEJF) JEJF, SUM(ZZ.JEDF) JEDF, -- SUM(CASE NY WHEN @JSNY THEN (CASE KM.YEFX WHEN '贷' THEN 0 - ZZ.JEYM ELSE JEYM END) ELSE 0 END) JEYM,-- '会计单位往来明细账查询' as 链接功能-- FROM UTZZKJ ZZ INNER JOIN MLKM KM ON ZZ.KMDH = KM.KMDH -- WHERE ZZ.WLDW<>'' AND (ZZ.NY >= @KSNY) AND ((ZZ.KMDH LIKE @PP + '%') or ZZ.WLDW like '%'+@PP +'%')-- AND (ZZ.NY <= @JSNY)-- GROUP BY ZZ.WLDW, KM.KMDH,KM.KMQC, KM.YEFX;--RETURN;--IF @LB='会计数量总账'-- SELECT KM.KMDH KMDH, KM.KMQC KMQC, KM.YEFX YEFX, ZZ.HBMC HBMC, -- SUM(CASE NY WHEN @KSNY THEN (CASE KM.YEFX WHEN '贷' THEN (0 - ZZ.SLYC) ELSE SLYC END) ELSE 0 END) SLYC, -- SUM(CASE NY WHEN @KSNY THEN (CASE KM.YEFX WHEN '贷' THEN (0 - ZZ.JEYC) ELSE JEYC END) ELSE 0 END) JEYC,-- SUM(ZZ.SLJF) SLJF,-- SUM(ZZ.JEJF) JEJF, -- SUM(ZZ.SLDF) SLDF,-- SUM(ZZ.JEDF) JEDF, -- SUM(CASE NY WHEN @JSNY THEN (CASE KM.YEFX WHEN '贷' THEN 0 - ZZ.SLYM ELSE SLYM END) ELSE 0 END) SLYM,-- (case SUM(CASE NY WHEN @JSNY THEN (CASE KM.YEFX WHEN '贷' THEN 0 - ZZ.SLYM ELSE SLYM END) ELSE 0 END) when-- 0 then 0 else SUM(CASE NY WHEN @JSNY THEN (CASE KM.YEFX WHEN '贷' THEN 0 - ZZ.JEYM ELSE JEYM END) ELSE 0 END)/-- SUM(CASE NY WHEN @JSNY THEN (CASE KM.YEFX WHEN '贷' THEN 0 - ZZ.SLYM ELSE SLYM END) ELSE 0 END) end) 单价,-- SUM(CASE NY WHEN @JSNY THEN (CASE KM.YEFX WHEN '贷' THEN 0 - ZZ.JEYM ELSE JEYM END) ELSE 0 END) JEYM,-- '会计数量明细账查询' as 链接功能-- FROM MLKM KM LEFT OUTER JOIN-- UTZZKJ ZZ ON KM.KMDH = ZZ.KMDH -- WHERE (KM.JC <= @JC) AND (ZZ.NY >= @KSNY) AND (ZZ.NY <= @JSNY) AND (KM.SLHS = 1)-- GROUP BY KM.KMDH, KM.KMQC, KM.YEFX, ZZ.HBMC-- HAVING (KM.KMDH LIKE @PP + '%');GO
/****** Object: StoredProcedure [dbo].[X9_ZZKJDLMX] Script Date: 2024-10-11 20:25:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 昆山华岳软件有限公司 岳国军
-- Create date: 2012-07-20
-- Script Date: 2019-03-19
-- Script Date: 2020-09-07
-- Script Date: 2022-06-28
-- Script Date: 2024-03-16-- Description: 会计多栏账项查询
-- =============================================
CREATE PROCEDURE [dbo].[X9_ZZKJDLMX](@KSNY CHAR(6),@JSNY CHAR(6),@KMDH varchar(12),@KMMC nvarchar(50),@PCXH VARCHAR(10))
ASDELETE UTMXKJ WHERE PCXH=@PCXHDECLARE @JC INT,@DLKMMC NVARCHAR(MAX),@MXKMDH varchar(12),@MXKMMC nvarchar(50),@YEFX NVARCHAR(2);SELECT @KMMC=KMQC,@JC=JC,@YEFX=YEFX FROM MLKM WHERE KMDH=@KMDH;SET @DLKMMC='';DECLARE UTDLKJKM_cursor CURSOR FORSELECT KM.KMDH,KM.KMMC FROM MLKM KM INNER JOIN (SELECT KMDH,SUM(JEJF) AS JFJE,SUM(JEDF) AS DFJE FROM UTZZKJ WHERE (NY BETWEEN @KSNY AND @JSNY) AND (KMDH LIKE @KMDH+'%') GROUP BY KMDH) ZZ ON ZZ.KMDH=KM.KMDH WHERE KM.KMDH LIKE @KMDH+'%' AND KM.JC=@JC+1 AND (ZZ.JFJE<>0 OR ZZ.DFJE<>0) ORDER BY KMDH;OPEN UTDLKJKM_cursor;FETCH FROM UTDLKJKM_cursor INTO @MXKMDH,@MXKMMC;WHILE @@FETCH_STATUS=0BEGINSET @DLKMMC=@DLKMMC+'['+@MXKMMC+'],'FETCH NEXT FROM UTDLKJKM_cursorINTO @MXKMDH,@MXKMMC;ENDCLOSE UTDLKJKM_cursor;DEALLOCATE UTDLKJKM_cursor ;IF @DLKMMC='' BEGINSELECT '没有有效的多栏数据'RETURN;ENDSET @DLKMMC=LEFT(@DLKMMC,LEN(@DLKMMC)-1);DECLARE @NY CHAR(6),@SYNY CHAR(6)SET @NY=@KSNY;WHILE @NY<=@JSNYBEGIN--期初余额INSERT INTO UTMXKJ(KMDH,KMQC,NY, RQ, OID, ZY, JFJE, DFJE, YEJE, JFSL, DFSL, YESL, PCXH, ZZXH)SELECT @KMDH AS KMDH, '' AS KMQC, @NY AS NY, LEFT(@NY, 4) + '年' + RIGHT(@NY, 2) + '月' AS RQ, '' AS OID, '月初余额' AS ZY, 0 AS JFJE, 0 AS DFJE,ISNULL(SUM(CASE @YEFX WHEN '借' THEN JEYC ELSE 0 - JEYC END), 0) AS YEJE, 0 AS JFSL, 0 AS DFSL, 0 AS YESL, @PCXH AS PCXH, 0 AS ZZXHFROM UTZZKJ INNER JOINMLKM ON UTZZKJ.KMDH = MLKM.KMDHWHERE (UTZZKJ.NY = @NY) AND (UTZZKJ.KMDH LIKE @KMDH + '%')--明细账INSERT INTO UTMXKJ(KMDH,KMQC,NY, RQ, OID, ZY, JFJE, DFJE, YEJE, JFSL, DFSL, YESL, PCXH, ZZXH)SELECT @KMDH,MLKM.KMMC, DJJZPZO.NY, CONVERT(CHAR(10), DJJZPZO.RQ, 127) AS RQ, DJJZPZO.OID, DJJZPZM.ZY, DJJZPZM.JFJE, DJJZPZM.DFJE, 0 AS YEJE, 0 AS JFSL, 0 AS DFSL, 0 AS YESL, @PCXH AS PCXH, DJJZPZM.ZZXHFROM DJJZPZO INNER JOINDJJZPZM ON DJJZPZO.OID = DJJZPZM.OID INNER JOINMLKM ON DJJZPZM.KMDH = MLKM.KMDHWHERE (DJJZPZO.NY = @NY) AND (DJJZPZO.DJR <> N'') AND (DJJZPZM.KMDH LIKE @KMDH + '%')ORDER BY RQ--本月合计INSERT INTO UTMXKJ (KMDH,KMQC,NY, RQ, OID, ZY, JFJE, DFJE, YEJE, JFSL, DFSL, YESL, PCXH, ZZXH)SELECT @KMDH,'' AS KMQC, @NY AS NY, LEFT(@NY, 4) + '年' + RIGHT(@NY, 2) + '月' AS RQ, '' AS OID,'本月合计' AS ZY, ISNULL(SUM(UTZZKJ.JEJF), 0) AS JFJE, ISNULL(SUM(UTZZKJ.JEDF), 0) AS DFJE, ISNULL(SUM(CASE @YEFX WHEN '借' THEN JEYM ELSE 0 - JEYM END), 0) AS YEJE, 0 AS JFSL, 0 AS DFSL, 0 AS YESL, @PCXH AS PCXH, 0 AS ZZXHFROM UTZZKJ INNER JOINMLKM ON UTZZKJ.KMDH = MLKM.KMDHWHERE (UTZZKJ.NY = @NY) AND (UTZZKJ.KMDH LIKE @KMDH + '%')SET @SYNY=@NY;SELECT @NY=CASE WHEN RIGHT(@SYNY,2)='12' THEN CONVERT(VARCHAR(4),CONVERT(INT,LEFT(@SYNY,4))+1)+'01' ELSE LEFT(@SYNY,4)+RIGHT('0'+CONVERT(VARCHAR(2),CONVERT(INT,RIGHT(@SYNY,2))+1),2) END;END--计算余额DECLARE @UYESL DECIMAL(18,6),@UYEJE DECIMAL(18,6)DECLARE @ID INT,@OID VARCHAR(15), @JFSL DECIMAL(18,6), @DFSL DECIMAL(18,6), @YESL DECIMAL(18,6), @JFJE DECIMAL(18,2), @DFJE DECIMAL(18,2), @YEJE DECIMAL(18,6);DECLARE UTDLMXKJ_cursor CURSOR FORSELECT ID,OID, JFSL, DFSL,YESL,JFJE,DFJE,YEJEFROM UTMXKJWHERE (PCXH = @PCXH)ORDER BY IDOPEN UTDLMXKJ_cursor;FETCH FROM UTDLMXKJ_cursorINTO @ID,@OID, @JFSL, @DFSL, @YESL, @JFJE, @DFJE, @YEJE;WHILE @@FETCH_STATUS=0BEGINIF @OID<>''BEGINIF @YEFX='借'BEGINSET @UYESL=@UYESL+@JFSL-@DFSL;SET @UYEJE=@UYEJE+@JFJE-@DFJE;UPDATE UTMXKJ SET YESL=@UYESL,YEJE=@UYEJE,ZKJE=@JFJE-@DFJEWHERE PCXH=@PCXH AND ID=@ID;ENDELSEBEGINSET @UYESL=@UYESL+@DFSL-@JFSL;SET @UYEJE=@UYEJE+@DFJE-@JFJE;UPDATE UTMXKJ SET YESL=@UYESL,YEJE=@UYEJE,ZKJE=@DFJE-@JFJEWHERE PCXH=@PCXH AND ID=@ID;ENDENDELSEBEGINSET @UYESL=@YESL;SET @UYEJE=@YEJE;ENDFETCH NEXT FROM UTDLMXKJ_cursorINTO @ID,@OID, @JFSL, @DFSL, @YESL, @JFJE, @DFJE, @YEJE;ENDCLOSE UTDLMXKJ_cursor;DEALLOCATE UTDLMXKJ_cursor ;DECLARE @CXSQL NVARCHAR(MAX);SET @CXSQL='SELECT RQ, OID, ZY, JFJE, DFJE,YEFX, YEJE,'+@DLKMMC+' FROM UTMXKJ '+'PIVOT (SUM(ZKJE) FOR KMQC IN ('+@DLKMMC+')) T'+' WHERE PCXH='+@PCXH+' ORDER BY ID'--SELECT @CXSQLEXEC (@CXSQL);RETURN; --SELECT * FROM UTMXKJ--SELECT ZY,[201301],[201312] FROM UTMXKJ -- PIVOT(sum(JFJE) FOR NY IN ([201301],[201312])) TGO
/****** Object: StoredProcedure [dbo].[X9_ZZKJLXMX] Script Date: 2024-10-11 20:25:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 昆山华岳软件有限公司 岳国军
-- Create date: 2012-07-20
-- Script Date: 2019-03-19
-- Script Date: 2022-06-28
-- Script Date: 2024-03-16-- Description: 会计明细账项连续查询
-- =============================================
CREATE PROCEDURE [dbo].[X9_ZZKJLXMX]@KSNY CHAR(6),@JSNY CHAR(6),@PPDH varchar(12),@PCXH VARCHAR(10)
ASDELETE UTMXKJ WHERE PCXH=@PCXH--科目循环DECLARE @NY CHAR(6),@KMDH VARCHAR(12),@KMQC NVARCHAR(100),@SYNY CHAR(6),@YEFX NVARCHAR(2);DECLARE UTMXKJKM_cursor CURSOR FORSELECT KMDH,KMQC,YEFX FROM MLKM WHERE DCMX=1 AND KMDH LIKE @PPDH +'%' ORDER BY KMDHOPEN UTMXKJKM_cursor;FETCH FROM UTMXKJKM_cursorINTO @KMDH,@KMQC,@YEFX;WHILE @@FETCH_STATUS=0BEGINSET @NY=@KSNYWHILE @NY<=@JSNYBEGIN--期初余额INSERT INTO UTMXKJ(KMDH,KMQC,NY, RQ, OID, ZY, JFJE, DFJE, YEJE, JFSL, DFSL, YESL, PCXH, ZZXH)SELECT @KMDH,@KMQC,@NY AS NY, LEFT(@NY, 4) + '年' + RIGHT(@NY, 2) + '月' AS RQ, '' AS OID, '月初余额' AS ZY, 0 AS JFJE, 0 AS DFJE,ISNULL(SUM(CASE @YEFX WHEN '借' THEN JEYC ELSE 0 - JEYC END), 0) AS YEJE, 0 AS JFSL, 0 AS DFSL, 0 AS YESL, @PCXH AS PCXH, 0 AS ZZXHFROM UTZZKJ INNER JOINMLKM ON UTZZKJ.KMDH = MLKM.KMDHWHERE (UTZZKJ.NY = @NY) AND (UTZZKJ.KMDH LIKE @KMDH + '%') --明细账INSERT INTO UTMXKJ(KMDH,KMQC,NY, RQ, OID, ZY, JFJE, DFJE, YEJE, JFSL, DFSL, YESL, PCXH, ZZXH)SELECT @KMDH,@KMQC,DJJZPZO.NY, CONVERT(CHAR(10), DJJZPZO.RQ, 127) AS RQ, DJJZPZO.OID, DJJZPZM.ZY, DJJZPZM.JFJE, DJJZPZM.DFJE, 0 AS YEJE, 0 AS JFSL, 0 AS DFSL, 0 AS YESL, @PCXH AS PCXH, DJJZPZM.ZZXHFROM DJJZPZO INNER JOINDJJZPZM ON DJJZPZO.OID = DJJZPZM.OID INNER JOINMLKM ON DJJZPZM.KMDH = MLKM.KMDHWHERE (DJJZPZO.NY = @NY) AND (DJJZPZO.DJR <> N'') AND (DJJZPZM.KMDH LIKE @KMDH + '%')ORDER BY RQ--本月合计INSERT INTO UTMXKJ (KMDH,KMQC,NY, RQ, OID, ZY, JFJE, DFJE, YEJE, JFSL, DFSL, YESL, PCXH, ZZXH)SELECT @KMDH,@KMQC, @NY AS NY, LEFT(@NY, 4) + '年' + RIGHT(@NY, 2) + '月' AS RQ, '' AS OID, '本月合计' AS ZY,ISNULL(SUM(UTZZKJ.JEJF), 0) AS JFJE, ISNULL(SUM(UTZZKJ.JEDF), 0) AS DFJE, ISNULL(SUM(CASE @YEFX WHEN '借' THEN JEYM ELSE 0 - JEYM END), 0) AS YEJE, 0 AS JFSL, 0 AS DFSL, 0 AS YESL,@PCXH AS PCXH, 0 AS ZZXHFROM UTZZKJ INNER JOINMLKM ON UTZZKJ.KMDH = MLKM.KMDHWHERE (UTZZKJ.NY = @NY) AND (UTZZKJ.KMDH LIKE @KMDH + '%')SET @SYNY=@NY;SELECT @NY=CASE WHEN RIGHT(@SYNY,2)='12' THEN CONVERT(VARCHAR(4),CONVERT(INT,LEFT(@SYNY,4))+1)+'01' ELSE LEFT(@SYNY,4)+RIGHT('0'+CONVERT(VARCHAR(2),CONVERT(INT,RIGHT(@SYNY,2))+1),2) END;ENDFETCH NEXT FROM UTMXKJKM_cursorINTO @KMDH,@KMQC,@YEFX;ENDCLOSE UTMXKJKM_cursor;DEALLOCATE UTMXKJKM_cursor ;--计算余额DECLARE @DQKMDH VARCHAR(12);DECLARE @UYESL DECIMAL(18,6),@UYEJE DECIMAL(18,6)DECLARE @ID INT,@OID VARCHAR(15), @JFSL DECIMAL(18,6), @DFSL DECIMAL(18,6), @YESL DECIMAL(18,6), @JFJE DECIMAL(18,2), @DFJE DECIMAL(18,2), @YEJE DECIMAL(18,6);DECLARE UTMXKJ_cursor CURSOR FORSELECT KMDH,ID,OID, JFSL, DFSL,YESL,JFJE,DFJE,YEJEFROM UTMXKJWHERE (PCXH = @PCXH)ORDER BY IDOPEN UTMXKJ_cursor;FETCH FROM UTMXKJ_cursorINTO @KMDH,@ID,@OID, @JFSL, @DFSL, @YESL, @JFJE, @DFJE, @YEJE;SET @DQKMDH='';WHILE @@FETCH_STATUS=0BEGINIF @DQKMDH<>@KMDHBEGINSELECT @YEFX=YEFX FROM MLKM WHERE KMDH=@KMDH;END IF @OID<>''BEGINIF @YEFX='借'BEGINSET @UYESL=@UYESL+@JFSL-@DFSL;SET @UYEJE=@UYEJE+@JFJE-@DFJE;UPDATE UTMXKJ SET YESL=@UYESL,YEJE=@UYEJEWHERE PCXH=@PCXH AND ID=@ID;ENDELSEBEGINSET @UYESL=@UYESL+@DFSL-@JFSL;SET @UYEJE=@UYEJE+@DFJE-@JFJE;UPDATE UTMXKJ SET YESL=@UYESL,YEJE=@UYEJEWHERE PCXH=@PCXH AND ID=@ID;ENDENDELSEBEGINSET @UYESL=@YESL;SET @UYEJE=@YEJE;ENDSET @DQKMDH=@KMDH; FETCH NEXT FROM UTMXKJ_cursorINTO @KMDH,@ID,@OID, @JFSL, @DFSL, @YESL, @JFJE, @DFJE, @YEJE;ENDCLOSE UTMXKJ_cursor;DEALLOCATE UTMXKJ_cursor ;SELECT KMDH,KMQC,NY,RQ, OID, ZY, JFJE, DFJE,YEFX, YEJEFROM UTMXKJ WHERE PCXH=@PCXH ORDER BY ID;RETURN;GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'负责人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MLBM', @level2type=N'COLUMN',@level2name=N'FZR'
GO
三、自定义函数
USE [hyX1206]
GO
/****** Object: UserDefinedFunction [dbo].[X9_BBJE] Script Date: 2024-10-11 20:25:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 昆山华岳软件有限公司
-- CreateDate: 2012-07-05
-- Script Date: 2022-06-24
-- Description: 本币金额计算
-- =============================================
CREATE FUNCTION [dbo].[X9_BBJE]
(@YWRQ DATETIME,@WBMC VARCHAR(30),@WBSL DECIMAL(18,4)) --业务日期 --外币名称 --外币数量
RETURNS VARCHAR(20)
AS
BEGINDeclare @BBJE DECIMAL(18,4);Declare @PJRQ VARCHAR(25);Declare @BBSTR VARCHAR(20);SET @BBJE=0;IF @WBMC='人民币(RMB)' OR @WBMC=''BEGINSET @BBSTR=STR(@WBSL,18,4); ENDELSEBEGINIF @WBSL IS NULL SET @WBSL=0SELECT @PJRQ =MAX(RQ) FROM MLWBPJ WHERE RQ<=CONVERT(VARCHAR(10),@YWRQ,120); SELECT @BBJE=(JZHL*@WBSL)/100 FROM MLWBPJ WHERE RQ=@PJRQ AND HBMC=@WBMC ;SET @BBSTR=STR(@BBJE,18,4);ENDRETURN @BBSTR;END
GO
/****** Object: UserDefinedFunction [dbo].[X9_DQNY] Script Date: 2024-10-11 20:25:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 昆山华岳软件有限公司
-- Create date: 2009-03-07
-- Description: 当前业务期间
-- =============================================
CREATE FUNCTION [dbo].[X9_DQNY]
(-- Add the parameters for the function here
)
RETURNS CHAR(6)
AS
BEGINDeclare @STR CHAR(6)SELECT @STR=MAX(NY) FROM MLNY-- Declare the return variable hereRETURN @STR
ENDGO
/****** Object: UserDefinedFunction [dbo].[X9_DQSY] Script Date: 2024-10-11 20:25:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 昆山华岳软件有限公司 岳国军
-- Create date: 2009-03-07
-- Script Date: 2020-08-04
-- Description: 当前上一业务期间 ,上月
-- =============================================
CREATE FUNCTION [dbo].[X9_DQSY]()RETURNS CHAR(6)
AS
BEGINDeclare @DY CHAR(6),@SY CHAR(6);SELECT @DY=MAX(NY) FROM MLNY;SELECT @SY=MAX(NY) FROM MLNY WHERE NY<>@DY;RETURN @SY
ENDGO
/****** Object: UserDefinedFunction [dbo].[X9_GRSDS] Script Date: 2024-10-11 20:25:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 昆山华岳软件有限公司 岳国军
-- Create date: 2014-06-18
-- Script Date: 2020-06-19
-- Description: 个人所得税计算
-- =============================================
CREATE FUNCTION [dbo].[X9_GRSDS]
(@JSZE DECIMAL(18,2)) --计税总额
RETURNS DECIMAL(18,2)
AS
BEGIN--2011年9月1日起调整后,也就是2012年实行的7级超额累进个人所得税税率表--应纳个人所得税税额= 应纳税所得额× 适用税率- 速算扣除数--扣除标准3500元/月(2011年9月1日起正式执行)(工资、薪金所得适用)--个税免征额3500元 (工资薪金所得适用)--2019年新改 起征点为5000DECLARE @JSCE DECIMAL(18,2),@YJSE DECIMAL(18,2);SET @JSCE=@JSZE-5000.00;IF @JSCE<=0BEGINSET @YJSE=0;RETURN @YJSE;ENDIF @JSCE<=3000BEGINSET @YJSE=@JSCE*0.03;RETURN @YJSE;ENDIF @JSCE<=12000BEGINSET @YJSE=@JSCE*0.10-210;RETURN @YJSE;ENDIF @JSCE<=25000BEGINSET @YJSE=@JSCE*0.20-1410;RETURN @YJSE;ENDIF @JSCE<=35000BEGINSET @YJSE=@JSCE*0.25-2660;RETURN @YJSE;ENDIF @JSCE<=55000BEGINSET @YJSE=@JSCE*0.30-4410;RETURN @YJSE;ENDIF @JSCE<=80000BEGINSET @YJSE=@JSCE*0.35-7160;RETURN @YJSE;END--大于80000SET @YJSE=@JSCE*0.45-15160;RETURN @YJSE;
ENDGO
/****** Object: UserDefinedFunction [dbo].[X9_INSTR] Script Date: 2024-10-11 20:25:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 昆山华岳软件有限公司 岳国军
-- Create date: 2009-03-07
-- Script Date: 2020-05-28
-- Description: 子串位置函数
-- =============================================
CREATE FUNCTION [dbo].[X9_INSTR]
(@STR NVARCHAR(MAX), --字符串@CH NVARCHAR(20), --查找的子串@CS INT --出现次数
)
RETURNS INT
AS
BEGIN-- Declare the return variable hereDECLARE @C NVARCHAR(20),@L INT,@I INT,@S INT; --单个字符,总长,循环变量,找到的位置\SET @L=LEN(@STR);SET @S=0;SET @I=0;WHILE @I<@L AND @S<@CSBEGINSET @I=@I+1;IF SUBSTRING(@STR,@I,LEN(@CH))=@CHSET @S=@S+1;ENDRETURN @I
ENDGO
/****** Object: UserDefinedFunction [dbo].[X9_RQSW] Script Date: 2024-10-11 20:25:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 昆山华岳软件有限公司 岳国军
-- Create date: 2022-05-17
-- Script Date: 2022-05-17
-- Description: 月初月末时间,用于按单据登记日期排序明细账项
-- =============================================
CREATE FUNCTION [dbo].[X9_RQSW]
(@NY CHAR(6),@SW BIT --0月初时间,1月末时间(22时)
)
RETURNS DATETIME
AS
BEGINDeclare @ND CHAR(4),@YF CHAR(2),@NEXT CHAR(6),@RQ DATETIME;SELECT @ND=LEFT(@NY,4),@YF=RIGHT(@NY,2);IF @SW=0SET @RQ=@ND+'-'+@YF+'-01';ELSEBEGINSELECT @NEXT = CASE WHEN RIGHT(@NY,2)='12' THEN CONVERT(VARCHAR(4),CONVERT(INT,LEFT(@NY,4))+1)+'01' ELSE LEFT(@NY,4)+RIGHT('0'+CONVERT(VARCHAR(2),CONVERT(INT,RIGHT(@NY,2))+1),2) END;SELECT @ND=LEFT(@NEXT,4),@YF=RIGHT(@NEXT,2);SET @RQ=@ND+'-'+@YF+'-01';SET @RQ=DATEADD(hour,-2,@RQ);ENDRETURN @RQ;
ENDGO
/****** Object: UserDefinedFunction [dbo].[X9_TJSPLIT] Script Date: 2024-10-11 20:25:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 昆山华岳软件有限公司 岳国军
-- Create date: 2011-10-15
-- Script Date: 2022-04-22
-- Description: 输入参照字符串解析函数
-- =============================================
CREATE FUNCTION [dbo].[X9_TJSPLIT]
(@TJMC VARCHAR(30), --条件名称@TJCS VARCHAR(500) --完全字符串
)
RETURNS NVARCHAR(100) --条件值
AS
BEGINDECLARE @I INT,@J INT,@ZF NVARCHAR(1),@TJTEXT NVARCHAR(100) ;SET @J=1;SET @ZF='';SET @TJTEXT='';SET @I=CHARINDEX(@TJMC,@TJCS)IF @I>0BEGINWHILE @J > 0BEGINSET @ZF=SUBSTRING(@TJCS,@I+LEN(@TJMC)+@J,1)IF @ZF<>'@'BEGINSET @TJTEXT=@TJTEXT+@ZF;SET @J=@J+1;ENDELSEBEGINSET @J=0;ENDENDENDRETURN @TJTEXT
ENDGO
/****** Object: UserDefinedFunction [dbo].[X9_WBJE] Script Date: 2024-10-11 20:25:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 昆山华岳软件有限公司
-- Create date: 2012-12-20
-- Script Date: 2022-06-24
-- Description: 外币金额计算
-- =============================================
CREATE FUNCTION [dbo].[X9_WBJE]
(@WBMC NVARCHAR(20),@BBSL DECIMAL(16,2)) --业务日期 --外币名称 --本币数量
RETURNS DECIMAL(16,4)
AS
BEGINDeclare @WBJE DECIMAL(16,4);Declare @PJRQ VARCHAR(25);Declare @WBSTR VARCHAR(20);SET @WBJE=0;IF @WBMC='人民币(RMB)' OR @WBMC=''BEGINSET @WBJE=0;ENDELSEBEGINIF @BBSL IS NULL SET @WBJE=0SELECT TOP 1 @WBJE=(@BBSL*100/JZHL) FROM MLWBPJ WHERE HBMC=@WBMC ORDER BY RQ DESC;ENDRETURN @WBJE;ENDGO
/****** Object: UserDefinedFunction [dbo].[X9_YHBM] Script Date: 2024-10-11 20:25:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO-- =============================================
-- Author: 昆山华岳软件有限公司
-- Create date: 2009-03-07
-- Description: 用户所属部门
-- =============================================
CREATE FUNCTION [dbo].[X9_YHBM]
(@DQYH VARCHAR(30))
RETURNS NVARCHAR(50)
AS
BEGINDeclare @STR NVARCHAR(50)SET @STR='';SELECT @STR=YHBM FROM X9_GNYHRETURN @STR
ENDGO
/****** Object: UserDefinedFunction [dbo].[X9_YHDW] Script Date: 2024-10-11 20:25:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO-- =============================================
-- Author: 昆山华岳软件有限公司
-- Create date: 2009-03-07
-- Description: 用户所属单位
-- =============================================
CREATE FUNCTION [dbo].[X9_YHDW]
(@DQYH VARCHAR(30))
RETURNS NVARCHAR(50)
AS
BEGINDeclare @STR NVARCHAR(50)SET @STR='';SELECT @STR=YHDW FROM X9_GNYHRETURN @STR
END