祺佑三层开发框架（猫框）2023.11.16发新

加菲猫的VFP
发布于 2023-11-16 12:52:41
1600
发布于 2023-11-16 12:52:41
文章被收录于专栏：加菲猫的VFP加菲猫的VFP
代码生成器增加几个生成模式
1 JSON转表
2 DAL中间层类
3 VFP前端
其中中间层类搭配VFP前端可以实现中间层模式下的一键SAVE
VFP类增加了qiyu_image, qiyu_treeview类
qiyu_image实现在GRID显示图片
qiyu_treeview实现了无限级类
qiyu_pagination实现了分页
数据表结构
/*
 Navicat Premium Data Transfer

 Source Server         : 127.0.0.1
 Source Server Type    : SQL Server
 Source Server Version : 10501600
 Source Host           : 127.0.0.1,3300:1433
 Source Catalog        : qiyutest
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 10501600
 File Encoding         : 65001

 Date: 16/11/2023 10:00:08
*/


-- ----------------------------
-- Table structure for pagedata
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[pagedata]') AND type IN ('U'))
  DROP TABLE [dbo].[pagedata]
GO

CREATE TABLE [dbo].[pagedata] (
  [id] int  IDENTITY(1,1) NOT NULL,
  [name] varchar(50) COLLATE Chinese_PRC_CI_AS  NOT NULL
)
GO

ALTER TABLE [dbo].[pagedata] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of pagedata
-- ----------------------------
SET IDENTITY_INSERT [dbo].[pagedata] ON
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'1', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'2', N'2')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'3', N'3')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'4', N'4')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'5', N'5')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'6', N'6')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'7', N'7')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'8', N'8')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'9', N'9')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'10', N'10')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'11', N'11')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'12', N'12')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'13', N'13')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'14', N'14')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'15', N'15')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'16', N'16')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'18', N'17')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'19', N'18')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'20', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'21', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'22', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'23', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'24', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'25', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'26', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'27', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'28', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'29', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'30', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'31', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'32', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'33', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'34', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'35', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'36', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'37', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'38', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'39', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'40', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'41', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'42', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'43', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'44', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'45', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'46', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'47', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'48', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'49', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'50', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'51', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'52', N'1')
GO

INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'53', N'1')
GO

SET IDENTITY_INSERT [dbo].[pagedata] OFF
GO


-- ----------------------------
-- Table structure for treedata
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[treedata]') AND type IN ('U'))
  DROP TABLE [dbo].[treedata]
GO

CREATE TABLE [dbo].[treedata] (
  [id] int  IDENTITY(1,1) NOT NULL,
  [name] varchar(50) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [fid] int  NULL
)
GO

ALTER TABLE [dbo].[treedata] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of treedata
-- ----------------------------
SET IDENTITY_INSERT [dbo].[treedata] ON
GO

INSERT INTO [dbo].[treedata] ([id], [name], [fid]) VALUES (N'1', N'ttt                                               ', N'0')
GO

INSERT INTO [dbo].[treedata] ([id], [name], [fid]) VALUES (N'2', N'12312                                             ', N'1')
GO

INSERT INTO [dbo].[treedata] ([id], [name], [fid]) VALUES (N'5', N'测试', N'2')
GO

INSERT INTO [dbo].[treedata] ([id], [name], [fid]) VALUES (N'6', N'我是子烊', N'5')
GO

SET IDENTITY_INSERT [dbo].[treedata] OFF
GO


-- ----------------------------
-- Table structure for treedata1
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[treedata1]') AND type IN ('U'))
  DROP TABLE [dbo].[treedata1]
GO

CREATE TABLE [dbo].[treedata1] (
  [id] int  IDENTITY(1,1) NOT NULL,
  [name] varchar(50) COLLATE Chinese_PRC_CI_AS  NOT NULL
)
GO

ALTER TABLE [dbo].[treedata1] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of treedata1
-- ----------------------------
SET IDENTITY_INSERT [dbo].[treedata1] ON
GO

INSERT INTO [dbo].[treedata1] ([id], [name]) VALUES (N'1', N'6677                                              ')
GO

