예제 #1
0
    /// <summary>
    /// 获取原材料半成品
    /// </summary>
    public static DataTable Items(string ItemType)
    {
        string       Sql = "SELECT ItemID,ItemName FROM sys_TJ_Item where Status =1 AND ItemType=" + ItemType;
        BLL_Document BLL = new BLL_Document();

        return(BLL.GetDataSet(Sql).Tables[0]);
    }
예제 #2
0
    /// <summary>
    /// 获取厂家
    /// </summary>
    public static DataTable Factory(string ItemID)
    {
        string       Sql = @" select [sys_TJ_Factory].FactoryID, [sys_TJ_Factory].FactoryName from [dbo].[sys_TJ_MainData] 
                      join [dbo].[sys_TJ_Item_Module] on [dbo].[sys_TJ_MainData].ModuleID = [dbo].[sys_TJ_Item_Module].ModuleID
                      join [dbo].[sys_TJ_Factory] on [dbo].[sys_TJ_Factory].FactoryID = [dbo].[sys_TJ_MainData].FactoryID
                      where [sys_TJ_Item_Module].ItemID = '" + ItemID + "'  group by  [sys_TJ_Factory].FactoryID, [sys_TJ_Factory].FactoryName ";
        BLL_Document BLL = new BLL_Document();

        return(BLL.GetDataSet(Sql).Tables[0]);
    }
예제 #3
0
    /// <summary>
    /// 获取强度等级
    /// </summary>
    /// <returns></returns>
    public static DataTable QDDJ()
    {
        string       Sql = @"select M.QDDJ from 
                    sys_TJ_MainData M
                    join sys_TJ_Item I on I.ItemID = M.ItemID AND  I.Status=1 AND I.ItemType =2
                    ANd I.ItemName in('混凝土抗压(同条件)','混凝土抗压')
                    group by QDDJ";
        BLL_Document BLL = new BLL_Document();

        return(BLL.GetDataSet(Sql).Tables[0]);
    }
예제 #4
0
    /// <summary>
    /// 工程部位
    /// </summary>
    public static DataTable Position(string ItemID)
    {
        string Sql = @"  select SGBW from [dbo].[sys_TJ_MainData] 
                          join [dbo].[sys_TJ_Item_Module] on [dbo].[sys_TJ_MainData].ModuleID = [dbo].[sys_TJ_Item_Module].ModuleID
                          join [dbo].[sys_TJ_Item] on [dbo].[sys_TJ_Item].ItemID = [dbo].[sys_TJ_Item_Module].ItemID 
                          AND [dbo].[sys_TJ_Item_Module].ItemID ='{0}' group by SGBW";

        if (ItemID.IsNullOrEmpty())
        {
            Sql = @"  select SGBW from [dbo].[sys_TJ_MainData] group by SGBW";
        }
        BLL_Document BLL = new BLL_Document();

        return(BLL.GetDataSet(string.Format(Sql, ItemID)).Tables[0]);
    }
예제 #5
0
    /// <summary>
    /// 型号
    /// </summary>
    public static DataTable Model(string ItemID)
    {
        string         Sql   = @"  select {0} as XH from [dbo].[sys_TJ_MainData] 
                          join [dbo].[sys_TJ_Item_Module] on [dbo].[sys_TJ_MainData].ModuleID = [dbo].[sys_TJ_Item_Module].ModuleID
                          join [dbo].[sys_TJ_Item] on [dbo].[sys_TJ_Item].ItemID = [dbo].[sys_TJ_Item_Module].ItemID 
                          AND [dbo].[sys_TJ_Item_Module].ItemID ='{1}' AND {0} is not null group by {0}";
        BLL_Document   BLL   = new BLL_Document();
        ItemCollection Attrs = Attribute(ItemID);

        if (!Attrs.ContainsKey("型号"))
        {
            return(null);
        }
        return(BLL.GetDataSet(string.Format(Sql, Attrs["型号"].BindField, ItemID)).Tables[0]);
    }
예제 #6
0
    public void Sheet()
    {
        string Sql = "select id,name from sys_sheet where ID in (" + "SheetID".RequestStr() + ")";


        BLL_Document BLL = new BLL_Document();
        DataSet      Ds  = BLL.GetDataSet(Sql);



        string Json = JsonConvert.SerializeObject(Ds.Tables[0]);


        Response.Write(Json);
        Response.End();
    }
