private String loginLog(string proc, int ntype)
{
BLL_LoginLog BLL = new BLL_LoginLog();
if (!String.IsNullOrEmpty(SelectedTestRoomCodes))
{
string SQL = "SELECT MAX(CompanyName) CompanyName,MAX(SegmentName) SegmentName,COUNT(1) as nTotals, LEFT(TestRoomCode,12) AS CompanyCode FROM dbo.sys_loginlog a join sys_tree b ON LEFT(TestRoomCode,12)=b.NodeCode WHERE TestRoomCode IN (" + SelectedTestRoomCodes + ") AND FirstAccessTime>='" + StartDate + "' AND FirstAccessTime<'" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "' GROUP BY LEFT(TestRoomCode,12) ORDER BY MAX(b.OrderID) ";
DataSet ds = DbHelperSQL.Query(SQL);
if (ds != null)
{
DataTable dt = ds.Tables[0];
List <ChartModel> list = new List <ChartModel>();
foreach (DataRow dr in dt.Rows)
{
ChartModel trcs = new ChartModel();
trcs.Description = dr["SegmentName"].ToString() + "\n" + dr["CompanyName"].ToString();
trcs.IntNumber = Int32.Parse(dr["nTotals"].ToString());
//trcs.IntNumberMarks = Int32.Parse(dr["nUserCounts"].ToString());
trcs.Para1 = dr["CompanyCode"].ToString();
list.Add(trcs);
}
return(JsonConvert.SerializeObject(list));
}
else
{
return("");
}
}
else
{
return("");
}
}
private String unpopfailgrid(String name, String fileds, String sqlwhere, String key, int ftype, out int pageCount, out int records)
{
sys_line sysBaseLine = System.Web.HttpContext.Current.Session["SysBaseLine"] as sys_line;
if (sysBaseLine.IsActive == 0)
{
sqlwhere = "and ReportDate>='" + StartDate + "' AND ReportDate<'" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "' AND (SGComment IS NULL OR JLComment IS NULL) ";
if (!String.IsNullOrEmpty(Request.Params["sTestcode"].ToString()))
{
sqlwhere += " and TestRoomCode='" + Request.Params["sTestcode"].ToString() + "' ";
}
}
else
{
name = "v_invalid_document";
sqlwhere = "and ReportDate>='" + StartDate + "' AND ReportDate<'" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "' AND F_InvalidItem NOT LIKE '%#%' AND AdditionalQualified=0 AND (SGComment IS NULL OR JLComment IS NULL) ";
if (!String.IsNullOrEmpty(Request.Params["sTestcode"].ToString()))
{
sqlwhere += " and TestRoomCode='" + Request.Params["sTestcode"].ToString() + "' ";
}
}
BLL_LoginLog BLL = new BLL_LoginLog();
DataTable dt = BLL.GetDataTablePager(name, fileds, sqlwhere, key, "ReportDate", OrderType, PageIndex, PageSize, out pageCount, out records);
if (dt != null)
{
return(JsonConvert.SerializeObject(dt));
}
else
{
return("");
}
}
private String SMS(String name, String fileds, String sqlwhere, String key, int ftype, out int pageCount, out int records)
{
sqlwhere = "and SentTime>='" + StartDate + "' AND SentTime<'" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "'";
if (!String.IsNullOrEmpty(Request.Params["username"]))
{
sqlwhere += " and PersonName LIKE '%" + Request.Params["username"].Trim() + "%'";
}
if (!String.IsNullOrEmpty(Request.Params["tel"]))
{
sqlwhere += " and SMSPhone LIKE '%" + Request.Params["tel"].Trim() + "%'";
}
if (!String.IsNullOrEmpty(SelectedTestRoomCodes))
{
sqlwhere += " and testroomcode in (" + SelectedTestRoomCodes + ") ";
}
else
{
sqlwhere += "and 1=0";
}
BLL_LoginLog BLL = new BLL_LoginLog();
DataTable dt = BLL.GetDataTablePager(name, fileds, sqlwhere, key, OrderField, OrderType, PageIndex, PageSize, out pageCount, out records);
if (dt != null)
{
return(JsonConvert.SerializeObject(dt));
}
else
{
return("");
}
}
/// <summary>
/// 获取资料查询列表
/// </summary>
/// <returns></returns>
public DataTable DocumentSearch()
{
string sqlwhere = string.Empty;
BLL_LoginLog BLL = new BLL_LoginLog();
#region 查询条件
sqlwhere = " and status>0 and BGRQ>='" + StartDate + "' AND BGRQ<'" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "'";
if (!String.IsNullOrEmpty(Request.Params["bgmc"]))
{
sqlwhere += " and m.name LIKE '%" + Request.Params["bgmc"].Trim() + "%'";
}
if (!String.IsNullOrEmpty(Request.Params["wtbh"]))
{
sqlwhere += " and WTBH LIKE '%" + Request.Params["wtbh"].Trim() + "%'";
}
if (!String.IsNullOrEmpty(Request.Params["bgbh"]))
{
sqlwhere += " and BGBH LIKE '%" + Request.Params["bgbh"].Trim() + "%'";
}
if (!String.IsNullOrEmpty(SelectedTestRoomCodes))
{
sqlwhere += " and testroomcode in (" + SelectedTestRoomCodes + ") ";
}
else
{
sqlwhere += "and 1=0";
}
#endregion
#region 查询脚本
string Sql = @"
SELECT
sys_tree.description 标段
,t2.description 单位
,t1.description 试验室
,sys_module.name as 模版名称
,BGBH 报告编号
,WTBH 委托编号
,CONVERT(NVARCHAR(10),BGRQ,120) 报告日期
FROM sys_document
left outer join sys_module on sys_document.ModuleId = sys_module.id
left outer join sys_tree on sys_document.SegmentCode = sys_tree.nodecode
left outer join sys_tree t1 on sys_document.TestRoomCode = t1.nodecode
left outer join sys_tree t2 on sys_document.CompanyCode = t2.nodecode
where 1=1
";
#endregion
DataSet DS = (Sql + sqlwhere).GetData();
return(DS.Tables[0] != null ? DS.Tables[0] : null);
}
private String loginLog(String name, String fileds, String sqlwhere, String key, int ftype, out int pageCount, out int records)
{
fileds = @"loginDay ,
ipAddress ,
macAddress ,
machineName ,
osVersion ,
osUserName ,
UserName ,
ProjectName ,
SegmentName ,
CompanyName ,
TestRoomName ,
TestRoomCode ,
FirstAccessTime ,
LastAccessTime";
sqlwhere = "and FirstAccessTime>='" + StartDate + "' AND FirstAccessTime<'" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "'";
#region For 首页
if (!"NUM".RequestStr().IsNullOrEmpty())
{
sqlwhere += " and testroomcode in ('" + "NUM".RequestStr() + "') ";
}
else if (!String.IsNullOrEmpty(SelectedTestRoomCodes))
{
sqlwhere += " and testroomcode in (" + SelectedTestRoomCodes + ") ";
}
else
{
sqlwhere += "and 1=0";
}
#endregion
if (!String.IsNullOrEmpty(Request.Params["username"]))
{
sqlwhere += " and UserName LIKE '%" + Request.Params["username"].Trim() + "%'";
}
BLL_LoginLog BLL = new BLL_LoginLog();
DataTable dt = BLL.GetDataTablePager(name, fileds, sqlwhere, key, "loginDay", OrderType, PageIndex, PageSize, out pageCount, out records);
if (dt != null)
{
return(JsonConvert.SerializeObject(dt));
}
else
{
return("");
}
}
private String ageremindundo(String proc, int ftype, out int pageCount, out int records)
{
BLL_LoginLog BLL = new BLL_LoginLog();
DataTable dt = BLL.GetProcDataTable(proc, StartDate, DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd"), SelectedTestRoomCodes, ftype, PageIndex, PageSize, OrderField, OrderType, out pageCount, out records);
if (dt != null)
{
return(JsonConvert.SerializeObject(dt));
}
else
{
return("");
}
}
private String stadiumLog(String name, String fileds, String sqlwhere, String key, int ftype, out int pageCount, out int records)
{
BLL_LoginLog BLL = new BLL_LoginLog();
DataTable dt = BLL.GetDataTablePager(name, fileds, sqlwhere, key, OrderField, OrderType, PageIndex, PageSize, out pageCount, out records);
if (dt != null)
{
return(JsonConvert.SerializeObject(dt));
}
else
{
return("");
}
}
public static DataSet GetData(this string SqlText)
{
DataSet Ds = new DataSet();
BLL_LoginLog BLL = new BLL_LoginLog();
using (System.Data.SqlClient.SqlConnection Conn = BLL.Connection as System.Data.SqlClient.SqlConnection)
{
Conn.Open();
using (System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(SqlText, Conn))
{
using (System.Data.SqlClient.SqlDataAdapter Adp = new System.Data.SqlClient.SqlDataAdapter(Cmd))
{
Adp.Fill(Ds);
}
Cmd.Dispose();
}
Conn.Close();
Conn.Dispose();
}
return(Ds);
}
private String onelogin(string proc, string testcode)
{
BLL_LoginLog BLL = new BLL_LoginLog();
DataTable dt = BLL.GetProcDataTableChartsPara5(proc, StartDate, DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd"), testcode, 1);
if (dt != null)
{
List <ChartModel> list = new List <ChartModel>();
foreach (DataRow dr in dt.Rows)
{
ChartModel trcs = new ChartModel();
trcs.Description = dr["loginDay"].ToString();
trcs.IntNumber = Int32.Parse(dr["nTotals"].ToString());
list.Add(trcs);
}
return(JsonConvert.SerializeObject(list));
}
else
{
return("");
}
}
private String operateLog(String name, String fileds, String sqlwhere, String key, int ftype, out int pageCount, out int records)
{
sys_line sysBaseLine = System.Web.HttpContext.Current.Session["SysBaseLine"] as sys_line;
if (sysBaseLine.IsActive == 0)
{
}
else
{
name = "v_operate_log";
}
sqlwhere = "and modifiedDate>='" + StartDate + "' AND modifiedDate<'" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "'";
if (!String.IsNullOrEmpty(Request.Params["username"]))
{
sqlwhere += " and modifiedby LIKE '%" + Request.Params["username"].Trim() + "%'";
}
if (!String.IsNullOrEmpty(SelectedTestRoomCodes))
{
sqlwhere += " and testroomcode in (" + SelectedTestRoomCodes + ") ";
}
else
{
sqlwhere += "and 1=0";
}
BLL_LoginLog BLL = new BLL_LoginLog();
DataTable dt = BLL.GetDataTablePager(name, fileds, sqlwhere, key, "modifiedDate", OrderType, PageIndex, PageSize, out pageCount, out records);
if (dt != null)
{
return(JsonConvert.SerializeObject(dt));
}
else
{
return("");
}
}
private String loginpop(string proc, string testcode)
{
//sTestcode = Request.Params["sTestcode"];
//result = loginpop("spweb_login_charts_pop", sTestcode);
BLL_LoginLog BLL = new BLL_LoginLog();
testcode = GetSelectTree(testcode, SelectedTestRoomCodes);
//DataTable dt = BLL.GetProcDataTableChartsPara5(proc, StartDate, DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd"), testcode, 1);
if (!String.IsNullOrEmpty(testcode))
{
string SQL = " SELECT UserName,MAX(SegmentName) SegmentName,MAX(CompanyName) CompanyName,MAX(TestRoomName) TestRoomName,COUNT(1) nTotals FROM dbo.sys_loginlog WHERE TestRoomCode IN (" + testcode + ") AND loginDay>='" + StartDate + "' AND loginDay<'" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "' GROUP BY UserName ORDER BY COUNT(1) DESC ";
DataSet ds = DbHelperSQL.Query(SQL);
if (ds != null)
{
DataTable dt = ds.Tables[0];
List <ChartModel> list = new List <ChartModel>();
foreach (DataRow dr in dt.Rows)
{
ChartModel trcs = new ChartModel();
trcs.Description = dr["UserName"].ToString();
trcs.IntNumber = Int32.Parse(dr["nTotals"].ToString());
trcs.Para1 = dr["UserName"].ToString();
list.Add(trcs);
}
return(JsonConvert.SerializeObject(list));
}
else
{
return("");
}
}
else
{
return("");
}
}
/// <summary>
/// 不合格数据分析
/// </summary>
/// <param name="name"></param>
/// <param name="fileds"></param>
/// <param name="sqlwhere"></param>
/// <param name="key"></param>
/// <param name="ftype"></param>
/// <param name="pageCount"></param>
/// <param name="records"></param>
/// <returns></returns>
private String evaluatedata(String name, String fileds, String sqlwhere, String key, int ftype, out int pageCount, out int records)
{
sys_line sysBaseLine = System.Web.HttpContext.Current.Session["SysBaseLine"] as sys_line;
if (sysBaseLine.IsActive == 0)
{
sqlwhere = "and ReportDate>='" + StartDate + "' AND ReportDate<'" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "'";
if (!String.IsNullOrEmpty(SelectedTestRoomCodes))
{
sqlwhere += " and TestRoomCode in (" + SelectedTestRoomCodes + ") ";
}
if (!String.IsNullOrEmpty(Request.Params["sReportCode"]))
{
sqlwhere += " and ReportNumber LIKE '%" + Request.Params["sReportCode"].Trim() + "%'";
}
if (!String.IsNullOrEmpty(Request.Params["sReportName"]))
{
sqlwhere += " and ReportName LIKE '%" + Request.Params["sReportName"].Trim() + "%'";
}
}
else
{
name = "v_invalid_document";
sqlwhere = "and ReportDate>='" + StartDate + "' AND ReportDate<'" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "' AND F_InvalidItem NOT LIKE '%#%' AND AdditionalQualified=0 ";
#region FOR 首页
if (!string.IsNullOrEmpty("RPNAME".RequestStr()))
{
switch ("RPNAME".RequestStr())
{
case "1": //混凝土抗压
sqlwhere += @" AND ModelIndex IN (
'A46D053A-0776-467F-9136-0AA45D2021D2',
'90973417-6C68-4E90-849B-0B1AEB36A234',
'E1E2A6EA-C092-4C8D-920D-170BE5128BC4',
'D06E5471-EE69-49E4-9AF4-1BE8AB2E0310',
'8A2B5231-8602-4519-8919-1FF649ED2E41',
'FA0D1C8C-DE69-4C16-A301-3A861C6B11CD',
'D1C56FBD-2EDC-40FF-956F-4DA7178F2DD3',
'4500603A-5BE7-4574-BBBA-77B4626A3EA1',
'05D0D71B-DEF3-42EE-A16A-79B34DE97E9B',
'A14D0408-690C-420C-9D41-7A2FA515C371',
'F21C9D4A-CB80-4705-AA7C-81A8BD17DB7D',
'C9A1DD95-79BF-4543-924B-94362381E705',
'E25F399F-A147-4663-B74A-98A46A39F121',
'269EE291-F6E7-4AEA-B4C7-A7D02B7C59DE',
'3AB86F48-7A73-46B1-8AA4-D1B55DE1EE8A',
'F34C2B8B-DDBE-4C04-BD01-F08B0F479AE8',
'A974B39B-EC88-4917-A1D5-F8FBBFBB1F7A',
'C72E2DD8-EC76-4663-B400-FB1B7F8F8C2B',
'7894EACD-DA8C-4659-9891-FB4D62EB9FF5'
)";
break;
case "2": //钢筋实验
sqlwhere += @" AND ModelIndex IN (
'AE9F8E75-773F-4DEE-A3A8-3E871AC8598E',
'A98902C1-BB72-4E79-9C74-4746D1709D3B',
'68F05EBC-5D34-49C5-9B57-49B688DF24F7',
'9B8BD64A-2D9C-4B67-B9DC-53FCF67FD361',
'46622671-E829-412C-99E6-587525ED968F',
'C7B2620E-7F4C-4586-AEBB-59855B54E522',
'3B46BC3A-92DF-4AFC-AA85-AE74FC00F96D',
'377A20DA-7E27-4CD3-B9E1-B3C7993CF6EA',
'A12AD84C-A7D3-4B42-A9DF-6E80A3E3A0CF',
'0A2F0365-D561-4504-B602-98FCC5C3AB94',
'4C817CF9-E7F3-422D-975F-C8175E738382'
) ";
break;
case "3": //混凝土原材
sqlwhere += "AND ModelIndex IN (SELECT ID FROM dbo.sys_module WHERE CatlogCode LIKE '0001%') ";
break;
case "4": //其他
sqlwhere += @" AND ModelIndex not IN(SELECT ID FROM dbo.sys_module WHERE CatlogCode LIKE '0001%' or ID in('AE9F8E75-773F-4DEE-A3A8-3E871AC8598E','A98902C1-BB72-4E79-9C74-4746D1709D3B','68F05EBC-5D34-49C5-9B57-49B688DF24F7','9B8BD64A-2D9C-4B67-B9DC-53FCF67FD361','46622671-E829-412C-99E6-587525ED968F','C7B2620E-7F4C-4586-AEBB-59855B54E522','3B46BC3A-92DF-4AFC-AA85-AE74FC00F96D','377A20DA-7E27-4CD3-B9E1-B3C7993CF6EA','A12AD84C-A7D3-4B42-A9DF-6E80A3E3A0CF','0A2F0365-D561-4504-B602-98FCC5C3AB94','4C817CF9-E7F3-422D-975F-C8175E738382','A46D053A-0776-467F-9136-0AA45D2021D2','90973417-6C68-4E90-849B-0B1AEB36A234','E1E2A6EA-C092-4C8D-920D-170BE5128BC4','D06E5471-EE69-49E4-9AF4-1BE8AB2E0310','8A2B5231-8602-4519-8919-1FF649ED2E41','FA0D1C8C-DE69-4C16-A301-3A861C6B11CD','D1C56FBD-2EDC-40FF-956F-4DA7178F2DD3','4500603A-5BE7-4574-BBBA-77B4626A3EA1','05D0D71B-DEF3-42EE-A16A-79B34DE97E9B','A14D0408-690C-420C-9D41-7A2FA515C371','F21C9D4A-CB80-4705-AA7C-81A8BD17DB7D','C9A1DD95-79BF-4543-924B-94362381E705','E25F399F-A147-4663-B74A-98A46A39F121','269EE291-F6E7-4AEA-B4C7-A7D02B7C59DE','3AB86F48-7A73-46B1-8AA4-D1B55DE1EE8A','F34C2B8B-DDBE-4C04-BD01-F08B0F479AE8','A974B39B-EC88-4917-A1D5-F8FBBFBB1F7A','C72E2DD8-EC76-4663-B400-FB1B7F8F8C2B','7894EACD-DA8C-4659-9891-FB4D62EB9FF5')) ";
break;
case "5": //已处理
sqlwhere += @" AND (DealResult IS NOT NULL) AND DealResult <> '' ";
break;
case "6": //未处理
sqlwhere += @" AND (DealResult='' OR DealResult IS NULL) ";
break;
}
}
#endregion
if (!"NUM".RequestStr().IsNullOrEmpty())
{
sqlwhere += " and testroomcode in ('" + "NUM".RequestStr() + "') ";
}
else if (!String.IsNullOrEmpty(SelectedTestRoomCodes))
{
sqlwhere += " and testroomcode in (" + SelectedTestRoomCodes + ") ";
}
if (!String.IsNullOrEmpty(Request.Params["sReportCode"]))
{
sqlwhere += " and ReportNumber LIKE '%" + Request.Params["sReportCode"].Trim() + "%'";
}
if (!String.IsNullOrEmpty(Request.Params["sReportName"]))
{
sqlwhere += " and ReportName LIKE '%" + Request.Params["sReportName"].Trim() + "%'";
}
}
BLL_LoginLog BLL = new BLL_LoginLog();
DataTable dt = BLL.GetDataTablePager(name, fileds, sqlwhere, key, "OrderID", "ASC", PageIndex, PageSize, out pageCount, out records);
if (dt != null)
{
return(JsonConvert.SerializeObject(dt));
}
else
{
return("");
}
}
private void List()
{
if ("Act".RequestStr() != "List")
{
return;
}
string sqlwhere = "and ReportDate between '" + "StartDate".RequestStr() + "' AND '" + DateTime.Parse("EndDate".RequestStr()).ToString("yyyy-MM-dd") + "' AND F_InvalidItem NOT LIKE '%#%' AND AdditionalQualified=1 ";
if (!"NUM".RequestStr().IsNullOrEmpty())
{
sqlwhere += " and testroomcode in ('" + "NUM".RequestStr() + "') ";
}
else if (!String.IsNullOrEmpty(SelectedTestRoomCodes))
{
sqlwhere += " and testroomcode in (" + SelectedTestRoomCodes + ") ";
}
if (!String.IsNullOrEmpty(Request.Params["sReportCode"]))
{
sqlwhere += " and ReportNumber LIKE '%" + Request.Params["sReportCode"].Trim() + "%'";
}
if (!String.IsNullOrEmpty(Request.Params["sReportName"]))
{
sqlwhere += " and ReportName LIKE '%" + Request.Params["sReportName"].Trim() + "%'";
}
#region 使用脚本分页
string Sql = @"
DECLARE @Page int
DECLARE @PageSize int
SET @Page = {1}
SET @PageSize = {2}
SET NOCOUNT ON
DECLARE @TempTable TABLE (IndexId int identity, _keyID varchar(50))
INSERT INTO @TempTable
(
_keyID
)
select d.IndexID from v_invalid_document d left outer join sys_module m on d.ModelIndex = m.id
where 1=1 {0} Order By OrderID ASC
SELECT
d.*,convert(varchar,d.ReportDate,23) as ReportDate1,
d.F_InvalidItem as F_InvalidItem1,d.F_InvalidItem as F_InvalidItem2
FROM v_invalid_document d
left outer join sys_module on d.ModelIndex = sys_module.id
INNER JOIN @TempTable t ON d.IndexID = t._keyID
WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)
Order By OrderID ASC
DECLARE @C int
select @C= count(d.IndexID) from v_invalid_document d left outer join sys_module m on d.ModelIndex = m.id where 1=1 {0}
select @C
";
string PageIndex = Request["page"];
string PageSize = Request["rows"];
Sql = string.Format(Sql, sqlwhere, PageIndex, PageSize);
BLL_LoginLog BLL = new BLL_LoginLog();
DataSet DSs = new DataSet();
using (System.Data.SqlClient.SqlConnection Conn = BLL.Connection as System.Data.SqlClient.SqlConnection)
{
Conn.Open();
using (System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(Sql, Conn))
{
using (System.Data.SqlClient.SqlDataAdapter Adp = new System.Data.SqlClient.SqlDataAdapter(Cmd))
{
Adp.Fill(DSs);
}
}
Conn.Close();
}
int records = DSs.Tables[1].Rows[0][0].ToString().Toint();
#endregion
if (DSs.Tables[0] != null)
{
Response.Write(DSs.Tables[0].ToJsonForEasyUI(records, ""));
}
Response.End();
}
/// <summary>
/// 不合格数据分析
/// </summary>
/// <returns></returns>
public DataTable BHGSJFX()
{
sys_line sysBaseLine = System.Web.HttpContext.Current.Session["SysBaseLine"] as sys_line;
string sqlwhere = string.Empty;
string name = "biz_all_failreport";
if (sysBaseLine.IsActive == 0)
{
sqlwhere = "and ReportDate>='" + StartDate + "' AND ReportDate<'" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "'";
if (!String.IsNullOrEmpty(SelectedTestRoomCodes))
{
sqlwhere += " and TestRoomCode in (" + SelectedTestRoomCodes + ") ";
}
if (!String.IsNullOrEmpty(Request.Params["sReportCode"]))
{
sqlwhere += " and ReportNumber LIKE '%" + Request.Params["sReportCode"].Trim() + "%'";
}
if (!String.IsNullOrEmpty(Request.Params["sReportName"]))
{
sqlwhere += " and ReportName LIKE '%" + Request.Params["sReportName"].Trim() + "%'";
}
}
else
{
name = "v_invalid_document";
sqlwhere = "and ReportDate>='" + StartDate + "' AND ReportDate<'" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "' AND F_InvalidItem NOT LIKE '%#%' AND AdditionalQualified=0 ";
if (!String.IsNullOrEmpty(SelectedTestRoomCodes))
{
sqlwhere += " and TestRoomCode in (" + SelectedTestRoomCodes + ") ";
}
if (!String.IsNullOrEmpty(Request.Params["sReportCode"]))
{
sqlwhere += " and ReportNumber LIKE '%" + Request.Params["sReportCode"].Trim() + "%'";
}
if (!String.IsNullOrEmpty(Request.Params["sReportName"]))
{
sqlwhere += " and ReportName LIKE '%" + Request.Params["sReportName"].Trim() + "%'";
}
}
BLL_LoginLog BLL = new BLL_LoginLog();
DataTable dt = BLL.GetDataTablePager(name, "*", sqlwhere, "IndexID", "SectionName", "ASC", 1, 100000, out pageCount, out records);
dt.Columns.Remove("rowNum");
dt.Columns.Remove("CompanyCode");
dt.Columns.Remove("TestRoomCode");
dt.Columns.Remove("AdditionalQualified");
dt.Columns.Remove("DeviceType");
dt.Columns.Remove("WTBH");
dt.Columns["ReportDate"].ColumnName = "日期";
dt.Columns["ReportName"].ColumnName = "试验报告";
dt.Columns["ReportNumber"].ColumnName = "报告编码";
dt.Columns["F_InvalidItem"].ColumnName = "不合格项目";
dt.Columns["SGComment"].ColumnName = "原因分析";
dt.Columns["JLComment"].ColumnName = "监理意见";
dt.Columns["SectionName"].ColumnName = "标段";
dt.Columns["CompanyName"].ColumnName = "单位";
dt.Columns["TestRoomName"].ColumnName = "试验室";
dt.Columns["DealResult"].ColumnName = "处理情况";
dt.Columns.Add("标准值");
dt.Columns.Add("误差值");
dt.Columns["日期"].SetOrdinal(0);
dt.Columns["标段"].SetOrdinal(1);
dt.Columns["单位"].SetOrdinal(2);
dt.Columns["试验室"].SetOrdinal(3);
dt.Columns["试验报告"].SetOrdinal(4);
dt.Columns["不合格项目"].SetOrdinal(5);
dt.Columns["标准值"].SetOrdinal(6);
dt.Columns["误差值"].SetOrdinal(7);
dt.Columns["原因分析"].SetOrdinal(8);
dt.Columns["监理意见"].SetOrdinal(9);
dt.Columns["处理情况"].SetOrdinal(10);
dt.Columns["报告编码"].SetOrdinal(11);
string[] Temp = null;
string[] Temp1 = null;
string _XM = string.Empty;
string _SZ = string.Empty;
string _WCZ = string.Empty;
foreach (DataRow Dr in dt.Rows)
{
Temp = Dr["不合格项目"].ToString().Split(new string[] { "||" }, StringSplitOptions.RemoveEmptyEntries);
if (Temp == null)
{
continue;
}
foreach (string V in Temp)
{
if (V.IsNullOrEmpty())
{
break;
}
Temp1 = V.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
_XM += Temp1.Length >= 1 ? Temp1[0] + "<br />":"";
_SZ += Temp1.Length >= 2? Temp1[1] + "<br />":"";
_WCZ += Temp1.Length >= 3 ? Temp1[2] + "<br />" : "";
}
Dr["不合格项目"] = _XM;
Dr["标准值"] = _SZ;
Dr["误差值"] = _WCZ;
Temp = null;
Temp1 = null;
_XM = string.Empty;
_SZ = string.Empty;
_WCZ = string.Empty;
}
return(dt);
}
public DataTable LOGINLOG()
{
string Sql = @"select UserName ,
SegmentName ,
CompanyName ,
TestRoomName ,
Count(id) as 登录次数
from sys_loginlog
where 1=1
{0}
group by UserName ,
SegmentName ,
CompanyName ,
TestRoomName
order by SegmentName,CompanyName
";
string fileds = @"
UserName ,
SegmentName ,
CompanyName ,
TestRoomName ,
Count(id) as C ";
string sqlwhere = "and FirstAccessTime>='" + StartDate + "' AND FirstAccessTime<'" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "'";
#region For 首页
if (!"NUM".RequestStr().IsNullOrEmpty())
{
sqlwhere += " and testroomcode in ('" + "NUM".RequestStr() + "') ";
}
else if (!String.IsNullOrEmpty(SelectedTestRoomCodes))
{
sqlwhere += " and testroomcode in (" + SelectedTestRoomCodes + ") ";
}
else
{
sqlwhere += "and 1=0";
}
#endregion
if (!String.IsNullOrEmpty(Request.Params["username"]))
{
sqlwhere += " and UserName LIKE '%" + Request.Params["username"].Trim() + "%'";
}
BLL_LoginLog BLL = new BLL_LoginLog();
Sql = string.Format(Sql, sqlwhere);
DataTable dt = BLL.GetDataTable(Sql);
// DataTable dt = BLL.GetDataTablePager("sys_loginlog"+" Group by UserName , SegmentName , CompanyName , TestRoomName ", fileds, sqlwhere, "loginDay", "loginDay", "0", 1, 11111111, out pageCount, out records);
dt.Columns["UserName"].ColumnName = "用户名";
dt.Columns["SegmentName"].ColumnName = "标段";
dt.Columns["CompanyName"].ColumnName = "单位";
dt.Columns["TestRoomName"].ColumnName = "试验室";
return(dt);
}
private void List()
{
if ("Act".RequestStr() != "List")
{
return;
}
string sqlwhere = " and status>0 and BGRQ between '" + "StartDate".RequestStr() + "' AND '" + DateTime.Parse("EndDate".RequestStr()).AddDays(1).ToString("yyyy-MM-dd") + "'";
if (!string.IsNullOrEmpty("RPNAME".RequestStr()))
{
sqlwhere += @" AND m.StatisticsCatlog IN (" + Server.UrlDecode("RPNAME".RequestStr()) + ") ";
}
if (!String.IsNullOrEmpty(Request.Params["bgmc"]))
{
sqlwhere += " and m.name LIKE '%" + Request.Params["bgmc"].Trim() + "%'";
}
if (!String.IsNullOrEmpty(Request.Params["wtbh"]))
{
sqlwhere += " and WTBH LIKE '%" + Request.Params["wtbh"].Trim() + "%'";
}
if (!String.IsNullOrEmpty(Request.Params["bgbh"]))
{
sqlwhere += " and BGBH LIKE '%" + Request.Params["bgbh"].Trim() + "%'";
}
if (!"NUM".RequestStr().IsNullOrEmpty())
{
sqlwhere += " and testroomcode in ('" + "NUM".RequestStr() + "') ";
}
else if (!String.IsNullOrEmpty(SelectedTestRoomCodes))
{
sqlwhere += " and testroomcode in (" + SelectedTestRoomCodes + ") ";
}
BLL_LoginLog BLL = new BLL_LoginLog();
#region 使用脚本分页
string Sql = @"
DECLARE @Page int
DECLARE @PageSize int
SET @Page = {1}
SET @PageSize = {2}
SET NOCOUNT ON
DECLARE @TempTable TABLE (IndexId int identity, _keyID varchar(50))
INSERT INTO @TempTable
(
_keyID
)
select d.ID from sys_document d left outer join sys_module m on d.ModuleId = m.id
where 1=1 {0} Order By BGRQ DESC
SELECT
sys_document.ID,
BGBH ,
WTBH,CONVERT(NVARCHAR(10),BGRQ,120) BGRQ ,CompanyCode,TestRoomCode,SegmentCode,
sys_tree.description SegmentName, t2.description CompanyName,t1.description TestRoomName
,sys_module.DeviceType ,
ModuleId,
sys_module.name as MName
FROM sys_document
left outer join sys_module on sys_document.ModuleId = sys_module.id
left outer join sys_tree on sys_document.SegmentCode = sys_tree.nodecode
left outer join sys_tree t1 on sys_document.TestRoomCode = t1.nodecode
left outer join sys_tree t2 on sys_document.CompanyCode = t2.nodecode
INNER JOIN @TempTable t ON sys_document.ID = t._keyID
WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)
Order By BGRQ DESC
DECLARE @C int
select @C= count(d.ID) from sys_document d left outer join sys_module m on d.ModuleId = m.id where 1=1 {0}
select @C
";
string PageIndex = Request["page"];
string PageSize = Request["rows"];
Sql = string.Format(Sql, sqlwhere, PageIndex, PageSize);
DataSet DSs = new DataSet();
using (System.Data.SqlClient.SqlConnection Conn = BLL.Connection as System.Data.SqlClient.SqlConnection)
{
Conn.Open();
using (System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(Sql, Conn))
{
using (System.Data.SqlClient.SqlDataAdapter Adp = new System.Data.SqlClient.SqlDataAdapter(Cmd))
{
Adp.Fill(DSs);
}
}
Conn.Close();
}
int records = DSs.Tables[1].Rows[0][0].ToString().Toint();
#endregion
if (DSs.Tables[0] != null)
{
Response.Write(DSs.Tables[0].ToJsonForEasyUI(records, ""));
}
Response.End();
}
private String report_Search(String name, String fileds, String sqlwhere, String key, int ftype, out int pageCount, out int records)
{
fileds = @"ID,
BGBH ,
WTBH,CONVERT(NVARCHAR(10),BGRQ,120) BGRQ ,CompanyCode,TestRoomCode,SegmentCode,
'' SegmentName, '' CompanyName,'' TestRoomName, '' MName,'' DeviceType ,
ModuleId
";
sqlwhere = " and status>0 and BGRQ>='" + StartDate + "' AND BGRQ<'" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "'";
#region
if (!string.IsNullOrEmpty("RPNAME".RequestStr()))
{
switch ("RPNAME".RequestStr())
{
case "1": //混凝土抗压
sqlwhere += @" AND m.id IN ('A46D053A-0776-467F-9136-0AA45D2021D2',
'90973417-6C68-4E90-849B-0B1AEB36A234',
'E1E2A6EA-C092-4C8D-920D-170BE5128BC4',
'D06E5471-EE69-49E4-9AF4-1BE8AB2E0310',
'8A2B5231-8602-4519-8919-1FF649ED2E41',
'FA0D1C8C-DE69-4C16-A301-3A861C6B11CD',
'D1C56FBD-2EDC-40FF-956F-4DA7178F2DD3',
'4500603A-5BE7-4574-BBBA-77B4626A3EA1',
'05D0D71B-DEF3-42EE-A16A-79B34DE97E9B',
'A14D0408-690C-420C-9D41-7A2FA515C371',
'F21C9D4A-CB80-4705-AA7C-81A8BD17DB7D',
'C9A1DD95-79BF-4543-924B-94362381E705',
'E25F399F-A147-4663-B74A-98A46A39F121',
'269EE291-F6E7-4AEA-B4C7-A7D02B7C59DE',
'3AB86F48-7A73-46B1-8AA4-D1B55DE1EE8A',
'F34C2B8B-DDBE-4C04-BD01-F08B0F479AE8',
'A974B39B-EC88-4917-A1D5-F8FBBFBB1F7A',
'C72E2DD8-EC76-4663-B400-FB1B7F8F8C2B',
'7894EACD-DA8C-4659-9891-FB4D62EB9FF5') ";
break;
case "2": //钢筋实验
sqlwhere += @" AND m.id IN ('AE9F8E75-773F-4DEE-A3A8-3E871AC8598E',
'A98902C1-BB72-4E79-9C74-4746D1709D3B',
'68F05EBC-5D34-49C5-9B57-49B688DF24F7',
'9B8BD64A-2D9C-4B67-B9DC-53FCF67FD361',
'46622671-E829-412C-99E6-587525ED968F',
'C7B2620E-7F4C-4586-AEBB-59855B54E522',
'3B46BC3A-92DF-4AFC-AA85-AE74FC00F96D',
'377A20DA-7E27-4CD3-B9E1-B3C7993CF6EA',
'A12AD84C-A7D3-4B42-A9DF-6E80A3E3A0CF',
'0A2F0365-D561-4504-B602-98FCC5C3AB94',
'4C817CF9-E7F3-422D-975F-C8175E738382') ";
break;
case "3": //混凝土原材
sqlwhere += " AND m.id IN (SELECT ID FROM dbo.sys_module WHERE CatlogCode LIKE '0001%') and d.status>0 and d.bgrq between '" + StartDate + "' and '" + DateTime.Parse(EndDate).ToString("yyyy-MM-dd") + "' ";
break;
case "4": //其他
sqlwhere += @" AND m.id not in (SELECT ID FROM dbo.sys_module WHERE CatlogCode LIKE '0001%' or ID in (
'AE9F8E75-773F-4DEE-A3A8-3E871AC8598E',
'A98902C1-BB72-4E79-9C74-4746D1709D3B',
'68F05EBC-5D34-49C5-9B57-49B688DF24F7',
'9B8BD64A-2D9C-4B67-B9DC-53FCF67FD361',
'46622671-E829-412C-99E6-587525ED968F',
'C7B2620E-7F4C-4586-AEBB-59855B54E522',
'3B46BC3A-92DF-4AFC-AA85-AE74FC00F96D',
'377A20DA-7E27-4CD3-B9E1-B3C7993CF6EA',
'A12AD84C-A7D3-4B42-A9DF-6E80A3E3A0CF',
'0A2F0365-D561-4504-B602-98FCC5C3AB94',
'4C817CF9-E7F3-422D-975F-C8175E738382',
'A46D053A-0776-467F-9136-0AA45D2021D2',
'90973417-6C68-4E90-849B-0B1AEB36A234',
'E1E2A6EA-C092-4C8D-920D-170BE5128BC4',
'D06E5471-EE69-49E4-9AF4-1BE8AB2E0310',
'8A2B5231-8602-4519-8919-1FF649ED2E41',
'FA0D1C8C-DE69-4C16-A301-3A861C6B11CD',
'D1C56FBD-2EDC-40FF-956F-4DA7178F2DD3',
'4500603A-5BE7-4574-BBBA-77B4626A3EA1',
'05D0D71B-DEF3-42EE-A16A-79B34DE97E9B',
'A14D0408-690C-420C-9D41-7A2FA515C371',
'F21C9D4A-CB80-4705-AA7C-81A8BD17DB7D',
'C9A1DD95-79BF-4543-924B-94362381E705',
'E25F399F-A147-4663-B74A-98A46A39F121',
'269EE291-F6E7-4AEA-B4C7-A7D02B7C59DE',
'3AB86F48-7A73-46B1-8AA4-D1B55DE1EE8A',
'F34C2B8B-DDBE-4C04-BD01-F08B0F479AE8',
'A974B39B-EC88-4917-A1D5-F8FBBFBB1F7A',
'C72E2DD8-EC76-4663-B400-FB1B7F8F8C2B',
'7894EACD-DA8C-4659-9891-FB4D62EB9FF5')
) ";
break;
}
}
#endregion
if (!String.IsNullOrEmpty(Request.Params["bgmc"]))
{
sqlwhere += " and m.name LIKE '%" + Request.Params["bgmc"].Trim() + "%'";
}
if (!String.IsNullOrEmpty(Request.Params["wtbh"]))
{
sqlwhere += " and WTBH LIKE '%" + Request.Params["wtbh"].Trim() + "%'";
}
if (!String.IsNullOrEmpty(Request.Params["bgbh"]))
{
sqlwhere += " and BGBH LIKE '%" + Request.Params["bgbh"].Trim() + "%'";
}
if (!"NUM".RequestStr().IsNullOrEmpty())
{
sqlwhere += " and testroomcode in ('" + "NUM".RequestStr() + "') ";
}
else if (!String.IsNullOrEmpty(SelectedTestRoomCodes))
{
sqlwhere += " and testroomcode in (" + SelectedTestRoomCodes + ") ";
}
else
{
sqlwhere += "and 1=0";
}
BLL_LoginLog BLL = new BLL_LoginLog();
// DataTable dt = BLL.GetDataTablePager(name, fileds, sqlwhere, key, "BGRQ", OrderType, PageIndex, PageSize, out pageCount, out records);
#region 使用脚本分页
string Sql = @"
DECLARE @Page int
DECLARE @PageSize int
SET @Page = {1}
SET @PageSize = {2}
SET NOCOUNT ON
DECLARE @TempTable TABLE (IndexId int identity, _keyID varchar(50))
INSERT INTO @TempTable
(
_keyID
)
select d.ID from sys_document d left outer join sys_module m on d.ModuleId = m.id
where 1=1 {0} Order By BGRQ DESC
SELECT
sys_document.ID,
BGBH ,
WTBH,CONVERT(NVARCHAR(10),BGRQ,120) BGRQ ,CompanyCode,TestRoomCode,SegmentCode,
sys_tree.description SegmentName, t2.description CompanyName,t1.description TestRoomName
,sys_module.DeviceType ,
ModuleId,
sys_module.name as MName
FROM sys_document
left outer join sys_module on sys_document.ModuleId = sys_module.id
left outer join sys_tree on sys_document.SegmentCode = sys_tree.nodecode
left outer join sys_tree t1 on sys_document.TestRoomCode = t1.nodecode
left outer join sys_tree t2 on sys_document.CompanyCode = t2.nodecode
INNER JOIN @TempTable t ON sys_document.ID = t._keyID
WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)
Order By BGRQ DESC
DECLARE @C int
select @C= count(d.ID) from sys_document d left outer join sys_module m on d.ModuleId = m.id where 1=1 {0}
select @C
";
Sql = string.Format(Sql, sqlwhere, PageIndex, PageSize);
//DataSet DS = BLL.GetDataSet(Sql);
DataSet DSs = new DataSet();
using (System.Data.SqlClient.SqlConnection Conn = BLL.Connection as System.Data.SqlClient.SqlConnection)
{
Conn.Open();
using (System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(Sql, Conn))
{
using (System.Data.SqlClient.SqlDataAdapter Adp = new System.Data.SqlClient.SqlDataAdapter(Cmd))
{
Adp.Fill(DSs);
}
}
Conn.Close();
}
decimal Tempc = Math.Round(decimal.Parse(DSs.Tables[1].Rows[0][0].ToString()) / decimal.Parse(PageSize.ToString()), 2);
Tempc = Math.Ceiling(Tempc);
records = DSs.Tables[1].Rows[0][0].ToString().Toint();
pageCount = Tempc.ToString().Toint();
#endregion
#region 过滤 !未使用
//DataTable Module = BLL.GetDataTable("select Id,Name,DeviceType from sys_module");
//DataTable Temp;
//foreach (DataRow Dr in dt.Rows)
//{
// Module.DefaultView.RowFilter = " ID = '" + Dr["ModuleId"].ToString() + "' ";
// Temp = Module.DefaultView.ToTable();
// Dr["MName"] = Temp.Rows[0]["Name"].ToString();
// Dr["DeviceType"] = Temp.Rows[0]["DeviceType"].ToString();
// Temp.Clear();
// Temp = null;
//}
#endregion
if (DSs.Tables[0] != null)
{
return(JsonConvert.SerializeObject(DSs.Tables[0]));
}
else
{
return("");
}
}
private String report_Search()
{//testroomid modelid 资料报告编号、报告日期、检测结果、打开报告
string StartDate = "StartDate".RequestStr();
string EndDate = "EndDate".RequestStr();
string testroomid = "testroomid".RequestStr();
string modelid = "modelid".RequestStr();
string fileds = @"ID,
BGBH ,
WTBH,CONVERT(NVARCHAR(10),BGRQ,120) BGRQ ,CompanyCode,TestRoomCode,SegmentCode,
'' SegmentName, '' CompanyName,'' TestRoomName, '' MName,'' DeviceType ,
ModuleId
";
string sqlwhere = " AND moduleid ='" + modelid + "' and status>0 and BGRQ>='" + StartDate + "' AND BGRQ<'" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "'";
if (!String.IsNullOrEmpty(testroomid))
{
sqlwhere += " and testroomcode in ('" + testroomid + "') ";
}
else
{
sqlwhere += "and 1=0";
}
BLL_LoginLog BLL = new BLL_LoginLog();
// DataTable dt = BLL.GetDataTablePager(name, fileds, sqlwhere, key, "BGRQ", OrderType, PageIndex, PageSize, out pageCount, out records);
#region 使用脚本分页
string Sql = @"
DECLARE @Page int
DECLARE @PageSize int
SET @Page = {1}
SET @PageSize = {2}
SET NOCOUNT ON
DECLARE @TempTable TABLE (IndexId int identity, _keyID varchar(50))
INSERT INTO @TempTable
(
_keyID
)
select d.ID from sys_document d left outer join sys_module m on d.ModuleId = m.id
where 1=1 {0} Order By BGRQ DESC
SELECT
sys_document.ID,
BGBH ,
WTBH,CONVERT(NVARCHAR(10),BGRQ,120) BGRQ ,CompanyCode,TestRoomCode,SegmentCode,
sys_tree.description SegmentName, t2.description CompanyName,t1.description TestRoomName
,sys_module.DeviceType ,
ModuleId,
sys_module.name as MName
FROM sys_document
left outer join sys_module on sys_document.ModuleId = sys_module.id
left outer join sys_tree on sys_document.SegmentCode = sys_tree.nodecode
left outer join sys_tree t1 on sys_document.TestRoomCode = t1.nodecode
left outer join sys_tree t2 on sys_document.CompanyCode = t2.nodecode
INNER JOIN @TempTable t ON sys_document.ID = t._keyID
WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)
Order By BGRQ DESC
DECLARE @C int
select @C= count(d.ID) from sys_document d left outer join sys_module m on d.ModuleId = m.id where 1=1 {0}
select @C
";
Sql = string.Format(Sql, sqlwhere, PageIndex, PageSize);
//DataSet DS = BLL.GetDataSet(Sql);
DataSet DSs = new DataSet();
using (System.Data.SqlClient.SqlConnection Conn = BLL.Connection as System.Data.SqlClient.SqlConnection)
{
Conn.Open();
using (System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(Sql, Conn))
{
using (System.Data.SqlClient.SqlDataAdapter Adp = new System.Data.SqlClient.SqlDataAdapter(Cmd))
{
Adp.Fill(DSs);
}
}
Conn.Close();
}
decimal Tempc = Math.Round(decimal.Parse(DSs.Tables[1].Rows[0][0].ToString()) / decimal.Parse(PageSize.ToString()), 2);
Tempc = Math.Ceiling(Tempc);
records = DSs.Tables[1].Rows[0][0].ToString().Toint();
pageCount = Tempc.ToString().Toint();
#endregion
if (DSs.Tables[0] != null)
{
return(JsonConvert.SerializeObject(DSs.Tables[0]));
}
else
{
return("");
}
}
