private DataTable GetInvalidExt(DateTime start, DateTime end, int type)
{
var helper = new TestRoomCodeHelper();
var codes = helper.GetValidTestRoomCodeList();
var result = new DataTable();
foreach (var code in codes)
{
var sql = string.Format(@" EXEC dbo.sp_pxjz_report @testcode = '{0}'
, @ftype = {1}
, @startdate = '{2}'
, @enddate = '{3}'
, @pageSize = 9999
, @page = 1
, @fldSort = '1'
, @Sort = 0
, @pageCount = 9999
, @Counts = 0", code, type, start, end);
var table = GetDataTable(sql);
if (table != null)
{
result.Merge(table, true, MissingSchemaAction.Add);
}
}
return(result);
}
public DataTable GetPXJZReportInfo(String segment, String company, String testroom, DateTime Start, DateTime End, Int32 fTpe)
{
String sql = "";
DataTable dt = new DataTable();
if (!String.IsNullOrEmpty(testroom))
{
sql = String.Format(@" EXEC dbo.sp_pxjz_report @testcode = '{0}', @ftype = {1}, @startdate = '{2}', @enddate = '{3}', @pageSize=9999, @page=1, @fldSort='segment', @Sort=0, @pageCount=9999,@Counts=0",
testroom,
fTpe,
Start.ToString("yyyy-MM-dd"),
End.AddDays(1).ToString("yyyy-MM-dd"));
//logger.Error(sql);
dt = GetDataTable(sql);
}
else
{
TestRoomCodeHelper trch = new TestRoomCodeHelper();
List <String> list = trch.GetValidTestRoomCodeList();
List <String> real1 = new List <string>();
List <String> real2 = new List <string>();
if (!String.IsNullOrEmpty(company))
{
foreach (var item in list)
{
if (item.StartsWith(company))
{
real1.Add(item);
}
}
}
else
{
real1.AddRange(list);
}
if (!String.IsNullOrEmpty(segment))
{
foreach (var item in real1)
{
if (item.StartsWith(segment))
{
real2.Add(item);
}
}
}
else
{
real2.AddRange(real1);
}
if (real2.Count == 0)
{
return(null);
}
int j = 0;
foreach (var item in real2)
{
if (item.Length == 16)
{
sql = String.Format(@" EXEC dbo.sp_pxjz_report @testcode = '{0}', @ftype = {1}, @startdate = '{2}', @enddate = '{3}', @pageSize=9999, @page=1, @fldSort='segment', @Sort=0, @pageCount=9999,@Counts=0",
item,
fTpe,
Start.ToString("yyyy-MM-dd"),
End.AddDays(1).ToString("yyyy-MM-dd"));
DataTable Data = GetDataTable(sql);
//logger.Error(sql);
if (j == 0)
{
dt = Data.Clone();
j++;
}
foreach (DataRow row in Data.Rows)
{
dt.ImportRow(row);
}
}
}
}
return(dt);
}
public DataTable GetOperateLogInfo(String segment, String company, String testroom, DateTime Start, DateTime End, String username, int pageindex, int PageSize, int doCount)
{
#region 显示字段
string fileds = " * ";
fileds = @" ID,
modifiedby AS 用户,
modifiedDate AS 操作日期,
optType AS 操作类型,
modelIndex ,
modelCode ,
dataID ,
segmentName AS 标段,
companyName AS 单位,
testRoom AS 试验室,
modelName AS 模板,
reportName AS 报告名称,
reportNumber AS 报告编号,
modifyItem,
comment ";
#endregion
#region 查询条件
String sql = String.Format(@" and modifiedDate>=''{0}'' AND modifiedDate<''{1}''",
Start.ToString("yyyy-MM-dd"),
End.AddDays(1).ToString("yyyy-MM-dd"));
if (testroom != "")
{
sql += " AND testRoomCode=''" + testroom + "'' ";
}
else
{
TestRoomCodeHelper trch = new TestRoomCodeHelper();
List <String> list = trch.GetValidTestRoomCodeList();
List <String> real1 = new List <string>();
List <String> real2 = new List <string>();
if (!String.IsNullOrEmpty(company))
{
foreach (var item in list)
{
if (item.StartsWith(company))
{
real1.Add(item);
}
}
}
else
{
real1.AddRange(list);
}
if (!String.IsNullOrEmpty(segment))
{
foreach (var item in real1)
{
if (item.StartsWith(segment))
{
real2.Add(item);
}
}
}
else
{
real2.AddRange(real1);
}
if (real2.Count == 0)
{
return(null);
}
sql += " AND TestRoomCode in (''" + String.Join("'',''", real2.ToArray()) + "'') ";
}
ApplicationContext AppContext = ApplicationContext.Current;
if (!AppContext.IsAdministrator)
{
sql += " AND modifiedby not in (''developer'') ";
}
if (!String.IsNullOrEmpty(username))
{
sql += " And modifiedby like ''%" + username + "%'' ";
}
#endregion
String sql_select = "";
DataTable dt = new DataTable();
sql_select = String.Format(@" EXEC dbo.sp_pager @tblname = '{0}', @strGetFields = '{1}', @fldName = '{2}', @PageSize = {3}, @PageIndex = {4},@doCount={5},@OrderType={6},@strWhere='{7}'",
"sys_operatelog",
fileds,
"modifiedDate",
PageSize,
pageindex,
doCount,
1, sql);
//logger.Error("Opear_QQ2:" + sql_select);
dt = GetDataTable(sql_select);
return(dt);
}
public DataTable GetLoginLogInfo(String segment, String company, String testroom, DateTime Start, DateTime End, String username, int pageindex, int PageSize, int doCount)
{
// @tblname VARCHAR(255), -- 表名
//@strGetFields nvarchar(1000) = "*", -- 需要返回的列
//@fldName varchar(255)='', -- 排序的字段名
//@PageSize int = 10, -- 页尺寸
//@PageIndex int = 1, -- 页码
//@doCount bit = 0, -- 返回, 非0 值则返回记录总数
//@OrderType bit = 0, -- 设置排序类型, 非0 值则降序
//@strWhere varchar(1500) = '' -- 查询条件(注意: 不要加where)
#region 显示字段
string fileds = " * ";
ApplicationContext AppContext = ApplicationContext.Current;
if (AppContext.IsAdministrator)
{
fileds = @" UserName as 用户 ,
ipAddress as IP地址,
macAddress as 物理地址,
machineName as 机器名,
osVersion as 操作系统,
osUserName as 系统账户,
ProjectName as 项目,
SegmentName as 标段,
CompanyName as 单位,
TestRoomName as 试验室,
FirstAccessTime as 登录时间,
LastAccessTime as 退出时间 ";
}
else
{
fileds = @" UserName as 用户 ,
ProjectName as 项目 ,
SegmentName as 标段,
CompanyName as 单位,
TestRoomName as 试验室,
FirstAccessTime as 登录时间,
LastAccessTime as 退出时间 ";
}
#endregion
#region 查询条件
String sql = String.Format(@" and FirstAccessTime>=''{0}'' AND FirstAccessTime<''{1}''",
Start.ToString("yyyy-MM-dd"),
End.AddDays(1).ToString("yyyy-MM-dd"));
if (testroom != "")
{
sql += " AND TestRoomCode=''" + testroom + "'' ";
}
else
{
TestRoomCodeHelper trch = new TestRoomCodeHelper();
List <String> list = trch.GetValidTestRoomCodeList();
List <String> real1 = new List <string>();
List <String> real2 = new List <string>();
if (!String.IsNullOrEmpty(company))
{
foreach (var item in list)
{
if (item.StartsWith(company))
{
real1.Add(item);
}
}
}
else
{
real1.AddRange(list);
}
if (!String.IsNullOrEmpty(segment))
{
foreach (var item in real1)
{
if (item.StartsWith(segment))
{
real2.Add(item);
}
}
}
else
{
real2.AddRange(real1);
}
if (real2.Count == 0)
{
return(null);
}
sql += " AND TestRoomCode in (''" + String.Join("'',''", real2.ToArray()) + "'') ";
}
if (!String.IsNullOrEmpty(username))
{
sql += " And UserName like ''%" + username + "%'' ";
}
#endregion
String sql_select = "";
DataTable dt = new DataTable();
sql_select = String.Format(@" EXEC dbo.sp_pager @tblname = '{0}', @strGetFields = '{1}', @fldName = '{2}', @PageSize = {3}, @PageIndex = {4},@doCount={5},@OrderType={6},@strWhere='{7}'",
"sys_loginlog",
fileds,
"FirstAccessTime",
PageSize,
pageindex,
doCount,
1, sql);
//logger.Error("Login_QQ"+doCount.ToString()+":" + sql_select);
dt = GetDataTable(sql_select);
return(dt);
}
public DataTable GetOperateLogList(String segment, String company, String testroom, DateTime Start, DateTime End, String username, int pageindex, int PageSize, int doCount)
{
#region 显示字段
string fileds = " * ";
fileds = @" a.ID,a.modifiedby AS 用户,a.modifiedDate AS 操作日期,a.modifyItem,
a.DataName AS 报告名称,a.BGBH AS 报告编号,c.Name AS 模板,a.optType AS 操作类型,
d.标段名称,d.单位名称,d.试验室名称 ";
#endregion
#region 查询条件
String sql = String.Format(@" and modifiedDate>=''{0}'' AND modifiedDate<''{1}''",
Start.ToString("yyyy-MM-dd"),
End.AddDays(1).ToString("yyyy-MM-dd"));
if (testroom != "")
{
sql += " AND testRoomCode=''" + testroom + "'' ";
}
else
{
TestRoomCodeHelper trch = new TestRoomCodeHelper();
List <String> list = trch.GetValidTestRoomCodeList();
List <String> real1 = new List <string>();
List <String> real2 = new List <string>();
if (!String.IsNullOrEmpty(company))
{
foreach (var item in list)
{
if (item.StartsWith(company))
{
real1.Add(item);
}
}
}
else
{
real1.AddRange(list);
}
if (!String.IsNullOrEmpty(segment))
{
foreach (var item in real1)
{
if (item.StartsWith(segment))
{
real2.Add(item);
}
}
}
else
{
real2.AddRange(real1);
}
if (real2.Count == 0)
{
return(null);
}
sql += " AND testRoomCode in (''" + String.Join("'',''", real2.ToArray()) + "'') ";
}
ApplicationContext AppContext = ApplicationContext.Current;
if (!AppContext.IsAdministrator)
{
sql += " AND modifiedby not in (''developer'') ";
}
if (!String.IsNullOrEmpty(username))
{
try
{
string dataID = new Guid(username).ToString();
sql += " And dataID = ''" + dataID + "'' ";
}
catch
{
sql += " And modifiedby like ''%" + username + "%'' ";
}
}
#endregion
String sql_select = "";
DataTable dt = new DataTable();
sql_select = String.Format(@" EXEC dbo.sp_pager @tblname = '{0}', @strGetFields = '{1}', @fldName = '{2}', @PageSize = {3}, @PageIndex = {4},@doCount={5},@OrderType={6},@strWhere='{7}'",
@"dbo.sys_operate_log a
JOIN dbo.sys_module c ON a.ModuleID = c.ID
JOIN dbo.v_bs_codeName d ON a.testRoomCode=d.试验室编码",
fileds,
"modifiedDate",
PageSize,
pageindex,
doCount,
1, sql);
dt = GetDataTable(sql_select);
return(dt);
}
public DataTable InitDataModificationList(String segment, String company, String testroom, DateTime start, DateTime end, String status, String content, String user)
{
String sql = String.Format(@"select ID,DataID,ModelIndex,ModelCode,State as 状态,Segment as 标段,CompanyName as 单位名称,TestRoomName as 试验室名称,ModelName as 模板名称,SponsorPerson as 申请者,SponsorDate as 申请日期,Caption as 内容,Reason as 原因,ProcessReason as 处理意见 from sys_biz_DataModification where
SponsorDate>='{0}' AND SponsorDate<'{1}'",
start.ToString("yyyy-MM-dd"),
end.AddDays(1).ToString("yyyy-MM-dd"));
if (testroom != "")
{
sql += " AND LEFT(ModelCode,16)='" + testroom + "' ";
}
else
{
TestRoomCodeHelper trch = new TestRoomCodeHelper();
List <String> list = trch.GetValidTestRoomCodeList();
List <String> real1 = new List <string>();
List <String> real2 = new List <string>();
if (!String.IsNullOrEmpty(company))
{
foreach (var item in list)
{
if (item.StartsWith(company))
{
real1.Add(item);
}
}
}
else
{
real1.AddRange(list);
}
if (!String.IsNullOrEmpty(segment))
{
foreach (var item in real1)
{
if (item.StartsWith(segment))
{
real2.Add(item);
}
}
}
else
{
real2.AddRange(real1);
}
if (real2.Count == 0)
{
return(null);
}
sql += " AND LEFT(ModelCode,16) in ('" + String.Join("','", real2.ToArray()) + "') ";
}
if (user != "")
{
sql += " AND SponsorPerson like '%" + user + "%' ";
}
if (content != "")
{
sql += " AND Caption like '%" + content + "%' ";
}
sql += " ORDER BY SponsorDate DESC";
DataTable Data = GetDataTable(sql);
return(Data);
}