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(); } }