当前位置： 首页 > article >正文

青少年编程与数学 02-002 Sql Server 数据库应用 19课题、数据库设计实例

article 2024/10/30 6:40:31

青少年编程与数学 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)
AS
	SET NOCOUNT ON;
BEGIN TRY
	DECLARE	@SJDH VARCHAR(30),@SYSJ VARCHAR(30),@DQDH VARCHAR(30),@DCMX BIT;
	--部门目录
	IF @MLMC='MLBM'
		BEGIN
			DECLARE 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=0
				BEGIN
					IF @SYSJ =@DQDH
						BEGIN
							IF @DCMX<>0
								UPDATE MLBM SET DCMX=0 WHERE BMDH=@DQDH;
						END
					ELSE
						BEGIN 
							IF @DCMX<>1
								UPDATE MLBM SET DCMX=1 WHERE BMDH=@DQDH;
						END
					SET @SYSJ=@SJDH;
					FETCH NEXT FROM MLBM_cursor INTO @DQDH,@SJDH,@DCMX;
				END
			CLOSE MLBM_cursor;
			DEALLOCATE MLBM_cursor	;
			RETURN;
		END
		
	--客户目录
	IF @MLMC='MLWL'
		BEGIN
			DECLARE 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,@DCMX
			WHILE @@FETCH_STATUS=0
			BEGIN
				IF @SYSJ = @DQDH
					BEGIN
						IF @DCMX<>0
							UPDATE MLWL SET DCMX=0 WHERE WLDH=@DQDH;
					END
				ELSE
					BEGIN 
						IF @DCMX<>1
							UPDATE MLWL SET DCMX=1 WHERE WLDH=@DQDH;
					END
				SET @SYSJ=@SJDH;
				FETCH NEXT FROM MLWL_cursor	INTO @DQDH,@SJDH,@DCMX
			END
			CLOSE MLWL_cursor;
			DEALLOCATE MLWL_cursor	;
			RETURN;
		END
	--会计科目
	IF @MLMC='MLKM'
		BEGIN
			--声明游标MLKM_cursor
			DECLARE 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,@DCMX
			WHILE @@FETCH_STATUS=0
			BEGIN
				IF @SYSJ = @DQDH
					BEGIN
						IF @DCMX<>0
							UPDATE MLKM SET DCMX=0 WHERE KMDH=@DQDH;
					END
				ELSE
					BEGIN 
						IF @DCMX<>1
							UPDATE MLKM SET DCMX=1 WHERE KMDH=@DQDH;
					END
				SET @SYSJ=@SJDH;
				FETCH NEXT FROM MLKM_cursor	INTO @DQDH,@SJDH,@DCMX
			END
			CLOSE MLKM_cursor;
			DEALLOCATE MLKM_cursor;
			UPDATE MLKM SET DLCX=0 WHERE DCMX=1 AND DLCX=1;
			RETURN;
		END
	--资产分类
	IF @MLMC='MLZCKP'
		BEGIN
			--声明游标MLZCFL_cursor
			DECLARE 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,@DCMX
			WHILE @@FETCH_STATUS=0
			BEGIN
				IF @SYSJ = @DQDH
					BEGIN
						IF @DCMX<>0
							UPDATE MLZCKP SET DCMX=0 WHERE ZCBH=@DQDH;
					END
				ELSE
					BEGIN 
						IF @DCMX<>1
							UPDATE MLZCKP SET DCMX=1 WHERE ZCBH=@DQDH;
					END
				SET @SYSJ=@SJDH;
				FETCH NEXT FROM MLZCFL_cursor INTO @DQDH,@SJDH,@DCMX
			END
			CLOSE MLZCFL_cursor;
			DEALLOCATE MLZCFL_cursor	;
			RETURN;
		END
END TRY
BEGIN CATCH
  	IF CURSOR_STATUS('LOCAL','MLCH_cursor')>=0 
		BEGIN
			CLOSE 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 TRY
	DECLARE @DJJG NVARCHAR(MAX);		--登记结果
	DECLARE @DYJG INT;								--调用结果
	DECLARE @SFDJ BIT;
	IF @DJMC='收款单' OR @DJMC='付款单' 
		BEGIN
			SELECT @SFDJ=CASE WHEN DJR<>'' OR SHR='' THEN 1 ELSE 0 END FROM DJCNO WHERE OID=@DJOID;
			IF @SFDJ=1
				BEGIN
					SELECT @DJMC + @DJOID +'已经登记或未经审核';			
				END
			ELSE
				BEGIN
					SET @DYJG=0;
					EXEC @DYJG=DBO.X9_DJDJ_06CN  @DQYH,@DJMC,@DJOID; 
					IF @DYJG=1
						BEGIN
							SELECT @DQYH;
						END	
					ELSE
						SELECT @DJMC + @DJOID +'未能完成登记';			
				END
			RETURN;
		END
	IF @DJMC='记账凭证'
		BEGIN
			SELECT @SFDJ=CASE WHEN DJR<>'' THEN 1 ELSE 0 END FROM DJJZPZO WHERE OID=@DJOID;
			IF @SFDJ=1
				BEGIN
					--反记账
					SET @DYJG=0;
					EXEC @DYJG=DBO.X9_DJDJ_05JZFX  @DQYH,@DJMC,@DJOID; 
					IF @DYJG=1
						BEGIN
							SELECT @DQYH;
						END	
					ELSE
						SELECT @DJMC + @DJOID +'未能完成反记账';			
				END
			ELSE
				BEGIN
					SET @DYJG=0;
					EXEC @DYJG=DBO.X9_DJDJ_05JZ  @DQYH,@DJMC,@DJOID; 
					IF @DYJG=1
						BEGIN
							SELECT @DQYH;
						END	
					ELSE
						SELECT @DJMC + @DJOID +'未能完成记账操作';			
				END
			RETURN;
		END
