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;
}
/* public Int64 GetBBalance(int year, int storeId, int itemId)
* {
* this.FlushData();
* this.LoadFromRawSql(String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}",storeId,itemId,year));
*
* Int64 bb = ( this.DataTable.Rows.Count > 0)? Convert.ToInt64(this.DataTable.Rows[0]["BBalance"]):0;
*
* return bb;
* }*/
public double GetBBalanceAmount(int year, int storeId, int itemId, int month)
{
//CALENDAR:
this.FlushData();
double bb = 0;
EthiopianDate.EthiopianDate ethioDate = new EthiopianDate.EthiopianDate(year, month, 30);
//int bYear = ((month > 10) ? year : year - 1);
int bYear = ethioDate.StartOfFiscalYear.Year;
string query = String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}",
storeId, itemId, bYear);
this.LoadFromRawSql(query);
if (this.DataTable.Rows.Count > 0)
{
bb = ((this.DataTable.Rows[0]["PhysicalInventoryPrice"].ToString() != "")
? Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventoryPrice"])
: 0);
}
else
{
string queryString =
String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}", storeId,
itemId, year - 1);
this.LoadFromRawSql(queryString);
if (this.DataTable.Rows.Count > 0)
{
bb = ((this.DataTable.Rows[0]["PhysicalInventoryPrice"].ToString() != "")
? Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventoryPrice"])
: 0);
}
else if (((year + 8) > DateTime.Now.Year) || (month > 10 && (year + 8) == DateTime.Now.Year))
// to check if it is different year from current
{
double 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;
//}
double ReceivedAmount = rec.GetReceivedAmountTillMonth(itemId, storeId, month, year);
double AdjustedAmount = dis.GetAdjustedAmountTillMonth(itemId, storeId, month, year);
double IssuedAmount = iss.GetIssuedAmountTillMonth(itemId, storeId, month, year);
double LostAmount = dis.GetLossesAmountTillMonth(itemId, storeId, month, year);
cons = ((ReceivedAmount + AdjustedAmount - IssuedAmount) - LostAmount);
bb = cons;
}
}
if (bb < 0)
{
}
return(bb);
//this.FlushData();
//this.LoadFromRawSql(String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}", storeId, itemId, year-1));
//double bb = (this.DataTable.Rows.Count > 0) ? ((this.DataTable.Rows[0]["PhysicalInventoryPrice"].ToString() != "") ? Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventoryPrice"]) : 0) : 0;
//return bb;
}
/* public Int64 GetBBalance(int year, int storeId, int itemId)
{
this.FlushData();
this.LoadFromRawSql(String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}",storeId,itemId,year));
Int64 bb = ( this.DataTable.Rows.Count > 0)? Convert.ToInt64(this.DataTable.Rows[0]["BBalance"]):0;
return bb;
}*/
public double GetBBalanceAmount(int year, int storeId, int itemId, int month)
{
//CALENDAR:
this.FlushData();
double bb = 0;
EthiopianDate.EthiopianDate ethioDate = new EthiopianDate.EthiopianDate(year, month, 30);
//int bYear = ((month > 10) ? year : year - 1);
int bYear = ethioDate.StartOfFiscalYear.Year;
string query = String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}",
storeId, itemId, bYear);
this.LoadFromRawSql(query);
if (this.DataTable.Rows.Count > 0)
{
bb = ((this.DataTable.Rows[0]["PhysicalInventoryPrice"].ToString() != "")
? Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventoryPrice"])
: 0);
}
else
{
string queryString =
String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}", storeId,
itemId, year - 1);
this.LoadFromRawSql(queryString);
if (this.DataTable.Rows.Count > 0)
{
bb = ((this.DataTable.Rows[0]["PhysicalInventoryPrice"].ToString() != "")
? Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventoryPrice"])
: 0);
}
else if (((year + 8) > DateTime.Now.Year) || (month > 10 && (year + 8) == DateTime.Now.Year))
// to check if it is different year from current
{
double 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;
//}
double ReceivedAmount = rec.GetReceivedAmountTillMonth(itemId, storeId, month, year);
double AdjustedAmount = dis.GetAdjustedAmountTillMonth(itemId, storeId, month, year);
double IssuedAmount = iss.GetIssuedAmountTillMonth(itemId, storeId, month, year);
double LostAmount = dis.GetLossesAmountTillMonth(itemId, storeId, month, year);
cons = ((ReceivedAmount + AdjustedAmount - IssuedAmount) - LostAmount);
bb = cons;
}
}
if (bb < 0)
{
}
return bb;
//this.FlushData();
//this.LoadFromRawSql(String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}", storeId, itemId, year-1));
//double bb = (this.DataTable.Rows.Count > 0) ? ((this.DataTable.Rows[0]["PhysicalInventoryPrice"].ToString() != "") ? Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventoryPrice"]) : 0) : 0;
//return bb;
}
