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

青少年编程与数学 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

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/459772.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

如何从iconfont中获取字体图标并应用到微信小程序中去？

如何从iconfont中获取字体图标并应用到微信小程序中去？

下面我们一一个微信小程序的登录界面的制作为例来说明&#xff0c;如何从iconfont中获取字体图标是如何应用到微信小程序中去的。首先我们看效果。 这里所有的图标&#xff0c;都是从iconfont中以字体的形式来加载的&#xff0c;也就是说&#xff0c;我们自始至终没有使用一张…
阅读更多...
Linux shell编程学习笔记87：blkid命令——获取块设备信息

Linux shell编程学习笔记87：blkid命令——获取块设备信息

0 引言 在进行系统安全检测时&#xff0c;我们需要收集块设备的信息&#xff0c;这些可以通过blkid命令来获取。 1 blkid命令的安装 blkid命令是基于libblkid库的命令行工具&#xff0c;可以在大多数Linux发行版中使用。 如果你的Linux系统中没有安装blkid命令&#xff0c;…
阅读更多...
RuoYi-Vue 使用开发 人员管理-查询功能

RuoYi-Vue 使用开发 人员管理-查询功能

说明&#xff1a;这里仅仅开发列表显示 与 查询功能&#xff0c;剩下的添加、修改等可能会遇到报错&#xff0c;后面有机会&#xff0c;会单独写一篇文章教学处理 1.了解开发需求 作为示例的二级开发&#xff0c;这里的人员管理&#xff0c;管理的是 部门信息&#xff0c;员工…
阅读更多...
Tomcat 11 下载/安装 与基本使用

Tomcat 11 下载/安装 与基本使用

为什么要使用Tomcat&#xff1f; 使用Apache Tomcat的原因有很多&#xff0c;以下是一些主要的优点和特点&#xff1a; 1. 开源与免费 Tomcat是一个完全开源的项目&#xff0c;任何人都可以免费使用。它由Apache软件基金会维护&#xff0c;拥有一个活跃的社区&#xff0c;这…
阅读更多...
Django入门教程——用户管理实现

Django入门教程——用户管理实现

第六章 用户管理实现 教学目的 复习数据的增删改查的实现。了解数据MD5加密算法以及实现模型表单中&#xff0c;自定义控件的使用中间件的原理和使用 需求分析 系统问题 员工档案涉及到员工的秘密&#xff0c;不能让任何人都可以看到&#xff0c;主要是人事部门进行数据的…
阅读更多...
[ 问题解决篇 ] 解决远程桌面安全登录框的问题

[ 问题解决篇 ] 解决远程桌面安全登录框的问题

&#x1f36c; 博主介绍 &#x1f468;‍&#x1f393; 博主介绍&#xff1a;大家好&#xff0c;我是 _PowerShell &#xff0c;很高兴认识大家~ ✨主攻领域&#xff1a;【渗透领域】【数据通信】 【通讯安全】 【web安全】【面试分析】 &#x1f389;点赞➕评论➕收藏 养成习…
阅读更多...
微信小程序时间弹窗——年月日时分

微信小程序时间弹窗——年月日时分

需求 1、默认当前时间2、选择时间弹窗限制最大值、最小值3、每次弹起更新最大值为当前时间&#xff0c;默认值为上次选中时间4、 minDate: new Date(2023, 10, 1).getTime(),也可以传入时间字符串new Date(2023-10-1 12:22).getTime() html <view class"flex bb ptb…
阅读更多...
【Spring框架】Spring框架的开发方式

【Spring框架】Spring框架的开发方式

目录 Spring框架开发方式前言具体案例导入依赖创建数据库表结构创建实体类编写持久层接口和实现类编写业务层接口和实现类配置文件的编写 IoC注解开发注解开发入门&#xff08;半注解&#xff09;IoC常用注解Spring纯注解方式开发 Spring整合JUnit测试 Spring框架开发方式 前言…
阅读更多...
江协科技STM32学习- P24 DMA数据转运DMA+AD多通道

江协科技STM32学习- P24 DMA数据转运DMA+AD多通道

&#x1f680;write in front&#x1f680; &#x1f50e;大家好&#xff0c;我是黄桃罐头&#xff0c;希望你看完之后&#xff0c;能对你有所帮助&#xff0c;不足请指正&#xff01;共同学习交流 &#x1f381;欢迎各位→点赞&#x1f44d; 收藏⭐️ 留言&#x1f4dd;​…
阅读更多...
【刷题11】CTFHub技能树sql注入系列

【刷题11】CTFHub技能树sql注入系列

整数型注入 看到源码了&#xff0c;直接sql一套秒了 字符型注入 SQL 报错注入 构造payload 1 and (select extractvalue(1,concat(’~’,(select database())))) 后续步骤跟sql基本步骤一样 SQL 布尔注入 人工测试太麻烦&#xff0c;这里直接使用sqlmap,知道这有sql注入漏洞&am…
阅读更多...
面试经典 150 题.P26. 删除有序数组中的重复项（003）

