public static DataTable GetAllFreeNonBulk()
{
string query = String.Format("select (st.Prefix + ' ' + pl.Label) as PalletLocationName , * from PalletLocation pl join StorageType st on pl.StorageTypeID = st.ID where StorageTypeID <> {0} and StorageTypeID <> {1} and pl.PalletID is null", StorageType.BulkStore,StorageType.PickFace);
PalletLocation pl = new PalletLocation();
pl.LoadFromRawSql(query);
return pl.DataTable;
}
public static DataTable GetAll()
{
PalletLocation pl = new PalletLocation();
String query = String.Format("select (st.Prefix + ' ' + pl.Label) as PalletLocationName , * from PalletLocation pl join StorageType st on pl.StorageTypeID = st.ID");
pl.LoadFromRawSql(query);
return pl.DataTable;
}
public static DataTable GetAllFreeNonBulk()
{
string query = String.Format("select (st.Prefix + ' ' + pl.Label) as PalletLocationName , * from PalletLocation pl join StorageType st on pl.StorageTypeID = st.ID where StorageTypeID <> {0} and StorageTypeID <> {1} and pl.PalletID is null", StorageType.BulkStore,StorageType.PickFace);
PalletLocation pl = new PalletLocation();
pl.LoadFromRawSql(query);
return pl.DataTable;
}
public static DataTable GetAll()
{
PalletLocation pl = new PalletLocation();
String query = String.Format("select (st.Prefix + ' ' + pl.Label) as PalletLocationName , * from PalletLocation pl join StorageType st on pl.StorageTypeID = st.ID");
pl.LoadFromRawSql(query);
return pl.DataTable;
}
public static DataTable GetAllFree(string StorageType)
{
string query = String.Format("select (st.Prefix + ' ' + pl.Label) as PalletLocationName , * from PalletLocation pl join StorageType st on pl.StorageTypeID = st.ID where StorageTypeID = {0} and pl.PalletID is null", StorageType);
PalletLocation pl = new PalletLocation();
pl.LoadFromRawSql(query);
return(pl.DataTable);
}
public static DataTable GetAllFreeFor(int itemID)
{
string query = String.Format("select (st.Prefix + ' ' + pl.Label) as PalletLocationName , * from PalletLocation pl join StorageType st on pl.StorageTypeID = st.ID join ItemPrefferedLocation ipl on ipl.PalletLocationID = pl.ID where ipl.ItemID = {0} and pl.PalletID is null", itemID);
PalletLocation pl = new PalletLocation();
pl.LoadFromRawSql(query);
if (pl.RowCount == 0)
{
Items itm = new Items();
itm.LoadByPrimaryKey(itemID);
return PalletLocation.GetAllFree(itm.StorageTypeID.ToString());
}
return pl.DataTable;
}
public static DataTable GetAllFreeFor(int itemID)
{
string query = String.Format("select (st.Prefix + ' ' + pl.Label) as PalletLocationName , * from PalletLocation pl join StorageType st on pl.StorageTypeID = st.ID join ItemPrefferedLocation ipl on ipl.PalletLocationID = pl.ID where ipl.ItemID = {0} and pl.PalletID is null", itemID);
PalletLocation pl = new PalletLocation();
pl.LoadFromRawSql(query);
if (pl.RowCount == 0)
{
Items itm = new Items();
itm.LoadByPrimaryKey(itemID);
return PalletLocation.GetAllFree(itm.StorageTypeID.ToString());
}
return pl.DataTable;
}
public int GetpalletidbyPalletLocationOrgetnew( int palletlocationid)
{
string query = HCMIS.Repository.Queries.PalletLocation.selectpalletidbyPalletLocation(palletlocationid);
PalletLocation pl = new PalletLocation();
pl.LoadFromRawSql(query);
if (pl.RowCount > 0)
return pl.PalletID;
else
{// create new pallet
Pallet pallet = new Pallet();
pallet.AddNew();
string query2 = HCMIS.Repository.Queries.PalletLocation.selectstoreIDbyPalletLocationID(palletlocationid);
PalletLocation pl2 = new PalletLocation();
pl2.LoadFromRawSql(query2);
pallet.StorageTypeID = pl2.StorageTypeID;
pallet.Save();
return pallet.ID;
}
}
/// <summary>
/// Gets the transfer pallet location.
/// </summary>
/// <returns></returns>
public static int GetTransferPalletLocation(int physicalStoreID)
{
var pl = new PalletLocation();
pl.LoadFromRawSql(HCMIS.Repository.Queries.PalletLocation.SelectGetTransferPalletLocation(), StorageType.Quaranteen);
if (pl.RowCount == 0)
{
CreateDefaultTransfer();
return GetQuaranteenPalletLocation(physicalStoreID);
}
return pl.ID;
}
public static int GetQuaranteenPalletLocationByPalletLocationID(int palletLocationID)
{
PhysicalStore ps=new PhysicalStore();
ps.LoadByPalletLocationID(palletLocationID);
int physicalStoreID = ps.ID;
int storageType;
int.TryParse(StorageType.Quaranteen, out storageType);
var pl = new PalletLocation();
pl.LoadFromRawSql(HCMIS.Repository.Queries.PalletLocation.SelectGetQuaranteenPalletLocation(physicalStoreID, storageType));
if (pl.RowCount == 0)
{
CreateDefaultQuaranteen();
return GetQuaranteenPalletLocation(physicalStoreID);
}
return pl.ID;
}
/// <summary>
/// Gets the quaranteen pallet location.
/// </summary>
/// <returns></returns>
public static int GetQuaranteenPalletLocation(int physicalStoreID)
{
int storageType;
int.TryParse(StorageType.Quaranteen, out storageType);
var pl = new PalletLocation();
pl.LoadFromRawSql(HCMIS.Repository.Queries.PalletLocation.SelectGetQuaranteenPalletLocation(physicalStoreID, storageType));
if (pl.RowCount == 0)
{
var physicalStore = new BLL.PhysicalStore();
physicalStore.LoadByPrimaryKey(physicalStoreID);
throw new Exception(String.Format("Please Create a suspended pallet location on store: {0} ",physicalStore.Name));
}
return pl.ID;
}
public static DataTable GetAllFreeForIntheSameStore(int itemID, int palletid)
{
string query = HCMIS.Repository.Queries.PalletLocation.SelectGetAllFreeForthesameStore(itemID,palletid);
PalletLocation pl = new PalletLocation();
pl.LoadFromRawSql(query);
return pl.DataTable;
}
/// <summary>
/// Gets all.
/// </summary>
/// <returns></returns>
public static DataTable GetAll()
{
PalletLocation pl = new PalletLocation();
String query = HCMIS.Repository.Queries.PalletLocation.SelectGetAll();
pl.LoadFromRawSql(query);
return pl.DataTable;
}
public static DataTable GetAllByPhisicalStore(int physicalStoreId,bool isNormal)
{
PalletLocation pl = new PalletLocation();
String query = isNormal
? HCMIS.Repository.Queries.PalletLocation.SelectNormalPalletLocationByPhysicalStore(physicalStoreId,
CurrentContext.
UserId)
: HCMIS.Repository.Queries.PalletLocation.SelectQuaranteenPalletLocationByPhysicalStore(
physicalStoreId, CurrentContext.UserId);
pl.LoadFromRawSql(query);
return pl.DataTable;
}
/// <summary>
/// Gets all free Pallet Locations
/// </summary>
/// <param name="StorageType">Type of the storage.</param>
/// <returns></returns>
public static DataTable GetAllFree(string StorageType)
{
string query;
if ( StorageType == BLL.StorageType.BulkStore)
{
query = HCMIS.Repository.Queries.PalletLocation.SelectGetAllFreeBulkStore(StorageType, BLL.StorageType.StackedStorage, BLL.StorageType.PickFace, BLL.StorageType.Free);
}
else if(StorageType == BLL.StorageType.Free)
{
query = HCMIS.Repository.Queries.PalletLocation.SelectGetAllFreeFree(StorageType);
}
else
{
query = HCMIS.Repository.Queries.PalletLocation.SelectGetAllFreeElse(StorageType, BLL.StorageType.Free);
}
PalletLocation pl = new PalletLocation();
pl.LoadFromRawSql(query);
return pl.DataTable;
}
/// <summary>
/// Gets the current and all free for.
/// </summary>
/// <param name="itemID">The item ID.</param>
/// <param name="current">The current.</param>
/// <returns></returns>
public static DataTable GetCurrentAndAllFreeFor(int itemID, int current)
{
string query = HCMIS.Repository.Queries.PalletLocation.SelectGetCurrentAndAllFreeFor(itemID);
PalletLocation pl = new PalletLocation();
pl.LoadFromRawSql(query);
Item itm = new Item();
itm.LoadByPrimaryKey(itemID);
pl.DataTable.Merge(PalletLocation.GetAllFreeForItem(itemID, itm.StorageTypeID.ToString()));
return pl.DataTable;
}
/// <summary>
/// Gets all occupied.
/// </summary>
/// <returns></returns>
public static DataView GetAllOccupied()
{
string query = HCMIS.Repository.Queries.PalletLocation.SelectGetAllOccupied();
PalletLocation pl = new PalletLocation();
pl.LoadFromRawSql(query);
DataRowView drv = pl.DefaultView.AddNew();
drv["Label"] = "New Pallet";
drv["ID"] = 0;
return pl.DefaultView;
}
/// <summary>
/// Gets all free non bulk.
/// </summary>
/// <returns></returns>
public static DataTable GetAllFreeNonBulk()
{
string query = HCMIS.Repository.Queries.PalletLocation.SelectGetAllFreeNonBulk(StorageType.BulkStore, StorageType.PickFace, StorageType.Quaranteen, StorageType.Free);
PalletLocation pl = new PalletLocation();
pl.LoadFromRawSql(query);
return pl.DataTable;
}
/// <summary>
/// Gets all free for pallet locations item.
/// </summary>
/// <param name="itemID">The item ID.</param>
/// <param name="StorageType">Type of the storage.</param>
/// <param name="volume">The volume.</param>
/// <param name="userID">The user ID.</param>
/// <returns></returns>
public static DataTable GetAllFreeForItem(int itemID, string StorageType, double volume,int userID)
{
if (volume == 0)
{
volume = 1;
}
//Hack to avoid division by zero, it needs to be fixed somehow.
string query;
if (StorageType == BLL.StorageType.BulkStore)
{
query = HCMIS.Repository.Queries.PalletLocation.SelectGetAllFreeForItemBulkStore(itemID, StorageType, volume, userID, BLL.StorageType.StackedStorage, BLL.StorageType.Free);
}
else if (StorageType == BLL.StorageType.Free)
{
query =
HCMIS.Repository.Queries.PalletLocation.SelectGetAllFreeForItem(itemID, StorageType, volume, userID);
}
else
{
// load existing locations that contain the item.
query = HCMIS.Repository.Queries.PalletLocation.SelectGetAllFreeForItemElseExistingLocation(itemID);
PalletLocation pl = new PalletLocation();
pl.LoadFromRawSql(query);
//TODO: What the ins string here is used for is not clear. This query needs to be changed. It seems like it's needlessly complicated
string ins = "";
while (!pl.EOF)
{
if (ins == "")
{
ins = pl.PalletID.ToString();
}
else
{
ins += "," + pl.PalletID;
}
pl.MoveNext();
}
//TOFIX: this is a query where the error resides
query = HCMIS.Repository.Queries.PalletLocation.SelectGetAllFreeForItemElseError(itemID, StorageType, volume, userID, ins, BLL.StorageType.Free);
}
PalletLocation plc = new PalletLocation();
plc.LoadFromRawSql(query);
return plc.DataTable;
}
/// <summary>
/// Gets all free pallet locations for item.
/// </summary>
/// <param name="itemID">The item ID.</param>
/// <param name="StorageType">Type of the storage.</param>
/// <returns></returns>
public static DataTable GetAllFreeForItem(int itemID, string StorageType,int shelfID = 0)
{
string query;
if (StorageType == BLL.StorageType.Free)// This option doesnt work so I just left it for the time being //
{
query = HCMIS.Repository.Queries.PalletLocation.SelectGetAllFreeForItemFree(itemID, StorageType);
}
else
{
query = HCMIS.Repository.Queries.PalletLocation.SelectGetAllFreeForItemElse(itemID, StorageType, BLL.StorageType.BulkStore,shelfID);
}
PalletLocation pl = new PalletLocation();
pl.LoadFromRawSql(query);
return pl.DataTable;
}
public DataTable GetPalletLocationsOnTheSameStore(int palletId)
{
string query = HCMIS.Repository.Queries.PalletLocation.SelectPalletLocationsOnTheSameStore(palletId);
PalletLocation pl = new PalletLocation();
pl.LoadFromRawSql(query);
return pl.DataTable;
}
/// <summary>
/// Garbages the collection.
/// </summary>
public static void GarbageCollection()
{
// clean the items with 0 balance and still holding the pallet location.
string query = HCMIS.Repository.Queries.PalletLocation.UpdateGarbageCollection();
var pl = new PalletLocation();
pl.LoadFromRawSql(query);
// clean out the removed pallet locations.
query = HCMIS.Repository.Queries.PalletLocation.UpdateGarbageCollectionPalletLocation();
pl.LoadFromRawSql(query);
}
/// <summary>
/// Gets all Pallet locations by warehouse:
/// </summary>
/// <param name="isSuspended"></param>
/// <param name="warehouseID"></param>
/// <returns></returns>
public static DataTable GetPalletLocation(bool isSuspended,int warehouseID)
{
var pl = new PalletLocation();
string query = !isSuspended ? HCMIS.Repository.Queries.PalletLocation.SelectNormalPalletLocation(warehouseID, CurrentContext.UserId) : HCMIS.Repository.Queries.PalletLocation.SelectQuaranteenPalletLocation(warehouseID, CurrentContext.UserId);
pl.LoadFromRawSql(query);
return pl.DataTable;
}
public static DataTable GetAll(int physicalStoreId)
{
PalletLocation pl = new PalletLocation();
String query = HCMIS.Repository.Queries.PalletLocation.SelectGetAll(physicalStoreId, StorageType.Quaranteen);
pl.LoadFromRawSql(query);
return pl.DataTable;
}
/// <summary>
/// Gets the quaranteen.
/// </summary>
/// <param name="userID">The user ID.</param>
/// <param name="warehouseID">WarehouseID default is null </param>
/// <returns></returns>
public static DataTable GetQuaranteen(int userID,int? warehouseID =null)
{
var query =
HCMIS.Repository.Queries.PalletLocation.SelectGetQuaranteen(userID, StorageType.Quaranteen);
var pl = new PalletLocation();
pl.LoadFromRawSql(query);
var dataTable = pl.DataTable;
if(warehouseID.HasValue)
{
var rows = dataTable.Select("WarehouseID =" + warehouseID.Value);
dataTable = rows.Length>0?rows.CopyToDataTable():null;
}
return dataTable;
}
/// <summary>
/// Gets the free in.
/// </summary>
/// <param name="selectedRackID">The selected rack ID.</param>
/// <returns></returns>
public static DataView GetFreeIn(int selectedRackID)
{
Shelf slf = new Shelf();
slf.LoadByPrimaryKey(selectedRackID);
string query = HCMIS.Repository.Queries.PalletLocation.SelectGetFreeIn(slf.ShelfStorageType, slf.ID);
PalletLocation pl = new PalletLocation();
pl.LoadFromRawSql(query);
return pl.DefaultView;
}
/// <summary>
/// Gets all free for.
/// </summary>
/// <param name="itemID">The item ID.</param>
/// <param name="volume">The volume.</param>
/// <param name="userID">The user ID.</param>
/// <param name="warehouseID">WarehouseID default is null </param>
/// <returns></returns>
public static DataTable GetAllFreeFor(int itemID, double volume, int userID,int? warehouseID=null)
{
if (volume == 0)
{
//Hack to avoid division by zero, it needs to be fixed somehow.
volume = 1;
}
var query =
HCMIS.Repository.Queries.PalletLocation.SelectGetAllFreeFor(itemID, volume, userID);
var pl = new PalletLocation();
pl.LoadFromRawSql(query);
var itm = new Item();
itm.LoadByPrimaryKey(itemID);
pl.DataTable.Merge(GetAllFreeForItem(itemID, itm.StorageTypeID.ToString(), volume, userID));
var dataTable = pl.DataTable;
if (warehouseID.HasValue)
{
var rows = dataTable.Select("WarehouseID =" + warehouseID.Value);
dataTable = rows.Length > 0 ? rows.CopyToDataTable() : null;
}
return dataTable;
}
/// <summary>
/// Gets the non free.
/// </summary>
/// <param name="selectedRackID">The selected rack ID.</param>
/// <returns></returns>
public static DataView GetNonFree(int selectedRackID)
{
string query = HCMIS.Repository.Queries.PalletLocation.SelectGetNonFree(selectedRackID);
PalletLocation pl = new PalletLocation();
pl.LoadFromRawSql(query);
return pl.DefaultView;
}
/// <summary>
/// Calculates all volumes.
/// </summary>
/// <param name="itemID">The item ID.</param>
internal static void CalculateAllVolumes(int itemID)
{
PalletLocation pl = new PalletLocation();
pl.LoadFromRawSql(HCMIS.Repository.Queries.PalletLocation.SelectCalculateAllVolumes(itemID));
Pallet pallet = new Pallet();
while (!pl.EOF)
{
pl.UsedVolume = pallet.CalculateCurrentVolume(pl.PalletID);
pl.Save();
pl.MoveNext();
}
}
