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 DataTable GetStockStatusByPhysicalStore(int physicalStoreID, int activityID, bool showStockout)
 {
     var query = HCMIS.Repository.Queries.Balance.SelectGetStockStatusByPhysicalStore(physicalStoreID, activityID,
                                                                                      showStockout);
     Balance balance = new Balance();
     balance.LoadFromRawSql(query);
     return balance.DataTable;
 }
        public static DataTable GetStockStatusByModeDamagedExcluded()
        {
            var query = HCMIS.Repository.Queries.Balance.SelectGetStockStatusByModeDamagedExcluded();
            Balance balance = new Balance();
            balance.LoadFromRawSql(query);

            return balance.DataTable;
        }
        public static DataTable GetStockStatusByAccountDamagedExcluded(int AccountID,bool showUnitPrice = false)
        {
            string query;
            if(showUnitPrice)
            {
                query = HCMIS.Repository.Queries.Balance.SelectGetStockStatusByAccountDamagedExcludedShowUnitPrice(AccountID);
            }
            else
            {
                query = HCMIS.Repository.Queries.Balance.SelectGetStockStatusByAccountDamagedExcludedHideUnitPrice(AccountID);
            }

            Balance balance = new Balance();
            balance.LoadFromRawSql(query);
            balance.AddColumn("LineNo", typeof(int));
            int i = 1;
            while (!balance.EOF)
            {
                balance.SetColumn("LineNo", i++);
                balance.MoveNext();
            }
            return balance.DataTable;
        }
 public static DataView GetSOHReportForFinance(int warehouseID, int activityID)
 {
     var query = HCMIS.Repository.Queries.Balance.SelectGetSOHReportForFinance(warehouseID, activityID);
     BLL.Balance balance = new Balance();
     balance.LoadFromRawSql(query);
     return balance.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>
 /// Makes sure that every quantity in the Loss/Adjustment table is entered as positive.
 /// The Losses are entered with positive quantity and with the Losses bit set to 1.
 /// The adjustments are entered with positive quantity and with the Losses bit set to 0.
 /// </summary>
 public static void GetAndFixLossAdjustmentInconsistencies()
 {
     const string fixQuery = "update disposal set quantity=0-quantity where quantity<0";
     BLL.Balance bal = new Balance();
     bal.LoadFromRawSql(fixQuery);
 }
 public DataView GetApprovedByOrderID(int orderID)
 {
     var query = HCMIS.Repository.Queries.Balance.SelectApprovedInformationByOrderID(orderID);
     var balance = new Balance();
     balance.LoadFromRawSql(query);
     return balance.DefaultView;
 }
        public static DataTable GetCountSheetByPhysicalStore(int physicalStoreID, int activityID, bool showStockout)
        {
            string stockOutQuery = "";
            if (!showStockout)
            {
                stockOutQuery = HCMIS.Repository.Queries.Balance.SelectGetCountSheetByPhysicalStoreShowStockOut(physicalStoreID, activityID, stockOutQuery);
            }

            var query = HCMIS.Repository.Queries.Balance.SelectGetCountSheetByPhysicalStoreHideStockOut(physicalStoreID, activityID, stockOutQuery);
            Balance balance = new Balance();
            balance.LoadFromRawSql(query);
            return balance.DataTable;
        }
        /// <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;
        }
 public DataView LoadHubRequested(int itemID ,int itemUnitID ,int modeID)
 {
     var query = HCMIS.Repository.Queries.Balance.SelectHubRequested(itemID ,itemUnitID,modeID);
     var balance = new Balance();
     balance.LoadFromRawSql(query);
     return balance.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;
            }
        }
 public decimal GetTotalApprovedQuantityByItem(int orderId, int itemId, int itemUnitId, int modeId)
 {
     var query = HCMIS.Repository.Queries.Balance.SelectTotalApprovedQty( orderId,itemId, itemUnitId, modeId);
     var balance = new Balance();
     balance.LoadFromRawSql(query);
     return balance.Getdecimal("TotalRequested");
 }
        /// <summary>
        /// Gets all SOH for print out.
        /// </summary>
        /// <param name="userId">The user id.</param>
        /// <param name="year">The year.</param>
        /// <param name="month">The month.</param>
        /// <returns></returns>
        public static DataTable GetAllSOHForPrintOut(int userId, int year, int month)
        {
            DataTable datatable = null;

            Activity stores = new Activity();
            stores.LoadByUserID(userId);
            Balance balance = new Balance();
            while(!stores.EOF){
                string account = stores.FullActivityName;
                if(datatable == null){
                    var query = HCMIS.Repository.Queries.Balance.SelectGetAllSOHForPrintOutInitial(stores.ID);
                    balance.LoadFromRawSql( query );
                    datatable = balance.DataTable;
                    datatable.Columns.Add("Account");
                    foreach (DataRow dr in datatable.Rows)
                    {
                        dr["Account"] = account;
                    }
                }else{
                    var query = HCMIS.Repository.Queries.Balance.SelectGetAllSOHForPrintOut(stores.ID);
                    balance.LoadFromRawSql(query);
                    DataTable dtbl = balance.DataTable;
                    dtbl.Columns.Add("Account");
                    foreach(DataRow dr in dtbl.Rows){
                        dr["Account"] = account;
                        datatable.ImportRow(dr);
                    }
                }
                stores.MoveNext();
            }
            return datatable;
        }
 public static DataTable GetCountSheetByPhysicalStoreWithBatchExpiry(int physicalStoreID, int activityID, bool showStockout, bool showLocations)
 {
     var query =
         HCMIS.Repository.Queries.Balance.SelectGetCountSheetByPhysicalStoreWithBatchExpiryHideStockOut(
             physicalStoreID, activityID, showLocations, showStockout);
     Balance balance = new Balance();
     balance.LoadFromRawSql(query);
     return balance.DataTable;
 }
        /// <summary>
        /// Gets Price for items that are in stock.
        /// </summary>
        /// <param name="storeId"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public static DataTable PriceOfAllItems(int storeId, int year, int month)
        {
            // this has to return the balance of all item as the name discribes
            // this is there fore working based on the stock on hand STORED procedure, if  the stored procedure for some reason threw an error

            Balance bal = new Balance();
            var query = HCMIS.Repository.Queries.Balance.SelectPriceOfAllItems(storeId);
            bal.LoadFromRawSql(query);
            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"));
 }
 public DataView GetBalanceByOrder(int orderID,int userId)
 {
     var query = HCMIS.Repository.Queries.Balance.SelectvwDispatchableItemByOrderID(orderID,userId);
     var balance = new Balance();
     balance.LoadFromRawSql(query);
     return balance.DefaultView;
 }
 /// <summary>
 /// Takes care of double entries in the Inventory Table.  One entered manually and another entered automatically
 /// </summary>
 public static void GetAndFixInventoryInconsistencies()
 {
     const string fixQuery = "delete from yearend where automaticallyentered=1 and itemid in (select itemid from yearend group by itemid,storeid,year having count(*)>1)";
     BLL.Balance bal = new Balance();
     bal.LoadFromRawSql(fixQuery);
 }