K2 SmartForm Filter Pencarian Stored Procedure di SQL Server

Dikarenakan Filter pencarian default yang disediakan K2 Smartform kurang disukai, setelah mencari-cari alternatif lain, akhirnya menemukan penggunaan stored procedure(SP) sebagai alternatif untuk filter pencarian.

Berikut langkah2 membuat custom filter pencarian dengan stored procedure

1. Buatlah smartobject Mahasiswa dengan table sbb:
   id int,
   kodemhs string,
   nama string,
   tgllahir date,
   jurusan String
2. Buatlah stored procedure dengan kode sbb dan run di query sql:
   

   CREATE PROCEDURE [dbo].[MahasiswaGetList]
   @pkodemhs nvarchar(100) = ”,
   @pnama nvarchar(100) = ”,
   @ptgl_lahir date = ‘1900-01-01’,
   @pjurusan nvarchar(100) = ”,
   @SOP nvarchar(100) = ‘=-=-=-=’
   AS
   SELECT TOP 1 ID, kodemhs, nama, tgl_lahir, jurusan
   FROM [dbo].[Mahasiswa]
   where 1 = 1

3. Lakukan Refresh Service Instance
   
4. Kemudian Create Smartobjects
5. Lakukan Alter Stored Procedure dengan kode berikut:
   

   CREATE PROCEDURE [dbo].[MahasiswaGetList]
   @pkodemhs nvarchar(100) = ”,
   @pnama nvarchar(100) = ”,
   @ptgl_lahir date = ‘1900-01-01’,
   @pjurusan nvarchar(100) = ”,
   @SOP nvarchar(100) = ‘=-=-=-=’
   AS

   set @pkodemhs = LTRIM(RTRIM(@pkodemhs))
   set @pnama = LTRIM(RTRIM(@pnama))
   set @pjurusan = LTRIM(RTRIM(@pjurusan))

   set @sql = ‘SELECT ID, kodemhs, nama, tgl_lahir, jurusan
   FROM [dbo].[Mahasiswa]
   where 1 = 1 ‘

   if (LEN(@pkodemhs) > 0) begin
   set @OP = REVERSE(PARSENAME(REPLACE(REVERSE(@SOP), ‘-‘, ‘.’), 1))

   if @OP = ‘=’
   set @sql = @sql + ‘ and kodemhs ‘ + @OP + ‘ ”’ + @pkodemhs + ””

   if @OP = ‘Start With’
   set @sql = @sql + ‘ and kodemhs Like ”’ + @pkodemhs + ‘%”’

   if @OP = ‘Like’
   set @sql = @sql + ‘ and kodemhs ‘ + @OP + ‘ ”%’ + @pkodemhs + ‘%”’
   end
   if (LEN(@pnama) > 0) begin
   set @OP = REVERSE(PARSENAME(REPLACE(REVERSE(@SOP), ‘-‘, ‘.’), 2))

   if @OP = ‘=’
   set @sql = @sql + ‘ and nama ‘ + @OP + ‘ ”’ + @pnama + ””
   if @OP = ‘Start With’
   set @sql = @sql + ‘ and nama Like ”’ + @pnama + ‘%”’
   if @OP = ‘Like’
   set @sql = @sql + ‘ and nama ‘ + @OP + ‘ ”%’ + @pnama + ‘%”’
   end
   if (LEN(@ptgl_lahir ) > 0) and (@ptgl_lahir <> ‘1900-01-01’) begin
   set @OP = REVERSE(PARSENAME(REPLACE(REVERSE(@SOP), ‘-‘, ‘.’), 3))

   if @OP = ‘=’
   set @sql = @sql + ‘ and tgl_lahir ‘ + @OP + ”” + convert(varchar(10), @ptgl_lahir ) + ””
   if @OP = ‘Start With’
   set @sql = @sql + ‘ and tgl_lahir Like ”’ + convert(varchar(10), @ptgl_lahir ) + ‘%”’
   if @OP = ‘Like’
   set @sql = @sql + ‘ and tgl_lahir ‘ + @OP + ‘ ”%’ + convert(varchar(10), @ptgl_lahir ) + ‘%”’
   end

   if (LEN(@pjurusan) > 0) begin
   set @OP = REVERSE(PARSENAME(REPLACE(REVERSE(@SOP), ‘-‘, ‘.’), 4))

   if @OP = ‘=’
   set @sql = @sql + ‘ and jurusan ‘ + @OP + ”” + @pjurusan + ””
   if @OP = ‘Start With’
   set @sql = @sql + ‘ and jurusan Like ”’ + @pjurusan + ‘%”’
   if @OP = ‘Like’
   set @sql = @sql + ‘ and jurusan ‘ + @OP + ‘ ”%’ + @pjurusan + ‘%”’
   end

   execute (@sql)

    

6. Kemudian buatlah form dengan tampilan berikut:
   
7. Pada button Search tambahkan method List dengan parameter input seperti digambar
   
   

K2 secara default akan mendeteksi apakah stored procedure mendapatkan List method atau Execute method, namun terdapat kendala dalam mengenali Stored Procedure yang sedikit advance pada nomor Ke-5 mengakibatkan K2 memberikan method Execute walaupun yang kita inginkan adalah List method.

Oleh karena itu untuk mendapatkan List Method, pertama kali kita mengakali K2 dengan coding sql simple pada awal yaitu nomor ke-2, setelah dilakukan Refresh Service Instance pada Service Object di SmartObjects – Services – Tester dan mendapatkan List Method kemudian lakukan alter SP lagi di nomor Ke-5 tanpa melakukan Refresh Service Instance, sehingga K2 akan menganggap SP tersebut menggunakan List Method.

Kelemahan pada cara ini karena memaksakan method List apabila dilakukan Refresh service Instance maka K2 akan memberikan method Execute ke SP tersebut, dan kita harus mengakali SP dengan simple SP lagi.

File contoh di atas dapat didownload di sini

Cara Terbaru untuk menampilkan List Method

Setelah menghubungi pihak K2 berikut langkah untuk menampilkan List Method untuk complex SP

Create Parent SP yang mengakses SP MahasiswaGetList

CREATE PROCEDURE [dbo].[MahasiswaGetListParent]
@pkodemhs nvarchar(100) = ”,
@pnama nvarchar(100) = ”,
@ptgl_lahir date = ‘1900-01-01’,
@pjurusan nvarchar(100) = ”,
@SOP nvarchar(100) = ‘=-=-=-=’
AS

IF 1=0 BEGIN
SET FMTONLY OFF
END
exec [MahasiswaGetList] @pkodemhs,@pnama,@ptgl_lahir,@pjurusan,@SOP;
–SET FMTONLY OFF

Testing environment menggunakan

1. K2 versi 11
2. SQL Server
3. SQL Server Management Studio