private void LoadUserList(DataGridView dgv)
{
DataTable dt = dalUser.Select();
dgv.Rows.Clear();
foreach (DataRow user in dt.Rows)
{
int n = dgv.Rows.Add();
dgv.Rows[n].Cells[dalUser.UserID].Value = user[dalUser.UserID].ToString();
dgv.Rows[n].Cells[dalUser.Username].Value = user[dalUser.Username].ToString();
dgv.Rows[n].Cells[dalUser.Permission].Value = user[dalUser.Permission].ToString();
dgv.Rows[n].Cells[dalUser.AddedDate].Value = user[dalUser.AddedDate].ToString();
dgv.Rows[n].Cells[dalUser.UpdatedDate].Value = user[dalUser.UpdatedDate].ToString();
dgv.Rows[n].Cells[dalUser.UpdatedBy].Value = user[dalUser.UpdatedBy].ToString();
if (Convert.ToInt32(user[dalUser.AddedBy]) <= 0)
{
dgv.Rows[n].Cells[dalUser.AddedBy].Value = "ADMIN";
}
else
{
dgv.Rows[n].Cells[dalUser.AddedBy].Value = dalUser.getUsername(Convert.ToInt32(user[dalUser.AddedBy]));
}
if (int.TryParse(user[dalUser.UpdatedBy].ToString(), out int by))
{
dgv.Rows[n].Cells[dalUser.UpdatedBy].Value = dalUser.getUsername(Convert.ToInt32(user[dalUser.UpdatedBy]));
}
}
tool.listPaint(dgv);
}
private void loadForecastList()
{
//get keyword from text box
string keywords = cmbCust.Text;
int index = 1;
//check if the keywords has value or not
if (!string.IsNullOrEmpty(keywords))
{
DataTable dt = dalItemCust.custSearch(keywords);
dt.DefaultView.Sort = "item_name ASC";
dt = dt.DefaultView.ToTable();
if (dt.Rows.Count <= 0)
{
MessageBox.Show("no data under this record.");
}
dgvForecast.Rows.Clear();
foreach (DataRow item in dt.Rows)
{
int n = dgvForecast.Rows.Add();
dgvForecast.Rows[n].Cells["NO"].Value = index;
dgvForecast.Rows[n].Cells["item_code"].Value = item["item_code"].ToString();
dgvForecast.Rows[n].Cells["item_name"].Value = item["item_name"].ToString();
dgvForecast.Rows[n].Cells["forecast_one"].Value = item["forecast_one"] == DBNull.Value ? 0.ToString() : item["forecast_one"].ToString();
dgvForecast.Rows[n].Cells["forecast_two"].Value = item["forecast_two"] == DBNull.Value ? 0.ToString() : item["forecast_two"].ToString();
dgvForecast.Rows[n].Cells["forecast_three"].Value = item["forecast_three"] == DBNull.Value ? 0.ToString() : item["forecast_three"].ToString();
dgvForecast.Rows[n].Cells["forecast_four"].Value = item["forecast_four"] == DBNull.Value? 0.ToString() : item["forecast_four"].ToString();
dgvForecast.Rows[n].Cells["forecast_updtd_date"].Value = item["forecast_updated_date"].ToString();
index++;
if (int.TryParse(item["forecast_updated_by"].ToString(), out int test))
{
if (Convert.ToInt32(item["forecast_updated_by"]) <= 0)
{
dgvForecast.Rows[n].Cells["forecast_updtd_by"].Value = "ADMIN";
}
else
{
dgvForecast.Rows[n].Cells["forecast_updtd_by"].Value = dalUser.getUsername(Convert.ToInt32(item["forecast_updated_by"]));
}
}
string currentMonth = item["forecast_current_month"]?.ToString();
resetCurrentMonth(item["item_code"].ToString(), n, currentMonth);
}
}
else
{
dgvForecast.DataSource = null;
}
tool.listPaintGreyHeader(dgvForecast);
}
public frmOrderFollowUpAction(int ID)
{
InitializeComponent();
orderID = ID;
txtFromWho.Text = dalUser.getUsername(MainDashboard.USER_ID);
}
private void MainDashboard_FormClosed(object sender, FormClosedEventArgs e)
{
tool.historyRecord(text.LogOut, text.Success, DateTime.Now, USER_ID);
// Application.Exit();
frmLogIn frm = new frmLogIn(dalUser.getUsername(USER_ID));
frm.Show();
}
private void insertDailyDataToDGV(DataGridView dgv, DataTable dt)
{
if (dt.Rows.Count > 0)
{
createDailyDatagridview(dgv);
dgv.Rows.Clear();
if (cbSortByItem.Checked)
{
dt = SortByItemCodeAndRemoveFailedData(dt);
}
else
{
dt = SortByTrfDateAndRemoveFailedData(dt);
}
foreach (DataRow item in dt.Rows)
{
int n = dgv.Rows.Add();
dgv.Rows[n].Cells[dalTrfHist.TrfID].Value = item[dalTrfHist.TrfID].ToString();
dgv.Rows[n].Cells[dalTrfHist.TrfItemCode].Value = item[dalTrfHist.TrfItemCode].ToString();
dgv.Rows[n].Cells[dalTrfHist.TrfItemName].Value = item[dalTrfHist.TrfItemName].ToString();
dgv.Rows[n].Cells[dalTrfHist.TrfDate].Value = Convert.ToDateTime(item[dalTrfHist.TrfDate]).ToString("dd/MM/yyyy");
dgv.Rows[n].Cells[dalTrfHist.TrfFrom].Value = item[dalTrfHist.TrfFrom].ToString();
dgv.Rows[n].Cells[dalTrfHist.TrfTo].Value = item[dalTrfHist.TrfTo].ToString();
dgv.Rows[n].Cells[dalTrfHist.TrfQty].Value = item[dalTrfHist.TrfQty].ToString();
dgv.Rows[n].Cells[dalTrfHist.TrfUnit].Value = item[dalTrfHist.TrfUnit].ToString();
dgv.Rows[n].Cells[dalTrfHist.TrfNote].Value = item[dalTrfHist.TrfNote].ToString();
dgv.Rows[n].Cells[dalTrfHist.TrfAddedDate].Value = Convert.ToDateTime(item[dalTrfHist.TrfAddedDate]).ToString("dd/MM/yyyy");
dgv.Rows[n].Cells[dalTrfHist.TrfAddedBy].Value = dalUser.getUsername(Convert.ToInt32(item["trf_hist_added_by"]));;
}
}
else
{
int n = dgv.Rows.Add();
dgv.Rows[n].Cells[dalTrfHist.TrfItemName].Value = "No data exist";
}
}
private void loadActionHistory(int orderID)
{
DataTable dt = dalOrderAction.Select(orderID);
dt.DefaultView.Sort = "order_action_id DESC";
DataTable sortedDt = dt.DefaultView.ToTable();
dgvAction.Rows.Clear();
foreach (DataRow action in sortedDt.Rows)
{
int n = dgvAction.Rows.Add();
if (!Convert.ToBoolean(action["active"]))
{
dgvAction.Rows[n].DefaultCellStyle.ForeColor = Color.Red;
dgvAction.Rows[n].DefaultCellStyle.Font = new Font(this.Font, FontStyle.Strikeout);
}
else if (action["action"].ToString().Equals("RECEIVE"))
{
dgvAction.Rows[n].DefaultCellStyle.ForeColor = Color.FromArgb(52, 168, 83);
}
dgvAction.Rows[n].Cells["order_action_id"].Value = action["order_action_id"].ToString();
dgvAction.Rows[n].Cells["order_id"].Value = action["ord_id"].ToString();
dgvAction.Rows[n].Cells["trf_id"].Value = action["trf_id"] == DBNull.Value? "-1" : action["trf_id"].ToString();
dgvAction.Rows[n].Cells["added_date"].Value = action["added_date"].ToString();
dgvAction.Rows[n].Cells["added_by"].Value = action["added_by"].ToString();
dgvAction.Rows[n].Cells["action"].Value = action["action"].ToString();
dgvAction.Rows[n].Cells["action_detail"].Value = action["action_detail"].ToString();
dgvAction.Rows[n].Cells["action_from"].Value = action["action_from"].ToString();
dgvAction.Rows[n].Cells["action_to"].Value = action["action_to"].ToString();
dgvAction.Rows[n].Cells["note"].Value = action["note"].ToString();
if (Convert.ToInt32(action["added_by"]) <= 0)
{
dgvAction.Rows[n].Cells["added_by"].Value = "ADMIN";
}
else
{
dgvAction.Rows[n].Cells["added_by"].Value = dalUser.getUsername(Convert.ToInt32(action["added_by"]));
}
}
listPaint(dgvAction);
}
private void LoadPlanningActionHistory(DataTable dt)
{
DataTable dt_History = NewHistoryTable();
DataRow row_History;
foreach (DataRow row in dt.Rows)
{
row_History = dt_History.NewRow();
int userID = -1;
if (row[dalPlanningAction.ActionAddedBy] != null)
{
userID = Convert.ToInt32(row[dalPlanningAction.ActionAddedBy]);
}
string userName = dalUser.getUsername(userID);
row_History[headerActionID] = row[dalPlanningAction.ActionID];
row_History[headerPlanningID] = row[dalPlanningAction.PlanID];
row_History[headerAddedDate] = row[dalPlanningAction.ActionAddedDate];
row_History[headerAddedBy] = userName;
row_History[headerAction] = row[dalPlanningAction.Action];
row_History[headerDetail] = row[dalPlanningAction.ActionDetail];
row_History[headerFrom] = row[dalPlanningAction.ActionFrom];
row_History[headerTo] = row[dalPlanningAction.ActionTo];
row_History[headerNote] = row[dalPlanningAction.ActioNote];
dt_History.Rows.Add(row_History);
}
dgvActionHistory.DataSource = null;
if (dt_History.Rows.Count > 0)
{
dgvActionHistory.DataSource = dt_History;
dgvHistoryUIEdit(dgvActionHistory);
dgvActionHistory.ClearSelection();
}
}
private int transferRecord(string stockResult)
{
string locationFrom;
if (string.IsNullOrEmpty(cmbSubFrom.Text))
{
locationFrom = cmbFrom.Text;
}
else
{
locationFrom = cmbSubFrom.Text;
}
string locationTo = cmbTo.Text;
utrfHist.trf_hist_item_code = txtItemCode.Text;
utrfHist.trf_hist_from = locationFrom;
utrfHist.trf_hist_to = locationTo;
utrfHist.trf_hist_qty = Convert.ToSingle(txtQty.Text);
utrfHist.trf_hist_unit = Unit;
utrfHist.trf_hist_trf_date = Convert.ToDateTime(dtpTrfDate.Text);
utrfHist.trf_hist_note = "[" + dalUser.getUsername(MainDashboard.USER_ID) + "] Order: Received";
utrfHist.trf_hist_added_date = DateTime.Now;
utrfHist.trf_hist_added_by = MainDashboard.USER_ID;
utrfHist.trf_result = stockResult;
utrfHist.trf_hist_from_order = 1;
//Inserting Data into Database
bool success = daltrfHist.Insert(utrfHist);
if (!success)
{
//Failed to insert data
MessageBox.Show("Failed to add new transfer record");
}
return(daltrfHist.getIndexNo(utrfHist));
}
private void InsertAllDataToSheet(string path, string fileName)
{
DataGridView dgv = dgvTargetItem;
DataTable dt = (DataTable)dgv.DataSource;
Excel.Application excelApp = new Excel.Application
{
Visible = true
};
Workbook g_Workbook = excelApp.Workbooks.Open(
path,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
object misValue = Missing.Value;
if (dt.Rows.Count > 0)//if datagridview have data
{
Worksheet xlWorkSheet = null;
int count = g_Workbook.Worksheets.Count;
xlWorkSheet = g_Workbook.Worksheets.Add(Type.Missing,
g_Workbook.Worksheets[count], Type.Missing, Type.Missing);
xlWorkSheet.Name = "TARGET LIST";
xlWorkSheet.PageSetup.LeftHeader = "&\"Courier New\"&8 " + DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss");
xlWorkSheet.PageSetup.CenterHeader = "&\"Courier New\"&12 SPP TARGET LIST";
xlWorkSheet.PageSetup.RightHeader = "&\"Courier New\"&8 PG -&P";
xlWorkSheet.PageSetup.CenterFooter = "&\"Courier New\"&8 Printed By " + dalUser.getUsername(MainDashboard.USER_ID);
xlWorkSheet.PageSetup.CenterHorizontally = true;
xlWorkSheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
xlWorkSheet.PageSetup.Orientation = XlPageOrientation.xlPortrait;
xlWorkSheet.PageSetup.Zoom = false;
xlWorkSheet.PageSetup.FitToPagesWide = 1;
xlWorkSheet.PageSetup.FitToPagesTall = false;
double pointToCMRate = 0.035;
xlWorkSheet.PageSetup.TopMargin = 1.2 / pointToCMRate;
xlWorkSheet.PageSetup.BottomMargin = 1.2 / pointToCMRate;
xlWorkSheet.PageSetup.HeaderMargin = 0.6 / pointToCMRate;
xlWorkSheet.PageSetup.FooterMargin = 0.6 / pointToCMRate;
xlWorkSheet.PageSetup.LeftMargin = 0.7 / pointToCMRate;
xlWorkSheet.PageSetup.RightMargin = 0.7 / pointToCMRate;
xlWorkSheet.PageSetup.PrintTitleRows = "$1:$1";
// Paste clipboard results to worksheet range
copyDGVtoClipboard(dgvTargetItem);
xlWorkSheet.Select();
Range CR = (Range)xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
#region old format setting
Range tRange = xlWorkSheet.UsedRange;
tRange.Font.Size = 11;
tRange.RowHeight = 30;
tRange.VerticalAlignment = XlHAlign.xlHAlignCenter;
tRange.Font.Name = "Courier New";
tRange.BorderAround(Type.Missing, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
//tRange.Borders.LineStyle = XlLineStyle.xlContinuous;
//tRange.Borders.Weight = XlBorderWeight.xlThin;
//Range FirstRow = (Range)xlWorkSheet.Application.Rows[1, Type.Missing];
Range FirstRow = xlWorkSheet.get_Range("a1:e1").Cells;
FirstRow.WrapText = true;
FirstRow.Font.Size = 8;
FirstRow.HorizontalAlignment = XlHAlign.xlHAlignCenter;
FirstRow.VerticalAlignment = XlHAlign.xlHAlignCenter;
FirstRow.RowHeight = 20;
FirstRow.Interior.Color = Color.WhiteSmoke;
FirstRow.Borders.LineStyle = XlLineStyle.xlContinuous;
FirstRow.Borders.Weight = XlBorderWeight.xlThin;
//tRange.EntireColumn.AutoFit();
//DataTable dt = (DataTable)dgv.DataSource;
int TypeIndex = dgv.Columns[header_Type].Index;
int SizeIndex = dgv.Columns[header_Size].Index;
int UnitIndex = dgv.Columns[header_Unit].Index;
int CodeIndex = dgv.Columns[header_Code].Index;
int TargetIndex = dgv.Columns[header_Target_Qty].Index;
xlWorkSheet.Cells[1, SizeIndex + 1].ColumnWidth = 6;
xlWorkSheet.Cells[1, UnitIndex + 1].ColumnWidth = 6;
xlWorkSheet.Cells[1, TypeIndex + 1].ColumnWidth = 25;
xlWorkSheet.Cells[1, CodeIndex + 1].ColumnWidth = 25;
xlWorkSheet.Cells[1, TargetIndex + 1].ColumnWidth = 15;
for (int j = 0; j <= dt.Rows.Count - 1; j++)
{
Range rangeSize = (Range)xlWorkSheet.Cells[j + 2, SizeIndex + 1];
Range rangeUnit = (Range)xlWorkSheet.Cells[j + 2, UnitIndex + 1];
Range rangeType = (Range)xlWorkSheet.Cells[j + 2, TypeIndex + 1];
Range rangeTarget = (Range)xlWorkSheet.Cells[j + 2, TargetIndex + 1];
rangeSize.HorizontalAlignment = XlHAlign.xlHAlignRight;
rangeUnit.HorizontalAlignment = XlHAlign.xlHAlignLeft;
rangeTarget.HorizontalAlignment = XlHAlign.xlHAlignCenter;
System.Drawing.Color color = System.Drawing.Color.Black;
rangeType.Borders[XlBordersIndex.xlEdgeRight].Color = color;
rangeType.Borders[XlBordersIndex.xlEdgeLeft].Color = color;
rangeTarget.Borders[XlBordersIndex.xlEdgeRight].Color = color;
rangeTarget.Borders[XlBordersIndex.xlEdgeLeft].Color = color;
}
#endregion
releaseObject(xlWorkSheet);
Clipboard.Clear();
dgvTargetItem.ClearSelection();
}
dgv = dgvMatPrepareList;
dt = (DataTable)dgv.DataSource;
MoveMaterialPrepareDataToSheet(g_Workbook);
g_Workbook.Worksheets.Item[1].Delete();
g_Workbook.Save();
releaseObject(g_Workbook);
//frmLoading.CloseForm();
Cursor = Cursors.Arrow; // change cursor to normal type
}
private void btnExcel_Click(object sender, EventArgs e)
{
try
{
Cursor = Cursors.WaitCursor; // change cursor to hourglass type
SaveFileDialog sfd = new SaveFileDialog();
DataGridView dgv;
string path = @"D:\StockAssistant\Document\Checklist";
Directory.CreateDirectory(path);
sfd.InitialDirectory = path;
sfd.Filter = "Excel Documents (*.xls)|*.xls";
sfd.FileName = setFileName();
if (sfd.ShowDialog() == DialogResult.OK)
{
Cursor = Cursors.WaitCursor; // change cursor to hourglass type
tool.historyRecord(text.Excel, text.getExcelString(sfd.FileName), DateTime.Now, MainDashboard.USER_ID);
if (switchToDeliverPage)
{
dgv = dgvDeliver;
}
else
{
dgv = dgvTransfer;
}
dgv.Focus();
// Copy DataGridView results to clipboard
copyAlltoClipboard(dgv);
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application xlexcel = new Microsoft.Office.Interop.Excel.Application();
xlexcel.PrintCommunication = false;
xlexcel.ScreenUpdating = false;
xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts
Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue);
xlexcel.Calculation = XlCalculation.xlCalculationManual;
Worksheet xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1);
if (switchToDeliverPage)
{
xlWorkSheet.Name = "DELIVERY CHECKLIST";
xlWorkSheet.PageSetup.CenterHeader = "&\"Calibri\"&16 DELIVERY CHECKLIST";
xlWorkSheet.PageSetup.LeftFooter = "&\"Calibri\"&11 " + "DELIVERED BY\n DATE";
xlWorkSheet.PageSetup.CenterFooter = "&\"Calibri\"&11 " + "CHECKED BY\n DATE";
}
else
{
xlWorkSheet.Name = "TRANSFER CHECKLIST";
xlWorkSheet.PageSetup.CenterHeader = "&\"Calibri\"&16 TRANSFER CHECKLIST";
xlWorkSheet.PageSetup.LeftFooter = "&\"Calibri\"&11 " + "APPROVED BY\n DATE";
//xlWorkSheet.PageSetup.CenterFooter = "&\"Calibri\"&11 " + "CHECKED\n DATE";
}
#region Save data to Sheet
//Header and Footer setup
xlWorkSheet.PageSetup.LeftHeader = "&\"Calibri\"&8 (" + dalUser.getUsername(MainDashboard.USER_ID) + ") " + DateTime.Now;
xlWorkSheet.PageSetup.RightHeader = "&\"Calibri\"&8 PG -&P";
//xlWorkSheet.PageSetup.CenterFooter = "&\"Calibri\"&8 Printed By " + dalUser.getUsername(MainDashboard.USER_ID) + " at " + DateTime.Now;
//Page setup
xlWorkSheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
xlWorkSheet.PageSetup.Orientation = XlPageOrientation.xlPortrait;
xlWorkSheet.PageSetup.Zoom = false;
xlWorkSheet.PageSetup.CenterHorizontally = true;
xlWorkSheet.PageSetup.FitToPagesWide = 1;
xlWorkSheet.PageSetup.FitToPagesTall = false;
double pointToCMRate = 0.035;
xlWorkSheet.PageSetup.TopMargin = 1.8 / pointToCMRate;
xlWorkSheet.PageSetup.BottomMargin = 4.0 / pointToCMRate;
xlWorkSheet.PageSetup.HeaderMargin = 1.0 / pointToCMRate;
xlWorkSheet.PageSetup.FooterMargin = 2.0 / pointToCMRate;
xlWorkSheet.PageSetup.LeftMargin = 1 / pointToCMRate;
xlWorkSheet.PageSetup.RightMargin = 1 / pointToCMRate;
xlWorkSheet.PageSetup.PrintTitleRows = "$1:$1";
xlexcel.PrintCommunication = true;
xlexcel.Calculation = XlCalculation.xlCalculationAutomatic;
// Paste clipboard results to worksheet range
xlWorkSheet.Select();
Range CR = (Range)xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
//content edit
Range tRange = xlWorkSheet.UsedRange;
//tRange.Borders.LineStyle = XlLineStyle.;
//tRange.Borders.Weight = XlBorderWeight.xlThin;
tRange.Font.Size = 12;
tRange.Font.Name = "Calibri";
tRange.EntireColumn.AutoFit();
tRange.EntireRow.AutoFit();
tRange.Rows[1].interior.color = Color.FromArgb(237, 237, 237);
int iTotalColumns = xlWorkSheet.UsedRange.Columns.Count;
int iTotalRows = xlWorkSheet.UsedRange.Rows.Count;
char letter = 'a';
for (int i = 0; i < iTotalColumns; i++)
{
Range column = xlWorkSheet.get_Range(letter + "1:" + letter + iTotalRows).Cells;
column.BorderAround(Type.Missing, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Type.Missing);
letter++;
}
#endregion
if (true)//cmbSubType.Text.Equals("PMMA")
{
for (int i = 0; i <= dgv.RowCount - 1; i++)
{
for (int j = 0; j <= dgv.ColumnCount - 1; j++)
{
Range range = (Range)xlWorkSheet.Cells[i + 2, j + 1];
//range.Rows.RowHeight = 30;
range.Font.Color = ColorTranslator.ToOle(dgv.Rows[i].Cells[j].InheritedStyle.ForeColor);
if (switchToDeliverPage)
{
if (j == 9)
{
range.Cells.Value = "";
}
if (j == 6)
{
//range.Cells.Value = "";
range.Columns.ColumnWidth = 20;
}
if (j == 0 || j == 1 || j == 2 || j == 3 || j == 5 || j == 7)
{
range.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.Cells.VerticalAlignment = XlVAlign.xlVAlignCenter;
}
else
{
range.Cells.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range.Cells.VerticalAlignment = XlVAlign.xlVAlignCenter;
}
}
else
{
if (j == 2)
{
range.Columns.ColumnWidth = 20;
}
if (j == 0 || j == 3 || j == 4 || j == 5 || j == 6)
{
range.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.Cells.VerticalAlignment = XlVAlign.xlVAlignCenter;
}
else
{
range.Cells.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range.Cells.VerticalAlignment = XlVAlign.xlVAlignCenter;
}
}
if (dgv.Rows[i].Cells[j].InheritedStyle.BackColor == Color.White)
{
range.Rows.RowHeight = 40;
}
else if (dgv.Rows[i].Cells[j].InheritedStyle.BackColor == Color.DimGray)
{
range.Rows.RowHeight = 2;
range.Interior.Color = Color.DimGray;
}
else
{
if (j != 9)
{
range.Interior.Color = ColorTranslator.ToOle(dgv.Rows[i].Cells[j].InheritedStyle.BackColor);
}
else
{
//range.Interior.Color = Color.White;
}
range.Rows.RowHeight = 40;
}
}
}
}
//Save the excel file under the captured location from the SaveFileDialog
xlWorkBook.SaveAs(sfd.FileName, XlFileFormat.xlWorkbookNormal,
misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlexcel.DisplayAlerts = true;
xlWorkBook.Close(true, misValue, misValue);
xlexcel.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlexcel);
// Clear Clipboard and DataGridView selection
Clipboard.Clear();
dgv.ClearSelection();
// Open the newly saved excel file
if (File.Exists(sfd.FileName))
{
System.Diagnostics.Process.Start(sfd.FileName);
}
}
Cursor = Cursors.Arrow; // change cursor to normal type
}
catch (Exception ex)
{
tool.saveToTextAndMessageToUser(ex);
}
finally
{
if (switchToDeliverPage && !itemChecking)
{
dgvDeliver.Columns[headerCheck].Visible = false;
dgvDeliver.Columns[headerReceivedBy].Visible = false;
}
Cursor = Cursors.Arrow; // change cursor to normal type
}
}
private void loadForecastList()
{
Cursor = Cursors.WaitCursor;
btnSearch.Enabled = false;
frmLoading.ShowLoadingScreen();
cbEditMode.Checked = false;
dgvForecast.DataSource = null;
string keywords = cmbCustomer.Text;
CalTotalMonth();
//MessageBox.Show(getYear("NOVEMBER 2019"));
//check if the keywords has value or not
if (!string.IsNullOrEmpty(keywords))
{
string itemNameKeyword = cmbPartName.Text;
string itemCodeKeyword = cmbPartCode.Text;
DataTable dt_Data = NewForecastTable();
DataRow dt_Row;
DataTable dt = dalItemCust.custSearch(keywords);
DataTable dt_ItemForecast = dalItemForecast.Select(tool.getCustID(keywords).ToString());
int index = 1;
dt.DefaultView.Sort = "item_name ASC";
dt = dt.DefaultView.ToTable();
foreach (DataRow row in dt.Rows)
{
string itemName = row[dalItem.ItemName].ToString();
string itemCode = row[dalItem.ItemCode].ToString();
bool itemMatch = itemName.Contains(itemNameKeyword.ToUpper());
itemMatch = itemMatch || string.IsNullOrEmpty(itemNameKeyword);
itemMatch = itemMatch && (itemCodeKeyword.Equals(itemCode, StringComparison.InvariantCulture) || string.IsNullOrEmpty(itemCodeKeyword));
if (itemMatch)
{
dt_Row = dt_Data.NewRow();
dt_Row[headerIndex] = index;
dt_Row[headerPartCode] = itemCode;
dt_Row[headerPartName] = itemName;
for (int i = 0; i < forecastMonthQty; i++)
{
//get month and year
//MessageBox.Show(getYear(dt_Data.Columns[3 + i].ColumnName));
string headerText = dt_Data.Columns[3 + i].ColumnName;
int year = Convert.ToInt32(getYear(headerText));
int month = DateTime.Parse("1." + getMonthName(headerText) + " 2008").Month;
DateTime date = DateTime.MaxValue;
//search data
DataRow data = tool.getItemForecastDataRow(dt_ItemForecast, itemCode, year, month);
if (data != null)
{
dt_Row[3 + i] = data[dalItemForecast.ForecastQty];
if (date == DateTime.MaxValue || date < Convert.ToDateTime(data[dalItemForecast.UpdatedDate]))
{
date = Convert.ToDateTime(data[dalItemForecast.UpdatedDate]);
dt_Row[headerUpdatedDate] = data[dalItemForecast.UpdatedDate];
int updatedID = int.TryParse(data[dalItemForecast.UpdatedBy].ToString(), out updatedID) ? updatedID : 0;
if (updatedID <= 0)
{
dt_Row[headerUpdatedBy] = "ADMIN";
}
else
{
dt_Row[headerUpdatedBy] = dalUser.getUsername(updatedID);
}
//dt_Row[headerUpdatedBy] = data[dalItemForecast.UpdatedBy];
}
}
else
{
dt_Row[3 + i] = 0;
}
}
dt_Data.Rows.Add(dt_Row);
index++;
}
}
if (dt_Data.Rows.Count > 0)
{
dgvForecast.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None;
dgvForecast.ColumnHeadersVisible = false;
dgvForecast.DataSource = dt_Data;
dgvForecastUIEdit(dgvForecast);
dgvForecast.ColumnHeadersVisible = true;
dgvForecast.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;
//dgvForecast.AutoResizeColumns();
//dgvForecast.Columns[headerUpdatedBy].Width = 500;
dgvForecast.ClearSelection();
}
}
frmLoading.CloseForm();
btnSearch.Enabled = true;
Cursor = Cursors.Arrow;
}
private void InsertAllDataToSheet(string path, string fileName)
{
DataGridView dgv = dgvCommon;
DataTable dt = (DataTable)dgv.DataSource;
Excel.Application excelApp = new Excel.Application
{
Visible = true
};
Workbook g_Workbook = excelApp.Workbooks.Open(
path,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
object misValue = Missing.Value;
if (dt.Rows.Count > 0)//if datagridview have data
{
Worksheet xlWorkSheet = null;
int count = g_Workbook.Worksheets.Count;
xlWorkSheet = g_Workbook.Worksheets.Add(Type.Missing,
g_Workbook.Worksheets[count], Type.Missing, Type.Missing);
xlWorkSheet.Name = "COMMON PART";
xlWorkSheet.PageSetup.LeftHeader = "&\"Courier New\"&8 " + DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss");
xlWorkSheet.PageSetup.CenterHeader = "&\"Courier New\"&12 SPP INVENTORY REPORT: COMMON PART";
xlWorkSheet.PageSetup.RightHeader = "&\"Courier New\"&8 PG -&P";
xlWorkSheet.PageSetup.CenterFooter = "&\"Courier New\"&8 Printed By " + dalUser.getUsername(MainDashboard.USER_ID);
xlWorkSheet.PageSetup.CenterHorizontally = true;
xlWorkSheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
xlWorkSheet.PageSetup.Orientation = XlPageOrientation.xlPortrait;
xlWorkSheet.PageSetup.Zoom = false;
xlWorkSheet.PageSetup.FitToPagesWide = 1;
xlWorkSheet.PageSetup.FitToPagesTall = false;
double pointToCMRate = 0.035;
xlWorkSheet.PageSetup.TopMargin = 1.2 / pointToCMRate;
xlWorkSheet.PageSetup.BottomMargin = 1.2 / pointToCMRate;
xlWorkSheet.PageSetup.HeaderMargin = 0.6 / pointToCMRate;
xlWorkSheet.PageSetup.FooterMargin = 0.6 / pointToCMRate;
xlWorkSheet.PageSetup.LeftMargin = 0.7 / pointToCMRate;
xlWorkSheet.PageSetup.RightMargin = 0.7 / pointToCMRate;
xlWorkSheet.PageSetup.PrintTitleRows = "$1:$1";
// Paste clipboard results to worksheet range
copyDGVtoClipboard(dgvCommon);
xlWorkSheet.Select();
Range CR = (Range)xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
#region old format setting
Range tRange = xlWorkSheet.UsedRange;
tRange.Font.Size = 11;
tRange.RowHeight = 30;
tRange.VerticalAlignment = XlHAlign.xlHAlignCenter;
tRange.Font.Name = "Courier New";
tRange.BorderAround(Type.Missing, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
//tRange.Borders.LineStyle = XlLineStyle.xlContinuous;
//tRange.Borders.Weight = XlBorderWeight.xlThin;
//Range FirstRow = (Range)xlWorkSheet.Application.Rows[1, Type.Missing];
Range FirstRow = xlWorkSheet.get_Range("a1:d1").Cells;
FirstRow.WrapText = true;
FirstRow.Font.Size = 8;
FirstRow.HorizontalAlignment = XlHAlign.xlHAlignCenter;
FirstRow.VerticalAlignment = XlHAlign.xlHAlignCenter;
FirstRow.RowHeight = 20;
FirstRow.Interior.Color = Color.WhiteSmoke;
FirstRow.Borders.LineStyle = XlLineStyle.xlContinuous;
FirstRow.Borders.Weight = XlBorderWeight.xlThin;
//tRange.EntireColumn.AutoFit();
//DataTable dt = (DataTable)dgv.DataSource;
int TypeIndex = dgv.Columns["TYPE"].Index;
int SizeIndex = dgv.Columns["SIZE"].Index;
int UnitIndex = dgv.Columns["UNIT"].Index;
int StockIndex = dgv.Columns["STOCK(PCS)"].Index;
xlWorkSheet.Cells[1, TypeIndex + 1].ColumnWidth = 25;
xlWorkSheet.Cells[1, SizeIndex + 1].ColumnWidth = 6;
xlWorkSheet.Cells[1, UnitIndex + 1].ColumnWidth = 6;
xlWorkSheet.Cells[1, StockIndex + 1].ColumnWidth = 25;
//xlWorkSheet.Cells[1, StdIndex + 1].Font.Italic = true;
//rangeForecast1.Font.Italic = true;
//DataTable dt_Source = (DataTable)dgv.DataSource;
for (int j = 0; j <= dt.Rows.Count - 1; j++)
{
Range rangeStock = (Range)xlWorkSheet.Cells[j + 2, StockIndex + 1];
Range rangeType = (Range)xlWorkSheet.Cells[j + 2, TypeIndex + 1];
rangeStock.HorizontalAlignment = XlHAlign.xlHAlignCenter;
rangeType.HorizontalAlignment = XlHAlign.xlHAlignCenter;
System.Drawing.Color color = System.Drawing.Color.Black;
rangeStock.Borders[XlBordersIndex.xlEdgeRight].Color = color;
rangeStock.Borders[XlBordersIndex.xlEdgeLeft].Color = color;
rangeType.Borders[XlBordersIndex.xlEdgeRight].Color = color;
rangeType.Borders[XlBordersIndex.xlEdgeLeft].Color = color;
}
#endregion
releaseObject(xlWorkSheet);
Clipboard.Clear();
dgvCommon.ClearSelection();
}
dgv = dgvUnique;
dt = (DataTable)dgv.DataSource;
#region divide by type
DataTable dt_type = dt.Clone();
string previousType = null;
string currentType = null;
foreach (DataRow row in dt.Rows)
{
//get type. if previous type is null, set previous = current type
currentType = row["TYPE"].ToString();
if (string.IsNullOrEmpty(currentType))
{
currentType = previousType;
}
if (previousType == null || previousType == currentType)
{
previousType = currentType;
//move data to new table
dt_type.ImportRow(row);
}
else if (previousType != currentType)
{
DataRow lastRow = dt_type.Rows[dt_type.Rows.Count - 1];
if (string.IsNullOrEmpty(lastRow["CODE"].ToString()))
{
dt_type.Rows.RemoveAt(dt_type.Rows.Count - 1);
}
dgvUnique.DataSource = dt_type;
MoveUniqueDataToSheet(g_Workbook, previousType);
previousType = currentType;
dt_type = dt.Clone();
if (!string.IsNullOrEmpty(row["TYPE"].ToString()))
{
dt_type.ImportRow(row);
}
}
}
if (dt_type.Rows.Count > 0)
{
dgvUnique.DataSource = dt_type;
MoveUniqueDataToSheet(g_Workbook, previousType);
}
dgvUnique.DataSource = dt;
#endregion
g_Workbook.Worksheets.Item[1].Delete();
g_Workbook.Save();
releaseObject(g_Workbook);
//frmLoading.CloseForm();
Cursor = Cursors.Arrow; // change cursor to normal type
}