internal static void UpdateHyperLink(ExcelWorksheet sheet,
int rownum,
ColumnString cell,
Uri hyperLink,
string stylenameHyperlink,
string packageDirectory = null)
{
if (hyperLink == null)
{
Common.UpdateCellString(sheet, rownum, cell);
}
else
{
ExcelRange cellRange = sheet.Cells[rownum, cell.ColumnNumber];
cellRange.Hyperlink = hyperLink;
cellRange.StyleName = stylenameHyperlink;
cellRange.Value = cell.Value;
//check file location
string path = Path.GetFullPath(Path.Combine(packageDirectory, cellRange.Hyperlink.OriginalString)).Replace("%20", " ");
if (!string.IsNullOrEmpty(packageDirectory) &&
!File.Exists(path))
{
SetComment(sheet, rownum, cell.ColumnNumber, "Can not resolve hyperlink.", Common.Colours.ErrorColour);
}
}
}
public CreditCard(ExcelWorksheet sheet, Dictionary <string, ColumnHeader> ch, int rownum, IEnumerable <Category> categoryRows)
{
RowNumber = rownum;
PaidDate = new ColumnDateTime(sheet, rownum, ch["Paid Date"]);
StatementDate = new ColumnDateTime(sheet, rownum, ch["Statement Date"]);
TransactionDate = new ColumnDateTime(sheet, rownum, ch["Transaction Date"]);
TransactionDescription = new ColumnString(sheet, rownum, ch["Transaction Description"]);
TransactionAmount = new ColumnDecimal(sheet, rownum, ch["Transaction Amount"]);
VatContent = new ColumnDecimal(sheet, rownum, ch["Vat Content"]);
Postage = new ColumnDecimal(sheet, rownum, ch["P & P"]);
Category = new ColumnString(sheet, rownum, ch["Category"]);
Notes = new ColumnString(sheet, rownum, ch["Notes"]);
if (sheet.Cells[rownum, ch["Notes"].ColumnNumber].Hyperlink != null)
{
NotesHyperLink = sheet.Cells[rownum, ch["Notes"].ColumnNumber].Hyperlink;
}
if (categoryRows.Any())
{
Categorise(categoryRows);
}
if (PaidDate.Value != null && StatementDate.Value == null)
{
StatementDate.Errors.Add("Statement date is missing.");
}
}
internal static ColumnString ReplaceIfEmpty(ColumnString original, ColumnString replacement)
{
if (!string.IsNullOrEmpty(original.Value))
{
return(original);
}
original.Value = replacement.Value;
original.Errors = replacement.Errors;
return(original);
}
static void Main(string[] args)
{
Database db = new Database("test-db");
List <Column> columns = new List <Column>();
Column ages = new ColumnInt("Age");
ages.AddValue("23");
ages.AddValue("42");
columns.Add(ages);
Column names = new ColumnString("Name");
names.AddValue("Maria");
names.AddValue("Ignacio");
columns.Add(names);
db.CreateTable("People", columns);
//string query = "SELECT * FROM People WHERE Age < 30;";
//Console.WriteLine(query + ": " + db.RunQuery(query));
string queryDelete = "DROP TABLE People;";
Console.WriteLine(queryDelete + ": " + db.RunQuery(queryDelete));
//string query = "SELECT Name FROM People;";
//Console.WriteLine(query + ": " + db.RunQuery(query));
//Console.WriteLine("MiniSQLJinix V0.0.0.0.0.1");
//BDData db = BDData.getInstance();
//int counter = 0;
//string line;
//// Read the file and display it line by line.
//System.IO.StreamReader file =
// new System.IO.StreamReader(@"..\..\..\Prueba.txt");
//while ((line = file.ReadLine()) != null)
//{
// System.Console.WriteLine(line);
// counter++;
// string result = db.RunQuery(line);
// Console.WriteLine(result);
//}
//file.Close();
//System.Console.WriteLine("There were {0} lines.", counter);
//// Suspend the screen.
//System.Console.ReadLine();
}
public static void UpdateCellString(ExcelWorksheet sheet, int rownumber, ColumnString field, string isBlankErrorMessage = "", bool IsBold = false)
{
if (field == null || (string.IsNullOrEmpty(field.Value) &&
!field.Errors.Any() &&
string.IsNullOrWhiteSpace(isBlankErrorMessage)))
{
return;
}
var cell = sheet.Cells[rownumber, field.ColumnNumber];
cell.Value = field.Value.TrimEnd('\r', '\n');
cell.Style.Font.Bold = IsBold;
WriteErrors(sheet, rownumber, field, isBlankErrorMessage);
}
public override String Run(Database bd)
{
//TODO: Create columns one by one in a list and call bd.CreateDatabase
if (bd.GetTableByName(pTabla) != null)
{
return(Messages.TableErrorAlreadyExists);
}
if (pTipoDato.Count != columnNames.Count)
{
return(Messages.WrongSyntax + ", Ammount of column names provided and column types does not match.");
}
int cont = 0;
List <Column> listOfCreation = new List <Column>();
foreach (String tipo in pTipoDato)
{
Column column;
if (String.IsNullOrWhiteSpace(columnNames[cont]) && String.IsNullOrEmpty(columnNames[cont]))
{
return(Messages.WrongSyntax + "; Empty, null or whitespace Strings are not accepted as column names.");
}
if (String.Equals(tipo, "TEXT", StringComparison.OrdinalIgnoreCase))
{
column = new ColumnString(columnNames[cont]);
}
else if (String.Equals(tipo, "INT", StringComparison.OrdinalIgnoreCase))
{
column = new ColumnInt(columnNames[cont]);
}
else if (String.Equals(tipo, "DOUBLE", StringComparison.OrdinalIgnoreCase))
{
column = new ColumnFloat(columnNames[cont]);
}
else
{
return(Messages.WrongSyntax + ", given type is not recognized by our system, please introduce the type in the form of 'String', 'Int' or 'Double' (System will ignore letter case)");
}
Console.WriteLine("Column created, type: " + tipo + ", column name: " + columnNames[cont]);
listOfCreation.Add(column);
cont++;
}
bd.CreateTable(pTabla, listOfCreation);
return("CREATE TABLE Query was successful, on standby for Database CreateTable method to validate");
}
public CurrentAccount(ExcelWorksheet sheet, Dictionary <string, ColumnHeader> ch, int rownum, IEnumerable <Category> categories, IEnumerable <CreditCard> ccRows)
{
RowNumber = rownum;
Date = new ColumnDateTime(sheet, rownum, ch["Date"]);
//if (Date.Value == null)
//{
// Date = null;
// rownum = -1;
// return ;
//}
Description = new ColumnString(sheet, rownum, ch["Description"]);
Debit = new ColumnDecimal(sheet, rownum, ch["Debit"]);
if (Debit.Value != null)
{
Debit.Value = -1 * Math.Abs((decimal)Debit.Value);
}
Credit = new ColumnDecimal(sheet, rownum, ch["Credit"]);
Balence = new ColumnDecimal(sheet, rownum, ch["Balence"]);
CalculatedMonthlyBalence = new ColumnDecimal(sheet, rownum, ch["Monthly"]);
CalculatedYearlyBalence = new ColumnDecimal(sheet, rownum, ch["Yearly"]);
Category = new ColumnString(sheet, rownum, ch["Category Override"]);
Notes = new ColumnString(sheet, rownum, ch["Notes"]);
if (sheet.Cells[rownum, ch["Notes"].ColumnNumber].Hyperlink != null)
{
NotesHyperLink = sheet.Cells[rownum, ch["Notes"].ColumnNumber].Hyperlink;
}
if (Date == null)
{
IsDivider = true;
if (Debit != null || Credit != null)
{
IsMonthlySummary = true;
}
}
IsStartingBalence = (rownum == 3);
// set the categories
Categorise(categories, ccRows);
//Set the train flag
IsTrainTravel = (Notes.Value.Equals("IsTrainTravel", StringComparison.CurrentCultureIgnoreCase));
}
public void GetEntityValueFromListing(Listing listing)
{
if (listing != null)
{
switch (listing.Type)
{
case "DateTime":
this.DateColumn = new ColumnDate(listing.Value);
if (DateColumn != null || DateColumn.EntityValue != null)
this.ListingItems.Add(DateColumn);
break;
case "Double":
this.DoubleCoumn = new ColumnDouble(listing);
if (DoubleCoumn != null || DoubleCoumn.EntityValue != null)
this.ListingItems.Add(DoubleCoumn);
break;
case "bool":
this.BoolCoumn = new ColumnBoolean(listing);
if (BoolCoumn != null || BoolCoumn.EntityValue != null)
this.ListingItems.Add(BoolCoumn);
break;
case "Int":
var numberValue = new ColumnNumber(listing);
if (numberValue != null || numberValue.EntityValue != null)
this.ListingItems.Add(numberValue);
break;
case "String":
var stringValue = new ColumnString(listing);
if (stringValue != null || stringValue.EntityValue != null)
this.ListingItems.Add(stringValue);
break;
}
}
else
{
throw new Exception("Item Listing is Missing");
}
}
/// <summary>
/// Load the category into Memory
/// </summary>
public void LoadCategory()
{
_SheetCategories = Package.Workbook.Worksheets[eWorkSheetLabels.CategoryLookup];
CategoryColumns = Common.GetColumnHeaders(_SheetCategories, 1);
List <Category> accountingCategories = new List <Category>();
int rownum = 2;
while (rownum <= _SheetCategories.Dimension.Rows)
{
ColumnString description = new ColumnString(_SheetCategories, rownum, CategoryColumns[Resources.ColumnHeader_Description]);
if (!string.IsNullOrEmpty(description.Value))
{
var row = new Category()
{
Description = description,
AccountingCategory = new ColumnString(_SheetCategories, rownum, CategoryColumns[Resources.ColumnHeader_Category]),
Notes = new ColumnString(_SheetCategories, rownum, CategoryColumns[Resources.ColumnHeader_Notes])
};
if (_SheetCategories.Cells[rownum, CategoryColumns[Resources.ColumnHeader_Notes].ColumnNumber].Hyperlink != null)
{
row.NotesHyperLink = _SheetCategories.Cells[rownum, CategoryColumns[Resources.ColumnHeader_Notes].ColumnNumber].Hyperlink;
}
accountingCategories.Add(row);
}
rownum++;
}
if (!accountingCategories.Any())
{
throw new IndexOutOfRangeException(Resources.Error_NoCategoriesCouldBeFound);
}
//Sort by description
accountingCategories = accountingCategories.OrderBy(o => o.Description.Value).ToList();
// Set the duplicate flags
IEnumerable <string> duplicateDescriptions = accountingCategories
.GroupBy(g => g.Description.Value)
.Where(w => !string.IsNullOrEmpty(w.Key) && w.Count() > 1)
.Select(s => s.Key);
foreach (string key in duplicateDescriptions)
{
foreach (Category c in accountingCategories.Where(w => !string.IsNullOrEmpty(w.Description.Value) &&
w.Description.Value.Equals(key, StringComparison.CurrentCultureIgnoreCase)))
{
c.IsDuplicateDescription = true;
}
}
IEnumerable <string> duplicateNotes = accountingCategories
.GroupBy(g => g.Notes.Value)
.Where(w => !string.IsNullOrEmpty(w.Key) && w.Count() > 1)
.Select(s => s.Key);
foreach (string key in duplicateNotes)
{
foreach (Category c in accountingCategories.Where(w => !string.IsNullOrEmpty(w.Notes.Value) &&
w.Notes.Value.Equals(key, StringComparison.CurrentCultureIgnoreCase)))
{
c.IsDuplicateNotes = true;
}
}
accountingCategories.Sort(delegate(Category x, Category y)
{
if (x.Description == null && y.Description == null)
{
return(0);
}
else if (x.Description == null)
{
return(-1);
}
else if (y.Description == null)
{
return(1);
}
else
{
return(x.Description.Value.CompareTo(y.Description.Value));
}
});
CategoryRows = accountingCategories;
}
private static void WriteErrors(ExcelWorksheet sheet, int rownum, ColumnString field, string isBlankErrorMessage = null)
{
WriteErrors(sheet, rownum, field.ColumnNumber, field.Errors, isBlankErrorMessage);
}