INSERT INTO [dbo].[treedata1] ([id], [name]) VALUES (N'2', N'687                                               ')
GO

INSERT INTO [dbo].[treedata1] ([id], [name]) VALUES (N'3', N'8909                                              ')
GO

SET IDENTITY_INSERT [dbo].[treedata1] OFF
GO


-- ----------------------------
-- Table structure for treepathdata
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[treepathdata]') AND type IN ('U'))
  DROP TABLE [dbo].[treepathdata]
GO

CREATE TABLE [dbo].[treepathdata] (
  [id] int  IDENTITY(1,1) NOT NULL,
  [name] varchar(50) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [path] varchar(250) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [fid] int  NULL
)
GO

ALTER TABLE [dbo].[treepathdata] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of treepathdata
-- ----------------------------
SET IDENTITY_INSERT [dbo].[treepathdata] ON
GO

SET IDENTITY_INSERT [dbo].[treepathdata] OFF
GO


-- ----------------------------
-- procedure structure for SelectBase1
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectBase1]') AND type IN ('FN', 'FS', 'FT', 'IF', 'TF'))
  DROP PROCEDURE[dbo].[SelectBase1]
GO

CREATE PROCEDURE [dbo].[SelectBase1]
@PageIndex  int, 
@PageSize  int, 
@TableName      nvarchar(4000), 
@Where           nvarchar(2000)='',
@rowcount       int output
as 

Declare @intStart      int 
Declare @intEnd         int 
declare @Column1 varchar(32) 
Declare @Sql nvarchar(2000), @WhereR nvarchar(1000), @OrderBy nvarchar(1000) 
set @rowcount=0 
set nocount on 

if CHARINDEX('order by', @Where)>0 
  begin 
    set @WhereR=substring(@Where, 1, CHARINDEX('order by',@Where)-1)  --取得条件 
    set @OrderBy=substring(@Where, CHARINDEX('order by',@Where), Len(@Where))  --取得排序方式(order by 字段 方式) 
    if @WhereR<>'' 
    begin 
      set @WhereR=' and '+@WhereR 
    end 

  end 
else 
  begin 
    set @WhereR=@Where 
    if @WhereR<>'' 
    begin 
      set @WhereR=' and '+@WhereR 
    end 
          set @OrderBy='' 
  end 

if @WhereR<>'' --说明有Where
begin 
  set @Where=' and '+@Where 
end 
print 'where处理完成'+@Where
print @WhereR
print @OrderBy
set @Sql='SELECT @rowcount=count(*) from '+cast(@TableName as varchar(4000))+' where 1=1 '+@WhereR
exec sp_executeSql @Sql,N'@rowcount int output',@rowcount output 
print '获取总行数'+@Sql
print @rowcount
if @PageIndex=0 and @PageSize=0  --不进行分页,查询所有数据列表 
  begin 
  set @Sql='SELECT * from '+cast(@TableName as varchar(4000))+' where 1=1 '+@Where 
  end 
else  --进行分页查询数据列表 
        print '开始分页查询'
  begin 
  set @intStart=(@PageIndex-1)*@PageSize+1; 
  set @intEnd=@intStart+@PageSize-1 

  set @Column1=col_name(object_id(@tableName),1) 

  if @Column1 is null begin set @Column1='ID' end 
  set @Sql='Create table #tem(tempID int identity(1,1) not null,Row Varchar(100)) '  
  set @Sql=@Sql+'insert #tem(Row) select '+@Column1+' from '+@TableName+' where 1=1 '+@Where  
  set @Sql=@Sql+' select * from '+@TableName+' left join #tem  on '+@Column1+'=#tem.Row '  
  set @Sql=@Sql+' where  #tem.tempID between '+cast(@intStart as varchar)+' and '+cast(@intEnd as varchar) +' Order by  tempID'
  end 
  print @Sql
  exec sp_executeSql @Sql 
--return @rowcount 
set nocount off
GO


-- ----------------------------
-- procedure structure for SelectBase
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectBase]') AND type IN ('FN', 'FS', 'FT', 'IF', 'TF'))
  DROP PROCEDURE[dbo].[SelectBase]
GO

