public List <CashReconciliation> GetAllCashReconcilation() { //...Create New Instance of Object... List <CashReconciliation> list = new List <CashReconciliation>(); CashReconciliation ins; //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); SqlCommand cmdI; //...SQL Commands... cmdI = new SqlCommand("SELECT cr.*,mt.MovementType,e.Name,rt.Reconciliation FROM t_CashReconciliation cr inner join l_MovementType mt on cr.MovementTypeID = mt.MovementTypeID inner join Employee e on cr.EmployeeID = e.EmployeeID inner join l_ReconciliationType rt on cr.ReconciliationTypeID = rt.ReconciliationTypeID where cr.Removed=0", con); cmdI.Connection.Open(); SqlDataReader drI = cmdI.ExecuteReader(); //...Retrieve Data... if (drI.HasRows) { while (drI.Read()) { ins = new CashReconciliation(); ins.CashReconciliationID = Convert.ToInt32(drI["CashReconcilationID"]); ins.ActualDate = Convert.ToDateTime(drI["ActualDate"]); ins.CreatedDate = Convert.ToDateTime(drI["CreatedDate"]); ins.ReconciliationTypeID = Convert.ToInt32(drI["ReconciliationTypeID"]); ins.MovementTypeID = Convert.ToInt32(drI["MovementTypeID"]); ins.EmployeeID = Convert.ToInt32(drI["EmployeeID"]); ins.recontype = drI["Reconciliation"].ToString(); ins.employee = drI["Name"].ToString(); ins.movementtype = drI["MovementType"].ToString(); ins.UserID = Convert.ToString(drI["UserID"]); ins.CompanyID = Convert.ToInt32(drI["CompanyID"]); ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]); ins.ModifiedBy = Convert.ToString(drI["ModifiedBy"]); ins.Removed = Convert.ToBoolean(drI["Removed"]); ins.Amount = Convert.ToDecimal(drI["Amount"]); list.Add(ins); } } //...Close Connections... drI.Close(); con.Close(); //...Return... return(list); }
public List<CashReconciliation> GetAllCashReconcilation() { //...Create New Instance of Object... List<CashReconciliation> list = new List<CashReconciliation>(); CashReconciliation ins; //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); SqlCommand cmdI; //...SQL Commands... cmdI = new SqlCommand("SELECT cr.*,mt.MovementType,e.Name,rt.Reconciliation FROM t_CashReconciliation cr inner join l_MovementType mt on cr.MovementTypeID = mt.MovementTypeID inner join Employee e on cr.EmployeeID = e.EmployeeID inner join l_ReconciliationType rt on cr.ReconciliationTypeID = rt.ReconciliationTypeID where cr.Removed=0", con); cmdI.Connection.Open(); SqlDataReader drI = cmdI.ExecuteReader(); //...Retrieve Data... if (drI.HasRows) { while (drI.Read()) { ins = new CashReconciliation(); ins.CashReconciliationID = Convert.ToInt32(drI["CashReconcilationID"]); ins.ActualDate = Convert.ToDateTime(drI["ActualDate"]); ins.CreatedDate = Convert.ToDateTime(drI["CreatedDate"]); ins.ReconciliationTypeID = Convert.ToInt32(drI["ReconciliationTypeID"]); ins.MovementTypeID = Convert.ToInt32(drI["MovementTypeID"]); ins.EmployeeID = Convert.ToInt32(drI["EmployeeID"]); ins.recontype = drI["Reconciliation"].ToString(); ins.employee = drI["Name"].ToString(); ins.movementtype = drI["MovementType"].ToString(); ins.UserID = Convert.ToString(drI["UserID"]); ins.CompanyID = Convert.ToInt32(drI["CompanyID"]); ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]); ins.ModifiedBy = Convert.ToString(drI["ModifiedBy"]); ins.Removed = Convert.ToBoolean(drI["Removed"]); ins.Amount = Convert.ToDecimal(drI["Amount"]); list.Add(ins); } } //...Close Connections... drI.Close(); con.Close(); //...Return... return list; }
public List <CashReconciliation> GetCashReconcilationsPerEmployeeType(int UserTypeID) { //...Create New Instance of Object... List <CashReconciliation> list = new List <CashReconciliation>(); CashReconciliation ins; //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); SqlCommand cmdI; //...SQL Commands... cmdI = new SqlCommand("SELECT * FROM t_CashReconcilation WHERE UserTypeID = " + UserTypeID, con); cmdI.Connection.Open(); SqlDataReader drI = cmdI.ExecuteReader(); //...Retrieve Data... if (drI.HasRows) { while (drI.Read()) { ins = new CashReconciliation(); ins.CashReconciliationID = Convert.ToInt32(drI["CashReconciliationID"]); ins.ActualDate = Convert.ToDateTime(drI["ActualDate"]); ins.CreatedDate = Convert.ToDateTime(drI["CreatedDate"]); ins.ReconciliationTypeID = Convert.ToInt32(drI["ReconciliationTypeID"]); ins.MovementTypeID = Convert.ToInt32(drI["MovementTypeID"]); ins.EmployeeID = Convert.ToInt32(drI["EmployeeID"]); ins.UserID = Convert.ToString(drI["UserID"]); ins.CompanyID = Convert.ToInt32(drI["CompanyID"]); ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]); ins.ModifiedBy = Convert.ToString(drI["ModifiedBy"]); ins.Removed = Convert.ToBoolean(drI["Removed"]); ins.Amount = Convert.ToDecimal(drI["Amount"]); list.Add(ins); } } //...Close Connections... drI.Close(); con.Close(); //...Return... return(list); }
public CashReconciliation GetCashReconcilation(int CashReconcilationID) { //...Create New Instance of Object... CashReconciliation ins = new CashReconciliation(); //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); SqlCommand cmdI; //...SQL Commands... cmdI = new SqlCommand("SELECT * FROM t_CashReconcilation WHERE CashReconcilationId =" + CashReconcilationID, con); cmdI.Connection.Open(); SqlDataReader drI = cmdI.ExecuteReader(); //...Retrieve Data... if (drI.HasRows) { while (drI.Read()) { ins.CashReconciliationID = Convert.ToInt32(drI["CashReconcilationID"]); ins.ActualDate = Convert.ToDateTime(drI["ActualDate"]); ins.CreatedDate = Convert.ToDateTime(drI["CreatedDate"]); ins.EmployeeID = Convert.ToInt32(drI["EmployeeID"]); ins.ReconciliationTypeID = Convert.ToInt32(drI["ReconciliationTypeID"]); ins.MovementTypeID = Convert.ToInt32(drI["MovementTypeID"]); ins.UserID = Convert.ToString(drI["UserID"]); ins.CompanyID = Convert.ToInt32(drI["CompanyID"]); ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]); ins.ModifiedBy = Convert.ToString(drI["ModifiedBy"]); ins.Removed = Convert.ToBoolean(drI["Removed"]); } } //...Close Connections... drI.Close(); con.Close(); //...Return... return ins; }
public CashReconciliation Update(CashReconciliation ins) { //...Get User and Date Data... string ModifiedDate = string.Format("{0:yyyy-MM-dd hh:mm:ss}", DateTime.Now); string EmployeeId = Convert.ToString(HttpContext.Current.Session["Username"]); //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); con.Open(); SqlCommand cmdI = con.CreateCommand(); cmdI.Connection = con; //...Update Record... cmdI.Parameters.Clear(); cmdI.CommandText = StoredProcedures.CashReconcilationUpdate; cmdI.CommandType = System.Data.CommandType.StoredProcedure; cmdI.Parameters.AddWithValue("@CashReconciliationID", ins.CashReconciliationID); cmdI.Parameters.AddWithValue("@ActualDate", ins.ActualDate); cmdI.Parameters.AddWithValue("@CreatedDate", DateTime.Now); cmdI.Parameters.AddWithValue("@ReconciliationTypeID", ins.ReconciliationTypeID); cmdI.Parameters.AddWithValue("@MovementTypeID", ins.MovementTypeID); cmdI.Parameters.AddWithValue("@EmployeeID", ins.EmployeeID); cmdI.Parameters.AddWithValue("@UserID", EmployeeId); cmdI.Parameters.AddWithValue("@CompanyID", ins.CompanyID); cmdI.Parameters.AddWithValue("@ModifiedDate", ModifiedDate); cmdI.Parameters.AddWithValue("@ModifiedBy", EmployeeId); cmdI.Parameters.AddWithValue("@Removed", ins.Removed); cmdI.Parameters.AddWithValue("@Amount", ins.Amount); // cmdI.Parameters.AddWithValue("@Cash", ins.Cash); // cmdI.Parameters.AddWithValue("@EFT", ins.EFT); cmdI.ExecuteNonQuery(); cmdI.Connection.Close(); return(ins); }
public CashReconciliation Update(CashReconciliation ins) { //...Get User and Date Data... string ModifiedDate = string.Format("{0:yyyy-MM-dd hh:mm:ss}", DateTime.Now); string EmployeeId = Convert.ToString(HttpContext.Current.Session["Username"]); //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); con.Open(); SqlCommand cmdI = con.CreateCommand(); cmdI.Connection = con; //...Update Record... cmdI.Parameters.Clear(); cmdI.CommandText = StoredProcedures.CashReconcilationUpdate; cmdI.CommandType = System.Data.CommandType.StoredProcedure; cmdI.Parameters.AddWithValue("@CashReconciliationID", ins.CashReconciliationID); cmdI.Parameters.AddWithValue("@ActualDate", ins.ActualDate); cmdI.Parameters.AddWithValue("@CreatedDate", DateTime.Now); cmdI.Parameters.AddWithValue("@ReconciliationTypeID", ins.ReconciliationTypeID); cmdI.Parameters.AddWithValue("@MovementTypeID", ins.MovementTypeID); cmdI.Parameters.AddWithValue("@EmployeeID", ins.EmployeeID); cmdI.Parameters.AddWithValue("@UserID", EmployeeId); cmdI.Parameters.AddWithValue("@CompanyID", ins.CompanyID); cmdI.Parameters.AddWithValue("@ModifiedDate", ModifiedDate); cmdI.Parameters.AddWithValue("@ModifiedBy", EmployeeId); cmdI.Parameters.AddWithValue("@Removed", ins.Removed); cmdI.Parameters.AddWithValue("@Amount", ins.Amount); // cmdI.Parameters.AddWithValue("@Cash", ins.Cash); // cmdI.Parameters.AddWithValue("@EFT", ins.EFT); cmdI.ExecuteNonQuery(); cmdI.Connection.Close(); return ins; }
public CashReconciliation InsertMultiple(CashReconciliation ins) { //...Get User and Date Data... string ModifiedDate = string.Format("{0:yyyy-MM-dd hh:mm:ss}", DateTime.Now); string EmployeeId = Convert.ToString(HttpContext.Current.Session["Username"]); string strTrx = "CashReconcilationIns_" + EmployeeId; //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); con.Open(); //...Command Interface... SqlCommand cmdI = con.CreateCommand(); SqlTransaction trx; trx = con.BeginTransaction(strTrx); cmdI.Connection = con; cmdI.Transaction = trx; try { //...Insert Record... cmdI.CommandText = StoredProcedures.CashReconcilationInsertMultiple; cmdI.CommandType = System.Data.CommandType.StoredProcedure; //cmdI.Parameters.AddWithValue("@CashReconcilationID", ins.CashReconcilationID); cmdI.Parameters.AddWithValue("@ActualDate", ins.ActualDate); cmdI.Parameters.AddWithValue("@CreatedDate", DateTime.Now); cmdI.Parameters.AddWithValue("@MovementTypeID", ins.MovementTypeID); cmdI.Parameters.AddWithValue("@EmployeeID", ins.EmployeeID); cmdI.Parameters.AddWithValue("@UserID", EmployeeId); cmdI.Parameters.AddWithValue("@CompanyID", ins.CompanyID); cmdI.Parameters.AddWithValue("@ModifiedDate", ModifiedDate); cmdI.Parameters.AddWithValue("@ModifiedBy", EmployeeId); cmdI.Parameters.AddWithValue("@Removed", ins.Removed); cmdI.Parameters.AddWithValue("@Amount", ins.Amount); cmdI.Parameters.AddWithValue("@Cash", ins.Cash); cmdI.Parameters.AddWithValue("@EFT", ins.EFT); //...Return new ID ins.CashReconciliationID = (int)cmdI.ExecuteScalar(); trx.Commit(); cmdI.Connection.Close(); } catch (SqlException ex) { if (trx != null) trx.Rollback(); } finally { //Check for close and respond accordingly if (con.State != ConnectionState.Closed) { con.Close(); } //Clean up con.Dispose(); cmdI.Dispose(); trx.Dispose(); } return ins; }
public List<CashReconciliation> GetCashReconcilationsPerEmployee(int EmployeeID, DateTime date) { //...Create New Instance of Object... List<CashReconciliation> list = new List<CashReconciliation>(); CashReconciliation ins; //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); SqlCommand cmdI; //...SQL Commands... cmdI = new SqlCommand("SELECT * FROM t_CashReconciliation WHERE EmployeeID = " + EmployeeID + " AND ActualDate = '" + date.ToShortDateString() + "' AND Removed=0", con); cmdI.Connection.Open(); SqlDataReader drI = cmdI.ExecuteReader(); //...Retrieve Data... if (drI.HasRows) { while (drI.Read()) { ins = new CashReconciliation(); ins.CashReconciliationID = Convert.ToInt32(drI["CashReconcilationID"]); ins.ActualDate = Convert.ToDateTime(drI["ActualDate"]); ins.CreatedDate = Convert.ToDateTime(drI["CreatedDate"]); ins.ReconciliationTypeID = Convert.ToInt32(drI["ReconciliationTypeID"]); ins.MovementTypeID = Convert.ToInt32(drI["MovementTypeID"]); ins.EmployeeID = Convert.ToInt32(drI["EmployeeID"]); ins.UserID = Convert.ToString(drI["UserID"]); ins.CompanyID = Convert.ToInt32(drI["CompanyID"]); ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]); ins.ModifiedBy = Convert.ToString(drI["ModifiedBy"]); ins.Removed = Convert.ToBoolean(drI["Removed"]); ins.Amount = Convert.ToDecimal(drI["Amount"]); list.Add(ins); } } //...Close Connections... drI.Close(); con.Close(); //...Return... return list; }
public JsonResult _UpdateRecon(CashReconciliation ins) { CashReconciliation ins2 = CRRep.Update(ins); //...Repopulate Grid... return Json(new GridModel(CRRep.GetAllCashReconcilation())); }
public CashReconciliation InsertMultiple(CashReconciliation ins) { //...Get User and Date Data... string ModifiedDate = string.Format("{0:yyyy-MM-dd hh:mm:ss}", DateTime.Now); string EmployeeId = Convert.ToString(HttpContext.Current.Session["Username"]); string strTrx = "CashReconcilationIns_" + EmployeeId; //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); con.Open(); //...Command Interface... SqlCommand cmdI = con.CreateCommand(); SqlTransaction trx; trx = con.BeginTransaction(strTrx); cmdI.Connection = con; cmdI.Transaction = trx; try { //...Insert Record... cmdI.CommandText = StoredProcedures.CashReconcilationInsertMultiple; cmdI.CommandType = System.Data.CommandType.StoredProcedure; //cmdI.Parameters.AddWithValue("@CashReconcilationID", ins.CashReconcilationID); cmdI.Parameters.AddWithValue("@ActualDate", ins.ActualDate); cmdI.Parameters.AddWithValue("@CreatedDate", DateTime.Now); cmdI.Parameters.AddWithValue("@MovementTypeID", ins.MovementTypeID); cmdI.Parameters.AddWithValue("@EmployeeID", ins.EmployeeID); cmdI.Parameters.AddWithValue("@UserID", EmployeeId); cmdI.Parameters.AddWithValue("@CompanyID", ins.CompanyID); cmdI.Parameters.AddWithValue("@ModifiedDate", ModifiedDate); cmdI.Parameters.AddWithValue("@ModifiedBy", EmployeeId); cmdI.Parameters.AddWithValue("@Removed", ins.Removed); cmdI.Parameters.AddWithValue("@Amount", ins.Amount); cmdI.Parameters.AddWithValue("@Cash", ins.Cash); cmdI.Parameters.AddWithValue("@EFT", ins.EFT); //...Return new ID ins.CashReconciliationID = (int)cmdI.ExecuteScalar(); trx.Commit(); cmdI.Connection.Close(); } catch (SqlException ex) { if (trx != null) { trx.Rollback(); } } finally { //Check for close and respond accordingly if (con.State != ConnectionState.Closed) { con.Close(); } //Clean up con.Dispose(); cmdI.Dispose(); trx.Dispose(); } return(ins); }