private double CountJiShiStock(string productId, string depotpositionId) { double value = 0; IList <Model.StockSeach> stockList = new List <Model.StockSeach>(); DateTime date = this.dateEditDate.DateTime.AddDays(1); value = stockManager.SelectStockQuantity1(productId, depotpositionId); stockList = this.stockManager.SelectJiShi(productId, date, DateTime.Now); if (stockList != null && stockList.Count > 0) { var list = from s in stockList where s.PositionName == depotpositionId //调拨单为进 orderby s.InvoiceDate.Value.Date descending select s; if (list.Where(l => l.InvoiceTypeIndex == 3).Count() > 0) { Model.StockSeach seach = list.Where(l => l.InvoiceTypeIndex == 3).OrderBy(o => o.InvoiceDate.Value.Date).ThenBy(d => d.InsertTime.Value).FirstOrDefault(); list = list.Where(l => l.InvoiceDate.Value.Date <= seach.InvoiceDate.Value.Date && l.InsertTime.Value < seach.InsertTime.Value) .OrderByDescending(o => o.InvoiceDate.Value.Date); value = (double)seach.StockCheckBookQuantity; } if (list != null && list.Count() > 0) { foreach (Model.StockSeach stock in list.ToList <Model.StockSeach>()) { if (stock.InvoiceTypeIndex == 0) { value = value + stock.InvoiceQuantity.Value; } if (stock.InvoiceTypeIndex == 1) { value = value - stock.InvoiceQuantity.Value; } if (stock.InvoiceTypeIndex == 2) { value = value - stock.InvoiceQuantity.Value; } } var list1 = from s in stockList where s.OutPositionName == depotpositionId //挑拨单为出 orderby s.InvoiceDate.Value.Date descending select s; foreach (Model.StockSeach stock in list1.ToList <Model.StockSeach>()) { if (stock.InvoiceTypeIndex == 2) { value = value + stock.InvoiceQuantity.Value; } } } } return(value); }
private void CalcHistoryStock2(IList <Model.Product> listPro, DateTime date) { List <ManualResetEvent> listMre = new List <ManualResetEvent>(); foreach (var item in listPro) { var stockList = this.stockManager.SelectJiShi(item.ProductId, date, DateTime.Now).OrderByDescending(o => o.InvoiceDate.Value.Date); ManualResetEvent mre = new ManualResetEvent(false); Thread t = new Thread((obj) => { ManualResetEvent manualResetEvent = (obj as object[])[0] as ManualResetEvent; var list = (obj as object[])[1] as IList <Model.StockSeach>; //因為調撥後總庫存不變,暫不處理 if (list != null && list.Count() > 0) { //若有盘点,以盘点后库存为准 Model.StockSeach seach = list.Where(s => s.InvoiceTypeIndex == 3).OrderByDescending(o => o.InvoiceDate).ThenByDescending(d => d.InsertTime).FirstOrDefault(); if (seach != null) { list = list.Where(l => l.InvoiceDate.Value.Date <= seach.InvoiceDate.Value.Date && l.InsertTime.Value < seach.InsertTime.Value) .OrderByDescending(o => o.InvoiceDate.Value.Date).ToList(); item.StocksQuantity = seach.StockCheckBookQuantity; } if (list != null && list.Count() > 0) { foreach (Model.StockSeach stock in list) { if (stock.InvoiceTypeIndex == 0) { item.StocksQuantity = Convert.ToDouble(item.StocksQuantity) + Convert.ToDouble(stock.InvoiceQuantity); } if (stock.InvoiceTypeIndex == 1) { item.StocksQuantity = Convert.ToDouble(item.StocksQuantity) - Convert.ToDouble(stock.InvoiceQuantity); } } } } manualResetEvent.Set(); }); t.SetApartmentState(ApartmentState.MTA); t.IsBackground = true; listMre.Add(mre); t.Start(new object[2] { mre, stockList }); } WaitHandle.WaitAll(listMre.ToArray()); }
public IList <Model.StockSeach> SelectJiShi(string productId, DateTime startDate, DateTime endDate) { //0出 1 入 2调拨 3盘点 IList <Model.StockSeach> list = new List <Model.StockSeach>(); string[] sqls = { "SELECT 0 as InvoiceTypeIndex, '出倉單' as InvoiceType,DepotOutId as InvoiceNO,(select inserttime FROM DepotOut where DepotOut.DepotOutId = DepotOutDetail.DepotOutId) as InsertTime,(select DepotOutDate FROM DepotOut where DepotOut.DepotOutId = DepotOutDetail.DepotOutId) as InvoiceDate ,DepotOutDetail.DepotOutDetailQuantity AS InvoiceQuantity, DepotOutDetail.DepotPositionId AS PositionName FROM DepotOutDetail WHERE ProductId='" + productId + "' and DepotOutId in (select DepotOutId from DepotOut where (DepotOutDate between '" + startDate.ToString("yyyy-MM-dd") + "' and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "') or ('" + startDate.ToString("yyyy-MM-dd") + "' is null and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "' is null))", "SELECT 1 as InvoiceTypeIndex,'入倉單' as InvoiceType,DepotInId as InvoiceNO,(select InsertTime FROM depotIn where depotIn.DepotInId = DepotInDetail.DepotInId) as InsertTime ,(select DepotInDate FROM depotIn where depotIn.DepotInId = DepotInDetail.DepotInId) as InvoiceDate ,DepotInDetail.DepotInQuantity AS InvoiceQuantity,DepotPositionId PositionName FROM DepotInDetail WHERE ProductId='" + productId + "' and DepotInId in (select DepotInId from DepotIn where (DepotInDate between '" + startDate.ToString("yyyy-MM-dd") + "' and '" + endDate.ToString("yyyy-MM-dd HH:mm:dd") + "') or ('" + startDate.ToString("yyyy-MM-dd") + "' is null and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "' is null )) ", "SELECT 3 as InvoiceTypeIndex,'盤點核准單' as InvoiceType,StockCheckId as InvoiceNO,(select InsertTime FROM StockCheck where StockCheck.StockCheckId = StockCheckDetail.StockCheckId) as InsertTime ,(select StockCheckDate FROM StockCheck where StockCheck.StockCheckId = StockCheckDetail.StockCheckId) as InvoiceDate ,StockCheckDetail.StockCheckQuantity AS InvoiceQuantity,DepotPositionId AS PositionName,(StockCheckBookQuantity-StockCheckQuantity) as StockCheckBookQuantity FROM StockCheckDetail WHERE ProductId='" + productId + "' and StockCheckId in (select StockCheckId from StockCheck where (StockCheckDate between '" + startDate.ToString("yyyy-MM-dd") + "' and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "') or ('" + startDate.ToString("yyyy-MM-dd") + "' is null and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "' is null ))", //"SELECT 2 as InvoiceTypeIndex,'庫存調撥單' as InvoiceType,InvoiceId as InvoiceNO,(select InsertTime FROM InvoicePT where InvoicePT.InvoiceId = InvoicePTdetail.InvoiceId) as InsertTime ,(select InvoiceDate FROM InvoicePT where InvoicePT.InvoiceId = InvoicePTdetail.InvoiceId) as InvoiceDate ,InvoicePTdetail.InvoicePTDetailQuantity AS InvoiceQuantity,DepotPositionInId AS PositionName,DepotPositionId AS OutPositionName FROM InvoicePTdetail WHERE ProductId='"+productId+"' and InvoiceId in (select InvoiceId from InvoicePT where (InvoiceDate between '"+startDate.ToString("yyyy-MM-dd")+"' and '"+endDate.ToString("yyyy-MM-dd HH:mm:ss")+"') or ('"+startDate.ToString("yyyy-MM-dd")+"' is null and '"+endDate.ToString("yyyy-MM-dd HH:mm:ss")+"' is null ))", "SELECT 1 as InvoiceTypeIndex,'生產入庫單' as InvoiceType,ProduceInDepotId as InvoiceNO,(select InsertTime FROM ProduceInDepot where ProduceInDepot.ProduceInDepotId = ProduceInDepotDetail.ProduceInDepotId) as InsertTime ,(select ProduceInDepotDate FROM ProduceInDepot where ProduceInDepot.ProduceInDepotId = ProduceInDepotDetail.ProduceInDepotId) as InvoiceDate ,ProduceInDepotDetail.ProduceQuantity AS InvoiceQuantity,DepotPositionId AS PositionName FROM ProduceInDepotDetail WHERE ProduceQuantity <> 0 and ProductId='" + productId + "' and ProduceInDepotId in (select ProduceInDepotId from ProduceInDepot where (ProduceInDepotDate between '" + startDate.ToString("yyyy-MM-dd") + "' and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "') or ('" + startDate.ToString("yyyy-MM-dd") + "' is null and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "' is null )) ", "SELECT 1 as InvoiceTypeIndex,'委外入庫單' as InvoiceType,ProduceOtherInDepotDetail.ProduceOtherInDepotId as InvoiceNO,(SELECT ProduceOtherInDepot.InsertTime FROM ProduceOtherInDepot WHERE ProduceOtherInDepot.ProduceOtherInDepotId = ProduceOtherInDepotDetail.ProduceOtherInDepotId) AS InsertTime,(SELECT ProduceOtherInDepot.ProduceOtherInDepotDate FROM ProduceOtherInDepot WHERE ProduceOtherInDepot.ProduceOtherInDepotId = ProduceOtherInDepotDetail.ProduceOtherInDepotId) AS InvoiceDate,ProduceOtherInDepotDetail.ProduceInDepotQuantity AS InvoiceQuantity,DepotPositionId AS PositionName FROM ProduceOtherInDepotDetail WHERE ProductId='" + productId + "' AND ProduceOtherInDepotId IN (SELECT ProduceOtherInDepotId FROM ProduceOtherInDepot WHERE (ProduceOtherInDepotDate BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "') OR ('" + startDate.ToString("yyyy-MM-dd") + "' is null and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "' is null ))", "SELECT 0 as InvoiceTypeIndex,'銷售出貨單' as InvoiceType,InvoiceId as InvoiceNO,(SELECT InvoiceXS.InsertTime FROM InvoiceXS WHERE InvoiceXS.InvoiceId = InvoiceXSDetail.InvoiceId) AS InsertTime,(SELECT InvoiceXS.InvoiceDate FROM InvoiceXS WHERE InvoiceXS.InvoiceId = InvoiceXSDetail.InvoiceId) AS InvoiceDate,InvoiceXSDetailQuantity AS InvoiceQuantity,DepotPositionId AS PositionName FROM InvoiceXSDetail WHERE ProductId = '" + productId + "' AND InvoiceId IN (SELECT InvoiceXS.InvoiceId FROM InvoiceXS WHERE (InvoiceDate BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "') OR ('" + startDate.ToString("yyyy-MM-dd") + "' is null and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "' is null ))", "SELECT 1 as InvoiceTypeIndex,'採購入庫單' as InvoiceType,InvoiceId as InvoiceNO,(SELECT InvoiceCG.InsertTime FROM InvoiceCG WHERE invoicecg.InvoiceId = InvoiceCGDetail.InvoiceId) AS InsertTime,(SELECT InvoiceCG.InvoiceDate FROM InvoiceCG WHERE invoicecg.InvoiceId = InvoiceCGDetail.InvoiceId) AS InvoiceDate,InvoiceCGDetail.InvoiceCGDetaiInQuantity AS InvoiceQuantity,DepotPositionId AS PositionName FROM InvoiceCGDetail WHERE ProductId = '" + productId + "' AND InvoiceId IN (SELECT InvoiceCG.InvoiceId FROM InvoiceCG WHERE (InvoiceDate BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "') OR ('" + startDate.ToString("yyyy-MM-dd") + "' is null and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "' is null ))", "SELECT 1 as InvoiceTypeIndex,'銷售退貨' as InvoiceType,InvoiceId as InvoiceNO,(SELECT InvoiceXT.InsertTime FROM InvoiceXT WHERE InvoiceXT.InvoiceId = InvoiceXTDetail.InvoiceId) AS InsertTime,(SELECT InvoiceXT.InvoiceDate FROM InvoiceXT WHERE InvoiceXT.InvoiceId = InvoiceXTDetail.InvoiceId) AS InvoiceDate, InvoiceXTDetail.InvoiceXTDetailQuantity AS InvoiceQuantity, InvoiceXTDetail.DepotPositionId AS PositionName FROM InvoiceXTDetail WHERE ProductId = '" + productId + "' AND InvoiceId IN (SELECT InvoiceId FROM InvoiceXT WHERE (InvoiceDate BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "') OR ('" + startDate.ToString("yyyy-MM-dd") + "' is null and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "' is null ))", "SELECT 0 as InvoiceTypeIndex,'採購退貨' as InvoiceType,InvoiceId as InvoiceNO,(SELECT InvoiceCT.InsertTime FROM InvoiceCT WHERE InvoiceCT.InvoiceId = InvoiceCTDetail.InvoiceId) AS InsertTime, (SELECT InvoiceCT.InvoiceDate FROM InvoiceCT WHERE InvoiceCT.InvoiceId = InvoiceCTDetail.InvoiceId) AS InvoiceDate, InvoiceCTDetail.InvoiceCTDetailQuantity AS InvoiceQuantity, InvoiceCTDetail.DepotPositionId AS PositionName FROM InvoiceCTDetail WHERE ProductId = '" + productId + "' AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCT WHERE (InvoiceDate BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "') OR ('" + startDate.ToString("yyyy-MM-dd") + "' is null and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "' is null ))", "SELECT 1 as InvoiceTypeIndex,'生产退料' as InvoiceType,ProduceMaterialExitId as InvoiceNO,(SELECT ProduceMaterialExit.InsertTime FROM ProduceMaterialExit WHERE ProduceMaterialExit.ProduceMaterialExitId=ProduceMaterialExitDetail.ProduceMaterialExitId) AS InsertTime,(SELECT ProduceMaterialExit.ProduceExitMaterialDate FROM ProduceMaterialExit WHERE ProduceMaterialExit.ProduceMaterialExitId=ProduceMaterialExitDetail.ProduceMaterialExitId) AS InvoiceDate,ProduceMaterialExitDetail.ProduceQuantity AS InvoiceQuantity,ProduceMaterialExitDetail.DepotPositionId AS PositionName FROM ProduceMaterialExitDetail WHERE ProductId='"+ productId + "' AND ProduceMaterialExitId IN (SELECT ProduceMaterialExit.ProduceMaterialExitId FROM ProduceMaterialExit WHERE (ProduceExitMaterialDate BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "') OR('" + startDate.ToString("yyyy-MM-dd") + "' IS NULL AND '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "' IS NULL))" }; for (int m = 0; m < sqls.Length; m++) { #region Model.StockSeach stockSeach; using (SqlDataReader dataReader = SQLDB.SqlHelper.ExecuteReader(SQLDB.SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqls[m], null)) { while (dataReader.Read()) { stockSeach = new Model.StockSeach(); for (int i = 0; i < dataReader.FieldCount; i++) { foreach (var item in stockSeach.GetType().GetProperties()) { string fieldName = item.Name;//属性名 //判断当前迭代出的属性名称是否和迭代出的dataReader的列名称一致 if (item.Name.ToLower().Equals(dataReader.GetName(i).ToLower())) { //从DataReader中读取值 object _value = dataReader[fieldName]; //将当前dataReader的单列值赋予相匹配的属性,否则赋予一个null值. if (_value != null && _value != DBNull.Value) { item.SetValue(stockSeach, _value, null); } else { item.SetValue(stockSeach, null, null); } } } } list.Add(stockSeach); } } #endregion } return(list); }
//商品窗体库存历史记录 public IList <Model.StockSeach> SelectReaderByPro(string productId, DateTime startDate, DateTime endDate) { IList <Model.StockSeach> list = new List <Model.StockSeach>(); string[] sqls = { "SELECT '出倉單' as InvoiceType,DepotOutId as InvoiceNO,(select DepotOutDate FROM DepotOut where DepotOut.DepotOutId = DepotOutDetail.DepotOutId) as InvoiceDate ,DepotOutDetail.DepotOutDetailQuantity AS InvoiceQuantity,ProductUnit AS InvoiceUnit,(SELECT DepotName FROM Depot WHERE Depot.DepotId=(SELECT DepotId FROM DepotOut WHERE DepotOut.DepotOutId=DepotOutDetail.DepotOutId )) AS DepotName,(SELECT id FROM DepotPosition WHERE DepotPosition.DepotPositionId=DepotOutDetail.DepotPositionId) AS PositionName,(SELECT StocksQuantity FROM product WHERE product.ProductId=DepotOutDetail.ProductId) AS Stock1,'' AS WorkHouseName,(SELECT InvioiceId FROM DepotOut WHERE DepotOut.DepotOutId = DepotOutDetail.DepotOutId) AS InvioiceId, (SELECT Description FROM DepotOut WHERE DepotOut.DepotOutId=DepotOutDetail.DepotOutId) as Description,isnull((SELECT InvoiceCusId FROM ProduceOtherMaterial WHERE ProduceOtherMaterialId IN (SELECT InvioiceId FROM DepotOut WHERE DepotOut.DepotOutId=DepotOutDetail.DepotOutId)),'')+isnull((SELECT CustomerInvoiceXOId FROM InvoiceXO WHERE InvoiceId=(SELECT InvoiceXOId FROM ProduceMaterial WHERE ProduceMaterial.ProduceMaterialID IN (SELECT InvioiceId FROM DepotOut WHERE DepotOut.DepotOutId=DepotOutDetail.DepotOutId))),'') as CusXOId,(0-DepotOutDetail.DepotOutDetailQuantity) AS StockCheckBookQuantity,Pihao FROM DepotOutDetail WHERE ProductId='" + productId + "' and DepotOutId in (select DepotOutId from DepotOut where (DepotOutDate between '" + startDate.ToString("yyyy-MM-dd") + "' and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "') or ('" + startDate.ToString("yyyy-MM-dd") + "' is null and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "' is null))", "SELECT '入倉單' as InvoiceType,DepotInId as InvoiceNO,(select DepotInDate FROM depotIn where depotIn.DepotInId = DepotInDetail.DepotInId) as InvoiceDate ,DepotInDetail.DepotInQuantity AS InvoiceQuantity,ProductUnit AS InvoiceUnit,(SELECT DepotName FROM Depot WHERE Depot.DepotId=(SELECT DepotId FROM depotIn WHERE depotIn.DepotInId=DepotInDetail.DepotInId )) AS DepotName,(SELECT id FROM DepotPosition WHERE DepotPosition.DepotPositionId=DepotInDetail.DepotPositionId) AS PositionName,(SELECT StocksQuantity FROM product WHERE product.ProductId=DepotInDetail.ProductId) AS Stock1,'' AS WorkHouseName,'' AS InvioiceId, Descriptions as Description,'' as CusXOId,DepotInDetail.DepotInQuantity AS StockCheckBookQuantity,'' as Pihao FROM DepotInDetail WHERE ProductId='" + productId + "' and DepotInId in (select DepotInId from DepotIn where (DepotInDate between '" + startDate.ToString("yyyy-MM-dd") + "' and '" + endDate.ToString("yyyy-MM-dd HH:mm:dd") + "') or ('" + startDate.ToString("yyyy-MM-dd") + "' is null and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "' is null )) ", "SELECT '盤點核准單' as InvoiceType,StockCheckId as InvoiceNO,(select StockCheckDate FROM StockCheck where StockCheck.StockCheckId = StockCheckDetail.StockCheckId) as InvoiceDate ,StockCheckDetail.StockCheckQuantity AS InvoiceQuantity,ProductUnitName AS InvoiceUnit,(SELECT DepotName FROM Depot WHERE Depot.DepotId=(SELECT DepotId FROM StockCheck WHERE StockCheck.StockCheckId=StockCheckDetail.StockCheckId )) AS DepotName,(SELECT id FROM DepotPosition WHERE DepotPosition.DepotPositionId=StockCheckDetail.DepotPositionId) AS PositionName,(SELECT StocksQuantity FROM product WHERE product.ProductId=StockCheckDetail.ProductId) AS Stock1,'' AS WorkHouseName,'' AS InvioiceId,Directions as Description,'' as CusXOId,(StockCheckQuantity-StockCheckBookQuantity) as StockCheckBookQuantity ,'' as Pihao FROM StockCheckDetail WHERE ProductId='" + productId + "' and StockCheckId in (select StockCheckId from StockCheck where (StockCheckDate between '" + startDate.ToString("yyyy-MM-dd") + "' and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "') or ('" + startDate.ToString("yyyy-MM-dd") + "' is null and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "' is null ))", "SELECT '庫存調撥單' as InvoiceType,InvoiceId as InvoiceNO,(select InvoiceDate FROM InvoicePT where InvoicePT.InvoiceId = InvoicePTdetail.InvoiceId) as InvoiceDate ,InvoicePTdetail.InvoicePTDetailQuantity AS InvoiceQuantity,InvoiceProductUnit AS InvoiceUnit,(SELECT DepotName FROM Depot WHERE Depot.DepotId=(SELECT DepotInId FROM InvoicePT WHERE InvoicePT.InvoiceId=InvoicePTdetail.InvoiceId )) AS DepotName,(SELECT DepotName FROM Depot WHERE Depot.DepotId=(SELECT DepotId FROM InvoicePT WHERE InvoicePT.InvoiceId=InvoicePTdetail.InvoiceId )) AS OutDepotName,(SELECT id FROM DepotPosition WHERE DepotPosition.DepotPositionId=InvoicePTdetail.DepotPositionInId) AS PositionName,(SELECT id FROM DepotPosition WHERE DepotPosition.DepotPositionId=InvoicePTdetail.DepotPositionId) AS OutPositionName,(SELECT StocksQuantity FROM product WHERE product.ProductId=InvoicePTdetail.ProductId) AS Stock1,'' AS WorkHouseName,'' AS InvioiceId,InvoicePTDetailNote as Description,'' as CusXOId,'' as Pihao FROM InvoicePTdetail WHERE ProductId='" + productId + "' and InvoiceId in (select InvoiceId from InvoicePT where (InvoiceDate between '" + startDate.ToString("yyyy-MM-dd") + "' and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "') or ('" + startDate.ToString("yyyy-MM-dd") + "' is null and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "' is null ))", "SELECT '生產入庫單' as InvoiceType,ProduceInDepotId as InvoiceNO,(select ProduceInDepotDate FROM ProduceInDepot where ProduceInDepot.ProduceInDepotId = ProduceInDepotDetail.ProduceInDepotId) as InvoiceDate ,ProduceInDepotDetail.ProduceQuantity AS InvoiceQuantity,ProductUnit AS InvoiceUnit,(SELECT DepotName FROM Depot WHERE Depot.DepotId=(SELECT DepotId FROM ProduceInDepot WHERE ProduceInDepot.ProduceInDepotId=ProduceInDepotDetail.ProduceInDepotId )) AS DepotName,(SELECT id FROM DepotPosition WHERE DepotPosition.DepotPositionId=ProduceInDepotDetail.DepotPositionId) AS PositionName,(SELECT StocksQuantity FROM product WHERE product.ProductId=ProduceInDepotDetail.ProductId) AS Stock1,(SELECT Workhousename FROM WorkHouse WHERE WorkHouse.WorkHouseId = (SELECT ProduceInDepot.WorkHouseId FROM ProduceInDepot WHERE ProduceInDepot.ProduceInDepotId = ProduceInDepotDetail.ProduceInDepotId)) AS WorkHouseName,'' AS InvioiceId,DetailDesc as Description,(select CustomerInvoiceXOId from invoicexo where invoiceid = (SELECT InvoiceXOId FROM PronoteHeader p WHERE p.PronoteHeaderID = ProduceInDepotDetail.PronoteHeaderId)) as CusXOId,ProduceInDepotDetail.ProduceQuantity AS StockCheckBookQuantity,ProduceTransferQuantity,'' as Pihao FROM ProduceInDepotDetail WHERE ProductId='" + productId + "' and ProduceInDepotId in (select ProduceInDepotId from ProduceInDepot where (ProduceInDepotDate between '" + startDate.ToString("yyyy-MM-dd") + "' and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "') or ('" + startDate.ToString("yyyy-MM-dd") + "' is null and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "' is null )) ", "SELECT '委外入庫單' as InvoiceType,ProduceOtherInDepotDetail.ProduceOtherInDepotId as InvoiceNO,(SELECT ProduceOtherInDepot.ProduceOtherInDepotDate FROM ProduceOtherInDepot WHERE ProduceOtherInDepot.ProduceOtherInDepotId = ProduceOtherInDepotDetail.ProduceOtherInDepotId) AS InvoiceDate,ProduceOtherInDepotDetail.ProduceInDepotQuantity AS InvoiceQuantity,ProduceOtherInDepotDetail.ProductUnit AS InvoiceUnit,(SELECT DepotName FROM Depot WHERE Depot.DepotId = (SELECT produceotherindepot.DepotId FROM ProduceOtherInDepot WHERE ProduceOtherInDepot.ProduceOtherInDepotId = ProduceOtherInDepotDetail.ProduceOtherInDepotId)) AS DepotName,(SELECT Id FROM DepotPosition WHERE DepotPosition.DepotPositionId = ProduceOtherInDepotDetail.DepotPositionId) AS PositionName,(SELECT product.StocksQuantity FROM Product WHERE product.ProductId = ProduceOtherInDepotDetail.ProductId) AS Stock1,'' AS WorkHouseName,'' AS InvioiceId,Description AS Description,InvoiceCusId as CusXOId ,ProduceOtherInDepotDetail.ProduceInDepotQuantity AS StockCheckBookQuantity,ProduceTransferQuantity,'' as Pihao FROM ProduceOtherInDepotDetail WHERE ProductId='" + productId + "' AND ProduceOtherInDepotId IN (SELECT ProduceOtherInDepotId FROM ProduceOtherInDepot WHERE (ProduceOtherInDepotDate BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "') OR ('" + startDate.ToString("yyyy-MM-dd") + "' is null and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "' is null ))", "SELECT '銷售出貨單' as InvoiceType,InvoiceId as InvoiceNO,(SELECT InvoiceXS.InvoiceDate FROM InvoiceXS WHERE InvoiceXS.InvoiceId = InvoiceXSDetail.InvoiceId) AS InvoiceDate,InvoiceXSDetailQuantity AS InvoiceQuantity,InvoiceProductUnit AS InvoiceUnit,(SELECT DepotName FROM Depot WHERE depot.DepotId = (SELECT invoicexs.DepotId FROM InvoiceXS WHERE invoicexs.InvoiceId = InvoiceXSDetail.InvoiceId)) AS DepotName,(SELECT Id FROM DepotPosition WHERE DepotPosition.DepotPositionId = invoicexsdetail.DepotPositionId) AS PositionName,(SELECT product.StocksQuantity FROM Product WHERE Product.ProductId = invoicexsdetail.ProductId) AS Stock1,'' AS WorkHouseName,'' AS InvioiceId,InvoiceXSDetailNote AS Description,(SELECT CustomerInvoiceXOId FROM InvoiceXO WHERE InvoiceId = InvoiceXOId) as CusXOId ,(0-InvoiceXSDetailQuantity) as StockCheckBookQuantity,'' as Pihao FROM InvoiceXSDetail WHERE ProductId = '" + productId + "' AND InvoiceId IN (SELECT InvoiceXS.InvoiceId FROM InvoiceXS WHERE (InvoiceDate BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "') OR ('" + startDate.ToString("yyyy-MM-dd") + "' is null and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "' is null ))", "SELECT '採購入庫單' as InvoiceType,InvoiceId as InvoiceNO,(SELECT InvoiceCG.InvoiceDate FROM InvoiceCG WHERE invoicecg.InvoiceId = InvoiceCGDetail.InvoiceId) AS InvoiceDate,InvoiceCGDetail.InvoiceCGDetaiInQuantity AS InvoiceQuantity,InvoiceCGUnit AS InvoiceUnit,(SELECT DepotName FROM Depot WHERE depot.DepotId = (SELECT InvoiceCG.DepotId FROM InvoiceCG WHERE InvoiceCG.InvoiceId = InvoiceCGDetail.InvoiceId)) AS DepotName,(SELECT Id FROM DepotPosition WHERE DepotPosition.DepotPositionId = InvoiceCGDetail.DepotPositionId) AS PositionName,(SELECT Product.StocksQuantity FROM Product WHERE Product.ProductId = InvoiceCGDetail.ProductId) AS Stock1,'' AS WorkHouseName,InvoiceCOId AS InvioiceId,InvoiceCGDetail.InvoiceCGDetailNote AS Description,(select CustomerInvoiceXOId from invoicexo where InvoiceId=(select InvoiceXOId from InvoiceCO where InvoiceId=InvoiceCGDetail.InvoiceCOId)) as CusXOId,InvoiceCGDetaiInQuantity as StockCheckBookQuantity,ProduceTransferQuantity,SupplierLotNumber as Pihao FROM InvoiceCGDetail WHERE ProductId = '" + productId + "' AND InvoiceId IN (SELECT InvoiceCG.InvoiceId FROM InvoiceCG WHERE (InvoiceDate BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "') OR ('" + startDate.ToString("yyyy-MM-dd") + "' is null and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "' is null ))", "SELECT '銷售退貨' as InvoiceType,InvoiceId as InvoiceNO,(SELECT InvoiceXT.InvoiceDate FROM InvoiceXT WHERE InvoiceXT.InvoiceId = InvoiceXTDetail.InvoiceId) AS InvoiceDate, InvoiceXTDetail.InvoiceXTDetailQuantity AS InvoiceQuantity, InvoiceXTDetail.InvoiceProductUnit AS InvoiceUnit, (SELECT DepotName FROM Depot WHERE depot.DepotId = (SELECT InvoiceXT.DepotId FROM InvoiceXT WHERE InvoiceXT.InvoiceId=InvoiceXTDetail.InvoiceId)) AS DepotName,(SELECT Id FROM DepotPosition WHERE DepotPosition.DepotPositionId = InvoiceXTDetail.DepotPositionId) AS PositionName, (SELECT Product.StocksQuantity FROM Product WHERE Product.ProductId = InvoiceXTDetail.ProductId) AS Stock1, '' AS WorkHouseName,'' AS InvioiceId,(SELECT InvoiceXT.InvoiceNote FROM InvoiceXT WHERE InvoiceXT.InvoiceId = InvoiceXTDetail.InvoiceId) AS Description,(SELECT CustomerInvoiceXOId FROM InvoiceXO WHERE InvoiceXO.InvoiceId = InvoiceXTDetail.InvoiceXOId ) as CusXOId,InvoiceXTDetail.InvoiceXTDetailQuantity AS StockCheckBookQuantity,'' as Pihao FROM InvoiceXTDetail WHERE ProductId = '" + productId + "' AND InvoiceId IN (SELECT InvoiceId FROM InvoiceXT WHERE (InvoiceDate BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "') OR ('" + startDate.ToString("yyyy-MM-dd") + "' is null and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "' is null ))", "SELECT '採購退貨' as InvoiceType,InvoiceId as InvoiceNO, (SELECT InvoiceCT.InvoiceDate FROM InvoiceCT WHERE InvoiceCT.InvoiceId = InvoiceCTDetail.InvoiceId) AS InvoiceDate, InvoiceCTDetail.InvoiceCTDetailQuantity AS InvoiceQuantity, InvoiceCTDetail.InvoiceProductUnit AS InvoiceUnit,(SELECT DepotName FROM Depot WHERE depot.DepotId = (SELECT InvoiceCT.DepotId FROM InvoiceCT WHERE InvoiceCT.InvoiceId=InvoiceCTDetail.InvoiceId)) AS DepotName, (SELECT Id FROM DepotPosition WHERE DepotPosition.DepotPositionId = InvoiceCTDetail.DepotPositionId) AS PositionName, (SELECT Product.StocksQuantity FROM Product WHERE Product.ProductId = InvoiceCTDetail.ProductId) AS Stock1,'' AS WorkHouseName,(SELECT InvoiceCustomXOId FROM InvoiceCO WHERE InvoiceCO.InvoiceId = InvoiceCTDetail.InvoiceCOId) as CusXOId,(SELECT InvoiceCT.InvoiceNote FROM InvoiceCT WHERE InvoiceCT.InvoiceId = InvoiceCTDetail.InvoiceId) AS Description,'' as CusXOId ,(0-InvoiceCTDetail.InvoiceCTDetailQuantity) AS StockCheckBookQuantity,'' as Pihao FROM InvoiceCTDetail WHERE ProductId = '" + productId + "' AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCT WHERE (InvoiceDate BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "') OR ('" + startDate.ToString("yyyy-MM-dd") + "' is null and '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "' is null ))", "SELECT '生产退料' as InvoiceType,ProduceMaterialExitId as InvoiceNO,(SELECT ProduceMaterialExit.ProduceExitMaterialDate FROM ProduceMaterialExit WHERE ProduceMaterialExit.ProduceMaterialExitId=ProduceMaterialExitDetail.ProduceMaterialExitId) AS InvoiceDate,ProduceMaterialExitDetail.ProduceQuantity AS InvoiceQuantity,ProduceMaterialExitDetail.ProductUnit AS InvoiceUnit,(SELECT DepotName FROM Depot WHERE Depot.DepotId=(SELECT ProduceMaterialExit.DepotId FROM ProduceMaterialExit WHERE ProduceMaterialExit.ProduceMaterialExitId=ProduceMaterialExitDetail.ProduceMaterialExitId)) AS DepotName,(SELECT Id FROM DepotPosition WHERE DepotPosition.DepotPositionId=ProduceMaterialExitDetail.DepotPositionId ) AS PositionName,(SELECT Product.StocksQuantity FROM Product WHERE Product.ProductId=ProduceMaterialExitDetail.ProductId) AS Stock1,(SELECT Workhousename FROM WorkHouse WHERE WorkHouse.WorkHouseId = (SELECT ProduceMaterialExit.WorkHouseId FROM ProduceMaterialExit WHERE ProduceMaterialExit.ProduceMaterialExitId=ProduceMaterialExitDetail.ProduceMaterialExitId)) AS WorkHouseName,'' AS InvioiceId,(SELECT ProduceMaterialExit.ProduceExitMaterialDesc FROM ProduceMaterialExit WHERE ProduceMaterialExit.ProduceMaterialExitId=ProduceMaterialExitDetail.ProduceMaterialExitId) AS Description,(SELECT InvoiceCusId FROM PronoteHeader WHERE PronoteHeader.PronoteHeaderID = (SELECT TOP 1 ProduceMaterialExit.PronoteHeaderID FROM ProduceMaterialExit WHERE ProduceMaterialExit.ProduceMaterialExitId = ProduceMaterialExitDetail.ProduceMaterialExitId)) as CusXOId,ProduceMaterialExitDetail.ProduceQuantity as StockCheckBookQuantity,'' as Pihao FROM ProduceMaterialExitDetail WHERE ProductId='"+ productId + "' AND ProduceMaterialExitId IN (SELECT ProduceMaterialExit.ProduceMaterialExitId FROM ProduceMaterialExit WHERE (ProduceExitMaterialDate BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "') OR('" + startDate.ToString("yyyy-MM-dd") + "' IS NULL AND '" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "' IS NULL))" }; for (int m = 0; m < sqls.Length; m++) { #region Model.StockSeach stockSeach; using (SqlDataReader dataReader = SQLDB.SqlHelper.ExecuteReader(SQLDB.SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqls[m], null)) { while (dataReader.Read()) { stockSeach = new Model.StockSeach(); for (int i = 0; i < dataReader.FieldCount; i++) { foreach (var item in stockSeach.GetType().GetProperties()) { string fieldName = item.Name;//属性名 //判断当前迭代出的属性名称是否和迭代出的dataReader的列名称一致 if (item.Name.ToLower().Equals(dataReader.GetName(i).ToLower())) { //从DataReader中读取值 object _value = dataReader[fieldName]; //将当前dataReader的单列值赋予相匹配的属性,否则赋予一个null值. if (_value != null && _value != DBNull.Value) { item.SetValue(stockSeach, _value, null); } else { item.SetValue(stockSeach, null, null); } } } } list.Add(stockSeach); } } #endregion } return(list); }
private void CalcHistoryStock(IEnumerable <Model.Product> listPro, DateTime date) { foreach (var item in listPro) { var stockList = this.stockManager.SelectJiShi(item.ProductId, date, DateTime.Now).OrderByDescending(o => o.InvoiceDate.Value.Date); //因為調撥後總庫存不變,暫不處理 if (stockList != null && stockList.Count() > 0) { //若有盘点,以盘点后库存为准 Model.StockSeach seach = stockList.Where(s => s.InvoiceTypeIndex == 3).OrderByDescending(o => o.InvoiceDate).ThenByDescending(d => d.InsertTime).FirstOrDefault(); if (seach != null) { stockList = stockList.Where(l => l.InvoiceDate.Value.Date <= seach.InvoiceDate.Value.Date && l.InsertTime.Value < seach.InsertTime.Value) .OrderByDescending(o => o.InvoiceDate.Value.Date); item.StocksQuantity = seach.StockCheckBookQuantity; } if (stockList != null && stockList.Count() > 0) { foreach (Model.StockSeach stock in stockList) { if (stock.InvoiceTypeIndex == 0) { item.StocksQuantity = Convert.ToDouble(item.StocksQuantity) + Convert.ToDouble(stock.InvoiceQuantity); } if (stock.InvoiceTypeIndex == 1) { item.StocksQuantity = Convert.ToDouble(item.StocksQuantity) - Convert.ToDouble(stock.InvoiceQuantity); } } } } } }
private void simpleButton1_Click(object sender, EventArgs e) { if (this.dateEdit1.EditValue == null) { MessageBox.Show(Properties.Resources.DateNotNull, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information); return; } DateTime date = this.dateEdit1.DateTime.Date.AddDays(1); IList <Model.StockSeach> stockList = new List <Model.StockSeach>(); dt = this.miscDataManager.SelectByCondition("Q15", this.lookUpEditDepotStar.EditValue == null ? null : this.lookUpEditDepotStar.EditValue.ToString(), lookUpEditDepotPosition.EditValue == null ? null : lookUpEditDepotPosition.EditValue.ToString(), null, this.textProductNameOrId.Text, this.btn_ProductNameStart.Text, this.btn_ProductNameEnd.Text, this.LookUpProductCategoryStart.EditValue == null ? null : this.LookUpProductCategoryStart.EditValue.ToString(), this.lookUpProductCategoryEnd.EditValue == null ? null : this.lookUpProductCategoryEnd.EditValue.ToString(), true, this.txt_Product_Id.Text);//this.checkEditShowZeroProduct.Checked 改为true,因为即时库存查的是当天库存,当天库存不为0,现在为0的情况就会查不出,故此,不显示为0库存要在查出以后做判断。 for (int i = 0; i < dt.Rows.Count; i++) { if (i == 0 || dt.Rows[i]["productid"].ToString() != dt.Rows[i - 1]["productid"].ToString()) { stockList = this.stockManager.SelectJiShi(dt.Rows[i]["productid"].ToString(), date, DateTime.Now); dt.Rows[i]["yifenpeiquantity"] = this.stockManager.SelectJiShidistributioned(dt.Rows[i]["productid"].ToString(), date, DateTime.Now).ToString("0.####"); } if (stockList != null && stockList.Count > 0) { var list = from s in stockList where s.PositionName == dt.Rows[i]["posoid"].ToString() //调拨单为进 orderby s.InvoiceDate.Value.Date descending select s; if (list.Where(l => l.InvoiceTypeIndex == 3).Count() > 0) { Model.StockSeach seach = list.Where(l => l.InvoiceTypeIndex == 3) .OrderBy(o => o.InvoiceDate.Value.Date).ThenBy(d => d.InsertTime.Value).FirstOrDefault(); list = list.Where(l => l.InvoiceDate.Value.Date <= seach.InvoiceDate.Value.Date && l.InsertTime.Value < seach.InsertTime.Value) .OrderByDescending(o => o.InvoiceDate.Value.Date); dt.Rows[i]["Quantity"] = seach.StockCheckBookQuantity; } if (list != null && list.Count() > 0) { foreach (Model.StockSeach stock in list.ToList <Model.StockSeach>()) { if (stock.InvoiceTypeIndex == 0) { dt.Rows[i]["Quantity"] = Convert.ToDouble(dt.Rows[i]["Quantity"].ToString()) + stock.InvoiceQuantity.Value; } if (stock.InvoiceTypeIndex == 1) { dt.Rows[i]["Quantity"] = Convert.ToDouble(dt.Rows[i]["Quantity"].ToString()) - stock.InvoiceQuantity.Value; } if (stock.InvoiceTypeIndex == 2) { dt.Rows[i]["Quantity"] = Convert.ToDouble(dt.Rows[i]["Quantity"].ToString()) - stock.InvoiceQuantity.Value; } //已定未入 if (stock.InvoiceType == "採購入庫單") { dt.Rows[i]["OrderOnWayQuantity"] = Convert.ToDouble(dt.Rows[i]["OrderOnWayQuantity"]) + stock.InvoiceQuantity.Value; } } var list1 = from s in stockList where s.OutPositionName == dt.Rows[i]["posoid"].ToString() //挑拨单为出 orderby s.InvoiceDate.Value.Date descending select s; foreach (Model.StockSeach stock in list1.ToList <Model.StockSeach>()) { if (stock.InvoiceTypeIndex == 2) { dt.Rows[i]["Quantity"] = Convert.ToDouble(dt.Rows[i]["Quantity"].ToString()) + stock.InvoiceQuantity.Value; } } } } } dt.AcceptChanges(); if (dt.Rows.Count < 1) { MessageBox.Show("无数据", this.Text); } dt.DefaultView.Sort = "spid,Quantity asc"; dt = dt.DefaultView.ToTable(); DataRow[] dr = dt.Select("Quantity<>'0'"); if (!this.checkEditShowZeroProduct.Checked && dr.Count() > 0) { dt = dr.CopyToDataTable(); } this.gridControl1.DataSource = dt; this.labelControl1.Text = dt.Rows.Count.ToString() + " 项"; //this.condition.DepotEnd = this.lookUpEditDepotEnd.EditValue == null ? null : this.lookUpEditDepotEnd.EditValue.ToString(); //this.condition.DepotStart = this.lookUpEditDepotStar.EditValue == null ? null : this.lookUpEditDepotStar.EditValue.ToString(); //this.condition.ProductNameStart = this.btn_ProductNameStart.Text; //this.condition.ProductNameEnd = this.btn_ProductNameEnd.Text; //this.condition.ProduceCategoryStart = this.LookUpProductCategoryStart.EditValue == null ? null : this.LookUpProductCategoryStart.EditValue.ToString(); //this.condition.ProductCategoryEnd = this.lookUpProductCategoryEnd.EditValue == null ? null : this.lookUpProductCategoryEnd.EditValue.ToString(); }
private void repositoryItemHyperLinkEdit1_Click(object sender, EventArgs e) { Model.StockSeach m = this.bindingSource1.Current as Model.StockSeach; if (m != null) { if (m.InvoiceType == "出倉單") { Settings.StockLimitations.OutStockEditForm f = new Book.UI.Settings.StockLimitations.OutStockEditForm(m.InvoiceNO); f.ShowDialog(this); } else if (m.InvoiceType == "入倉單") { Settings.StockLimitations.DepotInForm f = new Book.UI.Settings.StockLimitations.DepotInForm(m.InvoiceNO); f.ShowDialog(this); } else if (m.InvoiceType == "盤點核准單") { Settings.StockLimitations.StockCheckForm f = new Book.UI.Settings.StockLimitations.StockCheckForm(m.InvoiceNO); f.ShowDialog(this); } else if (m.InvoiceType == "庫存調撥單") { Invoices.PT.EditForm f = new Book.UI.Invoices.PT.EditForm(m.InvoiceNO); f.ShowDialog(this); } else if (m.InvoiceType == "生產入庫單") { produceManager.ProduceInDepot.EditForm f = new Book.UI.produceManager.ProduceInDepot.EditForm(m.InvoiceNO); f.ShowDialog(this); } else if (m.InvoiceType == "委外入庫單") { produceManager.ProduceOtherInDepot.Editform f = new Book.UI.produceManager.ProduceOtherInDepot.Editform(m.InvoiceNO, "view"); f.ShowDialog(this); } else if (m.InvoiceType == "銷售出貨單") { Invoices.XS.EditForm f = new Book.UI.Invoices.XS.EditForm(m.InvoiceNO); f.ShowDialog(this); } else if (m.InvoiceType == "採購入庫單") { Invoices.CG.EditForm f = new Book.UI.Invoices.CG.EditForm(m.InvoiceNO); f.ShowDialog(this); } else if (m.InvoiceType == "銷售退貨") { Invoices.XT.EditForm f = new Book.UI.Invoices.XT.EditForm(m.InvoiceNO); f.ShowDialog(this); } else if (m.InvoiceType == "採購退貨") { Invoices.CT.EditForm f = new Book.UI.Invoices.CT.EditForm(m.InvoiceNO, "view"); f.ShowDialog(this); } else if (m.InvoiceType == "生产退料") { produceManager.ProduceMaterialExit.EditForm f = new Book.UI.produceManager.ProduceMaterialExit.EditForm(m.InvoiceNO); f.ShowDialog(this); } } }