public Int64 GetBBalance(int year, int storeId, int itemId, int month)
{
//CALENDAR:
this.FlushData();
Int64 bb = 0;
//int bYear = ((month > 10) ? year : year - 1);
EthiopianDate.EthiopianDate ethioDate = new EthiopianDate.EthiopianDate(year, month, 30);
int bYear = ethioDate.FiscalYear;
string query =
String.Format(
"Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2} and (AutomaticallyEntered = 0 or AutomaticallyEntered is null) order by PhysicalInventory desc",
storeId, itemId, bYear);
this.LoadFromRawSql(query);
if (this.DataTable.Rows.Count > 0 && this.DataTable.Rows[0]["PhysicalInventory"] != DBNull.Value)
{
bb = Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventory"]);
}
else
{
this.LoadFromRawSql(
String.Format(
"Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2} and (AutomaticallyEntered = 0 or AutomaticallyEntered is null)",
storeId, itemId, year - 1));
if (this.DataTable.Rows.Count > 0)
{
bb = Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventory"]);
}
else if (((year + 8) > DateTime.Now.Year) || (month > 10 && (year + 8) == DateTime.Now.Year))
// to check if it is different year from current
{
Int64 cons = 0;
IssueDoc iss = new IssueDoc();
ReceiveDoc rec = new ReceiveDoc();
Disposal dis = new Disposal();
if ((year + 8) > DateTime.Now.Year) // to check if it is hamle and Nehase
{
year = year - 1;
}
month = 10;
//}
long RecievedQuantity = rec.GetReceivedQuantityTillMonth(itemId, storeId, month, year);
long AdjustedQuantity = dis.GetAdjustedQuantityTillMonth(itemId, storeId, month, year);
long IssuedQuantity = iss.GetIssuedQuantityTillMonth(itemId, storeId, month, year);
long LossQuantity = dis.GetLossesQuantityTillMonth(itemId, storeId, month, year);
// long BalanceQuantity = yEnd.GetBBalance(year, storeId, itemId, month);
cons = (RecievedQuantity + AdjustedQuantity - IssuedQuantity - LossQuantity);
//cons = (rec.GetReceivedQuantityTillMonth(itemId, storeId, month, year) + dis.GetAdjustedQuantityTillMonth(itemId, storeId, month, year) - iss.GetIssuedQuantityTillMonth(itemId, storeId, month, year) - dis.GetLossesQuantityTillMonth(itemId, storeId, month, year));
bb = cons;
}
}
return(bb);
}
/// <summary>
/// Get begining balance by item, store and month
/// </summary>
/// <param name="year">The year.</param>
/// <param name="storeId">The store id.</param>
/// <param name="itemId">The item id.</param>
/// <param name="month">The month.</param>
/// <returns></returns>
public Int64 GetBBalance(int year, int storeId, int itemId, int month)
{
this.FlushData();
Int64 bb = 0;
int bYear = ((month > 10) ? year : year - 1);
this.LoadFromRawSql(HCMIS.Repository.Queries.YearEnd.SelectGetBBalance(storeId, itemId, bYear));
if (this.DataTable.Rows.Count > 0)
{
bb = Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventory"]);
}
else
{
this.LoadFromRawSql(HCMIS.Repository.Queries.YearEnd.SelectGetBBalanceSelectElse(year, storeId, itemId));
if (this.DataTable.Rows.Count > 0)
{
bb = Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventory"]);
}
else if (((year + 8) > DateTimeHelper.ServerDateTime.Year) || (month > 10 && (year + 8) == DateTimeHelper.ServerDateTime.Year)) // to check if it is different year from current
{
Int64 cons = 0;
IssueDoc iss = new IssueDoc();
ReceiveDoc rec = new ReceiveDoc();
LossAndAdjustment dis = new LossAndAdjustment();
if ((year + 8) > DateTimeHelper.ServerDateTime.Year) // to check if it is hamle and Nehase
{
year = year - 1;
}
month = 10;
//}
long receivedQuantity = rec.GetReceivedQuantityTillMonth(itemId, storeId, month, year);
long adjustedQuantity = dis.GetAdjustedQuantityTillMonth(itemId, storeId, month, year);
long issuedQuantity = iss.GetIssuedQuantityTillMonth(itemId, storeId, month, year);
long lostQuantity = dis.GetLossesQuantityTillMonth(itemId, storeId, month, year);
cons = (receivedQuantity + adjustedQuantity - issuedQuantity - lostQuantity);
bb = cons;
}
}
return(bb);
}
/// <summary>
/// Get begining balance by item, store and month
/// </summary>
/// <param name="year">The year.</param>
/// <param name="storeId">The store id.</param>
/// <param name="itemId">The item id.</param>
/// <param name="month">The month.</param>
/// <returns></returns>
public Int64 GetBBalance(int year, int storeId, int itemId, int month)
{
this.FlushData();
Int64 bb = 0;
int bYear = ((month > 10) ? year : year - 1);
this.LoadFromRawSql(HCMIS.Repository.Queries.YearEnd.SelectGetBBalance(storeId, itemId, bYear));
if (this.DataTable.Rows.Count > 0)
{
bb = Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventory"]);
}
else
{
this.LoadFromRawSql(HCMIS.Repository.Queries.YearEnd.SelectGetBBalanceSelectElse(year, storeId, itemId));
if (this.DataTable.Rows.Count > 0)
{
bb = Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventory"]);
}
else if (((year + 8) > DateTimeHelper.ServerDateTime.Year) || (month > 10 && (year + 8) == DateTimeHelper.ServerDateTime.Year)) // to check if it is different year from current
{
Int64 cons = 0;
IssueDoc iss = new IssueDoc();
ReceiveDoc rec = new ReceiveDoc();
LossAndAdjustment dis = new LossAndAdjustment();
if ((year + 8) > DateTimeHelper.ServerDateTime.Year) // to check if it is hamle and Nehase
year = year - 1;
month = 10;
//}
long receivedQuantity = rec.GetReceivedQuantityTillMonth(itemId, storeId, month, year);
long adjustedQuantity = dis.GetAdjustedQuantityTillMonth(itemId, storeId, month, year);
long issuedQuantity = iss.GetIssuedQuantityTillMonth(itemId, storeId, month, year);
long lostQuantity = dis.GetLossesQuantityTillMonth(itemId, storeId, month, year);
cons = (receivedQuantity + adjustedQuantity - issuedQuantity - lostQuantity);
bb = cons;
}
}
return bb;
}
public DataTable BalanceAllItems(int storeId, int year, int month, string selectedType)
{
IssueDoc iss = new IssueDoc();
ReceiveDoc rec = new ReceiveDoc();
Disposal dis = new Disposal();
YearEnd yEnd = new YearEnd();
Items itm = new Items();
DataTable dtResult = new DataTable();
string[] col = { "FullItemName", "BBalance", "BBAmount", "ReceivedQty", "ReceivedAmount", "IssuedQty", "IssuedAmount", "LossesQty", "LossesAmount", "AdjustedQty", "AdjustedAmount", "SOH", "SOHAmount", "Received", "ID", "CategoryId", "SubCategoryID" };
int i = 0;
foreach (string s in col)
{
if (i > 0)
{
dtResult.Columns.Add(s, typeof(double));
}
else
{
dtResult.Columns.Add(s);
}
i++;
}
DataTable dtItem = ((selectedType == "Drug") ? itm.GetAllItems(1) : itm.GetAllSupply());
foreach (DataRow dr in dtItem.Rows)
{
string itemName = dr["FullItemName"].ToString();// +" - " + dr["DosageForm"].ToString() + " - " + dr["Strength"].ToString();
int itemId = Convert.ToInt32(dr["ID"]);
Int64 bb = yEnd.GetBBalance(year, storeId, itemId, month);
double bbAmount = yEnd.GetBBalanceAmount(year, storeId, itemId, month);
Int64 recQuant = rec.GetReceivedQuantityTillMonth(itemId, storeId, month, year);
double recPrice = rec.GetReceivedAmountTillMonth(itemId, storeId, month, year);
Int64 issuedQuant = iss.GetIssuedQuantityTillMonth(itemId, storeId, month, year);
double issPrice = iss.GetIssuedAmountTillMonth(itemId, storeId, month, year);
Int64 lossQuant = dis.GetLossesQuantityTillMonth(itemId, storeId, month, year);
double lossAmount = dis.GetLossesAmountTillMonth(itemId, storeId, month, year);
Int64 adjQuant = dis.GetAdjustedQuantityTillMonth(itemId, storeId, month, year);
double adjAmount = dis.GetAdjustedAmountTillMonth(itemId, storeId, month, year);
Int64 SOH = bb + recQuant + adjQuant - issuedQuant - lossQuant;
double SOHAmount = (bbAmount + recPrice + adjAmount - issPrice) - lossAmount;
if (SOHAmount < 0)
{
;
}
int Isrec = ((bb == 0 && recQuant == 0) ? 0 : 1);
object[] obj = { itemName, bb, bbAmount, recQuant, recPrice, issuedQuant, issPrice, lossQuant, lossAmount, adjQuant, adjAmount, SOH, SOHAmount, Isrec, itemId, Convert.ToInt32(dr["CategoryId"]), Convert.ToInt32(dr["SubCategoryID"]) };
dtResult.Rows.Add(obj);
}
return dtResult;
}
/// <summary>
///
/// </summary>
/// <param name="itemId"></param>
/// <param name="storeId"></param>
/// <param name="mon"></param>
/// <param name="yr"></param>
/// <returns></returns>
public Int64 CalculateSOH(int itemId, int storeId, int mon, int yr)
{
//ask by normal u get soh on fiscal month
IssueDoc iss = new IssueDoc();
ReceiveDoc rec = new ReceiveDoc();
Disposal dis = new Disposal();
YearEnd yEnd = new YearEnd();
int month = (mon > 2) ? mon - 2 : ((mon == 1) ? 11 : 12);
int year = (mon > 2) ? yr : yr - 1;
Int64 cons = 0;
cons = (rec.GetReceivedQuantityTillMonth(itemId, storeId, month, year) + dis.GetAdjustedQuantityTillMonth(itemId, storeId, month, year) - iss.GetIssuedQuantityTillMonth(itemId, storeId, month, year) - dis.GetLossesQuantityTillMonth(itemId, storeId, month, year));
return cons;
}
/// <summary>
/// Gets SOH of a specific item for the end of the specified Ethipian Fiscal Month Item
/// </summary>
/// <param name="itemId"></param>
/// <param name="storeId"></param>
/// <param name="month"></param>
/// <param name="year"></param>
/// <returns></returns>
public Int64 GetSOHOld(int itemId, int storeId, int month, int year)
{
//ask by normal u get soh on normal month
IssueDoc iss = new IssueDoc();
ReceiveDoc rec = new ReceiveDoc();
Disposal dis = new Disposal();
YearEnd yEnd = new YearEnd();
//int month = (mon > 2) ? mon - 2 : ((mon == 1) ? 11 : 12);
//int year = (mon > 2) ? yr : yr - 1;
Int64 cons = 0;
long recievedQuantity = rec.GetReceivedQuantityTillMonth(itemId, storeId, month, year);
long adjustedQuantity = dis.GetAdjustedQuantityTillMonth(itemId, storeId, month, year);
long issuedQuantity = iss.GetIssuedQuantityTillMonth(itemId, storeId, month, year);
long lossQuantity = dis.GetLossesQuantityTillMonth(itemId, storeId, month, year);
long balanceQuantity = yEnd.GetBBalance(year, storeId, itemId, month);
cons = (balanceQuantity + recievedQuantity + adjustedQuantity - issuedQuantity - lossQuantity);
this.FlushData();
// this.LoadFromRawSql(String.Format("SELECT SUM(QuantityLeft) AS Quantity FROM ReceiveDoc WHERE (ItemID = {0}) AND (StoreID = {1} AND ((Month(Date) <= {2} AND Year(Date) = {3}) OR (Month(Date) > 10 AND Year(Date) = {4})))", itemId, storeId, month, year, year - 1));
// cons = (this.DataTable.Rows[0]["Quantity"].ToString() != "") ? Convert.ToInt64(this.DataTable.Rows[0]["Quantity"]) : 0;
return cons;
}
public Int64 GetBBalance(int year, int storeId, int itemId, int month)
{
//CALENDAR:
this.FlushData();
Int64 bb = 0;
//int bYear = ((month > 10) ? year : year - 1);
EthiopianDate.EthiopianDate ethioDate = new EthiopianDate.EthiopianDate(year, month, 30);
int bYear = ethioDate.FiscalYear;
string query =
String.Format(
"Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2} and (AutomaticallyEntered = 0 or AutomaticallyEntered is null) order by PhysicalInventory desc",
storeId, itemId, bYear);
this.LoadFromRawSql(query);
if (this.DataTable.Rows.Count > 0 && this.DataTable.Rows[0]["PhysicalInventory"] != DBNull.Value)
{
bb = Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventory"]);
}
else
{
this.LoadFromRawSql(
String.Format(
"Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2} and (AutomaticallyEntered = 0 or AutomaticallyEntered is null)",
storeId, itemId, year - 1));
if (this.DataTable.Rows.Count > 0)
{
bb = Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventory"]);
}
else if (((year + 8) > DateTime.Now.Year) || (month > 10 && (year + 8) == DateTime.Now.Year))
// to check if it is different year from current
{
Int64 cons = 0;
IssueDoc iss = new IssueDoc();
ReceiveDoc rec = new ReceiveDoc();
Disposal dis = new Disposal();
if ((year + 8) > DateTime.Now.Year) // to check if it is hamle and Nehase
year = year - 1;
month = 10;
//}
long RecievedQuantity = rec.GetReceivedQuantityTillMonth(itemId, storeId, month, year);
long AdjustedQuantity = dis.GetAdjustedQuantityTillMonth(itemId, storeId, month, year);
long IssuedQuantity = iss.GetIssuedQuantityTillMonth(itemId, storeId, month, year);
long LossQuantity = dis.GetLossesQuantityTillMonth(itemId, storeId, month, year);
// long BalanceQuantity = yEnd.GetBBalance(year, storeId, itemId, month);
cons = (RecievedQuantity + AdjustedQuantity - IssuedQuantity - LossQuantity);
//cons = (rec.GetReceivedQuantityTillMonth(itemId, storeId, month, year) + dis.GetAdjustedQuantityTillMonth(itemId, storeId, month, year) - iss.GetIssuedQuantityTillMonth(itemId, storeId, month, year) - dis.GetLossesQuantityTillMonth(itemId, storeId, month, year));
bb = cons;
}
}
return bb;
}