/// <summary>
/// 插入掘进巷道数据
/// </summary>
/// <returns></returns>
public static bool insertTunnelHC(TunnelHCEntityNew tunnelHCEntity)
{
ManageDataBase db = new ManageDataBase(DATABASE_TYPE.MiningSchedulingDB);
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO " + TunnelHCDbConstNames.TABLE_NAME + " (" + TunnelHCDbConstNames.TUNNEL_ID1 + "," + TunnelHCDbConstNames.TUNNEL_ID2 + "," + TunnelHCDbConstNames.TUNNEL_ID3 + "," + TunnelHCDbConstNames.TUNNEL_ID + "," + TunnelHCDbConstNames.TEAM_NAME_ID + "," + TunnelHCDbConstNames.START_DATE + "," + TunnelHCDbConstNames.IS_FINISH + "," + TunnelHCDbConstNames.STOP_DATE + "," + TunnelHCDbConstNames.WORK_STYLE + "," + TunnelHCDbConstNames.WORK_TIME + ") VALUES(");
sb.Append(tunnelHCEntity.TunnelID_ZY + ",");
sb.Append(tunnelHCEntity.TunnelID_FY + ",");
sb.Append(tunnelHCEntity.TunnelID_KQY + ",'");
sb.Append(tunnelHCEntity.TunnelID + "','");
sb.Append(tunnelHCEntity.TeamNameID + "','");
sb.Append(tunnelHCEntity.StartDate + "',");
sb.Append(tunnelHCEntity.IsFinish + ",'");
sb.Append(tunnelHCEntity.StopDate + "','");
sb.Append(tunnelHCEntity.WorkStyle + "','");
sb.Append(tunnelHCEntity.WorkTime + "')");
bool bResult = db.OperateDB(sb.ToString());
setTunnelAsHC(tunnelHCEntity.TunnelID_ZY);
setTunnelAsHC(tunnelHCEntity.TunnelID_FY);
setTunnelAsHC(tunnelHCEntity.TunnelID_KQY);
//其它巷道
string[] sArray = new string[10];
if (tunnelHCEntity.TunnelID != null)
{
sArray = tunnelHCEntity.TunnelID.Split(',');
}
foreach (string i in sArray)
{
int iTunnelID = Convert.ToInt16(i);
setTunnelAsHC(iTunnelID);
}
return(bResult);
}
/// <summary>
/// 返回某条回采巷道信息
/// </summary>
/// <param name="tunnelHCID">回采巷道ID</param>
/// <returns>回采巷道实体</returns>
public static TunnelHCEntity selectTunnelHC(int tunnelHCID)
{
ManageDataBase db = new ManageDataBase(DATABASE_TYPE.MiningSchedulingDB);
string sql = "SELECT * FROM " + TunnelHCDbConstNames.TABLE_NAME + " WHERE " + TunnelHCDbConstNames.ID + " = " + tunnelHCID;
DataSet ds = db.ReturnDS(sql);
TunnelHCEntity tunnelHCEntity = new TunnelHCEntity();
if (ds.Tables[0].Rows.Count > 0)
{
try
{
tunnelHCEntity.ID = (int)ds.Tables[0].Rows[0][TunnelHCDbConstNames.ID];
tunnelHCEntity.TunnelID_ZY = (int)ds.Tables[0].Rows[0][TunnelHCDbConstNames.TUNNEL_ID1];
tunnelHCEntity.TunnelID_FY = (int)ds.Tables[0].Rows[0][TunnelHCDbConstNames.TUNNEL_ID2];
tunnelHCEntity.TunnelID_KQY = (int)ds.Tables[0].Rows[0][TunnelHCDbConstNames.TUNNEL_ID3];
tunnelHCEntity.TeamNameID = (int)ds.Tables[0].Rows[0][TunnelHCDbConstNames.TEAM_NAME_ID];
tunnelHCEntity.StartDate = Convert.ToDateTime(ds.Tables[0].Rows[0][TunnelHCDbConstNames.START_DATE]);
tunnelHCEntity.IsFinish = (int)ds.Tables[0].Rows[0][TunnelHCDbConstNames.IS_FINISH];
tunnelHCEntity.StopDate = Convert.ToDateTime(ds.Tables[0].Rows[0][TunnelHCDbConstNames.STOP_DATE]);
tunnelHCEntity.WorkStyle = ds.Tables[0].Rows[0][TunnelHCDbConstNames.WORK_STYLE].ToString();
tunnelHCEntity.WorkTime = ds.Tables[0].Rows[0][TunnelHCDbConstNames.WORK_TIME].ToString();
}
catch
{
return(null);
}
}
else
{
return(null);
}
return(tunnelHCEntity);
}
/// <summary>
/// 验证数据库中是否存在表 tableName
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public static bool ExistTableInDatabase(string tableName)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("if objectproperty(object_id(");
strSql.Append("'" + tableName + "'");
strSql.Append("),'IsUserTable') = 1 ");
strSql.Append("select 1 else select 0");
ManageDataBase database = new ManageDataBase(LibDatabase.DATABASE_TYPE.WarningManagementDB);
DataSet ds = database.ReturnDS(strSql.ToString());
if (ds != null)
{
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
string value = dt.Rows[0][0].ToString();
if (value == "1")
{
return(true);
}
else
{
return(false);
}
}
}
return(false);
}
/// <summary>
/// 通过部门名称修改部门信息记录
/// </summary>
/// <param name="ent">部门信息实体</param>
/// <param name="oldName">部门旧名称</param>
/// <returns></returns>
public static bool UpdateDepartmentInfomationDatabase(Department
ent, string oldName)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("UPDATE ");
strSql.Append(DepartmentInformationManagemetDbConstNames.TABLE_NAME);
strSql.Append(" SET ");
strSql.Append(DepartmentInformationManagemetDbConstNames.DEPT_NAME
+ " = ");
strSql.Append("'" + ent.Name + "',");
strSql.Append(DepartmentInformationManagemetDbConstNames.DEPT_TEL
+ " = ");
strSql.Append("'" + ent.Tel + "',");
strSql.Append(DepartmentInformationManagemetDbConstNames.DEPT_EMAIL
+ " = ");
strSql.Append("'" + ent.Email + "',");
strSql.Append(DepartmentInformationManagemetDbConstNames.DEPT_STAFF_COUNT
+ " = ");
strSql.Append("'" + ent.Staff + "',");
strSql.Append(DepartmentInformationManagemetDbConstNames.DEPT_REMARKS
+ " = ");
strSql.Append("'" + ent.Remark + "'");
strSql.Append(" WHERE ");
strSql.Append(DepartmentInformationManagemetDbConstNames.DEPT_NAME
+ " = ");
strSql.Append("'" + oldName + "'");
ManageDataBase database = new
ManageDataBase(DATABASE_TYPE.WarningManagementDB);
return(database.OperateDB(strSql.ToString()));
}
/// <summary>
/// 将规则编码和参数信息更新至巷道信息表
/// 注意：当传入的参数为null时，说明该巷道未绑定规则编码，此时仍需更新数据库
/// </summary>
/// <param name="tunnelID">巷道ID</param>
/// <param name="info">巷道绑定的所有规则ID和参数信息</param>
/// <returns>更新成功返回True</returns>
public static bool UpdateRuleIdsAndParams2TunnelTable(int tunnelID,
RuleInfo[] info)
{
string ruleIds = "";
string warningParams = "";
if (info != null)
{
int n = info.Length;
for (int i = 0; i < n; i++)
{
RuleInfo cvtInfo =
PreWarningRulesBLL.ConvertOneRuleCodeAndParamInfo2DBString(info[i]);
ruleIds += cvtInfo.RuleCodesStr;
warningParams += cvtInfo.ParamsInfoStr;
}
}
string sql = "UPDATE " +
TunnelInfoDbConstNames.TABLE_NAME + " SET " +
TunnelInfoDbConstNames.RULE_IDS + "='" + ruleIds + "'," +
TunnelInfoDbConstNames.PRE_WARNING_PARAMS + "='" +
warningParams + "' WHERE " +
TunnelInfoDbConstNames.ID + "=" + tunnelID;
ManageDataBase db = new
ManageDataBase(DATABASE_TYPE.GeologyMeasureDB);
return(db.OperateDB(sql));
}
/// <summary>
/// 获取巷道绑定的所有规则编码和参数
/// </summary>
/// <param name="preWarningParamsInDB">数据库中保存的预警规则参数</param>
/// <returns>规则参数与编码单元</returns>
/// <summary>
public static RuleInfo[] GetTunnelBindingRuleIdsAndParams(int
tunnelID)
{
//获取巷道绑定的规则编码字符串和预警参数字符串
string sql = "SELECT " +
TunnelInfoDbConstNames.RULE_IDS + ", " +
TunnelInfoDbConstNames.PRE_WARNING_PARAMS + " FROM " +
TunnelInfoDbConstNames.TABLE_NAME + " WHERE " +
TunnelInfoDbConstNames.ID + "=" + tunnelID;
ManageDataBase db = new
ManageDataBase(DATABASE_TYPE.GeologyMeasureDB);
DataSet ds = db.ReturnDS(sql);
string allRuleIds = "";
if (ds.Tables[0].Rows.Count < 1)
{
//Alert.alert("数据库中无对应巷道！");
return(null);
}
allRuleIds =
ds.Tables[0].Rows[0][TunnelInfoDbConstNames.RULE_IDS].ToString();
string allParams =
ds.Tables[0].Rows[0][TunnelInfoDbConstNames.PRE_WARNING_PARAMS].ToString();
if (allRuleIds == "")//未绑定规则编码
{
return(null);
}
RuleInfo[] ret = ParseRuleIdsAndParams(allRuleIds, allParams);
return(ret);
}
selectDefaultTunnel(string tableName)
{
string sql = "SELECT * FROM " + TunnelDefaultSelectDbConstNames.TABLE_NAME +
" WHERE " + TunnelDefaultSelectDbConstNames.TABLE_NAME_USE + " = '" +
tableName + "'";
ManageDataBase db = new
ManageDataBase(LibDatabase.DATABASE_TYPE.GeologyMeasureDB);
DataSet ds = db.ReturnDS(sql);
LibEntity.TunnelDefaultSelect tunnelDefaultSelectEntity = new
LibEntity.TunnelDefaultSelect();
if (ds.Tables[0].Rows.Count > 0)
{
tunnelDefaultSelectEntity.TableName =
ds.Tables[0].Rows[0][TunnelDefaultSelectDbConstNames.TABLE_NAME_USE].ToString();
tunnelDefaultSelectEntity.MineID =
Convert.ToInt32(ds.Tables[0].Rows[0][TunnelDefaultSelectDbConstNames.MINE_ID].ToString());
tunnelDefaultSelectEntity.HorizontalID =
Convert.ToInt32(ds.Tables[0].Rows[0][TunnelDefaultSelectDbConstNames.HORIZONTAL_ID].ToString());
tunnelDefaultSelectEntity.MiningAreaID =
Convert.ToInt32(ds.Tables[0].Rows[0][TunnelDefaultSelectDbConstNames.MINING_AREA_ID].ToString());
tunnelDefaultSelectEntity.WorkingFaceID =
Convert.ToInt32(ds.Tables[0].Rows[0][TunnelDefaultSelectDbConstNames.WORKING_FACE_ID].ToString());
return(tunnelDefaultSelectEntity);
}
return(null);
}
private static TunnelHChuan selectTunnelHChuan(int tunnelHChuanID)
{
ManageDataBase db = new
ManageDataBase(DATABASE_TYPE.MiningSchedulingDB);
string sql = "SELECT * FROM " +
TunnelHChuanDbConstNames.TABLE_NAME + " WHERE " +
TunnelHChuanDbConstNames.ID + " = " + tunnelHChuanID;
DataSet ds = db.ReturnDS(sql);
TunnelHChuan tunnelHChuanEntity = new TunnelHChuan();
if (ds.Tables[0].Rows.Count > 0)
{
try
{
tunnelHChuanEntity.Id =
(int)ds.Tables[0].Rows[0][TunnelHChuanDbConstNames.ID];
tunnelHChuanEntity.TunnelId1 =
(int)ds.Tables[0].Rows[0][TunnelHChuanDbConstNames.TUNNEL_ID1];
tunnelHChuanEntity.TunnelId2 =
(int)ds.Tables[0].Rows[0][TunnelHChuanDbConstNames.TUNNEL_ID2];
tunnelHChuanEntity.X1 =
(double)ds.Tables[0].Rows[0][TunnelHChuanDbConstNames.TUNNEL_X1];
tunnelHChuanEntity.Y1 =
(double)ds.Tables[0].Rows[0][TunnelHChuanDbConstNames.TUNNEL_Y1];
tunnelHChuanEntity.Z1 =
(double)ds.Tables[0].Rows[0][TunnelHChuanDbConstNames.TUNNEL_Z1];
tunnelHChuanEntity.X2 =
(double)ds.Tables[0].Rows[0][TunnelHChuanDbConstNames.TUNNEL_X2];
tunnelHChuanEntity.Y2 =
(double)ds.Tables[0].Rows[0][TunnelHChuanDbConstNames.TUNNEL_Y2];
tunnelHChuanEntity.Z2 =
(double)ds.Tables[0].Rows[0][TunnelHChuanDbConstNames.TUNNEL_Z2];
tunnelHChuanEntity.Azimuth =
(double)ds.Tables[0].Rows[0][TunnelHChuanDbConstNames.TUNNEL_AZIMUTH];
tunnelHChuanEntity.Team.TeamId =
(int)ds.Tables[0].Rows[0][TunnelHChuanDbConstNames.TEAM_NAME_ID];
tunnelHChuanEntity.StartDate =
Convert.ToDateTime(ds.Tables[0].Rows[0][TunnelHChuanDbConstNames.START_DATE]);
tunnelHChuanEntity.IsFinish =
(int)ds.Tables[0].Rows[0][TunnelHChuanDbConstNames.IS_FINISH];
tunnelHChuanEntity.StopDate =
Convert.ToDateTime(ds.Tables[0].Rows[0][TunnelHChuanDbConstNames.STOP_DATE]);
tunnelHChuanEntity.WorkStyle =
ds.Tables[0].Rows[0][TunnelHChuanDbConstNames.WORK_STYLE].ToString();
tunnelHChuanEntity.WorkTime =
ds.Tables[0].Rows[0][TunnelHChuanDbConstNames.WORK_TIME].ToString();
tunnelHChuanEntity.State =
ds.Tables[0].Rows[0][TunnelHChuanDbConstNames.TUNNEL_STATE].ToString();
}
catch
{
return(null);
}
}
else
{
return(null);
}
return(tunnelHChuanEntity);
}
/// <summary>
/// 通过用户ID查询用户电话号码
/// </summary>
/// <param name="id">选择的用户ID</param>
/// <returns></returns>
public static string ReturnPhoneNumber(int id)
{
var manaDB = new ManageDataBase(DATABASE_TYPE.GasEmissionDB);
var strBuilder = new StringBuilder();
strBuilder.Append("SELECT " + UserInformationDetailsManagementDbConstNames.USER_PHONENUMBER);
strBuilder.Append(" FROM " + UserInformationDetailsManagementDbConstNames.TABLE_NAME);
strBuilder.Append(" WHERE " + UserInformationDetailsManagementDbConstNames.ID + "=" + id);
var ds = manaDB.ReturnDS(strBuilder.ToString());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
return(ds.Tables[0].Rows[0][0].ToString());
}
return("");
//if (double.TryParse(ds.Tables[0].Rows[0][0].ToString(), out douPhoneNumber) == false)
//{
// return false;
//}
//else
//{
// return true;
//}
//if(ds.count)
}
/// <summary>
/// <工作面>信息登录
/// </summary>
/// <param name="workingFaceEntity"><工作面>实体</param>
/// <returns>成功与否：true，false</returns>
public static bool insertWorkingFaceInfo(WorkingFaceEntityNew workingFaceEntity)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append("INSERT INTO " + WorkingFaceDbConstNamesNew.TABLE_NAME);
sqlStr.Append(" (" + WorkingFaceDbConstNamesNew.WORKINGFACE_NAME);
sqlStr.Append(", " + WorkingFaceDbConstNamesNew.MININGAREA_ID);
sqlStr.Append(", " + WorkingFaceDbConstNamesNew.COORDINATE_X);
sqlStr.Append(", " + WorkingFaceDbConstNamesNew.COORDINATE_Y);
sqlStr.Append(", " + WorkingFaceDbConstNamesNew.COORDINATE_Z);
sqlStr.Append(", " + WorkingFaceDbConstNamesNew.BID);
sqlStr.Append(" )");
sqlStr.Append(" VALUES (");
sqlStr.Append(" '" + workingFaceEntity.WorkingFaceName + "'");
sqlStr.Append(", '" + workingFaceEntity.MiningareaId + "'");
sqlStr.Append(", '" + workingFaceEntity.CoordinateX + "'");
sqlStr.Append(", '" + workingFaceEntity.CoordinateY + "'");
sqlStr.Append(", '" + workingFaceEntity.CoordinateZ + "'");
sqlStr.Append(", '" + workingFaceEntity.BID + "'");
sqlStr.Append(" )");
ManageDataBase db = new ManageDataBase(DATABASE_TYPE.GeologyMeasureDB);
bool bResult = db.OperateDB(sqlStr.ToString());
return(bResult);
}
selectWorkTimeNameByWorkTimeGroupIdAndSysTime(int workTimeGroupId,
string sysDateTime)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT " +
MineDataSimpleDbConstNames.WORK_TIME_NAME);
strSql.Append(" FROM " +
MineDataSimpleDbConstNames.TABLE_NAME);
strSql.Append(" WHERE ");
strSql.Append(MineDataSimpleDbConstNames.WORK_TIME_GROUP_ID +
" = " + workTimeGroupId);
strSql.Append(" AND ");
strSql.Append("'" + sysDateTime + "'" + " <= " +
MineDataSimpleDbConstNames.WORK_TIME_TO);
strSql.Append(" AND ");
strSql.Append("'" + sysDateTime + "'" + " >= " +
MineDataSimpleDbConstNames.WORK_TIME_FROM);
ManageDataBase db = new
ManageDataBase(DATABASE_TYPE.GeologyMeasureDB);
DataSet ds = db.ReturnDS(strSql.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
return(ds.Tables[0].Rows[0][0].ToString());
}
else
{
return(null);
}
}
//查找重复用户名,查找到时，返回true
public static bool FindTheSameLoginName(string newLoginName)
{
//DataTable dt = database.ReturnDS("T_USER_INFO_MANAGEMENT where USER_LOGIN_NAME = '" + name + "'").Tables[0];
bool returnValue = false;
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from ");
strSql.Append(UserInformationManagementDbConstNames.TABLE_NAME);
strSql.Append(" where ");
strSql.Append(UserInformationManagementDbConstNames.USER_LOGIN_NAME);
strSql.Append(" = ");
strSql.Append("'" +
LibEncryptDecrypt.DWEncryptDecryptClass.EncryptString(newLoginName)
+ "'");
ManageDataBase database = new
ManageDataBase(DATABASE_TYPE.WarningManagementDB);
DataTable dt = database.ReturnDS(strSql.ToString()).Tables[0];
if (dt != null)
{
if (dt.Rows.Count > 0)
{
returnValue = true;
}
}
return(returnValue);
}
/// <summary>
/// 查找重复用户名,查找到时，返回true
/// </summary>
/// <param name="newGroupName"></param>
/// <returns></returns>
public static bool FindTheSameGroupName(string newGroupName)
{
bool returnValue = false;
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM ");
strSql.Append(UserGroupInformationMangementDbConstNames.TABLE_NAME);
strSql.Append(" WHERE ");
strSql.Append(UserGroupInformationMangementDbConstNames.USER_GROUP_NAME);
strSql.Append(" = ");
strSql.Append("'" + newGroupName + "'");
ManageDataBase database = new
ManageDataBase(DATABASE_TYPE.WarningManagementDB);
DataTable dt = database.ReturnDS(strSql.ToString()).Tables[0];
if (dt != null)
{
if (dt.Rows.Count > 0)
{
returnValue = true;
}
}
return(returnValue);
}
/// <summary>
/// 修改掘进巷道数据
/// </summary>
/// <returns></returns>
public static bool updateTunnelHChuan(TunnelHChuanEntity tunnelHChuanEntity)
{
clearTunnelTypeOfHChuan(tunnelHChuanEntity.ID);
ManageDataBase db = new ManageDataBase(DATABASE_TYPE.MiningSchedulingDB);
StringBuilder sb = new StringBuilder();
sb.Append("UPDATE " + TunnelHChuanDbConstNames.TABLE_NAME + " SET " + TunnelHChuanDbConstNames.TUNNEL_ID1 + " = ");
sb.Append(tunnelHChuanEntity.TunnelID1 + "," + TunnelHChuanDbConstNames.TUNNEL_ID2 + " = ");
sb.Append(tunnelHChuanEntity.TunnelID2 + "," + TunnelHChuanDbConstNames.TUNNEL_X1 + " = ");
sb.Append(tunnelHChuanEntity.X_1 + "," + TunnelHChuanDbConstNames.TUNNEL_Y1 + " = ");
sb.Append(tunnelHChuanEntity.Y_1 + "," + TunnelHChuanDbConstNames.TUNNEL_Z1 + " = ");
sb.Append(tunnelHChuanEntity.Z_1 + "," + TunnelHChuanDbConstNames.TUNNEL_X2 + " = ");
sb.Append(tunnelHChuanEntity.X_2 + "," + TunnelHChuanDbConstNames.TUNNEL_Y2 + " = ");
sb.Append(tunnelHChuanEntity.Y_2 + "," + TunnelHChuanDbConstNames.TUNNEL_Z2 + " = ");
sb.Append(tunnelHChuanEntity.Z_2 + "," + TunnelHChuanDbConstNames.TUNNEL_AZIMUTH + " = ");
sb.Append(tunnelHChuanEntity.Azimuth + "," + TunnelHChuanDbConstNames.TEAM_NAME_ID + " = ");
sb.Append(tunnelHChuanEntity.TeamNameID + "," + TunnelHChuanDbConstNames.START_DATE + "='");
sb.Append(tunnelHChuanEntity.StartDate + "'," + TunnelHChuanDbConstNames.IS_FINISH + "=");
sb.Append(tunnelHChuanEntity.IsFinish + "," + TunnelHChuanDbConstNames.STOP_DATE + "='");
sb.Append(tunnelHChuanEntity.StopDate + "'," + TunnelHChuanDbConstNames.WORK_STYLE + "='");
sb.Append(tunnelHChuanEntity.WorkStyle + "'," + TunnelHChuanDbConstNames.WORK_TIME + "='");
sb.Append(tunnelHChuanEntity.WorkTime + "'," + TunnelHChuanDbConstNames.TUNNEL_STATE + " = '");
sb.Append(tunnelHChuanEntity.State + "' WHERE " + TunnelHChuanDbConstNames.ID + "=");
sb.Append(tunnelHChuanEntity.ID);
bool bResult = db.OperateDB(sb.ToString());
//setTunnelAsHChuan(tunnelHChuanEntity.TunnelID1);
//setTunnelAsHChuan(tunnelHChuanEntity.TunnelID2);
return(bResult);
}
GetUserLoginInformationByID(int id)
{
UserInformationDetails ent = null;
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM ");
strSql.Append(UserInformationDetailsManagementDbConstNames.TABLE_NAME);
strSql.Append(" WHERE ");
strSql.Append(UserInformationDetailsManagementDbConstNames.ID);
strSql.Append(" = ");
strSql.Append(id);
ManageDataBase database = new
ManageDataBase(DATABASE_TYPE.WarningManagementDB);
DataTable dt = database.ReturnDS(strSql.ToString()).Tables[0];
if (dt != null)
{
int n = dt.Rows.Count;
for (int i = 0; i < n; i++)
{
ent = new UserInformationDetails();
ent.Id = (int)dt.Rows[i][0];
ent.Name = dt.Rows[i][1].ToString();
ent.PhoneNumber = dt.Rows[i][2].ToString();
ent.TelePhoneNumber = dt.Rows[i][3].ToString();
ent.Email = dt.Rows[i][4].ToString();
ent.Depratment = dt.Rows[i][5].ToString();
ent.Position = dt.Rows[i][6].ToString();
ent.Remarks = dt.Rows[i][7].ToString();
ent.IsInform = Convert.ToInt16(dt.Rows[i][8]);
}
}
return(ent);
}
/// <summary>
/// 获取当前预警数据表中所有预警数据（不考虑巷道ID、时间等约束条件）。
/// </summary>
/// <param name="dataTableName"></param>
/// <param name="tunnelID"></param>
/// <param name="minTime"></param>
/// <param name="maxTime"></param>
/// <returns></returns>
DataSet GetPreWarningDatas(string dataTableName)
{
string sql = "SELECT * FROM " + dataTableName;
ManageDataBase db = new ManageDataBase(DATABASE_TYPE.WarningManagementDB);
return(db.ReturnDS(sql));
}
public static List <String> GetWarningIdListWithTunnelId(string
tunnelId)
{
List <String> results = new List <string>();
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM ");
strSql.Append(PreWarningResultViewDbConstNames.VIEW_NAME);
strSql.Append(" WHERE ");
strSql.Append(PreWarningResultDBConstNames.WARNING_RESULT);
strSql.Append(" < 2 ");
strSql.Append(" AND " +
PreWarningResultDBConstNames.HANDLE_STATUS + " < 3"); // 3指 HANDLED
strSql.Append(" AND WARNING_STATUS = 1");
strSql.Append(" AND ");
strSql.Append(TunnelInfoDbConstNames.ID + " = '" + tunnelId +
"'");
ManageDataBase db = new
ManageDataBase(DATABASE_TYPE.WarningManagementDB);
DataTable dt = db.ReturnDS(strSql.ToString()).Tables[0];
for (int i = 0; i < dt.Rows.Count; i++)
{
results.Add(dt.Rows[i][PreWarningResultDBConstNames.ID].ToString());
}
return(results);
}
/// <summary>
/// 获取字段名对应的所有规则编码(查询当前预警数据表对应的预警数据字段与规则编码关系表)
/// 注意：各个绑定表中字段名称要一致！
/// </summary>
/// <param name="fieldName">字段名</param>
/// <returns>注：无数据或出错返回null</returns>
string[] GetRuleCodesByFieldName(string fieldName, string bindingTableName)
{
string[] retRuleCodes = null;
//string sql = "SELECT BINDING_WARNING_RULES FROM " + bindingTableName + " WHERE COLUMN_NAME='" + fieldName + "'";
string sql = "SELECT " + WarningDataBindingTableCommonDbConstNames.BINDING_WARNING_RULES +
" FROM " + bindingTableName +
" WHERE " + WarningDataBindingTableCommonDbConstNames.COLUMN_NAME + "='" + fieldName + "'";
ManageDataBase db = new ManageDataBase(DATABASE_TYPE.WarningManagementDB);
DataSet ds = db.ReturnDS(sql);
try
{
int n = ds.Tables[0].Rows.Count;
if (n > 0)
{
retRuleCodes = new string[n];
}
else
{
Alert.alert("字段名：" + fieldName + "未绑定规则编码！【表名：" + bindingTableName + "】");
return(null);
}
for (int i = 0; i < n; i++)
{
//retRuleCodes[i] = ds.Tables[0].Rows[i]["BINDING_WARNING_RULES"].ToString();
retRuleCodes[i] = ds.Tables[0].Rows[i][WarningDataBindingTableCommonDbConstNames.BINDING_WARNING_RULES].ToString();
}
}
catch (Exception ex)
{
Alert.alert(ex.Message);
return(null);
}
return(retRuleCodes);
}
///// <summary>
///// 获取预警数据字段与规则编码关系表中所有预警数据字段。
///// </summary>
///// <param name="tableName"></param>
///// <returns></returns>
//string[] GetPreWarningDataBindingTableFieldNames(string bindinTableName)
//{
// //string sql = "select name from syscolumns where id=object_id(N'" + bindinTableName + "')";
// string sql = "SELECT DISTINCT COLUMN_NAME FROM " + bindinTableName;
// ManageDataBase db = new ManageDataBase(DATABASE_TYPE.WarningManagementDB);
// DataSet ds = db.ReturnDS(sql);
// string[] ret = null;
// try
// {
// int n = ds.Tables[0].Rows.Count;
// if (n > 0)
// {
// ret = new string[n];
// }
// for (int i = 0; i < n; i++)
// {
// ret[i] = ds.Tables[0].Rows[i][0].ToString();
// }
// }
// catch (Exception ex)
// {
// Alert.alert(ex.Message);
// return null;
// }
// return ret;
//}
/// <summary>
/// 读取当前预警数据表对应的预警数据字段与规则编码关系表，获取预警数据字段与规则编码关系表中所有字段名（Distinct）及字段使用方式
/// </summary>
/// <param name="tableName">预警数据绑定表名</param>
/// <returns></returns>
BindingTableEntity[] GetPreWarningDataBindingTableEntity(string bindinTableName)
{
BindingTableEntity[] ret = null;
//string sql = "SELECT COLUMN_NAME, COLUMN_USE_MANNER, BINDING_WARNING_RULES FROM " + bindinTableName;
string sql = "SELECT " +
WarningDataBindingTableCommonDbConstNames.COLUMN_NAME + ", " +
WarningDataBindingTableCommonDbConstNames.COLUMN_USE_MANNER + ", " +
WarningDataBindingTableCommonDbConstNames.BINDING_WARNING_RULES + " FROM " + bindinTableName;
ManageDataBase db = new ManageDataBase(DATABASE_TYPE.WarningManagementDB);
DataSet ds = db.ReturnDS(sql);
try
{
int n = ds.Tables[0].Rows.Count;
if (n > 0)
{
ret = new BindingTableEntity[n];
}
for (int i = 0; i < n; i++)
{
ret[i] = new BindingTableEntity();
ret[i].ColumnName = ds.Tables[0].Rows[i][WarningDataBindingTableCommonDbConstNames.COLUMN_NAME].ToString();
ret[i].UseManner = (COLUMN_USE_MANNER)Enum.Parse(typeof(COLUMN_USE_MANNER), ds.Tables[0].Rows[i][WarningDataBindingTableCommonDbConstNames.COLUMN_USE_MANNER].ToString());
ret[i].BindingWarningRules = ds.Tables[0].Rows[i][WarningDataBindingTableCommonDbConstNames.BINDING_WARNING_RULES].ToString();
}
}
catch (Exception ex)
{
Alert.alert(ex.Message);
return(null);
}
return(ret);
}
/// <summary>
/// 处理地质构造信息
/// </summary>
/// <param name="geologySpaceEntity"></param>
/// <returns></returns>
public static bool ProcGeologySpaceEntityInfo(GeologySpaceEntityNew geologySpaceEntity)
{
ManageDataBase db = new ManageDataBase(DATABASE_TYPE.MiningSchedulingDB);
StringBuilder sb = new StringBuilder();
sb.Append("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;");
sb.Append("BEGIN TRANSACTION;");
sb.Append("IF EXISTS( SELECT * FROM T_GEOLOGY_SPACE WHERE ");
sb.Append(GeologySpaceDbConstNamesNew.WORKFACE_ID + "=" + geologySpaceEntity.WorkSpaceID);
sb.Append(" AND " + GeologySpaceDbConstNamesNew.TECTONIC_ID + "=" + geologySpaceEntity.TectonicID);
sb.Append(" AND " + GeologySpaceDbConstNamesNew.TECTONIC_TYPE + "=" + geologySpaceEntity.TectonicType);
sb.Append(" BEGIN ");
sb.Append(" UPDATE " + GeologySpaceDbConstNamesNew.TABLE_NAME + " SET ");
sb.Append(GeologySpaceDbConstNamesNew.TECTONIC_DISTANCE + "=" + geologySpaceEntity.Distance);
sb.Append("," + GeologySpaceDbConstNamesNew.DATE_TIME + "=" + geologySpaceEntity.onDateTime);
sb.Append(" WHERE " + GeologySpaceDbConstNamesNew.WORKFACE_ID + "=" + geologySpaceEntity.WorkSpaceID);
sb.Append(" AND " + GeologySpaceDbConstNamesNew.TECTONIC_ID + "=" + geologySpaceEntity.TectonicID);
sb.Append(" AND " + GeologySpaceDbConstNamesNew.TECTONIC_TYPE + "=" + geologySpaceEntity.TectonicType + ";");
sb.Append(" END ");
sb.Append(" ELSE ");
sb.Append(" BEGIN ");
sb.Append("INSERT INTO " + GeologySpaceDbConstNamesNew.TABLE_NAME + "(" + GeologySpaceDbConstNamesNew.WORKFACE_ID + ","
+ GeologySpaceDbConstNamesNew.TECTONIC_ID + "," + GeologySpaceDbConstNamesNew.TECTONIC_TYPE + ","
+ GeologySpaceDbConstNamesNew.TECTONIC_DISTANCE + "," + GeologySpaceDbConstNamesNew.DATE_TIME + ")VALUES(");
sb.Append(geologySpaceEntity.WorkSpaceID + "," + geologySpaceEntity.TectonicID + "," + geologySpaceEntity.TectonicType + "," + geologySpaceEntity.Distance + ",'" + geologySpaceEntity.onDateTime + "');");
sb.Append(" END ");
sb.Append("COMMIT TRANSACTION;");
bool bres = db.OperateDB(sb.ToString());
return(bres);
}
/// <summary>
/// 根据预警规则实体信息更新数据库中预警规则信息
/// </summary>
/// <param name="ent"></param>
/// <returns></returns>
public static bool updateWarningRulesInfo(PreWarningRules ent)
{
string sql = "UPDATE " + PreWarningRulesDbConstNames.TABLE_NAME
+ " SET " +
PreWarningRulesDbConstNames.RULE_CODE + "='" + ent.RuleCode
+ "'," +
PreWarningRulesDbConstNames.RULE_TYPE + "='" + ent.RuleType +
"'," +
PreWarningRulesDbConstNames.WARNING_TYPE + "='" +
ent.WarningType + "'," +
PreWarningRulesDbConstNames.WARNING_LEVEL + "='" +
ent.WarningLevel + "'," +
PreWarningRulesDbConstNames.SUITABLE_LOCATION + "='" +
ent.SuitableLocation + "'," +
PreWarningRulesDbConstNames.RULE_DESCRIPTION + "='" +
ent.RuleDescription + "'," +
PreWarningRulesDbConstNames.INDICATOR_TYPE + "='" +
ent.IndicatorType + "'," +
PreWarningRulesDbConstNames.OPERATOR + "='" + ent.Operator +
"'," +
PreWarningRulesDbConstNames.MODIFY_DATE + "='" +
ent.ModifyDate.ToString() + "', " +//MARK FIELD
PreWarningRulesDbConstNames.REMARKS + "='" + ent.Remarks + "' "
+ " WHERE " +
PreWarningRulesDbConstNames.RULE_ID + "=" +
ent.RuleId.ToString();
ManageDataBase db = new
ManageDataBase(DATABASE_TYPE.WarningManagementDB);
bool ret = db.OperateDB(sql);
//未添加更新巷道信息表的代码，参数中需要将参数名同时保存
//MARK YANGER
return(ret);
}
/// <summary>
/// 通过用户组名更新记录
/// </summary>
/// <param name="ent">用户组实体</param>
/// <param name="oldName">用户组名称</param>
/// <returns>是否更新成功</returns>
public static bool UpdateUserGroupInfomationDatabase(UserGroup ent,
string oldName)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("UPDATE ");
strSql.Append(UserGroupInformationMangementDbConstNames.TABLE_NAME);
strSql.Append(" SET ");
strSql.Append(UserGroupInformationMangementDbConstNames.USER_GROUP_NAME
+ " = ");
strSql.Append("'" + ent.GroupName + "',");
strSql.Append(UserGroupInformationMangementDbConstNames.USER_GROUP_STAFF_COUNT
+ " = ");
strSql.Append("'" + ent.UserCount + "',");
strSql.Append(UserGroupInformationMangementDbConstNames.USER_GROUP_REMARKS
+ " = ");
strSql.Append("'" + ent.Remark + "'");
strSql.Append(" WHERE ");
strSql.Append(UserGroupInformationMangementDbConstNames.USER_GROUP_NAME
+ " = ");
strSql.Append("'" + oldName + "'");
ManageDataBase database = new
ManageDataBase(DATABASE_TYPE.WarningManagementDB);
return(database.OperateDB(strSql.ToString()));
}
private static bool setTunnelAsHChuan(int tunnelID)
{
ManageDataBase db = new ManageDataBase(DATABASE_TYPE.GeologyMeasureDB);
string sql = "UPDATE " + TunnelInfoDbConstNames.TABLE_NAME + " SET " + TunnelInfoDbConstNames.TUNNEL_TYPE + " = '" + TunnelHChuanDbConstNames.TUNNEL_TYPE + "' WHERE " + TunnelInfoDbConstNames.ID + " = " + tunnelID;
bool bResult = db.OperateDB(sql);
return(bResult);
}
/// <summary>
/// 查询回采巷道数据
/// </summary>
/// <returns></returns>
public static DataSet selectTunnelHChuan()
{
ManageDataBase db = new ManageDataBase(DATABASE_TYPE.MiningSchedulingDB);
string sql = "SELECT * FROM " + TunnelHChuanDbConstNames.TABLE_NAME;
DataSet ds = db.ReturnDS(sql);
return(ds);
}
public static bool ClearPreWarningDB()
{
string sql = "TRUNCATE TABLE[" +
PreWarningRulesDbConstNames.TABLE_NAME + "]";
ManageDataBase db = new
ManageDataBase(DATABASE_TYPE.WarningManagementDB);
return(db.OperateDB(sql));
}
/// <summary>
/// 获取当前预警数据表中巷道ID、时间等符合条件的所有预警数据。
/// </summary>
/// <param name="dataTableName"></param>
/// <param name="tunnelID"></param>
/// <param name="minTime"></param>
/// <param name="maxTime"></param>
/// <returns></returns>
DataSet GetPreWarningDatas(string dataTableName, int tunnelID, DateTime minTime, DateTime maxTime)
{
string sql = "SELECT * FROM " + dataTableName + " WHERE " + WarningDatasCommonDbConstNames.TUNNEL_ID + "=" + tunnelID +
" AND " + WarningDatasCommonDbConstNames.DATETIME + ">='" + minTime.ToString() + "'" +
" AND " + WarningDatasCommonDbConstNames.DATETIME + "<='" + maxTime.ToString() + "'";
ManageDataBase db = new ManageDataBase(DATABASE_TYPE.WarningManagementDB);
return(db.ReturnDS(sql));
}
/// <summary>
/// 删除停采线信息
/// </summary>
/// <param name="stoplineEntity">停采线实体</param>
/// <returns>是否成功删除?true:false</returns>
public static bool deleteGeologySpaceEntityInfo(GeologySpaceEntityNew geologySpaceEntity)
{
ManageDataBase db = new ManageDataBase(DATABASE_TYPE.MiningSchedulingDB);
string sql = "DELETE FROM " + GeologySpaceDbConstNamesNew.TABLE_NAME + " WHERE " + GeologySpaceDbConstNamesNew.TECTONIC_ID + " =" + geologySpaceEntity.TectonicID + " AND " + GeologySpaceDbConstNamesNew.TECTONIC_TYPE + "=" + geologySpaceEntity.TectonicType;
bool bResult = db.OperateDB(sql);
return(bResult);
}
/// <summary>
/// 查询所有预警规则
/// </summary>
/// <returns></returns>
public static DataSet selectAllWarningRules()
{
string sql = "SELECT * FROM " +
PreWarningRulesDbConstNames.TABLE_NAME + " ORDER BY " +
PreWarningRulesDbConstNames.SUITABLE_LOCATION;;
ManageDataBase db = new
ManageDataBase(DATABASE_TYPE.WarningManagementDB);
return(db.ReturnDS(sql));
}
/// <summary>
/// 删除指定工作面的地质构造信息
/// </summary>
/// <param name="workingFaceId">工作面ID</param>
/// <returns></returns>
public static bool DeleteGeologySpaceEntityInfos(int workingFaceId)
{
var db = new ManageDataBase(DATABASE_TYPE.MiningSchedulingDB);
var sql = "DELETE FROM " + GeologySpaceDbConstNames.TABLE_NAME
+ " WHERE " +
GeologySpaceDbConstNames.WORKFACE_ID + "=" + workingFaceId;
var bResult = db.OperateDB(sql);
return(bResult);
}
/// <summary>
/// 获取全部煤层信息
/// </summary>
/// <returns>全部煤层信息</returns>
public static DataSet selectAllCoalSeamsInfo()
{
string sqlStr = "SELECT * FROM " +
CoalSeamsDbConstNames.TABLE_NAME;
ManageDataBase db = new
ManageDataBase(DATABASE_TYPE.GeologyMeasureDB);
DataSet ds = db.ReturnDS(sqlStr);
return(ds);
}
public List<WorkRecordAll> SearchStaffDataBaseWorkAll(int Year, int Month, int indexpage, int Day)
{
List<WorkRecordAll> returnValue = new List<WorkRecordAll>();
DataBase Base = new DataBase();
// string SearchStaffCondition = "and (a.ResignationDate='1900-01-01' or a.ResignationDate>@SearchDate ) and a.AppointmentDate<@SearchDate";
string SearchStaffCondition = "and (ResignationDate='1900-01-01' or ResignationDate > @ResignationDate ) and AppointmentDate<@SearchDate";
ManageDataBase msg = new ManageDataBase();
bool hasRoles = false;
string[] Roles = msg.getMembershipStaffRoles(HttpContext.Current.User.Identity.Name);
for (int i = 1; i < Roles.Length; i++)
{
if (Roles[i] == "4" || Roles[i] == "19")
{
hasRoles = true;
}
}
if (!hasRoles)
{
SearchStaffCondition += " and a.staffID = @StffID ";
}
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT * FROM " +
"( " +
"SELECT ROW_NUMBER() OVER (ORDER BY Unit,staffid asc)AS RowNum, * " +
"FROM " +
"( select a.staffid ,a.staffname,a.id,a.Unit, b.*,c.* from StaffDatabase a " +
" left join ( select StaffID as TID,YearVaction,workadd,WorkMinus from YearVaction where Year = @Year ) c on a.StaffID = c.TID "+
" left join " +
" (" +
" select StaffID as Teacherid , " +
" SUM(case vacationType when 2 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v1', " +
" SUM(case vacationType when 3 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v2', " +
" SUM(case vacationType when 4 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v3', " +
" SUM(case vacationType when 5 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v4', " +
" SUM(case vacationType when 6 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v5', " +
" SUM(case vacationType when 7 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v6', " +
" SUM(case vacationType when 8 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v7', " +
" SUM(case vacationType when 9 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v8', " +
" SUM(case vacationType when 10 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v9', " +
" SUM(case vacationType when 11 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v10', " +
" SUM(case vacationType when 12 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v11', " +
//" SUM(case vacationType when 13 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v12' " +
" SUM(case vacationType when 14 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v14', " +
" SUM(case vacationType when 15 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v15', " +
" SUM(case vacationType when 13 then ABS(starttime - endtime) /8 else 0 end) as 'v12' " +
//" SUM(case vacationType when 2 then ABS(starttime - endtime) /8 else 0 end) as 'v1', " +
//" SUM(case vacationType when 3 then ABS(starttime - endtime) /8 else 0 end) as 'v2', " +
//" SUM(case vacationType when 4 then ABS(starttime - endtime) /8 else 0 end) as 'v3', " +
//" SUM(case vacationType when 5 then ABS(starttime - endtime) /8 else 0 end) as 'v4', " +
//" SUM(case vacationType when 6 then ABS(starttime - endtime) /8 else 0 end) as 'v5', " +
//" SUM(case vacationType when 7 then ABS(starttime - endtime) /8 else 0 end) as 'v6', " +
//" SUM(case vacationType when 8 then ABS(starttime - endtime) /8 else 0 end) as 'v7', " +
//" SUM(case vacationType when 9 then ABS(starttime - endtime) /8 else 0 end) as 'v8', " +
//" SUM(case vacationType when 10 then ABS(starttime - endtime) /8 else 0 end) as 'v9', " +
//" SUM(case vacationType when 11 then ABS(starttime - endtime) /8 else 0 end) as 'v10', " +
//" SUM(case vacationType when 12 then ABS(starttime - endtime) /8 else 0 end) as 'v11', " +
// " SUM(case vacationType when 13 then ABS(starttime - endtime) /8 else 0 end) as 'v12' " +
" from WorkRecordManage " +
" where 1=1 and Year([date]) = @Year ";
//WHO 改 年月日 (有點爛 待改) ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end)
if ( Month > 0 && Day > 0 )
{
sql += " and MONTH([date]) = @Month and Day([date]) = @Day";
}
else if (Month > 0 && Day < 0) {
sql += " and MONTH([date]) = @Month ";
}
sql += " group by StaffID " +
" ) b on a.staffid = b.Teacherid " +
" where a.isDeleted=0 " + SearchStaffCondition +
") as alltable " +
") " +
"AS NewTable ";
sql += " WHERE RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage) ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ResignationDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(Year + "-" + (Month > 0 ? Month : 1) + "-" + (Day > 0 ? Day : 1));
cmd.Parameters.Add("@SearchDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(Year + "-" + (Month > 0 ? Month : 12) + "-" + (Day > 0 ? Day : 28));//bug 就算了
cmd.Parameters.Add("@StffID", HttpContext.Current.User.Identity.Name);
cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
cmd.Parameters.Add("@Year", SqlDbType.Int).Value = Year;
cmd.Parameters.Add("@Month", SqlDbType.Int).Value = Month;
cmd.Parameters.Add("@Day", SqlDbType.Int).Value = Day;
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
WorkRecordAll addValue = new WorkRecordAll();
addValue.StaffID = dr["StaffID"].ToString();
addValue.StaffName = dr["StaffName"].ToString();
addValue.YearVaction = dr["yearvaction"].ToString();
addValue.WorkAdd = dr["workadd"].ToString();
addValue.WorkMinus = dr["workminus"].ToString();
addValue.V1 = dr["V1"].ToString();
addValue.V2 = dr["V2"].ToString();
addValue.V3 = dr["V3"].ToString();
addValue.V4 = dr["V4"].ToString();
addValue.V5 = dr["V5"].ToString();
addValue.V6 = dr["V6"].ToString();
addValue.V7 = dr["V7"].ToString();
addValue.V8 = dr["V8"].ToString();
addValue.V9 = dr["V9"].ToString();
addValue.V10 = dr["V10"].ToString();
addValue.V11 = dr["V11"].ToString();
addValue.V12 = dr["V12"].ToString();
addValue.V14 = dr["V14"].ToString();
addValue.V15 = dr["V15"].ToString();
returnValue.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
string Ex = e.Message.ToString();
//StaffDataList addValue = new StaffDataList();
//addValue.checkNo = "-1";
//addValue.errorMsg = e.Message.ToString();
//returnValue.Add(addValue);
}
}
return returnValue;
}
public List<StaffDataList> getNoMembershipStaffDataList()
{
ManageDataBase sDB = new ManageDataBase();
return sDB.getNoMembershipStaffDataList();
}
public List<string[]> getRolesDataList()
{
ManageDataBase mDB = new ManageDataBase();
return mDB.getRolesDataList();
}
public string[] getSalaryValue()
{
ManageDataBase mDB = new ManageDataBase();
return mDB.getSalaryValue();
}
public string ManagePageRoles()
{
string returnValue = "";
ManageDataBase msg = new ManageDataBase();
string[] MembershipStaffRoles = msg.getMembershipStaffRoles(HttpContext.Current.User.Identity.Name);
bool StaffRolesBool = false;
foreach (string item in MembershipStaffRoles)
{
if (item == "4") {
StaffRolesBool = true;
}
}
if (StaffRolesBool)
{
returnValue = "<input type=\"button\" value=\"系統管理頁\" onclick=\"window.location.href='./manage/admin.aspx';\"/>";
}
return returnValue;
}
public string[] OutPropertyRecordDataBase(string rID, string unit)
{
PropertyDataBase pDB = new PropertyDataBase();
StaffDataBase sDB = new StaffDataBase();
ManageDataBase msg = new ManageDataBase();
string[] MembershipStaffRoles = msg.getMembershipStaffRoles(HttpContext.Current.User.Identity.Name);
string StaffRoles = string.Join(",",MembershipStaffRoles);
int aa = StaffRoles.IndexOf("15");
int aab = StaffRoles.IndexOf("4");
if (StaffRoles.IndexOf("15") > -1 || StaffRoles.IndexOf("4") > -1)
{
return pDB.OutPropertyRecordData(rID, unit);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public RolesStruct getStaffRoles(string sID)
{
RolesStruct returnValue = new RolesStruct();
string LimitRoles = "";
ManageDataBase msg = new ManageDataBase();
string[] MembershipStaffRoles = msg.getMembershipStaffRoles(sID);
for (int i = 1; i < MembershipStaffRoles.Length; i++)
{ //i=0=>Roles DB ID
if (MembershipStaffRoles[i] != "0")
{
if (LimitRoles.Length > 0)
{
LimitRoles += " OR ";
}
LimitRoles += " cR.ID=" + MembershipStaffRoles[i];
}
}
if (LimitRoles.Length > 0)
{
LimitRoles = " AND " + LimitRoles;
}
if (sID.Length > 0 && LimitRoles.Length > 0)
{
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT cR.*,ISNULL(cRUnit.CaseManagement,0) as CaseUnit , ISNULL(cRUnit.ListeningManagement,0) as hearingUnit, " +
"ISNULL(cRUnit.TeachingManagement,0) as teachUnit, ISNULL(cRUnit.Payroll,0) as payrollUnit, ISNULL(cRUnit.Attendance,0) as aUnit, " +
"ISNULL(cRUnit.PersonnelManagement,0) as personnelUnit, ISNULL(cRUnit.PropertyApplyManagement,0) as paUnit, " +
"ISNULL(cRUnit.PropertyManagement,0) as propertyUnit, ISNULL(cRUnit.LibraryManagement,0) as LUnit, " +
"ISNULL(cRUnit.ServiceFees,0) as sUnit, ISNULL(cRUnit.CaseTemperature,0) as cBTUnit, ISNULL(cRUnit.TeachersTemperature,0) as tBTUnit, " +
"ISNULL(cRUnit.StationeryManagement,0) as stationeryUnit, ISNULL(cRUnit.RemindeSystem,0) as remindeUnit " +
"FROM Competence_Roles cR LEFT JOIN Competence_Roles_Unit cRUnit ON cR.ID=cRUnit.cRolesID AND cRUnit.isDeleted=0 " +
"WHERE cR.isDeleted=0 " + LimitRoles;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(sID);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string[] RolesCompareReturn = this.RolesCompare(returnValue.caseStu, dr["CaseManagement"].ToString(), dr["CaseUnit"].ToString());
returnValue.caseStu[0] = RolesCompareReturn[0];
returnValue.caseStu[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.hearing, dr["ListeningManagement"].ToString(), dr["hearingUnit"].ToString());
returnValue.hearing[0] = RolesCompareReturn[0];
returnValue.hearing[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.teach, dr["TeachingManagement"].ToString(), dr["teachUnit"].ToString());
returnValue.teach[0] = RolesCompareReturn[0];
returnValue.teach[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.salary, dr["Payroll"].ToString(), dr["payrollUnit"].ToString());
returnValue.salary[0] = RolesCompareReturn[0];
returnValue.salary[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.attendance, dr["Attendance"].ToString(), dr["aUnit"].ToString());
returnValue.attendance[0] = RolesCompareReturn[0];
returnValue.attendance[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.personnel, dr["PersonnelManagement"].ToString(), dr["personnelUnit"].ToString());
returnValue.personnel[0] = RolesCompareReturn[0];
returnValue.personnel[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.apply, dr["PropertyApplyManagement"].ToString(), dr["paUnit"].ToString());
returnValue.apply[0] = RolesCompareReturn[0];
returnValue.apply[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.property, dr["PropertyManagement"].ToString(), dr["propertyUnit"].ToString());
returnValue.property[0] = RolesCompareReturn[0];
returnValue.property[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.library, dr["LibraryManagement"].ToString(), dr["LUnit"].ToString());
returnValue.library[0] = RolesCompareReturn[0];
returnValue.library[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.serviceFees, dr["ServiceFees"].ToString(), dr["sUnit"].ToString());
returnValue.serviceFees[0] = RolesCompareReturn[0];
returnValue.serviceFees[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.caseBT, dr["CaseTemperature"].ToString(), dr["cBTUnit"].ToString());
returnValue.caseBT[0] = RolesCompareReturn[0];
returnValue.caseBT[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.teachBT, dr["TeachersTemperature"].ToString(), dr["tBTUnit"].ToString());
returnValue.teachBT[0] = RolesCompareReturn[0];
returnValue.teachBT[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.stationery, dr["StationeryManagement"].ToString(), dr["stationeryUnit"].ToString());
returnValue.stationery[0] = RolesCompareReturn[0];
returnValue.stationery[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.remind, dr["RemindeSystem"].ToString(), dr["remindeUnit"].ToString());
returnValue.remind[0] = RolesCompareReturn[0];
returnValue.remind[1] = RolesCompareReturn[1];
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue.checkNo = "-1";
returnValue.errorMsg = e.Message;
}
}
}
return returnValue;
}
public string[] setAidsBrandList(CreateAidsBrand structValue)
{
ManageDataBase mDB = new ManageDataBase();
return mDB.setAidsBrandList(structValue);
}
public string[] setStaffRolesData(List<string> RolesList,string sID)
{
ManageDataBase mDB = new ManageDataBase();
return mDB.setStaffRolesData(RolesList, sID);
}
public string[] createSalaryValue(string Value)
{
ManageDataBase mDB = new ManageDataBase();
return mDB.createSalaryValue(Value);
}
public string[] SearchStaffDataBaseWorkAllCount(int Year, int Month,int Day)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
string SearchStaffCondition = "and (ResignationDate='1900-01-01' or ResignationDate > @ResignationDate ) and AppointmentDate<@SearchDate ";
//List<string> UserFile = getStaffDataName(HttpContext.Current.User.Identity.Name);
//this.personnelFunction();
ManageDataBase msg = new ManageDataBase();
bool hasRoles = false;
string[] Roles = msg.getMembershipStaffRoles(HttpContext.Current.User.Identity.Name);
for (int i = 1; i < Roles.Length; i++)
{
if (Roles[i] == "4" || Roles[i] == "19")
{
hasRoles = true;
}
}
if (!hasRoles) {
SearchStaffCondition += " and staffID = @StffID ";
}
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT COUNT(*) AS QCOUNT FROM StaffDatabase WHERE isDeleted=0 " + SearchStaffCondition;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
//ResignationDate
cmd.Parameters.Add("@ResignationDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction( Year + "-" + (Month>0 ? Month : 1 ) + "-" + (Day>0?Day:1));
cmd.Parameters.Add("@SearchDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(Year + "-" + (Month > 0 ? Month : 12) + "-" + (Day > 0 ? Day : 28));//bug 就算了
cmd.Parameters.Add("@StffID", HttpContext.Current.User.Identity.Name);
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}