예제 #7
0
    public void List()
    {
        int Now = (int)DateTime.Now.DayOfWeek;

        string RType     = "RType".RequestStr();
        string StartDate = "";
        string EndDate   = "";

        string UStartDate = "";
        string UEndDate   = "";
        string unit       = "周";

        switch (RType)
        {
        case "W":
            StartDate = DateTime.Now.AddDays(-7).ToString("yyyy-MM-dd");
            EndDate   = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");

            UStartDate = DateTime.Now.AddDays(-14).ToString("yyyy-MM-dd");
            UEndDate   = StartDate.ToDateTime().AddDays(-1).ToString("yyyy-MM-dd");

            unit = "周";
            break;

        case "M":
            StartDate = DateTime.Now.AddMonths(-1).ToString("yyyy-MM-" + 26);
            EndDate   = DateTime.Now.ToString("yyyy-MM-" + 25);

            UStartDate = DateTime.Now.AddMonths(-2).ToString("yyyy-MM-" + 26);
            UEndDate   = DateTime.Now.AddMonths(-1).ToString("yyyy-MM-" + 25);
            unit       = "月";
            break;
        }

        int RecordCount = 0;

        #region SQL

        string SqlRoom = @"
                        select 
                        tsg.DESCRIPTION+' '+t.DESCRIPTION as 单位,

                        t.NodeCode
                        ,''as 上{0}资料数量	
                        ,''as 本{0}资料数量	
                        ,''as 截止本{0}累计数量	
                        ,''as 本{0}资料修改次数	
                        ,''as 上{0}登录次数	
                        ,''as 本{0}系统登录次数	
                        ,''as 截止累计登录次数	
                        ,''as 本{0}不合格报告数量	
                        ,''as 处理完成数量	
                        ,''as 未处理不合格报告数量
                        ,''as 在岗人数	
                        ,''as 离岗人数
                        ,''as 设备数量

                        from sys_Tree t
                       
                        left outer join sys_Tree tsg on tsg.nodecode = left(t.nodecode,8)

                        where len(t.NodeCode)=12
                        order by t.OrderID  asc 
                        ";

        string Sql = @" 
                        -- 1 上周资料
                        select left(testroomcode,12) as testroomcode,count(1) as ct from sys_document a 
                        join sys_module b on a.moduleid=b.id
                        where status>0 and b.moduletype=1  and  a.bgrq between 
                        '{0}' and '{1}' group by a.testroomcode

                        -- 2 本周资料
                        select left(testroomcode,12) as testroomcode,count(1) as ct from sys_document a 
                        join sys_module b on a.moduleid=b.id
                        where status>0 and b.moduletype=1  and  a.bgrq between 
                        '{2}' and '{3}' group by a.testroomcode
                        
                        --3 本周修改资料次数
                        SELECT left(testroomcode,12) as testroomcode,count(1) as ct
                        FROM sys_request_change
                        where RequestTime between  '{2}' and '{3}' 
                        group by TestRoomCode
    
                        -- 4 上周周系统登录次数
                        SELECT left(testroomcode,12) as testroomcode, COUNT(1) as ct  FROM dbo.sys_loginlog WHERE  FirstAccessTime between '{0}' and '{1}' AND len(testroomcode)>12  group by testroomcode

                        -- 5 本周系统登录次数
                        SELECT left(testroomcode,12) as testroomcode, COUNT(1) as ct  FROM dbo.sys_loginlog WHERE  FirstAccessTime between '{2}' and '{3}' AND len(testroomcode)>12  group by testroomcode


                        -- 6 本周不合格资料
                        SELECT left(testroomcode,12) as testroomcode,count(1) as ct,b.StatisticsCatlog  FROM dbo.sys_invalid_document a
                        join sys_module b on a.moduleid=b.id
                        WHERE AdditionalQualified=0  and status>0   and 
                        bgrq between '{2}' and '{3}' AND  F_InvalidItem NOT LIKE '%#%'
                        group by a.testroomcode,b.StatisticsCatlog

                        
                        -- 7 本周已经处理资料
                        SELECT left(testroomcode,12) as testroomcode,count(1) as ct,b.StatisticsCatlog  FROM dbo.sys_invalid_document a
                        join sys_module b on a.moduleid=b.id
                        WHERE AdditionalQualified=0  and status>0   and (DealResult <>'' OR DealResult IS NOT NULL) and
                        bgrq between '{2}' and '{3}' AND  F_InvalidItem NOT LIKE '%#%'
                        group by a.testroomcode,b.StatisticsCatlog

                        -- 8 本周未处理资料
                        SELECT left(testroomcode,12) as testroomcode,count(1) as ct,b.StatisticsCatlog  FROM dbo.sys_invalid_document a
                        join sys_module b on a.moduleid=b.id
                        WHERE AdditionalQualified=0  and status>0   and (DealResult='' OR DealResult IS NULL) and
                        bgrq between '{2}' and '{3}' AND  F_InvalidItem NOT LIKE '%#%'
                        group by a.testroomcode,b.StatisticsCatlog


                        -- 9 截止本月累计数  资料
                        select left(testroomcode,12) as testroomcode,count(1) as ct from sys_document a 
                        join sys_module b on a.moduleid=b.id
                        where status>0 and b.moduletype=1  
                         group by a.testroomcode

                        --10 截止累计登录次
                        SELECT left(testroomcode,12) as testroomcode, COUNT(1) as ct  FROM dbo.sys_loginlog WHERE   len(testroomcode)>12  group by testroomcode



                        -- 11 人员
                        SELECT 
                        left(testroomcode,12) as testroomcode,
                        sum(case  when Status>0  then 1 else 0 end) as ct, --在岗人数
                        sum(case  when Status=0  then 1 else 0 end) as lct --离岗人数
                        FROM dbo.sys_document WHERE ModuleID='08899BA2-CC88-403E-9182-3EF73F5FB0CE'
                        group by testroomcode
                        
                        --12 设备
                        SELECT left(testroomcode,12) as testroomcode,COUNT(1) as ct FROM sys_document where ModuleID in('A0C51954-302D-43C6-931E-0BAE2B8B10DB') and Status>0 group by TestRoomCode 
                        ";


        #endregion


        SqlRoom = string.Format(SqlRoom, unit);

        Sql = string.Format(Sql, UStartDate, UEndDate, StartDate, EndDate);

        Sql = SqlRoom + Sql;
        BLL_Document BLL = new BLL_Document();
        DataSet      Ds  = BLL.GetDataSet(Sql);

        DataTable Rooms = Ds.Tables[0];

        foreach (DataRow Dr in Rooms.Rows)
        {
            Dr["上" + unit + "资料数量"]    = Ds.Tables[1].Compute("sum(ct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();
            Dr["本" + unit + "资料数量"]    = Ds.Tables[2].Compute("sum(ct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();
            Dr["截止本" + unit + "累计数量"]  = Ds.Tables[9].Compute("sum(ct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();
            Dr["本" + unit + "资料修改次数"]  = Ds.Tables[3].Compute("sum(ct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();
            Dr["上" + unit + "登录次数"]    = Ds.Tables[4].Compute("sum(ct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();
            Dr["本" + unit + "系统登录次数"]  = Ds.Tables[5].Compute("sum(ct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();
            Dr["截止累计登录次数"]             = Ds.Tables[10].Compute("sum(ct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();
            Dr["本" + unit + "不合格报告数量"] = Ds.Tables[6].Compute("sum(ct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();
            Dr["处理完成数量"]     = Ds.Tables[7].Compute("sum(ct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();
            Dr["未处理不合格报告数量"] = Ds.Tables[8].Compute("sum(ct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();

            Dr["在岗人数"] = Ds.Tables[11].Compute("sum(ct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();
            Dr["离岗人数"] = Ds.Tables[11].Compute("sum(lct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();
            Dr["设备数量"] = Ds.Tables[12].Compute("sum(ct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();
        }
        Rooms.Columns.Remove("NodeCode");

        DataRow All = Rooms.NewRow();
        All["单位"] = "合计";
        All["上" + unit + "资料数量"]    = Ds.Tables[1].Compute("sum(ct)", "").ToString();
        All["本" + unit + "资料数量"]    = Ds.Tables[2].Compute("sum(ct)", "").ToString();
        All["截止本" + unit + "累计数量"]  = Ds.Tables[9].Compute("sum(ct)", " ").ToString();
        All["本" + unit + "资料修改次数"]  = Ds.Tables[3].Compute("sum(ct)", "").ToString();
        All["上" + unit + "登录次数"]    = Ds.Tables[4].Compute("sum(ct)", "").ToString();
        All["本" + unit + "系统登录次数"]  = Ds.Tables[5].Compute("sum(ct)", "").ToString();
        All["截止累计登录次数"]             = Ds.Tables[10].Compute("sum(ct)", " ").ToString();
        All["本" + unit + "不合格报告数量"] = Ds.Tables[6].Compute("sum(ct)", "").ToString();
        All["处理完成数量"]     = Ds.Tables[7].Compute("sum(ct)", "").ToString();
        All["未处理不合格报告数量"] = Ds.Tables[8].Compute("sum(ct)", "").ToString();

        All["在岗人数"] = Ds.Tables[11].Compute("sum(ct)", " ").ToString();
        All["离岗人数"] = Ds.Tables[11].Compute("sum(lct)", " ").ToString();
        All["设备数量"] = Ds.Tables[12].Compute("sum(ct)", " ").ToString();


        Rooms.Rows.Add(All);


        string Json = JsonConvert.SerializeObject(Rooms);
        Json = "{\"rows\":" + Json + ",\"total\":" + RecordCount + "}";

        Response.Write(Json);
        Response.End();
    }
예제 #8
0
    /// <summary>
    /// 上传资料排名
    /// </summary>
    /// <returns></returns>
    public DataTable SCZLPM()
    {
        int Now = (int)DateTime.Now.DayOfWeek;

        string RType     = "RType".RequestStr();
        string StartDate = "StartDate".RequestStr();
        string EndDate   = "EndDate".RequestStr().ToDateTime().AddDays(1).ToString("yyyy-MM-dd");



        #region SQL



        string Sql = @" 


                        -- 0 施工前三
                        select top 3
						t1.description+' '+t2.description+' '+t.description as '单位'
						
						,count(1) as '数量' 
						from sys_document a 
                        join sys_module b on a.moduleid=b.id
						left outer join sys_tree t on t.nodecode = a.testroomcode
						left outer join sys_tree t1 on t1.nodecode = left(t.nodecode,12)
						left outer join sys_tree t2 on t2.nodecode = left(t.nodecode,8)
                        where status>0 and b.moduletype=1  and  a.bgrq between 
                        '{1}' and '{0}' AND t1.DepType='@unit_施工单位'
						group by a.testroomcode ,t.description,t1.description,t2.description
						order by 数量 desc

                        -- 1 施工后三
                        select top 3
						t1.description+' '+t2.description+' '+t.description as '单位'
						
						,count(1) as '数量' 
						from sys_document a 
                        join sys_module b on a.moduleid=b.id
						left outer join sys_tree t on t.nodecode = a.testroomcode
						left outer join sys_tree t1 on t1.nodecode = left(t.nodecode,12)
						left outer join sys_tree t2 on t2.nodecode = left(t.nodecode,8)
                        where status>0 and b.moduletype=1  and  a.bgrq between 
                        '{1}' and '{0}' AND t1.DepType='@unit_施工单位'
						group by a.testroomcode ,t.description,t1.description,t2.description
						order by 数量 asc
                        
                        -- 2 监理前三
                        select top 3
						t1.description+' '+t2.description+' '+t.description as '单位'
						
						,count(1) as '数量' 
						from sys_document a 
                        join sys_module b on a.moduleid=b.id
						left outer join sys_tree t on t.nodecode = a.testroomcode
						left outer join sys_tree t1 on t1.nodecode = left(t.nodecode,12)
						left outer join sys_tree t2 on t2.nodecode = left(t.nodecode,8)
                        where status>0 and b.moduletype=1  and  a.bgrq between 
                        '{1}' and '{0}' AND t1.DepType='@unit_监理单位'
						group by a.testroomcode ,t.description,t1.description,t2.description
						order by 数量 desc

                        -- 3系统登录次数
                        SELECT 
						top 3
						t1.description+' '+t2.description+' '+t.description as '单位'
						,
						COUNT(1) as '数量' 
						FROM dbo.sys_loginlog a
						left outer join sys_tree t on t.nodecode = a.testroomcode
						left outer join sys_tree t1 on t1.nodecode = left(t.nodecode,12)
						left outer join sys_tree t2 on t2.nodecode = left(t.nodecode,8)
						WHERE  FirstAccessTime between '{1}' and '{0}' AND len(testroomcode)>12  
						group by testroomcode ,t.description,t1.description,t2.description
						order by 数量 desc

                        -- 4 系统登录次数
                        SELECT 
						top 3
						t1.description+' '+t2.description+' '+t.description as '单位'
						,
						COUNT(1) as '数量' 
						FROM dbo.sys_loginlog a
						left outer join sys_tree t on t.nodecode = a.testroomcode
						left outer join sys_tree t1 on t1.nodecode = left(t.nodecode,12)
						left outer join sys_tree t2 on t2.nodecode = left(t.nodecode,8)
						WHERE  FirstAccessTime between '{1}' and '{0}' AND len(testroomcode)>12  
						group by testroomcode ,t.description,t1.description,t2.description
						order by 数量 asc
                  


                        ";


        #endregion



        Sql = string.Format(Sql, EndDate, StartDate);


        BLL_Document BLL = new BLL_Document();
        DataSet      Ds  = BLL.GetDataSet(Sql);



        DataTable Out = new DataTable();
        Out.Columns.Add(new DataColumn("0"));
        Out.Columns.Add(new DataColumn("1"));
        Out.Columns.Add(new DataColumn("2"));
        Out.Columns.Add(new DataColumn("3"));
        Out.Columns.Add(new DataColumn("4"));
        Out.Columns.Add(new DataColumn("5"));
        Out.Columns.Add(new DataColumn("6"));
        Out.Columns.Add(new DataColumn("7"));
        Out.Columns.Add(new DataColumn("8"));
        Out.Columns.Add(new DataColumn("9"));


        DataRow Dr = Out.NewRow();
        Out.Rows.Add(Dr);
        Dr = Out.NewRow();
        Out.Rows.Add(Dr);
        Dr = Out.NewRow();
        Out.Rows.Add(Dr);
        Dr = Out.NewRow();
        Out.Rows.Add(Dr);

        int ct = 0;
        for (int i = 0; i < Ds.Tables.Count; i++)
        {
            int rt = 0;
            foreach (DataRow tDr in Ds.Tables[i].Rows)
            {
                Out.Rows[rt][ct + 0] = tDr[0].ToString();
                Out.Rows[rt][ct + 1] = tDr[1].ToString();

                Out.Rows[3][ct + 0] = "合计";
                Out.Rows[3][ct + 1] = (Out.Rows[3][ct + 1].ToString().Toint() + tDr[1].ToString().Toint()).ToString();
                rt++;
            }
            ct = ct + 2;
        }

        Out.Columns[0].ColumnName = "施工单位周期前三名";
        Out.Columns[2].ColumnName = "施工单位周期后三名";
        Out.Columns[4].ColumnName = "监理单位周期前三名";
        Out.Columns[6].ColumnName = "登陆系统次数周期前三名";
        Out.Columns[8].ColumnName = "登陆系统次数周期后三名";

        Out.Columns[1].ColumnName = "施工单位周期前三名数量";
        Out.Columns[3].ColumnName = "施工单位周期后三名数量";
        Out.Columns[5].ColumnName = "监理单位周期前三名数量";
        Out.Columns[7].ColumnName = "登陆系统次数周期前三名数量";
        Out.Columns[9].ColumnName = "登陆系统次数周期后三名数量";
        return(Out);
    }
예제 #9
0
    public DataTable WORKREPORT()
    {
        int Now = (int)DateTime.Now.DayOfWeek;

        string RType     = "RType".RequestStr();
        string StartDate = "";
        string EndDate   = "";

        string UStartDate = "";
        string UEndDate   = "";
        string unit       = "周";

        switch (RType)
        {
        case "W":
            StartDate = DateTime.Now.AddDays(-7).ToString("yyyy-MM-dd");
            EndDate   = DateTime.Now.ToString("yyyy-MM-dd");

            UStartDate = DateTime.Now.AddDays(-14).ToString("yyyy-MM-dd");
            UEndDate   = StartDate.ToDateTime().ToString("yyyy-MM-dd");

            unit = "周";
            break;

        case "M":
            StartDate = DateTime.Now.AddMonths(-1).ToString("yyyy-MM-" + 25);
            EndDate   = DateTime.Now.ToString("yyyy-MM-" + 25);

            UStartDate = DateTime.Now.AddMonths(-2).ToString("yyyy-MM-" + 25);
            UEndDate   = DateTime.Now.AddMonths(-1).ToString("yyyy-MM-" + 25);
            unit       = "月";
            break;
        }


        #region SQL

        string SqlRoom = @"
                        select 
                        tsg.DESCRIPTION+' '+t.DESCRIPTION as 单位,

                        t.NodeCode
                        ,''as 上{0}资料数量	
                        ,''as 本{0}资料数量	
                        ,''as 截止本{0}累计数量	
                        ,''as 本{0}资料修改次数	
                        ,''as 上{0}登录次数	
                        ,''as 本{0}系统登录次数	
                        ,''as 截止累计登录次数	
                        ,''as 本{0}不合格报告数量	
                        ,''as 处理完成数量	
                        ,''as 未处理不合格报告数量

                        from sys_Tree t
                       
                        left outer join sys_Tree tsg on tsg.nodecode = left(t.nodecode,8)

                        where len(t.NodeCode)=12
                        order by t.OrderID  asc 
                        ";

        string Sql = @" 
                        -- 1 上周资料
                        select left(testroomcode,12) as testroomcode,count(1) as ct from sys_document a 
                        join sys_module b on a.moduleid=b.id
                        where status>0 and b.moduletype=1  and  a.bgrq between 
                        '{0}' and '{1}' group by a.testroomcode

                        -- 2 本周资料
                        select left(testroomcode,12) as testroomcode,count(1) as ct from sys_document a 
                        join sys_module b on a.moduleid=b.id
                        where status>0 and b.moduletype=1  and  a.bgrq between 
                        '{2}' and '{3}' group by a.testroomcode
                        
                        --3 本周修改资料次数
                        SELECT left(testroomcode,12) as testroomcode,count(1) as ct
                        FROM sys_request_change
                        where RequestTime between  '{2}' and '{3}' 
                        group by TestRoomCode
    
                        -- 4 上周周系统登录次数
                        SELECT left(testroomcode,12) as testroomcode, COUNT(1) as ct  FROM dbo.sys_loginlog WHERE  FirstAccessTime between '{0}' and '{1}' AND len(testroomcode)>12  group by testroomcode

                        -- 5 本周系统登录次数
                        SELECT left(testroomcode,12) as testroomcode, COUNT(1) as ct  FROM dbo.sys_loginlog WHERE  FirstAccessTime between '{2}' and '{3}' AND len(testroomcode)>12  group by testroomcode


                        -- 6 本周不合格资料
                        SELECT left(testroomcode,12) as testroomcode,count(1) as ct,b.StatisticsCatlog  FROM dbo.sys_invalid_document a
                        join sys_module b on a.moduleid=b.id
                        WHERE AdditionalQualified=0  and status>0   and 
                        bgrq between '{2}' and '{3}' AND  F_InvalidItem NOT LIKE '%#%'
                        group by a.testroomcode,b.StatisticsCatlog

                        
                        -- 7 本周已经处理资料
                        SELECT left(testroomcode,12) as testroomcode,count(1) as ct,b.StatisticsCatlog  FROM dbo.sys_invalid_document a
                        join sys_module b on a.moduleid=b.id
                        WHERE AdditionalQualified=0  and status>0   and (DealResult <>'' OR DealResult IS NOT NULL) and
                        bgrq between '{2}' and '{3}' AND  F_InvalidItem NOT LIKE '%#%'
                        group by a.testroomcode,b.StatisticsCatlog

                        -- 8 本周未处理资料
                        SELECT left(testroomcode,12) as testroomcode,count(1) as ct,b.StatisticsCatlog  FROM dbo.sys_invalid_document a
                        join sys_module b on a.moduleid=b.id
                        WHERE AdditionalQualified=0  and status>0   and (DealResult='' OR DealResult IS NULL) and
                        bgrq between '{2}' and '{3}' AND  F_InvalidItem NOT LIKE '%#%'
                        group by a.testroomcode,b.StatisticsCatlog


                        -- 9 截止本月累计数  资料
                        select left(testroomcode,12) as testroomcode,count(1) as ct from sys_document a 
                        join sys_module b on a.moduleid=b.id
                        where status>0 and b.moduletype=1  
                         group by a.testroomcode

                        --10 截止累计登录次
                        SELECT left(testroomcode,12) as testroomcode, COUNT(1) as ct  FROM dbo.sys_loginlog WHERE   len(testroomcode)>12  group by testroomcode
                        ";


        #endregion


        SqlRoom = string.Format(SqlRoom, unit);


        Sql = string.Format(Sql, UStartDate, UEndDate, StartDate, EndDate);

        Sql = SqlRoom + Sql;
        BLL_Document BLL = new BLL_Document();
        DataSet      Ds  = BLL.GetDataSet(Sql);

        DataTable Rooms = Ds.Tables[0];

        foreach (DataRow Dr in Rooms.Rows)
        {
            Dr["上" + unit + "资料数量"]    = Ds.Tables[1].Compute("sum(ct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();
            Dr["本" + unit + "资料数量"]    = Ds.Tables[2].Compute("sum(ct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();
            Dr["截止本" + unit + "累计数量"]  = Ds.Tables[9].Compute("sum(ct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();
            Dr["本" + unit + "资料修改次数"]  = Ds.Tables[3].Compute("sum(ct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();
            Dr["上" + unit + "登录次数"]    = Ds.Tables[4].Compute("sum(ct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();
            Dr["本" + unit + "系统登录次数"]  = Ds.Tables[5].Compute("sum(ct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();
            Dr["截止累计登录次数"]             = Ds.Tables[10].Compute("sum(ct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();
            Dr["本" + unit + "不合格报告数量"] = Ds.Tables[6].Compute("sum(ct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();
            Dr["处理完成数量"]     = Ds.Tables[7].Compute("sum(ct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();
            Dr["未处理不合格报告数量"] = Ds.Tables[8].Compute("sum(ct)", " testroomcode ='" + Dr["NodeCode"].ToString() + "'").ToString();
        }
        Rooms.Columns.Remove("NodeCode");

        DataRow All = Rooms.NewRow();
        All["单位"] = "合计";
        All["上" + unit + "资料数量"]    = Ds.Tables[1].Compute("sum(ct)", "").ToString();
        All["本" + unit + "资料数量"]    = Ds.Tables[2].Compute("sum(ct)", "").ToString();
        All["截止本" + unit + "累计数量"]  = Ds.Tables[9].Compute("sum(ct)", " ").ToString();
        All["本" + unit + "资料修改次数"]  = Ds.Tables[3].Compute("sum(ct)", "").ToString();
        All["上" + unit + "登录次数"]    = Ds.Tables[4].Compute("sum(ct)", "").ToString();
        All["本" + unit + "系统登录次数"]  = Ds.Tables[5].Compute("sum(ct)", "").ToString();
        All["截止累计登录次数"]             = Ds.Tables[10].Compute("sum(ct)", " ").ToString();
        All["本" + unit + "不合格报告数量"] = Ds.Tables[6].Compute("sum(ct)", "").ToString();
        All["处理完成数量"]     = Ds.Tables[7].Compute("sum(ct)", "").ToString();
        All["未处理不合格报告数量"] = Ds.Tables[8].Compute("sum(ct)", "").ToString();



        Rooms.Rows.Add(All);

        return(Rooms);
    }
예제 #10
0
    /// <summary>
    /// 信用评价
    /// </summary>
    /// <returns></returns>
    public DataTable XYPJ()
    {
        #region 查询语句

        string Sql = @" 
                        DECLARE @Page int
                        DECLARE @PageSize int
                        SET @Page = {0}
                        SET @PageSize = {1}
                        SET NOCOUNT ON
                        DECLARE @TempTable TABLE (IndexId int identity, _keyID int)
                        INSERT INTO @TempTable
                        (
	                        _keyID
                        )
                        select ID from Sys_ReditRating where CreateOn between '{2}' AND '{3}' AND TestRoomCode IN ({4}) Order By  CreateOn Desc

                        SELECT
                        segmentcode  as '标段',
						companycode as '单位',
						testroomcode as '试验室',
						CompanyType as '单位性质',
						name as '姓名',
						idcard as '身份证',
						job as '职务',
						deduct as '总扣分数',
						remark as '备注',
						createon as '评价时间'
                        FROM Sys_ReditRating
                        INNER JOIN @TempTable t ON Sys_ReditRating.ID = t._keyID
                        WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)
                        Order By  CreateOn Desc

                        DECLARE @C int
                        select @C= count(ID)  from Sys_ReditRating where CreateOn between '{2}' AND '{3}' AND TestRoomCode IN ({4}) 
                        select @C 
                        ";

        Sql = string.Format(Sql, 1, 100000, DateTime.Parse(StartDate).AddDays(-1).ToString("yyyy-MM-dd"), DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd"), SelectedTestRoomCodes);
        #endregion

        BLL_Document BLL = new BLL_Document();



        #region 事件段数量
        Sql = string.Format(Sql, SelectedTestRoomCodes, DateTime.Parse(StartDate).AddDays(-1).ToString("yyyy-MM-dd"), DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd"));

        DataSet Ds = BLL.GetDataSet(Sql);

        #endregion


        if (Ds.Tables.Count > 1)
        {
            DataTable List = Ds.Tables[0];

            #region 替换编码-》名称 防止SQL混乱

            string SqlTemp = "select nodecode,description from sys_tree ";

            DataTable TabTemp = BLL.GetDataTable(SqlTemp);

            foreach (DataRow Dr in List.Rows)
            {
                try
                {
                    TabTemp.DefaultView.RowFilter = " nodecode = '" + Dr["标段"].ToString() + "'";
                    Dr["标段"] = TabTemp.DefaultView.ToTable().Rows[0]["description"].ToString();
                    TabTemp.DefaultView.RowFilter = "";

                    TabTemp.DefaultView.RowFilter = " nodecode = '" + Dr["单位"].ToString() + "'";
                    Dr["单位"] = TabTemp.DefaultView.ToTable().Rows[0]["description"].ToString();
                    TabTemp.DefaultView.RowFilter = "";

                    TabTemp.DefaultView.RowFilter = " nodecode = '" + Dr["试验室"].ToString() + "'";
                    Dr["试验室"] = TabTemp.DefaultView.ToTable().Rows[0]["description"].ToString();
                    TabTemp.DefaultView.RowFilter = "";
                }
                catch
                { }


                TabTemp.DefaultView.RowFilter = "";
            }
            TabTemp.Clear();
            #endregion

            return(List);
        }
        else
        {
            return(null);
        }
        return(null);
    }
예제 #11
0
    public void List()
    {
        string StartDate = "StartDate".RequestStr();
        string EndDate   = "EndDate".RequestStr().ToDateTime().AddDays(1).ToString("yyyy-MM-dd");



        #region SQL



        string Sql = @" 


                        -- 0 施工前三
                        select top 3
						t2.description+' '+t1.description as '单位'
						
						,count(1) as '数量' 
						from sys_document a 
                        join sys_module b on a.moduleid=b.id
						left outer join sys_tree t on t.nodecode = a.testroomcode
						left outer join sys_tree t1 on t1.nodecode = left(t.nodecode,12)
						left outer join sys_tree t2 on t2.nodecode = left(t.nodecode,8)
                        where status>0 and b.moduletype=1  and  a.bgrq between 
                        '{1}' and '{0}' AND t1.DepType='@unit_施工单位'
						group by testroomcode ,t1.description,t2.description
						order by 数量 desc

                        -- 1 施工后三
                        select top 3
						t2.description+' '+t1.description as '单位'
						
						,count(1) as '数量' 
						from sys_document a 
                        join sys_module b on a.moduleid=b.id
						left outer join sys_tree t on t.nodecode = a.testroomcode
						left outer join sys_tree t1 on t1.nodecode = left(t.nodecode,12)
						left outer join sys_tree t2 on t2.nodecode = left(t.nodecode,8)
                        where status>0 and b.moduletype=1  and  a.bgrq between 
                        '{1}' and '{0}' AND t1.DepType='@unit_施工单位'
						group by testroomcode ,t1.description,t2.description
						order by 数量 asc
                        
                        -- 2 监理前三
                        select top 3
						t2.description+' '+t1.description as '单位'
						
						,count(1) as '数量' 
						from sys_document a 
                        join sys_module b on a.moduleid=b.id
						left outer join sys_tree t on t.nodecode = a.testroomcode
						left outer join sys_tree t1 on t1.nodecode = left(t.nodecode,12)
						left outer join sys_tree t2 on t2.nodecode = left(t.nodecode,8)
                        where status>0 and b.moduletype=1  and  a.bgrq between 
                        '{1}' and '{0}' AND t1.DepType='@unit_监理单位'
						group by testroomcode ,t1.description,t2.description
						order by 数量 desc

                        -- 3系统登录次数
                        SELECT 
						top 3
						t2.description+' '+t1.description as '单位'
						,
						COUNT(1) as '数量' 
						FROM dbo.sys_loginlog a
						left outer join sys_tree t on t.nodecode = a.testroomcode
						left outer join sys_tree t1 on t1.nodecode = left(t.nodecode,12)
						left outer join sys_tree t2 on t2.nodecode = left(t.nodecode,8)
						WHERE  FirstAccessTime between '{1}' and '{0}' AND len(testroomcode)>12  
						group by testroomcode ,t1.description,t2.description
						order by 数量 desc

                        -- 4 系统登录次数
                        SELECT 
						top 3
						t2.description+' '+t1.description as '单位'
						,
						COUNT(1) as '数量' 
						FROM dbo.sys_loginlog a
						left outer join sys_tree t on t.nodecode = a.testroomcode
						left outer join sys_tree t1 on t1.nodecode = left(t.nodecode,12)
						left outer join sys_tree t2 on t2.nodecode = left(t.nodecode,8)
						WHERE  FirstAccessTime between '{1}' and '{0}' AND len(testroomcode)>12  
						group by testroomcode ,t1.description,t2.description
						order by 数量 asc
                  


                        ";


        #endregion



        Sql = string.Format(Sql, EndDate, StartDate);


        BLL_Document BLL = new BLL_Document();
        DataSet      Ds  = BLL.GetDataSet(Sql);



        DataTable Out = new DataTable();
        Out.Columns.Add(new DataColumn("0"));
        Out.Columns.Add(new DataColumn("1"));
        Out.Columns.Add(new DataColumn("2"));
        Out.Columns.Add(new DataColumn("3"));
        Out.Columns.Add(new DataColumn("4"));
        Out.Columns.Add(new DataColumn("5"));
        Out.Columns.Add(new DataColumn("6"));
        Out.Columns.Add(new DataColumn("7"));
        Out.Columns.Add(new DataColumn("8"));
        Out.Columns.Add(new DataColumn("9"));


        DataRow Dr = Out.NewRow();
        Out.Rows.Add(Dr);
        Dr = Out.NewRow();
        Out.Rows.Add(Dr);
        Dr = Out.NewRow();
        Out.Rows.Add(Dr);
        Dr = Out.NewRow();
        Out.Rows.Add(Dr);

        int ct = 0;
        for (int i = 0; i < Ds.Tables.Count; i++)
        {
            int rt = 0;
            foreach (DataRow tDr in Ds.Tables[i].Rows)
            {
                Out.Rows[rt][ct + 0] = tDr[0].ToString();
                Out.Rows[rt][ct + 1] = tDr[1].ToString();

                Out.Rows[3][ct + 0] = "合计";
                Out.Rows[3][ct + 1] = (Out.Rows[3][ct + 1].ToString().Toint() + tDr[1].ToString().Toint()).ToString();
                rt++;
            }
            ct = ct + 2;
        }



        string Json = JsonConvert.SerializeObject(Out);
        Json = "{\"rows\":" + Json + ",\"total\":0}";

        Response.Write(Json);
        Response.End();
    }
예제 #12
0
    /// <summary>
    /// 施工部位的原材料使用情况
    /// </summary>
    /// <param name="RecordCount"></param>
    /// <param name="PageIndex"></param>
    /// <param name="PageSize"></param>
    /// <param name="StartDate"></param>
    /// <param name="EndDate"></param>
    /// <param name="TestRoom"></param>
    /// <param name="Position"></param>
    /// <returns></returns>
    public static DataTable PositionItem(out int RecordCount, int PageIndex, int PageSize,
                                         string StartDate, string EndDate,
                                         string TestRoom,
                                         string Position
                                         )
    {
        #region SQL

        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(200))
                        INSERT INTO @TempTable
                        (
	                        _keyID
                        )
                        select sys_TJ_MainData.DataID from [dbo].[sys_TJ_MainData] 
                          join [dbo].[sys_TJ_Item_Module] on [dbo].[sys_TJ_MainData].ModuleID = [dbo].[sys_TJ_Item_Module].ModuleID
                          WHERE 1=1 
                          {0} 
 

                        SELECT 
                          sys_TJ_MainData.FactoryName as '生产厂家', 
                          sys_TJ_Item.ItemName as '原材',
                          {3} as '型号',
                          sys_TJ_MainData.SGBW as '工程部位',
                          sys_TJ_MainData.BGRQ as '报告日期',
                          sys_TJ_MainData.BGBH as '报告编号'
                           from  sys_TJ_MainData
                          JOIN sys_TJ_Item_Module on sys_TJ_MainData.ModuleID = sys_TJ_Item_Module.ModuleID 
                          Join sys_TJ_Item on sys_TJ_Item.ItemID = sys_TJ_Item_Module.ItemID
                        INNER JOIN @TempTable t ON sys_TJ_MainData.DataID = t._keyID
                        WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)
                         {0}        
                        Order By  BGRQ Desc

                        DECLARE @C int
                        select @C= count(DataID) from [dbo].[sys_TJ_MainData] 
                          join [dbo].[sys_TJ_Item_Module] on [dbo].[sys_TJ_MainData].ModuleID = [dbo].[sys_TJ_Item_Module].ModuleID
                          where 1=1 
                          {0}
                        select @C 
                        ";


        #endregion



        string Where = "  ";

        Where += string.IsNullOrEmpty(StartDate) ? " " : " AND BGRQ >='" + StartDate + "' ";
        Where += string.IsNullOrEmpty(EndDate) ? " " : "  AND BGRQ <='" + DateTime.Parse(EndDate).AddDays(1).ToShortDateString() + "' ";
        Where += string.IsNullOrEmpty(TestRoom) ? " " : " AND  TestRoomCode in(" + TestRoom + ") ";
        Where += string.IsNullOrEmpty(Position) ? " " : "  AND sys_TJ_MainData.SGBW ='" + Position + "' ";



        Sql = string.Format(Sql,
                            Where,
                            PageIndex,
                            PageSize,
                            " ' ' "
                            );

        RecordCount = 0;
        BLL_Document BLL = new BLL_Document();
        DataSet      Ds  = BLL.GetDataSet(Sql);
        RecordCount = int.Parse(Ds.Tables[1].Rows[0][0].ToString());
        return(Ds.Tables[0]);
    }
예제 #13
0
    public void List()
    {
        int RecordCount = 0;

        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(200))
                        INSERT INTO @TempTable
                        (
	                        _keyID
                        )
                        select id from  sys_document join Sys_Tree t1 on t1.NodeCode = sys_document.SegmentCode   where ModuleID in('A0C51954-302D-43C6-931E-0BAE2B8B10DB') {0}  order by t1.OrderID asc

                         select  
                    id,
                    t1.description as '标段名称',
                    t2.description as '单位名称',
                    t3.description as '试验室名称',
                    Ext2 as '管理编号',
                    Ext1 as '设备名称',
                    Ext3 as '生产厂家',
                    Ext4 as '规格型号',
                    Ext9 as '数量',
                    Ext11 as '检定情况',
                    Ext12 as '检定证书编号',
                    Ext21 as '上次校验日期',
                    Ext22 as '预计下次校验日期',
                    Ext15 as '检定周期'
                     from  sys_document 
                     join Sys_Tree t1 on t1.NodeCode = sys_document.SegmentCode
                     join Sys_Tree t2 on t2.NodeCode = sys_document.CompanyCode
                     join Sys_Tree t3 on t3.NodeCode = sys_document.TestRoomCode
                        INNER JOIN @TempTable t ON sys_document.id = t._keyID
                        WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)
                        AND ModuleID in('A0C51954-302D-43C6-931E-0BAE2B8B10DB') 
                         {0}        
                        Order By  t1.OrderID  asc

                        DECLARE @C int
                        select @C=Count(id) from  sys_document   where ModuleID in('A0C51954-302D-43C6-931E-0BAE2B8B10DB') {0}
                        select @C ";


        //A14 I14

        string Where = " AND Status>0  ";


        switch ("meet".RequestStr())
        {
        case "1":
            Where += " AND Ext22 <'" + DateTime.Now.AddDays(1).AddSeconds(-1).ToString() + "' ";
            break;

        case "2":
            Where += " AND Ext22 >'" + DateTime.Now.AddDays(1).AddSeconds(-1).ToString() + "' ";
            break;
        }

        #region For首页
        if (!"NUM".RequestStr().IsNullOrEmpty())
        {
            Where += " and TestRoomCode in ('" + "NUM".RequestStr() + "') ";
        }
        else if (!String.IsNullOrEmpty(SelectedTestRoomCodes))
        {
            Where += " and TestRoomCode in (" + SelectedTestRoomCodes + ") ";
        }
        else
        {
            Where += "and 1=1";
        }



        if (!string.IsNullOrEmpty("RPNAME".RequestStr()))
        {
            switch ("RPNAME".RequestStr())
            {
            case "1":     //待标定数
                Where += " AND Ext22 <'" + DateTime.Now.AddDays(1).AddSeconds(-1).ToString() + "' ";
                break;
            }
        }
        #endregion

        Sql = string.Format(Sql,
                            Where,
                            "page".RequestStr(),
                            "rows".RequestStr());

        RecordCount = 0;
        BLL_Document BLL = new BLL_Document();
        DataSet      Ds  = BLL.GetDataSet(Sql);
        RecordCount = int.Parse(Ds.Tables[1].Rows[0][0].ToString());


        decimal Tempc = Math.Round(decimal.Parse(Ds.Tables[1].Rows[0][0].ToString()) / decimal.Parse("rows".RequestStr()), 2);
        Tempc = Math.Ceiling(Tempc);

        string Json      = JsonConvert.SerializeObject(Ds.Tables[0]);
        int    pageCount = Tempc.ToString().Toint();
        Json = "{\"total\": \"" + pageCount + "\", \"page\": \"" + "page".RequestStr() + "\", \"records\": \"" + RecordCount + "\", \"rows\" : " + Json + "}";

        Response.Write(Json);
        Response.End();
    }
예제 #14
0
    public void List()
    {
        int RecordCount = 0;

        #region SQL



        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(200))
                        INSERT INTO @TempTable
                        (
	                        _keyID
                        )
                         SELECT KMID  FROM sys_KeyModify 

                          WHERE 1=1 
                          {0} 
                            Order by ModifyTime DESC

        
                          SELECT sys_module.name,sys_KeyModify.*,
t1.DESCRIPTION +' '+sys_tree.DESCRIPTION as DESCRIPTION
 FROM sys_KeyModify
left outer join sys_tree on sys_KeyModify.testroomcode = sys_tree.nodecode
left outer join sys_tree as t1 on Left(sys_KeyModify.testroomcode,8) = t1.nodecode
left outer join sys_module on sys_module.id = sys_KeyModify.moduleid
                        INNER JOIN @TempTable t ON sys_KeyModify.KMID = t._keyID
                        WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)
                         {0}        
                        Order By  ModifyTime Desc

                        DECLARE @C int
                        select @C= count(DataID) from sys_KeyModify
                         
                          where 1=1 
                          {0}
                        select @C 
                        ";


        #endregion



        string Where = "  ";

        Where += string.IsNullOrEmpty("StartDate".RequestStr()) ? " " : " AND modifytime >='" + "StartDate".RequestStr() + "' ";
        Where += string.IsNullOrEmpty("EndDate".RequestStr()) ? " " : "  AND modifytime <='" + DateTime.Parse("EndDate".RequestStr()).AddDays(1).ToShortDateString() + "' ";
        Where += string.IsNullOrEmpty(SelectedTestRoomCodes) ? " " : " AND  TestRoomCode in(" + SelectedTestRoomCodes + ") ";



        Sql = string.Format(Sql,
                            Where,
                            "page".RequestStr(),
                            "rows".RequestStr());

        RecordCount = 0;
        BLL_Document BLL = new BLL_Document();
        DataSet      Ds  = BLL.GetDataSet(Sql);
        RecordCount = int.Parse(Ds.Tables[1].Rows[0][0].ToString());



        string Json = JsonConvert.SerializeObject(Ds.Tables[0]);
        Json = "{\"rows\":" + Json + ",\"total\":" + RecordCount + "}";

        Response.Write(Json);
        Response.End();
    }
예제 #15
0
    public void List()
    {
        int RecordCount = 0;

        DataSet DS    = LineDbHelperSQL.Query("SELECT [Account] FROM [Sys_Users]");
        string  Users = string.Empty;

        if (DS.Tables.Count > 0 && DS.Tables[0].Rows.Count > 0)
        {
            foreach (DataRow Dr in DS.Tables[0].Rows)
            {
                Users += Users.IsNullOrEmpty() ? "'" + Dr[0].ToString() + "'" : ",'" + Dr[0].ToString() + "'";
            }
        }

        #region SQL



        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(200))
                        INSERT INTO @TempTable
                        (
	                        _keyID
                        )
                         SELECT ID  FROM sys_operate_log 

                          WHERE 1=1 
                          {0} 
                           Order by modifiedDate DESC

        
                         SELECT  sys_operate_log.ID,
                            modifiedby as 'YH',
                            t2.description as 'BD',
                            t3.description as 'DW',
                            t1.description as 'SYS',
                            modifiedDate as 'CZRQ',
                            optType as 'CZLX',
                            sys_module.name as 'MB',
                            DataName as 'BGRQ',
                            BGBH as 'BGBH',
                            modifyitem as 'XGRZ'
                              FROM sys_operate_log
                              left outer join sys_module  on sys_module.id = sys_operate_log.moduleID
                              left outer join sys_tree t1 on t1.nodecode = sys_operate_log.testroomcode
                              left outer join sys_tree t2 on t2.nodecode = left(sys_operate_log.testroomcode,8)
                              left outer join sys_tree t3 on t3.nodecode = left(sys_operate_log.testroomcode,12)
                        INNER JOIN @TempTable t ON sys_operate_log.ID = t._keyID
                        WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)
                         {0}        
                        Order By  modifiedDate Desc

                        DECLARE @C int
                        select @C= count(ID) from sys_operate_log
                         
                          where 1=1 
                          {0}
                        select @C 
                        ";


        #endregion



        string Where = "  ";

        Where += string.IsNullOrEmpty("StartDate".RequestStr()) ? " " : " AND modifiedDate >='" + "StartDate".RequestStr() + "' ";
        Where += string.IsNullOrEmpty("EndDate".RequestStr()) ? " " : "  AND modifiedDate <='" + DateTime.Parse("EndDate".RequestStr()).AddDays(1).ToShortDateString() + "' ";
        Where += string.IsNullOrEmpty(SelectedTestRoomCodes) ? " " : " AND  TestRoomCode in(" + SelectedTestRoomCodes + ") ";
        Where += string.IsNullOrEmpty("Person".RequestStr()) ? " " : " AND modifiedby like'%" + "Person".RequestStr() + "%' ";
        Where += Users.IsNullOrEmpty() ? "" : " AND modifiedby in (" + Users + ")";


        Sql = string.Format(Sql,
                            Where,
                            "page".RequestStr(),
                            "rows".RequestStr());

        RecordCount = 0;
        BLL_Document BLL = new BLL_Document();
        DataSet      Ds  = BLL.GetDataSet(Sql);
        RecordCount = int.Parse(Ds.Tables[1].Rows[0][0].ToString());



        string Json = JsonConvert.SerializeObject(Ds.Tables[0]);
        Json = "{\"rows\":" + Json + ",\"total\":" + RecordCount + "}";

        Response.Write(Json);
        Response.End();
    }
예제 #16
0
    public string GetCharJson()
    {
        #region 查询语句



        //按照试验室排名 无分数不显示
        string SqlBySYS = @"
                        select 
                        t1.description+'  '+t2.description+'  '+t3.description description,
                        sum(deduct) deduct from [sys_ReditRating] r
                        left outer join sys_Tree t1 on t1.nodecode = r.segmentcode
                        left outer join sys_Tree t2 on t2.nodecode = r.CompanyCode
                        left outer join sys_Tree t3 on t3.nodecode = r.testroomcode
                        where (r.ISDeleted <> 0 or r.ISDeleted is null) and r.testroomcode in ({0})  ANd r.CreateOn >='{1}' AND r.CreateOn<='{2}'
                        group by testroomcode,t1.description,t2.description,t3.description
                        order by deduct desc
                        ";

        //按照人员排名 无分数不显示
        string SqlByPerson = @"
                            select 
                            name as description,
                            sum(deduct) deduct from [sys_ReditRating] r
                            where (r.ISDeleted <> 0 or r.ISDeleted is null) AND Name <>''  and r.testroomcode in ({0})  ANd r.CreateOn >='{1}' AND r.CreateOn<='{2}'
                            group by name
                            order by deduct desc

                            ";
        #endregion

        BLL_Document BLL = new BLL_Document();

        string Sql = "n".RequestStr() == "0" ? SqlByPerson : SqlBySYS;

        #region 事件段数量
        Sql = string.Format(Sql, SelectedTestRoomCodes, "StartDate".RequestStr(), DateTime.Parse("EndDate".RequestStr()).AddDays(1).ToString("yyyy-MM-dd"));

        DataSet Ds = BLL.GetDataSet(Sql);


        #endregion


        if (Ds.Tables.Count > 0)
        {
            foreach (DataRow Dr in Ds.Tables[0].Rows)
            {
                if (Dr["deduct"].ToString() == "" || Dr["deduct"].ToString() == "NULL")
                {
                    Dr["deduct"] = "0";
                }
            }
            DataTable List = Ds.Tables[0];



            return(JsonConvert.SerializeObject(List));
        }
        else
        {
            return("");
        }
    }
예제 #17
0
    /// <summary>
    /// 列表
    /// </summary>
    /// <returns>Json:string</returns>
    public string GetListJson()
    {
        string testroom  = "testroom".RequestStr();
        string name      = "name".RequestStr();
        string createdby = "createdby".RequestStr();
        string deduct    = "deduct".RequestStr();
        string SqlWhere  = " AND 1=1 ";

        SqlWhere += testroom.IsNullOrEmpty() ? string.Empty : " AND t3.DESCRIPTION like '%" + testroom + "%' ";
        SqlWhere += name.IsNullOrEmpty() ? string.Empty : " AND a.name like '%" + name + "%' ";
        SqlWhere += createdby.IsNullOrEmpty() ? string.Empty : " AND a.createdby like '%" + createdby + "%' ";
        SqlWhere += deduct.IsNullOrEmpty() ? string.Empty : " AND a.remark like '%" + deduct + "%' ";

        #region 查询语句

        string Sql = @" 
                        DECLARE @Page int
                        DECLARE @PageSize int
                        SET @Page = {0}
                        SET @PageSize = {1}
                        SET NOCOUNT ON
                        DECLARE @TempTable TABLE (IndexId int identity, _keyID int)
                        INSERT INTO @TempTable
                        (
	                        _keyID
                        )
                        select ID from Sys_ReditRating where CreateOn between '{2}' AND '{3}' AND TestRoomCode IN ({4}) AND (ISDeleted <> 0 or ISDeleted is  NULL) Order By  CreateOn Desc
                        
                        SELECT
                        SysID,
                        t1.DESCRIPTION  as '标段',
						 t2.DESCRIPTION  as '单位',
						 t3.DESCRIPTION  as '试验室',
						CompanyType as '单位性质',
                        RType as '评价类别',
						name as '姓名',
						idcard as '身份证',
						job as '职务',
						deduct as '总扣分数',
						remark as '备注',
						createon as '评价时间',
                        createby as '评价人'
                        FROM Sys_ReditRating a
                        left outer join sys_tree t1 on t1.NodeCode = a.SegmentCode 
					    left outer join sys_tree t2 on t2.NodeCode = a.companycode
					    left outer join sys_tree t3 on t3.NodeCode = a.testroomcode 
                        INNER JOIN @TempTable t ON a.ID = t._keyID
                        WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)
                        {5}
                        Order By  CreateOn Desc

                        DECLARE @C int
                        select @C= count(ID)  from Sys_ReditRating a left outer join sys_tree t3 on t3.NodeCode = a.testroomcode  where CreateOn between '{2}' AND '{3}' AND TestRoomCode IN ({4})  AND (ISDeleted <> 0 or ISDeleted is  NULL) {5}
                        select @C 
                        ";

        Sql = string.Format(Sql, PageIndex, PageSize, "StartDate".RequestStr(), DateTime.Parse("EndDate".RequestStr()).AddDays(1).ToString("yyyy-MM-dd"), SelectedTestRoomCodes, SqlWhere);
        #endregion

        BLL_Document BLL = new BLL_Document();

        DataSet Ds = BLL.GetDataSet(Sql);



        if (Ds.Tables.Count > 1)
        {
            DataTable List = Ds.Tables[0];

            RecordCount = Ds.Tables[1].Rows[0][0].ToString();

            return(JsonConvert.SerializeObject(List));
        }
        else
        {
            return("");
        }
    }
예제 #18
0
    /// <summary>
    /// 添加
    /// </summary>
    public string Edit()
    {
        string Result = "";

        try
        {
            string KMID    = Request["KMID"].ToString();
            string Content = Request["Content"].ToString();
            string Person  = UserName;
            //rolename


            string Sql = @"

                UPDATE [dbo].[sys_KeyModify]
                          SET 
                           [YZUserName] = '{0}'
                          ,[YZContent] = '{1}'
                          ,[YZOPTime] = '{2}'
                          ,Status={4}
                   WHERE KMID = '{3}'

";

            Sql = string.Format(Sql, Person, Content, DateTime.Now.ToString(), KMID, "Type".RequestStr());
            BLL_Document BLL = new BLL_Document();



            Result = BLL.ExcuteCommand(Sql) > 0 ? "true" : "false";

            try
            {
                #region 发短信
                if ("Type".RequestStr() == "2" && Result == "true")
                {
                    sys_line sysBaseLine = System.Web.HttpContext.Current.Session["SysBaseLine"] as sys_line;
                    DataSet  DsTemp      = LineDbHelperSQL.Query("SELECT [SMSState] FROM [sys_line] where ID ='" + sysBaseLine.ID + "'");
                    if (DsTemp.Tables.Count > 0 && DsTemp.Tables[0].Rows.Count > 0 && DsTemp.Tables[0].Rows[0][0].ToString() == "1")
                    {
                        string SMSSql = @"
                                 SELECT sys_module.name,sys_KeyModify.BGBH,sys_KeyModify.ModifyItem,sys_KeyModify.YzUserName,
                        t1.DESCRIPTION +' '+sys_tree.DESCRIPTION as DESCRIPTION,TestRoomCode
                         FROM sys_KeyModify
                        left outer join sys_tree on sys_KeyModify.testroomcode = sys_tree.nodecode
                        left outer join sys_tree as t1 on Left(sys_KeyModify.testroomcode,8) = t1.nodecode
                        left outer join sys_module on sys_module.id = sys_KeyModify.moduleid
                        where sys_KeyModify.KMID = '" + KMID + "'";

                        DataSet Ds = BLL.GetDataSet(SMSSql);

                        string SmsContent = "{0} {1} {2} {3} {4}拒绝";
                        if (Ds.Tables[0] != null && Ds.Tables[0].Rows.Count > 0)
                        {
                            DataRow Dr = Ds.Tables[0].Rows[0];

                            ModifyItem[] Temp = Newtonsoft.Json.JsonConvert.DeserializeObject <ModifyItem[]>(Dr["ModifyItem"].ToString());

                            SmsContent = string.Format(SmsContent
                                                       , Dr["DESCRIPTION"].ToString()
                                                       , Dr["name"].ToString()
                                                       , Dr["BGBH"].ToString()
                                                       , Temp[0].Description + Temp[0].CurrentValue + "修改为" + Temp[0].OriginalValue
                                                       , Dr["YzUserName"].ToString()
                                                       );



                            string CPS = @"  SELECT CellPhone
                        FROM [SYGLDB_ZhengXu].[dbo].[sys_sms_receiver]
                        where TestRoomCode ='" + Ds.Tables[0].Rows[0]["TestRoomCode"].ToString() + "' and CellPhone <>'' ANd CellPhone is not null and IsActive=1";

                            string MS = "";
                            Ds = BLL.GetDataSet(CPS);
                            if (Ds.Tables[0] != null && Ds.Tables[0].Rows.Count > 0)
                            {
                                foreach (DataRow Dr1 in Ds.Tables[0].Rows)
                                {
                                    if (!Dr1[0].ToString().IsNullOrEmpty())
                                    {
                                        MS += MS.IsNullOrEmpty() ? Dr1[0].ToString() : "," + Dr1[0].ToString();
                                    }
                                }
                            }
                            if (!MS.IsNullOrEmpty())
                            {
                                string SMSResult = SendSMS("Mobile=" + MS + "&Content=" + SmsContent + "&Stime=" + DateTime.Now.ToString() + "&Extno=1");
                            }
                        }
                    }
                }
                #endregion
            }
            catch { }
        }
        catch
        {
            Result = "false";
        }
        return(Result);
    }