public void SaveMonthlyOpexInfo(OpexDailyBO opexDailyBo)
{
SqlConnection conn = new SqlConnection(DbConnectionBasic.ConnectionString);
string queryString = "";
queryString = "INSERT INTO SBP_OPEX(Expense_Purpose_ID,Purpose,Branch_ID,Amount,Voucher_No,Voucher_Image,Expense_Date,Expense_Month,Remarks,Expense_Type,Entry_By,Entry_Date)" +
"VALUES(@Expense_Purpose_ID,@Purpose,@Branch_ID,@Amount,@Voucher_No,@Voucher_Image,CAST(FLOOR(CAST(@Expense_Date AS FLOAT)) AS DATETIME),@Expense_Month,@Remarks,@Expense_Type,@Entry_By,CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.AddParameter("@Expense_Purpose_ID", SqlDbType.Int, (object)opexDailyBo.PurposeId);
_dbConnection.AddParameter("@Purpose", SqlDbType.VarChar, (object)opexDailyBo.Purpose);
_dbConnection.AddParameter("@Branch_ID", SqlDbType.Int, (object)opexDailyBo.BranchId);
_dbConnection.AddParameter("@Amount", SqlDbType.Money, (object)opexDailyBo.Amount);
_dbConnection.AddParameter("@Voucher_No", SqlDbType.VarChar, (object)opexDailyBo.VoucherNo);
_dbConnection.AddParameter("@Voucher_Image", SqlDbType.Image, (object)opexDailyBo.VoucherImage);
_dbConnection.AddParameter("@Expense_Date", SqlDbType.DateTime, (object)opexDailyBo.ExpenseDate);
_dbConnection.AddParameter("@Remarks", SqlDbType.VarChar, (object)opexDailyBo.Remarks);
_dbConnection.AddParameter("@Entry_By", SqlDbType.VarChar, (object)GlobalVariableBO._userName);
_dbConnection.AddParameter("@Expense_Type", SqlDbType.VarChar, (object)opexDailyBo.ExpenseType);
_dbConnection.AddParameter("@Expense_Month", SqlDbType.DateTime, (object)opexDailyBo.MonthOfExpense);
_dbConnection.ExecuteNonQuery(queryString);
}
catch (Exception)
{
throw;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public void SaveCommonOpexInfo(OpexDailyBO opexDailyBo)
{
string queryString = "SP_CommonExpense";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.ActiveStoredProcedure();
_dbConnection.AddParameter("@Expense_Purpose_ID", SqlDbType.Int, (object)opexDailyBo.PurposeId);
_dbConnection.AddParameter("@Purpose", SqlDbType.VarChar, (object)opexDailyBo.Purpose);
_dbConnection.AddParameter("@Amount", SqlDbType.Money, (object)opexDailyBo.Amount);
_dbConnection.AddParameter("@Expense_Date", SqlDbType.DateTime, (object)opexDailyBo.ExpenseDate.ToShortDateString());
_dbConnection.AddParameter("@Expense_Month", SqlDbType.DateTime, (object)opexDailyBo.MonthOfExpense.ToShortDateString());
_dbConnection.AddParameter("@Remarks", SqlDbType.VarChar, (object)opexDailyBo.Remarks);
_dbConnection.AddParameter("@Entry_By", SqlDbType.VarChar, (object)GlobalVariableBO._userName);
_dbConnection.AddParameter("@VoucharNo", SqlDbType.VarChar, (object)opexDailyBo.VoucherNo);
_dbConnection.AddParameter("@VoucherImage", SqlDbType.Image, (object)opexDailyBo.VoucherImage);
_dbConnection.ExecuteNonQuery(queryString);
}
catch (Exception)
{
throw;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public void SaveDailyOpexInfo(OpexDailyBO opexDailyBo)
{
CommonBAL commonBal = new CommonBAL();
int expenseId = 0;
string queryString_ImgExt = @"INSERT INTO [SBP_OPEX_ImgExt]
(
[Expense_ID]
, [Voucher_Image]
, [Update_Date]
)
VALUES
(
@Expense_ID
, @Voucher_Image
, @Update_Date
)";
string queryStringWithoutImage = "";
queryStringWithoutImage = @"INSERT INTO SBP_OPEX
(
Expense_Purpose_ID
,Purpose
,Branch_ID
,Amount
,Voucher_No
-- ,Voucher_Image
,Expense_Date
,Remarks
,Entry_By
,Entry_Date
,Expense_Type
,Update_Date
)"
+
@"VALUES(
@Expense_Purpose_ID
,@Purpose
,@Branch_ID
,@Amount
,@Voucher_No
-- ,@Voucher_Image
,CAST(FLOOR(CAST(@Expense_Date AS FLOAT)) AS DATETIME)
,@Remarks
,@Entry_By
,CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
,@Expense_Type
,@Update_Date)";
string queryStringWithImage = "";
queryStringWithImage = @"INSERT INTO SBP_OPEX
(
Expense_Purpose_ID
,Purpose
,Branch_ID
,Amount
,Voucher_No
,Voucher_Image
,Expense_Date
,Remarks
,Entry_By
,Entry_Date
,Expense_Type
,Update_Date
)"
+
@"VALUES
(
@Expense_Purpose_ID
,@Purpose
,@Branch_ID
,@Amount
,@Voucher_No
,@Voucher_Image
,CAST(FLOOR(CAST(@Expense_Date AS FLOAT)) AS DATETIME)
,@Remarks
,@Entry_By
,CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
,@Expense_Type
,@Update_Date)";
try
{
_dbConnection.ConnectDatabase_ImageExt();
_dbConnection.ConnectDatabase();
_dbConnection.StartTransaction_ImageExt();
_dbConnection.StartTransaction();
_dbConnection.AddParameter("@Expense_Purpose_ID", SqlDbType.Int, (object)opexDailyBo.PurposeId);
_dbConnection.AddParameter("@Purpose", SqlDbType.VarChar, (object)opexDailyBo.Purpose);
_dbConnection.AddParameter("@Branch_ID", SqlDbType.Int, (object)opexDailyBo.BranchId);
_dbConnection.AddParameter("@Amount", SqlDbType.Money, (object)opexDailyBo.Amount);
_dbConnection.AddParameter("@Voucher_No", SqlDbType.VarChar, (object)opexDailyBo.VoucherNo);
// _dbConnection.AddParameter("@Voucher_Image", SqlDbType.Image, null);
_dbConnection.AddParameter("@Expense_Date", SqlDbType.DateTime, (object)opexDailyBo.ExpenseDate);
_dbConnection.AddParameter("@Remarks", SqlDbType.VarChar, (object)opexDailyBo.Remarks);
_dbConnection.AddParameter("@Entry_By", SqlDbType.VarChar, (object)GlobalVariableBO._userName);
_dbConnection.AddParameter("@Expense_Type", SqlDbType.VarChar, opexDailyBo.ExpenseType == null ? string.Empty : (object)opexDailyBo.ExpenseType);
_dbConnection.AddParameter("@Update_Date", SqlDbType.DateTime, (object)commonBal.GetCurrentServerDate().ToShortDateString());
_dbConnection.ExecuteNonQuery(queryStringWithoutImage);
_dbConnection.ClearParameters();
_dbConnection.Commit();
expenseId = commonBal.GetMaxID("SBP_OPEX", "Expense_ID");
_dbConnection.AddParameter_ImageExt("@Expense_ID", SqlDbType.Int, (object)expenseId);
_dbConnection.AddParameter_ImageExt("@Voucher_Image", SqlDbType.Image, (object)opexDailyBo.VoucherImage);
_dbConnection.AddParameter_ImageExt("@Update_Date", SqlDbType.DateTime, (object)commonBal.GetCurrentServerDate().ToShortDateString());
_dbConnection.ExecuteNonQuery_ImageExt(queryString_ImgExt);
_dbConnection.ClearParameters_ImageExt();
_dbConnection.Commit_ImageExt();
}
catch (Exception ex)
{
try
{
_dbConnection.Rollback();
_dbConnection.Rollback_ImageExt();
_dbConnection.CloseDatabase();
_dbConnection.ConnectDatabase();
_dbConnection.StartTransaction();
_dbConnection.AddParameter("@Expense_Purpose_ID", SqlDbType.Int, (object)opexDailyBo.PurposeId);
_dbConnection.AddParameter("@Purpose", SqlDbType.VarChar, (object)opexDailyBo.Purpose);
_dbConnection.AddParameter("@Branch_ID", SqlDbType.Int, (object)opexDailyBo.BranchId);
_dbConnection.AddParameter("@Amount", SqlDbType.Money, (object)opexDailyBo.Amount);
_dbConnection.AddParameter("@Voucher_No", SqlDbType.VarChar, (object)opexDailyBo.VoucherNo);
_dbConnection.AddParameter("@Voucher_Image", SqlDbType.Image, (object)opexDailyBo.VoucherImage);
_dbConnection.AddParameter("@Expense_Date", SqlDbType.DateTime, (object)opexDailyBo.ExpenseDate);
_dbConnection.AddParameter("@Remarks", SqlDbType.VarChar, (object)opexDailyBo.Remarks);
_dbConnection.AddParameter("@Entry_By", SqlDbType.VarChar, (object)GlobalVariableBO._userName);
_dbConnection.AddParameter("@Expense_Type", SqlDbType.VarChar, (object)opexDailyBo.ExpenseType);
_dbConnection.AddParameter("@Update_Date", SqlDbType.DateTime, (object)commonBal.GetCurrentServerDate().ToShortDateString());
_dbConnection.ExecuteNonQuery(queryStringWithImage);
_dbConnection.Commit();
}
catch (Exception ey)
{
_dbConnection.Rollback();
throw new Exception(ey.Message);
}
finally
{
_dbConnection.CloseDatabase();
}
}
finally
{
_dbConnection.CloseDatabase_ImageExt();
_dbConnection.CloseDatabase();
}
}