public bool DeleteSalesPerson(SalesPerson sp)
{
try
{
this.readconfile = new ReadConfigFile();
this.con = new OleDbConnection();
this.con.ConnectionString = this.readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
if (this.con.ConnectionString != "")
{
this.con.Open();
this.cmd = new OleDbCommand();
if (this.con.State == ConnectionState.Open)
{
this.tran = this.con.BeginTransaction();
this.cmd.Connection = this.con;
this.cmd.Transaction = this.tran;
this.cmd.CommandType = CommandType.Text;
this.cmd.CommandText = "Delete * from HRBSalesAccounts where SalesPersonID=" + sp.SPId;
this.cmd.ExecuteNonQuery();
this.cmd.CommandText = "Delete * from SalesPersons where SalesPersonID=" + sp.SPId;
if (this.cmd.ExecuteNonQuery() == 0)
{
this.tran.Rollback();
return(false);
}
this.tran.Commit();
this.con.Close();
return(true);
}
}
return(false);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 用户登录
/// </summary>
/// <param name="sp"></param>
/// <returns></returns>
public SalesPerson UserLogin(SalesPerson sp)
{
string sql = "select SalesPersonId,SalesPersonName from SalesPerson where SalesPersonId=@SalesPersonId and LoginPassword=@LoginPassword";
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@SalesPersonId", sp.SalesPersonId),
new SqlParameter("@LoginPassword", sp.LoginPassword)
};
SqlDataReader reader = null;
try
{
reader = Helper.SqlHelper.GetReader(sql, param);
}
catch (Exception ex)
{
Helper.LogHelper.WriteLog(ex.Message);
throw ex;
}
if (reader.Read())
{
SalesPerson objSP = new SalesPerson()
{
SalesPersonName = reader["SalesPersonName"].ToString(),
SalesPersonId = Convert.ToInt32(reader["SalesPersonId"])
};
// 添加登录日志
LoginLogs log = new LoginLogs()
{
LoginId = objSP.SalesPersonId,
LoginName = objSP.SalesPersonName,
LoginTime = DateTime.Now,
ServerName = Dns.GetHostName()
};
objSP.LogId = WriteLoginLog(log);
return(objSP);
}
else
{
return(null);
}
}
public SalesPerson UserLogin(SalesPerson objSales)
{
string sql = "select SPName from SalesPerson where SalesPersonId=@SalesPersonId and LoginPwd=@LoginPwd ";
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@SalesPersonId ", objSales.SalesPersonId),
new SqlParameter("@LoginPwd", objSales.LoginPwd),
};
SqlDataReader objReader = SQLHelper.GetReader(sql, param);
SalesPerson sP = null;
if (objReader.Read())
{
sP = new SalesPerson()
{
SPName = objReader["SPName"].ToString()
};
}
else
{
sP = null;
}
// method 2
//object result = SQLHelper.GetSingleResult(sql, param);
//if (result == null)
//{
// return null;
//}
//else
//{
// objSales.SPName = result.ToString();
//}
//return objSales;
objReader.Close();
return(sP);
}
/// <summary>
/// 销售员登录
/// </summary>
/// <param name="salesPerson"></param>
/// <returns>登录成功返回销售员对象，登录失败返回null</returns>
public SalesPerson SalesPersonLogin(SalesPerson salesPerson)
{
string sql = "SELECT SPName FROM SalesPerson Where SalesPersonId = @SalesPersonId AND LoginPwd = @LoginPwd";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@SalesPersonId", salesPerson.SalesPersonId),
new SqlParameter("@LoginPwd", salesPerson.LoginPwd)
};
SqlDataReader reader = SqlHelper.GetReader(sql, param);
if (reader.Read())
{
salesPerson.SPName = reader["SPName"].ToString();
}
else
{
salesPerson = null;
}
reader.Close();
return(salesPerson);
}
public bool SaveSalesPerson(SalesPerson sp)
{
try
{
this.readconfile = new ReadConfigFile();
this.con = new OleDbConnection();
this.con.ConnectionString = this.readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
if (this.con.ConnectionString != "")
{
this.con.Open();
this.cmd = new OleDbCommand();
if (this.con.State == ConnectionState.Open)
{
this.tran = this.con.BeginTransaction();
this.cmd.Connection = this.con;
this.cmd.Transaction = this.tran;
this.cmd.CommandText = "Select Max(SalesPersonID) from SalesPersons";
this.cmd.CommandType = CommandType.Text;
int MaxSPID = 0;
if (this.cmd.ExecuteScalar() == System.DBNull.Value)
{
MaxSPID = 1;
}
else
{
MaxSPID = Convert.ToInt32(this.cmd.ExecuteScalar()) + 1;
}
if (sp.SPId == 0)
{
this.cmd.CommandText = "insert into SalesPersons(SalesPersonID,Code,Name,CommissionAmount,YTDCommission,SCommissionDate,PriorYearCommission,TotalCommission,InsertionDate,isInactive) values("
+ MaxSPID + ",'" + sp.Id + "','" + sp.Name + "'," + sp.CommissionAmount + "," + sp.YTDCommissionAmount + ",#" + sp.SCommissionDate + "#," + sp.PriorCommissionAmount + "," + sp.TotalCommission + ",#" + DateTime.Now.Date + "#," + sp.IsInActive + ")";
if (this.cmd.ExecuteNonQuery() == 0)
{
this.tran.Rollback();
this.con.Close();
return(false);
}
for (int i = 0; i < sp.HRBAccounts.Count; i++)
{
this.cmd.CommandText = "insert into HRBSalesAccounts(SalesPersonID,Code,Name,CommissionAmount,YTDCommission,SCommissionDate,PriorYearCommission,TotalCommission,InsertionDate,isInactive,AccountNo,CommissionRate) values (" + MaxSPID + ",'" + sp.Id + "','" + sp.Name + "'," + sp.CommissionAmount + "," + sp.YTDCommissionAmount + ",#" + sp.SCommissionDate + "#," + sp.PriorCommissionAmount + "," + sp.TotalCommission + ",#" + DateTime.Now.Date + "#," + sp.IsInActive + ",'" + sp.HRBAccounts[i].SalesAccountID + "'," + sp.HRBAccounts[i].Percentage + ")";
this.cmd.ExecuteNonQuery();
}
}
else
{
this.cmd.CommandText = "Update SalesPersons set isInactive=" + sp.IsInActive + " where SalesPersonID=" + sp.SPId;
if (this.cmd.ExecuteNonQuery() == 0)
{
this.tran.Rollback();
this.con.Close();
return(false);
}
this.cmd.CommandText = "Delete from HRBSalesAccounts where SalesPersonID =" + sp.SPId;
this.cmd.ExecuteNonQuery();
for (int i = 0; i < sp.HRBAccounts.Count; i++)
{
this.cmd.CommandText = "insert into HRBSalesAccounts(SalesPersonID,Code,Name,CommissionAmount,YTDCommission,SCommissionDate,PriorYearCommission,TotalCommission,InsertionDate,isInactive,AccountNo,CommissionRate) values (" + sp.SPId + ",'" + sp.Id + "','" + sp.Name + "'," + sp.CommissionAmount + "," + sp.YTDCommissionAmount + ",#" + sp.SCommissionDate + "#," + sp.PriorCommissionAmount + "," + sp.TotalCommission + ",#" + DateTime.Now.Date + "#," + sp.IsInActive + ",'" + sp.HRBAccounts[i].SalesAccountID + "'," + sp.HRBAccounts[i].Percentage + ")";
this.cmd.ExecuteNonQuery();
}
}
this.tran.Commit();
this.con.Close();
return(true);
}
}
return(false);
}
catch (Exception)
{
throw;
}
}
public List <SalesPerson> GetSalesPersons()
{
List <SalesPerson> sPersons;
try
{
this.readconfile = new ReadConfigFile();
this.con = new OleDbConnection();
this.con.ConnectionString = this.readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
this.dt = new DataTable();
DataTable dtAcc = new DataTable();
if (this.con.ConnectionString != "")
{
this.con.Open();
this.cmd = new OleDbCommand();
if (this.con.State == ConnectionState.Open)
{
this.cmd.Connection = this.con;
this.cmd.CommandText = "Select * from SalesPersons";
this.da = new OleDbDataAdapter(this.cmd.CommandText, this.con);
this.da.Fill(this.dt);
this.cmd.CommandText = "Select * from HRBSalesAccounts";
this.da = new OleDbDataAdapter(this.cmd.CommandText, this.con);
this.da.Fill(dtAcc);
this.con.Close();
}
}
sPersons = new List <SalesPerson>();
SalesPerson sp;
if (this.dt.Rows.Count > 0)
{
foreach (DataRow row in this.dt.Rows)
{
sp = new SalesPerson();
sp.SPId = Convert.ToInt32(row["SalesPersonID"]);
sp.Id = Convert.ToString(row["Code"]);
sp.Name = Convert.ToString(row["Name"]);
sp.CommissionAmount = Convert.ToDecimal(row["CommissionAmount"]);
sp.YTDCommissionAmount = Convert.ToDecimal(row["YTDCommission"]);
sp.SCommissionDate = Convert.ToDateTime(row["SCommissionDate"]);
sp.PriorCommissionAmount = Convert.ToDecimal(row["PriorYearCommission"]);
sp.TotalCommission = Convert.ToDecimal(row["TotalCommission"]);
sp.InsertionDate = Convert.ToDateTime(row["InsertionDate"]);
sp.IsInActive = Convert.ToBoolean(row["isInactive"]);
//get accounts
HRobItem hAcc;
for (int i = 0; i < dtAcc.Rows.Count; i++)
{
if (sp.SPId == Convert.ToInt64(dtAcc.Rows[i]["SalesPersonID"]))
{
hAcc = new HRobItem();
hAcc.Percentage = Convert.ToDecimal(dtAcc.Rows[i]["CommissionRate"]);
hAcc.SalesAccount.AccountId = Convert.ToString(dtAcc.Rows[i]["AccountNo"]);
sp.HRBAccounts.Add(hAcc);
}
}
sPersons.Add(sp);
}
}
sPersons = sPersons;
}
catch (Exception)
{
throw;
}
return(sPersons);
}