public static IEnumerable<DepSumCount> GetDepDateSum(DepDateCount deplist, out int countSum)
{
var entities = new LazlwEntities();
if (deplist.startDate.Year != 1 && deplist.endDate.Year != 1)
{
deplist.startDate = Convert.ToDateTime(deplist.startDate.ToString("yyyy-MM-01"));
deplist.endDate = deplist.endDate.AddMonths(1).AddDays(-deplist.endDate.AddMonths(1).Day+1).AddSeconds(-1);
}
else
{
DateTime dtTarget = DateTime.Now;
deplist.startDate = dtTarget.AddDays(-dtTarget.Day + 1);
deplist.endDate = dtTarget.AddMonths(1).AddDays(-dtTarget.AddMonths(1).Day + 1).AddSeconds(-1);
}
string sql= @"SELECT convert(varchar(7),dd.DepUTime,120) AS DepUTime, ISNULL(SUM(dd.DepDlk),0) as DepDlk ,ISNULL(SUM(dd.DepDyk),0) as DepDyk,
ISNULL(SUM(dd.DepCb),0) as DepCb,ISNULL(SUM(dd.DepNfmz),0) as DepNfmz ,ISNULL(SUM(dd.DepHbmz),0) as DepHbmz,ISNULL(SUM(dd.DepZz),0) as DepZz,
ISNULL(SUM(dd.DepBy),0) as DepBy,ISNULL(SUM(dd.DepYf),0) as DepYf
FROM DepDate AS dd Left join Person as p on p.PerId=dd.DepPerId where dd.DepUTime between '" + deplist.startDate + "' and '" + deplist.endDate + "' and p.PerAudit=1 Group by convert(varchar(7),dd.DepUTime,120) ";
string sqlSum = @"SELECT ISNULL(SUM(dd.DepDlk),0) as DepDlk ,ISNULL(SUM(dd.DepDyk),0) as DepDyk,
ISNULL(SUM(dd.DepCb),0) as DepCb,ISNULL(SUM(dd.DepNfmz),0) as DepNfmz ,ISNULL(SUM(dd.DepHbmz),0) as DepHbmz,ISNULL(SUM(dd.DepZz),0) as DepZz,
ISNULL(SUM(dd.DepBy),0) as DepBy,ISNULL(SUM(dd.DepYf),0) as DepYf
FROM DepDate AS dd Left join Person as p on p.PerId=dd.DepPerId where dd.DepUTime between '" + deplist.startDate + "' and '" + deplist.endDate + "' and p.PerAudit=1 ";
var model=entities.Database.SqlQuery<DepDateSum>(sqlSum).First();
countSum = 0;
if (deplist.DepDlk == 1)
{
countSum += model.DepDlk;
}
if (deplist.DepDyk == 1)
{
countSum += model.DepDyk;
}
if (deplist.DepCb == 1)
{
countSum += model.DepCb;
}
if (deplist.DepNfmz == 1)
{
countSum += model.DepNfmz;
}
if (deplist.DepHbmz == 1)
{
countSum += model.DepHbmz;
}
if (deplist.DepZz == 1)
{
countSum += model.DepZz;
}
if (deplist.DepBy == 1)
{
countSum += model.DepBy;
}
if (deplist.DepYf == 1)
{
countSum += model.DepYf;
}
return entities.Database.SqlQuery<DepSumCount>(sql).OrderByDescending(t => t.DepUTime);
}
public static bool InsertOutDate(OutDate model)
{
using (LazlwEntities entities = new LazlwEntities())
{
entities.OutDates.Add(model);
return entities.SaveChanges() > 0;
}
}
public static int GetOutDateSum(int outPerId, DateTime dateTime)
{
var entities = new LazlwEntities();
var list = entities.OutDates.Where(t => t.OutPerId == outPerId && t.OutUTime.Year == dateTime.Year);
if (list.Count() == 0)
{
return 0;
}
return list.Sum(t => t.OutSzcl);
}
public static Person GetLoginPerson(string perUser, string perPwd)
{
using (LazlwEntities entities = new LazlwEntities())
{
var entity = entities.People.FirstOrDefault(t => t.PerUser == perUser && t.PerPwd == perPwd);
if(entity!=null){
entity.PerUTime=DateTime.Now;
entities.SaveChanges();
}
return entity;
}
}
public static void DelPersonAll(int perId)
{
using (LazlwEntities entities = new LazlwEntities())
{
var depEntity=entities.DepDates.Where(t => t.DepPerId == perId);
entities.DepDates.RemoveRange(depEntity);
var outEntity = entities.OutDates.Where(t => t.OutPerId == perId);
entities.OutDates.RemoveRange(outEntity);
var entity = entities.People.FirstOrDefault(t => t.PerId == perId);
if (entity != null)
{
entities.People.Remove(entity);
}
entities.SaveChanges();
}
}
public static IEnumerable<OutSumCount> GetOutDateSum(DateTime sDate, DateTime eDate,int outpig, out int countSum)
{
var entities = new LazlwEntities();
string sql = @"SELECT convert(varchar(7),od.OutUTime,120) AS OutUTime, ISNULL(SUM(od.OutDxsz),0) as OutDxsz ,
ISNULL(SUM(od.OutSzcl),0) as OutSzcl FROM OutDate as od left join Person as p on p.PerId=od.OutPerId where od.OutUTime between '" + sDate + "' and '" + eDate
+ "' and p.PerAudit=1 Group by convert(varchar(7),od.OutUTime,120) ";
string sqlCount = @"SELECT ";
if(outpig!=-1){
sqlCount+=outpig==0?"ISNULL(SUM(od.OutDxsz),0) as countSum ":"ISNULL(SUM(od.OutSzcl),0) as countSum ";
}else{
sqlCount+="(ISNULL(SUM(od.OutDxsz),0)+ISNULL(SUM(od.OutSzcl),0)) as countSum ";
}
sqlCount += @"FROM OutDate as od left join Person as p on p.PerId=od.OutPerId where od.OutUTime between '" + sDate + "' and '" + eDate + "' and p.PerAudit=1 ";
countSum = entities.Database.SqlQuery<OutSum>(sqlCount).First().countSum;
return entities.Database.SqlQuery<OutSumCount>(sql).OrderByDescending(t => t.OutUTime);
}
public static OutDateSum GetDepDateSum(int perId)
{
var entities = new LazlwEntities();
string sql = @"SELECT ISNULL(SUM(od.OutDxsz) ,0) as OutDxsz,ISNULL(SUM(od.OutSzcl),0) as OutSzcl
FROM OutDate AS od";
List<SqlParameter> parList = new List<SqlParameter>();
string sqlWhere = "";
if (perId != -1)
{
sqlWhere += " OutPerId=@OutPerId and";
parList.Add(new SqlParameter("@OutPerId", perId));
}
if (!string.IsNullOrEmpty(sqlWhere))
{
sqlWhere = sqlWhere.Remove(sqlWhere.Length - 3, 3);
sql += " where " + sqlWhere;
}
return entities.Database.SqlQuery<OutDateSum>(sql, parList.ToArray()).First();
}
public static IEnumerable<OutDepPerson> GetPersonByPage(int load,int perAudit, string keyword, int PageSize, int PageIndex)
{
var entities = new LazlwEntities();
string sql = @"SELECT ISNULL(dd.DepId,0) AS DepId,ISNULL(od.OutId,0) AS OutId,p.PerAddr,p.PerAudit,p.PerClass,p.PerComp,p.PerId,p.PerLTime,p.PerMail,p.PerName,p.PerPwd,p.PerRTime,p.PerTel,
p.PerType,p.PerUTime,p.PerUser FROM Person as p
LEFT JOIN (
SELECT distinct dd.DepPerId,DD.DepUTime,dd.DepId
FROM DepDate as dd
where CONVERT(varchar(7),dd.DepUTime,120)=@UTime
) as dd on dd.DepPerId=p.PerId
LEFT JOIN(
SELECT distinct od.OutPerId,od.OutUTime,od.OutId
FROM OutDate AS od
where CONVERT(varchar(7),od.OutUTime,120)=@UTime
)as od on od.OutPerId=p.PerId ";
List<SqlParameter> parList = new List<SqlParameter>();
parList.Add(new SqlParameter("@UTime", DateTime.Now.ToString("yyyy-MM")));
string sqlWhere = "";
if(!string.IsNullOrEmpty(keyword)){
sqlWhere += " (p.PerUser like @PerComp or p.PerComp like @PerComp or p.PerName like @PerComp) and";
parList.Add(new SqlParameter("@PerComp", "%" + keyword+"%"));
}
if (perAudit != -1)
{
sqlWhere += " p.PerAudit=@PerAudit and";
parList.Add(new SqlParameter("@PerAudit", perAudit));
}
if (load != -1)
{
sqlWhere += " dd.DepId is null and od.OutId is null and";
}
if (!string.IsNullOrEmpty(sqlWhere))
{
sqlWhere = sqlWhere.Remove(sqlWhere.Length - 3, 3);
sql += " where " + sqlWhere;
}
if (perAudit == -1)
{
return entities.Database.SqlQuery<OutDepPerson>(sql, parList.ToArray()).OrderByDescending(t => t.PerId).Skip((PageIndex - 1) * PageSize).Take(PageSize);
}
return entities.Database.SqlQuery<OutDepPerson>(sql, parList.ToArray()).OrderByDescending(t => t.PerUTime).Skip((PageIndex - 1) * PageSize).Take(PageSize);
}
public static bool UpdateSysAdminAdId(int adId, string adPwd,string newAdPwd,out string msg)
{
using (LazlwEntities entities = new LazlwEntities())
{
var model = entities.SysAdminZlwGls.FirstOrDefault(t => t.AdId == adId && t.AdPwd == adPwd);
if (model == null)
{
msg = "原密码有误请重试";
return false;
}
if (model.AdUser.Equals(newAdPwd.ToLower()))
{
msg = "用户名和新密码不能相同";
return false;
}
model.AdPwd = newAdPwd;
msg = "密码修改成功";
return entities.SaveChanges() > 0;
}
}
public static bool UpdatePersonAudit(int perId, byte perAudit)
{
using (LazlwEntities entities = new LazlwEntities())
{
var oldentity = entities.People.FirstOrDefault(t => t.PerId == perId);
if (oldentity == null)
{
return false;
}
oldentity.PerAudit = perAudit;
return entities.SaveChanges() > 0;
}
}
public static bool UpdateOutDate(OutDate model, OutDate oldModel)
{
using (LazlwEntities entities = new LazlwEntities())
{
entities.OutDates.Attach(oldModel);
ClassValueCopier.Copy(oldModel, model);
return entities.SaveChanges() > 0;
}
}
public static bool UpdatePerPwd(string oldPerPwd, string newPerPwd, int perId,out string msg)
{
msg="修改密码成功";
var entities = new LazlwEntities();
var model= entities.People.FirstOrDefault(t => t.PerId == perId);
if (model == null)
{
msg = "修改密码失败";
return false;
}
if (model.PerPwd != oldPerPwd)
{
msg = "旧密码输入错误";
return false;
}
model.PerPwd = newPerPwd;
if (entities.SaveChanges() > 0)
{
return true;
}
return false;
}
public static bool UpdatePerson(Person model)
{
using (LazlwEntities entities = new LazlwEntities())
{
var oldentity = entities.People.FirstOrDefault(t => t.PerId == model.PerId);
if (oldentity == null)
{
return false;
}
oldentity.PerComp=model.PerComp;
oldentity.PerType = model.PerType;
oldentity.PerName=model.PerName;
oldentity.PerTel=model.PerTel;
oldentity.PerMail=model.PerMail;
oldentity.PerAddr=model.PerAddr;
oldentity.PerLimit = model.PerLimit;
oldentity.PerGfdj = model.PerGfdj;
oldentity.PerLsmj = model.PerLsmj;
oldentity.PerXntd = model.PerXntd;
oldentity.PerZqc = model.PerZqc;
oldentity.PerCdc = model.PerCdc;
if (!string.IsNullOrEmpty(model.PerUser))
{
oldentity.PerUser = model.PerUser;
}
if (!string.IsNullOrEmpty(model.PerPwd))
{
oldentity.PerPwd = model.PerPwd;
}
return entities.SaveChanges() > 0;
}
}
public static int GetPersonCount(int perAudit, string keyword)
{
var entities = new LazlwEntities();
if (perAudit != -1)
{
return entities.People.Count(t=>t.PerAudit==perAudit&&(t.PerComp.Contains(keyword)||t.PerUser.Contains(keyword)||t.PerName.Contains(keyword)));
}
return entities.People.Count(t=>(t.PerComp.Contains(keyword) || t.PerUser.Contains(keyword)));
}
public static bool InsertPerson(Person model,out string msg)
{
using (LazlwEntities entities = new LazlwEntities())
{
msg = "用户注册成功";
var entity = entities.People.FirstOrDefault(t => t.PerUser == model.PerUser);
if (entity != null)
{
msg="用户已经存在";
return false;
}
entities.People.Add(model);
return entities.SaveChanges() > 0;
}
}
public static Person GetPersonByPerId(int perId)
{
var entities = new LazlwEntities();
return entities.People.FirstOrDefault(t => t.PerId == perId);
}
public static List<OutDate> GetDepDateAll(int outPerId, DateTime sDate, DateTime eDate)
{
var entities = new LazlwEntities();
return entities.OutDates.Where(t => t.OutPerId == outPerId && t.OutUTime >= sDate && t.OutUTime <= eDate).OrderByDescending(t => t.OutUTime).ToList();
}
public static SysAdminZlwGl GetSysAdminLogin(string adUser, string adPwd)
{
var entities = new LazlwEntities();
return entities.SysAdminZlwGls.FirstOrDefault(t => t.AdUser == adUser&&t.AdPwd==adPwd);
}
public static IQueryable<DepDate> GetDepDateList(int depPerId)
{
var entities = new LazlwEntities();
return entities.DepDates.Where(t => t.DepPerId == depPerId).OrderByDescending(t=>t.DepUTime);
}
public static IQueryable<OutDate> GetDepDateList(int outPerId)
{
var entities = new LazlwEntities();
return entities.OutDates.Where(t => t.OutPerId == outPerId).OrderByDescending(t => t.OutUTime);
}
public static int GetDepDateSumCount()
{
var entities = new LazlwEntities();
string sql = @"SELECT ( ISNULL(SUM(dd.DepDlk),0) +ISNULL(SUM(dd.DepDyk),0) +
ISNULL(SUM(dd.DepCb),0)+ISNULL(SUM(dd.DepNfmz),0)+ISNULL(SUM(dd.DepHbmz),0)+ISNULL(SUM(dd.DepZz),0)+
ISNULL(SUM(dd.DepBy),0)+ISNULL(SUM(dd.DepYf),0)) as countSum
FROM DepDate AS dd ";
return entities.Database.SqlQuery<DepSum>(sql).FirstOrDefault().countSum;
}
public static OutDate GetOutDateInfo(int outPerId, DateTime dateTime)
{
var entities = new LazlwEntities();
return entities.OutDates.FirstOrDefault(t => t.OutPerId == outPerId && t.OutUTime.Year == dateTime.Year && t.OutUTime.Month == dateTime.Month);
}
public static int GetPersonByPageOutDep()
{
var entities = new LazlwEntities();
string sqlCount = @"SELECT Count(p.PerId) as RecordCount FROM Person as p
LEFT JOIN (
SELECT distinct dd.DepPerId,DD.DepUTime,dd.DepId
FROM DepDate as dd
where CONVERT(varchar(7),dd.DepUTime,120)=@UTime
) as dd on dd.DepPerId=p.PerId
LEFT JOIN(
SELECT distinct od.OutPerId,od.OutUTime,od.OutId
FROM OutDate AS od
where CONVERT(varchar(7),od.OutUTime,120)=@UTime
)as od on od.OutPerId=p.PerId where dd.DepId is null and od.OutId is null ";
List<SqlParameter> parList = new List<SqlParameter>();
parList.Add(new SqlParameter("@UTime", DateTime.Now.ToString("yyyy-MM")));
var queryCount = entities.Database.SqlQuery<OutDepCount>(sqlCount, parList.ToArray());
return queryCount.First().RecordCount;
}
public static DepDate GetDepDateInfo(int depPerId, DateTime dateTime)
{
var entities = new LazlwEntities();
return entities.DepDates.FirstOrDefault(t => t.DepPerId == depPerId && t.DepUTime.Year == dateTime.Year && t.DepUTime.Month == dateTime.Month);
}
