public DBDataSet.TA_PTableUsableColumnsDataTable MinutesToTime(DBDataSet.TA_PTableUsableColumnsDataTable table)
{
for (int i = 1; i < table.Rows.Count; i++)
{
for (int j = 0; j < table.Columns.Count; j++)
{
if (IsHourlyColumn(j))
{
string col = table.Rows[i][j].ToString();
int intCol = 0;
if (col.Length > 0 && col != "-1000" && Int32.TryParse(col, out intCol) && intCol > 0)
{
if (table.Rows[i][1].ToString().Split('/')[2] == "00")
{
col = GTS.Clock.Infrastructure.Utility.Utility.IntTimeToTime(intCol);
}
else
{
col = GTS.Clock.Infrastructure.Utility.Utility.IntTimeToRealTime(intCol);
}
table.Rows[i][j] = col;
}
}
}
}
return(table);
}
public DBDataSet.TA_PTableUsableColumnsDataTable GetPTableUsableColumns(string barcode, string fromDate, string toDate, string description, bool fromSavedTable)
{
try
{
string tableName1 = GetPtableFromDate(toDate);
string tableName2 = GetPtableFromDate(fromDate);
if (tableName1 == "" || tableName2 == "")
{
return(null);
}
if (fromSavedTable)
{
tableName1 += "_saved";
tableName2 += "_saved";
}
DBDataSet.TA_PTableUsableColumnsDataTable ptable = new DBDataSet.TA_PTableUsableColumnsDataTable();
string desc = "";
if (description.Length > 0)
{
desc = "'" + description + "' as [Operator],";
}
string sql = String.Format(@"SELECT {0}{1} from {2} where prc_pcode={3} and Prc_Date <='{4}' and Prc_Date <>'{5}' order by Prc_Date ", desc, UsableColumns, tableName1, barcode.PadLeft(8, '0'), toDate, GetMonthDate(toDate));
SqlCommand command = new SqlCommand(sql, Connection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DBDataSet.TA_PTableUsableColumnsDataTable ptable1 = new DBDataSet.TA_PTableUsableColumnsDataTable();
adapter.Fill(ptable1);
sql = String.Format(@"SELECT {0}{1} from {2} where prc_pcode={3} and Prc_Date ='{4}' order by Prc_Date ", desc, UsableColumns, tableName1, barcode.PadLeft(8, '0'), GetMonthDate(toDate));
command.CommandText = sql;
adapter = new SqlDataAdapter(command);
DBDataSet.TA_PTableUsableColumnsDataTable monthPtable = new DBDataSet.TA_PTableUsableColumnsDataTable();
adapter.Fill(monthPtable);
if (tableName1 == tableName2)
{
ptable = AppendPTable(monthPtable, ptable1);
}
else
{
sql = String.Format(@"SELECT {0}{1} from {2} where prc_pcode={3} and Prc_Date >='{4}' order by Prc_Date ", desc, UsableColumns, tableName2, barcode.PadLeft(8, '0'), fromDate);
command.CommandText = sql;
adapter = new SqlDataAdapter(command);
DBDataSet.TA_PTableUsableColumnsDataTable ptable2 = new DBDataSet.TA_PTableUsableColumnsDataTable();
adapter.Fill(ptable2);
ptable = AppendPTable(monthPtable, ptable2);
ptable = AppendPTable(ptable, ptable1);
}
return(ptable);
}
finally
{
Connection.Close();
}
}
private void Compaire()
{
helper.InitTA_PTable();
DataSet.DBDataSetTableAdapters.TA_CompaireDiffrenceTableAdapter cdTA = new DataSet.DBDataSetTableAdapters.TA_CompaireDiffrenceTableAdapter();
cdTA.Connection = GTSAppSettings.SQLConnection;
for (int k = 0; k < calObjectList.Count; k++)
{
CalcObject objcet = calObjectList[k];
DBDataSet.TA_PTableUsableColumnsDataTable ta_ptable = new DBDataSet.TA_PTableUsableColumnsDataTable();
DataSet.DBDataSetTableAdapters.TA_PTableUsableColumnsTableAdapter ptableTA = new DataSet.DBDataSetTableAdapters.TA_PTableUsableColumnsTableAdapter();
ptableTA.Connection = GTSAppSettings.SQLConnection;
ptableTA.Fill(ta_ptable);
DBDataSet.TA_PTableUsableColumnsDataTable ptable1 = helper.GetPTableUsableColumns(objcet.barcode, objcet.fromDate, objcet.toDate, "Clock6", true);
DBDataSet.TA_PTableUsableColumnsDataTable ptable2 = helper.GetPTableUsableColumns(objcet.barcode, objcet.fromDate, objcet.toDate, "GTS", false);
DBDataSet.TA_PTableUsableColumnsDataTable ptable = helper.AppendPTable(ptable1, ptable2);
string monthDate = objcet.toDate;
monthDate = monthDate.Remove(monthDate.Length - 2, 2);
monthDate += "00";
ptable.DefaultView.Sort = "prc_date";
ta_ptable = helper.AppendPTable(ta_ptable, ptable.DefaultView);
bool[,] deffMat = helper.GetDifferenceIndex(ta_ptable, objcet.barcode);
int diffCount = 0;
int difMonthCount = 0;
for (int i = 1; i < deffMat.GetLength(0); i++)
{
for (int j = 0; j < deffMat.GetLength(1) - 1; j++)
{
if ((ta_ptable.Rows[i][1].ToString() == monthDate) && (j == 2 || j == 3 || j == 4 || j == 21))
{
continue;
}
if (ta_ptable.Rows[i][1].ToString() == monthDate && deffMat[i, j])
{
difMonthCount++;
}
if (deffMat[i, j])
{
diffCount++;
}
}
}
diffCount = diffCount / 2;
cdTA.Insert(objcet.barcode, diffCount, difMonthCount, objcet.fromDate, objcet.toDate);
}
}
public DBDataSet.TA_PTableUsableColumnsDataTable AppendPTable(DBDataSet.TA_PTableUsableColumnsDataTable table1, DBDataSet.TA_PTableUsableColumnsDataTable table2)
{
try
{
DBDataSet.TA_PTableUsableColumnsDataTable table = new DBDataSet.TA_PTableUsableColumnsDataTable();
table.Merge(table1);
table.Merge(table2);
return(table);
}
finally
{
Connection.Close();
}
}
public DBDataSet.TA_PTableUsableColumnsDataTable GetPTableUsableColumns(string barcode, string tableName, string description)
{
try
{
string desc = "";
if (description.Length > 0)
{
desc = "'" + description + "' as [Operator],";
}
string sql = String.Format(@"SELECT {0}{1} from {2} where prc_pcode={3}", desc, UsableColumns, tableName, barcode.PadLeft(8, '0'));
SqlCommand command = new SqlCommand(sql, Connection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DBDataSet.TA_PTableUsableColumnsDataTable ptable = new DBDataSet.TA_PTableUsableColumnsDataTable();
adapter.Fill(ptable);
return(ptable);
}
finally
{
Connection.Close();
}
}
