plantseason-search.sql

SELECT [PlantSeason].[PlantSeason_ID] AS [id], [PlantSeason].[PlantSeason_Active] AS [active], [PlantSeason].[PlantSeason_Changed] AS [updatedAt], [PlantSeason].[PlantSeason_Created] AS [createdAt], [PlantSeason].[PlantSeason_Changer] AS [updatedBy], [PlantSeason].[PlantSeason_NewSurvey] AS [newSurvey], [PlantSeason].[PlantSeason_BonsucroProj] AS [bonsucroProj], [PlantSeason].[PlantSeason_LoanSeason] AS [loanSeasonName], [PlantSeason].[Season_ID] AS [seasonId], [PlantSeason].[PlantSeason_BonsucroArea] AS [bonsucroArea], [PlantSeason].[PlantSeason_PlantingDate] AS [plantingDate], [PlantSeason].[PlantSeason_HarvestDate] AS [harvestDate], [PlantSeason].[PlantSeason_SurveyVolumn] AS [surveyVolumn], [PlantSeason].[PlantSeason_SurveyBy] AS [surveyBy], [PlantSeason].[PlantSeason_ApproveDate] AS [approveDate], [PlantSeason].[PlantSeason_ApproveBy] AS [approveBy], [PlantSeason].[PlantSeason_ClosePlantDate] AS [closePlantDate], [PlantSeason].[PlantSeason_ClosePlantBy] AS [closePlantBy], [PlantSeason].[Plant_ID] AS [plantId], [PlantSeason].[Quota_ID] AS [quotaId], [PlantSeason].[Zone_ID] AS [zoneId], [PlantSeason].[FertilizerType_ID] AS [fertilizerTypeId], [PlantSeason].[PlantStatus_ID] AS [plantStatusId], [PlantSeason].[WaterType_ID] AS [waterTypeId], [PlantSeason].[SoilType_ID] AS [soilTypeId], [PlantSeason].[Gene_ID] AS [geneId], [PlantSeason].[PlantType_ID] AS [plantTypeId], [PlantSeason].[PlaceType_ID] AS [placeTypeId], [PlantSeason].[WaterUsage_ID] AS [waterUsageId], [PlantSeason].[MethodType_ID] AS [methodTypeId], [PlantSeason].[WaterCourse_ID] AS [waterCourseId], [PlantSeason].[HarvestType_ID] AS [harvestTypeId], [PlantSeason].[PlantWay_ID] AS [plantWayId], [PlantSeason].[Holding_ID] AS [holdingId], [PlantSeason].[Owner_ID] AS [ownerId], (SELECT COUNT(*) FROM tb_opr_Control c WHERE PlantSeason.PlantSeason_ID = c.PlantSeason_ID AND c.Control_No LIKE '%Admin-seed%')  AS [controlTotal], [plant].[Plant_ID] AS [plant.id], [plant].[Plant_Active] AS [plant.active], [plant].[Plant_Changed] AS [plant.updatedAt], [plant].[Plant_Created] AS [plant.createdAt], [plant].[Plant_Changer] AS [plant.updatedBy], [plant].[Plant_No] AS [plant.no], [plant].[Plant_Name] AS [plant.name], [plant].[Plant_Moo] AS [plant.moo], [plant].[Plant_District] AS [plant.district], [plant].[Plant_Province] AS [plant.province], [plant].[Plant_SubDistrict] AS [plant.subdistrict], [plant].[Plant_Latitude] AS [plant.lat], [plant].[Plant_Longitude] AS [plant.long], [plant].[Plant_AreaRai] AS [plant.areaAsRai], [plant].[Plant_AreaShape] AS [plant.areaFromShape], [plant].[Quota_ID] AS [plant.quotaId], [plant].[Plant_CreateBy] AS [plant.createdBy], CONVERT(varchar(max), [Plant_GeoLocation]) AS [plant.geoLocation] FROM [tb_opr_PlantSeason] AS [PlantSeason] LEFT OUTER JOIN [tb_opr_Plant] AS [plant] ON [PlantSeason].[Plant_ID] = [plant].[Plant_ID] AND [plant].[Plant_Active] = 1 
WHERE [PlantSeason].[Quota_ID] = N'601' 
AND [PlantSeason].[PlantSeason_LoanSeason] = N'6263' 
AND [PlantSeason].[PlantSeason_Active] = 1 
ORDER BY [PlantSeason].[PlantSeason_ID] OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;