//Customer
public static string AddCustomer(string Forename, string Lastname, string adress, int PhoneNumber, string Email, double StrengthRight, double StrengthLeft, DateTime SignupDate, string notes = "")
{
SqlConnection conn = new SqlConnection(SQLConnecter.Connect());
using (SqlCommand com = new SqlCommand())
{
com.Connection = conn;
com.CommandText = "AddCustomer";
com.Parameters.Add("@Forename", SqlDbType.VarChar).Value = Forename;
com.Parameters.Add("@Lastname", SqlDbType.VarChar).Value = Lastname;
com.Parameters.Add("@adress", SqlDbType.VarChar).Value = adress;
com.Parameters.Add("@PhoneNumber", SqlDbType.Int).Value = PhoneNumber;
com.Parameters.Add("@Email", SqlDbType.VarChar).Value = Email;
com.Parameters.Add("@StrengthRight", SqlDbType.Float).Value = StrengthRight;
com.Parameters.Add("@StrengthLeft", SqlDbType.Float).Value = StrengthLeft;
com.Parameters.Add("@notes", SqlDbType.VarChar).Value = notes;
com.Parameters.Add("@SignupDate", SqlDbType.Date).Value = SignupDate;
com.CommandType = CommandType.StoredProcedure;
conn.Open();
com.ExecuteScalar();
conn.Close();
return("Customer Created!");
}
}
public bool UpdateGameById(int gameId, GameSaveData update, SQLConnecter connecter)
{
string updateCommand = string.Format("UPDATE Game set Row='{0}', Column='{1}', PlayerName1='{2}', PlayerName2='{3}', GameMode='{4}', Turn='{5}', CaroBoard='{6}' where id='{5}'",
update.Row, update.Column, update.PlayerName1, update.PlayerName2, update.GameMode, update.Turn, update.CaroBoard, gameId);
return(ExecuteCommand(updateCommand, connecter.connection));
}
/// <summary>
/// contructor of RentBikeController
/// </summary>
public RentBikeController()
{
bikeService = new BaseBikeService(SQLConnecter.GetInstance());
stationService = new StationService(SQLConnecter.GetInstance());
cardService = new CardService(SQLConnecter.GetInstance());
transactionService = new TransactionService(SQLConnecter.GetInstance());
}
public static DataTable GetProductsSpecificDataTable(string Brand, string Colour, string FrameType, string Glasstype, int Price)
{
var ProductSpeceficTable = new DataTable();
using (SqlConnection conn = new SqlConnection(SQLConnecter.Connect()))
{
using (SqlCommand com = new SqlCommand())
{
com.Connection = conn;
com.CommandText = "SelectSpecificProducts";
com.Parameters.Add("@Brand", SqlDbType.VarChar).Value = Brand;
com.Parameters.Add("@Colour", SqlDbType.VarChar).Value = Colour;
com.Parameters.Add("@FrameType", SqlDbType.VarChar).Value = FrameType;
com.Parameters.Add("@Glasstype", SqlDbType.VarChar).Value = Glasstype;
com.Parameters.Add("@Price", SqlDbType.Real).Value = Price;
com.CommandType = CommandType.StoredProcedure;
conn.Open();
SqlDataReader reader = com.ExecuteReader();
ProductSpeceficTable.Load(reader);
}
}
return(ProductSpeceficTable);
}
//Customer
public static string AlterCustomer(string forename, string lastname, string adress, int phoneNumber, string email, float strengthRight, float strengthLeft, string notes, DateTime signupDate, int CustomerID)
{
SqlConnection conn = new SqlConnection(SQLConnecter.Connect());
using (SqlCommand com = new SqlCommand())
{
com.Connection = conn;
com.CommandText = "AlterCustomer";
com.Parameters.Add("@CustomerID", SqlDbType.Int).Value = CustomerID;
com.Parameters.Add("@Forename", SqlDbType.VarChar).Value = forename;
com.Parameters.Add("@Lastname", SqlDbType.VarChar).Value = lastname;
com.Parameters.Add("@adress", SqlDbType.VarChar).Value = adress;
com.Parameters.Add("@PhoneNumber", SqlDbType.Int).Value = phoneNumber;
com.Parameters.Add("@Email", SqlDbType.VarChar).Value = email;
com.Parameters.Add("@StrengthRight", SqlDbType.Float).Value = strengthRight;
com.Parameters.Add("@StrengthLeft", SqlDbType.Float).Value = strengthLeft;
com.Parameters.Add("@notes", SqlDbType.VarChar).Value = notes;
com.Parameters.Add("@SignupDate", SqlDbType.Date).Value = signupDate;
com.CommandType = CommandType.StoredProcedure;
conn.Open();
com.ExecuteScalar();
conn.Close();
return("Customer Created!");
}
}
static void Main()
{
Application.SetHighDpiMode(HighDpiMode.SystemAware);
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
//connect to database
connecter = SQLConnecter.GetInstance();
//init services
bikeService = new BikeService(connecter);
cardService = new CardService(connecter);
electricBikeService = new ElectricBikeService(connecter);
stationService = new StationService(connecter);
tandemService = new TandemService(connecter);
transactionService = new TransactionService(connecter);
userService = new UserService(connecter);
//init controllers
rentBikeController = new RentBikeController();
bikeStationController = new BikeStationController();
returnBikeController = new ReturnBikeController();
//init the presentation
homePageForm = new HomePageForm();
stationDetailForm = new StationDetailForm();
bikeDetailForm = new BikeDetailForm();
cardInformationForm = new CardInformationForm();
listBikeForm = new ListBikeForm();
rentBikeForm = new RentBikeForm();
returnBikeForm = new ReturnBikeForm();
transactionInformationForm = new TransactionInformationForm();
Application.Run(homePageForm);
}
//Products
public static void AlterProduct(int ProductID, string Productname, float price, string Colour, string Brand, string FrameType, string Glasstype, int?RightLensID, int?LeftLensID, string Productdescription = "")
{
SqlConnection conn = new SqlConnection(SQLConnecter.Connect());
using (SqlCommand com = new SqlCommand())
{
com.Connection = conn;
com.CommandText = "AlterProduct";
com.Parameters.Add("@ProductID", SqlDbType.Int).Value = ProductID;
com.Parameters.Add("@Productname", SqlDbType.VarChar).Value = Productname;
com.Parameters.Add("@Price", SqlDbType.Real).Value = price;
com.Parameters.Add("@Colour", SqlDbType.VarChar).Value = Colour;
com.Parameters.Add("@Brand", SqlDbType.VarChar).Value = Brand;
com.Parameters.Add("@FrameType", SqlDbType.VarChar).Value = FrameType;
com.Parameters.Add("@Glasstype", SqlDbType.VarChar).Value = Glasstype;
com.Parameters.Add("@RightLensID", SqlDbType.Int).Value = RightLensID;
com.Parameters.Add("@LeftLensID", SqlDbType.Int).Value = LeftLensID;
com.Parameters.Add("@Productdescription", SqlDbType.VarChar).Value = Productdescription;
com.CommandType = CommandType.StoredProcedure;
conn.Open();
com.ExecuteScalar();
conn.Close();
}
}
public override void CreateTable(SQLConnecter connecter)
{
string createCommand = string.Format("CREATE TABLE IF NOT EXISTS Game {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7})",
"([id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT",
"Row int", "Column int", "PlayerName1 varchar(100)",
"PlayerName2 varchar(100)", "GameMode varchar(20)",
"Turn int", "CaroBoard varchar(1000)");
SQLiteCommand command = new SQLiteCommand(createCommand, connecter.connection);
command.ExecuteNonQuery();
}
public GameSaveData GetGameById(int gameId, SQLConnecter connecter)
{
DataSet data = new DataSet();
SQLiteDataAdapter adapter = new SQLiteDataAdapter(
string.Format("select id, Row, Column, PlayerName1, PlayerName2, GameMode, Turn, CaroBoard from Game where id='{0}'", gameId),
connecter.connection);
adapter.Fill(data);
DataRow entity = data.Tables[0].Rows[0];
return(Utils.ToObject <GameSaveData>(entity));
}
public StorageService()
{
string currentPath = Utils.GetCurrentDirectory();
string projectDirectory = Directory.GetParent(currentPath).Parent.FullName;
connecter = SQLConnecter.GetInstance(string.Format("Data Source={0}; Version = 3;",
projectDirectory + @"\Resources\data\data.sqlite"));
connecter.OpenConnection();
gameWorker = new SaveGameWorker();
CurrentIndex = -1;
InitializeConfiguration();
LoadGame();
}
public int InsertGame(GameSaveData insertData, SQLConnecter connecter)
{
string insertCommand = string.Format("INSERT INTO Game(Row,Column,PlayerName1,PlayerName2,GameMode,Turn,CaroBoard) VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}')",
insertData.Row, insertData.Column, insertData.PlayerName1, insertData.PlayerName2, insertData.GameMode, insertData.Turn, insertData.CaroBoard);
if (ExecuteCommand(insertCommand, connecter.connection))
{
string getIdCoommand = "SELECT last_insert_rowid()";
int _id = Convert.ToInt32(ExecuteScalar(getIdCoommand, connecter.connection));
return(_id);
}
else
{
return(-1);
}
}
public IEnumerable <GameSaveData> GetListGames(SQLConnecter connecter)
{
DataSet data = new DataSet();
SQLiteDataAdapter adapter = new SQLiteDataAdapter(
string.Format("select id, Row, Column, PlayerName1, PlayerName2, GameMode, Turn, CaroBoard from Game"),
connecter.connection);
adapter.Fill(data);
DataRowCollection dataList = data.Tables[0].Rows;
int count = dataList.Count;
for (int i = 0; i < count; i++)
{
yield return(Utils.ToObject <GameSaveData>(dataList[i]));
}
}
//Products
public static void DeleteProduct(int ProductID)
{
SqlConnection conn = new SqlConnection(SQLConnecter.Connect());
using (SqlCommand com = new SqlCommand())
{
com.Connection = conn;
com.CommandText = "DeleteProduct";
com.Parameters.Add("@ProductID", SqlDbType.Int).Value = ProductID;
com.CommandType = CommandType.StoredProcedure;
conn.Open();
com.ExecuteScalar();
conn.Close();
}
}
public static string DeleteOrder(int OrderID)
{
SqlConnection conn = new SqlConnection(SQLConnecter.Connect());
using (SqlCommand com = new SqlCommand())
{
com.Connection = conn;
com.CommandText = "DeleteOrder";
com.Parameters.Add("@OrderID", SqlDbType.Int).Value = OrderID;
com.CommandType = CommandType.StoredProcedure;
conn.Open();
com.ExecuteScalar();
conn.Close();
return("Order Deleted");
}
}
//Order
public static void AddOrder(int OrderID, int customerID, DateTime Date)
{
SqlConnection conn = new SqlConnection(SQLConnecter.Connect());
using (SqlCommand com = new SqlCommand())
{
com.Connection = conn;
com.CommandText = "AddOrder";
com.Parameters.Add("@OrderID", SqlDbType.Int).Value = OrderID;
com.Parameters.Add("@CustomerID", SqlDbType.Int).Value = customerID;
com.Parameters.Add("@Date", SqlDbType.Date).Value = Date;
com.CommandType = CommandType.StoredProcedure;
conn.Open();
com.ExecuteScalar();
conn.Close();
}
}
public MainViewModel()
{
SQLConnecter.CreateConnection();
objectService = new ObjectService();
InventoryObjects = new List <InventoryObject>();
StartTime = DateTime.Now;
EndTime = DateTime.Now;
InitializeLoadedWindowCommand();
InitializeUnitWindowCommand();
InitializeSuplierWindowCommand();
InitializeCustomerWindowCommand();
InitializeObjectWindowCommand();
InitializeUserWindowCommand();
InitializeInputWindowCommand();
InitializeOutputWindowCommand();
InitializeFilterButtonCommand();
}
//Products
public static DataTable GetProductsDataTable()
{
var ProductTable = new DataTable();
using (SqlConnection conn = new SqlConnection(SQLConnecter.Connect()))
{
using (SqlCommand com = new SqlCommand())
{
com.Connection = conn;
com.CommandText = "SelectAllProducts";
com.CommandType = CommandType.StoredProcedure;
conn.Open();
SqlDataReader reader = com.ExecuteReader();
ProductTable.Load(reader);
}
}
return(ProductTable);
}
public TandemService(SQLConnecter connecter)
{
this.connecter = connecter;
}
/// <summary>
/// contructor of StationService
/// </summary>
/// <param name="connecter">The instance representing connection to database</param>
public StationService(SQLConnecter connecter) : base(connecter)
{
}
/// <summary>
/// contructor of BaseService
/// </summary>
/// <param name="connecter">the instance representing connection to database</param>
public BaseService(SQLConnecter connecter)
{
this.connecter = connecter;
}
/// <summary>
/// Contructor of ViewBikeController
/// </summary>
public ViewBikeController()
{
bikeService = new BaseBikeService(SQLConnecter.GetInstance());
}
public bool DeleteGameById(int gameId, SQLConnecter connecter)
{
string updateCommand = string.Format("DELETE FROM Game where id='{0}'", gameId);
return(ExecuteCommand(updateCommand, connecter.connection));
}
public UserService(SQLConnecter connecter)
{
this.connecter = connecter;
}
public virtual void CreateTable(SQLConnecter connecter)
{
}
public ElectricBikeService(SQLConnecter connecter)
{
this.connecter = connecter;
}
public TransactionService(SQLConnecter connecter)
{
this.connecter = connecter;
}
/// <summary>
/// Contructor of ViewStationController
/// </summary>
public ViewStationController()
{
stationService = new StationService(SQLConnecter.GetInstance());
}
/// <summary>
/// contructor of BaseBikeService
/// </summary>
/// <param name="connecter">The instance representing connection to database</param>
public BaseBikeService(SQLConnecter connecter) : base(connecter)
{
}
/// <summary>
/// contructor of CardService
/// </summary>
/// <param name="connecter">The instance representing connection to database</param>
public CardService(SQLConnecter connecter) : base(connecter)
{
}
public CardService(SQLConnecter connecter)
{
this.connecter = connecter;
}
