Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
        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);
        }