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