예제 #1
0
 public long addaccount(Account account)
 {
     SqlConnection conn = null;
     SqlTransaction trans = null;
     AccountDao accountDao = new AccountDao();
     long AccountID = 0;
     ConnectionDao ConnectionDao = new ConnectionDao();
     try
     {
         conn = ConnectionDao.getConnection();
         trans = conn.BeginTransaction();
         AccountID = accountDao.addAccountBasicInfo(conn, trans, account);
         if (!AccountID.Equals(0))
         {
             trans.Commit();
         }
         else
         {
             trans.Rollback();
         }
     }
     catch (Exception exception)
     {
         trans.Rollback();
         System.Diagnostics.Trace.WriteLine("[EmployeeServices:addEmployee] Exception " + exception.StackTrace);
     }
     finally
     {
         ConnectionDao.closeConnection(conn);
     }
     return AccountID;
 }
예제 #2
0
        public DataSet getmenu(string department)
        {
            EmployeeDao employeedao = new EmployeeDao();
            Employee employee = employeedao.getempdepartment(department);
            ConnectionDao ConnectionDao = new ConnectionDao();
            SqlDataAdapter adp = new SqlDataAdapter("select department_name,weburl from Department where Department_Name='" + employee.department + "'", ConnectionDao.getConnection());
            DataSet ds4 = new DataSet();
            adp.Fill(ds4);
            string Department = ds4.Tables[0].Rows[0]["department_name"].ToString();
            if (Department == "Sales")
            {
                ds4 = addMenu(ds4, "Email");
            }

            if (Department == "Corporate")
            {
                ds4.Tables[0].Rows.RemoveAt (0);
                ds4.AcceptChanges();

                ds4 = addMenu(ds4, "Customer");
                ds4 = addMenu(ds4, "Email");

                ds4 = addMenu(ds4, "Ticketing");
                ds4 = addMenu(ds4, "Dashboard");
                ds4 = addMenu(ds4, "Reports");

            }
            return ds4;
        }
 public string addCustomerAccountInfo(Customer_Account_O2M customerAccount)
 {
     SqlConnection conn = null;
     SqlTransaction trans = null;
      string returnString = IdProConstants.FAIL;
     Customer_Account_O2MDAO customerAccounto2mDao = new Customer_Account_O2MDAO();
     long AccountID = 0;
     ConnectionDao ConnectionDao = new ConnectionDao();
     try
     {
         conn = ConnectionDao.getConnection();
         trans = conn.BeginTransaction();
         returnString  = customerAccounto2mDao.addCustomerAccountO2MInfo(conn, trans, customerAccount);
         if (!AccountID.Equals(0))
         {
             trans.Commit();
         }
         else
         {
             trans.Rollback();
         }
     }
     catch (Exception exception)
     {
         trans.Rollback();
         System.Diagnostics.Trace.WriteLine("[EmployeeServices:addEmployee] Exception " + exception.StackTrace);
     }
     finally
     {
         ConnectionDao.closeConnection(conn);
     }
     return returnString ;
 }
예제 #4
0
        public string addticketsAssignment(SqlConnection conn, SqlTransaction trans, Ticket ticket)
        {
            ConnectionDao connectiondao = new ConnectionDao();
             string strreturn = IdProConstants.SUCCESS;
            SqlCommand cmd = null;
            SqlDataReader rs = null;
               // int TICKETNOTEID = 0;
            string query = "INSERT INTO TicketAssignment([ticketid],[assignby],[assignto],[assigndate])VALUES (@ticketid,@assignby,@assignto,@assigndate)";

            try
            {
                cmd = connectiondao.getSqlCommand(query, conn, trans);

                SqlParameter parm1 = new SqlParameter();
                parm1.ParameterName = "@ticketid";
                parm1.Value = ticket.ticketid;
                cmd.Parameters.Add(parm1);

                SqlParameter parm2 = new SqlParameter();
                parm2.ParameterName = "@assignby";
                parm2.Value = HttpContext.Current.Session["username"];
                cmd.Parameters.Add(parm2);

                SqlParameter parm3 = new SqlParameter();
                parm3.ParameterName = "@assignto";
                parm3.Value = ticket.assignto;
                cmd.Parameters.Add(parm3);

                SqlParameter parm4 = new SqlParameter();
                parm4.ParameterName = "@assigndate";
                parm4.Value = ticket.assigndate;
                cmd.Parameters.Add(parm4);

                rs = cmd.ExecuteReader();
                if (rs.Read())
                {
                   // TICKETAssignment = Int32.Parse(rs["Assignid"].ToString());
                }
            }
            catch (Exception exception)
            {
                System.Diagnostics.Trace.WriteLine("[TicketDao:addticketsTypedetail] Exception " + exception.StackTrace);
            }
            finally
            {
                connectiondao.closeDabaseEntities(cmd, rs);
            }

            return strreturn;
        }
