public static int Parse(Edi834DataAccess dbAccess,string content)
        {
            Extras.Logging log = new Extras.Logging();
            //string sEdiFile = ""; ;
            ediDocument oEdiDoc = null;
            //ediSchema oSchema = null;
            ediSchemas oSchemas = null;
            //ediInterchange oInterchange = null;
            //ediGroup oGroup = null;
            //ediTransactionSet oTransactionset = null;
            ediDataSegment oSegment = null;
            //ediAcknowledgment oAck = null;
               // ediWarning oWarning = null;
              //  ediWarnings oWarnings = null;
              //  Int32 nWarningsCount;
            string sPath = AppDomain.CurrentDomain.BaseDirectory;
            string sSefFile = "834_005010X220A1.SemRef.SEF";
            ediAcknowledgment oAck = null;

            //string sValue;
            //string sHlEntity = "";
            //string sNm1Entity = "";
            //string sQlfr;

            //CREATES EDIDOC OBJECT
            oEdiDoc = new ediDocument();
            oEdiDoc.EventNotify += new EventNotifyDelegate(oEdiDoc_EventNotify);
            //THIS MAKES CERTAIN THAT FREDI ONLY USES THE SEF FILE PROVIDED, AND THAT IT DOES
            //NOT USE ITS BUILT-IN STANDARD REFERENCE TABLE TO TRANSLATE THE EDI FILE.
            oSchemas = (ediSchemas)oEdiDoc.GetSchemas();
            oSchemas.EnableStandardReference = false;

            oAck = (ediAcknowledgment)oEdiDoc.GetAcknowledgment();
            oAck.EnableInterchangeAcknowledgment = true;

            //THIS OPTIONS STOPS FREDI FROM KEEPING ALL THE SEGMENTS IN MEMORY
            oEdiDoc.CursorType = DocumentCursorTypeConstants.Cursor_ForwardOnly;

            //LOADS THE SEF FILE
            oEdiDoc.LoadSchema(sPath + sSefFile, SchemaTypeIDConstants.Schema_Standard_Exchange_Format);

            //LOADS THE EDI FILE
            oEdiDoc.LoadEdiString(content);

            //GETS THE FIRST DATA SEGMENT

            ediDataSegment.Set(ref oSegment, (ediDataSegment)oEdiDoc.FirstDataSegment);

            MsgHeader msg_header=null;
             MsgTrailer msg_trailer =null;
             Int64 msg_HeaderID = 0;
             Int64 msg_transID = 0;
            TransactionHeader trans_Header = null;
            TransactionTrailer trans_Trailer = null;
            Member.HealthCoverage health_coverage=null;
            Member.MemberReport member_report=null;
            Member msg_Member = null;
            while (oSegment != null)
            {
                sSegmentID = oSegment.ID;
                sLoopSection = oSegment.LoopSection;
                nArea = oSegment.Area;
               // Debug.WriteLine("segment id ="+ sSegmentID+"\t section ="+sLoopSection+" \t nArea= "+nArea+"\r\t");

                if (nArea == 0)
                {
                    if (sLoopSection == "")
                    {
                        if (sSegmentID == "ISA")
                        {
                            if (msg_header == null)
                                msg_header = new MsgHeader();
                            msg_header.InterchangeControlHeader=oSegment.get_DataElementValue(1, 0);
                            msg_header.AuthorizationInformation = oSegment.get_DataElementValue(2, 0);
                            msg_header.SecurityInformationQualifier = oSegment.get_DataElementValue(3, 0);
                            msg_header.SecurityInformation = oSegment.get_DataElementValue(4, 0);
                            msg_header.InterchangeIDQualifier_1 = oSegment.get_DataElementValue(5, 0);
                            msg_header.InterchangeSenderID = oSegment.get_DataElementValue(6, 0);
                            msg_header.InterchangeIDQualifier_2 = oSegment.get_DataElementValue(7, 0);
                            msg_header.InterchangeReceiverID = oSegment.get_DataElementValue(8, 0);
                            msg_header.IntegerchangeDate = oSegment.get_DataElementValue(9, 0);
                            msg_header.IntegerchangeTime = oSegment.get_DataElementValue(10, 0);
                            msg_header.RepetitionSeperator = oSegment.get_DataElementValue(11, 0);
                            msg_header.InterchangeControlVersionNumber = oSegment.get_DataElementValue(12, 0);
                            msg_header.InterchangeControlNumber_header = oSegment.get_DataElementValue(13, 0);
                            msg_header.AcknowledgementRequest = oSegment.get_DataElementValue(14, 0);
                            msg_header.UsageIdentifier = oSegment.get_DataElementValue(15, 0);
                            msg_header.ComponentElementSeparator = oSegment.get_DataElementValue(16, 0);

                        }
                        else if (sSegmentID == "GS")
                        {
                            if (msg_header == null)
                                msg_header = new MsgHeader();
                            msg_header.FunctionalGroupHeader_header=oSegment.get_DataElementValue(1,0);
                            msg_header.ApplicationSendersCode=oSegment.get_DataElementValue(2,0);
                            msg_header.ApplicationReceiversCode=oSegment.get_DataElementValue(3,0);
                            msg_header.Date=oSegment.get_DataElementValue(4,0);
                            msg_header.Time=oSegment.get_DataElementValue(5,0);
                            msg_header.GroupControlNumber_header=oSegment.get_DataElementValue(6,0);
                            msg_header.ResponsibleAgencyCode=oSegment.get_DataElementValue(7,0);
                            msg_header.IndustryIndentifierCode=oSegment.get_DataElementValue(8,0);
                            dbAccess.InsertNewMessageHeader(msg_header);        //insert it into the database;
                            msg_header = null;
                            msg_HeaderID = dbAccess.getHeaderID();              //get the header id;

                        }
                        else if (sSegmentID == "GE")
                        {
                            if (msg_trailer == null)
                                msg_trailer = new MsgTrailer();
                            msg_trailer.FunctionalGroupHeader_trailer = oSegment.get_DataElementValue(1, 0);
                            msg_trailer.GroupControlNumber_trailer = oSegment.get_DataElementValue(2, 0);
                        }
                        else if (sSegmentID == "IEA")
                        {
                            msg_trailer.InterchangeControlTrailer = oSegment.get_DataElementValue(1, 0);
                            msg_trailer.InterchangeControlNumber_trailer = oSegment.get_DataElementValue(2, 0);
                            dbAccess.insertNewMessageTrailer(msg_trailer,msg_HeaderID);
                            msg_trailer = null;
                        }
                        else
                        {
                            Debug.WriteLine("segment id ="+ sSegmentID+"\t section ="+sLoopSection+" \t nArea= "+nArea+" is  not  be recorded");
                        }
                    }
                }
                else if (nArea == 1)
                {
                    if (trans_Header == null)
                        trans_Header = new TransactionHeader();
                    if (sLoopSection == "")
                    {
                        if (sSegmentID == "ST")
                        {
                            trans_Header.HeaderID = msg_HeaderID;
                            trans_Header.TransactionSetIdentifier = oSegment.get_DataElementValue(1, 0);
                            trans_Header.TranscationSetControlNumber = oSegment.get_DataElementValue(2, 0);
                            trans_Header.ImplementationConventionReference = oSegment.get_DataElementValue(3, 0);
                        }
                        else if (sSegmentID == "BGN")
                        {
                            if (trans_Header == null)
                        trans_Header = new TransactionHeader();
                            trans_Header.TransactionSetPurposeCode = oSegment.get_DataElementValue(1, 0);
                            trans_Header.ReferenceIdentification_BS = oSegment.get_DataElementValue(2, 0);
                            trans_Header.Date = oSegment.get_DataElementValue(3, 0);
                            trans_Header.Time = oSegment.get_DataElementValue(4, 0);
                            trans_Header.ActionCode = oSegment.get_DataElementValue(5, 0);
                        }
                        else if (sSegmentID == "REF")
                        {if (trans_Header == null)
                        trans_Header = new TransactionHeader();
                            trans_Header.ReferenceIdentificationQualifier = oSegment.get_DataElementValue(1, 0);
                            trans_Header.ReferenceIdentification = oSegment.get_DataElementValue(2, 0);
                        }
                        else if(sSegmentID=="DTP")
                        {
                            //LOOP
                            EDIModel.TransactionHeader.FileDate file_date;
                            file_date.Date_TimeQualifier=oSegment.get_DataElementValue(1,0);
                            file_date.DateTimePeriodFormatQualifier=oSegment.get_DataElementValue(2,0);
                            file_date.DateTimePeriod=oSegment.get_DataElementValue(3,0);
                            trans_Header.FileEffectiveDate.AddLast(file_date);
                        }
                        else if (sSegmentID == "QTY")
                        {
                            EDIModel.TransactionHeader.TransactionSets trans_set;
                            trans_set.QuantityQualifier = oSegment.get_DataElementValue(1, 0);
                            trans_set.Quantity = oSegment.get_DataElementValue(2, 0);
                            trans_Header.TransactionSetControlTotals.AddLast(trans_set);

                        }

                    }
                    else if (sLoopSection == "N1")
                    {
                        if (sSegmentID == "N1" )
                        {
                            string EntityIdentifier = oSegment.get_DataElementValue(1, 0);
                            if (EntityIdentifier == "P5")
                            {
                                trans_Header.EntityIdentifierCode_Sponsor = oSegment.get_DataElementValue(1, 0);
                                trans_Header.Name_Sponsor = oSegment.get_DataElementValue(2, 0);
                                trans_Header.IdentificationCodeQualifier_Sponsor = oSegment.get_DataElementValue(3, 0);
                                //optional
                                trans_Header.IdentificationCode_Sponsor = oSegment.get_DataElementValue(4, 0);

                            }
                            else if (EntityIdentifier =="IN")
                            {
                                trans_Header.EntityIdentifierCode_Payer= oSegment.get_DataElementValue(1, 0);
                                trans_Header.Name_Payer = oSegment.get_DataElementValue(2, 0);
                                trans_Header.IdentificationCodeQualifier_Payer = oSegment.get_DataElementValue(3, 0);
                                trans_Header.IdentificationCode_Payer = oSegment.get_DataElementValue(4, 0);

                            }
                            else
                            {
                                //LOOP
                                EDIModel.TransactionHeader.TPABroker tpa_broker;
                                tpa_broker.EntityIdentityCode = oSegment.get_DataElementValue(1, 0);
                                tpa_broker.Name_TPA_Broker = oSegment.get_DataElementValue(2, 0);
                                tpa_broker.IdentificationCodeQualifier_TPA_Broker = oSegment.get_DataElementValue(3, 0);
                                tpa_broker.IdentificationCode_TPA_Broker = oSegment.get_DataElementValue(4, 0);
                                trans_Header.TPABrokerAccount.AddLast(tpa_broker);
                            }
                        }
                    }
                }

                else if (nArea == 2)
                {
                    if (trans_Header != null)
                    {
                        dbAccess.insertNewTransaction(trans_Header);    //insert the header into database
                        msg_transID = dbAccess.getTransactionID();
                        dbAccess.insertNewTransaction_FileDate(trans_Header.FileEffectiveDate, msg_transID);
                        dbAccess.insertNewTransaction_Sets(trans_Header.TransactionSetControlTotals, msg_transID);
                        dbAccess.insertNewTransaction_TPABroker(trans_Header.TPABrokerAccount, msg_transID);
                        trans_Header = null;
                    }
                    if (sLoopSection == "INS")
                    {
                        if (sSegmentID == "INS")
                        {
                            //Start to import the memeber information;
                            msg_Member = null;
                            msg_Member = new Member();
                            msg_Member.Yes_No_Condition = oSegment.get_DataElementValue(1, 0);
                            msg_Member.IndividualRelationshipCode = oSegment.get_DataElementValue(2, 0);
                            msg_Member.MaintenanceTypeCode_MLD = oSegment.get_DataElementValue(3, 0);
                            //Optional
                            msg_Member.MaintenanceReasonCode = oSegment.get_DataElementValue(4, 0);
                            msg_Member.BenefitStatusCode = oSegment.get_DataElementValue(5, 0);
                            //Optional
                            msg_Member.EmploymentStatusCode = oSegment.get_DataElementValue(8, 0);
                        }
                        else if (sSegmentID == "REF")
                        {
                            string SubscriberNumber = oSegment.get_DataElementValue(1, 0);
                            if (SubscriberNumber == "0F")
                            {
                                msg_Member.SubscriberNumber = oSegment.get_DataElementValue(1, 0);
                                //Optional
                                msg_Member.ReferenceNumber = oSegment.get_DataElementValue(2, 0);
                            }
                            else if (SubscriberNumber == "1L")
                            {
                                msg_Member.MemberPolicyNumber = oSegment.get_DataElementValue(1, 0);
                                msg_Member.ReferenceIdentification_MPM = oSegment.get_DataElementValue(2, 0);
                            }
                            else
                            {
                                //LOOP
                                EDIModel.Member.MemberIdentification member_Identification;
                                member_Identification.MemberIdentificationNumber = oSegment.get_DataElementValue(1, 0);
                                member_Identification.ReferenceIdentification = oSegment.get_DataElementValue(2, 0);
                                msg_Member.MemberIdentificationNumber.AddLast(member_Identification);
                            }
                        }
                        else if (sSegmentID == "DTP")
                        {
                            EDIModel.Member.MemberDate member_level_date;
                            //optional
                            member_level_date.Date_Time_Qualifier = oSegment.get_DataElementValue(1, 0);
                            //optional
                            member_level_date.Date_Time_Format = oSegment.get_DataElementValue(2, 0);
                            //optional
                            member_level_date.Date_Time_Period = oSegment.get_DataElementValue(3, 0);
                            msg_Member.MemberLevelDate.AddLast(member_level_date);
                        }
                        else if (sSegmentID == "LS")
                        {
                            msg_Member.LoopIdentifierCode = oSegment.get_DataElementValue(1, 0);
                        }
                        else if (sSegmentID == "LE")
                        {
                            msg_Member.LoopIdentifierCode = oSegment.get_DataElementValue(1, 0);

                        }

                    }
                    else if (sLoopSection == "")
                    {
                        if (sSegmentID == "SE")
                        {
                            //Insert into database;
                            if (health_coverage != null)
                            {
                                msg_Member.HealthCoverages.AddLast(health_coverage);
                                health_coverage = null;
                            }
                            if (member_report != null)
                            {
                                msg_Member.MemberReportingCategories.AddLast(member_report);
                                member_report = null;
                            }
                            dbAccess.insertNewMember(msg_Member, msg_transID);// insert the member into the database

                            if (trans_Trailer == null)
                                trans_Trailer = new TransactionTrailer();
                            trans_Trailer.NumberOfIncludedSegments = oSegment.get_DataElementValue(1, 0);
                            trans_Trailer.TransactionSetControlNumber = oSegment.get_DataElementValue(2, 0);
                            dbAccess.insertNewTransactionTrailer(trans_Trailer, msg_transID);
                            trans_Trailer = null;

                        }
                    }
                    else if (sLoopSection == "INS;NM1")
                    {
                        if (sSegmentID == "NM1")
                        {
                            msg_Member.EntityIdentifierCode_Member = oSegment.get_DataElementValue(1, 0);
                            msg_Member.EntityTypeQualifier_Member = oSegment.get_DataElementValue(2, 0);
                            msg_Member.NameLast = oSegment.get_DataElementValue(3, 0);
                            msg_Member.NameFirst = oSegment.get_DataElementValue(4, 0);
                            //optional
                            msg_Member.NameMiddle = oSegment.get_DataElementValue(5, 0);
                            msg_Member.IdentificationCodeQualifier = oSegment.get_DataElementValue(8, 0);
                            //optional
                            msg_Member.IdentificationCode = oSegment.get_DataElementValue(9, 0);
                        }
                        else if (sSegmentID == "PER")
                        {
                            msg_Member.ContactFunctionCode = oSegment.get_DataElementValue(1, 0);
                            //keep empty if not given
                            msg_Member.CommunicationNumberQualifier_1 = oSegment.get_DataElementValue(3, 0);
                            msg_Member.CommunicationNumber_1 = oSegment.get_DataElementValue(4, 0);
                            msg_Member.CommunicationNumberQualifier_2 = oSegment.get_DataElementValue(5, 0);
                            msg_Member.CommunicationNumber_2 = oSegment.get_DataElementValue(6, 0);
                            msg_Member.CommunicationNumberQualifier_3 = oSegment.get_DataElementValue(7, 0);
                            msg_Member.CommunicationNumber_3 = oSegment.get_DataElementValue(8, 0);
                        }
                        else if (sSegmentID == "N3")
                        {
                            //Keep Empty if not Given
                            msg_Member.AddressLine_1 = oSegment.get_DataElementValue(1, 0);
                            //optional
                            msg_Member.AddressLine_2 = oSegment.get_DataElementValue(2, 0);
                        }
                        else if (sSegmentID == "N4")
                        {
                            msg_Member.City = oSegment.get_DataElementValue(1, 0);
                            msg_Member.State = oSegment.get_DataElementValue(2, 0);
                            msg_Member.PostalCode = oSegment.get_DataElementValue(3, 0);
                            //Optional
                            msg_Member.LocationQualifier = oSegment.get_DataElementValue(5, 0);
                            //optional
                            msg_Member.LocationIdentifier = oSegment.get_DataElementValue(6, 0);
                        }
                        else if (sSegmentID == "DMG")
                        {
                            msg_Member.Date_Time_FormatQualifier_MD = oSegment.get_DataElementValue(1, 0);
                            msg_Member.Date_Time_Period_MD = oSegment.get_DataElementValue(2, 0);
                            msg_Member.GenderCode = oSegment.get_DataElementValue(3, 0);
                            //optional
                            msg_Member.MaritalStatusCode = oSegment.get_DataElementValue(4, 0);
                            msg_Member.CompositeRaceOrEthnicityInformation = oSegment.get_DataElementValue(5, 0);
                        }
                        else if (sSegmentID == "HLH")
                        {
                            //optional
                            msg_Member.HealthRelatedCode = oSegment.get_DataElementValue(1, 0);
                        }
                    }
                    else if (sLoopSection == "INS;HD")
                    {
                        if (sSegmentID == "HD")
                        {
                            //LOOP Class
                            if (health_coverage == null)
                                health_coverage = new Member.HealthCoverage();
                            else
                            {
                                msg_Member.HealthCoverages.AddLast(health_coverage);
                                health_coverage = new Member.HealthCoverage();
                            }
                            health_coverage.MaintenanceTypeCode = oSegment.get_DataElementValue(1, 0);
                            health_coverage.InsuranceLineCode = oSegment.get_DataElementValue(3, 0);
                            //should set to be empty
                            health_coverage.PlanCoverageDescription = oSegment.get_DataElementValue(4, 0);
                        }
                        else if (sSegmentID == "DTP")
                        {
                            EDIModel.Member.HealthCoverage_Dates health_date;
                            health_date.Date_Time_Qualifier = oSegment.get_DataElementValue(1, 0);
                            health_date.Date_Time_FormatQualifier_HCD = oSegment.get_DataElementValue(2, 0);
                            health_date.Date_Time_Period_HCD = oSegment.get_DataElementValue(3, 0);
                            health_coverage.HealthCoverageDates.AddLast(health_date);
                        }
                        else if (sSegmentID == "REF")
                        {
                            EDIModel.Member.HealthPolicyNum policy_num;
                            policy_num.ReferenceIdentificationQualifier = oSegment.get_DataElementValue(1, 0);
                            policy_num.ReferenceIdentification = oSegment.get_DataElementValue(2, 0);
                            health_coverage.PolicyNumber.AddLast(policy_num);
                        }
                    }
                    else if (sLoopSection == "INS;LX")
                    {
                        if (sSegmentID == "LX")
                        {
                            if (member_report == null)
                                member_report = new Member.MemberReport();
                            else
                            {
                                msg_Member.MemberReportingCategories.AddLast(member_report);
                                member_report = new Member.MemberReport();
                            }
                            member_report.AssignedNumber = oSegment.get_DataElementValue(1, 0);
                        }
                    }
                    else if (sLoopSection == "INS;LX;N1")
                    {
                        if (sSegmentID == "N1")
                        {
                            member_report.EntityIdentifierCode = oSegment.get_DataElementValue(1, 0);
                            member_report.Name = oSegment.get_DataElementValue(2, 0);
                        }
                        else if (sSegmentID == "REF")
                        {
                            member_report.ReferenceIdentificationQualifier = oSegment.get_DataElementValue(1, 0);
                            member_report.ReferenceIdentification = oSegment.get_DataElementValue(2, 0);
                        }
                    }
                }
             ediDataSegment.Set(ref oSegment, (ediDataSegment)oSegment.Next());
            }
            //dbAccess.getHeaderTrailerID();
            //Console.ReadKey();
            string sFileName = "TA1_834_X220A1_" +
                    System.DateTime.Now.Year.ToString() +
                    System.DateTime.Now.Month.ToString().PadLeft(2, '0') +
                    System.DateTime.Now.Day.ToString().PadLeft(2, '0') +
                    System.DateTime.Now.Hour.ToString().PadLeft(2, '0') +
                    System.DateTime.Now.Minute.ToString().PadLeft(2, '0') +
                    System.DateTime.Now.Second.ToString().PadLeft(2, '0') + ".txt";
            oAck.Save(DIR_TA1_OUT+ sFileName);
            oSchemas.Dispose();
            oEdiDoc.Dispose();
            return 0;
        }
        public int insertNewMember_Identification(Member member, Int64 MemberID)
        {
            Database db = new Database();
            foreach (EDIModel.Member.MemberIdentification Identification in member.MemberIdentificationNumber)
            {

                SqlParameter[] param =
                {
                    db.MakeInParam("@MemberID",SqlDbType.BigInt,0,MemberID),
                   db.MakeInParam("@MemberIdentificationNumber",SqlDbType.VarChar,3,Identification.MemberIdentificationNumber),
                   db.MakeInParam("@ReferenceIdentification",SqlDbType.VarChar,50,Identification.ReferenceIdentification)
                };
                try
                {
                    db.execNonQuery("EDI_Member_Identification_Add", param);
                }
                catch (Exception ex)
                {
                    if (db.IsOpen() == "Open") db.Close();
                    log.writeToAppLog("ERROR", "Unable to insert the message Member_Identification.\r\n" + ex.ToString(), "", "");
                    throw new Exception(ex.ToString());
                }

            }
            if (db.IsOpen() == "Open") db.Close();
            return 0;
        }
 public int insertNewMember_HealthCoverage(Member member, Int64 MemberID)
 {
     foreach (EDIModel.Member.HealthCoverage health in member.HealthCoverages)
     {
         Database db = new Database();
         SqlParameter[] param =
         {
             db.MakeInParam("@MemberID",SqlDbType.BigInt,0,MemberID),
             db.MakeInParam("@MaintenanceTypeCode",SqlDbType.VarChar,3,health.MaintenanceTypeCode),
             db.MakeInParam("@InsuranceLineCode",SqlDbType.VarChar,3,health.InsuranceLineCode),
             db.MakeInParam("@PlanCoverageDescription",SqlDbType.VarChar,50,health.PlanCoverageDescription)
         };
         try
         {
             db.execNonQuery("EDI_Member_HealthCoverage_Add", param);
             db.Close();
             Int64 healthCoverageID=GetHealthCoverageID();
             insertNewMember_HealthCoverage_Dates(health.HealthCoverageDates, healthCoverageID);
             insertNewMember_HealthCoverage_PolicyNum(health.PolicyNumber, healthCoverageID);
             return 0;
         }
         catch (Exception ex)
         {
             if (db.IsOpen() == "Open") db.Close();
             log.writeToAppLog("ERROR", "Unable to insert the message Member Health Coverage.\r\n" + ex.ToString(), "", "");
             throw new Exception(ex.ToString());
         }
     }
     return 0;
 }
        public int insertNewMember_Dates(Member member, Int64 MemberID)
        {
            Database db = new Database();
            foreach (EDIModel.Member.MemberDate date in member.MemberLevelDate)
            {
                SqlParameter[] param =
                {
                    db.MakeInParam("@MemberID",SqlDbType.BigInt,0,MemberID),
                    db.MakeInParam("@Date_Time_Qualifier",SqlDbType.VarChar,3,date.Date_Time_Qualifier),
                    db.MakeInParam("@Date_Time_Format",SqlDbType.VarChar,3,date.Date_Time_Format),
                    db.MakeInParam("@Date_Time_Period",SqlDbType.VarChar,8,date.Date_Time_Period)
                };
                try
                {
                    db.execNonQuery("EDI_Member_Dates_Add", param);

                }
                catch (Exception ex)
                {
                    if (db.IsOpen() == "Open") db.Close();
                    log.writeToAppLog("ERROR", "Unable to insert the message Member_Dates.\r\n" + ex.ToString(), "", "");
                    throw new Exception(ex.ToString());
                }
            }
            if (db.IsOpen() == "Open") db.Close();
            return 0;
        }
 public int insertNewMember(Member member, Int64 msg_transID)
 {
     Database db = new Database();
     SqlParameter[] param =
     {
         db.MakeInParam("@TransactionID",SqlDbType.BigInt,0,msg_transID),
         db.MakeInParam("@Yes_No_Condition",SqlDbType.VarChar,1,member.@Yes_No_Condition),
         db.MakeInParam("@IndividualRelationshipCode",SqlDbType.VarChar,2,member.IndividualRelationshipCode),
         db.MakeInParam("@MaintenanceTypeCode_MLD",SqlDbType.VarChar,3,member.MaintenanceTypeCode_MLD),
         db.MakeInParam("@MaintenanceReasonCode",SqlDbType.VarChar,3,member.MaintenanceReasonCode),
         db.MakeInParam("@BenefitStatusCode",SqlDbType.VarChar,1,member.BenefitStatusCode),
         db.MakeInParam("@EmploymentStatusCode",SqlDbType.VarChar,2,member.EmploymentStatusCode),
         db.MakeInParam("@SubscriberNumber",SqlDbType.VarChar,3,member.SubscriberNumber),
         db.MakeInParam("@ReferenceNumber",SqlDbType.VarChar,50,member.ReferenceNumber),
         db.MakeInParam("@MemberPolicyNumber",SqlDbType.VarChar,3,member.MemberPolicyNumber),
         db.MakeInParam("@ReferenceIdentification_MPM",SqlDbType.VarChar,50,member.ReferenceIdentification_MPM),
         db.MakeInParam("@EntityIdentifierCode_Member",SqlDbType.VarChar,3,member.EntityIdentifierCode_Member),
         db.MakeInParam("@EntityTypeQualifier_Member",SqlDbType.VarChar,1,member.EntityTypeQualifier_Member),
         db.MakeInParam("@NameLast",SqlDbType.VarChar,60,member.NameLast),
         db.MakeInParam("@NameFirst",SqlDbType.VarChar,35,member.NameFirst),
         db.MakeInParam("@NameMiddle",SqlDbType.VarChar,25,member.NameMiddle),
          db.MakeInParam("@IdentificationCodeQualifier",SqlDbType.VarChar,2,member.IdentificationCodeQualifier),
         db.MakeInParam("@IdentificationCode",SqlDbType.VarChar,80,member.IdentificationCode),
         db.MakeInParam("@ContactFunctionCode",SqlDbType.VarChar,2,member.ContactFunctionCode),
         db.MakeInParam("@CommunicationNumberQualifier_1",SqlDbType.VarChar,2,member.CommunicationNumberQualifier_1),
         db.MakeInParam("@CommunicationNumber_1",SqlDbType.VarChar,256,member.CommunicationNumber_1),
         db.MakeInParam("@CommunicationNumberQualifier_2",SqlDbType.VarChar,2,member.CommunicationNumberQualifier_2),
         db.MakeInParam("@CommunicationNumber_2",SqlDbType.VarChar,256,member.CommunicationNumber_2),
         db.MakeInParam("@CommunicationNumberQualifier_3",SqlDbType.VarChar,2,member.CommunicationNumberQualifier_3),
         db.MakeInParam("@CommunicationNumber_3",SqlDbType.VarChar,256,member.CommunicationNumber_3),
         db.MakeInParam("@AddressLine_1",SqlDbType.VarChar,55,member.AddressLine_1),
         db.MakeInParam("@AddressLine_2",SqlDbType.VarChar,55,member.AddressLine_2),
         db.MakeInParam("@City",SqlDbType.VarChar,30,member.City),
         db.MakeInParam("@State",SqlDbType.VarChar,2,member.State),
         db.MakeInParam("@PostalCode",SqlDbType.VarChar,13,member.PostalCode),
         db.MakeInParam("@LocationQualifier",SqlDbType.VarChar,2,member.LocationQualifier),
         db.MakeInParam("@LocationIdentifier",SqlDbType.VarChar,30,member.LocationIdentifier),
         db.MakeInParam("@Date_Time_FormatQualifier_MD",SqlDbType.VarChar,3,member.Date_Time_FormatQualifier_MD),
         db.MakeInParam("@Date_Time_Period_MD",SqlDbType.VarChar,8,member.Date_Time_Period_MD),
         db.MakeInParam("@GenderCode",SqlDbType.VarChar,1,member.GenderCode),
         db.MakeInParam("@MaritalStatusCode",SqlDbType.VarChar,1,member.MaritalStatusCode),
         db.MakeInParam("@CompositeRaceOrEthnicityInformation",SqlDbType.VarChar,10,member.CompositeRaceOrEthnicityInformation),
         db.MakeInParam("@HealthRelatedCode",SqlDbType.VarChar,1,member.HealthRelatedCode),
         db.MakeInParam("@LoopIdentifierCode",SqlDbType.VarChar,4,member.LoopIdentifierCode)
     };
     try
     {
         db.execNonQuery("EDI_Member_Add", param);
         db.Close();
         Int64 memberID = GetMemberID();
         insertNewMember_Identification(member, memberID);
         insertNewMember_Dates(member, memberID);
         insertNewMember_HealthCoverage(member, memberID);
         insertNewMember_Reports(member.MemberReportingCategories,memberID);
         return 0;
     }
     catch (Exception ex)
     {
         if (db.IsOpen() == "Open") db.Close();
         log.writeToAppLog("ERROR", "Unable to insert the message member.\r\n" + ex.ToString(), "", "");
         throw new Exception(ex.ToString());
     }
 }
        public static Member setupMember(DataRow dr)
        {
            Member oMember = new Member();
            string relCode=dr["RELCOD"].ToString();
            if (relCode.Equals("1"))       //if SUBNUM==MEMBER
            {
                oMember.Yes_No_Condition = "Y";
                oMember.IndividualRelationshipCode = "18"; //self
            }
            else
            {
                oMember.Yes_No_Condition = "N";
                if (relCode.Equals("2"))
                    oMember.IndividualRelationshipCode = "01";//spouse
                else if (relCode.Equals("3"))
                    oMember.IndividualRelationshipCode = "19";//child
                else
                    oMember.IndividualRelationshipCode = "53"; //life partner(others)
            }
            oMember.MaintenanceTypeCode_MLD = ConfigurationManager.AppSettings["MaintenanceCode"];
            oMember.MaintenanceReasonCode = ConfigurationManager.AppSettings["MaintenanceReasonCode"];
            oMember.BenefitStatusCode = ConfigurationManager.AppSettings["BenefitStatusCode"];
            oMember.EmploymentStatusCode = ConfigurationManager.AppSettings["StatusCode"];
            #region Subscriber number
            oMember.SubscriberNumber = "0F";
            oMember.ReferenceNumber = "00000";           // nead to look at
            #endregion Subscriber number

            #region Member Policy Number
            oMember.MemberPolicyNumber = "1L";
            oMember.ReferenceIdentification_MPM = ConfigurationManager.AppSettings["GroupValue"];
            #endregion Member Policy Number

            #region Member Identification Number
            EDIModel.Member.MemberIdentification iden = new Member.MemberIdentification();
            iden.MemberIdentificationNumber = "23";
            iden.ReferenceIdentification = "001";
            oMember.MemberIdentificationNumber.AddLast(iden);   //need to look at
            #endregion  Member Identification Number

            #region NM1 Segment Member Name
            oMember.EntityIdentifierCode_Member = "IL";
            oMember.EntityTypeQualifier_Member = "1";
            oMember.NameLast = dr["LASTNAME"].ToString();
            oMember.NameFirst = dr["FIRSTNAME"].ToString();
            oMember.NameMiddle = dr["MIDINIT"].ToString();
            if (!String.IsNullOrEmpty(dr["SOCSEC"].ToString()))
            {
                oMember.IdentificationCodeQualifier = "34";//Social security Number
                oMember.IdentificationCode = dr["SOCSEC"].ToString();
            }
            #endregion NM1 Segment Member Name

            #region PER Segment Member Communication Numbers
            if (!String.IsNullOrEmpty(dr["HOMEPH"].ToString()))
            {
                oMember.ContactFunctionCode = "IP";
                oMember.CommunicationNumberQualifier_1 = "TE";
                oMember.CommunicationNumber_1 = dr["HOMEPH"].ToString();
            }
            else if (!String.IsNullOrEmpty(dr["EMAIL"].ToString()))
            {
                oMember.ContactFunctionCode = "IP";
                oMember.CommunicationNumberQualifier_2 = "EM";
                oMember.CommunicationNumber_2 = dr["EMAIL"].ToString();
            }
            #endregion Segment Member Communication Numbers

            #region Member Address
            oMember.AddressLine_1 = dr["ADRLN1"].ToString();
            oMember.AddressLine_2 = dr["ADRLN2"].ToString();
            #endregion Member Address

            #region Member residence
            oMember.City = dr["CITYCD"].ToString();
            oMember.State = dr["STACOD"].ToString();
            oMember.PostalCode = dr["ZIPCOD"].ToString();
            #endregion Member residence

            #region Member Demographic
            oMember.Date_Time_FormatQualifier_MD = "D8";
            oMember.Date_Time_Period_MD =  Convert.ToDateTime(dr["BTHDAT"]).ToString("yyyyMMdd");
            oMember.GenderCode = dr["SEXCOD"].ToString();
            #endregion Member Demographic

             DataTable dt_hc = dbAccess.GetHealthCoverage(dr["MEMBER"].ToString());
             foreach (DataRow dr_hc in dt_hc.Rows)
            {
                #region Health Coverage LOOP
                if (!validateHCData(dr_hc))
                    continue;
                EDIModel.Member.HealthCoverage healthCoverage  = new Member.HealthCoverage();
                healthCoverage.MaintenanceTypeCode = ConfigurationManager.AppSettings["TypeCodeHealthCoverage"];
                healthCoverage.InsuranceLineCode = ConfigurationManager.AppSettings["InsuranceLineCode"];
                healthCoverage.PlanCoverageDescription = dr_hc["BENPKG"].ToString();
                oMember.HealthCoverages.AddLast(healthCoverage);
                #endregion Health Coverage LOOP

                #region HealthCoverageDates
                Member.HealthCoverage_Dates healthDates_start = new Member.HealthCoverage_Dates();
                healthDates_start.Date_Time_Qualifier="348"; //start
                healthDates_start.Date_Time_FormatQualifier_HCD="D8";
                healthDates_start.Date_Time_Period_HCD = Convert.ToDateTime(dr_hc["EFFDAT"]).ToString("yyyyMMdd");
                Member.HealthCoverage_Dates healthDates_end = new Member.HealthCoverage_Dates();
                healthDates_end.Date_Time_Qualifier= "349";
                healthDates_end.Date_Time_FormatQualifier_HCD="D8";
                healthDates_end.Date_Time_Period_HCD = Convert.ToDateTime(dr_hc["EXPDAT"]).ToString("yyyyMMdd");
                healthCoverage.HealthCoverageDates.AddLast(healthDates_start);
                healthCoverage.HealthCoverageDates.AddLast(healthDates_end);
                #endregion  HealthCoverageDates
            }

            return oMember;
        }