public IEnumerable <ZipCodes> GetZipcodesByZone(int zone_id)
{
//This is a fairly generic db query with no parameters and pulling all values from reader and tossing data into User
IList <ZipCodes> allZips = new List <ZipCodes>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(ZoneSelectonZipCodeQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter(":zone_id", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = zone_id;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
ZipCodes newZipcode = populateZipcodesFromDB(dr);
allZips.Add(newZipcode);
}
}
}
}
return(allZips);
}
public IList <AppointmentSheet> AppointmentQueue(int said, Boolean reschedule)
{
assignableAppointments.Clear();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetAppointmentQueueQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter(":salesagent", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":reschedule", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Prepare();
command.Parameters[0].Value = said;
command.Parameters[1].Value = reschedule;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
AppointmentSheet newAppointment = populateAppointmentFromDB(dr);
assignableAppointments.Add(newAppointment);
}
}
}
}
return(assignableAppointments);
}
public IEnumerable <CalendarEvent> GetEventsByUserId(int userid)
{
IList <CalendarEvent> userEvents = new List <CalendarEvent>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(EventByUserIdQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = userid;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
CalendarEvent newEvent = populateEventsFromDB(dr);
userEvents.Add(newEvent);
}
}
}
}
return(userEvents);
}
public IList <Lead> GetWarmLeads(int userId)
{
warmLeads.Clear();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetWarmLeadsQuery, conn))
{
if (userId == -1)
{
command.Parameters.AddWithValue("userId", null);
}
else
{
command.Parameters.AddWithValue("userId", userId);
}
command.Prepare();
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
Lead newLead = populateLeadFromDB(dr);
warmLeads.Add(newLead);
}
}
}
}
return(warmLeads);
}
public Account GetAccountByAccountId(int accountid)
{
//Please see section on using prepared statements in npgsql user manual for explanation on params and query structure
Domain.Account Account = new Domain.Account();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(AccountByAccountIDQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("accountid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = accountid;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
Account = populateAccountFromDB(dr);
}
}
}
}
return(Account);
}
public Domain.Lead LeadByLeadID(int leadid)
{
Lead newLead = new Lead();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(LeadByLeadIDSelectQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("leadid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = leadid;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
if (dr.Read())
{
newLead = populateLeadFromDB(dr);
}
//IF there is more than one row coming back for an id, than we should toss an exception
if (dr.Read())
{
throw new InvalidOperationException("More than one user came back when Querying by UserId");
}
}
}
}
return(newLead);
}
public Ticket GetTicketByTicketId(int ticketid)
{
//Please see section on using prepared statements in npgsql user manual for explanation on params and query structure
Ticket newTicket = new Ticket();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(TicketByTicketIdSelectQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("ticketid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = ticketid;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
newTicket = populateTicketsFromDB(dr);
}
}
}
}
return(newTicket);
}
public IList <Lead> GetLeadByCardType(string cardtype)
{
IList <Domain.Lead> getLeads = new List <Domain.Lead>();
getLeads.Clear();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetLeadsByCardTypeQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter(":cardtype", NpgsqlTypes.NpgsqlDbType.Text));
command.Prepare();
command.Parameters[0].Value = cardtype;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
Lead newLead = populateLeadFromDB(dr);
getLeads.Add(newLead);
}
}
}
}
return(getLeads);
}
public IEnumerable <UserZone> GetAllZonesByUser(int userid)
{
IList <UserZone> zones = new List <UserZone>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetZoneByUserQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = userid;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
var temp = new UserZone()
{
UserID = Helper.ConvertFromDBVal <int>(dr[0]), ZoneId = Helper.ConvertFromDBVal <int>(dr[1])
};
zones.Add(temp);
}
}
}
}
return(zones);
}
public Report CheckExistingRecord(int aaid)
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(ReportCheckExistingRecordQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("month", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("year", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("aauserid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = Helper.ConverttoStringDate(DateTime.Now);
command.Parameters[1].Value = DateTime.Now.Year;
command.Parameters[2].Value = aaid;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
newReport = populateRecordsFromDB(dr);;
}
}
}
}
return(newReport);
}
private int GetAccounts(DateTime startdate, DateTime enddate, int userid, int sauserid, string volume)
{
int accountscount;
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetAccountsQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("sauserid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("startdate", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("enddate", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("volume", NpgsqlTypes.NpgsqlDbType.Text));
command.Prepare();
command.Parameters[0].Value = userid;
command.Parameters[1].Value = sauserid;
command.Parameters[2].Value = startdate;
command.Parameters[3].Value = enddate;
command.Parameters[4].Value = volume;
int dr;
dr = Convert.ToInt32(command.ExecuteScalar());
accountscount = dr;
}
}
return(accountscount);
}
public IEnumerable <Account> GetAccountsByLeadId(int leadId)
{
//Please see section on using prepared statements in npgsql user manual for explanation on params and query structure
IList <Domain.Account> leadAccounts = new List <Domain.Account>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(AccountSelectonLeadIDQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("leadid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = leadId;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
Account newAccount = populateAccountFromDB(dr);
leadAccounts.Add(newAccount);
}
}
}
}
return(leadAccounts);
}
/// <summary>
/// Returns the role with it's permissions list populated
/// </summary>
/// <param name="roleId"></param>
/// <returns></returns>
public Domain.Role GetRoleByRoleId(int roleId)
{
Role role = new Role()
{
RoleId = roleId
};
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection())
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetRoleByIdQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("role_id", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = roleId;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (role.Name.Equals(""))
{
role.Name = dr[0].ToString();
}
role.Permissions.Add(new Permission()
{
Name = dr[1].ToString(), Action = dr[2].ToString(), PermissionId = Helper.ConvertFromDBVal <int>(dr[3])
});
}
}
}
}
return(role);
}
public IEnumerable <User> GetAllUsersByTeam(int TeamNumber)
{
//This is a fairly generic db query with no parameters and pulling all values from reader and tossing data into User
IList <User> allUsers = new List <User>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(SelectAllByTeamNumberQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("team", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = TeamNumber;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
User newUser = populateUserFromDB(dr);
allUsers.Add(newUser);
}
}
}
}
return(allUsers);
}
public User GetUserByUsername(string username)
{
//Please see section on using prepared statements in npgsql user manual for explanation on params and query structure
User newUser = null;
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(SelectByUsernameQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("username", NpgsqlTypes.NpgsqlDbType.Text));
command.Prepare();
command.Parameters[0].Value = username;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
newUser = populateUserFromDB(dr);
}
}
}
}
return(newUser);
}
/// <summary>
/// Returns a single user by using a User Id to locate it
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public User GetUserById(int id)
{
//Please see section on using prepared statements in npgsql user manual for explanation on params and query structure
User newUser = null;
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(SelectByUserIdQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("user_id", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = id;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
if (dr.Read())
{
newUser = populateUserFromDB(dr);
}
//IF there is more than one row coming back for an id, than we should toss an exception
if (dr.Read())
{
throw new InvalidOperationException("More than one user came back when Querying by UserId");
}
}
}
}
return(newUser);
}
public void SaveReports(Domain.Report Record)
{
string query;
bool isUpdate = false;
// Want to know right off the bat if we're doing a insert or update
if (CheckExistingRecord(Record.AssignedAAUserID).ReportId != 0)
{
query = ReportUpdateQuery;
isUpdate = true;
}
else
{
query = ReportInsertQuery;
}
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("month", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("year", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("calls", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("appointments", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("goodapts", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("closes", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("accounts", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("aauserid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("sauserid", NpgsqlTypes.NpgsqlDbType.Integer));
if (isUpdate)
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("mbsreport_id", NpgsqlTypes.NpgsqlDbType.Integer));
}
command.Prepare();
command.Parameters[0].Value = Helper.ConverttoStringDate(DateTime.Now);
command.Parameters[1].Value = DateTime.Now.Year;
command.Parameters[2].Value = Record.MonthlyCalls;
command.Parameters[3].Value = Record.MonthlyAppointments;
command.Parameters[4].Value = Record.MonthlyGoodAppointments;
command.Parameters[5].Value = Record.MonthlyCloses;
command.Parameters[6].Value = Record.MonthlyAccounts;
command.Parameters[7].Value = Record.AssignedAAUserID;
command.Parameters[8].Value = Record.AssignedSAUserID;
if (isUpdate)
{
command.Parameters[9].Value = Record.ReportId;
}
int rowsAffected = command.ExecuteNonQuery();
}
}
}
public void SaveThreshold(Domain.Threshold Threshold)
{
string query;
bool isUpdate = false;
// Want to know right off the bat if we're doing a insert or update
if (Threshold.ThresholdId > 0)
{
query = ThresholdUpdateQuery;
isUpdate = true;
}
else
{
query = ThresholdInsertQuery;
}
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("uppercalendar", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("lowercalendar", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("we_upperdashboard", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("we_lowerdashboard", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("nc_upperdashboard", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("nc_lowerdashboard", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("weg_upperdashboard", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("wes_upperdashboard", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("weg_lowerdashboard", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("wes_lowerdashboard", NpgsqlTypes.NpgsqlDbType.Integer));
if (isUpdate)
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("thresholdid", NpgsqlTypes.NpgsqlDbType.Integer));
}
command.Prepare();
command.Parameters[0].Value = Threshold.Upper_Calendar;
command.Parameters[1].Value = Threshold.Lower_Calendar;
command.Parameters[4].Value = Threshold.NC_Upper_Dashboard;
command.Parameters[5].Value = Threshold.NC_Lower_Dashboard;
command.Parameters[6].Value = Threshold.WE_GA_Upper_Dashboard;
command.Parameters[7].Value = Threshold.WE_SA_Upper_Dashboard;
command.Parameters[8].Value = Threshold.WE_GA_Lower_Dashboard;
command.Parameters[9].Value = Threshold.WE_SA_Lower_Dashboard;
if (isUpdate)
{
command.Parameters[10].Value = Threshold.ThresholdId;
}
int rowsAffected = command.ExecuteNonQuery();
}
}
}
public void SaveTimeSlot(TimeSlot timeSlot)
{
string query;
bool isUpdate = false;
// Want to know right off the bat if we're doing a insert or update
if (timeSlot.TimeSlotId > 0)
{
query = TimeSlotUpdateQuery;
isUpdate = true;
}
else
{
query = TimeSlotInsertQuery;
}
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("num_available_sa", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("color", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("start_time", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("end_time", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("title", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("all_day", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("parent_user_id", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("zone", NpgsqlTypes.NpgsqlDbType.Integer));
if (isUpdate)
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("timeslot_id", NpgsqlTypes.NpgsqlDbType.Integer));
}
command.Prepare();
command.Parameters[0].Value = timeSlot.Num_Available_SA;
command.Parameters[1].Value = timeSlot.Color;
command.Parameters[2].Value = timeSlot.StartTime;
command.Parameters[3].Value = timeSlot.EndTime;
command.Parameters[4].Value = timeSlot.Title;
command.Parameters[5].Value = timeSlot.All_Day;
command.Parameters[6].Value = timeSlot.Parent_User_ID;
command.Parameters[7].Value = timeSlot.Zone;
if (isUpdate)
{
command.Parameters[8].Value = timeSlot.TimeSlotId;
}
int rowsAffected = command.ExecuteNonQuery();
}
}
}
//save phone user to DB
public void SavePhoneUser(PhoneUser phoneuser)
{
string query;
bool isUpdate = false;
// check for insert or update
if (phoneuser.PhoneUserId > 0)
{
query = UserUpdateQuery;
isUpdate = true;
}
else
{
query = UserInsertQuery;
}
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("extension", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("firstname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("middlename", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("lastname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("crmuserid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("accountid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("voicemailid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("email", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("date_created", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("extension_server_uuid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("username", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("password", NpgsqlTypes.NpgsqlDbType.Text));
command.Prepare();
command.Parameters[0].Value = phoneuser.Extension;
command.Parameters[1].Value = phoneuser.FirstName;
command.Parameters[2].Value = phoneuser.MiddleName;
command.Parameters[3].Value = phoneuser.LastName;
command.Parameters[4].Value = phoneuser.CRMUserId;
command.Parameters[5].Value = phoneuser.AccountId;
command.Parameters[6].Value = phoneuser.VoiceMailId;
command.Parameters[7].Value = phoneuser.Email;
command.Parameters[8].Value = phoneuser.Date_Created;
command.Parameters[9].Value = phoneuser.Extension_Server_UUID;
command.Parameters[10].Value = phoneuser.UserName;
command.Parameters[11].Value = phoneuser.Password;
int rowsAffected = command.ExecuteNonQuery();
}
}
}
public static List <Tuple <int?, string, int> > CountListForMatterTypes(
IDbConnection conn = null,
bool closeConnection = true)
{
List <Tuple <int?, string, int> > ret = new List <Tuple <int?, string, int> >();
conn = DataHelper.OpenIfNeeded(conn);
using (Npgsql.NpgsqlCommand dbCommand = (Npgsql.NpgsqlCommand)conn.CreateCommand())
{
dbCommand.CommandText =
"SELECT m.matter_type_id, t.title, COUNT(*) AS count FROM matter m LEFT JOIN matter_type t " +
"ON t.id = m.matter_type_id GROUP BY matter_type_id, t.title ORDER BY count DESC";
try
{
dbCommand.Prepare();
using (Npgsql.NpgsqlDataReader reader = dbCommand.ExecuteReader())
{
while (reader.Read())
{
int?matterTypeId;
if (reader.IsDBNull(0))
{
matterTypeId = null;
}
else
{
matterTypeId = reader.GetInt32(0);
}
string title = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
int count = reader.IsDBNull(2) ? 0 : reader.GetInt32(2);
ret.Add(new Tuple <int?, string, int>(matterTypeId, title, count));
}
}
}
catch (Npgsql.NpgsqlException e)
{
System.Diagnostics.Trace.WriteLine(e.ToString());
throw;
}
}
DataHelper.Close(conn, closeConnection);
return(ret);
}
public void DeleteZone(Zone zone)
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(ZoneDeleteQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("zoneid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = zone.ZoneId;
int rowsAffected = command.ExecuteNonQuery();
}
}
}
public void DeleteCalendarEvent(CalendarEvent calendarEvent)
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(EventDeleteQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("eventid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = calendarEvent.id;
int rowsAffected = command.ExecuteNonQuery();
}
}
}
public static List <Tuple <int?, string, decimal> > ListTotalBillingsForContactsForLastYear(
IDbConnection conn = null,
bool closeConnection = true)
{
List <Tuple <int?, string, decimal> > ret = new List <Tuple <int?, string, decimal> >();
conn = DataHelper.OpenIfNeeded(conn);
using (Npgsql.NpgsqlCommand dbCommand = (Npgsql.NpgsqlCommand)conn.CreateCommand())
{
dbCommand.CommandText =
"SELECT bill_to_contact_id as id, (SELECT display_name FROM contact WHERE id=bill_to_contact_id) as contact, SUM(total) as total FROM invoice WHERE date > date - INTERVAL '1 year' GROUP BY bill_to_contact_id ORDER BY total DESC";
try
{
dbCommand.Prepare();
using (Npgsql.NpgsqlDataReader reader = dbCommand.ExecuteReader())
{
while (reader.Read())
{
int?id;
if (reader.IsDBNull(0))
{
id = null;
}
else
{
id = reader.GetInt32(0);
}
string contact = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
decimal amount = reader.IsDBNull(2) ? 0 : reader.GetDecimal(2);
ret.Add(new Tuple <int?, string, decimal>(id, contact, amount));
}
}
}
catch (Npgsql.NpgsqlException e)
{
System.Diagnostics.Trace.WriteLine(e.ToString());
throw;
}
}
DataHelper.Close(conn, closeConnection);
return(ret);
}
public void DeleteAccounts(Domain.Account account)
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(AccountDeleteQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("accountid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = account.AccountId;
int rowsAffected = command.ExecuteNonQuery();
}
}
}
public void SaveHistory(TicketHistory TicketHistory)
{
string query;
bool isUpdate = false;
// Want to know right off the bat if we're doing a insert or update
if (TicketHistory.HistoryId > 0)
{
query = TicketHistoryUpdateQuery;
isUpdate = true;
}
else
{
query = TicketHistoryInsertQuery;
}
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("hticketid", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("historydate", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("userworked", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("haction", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("comment", NpgsqlTypes.NpgsqlDbType.Text));
if (isUpdate)
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("historyid", NpgsqlTypes.NpgsqlDbType.Integer));
}
command.Prepare();
command.Parameters[0].Value = TicketHistory.TicketId;
command.Parameters[1].Value = TicketHistory.HistoryDate;
command.Parameters[2].Value = TicketHistory.UserWorked;
command.Parameters[3].Value = TicketHistory.Action;
command.Parameters[4].Value = TicketHistory.Comment;
if (isUpdate)
{
command.Parameters[5].Value = TicketHistory.HistoryId;
}
int rowsAffected = command.ExecuteNonQuery();
}
}
}
public void DeleteHistory(TicketHistory TicketHistory)
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(TicketHistoryDeleteQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("historyid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = TicketHistory.HistoryId;
int rowsAffected = command.ExecuteNonQuery();
}
}
}
private void button3_Click(object sender, EventArgs e)
{
string _conStr = ConfigurationManager.AppSettings["DB_SK"];
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(_conStr);
connection.Open();
Npgsql.NpgsqlTransaction et = connection.BeginTransaction();
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand("nolstedt01(:p_personnr,:p_contactchannel_id)", connection, et);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("p_personnr", "196402230319");
cmd.Parameters.AddWithValue("p_contactchannel_id", "48AF6816C15B49A9956079BD6D8DE561");
cmd.Prepare();
cmd.ExecuteNonQuery();
et.Save("apa");
et.Commit();
connection.Close();
}
public void SaveEquipment(Domain.Equipment equipment)
{
string query;
bool isUpdate = false;
// Want to know right off the bat if we're doing a insert or update
if (equipment.EquipmentId > 0)
{
query = EquipmentUpdateQuery;
isUpdate = true;
}
else
{
query = EquipmentInsertQuery;
}
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("name", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("type", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("active", NpgsqlTypes.NpgsqlDbType.Boolean));
if (isUpdate)
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("equipmentid", NpgsqlTypes.NpgsqlDbType.Integer));
}
command.Prepare();
command.Parameters[0].Value = equipment.Name;
command.Parameters[1].Value = equipment.Type;
command.Parameters[2].Value = equipment.Active;
if (isUpdate)
{
command.Parameters[3].Value = equipment.EquipmentId;
}
int rowsAffected = command.ExecuteNonQuery();
}
}
}
public void SaveZone(Zone zone)
{
string query;
bool isUpdate = false;
// Want to know right off the bat if we're doing a insert or update
if (zone.ZoneId > 0)
{
query = ZoneUpdateQuery;
isUpdate = true;
}
else
{
query = ZoneInsertQuery;
}
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("zone_number", NpgsqlTypes.NpgsqlDbType.Integer));
if (isUpdate)
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("zone_id", NpgsqlTypes.NpgsqlDbType.Integer));
}
command.Prepare();
command.Parameters[0].Value = zone.ZoneNumber;
if (isUpdate)
{
command.Parameters[2].Value = zone.ZoneId;
}
int rowsAffected = command.ExecuteNonQuery();
}
}
}
public int GetZoneByZipcode(string zipcode)
{
string tranformedzip;
//to get rid of -XXXX
if (zipcode != "")
{
tranformedzip = zipcode.Substring(0, 5);
}
else
{
tranformedzip = "00000";
}
int zip = Convert.ToInt32(tranformedzip);
int zone = 0;
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(ZipSelectionZoneQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("zip", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = zip;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
if (dr.Read() == true)
{
zone = (int)dr[0];
}
else
{
zone = 0;
}
}
}
}
return(zone);
}