예제 #5
0
        public string addUser(SqlConnection conn, SqlTransaction trans, User user)
        {
            ConnectionDao connectionDao = new ConnectionDao();
            string returnString = IdProConstants.SUCCESS;
            Employee objEmployee = new Employee();
            SqlCommand cmd = null;
            SqlDataReader rs = null;
            AddEmployee obj = new AddEmployee();
              UserServices userService = new UserServices();
            string query = "INSERT INTO Users([username],[password],[role]) VALUES(@UserName,@Password,@Role)";
            try
            {

                cmd = connectionDao.getSqlCommand(query, conn, trans);

                SqlParameter param1 = new SqlParameter();
                param1.ParameterName = "@UserName";
                //param1.Value = HttpContext.Current.Session["useremp"];
                param1.Value = user.Username;
                cmd.Parameters.Add(param1);

                SqlParameter param2 = new SqlParameter();
                param2.ParameterName = "@Password";
                //param2.Value = userService.getHashPassword(HttpContext.Current.Session["useremp"].ToString ());
                param2.Value = userService.getHashPassword(user.Password);
                cmd.Parameters.Add(param2);

                SqlParameter param3 = new SqlParameter();
                param3.ParameterName = "@Role";
                //param3.Value = HttpContext.Current.Session["rol"];

                param3.Value = user.Role ;
                cmd.Parameters.Add(param3);

                cmd.ExecuteScalar();

            }
            catch (Exception exception)
            {
                System.Diagnostics.Trace.WriteLine("[UserDAO:addUser] Exception " + exception.StackTrace);
                returnString = IdProConstants.FAIL;
            }
            finally
            {
                connectionDao.closeDabaseEntities(cmd, rs);
            }

            return returnString;
        }
예제 #6
0
 public string getWebUrl(string department)
 {
     string retval = string.Empty;
     string sql = "select weburl from department where department_name = '" + department + "'";
     ConnectionDao ConnectionDao = new ConnectionDao();
     SqlDataAdapter adp = new SqlDataAdapter(sql, ConnectionDao.getConnection());
     DataSet ds = new DataSet();
     adp.Fill(ds);
     retval = ds.Tables[0].Rows[0]["weburl"].ToString();
     if (retval == "")
     {
         retval = "javascript:void(0);";
     }
     return retval;
 }
예제 #7
0
        public long addAccountBasicInfo(SqlConnection conn, SqlTransaction trans, Account account)
        {
            ConnectionDao connectionDao = new ConnectionDao();
            SqlCommand cmd = null;
            SqlDataReader rs = null;
            int AccountID = 0;
            string query = "INSERT INTO account([Status],[Added_By],[Added_Date]) VALUES (@Status,@Added_By,@Added_Date);SELECT Account_ID=scope_identity();";
            try
            {

                cmd = connectionDao.getSqlCommand(query, conn, trans);

                SqlParameter param1 = new SqlParameter();
                param1.ParameterName = "@Status";
                param1.Value = "Active";
                cmd.Parameters.Add(param1);

                SqlParameter param2 = new SqlParameter();
                param2.ParameterName = "@Added_By";
                param2.Value = HttpContext .Current.Session["username"];
                cmd.Parameters.Add(param2);

                SqlParameter param3 = new SqlParameter();
                param3.ParameterName = "@Added_Date";
                param3.Value = System.DateTime.Now;
                cmd.Parameters.Add(param3);

                rs = cmd.ExecuteReader();
                if (rs.Read())
                {
                    AccountID = Int32.Parse(rs["Account_ID"].ToString());
                }

            }
            catch (Exception exception)
            {
                System.Diagnostics.Trace.WriteLine("[CustomerDAO:addCustomerBasicInfo] Exception " + exception.StackTrace);

            }
            finally
            {
                connectionDao.closeDabaseEntities(cmd, rs);
            }

            return AccountID;
        }
        public string addCustomerAccountO2MInfo(SqlConnection conn, SqlTransaction trans, Customer_Account_O2M  customerAccount)
        {
            ConnectionDao ConnectionDao = new ConnectionDao();
            string returnString = IdProConstants.SUCCESS;
            SqlCommand cmd = null;
            SqlDataReader rs = null;
            //string query = "INSERT INTO Employees([FIRST_NAME],[LAST_NAME],[EMAIL],[USERNAME],[STATUS],[ADDED_BY],[ADDED_DATE],[LAST_UPDATED_BY],[LAST_UPDATED_DATE]) VALUES(@FIRST_NAME,@LAST_NAME,@EMAIL,@USERNAME,@STATUS,@ADDED_BY,@ADDED_DATE,@LAST_UPDATED_BY,@LAST_UPDATED_DATE)";
            string query = "INSERT INTO Customer_Account_O2M([Account_ID],[Customer_ID]) VALUES(@AccountID,@CustomerID)";
            // string query = "INSERT INTO Employees([FIRST_NAME],[LAST_NAME],[EMAIL],[USERNAME],[STATUS],[Department],[Title],[ADDED_BY],[ADDED_DATE]) VALUES('" + employee.FirstName + "','" + employee.LastName + "','" + employee.Email + "','" + employee.Username + "','A','" + employee.department + "','" + employee.title + "','121',getdate())";
            try
            {
                cmd = ConnectionDao.getSqlCommand(query, conn, trans);

                SqlParameter param1 = new SqlParameter();
                param1.ParameterName = "@AccountID";
                param1.Value = customerAccount.AccountID;
                cmd.Parameters.Add(param1);

                SqlParameter param2 = new SqlParameter();
                param2.ParameterName = "@CustomerID";
                param2.Value = customerAccount.CustomerID;
                cmd.Parameters.Add(param2);

                cmd.ExecuteNonQuery();

            }
            catch (Exception exception)
            {
                System.Diagnostics.Trace.WriteLine("[EmployeeDAO:addEmployee] Exception " + exception.StackTrace);
                returnString = IdProConstants.FAIL;
            }
            finally
            {
                ConnectionDao.closeDabaseEntities(cmd, rs);
            }

            return returnString;
        }
