Пример #1
0
        private List <CLayer.PropertyFiles> GetGDSImages(string response, long id)
        {
            List <CLayer.PropertyFiles> picturelist = new List <CLayer.PropertyFiles>();
            XmlDocument xmlDoc = new XmlDocument();
            string      a      = string.Empty;

            //  List<CLayer.PropertyFiles> pictureslist = new List<CLayer.PropertyFiles>();
            try
            {
                // xmlDoc.Load(@"F:\HotelDescriptiveIno.xml");
                xmlDoc.LoadXml(response);
                XmlNamespaceManager xmlnsManager    = new System.Xml.XmlNamespaceManager(xmlDoc.NameTable);
                XmlNode             node            = null;
                XmlNode             nodeImages      = null;
                XmlNode             nodeguestImages = null;

                string nodeRoot           = "/soapenv:Envelope/soapenv:Body/";
                string nodevalue          = "/si:OTA_HotelDescriptiveInfoRS/si:HotelDescriptiveContents/si:HotelDescriptiveContent/si:HotelInfo/si:Descriptions";                                    //"/si:MultimediaDescriptions/si:MultimediaDescription/si:ImageItems/si:ImageItem/si:ImageFormat/si:URL";
                string nodevalueguestroom = "/si:OTA_HotelDescriptiveInfoRS/si:HotelDescriptiveContents/si:HotelDescriptiveContent/si:HotelInfo/si:CategoryCodes/si:CategoryCodes/si:GuestRoomInfo"; //si:MultimediaDescriptions/si:MultimediaDescription/si:ImageItems/si:ImageItem/si:ImageFormat/si:URL";

                xmlnsManager.AddNamespace("soapenv", "http://schemas.xmlsoap.org/soap/envelope/");
                xmlnsManager.AddNamespace("awsse", "http://xml.amadeus.com/2010/06/Session_v3");
                xmlnsManager.AddNamespace("wsa", "http://www.w3.org/2005/08/addressing");
                xmlnsManager.AddNamespace("si", "http://www.opentravel.org/OTA/2003/05");

                nodeImages      = xmlDoc.SelectSingleNode(nodeRoot + nodevalue, xmlnsManager);
                nodeguestImages = xmlDoc.SelectSingleNode(nodeRoot + nodevalueguestroom, xmlnsManager);

                #region MAIN IMAGES
                if (nodeImages != null)
                {
                    XmlNodeList list         = nodeImages.SelectNodes("//si:MultimediaDescriptions/si:MultimediaDescription", xmlnsManager);
                    int         PictureCount = 0;
                    int         nodes        = 0;
                    if (list != null)
                    {
                        foreach (XmlNode item in list)
                        {
                            var subItem = item.SelectNodes("//si:ImageItems", xmlnsManager);
                            if (subItem != null)
                            {
                                foreach (XmlNode sItem in subItem)
                                {
                                    IEnumerable <XmlNode> subInnerItem = sItem.SelectNodes("//si:ImageItem", xmlnsManager).Cast <XmlNode>().OrderByDescending(r => Convert.ToDateTime(r.Attributes["LastModifyDateTime"].Value));
                                    // IEnumerable<XmlNode> subInnerItem = sItem.SelectNodes("//si:ImageItem/si:ImageFormat[@DimensionCategory='E']", xmlnsManager).Cast<XmlNode>().OrderByDescending(r => Convert.ToDecimal(r.Attributes["LastModifyDateTime"].Value));
                                    if (subInnerItem != null)
                                    {
                                        foreach (XmlNode sItem1 in subInnerItem)
                                        {
                                            var subUrlItem = sItem1.SelectNodes("//si:ImageItem/si:ImageFormat[@DimensionCategory='E']", xmlnsManager);
                                            if (subUrlItem != null)
                                            {
                                                PictureCount = subInnerItem.Count();
                                                foreach (XmlNode UItem in subUrlItem)
                                                {
                                                    var UrlItem = UItem.ChildNodes[0].InnerText;  //url

                                                    bool bImageExists = true;
                                                    if (!string.IsNullOrEmpty(UrlItem))
                                                    {
                                                        bImageExists = APIUtility.DoesImageExistRemotely(UrlItem);
                                                    }

                                                    if (bImageExists)
                                                    {
                                                        CLayer.PropertyFiles picture = new CLayer.PropertyFiles();
                                                        picture.FileName   = UrlItem;// itemimg.ImageFormat.Where(x => x.DimensionCategory == "E").ToList()[0].URL;
                                                        picture.PropertyId = id;
                                                        picture.IsValid    = true;
                                                        picturelist.Add(picture);
                                                        BLayer.Property.GDSSaveImageurl(id, picture.FileName);
                                                    }
                                                    nodes++;
                                                    if (nodes == PictureCount)
                                                    {
                                                        break;
                                                    }
                                                }
                                            }
                                            if (nodes == PictureCount)
                                            {
                                                break;
                                            }
                                        }
                                    }
                                    if (nodes == PictureCount)
                                    {
                                        break;
                                    }
                                }
                            }
                            if (nodes == PictureCount)
                            {
                                break;
                            }
                        }
                    }
                }
                #endregion


                #region GUESTROOMIMAGES
                if (nodeguestImages != null)
                {
                    XmlNodeList listGuestRoomImages = nodeguestImages.SelectNodes("//si:MultimediaDescriptions/si:MultimediaDescription", xmlnsManager);
                    if (listGuestRoomImages != null)
                    {
                        int PictureCount = 0;
                        int nodes        = 0;
                        foreach (XmlNode item in listGuestRoomImages)
                        {
                            var subItem = item.SelectNodes("//si:ImageItems", xmlnsManager);
                            if (subItem != null)
                            {
                                foreach (XmlNode sItem in subItem)
                                {
                                    IEnumerable <XmlNode> subInnerItem = sItem.SelectNodes("//si:ImageItem", xmlnsManager).Cast <XmlNode>().OrderByDescending(r => Convert.ToDateTime(r.Attributes["LastModifyDateTime"].Value));
                                    // IEnumerable<XmlNode> subInnerItem = sItem.SelectNodes("//si:ImageItem/si:ImageFormat[@DimensionCategory='E']", xmlnsManager).Cast<XmlNode>().OrderByDescending(r => Convert.ToDecimal(r.Attributes["LastModifyDateTime"].Value));
                                    if (subInnerItem != null)
                                    {
                                        foreach (XmlNode sItem1 in subInnerItem)
                                        {
                                            var subUrlItem = sItem1.SelectNodes("//si:ImageItem/si:ImageFormat[@DimensionCategory='E']", xmlnsManager);
                                            if (subUrlItem != null)
                                            {
                                                PictureCount = subInnerItem.Count();
                                                foreach (XmlNode UItem in subUrlItem)
                                                {
                                                    var  UrlItem      = UItem.ChildNodes[0].InnerText; //url
                                                    bool bImageExists = true;
                                                    if (!string.IsNullOrEmpty(UrlItem))
                                                    {
                                                        bImageExists = APIUtility.DoesImageExistRemotely(UrlItem);
                                                    }

                                                    if (bImageExists)
                                                    {
                                                        CLayer.PropertyFiles picture = new CLayer.PropertyFiles();
                                                        picture.FileName   = UrlItem;// itemimg.ImageFormat.Where(x => x.DimensionCategory == "E").ToList()[0].URL;
                                                        picture.PropertyId = id;
                                                        picturelist.Add(picture);
                                                        BLayer.Property.GDSSaveImageurl(id, picture.FileName);
                                                    }
                                                    nodes++;
                                                    if (nodes == PictureCount)
                                                    {
                                                        break;
                                                    }
                                                }
                                            }
                                            if (nodes == PictureCount)
                                            {
                                                break;
                                            }
                                        }
                                    }
                                    if (nodes == PictureCount)
                                    {
                                        break;
                                    }
                                }
                            }
                            if (nodes == PictureCount)
                            {
                                break;
                            }
                        }
                    }
                }

                #endregion
            }
            catch (Exception ex)
            {
                LogHandler.AddLog(ex.Message);
            }
            return(picturelist);
        }
