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();
}
}
