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()); }
/// <summary> /// Get available number of Months the item was in store in all logical stores /// </summary> /// <param name="itemId"></param> /// <param name="dt1"></param> /// <param name="dt2"></param> /// <returns></returns> public static int GetAvailableNoOfMonthsInAllStores(int itemId, DateTime dt1, DateTime dt2) { Balance balance = new Balance(); int count = 1; balance.FlushData(); balance.LoadFromRawSql(HCMIS.Repository.Queries.Balance.SelectGetAvailableNoOfMonthsInAllStores(itemId, dt1, dt2)); DateTime dtLast = (balance.DataTable.Rows.Count > 0) ? balance.GetDateTime("Date") : dt2; TimeSpan tDiff = dt2 - dtLast; count = tDiff.Days / 30; count = (count > 0) ? count : 1; return count; }