예제 #9
0
        public string addEmployee(Employee employee)
        {
            SqlConnection conn = null;
            SqlTransaction trans = null;

            string returnString = IdProConstants.SUCCESS;

            UserDAO userDao = new UserDAO();
            EmployeeDao EmployeeDao = new EmployeeDao();
            ConnectionDao ConnectionDao = new ConnectionDao();
            UserServices userServices = new UserServices();

            if (isEmployeeEmailexist(employee.Email.Trim()))
            {
                returnString = "Employee Email already Exist in the system";
            }
            // else if (userServices.isUserNameExist(employee.USER.Username))
            else if (userServices.isUserNameExist(employee.Username))
            {
                returnString = "UserName already Exit in the system";
            }
            else
            {

                try
                {
                    conn = ConnectionDao.getConnection();

                    trans = conn.BeginTransaction();

                    returnString = userDao.addUser(conn, trans, employee.USER);

                    if (IdProConstants.SUCCESS.Equals(returnString))
                    {
                        returnString = EmployeeDao.addEmployee(conn, trans, employee);

                    }

                    if (IdProConstants.SUCCESS.Equals(returnString))
                    {
                        trans.Commit();

                    }
                    else
                    {
                        trans.Rollback();
                    }
                }
                catch (Exception exception)
                {
                    trans.Rollback();
                    System.Diagnostics.Trace.WriteLine("[EmployeeServices:addEmployee] Exception " + exception.StackTrace);

                }
                finally
                {
                    ConnectionDao.closeConnection(conn);

                }
            }

            return returnString;
        }
예제 #10
0
        public string addEmployee(SqlConnection conn, SqlTransaction trans, Employee employee)
        {
            ConnectionDao ConnectionDao = new ConnectionDao();
            string returnString = IdProConstants.SUCCESS;
            SqlCommand cmd = null;
            SqlDataReader rs = null;
               //string query = "INSERT INTO Employees([FIRST_NAME],[LAST_NAME],[EMAIL],[USERNAME],[STATUS],[ADDED_BY],[ADDED_DATE],[LAST_UPDATED_BY],[LAST_UPDATED_DATE]) VALUES(@FIRST_NAME,@LAST_NAME,@EMAIL,@USERNAME,@STATUS,@ADDED_BY,@ADDED_DATE,@LAST_UPDATED_BY,@LAST_UPDATED_DATE)";
            string query = "INSERT INTO Employees([FIRST_NAME],[LAST_NAME],[EMAIL],[USERNAME],[STATUS],[Title],[ADDED_BY],[ADDED_DATE],[DEPARTMENT]) VALUES(@FIRST_NAME,@LAST_NAME,@EMAIL,@USERNAME,@STATUS,@Title,@ADDED_BY,@ADDED_DATE,@Department)";
              // string query = "INSERT INTO Employees([FIRST_NAME],[LAST_NAME],[EMAIL],[USERNAME],[STATUS],[Department],[Title],[ADDED_BY],[ADDED_DATE]) VALUES('" + employee.FirstName + "','" + employee.LastName + "','" + employee.Email + "','" + employee.Username + "','A','" + employee.department + "','" + employee.title + "','121',getdate())";
            try
            {
                cmd = ConnectionDao.getSqlCommand(query, conn, trans);

                SqlParameter param1 = new SqlParameter();
                param1.ParameterName = "@FIRST_NAME";
                param1.Value = employee.FirstName;
                cmd.Parameters.Add(param1);

                SqlParameter param2 = new SqlParameter();
                param2.ParameterName = "@LAST_NAME";
                param2.Value = employee.LastName;
                cmd.Parameters.Add(param2);

                SqlParameter param3 = new SqlParameter();
                param3.ParameterName = "@EMAIL";
                param3.Value = employee.Email;
                cmd.Parameters.Add(param3);

                SqlParameter param4 = new SqlParameter();
                param4.ParameterName = "@USERNAME";
                //   param4.Value = employee.getUser().getUserName();
                param4.Value = employee.Username;
                cmd.Parameters.Add(param4);

                SqlParameter param5 = new SqlParameter();
                param5.ParameterName = "@STATUS";
                param5.Value = employee.status;
                cmd.Parameters.Add(param5);

                SqlParameter param6 = new SqlParameter();
                param6.ParameterName = "@Department";
                param6.Value = employee.department;
                cmd.Parameters.Add(param6);

                SqlParameter param7 = new SqlParameter();
                param7.ParameterName = "@Title";
                param7.Value = employee.title;
                cmd.Parameters.Add(param7);

                SqlParameter param10 = new SqlParameter();
                param10.ParameterName = "@ADDED_BY";
                param10.Value = HttpContext.Current.Session["username"];
                cmd.Parameters.Add(param10);

                SqlParameter param11 = new SqlParameter();
                param11.ParameterName = "@ADDED_DATE";
                param11.Value = System.DateTime.Now;
                cmd.Parameters.Add(param11);

                //SqlParameter param8 = new SqlParameter();
                //param8.ParameterName = "@LAST_UPDATED_BY";
                //param8.Value = employee.LastUpdatedBy;
                //cmd.Parameters.Add(param8);

                //SqlParameter param9 = new SqlParameter();
                //param9.ParameterName = "@LAST_UPDATED_DATE";
                //param9.Value = employee.LastUpdatedDate;
                //cmd.Parameters.Add(param9);

                cmd.ExecuteNonQuery();

            }
            catch (Exception exception)
            {
                System.Diagnostics.Trace.WriteLine("[EmployeeDAO:addEmployee] Exception " + exception.StackTrace);
                returnString = IdProConstants.FAIL;
            }
            finally
            {
                ConnectionDao.closeDabaseEntities(cmd, rs);
            }

            return returnString;
        }
