public DataSet GetPaymentReceipt(string receiptNumber)
{
SqlCommand sqlCmd = new SqlCommand("GET_PAYMENT_RECEIPT", sqlCon);
try
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@PAYMENT_RECEIPT_ID", receiptNumber);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
dst = new DataSet();
sqlDa.Fill(dst);
LogError.LogEvent("GET_PAYMENT_RECEIPT", "", "GetPaymentReceipt");
}
catch (Exception ex)
{
dst = null;
LogError.LogEvent("GET_PAYMENT_RECEIPT", ex.Message, "GetPaymentReceipt");
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return(dst);
}
public DataSet GetAutoSuggestData(string strType)
{
SqlCommand sqlCmd = new SqlCommand("GET_AUTOPOPULATE_DATA", sqlCon);
DataSet dstAuto = new DataSet();
try
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@TYPE", strType);
sqlCon.Open();
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
sqlDa.Fill(dstAuto);
LogError.LogEvent("GET_AUTOPOPULATE_DATA --> " + strType, "", "GetAutoSuggestData");
}
catch (Exception ex)
{
dstAuto = null;
LogError.LogEvent("GET_AUTOPOPULATE_DATA --> " + strType, ex.Message, "GetAutoSuggestData");
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return(dstAuto);
}
public DataSet GetLoanApplicationList(int intMonth, int intYear, string strAll)
{
string strSQL = "SELECT MEMBER_NAME, MEMBER_VILLAGE, MEMBER_ID, APP_ID " +
"FROM LOAN_APPLICATION " +
"INNER JOIN MEMBER_MASTER ON MEMBER_ID = APP_MEMBER_ID" + "" +
" WHERE APP_MONTH=" + intMonth.ToString() + " AND APP_YEAR=" + intYear.ToString();
if (strAll == "YES")
{
strSQL += " AND APP_TAKEN=0";
}
SqlCommand sqlCmd = new SqlCommand(strSQL, sqlCon);
try
{
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
dst = new DataSet();
sqlDa.Fill(dst);
LogError.LogEvent(sqlCmd.CommandText, "", "GetLoanApplicationList");
}
catch (Exception ex)
{
dst = null;
LogError.LogEvent(sqlCmd.CommandText, ex.Message, "GetLoanApplicationList");
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return(dst);
}
public decimal GetMandalBalance(int intMonth, int intYear)
{
SqlCommand sqlCmd = new SqlCommand("GET_MANDAL_BALANCE", sqlCon);
decimal dcmlBalance = 0;
try
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@MONTH", intMonth);
sqlCmd.Parameters.AddWithValue("@YEAR", intYear);
sqlCon.Open();
dcmlBalance = Convert.ToDecimal(sqlCmd.ExecuteScalar());
LogError.LogEvent("GET_MANDAL_BALANCE", "", "GetMandalDetails");
}
catch (Exception ex)
{
dst = null;
LogError.LogEvent("GET_MANDAL_BALANCE", ex.Message, "GetMandalBalance");
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return dcmlBalance;
}
public DataSet ValidateUser(string strUserID, string strPass)
{
SqlCommand sqlCmd = new SqlCommand("VALIDATE_USER", sqlCon);
try
{
sqlCmd.Parameters.AddWithValue("@USER_ID", strUserID);
sqlCmd.Parameters.AddWithValue("@PASSWORD", strPass);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCon.Open();
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
dst = new DataSet();
sqlDa.Fill(dst);
LogError.LogEvent("VALIDATE_USER --> " + strUserID + "," + strPass, "", "ValidateUuser");
}
catch (Exception ex)
{
dst = new DataSet();
dst = null;
LogError.LogEvent("VALIDATE_USER --> " + strUserID + "," + strPass, ex.Message, "ValidateUuser");
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return(dst);
}
public DataSet GetPendingPayments(int intMemberNumber, int isAdvanceLoanPayment)
{
SqlCommand sqlCmd = new SqlCommand("GET_PENDING_PAYMENTS", sqlCon);
try
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@MEMBER_ID", intMemberNumber);
sqlCmd.Parameters.AddWithValue("@IS_ADVANCE_LOAN_PAYMENT", isAdvanceLoanPayment);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
dst = new DataSet();
sqlDa.Fill(dst);
LogError.LogEvent("GET_PENDING_PAYMENTS", "", "GetPendingPayments");
}
catch (Exception ex)
{
dst = null;
LogError.LogEvent("GET_PENDING_PAYMENTS", ex.Message, "GetPendingPayments");
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return(dst);
}
public DataSet GetEMIHistory()
{
SqlCommand sqlCmd = new SqlCommand("SELECT CONVERT(VARCHAR,EMI_FROM_DATE,105) AS EMI_DATE,EMI_AMOUNT FROM MONTHLY_EMI_HISTORY ORDER BY EMI_FROM_DATE", sqlCon);
try
{
sqlCmd.CommandType = CommandType.Text;
sqlCon.Open();
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
dst = new DataSet();
sqlDa.Fill(dst);
LogError.LogEvent(sqlCmd.CommandText, "", "GetEMIHistory");
}
catch (Exception ex)
{
dst = new DataSet();
dst = null;
LogError.LogEvent(sqlCmd.CommandText, ex.Message, "GetEMIHistory");
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return(dst);
}
public bool CheckLoanRunning(int intMemberId)
{
SqlCommand sqlCmd = new SqlCommand("SELECT COUNT(*) FROM LOAN_MASTER WHERE LOAN_PENDING_AMOUNT>0 AND LOAN_MEMBER_ID=@MEMBER_ID", sqlCon);
int intNoOfRows = 0;
bool blnRunning = false;
try
{
sqlCmd.CommandType = CommandType.Text;
sqlCmd.Parameters.AddWithValue("@MEMBER_ID", intMemberId);
sqlCon.Open();
intNoOfRows = Convert.ToInt32(sqlCmd.ExecuteScalar());
if (intNoOfRows >= 1)
{
blnRunning = true;
}
}
catch (Exception ex)
{
blnRunning = false;
LogError.LogEvent(sqlCmd.CommandText, ex.Message, "CheckLoanRunning");
return(false);
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return(blnRunning);
}
public DataSet GetPaymentReport(string fromDate, string toDate, string type)
{
SqlCommand sqlCmd = new SqlCommand("GET_PAYMENT_REPORT", sqlCon);
try
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@FROM_DATE", fromDate);
sqlCmd.Parameters.AddWithValue("@TO_DATE", toDate);
sqlCmd.Parameters.AddWithValue("@TYPE", type);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
dst = new DataSet();
sqlDa.Fill(dst);
LogError.LogEvent("GET_PAYMENT_REPORT", "", "GetPaymentReport");
}
catch (Exception ex)
{
dst = null;
LogError.LogEvent("GET_PAYMENT_REPORT", ex.Message, "GetPaymentReport");
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return(dst);
}
public DataSet GetYearlySummaryReport(int intYear)
{
SqlCommand sqlCmd = new SqlCommand("GET_YEARLY_SUMMARY", sqlCon);
try
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@YEAR", intYear);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
dst = new DataSet();
sqlDa.Fill(dst);
LogError.LogEvent("GET_YEARLY_SUMMARY", "", "GetYearlySummaryReport");
}
catch (Exception ex)
{
dst = null;
LogError.LogEvent("GET_YEARLY_SUMMARY", ex.Message, "GetYearlySummaryReport");
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return(dst);
}
public DataSet GetMemberList(string strType)
{
SqlCommand sqlCmd = new SqlCommand("GET_MEMBER_LIST", sqlCon);
try
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@TYPE", strType);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
dst = new DataSet();
sqlDa.Fill(dst);
LogError.LogEvent("GET_MEMBER_LIST", "", "GetMemberList");
}
catch (Exception ex)
{
dst = null;
LogError.LogEvent("GET_MEMBER_LIST", ex.Message, "GetMemberList");
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return(dst);
}
public DataSet GetMonthlyPaymentList(int intMonth, int intYear)
{
SqlCommand sqlCmd = new SqlCommand("GET_MONTHLY_PAYMENT_LIST", sqlCon);
try
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@MONTH", intMonth);
sqlCmd.Parameters.AddWithValue("@YEAR", intYear);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
dst = new DataSet();
sqlDa.Fill(dst);
LogError.LogEvent("GET_MONTHLY_PAYMENT_LIST", "", "GetMonthlyPaymentList");
}
catch (Exception ex)
{
dst = null;
LogError.LogEvent("GET_MONTHLY_PAYMENT_LIST", ex.Message, "GetMonthlyPaymentList");
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return(dst);
}
public DataSet GetLoanDetails(int intLoanId)
{
SqlCommand sqlCmd = new SqlCommand("GET_LOAN_DETAILS", sqlCon);
try
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@LOAN_ID", intLoanId);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
dst = new DataSet();
sqlDa.Fill(dst);
LogError.LogEvent("GET_LOAN_DETAILS", "", "GetLoanDetails");
}
catch (Exception ex)
{
dst = null;
LogError.LogEvent("GET_LOAN_DETAILS", ex.Message, "GetLoanDetails");
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return(dst);
}
public bool AddNewMember(string strXML, out int intMemberNo, byte[] photo, out int intLoanId, out string strPaymentId)
{
SqlCommand sqlCmd = new SqlCommand("ADD_NEW_MEMBER", sqlCon);
int intNoOfRows = 0;
bool blnSuccess = false;
intMemberNo = 0;
intLoanId = 0;
strPaymentId = string.Empty;
try
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@STRXML", strXML);
SqlParameter sqlMemberNum = new SqlParameter("@MEMBER_ID", SqlDbType.Int);
sqlMemberNum.Direction = ParameterDirection.Output;
sqlCmd.Parameters.Add(sqlMemberNum);
SqlParameter sqlLoanId = new SqlParameter("@LOAN_ID", SqlDbType.Int);
sqlLoanId.Direction = ParameterDirection.Output;
sqlCmd.Parameters.Add(sqlLoanId);
sqlCmd.Parameters.AddWithValue("@PHOTO", photo);
SqlParameter sqlPaymentId = new SqlParameter("@PAYMENT_RECEIPT_ID", SqlDbType.VarChar, 8);
sqlPaymentId.Direction = ParameterDirection.Output;
sqlCmd.Parameters.Add(sqlPaymentId);
sqlCon.Open();
sqlTxn = sqlCon.BeginTransaction();
sqlCmd.Transaction = sqlTxn;
intNoOfRows = Convert.ToInt32(sqlCmd.ExecuteNonQuery());
if (intNoOfRows >= 241)
{
sqlTxn.Commit();
intMemberNo = Convert.ToInt32(sqlMemberNum.Value);
intLoanId = Convert.ToInt32(sqlLoanId.Value);
strPaymentId = sqlPaymentId.Value.ToString();
blnSuccess = true;
}
else
{
sqlTxn.Rollback();
}
}
catch (Exception ex)
{
blnSuccess = false;
sqlTxn.Rollback();
LogError.LogEvent("ADD_NEW_MEMBER --> " + strXML, ex.Message, "AddNewMember");
return(false);
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return(blnSuccess);
}
public bool AddPayment(string strXML, int intNumberOfRows, out string strPaymentId)
{
SqlCommand sqlCmd = new SqlCommand("ADD_PAYMENT", sqlCon);
int intNoOfRows = 0;
bool blnSuccess = false;
strPaymentId = string.Empty;
try
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@STRXML", strXML);
SqlParameter sqlPaymentId = new SqlParameter("@PAYMENT_RECEIPT_ID", SqlDbType.VarChar, 8);
sqlPaymentId.Direction = ParameterDirection.Output;
sqlCmd.Parameters.Add(sqlPaymentId);
sqlCon.Open();
sqlTxn = sqlCon.BeginTransaction();
sqlCmd.Transaction = sqlTxn;
intNoOfRows = Convert.ToInt32(sqlCmd.ExecuteNonQuery());
if (intNoOfRows == intNumberOfRows)
{
sqlTxn.Commit();
blnSuccess = true;
strPaymentId = sqlPaymentId.Value.ToString();
}
else
{
sqlTxn.Rollback();
}
}
catch (Exception ex)
{
blnSuccess = false;
sqlTxn.Rollback();
LogError.LogEvent("ADD_PAYMENT --> " + strXML, ex.Message, "AddPayment");
return(false);
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return(blnSuccess);
}
public bool UpdateMemberDetails(string strXML, byte[] photo)
{
SqlCommand sqlCmd = new SqlCommand("UPUDATE_MEMBER_DETAILS", sqlCon);
int intNoOfRows = 0;
bool blnSuccess = false;
try
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@STRXML", strXML);
sqlCmd.Parameters.AddWithValue("@PHOTO", photo);
sqlCon.Open();
sqlTxn = sqlCon.BeginTransaction();
sqlCmd.Transaction = sqlTxn;
intNoOfRows = Convert.ToInt32(sqlCmd.ExecuteNonQuery());
if (intNoOfRows == 1)
{
sqlTxn.Commit();
blnSuccess = true;
}
else
{
sqlTxn.Rollback();
}
}
catch (Exception ex)
{
blnSuccess = false;
sqlTxn.Rollback();
LogError.LogEvent("UPUDATE_MEMBER_DETAILS --> " + strXML, ex.Message, "UpdateMemberDetails");
return(false);
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return(blnSuccess);
}
public DataSet GetMandalDetails()
{
SqlCommand sqlCmd = new SqlCommand("SELECT * FROM MANDAL_DETAILS", sqlCon);
try
{
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
dst = new DataSet();
sqlDa.Fill(dst, "Mandal");
LogError.LogEvent("SELECT * FROM MANDAL_DETAILS", "", "GetMandalDetails");
}
catch (Exception ex)
{
dst = null;
LogError.LogEvent("SELECT * FROM MANDAL_DETAILS", ex.Message, "GetLastNumber");
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return dst;
}
public DataSet GetDashboardData()
{
SqlCommand sqlCmd = new SqlCommand("GET_DASHBOARD_DATA", sqlCon);
try
{
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
dst = new DataSet();
sqlDa.Fill(dst);
LogError.LogEvent("GET_DASHBOARD_DATA", "", "GetDashboardData");
}
catch (Exception ex)
{
dst = null;
LogError.LogEvent("GET_DASHBOARD_DATA", ex.Message, "GetDashboardData");
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return dst;
}
public bool AddNewLoanApplication(string strXML)
{
SqlCommand sqlCmd = new SqlCommand("ADD_LOAN_APPLICATION", sqlCon);
int intNoOfRows = 0;
bool blnSuccess = false;
try
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@STRXML", strXML);
sqlCon.Open();
sqlTxn = sqlCon.BeginTransaction();
sqlCmd.Transaction = sqlTxn;
intNoOfRows = Convert.ToInt32(sqlCmd.ExecuteNonQuery());
if (intNoOfRows == 1)
{
sqlTxn.Commit();
blnSuccess = true;
}
else
{
sqlTxn.Rollback();
}
}
catch (Exception ex)
{
blnSuccess = false;
sqlTxn.Rollback();
LogError.LogEvent("ADD_LOAN_APPLICATION --> " + strXML, ex.Message, "AddNewLoanApplication");
return(false);
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return(blnSuccess);
}
public int ChangeUserPassword(string strXML)
{
int intNoOfRows = 0;
SqlCommand sqlCmd = new SqlCommand("CHANGE_PASSWORD", sqlCon);
try
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@STRXML", strXML);
sqlCon.Open();
sqlTxn = sqlCon.BeginTransaction();
sqlCmd.Transaction = sqlTxn;
intNoOfRows = Convert.ToInt32(sqlCmd.ExecuteScalar());
if (intNoOfRows == 2)
{
sqlTxn.Commit();
}
else if (intNoOfRows == 1)
{
sqlTxn.Rollback();
}
else
{
sqlTxn.Rollback();
}
}
catch (Exception ex)
{
sqlTxn.Rollback();
LogError.LogEvent("CHANGE_PASSWORD -->" + strXML.ToString(), ex.Message, "ChangePwd");
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return(intNoOfRows);
}
public DataSet GetPendingLoanApplicationList()
{
SqlCommand sqlCmd = new SqlCommand("SELECT MEMBER_NAME,MEMBER_VILLAGE,CONVERT(VARCHAR,APP_DATE,103) AS APPLICATION_DATE,DATENAME(MONTH,CONVERT(DATE,'01/' + CONVERT(VARCHAR,APP_MONTH) + '/1900',103)) + ' - ' + CONVERT(VARCHAR,APP_YEAR) AS REQUIRED_ON FROM LOAN_APPLICATION INNER JOIN MEMBER_MASTER ON MEMBER_ID=APP_MEMBER_ID WHERE APP_TAKEN='0'", sqlCon);
try
{
sqlCmd.CommandType = CommandType.Text;
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
dst = new DataSet();
sqlDa.Fill(dst);
LogError.LogEvent(sqlCmd.CommandText, "", "GetPendingLoanApplicationList");
}
catch (Exception ex)
{
dst = null;
LogError.LogEvent(sqlCmd.CommandText, ex.Message, "GetPendingLoanApplicationList");
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return(dst);
}
public DataSet GetPendingLoanList()
{
SqlCommand sqlCmd = new SqlCommand("GET_PENDING_LOAN_LIST", sqlCon);
try
{
sqlCmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
dst = new DataSet();
sqlDa.Fill(dst);
LogError.LogEvent("GET_PENDING_LOAN_LIST", "", "GetPendingLoanList");
}
catch (Exception ex)
{
dst = null;
LogError.LogEvent("GET_PENDING_LOAN_LIST", ex.Message, "GetPendingLoanList");
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return(dst);
}
public int ChangeEMImount(int month, int year, decimal dcmlAmount)
{
SqlCommand sqlCmd = new SqlCommand("UPDATE_EMI_AMOUNT", sqlCon);
int intResult = 0;
try
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@MONTH", month);
sqlCmd.Parameters.AddWithValue("@YEAR", year);
sqlCmd.Parameters.AddWithValue("@AMOUNT", dcmlAmount);
sqlCon.Open();
sqlTxn = sqlCon.BeginTransaction();
sqlCmd.Transaction = sqlTxn;
intResult = Convert.ToInt32(sqlCmd.ExecuteScalar());
if (intResult == 1)
{
sqlTxn.Commit();
}
else
{
sqlTxn.Rollback();
}
}
catch (Exception ex)
{
sqlTxn.Rollback();
LogError.LogEvent("UPDATE_EMI_AMOUNT", ex.Message, "ChangeEMImount");
}
finally
{
sqlCon.Close();
sqlCmd.Parameters.Clear();
}
return(intResult);
}
