private static void CalculateAllFollowingCosts(DocumentData docData)
{
if (docData.GoodIds.Count == 0) return;
using (var db = Helpers.DB.PosConnect())
{
var allDocs =
db.Query(@"select docid,_date from GoodsMovement
where _date>[email protected] and [email protected] and PcsOut>0 and goodid in @GoodIds
group by docid,_date order by _date",
new { docDate = docData.DocumentDate, StoreId = docData.StoreId, GoodIds = docData.GoodIds })
.Select(x => new { DocumentId = (Guid)x.docid, DocumentDate = (DateTime)x._date }).ToList();
foreach (var doc in allDocs)
{
db.Execute(@"DECLARE @DocumentDate datetime
SET @DocumentDate=(select top 1 _date from GoodsMovement where [email protected])
UPDATE GM
SET MoneyOut=GM.PcsOut*ISNULL(t.Cost,0)
FROM GoodsMovement GM
JOIN (
SELECT GoodId,
COALESCE(Cost,
(SELECT TOP 1 MoneyIn/PcsIn FROM GoodsMovement mv
WHERE [email protected] AND mv._date<@DocumentDate AND mv.PcsIn>0 AND mv.MoneyIn>0 AND mv.GoodId=t.GoodId
order by mv._date desc),
0) Cost
FROM (
SELECT GoodId,
(sum(ISNULL(OpeningMoney,0)) + Sum(ISNULL(MoneyIn,0)) - sum(ISNULL(MoneyOut,0))) /
NULLIF((sum(ISNULL(OpeningPcs,0)) + Sum(ISNULL(PcsIn,0)) - sum(ISNULL(PcsOut,0))),0) Cost
FROM(
select GoodId, OpeningPcs, OpeningMoney, 0.00 PcsIn, 0.00 MoneyIn, 0.00 PcsOut, 0.00 MoneyOut
from Stock
where YEAR(Period) = YEAR(@DocumentDate)
and MONTH(Period) = MONTH(@DocumentDate)
and [email protected]
and goodid in @goods
union all
select GoodId, 0, 0, PcsIn, MoneyIn, PcsOut, MoneyOut
from GoodsMovement
where YEAR(_date)=YEAR(@DocumentDate)
and MONTH(_date)=MONTH(@DocumentDate)
and _date<[email protected]
and [email protected]
and docid <> @docid
and goodid in @goods)t
group by GoodId
) t
)t
ON [email protected] and t.GoodId=GM.GoodId
UPDATE R
SET R.Cost=(GM.MoneyOut/GM.PcsOut)
FROM ReceiptDetails R
JOIN GoodsMovement GM
ON R.DocId= GM.docid
AND R.GoodId=GM.goodid
WHERE [email protected]
AND [email protected]
", new { docid = doc.DocumentId, goods = docData.GoodIds, StoreId = docData.StoreId });
}
}
}
public override IMatrixData ProcessData(IMatrixData[] inputData, Parameters param, ref IMatrixData[] supplTables,
ref IDocumentData[] documents, ProcessInfo processInfo)
{
TextWriter defaultOut = Console.Out;
TextWriter defaultErr = Console.Error;
try{
if (documents == null){
documents = new IDocumentData[NumDocuments];
for (int i = 0; i < NumDocuments; i++){
documents[i] = new DocumentData();
}
}
TextWriter logger = null;
if (documents.Length > 0){
logger = new StreamWriter(new DocumentStream(documents[0]));
Console.SetOut(logger);
Console.SetError(logger);
}
int nThreads = GetMaxThreads(param);
IList<MsRunImpl> runs = new List<MsRunImpl>();
SingleChoiceWithSubParams singleSub =
param.GetParam(MetadataElement.MS_RUN.Name) as SingleChoiceWithSubParams;
if (singleSub != null){
MsRunParam sub =
singleSub.SubParams[singleSub.Value].GetAllParameters().FirstOrDefault() as MsRunParam;
if (sub != null){
if (sub.Value != null){
foreach (MsRunImpl run in sub.Value){
runs.Add(run);
}
}
}
}
IList<StudyVariable> studyVariables = new List<StudyVariable>();
singleSub = param.GetParam(MetadataElement.STUDY_VARIABLE.Name) as SingleChoiceWithSubParams;
if (singleSub != null){
StudyVariableParam sub =
singleSub.SubParams[singleSub.Value].GetAllParameters().FirstOrDefault() as StudyVariableParam;
if (sub != null){
if (sub.Value != null){
foreach (StudyVariable variable in sub.Value){
studyVariables.Add(variable);
}
}
}
}
IList<Sample> samples = new List<Sample>();
singleSub = param.GetParam(MetadataElement.SAMPLE.Name) as SingleChoiceWithSubParams;
if (singleSub != null){
SampleParam sub =
singleSub.SubParams[singleSub.Value].GetAllParameters().FirstOrDefault() as SampleParam;
if (sub != null){
if (sub.Value != null){
foreach (Sample sample in sub.Value){
samples.Add(sample);
}
}
}
}
IList<Assay> assays = new List<Assay>();
singleSub = param.GetParam(MetadataElement.ASSAY.Name) as SingleChoiceWithSubParams;
if (singleSub != null){
AssayParam sub =
singleSub.SubParams[singleSub.Value].GetAllParameters().FirstOrDefault() as AssayParam;
if (sub != null){
if (sub.Value != null){
foreach (Assay assay in sub.Value){
assays.Add(assay);
}
}
}
}
IList<Database> databases = new List<Database>();
singleSub = param.GetParam("database") as SingleChoiceWithSubParams;
if (singleSub != null){
DatabaseParam sub =
singleSub.SubParams[singleSub.Value].GetAllParameters().FirstOrDefault() as DatabaseParam;
if (sub != null && sub.Value != null){
foreach (Database db in sub.Value){
databases.Add(db);
}
}
}
IMatrixData output = (IMatrixData) inputData[0].CreateNewInstance(DataType.Matrix);
List<string> columnnames = new List<string>{
MetadataElement.STUDY_VARIABLE.Name,
MetadataElement.ASSAY.Name,
MetadataElement.MS_RUN.Name,
MetadataElement.SAMPLE.Name,
MetadataElement.INSTRUMENT.Name
};
List<string[]> matrix = new List<string[]>();
for (int i = 0; i < columnnames.Count; i++){
matrix.Add(new string[assays.Count]);
}
for (int i = 0; i < assays.Count; i++){
Assay assay = assays[i];
MsRunImpl runImpl = runs.FirstOrDefault(x => x.Id.Equals(assay.MsRun.Id));
Instrument instrument = instruments.FirstOrDefault(x => x.Id.Equals(assay.MsRun.Id));
if (runImpl == null){
continue;
}
var studyVariable = i < studyVariables.Count ? studyVariables[i] : null;
var sample = i < samples.Count ? samples[i] : null;
foreach (var s in studyVariables){
if (s.AssayMap.ContainsKey(assay.Id)){
studyVariable = s;
try{
int sampleId = studyVariable.SampleMap.FirstOrDefault().Key;
sample = samples.FirstOrDefault(x => x.Id.Equals(sampleId));
}
catch (Exception){
Console.Error.WriteLine("Can not find sample");
}
break;
}
}
AddRow(matrix, columnnames, i, runImpl, assay, sample, studyVariable, instrument);
}
output.SetData(Matrix.Experiment, new List<string>(), new float[assays.Count,columnnames.Count],
columnnames,
matrix,
new List<string>(), new List<string[][]>(), new List<string>(), new List<double[]>(),
new List<string>(), new List<double[][]>(), new List<string>(), new List<string[][]>(),
new List<string>(), new List<double[]>());
IList<IMatrixData> supplement = new List<IMatrixData>();
try{
IList<MsRunImpl> aplfiles =
runs.Where(x => x.Location != null && x.Location.Value.EndsWith(".apl")).ToList();
IMatrixData temp = ProcessAplFiles(processInfo, nThreads, aplfiles);
if (temp != null){
supplement.Add(temp);
}
}
catch (Exception e){
throw new Exception("Could not parse spectra file(s)! " + e.Message + "\n" + e.StackTrace);
}
try{
IMatrixData temp = ProcessDbFiles(processInfo, databases.Count < nThreads ? 1 : nThreads, databases);
if (temp != null){
supplement.Add(temp);
}
}
catch (Exception e){
throw new Exception("Could not parse database file(s)! " + e.Message + "\n" + e.StackTrace);
}
if (logger != null){
logger.Dispose();
}
supplTables = supplement.ToArray();
processInfo.Status("Define Experiment: DONE!");
processInfo.Progress(100);
return output;
}
catch (Exception e){
string msg = "Process aborted! " + e.Message;
MessageBox.Show(msg);
Logger.Error(Name, msg);
processInfo.Status(msg);
}
finally{
Console.SetOut(defaultOut);
Console.SetError(defaultErr);
}
return null;
}
/// <summary>
/// Copy the newly opened Document into the current document.
/// </summary>
/// <param name="doc">The newly opened document</param>
private void Open_(Document doc)
{
// Copy data from newly loaded doc into this doc
m_data = doc.m_data;
// Update the document references to point to this document
m_data.Palettes.UpdateDocument(this);
m_data.Spritesets.UpdateDocument(this);
m_data.BackgroundPalettes.UpdateDocument(this);
m_data.BackgroundSpritesets.UpdateDocument(this);
m_data.BackgroundMaps.UpdateDocument(this);
m_data.Filer.UpdateDocument(this);
Spriteset ss = m_data.Spritesets.Current;
if (ss != null)
ss.SelectFirstSprite();
Spriteset bss = m_data.BackgroundSpritesets.Current;
if (bss != null)
bss.SelectFirstSprite();
BgImages bgis = m_data.BackgroundImages;
if (bgis != null)
bgis.SelectFirstImage();
Owner.ClearUndo();
}