예제 #11
0
        public string updateEmployee(SqlConnection conn, SqlTransaction trans, Employee employee)
        {
            ConnectionDao ConnectionDao = new ConnectionDao();
            string returnString = IdProConstants.SUCCESS;

            SqlCommand cmd = null;
            SqlDataReader rs = null;

            string query = "Update Employees set FIRST_NAME=@FIRST_NAME,LAST_NAME=@LAST_NAME,EMAIL=@EMAIL,USERNAME=@USERNAME,STATUS=@STATUS,LAST_UPDATED_BY=@id,LAST_UPDATED_DATE=@LAST_UPDATED_BY WHERE Employee_Id=@id";

            try
            {

                cmd = ConnectionDao.getSqlCommand(query, conn, trans);

                SqlParameter param1 = new SqlParameter();
                param1.ParameterName = "@FIRST_NAME";
                param1.Value = employee.FirstName;
                cmd.Parameters.Add(param1);

                SqlParameter param2 = new SqlParameter();
                param2.ParameterName = "@LAST_NAME";
                param2.Value = employee.LastName;
                cmd.Parameters.Add(param2);

                SqlParameter param3 = new SqlParameter();
                param3.ParameterName = "@EMAIL";
                param3.Value = employee.Email;
                cmd.Parameters.Add(param3);

                SqlParameter param4 = new SqlParameter();
                param4.ParameterName = "@USERNAME";
                param4.Value = employee.USER.Username;
                cmd.Parameters.Add(param4);

                SqlParameter param5 = new SqlParameter();
                param5.ParameterName = "@STATUS";
                param5.Value = employee.status;
                cmd.Parameters.Add(param5);

                SqlParameter param6 = new SqlParameter();
                param6.ParameterName = "@id";
                param6.Value = employee.EmployeeId;
                cmd.Parameters.Add(param6);

                SqlParameter param8 = new SqlParameter();
                param8.ParameterName = "@LAST_UPDATED_BY";
                param8.Value = employee.EmployeeId;
                cmd.Parameters.Add(param8);

                SqlParameter param9 = new SqlParameter();
                param9.ParameterName = "@LAST_UPDATED_DATE";
                param9.Value = DateTime.Now;
                cmd.Parameters.Add(param9);

                cmd.ExecuteNonQuery();

            }
            catch (Exception exception)
            {
                System.Diagnostics.Trace.WriteLine("[EmployeeDAO:updateEmployee] Exception " + exception.StackTrace);
                returnString = IdProConstants.FAIL;
            }
            finally
            {
                ConnectionDao.closeDabaseEntities(cmd, rs);
            }

            return returnString;
        }
예제 #12
0
        public DataSet getfillstatus()
        {
            Employee objempstatus = new Employee();
            ConnectionDao ConnectionDao = new ConnectionDao();

            SqlDataAdapter adp = new SqlDataAdapter("select * from Employee_Statuses", ConnectionDao.getConnection());
            DataSet   ds1 = new DataSet();
            adp.Fill(ds1);
            return ds1;
        }
예제 #13
0
 public DataSet getemployeedetailsearch(string lastname)
 {
     ConnectionDao ConnectionDao = new ConnectionDao();
     string str ="select a.*,b.department_name,b.description from employees a, Department b  where a.Department=b.Department_Name and  last_name like '%" + lastname + "%'";
     SqlDataAdapter adp = new SqlDataAdapter(str, ConnectionDao.getConnection());
     DataSet ds1 = new DataSet();
     adp.Fill(ds1);
     return ds1;
 }
