Scalar function of sql

ALTER  FUNCTION [Accounts].[userDefinedFunction_Group_Debit_Amount]
(
@companyId UNIQUEIDENTIFIER,
@financialyearId UNIQUEIDENTIFIER,
@groupId UNIQUEIDENTIFIER,
@fromDate Datetime,
@toDate Datetime
)

RETURNS DECIMAL(18,2)

 AS 

     BEGIN
      DECLARE @DEBIT_AMOUNT DECIMAL(18,2)
        SET @DEBIT_AMOUNT=0
        SET @DEBIT_AMOUNT= (SELECT    
               SUM(Accounts.VoucherDetails.DebitAmount) AS DrAmount          
               
                    FROM   Accounts.LedgerGroups INNER JOIN
                            Accounts.Ledgers ON Accounts.LedgerGroups.Id = Accounts.Ledgers.GroupId INNER JOIN
                            Accounts.VoucherDetails ON Accounts.Ledgers.Id = Accounts.VoucherDetails.LedgerId INNER JOIN
                            Accounts.Vouchers ON Accounts.VoucherDetails.VoucherId = Accounts.Vouchers.Id
                    WHERE   Accounts.Vouchers.VoucherDate BETWEEN @fromDate AND @toDate and Accounts.LedgerGroups.Id = @groupId
                          AND (Accounts.Vouchers.FinancialYearId = @financialYearId) AND (Accounts.Vouchers.CompanyId = @companyId)
                    GROUP BY   Accounts.LedgerGroups.Id, Accounts.LedgerGroups.GroupName, Accounts.Vouchers.FinancialYearId,
                               Accounts.Vouchers.CompanyId)--you see in design mode
        RETURN @DEBIT_AMOUNT
    END

Scalar function of sql

ALTER  FUNCTION [Accounts].[userDefinedFunction_Group_Credit_Amount]
(
@companyId UNIQUEIDENTIFIER,
@financialYearId UNIQUEIDENTIFIER,
@groupId UNIQUEIDENTIFIER,
@fromDate Datetime,
@toDate Datetime
)

RETURNS DECIMAL(18,2)

 AS 

     BEGIN
      DECLARE @CREDIT_AMOUNT DECIMAL(18,2)
        SET @CREDIT_AMOUNT=0
        SET @CREDIT_AMOUNT= (SELECT             
               SUM(Accounts.VoucherDetails.CreditAmount) AS CrAmount
               
                    FROM    Accounts.LedgerGroups INNER JOIN
                            Accounts.Ledgers ON Accounts.LedgerGroups.Id = Accounts.Ledgers.GroupId INNER JOIN
                            Accounts.VoucherDetails ON Accounts.Ledgers.Id = Accounts.VoucherDetails.LedgerId INNER JOIN
                            Accounts.Vouchers ON Accounts.VoucherDetails.VoucherId = Accounts.Vouchers.Id

                    WHERE   Accounts.Vouchers.VoucherDate BETWEEN @fromDate AND @toDate and Accounts.LedgerGroups.Id = @groupId
                            AND (Accounts.Vouchers.FinancialYearId = @financialYearId) AND (Accounts.Vouchers.CompanyId = @companyId)
                    GROUP BY  Accounts.LedgerGroups.Id, Accounts.LedgerGroups.GroupName, Accounts.Vouchers.FinancialYearId,
                               Accounts.Vouchers.CompanyId)
        RETURN @CREDIT_AMOUNT
    END

Scalar function of sql

ALTER  FUNCTION [Accounts].[userDefinedFunction_Account_Group_OpeningBalance_Amount]
(
@companyId uniqueidentifier,
@financialyearId uniqueidentifier,
@groupId uniqueidentifier,
@fromDate Datetime
)

