public static void InsertSIHOT_Reservation_Delete(SIHOT_Reservation value)
 {
     SqlParameter[] param = new SqlParameter[] {
         new SqlParameter("@TN", value.TN),
         new SqlParameter("@OC", value.OC),
         new SqlParameter("@HN", value.HN),
         new SqlParameter("@SIHOT_Document_Id", value.SIHOT_Document_Id)
     };
     SqlHelper.ExecuteNonQuery(Helper.ConnectionString, CommandType.StoredProcedure, "insert_tblSIHOT_Reservation_Delete", param);
 }
    public static SIHOT_Reservation Read(SqlDataReader reader)
    {
        SIHOT_Reservation retval = new SIHOT_Reservation();

        for (int i = 0; i < reader.FieldCount; i++)
        {
            switch (reader.GetName(i))
            {
                case "TN":
                    retval.TN = Helper.ToInt32(reader[i]);
                    break;
                case "OC":
                    retval.OC = Helper.ToString(reader[i]);
                    break;
                case "HN":
                    retval.HN = Helper.ToInt32(reader[i]);
                    break;
                case "USERID":
                    retval.UserId = Helper.ToString(reader[i]);
                    break;
                case "WS":
                    retval.WS = Helper.ToString(reader[i]);
                    break;
                case "ObjectId":
                    retval.OBJID = Helper.ToString(reader[i]);
                    break;
                case "RNO":
                    retval.RNO = Helper.ToInt32(reader[i]);
                    break;
                case "RSNO":
                    retval.RSNO = Helper.ToInt32(reader[i]);
                    break;
                case "ARR_Date":
                    retval.ARR = Helper.ToDateTime(reader[i]);
                    break;
                case "DEP_Date":
                    retval.DEP = Helper.ToDateTime(reader[i]);
                    break;
                case "NOPAX":
                    retval.NOPAX = Helper.ToInt32(reader[i]);
                    break;
                case "RT":
                    retval.RT = Helper.ToString(reader[i]);
                    break;
                case "ORG":
                    retval.ORG = Helper.ToString(reader[i]);
                    break;
            }
        }
        return retval;
    }
 public static void InsertSIHOT_Reservation(SIHOT_Reservation value)
 {
     SqlParameter[] param = new SqlParameter[] {
         new SqlParameter("@TN", value.TN),
         new SqlParameter("@OC", value.OC),
         new SqlParameter("@HN", value.HN),
         new SqlParameter("@USERID", value.UserId),
         new SqlParameter("@WS", value.WS),
         new SqlParameter("@PCIID", value.PCIID),
         new SqlParameter("@OBJID", value.OBJID),
         new SqlParameter("@RNO", value.RNO),
         new SqlParameter("@RSNO", value.RSNO),
         new SqlParameter("@ARR_Date", value.ARR),
         new SqlParameter("@DEP_Date", value.DEP),
         new SqlParameter("@NOPAX", value.NOPAX),
         new SqlParameter("@RT", value.RT),
         new SqlParameter("@ORG", value.ORG),
         new SqlParameter("@SIHOT_Document_Id", value.SIHOT_Document_Id),
         new SqlParameter("@SIHOT_Reservation_Id", value.SIHOT_Reservation_Id)
     };
     SqlHelper.ExecuteNonQuery(Helper.ConnectionString, CommandType.StoredProcedure, "insert_tblSIHOT_Reservation", param);
 }
    //Change of Reservation uploadxml
    void ProcessCR(string strFileName)
    {
        //Clears the dataset
        dsAux.Reset();
        dsAux.ReadXml(strFileName);
        SqlDataAdapter myDataAdapter = new SqlDataAdapter();
        m_SH_Hotel_Num = 0;

        //Saving the document
        DataRow myRow;
        DataTable tbl = dsAux.Tables[0];
        //Checking how many table contains the dataset

        if (dsAux.Tables.Count ==2)
        {
            ProcessCR_2_tbls(strFileName);
            return;
        }
        else if (dsAux.Tables.Count ==5)
        {
            //cards inside CR  do the same but remove CR
        }

        else if (dsAux.Tables.Count != 4)
        {
            return;
        }
        //dsAux.Tables[0].ReadXml(

        SIHOT_Reservation item = new SIHOT_Reservation();
        //Being used to get back the reservation from the Database
        SIHOT_Reservation itemReturn = new SIHOT_Reservation();
        for (int i = 0; i < tbl.Rows.Count; i++)
        {
            myRow = tbl.Rows[i];
            m_TRansactionNumber = Helper.ToInt32(myRow["TN"].ToString());
            item.TN = m_TRansactionNumber;
            item.OC = myRow["OC"].ToString();
            item.HN = Helper.ToInt32(myRow["HN"].ToString());

            m_SH_Hotel_Num = Helper.ToInt32(myRow["HN"].ToString());
            if (myRow.Table.Columns.Contains("WS"))
                item.WS = myRow["WS"].ToString();
            if (myRow.Table.Columns.Contains("USERID"))
                item.UserId = myRow["USERID"].ToString();
            item.OBJID = myRow["OBJID"].ToString();
            item.ORG = myRow["ORG"].ToString();

            item.SIHOT_Document_Id = myRow["SIHOT-Document_Id"].ToString();
        }

        //Second Part Saving the reservation
        myRow = null;
        tbl = dsAux.Tables[1];
        for (int i = 0; i < tbl.Rows.Count; i++)
        {
            myRow = tbl.Rows[i];
            //Check if this reservation already exist
            item.RNO = Helper.ToInt32(myRow["RNO"].ToString());
            item.RSNO = Helper.ToInt32(myRow["RSNO"].ToString());
            m_ReservationNum = Helper.ToInt32(myRow["RNO"].ToString());
            m_ReservationSubNum = Helper.ToInt32(myRow["RSNO"].ToString());
            //throw new Exception("The Value is here: " + Helper.ToDT_mm_DD_yyyy(myRow["ARR"].ToString()) + Helper.ToDateTime(myRow["DEP"].ToString()));
            //throw new Exception("The Value is here: " + myRow["ARR"].ToString() + " to "+ myRow["DEP"].ToString());

            //For Hotels with format dd/mm/yyyy
            if (item.HN == 5907 || item.HN == 7172)
            {
                if (myRow.Table.Columns.Contains("ARR"))
                    //item.ARR = DateTime.Parse(myRow["ARR"].ToString());
                    item.ARR = Helper.ToDateTime(myRow["ARR"].ToString());

                if (myRow.Table.Columns.Contains("DEP"))
                    item.DEP = Helper.ToDateTime(myRow["DEP"].ToString());
            }
            else if (item.WS == "A_LLAVES" || item.WS == "GTE_FRONT" || item.WS == "MPVRTELEFONOS2")
            {
                if (myRow.Table.Columns.Contains("ARR"))
                    item.ARR = Helper.ToDateTime(myRow["ARR"].ToString());

                if (myRow.Table.Columns.Contains("DEP"))
                    item.DEP = Helper.ToDateTime(myRow["DEP"].ToString());
            }
            else
            {
                //For Hotels with format dd/mm/yyyy
                if (myRow.Table.Columns.Contains("ARR"))
                    item.ARR = Helper.ToDT_mm_DD_yyyy(myRow["ARR"].ToString());
                if (myRow.Table.Columns.Contains("DEP"))
                    item.DEP = Helper.ToDT_mm_DD_yyyy(myRow["DEP"].ToString());
            }

            item.NOPAX = Helper.ToInt32(myRow["NOPAX"].ToString());
            item.RT = myRow["RT"].ToString();
            item.OBJID = myRow["OBJID"].ToString();

            item.SIHOT_Reservation_Id = myRow["SIHOT-Reservation_Id"].ToString(); ;
            //Check if that reservation exist or not is PMS Interface
            //if (item.RNO==219157)
            //{
            //    throw new Exception(item.RNO.ToString() +"/"+ item.RSNO);
            //}
            itemReturn = SIHOT_ReservationDB.SelectReservationFC(item);
            //if (itemReturn.RNO == 0 && itemReturn.RSNO==0)
            if (itemReturn.RNO == 0 && itemReturn.RSNO == 0)
            {
                //insert that row in the table
                SIHOT_ReservationDB.InsertSIHOT_Reservation(item);
            }
            else if (itemReturn.RNO > 0)
            {
               SIHOT_ReservationDB.Update_tblSIHOT_ReservationByResNum(item);
            }

        }

        //Saving the Person
        myRow = null;

        tbl = dsAux.Tables[2];
        if (dsAux.Tables.IndexOf(tbl) > -1)
        {
            SIHOTPerson itemPerson = new SIHOTPerson();
            //Being used to get back the reservation from the Database
            SIHOTPerson itemPersonReturn = new SIHOTPerson();
            for (int i = 0; i < tbl.Rows.Count; i++)
            {
                myRow = tbl.Rows[i];
                itemPerson.TN = m_TRansactionNumber;
                itemPerson.HN = m_SH_Hotel_Num;
                itemPerson.RNO = m_ReservationNum;
                itemPerson.RSNO = m_ReservationSubNum;
                //For Hotels with format dd/mm/yyyy
                if (item.HN == 5907 || item.HN == 7172)
                {
                    if (myRow.Table.Columns.Contains("ARR"))
                        //item.ARR = DateTime.Parse(myRow["ARR"].ToString());
                        itemPerson.ARR = Helper.ToDateTime(myRow["ARR"].ToString());

                    if (myRow.Table.Columns.Contains("DEP"))
                        itemPerson.DEP = Helper.ToDateTime(myRow["DEP"].ToString());
                }
                else if (item.WS == "A_LLAVES" || item.WS == "GTE_FRONT" || item.WS == "MPVRTELEFONOS2")
                {
                    if (myRow.Table.Columns.Contains("ARR"))
                        itemPerson.ARR = Helper.ToDateTime(myRow["ARR"].ToString());

                    if (myRow.Table.Columns.Contains("DEP"))
                        itemPerson.DEP = Helper.ToDateTime(myRow["DEP"].ToString());
                }
                else
                {
                    //For Hotels with format dd/mm/yyyy
                    if (myRow.Table.Columns.Contains("ARR"))
                        itemPerson.ARR = Helper.ToDT_mm_DD_yyyy(myRow["ARR"].ToString());
                    if (myRow.Table.Columns.Contains("DEP"))
                        itemPerson.DEP = Helper.ToDT_mm_DD_yyyy(myRow["DEP"].ToString());
                }

                //Check if this PCIID already exist
                //itemPerson.RNO = item.RNO;
                if (myRow.Table.Columns.Contains("PCIID"))
                    itemPerson.PCIID = Helper.ToInt32(myRow["PCIID"].ToString());
                itemPerson.NOPAX = Helper.ToInt32(myRow["NOPAX"].ToString());
                itemPerson.TG = myRow["TG"].ToString();
                itemPerson.HASPREV = Helper.ToBoolean(myRow["HASPREV"].ToString());
                itemPerson.HASSUCC = Helper.ToBoolean(myRow["HASSUCC"].ToString());
                if (myRow.Table.Columns.Contains("GID"))
                    itemPerson.GID = myRow["GID"].ToString().Trim();
                //If DataRow.Table.Columns.Contains("column") Then
                if (myRow.Table.Columns.Contains("VIP"))
                    itemPerson.VIP = myRow["VIP"].ToString();

                if (myRow.Table.Columns.Contains("TITLE"))
                    itemPerson.Title = myRow["TITLE"].ToString();
                if (myRow.Table.Columns.Contains("Street"))
                    itemPerson.Address.Street1 = myRow["STREET"].ToString();
                if (myRow.Table.Columns.Contains("CITY"))
                    itemPerson.Address.City = myRow["CITY"].ToString();
                if (myRow.Table.Columns.Contains("ZIP"))
                    itemPerson.Address.Zip = myRow["ZIP"].ToString();
                if (myRow.Table.Columns.Contains("COUNTRY"))
                    itemPerson.Address.Country = myRow["COUNTRY"].ToString();
                //itemPerson.Address.State = myRow["STATE"].ToString();
                //itemPerson.Area = myRow["AREA"].ToString();
                if (myRow.Table.Columns.Contains("PHONE"))
                    itemPerson.Phone = myRow["PHONE"].ToString();
                if (myRow.Table.Columns.Contains("FAX"))
                    itemPerson.Fax = myRow["FAX"].ToString();
                if (myRow.Table.Columns.Contains("EMAIL"))
                    itemPerson.EMAIL = myRow["EMAIL"].ToString();
                //throw new Exception(myRow["DOB"].ToString());
                //if (myRow.Table.Columns.Contains("DOB"))
                //{
                //    if (myRow["DOB"].ToString() != "")
                //        itemPerson.DOB = Helper.ToDateTime(myRow["DOB"].ToString());
                //}
                if (myRow.Table.Columns.Contains("Salutation"))
                {
                    itemPerson.Salutation = myRow["Salutation"].ToString();
                }
                if (myRow.Table.Columns.Contains("TAXID"))
                {
                        itemPerson.TAXID = myRow["TAXID"].ToString();
                }

                if (myRow.Table.Columns.Contains("SEX"))
                    itemPerson.Sex = myRow["SEX"].ToString();
                //We assign the agency name in the last name; when is an insert we use the agency name and the last name. When is an update we only update the last name
                itemPerson.AgencyName = myRow["SN"].ToString();
                itemPerson.SN = myRow["SN"].ToString();
                itemPerson.CN = myRow["CN"].ToString();
                itemPerson.MC = myRow["MC"].ToString();
                itemPerson.RN = myRow["RN"].ToString();
                itemPerson.CAT = myRow["CAT"].ToString();
                itemPerson.SF = myRow["SF"].ToString();
                if (myRow.Table.Columns.Contains("MATCHCODE"))
                    if (myRow.Table.Columns.Contains("MATCHCODE").ToString() != "")
                        itemPerson.MATCHCODE = myRow["MATCHCODE"].ToString();

                itemPerson.SIHOT_Reservation_Id = myRow["SIHOT-Reservation_Id"].ToString();
                itemPerson.SIHOT_Person_Id = myRow["SIHOT-Person_Id"].ToString();
                //Chck if the PCIID already exist in the table
                int count=0;
                itemPersonReturn = SIHOTPersonDB.SelectPersonByPCIID_CR(itemPerson, out count);
                if (itemPersonReturn.PCIID == 0)
                    //if (count == 0)
                    //if (count != 0)
                {
                    //insert that row in the table; PCIID doesn't exist
                    SIHOTPersonDB.InsertSIHOT_Person(itemPerson);
                    //update that row in the table; PCIID already exist
                }
                else if (itemPersonReturn.PCIID > 0)
                {
                    Response.Write("update" + itemPerson.PCIID + itemPerson.HN);
                    SIHOTPersonDB.UpdateSIHOT_Person_CR(itemPerson);
                }
            }
        }

        //Saving the Package
        myRow = null;
        tbl = dsAux.Tables[3];
        SIHOT_Package itemPackage = new SIHOT_Package();
        SIHOT_Package itemPackageReturn = new SIHOT_Package();
        for (int i = 0; i < tbl.Rows.Count; i++)
        {
            //global variable
            myRow = tbl.Rows[i];
            itemPackage.TN = m_TRansactionNumber;
            if (myRow["From"].ToString() != null)
                itemPackage.SIHOT_PACKAGE_FROM = Helper.ToDateTime(myRow["From"].ToString());
            if (myRow["To"].ToString() != null)
                itemPackage.SIHOT_PACKAGE_TO = Helper.ToDateTime(myRow["To"].ToString());
            if (myRow["SID"].ToString() != null)
                itemPackage.SIHOT_PACKAGE_SID = myRow["SID"].ToString();
            if (myRow["RS"].ToString() != null)
                itemPackage.SIHOT_PACKAGE_RS = myRow["RS"].ToString();
            if (myRow["SIDT"].ToString() != null)
                itemPackage.SIHOT_PACKAGE_SIDT = myRow["SIDT"].ToString();
            if (myRow["SIHOT-Person_Id"].ToString() != null)
                itemPackage.SIHOT_Person_Id = myRow["SIHOT-Person_Id"].ToString();

            //Right now I don't need the package
            //itemPackageReturn = SIHOTPackageDB.s;
            //if (itemPackageReturn.PCIID == 0)
            //{
            //    //inserting the row in the table
            //    SIHOTPackageDB.InsertSIHOT_Package(itemPackage);
            //}
            //else if (itemPackageReturn.PCIID == 0)
            //{
            //    SIHOTPackageDB.UPDATESIHOT_Package(itemPackage);
            //}
        }

        //myDataAdapter.Fill(datos2, "TABLA3");
        //myDataAdapter.Update(datos2, nombretabla);
        //myDataAdapter.Update(

        //string filePath = "C:\\temp\\SIHOTForeCast2.xml";
        //dsAux.ReadXml(filePath);
        //GridView1.DataSource = dsAux;
        //GridView1.DataBind();

        //DataSet ds = new DataSet();
        //ds.ReadXml(Request.PhysicalApplicationPath + @"\App_Data\" + "MilitaryTime.xml");
        //load an xml file into a dataset and bind it to a GridView
        //System.Data.DataSet myDS = new System.Data.DataSet();

        ////myDS.ReadXml(Server.MapPath(@"C:\temp\SIHOTForeCast2.xml"));
        //myDS.ReadXml(Request.PhysicalApplicationPath + @"\App_Data\" + "SIHOTForeCast2.xml");

        ////bind with a GridView for display
        //GridView1.DataSource = myDS;
        //GridView1.DataBind();
    }
    void ProcessMultipleFiles()
    {
        DataTable tbl;
        foreach (DataTable dtTemp in dsAux.Tables)
        {
            //cmbDataSetTables.Items.Add(dtTemp.TableName);
            //Saving the document
            DataRow myRow;
            SIHOT_Reservation item = new SIHOT_Reservation();

            if (dtTemp.TableName == "SIHOT-Document")
            {
                myRow = null;
                tbl = dsAux.Tables[dtTemp.TableName];
                for (int i = 0; i < tbl.Rows.Count; i++)
                {
                    myRow = tbl.Rows[i];
                    m_TRansactionNumber = Helper.ToInt32(myRow["TN"].ToString());
                    item.TN = m_TRansactionNumber;
                    item.OC = myRow["OC"].ToString();
                    item.HN = Helper.ToInt32(myRow["HN"].ToString());
                    m_SH_Hotel_Num = Helper.ToInt32(myRow["HN"].ToString());

                    item.SIHOT_Document_Id = myRow["SIHOT-Document_Id"].ToString();
                    //insert that row in the table
                        this.GridView1.DataSource = dsAux.Tables[dtTemp.TableName];
                        GridView1.DataBind();
                }
            }

            if (dtTemp.TableName == "SIHOT-Reservation")
            {
                myRow = null ;
                tbl = dsAux.Tables[dtTemp.TableName];

                for (int i = 0; i < tbl.Rows.Count; i++)
                {
                    myRow = tbl.Rows[i];
                    item.RNO = Helper.ToInt32(myRow["RNO"].ToString());
                    item.RSNO = Helper.ToInt32(myRow["RSNO"].ToString());
                    item.ARR = Helper.ToDateTime(myRow["ARR"].ToString());
                    //items.ARR.Value.ToString("MM/dd/yyyy");
                    item.DEP = Helper.ToDateTime(Helper.ToDT_mm_DD_yyyy(myRow["DEP"].ToString()));
                    item.NOPAX = Helper.ToInt32(myRow["NOPAX"].ToString());
                    item.RT = myRow["RT"].ToString();
                    item.SIHOT_Reservation_Id = myRow["SIHOT-Reservation_Id"].ToString();
                }
                //insert that row in the table
                SIHOT_ReservationDB.InsertSIHOT_Reservation(item);
                //Response.Write("Registro agregado exitosamente");

                    this.GridView2.DataSource = dsAux.Tables[dtTemp.TableName];
                    GridView2.DataBind();

            }

            if (dtTemp.TableName == "SIHOT-Person")
            {

                //Second Part Saving the reservation
                //Check if this reservation already exist

                //Saving the Person
                myRow = null;
                tbl = dsAux.Tables[dtTemp.TableName];
                SIHOTPerson itemPerson = new SIHOTPerson();
                for (int i = 0; i < tbl.Rows.Count; i++)
                {
                    myRow = tbl.Rows[i];

                    itemPerson.TN = m_TRansactionNumber;
                    itemPerson.ARR = Helper.ToDateTime(myRow["ARR"].ToString());
                    itemPerson.DEP = Helper.ToDateTime(myRow["DEP"].ToString());
                    //Check if this PCIID already exist
                    itemPerson.PCIID = Helper.ToInt32(myRow["PCIID"].ToString());
                    itemPerson.NOPAX = Helper.ToInt32(myRow["NOPAX"].ToString());
                    itemPerson.TG = myRow["PCIID"].ToString();
                    itemPerson.HASPREV = Helper.ToBoolean(myRow["HASPREV"].ToString());
                    itemPerson.HASSUCC = Helper.ToBoolean(myRow["HASSUCC"].ToString());
                    itemPerson.GID = myRow["GID"].ToString();
                    itemPerson.LN = myRow["LN"].ToString();
                    itemPerson.VIP = myRow["VIP"].ToString();
                    itemPerson.Title = myRow["TITLE"].ToString();
                    itemPerson.Address.Street1 = myRow["STREET"].ToString();
                    itemPerson.Address.City = myRow["CITY"].ToString();
                    itemPerson.Address.Zip = myRow["ZIP"].ToString();
                    itemPerson.Address.Country = myRow["COUNTRY"].ToString();
                    //itemPerson.Address.State = myRow["STATE"].ToString();
                    //itemPerson.Area = myRow["AREA"].ToString();
                    itemPerson.Phone = myRow["PHONE"].ToString();
                    itemPerson.Fax = myRow["FAX"].ToString();
                    itemPerson.EMAIL = myRow["EMAIL"].ToString();
                    //if (myRow["DOB"].ToString()!=null)
                    //    itemPerson.DOB = Helper.ToDateTime(myRow["DOB"].ToString());

                    itemPerson.Sex = myRow["SEX"].ToString();
                    itemPerson.SN = myRow["SN"].ToString();
                    itemPerson.CN = myRow["CN"].ToString();
                    itemPerson.MC = myRow["MC"].ToString();
                    itemPerson.RN = myRow["RN"].ToString();
                    itemPerson.CAT = myRow["CAT"].ToString();

                    itemPerson.SIHOT_Reservation_Id = myRow["SIHOT-Reservation_Id"].ToString();
                    itemPerson.SIHOT_Person_Id = myRow["SIHOT-Person_Id"].ToString();
                    //insert that row in the table
                    SIHOTPersonDB.InsertSIHOT_Person(itemPerson);
                    if (dtTemp.TableName == "SIHOT-Person")
                    {
                        this.GridView3.DataSource = dsAux.Tables[dtTemp.TableName];
                        GridView3.DataBind();
                    }

                }
            }
            if (dtTemp.TableName == "SIHOT-Package")
            {
                //Saving the Package
                myRow = null;
                tbl = dsAux.Tables[dtTemp.TableName];
                SIHOT_Package itemPackage = new SIHOT_Package();
                for (int i = 0; i < tbl.Rows.Count; i++)
                {
                    //global variable
                    myRow = tbl.Rows[i];
                    itemPackage.TN = m_TRansactionNumber;
                    if (myRow["From"].ToString() != null)
                        itemPackage.SIHOT_PACKAGE_FROM = Helper.ToDateTime(myRow["From"].ToString());
                    if (myRow["To"].ToString() != null)
                        itemPackage.SIHOT_PACKAGE_TO = Helper.ToDateTime(myRow["To"].ToString());
                    if (myRow["SID"].ToString() != null)
                        itemPackage.SIHOT_PACKAGE_SID = myRow["SID"].ToString();
                    if (myRow["RS"].ToString() != null)
                        itemPackage.SIHOT_PACKAGE_RS = myRow["RS"].ToString();
                    if (myRow["SIDT"].ToString() != null)
                        itemPackage.SIHOT_PACKAGE_SIDT = myRow["SIDT"].ToString();
                    if (myRow["SIHOT-Person_Id"].ToString() != null)
                        itemPackage.SIHOT_Person_Id = myRow["SIHOT-Person_Id"].ToString();
                    //inserting the row in the table
                    SIHOTPackageDB.InsertSIHOT_Package(itemPackage);
                    if (dtTemp.TableName == "SIHOT-Package")
                    {
                        this.GridView4.DataSource = dsAux.Tables[dtTemp.TableName];
                        GridView4.DataBind();
                    }

                }
            }
        }
    }
    void ProcessCR_2_tbls(string strFileName)
    {
        //Clears the dataset
        dsAux.Reset();
        dsAux.ReadXml(strFileName);
        SqlDataAdapter myDataAdapter = new SqlDataAdapter();
        m_SH_Hotel_Num = 0;

        //Saving the document
        DataRow myRow;
        DataTable tbl = dsAux.Tables[0];

        SIHOT_Reservation item = new SIHOT_Reservation();
        //Being used to get back the reservation from the Database
        SIHOT_Reservation itemReturn = new SIHOT_Reservation();
        for (int i = 0; i < tbl.Rows.Count; i++)
        {
            myRow = tbl.Rows[i];
            m_TRansactionNumber = Helper.ToInt32(myRow["TN"].ToString());
            item.TN = m_TRansactionNumber;
            item.OC = myRow["OC"].ToString();
            item.HN = Helper.ToInt32(myRow["HN"].ToString());

            m_SH_Hotel_Num = Helper.ToInt32(myRow["HN"].ToString());
            if (myRow.Table.Columns.Contains("WS"))
                item.WS = myRow["WS"].ToString();
            if (myRow.Table.Columns.Contains("USERID"))
                item.UserId = myRow["USERID"].ToString();
            item.OBJID = myRow["OBJID"].ToString();
            item.ORG = myRow["ORG"].ToString();

            item.SIHOT_Document_Id = myRow["SIHOT-Document_Id"].ToString();
        }

        //Second Part Saving the reservation
        myRow = null;
        tbl = dsAux.Tables[1];
        for (int i = 0; i < tbl.Rows.Count; i++)
        {
            myRow = tbl.Rows[i];
            //Check if this reservation already exist
            item.RNO = Helper.ToInt32(myRow["RNO"].ToString());
            item.RSNO = Helper.ToInt32(myRow["RSNO"].ToString());
            m_ReservationNum = Helper.ToInt32(myRow["RNO"].ToString());
            m_ReservationSubNum = Helper.ToInt32(myRow["RSNO"].ToString());
            //throw new Exception("The Value is here: " + Helper.ToDT_mm_DD_yyyy(myRow["ARR"].ToString()) + Helper.ToDateTime(myRow["DEP"].ToString()));
            //throw new Exception("The Value is here: " + myRow["ARR"].ToString() + " to "+ myRow["DEP"].ToString());

            //For Hotels with format dd/mm/yyyy
            if (item.HN == 5907 || item.HN == 7172)
            {
                if (myRow.Table.Columns.Contains("ARR"))
                    //item.ARR = DateTime.Parse(myRow["ARR"].ToString());
                    item.ARR = Helper.ToDateTime(myRow["ARR"].ToString());

                if (myRow.Table.Columns.Contains("DEP"))
                    item.DEP = Helper.ToDateTime(myRow["DEP"].ToString());
            }
            else if (item.WS == "A_LLAVES" || item.WS == "GTE_FRONT" || item.WS == "MPVRTELEFONOS2")
            {
                if (myRow.Table.Columns.Contains("ARR"))
                    item.ARR = Helper.ToDateTime(myRow["ARR"].ToString());

                if (myRow.Table.Columns.Contains("DEP"))
                    item.DEP = Helper.ToDateTime(myRow["DEP"].ToString());
            }
            else
            {
                //For Hotels with format dd/mm/yyyy
                if (myRow.Table.Columns.Contains("ARR"))
                    item.ARR = Helper.ToDT_mm_DD_yyyy(myRow["ARR"].ToString());
                if (myRow.Table.Columns.Contains("DEP"))
                    item.DEP = Helper.ToDT_mm_DD_yyyy(myRow["DEP"].ToString());
            }

            item.NOPAX = Helper.ToInt32(myRow["NOPAX"].ToString());
            item.RT = myRow["RT"].ToString();
            item.OBJID = myRow["OBJID"].ToString();

            if (myRow.Table.Columns.Contains("SIHOT-Reservation_Id"))
                item.SIHOT_Reservation_Id = myRow["SIHOT-Reservation_Id"].ToString(); ;
            //Check if that reservation exist or not is PMS Interface
            //if (item.RNO==219157)
            //{
            //    throw new Exception(item.RNO.ToString() +"/"+ item.RSNO);
            //}
            itemReturn = SIHOT_ReservationDB.SelectReservationFC(item);
            //if (itemReturn.RNO == 0 && itemReturn.RSNO==0)
            if (itemReturn.RNO == 0 && itemReturn.RSNO == 0)
            {
                //insert that row in the table
                SIHOT_ReservationDB.InsertSIHOT_Reservation(item);
            }
            else if (itemReturn.RNO > 0)
            {
                SIHOT_ReservationDB.Update_tblSIHOT_ReservationByResNum(item);
            }

        }
    }
    //Search the reservation in PMS interface
    public static SIHOT_Reservation SelectReservationFC(SIHOT_Reservation value)
    {
        SIHOT_Reservation retval = new SIHOT_Reservation();

        SqlParameter[] param = new SqlParameter[]{
            new SqlParameter("@parHN", value.HN),
            new SqlParameter("@parReservationNumber", value.RNO),
            new SqlParameter("@parReservationSubNumber", value.RSNO)
        };

        using (SqlDataReader reader = SqlHelper.ExecuteReader(Helper.ConnectionString, CommandType.StoredProcedure, "Select_tblSIHOT_ReservationFC", param))
        {
            if (reader.Read())
            {
                retval = (Read(reader));
            }
        }
        return retval;
    }