예제 #14
0
 public DataTable getemployeedetailfill()
 {
     Employee empid = new Employee();
     ConnectionDao ConnectionDao = new ConnectionDao();
     SqlDataAdapter adp = new SqlDataAdapter("select a.* ,b.* from  Employees a, Users b where a.UserName=b.UserName and Employee_Id='" + EmployeeIDfill12 + "'", ConnectionDao.getConnection());
     DataTable ds1 = new DataTable();
     adp.Fill(ds1);
     return ds1;
 }
예제 #15
0
 public DataSet getticketnodehistory(string ticketid)
 {
     TicketDao ticketdao = new TicketDao();
     Ticket ticket = ticketdao.getticketid(ticketid);
     ConnectionDao ConnectionDao = new ConnectionDao();
     SqlDataAdapter adp = new SqlDataAdapter("select * from TicketNote where ticketid='" + ticket.ticketgetid + "'", ConnectionDao.getConnection());
     DataSet ds2 = new DataSet();
     adp.Fill(ds2);
     return ds2;
 }
예제 #16
0
        public int getNumberOfCustomerBySSN(string ssn)
        {
            ConnectionDao connectionDao = new ConnectionDao();
            Account account = new Account();

            SqlCommand cmd = null;
            SqlConnection conn = null;
            SqlDataReader rs = null;

            int count = 0;

            string query = "select count(*) as count from customer where ssn=@ssn";

            try
            {
                conn = connectionDao.getConnection();
                cmd = connectionDao.getSqlCommandWithoutTransaction(query, conn);

                SqlParameter param1 = new SqlParameter();
                param1.ParameterName = "@ssn";
                param1.Value = ssn;
                cmd.Parameters.Add(param1);

                rs = cmd.ExecuteReader();

                if (rs.Read())
                {
                    count = Int32.Parse(rs["count"].ToString());
                }

            }
            catch (Exception exception)
            {
                System.Diagnostics.Trace.WriteLine("[CustomerDAO:getNumberOfCustomerBySSN] Exception " + exception.StackTrace);

            }
            finally
            {
                connectionDao.closeConnection(conn);
                connectionDao.closeDabaseEntities(cmd, rs);
            }

            return count;
        }
예제 #17
0
        public long addCustomerBasicInfo(SqlConnection conn, SqlTransaction trans, customer customer)
        {
            string strdrop = "";
             ConnectionDao connectionDao = new ConnectionDao();
             string returnString = IdProConstants.SUCCESS;
             SqlCommand cmd = null;
             SqlDataReader rs = null;
             long Customer_ID = 0;
             string query = "INSERT INTO Customer([First_name],[Last_name],[DOB],[SSN],[Address],[City],[State],[zip],[Primary_email],[Secondary_Email],[phone1],[phone2],[Customer_Type]) VALUES (@First_name,@Last_name,@DOB,@SSN,@Address,@City,@State,@zip,@Primary_email,@Secondary_Email,@phone1,@phone2,@Customer_Type);SELECT Customer_ID=scope_identity();";
             try
             {
                strdrop = customer.dropphone1;
                cmd = connectionDao.getSqlCommand(query, conn, trans);

                SqlParameter param1 = new SqlParameter();
                param1.ParameterName = "@First_name";
                param1.Value = customer.firstname;
                cmd.Parameters.Add(param1);

                SqlParameter param2 = new SqlParameter();
                param2.ParameterName = "@Last_name";
                param2.Value = customer.lastname;
                cmd.Parameters.Add(param2);

                SqlParameter param3 = new SqlParameter();
                param3.ParameterName = "@SSN";
                param3.Value = customer.ssn;
                cmd.Parameters.Add(param3);

                SqlParameter param4 = new SqlParameter();
                param4.ParameterName = "@Address";
                param4.Value = customer.address1;
                cmd.Parameters.Add(param4);

                SqlParameter param5 = new SqlParameter();
                param5.ParameterName = "@City";
                param5.Value = customer.city;
                cmd.Parameters.Add(param5);

                SqlParameter param6 = new SqlParameter();
                param6.ParameterName = "@State";
                param6.Value = customer.states;
                cmd.Parameters.Add(param6);

                SqlParameter param7 = new SqlParameter();
                param7.ParameterName = "@zip";
                param7.Value = customer.zip;
                cmd.Parameters.Add(param7);

                SqlParameter param9 = new SqlParameter();
                param9.ParameterName = "@Primary_email";
                param9.Value = customer.primaryemail;
                cmd.Parameters.Add(param9);

                SqlParameter param10 = new SqlParameter();
                param10.ParameterName = "@Secondary_Email";
                param10.Value = customer.secondaryemail;
                cmd.Parameters.Add(param10);

                    SqlParameter param11 = new SqlParameter();
                    param11.ParameterName = "@phone1";
                    param11.Value = customer.phone1;
                    cmd.Parameters.Add(param11);

                    SqlParameter param12 = new SqlParameter();
                    param12.ParameterName = "@phone2";
                    param12.Value = customer.phone2;
                    cmd.Parameters.Add(param12);

                SqlParameter param16 = new SqlParameter();
                param16.ParameterName = "@Customer_Type";
                param16.Value = "primary";
                cmd.Parameters.Add(param16);

                SqlParameter param17 = new SqlParameter();
                param17.ParameterName = "@DOB";
                param17.Value = customer.dob;
                cmd.Parameters.Add(param17);

                rs = cmd.ExecuteReader();
                if (rs.Read())
                {
                    Customer_ID = Int32.Parse(rs["Customer_ID"].ToString());
                }
                //while (rs.Read())
                //{
                //    customerNumber = Int32.Parse(rs["Customer_ID"].ToString());
                //}

            }
            catch (Exception exception)
            {
                System.Diagnostics.Trace.WriteLine("[CustomerDAO:addCustomerBasicInfo] Exception " + exception.StackTrace);

            }
            finally
            {
                connectionDao.closeDabaseEntities(cmd, rs);
            }

             return Customer_ID;
        }
