This script requires version SP7 or above.
int stationId = -1, areaId = -1, trackingPointId = -1;
bool okay = true;
string cmd = string.Empty, errorText = string.Empty;
DateTime currDate = DateTime.Now;
int currShiftId = -1;
float currProdHour = -1f;
try
{
string connectString = "Data Source=ATSUSNB074;Database=Inspect;User Id=sa;Password=master;";
var sql = new AdoSql(connectString);
//
// Get relevant station info for given code
//
try
{
cmd =
" select station_id, area_id" +
" from stations" +
" where code = ?"; // ScriptInfo.value("station_code")
sql.SetCommand(cmd);
sql.AddParameter("code", "MPI1");
if (sql.OpenDataset())
{
if (sql.Read())
{
sql.GetField(0, out stationId);
sql.GetField(1, out areaId);
// WriteLine("stationId: {0}", stationId);
// WriteLine("areaId: {0}", areaId);
}
}
sql.CloseDataset();
// Get tracking point for station
cmd =
" select cast(property_value as int)" +
" from station_properties" +
" where station_id = ?" +
" and property_id = 36";
sql.SetCommand(cmd);
sql.AddParameter("stationId", stationId);
sql.OpenDatasetGetSingleValue(out trackingPointId, -1);
}
catch (Exception ex)
{
errorText +=
string.Format(
"========================================\n" +
"ADO Error!\nDescription [{0}]\n" +
"Cmd [{1}]\n" +
"========================================\n",
ex.Message, cmd);
WriteLine("Exception: " + errorText);
okay = false;
}
//
// Get day and shift
//
if (okay)
{
try
{
cmd =
" select actual_date, shift_id, prod_hour" +
" from dbo.get_datetime_info_for_area(7, getdate())";
sql.SetCommand(cmd, ATS.Shared.ADO.NET.CommandType.Text);
if (sql.OpenDataset())
{
if (sql.Read())
{
sql.GetField(0, out currDate);
sql.GetField(1, out currShiftId);
sql.GetField(2, out currProdHour);
}
}
sql.CloseDataset();
// WriteLine("currDate: {0}", currDate);
// WriteLine("currShiftId: {0}", currShiftId);
// WriteLine("currProdHour: {0}", currProdHour);
}
catch (Exception ex)
{
errorText +=
string.Format(
"========================================\n" +
"ADO Error!\nDescription [{0}]\n" +
"Cmd [{1}]\n" +
"========================================\n",
ex.Message, cmd);
okay = false;
}
}
//
// Calculate the direct run (i.e. Pct OK)
// To do so we'll need the sample + defects
// for each hour of the current production date
//
if (okay)
{
var data = new Dictionary<Int32, System.Tuple<Int32, Int32, Int32>>();
for (int i = 0; i < 24; i++)
{
data[i] = Tuple.Create(0, 0, 0);
}
//
// Samples
//
try
{
cmd =
" select {fn hour(actual_date)}, count(*)" +
" from tracking_point_passes" +
" where tracking_point_id = ?" +
" and prod_date = ?" +
" group by {fn hour(actual_date)}";
sql.SetCommand(cmd);
sql.AddParameter("trackingPointId", trackingPointId);
sql.AddParameter("prodDate", currDate);
if (sql.OpenDataset())
{
Int32 hour;
Int32 sample;
while (sql.Read())
{
sql.GetField(0, out hour);
sql.GetField(1, out sample);
data[hour] = new Tuple<int, int, int>(sample, 0, 0);
}
}
sql.CloseDataset();
}
catch (Exception ex)
{
errorText +=
string.Format(
"========================================\n" +
"ADO Error!\nDescription [{0}]\n" +
"Cmd [{1}]\n" +
"========================================\n",
ex.Message, cmd);
okay = false;
}
//
// Defects and Bad Units
//
try
{
cmd =
" select {fn hour(actual_date)}, sum(qty), count(distinct unit_id)" +
" from defects" +
" where recording_station_id = ?" +
" and prod_date = ?" +
" group by {fn hour(actual_date)}";
sql.AddParameter("stationId", stationId);
sql.AddParameter("prodDate", currDate);
if (sql.OpenDataset())
{
Int32 hour, qty, badUnits;
while (sql.Read())
{
sql.GetField(0, out hour);
sql.GetField(1, out qty);
sql.GetField(2, out badUnits);
data[hour] = new Tuple<int, int, int>(data[hour].Item1, qty, badUnits);
}
}
sql.CloseDataset();
}
catch (Exception ex)
{
errorText +=
string.Format(
"========================================\n" +
"ADO Error!\nDescription [{0}]\n" +
"Cmd [{1}]\n" +
"========================================\n",
ex.Message, cmd);
okay = false;
}
//
// Final Output
//
// Header
WriteLine("Hour Sample Defects Bad Units Pct OK");
WriteLine("----- --------- --------- ----------- ------");
// 5am - 11pm
for (int hour = 5; hour < 24; hour++)
{
var info = data[hour];
float sample = data[hour].Item1;
float defects = data[hour].Item2;
float badUnits = data[hour].Item3;
var tempValue = (sample > badUnits) ? (sample - badUnits) : 0;
if (sample > 0f)
{
float pctOk = (tempValue / sample) * 100.0f;
WriteLine("{0,-5}{1,7:F0}{2,11:F0}{3,12:F0}{4,9:F2}", hour, sample, defects, badUnits, pctOk);
}
else if (defects == 0.0f)
{
WriteLine("{0,-5}{1,7:F0}{2,11:F0}{3,12:F0}{4,9:F2}", hour, sample, defects, badUnits, 100.0f);
}
else
{
WriteLine("???.??");
}
}
}
}
catch (Exception ex)
{
WriteLine("Exception: {0}", ex.Message);
}
Can we improve this topic?