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);
            }
        }
예제 #3
0
        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);
        }