public List <clsMemberRegistration> GetAllUnSentToGP()
{
List <clsMemberRegistration> lst = new List <clsMemberRegistration>();
using (SqlConnection conn = new SqlConnection(strConn))
{
using (SqlCommand cmd = new SqlCommand("TMR_USP_GetUnSentToGP"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
clsMemberRegistration info = new clsMemberRegistration();
info.id = int.Parse(reader["id"].ToString());
info.RegistrationNo = reader["RegistrationNo"].ToString();
info.ClientID = reader["ClientID"].ToString();
info.Name = reader["Name"].ToString();
info.Booking = Convert.ToDateTime(reader["Booking"]);
info.strProjectid = reader["ProjectID"].ToString();
info.Plan = reader["Plan"].ToString();
lst.Add(info);
}
conn.Close();
}
}
return(lst);
}
public bool UpdateMemberRegistrationAfterCancellation(clsMemberRegistration info)
{
int st = 0;
try
{
using (SqlConnection conn = new SqlConnection(strConn))
{
conn.Open();
{
using (SqlCommand cmd = new SqlCommand("TMR_USP_UpdateMemRegAfterCancellation"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@RegistrationNo", info.RegistrationNo);
cmd.Parameters.AddWithValue("@ClientID", info.ClientID);
cmd.Parameters.AddWithValue("@StatusCode", info.intStatusCode);
st = cmd.ExecuteNonQuery();
}
}
conn.Close();
}
}
catch (Exception ex) { }
if (st > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool UpdateGPCustomer(clsMemberRegistration info)
{
string strDBName = this.GetProjectDBName(info.strProjectid);
int st = 0;
try
{
using (SqlConnection conn = new SqlConnection(strConn))
{
conn.Open();
{
using (SqlCommand cmd = new SqlCommand("TMR_USP_UpdateCustomer"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@DBName", strDBName);
cmd.Parameters.AddWithValue("@custnmbr", info.RegistrationNo);
cmd.Parameters.AddWithValue("@Name", info.Name);
cmd.Parameters.AddWithValue("@CNTCT", info.Name);
cmd.Parameters.AddWithValue("@STMNT", info.Name);
cmd.Parameters.AddWithValue("@SHRT", info.Name);
cmd.Parameters.AddWithValue("@ADRSCODE", "PRIMARY");//info.FatherOrHusbandType);
cmd.Parameters.AddWithValue("@ADRS1", info.CurrentAddress1);
cmd.Parameters.AddWithValue("@ADRS2", info.CurrentAddress2);
cmd.Parameters.AddWithValue("@ADRS3", info.CurrentAddress2);
cmd.Parameters.AddWithValue("@CNtry", info.Country);
cmd.Parameters.AddWithValue("@city", info.City);
st = cmd.ExecuteNonQuery();
}
}
conn.Close();
}
}
catch (Exception ex) { }
if (st > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool UpdateMemberRegistrationAfterAllocation(clsMemberRegistration info)
{
int st = 0;
try
{
using (SqlConnection conn = new SqlConnection(strConn))
{
conn.Open();
{
using (SqlCommand cmd = new SqlCommand("TMR_USP_UpdateMemRegAtAllocation"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@RegistrationNo", info.RegistrationNo);
cmd.Parameters.AddWithValue("@ClientID", info.ClientID);
cmd.Parameters.AddWithValue("@Block", info.Block);
cmd.Parameters.AddWithValue("@Plot", info.Plot);
cmd.Parameters.AddWithValue("@Allocated", info.bitAllocated);
cmd.Parameters.AddWithValue("@StatusCode", info.intStatusCode);
cmd.Parameters.AddWithValue("@DirectorName", string.IsNullOrEmpty(info.strDirectorName) ? string.Empty : info.strDirectorName);
st = cmd.ExecuteNonQuery();
}
}
conn.Close();
}
}
catch (Exception ex) { }
if (st > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Get Cancellation Status Info
/// </summary>
/// <param name="status"></param>
/// <returns></returns>
public List <clsMemberRegistration> GetMemberByStatusFromMemberRegistration(string status)
{
List <clsMemberRegistration> lst = new List <clsMemberRegistration>();
using (SqlConnection conn = new SqlConnection(strConn))
{
conn.Open();
try
{
using (SqlCommand cmd = new SqlCommand("TMR_USP_GetCancellationByStatus"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@Status", status);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
clsMemberRegistration info = new clsMemberRegistration();
info.RegistrationNo = reader["RegistrationNo"].ToString();
info.ClientID = reader["ClientID"].ToString();
info.Name = reader["Name"].ToString();
info.FatherOrHusbandType = reader["FatherOrHusbandType"].ToString();
info.FatherOrHusband = reader["FatherOrHusbandName"].ToString();
info.NIDOrCNIC = reader["CNIC"].ToString();
info.Nationality = reader["Nationality"].ToString();
info.DOB = Convert.ToDateTime(reader["DOB"].ToString());
info.CurrentAddress1 = reader["CurrentAddress1"].ToString();
info.CurrentAddress2 = reader["CurrentAddress2"].ToString();
info.CurrentAddress3 = reader["CurrentAddress3"].ToString();
info.Country = reader["Country"].ToString();
info.City = reader["City"].ToString();
info.PhOff = reader["PhOff"].ToString();
info.Res = reader["Res"].ToString();
info.Mob = reader["Mob"].ToString();
info.Fax = reader["Fax"].ToString();
info.EmailAddress = reader["EmailAddress"].ToString();
info.Plan = reader["Plan"].ToString();
info.Booking = Convert.ToDateTime(reader["Booking"].ToString());
info.strProjectid = reader["ProjectID"].ToString();
info.Block = reader["Block"].ToString();
info.Plot = reader["Plot"].ToString();
info.UnitRate = Convert.ToDecimal(reader["UnitRate"].ToString());
info.TotalPrice = Convert.ToDecimal(reader["TotalPrice"].ToString());
info.FinanceAmt = Convert.ToDecimal(reader["FinanceAmt"].ToString());
info.RebatAmt = Convert.ToDecimal(reader["RebatAmt"].ToString());
info.NetOrRetailPrice = Convert.ToDecimal(reader["NetPrice"].ToString());
info.BookingPrice = Convert.ToDecimal(reader["BookingPrice"].ToString());
lst.Add(info);
}
conn.Close();
}
}
catch (Exception ex) { }
}
return(lst);
}
public bool CreateGPCustomer(clsMemberRegistration info)
{
string strDBName = this.GetProjectDBName(info.strProjectid);
int st = 0;
try
{
using (SqlConnection conn = new SqlConnection(strConn))
{
conn.Open();
{
using (SqlCommand cmd = new SqlCommand("TMR_USP_CreateCustomer"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@DBName", strDBName);
cmd.Parameters.AddWithValue("@custnmbr", info.RegistrationNo);
cmd.Parameters.AddWithValue("@Name", info.Name);
cmd.Parameters.AddWithValue("@CNTCT", info.Name);
cmd.Parameters.AddWithValue("@STMNT", info.Name);
cmd.Parameters.AddWithValue("@SHRT", info.Name);
cmd.Parameters.AddWithValue("@ADRSCODE", "PRIMARY");//info.FatherOrHusbandType);
cmd.Parameters.AddWithValue("@ADRS1", info.CurrentAddress1);
cmd.Parameters.AddWithValue("@ADRS2", info.CurrentAddress2);
cmd.Parameters.AddWithValue("@ADRS3", info.CurrentAddress2);
cmd.Parameters.AddWithValue("@CNtry", info.Country);
cmd.Parameters.AddWithValue("@city", info.City);
cmd.Parameters.AddWithValue("@custclas", info.strCustomerClass);
cmd.Parameters.AddWithValue("@PHONEOFF", info.PhOff);
cmd.Parameters.AddWithValue("@PHONERES", info.Res);
cmd.Parameters.AddWithValue("@MOBILE", info.Mob);
cmd.Parameters.AddWithValue("@CNIC", info.ClientID);
cmd.Parameters.AddWithValue("@Project", info.strProjectid);
cmd.Parameters.AddWithValue("@Block", info.Block);
cmd.Parameters.AddWithValue("@UnitID", info.Plot);
//@CNIC varchar(50), @Project varchar(50), @PHONEOFF varchar(50), @PHONERES varchar(50), @MOBILE varchar(50)
st = cmd.ExecuteNonQuery();
}
}
conn.Close();
}
}
catch (Exception ex) { }
if (st > 0)
{
return(true);
}
else
{
return(false);
}
}
public List <clsMemberRegistration> GetMemberRegistration(clsMemberShipFilter filter)
{
List <clsMemberRegistration> lst = new List <clsMemberRegistration>();
using (SqlConnection conn = new SqlConnection(strConn))
{
try
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("TMR_USP_GetMembership"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
//conn.Open();
cmd.Parameters.AddWithValue("@ProjectID", filter.strProjectID);
cmd.Parameters.AddWithValue("@Block", filter.strBlockNo);
cmd.Parameters.AddWithValue("@RegistrationNo", filter.strMemberShipID);
cmd.Parameters.AddWithValue("@MobileNumber", filter.strMobileNumber);
cmd.Parameters.AddWithValue("@Name", filter.strName);
cmd.Parameters.AddWithValue("@ClientID", filter.strClientID);
cmd.Parameters.AddWithValue("@StatusCode", (int)filter.membershipStatus);
cmd.Parameters.AddWithValue("@FilterAllocated", filter.bFilterAllocated);
cmd.Parameters.AddWithValue("@Allocated", filter.bAllocated);
cmd.Parameters.AddWithValue("@ForProcess", filter.bForProcess);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
clsMemberRegistration info = new clsMemberRegistration();
info.RegistrationNo = reader["RegistrationNo"].ToString();
info.ClientID = reader["ClientID"].ToString();
info.Name = reader["Name"].ToString();
info.FatherOrHusbandType = reader["FatherOrHusbandType"].ToString();
info.FatherOrHusband = reader["FatherOrHusbandName"].ToString();
info.NIDOrCNIC = reader["CNIC"].ToString();
info.Nationality = reader["Nationality"].ToString();
info.DOB = Convert.ToDateTime(reader["DOB"].ToString());
info.CurrentAddress1 = reader["CurrentAddress1"].ToString();
info.CurrentAddress2 = reader["CurrentAddress2"].ToString();
info.CurrentAddress3 = reader["CurrentAddress3"].ToString();
info.Country = reader["Country"].ToString();
info.City = reader["City"].ToString();
info.PhOff = reader["PhOff"].ToString();
info.Res = reader["Res"].ToString();
info.Mob = reader["Mob"].ToString();
info.Fax = reader["Fax"].ToString();
info.EmailAddress = reader["EmailAddress"].ToString();
info.Plan = reader["Plan"].ToString();
info.Booking = Convert.ToDateTime(reader["Booking"].ToString());
info.strProjectid = reader["ProjectID"].ToString();
info.Block = reader["Block"].ToString();
info.Plot = reader["Plot"].ToString();
info.UnitRate = Convert.ToDecimal(reader["UnitRate"].ToString());
info.TotalPrice = Convert.ToDecimal(reader["TotalPrice"].ToString());
info.FinanceAmt = Convert.ToDecimal(reader["FinanceAmt"].ToString());
info.RebatAmt = Convert.ToDecimal(reader["RebatAmt"].ToString());
info.NetOrRetailPrice = Convert.ToDecimal(reader["NetPrice"].ToString());
info.BookingPrice = Convert.ToDecimal(reader["BookingPrice"].ToString());
info.strStatus = reader["Status"].ToString();
info.bitAllocated = Convert.ToBoolean(reader["Allocated"]);
info.bTransferPending = Convert.ToBoolean(reader["TransferPending"]);
info.bSendToGP = Convert.ToBoolean(reader["SendToGP"]);
lst.Add(info);
}
conn.Close();
}
}
catch (Exception exc) { }
finally { }
}
return(lst);
}
public clsMemberRegistration GetMemberRegistrationInfoById(int id)
{
clsMemberRegistration info = new clsMemberRegistration();
try
{
using (SqlConnection conn = new SqlConnection(strConn))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("TMR_USP_GetMemberRegistrationByID"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
//conn.Open();
cmd.Parameters.AddWithValue("@id", id);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows == false)
{
info = null;
}
while (reader.Read())
{
info = new clsMemberRegistration();
info.RegistrationNo = reader["RegistrationNo"].ToString();
info.ClientID = reader["ClientID"].ToString();
info.strProjectid = reader["ProjectID"].ToString();
info.Name = reader["Name"].ToString();
info.FatherOrHusbandType = reader["FatherOrHusbandType"].ToString();
info.FatherOrHusband = reader["FatherOrHusbandName"].ToString();
info.NIDOrCNIC = reader["CNIC"].ToString();
info.Nationality = reader["Nationality"].ToString();
info.DOB = Convert.ToDateTime(reader["DOB"].ToString());
info.CurrentAddress1 = reader["CurrentAddress1"].ToString();
info.CurrentAddress2 = reader["CurrentAddress2"].ToString();
info.CurrentAddress3 = reader["CurrentAddress3"].ToString();
info.Country = reader["Country"].ToString();
info.City = reader["City"].ToString();
info.PhOff = reader["PhOff"].ToString();
info.Res = reader["Res"].ToString();
info.Mob = reader["Mob"].ToString();
info.Fax = reader["Fax"].ToString();
info.EmailAddress = reader["EmailAddress"].ToString();
info.Plan = reader["Plan"].ToString();
info.Booking = Convert.ToDateTime(reader["Booking"].ToString());
info.Block = reader["Block"].ToString();
info.Plot = reader["Plot"].ToString();
info.UnitRate = Convert.ToDecimal(reader["UnitRate"].ToString());
info.TotalPrice = Convert.ToDecimal(reader["TotalPrice"].ToString());
info.FinanceAmt = Convert.ToDecimal(reader["FinanceAmt"].ToString());
info.RebatAmt = Convert.ToDecimal(reader["RebatAmt"].ToString());
info.NetOrRetailPrice = Convert.ToDecimal(reader["NetPrice"].ToString());
info.BookingPrice = Convert.ToDecimal(reader["BookingPrice"].ToString());
info.strProjectid = reader["ProjectID"].ToString();
info.intPercentage = int.Parse(reader["Percentage"].ToString());
info.strStatus = (reader["Status"].ToString());
info.intStatusCode = int.Parse(reader["statuscode"].ToString());
info.AccountNumber = reader["AccountNumber"].ToString();
info.AccountTitle = reader["AccountTitle"].ToString();
info.BankName = reader["BankName"].ToString();
info.CustomerNumber = (reader["CustomerNumber"].ToString());
info.strSalesRep = (reader["SalesRep"].ToString());
info.strCustomerClass = (reader["CustomerClass"].ToString());
info.bSoleOwner = Convert.ToBoolean(reader["SoleOwner"]);
info.bTransferPending = Convert.ToBoolean(reader["TransferPending"]);
info.bSendToGP = Convert.ToBoolean(reader["SendToGP"]);
info.id = int.Parse(reader["id"].ToString());
}
conn.Close();
}
}
}
catch (Exception exc)
{
info = null;
}
finally { }
return(info);
}