/// <summary>
/// 删除人员信息
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public bool DeletePersonInfo(string guid)
{
DeletePersonInfoSql += " and NAMEID = '" + guid + "'";
int i = DbHelperACE.ExecuteSql(DeletePersonInfoSql);
return(i > 0);
}
/// <summary>
/// 删除人员信息
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public bool DeletePersonOutInfo(string id)
{
DeletePersonOutInfoSql += " and id = " + id + "";
int i = DbHelperACE.ExecuteSql(DeletePersonOutInfoSql);
return(i > 0);
}
/// <summary>
/// 删除人员信息
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public bool DeletePersonRewardPunishInfo(string id)
{
DeletePersonRewardPunishInfoSql += " and ID = " + id + "";
int i = DbHelperACE.ExecuteSql(DeletePersonRewardPunishInfoSql);
return(i > 0);
}
public static int GetMaxID(string FieldName, string TableName)
{
string strsql = "select max(" + FieldName + ")+1 from " + TableName;
object obj = DbHelperACE.GetSingle(strsql);
if (obj == null)
{
return(1);
}
else
{
return(int.Parse(obj.ToString()));
}
}
public static int GetRecordCount(string _ID, string _tbName, string _strCondition, int _Dist)
{
string sql = getPageListCounts(_ID, _tbName, _strCondition, _Dist);
object obj = DbHelperACE.GetSingle(sql);
if (obj == null)
{
return(1);
}
else
{
return(int.Parse(obj.ToString()));
}
}
public DataTable GetPersonRewardPunishDataTable(string type, string incident, string nameid, string fromdate, string todate, string orderstr)
{
if (!string.IsNullOrEmpty(type))
{
SelectPersonRewardPunishInfoListSql += "and RPTYPE = '" + type + "' ";
}
if (!string.IsNullOrEmpty(incident))
{
SelectPersonRewardPunishInfoListSql += "and INCIDENT = '" + incident + "' ";
}
if (!string.IsNullOrEmpty(nameid))
{
SelectPersonRewardPunishInfoListSql += "and NAMEID = '" + nameid + "' ";
}
if (!string.IsNullOrEmpty(fromdate))
{
SelectPersonRewardPunishInfoListSql += "and Format(RPTIME,\"yyyy-mm-dd\") >= '" + fromdate + "' ";
}
if (!string.IsNullOrEmpty(todate))
{
SelectPersonRewardPunishInfoListSql += "and Format(RPTIME,\"yyyy-mm-dd\") <= '" + todate + "' ";
}
if (!string.IsNullOrEmpty(orderstr))
{
SelectPersonRewardPunishInfoListSql += orderstr;
}
else
{
SelectPersonRewardPunishInfoListSql += " order by RPTIME desc ,OPERATORTIME desc";
}
DataSet ds = DbHelperACE.Query(SelectPersonRewardPunishInfoListSql);
return(ds.Tables[0]);
}
/// <summary>
/// 返回人员统计列表
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public List <NameValuePair <double> > GetPersonRPScoreByDate(string fromDate, string toDate)
{
List <NameValuePair <double> > PersonPlanList = new List <NameValuePair <double> >();
List <OleDbParameter> paramList = new List <OleDbParameter>();
OleDbParameter param = new OleDbParameter("@fromDate", OleDbType.LongVarWChar);
param.Value = fromDate;
paramList.Add(param);
OleDbParameter toDateparam = new OleDbParameter("@toDate", OleDbType.LongVarWChar);
toDateparam.Value = toDate;
paramList.Add(toDateparam);
DataSet dshj = DbHelperACE.Query(GetPersonRPScoreByDateSql, paramList.ToArray());
DataRowCollection drshj = dshj.Tables[0].Rows;
for (int i = 0; i < drshj.Count; i++)
{
NameValuePair <double> item = new NameValuePair <double>();
item.Name = drshj[i]["name"].ToString().Trim();
if (drshj[i]["score"] != DBNull.Value)
{
try
{
item.Value = Convert.ToDouble(drshj[i]["score"].ToString().Trim());
}
catch
{ }
}
PersonPlanList.Add(item);
}
return(PersonPlanList);
}
/// <summary>
/// 获取基础表信息
/// </summary>
/// <param name="type">类型</param>
/// <param name="code">code值</param>
/// <returns>基础表信息</returns>
public List <CodeDicItem> CodeDicItemList(string type, string code)
{
if (!string.IsNullOrEmpty(type))
{
CodeDicItemListSql += "and type = '" + type + "' ";
}
if (!string.IsNullOrEmpty(code))
{
CodeDicItemListSql += "and code = '" + code + "' ";
}
DataSet ds = DbHelperACE.Query(CodeDicItemListSql);
List <CodeDicItem> list = new List <CodeDicItem>();
DataRowCollection drs = ds.Tables[0].Rows;
for (int i = 0; i < drs.Count; i++)
{
CodeDicItem item = new CodeDicItem();
item.CODE = drs[i]["CODE"].ToString().Trim();
item.NAME = drs[i]["NAME"].ToString().Trim();
item.TYPE = drs[i]["TYPE"].ToString().Trim();
item.DSC = drs[i]["DSC"].ToString().Trim();
item.A1 = drs[i]["A1"].ToString().Trim();
item.A2 = drs[i]["A2"].ToString().Trim();
item.A3 = drs[i]["A3"].ToString().Trim();
list.Add(item);
}
return(list);
}
/// <summary>
/// 获取活动下所有人员
/// </summary>
/// <param name="orgGuid">组织ID</param>
/// <returns>所有人员信息</returns>
public List <PP_PersonLeaveInfo> GetPP_PersonInfos(string name, string type)
{
if (!string.IsNullOrEmpty(name))
{
GetpersonLeaveInfos2017 += "and name = '" + name + "' ";
}
if (!string.IsNullOrEmpty(type))
{
GetpersonLeaveInfos2017 += "and type = '" + type + "' ";
}
DataSet ds = DbHelperACE.Query(GetpersonLeaveInfos2017);
List <PP_PersonLeaveInfo> list = new List <PP_PersonLeaveInfo>();
DataRowCollection drs = ds.Tables[0].Rows;
for (int i = 0; i < drs.Count; i++)
{
//没有请假日期不算
if (string.IsNullOrEmpty(drs[i]["LeaveDay"].ToString()))
{
continue;
}
PP_PersonLeaveInfo item = new PP_PersonLeaveInfo();
//list[i]. = drs[i]["name"].ToString().Trim();
item.NAME = drs[i]["name"].ToString().Trim();
item.LEAVE_DAY = drs[i]["LeaveDay"].ToString().Trim();
item.LEAVE_TYPE = drs[i]["type"].ToString().Trim();
list.Add(item);
}
return(list);
}
/// <summary>
/// 返回人员统计列表
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public List <PersonPlanStatisticData> GetPersonPlanStats2017(string obj)
{
DataSet ds = DbHelperACE.Query(Getpersonstat2017);
List <PersonPlanStatisticData> PersonPlanList = new List <PersonPlanStatisticData>();
DataRowCollection drs = ds.Tables[0].Rows;
for (int i = 0; i < drs.Count; i++)
{
PersonPlanStatisticData item = new PersonPlanStatisticData();
item.AddressGuid = drs[i]["name"].ToString().Trim();
item.Address = drs[i]["name"].ToString().Trim();
item.Type = drs[i]["type"].ToString().Trim();
if (drs[i]["statisticcount"] != DBNull.Value)
{
item.Count = Double.Parse(drs[i]["statisticcount"].ToString().Trim());
}
PersonPlanList.Add(item);
}
DataSet dshj = DbHelperACE.Query(GetpersonstatHJ2017);
DataRowCollection drshj = dshj.Tables[0].Rows;
for (int i = 0; i < drshj.Count; i++)
{
PersonPlanStatisticData item = new PersonPlanStatisticData();
item.AddressGuid = drshj[i]["name"].ToString().Trim();
item.Address = drshj[i]["name"].ToString().Trim();
item.Type = drshj[i]["type"].ToString().Trim();
if (drshj[i]["statisticcount"] != DBNull.Value)
{
item.Count = Double.Parse(drshj[i]["statisticcount"].ToString().Trim());
}
PersonPlanList.Add(item);
}
return(PersonPlanList);
}
public static bool Exists(string strSql, params OleDbParameter[] cmdParms)
{
object obj = DbHelperACE.GetSingle(strSql, cmdParms);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// 返回人员统计列表
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public List <PersonPlanStatisticData> GetPersonRPStatByDate(string fromDate, string toDate)
{
// SELECT nameid, name, INCIDENT as type, sum(FRACTION) as statisticcount FROM PersonRewardPunishInfo
//where Format(RPTIME,"yyyy-mm-dd") <= "2012/10/12"
//group by NAMEID, NAME, INCIDENT order by NAME, INCIDENT
if (string.IsNullOrEmpty(fromDate) == false)
{
GetPersonRPStatByDateSql += " and Format(RPTIME,\"yyyy-mm-dd\") >= \'" + fromDate + "'";
}
if (string.IsNullOrEmpty(toDate) == false)
{
GetPersonRPStatByDateSql += " and Format(RPTIME,\"yyyy-mm-dd\") <= \'" + toDate + "'";
}
GetPersonRPStatByDateSql += " group by NAMEID, NAME, INCIDENT order by NAME, INCIDENT ";
DataSet ds = DbHelperACE.Query(GetPersonRPStatByDateSql);
List <PersonPlanStatisticData> PersonPlanList = new List <PersonPlanStatisticData>();
DataRowCollection drs = ds.Tables[0].Rows;
for (int i = 0; i < drs.Count; i++)
{
PersonPlanStatisticData item = new PersonPlanStatisticData();
item.AddressGuid = drs[i]["nameid"].ToString().Trim();
item.Address = drs[i]["name"].ToString().Trim();
item.Type = drs[i]["type"].ToString().Trim();
if (drs[i]["statisticcount"] != DBNull.Value)
{
item.Count = Double.Parse(drs[i]["statisticcount"].ToString().Trim());
}
PersonPlanList.Add(item);
}
// and Format(RPTIME,'yyyy - mm - dd') >= @fromDate
//and Format(RPTIME,'yyyy - mm - dd') <= @toDate
List <OleDbParameter> paramList = new List <OleDbParameter>();
OleDbParameter param = new OleDbParameter("@fromDate", OleDbType.LongVarWChar);
param.Value = fromDate;
paramList.Add(param);
OleDbParameter toDateparam = new OleDbParameter("@toDate", OleDbType.LongVarWChar);
toDateparam.Value = toDate;
paramList.Add(toDateparam);
DataSet dshj = DbHelperACE.Query(GetPersonRPStatByDateHJSql, paramList.ToArray());
DataRowCollection drshj = dshj.Tables[0].Rows;
for (int i = 0; i < drshj.Count; i++)
{
PersonPlanStatisticData item = new PersonPlanStatisticData();
item.AddressGuid = drshj[i]["name"].ToString().Trim();
item.Address = drshj[i]["name"].ToString().Trim();
item.Type = drshj[i]["type"].ToString().Trim();
if (drshj[i]["statisticcount"] != DBNull.Value)
{
item.Count = Double.Parse(drshj[i]["statisticcount"].ToString().Trim());
}
PersonPlanList.Add(item);
}
return(PersonPlanList);
}
/// <summary>
/// 获取 所有人员
/// </summary>
/// <param name="militaryrank">军衔</param>
/// <param name="nameid">名称id</param>
/// <param name="orderstr">排序</param>
/// <returns>所有人员信息</returns>
public List <PersonBasicInfo> GetPersonBasicInfos(string militaryrank, string nameid, string orderstr, bool isShowPic = true)
{
if (!string.IsNullOrEmpty(militaryrank))
{
SelectPersonInfoListSql += "and MILITARYRANK = '" + militaryrank + "' ";
}
if (!string.IsNullOrEmpty(nameid))
{
SelectPersonInfoListSql += "and NAMEID = '" + nameid + "' ";
}
if (!string.IsNullOrEmpty(orderstr))
{
SelectPersonInfoListSql += orderstr;
}
else
{
SelectPersonInfoListSql += " order by MILITARYRANK,POLITICAL";
}
DataSet ds = DbHelperACE.Query(SelectPersonInfoListSql);
List <PersonBasicInfo> list = new List <PersonBasicInfo>();
DataRowCollection drs = ds.Tables[0].Rows;
for (int i = 0; i < drs.Count; i++)
{
PersonBasicInfo item = new PersonBasicInfo();
item.NAMEID = drs[i]["NAMEID"].ToString().Trim();
item.NAME = drs[i]["NAME"].ToString().Trim();
item.SEX = drs[i]["SEX"].ToString().Trim();
item.BIRTHDATE = drs[i]["BIRTHDATE"].ToString().Trim();
item.NATION = drs[i]["NATION"].ToString().Trim();
item.ENLISTMENTDATE = drs[i]["ENLISTMENTDATE"].ToString().Trim();
item.MILITARYRANK = drs[i]["MILITARYRANK"].ToString().Trim();
item.ORIGINPLACE = drs[i]["ORIGINPLACE"].ToString().Trim();
item.ARMYSEAT = drs[i]["ARMYSEAT"].ToString().Trim();
item.MAJOR = drs[i]["MAJOR"].ToString().Trim();
item.EDUCATION = drs[i]["EDUCATION"].ToString().Trim();
item.POLITICAL = drs[i]["POLITICAL"].ToString().Trim();
item.PARTYTIME = drs[i]["PARTYTIME"].ToString().Trim();
item.HJQK = drs[i]["HJQK"].ToString().Trim();
item.BLOODTYPE = drs[i]["BLOODTYPE"].ToString().Trim();
item.IDCARD = drs[i]["IDCARD"].ToString().Trim();
item.HOBBY = drs[i]["HOBBY"].ToString().Trim();
item.CHARACTERTYPE = drs[i]["CHARACTERTYPE"].ToString().Trim();
item.QQID = drs[i]["QQID"].ToString().Trim();
item.HOMEADDRESS = drs[i]["HOMEADDRESS"].ToString().Trim();
item.PHONE = drs[i]["PHONE"].ToString().Trim();
item.SPOUSENAME = drs[i]["SPOUSENAME"].ToString().Trim();
item.SPOUSEMARRIAGETIME = drs[i]["SPOUSEMARRIAGETIME"].ToString().Trim();
item.SPOUSESUNIT = drs[i]["SPOUSESUNIT"].ToString().Trim();
item.SPOUSESHOMEADDRESS = drs[i]["SPOUSESHOMEADDRESS"].ToString().Trim();
item.SPOUSESPHONE = drs[i]["SPOUSESPHONE"].ToString().Trim();
item.CHILDRENNAME = drs[i]["CHILDRENNAME"].ToString().Trim();
item.CHILDRENSEX = drs[i]["CHILDRENSEX"].ToString().Trim();
item.CHILDRENBIRTH = drs[i]["CHILDRENBIRTH"].ToString().Trim();
item.ENLISTINGRESUME = drs[i]["ENLISTINGRESUME"].ToString().Trim();
item.TRAININGSITUATION = drs[i]["TRAININGSITUATION"].ToString().Trim();
item.REWARDSPUNISHMENTS = drs[i]["REWARDSPUNISHMENTS"].ToString().Trim();
item.FAMILYMEMBER = drs[i]["FAMILYMEMBER"].ToString().Trim();
item.A1 = drs[i]["A1"].ToString().Trim();
item.A2 = drs[i]["A2"].ToString().Trim();
item.A3 = drs[i]["A3"].ToString().Trim();
if (isShowPic == true)
{
if (drs[i]["PHOTO"] != null && !drs[i]["PHOTO"].ToString().Equals(""))
{
item.PHOTO = (byte[])drs[i]["PHOTO"];
}
}
list.Add(item);
}
return(list);
}
/// <summary>
/// 获取人员外出信息
/// </summary>
/// <returns>所有人员外出信息</returns>
public List <PersonOutInfo> GetPersonOutInfos(string nameid, string incident, string fromdate, string todate)
{
if (!string.IsNullOrEmpty(incident))
{
SelectPersonOutInfoListSql += "and INCIDENT = '" + incident + "' ";
}
if (!string.IsNullOrEmpty(nameid))
{
SelectPersonOutInfoListSql += "and NAMEID = '" + nameid + "' ";
}
if (!string.IsNullOrEmpty(fromdate))
{
SelectPersonOutInfoListSql += "and Format(OUTTIME,\"yyyy-mm-dd\") >= '" + fromdate + "' ";
}
if (!string.IsNullOrEmpty(todate))
{
SelectPersonOutInfoListSql += "and Format(BACKTIME,\"yyyy-mm-dd\") <= '" + todate + "' ";
}
SelectPersonOutInfoListSql += " order by OPERATORTIME desc";
DataSet ds = DbHelperACE.Query(SelectPersonOutInfoListSql);
List <PersonOutInfo> list = new List <PersonOutInfo>();
DataRowCollection drs = ds.Tables[0].Rows;
for (int i = 0; i < drs.Count; i++)
{
PersonOutInfo item = new PersonOutInfo();
item.ID = drs[i]["ID"].ToString().Trim();
item.NAMEID = drs[i]["NAMEID"].ToString().Trim();
item.NAME = drs[i]["NAME"].ToString().Trim();
item.INCIDENT = drs[i]["INCIDENT"].ToString().Trim();
item.OUTTIMEHOUR = drs[i]["OUTTIMEHOUR"].ToString().Trim();
if (string.IsNullOrEmpty(drs[i]["OUTTIMEMINUTE"].ToString()))
{
item.OUTTIMEMINUTE = "00";
}
else
{
item.OUTTIMEMINUTE = drs[i]["OUTTIMEMINUTE"].ToString().Trim();
}
item.BACKTIMEHOUR = drs[i]["BACKTIMEHOUR"].ToString().Trim();
if (string.IsNullOrEmpty(drs[i]["BACKTIMEMINUTE"].ToString()))
{
item.BACKTIMEMINUTE = "00";
}
else
{
item.BACKTIMEMINUTE = drs[i]["BACKTIMEMINUTE"].ToString().Trim();
}
try
{
if (drs[i]["OUTTIME"] != null && drs[i]["OUTTIME"].ToString() != string.Empty)
{
item.OUTTIME = Convert.ToDateTime(drs[i]["OUTTIME"]).ToString("yyyy-MM-dd") + " " + item.OUTTIMEHOUR + "时" + item.OUTTIMEMINUTE + "分";
}
}
catch
{
item.OUTTIME = drs[i]["OUTTIME"].ToString().Trim();
}
try
{
if (drs[i]["BACKTIME"] != null && drs[i]["BACKTIME"].ToString() != string.Empty)
{
item.BACKTIME = Convert.ToDateTime(drs[i]["BACKTIME"]).ToString("yyyy-MM-dd") + " " + item.BACKTIMEHOUR + "时" + item.BACKTIMEMINUTE + "分";
}
}
catch
{
item.BACKTIME = drs[i]["BACKTIME"].ToString().Trim();
}
item.BZ = drs[i]["BZ"].ToString().Trim();
item.OPERATOR = drs[i]["OPERATOR"].ToString().Trim();
if (drs[i]["OPERATORTIME"] != null && drs[i]["OPERATORTIME"].ToString() != string.Empty)
{
item.OPERATORTIME = Convert.ToDateTime(drs[i]["OPERATORTIME"]);
}
try
{
if (drs[i]["OPERATORTIME"] != null && drs[i]["OPERATORTIME"].ToString() != string.Empty)
{
item.OPERATORTIME = Convert.ToDateTime(drs[i]["OPERATORTIME"]).ToLocalTime();
}
}
catch
{
//item.OPERATORTIME =null;
}
item.OPERATORID = drs[i]["OPERATORID"].ToString().Trim();
item.A1 = drs[i]["A1"].ToString().Trim();
item.A2 = drs[i]["A2"].ToString().Trim();
item.A3 = drs[i]["A3"].ToString().Trim();
list.Add(item);
}
return(list);
}