private void btnRefer_Click(object sender, EventArgs e)
{
if (tmpSrc != null)
{
tmpSrc.Clear();
lblRowCshow.Text = "0";
}
if (chkPartNum.Checked)
{
if (txtKeyIn.TextLength < 11)
{
sysMessage.sysMsg("您輸入的料號碼數不足，請確認！", "錯誤");
}
else
{
tmpSrc = GetSQL.業務_訂單現況查詢(txtKeyIn.Text);
tmpSrc.Columns.Remove("partnum");
tmpSrc.Columns.Remove("REVision");
tmpSrc.Columns.Remove("CustomerPartNum");
tmpSrc.Columns.Remove("serialnum");
tmpSrc.Columns.Remove("podate");
tmpSrc.Columns.Remove("expstkdate");
tmpSrc.Columns.Remove("proccode");
tmpSrc.Columns.Remove("LayerId");
tmpSrc.Columns.Remove("QntyXOut");
tmpSrc.Columns.Remove("QntyFGS");
tmpSrc.Columns.Remove("CustomerId");
tmpSrc.Columns.Remove("LotStatus");
tmpSrc.Columns.Remove("PO_Revision");
tmpSrc.Columns.Remove("isPO");
tmpSrc.Columns.Remove("scQuantity");
tmpSrc.Columns.Remove("sL_LL");
tmpSrc.Columns.Remove("LPiece");
tmpSrc.Columns.Remove("LLPiece");
tmpSrc.Columns.Remove("LLLPiece");
tmpSrc.Columns.Remove("rptType");
tmpSrc.Columns.Remove("ReportTitle");
tmpSrc.Columns["sPartNum"].SetOrdinal(0);
tmpSrc.Columns["sPO_Revision"].SetOrdinal(1);
tmpSrc.Columns["s_podate"].SetOrdinal(4);
tmpSrc.Columns["s_expstkdate"].SetOrdinal(5);
tmpSrc.Columns["sWIP_Revision"].SetOrdinal(7);
dgvDataSrc.DataSource = tmpSrc;
dgvDataSrcByPartnum();
int tmp = 0;
for (int i = 0; i < dgvDataSrc.Rows.Count - 1; i++)
{
if (dgvDataSrc.Rows[i].Cells["sPO_Revision"].Value.ToString() != "")
{
tmp++;
}
}
lblRowCshow.Text = Convert.ToString(tmp);
}
}
else
{
int POType = 0;
if (rdoPOType1.Checked)
{
POType = 1;
}
if (chkDateStart.Checked)
{
tmpSrc = GetSQL.業務_訂單未交貨明細表A(dtpDateS.Value.ToString("yyyy-MM-dd 00:00:00"),
dtpDateE.Value.ToString("yyyy-MM-dd 23:59:59"), POType);
}
else if (chkCustomerID.Checked)
{
if (txtKeyIn.TextLength != 3)
{
sysMessage.sysMsg("您輸入的客戶代碼不足3碼或超過3碼，請確認！", "訊息");
return;
}
tmpSrc = GetSQL.業務_訂單未交貨明細表C(txtKeyIn.Text, POType);
}
else
{
tmpSrc = GetSQL.業務_訂單未交貨明細表B(dtpDateE.Value.ToString("yyyy-MM-dd 23:59:59"), POType);
}
string strNote = "";
if (tmpSrc.Rows.Count > 0)
{
PGB pgb = new PGB();
pgb.Text = "查詢中";
pgb.label1.Text = "比對現帳中.....請稍候";
pgb.progressBar1.Minimum = 0;
pgb.progressBar1.Maximum = tmpSrc.Rows.Count;
pgb.progressBar1.Step = 1;
pgb.Show();
Application.DoEvents();
tmpSrc.Columns.Add("現帳備註", Type.GetType("System.String"));
//依查出來的未交明細料號，下去跑製程現況
for (int i = 0; i < tmpSrc.Rows.Count; i++)
{
tmpMath = GetSQL.業務_訂單現況查詢(tmpSrc.Rows[i]["料號"].ToString().Trim());
pgb.progressBar1.Maximum += tmpMath.Rows.Count;
//依查出來的現況，帶出製程、狀態、數量
for (int x = 0; x < tmpMath.Rows.Count; x++)
{
if (tmpMath.Rows[x]["proccode"].ToString().Trim() != "")
{
strNote = tmpMath.Rows[x]["ProcName"].ToString().Trim() + " " +
tmpMath.Rows[x]["l0q"].ToString().Trim();
if (tmpSrc.Rows[i]["現帳備註"].ToString() == "")
{
tmpSrc.Rows[i]["現帳備註"] += strNote;
}
else
{
tmpSrc.Rows[i]["現帳備註"] += " / " + strNote;
}
}
pgb.progressBar1.Value++;
}
pgb.progressBar1.Value++;
}
pgb.Dispose();
}
dgvDataSrc.DataSource = tmpSrc;
if (tmpSrc.Rows.Count == 0)
{
sysMessage.NoData();
return;
}
dgvDataSrcFormat();
lblRowCshow.Text = Convert.ToString(dgvDataSrc.Rows.Count - 1);
}
}
/// <summary>
/// 將資料表轉成Eecel檔案
/// 若轉出並存檔成功，傳回true
/// </summary>
/// <param name="Source">要轉出的資料表陣列</param>
/// <param name="SheetName">工作表名陣列</param>
/// <param name="SavePath">存檔路徑及檔名</param>
public static bool DataTableToExcel(DataTable[] Source, string[] SheetName, string SavePath)
{
bool result = false;
IWorkbook workbook;
ISheet Sheet;
string filenameExtension = ".xls";
if (sysMessage.ExcelSaveRevi() == DialogResult.Yes)
{
filenameExtension = ".xlsx";
workbook = new XSSFWorkbook();
}
else
{
workbook = new HSSFWorkbook();
}
int rowCount = 0;
for (int i = 0; i < Source.Length; i++)
{
for (int j = 0; j < Source[i].Rows.Count; j++)
{
rowCount++;
}
}
PGB pgb = new PGB();
pgb.Text = "滙出Excel";
pgb.label1.Text = "正在滙出資料中...請稍候.....";
pgb.progressBar1.Value = 0;
pgb.progressBar1.Maximum = rowCount;
pgb.progressBar1.Step = 1;
pgb.Show();
Application.DoEvents();
for (int AllSheet = 0; AllSheet < Source.Length; AllSheet++)
{
if (Source[AllSheet].TableName != string.Empty | Source[AllSheet].TableName != "")
{
Sheet = workbook.CreateSheet(Source[AllSheet].TableName);
}
else
{
Sheet = workbook.CreateSheet(SheetName[AllSheet]);
}
Sheet.CreateRow(0);
for (int i = 0; i < Source[AllSheet].Columns.Count; i++)
{
Sheet.GetRow(0).CreateCell(i).SetCellValue(Source[AllSheet].Columns[i].ColumnName);
}
for (int i = 0; i < Source[AllSheet].Rows.Count; i++)
{
Sheet.CreateRow(i + 1);
for (int x = 0; x < Source[AllSheet].Columns.Count; x++)
{
Sheet.GetRow(i + 1).CreateCell(x).SetCellValue(Source[AllSheet].Rows[i][x].ToString());
}
pgb.progressBar1.Value++;
}
//自動調整欄寬
for (int i = 0; i < Source[AllSheet].Columns.Count; i++)
{
Sheet.AutoSizeColumn(i);
}
}
FileStream SaveFile = new FileStream(SavePath + filenameExtension, FileMode.Create);
try
{
workbook.Write(SaveFile);
result = true;
}
catch (Exception ex)
{
sysMessage.SystemEx(ex.Message);
}
SaveFile.Close();
pgb.Dispose();
return result;
}
private void btnChkNAS_Click(object sender, EventArgs e)
{
if (dgvData.DataSource == null | dgvData.Rows.Count - 1 == 0)
{
sysMessage.NoData();
}
else
{
DataTable tmpDT = new DataTable();
tmpDT.Columns.Add("料號");
tmpDT.Columns.Add("狀態");
PGB pgb = new PGB();
pgb.Text = "檢查中";
pgb.label1.Text = "檢查料號中，請稍候.....";
pgb.progressBar1.Minimum = 0;
pgb.progressBar1.Maximum = dgvData.Rows.Count - 1;
pgb.progressBar1.Value = 0;
pgb.Show();
Application.DoEvents();
string Path = @"\\n5200xxx\LDI_Source\Inn Layer";
string FilePath = "";
if (rdo310.Checked)
{
Path = @"\\n5200xxx\LDI_Source\Out Layer";
}
for (int i = 0; i < dgvData.Rows.Count - 1; i++)
{
FilePath = Path + @"\" + dgvData.Rows[i].Cells["料號"].Value.ToString().Substring(0, 3);
if (!System.IO.Directory.Exists(FilePath))
{
DataRow row = tmpDT.NewRow();
row["料號"] = dgvData.Rows[i].Cells["料號"].Value.ToString();
row["狀態"] = "客戶代號資料夾不存在";
tmpDT.Rows.Add(row);
}
else
{
int result = 0;
string[] Directory = System.IO.Directory.GetDirectories(FilePath);
for (int x = 0; x < Directory.Length; x++)
{
if (Directory[x].Contains(dgvData.Rows[i].Cells["料號"].Value.ToString().Trim()))
{
result++;
}
}
if (result == 0)
{
DataRow row = tmpDT.NewRow();
row["料號"] = dgvData.Rows[i].Cells["料號"].Value.ToString();
row["狀態"] = "料號資料夾不存在";
tmpDT.Rows.Add(row);
}
}
pgb.progressBar1.Value++;
}
pgb.Dispose();
if (tmpDT.Rows.Count == 0)
{
sysMessage.sysMsg("檢查結果：OK", "訊息");
}
else
{
tmpDT.DefaultView.Sort = "料號";
NewRTB nrtb = new NewRTB();
for (int i = 0; i < tmpDT.Rows.Count; i++)
{
nrtb.rtbMain.Text += tmpDT.Rows[i]["料號"].ToString().Trim() + " - " +
tmpDT.Rows[i]["狀態"].ToString() + "\n";
}
nrtb.Show();
}
}
}
/// <summary>
/// 將Excel檔案轉成資料表陣列
/// </summary>
/// <param name="strFilePath">檔案路徑</param>
/// <returns>DataTable Array</returns>
public static DataTable[] ExcelToDataTable(string strFilePath)
{
DataTable[] result;
IWorkbook wb;
ISheet hst;
IRow HeaderRow;
IRow row;
using (FileStream fs = new FileStream(strFilePath, FileMode.Open))
{
if (strFilePath.Contains(".xlsx"))
{
wb = new XSSFWorkbook(fs);
hst = new XSSFSheet();
}
else
{
wb = new HSSFWorkbook(fs);
hst = new HSSFSheet((HSSFWorkbook)wb);
}
result = new DataTable[wb.NumberOfSheets];
try
{
PGB pgb = new PGB();
pgb.Text = "滙入資料";
pgb.label1.Text = "滙入資料中，請稍候.....";
pgb.Show();
Application.DoEvents();
for (int i = 0; i < wb.NumberOfSheets; i++)
{
hst = wb.GetSheetAt(i);
HeaderRow = hst.GetRow(0);
result[i] = new DataTable();
pgb.progressBar1.Minimum = 0;
pgb.progressBar1.Value = 0;
if (HeaderRow == null & i == wb.NumberOfSheets)
{
break;
}
else if (HeaderRow == null & i < wb.NumberOfSheets)
{
goto Next;
}
pgb.progressBar1.Maximum = HeaderRow.LastCellNum + hst.LastRowNum;
pgb.progressBar1.Step = 1;
//寫入欄位標題
for (int h = HeaderRow.FirstCellNum; h < HeaderRow.LastCellNum; h++)
{
if (HeaderRow.GetCell(h) != null)
{
result[i].Columns.Add(new DataColumn(HeaderRow.GetCell(h).StringCellValue));
}
else
{
result[i].Columns.Add(new DataColumn(""));
}
pgb.progressBar1.Value++;
}
//寫入欄位資料
for (int j = (hst.FirstRowNum + 1); j <= hst.LastRowNum; j++)
{
row = hst.GetRow(j);
if (row == null) continue;
DataRow dtRow = result[i].NewRow();
for (int x = row.FirstCellNum; x < row.LastCellNum; x++)
{
if (row.GetCell(x) != null)
{
//由NPOI內提供的類別庫判斷公式類型
IFormulaEvaluator iFormula = WorkbookFactory.CreateFormulaEvaluator(wb);
var formulaType = iFormula.Evaluate(row.GetCell(x)).CellType;
//若判斷結果類型為數值(注意：日期結果會被轉為數值)
if (formulaType == CellType.Numeric)
{
ICell cell = iFormula.EvaluateInCell(row.GetCell(x));
// 判斷結果類型是否為日期
if (DateUtil.IsCellDateFormatted(cell))
{
//設定日期格式
//ICellStyle cellStyle = wb.CreateCellStyle();
//IDataFormat format = wb.CreateDataFormat();
//cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
//row.GetCell(x).CellStyle = cellStyle;
//dtRow[x] = row.GetCell(x).ToString();
dtRow[x] = row.GetCell(x).DateCellValue.ToString("yyyy-MM-dd");
}
else
{
dtRow[x] = row.GetCell(x).NumericCellValue;
}
}
//判斷結果類型是否為字串
else if (formulaType == CellType.String)
{
dtRow[x] = row.GetCell(x).StringCellValue;
}
else
{
dtRow[x] = row.GetCell(x).ToString();
}
}
}
result[i].Rows.Add(dtRow);
pgb.progressBar1.Value++;
}
Next:;
}
pgb.Dispose();
}
catch (Exception ex)
{
sysMessage.SystemEx(ex.Message);
}
return result;
}
}
private void btnUpload_Click(object sender, EventArgs e)
{
string strFilePath = "";
OpenFileDialog openFile = new OpenFileDialog();
if (openFile.ShowDialog() == DialogResult.OK)
{
strFilePath = openFile.FileName;
DataTable[] result = ewPour.ExcelToDataTable(strFilePath);
dgvSource.DataSource = result[0];
dgvFormat();
DataTable tmpDT = result[0].Copy();
lblSourceNumShow.Text = Convert.ToString(dgvSource.Rows.Count - 1);
PGB pgb = new PGB();
pgb.Text = "資料配對";
pgb.label1.Text = "資料配對中，請稍候.....";
pgb.progressBar1.Minimum = 0;
pgb.progressBar1.Maximum = tmpDT.Rows.Count;
pgb.Show();
Application.DoEvents();
double[] tmp = new double[2];
DataTable dtTmp = new DataTable();
dtTmp.Columns.Add("PartNum");
dtTmp.Columns.Add("Revision");
dtTmp.Columns.Add("LotNum");
dtTmp.Columns.Add("ProcCode");
dtTmp.Columns.Add("PP");
dtTmp.Columns.Add("PartNum2");
dtTmp.Columns.Add("Revision2");
dtTmp.Columns.Add("LotNum2");
dtTmp.Columns.Add("ProcCode2");
dtTmp.Columns.Add("PP2");
dtTmp.Columns.Add("Program");
dtTmp.Columns["PartNum"].ColumnName = "料號";
dtTmp.Columns["Revision"].ColumnName = "版本";
dtTmp.Columns["LotNum"].ColumnName = "批號";
dtTmp.Columns["ProcCode"].ColumnName = "途程";
dtTmp.Columns["PP"].ColumnName = "品名";
dtTmp.Columns["PartNum2"].ColumnName = "混壓料號";
dtTmp.Columns["Revision2"].ColumnName = "混壓版本";
dtTmp.Columns["LotNum2"].ColumnName = "混壓批號";
dtTmp.Columns["ProcCode2"].ColumnName = "混壓途程";
dtTmp.Columns["PP2"].ColumnName = "混壓品名";
dtTmp.Columns["Program"].ColumnName = "壓機程式";
//##### DataTable在使用Delete()時，會使Count改變，所以要使用倒序檢查，才不會因Count變動而導致比對不完整 #####//
//先拿掉超過8層板的料號
for (int i = tmpDT.Rows.Count - 1; i >= 0; i--)
{
//若為10層板以上，層別代號非數字，先行直接刪除
if (ewCheck.ChkLetter(tmpDT.Rows[i]["料號"].ToString().Substring(4, 1)))
{
tmpDT.Rows[i].Delete();
}
else
{
if (Convert.ToInt32(tmpDT.Rows[i]["料號"].ToString().Substring(4, 1)) > 8)
{
tmpDT.Rows[i].Delete();
}
}
pgb.progressBar1.Value++;
}
tmpDT.AcceptChanges();
pgb.progressBar1.Maximum += (tmpDT.Rows.Count * tmpDT.Rows.Count);
//進行計算，計算板子的長短邊距是否有小於1.8inch
for (int i = 0; i < tmpDT.Rows.Count; i++)
{
for (int x = 0; x < tmpDT.Rows.Count; x++)
{
tmp = ewMath.BlendPress(Convert.ToDouble(tmpDT.Rows[i]["L"]),
Convert.ToDouble(tmpDT.Rows[x]["L"]),
Convert.ToDouble(tmpDT.Rows[i]["W"]),
Convert.ToDouble(tmpDT.Rows[x]["W"]));
if (tmp[0] < 1.8 & tmp[1] < 1.8)
{
DataRow row = dtTmp.NewRow();
row["料號"] = tmpDT.Rows[i]["料號"].ToString().Trim();
row["版本"] = tmpDT.Rows[i]["版本"].ToString().Trim();
row["批號"] = tmpDT.Rows[i]["批號"].ToString().Trim();
row["品名"] = tmpDT.Rows[i]["品名"].ToString().Trim();
row["混壓料號"] = tmpDT.Rows[x]["料號"].ToString().Trim();
row["混壓版本"] = tmpDT.Rows[x]["版本"].ToString().Trim();
row["混壓批號"] = tmpDT.Rows[x]["批號"].ToString().Trim();
row["混壓品名"] = tmpDT.Rows[x]["品名"].ToString().Trim();
dtTmp.Rows.Add(row);
}
pgb.progressBar1.Value++;
}
}
//取得該批號在傑偲的現帳途程
pgb.progressBar1.Maximum += dtTmp.Rows.Count;
for (int i = 0; i < dtTmp.Rows.Count; i++)
{
dtTmp.Rows[i]["途程"] = GetSQL.FMEdProcCode(dtTmp.Rows[i]["批號"].ToString());
dtTmp.Rows[i]["混壓途程"] = GetSQL.FMEdProcCode(dtTmp.Rows[i]["混壓批號"].ToString());
pgb.progressBar1.Value++;
}
//拿掉二邊重覆的料號
pgb.progressBar1.Maximum += dtTmp.Rows.Count;
for (int i = dtTmp.Rows.Count - 1; i >= 0; i--)
{
if (dtTmp.Rows[i]["料號"].ToString().Trim() == dtTmp.Rows[i]["混壓料號"].ToString().Trim())
{
dtTmp.Rows[i].Delete();
}
pgb.progressBar1.Value++;
}
dtTmp.AcceptChanges();
//拿掉4層板配8層板的結果
pgb.progressBar1.Maximum += dtTmp.Rows.Count;
for (int i = dtTmp.Rows.Count - 1; i >= 0; i--)
{
if ((Convert.ToInt32(dtTmp.Rows[i]["料號"].ToString().Trim().Substring(4, 1)) == 4 &
Convert.ToInt32(dtTmp.Rows[i]["混壓料號"].ToString().Trim().Substring(4, 1)) == 8) |
(Convert.ToInt32(dtTmp.Rows[i]["料號"].ToString().Trim().Substring(4, 1)) == 8 &
Convert.ToInt32(dtTmp.Rows[i]["混壓料號"].ToString().Trim().Substring(4, 1)) == 4))
{
dtTmp.Rows[i].Delete();
}
pgb.progressBar1.Value++;
}
dtTmp.AcceptChanges();
//拿掉二邊不同PP廠商的料號，NY=南亞、HT=宏泰
/* 2016-08-18 又取消掉不同廠商不可混壓的規則
pgb.progressBar1.Maximum += dtTmp.Rows.Count;
for (int i = dtTmp.Rows.Count - 1; i >= 0; i--)
{
if (dtTmp.Rows[i]["品名"].ToString().Contains("NY") &
dtTmp.Rows[i]["混壓品名"].ToString().Contains("HT"))
{
dtTmp.Rows[i].Delete();
}
else if (dtTmp.Rows[i]["品名"].ToString().Contains("HT") &
dtTmp.Rows[i]["混壓品名"].ToString().Contains("NY"))
{
dtTmp.Rows[i].Delete();
}
pgb.progressBar1.Value++;
}
dtTmp.AcceptChanges();
*/
//拿掉4層板不可混壓的料號，PP為1080，但TG不是140及155
pgb.progressBar1.Maximum += dtTmp.Rows.Count;
for (int i = dtTmp.Rows.Count - 1; i >= 0; i--)
{
//檢查左、右二邊料號是不是4層板
if (Convert.ToInt32(dtTmp.Rows[i]["料號"].ToString().Substring(4, 1)) == 4 &
Convert.ToInt32(dtTmp.Rows[i]["混壓料號"].ToString().Substring(4, 1)) == 4)
{
//檢查左、右二邊PP是不是1080，若不是1080就都拿掉
if (dtTmp.Rows[i]["品名"].ToString().Contains("1080") &
dtTmp.Rows[i]["混壓品名"].ToString().Contains("1080"))
{
//檢查TG是不是140、155，若不是就拿掉
if (dtTmp.Rows[i]["品名"].ToString().Contains("140") |
dtTmp.Rows[i]["品名"].ToString().Contains("155"))
{
if (!dtTmp.Rows[i]["混壓品名"].ToString().Contains("140") &&
!dtTmp.Rows[i]["混壓品名"].ToString().Contains("155"))
{
dtTmp.Rows[i].Delete();
}
}
}
else
{
dtTmp.Rows[i].Delete();
}
}
pgb.progressBar1.Value++;
}
dtTmp.AcceptChanges();
//
pgb.progressBar1.Maximum += dtTmp.Rows.Count;
for (int i = dtTmp.Rows.Count - 1; i >= 0; i--)
{
if (Convert.ToInt32(dtTmp.Rows[i]["料號"].ToString().Substring(4, 1)) > 4 &
Convert.ToInt32(dtTmp.Rows[i]["混壓料號"].ToString().Substring(4, 1)) == 4)
{
if (dtTmp.Rows[i]["品名"].ToString().Contains("1080") &
dtTmp.Rows[i]["混壓品名"].ToString().Contains("1080"))
{
dtTmp.Rows[i].Delete();
}
}
}
dtTmp.AcceptChanges();
//DataTable tmpProgram = GetSQL.壓合熱壓程式表();
pgb.progressBar1.Maximum += dtTmp.Rows.Count;
for (int i = 0; i < dtTmp.Rows.Count; i++)
{
if (dtTmp.Rows[i]["品名"].ToString().Contains("1080") &
dtTmp.Rows[i]["混壓品名"].ToString().Contains("1080"))
{
if ((dtTmp.Rows[i]["品名"].ToString().Contains("TG140") &
dtTmp.Rows[i]["混壓品名"].ToString().Contains("TG155")) |
(dtTmp.Rows[i]["品名"].ToString().Contains("TG155") &
dtTmp.Rows[i]["混壓品名"].ToString().Contains("TG140")))
{
dtTmp.Rows[i]["壓機程式"] = "1080PP-HT150";
}
else
{
dtTmp.Rows[i]["壓機程式"] = "1080PP-NY140";
}
}
}
pgb.Dispose();
srcResult.DataSource = dtTmp;
dgvResult.DataSource = srcResult;
dgvResultFormat();
lblResultNumShow.Text = Convert.ToString(dgvResult.Rows.Count - 1);
}
}