public static List <Company> GetAllCompanies()
{
var list = new List <Company>();
SqlCeCommand sqlCeCommand = new SqlCeCommand("select * from company order by name", DatabaseConnector.DatabaseConnection);
sqlCeCommand.CommandType = CommandType.Text;
SqlCeResultSet sqlCeResultSet = sqlCeCommand.ExecuteResultSet(ResultSetOptions.Scrollable);
if (sqlCeResultSet.HasRows)
{
int ordinal0 = sqlCeResultSet.GetOrdinal("id");
int ordinal1 = sqlCeResultSet.GetOrdinal("name");
int ordinal2 = sqlCeResultSet.GetOrdinal("address");
int ordinal3 = sqlCeResultSet.GetOrdinal("OIB");
sqlCeResultSet.ReadFirst();
list.Add(new Company()
{
Id = sqlCeResultSet.GetInt32(ordinal0),
Name = sqlCeResultSet.GetString(ordinal1),
Address = sqlCeResultSet.GetString(ordinal2),
OIB = sqlCeResultSet.GetString(ordinal3)
});
while (sqlCeResultSet.Read())
{
list.Add(new Company()
{
Id = sqlCeResultSet.GetInt32(ordinal0),
Name = sqlCeResultSet.GetString(ordinal1),
Address = sqlCeResultSet.GetString(ordinal2),
OIB = sqlCeResultSet.GetString(ordinal3)
});
}
}
return(list);
}
public Usuario obtenerUsuario(string userName)
{
con.Open();
Usuario usuario = new Usuario();
string query = "select * from empleat where usu = @user";
SqlCeCommand command = new SqlCeCommand(query, con);
command.Parameters.AddWithValue("@user", userName);
SqlCeResultSet results = command.ExecuteResultSet(ResultSetOptions.Scrollable);
if (results.HasRows)
{
results.ReadFirst();
usuario.idEmpleat = results.GetInt32(0);
usuario.idEmpresa = results.GetInt32(1);
usuario.usu = results.GetString(2);
usuario.nom = results.GetString(4);
usuario.email = results.GetString(5);
return(usuario);
}
con.Close();
return(usuario);
}
// возвращает сканированный товар и количество
public Scan GetScan(int artcode)
{
Scan scan = null;
string selectCommand = @" SELECT artcode, id_gamma, qty FROM scan WHERE artcode = @artcode and id_gamma = @id_gamma";
using (SqlCeConnection connect = new SqlCeConnection(Datasource))
{
connect.Open();
using (SqlCeCommand command = connect.CreateCommand())
{
command.CommandText = selectCommand;
var param = command.Parameters.Add("artcode", SqlDbType.Int);
param.Value = artcode;
param = command.Parameters.Add("id_gamma", SqlDbType.Int);
param.Value = GlobalArea.CurrentEmployee.GammaID;
using (SqlCeResultSet res = command.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (!res.HasRows)
{
return(null);
}
if (!res.ReadFirst())
{
return(null);
}
scan = new Scan((int)res.GetInt32(res.GetOrdinal("artcode")),
(int)res.GetInt32(res.GetOrdinal("id_gamma")),
(int)res.GetInt32(res.GetOrdinal("qty")));
}
}
return(scan);
}
}
public Employee GetEmployee(string barcode)
{
Employee emp = null;
string selectCommand = @" SELECT CONVERT(INT,id_gamma) id_gamma, ename, case when barcode is not null then barcode else '' END barcode FROM Employee WHERE barcode IS NOT NULL AND barcode = CONVERT(NVARCHAR(12), @barcode) ";
using (SqlCeConnection connect = new SqlCeConnection(Datasource))
{
connect.Open();
using (SqlCeCommand command = new SqlCeCommand(selectCommand, connect))
{
var param = command.Parameters.Add("barcode", SqlDbType.NVarChar);
param.Value = barcode;
using (SqlCeResultSet res = command.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (res.ReadFirst())
{
emp = new Employee()
{
GammaID = (int)res.GetInt32(res.GetOrdinal("id_gamma")),
Barcode = (res.IsDBNull(res.GetOrdinal("barcode"))) ? "" : res.GetString(res.GetOrdinal("barcode")),
Name = res.GetString(res.GetOrdinal("ename"))
};
}
}
}
}
return(emp);
}
public static Company GetCompany(int id)
{
string sql = "select id, name, address, OIB from company where id=@id";
Company company = null;
SqlCeCommand cmd = new SqlCeCommand(sql, DatabaseConnector.DatabaseConnection);
cmd.Parameters.AddWithValue("@id", id);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
if (rs.HasRows)
{
int ordId = rs.GetOrdinal("id");
int ordName = rs.GetOrdinal("name");
int ordAddress = rs.GetOrdinal("address");
int ordOIB = rs.GetOrdinal("OIB");
rs.ReadFirst();
company = new Company();
company.Id = rs.GetInt32(ordId);
company.Name = rs.GetString(ordName);
company.Address = rs.GetString(ordAddress);
company.OIB = rs.GetString(ordOIB);
}
return(company);
}
public static int GetTicketCount()
{
string sql = "select * from counter";
try
{
SqlCeCommand cmd = new SqlCeCommand(sql, DatabaseConnector.DatabaseConnection);
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
if (rs.HasRows)
{
int ordticketCounter = rs.GetOrdinal("ticketCounter");
rs.ReadFirst();
int count = rs.GetInt32(ordticketCounter);
return(count);
}
}
catch (Exception e)
{
Logger.Logger.Log(e);
}
return(-1);
}
public ListaPub CarregarListaPublicacao(int idLista)
{
ListaPub lista;
using (SqlCeCommand command = CreateCommand("SELECT * FROM LISTA WHERE ID_LISTA = @ID_LISTA")) {
command.Parameters.AddWithValue("ID_LISTA", idLista);
using (SqlCeResultSet resultSet = command.ExecuteResultSet(ResultSetOptions.None)) {
resultSet.Read();
lista = new ListaPub()
{
IdLista = resultSet.GetInt32(resultSet.GetOrdinal("ORDEM_SORTEIO")),
Nome = resultSet.GetString(resultSet.GetOrdinal("NOME")),
FonteSementeSorteio = resultSet.GetString(resultSet.GetOrdinal("FONTE_SEMENTE")),
SementeSorteio = resultSet.GetInt32(resultSet.GetOrdinal("SEMENTE_SORTEIO")),
Candidatos = new List <CandidatoPub>()
};
}
}
string queryCandidatos = @"
SELECT
CANDIDATO_LISTA.SEQUENCIA_CONTEMPLACAO, CANDIDATO.CPF, CANDIDATO.NOME, QUANTIDADE_CRITERIOS
FROM
CANDIDATO_LISTA
INNER JOIN LISTA ON CANDIDATO_LISTA.ID_LISTA = LISTA.ID_LISTA
INNER JOIN CANDIDATO ON CANDIDATO_LISTA.ID_CANDIDATO = CANDIDATO.ID_CANDIDATO
WHERE LISTA.ID_LISTA = @ID_LISTA AND CANDIDATO_LISTA.SEQUENCIA_CONTEMPLACAO IS NOT NULL
ORDER BY CANDIDATO_LISTA.SEQUENCIA_CONTEMPLACAO
";
using (SqlCeCommand command = CreateCommand(queryCandidatos)) {
command.Parameters.AddWithValue("ID_LISTA", idLista);
using (SqlCeResultSet resultSet = command.ExecuteResultSet(ResultSetOptions.None)) {
while (resultSet.Read())
{
lista.Candidatos.Add(new CandidatoPub {
IdCandidato = resultSet.GetInt32(resultSet.GetOrdinal("SEQUENCIA_CONTEMPLACAO")),
Cpf = resultSet.GetDecimal(resultSet.GetOrdinal("CPF")),
Nome = resultSet.GetString(resultSet.GetOrdinal("NOME")),
QuantidadeCriterios = resultSet.GetInt32(resultSet.GetOrdinal("QUANTIDADE_CRITERIOS"))
});
}
}
}
return(lista);
}
// возвращает одну запись справочника
public Ean GetEan(string barcode)
{
Ean ean = null;
string commandText = @" SELECT top(1) s.artcode, s.ean13, s.names, s.koef, p.qty, s.nds, s.Manufacturer
FROM sprean s
LEFT OUTER JOIN pereuchet p ON s.artcode = p.artcode
WHERE s.ean13 = @barcode
ORDER BY (CASE WHEN p.qty IS NULL THEN 0 ELSE p.qty END) DESC, s.artcode desc ";
using (SqlCeConnection connect = new SqlCeConnection(Datasource))
{
connect.Open();
using (SqlCeCommand command = connect.CreateCommand())
{
command.CommandText = commandText;
command.Parameters.Add("barcode", SqlDbType.NVarChar).Value = barcode;
using (SqlCeResultSet res = command.ExecuteResultSet(ResultSetOptions.Scrollable))
{
try
{
if (res.HasRows)
{
res.ReadFirst();
{
ean = new Ean();
ean.ArtCode = res.GetInt32(0);
ean.Ean13 = res.GetString(1);
ean.Name = res.GetString(2);
ean.Koef = res.GetInt32(3);
ean.ControlQty = res.IsDBNull(4) ? 0 : res.GetInt32(4);
ean.Nds = res.GetInt32(5);
ean.Manufacturer = res.GetString(6);
}
}
}
finally
{
res.Close();
}
}
}
}
return(ean);
}
public List <OutgoingTwilioMessage> GetAllOutboxMessages()
{
List <OutgoingTwilioMessage> outboxMsgs = new List <OutgoingTwilioMessage>();
SqlCeCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = "SELECT COUNT(*) FROM " + TableOutbox;
Int32 count = (Int32)cmd.ExecuteScalar();
if (count == 0)
{
App.logger.Log("DBStore.GetAllOutboxMessages(): no messages to offload");
return(outboxMsgs);
}
// DEBUG
App.logger.Log("DBStore.GetAllOutboxMessages(): offloading " + count + " outbox message(s)");
try
{
//cmd.CommandText = "SELECT TOP (" + maxBatchLoad + ") * FROM " + TableActivations;
cmd.CommandText = "SELECT * FROM " + TableOutbox;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
while (rs.Read())
{
OutgoingTwilioMessage outMsg = new OutgoingTwilioMessage
{
id = rs.GetInt32(0),
Timestamp = rs.GetDateTime(1),
From = rs.GetString(2),
To = rs.GetString(3),
Action = rs.GetString(4),
Method = rs.GetString(5),
Body = rs.GetString(6),
MediaURLs = rs.GetString(7),
Client = rs.GetString(8)
};
// DEBUG
App.logger.Log("DBStore.GetAllOutboxMessages(): message = " + outMsg.ToString());
outboxMsgs.Add(outMsg);
}
}
catch (Exception ex)
{
var message = "! Error in DBStore.GetAllOutboxMessages(): " + ex.Message + "\n" + ex.TargetSite;
App.logger.Log(message);
}
return(outboxMsgs);
}
public ICollection <Lista> CarregarListas()
{
List <Lista> listas = new List <Lista>();
using (SqlCeCommand command = CreateCommand($"SELECT * FROM LISTA ORDER BY ORDEM_SORTEIO")) {
using (SqlCeResultSet resultSet = command.ExecuteResultSet(ResultSetOptions.None)) {
while (resultSet.Read())
{
listas.Add(new Lista {
IdLista = resultSet.GetInt32(resultSet.GetOrdinal("ID_LISTA")),
OrdemSorteio = resultSet.GetInt32(resultSet.GetOrdinal("ORDEM_SORTEIO")),
Nome = resultSet.GetString(resultSet.GetOrdinal("NOME")),
Quantidade = resultSet.GetInt32(resultSet.GetOrdinal("QUANTIDADE")),
Sorteada = resultSet.GetBoolean(resultSet.GetOrdinal("SORTEADA")),
Publicada = resultSet.GetBoolean(resultSet.GetOrdinal("PUBLICADA"))
});
}
}
}
return(listas);
}
public Lista CarregarProximaLista()
{
using (SqlCeCommand command = CreateCommand("SELECT TOP(1) * FROM LISTA WHERE SORTEADA = 0 ORDER BY ORDEM_SORTEIO")) {
using (SqlCeResultSet resultSet = command.ExecuteResultSet(ResultSetOptions.None)) {
if (resultSet.Read())
{
int idLista = resultSet.GetInt32(resultSet.GetOrdinal("ID_LISTA"));
return(new Lista {
IdLista = idLista,
OrdemSorteio = resultSet.GetInt32(resultSet.GetOrdinal("ORDEM_SORTEIO")),
Nome = resultSet.GetString(resultSet.GetOrdinal("NOME")),
Quantidade = resultSet.GetInt32(resultSet.GetOrdinal("QUANTIDADE")),
CandidatosDisponiveis = CandidatosDisponiveisLista(idLista)
});
}
else
{
return(null);
}
}
}
}
protected void btnLogin_Click(object sender, EventArgs e)
{
con.Open();
string query = "select * from empleat where usu = @usuario and psw = @password and dataBaixa IS NOT NULL";
SqlCeCommand command = new SqlCeCommand(query, con);
command.Parameters.AddWithValue("@usuario", txtbUser.Text);
command.Parameters.AddWithValue("@password", txtbPass.Text);
//SqlCeDataReader reader = command.ExecuteReader();
//SqlCeDataReader reader = command.ExecuteResultSet(ResultSetOptions.Scrollable);
SqlCeResultSet results = command.ExecuteResultSet(ResultSetOptions.Scrollable);
if (results.HasRows) //Si el usuario existe lo redireccionamos a la pagina Home
{
results.ReadFirst();
Session["idEmpleat"] = results.GetInt32(0);
Session["idEmpresa"] = results.GetInt32(1);
FormsAuthentication.RedirectFromLoginPage(txtbUser.Text, chkRecuerda.Checked);
}
else //Si el usuario no existe
{
lblMensaje.Text = "Usuario y/o contraseña incorrectos.";
dvMensaje.Visible = true;
txtbUser.Text = "";
txtbPass.Text = "";
txtbUser.Focus();
}
con.Close();
}
private ICollection <Empreendimento> CarregarEmpreendimentos()
{
ObservableCollection <Empreendimento> empreendimentos = new ObservableCollection <Empreendimento>();
using (SqlCeCommand command = CreateCommand($"SELECT * FROM EMPREENDIMENTO ORDER BY ORDEM")) {
using (SqlCeResultSet resultSet = command.ExecuteResultSet(ResultSetOptions.None)) {
while (resultSet.Read())
{
empreendimentos.Add(new Empreendimento {
Ordem = resultSet.GetInt32(resultSet.GetOrdinal("ORDEM")),
Nome = resultSet.GetString(resultSet.GetOrdinal("NOME"))
});
}
}
}
return(empreendimentos);
}
public static TValue SafeGet <TValue>(this SqlCeResultSet self, string columnName, TValue defaultValue = default(TValue))
{
var t = typeof(TValue);
var ordinal = self.GetOrdinal(columnName);
if (self.IsDBNull(ordinal))
{
return(defaultValue);
}
dynamic value;
if (t == typeof(int))
{
value = self.GetInt32(ordinal);
}
else if (t == typeof(long))
{
value = self.GetInt64(ordinal);
}
else if (t == typeof(bool))
{
value = self.GetBoolean(ordinal);
}
else if (t == typeof(object))
{
value = self.GetValue(ordinal);
}
else if (t == typeof(string))
{
value = self.GetString(ordinal);
}
else if (t == typeof(int?) || t == typeof(long?) || t == typeof(bool?))
{
value = self.GetValue(ordinal);
}
else
{
throw new ApplicationException($"{nameof(SafeGet)} does not support type '{t.Name}'!");
}
return(value == null ? defaultValue : (TValue)Convert.ChangeType(value, Nullable.GetUnderlyingType(typeof(TValue)) ?? typeof(TValue)));
}
private void FlushTimings(SqlCeResultSet resultSet)
{
foreach(KeyValuePair<int, List<long>> timingKvp in m_timings)
{
if(timingKvp.Value.Count == 0)
continue;
int funcOrdinal = resultSet.GetOrdinal("FunctionId");
int minOrdinal = resultSet.GetOrdinal("RangeMin");
int maxOrdinal = resultSet.GetOrdinal("RangeMax");
int hitsOrdinal = resultSet.GetOrdinal("HitCount");
for(int t = 0; t < timingKvp.Value.Count; ++t)
{
bool foundBin = true;
long time = timingKvp.Value[t];
if(!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, time))
{
foundBin = false;
}
if(foundBin)
{
resultSet.Read();
var id = resultSet.GetInt32(funcOrdinal);
if(id != timingKvp.Key)
{
if(!resultSet.Read())
{
foundBin = false;
}
}
if(foundBin)
{
var min = resultSet.GetInt64(minOrdinal);
var max = resultSet.GetInt64(maxOrdinal);
if(id != timingKvp.Key || time < min || time > max)
foundBin = false;
}
}
if(foundBin)
{
//we've got a usable bin, increment and move on
var hits = resultSet.GetInt32(hitsOrdinal);
resultSet.SetInt32(hitsOrdinal, hits + 1);
resultSet.Update();
continue;
}
//didn't find a bin, create a new one for this entry
var row = resultSet.CreateRecord();
row[funcOrdinal] = timingKvp.Key;
row[minOrdinal] = time;
row[maxOrdinal] = time;
row[hitsOrdinal] = 1;
resultSet.Insert(row, DbInsertOptions.KeepCurrentPosition);
//we need to bin-merge
//start by seeking to the first record for this function
if(!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, 0.0f))
resultSet.ReadFirst();
else
resultSet.Read();
var mergeId = resultSet.GetInt32(funcOrdinal);
if(mergeId != timingKvp.Key)
resultSet.Read();
mergeId = resultSet.GetInt32(funcOrdinal);
//we know at least one exists, cause we just inserted one
Debug.Assert(mergeId == timingKvp.Key);
//Search for the merge that produces the smallest merged bucket
long lastMin = resultSet.GetInt64(minOrdinal);
int lastHits = resultSet.GetInt32(hitsOrdinal);
bool shouldMerge = resultSet.Read();
//these store all the data about the best merge so far
long smallestRange = long.MaxValue;
long bestMin = 0;
long bestMax = 0;
int mergedHits = 0;
for(int b = 0; b < kTimingBuckets && shouldMerge; ++b)
{
long max = resultSet.GetInt64(maxOrdinal);
long range = max - lastMin;
if(range < smallestRange)
{
smallestRange = range;
bestMin = lastMin;
bestMax = max;
mergedHits = lastHits + resultSet.GetInt32(hitsOrdinal);
}
lastMin = resultSet.GetInt64(minOrdinal);
lastHits = resultSet.GetInt32(hitsOrdinal);
//if this read fails, we have insufficient buckets to bother merging
shouldMerge = resultSet.Read();
}
if(shouldMerge)
{
//seek to the first (lower) bin
resultSet.Seek(DbSeekOptions.FirstEqual, timingKvp.Key, bestMin);
resultSet.Read();
//expand this bin to include the next one
resultSet.SetInt64(maxOrdinal, bestMax);
resultSet.SetInt32(hitsOrdinal, mergedHits);
//go to the now redundant bin
resultSet.Update();
resultSet.Read();
//delete the bin
resultSet.Delete();
}
}
#if FALSE
//DEBUG ONLY HACK: display buckets
if(!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, 0.0f))
resultSet.ReadFirst();
else
resultSet.Read();
var tempId = resultSet.GetInt32(funcOrdinal);
if(tempId != timingKvp.Key)
resultSet.Read();
Console.WriteLine("Buckets for function {0}:", timingKvp.Key);
for(int b = 0; b < kTimingBuckets; ++b)
{
long min = resultSet.GetInt64(minOrdinal);
long max = resultSet.GetInt64(maxOrdinal);
int hits = resultSet.GetInt32(hitsOrdinal);
Console.WriteLine("[{0}, {1}]: {2}", min, max, hits);
resultSet.Read();
}
#endif
}
}
private void FlushTimings(SqlCeResultSet resultSet)
{
foreach (KeyValuePair <int, List <long> > timingKvp in m_timings)
{
if (timingKvp.Value.Count == 0)
{
continue;
}
int funcOrdinal = resultSet.GetOrdinal("FunctionId");
int minOrdinal = resultSet.GetOrdinal("RangeMin");
int maxOrdinal = resultSet.GetOrdinal("RangeMax");
int hitsOrdinal = resultSet.GetOrdinal("HitCount");
for (int t = 0; t < timingKvp.Value.Count; ++t)
{
bool foundBin = true;
long time = timingKvp.Value[t];
if (!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, time))
{
foundBin = false;
}
if (foundBin)
{
resultSet.Read();
var id = resultSet.GetInt32(funcOrdinal);
if (id != timingKvp.Key)
{
if (!resultSet.Read())
{
foundBin = false;
}
}
if (foundBin)
{
var min = resultSet.GetInt64(minOrdinal);
var max = resultSet.GetInt64(maxOrdinal);
if (id != timingKvp.Key || time < min || time > max)
{
foundBin = false;
}
}
}
if (foundBin)
{
//we've got a usable bin, increment and move on
var hits = resultSet.GetInt32(hitsOrdinal);
resultSet.SetInt32(hitsOrdinal, hits + 1);
resultSet.Update();
continue;
}
//didn't find a bin, create a new one for this entry
var row = resultSet.CreateRecord();
row[funcOrdinal] = timingKvp.Key;
row[minOrdinal] = time;
row[maxOrdinal] = time;
row[hitsOrdinal] = 1;
resultSet.Insert(row, DbInsertOptions.KeepCurrentPosition);
//we need to bin-merge
//start by seeking to the first record for this function
if (!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, 0.0f))
{
resultSet.ReadFirst();
}
else
{
resultSet.Read();
}
var mergeId = resultSet.GetInt32(funcOrdinal);
if (mergeId != timingKvp.Key)
{
resultSet.Read();
}
mergeId = resultSet.GetInt32(funcOrdinal);
//we know at least one exists, cause we just inserted one
Debug.Assert(mergeId == timingKvp.Key);
//Search for the merge that produces the smallest merged bucket
long lastMin = resultSet.GetInt64(minOrdinal);
int lastHits = resultSet.GetInt32(hitsOrdinal);
bool shouldMerge = resultSet.Read();
//these store all the data about the best merge so far
long smallestRange = long.MaxValue;
long bestMin = 0;
long bestMax = 0;
int mergedHits = 0;
for (int b = 0; b < kTimingBuckets && shouldMerge; ++b)
{
long max = resultSet.GetInt64(maxOrdinal);
long range = max - lastMin;
if (range < smallestRange)
{
smallestRange = range;
bestMin = lastMin;
bestMax = max;
mergedHits = lastHits + resultSet.GetInt32(hitsOrdinal);
}
lastMin = resultSet.GetInt64(minOrdinal);
lastHits = resultSet.GetInt32(hitsOrdinal);
//if this read fails, we have insufficient buckets to bother merging
shouldMerge = resultSet.Read();
}
if (shouldMerge)
{
//seek to the first (lower) bin
resultSet.Seek(DbSeekOptions.FirstEqual, timingKvp.Key, bestMin);
resultSet.Read();
//expand this bin to include the next one
resultSet.SetInt64(maxOrdinal, bestMax);
resultSet.SetInt32(hitsOrdinal, mergedHits);
//go to the now redundant bin
resultSet.Update();
resultSet.Read();
//delete the bin
resultSet.Delete();
}
}
#if FALSE
//DEBUG ONLY HACK: display buckets
if (!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, 0.0f))
{
resultSet.ReadFirst();
}
else
{
resultSet.Read();
}
var tempId = resultSet.GetInt32(funcOrdinal);
if (tempId != timingKvp.Key)
{
resultSet.Read();
}
Console.WriteLine("Buckets for function {0}:", timingKvp.Key);
for (int b = 0; b < kTimingBuckets; ++b)
{
long min = resultSet.GetInt64(minOrdinal);
long max = resultSet.GetInt64(maxOrdinal);
int hits = resultSet.GetInt32(hitsOrdinal);
Console.WriteLine("[{0}, {1}]: {2}", min, max, hits);
resultSet.Read();
}
#endif
}
}
private void LoadData(ThreadExecuteTask threadExecute)
{
try
{
this.symbolList.Clear();
for (int i = 0; i < countPerPage; i++)
{
//If an abort has been requested, we should quit
if (threadExecute != null && threadExecute.State ==
ThreadExecuteTask.ProcessingState.requestAbort)
{
threadExecute.setProcessingState(ThreadExecuteTask.ProcessingState.aborted);
System.Windows.Forms.MessageBox.Show("aborted");
return;
}
bool ok;
if (i == 0)
{
ok = this.resultSet.ReadAbsolute(this.firstRecordindex);
Debug.Assert(ok, "Failed to seek to position: " + this.firstRecordindex);
}
else
{
ok = resultSet.Read();
}
if (ok)
{
SymbolInfo info = new SymbolInfo();
info.Id = resultSet.GetInt32(0);
object image = resultSet.GetValue(1);
using (MemoryStream ms = new MemoryStream(image as byte[]))
{
info.Image = ResizeImage(new Bitmap(ms), this.imageSize);
}
info.Sound = resultSet.GetValue(2) as byte[];
info.Text = resultSet.GetString(3);
symbolList.Add(info);
}
else
{
break;
}
}
}
catch (Exception e)
{
Debug.WriteLine(e);
}
if (this.DataLoaded != null)
{
this.DataLoaded();
}
}
public frmMain()
{
InitializeComponent();
if (!File.Exists(string.Format("{0}\\MyPersonalIndex\\MPI.sqlite", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData))))
{
MessageBox.Show("Error updating! Please run MyPersonalIndex version 3.0 (and then close it) before using this upgrade program.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
throw new Exception();
}
if (!File.Exists(string.Format("{0}\\MyPersonalIndex\\MPI.sdf", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData))))
{
MessageBox.Show("Error updating! It does not appear you have MyPersonalIndex version 2.0 or later installed.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
throw new Exception();
}
try
{
cnLite = new SQLiteConnection(string.Format("Data Source={0}\\MyPersonalIndex\\MPI.sqlite", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)));
cnCe = new SqlCeConnection(string.Format("Data Source={0}\\MyPersonalIndex\\MPI.sdf", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)));
if (cnCe.State == ConnectionState.Closed)
{
cnCe.Open();
}
if (cnLite.State == ConnectionState.Closed)
{
cnLite.Open();
}
using (SQLiteCommand c = new SQLiteCommand("BEGIN", cnLite))
c.ExecuteNonQuery();
// Portfolios
if (Convert.ToDouble(ExecuteScalar("SELECT Version FROM Settings")) < 2.01)
{
MessageBox.Show("Error updating! Upgrade to version 2.0.1 before running this installer.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
throw new Exception();
}
Dictionary <int, int> portfolioMapping = new Dictionary <int, int>();
using (SqlCeResultSet rs = ExecuteResultSet("SELECT * FROM Portfolios"))
{
foreach (SqlCeUpdatableRecord rec in rs)
{
int ID = rec.GetInt32(rec.GetOrdinal("ID"));
string Name = rec.GetString(rec.GetOrdinal("Name"));
bool Dividends = rec.GetBoolean(rec.GetOrdinal("Dividends"));
bool HoldingsShowHidden = rec.GetBoolean(rec.GetOrdinal("HoldingsShowHidden"));
int CostCalc = rec.GetInt32(rec.GetOrdinal("CostCalc"));
bool NAVSort = rec.GetBoolean(rec.GetOrdinal("NAVSort"));
decimal NAVStartValue = rec.GetDecimal(rec.GetOrdinal("NAVStartValue"));
int AAThreshold = rec.GetInt32(rec.GetOrdinal("AAThreshold"));
bool AAShowBlank = rec.GetBoolean(rec.GetOrdinal("AAShowBlank"));
bool AcctShowBlank = rec.GetBoolean(rec.GetOrdinal("AcctShowBlank"));
bool CorrelationShowHidden = rec.GetBoolean(rec.GetOrdinal("CorrelationShowHidden"));
DateTime StartDate = rec.GetDateTime(rec.GetOrdinal("StartDate"));
using (SQLiteCommand c = new SQLiteCommand("INSERT INTO Portfolios (Description, StartValue, AAThreshold, ThresholdMethod, " +
" CostBasis, StartDate, Dividends, HoldingsShowHidden, AAShowBlank, CorrelationShowHidden, AcctShowBlank, " +
" NAVSortDesc) VALUES (@Description, @StartValue, @AAThreshold, @ThresholdMethod, " +
" @CostBasis, @StartDate, @Dividends, @HoldingsShowHidden, @AAShowBlank, @CorrelationShowHidden, @AcctShowBlank, " +
" @NAVSortDesc)", cnLite))
{
c.Parameters.AddWithValue("@Description", Name);
c.Parameters.AddWithValue("@StartValue", NAVStartValue);
c.Parameters.AddWithValue("@AAThreshold", AAThreshold);
c.Parameters.AddWithValue("@ThresholdMethod", 0);
c.Parameters.AddWithValue("@CostBasis", CostCalc + 1);
c.Parameters.AddWithValue("@StartDate", ConvertDateToJulian(StartDate));
c.Parameters.AddWithValue("@Dividends", Dividends ? 1 : 0);
c.Parameters.AddWithValue("@HoldingsShowHidden", HoldingsShowHidden ? 1 : 0);
c.Parameters.AddWithValue("@AAShowBlank", AAShowBlank ? 1 : 0);
c.Parameters.AddWithValue("@CorrelationShowHidden", CorrelationShowHidden ? 1 : 0);
c.Parameters.AddWithValue("@AcctShowBlank", AcctShowBlank ? 1 : 0);
c.Parameters.AddWithValue("@NAVSortDesc", NAVSort ? 1 : 0);
c.ExecuteNonQuery();
}
portfolioMapping.Add(ID, getIdentity());
}
}
// Asset Allocation
Dictionary <int, int> aaMapping = new Dictionary <int, int>();
using (SqlCeResultSet rs = ExecuteResultSet("SELECT * FROM AA"))
{
foreach (SqlCeUpdatableRecord rec in rs)
{
int ID = rec.GetInt32(rec.GetOrdinal("ID"));
int PortfolioID = rec.GetInt32(rec.GetOrdinal("Portfolio"));
string Description = rec.GetString(rec.GetOrdinal("AA"));
decimal?Target = null;
if (rec.GetValue(rec.GetOrdinal("Target")) != System.DBNull.Value)
{
Target = rec.GetDecimal(rec.GetOrdinal("Target")) / 100;
}
using (SQLiteCommand c = new SQLiteCommand("INSERT INTO AA (PortfolioID, Description, Target) " +
" VALUES (@PortfolioID, @Description, @Target)", cnLite))
{
c.Parameters.AddWithValue("@PortfolioID", portfolioMapping[PortfolioID]);
c.Parameters.AddWithValue("@Description", Description);
c.Parameters.AddWithValue("@Target", Target.HasValue ? Target.Value : (object)System.DBNull.Value);
c.ExecuteNonQuery();
}
aaMapping.Add(ID, getIdentity());
}
}
// Accounts
Dictionary <int, int> acctMapping = new Dictionary <int, int>();
using (SqlCeResultSet rs = ExecuteResultSet("SELECT * FROM Accounts"))
{
foreach (SqlCeUpdatableRecord rec in rs)
{
int ID = rec.GetInt32(rec.GetOrdinal("ID"));
int PortfolioID = rec.GetInt32(rec.GetOrdinal("Portfolio"));
string Description = rec.GetString(rec.GetOrdinal("Name"));
bool OnlyGain = rec.GetBoolean(rec.GetOrdinal("OnlyGain"));
decimal?TaxRate = null;
if (rec.GetValue(rec.GetOrdinal("TaxRate")) != System.DBNull.Value)
{
TaxRate = rec.GetDecimal(rec.GetOrdinal("TaxRate")) / 100;
}
using (SQLiteCommand c = new SQLiteCommand("INSERT INTO Acct (PortfolioID, Description, TaxRate, TaxDeferred, CostBasis) " +
" VALUES (@PortfolioID, @Description, @TaxRate, @TaxDeferred, @CostBasis)", cnLite))
{
c.Parameters.AddWithValue("@PortfolioID", portfolioMapping[PortfolioID]);
c.Parameters.AddWithValue("@Description", Description);
c.Parameters.AddWithValue("@TaxDeferred", !OnlyGain);
c.Parameters.AddWithValue("@CostBasis", 0);
c.Parameters.AddWithValue("@TaxRate", TaxRate.HasValue ? TaxRate.Value : (object)System.DBNull.Value);
c.ExecuteNonQuery();
}
acctMapping.Add(ID, getIdentity());
}
}
// Securities
Dictionary <int, int> securityMapping = new Dictionary <int, int>();
using (SqlCeResultSet rs = ExecuteResultSet("SELECT * FROM Tickers"))
{
foreach (SqlCeUpdatableRecord rec in rs)
{
int ID = rec.GetInt32(rec.GetOrdinal("ID"));
int PortfolioID = rec.GetInt32(rec.GetOrdinal("Portfolio"));
string Ticker = rec.GetString(rec.GetOrdinal("Ticker"));
bool Active = rec.GetBoolean(rec.GetOrdinal("Active"));
int AA = rec.GetInt32(rec.GetOrdinal("AA"));
bool Hide = rec.GetBoolean(rec.GetOrdinal("Hide"));
int Account = rec.GetInt32(rec.GetOrdinal("Acct"));
using (SQLiteCommand c = new SQLiteCommand("INSERT INTO Security (PortfolioID, Symbol, Account, DivReinvest, CashAccount, IncludeInCalc, Hide) " +
" VALUES (@PortfolioID, @Symbol, @Account, @DivReinvest, @CashAccount, @IncludeInCalc, @Hide)", cnLite))
{
c.Parameters.AddWithValue("@PortfolioID", portfolioMapping[PortfolioID]);
c.Parameters.AddWithValue("@Symbol", Ticker);
c.Parameters.AddWithValue("@Account", Account == -1 ? (object)System.DBNull.Value : acctMapping[Account]);
c.Parameters.AddWithValue("@DivReinvest", 0);
c.Parameters.AddWithValue("@CashAccount", Ticker == "$" ? 1 : 0);
c.Parameters.AddWithValue("@IncludeInCalc", Active ? 1 : 0);
c.Parameters.AddWithValue("@Hide", Hide ? 1 : 0);
c.ExecuteNonQuery();
}
securityMapping.Add(ID, getIdentity());
if (AA == -1 || !aaMapping.ContainsKey(AA))
{
continue;
}
using (SQLiteCommand c = new SQLiteCommand("INSERT INTO SecurityAA (SecurityID, AAID, Percent) " +
" VALUES (@SecurityID, @AAID, @Percent)", cnLite))
{
c.Parameters.AddWithValue("@SecurityID", securityMapping[ID]);
c.Parameters.AddWithValue("@AAID", aaMapping[AA]);
c.Parameters.AddWithValue("@Percent", 1);
c.ExecuteNonQuery();
}
}
}
using (SqlCeResultSet rs = ExecuteResultSet("SELECT * FROM Trades WHERE Custom IS NULL"))
{
foreach (SqlCeUpdatableRecord rec in rs)
{
DateTime TradeDate = rec.GetDateTime(rec.GetOrdinal("Date"));
int TickerID = rec.GetInt32(rec.GetOrdinal("TickerID"));
decimal Shares = rec.GetDecimal(rec.GetOrdinal("Shares"));
decimal Price = rec.GetDecimal(rec.GetOrdinal("Price"));
using (SQLiteCommand c = new SQLiteCommand("INSERT INTO SecurityTrades (SecurityID, Type, Value, Price, Frequency, Date) " +
" VALUES (@SecurityID, @Type, @Value, @Price, @Frequency, @Date)", cnLite))
{
c.Parameters.AddWithValue("@SecurityID", securityMapping[TickerID]);
c.Parameters.AddWithValue("@Type", Shares < 0 ? 1 : 0);
c.Parameters.AddWithValue("@Value", Math.Abs(Shares));
c.Parameters.AddWithValue("@Price", Price);
c.Parameters.AddWithValue("@Frequency", 0);
c.Parameters.AddWithValue("@Date", ConvertDateToJulian(TradeDate));
c.ExecuteNonQuery();
}
}
}
using (SqlCeResultSet rs = ExecuteResultSet("SELECT * FROM CustomTrades"))
{
foreach (SqlCeUpdatableRecord rec in rs)
{
string Dates = rec.GetString(rec.GetOrdinal("Dates"));
int TickerID = rec.GetInt32(rec.GetOrdinal("TickerID"));
int TradeType = rec.GetInt32(rec.GetOrdinal("TradeType"));
int Frequency = rec.GetInt32(rec.GetOrdinal("Frequency"));
decimal Value = rec.GetDecimal(rec.GetOrdinal("Value"));
tradeType newTradeType = tradeType.tradeType_Purchase;
tradeFreq newFrequency = tradeFreq.tradeFreq_Once;
List <DateTime> ConvertedDates = new List <DateTime>();
switch ((DynamicTradeFreq)Frequency)
{
case DynamicTradeFreq.Daily:
ConvertedDates.Add(DateTime.MinValue);
newFrequency = tradeFreq.tradeFreq_Daily;
break;
case DynamicTradeFreq.Monthly:
ConvertedDates.Add(new DateTime(2009, 1, Convert.ToInt32(Dates)));
newFrequency = tradeFreq.tradeFreq_Monthly;
break;
case DynamicTradeFreq.Weekly:
ConvertedDates.Add(new DateTime(2009, 1, 4 + Convert.ToInt32(Dates)));
newFrequency = tradeFreq.tradeFreq_Weekly;
break;
case DynamicTradeFreq.Yearly:
ConvertedDates.Add(new DateTime(2009, 1, 1).AddDays(Convert.ToInt32(Dates) - 1));
newFrequency = tradeFreq.tradeFreq_Yearly;
break;
case DynamicTradeFreq.Once:
foreach (string s in Dates.Split('|'))
{
ConvertedDates.Add(new DateTime(Convert.ToInt32(s.Substring(0, 4)), Convert.ToInt32(s.Substring(4, 2)), Convert.ToInt32(s.Substring(6, 2))));
}
newFrequency = tradeFreq.tradeFreq_Once;
break;
}
switch ((DynamicTradeType)TradeType)
{
case DynamicTradeType.AA:
newTradeType = tradeType.tradeType_AA;
break;
case DynamicTradeType.Fixed:
newTradeType = Value < 0 ? tradeType.tradeType_FixedSale : tradeType.tradeType_FixedPurchase;
break;
case DynamicTradeType.Shares:
newTradeType = Value < 0 ? tradeType.tradeType_Sale : tradeType.tradeType_Purchase;
break;
case DynamicTradeType.TotalValue:
newTradeType = tradeType.tradeType_TotalValue;
break;
}
foreach (DateTime d in ConvertedDates)
{
using (SQLiteCommand c = new SQLiteCommand("INSERT INTO SecurityTrades (SecurityID, Type, Value, Frequency, Date) " +
" VALUES (@SecurityID, @Type, @Value, @Frequency, @Date)", cnLite))
{
c.Parameters.AddWithValue("@SecurityID", securityMapping[TickerID]);
c.Parameters.AddWithValue("@Type", (int)newTradeType);
c.Parameters.AddWithValue("@Value", Math.Abs(Value));
c.Parameters.AddWithValue("@Frequency", (int)newFrequency);
c.Parameters.AddWithValue("@Date", d == DateTime.MinValue ? (object)System.DBNull.Value : ConvertDateToJulian(d));
c.ExecuteNonQuery();
}
}
}
}
// Settings
using (SqlCeResultSet rs = ExecuteResultSet("SELECT * FROM Settings"))
{
rs.ReadFirst();
DateTime DataStartDate = rs.GetDateTime(rs.GetOrdinal("DataStartDate"));
int? LastPortfolio = null;
if (rs.GetValue(rs.GetOrdinal("LastPortfolio")) != System.DBNull.Value)
{
LastPortfolio = rs.GetInt32(rs.GetOrdinal("LastPortfolio"));
}
int WindowX = rs.GetInt32(rs.GetOrdinal("WindowX"));
int WindowY = rs.GetInt32(rs.GetOrdinal("WindowY"));
int WindowHeight = rs.GetInt32(rs.GetOrdinal("WindowHeight"));
int WindowWidth = rs.GetInt32(rs.GetOrdinal("WindowWidth"));
int WindowState = rs.GetInt32(rs.GetOrdinal("WindowState"));
bool Splits = rs.GetBoolean(rs.GetOrdinal("Splits"));
bool TickerDiv = rs.GetBoolean(rs.GetOrdinal("TickerDiv"));
using (SQLiteCommand c = new SQLiteCommand("UPDATE Settings SET DataStartDate = @DataStartDate, LastPortfolio = @LastPortfolio, " +
" WindowX = @WindowX, WindowY = @WindowY, WindowHeight = @WindowHeight, WindowWidth = @WindowWidth, " +
" WindowState = @WindowState, Splits = @Splits, CompareIncludeDividends = @CompareIncludeDividends", cnLite))
{
c.Parameters.AddWithValue("@DataStartDate", ConvertDateToJulian(DataStartDate));
c.Parameters.AddWithValue("@LastPortfolio", LastPortfolio.HasValue ? portfolioMapping[LastPortfolio.Value] : (object)System.DBNull.Value);
c.Parameters.AddWithValue("@WindowX", WindowX);
c.Parameters.AddWithValue("@WindowY", WindowY);
c.Parameters.AddWithValue("@WindowHeight", WindowHeight);
c.Parameters.AddWithValue("@WindowWidth", WindowWidth);
c.Parameters.AddWithValue("@WindowState", WindowState);
c.Parameters.AddWithValue("@Splits", Splits ? 1 : 0);
c.Parameters.AddWithValue("@CompareIncludeDividends", TickerDiv ? 1 : 0);
c.ExecuteNonQuery();
}
}
using (SQLiteCommand c = new SQLiteCommand("COMMIT", cnLite))
c.ExecuteNonQuery();
MessageBox.Show("Upgrade successful!", "Success");
}
catch (Exception e)
{
if (cnLite.State == ConnectionState.Open)
{
using (SQLiteCommand c = new SQLiteCommand("ROLLBACK", cnLite))
c.ExecuteNonQuery();
}
MessageBox.Show("Error updating! You can run this program again from the installation folder.\nError:\n" + e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
finally
{
cnCe.Close();
cnLite.Close();
throw new Exception();
}
}
public int Insert()
{
_resultSet.Insert(_record, DbInsertOptions.PositionOnInsertedRow);
return(_resultSet.GetInt32(0));
}
/// <summary>
/// Returns generated Id.
/// </summary>
/// <returns></returns>
public static Int32 Write_Level1(Level1 level1, SqlCeConnection conn)
{
Dictionary <string, Int32> idBag = new Dictionary <string, int>();
// Level1
using (SqlCeCommand cmd = new SqlCeCommand(level1.GetType().Name, conn))
{
cmd.CommandType = System.Data.CommandType.TableDirect;
using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
{
SqlCeUpdatableRecord r = rs.CreateRecord();
r.SetString(1, level1.Value); // Payload
rs.Insert(r, DbInsertOptions.PositionOnInsertedRow);
idBag[level1.GetType().Name] = rs.GetInt32(0);
}
}
foreach (var level2 in level1.levels) // Payload
{
// Level2
using (SqlCeCommand cmd = new SqlCeCommand(level2.GetType().Name, conn))
{
cmd.CommandType = System.Data.CommandType.TableDirect;
using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
{
SqlCeUpdatableRecord r = rs.CreateRecord();
r.SetInt32(GetOrdinal(r, level2.GetType(), level1.GetType()), idBag[level1.GetType().Name]); // foreign key
r.SetString(1, level2.Value); // payload
rs.Insert(r, DbInsertOptions.PositionOnInsertedRow);
idBag[level2.GetType().Name] = rs.GetInt32(0);
}
}
foreach (var level3 in level2.levels)
{
using (SqlCeCommand cmd = new SqlCeCommand(level3.GetType().Name, conn))
{
cmd.CommandType = System.Data.CommandType.TableDirect;
using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
{
SqlCeUpdatableRecord r = rs.CreateRecord();
r.SetInt32(GetOrdinal(r, level3.GetType(), level2.GetType()), idBag[level2.GetType().Name]);
r.SetString(1, level3.Value);
rs.Insert(r, DbInsertOptions.PositionOnInsertedRow);
idBag[level3.GetType().Name] = rs.GetInt32(0);
}
}
foreach (var level4 in level3.levels)
{
using (SqlCeCommand cmd = new SqlCeCommand(level4.GetType().Name, conn))
{
cmd.CommandType = System.Data.CommandType.TableDirect;
using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
{
SqlCeUpdatableRecord r = rs.CreateRecord();
r.SetInt32(GetOrdinal(r, level4.GetType(), level3.GetType()), idBag[level3.GetType().Name]);
r.SetString(1, level4.Value);
rs.Insert(r, DbInsertOptions.PositionOnInsertedRow);
idBag[level4.GetType().Name] = rs.GetInt32(0);
}
}
foreach (var level5 in level4.levels)
{
using (SqlCeCommand cmd = new SqlCeCommand(level5.GetType().Name, conn))
{
cmd.CommandType = System.Data.CommandType.TableDirect;
using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
{
SqlCeUpdatableRecord r = rs.CreateRecord();
r.SetInt32(GetOrdinal(r, typeof(Level5), typeof(Level4)), idBag[level4.GetType().Name]);
r.SetString(1, level5.Value);
rs.Insert(r, DbInsertOptions.PositionOnInsertedRow);
}
}
}
}
}
}
return(idBag[level1.GetType().Name]);
}
/// <summary>
/// Returns generated Id.
/// </summary>
/// <returns></returns>
public static Int32 Write_Shipment(Shipment shipment, SqlCeConnection conn)
{
Dictionary <string, Int32> idBag = new Dictionary <string, int>();
// Shipment
using (SqlCeCommand cmd = new SqlCeCommand(shipment.GetType().Name, conn))
{
cmd.CommandType = System.Data.CommandType.TableDirect;
using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
{
SqlCeUpdatableRecord r = rs.CreateRecord();
/*r.SetInt32(1, shipment.SourceId);
* r.SetInt32(2, shipment.CompanyId);
* r.SetInt32(3, shipment.NumberOfPackages);
* r.SetBoolean(4, shipment.RateRuleApplied);
* if (shipment.ShipDate.HasValue)
* r.SetDateTime(5, shipment.ShipDate.Value);
* r.SetString(6, shipment.Workstation); */
rs.Insert(r, DbInsertOptions.PositionOnInsertedRow);
idBag[shipment.GetType().Name] = rs.GetInt32(0);
}
}
/*foreach (var level2 in shipment.levels) // Payload
* {
* // Level2
* using (SqlCeCommand cmd = new SqlCeCommand(level2.GetType().Name, conn))
* {
* cmd.CommandType = System.Data.CommandType.TableDirect;
* using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
* {
* SqlCeUpdatableRecord r = rs.CreateRecord();
* r.SetInt32(GetOrdinal(r, level2.GetType(), shipment.GetType()), idBag[shipment.GetType().Name]); // foreign key
* r.SetString(1, level2.Value); // payload
* rs.Insert(r, DbInsertOptions.PositionOnInsertedRow);
* idBag[level2.GetType().Name] = rs.GetInt32(0);
* }
* }
*
* foreach (var level3 in level2.levels)
* {
* using (SqlCeCommand cmd = new SqlCeCommand(level3.GetType().Name, conn))
* {
* cmd.CommandType = System.Data.CommandType.TableDirect;
* using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
* {
* SqlCeUpdatableRecord r = rs.CreateRecord();
* r.SetInt32(GetOrdinal(r, level3.GetType(), level2.GetType()), idBag[level2.GetType().Name]);
* r.SetString(1, level3.Value);
* rs.Insert(r, DbInsertOptions.PositionOnInsertedRow);
* idBag[level3.GetType().Name] = rs.GetInt32(0);
* }
* }
*
* foreach (var level4 in level3.levels)
* {
* using (SqlCeCommand cmd = new SqlCeCommand(level4.GetType().Name, conn))
* {
* cmd.CommandType = System.Data.CommandType.TableDirect;
* using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
* {
* SqlCeUpdatableRecord r = rs.CreateRecord();
* r.SetInt32(GetOrdinal(r, level4.GetType(), level3.GetType()), idBag[level3.GetType().Name]);
* r.SetString(1, level4.Value);
* rs.Insert(r, DbInsertOptions.PositionOnInsertedRow);
* idBag[level4.GetType().Name] = rs.GetInt32(0);
* }
* }
*
* foreach (var level5 in level4.levels)
* {
* using (SqlCeCommand cmd = new SqlCeCommand(level5.GetType().Name, conn))
* {
* cmd.CommandType = System.Data.CommandType.TableDirect;
* using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
* {
* SqlCeUpdatableRecord r = rs.CreateRecord();
* r.SetInt32(GetOrdinal(r, typeof(Level5), typeof(Level4)), idBag[level4.GetType().Name]);
* r.SetString(1, level5.Value);
* rs.Insert(r, DbInsertOptions.PositionOnInsertedRow);
* }
* }
* }
* }
* }
* } */
return(idBag[shipment.GetType().Name]);
}
/// <summary>
/// Gets all columns for a given table.
/// </summary>
/// <param name="connectionString">The connection string used to connect to the target database.</param>
/// <param name="table"></param>
/// <returns></returns>
public ColumnSchema[] GetTableColumns(string connectionString, TableSchema table)
{
//Erik Ejlskov - exclude system columns
string getColumnSql = string.Format(@"SELECT
COLUMN_NAME as [Name],
COLUMN_DEFAULT as [Default],
IS_NULLABLE as [IsNullable],
DATA_TYPE as [DataType],
CHARACTER_MAXIMUM_LENGTH as [Length],
NUMERIC_PRECISION as [Precision],
NUMERIC_SCALE as [Scale],
AUTOINC_SEED,
AUTOINC_INCREMENT,
COLUMN_HASDEFAULT,
COLUMN_FLAGS
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = '{0}' AND
COLUMN_FLAGS <> 98 AND
COLUMN_FLAGS <> 114
ORDER BY
ORDINAL_POSITION", table.Name);
var columns = new List <ColumnSchema>();
using (SqlCeCommand cmd = GetCeCommand(connectionString, getColumnSql))
{
using (SqlCeResultSet results = cmd.ExecuteResultSet(ResultSetOptions.None))
{
while (results.Read())
{
var extendedProperties = new List <ExtendedProperty>();
if (!results.IsDBNull(7))
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IsIdentity, true, DbType.Boolean));
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentitySeed, results["AUTOINC_SEED"].ToString(), DbType.String));
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentityIncrement, results["AUTOINC_INCREMENT"].ToString(), DbType.String));
}
else
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IsIdentity, false, DbType.Boolean));
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentitySeed, "0", DbType.String));
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentityIncrement, "0", DbType.String));
}
if (results["COLUMN_HASDEFAULT"] != null)
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.DefaultValue, results["Default"].ToString(), DbType.String));
}
else
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.DefaultValue, string.Empty, DbType.String));
}
var name = (string)results["Name"];
var nativeType = (string)results["DataType"];
//Erik Ejlskov - should be "timestamp" instead
if (nativeType == "rowversion")
{
nativeType = "timestamp";
}
DbType dataType = GetDbTypeFromString(nativeType);
if ((dataType == DbType.Guid && results.GetInt32(10) == 378) || (dataType == DbType.Guid && results.GetInt32(10) == 282))
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IsRowGuidColumn, true, DbType.Boolean));
}
else
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IsRowGuidColumn, false, DbType.Boolean));
}
int size;
int.TryParse(results["Length"].ToString(), out size);
byte precision;
byte.TryParse(results["Precision"].ToString(), out precision);
int scale;
int.TryParse(results["scale"].ToString(), out scale);
bool allowNull = GetBoolFromYesNo((string)results["IsNullable"]);
var s = new ColumnSchema(table, name, dataType, nativeType, size, precision, scale, allowNull, extendedProperties.ToArray());
columns.Add(s);
} // while(read)
} // using(results)
} // using(command)
return(columns.ToArray());
}
public List <IncomingTwilioMessage> GetAllInboxMessages()
{
List <IncomingTwilioMessage> inboxMsgs = new List <IncomingTwilioMessage>();
SqlCeCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = "SELECT COUNT(*) FROM " + TableInbox;
Int32 count = (Int32)cmd.ExecuteScalar();
if (count == 0)
{
App.logger.Log("DBStore.GetAllInboxMessages(): no messages to offload");
return(inboxMsgs);
}
App.logger.Log("DBStore.GetAllInboxMessages(): offloading " + count + " inbox message(s)");
try
{
//cmd.CommandText = "SELECT TOP (" + maxBatchLoad + ") * FROM " + TableActivations;
cmd.CommandText = "SELECT * FROM " + TableInbox;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
while (rs.Read())
{
IncomingTwilioMessage inMsg = new IncomingTwilioMessage
{
id = rs.GetInt32(0),
Timestamp = rs.GetDateTime(1),
AccountSid = rs.GetString(2),
ApiVersion = rs.GetString(3),
Body = rs.GetString(4),
From = rs.GetString(5),
FromCity = rs.GetString(6),
FromCountry = rs.GetString(7),
FromState = rs.GetString(8),
FromZip = rs.GetString(9),
MessageSid = rs.GetString(10),
NumMedia = rs.GetString(11),
NumSegments = rs.GetString(12),
SmsSid = rs.GetString(13),
SmsStatus = rs.GetString(14),
ToState = rs.GetString(15),
To = rs.GetString(16),
ToCity = rs.GetString(17),
ToCountry = rs.GetString(18),
ToZip = rs.GetString(19),
MediaURLs = rs.GetString(20)
};
// DEBUG
App.logger.Log("DBStore.GetAllInboxMessages(): message = " + inMsg.ToString());
inboxMsgs.Add(inMsg);
}
}
catch (Exception ex)
{
var message = "! Error in DBStore.GetAllInboxMessages(): " + ex.Message + "\n" + ex.TargetSite;
App.logger.Log(message);
}
return(inboxMsgs);
}
private void ClassificarListaSorteio(int idUltimaLista, string tipoOrdenacao)
{
List <CandidatoGrupo> candidatosLista = new List <CandidatoGrupo>();
using (SqlCeCommand command = CreateCommand("SELECT * FROM CANDIDATO_LISTA INNER JOIN CANDIDATO ON CANDIDATO_LISTA.ID_CANDIDATO = CANDIDATO.ID_CANDIDATO WHERE CANDIDATO_LISTA.ID_LISTA = @ID_LISTA")) {
command.Parameters.AddWithValue("ID_LISTA", idUltimaLista);
using (SqlCeResultSet resultSet = command.ExecuteResultSet(ResultSetOptions.None)) {
while (resultSet.Read())
{
candidatosLista.Add(new CandidatoGrupo {
IdCandidato = resultSet.GetInt32(resultSet.GetOrdinal("ID_CANDIDATO")),
Cpf = resultSet.GetDecimal(resultSet.GetOrdinal("CPF")),
Nome = resultSet.GetString(resultSet.GetOrdinal("NOME")).ToUpper().TrimEnd(),
QuantidadeCriterios = resultSet.GetInt32(resultSet.GetOrdinal("QUANTIDADE_CRITERIOS"))
});
}
}
}
CandidatoGrupo[] candidatosOrdenados;
if (tipoOrdenacao == "SIMPLES")
{
candidatosOrdenados = candidatosLista
.OrderByDescending(c => c.QuantidadeCriterios)
.ThenBy(c => c.Nome)
.ThenByDescending(c => c.Cpf)
.ToArray();
}
else if (tipoOrdenacao == "COMPOSTO")
{
candidatosOrdenados = candidatosLista
.OrderByDescending(c => c.QuantidadeCriteriosComposta)
.ThenBy(c => c.Nome)
.ThenByDescending(c => c.Cpf)
.ToArray();
}
else
{
candidatosOrdenados = candidatosLista
.OrderBy(c => c.Nome)
.ThenByDescending(c => c.Cpf)
.ToArray();
}
CandidatoGrupo candidatoAnterior = null;
int sequencia = 1;
int classificacao = 1;
SqlCeCommand updateCommand = CreateCommand(
"UPDATE CANDIDATO_LISTA SET SEQUENCIA = @SEQUENCIA, CLASSIFICACAO = @CLASSIFICACAO WHERE ID_LISTA = @ID_LISTA AND ID_CANDIDATO = @ID_CANDIDATO",
new SqlCeParameter("SEQUENCIA", -1),
new SqlCeParameter("CLASSIFICACAO", -1),
new SqlCeParameter("ID_LISTA", idUltimaLista),
new SqlCeParameter("ID_CANDIDATO", -1)
);
updateCommand.Prepare();
foreach (CandidatoGrupo candidato in candidatosOrdenados)
{
if (candidatoAnterior != null)
{
if (tipoOrdenacao == "SIMPLES" && candidato.QuantidadeCriterios != candidatoAnterior.QuantidadeCriterios)
{
classificacao++;
}
else if (tipoOrdenacao == "COMPOSTO" && candidato.QuantidadeCriteriosComposta != candidatoAnterior.QuantidadeCriteriosComposta)
{
classificacao++;
}
}
updateCommand.Parameters["SEQUENCIA"].Value = sequencia;
updateCommand.Parameters["CLASSIFICACAO"].Value = classificacao;
updateCommand.Parameters["ID_CANDIDATO"].Value = candidato.IdCandidato;
updateCommand.ExecuteNonQuery();
sequencia++;
candidatoAnterior = candidato;
}
}
private static void Main(string[] args)
{
SqlCeConnection sqlCeCon = new SqlCeConnection("Data Source=\\endo.sdf");
try
{
sqlCeCon.Open();
Console.WriteLine("Connection is open");
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = sqlCeCon;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT * FROM Workout;";
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
List <Workout> workoutList = new List <Workout>();
if (rs.HasRows)
{
int ordId = rs.GetOrdinal("Id");
int ordWorkoutId = rs.GetOrdinal("WorkoutId");
int ordSport = rs.GetOrdinal("Sport");
int ordDuration = rs.GetOrdinal("Duration");
while (rs.Read())
{
Guid id = rs.GetGuid(ordId);
string workoutId = rs.GetString(ordWorkoutId);
int sport = rs.GetInt32(ordSport);
double duration = rs.GetDouble(ordDuration);
workoutList.Add(new Workout(id, workoutId, sport, duration));
}
}
int counter = 1;
foreach (Workout workout in workoutList)
{
cmd.CommandText = $"SELECT * FROM Track WHERE Track.WorkoutId='{workout.Id}';";
//Console.WriteLine(cmd.CommandText);
rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
List <Track> trackList = new List <Track>();
if (rs.HasRows)
{
int ordId = rs.GetOrdinal("Id");
int ordWorkoutId = rs.GetOrdinal("WorkoutId");
int ordTimestamp = rs.GetOrdinal("Timestamp");
int ordInstruction = rs.GetOrdinal("Instruction");
int ordLatitude = rs.GetOrdinal("Latitude");
int ordLongitude = rs.GetOrdinal("Longitude");
int ordDistance = rs.GetOrdinal("Distance");
int ordSpeed = rs.GetOrdinal("Speed");
int ordAltitude = rs.GetOrdinal("Altitude");
int ordSentToServer = rs.GetOrdinal("SentToServer");
while (rs.Read())
{
int id = rs.GetInt32(ordId);
Guid workoutId = rs.GetGuid(ordWorkoutId);
DateTime timestamp = rs.GetDateTime(ordTimestamp);
timestamp = timestamp.Subtract(new TimeSpan(2, 0, 0));
int instruction = rs.IsDBNull(ordInstruction) ? -1 : rs.GetInt32(ordInstruction);
double latitude = rs.GetDouble(ordLatitude);
double longitude = rs.GetDouble(ordLongitude);
double distance = rs.GetDouble(ordDistance);
double speed = rs.GetDouble(ordSpeed);
double altitude = rs.GetDouble(ordAltitude);
bool sentToServer = rs.GetBoolean(ordSentToServer);
trackList.Add(new Track(id, workoutId, timestamp, instruction, latitude, longitude, distance, speed, altitude, sentToServer));
}
string fileName;
fileName = String.Format("Endo_{0}_tcx.tcx", counter);
CreateXmlTcx(fileName, workout, trackList);
fileName = String.Format("Endo_{0}_gpx.gpx", counter);
CreateXmlGpx(fileName, workout, trackList);
}
counter++;
}
sqlCeCon.Close();
Console.WriteLine("Connection is closed");
}
catch (Exception ex)
{
Console.WriteLine(ex.Source + " - " + ex.Message);
}
//Console.ReadKey();
}
public void SortearProximaLista(Action <string> updateStatus, Action <int> updateProgress, Action <string> logText, int?sementePersonalizada = null)
{
updateStatus("Iniciando sorteio...");
Lista proximaLista = CarregarProximaLista();
if (proximaLista == null)
{
throw new Exception("Não existem listas disponíveis para sorteio.");
}
double quantidadeAtual = 0;
double quantidadeTotal = Math.Min(proximaLista.Quantidade, (int)proximaLista.CandidatosDisponiveis);
string fonteSemente = "PERSONALIZADA";
int semente = (sementePersonalizada == null) ? ObterSemente(ref fonteSemente) : (int)sementePersonalizada;
ExecuteNonQuery(
"UPDATE LISTA SET SORTEADA = 1, SEMENTE_SORTEIO = @SEMENTE_SORTEIO, FONTE_SEMENTE = @FONTE_SEMENTE WHERE ID_LISTA = @ID_LISTA",
new SqlCeParameter("SEMENTE_SORTEIO", semente),
new SqlCeParameter("FONTE_SEMENTE", fonteSemente),
new SqlCeParameter("ID_LISTA", proximaLista.IdLista)
);
Random random = new Random(semente);
string queryGrupoSorteio = @"
SELECT TOP(1) CANDIDATO_LISTA.CLASSIFICACAO AS CLASSIFICACAO, COUNT(*) AS QUANTIDADE
FROM CANDIDATO_LISTA INNER JOIN CANDIDATO ON CANDIDATO_LISTA.ID_CANDIDATO = CANDIDATO.ID_CANDIDATO
WHERE CANDIDATO_LISTA.ID_LISTA = @ID_LISTA AND CANDIDATO_LISTA.DATA_CONTEMPLACAO IS NULL AND CANDIDATO.CONTEMPLADO = 0
GROUP BY CANDIDATO_LISTA.CLASSIFICACAO
ORDER BY CANDIDATO_LISTA.CLASSIFICACAO
";
SqlCeCommand commandGrupoSorteio = CreateCommand(queryGrupoSorteio);
commandGrupoSorteio.Parameters.AddWithValue("ID_LISTA", proximaLista.IdLista);
commandGrupoSorteio.Prepare();
string queryCandidatosGrupo = @"
SELECT CANDIDATO_LISTA.SEQUENCIA, CANDIDATO.ID_CANDIDATO, CANDIDATO.CPF, CANDIDATO.NOME
FROM CANDIDATO_LISTA INNER JOIN CANDIDATO ON CANDIDATO_LISTA.ID_CANDIDATO = CANDIDATO.ID_CANDIDATO
WHERE CANDIDATO_LISTA.ID_LISTA = @ID_LISTA AND CANDIDATO_LISTA.DATA_CONTEMPLACAO IS NULL AND CANDIDATO.CONTEMPLADO = 0 AND CANDIDATO_LISTA.CLASSIFICACAO = @CLASSIFICACAO
ORDER BY CANDIDATO_LISTA.SEQUENCIA
";
SqlCeCommand commandCandidatosGrupo = CreateCommand(queryCandidatosGrupo);
commandCandidatosGrupo.Parameters.AddWithValue("ID_LISTA", proximaLista.IdLista);
commandCandidatosGrupo.Parameters.AddWithValue("CLASSIFICACAO", -1);
commandCandidatosGrupo.Prepare();
GrupoSorteio grupoSorteio = null;
for (int i = 1; i <= proximaLista.Quantidade; i++)
{
if (grupoSorteio == null || grupoSorteio.Quantidade < 1)
{
updateStatus("Carregando próximo grupo de sorteio.");
using (SqlCeResultSet resultSet = commandGrupoSorteio.ExecuteResultSet(ResultSetOptions.None)) {
if (resultSet.Read())
{
grupoSorteio = new GrupoSorteio {
Classificacao = resultSet.GetInt32(resultSet.GetOrdinal("CLASSIFICACAO")),
Quantidade = resultSet.GetInt32(resultSet.GetOrdinal("QUANTIDADE"))
};
}
else
{
grupoSorteio = null;
}
}
if (grupoSorteio != null)
{
commandCandidatosGrupo.Parameters["CLASSIFICACAO"].Value = grupoSorteio.Classificacao;
using (SqlCeResultSet resultSet = commandCandidatosGrupo.ExecuteResultSet(ResultSetOptions.None)) {
while (resultSet.Read())
{
CandidatoGrupo candidato = new CandidatoGrupo {
Sequencia = resultSet.GetInt32(resultSet.GetOrdinal("SEQUENCIA")),
IdCandidato = resultSet.GetInt32(resultSet.GetOrdinal("ID_CANDIDATO")),
Cpf = resultSet.GetDecimal(resultSet.GetOrdinal("CPF")),
Nome = resultSet.GetString(resultSet.GetOrdinal("NOME"))
};
grupoSorteio.Candidatos.Add(candidato.Sequencia, candidato);
}
}
}
}
if (grupoSorteio == null)
{
break;
}
else
{
updateStatus($"Sorteando entre o grupo de classificação \"{grupoSorteio.Classificacao}\": {quantidadeTotal - quantidadeAtual} vagas restantes.");
}
int indiceSorteado = (grupoSorteio.Quantidade == 1) ? 0 : random.Next(0, grupoSorteio.Quantidade);
CandidatoGrupo candidatoSorteado = grupoSorteio.Candidatos.Skip(indiceSorteado).Take(1).First().Value;
grupoSorteio.Candidatos.Remove(candidatoSorteado.Sequencia);
ExecuteNonQuery(
"UPDATE CANDIDATO SET CONTEMPLADO = 1 WHERE ID_CANDIDATO = @ID_CANDIDATO",
new SqlCeParameter("ID_CANDIDATO", candidatoSorteado.IdCandidato)
);
ExecuteNonQuery(
@"
UPDATE CANDIDATO_LISTA
SET SEQUENCIA_CONTEMPLACAO = @SEQUENCIA_CONTEMPLACAO, DATA_CONTEMPLACAO = @DATA_CONTEMPLACAO
WHERE ID_CANDIDATO = @ID_CANDIDATO AND ID_LISTA = @ID_LISTA
",
new SqlCeParameter("SEQUENCIA_CONTEMPLACAO", i),
new SqlCeParameter("DATA_CONTEMPLACAO", DateTime.Now),
new SqlCeParameter("ID_CANDIDATO", candidatoSorteado.IdCandidato),
new SqlCeParameter("ID_LISTA", proximaLista.IdLista)
);
grupoSorteio.Quantidade--;
quantidadeAtual++;
updateProgress((int)((quantidadeAtual / quantidadeTotal) * 100));
logText(string.Format("{0:0000} - {1:000'.'000'.'000-00} - {2}", i, candidatoSorteado.Cpf, candidatoSorteado.Nome.ToUpper()));
}
updateStatus("Sorteio da lista finalizado!");
}