Пример #2
0
        public bool UpdatePropertyStarRatings()
        {
            bool   result  = false;
            long   id1     = 0;
            string HotelID = string.Empty;

            try
            {
                String hotelcode = string.Empty;

                List <CLayer.Property>               objProperties = BLayer.Property.GetAllGDSProperties().ToList();
                CLayer.GDSBookingDetails.Envelope    ss            = new CLayer.GDSBookingDetails.Envelope();
                CLayer.GDSBookingDetailsAdv.Envelope ssAdv         = new CLayer.GDSBookingDetailsAdv.Envelope();// ser.Deserialize<CLayer.GDSBookingDetailsAdv.Envelope>(hotel);
                long id = 0;
                foreach (var pitem in objProperties)
                {
                    try
                    {
                        //  #region Update Property Description
                        StringBuilder Description = new StringBuilder();
                        //Update property description start
                        Serializer ser = new Serializer();
                        id  = pitem.PropertyId;
                        id1 = id;
                        string HotelCode = pitem.HotelID;
                        HotelID = HotelCode;
                        string hotel = GetGDS_Hotel_Details(HotelCode);



                        ss = ser.Deserialize <CLayer.GDSBookingDetails.Envelope>(hotel);

                        DataTable dtHotel = BLayer.Property.GetHotelFormattedDescription(id);
                        string    FormattedDescription = string.Empty;
                        if (dtHotel.Rows.Count > 0)
                        {
                            foreach (DataRow dr in dtHotel.Rows)
                            {
                                FormattedDescription = Convert.ToString(dr["FormattedDescription"]);
                            }
                        }

                        FormattedDescription = GDSProcess.GDSFormatDescription.GetFormattedDescription(hotel);



                        int StarRatings     = GDSProcess.GDSFormatDescription.StarRatings;
                        int LocalStarRating = APIUtility.GetStarRating(hotel);
                        if (LocalStarRating > 0)
                        {
                            BLayer.Property.GDSUpdatePropertyStarRatings(id, LocalStarRating);
                        }
                        if ((LocalStarRating == 0) && (StarRatings == 0))
                        {
                            BLayer.Property.GDSUpdatePropertyStarRatings(id, 0);
                        }
                    }
                    catch (Exception ex)
                    {
                        //  LogHandler.AddLog("Error in-"+hotelcode);
                        WriteToLog(HotelID.ToString() + "-" + ex.Message, true);
                    }
                    finally
                    {
                    }
                }
                result = true;
            }
            catch (Exception ex)
            {
                LogHandler.AddLog(ex.Message);
                string a = id1.ToString();
                result = false;

                throw ex;
            }
            return(result);
        }
