public IList <string> selectAllCustomersListORMBis(string dbpath)
{
List <string> custLst = null;
IList <string> lstOutString = new List <string>();
try
{
//var ctx = new SQLiteDatabaseContext(dbpath);
using (var ctx = new SQLiteDatabaseContext(dbpath))
{
custLst = ctx.Database.SqlQuery <string>("SELECT distinct customer from ordini").ToList();
}
lstOutString = new List <string>();
foreach (var cust in custLst)
{
lstOutString.Add("'" + cust + "'");
}
}
catch (Exception ex)
{
Trace.WriteLine("[PERSISTENCE] " + ex.Message);
}
return(lstOutString);
}
// legge una stringa con i codici clienti da graficare
public string ReadCustomerListORM(string dbpath, int n)
{
List <string> lstClienti;
string ret = "Error reading DB";
try
{
//var ctx = new SQLiteDatabaseContext(dbpath);
using (var ctx = new DSS19.SQLiteDatabaseContext(dbpath))
{
lstClienti = ctx.Database.SqlQuery <string>("SELECT distinct customer from ordini").ToList();
}
// legge solo alcuni clienti (si poteva fare tutto nella query)
List <string> lstOutStrings = new List <string>();
Random r = new Random(550);
while (lstOutStrings.Count < n)
{
int randomIndex = r.Next(0, lstClienti.Count); //Choose a random object in the list
lstOutStrings.Add("'" + lstClienti[randomIndex] + "'"); //add it to the new, random list
lstClienti.RemoveAt(randomIndex); //remove to avoid duplicates
}
ret = string.Join(",", lstOutStrings);
}
catch (Exception ex)
{
Trace.WriteLine($"Error: {ex.Message}");
}
Trace.WriteLine(ret);
return(ret);
}
//update ORM
/*public void deleteCustomertORM(string dbpath, string oldCust, string newCust)
* {
* try
* {
* //var ctx = new SQLiteDatabaseContext(dbpath);
* using (var ctx = new SQLiteDatabaseContext(dbpath))
* {
* ctx.Database.ExecuteSqlCommand("DELETE FROM ordini WHERE customer = '" + cust + "');
*
*
*
* }
* }
* catch (Exception ex)
* {
* errorLog("insert ORM" + ex.Message);
* }
* }*/
public string selectCustomerListORMBis(string dbpath, int n)
{
String ret = "";
List <string> custLst = null;
try
{
//var ctx = new SQLiteDatabaseContext(dbpath);
using (var ctx = new SQLiteDatabaseContext(dbpath))
{
custLst = ctx.Database.SqlQuery <string>("SELECT distinct customer from ordini ORDER BY RANDOM() LIMIT " + n).ToList();
}
IList <string> lstOutString = new List <string>();
foreach (var cust in custLst)
{
lstOutString.Add("'" + cust + "'");
}
ret = string.Join(",", lstOutString);
}
catch (Exception ex)
{
Trace.WriteLine("[PERSISTENCE] " + ex.Message);
}
return(ret);
}
public string selectQuantitiesListORM(string dbpath)
{
List <int> quantList;
string ret = "Error reading DB";
try
{
//var ctx = new SQLiteDatabaseContext(dbpath);
using (var ctx = new SQLiteDatabaseContext(dbpath))
{
quantList = ctx.Database.SqlQuery <int>("SELECT quant from ordini limit 100").ToList();
}
// legge solo alcuni clienti (si poteva fare tutto nella query)
ret = string.Join(",", quantList);
Trace.WriteLine(ret);
// Aggiungere tutti gli altri casi. Update, delete, insert ..
}
catch (Exception ex)
{
Trace.WriteLine($"Error: {ex.Message}");
}
return(ret);
}
// Reads an instance from the db
public GAPclass readGAPinstance(string dbpath)
{
int i, j;
List <int> lstCap;
List <double> lstCosts;
try
{
using (var ctx = new SQLiteDatabaseContext(dbpath))
{
lstCap = ctx.Database.SqlQuery <int>("SELECT cap from capacita").ToList();
G.m = lstCap.Count();
G.cap = new int[G.m];
//inizializzo ogni magazzino con la sua capacità
for (i = 0; i < G.m; i++)
{
G.cap[i] = lstCap[i];
}
lstCosts = ctx.Database.SqlQuery <double>("SELECT cost from costi").ToList();
//determino il numero dei clienti
G.n = lstCosts.Count / G.m;
//alloco spazio per matrice dei costi
G.c = new double[G.m, G.n]; //righe e colonne
// array di richieste, ogni cella è la previsione della quantità che verrà richiesta dall'i-esimo customer
G.req = new int[G.n];
//alloco spazio per la sol (array lungo il # di clienti, per ogni cliente ho il riferimento del
//magazzino a cui si rifarà)
G.sol = new int[G.n];
G.solbest = new int[G.n];
//costo massimo della soluzione
G.zub = Double.MaxValue;
//costo minimo della soluzione
G.zlb = Double.MinValue;
//popolo matrice dei costi
for (i = 0; i < G.m; i++)
{
for (j = 0; j < G.n; j++)
{
G.c[i, j] = lstCosts[i * G.n + j];
}
}
for (j = 0; j < G.n; j++)
{
G.req[j] = -1; // placeholder
}
}
}
catch (Exception ex)
{
Trace.WriteLine("[readGAPinstance] Error:" + ex.Message);
}
Trace.WriteLine("Fine lettura dati istanza GAP");
return(G);
}
//update ORM
public void updateCustomertORM(string dbpath, string oldCust, string newCust)
{
try
{
//var ctx = new SQLiteDatabaseContext(dbpath);
using (var ctx = new SQLiteDatabaseContext(dbpath))
{
ctx.Database.ExecuteSqlCommand("UPDATE ordini SET customer = '" + newCust + "' WHERE customer = '" + oldCust + "'");
}
}
catch (Exception ex)
{
errorLog("insert ORM" + ex.Message);
}
}
//insert ORM
public void insertCustomertORM(string dbpath, string cust)
{
try
{
//var ctx = new SQLiteDatabaseContext(dbpath);
using (var ctx = new SQLiteDatabaseContext(dbpath))
{
ctx.Database.ExecuteSqlCommand("INSERT INTO ordini (customer) VALUES ('" + cust + "') ");
}
}
catch (Exception ex)
{
errorLog("insert ORM" + ex.Message);
}
}
// Reads an instance from the db
public void readGAPinstance(string dbOrdinipath)
{
int i, j;
List <int> lstCap;
List <double> lstCosts;
try
{
using (var ctx = new SQLiteDatabaseContext(dbOrdinipath))
{
lstCap = ctx.Database.SqlQuery <int>("SELECT cap from capacita").ToList();
G.m = lstCap.Count();
G.cap = new int[G.m];
for (i = 0; i < G.m; i++)
{
G.cap[i] = lstCap[i];
}
lstCosts = ctx.Database.SqlQuery <double>("SELECT cost from costi").ToList();
G.n = lstCosts.Count / G.m;
G.c = new double[G.m, G.n];
G.req = new int[G.n];
G.sol = new int[G.n];
G.solbest = new int[G.n];
G.zub = Double.MaxValue;
G.zlb = Double.MinValue;
for (i = 0; i < G.m; i++)
{
for (j = 0; j < G.n; j++)
{
G.c[i, j] = lstCosts[i * G.n + j];
}
}
for (j = 0; j < G.n; j++)
{
G.req[j] = -1; // placeholder
}
}
}
catch (Exception ex)
{
Trace.WriteLine("[readGAPinstance] Error:" + ex.Message);
}
Trace.WriteLine("Fine lettura dati istanza GAP");
}
//restituisce un customer random
public string readRandomCustomer()
{
string ret = "Error reading DB";
try
{
//var ctx = new SQLiteDatabaseContext(dbpath);
using (var ctx = new SQLiteDatabaseContext(this.dbpath))
{
ret = ctx.Database.SqlQuery <string>("SELECT distinct customer from ordini ORDER BY RANDOM() LIMIT 1").SingleOrDefault();
}
ret = "'" + ret + "'";
}
catch (Exception ex)
{
Trace.WriteLine($"Error: {ex.Message}");
}
return(ret);
}
public string readCutomerList(string dbpath)
{
List <string> lstOrdini;
string ret = "Error reading DB";
try
{
//var ctx = new SQLiteDatabaseContext(dbpath);
using (var ctx = new SQLiteDatabaseContext(dbpath))
{
lstOrdini = ctx.Database.SqlQuery <string>("SELECT distinct customer from ordini").ToList();
}
ret = string.Join(",", lstOrdini);
}
catch (Exception ex)
{
Trace.WriteLine($"Error: {ex.Message}");
}
return(ret);
}
// restituisce la lista di tutti i customers
public string readCustomerListORM()
{
List <string> lstClienti;
string ret = "Error reading DB";
try
{
//var ctx = new SQLiteDatabaseContext(dbpath);
using (var ctx = new SQLiteDatabaseContext(this.dbpath))
{
lstClienti = ctx.Database.SqlQuery <string>("SELECT distinct customer from ordini").ToList();
}
List <string> lstOutStrings = new List <string>();
lstClienti.ForEach(c => lstOutStrings.Add("'" + c + "'"));
ret = string.Join(",", lstOutStrings);
}
catch (Exception ex)
{
Trace.WriteLine($"Error: {ex.Message}");
}
return(ret);
}