//插入数据
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);
}
}