public frmTransferHistory() { InitializeComponent(); dt_Item = dalItem.Select(); tool.DoubleBuffered(dgvTrf, true); loadItemCategoryData(cmbTrfItemCat); loadLocationCategoryData(); }
private void loadItemNameList() { //show item based on keywords DataTable dt = dalItem.Select(); //remove repeating name in item_name DataTable distinctTable = dt.DefaultView.ToTable(true, "item_name"); //sort the data according item_name distinctTable.DefaultView.Sort = "item_name ASC"; cmbItemName.DataSource = distinctTable; cmbItemName.DisplayMember = "item_name"; cmbItemName.ValueMember = "item_name"; cmbItemName.SelectedIndex = -1; }
private void LoadMatList() { dgvMatList.DataSource = null; #region variable DataTable dt_MAT = NewMatlistTable(); DataRow row_dtMat; DataTable dt_ItemInfo = dalItem.Select(); float prepareQty = 0; string prePartCode = null; string partCode = null; bool isSamePlan = false; DataTable dt = dalMatPlan.Select(); DataTable dt_Stock = dalStock.Select(); //string sortBy = cmbSort.Text; #endregion DataView dv = dt.DefaultView; //dv.Sort = dalItem.ItemCat + " asc, " + "mat_code asc, " + dalMac.MacLocation + " asc, " + dalPlan.productionStartDate + " asc"; dv.Sort = dalMac.MacID + " asc, " + dalPlan.productionStartDate + " asc, " + dalPlan.partCode + " asc"; //dv.Sort = dalMac.MacLocation + " asc, " + dalMac.MacID + " asc, " + dalPlan.productionStartDate + " asc, " + dalPlan.partCode + " asc, " + "mat_code asc "; dt = dv.ToTable(); dt = specialDataSort(dt); dt_MaterialPlan = dt; #region sorting //if (sortBy.Equals(sortByFac)) //{ // DataView dv = dt.DefaultView; // //dv.Sort = dalMac.MacLocation+ " asc"; // //dv.Sort = dalMac.MacLocation + " asc, " + dalMac.MacID + " asc"; // dv.Sort = dalMac.MacLocation + " asc, " + dalItem.ItemCat + " asc, " + "mat_code asc, " + dalMac.MacID + " asc, " + dalPlan.productionStartDate + " asc"; // dt = dv.ToTable(); //} //else if (sortBy.Equals(sortByMac)) //{ // DataView dv = dt.DefaultView; // dv.Sort = dalMac.MacID + " asc"; // dt = dv.ToTable(); //} //else if (sortBy.Equals(sortByPlan)) //{ // DataView dv = dt.DefaultView; // dv.Sort = dalPlan.planID + " asc"; // dt = dv.ToTable(); //} //else if (sortBy.Equals(sortByPart)) //{ // DataView dv = dt.DefaultView; // dv.Sort = dalPlan.partCode + " asc"; // dt = dv.ToTable(); //} //else if (sortBy.Equals(sortByMat)) //{ // DataView dv = dt.DefaultView; // dv.Sort = dalItem.ItemCat + " asc, " + "mat_code asc, " + dalMac.MacLocation + " asc, " + dalPlan.productionStartDate + " asc"; // dt = dv.ToTable(); //} #endregion #region data proccessing foreach (DataRow row in dt.Rows) { bool active = Convert.ToBoolean(row[dalMatPlan.Active]); if (active) { partCode = row[dalPlan.partCode].ToString(); if (prePartCode == null || prePartCode == partCode) { if (prePartCode != null) { isSamePlan = true; } prePartCode = partCode; } else { row_dtMat = dt_MAT.NewRow(); dt_MAT.Rows.Add(row_dtMat); prePartCode = partCode; } row_dtMat = dt_MAT.NewRow(); if (!isSamePlan) { //row_dtMat[headerFac] = row[dalMac.MacLocation].ToString(); //row_dtMat[headerMac] = row[dalPlan.machineID]; //row_dtMat[headerPartCode] = partCode; //row_dtMat[headerPartName] = tool.getItemNameFromDataTable(dt_ItemInfo, partCode); //row_dtMat[headerStart] = row[dalPlan.productionStartDate]; //row_dtMat[headerEnd] = row[dalPlan.productionEndDate]; //row_dtMat[headerAbleProduceQty] = row[dalPlan.ableQty]; //row_dtMat[headerTargetQty] = row[dalPlan.targetQty]; } row_dtMat[headerFac] = row[dalMac.MacLocation].ToString(); row_dtMat[headerMac] = row[dalPlan.machineID]; row_dtMat[headerPartCode] = partCode; row_dtMat[headerPartName] = tool.getItemNameFromDataTable(dt_ItemInfo, partCode); row_dtMat[headerStart] = row[dalPlan.productionStartDate]; row_dtMat[headerEnd] = row[dalPlan.productionEndDate]; row_dtMat[headerAbleProduceQty] = row[dalPlan.ableQty]; row_dtMat[headerTargetQty] = row[dalPlan.targetQty]; row_dtMat[headerID] = row[dalPlan.planID].ToString(); row_dtMat[headerMatCode] = row[dalMatPlan.MatCode]; row_dtMat[headerMatUse] = row[dalMatPlan.PlanToUse]; dt_MAT.Rows.Add(row_dtMat); isSamePlan = false; } } #endregion #region set dgv data source if (dt_MAT.Rows.Count > 0) { dgvMatList.DataSource = dt_MAT; dgvMatListUIEdit(dgvMatList); dgvMatList.ClearSelection(); //addComboBoxToDGVCell(); } else { MessageBox.Show("No data found!"); } #endregion }
private void LoadStockAlert() { Cursor = Cursors.WaitCursor; #region indicate start and end date string start; string end; int yearStart = -1, yearEnd = -1; int monthStart = DateTime.Now.Month; int monthEnd = monthStart + 2; string Month_1 = monthStart.ToString(); string Month_2 = (monthStart + 1).ToString("MMMM"); string Month_3 = monthEnd.ToString("MMMM"); yearStart = DateTime.Now.Year; yearEnd = monthEnd < monthStart ? yearStart + 1 : yearStart; start = new DateTime(yearStart, monthStart, 1).ToString("yyyy/MM/dd"); end = new DateTime(yearEnd, monthEnd, DateTime.DaysInMonth(yearEnd, monthEnd)).ToString("yyyy/MM/dd"); #endregion #region Load data from database DataTable dt = NewStockAlertTable(); DataTable dt_Product_StockAlert = NewStockAlertTable(); string itemCust = text.SPP_BrandName; DataTable dt_Product = dalItemCust.SPPCustSearch(itemCust); DataTable dt_TrfHist = dalTrfHist.SPPItemToCustomerSearch(start, end, itemCust); DataTable dt_Item = dalItem.Select(); DataTable dt_SppCustomer = dalSPP.CustomerWithoutRemovedDataSelect(); //int index = 1; #endregion foreach (DataRow row in dt_Product.Rows) { string itemCode = row[dalSPP.ItemCode].ToString(); string itemName = row[dalSPP.ItemName].ToString(); int readyStock = int.TryParse(row[dalItem.ItemStock].ToString(), out readyStock) ? readyStock : 0; int qtyPerPacket = int.TryParse(row[dalSPP.QtyPerPacket].ToString(), out qtyPerPacket) ? qtyPerPacket : 0; int qtyPerBag = int.TryParse(row[dalSPP.QtyPerBag].ToString(), out qtyPerBag) ? qtyPerBag : 0; int maxLevel = int.TryParse(row[dalSPP.MaxLevel].ToString(), out maxLevel) ? maxLevel : 0; var deliveredQty = GetDeliveredQty(dt_SppCustomer, dt_TrfHist, monthStart, monthStart + 1, monthStart + 2, itemCode); int deliveredQty_1 = deliveredQty.Item1; int deliveredQty_2 = deliveredQty.Item2; int deliveredQty_3 = deliveredQty.Item3; int stillNeed_1 = maxLevel * qtyPerBag - deliveredQty_1 > 0 ? maxLevel * qtyPerBag - deliveredQty_1 : 0; int stillNeed_2 = maxLevel * qtyPerBag - deliveredQty_2 > 0 ? maxLevel * qtyPerBag - deliveredQty_2 : 0; int stillNeed_3 = maxLevel * qtyPerBag - deliveredQty_3 > 0 ? maxLevel * qtyPerBag - deliveredQty_3 : 0; int bal_1 = readyStock - stillNeed_1; int bal_2 = bal_1 - stillNeed_2; int bal_3 = bal_2 - stillNeed_3; DataRow alert_row = dt_Product_StockAlert.NewRow(); int divideBy = 1; if (cbInBagUnit.Checked) { divideBy = qtyPerBag; } //alert_row[header_Index] = index; alert_row[header_ItemCode] = itemCode; alert_row[header_ItemName] = itemName; alert_row[header_Stock] = readyStock / divideBy; alert_row[header_BalAfter1] = bal_1 / divideBy; alert_row[header_BalAfter2] = bal_2 / divideBy; alert_row[header_BalAfter3] = bal_3 / divideBy; dt_Product_StockAlert.Rows.Add(alert_row); //index++; } string selectedType = cmbType.Text; if (selectedType == Type_Part) { dt = LoadMatPartList(dt_Product_StockAlert); } else { dt = dt_Product_StockAlert; } dt.DefaultView.Sort = header_BalAfter3 + " ASC"; dt = dt.DefaultView.ToTable(); dt.AcceptChanges(); RearrangeIndex(dt); dgvStockAlert.DataSource = dt; DgvUIEdit(dgvStockAlert); dgvStockAlert.ClearSelection(); Cursor = Cursors.Arrow; }
private void LoadDeliverData() { #region pre setup dgvDeliver.DataSource = null; DataTable dt = NewDeliverTable(); DataRow dt_row; float total = 0; string preMatCode = null; string matCode = null; string facName = null; string from = null; string preFrom = null; string preFacName = null; string fromLocation = null; float preparingQty = 0; int index = 1; DataTable dt_MatPlan = dalMatPlan.Select(); DataTable dt_ItemInfo = dalItem.Select(); DataView dv = dt_MatPlan.DefaultView; dv.Sort = dalMac.MacLocation + " asc, " + dalItem.ItemCat + " asc, " + "mat_code asc"; dt_MatPlan = dv.ToTable(); #endregion #region data proccessing foreach (DataRow row in dt_MatPlan.Rows) { bool active = Convert.ToBoolean(row[dalMatPlan.Active]); from = row[dalMatPlan.MatFrom].ToString(); string partCode = row[dalPlan.partCode].ToString(); preparingQty = float.TryParse(row[dalMatPlan.Prepare].ToString(), out float i) ? Convert.ToSingle(row[dalMatPlan.Prepare].ToString()) : 0; if (active && !string.IsNullOrEmpty(from) && preparingQty > 0) { string cat = row[dalItem.ItemCat].ToString(); facName = row[dalMac.MacLocation].ToString(); matCode = row[dalMatPlan.MatCode].ToString(); if (preFacName == null) { preFacName = facName; } else if (preFacName == facName) { } else { dt_row = dt.NewRow(); dt.Rows.Add(dt_row); preFacName = facName; } dt_row = dt.NewRow(); dt_row[headerIndex] = index; dt_row[headerType] = cat; dt_row[headerMatCode] = matCode; dt_row[headerPlanID] = row[dalMatPlan.PlanID]; dt_row[headerItem] = tool.getItemNameFromDataTable(dt_ItemInfo, partCode) + "(" + partCode + ")"; dt_row[headerFrom] = from; dt_row[headerTo] = facName; dt_row[headerQty] = preparingQty; if (cat == text.Cat_RawMat || cat == text.Cat_Pigment || cat == text.Cat_MB) { string deliverBag = (int)preparingQty / 25 + " BAG"; if (preparingQty % 25 != 0) { deliverBag += " + " + preparingQty % 25 + "KG"; } dt_row[headerDeliverBag] = deliverBag; } dt.Rows.Add(dt_row); index++; } } #endregion #region set dgv data source if (dt.Rows.Count > 0) { dgvDeliver.DataSource = dt; dgvDeliverUIEdit(dgvDeliver); dgvDeliver.ClearSelection(); } else { MessageBox.Show("No data found!"); } #endregion }
private void LoadDeliveredData() { lblTotalBag.Text = 0 + " BAG(s) / " + 0 + " PCS " + text_Selected; if (Validation()) { #region Pre Setting frmLoading.ShowLoadingScreen(); DataGridView dgv = dgvList; DataTable dt_DeliveredReport = NewDeliveredTable(); //DataTable dt_ItemList; //DataTable dt_TrfHist; dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None; string dateType = cmbDateType.Text; //string itemSize = cmbItemSize.Text; //string itemType = cmbItemType.Text; #endregion #region load item list //dt_ItemList = dalItemCust.SPPCustSearch(itemType); #endregion #region get start and end date string start = dtpDateFrom.Value.ToString("yyyy/MM/dd"); string end = dtpDateTo.Value.ToString("yyyy/MM/dd"); #endregion #region Load data from database string itemCust = text.SPP_BrandName; DataTable dt_Product = dalItemCust.SPPCustSearchWithTypeAndSize(itemCust); if (cbSortByType.Checked) { dt_Product.DefaultView.Sort = dalSPP.TypeName + " ASC"; dt_Product = dt_Product.DefaultView.ToTable(); } else { dt_Product.DefaultView.Sort = dalSPP.SizeNumerator + " ASC"; dt_Product = dt_Product.DefaultView.ToTable(); } DataTable dt_Item = dalItem.Select(); DataTable dt_DOList = dalSPP.DOWithTrfInfoSelect(start, end); int index = 1; #endregion #region load product list foreach (DataRow row in dt_Product.Rows) { string itemCode = row[dalSPP.ItemCode].ToString(); string itemName = row[dalSPP.ItemName].ToString(); int readyStock = int.TryParse(row[dalItem.ItemStock].ToString(), out readyStock) ? readyStock : 0; int qtyPerPacket = int.TryParse(row[dalSPP.QtyPerPacket].ToString(), out qtyPerPacket) ? qtyPerPacket : 0; int qtyPerBag = int.TryParse(row[dalSPP.QtyPerBag].ToString(), out qtyPerBag) ? qtyPerBag : 0; int maxLevel = int.TryParse(row[dalSPP.MaxLevel].ToString(), out maxLevel) ? maxLevel : 0; int numerator = int.TryParse(row[dalSPP.SizeNumerator].ToString(), out numerator) ? numerator : 1; int denominator = int.TryParse(row[dalSPP.SizeDenominator].ToString(), out denominator) ? denominator : 1; string sizeUnit = row[dalSPP.SizeUnit].ToString().ToUpper(); string typeName = row[dalSPP.TypeName].ToString(); int pcsStock = readyStock; int bagStock = pcsStock / qtyPerBag; string sizeString = ""; int size = 1; if (denominator == 1) { size = numerator; sizeString = numerator + " " + sizeUnit; } else { size = numerator / denominator; sizeString = numerator + "/" + denominator + " " + sizeUnit; } DataRow newRow = dt_DeliveredReport.NewRow(); newRow[header_Index] = index++; newRow[header_ItemCode] = itemCode; newRow[header_ItemSize_Numerator] = numerator; newRow[header_ItemSize_Denominator] = denominator; newRow[header_SizeUnit] = sizeUnit; newRow[header_ItemType] = typeName; newRow[header_ItemString] = sizeString + " " + typeName; newRow[header_StdPacking] = qtyPerBag; bool DOFound = false; foreach (DataRow row_DO in dt_DOList.Rows) { string trfResult = row_DO[dalTrfHist.TrfResult].ToString(); if (trfResult == "Passed" && row_DO[dalSPP.ItemCode].ToString() == itemCode) { int custID = int.TryParse(row_DO[dalSPP.CustomerTableCode].ToString(), out custID) ? custID : -1; string shortName = row_DO[dalSPP.ShortName].ToString(); DateTime trfDate = DateTime.TryParse(row_DO[dalTrfHist.TrfDate].ToString(), out trfDate) ? trfDate : DateTime.MaxValue; int deliveredPcs = int.TryParse(row_DO[dalTrfHist.TrfQty].ToString(), out deliveredPcs) ? deliveredPcs : 0; if (dt_DeliveredReport.Rows.Count > 0) { bool custFound = false; foreach (DataRow delivered_Row in dt_DeliveredReport.Rows) { if (delivered_Row[header_ItemCode].ToString() == itemCode && delivered_Row[header_CustID].ToString() == custID.ToString()) { //get delivered pcs qty string dateHeaderName = GetDateHeaderName(trfDate); int oldDeliveredPcs = int.TryParse(delivered_Row[dateHeaderName + header_DeliveredPcs].ToString(), out oldDeliveredPcs) ? oldDeliveredPcs : 0; deliveredPcs += oldDeliveredPcs; int deliveredBag = deliveredPcs / qtyPerBag; string bagString = " BAGS ("; if (deliveredBag <= 1) { bagString = " BAG ("; } newRow[dateHeaderName + header_DeliveredPcs] = deliveredPcs; newRow[dateHeaderName + header_DeliveredBag] = deliveredBag; newRow[dateHeaderName] = deliveredBag + bagString + deliveredPcs + ")"; DOFound = true; custFound = true; break; } } if (!custFound) { newRow = dt_DeliveredReport.NewRow(); newRow[header_Index] = index++; newRow[header_ItemCode] = itemCode; newRow[header_ItemSize_Numerator] = numerator; newRow[header_ItemSize_Denominator] = denominator; newRow[header_SizeUnit] = sizeUnit; newRow[header_ItemString] = sizeString + " " + typeName; newRow[header_ItemType] = typeName; newRow[header_StdPacking] = qtyPerBag; newRow[header_CustID] = custID; newRow[header_CustShortName] = shortName; string dateHeaderName = GetDateHeaderName(trfDate); int deliveredBag = deliveredPcs / qtyPerBag; string bagString = " Bags ("; if (deliveredBag <= 1) { bagString = " Bag ("; } newRow[dateHeaderName + header_DeliveredPcs] = deliveredPcs; newRow[dateHeaderName + header_DeliveredBag] = deliveredBag; newRow[dateHeaderName] = deliveredBag + bagString + deliveredPcs + ")"; dt_DeliveredReport.Rows.Add(newRow); DOFound = true; } } else { // insert first data of the table newRow[header_CustID] = custID; newRow[header_CustShortName] = shortName; string dateHeaderName = GetDateHeaderName(trfDate); int deliveredBag = deliveredPcs / qtyPerBag; string bagString = " Bags ("; if (deliveredBag <= 1) { bagString = " Bag ("; } newRow[dateHeaderName + header_DeliveredPcs] = deliveredPcs; newRow[dateHeaderName + header_DeliveredBag] = deliveredBag; newRow[dateHeaderName] = deliveredBag + bagString + deliveredPcs + ")"; dt_DeliveredReport.Rows.Add(newRow); DOFound = true; } } } if (!DOFound) { dt_DeliveredReport.Rows.Add(newRow); } } #endregion #region print data //int index = 1; //int qtyPerBag = 0; //int deliveredPcsQty = 0; //int totalDeliveredPcsQty = 0; //bool dataPrinted = false; //bool itemFound = false; //TO-DO: #endregion if (cbSortByCustomer.Checked) { dt_DeliveredReport.DefaultView.Sort = header_CustShortName + " DESC"; dt_DeliveredReport = dt_DeliveredReport.DefaultView.ToTable(); } if (cbMergeItem.Checked) { dt_DeliveredReport = MergeSameItem(dt_DeliveredReport); } else if (cbMergeCustomer.Checked) { dt_DeliveredReport = MergeSameCustomer(dt_DeliveredReport); } else { dt_DeliveredReport = CalculateRowTotal(dt_DeliveredReport); } ReallocateIndex(dt_DeliveredReport); AddDividerEmptyRow(dt_DeliveredReport); dt_DeliveredReport = AddDividerEmptyRow(dt_DeliveredReport); dgv.DataSource = dt_DeliveredReport; DgvUIEdit(dgv); dgvList.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; dgvList.Columns.Cast <DataGridViewColumn>().ToList().ForEach(f => f.SortMode = DataGridViewColumnSortMode.NotSortable); dgv.ClearSelection(); frmLoading.CloseForm(); } }