public static IEnumerable <QcJob> GetJob(QcUser user)
{
string name = user.UserID;
string sql = "select distinct j.* from " + QcProject.TableName + " p,"
+ QcTask.TableName + " t," + QcJob.TableName + " j where (p.项目编号=t.项目编号 and"
+ " t.任务编号=j.任务编号) and (t.任务负责人=' " + name + "' or t.创建人='" + name +
"' or p.项目负责人='" + name + "' or p.创建人='" + name + "' or j.创建人='" + name +
"' or j.作业员='" + name + "') order by j.修改日期 desc";
var rows = DbHelper.Query(sql);
return(rows.Select(t => new QcJob(t)));
}
public static IEnumerable <string> GetUserByPermission(string Permission)
{
//获取具有指定权限的用户列表
string sql = "select distinct u.* from QC_USE_USERINFO u,QC_USE_ROLE r,QC_USE_PERMISSION p,QC_USE_PERMISSIONENUM pe " +
"where pe.权限枚举='" + Permission + "' and pe.权限编码=p.权限编码 and p.角色编码=r.角色编码 and r.用户id=u.用户id";
var lst = DbHelper.Query(sql);
if (lst == null)
{
return(new List <string>());
}
return(lst.Select(r => r.Field <string>(QcUser.NameField)));
}
public static List <QcTask> GetTask(QcProject pro)
{
var pls = DbHelper.Query("Select * from " + TableName + " where 项目编号='" + pro.Code + "'");
if (pls == null)
{
return(null);
}
return((from p in pls select new QcTask(p)
{
Parent = pro
}).ToList());
}
/// <summary>
/// 初始化用户信息到内存
/// </summary>
static public void InitUsers()
{
var Userss = DbHelper.Query("select * from QC_USE_USERINFO");
if (Userss != null)
{
lstUsers = (from p in Userss select new QcUser(p)).ToList();
}
if (lstUsers == null)
{
lstUsers = new List <QcUser>();
}
}
public static List <QcTask> GetTaskBySql(string sql)
{
var pls = DbHelper.Query(sql);
if (pls == null)
{
return(null);
}
return((from p in pls select new QcTask(p)
{
Parent = null
}).ToList());
}
public static IEnumerable <string> GetUserByRole(string Role)
{
//获取具有指定权限的用户列表
string sql = "select distinct u.* from QC_USE_USERINFO u,QC_USE_ROLE r,QC_USE_ROLEENUM re " +
"where r.用户id=u.用户id and r.角色编码=re.角色编码 and re.角色名称='" + Role + "'";
var lst = DbHelper.Query(sql);
if (lst == null)
{
return(new List <string>());
}
return(lst.Select(r => r.Field <string>(QcUser.NameField)));
}
public static List <QcJob> GetJob(QcTask task)
{
var pls = DbHelper.Query("Select * from " + TableName + " where 任务编号='" + task.Code + "'");
if (pls == null)
{
return(null);
}
return((from p in pls select new QcJob(p)
{
Parent = task
}).ToList());
}
/// <summary>
/// where 检查项编码 like=expr
/// </summary>
/// <param name="expr"></param>
/// <returns></returns>
public static List <QcCheckEntry> GetCheckEntry(string expr)
{
var pls = DbHelper.Query("Select * from QC_EVA_CHECKENTRY where 检查项编码 like '" + expr + "%'");
if (pls == null)
{
return(new List <QcCheckEntry>());
}
return((from p in pls select new QcCheckEntry(p)
{
Parent = null
}).ToList());
}
public List <QcPermissionEnum> InitPermission()
{
List <QcPermissionEnum> lstPermissionEnum = new List <QcPermissionEnum>();
var sql = "select * from QC_USE_PERMISSION where 角色编码='" + this.RoleCode + "'";
foreach (var v in DbHelper.Query(sql))
{
var qp = new QcPermission(v);
var qpe = QcPermissionEnum.GetPermissionFromCode(v.Field <string>("权限编码"));
qp.PermissionEnum = qpe;
lstPermissionEnum.Add(qpe);
}
return(lstPermissionEnum);
}
public virtual void Refresh()
{
string sql = GetRefreshSql();
var d = DbHelper.Query(sql);
if (d != null)
{
if (d.Count() > 0)
{
row = d.First();
this.RiseRefresh();
}
}
}
/// <summary>
/// where expr is not null
/// </summary>
/// <param name="expr"></param>
/// <returns></returns>
public static List <QcCheckEntry> GetCheckEntrySql(string expr)
{
string sql = expr == "" ? "Select * from QC_EVA_CHECKENTRY" : "Select * from QC_EVA_CHECKENTRY where " + expr;
var pls = DbHelper.Query(sql);
if (pls == null)
{
return(new List <QcCheckEntry>());
}
return((from p in pls select new QcCheckEntry(p)
{
Parent = null
}).ToList());
}
public static List <QcJob> GetJob(string expr)
{
// QcLog.LogString(":"+expr);
var pls = DbHelper.Query("Select * from " + TableName + ((expr != "") ? " where " + expr: ""));
if (pls == null)
{
return(new List <QcJob>());
}
return((from p in pls select new QcJob(p)
{
Parent = null
}).ToList());
}
/// <summary>
/// 刷新数据行，以获得一致性数据
/// </summary>
private void Refresh()
{
string expr = GetExpr();
string sql = "select * from " + tablename + " where " + expr;
var d = DbHelper.Query(sql);
if (d != null)
{
if (d.Count() > 0)
{
row = d.First();
}
}
}
public static IEnumerable <QcMsg> GetUserMsg(string name)
{
List <QcMsg> lstmsg = new List <QcMsg>();
string sql = "select * from QC_USE_MSG where 接收者='" + name + "' and 标记<>'1'";
var msgs = DbHelper.Query(sql);
if (msgs != null)
{
foreach (var v in msgs)
{
lstmsg.Add(new QcMsg(v));
}
}
return(lstmsg);
}
public void statistics()
{
//string sql = "update " + QcEvaCheckReCode.TableName + " t set t.A类错误='',t.B类错误='',t.C类错误='',t.D类错误='',t.备注='', t.描述类型='' where 数据ID='" + Data.数据ID + "' ";
//DbHelper.Execute(sql);
//string errorsql = "select cd.数据id,cr.检查项编码,ec.错误id,ec.错漏数量,ec.检查对象,ec.错误描述,ec.错漏类别,ec.描述类型,ec.备注 " +
// " from qc_ins_erorecode ec, Qc_Pro_Checkdata cd, Qc_Ins_Checkrule cr,qc_pro_job j " +
//" where j.方案id=cr.方案id and cd.作业编号=j.作业编号 and ec.数据id=cd.数据id and ec.规则id=cr.规则id and ec.数据id='" + Data.数据ID + "'";
string errorsql = "select cd.数据id,cr.检查项编码,ec.错误id,ec.错漏数量,ec.检查对象,ec.错误描述,ec.错漏类别,ec.描述类型,ec.参数,ec.是否确认 " +
" from qc_ins_erorecode ec, Qc_Pro_Checkdata cd, Qc_Ins_Checkrule cr " +
" where ec.数据id=cd.数据id and ec.规则id=cr.规则id and ec.数据id='" + Data.数据ID + "' and ec.是否确认=-1";
var errors = DbHelper.Query(errorsql).Select(t => new QcEvaDataRow(t, t["检查项编码"].ToString()));
lst.ForEach(t => t.statistics(errors));
//Parallel.ForEach(lst, t => t.statistics());
}
public bool ChangePassword(string newpassword, string oldpassword)
{
string sql = "select * from QC_USE_USERINFO where 用户名='" + this.UserName + "' and 用户密码='" + QcEncrypt.Md5Hash(this.UserName + QcEncrypt.Md5Hash(oldpassword)) + "'";
var v = DbHelper.Query(sql);
if (v != null)
{
if (v.Count() > 0)
{
QcUser u = new QcUser(v.First());
u["用户密码"] = QcEncrypt.Md5Hash(this.UserName + QcEncrypt.Md5Hash(newpassword));
return(u.Update());
}
}
return(false);
}
static private void InitRole()
{
if (lstRoleEnums == null)
{
QcNode.NodeUpdateToDb += QcNode_NodeUpdateToDb;
}
lstRoleEnums = new List <QcRoleEnum>();
var Roles = DbHelper.Query("select * from QC_USE_RoleENUM");
foreach (var v in Roles)
{
var qr = new QcRoleEnum(v);
qr.InitPermission();
lstRoleEnums.Add(qr);
}
}
public static QcMsg GetMsg(string msg)
{
var t = msg.Split('|');
string sql = "select * from QC_USE_MSG where 消息编号='" + t[0] + "'";
var rows = DbHelper.Query(sql);
if (rows != null)
{
var v = rows.FirstOrDefault();
if (v != null)
{
return(new QcMsg(v));
}
}
return(null);
}
public static QcJob GetJobByid(string expr)
{
// QcLog.LogString(":"+expr);
var pls = DbHelper.Query("Select * from " + TableName + ((expr != "") ? " where 作业编号='" + expr + "'" :""));
if (pls == null)
{
return(null);
}
{
return((from p in pls select new QcJob(p)
{
Parent = null
}).ToList().FirstOrDefault());
}
}
/// <summary>
/// 重载，完成分组编码和分组名称的转换
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
void InitRoles()
{
lstRoleEnums = new List <QcRoleEnum>();
lstRoles = new List <QcRole>();
var sql = "select * from QC_USE_ROLE where 用户ID='" + this.UserID + "'";
foreach (var v in DbHelper.Query(sql))
{
var qp = new QcRole(v);
var qpe = QcRoleEnum.GetRoleFromCode(v.Field <string>("角色编码"));
qp.roleenum = qpe;
lstRoles.Add(qp);
lstRoleEnums.Add(qpe);
}
RefreshPermission();
}
public void LoadErrors()
{
lst.Clear();
string errorsql = "select cd.数据id,cr.检查项编码,ec.错误id,ec.错漏数量,ec.检查对象,ec.错误描述,ec.错漏类别,ec.描述类型,ec.参数,ec.是否确认,ec.规则id" +
" from qc_ins_erorecode ec, Qc_Pro_Checkdata cd, Qc_Ins_Checkrule cr " +
" where ec.数据id=cd.数据id and ec.规则id=cr.规则id and ec.数据id='"
+ Data.数据ID + "' and cr.检查项编码='" + checkentry.Code + "'"
+ " and ec.是否确认=-1 order by ec.错误id"
;
var erros = DbHelper.Query(errorsql);
if (erros != null)
{
foreach (var e in erros)
{
Add(new QcEvaDataRow(e, checkentry));
}
}
}
public static IEnumerable <QcDevice> GetDevices(string expr = "", string orderby = "")
{
if (expr != "")
{
expr = " where " + expr;
}
if (orderby != "")
{
expr = expr + orderby;
}
try
{
return(DbHelper.Query("select * from QC_PRO_DEVICE " + expr).Select(t => new QcDevice(t)));
}
catch
{
return(GetDevices());
}
}
public static IEnumerable <QcStandard> GetStandards(string expr = "", string orderby = "")
{
if (expr != "")
{
expr = " where " + expr;
}
if (orderby != "")
{
expr = expr + orderby;
}
try
{
return(DbHelper.Query("select * from QC_PRO_STANDARD " + expr).Select(t => new QcStandard(t)));
}
catch
{
return(GetStandards());
}
}
public static IEnumerable <QcCompany> GetCompanys(string expr = "", string orderby = "")
{
if (expr != "")
{
expr = " where " + expr;
}
if (orderby != "")
{
expr = expr + orderby;
}
try
{
return(DbHelper.Query("select * from QC_PRO_COMPANY " + expr).Select(t => new QcCompany(t)));
}
catch
{
return(GetCompanys());
}
}
public static QcCheckProject GetCheckProjectByid(string id)
{
var pls = DbHelper.Query("Select * from " + TableName + " where " + NameField + "='" + id + "'");
if (pls.Count() > 0)
{
if (pls.First() != null)
{
return(new QcCheckProject(pls.First()));
}
else
{
return(null);
}
}
else
{
return(null);
}
}
static public QcUser RefreshUser(string strname)
{
var tu = lstUsers.FirstOrDefault(t => t.Name == strname);
if (tu != null)
{
tu.Refresh();
return(tu);
}
string sql = "select * from " + TableName + " where 姓名='" + strname + "'";
var users = DbHelper.Query(sql);
if (users != null)
{
var u = users.FirstOrDefault();
if (u != null)
{
var user = new QcUser(u);
lstUsers.Add(user);
return(user);
}
}
return(null);;
}
public static IEnumerable <QcEvaData> GetEvaData(QcJob job)
{
// QcProductLevel level = QcProductLevel.QcProductLevels.FirstOrDefault(t => t.Code == job["产品级别编码"]);
QcCheckProject prj = QcCheckProject.GetCheckProjectByid(job["方案ID"]);
if (prj != null)
{
QcProductType type = QcProductType.GetProductTypeById(prj["产品类别编码"]);
Dictionary <string, QcCheckEntry> lst = new Dictionary <string, QcCheckEntry>();
type.Nodes.ForEach(t => t.Nodes.ForEach(tt => tt.Nodes.ForEach(ttt => lst.Add(ttt.Code, ttt))));
/* var rows = DbHelper.Query("select distinct d.数据名称,j.作业编号,d.数据id,r.检查项编码,e.错误id,e.错漏数量,e.检查对象,e.错误描述,e.错漏类别 from qc_pro_checkdata d,qc_pro_job j,qc_ins_checkproject p,qc_ins_checkrule r,qc_ins_erorecode e " +
* "where j.作业编号=d.作业编号 and e.规则id=r.规则id and e.数据id=d.数据id and e.错漏类别!='Undefined' and j.作业编号='" + job.Code + "'");
* */
/*var rows = DbHelper.Query("select dd.数据名称,dd.作业编号,dd.数据id,dd.检查项编码,e.错误id,e.错漏数量,e.检查对象,e.错误描述,e.错漏类别 " +
* " from (select distinct d.数据名称,j.作业编号,d.数据id,r.检查项编码 " +
* " from qc_pro_checkdata d,qc_pro_job j,qc_ins_checkrule r " +
* "where j.作业编号=d.作业编号 and r.方案id=j.方案id and " +
* "j.作业编号 = '" + job.Code + "') dd left join qc_ins_erorecode e on e.数据id=dd.数据id");
* */
string sql = "select distinct d.数据名称,d.作业编号,d.数据id,ce.检查项编码,ce.结果值枚举 " +
" from qc_pro_checkdata d,qc_pro_job j,qc_pro_producttype pt,qc_eva_quaelement qe,qc_eva_subquaelement sqe,qc_eva_checkentry ce " +
" where j.作业编号=d.作业编号 and j.产品类别编码=pt.产品类别编码 and pt.产品类别编码=qe.产品类别编码 and qe.质量元素编码=sqe.质量元素编码 and sqe.质量子元素编码=ce.质量子元素编码 and " +
" j.作业编号 like '" + job.Code + "' order by d.数据名称 ";
#if GenErrorCode
string errorsql = "select cd.数据id,cr.检查项编码,ec.错误id,ec.错漏数量,ec.检查对象,ec.错误描述,ec.错漏类别,ec.描述类型,ec.参数 " +
" from qc_ins_erorecode ec, Qc_Pro_Checkdata cd, Qc_Ins_Checkrule cr,qc_pro_job j " +
" where j.方案id=cr.方案id and cd.作业编号=j.作业编号 and ec.数据id=cd.数据id and ec.规则id=cr.规则id and j.作业编号='" + job.Code + "'";
var errors = DbHelper.Query(errorsql);
var errorgroups = errors.GroupBy(t => t["数据id"].ToString());
string crsql = "select * from Qc_Pro_Job j,Qc_Ins_Checkproject cp,qc_ins_checkrule cr where j.方案id=cp.方案id and cp.方案id=cr.方案id and j.作业编号='" + job.Code + "'";
var crs = DbHelper.Query(crsql).Select(t => new { ID = t["规则ID"], Code = t["检查项编码"] });
#endif
var rows = DbHelper.Query(sql);
if (rows == null)
{
return(null);
}
var datas = rows.Select(t => new QcEvaDataRow(t, lst[t.Field <string>("检查项编码")]));
List <QcEvaData> data = new List <QcEvaData>();
foreach (var ds in datas.GroupBy(dst => dst.数据ID))
{
#if GenErrorCode
var errorgroup = errorgroups.FirstOrDefault(t => t.Key == ds.First().数据ID);
#endif
string recodesql = "select * from qc_eva_checkrecode t where 数据ID='" + ds.First().数据ID + "'";
var recoderows = DbHelper.Query(recodesql);
IEnumerable <QcEvaCheckReCode> recodes = null;
if (recoderows != null)
{
recodes = recoderows.Select(t => new QcEvaCheckReCode(t));
}
QcEvaData evadata = new QcEvaData(ds.First());
data.Add(evadata);
foreach (var qds in ds.GroupBy(qdst => qdst.质量元素编码))
{
QcEvaQaData evaqadata = new QcEvaQaData(qds.First().checkentry.Parent.Parent);
evadata.Add(evaqadata);
foreach (var sqds in qds.GroupBy(sqdst => sqdst.质量子元素编码))
{
QcEvaSubQaData subqadata = new QcEvaSubQaData(sqds.First().checkentry.Parent);
evaqadata.Add(subqadata);
foreach (var ceds in sqds.GroupBy(cedst => cedst.检查项编码))
{
QcEvaCheckReCode recode = null;
if (recodes != null)
{
recode = recodes.FirstOrDefault(t => t["检查项编码"] == ceds.First().检查项编码);
}
QcEvaCheckData checkdata = new QcEvaCheckData(ceds.First(), recode);
subqadata.Add(checkdata);
#if GenErrorCode
if (errorgroup != null)
{
//foreach (var dr in errorgroup.Where(
// t => (t["数据id"].ToString() == ceds.First().数据ID) && (t["检查项编码"].ToString() == ceds.First().检查项编码)))
// {
// checkdata.Add(new QcEvaDataRow(dr, ceds.First().checkentry));
// }
}
else
{
var dr = ceds.First();
int count = r.Next() % 4 + 65;
char change = (char)count;
int n = 1;
var checkentry = dr.checkentry;
//"Y/N", "R:R0", "M;M0", "A?B?C?D?"
switch (checkentry["结果值枚举"])
{
case "M;M0":
n = 1;
break;
default:
n = r.Next() % 6 + 5;
break;
}
for (int i = 0; i < n; i++)
{
QcErrorCode er = new QcErrorCode();
switch (checkentry["结果值枚举"])
{
case "M;M0":
er["参数"] = "M=5";
break;
case "R:R0":
er["参数"] = "N=" + (2000 + r.Next() % 13000).ToString();
break;
default:
n = r.Next() % 6 + 5;
break;
}
er["检查对象"] = "11111";
er["错误描述"] = "AAA";
er["数据ID"] = dr.数据ID;
er["作业编号"] = dr["作业编号"];
er["描述类型"] = "1";
er["是否确认"] = "1";
er["错漏数量"] = "1";
er["错漏类别"] = change.ToString();
er["规则ID"] = crs.Where(t => t.Code.ToString() == dr.检查项编码).First().ID.ToString();
er.Update();
}
}
#endif
}
}
}
evadata.Calc();
}
return(data);
}
return(null);
}
public static IEnumerable <QcStandardBorrow> GetStandardBorrowByStandard(QcStandard standard)
{
return(DbHelper.Query("select * from QC_PRO_STANDARDBORROW where 标准ID='" + standard.Code + "'").Select(t => new QcStandardBorrow(t)));
}
/// <summary>
/// where expr is not null
/// </summary>
/// <param name="expr"></param>
/// <returns></returns>
public static List <QcCheckRule> GetCheckRule(string expr)
{
var pls = DbHelper.Query("Select * from " + TableName + " where " + expr);
return((from p in pls select new QcCheckRule(p)).ToList());
}
public static IEnumerable <QcCheckData> GetCheckData(QcJob job)
{
var rows = DbHelper.Query("select * from " + QcCheckData.TableName + " where 作业编号='" + job.Code + "'");
return(rows.Select(t => new QcCheckData(t)));
}