/// <summary>
/// 组成sql语句
/// </summary>
/// /// lstParam[0] : 开始时间 （数字型）
/// lstParam[1] : 结束时间 （数字型）
/// lstParam[2] : 统计分机号还是坐席
/// lstParam[3] : 临时表中的ID号
/// <returns></returns>
public static OperationReturn CreateSql(SessionInfo session, List <string> lstParam)
{
OperationReturn optReturn = new OperationReturn();
string strSql = string.Empty;
try
{
long lstartTime = long.Parse(lstParam[0]);
long lEndTime = long.Parse(lstParam[1]);
string rentToken = session.RentInfo.Token;
string strMode = lstParam[2];
string strWhere = string.Empty;
if (lstParam.Count >= 3)
{
if (strMode == "A")
{
strWhere = string.Format(" and C039 in (select C012 from T_00_901 where C001 = {0})", lstParam[3]);
}
else if (strMode == "E")
{
strWhere = string.Format(" and C042 in (select C012 from T_00_901 where C001 = {0})", lstParam[3]);
}
}
//是否分表
var tableInfo =
session.ListPartitionTables.FirstOrDefault(
t => t.TableName == ConstValue.TABLE_NAME_RECORD && t.PartType == TablePartType.DatetimeRange);
if (tableInfo == null)
{
//没有分表 直接从T_21_001_00000中查询
strSql = string.Format("select C002,C004,C005,C006,C012,C068 from t_21_001_{0} where C006 >= {1} and C006 <= {2}", rentToken, lstartTime, lEndTime);
strSql += strWhere;
}
else
{
// 算出表名
string strStartTableName = string.Format("T_21_001_{0}_{1}", rentToken, lstartTime.ToString().Substring(2, 4));
string strEndTableName = string.Format("T_21_001_{0}_{1}", rentToken, lEndTime.ToString().Substring(2, 4));
//如果没有跨月
if (strStartTableName == strEndTableName)
{
optReturn = CommonFuns.CheckTableExists(session, strStartTableName);
//如果表不存在 或者检查失败
if (!optReturn.Result)
{
optReturn.Code = (int)S6106WcfErrorCode.NoData;
return(optReturn);
}
strSql = string.Format("select C002,C004,C005,C006,C012,C068 from {0} where C006 >= {1} and C006 <= {2} ", strStartTableName, lstartTime, lEndTime);
strSql += strWhere;
}
else
{
DateTime dtStart = CommonFunctions.StringToDateTime(lstartTime.ToString());
DateTime dtEnd = CommonFunctions.StringToDateTime(lEndTime.ToString());
List <string> lstTables = CommonFunctions.GetTablesByTime(ConstValue.TABLE_NAME_RECORD, dtStart, dtEnd, rentToken);
for (int i = 0; i < lstTables.Count; i++)
{
optReturn = CommonFuns.CheckTableExists(session, lstTables[i]);
if (optReturn.Result)
{
if (!string.IsNullOrEmpty(strSql))
{
strSql += " union ";
}
strSql += string.Format("select C002,C004,C005,C006,C012,C068 from {0} where C006 <= {1} ", lstTables[i], lEndTime);
strSql += strWhere;
}
}
}
}
if (string.IsNullOrEmpty(strSql))
{
optReturn.Result = false;
optReturn.Code = (int)S6106WcfErrorCode.NoData;
optReturn.Message = "sql is null, all table does not exist";
return(optReturn);
}
optReturn.Result = true;
optReturn.Code = Defines.RET_SUCCESS;
optReturn.Data = strSql;
optReturn.StringValue = strSql;
optReturn.Message = strSql;
}
catch (Exception ex)
{
optReturn.Result = false;
optReturn.Code = Defines.RET_FAIL;
optReturn.Message = ex.Message;
optReturn.StringValue = strSql;
return(optReturn);
}
return(optReturn);
}
/// <summary>
/// 获得mssql时的sql语句
/// </summary>
/// <param name="session"></param>
/// <param name="lstParam"></param>
/// lstParam[0] : 开始时间 （数字型）
/// lstParam[1] : 结束时间 （数字型）
/// <returns></returns>
public static OperationReturn CreateRecordCountSqlInMSSql(SessionInfo session, List <string> lstParam)
{
OperationReturn optReturn = new OperationReturn();
try
{
long lstartTime = long.Parse(lstParam[0]);
long lEndTime = long.Parse(lstParam[1]);
string rentToken = session.RentInfo.Token;
//是否分表
var tableInfo =
session.ListPartitionTables.FirstOrDefault(
t => t.TableName == ConstValue.TABLE_NAME_RECORD && t.PartType == TablePartType.DatetimeRange);
string strSql = string.Empty;
if (tableInfo == null) //没有分表 直接从T_21_001_租户表中查询
{
strSql = string.Format("select count(C002) as RecCount, Convert ( VARCHAR(10), C005, 120) as RecDate,C045 as RecDir from T_21_001_{0} " +
" where C006 > {1} and C006 <{2} " +
"group by Convert ( VARCHAR(10), C005, 120),C045 " +
"order by Convert ( VARCHAR(10), C005, 120)", rentToken, lstartTime, lEndTime);
}
else
{
//分表 判断起始时间是否跨月 如果没有跨月 直接从对应的月份查询 如果跨月 则分开两个表查询
// 算出表名
string strStartTableName = string.Format("T_21_001_{0}_{1}", rentToken, lstartTime.ToString().Substring(2, 4));
string strEndTableName = string.Format("T_21_001_{0}_{1}", rentToken, lEndTime.ToString().Substring(2, 4));
//如果没有跨月
if (strStartTableName == strEndTableName)
{
optReturn = CommonFuns.CheckTableExists(session, strStartTableName);
//如果表不存在 或者检查失败
if (!optReturn.Result)
{
optReturn.Code = (int)S6106WcfErrorCode.NoData;
return(optReturn);
}
strSql = string.Format("select count(C002) as RecCount, Convert ( VARCHAR(10), C004, 120) as RecDate,C045 as RecDir from {0} " +
" where C006 > {1} and C006 <{2} " +
"group by Convert ( VARCHAR(10), C004, 120),C045 " +
"", strStartTableName, lstartTime, lEndTime);
}
else
{
DateTime dtStart = CommonFunctions.StringToDateTime(lstartTime.ToString());
DateTime dtEnd = CommonFunctions.StringToDateTime(lEndTime.ToString());
List <string> lstTables = CommonFunctions.GetTablesByTime(ConstValue.TABLE_NAME_RECORD, dtStart, dtEnd, rentToken);
for (int i = 0; i < lstTables.Count; i++)
{
optReturn = CommonFuns.CheckTableExists(session, lstTables[i]);
if (optReturn.Result)
{
if (i == 0)
{
strSql = string.Format("select count(C002) as RecCount, Convert ( VARCHAR(10), C004, 120) as RecDate,C045 as RecDir from {0} " +
" where C006 > {1} " +
"group by Convert ( VARCHAR(10), C004, 120),C045 ", lstTables[i], lstartTime);
continue;
}
if (!string.IsNullOrEmpty(strSql))
{
strSql += " union ";
}
strSql += string.Format("select count(C002) as RecCount, Convert ( VARCHAR(10), C004, 120) as RecDate,C045 as RecDir from {0} " +
" where C006 < {1} " +
"group by Convert ( VARCHAR(10), C004, 120),C045 ", lstTables[i], lEndTime);
}
}
if (!string.IsNullOrEmpty(strSql))
{
strSql += "order by Convert ( VARCHAR(10), C004, 120)";
}
}
optReturn.Message = "strStartTableName = " + strStartTableName + " , strEndTableName = " + strEndTableName;
}
if (string.IsNullOrEmpty(strSql))
{
optReturn.Code = (int)S6106WcfErrorCode.NoData;
optReturn.Result = false;
return(optReturn);
}
optReturn.Result = true;
optReturn.Code = Defines.RET_SUCCESS;
optReturn.StringValue = strSql;
}
catch (Exception ex)
{
optReturn.Result = false;
optReturn.Code = Defines.RET_FAIL;
optReturn.Message = ex.Message;
return(optReturn);
}
return(optReturn);
}
/// <summary>
/// 获得mssql时的sql语句
/// </summary>
/// <param name="session"></param>
/// <param name="lstParam"></param>
/// lstParam[0] : 开始时间 （数字型）
/// lstParam[1] : 结束时间 （数字型）
/// lstParam[2]及以后：可管理的用户
/// <returns></returns>
public static OperationReturn CreateUMPUsedCountSql(SessionInfo session, List <string> lstParam)
{
OperationReturn optReturn = new OperationReturn();
try
{
long lstartTime = long.Parse(lstParam[0]);
long lEndTime = long.Parse(lstParam[1]);
string rentToken = session.RentInfo.Token;
//先组织处需要包含的用户ID的where语句
string strUser = string.Empty;
if (lstParam.Count > 2)
{
strUser = "******";
for (int i = 2; i < lstParam.Count; i++)
{
strUser += lstParam[i] + ",";
}
strUser = strUser.TrimEnd(',');
strUser += ")";
}
//是否分表
var tableInfo =
session.ListPartitionTables.FirstOrDefault(
t => t.TableName == ConstValue.TABLE_NAME_OPTLOG && t.PartType == TablePartType.DatetimeRange);
string strSql = string.Empty;
if (tableInfo == null) //没有分表 直接从T_11_901_租户表中查询
{
strSql = string.Format("SELECT C001,C008 FROM T_11_901_{0}" +
" where C004 = 110001 and C008 > {1} and C008 < {2} and C009 = 'R1' ", rentToken, lstartTime, lEndTime);
strSql += strUser;
}
else
{
//分表 判断起始时间是否跨月 如果没有跨月 直接从对应的月份查询 如果跨月 则分开两个表查询
// 算出表名
string strStartTableName = string.Format("T_11_901_{0}_{1}", rentToken, lstartTime.ToString().Substring(2, 4));
string strEndTableName = string.Format("T_11_901_{0}_{1}", rentToken, lEndTime.ToString().Substring(2, 4));
//如果没有跨月
if (strStartTableName == strEndTableName)
{
optReturn = CommonFuns.CheckTableExists(session, strStartTableName);
//如果表不存在 或者检查失败
if (!optReturn.Result)
{
optReturn.Code = (int)S6106WcfErrorCode.NoData;
return(optReturn);
}
strSql = string.Format("SELECT C001,C008 FROM {0} " +
" where C004 = 110001 and C008 > {1} and C008 <= {2} and C009 = 'R1' "
, strStartTableName, lstartTime, lEndTime);
strSql += strUser;
}
else
{
DateTime dtStart = CommonFunctions.StringToDateTime(lstartTime.ToString());
DateTime dtEnd = CommonFunctions.StringToDateTime(lEndTime.ToString());
List <string> lstTables = CommonFunctions.GetTablesByTime(ConstValue.TABLE_NAME_OPTLOG, dtStart, dtEnd, rentToken);
for (int i = 0; i < lstTables.Count; i++)
{
optReturn = CommonFuns.CheckTableExists(session, lstTables[i]);
if (optReturn.Result)
{
if (!string.IsNullOrEmpty(strSql))
{
strSql += " union ";
}
strSql = string.Format("SELECT C001,C008 FROM {0}" +
" where C004 = 110001 and C008 > {1} and C008 <= {2} and C009 = 'R1' "
, lstTables[i], lstartTime, lEndTime);
strSql += strUser;
}
}
}
}
if (string.IsNullOrEmpty(strSql))
{
optReturn.Code = (int)S6106WcfErrorCode.NoData;
optReturn.Result = false;
return(optReturn);
}
optReturn.Result = true;
optReturn.Code = Defines.RET_SUCCESS;
optReturn.StringValue = strSql;
}
catch (Exception ex)
{
optReturn.Result = false;
optReturn.Code = Defines.RET_FAIL;
optReturn.Message = ex.Message;
return(optReturn);
}
return(optReturn);
}