public DataTable ExecSQL(string sql, Hashtable htAppend)
{
CmdParameterCollection cpc = new CmdParameterCollection();
DataTable dt = null;
_data = DataAccessFactory.Instance.CreateDataAccess();
try
{
if (htAppend != null)
{
foreach (DictionaryEntry de in htAppend)
{
CmdParameter cp = new CmdParameter(de.Key.ToString(), de.Value);
cpc.Add(cp);
}
}
_data.Open();
dt = _data.ExecuteDataSet(sql, cpc).Tables[0];
}
catch (Exception e)
{
throw e;
}
finally
{
_data.Close();
}
return(dt);
}
public void DeleteMethod(DataRow row)
{
string sql;
_data = DataAccessFactory.Instance.CreateDataAccess();
_data.Open();
var tran = _data.BeginTransaction();
CmdParameterCollection para = new CmdParameterCollection();
try
{
sql = "Delete From T_METHODS Where MID=@MID";
para.Add("@MID", row["MID"]);
_data.ExecuteNonQuery(sql, para);
tran.Commit();
}
catch (Exception e)
{
tran.Rollback();
throw e;
}
finally
{
_data.Close();
}
}
public void InsertMethod(DataTable dt)
{
string sql = "INSERT INTO T_METHODS(MID,MNAME,COMMENTARY,DETAIL,VALUETYPE,METHODTYPE) " +
"Values(@MID,@MNAME,@COMMENTARY,@DETAIL,@VALUETYPE,@METHODTYPE)";
CmdParameterCollection para = new CmdParameterCollection();
para.Add("@MID", dt.Rows[0]["MID"]);
para.Add("@MNAME", dt.Rows[0]["MNAME"]);
para.Add("@COMMENTARY", dt.Rows[0]["COMMENTARY"]);
para.Add("@DETAIL", dt.Rows[0]["DETAIL"]);
para.Add("@VALUETYPE", dt.Rows[0]["VALUETYPE"]);
para.Add("@METHODTYPE", dt.Rows[0]["METHODTYPE"]);
_data = DataAccessFactory.Instance.CreateDataAccess();
_data.Open();
try
{
_data.ExecuteNonQuery(sql, para);
}
catch (Exception e)
{
throw e;
}
finally
{
_data.Close();
}
}
public DataTable ExecProc(string procName, Dictionary <string, object> dic)
{
CmdParameterCollection cpc = new CmdParameterCollection();
_data = DataAccessFactory.Instance.CreateDataAccess();
foreach (KeyValuePair <string, object> de in dic)
{
cpc.Add(de.Key, de.Value);
}
DataTable dt = _data.RunProcedure(procName, cpc, procName).Tables[0];
return(dt);
}
public DataTable GetProcParams(string procName)
{
string sql = string.Empty;
_data = DataAccessFactory.Instance.CreateDataAccess();
switch (_data.DatabaseType)
{
case DatabaseType.DB2:
sql = @"select distinct PARMNAME
from SYSCAT.ROUTINEPARMS
WHERE ROUTINENAME = @PROCNAME";
break;
case DatabaseType.MSSQLServer:
sql = @"select distinct c.name as PARMNAME
from SysColumns c,sysobjects o,systypes b
where c.id = o.id and o.type = 'p' and c.xtype=b.xtype and b.status=0
AND o.name = @PROCNAME ";
break;
// case DatabaseType.OleDBSupported:
// break;
// case DatabaseType.Oracle:
// sql = @"
//select distinct ARGUMENT_NAME AS PARMNAME
//from user_arguments
//WHERE OBJECT_NAME =@PROCNAME and PACKAGE_NAME = @PACKAGENAME";
// break;
}
if (string.IsNullOrEmpty(sql))
{
return(null);
}
CmdParameterCollection qpc = new CmdParameterCollection();
//if (_data.DatabaseType == DatabaseType.Oracle)
//{
// qpc.Add("@PROCNAME", procName.Split('.')[1]);
// qpc.Add("@PACKAGENAME", procName.ToUpper().Split('.')[0]);
//}
//else
qpc.Add("@PROCNAME", procName.ToUpper());
return(_data.ExecuteDataSet(sql, qpc).Tables[0]);
}
public string GetInfo(string openId)
{
Dictionary <string, object> dicRst = new Dictionary <string, object>();
IDataAccess data = null;
try
{
API api = new API();
data = DataAccessFactory.Instance.CreateDataAccess();
data.Open();
CmdParameterCollection paras = new CmdParameterCollection();
paras.Add("@openId", openId);
IDataReader reader = data.ExecuteReader(@"
select * from SignRecord
where OpenId = @openId", paras);
if (reader.Read())
{
dicRst.Add("count", 1);
dicRst.Add("type", reader["UserType"]);
dicRst.Add("code", reader["Code"]);
dicRst.Add("name", reader["UserName"]);
dicRst.Add("phone", reader["PhoneNumber"]);
}
else
{
dicRst.Add("count", 0);
}
}
catch (Exception ex)
{
ErrorLog.WriteLog(ex);
dicRst.Add("errmsg", "出现错误，请联系管理员");
}
finally
{
data.Close();
}
return(dicRst.ToJsonString());
}
public string SignIn(string type, string openId, string code, string name, string phoneNumber)
{
Dictionary <string, object> dicRst = new Dictionary <string, object>();
IDataAccess data = null;
try
{
data = DataAccessFactory.Instance.CreateDataAccess();
data.Open();
if (type == "Staff")
{
CmdParameterCollection paras = new CmdParameterCollection();
paras.Add("@WorkNumber", code);
paras.Add("@UserName", name);
object obj = data.ExecuteScalar(@"
select count(1) from Staff
where WorkNumber=@WorkNumber and UserName=@UserName ", paras);
if (obj == null || obj == DBNull.Value)
{
dicRst.Add("errmsg", "工号与姓名不符，请核对");
}
else
{
if (Convert.ToInt32(obj) == 1)
{
CmdParameterCollection paras3 = new CmdParameterCollection();
paras3.Add("@Code", code);
IDataReader reader = data.ExecuteReader(@"
select * from SignRecord
where Code=@Code", paras3);
if (reader.Read())
{
dicRst.Add("result", "您已经于" +
Convert.ToDateTime(reader["CreateTime"]).ToString("yyyy-MM-dd HH:mm:ss") +
"进行过签到，无需再次签到");
reader.Close();
}
else
{
reader.Close();
CmdParameterCollection paras2 = new CmdParameterCollection();
paras2.Add("@Id", BitConverter.ToInt64(Guid.NewGuid().ToByteArray(), 0));
paras2.Add("@OpenId", openId);
paras2.Add("@UserType", type);
paras2.Add("@Code", code);
paras2.Add("@UserName", name);
paras2.Add("@PhoneNumber", phoneNumber);
paras2.Add("@CreateTime", DateTime.Now);
data.ExecuteNonQuery(@"
insert into SignRecord(Id,OpenId,UserType,Code,UserName,PhoneNumber,CreateTime)
values(@Id,@OpenId,@UserType,@Code,@UserName,@PhoneNumber,@CreateTime)", paras2);
dicRst.Add("result", "签到成功！");
}
}
else
{
dicRst.Add("errmsg", "工号与姓名不符，请核对");
}
}
}
else
{
dicRst.Add("errmsg", "暂未开通服务");
}
}
catch (Exception ex)
{
ErrorLog.WriteLog(ex);
dicRst.Add("errmsg", "签到错误，请联系管理员");
}
finally
{
data.Close();
}
return(dicRst.ToJsonString());
}
public void UpdateMethod(DataTable dt)
{
string sql;
_data = DataAccessFactory.Instance.CreateDataAccess();
_data.Open();
var tran = _data.BeginTransaction();
CmdParameterCollection para = new CmdParameterCollection();
try
{
foreach (DataRow dr in dt.Rows)
{
switch (dr.RowState)
{
case DataRowState.Added:
sql = "INSERT INTO T_METHODS(MID,MNAME,COMMENTARY,DETAIL,VALUETYPE,METHODTYPE)" +
"Values(@MID,@MNAME,@COMMENTARY,@DETAIL,@VALUETYPE,@METHODTYPE)";
para.Add("@MID", dt.Rows[0]["MID"]);
para.Add("@MNAME", dt.Rows[0]["MNAME"]);
para.Add("@COMMENTARY", dt.Rows[0]["COMMENTARY"]);
para.Add("@DETAIL", dt.Rows[0]["DETAIL"]);
para.Add("@VALUETYPE", dt.Rows[0]["VALUETYPE"]);
para.Add("@METHODTYPE", dt.Rows[0]["METHODTYPE"]);
break;
case DataRowState.Modified:
sql = "UPDATE T_METHODS SET MNAME=@MNAME,COMMENTARY=@COMMENTARY,DETAIL=@DETAIL,METHODTYPE=@METHODTYPE Where MID=@MID ";
para.Add("@MNAME", dt.Rows[0]["MNAME"]);
para.Add("@COMMENTARY", dt.Rows[0]["COMMENTARY"]);
para.Add("@DETAIL", dt.Rows[0]["DETAIL"]);
para.Add("@VALUETYPE", dt.Rows[0]["VALUETYPE"]);
para.Add("@METHODTYPE", dt.Rows[0]["METHODTYPE"]);
para.Add("@MID", dt.Rows[0]["MID"]);
break;
case DataRowState.Deleted:
sql = "Delete From T_METHODS Where MID=@MID ";
para.Add("@MID", dt.Rows[0]["MID"]);
break;
default:
continue;
}
_data.ExecuteNonQuery(sql, para);
}
tran.Commit();
}
catch (Exception e)
{
tran.Rollback();
throw e;
}
finally
{
_data.Close();
}
}