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