예제 #18
0
        public string TicketAdd(Ticket ticket)
        {
            SqlConnection conn = null;
            SqlTransaction trans = null;

            string returnString = IdProConstants.SUCCESS;

            TicketDao ticketdao = new TicketDao();
            ConnectionDao ConnectionDao = new ConnectionDao();

            try
            {
                conn = ConnectionDao.getConnection();

                trans = conn.BeginTransaction();

                //ticket.tickettypeid = ticketdao.addticketsTypedetail(conn, trans, ticket);
                //if (!ticket.tickettypeid.Equals(0))
                //{
                  ticket.ticketid = ticketdao.addticketsdetail(conn, trans, ticket);
                    if (!ticket.ticketid.Equals(0))
                    {
                        ticket.noteid = ticketdao.addticketsNotedetail(conn, trans, ticket);

                        if (!ticket.noteid.Equals(0))
                        {
                            returnString = ticketdao.addticketsAssignment(conn, trans, ticket);
                        }
                        else
                        {
                            trans.Commit();
                        }
                    }
                    else
                    {
                        trans.Rollback();
                    }

                    trans.Commit();
                }
                //else
                //{
                //    trans.Rollback();
                //}

            //}

            catch (Exception exception)
            {
                trans.Rollback();
                System.Diagnostics.Trace.WriteLine("[EmployeeServices:addEmployee] Exception " + exception.StackTrace);

            }
            finally
            {
                ConnectionDao.closeConnection(conn);

            }

            return returnString;
        }
예제 #19
0
 public DataSet getpriority()
 {
     ConnectionDao ConnectionDao = new ConnectionDao();
     SqlDataAdapter adp = new SqlDataAdapter("select * from TicketPriority", ConnectionDao.getConnection());
     DataSet ds1 = new DataSet();
     adp.Fill(ds1);
     return ds1;
 }
예제 #20
0
 public DataSet getticketassignmenthistory(string ticketid)
 {
     TicketDao ticketdao = new TicketDao();
     Ticket ticket = ticketdao.getticketid(ticketid);
     ConnectionDao ConnectionDao = new ConnectionDao();
     SqlDataAdapter adp = new SqlDataAdapter("select * from TicketAssignment where ticketid='" + ticket.ticketgetid + "'", ConnectionDao.getConnection());
     DataSet ds3 = new DataSet();
     adp.Fill(ds3);
     return ds3;
 }
예제 #21
0
        public int addticketsdetail(SqlConnection conn, SqlTransaction trans, Ticket ticket)
        {
            ConnectionDao connectiondao = new ConnectionDao();
              //  string strreturn = IdProConstants.SUCCESS;
            SqlCommand cmd = null;
            SqlDataReader rs = null;
             int TICKETID = 0;
             string query = "INSERT INTO Ticket([accountid],[tickettype],[assignto],[Assignby],[opendate],[closedate],[status],[priority],[assigndate])VALUES (@accountid,@tickettype,@assignto,@Assignby,@opendate,@closedate,@status,@priority,@assigndate);SELECT ticketid=scope_identity();";

               try
               {
               cmd = connectiondao.getSqlCommand(query, conn, trans);

               SqlParameter parm1 = new SqlParameter();
               parm1.ParameterName = "@accountid";
               parm1.Value = HttpContext.Current.Session["AccountID"];
               cmd.Parameters.Add(parm1);

               SqlParameter parm2 = new SqlParameter();
               parm2.ParameterName = "@tickettype";
               parm2.Value = ticket.tickettypestring ;
               cmd.Parameters.Add(parm2);

               SqlParameter parm3 = new SqlParameter();
               parm3.ParameterName = "@assignto";
               parm3.Value = ticket.assignto;
               cmd.Parameters.Add(parm3);

               SqlParameter parm4 = new SqlParameter();
               parm4.ParameterName = "@Assignby";
               parm4.Value = HttpContext.Current.Session["username"];
               cmd.Parameters.Add(parm4);

               SqlParameter parm5 = new SqlParameter();
               parm5.ParameterName = "@opendate";
               parm5.Value = ticket.assigndate;
               cmd.Parameters.Add(parm5);

               SqlParameter parm6 = new SqlParameter();
               parm6.ParameterName = "@closedate";
               parm6.Value = "";
               cmd.Parameters.Add(parm6);

               SqlParameter parm7 = new SqlParameter();
               parm7.ParameterName = "@status";
               parm7.Value = "Open";
               cmd.Parameters.Add(parm7);

               SqlParameter parm8 = new SqlParameter();
               parm8.ParameterName = "@priority";
               parm8.Value = ticket.priorty;
               cmd.Parameters.Add(parm8);

               SqlParameter parm9 = new SqlParameter();
               parm9.ParameterName = "@assigndate";
               parm9.Value = ticket.assigndate;
               cmd.Parameters.Add(parm9);

               rs = cmd.ExecuteReader();
               if (rs.Read())
               {
                  TICKETID  = Int32.Parse(rs["ticketid"].ToString());
               }
               }
               catch (Exception exception)
               {
               System.Diagnostics.Trace.WriteLine("[TicketDao:addticketsdetail] Exception " + exception.StackTrace);
               }
               finally
               {
               connectiondao.closeDabaseEntities(cmd, rs);
               }

               return TICKETID;
        }
