public void Insert(IPOChargeDefBO ipoChargeDefBo)
{
string queryString = "";
CommonBAL commonBAL = new CommonBAL();
ipoChargeDefBo.SlNo = commonBAL.GenerateID("SBP_Ch_Def_IPO", "Sl_No");
queryString = @"SBPSaveIPOChargeDef";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.ActiveStoredProcedure();
_dbConnection.AddParameter("@SlNo", SqlDbType.Int, ipoChargeDefBo.SlNo);
_dbConnection.AddParameter("@CompShortCode", SqlDbType.NVarChar, ipoChargeDefBo.CompShortCode);
_dbConnection.AddParameter("@CompName", SqlDbType.NVarChar, ipoChargeDefBo.CompName);
_dbConnection.AddParameter("@ShareType", SqlDbType.VarChar, ipoChargeDefBo.ShareType);
_dbConnection.AddParameter("@Commision", SqlDbType.Money, ipoChargeDefBo.Commision);
_dbConnection.AddParameter("@EffectiveDate", SqlDbType.DateTime, ipoChargeDefBo.EffectiveDate.ToShortDateString());
_dbConnection.AddParameter("@remark1", SqlDbType.VarChar, ipoChargeDefBo.Remarks1);
_dbConnection.AddParameter("@remark2", SqlDbType.VarChar, ipoChargeDefBo.Remarks2);
_dbConnection.AddParameter("@entryBy", SqlDbType.VarChar, GlobalVariableBO._userName);
_dbConnection.ExecuteNonQuery(queryString);
}
catch (Exception exception)
{
throw exception;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public void SaveData(List <CashBackRegBO> cashBackRegBos)
{
string queryString = "";
CommonBAL commonBAL = new CommonBAL();
// cashBackRegBo.SlNo = commonBAL.GenerateID("SBP_CashBack_Reg", "CB_ID");
queryString = @"SBPSaveCashBackReg";
try
{
_dbConnection.ConnectDatabase();
foreach (CashBackRegBO cashBackRegBo in cashBackRegBos)
{
_dbConnection.ActiveStoredProcedure();
// _dbConnection.AddParameter("@cbID", SqlDbType.Int, cashBackRegBo.SlNo);
_dbConnection.AddParameter("@sessionID", SqlDbType.Int, cashBackRegBo.SessionId);
_dbConnection.AddParameter("@custCode", SqlDbType.NVarChar, cashBackRegBo.CustCode);
_dbConnection.AddParameter("@planID", SqlDbType.Int, cashBackRegBo.PlanId);
_dbConnection.AddParameter("@planName", SqlDbType.VarChar, cashBackRegBo.PlanName);
_dbConnection.AddParameter("@cashbackamount", SqlDbType.Money, cashBackRegBo.CashBackAmount);
_dbConnection.AddParameter("@remark", SqlDbType.VarChar, cashBackRegBo.Remarks);
// _dbConnection.AddParameter("@entryDate", SqlDbType.DateTime, cashBackRegBo.EntryDate);
_dbConnection.AddParameter("@entryBy", SqlDbType.VarChar, GlobalVariableBO._userName);
_dbConnection.ExecuteNonQuery(queryString);
}
}
catch (Exception exception)
{
throw exception;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public void Insert(MarginChargeDefBO marginChargeDefBo)
{
string queryString = "";
CommonBAL commonBAL = new CommonBAL();
marginChargeDefBo.Id = commonBAL.GenerateID("SBP_Margin_Charge_Def", "ID");
queryString = @"SBPSaveMarginCharge";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.ActiveStoredProcedure();
_dbConnection.AddParameter("@ChDefId", SqlDbType.Int, marginChargeDefBo.Id);
_dbConnection.AddParameter("@PlanName", SqlDbType.NVarChar, marginChargeDefBo.PlanName);
_dbConnection.AddParameter("@ChargeRate", SqlDbType.Float, marginChargeDefBo.ChargeRate);
_dbConnection.AddParameter("@EffectiveCount", SqlDbType.Int, marginChargeDefBo.EffectiveCount);
_dbConnection.AddParameter("@effectiveDate", SqlDbType.DateTime, marginChargeDefBo.EffectiveDate.ToShortDateString());
_dbConnection.AddParameter("@remarks", SqlDbType.VarChar, marginChargeDefBo.Remarks);
_dbConnection.AddParameter("@entryBy", SqlDbType.VarChar, GlobalVariableBO._userName);
_dbConnection.ExecuteNonQuery(queryString);
}
catch (Exception exception)
{
throw exception;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public void SaveIntoTradePrice(DateTime eventDate)
{
string queryStringInsert = "";
string queryStringUploadHistory = "";
CommonBAL comBAL = new CommonBAL();
long slNo = comBAL.GenerateID("SBP_Upload_History", "Upload_ID");
//NI
queryStringInsert = "INSERT INTO SBP_Trade_Price(Instrument_Code,Open_Price,High_Price,Low_Price,Close_Price,Change,Total_Trade,Volume,Trade_Value,Trade_Date) SELECT Instrument_Code,Open_Price,High_Price,Low_Price,Close_Price,Change,Total_Trade,Volume,Trade_Value,'" + eventDate + "' FROM SBP_Trade_Price_Temp";
queryStringUploadHistory = "INSERT INTO SBP_Upload_History(Upload_ID,File_Name,Upload_Date,Entry_By) SELECT TOP 1 " + slNo + ",'tradePrice','" + eventDate.ToShortDateString() + "','" + GlobalVariableBO._userName + "' FROM SBP_Trade_Price_Temp";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.StartTransaction();
//_dbConnection.ExecuteNonQuery(queryStringUpdate);
_dbConnection.ExecuteNonQuery(queryStringInsert);
_dbConnection.ExecuteNonQuery(queryStringUploadHistory);
_dbConnection.Commit();
}
catch (Exception exception)
{
_dbConnection.Rollback();
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 UpdateBOCloseInfo()
{
string queryStringCustomers = "";
string queryStringInsHistory = "";
string queryStringUploadHistory = "";
queryStringCustomers = "UPDATE SBP_Customers SET BO_Status_ID=2,BO_Close_Date=(Select TOP 1 Closure_Date FROM SBP_08DP04UX)WHERE BO_ID IN(Select SUBSTRING(BO_ID,9,8) FROM SBP_08DP04UX)";
queryStringInsHistory = "INSERT INTO SBP_08DP04UX_History(BO_ID,BO_Name,Closure_Date,Request_Date,Reason_Failure,Remarks,File_Date) SELECT BO_ID,BO_Name,Closure_Date,Request_Date,Reason_Failure,Remarks,File_Date FROM SBP_08DP04UX";
CommonBAL comBAL = new CommonBAL();
long slNo = comBAL.GenerateID("SBP_Upload_History", "Upload_ID");
queryStringUploadHistory = "INSERT INTO SBP_Upload_History(Upload_ID,File_Name,Upload_Date,Entry_By) SELECT TOP 1 " + slNo + ",'08DP04UX',File_Date,'" + GlobalVariableBO._userName + "' FROM SBP_08DP04UX";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.StartTransaction();
_dbConnection.ExecuteNonQuery(queryStringCustomers);
_dbConnection.ExecuteNonQuery(queryStringInsHistory);
_dbConnection.ExecuteNonQuery(queryStringUploadHistory);
_dbConnection.Commit();
}
catch (Exception exception)
{
_dbConnection.Rollback();
throw exception;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public void SaveIntoShareDW_UITran(DbConnection connection)
{
string queryStringInsShareDW = "";
string queryStringInsHistory = "";
string queryStringUploadHistory = "";
queryStringInsShareDW = "INSERT INTO SBP_Share_DW(Cust_Code,Comp_Short_Code,Quantity,Lockedin_Quantity,Available_Quantity,Issue_Price,Received_Date,Effective_Date,Deposit_Withdraw,Deposit_Type,Share_Type,Issue_Amount,Entry_Date,Entry_By)SELECT dbo.GetCustCodeFromBO(BO_ID),dbo.GetCompShortCodeFromISINNo(ISIN_No),Current_Balance,Lockedin_Balance,Current_Balance-Lockedin_Balance,Issue_Price,Effective_Date,Effective_Date,'Deposit','IPO','CDBL',Issue_Price*Current_Balance,Getdate(),'" + GlobalVariableBO._userName + "' From SBP_16DP95UX";
queryStringInsHistory = "INSERT INTO SBP_16DP95UX_History(ISIN_No,ISIN_Short_Name,Sequence_No,Effective_Date,BO_ID,BO_Short_Name,BO_Category,BO_Account_Status,Current_Balance,Lockedin_Balance,Proc_Flag,un1,un2,un3,Issue_Price,CA_Type)" +
"SELECT ISIN_No,ISIN_Short_Name,Sequence_No,Effective_Date,BO_ID,BO_Short_Name,BO_Category,BO_Account_Status,Current_Balance,Lockedin_Balance,Proc_Flag,un1,un2,un3,Issue_Price,CA_Type FROM SBP_16DP95UX";
CommonBAL comBAL = new CommonBAL();
long slNo = comBAL.GenerateID("SBP_Upload_History", "Upload_ID");
queryStringUploadHistory = "INSERT INTO SBP_Upload_History(Upload_ID,File_Name,Upload_Date,Entry_By) SELECT TOP 1 " + slNo + ",'16DP95UX',Effective_Date,'" + GlobalVariableBO._userName + "' FROM SBP_16DP95UX";
try
{
//_dbConnection.ConnectDatabase();
//_dbConnection.StartTransaction();
connection.ExecuteNonQuery(queryStringInsShareDW);
connection.ExecuteNonQuery(queryStringInsHistory);
connection.ExecuteNonQuery(queryStringUploadHistory);
//_dbConnection.Commit();
}
catch (Exception exception)
{
//_dbConnection.Rollback();
throw exception;
}
//finally
//{
// //_dbConnection.CloseDatabase();
//}
}
public void UpdateClientCode(string _newcustCode, string _oldCustCode, int _updateID)
{
string queryStringInsert = "";
CommonBAL commonBal = new CommonBAL();
long slNo = commonBal.GenerateID("SBP_Transaction_Change_History", "Sl_No");
string queryLog = "INSERT INTO SBP_Transaction_Change_History(Sl_No,BORN,InstrumentCode,ISIN,UserID,BuySellFlag,TradeQty,TradePrice,EventDate,EventTime,OldCustomer,NewCustomer,ModifiedBy,ModifiedDate) SELECT " + slNo + ",BORN,InstrumentCode,ISIN,UserID,BuySellFlag,TradeQty,TradePrice,EventDate,EventTime,'" + _oldCustCode + "','" + _newcustCode + "','" + GlobalVariableBO._userName + "',GETDATE() FROM SBP_Transaction_Temp WHERE ID='" + _updateID + "'";
queryStringInsert = "UPDATE SBP_Transaction_Temp SET Customer='" + _newcustCode + "',BOID=(SELECT '12023500'+dbo.GetBOFromCustCode('" + _newcustCode + "')) WHERE ID=" + _updateID + "";
//queryLog = "UPDATE SBP_Transaction_Temp SET Customer='" + _custCode + "',BOID=(SELECT '12023500'+dbo.GetBOFromCustCode('" + _custCode + "')) WHERE ID=" + _updateID + "";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.StartTransaction();
_dbConnection.ExecuteNonQuery(queryStringInsert);
_dbConnection.ExecuteNonQuery(queryLog);
_dbConnection.Commit();
}
catch (Exception exception)
{
_dbConnection.Rollback();
throw exception;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public void SaveIntoTransaction()
{
string queryStringInsert = "";
string queryStringUploadHistory = "";
queryStringInsert = "EXECUTE SP_ProcessTransactions;";
CommonBAL comBAL = new CommonBAL();
long slNo = comBAL.GenerateID("SBP_Upload_History", "Upload_ID");
queryStringUploadHistory = "INSERT INTO SBP_Upload_History(Upload_ID,File_Name,Upload_Date,Entry_By) SELECT TOP 1 " + slNo + ",'tradeFileOld',EventDate,'" + GlobalVariableBO._userName + "' FROM SBP_Transactions_Temp_Old";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.StartTransaction();
_dbConnection.ExecuteNonQuery(queryStringInsert);
_dbConnection.ExecuteNonQuery(queryStringUploadHistory);
_dbConnection.Commit();
}
catch (Exception exception)
{
_dbConnection.Rollback();
throw exception;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public void Insert(ClosingChargeBO closingChargeBo)
{
string queryString = "";
CommonBAL commonBAL = new CommonBAL();
closingChargeBo.ChId = commonBAL.GenerateID("SBP_Account_Closing_Charge", "Ch_ID");
queryString = @"SBPSaveClosingCharge";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.ActiveStoredProcedure();
_dbConnection.AddParameter("@ChId", SqlDbType.Int, closingChargeBo.ChId);
_dbConnection.AddParameter("@ClosingChargeRate", SqlDbType.Money, closingChargeBo.ClosingCharge);
_dbConnection.AddParameter("@EffectiveDate", SqlDbType.DateTime, closingChargeBo.EffectiveDate.ToShortDateString());
_dbConnection.AddParameter("@entryBy", SqlDbType.VarChar, GlobalVariableBO._userName);
_dbConnection.ExecuteNonQuery(queryString);
}
catch (Exception exception)
{
throw exception;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public void ChangeCheckPrintStatus(int requisitionId) //Change to Reprint
{
CommonBAL commonBal = new CommonBAL();
long logId;
logId = commonBal.GenerateID("SBP_Check_Reprint_Log", "Log_Id");
string queryLog = "INSERT INTO SBP_Check_Reprint_Log (Log_Id,Requisition_Id,Cust_Code,Amount,Requisition_Date,Payment_Media_No,Voucher_Sl_No,Check_Date,Collection_Branch_Id,Entry_Branch_ID,Modified_By,Modified_Date) SELECT " + logId + ",Sl_No,Cust_Code,Amount,Requisition_Date,(SELECT Payment_Media_No FROM SBP_Payment WHERE Requisition_ID=SBP_Check_Requisition.Sl_No),(SELECT Voucher_Sl_No FROM SBP_Payment WHERE Requisition_ID=SBP_Check_Requisition.Sl_No),(SELECT Received_Date FROM SBP_Payment WHERE Requisition_ID=SBP_Check_Requisition.Sl_No),Collection_Branch_Id,(SELECT Branch_ID FROM SBP_Users WHERE User_Name=SBP_Check_Requisition.Entry_By),'" + GlobalVariableBO._userName + "',CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) FROM SBP_Check_Requisition WHERE Sl_No=" + requisitionId + "";
string queryUpdateRequisition = "UPDATE SBP_Check_Requisition SET Is_Printed=0 WHERE Sl_No=" + requisitionId + "";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.StartTransaction();
_dbConnection.ExecuteNonQuery(queryLog);
_dbConnection.ExecuteNonQuery(queryUpdateRequisition);
_dbConnection.Commit();
}
catch (Exception)
{
_dbConnection.Rollback();
}
finally
{
_dbConnection.CloseDatabase();
}
}
public void Insert(ChargeDefBO chargeDefBo)
{
string queryString = "";
CommonBAL commonBAL = new CommonBAL();
chargeDefBo.ChDefId = commonBAL.GenerateID("SBP_Ch_Def_All", "Ch_ID");
queryString = @"SBPSaveChargeDef";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.ActiveStoredProcedure();
_dbConnection.AddParameter("@ChDefId", SqlDbType.Int, chargeDefBo.ChDefId);
_dbConnection.AddParameter("@ChItem", SqlDbType.VarChar, chargeDefBo.ChItem);
_dbConnection.AddParameter("@ChItemDescription", SqlDbType.VarChar, chargeDefBo.ChItemDescription);
_dbConnection.AddParameter("@Category", SqlDbType.VarChar, chargeDefBo.Category);
_dbConnection.AddParameter("@MinCh", SqlDbType.Money, chargeDefBo.MinCh);
_dbConnection.AddParameter("@ChRate", SqlDbType.Decimal, chargeDefBo.ChRate);
_dbConnection.AddParameter("@EffectiveDate", SqlDbType.DateTime, chargeDefBo.EffectiveDate.ToShortDateString());
_dbConnection.AddParameter("@entryBy", SqlDbType.VarChar, GlobalVariableBO._userName);
_dbConnection.ExecuteNonQuery(queryString);
}
catch (Exception exception)
{
throw exception;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public void ConvertVisibleToHidden(object companyName)
{
string companyQueryString = "";
long slNo;
CommonBAL commonBal = new CommonBAL();
slNo = commonBal.GenerateID("SBP_Hide_Company_Price", "Sl_No");
companyQueryString = "INSERT INTO SBP_Hide_Company_Price(Sl_No,Instrument_Code) VALUES(" + slNo + ",'" + companyName + "')";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.StartTransaction();
_dbConnection.ExecuteNonQuery(companyQueryString);
_dbConnection.Commit();
}
catch (Exception exception)
{
_dbConnection.Rollback();
throw exception;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public void Insert(CompanyCategoryChangeBO changeBo)
{
string compCatChangeQryString = "";
CommonBAL commonBAL = new CommonBAL();
changeBo.SlNo = commonBAL.GenerateID("SBP_Comp_Cat_Log", "Sl_No");
try
{
_dbConnection.ConnectDatabase();
_dbConnection.StartTransaction();
compCatChangeQryString = @"SBPSaveCompCatChange";
_dbConnection.ActiveStoredProcedure();
_dbConnection.AddParameter("@SlNo", SqlDbType.Int, changeBo.SlNo);
_dbConnection.AddParameter("@CompShortCode", SqlDbType.NVarChar, changeBo.CompShortCode);
_dbConnection.AddParameter("@OldCategoryId", SqlDbType.Int, changeBo.OldCategoryId);
_dbConnection.AddParameter("@NewCategoryId", SqlDbType.Int, changeBo.NewCategoryId);
_dbConnection.AddParameter("@EffectiveDate", SqlDbType.DateTime, changeBo.EffectiveDate);
_dbConnection.AddParameter("@entryBy", SqlDbType.VarChar, GlobalVariableBO._userName);
_dbConnection.ExecuteNonQuery(compCatChangeQryString);
_dbConnection.Commit();
}
catch (Exception exception)
{
_dbConnection.Rollback();
throw exception;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public void InsertData(PdfReportBO testClassBo)
{
string queryString = "";
CommonBAL oCommonBal = new CommonBAL();
testClassBo.RelationshipID = oCommonBal.GenerateID("SBP_Relationship", "Relationship_ID");
queryString = "INSERT INTO SBP_Relationship (Relationship_ID,Relation)" +
" Values(" + testClassBo.RelationshipID + ",'" + testClassBo.Relationship + "')";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.StartTransaction();
_dbConnection.ExecuteNonQuery(queryString);
_dbConnection.Commit();
}
catch (Exception)
{
_dbConnection.Rollback();
throw;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public void Insert(BranchManagementBO branchMngmntBO)
{
string queryString = "";
CommonBAL commonBAL = new CommonBAL();
branchMngmntBO.BranchId = commonBAL.GenerateID("SBP_Broker_Branch", "Branch_ID");
queryString = @"SBPSaveBranch";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.ActiveStoredProcedure();
_dbConnection.AddParameter("@branchID", SqlDbType.Int, branchMngmntBO.BranchId);
_dbConnection.AddParameter("@branchName", SqlDbType.VarChar, branchMngmntBO.BranchName);
_dbConnection.AddParameter("@address", SqlDbType.VarChar, branchMngmntBO.BranchAddress);
_dbConnection.AddParameter("@telePhone", SqlDbType.VarChar, branchMngmntBO.TelePhone);
_dbConnection.AddParameter("@fax", SqlDbType.VarChar, branchMngmntBO.Fax);
_dbConnection.AddParameter("@eMail", SqlDbType.VarChar, branchMngmntBO.Email);
_dbConnection.AddParameter("@web", SqlDbType.VarChar, branchMngmntBO.Web);
_dbConnection.AddParameter("@openDate", SqlDbType.DateTime, branchMngmntBO.BranchOpeningdate.ToShortDateString());
_dbConnection.AddParameter("@isActive ", SqlDbType.Int, branchMngmntBO.BranchIsActive);
_dbConnection.AddParameter("@entryBy", SqlDbType.VarChar, GlobalVariableBO._userName);
_dbConnection.ExecuteNonQuery(queryString);
}
catch (Exception exception)
{
throw exception;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public void InsertCustomerGroup(AdditionalInformationBO additionalInformationBo)
{
string queryString = "";
CommonBAL oCommonBal = new CommonBAL();
additionalInformationBo.CustomerGroupID = oCommonBal.GenerateID("SBP_Cust_Group", "Cust_Group_ID");
queryString = @"SBPSaveCustomerGroup";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.ActiveStoredProcedure();
_dbConnection.AddParameter("@CustomerGroupID", SqlDbType.Int, additionalInformationBo.CustomerGroupID);
_dbConnection.AddParameter("@CustomerGroup", SqlDbType.VarChar, additionalInformationBo.CustomerGroup);
_dbConnection.AddParameter("@CustomerGroupDescription", SqlDbType.VarChar, additionalInformationBo.CustomerGroupDescription);
_dbConnection.ExecuteNonQuery(queryString);
}
catch (Exception exception)
{
throw exception;
}
finally
{
_dbConnection.CloseDatabase();
}
}
//public void InsertOrderChannel(AdditionalInformationBO additionalInformationBo)
//{
// string queryString = "";
// CommonBAL oCommonBal = new CommonBAL();
// additionalInformationBo.OrderChannelID = oCommonBal.GenerateID("SBP_Order_Channel", "Order_Channel_ID");
// queryString = "INSERT INTO SBP_Order_Channel (Order_Channel_ID,Order_Channel,Status)" +
// " Values('" + additionalInformationBo.OrderChannelID + "','" + additionalInformationBo.OrderChannel + "','" + additionalInformationBo.OrderChannelStatus + "')";
// try
// {
// _dbConnection.ConnectDatabase();
// _dbConnection.StartTransaction();
// _dbConnection.ExecuteNonQuery(queryString);
// _dbConnection.Commit();
// }
// catch (Exception)
// {
// _dbConnection.Rollback();
// throw;
// }
// finally
// {
// _dbConnection.CloseDatabase();
// }
//}
public void InsertCompanyCategory(AdditionalInformationBO additionalInformationBo)
{
string queryString = "";
CommonBAL oCommonBal = new CommonBAL();
additionalInformationBo.CompanyCategoryID = oCommonBal.GenerateID("SBP_Comp_Category", "Comp_Cat_ID");
queryString = @"SBPSaveCompanyCategory";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.ActiveStoredProcedure();
_dbConnection.AddParameter("@CompanyCategoryID", SqlDbType.Int, additionalInformationBo.CompanyCategoryID);
_dbConnection.AddParameter("@CompanyCategory", SqlDbType.NVarChar, additionalInformationBo.CompanyCategory);
_dbConnection.AddParameter("@CompanyCategoryMinDate", SqlDbType.Int, additionalInformationBo.CompanyCategoryMinDate);
_dbConnection.ExecuteNonQuery(queryString);
}
catch (Exception exception)
{
throw exception;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public void SaveOthersHoliday(HolidayBO holydayBo)
{
string queryString = "";
CommonBAL comBAL = new CommonBAL();
// holydayBo.SlNo = comBAL.GenerateID("SBP_Holiday", "Sl_No");
queryString = @"SBPSaveHoliday";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.ActiveStoredProcedure();
//_dbConnection.AddParameter("@SlNo", SqlDbType.Int, holydayBo.SlNo);
_dbConnection.AddParameter("@HolydayDate", SqlDbType.DateTime, holydayBo.HolydayDate.ToShortDateString());
_dbConnection.AddParameter("@Purpose", SqlDbType.VarChar, holydayBo.Purpose);
_dbConnection.AddParameter("@entryBy", SqlDbType.VarChar, GlobalVariableBO._userName);
_dbConnection.ExecuteNonQuery(queryString);
}
catch (Exception exception)
{
throw exception;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public void Insert(CustomerCommisionBO commisionBo)
{
CommonBAL commonBAL = new CommonBAL();
commisionBo.CommID = commonBAL.GenerateID("SBP_Cust_Commissions", "Commission_ID");
string queryString = "";
queryString = @"SBPSaveCustCommissionDef";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.ActiveStoredProcedure();
_dbConnection.AddParameter("@CommID", SqlDbType.Int, commisionBo.CommID);
_dbConnection.AddParameter("@GroupId", SqlDbType.Int, commisionBo.GroupId);
_dbConnection.AddParameter("@MinComm", SqlDbType.Money, commisionBo.MinComm);
_dbConnection.AddParameter("@GroupCommRate", SqlDbType.Money, commisionBo.GroupCommRate);
_dbConnection.AddParameter("@Category", SqlDbType.VarChar, commisionBo.Category);
_dbConnection.AddParameter("@EffectiveFrom", SqlDbType.DateTime, commisionBo.EffectiveFrom);
_dbConnection.AddParameter("@entryBy", SqlDbType.VarChar, GlobalVariableBO._userName);
_dbConnection.ExecuteNonQuery(queryString);
}
catch (Exception exception)
{
throw exception;
}
finally
{
_dbConnection.CloseDatabase();
}
}
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 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 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 void Insert(PaymentMaturityBO paymentMaturityBo)
{
string queryString = "";
CommonBAL commonBAL = new CommonBAL();
paymentMaturityBo.Id = commonBAL.GenerateID("SBP_Payment_Media_Maturity", "ID");
queryString = @"SBPSavePaymentMediaMaturity";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.ActiveStoredProcedure();
_dbConnection.AddParameter("@Id", SqlDbType.Int, paymentMaturityBo.Id);
_dbConnection.AddParameter("@PaymentMedia", SqlDbType.VarChar, paymentMaturityBo.PaymentMedia);
_dbConnection.AddParameter("@MaturityDays", SqlDbType.Int, paymentMaturityBo.MaturityDays);
_dbConnection.AddParameter("@effectiveDate", SqlDbType.DateTime, paymentMaturityBo.EffectiveDate);
_dbConnection.AddParameter("@entryBy", SqlDbType.VarChar, GlobalVariableBO._userName);
_dbConnection.ExecuteNonQuery(queryString);
}
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 SaveParentInfo(ParentAndChildBO pBo)
{
CommonBAL com = new CommonBAL();
string query = @"INSERT INTO [SBP_Parent_Child_Details]
(
Parent_Code
,[Child_Code]
,[Child_BOID]
,[Handeler_Name]
,[Parent_Name]
,[Parent_Gender]
,[Parent_Profession]
,[Parent_Email]
,[Parent_Contact_Mobile]
,[Parent_Contact_Land]
,[Parent_Address_Present]
,[Parent_Address_Permanet]
,[Entry_By]
,[Entry_Date])
VALUES
('" + pBo.Parent_Code + "','"
+ pBo.Child_Code + "','"
+ pBo.Chil_BoId + "','"
+ com.HandlingSingelQuation(pBo.Handeler_Name) + "','"
+ com.HandlingSingelQuation(pBo.Parent_Name) + "','"
+ com.HandlingSingelQuation(pBo.Parent_Gender) + "','"
+ com.HandlingSingelQuation(pBo.Parent_Profession) + "','"
+ com.HandlingSingelQuation(pBo.parent_Email) + "','"
+ pBo.Parent_Cell + "','"
+ pBo.parent_land + "','"
+ com.HandlingSingelQuation(pBo.Parent_Present_addr) + "','"
+ com.HandlingSingelQuation(pBo.parent_Permanent_add) + "','"
+ com.HandlingSingelQuation(GlobalVariableBO._userName) + "',"
+ ("getdate()") + ")";
try
{
_dbconnection.ConnectDatabase();
_dbconnection.ExecuteNonQuery(query);
}
catch (Exception ex)
{
throw ex;
}
finally
{
_dbconnection.CloseDatabase();
}
}
public void DeleteParentChild_LOG(int parent)
{
CommonBAL com = new CommonBAL();
string query = @"ParentChildDeleteLog";
try
{
_dbconnection.ConnectDatabase();
_dbconnection.AddParameter("@Parent_Code", SqlDbType.Int, parent);
_dbconnection.AddParameter("@User_Name", SqlDbType.VarChar, GlobalVariableBO._userName);
_dbconnection.ExecuteProQuery(query);
}
catch (Exception ex)
{
throw ex;
}
finally
{
_dbconnection.CloseDatabase();
}
}
public void SaveHideCustomer(string custCode, string userName, int resourceId, int criteraId)
{
CommonBAL commonBAL = new CommonBAL();
long slNo = commonBAL.GenerateID("SBP_Hide_Customer", "Sl_No");
string queryString = "INSERT INTO SBP_Hide_Customer(Sl_No,Reference_Code,User_Name,Resource_ID,Criteria_ID,Entry_Date,Entry_By) Values(" + slNo + ",'" + custCode + "','" + userName + "'," + resourceId + "," + criteraId + ",CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME),'" + GlobalVariableBO._userName + "')";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.ExecuteNonQuery(queryString);
}
catch (Exception exception)
{
throw exception;
}
finally
{
_dbConnection.CloseDatabase();
}
}
public void InsertDashboardLog(string custCode)
{
CommonBAL commonBAL = new CommonBAL();
//int slNo = commonBAL.GenerateID("SBP_Dashboard_Log", "Sl_No");
//string queryString = "INSERT INTO SBP_Dashboard_Log(Sl_No,User_Name,Cust_Code,Log_time) Values(" + slNo + ",'" + GlobalVariableBO._userName + "','" + custCode + "',GETDATE())";
string queryString = "INSERT INTO SBP_Dashboard_Log(User_Name,Cust_Code,Log_time) Values('" + GlobalVariableBO._userName + "','" + custCode + "',GETDATE())";
try
{
_dbConnection.ConnectDatabase();
_dbConnection.ExecuteNonQuery(queryString);
}
catch (Exception exception)
{
throw exception;
}
finally
{
_dbConnection.CloseDatabase();
}
}
