public static string getProjects(string articleID)
{
string projects = "";
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "SELECT proj.ProjectName FROM Article_Project AS artproj" +
" JOIN Projects AS proj ON proj.ProjectID = artproj.ID_Project" +
" WHERE artproj.ID_Article = '" + articleID + "'";
SQLiteCommand cmd = new SQLiteCommand(query, con);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
projects += "," + reader[0].ToString();
}
return(projects.Remove(0, 1));
}
else
{
return("");
}
}
}
public static List <ArticleModel> checkSupplierAndManufacturerPartNumber(string manufacturerPartNumber, string supplierPartNumber)
{
List <ArticleModel> duplicateArticles = new List <ArticleModel>();
using (SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString()))
{
con.Open();
string query = "SELECT ArticleID, ManufacturerPartNumber, SupplierPartNumber FROM Articles WHERE ManufacturerPartNumber IS @ManufacturerPartNumber OR SupplierPartNumber IS @SupplierPartNumber";
SQLiteCommand cmd = new SQLiteCommand(query, con);
cmd.Parameters.AddWithValue("@ManufacturerPartNumber", manufacturerPartNumber);
cmd.Parameters.AddWithValue("@SupplierPartNumber", supplierPartNumber);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ArticleModel article = new ArticleModel()
{
ArticleID = Convert.ToInt32(reader[0].ToString()),
ManufacturerPartNumber = reader[1].ToString(),
SupplierPartNumber = reader[2].ToString()
};
duplicateArticles.Add(article);
}
}
con.Close();
return(duplicateArticles);
}
}
public static List <ArticleModel> GetAdvancedSearchList(CheckBox[] cbxs, string input)
{
using (SQLiteConnection cnn = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString()))
{
var output = cnn.Query <ArticleModel>(ExpertSmartSearch.advancedQuery(cbxs, input), new DynamicParameters());
cnn.Close();
addHyperLinkAndProjects(output.ToList());
return(output.ToList());
}
}
public static List <ArticleModel> myFoundArticles(string columnName, string attr)
{
using (SQLiteConnection cnn = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString()))
{
var output = cnn.Query <ArticleModel>("SELECT Article.ArticleID, Article.Description , Manufacturer.name AS Manufacturer,Article.ManufacturerPartNumber, Supplier.name AS Supplier, Article.SupplierPartNumber , Location.Location AS Location, Article.Quantity, Article.Created, Article.LastUpdate, Article.Pricing FROM Articles AS Article"
+ " left JOIN Manufacturers AS Manufacturer ON Article.ID_Manufacturer = Manufacturer.ManufacturerID "
+ " left JOIN Suppliers AS Supplier ON Article.ID_Supplier = Supplier.SupplierID"
+ " left JOIN Locations AS Location ON Article.ID_Location = Location.LocationID WHERE " + columnName + " Like '%" + attr + "%'", new DynamicParameters());
cnn.Close();
return(output.ToList());
}
}
public static void deleteArticle(ArticleModel article)
{
archivInsert(article);
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "DELETE FROM Articles WHERE ArticleID = @ArticleID ";
SQLiteCommand cmd = new SQLiteCommand(query, con);
cmd.Parameters.AddWithValue("@ArticleID", article.ArticleID);
cmd.ExecuteNonQuery();
con.Close();
}
public static void addSupplier(SupplierModel supplier)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
string query = "INSERT INTO Suppliers (Name, ClientNumber, WebAddress) VALUES(@Name,@ClientNumber, @WebAddress);";
SQLiteCommand cmd = new SQLiteCommand(query, con);
con.Open();
cmd.Parameters.AddWithValue("@Name", supplier.Name);
cmd.Parameters.AddWithValue("@ClientNumber", supplier.webAddress);
cmd.Parameters.AddWithValue("@WebAddress", supplier.clientNumber);
cmd.ExecuteNonQuery();
con.Close();
}
public static List <ArticleModel> GetInventoryList()
{
using (SQLiteConnection cnn = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString()))
{
var output = cnn.Query <ArticleModel>("SELECT Article.ArticleID, Article.Description , Manufacturer.name AS Manufacturer,Article.ManufacturerPartNumber, Supplier.name AS Supplier, Article.SupplierPartNumber , Location.Location AS Location, Article.Quantity, Article.Created, Article.LastUpdate, Article.Pricing FROM Articles AS Article"
+ " left JOIN Manufacturers AS Manufacturer ON Article.ID_Manufacturer = Manufacturer.ManufacturerID"
+ " left JOIN Suppliers AS Supplier ON Article.ID_Supplier = Supplier.SupplierID"
+ " left JOIN Locations AS Location ON Article.ID_Location = Location.LocationID; ", new DynamicParameters());
cnn.Close();
addHyperLinkAndProjects(output.ToList());
return(output.ToList());
}
}
public static void archivInsert(ArticleModel article)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "INSERT INTO Archiv_Article( " +
"old_ArticleID, old_Description, " +
"old_Category, old_Pricing, " +
"old_Projects, " +
"old_SupplierPartNumber, " +
"old_ManufacturerPartNumber, " +
"old_Manufacturer, " +
"old_Supplier, " +
"old_Status," +
"old_Created, " +
"old_Deleted) " +
"VALUES(" +
"@old_ArticleID, @old_Description, " +
"@old_Category, @old_Pricing, " +
"@old_Projects, " +
"@old_SupplierPartNumber, " +
"@old_ManufacturerPartNumber, " +
"@old_Manufacturer, " +
"@old_Supplier, " +
"@old_Status," +
"@old_Created, " +
"@old_Deleted )";
SQLiteCommand cmd = new SQLiteCommand(query, con);
cmd.Parameters.AddWithValue("@old_ArticleID", article.ArticleID);
cmd.Parameters.AddWithValue("@old_Description", article.Description);
cmd.Parameters.AddWithValue("@old_Category", article.Category);
cmd.Parameters.AddWithValue("@old_Pricing", article.Pricing);
cmd.Parameters.AddWithValue("@old_Projects", Article_ProjectController.getProjects(article.ArticleID.ToString()));
cmd.Parameters.AddWithValue("@old_SupplierPartNumber", article.SupplierPartNumber);
cmd.Parameters.AddWithValue("@old_ManufacturerPartNumber", article.ManufacturerPartNumber);
cmd.Parameters.AddWithValue("@old_Manufacturer", article.Manufacturer);
cmd.Parameters.AddWithValue("@old_Supplier", article.Supplier);
cmd.Parameters.AddWithValue("@old_Status", article.Status);
cmd.Parameters.AddWithValue("@old_Created", article.Created);
cmd.Parameters.AddWithValue("@old_Created", article.Created);
cmd.Parameters.AddWithValue("@old_Deleted", DateTime.Now.ToString("dd/MM/yyyy"));
cmd.ExecuteNonQuery();
con.Close();
}
public static int getStatusID(string status)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "SELECT StatusID FROM Status WHERE Status = '" + status + "'";
SQLiteCommand cmd = new SQLiteCommand(query, con);
var statusID = cmd.ExecuteScalar();
con.Close();
if (statusID != null)
{
return(Convert.ToInt32(statusID));
}
else
{
return(0);
}
}
public static void addNewArticle(ArticleModel article)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
string query = "INSERT INTO Articles (ArticleID, Description, ID_Category, Pricing, Quantity, ID_Project, SupplierPartNumber, ManufacturerPartNumber, ID_Manufacturer, ID_Supplier, ID_Status, Created, LastUpdate, ID_Location) VALUES (@ArticleID, @Description, @ID_Category, @Pricing, @Quantity, @ID_Project, @SupplierPartNumber, @ManufacturerPartNumber, @ID_Manufacturer, @ID_Supplier, @ID_Status, @Created, @LastUpdate, @ID_Location)";
SQLiteCommand cmd = new SQLiteCommand(query, con);
con.Open();
cmd.Parameters.AddWithValue("@ArticleID", article.ArticleID);
cmd.Parameters.AddWithValue("@Description", article.Description);
cmd.Parameters.AddWithValue("@ID_Category", article.ID_Category);
cmd.Parameters.AddWithValue("@Pricing", article.Pricing);
cmd.Parameters.AddWithValue("@Quantity", article.Quantity);
cmd.Parameters.AddWithValue("@ID_Project", article.ID_Project);
cmd.Parameters.AddWithValue("@SupplierPartNumber", article.SupplierPartNumber);
cmd.Parameters.AddWithValue("@ManufacturerPartNumber", article.ManufacturerPartNumber);
cmd.Parameters.AddWithValue("@ID_Manufacturer", article.ID_Manufacturer);
cmd.Parameters.AddWithValue("@ID_Supplier", article.ID_Supplier);
cmd.Parameters.AddWithValue("@ID_Status", article.ID_Status);
cmd.Parameters.AddWithValue("@Created", article.Created);
cmd.Parameters.AddWithValue("@LastUpdate", article.LastUpdate);
cmd.Parameters.AddWithValue("@ID_Location", article.ID_Location);
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show(article.ArticleID + ": " + article.Description + " was added successfully");
/*cmd.CommandText = query;
* cmd.Parameters.AddWithValue("@ArticleID", 9000);
* cmd.Parameters.AddWithValue("@Description", "LALALALA");
* cmd.Parameters.AddWithValue("@ID_Category", 121);
* cmd.Parameters.AddWithValue("@Pricing", 12.2);
* cmd.Parameters.AddWithValue("@Quantity", 12);
* cmd.Parameters.AddWithValue("@ID_Projects", 1);
* cmd.Parameters.AddWithValue("@SupplierPartNumber", "asdasdasdas");
* cmd.Parameters.AddWithValue("@ManufacturerPartNumber", "asdasdasd");
* cmd.Parameters.AddWithValue("@ID_Manufacturer", 1);
* cmd.Parameters.AddWithValue("@ID_Supplier", "asdasdasdasd");
* cmd.Parameters.AddWithValue("@ID_Status", 1);
* cmd.Parameters.AddWithValue("@Created", "12/12/2018");
* cmd.Parameters.AddWithValue("@LastUpdate", "12/12/2018");
* cmd.Parameters.AddWithValue("@ID_Location", 1);*/
}
public static bool articleprojectExists(Article_ProjectModel proj)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "SELECT * FROM Article_Project WHERE ID_Project = '" + proj.ID_Project + "' AND ID_Article = '" + proj.ID_Article + "'";
SQLiteCommand cmd = new SQLiteCommand(query, con);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
return(true);
}
else
{
return(false);
}
}
}
public static void addArticleProjects(List <Article_ProjectModel> article_projects)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
string query = "INSERT INTO Article_Project (ID_Article, ID_Project) VALUES(@ID_Article, @ID_Project);";
SQLiteCommand cmd = new SQLiteCommand(query, con);
con.Open();
foreach (Article_ProjectModel project in article_projects)
{
if (!articleprojectExists(project))
{
cmd.Parameters.AddWithValue("@ID_Article", project.ID_Article);
cmd.Parameters.AddWithValue("@ID_Project", project.ID_Project);
cmd.ExecuteNonQuery();
}
else
{
}
}
con.Close();
}
public static void updateArticle(ArticleModel am)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "UPDATE Articles SET Description = @Description"
+ ", Pricing = @Pricing"
+ ", ID_Project = @ID_Project"
+ ", SupplierPartNumber = @SupplierPartNumber "
+ ", ManufacturerPartNumber = @ManufacturerPartNumber"
+ ", ID_Manufacturer = @ID_Manufacturer"
+ ", ID_Supplier = @ID_Supplier"
+ ", ID_Status = @ID_Status"
+ ", LastUpdate = @LastUpdate"
+ ", ID_Location = @ID_Location"
+ ", Quantity = @Quantity"
+ " WHERE ArticleID = @ArticleID";
SQLiteCommand cmd = new SQLiteCommand(query, con);
cmd.Parameters.AddWithValue("@Description", am.Description);
cmd.Parameters.AddWithValue("@Pricing", am.Pricing);
cmd.Parameters.AddWithValue("@ID_Project", am.ID_Project);
cmd.Parameters.AddWithValue("@SupplierPartNumber", am.SupplierPartNumber);
cmd.Parameters.AddWithValue("@ManufacturerPartNumber", am.ManufacturerPartNumber);
cmd.Parameters.AddWithValue("@ID_Manufacturer", am.ID_Manufacturer);
cmd.Parameters.AddWithValue("@ID_Supplier", am.ID_Supplier);
cmd.Parameters.AddWithValue("@ID_Status", am.ID_Status);
cmd.Parameters.AddWithValue("@LastUpdate", am.LastUpdate);
cmd.Parameters.AddWithValue("@ID_Location", am.ID_Location);
cmd.Parameters.AddWithValue("@Quantity", am.Quantity);
cmd.Parameters.AddWithValue("@ArticleID", am.ArticleID);
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show(am.ArticleID + ": " + am.Description + " was successfully updated");
}
