sql


## program c#

```
using MySqlConnector;
using Dapper;

var builder = WebApplication.CreateBuilder(args);
var app = builder.Build();

var dbDataProvider = new DBDataProvider();

app.MapGet("/product", (int? pageNum) =>
{
return dbDataProvider.GetProducts(pageNum ?? 1);
});

app.MapPost("/product", (Product newProduct) =>
{
dbDataProvider.saveProduct(newProduct);
});

app.MapDelete("/product/{id:int}", (int id) =>
{
dbDataProvider.removeProduct(id);
});

app.MapPut("/product", (Product editProduct) =>
{
dbDataProvider.saveProduct(editProduct);
});

app.MapGet("/productCount", () =>
{
return dbDataProvider.GetProductCount();
});

app.MapPut("/minCostForAgent/{minCost:decimal}", (decimal minCost, int[] ids) =>
{
dbDataProvider.setMinCostForAgent(minCost, ids);
});

app.MapGet("/articleCheck/{article}/{ID:int}", (string article, int ID) =>
{
return dbDataProvider.getArticleCheck(article, ID);
});

app.MapGet("/saleCount/{ID:int}", (int ID) =>
{
return dbDataProvider.saleCount(ID);
});

app.MapDelete("/productMaterial/{ID:int}", (int ID) =>
{
dbDataProvider.removeProductMaterial(ID);
});

app.MapDelete("/productCostHistory/{ID:int}", (int ID) =>
{
dbDataProvider.removeProductCostHistory(ID);
});

app.MapGet("/productMaterial/{ID:int}", (int ID) =>
{
return dbDataProvider.getProductMaterials(ID);
});

app.MapGet("/material", () =>
{
return dbDataProvider.getMaterials();
});

app.MapPost("/productMaterial", (ProductMaterial material) =>
{
dbDataProvider.addProductMaterial(material);
});


app.Run();

```

## dbddataprovb

