public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerGA1Vo> voList = new ValueObjectList <ProductionControllerGA1Vo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select distinct model from modeltbl where model like 'LDP%' or model like 'GR%' order by model desc");
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerGA1Vo outVo = new ProductionControllerGA1Vo
{
ModelCode = dataReader["model"].ToString()
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
StringBuilder sql = new StringBuilder();
ProductionControllerGA1Vo voList = new ProductionControllerGA1Vo();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
string line = "";
string grline = "";
if (!string.IsNullOrEmpty(inVo.LineCode))
{
line = ",a.line";
grline = ",a.line";
}
else
{
line = ",cast ('All Line' as character varying) line"; grline = ",line";
}
sql.Append(@"select id,model, line,dates, process, sum(inspectdata) inspectdata from (select cast(dense_rank() over(partition by line order by process) as int ) id, a.model" + line + ", a.process,cast (a.inspectdate as date) dates, a.serno, sum(inspectdata) inspectdata from " + inVo.TableName);
sql.Append(" a left join " + inVo.TableName + "data b on a.serno = b.serno where a.inspectdate = b.inspectdate ");
sql.Append(@" and a.inspectdate >= :datefrom and a.inspectdate <= :dateto ");
sqlParameter.AddParameter("datefrom", inVo.DateFrom);
sqlParameter.AddParameter("dateto", inVo.DateTo);
if (!string.IsNullOrEmpty(inVo.ModelCode))
{
sql.Append(@" and a.model =:model");
sqlParameter.AddParameterString("model", inVo.ModelCode);
}
if (!string.IsNullOrEmpty(inVo.LineCode))
{
sql.Append(@" and a.line =:line");
sqlParameter.AddParameterString("line", inVo.LineCode);
}
if (!string.IsNullOrEmpty(inVo.ProcessCode))
{
sql.Append(@" and a.process =:process");
sqlParameter.AddParameterString("process", inVo.ProcessCode);
}
sql.Append(@" group by process" + grline + ",a.model,a.serno,a.inspectdate order by a.inspectdate ) tbl ");
sql.Append(@" group by id,dates, model,line, process order by dates");
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
DataSet ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter);
//execute SQL
ProductionControllerGA1Vo outVo1 = new ProductionControllerGA1Vo
{
dt = ds.Tables[0],
};
return(outVo1);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerGA1Vo> voList = new ValueObjectList <ProductionControllerGA1Vo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append("select count(*) datas from (");
sql.Append("select * from (");
sql.Append("select a90_barcode bar,max(a90_date+a90_time) from t_checkpusha90 ");
sql.Append("where a90_date+a90_time >= :datefrom and a90_date+a90_time <= :dateto ");
if (!string.IsNullOrEmpty(inVo.LineCode))
{
sql.Append(@" and a90_line =:line");
sqlParameter.AddParameterString("line", inVo.LineCode);
}
if (!string.IsNullOrEmpty(inVo.ModelCode))
{
sql.Append(@" and a90_model =:a90_model");
sqlParameter.AddParameterString("a90_model", inVo.ModelCode);
}
sql.Append(" group by a90_barcode) a left join t_checkpusha90 b on a.bar = b.a90_barcode and a.max = b.a90_date+b.a90_time ) tbl ");
sql.Append("where 1=1 and bar not like ''");
if (inVo.change)
{
sql.Append(@" and a90_noise_status = 'OK' ");
}
else
{
sql.Append(@" and a90_noise_status = 'NG' ");
}
sqlParameter.AddParameter("datefrom", inVo.DateFrom);
sqlParameter.AddParameter("dateto", inVo.DateTo);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerGA1Vo outVo = new ProductionControllerGA1Vo
{
InspecData = dataReader["datas"].ToString()
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerGA1Vo> voList = new ValueObjectList <ProductionControllerGA1Vo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select a.process,case when sum(inspectdata) = null then 0 else sum(inspectdata) end inspectdata from " + inVo.TableName + " a left join " + inVo.TableName + "data b on a.serno = b.serno where ");
sql.Append(@" a.inspectdate >= :datefrom and a.inspectdate <= :dateto and inspectdata != 0 and b.inspectdate >= :datefrom and b.inspectdate <= :dateto and a.inspectdate = b.inspectdate ");
sqlParameter.AddParameter("datefrom", inVo.DateFrom);
sqlParameter.AddParameter("dateto", inVo.DateTo);
if (!string.IsNullOrEmpty(inVo.LineCode))
{
sql.Append(@" and a.line =:line");
sqlParameter.AddParameterString("line", inVo.LineCode);
}
if (!string.IsNullOrEmpty(inVo.ModelCode))
{
sql.Append(@" and a.model =:model ");
sqlParameter.AddParameterString("model", inVo.ModelCode);
}
sql.Append("group by a.process order by a.process");
//if (!string.IsNullOrEmpty(inVo.ProcessName))
//{
// sql.Append(@" and a.process =:process");
// sqlParameter.AddParameterString("process", inVo.ProcessName);
//}
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerGA1Vo outVo = new ProductionControllerGA1Vo
{
ProcessName = dataReader["process"].ToString(),
InspecData = dataReader["inspectdata"].ToString()
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerGA1Vo> voList = new ValueObjectList <ProductionControllerGA1Vo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append("select count (*) datas from (");
sql.Append("select distinct barcode from t_noisecheck_a90 ");
sql.Append("where date_check >= :datefrom and date_check <= :dateto ");
if (!string.IsNullOrEmpty(inVo.ModelCode))
{
sql.Append(@" and model =:model");
sqlParameter.AddParameterString("model", inVo.ModelCode);
}
if (inVo.change)//search theo line
{
sql.Append(@" and line =:line");
sqlParameter.AddParameterString("line", inVo.LineCode);
}
else//search tat ca line
{
//de cho vui thui @@
}
sql.Append(@" ) tbl");
sqlParameter.AddParameter("datefrom", inVo.DateFrom);
sqlParameter.AddParameter("dateto", inVo.DateTo);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerGA1Vo outVo = new ProductionControllerGA1Vo
{
InspecData = dataReader["datas"].ToString()
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
StringBuilder sql = new StringBuilder();
ProductionControllerGA1Vo voList = new ProductionControllerGA1Vo();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append("select 'INPUT' inspect, ");
sql.Append(select(inVo.TableName, inVo.ModelCode, inVo.LineCode, inVo.DateFrom, inVo.DateTo, "FRAME", "IP") + " FRAME,");
sql.Append(select(inVo.TableName, inVo.ModelCode, inVo.LineCode, inVo.DateFrom, inVo.DateTo, "GEAR", "IP") + " GEAR,");
sql.Append(select(inVo.TableName, inVo.ModelCode, inVo.LineCode, inVo.DateFrom, inVo.DateTo, "MOTOR", "IP") + " MOTOR ");
sql.Append("union ");
sql.Append("select 'OUTPUT' inspect, ");
sql.Append(select(inVo.TableName, inVo.ModelCode, inVo.LineCode, inVo.DateFrom, inVo.DateTo, "FRAME", "OP") + " FRAME,");
sql.Append(select(inVo.TableName, inVo.ModelCode, inVo.LineCode, inVo.DateFrom, inVo.DateTo, "GEAR", "OP") + " GEAR,");
sql.Append(select(inVo.TableName, inVo.ModelCode, inVo.LineCode, inVo.DateFrom, inVo.DateTo, "MOTOR", "OP") + " MOTOR ");
sql.Append("union ");
sql.Append("select 'TOTAL NG' inspect, ");
sql.Append(select(inVo.TableName, inVo.ModelCode, inVo.LineCode, inVo.DateFrom, inVo.DateTo, "FRAME", "NG") + " FRAME, ");
sql.Append(select(inVo.TableName, inVo.ModelCode, inVo.LineCode, inVo.DateFrom, inVo.DateTo, "GEAR", "NG") + " GEAR, ");
sql.Append(select(inVo.TableName, inVo.ModelCode, inVo.LineCode, inVo.DateFrom, inVo.DateTo, "MOTOR", "NG") + " MOTOR ");
sql.Append("order by inspect ");
//sqlParameter.AddParameter("model", inVo.ModelCode);
//sqlParameter.AddParameter("line", inVo.LineCode);
//sqlParameter.AddParameter("datefrom", inVo.DateFrom);
//sqlParameter.AddParameter("dateto", inVo.DateTo);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
DataSet ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter);
//execute SQL
ProductionControllerGA1Vo outVo1 = new ProductionControllerGA1Vo
{
dt = ds.Tables[0],
};
return(outVo1);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerGA1Vo> voList = new ValueObjectList <ProductionControllerGA1Vo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append("select count(*) datas from (select * from ");
sql.Append("(select a90_barcode bar,max(oid) oid from t_checkpusha90 where a90_date+a90_time >= :datefrom and a90_date+a90_time <= :dateto ");
if (!string.IsNullOrEmpty(inVo.LineCode))
{
sql.Append(" and a90_line = :a90_line ");
sqlParameter.AddParameter("a90_line", inVo.LineCode);
}
if (!string.IsNullOrEmpty(inVo.ModelCode))
{
sql.Append(" and a90_model = :a90_model ");
sqlParameter.AddParameter("a90_model", inVo.ModelCode);
}
sql.Append(" group by a90_barcode) a left join t_checkpusha90 b on a.oid = b.oid where b.a90_oqc_data = 'NG') b");
sqlParameter.AddParameter("datefrom", inVo.DateFrom);
sqlParameter.AddParameter("dateto", inVo.DateTo);
//group by a90_barcode) a left join t_checkpusha90 b on a.oid = b.oid where b.a90_oqc_data = 'NG') b
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//DataSet ds = sqlCommandAdapter.ExecuteScalar(sqlParameter);
IDataReader reader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
//execute SQL
while (reader.Read())
{
ProductionControllerGA1Vo outVo = new ProductionControllerGA1Vo
{
//LineCode = dataReader["line"].ToString() inspectdata
InspecData = reader["datas"].ToString()
};
voList.add(outVo);
}
reader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
StringBuilder sql = new StringBuilder();
ProductionControllerGA1Vo voList = new ProductionControllerGA1Vo();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select inspect, sum(inspectdata) inspectdata from " + inVo.TableName);
sql.Append(" a left join " + inVo.TableName + "data b on a.serno = b.serno where a.inspectdate = b.inspectdate ");
sql.Append(@" and a.inspectdate >= :datefrom and a.inspectdate <= :dateto ");
sqlParameter.AddParameter("datefrom", inVo.DateFrom);
sqlParameter.AddParameter("dateto", inVo.DateTo);
if (!string.IsNullOrEmpty(inVo.ModelCode))
{
sql.Append(@" and a.model =:model");
sqlParameter.AddParameterString("model", inVo.ModelCode);
}
if (!string.IsNullOrEmpty(inVo.LineCode))
{
sql.Append(@" and a.line =:line");
sqlParameter.AddParameterString("line", inVo.LineCode);
}
if (!string.IsNullOrEmpty(inVo.ProcessCode))
{
sql.Append(@" and a.process =:process");
sqlParameter.AddParameterString("process", inVo.ProcessCode);
}
sql.Append(@" group by inspect order by inspect");
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
DataSet ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter);
//execute SQL
ProductionControllerGA1Vo outVo1 = new ProductionControllerGA1Vo
{
dt = ds.Tables[0],
};
return(outVo1);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
StringBuilder sql = new StringBuilder();
ProductionControllerGA1Vo voList = new ProductionControllerGA1Vo();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(" select a.process, a.inspect,a.sum inspectdata from (");
sql.Append("select a.process, b.inspect, sum(b.inspectdata) from " + inVo.TableName + " a left join " + inVo.TableName + "data b on a.serno = b.serno where a.inspectdate >= :datefrom and a.inspectdate <= :dateto and a.model = :model and line =:line and a.process not in ('FA_IP','GC_IP') group by a.process, b.inspect");
sql.Append(" union ");
sql.Append("select 'FA_IP' process, 'Input Frame' inspect, sum(b.inspectdata) from " + inVo.TableName + " a left join " + inVo.TableName + "data b on a.serno = b.serno where a.inspectdate >= :datefrom and a.inspectdate <= :dateto and a.process in ('FA_IP') and a.model = :model and line =:line");
sql.Append(" union ");
sql.Append("select 'GC_IP' process, 'Input Gear' inspect, sum(b.inspectdata) from " + inVo.TableName + " a left join " + inVo.TableName + "data b on a.serno = b.serno where a.inspectdate >= :datefrom and a.inspectdate <= :dateto and a.process in ('GC_IP') and a.model = :model and line =:line");
sql.Append(" union ");
sql.Append("select 'TOTAL_F' process, 'Toal NG Frame' inspect, sum(b.inspectdata) from " + inVo.TableName + " a left join " + inVo.TableName + "data b on a.serno = b.serno where a.inspectdate >= :datefrom and a.inspectdate <= :dateto and a.process in ('FA_APP', 'FA_BallB', 'FA_Caulk') and a.model = :model and line =:line");
sql.Append(" union ");
sql.Append("select 'TOTAL_G' process, 'Toal NG Gear' inspect, sum(b.inspectdata) from " + inVo.TableName + " a left join " + inVo.TableName + "data b on a.serno = b.serno where a.inspectdate >= :datefrom and a.inspectdate <= :dateto and a.process in ('GC_Bear','GC_OSW','GC_PD','GC_C','GC_PU','GC_FGASS','GC_FGCHK') and a.model = :model and line =:line");
sql.Append(" union ");
sql.Append("Select 'TOTAL_M' process, 'Toal NG Motor' inspect, sum(b.inspectdata) from " + inVo.TableName + " a left join " + inVo.TableName + "data b on a.serno = b.serno where a.inspectdate >= :datefrom and a.inspectdate <= :dateto and a.process in ('MC_FPC','MC_FWCHK','MC_Mark','MC_NOICHK','MC_STMASS') and a.model = :model and line =:line");
sql.Append(" union ");
sql.Append("select 'OUTPUT' process, 'OP3' inspect, cast(0 as double precision) sum ");
sql.Append(" union ");
sql.Append("select 'MC_THUCHK' process, 'THU_NG' inspect, cast(0 as double precision) sum ");
sql.Append(") a left join processtbl c on a.process = c.process where c.line = :line order by c.stopmark, a.inspect ");
sqlParameter.AddParameter("model", inVo.ModelCode);
sqlParameter.AddParameter("line", inVo.LineCode);
sqlParameter.AddParameter("datefrom", inVo.DateFrom);
sqlParameter.AddParameter("dateto", inVo.DateTo);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
DataSet ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter);
//execute SQL
ProductionControllerGA1Vo outVo1 = new ProductionControllerGA1Vo
{
dt = ds.Tables[0],
};
return(outVo1);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerGA1Vo> voList = new ValueObjectList <ProductionControllerGA1Vo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append("select count(distinct barcode) from t_serno where 1=1 ");
sql.Append(" and regist_date >= :datefrom and regist_date <= :dateto ");
if (!string.IsNullOrEmpty(inVo.ModelCode))
{
sql.Append(@" and model =:model");
sqlParameter.AddParameterString("model", inVo.ModelCode);
}
if (!string.IsNullOrEmpty(inVo.LineCode))
{
sql.Append(@" and line =:line");
sqlParameter.AddParameterString("line", inVo.LineCode);
}
sqlParameter.AddParameter("datefrom", inVo.DateFrom);
sqlParameter.AddParameter("dateto", inVo.DateTo);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerGA1Vo outVo = new ProductionControllerGA1Vo
{
InspecData = dataReader["count"].ToString()
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerGA1Vo> voList = new ValueObjectList <ProductionControllerGA1Vo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select distinct b.inspect from " + inVo.TableName);
sql.Append(" a left join " + inVo.TableName + "data b on a.serno = b.serno where a.inspectdate = b.inspectdate ");
if (!string.IsNullOrEmpty(inVo.LineCode))
{
sql.Append(@" and a.line =:line");
sqlParameter.AddParameterString("line", inVo.LineCode);
}
if (!string.IsNullOrEmpty(inVo.ProcessCode))
{
sql.Append(@" and a.process =:process");
sqlParameter.AddParameterString("process", inVo.ProcessCode);
}
sql.Append(@" order by b.inspect");
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerGA1Vo outVo = new ProductionControllerGA1Vo
{
ItemCode = dataReader["inspect"].ToString()
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerGA1Vo> voList = new ValueObjectList <ProductionControllerGA1Vo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append("select distinct line from processtbl a left join modeltbl b on a.model = b.model where 1=1 ");
if (!string.IsNullOrEmpty(inVo.ModelCode))
{
sql.Append(@" and a.model =:model_cd");
sqlParameter.AddParameterString("model_cd", inVo.ModelCode);
}
sql.Append(@" order by line");
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerGA1Vo outVo = new ProductionControllerGA1Vo
{
LineCode = dataReader["line"].ToString()
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
StringBuilder sql = new StringBuilder();
ProductionControllerGA1Vo voList = new ProductionControllerGA1Vo();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
//string sqlChung = " times,count(*) from (select a90_barcode, a90_date+a90_time regist_date from t_checkpusha90 where a90_thurst_status = 'OK' and a90_date+a90_time >= :datefrom and a90_date+a90_time <= :dateto) wl where regist_date >= ";
string sqlChung = " times, count(distinct barcode) from (select * from t_serno where regist_date >= :datefrom and regist_date <=:dateto) t where 1=1 ";
if (!string.IsNullOrEmpty(inVo.ModelCode))
{
sqlChung += " and model = :model";
sqlParameter.AddParameter("model", inVo.ModelCode);
}
if (!string.IsNullOrEmpty(inVo.LineCode))
{
sqlChung += " and line = :line";
sqlParameter.AddParameter("line", inVo.LineCode);
}
sqlChung += " and regist_date >=";
sql.Append(@"select '01:00:00' " + sqlChung + " '" + inVo.Date + " 00:00:01' and regist_date <= '" + inVo.Date + " 01:00:00'");
sql.Append(" union ");
sql.Append(@"select '02:00:00' " + sqlChung + " '" + inVo.Date + " 01:00:01' and regist_date <= '" + inVo.Date + " 02:00:00'");
sql.Append(" union ");
sql.Append(@"select '03:00:00' " + sqlChung + " '" + inVo.Date + " 02:00:01' and regist_date <= '" + inVo.Date + " 03:00:00'");
sql.Append(" union ");
sql.Append(@"select '04:00:00' " + sqlChung + " '" + inVo.Date + " 03:00:01' and regist_date <= '" + inVo.Date + " 04:00:00'");
sql.Append(" union ");
sql.Append(@"select '05:00:00' " + sqlChung + " '" + inVo.Date + " 04:00:01' and regist_date <= '" + inVo.Date + " 05:00:00'");
sql.Append(" union ");
sql.Append(@"select '06:00:00' " + sqlChung + " '" + inVo.Date + " 05:00:01' and regist_date <= '" + inVo.Date + " 06:00:00'");
sql.Append(" union ");
sql.Append(@"select '07:00:00' " + sqlChung + " '" + inVo.Date + " 06:00:01' and regist_date <= '" + inVo.Date + " 07:00:00'");
sql.Append(" union ");
sql.Append(@"select '08:00:00' " + sqlChung + " '" + inVo.Date + " 07:00:01' and regist_date <= '" + inVo.Date + " 08:00:00'");
sql.Append(" union ");
sql.Append(@"select '09:00:00' " + sqlChung + " '" + inVo.Date + " 08:00:01' and regist_date <= '" + inVo.Date + " 09:00:00'");
sql.Append(" union ");
sql.Append(@"select '10:00:00' " + sqlChung + " '" + inVo.Date + " 09:00:01' and regist_date <= '" + inVo.Date + " 10:00:00'");
sql.Append(" union ");
sql.Append(@"select '11:00:00' " + sqlChung + " '" + inVo.Date + " 10:00:01' and regist_date <= '" + inVo.Date + " 11:00:00'");
sql.Append(" union ");
sql.Append(@"select '12:00:00' " + sqlChung + " '" + inVo.Date + " 11:00:01' and regist_date <= '" + inVo.Date + " 12:00:00'");
sql.Append(" union ");
sql.Append(@"select '13:00:00' " + sqlChung + " '" + inVo.Date + " 12:00:01' and regist_date <= '" + inVo.Date + " 13:00:00'");
sql.Append(" union ");
sql.Append(@"select '14:00:00' " + sqlChung + " '" + inVo.Date + " 13:00:01' and regist_date <= '" + inVo.Date + " 14:00:00'");
sql.Append(" union ");
sql.Append(@"select '15:00:00' " + sqlChung + " '" + inVo.Date + " 14:00:01' and regist_date <= '" + inVo.Date + " 15:00:00'");
sql.Append(" union ");
sql.Append(@"select '16:00:00' " + sqlChung + " '" + inVo.Date + " 15:00:01' and regist_date <= '" + inVo.Date + " 16:00:00'");
sql.Append(" union ");
sql.Append(@"select '17:00:00' " + sqlChung + " '" + inVo.Date + " 16:00:01' and regist_date <= '" + inVo.Date + " 17:00:00'");
sql.Append(" union ");
sql.Append(@"select '18:00:00' " + sqlChung + " '" + inVo.Date + " 17:00:01' and regist_date <= '" + inVo.Date + " 18:00:00'");
sql.Append(" union ");
sql.Append(@"select '19:00:00' " + sqlChung + " '" + inVo.Date + " 18:00:01' and regist_date <= '" + inVo.Date + " 19:00:00'");
sql.Append(" union ");
sql.Append(@"select '20:00:00' " + sqlChung + " '" + inVo.Date + " 19:00:01' and regist_date <= '" + inVo.Date + " 20:00:00'");
sql.Append(" union ");
sql.Append(@"select '21:00:00' " + sqlChung + " '" + inVo.Date + " 20:00:01' and regist_date <= '" + inVo.Date + " 21:00:00'");
sql.Append(" union ");
sql.Append(@"select '22:00:00' " + sqlChung + " '" + inVo.Date + " 21:00:01' and regist_date <= '" + inVo.Date + " 22:00:00'");
sql.Append(" union ");
sql.Append(@"select '23:00:00' " + sqlChung + " '" + inVo.Date + " 22:00:01' and regist_date <= '" + inVo.Date + " 23:00:00'");
sql.Append(" union ");
sql.Append(@"select '23:59:59' " + sqlChung + " '" + inVo.Date + " 23:00:01' and regist_date <= '" + inVo.Date + " 23:59:59'");
sql.Append(" order by times ");
sqlParameter.AddParameter("datefrom", inVo.DateFrom);
sqlParameter.AddParameter("dateto", inVo.DateTo);
//if (!string.IsNullOrEmpty(inVo.LineCode))
//{
// sql.Append(@" and a90_line =:line");
// sqlParameter.AddParameterString("line", inVo.LineCode);
//}
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
DataSet ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter);
//execute SQL
ProductionControllerGA1Vo outVo1 = new ProductionControllerGA1Vo
{
dt = ds.Tables[0],
};
return(outVo1);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
StringBuilder sql = new StringBuilder();
ProductionControllerGA1Vo voList = new ProductionControllerGA1Vo();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
//string sqlChung = " times,count(*) from (select a90_barcode, a90_date+a90_time datetimes from t_checkpusha90 where a90_thurst_status = 'OK' and a90_date+a90_time >= :datefrom and a90_date+a90_time <= :dateto) wl where datetimes >= ";
string sqlChung = " times,count(*) from (select a.a90_barcode,a.datetimes,a.a90_thurst_status from (select row_number() over(partition by a90_barcode order by a90_date+a90_time) id, a90_barcode,a90_date+a90_time datetimes,a90_thurst_status from t_checkpusha90 where a90_date+a90_time >= :datefrom and a90_date+a90_time <= :dateto and a90_line = :line and a90_model = :a90_model order by a90_barcode,datetimes) a,(select max(id) id,a90_barcode from (select row_number() over(partition by a90_barcode order by a90_date+a90_time) id, a90_barcode,a90_date+a90_time datetimes,a90_thurst_status from t_checkpusha90 where a90_date+a90_time >= :datefrom and a90_date+a90_time <= :dateto and a90_line = :line and a90_model = :a90_model order by a90_barcode,datetimes) b group by a90_barcode) b where a.id = b.id and a.a90_barcode = b.a90_barcode and a.a90_barcode != '' ";
sqlParameter.AddParameter("a90_model", inVo.ModelCode);
sqlParameter.AddParameter("line", inVo.LineCode);
if (inVo.change)
{
sqlChung += " and a.a90_thurst_status = 'OK' ";
}
else
{
sqlChung += " and a.a90_thurst_status = 'NG' ";
}
sqlChung += ") tbl where datetimes >= ";
sql.Append(@"select '01:00:00' " + sqlChung + " '" + inVo.Date + " 00:00:01' and datetimes <= '" + inVo.Date + " 01:00:00'");
sql.Append(" union ");
sql.Append(@"select '02:00:00' " + sqlChung + " '" + inVo.Date + " 01:00:01' and datetimes <= '" + inVo.Date + " 02:00:00'");
sql.Append(" union ");
sql.Append(@"select '03:00:00' " + sqlChung + " '" + inVo.Date + " 02:00:01' and datetimes <= '" + inVo.Date + " 03:00:00'");
sql.Append(" union ");
sql.Append(@"select '04:00:00' " + sqlChung + " '" + inVo.Date + " 03:00:01' and datetimes <= '" + inVo.Date + " 04:00:00'");
sql.Append(" union ");
sql.Append(@"select '05:00:00' " + sqlChung + " '" + inVo.Date + " 04:00:01' and datetimes <= '" + inVo.Date + " 05:00:00'");
sql.Append(" union ");
sql.Append(@"select '06:00:00' " + sqlChung + " '" + inVo.Date + " 05:00:01' and datetimes <= '" + inVo.Date + " 06:00:00'");
sql.Append(" union ");
sql.Append(@"select '07:00:00' " + sqlChung + " '" + inVo.Date + " 06:00:01' and datetimes <= '" + inVo.Date + " 07:00:00'");
sql.Append(" union ");
sql.Append(@"select '08:00:00' " + sqlChung + " '" + inVo.Date + " 07:00:01' and datetimes <= '" + inVo.Date + " 08:00:00'");
sql.Append(" union ");
sql.Append(@"select '09:00:00' " + sqlChung + " '" + inVo.Date + " 08:00:01' and datetimes <= '" + inVo.Date + " 09:00:00'");
sql.Append(" union ");
sql.Append(@"select '10:00:00' " + sqlChung + " '" + inVo.Date + " 09:00:01' and datetimes <= '" + inVo.Date + " 10:00:00'");
sql.Append(" union ");
sql.Append(@"select '11:00:00' " + sqlChung + " '" + inVo.Date + " 10:00:01' and datetimes <= '" + inVo.Date + " 11:00:00'");
sql.Append(" union ");
sql.Append(@"select '12:00:00' " + sqlChung + " '" + inVo.Date + " 11:00:01' and datetimes <= '" + inVo.Date + " 12:00:00'");
sql.Append(" union ");
sql.Append(@"select '13:00:00' " + sqlChung + " '" + inVo.Date + " 12:00:01' and datetimes <= '" + inVo.Date + " 13:00:00'");
sql.Append(" union ");
sql.Append(@"select '14:00:00' " + sqlChung + " '" + inVo.Date + " 13:00:01' and datetimes <= '" + inVo.Date + " 14:00:00'");
sql.Append(" union ");
sql.Append(@"select '15:00:00' " + sqlChung + " '" + inVo.Date + " 14:00:01' and datetimes <= '" + inVo.Date + " 15:00:00'");
sql.Append(" union ");
sql.Append(@"select '16:00:00' " + sqlChung + " '" + inVo.Date + " 15:00:01' and datetimes <= '" + inVo.Date + " 16:00:00'");
sql.Append(" union ");
sql.Append(@"select '17:00:00' " + sqlChung + " '" + inVo.Date + " 16:00:01' and datetimes <= '" + inVo.Date + " 17:00:00'");
sql.Append(" union ");
sql.Append(@"select '18:00:00' " + sqlChung + " '" + inVo.Date + " 17:00:01' and datetimes <= '" + inVo.Date + " 18:00:00'");
sql.Append(" union ");
sql.Append(@"select '19:00:00' " + sqlChung + " '" + inVo.Date + " 18:00:01' and datetimes <= '" + inVo.Date + " 19:00:00'");
sql.Append(" union ");
sql.Append(@"select '20:00:00' " + sqlChung + " '" + inVo.Date + " 19:00:01' and datetimes <= '" + inVo.Date + " 20:00:00'");
sql.Append(" union ");
sql.Append(@"select '21:00:00' " + sqlChung + " '" + inVo.Date + " 20:00:01' and datetimes <= '" + inVo.Date + " 21:00:00'");
sql.Append(" union ");
sql.Append(@"select '22:00:00' " + sqlChung + " '" + inVo.Date + " 21:00:01' and datetimes <= '" + inVo.Date + " 22:00:00'");
sql.Append(" union ");
sql.Append(@"select '23:00:00' " + sqlChung + " '" + inVo.Date + " 22:00:01' and datetimes <= '" + inVo.Date + " 23:00:00'");
sql.Append(" union ");
sql.Append(@"select '23:59:59' " + sqlChung + " '" + inVo.Date + " 23:00:01' and datetimes <= '" + inVo.Date + " 23:59:59'");
sql.Append(" order by times ");
sqlParameter.AddParameter("datefrom", inVo.DateFrom);
sqlParameter.AddParameter("dateto", inVo.DateTo);
//if (!string.IsNullOrEmpty(inVo.LineCode))
//{
// sql.Append(@" and a90_line =:line");
// sqlParameter.AddParameterString("line", inVo.LineCode);
//}
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
DataSet ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter);
//execute SQL
ProductionControllerGA1Vo outVo1 = new ProductionControllerGA1Vo
{
dt = ds.Tables[0],
};
return(outVo1);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
StringBuilder sql = new StringBuilder();
ProductionControllerGA1Vo voList = new ProductionControllerGA1Vo();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
string sqlChung = " times, model,line, process,sum(inspectdata) inspectdata from (select a.serno,a.model,a.line, a.process,sum(inspectdata) inspectdata,a.inspectdate from " + inVo.TableName + " a left join " + inVo.TableName + "data b on a.serno = b.serno where model = :model and line = :line and a.inspectdate = b.inspectdate and a.inspectdate >= :datefrom and a.inspectdate <= :dateto group by a.serno,a.model,a.line, a.process,a.inspectdate order by a.inspectdate) tbl where process != 'MC_NOICHK' and inspectdate >= ";
sqlParameter.AddParameter("model", inVo.ModelCode);
sqlParameter.AddParameter("line", inVo.LineCode);
sql.Append("select '01:00:00'" + sqlChung + " '" + inVo.Date + " 00:00:01' and inspectdate <= '" + inVo.Date + " 01:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '02:00:00'" + sqlChung + " '" + inVo.Date + " 01:00:01' and inspectdate <= '" + inVo.Date + " 02:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '03:00:00'" + sqlChung + " '" + inVo.Date + " 02:00:01' and inspectdate <= '" + inVo.Date + " 03:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '04:00:00'" + sqlChung + " '" + inVo.Date + " 03:00:01' and inspectdate <= '" + inVo.Date + " 04:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '05:00:00'" + sqlChung + " '" + inVo.Date + " 04:00:01' and inspectdate <= '" + inVo.Date + " 05:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '06:00:00'" + sqlChung + " '" + inVo.Date + " 05:00:01' and inspectdate <= '" + inVo.Date + " 06:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '07:00:00'" + sqlChung + " '" + inVo.Date + " 06:00:01' and inspectdate <= '" + inVo.Date + " 07:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '08:00:00'" + sqlChung + " '" + inVo.Date + " 07:00:01' and inspectdate <= '" + inVo.Date + " 08:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '09:00:00'" + sqlChung + " '" + inVo.Date + " 08:00:01' and inspectdate <= '" + inVo.Date + " 09:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '10:00:00'" + sqlChung + " '" + inVo.Date + " 09:00:01' and inspectdate <= '" + inVo.Date + " 10:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '11:00:00'" + sqlChung + " '" + inVo.Date + " 10:00:01' and inspectdate <= '" + inVo.Date + " 11:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '12:00:00'" + sqlChung + " '" + inVo.Date + " 11:00:01' and inspectdate <= '" + inVo.Date + " 12:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '13:00:00'" + sqlChung + " '" + inVo.Date + " 12:00:01' and inspectdate <= '" + inVo.Date + " 13:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '14:00:00'" + sqlChung + " '" + inVo.Date + " 13:00:01' and inspectdate <= '" + inVo.Date + " 14:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '15:00:00'" + sqlChung + " '" + inVo.Date + " 14:00:01' and inspectdate <= '" + inVo.Date + " 15:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '16:00:00'" + sqlChung + " '" + inVo.Date + " 15:00:01' and inspectdate <= '" + inVo.Date + " 16:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '17:00:00'" + sqlChung + " '" + inVo.Date + " 16:00:01' and inspectdate <= '" + inVo.Date + " 17:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '18:00:00'" + sqlChung + " '" + inVo.Date + " 17:00:01' and inspectdate <= '" + inVo.Date + " 18:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '19:00:00'" + sqlChung + " '" + inVo.Date + " 18:00:01' and inspectdate <= '" + inVo.Date + " 19:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '20:00:00'" + sqlChung + " '" + inVo.Date + " 19:00:01' and inspectdate <= '" + inVo.Date + " 20:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '21:00:00'" + sqlChung + " '" + inVo.Date + " 20:00:01' and inspectdate <= '" + inVo.Date + " 21:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '22:00:00'" + sqlChung + " '" + inVo.Date + " 21:00:01' and inspectdate <= '" + inVo.Date + " 22:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '23:00:00'" + sqlChung + " '" + inVo.Date + " 22:00:01' and inspectdate <= '" + inVo.Date + " 23:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '23:59:59'" + sqlChung + " '" + inVo.Date + " 23:00:01' and inspectdate <= '" + inVo.Date + " 23:59:59' group by model,line, process");
sql.Append(" order by times, process ");
sqlParameter.AddParameter("datefrom", inVo.DateFrom);
sqlParameter.AddParameter("dateto", inVo.DateTo);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
DataSet ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter);
//execute SQL
ProductionControllerGA1Vo outVo1 = new ProductionControllerGA1Vo
{
dt = ds.Tables[0],
};
return(outVo1);
}
