SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
/*
Created By-manish kumar rawat
Created Date-21-10-2016
Modified By-
Modified Date
Desc-This is use to get Prapatra20
Proc_Get_Praptra16_ByOrgId_MS 1
*/
CREATE PROCEDURE dbo.Proc_Get_Praptra16_ByOrgId_MS
@OrgnisationId BIGINT
AS
SELECT * INTO #tempPrapatra20
FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY o.OrganisationId,m.MandalId, k.KhandId ORDER BY k.KhandId ASC,a.TransDate DESC ) RowNo ,
a.P16Id ,o.OrganisationNameInHindi ,m.Mandal ,k.Khand ,
CASE WHEN a.IsDeleted = 1 THEN 0
ELSE ISNULL(TotalNoOfTello, 0)
END AS TotalNoOfTello ,
CASE WHEN a.IsDeleted = 1 THEN 0
ELSE ISNULL(KharifNoOfTello, 0)
END AS KharifNoOfTello ,
CASE WHEN a.IsDeleted = 1 THEN 0
ELSE ISNULL(RabiNoOfTello, 0)
END AS RabiNoOfTello ,
CASE WHEN a.IsDeleted = 1 THEN 0
ELSE ISNULL(TotalTeloInFasali, 0)
END AS TotalTeloInFasali ,
CASE WHEN a.IsDeleted = 1 THEN 0
ELSE ISNULL(TotalTarget, 0)
END AS TotalTarget ,
CASE WHEN a.IsDeleted = 1 THEN 0
ELSE ISNULL(KharifTarget, 0)
END AS KharifTarget ,
CASE WHEN a.IsDeleted = 1 THEN 0
ELSE ISNULL(ProgressTarget, 0)
END AS ProgressTarget ,
CASE WHEN a.IsDeleted = 1 THEN 0
ELSE ISNULL(PercentageOfProgress, 0)
END AS PercentageOfProgress ,
ISNULL(Remarks, 'N/A') AS Remarks ,
CASE WHEN a.isDeleted=0 THEN ([dbo].[GetMonthNameinHindi](MonthId)) ELSE 'N/A' END AS [MonthName],
CASE WHEN a.isDeleted=0 THEN a.TransDate ELSE '' END AS TransDate ,a.KhandId ,m.MandalId,o.OrganisationId
FROM Mandal_MS m
INNER JOIN dbo.Khand_MS k ON k.MandalId = m.MandalId
INNER JOIN dbo.Organisation_MS o ON o.OrganisationId = m.OrganisationId
LEFT JOIN Prapatra16_MS a ON a.KhandId = k.KhandId AND a.MandalId = k.MandalId
WHERE a.OrganisationId = @OrgnisationId AND a.IsSubmitMonitering=1
) tt
WHERE tt.RowNo = 1 ORDER BY tt.P16Id,tt.MandalId, tt.KhandId ASC
--------------------Select Record not exists in praptra Above data-----------
SELECT * INTO #temp1Prapatra20
FROM (( SELECT '1' RowNo ,ROW_NUMBER()OVER(ORDER BY k.KhandId ASC)AS Id,o.OrganisationNameInHindi ,m.Mandal ,k.Khand ,
CASE WHEN a.IsDeleted = 1 THEN 0
ELSE ISNULL(TotalNoOfTello, 0)
END AS TotalNoOfTello ,
CASE WHEN a.IsDeleted = 1 THEN 0
ELSE ISNULL(KharifNoOfTello, 0)
END AS KharifNoOfTello ,
CASE WHEN a.IsDeleted = 1 THEN 0
ELSE ISNULL(RabiNoOfTello, 0)
END AS RabiNoOfTello ,
CASE WHEN a.IsDeleted = 1 THEN 0
ELSE ISNULL(TotalTeloInFasali, 0)
END AS TotalTeloInFasali ,
CASE WHEN a.IsDeleted = 1 THEN 0
ELSE ISNULL(TotalTarget, 0)
END AS TotalTarget ,
CASE WHEN a.IsDeleted = 1 THEN 0
ELSE ISNULL(KharifTarget, 0)
END AS KharifTarget ,
CASE WHEN a.IsDeleted = 1 THEN 0
ELSE ISNULL(ProgressTarget, 0)
END AS ProgressTarget ,
CASE WHEN a.IsDeleted = 1 THEN 0
ELSE ISNULL(PercentageOfProgress, 0)
END AS PercentageOfProgress ,
ISNULL(Remarks, 'N/A') AS Remarks , 'N/A' AS [MonthName],CONVERT(VARCHAR(15),'',103) AS TransDate ,k.KhandId ,m.MandalId,o.OrganisationId
FROM Khand_MS k
LEFT JOIN Prapatra16_MS a ON a.KhandId = k.KhandId
INNER JOIN dbo.Mandal_MS m ON k.MandalId = m.MandalId
INNER JOIN dbo.Organisation_MS o ON o.OrganisationId = m.OrganisationId
WHERE k.KhandId IN ( SELECT DISTINCT KhandId FROM dbo.Khand_MS WHERE m.OrganisationId = @OrgnisationId
--AND MandalId NOT IN (SELECT MandalId FROM #temp3Praptra5B)
AND KhandId NOT IN (SELECT KhandId FROM #tempPrapatra20))
AND o.OrganisationId = @OrgnisationId
))tr
WHERE tr.RowNo = 1 ORDER BY tr.Id,tr.MandalId, tr.KhandId ASC
------------Select--------------------
SELECT * INTO #ResultTable
FROM (
SELECT * FROM #tempPrapatra20
UNION
SELECT * FROM #temp1Prapatra20
)AS x
SELECT * FROM #ResultTable ORDER BY #ResultTable.MandalId,#ResultTable.KhandId ASC
-------------DropTable-----------------
DROP TABLE #tempPrapatra20
DROP TABLE #temp1Prapatra20
DROP TABLE #ResultTable
----------------------------------------------------
GO
this is sql procedure
<asp:GridView ID="grd" runat="server" ShowFooter="true" CssClass="table table-bordered table-striped info" Width="100%" PageSize="40" OnPreRender="grd_PreRender" AutoGenerateColumns="false" OnRowDataBound="grd_RowDataBound" OnRowCreated="grd_RowCreated">
<Columns>
<asp:TemplateField HeaderStyle-Width="5%" ItemStyle-HorizontalAlign="Center"
HeaderStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:Label ID="lblserialno" runat="server" Text='<%# Container.DataItemIndex + 1 %>'></asp:Label>
<asp:HiddenField ID="hdnId" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="lblMandal" runat="server" Text='<%#Eval("Mandal") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate >
<asp:Label ID="lbltxttotal" runat="server" Text="कुल योग" />
</FooterTemplate>
<ItemStyle CssClass="vamiddile" />
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="lblKhand" runat="server" Text='<%#Eval("Khand") %>'></asp:Label>
<asp:Label ID="lblKhandId" runat="server" Visible="false" Text='<%#Eval("KhandId") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate >
<asp:Label ID="lblKhandName" runat="server" Text="कुल योग" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="lblMonthName" runat="server" Text='<%#Eval("MonthName") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="lblTotalTelo" runat="server" Text='<%#Eval("TotalNoOfTello") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="txtTotalRecieved" runat="server"></asp:Label>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="lblKharifTeloNos" runat="server" Text='<%#Eval("KharifNoOfTello") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="txtTotalEndofMonth" runat="server"></asp:Label>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="lblRabiTelo" runat="server" Text='<%#Eval("RabiNoOfTello") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblPercnetage" runat="server"></asp:Label>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="lblTotalTeloInFasali" runat="server" Text='<%#Eval("TotalTeloInFasali") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblamountrecivedmd" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="lblTotalTarget" runat="server" Text='<%#Eval("TotalTarget") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblendofmonthmd" runat="server"></asp:Label>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="lblKharifTarget" runat="server" Text='<%#Eval("KharifTarget") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotalKharifTarget" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="lblProgressTarget" runat="server" Text='<%#Eval("ProgressTarget") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotalProgressTarget" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="lblPrecentageofProgress" runat="server" Text='<%#Eval("PercentageOfProgress") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotalPrecentageofProgress" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:ImageButton ID="lnkbtnEdit" ImageUrl="~/site/images/edit.png" CommandArgument='<%#Eval("P16Id") %>' CommandName="editrecord" CausesValidation="false"
runat="server" />
<asp:Label ID="lblId" runat="server" Text='<%#Eval("P16Id") %>' Visible="false"></asp:Label>
<asp:Label ID="lblOrganisationId" runat="server" Text='<%#Eval("OrganisationId") %>' Visible="false"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:ImageButton ID="lnkbtnDelete" runat="server" ImageUrl="~/site/Images/delete.png" CommandArgument='<%#Eval("P16Id") %>'
CommandName="deleterecord" CausesValidation="false" />
<uc1:ConfirmBoxDel ID="confirm1" runat="server" Message="Do You want to Delete ?"
TargetControlId="lnkbtnDelete" Title="Confirmation" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
<RowStyle CssClass="grid_row" />
<AlternatingRowStyle CssClass="grid_arow" />
<EmptyDataRowStyle CssClass="emptyRec" />
<FooterStyle />
<PagerStyle CssClass="paging_bootstrap" />
</asp:GridView>
this html grid code
