public static decimal GetPrice(int productID, Int32 vendorId)
{
using (var pDb = new PetaPoco.Database(Environments.Current.Connection, "System.Data.SqlClient"))
{
var vendorAssortmentId = pDb.FirstOrDefault <Int32>(String.Format("select vendorassortmentid from vendorassortment where productid = {0} and vendorid = {1}", productID, vendorId));
var price = pDb.FirstOrDefault <Decimal>(String.Format("select isnull(specialprice, price) from vendorprice where vendorassortmentid = {0}", vendorAssortmentId));
return(price);
}
}
public static int GetSlipNumber(int vendorId, string salesSlipNumberSetting = "")
{
using (var pDb = new PetaPoco.Database(Environments.Current.Connection, "System.Data.SqlClient"))
{
var slipNumber = pDb.FirstOrDefault <int>(String.Format("select value from vendorsetting where vendorid = {0} and settingkey = '{1}'", vendorId, string.IsNullOrEmpty(salesSlipNumberSetting) ? MANCO_SALESSLIP_NUMBER_SETTINGKEY : salesSlipNumberSetting));
return(slipNumber == 0 ? 1 : slipNumber); //start always from 1
}
}
public static string GetBarcode(int productID)
{
using (var pDb = new PetaPoco.Database(Environments.Current.Connection, "System.Data.SqlClient"))
{
var barcode = pDb.FirstOrDefault <string>(@"select barcode from productbarcode where productid = @0 and barcodetype = @1", productID, (int)BarcodeTypes.Default);
barcode.ThrowIfNull("Barcode not found for product " + productID);
return(barcode);
}
}
string GetSN(PetaPoco.Database db, string modename, string fieldname)
{
var autoCodeRule = db.FirstOrDefault <FastDev.DevDB.Model.core_autoCode>("where ModelName = @0 and FieldName = @1", modename, fieldname);
if (autoCodeRule != null)
{
return(new FastDev.DevDB.AutoCode.AutoCodeService(db, autoCodeRule).GetNewAutoCode());
}
return("");
}
public List <DatColStockModel> MapToDatCol(Int32 vendorId, List <Model.WehkampStockMutation> mutations)
{
using (var pDb = new PetaPoco.Database(Environments.Current.Connection, "System.Data.SqlClient"))
{
int connectorID = pDb.FirstOrDefault <int>(@"select connectorid from contentproduct where vendorid = @0 and isassortment = 1", vendorId);
int shopNumber = ConnectorHelper.GetStoreNumber(connectorID);
int differenteShopNumber = VendorHelper.GetDifferenceShopNumber(vendorId);
string employeeNumber = VendorHelper.GetEmployeeNumber(vendorId);
var salesSlipNumber = ReceiptHelper.GetSlipNumber(vendorId);
int _receiptIndex = GenericSlipNumberHelper.GetSlipNumberForTransfer(vendorId, ReceiptHelper.STOCK_SALESSLIP_RECEIPT_NUMBER_SETTING_KEY);
int _receiptIndexSurplu = GenericSlipNumberHelper.GetSlipNumberForTransfer(vendorId, ReceiptHelper.STOCK_SALESSLIP_RECEIPT_NUMBER_SETTING_KEY_SURPLUS);
mutations.ForEach(mutation =>
{
_index += 200;
var line = new DatColStockModel
{
StoreNumber = (mutation.MutationQuantity > 0 ? differenteShopNumber : shopNumber).ToString("D3") + " 01",
EmployeeNumber = employeeNumber,
ReceiptNumber = salesSlipNumber,
TransactionType = "20",
DateNotified = mutation.MutationDate.ToString("yyyyMMddHHmm"),
RecordType = "01",
SubType = "00",
NumberOfSkus = Math.Abs(mutation.MutationQuantity),
MancoOrSurplus = mutation.MutationQuantity > 0 ? shopNumber : differenteShopNumber,
FixedField1 = "000000000+",
RecordSequence = _index,
FixedField2 = "000",
FixedField3 = "000000000+",
FixedField4 = "000",
FixedField5 = "000000000+",
FixedField6 = "000",
OriginalSellingPrice = (int)Math.Round(PriceHelper.GetPrice(mutation.ProductID, vendorId) * 100),
FixedField7 = "00",
ArticleNumberColorCodeSizeCode = ProductHelper.GetPFAItemNumber(mutation.Articlenumber, mutation.Colorcode, mutation.ProductID),
Barcode = BarcodeHelper.GetBarcode(mutation.ProductID),
Receipt = string.Format("{0}{1}{2}", 0, mutation.MutationQuantity > 0 ? differenteShopNumber.ToString("D3") : shopNumber.ToString(), _receiptIndex.ToString().PadLeft(4, '0')),
TaxCode = "1",
EmployeeNumber2 = employeeNumber,
ScannedWithBarcodeReader = 0
};
_list.Add(line);
});
ReceiptHelper.IncrementSalesSlipNumber(ref salesSlipNumber, vendorId, ReceiptHelper.STOCK_SALESSLIP_NUMBER_SETTINGKEY);
return(_list);
}
}
public int GetProductIdentification(string incompleteBarcode)
{
using (var pDb = new PetaPoco.Database(Connection, "System.Data.SqlClient"))
{
pDb.CommandTimeout = 5 * 60;
string completeBarcode = BarcodeHelper.AddCheckDigitToBarcode(incompleteBarcode);
int match = pDb.FirstOrDefault <int>("SELECT PRODUCTID FROM PRODUCTBARCODE WHERE BARCODE = @0", completeBarcode);
return(match);
}
}
public static string GetPFAItemNumber(string articleNumber, string colorCode, int productID)
{
using (var pDb = new PetaPoco.Database(Environments.Current.Connection, "System.Data.SqlClient"))
{
if (string.IsNullOrEmpty(articleNumber) || string.IsNullOrEmpty(colorCode))
{
var vendorItemNumber = pDb.FirstOrDefault <string>("select VendorItemNumber from product where productid = @0", productID);
if (!vendorItemNumber.Contains(" "))
{
throw new InvalidOperationException("Something went wrong with trying to parse the article and color codes");
}
var parts = vendorItemNumber.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
articleNumber = parts[0];
colorCode = parts[1];
}
var size = pDb.FirstOrDefault <String>("select barcode from productbarcode where productid = @0 and barcodetype = @1", productID, (int)BarcodeTypes.PFA) ?? "990";
String pfaItemNumber = string.Format("{0}{1}{2}", articleNumber, colorCode.PadLeft(3, '0'), size.PadLeft(4, '0'));
return(pfaItemNumber);
}
}
public void LogIn()
{
using (var db = new PetaPoco.Database("db"))
{
var user = new UserModel();
try
{
user = db.FirstOrDefault <UserModel>("SELECT u.UserId AS UserId, u.UserName as UserName, u.StoreId as StoreId FROM Users u LEFT JOIN Stores s ON s.StoreId = u.StoreId WHERE KeyCode = @0 AND Username = @1", Password, Username);
if (user == null)
{
MessageBox.Show("Anmeldung fehlgeschlagen.", "Fehler", MessageBoxButton.OK, MessageBoxImage.Error);
}
else
{
//var naviContext = (NavigationViewModel)MainWindow.Instance.NavigationPanel.DataContext;
//naviContext.CurrentUser = user;
//naviContext.StoreName = currentStore;
var currentStore = db.ExecuteScalar <string>("SELECT StoreName FROM Stores WHERE StoreId = @0", user.StoreId);
App.Current.Properties["UserName"] = user.UserName;
App.Current.Properties["UserId"] = user.UserId;
App.Current.Properties["StoreId"] = user.StoreId;
App.Current.Properties["StoreName"] = currentStore;
//var props = App.Current.Properties;
//string uid = Application.Current.Properties["UserId"].ToString();
MainWindow.Instance.MainWindowContentControl.Content = new MenuView();
MainWindow.Instance.NavigationPanel.DataContext = new NavigationViewModel();
}
}
catch (Exception ex)
{
MessageBox.Show(string.Format("Daten konnten nicht abgerufen werden:\n\n" + ex.Message), "Fehler");
}
}
}
public void SaveValueSet(ValueSet.Entity.ValueSet v)
{
string msg;
try
{
var dupval = db.FirstOrDefault <ValueSet.Entity.ValueSet>(" where id<>@0 and text=@1", v.Id, v.Text);
if (dupval != null)
{
throw new InfoException("同名的值集已经存在!");
}
if (v.Id == 0)
{
v.IsDelete = false;
v.IsEnabled = true;
if (string.IsNullOrEmpty(v.Text))
{
v.Text = v.Value;
}
if (string.IsNullOrEmpty(v.Value))
{
v.Value = v.Text;
}
db.Insert(v);
}
else
{
db.Save(v);
}
}
catch (Exception e)
{
throw e;
}
}
public MUser GetNextUser()
{
MUser user = db.FirstOrDefault <MUser>("WHERE finished<9 ORDER BY sort DESC");
return(user);
}
public SettingsProvider(string scope)
{
_currentSettings = new Hashtable();
_scopelessSettings = new Hashtable();
string p = System.IO.Path.Combine(new string[] { System.Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData), "GAPP" });
if (!Directory.Exists(p))
{
Directory.CreateDirectory(p);
}
string fn = System.IO.Path.Combine(p, "settings.db3");
//create backup first
List <string> availableBackups = new List <string>();
if (File.Exists(fn))
{
File.Copy(fn, string.Format("{0}.{1}.bak", fn, DateTime.Now.ToString("yyyyMMddHHmmss")));
//keep maximum of X backups
availableBackups = Directory.GetFiles(p, "settings.db3.*.bak").OrderBy(x => x).ToList();
while (availableBackups.Count > 20)
{
File.Delete(availableBackups[0]);
availableBackups.RemoveAt(0);
}
}
bool dbOK;
bool backupUsed = false;
do
{
try
{
_database = new PetaPoco.Database(string.Format("data source=file:{0}", fn), Community.CsharpSqlite.SQLiteClient.SqliteClientFactory.Instance);
dbOK = _database.ExecuteScalar <string>("PRAGMA integrity_check") == "ok";
//if (availableBackups.Count > 2) dbOK = false; //test
}
catch
{
dbOK = false;
}
if (!dbOK)
{
backupUsed = true;
try
{
_database.Dispose();
}
catch
{
}
_database = null;
//delete settings and move to latest
File.Delete(fn);
if (availableBackups.Count > 0)
{
File.Move(availableBackups[availableBackups.Count - 1], fn);
availableBackups.RemoveAt(availableBackups.Count - 1);
}
}
} while (!dbOK);
if (backupUsed)
{
System.Windows.Forms.MessageBox.Show("The settings file was corrupt and a backup file is restored.", "Settings");
}
if (!TableExists("Settings"))
{
_database.Execute("create table 'Settings' (Name text, Value text)");
_database.Execute("insert into Settings (Name, Value) values ('Scope', 'default')");
}
if (!TableExists("SettingsScope"))
{
_database.Execute("create table 'SettingsScope' (ID integer primary key autoincrement, Name text)");
}
if (string.IsNullOrEmpty(scope))
{
scope = _database.ExecuteScalar <string>("select Value from Settings where Name=@0", "Scope");
if (string.IsNullOrEmpty(scope))
{
scope = "default";
}
}
_scope = _database.FirstOrDefault <SettingsScope>("where Name=@0", scope);
if (_scope == null)
{
_scope = new SettingsScope();
_scope.Name = scope;
_database.Save(_scope);
}
if (!TableExists(string.Format("Settings_{0}", _scope.ID)))
{
_database.Execute(string.Format("create table 'Settings_{0}' (Name text, Value text)", _scope.ID));
}
var settings = _database.Fetch <Setting>(string.Format("select * from Settings_{0}", _scope.ID));
foreach (var s in settings)
{
_currentSettings.Add(s.Name, s.Value);
}
var scopelesssettings = _database.Fetch <Setting>("select * from Settings");
foreach (var s in scopelesssettings)
{
_scopelessSettings.Add(s.Name, s.Value);
}
}
public void SaveImportedExcelRestockLines(object parameter)
{
//var navContext = (NavigationViewModel)MainWindow.Instance.NavigationPanel.DataContext;
try
{
var vm = (ExcelImportViewModel)parameter;
var storeId = App.Current.Properties["StoreId"].ToString();
int resultStoreId;
int.TryParse(storeId, out resultStoreId);
if (resultStoreId == 0)
{
throw new Exception("Benutzer ist keine Filiale zu geordnet.");
}
var userId = App.Current.Properties["UserId"].ToString();
int resultUserId;
int.TryParse(userId, out resultUserId);
if (resultUserId == 0)
{
throw new Exception("Unbekannter Benutzer.");
}
using (var db = new PetaPoco.Database("db"))
{
using (var scope = db.GetTransaction())
{
var storages = from rsml in vm.RestockModelLines
group rsml by rsml.StorageName into rsmlGroup
orderby rsmlGroup.Key
select rsmlGroup.Key;
var list = storages.ToList();
//var storagelist = vm.RestockModelLines.Where(x => string.IsNullOrWhiteSpace(x.StorageName) == false).GroupBy(x => x.StorageName).ToList();
foreach (var storageName in list)
{
var storageId = db.FirstOrDefault <int?>("SELECT StorageId From Storages WHERE StorageName = @0;", storageName);
if (storageId == null)
{
db.Execute("INSERT INTO Storages(StorageName) VALUES(@0);", storageName);
}
}
var restockId = db.ExecuteScalar <int>("INSERT INTO Restocks(Dt, UserId) VALUES(Date('now'),@0);SELECT last_insert_rowid();", resultUserId);
foreach (var item in vm.RestockModelLines)
{
//db.Execute("INSERT INTO RestockLines(, StoreId, UserId) VALUES(@0, @1, @2)", restock.Date, restock.StoreId, restock.UserId);
var ArtId = db.FirstOrDefault <int?>("SELECT ArticleId From Articles WHERE GTIN = @0;", item.GTIN);
if (ArtId == null)
{
var storageId = db.ExecuteScalar <int?>("Select StorageId FROM Storages WHERE StorageName = @0;", item.StorageName);
ArtId = db.ExecuteScalar <int>("INSERT INTO Articles(GTIN, ADesc, StorageId) VALUES(@0, @1, @2); SELECT last_insert_rowid();", item.GTIN, item.ArtDesc, storageId);
}
db.Execute("INSERT INTO RestockLines(RestockId, Pos, ArtId, Amt) VALUES(@0, @1, @2, @3);", restockId, item.Pos, ArtId, 0);
}
scope.Complete();
}
}
MessageBox.Show("Daten wurden erfolgreich importiert.", "Hinweis", MessageBoxButton.OK, MessageBoxImage.Information);
vm.RestockModelLines.Clear();
}
catch (Exception ex)
{
MessageBox.Show(string.Format("Daten konnten nicht gespeichert werden:\n\n" + ex.Message), "Fehler");
}
}
public Component GetComponentDetails(int Id)
{
var data = db.FirstOrDefault <Component>($"Select * from Component where Id={Id}");
return(data);
}
