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 List <T> queryObjectsCollection <T>(String query)
{
List <T> result = new List <T>();
Npgsql.NpgsqlConnection connection = null;
try
{
connection = getConnection();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection);
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
T bean = fillObject <T>(reader);
result.Add(bean);
}
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(result);
}
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 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);
}
/// <summary>
/// Init
/// </summary>
/// <param name="reader">Reader</param>
public ConstraintModel(Npgsql.NpgsqlDataReader reader)
{
this.Name = reader.GetString(2);
this.Schema = reader.GetString(1);
this.Table = reader.GetString(5);
this.Type = reader.GetString(6);
}
public override List <List <string> > Select(string sql, List <Database.Bind> bind_var = null)
{
if (!this.IsConnected)
{
throw new WmibException("The database is not connected");
}
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, this.connection);
if (bind_var != null)
{
BindVars(sql, bind_var, command);
}
SystemHooks.OnSQL(LocalName, sql);
Npgsql.NpgsqlDataReader dr = command.ExecuteReader();
List <List <string> > results = new List <List <string> >();
while (dr.Read())
{
List <string> line = new List <string>();
results.Add(line);
for (int i = 0; i < dr.FieldCount; i++)
{
line.Add(dr[i].ToString());
}
}
return(results);
}
/// <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 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);
}
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);
}
private static CalendarEvent populateEventsFromDB(Npgsql.NpgsqlDataReader dr)
{
CalendarEvent newEvent = new CalendarEvent();
newEvent.id = Helper.ConvertFromDBVal <int>(dr[0]);
newEvent.title = dr[1].ToString();
newEvent.type = dr[2].ToString();
newEvent.description = dr[3].ToString();
newEvent.appointment = Helper.ConvertFromDBVal <Boolean>(dr[4]);
newEvent.personal = Helper.ConvertFromDBVal <Boolean>(dr[5]);
newEvent.city = dr[6].ToString();
newEvent.state = dr[7].ToString();
newEvent.zipcode = Helper.ConvertFromDBVal <int>(dr[8]);
newEvent.zone = Helper.ConvertFromDBVal <int>(dr[9]);
newEvent.map = dr[10].ToString();
newEvent.creator = Helper.ConvertFromDBVal <int>(dr[11]);
newEvent.assigned = Helper.ConvertFromDBVal <int>(dr[12]);
newEvent.start = dr[13].ToString();
newEvent.end = dr[14].ToString();
newEvent.street = dr[15].ToString();
newEvent.Parent_User_Id = Helper.ConvertFromDBVal <int>(dr[16]);
newEvent.Parent_Appointment_Id = Helper.ConvertFromDBVal <int>(dr[17]);
newEvent.Appointment_Reference = (dr[18]).ToString();
return(newEvent);
}
public bool Login(string user, string password)
{
using (var connection = GetConnection())
{
Console.Write(user, password);
var sql = "SELECT * FROM users where username="******"'" + user + "' and password= '******'";
connection.Open();
using var cmd = new Npgsql.NpgsqlCommand(sql, connection);
Npgsql.NpgsqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
UserLoginCache.idUser = dr.GetInt32(0);
UserLoginCache.Username = dr.GetString(1);
UserLoginCache.Email = dr.GetString(3);
UserLoginCache.F_name = dr.GetString(4);
UserLoginCache.L_name = dr.GetString(5);
UserLoginCache.Role = dr.GetString(6);
}
return(true);
}
else
{
return(false);
}
}
}
private static Card populateCardFromDB(Npgsql.NpgsqlDataReader dr)
{
Card newUser = new Card();
newUser.CardId = Helper.ConvertFromDBVal <int>(dr[0]);
newUser.CardType = dr[1].ToString();
newUser.Comment = dr[2].ToString();
newUser.CreatedOn = Helper.ConvertFromDBVal <DateTime>(dr[3]);
newUser.LastUpdated = Helper.ConvertFromDBVal <DateTime>(dr[4]);
newUser.LeftVM = Helper.ConvertFromDBVal <Boolean>(dr[5]);
newUser.NumberCalled = Helper.ConvertFromDBVal <int>(dr[6]);
newUser.TalkedToDM = Helper.ConvertFromDBVal <Boolean>(dr[7]);
newUser.TalkedToOfficeManager = Helper.ConvertFromDBVal <Boolean>(dr[8]);
newUser.TalkedToOther = Helper.ConvertFromDBVal <Boolean>(dr[9]);
newUser.TalkedToPerson = Helper.ConvertFromDBVal <Boolean>(dr[10]);
newUser.CreatorId = Helper.ConvertFromDBVal <int>(dr[11]);
newUser.ParentLeadId = Helper.ConvertFromDBVal <int>(dr[12]);
newUser.AssignedAAId = Helper.ConvertFromDBVal <int>(dr[13]);
newUser.AppointmentSheetId = Helper.ConvertFromDBVal <int>(dr[14]);
newUser.Reassigned = Helper.ConvertFromDBVal <Boolean>(dr[15]);
newUser.CreatorName = dr[16].ToString();
newUser.CallBackDate = Helper.ConvertFromDBVal <DateTime>(dr[17]);
newUser.NoInterestChk = Helper.ConvertFromDBVal <Boolean>(dr[18]);
newUser.NoInterestRea = dr[19].ToString();
newUser.AcquiredDMName = Helper.ConvertFromDBVal <Boolean>(dr[20]);
return(newUser);
}
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 LogModel(Npgsql.NpgsqlDataReader reader)
{
this.Created = reader.GetDateTime(3);
this.Message = reader.GetString(2);
this.Path = reader.GetString(0);
this.Section = reader.GetString(1);
}
private static User populateUserFromDB(Npgsql.NpgsqlDataReader dr)
{
// We are using ToString() here instead of (string)dr[] to fight DBNulls
User newUser = new User();
newUser.UserId = Helper.ConvertFromDBVal <int>(dr[0]);
newUser.UserName = dr[1].ToString();
newUser.Password = dr[2].ToString();
newUser.FirstName = dr[3].ToString();
newUser.MiddleName = dr[4].ToString();
newUser.LastName = dr[5].ToString();
newUser.Address1 = dr[6].ToString();
newUser.Address2 = dr[7].ToString();
newUser.City = dr[8].ToString();
newUser.State = dr[9].ToString();
newUser.ZipCode = dr[10].ToString();
newUser.AssignedRoleId = Helper.ConvertFromDBVal <int>(dr[11]);
newUser.OfficeNumber = Helper.ConvertFromDBVal <int>(dr[12]);
newUser.SalesRepNumber = Helper.ConvertFromDBVal <int>(dr[13]);
newUser.CalendarColor = dr[14].ToString();
newUser.PhoneNumberOne = dr[15].ToString();
newUser.PhoneNumberTwo = dr[16].ToString();
newUser.FaxNumber = dr[17].ToString();
newUser.HourlyRate = Helper.ConvertFromDBVal <Single>(dr[18]);
newUser.EmailOne = dr[19].ToString();
newUser.EmailTwo = dr[20].ToString();
newUser.IsActive = Helper.ConvertFromDBVal <Boolean>(dr[21]);
newUser.TeamNumber = Helper.ConvertFromDBVal <int>(dr[22]);
return(newUser);
}
void create_graph()
{
try
{
int id = 1;
var sql = @"select(c.model || ' ' || b.brand) as c_col, count(p.id) as avto
from car c, brands b, purchases p
where b.id = c.brand_id and c.id = p.car_id
group by c_col;";
Connection.con = new Npgsql.NpgsqlConnection(Connection.connection());
Connection.con.Open();
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, Connection.con);
Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
chart1.Series.Add(reader["c_col"].ToString());
chart1.Series[reader["c_col"].ToString()].Points.AddY(reader["avto"]);
chart1.Series[reader["c_col"].ToString()].ChartType = SeriesChartType.Column;
chart1.Series[reader["c_col"].ToString()].IsValueShownAsLabel = true;
chart1.Series[reader["c_col"].ToString()].LegendText = id + ". " + reader["c_col"].ToString();
chart1.ChartAreas["ChartArea1"].AxisX.MajorGrid.Enabled = false;
chart1.ChartAreas["ChartArea1"].AxisY.MajorGrid.Enabled = false;
id++;
}
Connection.con.Close();
}
catch
{
Connection.con.Close();
}
}
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 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);
}
/// <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 void Initialize(string connectstr)
{
acc = connectstr;
InitializeComponent();
using (Npgsql.NpgsqlCommand cmm = new Npgsql.NpgsqlCommand("", new Npgsql.NpgsqlConnection(acc)))
{
try
{
cmm.CommandText = "SELECT to_number(substring(substring(schema_name from '....$') from 1 for 2 )) thang,to_number('20'||(substring(substring(schema_name from '....$') from '..$'))) nam ,substring(substring(schema_name from '....$') from 1 for 2 )||'-20'||(substring(substring(schema_name from '....$') from '..$')) thangnam from information_schema.schemata where schema_name like 'medibv0%' or schema_name like 'medibv1%' order by thang,nam desc";
cmm.Connection.Open();
Npgsql.NpgsqlDataReader dtrd = cmm.ExecuteReader();
while (dtrd.Read())
{
THANGNAMRow nr = THANGNAM.NewTHANGNAMRow();
for (int i = 0; i < dtrd.FieldCount; i++)
{
if (THANGNAM.Columns.Contains(dtrd.GetName(i)))
{
nr[dtrd.GetName(i)] = dtrd[i];
}
}
THANGNAM.Rows.Add(nr);
}
}
finally
{
cmm.Connection.Close();
}
}
}
/// <summary>
/// Returns a list of all roles in db. Permissions lists will NOT be populated.
/// Can be updated to do so if necessary
/// </summary>
/// <returns></returns>
public IEnumerable <Domain.Role> GetAllRoles()
{
IList <Domain.Role> roles = new List <Domain.Role>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection())
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetAllRolesQuery, conn))
{
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
var temp = new Role()
{
RoleId = Helper.ConvertFromDBVal <int>(dr[1]), Name = dr[0].ToString()
};
roles.Add(temp);
}
}
}
}
return(roles);
}
/// <summary>
/// Returns the geometry corresponding to the Object ID
/// </summary>
/// <param name="oid">Object ID</param>
/// <returns>geometry</returns>
public IGeometry GetGeometryByID(uint oid)
{
IGeometry geom = null;
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strSQL = "SELECT AsBinary(" + this.GeometryColumn + ") AS Geom FROM " + this.Table + " WHERE " + this.ObjectIdColumn + "='" + oid.ToString() + "'";
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);
}
}
}
}
conn.Close();
}
return(geom);
}
public IEnumerable <ManagerWiseAllocationDto> GetManagerWiseAllocationSummaryFromPostgres()
{
List <ManagerWiseAllocationDto> records = new List <ManagerWiseAllocationDto>();
Npgsql.NpgsqlConnection con = null;
try
{
con = new Npgsql.NpgsqlConnection(PostgresSqlQueries.CONNECTION_STRING);
con.Open();
string qry = PostgresSqlQueries.GET_MANAGER_WISE_PROJECTS_SUMMARY.Replace("__CURRENT_DATE__", $"{DateTime.Today.Year}-{DateTime.Today.Month}-{DateTime.Today.Day}");
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(qry, con);
Npgsql.NpgsqlDataReader res = cmd.ExecuteReader();
if (res.HasRows)
{
while (res.Read())
{
records.Add(new ManagerWiseAllocationDto
{
ManagerName = res.IsDBNull(1) == false ? res.GetString(1) : "",
ProjectCount = res.IsDBNull(2) == false ? (int)res.GetInt64(2) : 0,
ProjectManagerID = res.IsDBNull(0) == false ? res.GetInt32(0) : -1
});
}
}
}
catch (Exception) { }
finally
{
con.Close();
con.Dispose();
}
return(records);
}
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);
}
public IEnumerable <EmployeeLoginDto> GetUserAndRoleMapEntries()
{
List <EmployeeLoginDto> records = new List <EmployeeLoginDto>();
Npgsql.NpgsqlConnection con = null;
try
{
con = new Npgsql.NpgsqlConnection(PostgresSqlQueries.SECURITY_DB_CONNECTION_STRING);
con.Open();
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(PostgresSqlQueries.GET_USER_LOGIN_ROLE_ENTRIES, con);
Npgsql.NpgsqlDataReader res = cmd.ExecuteReader();
if (res.HasRows)
{
while (res.Read())
{
records.Add(new EmployeeLoginDto
{
Email = res.IsDBNull(1) == false ? res.GetString(1) : "",
RoleName = res.IsDBNull(2) == false ? res.GetString(2) : "",
UserID = res.IsDBNull(0) == false ? res.GetString(0) : ""
});
}
}
}
catch (Exception) { }
finally
{
con.Close();
con.Dispose();
}
return(records);
}
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 IEnumerable <PodWiseCountDto> GetPodWiseAllocationCount()
{
List <PodWiseCountDto> records = new List <PodWiseCountDto>();
Npgsql.NpgsqlConnection con = null;
try
{
con = new Npgsql.NpgsqlConnection(PostgresSqlQueries.CONNECTION_STRING);
con.Open();
string query = PostgresSqlQueries.POD_WISE_EMPLOYEE_COUNT.Replace("__CURRENT_DATE__", $"{ DateTime.Today.Year}-{ DateTime.Today.Month}-{ DateTime.Today.Day}");
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(query, con);
Npgsql.NpgsqlDataReader res = cmd.ExecuteReader();
if (res.HasRows)
{
while (res.Read())
{
records.Add(new PodWiseCountDto
{
PracticeName = res.IsDBNull(0) == false ? res.GetString(0) : "",
SubCategoryName = res.IsDBNull(1) == false ? res.GetString(1) : "",
Count = res.IsDBNull(2) == false ? (int)res.GetInt64(2) : 0,
});
}
}
}
catch (Exception exp) { }
finally
{
con.Close();
con.Dispose();
}
return(records);
}
public string NewID()
{
string i = "";
string sQuery = "select '" + clsGlobal.pstrservercode + "'||nextval('tbm_booking_nextid') as id;";
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi);
cmd.CommandText = sQuery;
try
{
Npgsql.NpgsqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
if (!rdr.IsDBNull(rdr.GetOrdinal("id")))
{
i = rdr.GetValue(0).ToString();
}
else
{
i = "";
};
}
rdr.Close();
}
catch (Npgsql.NpgsqlException Ex)
{
System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!");
return("");
}
return(i);
}
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);
}
