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(""); } }