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); }