using System;
using System.Data;
using System.Collections.Generic;
using System.Collections;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;

public partial class StoredProcedures
{
    protected static readonly SqlConnection oConn = new SqlConnection("Context Connection=true");

    public static SqlDecimal GetTaxRate(SqlString iClientCode, SqlInt32 iTaxYear)
    {
        SqlString sSqlString = null;

        sSqlString = "Select " +
            "TaxRate from Tax t INNER JOIN " +
            "ClientCodes c ON t.ClientCode = c.ClientCodeID " +
            "WHERE c.ClientCode = @ClientCode AND t.TaxYear = @TaxYear";

        SqlCommand cmd = new SqlCommand(sSqlString.ToString(), oConn);

        cmd.Parameters.AddWithValue("@TaxYear", iTaxYear);
        cmd.Parameters.AddWithValue("@ClientCode", iClientCode);

        SqlDataReader dr = cmd.ExecuteReader();

        dr.Read();

        SqlDecimal decTaxRate = dr.GetSqlDecimal(0);

        cmd.Dispose();
        dr.Dispose();

        return decTaxRate / 100;
    }

    public static List<SqlDecimal> GetCurrentFees(SqlString sParcelNo, SqlInt32 iTaxYear)
    {
        SqlString sSqlString = null;
        List<SqlDecimal> FeesList = new List<SqlDecimal>();

        sSqlString = "Select Sum(a.TranAmount), a.BillingTypeID, b.BillingTypeDescription " + 
                    "FROM BillingDetail a " +
                    "INNER JOIN BillingType b " +
                    "ON a.BillingTypeID = b.BillingTypeID " +
                    "WHERE ParcelNo = @ParcelNo " +
                    "AND TaxYear = @TaxYear " +
                    "Group by a.BillingTypeID, b.BillingTypeDescription";

        SqlCommand cmd = new SqlCommand(sSqlString.ToString(), oConn);

        cmd.Parameters.AddWithValue("@ParcelNo", sParcelNo);
        cmd.Parameters.AddWithValue("@TaxYear", iTaxYear);

        SqlDataReader dr = cmd.ExecuteReader();

        if (dr.HasRows)
        {
            while (dr.Read())
            {
                FeesList.Add(0);
                FeesList.Add(0);
                FeesList.Add(0);
                
                switch(dr.GetSqlInt32(1).ToString())
                {
                    case "1":
                        FeesList[0] = dr.GetSqlDecimal(0);
                        break;
                    case "2":
                        FeesList[1] = dr.GetSqlDecimal(0);
                        break;
                    case "3":
                        FeesList[2] = dr.GetSqlDecimal(0);
                        break;
                }
            }
        }

        cmd.Dispose();
        dr.Dispose();

        return FeesList;
    }

