/// <summary>
/// Gets the item by manufactuer and unit.
/// </summary>
/// <returns></returns>
public DataTable GetItemByManufactuerAndUnit()
{
string query = "";
//all items = 0
query = HCMIS.Repository.Queries.Item.SelectGetItemByManufactuerAndUnit();
Item itms = new Item();
itms.LoadFromRawSql(query);
return itms.DataTable;
}
/// <summary>
/// Gets list of ever received items
/// </summary>
/// <returns></returns>
public static Dictionary<int, int> GetRecievedItems()
{
var query = HCMIS.Repository.Queries.Item.SelectGetRecievedItems();
Item itms = new Item();
itms.LoadFromRawSql(query);
System.Collections.Generic.Dictionary<int, int> dict = new System.Collections.Generic.Dictionary<int, int>();
int[] arr = new int[itms.RowCount];
for (int i = 0; i < itms.RowCount; i++)
{
dict.Add(itms.ID, itms.ID);
itms.MoveNext();
}
return dict;
}
public static void FixExpirySettings()
{
// Applys the default setting for those columns that do have null as expiry setting
// pharmacuticals require batch and expiry, non pharmacuticals don't require it.
Item items = new Item();
var query = HCMIS.Repository.Queries.Item.UpdateFixExpirySettings();
items.LoadFromRawSql(query);
}
public static DataTable GetItemsDetailByFilter(String Filter)
{
var query = HCMIS.Repository.Queries.Item.SelectGetItemsDetailByFilter(Filter);
Item itms = new Item();
itms.LoadFromRawSql(query);
return itms.DataTable;
}
/// <summary>
/// Gets the near expiry breakdown.
/// </summary>
/// <param name="itemType">Type of the item.</param>
/// <returns></returns>
public static DataTable GetNearExpiryBreakdown(int itemType, int modeID ,int warehouseID)
{
var query = HCMIS.Repository.Queries.Item.SelectGetNearExpiryBreakdown(itemType, modeID ,warehouseID);
Item items = new Item();
items.LoadFromRawSql(query);
return items.DataTable;
}
/// <summary>
/// Gets the items by commodity type for transfer to account.
/// </summary>
/// <param name="AccountFrom">The account from.</param>
/// <param name="AccountTo">The account to.</param>
/// <returns></returns>
public static DataTable GetItemsByCommodityTypeForTransferToAccount(int AccountFrom, int AccountTo)
{
string query = "";
//all items = 0
query = HCMIS.Repository.Queries.Item.SelectGetItemsByCommodityTypeForTransferToAccount(AccountFrom, AccountTo);
Item itms = new Item();
itms.LoadFromRawSql(query);
return itms.DataTable;
}
/// <summary>
/// Returns items that have been issued using an STV/Invoice number for SRM
/// </summary>
/// <param name="receiptInvoiceID">The Receipt Invoice ID.</param>
/// <returns></returns>
public static DataTable GetItemsByReceiptInvoiceNoForSTVTransfer(int receiptInvoiceID)
{
string query = "";
query = HCMIS.Repository.Queries.Item.SelectGetItemsByReceiptInvoiceNoForSTVTransfer(receiptInvoiceID);
Item itms = new Item();
itms.LoadFromRawSql(query);
itms.AddColumn("LineNo", typeof(int));
// make a better numbering
int i = 1;
while (!itms.EOF)
{
itms.SetColumn("LineNo", i++);
itms.MoveNext();
}
return itms.DataTable;
}
public DataTable GetReceiveDocDetailForDiagnostics(int itemID,int ManufacturerID,int UnitID,int MovingAverageID)
{
string query = "";
//all items = 0
query = HCMIS.Repository.Queries.Item.SelectGetReceiveDocDetailForDiagnostics(itemID, ManufacturerID, UnitID, MovingAverageID);
Item itms = new Item();
itms.LoadFromRawSql(query);
return itms.DataTable;
}
public static DataTable GetAllItemsByCommodityType(int CommodityType)
{
Item itms = new Item();
itms.LoadFromRawSql(HCMIS.Repository.Queries.Item.SelectGetAllItemsByCommodityType(CommodityType));
return itms.DataTable;
}
public static DataTable GetActiveItemsBySupplier(int supplierID, int? storeID)
{
string query = HCMIS.Repository.Queries.Item.SelectGetActiveItemsBySupplier(supplierID, storeID);
Item it = new Item();
it.LoadFromRawSql(query);
return it.DataTable;
}
/// <summary>
/// Gets the receive doc by item and manufactuer and unit.
/// </summary>
/// <param name="ItemID">The item ID.</param>
/// <param name="ManufacturerID">The manufacturer ID.</param>
/// <param name="ItemUnitID">The item unit ID.</param>
/// <param name="AccountID">The account ID.</param>
/// <returns></returns>
public DataTable GetReceiveDocByItemAndManufactuerAndUnit(int ItemID, int ManufacturerID, int ItemUnitID,int AccountID)
{
string query = "";
//all items = 0
query = HCMIS.Repository.Queries.Item.SelectGetReceiveDocByItemAndManufactuerAndUnit(ItemID, ManufacturerID, ItemUnitID, AccountID);
Item itms = new Item();
itms.LoadFromRawSql(query);
return itms.DataTable;
}
public DataTable GetJournalEntriesForDiagnostics(int AffectedLedgerID)
{
string query = "";
//all items = 0
query = HCMIS.Repository.Queries.Item.SelectGetJournalEntriesForDiagnostics(AffectedLedgerID);
Item itms = new Item();
itms.LoadFromRawSql(query);
return itms.DataTable;
}
public DataTable GetItemsDistributedByAccount(int accountid, DateTime fromdate, DateTime todate)
{
var items = new Item();
var query = HCMIS.Repository.Queries.Item.SelectItemsDistributedByAccount(accountid, fromdate, todate);
items.LoadFromRawSql(query);
return items.DataTable;
}
/// <summary>
/// Returns the commodites by type
/// </summary>
/// <param name="commodityType">0 - All Items</param>
/// <param name="storeID">The store ID.</param>
/// <returns></returns>
public static DataTable GetActiveItemsByCommodityTypeForReceiveScreen(int commodityType, int? storeID)
{
string query = "";
if (Settings.IsRdfMode)
{
// let 0 mean all items
if (commodityType == 0)
{
query = HCMIS.Repository.Queries.Item.SelectGetActiveItemsByCommodityTypeForReceiveScreenAllItem(query, storeID);
}
else
{
query = HCMIS.Repository.Queries.Item.SelectGetActiveItemsByCommodityTypeForReceiveScreen(commodityType, query, storeID);
}
}
else
{
// handle the freaking program store
query = HCMIS.Repository.Queries.Item.SelectGetActiveItemsByCommodityTypeForReceiveScreenNotRdfMode(
commodityType, query, storeID);
}
Item itms = new Item();
itms.LoadFromRawSql(query);
return itms.DataTable;
}
public static DataTable GetItemDetailForItemChanger(int ItemID,int ManufacturerID,int UnitID,int ActivityID)
{
var query = HCMIS.Repository.Queries.Item.SelectGetItemDetailForItemChanger(ItemID, ManufacturerID, UnitID, ActivityID);
Item itms = new Item();
itms.LoadFromRawSql(query);
return itms.DataTable;
}
/// <summary>
/// Brings Active Items that have stock and
/// Excludes units of items that are stocked out. Incluldes items that are completely stocked out.
/// (For example, if it has two units and one is stocked out,
/// it doesn't get included.) If it has two units and both are stocked out, it gets included.
/// </summary>
/// <param name="commodityType"></param>
/// <returns></returns>
public static DataTable GetCommodityForRequisition(int commodityTypeId,int modeId)
{
var itmsNormal = new Item();
var query = HCMIS.Repository.Queries.Item.SelectGetCommodityForRequisition(commodityTypeId, modeId, BLL.Settings.IsCenter);
itmsNormal.LoadFromRawSql(query);
return itmsNormal.DefaultView.ToTable();
}
/// <summary>
/// Returns the price list by Manufacturer for items received on a specific date
/// </summary>
/// <param name="date">Date in Gregorian Calendar</param>
/// <param name="storeID">The store ID.</param>
/// <returns></returns>
public static DataTable GetItemPriceListByManufacturer(DateTime date, Int32 storeID)
{
var query = HCMIS.Repository.Queries.Item.SelectGetItemPriceListByManufacturer(date, storeID);
Item itm = new Item();
itm.LoadFromRawSql(query);
itm.AddColumn("UnitsSelling", typeof(string));
//For the items that haven't been priced
while (!itm.EOF)
{
string priceString = itm.GetColumn("UnitsSellingBeforeFormat").ToString();
itm.SetColumn("UnitsSelling", FormatNumber(priceString));
itm.MoveNext();
}
return itm.DataTable;
}
/// <summary>
/// Brings All Active Items Previously Issued to the specefic Facility and that have stock and
/// Excludes units of items that are stocked out. Incluldes items that are completely stocked out.
/// (For example, if it has two units and one is stocked out,
/// it doesn't get included.) If it has two units and both are stocked out, it gets included.
/// </summary>
/// <param name="facilityID"></param>
/// <param name="modeId"></param>
/// <param name="range"> </param>
/// <returns></returns>
public static DataTable GetCommodityHistoryByFacilityForRequisition(int facilityID, int modeId,int range = -1)
{
var itmsNormal = new Item();
var query = HCMIS.Repository.Queries.Item.SelectGetCommodityHistoryByFacilityForRequisition(facilityID, modeId, BLL.Settings.IsCenter,range);
itmsNormal.LoadFromRawSql(query);
return itmsNormal.DefaultView.ToTable();
}
/// <summary>
/// Gets the items by commodity type for transfer to hub.
/// </summary>
/// <param name="commodityType">Type of the commodity.</param>
/// <param name="StoreID">The store ID.</param>
/// <returns></returns>
public static DataTable GetItemsByCommodityTypeForTransferToHub(int commodityType,int StoreID)
{
string query = "";
//all items = 0
if (commodityType == 0)
{
query = HCMIS.Repository.Queries.Item.SelectGetItemsByCommodityTypeForTransferToHub(StoreID);
}
else
{
query = HCMIS.Repository.Queries.Item.SelectGetItemsByCommodityTypeForTransferToHubByCommodity(commodityType, StoreID);
}
Item itms = new Item();
itms.LoadFromRawSql(query);
return itms.DataTable;
}
/// <summary>
/// Returns the commodites by type
/// </summary>
/// <param name="commodityType">0 - All Items</param>
/// <param name="storeID">The store ID.</param>
/// <returns></returns>
public static DataTable GetDistinctActiveItemsByCommodityType(int commodityType, int userID,bool ExcludeNeverReceived = true)
{
string query = "";
// let 0 mean all items
if (commodityType == 0)
{
query = HCMIS.Repository.Queries.Item.SelectGetDistinctActiveItemsByCommodityTypeAllItems(query, userID, ExcludeNeverReceived);
}
else
{
query = HCMIS.Repository.Queries.Item.SelectGetDistinctActiveItemsByCommodityType(commodityType, query, userID, ExcludeNeverReceived);
}
Item itms = new Item();
itms.LoadFromRawSql(query);
return itms.DataTable;
}
/// <summary>
/// Returns items that have been issued using an STV/Invoice number for SRM
/// </summary>
/// <param name="stvID">The STV ID.</param>
/// <param name="storeID">The store ID.</param>
/// <returns></returns>
public static DataTable GetItemsBySTVInvoiceNoForStockReturn(int stvID,int storeID)
{
var query = HCMIS.Repository.Queries.Item.SelectGetItemsBySTVInvoiceNoForStockReturn(stvID, storeID);
Item itms = new Item();
itms.LoadFromRawSql(query);
itms.AddColumn("LineNo", typeof(int));
// make a better numbering
int i = 1;
while (!itms.EOF)
{
itms.SetColumn("LineNo", i++);
itms.MoveNext();
}
return itms.DataTable;
}
public static DataTable GetFacilityHistory(int facilityID, DateTime fromDate, DateTime toDate)
{
var query = HCMIS.Repository.Queries.Item.SelectGetFacilityHistory(facilityID, fromDate.ToShortDateString(), toDate.ToShortDateString());
var item = new Item();
item.LoadFromRawSql(query);
return item.DefaultView.ToTable();
}
/// <summary>
/// Gets last received date for an item in a logical store
/// </summary>
/// <param name="itemId">The item id.</param>
/// <param name="storeId">The store id.</param>
/// <returns></returns>
public static DateTime GetLastReceiveDate(int itemId, int storeId)
{
Item items = new Item();
items.LoadFromRawSql(HCMIS.Repository.Queries.Item.SelectGetLastReceiveDate(itemId, storeId));
DateTime dt = (items.DataTable.Rows.Count > 0) ? Convert.ToDateTime(items.DataTable.Rows[0]["Date"]) : new DateTime();
return dt;
}
public static DataTable GetItemByIdForCustomIssue(int ItemID, int unitID, int StoreID)
{
string query = "";
if (Settings.IsCenter)
{
query = HCMIS.Repository.Queries.Item.SelectGetItemByIdForCustomIssueCenter(ItemID, unitID, StoreID);
}
else
{
query = HCMIS.Repository.Queries.Item.SelectGetItemByIdForCustomIssueHub(ItemID, unitID, StoreID);
}
Item itms = new Item();
itms.LoadFromRawSql(query);
return itms.DataTable;
}
//Added warehouse filter
/// <summary>
/// Get List of expired items by batch
/// </summary>
/// <param name="storeId">The store id.</param>
/// <param name="months">The months.</param>
/// <param name="category">The category.</param>
/// <returns></returns>
public static DataView GetNearlyExpiredItemsByBatch(int modeID, int months, int category ,int warehouseID)
{
var query = HCMIS.Repository.Queries.Item.SelectGetNearlyExpiredItemsByBatch(modeID, months, category , warehouseID);
Item itms = new Item();
itms.LoadFromRawSql(query);
return itms.DefaultView;
}
/// <summary>
/// Gets the item by id for transfer to hub.
/// </summary>
/// <param name="ItemID">The item ID.</param>
/// <param name="unitID">The unit ID.</param>
/// <param name="StoreID">The store ID.</param>
/// <returns></returns>
public static DataTable GetItemByIdForTransfer(int ItemID, int unitID, int StoreID,int PhysicalStoreID)
{
var query = HCMIS.Repository.Queries.Item.SelectGetItemByIdForTransfer(ItemID, unitID, StoreID, PhysicalStoreID);
Item itms = new Item();
itms.LoadFromRawSql(query);
return itms.DataTable;
}
/// <summary>
/// Checks if the item is has to apply moving average or not
/// </summary>
/// <param name="itemId">The item id.</param>
/// <param name="supplierId">The supplier id.</param>
/// <returns>
/// <c>true</c> if [is moving average] [the specified item id]; otherwise, <c>false</c>.
/// </returns>
public static bool IsMovingAverage(int itemId, int supplierId)
{
//TODO: this has to be a flag on the
var query = HCMIS.Repository.Queries.Item.SelectIsMovingAverage(itemId, supplierId);
Item itms = new Item();
itms.LoadFromRawSql(query);
return (itms.RowCount > 0);
}
public static DataTable GetItemByIdForTransferToHub(int ItemID, int unitID, int StoreID)
{
string query = "";
query = HCMIS.Repository.Queries.Item.SelectGetItemByIdForTransferToHub(ItemID, unitID, StoreID);
Item itms = new Item();
itms.LoadFromRawSql(query);
return itms.DataTable;
}
public DataTable GetCostTierComparision()
{
string query = "";
//all items = 0
query = HCMIS.Repository.Queries.Item.SelectGetCostTierComparision();
Item itms = new Item();
itms.LoadFromRawSql(query);
return itms.DataTable;
}
/// <summary>
/// Gets the issues by receiving unit faster.
/// </summary>
/// <param name="storeID">The store ID.</param>
/// <param name="routeID">The route ID.</param>
/// <param name="type">The type.</param>
/// <returns></returns>
/// Shit: what a name? and it does crazy stuff too.
public DataTable GetIssuesByReceivingUnitFaster(int storeID, int routeID, int type)
{
var ru = new Institution();
if (Settings.IsRdfMode)
ru.GetFacilitiesThatEverReceivedItems();
else
ru.GetAllUnderRoute(routeID);
//Load all the items.
var query = HCMIS.Repository.Queries.Item.SelectGetIssuesByReceivingUnitFaster(type);
this.LoadFromRawSql(query);
//Add the columns
while (!ru.EOF)
{
this.AddColumn(string.Format("{0},{1}", ru.Name, ru.ID), typeof(int));
ru.MoveNext();
}
while (!this.EOF)
{
Institution rus = new Institution();
if (Settings.IsRdfMode)
rus.GetFacilitiesThatEverReceivedItems();
else
rus.GetAllUnderRoute(routeID);
while (!rus.EOF)
{
int itemID = int.Parse(this.GetColumn("ID").ToString());
var itm = new Item();
var issueQuery = HCMIS.Repository.Queries.Item.SelectGetIssuesByReceivingUnitFasterIssueQuery(itemID, rus.ID);
itm.LoadFromRawSql(issueQuery);
if (itm.RowCount > 0 && !itm.IsColumnNull("Quantity"))
this.SetColumn(string.Format("{0},{1}", ru.Name, ru.ID), itm.GetColumn("Quantity"));
rus.MoveNext();
}
this.MoveNext();
}
return this.DataTable;
}
