internal List <SelectListItem> getSlots()
{
List <SelectListItem> listTimeSlots = new List <SelectListItem>();
MySqlCommand cmd;
DataTable Dt = new DataTable();
DataSet dset = new DataSet();
try
{
MySqlConnection con = GlobalConnection.getConnection();
cmd = new MySqlCommand("sp_Timeslots", con);
cmd.CommandType = CommandType.StoredProcedure;
MySqlDataAdapter sd = new MySqlDataAdapter(cmd);
sd.Fill(Dt);
foreach (DataRow dR in Dt.Rows)
{
listTimeSlots.Add(new SelectListItem
{
Value = Convert.ToString(dR["SERVICE_ID"]),
Text = Convert.ToString(dR["SERVICETYPE"]),
});
}
return(listTimeSlots);
}
catch (Exception ex)
{
log.Error(ex.Message);
return(listTimeSlots);
}
}
internal DataTable GetPhoneNumber(int loginID)
{
MySqlCommand cmd;
string MobileNumber = string.Empty;
DataTable Dt = new DataTable();
DataSet dset = new DataSet();
try
{
MySqlConnection con = GlobalConnection.getConnection();
cmd = new MySqlCommand("GetPhoneNumber", con);
cmd.Parameters.AddWithValue("?p_loginId", loginID);
cmd.Parameters.AddWithValue("?p_Phone", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters.AddWithValue("?p_FIRSTNAME", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters.AddWithValue("?p_LASTNAME", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.CommandType = CommandType.StoredProcedure;
MySqlDataAdapter sd = new MySqlDataAdapter(cmd);
sd.Fill(dset);
if (dset != null)
{
Dt = dset.Tables[0];
}
return(Dt);
}
catch (Exception ex)
{
log.Error(ex.Message);
return(Dt);
}
}
internal int TicketStatusUpdate(int Ticketid, int a_id, string time, string response, string ExpectedClosedate, string progress)
{
MySqlCommand cmd;
DataTable Dt = new DataTable();
int res = 0;
try
{
MySqlConnection con = GlobalConnection.getConnection();
cmd = new MySqlCommand("sp_TicketStatusupdate", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?p_p_Ticketid", Ticketid);
cmd.Parameters.AddWithValue("?p_p_ApartmentID", a_id);
cmd.Parameters.AddWithValue("?p_p_TICKET_FLAG", 1);
cmd.Parameters.AddWithValue("?p_p_TICKET_SLOT", time);
cmd.Parameters.AddWithValue("?p_p_TICKET_EXP_CLOSURE_DATE", ExpectedClosedate);
cmd.Parameters.AddWithValue("?p_p_TICKET_RESPONSE", response);
cmd.Parameters.AddWithValue("?p_p_TICKET_STATUS", progress);
res = cmd.ExecuteNonQuery();
return(res);
}
catch (Exception ex)
{
log.Error(ex.Message);
return(res);
}
}
internal int SaveNewTicket(string Type, string Desc, int id)
{
MySqlCommand cmd;
DataTable Dt = new DataTable();
int res = 0;
try
{
MySqlConnection con = GlobalConnection.getConnection();
cmd = new MySqlCommand("createNewTicket", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?p_p_TICKET_TYPE", Type);
cmd.Parameters.AddWithValue("?p_p_TICKET_DESC", Desc);
cmd.Parameters.AddWithValue("?p_p_TICKET_STATUS", "Pending");
cmd.Parameters.AddWithValue("?p_p_RAISED_BY_LOGIN_ID", id);
cmd.Parameters.AddWithValue("?p_p_TICKET_FLAG", "0");
res = cmd.ExecuteNonQuery();
return(res);
}
catch (Exception ex)
{
log.Error(ex.Message);
return(res);
}
}
internal DataTable Check(string user, string pass)
{
var userName="";
var password="";
string[] res = new string[10];
//SqlConnection objcon = new SqlConnection();
//objcon.ConnectionString = ConfigurationManager.ConnectionStrings["ConnString"].ToString();
//objcon.Open();
SqlCommand cmd;
SqlConnection con = GlobalConnection.getConnection();
cmd = new SqlCommand("CheckUSerExistence", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@User", user);
cmd.Parameters.Add("@pass", pass);
public List <TicketModel> getAllTableDetailsOwner(int id)
{
List <TicketModel> objTicketModel = new List <TicketModel>();
MySqlCommand cmd;
DataTable Dt = new DataTable();
try
{
MySqlConnection con = GlobalConnection.getConnection();
cmd = new MySqlCommand("getTicketListOwner", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?p_p_ApartmentID", id);
MySqlDataAdapter sd = new MySqlDataAdapter(cmd);
sd.Fill(Dt);
foreach (DataRow dR in Dt.Rows)
{
objTicketModel.Add(new TicketModel
{
TicketID = Convert.ToString(dR["TICKET_ID"]),
Type = Convert.ToString(dR["TICKET_TYPE"]),
Description = Convert.ToString(dR["TICKET_DESC"]),
Raised_Date = Convert.ToString(dR["TICKET_RAISED_DATE"]),
Status = Convert.ToString(dR["TICKET_STATUS"]),
FIRSTNAME = Convert.ToString(dR["FIRSTNAME"]),
LASTNAME = Convert.ToString(dR["LASTNAME"]),
MOBILENO = Convert.ToString(dR["MOBILENO"]),
H_FLOOR_NO = Convert.ToString(dR["H_FLOOR_NO"]),
H_Number = Convert.ToString(dR["H_Number"]),
H_BLOCK = Convert.ToString(dR["H_BLOCK"]),
TicketNo = Convert.ToString(dR["TICKET_NUMBER"]),
});
}
return(objTicketModel);
}
catch (Exception ex)
{
log.Error(ex.Message);
return(objTicketModel);
}
}
internal DataTable Check(string user, string pass)
{
var userName = "";
var password = "";
string[] res = new string[10];
//SqlConnection objcon = new SqlConnection();
//objcon.ConnectionString = ConfigurationManager.ConnectionStrings["ConnString"].ToString();
//objcon.Open();
MySqlCommand cmd;
MySqlConnection con = GlobalConnection.getConnection();
cmd = new MySqlCommand("CheckUSerExistence", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?p_User", user);
cmd.Parameters.AddWithValue("?p_pass", pass);
MySqlDataAdapter sd = new MySqlDataAdapter(cmd);
DataTable DS = new DataTable();
sd.Fill(DS);
//foreach (DataRow dR in DS.Rows)
//{
// userName = Convert.ToString(dR["userName"]);
// password = Convert.ToString(dR["password"]);
//}
//if ((userName == user) && (password == pass)) //when you click login
//{
// res[0] = "1";
//}
return(DS);
}
internal DataSet getLocation()
{
List <SelectListItem> listLocations = new List <SelectListItem>();
MySqlCommand cmd;
DataTable Dt = new DataTable();
DataSet dset = new DataSet();
try
{
MySqlConnection con = GlobalConnection.getConnection();
cmd = new MySqlCommand("[getLocation]", con);
cmd.CommandType = CommandType.StoredProcedure;
MySqlDataAdapter sd = new MySqlDataAdapter(cmd);
sd.Fill(dset);
return(dset);
}
catch (Exception ex)
{
log.Error(ex.Message);
return(dset);
}
}
public DataTable FetchTicketDetail(int id, int a_id)
{
List <TicketModel> objTicketModel = new List <TicketModel>();
MySqlCommand cmd;
MySqlConnection con = GlobalConnection.getConnection();
DataTable Dt = new DataTable();
try
{
cmd = new MySqlCommand("FetchTicketDetail", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?p_Ticket_id", id);
cmd.Parameters.AddWithValue("?p_Apart_id", a_id);
MySqlDataAdapter sd = new MySqlDataAdapter(cmd);
sd.Fill(Dt);
return(Dt);
}
catch (Exception ex)
{
log.Error(ex.Message);
return(Dt);
}
}
internal TicketModel getTicketCout(int id)
{
MySqlCommand cmd;
DataTable Dt = new DataTable();
TicketModel lstTicketCount = new TicketModel();
MySqlConnection con = GlobalConnection.getConnection();
try
{
cmd = new MySqlCommand("sp_TicketCount", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?p_Apartment_ID", id);
cmd.Parameters.AddWithValue("?p_Processing", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters.AddWithValue("?p_Pending", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters.AddWithValue("?p_Completed", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters.AddWithValue("?p_Total", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
int p_processing = (int)cmd.Parameters["?p_Processing"].Value;
int p_completed = (int)cmd.Parameters["?p_Completed"].Value;
int p_pending = (int)cmd.Parameters["?p_Pending"].Value;
int p_total = (int)cmd.Parameters["?p_Total"].Value;
lstTicketCount.completed = p_completed;
lstTicketCount.pending = p_pending;
lstTicketCount.processing = p_processing;
lstTicketCount.total = p_total;
return(lstTicketCount);
}
catch (Exception ex)
{
log.Error(ex.Message);
return(lstTicketCount);
}
}
public List <CustomerModel> getAllTableDetails(int a_id, int permission_id)
{
List <CustomerModel> regdetails = new List <CustomerModel>();
MySqlCommand cmd;
try
{
MySqlConnection con = GlobalConnection.getConnection();
DataTable Dt = new DataTable();
cmd = new MySqlCommand("CustomerRecordsDesc", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?p_p_id", 0);
cmd.Parameters.AddWithValue("?p_first_name", "");
cmd.Parameters.AddWithValue("?p_last_name", "");
cmd.Parameters.AddWithValue("?p_city", "");
cmd.Parameters.AddWithValue("?p_country", "");
cmd.Parameters.AddWithValue("?p_phone", "");
cmd.Parameters.AddWithValue("?p_H_id", 0);
cmd.Parameters.AddWithValue("?p_middle_name", "");
cmd.Parameters.AddWithValue("?p_mobileno", "");
cmd.Parameters.AddWithValue("?p_address", "");
cmd.Parameters.AddWithValue("?p_username", "");
cmd.Parameters.AddWithValue("?p_password", "");
//cmd.Parameters.AddWithValue("?p_lastlogindate","");
cmd.Parameters.AddWithValue("?p_state", "");
cmd.Parameters.AddWithValue("?p_action", "");
cmd.Parameters.AddWithValue("?p_a_id", a_id);
cmd.Parameters.AddWithValue("?p_Permission_id", permission_id);
MySqlDataAdapter sd = new MySqlDataAdapter(cmd);
sd.Fill(Dt);
foreach (DataRow dR in Dt.Rows)
{
regdetails.Add(new CustomerModel
{
id = Convert.ToInt32(dR["LOGINID"]),
firstName = Convert.ToString(dR["FIRSTNAME"]),
lastName = Convert.ToString(dR["LASTNAME"]),
phoneNO = Convert.ToString(dR["phone"]),
country = Convert.ToString(dR["COUNTRY"]),
h_id = Convert.ToInt32(dR["h_id"]),
middleName = Convert.ToString(dR["MIDDLENAME"]),
mobileNo = Convert.ToString(dR["MOBILENO"]),
address = Convert.ToString(dR["country"]),
userName = Convert.ToString(dR["USERNAME"]),
password = Convert.ToString(dR["country"]),
// lastLoginDate = Convert.ToString(dR["LASTLOGGEDDATE"]),
state = Convert.ToString(dR["STATE"]),
city = Convert.ToString(dR["CITY"]),
houseNo = Convert.ToString(dR["H_NUMBER"]),
blockNo = Convert.ToString(dR["H_BLOCK"]),
floorNo = Convert.ToString(dR["H_FLOOR_NO"]),
});
}
return(regdetails);
}
catch (Exception ex)
{
log.Error(ex.Message);
return(regdetails);
}
}
internal string[] SaveRecord(int id, string FirstName, string LastName, string Contact, string City, string Country,
string proof1, string proof2, string blockNo, string floorNo, string flatNo, string loginName, string pwd)
{
string[] res = new string[3];
MySqlCommand cmd = null;
GlobalConnection conn = new GlobalConnection();
cmd.Connection = GlobalConnection.getConnection();
//conn.Open();
if (id == 0)
{
//cmd = new MySqlCommand("AddNewCustomer", objcon);
//cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.AddWithValue("@p_id", id);
//cmd.Parameters.AddWithValue("@p_FirstName", FirstName);
//cmd.Parameters.AddWithValue("@p_LastName", LastName);
//cmd.Parameters.AddWithValue("@p_contact_Number", Contact);
//cmd.Parameters.AddWithValue("@p_City", City);
//cmd.Parameters.AddWithValue("@p_Country", Country);
//cmd.Parameters.AddWithValue("@p_Action", "I");
//res[0] = cmd.ExecuteNonQuery().ToString();
//res[0] = "Saved";
//cmd.CommandText = "AddNewCustomer";
//cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.AddWithValue("p_id", OracleDbType.Long).Value = id;
//cmd.Parameters.AddWithValue("p_FirstName", OracleDbType.Varchar2).Value = FirstName;
//cmd.Parameters.AddWithValue("p_LastName", OracleDbType.Varchar2).Value = LastName;
//cmd.Parameters.AddWithValue("p_contact_Number", OracleDbType.Varchar2).Value = Contact;
//cmd.Parameters.AddWithValue("p_City", OracleDbType.Varchar2).Value =City;
//cmd.Parameters.AddWithValue("p_Country", OracleDbType.Varchar2).Value = Country;
//cmd.Parameters.AddWithValue("p_Action", OracleDbType.Varchar2).Value = "I";
//cmd.ExecuteNonQuery();
res[0] = "Saved";
}
else if (id != 0)
{
//cmd = new MySqlCommand("AddNewCustomer", objcon);
//cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.AddWithValue("@p_id", id);
//cmd.Parameters.AddWithValue("@p_FirstName", FirstName);
//cmd.Parameters.AddWithValue("@p_LastName", LastName);
//cmd.Parameters.AddWithValue("@p_contact_Number", Contact);
//cmd.Parameters.AddWithValue("@p_City", City);
//cmd.Parameters.AddWithValue("@p_Country", Country);
//cmd.Parameters.AddWithValue("@p_Action", "U");
//res[0] = cmd.ExecuteNonQuery().ToString();
//res[0] = "Updated";
cmd.CommandText = "AddNewCustomer";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?p_p_id", OracleDbType.Long).Value = id;
cmd.Parameters.AddWithValue("?p_p_FirstName", OracleDbType.Varchar2).Value = FirstName;
cmd.Parameters.AddWithValue("?p_p_LastName", OracleDbType.Varchar2).Value = LastName;
cmd.Parameters.AddWithValue("?p_p_contact_Number", OracleDbType.Varchar2).Value = Contact;
cmd.Parameters.AddWithValue("?p_p_City", OracleDbType.Varchar2).Value = City;
cmd.Parameters.AddWithValue("?p_p_Country", OracleDbType.Varchar2).Value = Country;
cmd.Parameters.AddWithValue("?p_p_Action", OracleDbType.Varchar2).Value = "U";
cmd.ExecuteNonQuery();
res[0] = "Updated";
}
return(res);
}