Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Assembly |
Parameters
| Name | Data Type | Length | Description |
|---|---|---|---|
| @filepath | varchar | 500 | |
| @filename | varchar | 100 | |
| @username | varchar | 50 | |
| @tipe | varchar | 50 | |
| @trx | varchar | 50 | |
| @id | bigint | 8 |
SQL Script
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spuload_val_pajak
(@filepath varchar(500), @filename varchar(100), @username varchar(50), @tipe varchar(50), @trx varchar(50), @id bigint )
AS
BEGIN
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
if @tipe='a1'
begin
if @trx = 'add'
begin
if (select COUNT(*) from tbl_upload_validasi_pajak where [filename] = @filename and [status_upload] = 'SUKSES') > 0
begin
select '06' as status, 'File Sudah Pernah Di Upload' as description
return
end
EXEC (
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@filepath +
''', [a1$]) WHERE F1=''pemberi_jkp'' AND F2=''kode_no_seri'' and F3=''tgl'' and f4=''dpp'' and f5=''masa_spt'' ) IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FORMAT FILE TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_upload_validasi_pajak] ([filename],[filepath],[status_upload], create_by,create_date, update_by, update_date, date_upload, tipe) VALUES ('''+ @filename +''','''+ @filepath +''',''SUKSES'','''+@username+''',GETDATE(), '''+@username+''',GETDATE(), GETDATE(), '''+@tipe+
''') SELECT ''00'' AS status, ''SUKSES TRANSFERED'' AS description ')
end
if @trx = 'delete'
begin
if (select COUNT(*) from tbl_upload_validasi_pajak where [id_validasi_pajak] = @id and [status_upload] = 'Deleted') > 0
begin
select '06' as status, 'File Sudah Deleted' as description
return
end
if (select COUNT(*) from tbl_upload_validasi_pajak where [id_validasi_pajak] = @id and [status_upload] <> 'SUKSES') > 0
begin
select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
return
end
update tbl_upload_validasi_pajak set status_upload='Deleted' where [id_validasi_pajak] = @id
SELECT '00' AS status, 'DATA DELETED' AS description
end
end
if @tipe='a2'
begin
if @trx = 'add'
begin
if (select COUNT(*) from tbl_upload_validasi_pajak where [filename] = @filename and [status_upload] = 'SUKSES') > 0
begin
select '06' as status, 'File Sudah Pernah Di Upload' as description
return
end
EXEC (
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@filepath +
''', [a2$]) WHERE F1=''pemberi_jkp'' AND F2=''npwp'' AND F3=''kode_no_seri'' and F4=''tgl'' and f5=''dpp'' and f6=''ppn'' and f7=''ppnbm'' and f8=''masa_spt'' ) IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FORMAT FILE TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_upload_validasi_pajak] ([filename],[filepath],[status_upload], create_by,create_date, update_by, update_date, date_upload, tipe) VALUES ('''+ @filename +''','''+ @filepath +''',''SUKSES'','''+@username+''',GETDATE(), '''+@username+''',GETDATE(), GETDATE(), '''+@tipe+
''') SELECT ''00'' AS status, ''SUKSES TRANSFERED'' AS description ')
end
if @trx = 'delete'
begin
if (select COUNT(*) from tbl_upload_validasi_pajak where [id_validasi_pajak] = @id and [status_upload] = 'Deleted') > 0
begin
select '06' as status, 'File Sudah Deleted' as description
return
end
if (select COUNT(*) from tbl_upload_validasi_pajak where [id_validasi_pajak] = @id and [status_upload] <> 'SUKSES') > 0
begin
select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
return
end
update tbl_upload_validasi_pajak set status_upload='Deleted' where [id_validasi_pajak] = @id
SELECT '00' AS status, 'DATA DELETED' AS description
end
end
if @tipe='b1'
begin
if @trx = 'add'
begin
if (select COUNT(*) from tbl_upload_validasi_pajak where [filename] = @filename and [status_upload] = 'SUKSES') > 0
begin
select '06' as status, 'File Sudah Pernah Di Upload' as description
return
end
EXEC (
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@filepath +
''', [b1$]) WHERE F1=''pemberi_jkp'' AND F2=''kode_no_seri'' and F3=''tgl'' and f4=''dpp'' and f5=''ppn'' and f6=''ppnbm'' and f7=''masa_spt'' ) IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FORMAT FILE TIDAK SESUAI B1'' AS description RETURN END INSERT INTO [tbl_upload_validasi_pajak] ([filename],[filepath],[status_upload], create_by,create_date, update_by, update_date, date_upload, tipe) VALUES ('''+ @filename +''','''+ @filepath +''',''SUKSES'','''+@username+''',GETDATE(), '''+@username+''',GETDATE(), GETDATE(), '''+@tipe+
''') SELECT ''00'' AS status, ''SUKSES TRANSFERED'' AS description ')
end
if @trx = 'delete'
begin
if (select COUNT(*) from tbl_upload_validasi_pajak where [id_validasi_pajak] = @id and [status_upload] = 'Deleted') > 0
begin
select '06' as status, 'File Sudah Deleted' as description
return
end
if (select COUNT(*) from tbl_upload_validasi_pajak where [id_validasi_pajak] = @id and [status_upload] <> 'SUKSES') > 0
begin
select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
return
end
update tbl_upload_validasi_pajak set status_upload='Deleted' where [id_validasi_pajak] = @id
SELECT '00' AS status, 'DATA DELETED' AS description
end
end
if @tipe='b2'
begin
if @trx = 'add'
begin
if (select COUNT(*) from tbl_upload_validasi_pajak where [filename] = @filename and [status_upload] = 'SUKSES') > 0
begin
select '06' as status, 'File Sudah Pernah Di Upload' as description
return
end
EXEC (
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@filepath +
''', [b2$]) WHERE F1=''pemberi_jkp'' AND F2=''npwp'' AND F3=''kode_no_seri'' and F4=''tgl'' and f5=''dpp'' and f6=''ppn'' and f7=''ppnbm'' and f8=''masa_spt'' ) IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FORMAT FILE TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_upload_validasi_pajak] ([filename],[filepath],[status_upload], create_by,create_date, update_by, update_date, date_upload, tipe) VALUES ('''+ @filename +''','''+ @filepath +''',''SUKSES'','''+@username+''',GETDATE(), '''+@username+''',GETDATE(), GETDATE(), '''+@tipe+
''') SELECT ''00'' AS status, ''SUKSES TRANSFERED'' AS description ')
end
if @trx = 'delete'
begin
if (select COUNT(*) from tbl_upload_validasi_pajak where [id_validasi_pajak] = @id and [status_upload] = 'Deleted') > 0
begin
select '06' as status, 'File Sudah Deleted' as description
return
end
if (select COUNT(*) from tbl_upload_validasi_pajak where [id_validasi_pajak] = @id and [status_upload] <> 'SUKSES') > 0
begin
select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
return
end
update tbl_upload_validasi_pajak set status_upload='Deleted' where [id_validasi_pajak] = @id
SELECT '00' AS status, 'DATA DELETED' AS description
end
end
if @tipe='b3'
begin
if @trx = 'add'
begin
if (select COUNT(*) from tbl_upload_validasi_pajak where [filename] = @filename and [status_upload] = 'SUKSES') > 0
begin
select '06' as status, 'File Sudah Pernah Di Upload' as description
return
end
EXEC (
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@filepath +
''', [b3$]) WHERE F1=''pemberi_jkp'' AND F2=''npwp'' AND F3=''kode_no_seri'' and F4=''tgl'' and f5=''dpp'' and f6=''ppn'' and f7=''ppnbm'' and f8=''masa_spt'' ) IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FORMAT FILE TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_upload_validasi_pajak] ([filename],[filepath],[status_upload], create_by,create_date, update_by, update_date, date_upload, tipe) VALUES ('''+ @filename +''','''+ @filepath +''',''SUKSES'','''+@username+''',GETDATE(), '''+@username+''',GETDATE(), GETDATE(), '''+@tipe+
''') SELECT ''00'' AS status, ''SUKSES TRANSFERED'' AS description ')
end
if @trx = 'delete'
begin
if (select COUNT(*) from tbl_upload_validasi_pajak where [id_validasi_pajak] = @id and [status_upload] = 'Deleted') > 0
begin
select '06' as status, 'File Sudah Deleted' as description
return
end
if (select COUNT(*) from tbl_upload_validasi_pajak where [id_validasi_pajak] = @id and [status_upload] <> 'SUKSES') > 0
begin
select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
return
end
update tbl_upload_validasi_pajak set status_upload='Deleted' where [id_validasi_pajak] = @id
SELECT '00' AS status, 'DATA DELETED' AS description
end
end
END
GO
GO
CREATE PROCEDURE dbo.spuload_val_pajak
(@filepath varchar(500), @filename varchar(100), @username varchar(50), @tipe varchar(50), @trx varchar(50), @id bigint )
AS
BEGIN
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
if @tipe='a1'
begin
if @trx = 'add'
begin
if (select COUNT(*) from tbl_upload_validasi_pajak where [filename] = @filename and [status_upload] = 'SUKSES') > 0
begin
select '06' as status, 'File Sudah Pernah Di Upload' as description
return
end
EXEC (
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@filepath +
''', [a1$]) WHERE F1=''pemberi_jkp'' AND F2=''kode_no_seri'' and F3=''tgl'' and f4=''dpp'' and f5=''masa_spt'' ) IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FORMAT FILE TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_upload_validasi_pajak] ([filename],[filepath],[status_upload], create_by,create_date, update_by, update_date, date_upload, tipe) VALUES ('''+ @filename +''','''+ @filepath +''',''SUKSES'','''+@username+''',GETDATE(), '''+@username+''',GETDATE(), GETDATE(), '''+@tipe+
''') SELECT ''00'' AS status, ''SUKSES TRANSFERED'' AS description ')
end
if @trx = 'delete'
begin
if (select COUNT(*) from tbl_upload_validasi_pajak where [id_validasi_pajak] = @id and [status_upload] = 'Deleted') > 0
begin
select '06' as status, 'File Sudah Deleted' as description
return
end
if (select COUNT(*) from tbl_upload_validasi_pajak where [id_validasi_pajak] = @id and [status_upload] <> 'SUKSES') > 0
begin
select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
return
end
update tbl_upload_validasi_pajak set status_upload='Deleted' where [id_validasi_pajak] = @id
SELECT '00' AS status, 'DATA DELETED' AS description
end
end
if @tipe='a2'
begin
if @trx = 'add'
begin
if (select COUNT(*) from tbl_upload_validasi_pajak where [filename] = @filename and [status_upload] = 'SUKSES') > 0
begin
select '06' as status, 'File Sudah Pernah Di Upload' as description
return
end
EXEC (
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@filepath +
''', [a2$]) WHERE F1=''pemberi_jkp'' AND F2=''npwp'' AND F3=''kode_no_seri'' and F4=''tgl'' and f5=''dpp'' and f6=''ppn'' and f7=''ppnbm'' and f8=''masa_spt'' ) IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FORMAT FILE TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_upload_validasi_pajak] ([filename],[filepath],[status_upload], create_by,create_date, update_by, update_date, date_upload, tipe) VALUES ('''+ @filename +''','''+ @filepath +''',''SUKSES'','''+@username+''',GETDATE(), '''+@username+''',GETDATE(), GETDATE(), '''+@tipe+
''') SELECT ''00'' AS status, ''SUKSES TRANSFERED'' AS description ')
end
if @trx = 'delete'
begin
if (select COUNT(*) from tbl_upload_validasi_pajak where [id_validasi_pajak] = @id and [status_upload] = 'Deleted') > 0
begin
select '06' as status, 'File Sudah Deleted' as description
return
end
if (select COUNT(*) from tbl_upload_validasi_pajak where [id_validasi_pajak] = @id and [status_upload] <> 'SUKSES') > 0
begin
select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
return
end
update tbl_upload_validasi_pajak set status_upload='Deleted' where [id_validasi_pajak] = @id
SELECT '00' AS status, 'DATA DELETED' AS description
end
end
if @tipe='b1'
begin
if @trx = 'add'
begin
if (select COUNT(*) from tbl_upload_validasi_pajak where [filename] = @filename and [status_upload] = 'SUKSES') > 0
begin
select '06' as status, 'File Sudah Pernah Di Upload' as description
return
end
EXEC (
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@filepath +
''', [b1$]) WHERE F1=''pemberi_jkp'' AND F2=''kode_no_seri'' and F3=''tgl'' and f4=''dpp'' and f5=''ppn'' and f6=''ppnbm'' and f7=''masa_spt'' ) IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FORMAT FILE TIDAK SESUAI B1'' AS description RETURN END INSERT INTO [tbl_upload_validasi_pajak] ([filename],[filepath],[status_upload], create_by,create_date, update_by, update_date, date_upload, tipe) VALUES ('''+ @filename +''','''+ @filepath +''',''SUKSES'','''+@username+''',GETDATE(), '''+@username+''',GETDATE(), GETDATE(), '''+@tipe+
''') SELECT ''00'' AS status, ''SUKSES TRANSFERED'' AS description ')
end
if @trx = 'delete'
begin
if (select COUNT(*) from tbl_upload_validasi_pajak where [id_validasi_pajak] = @id and [status_upload] = 'Deleted') > 0
begin
select '06' as status, 'File Sudah Deleted' as description
return
end
if (select COUNT(*) from tbl_upload_validasi_pajak where [id_validasi_pajak] = @id and [status_upload] <> 'SUKSES') > 0
begin
select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
return
end
update tbl_upload_validasi_pajak set status_upload='Deleted' where [id_validasi_pajak] = @id
SELECT '00' AS status, 'DATA DELETED' AS description
end
end
if @tipe='b2'
begin
if @trx = 'add'
begin
if (select COUNT(*) from tbl_upload_validasi_pajak where [filename] = @filename and [status_upload] = 'SUKSES') > 0
begin
select '06' as status, 'File Sudah Pernah Di Upload' as description
return
end
EXEC (
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@filepath +
''', [b2$]) WHERE F1=''pemberi_jkp'' AND F2=''npwp'' AND F3=''kode_no_seri'' and F4=''tgl'' and f5=''dpp'' and f6=''ppn'' and f7=''ppnbm'' and f8=''masa_spt'' ) IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FORMAT FILE TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_upload_validasi_pajak] ([filename],[filepath],[status_upload], create_by,create_date, update_by, update_date, date_upload, tipe) VALUES ('''+ @filename +''','''+ @filepath +''',''SUKSES'','''+@username+''',GETDATE(), '''+@username+''',GETDATE(), GETDATE(), '''+@tipe+
''') SELECT ''00'' AS status, ''SUKSES TRANSFERED'' AS description ')
end
if @trx = 'delete'
begin
if (select COUNT(*) from tbl_upload_validasi_pajak where [id_validasi_pajak] = @id and [status_upload] = 'Deleted') > 0
begin
select '06' as status, 'File Sudah Deleted' as description
return
end
if (select COUNT(*) from tbl_upload_validasi_pajak where [id_validasi_pajak] = @id and [status_upload] <> 'SUKSES') > 0
begin
select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
return
end
update tbl_upload_validasi_pajak set status_upload='Deleted' where [id_validasi_pajak] = @id
SELECT '00' AS status, 'DATA DELETED' AS description
end
end
if @tipe='b3'
begin
if @trx = 'add'
begin
if (select COUNT(*) from tbl_upload_validasi_pajak where [filename] = @filename and [status_upload] = 'SUKSES') > 0
begin
select '06' as status, 'File Sudah Pernah Di Upload' as description
return
end
EXEC (
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@filepath +
''', [b3$]) WHERE F1=''pemberi_jkp'' AND F2=''npwp'' AND F3=''kode_no_seri'' and F4=''tgl'' and f5=''dpp'' and f6=''ppn'' and f7=''ppnbm'' and f8=''masa_spt'' ) IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FORMAT FILE TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_upload_validasi_pajak] ([filename],[filepath],[status_upload], create_by,create_date, update_by, update_date, date_upload, tipe) VALUES ('''+ @filename +''','''+ @filepath +''',''SUKSES'','''+@username+''',GETDATE(), '''+@username+''',GETDATE(), GETDATE(), '''+@tipe+
''') SELECT ''00'' AS status, ''SUKSES TRANSFERED'' AS description ')
end
if @trx = 'delete'
begin
if (select COUNT(*) from tbl_upload_validasi_pajak where [id_validasi_pajak] = @id and [status_upload] = 'Deleted') > 0
begin
select '06' as status, 'File Sudah Deleted' as description
return
end
if (select COUNT(*) from tbl_upload_validasi_pajak where [id_validasi_pajak] = @id and [status_upload] <> 'SUKSES') > 0
begin
select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
return
end
update tbl_upload_validasi_pajak set status_upload='Deleted' where [id_validasi_pajak] = @id
SELECT '00' AS status, 'DATA DELETED' AS description
end
end
END
GO
Depends On
1Used By
No items found