/// <summary>
/// 获取有下级信息的二级分类号数据
/// </summary>
/// <param name="TopClassID">类目ID</param>
/// <returns>DataSet</returns>
public DataSet getExactSecondTypeNumData(int TopClassID, string TopTypeNum)
{
strSql = new StringBuilder();
//strSql.Append("select * from view城建分类号新");
//strSql.Append(" where 一级类目ID = @TopClassID and 类目级别 = 2 and SUBSTRING(分类号, 1, 1) = @TopTypeNum and 分类号 is not null");
//strSql.Append(" and (ID IN (SELECT 分类号ID FROM 城建项目))");
//strSql.Append(" order by 分类号");
strSql.Append("select * from view城建分类号新 as b");
if (DataSql.ConnectType == DB.DBConnectType.SqlServer)//sql语句
{
strSql.Append(" where b.一级类目ID = @TopClassID and b.分类号 is not null and b.类目级别 = 2 and SUBSTRING(b.分类号, 1, 1) = @TopTypeNum ");
}
else//access语句
{
strSql.Append(" where b.一级类目ID = @TopClassID and b.分类号 is not null and b.类目级别 = 2 and MID(b.分类号, 1, 1) = @TopTypeNum ");
}
strSql.Append(" and exists (select * from 城建项目 as c ");
strSql.Append(" where c.分类号ID = b.ID ");
strSql.Append(" and exists ( select * from 城建案卷 where 项目ID = c.项目ID)) ");
strSql.Append(" order by 分类号");
SqlParameter[] parameter =
{
new SqlParameter("@TopClassID", SqlDbType.Int, 4),
new SqlParameter("@TopTypeNum", SqlDbType.NVarChar, 15)
};
parameter[0].Value = TopClassID;
parameter[1].Value = TopTypeNum;
return(DataSql.Query(strSql.ToString(), parameter));
}
/// <summary> 获得数据列表 </summary>
/// <param name="ProjectID">项目ID</param>
/// <returns>DataSet</returns>
public DataSet GetZhData(int ProjectID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT f.案卷ID,f.项目ID,f.案卷号,f.总登记号,f.题名,f.编制单位,f.编制时间,f.载体类型,f.数量,f.单位,f.规格,f.归档时间, ");
strSql.Append("f.主题词,f.存放地址,f.本工程卷数,f.本卷卷次,f.接收人,f.立卷人,f.审核人,f.审核时间,f.录入人, ");
if (UIPublicClass.PublicClass.IsInputForPageIndex)
{
strSql.Append("f.文件总页数,");
}
strSql.Append("f.附注,f.UserID,f.InTime ");
strSql.Append(" FROM 城建案卷 as f INNER JOIN");
strSql.Append(" 城建项目 ON f.项目ID = 城建项目.项目ID");
strSql.Append(" WHERE (城建项目.分类号ID IN");
strSql.Append(" (SELECT 分类号ID");
strSql.Append(" FROM 城建项目");
strSql.Append(" WHERE 项目ID = @项目ID)) order by 案卷号");
SqlParameter[] parameters =
{
new SqlParameter("@项目ID", SqlDbType.Int, 4)
};
parameters[0].Value = ProjectID;
return(DataSql.Query(strSql.ToString(), parameters));
}
///// <summary>
///// 一级分类号节点的信息获取
///// </summary>
///// <param name="TopClassID">一级类目ID</param>
///// <returns>一级分类号节点的结果集</returns>
//public DataSet getTopTypeNumNodesData(int TopClassID)
//{
// strSql = new StringBuilder();
// strSql.Append("select ID,分类号,类目名称 from view城建分类号新 where 一级类目ID = @TopClassID and 分类号 is not null and 类目级别 = 1");
// strSql.Append(" order by 分类号");
// SqlParameter[] parameter = {
// new SqlParameter("@TopClassID", SqlDbType.Int, 4) };
// parameter[0].Value = TopClassID;
// return DataSql.Query(strSql.ToString(), parameter);
//}
/// <summary>
/// 二级分类号节点的信息获取
/// </summary>
/// <param name="TopClassID">一级类目ID</param>
/// <returns>二级分类号节点的结果集</returns>
public DataSet getSecondTypeNumNodesData(int TopClassID, string TopTypeNum)
{
strSql = new StringBuilder();
strSql.Append("select ID,分类号,类目名称 from view城建分类号新");
if (DataSql.ConnectType == DB.DBConnectType.SqlServer)//sql语句
{
strSql.Append(" where 一级类目ID = @TopClassID and 分类号 is not null and 类目级别 = 2 and SUBSTRING(分类号, 1, 1) = @TopTypeNum");
}
else//access语句
{
strSql.Append(" where 一级类目ID = @TopClassID and 分类号 is not null and 类目级别 = 2 and MID(分类号, 1, 1) = @TopTypeNum");
}
strSql.Append(" order by 分类号");
SqlParameter[] parameter =
{
new SqlParameter("@TopClassID", SqlDbType.Int, 4),
new SqlParameter("@TopTypeNum", SqlDbType.NVarChar, 15)
};
parameter[0].Value = TopClassID;
parameter[1].Value = TopTypeNum;
DataSet ds = DataSql.Query(strSql.ToString(), parameter);
int rowcount = ds.Tables[0].Rows.Count;
return(ds);
}
//加载原件数据
public DataSet getData(int docID)
{
string strSql = "select 原件ID,序号,描述 from BZ_原件 where 文件ID =" + docID + " order by 序号";
DataSet ds = DataSql.Query(strSql);
return(ds);
}
/// <summary> 获取查询结果集 </summary>
/// <param name="topClassID">一级类目ID</param>
/// <param name="typeName">分类号名称</param>
/// <param name="projectNumS">项目编号起始</param>
/// <param name="projectNumE">项目编号终止</param>
/// <returns>DataSet</returns>
public DataSet getQueryRestule(int topClassID, string typeName, string projectNumS, string projectNumE)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select 项目编号,count(案卷ID) as 案卷数量,min(总登记号) as 总登记号起始,max(总登记号) as 总登记号终止, ");
strSql.Append("min(案卷号) as 案卷号起始,max(案卷号) as 案卷号终止 ");
strSql.Append("from view城建分类号新 inner join ");
strSql.Append("城建项目 on view城建分类号新.ID = 城建项目.分类号ID inner join ");
strSql.Append("城建案卷 on 城建项目.项目ID = 城建案卷.项目ID ");
strSql.Append("where view城建分类号新.一级类目ID = @topClassID and view城建分类号新.分类号 = @typeName ");
if (projectNumS != string.Empty && projectNumE != string.Empty)
{
strSql.Append("and 项目编号 >= " + projectNumS + " and 项目编号 <= " + projectNumE + " ");
}
else if (projectNumS != string.Empty)
{
strSql.Append("and 项目编号 = " + projectNumS + " ");
}
else if (projectNumE != string.Empty)
{
strSql.Append("and 项目编号 = " + projectNumE + " ");
}
strSql.Append("group by 项目编号 ");
SqlParameter[] parameter =
{
new SqlParameter("@topClassID", SqlDbType.Int),
new SqlParameter("@typeName", SqlDbType.NVarChar, 15)
};
parameter[0].Value = topClassID;
parameter[1].Value = typeName;
return(DataSql.Query(strSql.ToString(), parameter));
}
//获取文件信息
public DataSet getDoc(int fileID)
{
string strSql = "select 文件ID,件号 from BZ_文件 where 案卷ID =" + fileID + " order by 件号";
DataSet ds = DataSql.Query(strSql);
return(ds);
}
//获取案卷列表
public DataSet getFile(int ContentID)
{
string strSql = "select 案卷ID,案卷号 from BZ_案卷 where 目录ID =" + ContentID + " order by 案卷号";
DataSet ds = DataSql.Query(strSql);
return(ds);
}
//获取目录列表
public DataSet getContent(int TopClassID)
{
string strSql = "select 目录ID,目录号 from 案卷目录 where 一级类目ID =" + TopClassID;
DataSet ds = DataSql.Query(strSql);
return(ds);
}
/// <summary> 查询获取单位信息列表 </summary>
/// <param string="inDWID">存在的单位ID</param>
/// <param name="unitName">单位名称</param>
/// <param name="unitNum">单位编号</param>
/// <returns>DataTable</returns>
public DataTable getUnitList(string inDWID, string unitName, string unitNum)
{
string strWhere = string.Empty;
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,(dwbh +' | '+dwmc) as name from UnitManage where ID not in (" + inDWID + ")");
if (unitName != string.Empty)
{
//if (strWhere != string.Empty)
// strWhere += " and ";
strWhere += " and dwmc like '%" + unitName + "%'";
}
if (unitNum != string.Empty)
{
//if (strWhere != string.Empty)
// strWhere += " and ";
strWhere += " and dwbh like '%" + unitNum + "%'";
}
if (strWhere != string.Empty)
{
strSql.Append(strWhere);
}
return(DataSql.Query(strSql.ToString()).Tables[0]);
}
/// <summary>
/// 一级分类号数据信息获取
/// </summary>
/// <param name="TopClassID">类目ID</param>
/// <returns>DataSet</returns>
public DataSet getExactTopTypeNumData(int TopClassID)
{
strSql = new StringBuilder();
strSql.Append("select * from view城建分类号新 as a ");
strSql.Append(" where a.一级类目ID = @TopClassID and a.分类号 is not null and a.类目级别 = 1 ");
strSql.Append(" and exists (select * from view城建分类号新 as b");
if (DataSql.ConnectType == DB.DBConnectType.SqlServer)//sql语句
{
strSql.Append(" where b.一级类目ID = @TopClassID and b.分类号 is not null and b.类目级别 = 2 and SUBSTRING(b.分类号, 1, 1) = a.分类号 ");
}
else//access语句
{
strSql.Append(" where b.一级类目ID = @TopClassID and b.分类号 is not null and b.类目级别 = 2 and MID(b.分类号, 1, 1) = a.分类号 ");
}
strSql.Append(" and exists (select * from 城建项目 as c ");
strSql.Append(" where c.分类号ID = b.ID ");
strSql.Append(" and exists ( select * from 城建案卷 where 项目ID = c.项目ID))) ");
strSql.Append(" order by 分类号");
SqlParameter[] parameter =
{
new SqlParameter("@TopClassID", SqlDbType.Int, 4)
};
parameter[0].Value = TopClassID;
return(DataSql.Query(strSql.ToString(), parameter));
}
/// <summary>
/// 获得档案类型列表
/// </summary>
public DataSet getArchiveTypeList()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from 档案类型");
return(DataSql.Query(strSql.ToString()));
}
/// <summary>
/// 获取用户列表
/// </summary>
/// <param name="dataAdapter"></param>
/// <returns></returns>
public DataSet getUserList()
{
StringBuilder selectCommand = new StringBuilder();
selectCommand.Append("select 用户ID,用户名,账号,密码,IsAdmin from 用户");
return(DataSql.Query(selectCommand.ToString()));
}
/// <summary>
/// 获取分类号的项目类型
/// </summary>
public DataSet getProjectType()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select 项目分类名称 from 城建项目分类");
return(DataSql.Query(strSql.ToString()));
}
public static string StreamToPdf(string sql)
{
string pdfFileName = "";
try
{
#region 原dataReader写法
//System.Data.SqlClient.SqlDataReader dr = DataAccess.DataSql.ExecuteReader(sql);
//while (dr.Read())
//{
// if (!(dr[0] is DBNull))
// {
// MemoryStream ms = new MemoryStream((byte[])dr[0]); //创建Stream对象
// //存下文件名
// pdfFileName = FileSystem.ConvertPathOut + dr[1].ToString() + ".pdf";
// FileStream Fs = new FileStream(pdfFileName, FileMode.Create);
// //写入文件
// BinaryWriter Bw = new BinaryWriter(Fs);
// Bw.Write(ms.ToArray());
// Fs.Close();
// Bw.Close();
// System.Windows.Forms.Application.DoEvents();
// }
//}
#endregion
DataSet ds = DataSql.Query(sql);
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (Convert.ToString(ds.Tables[0].Rows[0][0]) != string.Empty)
{
MemoryStream ms = new MemoryStream((byte[])ds.Tables[0].Rows[0][0]); //创建Stream对象
//存下文件名
pdfFileName = FileSystem.ConvertPathOut + Convert.ToString(ds.Tables[0].Rows[0][1]) + ".pdf";
FileStream Fs = new FileStream(pdfFileName, FileMode.Create);
//写入文件
BinaryWriter Bw = new BinaryWriter(Fs);
Bw.Write(ms.ToArray());
Fs.Close();
Bw.Close();
System.Windows.Forms.Application.DoEvents();
}
}
}
}
catch { }
finally
{
}
return(pdfFileName);
}
/// <summary>
/// 获取一级类目
/// </summary>
/// <param name="DeptID">全宗ID</param>
/// <returns></returns>
public DataSet getTopClass(int DeptID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM 一级类目");
strSql.Append(" WHERE 全宗ID = " + DeptID + " order by 顺序");
return(DataSql.Query(strSql.ToString()));
}
/// <summary>
/// 获取用户拥有的角色ID数据集
/// </summary>
/// <param name="用户ID"></param>
/// <returns></returns>
public DataSet getUserRole(int UserID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select 角色ID from 用户角色");
strSql.Append(" where 用户ID = " + UserID);
return(DataSql.Query(strSql.ToString()));
}
/// <summary> 获取文件关联的单位 </summary>
/// <param name="docID">文件ID</param>
/// <returns>DataTable</returns>
public DataTable getDocUnitID(int docID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select dwID from UnitForDoc where docID = @DocID");
SqlParameter[] parameter = { new SqlParameter("@DocID", SqlDbType.Int) };
parameter[0].Value = docID;
return(DataSql.Query(strSql.ToString(), parameter).Tables[0]);
}
/// <summary>
/// 获取一级类目列表
/// </summary>
/// <returns></returns>
public DataSet getTopClassList(int 全宗ID)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append("select * from 一级类目");
sqlStr.Append(" where 全宗ID =" + 全宗ID + " and 类型ID in (1,2,4,9,12) order by 顺序");
DataSet ds = DataSql.Query(sqlStr.ToString());
return(ds);
}
/// <summary>
/// 获取角色关联的全宗ID数据集
/// </summary>
/// <param name="RoleID"></param>
/// <returns></returns>
public DataSet getRoleDept(int RoleID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT 全宗.全宗ID ");
strSql.Append(" FROM (全宗 INNER JOIN ");
strSql.Append(" 一级类目 ON 全宗.全宗ID = 一级类目.全宗ID) INNER JOIN ");
strSql.Append(" 角色权限 ON 一级类目.类目ID = 角色权限.一级类目ID ");
strSql.Append(" where 角色权限.角色ID = " + RoleID);
return(DataSql.Query(strSql.ToString()));
}
/// <summary>
/// 获取案卷号最大值
/// </summary>
/// <param name="ContentID">目录ID</param>
/// <returns>返回案卷号最大值</returns>
public DataSet getFileNumMax(int ContentID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select max(案卷号) as 案卷号 from BZ_案卷 where 目录ID = @ContentID");
SqlParameter[] parameter =
{
new SqlParameter("@ContentID", SqlDbType.Int, 4)
};
parameter[0].Value = ContentID;
return(DataSql.Query(strSql.ToString(), parameter));
}
/// <summary>
/// 案卷节点信息的获取
/// </summary>
/// <param name="ContentID">类目ID</param>
/// <returns>返回节点结果集</returns>
public DataSet getFileNodeData(int TypeNumID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select 案卷ID,案卷号 from view城建案卷旧 where 分类号ID = @TypeNumID order by 案卷号");
SqlParameter[] parameter =
{
new SqlParameter("@TypeNumID", SqlDbType.Int, 4)
};
parameter[0].Value = TypeNumID;
return(DataSql.Query(strSql.ToString(), parameter));
}
/// <summary>
/// 原件数据信息获取
/// </summary>
/// <param name="dataAdapter">SqlDataAdapter</param>
/// <param name="DocID">文件ID</param>
/// <returns>原件数据信息结果集</returns>
public static DataSet getPhotoData(int 案卷ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from SX_文件 where 案卷ID = @案卷ID order by 件号");
SqlParameter[] parameter =
{
new SqlParameter("@案卷ID", SqlDbType.Int, 4)
};
parameter[0].Value = 案卷ID;
return(DataSql.Query(strSql.ToString(), parameter));
}
/// <summary>
/// 案卷目录节点的信息获取
/// </summary>
/// <param name="TopClassID">类目ID</param>
/// <returns>案卷节点的结果集</returns>
public DataSet getContent(int TopClassID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select 目录号 from 案卷目录 where 一级类目ID = @TopClassID order by 目录号");
SqlParameter[] parameter =
{
new SqlParameter("@TopClassID", SqlDbType.Int, 4)
};
parameter[0].Value = TopClassID;
return(DataSql.Query(strSql.ToString(), parameter));
}
/// <summary>
/// 文件节点信息的获取
/// </summary>
/// <param name="FileID">案卷ID</param>
/// <returns>节点结果集</returns>
public DataSet getDocNodeData(int FileID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select 文件ID,件号 from view城建文件旧 where 案卷ID = @FileID order by 件号");
SqlParameter[] parameter =
{
new SqlParameter("@FileID", SqlDbType.Int, 4)
};
parameter[0].Value = FileID;
return(DataSql.Query(strSql.ToString(), parameter));
}
/// <summary>
/// 原件节点信息的获取
/// </summary>
/// <param name="DocID">文件ID</param>
/// <returns>原件节点结果集</returns>
public DataSet getYjNodeData(int DocID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select 原件ID,序号 from BZ_原件 where 文件ID = @DocID order by 序号");
SqlParameter[] parameters =
{
new SqlParameter("@DocID", SqlDbType.Int, 4)
};
parameters[0].Value = DocID;
return(DataSql.Query(strSql.ToString(), parameters));
}
/// <summary>
/// 原件数据信息获取
/// </summary>
/// <param name="dataAdapter">SqlDataAdapter</param>
/// <param name="DocID">文件ID</param>
/// <returns>原件数据信息结果集</returns>
public DataSet getData(int DocID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select 原件ID,城建旧大纲文件ID,序号,描述,扩展名, 路径, 保存名称 from 城建档案原件 where 城建旧大纲文件ID = @DocID order by 序号");
SqlParameter[] parameter =
{
new SqlParameter("@DocID", SqlDbType.Int, 4)
};
parameter[0].Value = DocID;
return(DataSql.Query(strSql.ToString(), parameter));
}
/// <summary>
/// 一级分类号节点的信息获取
/// </summary>
/// <param name="TopClassID">一级类目ID</param>
/// <returns>一级分类号节点的结果集</returns>
public DataSet getTopTypeNumNodesData(int TopClassID)
{
strSql = new StringBuilder();
strSql.Append("select ID,分类号,类目名称 from view城建分类号旧 where 一级类目ID = @TopClassID and 分类号 is not null and 类目级别 = 1");
strSql.Append(" order by 分类号");
SqlParameter[] parameter =
{
new SqlParameter("@TopClassID", SqlDbType.Int, 4)
};
parameter[0].Value = TopClassID;
return(DataSql.Query(strSql.ToString(), parameter));
}
/// <summary>
/// 获取件号的最大值
/// </summary>
/// <param name="FileID">案卷ID</param>
/// <returns>件号的最大值</returns>
public DataSet getDocMaxNum(int FileID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select max(件号) as 件号 from BZ_文件 where 案卷ID = @FileID");
SqlParameter[] parameter =
{
new SqlParameter("@FileID", SqlDbType.Int, 4)
};
parameter[0].Value = FileID;
return(DataSql.Query(strSql.ToString(), parameter));
}
/// <summary>
/// 城建档案原件数据信息获取
/// </summary>
/// <param name="DocID">文件ID</param>
/// <returns>原件数据信息结果集</returns>
public static DataSet getData_CJ(int DocID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from 城建档案原件 where 城建项目大纲文件ID = @DocID order by 序号");
SqlParameter[] parameters =
{
new SqlParameter("@DocID", SqlDbType.Int, 4)
};
parameters[0].Value = DocID;
return(DataSql.Query(strSql.ToString(), parameters));
}
/// <summary>
/// 获取项目表中的专业记载项
/// </summary>
/// <param name="项目ID"></param>
/// <returns></returns>
public DataSet getDataZYJZ(int 项目ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select 总用地面积, 总建筑面积, 幢数, 工程造价, 工程结算 from 城建项目");
strSql.Append(" where 项目ID= @项目ID");
SqlParameter[] parameters =
{
new SqlParameter("@项目ID", SqlDbType.Int, 4)
};
parameters[0].Value = 项目ID;
return(DataSql.Query(strSql.ToString(), parameters));
}