RETURNS DECIMAL(18,2)

 AS 

     BEGIN
      DECLARE @OPENINGBALANCE_AMOUNT DECIMAL(18,2),@DEBIT_AMOUNT DECIMAL(18,2),@CREDIT_AMOUNT DECIMAL(18,2)

        SET @OPENINGBALANCE_AMOUNT=0
        SET @DEBIT_AMOUNT=0
        SET @CREDIT_AMOUNT=0

        SET @OPENINGBALANCE_AMOUNT=(SELECT ISNULL(SUM(OBAmount),0) FROM Accounts.view_Account_Group_OpeningBalance WHERE GroupId=@groupId AND FinancialYearId = @financialYearId AND CompanyId = @companyId)
        SET @DEBIT_AMOUNT = (SELECT ISNULL(SUM([DebitAmount]-[CreditAmount]) ,0) FROM Accounts.view_Account_Group_DebitAmount_CreditAmount where VoucherDate < @fromDate and [GroupId] =@groupId AND FinancialYearId = @financialYearId AND CompanyId = @companyId)

        SET   @OPENINGBALANCE_AMOUNT=(@OPENINGBALANCE_AMOUNT+@DEBIT_AMOUNT)-@CREDIT_AMOUNT

        RETURN @OPENINGBALANCE_AMOUNT
    END

update query execute in data context

public void ChangeIsCurrentYear()
        {
            _DatabaseContext.Database.ExecuteSqlCommand("Update Accounts.FinancialYears set IsCurrentYear={0}", false);
        }

function with parameter of sql execute in datacontext

public IList<TrialBalance> GetGroupBalances(Guid? companyId, Guid? financialYearId, DateTime fromDate, DateTime toDate)
        {
           return _DatabaseContext.Database.SqlQuery<TrialBalance>(@"Select * from Accounts.udf_Account_Group_Details('" + companyId + "', '" + financialYearId + "','" + fromDate + "','" + toDate + "')").AsQueryable().ToList();
        }

Modal Pop Up Control

<div>
        <asp:ModalPopupExtender ID="mpeFinancialYear" runat="server" TargetControlID="btnSetFinancialYear"
            PopupControlID="pnlFinancialYear" CancelControlID="imgbtnFinancialYearExit">
            <Animations>
            <OnShown>
              <FadeIn duration="0.15" Fps="100" />
            </OnShown>
            </Animations>
        </asp:ModalPopupExtender>
        <asp:Panel ID="pnlFinancialYear" runat="server" BackColor="White" Style="display: none">
            <table style="border: 5px Solid #666666; width: 100%; height: 100%" class="boxShadow"
                cellpadding="10" cellspacing="0">
                <tr>
                    <td style="border-bottom: solid 1px #C0C0C0">
                        <h3>
                            Financial Year</h3>
                    </td>
                    <td style="width: 22px; border-bottom: solid 1px #C0C0C0;" align="right">
                        <a href="#" class="closePopupButton" onclick="closepopup()" id="imgbtnFinancialYearExit">
                            X</a>
                    </td>
                </tr>
                <tr>
                    <td colspan="2" style="padding-left: 24px;">
                        <table cellpadding="4" cellspacing="0">
                            <tr>
                                <td style="width: 150px;">
                                    Financial Year
                                </td>
                                <td>
                                    :
                                </td>
                                <td>
                                    <asp:DropDownList ID="ddlFinancialYear" runat="server" Width="100%" MaxLength="200"
                                        AutoPostBack="True" OnSelectedIndexChanged="ddlFinancialYear_SelectedIndexChanged">
                                    </asp:DropDownList>
                                    <asp:RequiredFieldValidator runat="server" ID="financialYearRequiredFieldValidator"
                                        ControlToValidate="ddlFinancialYear" Display="None" InitialValue="0" ErrorMessage="Invalid A/c No."
                                        ForeColor="Red" ValidationGroup="FinancialYearValidationGroup"></asp:RequiredFieldValidator>
                                </td>
                            </tr>
                        </table>
                    </td>
                </tr>
            </table>
        </asp:Panel>
    </div>

Message box in javascript from Code behind

                    Page.ClientScript.RegisterStartupScript(GetType(), "msgbox", "alert('Ledger name already exist in this financial year')", true);