/*
alter database Parsian set trustworthy on
GO
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
*/

Drop PROCEDURE xp_CheckPelak
GO
Drop PROCEDURE xp_reinsure
GO
Drop PROCEDURE xp_PayRequest
GO
Drop PROCEDURE xp_GetGroupState
GO
Drop PROCEDURE xp_EvasanInsertBN
GO
Drop PROCEDURE xp_EvasanInsertCANCEL
GO

Drop ASSEMBLY [SqlClassLibrary.XmlSerializers]
GO
Drop ASSEMBLY SqlClassLibrary
GO
CREATE ASSEMBLY SqlClassLibrary
FROM 'C:\Arabpoor\SqlClassLibrary.dll'
WITH PERMISSION_SET = UNSAFE
GO
CREATE ASSEMBLY [SqlClassLibrary.XmlSerializers]
FROM 'C:\Arabpoor\SqlClassLibrary.XmlSerializers.dll'
WITH PERMISSION_SET = SAFE;
GO
CREATE PROCEDURE xp_GetGroupState(
        @UserName nvarchar(50),
        @Passwrd nvarchar(50),
        @ConfirmDateFrom nvarchar(10),
        @ConfirmDateTo nvarchar(10),
        @aDataTable xml out)
AS EXTERNAL NAME SqlClassLibrary.StoredProcedures.xp_GetGroupState
GO
CREATE PROCEDURE xp_PayRequest( 
@UserName nvarchar(50),
@Passwrd nvarchar(50),
@RequestDate nvarchar(50),
@Cited nvarchar(50),
@Amount nvarchar(50),
@Reason nvarchar(Max),
@Receiver nvarchar(Max),
@AcountNo nvarchar(50),
@propertyCode nvarchar(50),
@UsingUnit nvarchar(50),
@RegisterNo nvarchar(50),
@FinancialCode nvarchar(50),
@TaxDocumentNo nvarchar(50),
@CompanyType nvarchar(50),
@Addres nvarchar(500),
@TelNo nvarchar(50),
@RequestUnitId nvarchar(50),
@RequestUserId nvarchar(50),
@RequestType nvarchar(50),
@Sign1 nvarchar(50),
@Sign2 nvarchar(50),
@Mobile nvarchar(50),
@uniquecode nvarchar(50),
@PayRequestResult nvarchar(50) out )
AS
EXTERNAL NAME SqlClassLibrary.StoredProcedures.xp_PayRequest
GO
GO
CREATE PROCEDURE xp_EvasanInsertBN( 
@UserName nVarchar(50),
@Password nVarchar(50),
@id nVarchar(50),
@bno nVarchar(50),
@sodurDate nVarchar(50),
@agentCode nVarchar(50),
@name nVarchar(150),
@lName nVarchar(150),
@codeMelli nVarchar(50),
@birthYear nVarchar(50),
@birthMonth nVarchar(50),
@birthDay nVarchar(50),
@sodurPlace nVarchar(50),
@city nVarchar(150),
@address nVarchar(250),
@passPortNo nVarchar(50),
@jens nVarchar(50),
@identityNo nVarchar(50),
@latinName nVarchar(150),
@latinLName nVarchar(150),
@hb nVarchar(50),
@hbArzi nVarchar(50),
@moddat nVarchar(50),
@moddatKind nVarchar(50),
@mSodurDate nVarchar(50),
@safarKind nVarchar(50),
@afzoodehMaliat nVarchar(50),
@arziTakhfifAmount nVarchar(50),
@coverLimit nVarchar(50),
@shahrdariAvarez nVarchar(50),
@takhfifAmount nVarchar(50),
@tel nVarchar(50),
@userId nVarchar(50),
@zone nVarchar(50),
@codeSodur nVarchar(50),       
@EvasanCode nvarchar(Max) out ,
@ErrMessage nvarchar(Max) out 
)
AS
EXTERNAL NAME SqlClassLibrary.StoredProcedures.xp_EvasanInsertBN
GO
GO
CREATE PROCEDURE xp_EvasanInsertCANCEL( 
@UserName nVarchar(50),
@Password nVarchar(50),
@id nVarchar(50),
@refrenceid nVarchar(50),
@bno nVarchar(50),
@cancelDate nVarchar(50),
@userId nVarchar(50),
@EvasanCode nvarchar(Max) out ,
@ErrMessage nvarchar(Max) out 
)
AS
EXTERNAL NAME SqlClassLibrary.StoredProcedures.xp_EvasanInsertCANCEL
GO

