public double EmployeeTemplateInsert(double IDS
, int UserID
, int FingerID
, int Template
, int TmpLen
, int Flag
)
{
SqlConnection conn = null;
try
{
// create and open a connection object
Variables var = new Variables();
conn = new SqlConnection(var.ConString());
conn.Open();
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("EmployeeTemplateInsert", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add IDS Int IDENTITY(1,1) NOT NULL PRIMARY KEY,
cmd.Parameters.Add(new SqlParameter("@UserID", UserID));
cmd.Parameters.Add(new SqlParameter("@FingerID", FingerID));
cmd.Parameters.Add(new SqlParameter("@Template", Template));
cmd.Parameters.Add(new SqlParameter("@TmpLen", TmpLen));
cmd.Parameters.Add(new SqlParameter("@Flag", Flag));
// cmd.Parameters.Add(new SqlParameter("@PersonImage", null));
//Add the output parameter to the command object
SqlParameter outPutParameter = new SqlParameter();
outPutParameter.ParameterName = "@IDSOut";
outPutParameter.SqlDbType = System.Data.SqlDbType.BigInt;
outPutParameter.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(outPutParameter);
// execute the command
//cmd.ExecuteReader();
cmd.ExecuteNonQuery();
return(Convert.ToDouble(outPutParameter.Value));
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
return(0);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
private void cmdLogin_Click(object sender, EventArgs e)
{
Variables var = new Variables();
Variables.DBName = txtDatabase.Text;
Variables.ServerName = txtServer.Text;
Variables.UserName = txtUser.Text;
Variables.Password = txtPassword.Text;
try
{
using (SqlConnection sqlConn = new SqlConnection(var.ConString()))
{
sqlConn.Open();
if (sqlConn.State == ConnectionState.Open)
{
mdiPayrollMenu parent = new mdiPayrollMenu();
parent.Show();
this.Hide();
}
else
{
MessageBox.Show("Cannot established connection.");
}
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
public double PayrollInsert(
double IDS
, DateTime PayrollDate
, DateTime DateFrom
, DateTime DateTo
, double Department
, string Remarks
)
{
SqlConnection conn = null;
try
{
// create and open a connection object
Variables var = new Variables();
conn = new SqlConnection(var.ConString());
conn.Open();
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("PayrollInsert", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@IDS", IDS));
cmd.Parameters.Add(new SqlParameter("@PayrollDate", PayrollDate));
cmd.Parameters.Add(new SqlParameter("@DateFrom", DateFrom));
cmd.Parameters.Add(new SqlParameter("@DateTo", DateTo));
cmd.Parameters.Add(new SqlParameter("@Department", Department));
cmd.Parameters.Add(new SqlParameter("@Remarks", Remarks));
//Add the output parameter to the command object
SqlParameter outPutParameter = new SqlParameter();
outPutParameter.ParameterName = "@IDSOut";
outPutParameter.SqlDbType = System.Data.SqlDbType.BigInt;
outPutParameter.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(outPutParameter);
// execute the command
//cmd.ExecuteReader();
cmd.ExecuteNonQuery();
return(Convert.ToDouble(outPutParameter.Value));
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
return(0);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public void AttendanceRuleUpdate(
double IDS
, DateTime PunchInRule
, DateTime BreakOutRule
, DateTime BreakInRule
, DateTime PunchOutRule
, DateTime OvertimeInRule
, DateTime OvertimeOutRule
, string ShiftName
, int DayOfTheWeek
, decimal LatePerMinutePenalty
)
{
SqlConnection conn = null;
try
{
// create and open a connection object
Variables var = new Variables();
conn = new SqlConnection(var.ConString());
conn.Open();
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("AttendanceRuleUpdate", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@IDS", IDS));
cmd.Parameters.Add(new SqlParameter("@PunchInRule", PunchInRule));
cmd.Parameters.Add(new SqlParameter("@BreakOutRule", BreakOutRule));
cmd.Parameters.Add(new SqlParameter("@BreakInRule", BreakInRule));
cmd.Parameters.Add(new SqlParameter("@PunchOutRule", PunchOutRule));
cmd.Parameters.Add(new SqlParameter("@OTInRule", OvertimeInRule));
cmd.Parameters.Add(new SqlParameter("@OTOutRule", OvertimeOutRule));
cmd.Parameters.Add(new SqlParameter("@ShiftName", ShiftName));
cmd.Parameters.Add(new SqlParameter("@DayOfTheWeek", DayOfTheWeek));
cmd.Parameters.Add(new SqlParameter("@LatePerMinutePenalty", LatePerMinutePenalty));
// execute the command
//cmd.ExecuteReader();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public DataTable PayrollSelectInitial(DateTime DateFrom, DateTime DateTo, double Department, int SSSPrem, int PHICPrem, int PagIbigPrem, int SSSLoan, int PagIbigLoan, int Savings)
{
SqlConnection conn = null;
SqlDataAdapter adpt = null;
try
{
// create and open a connection object
Variables var = new Variables();
conn = new SqlConnection(var.ConString());
conn.Open();
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("GetInitialPayroll", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@DateFrom", DateFrom));
cmd.Parameters.Add(new SqlParameter("@DateTo", DateTo));
cmd.Parameters.Add(new SqlParameter("@Department", Department));
//int SSSPrem, int PHICPrem, int PagIbigPrem, int SSSLoan, int PagIbigLoan, int Savings
cmd.Parameters.Add(new SqlParameter("@SSSPrem", SSSPrem));
cmd.Parameters.Add(new SqlParameter("@PHICPrem", PHICPrem));
cmd.Parameters.Add(new SqlParameter("@PagIbigPrem", PagIbigPrem));
cmd.Parameters.Add(new SqlParameter("@SSSLoan", SSSLoan));
cmd.Parameters.Add(new SqlParameter("@PagIbigLoan", PagIbigLoan));
cmd.Parameters.Add(new SqlParameter("@Savings", Savings));
// execute the command
// cmd.ExecuteReader();
adpt = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adpt.Fill(dt);
return(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
return(null);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public void MachineUpdate(double IDS
, string MachineName
, string MachineNumber
, string PortNumber
, string IPAddress
, string BaudRate
, string ComType
, int Stat)
{
SqlConnection conn = null;
try
{
// create and open a connection object
Variables var = new Variables();
conn = new SqlConnection(var.ConString());
conn.Open();
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("MachineUpdate", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@IDS", IDS));
cmd.Parameters.Add(new SqlParameter("@MachineName", MachineName));
cmd.Parameters.Add(new SqlParameter("@MachineNumber", MachineNumber));
cmd.Parameters.Add(new SqlParameter("@PortNumber", PortNumber));
cmd.Parameters.Add(new SqlParameter("@IPAddress", IPAddress));
cmd.Parameters.Add(new SqlParameter("@BaudRate", BaudRate));
cmd.Parameters.Add(new SqlParameter("@ComType", ComType));
cmd.Parameters.Add(new SqlParameter("@Stat", Stat));
// cmd.Parameters.Add(new SqlParameter("@PersonImage", null));
// execute the command
//cmd.ExecuteReader();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public void EmployeeTemplateUpdate(double IDS
, int UserID
, int FingerID
, byte[] Template
, int TmpLen
, int Flag
)
{
SqlConnection conn = null;
try
{
// create and open a connection object
Variables var = new Variables();
conn = new SqlConnection(var.ConString());
conn.Open();
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("EmployeeTemplateUpdate", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@UserID", UserID));
cmd.Parameters.Add(new SqlParameter("@FingerID", FingerID));
cmd.Parameters.Add(new SqlParameter("@Template", Template));
cmd.Parameters.Add(new SqlParameter("@TmpLen", TmpLen));
cmd.Parameters.Add(new SqlParameter("@Flag", Flag));
// cmd.Parameters.Add(new SqlParameter("@PersonImage", null));
// execute the command
//cmd.ExecuteReader();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public void PayrollUpdate(double IDS
, DateTime PayrollDate
, DateTime DateFrom
, DateTime DateTo
, double Department
, string Remarks)
{
SqlConnection conn = null;
try
{
// create and open a connection object
Variables var = new Variables();
conn = new SqlConnection(var.ConString());
conn.Open();
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("PayrollUpdate", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@IDS", IDS));
cmd.Parameters.Add(new SqlParameter("@PayrollDate", PayrollDate));
cmd.Parameters.Add(new SqlParameter("@DateFrom", DateFrom));
cmd.Parameters.Add(new SqlParameter("@DateTo", DateTo));
cmd.Parameters.Add(new SqlParameter("@Department", Department));
cmd.Parameters.Add(new SqlParameter("@Remarks", Remarks));
// cmd.Parameters.Add(new SqlParameter("@PersonImage", null));
// execute the command
//cmd.ExecuteReader();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public DataTable AttendanceRuleSelect(double IDS)
{
SqlConnection conn = null;
SqlDataAdapter adpt = null;
try
{
// create and open a connection object
Variables var = new Variables();
conn = new SqlConnection(var.ConString());
conn.Open();
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("AttendanceRuleSelect", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@IDS", IDS));
// execute the command
// cmd.ExecuteReader();
adpt = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adpt.Fill(dt);
return(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
return(null);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public double RemoveAllEmployeeFromShift(string ShiftName)
{
SqlConnection conn = null;
try
{
// create and open a connection object
Variables var = new Variables();
conn = new SqlConnection(var.ConString());
conn.Open();
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("RemoveAllEmployeeFromShift", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@ShiftName", ShiftName));
// execute the command
// cmd.ExecuteReader();
cmd.ExecuteNonQuery();
return(0);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
return(1);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public void PayrollEmployeeUpdate(double IDS
, double EmployeeIDS
, double PayrollIDS
, double Department
, decimal NoOfDays
, decimal SalaryRate
, decimal OTHours
, decimal OTRate
, decimal Incentives
, decimal SSSPrem
, decimal PHICPrem
, decimal PagIbigPrem
, decimal SSSLoan
, decimal PagIbigLoan
, decimal CashAdvance
, decimal Savings
, decimal OtherDeduction
, decimal LateEO
, decimal LateEORate
)
{
SqlConnection conn = null;
try
{
// create and open a connection object
Variables var = new Variables();
conn = new SqlConnection(var.ConString());
conn.Open();
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("PayrollEmployeeUpdate", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@IDS", IDS));
cmd.Parameters.Add(new SqlParameter("@EmployeeIDS", EmployeeIDS));
cmd.Parameters.Add(new SqlParameter("@PayrollIDS", PayrollIDS));
cmd.Parameters.Add(new SqlParameter("@Department", Department));
cmd.Parameters.Add(new SqlParameter("@NoOfDays", NoOfDays));
cmd.Parameters.Add(new SqlParameter("@SalaryRate", SalaryRate));
cmd.Parameters.Add(new SqlParameter("@OTHours", OTHours));
cmd.Parameters.Add(new SqlParameter("@OTRate", OTRate));
cmd.Parameters.Add(new SqlParameter("@Incentives", Incentives));
cmd.Parameters.Add(new SqlParameter("@SSSPrem", SSSPrem));
cmd.Parameters.Add(new SqlParameter("@PHICPrem", PHICPrem));
cmd.Parameters.Add(new SqlParameter("@PagIbigPrem", PagIbigPrem));
cmd.Parameters.Add(new SqlParameter("@SSSLoan", SSSLoan));
cmd.Parameters.Add(new SqlParameter("@PagIbigLoan", PagIbigLoan));
cmd.Parameters.Add(new SqlParameter("@CashAdvance", CashAdvance));
cmd.Parameters.Add(new SqlParameter("@Savings", Savings));
cmd.Parameters.Add(new SqlParameter("@OtherDeduction", OtherDeduction));
cmd.Parameters.Add(new SqlParameter("@LateEO", LateEO));
cmd.Parameters.Add(new SqlParameter("@LateEORate", LateEORate));
// cmd.Parameters.Add(new SqlParameter("@PersonImage", null));
// execute the command
//cmd.ExecuteReader();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public double MachineInsert(double IDS
, string MachineName
, string MachineNumber
, string PortNumber
, string IPAddress
, string BaudRate
, string ComType
, int Stat)
{
SqlConnection conn = null;
try
{
// create and open a connection object
Variables var = new Variables();
conn = new SqlConnection(var.ConString());
conn.Open();
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("MachineInsert", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@IDS", IDS));
cmd.Parameters.Add(new SqlParameter("@MachineName", MachineName));
cmd.Parameters.Add(new SqlParameter("@MachineNumber", MachineNumber));
cmd.Parameters.Add(new SqlParameter("@PortNumber", PortNumber));
cmd.Parameters.Add(new SqlParameter("@IPAddress", IPAddress));
cmd.Parameters.Add(new SqlParameter("@BaudRate", BaudRate));
cmd.Parameters.Add(new SqlParameter("@ComType", ComType));
cmd.Parameters.Add(new SqlParameter("@Stat", Stat));
// cmd.Parameters.Add(new SqlParameter("@PersonImage", null));
//Add the output parameter to the command object
SqlParameter outPutParameter = new SqlParameter();
outPutParameter.ParameterName = "@IDSOut";
outPutParameter.SqlDbType = System.Data.SqlDbType.BigInt;
outPutParameter.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(outPutParameter);
// execute the command
//cmd.ExecuteReader();
cmd.ExecuteNonQuery();
return(Convert.ToDouble(outPutParameter.Value));
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
return(0);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public void EmployeeUpdate(double IDS
, string lName
, string fName
, string mName
, string AddressHome
, string ContactNo
, DateTime DateOfBirth
, DateTime DateOfEmployment
, int Gender
, int CivilStatus
, string Position
, int Department
, int Unit
, decimal Salary
, byte[] pic
, int Stat
, decimal SSSPrem
, decimal PHICPrem
, decimal PagIbigPrem
, decimal SSSLoan
, decimal PagIbigLoan
, decimal Savings
, decimal OTRate
// , string ShiftName
)
{
SqlConnection conn = null;
try
{
// create and open a connection object
Variables var = new Variables();
conn = new SqlConnection(var.ConString());
conn.Open();
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("EmployeeUpdate", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@IDS", IDS));
cmd.Parameters.Add(new SqlParameter("@lName", lName));
cmd.Parameters.Add(new SqlParameter("@fName", fName));
cmd.Parameters.Add(new SqlParameter("@mName", mName));
cmd.Parameters.Add(new SqlParameter("@AddressHome", AddressHome));
cmd.Parameters.Add(new SqlParameter("@ContactNo", ContactNo));
cmd.Parameters.Add(new SqlParameter("@DateOfBirth", DateOfBirth));
cmd.Parameters.Add(new SqlParameter("@DateOfEmployment", DateOfEmployment));
cmd.Parameters.Add(new SqlParameter("@Gender", Gender));
cmd.Parameters.Add(new SqlParameter("@CivilStatus", CivilStatus));
cmd.Parameters.Add(new SqlParameter("@Position", Position));
cmd.Parameters.Add(new SqlParameter("@Department", Department));
cmd.Parameters.Add(new SqlParameter("@Salary", Salary));
cmd.Parameters.Add(new SqlParameter("@Unit", Unit));
cmd.Parameters.Add(new SqlParameter("@PersonImage", pic));
cmd.Parameters.Add(new SqlParameter("@Stat", Stat));
cmd.Parameters.Add(new SqlParameter("@SSSPrem", SSSPrem));
cmd.Parameters.Add(new SqlParameter("@PHICPrem", PHICPrem));
cmd.Parameters.Add(new SqlParameter("@PagIbigPrem", PagIbigPrem));
cmd.Parameters.Add(new SqlParameter("@SSSLoan", SSSLoan));
cmd.Parameters.Add(new SqlParameter("@PagIbigLoan", PagIbigLoan));
cmd.Parameters.Add(new SqlParameter("@Savings", Savings));
cmd.Parameters.Add(new SqlParameter("@OTRate", OTRate));
// cmd.Parameters.Add(new SqlParameter("@ShiftName", ShiftName));
// cmd.Parameters.Add(new SqlParameter("@PersonImage", null));
// execute the command
//cmd.ExecuteReader();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
