public void FixInvalidPicturesAll()
{
string sql = "SELECT id,imageurl FROM gdspropertyimageurls WHERE (IsValid IS NULL) OR (IsValid AND TIMESTAMPDIFF(DAY,IFNULL(LastChecked,DATE_ADD(CURDATE(),INTERVAL -5 DAY)) , NOW()) > 3) LIMIT 100";
MySqlConnection _connection = new MySqlConnection();
const int IMAGE_FOUND = 1;
const int IMAGE_NOTFOUND = 2;
const int IMAGE_ERROR = 3;
const int IMAGE_ERROR_MAXCOUNT = 3;
try
{
StringBuilder validIds, deleteIds;
validIds = new StringBuilder();
deleteIds = new StringBuilder();
DataTable dt = null;
MySqlCommand _cmd = new MySqlCommand();
_connection = new MySqlConnection();
_connection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
_cmd.CommandText = sql;
_cmd.CommandType = CommandType.Text;
_cmd.Connection = _connection;
MySqlCommand _execmd = new MySqlCommand();
MySqlDataAdapter _dada = new MySqlDataAdapter(_cmd);
HttpWebRequest request;
string imageUrl;
long imageId = 0;
int imageExist;
bool vadded, dadded;
int imageDownloadError = 0;
int maxRepetition = 0;
while (maxRepetition < 200)
{
maxRepetition++;
validIds.Clear();
deleteIds.Clear();
try
{
dt = new DataTable();
_connection.Open();
_dada.Fill(dt);
}
finally
{
if (_connection.State == ConnectionState.Open)
{
_connection.Close();
}
}
if (dt == null | dt.Rows.Count == 0)
{
break;
}
vadded = false;
dadded = false;
foreach (DataRow dr in dt.Rows)
{
imageExist = IMAGE_NOTFOUND;
imageId = DataConv.ToLong(dr["id"]);
imageUrl = DataConv.ToString(dr["imageurl"]);
#if DEBUG
ServicePointManager.Expect100Continue = true;
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
#endif
request = (HttpWebRequest)HttpWebRequest.Create(imageUrl);
#if !DEBUG
ServicePointManager.Expect100Continue = true;
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
#endif
request.Timeout = 10000; //10 seconds
request.Method = "HEAD";
try {
using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
{
if (response.StatusCode == HttpStatusCode.OK)
{
imageExist = IMAGE_FOUND;
Console.WriteLine("Image Found - " + imageUrl);
}
}
}
catch (Exception ex)
{
if (ex.Message.Contains("404"))
{
imageExist = IMAGE_NOTFOUND;
Console.WriteLine("Image NOT Found - " + imageUrl);
}
else
{
imageExist = IMAGE_ERROR;
imageDownloadError++;
// LogHandler.HandleError(ex);
Console.WriteLine("ERROR - " + ex.Message + " -- " + imageUrl);
if (imageDownloadError >= IMAGE_ERROR_MAXCOUNT)
{
return;//do not run this function.... it may be due to broken internet connection
}
}
}
if (imageExist == IMAGE_FOUND)
{
if (vadded)
{
validIds.Append(",");
}
else
{
vadded = true;
}
validIds.Append(imageId);
}
else if (imageExist == IMAGE_NOTFOUND)
{
if (dadded)
{
deleteIds.Append(",");
}
else
{
dadded = true;
}
deleteIds.Append(imageId);
}
}
try
{
_execmd.Connection = _connection;
_connection.Open();
if (vadded)
{
_execmd.CommandText = "Update gdspropertyimageurls set IsValid=1,LastChecked=NOW() Where id in(" + validIds.ToString() + ")";
_execmd.ExecuteNonQuery();
}
if (dadded)
{
_execmd.CommandText = "Delete From gdspropertyimageurls Where id in(" + deleteIds.ToString() + ")";
_execmd.ExecuteNonQuery();
}
}
finally
{
if (_connection.State == ConnectionState.Open)
{
_connection.Close();
}
}
}
}catch (Exception ex)
{
LogHandler.HandleError(ex);
}
}
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);
}
}