public int Insert(UserInfo oParam)
{
string sql = @"INSERT INTO Sys_User
(
SysNo,UserID,UserName,
Pwd,Email,Phone,Note,
Status,DepartmentSysNo,MobilePhone,Flag,PMGroupSysNo,StationSysNo
)
VALUES (
@SysNo,@UserID,@UserName,
@Pwd,@Email,@Phone,@Note,
@Status,@DepartmentSysNo,@MobilePhone,@Flag,@PMGroupSysNo,@StationSysNo
)";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
SqlParameter paramUserID = new SqlParameter("@UserID", SqlDbType.NVarChar,20);
SqlParameter paramUserName = new SqlParameter("@UserName", SqlDbType.NVarChar,20);
SqlParameter paramPwd = new SqlParameter("@Pwd", SqlDbType.NVarChar,50);
SqlParameter paramEmail = new SqlParameter("@Email", SqlDbType.NVarChar,50);
SqlParameter paramPhone = new SqlParameter("@Phone", SqlDbType.NVarChar,100);
SqlParameter paramNote = new SqlParameter("@Note", SqlDbType.NVarChar,200);
SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);
SqlParameter paramDepartmentSysNo = new SqlParameter("@DepartmentSysNo", SqlDbType.Int, 4);
SqlParameter paramMobilePhone = new SqlParameter("@MobilePhone", SqlDbType.VarChar, 30);
SqlParameter paramFlag = new SqlParameter("@Flag", SqlDbType.Int, 4);
SqlParameter paramPMGroupSysNo = new SqlParameter("@PMGroupSysNo", SqlDbType.Int, 4);
SqlParameter paramStationSysNo = new SqlParameter("@StationSysNo", SqlDbType.Int, 4);
paramSysNo.Value = oParam.SysNo;
paramUserID.Value = oParam.UserID;
paramUserName.Value = oParam.UserName;
paramPwd.Value = oParam.Pwd;
paramEmail.Value = oParam.Email;
paramDepartmentSysNo.Value = oParam.DepartmentSysNo;
paramMobilePhone.Value = oParam.MobilePhone;
if ( oParam.Phone != AppConst.StringNull)
paramPhone.Value = oParam.Phone;
else
paramPhone.Value = System.DBNull.Value;
if ( oParam.Note != AppConst.StringNull )
paramNote.Value = oParam.Note;
else
paramNote.Value = System.DBNull.Value;
paramStatus.Value = oParam.Status;
if (oParam.Flag != AppConst.IntNull)
paramFlag.Value = oParam.Flag;
else
paramFlag.Value = System.DBNull.Value;
if (oParam.PMGroupSysNo != AppConst.IntNull)
paramPMGroupSysNo.Value = oParam.PMGroupSysNo;
else
paramPMGroupSysNo.Value = System.DBNull.Value;
if (oParam.StationSysNo != AppConst.IntNull)
paramStationSysNo.Value = oParam.StationSysNo;
else
paramStationSysNo.Value = System.DBNull.Value;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramUserID);
cmd.Parameters.Add(paramUserName);
cmd.Parameters.Add(paramPwd);
cmd.Parameters.Add(paramEmail);
cmd.Parameters.Add(paramPhone);
cmd.Parameters.Add(paramNote);
cmd.Parameters.Add(paramStatus);
cmd.Parameters.Add(paramDepartmentSysNo);
cmd.Parameters.Add(paramMobilePhone);
cmd.Parameters.Add(paramFlag);
cmd.Parameters.Add(paramPMGroupSysNo);
cmd.Parameters.Add(paramStationSysNo);
return SqlHelper.ExecuteNonQuery(cmd);
}
//public void InitPayPlusCharge()
//{
// lock (payPlusChargeLock)
// {
// payPlusChargeHash.Clear();
// string sql = "select * from PayPlusCharge order by BankName,InstallmentNum";
// DataSet ds = SqlHelper.ExecuteDataSet(sql);
// if (!Util.HasMoreRow(ds))
// return;
// foreach (DataRow dr in ds.Tables[0].Rows)
// {
// PayPlusChargeInfo item = new PayPlusChargeInfo();
// Map(item, dr);
// payPlusChargeHash.Add(item.SysNo, item);
// }
// }
//}
public void InitBranchUser()
{
lock (branchUserLock)
{
branchUserHash.Clear();
string sql = "select * from Sys_User where stationsysno>0 and status=" + (int)AppEnum.BiStatus.Valid;
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (!Util.HasMoreRow(ds))
return;
foreach (DataRow dr in ds.Tables[0].Rows)
{
UserInfo item = new UserInfo();
item = SysManager.GetInstance().LoadUser(Util.TrimIntNull(dr["sysno"]));
branchUserHash.Add(item.SysNo, item);
}
}
}
public int Update(UserInfo oParam)
{
string sql = @"UPDATE Sys_User SET
UserID=@UserID,
UserName=@UserName,
Pwd=@Pwd, Email=@Email,
Phone=@Phone, Note=@Note,
Status=@Status,DepartmentSysNo=@DepartmentSysNo,MobilePhone=@MobilePhone,Flag=@Flag,PMGroupSysNo=@PMGroupSysNo,StationSysNo=@StationSysNO
WHERE SysNo=@SysNo";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
SqlParameter paramUserID = new SqlParameter("@UserID", SqlDbType.NVarChar,20);
SqlParameter paramUserName = new SqlParameter("@UserName", SqlDbType.NVarChar,20);
SqlParameter paramPwd = new SqlParameter("@Pwd", SqlDbType.NVarChar,50);
SqlParameter paramEmail = new SqlParameter("@Email", SqlDbType.NVarChar,50);
SqlParameter paramPhone = new SqlParameter("@Phone", SqlDbType.NVarChar,100);
SqlParameter paramNote = new SqlParameter("@Note", SqlDbType.NVarChar,200);
SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);
SqlParameter paramDepartmentSysNo = new SqlParameter("@DepartmentSysNo", SqlDbType.Int, 4);
SqlParameter paramMobilePhone = new SqlParameter("@MobilePhone", SqlDbType.VarChar, 30);
SqlParameter paramFlag = new SqlParameter("@Flag", SqlDbType.Int, 4);
SqlParameter paramPMGroupSysNo = new SqlParameter("@PMGroupSysNo", SqlDbType.Int, 4);
SqlParameter paramStationSysNo = new SqlParameter("@StationSysNo", SqlDbType.Int, 4);
paramSysNo.Value = oParam.SysNo;
paramUserID.Value = oParam.UserID;
paramUserName.Value = oParam.UserName;
paramPwd.Value = oParam.Pwd;
paramEmail.Value = oParam.Email;
paramPhone.Value = oParam.Phone;
paramNote.Value = oParam.Note;
paramStatus.Value = oParam.Status;
paramDepartmentSysNo.Value = oParam.DepartmentSysNo;
paramMobilePhone.Value = oParam.MobilePhone;
paramFlag.Value = oParam.Flag;
paramPMGroupSysNo.Value = oParam.PMGroupSysNo;
paramStationSysNo.Value = oParam.StationSysNo;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramUserID);
cmd.Parameters.Add(paramUserName);
cmd.Parameters.Add(paramPwd);
cmd.Parameters.Add(paramEmail);
cmd.Parameters.Add(paramPhone);
cmd.Parameters.Add(paramNote);
cmd.Parameters.Add(paramStatus);
cmd.Parameters.Add(paramDepartmentSysNo);
cmd.Parameters.Add(paramMobilePhone);
cmd.Parameters.Add(paramFlag);
cmd.Parameters.Add(paramPMGroupSysNo);
cmd.Parameters.Add(paramStationSysNo);
return SqlHelper.ExecuteNonQuery(cmd);
}
public Hashtable GetDepartmentValidUserDs(int DepartmentSysNo)
{
string sql = " select * from Sys_User where 1=1 and Status="+(int)AppEnum.BiStatus.Valid +"and DepartmentSysNo="+DepartmentSysNo;
sql += "order by username";
DataSet ds=SqlHelper.ExecuteDataSet(sql);
if (!Util.HasMoreRow(ds))
return null;
Hashtable ht = new Hashtable(10);
foreach (DataRow dr in ds.Tables[0].Rows)
{
UserInfo item = new UserInfo();
map(item, dr);
ht.Add(item.SysNo, item);
}
return ht;
}
public void ImportUser()
{
if ( !AppConfig.IsImportable)
throw new BizException("Is Importable is false");
/* do not use the following code after Data Pour in */
string sql = " select top 1 * from Sys_User ";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if ( Util.HasMoreRow(ds) )
throw new BizException("the table User is not empty");
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
string sql1 = @"select u.sysno, u.usercode, u.userpassword, u.status, e.employeename, e.email, e.memo from ipp2003..employee e, ipp2003..internal_user u
where e.sysno = u.sysno
and u.usercode = e.employeecode";
DataSet ds1 = SqlHelper.ExecuteDataSet(sql1);
foreach(DataRow dr1 in ds1.Tables[0].Rows )
{
UserInfo oUser = new UserInfo();
oUser.SysNo = Util.TrimIntNull(dr1["SysNo"]);
oUser.UserID = Util.TrimNull(dr1["UserCode"]);
oUser.UserName = Util.TrimNull(dr1["EmployeeName"]);
oUser.Pwd = Util.TrimNull(dr1["UserPassword"]);
oUser.Email = Util.TrimNull(dr1["Email"]);
oUser.Phone = "";
oUser.Note = Util.TrimNull(dr1["Memo"]);
oUser.Status = Util.TrimIntNull(dr1["Status"]);
this.InsertUser(oUser);
//insert into convert table
ImportInfo oUserConvert = new ImportInfo();
oUserConvert.OldSysNo = Util.TrimIntNull(dr1["SysNo"]);
oUserConvert.NewSysNo = oUser.SysNo;
new ImportDac().Insert(oUserConvert, "Sys_User");
}
scope.Complete();
}
}
/// <summary>
/// �����û����û�ID������
/// </summary>
/// <param name="oParam"></param>
/// <returns></returns>
public int UpdateUser_DBC(UserInfo oParam)
{
if (oParam.Flag == (int)AppEnum.UserFlag.PMD)
DeletePMDFlag();
return new UserDac().Update(oParam);
}
/// <summary>
/// Map UserInfo
/// </summary>
/// <param name="oParam"></param>
/// <param name="tempdr"></param>
private void map(UserInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.UserID = Util.TrimNull(tempdr["UserID"]);
oParam.UserName = Util.TrimNull(tempdr["UserName"]);
oParam.Pwd = Util.TrimNull(tempdr["Pwd"]);
oParam.Email = Util.TrimNull(tempdr["Email"]);
oParam.Phone = Util.TrimNull(tempdr["Phone"]);
oParam.Note = Util.TrimNull(tempdr["Note"]);
oParam.Status = Util.TrimIntNull(tempdr["Status"]);
oParam.DepartmentSysNo = Util.TrimIntNull(tempdr["DepartmentSysNo"]);
oParam.BranchSysNo = Util.TrimIntNull(tempdr["BranchSysNo"]);
oParam.StationSysNo = Util.TrimIntNull(tempdr["StationSysNo"]);
}
/// <summary>
/// �����û����û�ID������
/// </summary>
/// <param name="oParam"></param>
/// <returns></returns>
public int UpdateUser(UserInfo oParam)
{
string sql = "select top 1 sysno from sys_user where sysno = " + oParam.SysNo + " and userid <> " + Util.ToSqlString(oParam.UserID);
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if ( Util.HasMoreRow((ds)))
throw new BizException("can't change User ID");
if (oParam.Flag == (int)AppEnum.UserFlag.PMD)
DeletePMDFlag();
return new UserDac().Update(oParam);
}
public UserInfo LoadUser(string paramUserID)
{
string sql = "select * from sys_user where userid=" + Util.ToSqlString(paramUserID);
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if ( !Util.HasMoreRow(ds))
return null;
UserInfo oUserInfo = new UserInfo();
map(oUserInfo, ds.Tables[0].Rows[0]);
return oUserInfo;
}
/// <summary>
/// ����sysno��ȡ�û�
/// </summary>
/// <param name="paramUserSysNo"></param>
/// <returns></returns>
public UserInfo LoadUser(int paramUserSysNo)
{
string sql = "select * from sys_user where sysno = " + paramUserSysNo;
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if ( !Util.HasMoreRow(ds) )
return null;
UserInfo oUserInfo = new UserInfo();
map(oUserInfo, ds.Tables[0].Rows[0]);
return oUserInfo;
}
/// <summary>
/// �����û����û�ID�����ظ�
/// </summary>
/// <param name="oParam"></param>
/// <returns></returns>
public int InsertUser(UserInfo oParam)
{
string sql = "select top 1 sysno from sys_user where userid = " + Util.ToSqlString(oParam.UserID);
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if ( Util.HasMoreRow(ds))
throw new BizException("the same User ID exists");
oParam.SysNo = SequenceDac.GetInstance().Create("Sys_Sequence");
if (oParam.Flag == (int)AppEnum.UserFlag.PMD)
DeletePMDFlag();
return new UserDac().Insert(oParam);
}
public SessionInfo()
{
User = new UserInfo();
}