    public static SqlDecimal GetAssessmentChange(SqlString sParcelNo, SqlInt32 iTaxYear)
    {
        SqlString sSqlString = null;

        sSqlString = "Select " +
            "sum(TranAmount) as AdjustmentAmount " +
            "from ParcelAssessedValueAdjustments " +
            "WHERE ParcelNo = @ParcelNo " +
            "AND TaxYear = @TaxYear ";

        SqlCommand cmd = new SqlCommand(sSqlString.ToString(), oConn);

        cmd.Parameters.AddWithValue("@TaxYear", iTaxYear);
        cmd.Parameters.AddWithValue("@ParcelNo", sParcelNo);

        SqlDataReader dr = cmd.ExecuteReader();

        dr.Read();
        
        SqlDecimal decCurrectMarketAppraisedValue = 0;

        if (dr.GetValue(0).ToString() != "")
        {
            decCurrectMarketAppraisedValue = dr.GetSqlDecimal(0);
        }
        else
        {
            decCurrectMarketAppraisedValue = 0; 
        }

        cmd.Dispose();
        dr.Dispose();

        return decCurrectMarketAppraisedValue;
    }

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void StarDetailsCLRProc(SqlString sParcelNo, SqlInt32 iTaxYear, SqlString iClientCode, SqlBoolean ByYear)
    {
        SqlString sSqlString = null;
        oConn.Open();

        sSqlString = @"SELECT 
	        DISTINCT 
	        dbo.AR_Master.ARMasterID, 
	        dbo.AR_Master.TaxYear, 
	        dbo.AR_Master.ClientCode, 
			dbo.ClientCodes.TownCode,
	        dbo.BillingDetail.ParcelNo, 
	        dbo.BillingDetail.ParcelID,
	        dbo.ParcelAssessedValue.OriginalMarketAssessedValue,
            dbo.ParcelASsessedValue.OriginalMarketValue,
	        (dbo.ParcelAssessedValue.OriginalMarketAssessedValue + (@decAssessmentChange)) as CurrentMarketAssessedValue,
	        (round((dbo.ParcelAssessedValue.OriginalMarketAssessedValue + (@decAssessmentChange)) * @TaxRate, 2)) AS CurrentTaxBilled,
	        (@decAssessmentChange) as AssessmentChange,
	        (Round(dbo.ParcelASsessedValue.OriginalMarketAssessedValue * @TaxRate, 2)) AS OriginalTaxBilled,
	        dbo.ParcelASsessedValue.OriginalGreenBeltAppraisedValue,
	        dbo.ParcelASsessedValue.ResidentialValueForTaxRelief,
	        dbo.ParcelASsessedValue.OtherValueForTaxRelief,
            (0) as AppraisalChanges,
	        dbo.ParcelASsessedValue.TaxableAssessment,
            @TaxBilled as 'Tax Billed',
            @InterestBilled as 'Interest Billed',
            @FeesBilled as 'Fees Billed',
            (round((dbo.ParcelAssessedValue.OriginalMarketAssessedValue + (@decAssessmentChange)) * @TaxRate, 2) + @InterestBilled + @FeesBilled) as TotalDue  
        FROM dbo.AR_Master 
		INNER JOIN dbo.ClientCodes ON dbo.AR_Master.ClientCode = dbo.ClientCodes.ClientCodeID
        INNER JOIN dbo.BillingDetail ON dbo.AR_Master.ARMasterID = dbo.BillingDetail.ARMasterID 
        --INNER JOIN dbo.Transactions ON dbo.Transactions.ARMasterID = dbo.BillingDetail.ARMasterID
        INNER JOIN dbo.Parcel ON dbo.BillingDetail.ParcelID = dbo.Parcel.ParcelID 
        INNER JOIN dbo.ParcelAssessedValue ON dbo.Parcel.ParcelID = dbo.ParcelAssessedValue.ParcelID
        INNER JOIN dbo.LegalDescription on dbo.Parcel.ParcelID = dbo.LegalDescription.ParcelID";

        if (sParcelNo.ToString().Length > 0 || iTaxYear > 0)
        {
            sSqlString += " WHERE";
        }

        if (sParcelNo.ToString().Length > 0 && iTaxYear == 0)
        {
            sSqlString += Environment.NewLine;
            sSqlString += " dbo.AR_Master.ParcelNo = " + "'" + sParcelNo + "'";
        }

        if (sParcelNo.ToString().Length == 0 && iTaxYear > 0)
        {
            sSqlString += Environment.NewLine;
            if (ByYear)
            {
                sSqlString += " dbo.AR_Master.TaxYear = " + iTaxYear;
            }
        }

        if (sParcelNo.ToString().Length > 0 && iTaxYear > 0)
        {
            sSqlString += Environment.NewLine;
            sSqlString += " dbo.AR_Master.ParcelNo = " + "'" + sParcelNo + "'";
            if (ByYear)
            {
                sSqlString += " AND dbo.AR_Master.TaxYear = " + iTaxYear;
            }
        }
     
        sSqlString += " AND dbo.ClientCodes.ClientCode = " + "'" + iClientCode + "'";

        SqlDecimal dec = GetTaxRate(iClientCode, iTaxYear);
        SqlDecimal decAssessmentChange = GetAssessmentChange(sParcelNo, iTaxYear);
        SqlCommand cmd = new SqlCommand(sSqlString.ToString(), oConn);
        List<SqlDecimal> FeesList = new List<SqlDecimal>();

        FeesList = GetCurrentFees(sParcelNo, iTaxYear);

        cmd.Parameters.AddWithValue("@TaxBilled", FeesList.Count >= 1 ? FeesList[0] : 0);
        cmd.Parameters.AddWithValue("@InterestBilled", FeesList.Count >= 2 ? FeesList[1] : 0);
        cmd.Parameters.AddWithValue("@FeesBilled", FeesList.Count >= 3 ? FeesList[2] : 0);
        cmd.Parameters.AddWithValue("@TotalDue", (FeesList.Count >= 1 ? FeesList[0] : 0) + (FeesList.Count >= 2 ? FeesList[1] : 0) + (FeesList.Count >= 3 ? FeesList[2] : 0));
        cmd.Parameters.AddWithValue("@TaxRate", dec);
        cmd.Parameters.AddWithValue("@decAssessmentChange", decAssessmentChange);

        //SqlContext.Pipe.Send("SQL:" + " " + sSqlString.ToString());

        SqlContext.Pipe.Send(cmd.ExecuteReader());
        cmd.Dispose();
    }
}