public async Task <int> InsertTransactionAsync(TransactionEntity transactionEntity, bool isUserPerformed = false)
{
string query = "INSERT INTO `Transaction`" +
"(`TransactionPartyId`,`Amount`,`IsIncome`,`TransactionDateTime`,`ScheduledTransactionId`,`Remarks`,`CreatedDateTime`,`IsUserPerformed`) " +
"VALUES (@TransactionPartyId,@Amount,@IsIncome,@TransactionDateTime,@ScheduledTransactionId,@Remarks,@CreatedDateTime,@IsUserPerformed);";
IEnumerable <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("@TransactionPartyId", transactionEntity.TransactionPartyId),
new KeyValuePair <string, object>("@Amount", transactionEntity.Amount),
new KeyValuePair <string, object>("@IsIncome", transactionEntity.IsIncome ? 1 : 0),
new KeyValuePair <string, object>("@TransactionDateTime", TimeConverterMethods.ConvertDateTimeToTimeStamp(transactionEntity.TransactionDateTime)),
new KeyValuePair <string, object>("@ScheduledTransactionId", transactionEntity.ScheduledTransactionId),
new KeyValuePair <string, object>("@Remarks", transactionEntity.Remarks),
new KeyValuePair <string, object>("@CreatedDateTime", TimeConverterMethods.ConvertDateTimeToTimeStamp(transactionEntity.CreatedDateTime)),
new KeyValuePair <string, object>("@IsUserPerformed", isUserPerformed ? 1 : 0)
};
int id = await SqliteConnector.ExecuteInsertQueryAsync(query, parameters, true);
query = "UPDATE `Transaction` SET `ReferenceNumber`=@ReferenceNumber WHERE `Id`=@Id";
parameters = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("@ReferenceNumber", id.ToString("TC00000000")),
new KeyValuePair <string, object>("@Id", id)
};
await SqliteConnector.ExecuteNonQueryAsync(query, parameters, true);
return(id);
}
public async Task <int> InsertTaskAsync(OneTimeTasks task, bool isUserPerformed = false)
{
string query = "INSERT INTO `OneTimeTasks`" +
"(`Type`,`Comments`,`Duration`,`CreatedUser`,`CreatedDateTime`,`IsDelete`,`Effectivedate`) " +
"VALUES (@Type,@Comments,@Duration,@CreatedUser,@CreatedDateTime,@IsDelete,@Effectivedate);";
IEnumerable <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("@Type", task.Type),
new KeyValuePair <string, object>("@Comments", task.Comments),
new KeyValuePair <string, object>("@Duration", task.Duration),
new KeyValuePair <string, object>("@CreatedUser", task.CreatedUser),
new KeyValuePair <string, object>("@Effectivedate", TimeConverterMethods.ConvertDateTimeToTimeStamp(task.Effectivedate)),
new KeyValuePair <string, object>("@CreatedDateTime", TimeConverterMethods.ConvertDateTimeToTimeStamp(task.CreatedDateTime)),
new KeyValuePair <string, object>("@IsDelete", 0)
};
int id = await SqliteConnector.ExecuteInsertQueryAsync(query, parameters, true);
query = "UPDATE `OneTimeTasks` SET `ReferenceNumber`=@ReferenceNumber WHERE `Id`=@Id";
parameters = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("@ReferenceNumber", id.ToString("OTASK000000")),
new KeyValuePair <string, object>("@Id", id)
};
await SqliteConnector.ExecuteNonQueryAsync(query, parameters, true);
return(id);
}
public async Task <int> UpdateUserLastCheckDateTimeAsync(DateTime dateTime)
{
string query = @"UPDATE `User` SET `LastCheckDateTime`=@LastCheckDateTime;";
IList <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("@LastCheckDateTime", TimeConverterMethods.ConvertDateTimeToTimeStamp(dateTime))
};
return(await SqliteConnector.ExecuteNonQueryAsync(query, parameters : parameters, true));
}
public TransactionPartyEntity ReaderToEntity(SQLiteDataReader reader)
{
return(new TransactionPartyEntity()
{
Id = int.Parse(reader["Id"].ToString()),
Code = reader["Code"].ToString(),
Description = reader["Description"].ToString(),
CreatedDateTime = TimeConverterMethods.ConvertTimeStampToDateTime(int.Parse(reader["CreatedDateTime"].ToString())),
IsActive = Convert.ToBoolean(int.Parse(reader["IsActive"].ToString()))
});
}
public async void UpdateNextTransactionDate(int id, DateTime dtAddDates)
{
string query = "UPDATE `ScheduledTasks` SET `Effectivedate`=@Effectivedate WHERE `Id`=@Id";
IEnumerable <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("@Id", id),
new KeyValuePair <string, object>("@Effectivedate", TimeConverterMethods.ConvertDateTimeToTimeStamp(dtAddDates))
};
await SqliteConnector.ExecuteNonQueryAsync(query, parameters, true);
}
public UserEntity ReaderToEntity(SQLiteDataReader reader)
{
return(new UserEntity()
{
Id = int.Parse(reader["Id"].ToString()),
FirstName = reader["FirstName"].ToString(),
LastName = reader["LastName"].ToString(),
StartingAmount = double.Parse(reader["StartingAmount"].ToString()),
CurrentBalance = double.Parse(reader["CurrentBalance"].ToString()),
RegisteredDateTime = TimeConverterMethods.ConvertTimeStampToDateTime(int.Parse(reader["RegisteredDateTime"].ToString())),
LastCheckDateTime = TimeConverterMethods.ConvertTimeStampToDateTime(int.Parse(reader["LastCheckDateTime"].ToString()))
});
}
public OneTimeTasks ReaderToEntity(SQLiteDataReader reader)
{
return(new OneTimeTasks()
{
Id = int.Parse(reader["Id"].ToString()),
ReferenceNumber = reader["ReferenceNumber"].ToString(),
Type = int.Parse(reader["Type"].ToString()),
IsDelete = Convert.ToBoolean(int.Parse(reader["IsDelete"].ToString())),
Duration = double.Parse(reader["Duration"].ToString()),
Comments = reader["Comments"].ToString(),
CreatedDateTime = TimeConverterMethods.ConvertTimeStampToDateTime(int.Parse(reader["CreatedDateTime"].ToString())),
Effectivedate = TimeConverterMethods.ConvertTimeStampToDateTime(int.Parse(reader["Effectivedate"].ToString())),
});
}
public async Task <int> InsertTransactionPartyAsync(TransactionPartyEntity transactionPartyEntity)
{
string query = "INSERT INTO `TransactionParty`" +
"(`Code`,`Description`,`CreatedDateTime`) " +
"VALUES (@Code,@Description,@CreatedDateTime);";
IEnumerable <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("@Code", transactionPartyEntity.Code),
new KeyValuePair <string, object>("@Description", transactionPartyEntity.Description),
new KeyValuePair <string, object>("@CreatedDateTime", TimeConverterMethods.ConvertDateTimeToTimeStamp(transactionPartyEntity.CreatedDateTime))
};
return(await SqliteConnector.ExecuteInsertQueryAsync(query, parameters, true));
}
public TransactionEntity ReaderToEntity(SQLiteDataReader reader)
{
return(new TransactionEntity()
{
Id = int.Parse(reader["Id"].ToString()),
ReferenceNumber = reader["ReferenceNumber"].ToString(),
TransactionPartyId = int.Parse(reader["TransactionPartyId"].ToString()),
ScheduledTransactionId = reader["ScheduledTransactionId"] == DBNull.Value ? null : (int?)int.Parse(reader["ScheduledTransactionId"].ToString()),
IsIncome = Convert.ToBoolean(int.Parse(reader["IsIncome"].ToString())),
Amount = double.Parse(reader["Amount"].ToString()),
Remarks = reader["Remarks"].ToString(),
TransactionDateTime = TimeConverterMethods.ConvertTimeStampToDateTime(int.Parse(reader["TransactionDateTime"].ToString())),
CreatedDateTime = TimeConverterMethods.ConvertTimeStampToDateTime(int.Parse(reader["CreatedDateTime"].ToString())),
IsUserPerformed = Convert.ToBoolean(int.Parse(reader["IsUserPerformed"].ToString())),
IsActive = Convert.ToBoolean(int.Parse(reader["IsActive"].ToString()))
});
}
public SheduledTransactionList ReaderToEntitySheduledTransactionList(SQLiteDataReader reader)
{
return(new SheduledTransactionList()
{
Id = int.Parse(reader["Id"].ToString()),
ReferenceNumber = reader["ReferenceNumber"].ToString(),
TransactionPartyId = int.Parse(reader["TransactionPartyId"].ToString()),
IsIncome = Convert.ToBoolean(int.Parse(reader["IsIncome"].ToString())),
IsActive = Convert.ToBoolean(int.Parse(reader["IsActive"].ToString())),
InfiniteSchedule = Convert.ToBoolean(int.Parse(reader["InfiniteSchedule"].ToString())),
Amount = double.Parse(reader["Amount"].ToString()),
Remarks = reader["Remarks"].ToString(),
RepeatType = reader["RepeatType"].ToString(),
EndDateTime = TimeConverterMethods.ConvertTimeStampToDateTime(int.Parse(reader["EndDateTime"].ToString())),
NextTransactionDate = TimeConverterMethods.ConvertTimeStampToDateTime(int.Parse(reader["NextTransactionDate"].ToString()))
});
}
public async Task <int> UpdateTaskAsync(OneTimeTasks task)
{
string query = "UPDATE `OneTimeTasks` " +
"SET `Type`=@Type,`Comments`=@Comments,`Duration`=@Duration,`Effectivedate`=@Effectivedate " +
"WHERE `Id` = @Id";
IEnumerable <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("@Type", task.Type),
new KeyValuePair <string, object>("@Comments", task.Comments),
new KeyValuePair <string, object>("@Duration", task.Duration),
new KeyValuePair <string, object>("@Effectivedate", TimeConverterMethods.ConvertDateTimeToTimeStamp(task.Effectivedate)),
new KeyValuePair <string, object>("@Id", task.Id),
};
return(await SqliteConnector.ExecuteNonQueryAsync(query, parameters, true));
}
public async Task <int> UpdateTransactionAsync(TransactionEntity transactionEntity)
{
string query = "UPDATE `Transaction` " +
"SET `TransactionPartyId`=@TransactionPartyId,`Amount`=@Amount,`IsIncome`=@IsIncome,`Remarks`=@Remarks,`TransactionDateTime`=@TransactionDateTime " +
"WHERE `Id` = @Id";
IEnumerable <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("@TransactionPartyId", transactionEntity.TransactionPartyId),
new KeyValuePair <string, object>("@Amount", transactionEntity.Amount),
new KeyValuePair <string, object>("@IsIncome", transactionEntity.IsIncome ? 1 : 0),
new KeyValuePair <string, object>("@Remarks", transactionEntity.Remarks),
new KeyValuePair <string, object>("@Id", transactionEntity.Id),
new KeyValuePair <string, object>("@TransactionDateTime", TimeConverterMethods.ConvertDateTimeToTimeStamp(transactionEntity.TransactionDateTime)),
};
return(await SqliteConnector.ExecuteNonQueryAsync(query, parameters, true));
}
public async Task <int> InsertUserDetailsAsync(UserEntity userEntity)
{
userEntity.CurrentBalance = userEntity.StartingAmount;
userEntity.RegisteredDateTime = DateTime.Now;
userEntity.LastCheckDateTime = DateTime.Now;
string query = @"INSERT INTO `User`
(`FirstName`,`LastName`,`RegisteredDateTime`,`StartingAmount`,`CurrentBalance`,`LastCheckDateTime`)
VALUES (@FirstName,@LastName,@RegisteredDateTime,@StartingAmount,@CurrentBalance,@LastCheckDateTime);";
IList <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("@FirstName", userEntity.FirstName),
new KeyValuePair <string, object>("@LastName", userEntity.LastName),
new KeyValuePair <string, object>("@RegisteredDateTime", TimeConverterMethods.ConvertDateTimeToTimeStamp(userEntity.RegisteredDateTime)),
new KeyValuePair <string, object>("@StartingAmount", userEntity.StartingAmount),
new KeyValuePair <string, object>("@CurrentBalance", userEntity.CurrentBalance),
new KeyValuePair <string, object>("@LastCheckDateTime", TimeConverterMethods.ConvertDateTimeToTimeStamp(userEntity.LastCheckDateTime))
};
return(await SqliteConnector.ExecuteInsertQueryAsync(query, parameters : parameters, true));
}
public async Task <int> UpdateScheduledTransactionListAsync(SheduledTransactionList transactionEntity)
{
string query = "UPDATE `ScheduledTransaction` " +
"SET `TransactionPartyId`=@TransactionPartyId,`Amount`=@Amount,`RepeatType`=@RepeatType,`StartDateTime`=@StartDateTime,`EndDateTime`=@EndDateTime,`InfiniteSchedule`=@InfiniteSchedule,`NextTransactionDate`=@NextTransactionDate,`IsActive`=@IsActive,`Remarks`=@Remarks " +
"WHERE `Id` = @Id";
IEnumerable <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("@TransactionPartyId", transactionEntity.TransactionPartyId),
new KeyValuePair <string, object>("@Amount", transactionEntity.Amount),
new KeyValuePair <string, object>("@IsIncome", transactionEntity.IsIncome ? 1 : 0),
new KeyValuePair <string, object>("@Remarks", transactionEntity.Remarks),
new KeyValuePair <string, object>("@RepeatType", transactionEntity.RepeatType),
new KeyValuePair <string, object>("@StartDateTime", TimeConverterMethods.ConvertDateTimeToTimeStamp(transactionEntity.StartDateTime)),
new KeyValuePair <string, object>("@EndDateTime", TimeConverterMethods.ConvertDateTimeToTimeStamp(transactionEntity.EndDateTime ?? transactionEntity.StartDateTime)),
new KeyValuePair <string, object>("@NextTransactionDate", TimeConverterMethods.ConvertDateTimeToTimeStamp(transactionEntity.NextTransactionDate)),
new KeyValuePair <string, object>("@InfiniteSchedule", transactionEntity.InfiniteSchedule ? 1 : 0),
new KeyValuePair <string, object>("@IsActive", transactionEntity.IsActive),
new KeyValuePair <string, object>("@Id", transactionEntity.Id),
};
return(await SqliteConnector.ExecuteNonQueryAsync(query, parameters, true));
}
public async Task <int> InsertScheduledTransactionListAsync(SheduledTransactionList transactionEntity)
{
string query = "INSERT INTO `ScheduledTransaction`" +
"(`TransactionPartyId`,`Amount`,`IsIncome`,`RepeatType`,`StartDateTime`,`Remarks`,`CreatedDateTime`,`EndDateTime`,`NextTransactionDate`,`InfiniteSchedule`,`IsDelete`,`CreatedUser`,`IsActive`) " +
"VALUES (@TransactionPartyId,@Amount,@IsIncome,@RepeatType,@StartDateTime,@Remarks,@CreatedDateTime,@EndDateTime,@NextTransactionDate,@InfiniteSchedule,@IsDelete,@CreatedUser,@IsActive);";
IEnumerable <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("@TransactionPartyId", transactionEntity.TransactionPartyId),
new KeyValuePair <string, object>("@Amount", transactionEntity.Amount),
new KeyValuePair <string, object>("@IsIncome", transactionEntity.IsIncome ? 1 : 0),
new KeyValuePair <string, object>("@RepeatType", transactionEntity.RepeatType),
new KeyValuePair <string, object>("@StartDateTime", TimeConverterMethods.ConvertDateTimeToTimeStamp(transactionEntity.StartDateTime)),
new KeyValuePair <string, object>("@Remarks", transactionEntity.Remarks),
new KeyValuePair <string, object>("@CreatedDateTime", TimeConverterMethods.ConvertDateTimeToTimeStamp(transactionEntity.CreatedDateTime)),
new KeyValuePair <string, object>("@EndDateTime", TimeConverterMethods.ConvertDateTimeToTimeStamp(transactionEntity.EndDateTime ?? transactionEntity.StartDateTime)),
new KeyValuePair <string, object>("@NextTransactionDate", TimeConverterMethods.ConvertDateTimeToTimeStamp(transactionEntity.NextTransactionDate)),
new KeyValuePair <string, object>("@InfiniteSchedule", transactionEntity.InfiniteSchedule ? 1 : 0),
new KeyValuePair <string, object>("@IsDelete", 0),
new KeyValuePair <string, object>("@CreatedUser", transactionEntity.CreatedUser),
new KeyValuePair <string, object>("@IsActive", 1)
};
int id = await SqliteConnector.ExecuteInsertQueryAsync(query, parameters, true);
query = "UPDATE `ScheduledTransaction` SET `ReferenceNumber`=@ReferenceNumber WHERE `Id`=@Id";
parameters = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("@ReferenceNumber", id.ToString("SYSTC00000000")),
new KeyValuePair <string, object>("@Id", id)
};
await SqliteConnector.ExecuteNonQueryAsync(query, parameters, true);
return(id);
}
public async Task <int> InsertTransactionLogAsync(TransactionLogEntity transactionLogEntity)
{
string query = "INSERT INTO `TransactionLog`" +
"(`TransactionId`,`ScheduledTransactionId`,`TransactionPartyId`,`IsDeletedTransaction`,`IsIncome`,`TransactionDateTime`,`Amount`,`StartingBalance`,`FinalBalance`,`Remarks`,`CreatedDateTime`,`IsUserPerformed`) " +
"VALUES(@TransactionId,@ScheduledTransactionId,@TransactionPartyId,@IsDeletedTransaction,@IsIncome,@TransactionDateTime,@Amount,@StartingBalance,@FinalBalance,@Remarks,@CreatedDateTime,@IsUserPerformed);";
IEnumerable <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("@TransactionId", transactionLogEntity.TransactionId),
new KeyValuePair <string, object>("@ScheduledTransactionId", transactionLogEntity.ScheduledTransactionId),
new KeyValuePair <string, object>("@TransactionPartyId", transactionLogEntity.TransactionPartyId),
new KeyValuePair <string, object>("@IsDeletedTransaction", transactionLogEntity.IsDeletedTransaction ? 1 : 0),
new KeyValuePair <string, object>("@IsIncome", transactionLogEntity.IsIncome ? 1 : 0),
new KeyValuePair <string, object>("@Amount", transactionLogEntity.Amount),
new KeyValuePair <string, object>("@StartingBalance", transactionLogEntity.StartingBalance),
new KeyValuePair <string, object>("@FinalBalance", transactionLogEntity.FinalBalance),
new KeyValuePair <string, object>("@Remarks", transactionLogEntity.Remarks),
new KeyValuePair <string, object>("@TransactionDateTime", TimeConverterMethods.ConvertDateTimeToTimeStamp(transactionLogEntity.TransactionDateTime)),
new KeyValuePair <string, object>("@CreatedDateTime", TimeConverterMethods.ConvertDateTimeToTimeStamp(transactionLogEntity.CreatedDateTime)),
new KeyValuePair <string, object>("@IsUserPerformed", transactionLogEntity.IsUserPerformed ? 1 : 0)
};
return(await SqliteConnector.ExecuteInsertQueryAsync(query, parameters, true));
}
public async Task <PredictionEntity> GetPredictionsAsync(int monthsBack, DateTime predictDate)
{
PredictionEntity predictionEntity = null;
await Task.Run(() =>
{
predictionEntity = new PredictionEntity()
{
WarningMessage = IsAvailableEnoughtData(monthsBack) ? "" : _warningMessage,
TodayBalanace = CurrentUser.CurrentBalance
};
IList <DailyBreakDownPredictionEntity> dailyBreakDownPredictions = new List <DailyBreakDownPredictionEntity>();
predictDate = predictDate.Date;
DateTime todayDate = DateTime.Now;
todayDate = todayDate.Date;// Remove time
DateTime monthsBackDate = todayDate.AddMonths(-1 * monthsBack);
IEnumerable <TransactionEntity> orderedTransactions = Transactions.Where(t => t.TransactionDateTime >= monthsBackDate && t.IsActive).OrderBy(t => t.TransactionDateTime);
if (orderedTransactions.Count() > 0)
{
predictionEntity.IsPredicted = true;
int daysFromBackMonth = (int)todayDate.Subtract(monthsBackDate.Date).TotalDays;
double totalInCome = orderedTransactions.Where(ot => ot.IsIncome).Select(ot => ot.Amount).Sum();
double totalInExpenses = orderedTransactions.Where(ot => !ot.IsIncome).Select(ot => ot.Amount).Sum();
predictionEntity.AverageIncome = totalInCome / daysFromBackMonth;
predictionEntity.AverageExpense = totalInExpenses / daysFromBackMonth;
int daysToPredictDate = (int)predictDate.Subtract(todayDate).TotalDays;
predictionEntity.PredictBalanace = CurrentUser.CurrentBalance + ((totalInCome - totalInExpenses) / daysFromBackMonth) * daysToPredictDate;
// Day by day predictions
IEnumerable <DayOfWeek> daysOfWeek = Enum.GetValues(typeof(DayOfWeek)).Cast <DayOfWeek>();
foreach (DayOfWeek dayOfWeek in daysOfWeek)
{
DailyBreakDownPredictionEntity dailyBreakDown = new DailyBreakDownPredictionEntity()
{
DayOfWeek = dayOfWeek
};
IEnumerable <DateTime> dates = TimeConverterMethods.DatesDayOfWeekForDuration(monthsBackDate, todayDate, dayOfWeek);
if (dates.Count() > 0)
{
IEnumerable <TransactionEntity> dayOfWeekIncomeTransactions = orderedTransactions.Where(t => t.IsIncome && dates.Any(d => d.Date == t.TransactionDateTime.Date));
dailyBreakDown.AverageIncome = dayOfWeekIncomeTransactions.Select(x => x.Amount).Sum() / dates.Count();
IEnumerable <TransactionEntity> dayOfWeekExpensesTransactions = orderedTransactions.Where(t => !t.IsIncome && dates.Any(d => d.Date == t.TransactionDateTime.Date));
dailyBreakDown.AverageExpense = dayOfWeekExpensesTransactions.Select(x => x.Amount).Sum() / dates.Count();
}
else
{
dailyBreakDown.AverageIncome = 0;
dailyBreakDown.AverageExpense = 0;
}
dailyBreakDownPredictions.Add(dailyBreakDown);
}
predictionEntity.DailyBreakDownPredictions = dailyBreakDownPredictions;
}
else
{
predictionEntity.IsPredicted = false;
predictionEntity.WarningMessage = "Error: No data found to do the predictions";
}
});
return(predictionEntity);
}
/// <summary>
/// Initialize Database
/// </summary>
public static void InitializeDatabase()
{
if (IsDatabaseInitialized)
{
return;
}
SQLiteConnection.CreateFile(_databasePath);
using (SQLiteConnection conn = GetConnection)
{
conn.Open();
//conn.ChangePassword(_password);
conn.Close();
}
using (SQLiteConnection conn = GetConnection)
{
conn.Open();
SQLiteTransaction transaction = conn.BeginTransaction();
try
{
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
#region Create Tables
cmd.CommandText =
@"CREATE TABLE `User` (
`Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`FirstName` TEXT NOT NULL,
`LastName` TEXT NOT NULL,
`SID` TEXT NOT NULL,
`RegisteredDateTime` INTEGER NOT NULL,
`StartingAmount` NUMERIC NOT NULL,
`CurrentBalance` NUMERIC NOT NULL,
`LastCheckDateTime` INTEGER NOT NULL
);";
cmd.ExecuteNonQuery();
Thread.Sleep(500);
cmd.CommandText =
@"CREATE TABLE `TransactionLog` (
`Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`TransactionId` INTEGER NOT NULL,
`ScheduledTransactionId` INTEGER,
`TransactionPartyId` INTEGER NOT NULL,
`IsDeletedTransaction` INTEGER NOT NULL,
`IsIncome` INTEGER NOT NULL,
`TransactionDateTime` INTEGER NOT NULL,
`Amount` NUMERIC NOT NULL,
`StartingBalance` NUMERIC NOT NULL,
`FinalBalance` NUMERIC NOT NULL,
`Remarks` TEXT NOT NULL,
`CreatedDateTime` INTEGER NOT NULL,
`IsUserPerformed` INTEGER NOT NULL
);";
cmd.ExecuteNonQuery();
Thread.Sleep(500);
cmd.CommandText =
@"CREATE TABLE `Transaction` (
`Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`ReferenceNumber` TEXT,
`TransactionPartyId` INTEGER NOT NULL,
`Amount` NUMERIC NOT NULL,
`IsIncome` INTEGER NOT NULL,
`TransactionDateTime` INTEGER NOT NULL,
`ScheduledTransactionId` INTEGER,
`Remarks` TEXT,
`IsUserPerformed` INTEGER NOT NULL DEFAULT 0,
`CreatedDateTime` INTEGER NOT NULL,
`IsActive` INTEGER NOT NULL DEFAULT 1
);";
cmd.ExecuteNonQuery();
Thread.Sleep(500);
//cmd.CommandText =
// @"CREATE TABLE `ScheduledTransaction` (
// `Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
// `Amount` NUMERIC NOT NULL,
// `TransactionPartyId` INTEGER NOT NULL,
// `Remarks` TEXT NOT NULL,
// `RepeatPeriod` INTEGER NOT NULL,
// `RepeatValue` INTEGER NOT NULL,
// `RepeatCount` INTEGER NOT NULL DEFAULT 1,
// `OccuredCount` INTEGER NOT NULL DEFAULT 0,
// `LastOccuredDateTime` INTEGER,
// `CreatedDateTime` INTEGER NOT NULL,
// `IsActive` INTEGER NOT NULL DEFAULT 1
// );";
//cmd.ExecuteNonQuery();
//Thread.Sleep(500);
cmd.CommandText =
@"CREATE TABLE `TransactionParty` (
`Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`Code` TEXT NOT NULL UNIQUE,
`Description` TEXT NOT NULL,
`CreatedDateTime` INTEGER NOT NULL,
`IsActive` INTEGER NOT NULL DEFAULT 1
);";
cmd.ExecuteNonQuery();
cmd.CommandText =
@"INSERT INTO `TransactionParty`
(`Id`,`Code`,`Description`,`CreatedDateTime`)
VALUES (1,'OWN','Owner Party'," + TimeConverterMethods.ConvertDateTimeToTimeStamp(DateTime.Now).ToString() + ");";
cmd.ExecuteNonQuery();
Thread.Sleep(500);
cmd.CommandText =
@"CREATE TABLE `OneTimeTasks` (
`Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`ReferenceNumber` TEXT,
`Duration` NUMERIC NOT NULL,
`Type` INTEGER NOT NULL,
`Comments` TEXT NOT NULL,
`IsDelete` INTEGER NOT NULL DEFAULT 0,
`CreatedDateTime` INTEGER NOT NULL,
`CreatedUser` INTEGER NOT NULL,
`Effectivedate` INTEGER NOT NULL
);";
cmd.ExecuteNonQuery();
Thread.Sleep(500);
cmd.CommandText =
@"CREATE TABLE `ScheduledTasks` (
`Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`ReferenceNumber` TEXT,
`Duration` NUMERIC NOT NULL,
`Type` INTEGER NOT NULL,
`RepeatType` TEXT NOT NULL,
`Comments` TEXT NOT NULL,
`IsDelete` INTEGER NOT NULL DEFAULT 0,
`IsActive` INTEGER NOT NULL DEFAULT 1,
`CreatedDateTime` INTEGER NOT NULL,
`StartDateTime` INTEGER NOT NULL,
`EndDateTime` INTEGER NOT NULL ,
`CreatedUser` INTEGER NOT NULL,
`Effectivedate` INTEGER NOT NULL
);";
cmd.ExecuteNonQuery();
Thread.Sleep(500);
cmd.CommandText =
@"CREATE TABLE `ScheduledTransaction` (
`Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`ReferenceNumber` TEXT,
`TransactionPartyId` INTEGER NOT NULL,
`Amount` NUMERIC NOT NULL,
`RepeatType` TEXT NOT NULL,
`Remarks` TEXT NOT NULL,
`StartDateTime` INTEGER NOT NULL,
`EndDateTime` INTEGER NOT NULL ,
`NextTransactionDate` INTEGER NOT NULL,
`InfiniteSchedule` INTEGER NOT NULL,
`IsIncome` INTEGER NOT NULL,
`IsDelete` INTEGER NOT NULL DEFAULT 0,
`CreatedDateTime` INTEGER NOT NULL,
`CreatedUser` INTEGER NOT NULL,
`IsActive` INTEGER NOT NULL DEFAULT 1
);";
cmd.ExecuteNonQuery();
Thread.Sleep(500);
#endregion
}
transaction.Commit();
}
catch
{
transaction.Rollback();
applicationErrorLog.ErrorLog("Database", "DB Transaction", "DB Table Create Main query Error");
}
finally
{
conn.Close();
}
}
}
