Comments by Member 11882351 (Top 8 by date)

USE [NihcaNov18]
GO
/****** Object: StoredProcedure [dbo].[sp_Member_selectIndexPagingNew2Count] Script Date: 2/12/2019 12:04:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- [sp_Member_selectIndexPagingNew2Count] 1,50,'arabe','','','','0','0','0','0','0','01/01/1900', '01/01/1900',0,0,0, 'order by Memberlastname asc','-1'
ALTER PROCEDURE [dbo].[sp_Member_selectIndexPagingNew2Count]
@PageIndex INT = 1,
@PageSize INT = 10,
@Search nvarchar(max),
@SearchLastName nvarchar(max),
@clientid nvarchar(max),
@barcode nvarchar(max),
@hasEmail nvarchar(max),
@hasContract nvarchar(max),
@hasAccount nvarchar(max),
@hasPolicy nvarchar(max),
@hasGroup nvarchar(max),
@FromDate date,
@ToDate date,
@clublocationid int,
@insuaranceid int,
@memberstatusid int,
@Sort nvarchar(max),
@IsVerified int


AS
BEGIN
SET NOCOUNT ON;


declare @recordCount nvarchar(max) set @recordCount = '0'
declare @qry1 nvarchar(max)

set @qry1 = ' select count(*)
from Member m
where
(m.memberstatusid = ' + Convert(varchar,@memberstatusid) + ' or ' + Convert(varchar,@memberstatusid) + ' = 0) and
(m.memberfirstname like ''%'+@Search+'%'' or m.memberemailid like ''%'+@Search+'%'' or '''+@Search+''' = '''')
and (m.MemberLastName like ''%'+@SearchLastName+'%'' or '''+@SearchLastName+''' = '''')
and (m.memberid in
(select mcl.memberid from memberclublocation mcl where isnull(mcl.isdeleted,0)=0 and (mcl.clublocationid = '''+Convert(varchar,@Clublocationid)+''' or '''+Convert(varchar,@Clublocationid)+'''=''0'') and
(mcl.barcode like ''%'+@barcode+'%'' or '''+@barcode+''' = '''')
and mcl.clublocationid in (select cl.clublocationid from clublocation cl where cl.clientid = '''+@clientid+''' or '''+@clientid+''' = ''''))
or ('''+Convert(varchar,@Clublocationid)+'''=''0'' and '''+@barcode+''' = '''' and '''+@clientid+''' = ''''))
and (
((MemberEmailid <> '''' and MemberEmailid is not null) and ''1'' = '+@hasEmail+')
or ((MemberEmailid = '''' or MemberEmailid is null) and ''2'' = '+@hasEmail+')
or ''0'' = '+@hasEmail+') and(
( m.memberid in (select ma.memberid from MemberAgreement ma) and '+@hasContract+' = ''1'')
or ( m.memberid not in (select ma.memberid from MemberAgreement ma) and '+@hasContract+' = ''2'')
or '+@hasContract+' = ''0''
) and (
((m.MaskedAccountNumber <> '''' and m.MaskedAccountNumber is not null) and '+@hasAccount+' = ''1'')
or ((m.MaskedAccountNumber = '''' or m.MaskedAccountNumber is null) and '+@hasAccount+' = ''2'')
or '+@hasAccount+' = ''0''
) and (((m.PolicyNo <> '''' and m.PolicyNo is not null) and '+@hasPolicy+' = ''1'')
or ((m.PolicyNo = '''' or m.PolicyNo is null) and '+@hasPolicy+' = ''2'') or '+@hasPolicy+' = ''0'')

USE [NihcaNov18]
GO
/****** Object: StoredProcedure [dbo].[sp_Member_selectIndexPagingNew2] Script Date: 2/12/2019 12:03:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- sp_Member_selectIndexPagingNew2 1,50,'arabe','','','','0','0','0','0','0','01/01/1900', '01/01/1900',0,0,0, 'order by Memberlastname asc','-1'
ALTER PROCEDURE [dbo].[sp_Member_selectIndexPagingNew2]
@PageIndex INT = 1,
@PageSize INT = 10,
@Search nvarchar(max),
@SearchLastName nvarchar(max),
@clientid nvarchar(max),
@barcode nvarchar(max),
@hasEmail nvarchar(max),
@hasContract nvarchar(max),
@hasAccount nvarchar(max),
@hasPolicy nvarchar(max),
@hasGroup nvarchar(max),
@FromDate date,
@ToDate date,
@clublocationid int,
@insuaranceid int,
@memberstatusid int,
@Sort nvarchar(max),
@IsVerified int

AS
BEGIN
SET NOCOUNT ON;

declare @recordCount nvarchar(max) set @recordCount = '0'
declare @qry1 nvarchar(max)

--select @recordCount = count(1) from Member
--where
--(memberfirstname like @barcode or memberlastname like '%'+@Search+'%' or memberemailid like ''%'+@Search+'%'' or @Search = '')
--and (memberid in (select mclo.memberid from memberclublocation mclo
-- where isnull(mclo.isdeleted,0) = 0 and (mclo.barcode = @barcode or @barcode = '') and mclo.clublocationid in
-- (select cl.clublocationid from clublocation cl where isnull(cl.isdeleted,0) = 0 and (cl.clientid = @clientid or @clientid = '') and (cl.clublocationid = @clublocationid or @clublocationid = 0) )
-- and mclo.clublocationid in (select ic.clubid from insuranceclub ic where (ic.insuranceid = @insuaranceid or @insuaranceid = 0) and isnull(ic.isdeleted,0) = 0)
-- ) )
--and (
--(isnull(MemberEmailid,'') <> '' and '1' = @hasEmail)
-- or (isnull(MemberEmailid,'') = '' and '2' = @hasEmail)
-- or '0' = @hasEmail
-- )
-- and(
-- ( member.memberid in (select ma.memberid from MemberAgreement ma) and @hasContract = '1' )
-- or ( member.memberid not in (select ma.memberid from MemberAgreement ma) and @hasContract = '2' )
-- or @hasContract = '0'

USE [NihcaNov18]
GO

/****** Object: Table [dbo].[Member] Script Date: 2/12/2019 12:00:14 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Member](
[MemberId] [int] IDENTITY(1,1) NOT NULL,
[MemberFirstName] [nvarchar](50) NULL,
[MemberLastName] [nvarchar](50) NULL,
[MemberMiddleInitial] [nvarchar](50) NULL,
[MemberAddressLine1] [nvarchar](100) NULL,
[MemberAddressLine2] [nvarchar](100) NULL,
[MemberAddressCity] [nvarchar](100) NULL,
[MemberAddressState] [nvarchar](100) NULL,
[MemberAddressZip] [varchar](10) NULL,
[MemberDateOfBirth] [datetime] NULL,
[MemberEmailId] [nvarchar](100) NULL,
[MemberPassword] [nvarchar](500) NULL,
[MemberRegisteredOn] [datetime] NULL,
[ClubLocationId] [int] NULL,
[IsActive] [bit] NULL,
[IsVerified] [bit] NULL,
[ForcePasswordChange] [bit] NULL,
[BankAccountName] [nvarchar](100) NULL,
[BankAccountNumber] [varchar](50) NULL,
[MaskedAccountNumber] [varchar](max) NULL,
[AccountType] [varchar](10) NULL,
[BankId] [int] NULL,
[SubmitCreditToClub] [bit] NULL,
[IsActiveAccount] [bit] NULL,
[AccountErrorDate] [datetime] NULL,
[AccountErrorDescription] [nvarchar](500) NULL,
[AccountInformationLastChangedOn] [datetime] NULL,
[policyno] [nvarchar](max) NULL,
[GroupNumber] [nvarchar](max) NULL,
[InsuranceCompanyID] [int] NULL,
[InsuranceInformationChangeDate] [datetime] NULL,
[MemberStatusID] [int] NULL,
[PhoneNumber] [nvarchar](50) NULL,
[CellNumber] [nvarchar](50) NULL,
[DependentId] [nvarchar](max) NULL,
[maskedpolicyno] [nvarchar](max) NULL,
[IsDeleted] [bit] NULL,
[LastAddedBy] [int] NULL,
[LastUpdatedOn] [datetime] NULL,
[CancelReason] [nvarchar](max) NULL,
[GuidId] [nvarchar](200) NULL,
[GuidUsed] [bit] NULL,
[GuidGenerated] [datetime] NULL,
[GuidIsUsed] [datetime] NULL,
[UpdatedBy] [int] NULL,
[DuesAmount] [money] NULL,
[AddedOn] [datetime] NULL,
[RewardsEnrollmentDate] [datetime] NULL,
[TempBSMemberID] [int] NULL,
[UpdateFrom] [nvarchar](max) NULL,
[Notes] [nvarchar](max) NULL,
[ResubmittedEffectiveDate] [datetime] NULL,
[PastClaims] [bit] NULL,
CONSTRAINT [PK_Member] PRIMARY KEY CLUSTERED
(
[MemberId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Member] WITH CHECK ADD CONSTRAINT [FK_Member_BankInformation1] FOREIGN KEY([BankId])
REFERENCES [dbo].[BankInformation] ([BankId])
GO

ALTER TABLE [dbo].[Member] CHECK CONSTRAINT [FK_Member_BankInformation1]
GO

ALTER TABLE [dbo].[Member] WITH CHECK ADD CONSTRAINT [FK_Member_InsuranceCompany] FOREIGN KEY([InsuranceCompanyID])
REFERENCES [dbo].[InsuranceCompany] ([InsuranceCompanyId])
GO

ALTER TABLE [dbo].[Member] CHECK CONSTRAINT [FK_Member_InsuranceCompany]
GO

ALTER TABLE [dbo].[Member] WITH CHECK ADD CONSTRAINT [FK_Member_MemberStatus] FOREIGN KEY([MemberStatusID])
REFERENCES [dbo].[MemberStatus] ([MemberStatusId])
GO

ALTER TABLE [dbo].[Member] CHECK CONSTRAINT [FK_Member_MemberStatus]
GO

public List<memberclass> GetMemberData(int pageIndex, int PageSize, string Search, string SearchLastname, string Clientid, string Barcode, string hasEmail, string hasContract, string hasAccountNo, string hasPolicyNo, string hasGroupNo, string From, string To, Int32 clublocationid, Int32 insuaranceid, Int32 MemberstatusId, string Sort, string IsVerified, Int32 TotalRecs)
{
try
{
DateTime FromNew = Convert.ToDateTime(From);
DateTime ToNew = Convert.ToDateTime(To);
//Int32 changeid = Convert.ToInt32(OBMI.GlobalModels.GlobalUtils.UserClubChainId);
obj_con.clearParameter();
obj_con.addParameter("@PageIndex", pageIndex);
obj_con.addParameter("@PageSize", PageSize);
obj_con.addParameter("@Search", Search);
obj_con.addParameter("@SearchLastName", SearchLastname.Trim());
obj_con.addParameter("@clientid", Convert.ToString(Clientid).Trim());
obj_con.addParameter("@barcode", Barcode.Trim());
obj_con.addParameter("@hasEmail", hasEmail);
obj_con.addParameter("@hasContract", hasContract);
obj_con.addParameter("@hasAccount", hasAccountNo);
obj_con.addParameter("@hasPolicy", hasPolicyNo);
obj_con.addParameter("@hasGroup", hasGroupNo);
obj_con.addParameter("@FromDate", FromNew);
obj_con.addParameter("@ToDate", ToNew);
obj_con.addParameter("@clublocationid", clublocationid);
obj_con.addParameter("@insuaranceid", insuaranceid);
//obj_con.addParameter("@ClubId", changeid);
//obj_con.addParameter("@userid", Convert.ToInt64(OBMI.GlobalModels.GlobalUtils.UserId));
obj_con.addParameter("@memberstatusid", MemberstatusId);
obj_con.addParameter("@Sort", Sort);
obj_con.addParameter("@IsVerified", IsVerified);
// obj_con.addParameter("@PageCount", 4, DBTrans.Update);
DataTable dt = ConvertDatareadertoDataTable(obj_con.ExecuteReader("sp_Member_selectIndexPagingNew2", CommandType.StoredProcedure));
obj_con.CommitTransaction();
obj_con.closeConnection();
return ConvertToList1MemberData(dt, TotalRecs);
}
catch (Exception ex)
{
throw new Exception("sp_Member_selectIndexPagingNew2");
}
}