/// <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 { } }