protected void DataBind()
{
SqliteConnector db = SqliteConnector.SqliteDB;
using (SQLiteConnection con = db.Connect())
{
CountEntries = db.GetCountEntries(con, SelectedArticle.Itmref);
dataGridViewCounts.DataSource = CountEntries;
}
}
private void toolStripButtonExport_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(saveFileDialogCSV.FileName))
{
saveFileDialogCSV.FileName = DateTime.Now.ToString("yyyyMMdd.csv");
}
if (saveFileDialogCSV.ShowDialog() == DialogResult.OK)
{
List <CountEntry> entries = null;
SqliteConnector sqlite = DbConnection;
using (SQLiteConnection sqliteCon = sqlite.Connect())
{
entries = sqlite.GetCountEntries(sqliteCon);
}
if (entries == null || entries.Count == 0)
{
ShowError("Sem registos para exportar...");
return;
}
using (Stream st = saveFileDialogCSV.OpenFile())
{
using (StreamWriter wr = new StreamWriter(st))
{
StringBuilder sb = new StringBuilder();
foreach (CountEntry en in entries)
{
sb.Clear();
sb.Append(en.Itmref);
sb.Append(";");
sb.Append(Properties.Settings.Default.DefaultFCY);
sb.Append(";");
sb.Append(en.Value.ToString().Replace('.', ','));
sb.Append(";");
sb.Append(en.Unit);
wr.WriteLine(sb);
}
}
}
}
}
protected void DataBind()
{
SqliteConnector db = SqliteConnector.SqliteDB;
using (SQLiteConnection con = db.Connect())
{
CountEntries = db.GetCountEntries(con, SelectedArticle.Itmref);
Locations = db.GetLocations(con);
}
double accCount = 0;
int labels = CountEntries.Count;
foreach (CountEntry e in CountEntries)
{
accCount += e.Value;
}
AccCount = accCount;
LabelCount = labels;
if (Locations.Count > 0)
{
comboBoxLocations.Items.AddRange(Locations.ToArray());
int selectedIndex = 0;
string l = Properties.Settings.Default.DefaultLocation;
if (!string.IsNullOrEmpty(l))
{
for (int i = 0; i < Locations.Count; i++)
{
if (Locations[i].Code.Equals(l))
{
selectedIndex = i;
break;
}
}
}
comboBoxLocations.SelectedIndex = selectedIndex;
}
}
private void backgroundWorkerSync_DoWork(object sender, DoWorkEventArgs e)
{
#if DEBUG
/*
* string username = "******";
* string key = "7a8d5b8e34fc8339f58cc2c8e8353e7f";
* string baseURL = "http://192.168.0.77:8080/api.dev";
*/
string username = "******";
string key = "a2292cc4096d84751539965522655cff";
string baseURL = "http://api.sotubo.pt";
#else
string username = "******";
string key = "a2292cc4096d84751539965522655cff";
string baseURL = "http://api.sotubo.pt";
#endif
WebClient cl = new WebClient();
cl.Credentials = new NetworkCredential(username, key);
cl.Encoding = System.Text.Encoding.UTF8;
cl.Headers[HttpRequestHeader.Authorization] = string.Format("Basic {0}:{1}", username, key);
cl.Headers[HttpRequestHeader.ContentType] = "application/json;charset=utf-8";
cl.Headers[HttpRequestHeader.Accept] = "application / json, text / plain, */*";
cl.Headers[HttpRequestHeader.AcceptEncoding] = "gzip, deflate";
//RestClient client = new RestClient();
//client.BaseUrl = new Uri("http://api.sotubo.pt");
//client.Authenticator = new HttpBasicAuthenticator("sig", "a2292cc4096d84751539965522655cff");
//client.BaseUrl = new Uri("http://192.168.0.77:8080/api.dev");
//client.Authenticator = new HttpBasicAuthenticator("ncs", "7a8d5b8e34fc8339f58cc2c8e8353e7f");
int campaign_id = -1;
bool dirty = false;
while (!backgroundWorkerSync.CancellationPending && campaign_id < 0)
{
try
{
string response = cl.DownloadString(baseURL + "/mrp/count");
//RestRequest request = new RestRequest(Method.POST);
//request.Resource = "mrp/count";
//IRestResponse response = client.Execute(request);
if (!string.IsNullOrEmpty(response))
{
JObject r = JObject.Parse(response);
if (r.ContainsKey("result") && r["result"].Value <Int32>() == 1)
{
JToken jdata = r["jdata"];
if (jdata != null)
{
campaign_id = jdata["id"].Value <Int32>();
break;
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
int t = 60;
while (t-- > 0 || backgroundWorkerSync.CancellationPending)
{
Thread.Sleep(1000);
}
}
SqliteConnector sqlite = DbConnection;
if (!backgroundWorkerSync.CancellationPending)
{
try
{
string response = cl.DownloadString(baseURL + "/mrp/count/locations");
//RestRequest request = new RestRequest(Method.POST);
//request.Resource = "mrp/count";
//IRestResponse response = client.Execute(request);
if (!string.IsNullOrEmpty(response))
{
JObject r = JObject.Parse(response);
if (r.ContainsKey("result") && r["result"].Value <Int32>() == 1)
{
JArray jdata = r["jdata"] as JArray;
if (jdata != null && jdata.Count > 0)
{
using (SQLiteConnection sqliteCon = sqlite.Connect())
{
List <Location> existent = sqlite.GetLocations(sqliteCon);
foreach (JObject loc in jdata)
{
string code = loc["code"].Value <string>();
string description = loc["description"].Value <string>();
Location db = null;
for (int i = 0; i < existent.Count; i++)
{
if (existent[i].Code.Equals(code))
{
db = existent[i];
break;
}
}
if (db == null)
{
//new location record
db = new Data.Location();
db.Code = code;
db.Description = description;
sqlite.Insert(sqliteCon, db);
}
else
{
//exists, update it and remove it from existent list so we can later delete it
bool changes = false;
if (!db.Description.Equals(description))
{
db.Description = description;
changes = true;
}
if (changes)
{
sqlite.Update(sqliteCon, db);
}
existent.Remove(db);
}
}
//the ones that remain in existent list are to be deleted
foreach (Location l in existent)
{
sqlite.Delete(sqliteCon, l);
}
}
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
}
while (!backgroundWorkerSync.CancellationPending)
{
dirty = false;
try
{
//sync upstream changes
long stamp = Properties.Settings.Default.SyncLastUpdate;
//RestRequest request = new RestRequest(Method.POST);
//request.Resource = string.Format("mrp/count/sync/{0}/{1}", campaign_id, stamp);
//IRestResponse response = client.Execute(request);
string response = cl.DownloadString(baseURL + string.Format("/mrp/count/sync/{0}/{1}", campaign_id, stamp));
if (!string.IsNullOrEmpty(response))
{
JObject r = JObject.Parse(response);
if (r.ContainsKey("result") && r["result"].Value <Int32>() == 1)
{
JArray jdata = r["jdata"] as JArray;
if (jdata != null && jdata.Count > 0)
{
using (SQLiteConnection sqliteCon = sqlite.Connect())
{
foreach (JObject it in jdata)
{
string hash = it["hash"].Value <string>();
long update_stamp = it["update"].Value <long>();
if (update_stamp > stamp)
{
stamp = update_stamp;
}
CountEntry en = new CountEntry();
en.Itmref = it["itmref"].Value <string>();
//en.LabelNumber = nextNumber;
en.Value = it["value"].Value <double>();
en.Unit = it["stu"].Value <string>();
en.Hash = it["hash"].Value <string>();
long unixTime = it["time"].Value <long>();
DateTime epoch = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
en.Time = epoch.AddSeconds(unixTime);
en.IsDeleted = it["deleted"].Value <int>() == 1;
if (en.IsDeleted)
{
en.DeleteKey = DateTime.UtcNow.ToFileTimeUtc();
}
en.SyncCode = it["id"].Value <int>();
en.Sync = CountEntry.SyncStatus.POSTED;
en.Location = it["location"].Value <string>();
CountEntry ex = sqlite.GetCountEntryByHash(sqliteCon, hash);
if (ex == null)
{
int nextNumber = 0;
List <CountEntry> CountEntries = sqlite.GetCountEntries(sqliteCon, en.Itmref);
nextNumber = CountEntries.Count + 1;
foreach (CountEntry entry in CountEntries)
{
nextNumber = Math.Max(nextNumber, entry.LabelNumber + 1);
}
en.LabelNumber = nextNumber;
sqlite.Insert(sqliteCon, en);
dirty = true;
}
else
{
bool changes = false;
if (!en.Itmref.Equals(ex.Itmref))
{
ex.Itmref = en.Itmref;
changes = true;
}
if (Math.Abs(en.Value - ex.Value) > 0.001)
{
ex.Value = en.Value;
changes = true;
}
if (!en.Unit.Equals(ex.Unit))
{
ex.Unit = en.Unit;
changes = true;
}
if (!en.Hash.Equals(ex.Hash))
{
ex.Hash = en.Hash;
changes = true;
}
if (Math.Abs((en.Time - ex.Time).TotalSeconds) > 1)
{
ex.Time = en.Time;
changes = true;
}
if (!en.IsDeleted.Equals(ex.IsDeleted))
{
ex.IsDeleted = en.IsDeleted;
changes = true;
}
if (!en.SyncCode.Equals(ex.SyncCode))
{
ex.SyncCode = en.SyncCode;
changes = true;
}
if (!en.Location.Equals(ex.Location))
{
ex.Location = en.Location;
changes = true;
}
if (changes)
{
sqlite.Update(sqliteCon, ex);
dirty = true;
}
}
}
}
}
}
}
if (stamp > Properties.Settings.Default.SyncLastUpdate)
{
Properties.Settings.Default.SyncLastUpdate = stamp;
Properties.Settings.Default.Save();
}
//check for dirty records
List <CountEntry> hoes = null;
using (SQLiteConnection sqliteCon = sqlite.Connect())
{
hoes = sqlite.GetDirtyCountEntries(sqliteCon);
}
if (hoes != null && hoes.Count > 0)
{
//get current active session if any
//request = new RestRequest(Method.POST);
//request.Resource = "mrp/count";
//response = client.Execute(request);
response = cl.DownloadString(baseURL + "/mrp/count");
if (!string.IsNullOrEmpty(response))
{
JObject r = JObject.Parse(response);
if (r.ContainsKey("result") && r["result"].Value <Int32>() == 1)
{
JToken jdata = r["jdata"];
if (jdata != null)
{
int campaing_id = jdata["id"].Value <Int32>();
//commit changes
foreach (CountEntry en in hoes)
{
Debug.Assert(en.Sync == CountEntry.SyncStatus.NEW);
//we are assuming new to know wich ones to update on local DB
if (en.IsDeleted && en.SyncCode > 0)
{
//delete operation
//request = new RestRequest(Method.POST);
//request.Resource = string.Format("mrp/count/delete/{0}/{1}", campaing_id, en.SyncCode);
//response = client.Execute(request);
response = cl.DownloadString(baseURL + string.Format("/mrp/count/delete/{0}/{1}", campaing_id, en.SyncCode));
r = JObject.Parse(response);
if (r.ContainsKey("result") && r["result"].Value <Int32>() == 1)
{
en.Sync = CountEntry.SyncStatus.POSTED;
}
}
if (!en.IsDeleted)
{
string url;
//request = new RestRequest(Method.POST);
if (en.SyncCode > 0)
{
//update
//request.Resource = string.Format("mrp/count/update/{0}/{1}", campaing_id, en.SyncCode);
url = baseURL + string.Format("/mrp/count/update/{0}/{1}", campaing_id, en.SyncCode);
}
else
{
//insert
//request.Resource = string.Format("mrp/count/create/{0}/", campaing_id);
url = baseURL + string.Format("/mrp/count/create/{0}", campaing_id);
}
Article a = null;
using (SQLiteConnection sqliteCon = sqlite.Connect())
{
a = sqlite.GetItem(sqliteCon, en.Itmref);
}
JObject parameters = new JObject();
parameters.Add("itmref", en.Itmref);
parameters.Add("itmdes", a == null ? string.Empty : a.Itmdes);
//parameters.Add("itmdes", string.Empty);
parameters.Add("value", en.Value);
parameters.Add("stu", en.Unit);
parameters.Add("operator", "000");
parameters.Add("hash", en.Hash);
parameters.Add("location", en.Location);
parameters.Add("location_data", string.Empty);
DateTime epoch = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
parameters.Add("time", Convert.ToInt64((en.Time - epoch).TotalSeconds));
//response = client.Execute(request);
response = cl.UploadString(url, parameters.ToString());
r = JObject.Parse(response);
//ignore result because we want to update local record anyway
if (r.ContainsKey("result"))
{
jdata = r["jdata"];
en.Sync = CountEntry.SyncStatus.POSTED;
en.SyncCode = jdata["id"].Value <Int32>();
}
}
else
{
en.Sync = CountEntry.SyncStatus.POSTED;
}
}
//update local DB
using (SQLiteConnection sqliteCon = sqlite.Connect())
{
foreach (CountEntry en in hoes)
{
if (en.Sync == CountEntry.SyncStatus.POSTED)
{
sqlite.Update(sqliteCon, en);
dirty = true;
}
}
}
}
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
int t = dirty ? 10 : 60;
while (t-- > 0 || backgroundWorkerSync.CancellationPending)
{
Thread.Sleep(1000);
}
}
}
private void toolStripButtonImport_Click(object sender, EventArgs e)
{
if (openFileDialogImport.ShowDialog(this) == DialogResult.OK)
{
string[] lines = File.ReadAllLines(openFileDialogImport.FileName);
SqliteConnector sqlite = DbConnection;
SQLiteTransaction tr = null;
int done = 0, total = lines.Length;
ReportProgress(0, "A importar ficheiro de dados");
using (SQLiteConnection sqliteCon = sqlite.Connect())
{
try
{
tr = sqliteCon.BeginTransaction();
foreach (string l in lines)
{
string[] els = l.Split('\t', ';');
Debug.Assert(els.Length == 5);
if (els.Length != 5)
{
throw new ArgumentException("Formato de ficheiro invalido");
}
string itmref = els[0];
string itmdes = els[1];
double qty = double.Parse(els[2].Replace('.', ','));
string stu = els[3];
DateTime date = DateTime.Parse(els[4]);
CountEntry en = new CountEntry();
en.Itmref = itmref;
en.Value = qty;
en.Unit = stu;
en.Time = date;
en.Sync = CountEntry.SyncStatus.NEW;
int nextNumber = 0;
List <CountEntry> CountEntries = sqlite.GetCountEntries(sqliteCon, en.Itmref);
nextNumber = CountEntries.Count + 1;
foreach (CountEntry entry in CountEntries)
{
nextNumber = Math.Max(nextNumber, entry.LabelNumber + 1);
}
en.LabelNumber = nextNumber;
sqlite.Insert(sqliteCon, en, tr);
done++;
int progress = (int)(100.0 * done / total);
ReportProgress(progress, string.Format("A inserir registo {0} de {1}", done, total));
}
tr.Commit();
ReportProgress(100, "Importacao concluida");
}
catch (Exception ex)
{
tr.Rollback();
ReportProgress(0, string.IsNullOrEmpty(ex.Message) ? "Erro ao importar ficheiro" : ex.Message);
}
}
}
}