//查询数据 public static KPIStatisticsData SelectKPIStatisticsData(DataBaseConfig ADataBaseConfig, GlobalSetting AGlobalSetting, int ATabelNumber, long AKPIMapingID, long AStartTimeLocal, long AObjectID, int ARowID = -1, int ASliceType = -1, int AColumnNumber = -1) { KPIStatisticsData dataKPIStatisticsTemp = new KPIStatisticsData(); DataTable LDataTableReturn = new DataTable(); string LStrDynamicSQL = string.Empty; string LStrRentToken = string.Empty; string LStrSingleObject = string.Empty; string TableName = string.Empty; string ColumnName = string.Empty; string ColumnNameTrend = string.Empty; string ColumnNameCompareProior = string.Empty; TableName = string.Format("T_46_01{0}_{1}", ATabelNumber, AGlobalSetting.StrRent); try { DatabaseOperation01Return LDatabaseOperationReturn = new DatabaseOperation01Return(); DataOperations01 LDataOperations = new DataOperations01(); switch (ATabelNumber) { case 1: { ColumnName = string.Format("C{0}01", AColumnNumber.ToString("00")); ColumnNameTrend = string.Format("C{0}04", AColumnNumber.ToString("00")); ColumnNameCompareProior = string.Format("C{0}09", AColumnNumber.ToString("00")); LStrDynamicSQL = string.Format("SELECT * FROM {0} WHERE C001={1} AND C002={4} AND C003={2} AND C004={5} AND C007={3} " , TableName, AKPIMapingID,//1 AObjectID, AStartTimeLocal, ARowID,//4 ASliceType); } break; case 2: case 3: case 4: case 5: { LStrDynamicSQL = string.Format("SELECT * FROM {0} WHERE C001={1} AND C002={2} AND C005={3} " , TableName, AKPIMapingID, AObjectID, AStartTimeLocal); } break; } LDatabaseOperationReturn = LDataOperations.SelectDataByDynamicSQL(ADataBaseConfig.IntDatabaseType, ADataBaseConfig.StrDatabaseProfile, LStrDynamicSQL); if (!LDatabaseOperationReturn.BoolReturn) { LDataTableReturn = null; } else { LDataTableReturn = LDatabaseOperationReturn.DataSetReturn.Tables[0]; if (LDataTableReturn.Rows.Count == 0) { return(null); } foreach (DataRow dr in LDataTableReturn.Rows) { dataKPIStatisticsTemp.KPIMappingID = AKPIMapingID; dataKPIStatisticsTemp.ObjectID = AObjectID; dataKPIStatisticsTemp.StartTimeLocal = AStartTimeLocal; if (ATabelNumber == 1) { dataKPIStatisticsTemp.ActualValue = DecimalParse(dr[ColumnName].ToString(), 0); dataKPIStatisticsTemp.Trend1 = DecimalParse(dr[ColumnNameTrend].ToString(), 0); dataKPIStatisticsTemp.ComparePrior = DecimalParse(dr[ColumnNameCompareProior].ToString(), 0); } else { dataKPIStatisticsTemp.ActualValue = DecimalParse(dr["C101"].ToString(), 0); dataKPIStatisticsTemp.Trend1 = DecimalParse(dr["C104"].ToString(), 0); dataKPIStatisticsTemp.ComparePrior = DecimalParse(dr["C109"].ToString(), 0); } } } } catch (Exception ex) { LDataTableReturn = null; FileLog.WriteInfo("SelectKPIStatisticsData()", ex.Message); } return(dataKPIStatisticsTemp); }
//插入数据 public static void InsertKpiStatisticsSliceData(DataBaseConfig ADataBaseConfig, GlobalSetting AGlobalSetting, int TabelNumber, KPIStatisticsData ADataKPIStatistics) { DataTable LDataTableReturn = new DataTable(); string LStrDynamicSQL = string.Empty; string LStrRentToken = string.Empty; string LStrSingleObject = string.Empty; string TableName = string.Empty; string ColumnName = string.Empty; TableName = string.Format("T_46_01{0}_{1}", TabelNumber, AGlobalSetting.StrRent); try { switch (TabelNumber) { case 1: //分钟表 { string LC101 = string.Empty; string LC102 = string.Empty; string LC103 = string.Empty; string LC104 = string.Empty; string LC105 = string.Empty; string LC106 = string.Empty; string LC107 = string.Empty; string LC109 = string.Empty; if (ADataBaseConfig.IntDatabaseType == 2) { LC101 = string.Format("C{0}01", ADataKPIStatistics.ColumnOrder.ToString("00")); LC102 = string.Format("C{0}02", ADataKPIStatistics.ColumnOrder.ToString("00")); LC103 = string.Format("C{0}03", ADataKPIStatistics.ColumnOrder.ToString("00")); LC104 = string.Format("C{0}04", ADataKPIStatistics.ColumnOrder.ToString("00")); LC105 = string.Format("C{0}05", ADataKPIStatistics.ColumnOrder.ToString("00")); LC106 = string.Format("C{0}06", ADataKPIStatistics.ColumnOrder.ToString("00")); LC107 = string.Format("C{0}07", ADataKPIStatistics.ColumnOrder.ToString("00")); LC109 = string.Format("C{0}09", ADataKPIStatistics.ColumnOrder.ToString("00")); LStrDynamicSQL = string.Format("IF NOT EXISTS (SELECT * FROM {0} WHERE C001={1} AND C003={2} AND C007={5} AND C004={24} AND C002={25} ) INSERT INTO {0} (C001,C002,C003,C004,C005,C006,C007,C008,C009,C010,C011,C015 ,{17},{18},{19},{20},{21},{22},{23},{26} ) VALUES ({1},{25},{2},{24},{3},{4},{5},'{6}',{7},{8},{9},{28},{10},{11},'{12}',{13},{14},'{15}',{16},{27}) ELSE UPDATE {0} SET C008='{6}',{17}={10},{18}={11},{19}='{12}',{20}={13},{21}={14},{22}='{15}',{23}={16},{26}={27} WHERE C001={1} AND C003={2} AND C007={5} AND C004={24} AND C002={25} ", TableName,//0 ADataKPIStatistics.KPIMappingID, ADataKPIStatistics.ObjectID, ADataKPIStatistics.SliceInOrder,//3 ADataKPIStatistics.StartTimeUTC, ADataKPIStatistics.StartTimeLocal, ADataKPIStatistics.UpdateTime,//6 ADataKPIStatistics.Year, ADataKPIStatistics.Month, ADataKPIStatistics.Day, ADataKPIStatistics.ActualValue, //10 ADataKPIStatistics.Goal1, //11 ADataKPIStatistics.CompareSign1, ADataKPIStatistics.Trend1, ADataKPIStatistics.ActualCompareGoal1, ADataKPIStatistics.Show1, ADataKPIStatistics.Goal2,//16 LC101, LC102, LC103, LC104, LC105, LC106, LC107, ADataKPIStatistics.SliceType,//24 ADataKPIStatistics.RowID, LC109, ADataKPIStatistics.ComparePrior, ADataKPIStatistics.KPIID ); } else if (ADataBaseConfig.IntDatabaseType == 3) { LStrDynamicSQL = string.Format("BEGIN UPDATE {0} SET C008=TO_DATE ('{6}','YYYY-MM-DD HH24:MI:SS'),{17}={10},{18}={11},{19}='{12}',{20}={13},{21}={14},{22}='{15}',{23}={16},{26}={27} WHERE C001={1} AND C002={2} AND C007={5} AND C004={24} AND C002={25} ; IF SQL%NOTFOUND THEN INSERT INTO {0} (C001,C002,C003,C004,C005,C006,C007,C008,C009, C010,C011,C015 ,{17},{18},{19},{20},{21},{22},{23},{26} ) VALUES ({1},{25},{2},{24},{3},{4},{5},TO_DATE ('{6}','YYYY-MM-DD HH24:MI:SS'),{7},{8},{9},{28},{10},{11},'{12}',{13},{14},'{15}',{16},{27}); END IF;COMMIT; END;", TableName, //0 ADataKPIStatistics.KPIMappingID, ADataKPIStatistics.ObjectID, ADataKPIStatistics.SliceInOrder, ADataKPIStatistics.StartTimeUTC, ADataKPIStatistics.StartTimeLocal,//5 ADataKPIStatistics.UpdateTime, ADataKPIStatistics.Year, ADataKPIStatistics.Month, ADataKPIStatistics.Day, ADataKPIStatistics.ActualValue, //10 ADataKPIStatistics.Goal1, //11 ADataKPIStatistics.CompareSign1, ADataKPIStatistics.Trend1, ADataKPIStatistics.ActualCompareGoal1, ADataKPIStatistics.Show1, ADataKPIStatistics.Goal2, LC101, LC102, LC103, LC104, LC105, LC106, LC107, ADataKPIStatistics.SliceType, ADataKPIStatistics.RowID, LC109, ADataKPIStatistics.ComparePrior, ADataKPIStatistics.KPIID ); } } break; case 2: case 3: case 4: { if (ADataBaseConfig.IntDatabaseType == 2) { LStrDynamicSQL = string.Format("IF NOT EXISTS (SELECT * FROM {0} WHERE C001={1} AND C002={2} AND C005={5} ) INSERT INTO {0} (C001,C002,C003,C004,C005,C006,C007,C008,C009,C101,C102,C103,C104,C105,C106,C107 ,C109,C117) VALUES ({1},{2},{3},{4},{5},'{6}',{7},{8},{9},{10},{11},'{12}',{13},{14},'{15}',{16},{17},{18}) ELSE UPDATE {0} SET C006='{6}',C101={10},C102={11},C103='{12}',C104={13},C105={14},C106='{15}',C107={16},C109={17} WHERE C001={1} AND C002={2} AND C005={5} ", TableName, ADataKPIStatistics.KPIMappingID,//1 ADataKPIStatistics.ObjectID, ADataKPIStatistics.SliceInOrder, ADataKPIStatistics.StartTimeUTC, ADataKPIStatistics.StartTimeLocal,//5 ADataKPIStatistics.UpdateTime, ADataKPIStatistics.Year, ADataKPIStatistics.Month, ADataKPIStatistics.Day, ADataKPIStatistics.ActualValue, //10 ADataKPIStatistics.Goal1, //11 ADataKPIStatistics.CompareSign1, ADataKPIStatistics.Trend1, ADataKPIStatistics.ActualCompareGoal1, ADataKPIStatistics.Show1, ADataKPIStatistics.Goal2,//16 ADataKPIStatistics.ComparePrior, ADataKPIStatistics.KPIID ); } else if (ADataBaseConfig.IntDatabaseType == 3) { LStrDynamicSQL = string.Format("BEGIN UPDATE {0} SET C006=TO_DATE ('{6}','YYYY-MM-DD HH24:MI:SS'),C101={10},C102={11},C103='{12}',C104={13},C105={14},C106='{15}',C107={16},C109={17} WHERE C001={1} AND C002={2} AND C005={5} ; IF SQL%NOTFOUND THEN INSERT INTO {0} (C001,C002,C003,C004,C005,C006,C007,C008,C009,C101,C102,C103,C104,C105,C106,C107 ,C109,C117) VALUES ({1},{2},{3},{4},{5},TO_DATE ('{6}','YYYY-MM-DD HH24:MI:SS'),{7},{8},{9},{10},{11},'{12}',{13},{14},'{15}',{16},{17},{18}); END IF; COMMIT; END;", TableName, ADataKPIStatistics.KPIMappingID,//1 ADataKPIStatistics.ObjectID, ADataKPIStatistics.SliceInOrder, ADataKPIStatistics.StartTimeUTC, ADataKPIStatistics.StartTimeLocal,///5 ADataKPIStatistics.UpdateTime, ADataKPIStatistics.Year, ADataKPIStatistics.Month, ADataKPIStatistics.Day, ADataKPIStatistics.ActualValue, //10 ADataKPIStatistics.Goal1, //11 ADataKPIStatistics.CompareSign1, ADataKPIStatistics.Trend1, ADataKPIStatistics.ActualCompareGoal1, ADataKPIStatistics.Show1, ADataKPIStatistics.Goal2,//16); ADataKPIStatistics.ComparePrior, ADataKPIStatistics.KPIID); } } break; case 5: //年表 { if (ADataBaseConfig.IntDatabaseType == 2) { LStrDynamicSQL = string.Format("IF NOT EXISTS (SELECT * FROM {0} WHERE C001={1} AND C002={2} AND C005={5} ) INSERT INTO {0} (C001,C002,C003,C004,C005,C006,C101,C102,C103,C104,C105,C106,C107 ,C109,C117) VALUES ({1},{2},{3},{4},{5},'{6}',{7},{8},'{9}',{10},{11},'{12}',{13},{14},{15}) ELSE UPDATE {0} SET C006='{6}',C101={7},C102={8},C103='{9}',C104={10},C105={11},C106='{12}',C107={13},C109={14} WHERE C001={1} AND C002={2} AND C005={5} ", TableName, //0 ADataKPIStatistics.KPIMappingID, ADataKPIStatistics.ObjectID, //2 ADataKPIStatistics.SliceInOrder, ADataKPIStatistics.StartTimeUTC, ADataKPIStatistics.StartTimeLocal, ADataKPIStatistics.UpdateTime, //6 ADataKPIStatistics.ActualValue, //7 ADataKPIStatistics.Goal1, //8 ADataKPIStatistics.CompareSign1, ADataKPIStatistics.Trend1, ADataKPIStatistics.ActualCompareGoal1, ADataKPIStatistics.Show1, ADataKPIStatistics.Goal2, //13 ADataKPIStatistics.ComparePrior, ADataKPIStatistics.KPIID ); } else if (ADataBaseConfig.IntDatabaseType == 3) { LStrDynamicSQL = string.Format("BEGIN UPDATE {0} SET C006=TO_DATE ('{6}','YYYY-MM-DD HH24:MI:SS'),C101={7},C102={8},C103='{9}',C104={10},C105={11},C106='{12}',C107={13},C109={14} WHERE C001={1} AND C002={2} AND C005={5} ; IF SQL%NOTFOUND THEN INSERT INTO {0} (C001,C002,C003,C004,C005,C006,C101,C102,C103,C104,C105,C106,C107,C109 ,C117) VALUES ({1},{2},{3},{4},{5},TO_DATE ('{6}','YYYY-MM-DD HH24:MI:SS'),{7},{8},'{9}',{10},{11},'{12}',{13},{14},{15}); END IF; COMMIT; END;", TableName, //0 ADataKPIStatistics.KPIMappingID, ADataKPIStatistics.ObjectID, //2 ADataKPIStatistics.SliceInOrder, ADataKPIStatistics.StartTimeUTC, ADataKPIStatistics.StartTimeLocal, //5 ADataKPIStatistics.UpdateTime, //6 ADataKPIStatistics.ActualValue, //7 ADataKPIStatistics.Goal1, ADataKPIStatistics.CompareSign1, ADataKPIStatistics.Trend1,//10 ADataKPIStatistics.ActualCompareGoal1, ADataKPIStatistics.Show1, ADataKPIStatistics.Goal2,//13 ADataKPIStatistics.ComparePrior, ADataKPIStatistics.KPIID); } } break; default: break; } FileLog.WriteInfo("InsertKpiStatisticsSliceData()", LStrDynamicSQL); DatabaseOperation01Return LDatabaseOperationReturn = new DatabaseOperation01Return(); DataOperations01 LDataOperations = new DataOperations01(); LDatabaseOperationReturn = LDataOperations.ExecuteDynamicSQL(ADataBaseConfig.IntDatabaseType, ADataBaseConfig.StrDatabaseProfile, LStrDynamicSQL); if (!LDatabaseOperationReturn.BoolReturn) { FileLog.WriteInfo("InsertKpiStatisticsSliceData()", LStrDynamicSQL + "Error"); } } catch (Exception ex) { FileLog.WriteInfo("InsertKpiStatisticsSliceData()", "Error :" + ex.Message); } }