/**
|
* @description 创建存储过程类
|
*/
|
export default class FuncUtils {
|
/**
|
* @description 创建页面存储过程
|
* @return {String}
|
*/
|
static getTableFunc (config) {
|
let form = ''
|
let formParam = ''
|
let _vars = ['bid', 'pageindex', 'pagesize', 'ordercol', 'ordertype', 'exceltype', 'septmenuno', 'lang', 'debug', 'loginuid', 'sessionuid', 'userid', 'errorcode', 'retmsg']
|
let _columns = []
|
let primaryKey = config.setting.primaryKey || 'ID'
|
|
if (!_vars.includes(primaryKey.toLowerCase())) {
|
_vars.push(primaryKey.toLowerCase())
|
formParam = `mchr13k@${primaryKey} nvarchar(50)='',`
|
}
|
|
if (config.search && config.search.length > 0) {
|
let _fields = new Map()
|
config.search.forEach(item => {
|
if (item.field) {
|
let type = ''
|
|
if (item.type.match(/date/ig)) {
|
type = 'datetime=null'
|
} else {
|
type = 'nvarchar(50)=\'\''
|
}
|
|
item.field.split(',').forEach(cell => {
|
let _f = cell
|
if (_fields.has(cell)) {
|
_f = _f + '1'
|
}
|
|
_fields.set(cell, true)
|
|
if (!_vars.includes(_f.toLowerCase())) {
|
_vars.push(_f.toLowerCase())
|
formParam = formParam + `mchr13k@${_f} ${type},`
|
}
|
})
|
}
|
})
|
}
|
|
if (config.columns && config.columns.length > 0) {
|
config.columns.forEach(item => {
|
if (item.field) {
|
_columns.push(`${item.field} as ${item.label}`)
|
}
|
})
|
|
form = `
|
declare @dc table (${_columns.join(',')})
|
|
@tableid ='${config.MenuID}'
|
`
|
}
|
|
let Ltext = `create proc ${config.func}
|
( /*${config.MenuName}*/
|
@appkey nvarchar(50)='',
|
@BID nvarchar(50)='',
|
@UserName nvarchar(50)='',
|
@FullName nvarchar(50)='',${formParam}
|
@PageIndex nvarchar(50)='',
|
@PageSize nvarchar(50)='',
|
@OrderCol nvarchar(50)='',
|
@OrderType nvarchar(50)='',
|
@exceltype nvarchar(50)='',
|
@sEPTMenuNo nvarchar(50)='${config.menuNo}',
|
@lang nvarchar(50)='',
|
@debug nvarchar(50)='',
|
@LoginUID nvarchar(50)='',
|
@SessionUid nvarchar(50)='',
|
@UserID nvarchar(50),
|
@dataM nvarchar(50),
|
@ErrorCode nvarchar(50) out,
|
@retmsg nvarchar(4000) out
|
)
|
as
|
begin
|
declare @BegindateTest datetime,@EnddateTest datetime
|
select @BegindateTest=getdate()
|
set @ErrorCode=''
|
set @retmsg=''
|
BEGIN TRY
|
/*事务操作*/
|
BEGIN TRAN
|
/*具体业务操作*/
|
|
/*
|
select top 10 * from sProcExcep order by id desc
|
|
${form}
|
if 1=2
|
begin
|
set @ErrorCode='E'
|
set @retmsg='在此写报错'
|
goto GOTO_RETURN
|
end
|
|
insert into sNote (remark,createuserid,CreateUser,CreateStaff)
|
select '在此写日志',@UserID,@UserName,@FullName
|
*/
|
|
COMMIT TRAN
|
SET NOCOUNT ON
|
RETURN
|
END TRY
|
BEGIN CATCH
|
/*错误处理*/
|
ROLLBACK TRAN
|
DECLARE @ErrorMessage NVARCHAR(4000);
|
DECLARE @ErrorSeverity INT;
|
DECLARE @ErrorState INT;
|
|
/*把自定义的友好的错误信息提示加上*/
|
set @ErrorCode=cast(ERROR_NUMBER() as nvarchar(50))
|
SET @retmsg=ERROR_MESSAGE();
|
SELECT @ErrorMessage=ERROR_MESSAGE(),
|
@ErrorSeverity=ERROR_SEVERITY(),
|
@ErrorState=ERROR_STATE();
|
|
RAISERROR(@ErrorMessage, /*-- Message text.*/
|
@ErrorSeverity, /*-- Severity.*/
|
@ErrorState /*-- State.*/
|
);
|
END CATCH
|
|
GOTO_RETURN:
|
ROLLBACK TRAN
|
|
END`
|
|
Ltext = Ltext.replace(/\n\s{4}/ig, 'mchr13k')
|
|
return Ltext
|
}
|
|
/**
|
* @description 创建存储过程
|
* @return {String}
|
*/
|
static getfunc (btn) {
|
let form = ''
|
let formParam = ''
|
let _vars = ['bid', 'septmenuno', 'lang', 'debug', 'loginuid', 'sessionuid', 'userid', 'errorcode', 'retmsg']
|
let columns = btn.columns
|
let primaryKey = btn.primaryKey
|
|
if (!_vars.includes(primaryKey.toLowerCase())) {
|
_vars.push(primaryKey.toLowerCase())
|
let _type = '50'
|
if (btn.Ot === 'requiredOnce') { // 多行拼接时，主键设为max
|
_type = 'max'
|
}
|
formParam = `mchr13k@${primaryKey} nvarchar(${_type})='',`
|
}
|
|
if (btn.fields && btn.fields.length > 0) {
|
let _fields = []
|
btn.fields.forEach(item => {
|
if (item.field) {
|
let type = ''
|
if (item.type.match(/date/ig)) {
|
type = 'datetime=null'
|
} else if (item.type === 'number') {
|
type = `decimal(18,${item.decimal})=0`
|
} else if (item.type === 'rate') {
|
type = `decimal(18,2)=0`
|
} else {
|
type = 'nvarchar(50)=\'\''
|
}
|
|
if (!_vars.includes(item.field.toLowerCase())) {
|
_vars.push(item.field.toLowerCase())
|
formParam = formParam + `mchr13k@${item.field} ${type},`
|
}
|
|
_fields.push(item.field)
|
}
|
})
|
|
let field1 = _fields.join(',')
|
let field2 = _fields.join(',@')
|
let field3 = _fields.map(cell => {
|
return cell + '=@' + cell
|
})
|
|
field2 = field2 ? '@' + field2 : ''
|
field3 = field3.join(',')
|
|
form = `
|
insert into ${btn.name} (${field1},createuserid) select ${field2},@UserID
|
|
update ${btn.name} set ${field3},modifydate=getdate(),modifyuser=@username,modifyuserid=@UserID
|
`
|
} else if (btn.OpenType === 'prompt' || btn.OpenType === 'exec') {
|
form = `
|
update ${btn.name} set ModifyDate=getdate(),ModifyUserID=@UserID where ${primaryKey}=@${primaryKey}
|
`
|
}
|
|
if (columns) {
|
let _col = []
|
let _field = []
|
columns.forEach(col => {
|
if (col.field) {
|
if (col.type === 'number') {
|
_col.push(col.field + ' decimal(18,2)')
|
} else {
|
_col.push(col.field + ' nvarchar(50)')
|
}
|
_field.push(col.field)
|
}
|
})
|
_col = _col.join(',')
|
_field = _field.join(',')
|
|
form = form + `
|
declare @dc table (${_col})
|
|
insert into @dc (${_field})
|
|
@tableid ='${btn.MenuID}'
|
`
|
}
|
|
// 打印自定义模板字段提示
|
let _printRemark = ''
|
if (btn.funcType === 'print') {
|
_printRemark = '/* 自定义数据打印模板时，请使用TemplateID字段 */'
|
}
|
|
let Ltext = `create proc ${btn.func}
|
( /*${btn.MenuName} ${btn.label}*/
|
@appkey nvarchar(50)='',
|
@BID nvarchar(50)='',
|
@UserName nvarchar(50)='',
|
@FullName nvarchar(50)='',${formParam}
|
@sEPTMenuNo nvarchar(50)='${btn.menuNo}',
|
@lang nvarchar(50)='',
|
@debug nvarchar(50)='',
|
@LoginUID nvarchar(50)='',
|
@SessionUid nvarchar(50)='',
|
@UserID nvarchar(50),
|
@ErrorCode nvarchar(50) out,
|
@retmsg nvarchar(4000) out
|
)
|
as
|
begin
|
declare @BegindateTest datetime,@EnddateTest datetime
|
select @BegindateTest=getdate()
|
set @ErrorCode=''
|
set @retmsg=''
|
BEGIN TRY
|
/*事务操作*/
|
BEGIN TRAN
|
/*具体业务操作*/
|
${_printRemark}
|
/*
|
select top 10 * from sProcExcep order by id desc
|
|
${form}
|
if 1=2
|
begin
|
set @ErrorCode='E'
|
set @retmsg='在此写报错'
|
goto GOTO_RETURN
|
end
|
|
insert into sNote (remark,createuserid,CreateUser,CreateStaff)
|
select '在此写日志',@UserID,@UserName,@FullName
|
*/
|
|
COMMIT TRAN
|
SET NOCOUNT ON
|
RETURN
|
END TRY
|
BEGIN CATCH
|
/*错误处理*/
|
ROLLBACK TRAN
|
DECLARE @ErrorMessage NVARCHAR(4000);
|
DECLARE @ErrorSeverity INT;
|
DECLARE @ErrorState INT;
|
|
/*把自定义的友好的错误信息提示加上*/
|
set @ErrorCode=cast(ERROR_NUMBER() as nvarchar(50))
|
SET @retmsg=ERROR_MESSAGE();
|
SELECT @ErrorMessage=ERROR_MESSAGE(),
|
@ErrorSeverity=ERROR_SEVERITY(),
|
@ErrorState=ERROR_STATE();
|
|
RAISERROR(@ErrorMessage, /* Message text.*/
|
@ErrorSeverity, /* Severity.*/
|
@ErrorState /* State.*/
|
);
|
END CATCH
|
|
GOTO_RETURN:
|
ROLLBACK TRAN
|
|
END`
|
|
Ltext = Ltext.replace(/\n\s{4}/ig, 'mchr13k')
|
|
return Ltext
|
}
|
|
/**
|
* @description 创建导入存储过程
|
* @return {String}
|
*/
|
static getexcelInfunc (btn) {
|
let _verify = btn.verify
|
|
let _uniquesql = ''
|
if (_verify.uniques && _verify.uniques.length > 0) {
|
_verify.uniques.forEach(unique => {
|
if (unique.status === 'false') return
|
|
let _fields = unique.field.split(',')
|
let _fields_ = _fields.map(_field => {
|
return `a.${_field}=b.${_field}`
|
})
|
_fields_ = _fields_.join(' and ')
|
|
if (unique.verifyType !== 'physical') {
|
_fields_ += ' and b.deleted=0'
|
}
|
|
_uniquesql += `
|
Set @tbid=''
|
Select top 1 @tbid=${_fields.join('+\' \'+')} from (select 1 as n,${unique.field} from @${btn.sheet} ) a group by ${unique.field} having sum(n)>1
|
|
If @tbid!=''
|
Begin
|
select @ErrorCode='${unique.errorCode}',@retmsg=@tbid+' 重复'
|
goto aaa
|
end
|
|
Set @tbid=''
|
Select top 1 @tbid=${_fields.join('+\' \'+')} from @${btn.sheet} a
|
Inner join ${btn.sheet} b on ${_fields_}
|
|
If @tbid!=''
|
Begin
|
select @ErrorCode='${unique.errorCode}',@retmsg=@tbid+' 与已有数据重复'
|
goto aaa
|
end
|
`
|
})
|
|
if (_uniquesql) {
|
_uniquesql = `
|
Declare @tbid Nvarchar(512)
|
${_uniquesql}`
|
}
|
}
|
|
let declarefields = []
|
let fields = []
|
|
_verify.columns.forEach(col => {
|
declarefields.push(`${col.Column} ${col.type}`)
|
fields.push(col.Column)
|
})
|
|
fields = fields.join(',')
|
|
let _sql = `declare @${btn.sheet} table (${declarefields.join(',')},jskey nvarchar(50),BID nvarchar(50))
|
|
Insert into @${btn.sheet} (${fields},jskey,BID)
|
|
select @LText=dbo.urldecode(dbo.base64decode(@LText))
|
|
${_uniquesql}
|
Insert into ${btn.sheet} (${fields},createuserid,createuser,createstaff,bid)
|
Select ${fields},@userid,@username,@fullname,@BID From @${btn.sheet}
|
|
Delete @${btn.sheet}`
|
|
let Ltext = `create proc ${btn.func}
|
( /*${btn.MenuName} ${btn.label}*/
|
@appkey nvarchar(50)='',
|
@ID nvarchar(50)='',
|
@BID nvarchar(50)='',
|
@UserName nvarchar(50)='',
|
@FullName nvarchar(50)='',
|
@Ltext nvarchar(max)='',
|
@sEPTMenuNo nvarchar(50)='${btn.menuNo}',
|
@secretkey nvarchar(50)='',
|
@timestamp nvarchar(50)='',
|
@lang nvarchar(50)='',
|
@LoginUID nvarchar(50)='',
|
@SessionUid nvarchar(50)='',
|
@UserID nvarchar(50),
|
@ErrorCode nvarchar(50) out,
|
@retmsg nvarchar(4000) out
|
)
|
as
|
begin
|
declare @BegindateTest datetime,@EnddateTest datetime
|
select @BegindateTest=getdate()
|
set @ErrorCode=''
|
set @retmsg=''
|
BEGIN TRY
|
/*事务操作*/
|
BEGIN TRAN
|
/*具体业务操作*/
|
|
/*
|
${_sql}
|
*/
|
|
COMMIT TRAN
|
SET NOCOUNT ON
|
RETURN
|
END TRY
|
BEGIN CATCH
|
/*错误处理*/
|
ROLLBACK TRAN
|
DECLARE @ErrorMessage NVARCHAR(4000);
|
DECLARE @ErrorSeverity INT;
|
DECLARE @ErrorState INT;
|
|
/*把自定义的友好的错误信息提示加上*/
|
set @ErrorCode=cast(ERROR_NUMBER() as nvarchar(50))
|
SET @retmsg=ERROR_MESSAGE();
|
SELECT @ErrorMessage=ERROR_MESSAGE(),
|
@ErrorSeverity=ERROR_SEVERITY(),
|
@ErrorState=ERROR_STATE();
|
|
RAISERROR(@ErrorMessage, /* Message text.*/
|
@ErrorSeverity, /* Severity.*/
|
@ErrorState /* State.*/
|
);
|
END CATCH
|
|
GOTO_RETURN:
|
ROLLBACK TRAN
|
|
END`
|
|
Ltext = Ltext.replace(/\n\s{4}/ig, 'mchr13k')
|
|
return Ltext
|
}
|
}
|
