protected string GetDatabaseSpecificConnectionString(Row row)
{
var builder = new SqlConnectionStringBuilder(_connectionString) {
InitialCatalog = row["database"].ToString()
};
return builder.ConnectionString;
}
/// <summary>
/// Copies the row values to command parameters.
/// </summary>
/// <param name="command">The command.</param>
/// <param name="row">The row.</param>
protected void CopyRowValuesToCommandParameters(IDbCommand command, Row row) {
foreach (var column in row.Columns) {
var value = row[column];
if (CanUseAsParameter(value))
AddParameter(command, column, value);
}
}
public List<Data.Row> FetchData(string connectionString, string sql)
{
List<Data.Row> result = new List<Data.Row>();
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = sql;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Row row = new Row();
for (int i = 0; i < reader.VisibleFieldCount; i++)
{
row[reader.GetName(i)] = GetValue(reader, i);
}
result.Add(row);
}
}
}
}
return result;
}
protected override void PrepareCommand(Row row, SqlCommand command) {
if (_isMaster) {
command.CommandText = string.Format("UPDATE {0} SET TflDeleted = @TflDeleted WHERE TflKey = @TflKey;", _name);
AddParameter(command, "@TflDeleted", true);
} else {
command.CommandText = string.Format("DELETE FROM {0} WHERE TflKey = @TflKey;", _name);
}
AddParameter(command, "@TflKey", row["TflKey"]);
}
protected override void PrepareCommand(Row row, SqlCommand command) {
var sets = new FieldSqlWriter(_process.CalculatedFields.WithOutput())
.Alias(_process.OutputConnection.L, _process.OutputConnection.R)
.SetParam()
.Write();
command.CommandText = string.Format("UPDATE {0} SET {1} WHERE TflKey = @TflKey;", _process.MasterEntity.OutputName(), sets);
foreach (var field in _process.CalculatedFields) {
AddParameter(command, field.Identifier, row[field.Alias]);
}
AddParameter(command, "TflKey", row["TflKey"]);
}
public IEnumerable<SqlCommand> Execute(Row row)
{
int addressId = Address.Load(row["locationIdentifier"].ToString()).AddressId;
var builder = new PropertyTypeCommandBuilder(propertyTypeValueProvider, addressId, row);
return builder.GetPropertyTypeCommandsFor<AddressPropertyType>(
(id, propertyTypeId, propertyValue) =>
{
Debug.WriteLine("creating command");
var command = new SqlCommand
{
CommandText =
"INSERT INTO AddressProperties(AddressID, AddressPropertyTypeID, PropertyValue) VALUES(@addressId, @addressPropertyTypeId, @propertyValue)"
};
command.Parameters.AddWithValue("addressId", id);
command.Parameters.AddWithValue("addressPropertyTypeId", propertyTypeId);
command.Parameters.AddWithValue("propertyValue", propertyValue);
return command;
});
}
public override void addDataRows(SheetData sheetData, DataTable dt)
{
// бежим по строкам
foreach (DataRow dataRow in dt.Rows)
{
var row = new Row();
for (int i = 0; i < dataRow.ItemArray.Length; i++)
{
var cell = new Cell();
var cellValue = new CellValue();
ApplyStyle(dataRow[i], RestMetadata.GetType(dt.Columns[i].ColumnName), ref cell, ref cellValue);
cell.Append(cellValue);
row.AppendChild(cell);
}
sheetData.AppendChild(row);
}
}
public void ExportDataTable(DataTable table, string exportFile)
{
//create the empty spreadsheet template and save the file //using the class generated by the Productivity tool
ExcelDocument excelDocument = new ExcelDocument();
excelDocument.CreatePackage(exportFile);
//string filename = "";
//File.Copy(filename, filename, true);
//populate the data into the spreadsheet
using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(exportFile, true))
{
WorkbookPart workbook = spreadsheet.WorkbookPart;
//create a reference to Sheet1
WorksheetPart worksheet = workbook.WorksheetParts.Last();
SheetData data = worksheet.Worksheet.GetFirstChild<SheetData>();
//add column names to the first row
Row header = new Row();
header.RowIndex = (UInt32)1;
foreach (DataColumn column in table.Columns)
{
Cell headerCell = createTextCell(
table.Columns.IndexOf(column) + 1,
1,
column.ColumnName);
header.AppendChild(headerCell);
}
data.AppendChild(header);
//loop through each data row
DataRow contentRow;
for (int i = 0; i < table.Rows.Count; i++)
{
contentRow = table.Rows[i];
data.AppendChild(createContentRow(contentRow, i + 2));
}
}
}
protected override void PrepareCommand(Row row, SqlCommand command) {
var fields = _entity.OutputFields();
var writer = new FieldSqlWriter(fields).AddDeleted(_entity);
var sets = writer.Alias(_connection.L, _connection.R).SetParam().Write(", ", false);
command.CommandText = string.Format(@"
UPDATE [{0}]
SET {1}, TflBatchId = @TflBatchId
WHERE TflKey = @TflKey;
", _entity.OutputName(), sets);
foreach (var field in fields) {
AddParameter(command, field.Identifier, row[field.Alias]);
}
if (_entity.Delete) {
AddParameter(command, "TflDeleted", false);
}
AddParameter(command, "TflKey", row["TflKey"]);
AddParameter(command, "TflBatchId", _entity.TflBatchId);
Logger.EntityDebug(_entity.Alias, command.CommandText);
}
DataRow GetDataRow(DataTable dataTable, Row row) {
var dr = dataTable.NewRow();
var values = new List<object>(row.ToEnumerable(_output.OutputFields)) { _output.Entity.BatchId };
dr.ItemArray = values.ToArray();
return dr;
}
/// <summary>
/// Prepares the command.
/// </summary>
/// <param name="row">The row.</param>
/// <param name="sqlCommand">The SQL command.</param>
protected override void PrepareCommand(Row row, SqlCommand sqlCommand)
{
sqlCommand.CommandText = Command;
CopyRowValuesToCommandParameters(sqlCommand, row);
}
private Row createContentRow(DataRow dataRow, int rowIndex)
{
Row row = new Row
{
RowIndex = (UInt32)rowIndex
};
for (int i = 0; i < dataRow.Table.Columns.Count; i++)
{
Cell dataCell = createTextCell(i + 1, rowIndex, dataRow[i]);
row.AppendChild(dataCell);
}
return row;
}
/// Execute database command
protected int ExecCommand(IDbCommand cmd)
{
string outTo = Context.TransformStr(OutTo, Transform);
RowSet rowset = (!string.IsNullOrEmpty(ToRowsetId))
? Context.Find<RowSet>(Context.TransformStr(ToRowsetId, Transform),true) : null;
if (rowset == null && !string.IsNullOrEmpty(outTo))
{
rowset = new RowSet();
Context.Initialize(rowset);
}
bool canSave = (rowset != null);
if (rowset != null)
{
rowset.Rows.Clear();
rowset.Columns = null;
rowset.Transform = TransformRules.None;
}
int nRows = 0;
switch (Mode)
{
case SqlMode.Query:
case SqlMode.DataTable:
using (var v = cmd.ExecuteReader())
{
if (v != null)
nRows = v.RecordsAffected;
if (v != null)
{
do
{
string[] colName = new string[v.FieldCount];
for (int i = 0; i < v.FieldCount; ++i)
{
colName[i] = v.GetName(i) ?? string.Empty;
if (colName[i].Length == 0)
colName[i] = "Column" + i;
}
while (v.Read())
{
Row r = new Row();
for (int i = 0; i < v.FieldCount; ++i)
{
object o = v.GetValue(i);
if (o == DBNull.Value)
o = null;
r[colName[i]] = o;
}
if (rowset!=null)
rowset.Rows.Add(r);
}
// Print as table
if (outTo != null)
{
if (Mode == SqlMode.DataTable)
{
if (canSave)
Context.OutTo(outTo, rowset.ToDataTable("resultset"));
}
else if (rowset != null)
Context.OutTo(outTo,rowset.ToTextTable(TableFormat));
}
// Prepare for the next rowset
rowset = null;
if (!string.IsNullOrEmpty(outTo))
rowset = new RowSet();
canSave = false;
} while (v.NextResult());
}
}
break;
case SqlMode.Xml:
XmlDocument xdoc = new XmlDocument();
if (!(cmd is SqlCommand))
throw new NotSupportedException("This database type does not support XML");
using (var reader=((SqlCommand)cmd).ExecuteXmlReader())
{
xdoc.Load(reader);
}
if (!string.IsNullOrEmpty(outTo))
{
using (MemoryStream ms = new MemoryStream())
{
XmlTextWriter tw = new XmlTextWriter(ms, System.Text.Encoding.UTF8);
tw.Formatting = Formatting.Indented;
xdoc.Save(tw);
tw.Flush();
ms.Position = 0;
Context.OutTo(outTo, new StreamReader(ms).ReadToEnd());
}
}
nRows = -1;
break;
case SqlMode.NonQuery:
nRows = cmd.ExecuteNonQuery();
break;
case SqlMode.Scalar:
object res = cmd.ExecuteScalar();
Context.OutTo(outTo, res);
break;
default:
throw new ParsingException("Unknown SQL statement type");
}
return nRows;
}
private void BuildReportTemplateEFPFlexi(string strTargetFile)
{
string fileName = strTargetFile;
const string alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
var tableTargets = new List<TableInfo>();
using (SpreadsheetDocument workbook = SpreadsheetDocument.Open(fileName, true))
{
var workbookPart = workbook.WorkbookPart;
var sharedStringPart = workbookPart.SharedStringTablePart;
var values = sharedStringPart.SharedStringTable.Elements<SharedStringItem>().ToArray();
foreach (WorksheetPart worksheetpart in workbookPart.WorksheetParts)
{
foreach (var sheetData in worksheetpart.Worksheet.Elements<SheetData>())
{
// reset accross sheets
//Cell FinaleCell = null;
//TableDefinition FinaleTable = null;
tableTargets.Clear();
#region TextPopulate
foreach (var cell in sheetData.Descendants<Cell>())
{
if (cell.CellValue != null)
{
if (cell.CellFormula != null)
{
// force recompute
cell.CellValue.Remove();
}
else if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
var index = int.Parse(cell.CellValue.Text);
if (values[index].InnerText.StartsWith(FLEXI_PREFIX))
{
string strBlockTypeAndName = values[index].InnerText.Substring(FLEXI_PREFIX.Length);
BlockConfiguration config = GetBlockConfiguration(strBlockTypeAndName);
if (TextBlock.IsMatching(config.Type))
{
TextBlock instance = BlockHelper.GetAssociatedBlockInstance<TextBlock>(config.Name);
if (instance != null)
{
SetCellValue(cell, instance.GetContent(reportData, config.Options));
}
}
else if (TableBlock.IsMatching(config.Type))
{
TableBlock instance = BlockHelper.GetAssociatedBlockInstance<TableBlock>(config.Name);
if (instance != null)
{
tableTargets.Add(new TableInfo
{
cell = cell,
table = instance.GetContent(reportData, config.Options)
});
//FinaleCell = cell;
//FinaleTable = instance.GetContent(reportData, config.Options);
}
}
}
}
}
}
#endregion TextPopulate
#region TablePopulate
foreach (var tableInfo in tableTargets)
{
var FinaleCell = tableInfo.cell;
var FinaleTable = tableInfo.table;
int intColumns = FinaleTable.NbColumns;
int intRows = FinaleTable.NbRows;
// TODO: handle cell references after 'Znn' (AA1, AB1...)
// TODO: current limitation: the generated cells must be in the range "A-Z"
char firstLetter = FinaleCell.CellReference.InnerText[0];
int firstColIdx = alphabet.IndexOf(firstLetter) + 1;
int lastColIdx = firstColIdx + intColumns - 1;
int curColIdx = firstColIdx;
uint firstRowIdx = uint.Parse(FinaleCell.CellReference.InnerText.Substring(1));
uint curRowIdx = firstRowIdx;
// create first row
Row curRow = new Row();
foreach (var result in FinaleTable.Data)
{
// append cell to current row
Cell c = new Cell();
SetCellValue(c, result.ToString());
c.CellReference = alphabet[curColIdx - 1] + curRowIdx.ToString();
c.StyleIndex = 0;
curRow.Append(c);
if (curColIdx == lastColIdx)
{
// add row to current worksheet
InsertRow(curRowIdx, worksheetpart, curRow, false);
// create new row for next data
curRow = new Row();
// first cell on next row
curRowIdx++;
curColIdx = firstColIdx;
}
else
{
// next cell
curColIdx++;
}
}
FinaleCell.Parent.RemoveChild(FinaleCell);
}
workbookPart.Workbook.Save();
#endregion TablePopulate
}
}
}
}
// Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet.
// If the cell already exists, returns it.
private static Cell InsertCellInWorksheet(WorksheetPart worksheetPart, string columnName, uint rowIndex)
{
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
string cellReference = columnName + rowIndex;
// If the worksheet does not contain a row with the specified row index, insert one.
Row row;
if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
}
else
{
row = new Row() { RowIndex = rowIndex };
sheetData.Append(row);
}
// If there is not a cell with the specified column name, insert one.
if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
}
else
{
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
Cell refCell = null;
foreach (Cell cell in row.Elements<Cell>())
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
Cell newCell = new Cell() { CellReference = cellReference };
row.InsertBefore(newCell, refCell);
worksheet.Save();
return newCell;
}
}
public static void ExportToOxml(bool firstTime, string filePath, DataTable resultsData)
{
//Delete the file if it exists.
if (firstTime && File.Exists(filePath))
{
File.Delete(filePath);
}
uint sheetId = 1; //Start at the first sheet in the Excel workbook.
if (firstTime)
{
//This is the first time of creating the excel file and the first sheet.
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
Create(filePath, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var bold1 = new Bold();
CellFormat cf = new CellFormat();
// Add Sheets to the Workbook.
Sheets sheets;
sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
var sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = sheetId,
Name = "Sheet" + sheetId
};
sheets.Append(sheet);
//Add Header Row.
var headerRow = new Row();
foreach (DataColumn column in resultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(column.ColumnName)
};
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow row in resultsData.Rows)
{
var newRow = new Row();
foreach (DataColumn col in resultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(row[col].ToString())
};
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
}
else
{
// Open the Excel file that we created before, and start to add sheets to it.
var spreadsheetDocument = SpreadsheetDocument.Open(filePath, true);
var workbookpart = spreadsheetDocument.WorkbookPart;
if (workbookpart.Workbook == null)
workbookpart.Workbook = new Workbook();
var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var sheets = spreadsheetDocument.WorkbookPart.Workbook.Sheets;
if (sheets.Elements<Sheet>().Any())
{
//Set the new sheet id
sheetId = sheets.Elements<Sheet>().Max(s => s.SheetId.Value) + 1;
}
else
{
sheetId = 1;
}
// Append a new worksheet and associate it with the workbook.
var sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = sheetId,
Name = "Sheet" + sheetId
};
sheets.Append(sheet);
//Add the header row here.
var headerRow = new Row();
foreach (DataColumn column in resultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(column.ColumnName)
};
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow row in resultsData.Rows)
{
var newRow = new Row();
foreach (DataColumn col in resultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(row[col].ToString())
};
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
}
public static Row InsertRow(uint rowIndex, WorksheetPart worksheetPart, Row insertRow, bool isNewLastRow = false)
{
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
Row retRow = !isNewLastRow ? sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex == rowIndex) : null;
// If the worksheet does not contain a row with the specified row index, insert one.
if (retRow != null)
{
// if retRow is not null and we are inserting a new row, then move all existing rows down.
if (insertRow != null)
{
UpdateRowIndexes(worksheetPart, rowIndex, false);
UpdateMergedCellReferences(worksheetPart, rowIndex, false);
UpdateHyperlinkReferences(worksheetPart, rowIndex, false);
// actually insert the new row into the sheet
retRow = sheetData.InsertBefore(insertRow, retRow); // at this point, retRow still points to the row that had the insert rowIndex
//string curIndex = retRow.RowIndex.ToString();
string curIndex = rowIndex.ToString();
string newIndex = rowIndex.ToString();
foreach (Cell cell in retRow.Elements<Cell>())
{
// Update the references for the rows cells.
cell.CellReference = new StringValue(cell.CellReference.Value.Replace(curIndex, newIndex));
}
// Update the row index.
retRow.RowIndex = rowIndex;
}
}
else
{
// Row doesn't exist yet, shifting not needed.
// Rows must be in sequential order according to RowIndex. Determine where to insert the new row.
Row refRow = !isNewLastRow ? sheetData.Elements<Row>().FirstOrDefault(row => row.RowIndex > rowIndex) : null;
// use the insert row if it exists
retRow = insertRow ?? new Row() { RowIndex = rowIndex };
IEnumerable<Cell> cellsInRow = retRow.Elements<Cell>();
if (cellsInRow.Any())
{
string curIndex = retRow.RowIndex.ToString();
string newIndex = rowIndex.ToString();
foreach (Cell cell in cellsInRow)
{
// Update the references for the rows cells.
cell.CellReference = new StringValue(cell.CellReference.Value.Replace(curIndex, newIndex));
}
// Update the row index.
retRow.RowIndex = rowIndex;
}
sheetData.InsertBefore(retRow, refRow);
}
return retRow;
}
public override bool Equals(Row other)
{
var or = other as RowA;
if (or==null) return false;
foreach(var f in this.Schema)
{
var v1 = this.GetFieldValue(f);
var v2 = or.GetFieldValue(f);
if (v1==null)
{
if (v2==null) continue;
else return false;
}
else if (v2 == null)
return false;
if (v1 is byte[])
{
return ((byte[])v1).SequenceEqual((byte[])v2);
}
if (!v1.Equals( v2 )) return false;
}
return true;
}
/// <summary>
/// Attaches miscellaneous attribute data to the features that have been loaded.
/// </summary>
/// <param name="keyIds">Index of the IDs to look for (indexed by formatted key)</param>
/// <returns>The number of rows that were found (-1 if no database tables have
/// been associated with Backsight)</returns>
static int Load(Dictionary<string, FeatureId> keyIds)
{
// Locate information about the tables associated with Backsight
ITable[] tables = EnvironmentContainer.Current.Tables;
if (tables.Length == 0)
return -1;
IDataServer ds = EditingController.Current.DataServer;
if (ds == null)
return -1;
// Copy the required keys into a temp table
Trace.WriteLine(String.Format("Locating attributes for {0} feature IDs in {1} tables", keyIds.Count, tables.Length));
int nFound = 0;
ds.RunTransaction(delegate
{
IConnection ic = ds.GetConnection();
const string KEYS_TABLE_NAME = "#Ids";
CopyKeysToTable(ic, keyIds, KEYS_TABLE_NAME);
foreach (ITable t in tables)
{
string sql = String.Format("SELECT * FROM {0} WHERE [{1}] IN (SELECT [FeatureId] FROM [{2}])",
t.TableName, t.IdColumnName, KEYS_TABLE_NAME);
DataTable tab = ds.ExecuteSelect(sql);
tab.TableName = t.TableName;
int featureIdIndex = tab.Columns[t.IdColumnName].Ordinal;
Debug.Assert(featureIdIndex>=0);
foreach (DataRow row in tab.Select())
{
string key = row[featureIdIndex].ToString().TrimEnd();
FeatureId fid;
if (keyIds.TryGetValue(key, out fid))
{
// Don't create a row if the ID is already associated with the
// table (this is meant to cover situations where the edit has actively
// formed the attributes, and is calling this method only to cover the
// fact that further attributes may be involved).
if (!fid.RefersToTable(t))
{
Row r = new Row(fid, t, row);
nFound++;
}
}
else
{
string msg = String.Format("Cannot find '{0}' in dictionary", key);
throw new Exception(msg);
}
}
}
});
return nFound;
}
/// <summary>
/// Prepares the command from the given row
/// </summary>
/// <param name="row">The row.</param>
/// <param name="command">The command.</param>
protected override void PrepareCommand(Row row, SqlCommand command)
{
command.CommandText = "UPDATE Users SET Name = @Name, TestMsg = 'UpperCased' WHERE Id = @Id";
command.Parameters.AddWithValue("@Name", row["Name"]);
command.Parameters.AddWithValue("@Id", row["Id"]);
}
/// <summary>
/// Prepares the command from the given row
/// </summary>
/// <param name="row">The row.</param>
/// <param name="command">The command.</param>
protected override void PrepareCommand(Row row, SqlCommand command)
{
command.CommandText = "INSERT INTO Fibonacci (id) VALUES(@id)";
command.Parameters.AddWithValue("@id", row["id"]);
}
private IEnumerable<Row> ToRows(IEnumerable<SQLServerSchemaBase> dbObjects, string path, string sqlType, bool useParent = false, bool addVersion = false)
{
var rows = new List<Row>();
foreach (var dbObject in dbObjects) {
var row = new Row();
row["sqlscript"] = addVersion ? dbObject.ToSqlAdd() : dbObject.ToSql();
row["database"] = _connectionStringBuilder.InitialCatalog;
row["schema"] = dbObject.Owner;
row["name"] = dbObject.Name;
row["objectid"] = useParent ? dbObject.Parent.Id : dbObject.Id;
row["indexid"] = useParent ? dbObject.Id : 0;
row["path"] = path;
row["type"] = sqlType;
row["created"] = dbObject.CreateDate;
row["modified"] = dbObject.ModifyDate;
row["use"] = (long)0;
row["lastused"] = dbObject.ModifyDate;
rows.Add(row);
}
return rows;
}
public bool ImportExcel(string strNotificationFrequencyId, string strNotificationFrequency, string NotificationTime, string BillingCurrencyId, string strBillingCurrency, string ContractTypeId, string ContractType, string DepartmentId, string Department)
{
if (System.IO.File.Exists(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "ClientTemplate.xlsx")))
{
System.IO.File.Delete(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "ClientTemplate.xlsx"));
}
string[] ListOfExportFieldName = new string[] { "NotificationFrequencyId", "Notification Frequency", "Notification Time", "BillingCurrencyId"
, "Billing Currency","ContractTypeId","Contract Type","ClientContactDepartmentId","Client Contact Department","Client Name","1st Line of the Address",
"2nd Line of the Address","City","County","Postcode","Company Number", "VAT Number","Client Bank","Sort Code","Client Bank A/c No","End Date",
"Client Contact Start Date","Client Contact First Name","Client Contact Last Name","Client Contact 1st Line of the Address","Client Contact 2nd Line of the Address",
"Client Contact City","Client Contact County","Client Contact Postcode","Client Contact Telephone Number","Client Contact Mobile Number","Client Contact Email Address"};
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "ClientTemplate.xlsx"), SpreadsheetDocumentType.Workbook))
{
SheetData sheetData = new SheetData();
Row row = new Row();
Row titleRow = new Row { RowIndex = (UInt32)1 };
//-------------------------Field Type----------------------------------
titleRow.AppendChild(CreateTextCell("A", ListOfExportFieldName[0], 1));
titleRow.AppendChild(CreateTextCell("B", ListOfExportFieldName[1], 1));
titleRow.AppendChild(CreateTextCell("C", ListOfExportFieldName[2], 1));
titleRow.AppendChild(CreateTextCell("D", ListOfExportFieldName[3], 1));
titleRow.AppendChild(CreateTextCell("E", ListOfExportFieldName[4], 1));
titleRow.AppendChild(CreateTextCell("F", ListOfExportFieldName[5], 1));
titleRow.AppendChild(CreateTextCell("G", ListOfExportFieldName[6], 1));
titleRow.AppendChild(CreateTextCell("H", ListOfExportFieldName[7], 1));
titleRow.AppendChild(CreateTextCell("I", ListOfExportFieldName[8], 1));
titleRow.AppendChild(CreateTextCell("J", ListOfExportFieldName[9], 1));
titleRow.AppendChild(CreateTextCell("K", ListOfExportFieldName[10], 1));
titleRow.AppendChild(CreateTextCell("L", ListOfExportFieldName[11], 1));
titleRow.AppendChild(CreateTextCell("M", ListOfExportFieldName[12], 1));
titleRow.AppendChild(CreateTextCell("N", ListOfExportFieldName[13], 1));
titleRow.AppendChild(CreateTextCell("O", ListOfExportFieldName[14], 1));
titleRow.AppendChild(CreateTextCell("P", ListOfExportFieldName[15], 1));
titleRow.AppendChild(CreateTextCell("Q", ListOfExportFieldName[16], 1));
titleRow.AppendChild(CreateTextCell("R", ListOfExportFieldName[17], 1));
titleRow.AppendChild(CreateTextCell("S", ListOfExportFieldName[18], 1));
titleRow.AppendChild(CreateTextCell("T", ListOfExportFieldName[19], 1));
titleRow.AppendChild(CreateTextCell("U", ListOfExportFieldName[20], 1));
titleRow.AppendChild(CreateTextCell("V", ListOfExportFieldName[21], 1));
titleRow.AppendChild(CreateTextCell("W", ListOfExportFieldName[22], 1));
titleRow.AppendChild(CreateTextCell("X", ListOfExportFieldName[23], 1));
titleRow.AppendChild(CreateTextCell("Y", ListOfExportFieldName[24], 1));
titleRow.AppendChild(CreateTextCell("Z", ListOfExportFieldName[25], 1));
titleRow.AppendChild(CreateTextCell("AA",ListOfExportFieldName[26], 1));
titleRow.AppendChild(CreateTextCell("AB",ListOfExportFieldName[27], 1));
titleRow.AppendChild(CreateTextCell("AC",ListOfExportFieldName[28], 1));
titleRow.AppendChild(CreateTextCell("AD",ListOfExportFieldName[29], 1));
titleRow.AppendChild(CreateTextCell("AE",ListOfExportFieldName[30], 1));
titleRow.AppendChild(CreateTextCell("AF",ListOfExportFieldName[31], 1));
// Append Row to SheetData
sheetData.AppendChild(titleRow);
DataTable dt = GetRecordFromSiteMaster(strNotificationFrequencyId, strNotificationFrequency, NotificationTime, BillingCurrencyId, strBillingCurrency, ContractTypeId, ContractType, DepartmentId, Department);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Row Subrow = new Row();
Subrow.AppendChild(CreateTextCell("A", Convert.ToString(dt.Rows[i]["NotificationFrequencyId"]), i + 2));
Subrow.AppendChild(CreateTextCell("B", Convert.ToString(dt.Rows[i]["Notification Frequency"]), i + 2));
Subrow.AppendChild(CreateTextCell("C", Convert.ToString(dt.Rows[i]["Notification Time"]), i + 2));
Subrow.AppendChild(CreateTextCell("D", Convert.ToString(dt.Rows[i]["BillingCurrencyId"]), i + 2));
Subrow.AppendChild(CreateTextCell("E", Convert.ToString(dt.Rows[i]["Billing Currency"]), i + 2));
Subrow.AppendChild(CreateTextCell("F", Convert.ToString(dt.Rows[i]["ContractTypeId"]), i + 2));
Subrow.AppendChild(CreateTextCell("G", Convert.ToString(dt.Rows[i]["Contract Type"]), i + 2));
Subrow.AppendChild(CreateTextCell("H", Convert.ToString(dt.Rows[i]["ClientContactDepartmentId"]), i + 2));
Subrow.AppendChild(CreateTextCell("I", Convert.ToString(dt.Rows[i]["Client Contact Department"]), i + 2));
// Append Row to SheetData
sheetData.AppendChild(Subrow);
}
}
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
Worksheet sheet1 = new Worksheet();
//-------------------Column Size---------------------
Columns columns = new Columns();
uint ival = 1;
foreach (string value in ListOfExportFieldName)
{
columns.Append(CreateColumnSize(1, ival, 28));
ival++;
}
sheet1.Append(columns);
//-------------------Column Size---------------------
sheet1.Append(sheetData);
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = sheet1;
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "ClientTemplate"
};
sheets.Append(sheet);
// Close the document.
spreadsheetDocument.Close();
}
return true;
}
/// <summary> /// Prepares the command for execution, set command text, parameters, etc /// </summary> /// <param name="cmd">The command.</param> /// <param name="row">The row.</param> protected abstract void PrepareCommand(IDbCommand cmd, Row row);
public bool ImportExcel(string strPropbandid)
{
if (System.IO.File.Exists(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "OwnerTemplate.xlsx")))
{
System.IO.File.Delete(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "OwnerTemplate.xlsx"));
}
string[] ListOfExportFieldName = new string[] { "ClientId", "Client", "NetworkId", "Network"
, "PropertyId","Property Address","First Name","Last Name","Start Date","Bank Name","Bank Sort Code","Correspondence Address",
"City","County","Post Code","E-mail Address","Mail Service","Telephone Number","Mobile Number","Managing Agent",
"Managing Telephone Number","Managing Email","Notes","Client Reference Number"};
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "OwnerTemplate.xlsx"), SpreadsheetDocumentType.Workbook))
{
SheetData sheetData = new SheetData();
Row row = new Row();
Row titleRow = new Row { RowIndex = (UInt32)1 };
//-------------------------Field Type----------------------------------
titleRow.AppendChild(CreateTextCell("A", ListOfExportFieldName[0], 1));
titleRow.AppendChild(CreateTextCell("B", ListOfExportFieldName[1], 1));
titleRow.AppendChild(CreateTextCell("C", ListOfExportFieldName[2], 1));
titleRow.AppendChild(CreateTextCell("D", ListOfExportFieldName[3], 1));
titleRow.AppendChild(CreateTextCell("E", ListOfExportFieldName[4], 1));
titleRow.AppendChild(CreateTextCell("F", ListOfExportFieldName[5], 1));
titleRow.AppendChild(CreateTextCell("G", ListOfExportFieldName[6], 1));
titleRow.AppendChild(CreateTextCell("H", ListOfExportFieldName[7], 1));
titleRow.AppendChild(CreateTextCell("I", ListOfExportFieldName[8], 1));
titleRow.AppendChild(CreateTextCell("J", ListOfExportFieldName[9], 1));
titleRow.AppendChild(CreateTextCell("K", ListOfExportFieldName[10], 1));
titleRow.AppendChild(CreateTextCell("L", ListOfExportFieldName[11], 1));
titleRow.AppendChild(CreateTextCell("M", ListOfExportFieldName[12], 1));
titleRow.AppendChild(CreateTextCell("N", ListOfExportFieldName[13], 1));
titleRow.AppendChild(CreateTextCell("O", ListOfExportFieldName[14], 1));
titleRow.AppendChild(CreateTextCell("P", ListOfExportFieldName[15], 1));
titleRow.AppendChild(CreateTextCell("Q", ListOfExportFieldName[16], 1));
titleRow.AppendChild(CreateTextCell("R", ListOfExportFieldName[17], 1));
titleRow.AppendChild(CreateTextCell("S", ListOfExportFieldName[18], 1));
titleRow.AppendChild(CreateTextCell("T", ListOfExportFieldName[19], 1));
titleRow.AppendChild(CreateTextCell("U", ListOfExportFieldName[20], 1));
titleRow.AppendChild(CreateTextCell("V", ListOfExportFieldName[21], 1));
titleRow.AppendChild(CreateTextCell("W", ListOfExportFieldName[22], 1));
titleRow.AppendChild(CreateTextCell("X", ListOfExportFieldName[23], 1));
// Append Row to SheetData
sheetData.AppendChild(titleRow);
DataTable dt = GetRecordFromSiteMaster(strPropbandid);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Row Subrow = new Row();
Subrow.AppendChild(CreateTextCell("A", Convert.ToString(dt.Rows[i]["ClientId"]), i + 2));
Subrow.AppendChild(CreateTextCell("B", Convert.ToString(dt.Rows[i]["Client"]), i + 2));
Subrow.AppendChild(CreateTextCell("C", Convert.ToString(dt.Rows[i]["NetworkId"]), i + 2));
Subrow.AppendChild(CreateTextCell("D", Convert.ToString(dt.Rows[i]["Network"]), i + 2));
Subrow.AppendChild(CreateTextCell("E", Convert.ToString(dt.Rows[i]["PropertyId"]), i + 2));
Subrow.AppendChild(CreateTextCell("F", Convert.ToString(dt.Rows[i]["Property Address"]), i + 2));
// Append Row to SheetData
sheetData.AppendChild(Subrow);
}
}
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
Worksheet sheet1 = new Worksheet();
//-------------------Column Size---------------------
Columns columns = new Columns();
uint ival = 1;
foreach (string value in ListOfExportFieldName)
{
columns.Append(CreateColumnSize(1, ival, 28));
ival++;
}
sheet1.Append(columns);
//-------------------Column Size---------------------
sheet1.Append(sheetData);
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = sheet1;
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "PropertyTemplate"
};
sheets.Append(sheet);
// Close the document.
spreadsheetDocument.Close();
}
return true;
}
// callback function for the tdsparser
// this will return an array of rows to store the rowdata
//
internal object[] CreateRowBuffer() {
Row row = new Row(resultSet.MetaData.Length);
resultSet.AddRow(row);
return row.DataFields;
}
internal void AddRow(Row row)
{
this._rowset.Add(row);
}
public bool ImportExcel(string strSiteid, string strSupplierID, string strSupplyType, string strNotificationId, string strNotification, string CategoryID, string Category, string strReadingInterval)
{
if (System.IO.File.Exists(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "MeterTemplate.xlsx")))
{
System.IO.File.Delete(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "MeterTemplate.xlsx"));
}
string[] ListOfExportFieldName = new string[] { "ClientId", "Client", "NetworkId", "Network"
, "PropertyId","Property Address","SupplyTypeId","Supply Type","ReadFrequencyId","Read Frequency","MeterCategoryId","Meter Category","Reading Interval",
"Start Date","End Date","Meter Serial","Device ID","Meter Start Date","Meter End Date","Opening Read","Offset Value",
"Warrenty Date"};
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "MeterTemplate.xlsx"), SpreadsheetDocumentType.Workbook))
{
SheetData sheetData = new SheetData();
Row row = new Row();
Row titleRow = new Row { RowIndex = (UInt32)1 };
//-------------------------Field Type----------------------------------
titleRow.AppendChild(CreateTextCell("A", ListOfExportFieldName[0], 1));
titleRow.AppendChild(CreateTextCell("B", ListOfExportFieldName[1], 1));
titleRow.AppendChild(CreateTextCell("C", ListOfExportFieldName[2], 1));
titleRow.AppendChild(CreateTextCell("D", ListOfExportFieldName[3], 1));
titleRow.AppendChild(CreateTextCell("E", ListOfExportFieldName[4], 1));
titleRow.AppendChild(CreateTextCell("F", ListOfExportFieldName[5], 1));
titleRow.AppendChild(CreateTextCell("G", ListOfExportFieldName[6], 1));
titleRow.AppendChild(CreateTextCell("H", ListOfExportFieldName[7], 1));
titleRow.AppendChild(CreateTextCell("I", ListOfExportFieldName[8], 1));
titleRow.AppendChild(CreateTextCell("J", ListOfExportFieldName[9], 1));
titleRow.AppendChild(CreateTextCell("K", ListOfExportFieldName[10], 1));
titleRow.AppendChild(CreateTextCell("L", ListOfExportFieldName[11], 1));
titleRow.AppendChild(CreateTextCell("M", ListOfExportFieldName[12], 1));
titleRow.AppendChild(CreateTextCell("N", ListOfExportFieldName[13], 1));
titleRow.AppendChild(CreateTextCell("O", ListOfExportFieldName[14], 1));
titleRow.AppendChild(CreateTextCell("P", ListOfExportFieldName[15], 1));
titleRow.AppendChild(CreateTextCell("Q", ListOfExportFieldName[16], 1));
titleRow.AppendChild(CreateTextCell("R", ListOfExportFieldName[17], 1));
titleRow.AppendChild(CreateTextCell("S", ListOfExportFieldName[18], 1));
titleRow.AppendChild(CreateTextCell("T", ListOfExportFieldName[19], 1));
titleRow.AppendChild(CreateTextCell("U", ListOfExportFieldName[20], 1));
titleRow.AppendChild(CreateTextCell("V", ListOfExportFieldName[21], 1));
// Append Row to SheetData
sheetData.AppendChild(titleRow);
DataTable dt = GetRecordFromSiteMaster(strSiteid, strSupplierID, strSupplyType, strNotificationId, strNotification, CategoryID, Category, strReadingInterval);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Row Subrow = new Row();
Subrow.AppendChild(CreateTextCell("A", Convert.ToString(dt.Rows[i]["ClientId"]), i + 2));
Subrow.AppendChild(CreateTextCell("B", Convert.ToString(dt.Rows[i]["Client"]), i + 2));
Subrow.AppendChild(CreateTextCell("C", Convert.ToString(dt.Rows[i]["NetworkId"]), i + 2));
Subrow.AppendChild(CreateTextCell("D", Convert.ToString(dt.Rows[i]["Network"]), i + 2));
Subrow.AppendChild(CreateTextCell("E", Convert.ToString(dt.Rows[i]["PropertyId"]), i + 2));
Subrow.AppendChild(CreateTextCell("F", Convert.ToString(dt.Rows[i]["Property Address"]), i + 2));
Subrow.AppendChild(CreateTextCell("G", Convert.ToString(dt.Rows[i]["SupplyTypeId"]), i + 2));
Subrow.AppendChild(CreateTextCell("H", Convert.ToString(dt.Rows[i]["Supply Type"]), i + 2));
Subrow.AppendChild(CreateTextCell("I", Convert.ToString(dt.Rows[i]["ReadFrequencyId"]), i + 2));
Subrow.AppendChild(CreateTextCell("J", Convert.ToString(dt.Rows[i]["Read Frequency"]), i + 2));
Subrow.AppendChild(CreateTextCell("K", Convert.ToString(dt.Rows[i]["MeterCategoryId"]), i + 2));
Subrow.AppendChild(CreateTextCell("L", Convert.ToString(dt.Rows[i]["Meter Category"]), i + 2));
Subrow.AppendChild(CreateTextCell("M", Convert.ToString(dt.Rows[i]["Reading Interval"]), i + 2));
// Append Row to SheetData
sheetData.AppendChild(Subrow);
}
}
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
Worksheet sheet1 = new Worksheet();
//-------------------Column Size---------------------
Columns columns = new Columns();
uint ival = 1;
foreach (string value in ListOfExportFieldName)
{
columns.Append(CreateColumnSize(1, ival, 28));
ival++;
}
sheet1.Append(columns);
//-------------------Column Size---------------------
sheet1.Append(sheetData);
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = sheet1;
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "MeterTemplate"
};
sheets.Append(sheet);
// Close the document.
spreadsheetDocument.Close();
}
return true;
}
/// <summary> /// Prepares the command from the given row /// </summary> /// <param name="row">The row.</param> /// <param name="command">The command.</param> protected abstract void PrepareCommand(Row row, SqlCommand command);
/// <summary>
/// Retrieves all reservations in the database and packages
/// </summary>
/// <param name="beginRange">Ending dates before this time will be skipped</param>
/// <returns>Beginning dates after this time will be skipped</returns>
public static List<Row> getReservations(DateTime beginRange, DateTime endRange)
{
SqlConnection conn = new SqlConnection(getConnectionString());
try
{
conn.Open(); //Open the connection
string command = "SELECT g.GuestFirstName, g.GuestSurName, rd.CheckinDate, res.ReservationStatus, rd.Nights, " +
"room.RoomID, room.RoomNumbers, res.ReservationID, " +
"rtype.HotelRoomTypeID, rtype.RoomType, rd.ReservationDetailID " +
"FROM Guest g " +
"INNER JOIN Reservation res ON res.GuestID = g.GuestID " +
"INNER JOIN ReservationDetail rd ON res.ReservationID = rd.ReservationID " +
"INNER JOIN Room room ON rd.RoomID = room.RoomID " +
"INNER JOIN HotelRoomType rtype ON rtype.HotelRoomTypeID = room.HotelRoomTypeID " +
"ORDER BY room.RoomNumbers";
SqlCommand connCommand = new SqlCommand(command, conn);
SqlDataReader rt = connCommand.ExecuteReader();
List<Row> RT = new List<Row>();
while (rt.Read())
{
string dateTemp = rt[2].ToString();
//string debug = dateTemp.Substring(0, dateTemp.IndexOf(' '));
DateTime begin = DateTime.Parse(dateTemp.Substring(0,dateTemp.IndexOf(' ')));
DateTime end = begin.AddDays(Double.Parse(rt[4].ToString()));
//if ((end - beginRange).Days < 0 || (begin - endRange).Days > 0)
//continue; //Skip the rest and go to the next record
char resStatus = Char.Parse(rt[3].ToString());
short id = Int16.Parse(rt[5].ToString());
string roomNumber = rt[6].ToString();
int resID = Int32.Parse(rt[7].ToString());
int hotelRoomTypeID = Int32.Parse(rt[8].ToString());
string roomType = rt[9].ToString();
int reservationDetailsID = Int32.Parse(rt[10].ToString());
string guestName = rt[0].ToString() + ' ' + rt[1].ToString();
//bool roomStatus = Char.Parse(rt[9].ToString()) == 'M' ? true : false;
Row temp = new Row(id, resID, hotelRoomTypeID, reservationDetailsID); //Create and fill a Row object
temp.Begin = begin;
temp.End = end;
temp.ReservationType = resStatus;
temp.RoomNumber = roomNumber;
temp.RoomType = roomType;
temp.GuestName = guestName;
//temp.Maintenance = roomStatus;
RT.Add(temp); //Add it to the list
}
return RT;
}
catch (Exception e)
{
}
return null;
}