CREATE PROCEDURE [dbo].[SelectBase]
@PageIndex  int, 
@PageSize  int, 
@TableName      nvarchar(4000), 
@Where           nvarchar(2000)='',
@rowcount       int output
as 

Declare @intStart      int 
Declare @intEnd         int 
declare @Column1 varchar(32) 
Declare @Sql nvarchar(2000), @WhereR nvarchar(1000), @OrderBy nvarchar(1000) 
set @rowcount=0 
set nocount on 

if CHARINDEX('order by', @Where)>0 
  begin 
    set @WhereR=substring(@Where, 1, CHARINDEX('order by',@Where)-1)  --取得条件 
    set @OrderBy=substring(@Where, CHARINDEX('order by',@Where), Len(@Where))  --取得排序方式(order by 字段 方式) 
    if @WhereR<>'' 
    begin 
      set @WhereR=' and '+@WhereR 
    end 

  end 
else 
  begin 
    set @WhereR=@Where 
    if @WhereR<>'' 
    begin 
      set @WhereR=' and '+@WhereR 
    end 
          set @OrderBy='' 
  end 

if @WhereR<>'' --说明有Where
begin 
  set @Where=' and '+@Where 
end 
print 'where处理完成'+@Where
print @WhereR
print @OrderBy
set @Sql='SELECT @rowcount=count(*) from '+cast(@TableName as varchar(4000))+' where 1=1 '+@WhereR
exec sp_executeSql @Sql,N'@rowcount int output',@rowcount output 
print '获取总行数'+@Sql
print @rowcount
if @PageIndex=0 and @PageSize=0  --不进行分页,查询所有数据列表 
  begin 
  set @Sql='SELECT * from '+cast(@TableName as varchar(4000))+' where 1=1 '+@Where 
  end 
else  --进行分页查询数据列表 
        print '开始分页查询'
  begin 
  set @intStart=(@PageIndex-1)*@PageSize+1; 
  set @intEnd=@intStart+@PageSize-1 

  set @Column1=col_name(object_id(@tableName),1) 

  if @Column1 is null begin set @Column1='ID' end 
  set @Sql='Create table #tem(tempID int identity(1,1) not null,Row Varchar(100)) '  
  set @Sql=@Sql+'insert #tem(Row) select '+@Column1+' from '+@TableName+' where 1=1 '+@Where  
  set @Sql=@Sql+' select * from '+@TableName+' left join #tem  on '+@Column1+'=#tem.Row '  
  set @Sql=@Sql+' where  #tem.tempID between '+cast(@intStart as varchar)+' and '+cast(@intEnd as varchar) +' Order by  tempID'
  end 
  print @Sql
  exec sp_executeSql @Sql 
--return @rowcount 
set nocount off
GO


-- ----------------------------
-- Auto increment value for pagedata
-- ----------------------------
DBCC CHECKIDENT ('[dbo].[pagedata]', RESEED, 53)
GO


-- ----------------------------
-- Primary Key structure for table pagedata
-- ----------------------------
ALTER TABLE [dbo].[pagedata] ADD CONSTRAINT [PK_pagedata] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Auto increment value for treedata
-- ----------------------------
DBCC CHECKIDENT ('[dbo].[treedata]', RESEED, 6)
GO


-- ----------------------------
-- Primary Key structure for table treedata
-- ----------------------------
ALTER TABLE [dbo].[treedata] ADD CONSTRAINT [PK_treedata] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Auto increment value for treedata1
-- ----------------------------
DBCC CHECKIDENT ('[dbo].[treedata1]', RESEED, 3)
GO


-- ----------------------------
-- Primary Key structure for table treedata1
-- ----------------------------
ALTER TABLE [dbo].[treedata1] ADD CONSTRAINT [PK_treedata1] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Auto increment value for treepathdata
-- ----------------------------
DBCC CHECKIDENT ('[dbo].[treepathdata]', RESEED, 1)
GO


-- ----------------------------
-- Primary Key structure for table treepathdata
-- ----------------------------
ALTER TABLE [dbo].[treepathdata] ADD CONSTRAINT [PK_treepathdata] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO
本文参与腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2023-11-16，如有侵权请联系 cloudcommunity@tencent.com 删除