using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace SQLReportTool
{
public partial class Form1 : Form
{
private string uid;
private string pwd;
private Dictionary<string, string> alarmLevelMap = new Dictionary<string, string>();
public Form1()
{
InitializeComponent();
for (int i = 1; i <= 30; i++) cmbDays.Items.Add(i.ToString());
cmbDays.SelectedIndex = 6;
LoadCredentials();
}
private void LoadCredentials()
{
try
{
var lines = File.ReadAllLines("Password.txt");
foreach (var line in lines)
{
if (line.StartsWith("UID=")) uid = line.Substring(4).Trim();
if (line.StartsWith("PWD=")) pwd = line.Substring(4).Trim();
}
if (string.IsNullOrWhiteSpace(uid) || string.IsNullOrWhiteSpace(pwd))
throw new Exception("Password.txt 缺少 UID 或 PWD。");
}
catch (Exception ex)
{
MessageBox.Show("讀取 Password.txt 失敗: " + ex.Message, "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.Exit();
}
}
private int GetSelectedDays()
{
return Convert.ToInt32(cmbDays.SelectedItem);
}
private async void btnMTBA_Click(object sender, EventArgs e)
{
int days = GetSelectedDays();
string sql = $@"
SELECT TOP 1000 EQMYEAR, EQMEF, PRDTIME, EQPID, EQPTYPE, STAGE, MTBA_F
FROM DVI.dbo.EQM_CIM_EQMTSUM_FIX_MTYPE_V
WHERE PRODAREA = 'MF27'
AND CREATION_DT >= DATEADD(DAY, -{days}, GETDATE())
ORDER BY EQMYEAR DESC, EQMMONTH DESC, EQMDD DESC";
await RunQueryAsync(sql, "MTBA 報表");
}
private async void btnDispatch_Click(object sender, EventArgs e)
{
int days = GetSelectedDays();
string sql = $@"
SELECT TOP 1000 DWLNO, DWLMCNO, DWLEQID, DWLITEM, DWLCLASS, DWLOPID, DWLASTIME, DWLACTIME, DWLSTATUS
FROM DVI.dbo.MMSTDWL_V
WHERE DWLDEP = 'MF27'
AND DWLCLASS IN ('修機', '換部品')
AND DWLCREATE >= DATEADD(DAY, -{days}, GETDATE())
ORDER BY DWLCREATE DESC";
await RunQueryAsync(sql, "派工報表");
}
private async void btnAlarmLevel_Click(object sender, EventArgs e)
{
lblStatus.Text = "查詢 AlarmLevel 中...";
Cursor = Cursors.WaitCursor;
int days = GetSelectedDays();
List<string> stages = new List<string>();
List<string> eqchars = new List<string>();
try
{
using (var conn = new OdbcConnection($"DSN=dw323;UID={uid};PWD={pwd};"))
{
conn.Open();
string rawStageSql = $@"
SELECT DISTINCT LTRIM(RTRIM(STAGE)) AS STAGE, LTRIM(RTRIM(EQP_EQCHAR)) AS EQCHAR
FROM DVI.dbo.EQM_CIM_EQPALARM_RAW_V
WHERE DEPT = 'MF27'
AND STAGE IS NOT NULL AND LTRIM(RTRIM(STAGE)) <> ''
AND EQP_EQCHAR IS NOT NULL AND LTRIM(RTRIM(EQP_EQCHAR)) <> ''
AND ALARM_TIME >= DATEADD(DAY, -{days}, GETDATE())";
using (var cmd = new OdbcCommand(rawStageSql, conn))
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string stage = reader["STAGE"].ToString();
string eqchar = reader["EQCHAR"].ToString();
if (!stages.Contains(stage)) stages.Add(stage);
if (!eqchars.Contains(eqchar)) eqchars.Add(eqchar);
}
}
}
if (stages.Count == 0 || eqchars.Count == 0)
{
MessageBox.Show("AlarmRaw 中無任何 STAGE 或 EQCHAR 記錄。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
lblStatus.Text = "查詢完成（無 STAGE/EQCHAR）";
return;
}
string stageInSql = string.Join(",", stages.ConvertAll(s => $"'{s.Replace("'", "''")}'"));
string eqcharInSql = string.Join(",", eqchars.ConvertAll(s => $"'{s.Replace("'", "''")}'"));
string levelSql = $@"
SELECT *, 'MF27_' + STAGE + '_' + EQCHAR + '_' + ALARM_ID AS A欄
FROM DVI.dbo.MST_CIM_ALARM_LEVEL_MAPPING_V
WHERE STAGE IN ({stageInSql}) AND EQCHAR IN ({eqcharInSql})";
await Task.Run(() =>
{
using (var conn = new OdbcConnection($"DSN=dw323;UID={uid};PWD={pwd};"))
{
conn.Open();
using (var da = new OdbcDataAdapter(levelSql, conn))
{
DataTable dt = new DataTable();
da.Fill(dt);
alarmLevelMap.Clear();
foreach (DataRow row in dt.Rows)
{
string key = row["A欄"].ToString();
string level = row["ALARM_LEVEL"]?.ToString() ?? "NA";
if (!alarmLevelMap.ContainsKey(key))
alarmLevelMap.Add(key, level);
}
dgvResult.Invoke(new MethodInvoker(() => dgvResult.DataSource = dt));
lblStatus.Invoke(new MethodInvoker(() => lblStatus.Text = $"查詢完成，共 {dt.Rows.Count} 筆。"));
}
}
});
}
catch (Exception ex)
{
lblStatus.Text = "查詢 AlarmLevel 失敗：" + ex.Message;
MessageBox.Show(ex.ToString(), "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
Cursor = Cursors.Default;
}
}
private async void btnAlarmRaw_Click(object sender, EventArgs e)
{
int days = GetSelectedDays();
string sql = $@"
SELECT TOP 1000 EQP_ID, EQP_EQCHAR, ALARM_ID, EVENT_ID, ALARM_TYPE, ALARM_LEVEL, ALARM_MSG, ALARM_TIME, STAGE
FROM DVI.dbo.EQM_CIM_EQPALARM_RAW_V
WHERE DEPT = 'MF27'
AND ALARM_TYPE = 'S'
AND ALARM_TIME >= DATEADD(DAY, -{days}, GETDATE())
ORDER BY ALARM_TIME DESC";
await Task.Run(() =>
{
using (var conn = new OdbcConnection($"DSN=dw323;UID={uid};PWD={pwd};"))
{
conn.Open();
using (var da = new OdbcDataAdapter(sql, conn))
{
DataTable dt = new DataTable();
da.Fill(dt);
dt.Columns.Add("等級比對", typeof(string));
// 新增一個 List 來存放符合條件的資料列
List<DataRow> rowsToKeep = new List<DataRow>();
foreach (DataRow row in dt.Rows)
{
string key = $"MF27_{row["STAGE"]}_{row["EQP_EQCHAR"]}_{row["ALARM_ID"]}";
string level = alarmLevelMap.ContainsKey(key) ? alarmLevelMap[key] : "NA";
row["等級比對"] = level;
// 只保留 "Failure"
if (level == "Failure")
rowsToKeep.Add(row);
}
// 建立新資料表，匯入符合的列
DataTable filtered = dt.Clone(); // 複製欄位結構
foreach (var row in rowsToKeep)
filtered.ImportRow(row);
dgvResult.Invoke(new MethodInvoker(() => dgvResult.DataSource = filtered));
lblStatus.Invoke(new MethodInvoker(() => lblStatus.Text = $"查詢完成，共 {filtered.Rows.Count} 筆（等級比對 = Failure）。"));
}
}
});
}
private async void btnSPC_Click(object sender, EventArgs e)
{
int days = GetSelectedDays();
string sql = $@"
SELECT TOP 100 LOTID, MEASURE_DATE, METHOD, EQUIPMENT, USER_ID,
OCCASION, ERROR_MSG, SPC_RESULT, OCAPNO, TRANSFER_DATE
FROM DVI.dbo.PRD_CIM_SPC_Data_V
WHERE MEASURE_DATE >= DATEADD(DAY, -{days}, GETDATE())
AND SPC_RESULT NOT IN ('OK','NG')";
//AND PRODAREA IN('27')
// AND EQUIPMENT like 'R%'
await RunQueryAsync(sql, "SPC 報表");
}
private async Task RunQueryAsync(string sql, string label)
{
lblStatus.Text = $"查詢 {label} 中...";
Cursor = Cursors.WaitCursor;
var sw = System.Diagnostics.Stopwatch.StartNew();
try
{
await Task.Run(() =>
{
using (var conn = new OdbcConnection($"DSN=dw323;UID={uid};PWD={pwd};"))
{
conn.Open();
using (var da = new OdbcDataAdapter(sql, conn))
{
DataTable dt = new DataTable();
da.Fill(dt);
dgvResult.Invoke(new MethodInvoker(() => dgvResult.DataSource = dt));
lblStatus.Invoke(new MethodInvoker(() =>
{
lblStatus.Text = dt.Rows.Count == 0
? $"{label}：查詢完成但無資料。"
: $"{label}：共 {dt.Rows.Count} 筆，耗時 {sw.ElapsedMilliseconds} ms";
}));
}
}
});
}
catch (Exception ex)
{
lblStatus.Text = $"{label} 查詢失敗：" + ex.Message;
MessageBox.Show(ex.ToString(), "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
Cursor = Cursors.Default;
}
}
private async void btnAGS_Click(object sender, EventArgs e)
{
/* lblStatus.Text = "查詢AGS 中...";
int days = GetSelectedDays(); // 你應已有這個方法從 cmbDays 取得天數
string sql = $@"
SELECT
SPILDT, EQPID, EQPCHAR, EQPTYP, BRANDCODE,
CHGSDT, CHGEDT, STATUS, EMPID, STATUSDESC
FROM DVI.dbo.DVI_EQM_CIM_EQPS_V
WHERE SPILDT >= DATEADD(DAY, -{days}, GETDATE())
AND EQPTYP LIKE 'R%'";
await Task.Run(() =>
{
using (var conn = new OdbcConnection($"DSN=dw323;UID={uid};PWD={pwd};"))
{
conn.Open();
using (var da = new OdbcDataAdapter(sql, conn))
{
DataTable rawTable = new DataTable();
da.Fill(rawTable);
// 用來儲存合併後資料
DataTable mergedTable = rawTable.Clone();
DataRow current = null;
foreach (DataRow row in rawTable.Rows)
{
if (current == null)
{
current = mergedTable.NewRow();
current.ItemArray = row.ItemArray.Clone() as object[];
}
else
{
// 比對 EQPID 和 STATUSDESC 是否相同
bool isSameGroup =
current["EQPID"].ToString() == row["EQPID"].ToString() &&
current["STATUSDESC"].ToString() == row["STATUSDESC"].ToString();
if (isSameGroup)
{
// 更新結束時間 CHGEDT 為目前行的 CHGEDT
current["CHGEDT"] = row["CHGEDT"];
}
else
{
// 新分段，加入前一筆資料
mergedTable.Rows.Add(current);
current = mergedTable.NewRow();
current.ItemArray = row.ItemArray.Clone() as object[];
}
}
}
if (current != null)
mergedTable.Rows.Add(current); // 加入最後一筆
dgvResult.Invoke(new MethodInvoker(() => dgvResult.DataSource = mergedTable));
lblStatus.Invoke(new MethodInvoker(() => lblStatus.Text = $"查詢完成，共 {mergedTable.Rows.Count} 筆（已合併）。"));
}
}
});
*/}
}
}
1.btnSPC_Click 讀取讀取時間要拉長不然會出現逾時
2.ALARM LEVEL跟ALARM RAW 幫我整併(ALARM RAW會用到ALARMLEVEL的東西)
3.後續剩下MTBA/派工/AlarmRaw/SPC/AGS 分天匯出TXT(包含TITLE)
需求如上 直接幫我設計.cs跟 desinger
博客主要提及了SelectDistinct方法，但未给出具体内容。
扫一扫
1095
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
