130 lines
4.6 KiB
C#
130 lines
4.6 KiB
C#
using MySql.Data.MySqlClient;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace NeshinaPolina2111
|
|
{
|
|
internal class DB
|
|
{
|
|
private static DB instance;
|
|
private MySqlConnection connection;
|
|
private string connectionString = "server=git.pgk.alspio.com; port=3306; username=ISP41_Neshina; password=ISP41_Neshina; database=ISP41_Neshina_Dem2011; Sslmode=none; Charset=utf8mb4";
|
|
|
|
private DB()
|
|
{
|
|
connection = new MySqlConnection(connectionString);
|
|
OpenConnection();
|
|
}
|
|
|
|
public static DB GetInstance()
|
|
{
|
|
if (instance == null)
|
|
instance = new DB();
|
|
return instance;
|
|
}
|
|
|
|
public void OpenConnection()
|
|
{
|
|
if (connection.State == System.Data.ConnectionState.Closed)
|
|
connection.Open();
|
|
}
|
|
|
|
public MySqlConnection GetConnection() => connection;
|
|
|
|
public static decimal CalculateProductCost(int productId) //метод расчёта для модуоля 2
|
|
{
|
|
string query = @"
|
|
SELECT
|
|
COALESCE(SUM(m.PriceUnitMaterial * pc.Quantity), 0) AS TotalCost
|
|
FROM Product_composition pc
|
|
JOIN Materials_import m ON pc.idMaterials_import = m.idMaterials_import
|
|
WHERE pc.idProducts_import = @productId";
|
|
|
|
var conn = DB.GetInstance().GetConnection();
|
|
if (conn.State == ConnectionState.Closed)
|
|
conn.Open();
|
|
|
|
using (var cmd = new MySqlCommand(query, conn))
|
|
{
|
|
cmd.Parameters.AddWithValue("@productId", productId);
|
|
var result = cmd.ExecuteScalar();
|
|
return Convert.ToDecimal(result);
|
|
}
|
|
}
|
|
|
|
//это для выполнения метода из 4 модуля рассчёт
|
|
public static int CalculateMaterialToPurchase(
|
|
int productTypeId,
|
|
int materialTypeId,
|
|
int quantityProduced,
|
|
decimal param1,
|
|
decimal param2,
|
|
decimal stockMaterial)
|
|
{//1 валидация
|
|
if (productTypeId <= 0 ||
|
|
materialTypeId <= 0 ||
|
|
quantityProduced <= 0 ||
|
|
param1 <= 0 ||
|
|
param2 <= 0 ||
|
|
stockMaterial < 0)
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
// загрузка коэфф тип продукт
|
|
decimal productCoefficient;
|
|
string productQuery = "SELECT Coefficient FROM Product_type_import WHERE idProduct_type_import = @productTypeId";
|
|
var conn = DB.GetInstance().GetConnection();
|
|
if (conn.State == ConnectionState.Closed)
|
|
conn.Open();
|
|
|
|
using (var cmd = new MySqlCommand(productQuery, conn))
|
|
{
|
|
cmd.Parameters.AddWithValue("@productTypeId", productTypeId);
|
|
var result = cmd.ExecuteScalar();
|
|
if (result == null || result == DBNull.Value)
|
|
return -1; // тип продукции не найден
|
|
productCoefficient = Convert.ToDecimal(result);
|
|
}
|
|
|
|
// загрузка процент брака
|
|
decimal scrapPercent;
|
|
string materialQuery = "SELECT Procent FROM Material_type_import WHERE idMaterial_type_import = @materialTypeId";
|
|
using (var cmd = new MySqlCommand(materialQuery, conn))
|
|
{
|
|
cmd.Parameters.AddWithValue("@materialTypeId", materialTypeId);
|
|
var result = cmd.ExecuteScalar();
|
|
if (result == null || result == DBNull.Value)
|
|
return -1; // тип материала не найден
|
|
scrapPercent = Convert.ToDecimal(result);
|
|
}
|
|
|
|
// базовая потребность на 1
|
|
decimal baseNeedPerUnit = param1 * param2 * productCoefficient;
|
|
|
|
// общая потреб без брак
|
|
decimal totalNeed = baseNeedPerUnit * quantityProduced;
|
|
|
|
// + учесть брак
|
|
decimal scrapMultiplier = 1.0m + (scrapPercent / 100.0m);
|
|
decimal totalNeedWithScrap = totalNeed * scrapMultiplier;
|
|
|
|
// минус остатки склада
|
|
decimal needToPurchase = totalNeedWithScrap - stockMaterial;
|
|
|
|
if (needToPurchase <= 0)
|
|
return 0;
|
|
|
|
// округлить до целого
|
|
// купить только целое
|
|
int resultInt = (int)Math.Ceiling(needToPurchase);
|
|
|
|
return resultInt;
|
|
}
|
|
}
|
|
}
|