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