public void InsertStock(IList <IStock> stocks)
{
string query =
"INSERT INTO Stock (ProductCode,Amount,ReservedAmount) VALUES(@productCode,@stockAmount,@reservedAmount)";
foreach (var stock in stocks)
{
_sqliteDataProvider.AddParameter("@productCode", stock.Code);
_sqliteDataProvider.AddParameter("@stockAmount", stock.StockAmount);
_sqliteDataProvider.AddParameter("@reservedAmount", stock.ReservedAmount);
_sqliteDataProvider.ExecuteScalar(query);
}
}
public IStock GetStockByCodeFromOrka(string stockCode)
{
const string query = "SELECT sm.stokkodu, sm.kalanmiktar, ss.fiyat FROM dbo.[STOK_MIZAN] sm " +
"join dbo.STK_STOKSATIR ss on sm.stokkodu = ss.stokkodu " +
"WHERE ss.firstdate IN (SELECT max(ss2.firstdate) FROM dbo.STK_STOKSATIR ss2 WHERE ss2.stokkodu=ss.stokkodu and ss2.IOdurum = 1) AND lower(sm.stokkodu) = lower(@stokkodu) and ss.IOdurum = 1"; _orkaDataProvider.AddParameter("@stokkodu", stockCode);
var row = _orkaDataProvider.ExecuteDataRows(query).FirstOrDefault();
return(row != null?CreateStockObject(row) : null);
}
public bool CreatePurchase(Purchase purchase)
{
const string query = "Insert into Purchases (ProductId,ProductCode,Amount,State,StartDate,SaleId,AssignedUser,TotalPrice," +
"UnitPrice,SupplierId,Currency,RequestedBySales,SalesUser,Comment) values " +
"(@ProductId,@ProductCode,@Amount,@State,@StartDate,@SaleId,@AssignedUser,@TotalPrice,@UnitPrice," +
"@SupplierId,@Currency,@RequestedBySales,@SalesUser,@comment);" +
"select last_insert_rowid();";
_sqliteDataProvider.AddParameter("@ProductId", purchase.ProductId);
_sqliteDataProvider.AddParameter("@ProductCode", purchase.ProductCode);
_sqliteDataProvider.AddParameter("@Amount", purchase.Quantity);
_sqliteDataProvider.AddParameter("@State", purchase.PurchaseState);
_sqliteDataProvider.AddParameter("@StartDate", purchase.PurchaseStartDate);
_sqliteDataProvider.AddParameter("@SaleId", purchase.OrderId);
_sqliteDataProvider.AddParameter("@SalesUser", purchase.SalesUserName);
_sqliteDataProvider.AddParameter("@AssignedUser", string.IsNullOrEmpty(purchase.PurchaserUserGuid) ?
null : purchase.PurchaserUserGuid);
_sqliteDataProvider.AddParameter("@TotalPrice", purchase.TotalPrice);
_sqliteDataProvider.AddParameter("@UnitPrice", purchase.UnitPrice);
_sqliteDataProvider.AddParameter("@SupplierId", purchase.SupplierId);
_sqliteDataProvider.AddParameter("@Currency", purchase.Currency);
_sqliteDataProvider.AddParameter("@RequestedBySales", purchase.RequestedBySales);
_sqliteDataProvider.AddParameter("@comment", purchase.Comment);
var queryResult = _sqliteDataProvider.ExecuteScalar(query);
return(queryResult != null);
}
public Product GetProductById(int productId)
{
const string query = "Select * From products Where Id = @productid";
_sqliteDataProvider.AddParameter("@productid", productId);
DataRow row = _sqliteDataProvider.ExecuteDataRows(query).FirstOrDefault();
return(CreateProduct(row));
}
public List <PackedProductExport> GetPackingExportInfo(int salesId)
{
var result = new List <PackedProductExport>();
const string query = "select pl.Id as palletId, pl.Width as width, pl.Height as height, " +
"pl.Depth as depth, pl.Weight as NetKG, pl.GrossWeight as GrossKG, " +
"p.Quantity as quantity, pr.Code as productCode, pr.DescriptionEng as description " +
"from Packing p, Pallets pl, Products pr " +
"where p.PalletId = pl.Id and p.ProductId = pr.Id and p.SalesId = @salesId ";
_sqliteDataProvider.AddParameter("@salesId", salesId);
var dataset = _sqliteDataProvider.ExecuteDataSet(query);
foreach (DataRow row in dataset.Tables[0].Rows)
{
result.Add(CreatePackedProductExportObject(row));
}
return(result.OrderBy(x => x.PalletId).ToList());
}
public Supplier GetSupplierById(int supplierId)
{
const string query = "Select * From suppliers Where Id = @supplierId";
_sqliteDataProvider.AddParameter("@supplierId", supplierId);
DataRow row = _sqliteDataProvider.ExecuteDataRows(query).FirstOrDefault();
if (row == null)
{
return(null);
}
return(CreateSupplier(row));
}
public IList <Sales> GetSalesBySalesPerson(string salesPeopleGuid)
{
const string query = "SELECT * From sales Where salespeople = @salesPeopleGuid";
_sqliteDataProvider.AddParameter("@salesPeopleGuid", salesPeopleGuid);
var dataSet = _sqliteDataProvider.ExecuteDataSet(query);
IList <Sales> sales = new List <Sales>();
foreach (DataRow row in dataSet.Tables[0].Rows)
{
sales.Add(CreateSales(row));
}
return(sales);
}
public Customer GetCustomerByName(string customerName)
{
const string query = "select * from customers where Name=@name";
_sqliteDataProvider.AddParameter("@name", customerName);
DataRow row = _sqliteDataProvider.ExecuteDataRows(query).FirstOrDefault();
return(row != null?CreateCustomerObject(row) : null);
}
public bool CreateExporter(IExporter exporter)
{
string query = "Insert into Exporters (name,address,tradeRegisterNo,vatNo,PhoneNumber,FaxNumber) values (@name,@address," +
"@tradeRegisterNo,@vatNo,@PhoneNumber,@FaxNumber);";
query += Environment.NewLine + "SELECT LAST_INSERT_ROWID();";
_sqliteDataProvider.AddParameter("@name", exporter.Name);
_sqliteDataProvider.AddParameter("@address", exporter.Address);
_sqliteDataProvider.AddParameter("@tradeRegisterNo", exporter.TradeRegisterNo);
_sqliteDataProvider.AddParameter("@vatNo", exporter.VatNo);
_sqliteDataProvider.AddParameter("@PhoneNumber", exporter.PhoneNumber);
_sqliteDataProvider.AddParameter("@FaxNumber", exporter.FaxNumber);
var exporterId = Convert.ToInt32(_sqliteDataProvider.ExecuteScalar(query));
foreach (var bankAccount in exporter.BankAccounts)
{
CreateBankAccount(bankAccount, exporterId);
}
return(true);
}