/// <summary>Перемістити паллету</summary>
/// <param name="palletCode">Унікальний код паллети</param>
/// <param name="newPreviousPallet">Унікальний код палети до якої підставиться паллета, що переміщується</param>
public static void MovePalletToNewPlace(long palletCode, long newPreviousPallet = 0)
{
//Просто оновити попередню паллету для нашої паллети
IQuery query = DB.NewQuery("UPDATE FilledCell SET PreviousCode=@PreviousCode WHERE PalletCode=@PalletCode");
query.AddInputParameter("PreviousCode", newPreviousPallet);
query.AddInputParameter("PalletCode", palletCode);
query.Execute();
}
public static long GetMeasureForNomenclature(long nomenclatureId, long classifier)
{
IQuery query = DB.NewQuery(@"SELECT Id FROM Measures m WHERE m.Nomenclature=@Nomenclature AND m.Classifier=@Classifier");
query.AddInputParameter("Nomenclature", nomenclatureId);
query.AddInputParameter("Classifier", classifier);
object idObj = query.SelectScalar();
return(idObj == null ? 0 : Convert.ToInt64(idObj));
}
private DataTable updateIncoiceList(string enterValue)
{
IQuery query = DB.NewQuery(@"
SELECT p.Id,RTRIM('№ '+CAST(p.SupplierIncomeNumber AS VARCHAR)) Description
FROM AcceptancePlan p
WHERE
CAST(p.SupplierIncomeNumber AS VARCHAR) like '%'+@Number+'%'
AND Car=@Car");
query.AddInputParameter("Number", enterValue);
query.AddInputParameter("Car", ScreenData.Car.Key);
DataTable table = query.SelectToTable();
return(table);
}
private DataTable UpdateNomenclature(string enterValue, bool isTare)
{
IQuery query = DB.NewQuery(
@"SELECT Id,RTRIM(Description)Description FROM Nomenclature
WHERE MarkForDeleting = 0 and IsTare = @IsTare
and (@IsTare = 1 or (UnitsQuantityPerPallet>0 and UnitsQuantityPerPack>0))
and Description like '%'+@Description+'%'
ORDER BY Description");
query.AddInputParameter("Description", enterValue);
query.AddInputParameter("IsTare", isTare);
DataTable table = query.SelectToTable();
return(table);
}
public static long GetBoxForPallet(long palletId)
{
IQuery query = DB.NewQuery(@"WITH
NomenclatureTable AS (SELECT Nomenclature,MeasureUnit FROM StockBalance b WHERE b.UniqueCode=@Pallet)
, BoxTable AS (SELECT * FROM Measures WHERE Classifier=@Box)
SELECT DISTINCT TOP 1 b.Id
FROM NomenclatureTable n
JOIN BoxTable b ON b.Id=n.MeasureUnit");
query.AddInputParameter("Pallet", palletId);
query.AddInputParameter("Box", ClassifierUnits.Box.Id);
object idObj = query.SelectScalar();
return(idObj == null ? 0 : Convert.ToInt64(idObj));
}
private bool phoneIsNotUnique(long longMobile)
{
IQuery query = DB.NewQuery("select top 1 cat.Description from Users as cat where cat.MobilePhone = @Phone and cat.Id <> @CurrentUserId");
query.AddInputParameter("CurrentUserId", User.Id);
query.AddInputParameter("Phone", longMobile);
object result = query.SelectScalar();
if (result == null)
{
return(false);
}
string.Format(WARNING_1, result.ToString().Trim()).WarningBox();
return(true);
}
/// <summary>Прибрати паллету зі скалду</summary>
/// <param name="palletCode">Унікальний код паллети</param>
public static void RemovePallet(long palletCode)
{
//Видалити рядок (в нашому випадку елемент довідника) з таблиці
IQuery query = DB.NewQuery("DELETE FROM FilledCell PalletCode=@PalletCode");
query.AddInputParameter("PalletCode", palletCode);
query.Execute();
}
public static DateTime GetDateOfManufactureById(long partyId)
{
IQuery query = DB.NewQuery("SELECT DateOfManufacture FROM Parties WHERE Id=@Id");
query.AddInputParameter("Id", partyId);
object date = query.SelectScalar();
return(date == null ? DateTime.MinValue : Convert.ToDateTime(date));
}
internal static Parties Find(long nomenclatureId, DateTime productionDate, int shelfLifeDays)
{
IQuery query = DB.NewQuery(@"SELECT Top 1 Id
FROM Parties
WHERE markForDeleting = 0
and Nomenclature=@Nomenclature
AND CAST(DateOfManufacture AS DATE)=@Date
and CAST(TheDeadlineSuitability AS DATE)=@ExpirationDate");
query.AddInputParameter("Date", productionDate.Date);
query.AddInputParameter("ExpirationDate", productionDate.AddDays(shelfLifeDays).Date);
query.AddInputParameter("Nomenclature", nomenclatureId);
var partyId = query.SelectInt64();
return(new Parties()
{
ReadingId = partyId
});
}
private DataTable UpdateCar(string enterValue)
{
IQuery query = DB.NewQuery(
"SELECT Id,RTRIM(Description)Description FROM Cars WHERE Description like '%'+@Car+'%' AND IsGroup=0 ORDER BY Description");
query.AddInputParameter("Car", enterValue);
DataTable table = query.SelectToTable();
return(table);
}
private static DataTable updateCarList(string enterValue)
{
IQuery query = DB.NewQuery(
@"
select distinct pa.Car Id, RTRIM(c.Description) Description from AcceptancePlan pa
join Cars c on c.Id = pa.Car
where pa.State < 2 and pa.MarkForDeleting = 0 and c.Description like '%'+@CarNumber+'%'
order by RTRIM(c.Description)
");
query.AddInputParameter("CarNumber", enterValue);
DataTable table = query.SelectToTable();
return(table);
}
private long getMovementId()
{
IQuery query = DB.NewQuery(@"
DECLARE @ShipmentPlanType uniqueidentifier='029B0572-E5B5-48CD-9805-1211319A5633'
SELECT TOP 1 Id
FROM Movement
WHERE Source=@Id AND SourceType=@ShipmentPlanType
ORDER BY Date DESC");
query.AddInputParameter("Id", Id);
object movementId = query.SelectScalar();
return(movementId == null ? 0 : Convert.ToInt64(movementId));
}
/// <summary>Отримати коротку інформацію зі штрихкоду</summary>
/// <typeparam name="T">Тип даних</typeparam>
/// <param name="barcode">Штрих-код</param>
/// <param name="id">ID</param>
/// <param name="description">Опис</param>
/// <returns>Чи було отримано коротку інформацію зі штрих-коду</returns>
public static bool GetCutDataByBarcode <T>(string barcode, out long id, out string description)
{
Type type = typeof(T);
if (CheckMatchingBarcodeAndType(barcode, type, out id))
{
string command = String.Format("SELECT RTRIM(Description) FROM {0} WHERE Id=@Id", type.Name);
IQuery query = DB.NewQuery(command);
query.AddInputParameter("Id", id);
object descriptionObj = query.SelectScalar();
if (descriptionObj != null)
{
description = descriptionObj.ToString();
return(true);
}
}
id = 0;
description = String.Empty;
return(false);
}
/// <summary>Получить таблицу ссылающихся объектов</summary>
/// <param name="links"></param>
/// <param name="id"></param>
/// <returns>Таблица ссылающихся объектов</returns>
private DataTable getReferedTable(MarkedObjectLinks links, long id)
{
string format = string.Empty;
string format_holders = string.Empty;
StringBuilder holders = new StringBuilder();
if (links.DBObjectType == AramisObjectType.Catalog)
{
format = query_catalogs_format;
format_holders = query_catalogs_holders_format;
}
else if (links.DBObjectType == AramisObjectType.Document)
{
format = query_documents_format;
format_holders = query_documents_holders_format;
}
foreach (ObjectReference objRef in links.ObjectReferences)
{
string dBSubtableName = string.Concat("Sub", objRef.TableName, objRef.SubTableName);
string additionalString = string.Empty;
if (objRef.CommonSubtableName != string.Empty)
{
additionalString = string.Concat(
"where ",
"Common",
objRef.CommonSubtableName,
".Guid = '",
SystemConfiguration.DBConfigurationTree[objRef.TableName].GUID.ToString(),
"'");
}
string holder = String.Format(
format_holders,
objRef.TableName,
objRef.SubTableName,
objRef.FieldName,
objRef.SubTableName == string.Empty
? objRef.TableName
: dBSubtableName,
objRef.SubTableName == string.Empty ? string.Empty : "Doc",
objRef.SubTableName == string.Empty
? "0"
: dBSubtableName + ".LineNumber",
additionalString,
objRef.SubTableName == string.Empty
? string.Empty
: String.Format("left join {0} on {0}.Id = {1}.IdDoc", objRef.TableName, dBSubtableName));
holders.Append(holders.Length == 0 ? holder : "\r\nunion all\r\n\r\n" + holder);
}
IQuery query = DB.NewQuery(string.Format(format, links.ObjectName, holders));
query.AddInputParameter("Id", id);
DataTable table = query.SelectToTable();
if (table == null)
{
throw new NullReferenceException("Ошибка виполнения запроса получения данных, о местах в которых есть ссылки на объект!");
}
return(table);
}
