public static string GetEmailActivationCode()
{
GenerateEmailActivationCode:
string strGuid = string.Empty;
try
{
//get new activation code
strGuid = System.Guid.NewGuid().ToString();
strGuid = strGuid.Replace("-", string.Empty);
strGuid = strGuid.Substring(0, 10).ToString();
}
catch (Exception ex)
{
ErrorLog(ex.Message, "DALUtility.vb, GetEmailActivationCode");
}
//Declare Sql Parameter
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@ActivationCodeByEmail", EncryptDecrypt.Encrypt(strGuid));
//Check This Activation Code Is Exists Then
if ((DALCommon.DataExistsByQuery("SELECT MerchantAccountNo FROM dbo.MerchantAccount WHERE ActivationCodeByEmail LIKE @ActivationCodeByEmail OR PasswordRecoveryKey LIKE @ActivationCodeByEmail", param)))
{
goto GenerateEmailActivationCode;
}
else
{
return(strGuid);
}
}
public static List <SelectListItem> GetClassSectionDropdown(decimal SchoolAccountId)
{
List <SelectListItem> lstClassSection = new List <SelectListItem>();
try
{
lstClassSection.Add(new SelectListItem {
Text = "--- Select Class Section--- ", Value = "0"
});
DataTable tblClassList = new DataTable();
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@SchoolAccountId", SchoolAccountId);
tblClassList = DALCommon.GetDataUsingDataTable("[sp_Admin_GetClassSectionDropdownListBySchoolId]", param);
if (tblClassList.Rows.Count > 0)
{
foreach (DataRow aClass in tblClassList.Rows)
{
lstClassSection.Add(new SelectListItem {
Text = Convert.ToString(aClass["SectionName"]), Value = Convert.ToString(aClass["SectionId"])
});
}
}
}
catch (Exception ex)
{
DALUtility.ErrorLog(ex.Message, "GetClassSectionDropdown, DALCommonFormData");
}
return(lstClassSection);
}
//Get Report List Dropdown
public static List <SelectListItem> GetReportListDropdown(double SchoolAccountId, int ReportCategory)
{
List <SelectListItem> lstReports = new List <SelectListItem>();
try
{
lstReports.Add(new SelectListItem {
Text = "--- Select Report Type --- ", Value = "0"
});
DataTable tblReportsList = new DataTable();
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@SchoolAccountId", SchoolAccountId);
param[1] = new SqlParameter("@ReportCategory", ReportCategory);
tblReportsList = DALCommon.GetDataUsingDataTable("[sp_GetReportInfoBySchoolId]", param);
if (tblReportsList.Rows.Count > 0)
{
foreach (DataRow aClass in tblReportsList.Rows)
{
lstReports.Add(new SelectListItem {
Text = Convert.ToString(aClass["ReportName"]), Value = Convert.ToString(aClass["ReportId"])
});
}
}
}
catch (Exception ex)
{
DALUtility.ErrorLog(ex.Message, "GetReportListDropdown, DALCommonFormData");
}
return(lstReports);
}
//Get Repor Info Data Table
public static ModelReportInfo GetReportInfoModel(int ReportId)
{
ModelReportInfo objModelReportInfo = new ModelReportInfo();
try
{
DataTable tblReportsList = new DataTable();
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@ReportId", ReportId);
tblReportsList = DALCommon.GetDataUsingDataTable("[sp_Admin_GetReportInfoByReportId]", param);
if (tblReportsList.Rows.Count > 0)
{
objModelReportInfo.ReportFilePathName = Convert.ToString(tblReportsList.Rows[0]["ReportFilePathName"]);
objModelReportInfo.SchoolAddressForReport = Convert.ToString(tblReportsList.Rows[0]["SchoolAddressForReport"]);
objModelReportInfo.SchoolTitleForReport = Convert.ToString(tblReportsList.Rows[0]["SchoolTitleForReport"]);
}
}
catch (Exception ex)
{
DALUtility.ErrorLog(ex.Message, "GetReportListDropdown, DALCommonFormData");
}
return(objModelReportInfo);
}
public static string GetMobileActivationCode()
{
GenerateMobileActivationCode:
string strGuid = string.Empty;
string numbers = null;
string singleNumberValue = null;
string strActivationCodeForMobile = string.Empty;
try
{
//declare string builder to get random numbers for 6 digits
StringBuilder builder = new StringBuilder();
//declare an object for random
Random Random = new Random();
//declare all the numbers
numbers = "1234567890";
//Now go for 6 digit numbers
for (int i = 0; i <= 5; i++)
{
//get the single number
singleNumberValue = Convert.ToString(numbers[(Random.Next(0, numbers.Length))]);
//append with the previous one
builder.Append(singleNumberValue);
}
//concatenate all the values 1 String and 5 Digits
strActivationCodeForMobile = builder.ToString();
}
catch (Exception ex)
{
ErrorLog(ex.Message, "DALUtility.vb, GetEmailActivationCode");
}
//Declare Sql Parameter
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@ActivationCodeByMobile", EncryptDecrypt.Encrypt(strActivationCodeForMobile));
//Check This Activation Code Is Exists Then
if ((DALCommon.DataExistsByQuery("SELECT UserAccountNo FROM dbo.UserAccount WHERE ActivationCodeByMobile LIKE @ActivationCodeByMobile", param)))
{
goto GenerateMobileActivationCode;
}
else
{
return(strActivationCodeForMobile);
}
}
//Get Repor Info Data Table
public static DataTable GetReportInfoDataTable(int ReportId)
{
DataTable tblDataTable = new DataTable();
try
{
DataTable tblReportsList = new DataTable();
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@ReportId", ReportId);
tblReportsList = DALCommon.GetDataUsingDataTable("[sp_Admin_GetReportInfoByReportId]", param);
return(tblDataTable);
}
catch (Exception ex)
{
DALUtility.ErrorLog(ex.Message, "GetReportListDropdown, DALCommonFormData");
}
return(tblDataTable);
}
public static int InsertUserLog(string CodeBlock, string ActivityOnPage, decimal UserAccountNo)
{
SqlDecimal sqlDecimal = SqlDecimal.Null;
SqlParameter[] param = new SqlParameter[5];
if ((UserAccountNo > 0))
{
param[0] = new SqlParameter("@UserAccountNo", Convert.ToDecimal(UserAccountNo));
}
else
{
param[0] = new SqlParameter("@UserAccountNo", sqlDecimal);
}
param[1] = new SqlParameter("@UserActivityOnSite", ActivityOnPage);
// param[2] = new SqlParameter("@SitePageName", HttpContext.Current.Request.Url.AbsoluteUri);
param[2] = new SqlParameter("@SitePageName", "");
param[3] = new SqlParameter("@BlockName", CodeBlock);
param[4] = new SqlParameter("@UserLogByIP", GetIP());
return(DALCommon.ExecuteNonQuery("sp_User_InsertUserLog", param));
}
//Get Repor Info Data Table
public static string GetReportFileNamePath(int ReportId)
{
string reportFileName = "";
try
{
DataTable tblReportsList = new DataTable();
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@ReportId", ReportId);
tblReportsList = DALCommon.GetDataUsingDataTable("[sp_Admin_GetReportInfoByReportId]", param);
if (tblReportsList.Rows.Count > 0)
{
reportFileName = Convert.ToString(tblReportsList.Rows[0]["ReportFilePathName"]);
}
}
catch (Exception ex)
{
DALUtility.ErrorLog(ex.Message, "GetReportListDropdown, DALCommonFormData");
}
return(reportFileName);
}
public int InsertStudentMonthlyFeeInsertTransaction(DataTable studentList, ModelStudentFee objModelStudentFee, int FeeMonth, decimal AddedBy)
{
//Initialization
conn = new SqlConnection(ConnectionString());
strErrorMsg = string.Empty;
intStatus = 0;
int totalStudent = studentList.Rows.Count;
int updatedTotalRecord = 0;
try
{
//Open Connection
if ((conn.State == System.Data.ConnectionState.Closed))
{
conn.Open();
}
//Begin Transaction
sqlTrans = conn.BeginTransaction();
//Initialize Command
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = sqlTrans;
//Update Transaction Response
foreach (DataRow aStudent in studentList.Rows)
{
SqlParameter[] paramStudentFee = new SqlParameter[12];
paramStudentFee[0] = new SqlParameter("@StudentId", aStudent["StudentId"]);
paramStudentFee[1] = new SqlParameter("@ComputerCode", aStudent["ComputerCode"]);
paramStudentFee[2] = new SqlParameter("@FeeMonth", FeeMonth);
paramStudentFee[3] = new SqlParameter("@MonthlyFee", aStudent["MonthlyFee"]);
paramStudentFee[4] = new SqlParameter("@AdmissionFee", objModelStudentFee.AdmissionFee);
paramStudentFee[5] = new SqlParameter("@Admission", objModelStudentFee.Admission);
paramStudentFee[6] = new SqlParameter("@ReAdmissionFee", objModelStudentFee.ReAdmissionFee);
paramStudentFee[7] = new SqlParameter("@RegistrationFee", objModelStudentFee.RegistrationFee);
paramStudentFee[8] = new SqlParameter("@ComputerFee", objModelStudentFee.ComputerFee);
paramStudentFee[9] = new SqlParameter("@Fine", objModelStudentFee.Fine);
paramStudentFee[10] = new SqlParameter("@ExamFee", objModelStudentFee.ExamFee);
paramStudentFee[11] = new SqlParameter("@GeneratorFee", objModelStudentFee.GeneratorFee);
intStatus = DALCommon.ExecuteTransaction("[sp_Admin_InsertStudentMonthlyFeeBulkStudentId]", cmd, paramStudentFee);
if (intStatus >= 1)
{
updatedTotalRecord = updatedTotalRecord + 1;
}
else
{
DALUtility.ErrorLogBulkFeeInsertion(aStudent["StudentId"].ToString(), aStudent["MonthlyFee"].ToString(), "Bulk Fee noy Insert", AddedBy.ToString());
}
}
if (totalStudent == updatedTotalRecord)
{
//Commit Transaction
sqlTrans.Commit();
intStatus = 1;
}
else if (intStatus == -1)
{
strErrorMsg = "Error Occured while inserting record";
}
else if (intStatus == -2)
{
strErrorMsg = "Fee already exists for this month";
}
else if (intStatus == -3)
{
strErrorMsg = "Error occured while generation Serial Number index";
}
else if (intStatus == -4)
{
strErrorMsg = "Student stucked off";
}
//If Error Field Is Not Empty
if ((!string.IsNullOrEmpty(strErrorMsg)))
{
DALUtility.ErrorLog(strErrorMsg, "BulkFeeInsertionTransaction.cs, InsertStudentMonthlyFeeInsertTransaction");
}
}
catch (Exception ex)
{
sqlTrans.Rollback();
DALUtility.ErrorLog(ex.Message, "BulkFeeInsertionTransaction.cs, InsertStudentMonthlyFeeInsertTransaction");
}
finally
{
if ((conn.State == System.Data.ConnectionState.Open))
{
conn.Close();
}
cmd = null;
sqlTrans = null;
}
return(intStatus);
}
public int InsertUnPaidMonthlyFeeVoucherTransaction(string VoucherDueDate, DataTable studentList, decimal AddedBy)
{
//Initialization
conn = new SqlConnection(ConnectionString());
strErrorMsg = string.Empty;
intStatus = 0;
int totalStudent = studentList.Rows.Count;
int updatedTotalRecord = 0;
int ResultStatus = 0;
try
{
//Open Connection
if ((conn.State == System.Data.ConnectionState.Closed))
{
conn.Open();
}
//Begin Transaction
sqlTrans = conn.BeginTransaction();
//Initialize Command
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = sqlTrans;
//Update Transaction Response
foreach (DataRow aStudent in studentList.Rows)
{
double voucherId = 0;
SqlParameter[] paramStudentFee = new SqlParameter[3];
paramStudentFee[0] = new SqlParameter("@StudentId", aStudent["StudentId"]);
paramStudentFee[1] = new SqlParameter("@VoucherDueDate", VoucherDueDate);
paramStudentFee[2] = new SqlParameter("@AddedBy", AddedBy);
voucherId = DALCommon.ExecuteTransactionReturnIdentity("[sp_Admin_InsertStudentFeeVoucher]", cmd, paramStudentFee);
if (voucherId >= 1)
{
DataTable tblUnPaidFeeMonth = new DataTable();
SqlParameter[] paramUnPaidFeeMonth = new SqlParameter[3];
paramUnPaidFeeMonth[0] = new SqlParameter("@VoucherId", voucherId);
paramUnPaidFeeMonth[1] = new SqlParameter("@StudentId ", aStudent["StudentId"]);
paramUnPaidFeeMonth[2] = new SqlParameter("@AddedBy ", AddedBy);
//Bulk Monthly Fee Voucher Creating
ResultStatus = DALCommon.ExecuteTransaction("[sp_Admin_InsertFeeVoucherMonthsDataByVoucherID]", cmd, paramUnPaidFeeMonth);
if (ResultStatus > 0)
{
updatedTotalRecord = updatedTotalRecord + 1;
}
else
{
intStatus = -2;
strErrorMsg = "Error Occured while inserting record";
DALUtility.ErrorLog("Error occured inserting Monthly Fee Voucher record", "StudentFeeVoucherTransaction.cs, InsertUnPaidMonthlyFeeVoucherTransaction");
}
}
else
{
intStatus = -3;
strErrorMsg = "Error Occured while inserting record";
DALUtility.ErrorLog("Error occured generating VoucherId", "StudentFeeVoucherTransaction.cs, InsertUnPaidMonthlyFeeVoucherTransaction");
}
}
//If Error Field Is Not Empty
if ((!string.IsNullOrEmpty(strErrorMsg)))
{
DALUtility.ErrorLog(strErrorMsg, "BulkFeeInsertionTransaction.cs, InsertStudentMonthlyFeeInsertTransaction");
}
if (updatedTotalRecord == totalStudent)
{
sqlTrans.Commit();
intStatus = 1;
}
}
catch (Exception ex)
{
sqlTrans.Rollback();
DALUtility.ErrorLog(ex.Message, "StudentFeeVoucherTransaction.cs, InsertUnPaidMonthlyFeeVoucherTransaction");
}
finally
{
if ((conn.State == System.Data.ConnectionState.Open))
{
conn.Close();
}
cmd = null;
sqlTrans = null;
}
return(intStatus);
}
public int InsertFeeVoucherFeePaidTransaction(string VoucherPaidDate, double FeeVoucherId, DataTable FeeIdList, double AddedBy)
{
//Initialization
conn = new SqlConnection(ConnectionString());
strErrorMsg = string.Empty;
intStatus = 0;
int totalFeeId = FeeIdList.Rows.Count;
int updatedTotalRecord = 0;
int ResultStatus = 0;
try
{
//Open Connection
if ((conn.State == System.Data.ConnectionState.Closed))
{
conn.Open();
}
//Begin Transaction
sqlTrans = conn.BeginTransaction();
//Initialize Command
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = sqlTrans;
//Update Transaction Response
foreach (DataRow aFeeId in FeeIdList.Rows)
{
int ResultRecord = 0;
SqlParameter[] paramStudentFee = new SqlParameter[3];
paramStudentFee[0] = new SqlParameter("@FeeId", aFeeId["FeeId"]);
paramStudentFee[1] = new SqlParameter("@FeeDate", VoucherPaidDate);
paramStudentFee[2] = new SqlParameter("@AddedBy", AddedBy);
ResultRecord = DALCommon.ExecuteTransaction("[sp_Admin_UpdateStudentFeePaidMarkedByFeeId]", cmd, paramStudentFee);
if (ResultRecord > 0)
{
updatedTotalRecord = updatedTotalRecord + 1;
}
else
{
intStatus = -2;
strErrorMsg = "Error occured while marking fee month paid with fee Id";
}
}
if (updatedTotalRecord == totalFeeId)
{
SqlParameter[] paramFeeVoucher = new SqlParameter[1];
paramFeeVoucher[0] = new SqlParameter("@VoucherId", FeeVoucherId);
//Mark voucher paid
ResultStatus = DALCommon.ExecuteTransaction("[sp_Admin_UpdateStudentFeeVoucherPaidMarkedByVoucherId]", cmd, paramFeeVoucher);
if (ResultStatus > 0)
{
sqlTrans.Commit();
intStatus = 1;
}
}
else
{
intStatus = -3;
strErrorMsg = "Error Occured while marking fee voucher paid";
}
//If Error Field Is Not Empty
if ((!string.IsNullOrEmpty(strErrorMsg)))
{
DALUtility.ErrorLog(strErrorMsg, "BulkFeeInsertionTransaction.cs, InsertStudentMonthlyFeeInsertTransaction, InsertFeeVoucherFeePaidTransaction");
}
}
catch (Exception ex)
{
sqlTrans.Rollback();
DALUtility.ErrorLog(ex.Message, "StudentFeeVoucherTransaction.cs, InsertUnPaidMonthlyFeeVoucherTransaction");
}
finally
{
if ((conn.State == System.Data.ConnectionState.Open))
{
conn.Close();
}
cmd = null;
sqlTrans = null;
}
return(intStatus);
}