public Order GetOrder()
{
Order order = new Order();
try
{
using (FileStream fs = new FileStream(FileName, FileMode.Open))
{
using (StreamReader sr = new StreamReader(fs))
{
string line;
while (!sr.EndOfStream)
{
try
{
line = sr.ReadLine();
string[] data = line.Split(Delimiter);
if ((data.Length) < 4)
{
continue;
}
Ean ean = GetEan(data[2]);
OrderItem item = new OrderItem(ean, new Scan(int.Parse(data[0]), GlobalArea.CurrentEmployee.GammaID));
item.Id = new Guid(Convert.ToString(data[4]));
item.Qty = int.Parse(data[1]);
order.OSpec.Add(item.Ean.Ean13, item);
}
catch (Exception) { }
}
}
}
}
catch (FileNotFoundException) { }
return(order);
}
public Ean GetEan(string barcode)
{
Ean ean = null;
string commandText = @" SELECT s.artcode, s.ean13, s.names, s.koef, IFNULL(p.qty,0) qty, s.nds, s.Manufacturer
FROM sprean s
LEFT OUTER JOIN pereuchet p ON s.artcode = p.artcode
WHERE s.ean13 = :barcode
ORDER BY IFNULL(p.qty,0) DESC, s.artcode desc";
using (var connect = GetConnect())
{
connect.Open();
using (SQLiteCommand command = connect.CreateCommand())
{
command.CommandText = commandText;
command.CommandType = CommandType.Text;
command.Parameters.Add("barcode", DbType.String).Value = barcode;
using (SQLiteDataReader res = command.ExecuteReader())
{
if (res.HasRows && res.Read())
{
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.GetInt32(4);
ean.Nds = res.GetInt32(5);
ean.Manufacturer = res.GetString(6);
}
}
}
}
return(ean);
}
public OrderItem GetOrdeItemByBarcode(string barcode)
{
if (barcode.Length == 0)
{
return(null);
}
Ean ean = GetEan(barcode);
return((ean != null) ? GetOrderItemByEan(ean) : null);
}
public OrderItem(Ean ean, Scan scan)
{
this.Ean = ean;
this.Scan = scan;
this.Id = Guid.NewGuid();
this.DateCreated = DateTime.Now;
this.ScanLog = new ScanLog(ean.ArtCode,
GlobalArea.CurrentEmployee.GammaID,
scan.Qty,
GlobalArea.CurrentDateSQLStr,
ean.Ean13);
}
public OrderItem GetOrderItemByEan(Ean ean)
{
if (ean == null)
{
return(null);
}
OrderItem orderItem;
Scan scan = GetScan(ean.ArtCode);
if (scan == null)
{
return(null);
}
orderItem = new OrderItem(ean, scan);
return(orderItem);
}
// возвращает одну запись справочника
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 OrderItem CreateOrderItem(Ean ean)
{
OrderItem orderItem = null;
string insertCommandText = @" INSERT INTO scan(ArtCode, id_gamma, Qty, Dt, StartDt, EndDt)
VALUES(@artcode, @id_gamma, @qty, datetime('now'), @StartDt, @EndDt) ";
using (var connect = GetConnect())
{
connect.Open();
SQLiteTransaction tx = connect.BeginTransaction();
using (SQLiteCommand command = connect.CreateCommand())
{
command.CommandText = insertCommandText;
command.Parameters.Add("artcode", DbType.Int32).Value = ean.ArtCode;
command.Parameters.Add("id_gamma", DbType.Int32).Value = GlobalArea.CurrentEmployee.GammaID;
command.Parameters.Add("qty", DbType.Int32).Value = 0;
command.Parameters.Add("StartDt", DbType.String).Value = GlobalArea.CurrentDateSQLStr;
command.Parameters.Add("EndDt", DbType.String).Value = GlobalArea.CurrentDateSQLStr;
command.Transaction = tx;
try
{
int rowsAffected = command.ExecuteNonQuery();
tx.Commit();
GlobalArea.Logger.Info("Insert into Scan table success.");
orderItem = GetOrderItemByEan(ean);
}
catch (Exception e)
{
GlobalArea.Logger.Error("[Exception][Rollback inserting into Scan table.] " + e.ToString());
MessageBox.Show(e.Message);
tx.Rollback();
orderItem = null;
}
}
}
if (orderItem != null)
{
var slog = orderItem.ScanLog;
SaveLog(ref slog);
}
return(orderItem);
}
//создает новую запись OrderItem
public OrderItem CreateOrderItem(Ean ean)
{
OrderItem orderItem = null;
string insertCommandText = @" INSERT INTO scan(ArtCode, id_gamma, Qty, Dt, StartDt, EndDt)
VALUES(@artcode, @id_gamma, @qty, GETDATE(), @StartDt, @EndDt) ";
using (SqlCeConnection connect = new SqlCeConnection(Datasource))
{
connect.Open();
SqlCeTransaction tx = connect.BeginTransaction();
SqlCeCommand command = connect.CreateCommand();
command.CommandText = insertCommandText;
command.Parameters.Add("artcode", SqlDbType.Int).Value = ean.ArtCode;
command.Parameters.Add("id_gamma", SqlDbType.Int).Value = GlobalArea.CurrentEmployee.GammaID;
command.Parameters.Add("qty", SqlDbType.Int).Value = 0;
command.Parameters.Add("StartDt", SqlDbType.NVarChar).Value = GlobalArea.CurrentDateSQLStr;
command.Parameters.Add("EndDt", SqlDbType.NVarChar).Value = GlobalArea.CurrentDateSQLStr;
command.Transaction = tx;
try
{
int rowsAffected = command.ExecuteNonQuery();
tx.Commit();
orderItem = GetOrderItemByEan(ean);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
tx.Rollback();
orderItem = null;
}
}
if (orderItem != null)
{
var slog = orderItem.ScanLog;
SaveLog(ref slog);
}
return(orderItem);
}
public OrderItem CreateOrderItemByBarcode(string barcode)
{
Ean ean = GetEan(barcode);
return((ean != null) ? CreateOrderItem(ean) : null);
}
public OrderItem GetOrderItemByEan(Ean ean)
{
throw new NotImplementedException();
}
private void ScaningData(string barcode)
{
OrderItem item = null;
try
{
//если в сканированых не нашли тогда ищем в БД
statusBar1.Text = "Поиск " + barcode + " в БД...";
statusBar1.Refresh();
//item = data.GetOrdeItemByBarcode(barcode);
Ean ean = data.GetEan(barcode);
if (ean != null)
{
statusBar1.Text = "Найдено: " + ean.ArtCode.ToString();
statusBar1.Refresh();
}
item = (ean != null) ? data.GetOrderItemByEan(ean) : null;
// запись не создана тогда создадим её
if (item == null)
{
//item = data.CreateOrderItemByBarcode(barcode);
item = (ean != null) ? data.CreateOrderItem(ean) : null;
}
//если ШК не найден то выход
if (item == null)
{
statusBar1.Text = "ШК " + barcode + " не найден.";
MessageBox.Show(statusBar1.Text, "Внимание", MessageBoxButtons.OK, MessageBoxIcon.None, MessageBoxDefaultButton.Button1);
RefreshStatusBar();
return;
}
try
{
if (GlobalArea.AppOption.FastScan)
{
item.Qty += ((GlobalArea.AppOption.MultypleQtyType == MultipleQty.BY_PACKAGE) ? item.Ean.Koef : 1);
item.ScanLog.ActType = AType.Update;
var t_itm = data.Save(ref item);
lastOrderItem = t_itm;
if (t_itm == null)
{
MessageBox.Show("Позиция не добавлена", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.None, MessageBoxDefaultButton.Button1);
}
}
else //using (EditQty editQty = new EditQty())
{
editQty.Text = "Ввод количества | " + barcode;
editQty.Ean13 = barcode;
var listOrderItem = new List <OrderItem>();
listOrderItem.Add(item);
editQty.cbArt.DataSource = listOrderItem;
editQty.cbArt.DisplayMember = "Name";
editQty.cbArt.ValueMember = "ArtCode";
editQty.cbArt.SelectedIndex = 0;
editQty.cbArt.Focus();
editQty.lblCnt.Text = editQty.cbArt.Items.Count.ToString();
if (editQty.cbArt.Items.Count == 1)
{
editQty.tbQty.Focus();
editQty.tbQty.SelectAll();
}
editQty.RefreshFont();
if ((editQty.ShowDialog() == DialogResult.OK) && (editQty.Qty != 0))
{
item.Qty += editQty.Qty;
item.ScanLog.ActType = AType.Update;
lastOrderItem = data.Save(ref item);
}
}
}
catch (Exception e)
{
MessageBox.Show(statusBar1.Text, "Ошибка записи данных в БД.", MessageBoxButtons.OK, MessageBoxIcon.None, MessageBoxDefaultButton.Button1);
}
}
finally
{
RefreshStatusBar();
}
}