Пример #3
0
        public bool UpdatePropertyDescriptionandImages()
        {
            bool   result  = false;
            long   id1     = 0;
            string HotelID = string.Empty;

            try
            {
                String hotelcode = string.Empty;

                List <CLayer.Property> objProperties   = BLayer.Property.GetAllGDSPropertiesWithOutData().ToList();
                List <CLayer.Property> objDescriptions = BLayer.Property.GetAllGDSPropertyDescriptionsWithOutData();
                //  List<CLayer.Property> objFormattedDescriptions = BLayer.Property.GetAllGDSPropertyFormattedDescriptionsWithOutData();
                List <CLayer.Property>               objTitles = BLayer.Property.GetAllGDSPropertyTitlesWithOutData();
                CLayer.GDSBookingDetails.Envelope    ss        = new CLayer.GDSBookingDetails.Envelope();
                CLayer.GDSBookingDetailsAdv.Envelope ssAdv     = new CLayer.GDSBookingDetailsAdv.Envelope();// ser.Deserialize<CLayer.GDSBookingDetailsAdv.Envelope>(hotel);
                long id = 0;
                foreach (var pitem in objProperties)
                {
                    try
                    {
                        #region Update Property Description
                        StringBuilder Description = new StringBuilder();
                        //Update property description start
                        Serializer ser = new Serializer();
                        id  = pitem.PropertyId;
                        id1 = id;
                        string HotelCode = pitem.HotelID;
                        HotelID = HotelCode;
                        string hotel = GetGDS_Hotel_Details(HotelCode);



                        ss = ser.Deserialize <CLayer.GDSBookingDetails.Envelope>(hotel);

                        DataTable dtHotel = BLayer.Property.GetHotelFormattedDescription(id);
                        string    FormattedDescription = string.Empty;
                        if (dtHotel.Rows.Count > 0)
                        {
                            foreach (DataRow dr in dtHotel.Rows)
                            {
                                FormattedDescription = Convert.ToString(dr["FormattedDescription"]);
                            }
                        }



                        if (string.IsNullOrEmpty(FormattedDescription))
                        {
                            FormattedDescription = GDSProcess.GDSFormatDescription.GetFormattedDescription(hotel);
                            BLayer.Property.GDSUpdatePropertyDescriptionFormatted(id, FormattedDescription, GDSProcess.GDSFormatDescription.StarRatings, "");
                        }
                        int LocalStarRating = APIUtility.GetStarRating(hotel);
                        if (LocalStarRating > 0)
                        {
                            BLayer.Property.GDSUpdatePropertyStarRatings(id, LocalStarRating);
                        }



                        if (ss.Body.OTA_HotelDescriptiveInfoRS.HotelDescriptiveContents != null)
                        {
                            var TitleItem = objTitles.Where(x => x.HotelID == HotelCode).ToList();
                            if (TitleItem != null)
                            {
                                if (TitleItem.Count > 0)
                                {
                                    string Title = ss.Body.OTA_HotelDescriptiveInfoRS.HotelDescriptiveContents.HotelDescriptiveContent.HotelName;
                                    BLayer.Property.GDSUpdatePropertyTitle(id, Title);
                                }
                            }

                            #region Update Description Without Formatting

                            if (string.IsNullOrEmpty(FormattedDescription))
                            {
                                var DescriptionItem = objDescriptions.Where(x => x.HotelID == HotelCode).ToList();
                                if (DescriptionItem != null)
                                {
                                    if (DescriptionItem.Count > 0)
                                    {
                                        if (ss.Body.OTA_HotelDescriptiveInfoRS.HotelDescriptiveContents.HotelDescriptiveContent.HotelInfo != null)
                                        {
                                            var description = ss.Body.OTA_HotelDescriptiveInfoRS.HotelDescriptiveContents.HotelDescriptiveContent.HotelInfo.Descriptions;
                                            if (description != null)
                                            {
                                                var descriptionList = description.MultimediaDescriptions.MultimediaDescription;


                                                var Descriptions = from order in descriptionList
                                                                   where order.TextItems != null
                                                                   select order;
                                                string HotelDescription = string.Empty;
                                                foreach (var desc in Descriptions)
                                                {
                                                    if (desc.InfoCode != null)
                                                    {
                                                        foreach (var datas in desc.TextItems.TextItem.Description)
                                                        {
                                                            HotelDescription = HotelDescription + datas.__Text + "#256#";
                                                        }
                                                    }
                                                    // HotelDescription = HotelDescription + "<br>";
                                                }
                                                Description.Append(HotelDescription);

                                                BLayer.Property.GDSUpdatePropertyDescription(id, Description.ToString());
                                            }
                                        }
                                    }
                                }
                            }

                            #endregion

                            //update property description end
                            #endregion

                            #region Update Property Images old method
                            //  var ssw = ss.Body.OTA_HotelDescriptiveInfoRS.HotelDescriptiveContents.HotelDescriptiveContent.HotelInfo.Descriptions;
                            ////  var sswguest = ssAdv.Body.OTA_HotelDescriptiveInfoRS.HotelDescriptiveContents.HotelDescriptiveContent.HotelInfo.CategoryCodes.GuestRoomInfo.Where(x => x.Code == 28);

                            //  if (ssw!=null)
                            //  {
                            //      var sswList = ssw;

                            //      List<CLayer.PropertyFiles> picturelist = new List<CLayer.PropertyFiles>();
                            //      //var filteredOrders = from order in sswList
                            //      //                     where order.ImageItems != null
                            //      //                     select order;

                            //      int nos = 0;
                            //    BLayer.Property.DeleteGDSPropertyImages(id);

                            //      foreach (var item in sswList.MultimediaDescriptions.MultimediaDescription)
                            //      {
                            //          if (item.ImageItems != null)
                            //          {
                            //              var t = item.ImageItems.ImageItem;
                            //              if (t != null)
                            //              {
                            //                  foreach (var itemimg in t)
                            //                  {
                            //                      CLayer.PropertyFiles picture = new CLayer.PropertyFiles();
                            //                      picture.FileName = itemimg.ImageFormat.Where(x => x.DimensionCategory == "E").ToList()[0].URL;
                            //                      picture.PropertyId = id;
                            //                      picturelist.Add(picture);
                            //                      BLayer.Property.GDSSaveImageurl(id, picture.FileName);
                            //                      nos++;
                            //                      if (nos == PictureCount) break;
                            //                  }
                            //              }
                            //          }
                            //          if (nos == PictureCount) break;
                            //      }
                            //      if(picturelist.Count<8)
                            //      {
                            //          ssAdv = ser.Deserialize<CLayer.GDSBookingDetailsAdv.Envelope>(hotel);
                            //          var sswguest = ssAdv.Body.OTA_HotelDescriptiveInfoRS.HotelDescriptiveContents.HotelDescriptiveContent.HotelInfo.CategoryCodes.GuestRoomInfo.Where(x => x.Code == 28);
                            //          if (sswguest != null)
                            //          {
                            //              int PictureCount = 8 - picturelist.Count;
                            //              int ImgCounter = 0;
                            //              foreach (var item in sswguest)
                            //              {
                            //                  foreach (var itemAdv in item.MultimediaDescriptions)
                            //                  {
                            //                      if (itemAdv.ImageItems != null)
                            //                      {
                            //                          var t = itemAdv.ImageItems;
                            //                          if (t != null)
                            //                          {
                            //                              foreach (var itemimg in t)
                            //                              {
                            //                                  CLayer.PropertyFiles picture = new CLayer.PropertyFiles();
                            //                                  picture.FileName = itemimg.ImageFormat.Where(x => x.DimensionCategory == "E").ToList()[0].URL;
                            //                                  picture.PropertyId = id;
                            //                                  picturelist.Add(picture);
                            //                                  BLayer.Property.GDSSaveImageurl(id, picture.FileName);
                            //                                  ImgCounter++;
                            //                                  if (ImgCounter == PictureCount) break;
                            //                              }
                            //                          }
                            //                      }
                            //                      if (ImgCounter == PictureCount) break;
                            //                  }
                            //                  if (ImgCounter == PictureCount) break;
                            //              }
                            //          }
                            //      }


                            //  }
                            #endregion



                            #region update property contact numbers
                            //update GDS Property contact numbers start
                            CLayer.GDSBookingDetails.Envelope GDSBookingDetails = ss;
                            string Phone  = string.Empty;
                            string Mobile = string.Empty;
                            string Email  = string.Empty;
                            if (GDSBookingDetails.Body.OTA_HotelDescriptiveInfoRS.HotelDescriptiveContents.HotelDescriptiveContent.ContactInfos.ContactInfo.Emails != null)
                            {
                                Email = GDSBookingDetails.Body.OTA_HotelDescriptiveInfoRS.HotelDescriptiveContents.HotelDescriptiveContent.ContactInfos.ContactInfo.Emails.Email.ToString();
                            }
                            if (GDSBookingDetails.Body.OTA_HotelDescriptiveInfoRS.HotelDescriptiveContents.HotelDescriptiveContent.ContactInfos.ContactInfo.Phones != null)
                            {
                                if (GDSBookingDetails.Body.OTA_HotelDescriptiveInfoRS.HotelDescriptiveContents.HotelDescriptiveContent.ContactInfos.ContactInfo.Phones.Phone.Where(x => x.PhoneTechType == "1").Count() > 0)
                                {
                                    Phone = GDSBookingDetails.Body.OTA_HotelDescriptiveInfoRS.HotelDescriptiveContents.HotelDescriptiveContent.ContactInfos.ContactInfo.Phones.Phone.Where(x => x.PhoneTechType == "1").FirstOrDefault().PhoneNumber;
                                }
                                if (GDSBookingDetails.Body.OTA_HotelDescriptiveInfoRS.HotelDescriptiveContents.HotelDescriptiveContent.ContactInfos.ContactInfo.Phones.Phone.Where(x => x.PhoneTechType == "5").Count() > 0)
                                {
                                    Mobile = GDSBookingDetails.Body.OTA_HotelDescriptiveInfoRS.HotelDescriptiveContents.HotelDescriptiveContent.ContactInfos.ContactInfo.Phones.Phone.Where(x => x.PhoneTechType == "5").FirstOrDefault().PhoneNumber;
                                }
                                if ((!string.IsNullOrEmpty(Phone)) || (!string.IsNullOrEmpty(Mobile)) || (!string.IsNullOrEmpty(Email)))
                                {
                                    BLayer.Property.GDSUpdatePropertyContactNumbers(id, Phone, Mobile, Email);
                                }
                            }
                            //update GDS Property Contact numbers end
                            //#region Transaction Log

                            //APIUtility.GenerateGDSTransactionLog("", "BulkImageandDescriptionSuccess", 0, (int)CLayer.ObjectStatus.GDSType.BulkHotelImageDescriptionUpdation, 0);

                            //#endregion Transaction log end
                            #endregion
                        }


                        #region UPDATE PROPERTY IMAGES
                        long ImageCount = BLayer.Property.GetGDSPropertyImagesCount(id);
                        if (ImageCount < 1)
                        {
                            BLayer.Property.DeleteGDSPropertyImages(id);
                            List <CLayer.PropertyFiles> pictlist = GetGDSImages(hotel, id);
                        }


                        #endregion

                        //    WriteToLog(HotelID);
                    }
                    catch (Exception ex)
                    {
                        //  LogHandler.AddLog("Error in-"+hotelcode);
                        WriteToLog(HotelID, true);
                    }
                    finally
                    {
                    }
                }
                result = true;
            }
            catch (Exception ex)
            {
                LogHandler.AddLog(ex.Message);
                string a = id1.ToString();
                result = false;

                throw ex;
            }
            return(result);
        }