面试经典 150 题.P26. 删除有序数组中的重复项（003）

本题来自&#xff1a;力扣-面试经典 150 题 面试经典 150 题 - 学习计划 - 力扣&#xff08;LeetCode&#xff09;全球极客挚爱的技术成长平台https://leetcode.cn/studyplan/top-interview-150/ 题解&#xff1a; class Solution {public int removeDuplicates(int[] nums) …
阅读更多...
docker中使用ros2humble的rviz2不显示问题

docker中使用ros2humble的rviz2不显示问题

这里写目录标题 docker中使用ros2humble的rviz2不显示问题删除 Docker 镜像和容器删除 Docker 容器Linux服务器下查看系统CPU个数、核心数、(make编译最大的)线程数总结&#xff1a; RVIZ2 不能显示数据集 docker中使用ros2humble的rviz2不显示问题 问题描述&#xff1a; roo…
阅读更多...
ELK + Filebeat + Spring Boot：日志分析入门与实践（二）

ELK + Filebeat + Spring Boot：日志分析入门与实践（二）

目录 一、环境 1.1 ELKF环境 1.2 版本 1.3 流程 二、Filebeat安装 2.1 安装 2.2 新增配置采集日志 三、logstash 配置 3.1 配置输出日志到es 3.2 Grok 日志格式解析 3.2 启动 logstash ​3.3 启动项目查看索引 一、环境 1.1 ELKF环境 springboot项目&#xff1a;w…
阅读更多...
基于SSM土家风景文化管理系统的设计

基于SSM土家风景文化管理系统的设计

管理员账户功能包括&#xff1a;系统首页&#xff0c;个人中心&#xff0c;用户管理&#xff0c;景点分类管理&#xff0c;热门景点管理&#xff0c;门票订单管理&#xff0c;旅游线路管理&#xff0c;系统管理 前提账号功能包括&#xff1a;系统首页&#xff0c;个人中心&…
阅读更多...
Linux特种文件系统--tmpfs文件系统

Linux特种文件系统--tmpfs文件系统

tmpfs类似于RamDisk&#xff08;只能使用物理内存&#xff09;&#xff0c;使用虚拟内存&#xff08;简称VM&#xff09;子系统的页面存储文件。tmpfs完全依赖VM&#xff0c;遵循子系统的整体调度策略。说白了tmpfs跟普通进程差不多&#xff0c;使用的都是某种形式的虚拟内存&a…
阅读更多...
不同概率分布的更新过程——Python实现（均匀分布、卡方分布、指数分布等作为概率分布的更新过程）

不同概率分布的更新过程——Python实现（均匀分布、卡方分布、指数分布等作为概率分布的更新过程）

更新过程（renewal process）是描述元件或设备更新现象的一类随机过程。以下是对更新过程的详细介绍： 一、定义与特点 定义：设对某元件的工作进行观测，假定元件的使用寿命是一随机变量，当元件发生故障时就进行修理或换上新的同类元件，而且元件的更新是即时的（修理或更换…
阅读更多...
GIT分布式版本控制系统基础操作

GIT分布式版本控制系统基础操作

问题大纲 1、什么分布式版本控制系统 2、简述Git的使用分为哪几个步骤 3、克隆和拉取的区别是什么&#xff1f; 4、git相关的所有指令 一、分布式版本控制系统 分布式版本控制系统是一种版本控制系统&#xff0c;它允许每个用户都拥有完整的项目历史记录和版本控制信息。与…
阅读更多...
ArcGIS必会的选择要素方法（AND、OR、R、IN等）位置选择等

ArcGIS必会的选择要素方法（AND、OR、R、IN等）位置选择等

今天来看看ArcGIS中的几个选择的重要使用方法 1、常规选择、 2、模糊查询、 3、组合复合条件查询&#xff08;AND、OR、IN&#xff09;&#xff0c; 4、空值NULL查询 5、位置选择 推荐学习&#xff1a; 以梦为马&#xff0c;超过万名学员学习ArcGIS入门到实战的应用课程…
阅读更多...
Spring Bean创建流程

Spring Bean创建流程

Spring Bean 创建流程图 大家总是会错误的理解Bean的“实例化”和“初始化”过程&#xff0c;总会以为初始化就是对象执行构造函数生成对象实例的过程&#xff0c;其实不然&#xff0c;在初始化阶段实际对象已经实例化出来了&#xff0c;初始化阶段进行的是依赖的注入和执行一…
阅读更多...
rtp协议：rtcp包格式和传输间隔

rtp协议：rtcp包格式和传输间隔

RTP Control Protocol -- RTCP-rtp控制协议 实时传输控制协议&#xff08;RTCP&#xff09;基于对会话中的所有参与者定期传输控制包&#xff0c;使用与数据包相同的分发机制。底层协议必须提供数据包和控制包的多路复用&#xff0c;例如使用UDP时使用不同的端口号。RTCP执行四…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023