/// <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);
}
private void LoadInventoryItems()
{
dtDate.Value = DateTime.Now;
DateTime dtCurent;
dtDate.CustomFormat = "MM/dd/yyyy";
dtCurent = ConvertDate.DateConverter(dtDate.Text);
int year = dtCurent.Year;
var itm = new Items();
DataTable dtItm;
switch (VisibilitySetting.HandleUnits)
{
case 1:
if (ckExclude == null || (!ckExclude.Checked))
dtItm = itm.ExcludeNeverReceivedItems(Convert.ToInt32(cboStores.EditValue),
Convert.ToInt32(lkCommodityTypes.EditValue));
else dtItm = itm.GetAllItems(1, Convert.ToInt32(lkCommodityTypes.EditValue));
PopulateItemList(dtItm, year);
dtDate.CustomFormat = "MMMM dd, yyyy";
break;
case 2:
if (ckExclude == null || (!ckExclude.Checked))
dtItm = itm.ExcludeNeverReceivedItemsForHandlingUnit(Convert.ToInt32(cboStores.EditValue),
Convert.ToInt32(lkCommodityTypes.EditValue));
else dtItm = itm.GetAllItems(1, Convert.ToInt32(lkCommodityTypes.EditValue));
PopulateItemList(dtItm, year);
dtDate.CustomFormat = "MMMM dd, yyyy";
break;
case 3:
if (ckExclude == null || (!ckExclude.Checked)) dtItm = itm.ExcludeNeverReceivedItemsForHandlingUnit(Convert.ToInt32(cboStores.EditValue), Convert.ToInt32(lkCommodityTypes.EditValue));
else dtItm = itm.GetAllItems(1, Convert.ToInt32(lkCommodityTypes.EditValue));
PopulateItemList(dtItm, year);
dtDate.CustomFormat = "MMMM dd, yyyy";
break;
}
}
private void radioGroup1_SelectedIndexChanged(object sender, EventArgs e)
{
DataTable dtItem = new DataTable();
Items itm = new Items();
selectedType = radioGroup1.EditValue.ToString();
if (selectedType == "Drug") dtItem = itm.GetAllItems(1);
else dtItem = itm.GetAllSupply();
PopulateCatTree(selectedType);
PopulateItemList(dtItem);
}
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 DataTable GetBelowMin(int storeId, int month, int year)
{
Items itm = new Items();
DataTable dtItem = itm.GetAllItems(1);
DataTable dt = new DataTable();
string[] cols = { "ID", "ItemName", "DosageForm", "Strength", "Unit", "StockCode" };
foreach (string st in cols)
{
dt.Columns.Add(st);
}
GeneralInfo pipline = new GeneralInfo();
pipline.LoadAll();
int min = pipline.Min;
int max = pipline.Max;
double eop = pipline.EOP;
Balance bal = new Balance();
foreach (DataRow dr in dtItem.Rows)
{
Int64 AMC = bal.CalculateAMC(Convert.ToInt32(dr["ID"]), storeId, month, year);
Int64 MinCon = AMC * min;
Int64 maxCon = AMC * max;
double eopCon = AMC * (eop + 0.25);
Int64 SOH = bal.GetSOH(Convert.ToInt32(dr["ID"]), storeId, month, year);
decimal MOS = (AMC != 0) ? (SOH / AMC) : 0;
Int64 reorder = (maxCon > SOH) ? maxCon - SOH : 0;
if (SOH > eopCon && (SOH <= MinCon))
{
object[] obb = { dr["ID"], dr["ItemName"], dr["DosageForm"], dr["Strength"], dr["Unit"], dr["StockCode"] };
dt.Rows.Add(obb);
}
//string status = (SOH <= eopCon && SOH > 0) ? "Near EOP" : ((SOH > maxCon) ? "Excess Stock" : ((SOH <= 0) ? "Stock Out" : "Normal"));
}
return dt;
}
private void cboStores_SelectedIndexChanged(object sender, EventArgs e)
{
if (cboStores.SelectedValue != null)
{
Items itm = new Items();
int storeId = Convert.ToInt32(cboStores.SelectedValue);
DataTable dtItem = new DataTable();
dtItem = ((ckExclude.Checked && ckExcNeverIssued.Checked) ? itm.GetReceivedNotIssuedItems(storeId,Convert.ToInt32(lkCommodityTypes.EditValue)) : ((ckExclude.Checked) ? itm.ExcludeNeverReceivedItems(storeId,Convert.ToInt32(lkCommodityTypes.EditValue)) : itm.GetAllItems(1,Convert.ToInt32(lkCommodityTypes.EditValue))));
PopulateItemList(dtItem);
lblState.Text = "All Items";
}
}
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 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 rdDrug_CheckedChanged(object sender, EventArgs e)
{
if (cboStores.SelectedValue != null && cboYear.SelectedItem != null)
{
Items itm = new Items();
int storeId = Convert.ToInt32(cboStores.SelectedValue);
DataTable dtItem = new DataTable();
dtItem = ((ckExclude.Checked) ? itm.ExcludeNeverReceivedItems(storeId,Convert.ToInt32(lkCommodityTypes.EditValue)) : itm.GetAllItems(1));
PopulateItemList(dtItem);
PopulateCatTree();
lblState.Text = "All Items";
}
}
/// <summary>
/// Handles the processing of balance
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnProcessBalance_Click(object sender, EventArgs e)
{
Items itm = new Items();
YearEnd yEnd = new YearEnd();
Balance bal = new Balance();
DataTable dtItm = itm.GetAllItems(1);
dtDate.Value = DateTime.Now;
DateTime dtCurent = Convert.ToDateTime(dtDate.Text);
foreach (DataRow dr in dtItm.Rows)
{
yEnd.AddNew();
yEnd.StoreID = Convert.ToInt32(cboStore.SelectedValue);
yEnd.ItemID = Convert.ToInt32(dr["ID"]);
yEnd.Year = Convert.ToInt32(cboYear.SelectedItem);
yEnd.Month = (Convert.ToInt32(cboYear.SelectedItem) == dtCurent.Year) ? dtCurent.Month : 10;
yEnd.BBalance = 0;
yEnd.EBalance = 0;
yEnd.PhysicalInventory = 0;
yEnd.Save();
}
}
private void cboStores_SelectedValueChanged(object sender, EventArgs e)
{
if (cboStores.SelectedValue != null)
{
Items itm = new Items();
DataTable dtItem = itm.GetAllItems(1);
PopulateItemList(dtItem);
lblState.Text = "All Items";
}
}
private void cboStatus_SelectedIndexChanged(object sender, EventArgs e)
{
if (cboStatus.SelectedItem != null)
{
Items itm = new Items();
DataTable dtItm = itm.GetAllItems(1);
// PopulateItemListByMonth(dtItm,Convert.ToInt32(cboMonth.SelectedValue),DateTime.Now.Year);
}
}
private void listAll_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
Items itm = new Items();
DataTable dtItm = itm.GetAllItems(1);
//groupList.Text = "All Items";
PopulateList(dtItm,lstDetail);
}
private void PopulateByProgram()
{
if (cboSubProgram.SelectedValue != null && cboStores.SelectedValue != null)
{
Items itm = new Items();
DataTable dtItem;
if (Convert.ToInt32(cboSubProgram.SelectedValue) > 0)
{
if (rdDrug.EditValue!=null)
dtItem = ((ckExclude.Checked) ? itm.ExcludeNeverReceivedItemsByProgram(Convert.ToInt32(cboSubProgram.SelectedValue), Convert.ToInt32(cboStores.SelectedValue)) : itm.GetItemsByProgram(Convert.ToInt32(cboSubProgram.SelectedValue)));
else
dtItem = ((ckExclude.Checked) ? itm.ExcludeNeverReceivedSuppliesByProgram(Convert.ToInt32(cboSubProgram.SelectedValue), Convert.ToInt32(cboStores.SelectedValue)) : itm.GetSupplyByProgram(Convert.ToInt32(cboSubProgram.SelectedValue)));
}
else
{
if (rdDrug.EditValue!=null)
dtItem = ((ckExclude.Checked) ? itm.ExcludeNeverReceivedItems(Convert.ToInt32(cboStores.SelectedValue),Convert.ToInt32(lkCommodityTypes.EditValue)) : itm.GetAllItems(1));
else
dtItem = ((ckExclude.Checked) ? itm.ExcludeNeverReceivedSupply(Convert.ToInt32(cboStores.SelectedValue)) : itm.GetAllSupply());
}
PopulateItemList(dtItem);
}
}
/// <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);
}
//SupplyListToIssue
public DataTable ItemListToIssue(int storeId, DateTime dtCurrent, string selectedType)
{
GeneralInfo pipline = new GeneralInfo();
DataTable dtList = new DataTable();
string[] str = { "No", "Stock Code", "FullItemName", "Unit", "SOH", "AMC", "Expired", "CategoryId", "SubCategoryID", "ID", "Status", "IsSelected" };
foreach (string s in str)
{
dtList.Columns.Add(s);
}
dtList.Columns[11].DataType = typeof(bool);
pipline.LoadAll();
int min = pipline.Min;
int max = pipline.Max;
double eop = pipline.EOP;
// int storeId = (cboStores.SelectedValue != null) ? Convert.ToInt32(cboStores.SelectedValue) : 1;
int count = 1;
Balance bal = new Balance();
Items itmB = new Items();
DataTable dtItem = ((selectedType == "Drug") ? itmB.GetAllItems(1) : itmB.GetAllSupply());
int curYear = dtCurrent.Year;
int curMonth = dtCurrent.Month;
foreach (DataRow dr in dtItem.Rows)
{
int yer = (curMonth < 11) ? curYear : curYear - 1;
Int64 AMC = bal.CalculateAMC(Convert.ToInt32(dr["ID"]), storeId, curMonth, curYear);
Int64 MinCon = AMC * min;
Int64 maxCon = AMC * max;
double eopCon = AMC * (eop + 0.25);
double beloweopCon = AMC * (eop - 0.25);
Int64 soh = bal.GetSOH(Convert.ToInt32(dr["ID"]), storeId, curMonth, curYear);
string status = (soh <= eopCon && soh > beloweopCon) ? "Near EOP" : ((soh > 0 && soh <= beloweopCon) ? "Below EOP" : ((soh > maxCon && maxCon != 0) ? "Excess Stock" : ((soh <= 0) ? "Stock Out" : "Normal")));//((SOH > beloweopCon && SOH <= MinCon) ? "Below Min" : ((SOH > maxCon && maxCon != 0) ? "Excess Stock" : ((SOH <= 0) ? "Stock Out" : "Normal"));
string itemName = dr["FullItemName"].ToString();
Int64 expAmount = itmB.GetExpiredQtyItemsByID(Convert.ToInt32(dr["ID"]), storeId);
object[] obj = { count.ToString(), dr["StockCode"].ToString(), itemName, dr["Unit"].ToString(), soh, AMC, expAmount, Convert.ToInt32(dr["CategoryId"]), Convert.ToInt32(dr["SubCategoryID"]), Convert.ToInt32(dr["ID"]), status, DBNull.Value };
dtList.Rows.Add(obj);
count++;
}
return dtList;
}
public DataTable OverStockedItems(int storeId, int year, int month, string selectedType, BackgroundWorker bw)
{
DataTable dtBal = new DataTable();
GeneralInfo pipline = new GeneralInfo();
Items itm = new Items();
Balance bal = new Balance();
IssueDoc iss = new IssueDoc();
ReceiveDoc rec = new ReceiveDoc();
string[] str = { "FullItemName", "SOH", "AMC", "MOS", "Min", "Max", "ExcessQty", "ExcessAmount", "CategoryId", "SubCategoryID", "ID" };
foreach (string s in str)
{
dtBal.Columns.Add(s);
}
pipline.LoadAll();
int min = pipline.Min;
int max = pipline.Max;
double eop = pipline.EOP;
DataTable dtItem = new DataTable();
dtItem = ((selectedType == "Drug") ? itm.GetAllItems(1) : itm.GetAllSupply());
int i = 1;
foreach (DataRow dr in dtItem.Rows)
{
string itemName = dr["FullItemName"].ToString();
int yer = (month < 11) ? year : year - 1;
Int64 AMC = bal.CalculateAMC(Convert.ToInt32(dr["ID"]), storeId, month, yer);
Int64 MinCon = AMC * min;
Int64 maxCon = AMC * max;
Int64 SOH = bal.GetSOH(Convert.ToInt32(dr["ID"]), storeId, month, yer);
if (SOH > maxCon)
{
decimal MOS = (AMC != 0) ? (Convert.ToDecimal(SOH) / Convert.ToDecimal(AMC)) : 0;
MOS = Decimal.Round(MOS, 1);
Int64 excessQty = SOH - maxCon;
double price = rec.GetLastReceivedCost(Convert.ToInt32(dr["ID"]), storeId);
double excessAmount = price * excessQty;
object[] obj = { itemName, SOH, AMC, MOS, MinCon, maxCon, excessQty, excessAmount, Convert.ToInt32(dr["CategoryId"]), Convert.ToInt32(dr["SubCategoryID"]), Convert.ToInt32(dr["ID"]) };
dtBal.Rows.Add(obj);
}
bw.ReportProgress(Convert.ToInt32((Convert.ToDouble(i++) / dtItem.Rows.Count) * 100));
}
return dtBal;
}
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);
}
}
public int CountBelowMin(int storeId, int month, int year)
{
Items itm = new Items();
DataTable dtItem = itm.GetAllItems(1);
GeneralInfo pipline = new GeneralInfo();
pipline.LoadAll();
int min = pipline.Min;
int max = pipline.Max;
double eop = pipline.EOP;
int count = 0;
Balance bal = new Balance();
if (storeId == 0)
{
count += (from DataRow dr in dtItem.Rows
let AMC = bal.CalculateAMCAll(Convert.ToInt32(dr["ID"]), month, year)
let MinCon = AMC * min
let maxCon = AMC * max
let eopCon = AMC * (eop + 0.25)
let SOH = bal.GetSOHAll(Convert.ToInt32(dr["ID"]), month, year)
let MOS = (AMC != 0) ? (SOH / AMC) : 0
let reorder = (maxCon > SOH) ? maxCon - SOH : 0
where SOH > eopCon && (SOH <= MinCon)
select MinCon).Count();
}
else
{
count += (from DataRow dr in dtItem.Rows
let AMC = bal.CalculateAMC(Convert.ToInt32(dr["ID"]), storeId, month, year)
let MinCon = AMC * min
let maxCon = AMC * max
let eopCon = AMC * (eop + 0.25)
let SOH = bal.GetSOH(Convert.ToInt32(dr["ID"]), storeId, month, year)
let MOS = (AMC != 0) ? (SOH / AMC) : 0
let reorder = (maxCon > SOH) ? maxCon - SOH : 0
where SOH > eopCon && (SOH <= MinCon)
select MinCon).Count();
}
return count;
}
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);
}
}
private void btnSearchByDate_Click(object sender, EventArgs e)
{
Items itm = new Items();
DataTable dtItm = new DataTable();
if (dtFrom.Value < dtTo.Value)
{
int storeId = Convert.ToInt32(cboStores.SelectedValue);
dtItm = ((ckExclude.Checked) ? itm.ExcludeNeverReceivedItems(storeId,Convert.ToInt32(lkCommodityTypes.EditValue)) : itm.GetAllItems(1));
PopulateItemListByDateRange(dtItm);
}
}