public void GetDataArchives()
{
string connectionString = ConfigurationManager.ConnectionStrings["Archives"].ConnectionString;
List <DataSql> datas = new List <DataSql>();
using (SqlConnection sqlConn = new SqlConnection(connectionString))
{
string cmdString = "SELECT versement, etablissement, direction, service, dossiers, extremes, elimination, communication, cote, localisation FROM ArchivesV2";
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = sqlConn;
cmd.CommandText = cmdString;
sqlConn.Open();
var dr = cmd.ExecuteReader();
while (dr.Read())
{
// We verify that Elimination doesn't start with 'éliminé', if so we don't fetch.
if (!dr["elimination"].ToString().ToLower().Contains("éliminé") &&
!dr["communication"].ToString().ToLower().Contains("éliminé") &&
!dr["communication"].ToString().ToLower().Contains("eliminé"))
{
DataSql dataSql = new DataSql
{
Cote = dr["cote"].ToString(),
Versement = string.IsNullOrEmpty(dr["versement"].ToString())
? new DateTime(1900, 1, 1)
: Convert.ToDateTime(dr["versement"].ToString()),
Etablissement = dr["etablissement"].ToString(),
Direction = dr["direction"].ToString(),
Service = dr["service"].ToString(),
Dossiers = dr["dossiers"].ToString(),
Extremes = dr["extremes"].ToString(),
Elimination = dr["elimination"].ToString(),
Localisation = dr["localisation"].ToString()
};
datas.Add(dataSql);
}
}
Context.Response.Write(JsonConvert.SerializeObject(datas));
}
}
}
/// <summary>
/// 修改全宗
/// </summary>
/// <param name="UserID"></param>
/// <param name="FieldName"></param>
/// <param name="FieldValue"></param>
/// <returns></returns>
public bool UpdateDept(int DeptID, string FieldName, string FieldValue)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update 全宗 set " + FieldName + "='" + FieldValue + "'");
strSql.Append(" where 全宗ID = " + DeptID);
int rows = DataSql.ExecuteSql(strSql.ToString());
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 获取有下级信息的项目数据信息
/// </summary>
/// <returns>DataTable</returns>
public DataSet getExactData(int TypeNumID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select 项目ID, 分类号ID, 项目编号, 工程名称, 工程地点, 建设单位, 立项批准单位, 设计单位, 勘察单位, 监理单位,");
strSql.Append(" 立项批准文号, 规划许可证号, 用地规划许可证号, 用地许可证号, 施工许可证号, ");
strSql.Append(" '' as 详细信息,保管期限, 密级, 进馆日期, 移交单位, 档号, 存放位置起始号, 附注 from view城建项目房屋建筑");
strSql.Append(" where 分类号ID = @TypeNumID AND ");
strSql.Append(" (项目ID IN (SELECT 项目ID FROM 城建案卷))");
strSql.Append(" order by 项目编号");
SqlParameter[] parameter =
{
new SqlParameter("@TypeNumID", SqlDbType.Int, 4)
};
parameter[0].Value = TypeNumID;
return(DataSql.Query(strSql.ToString(), parameter));
}
/// <summary>
/// 获取档案类型ID
/// </summary>
/// <param name="DeptID">一级类目ID</param>
/// <returns></returns>
public int getArchiveType(int TopClassID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT 类型ID FROM 一级类目");
strSql.Append(" WHERE 类目ID = " + TopClassID);
object obj = DataSql.GetSingle(strSql.ToString());
if (obj == null)
{
return(0);
}
else
{
return((int)obj);
}
}
/// <summary>
/// 获取类目信息列表
/// </summary>
/// <param name="ClassID">一级ID</param>
/// <param name="storageTime">保管期限</param>
/// <param name="Year">年度</param>
/// <returns>DataTable</returns>
public DataSet getClass(int TopClassID, string Limit, string Year)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select 类目 from GD_类目");
strSql.Append(" where 一级类目ID = @TopClassID and 保管期限 = @Limit and 年度 = @Year ");
SqlParameter[] parameter =
{
new SqlParameter("@TopClassID", SqlDbType.Int, 4),
new SqlParameter("@Limit", SqlDbType.NVarChar, 10),
new SqlParameter("@Year", SqlDbType.NVarChar, 10)
};
parameter[0].Value = TopClassID;
parameter[1].Value = Limit;
parameter[2].Value = Year;
return(DataSql.Query(strSql.ToString(), parameter));
}
protected int ObtenerEscalar(string sql)
{
int n = 0;
dt = new DataTable();
using (DataSql datos = new DataSql(AppGlobal.baseDatos.CadenaConexion))
{
resultado = datos.EjecutarConsulta(sql);
if (resultado.Exitoso && resultado.Tablas.Tables.Count > 0)
{
dt = resultado.Tablas.Tables[0];
n = Convert.ToInt32(dt.Rows[0][0]);
}
}
return n;
}
public DataSet LoginExterno(string strUsuario, bool bCodigo)
{
StringBuilder strConsulta = new StringBuilder();
DataSet dstDatos = new DataSet();
string sIdioma = clsSesiones.getIdioma();
try
{
strConsulta.AppendLine("EXEC SPCONSULTAUSUARIO " + strUsuario);
dstDatos = new DataSql().Select(strConsulta.ToString());
}
catch (Exception ex)
{
ExceptionHandled.Publicar(ex);
return(null);
}
return(dstDatos);
}
/// <summary> 根据单位ID获取单位编号 </summary>
/// <param name="unitID">单位ID</param>
/// <returns>string</returns>
public string getUnitNum(int unitID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select dwbh from UnitManage where ID = @DwID");
SqlParameter[] parameter = { new SqlParameter("@DwID", SqlDbType.Int) };
parameter[0].Value = unitID;
object obj = DataSql.GetSingle(strSql.ToString(), parameter);
if (Object.Equals(obj, null) || Object.Equals(obj, System.DBNull.Value))
{
return(string.Empty);
}
else
{
return(Convert.ToString(obj));
}
}
/// <summary> 获取分类号ID </summary>
/// <param name="projectID">项目ID</param>
/// <returns>int</returns>
private int getFlhID(int projectID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select 分类号ID from 城建项目 where 项目ID = @projectID");
SqlParameter[] parameter = { new SqlParameter("@projectID", SqlDbType.Int) };
parameter[0].Value = projectID;
object obj = DataSql.GetSingle(strSql.ToString(), parameter);
if (Object.Equals(obj, null) || Object.Equals(obj, System.DBNull.Value))
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// Metodo que inserta elregistro del buzon de contacto
/// </summary>
/// <param name="sReferePais"></param>
/// <param name="sIdioma"></param>
/// <returns></returns>
/// /// <remarks>
/// Autor: Juan Camilo Diaz
/// Company: Ssoft Colombia
/// Fecha: 2014-02-26
/// -------------------
/// Control de Cambios
/// -------------------
/// </remarks>
public DataTable insertar_buzon(string intEmpresa, string intProyecto, string intTipo, string intTema, string intEstado,
string strDescripcion, string dtmFecha, string strHora, string strSolicitante, string strEmail, string intPais, string intCiudad, string dtmFechaCierre,
string strHoraCierre, string intUsuario, string strTelSolicitante, string strCiudad)
{
string sIdioma = clsSesiones.getIdioma();
string[] ParametrosSp = new string[18];
int cont = 0;
ParametrosSp[cont++] = intEmpresa;
ParametrosSp[cont++] = sIdioma;
ParametrosSp[cont++] = intProyecto;
ParametrosSp[cont++] = intTipo;
ParametrosSp[cont++] = intTema;
ParametrosSp[cont++] = intEstado;
ParametrosSp[cont++] = strDescripcion;
ParametrosSp[cont++] = dtmFecha;
ParametrosSp[cont++] = strHora;
ParametrosSp[cont++] = strSolicitante;
ParametrosSp[cont++] = strEmail;
ParametrosSp[cont++] = intPais;
ParametrosSp[cont++] = intCiudad;
ParametrosSp[cont++] = dtmFechaCierre;
ParametrosSp[cont++] = strHoraCierre;
ParametrosSp[cont++] = intUsuario;
ParametrosSp[cont++] = strTelSolicitante;
ParametrosSp[cont++] = strCiudad;
DataSql dsConsulta = new DataSql();
//se ejecuta el procedimiento
DataSet dsDatosConsulta = dsConsulta.Select("SPInsertarBuzon", ParametrosSp);
if (dsDatosConsulta != null && dsDatosConsulta.Tables.Count > 0 && dsDatosConsulta.Tables[0].Rows.Count > 0)
{
//se retorna la tabla
return(dsDatosConsulta.Tables[0]);
}
else
{
//si no hay resultados se retorna null
return(null);
}
}
/// <summary>
/// 修改文件
/// </summary>
/// <param name="DocID">文件ID</param>
/// <param name="DocNum">件号</param>
/// <param name="DocTitle">题名</param>
/// <param name="DocZRZ">责任者</param>
/// <param name="DocTime">时间</param>
/// <param name="DocWB">文本</param>
/// <param name="DocPageNum">页数</param>
/// <returns></returns>
public int UpdeteDoc(int DocID, int DocNum, string DocTitle, string DocZRZ, string DocTime, string DocWB, int DocPageNum, string DocBH)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update 城建档案文件 set 件号 = @DocNum");
strSql.Append(",题名 = @DocTitle");
strSql.Append(",责任者 = @DocZRZ");
strSql.Append(",时间 = @DocTime");
strSql.Append(",文本 = @DocWB");
strSql.Append(",页数 = @DocPageNum");
strSql.Append(",文件编号 = @DocBH");
strSql.Append(" where 文件ID = @DocID");
SqlParameter[] parameter =
{
new SqlParameter("@DocNum", SqlDbType.Int, 4),
new SqlParameter("@DocTitle", SqlDbType.NVarChar, 200),
new SqlParameter("@DocZRZ", SqlDbType.NVarChar, 50),
new SqlParameter("@DocTime", SqlDbType.NVarChar, 10),
new SqlParameter("@DocWB", SqlDbType.NVarChar, 4),
new SqlParameter("@DocPageNum", SqlDbType.Int, 4),
new SqlParameter("@DocBH", SqlDbType.NVarChar, 30),
new SqlParameter("@DocID", SqlDbType.Int, 4)
};
parameter[0].Value = DocNum;
parameter[1].Value = DocTitle;
parameter[2].Value = DocZRZ;
parameter[3].Value = DocTime;
parameter[4].Value = DocWB;
parameter[5].Value = DocPageNum;
parameter[6].Value = DocBH;
parameter[7].Value = DocID;
object obj = DataSql.ExecuteSql(strSql.ToString(), parameter);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
///修改角色权限
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public void UpdateRolePermission(int RoleID, SystemPermission.Permission pm)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update 角色权限 set ");
strSql.Append("录入=@录入,");
strSql.Append("检索=@检索,");
strSql.Append("打印=@打印,");
strSql.Append("统计=@统计,");
strSql.Append("非密原件查看=@非密原件查看,");
strSql.Append("涉密原件查看=@涉密原件查看,");
strSql.Append("原件管理=@原件管理,");
strSql.Append("借阅=@借阅,");
strSql.Append("系统管理=@系统管理");
strSql.Append(" where 角色ID=@角色ID and 一级类目ID=@一级类目ID ");
SqlParameter[] parameters =
{
new SqlParameter("@录入", SqlDbType.Bit),
new SqlParameter("@检索", SqlDbType.Bit),
new SqlParameter("@打印", SqlDbType.Bit),
new SqlParameter("@统计", SqlDbType.Bit),
new SqlParameter("@非密原件查看", SqlDbType.Bit),
new SqlParameter("@涉密原件查看", SqlDbType.Bit),
new SqlParameter("@原件管理", SqlDbType.Bit),
new SqlParameter("@借阅", SqlDbType.Bit),
new SqlParameter("@系统管理", SqlDbType.Bit),
new SqlParameter("@角色ID", SqlDbType.Int,4),
new SqlParameter("@一级类目ID", SqlDbType.Int, 4)
};
parameters[0].Value = pm.录入;
parameters[1].Value = pm.检索;
parameters[2].Value = pm.打印;
parameters[3].Value = pm.统计;
parameters[4].Value = pm.非密原件查看;
parameters[5].Value = pm.涉密原件查看;
parameters[6].Value = pm.原件管理;
parameters[7].Value = pm.借阅;
parameters[8].Value = pm.系统管理;
parameters[9].Value = RoleID;
parameters[10].Value = pm.一级类目ID;
DataSql.ExecuteSql(strSql.ToString(), parameters);
}
/// <summary>
/// 项目数据信息获取
/// </summary>
/// <returns>DataTable</returns>
public DataSet getData(int TypeNumID)
{
//项目ID, 分类号ID, 项目编号, 工程名称, 工程地点, 责任者, 文号项, 专业记载, 总卷数, 文字_卷, 图纸_卷, 图纸_张, 底图_张, 照片_张,
// 底片_张, 录音带, 录像带, 光盘, 计算机磁带, 计算机磁盘, 缩微片盘, 缩微片张, 其它, 保管期限, 密级, 进馆日期, 移交单位, 档号, 缩微号,
// 存放位置起始号, 附注
StringBuilder strSql = new StringBuilder();
strSql.Append("select 项目ID, 分类号ID, 项目编号, 工程名称, 工程地点, 责任者, 文号项, 专业记载, ");
strSql.Append(" '' as 详细信息,保管期限, 密级, 进馆日期, 移交单位, 档号, 存放位置起始号, 附注 from view城建项目通用");
strSql.Append(" where 分类号ID = @TypeNumID");
strSql.Append(" order by 项目编号");
SqlParameter[] parameter =
{
new SqlParameter("@TypeNumID", SqlDbType.Int, 4)
};
parameter[0].Value = TypeNumID;
return(DataSql.Query(strSql.ToString(), parameter));
}
/// <summary> 在同一二级类目下是否存在相同的案卷号 </summary>
/// <param name="projectID">项目ID</param>
/// <param name="fileNum">案卷号</param>
/// <returns>bool</returns>
public bool ExistsByFileNum(int projectID, int fileNum)
{
StringBuilder strSql = new StringBuilder();
int flhID = getFlhID(projectID);
strSql.Append("select count(*) ");
strSql.Append("from (view城建分类号新 inner join ");
strSql.Append("城建项目 on view城建分类号新.ID = 城建项目.分类号ID) inner join ");
strSql.Append("城建案卷 on 城建项目.项目ID = 城建案卷.项目ID ");
strSql.Append("where view城建分类号新.ID = @flhID and 城建案卷.案卷号 = @fileNum");
SqlParameter[] parameter =
{
new SqlParameter("@flhID", SqlDbType.Int),
new SqlParameter("@fileNum", SqlDbType.Int)
};
parameter[0].Value = flhID;
parameter[1].Value = fileNum;
return(DataSql.Exists(strSql.ToString(), parameter));
}
/// <summary>
/// 获取有下级信息的一级分类号节点
/// </summary>
/// <param name="TopClassID">一级类目ID</param>
/// <returns>一级分类号节点的结果集</returns>
public DataSet getExactTopTypeNumNodesData(int TopClassID)
{
strSql = new StringBuilder();
strSql.Append("select ID,分类号,类目名称 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");
strSql.Append(" where b.一级类目ID = @TopClassID and b.分类号 is not null and b.类目级别 = 2 and SUBSTRING(b.分类号, 1, 1) = a.分类号 ");
strSql.Append(" and exists (select * from view城建分类号旧 as c");
strSql.Append(" where c.一级类目ID = @TopClassID and c.分类号 is not null and c.类目级别 = 3 and 分类号 like b.分类号 + '%' ");
strSql.Append(" and exists ( select * from view城建案卷旧 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>
/// <param name="topClassID">一级类目ID</param>
/// <param name="typeName">分类号名称</param>
/// <param name="projectNumS">项目编号起始</param>
/// <returns>DataSet</returns>
public DataSet getProjectList(int topClassID, string typeName, string projectNumS)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select 项目编号 from view城建分类号新 inner join ");
strSql.Append("城建项目 on view城建分类号新.ID = 城建项目.分类号ID ");
strSql.Append("where view城建分类号新.一级类目ID = @topClassID and ");
strSql.Append("view城建分类号新.分类号 = @typeName and 城建项目.项目编号>=@projectNumS");
SqlParameter[] parameter =
{
new SqlParameter("@topClassID", SqlDbType.Int),
new SqlParameter("@typeName", SqlDbType.NVarChar,15),
new SqlParameter("@projectNumS", SqlDbType.NVarChar, 20)
};
parameter[0].Value = topClassID;
parameter[1].Value = typeName;
parameter[2].Value = projectNumS;
return(DataSql.Query(strSql.ToString(), parameter));
}
/// <summary>
/// 获取有下级信息的三级分类号数据信息
/// </summary>
/// <param name="TopClassID">一级类目ID</param>
/// <returns>三级分类号节点的结果集</returns>
public DataSet getExactThirdTypeNumData(int TopClassID, string SecondTypeNum)
{
strSql = new StringBuilder();
//strSql.Append("select * from view城建分类号旧");
//strSql.Append(" where 一级类目ID = @TopClassID and 分类号 is not null and 类目级别 = 3 and 分类号 like '" + SecondTypeNum + "%'");
//strSql.Append(" and (ID IN (SELECT 分类号ID FROM view城建案卷旧))");
//strSql.Append(" order by 分类号");
strSql.Append("select * from view城建分类号旧 as c");
strSql.Append(" where c.一级类目ID = @TopClassID and c.分类号 is not null and c.类目级别 = 3 and 分类号 like '" + SecondTypeNum + "%' ");
strSql.Append(" and exists ( select * from view城建案卷旧 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>
/// <returns></returns>
public bool UserDelete(int UserID)
{
//先判断用户是否拥有角色
StringBuilder strSelect = new StringBuilder();
strSelect.Append("SELECT COUNT(*) FROM 用户角色 ");
strSelect.Append(" WHERE 用户ID = " + UserID);
bool cmdresult = DataSql.Exists(strSelect.ToString());
if (!cmdresult)
{
//rows = DataSql.ExecuteSql("delete 用户 where 用户ID = " + UserID);
ArrayList SqlList = new ArrayList();
SqlList.Add("delete from 用户角色 where 用户ID = " + UserID);
SqlList.Add("delete from 用户 where 用户ID = " + UserID);
try
{
DataSql.ExecuteSqlTran(SqlList);
return(true);
}
catch
{
return(false);
}
}
else
{
//rows = 0;
MessageBox.Show("不允许删除拥有角色的用户！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(false);
}
//if (rows > 0)
//{
// return true;
//}
//else
//{
// return false;
//}
}
public DataTable ConReservasProyectos(string sProyecto)
{
string[] ParametrosSp = new string[1];
ParametrosSp[0] = sProyecto;
DataSql dsConsulta = new DataSql();
//se ejecuta el procedimiento
DataSet dsDatosConsulta = dsConsulta.Select("SPConsultaReservasProyecto", ParametrosSp);
if (dsDatosConsulta != null && dsDatosConsulta.Tables.Count > 0 && dsDatosConsulta.Tables[0].Rows.Count > 0)
{
//se retorna la tabla
return(dsDatosConsulta.Tables[0]);
}
else
{
//si no hay resultados se retorna null
return(null);
}
}
/// <summary> 获取节点信息 </summary>
/// <param name="ProjectID">项目ID</param>
/// <returns>DataSet</returns>
public DataSet GetZhNodesData(int ProjectID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT 案卷ID,案卷号 ");
strSql.Append(" FROM 城建案卷 INNER JOIN");
strSql.Append(" 城建项目 ON 城建案卷.项目ID = 城建项目.项目ID");
strSql.Append(" WHERE (城建项目.分类号ID IN");
strSql.Append(" (SELECT 分类号ID");
strSql.Append(" FROM 城建项目");
strSql.Append(" WHERE 项目ID = @projectID)) order by 案卷号");
SqlParameter[] parameters =
{
new SqlParameter("@projectID", SqlDbType.Int, 4)
};
parameters[0].Value = ProjectID;
return(DataSql.Query(strSql.ToString(), parameters));
}
/// <summary>
/// 项目数据信息获取
/// </summary>
/// <returns>DataTable</returns>
public DataSet getData(int TypeNumID)
{
//项目ID, 分类号ID, 项目编号, 工程名称, 工程地点, 建设单位, 立项批准单位, 被征单位, 规划批准单位, 立项批准文号, 规划许可证号,
// 用地规划许可证号, 用地许可证号, 地形图号, 文字_卷, 图纸_张, 光盘, 计算机磁盘, 保管期限, 密级, 进馆日期, 移交单位, 档号, 缩微号,
// 存放位置起始号, 附注
StringBuilder strSql = new StringBuilder();
strSql.Append("select 项目ID, 分类号ID, 项目编号, 工程名称, 工程地点, 建设单位, 立项批准单位, 被征单位, 规划批准单位, ");
strSql.Append(" 立项批准文号, 规划许可证号, 用地规划许可证号, 用地许可证号, 地形图号, ");
strSql.Append(" '' as 详细信息,保管期限, 密级, 进馆日期, 移交单位, 档号, 存放位置起始号, 附注 from view城建项目建设用地规划管理");
strSql.Append(" where 分类号ID = @TypeNumID");
strSql.Append(" order by 项目编号");
SqlParameter[] parameter =
{
new SqlParameter("@TypeNumID", SqlDbType.Int, 4)
};
parameter[0].Value = TypeNumID;
return(DataSql.Query(strSql.ToString(), parameter));
}
///// <summary>
///// 获得有下级信息的节点数据
///// </summary>
//public DataSet GetExactNodesData(int 项目ID)
//{
// StringBuilder strSql = new StringBuilder();
// strSql.Append("select 案卷ID,案卷号 ");
// strSql.Append(" FROM 城建案卷 ");
// strSql.Append(" where 项目ID=@项目ID ");
// strSql.Append(" and (案卷ID in (select 案卷ID from 城建文件)) order by 案卷号");
// SqlParameter[] parameters = {
// new SqlParameter("@项目ID", SqlDbType.Int,4)};
// parameters[0].Value = 项目ID;
// return DataSql.Query(strSql.ToString(), parameters);
//}
///// <summary>
///// 获得有下级信息的节点数据
///// </summary>
//public DataSet GetZhExactNodesData(int 项目ID)
//{
// StringBuilder strSql = new StringBuilder();
// //strSql.Append("select 案卷ID,案卷号 ");
// //strSql.Append(" FROM 城建案卷 ");
// //strSql.Append(" where 项目ID=@项目ID ");
// //strSql.Append(" and (案卷ID in (select 案卷ID from 城建文件)) order by 案卷号");
// strSql.Append("SELECT 案卷ID,案卷号 ");
// strSql.Append(" FROM 城建案卷 INNER JOIN");
// strSql.Append(" 城建项目 ON 城建案卷.项目ID = 城建项目.项目ID");
// strSql.Append(" WHERE (城建项目.分类号ID IN");
// strSql.Append(" (SELECT 分类号ID");
// strSql.Append(" FROM 城建项目");
// strSql.Append(" WHERE 项目ID = @项目ID)");
// strSql.Append(" and (案卷ID in (select 案卷ID from 城建文件))) order by 案卷号");
// SqlParameter[] parameters = {
// new SqlParameter("@项目ID", SqlDbType.Int,4)};
// parameters[0].Value = 项目ID;
// return DataSql.Query(strSql.ToString(), parameters);
//}
/// <summary> 获得数据列表 </summary>
/// <param name="ProjectID">项目ID</param>
/// <returns>DataSet</returns>
public DataSet GetData(int ProjectID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select 案卷ID,项目ID,案卷号,总登记号,题名,编制单位,编制时间,载体类型,数量,单位,规格,归档时间, ");
strSql.Append("主题词,存放地址,本工程卷数,本卷卷次,接收人,立卷人,审核人,审核时间,录入人,");
if (UIPublicClass.PublicClass.IsInputForPageIndex)
{
strSql.Append("文件总页数,");
}
strSql.Append("附注,UserID,InTime ");
strSql.Append(" FROM 城建案卷 ");
strSql.Append(" where 项目ID=@projectID order by 案卷号");
SqlParameter[] parameters =
{
new SqlParameter("@projectID", SqlDbType.Int, 4)
};
parameters[0].Value = ProjectID;
return(DataSql.Query(strSql.ToString(), parameters));
}
/// <summary> 是否存在相同的题名 </summary>
/// <param name="ProjectID">项目ID</param>
/// <param name="titleText">题名</param>
/// <returns>bool</returns>
public bool ExistsByTitle(int ProjectID, string titleText)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(*) from 城建案卷");
strSql.Append(" where 题名=@题名 and 项目ID in ");
strSql.Append(" (select 项目ID from 城建项目 ");
strSql.Append(" where 工程名称 in ");
strSql.Append(" (select 工程名称 from 城建项目 where 项目ID = @项目ID) )");
SqlParameter[] parameters =
{
new SqlParameter("@题名", SqlDbType.NVarChar, 200),
new SqlParameter("@项目ID", SqlDbType.Int, 4)
};
parameters[0].Value = titleText;
parameters[1].Value = ProjectID;
return(DataSql.Exists(strSql.ToString(), parameters));
}
/// <summary>
/// 修改一级类目的档案类型
/// </summary>
/// <param name="类目ID"></param>
/// <param name="ArchiveType"></param>
/// <returns></returns>
public bool UpdateArchiveType(int 类目ID, string ArchiveType)
{
int TypeID = getArchiveTypeID(ArchiveType);
StringBuilder strSql = new StringBuilder();
strSql.Append("update 一级类目 set 档案类型 = " + TypeID);
strSql.Append(" where 类目ID = " + 类目ID);
int rows = DataSql.ExecuteSql(strSql.ToString());
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 案卷目录数据信息获取
/// </summary>
/// <param name="TopClassID">类目ID</param>
/// <returns>DataSet</returns>
public DataSet getCheckData(int TopClassID, int UserID)
{
StringBuilder sql = new StringBuilder();
sql.Append("select distinct c.保管期限,c.一级类目ID from (GD_类目 as c INNER JOIN");
sql.Append(" SYS_科室 AS t ON c.科室代号 = t.科室代号) INNER JOIN");
sql.Append(" 角色目录权限 AS p ON t.ID = p.科室ID");
sql.Append(" where c.一级类目ID = @TopClassID and p.查看 = 1");
sql.Append(" and p.角色ID in (select 角色ID from 用户角色 where 用户ID = @UserID)");
sql.Append(" order by 保管期限");
SqlParameter[] parameter =
{
new SqlParameter("@TopClassID", SqlDbType.Int, 4),
new SqlParameter("@UserID", SqlDbType.Int, 4)
};
parameter[0].Value = TopClassID;
parameter[1].Value = UserID;
return(DataSql.Query(sql.ToString(), parameter));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int AddZYJZ(int 项目ID,
string 用地分类,
string 征拨分类,
string 原土地分类,
string 批准时间,
string 用地面积)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into 城建专业记载建设用地规划管理(");
strSql.Append("项目ID,用地分类,征拨分类,原土地分类,批准时间,用地面积)");
strSql.Append(" values (");
strSql.Append("@项目ID,@用地分类,@征拨分类,@原土地分类,@批准时间,@用地面积)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@项目ID", SqlDbType.Int, 4),
new SqlParameter("@用地分类", SqlDbType.NVarChar, 10),
new SqlParameter("@征拨分类", SqlDbType.NVarChar, 10),
new SqlParameter("@原土地分类", SqlDbType.NVarChar, 10),
new SqlParameter("@批准时间", SqlDbType.NVarChar, 10),
new SqlParameter("@用地面积", SqlDbType.NVarChar, 10)
};
parameters[0].Value = 项目ID;
parameters[1].Value = 用地分类;
parameters[2].Value = 征拨分类;
parameters[3].Value = 原土地分类;
parameters[4].Value = 批准时间;
parameters[5].Value = 用地面积;
object obj = DataSql.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 获取全宗名称
/// </summary>
/// <param name="ArchiveType"></param>
/// <returns></returns>
public string getDeptName(int 全宗ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select 全宗名称 from 全宗 ");
strSql.Append(" where 全宗ID = " + 全宗ID);
object obj = DataSql.GetSingle(strSql.ToString());
string cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = "";
}
else
{
cmdresult = obj.ToString();
}
return(cmdresult);
}
/// <summary>
/// 获取档案类型ID
/// </summary>
/// <param name="ArchiveType">档案类型名称</param>
/// <returns></returns>
public int getArchiveTypeID(string ArchiveTypeName)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select 类型ID from 档案类型 ");
strSql.Append(" where 档案类型 = '" + ArchiveTypeName + "'");
object obj = DataSql.GetSingle(strSql.ToString());
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
return(cmdresult);
}
/// <summary>
/// 获取案卷的信息
/// </summary>
/// <param name="FileID"></param>
/// <returns></returns>
public DataSet getFileInfo(int FileID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT ZP_册.册ID,");
if (DataAccess.DataSql.ConnectType == DB.DBConnectType.SqlServer)
{
strSql.Append("全宗.全宗号 + N'/' + 一级类目.类目名称 + N'/' + ZP_册.册号 AS 档号, ");
}
else
{
strSql.Append("全宗.全宗号 + \"/\" + 一级类目.类目名称 + \"/\" + ZP_册.册号 AS 档号, ");
}
strSql.Append("ZP_册.说明, ZP_册.保管期限, ZP_册.立册时间");
strSql.Append(" FROM (ZP_册 INNER JOIN ");
strSql.Append(" 一级类目 ON ZP_册.一级类目ID = 一级类目.类目ID) INNER JOIN ");
strSql.Append(" 全宗 ON 一级类目.全宗ID = 全宗.全宗ID ");
strSql.Append(" where ZP_册.册ID = " + FileID);
return(DataSql.Query(strSql.ToString()));
}
/// <summary>
/// 项目数据信息获取
/// </summary>
/// <returns>DataTable</returns>
public DataSet getData(int TypeNumID)
{
//项目ID, 分类号ID, 项目编号, 工程名称, 工程地点, 建设单位, 立项批准单位, 设计单位, 勘察单位, 监理单位, 立项批准文号,
// 规划许可证号, 用地规划许可证号, 用地许可证号, 施工许可证号, 总卷数, 文字_卷, 图纸_卷, 图纸_张, 底图_张, 照片_张, 底片_张,
// 录音带, 录像带, 光盘, 计算机磁带, 计算机磁盘, 缩微片盘, 缩微片张, 其它, 保管期限, 密级, 进馆日期, 移交单位, 档号, 缩微号,
// 存放位置起始号, 附注
StringBuilder strSql = new StringBuilder();
strSql.Append("select 项目ID, 分类号ID, 项目编号, 工程名称, 工程地点, 建设单位, 立项批准单位, 设计单位, 勘察单位, 监理单位,");
strSql.Append(" 立项批准文号, 规划许可证号, 用地规划许可证号, 用地许可证号, 施工许可证号, ");
strSql.Append(" '' as 详细信息,保管期限, 密级, 进馆日期, 移交单位, 档号, 存放位置起始号, 附注 from view城建项目市政基础设施");
strSql.Append(" where 分类号ID = @TypeNumID");
strSql.Append(" order by 项目编号");
SqlParameter[] parameter =
{
new SqlParameter("@TypeNumID", SqlDbType.Int, 4)
};
parameter[0].Value = TypeNumID;
return(DataSql.Query(strSql.ToString(), parameter));
}
