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);
}
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);
}
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);
}
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);
}
}
