using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Xml;

namespace Neo.Legitimate.NLTISWebService.Data
{
    internal class OlasOperatingLicence : OlasEntity
    {
        private const int LicenceStatusID = 3;
        private const int ApplicationTypeID = 43;
        private const string CashierReceiptNumber = "KZN OLAS Migration";
        private const string GazetteNumber = "KZN OLAS Migration";
        private const int DefaultBoardID = 33053;
        private const int DefaultBoardMeetingVenueID = 1;
        private static DateTime GazetteDate = new DateTime(2013, 3, 1);
        private static DateTime BoardMeetingDate = new DateTime(2013, 3, 1);
        private OlasAssociation _association;
        private OlasMember _member;
        public int ProvinceID
        {
            get
            {
                return NLTISDataBroker.ParseInt(base["provinceID"], 4);
            }
        }
        public int BoardID
        {
            get
            {
                return NLTISDataBroker.ParseInt(base["boardID"], 33053);
            }
        }
        public int BoardMeetingVenueID
        {
            get
            {
                return NLTISDataBroker.ParseInt(base["boardVenueID"], 1);
            }
        }
        public OlasOperatingLicence(XmlElement root)
            : base(root)
        {
            this._association = new OlasAssociation(root.SelectSingleNode("//Association") as XmlElement);
            this._member = new OlasMember(root.SelectSingleNode("//Member") as XmlElement);
        }
        public override void Verify()
        {
            this._association.Verify();
            this._member.Verify();
        }
        protected override bool Exists()
        {
            return false;
        }
        protected override void Read()
        {
        }
        public override void Create(SqlCommand command)
        {
            using (SqlConnection connection = new SqlConnection(base.ConnectionString))
            {
                SqlCommand sqlCommand;
                command = (sqlCommand = connection.CreateCommand());
                try
                {
                    connection.Open();
                    SqlTransaction transaction = connection.BeginTransaction();
                    command.Transaction = transaction;
                    try
                    {
                        this.SaveEntity(command, this._association);
                        this.SaveEntity(command, this._member);
                        int receiptID = -1;
                        int applicationID = -1;
                        string receiptNumber = null;
                        string applicationNumber = null;
                        int newVehicleID = -1;
                        int licenceID = -1;
                        int issueNumber = 1;
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@iUserBoardId", this.BoardID);
                        command.Parameters.AddWithValue("@iUserProvinceId", this.ProvinceID);
                        command.Parameters.AddWithValue("@psXMLAuditInfo", base.UserAudit);
                        command.CommandText = "spOLASNewReceiptNumSIOL";
                        receiptNumber = NLTISDataBroker.ToString(command.ExecuteScalar());
                        command.CommandText = "spOLASNewApplNumSIOL";
                        applicationNumber = NLTISDataBroker.ToString(command.ExecuteScalar());
                        int memberEntityID = this._member.GetEntityID(command);
                        int assocEntityID = this._association.GetEntityID(command);
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "spOLASSaveReceiptDetailsIUOL";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@psReceiptNum", receiptNumber);
                        command.Parameters.AddWithValue("@psApplNum", applicationNumber);
                        command.Parameters.AddWithValue("@pdtApplDate", DateTime.Now);
                        command.Parameters.AddWithValue("@piApplType", 43);
                        command.Parameters.AddWithValue("@piEntityId", memberEntityID);
                        command.Parameters.AddWithValue("@piEntityTypeId", 4);
                        command.Parameters.AddWithValue("@piTypeID", this._member["idType"]);
                        command.Parameters.AddWithValue("@psID", this._member["idNumber"]);
                        command.Parameters.AddWithValue("@psSurname", this._member["lastName"]);
                        command.Parameters.AddWithValue("@psInitials", this._member["initials"]);
                        command.Parameters.AddWithValue("@psFirstname", this._member["firstName"]);
                        command.Parameters.AddWithValue("@piApplicantAssocEntityId", assocEntityID);
                        command.Parameters.AddWithValue("@piTransferorEntityId", DBNull.Value);
                        command.Parameters.AddWithValue("@piTransferorEntityTypeId", DBNull.Value);
                        command.Parameters.AddWithValue("@piTransferorTypeID", DBNull.Value);
                        command.Parameters.AddWithValue("@psTransferorID", DBNull.Value);
                        command.Parameters.AddWithValue("@psTransferorTradeName", DBNull.Value);
                        command.Parameters.AddWithValue("@psTransferorSurname", DBNull.Value);
                        command.Parameters.AddWithValue("@psTransferorInitials", DBNull.Value);
                        command.Parameters.AddWithValue("@psTransferorFirstname", DBNull.Value);
                        command.Parameters.AddWithValue("@piTransfererAssocEntityId", DBNull.Value);
                        command.Parameters.AddWithValue("@piSeatCapacity", this._member.VehicleSeatingCapacity);
                        command.Parameters.AddWithValue("@piTotVehicles", this._member.VehicleCount);
                        command.Parameters.AddWithValue("@piUserId", base.UserID);
                        command.Parameters.AddWithValue("@piPermitBoardId", this.BoardID);
                        command.Parameters.AddWithValue("@psXMLAuditInfo", base.UserAudit);
                        command.Parameters.AddWithValue("@piTenderContractId", DBNull.Value);
                        command.Parameters.AddWithValue("@psContractNumber", DBNull.Value);
                        command.Parameters.AddWithValue("@piRepEntityID", DBNull.Value);
                        receiptID = Convert.ToInt32(command.ExecuteScalar());
                        command.CommandType = CommandType.Text;
                        command.CommandText = "SELECT pkiApplicationId FROM tblApplication WHERE sApplicationNum = @AppNumber";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@AppNumber", applicationNumber);
                        applicationID = Convert.ToInt32(command.ExecuteScalar());
                        double adminFee = 0.0;
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "spOLASTotReceiptAmountS";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@psCashierReceiptNumber", receiptNumber);
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                adminFee = Convert.ToDouble(reader["cTotalAdminFee"]);
                                double totalFee = Convert.ToDouble(reader["cTotalAmountCharged"]);
                            }
                        }
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "spOLASReceiptModalU";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@psReceiptNum", receiptNumber);
                        command.Parameters.AddWithValue("@psCashierReceiptNumber", "Western Cape OLAS Migration");
                        command.Parameters.AddWithValue("@piPaymentTypeId", 1);
                        command.Parameters.AddWithValue("@psChequeReferenceNumber", DBNull.Value);
                        command.Parameters.AddWithValue("@psXMLAuditInfo", base.UserAudit);
                        command.Parameters.AddWithValue("@pcAdminFee", adminFee);
                        command.ExecuteNonQuery();
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "spOLASRegNewPermitIssuesSIOL";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@psApplicationNum", applicationNumber);
                        command.Parameters.AddWithValue("@psXMLAuditInfo", base.UserAudit);
                        using (SqlDataReader reader2 = command.ExecuteReader())
                        {
                            if (reader2.Read())
                            {
                                newVehicleID = Convert.ToInt32(reader2["pkiVehicleId"]);
                                licenceID = Convert.ToInt32(reader2["pkiLicenceId"]);
                                issueNumber = Convert.ToInt32(reader2["iIssueNum"]);
                            }
                        }
                        VehicleService[] services = this._member.VehicleServices;
                        StringBuilder serviceTypesCsv = new StringBuilder();
                        for (int i = 0; i < services.Length; i++)
                        {
                            if (i > 0)
                            {
                                serviceTypesCsv.Append(',');
                            }
                            serviceTypesCsv.Append(services[i].ServiceTypeID);
                        }
                        string licenceCsv = NLTISDataBroker.ToString(licenceID);
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "spOLASRegSTSaveSelectionSOL";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@psLicenceIds", licenceCsv);
                        command.Parameters.AddWithValue("@psSTIds", serviceTypesCsv.ToString());
                        command.Parameters.AddWithValue("@psUnSelectedSTIds", string.Empty);
                        command.Parameters.AddWithValue("@psXMLAuditInfo", base.UserAudit);
                        command.ExecuteNonQuery();
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "spOLASRegSCSaveSelectionSOL";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@psLicenceIds", licenceCsv);
                        command.Parameters.AddWithValue("@psXMLAuditInfo", base.UserAudit);
                        command.Parameters.AddWithValue("@psUnSelectedSCIds", string.Empty);
                        SqlParameter paramServiceType = command.Parameters.AddWithValue("@piServiceTypeId", 8);
                        SqlParameter paramServiceConditions = command.Parameters.Add("@psSCIds", SqlDbType.VarChar, 7000);
                        for (int j = 0; j < services.Length; j++)
                        {
                            paramServiceType.Value = services[j].ServiceTypeID;
                            paramServiceConditions.Value = services[j].ServiceConditions;
                            command.ExecuteNonQuery();
                        }
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "spOLASRegSRSaveSelectionSOL";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@psLicenceIds", licenceCsv);
                        command.Parameters.AddWithValue("@psUnSelectedARIds", string.Empty);
                        command.Parameters.AddWithValue("@piEntityTypeId", 4);
                        command.Parameters.AddWithValue("@psXMLAuditInfo", base.UserAudit);
                        paramServiceType = command.Parameters.AddWithValue("@piServiceTypeId", 8);
                        SqlParameter paramServiceRoutes = command.Parameters.Add("@psARIds", SqlDbType.VarChar, 7000);
                        for (int k = 0; k < services.Length; k++)
                        {
                            paramServiceType.Value = services[k].ServiceTypeID;
                            paramServiceRoutes.Value = services[k].ServiceRoutes;
                            command.ExecuteNonQuery();
                        }
                        command.CommandType = CommandType.Text;
                        command.CommandText = "SELECT pkiApplicationGazetteTypeId FROM tluApplicationGazetteType WHERE bActive = 1 AND fkiProvinceId = 4";
                        command.Parameters.Clear();
                        int gazetteTypeID = Convert.ToInt32(command.ExecuteScalar());
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "spOLASAddApplicationGazette";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@fkiApplicationGazetteTypeId", gazetteTypeID);
                        command.Parameters.AddWithValue("@dtApplicationGazetteDate", OlasOperatingLicence.GazetteDate);
                        command.Parameters.AddWithValue("@sApplicationGazetteNumber", "KZN OLAS Migration");
                        command.Parameters.AddWithValue("@sAuditInfo", base.UserAudit);
                        int gazetteID = Convert.ToInt32(command.ExecuteScalar());
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "spOLASLinkAppToGazette";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@iGazetteID", gazetteID);
                        command.Parameters.AddWithValue("@bProv", this.ProvinceID);
                        command.Parameters.AddWithValue("@iApplicationID", applicationID);
                        command.Parameters.AddWithValue("@iOrderBy", 1);
                        command.Parameters.AddWithValue("@sAuditInfo", base.UserAudit);
                        command.ExecuteNonQuery();
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "spOLASInsertPermitBoardMeeting";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@iBoardID", this.BoardID);
                        command.Parameters.AddWithValue("@iVenueID", this.BoardMeetingVenueID);
                        command.Parameters.AddWithValue("@dtMeetingDate", OlasOperatingLicence.BoardMeetingDate);
                        command.Parameters.AddWithValue("@sAuditInfo", base.UserAudit);
                        int boardMeetingID = Convert.ToInt32(command.ExecuteScalar());
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "spOLASInsertApplicationPermitBoardMeeting";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@iBoardMeetingID", boardMeetingID);
                        command.Parameters.AddWithValue("@iApplicationID", applicationID);
                        command.Parameters.AddWithValue("@iPrintOrder", 1);
                        command.Parameters.AddWithValue("@sAuditInfo", base.UserAudit);
                        int boardMeetingMinutesID = Convert.ToInt32(command.ExecuteScalar());
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "spOLASInsertPermitBoardMeetingMinutesItemForOL";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@iPermitBoardMeetingMinutesID", boardMeetingMinutesID);
                        command.Parameters.AddWithValue("@iLicenceID", licenceID);
                        command.Parameters.AddWithValue("@sAuditInfo", base.UserAudit);
                        command.ExecuteNonQuery();
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "spOLASUpdateApplicationWithBMStatus";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@iApplicationID", applicationID);
                        command.Parameters.AddWithValue("@sAuditInfo", base.UserAudit);
                        command.ExecuteNonQuery();
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "spOLASUpdatePermitBoardMeetingMinutes";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@iPermitBoardMeetingMinutesID", boardMeetingMinutesID);
                        command.Parameters.AddWithValue("@sFactualReview", DBNull.Value);
                        command.Parameters.AddWithValue("@sReferredTo", DBNull.Value);
                        command.Parameters.AddWithValue("@sPresent", DBNull.Value);
                        command.Parameters.AddWithValue("@sComments", DBNull.Value);
                        command.Parameters.AddWithValue("@sAuditInfo", base.UserAudit);
                        command.ExecuteNonQuery();
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "spOLASSetLettersPrinted";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@iPermitBoardMeetingMinutesId", boardMeetingMinutesID);
                        command.Parameters.AddWithValue("@sAuditInfo", base.UserAudit);
                        command.ExecuteNonQuery();
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "spOLASNewOLNumSI";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@iUserBoardId", this.BoardID);
                        command.Parameters.AddWithValue("@iUserProvinceId", this.ProvinceID);
                        command.Parameters.AddWithValue("@psXMLAuditInfo", base.UserAudit);
                        string olNumber = NLTISDataBroker.ToString(command.ExecuteScalar());
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "spOLASUpdateOLWithOLNum";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@iLicenceID", licenceID);
                        command.Parameters.AddWithValue("@sLicenceNum", olNumber);
                        command.ExecuteNonQuery();
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "spOLASUpdateApplicationToDecisionCaptured";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@iApplicationID", applicationID);
                        command.Parameters.AddWithValue("@sAuditInfo", base.UserAudit);
                        command.ExecuteNonQuery();
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "spOLASUpliftmentLinkVehicleToOLU";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@iLicenceId", licenceID);
                        command.Parameters.AddWithValue("@iVehicleId", this._member.VehicleID);
                        command.Parameters.AddWithValue("@sAuditInfo", base.UserAudit);
                        command.ExecuteNonQuery();
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "spOLASUpliftmentVehicleDetailsLU";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@iVehicleId", newVehicleID);
                        command.Parameters.AddWithValue("@iVehicleStatusId", 1);
                        command.Parameters.AddWithValue("@sRegistrationNum", this._member.VehicleRegNumber);
                        command.Parameters.AddWithValue("@sChassisNum", this._member.VehicleChassisNumber);
                        command.Parameters.AddWithValue("@iSUMSContractId", -1);
                        command.Parameters.AddWithValue("@iSUMSVehicleId", -1);
                        command.Parameters.AddWithValue("@sAuditInfo", base.UserAudit);
                        command.Parameters.AddWithValue("@iLicenceId", licenceID);
                        command.Parameters.AddWithValue("@sApplicationNum", applicationNumber);
                        object result = command.ExecuteScalar();
                        this._member.VehicleID = newVehicleID;
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "spOLASUpliftmentLicenceDetailsU";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@iLicenceId", licenceID);
                        command.Parameters.AddWithValue("@iLicenceStatusID", 3);
                        command.Parameters.AddWithValue("@bCOFSubmitted", false);
                        command.Parameters.AddWithValue("@bComplyAdditionalRequirements", true);
                        command.Parameters.AddWithValue("@sAuditInfo", base.UserAudit);
                        command.ExecuteNonQuery();
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "spOLASUpliftmentGenerateLicenceU";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@iLicenceId", licenceID);
                        command.Parameters.AddWithValue("@iLicenceStatusID", 3);
                        command.Parameters.AddWithValue("@sUniqueNum", base["certificateNumber"]);
                        command.Parameters.AddWithValue("@dtIssueDate", NLTISDataBroker.ParseDbDate(base["issueDate"]));
                        command.Parameters.AddWithValue("@dtExpiryDate", NLTISDataBroker.ParseDbDate(base["expiryDate"]));
                        command.Parameters.AddWithValue("@bIndefinite", NLTISDataBroker.ParseBool(base["indefinite"], false));
                        command.Parameters.AddWithValue("@sAuditInfo", base.UserAudit);
                        command.ExecuteNonQuery();
                        base["receiptID"] = NLTISDataBroker.ToString(receiptID);
                        base["receiptNumber"] = receiptNumber;
                        base["applicationID"] = NLTISDataBroker.ToString(applicationID);
                        base["applicationNumber"] = applicationNumber;
                        base["licenceID"] = NLTISDataBroker.ToString(licenceID);
                        base["olNumber"] = olNumber;
                        base["issueNumber"] = NLTISDataBroker.ToString(issueNumber);
                        base["gazetteID"] = NLTISDataBroker.ToString(gazetteID);
                        transaction.Commit();
                        base.UpdateStatus(0, MigrationStatus.Created.ToString());
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        base.UpdateStatus(2, NLTISDataBroker.ToString(ex));
                    }
                }
                finally
                {
                    if (sqlCommand != null)
                    {
                        sqlCommand.Dispose();
                    }
                }
            }
        }
        public override void Update(SqlCommand command)
        {
        }
        private void SaveEntity(SqlCommand command, IEntity entity)
        {
            if (entity.StatusID == 1)
            {
                entity.Update(command);
            }
            else
            {
                entity.Create(command);
            }
        }
    }
}