SQL Query I have used
alter PROC [dbo].[APP_SELECT_LEADS_REPORT]
@Search nvarchar(100) = null,
@FromDate datetime = null,
@ToDate datetime = null,
@Village_ID bigint = null,
@Plant_ID bigint = null,
@County_ID bigint = null,
@Location_ID bigint = null
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
Declare @SQL nvarchar(max);
Declare @fname nvarchar(100);
Declare @lname nvarchar(100);
--Replace Prefix
Set @Search = REPLACE(@Search, 'OP-', '');
Set @Search = REPLACE(@Search, 'FN-', '');
--Split First name
SET @fname = SUBSTRING(@Search, 1, CASE WHEN CHARINDEX(' ', @Search)>0 THEN CHARINDEX(' ',
@Search) - 1 ELSE LEN(@Search) END );
SET @lname = REVERSE(SUBSTRING(REVERSE(@Search), 1, CASE WHEN CHARINDEX(' ',
REVERSE(@Search))>0 THEN CHARINDEX(' ', REVERSE(@Search)) - 1 ELSE LEN(REVERSE(@Search))
END ) );
--Start Get All Leads With Details
SET @SQL = N'SELECT 1 as ''response'', ''Leads to Prospect'' as message, ROW_NUMBER()
OVER(ORDER BY L.Lead_Id ASC) AS [Index],
L.National_ID, ltrim(rtrim(L.First_name + '' '' + isnull(L.Last_name, ''''))) as [Name], convert(DATE,
L.Date_of_birth, 104) as [DOB],
L.Contact_no,L.Gender,L.Physical_address,''OP-'' + convert(nvarchar, L.Lead_Id) OP_Number,
P.Title as [Plant], P.[Location], Z.Zone_Name, C.County_Name, SC.SubCounty_Name,
R.Region_Name,Z.Zone_Name,
L.Landmark, L.Field_zone,L.Sub_location, L.Field_Village_located,
Loc.Location_Name,V.Village_Name,L.Access_type, L.
[Distance_of_field],ISNULL( L.Opp_commited_land_acres,''0'') AS
Opp_commited_land_acres,L.Additional_data,
L.[Opp_land_type], L.[Opp_land_shape],L.[Opp_land_status], L.[Opp_type_soil], L.[Opp_land_acres],
L.[Opp_field_category], L.[Opp_crops_on_land],
convert(nvarchar, L.Created_On, 106) as [Date],MU.FirstName+ '' '' + MU.LastName As CreatedUser,
CASE
WHEN L.Is_Approve = 1 THEN ''Approved''
WHEN L.Is_Approve = 2 THEN ''Rejected''
WHEN L.Is_Approve = 3 THEN ''Revert''
ELSE ''Not Approved''
END AS [Status] ,L.Approve_Remarks,L.Is_Company,L.Cordinates
From [dbo].[MD_LEAD_MASTER_DATA] L
LEFT JOIN [dbo].[MD_LOCATION] LOC ON L.[Location] = Loc.Location_ID and LOC.Is_Active=1
INNER JOIN [dbo].[MD_VILLAGES] V ON L.Village = V.Village_ID and V.Is_Active=1
LEFT JOIN MD_SUBLOCATION SL on V.SubLocation_ID=SL.SubLocation_ID and V.Is_Active=1
LEFT JOIN MD_SECTION SE on SE.Section_ID=SL.Section_ID and SE.Is_Active=1
LEFT JOIN MD_ZONE Z on Z.Zone_ID=SE.ZONE_ID and Z.Is_Active=1
LEFT JOIN MD_REGION R on R.Region_ID=Z.Region_ID and R.IS_Active=1
LEFT join MD_DIVISION DV on DV.Division_ID=LOC.Division_ID and DV.IS_Active=1
LEFT Join MD_SUBCOUNTY SC on SC.SubCounty_ID=DV.SubCounty_ID and SC.IS_Active=1
LEFT Join MD_COUNTY C on C.County_ID=SC.County_ID and C.IS_Active=1
LEFT Join MD_PLANTS P on P.Plant_ID=C.Plant_ID and P.IS_Active=1
LEFT JOIN MD_USER MU on L.Created_By = MU.User_ID
WHERE L.Is_Active=1';
--Search
IF (@Search is not null and @Search <> '')
BEGIN
SET @SQL = @SQL + ' and (L.National_ID LIKE ''%' + @Search + '%'' or L.First_name LIKE ''%' +
@fname + '%'' or L.Last_name LIKE ''%' + @lname + '%'' or L.Lead_Id LIKE ''%' + @Search + '%'' )';
END
--From and To Date
IF ((@FromDate is not null and @FromDate <> '') and (@ToDate is not null and @ToDate <> ''))
BEGIN
SET @SQL = @SQL + ' and (L.Created_On >= '''+ convert(nvarchar, @FromDate, 111) +''' and
L.Created_On <= '''+ convert(nvarchar, @ToDate, 111) +''')';
END
--Village
IF (@Village_ID is not null and @Village_ID <> '')
BEGIN
SET @SQL = @SQL + ' and (L.Village = '+ convert(nvarchar, @Village_ID) +')';
END
--Plant
IF (@Plant_ID is not null and @Plant_ID <> '')
BEGIN
SET @SQL = @SQL + ' and (V.Plant_ID = '+ convert(nvarchar, @Plant_ID) +')';
END
--County
IF (@County_ID is not null and @County_ID <> '')
BEGIN
SET @SQL = @SQL + ' and (C.County_ID = '+ convert(nvarchar, @County_ID) +')';
END
--Location
IF (@Location_ID is not null and @Location_ID <> '')
BEGIN
SET @SQL = @SQL + ' and (L.Location = '+ convert(nvarchar, @Location_ID) +')';
END
EXEC(@SQL);
--End Get All Leads With Details
--Get All Leads to Opportunity With Bussiness Partner Details
SET @SQL = N'SELECT 1 as ''response'', ''Leads to Opportunity'' as message, ROW_NUMBER()
OVER(ORDER BY L.Lead_Id ASC) AS [Index],
L.National_ID, ltrim(rtrim(L.First_name + '' '' + isnull(L.Last_name, ''''))) as [Name],
L.Contact_no, L.Physical_address,''OP-'' + convert(nvarchar, L.Lead_Id) OP_Number,
P.Title as [Plant], P.[Location], Z.Zone_Name, C.County_Name, R.Region_Name,
L.Landmark, L.Field_zone, L.Field_Village_located, Loc.Location_Name,V.Village_Name, L.
[Distance_of_field],
L.[Opp_land_type], L.[Opp_land_shape],L.[Opp_land_status], L.[Opp_type_soil], L.[Opp_land_acres],
L.[Opp_field_category], L.[Opp_crops_on_land],
convert(nvarchar, L.Created_On, 106) as [Date]
From [dbo].[DT_LEAD_MASTER_TRANSACT] L
INNER JOIN [dbo].[MD_BUS_PARTNER_MASTER] BP ON L.National_ID=BP.ID_Number
LEFT JOIN [dbo].[MD_LOCATION] LOC ON L.[Location] = Loc.Location_ID and LOC.Is_Active=1
INNER JOIN [dbo].[MD_VILLAGES] V ON L.Village = V.Village_ID and V.Is_Active=1
LEFT JOIN MD_SUBLOCATION SL on V.SubLocation_ID=SL.SubLocation_ID and V.Is_Active=1
LEFT JOIN MD_SECTION SE on SE.Section_ID=SL.Section_ID and SE.Is_Active=1
LEFT JOIN MD_ZONE Z on Z.Zone_ID=SE.ZONE_ID and Z.Is_Active=1
LEFT JOIN MD_REGION R on R.Region_ID=Z.Region_ID and R.IS_Active=1
LEFT join MD_DIVISION DV on DV.Division_ID=LOC.Division_ID and DV.IS_Active=1
LEFT Join MD_SUBCOUNTY SC on SC.SubCounty_ID=DV.SubCounty_ID and SC.IS_Active=1
LEFT Join MD_COUNTY C on C.County_ID=SC.County_ID and C.IS_Active=1
LEFT Join MD_PLANTS P on P.Plant_ID=C.Plant_ID and P.IS_Active=1
WHERE L.Is_Active=1 and L.Is_Approve=1';
--Search
IF (@Search is not null and @Search <> '')
BEGIN
SET @SQL = @SQL + ' and (L.National_ID LIKE ''%' + @Search + '%'' or L.First_name LIKE ''%' +
@fname + '%'' or L.Last_name LIKE ''%' + @lname + '%'' or L.Lead_Id LIKE ''%' + @Search + '%'' )';
END
--From and To Date
IF ((@FromDate is not null and @FromDate <> '') and (@ToDate is not null and @ToDate <> ''))
BEGIN
SET @SQL = @SQL + ' and (L.Created_On >= '''+ convert(nvarchar, @FromDate, 111) +''' and
L.Created_On <= '''+ convert(nvarchar, @ToDate, 111) +''')';
END
--Village
IF (@Village_ID is not null and @Village_ID <> '')
BEGIN
SET @SQL = @SQL + ' and (L.Village = '+ convert(nvarchar, @Village_ID) +')';
END
--Plant
IF (@Plant_ID is not null and @Plant_ID <> '')
BEGIN
SET @SQL = @SQL + ' and (V.Plant_ID = '+ convert(nvarchar, @Plant_ID) +')';
END
--County
IF (@County_ID is not null and @County_ID <> '')
BEGIN
SET @SQL = @SQL + ' and (C.County_ID = '+ convert(nvarchar, @County_ID) +')';
END
--Location
IF (@Location_ID is not null and @Location_ID <> '')
BEGIN
SET @SQL = @SQL + ' and (L.Location = '+ convert(nvarchar, @Location_ID) +')';
END
EXEC(@SQL);
--End Get All Leads to Opportunity With Bussiness Partner Details
--Start Get All Leads to Opportunity With Field Attribute Details
SET @SQL = N'SELECT 1 as ''response'', ''Opportunity Leads With Field Attribute'' as message,
ROW_NUMBER() OVER(ORDER BY L.Lead_Id ASC) AS [Index],
L.National_ID, ltrim(rtrim(L.First_name + '' '' + isnull(L.Last_name, ''''))) as [Name],
L.Contact_no, L.Physical_address,''OP-'' + convert(nvarchar, L.Lead_Id) OP_Number, ''FN-'' +
convert(nvarchar, L.Lead_Id) Field_Number,
P.Title as [Plant], P.[Location], Z.Zone_Name, C.County_Name, R.Region_Name,
L.Landmark, L.Field_zone, L.Field_Village_located, Loc.Location_Name,V.Village_Name, L.
[Distance_of_field],
L.[Opp_land_type], L.[Opp_land_shape],L.[Opp_land_status], L.[Opp_type_soil], L.[Opp_land_acres],
L.[Opp_field_category], L.[Opp_crops_on_land],
LT.Land_type,LS.Land_shape,LST.Land_status,ST.Soil_type, SN.Soil_Nature, STE.Soil_Texture,STEE.
[Steepness],BPLA.[Total_Land_Area],
FC.Field_category,BPLA.[Other_Crops_On_Land],DC.Drainage_Condition,BPLA.[Soil_Depth],BPLA.
[Soil_Color],BPLA.[Measured_Cane_Area],BPLA.[No_Of_Fields],
BPLA.[Climatic_Conditions],BPLA.[Area_Under_Cultivation],UOM.[Short_Code],BPLOC.[PlotNo],
BPLOC.[Landmark],BPLOC.[Cordinates], PVD.[Distance] + '' '' + convert(nvarchar, UOM1.Short_Code)
Distance, CT.Code as CropType, MM.[Description],
convert(nvarchar, BPLA.[Date_Of_Planting], 106) as [Date_Of_Planting], convert(nvarchar,
L.Created_On, 106) as [Date]
From [dbo].[DT_LEAD_MASTER_TRANSACT] L
INNER JOIN [dbo].[MD_BUS_PARTNER_MASTER] BP ON L.National_ID=BP.ID_Number
INNER JOIN [dbo].[DT_BUS_PARTNER_LAND_ATTRIBUTES] BPLA ON
L.Lead_Id=BPLA.LeadOpportunity_ID and BP.Bus_Partner_ID=BPLA.Bus_Partner_ID
INNER JOIN [dbo].[DT_BUS_PARTNER_LOCATION_ATTRIBUTES] BPLOC ON
L.Lead_Id=BPLOC.LeadOpportunity_ID and BPLA.Land_Attributes_ID=BPLOC.Land_Attributes_ID
LEFT JOIN [dbo].[MD_LOCATION] LOC ON L.[Location] = Loc.Location_ID and LOC.Is_Active=1
INNER JOIN [dbo].[MD_VILLAGES] V ON L.Village = V.Village_ID and V.Is_Active=1
LEFT JOIN MD_SUBLOCATION SL on V.SubLocation_ID=SL.SubLocation_ID and V.Is_Active=1
LEFT JOIN MD_SECTION SE on SE.Section_ID=SL.Section_ID and SE.Is_Active=1
LEFT JOIN MD_ZONE Z on Z.Zone_ID=SE.ZONE_ID and Z.Is_Active=1
LEFT JOIN MD_REGION R on R.Region_ID=Z.Region_ID and R.IS_Active=1
LEFT join MD_DIVISION DV on DV.Division_ID=LOC.Division_ID and DV.IS_Active=1
LEFT Join MD_SUBCOUNTY SC on SC.SubCounty_ID=DV.SubCounty_ID and SC.IS_Active=1
LEFT Join MD_COUNTY C on C.County_ID=SC.County_ID and C.IS_Active=1
LEFT Join MD_PLANTS P on P.Plant_ID=C.Plant_ID and P.IS_Active=1
INNER JOIN [dbo].[MD_FIELD_TYPE] FT ON BPLOC.Field_Type_ID = FT.Field_Type_ID and
FT.Is_Active=1
INNER JOIN [dbo].[MD_PLANT_VILLAGE_DISTANCE] PVD ON BPLOC.Distance_ID = PVD.Distance_ID
INNER JOIN [dbo].[MD_UOM] UOM1 ON PVD.[UOM_ID]=UOM1.[UOM_ID] and UOM1.Is_Active=1
INNER JOIN [dbo].[MD_LAND_TYPE] LT ON L.Opp_land_type = LT.Land_type_id and LT.Is_Active=1
INNER JOIN [dbo].[MD_LAND_SHAPE] LS ON L.Opp_land_shape = LS.Land_shape_id and
LS.Is_Active=1
INNER JOIN [dbo].[MD_LAND_STATUS] LST ON L.Opp_land_status = LST.Land_status_id and
LST.Is_Active=1
INNER JOIN [dbo].[MD_SOIL_TYPE] ST ON L.Opp_type_soil = ST.Soil_type_id and ST.Is_Active=1
INNER JOIN [dbo].[MD_FIELD_CATEGORY] FC ON L.Opp_field_category = FC.Field_category_id and
FC.Is_Active=1
INNER JOIN [dbo].[MD_SOIL_NATURE] SN ON BPLA.SOIL_NATURE_ID = SN.SOIL_NATURE_ID and
SN.Is_Active=1
INNER JOIN [dbo].[MD_SOIL_TEXTURE] STE ON BPLA.SOIL_TEXTURE_ID = STE.SOIL_TEXTURE_ID and
STE.Is_Active=1
INNER JOIN [dbo].[MD_STEEPNESS] STEE ON BPLA.Steepness_ID = STEE.Steepness_ID and
STEE.Is_Active=1
INNER JOIN [dbo].[MD_CROP_TYPE] CT ON BPLA.Crop_Type_ID = CT.Crop_Type_ID and
CT.Is_Active=1
INNER JOIN [dbo].[MD_DRAINAGE_CONDITION] DC ON BPLA.Drainage_Condition_ID =
DC.Drainage_Condition_ID and DC.Is_Active=1
INNER JOIN [dbo].[MD_MATERIAL_MASTER] MM ON BPLA.Material_Master_ID =
MM.Material_Master_ID and MM.Is_Active=1
INNER JOIN [dbo].[MD_UOM] UOM ON BPLA.[UOM_ID] = UOM.UOM_ID and UOM.Is_Active=1
WHERE L.Is_Active=1 and L.Is_Approve=1';
--Search
IF (@Search is not null and @Search <> '')
BEGIN
SET @SQL = @SQL + ' and (L.National_ID LIKE ''%' + @Search + '%'' or L.First_name LIKE ''%' +
@fname + '%'' or L.Last_name LIKE ''%' + @lname + '%'' or L.Lead_Id LIKE ''%' + @Search + '%'' )';
END
--From and To Date
IF ((@FromDate is not null and @FromDate <> '') and (@ToDate is not null and @ToDate <> ''))
BEGIN
SET @SQL = @SQL + ' and (L.Created_On >= '''+ convert(nvarchar, @FromDate, 111) +''' and
L.Created_On <= '''+ convert(nvarchar, @ToDate, 111) +''')';
END
--Village
IF (@Village_ID is not null and @Village_ID <> '')
BEGIN
SET @SQL = @SQL + ' and (L.Village = '+ convert(nvarchar, @Village_ID) +')';
END
--Plant
IF (@Plant_ID is not null and @Plant_ID <> '')
BEGIN
SET @SQL = @SQL + ' and (V.Plant_ID = '+ convert(nvarchar, @Plant_ID) +')';
END
--County
IF (@County_ID is not null and @County_ID <> '')
BEGIN
SET @SQL = @SQL + ' and (C.County_ID = '+ convert(nvarchar, @County_ID) +')';
END
--Location
IF (@Location_ID is not null and @Location_ID <> '')
BEGIN
SET @SQL = @SQL + ' and (L.Location = '+ convert(nvarchar, @Location_ID) +')';
END
EXEC(@SQL);
--End Get All Leads to Opportunity With Field Attribute Details
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
DECLARE @ErrMsg NVARCHAR(MAX) = ERROR_MESSAGE()
DECLARE @lineno BIGINT = ERROR_LINE()
DECLARE @ErrNo VARCHAR(100) = ERROR_NUMBER()
DECLARE @SPNAME VARCHAR(300) = ERROR_PROCEDURE()
SELECT '0' AS response, @ErrMsg as 'message', 'INTERNAL_DB_ERROR' AS reason;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
END
-- APP_SELECT_LEADS_REPORT '67','2019-09-11','2019-09-20',1
-- APP_SELECT_LEADS_REPORT '','','',1,1,1,1
-- APP_SELECT_LEADS_REPORT '67','2019-09-11','2019-09-20',1
-- APP_SELECT_LEADS_REPORT '67','2019-09-11','2019-09-20',1
-------------------------------------------------------------------------------------------------------------------------------------------
column I have added while altering sp
L.Gender
SC.SubCounty_Name
Z.Zone_Name
,L.Sub_location,
,L.Access_type,
L.Additional_data,
,L.Approve_Remarks,
L.Is_Company,
L.Cordinates
convert(DATE, L.Date_of_birth, 104) as [DOB],