예제 #22
0
        public string updateEmployee(Employee employee)
        {
            SqlConnection conn = null;
            SqlTransaction trans = null;
            string returnString = IdProConstants.SUCCESS;
            UserDAO userDao = new UserDAO();
            EmployeeDao EmployeeDao = new EmployeeDao();
            ConnectionDao ConnectionDao = new ConnectionDao();
            UserServices userServices = new UserServices();
            Employee employeeById = EmployeeDao.getEmployeeById(employee.EmployeeId);
            if (!(employeeById.Email.Trim().Equals(employee.Email.Trim())) && isEmployeeEmailexist(employee.Email.Trim()))
            {
                returnString = "Employee Email already Exist in the system";
            }
            else if (!(employeeById.USER.Username.Trim().ToUpper().Equals(employee.USER.Username.Trim().ToUpper())) && userServices.isUserNameExist(employee.USER.Username))
            {
                returnString = "UserName already Exit in the system";
            }
            else
            {
                try
                {
                    conn = ConnectionDao.getConnection();
                    trans = conn.BeginTransaction();
                    HttpContext.Current.Session["prevUserName"] = employeeById.USER.Username;
                    returnString = userDao.updateUser(conn, trans, employee.USER);
                    if (IdProConstants.SUCCESS.Equals(returnString))
                    {
                        returnString = EmployeeDao.updateEmployee(conn, trans, employee);
                    }
                    if (IdProConstants.SUCCESS.Equals(returnString))
                    {
                        trans.Commit();
                    }
                    else
                    {
                        trans.Rollback();
                    }
                }
                catch (Exception exception)
                {
                    trans.Rollback();
                    System.Diagnostics.Trace.WriteLine("[EmployeeServices:updateEmployee] Exception " + exception.StackTrace);

                }
                finally
                {
                    ConnectionDao.closeConnection(conn);

                }
            }

            return returnString;
        }
예제 #23
0
 public DataSet getAssigned()
 {
     ConnectionDao ConnectionDao = new ConnectionDao();
     SqlDataAdapter adp = new SqlDataAdapter("select * from Department where Department_Name='Account'", ConnectionDao.getConnection());
     DataSet ds1 = new DataSet();
     adp.Fill(ds1);
     return ds1;
 }
예제 #24
0
        public Ticket getticketid(string ticketid)
        {
            ConnectionDao connectionDao = new ConnectionDao();
            Ticket ticket = new Ticket();
            SqlCommand cmd = null;
            SqlConnection conn = null;
            SqlDataReader rs = null;
            HttpContext.Current.Session["accountid"] = "1006";
            string query = "select * from Ticket where accountid='" + HttpContext.Current.Session["accountid"] + "'";

            try
            {
                conn = connectionDao.getConnection();
                cmd = connectionDao.getSqlCommandWithoutTransaction(query, conn);

                rs = cmd.ExecuteReader();

                if (rs.Read())
                {
                    ticket.ticketgetid = (rs["ticketid"].ToString().Trim());
                    strid = Convert.ToString(ticket.ticketgetid);
                }
                else
                {

                    ticket = null;
                }

            }
            catch (Exception exception)
            {
                System.Diagnostics.Trace.WriteLine("[TicketDao:getticketid] Exception " + exception.StackTrace);
                ticket = null;

            }
            finally
            {
                connectionDao.closeConnection(conn);
                connectionDao.closeDabaseEntities(cmd, rs);
            }

            return ticket;
        }
