private void InnerAdd(KeyMan keyman, string TableName)
{
DbParamDict dbParamDict = new DbParamDict();
AddAllFields(dbParamDict, keyman);
string sql = "insert into " + TableName + dbParamDict.GetSqlString();
SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, sql, dbParamDict.GetParams());
}
public void Add(KeyMan keyman)
{
if (TMIsKey(keyman.KeyTMNumber))
{
throw new Exception(string.Format("[{0}]{1}已经是关键人!", keyman.KeyTMNumber, keyman.KeyTMName));
}
keyman.eOpt = Convert.ToInt32(EKeyTrainmanOpt.KTMAdd);
InnerAdd(keyman, "tab_keyTrainman");
UpdateTMKeyState(1, keyman.KeyTMNumber);
InnerAdd(keyman, "tab_keyTrainman_His");
}
public void Update(KeyMan keyman)
{
DbParamDict dbParamDict = new DbParamDict();
keyman.eOpt = Convert.ToInt32(EKeyTrainmanOpt.KTMModify);
AddAllFields(dbParamDict, keyman);
string sql = "update tab_keyTrainman " + dbParamDict.GetUpdateFieldsString() + " where strGUID = @strGUID";
SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, sql, dbParamDict.GetParams());
InnerAdd(keyman, "tab_keyTrainman_His");
}
public void Del(KeyMan keyman)
{
string sql = "delete from tab_keyTrainman where strGUID = @strGUID";
DbParamDict dbParamDict = new DbParamDict();
dbParamDict.Add("strGUID", keyman.ID);
keyman.eOpt = Convert.ToInt32(EKeyTrainmanOpt.KTMdel);
SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, sql, dbParamDict.GetParams());
UpdateTMKeyState(0, keyman.KeyTMNumber);
InnerAdd(keyman, "tab_keyTrainman_His");
}
public LCOutput Update(string data)
{
outPut.Clear();
try
{
KeyMan keyman = Newtonsoft.Json.JsonConvert.DeserializeObject <KeyMan>(data);
new DBKeyMan().Update(keyman);
}
catch (Exception ex)
{
outPut.result = 1;
outPut.resultStr = ex.Message.ToString();
}
return(outPut);
}
private void AddAllFields(DbParamDict dbParamDict, KeyMan keyman)
{
dbParamDict.Add("strGUID", keyman.ID);
dbParamDict.Add("strTrainmanNumber", keyman.KeyTMNumber);
dbParamDict.Add("strTrainmanName", keyman.KeyTMName);
dbParamDict.Add("strWorkShopGUID", keyman.KeyTMWorkShopGUID);
dbParamDict.Add("strWorkShopName", keyman.KeyTMWorkShopName);
dbParamDict.Add("strCheJianGUID", keyman.KeyTMCheDuiGUID);
dbParamDict.Add("strCheJianName", keyman.KeyTMCheDuiName);
dbParamDict.Add("dtStartTime", keyman.KeyStartTime);
dbParamDict.Add("dtEndTime", keyman.KeyEndTime);
dbParamDict.Add("strReason", keyman.KeyReason);
dbParamDict.Add("strAnnouncements", keyman.KeyAnnouncements);
dbParamDict.Add("strRegisterNumber", keyman.RegisterNumber);
dbParamDict.Add("strRegisterName", keyman.RegisterName);
dbParamDict.Add("dtRegisterTime", keyman.RegisterTime);
dbParamDict.Add("strClientNumber", keyman.ClientNumber);
dbParamDict.Add("strClientName", keyman.ClientName);
dbParamDict.Add("eOpt", keyman.eOpt);
}
private List <KeyMan> InnerGet(KMQueryQC queryQC, string TableName)
{
List <KeyMan> ret = new List <KeyMan>();
string sql = "select * from " + TableName + " where 1=1 ";
if (!string.IsNullOrEmpty(queryQC.WorkShopGUID))
{
sql += " and strWorkShopGUID = @strWorkShopGUID";
}
if (!string.IsNullOrEmpty(queryQC.CheDuiGUID))
{
sql += " and strCheJianGUID = @strCheJianGUID";
}
if (!string.IsNullOrEmpty(queryQC.KeyTMNumber))
{
sql += " and strTrainmanNumber = @strTrainmanNumber";
}
if (!string.IsNullOrEmpty(queryQC.KeyTMName))
{
sql += " and strTrainmanName = @strTrainmanName";
}
if (queryQC.RegisterEndTime != null)
{
sql += " and dtRegisterTime >= @dtRegisterStartTime";
}
if (queryQC.RegisterStartTime != null)
{
sql += " and dtRegisterTime <= @dtRegisterEndTime";
}
sql += " order by dtRegisterTime desc,eOpt desc";
DbParamDict dbParamDict = new DbParamDict();
dbParamDict.Add("strWorkShopGUID", queryQC.WorkShopGUID, ParamDataType.dtString);
dbParamDict.Add("strCheJianGUID", queryQC.CheDuiGUID, ParamDataType.dtString);
dbParamDict.Add("strTrainmanNumber", queryQC.KeyTMNumber, ParamDataType.dtString);
dbParamDict.Add("strTrainmanName", queryQC.KeyTMName, ParamDataType.dtString);
dbParamDict.Add("dtRegisterStartTime", queryQC.RegisterStartTime, ParamDataType.dtDateTime);
dbParamDict.Add("dtRegisterEndTime", queryQC.RegisterEndTime, ParamDataType.dtDateTime);
DataTable dt = SqlHelper.ExecuteDataset(SqlHelper.ConnString, CommandType.Text, sql, dbParamDict.GetParams()).Tables[0];
KeyMan keyman;
foreach (DataRow dr in dt.Rows)
{
keyman = new KeyMan();
keyman.ID = ObjectConvertClass.static_ext_string(dr["strGUID"]);
keyman.KeyAnnouncements = ObjectConvertClass.static_ext_string(dr["strAnnouncements"]);
keyman.KeyEndTime = ObjectConvertClass.static_ext_date(dr["dtEndTime"]);
keyman.KeyReason = ObjectConvertClass.static_ext_string(dr["strReason"]);
keyman.KeyStartTime = ObjectConvertClass.static_ext_date(dr["dtStartTime"]);
keyman.KeyTMCheDuiGUID = ObjectConvertClass.static_ext_string(dr["strCheJianGUID"]);
keyman.KeyTMCheDuiName = ObjectConvertClass.static_ext_string(dr["strCheJianName"]);
keyman.KeyTMName = ObjectConvertClass.static_ext_string(dr["strTrainmanName"]);
keyman.KeyTMNumber = ObjectConvertClass.static_ext_string(dr["strTrainmanNumber"]);
keyman.KeyTMWorkShopGUID = ObjectConvertClass.static_ext_string(dr["strWorkShopGUID"]);
keyman.KeyTMWorkShopName = ObjectConvertClass.static_ext_string(dr["strWorkShopName"]);
keyman.RegisterName = ObjectConvertClass.static_ext_string(dr["strRegisterName"]);
keyman.RegisterNumber = ObjectConvertClass.static_ext_string(dr["strRegisterNumber"]);
keyman.RegisterTime = ObjectConvertClass.static_ext_date(dr["dtRegisterTime"]);
keyman.ClientNumber = ObjectConvertClass.static_ext_string(dr["strClientNumber"]);
keyman.ClientName = ObjectConvertClass.static_ext_string(dr["strClientName"]);
keyman.eOpt = ObjectConvertClass.static_ext_int(dr["eOpt"]);
ret.Add(keyman);
}
return(ret);
}
