Franchies Search Dynamic SQL Stored Procedure

-- =============================================                                                      
-- Author:  Javed khan                                                      
-- Create date: 25/12/2013                                                    
-- Description: for dynamic search of frnchies  
--Alter date: 01-03-2014
--Alter by : Me                                                    
-- =============================================  
CREATE procedure [dbo].[lsp_busSearchFrnchAdv]    
(    
 @UID int,    
 @ctgryid int,    
 @subctgryid int,    
 @subsubctgryid searchSubsubctgry readonly,    
 @mininvest int,    
 @curncyid int      
)    
as    
begin    
create table #t  
(  
 subsub int  
)  
insert into #t (subsub) select Tsubsubctgryid from @subsubctgryid  
declare @qrystr varchar(max)    
   
set @qrystr='select left(cmp.compname,26) as compname,left(cmp.compaddrs,26) as compaddrs,                    
''UserFiles/100/''+convert(varchar,cmp.usrid)+''/CompDealsOffer/''+convert(varchar,cmp.compid)+''.''+right(cmp.imgurl,3)+'''' as compImg,                    
left(isnull(frn.frnchmngr,''not available''),20) as frnchmngr,frn.frnchid    
,frn.mininvest,cntry1.crncyName as mincurncy,frn.maxinvest,cntry2.crncyName as maxcrncy                  
,frn.minarea,unt.UnitName,frn.commission,frn.commissionin,frn.frnchfee,cntry3.crncyName as feecrncy,                  
frn.frnchterm,frn.TandC,      
vw.name,vw.cityname,vw.image as usrimg,frn.usrid    
from tblfranchisee frn      
inner join tblbusofferComp cmp on frn.cmpnyid=cmp.compid      
inner join (select distinct compid,compctgryid,compsubctgryid,compsubsubctgryid from tblbusoffercompDtl) cmpdtl on frn.cmpnyid= cmpdtl.compid    
left join tblcurrency cntry1 on frn.mincurncy=cntry1.crncyid                
left join tblcurrency cntry2 on frn.maxcurncy=cntry2.crncyid              
               
left join tblcurrency cntry3 on frn.feecurncy=cntry3.crncyid            
left join tblUsrUnitStatic unt on frn.areaunit=unt.UnitId      
inner join vwfrndlistfld vw on frn.usrid=vw.usrid where frn.isdel=0'    
if(@ctgryid!='')    
set @qrystr=@qrystr+' and cmpdtl.compctgryid='+cast(@ctgryid as varchar(150))    
     
if(@subctgryid!='')    
set @qrystr=@qrystr+' and cmpdtl.compsubctgryid='+cast(@subctgryid as varchar(150))    
if((select count(*) from #t)>0)    
set @qrystr=@qrystr+' and cmpdtl.compsubsubctgryid in (select subsub from #t)'    
if(@mininvest!='')    
set @qrystr=@qrystr+' and frn.mininvest>='+cast(@mininvest as varchar(200))    
if(@curncyid!='')    
set @qrystr=@qrystr+' and frn.mincurncy='+cast(@curncyid as varchar(200))    
   
set @qrystr=@qrystr+' order by frn.cdate desc'    
   
exec (@qrystr)    
end 

Stored procedure dynamic sql

-- =============================================                                                      
-- Author:  Javed khan                                                      
-- Create date: 25/12/2013                                                    
-- Description: for dynamic search of frnchies  
--Alter date: 01-03-2014
--Alter by : Me                                                    
-- =============================================  
CREATE procedure [dbo].[lsp_busSearchFrnchAdv]    
(    
 @UID int,    
 @ctgryid int,    
 @subctgryid int,    
 @subsubctgryid searchSubsubctgry readonly,    
 @mininvest int,    
 @curncyid int      
)    
as    
begin    
create table #t  
(  
 subsub int  
)  
insert into #t (subsub) select Tsubsubctgryid from @subsubctgryid  
declare @qrystr varchar(max)    
   
set @qrystr='select left(cmp.compname,26) as compname,left(cmp.compaddrs,26) as compaddrs,                    
''UserFiles/100/''+convert(varchar,cmp.usrid)+''/CompDealsOffer/''+convert(varchar,cmp.compid)+''.''+right(cmp.imgurl,3)+'''' as compImg,                    
left(isnull(frn.frnchmngr,''not available''),20) as frnchmngr,frn.frnchid    
,frn.mininvest,cntry1.crncyName as mincurncy,frn.maxinvest,cntry2.crncyName as maxcrncy                  
,frn.minarea,unt.UnitName,frn.commission,frn.commissionin,frn.frnchfee,cntry3.crncyName as feecrncy,                  
frn.frnchterm,frn.TandC,      
vw.name,vw.cityname,vw.image as usrimg,frn.usrid    
from tblfranchisee frn      
inner join tblbusofferComp cmp on frn.cmpnyid=cmp.compid      
inner join (select distinct compid,compctgryid,compsubctgryid,compsubsubctgryid from tblbusoffercompDtl) cmpdtl on frn.cmpnyid= cmpdtl.compid    
left join tblcurrency cntry1 on frn.mincurncy=cntry1.crncyid                
left join tblcurrency cntry2 on frn.maxcurncy=cntry2.crncyid              
               
