public void Update(ExpenseEntryBO expenseEntryBo, int expenseIdForUpdate)
{
string queryStringIns = "";
CommonBAL cmnBAL = new CommonBAL();
queryStringIns = @"UPDATE SBP_Expense_Lookup
SET [Category_ID] =" + expenseEntryBo.Category_ID
+ ",[Expense_Description] = '" + expenseEntryBo.Expense_Description + "'"
+ ",[Frequency_ID] = " + expenseEntryBo.Expense_Frequency
+ ",[Update_Date] = '" + cmnBAL.GetCurrentServerDate().ToShortDateString() + "'"
+ " WHERE Expense_ID=" + expenseIdForUpdate;
try
{
_dbConnection.ConnectDatabase();
_dbConnection.ExecuteNonQuery(queryStringIns);
}
catch (Exception exception)
{
throw exception;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public DateTime GetMaxPriceDate()
{
CommonBAL objBal = new CommonBAL();
DataTable dtMaxDate = null;
DateTime maxDate = objBal.GetCurrentServerDate();
string quryString = "";
quryString = "SELECT MAX(Trade_Date) AS 'Trade_Date' FROM SBP_Trade_Price";
try
{
_dbConnection.ConnectDatabase();
dtMaxDate = _dbConnection.ExecuteQuery(quryString);
}
catch (Exception exc)
{
throw exc;
}
finally
{
_dbConnection.CloseDatabase();
}
if (dtMaxDate.Rows.Count > 0)
{
maxDate = Convert.ToDateTime(dtMaxDate.Rows[0]["Trade_Date"]);
}
return(maxDate);
}
public void SaveCategory(string OCCType)
{
string queryString = "";
CommonBAL cmnBAL = new CommonBAL();
queryString = @"INSERT INTO SBP_Expense_Frequency
(
[Frequency_Name]
,[Entry_By]
,[Entry_Date]
)
VALUES
('"
+ OCCType
+ "','" + GlobalVariableBO._userName
+ "','" + cmnBAL.GetCurrentServerDate().ToShortDateString()
+ "')";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.ExecuteNonQuery(queryString);
}
catch (Exception exception)
{
throw exception;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public void SaveCategory(Expense_CategoryEntryBO categoryEntryBo)
{
string queryString = "";
CommonBAL cmnBAL = new CommonBAL();
queryString =
@"INSERT INTO SBP_Expense_Category_Lookup
([Category_Name]
,[Category_Type_ID]
,[Sub_Category]
,[Update_Date]
)
VALUES
("
+ "'" + categoryEntryBo.Category_Name
+ "'," + categoryEntryBo.Category_Type_ID
+ ",'" + categoryEntryBo.Sub_Category
+ "','" + cmnBAL.GetCurrentServerDate().ToShortDateString()
+ "')";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.ExecuteNonQuery(queryString);
}
catch (Exception exception)
{
throw exception;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public void Update(Expense_CategoryEntryBO categoryEntryBo, int CategoryIdForUpdate)
{
string queryStringIns = "";
CommonBAL cmnBAL = new CommonBAL();
queryStringIns = @"UPDATE SBP_Expense_Category_Lookup
SET [Category_Name] ='" + categoryEntryBo.Category_Name + "'"
+ ",[Category_Type_ID] = " + categoryEntryBo.Category_Type_ID
+ ",[Sub_Category] = '" + categoryEntryBo.Sub_Category + "'"
+ ",[Update_Date] = '" + cmnBAL.GetCurrentServerDate().ToShortDateString() + "'"
+ " WHERE Category_ID=" + CategoryIdForUpdate;
try
{
_dbConnection.ConnectDatabase();
_dbConnection.ExecuteNonQuery(queryStringIns);
}
catch (Exception exception)
{
throw exception;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public int InsertIneterestWithdrawLog_UITransApplied(string Cust_Code, DateTime FromDate, DateTime ToDate, Double Amount, string Reference, string ConditionedBy)
{
CommonBAL comBal = new CommonBAL();
int NewProcessID = 0;
DateTime TodayServerDate = comBal.GetCurrentServerDate();
string queryString = "";
queryString = @"INSERT INTO [SBP_Database].[dbo].[SBP_InterestWithdraw_Log]
(
Applied_Cust_Code
,[FromDate]
,[ToDate]
,[Withdraw_Amount]
,[Reference]
,[ConditionedBy]
,[ProcessedBy]
,[ProcessedDate]
)
VALUES
(
'" + Cust_Code + @"'
,'" + FromDate.ToShortDateString() + @"'
,'" + ToDate.ToShortDateString() + @"'
," + Amount + @"
,'" + Reference + @"'
,'" + ConditionedBy + @"'
,'" + GlobalVariableBO._userName + @"'
,GETDATE()
)
SELECT SCOPE_IDENTITY() AS NewID";
try
{
//_dbConnection.ConnectDatabase();
_dbConnection.ClearParameters();
DataTable Dt = _dbConnection.ExecuteQuery(queryString);
if (Dt.Rows.Count > 0)
{
NewProcessID = Convert.ToInt32(Dt.Rows[0]["NewID"].ToString());
}
}
catch (Exception)
{
throw;
}
finally
{
//_dbConnection.CloseDatabase();
}
return(NewProcessID);
}
public void SaveExpense(ExpenseEntryBO expenseEntryBo)
{
string queryString = "";
CommonBAL cmnBAL = new CommonBAL();
queryString =
@"INSERT INTO SBP_Expense_Lookup
(
[Category_ID]
,[Expense_Description]
,[Frequency_ID]
,[Update_Date]
,[Expense_Type_ID]
)
VALUES
("
+ expenseEntryBo.Category_ID
+ ",'" + expenseEntryBo.Expense_Description
+ "'," + expenseEntryBo.Expense_Frequency
+ ",'" + cmnBAL.GetCurrentServerDate().ToShortDateString()
+ @",( Select b.Category_Type_ID
From dbo.SBP_Expense_Category_Lookup as b Where b.Category_ID=" + expenseEntryBo.Category_ID + @")"
+ "')";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.ExecuteNonQuery(queryString);
}
catch (Exception exception)
{
throw exception;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public void Update(string OCCType, int OCCTypeIdForUpdate)
{
string queryStringIns = "";
CommonBAL cmnBAL = new CommonBAL();
queryStringIns = @"UPDATE SBP_Expense_Frequency
SET [Frequency_Name] ='" + OCCType + "'"
+ ",[Entry_By] = '" + GlobalVariableBO._userName + "'"
+ ",[Entry_Date] = '" + cmnBAL.GetCurrentServerDate().ToShortDateString() + "'"
+ " WHERE Frequency_ID=" + OCCTypeIdForUpdate;
try
{
_dbConnection.ConnectDatabase();
_dbConnection.ExecuteNonQuery(queryStringIns);
}
catch (Exception exception)
{
throw exception;
}
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();
}
}
public void DepositBOClosingChargeInto98Account(DataTable dtAllAccountListNeedToDeposit98Account, DateTime ReceivedDate)
{
string queryString = string.Empty;
CommonBAL cmnBAL = new CommonBAL();
try
{
_dbConnection.ConnectDatabase();
_dbConnection.StartTransaction();
//if (accNo == 98)
//{
for (int i = 0; i < dtAllAccountListNeedToDeposit98Account.Rows.Count; i++)
{
queryString =
@"INSERT INTO SBP_Payment
(
[Cust_Code]
,[Amount]
,[Received_Date]
,[Payment_Media]
,[Payment_Media_No]
,[Payment_Media_Date]
,[Bank_ID]
,[Bank_Name]
,[Branch_ID]
,[Bank_Branch]
,[Received_By]
,[Deposit_Withdraw]
,[Payment_Approved_By]
,[Payment_Approved_Date]
,[Voucher_Sl_No]
,[Trans_Reason]
,[Remarks]
,[Entry_Date]
,[Entry_By]
,[Maturity_Days]
,[Requisition_ID]
,[Entry_Branch_ID])
VALUES
("
+ " '98'"
+ "," + dtAllAccountListNeedToDeposit98Account.Rows[i]["Amount"]
+ ",'" + dtAllAccountListNeedToDeposit98Account.Rows[i]["Received_Date"]
//+ ",'" + ReceivedDate.ToString("yyyy-MM-dd")
+ "','Cash'"
+ ",NULL"
+ ",NULL"
+ ",NULL"
+ ",NULL"
+ ",NULL"
+ ",NULL"
+ ",NULL"
+ ",'Deposit'"
+ ",NULL"
+ ",NULL"
+ ",'" + dtAllAccountListNeedToDeposit98Account.Rows[i]["Voucher_Sl_No"]
+ "','" + dtAllAccountListNeedToDeposit98Account.Rows[i]["Cust_Code"]
+ "',NULL"
+ ",'" + cmnBAL.GetCurrentServerDate().ToString("yyyy-MM-dd")
+ "','" + GlobalVariableBO._userName
+ "', NULL"
+ "," + dtAllAccountListNeedToDeposit98Account.Rows[i]["Requisition_ID"]
+ "," + GlobalVariableBO._branchId
+ ")";
_dbConnection.ExecuteNonQuery(queryString);
}
//}
// else if(accNo==99)
// {
// for (int i = 0; i < dtAllAccountListNeedToDeposit98OR99Account.Rows.Count; i++)
// {
// //Need Not to Change
// queryString =
// @"INSERT INTO SBP_Payment
// (
// [Cust_Code]
// ,[Amount]
// ,[Received_Date]
// ,[Payment_Media]
// ,[Payment_Media_No]
// ,[Payment_Media_Date]
// ,[Bank_ID]
// ,[Bank_Name]
// ,[Branch_ID]
// ,[Bank_Branch]
// ,[Received_By]
// ,[Deposit_Withdraw]
// ,[Payment_Approved_By]
// ,[Payment_Approved_Date]
// ,[Voucher_Sl_No]
// ,[Trans_Reason]
// ,[Remarks]
// ,[Entry_Date]
// ,[Entry_By]
// ,[Maturity_Days]
// ,[Requisition_ID]
// ,[Entry_Branch_ID])
// VALUES
// ("
// + " '" + accNo + "'"
// + "," + dtAllAccountListNeedToDeposit98OR99Account.Rows[i]["Amount"]
// + ",'" + dtAllAccountListNeedToDeposit98OR99Account.Rows[i]["Received_Date"]
// + "','Cash'"
// + ",NULL"
// + ",'" + dtAllAccountListNeedToDeposit98OR99Account.Rows[i]["Received_Date"]
// + "',NULL"
// + ",NULL"
// + ",NULL"
// + ",NULL"
// + ",NULL"
// + ",'Deposit'"
// + ",NULL"
// + ",NULL"
// + ",'" + dtAllAccountListNeedToDeposit98OR99Account.Rows[i]["Voucher_Sl_No"]
// + "','" + dtAllAccountListNeedToDeposit98OR99Account.Rows[i]["Cust_Code"]
// + "',NULL"
// + ",'" + cmnBAL.GetCurrentServerDate().ToString("yyyy-MM-dd")
// + "','" + GlobalVariableBO._userName
// + "', NULL"
// + ", NULL"
// + "," + GlobalVariableBO._branchId
// + ")";
// _dbConnection.ExecuteNonQuery(queryString);
// }
// }
_dbConnection.Commit();
}
catch (Exception ex)
{
_dbConnection.Rollback();
throw ex;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public void ProcessCashBack(int sessionId, DateTime ProcessDate)
{
CommonBAL objBAL = new CommonBAL();
string queryStringDeposit = "";
string queryStringWithdraw = "";
queryStringDeposit =
@" INSERT INTO SBP_Payment
(
[Cust_Code]
,[Amount]
,[Received_Date]
,[Payment_Media]
,[Payment_Media_No]
,[Payment_Media_Date]
,[Bank_Name]
,[Bank_Branch]
,[Received_By]
,[Deposit_Withdraw]
,[Payment_Approved_By]
,[Payment_Approved_Date]
,[Voucher_Sl_No]
,[Trans_Reason]
,[Remarks]
,[Entry_Date]
,[Entry_By]
,[Maturity_Days]
,[Requisition_ID]
,[Entry_Branch_ID]
)
SELECT
[Cust_Code]
,[CashBack_Amount]
"
+ ",CONVERT(DATETIME, CONVERT(VARCHAR(11), CONVERT(DATETIME,'" + ProcessDate.ToShortDateString() + "',103)))"
+ @",'Cash'
,''
,NULL
,''
,''
,''
,'Deposit'
,''
,NULL
,'CB'"
+ ",'CB-'+CONVERT(VARCHAR(10), RIGHT('0' + RTRIM(MONTH(Session_End_Date)), 2))+'-'+ CONVERT(VARCHAR(10),RIGHT('0' + RTRIM(YEAR(Session_End_Date)), 2))"
+ ",''"
+ ",CONVERT(DATETIME, CONVERT(VARCHAR(11), CONVERT(DATETIME,'" + objBAL.GetCurrentServerDate().ToShortDateString() + "',103)))"
+
",'" + GlobalVariableBO._userName + "'"
+ ",0"
+ ",0"
+ "," + GlobalVariableBO._branchId
+ @" FROM SBP_Cashback_Reg ,dbo.SBP_Cashback_Session
WHERE SBP_Cashback_Reg.CashBack_SessionID=SBP_Cashback_Session.ID
AND CashBack_SessionID=" + sessionId;
queryStringWithdraw =
@" INSERT INTO SBP_Payment
(
[Cust_Code]
,[Amount]
,[Received_Date]
,[Payment_Media]
,[Payment_Media_No]
,[Payment_Media_Date]
,[Bank_Name]
,[Bank_Branch]
,[Received_By]
,[Deposit_Withdraw]
,[Payment_Approved_By]
,[Payment_Approved_Date]
,[Voucher_Sl_No]
,[Trans_Reason]
,[Remarks]
,[Entry_Date]
,[Entry_By]
,[Maturity_Days]
,[Requisition_ID]
,[Entry_Branch_ID]
)
SELECT "
+ "'" + Indication_CompanyCode.Kscl_company_Account + "'"
+ @",[CashBack_Amount]
"
+ ",CONVERT(DATETIME, CONVERT(VARCHAR(11), CONVERT(DATETIME,'" + ProcessDate.ToShortDateString() + "',103)))"
+ @",'Cash'
,''
,NULL
,NULL
,NULL
,NULL
,'Withdraw'
,NULL
,NULL"
+ ",'CB'+[Cust_Code]"
+ ",'CB-'+CONVERT(VARCHAR(10), RIGHT('0' + RTRIM(MONTH(Session_End_Date)), 2))+'-'+ CONVERT(VARCHAR(10),RIGHT('0' + RTRIM(YEAR(Session_End_Date)), 2))"
+ ",NULL"
+ ",CONVERT(DATETIME, CONVERT(VARCHAR(11), CONVERT(DATETIME,'" + objBAL.GetCurrentServerDate().ToShortDateString() + "',103)))"
+
",'" + GlobalVariableBO._userName + "'"
+ ",0"
+ ",NULL"
+ "," + GlobalVariableBO._branchId
+ @" FROM dbo.SBP_Cashback_Reg,dbo.SBP_Cashback_Session
WHERE SBP_Cashback_Reg.CashBack_SessionID=SBP_Cashback_Session.ID
AND CashBack_SessionID=" + sessionId;
string updateQuery = @"UPDATE SBP_Cashback_Session
SET [IsProcessed] =1
,[Process_Date]='" + ProcessDate.ToShortDateString()// objBAL.GetCurrentServerDate().ToShortDateString()
+ "' WHERE ID=" + sessionId;
try
{
_dbConnection.ConnectDatabase();
_dbConnection.StartTransaction();
_dbConnection.ExecuteNonQuery(queryStringDeposit);
_dbConnection.ExecuteNonQuery(queryStringWithdraw);
_dbConnection.ExecuteNonQuery(updateQuery);
_dbConnection.Commit();
}
catch (Exception)
{
_dbConnection.Rollback();
throw;
}
finally
{
_dbConnection.CloseDatabase();
}
}