END TRY
BEGIN CATCH
	DECLARE @EMsg NVARCHAR(4000);
	SELECT @EMsg = ERROR_MESSAGE();
	SELECT @DJMC + @DJOID +'操作时出错,' + CHAR(13) + CHAR(10) + @EMsg  ;			
END CATCH


GO
/****** 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 TRY
	SET 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_cursor
		DECLARE JZPZ_cursor CURSOR LOCAL FOR
			SELECT     KMDH,WLDW,WLRY,HSBM,HBMC,JFJE, DFJE, SLWB,MID
			FROM       DJJZPZM
			WHERE     (OID = @DJOID) AND SFSH=1 
			ORDER BY KMDH,WLDW,WLRY,HSBM,HBMC;
		OPEN JZPZ_cursor;
		FETCH FROM JZPZ_cursor
			INTO @KMDH,@WLDW,@WLRY,@HSBM,@HBMC,@JFJE,@DFJE,@SLWB,@MID;
		WHILE @@FETCH_STATUS=0
			BEGIN
				--登记会计总账
				IF EXISTS(SELECT * FROM UTZZKJ WHERE NY=@NY AND KMDH=@KMDH AND WLDW=@WLDW 
						AND WLRY=@WLRY AND HSBM=@HSBM AND HBMC=@HBMC)
					BEGIN
						IF @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;
								ELSE
									UPDATE 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;
							END
						UPDATE 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 UTZZKJ
							WHERE 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;
					END
				ELSE
					BEGIN
						IF @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)
						ELSE
							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 ,0,@SLWB,0-@SLWB,0,0,@DFJE,0-@DFJE)
					END
				FETCH NEXT FROM JZPZ_cursor
				INTO @KMDH,@WLDW,@WLRY,@HSBM,@HBMC,@JFJE,@DFJE,@SLWB,@MID;
			END
		CLOSE 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=@SY
			BEGIN
				--更新当月会计总账期初数及期末数,即可以登记到上月
				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.JEYM
					FROM 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-JEDF
					WHERE 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, ZZXH
						FROM   UTZZKJ AS SYZZ
						WHERE     (NY = @SY) AND (KMDH+HSBM+WLDW+WLRY+HBMC) NOT IN
							(SELECT KMDH+HSBM+WLDW+WLRY+HBMC FROM UTZZKJ AS DYZZ WHERE NY=@NY);
			END
		COMMIT TRANSACTION;
   		RETURN 1;
	END
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
		BEGIN
			IF CURSOR_STATUS('LOCAL','JZPZ_cursor')>=0 
				BEGIN
					CLOSE JZPZ_cursor;
					DEALLOCATE JZPZ_cursor	;
				END 
			ROLLBACK TRANSACTION;
		END
	INSERT [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 TRY
	SET NOCOUNT ON;
	BEGIN
		BEGIN 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_cursor
		DECLARE JZPZFX_cursor CURSOR LOCAL FOR
			SELECT     KMDH,WLDW,WLRY,HSBM,HBMC,JFJE, DFJE, SLWB,MID
			FROM       DJJZPZM
			WHERE     (OID = @DJOID)
			ORDER BY KMDH,WLDW,WLRY,HSBM,HBMC;
		OPEN JZPZFX_cursor;
		FETCH FROM JZPZFX_cursor
			INTO @KMDH,@WLDW,@WLRY,@HSBM,@HBMC,@JFJE,@DFJE,@SLWB,@MID;
		WHILE @@FETCH_STATUS=0
			BEGIN
				--登记会计总账 反向
				IF EXISTS(SELECT * FROM UTZZKJ WHERE NY=@NY AND KMDH=@KMDH AND 
							WLDW=@WLDW AND WLRY=@WLRY AND HSBM=@HSBM AND HBMC=@HBMC)
					BEGIN
						IF @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;
								ELSE
									UPDATE 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;
							END
						UPDATE 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 UTZZKJ
							WHERE 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;
					END
				FETCH NEXT FROM JZPZFX_cursor
				INTO @KMDH,@WLDW,@WLRY,@HSBM,@HBMC,@JFJE,@DFJE,@SLWB,@MID;
			END
		CLOSE 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()
			BEGIN
				DECLARE @SYNY CHAR(6);
				DECLARE @DQNY CHAR(6);
				SET @SYNY=@NY;
				DECLARE KJNY_cursor CURSOR LOCAL FOR
					SELECT  NY
					FROM    MLNY
					WHERE   NY>@NY;
				OPEN KJNY_cursor;
				FETCH FROM KJNY_cursor INTO @DQNY;
				WHILE @@FETCH_STATUS=0
					BEGIN
						--更新UTZZKJ
						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 = @SYNY))
						UPDATE UTZZKJ SET SLYC=SYZZ.SLYM,JEYC=SYZZ.JEYM
							FROM 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-JEDF
							WHERE 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, ZZXH
								FROM   UTZZKJ AS SYZZ
								WHERE     (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;
					END
				CLOSE KJNY_cursor;
				DEALLOCATE KJNY_cursor	;
			END 
		COMMIT TRANSACTION;
   		RETURN 1;
	END
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
		BEGIN
			IF CURSOR_STATUS('LOCAL','JZPZFX_cursor')>=0 
				BEGIN
					CLOSE JZPZFX_cursor;
					DEALLOCATE JZPZFX_cursor	;
				END 
			ROLLBACK TRANSACTION;
		END
	INSERT [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;
END

GO
/****** 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 TRY
	IF @SXZD ='KMDH' 
		BEGIN
			SELECT * FROM MLKM WHERE KMDH=@SXSTR ;
			RETURN;
		END 
END TRY
BEGIN CATCH
		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_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='记账凭证'
			BEGIN
				IF EXISTS(SELECT OID FROM DJJZPZM PZ INNER JOIN MLKM KM ON KM.KMDH=PZ.KMDH 
								WHERE KM.DCMX=0 AND PZ.OID=@DJOID)
					BEGIN
						SELECT '使用了非明细科目，请检查单据的正确性';
						RETURN;
					END
				IF 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)
					BEGIN
						SELECT '辅助核算中的往来核算或部门核算项目填写有误，请检查单据的正确性';
						RETURN;
					END
				IF 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)
					BEGIN
						SELECT '辅助核算中的数量或外币核算项目填写有误，请检查单据的正确性';
						RETURN;
					END
				UPDATE 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;
				END
		SELECT @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 TRY
	SET NOCOUNT ON;
	DECLARE @MLID INT;
	DECLARE @SFFL BIT;
	SET @MLID =0;
	IF @FMEMO='授权范围'
		BEGIN
			SELECT '角色' as  授权范围 
			UNION ALL
			SELECT '上级' as  授权范围 
			UNION ALL
			SELECT '下级' as  授权范围 
			UNION ALL
			SELECT '同级' as  授权范围 
			UNION ALL
			SELECT '发起人' as  授权范围 
			RETURN;
		END
	IF @FMEMO='授权角色'
		BEGIN
			SELECT YHJS FROM X9_GNYHJS;
			RETURN;
		END
	IF @FMEMO='账项类别' 
		BEGIN
			SELECT '会计分类总账' as  账项类别 
			UNION ALL
			SELECT '会计数量总账' as  账项类别 
			UNION ALL
			SELECT '会计外币总账' as  账项类别 
			UNION ALL
			SELECT '会计个人往来总账' as  账项类别 
			UNION ALL
			SELECT '会计部门总账' as  账项类别 
			RETURN;
		END	
	IF @FMEMO='明细类别' 
		BEGIN
			SELECT '会计分类明细账' as  明细类别 
			UNION ALL
			SELECT '会计数量明细账' as  明细类别 
			UNION ALL
			SELECT '会计外币明细账' as  明细类别 
			UNION ALL
			SELECT '会计个人往来明细账' as  明细类别 
			UNION ALL
			SELECT '会计部门明细账' as  明细类别 
			RETURN;
		END	

	IF @FMEMO='业务期间' or  @FMEMO='会计期间'
		BEGIN
			SELECT TOP 12 NY  from MLNY ORDER BY NY DESC;
			RETURN;
		END
	IF @FMEMO='会计处理期间'
		BEGIN
			SELECT TOP 2 NY  from MLNY ORDER BY NY DESC;
			RETURN;
		END
	IF @FMEMO='开始年月'
		BEGIN
			SELECT TOP 12 NY  from MLNY ORDER BY NY
			RETURN;
		END
	IF @FMEMO='结束年月'
		BEGIN
			SELECT TOP 12 NY  from MLNY ORDER BY NY DESC;
			RETURN;
		END
	iF @FMEMO='年月'
		BEGIN
			SELECT  NY  from MLNY ORDER BY NY
			RETURN;
		END
	IF @FMEMO='所属部门'
		BEGIN
			SELECT BMMC from MLBM ORDER BY BMDH;
			RETURN;
		END
	IF @FMEMO='用户角色'
		BEGIN
			SELECT YHJS from X9_GNYHJS ORDER BY JSBH;
			RETURN;
		END
	IF @FMEMO='条件输入控件' 
		BEGIN
			SELECT 'ButtonEdit' as   条件输入控件
			UNION ALL
			SELECT 'CheckEdit' as  条件输入控件 
			UNION ALL
			SELECT 'DateEdit' as  条件输入控件 
			UNION ALL
			SELECT 'ListEdit' as  条件输入控件 
			UNION ALL
			SELECT 'TextEdit' as  条件输入控件 
			UNION ALL
			SELECT 'TextRead' as  条件输入控件 
			RETURN;
		END	
	IF @FMEMO='结算外币' 
		BEGIN
			SELECT '' as 结算外币
			UNION ALL
			SELECT HBMC AS 结算外币 FROM MLWB;
			RETURN;
		END	
	RETURN
END TRY
BEGIN CATCH
	DECLARE @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='客户代号' 
		BEGIN
			SELECT WLDH AS 客户代号,WLMC AS 客户名称, JSWB AS 结算外币 FROM MLWL WHERE WLDH<'50' AND 
					(WLDH+WLMC+DZ+DH+LXR+ZJM) LIKE '%'+@FTEXT+'%'  AND DCMX=1;
			RETURN;
		END
	IF @FMEMO='供应商代号' 
		BEGIN
			SELECT WLDH AS 供应商代号,WLMC AS 供应商名称, JSWB AS 结算外币 FROM MLWL WHERE WLDH>'50' AND 
					(WLDH+WLMC+DZ+DH+LXR+ZJM) LIKE '%'+@FTEXT+'%'  AND DCMX=1;
			RETURN;
		END
	IF @FMEMO='供货单位' 
		BEGIN
			SELECT WLDH+'_'+WLMC AS 供货单位, JSWB AS 结算外币 FROM MLWL WHERE WLDH>'50' AND 
					(WLDH+WLMC+DZ+DH+LXR+ZJM) LIKE '%'+@FTEXT+'%'  AND DCMX=1;
			RETURN;
		END
	IF @FMEMO ='项目代号'
		BEGIN
			SELECT   XMDH AS  项目代号,  XMMC AS 项目名称, XMQC AS 项目全称, XMLX 项目类型, XMJLDW 项目计量单位
			FROM      MLCBXM    WHERE (XMDH+ZJM+XMMC LIKE '%'+@FTEXT+'%' ) 
			RETURN;
		END
	------------------------------------------------------------------
	---公共参考信息，不区分功能
	------------------------------------------------------------------
	IF @FMEMO='部门全称'
		BEGIN
			SELECT BMQC AS 部门全称 FROM MLBM WHERE BMMC+ZJM LIKE '%'+@FTEXT+'%' ORDER BY BMDH;
			RETURN;
		END
	IF @FMEMO='所属部门'
		BEGIN
			SELECT BMMC AS 所属部门 FROM MLBM WHERE BMMC+ZJM LIKE '%'+@FTEXT+'%' ORDER BY BMDH;
			RETURN;
		END
	IF @FMEMO='所属单位'
		BEGIN
			SELECT WLMC AS 所属单位 FROM MLWL WHERE DCMX=1 AND WLMC+ZJM LIKE '%'+@FTEXT+'%';
			RETURN;
		END

	IF @FMEMO='开始年月' 
		BEGIN
			SELECT NY AS 开始年月 FROM MLNY ORDER BY NY DESC;
			RETURN;
		END	
	IF @FMEMO='结束年月' 
		BEGIN
			SELECT NY AS 结束年月 FROM MLNY ORDER BY NY DESC;
			RETURN;
		END	
	IF @FMEMO='年月' 
		BEGIN
			SELECT NY AS 年月 FROM MLNY ORDER BY NY DESC;
			RETURN;
		END	
	IF @FMEMO='业务期间' 
		BEGIN
			SELECT NY AS 业务期间 FROM MLNY ORDER BY NY DESC;
			RETURN;
		END	
	IF (@GNMC='记账凭证填制') AND (@FMEMO='往来单位')
		BEGIN
			SELECT WLDH+'_'+WLMC AS 往来单位, JSWB AS 结算外币 FROM MLWL
				WHERE  (WLDH+'_'+WLMC+ZJM LIKE  '%'+@FTEXT+'%') AND DCMX=1; 
		END
	IF (@GNMC='记账凭证填制') AND (@FMEMO='往来人员')
		BEGIN
			SELECT LTRIM(STR(RYBH))+'_'+RYXM AS 往来人员 FROM MLBMRY 
				WHERE (STR(RYBH)+'_'+RYXM+ZJM LIKE  '%'+@FTEXT+'%');
		END 
	IF (@GNMC='记账凭证填制') AND (@FMEMO='核算部门')
		BEGIN
			SELECT BMDH+'_'+BMMC AS 核算部门 FROM MLBM
				WHERE (BMDH+'_'+BMMC+ZJM LIKE  '%'+@FTEXT+'%') ; 
		END
	IF @FMEMO='现金流向'
		BEGIN
			SELECT XMBH+'_'+LTRIM(XMMC) AS 现金流向 FROM MLXJLL 
				WHERE  LEN(XMBH)=4 ORDER BY XMBH;
		END
	IF @FMEMO='收款银行' 
		BEGIN
			SELECT YHMC AS 收款银行,ZHHM AS 收款账号 FROM MLYHZH ORDER BY ZHDH;
			RETURN;
		END	
	IF @FMEMO='付款银行' 
		BEGIN
			SELECT YHMC AS 付款银行,ZHHM AS 付款账号 FROM MLYHZH ORDER BY ZHDH;
			RETURN;
		END	
	DECLARE @BMDH VARCHAR(12);
	IF @FMEMO='部门代号' 
		BEGIN
			SELECT BMDH AS 部门代号,BMMC AS 部门名称
				FROM MLBM  
				WHERE (BMDH+BMMC+ZJM LIKE  '%'+@FTEXT+'%')   
			RETURN;
		END
	IF @FMEMO='部门名称' 
		BEGIN
			SELECT BMMC 部门名称, BMDH  AS 部门代号 FROM MLBM WHERE (BMMC+ZJM  LIKE '%'+@FTEXT+'%' ) ;
			RETURN;
		END	
	--用于多选
	declare @xd bit;
	set @xd=0;
	IF @FMEMO='所属行业' 
		BEGIN
			--多选示例 原为comboedit
			SELECT @BMDH =dbo.x9_tjsplit('BMDH',@TJSTR);
			SELECT @xd as 选定 ,'制造业' AS 所属行业 UNION ALL
			SELECT @xd as 选定 ,'商品流通业' AS 所属行业 UNION ALL
			SELECT @xd as 选定 ,'电子商务' AS 所属行业 UNION ALL
			SELECT @xd as 选定 ,'服务业' AS 所属行业; 
		END
	IF @FMEMO='规格系列' 
		BEGIN
			SELECT XH1 AS 规格系列 FROM MLGG
				WHERE (XH1+ZJM LIKE '%'+@FTEXT+'%' ) and JC=1 ;
			RETURN;
		END	
	IF @FMEMO='产品代号'
		BEGIN
			SELECT CHDH AS 产品代号 ,CHMC AS  产品名称,JLDW AS 计量单位 
				FROM MLCH 
				WHERE (CHDH+ZJM+CHMC LIKE '%'+@FTEXT+'%' )  AND DCMX=1 ;
			RETURN;
		END
	DECLARE @PPZX VARCHAR(50);
	IF @FMEMO='人员编号'
		BEGIN
			SELECT @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;
		END
	IF @FMEMO='科目代号' 
		BEGIN
			SELECT KMDH AS  科目代号, KMQC AS  科目全称,HBMC AS 单位或币名
			FROM MLKM  WHERE (KMDH+KMQC+ZJM LIKE  '%'+@FTEXT+'%')  AND DCMX=1 ;
			RETURN;
		END
	IF @FMEMO='多栏科目代号' 
		BEGIN
			SELECT KMDH AS  多栏科目代号, KMQC AS  多栏科目全称 FROM MLKM WHERE DLCX=1 AND DCMX=0;
			RETURN;
		END
	IF  @FMEMO='出纳科目代号' 
		BEGIN
			SELECT     KMDH AS  出纳科目代号, KMQC AS  出纳科目名称
			FROM MLKM WHERE (KMDH+KMQC+ZJM LIKE  '%'+@FTEXT+'%')  AND DCMX=1 AND CNHS=1;
			RETURN;
		END
	IF  @FMEMO='对方科目代号' 
		BEGIN
			SELECT     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='往来单位'
		BEGIN
			DECLARE @FG INT;
			SET @FG=0
			SET @FG=CHARINDEX('_',@FTEXT);
			IF @FG>0
				BEGIN
					SET @FTEXT=SUBSTRING(@FTEXT,@FG+1,LEN(@FTEXT)-@FG);
				END
			IF (@GNMC='采购订单填制' or @GNMC='采购入库单填制' OR @GNMC='质量检验单填制'  
					OR @GNMC='委外入库单填制' OR @GNMC='采购计划单填制'  OR @GNMC='委外出库单填制' 
					OR @GNMC='采购价格调整表' OR @GNMC='委外价格调整表'  OR @GNMC='委外订单填制')
				BEGIN
					SELECT 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;
				END
			IF (@GNMC='销售订单填制' or @GNMC='销售出库单填制' or @GNMC='生产计划单填制'or @GNMC='销售价格调整表')
				BEGIN
					SELECT 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;
				END
			IF (@GNMC='收款单填制' OR @GNMC='应收账款总账查询' OR @GNMC='应收账款明细账查询')
				BEGIN
					SELECT 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;
				END
			IF (@GNMC='付款单填制'  OR @GNMC='应付账款总账查询' OR @GNMC='应付账款明细账查询')
				BEGIN
					SELECT 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;
				END
		END	
	IF @FMEMO ='功能编号' AND @GNMC='查询功能显示设置'
		BEGIN
			SELECT GNBH,GNMC FROM X9_GN WHERE GNBH+GNMC LIKE '%'+@FTEXT+'%' AND (GNLY='查询输入' OR GNLY='汇总查询') ORDER BY GNBH;
			RETURN;
		END
END TRY
BEGIN CATCH
		THROW;
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 TRY
		IF @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.SFYC
						FROM YH CROSS JOIN X9_GN GN
						WHERE 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.SFYC
					FROM 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='功能列表定义'
			BEGIN
				DELETE 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 MKMC
					FROM 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 '功能列表定义功能提交后处理,已完成帮助主题的更新。';
				RETURN
			END
		IF @GNNAME='客户目录'
			BEGIN
				EXEC X9_REZJM @TBNAME;
				EXEC X9_DCMX @TBNAME;
				UPDATE MLWL SET ZKL=0 WHERE ZKL<0 OR ZKL>100;
				SELECT '客户目录提交后处理已完成';
				RETURN
			END
	IF @GNNAME='供应商目录'
			BEGIN
				EXEC X9_REZJM @TBNAME;
				EXEC X9_DCMX @TBNAME;
				SELECT '供应商目录提交后处理已完成';
				RETURN
			END 
		IF @GNNAME='部门目录'
			BEGIN
				EXEC X9_REZJM @TBNAME;
				EXEC X9_DCMX @TBNAME;
				SELECT '部门目录提交后处理已完成'
				RETURN
			END  
		IF @GNNAME='会计科目'
			BEGIN
				EXEC X9_REZJM @TBNAME;
				EXEC X9_DCMX @TBNAME;
				SELECT '会计科目提交后处理已完成'
				RETURN
			END  
		IF @GNNAME='固定资产卡片管理'
			BEGIN
				EXEC X9_REZJM @TBNAME;
				EXEC X9_DCMX @TBNAME;
				SELECT '固定资产档案提交后处理已完成'
				RETURN
			END  
		IF @GNNAME='数据字典'
			BEGIN
				SELECT '数据字典提交后处理已完成'
				RETURN
			END 
	END TRY
	BEGIN CATCH
		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_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
BEGIN
	BEGIN 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 TRY
	BEGIN CATCH
		DECLARE @ErrorMessage NVARCHAR(4000),@ErrorState INT;
		SELECT 	@ErrorMessage = ERROR_MESSAGE(),@ErrorState = ERROR_STATE();
		THROW 410,@ErrorMessage,@ErrorState;
	END CATCH; 
END

GO
/****** 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 )
AS
	IF @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 JOIN
							  UTZZKJ 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.KMDH
		WHERE     (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.HBMC
		HAVING      (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))
AS
	DELETE UTMXKJ WHERE PCXH=@PCXH
	DECLARE @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 FOR
		SELECT 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=0
	BEGIN
		SET @DLKMMC=@DLKMMC+'['+@MXKMMC+'],'
		FETCH NEXT FROM UTDLKJKM_cursor
			INTO @MXKMDH,@MXKMMC;
	END
	CLOSE UTDLKJKM_cursor;
	DEALLOCATE UTDLKJKM_cursor	;
	IF @DLKMMC='' 
		BEGIN
			SELECT '没有有效的多栏数据'
			RETURN;
		END
	SET @DLKMMC=LEFT(@DLKMMC,LEN(@DLKMMC)-1);
	DECLARE	@NY CHAR(6),@SYNY CHAR(6)
	SET @NY=@KSNY;
	WHILE @NY<=@JSNY
		BEGIN
			--期初余额
			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 ZZXH
				FROM         UTZZKJ INNER JOIN
								  MLKM ON UTZZKJ.KMDH = MLKM.KMDH
				WHERE     (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.ZZXH
				FROM         DJJZPZO INNER JOIN
								  DJJZPZM ON DJJZPZO.OID = DJJZPZM.OID INNER JOIN
								  MLKM ON DJJZPZM.KMDH = MLKM.KMDH
				WHERE     (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 ZZXH
				FROM  UTZZKJ INNER JOIN
					  MLKM ON UTZZKJ.KMDH = MLKM.KMDH
				WHERE     (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 FOR
		SELECT     ID,OID, JFSL, DFSL,YESL,JFJE,DFJE,YEJE
		FROM       UTMXKJ
		WHERE     (PCXH = @PCXH)
		ORDER BY ID
	OPEN UTDLMXKJ_cursor;
	FETCH FROM UTDLMXKJ_cursor
		INTO @ID,@OID, @JFSL, @DFSL, @YESL, @JFJE, @DFJE, @YEJE;
	WHILE @@FETCH_STATUS=0
	BEGIN
		IF @OID<>''
			BEGIN
				IF @YEFX='借'
					BEGIN
						SET @UYESL=@UYESL+@JFSL-@DFSL;
						SET @UYEJE=@UYEJE+@JFJE-@DFJE;
						UPDATE UTMXKJ SET YESL=@UYESL,YEJE=@UYEJE,ZKJE=@JFJE-@DFJE
							WHERE PCXH=@PCXH AND ID=@ID;
					END
				ELSE
					BEGIN
						SET @UYESL=@UYESL+@DFSL-@JFSL;
						SET @UYEJE=@UYEJE+@DFJE-@JFJE;
						UPDATE UTMXKJ SET YESL=@UYESL,YEJE=@UYEJE,ZKJE=@DFJE-@JFJE
							WHERE PCXH=@PCXH AND ID=@ID;
					END
			END
		ELSE
			BEGIN
				SET @UYESL=@YESL;
				SET @UYEJE=@YEJE;
			END
		FETCH NEXT FROM UTDLMXKJ_cursor
		INTO @ID,@OID, @JFSL, @DFSL, @YESL, @JFJE, @DFJE, @YEJE;
	END
	CLOSE 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 @CXSQL
	EXEC (@CXSQL);
	RETURN;	
	--SELECT * FROM UTMXKJ
	--SELECT ZY,[201301],[201312] FROM UTMXKJ   
	--	PIVOT(sum(JFJE) FOR NY IN ([201301],[201312])) T


GO
/****** 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)
AS
	DELETE UTMXKJ WHERE PCXH=@PCXH
	--科目循环
	DECLARE	@NY CHAR(6),@KMDH VARCHAR(12),@KMQC NVARCHAR(100),@SYNY CHAR(6),@YEFX NVARCHAR(2);
	DECLARE UTMXKJKM_cursor CURSOR FOR
		SELECT KMDH,KMQC,YEFX FROM MLKM 
		WHERE DCMX=1 AND KMDH LIKE @PPDH +'%' ORDER BY KMDH
	OPEN UTMXKJKM_cursor;
	FETCH FROM UTMXKJKM_cursor
		INTO @KMDH,@KMQC,@YEFX;
	WHILE @@FETCH_STATUS=0
		BEGIN
			SET @NY=@KSNY
			WHILE @NY<=@JSNY
				BEGIN
					--期初余额
					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 ZZXH
						FROM         UTZZKJ INNER JOIN
										  MLKM ON UTZZKJ.KMDH = MLKM.KMDH
						WHERE     (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.ZZXH
						FROM         DJJZPZO INNER JOIN
										  DJJZPZM ON DJJZPZO.OID = DJJZPZM.OID INNER JOIN
										  MLKM ON DJJZPZM.KMDH = MLKM.KMDH
						WHERE     (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 ZZXH
						FROM  UTZZKJ INNER JOIN
							  MLKM ON UTZZKJ.KMDH = MLKM.KMDH
						WHERE     (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
			FETCH NEXT FROM UTMXKJKM_cursor
				INTO @KMDH,@KMQC,@YEFX;
		END
	CLOSE 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 FOR
		SELECT     KMDH,ID,OID, JFSL, DFSL,YESL,JFJE,DFJE,YEJE
		FROM       UTMXKJ
		WHERE     (PCXH = @PCXH)
		ORDER BY ID
	OPEN UTMXKJ_cursor;
	FETCH FROM UTMXKJ_cursor
		INTO @KMDH,@ID,@OID, @JFSL, @DFSL, @YESL, @JFJE, @DFJE, @YEJE;
	SET @DQKMDH='';
	WHILE @@FETCH_STATUS=0
	BEGIN
		IF @DQKMDH<>@KMDH
			BEGIN
				SELECT @YEFX=YEFX FROM MLKM WHERE KMDH=@KMDH;
			END		
		IF @OID<>''
			BEGIN
				IF @YEFX='借'
					BEGIN
						SET @UYESL=@UYESL+@JFSL-@DFSL;
						SET @UYEJE=@UYEJE+@JFJE-@DFJE;
						UPDATE UTMXKJ SET YESL=@UYESL,YEJE=@UYEJE
							WHERE PCXH=@PCXH AND ID=@ID;
					END
				ELSE
					BEGIN
						SET @UYESL=@UYESL+@DFSL-@JFSL;
						SET @UYEJE=@UYEJE+@DFJE-@JFJE;
						UPDATE UTMXKJ SET YESL=@UYESL,YEJE=@UYEJE
							WHERE PCXH=@PCXH AND ID=@ID;
					END
			END
		ELSE
			BEGIN
				SET @UYESL=@YESL;
				SET @UYEJE=@YEJE;
			END
		SET @DQKMDH=@KMDH;	
		FETCH NEXT FROM UTMXKJ_cursor
			INTO @KMDH,@ID,@OID, @JFSL, @DFSL, @YESL, @JFJE, @DFJE, @YEJE;
	END
	CLOSE UTMXKJ_cursor;
	DEALLOCATE UTMXKJ_cursor	;
	SELECT KMDH,KMQC,NY,RQ, OID, ZY, JFJE, DFJE,YEFX, YEJE
				 FROM 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
BEGIN
	Declare @BBJE DECIMAL(18,4);
	Declare @PJRQ VARCHAR(25);
	Declare @BBSTR VARCHAR(20);
	SET @BBJE=0;
	IF @WBMC='人民币(RMB)' OR @WBMC=''
		BEGIN
			SET @BBSTR=STR(@WBSL,18,4); 
		END
	ELSE
		BEGIN
			IF @WBSL IS NULL SET  @WBSL=0
			SELECT @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);
		END
	RETURN @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
BEGIN
	Declare @STR CHAR(6)
	SELECT @STR=MAX(NY) FROM MLNY
	-- Declare the return variable here
	RETURN @STR
END

GO
/****** 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
BEGIN
	Declare @DY CHAR(6),@SY CHAR(6);
	SELECT @DY=MAX(NY) FROM MLNY;
	SELECT @SY=MAX(NY) FROM MLNY WHERE NY<>@DY;
	RETURN @SY
END

GO
/****** 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年新改 起征点为5000
	DECLARE @JSCE DECIMAL(18,2),@YJSE DECIMAL(18,2);
	SET @JSCE=@JSZE-5000.00;
	IF @JSCE<=0
		BEGIN
			SET @YJSE=0;
			RETURN @YJSE;
		END
	IF @JSCE<=3000
		BEGIN
			SET @YJSE=@JSCE*0.03;
			RETURN @YJSE;
		END
	IF @JSCE<=12000
		BEGIN
			SET @YJSE=@JSCE*0.10-210;
			RETURN @YJSE;
		END
	IF @JSCE<=25000
		BEGIN
			SET @YJSE=@JSCE*0.20-1410;
			RETURN @YJSE;
		END
	IF @JSCE<=35000
		BEGIN
			SET @YJSE=@JSCE*0.25-2660;
			RETURN @YJSE;
		END
	IF @JSCE<=55000
		BEGIN
			SET @YJSE=@JSCE*0.30-4410;
			RETURN @YJSE;
		END
 	IF @JSCE<=80000
		BEGIN
			SET @YJSE=@JSCE*0.35-7160;
			RETURN @YJSE;
		END
	--大于80000
	SET @YJSE=@JSCE*0.45-15160;
	RETURN @YJSE;
END

GO
/****** 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 here
	DECLARE @C NVARCHAR(20),@L INT,@I INT,@S INT; --单个字符，总长，循环变量，找到的位置\
	SET @L=LEN(@STR);
	SET @S=0;
	SET @I=0;
	WHILE @I<@L AND @S<@CS
		BEGIN
			SET @I=@I+1;
			IF SUBSTRING(@STR,@I,LEN(@CH))=@CH
				SET @S=@S+1;
		END
	RETURN @I
END

GO
/****** 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
BEGIN
	Declare @ND CHAR(4),@YF CHAR(2),@NEXT CHAR(6),@RQ DATETIME;
	SELECT @ND=LEFT(@NY,4),@YF=RIGHT(@NY,2);
	IF @SW=0
		SET @RQ=@ND+'-'+@YF+'-01';
	ELSE
		BEGIN
			SELECT @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);
		END
	RETURN @RQ;
END

GO
/****** 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
BEGIN
	DECLARE @I INT,@J INT,@ZF NVARCHAR(1),@TJTEXT NVARCHAR(100) ;
	SET @J=1;
	SET @ZF='';
	SET @TJTEXT='';
	SET @I=CHARINDEX(@TJMC,@TJCS)
	IF @I>0
		BEGIN
			WHILE @J > 0
				BEGIN
					SET @ZF=SUBSTRING(@TJCS,@I+LEN(@TJMC)+@J,1)
					IF @ZF<>'@'
						BEGIN
							SET @TJTEXT=@TJTEXT+@ZF;
							SET @J=@J+1;
						END
					ELSE
						BEGIN
							SET @J=0;
						END
				END
		END
	RETURN @TJTEXT
END

GO
/****** 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
BEGIN
	Declare @WBJE DECIMAL(16,4);
	Declare @PJRQ VARCHAR(25);
	Declare @WBSTR VARCHAR(20);
	SET @WBJE=0;
	IF @WBMC='人民币(RMB)' OR @WBMC=''
		BEGIN
			SET @WBJE=0;
		END
	ELSE
		BEGIN
			IF @BBSL IS NULL SET @WBJE=0
			SELECT TOP 1 @WBJE=(@BBSL*100/JZHL) FROM MLWBPJ WHERE HBMC=@WBMC ORDER BY RQ DESC;
		END
	RETURN @WBJE;
	
END

GO
/****** 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
BEGIN
	Declare @STR NVARCHAR(50)
	SET @STR='';
	SELECT @STR=YHBM FROM X9_GNYH
	RETURN @STR
END

GO
/****** 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
BEGIN
	Declare @STR NVARCHAR(50)
	SET @STR='';
	SELECT @STR=YHDW FROM X9_GNYH
	RETURN @STR
END

http://www.kler.cn/a/371205.html

相关文章：

  • 算法练习：209. 长度最小的子数组
  • Hexo提交部署命令与Git Bash Here控制终端中按下Ctrl+C无法中断hexo s的解决办法
  • 基于yolov8的布匹缺陷检测系统，支持图像、视频和摄像实时检测【pytorch框架、python源码】
  • django中的类属性和类方法
  • rocketmq服务的docker启动和配置
  • 计算和比较不同图像重建方法之间的视觉相似性，使用 LPIPS（Learned Perceptual Image Patch Similarity）度量来评估。
  • NPU 神经网络处理单元
  • el-date-picker日期选择器，如何通过v-model绑定两个变量(还能正常回显)
  • ＜十六＞Ceph mon 运维
  • 重学SpringBoot3-怎样优雅停机
  • Tree of Thoughts: Deliberate Problem Solving with Large Language Models
  • 数据结构——基础知识补充
  • 有趣智力题(非编程题)
  • 【linux网络编程】| socket套接字 | 实现UDP协议聊天室
  • React前端框架
  • 如何在Linux系统中使用Nginx作为Web服务器
  • [数据结构]堆
  • 自然语言处理与文本分析及挖掘：原理、算法及应用场景介绍
  • HCIP-HarmonyOS Application Developer V1.0 笔记（一）
  • 初识WebGL
  • 使用 Microsoft Clarity 记录分析用户行为
  • Netty特点及相关面试题
  • 自动化部署-02-jenkins部署微服务
  • 抖音短剧小程序上线：短视频平台的全新娱乐体验
  • 力扣每日一题合集
  • 深入理解Redis的四种模式