//插入数据 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); } }
/// <summary> /// 将数据插入QM的统计表 /// </summary> /// <param name="ADataBaseConfig"></param> /// <param name="AGlobalSetting"></param> /// <param name="TabelNumber"> 表示T_46_022,T_46_023,T_46_024,T_46_025</param> /// <param name="ADataFirstStatisticsSlice"></param> public static void InsertQMStatistics(DataBaseConfig ADataBaseConfig, GlobalSetting AGlobalSetting, int TabelNumber, DataFirstStatisticsSlice ADataFirstStatisticsSlice) { DataTable LDataTableReturn = new DataTable(); string LStrDynamicSQL = string.Empty; string LStrRentToken = string.Empty; string LStrSingleObject = string.Empty; string TableName = string.Empty; string ColumnName = string.Empty; int LTempOrder = 0;//天在当年的天数,天所在的周属于当年的第几周... switch (TabelNumber) { case 2: { LTempOrder = GetDayInYear(new DateTime(ADataFirstStatisticsSlice.Year, ADataFirstStatisticsSlice.Month, ADataFirstStatisticsSlice.Day)); TableName = "T_46_022"; ColumnName = "C10" + ADataFirstStatisticsSlice.OrderID; } break; case 3: { TableName = "T_46_023"; ColumnName = "C10" + ADataFirstStatisticsSlice.OrderID; } break; case 4: { LTempOrder = ADataFirstStatisticsSlice.Month; TableName = "T_46_024"; ColumnName = "C10" + ADataFirstStatisticsSlice.OrderID; } break; case 5: { LTempOrder = ADataFirstStatisticsSlice.Year; TableName = "T_46_025"; ColumnName = "C10" + ADataFirstStatisticsSlice.OrderID; } break; default: break; } try { if (ADataBaseConfig.IntDatabaseType == 2) { LStrDynamicSQL = string.Format("IF NOT EXISTS (SELECT * FROM {0} WHERE C001={1} AND C002='{2}' AND C003={3} AND C004={4} AND C006={6}) INSERT INTO {0} (C001,C002,C003,C004,C005,C006,C007,C008,C009,C010,C011,{12}) VALUES ({1},'{2}',{3},{4},{5},{6},'{7}',{8},{9},{10},1000,{11}) ELSE UPDATE {0} SET {12}={11},C011=1000 WHERE C001={1} AND C002='{2}' AND C003={3} AND C004={4} AND C006={6} ", TableName, ADataFirstStatisticsSlice.ObjectType, ADataFirstStatisticsSlice.StrRent, ADataFirstStatisticsSlice.ObjectID, LTempOrder, ADataFirstStatisticsSlice.StartTimeUTC, ADataFirstStatisticsSlice.StartTimeLocal, ADataFirstStatisticsSlice.UpdateTime, ADataFirstStatisticsSlice.Year, ADataFirstStatisticsSlice.Month, ADataFirstStatisticsSlice.Day, ADataFirstStatisticsSlice.Value01, ColumnName); } else { LStrDynamicSQL = string.Format("BEGIN UPDATE {0} SET {12}={11},C011=1000 WHERE C001={1} AND C002='{2}' AND C003={3} AND C004={4} AND C006={6};IF SQL%NOTFOUND THEN INSERT INTO {0}(C001,C002,C003,C004,C005,C006,C007,C008,C009,C010,C011,{12}) VALUES ({1},'{2}',{3},{4},{5},{6},TO_DATE('{7}','YYYY-MM-DD HH24:MI:SS'),{8},{9},{10},1000,{11}); END IF; COMMIT; END;", TableName, ADataFirstStatisticsSlice.ObjectType, ADataFirstStatisticsSlice.StrRent, ADataFirstStatisticsSlice.ObjectID, LTempOrder, ADataFirstStatisticsSlice.StartTimeUTC, ADataFirstStatisticsSlice.StartTimeLocal, ADataFirstStatisticsSlice.UpdateTime, ADataFirstStatisticsSlice.Year, ADataFirstStatisticsSlice.Month, ADataFirstStatisticsSlice.Day, ADataFirstStatisticsSlice.Value01, ColumnName); } FileLog.WriteInfo("InsertQMStatistics()", LStrDynamicSQL); DatabaseOperation01Return LDatabaseOperationReturn = new DatabaseOperation01Return(); DataOperations01 LDataOperations = new DataOperations01(); LDatabaseOperationReturn = LDataOperations.ExecuteDynamicSQL(ADataBaseConfig.IntDatabaseType, ADataBaseConfig.StrDatabaseProfile, LStrDynamicSQL); if (!LDatabaseOperationReturn.BoolReturn) { FileLog.WriteInfo("InsertQMStatistics()", LStrDynamicSQL + "Error"); } } catch (Exception ex) { FileLog.WriteInfo("InsertQMStatistics()", "Error :" + ex.Message); } }
//将数据插入录音的统计表 /// <summary> /// /// </summary> /// <param name="ADataBaseConfig"></param> /// <param name="AGlobalSetting"></param> /// <param name="TabelNumber"> 表示T_46_031,T_46_032,T_46_033,T_46_034,T_46_035</param> /// <param name="ADataFirstStatisticsSlice"></param> public static void InsertRecordStatistics(DataBaseConfig ADataBaseConfig, GlobalSetting AGlobalSetting, int TabelNumber, DataFirstStatisticsSlice ADataFirstStatisticsSlice) { 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_03{0}", TabelNumber); try { switch (TabelNumber) { case 1: { ColumnName = GetColumnName(ADataFirstStatisticsSlice.OrderID); } break; case 2: case 3: case 4: case 5: { ColumnName = "C1" + Convert.ToInt16(ADataFirstStatisticsSlice.RecordFunction).ToString("00"); } break; default: break; } ///在31表是C004为 数据类型 其它是C004是一年里的第几天 /// if (TabelNumber == 1) { if (ADataBaseConfig.IntDatabaseType == 2) { LStrDynamicSQL = string.Format(" IF NOT EXISTS (SELECT * FROM {0} WHERE C001={1} AND C002='{2}' AND C003={3} AND C004={4} AND C006={6} ) INSERT INTO {0} (C001,C002,C003,C004,C005,C006,C007,C008,C009,C010,C011,{12}) VALUES ({1},'{2}',{3},{4},{5},{6},'{7}',{8},{9},{10},1000,{11} ) ELSE UPDATE {0} SET {12}={11} WHERE C001={1} AND C002='{2}' AND C003={3} AND C004={4} AND C006={6} ", TableName, //0 ADataFirstStatisticsSlice.ObjectType, //1 ADataFirstStatisticsSlice.StrRent, ADataFirstStatisticsSlice.ObjectID, Convert.ToInt16(ADataFirstStatisticsSlice.RecordFunction), ADataFirstStatisticsSlice.StartTimeUTC, ADataFirstStatisticsSlice.StartTimeLocal, ADataFirstStatisticsSlice.UpdateTime, ADataFirstStatisticsSlice.Year, ADataFirstStatisticsSlice.Month, ADataFirstStatisticsSlice.Day, ADataFirstStatisticsSlice.Value01,//11 ColumnName ); } else { LStrDynamicSQL = string.Format("BEGIN UPDATE {0} SET {12}={11} WHERE C001={1} AND C002='{2}' AND C003={3} AND C004={4} AND C006={6} ; IF SQL%NOTFOUND THEN INSERT INTO {0} (C001,C002,C003,C004,C005,C006,C007,C008,C009,C010,C011,{12} ) VALUES ({1},'{2}',{3},{4},{5},{6},TO_DATE ('{7}','YYYY-MM-DD HH24:MI:SS'),{8},{9},{10},1000,{11}); END IF; COMMIT; END;", TableName, ADataFirstStatisticsSlice.ObjectType, ADataFirstStatisticsSlice.StrRent, ADataFirstStatisticsSlice.ObjectID, Convert.ToInt16(ADataFirstStatisticsSlice.RecordFunction), ADataFirstStatisticsSlice.StartTimeUTC, ADataFirstStatisticsSlice.StartTimeLocal, ADataFirstStatisticsSlice.UpdateTime, ADataFirstStatisticsSlice.Year, ADataFirstStatisticsSlice.Month, ADataFirstStatisticsSlice.Day, ADataFirstStatisticsSlice.Value01, ColumnName); } } else { if (ADataBaseConfig.IntDatabaseType == 2) { LStrDynamicSQL = string.Format(" IF NOT EXISTS (SELECT * FROM {0} WHERE C001={1} AND C002='{2}' AND C003={3} AND C004={4} AND C006={6} ) INSERT INTO {0} (C001,C002,C003,C004,C005,C006,C007,C008,C009,C010,C011,{12} ) VALUES ({1},'{2}',{3},{4},{5},{6},'{7}',{8},{9},{10},1000,{11}) ELSE UPDATE {0} SET {12}={11} WHERE C001={1} AND C002='{2}' AND C003={3} AND C004={4} AND C006={6} ", TableName, ADataFirstStatisticsSlice.ObjectType,//1 ADataFirstStatisticsSlice.StrRent, ADataFirstStatisticsSlice.ObjectID, 0,//一年里这周,这个月的顺序 ADataFirstStatisticsSlice.StartTimeUTC, ADataFirstStatisticsSlice.StartTimeLocal, ADataFirstStatisticsSlice.UpdateTime, ADataFirstStatisticsSlice.Year, ADataFirstStatisticsSlice.Month, ADataFirstStatisticsSlice.Day, ADataFirstStatisticsSlice.Value01, ColumnName); } else { LStrDynamicSQL = string.Format("BEGIN UPDATE {0} SET {12}={11} WHERE C001={1} AND C002='{2}' AND C003={3} AND C004={4} AND C006={6} ; IF SQL%NOTFOUND THEN INSERT INTO {0} (C001,C002,C003,C004,C005,C006,C007,C008,C009,C010,C011,{12} ) VALUES ({1},'{2}',{3},{4},{5},{6},TO_DATE ('{7}','YYYY-MM-DD HH24:MI:SS'),{8},{9},{10},1000,{11}) END IF;COMMIT; END;", TableName, ADataFirstStatisticsSlice.ObjectType, ADataFirstStatisticsSlice.StrRent, ADataFirstStatisticsSlice.ObjectID, 0, ADataFirstStatisticsSlice.StartTimeUTC, ADataFirstStatisticsSlice.StartTimeLocal, ADataFirstStatisticsSlice.UpdateTime, ADataFirstStatisticsSlice.Year, ADataFirstStatisticsSlice.Month, ADataFirstStatisticsSlice.Day, ADataFirstStatisticsSlice.Value01, ColumnName); } } FileLog.WriteInfo("InsertRecordStatistics()", LStrDynamicSQL); DatabaseOperation01Return LDatabaseOperationReturn = new DatabaseOperation01Return(); DataOperations01 LDataOperations = new DataOperations01(); LDatabaseOperationReturn = LDataOperations.ExecuteDynamicSQL(ADataBaseConfig.IntDatabaseType, ADataBaseConfig.StrDatabaseProfile, LStrDynamicSQL); if (!LDatabaseOperationReturn.BoolReturn) { FileLog.WriteInfo("InsertRecordStatistics()", LStrDynamicSQL + "Error"); } } catch (Exception ex) { FileLog.WriteInfo("InsertRecordStatistics()", "Error :" + ex.Message); } }