Пример #4
0
        public void GenerateReport()
        {
            //List<CLayer.PropertyDetailsReport> Reportlist;
            MySqlConnection _connection = null;

            try
            {
                string filename   = System.Configuration.ConfigurationManager.AppSettings.Get("Filename");
                string filePath   = System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\" + filename;
                string destFolder = System.Configuration.ConfigurationManager.AppSettings.Get("DestFilePath");


                if (System.IO.File.Exists(filePath))
                {
                    try {
                        File.Delete(filePath);
                    }
                    catch (Exception ex)
                    {
                        LogHandler.LogError(ex);
                    }
                }
                try
                {
                    //getting connection here iteself for using data reader

                    _connection = new MySqlConnection();
                    _connection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

                    MySqlCommand cmd = new MySqlCommand();
                    cmd.Connection  = _connection;
                    cmd.CommandText = "report_PropertyDetails_ExcelJob";
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add("pSearchString", MySqlDbType.VarChar).Value = "";
                    cmd.Parameters.Add("pSearchvalue", MySqlDbType.Int32).Value    = 0;

                    MySqlDataReader dr = null;
                    CLayer.PropertyDetailsReport data = new CLayer.PropertyDetailsReport();
                    //No columns in report
                    const int NO_OF_COLUMNS = 31;

                    //open excel realted items

                    string xlsxFilePath = filePath;

                    using (SpreadsheetDocument document = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
                    {
                        //WriteExcelFile(ds, document);
                        //  Create the Excel file contents.  This function is used when creating an Excel file either writing
                        //  to a file, or writing to a MemoryStream.
                        document.AddWorkbookPart();
                        document.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

                        //  My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010)
                        document.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));

                        //  If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
                        WorkbookStylesPart workbookStylesPart = document.WorkbookPart.AddNewPart <WorkbookStylesPart>("rIdStyles");
                        Stylesheet         stylesheet         = new Stylesheet();
                        workbookStylesPart.Stylesheet = stylesheet;



                        //  For each worksheet you want to create
                        //string workSheetID = "rId1" ;
                        //string worksheetName = "ReportData";

                        WorksheetPart newWorksheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>();
                        newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();

                        // create sheet data
                        newWorksheetPart.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData());

                        // save worksheet
                        //WriteDataTableToExcelWorksheet(dt, newWorksheetPart);
                        var worksheet = newWorksheetPart.Worksheet;
                        var sheetData = worksheet.GetFirstChild <SheetData>();



                        //  Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
                        //
                        //  We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
                        //  cells of data, we'll know if to write Text values or Numeric cell values.

                        bool[] IsNumericColumn = new bool[NO_OF_COLUMNS];

                        string[] excelColumnNames = new string[NO_OF_COLUMNS];
                        for (int n = 0; n < NO_OF_COLUMNS; n++)
                        {
                            excelColumnNames[n] = CreateExcelFile.GetExcelColumnName(n);
                        }

                        //
                        //  Create the Header row in our Excel Worksheet
                        //
                        uint rowIndex = 1;

                        var headerRow = new Row {
                            RowIndex = rowIndex
                        };                                                    // add a row at the top of spreadsheet
                        sheetData.Append(headerRow);
                        string[] columnName = new string[NO_OF_COLUMNS] {
                            "SupplierBusinessName",

                            "Login ID",
                            "Supplier EmailID",
                            "Supplier Contact Name",
                            "Supplier Address",
                            "Supplier Phone",

                            "Supplier Mobile",
                            "Supplier City",
                            "Supplier State",
                            "Property Name",
                            "Property Address",

                            "B2CMarkup Short Term",
                            "B2CMarkup Long Term",
                            "B2BMarkup Short Term",
                            "B2BMarkup Long Term",
                            "Property City",

                            "Property State",
                            "Joined Date",
                            "Accommodation Description",
                            "Accommodation Type",
                            "Quantity",

                            "Daily Rate",
                            "Weekly Rate",
                            "Monthly Rate",
                            "LongTerm Rate",
                            "Guest Rate",

                            "Bedrooms",
                            "Supplier Total Accommodations",
                            "Accommodation MaxPeople",
                            "Adults",
                            "Children"
                        };

                        for (int colInx = 0; colInx < NO_OF_COLUMNS; colInx++)
                        {
                            CreateExcelFile.AppendTextCell(excelColumnNames[colInx] + "1", columnName[colInx], headerRow);
                            IsNumericColumn[colInx] = false;
                        }

                        try
                        {
                            //
                            //  Now, step through each row of data from DataReader
                            //
                            int colInx = 0;
                            //do not restart rowIndex, its value is already changed

                            _connection.Open();
                            dr = cmd.ExecuteReader();     //open reader

                            while (dr.Read())
                            {
                                // create a new row, and append a set of this row's data to it.
                                ++rowIndex;
                                var newExcelRow = new Row {
                                    RowIndex = rowIndex
                                };                                                      // add a row at the top of spreadsheet
                                sheetData.Append(newExcelRow);

                                //numeric column - model
                                // CreateExcelFile.AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
                                //text column - model
                                //CreateExcelFile.AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
                                colInx = 0;
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["Supplier_Business_Name"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["Login_ID"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["Supplier_Email_ID"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["Supplier_Contact_Name"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["Supplier_Address"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["Supplier_Phone"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["SupplierMobile"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["Supplier_City"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["Supplier_State"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["Property_Name"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["Property_Address"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["B2CMarkupShortTerm"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["B2CMarkupLongTerm"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["B2BMarkupShortTerm"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["B2BMarkupLongTerm"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["Property_City"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["Property_State"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToDate(dr["Joined_Date"]).ToString("dd/MM/yyyy"), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["Accommodation_Description"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["AccommodationType"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["Quantity"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["DailyRate"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["WeeklyRate"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["MonthlyRate"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["LongTermRate"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["GuestRate"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["Bedrooms"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["SupplierTotalAccommodations"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["Accommodation_MaxPeople"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["Adults"]), newExcelRow);
                                CreateExcelFile.AppendTextCell(excelColumnNames[colInx++] + rowIndex.ToString(), DataConv.ToString(dr["Children"]), newExcelRow);
                            }    //while loop
                        }catch (Exception ex)
                        { LogHandler.LogError(ex); }

                        if (dr != null)
                        {
                            dr.Close();     //close reader
                        }
                        try
                        {
                            if (_connection.State == System.Data.ConnectionState.Open)
                            {
                                _connection.Close();
                            }
                        }
                        catch { }
                        newWorksheetPart.Worksheet.Save();

                        // create the worksheet to workbook relation
                        document.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());

                        document.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>().AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet()
                        {
                            Id      = document.WorkbookPart.GetIdOfPart(newWorksheetPart),
                            SheetId = 1,
                            Name    = "ReportData"
                        });



                        document.WorkbookPart.Workbook.Save();
                    }
                }
                catch (Exception ex)
                {
                    LogHandler.LogError(ex);
                }finally
                {
                    try
                    {
                        if (_connection.State == System.Data.ConnectionState.Open)
                        {
                            _connection.Close();
                        }
                    }
                    catch { }
                }
                File.Copy(filePath, destFolder + filename, true);
            }
            catch (Exception ex)
            {
                LogHandler.LogError(ex);
            }
        }