public LaborWage員工工時Table GetDataGroupByLabor(List <string> worksheetNumberList) { LaborWage員工工時Table table = new LaborWage員工工時Table(); if (worksheetNumberList.Count > 0) { string inQuery = " IN ( '" + string.Join("','", worksheetNumberList.ToArray()) + "' )"; OleDbCommand cmd = new OleDbCommand(); cmd.CommandText = "SELECT L.姓名 AS 員工姓名, Sum(val(Format(D.QTY*P.UnitPrice,'#')))/" + Settings.HourlyPay + " AS 工時 " + "FROM (Data as D INNER JOIN Product as P ON D.產品編號 = P.編號 AND D.外包品名編號 = P.外包品名編號) INNER JOIN Labor as L ON L.編號 = D.人員編號 " + "WHERE 工作單號 " + inQuery + "GROUP BY L.姓名"; cmd.Connection = _connection; Adapter.SelectCommand = cmd; Adapter.Fill(table); } return(table); }
public LaborWage員工工時Table GetDataByDate(DateTime from, DateTime to) { LaborWage員工工時Table table = new LaborWage員工工時Table(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = _connection; cmd.CommandText = "SELECT 日期, L.姓名 AS 員工姓名, Sum(val(Format(D.QTY*P.UnitPrice,'#')))/" + Settings.HourlyPay + " AS 工時 " + "FROM (Data as D INNER JOIN Product as P ON D.產品編號 = P.編號 AND D.外包品名編號 = P.外包品名編號) INNER JOIN Labor as L ON L.編號 = D.人員編號 " + "WHERE 日期 >= ? AND 日期 <= ? " + "GROUP BY 日期, L.姓名 "; cmd.Parameters.Add(new OleDbParameter("日期1", from)); cmd.Parameters.Add(new OleDbParameter("日期2", to)); Adapter.SelectCommand = cmd; Adapter.Fill(table); return(table); }