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