/// <summary>
/// Function to get all the values for Report based on parameters
/// </summary>
/// <param name="strFromdate"></param>
/// <param name="strTodate"></param>
/// <param name="strSalaryMonth"></param>
/// <param name="strDesignation"></param>
/// <param name="strEmployee"></param>
/// <param name="strBonusOrDeduction"></param>
/// <returns></returns>
public DataTable BonusDeductionReportGridFill(string strFromdate, string strTodate, string strSalaryMonth, string strDesignation, string strEmployee, string strBonusOrDeduction)
{
DataTable dtbl = new DataTable();
try
{
SqlDataAdapter sqlda = new SqlDataAdapter("BonusDeductionReportGridFill", sqlcon);
sqlda.SelectCommand.CommandType = CommandType.StoredProcedure;
dtbl.Columns.Add("Sl No", typeof(int));
dtbl.Columns["Sl No"].AutoIncrement = true;
dtbl.Columns["Sl No"].AutoIncrementSeed = 1;
dtbl.Columns["Sl No"].AutoIncrementStep = 1;
sqlda.SelectCommand.Parameters.Add("@fromDate", SqlDbType.DateTime).Value = strFromdate;
sqlda.SelectCommand.Parameters.Add("@toDate", SqlDbType.DateTime).Value = strTodate;
sqlda.SelectCommand.Parameters.Add("@salaryMonth", SqlDbType.DateTime).Value = strSalaryMonth;
sqlda.SelectCommand.Parameters.Add("@designation", SqlDbType.VarChar).Value = strDesignation;
sqlda.SelectCommand.Parameters.Add("@employee", SqlDbType.VarChar).Value = strEmployee;
sqlda.SelectCommand.Parameters.Add("@bonusOrdeduction", SqlDbType.VarChar).Value = strBonusOrDeduction;
sqlda.Fill(dtbl);
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
return(dtbl);
}
/// <summary>
/// Function to check existence of SalesQuotation Number
/// </summary>
/// <param name="strInvoiceNo"></param>
/// <param name="decSalesQuotationVoucherTypeId"></param>
/// <returns></returns>
public bool CheckSalesQuotationNumberExistance(string strInvoiceNo, decimal decSalesQuotationVoucherTypeId)
{
bool isEdit = false;
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sqlcmd = new SqlCommand("CheckSalesQuotationNumberExistance", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sqlcmd.Parameters.Add("@invoiceNo", SqlDbType.VarChar);
sprmparam.Value = strInvoiceNo;
sprmparam = sqlcmd.Parameters.Add("@voucherTypeId", SqlDbType.Decimal);
sprmparam.Value = decSalesQuotationVoucherTypeId;
isEdit = Convert.ToBoolean(sqlcmd.ExecuteScalar());
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
finally
{
sqlcon.Close();
}
return(isEdit);
}
/// <summary>
/// Function to Daily Attendance Details Search GridFill
/// </summary>
/// <param name="strDate"></param>
/// <returns></returns>
public DataTable DailyAttendanceDetailsSearchGridFill(string strDate)
{
DataTable dtblAttendance = new DataTable();
dtblAttendance.Columns.Add("Sl NO", typeof(decimal));
dtblAttendance.Columns["Sl NO"].AutoIncrement = true;
dtblAttendance.Columns["Sl NO"].AutoIncrementSeed = 1;
dtblAttendance.Columns["Sl NO"].AutoIncrementStep = 1;
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlDataAdapter sdaadapter = new SqlDataAdapter("DailyAttendanceDetailsSearchGridFill", sqlcon);
sdaadapter.SelectCommand.CommandType = CommandType.StoredProcedure;
sdaadapter.SelectCommand.Parameters.Add("@date", SqlDbType.DateTime).Value = DateTime.Parse(strDate);
sdaadapter.Fill(dtblAttendance);
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
finally
{
sqlcon.Close();
}
return(dtblAttendance);
}
/// <summary>
/// Function to Update values in DailyAttendanceMaster Table
/// </summary>
/// <param name="dailyattendancemasterinfo"></param>
public void DailyAttendanceMasterEdit(DailyAttendanceMasterInfo dailyattendancemasterinfo)
{
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sccmd = new SqlCommand("DailyAttendanceMasterEdit", sqlcon);
sccmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sccmd.Parameters.Add("@dailyAttendanceMasterId", SqlDbType.Decimal);
sprmparam.Value = dailyattendancemasterinfo.DailyAttendanceMasterId;
sprmparam = sccmd.Parameters.Add("@date", SqlDbType.DateTime);
sprmparam.Value = dailyattendancemasterinfo.Date;
sprmparam = sccmd.Parameters.Add("@narration", SqlDbType.VarChar);
sprmparam.Value = dailyattendancemasterinfo.Narration;
sprmparam = sccmd.Parameters.Add("@extraDate", SqlDbType.DateTime);
sprmparam.Value = dailyattendancemasterinfo.ExtraDate;
sprmparam = sccmd.Parameters.Add("@extra1", SqlDbType.VarChar);
sprmparam.Value = dailyattendancemasterinfo.Extra1;
sprmparam = sccmd.Parameters.Add("@extra2", SqlDbType.VarChar);
sprmparam.Value = dailyattendancemasterinfo.Extra2;
sccmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
finally
{
sqlcon.Close();
}
}
/// <summary>
/// Function to view all company for selectcompany form
/// </summary>
/// <returns></returns>
public DataTable CompanyViewAllForSelectCompany()
{
DataTable dtbl = new DataTable();
dtbl.Columns.Add("SlNo", typeof(decimal));
dtbl.Columns["SlNo"].AutoIncrement = true;
dtbl.Columns["SlNo"].AutoIncrementSeed = 1;
dtbl.Columns["SlNo"].AutoIncrementStep = 1;
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlDataAdapter sqlda = new SqlDataAdapter("CompanyViewAllForSelectCompany", sqlcon);
sqlda.SelectCommand.CommandType = CommandType.StoredProcedure;
sqlda.Fill(dtbl);
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
finally
{
sqlcon.Close();
}
return(dtbl);
}
/// <summary>
/// Function to Daily Attendance View For Daily Attendance Report
/// </summary>
/// <param name="strDate"></param>
/// <param name="strStatus"></param>
/// <param name="strEmployeeCode"></param>
/// <param name="strDesignation"></param>
/// <returns></returns>
public DataTable DailyAttendanceViewForDailyAttendanceReport(string strDate, string strStatus, string strEmployeeCode, string strDesignation)
{
DataTable dtbl = new DataTable();
try
{
SqlDataAdapter sqlda = new SqlDataAdapter("DailyAttendanceViewForDailyAttendanceReport", sqlcon);
sqlda.SelectCommand.CommandType = CommandType.StoredProcedure;
dtbl.Columns.Add("SlNo", typeof(decimal));
dtbl.Columns["SlNo"].AutoIncrement = true;
dtbl.Columns["SlNo"].AutoIncrementSeed = 1;
dtbl.Columns["SlNo"].AutoIncrementStep = 1;
sqlda.SelectCommand.Parameters.Add("@workingDay", SqlDbType.VarChar).Value = strDate;
sqlda.SelectCommand.Parameters.Add("@employeeCode", SqlDbType.VarChar).Value = strEmployeeCode;
sqlda.SelectCommand.Parameters.Add("@designation", SqlDbType.VarChar).Value = strDesignation;
sqlda.SelectCommand.Parameters.Add("@status", SqlDbType.VarChar).Value = strStatus;
sqlda.Fill(dtbl);
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
return(dtbl);
}
/// <summary>
/// Function to view HoildaySettings based on parameters
/// </summary>
/// <param name="strMonth"></param>
/// <param name="strYear"></param>
/// <returns></returns>
public DataTable HoildaySettingsViewAllLimited(string strMonth, string strYear)
{
DataTable dtblHolidaySettings = new DataTable();
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlDataAdapter sqlda = new SqlDataAdapter("HoildaySettingsViewAllLimited", sqlcon);
sqlda.SelectCommand.CommandType = CommandType.StoredProcedure;
sqlda.SelectCommand.Parameters.Add("@Month", SqlDbType.VarChar).Value = strMonth;
sqlda.SelectCommand.Parameters.Add("@Year", SqlDbType.VarChar).Value = strYear;
sqlda.Fill(dtblHolidaySettings);
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
finally
{
sqlcon.Close();
}
return(dtblHolidaySettings);
}
/// <summary>
/// Function to get payhead type
/// </summary>
/// <param name="payHeadId"></param>
/// <returns></returns>
public string PayHeadTypeView(decimal payHeadId)
{
string price = null;
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sqlcmd = new SqlCommand("PayHeadTypeView", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.Add("@payHeadId", SqlDbType.Decimal).Value = payHeadId;
price = (sqlcmd.ExecuteScalar().ToString());
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
finally
{
sqlcon.Close();
}
return(price);
}
/// <summary>
/// Function to check existence of StandardRate based on the parameter
/// </summary>
/// <param name="decstandardRateId"></param>
/// <param name="dtapplicableFrom"></param>
/// <param name="dtapplicableTo"></param>
/// <param name="decProductId"></param>
/// <param name="decBatchId"></param>
/// <returns></returns>
public bool StandardrateCheckExistence(decimal decstandardRateId, DateTime dtapplicableFrom, DateTime dtapplicableTo, decimal decProductId, decimal decBatchId)
{
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sqlcmd = new SqlCommand("StandardrateCheckExistence", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sqlcmd.Parameters.Add("@standardRateId", SqlDbType.Decimal);
sprmparam.Value = decstandardRateId;
sprmparam = sqlcmd.Parameters.Add("@applicableFrom", SqlDbType.DateTime);
sprmparam.Value = dtapplicableFrom;
sprmparam = sqlcmd.Parameters.Add("@applicableTo", SqlDbType.DateTime);
sprmparam.Value = dtapplicableTo;
sprmparam = sqlcmd.Parameters.Add("@productId", SqlDbType.Decimal);
sprmparam.Value = decProductId;
sprmparam = sqlcmd.Parameters.Add("@batchId", SqlDbType.Decimal);
sprmparam.Value = decBatchId;
object obj = sqlcmd.ExecuteScalar();
decimal decCount = 0;
if (obj != null)
{
decCount = Convert.ToDecimal(obj.ToString());
}
if (decCount > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
finally
{
sqlcon.Close();
}
return(false);
}
/// <summary>
/// Function to check existence for insert based on parameter
/// </summary>
/// <param name="strfromDate"></param>
/// <param name="strtoDate"></param>
/// <param name="decvoucherTypeId"></param>
/// <param name="decsuffixprefixId"></param>
/// <returns></returns>
public bool SuffixPrefixCheckExistenceForAdd(String strfromDate, String strtoDate, decimal decvoucherTypeId, decimal decsuffixprefixId)
{
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sqlcmd = new SqlCommand("SuffixPrefixCheckExistenceForAdd", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sqlcmd.Parameters.Add("@fromDate", SqlDbType.DateTime);
sprmparam.Value = DateTime.Parse(strfromDate);
sprmparam = sqlcmd.Parameters.Add("@toDate", SqlDbType.DateTime);
sprmparam.Value = DateTime.Parse(strtoDate);
sprmparam = sqlcmd.Parameters.Add("@voucherTypeId", SqlDbType.Decimal);
sprmparam.Value = decvoucherTypeId;
sprmparam = sqlcmd.Parameters.Add("@suffixprefixId", SqlDbType.Decimal);
sprmparam.Value = decsuffixprefixId;
object obj = sqlcmd.ExecuteScalar();
decimal decCount = 0;
if (obj != null)
{
decCount = decimal.Parse(obj.ToString());
}
if (decCount > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
finally
{
sqlcon.Close();
}
return(false);
}
/// <summary>
/// Function to PriceList CheckExistence
/// </summary>
/// <param name="decpricelistId"></param>
/// <param name="decpricinglevelId"></param>
/// <param name="decbatchId"></param>
/// <param name="decProductId"></param>
/// <returns></returns>
public bool PriceListCheckExistence(decimal decpricelistId, decimal decpricinglevelId, decimal decbatchId, decimal decProductId)
{
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sqlcmd = new SqlCommand("PriceListCheckExistence", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sqlcmd.Parameters.Add("@pricelistId", SqlDbType.Decimal);
sprmparam.Value = decpricelistId;
sprmparam = sqlcmd.Parameters.Add("@pricinglevelId", SqlDbType.Decimal);
sprmparam.Value = decpricinglevelId;
sprmparam = sqlcmd.Parameters.Add("@batchId", SqlDbType.Decimal);
sprmparam.Value = decbatchId;
sprmparam = sqlcmd.Parameters.Add("@productId", SqlDbType.Decimal);
sprmparam.Value = decProductId;
object obj = sqlcmd.ExecuteScalar();
decimal decCount = 0;
if (obj != null)
{
decCount = Convert.ToDecimal(obj.ToString());
}
if (decCount > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
finally
{
sqlcon.Close();
}
return(false);
}
/// <summary>
/// Function to check existence based on parameters and return status
/// </summary>
/// <param name="dtDate"></param>
/// <param name="decCurrencyId"></param>
/// <param name="decExchangeRateId"></param>
/// <returns></returns>
public bool ExchangeRateCheckExistence(DateTime dtDate, decimal decCurrencyId, decimal decExchangeRateId)
{
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sqlcmd = new SqlCommand("ExchangeRateCheckExistence", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sqlcmd.Parameters.Add("@date", SqlDbType.DateTime);
sprmparam.Value = dtDate;
sprmparam = sqlcmd.Parameters.Add("@exchangeRateId", SqlDbType.Decimal);
sprmparam.Value = decExchangeRateId;
sprmparam = sqlcmd.Parameters.Add("@currencyId", SqlDbType.Decimal);
sprmparam.Value = decCurrencyId;
object obj = sqlcmd.ExecuteScalar();
decimal decCount = 0;
if (obj != null)
{
decCount = Convert.ToDecimal(obj.ToString());
}
if (decCount > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
finally
{
sqlcon.Close();
}
return(false);
}
/// <summary>
/// Function to check existance of route
/// </summary>
/// <param name="strRouteName"></param>
/// <param name="decRouteId"></param>
/// <param name="decAreaId"></param>
/// <returns></returns>
public bool RouteCheckExistence(String strRouteName, decimal decRouteId, decimal decAreaId)
{
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sqlcmd = new SqlCommand("RouteCheckExistence", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sqlcmd.Parameters.Add("@routeName", SqlDbType.VarChar);
sprmparam.Value = strRouteName;
sprmparam = sqlcmd.Parameters.Add("@routeId", SqlDbType.Decimal);
sprmparam.Value = decRouteId;
sprmparam = sqlcmd.Parameters.Add("@areaId", SqlDbType.Decimal);
sprmparam.Value = decAreaId;
object obj = sqlcmd.ExecuteScalar();
decimal decCount = 0;
if (obj != null)
{
decCount = decimal.Parse(obj.ToString());
}
if (decCount > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
finally
{
sqlcon.Close();
}
return(false);
}
/// <summary>
/// Function to Daily Salary Voucher Check Existence
/// </summary>
/// <param name="voucherNumber"></param>
/// <param name="voucherTypeId"></param>
/// <param name="masterId"></param>
/// <returns></returns>
public bool DailySalaryVoucherCheckExistence(string voucherNumber, decimal voucherTypeId, decimal masterId)
{
bool trueOrfalse = false;
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sccmd = new SqlCommand("DailySalaryVoucherCheckExistence", sqlcon);
sccmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sccmd.Parameters.Add("@voucherNo", SqlDbType.VarChar);
sprmparam.Value = voucherNumber;
sprmparam = sccmd.Parameters.Add("@dailySalaryVoucherMasterId", SqlDbType.Decimal);
sprmparam.Value = masterId;
sprmparam = sccmd.Parameters.Add("@voucherTypeId", SqlDbType.Decimal);
sprmparam.Value = voucherTypeId;
object obj = sccmd.ExecuteScalar();
if (obj != null)
{
if (int.Parse(obj.ToString()) == 0)
{
trueOrfalse = true;
}
}
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
finally
{
sqlcon.Close();
}
return(trueOrfalse);
}
/// <summary>
/// Function to check existence of service category based on parameter
/// </summary>
/// <param name="strCategoryName"></param>
/// <param name="decServiceCategoryId"></param>
/// <returns></returns>
public bool ServiceCategoryCheckIfExist(String strCategoryName, decimal decServiceCategoryId)
{
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sqlcmd = new SqlCommand("ServiceCategoryCheckIfExist", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sqlcmd.Parameters.Add("@categoryName", SqlDbType.VarChar);
sprmparam.Value = strCategoryName;
sprmparam = sqlcmd.Parameters.Add("@serviceCategoryId", SqlDbType.Decimal);
sprmparam.Value = decServiceCategoryId;
object obj = sqlcmd.ExecuteScalar();
decimal decCount = 0;
if (obj != null)
{
decCount = Convert.ToDecimal(obj.ToString());
}
if (decCount > 0)
{
return(true);
}
else
{
return(false);;
}
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
finally
{
sqlcon.Close();
}
return(false);
}
/// <summary>
/// Function to add Daily Attendance Add To its Master table
/// </summary>
/// <param name="dailyattendancemasterinfo"></param>
/// <returns></returns>
public decimal DailyAttendanceAddToMaster(DailyAttendanceMasterInfo dailyattendancemasterinfo)
{
decimal incount = 0;
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sccmd = new SqlCommand("DailyAttendanceAddToMaster", sqlcon);
sccmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sccmd.Parameters.Add("@date", SqlDbType.DateTime);
sprmparam.Value = dailyattendancemasterinfo.Date;
sprmparam = sccmd.Parameters.Add("@narration", SqlDbType.VarChar);
sprmparam.Value = dailyattendancemasterinfo.Narration;
sprmparam = sccmd.Parameters.Add("@extra1", SqlDbType.VarChar);
sprmparam.Value = dailyattendancemasterinfo.Extra1;
sprmparam = sccmd.Parameters.Add("@extra2", SqlDbType.VarChar);
sprmparam.Value = dailyattendancemasterinfo.Extra2;
Object obj = sccmd.ExecuteScalar();
if (obj != null)
{
incount = decimal.Parse(obj.ToString());
}
{
}
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
finally
{
sqlcon.Close();
}
return(incount);
}
/// <summary>
/// Function to get particular values from DailyAttendanceMaster Table based on the parameter
/// </summary>
/// <param name="dailyAttendanceMasterId"></param>
/// <returns></returns>
public DailyAttendanceMasterInfo DailyAttendanceMasterView(decimal dailyAttendanceMasterId)
{
DailyAttendanceMasterInfo dailyattendancemasterinfo = new DailyAttendanceMasterInfo();
SqlDataReader sdrreader = null;
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sccmd = new SqlCommand("DailyAttendanceMasterView", sqlcon);
sccmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sccmd.Parameters.Add("@dailyAttendanceMasterId", SqlDbType.Decimal);
sprmparam.Value = dailyAttendanceMasterId;
sdrreader = sccmd.ExecuteReader();
while (sdrreader.Read())
{
dailyattendancemasterinfo.DailyAttendanceMasterId = decimal.Parse(sdrreader[0].ToString());
dailyattendancemasterinfo.Date = DateTime.Parse(sdrreader[1].ToString());
dailyattendancemasterinfo.Narration = sdrreader[2].ToString();
dailyattendancemasterinfo.ExtraDate = DateTime.Parse(sdrreader[3].ToString());
dailyattendancemasterinfo.Extra1 = sdrreader[4].ToString();
dailyattendancemasterinfo.Extra2 = sdrreader[5].ToString();
}
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
finally
{
sdrreader.Close();
sqlcon.Close();
}
return(dailyattendancemasterinfo);
}
/// <summary>
/// Function to check the existance of purchase order number
/// </summary>
/// <param name="strinvoiceNo"></param>
/// <param name="decPurchaseorderMasterId"></param>
/// <param name="decVoucherTypeId"></param>
/// <returns></returns>
public bool PurchaseOrderNumberCheckExistence(string strinvoiceNo, decimal decPurchaseorderMasterId, decimal decVoucherTypeId)
{
bool isEdit = false;
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sqlcmd = new SqlCommand("PurchaseOrderNumberCheckExistence", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sqlcmd.Parameters.Add("@invoiceNo", SqlDbType.VarChar);
sprmparam.Value = strinvoiceNo;
sprmparam = sqlcmd.Parameters.Add("@voucherTypeId", SqlDbType.VarChar);
sprmparam.Value = decVoucherTypeId;
sprmparam = sqlcmd.Parameters.Add("@purchaseOrderMasterId", SqlDbType.Decimal);
sprmparam.Value = decPurchaseorderMasterId;
object obj = sqlcmd.ExecuteScalar();
if (obj != null)
{
if (int.Parse(obj.ToString()) == 1)
{
isEdit = true;
}
}
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
finally
{
sqlcon.Close();
}
return(isEdit);
}
/// <summary>
/// Function to get all the values from DailyAttendanceMaster Table
/// </summary>
/// <returns></returns>
public DataTable DailyAttendanceMasterViewAll()
{
DataTable dtbl = new DataTable();
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlDataAdapter sdaadapter = new SqlDataAdapter("DailyAttendanceMasterViewAll", sqlcon);
sdaadapter.SelectCommand.CommandType = CommandType.StoredProcedure;
sdaadapter.Fill(dtbl);
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
finally
{
sqlcon.Close();
}
return dtbl;
}
/// <summary>
/// Company current date edit
/// </summary>
/// <param name="dtCurrentDate"></param>
public void CompanyCurrentDateEdit(DateTime dtCurrentDate)
{
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sqlcmd = new SqlCommand("CompanyCurrentDateEdit", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.Add("@companyId", SqlDbType.Decimal).Value = 1;//PublicVariables._decCurrentCompanyId;
sqlcmd.Parameters.Add("@currentDate", SqlDbType.DateTime).Value = dtCurrentDate;
sqlcmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
finally
{
sqlcon.Close();
}
}
/// <summary>
/// Function to get the next id for DailyAttendanceMaster Table
/// </summary>
/// <returns></returns>
public int DailyAttendanceMasterGetMax()
{
int max = 0;
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sccmd = new SqlCommand("DailyAttendanceMasterMax", sqlcon);
sccmd.CommandType = CommandType.StoredProcedure;
max = int.Parse(sccmd.ExecuteScalar().ToString());
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
finally
{
sqlcon.Close();
}
return max;
}
/// <summary>
/// Function to delete particular details based on the parameter From Table DailyAttendanceMaster
/// </summary>
/// <param name="DailyAttendanceMasterId"></param>
public void DailyAttendanceMasterDelete(decimal DailyAttendanceMasterId)
{
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sccmd = new SqlCommand("DailyAttendanceMasterDelete", sqlcon);
sccmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sccmd.Parameters.Add("@dailyAttendanceMasterId", SqlDbType.Decimal);
sprmparam.Value = DailyAttendanceMasterId;
sccmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
finally
{
sqlcon.Close();
}
}
/// <summary>
/// Function to check References of account group for add or delete
/// </summary>
/// <param name="strAccountGroupName"></param>
/// <param name="decAccountGroupId"></param>
/// <returns></returns>
public bool AccountGroupCheckExistence(string strAccountGroupName, decimal decAccountGroupId)
{
bool isEdit = false;
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sqlcmd = new SqlCommand("AccountGroupCheckExistence", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sqlcmd.Parameters.Add("@accountGroupName", SqlDbType.VarChar);
sprmparam.Value = strAccountGroupName;
sprmparam = sqlcmd.Parameters.Add("@accountGroupId", SqlDbType.Decimal);
sprmparam.Value = decAccountGroupId;
object obj = sqlcmd.ExecuteScalar();
if (obj != null)
{
if (int.Parse(obj.ToString()) == 1)
{
isEdit = true;
}
}
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
finally
{
sqlcon.Close();
}
return(isEdit);
}
/// <summary>
/// Company get Id for the company
/// </summary>
/// <returns></returns>
public decimal CompanyGetIdIfSingleCompany()
{
decimal decCompanyId = 0;
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sccmd = new SqlCommand("CompanyGetIdIfSingleCompany", sqlcon);
sccmd.CommandType = CommandType.StoredProcedure;
decCompanyId = Convert.ToDecimal(sccmd.ExecuteScalar().ToString());
}
catch (Exception ex)
{
Messages.ErrorMessage(ex.ToString());
}
finally
{
sqlcon.Close();
}
return(decCompanyId);
}
/// <summary>
/// Function to restore DB
/// </summary>
public void ReStoreDB()
{
string currDBName = sqlcon.Database;
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
string DBName = "";// DB name
DBName = sqlcon.Database;
currDBName = sqlcon.Database;
string[] arr = DBName.Split('\\');
DBName = arr[arr.Length - 1].Replace(".MDF", "");
string DBFolder = ""; // Folder name (company id)
DBFolder = arr[arr.Length - 2];
OpenFileDialog opDialog = new OpenFileDialog();
opDialog.Filter = "BackUp Files (*.bak)|*.bak";
opDialog.Title = "Select your .bak file for restore the database Openmiracle";
string path = "";
path = (ConfigurationManager.AppSettings["ApplicationPath"] == null || ConfigurationManager.AppSettings["ApplicationPath"].ToString() == null) ? null : ConfigurationManager.AppSettings["ApplicationPath"].ToString() + "\\Data\\" + DBFolder + "\\" + DBName + ".MDF";
if (opDialog.ShowDialog() == DialogResult.OK)
{
string filePath = Path.GetFullPath(opDialog.FileName);
{
string fileName = filePath;
string databaseName = DBName;
string dataFilePath = (ConfigurationManager.AppSettings["ApplicationPath"] == null || ConfigurationManager.AppSettings["ApplicationPath"].ToString() == null) ? null : ConfigurationManager.AppSettings["ApplicationPath"].ToString() + "\\Data\\" + DBFolder;
String dataFileLocation = dataFilePath + @"\" + DBName + ".mdf";
String logFileLocation = dataFilePath + @"\" + DBName + "_log.ldf";
string strSqlFirst = "RESTORE FILELISTONLY FROM DISK = '" + fileName + "'";
string strSql = "ALTER DATABASE [" + sqlcon.Database + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
string strSqlRestore = "RESTORE DATABASE [" + sqlcon.Database + "] FROM DISK = '" + fileName + "' WITH MOVE '" + "DBOpenmiracle" + "' TO '" + dataFileLocation + "', MOVE '" + "DBOpenmiracle_log" + "' TO '" + logFileLocation + "'";
string strAlter = "ALTER DATABASE [" + sqlcon.Database + "] SET MULTI_USER";
sqlcon.ChangeDatabase("master");
SqlCommand sqlCmd1 = new SqlCommand(strSqlFirst + "\n" + strSql + "\n" + strSqlRestore + "\n" + strAlter, sqlcon);
sqlCmd1.CommandType = CommandType.Text;
sqlCmd1.ExecuteNonQuery();
MessageBox.Show("Restore of " + databaseName +
" Complete!", "Restore", MessageBoxButtons.OK, MessageBoxIcon.Information);
try
{
sqlcon.ChangeDatabase(currDBName);
}
catch
{
//catches Exception during DataBase change
}
//After Restore Checking whether the Database is ok or not [if 'ChangeDatabase' fails, its required to restart application for reseting the db connection]
DataTable dtbl = new DataTable();
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlDataAdapter sdaadapter = new SqlDataAdapter("CompanyViewAll", sqlcon);
sdaadapter.SelectCommand.CommandType = CommandType.StoredProcedure;
sdaadapter.Fill(dtbl);
}
catch
{
///cathes any error due to DataBase Restore
Messages.InformationMessage("Please close and re-open your application");
Application.Exit();
}
finally
{
sqlcon.Close();
}
}
}
}
catch
{
Messages.ErrorMessage("Restore failed for database");
try
{
//For changing database to MultiUser mode if any Restore operation terminated abnormaly
string AlterAgain = "ALTER DATABASE [" + sqlcon.Database + "] SET MULTI_USER";
SqlCommand sqlCmd = new SqlCommand(AlterAgain);
sqlCmd.CommandType = CommandType.Text;
sqlCmd.ExecuteNonQuery();
}
catch
{
//Catches any exception during Alter DB
}
}
finally
{
}
}