/// <summary>
/// 获取试验室
/// </summary>
/// <returns></returns>
public string GetTestRoom()
{
string Result = string.Empty;
string Sql = @"
select
t.nodecode,
s.description+'-'+c.description +'-'+t.description as description
from Sys_tree t
left outer join Sys_tree s
on left(t.nodecode,8) = s.nodecode
left outer join Sys_tree c
on left(t.nodecode,12) = c.nodecode
where t.NodeCode in ({0})
order by c.description asc
";
BLL_Document BLL = new BLL_Document();
DataTable List = BLL.GetDataTable(string.Format(Sql, SelectedTestRoomCodes));
Result = JsonConvert.SerializeObject(List);
return(Result);
}
public string GetCount()
{
string Result = "0";
BLL_Document BLL = new BLL_Document();
DataTable _Data = BLL.GetDataTable("SELECT DataID FROM dbo.sys_test_data WHERE DataID='" + Request["id"].ToString() + "' AND Status=1 order by SerialNumber asc");
Result = _Data.Rows.Count.ToString();
return(Result);
}
/// <summary>
/// 获取人员信息
/// </summary>
public string GetPerson()
{
string TestRoomCode = "TestRoomCode".RequestStr();
string Sql = @" SELECT Ext1 Name,Ext5 Job
FROM dbo.sys_document a JOIN dbo.v_bs_codeName b
ON a.ModuleID='08899BA2-CC88-403E-9182-3EF73F5FB0CE'
AND a.TestRoomCode=b.试验室编码
JOIN dbo.Sys_Tree c ON LEFT(a.TestRoomCode,12)=c.NodeCode
where a.TestRoomCode = '{0}'
Order By OrderID,TestRoomCode ASC";
Sql = string.Format(Sql, TestRoomCode);
BLL_Document Bll = new BLL_Document();
DataTable DT = Bll.GetDataTable(Sql);
return(JsonConvert.SerializeObject(DT));
}
public void LoadReport()
{
string DocumentID = "ID".RequestStr();
string SheetIndex = "SI".RequestStr();
string SheetXML = "";
string sheetData = "";
string SheetName = "";
string SheetID = "";
string ReportSheetID = "";
SheetIndex = SheetIndex.IsNullOrEmpty() ? "0" : SheetIndex;
try
{
JZDocument sheetDataAreaCells;
List <JZCell> Cells = new List <JZCell>();
#region 查询数据
DataTable _TempTable = new DataTable();
string _TempStr = @"
Select testroomcode, m.reportsheetid,d.Data
from [dbo].[sys_document] d
left outer join [dbo].[sys_module] m
on d.ModuleId = m.ID
where d.ModuleID = 'E77624E9-5654-4185-9A29-8229AAFDD68B'
ANd TestRoomCode = '{0}'";
_TempStr = string.Format(_TempStr, DocumentID);
BLL_Document Bll = new BLL_Document();
_TempTable = Bll.GetDataTable(_TempStr);
if (_TempTable.Rows.Count > 0)
{
sheetData = _TempTable.Rows[SheetIndex.Toint()]["Data"].ToString();
sheetDataAreaCells = Newtonsoft.Json.JsonConvert.DeserializeObject <JZDocument>(sheetData);
SheetCount = _TempTable.Rows.Count;
ReportSheetID = _TempTable.Rows[SheetIndex.Toint()]["reportsheetid"].ToString();
foreach (JZSheet Jzs in sheetDataAreaCells.Sheets)
{
if (Jzs.ID.ToString() == ReportSheetID)
{
SheetName = Jzs.Name;
SheetID = Jzs.ID.ToString();
Cells = Jzs.Cells;
break;
}
}
_TempStr = "select SheetXML ,SheetData from sys_sheet where ID = '{0}'";
SheetXML = Bll.ExcuteScalar(string.Format(_TempStr, ReportSheetID)).ToString();
}
else
{
throw new Exception("占无报告数据");
}
_TempTable.Clear();
_TempTable.Dispose();
#endregion
#region 创建WIN组件
FarPoint.Win.Spread.FpSpread WinSp = new FarPoint.Win.Spread.FpSpread();
WinSp.Sheets.Clear();
SheetXML = JZCommonHelper.GZipDecompressString(SheetXML);
int a = SheetXML.Length;
FarPoint.Win.Spread.SheetView SheetView = Serializer.LoadObjectXml(typeof(FarPoint.Win.Spread.SheetView), SheetXML, "SheetView") as FarPoint.Win.Spread.SheetView;
SheetView.SheetName = "Document";
if (sheetDataAreaCells != null)
{
foreach (JZCell cell in Cells)
{
//
if (SheetView.Cells[cell.Name].CellType.ToString() == "图片")
{
SheetView.Cells[cell.Name].ResetCellType();
SheetView.Cells[cell.Name].Value = "<img src='data:image/gif;base64," + cell.Value.ToString().Replace("\r\n", "") + "' />";
}
else if (SheetView.Cells[cell.Name].CellType.ToString() == "文本")
{
SheetView.Cells[cell.Name].Value = cell.Value;
}
else if (SheetView.Cells[cell.Name].CellType.ToString() == "长文本")
{
SheetView.Cells[cell.Name].ResetCellType();
SheetView.Cells[cell.Name].Value = cell.Value;
}
else if (SheetView.Cells[cell.Name].CellType.ToString() == "超链接")
{
SheetView.Cells[cell.Name].ResetCellType();
if (cell.Value != null)
{
try
{
string[] Temp = Newtonsoft.Json.JsonConvert.DeserializeObject <string[]>(cell.Value.ToString());
SheetView.Cells[cell.Name].Value = Temp[0] + "[" + Temp[2] + "]<img src='data:image/gif;base64," + Temp[1].Replace("\r\n", "") + "' />";
}
catch
{ }
}
}
}
}
WinSp.Sheets.Add(SheetView);
WinSp.LoadFormulas(true);
#endregion
#region 临时文件
string SitePath = Server.MapPath("~/Temp");
DirectoryInfo Dir = new System.IO.DirectoryInfo(SitePath);
if (!Dir.Exists)
{
Dir.Create();
}
#endregion
#region Save HTML
string FileName = SitePath + "/" + SheetID + DateTime.Now.ToString("yyMMddhhmmss") + ".html";
WinSp.ActiveSheet.SaveHtml(FileName);
FileInfo FHtml = new System.IO.FileInfo(FileName);
FileStream Fs = FHtml.OpenRead();
byte[] Buuff = new byte[Fs.Length];
Fs.Read(Buuff, 0, (int)Fs.Length);
Fs.Close();
Fs.Dispose();
string HTML = ASCIIEncoding.UTF8.GetString(Buuff);
File.Delete(FileName);
#endregion
#region show Element
StringBuilder Nav = new StringBuilder();
StringBuilder Cont = new StringBuilder();
Nav.Append("<ul class=\"nav nav-tabs\">");
Cont.Append("<div class=\"tab-content\">");
if (SheetCount.Toint() > 1)
{
for (int i = 0; i < SheetCount; i++)
{
Nav.Append("<li class=\"" + (i == SheetIndex.Toint() ? "active" : "") + "\" ><a data=\"testroominfo.aspx?ID=" + DocumentID + "&SI=" + i + "&R=" + new Random().Next(0, 100000) + "\" href=\"#t" + i + "\" data-toggle=\"tab\" >第" + (i + 1).ToString() + "</a></li>");
Cont.Append("<div class=\"tab-pane CPane " + (i == SheetIndex.Toint() ? "active" : "") + " \" id=\"t" + i.ToString() + "\">" + ((i == SheetIndex.Toint()) ? HTML : "") + "</div>");
}
Nav.Append("</ul>");
Cont.Append("</div>");
form1.InnerHtml = Nav.ToString() + Cont.ToString();
}
else
{
form1.InnerHtml = HTML;
}
#endregion
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
}
}
/// <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);
}