internal static List <TSelectOpt> GetWeekdateList(string selYear)
{
using (var context = new WarehouseLaborEffEntities())
{
var lst = (from c in context.v_tbl_weekdata
group c by c.Date into g
orderby g.Key
select g.FirstOrDefault()
).AsEnumerable();
var nYear = 0;
if (int.TryParse(selYear, out nYear))
{
var qWeekData = (from x in lst
where x.Date.Year == nYear
select x).Select(x => new TSelectOpt
{
id = DateTimeHelper.GetLocalDateStrNull(x.Date),
text = DateTimeHelper.GetLocalDateStrNull(x.Date)
});
return(qWeekData.ToList());
}
else
{
var qWeekData = (from x in lst
select x).Select(x => new TSelectOpt
{
id = DateTimeHelper.GetLocalDateStrNull(x.Date),
text = DateTimeHelper.GetLocalDateStrNull(x.Date)
});
return(qWeekData.ToList());
}
}
}
public static bool DeleteUser(int id, out string errmsg)
{
bool bOk = false;
errmsg = string.Empty;
using (var mContext = new WarehouseLaborEffEntities())
{
var persons = from p in mContext.sys_user
where p.id == id
select p;
if (persons.Any())
{
var obj = persons.First();
mContext.sys_user.Remove(obj);
try
{
mContext.SaveChanges();
bOk = true;
}
catch (Exception ex)
{
errmsg = ex.Message;
}
}
}
return(bOk);
}
public static bool EnableUser(int uid, bool bEnabled, out string errmsg)
{
bool bOk = false;
using (var context = new WarehouseLaborEffEntities())
{
var persons = from p in context.sys_user
where p.id == uid
select p;
foreach (var obj in persons)
{
obj.IsValid = bEnabled;
}
try
{
context.SaveChanges();
bOk = true;
errmsg = string.Empty;
}
catch (Exception ex)
{
errmsg = ex.Message;
}
}
return(bOk);
}
private static TDatatables GetHCDataBu(string selYear, string bu)
{
int nYear = 0;
int.TryParse(selYear, out nYear);
var res = new TDatatables();
res.kinds = GetHCRows();
using (var context = new WarehouseLaborEffEntities())
{
var qry = (from c in context.v_tbl_hcdata
where 0 == string.Compare(c.Warehouse, bu, true)
orderby c.Date
select c
).AsEnumerable();
var items = from c in qry
where c.Date.Year == nYear
select new {
Date = DateTimeHelper.GetLocalDateStr(c.Date).Substring(0, 7), /*yyyy-MM*/
c.System_Clerk,
c.Receiving,
c.Shipping,
c.RTV_Scrap,
c.Inventory_Control,
c.Overall,
c.Forklift_Driver,
c.Total
};
res.data = items.ToList();
}
return(res);
}
public static bool DeleteHCData(IEnumerable <int> ids, out string sErr)
{
sErr = string.Empty;
var bOk = false;
using (var mContext = new WarehouseLaborEffEntities())
{
var items = from p in mContext.tbl_hcdata
where ids.Contains(p.id)
select p;
if (items.Any())
{
mContext.tbl_hcdata.RemoveRange(items);
try
{
mContext.SaveChanges();
bOk = true;
}
catch (Exception ex)
{
sErr = ex.Message;
}
}
else
{
sErr = "数据已删除";
}
}
return(bOk);
}
public static void Update(sys_user user)
{
using (var context = new WarehouseLaborEffEntities())
{
//user.LastLogon = DateTime.Now;
context.Entry(user).State = EntityState.Modified;
context.SaveChanges();
}
}
internal static List <string> QueryYear(string dataType)
{
if (dataType.Equals("weekdata", StringComparison.InvariantCultureIgnoreCase))
{
using (var context = new WarehouseLaborEffEntities())
{
var lst = (from c in context.v_tbl_weekdata
select c.Date
).AsEnumerable()
.Select(x => x.Date.ToString("yyyy"))
.Distinct().OrderByDescending(x => x);
var lstYear = new List <string>();
lstYear.Add("All");
lstYear.AddRange(lst);
return(lstYear);
}
}
else if (dataType.Equals("monthdata", StringComparison.InvariantCultureIgnoreCase))
{
using (var context = new WarehouseLaborEffEntities())
{
var lst = (from c in context.v_tbl_monthdata
select c.Date
).AsEnumerable()
.Select(x => x.Date.ToString("yyyy"))
.Distinct().OrderByDescending(x => x);
return(lst.ToList());
}
}
else if (dataType.Equals("hcdata", StringComparison.InvariantCultureIgnoreCase))
{
using (var context = new WarehouseLaborEffEntities())
{
var lst = (from c in context.v_tbl_hcdata
select c.Date
).AsEnumerable()
.Select(x => x.Date.ToString("yyyy"))
.Distinct().OrderByDescending(x => x);
return(lst.ToList());
}
}
else if (dataType.Equals("hcrate", StringComparison.InvariantCultureIgnoreCase))
{
using (var context = new WarehouseLaborEffEntities())
{
var lst = (from c in context.v_tbl_hcdata_rate
select c.Date
).AsEnumerable()
.Select(x => x.Date.ToString("yyyy"))
.Distinct().OrderByDescending(x => x);
return(lst.ToList());
}
}
return(new List <string>());
}
public static bool HasRight(string sAD, int nRightID)
{
using (var context = new WarehouseLaborEffEntities())
{
var qry = context.Database.SqlQuery <int>("select FN_Check_UserRight(@ad, @rightID)",
new MySqlParameter("@ad", sAD),
new MySqlParameter("@rightID", nRightID)
);
return(qry.FirstOrDefault() > 0);
}
}
public static bool HasOtherUsers()
{
//sys_user user = null;
using (var context = new WarehouseLaborEffEntities())
{
var peoples = context.sys_user;
if (peoples.Any())
{
return(true);
}
}
return(false);
}
public static List <string> GetHCRateMonth(string selYear)
{
int nYear = 0;
int.TryParse(selYear, out nYear);
using (var context = new WarehouseLaborEffEntities())
{
var qry = (from x in context.v_tbl_hcdata
select x.Date).Distinct().AsEnumerable()
.Where(x => x.Year == nYear)
.Select(x => x.ToString("yyyy-MM"));
return(qry.ToList());
}
}
//========================================
internal static List <TSelectOpt> GetWarehouseList()
{
using (var context = new WarehouseLaborEffEntities())
{
var qry = from c in context.tbl_bu
orderby c.bu
select new TSelectOpt
{
id = c.bu,
text = c.bu
};
return(qry.ToList());
}
}
internal static List <SelectListItem> LoadRoles()
{
using (var context = new WarehouseLaborEffEntities())
{
//暂不考虑排序
var users = from r in context.sys_roles
select new SelectListItem
{
Text = r.RoleName,
Value = r.id,
Selected = false
};
var ret = users.ToList();
return(ret);
}
}
internal static List <TSysUserInfo> LoadAll()
{
using (var context = new WarehouseLaborEffEntities())
{
var users = from x in context.sys_user
select new TSysUserInfo
{
id = x.id,
ADAccount = x.ADAccount,
FullName = x.FullName,
Email = x.Email,
IsValid = x.IsValid
};
return(users.ToList());
}
}
public static void UpdateUserLoginTimeByAd(string sAdName)
{
using (var context = new WarehouseLaborEffEntities())
{
var people = from p in context.sys_user
where (0 == String.Compare(p.ADAccount, sAdName, StringComparison.InvariantCultureIgnoreCase))
select p;
if (people.Any())
{
var user = people.First();
user.LastLogon = DateTime.Now;
context.Entry(user).State = EntityState.Modified;
context.SaveChanges();
}
}
}
internal static List <TSelectOpt> GetMonthdateList()
{
using (var context = new WarehouseLaborEffEntities())
{
var qry = (from c in context.v_tbl_monthdata
group c by c.Date into g
orderby g.Key
select g.FirstOrDefault()
).ToList().Select(x => new TSelectOpt
{
id = DateTimeHelper.GetLocalDateStrNull(x.Date),
text = DateTimeHelper.GetLocalDateStrNull(x.Date)
});
return(qry.ToList());
}
}
public static sys_user GetUserInfoByAd(string sAdName)
{
sys_user user = null;
using (var context = new WarehouseLaborEffEntities())
{
var people = from p in context.sys_user
where (0 == String.Compare(p.ADAccount, sAdName, StringComparison.InvariantCultureIgnoreCase))
select p;
if (people.Any())
{
user = people.First();
}
}
return(user);
}
//取用户的roleID
public static List <string> GetUserRoleID(string sAd)
{
//var userInfo = CommonInfo.CurrentUser;
if (string.IsNullOrEmpty(sAd))
{
return(new List <string>());
}
using (var context = new WarehouseLaborEffEntities())
{
var roleIDs = from r in context.sys_user
join ur in context.sys_user_role_conn
on r.id equals ur.RefUserID
where r.ADAccount == sAd
select ur.RefRoleID;
return(roleIDs.ToList());
}
}
public static bool ChangeRole(string userAD, string[] roleIDs, out string sErr)
{
sErr = string.Empty;
using (var context = new WarehouseLaborEffEntities())
{
var userID = (from u in context.sys_user
where (0 == String.Compare(u.ADAccount, userAD, StringComparison.InvariantCultureIgnoreCase))
select u.id).FirstOrDefault();
if (0 == userID)
{
return(false);
}
//先删后加
var bOk = false;
try
{
var qry = from x in context.sys_user_role_conn
where x.RefUserID == userID
select x;
context.sys_user_role_conn.RemoveRange(qry);
var lst = new List <sys_user_role_conn>();
foreach (var rid in roleIDs)
{
lst.Add(new sys_user_role_conn
{
RefRoleID = rid,
RefUserID = userID
});
}
context.sys_user_role_conn.AddRange(lst);
context.SaveChanges();
bOk = true;
sErr = string.Empty;
}
catch (Exception ex)
{
sErr = ex.Message;
}
return(bOk);
}
}
public static object QueryMonthDataAll(string date, string warehouse)
{
using (var db = new WarehouseLaborEffEntities())
{
var query = db.tbl_monthdata.AsQueryable();
IEnumerable <tbl_monthdata> enQ = null;
if (!string.IsNullOrEmpty(warehouse))
{
query = from x in query
where 0 == string.Compare(x.Warehouse, warehouse, true)
select x;
}
if (!string.IsNullOrEmpty(date))
{
enQ = from x in query.AsEnumerable()
where DateTimeHelper.EquStr(x.Date, date)
select x;
}
else
{
enQ = query.AsEnumerable();
}
var items = enQ.Select(x => new
{
id = x.id,
Date = DateTimeHelper.GetLocalDateStr(x.Date),
Warehouse = x.Warehouse,
HC_FCST = x.HC_FCST,
HC_Actual = x.HC_Actual,
HC_Support = x.HC_Support,
HC_Utilization = x.HC_Utilization.Value.ToString("F"),
Case_ID_in = x.Case_ID_in,
Case_ID_out = x.Case_ID_out,
Pallet_In = x.Pallet_In,
Pallet_Out = x.Pallet_Out,
Jobs_Rec = x.Jobs_Rec,
Jobs_Issue = x.Jobs_Issue,
Reel_ID_Rec = x.Reel_ID_Rec
}).ToList();
return(items);
}
}
public static bool InsertUserInfo(string inputad, ref string errmsg)
{
bool bOk = false;
var adUser = GetAdInfo(inputad, out errmsg);
if (adUser == null)
{
errmsg = "AD login failed!";
return(false);
}
var adInfo = GetUserInfoByAd(inputad);
if (adInfo != null)
{
errmsg = "You had been registered!";
return(false);
}
using (var context = new WarehouseLaborEffEntities())
{
var entity = new sys_user()
{
ADAccount = adUser.ADAccount,
Email = adUser.Email,
FullName = adUser.FirstName + ' ' + adUser.LastName,
IsAdmin = false,
IsValid = true
};
try
{
context.sys_user.Add(entity);
context.SaveChanges();
bOk = true;
errmsg = string.Empty;
}
catch (Exception ex)
{
errmsg = ex.Message;
}
}
return(bOk);
}
public static object QueryHCDataAll(string date, string warehouse)
{
using (var db = new WarehouseLaborEffEntities())
{
var query = db.tbl_hcdata.AsQueryable();
IEnumerable <tbl_hcdata> enQ = null;
if (!string.IsNullOrEmpty(warehouse))
{
query = from x in query
where 0 == string.Compare(x.Warehouse, warehouse, true)
select x;
}
if (!string.IsNullOrEmpty(date))
{
enQ = from x in query.AsEnumerable()
where DateTimeHelper.EquStr(x.Date, date)
select x;
}
else
{
enQ = query.AsEnumerable();
}
var items = enQ.Select(x => new
{
id = x.id,
Date = DateTimeHelper.GetLocalDateStr(x.Date),
Warehouse = x.Warehouse,
Overall = x.Overall,
System_Clerk = x.System_Clerk,
Inventory_Control = x.Inventory_Control,
RTV_Scrap = x.RTV_Scrap,
Receiving = x.Receiving,
Shipping = x.Shipping,
Forklift_Driver = x.Forklift_Driver,
Total = x.Total
}).ToList();
return(items);
}
}
private static TDatatables GetHCRateBu(DateTime date, string bu)
{
var res = new TDatatables();
res.kinds = GetHCRateKinds();
using (var context = new WarehouseLaborEffEntities())
{
var qry = (from c in context.v_tbl_hcdata_rate
where (0 == string.Compare(c.Warehouse, bu, true) &&
c.Date == date
)
select c
).AsEnumerable().Select(c => new
{
//Date = DateTimeHelper.GetLocalDateStr(c.Date).Substring(0, 7),/*yyyy-MM*/
c.System_Clerk,
c.Receiving,
c.Shipping,
c.RTV_Scrap,
c.Inventory_Control,
c.Overall
});
var dat = qry.Single();
//var vals = new List<int>();
//vals.Add(Int2Still(dat.System_Clerk));
//vals.Add(Int2Still(dat.Receiving));
//vals.Add(Int2Still(dat.Shipping));
//vals.Add(Int2Still(dat.RTV_Scrap));
//vals.Add(Int2Still(dat.Inventory_Control));
//vals.Add(Int2Still(dat.Overall));
res.data = dat;
}
return(res);
}
