public Boolean update <T>(T t) where T : class
{
using (se = new schedulingEntities())
{
se.Set(t.GetType()).Attach(t);
se.Entry(t).State = EntityState.Modified;
return(se.SaveChanges() > 0);
}
}
public Boolean deleteWork(int rowNum)
{
using (se = new schedulingEntities())
{
var param = new SqlParameter("@rowNum", rowNum);
var sql = "delete from work_detail where row_num=@rowNum";
return(se.Database.ExecuteSqlCommand(sql, param) > 0);
}
}
public T save <T>(T t)
{
using (se = new schedulingEntities())
{
se.Set(t.GetType()).Add(t);
se.SaveChanges();
return(t);
}
}
/// <summary>
/// 根据排产id查询模块信息
/// </summary>
/// <param name="workId"></param>
/// <returns></returns>
public List <module_info> listByWorkId(int workId)
{
var @params = new SqlParameter("@workId", workId);
string sql = "select * from module_info where id in (select module_id from work_module where work_id = @workId)";
using (se = new schedulingEntities())
{
var result = se.Database.SqlQuery <module_info>(sql, @params);
return(result.ToList());
}
}
/// <summary>
/// 查询所有模块信息
/// </summary>
/// <param name="company">公司</param>
/// <param name="isHasNum">是否过滤没有效率的模块</param>
/// <returns></returns>
public List <ModuleItem> listByNum(string company, Boolean isHasNum)
{
var companyParam = new SqlParameter("@company", company);
string sql = "select mi.*,mt.name as typeName,(select name from module_info as mmi where mmi.id = mi.parent_id) as parentName from module_info as mi left join module_type as mt on mi.type_id = mt.id where mi.company = @company" + (isHasNum ? " and num is not null and num > 0" : "");
using (se = new schedulingEntities())
{
var result = se.Database.SqlQuery <ModuleItem>(sql, companyParam);
return(result.ToList());
}
}
public Boolean deleteBatchByOrderId(int id)
{
using (se = new schedulingEntities())
{
var result = se.order_bom.Where(o => o.order_id == id);
foreach (var orderBom in result)
{
se.Entry <order_bom>(se.Set <order_bom>().Find(orderBom.id)).State = EntityState.Deleted;
}
return(se.SaveChanges() > 0);
}
}
public List <department> getDepartment(String company)
{
var @params = new SqlParameter[] {
new SqlParameter("@company", company),
};
string sql = "select * from department where company=@company ";
using (se = new schedulingEntities())
{
var result = se.Database.SqlQuery <department>(sql, @params);
return(result.ToList());
}
}
public List <paibanbiao_renyuan> getAllList(String company)
{
//var @params = new SqlParameter[]{
// new SqlParameter("@company", company),
//};
//string sql = "select department_name from paibanbiao_renyuan where company=@company group by department_name ";
using (se = new schedulingEntities())
{
//var result = se.Database.SqlQuery<paibanbiao_renyuan>(sql, @params);
var result = se.paibanbiao_renyuan.Where(r => r.company == company);
return(result.ToList());
}
}
public List <user_info> getlist(int skip, int take, string company, string user_code)
{
var @params = new SqlParameter[] {
new SqlParameter("@company", company),
new SqlParameter("@user_code", user_code),
};
string sql = "select * from user_info where company=@company and user_code like '%' + @user_code + '%'";
using (se = new schedulingEntities())
{
var result = se.Database.SqlQuery <user_info>(sql, @params);
return(result.ToList());
}
}
public List <paibanbiao_detail> getList(int skip, int take, String company, string staff_name, string banci)
{
var @params = new SqlParameter[] {
new SqlParameter("@staff_name", staff_name),
new SqlParameter("@banci", banci),
new SqlParameter("@company", company),
};
string sql = "select * from paibanbiao_detail where staff_name like '%' + @staff_name + '%' and b like '%' + @banci + '%' and company=@company ";
using (se = new schedulingEntities())
{
var result = se.Database.SqlQuery <paibanbiao_detail>(sql, @params).OrderBy(pd => pd.c).Skip(skip).Take(take);
return(result.ToList());
}
}
/// <summary>
/// 查询人员信息
/// </summary>
/// <param name="company"></param>
/// <returns></returns>
/// <summary>
/// 查询所有排班汇总
/// </summary>
/// <param name="company"></param>
/// <returns></returns>
public List <order_check> getList(int skip, int take, string order_number, string moudle, string company)
{
var @params = new SqlParameter[] {
new SqlParameter("@moudle", moudle),
new SqlParameter("@order_number", order_number),
new SqlParameter("@company", company),
};
string sql = "select * from order_check where moudle like '%'+ @moudle +'%' and order_number like '%'+ @order_number +'%' and company=@company";
using (se = new schedulingEntities())
{
var result = se.Database.SqlQuery <order_check>(sql, @params).OrderBy(p => p.riqi).Skip(skip).Take(take);
return(result.ToList());
}
}
/// <summary>
/// 查询所有部门
/// </summary>
/// <param name="company"></param>
/// <returns></returns>
public List <department> getList(int skip, int take, string company, string department_name, string view_name)
{
var @params = new SqlParameter[] {
new SqlParameter("@department_name", department_name),
new SqlParameter("@view_name", view_name),
new SqlParameter("@company", company),
};
string sql = "select * from department where department_name like '%'+ @department_name +'%' and view_name like '%'+ @view_name +'%' and company=@company";
using (se = new schedulingEntities())
{
var result = se.Database.SqlQuery <department>(sql, @params).OrderBy(d => d.department_name).Skip(skip).Take(take);
//var result = se.department.Where(b => b.company == company).OrderBy(d => d.department_name).Skip(skip).Take(take);
return(result.ToList());
}
}
/// <summary>
/// 查询所有排班汇总
/// </summary>
/// <param name="company"></param>
/// <returns></returns>
public List <paibanbiao_info> getList(int skip, int take, string department_name, string plan_name, string company)
{
var @params = new SqlParameter[] {
new SqlParameter("@department_name", department_name),
new SqlParameter("@plan_name", plan_name),
new SqlParameter("@remarks1", company),
};
string sql = "select * from paibanbiao_info where department_name like '%'+ @department_name +'%' and plan_name like '%'+ @plan_name +'%' and remarks1=@remarks1";
using (se = new schedulingEntities())
{
//var result = se.Database.SqlQuery<WorkSummary>(sql, @params).OrderBy(w => w.type).Skip(skip).Take(take);
var result = se.Database.SqlQuery <paibanbiao_info>(sql, @params).OrderBy(p => p.riqi).Skip(skip).Take(take);
return(result.ToList());
}
}
public int SummaryCount(string company, int typeId, string orderId)
{
var @params = new SqlParameter[] {
new SqlParameter("@typeId", typeId),
new SqlParameter("@company", company),
new SqlParameter("@orderId", orderId)
};
string sql = "select mt.name as type,mi.name as name,mi.num as num,(select name from module_info where id = mi.parent_id) as parentName,sum(wd.work_num) as workNum from work_module as wm left join module_info as mi on wm.module_id = mi.id left join module_type as mt on mi.type_id = mt.id left join work_detail as wd on wm.work_id = wd.id left join order_info as o on wd.order_id = o.id where wd.company = @company " + (typeId > 0 ? "and mi.type_id = @typeId" : "") + " and o.order_id like '%' + @orderId + '%' group by mt.name,mi.name,mi.num,mi.parent_id";
using (se = new schedulingEntities())
{
var result = se.Database.SqlQuery <WorkSummary>(sql, @params).OrderBy(w => w.type).Count();
return((int)result);
}
}
/// <summary>
/// 查询人员信息
/// </summary>
/// <param name="company"></param>
/// <returns></returns>
public List <paibanbiao_renyuan> getList(int skip, int take, String company, string staff_name, string staff_banci)
{
var @params = new SqlParameter[] {
new SqlParameter("@staff_name", staff_name),
new SqlParameter("@banci", staff_banci),
new SqlParameter("@company", company),
};
string sql = "select * from paibanbiao_renyuan where staff_name like '%'+ @staff_name +'%' and banci like '%'+ @banci +'%' and company=@company";
using (se = new schedulingEntities())
{
var result = se.Database.SqlQuery <paibanbiao_renyuan>(sql, @params).OrderBy(p => p.department_name).Skip(skip).Take(take);
//var result = se.paibanbiao_renyuan.Where(r => r.company == company).OrderBy(r => r.department_name).Skip(skip).Take(take);
return(result.ToList());
}
}
public List <WorkItem> list(string company, int skip, int take, string orderId)
{
var @params = new SqlParameter[] {
new SqlParameter("@company", company),
new SqlParameter("@orderId", orderId),
new SqlParameter("@minRowNumber", skip * take),
new SqlParameter("@maxRowNumber", (skip + 1) * take + 1)
};
string sql = "select * from (select row_number() over(order by wd.id) as rownum,wd.*,oi.order_id as orderId from work_detail as wd left join order_info as oi on wd.order_id = oi.id) as wd where wd.company = @company and wd.orderId like '%' + @orderId + '%' and rownum > @minRowNumber and rownum < @maxRowNumber order by wd.row_num,wd.is_insert asc";
using (se = new schedulingEntities())
{
var result = se.Database.SqlQuery <WorkItem>(sql, @params);
return(result.ToList());
}
}
/// <summary>
/// 条件查询
/// </summary>
/// <param name="company">公司</param>
/// <param name="skip">跳过行数</param>
/// <param name="take">查询行数</param>
/// <param name="code">编码</param>
/// <param name="name">名称</param>
/// <param name="type">类别</param>
/// <returns></returns>
public List <BomInfoItem> list(string company, int skip, int take, string code, string name, string type)
{
using (se = new schedulingEntities())
{
var @params = new SqlParameter[] {
new SqlParameter("@company", company),
new SqlParameter("@code", code),
new SqlParameter("@name", name),
new SqlParameter("@type", type),
new SqlParameter("@minRowNumber", skip * take),
new SqlParameter("@maxRowNumber", (skip + 1) * take + 1)
};
string sql = "select * from (select row_number() over(order by b.id) as rownum,b.*,isnull((select sum(o.use_num*oi.set_num) from order_bom as o left join order_info as oi on o.order_id = oi.id where o.bom_id = b.id), 0) as useNum from bom_info as b where b.company = @company and code like '%' + @code + '%' and name like '%' + @name + '%' and type like '%' + @type + '%') as bom where bom.rownum > @minRowNumber and rownum < @maxRowNumber";
var result = se.Database.SqlQuery <BomInfoItem>(sql, @params);
return(result.ToList());
}
}