public static int GetChangeAfterDate(int itemID, int storeID, DateTime date) { BLL.Balance bal = new Balance(); string query = String.Format(@"select ISNULL((select Quantity from receivedoc where ItemID={0} and StoreID={1} and Date>{2}),0) - ISNULL((select Quantity from IssueDoc where ItemID={0} and StoreId={1} and Date>{2}),0) + ISNULL((select Quantity from Disposal where ItemID={0} and StoreId={1} and Losses<>1 and Date>{2}),0) - ISNULL((select Quantity from Disposal where ItemID={0} and StoreId={1} and Losses=1 and Date>{2}),0) as Qty", itemID, storeID, date); bal.FlushData(); bal.LoadFromRawSql(query); return int.Parse(bal.GetColumn("Qty").ToString()); }
public static DataView GetStockCardByWarehouse(int storeId, int itemId, int? unitID, int fiscalYear, int physicalStoreTypeID,int manufactuerID) { var ld = new ListDictionary(); ld.Add("@ItemID", itemId); ld.Add("@UnitID", unitID); ld.Add("@StoreID", storeId); ld.Add("@FiscalYear", fiscalYear); ld.Add("@Warehouse", physicalStoreTypeID); ld.Add("@Manufacturer",manufactuerID); Balance bal = new Balance(); bal.LoadFromSql("[Rpt_StockCard]", ld, CommandType.StoredProcedure); bal.Rewind(); decimal Balance = 0; while (!bal.EOF) { Balance += Convert.ToDecimal(bal.GetColumn("Balance")); bal.SetColumn("Balance", Balance); bal.MoveNext(); } return bal.DefaultView; }
public static DataView GetStockCardByDate(int storeId, int itemId, int? unitID, int physicalStoreTypeID, int manufactuerID, DateTime fromDate, DateTime toDate) { var ld = new ListDictionary(); ld.Add("@ItemID", itemId); ld.Add("@UnitID", unitID); ld.Add("@StoreID", storeId); ld.Add("@fromdate", fromDate); ld.Add("@toDate", toDate); ld.Add("@Warehouse", physicalStoreTypeID); ld.Add("@Manufacturer", manufactuerID); Balance bal = new Balance(); bal.LoadFromSql("[Rpt_StockCardByDate]", ld, CommandType.StoredProcedure); bal.Rewind(); int Balance = 0; while (!bal.EOF) { Balance += Convert.ToInt32(bal.GetColumn("Balance")); bal.SetColumn("Balance", Balance); bal.MoveNext(); } return bal.DefaultView; }
public static DataTable GetReservedItemsWithAmount(int modeID) { BLL.Balance bal = new Balance(); var query = HCMIS.Repository.Queries.Balance.SelectGetReservedItemsWithAmount(modeID, BLL.OrderStatus.Constant.PICK_LIST_GENERATED, BLL.OrderStatus.Constant.PICK_LIST_CONFIRMED); bal.LoadFromRawSql(query); while (!bal.EOF) { bal.SetColumn("Difference", DateTimeHelper.GetDateSpan(Convert.ToDateTime(bal.GetColumn("SavedDate")), DateTimeHelper.ServerDateTime)); bal.MoveNext(); } return bal.DataTable; }
/// <summary> /// Gets the quantity left. /// </summary> /// <param name="storeID">The store ID.</param> /// <param name="itemID">The item ID.</param> /// <param name="unitID">The unit ID.</param> /// <returns></returns> public static long GetQuantityLeft(int storeID, int itemID, int unitID) { var query = HCMIS.Repository.Queries.Balance.SelectGetQuantityLeft(storeID, itemID, unitID); BLL.Balance bal = new Balance(); bal.LoadFromRawSql(query); return Convert.ToInt64(bal.GetColumn("QL")); }
/// <summary> /// Gets bin card by supplier /// </summary> /// <param name="itemId"></param> /// <param name="supplierId"></param> /// <returns></returns> public static DataView GetBinCard(int storeId, int itemId, int? unitID, int fiscalYear, int supplierId) { var ld = new ListDictionary(); ld.Add("@ItemID", itemId); ld.Add("@UnitID", unitID); ld.Add("@StoreID", storeId); ld.Add("@FiscalYear", fiscalYear); ld.Add("@SupplierID", supplierId); Balance bal = new Balance(); bal.LoadFromSql("Rpt_BinCardBySupplier", ld, CommandType.StoredProcedure); bal.Rewind(); int Balance = 0; while (!bal.EOF) { Balance += Convert.ToInt32(bal.GetColumn("Balance")); bal.SetColumn("Balance", Balance); bal.MoveNext(); } return bal.DefaultView; }
/// <summary> /// Gets the un priced quantity. /// </summary> /// <param name="itemID">The item ID.</param> /// <param name="storeID">The store ID.</param> /// <param name="unitID">The unit ID.</param> /// <param name="manufacturerID">The manufacturer ID.</param> /// <param name="physicalStoreID">The physical store ID.</param> /// <param name="preferredExpiryDate">The preferred expiry date.</param> /// <returns></returns> public Int64 GetUnPricedQuantity(int itemID, int storeID, int? unitID, int? manufacturerID,int? physicalStoreID, DateTime? preferredExpiryDate) { string query; if (BLL.Settings.IsCenter) //If it is center, we don't require it to have been received by delivery notes. { query = HCMIS.Repository.Queries.Balance.SelectGetUnPricedQuantityForCenter(itemID, storeID, unitID, manufacturerID, physicalStoreID, preferredExpiryDate); } else { query = HCMIS.Repository.Queries.Balance.SelectGetUnPricedQuantityForHub(itemID, storeID, unitID, manufacturerID, physicalStoreID, preferredExpiryDate); } BLL.Balance bal = new Balance(); bal.LoadFromRawSql(query); try { if (bal.IsColumnNull("UQ")) return 0; return Convert.ToInt64(bal.GetColumn("UQ")); } catch { return 0; } }