public int viewTroops()
{
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DbConnect.oradb);
String strSQL = "SELECT * FROM V_PlayerTroops";
conn.Open();
Oracle.ManagedDataAccess.Client.OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand(strSQL, conn);
Oracle.ManagedDataAccess.Client.OracleDataReader dr = cmd.ExecuteReader();
//read the record in dr
dr.Read();
if (dr.IsDBNull(0))
{
playerTroops = 1;
}
else
{
playerTroops = Convert.ToInt16(dr.GetValue(0)) + 1;
}
conn.Close();
return(playerTroops);
}
public static OracleDataReader GetRecentProductData(int MachineId, OracleConnection Con)
{
Logger.Debug("GetRecentProductData started for Machine={MachineId}", MachineId);
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
string sql = $@"SELECT SUM(op_qty.QUANTITY) AS QUANTITY, SUM(uom.WEIGHT_NETTO * op_qty.QUANTITY) AS QUANTITY_KG, prod.PRODUCT_ID, prod.PRODUCT_NR, MIN(operation.STARTED_DATE) AS STARTED_DATE,
MAX(operation.FINISHED_DATE) AS FINISHED_DATE
FROM QMES_FO_MACHINE mach LEFT OUTER JOIN
QMES_WIP_OPERATION operation ON mach.MACHINE_ID = operation.MACHINE_ID LEFT OUTER JOIN
QMES_WIP_ORDER ord ON ord.ORDER_ID = operation.ORDER_ID LEFT OUTER JOIN
QMES_WIP_OPERATION_QTY op_qty ON op_qty.OPERATION_ID = operation.OPERATION_ID LEFT OUTER JOIN
QCM_PRODUCTS prod ON prod.PRODUCT_ID = op_qty.PRODUCT_ID LEFT OUTER JOIN
QCM_PACKAGE_HEADERS pack ON pack.PRODUCT_ID = prod.PRODUCT_ID LEFT OUTER JOIN
QCM_PACKAGE_LEVELS uom ON uom.PACKAGE_ID = pack.PACKAGE_ID
WHERE (operation.OPERATION_TYPE_ID = 11) AND (uom.LEVEL_NR = 0) AND (operation.STARTED_DATE > :StartDate) AND (mach.MACHINE_ID = {MachineId})
GROUP BY prod.PRODUCT_ID, prod.PRODUCT_NR";
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(sql, Con);
OracleParameter[] parameters = new OracleParameter[]
{
new OracleParameter("StartDate", Utilities.GetStartDate()),
};
Command.Parameters.AddRange(parameters);
var reader = Command.ExecuteReader();
Logger.Debug("GetRecentProductData finished");
return(reader);
}
public void InsertProductInList(int list_id, int product_id, int quantity, Connection connection)
{
try
{
if (connection._connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
var command = new Oracle.ManagedDataAccess.Client.OracleCommand("insert_product_info", connection._connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("list_id", OracleDbType.Int32, System.Data.ParameterDirection.Input).Value = list_id;
command.Parameters.Add("product_id", OracleDbType.Int32, System.Data.ParameterDirection.Input).Value = product_id;
command.Parameters.Add("quantity_2", OracleDbType.Int32, System.Data.ParameterDirection.Input).Value = quantity;
command.ExecuteNonQuery();
Oracle.ManagedDataAccess.Client.OracleDataReader reader = command.ExecuteReader();
connection.Close();
}
catch (Exception e)
{
connection.Close();
throw;
}
}
private OracleDataReader GetRecentBoxesScans(int MachineId, OracleConnection Con)
{
Logger.Debug("GetRecentBoxesScans started for Machine={MachineId}", MachineId);
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
string str = $@"SELECT scan.MACHINE_ID, to_char(scan.C_DATE, 'HH24') AS SCAN_HOUR, to_char(scan.C_DATE, 'YYYY-MM-DD') AS SCAN_DAY, SUM(scan.SCAN_COUNT) AS QUANTITY, uom.BU_QUANTITY, SUM(scan.ERROR_COUNT) AS ERROR,
scan.EAN_TYPE, op_qty.PRODUCT_ID, prod.PRODUCT_NR
FROM QMES_WIP_SCAN_COUNT scan LEFT OUTER JOIN
QMES_WIP_OPERATION_QTY op_qty ON op_qty.OPERATION_ID = scan.OPERATION_ID LEFT OUTER JOIN
QCM_PRODUCTS prod ON prod.PRODUCT_ID = op_qty.PRODUCT_ID LEFT OUTER JOIN
QCM_PACKAGE_HEADERS pack ON pack.PRODUCT_ID = prod.PRODUCT_ID LEFT OUTER JOIN
QCM_PACKAGE_LEVELS uom ON uom.PACKAGE_ID = pack.PACKAGE_ID
WHERE (scan.C_DATE >= :StartDate) AND (scan.MACHINE_ID = {MachineId}) AND (scan.EAN_TYPE=1) AND (uom.LEVEL_NR = 1)
GROUP BY scan.MACHINE_ID, to_char(scan.C_DATE, 'HH24'), to_char(scan.C_DATE, 'YYYY-MM-DD'), scan.EAN_TYPE, op_qty.PRODUCT_ID, prod.PRODUCT_NR, uom.BU_QUANTITY
ORDER BY SCAN_DAY DESC, SCAN_HOUR DESC";
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(str, Con);
OracleParameter[] parameters = new OracleParameter[]
{
new OracleParameter("StartDate", Utilities.GetStartDate()),
};
Command.Parameters.AddRange(parameters);
var reader = Command.ExecuteReader();
Logger.Debug("GetRecentBoxesScans ended");
return(reader);
}
private void btnLoad_Click(object sender, EventArgs e)
{
try
{
dgvPreview.Columns.Clear();
oraClient.OracleCommand cmd = oracleConnection.CreateCommand();
cmd.CommandText = txtWGStatement.Text;
using (oraClient.OracleDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
DataTable dataTable = new DataTable();
dataTable.Load(reader);
dgvPreview.DataSource = dataTable;
}
reader.Close();
reader.Dispose();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error in btnLoad_Click()", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void insert_into_common_table(Connection connection, int id_store, int id_client, int dist)
{
try
{
if (connection._connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
var command = new Oracle.ManagedDataAccess.Client.OracleCommand("insert_into_distance_table", connection._connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("store_id", OracleDbType.Int32, System.Data.ParameterDirection.Input).Value = id_store;
command.Parameters.Add("client_id", OracleDbType.Int32, System.Data.ParameterDirection.Input).Value = id_client;
command.Parameters.Add("distance", OracleDbType.Int32, System.Data.ParameterDirection.Input).Value = dist;
command.ExecuteNonQuery();
Oracle.ManagedDataAccess.Client.OracleDataReader reader = command.ExecuteReader();
connection.Close();
}
catch (Exception e)
{
connection.Close();
throw;
}
}
public int getMaxIdClient(Connection connection)
{
if (connection._connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
var command = new Oracle.ManagedDataAccess.Client.OracleCommand("get_total_price1", connection._connection);
string city = textBox1.Text;
command.CommandText = "select max(clientID) from addressC";
command.CommandType = CommandType.Text;
OracleDataReader dr = command.ExecuteReader();
int index = 0;
if (dr.HasRows)
{
while (dr.Read())
{
index = dr.GetInt32(0);
}
}
int ok = index;
connection.Close();
return(index);
}
public virtual List <T> ExecuteReader <T>(CommandType cmdType,
string cmdText, System.Func <IDataReader, T> transform, params OracleParameter[] commandParameters)
{
var myList = new List <T>();
using (var connection = new OracleConnection(ConnectionString))
{
connection.Open();
using (var command = new OracleCommand(cmdText, connection))
{
command.CommandType = cmdType;
if (commandParameters != null)
{
command.Parameters.AddRange(commandParameters);
}
// Since none of the rows are likely to be large, we will execute this without specifying a CommandBehavior
// This will cause the default (non-sequential) access mode to be used
using (var reader = command.ExecuteReader())
{
//if (reader.Read())
//{
while (reader.Read())
{
myList.Add(transform(reader));
}
//}
}
}
}
return(myList);
}
public IHttpActionResult GetMachine(int Id)
{
try
{
if (RuntimeSettings.MockServer)
{
string status = Id % 2 == 0 ? "PR" : "ST";
Machine machine = new Machine {
Id = Id, Name = $"Linia {Id}", State = status, Type = 3, VisibleInAPS = true
};
return(Ok(machine));
}
else
{
using (OracleConnection Con = new Oracle.ManagedDataAccess.Client.OracleConnection(Static.Secrets.ApiConnectionString))
{
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
string str = $@"SELECT MACHINE_ID, MACHINE_NR, STATE, MACHINE_TYPE_ID, IS_VISIBLE_APS
FROM QMES_FO_MACHINE
WHERE MACHINE_ID = {Id}";
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(str, Con);
var reader = Command.ExecuteReader();
if (reader.HasRows)
{
Machine m = new Machine();
while (reader.Read())
{
m.Id = Convert.ToInt32(reader[reader.GetOrdinal("MACHINE_ID")].ToString());
m.Name = reader[reader.GetOrdinal("MACHINE_NR")].ToString();
m.State = reader[reader.GetOrdinal("STATE")].ToString();
m.Type = Convert.ToInt32(reader[reader.GetOrdinal("MACHINE_TYPE_ID")].ToString());
m.VisibleInAPS = reader[reader.GetOrdinal("IS_VISIBLE_APS")].ToString() == "T" ? true : false;
}
Logger.Info("GetMachines: Sukces, zwracam maszynę {Id}", m.Id);
return(Ok(m));
}
else
{
Logger.Info("GetMachines: Porażka, nie znaleziono maszyny {Id}", Id);
return(NotFound());
}
}
}
}
catch (Exception ex)
{
Logger.Error("Błąd w GetMachine. ID:{Id}, Szczegóły: {Message}", Id, ex.ToString());
return(InternalServerError(ex));
}
}
private async Task <List <Operation2Product> > _GetOperation2Product(string operationNumbers)
{
try
{
using (OracleConnection Con = new Oracle.ManagedDataAccess.Client.OracleConnection(Static.Secrets.ApiConnectionString))
{
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
string str = $@"SELECT DISTINCT op.OPERATION_ID, op.OPERATION_NR, pr.PRODUCT_ID, pr.PRODUCT_NR, pr.SUB_PROD_TYPE
FROM QMES_WIP_OPERATION op LEFT OUTER JOIN
QMES_WIP_ORDER2PRODUCT o2p ON o2p.OPERATION_ID = op.OPERATION_ID LEFT OUTER JOIN
QCM_PRODUCTS pr ON pr.PRODUCT_ID = o2p.PRODUCT_ID
WHERE (op.OPERATION_NR IN ({operationNumbers})) AND (pr.SUB_PROD_TYPE IN ('WR', 'PP'))
ORDER BY op.OPERATION_ID, pr.SUB_PROD_TYPE DESC";
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(str, Con);
var reader = Command.ExecuteReader();
List <Operation2Product> Items = new List <Operation2Product>();
if (reader.HasRows)
{
while (reader.Read())
{
Operation2Product o = new Operation2Product();
o.OPERATION_ID = Convert.ToInt32(reader["OPERATION_ID"].ToString());
o.OPERATION_NR = reader["OPERATION_NR"].ToString();
o.PRODUCT_ID = Convert.ToInt32(reader["PRODUCT_ID"].ToString());
o.PRODUCT_NR = reader["PRODUCT_NR"].ToString();
o.SUB_PROD_TYPE = reader["SUB_PROD_TYPE"].ToString();
Items.Add(o);
}
}
else
{
}
return(Items);
}
}
catch (Exception ex)
{
throw;
}
}
private void button1_Click(object sender, EventArgs e)
{
string name = textBoxName.Text;
string surname = textBoxSurname.Text;
Connection connection = new Connection();
if (connection._connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
var command = new Oracle.ManagedDataAccess.Client.OracleCommand("GETUSERORDERS1", connection._connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("nume", OracleDbType.Varchar2, System.Data.ParameterDirection.Input).Value = name;
command.Parameters.Add("prenume", OracleDbType.Varchar2, System.Data.ParameterDirection.Input).Value = surname;
Oracle.ManagedDataAccess.Client.OracleParameter p_rc = command.Parameters.Add("rc", OracleDbType.RefCursor,
DBNull.Value,
System.Data.ParameterDirection.Output);
List <Order> orders_list = new List <Order>();
// Oracle.ManagedDataAccess.Client.OracleParameter output = command.Parameters.Add("l_cursor", OracleDbType.RefCursor);
// output.Direction = System.Data.ParameterDirection.ReturnValue;
command.ExecuteNonQuery();
// Oracle.ManagedDataAccess.Client.OracleDataReader reader = command.ExecuteReader();
// Dim reader As OracleDataReader
DbDataReader reader =
command.ExecuteReader(CommandBehavior.SequentialAccess);
// var reader = command.Parameters("rc").Value;
while (reader.Read())
{
Order order = new Order(reader.GetInt32(0));
orders_list.Add(order);
}
connection.Close();
seeOrdersForm seeOrdersForm = new seeOrdersForm(connection, orders_list);
}
public List <Products_Info> GetDbProductsList(Connection connection, int id)
{
try
{
if (connection._connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
var command = new Oracle.ManagedDataAccess.Client.OracleCommand("returnCartPro", connection._connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
List <Products_Info> products_Info_list = new List <Products_Info>();
command.Parameters.Add("ID_INPUT", OracleDbType.Int32, System.Data.ParameterDirection.Input).Value = id;
Oracle.ManagedDataAccess.Client.OracleParameter p_rc = command.Parameters.Add("rc", OracleDbType.RefCursor,
DBNull.Value,
System.Data.ParameterDirection.Output);
// Oracle.ManagedDataAccess.Client.OracleParameter output = command.Parameters.Add("rc", OracleDbType.RefCursor);
// output.Direction = System.Data.ParameterDirection.ReturnValue;
command.ExecuteNonQuery();
Oracle.ManagedDataAccess.Client.OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Products_Info products_Info = new Products_Info(
reader.GetInt32(0),
reader.GetString(1),
reader.GetInt32(2),
reader.GetInt32(3),
reader.GetString(4),
reader.GetString(5));
products_Info_list.Add(products_Info);
}
connection.Close();
return(products_Info_list);
// return null;
}
catch (Exception e)
{
connection.Close();
throw;
}
}
public IHttpActionResult GetEntry(string id)
{
string ConStr = Static.Secrets.ApiConnectionString;
var Con = new Oracle.ManagedDataAccess.Client.OracleConnection(ConStr);
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
string str = string.Format("SELECT * FROM ifc.qmes_tpm_repairs_imp WHERE order_nr = '{0}'", id);
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(str, Con);
var reader = Command.ExecuteReader();
Process p = new Process();
if (reader.HasRows)
{
while (reader.Read())
{
p = new Process();
p.Number = reader[reader.GetOrdinal("order_nr")].ToString();
p.Manager = reader[reader.GetOrdinal("manager_nr")].ToString();
p.StartDate = null;
p.EndDate = null;
p.FinishedBy = reader[reader.GetOrdinal("closemean_nr")].ToString();
p.InitialDiagnosis = reader[reader.GetOrdinal("initial_diagnosis")].ToString();
p.RepairActions = reader[reader.GetOrdinal("repair_actions")].ToString();
p.Status = reader[reader.GetOrdinal("STATUS")].ToString();
p.IsAdjustment = reader[reader.GetOrdinal("IS_ADJUSTMENT")].ToString();
p.ReasonCode2 = reader[reader.GetOrdinal("REASONCODE2")].ToString();
p.ReasonCode3 = reader[reader.GetOrdinal("REASONCODE3")].ToString();
}
return(Ok(p));
}
else
{
return(NotFound());
}
}
public static List <ProductMachineEfficiency> GetProductMachineEfficiencies(string ProductIds = null)
{
List <ProductMachineEfficiency> Items = new List <ProductMachineEfficiency>();
try
{
using (OracleConnection Con = new Oracle.ManagedDataAccess.Client.OracleConnection(Static.Secrets.ApiConnectionString))
{
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
string sql = $@"SELECT PRODUCT_ID, MACHINE_ID, CAST(EFFICIENCY AS INT) AS EFFICIENCY, CAST(MAX_EFFICIENCY AS INT) AS MAX_EFFICIENCY FROM QMES_FO_MACHINE_EFFICIENCY";
if (!string.IsNullOrEmpty(ProductIds))
{
sql += $" WHERE PRODUCT_ID IN({ ProductIds})";
}
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(sql, Con);
var reader = Command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
ProductMachineEfficiency ef = new ProductMachineEfficiency();
ef.PRODUCT_ID = Convert.ToInt32(reader["PRODUCT_ID"].ToString());
ef.MACHINE_ID = Convert.ToInt32(reader["MACHINE_ID"].ToString());
ef.EFFICIENCY = Convert.ToInt32(reader["EFFICIENCY"].ToString());
ef.MAX_EFFICIENCY = Convert.ToInt32(reader["MAX_EFFICIENCY"].ToString());
Items.Add(ef);
}
}
}
return(Items);
}
catch (Exception ex)
{
throw;
}
}
private void previousOrdersBtn_Click(object sender, EventArgs e)
{
string[] words = greetingLabel.Text.Split(' ');
string name = words[1];
string surname = words[2];
Connection connection = new Connection();
if (connection._connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
var command = new Oracle.ManagedDataAccess.Client.OracleCommand("GETUSERORDERS1", connection._connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("nume", OracleDbType.Varchar2, System.Data.ParameterDirection.Input).Value = name;
command.Parameters.Add("prenume", OracleDbType.Varchar2, System.Data.ParameterDirection.Input).Value = surname;
List <Order> orders_list = new List <Order>();
Oracle.ManagedDataAccess.Client.OracleParameter output = command.Parameters.Add("l_cursor", OracleDbType.RefCursor);
output.Direction = System.Data.ParameterDirection.ReturnValue;
command.ExecuteNonQuery();
Oracle.ManagedDataAccess.Client.OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Order order = new Order(reader.GetInt32(0));
orders_list.Add(order);
}
connection.Close();
seeOrdersForm seeOrdersForm = new seeOrdersForm(connection, orders_list);
}
public ActionResult Lister()
{
User user = (User)Session["User"];
Oracle.ManagedDataAccess.Client.OracleCommand ObjSelct = new Oracle.ManagedDataAccess.Client.OracleCommand("select * from QUESTIONS", user.connexion);
Oracle.ManagedDataAccess.Client.OracleDataReader ObjeRead = ObjSelct.ExecuteReader();
List <Question> q = new List <Question>();
while (ObjeRead.Read())
{
Question QQ = new Question();
QQ.Id = ObjeRead.GetString(0);
QQ.Enoncer = ObjeRead.GetString(1);
QQ.flag = ObjeRead.GetString(2);
QQ.Dificulter = ObjeRead.GetString(3);
q.Add(QQ);
}
ObjeRead.Close();
return(View(q));
}
public bool OrderExists(string id)
{
string ConStr = Static.Secrets.OracleConnectionString;
var Con = new Oracle.ManagedDataAccess.Client.OracleConnection(ConStr);
bool Existent = false;
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
string str = string.Format("SELECT * FROM QMES_WIP_ORDER WHERE ORDER_NR='{0}'", id);
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(str, Con);
var reader = Command.ExecuteReader();
if (reader.HasRows)
{
Existent = true;
}
return(Existent);
}
/// <summary>
/// Executes the query.
/// </summary>
/// <typeparam name="TypeDataSet">The data type to examine.</typeparam>
/// <param name="dataSet">The data set to return containing the data.</param>
/// <param name="tables">The data tables names to return.</param>
/// <param name="queryText">The query text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>The sql command containing any return values.</returns>
public DbCommand ExecuteClientQuery <TypeDataSet>(ref TypeDataSet dataSet, string[] tables,
string queryText, CommandType commandType, string connectionString, params DbParameter[] values)
where TypeDataSet : System.Data.DataSet, new()
{
// Initial connection objects.
OracleClient.OracleCommand sqlCommand = null;
OracleClient.OracleConnection connection = null;
IDataReader dataReader = null;
try
{
// Create a new connection.
using (connection = new OracleClient.OracleConnection(connectionString))
{
// Open the connection.
connection.Open();
// Create the command and assign any parameters.
sqlCommand = new OracleClient.OracleCommand(queryText, connection);
sqlCommand.CommandType = commandType;
if (values != null)
{
foreach (OracleClient.OracleParameter sqlParameter in values)
{
sqlCommand.Parameters.Add(sqlParameter);
}
}
// Load the data into the table.
using (dataReader = sqlCommand.ExecuteReader())
{
dataSet = new TypeDataSet();
dataSet.EnforceConstraints = false;
dataSet.Load(dataReader, LoadOption.OverwriteChanges, tables);
dataReader.Close();
}
// Close the database connection.
connection.Close();
}
// Return the sql command, including
// any parameters that have been
// marked as output direction.
return(sqlCommand);
}
catch (Exception ex)
{
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
if (connection != null)
{
connection.Close();
}
}
}
/// <summary>
/// Executes the query.
/// </summary>
/// <param name="dataSet">The data set to return containing the data.</param>
/// <param name="tables">The datatable schema to add.</param>
/// <param name="queryText">The query text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>The sql command containing any return values.</returns>
public DbCommand ExecuteQuery(ref System.Data.DataSet dataSet, DataTable[] tables, string queryText,
CommandType commandType, string connectionString, params DbParameter[] values)
{
// Initial connection objects.
DbCommand dbCommand = null;
OracleClient.OracleConnection orlConnection = null;
IDataReader dataReader = null;
try
{
// Create a new connection.
using (orlConnection = new OracleClient.OracleConnection(connectionString))
{
// Open the connection.
orlConnection.Open();
// Create the command and assign any parameters.
dbCommand = new OracleClient.OracleCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
ConnectionContext.ConnectionDataType.OracleDataType, queryText), orlConnection);
dbCommand.CommandType = commandType;
if (values != null)
{
foreach (OracleClient.OracleParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Load the data into the table.
using (dataReader = dbCommand.ExecuteReader())
{
dataSet = new System.Data.DataSet();
dataSet.Tables.AddRange(tables);
dataSet.EnforceConstraints = false;
dataSet.Load(dataReader, LoadOption.OverwriteChanges, tables);
dataReader.Close();
}
// Close the database connection.
orlConnection.Close();
}
// Return the sql command, including
// any parameters that have been
// marked as output direction.
return(dbCommand);
}
catch (Exception ex)
{
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
if (orlConnection != null)
{
orlConnection.Close();
}
}
}
public IHttpActionResult GetMachines(int?Type = null, bool?VisibleInAPS = null)
{
try
{
if (RuntimeSettings.MockServer)
{
List <Machine> Machines = new List <Machine>();
for (int i = 1; i < 11; i++)
{
string status = i % 2 == 0 ? "PR" : "ST";
Machines.Add(new Machine {
Id = i, Name = $"Linia {i}", State = status, Type = 3, VisibleInAPS = true
});
}
Logger.Info("GetMachines: Sukces, zwracam {count} maszyn", Machines.Count);
return(Ok(Machines));
}
else
{
using (OracleConnection Con = new Oracle.ManagedDataAccess.Client.OracleConnection(Static.Secrets.ApiConnectionString))
{
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
string str = @"SELECT MACHINE_ID, MACHINE_NR, STATE, MACHINE_TYPE_ID, IS_VISIBLE_APS
FROM QMES_FO_MACHINE
ORDER BY MACHINE_NR";
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(str, Con);
var reader = Command.ExecuteReader();
List <Machine> Machines = new List <Machine>();
if (reader.HasRows)
{
while (reader.Read())
{
Machine m = new Machine();
m.Id = Convert.ToInt32(reader[reader.GetOrdinal("MACHINE_ID")].ToString());
m.Name = reader[reader.GetOrdinal("MACHINE_NR")].ToString();
m.State = reader[reader.GetOrdinal("STATE")].ToString();
m.Type = Convert.ToInt32(reader[reader.GetOrdinal("MACHINE_TYPE_ID")].ToString());
m.VisibleInAPS = reader[reader.GetOrdinal("IS_VISIBLE_APS")].ToString() == "T" ? true : false;
Machines.Add(m);
}
if (Type != null)
{
Machines = Machines.Where(m => m.Type == (int)Type).ToList();
}
if (VisibleInAPS != null)
{
Machines = Machines.Where(m => m.VisibleInAPS == (bool)VisibleInAPS).ToList();
}
Logger.Info("GetMachines: Sukces, zwracam {count} maszyn", Machines.Count);
return(Ok(Machines));
}
else
{
Logger.Info("GetMachines: Porażka, nie znaleziono maszyn.");
return(NotFound());
}
}
}
}
catch (Exception ex)
{
Logger.Error("GetMachines: Błąd. Szczegóły: {Message}", ex.ToString());
return(InternalServerError(ex));
}
}
private async Task <List <PlannedComponent> > _GetPlannedComponents(string query = null)
{
try
{
List <PlannedComponent> Plan = new List <PlannedComponent>();
using (OracleConnection Con = new Oracle.ManagedDataAccess.Client.OracleConnection(Static.Secrets.ApiConnectionString))
{
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
//make sure operation date is always indicated
//otherwise we can crash the db
if (query != null)
{
if (!query.Contains("OPERATION_DATE") && !query.Contains("OPERATION_WEEK") && !query.Contains("OPERATION_YEAR"))
{
query += $" AND (OPERATION_WEEK = {DateTime.Now.IsoWeekOfYear()}) AND (OPERATION_YEAR = {DateTime.Now.Year})";
}
}
else
{
query = $"(OPERATION_WEEK = {DateTime.Now.IsoWeekOfYear()}) AND (OPERATION_YEAR = {DateTime.Now.Year})";
}
string str = $@"SELECT OPERATION_DATE, OPERATION_DAY, OPERATION_WEEK, OPERATION_YEAR, SHIFT_ID, SHIFT_NAME, MACHINE_NR, OPERATION_NR, OPERATION_TYPE_NAME,
ORDER_NR, PRODUCT_NR, PRODUCT_NAME, PROD_TYPE, SUB_PROD_TYPE, ORDER_TYPE_CODE, ORDER_TYPE_NAME, BOM_NR, PRODUCT_QUANTITY, PRODUCT_QUANTITY_ALL
FROM QMESV_GRID_WIP_PROD_SUPPLY
WHERE {query}
ORDER BY OPERATION_DATE, SHIFT_ID, ORDER_NR";
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(str, Con);
var reader = Command.ExecuteReader();
//List<PlannedComponent> Plan = new List<PlannedComponent>();
if (reader.HasRows)
{
while (reader.Read())
{
PlannedComponent c = new PlannedComponent();
c.OPERATION_DATE = Convert.ToDateTime(reader["OPERATION_DATE"].ToString());
c.OPERATION_DAY = Convert.ToDateTime(reader["OPERATION_DAY"].ToString());
c.OPERATION_WEEK = Convert.ToInt32(reader["OPERATION_WEEK"].ToString());
c.OPERATION_YEAR = Convert.ToInt32(reader["OPERATION_YEAR"].ToString());
c.SHIFT_ID = Convert.ToInt32(reader["SHIFT_ID"].ToString());
c.SHIFT_NAME = reader["SHIFT_NAME"].ToString();
c.MACHINE_NR = reader["MACHINE_NR"].ToString();
c.OPERATION_NR = reader["OPERATION_NR"].ToString();
c.OPERATION_TYPE_NAME = reader["OPERATION_TYPE_NAME"].ToString();
c.ORDER_NR = reader["ORDER_NR"].ToString();
c.PRODUCT_NR = reader["PRODUCT_NR"].ToString();
c.PRODUCT_NAME = reader["PRODUCT_NAME"].ToString();
c.PROD_TYPE = reader["PROD_TYPE"].ToString();
c.SUB_PROD_TYPE = reader["SUB_PROD_TYPE"].ToString();
c.ORDER_TYPE_CODE = reader["ORDER_TYPE_CODE"].ToString();
c.BOM_NR = reader["BOM_NR"].ToString();
c.PRODUCT_QUANTITY = Convert.ToInt64(reader["PRODUCT_QUANTITY"].ToString());
c.PRODUCT_QUANTITY_ALL = Convert.ToInt64(reader["PRODUCT_QUANTITY_ALL"].ToString());
Plan.Add(c);
}
}
return(Plan);
}
}
catch (Exception ex)
{
Logger.Error("_GetPlannedComponents: Błąd. Szczegóły: {Message}", ex.ToString());
throw;
}
}
public IHttpActionResult GetWarehouseEntries(string query = null)
{
try
{
using (OracleConnection Con = new Oracle.ManagedDataAccess.Client.OracleConnection(Static.Secrets.ApiConnectionString))
{
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
if (query == null)
{
query = $"(pi.C_DATE >= '{DateTime.Now.StartOfWeek().ToString("yyyy-MM-dd HH:mm:ss")}')";
}
else
{
if (!query.Contains("C_DATE"))
{
//make sure start date is always indicated
//otherwise we can crash the db
query += $" AND (pi.C_DATE >= '{DateTime.Now.StartOfWeek().ToString("yyyy-MM-dd HH:mm:ss")}') ";
}
}
string str = $@"SELECT pr.PRODUCT_NR, s.SERIAL_NR, pi.PRODUCTION_ID, pi.LOADUNIT_ID, pi.LOADUNIT_NR, pi.PRODUCT_ID, pi.DATE_PROD, pi.QUANTITY, pi.WEIGHT, pi.LENGTH, pi.WIDTH, pi.HEIGHT, pi.STATUS, pi.C_DATE, pi.LM_DATE
FROM PRODUCTION_ITEMS pi LEFT OUTER JOIN
QCM_PRODUCTS pr ON pr.PRODUCT_ID = pi.PRODUCT_ID LEFT OUTER JOIN
QCM_PROD_SERIALS s ON s.PROD_SERIAL_ID = pi.PROD_SERIAL_ID
WHERE {query}";
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(str, Con);
var reader = Command.ExecuteReader();
List <WarehouseEntry> Entries = new List <WarehouseEntry>();
if (reader.HasRows)
{
while (reader.Read())
{
WarehouseEntry w = new WarehouseEntry();
w.LOADUNIT_ID = Convert.ToInt64(reader["LOADUNIT_ID"].ToString());
w.LOADUNIT_NR = reader["LOADUNIT_NR"].ToString();
w.PRODUCTION_ID = Convert.ToInt64(reader["PRODUCTION_ID"].ToString());
w.PRODUCT_ID = Convert.ToInt64(reader["PRODUCT_ID"].ToString());
w.PRODUCT_NR = reader["PRODUCT_NR"].ToString();
w.SERIAL_NR = reader["SERIAL_NR"].ToString();
w.QUANTITY = Convert.ToInt32(reader["QUANTITY"].ToString());
w.STATUS = reader["STATUS"].ToString();
w.WEIGHT = Convert.ToDouble(reader["WEIGHT"].ToString());
w.WIDTH = Convert.ToInt32(reader["WIDTH"].ToString());
w.LENGTH = Convert.ToInt32(reader["LENGTH"].ToString());
w.HEIGHT = Convert.ToInt32(reader["HEIGHT"].ToString());
w.C_DATE = Convert.ToDateTime(reader["C_DATE"].ToString());
w.LM_DATE = Convert.ToDateTime(reader["LM_DATE"].ToString());
Entries.Add(w);
}
return(Ok(Entries));
}
else
{
Logger.Info("GetWarehouseEntries: Porażka, nie znaleziono nic..");
return(NotFound());
}
}
}
catch (Exception ex)
{
Logger.Error("GetWarehouseEntries: Błąd. Szczegóły: {Message}", ex.ToString());
return(InternalServerError(ex));
}
}
public IHttpActionResult GetShipments(string DeliveryNote = null, string query = null)
{
try
{
using (OracleConnection Con = new Oracle.ManagedDataAccess.Client.OracleConnection(Static.Secrets.ApiConnectionString))
{
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
if (query == null && DeliveryNote == null)
{
query = $"(d.DATE_EMITTED >= '{DateTime.Now.StartOfWeek().ToString("yyyy-MM-dd HH:mm:ss")}')";
}
else if (query != null)
{
if (!query.Contains("DATE_EMITTED"))
{
query += $" AND (d.DATE_EMITTED >= '{DateTime.Now.StartOfWeek().ToString("yyyy-MM-dd HH:mm:ss")}')";
}
}
if (DeliveryNote != null)
{
if (string.IsNullOrEmpty(query))
{
query = $"d.C_ORDER_NR = '{DeliveryNote}'";
}
else
{
query += $" AND d.C_ORDER_NR = '{DeliveryNote}'";
}
}
string str = $@"SELECT d.DOC_ID, d.DOC_TYPE_NR, d.DATE_EMITTED, d.FIRM_ID, d.ADR_STREET, d.ADR_ZIPCODE, d.ADR_CITY, d.C_ORDER_NR, di.DOC_ITEM_ID, di.PRODUCT_ID, pr.PRODUCT_NR, pr.NAME, di.PROD_SERIAL_ID, s.SERIAL_NR, di.QUANTITY, di.WEIGHT, di.WEIGHT_NETTO
FROM DOCUMENTS d LEFT JOIN DOCUMENT_ITEMS di ON d.DOC_ID = di.DOC_ID
LEFT JOIN QCM_PRODUCTS pr ON pr.PRODUCT_ID = di.PRODUCT_ID
LEFT JOIN QCM_PROD_SERIALS s ON s.PROD_SERIAL_ID = di.PROD_SERIAL_ID
WHERE DOC_TYPE_NR='WHD_WZ' AND {query}";
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(str, Con);
var reader = Command.ExecuteReader();
List <Shipment> Shipments = new List <Shipment>();
if (reader.HasRows)
{
while (reader.Read())
{
if (!Shipments.Any(ship => ship.DOC_ID == Convert.ToInt64(reader["DOC_ID"].ToString())))
{
//new shipment
Shipment sh = new Shipment();
sh.DOC_ID = Convert.ToInt64(reader["DOC_ID"].ToString());
sh.DOC_TYPE_NR = reader["DOC_TYPE_NR"].ToString();
sh.C_ORDER_NR = reader["C_ORDER_NR"].ToString();
sh.DATE_EMITTED = Convert.ToDateTime(reader["DATE_EMITTED"].ToString());
sh.FIRM_ID = Convert.ToInt64(reader["FIRM_ID"].ToString());
sh.ADR_STREET = reader["ADR_STREET"].ToString();
sh.ADR_ZIPCODE = reader["ADR_ZIPCODE"].ToString();
sh.ADR_CITY = reader["ADR_CITY"].ToString();
sh.WEIGHT = 0;
sh.WEIGHT_NETTO = 0;
sh.Items = new List <ShipmentItem>();
Shipments.Add(sh);
}
ShipmentItem si = new ShipmentItem();
si.DOC_ID = Convert.ToInt64(reader["DOC_ID"].ToString());
si.DOC_ITEM_ID = Convert.ToInt64(reader["DOC_ITEM_ID"].ToString());
si.PRODUCT_ID = Convert.ToInt64(reader["PRODUCT_ID"].ToString());
si.PRODUCT_NR = reader["PRODUCT_NR"].ToString();
si.NAME = reader["NAME"].ToString();
si.PROD_SERIAL_ID = Convert.ToInt64(reader["PROD_SERIAL_ID"].ToString());
si.SERIAL_NR = reader["SERIAL_NR"].ToString();
si.QUANTITY = Convert.ToInt64(reader["QUANTITY"].ToString());
si.WEIGHT = Convert.ToDouble(reader["WEIGHT"].ToString());
si.WEIGHT_NETTO = Convert.ToDouble(reader["WEIGHT_NETTO"].ToString());
Shipment s = Shipments.Where(sh => sh.DOC_ID == Convert.ToInt64(reader["DOC_ID"].ToString())).FirstOrDefault();
s.WEIGHT += si.WEIGHT;
s.WEIGHT_NETTO += si.WEIGHT_NETTO;
s.Items.Add(si);
}
return(Ok(Shipments));
}
else
{
Logger.Info("GetShipments: Porażka, nie znaleziono nic..");
return(NotFound());
}
}
}
catch (Exception ex)
{
Logger.Error("GetShipments: Błąd. Szczegóły: {Message}", ex.ToString());
return(InternalServerError(ex));
}
}
public IHttpActionResult GetStocks(string query = null)
{
try
{
using (OracleConnection Con = new Oracle.ManagedDataAccess.Client.OracleConnection(Static.Secrets.ApiConnectionString))
{
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
string str = @"SELECT
LU.LOADUNIT_NR,
SPC.IS_BULK,
LU.LOADUNIT_ID,
SP.SP_NR,
SP.SP_ID,
SPC.NAME AS SPC_NAME,
LUC.POSITION_NR,
P.PRODUCT_NR,
P.name,
P.PRODUCT_ID,
LUC.PROD_SERIAL_ID,
PS.SERIAL_NR,
LUC.DATE_EXPIRE,
LUC.BU_QUANTITY,
PKGL_FL.BU_QUANTITY AS MAX_BU_QUANTITY,
PKGL_BU.INFO AS OPAKOWANIE,
PKGH.HAS_FULL_LU,
V_SUM_Q.BU_QUANTITY_LU,
SA.wh_id,
LU.status_quality,
LU.c_Date
FROM
LOAD_UNITS_FAST LU JOIN QWHV_FULFILL_AUX V_SUM_Q ON V_SUM_Q.loadunit_id = LU.LOADUNIT_ID
JOIN LU_CONTENTS_FAST LUC ON LU.LOADUNIT_ID = LUC.LOADUNIT_ID
JOIN QCM_PRODUCTS P ON LUC.PRODUCT_ID = P.PRODUCT_ID
JOIN QCM_PROD_SERIALS PS ON LUC.PRODUCT_ID = PS.PRODUCT_ID AND LUC.PROD_SERIAL_ID = PS.PROD_SERIAL_ID
JOIN STORAGEPLACES SP ON LU.SP_ID = SP.SP_ID
JOIN SP_CLASSES SPC ON SP.SP_CLASS_ID = SPC.SP_CLASS_ID
JOIN QCM_PACKAGE_HEADERS PKGH ON P.PRODUCT_ID = PKGH.PRODUCT_ID
JOIN QCM_PACKAGE_LEVELS PKGL_BU ON PKGH.PACKAGE_ID = PKGL_BU.PACKAGE_ID
JOIN STORAGEAREAS SA ON SA.SA_ID = SP.SA_ID
JOIN SA_CLASSES SAC ON SA.SA_CLASS_ID = SAC.SA_CLASS_ID
LEFT OUTER JOIN QCM_PACKAGE_LEVELS PKGL_FL ON PKGL_FL.PACKAGE_ID = PKGH.PACKAGE_ID
WHERE
PKGL_BU.LEVEL_NR = 0 AND
SAC.IS_STORAGE = 'T' AND
PKGL_BU.status = 'OK' AND
PKGL_FL.is_full_lu = 'T' AND
PKGL_FL.status = 'OK'";
if (query != null)
{
str += $" AND {query}";
}
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(str, Con);
var reader = Command.ExecuteReader();
List <StockPallet> Stocks = new List <StockPallet>();
if (reader.HasRows)
{
while (reader.Read())
{
StockPallet s = new StockPallet();
s.LOADUNIT_NR = reader["LOADUNIT_NR"].ToString();
s.SP_NR = reader["SP_NR"].ToString();
s.PRODUCT_ID = Convert.ToInt64(reader["PRODUCT_ID"].ToString());
s.PRODUCT_NR = reader["PRODUCT_NR"].ToString();
s.NAME = reader["NAME"].ToString();
s.SERIAL_NR = reader["SERIAL_NR"].ToString();
s.DATE_EXPIRE = Convert.ToDateTime(reader["DATE_EXPIRE"].ToString());
s.BU_QUANTITY = Convert.ToInt32(reader["BU_QUANTITY"].ToString());
s.STATUS_QUALITY = Convert.ToInt32(reader["STATUS_QUALITY"].ToString());
s.C_DATE = Convert.ToDateTime(reader["C_DATE"].ToString());
Stocks.Add(s);
}
return(Ok(Stocks));
}
else
{
Logger.Info("GetStocks: Nie znaleziono zapasów");
return(NotFound());
}
}
}
catch (Exception ex)
{
Logger.Error("GetStocks: Błąd. Szczegóły: {Message}", ex.ToString());
return(InternalServerError(ex));
}
}
public IHttpActionResult GetStocksByProduct(string query = null)
{
try
{
using (OracleConnection Con = new Oracle.ManagedDataAccess.Client.OracleConnection(Static.Secrets.ApiConnectionString))
{
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
string qString = "";
if (query != null)
{
qString += $" AND {query}";
}
string str = $@"SELECT COUNT(LU.LOADUNIT_NR) AS LOADUNIT_NR, P.PRODUCT_NR, P.NAME, P.PRODUCT_ID, SUM(LUC.BU_QUANTITY) AS BU_QUANTITY, LU.STATUS_QUALITY
FROM LOAD_UNITS_FAST LU INNER JOIN
QWHV_FULFILL_AUX V_SUM_Q ON V_SUM_Q.LOADUNIT_ID = LU.LOADUNIT_ID INNER JOIN
LU_CONTENTS_FAST LUC ON LU.LOADUNIT_ID = LUC.LOADUNIT_ID INNER JOIN
QCM_PRODUCTS P ON LUC.PRODUCT_ID = P.PRODUCT_ID INNER JOIN
QCM_PROD_SERIALS PS ON LUC.PRODUCT_ID = PS.PRODUCT_ID AND LUC.PROD_SERIAL_ID = PS.PROD_SERIAL_ID INNER JOIN
STORAGEPLACES SP ON LU.SP_ID = SP.SP_ID INNER JOIN
SP_CLASSES SPC ON SP.SP_CLASS_ID = SPC.SP_CLASS_ID INNER JOIN
QCM_PACKAGE_HEADERS PKGH ON P.PRODUCT_ID = PKGH.PRODUCT_ID INNER JOIN
QCM_PACKAGE_LEVELS PKGL_BU ON PKGH.PACKAGE_ID = PKGL_BU.PACKAGE_ID INNER JOIN
STORAGEAREAS SA ON SA.SA_ID = SP.SA_ID INNER JOIN
SA_CLASSES SAC ON SA.SA_CLASS_ID = SAC.SA_CLASS_ID LEFT OUTER JOIN
QCM_PACKAGE_LEVELS PKGL_FL ON PKGL_FL.PACKAGE_ID = PKGH.PACKAGE_ID
WHERE (PKGL_BU.LEVEL_NR = 0) AND (SAC.IS_STORAGE = 'T') AND (PKGL_BU.STATUS = 'OK') AND (PKGL_FL.IS_FULL_LU = 'T') AND (PKGL_FL.STATUS = 'OK') {qString}
GROUP BY P.PRODUCT_NR, P.NAME, P.PRODUCT_ID, LU.STATUS_QUALITY";
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(str, Con);
var reader = Command.ExecuteReader();
List <StockProduct> Stocks = new List <StockProduct>();
if (reader.HasRows)
{
while (reader.Read())
{
StockProduct s = new StockProduct();
s.LOADUNIT_NR = Convert.ToInt32(reader["LOADUNIT_NR"].ToString());
s.PRODUCT_ID = Convert.ToInt64(reader["PRODUCT_ID"].ToString());
s.PRODUCT_NR = reader["PRODUCT_NR"].ToString();
s.NAME = reader["NAME"].ToString();
s.BU_QUANTITY = Convert.ToInt32(reader["BU_QUANTITY"].ToString());
s.STATUS_QUALITY = Convert.ToInt32(reader["STATUS_QUALITY"].ToString());
Stocks.Add(s);
}
return(Ok(Stocks));
}
else
{
Logger.Info("GetStocksByProduct: Nie znaleziono zapasów");
return(NotFound());
}
}
}
catch (Exception ex)
{
Logger.Error("GetStocksByProduct: Błąd. Szczegóły: {Message}", ex.ToString());
return(InternalServerError(ex));
}
}
/// <summary>
/// Executes the query.
/// </summary>
/// <param name="dataTable">The data table to return containing the data.</param>
/// <param name="queryText">The query text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="getSchemaTable">Get the table schema from the database and then load the data. Used when
/// returning data from the database for a particilar table.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>The sql command containing any return values.</returns>
public DbCommand ExecuteQuery(ref DataTable dataTable, string queryText, CommandType commandType,
string connectionString, bool getSchemaTable, params DbParameter[] values)
{
// Initial connection objects.
DbCommand dbCommand = null;
OracleClient.OracleConnection orlConnection = null;
IDataReader dataReader = null;
try
{
// Create a new connection.
using (orlConnection = new OracleClient.OracleConnection(connectionString))
{
// Open the connection.
orlConnection.Open();
// Create the command and assign any parameters.
dbCommand = new OracleClient.OracleCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
ConnectionContext.ConnectionDataType.OracleDataType, queryText), orlConnection);
dbCommand.CommandType = commandType;
if (values != null)
{
foreach (OracleClient.OracleParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Load the data into the table.
using (dataReader = dbCommand.ExecuteReader())
{
// Get the schema from the data because the
// table has not predefined schema
if (getSchemaTable)
{
// Load the table after the schema is
// returned.
dataTable = dataReader.GetSchemaTable();
dataTable = new DataTable();
System.Data.DataSet localDataSet = new System.Data.DataSet();
localDataSet.EnforceConstraints = false;
localDataSet.Tables.Add(dataTable);
dataTable.Load(dataReader);
}
else
{
// Load the data into a table schema.
// Load the data into a table schema.
System.Data.DataSet localDataSet = new System.Data.DataSet();
localDataSet.EnforceConstraints = false;
localDataSet.Tables.Add(dataTable);
dataTable.Load(dataReader);
}
dataReader.Close();
}
// Close the database connection.
orlConnection.Close();
}
// Return the sql command, including
// any parameters that have been
// marked as output direction.
return(dbCommand);
}
catch (Exception ex)
{
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
if (orlConnection != null)
{
orlConnection.Close();
}
}
}
public static List <ProductionPlanItem> GetProductionPlan(string query = null)
{
try
{
using (OracleConnection Con = new Oracle.ManagedDataAccess.Client.OracleConnection(Static.Secrets.ApiConnectionString))
{
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
if (query != null)
{
if (!query.Contains("START_DATE"))
{
//make sure start date is always indicated
//otherwise we can crash the db
query += $" AND (sp.START_DATE >= '{DateTime.Now.StartOfWeek().ToString("yyyy-MM-dd HH:mm:ss")}') AND (ss.STATUS = 'CO') AND (op.STATUS <> 'RG')";
}
query = $"(op.OPERATION_TYPE_ID = 11) AND (uom.LEVEL_NR = 0) AND (uomPal.LEVEL_NR = 3) AND (o2p.ACTION = 'TO_DO') AND (ss.STATUS = 'CO') AND (op.STATUS <> 'RG') AND {query}";
}
else
{
query = $"(op.OPERATION_TYPE_ID = 11) AND (uom.LEVEL_NR = 0) AND (uomPal.LEVEL_NR = 3) AND (o2p.ACTION = 'TO_DO') AND (ss.STATUS = 'CO') AND (op.STATUS <> 'RG') AND (sp.START_DATE >= '{DateTime.Now.StartOfWeek().ToString("yyyy-MM-dd HH:mm:ss")}')";
}
string str = $@"SELECT sp.SCHEDULING_ID, ss.BEGIN_DATE, ss.END_DATE, sp.START_DATE, sp.STOP_DATE, sp.MACHINE_ID, m.MACHINE_NAME, ord.ORDER_NR, op.OPERATION_NR, op.STATUS, pr.PRODUCT_ID, pr.PRODUCT_NR, pr.NAME, o2p.QUANTITY, (uom.WEIGHT_NETTO * o2p.QUANTITY) AS WEIGHT, (o2p.QUANTITY / uomPal.BU_QUANTITY) AS PAL
FROM QMES_EJS_SCHEDULING_POSITION sp
LEFT JOIN QMES_EJS_SCHEDULING_SESSION ss ON ss.SCHEDULING_ID = sp.SCHEDULING_ID
LEFT JOIN QMES_FO_MACHINE m ON m.MACHINE_ID = sp.MACHINE_ID LEFT OUTER JOIN
QMES_WIP_OPERATION op ON op.OPERATION_ID = sp.OPERATION_ID LEFT OUTER JOIN
QMES_WIP_ORDER ord ON ord.ORDER_ID = op.ORDER_ID LEFT OUTER JOIN
QMES_WIP_ORDER2PRODUCT o2p ON o2p.OPERATION_ID = sp.OPERATION_ID LEFT OUTER JOIN
QCM_PRODUCTS pr ON pr.PRODUCT_ID = o2p.PRODUCT_ID LEFT OUTER JOIN
QCM_PACKAGE_HEADERS pack ON pack.PRODUCT_ID = pr.PRODUCT_ID LEFT OUTER JOIN
QMES_WIP_ORDER2PRODUCT o2p ON o2p.OPERATION_ID = sp.OPERATION_ID AND o2p.PRODUCT_ID = pr.PRODUCT_ID LEFT OUTER JOIN
QCM_PACKAGE_LEVELS uom ON uom.PACKAGE_ID = pack.PACKAGE_ID LEFT OUTER JOIN
QCM_PACKAGE_LEVELS uomPal ON uomPal.PACKAGE_ID = pack.PACKAGE_ID
WHERE {query}
ORDER BY sp.START_DATE";
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(str, Con);
var reader = Command.ExecuteReader();
List <ProductionPlanItem> Plan = new List <ProductionPlanItem>();
double pal;
if (reader.HasRows)
{
while (reader.Read())
{
ProductionPlanItem p = new ProductionPlanItem();
p.SCHEDULING_ID = Convert.ToInt32(reader["SCHEDULING_ID"].ToString());
p.BEGIN_DATE = Convert.ToDateTime(reader["BEGIN_DATE"].ToString());
p.END_DATE = Convert.ToDateTime(reader["END_DATE"].ToString());
p.START_DATE = Convert.ToDateTime(reader["START_DATE"].ToString());
p.STOP_DATE = Convert.ToDateTime(reader["STOP_DATE"].ToString());
p.MACHINE_ID = Convert.ToInt32(reader["MACHINE_ID"].ToString());
p.MACHINE_NAME = reader["MACHINE_NAME"].ToString();
p.ORDER_NR = reader["ORDER_NR"].ToString();
p.OPERATION_NR = reader["OPERATION_NR"].ToString();
p.PRODUCT_ID = Convert.ToInt64(reader["PRODUCT_ID"].ToString());
p.PRODUCT_NR = reader["PRODUCT_NR"].ToString();
p.NAME = reader["NAME"].ToString();
p.QUANTITY = Convert.ToInt64(reader["QUANTITY"].ToString());
p.WEIGHT = Convert.ToDouble(reader["WEIGHT"].ToString());
p.STATUS = reader["STATUS"].ToString();
try
{
if (double.TryParse(reader["PAL"].ToString(), out pal))
{
p.PAL = Convert.ToDouble(reader["PAL"].ToString());
}
else
{
p.PAL = 0;
}
}
catch (Exception ex)
{
p.PAL = 0;
}
Plan.Add(p);
}
}
else
{
}
return(Plan);
}
}
catch (Exception ex)
{
throw;
}
}
