public bool ComplateMovement(long documentId, bool forceCompletion, out string errorMessage)
{
errorMessage = string.Empty;
var document = new Moving() { ReadingId = documentId };
using (var locker = new DatabaseObjectLocker(document))
{
if (!locker.LockForCurrentPdtThread()) return false;
document.State = StatesOfDocument.Completed;
return document.Write() == WritingResult.Success;
}
}
private bool createMoving(long shipmentId)
{
var document = new Moving();
document.SetRef("PickingPlan", shipmentId);
document.Date = DateTime.Now;
var q = DB.NewQuery(@"select Nomenclature, sum(Quantity) [Quantity], min(LineNumber) LineNumber
from SubShipmentPlanNomenclatureInfo
where IdDoc = @IdDoc
group by Nomenclature
order by LineNumber");
q.AddInputParameter("IdDoc", shipmentId);
var docTable = document.NomenclatureInfo;
q.Foreach(qResult =>
{
var nomenclarute = Convert.ToInt64(qResult["Nomenclature"]);
var quantity = Convert.ToDecimal(qResult["Quantity"]);
var docRow = docTable.GetNewRow(document);
docRow[document.Nomenclature] = nomenclarute;
docRow[document.PlanValue] = quantity;
docRow[document.RowState] = RowsStates.PlannedPicking;
docRow.AddRowToTable(document);
});
var result = document.Write();
return result == WritingResult.Success;
}
public bool WritePickingResult(long documentId, int currentLineNumber, DataTable resultTable, long partyId, out int sameWareNextTaskLineNumber)
{
sameWareNextTaskLineNumber = 0;
var currentTime = SystemConfiguration.ServerDateTime;
var document = new Moving() { ReadingId = documentId };
using (var locker = new DatabaseObjectLocker(document))
{
if (!locker.LockForCurrentPdtThread()) return false;
var docTable = document.NomenclatureInfo;
var palletCode = Convert.ToInt64(resultTable.Rows[0][document.PalletCode.ColumnName]);
var wareRow = docTable.Rows[currentLineNumber - 1];
var newPickingTaskPlan = 0M;
for (int rowIndex = 0; rowIndex < resultTable.Rows.Count; rowIndex++)
{
var resultWareRow = resultTable.Rows[rowIndex];
var plan = Convert.ToDecimal(resultWareRow[document.PlanValue.ColumnName]);
var fact = Convert.ToDecimal(resultWareRow[document.FactValue.ColumnName]);
DataRow docRow = null;
var isTare = rowIndex != 0;
if (isTare)
{
docRow = docTable.GetNewRow(document);
docRow[document.Nomenclature] = Convert.ToInt64(resultWareRow[document.Nomenclature.ColumnName]);
}
else
{
docRow = wareRow;
docRow[document.Party] = partyId;
newPickingTaskPlan = plan - fact;
}
docRow[document.PlanValue] = plan;
docRow[document.FactValue] = fact;
docRow[document.StartCell] = resultWareRow[document.StartCell.ColumnName];
docRow[document.FinalCell] = Consts.RedemptionCell.Id;
docRow[document.StartCodeOfPreviousPallet] =
resultWareRow[document.StartCodeOfPreviousPallet.ColumnName];
docRow[document.FinalCodeOfPreviousPallet] = 0L;
docRow[document.RowState] = (int)RowsStates.Completed;
docRow[document.RowDate] = currentTime;
docRow[document.PalletCode] = palletCode;
if (isTare)
{
docRow.AddRowToTable(document);
}
}
var boxesFinder = new BoxesFinder(resultTable, palletCode);
if (boxesFinder.BoxesRowAdded)
{
var boxesRow = document.NomenclatureInfo.GetNewRow(document);
boxesRow[document.Nomenclature] = boxesFinder.BoxesRow[document.Nomenclature.ColumnName];
boxesRow[document.StartCodeOfPreviousPallet] = 0L;
boxesRow[document.FinalCodeOfPreviousPallet] = 0L;
boxesRow[document.RowState] = (int)RowsStates.Completed;
boxesRow[document.RowDate] = currentTime;
boxesRow[document.FactValue] = boxesFinder.BoxesRow[document.FactValue.ColumnName];
boxesRow[document.FinalCell] = wareRow[document.FinalCell];
boxesRow[document.StartCell] = wareRow[document.StartCell];
boxesRow[document.PalletCode] = palletCode;
boxesRow.AddRowToTable(document);
}
if (newPickingTaskPlan > 0)
{
var newTaskRow = document.NomenclatureInfo.GetNewRow(document);
newTaskRow[document.PlanValue] = newPickingTaskPlan;
newTaskRow[document.Nomenclature] = wareRow[document.Nomenclature.ColumnName];
newTaskRow[document.RowState] = (int)RowsStates.PlannedPicking;
newTaskRow.AddRowToTable(document);
sameWareNextTaskLineNumber = Convert.ToInt32(newTaskRow[CONSTS.LINE_NUMBER_COLUMN_NAME]);
wareRow[document.PlanValue] = wareRow[document.FactValue];
}
var result = document.Write();
return result == WritingResult.Success;
}
}
public bool WriteMovementResult(long documentId, DataTable resultTable)
{
var q = DB.NewQuery("select max(LineNumber) lastRowNumber from SubMovingNomenclatureInfo where IdDoc = @IdDoc");
q.AddInputParameter("IdDoc", documentId);
var lastLineNumber = q.SelectInt64();
var document = new Moving();
var currentTime = SystemConfiguration.ServerDateTime;
// ��� ������ ������ ���� � ���� �����, �.�. ����� ��������� ������� ����� ���������� � ��������, ��� ��� �� null
var docTable = document.NomenclatureInfo;
var palletCode = Convert.ToInt64(resultTable.Rows[0][document.PalletCode.ColumnName]);
new BoxesFinder(resultTable, palletCode);
var userId = getUserId();
for (int rowIndex = 0; rowIndex < resultTable.Rows.Count; rowIndex++)
{
var sourceRow = resultTable.Rows[rowIndex];
var newRow = docTable.GetNewRow(document);
newRow[document.RowState] = (int)RowsStates.Completed;
newRow[document.RowDate] = currentTime;
var isTare = rowIndex != 0;
if (!isTare)
{
var sticker = new Stickers() { ReadingId = palletCode };
newRow[document.Party] = sticker.GetRef("Party");
}
newRow[document.Nomenclature] = Convert.ToInt64(sourceRow[document.Nomenclature.ColumnName]);
newRow[document.PlanValue] = Convert.ToDecimal(sourceRow[document.PlanValue.ColumnName]);
newRow[document.FactValue] = Convert.ToDecimal(sourceRow[document.FactValue.ColumnName]);
newRow[document.PalletCode] = palletCode;
newRow[document.StartCodeOfPreviousPallet] = Convert.ToInt64(sourceRow[document.StartCodeOfPreviousPallet.ColumnName]);
newRow[document.FinalCodeOfPreviousPallet] = Convert.ToInt64(sourceRow[document.FinalCodeOfPreviousPallet.ColumnName]);
newRow[document.StartCell] = Convert.ToInt64(sourceRow[document.StartCell.ColumnName]);
newRow[document.FinalCell] = Convert.ToInt64(sourceRow[document.FinalCell.ColumnName]);
newRow[document.Employee] = userId;
newRow[CONSTS.LINE_NUMBER_COLUMN_NAME] = lastLineNumber + rowIndex + 1;
newRow.AddRowToTable(document);
}
var rowsToInsert = document.NomenclatureInfo;
q = DB.NewQuery(@"insert into subMovingNomenclatureInfo([IdDoc],[LineNumber],[PalletCode],[Nomenclature],
[PlanValue],[FactValue],[RowState],[RowDate],
[StartCodeOfPreviousPallet] ,[FinalCodeOfPreviousPallet],
[StartCell],[FinalCell], [Party], [Employee])
select @IdDoc, [LineNumber],[PalletCode],[Nomenclature],
[PlanValue],[FactValue],[RowState],[RowDate],
[StartCodeOfPreviousPallet] ,[FinalCodeOfPreviousPallet],
[StartCell],[FinalCell], [Party], [Employee]
from @table");
q.AddInputTVPParameter("table", rowsToInsert, "dbo.tvp_Moving_NomenclatureInfo");
q.AddInputParameter("IdDoc", documentId);
q.Execute();
return q.ThrowedException == null;
}
public bool GetNewMovementId(out long documentId)
{
var doc = new Moving();
doc.SetRef("Responsible", getUserId());
doc.Date = SystemConfiguration.ServerDateTime;
doc.SetRef("Responsible", getUserId());
var writeResult = doc.Write();
documentId = doc.Id;
return writeResult == WritingResult.Success;
}
public bool GetPickingTask(long documentId, long palletId, int predefinedTaskLineNumber, int currentLineNumber,
out long stickerId, out long wareId, out string wareDescription,
out long cellId, out string cellDescription,
out long partyId, out DateTime productionDate,
out int unitsPerBox, out int unitsToPick,
out int lineNumber)
{
const string sql =
@"
select top 1 cap.[State],
task.PalletCode stickerId, task.LineNumber,
task.Nomenclature wareId, rtrim(n.[description]) wareDescription,
task.StartCell cellId, ISNULL(rtrim(c.[Description]), '') cellDescription,
task.Party partyId, isnull(p.DateOfManufacture, '0001-01-01') productionDate,
n.UnitsQuantityPerPack unitsPerBox, task.PlanValue unitsToPick
from SubMovingNomenclatureInfo task
join Nomenclature n on n.Id = task.Nomenclature
left join Cells c on c.Id = task.StartCell
left join Parties p on p.Id = task.Party
join Moving cap on cap.Id = task.IdDoc
where IdDoc = @IdDoc and (RowState = @PlannedPickingState or (RowState = @ProcessingState and Employee = @userId))
and (@currentLineNumber = 0 or task.LineNumber = @currentLineNumber)
and (@nomenclature = 0 or task.Nomenclature = @nomenclature)
order by [LineNumber]";
var q = DB.NewQuery(sql);
q.AddInputParameter("IdDoc", documentId);
q.AddInputParameter("PlannedPickingState", RowsStates.PlannedPicking);
q.AddInputParameter("ProcessingState", RowsStates.Processing);
q.AddInputParameter("userId", getUserId());
q.AddInputParameter("currentLineNumber", predefinedTaskLineNumber);
var nomenclatureId = 0L;
if (palletId != 0)
{
var sticker = new Stickers() { ReadingId = palletId };
nomenclatureId = sticker.GetRef("Nomenclature");
}
q.AddInputParameter("nomenclature", nomenclatureId);
stickerId = wareId = cellId = partyId = unitsToPick = unitsPerBox = lineNumber = 0;
wareDescription = cellDescription = string.Empty;
productionDate = DateTime.MinValue;
// � ���������� ����� ������������� �������� ������ � ���������� �������� palletId, � ����� � �� �������������
using (var locker = new DatabaseObjectLocker(typeof(Moving), documentId))
{
if (!locker.LockForCurrentPdtThread()) return false;
var qResult = q.SelectRow();
if (qResult == null)
{
return true;
}
var documentState = (StatesOfDocument)Convert.ToInt32(qResult["State"]);
if (documentState == StatesOfDocument.Planned)
{
var moving = new Moving() { ReadingId = documentId };
moving.State = StatesOfDocument.Processing;
moving.Write();
}
stickerId = Convert.ToInt64(qResult["stickerId"]);
wareId = Convert.ToInt64(qResult["wareId"]);
wareDescription = qResult["wareDescription"].ToString();
cellId = Convert.ToInt64(qResult["cellId"]);
cellDescription = qResult["cellDescription"].ToString();
partyId = Convert.ToInt64(qResult["partyId"]);
productionDate = (DateTime)qResult["productionDate"];
unitsPerBox = Convert.ToInt32(qResult["unitsPerBox"]);
unitsToPick = Convert.ToInt32(qResult["unitsToPick"]);
lineNumber = Convert.ToInt32(qResult["LineNumber"]);
if (currentLineNumber > 0)
{
documentId.SetRowState(typeof(Moving), currentLineNumber, RowsStates.PlannedPicking);
}
documentId.SetRowState(typeof(Moving), lineNumber, RowsStates.Processing, employee: getUserId());
}
return true;
}
private void fillMovement(Moving movement, MovementFillingInfo info)
{
// Query query = DB.NewQuery(@"
//--DECLARE @Nomenclature BIGINT=1;
//--DECLARE @Measure BIGINT=1;
//--DECLARE @Party BIGINT=2;
//DECLARE @SourceType uniqueidentifier='029B0572-E5B5-48CD-9805-1211319A5633';
//
//WITH
// PalletOrder AS (SELECT f.PalletCode,ROW_NUMBER() OVER(ORDER BY f.CreationDate) PalletOrder FROM FilledCell f)
//,AcceptedCode AS (
// SELECT DISTINCT a.NomenclatureCode code
// FROM SubAcceptanceOfGoodsNomenclatureInfo a
// WHERE a.NomenclatureParty=@Party
//
// EXCEPT
//
// SELECT DISTINCT n.NomenclatureCode
// FROM Movement m
// JOIN SubMovementNomenclatureInfo n ON n.IdDoc=m.Id
// JOIN ShipmentPlan p ON p.Id=m.Source AND m.SourceType=@SourceType
// WHERE m.MarkForDeleting=0 AND n.RowState=0 AND n.NomenclatureParty=@Party AND n.Nomenclature=@Nomenclature AND n.NomenclatureMeasure=@Measure)
//,PreparedData AS (
// SELECT
// b.Cell,b.ExpariedDate,b.UniqueCode,b.Quantity,b.MeasureUnit,
// ROW_NUMBER() OVER (PARTITION BY b.Cell ORDER BY p.PalletOrder DESC,b.ExpariedDate DESC) RowNumber
// FROM StockBalance b
// JOIN PalletOrder p ON p.PalletCode=b.UniqueCode
// JOIN AcceptedCode a ON a.code=b.UniqueCode
// WHERE b.Nomenclature=@Nomenclature AND b.MeasureUnit=@Measure AND b.State=4)
//
//SELECT *
//FROM PreparedData");
// query.AddInputParameter("Nomenclature", info.Nomenclature);
// query.AddInputParameter("Measure", info.Measure);
// query.AddInputParameter("Party", info.Party);
// DataTable table = query.SelectToTable();
// double howIsUsed = 0;
// if (table != null)
// {
// foreach (DataRow row in table.Rows)
// {
// double quantity = Convert.ToDouble(row["Quantity"]);
// howIsUsed += quantity;
// DataRow newRow = movement.NomenclatureInfo.GetNewRow(movement);
// newRow[movement.NomenclatureCode] = row["UniqueCode"];
// newRow.SetRefValueToRowCell(movement, movement.Nomenclature, info.Nomenclature, typeof(Nomenclature));
// newRow.SetRefValueToRowCell(movement, movement.NomenclatureMeasure, info.Measure, typeof(Measures));
// newRow.SetRefValueToRowCell(movement, movement.NomenclatureParty, info.Party, typeof(Parties));
// newRow[movement.NomenclatureCount] = quantity;
// newRow.SetRefValueToRowCell(movement, movement.SourceCell, row["Cell"], typeof(Cells));
// // newRow.SetRefValueToRowCell(movement, movement.DestinationCell, Cells.Buyout.Id, typeof(Cells));
// newRow[movement.RowState] = StatesOfDocument.Planned;
// newRow.AddRowToTable(movement);
// if (howIsUsed >= info.Count)
// {
// break;
// }
// }
// }
// movement.SetSubtableModified(movement.NomenclatureInfo.TableName);
}
public void CreateMovement()
{
long movementId = getMovementId();
Moving movement;
if (movementId == 0)
{
WritingResult result = Write();
if (result == WritingResult.Success)
{
movement = new Moving {
PickingPlan = this, State = StatesOfDocument.Planned
};
movement.AfterWriting += movement_AfterWriting;
List <MovementFillingInfo> list = new List <MovementFillingInfo>();
foreach (DataRow row in NomenclatureInfo.Rows)
{
long nomenclature = Convert.ToInt64(row[Nomenclature]);
long party = Convert.ToInt64(row[Party]);
double count = Convert.ToDouble(row[Quantity]);
bool find = false;
foreach (MovementFillingInfo info in list)
{
if (info.Nomenclature == nomenclature && info.Party == party)
{
info.Count += count;
find = true;
break;
}
}
if (!find)
{
list.Add(new MovementFillingInfo
{
Count = count,
Nomenclature = nomenclature,
Party = party
});
}
}
fillMovement(movement, list);
}
else
{
return;
}
}
else
{
movement = new Moving()
{
ReadingId = movementId
};
}
UserInterface.Current.ShowItem(movement);
}
private void fillMovement(Moving movement, MovementFillingInfo info)
{
// Query query = DB.NewQuery(@"
//--DECLARE @Nomenclature BIGINT=1;
//--DECLARE @Measure BIGINT=1;
//--DECLARE @Party BIGINT=2;
//DECLARE @SourceType uniqueidentifier='029B0572-E5B5-48CD-9805-1211319A5633';
//
//WITH
// PalletOrder AS (SELECT f.PalletCode,ROW_NUMBER() OVER(ORDER BY f.CreationDate) PalletOrder FROM FilledCell f)
//,AcceptedCode AS (
// SELECT DISTINCT a.NomenclatureCode code
// FROM SubAcceptanceOfGoodsNomenclatureInfo a
// WHERE a.NomenclatureParty=@Party
//
// EXCEPT
//
// SELECT DISTINCT n.NomenclatureCode
// FROM Movement m
// JOIN SubMovementNomenclatureInfo n ON n.IdDoc=m.Id
// JOIN ShipmentPlan p ON p.Id=m.Source AND m.SourceType=@SourceType
// WHERE m.MarkForDeleting=0 AND n.RowState=0 AND n.NomenclatureParty=@Party AND n.Nomenclature=@Nomenclature AND n.NomenclatureMeasure=@Measure)
//,PreparedData AS (
// SELECT
// b.Cell,b.ExpariedDate,b.UniqueCode,b.Quantity,b.MeasureUnit,
// ROW_NUMBER() OVER (PARTITION BY b.Cell ORDER BY p.PalletOrder DESC,b.ExpariedDate DESC) RowNumber
// FROM StockBalance b
// JOIN PalletOrder p ON p.PalletCode=b.UniqueCode
// JOIN AcceptedCode a ON a.code=b.UniqueCode
// WHERE b.Nomenclature=@Nomenclature AND b.MeasureUnit=@Measure AND b.State=4)
//
//SELECT *
//FROM PreparedData");
// query.AddInputParameter("Nomenclature", info.Nomenclature);
// query.AddInputParameter("Measure", info.Measure);
// query.AddInputParameter("Party", info.Party);
// DataTable table = query.SelectToTable();
// double howIsUsed = 0;
// if (table != null)
// {
// foreach (DataRow row in table.Rows)
// {
// double quantity = Convert.ToDouble(row["Quantity"]);
// howIsUsed += quantity;
// DataRow newRow = movement.NomenclatureInfo.GetNewRow(movement);
// newRow[movement.NomenclatureCode] = row["UniqueCode"];
// newRow.SetRefValueToRowCell(movement, movement.Nomenclature, info.Nomenclature, typeof(Nomenclature));
// newRow.SetRefValueToRowCell(movement, movement.NomenclatureMeasure, info.Measure, typeof(Measures));
// newRow.SetRefValueToRowCell(movement, movement.NomenclatureParty, info.Party, typeof(Parties));
// newRow[movement.NomenclatureCount] = quantity;
// newRow.SetRefValueToRowCell(movement, movement.SourceCell, row["Cell"], typeof(Cells));
// // newRow.SetRefValueToRowCell(movement, movement.DestinationCell, Cells.Buyout.Id, typeof(Cells));
// newRow[movement.RowState] = StatesOfDocument.Planned;
// newRow.AddRowToTable(movement);
// if (howIsUsed >= info.Count)
// {
// break;
// }
// }
// }
// movement.SetSubtableModified(movement.NomenclatureInfo.TableName);
}
private void fillMovement(Moving movement, IEnumerable<MovementFillingInfo> list)
{
foreach (MovementFillingInfo info in list)
{
fillMovement(movement, info);
}
}
public void CreateMovement()
{
long movementId = getMovementId();
Moving movement;
if (movementId == 0)
{
WritingResult result = Write();
if (result == WritingResult.Success)
{
movement = new Moving { PickingPlan = this, State = StatesOfDocument.Planned };
movement.AfterWriting += movement_AfterWriting;
List<MovementFillingInfo> list = new List<MovementFillingInfo>();
foreach (DataRow row in NomenclatureInfo.Rows)
{
long nomenclature = Convert.ToInt64(row[Nomenclature]);
long party = Convert.ToInt64(row[Party]);
double count = Convert.ToDouble(row[Quantity]);
bool find = false;
foreach (MovementFillingInfo info in list)
{
if (info.Nomenclature == nomenclature && info.Party == party)
{
info.Count += count;
find = true;
break;
}
}
if (!find)
{
list.Add(new MovementFillingInfo
{
Count = count,
Nomenclature = nomenclature,
Party = party
});
}
}
fillMovement(movement, list);
}
else
{
return;
}
}
else
{
movement = new Moving() { ReadingId = movementId };
}
UserInterface.Current.ShowItem(movement);
}