/// <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 ICollection<ApprovedDetail> GetApprovedDetail(int orderId)
{
ICollection<ApprovedDetail> stockInformations = new Collection<ApprovedDetail>();
var balance = new Balance();
var approvedInformationTable = balance.GetApprovedByOrderID(orderId);
foreach (DataRowView approvedInformationRow in approvedInformationTable)
{
var itemID = Convert.ToInt32(approvedInformationRow["ItemID"]);
var unitID = Convert.ToInt32(approvedInformationRow["UnitID"]);
var stockInformation = new ApprovedDetail
{
Item = _itemRepository.FindSingle(Convert.ToInt32(itemID)),
Unit = _unitOfIssueRepository.FindSingle(Convert.ToInt32(unitID)),
Manufacturer = DBNull.Value != approvedInformationRow["ManufacturerId"] ? _manufacturerRepository.FindSingle(Convert.ToInt32(approvedInformationRow["ManufacturerId"])) : null,
ActivityGroup = _activityRepository.FindSingle(Convert.ToInt32(approvedInformationRow["ActivityID"]), Convert.ToBoolean(approvedInformationRow["IsDeliveryNote"])),
ExpiryDate = DBNull.Value != approvedInformationRow["ExpiryDate"] ? Convert.ToDateTime(approvedInformationRow["ExpiryDate"]) : (DateTime?)null,
physicalStore = DBNull.Value != approvedInformationRow["PhysicalStoreID"] ? _physicalStoreRepository.FindSingle(Convert.ToInt32(approvedInformationRow["PhysicalStoreID"])) : null,
Quantity = Convert.ToDecimal(approvedInformationRow["ApprovedQuantity"])
};
stockInformations.Add(stockInformation);
}
return stockInformations;
}
private void lookUpEdit1_EditValueChanged(object sender, EventArgs e)
{
if(lkMode.EditValue !=null)
{
Balance balance = new Balance();
balance.GetSOHByMode(Convert.ToInt32(lkMode.EditValue));
gridSOH.DataSource = balance.GetSOHByMode(Convert.ToInt32(lkMode.EditValue));
}
}
public void PopulateItemList(DataTable dtItem)
{
progressBar1.Visible = true;
progressBar1.Minimum = 1;
progressBar1.Value = 1;
progressBar1.Maximum = dtItem.Rows.Count;
GeneralInfo pipline = new GeneralInfo();
pipline.LoadAll();
int min = pipline.Min;
int max = pipline.Max;
int storeId = (cboStores.SelectedValue != null)?Convert.ToInt32(cboStores.SelectedValue):1;
lstItem.Items.Clear();
int col = 0;
int count = 1;
Balance bal = new Balance();
Items itmIssues = new Items();
int year = Convert.ToInt32(cboYear.SelectedItem);
foreach (DataRow dr in dtItem.Rows)
{
int itemId = Convert.ToInt32(dr["ID"]);
string itemName = dr["ItemName"].ToString() + " - " + dr["DosageForm"].ToString() + " - " + dr["Strength"].ToString();
int yer = (dtCurrent.Month < 11) ? year : year - 1;// (dtCurrent.Month < 11) ? Convert.ToInt32(cboYear.SelectedItem) : Convert.ToInt32(cboYear.SelectedItem) - 1;
Int64 SOH = bal.GetSOH(Convert.ToInt32(dr["ID"]), storeId, dtCurrent.Month, yer);
int[] mon = {11,12,1,2,3,4,5,6,7,8,9,10};
long[] cons = new long[12];
for (int i = 0; i < mon.Length;i++ )
{
int yr = (mon[i] < 11) ? year : year - 1;
cons[i] = itmIssues.GetQuantityIssuedByItemPerMonth(mon[i], itemId, storeId, yr);
}
string[] str = { count.ToString(), itemName, ((SOH != 0) ? SOH.ToString("#,###") : "0"), ((cons[0] != 0) ? cons[0].ToString("#,###") : "0"), ((cons[1] != 0) ? cons[1].ToString("#,###") : "0"), ((cons[2] != 0) ? cons[2].ToString("#,###") : "0"), ((cons[3] != 0) ? cons[3].ToString("#,###") : "0"), ((cons[4] != 0) ? cons[4].ToString("#,###") : "0"), ((cons[5] != 0) ? cons[5].ToString("#,###") : "0"), ((cons[6] != 0) ? cons[6].ToString("#,###") : "0"), ((cons[7] != 0) ? cons[7].ToString("#,###") : "0"), ((cons[8] != 0) ? cons[8].ToString("#,###") : "0"), ((cons[9] != 0) ? cons[9].ToString("#,###") : "0"), ((cons[10] != 0) ? cons[10].ToString("#,###") : "0"), ((cons[11] != 0) ? cons[11].ToString("#,###") : "0") };
ListViewItem listItem = new ListViewItem(str);
listItem.ToolTipText = itemName;
listItem.Tag = dr["ID"];
if (col != 0)
{
listItem.BackColor = Color.FromArgb(233, 247, 248);
col = 0;
}
else
{
col++;
}
lstItem.Items.Add(listItem);
count++;
progressBar1.PerformStep();
}
progressBar1.Visible = false;
}
public static StockCardReport CreateStockCard(int activityID, int itemID, int unitID, int warehouse, int manufactuererID, DateTime? startDate = null, DateTime? endDate = null)
{
BLL.Balance balance = new Balance();
BLL.Item item = new Item();
item.LoadByPrimaryKey(itemID);
ItemUnit iunit = new ItemUnit();
iunit.LoadByPrimaryKey(unitID);
BLL.Warehouse physicalStore = new Warehouse();
physicalStore.LoadByPrimaryKey(warehouse);
var dataView = new DataView();
if(startDate == null || endDate == null)
{
dataView = Balance.GetStockCardByWarehouse(activityID, itemID, unitID, EthiopianDate.EthiopianDate.Now.FiscalYear,
warehouse, manufactuererID);
}
else
{
dataView = Balance.GetStockCardByDate(activityID, itemID, unitID, warehouse, manufactuererID,
startDate.Value, endDate.Value);
}
StockCardReport report = new StockCardReport();
report.DataSource = dataView;
// Bind the headers here.
report.StockCode.Text = item.StockCode;
report.Description.Text = item.FullItemName;
report.Unit.Text = iunit.Text;
Activity store = new Activity();
store.LoadByPrimaryKey(activityID);
report.Account.Text = store.AccountName;
report.SubAccount.Text = store.SubAccountName;
report.Activity.Text = store.Name;
Manufacturer manufacturers = new Manufacturer();
manufacturers.LoadByPrimaryKey(manufactuererID);
report.Manufacturer.Text = manufacturers.Name;
// TODO: add the Item Source Here (the suggested interpretation is that item source is supplier)
report.PrintedBy.Text = string.Format("Generated By: {0} On {1}", CurrentContext.LoggedInUserName,
BLL.DateTimeHelper.ServerDateTime.ToString("dd-MM-yyyy hh:mm tt"));
report.Store.Text = physicalStore.Name;
report.AppVersion.Text = "HCMIS";
return report;
}
private void bw_DoWork(object sender, DoWorkEventArgs e)
{
var bal = new Balance();
var dtBal = new DataTable();
int[] arr = (int[])e.Argument;
int storeId = arr[0], month = arr[1], year = arr[2], programID = arr[3], commodityTypeID = arr[4];
switch (VisibilitySetting.HandleUnits)
{
case 1:
if (filter == "Stock Out" && ckExclude.Checked)
{
dtBal = bal.BalanceOfAllItemsForStockStatus(storeId, year, month, SelectedType, programID, commodityTypeID, dtCur, bw);
}
else if (filter == "Stock Out" && !ckExclude.Checked)
{
dtBal = bal.BalanceOfAllItemsForStockStatus(storeId, year, month, SelectedType, programID, commodityTypeID, dtCur, bw);
}
else if (filter == "Over Stocked" && ckExclude.Checked)
{
dtBal = bal.BalanceOfAllItemsForStockStatus(storeId, year, month, SelectedType, programID, commodityTypeID, dtCur, bw);
}
else if (filter == "Over Stocked" && !ckExclude.Checked)
{
dtBal = bal.BalanceOfAllItemsForStockStatus(storeId, year, month, SelectedType, programID, commodityTypeID, dtCur, bw);
}
e.Result = dtBal;
break;
case 2:
dtBal = (DataTable)bal.BalanceOfAllItemsUsingUnit(storeId, year, month, SelectedType, programID, commodityTypeID, dtCur, bw);
e.Result = dtBal;
break;
default:
dtBal = (DataTable)bal.BalanceOfAllItemsUsingUnit(storeId, year, month, SelectedType, programID, commodityTypeID, dtCur, bw);
e.Result = dtBal;
break;
}
// e.Result = dtBal;
}
private void OrderDetailForm_Load(object sender, EventArgs e)
{
Item item = new Item();
item.LoadByPrimaryKey(_itemID);
txtItemName.Text = item.FullItemName;
ItemUnit iu = new ItemUnit();
iu.LoadByPrimaryKey(_unitID);
txtUnit.Text = iu.Text;
BLL.Balance balance = new Balance();
gridApprovedPrinted.DataSource = balance.GetApprovedValueForFacility(CurrentContext.UserId, item.ID, iu.ID);
gridPicklistPrinted.DataSource = balance.GetPicklistedValueForFacility(CurrentContext.UserId, item.ID, iu.ID);
var activities = new Activity();
activities.LoadByUserID(CurrentContext.UserId);
DataTable dtbl = null;
while (!activities.EOF)
{
DataTable dt = balance.GetSOHForAnItem(activities.ID, item.ID, iu.ID);
if (dt != null && dt.Rows.Count > 0)
{
dt.Columns.Add("Account");
dt.Rows[0]["Account"] = activities.FullActivityName;
if (dtbl == null && Convert.ToInt32(dt.Rows[0]["SOH"]) > 0)
{
dtbl = dt;
}
else if (Convert.ToInt32(dt.Rows[0]["SOH"]) > 0)
{
dtbl.ImportRow(dt.Rows[0]);
}
}
activities.MoveNext();
}
gridItemStockStatus.DataSource = dtbl;
this.Text = string.Format("({0} - {1}) Details of : {2}", _itemID, _unitID, item.FullItemName);
}
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 ICollection<StockInformation> GetStockInformationByOrderID(int orderId)
{
var balance = new Balance();
var stockInformationTable = balance.GetBalanceByOrder(orderId, CurrentContext.UserId);
ICollection<StockInformation> stockInformations = new Collection<StockInformation>();
foreach (DataRowView stockInformationRow in stockInformationTable)
{
var itemID = Convert.ToInt32(stockInformationRow["ItemID"]);
var unitID = Convert.ToInt32(stockInformationRow["UnitID"]);
var stockInformation = new StockInformation
{
Item = _itemRepository.FindSingle(Convert.ToInt32(itemID)),
Unit = _unitOfIssueRepository.FindSingle(Convert.ToInt32(unitID)),
Manufacturer =
DBNull.Value != stockInformationRow["ManufacturerId"]
? _manufacturerRepository.FindSingle(
Convert.ToInt32(stockInformationRow["ManufacturerId"]))
: null,
Activity =
_activityRepository.FindSingle(
Convert.ToInt32(stockInformationRow["ActivityID"]),
Convert.ToBoolean(stockInformationRow["DeliveryNote"])),
ExpiryDate =
DBNull.Value != stockInformationRow["ExpiryDate"]
? Convert.ToDateTime(stockInformationRow["ExpiryDate"])
: (DateTime?) null,
PhysicalStore =
DBNull.Value != stockInformationRow["PhysicalStoreID"]
? _physicalStoreRepository.FindSingle(
Convert.ToInt32(stockInformationRow["PhysicalStoreID"]))
: null,
Quantity = Convert.ToDecimal(stockInformationRow["Usable"])
};
stockInformations.Add(stockInformation);
}
return stockInformations;
}
public static Reports.BinCardReport CreateBinCard(int activityID, int itemID, int unitID, int warehouse,DateTime? startDate=null,DateTime? endDate =null)
{
BLL.Balance balance = new Balance();
BLL.Item item = new Item();
item.LoadByPrimaryKey(itemID);
ItemUnit iunit = new ItemUnit();
iunit.LoadByPrimaryKey(unitID);
Activity activity = new Activity();
activity.LoadByPrimaryKey(activityID);
var dataView = new DataView();
if (startDate == null || endDate == null)
{
dataView = Balance.GetBinCardByWarehouse(activityID, itemID, unitID, EthiopianDate.EthiopianDate.Now.FiscalYear,
warehouse);
}
else
{
dataView = Balance.GetBinCardByDate(activityID, itemID, unitID, warehouse,startDate.Value,endDate.Value);
}
BinCardReport report = new BinCardReport();
report.DataSource = dataView;
report.StockCode.Text = item.StockCode;
report.Description.Text = item.FullItemName;
report.Unit.Text = iunit.Text;
report.ItemSource.Text = activity.FullActivityName;
// TODO: add the Item Source Here (the suggested interpretation is that item source is supplier)
report.PrintedBy.Text = string.Format("Generated By: {0} On {1}", CurrentContext.LoggedInUserName,
BLL.DateTimeHelper.ServerDateTime.ToString("dd-MM-yyyy hh:mm tt"));
report.AppVersion.Text = "HCMIS";
return report;
}
/// <summary>
/// Gets the balance of all items
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void bw_DoWork(object sender, DoWorkEventArgs e)
{
var bal = new Balance();
int[] arr = (int[])e.Argument;
int storeId = arr[0], month = arr[1]; // year = arr[2], quarter = arr[3];
dtFrom.CustomFormat = "MM/dd/yyyy";
DateTime dt1 = ConvertDate.DateConverter(dtFrom.Text);
dtTo.CustomFormat = "MM/dd/yyyy";
DateTime dt2 = ConvertDate.DateConverter(dtTo.Text);
//string dRange = "From " + dtFrom.Text + " to " + dtTo.Text;
//layoutControlGroup3.Text = "Cost Report " + dRange;
if (dt1 == dt2)
{
dt1 = ((dt1.Month == 11 || dt1.Month == 12)? new DateTime(dt1.Year, 11,1) : new DateTime(dt1.Year -1,11,1));
//dRange = "For Year " + dt1.Year.ToString();
}
var dtBal = bal.TransactionReport(storeId, dt1,dt2, _selectedType, bw);
e.Result = dtBal;
//txtFromDate.Text = dt1.ToShortDateString();
}
/// <summary>
/// Computes the stock calculations for an order detail.
/// </summary>
/// <param name="currentMonth">The current month.</param>
/// <param name="currentYear">The current year.</param>
/// <param name="userID">The user ID.</param>
/// <param name="orderDetail">The order detail.</param>
/// <returns></returns>
public DataRow ComputeStockCalculationsForAnOrderDetail(int currentMonth, int currentYear, int userID, OrderDetail orderDetail)
{
if (!IsOrderDetailTableReady(orderDetail))
{
PrepareOrderDetailTable(orderDetail);
}
int? preferredManufacturer;
int? preferredPhysicalStoreID;
decimal usableStock;
decimal approved;
decimal availableQuantity;
Balance bal = new Balance();
ItemManufacturer imf = new ItemManufacturer();
int? unitid = null;
PriceSettings priceSettings = BLL.Settings.HandleDeliveryNotes ? PriceSettings.BOTH : PriceSettings.PRICED_ONLY;
BLL.Order parentOrder = new Order();
parentOrder.LoadByPrimaryKey(orderDetail.OrderID);
unitid = orderDetail.UnitID;
preferredManufacturer = orderDetail.IsColumnNull("PreferredManufacturerID") ? null : new int?(orderDetail.PreferredManufacturerID);
preferredPhysicalStoreID = orderDetail.IsColumnNull("PreferredPhysicalStoreID") ? null : new int?(orderDetail.PreferredPhysicalStoreID);
if (orderDetail.IsColumnNull("StoreID"))
{
orderDetail.StoreID = BLL.Activity.GetActivityUsingFEFO(this.FromStore, orderDetail.ItemID, orderDetail.UnitID);
orderDetail.Save();
}
Activity storeObject = new Activity();
availableQuantity = storeObject.LoadOptionsForOrderDetail(userID, orderDetail.ID, priceSettings, bal, false, out usableStock, out approved);
orderDetail.SetColumn("AvailableStores", storeObject.DefaultView);
if (storeObject.RowCount == 1)
{
orderDetail.StoreID = storeObject.ID;
// Avoid error if the column IsDeliveryNote doesn't exsit at all.
orderDetail.DeliveryNote = storeObject.DefaultView.Table.Columns.IndexOf("IsDeliveryNote") >= 0 &&
!storeObject.IsColumnNull("IsDeliveryNote") &&
Convert.ToBoolean(storeObject.GetColumn("IsDeliveryNote"));
availableQuantity = Convert.ToDecimal(storeObject.GetColumn("AvailableSKU"));
}
else if (storeObject.RowCount > 1)
{
//TOCLEAN: Lord have mercy.
//
// check if the default activity is selected
// if it has been selected, then do a good job and select it.
storeObject.Rewind();
while (
!storeObject.EOF
&&
(
(
storeObject.ID == orderDetail.StoreID
&& !orderDetail.IsColumnNull("DeliveryNote")
&& orderDetail.DeliveryNote
&& !Convert.ToBoolean(storeObject.GetColumn("IsDeliveryNote"))
)
||
storeObject.ID != orderDetail.StoreID
)
)
{
storeObject.MoveNext();
}
// the selected store is found, don't worry.
if (!storeObject.EOF)
{
availableQuantity = Convert.ToDecimal(storeObject.GetColumn("AvailableSKU"));
}
else
{
// the selected store is not found, please do select the first store.
storeObject.Rewind();
orderDetail.StoreID = storeObject.ID;
orderDetail.DeliveryNote = (storeObject.DefaultView.Table.Columns.IndexOf("IsDeliveryNote") >= 0 &&
!storeObject.IsColumnNull("IsDeliveryNote") &&
Convert.ToBoolean(storeObject.GetColumn("IsDeliveryNote")));
availableQuantity = Convert.ToDecimal(storeObject.GetColumn("AvailableSKU"));
}
}
orderDetail.SetColumn("HasStores", (storeObject.RowCount > 1) ? "*" : "");
// Precaution ... to hide -ve available quantity.
if (availableQuantity < 0)
{
availableQuantity = 0;
}
orderDetail.StockedOut = availableQuantity <= 0;
orderDetail.Save();
int qinBu = 1;
if (unitid.HasValue)
{
ItemUnit itemUnit = new ItemUnit();
itemUnit.LoadByPrimaryKey(unitid.Value);
qinBu = itemUnit.QtyPerUnit;
//Checking if the columns from the vwGetAllItems have been filled in.
var fullItemName = orderDetail.GetColumn("FullItemName").ToString();
if (string.IsNullOrEmpty(fullItemName))
{
BLL.Order temp = new Order();
temp.LoadFromRawSql(HCMIS.Repository.Queries.Order.SelectItemDetail(orderDetail.ItemID));
orderDetail.SetColumn("Unit", itemUnit.Text);
orderDetail.SetColumn("FullItemName", temp.GetColumn("FullItemName"));
orderDetail.SetColumn("StockCode", temp.GetColumn("StockCode"));
orderDetail.SetColumn("CategoryType", temp.GetColumn("CategoryType"));
}
}
orderDetail.SetColumn("AvailableQuantity", availableQuantity);
orderDetail.SetColumn("PricedQuantity", usableStock);
if (orderDetail.IsColumnNull("ApprovedQuantity"))
{
if ((orderDetail.Quantity / ((long)qinBu)) < availableQuantity)
{
orderDetail.ApprovedQuantity = orderDetail.Quantity;
}
else
{
orderDetail.ApprovedQuantity = availableQuantity * qinBu;
}
}
if (BLL.Settings.AllowPreferredManufacturers)
{
Manufacturer manuf = new Manufacturer();
manuf.LoadForItem(orderDetail.ItemID, orderDetail.StoreID, orderDetail.UnitID, true);
manuf.AddNew();
manuf.ID = -1;
manuf.Name = "Remove Preference";
orderDetail.SetColumn("AvailableManufacturer", manuf.DefaultView);
orderDetail.SetColumn("HasManufacturers", (manuf.RowCount > 2) ? "*" : "");
if (manuf.RowCount == 2)
{
manuf.Rewind();
orderDetail.PreferredManufacturerID = manuf.ID;
}
}
if (BLL.Settings.AllowPreferredPhysicalStore)
{
PhysicalStore phyStore = new PhysicalStore();
phyStore.LoadForItem(userID, orderDetail.ItemID, orderDetail.StoreID, orderDetail.UnitID);
phyStore.AddNew();
phyStore.Name = "Remove Preference";
phyStore.ID = -1;
orderDetail.SetColumn("AvailablePhysicalStore", phyStore.DefaultView);
orderDetail.SetColumn("HasPhysicalStoreChoice", (phyStore.RowCount > 2) ? "*" : "");
if (phyStore.RowCount == 2)
{
phyStore.Rewind();
orderDetail.PreferredPhysicalStoreID = phyStore.ID;
}
}
if (BLL.Settings.AllowPreferredExpiry)
{
ReceiveDoc rd = new ReceiveDoc();
rd.LoadExpiryDatesForItem(orderDetail.ItemID, orderDetail.StoreID, orderDetail.UnitID, true, preferredManufacturer, preferredPhysicalStoreID);
rd.AddNew();
rd.SetColumn("ExpiryDateString", "Remove Preference");
orderDetail.SetColumn("AvailableExpiry", rd.DefaultView);
orderDetail.SetColumn("HasExpiryChoice", (rd.RowCount > 2) ? "*" : "");
if (!orderDetail.IsColumnNull("PreferredExpiryDate"))
{
DateTime expDate = orderDetail.PreferredExpiryDate;
string expDateStr = string.Format("{0}-{1:00}-{2:00}", expDate.Year, expDate.Month, expDate.Day, "");
orderDetail.SetColumn("ExpiryDateString", expDateStr);
}
}
// do some reseting if the approved quanitty is greater than
if (orderDetail.ApprovedQuantity / qinBu > availableQuantity)
{
orderDetail.ApprovedQuantity = availableQuantity * qinBu;
}
orderDetail.SetColumn("UsableStock", usableStock);
orderDetail.SetColumn("PApprovedStock", approved);
orderDetail.SetColumn("SKUBU", qinBu);
orderDetail.SetColumn("AvailableSKU", availableQuantity);
string TextID = ((orderDetail.IsColumnNull("DeliveryNote") || !orderDetail.DeliveryNote)
? "N"
: "D") + orderDetail.StoreID.ToString();
orderDetail.SetColumn("TextID", TextID);
orderDetail.SetColumn("ApprovedSKU", orderDetail.ApprovedQuantity / Convert.ToDecimal(qinBu));
orderDetail.SetColumn("RequestedSKU", orderDetail.Quantity / Convert.ToDecimal(qinBu));
if (availableQuantity == 0)
{
orderDetail.SetColumnNull("TextID");
orderDetail.SetColumnNull("StoreID");
}
Item itm = new Item();
string warning = (itm.GetItemAllowStatus(orderDetail.ItemID, this.RequestedBy) == 0) ? "Warning" : "";
orderDetail.SetColumn("Warning", warning);
//if (!orderDetail.IsColumnNull("StoreID"))
//{
// var balance = new Balance();
//balance.LoadQuantityNotReceive(orderDetail.ItemID, orderDetail.UnitID, parentOrder.FromStore);
//var totalrequested =balance.GetTotalApprovedQuantityByItem(parentOrder.ID, orderDetail.ItemID, orderDetail.UnitID,parentOrder.FromStore);
orderDetail.SetColumn("GIT", 0);
orderDetail.SetColumn("CRequested",0);
orderDetail.SetColumn("CApproved",0);
//orderDetail.SetColumn("DOS", balance.DOS);
//orderDetail.SetColumn("TotalIssued", balance.TotalIssued);
//orderDetail.SetColumn("FiscalYearDays", balance.FiscalYearDays);
//decimal amc = 0;
//decimal mos = 0;
//var totalissued = balance.TotalIssued;
//var totaldatediff = balance.FiscalYearDays - balance.DOS;
//if (totalissued != 0)
//{
// amc = Convert.ToDecimal(totalissued / totaldatediff) * 30;
//}
//else if (amc == 0)
//{
// mos = Convert.ToDecimal(balance.FiscalYearDays / 30.0);
//}
//else if (amc != 0 && availableQuantity != 0)
//{
// mos = Convert.ToDecimal(availableQuantity / amc);
//}
//else if (availableQuantity == 0 && amc != 0)
//{
// mos = 0;
//}
//else
//{
// amc = 0;
// mos = 0;
//}
orderDetail.SetColumn("TotalRequested", 0);
orderDetail.SetColumn("AMC", 0);
orderDetail.SetColumn("MOS", 0);
//}
return orderDetail.DefaultView.ToTable().Rows[0];
}
public static void MakeStockCalculations(int userID, int currentMonth, int currentYear, PriceSettings unpricedOrPricedOrBoth, OrderDetail ordDetail, int storeID, int itemID, Order order, Balance bal, int? unitid, int? preferredManufacturer, DateTime? preferedExpiry, int? preferredPhysicalStoreID, out decimal usableStock, out decimal approved, out decimal availableQuantity, bool markStockoutBit)
{
bal.GetItemsAvailableForApproval(unpricedOrPricedOrBoth, currentMonth, currentYear, storeID, itemID, unitid, preferedExpiry, preferredManufacturer, preferredPhysicalStoreID, userID, out usableStock, out approved, out availableQuantity);
}
public void GenerateExpiryChart()
{
DateTime selectedStartedDate = EthiopianDate.EthiopianDate.EthiopianToGregorian(String.Format("{0}/{1}/{2}", 1, 1, (int)cboYear.EditValue));
DateTime selectedEndDate = EthiopianDate.EthiopianDate.EthiopianToGregorian(String.Format("{0}/{1}/{2}", 30, 12, (int)cboYear.EditValue));
dtFrom.Value = selectedStartedDate;
dtTo.Value = selectedEndDate;
// Generate the pie Chart for the Current SOH and EXpired Drugs
dtFrom.CustomFormat = "MM/dd/yyyy";
DateTime dt1 = ConvertDate.DateConverter(dtFrom.Text);
dtTo.CustomFormat = "MM/dd/yyyy";
DateTime dt2 = ConvertDate.DateConverter(dtTo.Text);
//string dRange = "From " + dtFrom.Text + " to " + dtTo.Text;
//layoutControlGroup3.Text = "Cost Report " + dRange;
if (dt1.Year == dt2.Year)
{
dt1 = ((dt1.Month == 11 || dt1.Month == 12) ? new DateTime(dt1.Year, 11, 1) : new DateTime(dt1.Year - 1, 11, 1));
//dRange = "For Year " + dt1.Year.ToString();
}
ReceiveDoc rec = new ReceiveDoc();
Balance bal = new Balance();
chartPie.Series.Clear();
lstExpStatus.Items.Clear();
Items itm = new Items();
int storeId = Convert.ToInt32(cboStores.EditValue);
int typeID = Convert.ToInt32(lkCategory.EditValue);
//object[] objExp = itm.CountExpiredItemsAndAmount(storeId);
object[] objExp = itm.CountExpiredItemsAndAmountByCategory(storeId,typeID ,dt1 ,dt2);
Int64 expAmount = Convert.ToInt64(objExp[0]);
Double expCost = Convert.ToDouble(objExp[1]);
// object[] nearObj = itm.CountNearlyExpiredQtyAmount(storeId);
object[] nearObj = itm.CountNearlyExpiredQtyAmountByCategory(storeId , typeID ,dt1 ,dt2);
Int64 nearExpAmount = Convert.ToInt64(nearObj[0]);
double nearExpCost = Convert.ToDouble(nearObj[1]);
// object[] sohObj = itm.GetAllSOHQtyAmount(storeId);
object[] sohObj = itm.GetAllSOHQtyAmountByCategory(storeId ,typeID ,dt1 ,dt2);
Int64 soh = Convert.ToInt64(sohObj[0]);
double sohPrice = Convert.ToDouble(sohObj[1]);
Int64 normal = (soh - nearExpAmount - expAmount);
Int64 nearExpiry = nearExpAmount;
Int64 expired = expAmount;
object[] obj = { normal, nearExpiry, expired };
DataTable dtSOHList = new DataTable();
dtSOHList.Columns.Add("Type");
dtSOHList.Columns.Add("Value");
dtSOHList.Columns[1].DataType = typeof(Int64);
double normalPrice = (sohPrice - nearExpCost - expCost);
Int64 totItm = normal + nearExpiry + expired;
object[] oo = { "Normal : " + normalPrice.ToString("C"), obj[0] };
dtSOHList.Rows.Add(oo);
object[] oo3 = { "Expired : " + expCost.ToString("C"), obj[2] };
dtSOHList.Rows.Add(oo3);
object[] oo2 = { "Near Expiry : " + nearExpCost.ToString("C"), obj[1] };
dtSOHList.Rows.Add(oo2);
decimal per = Convert.ToDecimal(normal) / Convert.ToDecimal(totItm) * 100;
per = Decimal.Round(per, 0);
string[] str = { "Normal", per.ToString() + "%", obj[0].ToString(), normalPrice.ToString("C") };
ListViewItem lstItmNor = new ListViewItem(str);
lstExpStatus.Items.Add(lstItmNor);
per = Convert.ToDecimal(nearExpiry) / Convert.ToDecimal(totItm) * 100;
per = Decimal.Round(per, 0);
string[] str1 = { "Near Expiry", per.ToString() + "%", obj[1].ToString(), nearExpCost.ToString("C") };
ListViewItem lstItmNor1 = new ListViewItem(str1);
lstExpStatus.Items.Add(lstItmNor1);
per = Convert.ToDecimal(expired) / Convert.ToDecimal(totItm) * 100;
per = Decimal.Round(per, 0);
string[] str2 = { "Expired", per.ToString() + "%", obj[2].ToString(), expCost.ToString("C") };
ListViewItem lstItmNor2 = new ListViewItem(str2);
lstExpStatus.Items.Add(lstItmNor2);
Series serExpired = new Series("pie", ViewType.Pie3D);
serExpired.DataSource = dtSOHList;
serExpired.ArgumentScaleType = ScaleType.Qualitative;
serExpired.ArgumentDataMember = "Type";
serExpired.ValueScaleType = ScaleType.Numerical;
serExpired.ValueDataMembers.AddRange(new string[] { "Value" });
serExpired.PointOptions.PointView = PointView.ArgumentAndValues;
serExpired.LegendText = "Key";
serExpired.PointOptions.ValueNumericOptions.Format = NumericFormat.Percent;
serExpired.PointOptions.ValueNumericOptions.Precision = 0;
((PieSeriesLabel)serExpired.Label).Position = PieSeriesLabelPosition.TwoColumns;
// ((PieSeriesLabel)serExpired.Label).ColumnIndent = 2;
((PiePointOptions)serExpired.PointOptions).PointView = PointView.ArgumentAndValues;
//((PiePointOptions)serExpired.PointOptions).Separator = " , ";
chartPie.Series.Add(serExpired);
chartPie.Size = new System.Drawing.Size(1000, 500);
}
private void BuildStoreInventoryList(int year, int storeId, DataTable dtItm)
{
string[] str = { "Item Name", "Batch No.", "Remark" };
foreach (string co in str)
{
dtBB.Columns.Add(co);
}
str = new string[] { "ItemId", "No.", "Beginning Balance", "Ending Balance(SOH)", "Physical Inventory", "ID", "RecID"};//, "Change Since Sene 30" };
foreach (string co in str)
{
dtBB.Columns.Add(co, typeof(int));
}
int count = 1;
var yProcess = new YearEnd();
var bal = new Balance();
dtDate.Value = DateTime.Now;
DateTime dtCurent = new DateTime();
dtDate.CustomFormat = "MM/dd/yyyy";
dtCurent = ConvertDate.DateConverter(dtDate.Text);
var recDoc = new ReceiveDoc();
int month = dtCurent.Month;
//btnSave.Enabled = true;
//CALENDAR:
//if ((dtCurent.Month == 10 && dtCurent.Day == 30) || dtCurent.Month == 11)
//{
// //btnSave.Enabled = ((!yProcess.IsInventoryComplete(year, storeId)));
// btnSave.Enabled =true;
// month = 10;
//}
//else
// btnSave.Enabled = false;
var yEnd = new YearEnd();
foreach (DataRow dr in dtItm.Rows)//For each item
{
string itemName = dr["ItemName"].ToString() + " - " + dr["DosageForm"].ToString() + " - " + dr["Strength"].ToString();
int itemID = Convert.ToInt32(dr["ID"]);
bool BalanceExists = (yProcess.DoesBalanceExist(year, storeId, itemID, false));
//We don't want to display those items whose inventory has already been done.
if (BalanceExists)
continue;
//bereket
//Int64 soh = bal.GetSOH(Convert.ToInt32(dr["ID"]), storeId, month, year);
Int64 soh = bal.GetSOHOptimized(Convert.ToInt32(dr["ID"]), storeId, month, year);
Int64 bbal = yEnd.GetBBalance(year, storeId, Convert.ToInt32(dr["ID"]), month);
yProcess.GetBalanceByItemId(year, storeId, Convert.ToInt32(dr["ID"]));
Int64 BB = (yProcess.RowCount > 0) ? yProcess.BBalance : bbal;
Int64 EB = ((yProcess.RowCount > 0 && yProcess.EBalance != 0) ? yProcess.EBalance : soh);
//Now if the physical inventory is chosen to be default value, we set it to the ending balance of last year.
string Phy = (yProcess.RowCount > 0) ? yProcess.PhysicalInventory.ToString() : (_defaultValueToPhysicalInventory ? EB.ToString() : "");
int id = (yProcess.RowCount > 0) ? yProcess.ID : 0;
string remark = (yProcess.RowCount > 0) ? yProcess.Remark : "";
//object[] obj = {Convert.ToInt32(dr["ID"]),count,itemName,"",BB,((EB != 0)?EB.ToString("#,###"):"0"),Phy,remark,id,-1};
//dtBB.Rows.Add(obj);
DataRowView drv = dtBB.DefaultView.AddNew();
if (yProcess.RowCount > 0)
{
drv["ID"] = yProcess.ID;
}
drv["ItemId"] = dr["ID"];
drv["No."] = count;
drv["Item Name"] = itemName;
drv["Beginning Balance"] = BB;
drv["Ending Balance(SOH)"] = EB;
if (Phy != "")
{
drv["Physical Inventory"] = Phy;
}
drv["RecID"] = -1;
drv["Remark"] = remark;
var ethioDate = new EthiopianDate.EthiopianDate(year, 1, 1);
//drv["Change Since Sene 30"] = BLL.Balance.GetChangeAfterDate(itemID, storeId, ethioDate.EndOfFiscalYear.ToGregorianDate());
count++;
//if (!BalanceExists)
//{
int theLastBalance = 0;
DataTable dtBatchs = recDoc.GetBatchWithValue(storeId, Convert.ToInt32(dr["ID"]), dtCurent);
foreach (DataRow drBatch in dtBatchs.Rows) //For each batch
{
if (drBatch["QuantityLeft"] != DBNull.Value && Convert.ToInt64(drBatch["QuantityLeft"]) != 0)
{
drv = dtBB.DefaultView.AddNew();
drv["Item Name"] = ">>";
drv["Batch No."] = drBatch["BatchNo"];
drv["Ending Balance(SOH)"] = Convert.ToInt64(drBatch["QuantityLeft"]);
//Now if the physical inventory is chosen to be default value, we set it to the ending balance of last year.
if (_defaultValueToPhysicalInventory)
drv["Physical Inventory"] = drBatch["QuantityLeft"].ToString();
theLastBalance += Convert.ToInt32(drBatch["QuantityLeft"]);
drv["RecID"] = drBatch["ID"];
}
}
//}
}
grdYearEnd.DataSource = dtBB;
dtDate.CustomFormat = "MMMM dd, yyyy";
}
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;
}
public DataTable GetRRFReportWithOutUnit(int storeId, int fromYear, int fromMonth, int toYear, int toMonth)
{
var balance = new Balance();
var issue = new IssueDoc();
var startDate = EthiopianDate.EthiopianDate.EthiopianToGregorian(String.Format("{0}/{1}/{2}", 1, fromMonth, fromYear));
var endDate = EthiopianDate.EthiopianDate.EthiopianToGregorian(String.Format("{0}/{1}/{2}", 30, toMonth, toYear));
if (fromMonth != 1)
fromMonth--;
else
{
fromYear--;
fromMonth = 12;
}
var dtbl = balance.GetSOHForProgramRRF(storeId, fromMonth, fromYear);
var dtbl2 = balance.GetSOHForProgramRRF(storeId, toMonth, toYear);
var dt1 = new DateTime(fromYear, fromMonth, DateTime.DaysInMonth(fromYear, fromMonth));
var dt2 = new DateTime(toYear, toMonth, DateTime.DaysInMonth(toYear, toMonth));
var query = string.Format("select distinct Items.ID, isnull(Quantity,0) as Quantity from" +
" Items left join (select ItemID, sum(Quantity) as Quantity from ReceiveDoc rd " +
"where [Date] between '{0}' and '{1}' and" + " StoreID = {2} group by ItemID) as" +
" A on Items.ID = A.ItemID", dt1, dt2, storeId);
//var query = string.Format("SELECT ItemID ID,SUM(Quantity) AS Quantity FROM ReceiveDoc rd WHERE [Date] BETWEEN '{0}' AND '{1}'AND StoreID = {2} GROUP BY ItemID ", dt1, dt2, storeId);
this.LoadFromRawSql(query);
var received = this.DataTable;
query = string.Format("select distinct Items.ID, isnull(Quantity,0) as Quantity " +
"from Items left join (select ItemID,sum(Quantity) Quantity " +
"from IssueDoc " +
"where [Date] between '{0}' and '{1}' and StoreID = {2} and IsTransfer = 0 " +
"group by ItemID ) as A on Items.ID = A.ItemID "
, dt1, dt2, storeId);
//query = string.Format(" SELECT ItemID ID ,SUM(Quantity) Quantity FROM IssueDoc WHERE [Date] BETWEEN '{0}' AND '{1}' AND StoreID = {2} AND IsTransfer = 0 GROUP BY ItemID", dt1, dt2, storeId);
this.LoadFromRawSql(query);
var issued = this.DataTable;
query = string.Format("select distinct Items.ID, isnull(Quantity,0) as Quantity from " +
"Items left join (select ItemID,sum(case when Losses = 1 then - Quantity else " +
"Quantity end) Quantity from Disposal where [Date] between '{0}' and '{1}' " +
"and StoreID = {2} group by ItemID) as A on Items.ID = A.ItemID "
, dt1, dt2, storeId);
//query = string.Format("select ItemID ID ,sum(case when Losses = 1 then - Quantity else Quantity end) Quantity from Disposal where [Date] between '{0}' and '{1}' and StoreID = {2} group by ItemID", dt1, dt2, storeId);
this.LoadFromRawSql(query);
var lost = this.DataTable;
query = string.Format("select distinct Items.ID,Items.StockCodeDACA,Items.Cost, case Items.Cost when 0 then 1 else isnull(Items.Cost,1) end as QtyPerPack from Items");
this.LoadFromRawSql(query);
var preferredPackSizetbl = DataTable;
var daysOutOfStock = this.GetItemsWithLastIssuedOrDisposedDate();
var x = (from y in dtbl.AsEnumerable()
join z in dtbl2.AsEnumerable()
on y["ID"] equals z["ID"]
join p in preferredPackSizetbl.AsEnumerable()
on y["ID"] equals p["ID"]
where Convert.ToInt32(y["EverReceived"]) == 1
select new
{
ID = y["ID"],
FullItemName = y["FullItemName"],
Unit = y["Unit"],
StockCode = y["StockCode"],
BeginingBalance = Convert.ToDouble(y["SOH"]),
ProgramID = y["ProgramID"],
SOH = Convert.ToDouble(z["SOH"]),
USOH = Convert.ToDouble(z["Dispatchable"]),
Max = Convert.ToInt32(z["Max"]),
QtyPerPack = Convert.ToDouble(p["QtyPerPack"]),
StockCodeDACA = p["StockCodeDACA"],
Status=y["Status"],
TypeID=y["TypeID"]
}).Distinct().ToArray();
var m = (from n in x
join z in received.AsEnumerable()
on n.ID equals z["ID"]
select new
{
ID = n.ID,
FullItemName = n.FullItemName,
Unit = n.Unit,
StockCode = n.StockCode,
BeginingBalance = n.BeginingBalance,
SOH = n.SOH,
USOH = n.USOH,
Max = n.Max,
QtyPerPack = n.QtyPerPack,
StockCodeDACA = n.StockCodeDACA,
ProgramID = n.ProgramID,
Received = z["Quantity"],
Status = n.Status,
TypeID = n.TypeID
}).ToArray();
var l = (from n in m
join z in issued.AsEnumerable()
on n.ID equals z["ID"]
select
new
{
ID = n.ID,
FullItemName = n.FullItemName,
Unit = n.Unit,
StockCode = n.StockCode,
BeginingBalance = n.BeginingBalance,
SOH = n.SOH,
USOH = n.USOH ,
Max = Convert.ToInt32(z["Quantity"]) * 2,
StockCodeDACA = n.StockCodeDACA,
QtyPerPack = n.QtyPerPack,
Received = n.Received,
ProgramID = n.ProgramID,
Status=n.Status,
Issued = Convert.ToInt32(z["Quantity"]),
TypeID=n.TypeID
}).ToArray();
var t = (from n in l
join z in lost.AsEnumerable()
on n.ID equals z["ID"]
select new
{
ID = n.ID,
FullItemName = n.FullItemName,
Unit = n.Unit,
StockCode = n.StockCode,
BeginingBalance = n.BeginingBalance,
SOH = n.SOH,
USOH = n.USOH ,
Max = n.Max,
StockCodeDACA = n.StockCodeDACA,
QtyPerPack = n.QtyPerPack,
Received = n.Received,
ProgramID = n.ProgramID,
Issued = n.Issued,
Status =n.Status,
LossAdj = z["Quantity"],
Quantity = (n.Max - n.SOH < 0) ? 0 : n.Max - n.SOH,
TypeID=n.TypeID
}).ToArray();
if (t.Length == 0)
{
var t2 = (from n in l
select
new
{
ID = n.ID,
FullItemName = n.FullItemName,
Unit = n.Unit,
StockCode = n.StockCode,
BeginingBalance = n.BeginingBalance,
SOH = n.SOH,
USOH = n.USOH,
Max = n.Max,
StockCodeDACA = n.StockCodeDACA,
QtyPerPack = n.QtyPerPack,
Received = n.Received,
Issued = n.Issued,
LossAdj = 0,
ProgramID = n.ProgramID,
Status = n.Status,
Quantity = (n.Max - n.SOH < 0) ? 0 : n.Max - n.SOH,
DaysOutOfStock =Builder.CalculateStockoutDays(Convert.ToInt32(n.ID), storeId, startDate, endDate),
//TODO: This is a quick fix. We need to take stock status from the last three months.
//TODO: This is a quick fix. We need to take stock status from the last three months.
// MaxStockQty =((120*n.Issued)/(60 -Convert.ToInt32(Builder.CalculateStockoutDays(Convert.ToInt32(n.ID), storeId, startDate,
// endDate)))),
TypeID = n.TypeID
}).ToArray();
var value = new DataTable();
value.Columns.Add("ID", typeof (int));
value.Columns.Add("FullItemName");
value.Columns.Add("Unit");
value.Columns.Add("StockCode");
value.Columns.Add("BeginingBalance", typeof (double));
value.Columns.Add("SOH", typeof (double));
value.Columns.Add("Max", typeof (double));
value.Columns.Add("StockCodeDACA", typeof (string));
value.Columns.Add("QtyPerPack", typeof (double));
value.Columns.Add("Issued", typeof (double));
value.Columns.Add("Received", typeof (double));
value.Columns.Add("LossAdj", typeof (double));
value.Columns.Add("Quantity", typeof (double));
value.Columns.Add("DaysOutOfStock", typeof (int));
//value.Columns.Add("MaxStockQty", typeof (double));
value.Columns.Add("ProgramID", typeof (int));
value.Columns.Add("Status", typeof (string));
value.Columns.Add("TypeID", typeof (int));
value.Columns.Add("LastDUSoh", typeof (decimal));
value.Columns.Add("USOH", typeof (decimal));
value.Columns.Add("TotalSOH", typeof (decimal));
foreach (var v in t2)
{
DataRowView drv = value.DefaultView.AddNew();
drv["ID"] = v.ID;
drv["FullItemName"] = v.FullItemName;
drv["Unit"] = v.Unit;
drv["StockCode"] = v.StockCode;
drv["BeginingBalance"] = v.BeginingBalance;
drv["SOH"] = v.SOH;
drv["USOH"] = v.USOH;
drv["Max"] = v.Max;
drv["StockCodeDACA"] = v.StockCodeDACA;
drv["QtyPerPack"] = v.QtyPerPack;
drv["Issued"] = v.Issued;
drv["Received"] = v.Received;
drv["LossAdj"] = v.LossAdj;
drv["Quantity"] = v.Quantity;
drv["ProgramID"] = v.ProgramID;
drv["DaysOutOfStock"] = v.DaysOutOfStock;
//drv["MaxStockQty"] = v.MaxStockQty;
drv["Status"] = v.Status;
drv["TypeID"] = v.TypeID;
drv["LastDUSoh"] = issue.GetDULastSOH1(Convert.ToInt32(v.ID), dt1, dt2);
drv["TotalSOH"] = v.USOH + issue.GetDULastSOH1(Convert.ToInt32(v.ID), dt1, dt2);
}
return value;
}
else
{
var t2 = (from n in t
select
new
{
ID = n.ID,
FullItemName = n.FullItemName,
Unit = n.Unit,
StockCode = n.StockCode,
BeginingBalance = n.BeginingBalance,
SOH = n.SOH,
USOH = n.USOH,
Max = n.Max,
StockCodeDACA = n.StockCodeDACA,
QtyPerPack = n.QtyPerPack,
Received = n.Received,
Issued = n.Issued,
LossAdj = n.LossAdj,
ProgramID = n.ProgramID,
Status = n.Status,
Quantity = (n.Max - n.SOH < 0) ? 0 : n.Max - n.SOH,
DaysOutOfStock =Builder.CalculateStockoutDays(Convert.ToInt32(n.ID), storeId, startDate, endDate),
//TODO: This is a quick fix. We need to take stock status from the last three months.
//TODO: This is a quick fix. We need to take stock status from the last three months.
//MaxStockQty =((120 * n.Issued) /(60 -Convert.ToInt32(Builder.CalculateStockoutDays(Convert.ToInt32(n.ID), storeId, startDate,endDate)))),
TypeID = n.TypeID
}).ToArray();
var value = new DataTable();
value.Columns.Add("ID", typeof(int));
value.Columns.Add("FullItemName");
value.Columns.Add("Unit");
value.Columns.Add("StockCode");
value.Columns.Add("BeginingBalance", typeof(double));
value.Columns.Add("SOH", typeof(double));
value.Columns.Add("Max", typeof(double));
value.Columns.Add("StockCodeDACA", typeof(string));
value.Columns.Add("QtyPerPack", typeof(double));
value.Columns.Add("Issued", typeof(double));
value.Columns.Add("Received", typeof(double));
value.Columns.Add("LossAdj", typeof(double));
value.Columns.Add("Quantity", typeof(double));
value.Columns.Add("DaysOutOfStock", typeof(int));
//value.Columns.Add("MaxStockQty", typeof(double));
value.Columns.Add("ProgramID", typeof(int));
value.Columns.Add("Status", typeof(string));
value.Columns.Add("TypeID", typeof(int));
value.Columns.Add("LastDUSoh", typeof(decimal));
value.Columns.Add("USOH", typeof(decimal));
value.Columns.Add("TotalSOH", typeof(decimal));
foreach (var v in t2)
{
DataRowView drv = value.DefaultView.AddNew();
drv["ID"] = v.ID;
drv["FullItemName"] = v.FullItemName;
drv["Unit"] = v.Unit;
drv["StockCode"] = v.StockCode;
drv["BeginingBalance"] = v.BeginingBalance;
drv["SOH"] = v.SOH;
drv["USOH"] = v.USOH;
drv["Max"] = v.Max;
drv["StockCodeDACA"] = v.StockCodeDACA;
drv["QtyPerPack"] = v.QtyPerPack;
drv["Issued"] = v.Issued;
drv["Received"] = v.Received;
drv["LossAdj"] = v.LossAdj;
drv["Quantity"] = v.Quantity;
drv["ProgramID"] = v.ProgramID;
drv["DaysOutOfStock"] = v.DaysOutOfStock;
//drv["MaxStockQty"] = v.MaxStockQty;
drv["Status"] = v.Status;
drv["TypeID"] = v.TypeID;
drv["LastDUSoh"] = issue.GetDULastSOH1(Convert.ToInt32(v.ID), dt1, dt2);
drv["TotalSOH"] = v.USOH + issue.GetDULastSOH1(Convert.ToInt32(v.ID), dt1, dt2);
}
return value;
}
}
public decimal GetMOS(int itemId, int storeId, DateTime dtCurrent)
{
GeneralInfo pipline = new GeneralInfo();
pipline.LoadAll();
int min = pipline.Min;
int max = pipline.Max;
double eop = pipline.EOP;
Items itmB = new Items();
Balance bal = new Balance();
//Int64 AMC= bal.CalculateAMC(itemId, storeId, dtCurrent.Month, dtCurrent.Year);
double AMC = Builder.CalculateAverageConsumption(itemId,storeId,DateTime.Today.Subtract(TimeSpan.FromDays(180)), dtCurrent, CalculationOptions.Monthly);
//bal.CalculateAMC(itemId, storeId, dtCurrent.Month, dtCurrent.Year);//dtBal.Rows.Count <= 0) ? 0 : ((dtBal.Rows[0]["AMC"].ToString() != "") ? Convert.ToInt64(dtBal.Rows[0]["AMC"]) : 0);
double MinCon = AMC * min;
double maxCon = AMC * max;
double eopCon = AMC * (eop + 0.25);
Int64 SOH = bal.GetSOH(itemId, storeId, dtCurrent.Month, dtCurrent.Year);
decimal MOS = (AMC != 0) ? (SOH / Convert.ToDecimal(AMC)) : 0;
MOS = Decimal.Round(MOS, 1);
return MOS;
}
/// <summary>
/// The code supports manufacturer preference here too but we choose not to prefer manufacturers when looking into the stores.
/// </summary>
/// <param name="userID"></param>
/// <param name="orderDetailID"></param>
/// <param name="pricedUnpricedBoth"></param>
/// <param name="bal"> </param>
/// <param name="markStockoutBit"> </param>
/// <param name="usableStock"> </param>
/// <param name="approved"> </param>
public decimal LoadOptionsForOrderDetail(int userID, int orderDetailID, PriceSettings pricedUnpricedBoth, Balance bal, bool markStockoutBit, out decimal usableStock, out decimal approved)
{
decimal avQuantity = 0;
BLL.OrderDetail orderDetail = new OrderDetail();
orderDetail.LoadByPrimaryKey(orderDetailID);
BLL.Order order = new Order();
order.LoadByPrimaryKey(orderDetail.OrderID);
Institution ru = new Institution();
ru.LoadByPrimaryKey(order.RequestedBy);
int month = EthiopianDate.EthiopianDate.Now.Month;
int year = EthiopianDate.EthiopianDate.Now.Year;
decimal availableQty = 0;
usableStock = 0;
approved = 0;
int? manufacturerPrefrence = null, unitID = null;
if (!orderDetail.IsColumnNull("PreferredManufacturerID"))
{
manufacturerPrefrence = orderDetail.PreferredManufacturerID;
}
DateTime? expPreferrence = null;
if(!orderDetail.IsColumnNull("PreferredExpiryDate"))
{
expPreferrence = orderDetail.PreferredExpiryDate;
}
int? preferredPhysicalStoreID = null;
if (!orderDetail.IsColumnNull("PreferredPhysicalStoreID"))
{
preferredPhysicalStoreID = orderDetail.PreferredPhysicalStoreID;
}
//--------------------------------------------------------------------------
//manufacturerPrefrence = null; //We are overriding the manufacturer preference.
//expPreferrence = null;
//--------------------------------------------------------------------------
if (!orderDetail.IsColumnNull("UnitID"))
{
unitID = orderDetail.UnitID;
}
BLL.UserActivity userStore = new UserActivity();
// Definitely a danger zone
userStore.LoadByUserIDAndStoreType(userID, order.FromStore, orderDetail.ItemID, unitID.Value);
while (!userStore.EOF)
{
var activity = new Activity();
activity.LoadByPrimaryKey(userStore.ActivityID);
if(order.FromStore == Mode.Constants.RDF && !BLL.Settings.IsCenter && !BLL.Settings.PrivateCanGetFromMDGAndPBS && ru.Ownership == OwnershipType.Constants.Private && activity.IsSubsidized ){
userStore.MoveNext();
continue;
}
// Load the Activity Selection that has PRiced Commodities.
Balance balance = new Balance();
if (pricedUnpricedBoth == PriceSettings.PRICED_ONLY || pricedUnpricedBoth == PriceSettings.BOTH)
{
BLL.Order.MakeStockCalculations(userID, month, year, PriceSettings.PRICED_ONLY, orderDetail,
userStore.ActivityID, orderDetail.ItemID, order, balance, unitID,
manufacturerPrefrence, expPreferrence, preferredPhysicalStoreID, out usableStock, out approved,
out availableQty, markStockoutBit);
avQuantity += availableQty;
if (availableQty > 0)
{
this.AddNew();
this.ID = userStore.ActivityID;
this.Name = string.Format("{0} ({1})", activity.FullActivityName, availableQty.ToString("#,##0"));
PrepareColumnsForApproval();
this.SetColumn("AvailableSKU", availableQty);
this.SetColumn("IsDeliveryNote", false);
this.SetColumn("TextID",string.Format("N{0}",this.ID));
}
}
// Load Activity Selections for Delivery Note Items.
if (pricedUnpricedBoth == PriceSettings.DELIVERY_NOTE_ONLY || pricedUnpricedBoth == PriceSettings.BOTH)
{
BLL.Order.MakeStockCalculations(userID, month, year, PriceSettings.DELIVERY_NOTE_ONLY, orderDetail,
userStore.ActivityID, orderDetail.ItemID, order, balance, unitID,
manufacturerPrefrence, expPreferrence, preferredPhysicalStoreID, out usableStock, out approved,
out availableQty, markStockoutBit);
avQuantity += availableQty;
if (availableQty > 0)
{
this.AddNew();
this.ID = userStore.ActivityID; //Just to give it a different ID so we know there is a change only.
this.Name = string.Format("+-- {0} - Delivery Notes - ({1})", userStore.GetColumn("ActivityName"),
availableQty.ToString("#,##0"));
PrepareColumnsForApproval();
this.SetColumn("AvailableSKU", availableQty);
this.SetColumn("IsDeliveryNote", true);
this.SetColumn("TextID", string.Format("D{0}", this.ID));
}
}
userStore.MoveNext();
}
return avQuantity;
}
/// <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;
}
public void GenerateExpiryChart()
{
// Generate the pie Chart for the Current SOH and EXpired Drugs
ReceiveDoc rec = new ReceiveDoc();
Balance bal = new Balance();
chartPie.Series.Clear();
Item itm = new Item();
int storeId = Convert.ToInt32(cboStores.SelectedValue);
object[] objExp = itm.CountExpiredItemsAndAmount(storeId);
Int64 expAmount = Convert.ToInt64(objExp[0]);
Double expCost = Convert.ToDouble(objExp[1]);
// TOFIX ---------
object[] nearObj = itm.CountNearlyExpiredQtyAmount(storeId);
Int64 nearExpAmount = Convert.ToInt64(nearObj[0]);
double nearExpCost = Convert.ToDouble(nearObj[1]);
object[] sohObj = {0,0}; //itm.GetAllSohQtyAmount(storeId);
Int64 soh = Convert.ToInt64(sohObj[0]);
double sohPrice = Convert.ToDouble(sohObj[1]);
Int64 normal = (soh - nearExpAmount - expAmount);
Int64 nearExpiry = nearExpAmount;
Int64 expired = expAmount;
object[] obj = { normal, nearExpiry, expired };
DataTable dtSOHList = new DataTable();
dtSOHList.Columns.Add("Type");
dtSOHList.Columns.Add("Value");
dtSOHList.Columns[1].DataType = typeof(Int64);
double normalPrice = (sohPrice - nearExpCost - expAmount);
Int64 totItm = normal + nearExpiry + expired;
object[] oo = { "Normal : " + normalPrice.ToString("C"), obj[0] };
dtSOHList.Rows.Add(oo);
object[] oo3 = { "Expired : " + expCost.ToString("C"), obj[2] };
dtSOHList.Rows.Add(oo3);
object[] oo2 = { "Near Expiry : " + nearExpCost.ToString("C"), obj[1] };
dtSOHList.Rows.Add(oo2);
decimal per = 0;
if (Convert.ToDecimal(totItm) > 0)
{
per = Convert.ToDecimal(normal) / Convert.ToDecimal(totItm) * 100;
}
Series serExpired = new Series("pie", ViewType.Pie3D);
serExpired.DataSource = dtSOHList;
serExpired.ArgumentScaleType = ScaleType.Qualitative;
serExpired.ArgumentDataMember = "Type";
serExpired.ValueScaleType = ScaleType.Numerical;
serExpired.ValueDataMembers.AddRange(new string[] { "Value" });
serExpired.PointOptions.PointView = PointView.ArgumentAndValues;
serExpired.LegendText = "Key";
serExpired.PointOptions.ValueNumericOptions.Format = NumericFormat.Percent;
serExpired.PointOptions.ValueNumericOptions.Precision = 0;
((PieSeriesLabel)serExpired.Label).Position = PieSeriesLabelPosition.TwoColumns;
((PiePointOptions)serExpired.PointOptions).PointView = PointView.ArgumentAndValues;
chartPie.Series.Add(serExpired);
chartPie.Size = new System.Drawing.Size(1000, 500);
}
private void PopulateSStatusReport()
{
if (curMont != 0 && curYear != 0)
{
var storeId = Convert.ToInt32(cboStores.EditValue);
curYear = Convert.ToInt32(cboYear.EditValue);
Balance blnc = new Balance();
DataTable dtbl = new DataTable();
if (storeId == 0)
{
dtbl = blnc.GetSOHForAllStores(curMont, Convert.ToInt32(cboYear.EditValue));
}
else
{
dtbl = blnc.GetSOH(storeId, curMont, Convert.ToInt32(cboYear.EditValue));
}
Items itm = new Items();
Balance bal = new Balance();
ReceiveDoc rec = new ReceiveDoc();
Programs prog = new Programs();
prog.GetProgramByName("Family Planning");
DataTable dtItm = itm.GetItemsByProgram(prog.ID);
int totalECLS = dtItm.Rows.Count;
int stockin, stockout, overstock, nearEOP, belowEOP, freeStockOut;
if (Convert.ToInt32(lkCategory.EditValue) != 0)
{
stockin = (from m in dtbl.AsEnumerable()
where m["Status"].ToString() == "Normal"
&& Convert.ToInt32(m["TypeID"]) == Convert.ToInt32(lkCategory.EditValue) && ((!ckExclude.Checked) || Convert.ToInt32(m["EverReceived"]) == 1)
select m).Count();
stockout = (from m in dtbl.AsEnumerable()
where m["Status"].ToString() == "Stock Out"
&& Convert.ToInt32(m["TypeID"]) == Convert.ToInt32(lkCategory.EditValue) && ((!ckExclude.Checked) || Convert.ToInt32(m["EverReceived"]) == 1)
select m).Count();
overstock = (from m in dtbl.AsEnumerable()
where m["Status"].ToString() == "Over Stocked"
&& Convert.ToInt32(m["TypeID"]) == Convert.ToInt32(lkCategory.EditValue) && ((!ckExclude.Checked) || Convert.ToInt32(m["EverReceived"]) == 1)
select m).Count();
nearEOP = (from m in dtbl.AsEnumerable()
where m["Status"].ToString() == "Near EOP"
&& Convert.ToInt32(m["TypeID"]) == Convert.ToInt32(lkCategory.EditValue) && ((!ckExclude.Checked) || Convert.ToInt32(m["EverReceived"]) == 1)
select m).Count();
belowEOP = (from m in dtbl.AsEnumerable()
where m["Status"].ToString() == "Below EOP"
&& Convert.ToInt32(m["TypeID"]) == Convert.ToInt32(lkCategory.EditValue) && ((!ckExclude.Checked) || Convert.ToInt32(m["EverReceived"]) == 1)
select m).Count();
freeStockOut = (from m in dtbl.AsEnumerable()
where m["Status"].ToString() == "Stock Out"
&& Convert.ToInt32(m["TypeID"]) == Convert.ToInt32(lkCategory.EditValue) && ((!ckExclude.Checked) || Convert.ToInt32(m["EverReceived"]) == 1)
select m).Count();
}
else
{
stockin = (from m in dtbl.AsEnumerable()
where m["Status"].ToString() == "Normal"
&& ((!ckExclude.Checked) || Convert.ToInt32(m["EverReceived"]) == 1)
select m).Count();
stockout = (from m in dtbl.AsEnumerable()
where m["Status"].ToString() == "Stock Out"
&& ((!ckExclude.Checked) || Convert.ToInt32(m["EverReceived"]) == 1)
select m).Count();
overstock = (from m in dtbl.AsEnumerable()
where m["Status"].ToString() == "Over Stocked"
&& ((!ckExclude.Checked) || Convert.ToInt32(m["EverReceived"]) == 1)
select m).Count();
nearEOP = (from m in dtbl.AsEnumerable()
where m["Status"].ToString() == "Near EOP"
&& ((!ckExclude.Checked) || Convert.ToInt32(m["EverReceived"]) == 1)
select m).Count();
belowEOP = (from m in dtbl.AsEnumerable()
where m["Status"].ToString() == "Below EOP"
&& ((!ckExclude.Checked) || Convert.ToInt32(m["EverReceived"]) == 1)
select m).Count();
freeStockOut = (from m in dtbl.AsEnumerable()
where m["Status"].ToString() == "Stock Out"
&& ((!ckExclude.Checked) || Convert.ToInt32(m["EverReceived"]) == 1)
select m).Count();
}
object[] obj = { stockin, stockout, overstock, nearEOP, belowEOP };
int totalItm = stockin + stockout + nearEOP + overstock;
decimal percen = ((totalItm != 0) ? (Convert.ToDecimal(stockin) / Convert.ToDecimal(totalItm)) * 100 : 0);
percen = Decimal.Round(percen, 0);
percen = ((totalItm != 0) ? (Convert.ToDecimal(stockout) / Convert.ToDecimal(totalItm)) * 100 : 0);
percen = Decimal.Round(percen, 0);
percen = ((totalItm != 0) ? (Convert.ToDecimal(overstock) / Convert.ToDecimal(totalItm)) * 100 : 0);
percen = Decimal.Round(percen, 0);
percen = ((totalItm != 0) ? (Convert.ToDecimal(nearEOP) / Convert.ToDecimal(totalItm)) * 100 : 0);
percen = Decimal.Round(percen, 0);
percen = ((totalItm != 0) ? (Convert.ToDecimal(belowEOP) / Convert.ToDecimal(totalItm)) * 100 : 0);
percen = Decimal.Round(percen, 0);
int totalFree = itm.CountFreeItems();
percen = ((totalFree != 0) ? (Convert.ToDecimal(freeStockOut) / Convert.ToDecimal(totalFree)) * 100 : 0);
percen = Decimal.Round(percen, 0);
totalFree = itm.CountVitalItems();
GenerateStockStatusPieChart(obj);
}
}
private void GenerateStockStatusPieChart()
{
ReceiveDoc rec = new ReceiveDoc();
chartPie.UseWaitCursor = true;
chartPie.Series.Clear();
int storeId = (cboStores.EditValue != null) ? Convert.ToInt32(cboStores.EditValue) : 0;
curYear = Convert.ToInt32(cboYear.EditValue);
var category = Convert.ToInt32(lkCategory.EditValue);
curMont = (curYear == dtCurrent.Year) ? dtCurrent.Month : 12;
//progressBar1.PerformStep();
Balance bal = new Balance();
Int64 stockin = bal.CountStockInByCategory(storeId, curMont, curYear ,category );
// progressBar1.PerformStep();
Int64 stockout = bal.CountStockOutByCategory(storeId, curMont, curYear, category);
// progressBar1.PerformStep();
Int64 overstock = bal.CountOverStockByCategory(storeId, curMont, curYear, category);
// progressBar1.PerformStep();
Int64 nearEOP = bal.CountNearEOPByCategory(storeId, curMont, curYear, category);
// progressBar1.PerformStep();
Int64 BelowEOP = bal.CountBelowEOPByCategory(storeId, curMont, curYear, category);
object[] obj = {stockin, stockout, overstock, nearEOP, BelowEOP};
DataTable dtList = new DataTable();
dtList.Columns.Add("Type");
dtList.Columns.Add("Value");
dtList.Columns[1].DataType = typeof (Int64);
object[] oo = {"In Stock", obj[0]};
dtList.Rows.Add(oo);
object[] oo2 = {"Stock Out", obj[1]};
dtList.Rows.Add(oo2);
object[] oo3 = {"Over Stock", obj[2]};
dtList.Rows.Add(oo3);
object[] oo4 = {"Near EOP", obj[3]};
dtList.Rows.Add(oo4);
object[] oo5 = {"Below EOP", obj[4]};
dtList.Rows.Add(oo5);
Series ser = new Series("pie", ViewType.Pie3D);
ser.DataSource = dtList;
ser.ArgumentScaleType = ScaleType.Qualitative;
ser.ArgumentDataMember = "Type";
ser.ValueScaleType = ScaleType.Numerical;
ser.ValueDataMembers.AddRange(new string[] {"Value"});
ser.PointOptions.PointView = PointView.ArgumentAndValues;
ser.LegendText = "Key";
ser.PointOptions.ValueNumericOptions.Format = NumericFormat.Percent;
ser.PointOptions.ValueNumericOptions.Precision = 0;
((PieSeriesLabel) ser.Label).Position = PieSeriesLabelPosition.TwoColumns;
((PiePointOptions) ser.PointOptions).PointView = PointView.ArgumentAndValues;
chartPie.Series.Add(ser);
chartPie.Size = new System.Drawing.Size(1000, 500);
//progressBar1.PerformStep();
GeneralInfo info = new GeneralInfo();
info.LoadAll();
lblHeader.Text = info.HospitalName + " Stock Status summary of year " + curYear + " of " + cboStores.Text;
// progressBar1.Visible = false;
chartPie.UseWaitCursor = false;
}
public static void MakeStockCalculationsStoredProc(int currentMonth, int currentYear, int orderDetailID, int storeID, int unpricedOrPricedOrBoth, bool markStockoutBit, out int usableStock, out int approved, out int availableQuantity)
{
usableStock = approved = availableQuantity = 0;
Balance bal = new Balance();
if (currentMonth == 13)
{
currentYear++;
currentMonth = 1;
}
ListDictionary ld = new ListDictionary
{
{"@currentMonth",currentMonth},
{"@currentYear",currentYear},
{"@currentDay",DateTime.DaysInMonth(currentYear,currentMonth)},
{"@orderDetailID",orderDetailID},
{"@storeID",storeID},
{"@unpricedOrPricedOrBoth",unpricedOrPricedOrBoth},
{"@markStockoutBit",markStockoutBit?1:0}
};
SqlParameter pUs=new SqlParameter();
pUs.Direction = ParameterDirection.Output;
pUs.ParameterName = "@usableStock";
pUs.Value = usableStock;
ld.Add(pUs.ParameterName, pUs.Value);
SqlParameter pApp = new SqlParameter();
pApp.Direction = ParameterDirection.Output;
pApp.ParameterName = "@approved";
pApp.Value = approved;
ld.Add(pApp.ParameterName, pApp.Value);
SqlParameter pAv = new SqlParameter();
pAv.Direction = ParameterDirection.Output;
pAv.ParameterName = "@availableQuantity";
pAv.Value = availableQuantity;
ld.Add(pAv.ParameterName, pAv.Value);
bal.LoadFromSqlNoExec("MakeStockCalculations", ld);
usableStock = Convert.ToInt32(ld["@usableStock"]);// Convert.ToInt32(pUs.Value);
approved = Convert.ToInt32(ld["@approved"]); //Convert.ToInt32(pApp.Value);
availableQuantity = Convert.ToInt32(ld["@availableQuantity"]); //Convert.ToInt32(pAv.Value);
}
/// <summary>
/// Gets the balance of all items
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void bw_DoWork(object sender, DoWorkEventArgs e)
{
Balance bal = new Balance();
int[] arr = (int [])e.Argument;
int storeId = arr[0], year= arr[1], programID=arr[2];
//if (year == EthiopianDate.EthiopianDate.Now.Year + 1)
//{
// month = 1; //When we're sending the month as 1 if the year has been incremented (Fiscal year = year + 1 if month > 10)
//}
DataTable dtBal = bal.ConsumptionByUnit(storeId, year, programID, bw);
e.Result = dtBal;
}
private void BuildStoreInventoryListForHandleUnit(int year, int storeId, DataTable dtItm)
{
string[] str = { "Item Name", "Batch No.", "Remark" };
foreach (string co in str)
{
dtBB.Columns.Add(co);
}
str = new string[] { "ItemId", "No.", "Beginning Balance", "Ending Balance(SOH)", "Physical Inventory", "ID", "RecID", "UnitID" };//, "Change Since Sene 30" };
foreach (string co in str)
{
dtBB.Columns.Add(co, typeof(int));
}
int count = 1;
var yProcess = new YearEnd();
var bal = new Balance();
dtDate.Value = DateTime.Now;
var dtCurent = new DateTime();
dtDate.CustomFormat = "MM/dd/yyyy";
dtCurent = ConvertDate.DateConverter(dtDate.Text);
var recDoc = new ReceiveDoc();
int month = dtCurent.Month;
if ((dtCurent.Month == 10 && dtCurent.Day == 30) || dtCurent.Month == 11)
{
btnSave.Enabled = ((!yProcess.IsInventoryComplete(year, storeId)));
//btnSave.Enabled = true;
month = 10;
}
else
btnSave.Enabled = false;
var yEnd = new YearEnd();
foreach (DataRow dr in dtItm.Rows)//For each item
{
var itemName = dr["ItemName"].ToString() + " - " + dr["DosageForm"].ToString() + " - " + dr["Strength"].ToString();
int itemId= Convert.ToInt32(dr["ID"]);
var unitid = Convert.ToInt32(dr["UnitID"]);
var BalanceExists = (yProcess.DoesBalanceExistByUnit(year, itemId,storeId, false,unitid));
//We don't want to display those items whose inventory has already been done.
if (BalanceExists)
continue;
// Int64 soh = bal.GetSOHByUnit(Convert.ToInt32(dr["ID"]), storeId, month, year,unitid);
Int64 soh = bal.GetSOHByUnitOptimized(Convert.ToInt32(dr["ID"]), storeId, month, year, unitid);
Int64 bbal = yEnd.GetBBalanceByUnit(year, storeId, Convert.ToInt32(dr["ID"]), month,unitid);
yProcess.GetBalanceByItemIdByUnitID(year, storeId, Convert.ToInt32(dr["ID"]),unitid);
Int64 BB = (yProcess.RowCount > 0) ? yProcess.BBalance : bbal;
Int64 EB = ((yProcess.RowCount > 0 && yProcess.EBalance != 0) ? yProcess.EBalance : soh);
string Phy = (yProcess.RowCount > 0) ? yProcess.PhysicalInventory.ToString() : (_defaultValueToPhysicalInventory ? EB.ToString() : "");
string remark = (yProcess.RowCount > 0) ? yProcess.Remark : "";
DataRowView drv = dtBB.DefaultView.AddNew();
if (yProcess.RowCount > 0)
{
drv["ID"] = yProcess.ID;
}
drv["ItemId"] = dr["ID"];
drv["No."] = count;
drv["Item Name"] = itemName;
drv["Beginning Balance"] = BB;
drv["Ending Balance(SOH)"] = EB;
drv["UnitID"] = unitid;
if (Phy != "")
{
drv["Physical Inventory"] = Phy;
}
drv["RecID"] = -1;
drv["Remark"] = remark;
count++;
int theLastBalance = 0;
DataTable dtBatchs = recDoc.GetBatchWithValueByUnit(storeId, Convert.ToInt32(dr["ID"]), dtCurent ,unitid);
foreach (DataRow drBatch in dtBatchs.Rows) //For each batch
{
if (drBatch["QuantityLeft"] != DBNull.Value && Convert.ToInt64(drBatch["QuantityLeft"]) != 0)
{
drv = dtBB.DefaultView.AddNew();
drv["Item Name"] = ">>";
drv["UnitID"] =drBatch["UnitID"];
drv["Batch No."] = drBatch["BatchNo"];
drv["Ending Balance(SOH)"] = Convert.ToInt64(drBatch["QuantityLeft"]);
//Now if the physical inventory is chosen to be default value, we set it to the ending balance of last year.
if (_defaultValueToPhysicalInventory)
drv["Physical Inventory"] = drBatch["QuantityLeft"].ToString();
theLastBalance += Convert.ToInt32(drBatch["QuantityLeft"]);
drv["RecID"] = drBatch["ID"];
}
}
//}
}
grdYearEnd.DataSource = dtBB;
dtDate.CustomFormat = "MMMM dd, yyyy"; //throw new NotImplementedException();
}
/// <summary>
///
/// </summary>
/// <param name="storeId"></param>
/// <param name="fromYear">In Ethiopian Calendar</param>
/// <param name="fromMonth">In Ethiopian Calendar</param>
/// <param name="toYear">In Ethiopian Calendar</param>
/// <param name="toMonth">In Ethiopian Calendar</param>
/// <returns></returns>
public DataTable GetRRFReportByUnit(int storeId ,int fromYear, int fromMonth, int toYear, int toMonth)
{
var balance = new Balance();
var startDate = EthiopianDate.EthiopianDate.EthiopianToGregorian(String.Format("{0}/{1}/{2}", 1, fromMonth, fromYear));
var endDate = EthiopianDate.EthiopianDate.EthiopianToGregorian(String.Format("{0}/{1}/{2}", 30, toMonth, toYear));
if (fromMonth != 1)
fromMonth--;
else
{
fromYear--;
fromMonth = 12; //Because SOH returns stock until the end of the month
}
var dtbl = balance.GetSOHByUnitForRRF(storeId,fromMonth, fromYear);
var dtbl2 = balance.GetSOHByUnitForRRF(storeId, toMonth, toYear);
var dt1 = new DateTime(fromYear, fromMonth, DateTime.DaysInMonth(fromYear, fromMonth));
var dt2 = new DateTime(toYear, toMonth, DateTime.DaysInMonth(toYear, toMonth));
//var query = string.Format("select distinct Items.ID, isnull(Quantity,0) as Quantity ,IsNull(A.UnitID ,0) from" +
// " Items left join (select ItemID,UnitID, sum(Quantity) as Quantity from ReceiveDoc rd " +
// "where [Date] between '{0}' and '{1}' and" + " StoreID = {2} group by ItemID,UnitID,SubProgramID) as" +
// " A on Items.ID = A.ItemID left join ItemUnit as iu on A.UnitID =iu.ID AND A.ItemID = iu.ItemID LEFT JOIN dbo.ProgramProduct AS pp ON pp.ItemID =A.ItemID", dt1, dt2, storeId);
var query = string.Format(@"SELECT rd.ItemID ID, rd.UnitID ,SUM(Quantity) AS Quantity FROM ReceiveDoc rd JOIN ItemUnit AS iu ON rd.UnitID = iu.ID AND rd.ItemID = iu.ItemID WHERE [Date] BETWEEN '{0}'AND '{1}' AND StoreID = {2} GROUP BY rd.ItemID , rd.UnitID ,rd.SubProgramID", dt1, dt2, storeId);
this.LoadFromRawSql(query);
var received = this.DataTable;
//query = string.Format("select distinct Items.ID, isnull(Quantity,0) as Quantity ,IsNull(A.UnitID ,0) " +
// "from Items left join (select ItemID, UnitID,sum(Quantity) Quantity " +
// "from IssueDoc rd where [Date] between '{0}' and '{1}' and " +
// "StoreID = {2} group by ItemID,UnitID) as A on Items.ID = A.ItemID " +
// "left join ItemUnit as iu on A.UnitID =iu.ID AND A.ItemID = iu.ItemID LEFT JOIN dbo.ProgramProduct AS pp ON pp.ItemID = A.ItemID", dt1, dt2, storeId);
query = string.Format(@"SELECT id.ItemID ID,id.UnitID,SUM(Quantity) AS Quantity FROM dbo.IssueDoc id JOIN ItemUnit AS iu ON id.UnitID = iu.ID AND id.ItemID = iu.ItemID WHERE [Date] BETWEEN '{0}' AND '{1}' AND StoreID = {2} GROUP BY id.ItemID , id.UnitID ", dt1, dt2, storeId);
this.LoadFromRawSql(query);
var issued = this.DataTable;
//query = string.Format("select distinct Items.ID, isnull(Quantity,0) as Quantity,IsNull(A.UnitID ,0) from " +
// "Items left join (select ItemID, UnitID,sum(case when Losses = 1 then - Quantity else " +
// "Quantity end) Quantity from Disposal where [Date] between '{0}' and '{1}' " +
// "and StoreID = {2} group by ItemID,UnitID) as A on Items.ID = A.ItemID " +
// "left join ItemUnit as iu on A.UnitID =iu.ID AND A.ItemID = iu.ItemID LEFT JOIN dbo.ProgramProduct AS pp ON pp.ItemID = A.ItemID", dt1, dt2, storeId);
query = string.Format(@"SELECT d.ItemID ID ,d.UnitID ,SUM(CASE WHEN Losses = 1 THEN -Quantity ELSE Quantity END) Quantity FROM Disposal d JOIN ItemUnit AS iu ON d.UnitID = iu.ID WHERE [Date] BETWEEN '{0}' AND '{1}'AND StoreID = {2} GROUP BY d.ItemID ,d.UnitID", dt1, dt2, storeId);
this.LoadFromRawSql(query);
var lost = this.DataTable;
query = string.Format(@"SELECT DISTINCT
Items.ID ,
Items.StockCodeDACA ,
Items.Cost ,
CASE Items.Cost
WHEN 0 THEN 1
ELSE ISNULL(Items.Cost, 1)
END AS QtyPerPack
FROM Items");
// JOIN dbo.ItemUnit iu ON dbo.Items.ID = iu.ItemID
this.LoadFromRawSql(query);
var preferredPackSizetbl = DataTable;
var itm = new Items();
var daysOutOfStock = this.GetItemsWithLastIssuedOrDisposedDateForUnitBased();
//query=string.Format("select ")
var x = (from y in dtbl.AsEnumerable()
join z in dtbl2.AsEnumerable()
on y["ID"] equals z["ID"]
join p in preferredPackSizetbl.AsEnumerable()
on y["ID"] equals p["ID"]
where Convert.ToInt32(y["EverReceived"]) == 1
&& Convert.ToInt32(y["UnitID"]) == Convert.ToInt32(z["UnitID"])
&& Convert.ToInt32(y["ProgramID"]) == Convert.ToInt32(z["ProgramID"])
select new {
ID = y["ID"],
FullItemName = y["FullItemName"],
Unit = y["Unit"],
StockCode = y["StockCode"],
BeginingBalance = Convert.ToDouble(y["SOH"]),
ProgramID=y["ProgramID"],
UnitID =y["UnitID"],
SOH = Convert.ToDouble(z["SOH"]),
Max = Convert.ToInt32(z["Max"]),
QtyPerPack = Convert.ToDouble(p["QtyPerPack"]),
StockCodeDACA = p["StockCodeDACA"],
TypeID=y["TypeID"]
}).ToArray();
var m = (from n in x
join z in received.AsEnumerable()
on n.ID equals z["ID"]
where Convert.ToInt32(n.UnitID) == Convert.ToInt32(z["UnitID"])
select new {
ID = n.ID,
FullItemName = n.FullItemName,
Unit = n.Unit,
StockCode = n.StockCode,
BeginingBalance = n.BeginingBalance,
SOH = n.SOH,
Max = n.Max,
QtyPerPack = n.QtyPerPack,
StockCodeDACA =n.StockCodeDACA,
ProgramID=n.ProgramID,
Received = z["Quantity"] ,
UnitID =n.UnitID,
TypeID=n.TypeID
}).ToArray();
var l = (from n in m
join z in issued.AsEnumerable()
on n.ID equals z["ID"]
where Convert.ToInt32(n.UnitID) == Convert.ToInt32(z["UnitID"])
select
new
{
ID = n.ID,
FullItemName = n.FullItemName,
Unit = n.Unit,
StockCode = n.StockCode,
BeginingBalance = n.BeginingBalance,
SOH = n.SOH,
Max = Convert.ToInt32(z["Quantity"]) * 2,
StockCodeDACA =n.StockCodeDACA,
QtyPerPack = n.QtyPerPack,
Received = n.Received,
ProgramID=n.ProgramID,
UnitID =n.UnitID,
Issued = Convert.ToInt32(z["Quantity"]),
TypeID=n.TypeID
}).ToArray();
var t = (from n in l
join z in lost.AsEnumerable()
on n.ID equals z["ID"]
where Convert.ToInt32(n.UnitID) == Convert.ToInt32(z["UnitID"])
select new
{
ID = n.ID,
FullItemName = n.FullItemName,
Unit = n.Unit,
StockCode = n.StockCode,
BeginingBalance = n.BeginingBalance,
SOH = n.SOH,
Max = n.Max,
StockCodeDACA = n.StockCodeDACA,
QtyPerPack = n.QtyPerPack,
Received = n.Received,
ProgramID=n.ProgramID,
Issued = n.Issued, LossAdj = z["Quantity"],
Quantity = (n.Max - n.SOH < 0) ? 0 : n.Max - n.SOH,
UnitID = n.UnitID,
TypeID=n.TypeID
}).ToArray();
var t1 = (from n in t
join z in daysOutOfStock.AsEnumerable()
on n.ID equals z["ID"]
select
new
{
ID = n.ID,
FullItemName = n.FullItemName,
Unit = n.Unit,
StockCode = n.StockCode,
BeginingBalance = n.BeginingBalance,
SOH = n.SOH,
Max = n.Max,
StockCodeDACA = n.StockCodeDACA,
QtyPerPack = n.QtyPerPack,
Received = n.Received,
Issued = n.Issued,
LossAdj = n.LossAdj,
UnitID = n.UnitID,
ProgramID = n.ProgramID,
Quantity = (n.Max - n.SOH < 0) ? 0 : n.Max - n.SOH,
DaysOutOfStock = Builder.CalculateStockoutDays(Convert.ToInt32(n.ID), storeId, startDate, endDate),
TypeID = n.TypeID
}).ToArray();
if (t.Length == 0)
{
var t2 = (from n in l
select
new
{
ID = n.ID,
FullItemName = n.FullItemName,
Unit = n.Unit,
StockCode = n.StockCode,
BeginingBalance = n.BeginingBalance,
SOH = n.SOH,
Max = n.Max,
StockCodeDACA = n.StockCodeDACA,
QtyPerPack = n.QtyPerPack,
Received = n.Received,
Issued = n.Issued,
LossAdj = 0,
ProgramID = n.ProgramID,
UnitID = n.UnitID,
Quantity = (n.Max - n.SOH < 0) ? 0 : n.Max - n.SOH,
DaysOutOfStock = Builder.CalculateStockoutDays(Convert.ToInt32(n.ID), storeId, startDate, endDate),//TODO: This is a quick fix. We need to take stock status from the last three months.
//TODO: This is a quick fix. We need to take stock status from the last three months.
MaxStockQty = ((120 * n.Issued) / (60 - Convert.ToInt32(Builder.CalculateStockoutDays(Convert.ToInt32(n.ID), storeId, startDate, endDate)))),
TypeID = n.TypeID
}).ToArray();
//return t;
// Converting shit into antoher shit.
// Just because i was not able to read the elemntes of the anonymus type in another method
var value = new DataTable();
value.Columns.Add("ID", typeof(int));
value.Columns.Add("FullItemName");
value.Columns.Add("Unit");
value.Columns.Add("StockCode");
value.Columns.Add("BeginingBalance", typeof(double));
value.Columns.Add("SOH", typeof(double));
value.Columns.Add("Max", typeof(double));
value.Columns.Add("StockCodeDACA", typeof(string));
value.Columns.Add("QtyPerPack", typeof(double));
value.Columns.Add("Issued", typeof(double));
value.Columns.Add("Received", typeof(double));
value.Columns.Add("LossAdj", typeof(double));
value.Columns.Add("Quantity", typeof(double));
value.Columns.Add("DaysOutOfStock", typeof(int));
value.Columns.Add("MaxStockQty", typeof(double));
value.Columns.Add("ProgramID", typeof(int));
value.Columns.Add("UnitID", typeof(int));
value.Columns.Add("TypeID", typeof(int));
foreach (var v in t2)
{
DataRowView drv = value.DefaultView.AddNew();
drv["ID"] = v.ID;
drv["FullItemName"] = v.FullItemName;
drv["Unit"] = v.Unit;
drv["StockCode"] = v.StockCode;
drv["BeginingBalance"] = v.BeginingBalance;
drv["SOH"] = v.SOH;
drv["Max"] = v.Max;
drv["StockCodeDACA"] = v.StockCodeDACA;
drv["QtyPerPack"] = v.QtyPerPack;
drv["Issued"] = v.Issued;
drv["Received"] = v.Received;
drv["LossAdj"] = v.LossAdj;
drv["Quantity"] = v.Quantity;
drv["ProgramID"] = v.ProgramID;
drv["DaysOutOfStock"] = v.DaysOutOfStock;
drv["MaxStockQty"] = v.MaxStockQty;
drv["UnitID"] = v.UnitID;
drv["TypeID"] = v.TypeID;
}
return value;
}
else
{
var t2 = (from n in t1
select
new
{
ID = n.ID,
FullItemName = n.FullItemName,
Unit = n.Unit,
StockCode = n.StockCode,
BeginingBalance = n.BeginingBalance,
SOH = n.SOH,
Max = n.Max,
StockCodeDACA = n.StockCodeDACA,
QtyPerPack = n.QtyPerPack,
Received = n.Received,
Issued = n.Issued,
LossAdj = n.LossAdj,
ProgramID = n.ProgramID,
UnitID = n.UnitID,
Quantity = (n.Max - n.SOH < 0) ? 0 : n.Max - n.SOH,
DaysOutOfStock =
Builder.CalculateStockoutDays(Convert.ToInt32(n.ID), storeId, startDate, endDate),
//TODO: This is a quick fix. We need to take stock status from the last three months.
//TODO: This is a quick fix. We need to take stock status from the last three months.
MaxStockQty =
((120*n.Issued)/
(60 -
Convert.ToInt32(Builder.CalculateStockoutDays(Convert.ToInt32(n.ID), storeId, startDate,
endDate)))),
TypeID = n.TypeID
}).ToArray();
//return t;
// Converting shit into antoher shit.
// Just because i was not able to read the elemntes of the anonymus type in another method
var value = new DataTable();
value.Columns.Add("ID", typeof (int));
value.Columns.Add("FullItemName");
value.Columns.Add("Unit");
value.Columns.Add("StockCode");
value.Columns.Add("BeginingBalance", typeof (double));
value.Columns.Add("SOH", typeof (double));
value.Columns.Add("Max", typeof (double));
value.Columns.Add("StockCodeDACA", typeof (string));
value.Columns.Add("QtyPerPack", typeof (double));
value.Columns.Add("Issued", typeof (double));
value.Columns.Add("Received", typeof (double));
value.Columns.Add("LossAdj", typeof (double));
value.Columns.Add("Quantity", typeof (double));
value.Columns.Add("DaysOutOfStock", typeof (int));
value.Columns.Add("MaxStockQty", typeof (double));
value.Columns.Add("ProgramID", typeof (int));
value.Columns.Add("UnitID", typeof (int));
value.Columns.Add("TypeID", typeof (int));
foreach (var v in t2)
{
DataRowView drv = value.DefaultView.AddNew();
drv["ID"] = v.ID;
drv["FullItemName"] = v.FullItemName;
drv["Unit"] = v.Unit;
drv["StockCode"] = v.StockCode;
drv["BeginingBalance"] = v.BeginingBalance;
drv["SOH"] = v.SOH;
drv["Max"] = v.Max;
drv["StockCodeDACA"] = v.StockCodeDACA;
drv["QtyPerPack"] = v.QtyPerPack;
drv["Issued"] = v.Issued;
drv["Received"] = v.Received;
drv["LossAdj"] = v.LossAdj;
drv["Quantity"] = v.Quantity;
drv["ProgramID"] = v.ProgramID;
drv["DaysOutOfStock"] = v.DaysOutOfStock;
drv["MaxStockQty"] = v.MaxStockQty;
drv["UnitID"] = v.UnitID;
drv["TypeID"] = v.TypeID;
}
return value;
}
}
private void repositoryItemButtonEdit1_Click(object sender, EventArgs e)
{
DataRow dr = grdViewYearEnd.GetDataRow(grdViewYearEnd.GetSelectedRows()[0]);
if (dr != null)
{
string batchNo = dr["Batch No."].ToString();
int recID = int.Parse(dr["RecID"].ToString());
BLL.ReceiveDoc rec = new ReceiveDoc();
if (recID == -1)
{
XtraMessageBox.Show("Use this button only on invalid batches", "Nothing to Remove",
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
rec.LoadByPrimaryKey(recID);
int itemID = rec.ItemID;
//Do another validation here (Make sure that the items that they try to remove have issues like the Total SOH not being equal to the sum of the listed batches and so forth.
int storeId = (cboStores.EditValue != null) ? Convert.ToInt32(cboStores.EditValue) : 1;
int month = EthiopianDate.EthiopianDate.Now.Month;
int year = EthiopianDate.EthiopianDate.Now.Year;
BLL.Balance bal = new Balance();
BLL.Items itm = new Items();
itm.LoadByPrimaryKey(itemID);
long currentBalance = bal.GetSOH(itemID, storeId, month, year);
long totalQuantityLeftInBatches = itm.TotalQuantityLeftInAllBatches(storeId);
if (currentBalance == totalQuantityLeftInBatches)
{
XtraMessageBox.Show(
"You do not have to use this button to remove this batch. Please set the physical inventory 0 instead. Use this button only for items with discrepancies.",
"Unapplicable", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
if (XtraMessageBox.Show("This will make the quantity left for this received batch zero and remove it from this list. Are you sure you want to continue?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
if (BLL.ReceiveDoc.MarkReceivedBatchAsEmpty(recID))
{
dr.Delete();
}
}
}
}
public DataTable GetEmergencyRRFReport(int storeId, int fromYear, int fromMonth, int toYear, int toMonth)
{
Balance balance = new Balance();
var startDate = EthiopianDate.EthiopianDate.EthiopianToGregorian(String.Format("{0}/{1}/{2}", 1, fromMonth, fromYear));
var endDate = EthiopianDate.EthiopianDate.EthiopianToGregorian(String.Format("{0}/{1}/{2}", 30, toMonth, toYear));
if (fromMonth != 1)
fromMonth--;
else
{
fromYear--;
fromMonth = 12;//Because SOH returns stock until the end of the month
}
DataTable dtbl = balance.GetSOHForProgramRRF(storeId, fromMonth, fromYear);
DataTable dtbl2 = balance.GetSOHForProgramRRF(storeId, toMonth, toYear);
DateTime dt1 = new DateTime(fromYear, fromMonth, DateTime.DaysInMonth(fromYear, fromMonth));
DateTime dt2 = new DateTime(toYear, toMonth, DateTime.DaysInMonth(toYear, toMonth));
string query = string.Format("select distinct Items.ID, isnull(Quantity,0) as Quantity from" +
" Items left join (select ItemID, sum(Quantity) as Quantity from ReceiveDoc rd " +
"where [Date] between '{0}' and '{1}' and" + " StoreID = {2} group by ItemID) as" +
" A on Items.ID = A.ItemID", dt1, dt2, storeId);
this.LoadFromRawSql(query);
DataTable received = this.DataTable;
query = string.Format("select distinct Items.ID, isnull(Quantity,0) as Quantity " +
"from Items left join (select ItemID, sum(Quantity) Quantity " +
"from IssueDoc rd where [Date] between '{0}' and '{1}' and " +
"StoreID = {2} group by ItemID) as A on Items.ID = A.ItemID", dt1, dt2, storeId);
this.LoadFromRawSql(query);
DataTable issued = this.DataTable;
query = string.Format("select distinct Items.ID, isnull(Quantity,0) as Quantity from " +
"Items left join (select ItemID, sum(case when Losses = 1 then - Quantity else " +
"Quantity end) Quantity from Disposal where [Date] between '{0}' and '{1}' " +
"and StoreID = {2} group by ItemID) as A on Items.ID = A.ItemID", dt1, dt2, storeId);
this.LoadFromRawSql(query);
DataTable lost = this.DataTable;
query = string.Format("select distinct Items.ID,Items.StockCodeDACA,Items.Cost, case Items.Cost " +
"when 0 then 1 else isnull(Items.Cost,1) end as QtyPerPack from Items");
this.LoadFromRawSql(query);
DataTable preferredPackSizetbl = DataTable;
BLL.Items itm = new Items();
System.Data.DataTable daysOutOfStock = this.GetItemsWithLastIssuedOrDisposedDate();
//query=string.Format("select ")
var theFilter = "Below EOP";
var x = (from y in dtbl.AsEnumerable()
join z in dtbl2.AsEnumerable()
on y["ID"] equals z["ID"]
join p in preferredPackSizetbl.AsEnumerable()
on y["ID"] equals p["ID"]
where Convert.ToInt32(y["EverReceived"]) == 1
select new
{
ID = y["ID"],
FullItemName = y["FullItemName"],
Unit = y["Unit"],
StockCode = y["StockCode"],
BeginingBalance = Convert.ToInt32(y["SOH"]),
SOH = Convert.ToInt32(z["SOH"]),
Max = Convert.ToInt32(z["Max"]),
QtyPerPack = Convert.ToInt32(p["QtyPerPack"]),
StockCodeDACA = p["StockCodeDACA"],
Status = z["Status"].ToString()
}).Distinct().ToArray();
var u = (from z in x where theFilter.Contains(z.Status) select z).Distinct().ToArray();
var m = (from n in u
join z in received.AsEnumerable()
on n.ID equals z["ID"]
select new
{
ID = n.ID,
FullItemName = n.FullItemName,
Unit = n.Unit,
StockCode = n.StockCode,
BeginingBalance = n.BeginingBalance,
SOH = n.SOH,
Max = n.Max,
QtyPerPack = n.QtyPerPack,
StockCodeDACA = n.StockCodeDACA,
Received = z["Quantity"]
}).ToArray();
var l = (from n in m
join z in issued.AsEnumerable()
on n.ID equals z["ID"]
select
new
{
ID = n.ID,
FullItemName = n.FullItemName,
Unit = n.Unit,
StockCode = n.StockCode,
BeginingBalance = n.BeginingBalance,
SOH = n.SOH,
Max = Convert.ToInt32(z["Quantity"]) * 2,
StockCodeDACA = n.StockCodeDACA,
QtyPerPack = n.QtyPerPack,
Received = n.Received,
Issued = Convert.ToInt32(z["Quantity"]),
}).ToArray();
var t = (from n in l
join z in lost.AsEnumerable()
on n.ID equals z["ID"]
select new
{
ID = n.ID,
FullItemName = n.FullItemName,
Unit = n.Unit,
StockCode = n.StockCode,
BeginingBalance = n.BeginingBalance,
SOH = n.SOH,
Max = n.Max,
StockCodeDACA = n.StockCodeDACA,
QtyPerPack = n.QtyPerPack,
Received = n.Received,
Issued = n.Issued,
LossAdj = z["Quantity"],
Quantity = (n.Max - n.SOH < 0) ? 0 : n.Max - n.SOH
}).ToArray();
var t1 = (from n in t
join z in daysOutOfStock.AsEnumerable()
on n.ID equals z["ID"]
select
new
{
ID = n.ID,
FullItemName = n.FullItemName,
Unit = n.Unit,
StockCode = n.StockCode,
BeginingBalance = n.BeginingBalance,
SOH = n.SOH,
Max = n.Max,
StockCodeDACA = n.StockCodeDACA,
QtyPerPack = n.QtyPerPack,
Received = n.Received,
Issued = n.Issued,
LossAdj = n.LossAdj,
Quantity = (n.Max - n.SOH < 0) ? 0 : n.Max - n.SOH,
DaysOutOfStock = Builder.CalculateStockoutDays(Convert.ToInt32(n.ID), storeId, startDate, endDate)//Builder.CalculateStockoutDays(Convert.ToInt32(ID), storeId, startDate,endDate) DBNull.Value ? 0 : (Convert.ToInt32(z["DaysOutOfStock"]) < 60 ? z["DaysOutOfStock"] : 0)
}).ToArray();
var t2 = (from n in t1
select
new
{
ID = n.ID,
FullItemName = n.FullItemName,
Unit = n.Unit,
StockCode = n.StockCode,
BeginingBalance = n.BeginingBalance,
SOH = n.SOH,
Max = n.Max,
StockCodeDACA = n.StockCodeDACA,
QtyPerPack = n.QtyPerPack,
Received = n.Received,
Issued = n.Issued,
LossAdj = n.LossAdj,
Quantity = (n.Max - n.SOH < 0) ? 0 : n.Max - n.SOH,
DaysOutOfStock = Builder.CalculateStockoutDays(Convert.ToInt32(n.ID), storeId, startDate, endDate),//TODO: This is a quick fix. We need to take stock status from the last three months.
//TODO: This is a quick fix. We need to take stock status from the last three months.
MaxStockQty = ((120 * n.Issued) / (60 - Convert.ToInt32(n.DaysOutOfStock)))
}).ToArray();
//return t;
// Converting shit into antoher shit.
// Just because i was not able to read the elemntes of the anonymus type in another method
DataTable value = new DataTable();
value.Columns.Add("ID", typeof(int));
value.Columns.Add("FullItemName");
value.Columns.Add("Unit");
value.Columns.Add("StockCode");
value.Columns.Add("BeginingBalance", typeof(int));
value.Columns.Add("SOH", typeof(int));
value.Columns.Add("Max", typeof(int));
value.Columns.Add("StockCodeDACA", typeof(string));
value.Columns.Add("QtyPerPack", typeof(int));
value.Columns.Add("Issued", typeof(int));
value.Columns.Add("Received", typeof(int));
value.Columns.Add("LossAdj", typeof(int));
value.Columns.Add("Quantity", typeof(int));
value.Columns.Add("DaysOutOfStock", typeof(int));
value.Columns.Add("MaxStockQty", typeof(int));
value.Columns.Add("ProgramID", typeof(int));
foreach (var v in t2)
{
DataRowView drv = value.DefaultView.AddNew();
drv["ID"] = v.ID;
drv["FullItemName"] = v.FullItemName;
drv["Unit"] = v.Unit;
drv["StockCode"] = v.StockCode;
drv["BeginingBalance"] = v.BeginingBalance;
drv["SOH"] = v.SOH;
drv["Max"] = v.Max;
drv["StockCodeDACA"] = v.StockCodeDACA;
drv["QtyPerPack"] = v.QtyPerPack;
drv["Issued"] = v.Issued;
drv["Received"] = v.Received;
drv["LossAdj"] = v.LossAdj;
drv["Quantity"] = v.Quantity;
drv["DaysOutOfStock"] = Builder.CalculateStockoutDays(Convert.ToInt32(drv["ID"]), storeId, startDate, endDate);
drv["MaxStockQty"] = v.MaxStockQty;
}
return value;
}
public DataView GetApprovedByOrderID(int orderID)
{
var query = HCMIS.Repository.Queries.Balance.SelectApprovedInformationByOrderID(orderID);
var balance = new Balance();
balance.LoadFromRawSql(query);
return balance.DefaultView;
}