/// <summary>
/// Open the AddItem Form with a supplied ID
/// </summary>
private void EditItems()
{
DataRow drv = gridView1.GetFocusedDataRow();
if (drv == null) return;
int itemId = Convert.ToInt32(drv["ID"]);
AddItem addItm = new AddItem(itemId, true);
MainWindow.ShowForms(addItm);
DataTable dtItem;
Items itm = new Items();
//if (txtItemName.Text != "")
//{
// string keyword = txtItemName.Text;
// dtItem = itm.GetItemByKeyword(keyword);
//}
//else if (selectedCat != 0)
//{
// dtItem = itm.GetItemsByCategory(selectedCat);
// toolStripButtonAddItems.Enabled = false;
// toolStripButtonEditItem.Enabled = false;
//}
//else if (selectedSubCat != 0)
//{
// dtItem = itm.GetItemsBySubCategory(selectedSubCat);
// toolStripButtonAddItems.Enabled = true;
// toolStripButtonEditItem.Enabled = false;
//}
//else
{
dtItem = itm.GetAllItems(1);
}
PopulateItemList(dtItem);
}
/// <summary>
/// Handles the changed event of the store combo bo
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cboStores_SelectedValueChanged(object sender, EventArgs e)
{
if (cboStores.EditValue != null)
{
int currentMonth = EthiopianDate.EthiopianDate.Now.Month;
var expDate = EthiopianDate.EthiopianDate.EthiopianToGregorian(String.Format("{0}/{1}/{2}", 1, currentMonth, Convert.ToInt32(cboYear.EditValue)));
Items itm = new Items();
DataTable dtItem = itm.GetExpiredItemsByBatch(Convert.ToInt32(cboStores.EditValue));
PopulateItemList(dtItem);
}
}
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 DataTable GetAllFreeFor(int itemID)
{
string query = String.Format("select (st.Prefix + ' ' + pl.Label) as PalletLocationName , * from PalletLocation pl join StorageType st on pl.StorageTypeID = st.ID join ItemPrefferedLocation ipl on ipl.PalletLocationID = pl.ID where ipl.ItemID = {0} and pl.PalletID is null", itemID);
PalletLocation pl = new PalletLocation();
pl.LoadFromRawSql(query);
if (pl.RowCount == 0)
{
Items itm = new Items();
itm.LoadByPrimaryKey(itemID);
return PalletLocation.GetAllFree(itm.StorageTypeID.ToString());
}
return pl.DataTable;
}
public 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>
/// Handles the changed event of the store combo bo
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cboStores_SelectedValueChanged(object sender, EventArgs e)
{
if(cboStores.EditValue != null)
{
int year = Convert.ToInt32(cmbYear.EditValue);
int reasonId;
reasonId = cboReasons.EditValue == null ? 0 : Convert.ToInt32(cboReasons.EditValue);
var itm = new Items();
// _selectedType = rdDrug.EditValue.ToString();
//DataTable dtItem = ((_selectedType == "Drug") ? itm.GetExpiredItemsByBatch(Convert.ToInt32(cboStores.EditValue)) : itm.GetExpiredSupplysByBatch(Convert.ToInt32(cboStores.EditValue)));
var dtItem = itm.GetAllExpiredItemsByBatch(Convert.ToInt32(cboStores.EditValue), year, reasonId ,Convert.ToInt32(lkCommodityTypes.EditValue));
// DataTable dtItem = itm.GetExpiredItemsByBatch(Convert.ToInt32(cboStores.EditValue), Convert.ToInt32(lkCommodityTypes.EditValue));
// DataTable dtItem = itm.GetExpiredItemsByBatch(Convert.ToInt32(cboStores.EditValue), Convert.ToInt32(lkCommodityTypes.EditValue), Convert.ToInt32(cboReasons.EditValue));
// DataTable dtItem = itm.GetExpiredItems(Convert.ToInt32(cboStores.EditValue), Convert.ToInt32(lkCommodityTypes.EditValue));
PopulateItemList(dtItem);
}
}
private void cboStores_EditValueChanged(object sender, EventArgs e)
{
var itm = new Items();
DataTable dtItem;
if (cboStores.EditValue == null) return;
if (lkCommodityTypes.EditValue == null)
{
dtItem = itm.GetNearlyExpiredItemsByBatchReport((int)cboStores.EditValue);
}
if (lkCommodityTypes.EditValue.ToString() == "0")
{
dtItem = itm.GetNearlyExpiredItemsByBatchReport((int)cboStores.EditValue);
}
else
{
dtItem = itm.GetNearlyExpiredItemsByBatch((int)cboStores.EditValue, (int)lkCommodityTypes.EditValue, _dtCurrent);
}
PopulateItemList(dtItem);
}
public static DataTable AllFiscalYears()
{
string query = @"select distinct year(date) as year from receivedoc
where month(date)<=10
union
select year(date)+1 as year from receivedoc
where month(date)>10
union
select distinct year(date) as year from issuedoc
where month(date)<=10
union
select year(date)+1 as year from issuedoc
where month(date)>10
union
select distinct year(date) as year from disposal
where month(date)<=10
union
select year(date)+1 as year from disposal
where month(date)>10";
BLL.Items itm = new Items();
itm.LoadFromRawSql(query);
return itm.DataTable;
}
/// <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;
}
}
public static void MergeItems(int itemToBeMergedIntoAnother, int itemUsedForMerging)
{
String[] tableNames ={"Disposal","IssueDoc","ReceiveDoc","YearEnd","ItemManufacturer","Items","ItemSupplier",
"ItemSupplyCategory","RRFDetail","OrderDetail","PickListDetail","ProductsCategory",
"ProgramProduct","DUsItemList","Exchange","InternalTransfer","Losses"};
for (int i = 0; i < tableNames.Length; i++)
{
BLL.Items itm = new Items();
string query = string.Format("Update {0} SET ItemID = {1} Where ItemID={2}", tableNames[i], itemUsedForMerging, itemToBeMergedIntoAnother);
itm.LoadFromRawSql(query);
}
}
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>
/// Returns the commodites by type
/// </summary>
/// <param name="commodityType">0 - All Items</param>
/// <returns></returns>
public static DataTable GetActiveItemsByCommodityType(int commodityType)
{
string query = "";
// let 0 mean all items
if (commodityType == 0)
{
query = string.Format("select v.Name as CommodityType,v.TypeID , *, " +
" CASE WHEN (SELECT COUNT(*) from ItemManufacturer i where i.ItemID = v.ID) > 0 then 1 else 0 end as HasManufacturer, IsSelected = cast( 0 as bit) " +
" from vwGetAllItems v where v.IsInHospitalList = 1 " +
" ORDER BY v.FullItemName");
}
else
{
query = string.Format("select v.Name as CommodityType, v.TypeID , *, " +
" CASE WHEN (SELECT COUNT(*) from ItemManufacturer i where i.ItemID = v.ID) > 0 then 1 else 0 end as HasManufacturer, IsSelected = cast( 0 as bit) " +
" from vwGetAllItems v " +
" where v.IsInHospitalList = 1 and v.TypeID = '{0}' ORDER BY v.FullItemName",
commodityType);
}
Items itms = new Items();
itms.LoadFromRawSql(query);
return itms.DataTable;
}
public static DataTable GetItems(string storageType)
{
Items itms = new Items();
itms.LoadItemsByStorageType(storageType);
return itms.DataTable;
}
/// <summary>
///
/// </summary>
/// <param name="rrfID"></param>
private void SendRRF(int rrfID)
{
// Do the webservice magic here.
GeneralInfo ginfo = new GeneralInfo();
ginfo.LoadAll();
chkCalculateInPacks.Checked = false;
ChooseGridView();
if (ginfo.IsColumnNull("FacilityID"))
{
XtraMessageBox.Show("The Facility ID Was not set, Please correct that and try again.");
return;
}
int facilityID = ginfo.FacilityID;
int startMonth = Convert.ToInt32(cboFromMonth.EditValue);
int endMonth = Convert.ToInt32(cboToMonth.EditValue); //startMonth + 1;
int fromYear = Convert.ToInt32(cboFromYear.EditValue);
int toYear = Convert.ToInt32(cboToYear.EditValue);
bool check;
RRFService.ServiceSoapClient sc = new RRFService.ServiceSoapClient();
try
{
check = sc.RRFExists(facilityID, toYear, startMonth, endMonth);
}
catch
{
XtraMessageBox.Show("There was a network Error, Please connect to the internet and try again.",
"Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (check)
{
check =
(XtraMessageBox.Show(
"Another Report has been submitted for the period you specified, Are you sure you would like to overwrite it?",
"Confirm Overwritting", MessageBoxButtons.YesNo, MessageBoxIcon.Question) !=
System.Windows.Forms.DialogResult.Yes);
}
if (!check)
{
RRFService.RrfSubmission rsubmission = new RrfSubmission
{
StartMonth = startMonth,
EndMonth = endMonth,
Year = toYear,
FacilityId = facilityID,
FacilityName = ginfo.HospitalName,
Items = new List<RRFItem>()
};
DataTable dtbl = ((DataView) gridItemChoiceView.DataSource).Table;
foreach (DataRow dr in dtbl.Rows)
{
BLL.Items item = new Items();
item.LoadByPrimaryKey(Convert.ToInt32(dr["ID"]));
if (!item.MappingID.HasValue)
{
continue;
}
RRFService.RRFItem itm = new RRFService.RRFItem()
{
ItemID = item.MappingID.Value // Convert.ToInt32(dr["ID"])
,
BBalance = Convert.ToInt32(dr["BeginingBalance"])
,
EBalance = Convert.ToInt32(dr["SOH"])
,
Consumption = Convert.ToInt32(dr["Issued"])
,
LossAdjustment = Convert.ToInt32(dr["LossAdj"])
,
Max = Convert.ToInt32(dr["Max"])
,
Received = Convert.ToInt32(dr["Received"])
,
Requested = Convert.ToInt32(dr["Quantity"])
};
rsubmission.Items.Add(itm);
}
sc = new RRFService.ServiceSoapClient();
try
{
rsubmission.ReportedBy = "";
if (sc.SubmitRRF(rsubmission))
{
XtraMessageBox.Show("The Request has been submitted to PFSA.", "Confirmation",
MessageBoxButtons.OK, MessageBoxIcon.Information);
RRF rrf = new RRF();
rrf.LoadByPrimaryKey(rrfID);
rrf.DateOfSubmission = DateTime.Now;
rrf.LastRRFStatus = CheckRRFStatus(rrf.ID);
rrf.Save();
}
}
catch (Exception ex)
{
}
}
}
/// <summary>
/// Handles Delete of an item
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void toolBtnDelete_Click(object sender, EventArgs e)
{
DataRow drv = gridView1.GetFocusedDataRow();
if (drv != null)
{
Items itm = new Items();
ProductsCategory proCat = new ProductsCategory();
int itemId = Convert.ToInt32(drv["ID"]);
if (!itm.HasTransactions(itemId))
{
if (XtraMessageBox.Show("Are You Sure, You want to delete this Transaction? You will not be able to restore this data.", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
proCat.GetCategoryByItem(itemId);
foreach (DataRowView drcat in proCat.DefaultView)
{
ProductsCategory cat = new ProductsCategory();
cat.LoadByPrimaryKey(Convert.ToInt32(drcat["ID"]));
cat.MarkAsDeleted();
cat.Save();
}
itm.LoadByPrimaryKey(itemId);
itm.MarkAsDeleted();
itm.Save();
XtraMessageBox.Show("Item Deleted!","Confirmation",MessageBoxButtons.OK,MessageBoxIcon.Information);
}
}
else
{
XtraMessageBox.Show("Unable to Delete, This Item has been Received or Issued.", "Unable to Delete", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
}
public long TotalQuantityLeftInAllBatches(int storeID)
{
string query =
string.Format(
"select SUM(QuantityLeft) Total from ReceiveDoc where itemid={0} and StoreID={1} group by ItemID",
this.ID, storeID);
BLL.Items temp = new Items();
temp.LoadFromRawSql(query);
if (temp.RowCount > 0)
{
return long.Parse(temp.GetColumn("Total").ToString());
}
return 0;
}
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;
}
private void ckExcNeverIssued_CheckedChanged(object sender, EventArgs e)
{
if (cboStores.SelectedValue != null && (cboYear.SelectedItem != null))
{
Items itm = new Items();
int storeId = (cboStores.SelectedValue != null) ? Convert.ToInt32(cboStores.SelectedValue) : 0;
DataTable dtItm = new DataTable();
dtItm = ((ckExclude.Checked && ckExcNeverIssued.Checked) ? itm.GetReceivedNotIssuedItems(storeId,Convert.ToInt32(lkCommodityTypes.EditValue)) : ((ckExclude.Checked) ? itm.ExcludeNeverReceivedItems(storeId, Convert.ToInt32(lkCommodityTypes.EditValue)) : itm.GetAllItems(1)));
PopulateItemList(dtItm);
}
}
private void PopulateByProgram()
{
if (cboSubProgram.SelectedValue != null && cboStores.SelectedValue != null)
{
Items itm = new Items();
DataTable dtItem = new DataTable();
if (Convert.ToInt32(cboSubProgram.SelectedValue) > 0)
{
dtItem = ((ckExclude.Checked) ? itm.ExcludeNeverReceivedItemsByProgram(Convert.ToInt32(cboSubProgram.SelectedValue), Convert.ToInt32(cboStores.SelectedValue)) : itm.GetItemsByProgram(Convert.ToInt32(cboSubProgram.SelectedValue)));
}
else
{
dtItem = (((ckExcNeverIssued.Checked && ckExclude.Checked) ? itm.GetReceivedNotIssuedItems(Convert.ToInt32(cboStores.SelectedValue),Convert.ToInt32(lkCommodityTypes.EditValue)) : (ckExclude.Checked) ? itm.ExcludeNeverReceivedItems(Convert.ToInt32(cboStores.SelectedValue), Convert.ToInt32(lkCommodityTypes.EditValue)) : itm.GetAllItems(1)));
}
PopulateItemList(dtItem);
}
}
private void cboIssuedTo_SelectedValueChanged(object sender, EventArgs e)
{
if (cboIssuedTo.SelectedValue != null)
{
DataTable dtItem = new DataTable();
Items itm = new Items();
int duId = Convert.ToInt32(cboIssuedTo.SelectedValue);
dtItem = itm.GetItemsByDU(duId);
PopulateItemListByDU(dtItem);
}
}
private void EditIssue_Load(object sender, EventArgs e)
{
Stores str = new Stores();
str.LoadAll();
cboStores.DataSource = str.DefaultView;
ReceivingUnits rUnit = new ReceivingUnits();
rUnit.GetActiveDispensaries();
cboReceivingUnit.DataSource = rUnit.DefaultView;
if (_tranId != 0)
{
IssueDoc iss = new IssueDoc();
Items itm = new Items();
iss.LoadByPrimaryKey(_tranId);
DataTable dtItm = itm.GetItemById(iss.ItemID);
string itemName = dtItm.Rows[0]["ItemName"].ToString() + " - " + dtItm.Rows[0]["DosageForm"].ToString() + " - " + dtItm.Rows[0]["Strength"].ToString();
txtRefNo.Text = iss.RefNo;
txtBatchNo.Text = iss.BatchNo;
try
{
txtPack.Text = iss.NoOfPack.ToString();
txtQtyPack.Text = iss.QtyPerPack.ToString();
txtPrice.Text = (iss.Cost * iss.QtyPerPack).ToString();
}
catch
{
txtPack.Text = "0";
txtQtyPack.Text = "0";
txtPrice.Text = (iss.Cost * 1).ToString();
}
txtQuantity.Text = iss.Quantity.ToString();
DateTime dtDate = Convert.ToDateTime(iss.Date.ToString("MM/dd/yyyy"));
txtDate.Text = dtDate.ToShortDateString();
//dtIssDate.Value = DateTime.Now;
//dtIssDate.CustomFormat = "MM/dd/yyyy";
//DateTime dtCurrent = Convert.ToDateTime(dtIssDate.Text);
//long tic = (DateTime.Now.Ticks - dtCurrent.Ticks);
//DateTime dtIssG = dtDate.AddTicks(tic);
//dtIssDate.Value = dtIssG;
cboStores.SelectedValue = iss.StoreId;
cboReceivingUnit.SelectedValue = iss.ReceivingUnitID;
txtItemName.Text = itemName;
txtReceivedBy.Text = iss.IssuedBy;
txtRemark.Text = iss.Remark;
}
}
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);
}
/// <summary>
/// Handles the text changed event of the txtItemName
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void txtItemName_TextChanged(object sender, EventArgs e)
{
DataTable dtItem;
Items itm = new Items();
if (txtItemName.Text != "")
{
string keyword = txtItemName.Text;
dtItem = itm.GetItemByKeyword(keyword);
}
else
{
dtItem = itm.GetAllItems(1);
}
PopulateItemList(dtItem);
}
/// <summary>
/// Populates Item list based on the selected category in the treeCategory
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void treeCategory_FocusedNodeChanged(object sender, DevExpress.XtraTreeList.FocusedNodeChangedEventArgs e)
{
DataRowView dr = (DataRowView)treeCategory.GetDataRecordByNode(treeCategory.Selection[0]);
Items itm = new Items();
DataTable dtItem;
//TODO: filter criteria;
string value = dr["ID"].ToString();
string type = value.Substring(0,1);
_catId = 0;
int categoryId = Convert.ToInt32(value.Substring(1));
switch (type)
{
case "C":
dtItem = itm.GetItemsByCategory(categoryId);
toolStripButtonAddItems.Enabled = false;
toolStripButtonEditItem.Enabled = false;
_selectedCat = categoryId;
break;
case "S":
dtItem = itm.GetItemsBySubCategory(categoryId);
toolStripButtonAddItems.Enabled = true;
_catId = categoryId;
toolStripButtonEditItem.Enabled = false;
_selectedSubCat = categoryId;
break;
default:
dtItem = itm.GetAllItem();
toolStripButtonAddItems.Enabled = false;
toolStripButtonEditItem.Enabled = false;
_selectedCat = 0;
break;
}
PopulateItemList(dtItem);
}
private void ProductTree_NodeMouseDoubleClick(object sender, TreeNodeMouseClickEventArgs e)
{
Items itm = new Items();
DataTable dtItem = new DataTable();
string value = ProductTree.SelectedNode.Name;
string type = value.Substring(0, 3);
int len = value.Length - 3;
int categoryId = Convert.ToInt32(value.Substring(3,len));
if (type == "cat")
{
dtItem = itm.GetItemsByCategory(categoryId);
lblState.Text = "All Items under " + ProductTree.SelectedNode.Text + " Category";
}
else if(type == "sub")
{
dtItem = itm.GetItemsBySubCategory(categoryId);
lblState.Text = "All Items under " + ProductTree.SelectedNode.Text + " Sub Category";
catID = categoryId;
}
else if (type == "sup")
{
dtItem = itm.GetSupplyByCategory(categoryId);
lblState.Text = "All Items under " + ProductTree.SelectedNode.Text + " Category";
catID = categoryId;
}
else if (type == "Als")
{
dtItem = itm.GetAllSupply();
lblState.Text = "All Items under " + ProductTree.SelectedNode.Text + " Category";
catID = categoryId;
}
else
{
dtItem = itm.GetAllItems(1);
lblState.Text = "All Items";
}
PopulateItemList(dtItem);
}
private void txtItemName_TextChanged(object sender, EventArgs e)
{
if (cboStores.SelectedValue != null && (cboYear.SelectedItem != null))
{
Items itm = new Items();
int storeId = (cboStores.SelectedValue != null) ? Convert.ToInt32(cboStores.SelectedValue) : 0;
DataTable dtItm = new DataTable();
if (txtItemName.Text != "")
{
dtItm = ((ckExclude.Checked && ckExcNeverIssued.Checked) ? itm.GetReceivedNotIssuedItemsByKeyword(storeId, txtItemName.Text) : ((ckExclude.Checked) ? itm.ExcludeNeverReceivedItemsByKeyword(storeId, txtItemName.Text) : itm.GetItemByKeywordInList(txtItemName.Text)));
}
else
{
dtItm = ((ckExclude.Checked && ckExcNeverIssued.Checked) ? itm.GetReceivedNotIssuedItems(storeId, Convert.ToInt32(lkCommodityTypes.EditValue)) : ((ckExclude.Checked) ? itm.ExcludeNeverReceivedItems(storeId, Convert.ToInt32(lkCommodityTypes.EditValue)) : itm.GetAllItems(1)));
}
PopulateItemList(dtItm);
}
}
public static DataTable AllYearsReport()
{
var itm = new Items();
itm.FlushData();
itm.LoadFromRawSql(@"SELECT Distinct CAST(YEAR(Date)AS nvarchar)as year from ReceiveDoc
union
select distinct CAST(YEAR(Date)AS nvarchar)as year from IssueDoc
union
select distinct CAST(YEAR(Date)AS nvarchar)as year from Disposal
order by year desc");
return itm.DataTable;
}
private void PopulateList()
{
_storeID = Convert.ToInt32(cboStores.EditValue);
Items itm = new Items();
_fromMonth = int.Parse(cboFromMonth.EditValue.ToString());
_toMonth = int.Parse(cboToMonth.EditValue.ToString());
_toYear = int.Parse(cboToYear.EditValue.ToString());
_fromYear = int.Parse(cboFromYear.EditValue.ToString());
var rrfHelper = new RRFItemHelper();
_tblRrf = rrfHelper.GetRRFReport(_storeID, _fromYear, _fromMonth, _toYear, _toMonth);
gridItemsChoice.DataSource = _tblRrf;
ChooseGridView();
}
private void PopulateList()
{
_storeID = Convert.ToInt32(cboStores.EditValue);
_programID = Convert.ToInt32(cboProgram.EditValue);
Items itm = new Items();
_fromMonth = int.Parse(cboFromMonth.EditValue.ToString());
_toMonth = int.Parse(cboToMonth.EditValue.ToString());
_toYear = int.Parse(cboToYear.EditValue.ToString());
_fromYear = int.Parse(cboFromYear.EditValue.ToString());
if (_standardRRF && VisibilitySetting.HandleUnits == 1)
{
tblRRF = itm.GetRRFReportWithOutUnit(_storeID, _fromYear, _fromMonth, _toYear, _toMonth);
}
else if (_standardRRF && VisibilitySetting.HandleUnits == 2)
{
tblRRF = itm.GetRRFReportByUnit(_storeID, _fromYear, _fromMonth, _toYear, _toMonth);
}
else if (_standardRRF && VisibilitySetting.HandleUnits == 3)
{
tblRRF = itm.GetRRFReportByUnit(_storeID, _fromYear, _fromMonth, _toYear, _toMonth);
}
else
{
tblRRF = itm.GetEmergencyRRFReport(_storeID, _fromYear, _fromMonth, _toYear, _toMonth);
}
gridItemsChoice.DataSource = tblRRF;
ChooseGridView();
}
/// <summary>
/// Loads the form data
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ManageItemsLoad(object sender, EventArgs e)
{
PopulateCatTree();
Items itm = new Items();
DataTable dtItem = itm.GetAllItem();
PopulateItemList(dtItem);
//lblState.Text = "All Items";
}