예제 #25
0
        public long addCustomerCoinfo(SqlConnection conn, SqlTransaction trans, customer customer)
        {
            ConnectionDao connectionDao = new ConnectionDao();
             string returnString = IdProConstants.SUCCESS;
             SqlCommand cmd = null;
             SqlDataReader rs = null;
             long Customer_ID = 0;
             string query = "INSERT INTO Customer([First_name],[Last_name],[DOB],[SSN],[Customer_Type]) VALUES (@First_name,@Last_name,@DOB,@SSN,@Customer_Type);SELECT Customer_ID=scope_identity();";
             try
             {

                 cmd = connectionDao.getSqlCommand(query, conn, trans);

                 SqlParameter param1 = new SqlParameter();
                 param1.ParameterName = "@First_name";
                 param1.Value = customer.cofirstname;
                 cmd.Parameters.Add(param1);

                 SqlParameter param2 = new SqlParameter();
                 param2.ParameterName = "@Last_name";
                 param2.Value = customer.colastname;
                 cmd.Parameters.Add(param2);

                 SqlParameter param3 = new SqlParameter();
                 param3.ParameterName = "@DOB";
                 param3.Value = customer.Codob;
                 cmd.Parameters.Add(param3);

                 SqlParameter param4 = new SqlParameter();
                 param4.ParameterName = "@SSN";
                 param4.Value = customer.cossn;
                 cmd.Parameters.Add(param4);

                 SqlParameter param16 = new SqlParameter();
                 param16.ParameterName = "@Customer_Type";
                 param16.Value = "Secondary";
                 cmd.Parameters.Add(param16);

                 rs = cmd.ExecuteReader();
                 if (rs.Read())
                 {
                     Customer_ID = Int32.Parse(rs["Customer_ID"].ToString());
                 }
             }

             catch (Exception exception)
             {
                 System.Diagnostics.Trace.WriteLine("[CustomerDAO:addCustomerBasicInfo] Exception " + exception.StackTrace);

             }
             finally
             {
                 connectionDao.closeDabaseEntities(cmd, rs);
             }

               //  return returnString;

             return Customer_ID;
        }
예제 #26
0
        public Employee getempdepartment(string department)
        {
            ConnectionDao connectionDao = new ConnectionDao();
            Employee employee = new Employee();
            SqlCommand cmd = null;
            SqlConnection conn = null;
            SqlDataReader rs = null;

            string query = "select * from Employees where UserName='******'";

            try
            {
                conn = connectionDao.getConnection();
                cmd = connectionDao.getSqlCommandWithoutTransaction(query, conn);

                rs = cmd.ExecuteReader();

                if (rs.Read())
                {
                    employee.department = (rs["Department"].ToString().Trim());

                }
                else
                {

                    employee = null;
                }

            }
            catch (Exception exception)
            {
                System.Diagnostics.Trace.WriteLine("[EmployeeDao:getempdepartment] Exception " + exception.StackTrace);
                employee = null;

            }
            finally
            {
                connectionDao.closeConnection(conn);
                connectionDao.closeDabaseEntities(cmd, rs);
            }

            return employee;
        }
예제 #27
0
 public DataSet getcustomersarch(string phoneno)
 {
     ConnectionDao ConnectionDao = new ConnectionDao();
     string str = "select customerid from customer where phone1 = '" + phoneno + "' or phone2 = '" + phoneno + "' ";
     SqlDataAdapter adp = new SqlDataAdapter(str, ConnectionDao.getConnection());
     DataSet ds1 = new DataSet();
     adp.Fill(ds1);
     return ds1;
 }
예제 #28
0
        public Employee getEmployeeByUserName(string userName)
        {
            ConnectionDao ConnectionDao = new ConnectionDao();
            Employee employee = new Employee();

            SqlCommand cmd = null;
            SqlConnection conn = null;
            SqlDataReader rs = null;

            string query = "select * from employees where userName=@userName";

            try
            {
                conn = ConnectionDao.getConnection();
                cmd = ConnectionDao.getSqlCommandWithoutTransaction(query, conn);

                SqlParameter param1 = new SqlParameter();
                param1.ParameterName = "@userName";
                param1.Value = userName;
                cmd.Parameters.Add(param1);

                rs = cmd.ExecuteReader();

                if (rs.Read())
                {
                    employee.FirstName=(rs["first_name"].ToString().Trim());
                    employee.LastName=(rs["last_name"].ToString().Trim());
                    employee.Email=(rs["email"].ToString().Trim());
                    employee.EmployeeStatus=(rs["status"].ToString().Trim());
                    employee.EmployeeId = (rs["Employee_Id"].ToString().Trim());
                    employee.department  = (rs["department"].ToString().Trim());
                    UserDAO userDao = new UserDAO();

                    User user = userDao.getUserByUserName(userName);
                    employee.USER=user;

                }
                else
                {
                    employee = null;
                }

            }
            catch (Exception exception)
            {
                System.Diagnostics.Trace.WriteLine("[EmployeeDAO:getEmployeeByUserName] Exception " + exception.StackTrace);
                employee = null;

            }
            finally
            {
                ConnectionDao.closeConnection(conn);
                ConnectionDao.closeDabaseEntities(cmd, rs);
            }

            return employee;
        }
예제 #29
0
 public DataSet getstates()
 {
     ConnectionDao ConnectionDao = new ConnectionDao();
     SqlDataAdapter adp = new SqlDataAdapter("select * from statemaster", ConnectionDao.getConnection());
     DataSet ds1 = new DataSet();
     adp.Fill(ds1);
     return ds1;
 }
예제 #30
0
 public DataTable getemployeedetail()
 {
     ConnectionDao ConnectionDao = new ConnectionDao();
     SqlDataAdapter adp = new SqlDataAdapter("select * from Employees", ConnectionDao.getConnection());
     DataTable   ds1 = new DataTable();
     adp.Fill(ds1);
     return ds1;
 }