```
public class DBDataProvider
{
public string connectionString = "Server=kolei.ru; User ID=aleuhin; Password=101005; Database=aleuhin";
public List GetProducts(int pageNum)
{
using (MySqlConnection db = new MySqlConnection(connectionString))
{
var builder = new SqlBuilder();

// добавление сортировки в запрос
if (orderCondition.Length > 0)
builder.OrderBy(orderCondition);

// добавление фильтрации в запрос
if (filters.Count > 0)
{
foreach (var item in filters)
builder.Where(item.Key, item.Value);
}

// составление шаблона запроса
var template = builder.AddTemplate(
"SELECT * FROM aleuhin.12 /**where**/ /**orderby**/ LIMIT @pageLen OFFSET @offset",
new
{
pageLen = 20,
offset = (pageNum - 1) * 20
}
);

// выполнение запроса
return db.Query
(
template.RawSql,
template.Parameters).ToList();
}
}

private string orderCondition = "";
public void setOrder(string condition)
{
orderCondition = condition;
}

private Dictionary filters = new Dictionary();
public void addFilter(string name, object value)
{
filters.Add(name, value);
}
public void clearFilter()
{
filters.Clear();
}

public void saveProduct(Product product)
{
using (MySqlConnection db = new MySqlConnection(connectionString))
{
if (product.ID == 0)
{
db.Execute("INSERT INTO Product" +
"(`Title`, `ProductTypeID`,`ArticleNumber`,`Description`,`Image`," +
"`ProductionPersonCount`,`ProductionWorkshopNumber`,`MinCostForAgent`) " +
"VALUES (@Title,@ProductTypeID,@ArticleNumber,@Description,@Image," +
"@ProductionPersonCount,@ProductionWorkshopNumber,@MinCostForAgent);", product);
}
else
{
db.Execute("UPDATE Product SET Title=@Title, ProductTypeID=@ProductTypeID, " +
"ArticleNumber=@ArticleNumber, Description=@Description, " +
"Image=@Image, ProductionPersonCount=@ProductionPersonCount, " +
"ProductionWorkshopNumber=@ProductionWorkshopNumber, " +
"MinCostForAgent=@MinCostForAgent " +
"WHERE ID=@ID", product);
}
}
}

public void removeProduct(int ID)
{
using (MySqlConnection db = new MySqlConnection(connectionString))
{
db.Execute("DELETE FROM Product WHERE ID = @ID", new { ID = ID });
}
}

public int GetProductCount()
{
using (MySqlConnection db = new MySqlConnection(connectionString))
{
var builder = new SqlBuilder();
if (filters.Count > 0)
{
foreach (var item in filters)
builder.Where(item.Key, item.Value);
}

var template = builder.AddTemplate(
"SELECT count(*) FROM 12 /**where**/");

return db.QuerySingle(
template.RawSql,
template.Parameters);
}
}

public void setMinCostForAgent(decimal minCostForAgent, int[] ids)
{
using (MySqlConnection db = new MySqlConnection(connectionString))
{
db.Execute("UPDATE Product SET MinCostForAgent=@newCost WHERE ID in @idList", new
{
newCost = minCostForAgent,
idList = ids
});
}
}

public int getArticleCheck(string article, int ID)
{
using (MySqlConnection db = new MySqlConnection(connectionString))
{
return db.QuerySingle("SELECT count(*) FROM Product " +
"WHERE ArticleNumber=@article AND ID != @ID;", new
{
article = article,
ID = ID
});
}
}


public int saleCount(int ID)
{
using (MySqlConnection db = new MySqlConnection(connectionString))
{
if (ID != 0)
{
return db.QuerySingle("SELECT count(*) FROM ProductSale WHERE ProductID=@ID", new { ID = ID });
}
return 0;
}
}

public void removeProductMaterial(int ID)
{
using (MySqlConnection db = new MySqlConnection(connectionString))
{
db.Execute("DELETE FROM ProductMaterial WHERE ProductID=@ID", new { ID = ID });
}
}

public void removeProductCostHistory(int ID)
{
using (MySqlConnection db = new MySqlConnection(connectionString))
{
db.Execute("DELETE FROM ProductCostHistory WHERE ProductID = @ID", new { ID = ID });
}
}

public List getProductMaterials(int ID)
{
using (MySqlConnection db = new MySqlConnection(connectionString))
{
return db.Query("SELECT pm.*,m.Title " +
"FROM ProductMaterial as pm,Material as m " +
"WHERE pm.ProductID = @ID AND m.ID = pm.MaterialID", new { ID = ID }).ToList();
}
}

public void deleteProductMaterial(ProductMaterial material)
{
using (MySqlConnection db = new MySqlConnection(connectionString))
{
db.Execute("DELETE FROM ProductMaterial WHERE ProductID = @ProductId AND MaterialID = @MaterialId", material);
}
}

public List getMaterials()
{
using (MySqlConnection db = new MySqlConnection(connectionString))
{
return db.Query("SELECT ID,Title FROM aleuhin.Material").ToList();
}
}

public void addProductMaterial(ProductMaterial material)
{
using (MySqlConnection db = new MySqlConnection(connectionString))
{
db.Execute("INSERT INTO ProductMaterial (`ProductID`,`MaterialID`,`Count`) VALUES (@ProductId,@MaterialId,@Count)", material);
}
}
}

```

## classes

```
public class Product
{
public int ID { get; set; }
public string Title { get; set; }
public int ProductTypeID { get; set; }
public string ProductTypeTitle { get; set; }
public string ArticleNumber { get; set; }
public string Description { get; set; }
public string Image { get; set; }
public int ProductionPersonCount { get; set; }
public int ProductionWorkshopNumber { get; set; }
public decimal MinCostForAgent { get; set; }
public int MaterialCost { get; set; }
public string MaterialString { get; set; }
public int? LastMonthSaleQuantity { get; set; }
}

public class ProductMaterial
{
public int ProductId { get; set; }
public int MaterialId { get; set; }
public double Count { get; set; }
}

public class Material
{
public int ID { get; set; }
public string Title { get; set; }
}

```