public bool Add(Model.role userroler)
{
try
{
string sqldep = string.Format("select * from system where id='{0}'", userroler.system);
DataSet dps = SqliteHelper.ExecuteDataSet(con, sqldep, CommandType.Text);
if (dps != null && dps.Tables.Count > 0)
{
system system = DataTableToList.GetList <system>(dps.Tables[0]).FirstOrDefault();
if (system != null)
{
userroler.system = system.guid;
}
}
string sql =
string.Format
(
"INSERT INTO role(roleName,system_id,roleDM,comment,guid) VALUES('{0}','{1}','{2}','{3}','{4}') ;select last_insert_rowid()",
userroler.roleName, userroler.system, userroler.roleDM, userroler.comment, userroler.guid
);
var ds = SqliteHelper.ExecuteScalar(con, sql, CommandType.Text);
if (Convert.ToInt32(ds) > 0)
{
return(true);
}
return(false);
}
catch
{
return(false);
}
}
public List <torquechecktarget> SelectByContion(Dictionary <string, string> dict, int pagesize, int pageno)
{
try
{
string temp = "";
foreach (var d in dict)
{
if (d.Key == "starttime")
{
temp += string.Format("checkDate>='{0}' and ", d.Value);
continue;
}
if (d.Key == "endtime")
{
temp += string.Format("checkDate<'{0}' and ", d.Value);
continue;
}
temp += string.Format("{0}='{1}' and ", d.Key, d.Value);
}
string contion = temp.Count() > 4 ? temp.Remove(temp.Count() - 4) : "";
contion += string.Format("LIMIT {0},{1}", (pageno * pagesize), pagesize);
string sql = "select * from torquechecktarget where " + contion;
DataSet ds = SqliteHelper.ExecuteDataSet(con, sql, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
return(DataTableToList.GetList <torquechecktarget>(ds.Tables[0]));
}
return(null);
}
catch { return(null); }
}
public bool AddNotReturn(Model.torquecheckrecord checkrecord)
{
try
{
string sqldep = string.Format("select * from torquechecktarget where id='{0}'", checkrecord.TorqueCheckTargetID);
DataSet dps = SqliteHelper.ExecuteDataSet(con, sqldep, CommandType.Text);
if (dps != null && dps.Tables.Count > 0)
{
torquechecktarget dep = DataTableToList.GetList <torquechecktarget>(dps.Tables[0]).FirstOrDefault();
if (dep != null)
{
checkrecord.TorqueCheckTargetID = dep.guid;
}
}
string sql =
string.Format
(
"INSERT INTO torquecheckrecord(TorqueCheckTargetID_id,TesterID,errorRangeMax,errorRangeMin,analyserValue,torqueTargetValue,checkTime,passedFlag,isEffective,isTurn,comment,guid) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}') ;select last_insert_rowid()",
checkrecord.TorqueCheckTargetID, checkrecord.TesterID, checkrecord.errorRangeMax, checkrecord.errorRangeMin, checkrecord.analyserValue, checkrecord.torqueTargetValue, checkrecord.checkTime, checkrecord.passedFlag == true ? 1 : 0, checkrecord.isEffective == true ? 1 : 0, checkrecord.isTurn == true ? 1 : 0, checkrecord.comment, checkrecord.guid
);
var ds = SqliteHelper.ExecuteNonQuery(con, sql, CommandType.Text);
if (Convert.ToInt32(ds) > 0)
{
return(true);
}
return(false);
}
catch
{
return(false);
}
}
public bool Add(List <Model.torquechecktarget> listtorquechecktarget)
{
try
{
string sql = "";
foreach (torquechecktarget torquechecktarget in listtorquechecktarget)
{
string sqldep = string.Format("select * from wrench where id='{0}'", torquechecktarget.wrenchID);
DataSet dps = SqliteHelper.ExecuteDataSet(con, sqldep, CommandType.Text);
if (dps != null && dps.Tables.Count > 0)
{
wrench dep = DataTableToList.GetList <wrench>(dps.Tables[0]).FirstOrDefault();
if (dep != null)
{
torquechecktarget.wrenchID = dep.guid;
}
}
sql +=
string.Format
(
"INSERT INTO torquechecktarget(wrenchID_id,checkDate,qaID,operatorID,torqueTargetValue,errorRangeMax,errorRangeMin,count,arry,is_good,comment,guid) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}') ;",
torquechecktarget.wrenchID, torquechecktarget.checkDate, torquechecktarget.qaID, torquechecktarget.operatorID, torquechecktarget.torqueTargetValue, torquechecktarget.errorRangeMax, torquechecktarget.errorRangeMin, torquechecktarget.count, torquechecktarget.arry, torquechecktarget.is_good == true?1:0, torquechecktarget.comment, torquechecktarget.guid
) + "\r";
}
sql += " select last_insert_rowid()";
var ds = SqliteHelper.ExecuteNonQuery(con, sql, CommandType.Text);
if (Convert.ToInt32(ds) > 0)
{
return(true);
}
return(false);
}
catch { return(false); }
}
public bool Add(Model.duties duty)
{
try
{
string sqldep = string.Format("select * from department where id='{0}'", duty.department);
DataSet dps = SqliteHelper.ExecuteDataSet(con, sqldep, CommandType.Text);
if (dps != null && dps.Tables.Count > 0)
{
department dep = DataTableToList.GetList <department>(dps.Tables[0]).FirstOrDefault();
if (dep != null)
{
duty.department = dep.guid;
}
}
string sql =
string.Format
(
"INSERT INTO duties(department_id,dutiesName,comment,guid) VALUES('{0}','{1}','{2}','{3}') ;select last_insert_rowid();",
duty.department, duty.dutiesName, duty.comment, duty.guid
);
var ds = SqliteHelper.ExecuteScalar(con, sql, CommandType.Text);
if (Convert.ToInt32(ds) > 0)
{
return(true);
}
return(false);
}
catch
{
return(false);
}
}
public string AddReturnGuid(Model.torquechecktarget torquechecktarget)
{
try
{
string sqldep = string.Format("select * from wrench where id='{0}'", torquechecktarget.wrenchID);
DataSet dps = SqliteHelper.ExecuteDataSet(con, sqldep, CommandType.Text);
if (dps != null && dps.Tables.Count > 0)
{
wrench dep = DataTableToList.GetList <wrench>(dps.Tables[0]).FirstOrDefault();
if (dep != null)
{
torquechecktarget.wrenchID = dep.guid;
}
}
string sql =
string.Format
(
"INSERT INTO torquechecktarget(wrenchID_id,checkDate,qaID,operatorID,torqueTargetValue,errorRangeMax,errorRangeMin,count,arry,is_good,comment,guid) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}') ;select last_insert_rowid()",
torquechecktarget.wrenchID, torquechecktarget.checkDate, torquechecktarget.qaID, torquechecktarget.operatorID, torquechecktarget.torqueTargetValue, torquechecktarget.errorRangeMax, torquechecktarget.errorRangeMin, torquechecktarget.count, torquechecktarget.arry, torquechecktarget.is_good == true?1:0, torquechecktarget.comment, torquechecktarget.guid
);
var ds = SqliteHelper.ExecuteScalar(con, sql, CommandType.Text);
return(ds.ToString());
}
catch
{
return("-1");
}
}
public bool addlist(List <Model.wrench> wrenchlist)
{
try
{
string sql = "";
foreach (wrench wrenchtool in wrenchlist)
{
string sqldep = string.Format("select * from wrenchspecies where id='{0}'", wrenchtool.species);
DataSet dps = SqliteHelper.ExecuteDataSet(con, sqldep, CommandType.Text);
if (dps != null && dps.Tables.Count > 0)
{
department dep = DataTableToList.GetList <department>(dps.Tables[0]).FirstOrDefault();
if (dep != null)
{
wrenchtool.species = dep.guid;
}
}
sqldep = string.Format("select * from wrenchstatus where id='{0}'", wrenchtool.status);
dps = SqliteHelper.ExecuteDataSet(con, sqldep, CommandType.Text);
if (dps != null && dps.Tables.Count > 0)
{
wrenchstatus dep = DataTableToList.GetList <wrenchstatus>(dps.Tables[0]).FirstOrDefault();
if (dep != null)
{
wrenchtool.status = dep.guid;
}
}
sql +=
string.Format
(
"INSERT INTO wrench(wrenchCode,wrenchBarCode,rangeMin,rangeMax,factory,createDate,IP,port,species_id,status_id,lastrepair,cycletime,isallowcheck,targetvalue,targetvalue1,targetvalue2,unit,comment,guid,offPset) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}');",
wrenchtool.wrenchCode, wrenchtool.wrenchBarCode, wrenchtool.rangeMin, wrenchtool.rangeMax, wrenchtool.factory, wrenchtool.createDate, wrenchtool.IP, wrenchtool.port, wrenchtool.species, wrenchtool.status, wrenchtool.lastrepair, wrenchtool.cycletime, wrenchtool.isallowcheck == true ? 1 : 0, wrenchtool.targetvalue, wrenchtool.targetvalue1, wrenchtool.targetvalue2, wrenchtool.unit, wrenchtool.comment, wrenchtool.guid, wrenchtool.offPset
) + "\r";
}
sql += "select last_insert_rowid();";
var ds = SqliteHelper.ExecuteNonQuery(con, sql, CommandType.Text);
if (Convert.ToInt32(ds) > 0)
{
return(true);
}
return(false);
}
catch
{
return(false);
}
}
public bool Add(List <Model.users> userlist)
{
try
{
string sql = "";
foreach (users user in userlist)
{
string sqldep = string.Format("select * from department where id='{0}'", user.department);
DataSet dps = SqliteHelper.ExecuteDataSet(con, sqldep, CommandType.Text);
if (dps != null && dps.Tables.Count > 0)
{
department dep = DataTableToList.GetList <department>(dps.Tables[0]).FirstOrDefault();
if (dep != null)
{
user.department = dep.guid;
}
}
string sqlduty = string.Format("select * from duties where id='{0}'", user.duties);
dps = SqliteHelper.ExecuteDataSet(con, sqlduty, CommandType.Text);
if (dps != null && dps.Tables.Count > 0)
{
duties dep = DataTableToList.GetList <duties>(dps.Tables[0]).FirstOrDefault();
if (dep != null)
{
user.duties = dep.guid;
}
}
sql +=
string.Format
(
"INSERT INTO users(username,is_superuser,password,is_staff,joinDate,department_id,duties_id,empID,cardID,phoneNumber,IDNum,mail,comment,guid) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}');",
user.username, user.is_superuser == true?1:0, user.password, user.is_staff == true?1:0, user.joinDate, user.department, user.duties, user.empID, user.cardID, user.phoneNumber, user.IDNum, user.mail, user.comment, user.guid
) + "\r";
}
sql += "select last_insert_rowid()";
var ds = SqliteHelper.ExecuteNonQuery(con, sql, CommandType.Text);
if (Convert.ToInt32(ds) > 0)
{
return(true);
}
return(false);
}
catch
{
return(false);
}
}
public List <Model.borrow> SelectByWrench(string wrench)
{
try
{
string sql = string.Format("select * from borrow where WrenchID_id='{0}' ", wrench);
DataSet ds = SqliteHelper.ExecuteDataSet(con, sql, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
return(DataTableToList.GetList <borrow>(ds.Tables[0]));
}
return(null);
}
catch
{
return(null);
}
}
public Model.wrench selectByBarcode(string barcode)
{
try
{
string sql = string.Format("select * from wrench where wrenchBarCode='{0}'", barcode);
DataSet ds = SqliteHelper.ExecuteDataSet(con, sql, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
return(DataTableToList.GetList <wrench>(ds.Tables[0]).FirstOrDefault());
}
return(null);
}
catch
{
return(null);
}
}
public List <Model.duties> SelectByDepartment(string departmentguid)
{
try
{
string sql = string.Format("select * from duties where department_id='{0}'", departmentguid);
DataSet ds = SqliteHelper.ExecuteDataSet(con, sql, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
return(DataTableToList.GetList <duties>(ds.Tables[0]));
}
return(null);
}
catch
{
return(null);
}
}
public List <Model.users> SelectByCode(string empID)
{
try
{
string sql = string.Format("select * from users where empID='{0}'", empID);
DataSet ds = SqliteHelper.ExecuteDataSet(con, sql, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
return(DataTableToList.GetList <users>(ds.Tables[0]));
}
return(null);
}
catch
{
return(null);
}
}
public Model.users Select(string CardId)
{
try
{
string sql = string.Format("select * from users where cardID='{0}'", CardId);
DataSet ds = SqliteHelper.ExecuteDataSet(con, sql, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
return(DataTableToList.GetList <users>(ds.Tables[0]).FirstOrDefault());
}
return(null);
}
catch
{
return(null);
}
}
public List <Model.users> SelectNameOrCardid(string contion)
{
try
{
string sql = string.Format("select * from users where username='******' or cardID='{1}'", contion, contion);
DataSet ds = SqliteHelper.ExecuteDataSet(con, sql, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
return(DataTableToList.GetList <users>(ds.Tables[0]));
}
return(null);
}
catch
{
return(null);
}
}
public List <Model.users> SelectByCard(string cardid, string password)
{
try
{
string sql = string.Format("select * from users where cardID='{0}' and password='******' ", cardid, password);
DataSet ds = SqliteHelper.ExecuteDataSet(con, sql, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
return(DataTableToList.GetList <users>(ds.Tables[0]));
}
return(null);
}
catch
{
return(null);
}
}
public List <Model.UserToRoleModel.usertorole> selectbyroleid(string roleguid)
{
try
{
string sql = string.Format("select * from usertorole where role_id='{0}'", roleguid);
DataSet ds = SqliteHelper.ExecuteDataSet(con, sql, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
return(DataTableToList.GetList <QDDL.Model.UserToRoleModel.usertorole>(ds.Tables[0]));
}
return(null);
}
catch
{
return(null);
}
}
public List <Model.wrench> SelectBarorcode(string contion)
{
try
{
string sql = string.Format("select * from wrench where wrenchBarCode='{0}' or wrenchCode='{1}'", contion, contion);
DataSet ds = SqliteHelper.ExecuteDataSet(con, sql, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
return(DataTableToList.GetList <wrench>(ds.Tables[0]));
}
return(null);
}
catch
{
return(null);
}
}
public List <Model.torquechecktarget> SelectByDate(DateTime first, DateTime last, string wrenchID)
{
try
{
string sql = string.Format("select * from torquechecktarget where checkDate>='{0}' and checkDate<='{1}' and wrenchID_id='{2}' ", first, last, wrenchID);
DataSet ds = SqliteHelper.ExecuteDataSet(con, sql, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
return(DataTableToList.GetList <torquechecktarget>(ds.Tables[0]));
}
return(null);
}
catch
{
return(null);
}
}
public List <Model.wrench> selectByid(int id)
{
try
{
string sql = string.Format("select * from wrench where id='{0}'", id);
DataSet ds = SqliteHelper.ExecuteDataSet(con, sql, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
return(DataTableToList.GetList <wrench>(ds.Tables[0]));
}
return(null);
}
catch
{
return(null);
}
}
public Model.wrenchspecies selectByGuid(string guid)
{
try
{
string sql = string.Format("select * from wrenchspecies where guid='{0}'", guid);
DataSet ds = SqliteHelper.ExecuteDataSet(con, sql, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
return(DataTableToList.GetList <wrenchspecies>(ds.Tables[0]).FirstOrDefault());
}
return(null);
}
catch
{
return(null);
}
}
public List <Model.role> selectSysidandguid(string systemid, string guid)
{
try
{
string sql = string.Format("select * from role where system_id='{0}' and guid='{1}'", systemid, guid);
DataSet ds = SqliteHelper.ExecuteDataSet(con, sql, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
return(DataTableToList.GetList <role>(ds.Tables[0]));
}
return(null);
}
catch
{
return(null);
}
}
public List <Model.wrenchspecies> selectbyname(string name)
{
try
{
string sql = string.Format("select * from wrenchspecies where speciesName='{0}'", name);
DataSet ds = SqliteHelper.ExecuteDataSet(con, sql, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
return(DataTableToList.GetList <wrenchspecies>(ds.Tables[0]));
}
return(null);
}
catch
{
return(null);
}
}
public List <Model.torquecheckrecord> SelectByCheckTargetID(string checktargetguid)
{
try
{
string sql = string.Format("select * from torquecheckrecord where TorqueCheckTargetID_id='{0}' ", checktargetguid);
DataSet ds = SqliteHelper.ExecuteDataSet(con, sql, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
return(DataTableToList.GetList <torquecheckrecord>(ds.Tables[0]));
}
return(null);
}
catch
{
return(null);
}
}
public List <Model.wrenchstatus> selectAll()
{
try
{
string sql = string.Format("select * from wrenchstatus");
DataSet ds = SqliteHelper.ExecuteDataSet(con, sql, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
return(DataTableToList.GetList <wrenchstatus>(ds.Tables[0]));
}
return(null);
}
catch
{
return(null);
}
}
public List <Model.department> SelectByFlag(bool flag = false)
{
try
{
string sql = string.Format("select * from department where delDepartment={0}", flag == true?1:0);
DataSet ds = SqliteHelper.ExecuteDataSet(con, sql, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
return(DataTableToList.GetList <department>(ds.Tables[0]));
}
return(null);
}
catch
{
return(null);
}
}
public List <Model.department> Select()
{
try
{
string sql = string.Format("select * from department");
DataSet ds = SqliteHelper.ExecuteDataSet(con, sql, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
return(DataTableToList.GetList <department>(ds.Tables[0]));
}
return(null);
}
catch
{
return(null);
}
}
public Model.department SelectById(string Did)
{
try
{
string sql = string.Format("select * from department where id='{0}'", Did);
DataSet ds = SqliteHelper.ExecuteDataSet(con, sql, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
return(DataTableToList.GetList <department>(ds.Tables[0]).FirstOrDefault());
}
return(null);
}
catch
{
return(null);
}
}
public bool add(Model.UserToRoleModel.usertorole usertorolemodel)
{
try
{
string sqldep = string.Format("select * from users where id='{0}'", usertorolemodel.user);
DataSet dps = SqliteHelper.ExecuteDataSet(con, sqldep, CommandType.Text);
if (dps != null && dps.Tables.Count > 0)
{
users dep = DataTableToList.GetList <users>(dps.Tables[0]).FirstOrDefault();
if (dep != null)
{
usertorolemodel.user = dep.guid;
}
}
string sqlduty = string.Format("select * from role where id='{0}'", usertorolemodel.role);
dps = SqliteHelper.ExecuteDataSet(con, sqlduty, CommandType.Text);
if (dps != null && dps.Tables.Count > 0)
{
role dep = DataTableToList.GetList <role>(dps.Tables[0]).FirstOrDefault();
if (dep != null)
{
usertorolemodel.role = dep.guid;
}
}
string sql =
string.Format
(
"INSERT INTO usertorole(user_id,role_id) VALUES('{0}','{1}') ;select last_insert_rowid()",
usertorolemodel.user, usertorolemodel.role
);
var ds = SqliteHelper.ExecuteScalar(con, sql, CommandType.Text);
if (Convert.ToInt32(ds) > 0)
{
return(true);
}
return(false);
}
catch (Exception ex)
{
// MessageAlert.Alert(ex.ToString());
return(false);
}
}
public List <Model.system> selectByname(string projectname)
{
try
{
string sql = string.Format("select * from system where systemName='{0}'", projectname);
DataSet ds = SqliteHelper.ExecuteDataSet(con, sql, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
return(DataTableToList.GetList <system>(ds.Tables[0]));
}
return(null);
}
catch (Exception ex)
{
LogUtil.WriteLog(typeof(string), "执行查询语句异常！" + ex);
return(null);
}
}
public List <Model.wrench> selectPage(int page, int pageNo)
{
string condition = string.Format(" id <=(SELECT id FROM wrench ORDER BY id desc LIMIT {0},{1}) ORDER BY id desc LIMIT {2}", (pageNo - 1) * page, 1, page);
string sql = "select * from wrench where " + condition;
try
{
DataSet ds = SqliteHelper.ExecuteDataSet(con, sql, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
return(DataTableToList.GetList <wrench>(ds.Tables[0]));
}
return(null);
}
catch
{
return(null);
}
}
