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