/// <summary>
/// 得到所有的用户信息
/// </summary>
/// <param name="AListUserInfo"></param>
/// <param name="AStrRent"></param>
public static void GetAllKPIMapping(DataBaseConfig ADataBaseConfig, GlobalSetting AGolbalSetting, ref List <KPIMapping> AListKPIDMapping)
{
AListKPIDMapping.Clear();
DataTable LDataTableReturn = new DataTable();
string LStrDynamicSQL = string.Empty;
string LStrRentToken = string.Empty;
string LStrSingleObject = string.Empty;
try
{
DatabaseOperation01Return LDatabaseOperationReturn = new DatabaseOperation01Return();
DataOperations01 LDataOperations = new DataOperations01();
LStrDynamicSQL = string.Format("SELECT * FROM T_46_003_{0} WHERE C006='1' AND C011='0' "
, AGolbalSetting.StrRent);
LDatabaseOperationReturn = LDataOperations.SelectDataByDynamicSQL(ADataBaseConfig.IntDatabaseType, ADataBaseConfig.StrDatabaseProfile, LStrDynamicSQL);
if (!LDatabaseOperationReturn.BoolReturn)
{
LDataTableReturn = null;
}
else
{
LDataTableReturn = LDatabaseOperationReturn.DataSetReturn.Tables[0];
foreach (DataRow dr in LDataTableReturn.Rows)
{
KPIMapping kpimappingtemp = new KPIMapping();
kpimappingtemp.KPIMappingID = LongParse(dr["C001"].ToString(), 0);
kpimappingtemp.KpiID = LongParse(dr["C002"].ToString(), 0);
kpimappingtemp.ObjectID = LongParse(dr["C003"].ToString(), 0);
kpimappingtemp.ActualApplyObjType = dr["C004"].ToString();
kpimappingtemp.ActualApplyCycle = dr["C005"].ToString();
kpimappingtemp.IsStart = dr["C006"].ToString();
kpimappingtemp.StatisticsStartTime = LongParse(dr["C007"].ToString(), 0);
kpimappingtemp.StatisticsStopTime = LongParse(dr["C008"].ToString(), 0);
kpimappingtemp.IsDrop = IntParse(dr["C009"].ToString(), 0);
kpimappingtemp.IsApplyAll = dr["C010"].ToString();
kpimappingtemp.IsDelete = IntParse(dr["C011"].ToString(), 0);
kpimappingtemp.IsStartGoal1 = dr["C014"].ToString();
kpimappingtemp.Goal1 = DecimalParse(dr["C015"].ToString(), 0);
kpimappingtemp.CompareSign1 = dr["C016"].ToString();
kpimappingtemp.IsStartGoal2 = dr["C018"].ToString();
kpimappingtemp.Goal2 = DecimalParse(dr["C019"].ToString(), 0);
kpimappingtemp.CompareSign2 = dr["C020"].ToString();
AListKPIDMapping.Add(kpimappingtemp);
}
}
}
catch (Exception ex)
{
LDataTableReturn = null;
FileLog.WriteInfo("GetAllKPIDefine()", ex.Message);
}
}
/// <summary>
/// 根据SQL语句返回查询结果集
/// </summary>
/// <param name="AIntDBType">数据库类型。2：MS SQL；3：Oracle</param>
/// <param name="AStrDBConnectProfile">数据库连接字符串</param>
/// <param name="AStrSQLString">SELECT 语句</param>
/// <returns></returns>
public DatabaseOperation01Return SelectDataByDynamicSQL(int AIntDBType, string AStrDBConnectProfile, string AStrSQLString)
{
DatabaseOperation01Return LClassReturn = new DatabaseOperation01Return();
SqlConnection LSqlConnection = null;
OracleConnection LOracleConnection = null;
try
{
if (AIntDBType == 2)
{
LSqlConnection = new SqlConnection(AStrDBConnectProfile);
LSqlConnection.Open();
SqlDataAdapter LSqlDataAdapter = new SqlDataAdapter(AStrSQLString, LSqlConnection);
LClassReturn.StrReturn = LSqlDataAdapter.Fill(LClassReturn.DataSetReturn).ToString();
LSqlDataAdapter.Dispose();
}
if (AIntDBType == 3)
{
LOracleConnection = new OracleConnection(AStrDBConnectProfile);
LOracleConnection.Open();
OracleDataAdapter LOracleDataAdapter = new OracleDataAdapter(AStrSQLString, LOracleConnection);
LClassReturn.StrReturn = LOracleDataAdapter.Fill(LClassReturn.DataSetReturn).ToString();
LOracleDataAdapter.Dispose();
}
}
catch (Exception ex)
{
LClassReturn.BoolReturn = false;
LClassReturn.StrReturn = "DataOperations01.SelectDataByDynamicSQL()\n" + ex.ToString();
}
finally
{
if (LSqlConnection != null)
{
if (LSqlConnection.State == System.Data.ConnectionState.Open)
{
LSqlConnection.Close();
}
LSqlConnection.Dispose();
}
if (LOracleConnection != null)
{
if (LOracleConnection.State == ConnectionState.Open)
{
LOracleConnection.Close();
}
LOracleConnection.Dispose(); LOracleConnection = null;
}
}
return(LClassReturn);
}
/// <summary>
/// 得到所有的用户信息
/// </summary>
/// <param name="AListUserInfo"></param>
/// <param name="AStrRent"></param>
public static void GetAllKPIDefine(DataBaseConfig ADataBaseConfig, GlobalSetting AGolbalSetting, ref List <KPIDefine> AListKPIDefineInfo)
{
AListKPIDefineInfo.Clear();
DataTable LDataTableReturn = new DataTable();
string LStrDynamicSQL = string.Empty;
string LStrRentToken = string.Empty;
string LStrSingleObject = string.Empty;
try
{
DatabaseOperation01Return LDatabaseOperationReturn = new DatabaseOperation01Return();
DataOperations01 LDataOperations = new DataOperations01();
LStrDynamicSQL = string.Format("SELECT * FROM T_46_001_{0} WHERE C009='1' "
, AGolbalSetting.StrRent);
LDatabaseOperationReturn = LDataOperations.SelectDataByDynamicSQL(ADataBaseConfig.IntDatabaseType, ADataBaseConfig.StrDatabaseProfile, LStrDynamicSQL);
if (!LDatabaseOperationReturn.BoolReturn)
{
LDataTableReturn = null;
}
else
{
LDataTableReturn = LDatabaseOperationReturn.DataSetReturn.Tables[0];
foreach (DataRow dr in LDataTableReturn.Rows)
{
KPIDefine kpiInfoTemp = new KPIDefine();
kpiInfoTemp.KpiID = LongParse(dr["C001"].ToString(), 0);
kpiInfoTemp.KpiName = dr["C002"].ToString();
kpiInfoTemp.ApplyObject = dr["C006"].ToString();
kpiInfoTemp.KpiType = IntParse(dr["C007"].ToString(), 0);
kpiInfoTemp.IsSystemContain = dr["C008"].ToString();
kpiInfoTemp.IsStart = dr["C009"].ToString();
kpiInfoTemp.ValueType = dr["C010"].ToString();
kpiInfoTemp.NewFormula = dr["C012"].ToString();
AListKPIDefineInfo.Add(kpiInfoTemp);
}
}
}
catch (Exception ex)
{
LDataTableReturn = null;
FileLog.WriteInfo("GetAllKPIDefine()", ex.Message);
}
}
/// <summary>
/// 得到录音表年的数据
/// </summary>
/// <param name="ADataBaseConfig"></param>
/// <param name="AObjectInfo"></param>
/// <param name="AGlobalSetting"></param>
/// <param name="StartTimeLocal"></param>
/// <param name="StopTimeLocal"></param>
/// <param name="AFuncType"></param>
/// <param name="AExtensionAgentType"></param>
/// <returns></returns>
public static long GetYearQMStatistics(DataBaseConfig ADataBaseConfig, ObjectInfo AObjectInfo, GlobalSetting AGlobalSetting, long StartTimeLocal, long StopTimeLocal, int AFuncType, int AExtensionAgentType)
{
long Value01 = 0;
DataTable LDataTableReturn = new DataTable();
string LStrDynamicSQL = string.Empty;
string LStrRentToken = string.Empty;
string LStrSingleObject = string.Empty;
string LColumnName = string.Format("C10{0}", AFuncType);
try
{
LStrDynamicSQL = string.Format("SELECT SUM({0}) AS VALUE01 FROM T_46_024 WHERE C001={1} AND C002={2} AND C003={3} AND C006>={5} AND C006<{6} ",
LColumnName,
AExtensionAgentType,
AGlobalSetting.StrRent,
AObjectInfo.ObjID,
AFuncType,
StartTimeLocal,
StopTimeLocal);
FileLog.WriteInfo("GetYearQMStatistics()", LStrDynamicSQL);
DatabaseOperation01Return LDatabaseOperationReturn = new DatabaseOperation01Return();
DataOperations01 LDataOperations = new DataOperations01();
LDatabaseOperationReturn = LDataOperations.SelectDataByDynamicSQL(ADataBaseConfig.IntDatabaseType, ADataBaseConfig.StrDatabaseProfile, LStrDynamicSQL);
if (!LDatabaseOperationReturn.BoolReturn)
{
LDataTableReturn = null;
}
else
{
LDataTableReturn = LDatabaseOperationReturn.DataSetReturn.Tables[0];
foreach (DataRow LDataRowSingleRow in LDataTableReturn.Rows)
{
Value01 += LongParse(LDataRowSingleRow["VALUE01"].ToString(), 0);
}
}
}
catch (Exception ex)
{
FileLog.WriteInfo("GetYearQMStatistics()", "Error :" + ex.Message);
}
return(Value01);
}
//得到录音初统计表成绩初统计表切片的天以上切片的数据的统计
/// <summary>
///
/// </summary>
/// <param name="ADataBaseConfig"></param>
/// <param name="AGlobalSetting"></param>
/// <param name="TableName"></param>
/// <param name="ColumnName"></param>
/// <param name="ObjectType">// 1座席 2分机 3用户 4真实分机 5机构 6 技能组</param>
/// <param name="StartTimeLocal"></param>
/// <param name="StopTimeLocal"></param>
/// <param name="ObjectSerialID"></param>
/// <returns></returns>
public static double GetStatisticsValueDayUp(DataBaseConfig ADataBaseConfig, GlobalSetting AGlobalSetting, string TableName, string ColumnName, int ObjectType, string StartTimeLocal, string StopTimeLocal, string ObjectSerialID)
{
double Value01 = 0;
DataTable LDataTableReturn = new DataTable();
string LStrDynamicSQL = string.Empty;
string LStrRentToken = AGlobalSetting.StrRent;
string LStrSingleObject = string.Empty;
string LColumnName = ColumnName;
try
{
LStrDynamicSQL = string.Format("SELECT ISNULL(SUM({0}),0) AS Value01 FROM {6} WHERE C001={1} AND C002={2} AND C003 IN (SELECT C011 FROM T_00_901 WHERE C001 = {3}) AND C006>={4} AND C006<{5} ",
LColumnName,
ObjectType,
LStrRentToken,
ObjectSerialID,
StartTimeLocal,
StopTimeLocal,
TableName);
FileLog.WriteInfo("GetDayRecordStatistics()", LStrDynamicSQL);
DatabaseOperation01Return LDatabaseOperationReturn = new DatabaseOperation01Return();
DataOperations01 LDataOperations = new DataOperations01();
LDatabaseOperationReturn = LDataOperations.SelectDataByDynamicSQL(ADataBaseConfig.IntDatabaseType, ADataBaseConfig.StrDatabaseProfile, LStrDynamicSQL);
if (!LDatabaseOperationReturn.BoolReturn)
{
LDataTableReturn = null;
}
else
{
LDataTableReturn = LDatabaseOperationReturn.DataSetReturn.Tables[0];
foreach (DataRow LDataRowSingleRow in LDataTableReturn.Rows)
{
Value01 += DoubleParse(LDataRowSingleRow["Value01"].ToString(), 0);
}
}
}
catch (Exception ex)
{
FileLog.WriteInfo("GetDayRecordStatistics()", "Error :" + ex.Message);
}
return(Value01);
}
//T_46_004
public static void GetAllKPIFormulaColumn(DataBaseConfig ADataBaseConfig, GlobalSetting AGolbalSetting, ref List <KPIFormulaColumn> AListKPIFormulaColumn)
{
AListKPIFormulaColumn.Clear();
DataTable LDataTableReturn = new DataTable();
string LStrDynamicSQL = string.Empty;
string LStrRentToken = string.Empty;
string LStrSingleObject = string.Empty;
try
{
DatabaseOperation01Return LDatabaseOperationReturn = new DatabaseOperation01Return();
DataOperations01 LDataOperations = new DataOperations01();
LStrDynamicSQL = string.Format("SELECT * FROM T_46_004_{0} "
, AGolbalSetting.StrRent);
LDatabaseOperationReturn = LDataOperations.SelectDataByDynamicSQL(ADataBaseConfig.IntDatabaseType, ADataBaseConfig.StrDatabaseProfile, LStrDynamicSQL);
if (!LDatabaseOperationReturn.BoolReturn)
{
LDataTableReturn = null;
}
else
{
LDataTableReturn = LDatabaseOperationReturn.DataSetReturn.Tables[0];
foreach (DataRow dr in LDataTableReturn.Rows)
{
KPIFormulaColumn kpiformulatemp = new KPIFormulaColumn();
kpiformulatemp.FormulaCharID = LongParse(dr["C001"].ToString(), 0);
kpiformulatemp.ColumnName = dr["C002"].ToString();
kpiformulatemp.ColumnSource = IntParse(dr["C004"].ToString(), 0);
kpiformulatemp.ApplayName = dr["C005"].ToString();
kpiformulatemp.DataType = IntParse(dr["C006"].ToString(), 0);
kpiformulatemp.ApplyCycle = dr["C007"].ToString();
kpiformulatemp.SpecialObjectTypeNumber = IntParse(dr["C008"].ToString(), 0);
AListKPIFormulaColumn.Add(kpiformulatemp);
}
}
}
catch (Exception ex)
{
LDataTableReturn = null;
FileLog.WriteInfo("GetAllKPIFormulaColumn()", ex.Message);
}
}
//得到分表信息
public static DataTable ObtainRentExistLogicPartitionTables(DataBaseConfig ADataBaseConfig, string AStrRentToken, string AStrTableName, ref List <string> AListStringTableName)
{
DataTable LDataTableReturn = new DataTable();
string LStrDynamicSQL = string.Empty;
AListStringTableName.Clear();
try
{
DatabaseOperation01Return LDatabaseOperationReturn = new DatabaseOperation01Return();
DataOperations01 LDataOperations = new DataOperations01();
if (ADataBaseConfig.IntDatabaseType == 2)
{
LStrDynamicSQL = "SELECT NAME AS TABLE_NAME FROM SYSOBJECTS WHERE NAME LIKE '" + AStrTableName + "_" + AStrRentToken + "_%' ORDER BY NAME ASC";
}
if (ADataBaseConfig.IntDatabaseType == 3)
{
LStrDynamicSQL = "SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE '" + AStrTableName + "_" + AStrRentToken + "_%' ORDER BY TABLE_NAME ASC";
}
LDatabaseOperationReturn = LDataOperations.SelectDataByDynamicSQL(ADataBaseConfig.IntDatabaseType, ADataBaseConfig.StrDatabaseProfile, LStrDynamicSQL);
if (!LDatabaseOperationReturn.BoolReturn)
{
LDataTableReturn = null;
}
else
{
LDataTableReturn = LDatabaseOperationReturn.DataSetReturn.Tables[0];
string LStringTableName = string.Empty;
foreach (DataRow dr in LDataTableReturn.Rows)
{
LStringTableName = dr["TABLE_NAME"].ToString();
AListStringTableName.Add(LStringTableName);
}
}
}
catch (Exception ex)
{
LDataTableReturn = null;
FileLog.WriteInfo("ObtainRentExistLogicPartitionTables()", "ERROR:" + ex.Message);
}
return(LDataTableReturn);
}
/// <summary>
/// 获取是否表有逻辑分表
/// </summary>
/// <param name="ARent"></param>
/// <returns> 0、运行错误1、有按月分表 2、 无按月分表 </returns>
public static int ObtainRentLogicTable(DataBaseConfig ADataBaseConfig, string ARentToken, string ATableNameField)
{
int Flag = 2;
string LStrDynamicSQL = string.Empty;
DatabaseOperation01Return LDatabaseOperationReturn = new DatabaseOperation01Return();
DataOperations01 LDataOperations = new DataOperations01();
LStrDynamicSQL = "SELECT * FROM T_00_000 WHERE C000 = '" + ARentToken + "' AND C001 = '" + ATableNameField + "' AND C004 = '1'";
LDatabaseOperationReturn = LDataOperations.SelectDataByDynamicSQL(ADataBaseConfig.IntDatabaseType, ADataBaseConfig.StrDatabaseProfile, LStrDynamicSQL);
if (!LDatabaseOperationReturn.BoolReturn)
{
return(Flag = 0);
}
if (LDatabaseOperationReturn.StrReturn == "1")
{
return(Flag = 1);
}
return(Flag);
}
/// <summary>
/// 得到技能组信息
/// </summary>
/// <param name="AListSkillInfo"></param>
/// <param name="AStrRent"></param>
public static void GetSkillInfo(DataBaseConfig ADataBaseConfig, ref List <ObjectInfo> AListSkillInfo, string AStrRent)
{
AListSkillInfo.Clear();
DataTable LDataTableReturn = new DataTable();
string LStrDynamicSQL = string.Empty;
string LStrRentToken = string.Empty;
string LStrSingleObject = string.Empty;
try
{
DatabaseOperation01Return LDatabaseOperationReturn = new DatabaseOperation01Return();
DataOperations01 LDataOperations = new DataOperations01();
LStrDynamicSQL = string.Format("SELECT * FROM T_11_009_{0} WHERE C000 = 2 AND C004 = 1 ORDER BY C002"
, AStrRent);
LDatabaseOperationReturn = LDataOperations.SelectDataByDynamicSQL(ADataBaseConfig.IntDatabaseType, ADataBaseConfig.StrDatabaseProfile, LStrDynamicSQL);
if (!LDatabaseOperationReturn.BoolReturn)
{
LDataTableReturn = null;
}
else
{
LDataTableReturn = LDatabaseOperationReturn.DataSetReturn.Tables[0];
foreach (DataRow LDataRowSingleRow in LDataTableReturn.Rows)
{
ObjectInfo skillInfoTemp = new ObjectInfo();
skillInfoTemp.ObjID = LongParse(LDataRowSingleRow["C001"].ToString(), 0);
skillInfoTemp.ObjName = EncryptionAndDecryption.EncryptDecryptString(LDataRowSingleRow["C008"].ToString(), IStrVerificationCode102, EncryptionAndDecryption.UMPKeyAndIVType.M102);
skillInfoTemp.ObjType = 6;
AListSkillInfo.Add(skillInfoTemp);
}
}
}
catch (Exception ex)
{
LDataTableReturn = null;
FileLog.WriteInfo("GetSkillInfo()", ex.Message);
}
}
/// <summary>
/// 得到所有的租户
/// </summary>
public static void ObtainRentList(DataBaseConfig ADataBaseConfig, ref List <string> AListStrRentExistObjects)
{
DataTable LDataTableReturn = new DataTable();
string LStrDynamicSQL = string.Empty;
string LStrRentToken = string.Empty;
string LStrSingleObject = string.Empty;
try
{
DatabaseOperation01Return LDatabaseOperationReturn = new DatabaseOperation01Return();
DataOperations01 LDataOperations = new DataOperations01();
LStrDynamicSQL = "SELECT * FROM T_00_121 ORDER BY C001 ASC";
LDatabaseOperationReturn = LDataOperations.SelectDataByDynamicSQL(ADataBaseConfig.IntDatabaseType, ADataBaseConfig.StrDatabaseProfile, LStrDynamicSQL);
if (!LDatabaseOperationReturn.BoolReturn)
{
LDataTableReturn = null;
}
else
{
LDataTableReturn = LDatabaseOperationReturn.DataSetReturn.Tables[0];
foreach (DataRow LDataRowSingleRent in LDataTableReturn.Rows)
{
LStrRentToken = EncryptionAndDecryption.EncryptDecryptString(LDataRowSingleRent["C021"].ToString(), IStrVerificationCode102, EncryptionAndDecryption.UMPKeyAndIVType.M102);
while (!Regex.IsMatch(LStrRentToken, @"^\d{5}$"))
{
LStrRentToken = EncryptionAndDecryption.EncryptDecryptString(LDataRowSingleRent["C021"].ToString(), IStrVerificationCode102, EncryptionAndDecryption.UMPKeyAndIVType.M102);
}
LDataRowSingleRent["C021"] = LStrRentToken;
LStrRentToken = LDataRowSingleRent["C021"].ToString();
AListStrRentExistObjects.Add(LStrRentToken);
}
}
}
catch (Exception ex)
{
LDataTableReturn = null;
FileLog.WriteInfo("ObtainRentList()", ex.Message);
}
}
/// <summary>
/// 根据机构或技能组在T_11_201表查询它下面对应的用户和座席和分机
/// </summary>
/// <param name="ADataBaseConfig"></param>
/// <param name="AResourceCodeBegin"></param>
/// <param name="AResourceCodeEnd"></param>
/// <param name="AStrRent"></param>
/// <param name="AListObjMappingID"></param>
public static void GetObjInfoMapping(DataBaseConfig ADataBaseConfig, string AResourceCodeBegin, string AResourceCodeEnd, string AStrRent, ref List <long> AListObjMappingID)
{
AListObjMappingID.Clear();
DataTable LDataTableReturn = new DataTable();
string LStrDynamicSQL = string.Empty;
string LStrRentToken = string.Empty;
string LStrSingleObject = string.Empty;
long LResourceCodeBegin = LongParse(AResourceCodeBegin + "0000000000000001", 0);
long LResourceCodeEnd = LongParse(AResourceCodeEnd + "0000000000000001", 0);
try
{
DatabaseOperation01Return LDatabaseOperationReturn = new DatabaseOperation01Return();
DataOperations01 LDataOperations = new DataOperations01();
LStrDynamicSQL = string.Format("SELECT * FROM T_11_201_{0} WHERE C003 >={1} AND C003<{2} "
, AStrRent, LResourceCodeBegin, LResourceCodeEnd);
LDatabaseOperationReturn = LDataOperations.SelectDataByDynamicSQL(ADataBaseConfig.IntDatabaseType, ADataBaseConfig.StrDatabaseProfile, LStrDynamicSQL);
if (!LDatabaseOperationReturn.BoolReturn)
{
LDataTableReturn = null;
}
else
{
LDataTableReturn = LDatabaseOperationReturn.DataSetReturn.Tables[0];
foreach (DataRow LDataRowSingleRow in LDataTableReturn.Rows)
{
long LLongObjMappingID = 0;
LLongObjMappingID = LongParse(LDataRowSingleRow["C004"].ToString(), 0);
AListObjMappingID.Add(LLongObjMappingID);
}
}
}
catch (Exception ex)
{
LDataTableReturn = null;
FileLog.WriteInfo("GetObjInfoMapping()", ex.Message);
}
}
/// <summary>
/// 得到所有座席信息
/// </summary>
/// <param name="AListAgentInfo"></param>
/// <param name="AStrRent"></param>
public static void GetAllAgentInfo(DataBaseConfig ADataBaseConfig, ref List <ObjectInfo> AListAgentInfo, GlobalSetting AGolbalSetting)
{
AListAgentInfo.Clear();
DataTable LDataTableReturn = new DataTable();
string LStrDynamicSQL = string.Empty;
string LStrRentToken = string.Empty;
string LStrSingleObject = string.Empty;
try
{
DatabaseOperation01Return LDatabaseOperationReturn = new DatabaseOperation01Return();
DataOperations01 LDataOperations = new DataOperations01();
LStrDynamicSQL = string.Format("SELECT * FROM T_11_101_{0} WHERE C001 >= 1030000000000000000 AND C001 < 1040000000000000000 AND C002=1 AND C012='1' "
, AGolbalSetting.StrRent);
LDatabaseOperationReturn = LDataOperations.SelectDataByDynamicSQL(ADataBaseConfig.IntDatabaseType, ADataBaseConfig.StrDatabaseProfile, LStrDynamicSQL);
if (!LDatabaseOperationReturn.BoolReturn)
{
LDataTableReturn = null;
}
else
{
LDataTableReturn = LDatabaseOperationReturn.DataSetReturn.Tables[0];
foreach (DataRow LDataRowSingleRow in LDataTableReturn.Rows)
{
ObjectInfo agentInfoTemp = new ObjectInfo();
agentInfoTemp.ObjID = LongParse(LDataRowSingleRow["C001"].ToString(), 0);
agentInfoTemp.BeyondOrgID = LongParse(LDataRowSingleRow["C011"].ToString(), 0);
agentInfoTemp.ObjName = EncryptionAndDecryption.EncryptDecryptString(LDataRowSingleRow["C017"].ToString(), IStrVerificationCode102, EncryptionAndDecryption.UMPKeyAndIVType.M102);
agentInfoTemp.ObjType = 1;
AListAgentInfo.Add(agentInfoTemp);
}
}
}
catch (Exception ex)
{
LDataTableReturn = null;
FileLog.WriteInfo("GetAllAgentInfo()", ex.Message);
}
}
/// <summary>
/// 得到所有公式对应的字符
/// </summary>
/// <param name="AListUserInfo"></param>
/// <param name="AStrRent"></param>
public static void GetAllKPIFormulaChar(DataBaseConfig ADataBaseConfig, GlobalSetting AGolbalSetting, ref List <KPIFormulaChar> AListKPIFormulaChar)
{
AListKPIFormulaChar.Clear();
DataTable LDataTableReturn = new DataTable();
string LStrDynamicSQL = string.Empty;
string LStrRentToken = string.Empty;
string LStrSingleObject = string.Empty;
try
{
DatabaseOperation01Return LDatabaseOperationReturn = new DatabaseOperation01Return();
DataOperations01 LDataOperations = new DataOperations01();
LStrDynamicSQL = string.Format("SELECT * FROM T_46_005_{0} "
, AGolbalSetting.StrRent);
LDatabaseOperationReturn = LDataOperations.SelectDataByDynamicSQL(ADataBaseConfig.IntDatabaseType, ADataBaseConfig.StrDatabaseProfile, LStrDynamicSQL);
if (!LDatabaseOperationReturn.BoolReturn)
{
LDataTableReturn = null;
}
else
{
LDataTableReturn = LDatabaseOperationReturn.DataSetReturn.Tables[0];
foreach (DataRow dr in LDataTableReturn.Rows)
{
KPIFormulaChar kpiFormulaChar = new KPIFormulaChar();
kpiFormulaChar.KpiID = LongParse(dr["C001"].ToString(), 0);
kpiFormulaChar.FormulaCharID = LongParse(dr["C002"].ToString(), 0);
kpiFormulaChar.MappingChar = dr["C003"].ToString();
AListKPIFormulaChar.Add(kpiFormulaChar);
}
}
}
catch (Exception ex)
{
LDataTableReturn = null;
FileLog.WriteInfo("GetAllKPIDefine()", ex.Message);
}
}
/// <summary>
/// 得到该租户的月和周的设定
/// 12010101每周开始于默认值为0,表示从周日晚上24点开始
/// 0为周日，1星期一，6为星期六
/// 12010102每月开始于默认值为1
/// 1为自然月,2为2号,最大28为28号
/// 12010401 为分机和座席 E为分机 A为座席 E char(27)A为座席+分机 R为真实分机
/// </summary>
public static void GetGlobalSetting(DataBaseConfig ADataBaseConfig, ref string AStrParamValue, string AStrRent, string AStrParamNumber)
{
AStrParamValue = string.Empty;
DataTable LDataTableReturn = new DataTable();
string LStrDynamicSQL = string.Empty;
string LStrRentToken = string.Empty;
string LStrSingleObject = string.Empty;
try
{
DatabaseOperation01Return LDatabaseOperationReturn = new DatabaseOperation01Return();
DataOperations01 LDataOperations = new DataOperations01();
LStrDynamicSQL = string.Format("SELECT * FROM T_11_001_{0} WHERE C003={1}"
, AStrRent
, AStrParamNumber);
LDatabaseOperationReturn = LDataOperations.SelectDataByDynamicSQL(ADataBaseConfig.IntDatabaseType, ADataBaseConfig.StrDatabaseProfile, LStrDynamicSQL);
if (!LDatabaseOperationReturn.BoolReturn)
{
LDataTableReturn = null;
}
else
{
LDataTableReturn = LDatabaseOperationReturn.DataSetReturn.Tables[0];
foreach (DataRow LDataRowSingleRow in LDataTableReturn.Rows)
{
AStrParamValue = EncryptionAndDecryption.EncryptDecryptString(LDataRowSingleRow["C006"].ToString(), IStrVerificationCode102, EncryptionAndDecryption.UMPKeyAndIVType.M102).Trim(' ').Substring(AStrParamNumber.Length);
}
}
}
catch (Exception ex)
{
LDataTableReturn = null;
FileLog.WriteInfo("GetGlobalSetting()", ex.Message);
}
}
/// <summary>
/// 执行SQL语句，返回受影响的行数
/// </summary>
/// <param name="AIntDBType">数据库类型。2：MS SQL；3：Oracle</param>
/// <param name="AStrDBConnectProfile">数据库连接字符串</param>
/// <param name="AStrSQLString">需要执行的SQL语句</param>
/// <returns></returns>
public DatabaseOperation01Return ExecuteDynamicSQL(int AIntDBType, string AStrDBConnectProfile, string AStrSQLString)
{
DatabaseOperation01Return LClassReturn = new DatabaseOperation01Return();
int LIntExecureReturn = 0;
SqlConnection LSqlConnection = null;
SqlCommand LSqlCommand = null;
OracleConnection LOracleConnection = null;
OracleCommand LOracleCommand = null;
try
{
if (AIntDBType == 2)
{
LSqlConnection = new SqlConnection(AStrDBConnectProfile);
LSqlConnection.Open();
LSqlCommand = new SqlCommand(AStrSQLString, LSqlConnection);
LIntExecureReturn = LSqlCommand.ExecuteNonQuery();
}
if (AIntDBType == 3)
{
LOracleConnection = new OracleConnection(AStrDBConnectProfile);
LOracleConnection.Open();
LOracleCommand = new OracleCommand(AStrSQLString, LOracleConnection);
LIntExecureReturn = LOracleCommand.ExecuteNonQuery();
}
LClassReturn.StrReturn = LIntExecureReturn.ToString();
}
catch (Exception ex)
{
LClassReturn.BoolReturn = false;
LClassReturn.StrReturn = "DataOperations01.ExecuteDynamicSQL()\n" + ex.ToString();
}
finally
{
if (LSqlCommand != null)
{
LSqlCommand.Dispose(); LSqlCommand = null;
}
if (LSqlConnection != null)
{
if (LSqlConnection.State == System.Data.ConnectionState.Open)
{
LSqlConnection.Close();
}
LSqlConnection.Dispose();
}
if (LOracleCommand != null)
{
LOracleCommand.Dispose(); LOracleCommand = null;
}
if (LOracleConnection != null)
{
if (LOracleConnection.State == ConnectionState.Open)
{
LOracleConnection.Close();
}
LOracleConnection.Dispose(); LOracleConnection = null;
}
}
return(LClassReturn);
}
/// <summary>
/// 获取流水号
/// </summary>
/// <param name="AIntDBType">数据库类型。2：MS SQL；3：Oracle</param>
/// <param name="AStrDBConnectProfile">数据库连接字符串</param>
/// <param name="AIntModuleID">模块编码 11 - 99</param>
/// <param name="AIntSerialType">流水号类型编码 100 ～ 920</param>
/// <param name="AStrRent5">租户编码（表5位）</param>
/// <param name="AStrTime">流水号时间（yyyyMMddHHmmss）</param>
/// <returns>19位长度的字符串，DatabaseOperation01Return.StrReturn</returns>
public DatabaseOperation01Return GetSerialNumberByProcedure(int AIntDBType, string AStrDBConnectProfile, int AIntModuleID, int AIntSerialType, string AStrRentID5, string AStrTime)
{
DatabaseOperation01Return LClassReturn = new DatabaseOperation01Return();
SqlConnection LSqlConnection = null;
SqlCommand LSqlCommand = null;
OracleConnection LOracleConnection = null;
OracleCommand LOracleCommand = null;
try
{
if (AIntDBType == 2)
{
#region MS SQL 数据库
LSqlConnection = new SqlConnection(AStrDBConnectProfile);
LSqlConnection.Open();
LSqlCommand = new SqlCommand();
LSqlCommand.Connection = LSqlConnection;
LSqlCommand.CommandType = CommandType.StoredProcedure;
LSqlCommand.CommandText = "P_00_001";
SqlParameter[] LSqlParameter =
{
new SqlParameter("@AInParam01", SqlDbType.VarChar, 2), //0
new SqlParameter("@AInParam02", SqlDbType.VarChar, 3), //1
new SqlParameter("@AInParam03", SqlDbType.VarChar, 5), //2
new SqlParameter("@AInParam04", SqlDbType.VarChar, 20), //3
new SqlParameter("@AOutParam01", SqlDbType.VarChar, 20), //4
new SqlParameter("@AOutErrorNumber", SqlDbType.Int), //5
new SqlParameter("@AOutErrorString", SqlDbType.NVarChar, 2000) //6
};
LSqlParameter[0].Value = AIntModuleID.ToString();
LSqlParameter[1].Value = AIntSerialType.ToString();
LSqlParameter[2].Value = AStrRentID5;
LSqlParameter[3].Value = AStrTime;
LSqlParameter[4].Direction = ParameterDirection.Output;
LSqlParameter[5].Direction = ParameterDirection.Output;
LSqlParameter[6].Direction = ParameterDirection.Output;
foreach (SqlParameter LSqlParameterSingle in LSqlParameter)
{
LSqlCommand.Parameters.Add(LSqlParameterSingle);
}
LSqlCommand.ExecuteNonQuery();
if (LSqlParameter[5].Value.ToString() != "0")
{
LClassReturn.BoolReturn = false;
LClassReturn.StrReturn = LSqlParameter[6].Value.ToString();
}
else
{
LClassReturn.StrReturn = LSqlParameter[4].Value.ToString();
}
#endregion
}
if (AIntDBType == 3)
{
#region Oracle 数据库
LOracleConnection = new OracleConnection(AStrDBConnectProfile);
LOracleConnection.Open();
LOracleCommand = new OracleCommand();
LOracleCommand.Connection = LOracleConnection;
LOracleCommand.CommandType = CommandType.StoredProcedure;
LOracleCommand.CommandText = "P_00_001";
OracleParameter[] LOracleParameter =
{
new OracleParameter("AInParam01", OracleDbType.Varchar2, 2), //0
new OracleParameter("AInParam02", OracleDbType.Varchar2, 3), //1
new OracleParameter("AInParam03", OracleDbType.Varchar2, 5), //2
new OracleParameter("AInParam04", OracleDbType.Varchar2, 20), //3
new OracleParameter("AOutParam01", OracleDbType.Varchar2, 20), //4
new OracleParameter("ErrorNumber", OracleDbType.Int32), //5
new OracleParameter("ErrorString", OracleDbType.NVarchar2, 2000) //6
};
LOracleParameter[0].Value = AIntModuleID.ToString();
LOracleParameter[1].Value = AIntSerialType.ToString();
LOracleParameter[2].Value = AStrRentID5;
LOracleParameter[3].Value = AStrTime;
LOracleParameter[4].Direction = ParameterDirection.Output;
LOracleParameter[5].Direction = ParameterDirection.Output;
LOracleParameter[6].Direction = ParameterDirection.Output;
foreach (OracleParameter LOracleParameterSingle in LOracleParameter)
{
LOracleCommand.Parameters.Add(LOracleParameterSingle);
}
LOracleCommand.ExecuteNonQuery();
if (LOracleParameter[5].Value.ToString() != "0")
{
LClassReturn.BoolReturn = false;
LClassReturn.StrReturn = LOracleParameter[6].Value.ToString();
}
else
{
LClassReturn.StrReturn = LOracleParameter[4].Value.ToString();
}
#endregion
}
}
catch (Exception ex)
{
LClassReturn.BoolReturn = false;
LClassReturn.StrReturn = "DataOperations01.GetSerialNumberByProcedure()\n" + ex.ToString();
}
finally
{
if (LSqlCommand != null)
{
LSqlCommand.Dispose(); LSqlCommand = null;
}
if (LSqlConnection != null)
{
if (LSqlConnection.State == System.Data.ConnectionState.Open)
{
LSqlConnection.Close();
}
LSqlConnection.Dispose();
}
if (LOracleCommand != null)
{
LOracleCommand.Dispose(); LOracleCommand = null;
}
if (LOracleConnection != null)
{
if (LOracleConnection.State == ConnectionState.Open)
{
LOracleConnection.Close();
}
LOracleConnection.Dispose(); LOracleConnection = null;
}
}
return(LClassReturn);
}
//查询数据
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);
}
}
//将数据插入录音的统计表
/// <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);
}
}
/// <summary>
/// 以本地时间查询录音表的数据
/// </summary>
/// <param name="ADataBaseConfig"></param>
/// <param name="AObjectInfo"></param>
/// <param name="AGlobalSetting"></param>
/// <param name="ADateTimeStart"></param>
/// <param name="ADateTimeStop"></param>
/// <param name="AFuncType"></param>
/// <param name="AExtensionAgentType"></param>
/// <returns></returns>
public static long GetAllRecordStatisticsInfo(DataBaseConfig ADataBaseConfig, ObjectInfo AObjectInfo, GlobalSetting AGlobalSetting, DateTime ADateTimeStart, DateTime ADateTimeStop, int AFuncType)
{
long Value01 = 0;
DataTable LDataTableReturn = new DataTable();
string LStrDynamicSQL = string.Empty;
string LStrRentToken = string.Empty;
string LStrSingleObject = string.Empty;
//根据座席和分机和真实分机查询相应 功能的值
string TableName = string.Empty;
DateTime utcTime = ADateTimeStart.ToUniversalTime();
if (AGlobalSetting.IlogicPartMark == 1)//为1按月分表
{
TableName = string.Format("T_21_001_{0}_{1}{2}", AGlobalSetting.StrRent, utcTime.ToString("yy"), utcTime.ToString("MM"));
if (!AGlobalSetting.LStrRecordName.Contains(TableName))
{
return(0);
}
}
else
{
TableName = string.Format("T_21_001_{0}", AGlobalSetting.StrRent);
}
try
{
DatabaseOperation01Return LDatabaseOperationReturn = new DatabaseOperation01Return();
DataOperations01 LDataOperations = new DataOperations01();
switch (AFuncType)
{
case 1: //RecordLength
{
if (ADataBaseConfig.IntDatabaseType == 2)
{
if (AObjectInfo.ObjType == 2) //分机
{
LStrDynamicSQL = string.Format("SELECT SUM(C012) AS VALUE01 FROM {0} WHERE C004>='{2}' AND C004<'{3}' AND C042='{1}' AND C020='{4}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop, AObjectInfo.ExtensionIP);
}
else if (AObjectInfo.ObjType == 1) //座席
{
LStrDynamicSQL = string.Format("SELECT SUM(C012) AS VALUE01 FROM {0} WHERE C004>='{2}' AND C004<'{3}' AND C039='{1}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop);
}
else if (AObjectInfo.ObjType == 4) //真实分机
{
LStrDynamicSQL = string.Format("SELECT SUM(C012) AS VALUE01 FROM {0} WHERE C004>='{2}' AND C004<'{3}' AND C058='{1}' AND C020='{4}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop, AObjectInfo.ExtensionIP);
}
}
else if (ADataBaseConfig.IntDatabaseType == 3)
{
if (AObjectInfo.ObjType == 2) //分机
{
LStrDynamicSQL = string.Format("SELECT SUM(C012) AS VALUE01 FROM {0} WHERE C004>=TO_DATE ('{2}','YYYY-MM-DD HH24:MI:SS') AND C004<TO_DATE ('{3}','YYYY-MM-DD HH24:MI:SS') AND C042='{1}' AND C020='{4}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop, AObjectInfo.ExtensionIP);
}
else if (AObjectInfo.ObjType == 1) //座席
{
LStrDynamicSQL = string.Format("SELECT SUM(C012) AS VALUE01 FROM {0} WHERE C004>=TO_DATE ('{2}','YYYY-MM-DD HH24:MI:SS') AND C004<TO_DATE ('{3}','YYYY-MM-DD HH24:MI:SS') AND C039='{1}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop);
}
else if (AObjectInfo.ObjType == 4) //真实分机
{
LStrDynamicSQL = string.Format("SELECT SUM(C012) AS VALUE01 FROM {0} WHERE C004>=TO_DATE ('{2}','YYYY-MM-DD HH24:MI:SS') AND C004<TO_DATE ('{3}','YYYY-MM-DD HH24:MI:SS') AND C058='{1}' AND C020='{4}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop, AObjectInfo.ExtensionIP);
}
}
}
break;
case 2: //RecordNumber
{
if (ADataBaseConfig.IntDatabaseType == 2)
{
if (AObjectInfo.ObjType == 2) //分机
{
LStrDynamicSQL = string.Format("SELECT COUNT(C001) AS VALUE01 FROM {0} WHERE C004>='{2}' AND C004<'{3}' AND C042='{1}' AND C020='{4}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop, AObjectInfo.ExtensionIP);
}
else if (AObjectInfo.ObjType == 1) //座席
{
LStrDynamicSQL = string.Format("SELECT COUNT(C001) AS VALUE01 FROM {0} WHERE C004>='{2}' AND C004<'{3}' AND C039='{1}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop);
}
else if (AObjectInfo.ObjType == 4) //真实分机
{
LStrDynamicSQL = string.Format("SELECT COUNT(C001) AS VALUE01 FROM {0} WHERE C004>='{2}' AND C004<'{3}' AND C059='{1}' AND C020='{4}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop, AObjectInfo.ExtensionIP);
}
}
else if (ADataBaseConfig.IntDatabaseType == 3)
{
if (AObjectInfo.ObjType == 2) //分机
{
LStrDynamicSQL = string.Format("SELECT COUNT(C001) AS VALUE01 FROM {0} WHERE C004>=TO_DATE ('{2}','YYYY-MM-DD HH24:MI:SS') AND C004<TO_DATE ('{3}','YYYY-MM-DD HH24:MI:SS') AND C042='{1}' AND C020='{4}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop, AObjectInfo.ExtensionIP);
}
else if (AObjectInfo.ObjType == 1) //座席
{
LStrDynamicSQL = string.Format("SELECT COUNT(C001) AS VALUE01 FROM {0} WHERE C004>=TO_DATE ('{2}','YYYY-MM-DD HH24:MI:SS') AND C004<TO_DATE ('{3}','YYYY-MM-DD HH24:MI:SS') AND C039='{1}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop);
}
else if (AObjectInfo.ObjType == 4) //真实分机
{
LStrDynamicSQL = string.Format("SELECT COUNT(C001) AS VALUE01 FROM {0} WHERE C004>=TO_DATE ('{2}','YYYY-MM-DD HH24:MI:SS') AND C004<TO_DATE ('{3}','YYYY-MM-DD HH24:MI:SS') AND C058='{1}' AND C020='{4}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop, AObjectInfo.ExtensionIP);
}
}
}
break;
case 3: //RingTime
{
if (ADataBaseConfig.IntDatabaseType == 2)
{
if (AObjectInfo.ObjType == 2) //分机
{
LStrDynamicSQL = string.Format("SELECT SUM(C061) AS VALUE01 FROM {0} WHERE C004>='{2}' AND C004<'{3}' AND C042='{1}' AND C020='{4}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop, AObjectInfo.ExtensionIP);
}
else if (AObjectInfo.ObjType == 1) //座席
{
LStrDynamicSQL = string.Format("SELECT SUM(C061) AS VALUE01 FROM {0} WHERE C004>='{2}' AND C004<'{3}' AND C039='{1}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop);
}
else if (AObjectInfo.ObjType == 4) //真实分机
{
LStrDynamicSQL = string.Format("SELECT SUM(C061) AS VALUE01 FROM {0} WHERE C004>='{2}' AND C004<'{3}' AND C058='{1}' AND C020='{4}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop, AObjectInfo.ExtensionIP);
}
}
else if (ADataBaseConfig.IntDatabaseType == 3)
{
if (AObjectInfo.ObjType == 2) //分机
{
LStrDynamicSQL = string.Format("SELECT SUM(C061) AS VALUE01 FROM {0} WHERE C004>=TO_DATE ('{2}','YYYY-MM-DD HH24:MI:SS') AND C004<TO_DATE ('{3}','YYYY-MM-DD HH24:MI:SS') AND C042='{1}' AND C020='{4}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop, AObjectInfo.ExtensionIP);
}
else if (AObjectInfo.ObjType == 1) //座席
{
LStrDynamicSQL = string.Format("SELECT SUM(C061) AS VALUE01 FROM {0} WHERE C004>=TO_DATE ('{2}','YYYY-MM-DD HH24:MI:SS') AND C004<TO_DATE ('{3}','YYYY-MM-DD HH24:MI:SS') AND C039='{1}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop);
}
else if (AObjectInfo.ObjType == 4) //真实分机
{
LStrDynamicSQL = string.Format("SELECT SUM(C061) AS VALUE01 FROM {0} WHERE C004>=TO_DATE ('{2}','YYYY-MM-DD HH24:MI:SS') AND C004<TO_DATE ('{3}','YYYY-MM-DD HH24:MI:SS') AND C058='{1}' AND C020='{4}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop, AObjectInfo.ExtensionIP);
}
}
}
break;
case 4: //holdTime
{
if (ADataBaseConfig.IntDatabaseType == 2)
{
if (AObjectInfo.ObjType == 2) //分机
{
LStrDynamicSQL = string.Format("SELECT SUM(C060) AS VALUE01 FROM {0} WHERE C004>='{2}' AND C004<'{3}' AND C042='{1}' AND C020='{4}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop, AObjectInfo.ExtensionIP);
}
else if (AObjectInfo.ObjType == 1) //座席
{
LStrDynamicSQL = string.Format("SELECT SUM(C060) AS VALUE01 FROM {0} WHERE C004>='{2}' AND C004<'{3}' AND C039='{1}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop);
}
else if (AObjectInfo.ObjType == 4) //真实分机
{
LStrDynamicSQL = string.Format("SELECT SUM(C060) AS VALUE01 FROM {0} WHERE C004>='{2}' AND C004<'{3}' AND C058='{1}' AND C020='{4}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop, AObjectInfo.ExtensionIP);
}
}
else if (ADataBaseConfig.IntDatabaseType == 3)
{
if (AObjectInfo.ObjType == 2) //分机
{
LStrDynamicSQL = string.Format("SELECT SUM(C060) AS VALUE01 FROM {0} WHERE C004>=TO_DATE ('{2}','YYYY-MM-DD HH24:MI:SS') AND C004<TO_DATE ('{3}','YYYY-MM-DD HH24:MI:SS') AND C042='{1}' AND C020='{4}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop, AObjectInfo.ExtensionIP);
}
else if (AObjectInfo.ObjType == 1) //座席
{
LStrDynamicSQL = string.Format("SELECT SUM(C060) AS VALUE01 FROM {0} WHERE C004>=TO_DATE ('{2}','YYYY-MM-DD HH24:MI:SS') AND C004<TO_DATE ('{3}','YYYY-MM-DD HH24:MI:SS') AND C039='{1}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop);
}
else if (AObjectInfo.ObjType == 4) //真实分机
{
LStrDynamicSQL = string.Format("SELECT SUM(C060) AS VALUE01 FROM {0} WHERE C004>=TO_DATE ('{2}','YYYY-MM-DD HH24:MI:SS') AND C004<TO_DATE ('{3}','YYYY-MM-DD HH24:MI:SS') AND C058='{1}' AND C020='{4}' ", TableName, AObjectInfo.ObjName, ADateTimeStart, ADateTimeStop, AObjectInfo.ExtensionIP);
}
}
}
break;
case 5:
{
if (ADataBaseConfig.IntDatabaseType == 2)
{
if (AObjectInfo.ObjType == 2) //分机
{
}
else if (AObjectInfo.ObjType == 1) //座席
{
}
else if (AObjectInfo.ObjType == 4) //真实分机
{
}
}
else if (ADataBaseConfig.IntDatabaseType == 3)
{
if (AObjectInfo.ObjType == 2) //分机
{
}
else if (AObjectInfo.ObjType == 1) //座席
{
}
else if (AObjectInfo.ObjType == 4) //真实分机
{
}
}
}
break;
default:
break;
}
LDatabaseOperationReturn = LDataOperations.SelectDataByDynamicSQL(ADataBaseConfig.IntDatabaseType, ADataBaseConfig.StrDatabaseProfile, LStrDynamicSQL);
if (!LDatabaseOperationReturn.BoolReturn)
{
LDataTableReturn = null;
}
else
{
LDataTableReturn = LDatabaseOperationReturn.DataSetReturn.Tables[0];
foreach (DataRow LDataRowSingleRow in LDataTableReturn.Rows)
{
//Value01 += LongParse(LDataRowSingleRow["VALUE01"].ToString(), 0);
//扩大1000倍
Value01 += Convert.ToInt64(DoubleParse(LDataRowSingleRow["VALUE01"].ToString(), 0) * 1000);
}
}
}
catch (Exception ex)
{
LDataTableReturn = null;
FileLog.WriteInfo("GetAllRecordStatisticsInfo()", ex.Message);
}
return(Value01);
}
public static long GetQMDayStatisticsInfo(DataBaseConfig ADataBaseConfig, ObjectInfo AObjectInfo, GlobalSetting AGlobalSetting,
DateTime ADateTimeStart, DateTime ADateTimeStop, int AFuncType, int AExtensionAgentType)
{
long Value01 = 0;
DataTable LDataTableReturn = new DataTable();
string LStrDynamicSQL = string.Empty;
string LStrRentToken = string.Empty;
string LStrSingleObject = string.Empty;
//根据座席和分机和真实分机查询相应 功能的值
string TableName21 = string.Empty;
string TableName38 = string.Format("T_31_008_{0}", AGlobalSetting.StrRent);
string TableName319 = string.Format("T_31_019_{0}", AGlobalSetting.StrRent);
string TableName320 = string.Format("T_31_020_{0}", AGlobalSetting.StrRent);
string TableName321 = string.Format("T_31_021_{0}", AGlobalSetting.StrRent);
DateTime utcTimeStart = ADateTimeStart.ToUniversalTime();
DateTime utcTimeEnd = ADateTimeStop.ToUniversalTime();
if (AGlobalSetting.IlogicPartMark == 1)//为1按月分表
{
TableName21 = string.Format("T_21_001_{0}_{1}{2}", AGlobalSetting.StrRent, utcTimeStart.ToString("yy"), utcTimeStart.ToString("MM"));
if (!AGlobalSetting.LStrRecordName.Contains(TableName21))
{
return(0);
}
}
else
{
TableName21 = string.Format("T_21_001_{0}", AGlobalSetting.StrRent);
}
try {
DatabaseOperation01Return LDatabaseOperationReturn = new DatabaseOperation01Return();
DataOperations01 LDataOperations = new DataOperations01();
switch (AFuncType)
{
case 1: //ScoreNumber 获取评分数量
{
if (ADataBaseConfig.IntDatabaseType == 2)
{
LStrDynamicSQL = string.Format("SELECT COUNT(1) AS VALUE01 FROM {0} T38,{1} T21 WHERE T38.C002=T21.C002 AND T21.C004>='{2}' AND T21.C004<'{3}' AND T38.C013={4}", TableName38, TableName21, ADateTimeStart, ADateTimeStop, AObjectInfo.ObjID);
}
else if (ADataBaseConfig.IntDatabaseType == 3)
{
LStrDynamicSQL = string.Format("SELECT COUNT(1) AS VALUE01 FROM {0} T38,{1} T21 WHERE T38.C002=T21.C002 AND T21.C004>=TO_DATE ('{2}','YYYY-MM-DD HH24:MI:SS') AND T21.C004<TO_DATE ('{3}','YYYY-MM-DD HH24:MI:SS') AND T38.C013={4}", TableName38, TableName21, ADateTimeStart, ADateTimeStop, AObjectInfo.ObjID);
}
}
break;
case 2: //坐席\分机申述数量
{
if (ADataBaseConfig.IntDatabaseType == 2)
{
LStrDynamicSQL = string.Format("SELECT COUNT(1) AS VALUE01 FROM {0} WHERE C012>='{1}' AND C012<'{2}' AND C004={3} AND C008=1", TableName319, utcTimeStart, utcTimeEnd, AObjectInfo.ObjID);
}
else if (ADataBaseConfig.IntDatabaseType == 3)
{
LStrDynamicSQL = string.Format("SELECT COUNT(1) AS VALUE01 FROM {0} WHERE C012>=TO_DATE ('{1}','YYYY-MM-DD HH24:MI:SS') AND C012<TO_DATE ('{2}','YYYY-MM-DD HH24:MI:SS') AND C004={3} AND C008=1", TableName319, utcTimeStart, utcTimeEnd, AObjectInfo.ObjID);
}
}
break;
case 3: //坐席\分机重新打分的数量（申诉成功并重新打分）
{
if (ADataBaseConfig.IntDatabaseType == 2)
{
LStrDynamicSQL = string.Format("SELECT COUNT(1) AS VALUE01 FROM {0} WHERE C012>='{1}' AND C012<'{2}' AND C004={3} AND C008=6", TableName319, utcTimeStart, utcTimeEnd, AObjectInfo.ObjID);
}
else if (ADataBaseConfig.IntDatabaseType == 3)
{
LStrDynamicSQL = string.Format("SELECT COUNT(1) AS VALUE01 FROM {0} WHERE C012>=TO_DATE ('{1}','YYYY-MM-DD HH24:MI:SS') AND C012<TO_DATE ('{2}','YYYY-MM-DD HH24:MI:SS') AND C004={3} AND C008=6", TableName319, utcTimeStart, utcTimeEnd, AObjectInfo.ObjID);
}
}
break;
case 4: //坐席\分机坐席被质检的总分数
{
if (ADataBaseConfig.IntDatabaseType == 2)
{
LStrDynamicSQL = string.Format("SELECT SUM(T38.C004) AS VALUE01 FROM {0} T38,{1} T21 WHERE T38.C002=T21.C002 AND T21.C004>='{2}' AND T21.C004<'{3}' AND T38.C013={4}", TableName38, TableName21, ADateTimeStart, ADateTimeStop, AObjectInfo.ObjID);
}
else if (ADataBaseConfig.IntDatabaseType == 3)
{
LStrDynamicSQL = string.Format("SELECT SUM(T38.C004) AS VALUE01 FROM {0} T38,{1} T21 WHERE T38.C002=T21.C002 AND T21.C004>=TO_DATE ('{2}','YYYY-MM-DD HH24:MI:SS') AND T21.C004<TO_DATE ('{3}','YYYY-MM-DD HH24:MI:SS') AND T38.C013={4}", TableName38, TableName21, ADateTimeStart, ADateTimeStop, AObjectInfo.ObjID);
}
}
break;
case 5: //质检员被申述的数量
{
if (ADataBaseConfig.IntDatabaseType == 2)
{
LStrDynamicSQL = string.Format("SELECT COUNT(1) AS VALUE01 FROM {0} T319,{1} T308 WHERE T319.C002 = T308.C001 AND T319.C012>='{2}' AND T319.C012<'{3}' AND T319.C0008=1 AND T308.C005={4}", TableName319, TableName38, utcTimeStart, utcTimeEnd, AObjectInfo.ObjID);
}
else if (ADataBaseConfig.IntDatabaseType == 3)
{
LStrDynamicSQL = string.Format("SELECT COUNT(1) AS VALUE01 FROM {0} T319,{1} T308 WHERE T319.C002 = T308.C001 AND T319.C012>=TO_DATE ('{2}','YYYY-MM-DD HH24:MI:SS') AND T319.C012<TO_DATE ('{3}','YYYY-MM-DD HH24:MI:SS') AND T319.C0008=1 AND T308.C005={4}", TableName319, TableName38, utcTimeStart, utcTimeEnd, AObjectInfo.ObjID);
}
}
break;
case 6: //质检员质检的数量
{
if (ADataBaseConfig.IntDatabaseType == 2)
{
LStrDynamicSQL = string.Format("SELECT COUNT(1) AS VALUE01 FROM {0} WHERE C006>='{1}' AND C006<'{2}' AND C005={3}", TableName38, utcTimeStart, utcTimeEnd, AObjectInfo.ObjID);
}
else if (ADataBaseConfig.IntDatabaseType == 3)
{
LStrDynamicSQL = string.Format("SELECT COUNT(1) AS VALUE01 FROM {0} WHERE C006>=TO_DATE ('{1}','YYYY-MM-DD HH24:MI:SS') AND C006<TO_DATE ('{2}','YYYY-MM-DD HH24:MI:SS') AND C005={3}", TableName38, utcTimeStart, utcTimeEnd, AObjectInfo.ObjID);
}
}
break;
case 7: //质检员完成的任务数量
{
if (ADataBaseConfig.IntDatabaseType == 2)
{
LStrDynamicSQL = string.Format("SELECT COUNT(1) AS VALUE01 FROM {0} T20,{1} T21 WHERE T20.C001=T21.C001 AND T20.C018>='{2}' AND T20.C018<'{3}' AND T21.C002={4} AND T20.C017='Y'", TableName320, TableName321, utcTimeStart, utcTimeEnd, AObjectInfo.ObjID);
}
else if (ADataBaseConfig.IntDatabaseType == 3)
{
LStrDynamicSQL = string.Format("SELECT COUNT(1) AS VALUE01 FROM {0} T20,{1} T21 WHERE T20.C001=T21.C001 AND T20.C018>=TO_DATE ('{2}','YYYY-MM-DD HH24:MI:SS') AND T20.C018<TO_DATE ('{3}','YYYY-MM-DD HH24:MI:SS') AND T21.C002={4} T20.C017='Y'", TableName320, TableName321, utcTimeStart, utcTimeEnd, AObjectInfo.ObjID);
}
}
break;
case 8: //质检员接到的任务数量
{
if (ADataBaseConfig.IntDatabaseType == 2)
{
LStrDynamicSQL = string.Format("SELECT COUNT(1) AS VALUE01 FROM {0} T20,{1} T21 WHERE T20.C001=T21.C001 AND T20.C006>='{2}' AND T20.C006<'{3}' AND T21.C002={4}", TableName320, TableName321, utcTimeStart, utcTimeEnd, AObjectInfo.ObjID);
}
else if (ADataBaseConfig.IntDatabaseType == 3)
{
LStrDynamicSQL = string.Format("SELECT COUNT(1) AS VALUE01 FROM {0} T20,{1} T21 WHERE T20.C001=T21.C001 AND T20.C006>=TO_DATE ('{2}','YYYY-MM-DD HH24:MI:SS') AND T20.C006<TO_DATE ('{3}','YYYY-MM-DD HH24:MI:SS') AND T21.C002={4}", TableName320, TableName321, utcTimeStart, utcTimeEnd, AObjectInfo.ObjID);
}
}
break;
default:
break;
}
LDatabaseOperationReturn = LDataOperations.SelectDataByDynamicSQL(ADataBaseConfig.IntDatabaseType, ADataBaseConfig.StrDatabaseProfile, LStrDynamicSQL);
if (!LDatabaseOperationReturn.BoolReturn)
{
LDataTableReturn = null;
}
else
{
LDataTableReturn = LDatabaseOperationReturn.DataSetReturn.Tables[0];
foreach (DataRow LDataRowSingleRow in LDataTableReturn.Rows)
{
Value01 += Convert.ToInt64(DoubleParse(LDataRowSingleRow["VALUE01"].ToString(), 0) * 1000);
}
}
}
catch (Exception ex)
{
LDataTableReturn = null;
FileLog.WriteInfo("GetQMDayStatisticsInfo()", ex.Message);
}
return(Value01);
}
/// <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="AListAgentInfo"></param>
/// <param name="AStrRent"></param>
public static void GetAllExtensionInfo(DataBaseConfig ADataBaseConfig, ref List <ObjectInfo> AListExtensionInfo, GlobalSetting AGlobalSetting)
{
AListExtensionInfo.Clear();
DataTable LDataTableReturn = new DataTable();
string LStrDynamicSQL = string.Empty;
string LStrRentToken = string.Empty;
string LStrSingleObject = string.Empty;
try
{
DatabaseOperation01Return LDatabaseOperationReturn = new DatabaseOperation01Return();
DataOperations01 LDataOperations = new DataOperations01();
if (AGlobalSetting.StrConfigAER.Equals("R"))//真实分机
{
LStrDynamicSQL = string.Format("SELECT * FROM T_11_101_{0} WHERE C001 >= 1050000000000000000 AND C001 < 1060000000000000000 AND C002=1 AND C012='1' "
, AGlobalSetting.StrRent);
}
else
{
LStrDynamicSQL = string.Format("SELECT * FROM T_11_101_{0} WHERE C001 >= 1040000000000000000 AND C001 < 1050000000000000000 AND C002=1 AND C012='1' "
, AGlobalSetting.StrRent);
}
LDatabaseOperationReturn = LDataOperations.SelectDataByDynamicSQL(ADataBaseConfig.IntDatabaseType, ADataBaseConfig.StrDatabaseProfile, LStrDynamicSQL);
if (!LDatabaseOperationReturn.BoolReturn)
{
LDataTableReturn = null;
}
else
{
LDataTableReturn = LDatabaseOperationReturn.DataSetReturn.Tables[0];
foreach (DataRow LDataRowSingleRow in LDataTableReturn.Rows)
{
ObjectInfo agentInfoTemp = new ObjectInfo();
agentInfoTemp.ObjID = LongParse(LDataRowSingleRow["C001"].ToString(), 0);
agentInfoTemp.BeyondOrgID = LongParse(LDataRowSingleRow["C011"].ToString(), 0);
if (agentInfoTemp.ObjID >= CombinSourceCode(StatisticsConstDefine.Const_Source_Extension_Begin) && agentInfoTemp.ObjID < CombinSourceCode(StatisticsConstDefine.Const_Source_TrueExtension_Begin))
{
agentInfoTemp.ObjType = 2;
}
else if (agentInfoTemp.ObjID >= CombinSourceCode(StatisticsConstDefine.Const_Source_TrueExtension_Begin) && agentInfoTemp.ObjID < CombinSourceCode(StatisticsConstDefine.Const_Source_Role_Begin))
{
agentInfoTemp.ObjType = 4;
}
string ExtAndIP = string.Empty;
ExtAndIP = EncryptionAndDecryption.EncryptDecryptString(LDataRowSingleRow["C017"].ToString(), IStrVerificationCode102, EncryptionAndDecryption.UMPKeyAndIVType.M102);
if (ExtAndIP != null && ExtAndIP.Length > 0)
{
string[] Values = ExtAndIP.Split(IStrSpliterChar.ToArray());
if (Values.Length > 1)
{
agentInfoTemp.ObjName = Values[0].ToString();
agentInfoTemp.ExtensionIP = Values[1].ToString();
}
}
AListExtensionInfo.Add(agentInfoTemp);
}
}
}
catch (Exception ex)
{
LDataTableReturn = null;
FileLog.WriteInfo("GetAllExtensionInfo()", ex.Message);
}
}