/// <summary>
/// To update details to database
/// </summary>
/// <param name="objExpense"></param>
/// <returns></returns>
public string UpdateBank(ExpenseBO objExpense)
{
cnn = new OracleConnection(con);
string returnResult = string.Empty;
proc = "USP_TRN_UPD_PROJ_EXPENSE";
cmd = new OracleCommand(proc, cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection.Open();
cmd.Parameters.Add("EXPENSEID_", objExpense.PROJECTEXPENSEID);
cmd.Parameters.Add("PROJECTID_", objExpense.PROJECTID);
cmd.Parameters.Add("EXPENSETYPE_", objExpense.EXPENSETYPE);
cmd.Parameters.Add("ACCOUNTCODE_", objExpense.ACCOUNTCODE);
cmd.Parameters.Add("EXPENSEAMOUNT_", objExpense.EXPENSEAMOUNT);
cmd.Parameters.Add("DATEOFEXPENSE_", objExpense.DATEOFEXPENSE.ToString(UtilBO.DateFormatDBFull));
cmd.Parameters.Add("UPDATEDBY_", objExpense.UPDATEDBY);
cmd.Parameters.Add("errorMessage_", OracleDbType.Varchar2, 500).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
if (cmd.Parameters["errorMessage_"].Value != null)
{
returnResult = cmd.Parameters["errorMessage_"].Value.ToString();
}
else
{
returnResult = string.Empty;
}
cmd.Connection.Close();
return(returnResult);
}
/// <summary>
/// To fetch details by ID
/// </summary>
/// <param name="EXPENSEID"></param>
/// <returns></returns>
public ExpenseBO GetExpenseByID(int EXPENSEID)
{
proc = "USP_TRN_GET_PROJ_EXPENSEBYID";
cnn = new OracleConnection(con);
ExpenseBO ExpenseBOobj = null;
cmd = new OracleCommand(proc, cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("EXPENSEID_", EXPENSEID);
cmd.Parameters.Add("Sp_recordset", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
try
{
cmd.Connection.Open();
OracleDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
ExpenseBOobj = new ExpenseBO();
if (!dr.IsDBNull(dr.GetOrdinal("PROJECTEXPENSEID")))
{
ExpenseBOobj.PROJECTEXPENSEID = Convert.ToInt32(dr.GetValue(dr.GetOrdinal("PROJECTEXPENSEID")));
}
if (!dr.IsDBNull(dr.GetOrdinal("PROJECTID")))
{
ExpenseBOobj.PROJECTID = Convert.ToInt32(dr.GetValue(dr.GetOrdinal("PROJECTID")));
}
if (!dr.IsDBNull(dr.GetOrdinal("EXPENSETYPE")))
{
ExpenseBOobj.EXPENSETYPE = dr.GetString(dr.GetOrdinal("EXPENSETYPE"));
}
if (!dr.IsDBNull(dr.GetOrdinal("ACCOUNTCODE")))
{
ExpenseBOobj.ACCOUNTCODE = dr.GetString(dr.GetOrdinal("ACCOUNTCODE"));
}
if (!dr.IsDBNull(dr.GetOrdinal("EXPENSEAMOUNT")))
{
ExpenseBOobj.EXPENSEAMOUNT = Convert.ToDecimal(dr.GetValue(dr.GetOrdinal("EXPENSEAMOUNT")));
}
if (!dr.IsDBNull(dr.GetOrdinal("DATEOFEXPENSE")))
{
ExpenseBOobj.DATEOFEXPENSE = dr.GetDateTime(dr.GetOrdinal("DATEOFEXPENSE"));
}
ExpenseBOobj.ISDELETED = dr.GetString(dr.GetOrdinal("ISDELETED"));
}
dr.Close();
}
catch (Exception ex)
{
throw ex;
}
return(ExpenseBOobj);
}
internal string GetTodaysExpense(ExpenseBO expenseBO)
{
DataAccessSqlHelper sqlHelper = new DataAccessSqlHelper(expenseBO.ConString);
SqlCommand command = sqlHelper.CreateCommand(CommandType.Text);
command.CommandText = "SELECT ISNULL(SUM(AMOUNT),0) FROM expenseentry where CONVERT(varchar(100),ExpenseDate,103) = CONVERT(varchar(100),getdate(),103) and userId = " + expenseBO.UserId;
return(Convert.ToString(sqlHelper.ExecuteScalar(command)));
}
public void InsertTransactionData(ExpenseBO expenseBO)
{
DataAccessSqlHelper sqlHelper = new DataAccessSqlHelper(expenseBO.ConString);
SqlCommand command = sqlHelper.CreateCommand(CommandType.StoredProcedure);
command.CommandText = "InsertExpenseTransaction";
sqlHelper.AddParameter(command, "@UserId", expenseBO.UserId, ParameterDirection.Input);
sqlHelper.ExecuteNonQuery(command);
}
internal int GetExpense(ExpenseBO expenseBO)
{
DataAccessSqlHelper sqlHelper = new DataAccessSqlHelper(expenseBO.ConString);
SqlCommand command = sqlHelper.CreateCommand(CommandType.StoredProcedure);
command.CommandText = "uspWSGetPreviousDayOrdersCount";
sqlHelper.AddParameter(command, "@UserId", expenseBO.UserId, ParameterDirection.Input);
//sqlHelper.AddParameter(command, "@OrderDate", expenseBO.OrderDate, ParameterDirection.Input);
return(Convert.ToInt32(sqlHelper.ExecuteScalar(command)));
}
/// <summary>
/// To fetch details from database
/// </summary>
/// <param name="ProjectID"></param>
/// <returns></returns>
public object GetAllExpenseData(int ProjectID)
{
OracleConnection cnn = new OracleConnection(AppConfiguration.ConnectionString);
OracleCommand cmd;
string proc = "USP_TRN_GET_PROJ_EXPENSE";
cmd = new OracleCommand(proc, cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("PROJECTID_", ProjectID);
cmd.Parameters.Add("Sp_recordset", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
cmd.Connection.Open();
OracleDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
ExpenseBO ExpenseBOobj = null;
ExpenseList ExpenseListobj = new ExpenseList();
ExpenseBOobj = new ExpenseBO();
while (dr.Read())
{
ExpenseBOobj = new ExpenseBO();
if (!dr.IsDBNull(dr.GetOrdinal("PROJECTEXPENSEID")))
{
ExpenseBOobj.PROJECTEXPENSEID = Convert.ToInt32(dr.GetValue(dr.GetOrdinal("PROJECTEXPENSEID")));
}
if (!dr.IsDBNull(dr.GetOrdinal("PROJECTID")))
{
ExpenseBOobj.PROJECTID = Convert.ToInt32(dr.GetValue(dr.GetOrdinal("PROJECTID")));
}
if (!dr.IsDBNull(dr.GetOrdinal("EXPENSETYPE")))
{
ExpenseBOobj.EXPENSETYPE = dr.GetString(dr.GetOrdinal("EXPENSETYPE"));
}
if (!dr.IsDBNull(dr.GetOrdinal("ACCOUNTCODE")))
{
ExpenseBOobj.ACCOUNTCODE = dr.GetString(dr.GetOrdinal("ACCOUNTCODE"));
}
if (!dr.IsDBNull(dr.GetOrdinal("EXPENSEAMOUNT")))
{
ExpenseBOobj.EXPENSEAMOUNT = Convert.ToDecimal(dr.GetValue(dr.GetOrdinal("EXPENSEAMOUNT")));
}
if (!dr.IsDBNull(dr.GetOrdinal("DATEOFEXPENSE")))
{
ExpenseBOobj.DATEOFEXPENSE = dr.GetDateTime(dr.GetOrdinal("DATEOFEXPENSE"));
}
ExpenseBOobj.ISDELETED = dr.GetString(dr.GetOrdinal("ISDELETED"));
ExpenseListobj.Add(ExpenseBOobj);
}
dr.Close();
return(ExpenseListobj);
}
internal int UpdateExpense(ExpenseBO expenseBO)
{
DataAccessSqlHelper sqlHelper = new DataAccessSqlHelper(expenseBO.ConString);
SqlCommand command = sqlHelper.CreateCommand(CommandType.StoredProcedure);
command.CommandText = "uspExpense";
sqlHelper.AddParameter(command, "@Mode", expenseBO.Mode, ParameterDirection.Input);
sqlHelper.AddParameter(command, "@QuantityList", expenseBO.Quantity, ParameterDirection.Input);
sqlHelper.AddParameter(command, "@UserId", expenseBO.UserId, ParameterDirection.Input);
sqlHelper.AddParameter(command, "@Parameters", expenseBO.Parameters, ParameterDirection.Input);
sqlHelper.AddParameter(command, "@latitude", expenseBO.Latitude, ParameterDirection.Input);
sqlHelper.AddParameter(command, "@longitude", expenseBO.Longitude, ParameterDirection.Input);
sqlHelper.AddParameter(command, "@processName", expenseBO.ProcessName, ParameterDirection.Input);
sqlHelper.AddParameter(command, "@mobileTransactionDate", expenseBO.MobileTransactionDate, ParameterDirection.Input);
sqlHelper.AddParameter(command, "@mobileReferenceNo", expenseBO.mobilereferenceNo, ParameterDirection.Input);
sqlHelper.AddParameter(command, "@Remarks", expenseBO.Remarks, ParameterDirection.Input);
sqlHelper.AddParameter(command, "@GpsSource", expenseBO.GpsSource, ParameterDirection.Input);
if (!string.IsNullOrEmpty(expenseBO.Field1))
{
sqlHelper.AddParameter(command, "@Field1List", expenseBO.Field1, ParameterDirection.Input);
}
if (!string.IsNullOrEmpty(expenseBO.Field2))
{
sqlHelper.AddParameter(command, "@Field2List", expenseBO.Field2, ParameterDirection.Input);
}
if (!string.IsNullOrEmpty(expenseBO.Field3))
{
sqlHelper.AddParameter(command, "@Field3List", expenseBO.Field3, ParameterDirection.Input);
}
if (!string.IsNullOrEmpty(expenseBO.Field4))
{
sqlHelper.AddParameter(command, "@Field4List", expenseBO.Field4, ParameterDirection.Input);
}
if (!string.IsNullOrEmpty(expenseBO.Field5))
{
sqlHelper.AddParameter(command, "@Field5List", expenseBO.Field5, ParameterDirection.Input);
}
if (!string.IsNullOrEmpty(expenseBO.ExpenseDate))
{
sqlHelper.AddParameter(command, "@ExpenseDateList", expenseBO.ExpenseDate, ParameterDirection.Input);
}
if (!string.IsNullOrEmpty(expenseBO.UniqueKey))
{
sqlHelper.AddParameter(command, "@UniqueKeyList", expenseBO.UniqueKey, ParameterDirection.Input);
}
if (expenseBO.ServerSyncDate != "" && expenseBO.MobileSyncDate != "")
{
sqlHelper.AddParameter(command, "@MobileSyncDate", expenseBO.MobileSyncDate, ParameterDirection.Input);
sqlHelper.AddParameter(command, "@ServerSyncDate", expenseBO.ServerSyncDate, ParameterDirection.Input);
}
return(Convert.ToInt32(sqlHelper.ExecuteNonQuery(command)));
}
/// <summary>
/// Save and Update
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnSaveExpence_Click(object sender, EventArgs e)
{
ExpenseBO objExpense = new ExpenseBO();
objExpense.PROJECTEXPENSEID = Convert.ToInt32(ViewState["PROJECTEXPENSEID"]);
ExpenseBLL objExpenseBLL = new ExpenseBLL();
string AlertMessage = string.Empty;
string message = string.Empty;
if (objExpense.PROJECTEXPENSEID == 0)
{
objExpense.PROJECTID = Convert.ToInt32(Session["PROJECT_ID"]);
objExpense.EXPENSETYPE = txtExpensetype.Text.Trim();
objExpense.ACCOUNTCODE = txtAccountcode.Text.Trim();
objExpense.EXPENSEAMOUNT = Convert.ToDecimal(txtExpenseAmt.Text.Trim());
objExpense.DATEOFEXPENSE = Convert.ToDateTime(dpDateofexpense.Text.ToString());
objExpense.UPDATEDBY = Convert.ToInt32(Session["USER_ID"]);
message = objExpenseBLL.AddExpense(objExpense);
if (string.IsNullOrEmpty(message) || message == "" || message == "null")
{
message = "Data saved successfully.";
Clearfields();
}
}
else
{
objExpense.PROJECTID = Convert.ToInt32(Session["PROJECT_ID"]);
objExpense.EXPENSETYPE = txtExpensetype.Text.Trim();
objExpense.ACCOUNTCODE = txtAccountcode.Text.Trim();
objExpense.EXPENSEAMOUNT = Convert.ToDecimal(txtExpenseAmt.Text.Trim());
objExpense.DATEOFEXPENSE = Convert.ToDateTime(dpDateofexpense.Text.ToString());
objExpense.UPDATEDBY = Convert.ToInt32(Session["USER_ID"]);
message = objExpenseBLL.UpdateExpense(objExpense);
if (string.IsNullOrEmpty(message) || message == "" || message == "null")
{
message = "Data Updated successfully.";
Clearfields();
}
}
AlertMessage = "alert('" + message + "');";
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "Added", AlertMessage, true);
grdExpense.AllowPaging = true;
BindGrid();
}
/// <summary>
/// To edit and delete
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void grdExpense_RowCommand(object sender, GridViewCommandEventArgs e)
{
string message = string.Empty;
try
{
if (e.CommandName == "EditRow")
{
ViewState["PROJECTEXPENSEID"] = e.CommandArgument;
ExpenseBLL objExpenseBLL = new ExpenseBLL();
ExpenseBO objExpense = objExpenseBLL.GetExpenseByID(Convert.ToInt32(ViewState["PROJECTEXPENSEID"]));
txtExpenseAmt.Text = UtilBO.CurrencyFormat(objExpense.EXPENSEAMOUNT);//objExpense.EXPENSEAMOUNT.ToString();
txtExpensetype.Text = objExpense.EXPENSETYPE.ToString();
txtAccountcode.Text = objExpense.ACCOUNTCODE;
if (objExpense.DATEOFEXPENSE != null)
{
dpDateofexpense.Text = objExpense.DATEOFEXPENSE.ToString(UtilBO.DateFormat);
}
btnSaveExpence.Text = "Update";
btnClearExpence.Text = "Cancel";
}
else if (e.CommandName == "DeleteRow")
{
ExpenseBLL objExpenseBLL = new ExpenseBLL();
message = objExpenseBLL.DeleteExpense(Convert.ToInt32(e.CommandArgument));
if (string.IsNullOrEmpty(message) || message == "" || message == "null")
{
message = "Data deleted successfully";
}
Clearfields();
grdExpense.AllowPaging = true;
BindGrid();
}
if (message != "")
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "Added", "alert('" + message + "');", true);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// to insert details to datbase
/// </summary>
/// <param name="dtExpen"></param>
/// <param name="ProjectID"></param>
/// <param name="uID"></param>
/// <returns></returns>
public DataTable savedata(DataTable dtExpen, int ProjectID, string uID)
{
ExpenseBO objExpenseBO = null;
OracleConnection myConnection = new OracleConnection(AppConfiguration.ConnectionString);
myConnection.Open();
OracleCommand myCommand;
OracleCommand cmd;
myConnection = new OracleConnection(AppConfiguration.ConnectionString);
//cmd = new OracleCommand("USP_TRN_DEL_PROJ_EXPENSE", myConnection);
//cmd.Connection = myConnection;
//cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.Add("PROJECTID_", ProjectID);
//myConnection.Open();
//cmd.ExecuteNonQuery();
//myConnection.Close();
myCommand = new OracleCommand("USP_TRN_INS_PROJ_EXPENSE", myConnection);
myCommand.Connection = myConnection;
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add("PROJECTID_", "");
myCommand.Parameters.Add("EXPENSETYPE_", "");
myCommand.Parameters.Add("ACCOUNTCODE_", "");
myCommand.Parameters.Add("EXPENSEAMOUNT_", "");
myCommand.Parameters.Add("DATEOFEXPENSE_", "");
myCommand.Parameters.Add("CREATEDBY_", "");
myConnection.Open();
foreach (DataRow dr in dtExpen.Rows)
{
objExpenseBO = new ExpenseBO();
objExpenseBO.PROJECTID = ProjectID;
objExpenseBO.EXPENSETYPE = Convert.ToString(dr["EXPENSETYPE"]);
objExpenseBO.ACCOUNTCODE = (dr["ACCOUNTCODE"].ToString());
if (dr["EXPENSEAMOUNT"].ToString().Trim() != "")
{
objExpenseBO.EXPENSEAMOUNT = Convert.ToDecimal((dr["EXPENSEAMOUNT"]));
}
else
{
objExpenseBO.EXPENSEAMOUNT = 0;
}
objExpenseBO.DATEOFEXPENSE = Convert.ToDateTime((dr["DATEOFEXPENSE"]));
objExpenseBO.CREATEDBY = Convert.ToInt32(uID);
myCommand.Parameters["PROJECTID_"].Value = objExpenseBO.PROJECTID;
myCommand.Parameters["EXPENSETYPE_"].Value = objExpenseBO.EXPENSETYPE;
myCommand.Parameters["ACCOUNTCODE_"].Value = objExpenseBO.ACCOUNTCODE;
myCommand.Parameters["EXPENSEAMOUNT_"].Value = objExpenseBO.EXPENSEAMOUNT;
myCommand.Parameters["DATEOFEXPENSE_"].Value = objExpenseBO.DATEOFEXPENSE.ToString(UtilBO.DateFormatDBFull);
myCommand.Parameters["CREATEDBY_"].Value = objExpenseBO.CREATEDBY;
myCommand.ExecuteNonQuery();
}
myConnection.Close();
return(dtExpen);
}
/// <summary>
/// To Update Expense
/// </summary>
/// <param name="objExpense"></param>
/// <returns></returns>
public string UpdateExpense(ExpenseBO objExpense)
{
ExpenseDAL ExpenseDALobj = new ExpenseDAL();
return(ExpenseDALobj.UpdateBank(objExpense));
}
//New Changes
/// <summary>
/// To Add Expense
/// </summary>
/// <param name="objExpense"></param>
/// <returns></returns>
public string AddExpense(ExpenseBO objExpense)
{
ExpenseDAL ExpenseDALobj = new ExpenseDAL();
return(ExpenseDALobj.AddExpense(objExpense));
}