CREATE PROCEDURE xp_CheckPelak( 
@strVehicleno2left nchar(2), 
@strVehiclenoharf nchar(2), 
@StrVehicleNo3right nchar(3), 
@strvehiclenoseri nchar(2), 
@Username nvarchar(50), 
@Password nvarchar(50),
@LastName nvarchar(50) out )
AS
EXTERNAL NAME SqlClassLibrary.StoredProcedures.xp_CheckPelak
GO
CREATE PROCEDURE xp_reinsure( 
        @CmpDocNo nvarchar(30),
        @MapFldDtlCod nvarchar(30),
        @MapBrnhCod nvarchar(30),
        @MapAgncCod nvarchar(30),
        @MapBrkrCod nvarchar(30),
        @MapPrsnTypCod nvarchar(30),
        @InsNam nvarchar(30),
        @NtnlId nvarchar(30),
        @Tel nvarchar(30),
        @Mbl nvarchar(30),
        @ZipCod nvarchar(30),
        @IsuDte nvarchar(30),
        @BgnDte nvarchar(30),
        @EndDte nvarchar(30),
        @Prm nvarchar(30),
        @Cptl nvarchar(30),
        @Tax nvarchar(30),
        @Tol nvarchar(30),
        @MapCurCod nvarchar(30),
        @Isu nvarchar(30),
        @Wag nvarchar(30),
        @Rct nvarchar(30),
        
        
        @Id nvarchar(30)out ,
        @UnqCod nvarchar(30)out ,
        @ErrorCods nvarchar(30)out ,
        @ErrorNams nvarchar(300)out 
)
AS
EXTERNAL NAME SqlClassLibrary.StoredProcedures.xp_reinsure
GO
--Test xp_CheckPelak
/*
Declare @S as varchar(100);
EXEC xp_CheckPelak '99','07','598','11','as','ali',@S out
Select @S
*/
GO
--Test  xp_reinsure with SP_CMN_Reins_SendPolicy
--Test xp_PayRequest
/*Declare @S as varchar(100)='TTTT';
Exec xp_PayRequest  
/*@UserName*/'Simab',
/*@Passwrd*/'@Simab@',
/*@RequestDate*/'1392/06/26',
/*@Cited*/'12345',
/*@Amount*/'100',
/*@Reason*/'Test',
/*@Receiver*/'تست',
/*@AcountNo*/'890540101102010013102001',
/*@propertyCode*/'0',
/*@UsingUnit*/'0',
/*@RegisterNo*/'9',
/*@FinancialCode*/'0',
/*@TaxDocumentNo*/'0',
/*@CompanyType*/'1',
/*@Addres*/'تست',
/*@TelNo*/'2152125',
/*@RequestUnitId*/'111110',
/*@RequestUserId*/'1250',
/*@RequestType*/'17',
/*@Sign1*/'2053',
/*@Sign2*/'1038',
/*@Mobile*/ '09121111111',
/*@uniquecode*/ '392900000016846',
@S out 
Select @S
GO
-------Test xp_GetGroupState
Declare @S as XML;
Exec xp_GetGroupState @UserName='Simab',@Passwrd='@Simab@',@ConfirmDateFrom='1392/01/01', @ConfirmDateTo='1392/09/30' , @aDatatable= @S out
Select * From 
(
Select 
c.value('(TraceCode)[1]', 'varchar(50)') TraceCode 
,c.value('(PayState)[1]', 'varchar(50)') PayState
,c.value('(ConfirmDate)[1]', 'varchar(50)') ConfirmDate
,c.value('(Regid)[1]', 'varchar(50)') Regid
,c.value('(LocalState)[1]', 'varchar(50)') LocalState
,c.value('(SourceState)[1]', 'varchar(50)') SourceState
,c.value('(DestinationState)[1]', 'varchar(50)') DestinationState
,c.value('(ACHID)[1]', 'varchar(50)') ACHID
from @S.nodes('DocumentElement/Table') T(C) 
) X/*, V_CASH_Debit D 
Where D.DocID=X.TraceCode
*/

/*
Declare @EvasanCode nvarchar(Max) ,@ErrMessage nvarchar(Max)
Exec xp_EvasanInsertCANCEL 
Null, /*@UserName nVarchar(50),*/
Null,/*@Password nVarchar(50),*/
0 ,/*@id nVarchar(50),*/
624,/*@refrenceid nVarchar(50),*/
'2240/510490/93/000023',/*@bno nVarchar(50),*/
'1393/01/28',/*@cancelDate nVarchar(50),*/
100,/*@userId nVarchar(50),*/
@EvasanCode out ,
@ErrMessage out 
Select @EvasanCode ,@ErrMessage 



*/