left join tblcurrency cntry3 on frn.feecurncy=cntry3.crncyid            
left join tblUsrUnitStatic unt on frn.areaunit=unt.UnitId      
inner join vwfrndlistfld vw on frn.usrid=vw.usrid where frn.isdel=0'    
if(@ctgryid!='')    
set @qrystr=@qrystr+' and cmpdtl.compctgryid='+cast(@ctgryid as varchar(150))    
     
if(@subctgryid!='')    
set @qrystr=@qrystr+' and cmpdtl.compsubctgryid='+cast(@subctgryid as varchar(150))    
if((select count(*) from #t)>0)    
set @qrystr=@qrystr+' and cmpdtl.compsubsubctgryid in (select subsub from #t)'    
if(@mininvest!='')    
set @qrystr=@qrystr+' and frn.mininvest>='+cast(@mininvest as varchar(200))    
if(@curncyid!='')    
set @qrystr=@qrystr+' and frn.mincurncy='+cast(@curncyid as varchar(200))    
   
set @qrystr=@qrystr+' order by frn.cdate desc'    
   
exec (@qrystr)    
end 

Insert Franchise inquery

-- Author:  Javed khan                                                    
-- Create date: 24/12/2013                                                  
-- Description: For Insert Inquery of user
---==========================================

Create proc [dbo].[lsp_enqinsert]    
(    
 @usrid bigint,    
 @frnchid bigint,    
 @enqcomnt varchar(1500),    
 @enqtype bit,    
 @n int output    
)    
as    
begin    
declare @isenq bit    
declare @comid varchar(max)  
if((select count(*) from tblbusfrnchenq where usrid= @usrid and frnchid=@frnchid and isdelsts=0)>0)    
begin    
set @isenq=0  
set @comid=(select distinct comid from tblbusfrnchenq where  usrid= @usrid and frnchid=@frnchid and isdelsts=0)  
end  
else  
begin  
if((select count(*) from  tblbusfrnchenq)=0)
set @comid=100
else
set @comid=(select max(comid)+1 from tblbusfrnchenq)--convert(varchar(max), @frnchid)+convert(varchar(max), @usrid)  
set @isenq=1    
 
end  
insert into tblbusfrnchenq(usrid,frnchid,enqcomnt,enqtype,isenq,comid) values(@usrid,@frnchid,@enqcomnt,@enqtype,@isenq,@comid)    
set @n=1    
end 

Java Script Enter Key Press Event

$(document).ready(function () {
        $('#txttndrsearch').keyup(function (e) {
            if (e.keyCode == 13) {
                if ($('#txttndrsearch').val() != '') {
                    searchclienttender();
                }
               
            }
        });
    });

Search Tender Client

-- =============================================                                                    
-- Author:  Javed khan                                                    
-- Create date: 26/03/2014                                                  
-- Description: For Searching Tender  for client
---==========================================
Alter procedure [dbo].[lsp_busSearchTndrclient] --159,'s'      
(      
 @usrid bigint,        
 @search varchar(100)        
)        
as
begin


select
(select COUNT (tl.usrid) from tblUsrLike tl where tl.shrtypId=27 and tl.usrShrid=tndr.Tid and tl.sts=1) as liked,      
(select tls.sts from tblUsrLike tls where tls.shrtypId=27 and tls.usrShrid=tndr.Tid and tls.usrid=@usrid) as likedsts,      
(select COUNT(td.usrid) from tblUsrDislike td where td.shrtypId=27 and td.usrShrid=tndr.Tid and td.sts=1) as disliked,      
(select tds.sts from tblUsrDislike tds where tds.shrtypId=27 and tds.usrShrid=tndr.Tid and tds.usrid=@usrid) as dislikedsts,


tndr.Ttitle,convert(varchar(20),tndr.CloseDate,105) as closedate
,sts.lName as stateName,
'UserFiles/100/'+convert(varchar,cmp.usrid)+'/CompDealsOffer/'+convert(varchar,cmp.compid)+'.'+right(cmp.imgurl,3)+'' as compImg
from tbltender tndr inner join tblbusofferComp cmp on tndr.cmpnyid=cmp.compid
inner join tblAddrsLevel1 sts on sts.id=tndr.stateid where tndr.usrid=@usrid
and tndr.Ttitle like @search+'%'
or convert(varchar(12),tndr.cdate,103) like  @search+'%'
or  sts.lName like @search+'%'
and tndr.isdel=0
order by tndr.cdate desc

end   

Jitendrasinghblogs: How to know table created date

Jitendrasinghblogs: How to know table created date: SELECT create_date FROM sys.tables WHERE name='tblUsrShrProp' SELECT          [ name ]         ,create_date     ...

Select Proc For Business Offer

-- =============================================                                  
-- Author:  Javed khan                                  
-- Create date: 29/10/2013                                
-- Description: To select the value from tblbusoffers table        
--Alter date:29/11/2013        
--Altered by: Me  
--Alter date:28/02/2014        
--Altered by: Me                                
-- =============================================                                  
CREATE PROCEDURE [dbo].[lsp_BusSelectoffer]              
(                                  
 @OpType tinyint,                        
 @offerId bigint,                  
 @usrid bigint                          
)                                  
AS                                  
begin                      
if(@optype=1)                      
select    
(select COUNT (tl.usrid) from tblUsrLike tl where tl.shrtypId=25 and tl.usrShrid=bo.offerId and tl.sts=1) as liked,    
(select tls.sts from tblUsrLike tls where tls.shrtypId=25 and tls.usrShrid=bo.offerId and tls.usrid=@usrid) as likedsts,    
(select COUNT(td.usrid) from tblUsrDislike td where td.shrtypId=25 and td.usrShrid=bo.offerId and td.sts=1) as disliked,    
(select tds.sts from tblUsrDislike tds where tds.shrtypId=25 and tds.usrShrid=bo.offerId and tds.usrid=@usrid) as dislikedsts,    
   
bo.offerid,left(bo.productName,18) as name,left(oc.compaddrs,22) as compaddrs,bo.discount,left(bo.offertitle,23) as offertitle,left(bo.offerdescrptn,185) as offerdescrptn,'UserFiles/100/'+convert(varchar,vw.usrid)+'/DealsOffer/'+convert(varchar,bo.  
     
       
         
offerid)+'.'+right(bo.pimg,3)+'' as imgPath from vwfrndlistfld vw                
inner join tblbusofferComp oc                  
on oc.usrid=vw.usrid                      
inner join tblbusoffers bo on bo.compid=oc.compid where bo.usrid=@usrid and bo.isdel=0 order by bo.cdate desc                  
               
if(@optype=2)                      
select    
(select COUNT (tl.usrid) from tblUsrLike tl where tl.shrtypId=25 and tl.usrShrid=bo.offerId and tl.sts=1) as liked,    
(select tls.sts from tblUsrLike tls where tls.shrtypId=25 and tls.usrShrid=bo.offerId and tls.usrid=@usrid) as likedsts,    
(select COUNT(td.usrid) from tblUsrDislike td where td.shrtypId=25 and td.usrShrid=bo.offerId and td.sts=1) as disliked,    
(select tds.sts from tblUsrDislike tds where tds.shrtypId=25 and tds.usrShrid=bo.offerId and tds.usrid=@usrid) as dislikedsts,    
     
 bo.offerid,oc.compid,oc.compName,oc.estblst,oc.compaddrs,oc.compweb,oc.compcontact,              
'UserFiles/100/'+convert(varchar,@usrid)+'/CompDealsOffer/'+convert(varchar,bo.compid)+'.'+right(oc.imgurl,3)+'' as compImg              
,              
[dbo].[fn_Bussubsubcat](bo.compid)as subsubctgry,              
dtl.compctgryid,dc.subctgryname,ctg.ctgname,dtl.compctgryid,              
bo.offerType,bo.productname,pct.prdctName as prdcat,psct.prdctsubName as prdsubcat,bo.offerPrice,                      
cntry.crncyName as currencyname,bo.discount,'UserFiles/100/'+convert(varchar,@usrid)+'/DealsOffer/'+convert(varchar,bo.offerid)+'.'+right(bo.pimg,3)+'' as imgPath              
,bo.offertitle,              
bo.offerFromTime,bo.offerToTime,bo.offertitle,bo.offerdescrptn                        
,ct.cityname,bo.noOfCouphons,convert(varchar(12), bo.offerVtill,101) as validDate ,bo.offerDescrptn,bo.trmsCondton                      
from tblbusofferComp oc                      
inner join tblbusoffers bo on bo.compid=oc.compid              
inner join (select distinct compid, compsubctgryid,compctgryid from tblbusoffercompDtl) dtl on oc.compid=dtl.compid                
inner join tblUsrPrflSubCtgry dc on dtl.compsubctgryid=dc.usrprflsubctgryid                
inner join tblusrprflctgry ctg on dtl.compctgryid=ctg.usrprflcatgryid              
inner join tblcurrency cntry on bo.curnytype=cntry.crncyid                
inner join tblcity ct on bo.offerLoctn=ct.cityid              
inner join tblbusprdctCtgry pct on bo.productcat=pct.prdctid              
inner join tblbusprdctSubCtgry psct on bo.productsubcat=psct.prdsubid              
where offerid=@offerId            
if(@optype=3)        
select top 3 bo.offerid,oc.compid,'UserFiles/100/'+convert(varchar,bo.usrid)+'/DealsOffer/'+convert(varchar,bo.offerid)+'.'+right(bo.pimg,3)+'' as imgPath        
,bo.productname,ct.cityname,bo.discount,oc.compcontact from tblbusoffers bo        
inner join tblbusofferComp oc on oc.compid=bo.compid        
inner join tblcity ct on bo.offerLoctn=ct.cityid        
where bo.isdel=0 and  bo.offerLoctn=(select city from tblusrdtl where usrid=@usrid)        
and bo.offerid not in (select top (@offerId) bo.offerid from tblbusoffers bo where bo.isdel=0 and bo.offerLoctn=(select city from tblusrdtl where usrid=@usrid))      
order by bo.cdate        
end 

http://www.codeproject.com/Articles/49938/What-is-New-in-ASP-NET-Visual-Studio-IDE