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);
}