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