public int UpdateAccount(Account account) { try { using (var connection = new OdbcConnection(this.ConnetionString)) { connection.Open(); var query = $@" UPDATE Accounts SET AuxAccNo = '{account.ProviderId}', Name = '{SafeConvert.ToPervasiveString(account.Name)}', Address = '{SafeConvert.ToPervasiveString(account.Address)}', City = '{SafeConvert.ToPervasiveString(account.City)}', ZipCode = '{account.ZipCode}', Reduc100 = {account.ComercialReduc * 100}, CreditLevel = {account.CreditLevel}, Email = '{account.Email}', OsekNo = '{account.OsekNo}', Fax = '{account.Fax}', Phone1 = '{account.Phone1}', Phone2 = '{account.Phone2}', SortCode1 = {account.SortCode1}, SortCode2 = {account.SortCode2}, SortCode34 = {account.SortCode3}, SortCode5 = {account.SortCode5}, SortCode6 = {account.SortCode6}, VatFlag = '{account.VatFlag}', Udate = {PervasiveDBHelper.ToPervasiveDate(DateTime.Now)} WHERE AccNo = {account.Id} "; var command = new OdbcCommand(query); command.Connection = connection; command.ExecuteNonQuery(); } return(account.Id); } catch (Exception ex) { Debug.WriteLine(ex.Message); throw; } }
public IEnumerable <Item> Find(ItemSearchParams searchParams) { var hasSiteSync = this.IsColumnExists($"Items", "SITESYNC"); var hasKupaSync = this.IsColumnExists($"Items", "KUPASYNC"); var hasUpdDate = this.IsColumnExists($"Items", "UitemDate"); try { var items = new List <Item>(); // ignore tables search with updated-date filter but no updated-date column // to prevent receiving incorrect data var hasUpdDateFilter = searchParams.FromUpdateDate.HasValue || searchParams.ToUpdateDate.HasValue; if (searchParams.PauseIfNoUpdateDateColumn && !hasUpdDate && hasUpdDateFilter) { return(items); } using (var connection = new OdbcConnection(this.ConnetionString)) { connection.Open(); var query = $@" SELECT * FROM Items WHERE ('{searchParams.ItemId ?? ""}' = '' OR ItemNo = '{searchParams.ItemId}') AND ('{searchParams.Barcode ?? ""}' = '' OR Barcode = '{searchParams.Barcode}') AND ({searchParams.SortCode} = 0 OR SortCode1 = {searchParams.SortCode} OR SortCode2 = {searchParams.SortCode} OR SortCode3 = {searchParams.SortCode}) "; /// AND (totqty >= {searchParams.QtyInStock}) if (searchParams.IsBlocked.HasValue) { query += $"AND(TITLE {(searchParams.IsBlocked.Value ? "=" : "<>")} '{ SafeConvert.ToPervasiveString("כ")}') "; } if (hasSiteSync) { query += $"AND SITESYNC = {(searchParams.SyncToSite ? 1 : 0)} "; } if (hasKupaSync) { query += $"AND KUPASYNC = {(searchParams.SyncToFunds ? 1 : 0)} "; } if (hasUpdDate) { query += $@" AND({ PervasiveDBHelper.ToPervasiveDate(searchParams.FromUpdateDate)} <= 0 OR UitemDate >= { PervasiveDBHelper.ToPervasiveDate(searchParams.FromUpdateDate)}) AND({ PervasiveDBHelper.ToPervasiveDate(searchParams.ToUpdateDate)} <= 0 OR UitemDate <= { PervasiveDBHelper.ToPervasiveDate(searchParams.ToUpdateDate)}) "; } query += "ORDER BY ItemNo DESC"; var command = new OdbcCommand(query); command.Connection = connection; /// throw new Exception(query); using (var reader = command.ExecuteReader()) { while (reader.Read()) { items.Add(new Item { Id = SafeConvert.ToString(reader["ItemNo"]), ItemNameHE = SafeConvert.ToString(reader["ItemName"]), ItemNameEN = SafeConvert.ToString(reader["Ename"]), Barcode = SafeConvert.ToString(reader["Barcode"]), Characteristic1 = SafeConvert.ToString(reader["ifun1"]), Characteristic2 = SafeConvert.ToString(reader["ifun2"]), CurrencyCode1 = SafeConvert.ToInt32(reader["FC1"]), CurrencyCode2 = SafeConvert.ToInt32(reader["FC2"]), CurrencyCode3 = SafeConvert.ToInt32(reader["FC3"]), CurrencyCode4 = SafeConvert.ToInt32(reader["FC4"]), CurrencyCode5 = SafeConvert.ToInt32(reader["FC5"]), CurrencyCode6 = SafeConvert.ToInt32(reader["FC6"]), CurrencyCode7 = SafeConvert.ToInt32(reader["FC7"]), CurrencyCode8 = SafeConvert.ToInt32(reader["FC8"]), CurrencyCode9 = SafeConvert.ToInt32(reader["FC9"]), CurrencyCode10 = SafeConvert.ToInt32(reader["FC10"]), Price1 = SafeConvert.ToSingle(reader["Prc1"]), Price2 = SafeConvert.ToSingle(reader["Prc2"]), Price3 = SafeConvert.ToSingle(reader["Prc3"]), Price4 = SafeConvert.ToSingle(reader["Prc4"]), Price5 = SafeConvert.ToSingle(reader["Prc5"]), Price6 = SafeConvert.ToSingle(reader["Prc6"]), Price7 = SafeConvert.ToSingle(reader["Prc7"]), Price8 = SafeConvert.ToSingle(reader["Prc8"]), Price9 = SafeConvert.ToSingle(reader["Prc9"]), Price10 = SafeConvert.ToSingle(reader["Prc10"]), SortCode1 = SafeConvert.ToInt32(reader["SortCode1"]), SortCode2 = SafeConvert.ToInt32(reader["SortCode2"]), SortCode3 = SafeConvert.ToInt32(reader["SortCode3"]), SupplerId1 = SafeConvert.ToInt32(reader["Supl1"]), SupplerId2 = SafeConvert.ToInt32(reader["Supl2"]), SupplerId3 = SafeConvert.ToInt32(reader["Supl3"]), SupplerId4 = SafeConvert.ToInt32(reader["Supl4"]), Unit = SafeConvert.ToString(reader["Unit"]), ///QtyInStock = SafeConvert.ToSingle(reader["totqty"]), VatFlag = SafeConvert.ToBoolean(reader["Patur"]), SyncToSite = SafeConvert.ToBoolean(reader.ReadOrDefault <bool>("SITESYNC")), SyncToFunds = SafeConvert.ToBoolean(reader.ReadOrDefault <bool>("KUPASYNC")), IsBlocked = SafeConvert.ToBoolean(reader["TITLE"]), UpdatedDate = FromPervasiveDate(Convert.ToDouble(reader.ReadOrDefault <double>("UitemDate"))), ProviderId = 0 }); } } return(items); } } catch (Exception ex) { Debug.WriteLine(ex.Message); throw; } }
public string CreateItem(Item item) { try { using (var connection = new OdbcConnection(this.ConnetionString)) { connection.Open(); var query = $@" INSERT INTO Items ( ItemNo, ItemName, Ename, Barcode, ifun1, ifun2, FC1, FC2, FC3, FC4, FC5, FC6, FC7, FC8, FC9, FC10, Prc1, Prc2, Prc3, Prc4, Prc5, Prc6, Prc7, Prc8, Prc9, Prc10, SortCode1, SortCode2, SortCode3, Supl1, Supl2, Supl3, Supl4, Unit, Patur, SITESYNC, KUPASYNC, TITLE, UitemDate ) ( SELECT '{SafeConvert.ToPervasiveString(item.Id)}', '{SafeConvert.ToPervasiveString(item.ItemNameHE)}', '{SafeConvert.ToPervasiveString(item.ItemNameEN)}', '{item.Barcode}', '{SafeConvert.ToPervasiveString(item.Characteristic1)}', '{SafeConvert.ToPervasiveString(item.Characteristic2)}', {item.CurrencyCode1}, {item.CurrencyCode2}, {item.CurrencyCode3}, {item.CurrencyCode4}, {item.CurrencyCode5}, {item.CurrencyCode6}, {item.CurrencyCode7}, {item.CurrencyCode8}, {item.CurrencyCode9}, {item.CurrencyCode10}, {item.Price1}, {item.Price2}, {item.Price3}, {item.Price4}, {item.Price5}, {item.Price6}, {item.Price7}, {item.Price8}, {item.Price9}, {item.Price10}, {item.SortCode1}, {item.SortCode2}, {item.SortCode3}, {item.SupplerId1}, {item.SupplerId2}, {item.SupplerId3}, {item.SupplerId4}, '{item.Unit}', {(item.VatFlag ? 1 : 0)}, {(item.SyncToSite ? 1 : 0)}, {(item.SyncToFunds ? 1 : 0)}, '{(item.IsBlocked ? SafeConvert.ToPervasiveString("כ") : "")}', {PervasiveDBHelper.ToPervasiveDate(DateTime.Now)} )"; var command = new OdbcCommand(query); command.Connection = connection; command.ExecuteNonQuery(); } return(item.Id); } catch (Exception ex) { Debug.WriteLine(ex.Message); throw; } }
public string UpdateItem(Item item) { try { using (var connection = new OdbcConnection(this.ConnetionString)) { connection.Open(); var query = $@" UPDATE Items SET ItemName = '{SafeConvert.ToPervasiveString(item.ItemNameHE)}', Ename = '{SafeConvert.ToPervasiveString(item.ItemNameEN)}', Barcode = '{item.Barcode}', ifun1 = '{SafeConvert.ToPervasiveString(item.Characteristic1)}', ifun2 = '{SafeConvert.ToPervasiveString(item.Characteristic2)}', FC1 = {item.CurrencyCode1}, FC2 = {item.CurrencyCode2}, FC3 = {item.CurrencyCode3}, FC4 = {item.CurrencyCode4}, FC5 = {item.CurrencyCode5}, FC6 = {item.CurrencyCode6}, FC7 = {item.CurrencyCode7}, FC8 = {item.CurrencyCode8}, FC9 = {item.CurrencyCode9}, FC10 = {item.CurrencyCode10}, Prc1 = {item.Price1}, Prc2 = {item.Price2}, Prc3 = {item.Price3}, Prc4 = {item.Price4}, Prc5 = {item.Price5}, Prc6 = {item.Price6}, Prc7 = {item.Price7}, Prc8 = {item.Price8}, Prc9 = {item.Price9}, Prc10 = {item.Price10}, SortCode1 = {item.SortCode1}, SortCode2 = {item.SortCode2}, SortCode3 = {item.SortCode3}, Supl1 = {item.SupplerId1}, Supl2 = {item.SupplerId2}, Supl3 = {item.SupplerId3}, Supl4 = {item.SupplerId4}, Unit = '{item.Unit}', Patur = {(item.VatFlag ? 1 : 0)}, SITESYNC = {(item.SyncToSite ? 1 : 0)}, KUPASYNC = {(item.SyncToFunds ? 1 : 0)}, TITLE = '{(item.IsBlocked ? SafeConvert.ToPervasiveString("כ") : "")}', UitemDate = {PervasiveDBHelper.ToPervasiveDate(DateTime.Now)} WHERE ItemNo = '{item.Id}' "; var command = new OdbcCommand(query); command.Connection = connection; command.ExecuteNonQuery(); } return(item.Id); } catch (Exception ex) { Debug.WriteLine(ex.Message); throw; } }
// TODO ->> duplicate with DocumentsDAL // תנועות מלאי private bool CreateInventoryTransaction(InventoryTransaction transaction, OdbcCommand command) { try { var query = $@" INSERT INTO InvTrs ( Store, ItemNo, InOut, DocCode, Details, Subject, Agent, Voucher, AccNo, Qty, ""Sum"", FcSum, AuxQty, SerNo, Color, Size, BatchNo, Pkuda, TrsNo, ExpireDate, DATE ) ( SELECT {transaction.StoreNo}, '{transaction.ItemId}', '{transaction.InOut}', '{transaction.DocumentId}', '{SafeConvert.ToPervasiveString(transaction.Notes)}', {transaction.SubjectId}, {transaction.AgentId}, '{transaction.Asmac}', {transaction.AccountId}, {transaction.Quantity}, {transaction.Total}, {transaction.FcTotal}, {transaction.AuxQuantity}, '{transaction.SerialNo}', '{transaction.Color}', '{transaction.Size}', '{transaction.BatchNo}', {transaction.CommandNo}, {transaction.TransactionId}, {PervasiveDBHelper.ToPervasiveDate(transaction.ExpireDate)}, {PervasiveDBHelper.ToPervasiveDate(transaction.CreatedDate)} ) "; command.CommandText = query; command.ExecuteNonQuery(); return(true); } catch (Exception ex) { Debug.WriteLine(ex.Message); throw; } }
public BulkInsertStatus CreateInventory(Inventory inventory) { string CreateInventoryItemQuery(long transactionId, InventoryItem inventoryItem) { var query = $@" INSERT INTO INVTRS ( TrsNo, ItemNo, Pkuda, Store, SerNo, AccNo, Agent, Subject, AuxQty, BatchNo, Details, DocCode, ExpireDate, FcSum, InOut, Qty, Color, Size, ""Sum"", Voucher, date ) ( SELECT {transactionId}, '{SafeConvert.ToPervasiveString(inventoryItem.ItemId)}', {inventoryItem.CommandNo}, {inventoryItem.StoreNo}, '{inventoryItem.SerialNo}', {inventoryItem.AccountId}, {inventoryItem.AgentId}, {inventoryItem.SubjectNo}, {inventoryItem.AuxQty}, '{inventoryItem.BatchNo}', '{SafeConvert.ToPervasiveString(inventoryItem.Details)}', '{inventoryItem.DocumentCode}', {PervasiveDBHelper.ToPervasiveDate(inventoryItem.ExpireDate)}, {inventoryItem.FcTotal}, '{inventoryItem.InOut}', {inventoryItem.Quantity}, '{inventoryItem.Color}', '{inventoryItem.Size}', {inventoryItem.Total}, {inventoryItem.Voucher}, {PervasiveDBHelper.ToPervasiveDate(DateTime.Now)} ) "; return(query); } OdbcTransaction transaction = null; var result = new BulkInsertStatus(); try { using (var connection = new OdbcConnection(this.ConnetionString)) { connection.Open(); transaction = connection.BeginTransaction(); var command = new OdbcCommand(); command.Connection = connection; command.Transaction = transaction; inventory.Items.ForEach(inventoryItem => { var transactionId = PervasiveDBHelper.GetColumnMaxValue(command, "INVTRS", "TrsNo"); command.CommandText = CreateInventoryItemQuery(transactionId, inventoryItem); command.ExecuteNonQuery(); result.NumOfSuccesses++; }); transaction.Commit(); } return(result); } catch (Exception ex) { Debug.WriteLine(ex.Message); result.NumOfFailures++; throw; } }
public int CreateAccount(Account account) { try { using (var connection = new OdbcConnection(this.ConnetionString)) { connection.Open(); var query = $@" INSERT INTO Accounts ( AccNo, AuxAccNo, Name, Address, City, ZipCode, Reduc100, CreditLevel, Email, OsekNo, Fax, Phone1, Phone2, SortCode1, SortCode2, SortCode34, SortCode5, SortCode6, VatFlag, Cdate, Udate ) ( SELECT {account.Id}, '{account.ProviderId}', '{SafeConvert.ToPervasiveString(account.Name)}', '{SafeConvert.ToPervasiveString(account.Address)}', '{SafeConvert.ToPervasiveString(account.City)}', '{account.ZipCode}', {account.ComercialReduc * 100}, {account.CreditLevel}, '{account.Email}', '{account.OsekNo}', '{account.Fax}', '{account.Phone1}', '{account.Phone2}', {account.SortCode1}, {account.SortCode2}, {account.SortCode3}, {account.SortCode5}, {account.SortCode6}, '{account.VatFlag}', {PervasiveDBHelper.ToPervasiveDate(DateTime.Now)}, {PervasiveDBHelper.ToPervasiveDate(DateTime.Now)} ) "; var command = new OdbcCommand(query); command.Connection = connection; command.ExecuteNonQuery(); } return(account.Id); } catch (Exception ex) { Debug.WriteLine(ex.Message); throw; } }