/// <summary>
/// Updates a Pawn Loan Array setting the PFI_NOTE and PRINT_NOTICE accourdingly.
/// </summary>
/// <param name="dataAccessor"></param>
/// <param name="errorCode"></param>
/// <param name="errorText"></param>
/// <param name="storeNumber"></param>
/// <param name="ticketNumber"></param>
/// <param name="originalPFINote"></param>
/// <param name="pfiEligibleDate"></param>
/// <param name="fee_amount"></param>
/// <returns></returns>
public static bool ExecuteAddPFIMailerDataArray(
OracleDataAccessor dataAccessor,
out string errorCode,
out string errorText,
int storeNumber,
List <int> ticketNumber, // p_ticketNumber
IEnumerable <DateTime> originalPFINote, // p_pfi_note
IEnumerable <DateTime> pfiEligibleDate, // p_eligible_date
decimal fee_amount = 0
)
{
errorCode = String.Empty;
errorText = String.Empty;
// Ensure the data accessor is valid
if (dataAccessor == null || !dataAccessor.Initialized)
{
BasicExceptionHandler.Instance.AddException("ExecuteGetPFIMailerDataArray Failed",
new ApplicationException("ExecuteGetPFIMailerDataArray Failed: Data accessor instance is invalid"));
return(false);
}
var oPFINote = originalPFINote.Select(dt => dt.ToString("MM/dd/yyyy")).ToList();
var oPFIEDate = pfiEligibleDate.Select(dt => dt.ToString("MM/dd/yyyy")).ToList();
// Set up input variables
var inParams = new List <OracleProcParam>
{
new OracleProcParam("p_storeNumber", storeNumber),
new OracleProcParam("p_ticketNumber", true, ticketNumber),
new OracleProcParam("p_pfi_note", true, oPFINote), //originalPFINote.ToString("MM/dd/yyyy")),
new OracleProcParam("p_eligible_date", true, oPFIEDate), //pfiEligibleDate.ToString("MM/dd/yyyy")),
new OracleProcParam("p_date_made", ShopDateTime.Instance.ShopDate.ToString("MM/dd/yyyy hh:mm:ss tt")),
new OracleProcParam("p_createdBy", GlobalDataAccessor.Instance.DesktopSession.FullUserName),
new OracleProcParam("p_fee_amount", fee_amount)
};
var retVal = false;
var refCursArr = new List <PairType <string, string> >();
try
{
DataSet outputDataSet;
retVal = dataAccessor.issueSqlStoredProcCommand(
"ccsowner", "pawn_mdse_procs",
"update_pfi_mailer_data_array", inParams,
refCursArr, "o_return_code",
"o_return_text",
out outputDataSet);
errorCode = dataAccessor.ErrorCode;
errorText = dataAccessor.ErrorDescription;
}
catch (OracleException oEx)
{
BasicExceptionHandler.Instance.AddException("ExecuteGetPFIMailerDataArray Failed", oEx);
errorCode = " --- ExecuteGetPFIMailerDataArray";
errorText = " --- OracleException thrown: " + oEx.Message;
return(false);
}
if (retVal == false)
{
BasicExceptionHandler.Instance.AddException("ExecuteGetPFIMailerDataArray Failed: return value is false", new ApplicationException());
errorCode = dataAccessor.ErrorCode + " -- ExecuteGetPFIMailerDataArray";
errorText = dataAccessor.ErrorDescription + " -- Return value is false";
return(false);
}
errorCode = "0";
errorText = "Success";
return(true);
}
//********************************************************
/// <summary>
/// one of four constructors: This is the next level contructor
/// accepting three of the needed parameters for data
/// gathering. As such, it uses the current date for the
/// inital day and false to indicate we want the previous
/// dateRange number of days
/// </summary>
//********************************************************
public GetFirearmData(string storeNum, OracleDataAccessor dataAccessor, int dateRange)
{
_dA = dataAccessor;
SetDefaults(DateTime.Now, storeNum, dateRange, false);
}
//********************************************************
/// <summary>
/// one of four constructors: this one takes all the values
/// needed to create our data
///
/// </summary>
//********************************************************
public GetFirearmData(DateTime initialDate, string storeNum,
OracleDataAccessor dataAccessor, int dateRange, bool forwards)
{
_dA = dataAccessor;
SetDefaults(initialDate, storeNum, dateRange, forwards);
}
public static bool GetAssignableItems(
string StoreNumber,
int searchType,
string searchCriteria,
out DataTable items,
out string errorCode,
out string errorText)
{
//Create input list
List <OracleProcParam> inParams = new List <OracleProcParam>();
//Add cat pointer
inParams.Add(new OracleProcParam("p_store_number", StoreNumber));
inParams.Add(new OracleProcParam("p_search_type", searchType));
inParams.Add(new OracleProcParam("p_search", searchCriteria));
//return GetItems("get_items2assign", inParams, "GetAssignableItems", out items, out errorCode, out errorText);
//Set default output values
errorCode = string.Empty;
errorText = string.Empty;
DataSet outputDataSet = null;
items = new DataTable();
//Verify that the accessor is valid
if (GlobalDataAccessor.Instance == null ||
GlobalDataAccessor.Instance.OracleDA == null)
{
errorCode = "GetAssignableItems";
errorText = "Invalid desktop session or data accessor instance";
BasicExceptionHandler.Instance.AddException("GetAssignableItems", new ApplicationException("Cannot execute the GetAssignableItems retrieval stored procedure"));
return(false);
}
//Get data accessor object
OracleDataAccessor dA = GlobalDataAccessor.Instance.OracleDA;
//Setup ref cursor array
List <PairType <string, string> > refCursors = new List <PairType <string, string> >();
//Add general ref cursors
refCursors.Add(new PairType <string, string>("c_mlist", "mdse"));
//Create output data set names
bool retVal = false;
try
{
retVal = dA.issueSqlStoredProcCommand(
"ccsowner", SQL_PACKAGE, "get_items2assign",
inParams, refCursors, "o_error_code", "o_error_desc",
out outputDataSet);
}
catch (OracleException oEx)
{
errorCode = "get_items2assign";
errorText = "Invocation of GetAssignableItems stored proc failed";
BasicExceptionHandler.Instance.AddException("OracleException thrown when invoking GetAssignableItems stored proc", oEx);
outputDataSet = null;
return(false);
}
//See if retVal is false
if (retVal == false)
{
errorCode = dA.ErrorCode;
errorText = dA.ErrorDescription;
return(false);
}
if (outputDataSet != null)
{
if (outputDataSet.Tables != null && outputDataSet.Tables.Count > 0)
{
//Get information and add to List
if (outputDataSet.Tables.Contains("MDSE"))
{
items = outputDataSet.Tables["MDSE"];
}
errorCode = "0";
errorText = string.Empty;
return(true);
}
else
{
errorCode = "1";
errorText = "No Data Found";
return(true);
}
}
errorCode = "GetAssignableItems";
errorText = "Operation failed";
return(false);
}
/// <summary>
/// Call to trigger the auto store credit forfeiture stored procedure
/// This will be called by the end of day process
/// </summary>
/// <param name="oda"></param>
/// <param name="storeNumber"></param>
/// <param name="currentDate"></param>
/// <param name="userId"></param>
/// <param name="errorCode"></param>
/// <param name="errorText"></param>
/// <returns></returns>
public static bool AutoForfeitStoreCredit(
OracleDataAccessor oda,
string storeNumber,
string currentDate,
string userId,
out string errorCode,
out string errorText)
{
//Check if auto store credit forfeiture is allowed in the store
//if not allowerd return
bool autoSCForfeiture = new BusinessRulesProcedures(GlobalDataAccessor.Instance.DesktopSession).IsAutoForfeitStoreCreditAllowed(GlobalDataAccessor.Instance.CurrentSiteId);
if (!autoSCForfeiture)
{
errorCode = "1";
errorText = "Auto store credit forfeiture is not allowed in this store";
return(false);
}
if (oda == null)
{
errorCode = "AutoForfeitStoreCreditFailed";
errorText = "Invalid desktop session or data accessor";
return(false);
}
//Get the sale store credit duration from business rule
SiteId site = GlobalDataAccessor.Instance.CurrentSiteId;
int saleStoreCreditDuration = new BusinessRulesProcedures(GlobalDataAccessor.Instance.DesktopSession).GetSaleStoreCreditDuration(site);
//Get layaway store credit duration from business rule
int layStoreCreditDuration = new BusinessRulesProcedures(GlobalDataAccessor.Instance.DesktopSession).GetLayStoreCreditDuration(site);
List <OracleProcParam> inParams = new List <OracleProcParam>();
inParams.Add(new OracleProcParam("p_store_number", storeNumber));
inParams.Add(new OracleProcParam("p_sale_sc_duration", saleStoreCreditDuration));
inParams.Add(new OracleProcParam("p_lay_sc_duration", layStoreCreditDuration));
inParams.Add(new OracleProcParam("p_forfeiture_date", currentDate));
inParams.Add(new OracleProcParam("p_user_id", userId));
if (inParams.Count > 0)
{
List <PairType <string, string> > refCursArr = new List <PairType <string, string> >();
bool retVal = false;
try
{
DataSet outputDataSet;
retVal = oda.issueSqlStoredProcCommand(
"ccsowner", "pawn_retail",
"auto_forfeiture_storecredit", inParams,
refCursArr, "o_return_code",
"o_return_text",
out outputDataSet);
}
catch (Exception oEx)
{
BasicExceptionHandler.Instance.AddException("Calling auto_forfeiture_storecredit stored procedure", oEx);
errorCode = "Auto Forfeiture Store Credit";
errorText = "Exception: " + oEx.Message;
return(false);
}
if (retVal == false)
{
errorCode = oda.ErrorCode;
errorText = oda.ErrorDescription;
return(false);
}
}
else
{
errorCode = "auto_forfeiture_storecreditFailed";
errorText = "No Valid input parameters given";
return(false);
}
errorCode = "0";
errorText = "Success";
return(true);
}
private void Process_MDSE_Locks()
{
OracleDataAccessor DA = GlobalDataAccessor.Instance.OracleDA;
string RecordType = cbRecordType.Text;
string ShopNbr = txtShop.Text.PadLeft(5, '0');
string ICNNbr = txtDetail.Text;
string LblSearchName;
string msg;
Point mdsePoint = new Point();
bool RetVal;
DialogResult frmResult;
Int32 OutRows;
mdsePoint.X = 79;
mdsePoint.Y = 162;
LblSearchName = "Shop / ICN:";
//create input parameters
List <OracleProcParam> iParams = new List <OracleProcParam>();
iParams.Add(new OracleProcParam("p_shop_number", ShopNbr));
iParams.Add(new OracleProcParam("p_icn_nbr", ICNNbr));
//setup ref cursor
var RefCursor = new List <PairType <string, string> >();
RefCursor.Add(new PairType <string, string>("o_mdse_data", "OUT_DATA"));
//execute stored procedure
DataSet OutDataSet;
try
{
RetVal = DA.issueSqlStoredProcCommand(
"ccsowner", "pawn_support_locks", "get_mdse_record", iParams,
RefCursor, "o_return_code", "o_return_text", out OutDataSet);
}
catch (OracleException oEx)
{
msg = string.Format("Invocation of get_mdse_record stored proc failed{0}{1}{0}{2}",
Environment.NewLine, oEx.Message, Smsg);
MessageBox.Show(msg, "Error");
return;
}
if (RetVal == false)
{
msg = DA.ErrorDescription;
MessageBox.Show(msg, "Error");
return;
}
if (OutDataSet != null)
{
if (OutDataSet.Tables != null)
{
if (OutDataSet.Tables.Count > 0)
{
DataTable mdseTable = OutDataSet.Tables["OUT_DATA"];
if (mdseTable != null && mdseTable.IsInitialized &&
mdseTable.Rows != null && mdseTable.Rows.Count > 0)
{
// add a full icn column to datatable and populate
mdseTable.Columns.Add("FullICN", typeof(string));
foreach (DataRow x in mdseTable.Rows)
{
string ICN_STORE = x[0].ToString();
string ICN_YEAR = x[1].ToString();
string ICN_DOC = x[2].ToString();
string ICN_DOC_TYPE = x[3].ToString();
string ICN_ITEM = x[4].ToString();
string ICN_SUBITEM = x[5].ToString();
string FullICN;
FullICN = "";
FullICN = FullICN + ICN_STORE.PadLeft(5, '0');
FullICN += ICN_YEAR;
FullICN += ICN_DOC.PadLeft(6, '0');
FullICN += ICN_DOC_TYPE;
FullICN += ICN_ITEM.PadLeft(3, '0');
FullICN += ICN_SUBITEM.PadLeft(2, '0');
x[11] = FullICN;
}
//find out how many rows were returned
OutRows = mdseTable.Rows.Count;
if (OutRows == 1)
{
//process for a single record
//make sure record has a temp lock
//foreach (DataRow dr in LocData.Tables[0].Rows)
string Locktype;
DataRow dr = mdseTable.Rows[0];
Locktype = dr[10].ToString();
string FullICN = dr[11].ToString();
if (Locktype != string.Empty)
{
var frmClearStatus2 = new FrmClearTempStatus2(Locktype, RecordType, LblSearchName,
mdsePoint, ShopNbr, FullICN);
frmResult = frmClearStatus2.ShowDialog();
if (frmResult == DialogResult.Cancel)
{
this.Close();
}
}
else
{
MessageBox.Show("No temp lock found.", "Warning",
MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
}
else
{
//process for multiple records
var frmDupMerch = new FrmDupMerch(mdseTable);
frmResult = frmDupMerch.ShowDialog();
if (frmResult == DialogResult.OK)
{
var frmClearStatus2 = new FrmClearTempStatus2(frmDupMerch.LockDescription, RecordType, LblSearchName,
mdsePoint, ShopNbr, frmDupMerch.FullICN);
frmResult = frmClearStatus2.ShowDialog();
if (frmResult == DialogResult.Cancel)
{
this.Close();
}
}
}
}
else
{
MessageBox.Show("Error fetching data.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
else
{
// no records were returned
MessageBox.Show("No record found matching this search criteria.", "No Data Found",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
else
{
MessageBox.Show("Error fetching data.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
else
{
MessageBox.Show("Error fetching data.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
public static bool GetTransferInTickets(DesktopSession cds, List <TransferVO> transfers, out string errorCode, out string errorText)
{
if (transfers == null)
{
throw new ArgumentNullException("transfers");
}
//Initialize output vars
errorCode = string.Empty;
errorText = string.Empty;
OracleDataAccessor dA = GlobalDataAccessor.Instance.OracleDA;
//Create parameter list
List <OracleProcParam> oParams = new List <OracleProcParam>();
oParams.Add(new OracleProcParam("p_store_number", cds.CurrentSiteId.StoreNumber));
oParams.Add(new OracleProcParam("p_user_id", cds.FullUserName));
//Setup ref cursor array
List <PairType <string, string> > refCursors = new List <PairType <string, string> >();
//Add general ref cursors
refCursors.Add(new PairType <string, string>("o_ti_tickets", "ti_tickets_ref_cursor"));
//Make stored proc call
bool retVal;
DataSet outputDataSet;
try
{
retVal = GlobalDataAccessor.Instance.OracleDA.issueSqlStoredProcCommand("ccsowner",
"transfers", "retrieve_TI_tickets", oParams, refCursors, "o_return_code",
"o_return_text", out outputDataSet);
errorCode = dA.ErrorCode;
errorText = dA.ErrorDescription;
}
catch (OracleException oEx)
{
BasicExceptionHandler.Instance.AddException("ExecuteGetTransferInTickets Failed", oEx);
errorCode = "ExecuteGetTransferInTicketsFailed";
errorText = "OracleException thrown: " + oEx.Message;
return(false);
}
if (retVal == false)
{
BasicExceptionHandler.Instance.AddException("ExecuteGetTransferInTickets Failed: return value is false", new ApplicationException());
errorCode = dA.ErrorCode + " --- ExecuteGetTransferInTickets";
errorText = dA.ErrorDescription + " -- Return value is false";
return(false);
}
if (outputDataSet != null && outputDataSet.Tables.Count == 1)
{
if (outputDataSet.Tables[0].TableName == "ti_tickets_ref_cursor")
{
foreach (DataRow dr in outputDataSet.Tables[0].Rows)
{
TransferVO transfer = new TransferVO();
transfer.Amount = Utilities.GetDecimalValue(dr["amount"], 0);
transfer.Carrier = Utilities.GetStringValue(dr["carrier"]);
transfer.NumberOfItems = Utilities.GetIntegerValue(dr["LastLine"], 0);
transfer.Status = Utilities.ParseEnum <TransferStatus>(Utilities.GetStringValue(dr["status"], string.Empty), TransferStatus.TI);
transfer.StatusDate = Utilities.GetDateTimeValue(dr["statusdate"]);
transfer.StoreNickName = Utilities.GetStringValue(dr["storenickname"]);
transfer.OriginalStoreNumber = Utilities.GetStringValue(dr["storenumber"]);
transfer.TempStatus = Utilities.ParseEnum <TransferTempStatus>(Utilities.GetStringValue(dr["tempstatus"], string.Empty), TransferTempStatus.NONE);
transfer.TransferTicketNumber = Utilities.GetIntegerValue(dr["transticketnum"], 0);
transfer.TransferType = Utilities.ParseEnum <TransferTypes>(Utilities.GetStringValue(dr["trantype"], string.Empty), TransferTypes.STORETOSTORE);
transfer.TransferSource = Utilities.ParseEnum <TransferSource>(Utilities.GetStringValue(dr["ticket_type"], string.Empty), TransferSource.NONE);
transfer.RejectReason = Utilities.GetStringValue(dr["reject_reason"], string.Empty);
transfer.DestinationStoreNumber = Utilities.GetStringValue(dr["entitynumber"], string.Empty);
if (transfer.TransferSource == TransferSource.NONE)
{
continue;
}
transfers.Add(transfer);
}
}
errorCode = "0";
errorText = "Success";
return(true);
}
return(false);
}
public static bool ExecuteGetMetalStones(
string PMetal_File_Name,
string Stones_File_Name,
out List <PMetalInfo> PMETAL_INFO,
out List <StoneInfo> STONES_INFO,
out string errorCode,
out string errorText)
{
//Set default output values
errorCode = string.Empty;
errorText = string.Empty;
PMETAL_INFO = new List <PMetalInfo>();
STONES_INFO = new List <StoneInfo>();
DataSet outputDataSet = null;
//Verify that the accessor is valid
if (GlobalDataAccessor.Instance == null ||
GlobalDataAccessor.Instance.OracleDA == null)
{
errorCode = "GetMetalStonesFailed";
errorText = "Invalid desktop session or data accessor instance";
BasicExceptionHandler.Instance.AddException("GetMetalStonesFailed", new ApplicationException("Cannot execute the GetMetalStones retrieval stored procedure"));
return(false);
}
//Get data accessor object
OracleDataAccessor dA = GlobalDataAccessor.Instance.OracleDA;
//Create input list
List <OracleProcParam> inParams = new List <OracleProcParam>();
//Add cat pointer
inParams.Add(new OracleProcParam("pmetalfile", PMetal_File_Name));
inParams.Add(new OracleProcParam("stonefile", Stones_File_Name));
//Setup ref cursor array
List <PairType <string, string> > refCursors = new List <PairType <string, string> >();
//Add general ref cursors
refCursors.Add(new PairType <string, string>("c_pmlist", PMETAL));
refCursors.Add(new PairType <string, string>("c_slist", STONES));
//Create output data set names
bool retVal = false;
try
{
retVal = dA.issueSqlStoredProcCommand(
"PROKNOW", "proknow_procs", "get_metal_stone_values",
inParams, refCursors, "o_error_code", "o_error_desc",
out outputDataSet);
}
catch (OracleException oEx)
{
errorCode = "GetMetalStonesFailed";
errorText = "Invocation of Get_Metal_Stone_Values stored proc failed";
BasicExceptionHandler.Instance.AddException("OracleException thrown when invoking Get_Metal_Stone_Values stored proc", oEx);
outputDataSet = null;
return(false);
}
//See if retVal is false
if (retVal == false)
{
errorCode = dA.ErrorCode;
errorText = dA.ErrorDescription;
return(false);
}
if (outputDataSet != null)
{
if (outputDataSet.Tables != null && outputDataSet.Tables.Count > 0)
{
//Get Metal information and add to List<Metal>
if (outputDataSet.Tables.Contains(PMETAL))
{
PMETAL_INFO = new List <PMetalInfo>();
foreach (DataRow dataRow in outputDataSet.Tables[PMETAL].Rows)
{
PMetalInfo metalInfo = new PMetalInfo();
metalInfo.Record_Type = Utilities.GetStringValue(dataRow["RECORD_TYPE"], "");
metalInfo.Category = Utilities.GetIntegerValue(dataRow["CAT_CODE"], 0);
metalInfo.Class = Utilities.GetStringValue(dataRow["CLASS"], "");
metalInfo.Metal = Utilities.GetStringValue(dataRow["METAL"], "");
metalInfo.Karats = Utilities.GetIntegerValue(dataRow["KARATS"], 0);
metalInfo.Gram_Low = Utilities.GetDecimalValue(dataRow["GRAM_LOW"], 0);
metalInfo.Gram_High = Utilities.GetDecimalValue(dataRow["GRAM_HIGH"], 0);
metalInfo.Loan_Buy_Per_Gram = Utilities.GetDecimalValue(dataRow["LOAN_BUY_PER_GRAM"], 0);
metalInfo.Retail_Per_Gram = Utilities.GetDecimalValue(dataRow["RETAIL_PER_GRAM"], 0);
PMETAL_INFO.Add(metalInfo);
}
}
// Get Stone information adn add to List<Stones>
if (outputDataSet.Tables.Contains(STONES))
{
STONES_INFO = new List <StoneInfo>();
foreach (DataRow dataRow in outputDataSet.Tables[STONES].Rows)
{
StoneInfo stoneInfo = new StoneInfo();
stoneInfo.Record_Type = Utilities.GetStringValue(dataRow["RECORDTYPE"], "");
stoneInfo.Min_Points = Utilities.GetDecimalValue(dataRow["MIN_POINTS"], 0);
stoneInfo.Max_Points = Utilities.GetDecimalValue(dataRow["MAX_POINTS"], 0);
stoneInfo.Clarity = Utilities.GetIntegerValue(dataRow["CLARITY"], 0);
stoneInfo.Color = Utilities.GetIntegerValue(dataRow["COLOR"], 0);
stoneInfo.Loan = Utilities.GetDecimalValue(dataRow["LOAN"], 0);
stoneInfo.Purchase = Utilities.GetDecimalValue(dataRow["PURCHASE"], 0);
stoneInfo.Retail = Utilities.GetDecimalValue(dataRow["RETAIL"], 0);
STONES_INFO.Add(stoneInfo);
}
}
errorCode = "0";
errorText = string.Empty;
return(true);
}
}
errorCode = "GetMetalStonesFailed";
errorText = "Operation failed";
return(false);
}
/// <summary>
/// Initializes a connection to the PawnSec database
/// </summary>
/// <param name="dHost"></param>
/// <param name="dPassword"></param>
/// <param name="dPort"></param>
/// <param name="dSchema"></param>
/// <param name="dService"></param>
/// <param name="dUser"></param>
public void InitializeConnection(
string dHost, string dPassword,
string dPort, string dSchema,
string dService, string dUser)
{
if (this.pawnSecLogger.IsLogDebug)
{
this.pawnSecLogger.logMessage(
LogLevel.DEBUG,
this,
"InitializeConnection({0},{1})...",
dHost,
dPort);
}
// check to see if connection is already established);
if (this.state == PawnSecState.CONNECTED)
{
this.pawnSecLogger.logMessage(LogLevel.WARN,
this, "- Already connected");
return;
}
// check all inputs
if (string.IsNullOrEmpty(dHost) ||
string.IsNullOrEmpty(dPassword) ||
string.IsNullOrEmpty(dPort) ||
string.IsNullOrEmpty(dSchema) ||
string.IsNullOrEmpty(dService) ||
string.IsNullOrEmpty(dUser))
{
this.pawnSecLogger.logMessage(LogLevel.ERROR,
this, "- Invalid inputs");
return;
}
// assign db related class members
this.dbHost = dHost;
this.dbPassword = dPassword;
this.dbPort = dPort;
this.dbSchema = dSchema;
this.dbService = dService;
this.dbUser = dUser;
// establish connection to database
try
{
this.dataAccessor = new OracleDataAccessor(
this.dbUser,
this.dbPassword,
this.dbHost,
this.dbPort,
this.dbService,
this.dbSchema,
FETCH_SZ_MX,
true,
true,
KEY
);
this.dataAccessor.AuxLogger = this.pawnSecLogger;
if (this.dataAccessor.Initialized)
{
this.state = PawnSecState.CONNECTED;
this.pawnSecLogger.logMessage(LogLevel.INFO, this, "- PawnSec Oracle data accessor connected successfully.");
}
else
{
this.pawnSecLogger.logMessage(LogLevel.FATAL, this, "- PawnSec Oracle data accessor is not initialized. Cannot interact with the database. Exiting!");
this.Close();
return;
}
}
catch (Exception eX)
{
this.pawnSecLogger.logMessage(LogLevel.FATAL, this, "- Could not connect to the PawnSec database: {0}", eX.Message);
this.Disconnect();
}
finally
{
if (this.state == PawnSecState.DISCONNECTED)
{
this.pawnSecLogger.logMessage(LogLevel.FATAL, this, "- Still disconneted from database.");
this.pawnSecLogger.Dispose();
}
}
}
/*__________________________________________________________________________________________*/
public bool GetChangeCustomerStatusDBData(
string customerNumber,
string StatusChanged,
string CurrentStatus,
string ReasonChanged,
string CurrentReason,
out string errorCode,
out string errorMessage) // out string errorCode,
{ // uncomment the code below and comment the DbReturnBool and errorMesg WCM
OracleDataAccessor oDa = GlobalDataAccessor.Instance.OracleDA;
//customerStatusData = null;
errorCode = string.Empty;
errorMessage = string.Empty;
//if (DesktopSession == null ||
// GlobalDataAccessor.Instance.OracleDA == null)
//{
// //customer = null;
// //errorCode = "getcustomerStatsDataFailed";
// errorMesg = "Invalid desktop session or data accessor";
// return (false);
//}
List <OracleProcParam> inParams = new List <OracleProcParam>();
inParams.Add(new OracleProcParam("p_customer_number", customerNumber));
inParams.Add(new OracleProcParam("p_status_changed", StatusChanged));
inParams.Add(new OracleProcParam("p_current_status", CurrentStatus));
inParams.Add(new OracleProcParam("p_reason_changed", ReasonChanged));
inParams.Add(new OracleProcParam("p_current_reason", CurrentReason));
bool retVal = false;
List <PairType <string, string> > refCursArr = new List <PairType <string, string> >();
//refCursArr.Add(new PairType<string, string>("o_customer_change_stats", CUSTOMER_CHANGE_STATUS));
DataSet outputDataSet;
try
{
DesktopSession.beginTransactionBlock();
retVal = oDa.issueSqlStoredProcCommand(
"ccsowner",
"pawn_support_cust_procs",
"update_customer_status",
inParams,
refCursArr,
"o_return_code",
"o_return_text", out outputDataSet);
}
catch (Exception oEx)
{
BasicExceptionHandler.Instance.AddException("Calling GetCustomerStatusDBData stored procedure", oEx);
errorCode = "GetCustomerChangeStatus";
errorMessage = "Exception: " + oEx.Message;
//return (false);
}
if (retVal == false)
{
DesktopSession.endTransactionBlock(EndTransactionType.ROLLBACK);
return(false);
}
else
{
DesktopSession.endTransactionBlock(EndTransactionType.COMMIT);
return(true);
}
}
/*__________________________________________________________________________________________*/
private bool internalExecuteSupportAppLookupCustomer(
List <OracleProcParam> inputParams,
out DataTable customer,
out DataTable customerIdentities,
out DataTable customerContacts,
out DataTable customerAddress,
out DataTable customerEmails,
out DataTable customerNotes,
out DataTable customerStoreCredit,
out string errorCode,
out string errorMesg)
{
//Get data accessor object
OracleDataAccessor dA = GlobalDataAccessor.Instance.OracleDA;
//Setup output defaults
customer = null;
customerIdentities = null;
customerContacts = null;
customerAddress = null;
customerEmails = null;
customerNotes = null;
customerStoreCredit = null;
errorCode = string.Empty;
errorMesg = string.Empty;
if (CollectionUtilities.isEmpty(inputParams))
{
errorCode = "LookupCustomerFailed";
errorMesg = "No input parameters specified";
}
List <PairType <string, string> > refCursArr = new List <PairType <string, string> >();
refCursArr.Add(new PairType <string, string>("r_cust_list", CUSTOMER_DATA_NAME));
refCursArr.Add(new PairType <string, string>("r_phone_list", CUSTOMER_PHONE_DATA_NAME));
refCursArr.Add(new PairType <string, string>("r_ident_list", CUSTOMER_IDENTITY_DATA_NAME));
refCursArr.Add(new PairType <string, string>("r_address_list", CUSTOMER_ADDRESS_DATA_NAME));
refCursArr.Add(new PairType <string, string>("r_email_list", CUSTOMER_EMAIL_DATA_NAME));
refCursArr.Add(new PairType <string, string>("r_notes_list", CUSTOMER_NOTES_DATA_NAME));
refCursArr.Add(new PairType <string, string>("r_store_credit_list", CUSTOMER_STORE_CREDIT));
DataSet outputDataSet;
bool retVal = false;
try
{
// EDW - OK change
retVal = dA.issueSqlStoredProcCommand(
"ccsowner", "pawn_support_cust_procs",
"get_cust_details", inputParams,
refCursArr, "o_return_code",
"o_return_text",
out outputDataSet);
}
catch (Exception oEx)
{
BasicExceptionHandler.Instance.AddException("Calling get_cust_details stored procedure", oEx);
errorCode = "GetCustDetails";
errorMesg = "Exception: " + oEx.Message;
return(false);
}
if (retVal == false)
{
errorCode = dA.ErrorCode;
errorMesg = dA.ErrorDescription;
return(false);
}
else
{
if (outputDataSet != null && outputDataSet.IsInitialized)
{
if (outputDataSet.Tables != null && outputDataSet.Tables.Count > 0)
{
customer = outputDataSet.Tables[CUSTOMER_DATA_NAME];
customerContacts = outputDataSet.Tables[CUSTOMER_PHONE_DATA_NAME];
customerIdentities = outputDataSet.Tables[CUSTOMER_IDENTITY_DATA_NAME];
customerAddress = outputDataSet.Tables[CUSTOMER_ADDRESS_DATA_NAME];
customerEmails = outputDataSet.Tables[CUSTOMER_EMAIL_DATA_NAME];
customerNotes = outputDataSet.Tables[CUSTOMER_NOTES_DATA_NAME];
customerStoreCredit = outputDataSet.Tables[CUSTOMER_STORE_CREDIT];
return(true);
}
}
}
errorCode = "LookupCustomerFail";
errorMesg = "Operation failed";
return(false);
}
// WCM 4/11/12 Written and not tested and not in use
/*__________________________________________________________________________________________*/
public bool GetPersonalInformationHistoryDBData(
OracleDataAccessor oDa,
string customerNumber,
out DataSet personalInformationData,
out string errorCode,
out string errorMesg)
{
personalInformationData = null;
errorCode = string.Empty;
errorMesg = string.Empty;
if (oDa == null)
{
errorCode = "personalInformationDataFailed";
errorMesg = "Invalid desktop session or data accessor";
return(false);
}
// Boolean StatusChanged,
//string CurrentStatus,
//Boolean ReasonChanged,
//string CurrentReason,
List <OracleProcParam> inParams = new List <OracleProcParam>();
inParams.Add(new OracleProcParam("p_customer_number", customerNumber));
if (inParams.Count > 0)
{
List <PairType <string, string> > refCursArr = new List <PairType <string, string> >();
refCursArr.Add(new PairType <string, string>("o_personal_information_history", PERSONAL_INFORMATION_HISTORY));
DataSet outputDataSet;
bool retVal;
try
{
retVal = oDa.issueSqlStoredProcCommand(
"ccsowner", "pawn_support_cust_procs",
"get_Customer_Stats", inParams,
refCursArr, "o_return_code",
"o_return_text",
out outputDataSet);
}
catch (Exception oEx)
{
BasicExceptionHandler.Instance.AddException("Calling GetCustomerStatusDBData stored procedure", oEx);
errorCode = "personalInformationData";
errorMesg = "Exception: " + oEx.Message;
return(false);
}
if (retVal == false)
{
errorCode = oDa.ErrorCode;
errorMesg = oDa.ErrorDescription;
return(false);
}
if (outputDataSet != null)
{
errorCode = "0";
personalInformationData = outputDataSet;
errorMesg = string.Empty;
return(true);
}
errorCode = "personalInformationDataFailedFailed";
errorMesg = "No data returned for stats for the customer";
return(false);
}
errorCode = "GetCustomerStatusDBDataFailedFailed";
errorMesg = "No valid input parameters given";
return(false);
}
/// <summary>
///
/// </summary>
/// <param name="storeNumber"></param>
/// <param name="userId"></param>
/// <param name="isCredit"></param>
/// <param name="cashDrawer"></param>
/// <param name="amount"></param>
/// <param name="receiptNumber"></param>
/// <param name="receiptNumber"></param>
/// <param name="workstation"></param>
/// <param name="date"></param>
/// <param name="errorCode"></param>
/// <param name="errorText"></param>
/// <returns></returns>
public static bool ExecuteUpdateTeller(
string storeNumber,
string userId,
bool isCredit,
string cashDrawer,
decimal amount,
string receiptNumber,
string workstation,
DateTime date,
out string errorCode,
out string errorText)
{
//Initialize outputs
errorCode = string.Empty;
errorText = string.Empty;
//Validate data accessor
if (GlobalDataAccessor.Instance == null ||
!GlobalDataAccessor.Instance.IsDataAccessorValid())
{
BasicExceptionHandler.Instance.AddException("ExecuteUpdateTellerFailed", new ApplicationException("Data accessor class is invalid"));
errorCode = "ExecuteUpdateTellerFailed";
errorText = "Invalid data accessor";
return(false);
}
OracleDataAccessor dA = GlobalDataAccessor.Instance.OracleDA;
//Prepare input parameter list
var iParams = new List <OracleProcParam>
{
new OracleProcParam("p_store_number", storeNumber),
new OracleProcParam("p_user_name", userId),
new OracleProcParam("p_is_credit", ((isCredit) ? "T" : "F")),
new OracleProcParam("p_cash_drawer", cashDrawer),
new OracleProcParam("p_amount", amount),
new OracleProcParam("p_loan_number", receiptNumber),
new OracleProcParam("p_workstation", workstation),
new OracleProcParam("p_sys_date", date)
};
//Make stored proc call
bool retVal = false;
try
{
DataSet outDSet;
retVal = dA.issueSqlStoredProcCommand("ccsowner",
"pawn_store_procs", "update_teller_new_pawn_loan",
iParams, null, "o_error_code",
"o_error_desc", out outDSet);
}
catch (OracleException oEx)
{
BasicExceptionHandler.Instance.AddException("ExecuteUpdateTeller Failed", oEx);
errorCode = "ExecuteUpdateTellerFailed";
errorText = "OracleException thrown: " + oEx.Message;
return(false);
}
if (retVal == false)
{
BasicExceptionHandler.Instance.AddException("ExecuteUpdateTeller Failed: return value is false", new ApplicationException(dA.ErrorCode + ":" + dA.ErrorDescription));
errorCode = "ExecuteUpdateTellerFailed: " + dA.ErrorCode + ": " + dA.ErrorDescription;
errorText = "Return value is false";
return(false);
}
errorCode = "0";
errorText = "Success";
return(true);
}
/// <summary>
/// Updates the teller tables for a void transaction
/// </summary>
/// <param name="receiptDtNumber"></param>
/// <param name="storeNumber"></param>
/// <param name="userId"></param>
/// <param name="workstation"></param>
/// <param name="refNumber"></param>
/// <param name="tranDate"></param>
/// <param name="errorCode"></param>
/// <param name="errorText"></param>
/// <returns></returns>
public static bool UpdateTellerOnVoid(
Int64 receiptDtNumber,
string storeNumber,
string userId,
string workstation,
string refNumber,
string tranDate,
out string errorCode,
out string errorText
)
{
//Initialize outputs
errorCode = string.Empty;
errorText = string.Empty;
//Validate data accessor
if (GlobalDataAccessor.Instance == null ||
GlobalDataAccessor.Instance.OracleDA == null ||
GlobalDataAccessor.Instance.OracleDA.Initialized == false)
{
BasicExceptionHandler.Instance.AddException("ExecuteUpdateTellerOnVoidFailed", new ApplicationException("Data accessor class is invalid"));
errorCode = "ExecuteUpdateTellerOnVoidFailed";
errorText = "Invalid data accessor";
return(false);
}
OracleDataAccessor dA = GlobalDataAccessor.Instance.OracleDA;
//Prepare input parameter list
var iParams = new List <OracleProcParam>
{
new OracleProcParam("p_receipt_detail_number", receiptDtNumber),
new OracleProcParam("p_store_number", storeNumber),
new OracleProcParam("p_user_name", userId),
new OracleProcParam("p_workstation", workstation),
new OracleProcParam("p_loan_number", refNumber),
new OracleProcParam("p_transaction_date", tranDate)
};
//Make stored proc call
bool retVal = false;
try
{
DataSet outDSet;
retVal = dA.issueSqlStoredProcCommand("ccsowner",
"pawn_store_procs", "update_teller_for_void",
iParams, null, "o_error_code",
"o_error_desc", out outDSet);
}
catch (OracleException oEx)
{
BasicExceptionHandler.Instance.AddException("ExecuteUpdateTellerForVoid Failed", oEx);
errorCode = "ExecuteUpdateTellerForVoidFailed";
errorText = "OracleException thrown: " + oEx.Message;
return(false);
}
if (retVal == false)
{
BasicExceptionHandler.Instance.AddException("ExecuteUpdateTellerForVOid Failed: return value is false", new ApplicationException(dA.ErrorCode + ":" + dA.ErrorDescription));
errorCode = "ExecuteUpdateTellerForVoidFailed: " + dA.ErrorCode + ": " + dA.ErrorDescription;
errorText = "Return value is false";
return(false);
}
errorCode = "0";
errorText = "Success";
return(true);
}
/// <summary>
/// Retrieves PFI Mailer data based on selected PFI Mailer option and parameters
/// </summary>
/// <param name="dataAccessor"></param>
/// <param name="pfiMailerOption"></param>
/// <param name="pfiMailerObjects"></param>
/// <param name="errorCode"></param>
/// <param name="errorText"></param>
/// <param name="pfiMailerAdjustmentDays"></param>
/// <param name="startTicketNumber"></param>
/// <param name="endTicketNumber"></param>
/// <param name="pfiDate"></param>
/// <returns></returns>
public static bool ExecuteGetPFIMailerData(
OracleDataAccessor dataAccessor,
PFIMailerOption pfiMailerOption,
out List <ReportObject.PFIMailer> pfiMailerObjects,
out string errorCode,
out string errorText,
int pfiMailerAdjustmentDays = 0,
int startTicketNumber = 0,
int endTicketNumber = 0,
DateTime pfiDate = new DateTime()
)
{
// initialize error output variables
pfiMailerObjects = new List <ReportObject.PFIMailer>();
errorCode = String.Empty;
errorText = String.Empty;
// Ensure the data accessor is valid
if (dataAccessor == null || !dataAccessor.Initialized)
{
BasicExceptionHandler.Instance.AddException("ExecuteGetPFIMailerData Failed",
new ApplicationException("ExecuteGetPFIMailerData Failed: Data accessor instance is invalid"));
return(false);
}
// Set up input variables
var inParams = new List <OracleProcParam>
{
new OracleProcParam("p_pfi_date", pfiDate.ToShortDateString()),
new OracleProcParam("p_store_number", GlobalDataAccessor.Instance.DesktopSession.CurrentSiteId.StoreNumber),
new OracleProcParam("p_pfi_mailer_adjustment_days", pfiMailerAdjustmentDays),
new OracleProcParam("p_startTicketNumber", startTicketNumber),
new OracleProcParam("p_endTicketNumber", endTicketNumber),
new OracleProcParam("p_pfiMailerOption", (int)pfiMailerOption)
};
// Set up output
DataSet outputDataSet;
var retVal = false;
var refCursArr = new List <PairType <string, string> >
{
new PairType <string, string>("pfi_mailer_info", "o_pfi_mailer_info")
};
try
{
retVal = dataAccessor.issueSqlStoredProcCommand(
"ccsowner", "pawn_mdse_procs",
"get_pfi_mailer_data", inParams,
refCursArr, "o_return_code",
"o_return_text",
out outputDataSet);
errorCode = dataAccessor.ErrorCode;
errorText = dataAccessor.ErrorDescription;
}
catch (OracleException oEx)
{
BasicExceptionHandler.Instance.AddException("ExecuteGetPFIMailerData Failed", oEx);
errorCode = " --- ExecuteGetPFIMailerData";
errorText = " --- OracleException thrown: " + oEx.Message;
return(false);
}
if (retVal == false)
{
BasicExceptionHandler.Instance.AddException("ExecuteGetPFIMailerData Failed: return value is false", new ApplicationException());
errorCode = dataAccessor.ErrorCode + " -- ExecuteGetPFIMailerData";
errorText = dataAccessor.ErrorDescription + " -- Return value is false";
return(false);
}
if (outputDataSet == null || !outputDataSet.IsInitialized ||
(outputDataSet.Tables.Count <= 0))
{
return(false);
}
pfiMailerObjects = new List <ReportObject.PFIMailer>();
ReportObject.PFIMailer pfiMailerObject;
foreach (DataRow pfiMailerDataRow in outputDataSet.Tables[0].Rows)
{
var pfiMailerFee = new BusinessRulesProcedures(GlobalDataAccessor.Instance.DesktopSession).GetPFIMailerFee(GlobalDataAccessor.Instance.CurrentSiteId);
pfiMailerObject = new ReportObject.PFIMailer();
pfiMailerObject.ticketNumber = Utilities.GetIntegerValue(pfiMailerDataRow["ticket_number"]);
pfiMailerObject.customerNumber = Utilities.GetIntegerValue(pfiMailerDataRow["customer_number"]);
pfiMailerObject.originalPFINote = Utilities.GetDateTimeValue(pfiMailerDataRow["PFI_NOTE"]);
pfiMailerObject.printNotice = pfiMailerDataRow["PRINT_NOTICE"].ToString();
pfiMailerObject.customerName = pfiMailerDataRow["customer_name"].ToString();
pfiMailerObject.customerId = pfiMailerDataRow["customer_id"].ToString();
pfiMailerObject.customerAddress = pfiMailerDataRow["customer_address"].ToString();
pfiMailerObject.customerCity = pfiMailerDataRow["customer_city"].ToString();
pfiMailerObject.customerState = pfiMailerDataRow["customer_state"].ToString();
pfiMailerObject.customerZipCode = pfiMailerDataRow["customer_zipCode"].ToString();
pfiMailerObject.pfiEligibleDate = Utilities.GetDateTimeValue(pfiMailerDataRow["pfi_eligible_date"]);
pfiMailerObject.storeName = pfiMailerDataRow["store_name"].ToString();
pfiMailerObject.storeNumber = Utilities.GetIntegerValue(pfiMailerDataRow["store_number"]);
pfiMailerObject.storeAddress = pfiMailerDataRow["store_address"].ToString();
pfiMailerObject.storeCity = pfiMailerDataRow["store_city"].ToString();
pfiMailerObject.storeState = pfiMailerDataRow["store_state"].ToString();
pfiMailerObject.storeZipCode = pfiMailerDataRow["store_zipCode"].ToString();
pfiMailerObject.storePhone = pfiMailerDataRow["store_phone"].ToString();
pfiMailerObject.pfiMailerFee = pfiMailerFee;
pfiMailerObjects.Add(pfiMailerObject);
}
// method completed successfully
return(true);
}
/// <summary>
/// Closes the connection and disposes the class
/// </summary>
public void Close()
{
this.pawnSecLogger.logMessage(LogLevel.INFO, this, "Closing security accessor connection...");
this.Disconnect();
this.dataAccessor = null;
}
private void Process_Pawn_Locks()
{
OracleDataAccessor DA = GlobalDataAccessor.Instance.OracleDA;
string RecordType = cbRecordType.Text;
string ShopNbr = txtShop.Text.PadLeft(5, '0');
string TicketNbr = txtDetail.Text;
Point mdsePoint = new Point();
string LblSearchName;
bool RetVal;
string msg;
string TempStatus;
string ErrorCode;
DialogResult frmResult;
Int32 nTicketNbr;
mdsePoint.X = 29;
mdsePoint.Y = 162;
LblSearchName = "Shop / Ticket Number:";
try
{
nTicketNbr = Convert.ToInt32(TicketNbr);
}
catch
{
MessageBox.Show("Invalid Ticket Number");
return;
}
//create input parameters
List <OracleProcParam> iParams = new List <OracleProcParam>();
iParams.Add(new OracleProcParam("p_shop_number", ShopNbr));
iParams.Add(new OracleProcParam("p_ticket_nbr", nTicketNbr));
iParams.Add(new OracleProcParam("o_temp_status", OracleDbType.Varchar2, DBNull.Value, ParameterDirection.Output, 20));
//execute stored procedure
DataSet OutDataSet;
try
{
RetVal = DA.issueSqlStoredProcCommand(
"ccsowner", "pawn_support_locks", "get_pawnlock_record", iParams,
null, "o_return_code", "o_return_text", out OutDataSet);
}
catch (OracleException oEx)
{
msg = string.Format("Invocation of get_mdse_record stored proc failed{0}{1}{0}{2}",
Environment.NewLine, oEx.Message, Smsg);
MessageBox.Show(msg, "Error");
return;
}
if (RetVal == false)
{
msg = DA.ErrorDescription;
ErrorCode = DA.ErrorCode;
if (ErrorCode == "1")
{
MessageBox.Show("No record found matching this search criteria.", "No Data Found",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
MessageBox.Show(msg, "Error");
return;
}
RetVal = OracleUtilities.GetProcedureOutput(OutDataSet, 0, out TempStatus);
if (TempStatus == "null")
{
MessageBox.Show("No temp lock found.", "Warning",
MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
var frmClearStatus2 = new FrmClearTempStatus2(
TempStatus,
RecordType, // "Pawn Loan"
LblSearchName, // "Shop / Ticket Number:"
mdsePoint, // Screen Locaton
ShopNbr, // store number
TicketNbr // ticket number
);
frmResult = frmClearStatus2.ShowDialog();
if (frmResult == DialogResult.Cancel)
{
this.Close();
}
}
private void FindButton_Click(object sender, EventArgs e)
{
bool RetVal;
string msg;
//string StoreGuid;
//HardwareConfig.Hardware_Config.StoreNumber = txtShop.Text;
StoreNumber = txtShop.Text.PadLeft(5, '0');
if (!txtShop.isValid) // for empty string validation
{
MessageBox.Show(txtShop.ErrorMessage);
txtShop.Select();
return;
}
else
{
OracleDataAccessor DA = GlobalDataAccessor.Instance.OracleDA;
// call oracle procedure to get store guid
//create input parameters
var iParams = new List <OracleProcParam>();
iParams.Add(new OracleProcParam("p_shop_number", StoreNumber));
iParams.Add(new OracleProcParam("o_store_guid", OracleDbType.Varchar2, DBNull.Value, ParameterDirection.Output, 50));
//execute stored procedure
DataSet OutDataSet;
try
{
RetVal = DA.issueSqlStoredProcCommand(
"ccsowner", "pawn_support_gen_procs", "get_store_guid", iParams,
null, "o_return_code", "o_return_text", out OutDataSet);
}
catch (OracleException oEx)
{
msg = string.Format("Invocation of get_store_guid stored proc failed{0}{1}{0}{2}",
Environment.NewLine, oEx.Message, Smsg);
MessageBox.Show(msg, "Error");
return;
}
if (RetVal == false)
{
msg = DA.ErrorDescription; // error description that should show only.
string ErrorCode = DA.ErrorCode;
if (ErrorCode == "1")
{
MessageBox.Show(msg, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Information);
txtShop.Select();
return;
}
MessageBox.Show(msg + " -- " + ErrorCode, "Error. Please Contact Support.");
return;
}
RetVal = OracleUtilities.GetProcedureOutput(OutDataSet, 0, out StoreGuid);
if (StoreGuid == "null")
{
MessageBox.Show("Error Retrieving Store GUID. Please contact support.", "Warning",
MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
}
//Hardware.StoreID = StoreGuid;
//Hardware_Config.Instance.StoreID = StoreGuid;
this.DialogResult = DialogResult.OK;
this.Close();
}
public static bool ManagerOverrideReason(
DateTime overrideDateTime,
string storeNumber,
string overrideID,
ManagerOverrideTransactionType[] arManagerOverrideTransactionType,
ManagerOverrideType[] arManagerOverrideType,
decimal[] arSuggestedValue,
decimal[] arApprovedValue,
int[] arTransactionNumber,
string comment,
out string errorCode,
out string errorText,
string userId = null,
string workId = null)
{
//Set default output values
errorCode = string.Empty;
errorText = string.Empty;
//Verify that the accessor is valid
if (GlobalDataAccessor.Instance == null ||
!GlobalDataAccessor.Instance.IsDataAccessorValid())
{
errorCode = "ManagerOverrideReason";
errorText = "Invalid desktop session or data accessor instance";
BasicExceptionHandler.Instance.AddException("ManagerOverrideReason",
new ApplicationException(
"Cannot execute the Insert Override History stored procedure"));
return(false);
}
//Get data accessor object
OracleDataAccessor dA = GlobalDataAccessor.Instance.OracleDA;
//Create input list
List <OracleProcParam> inParams = new List <OracleProcParam>();
OracleProcParam pManagerOverrideTransType = new OracleProcParam(ParameterDirection.Input
, DataTypeConstants.PawnDataType.LISTSTRING
, "p_ovr_tran_cd"
, (arManagerOverrideTransactionType == null) ? 1 : arManagerOverrideTransactionType.Length);
if (arManagerOverrideTransactionType != null && arManagerOverrideTransactionType.Length > 0)
{
for (int i = 0; i < arManagerOverrideTransactionType.Length; i++)
{
pManagerOverrideTransType.AddValue(arManagerOverrideTransactionType[i].ToString());
}
}
else
{
pManagerOverrideTransType.AddValue(string.Empty);
}
inParams.Add(pManagerOverrideTransType);
OracleProcParam pManagerOverrideType = new OracleProcParam(ParameterDirection.Input
, DataTypeConstants.PawnDataType.LISTSTRING
, "p_ovr_sub_type_cd"
, (arManagerOverrideType == null) ? 1 : arManagerOverrideType.Length);
if (arManagerOverrideType != null && arManagerOverrideType.Length > 0)
{
for (int i = 0; i < arManagerOverrideType.Length; i++)
{
pManagerOverrideType.AddValue(arManagerOverrideType[i].ToString());
}
}
else
{
pManagerOverrideType.AddValue(string.Empty);
}
inParams.Add(pManagerOverrideType);
inParams.Add(new OracleProcParam("p_store_number", storeNumber));
inParams.Add(new OracleProcParam("p_override_by", overrideID));
OracleProcParam psuggestedvalue = new OracleProcParam(ParameterDirection.Input
, DataTypeConstants.PawnDataType.LISTFLOAT
, "p_sugg_value"
, (arSuggestedValue == null) ? 1 : arSuggestedValue.Length);
if (arSuggestedValue != null && arSuggestedValue.Length > 0)
{
for (int i = 0; i < arSuggestedValue.Length; i++)
{
psuggestedvalue.AddValue(arSuggestedValue[i]);
}
}
else
{
psuggestedvalue.AddValue(string.Empty);
}
inParams.Add(psuggestedvalue);
OracleProcParam papprovedvalue = new OracleProcParam(ParameterDirection.Input
, DataTypeConstants.PawnDataType.LISTFLOAT
, "p_appr_value"
, (arApprovedValue == null) ? 1 : arApprovedValue.Length);
if (arApprovedValue != null && arApprovedValue.Length > 0)
{
for (int i = 0; i < arApprovedValue.Length; i++)
{
papprovedvalue.AddValue(arApprovedValue[i]);
}
}
else
{
papprovedvalue.AddValue(string.Empty);
}
inParams.Add(papprovedvalue);
OracleProcParam ptrxnumber = new OracleProcParam(ParameterDirection.Input
, DataTypeConstants.PawnDataType.LISTINT
, "p_trx_number"
, (arTransactionNumber == null) ? 1 : arTransactionNumber.Length);
if (arTransactionNumber != null && arTransactionNumber.Length > 0)
{
for (var i = 0; i < arTransactionNumber.Length; i++)
{
ptrxnumber.AddValue(arTransactionNumber[i]);
}
}
else
{
ptrxnumber.AddValue(string.Empty);
}
inParams.Add(ptrxnumber);
inParams.Add(new OracleProcParam("p_trx_date", overrideDateTime));
inParams.Add(new OracleProcParam("p_user_id", (!string.IsNullOrEmpty(userId) ? userId : GlobalDataAccessor.Instance.DesktopSession.UserName)));
inParams.Add(new OracleProcParam("p_workstation_id", (!string.IsNullOrEmpty(workId) ? workId : GlobalDataAccessor.Instance.CurrentSiteId.TerminalId)));
inParams.Add(new OracleProcParam("p_ovr_comment", comment));
//Create output data set names
bool retVal = false;
try
{
DataSet outputDataSet = null;
retVal = dA.issueSqlStoredProcCommand(
"ccsowner", "pawn_gen_procs", "insert_override_history",
inParams, null, "o_return_code", "o_return_text",
out outputDataSet);
}
catch (OracleException oEx)
{
errorCode = "ManagerOverrideReasonFailed";
errorText = "Invocation of insert_override_history stored proc failed";
BasicExceptionHandler.Instance.AddException("OracleException thrown when invoking insert_override_history stored proc", oEx);
return(false);
}
//See if retVal is false
if (retVal)
{
errorCode = "0";
errorText = string.Empty;
return(true);
}
else
{
errorCode = dA.ErrorCode;
errorText = dA.ErrorDescription;
return(true);
}
}
public static bool GetExtensionPeriod(DateTime partialPaymentDate,
DateTime loanDateMade,
DateTime currentDate,
DateTime dueDate,
DateTime pfiNote,
DateTime pfiDate,
ExtensionTerms extensionType,
out int daysToPay,
out int monthsToPay,
out DateTime lastCycleEnd)
{
//Set output vars
string errorCode = string.Empty;
string errorText = string.Empty;
daysToPay = 0;
monthsToPay = 0;
lastCycleEnd = DateTime.MaxValue;
//Ensure the data accessor is valid
if (GlobalDataAccessor.Instance.OracleDA == null ||
GlobalDataAccessor.Instance.OracleDA.Initialized == false)
{
BasicExceptionHandler.Instance.AddException("ExecuteGetNextNumber Failed",
new ApplicationException("ExecuteGetNextNumber Failed: Data accessor instance is invalid"));
return(false);
}
OracleDataAccessor dA = GlobalDataAccessor.Instance.OracleDA;
List <string> miscFlags = new List <string>();
if (extensionType == ExtensionTerms.MONTHLY)
{
miscFlags.Add("M");
}
else
{
miscFlags.Add("D");
}
//Create parameter list
List <OracleProcParam> oParams = new List <OracleProcParam>();
oParams.Add(new OracleProcParam("date_made", loanDateMade.ToShortDateString()));
oParams.Add(new OracleProcParam("due_date", dueDate.ToShortDateString()));
oParams.Add(new OracleProcParam("pu_date", currentDate.ToShortDateString()));
oParams.Add(new OracleProcParam("pp_date_made", partialPaymentDate.ToShortDateString()));
oParams.Add(new OracleProcParam("misc_flags", true, miscFlags));
oParams.Add(new OracleProcParam("o_cycles_late", OracleDbType.Decimal, DBNull.Value, ParameterDirection.Output, 1));
oParams.Add(new OracleProcParam("o_days_into_cycle", OracleDbType.Decimal, DBNull.Value, ParameterDirection.Output, 1));
oParams.Add(new OracleProcParam("o_last_cyc_end", OracleDbType.Date, DBNull.Value, ParameterDirection.Output, 1));
oParams.Add(new OracleProcParam("o_next_cyc_end", OracleDbType.Date, DBNull.Value, ParameterDirection.Output, 1));
oParams.Add(new OracleProcParam("o_pp_days_aref", OracleDbType.Decimal, DBNull.Value, ParameterDirection.Output, 1));
oParams.Add(new OracleProcParam("o_pp_days_cred", OracleDbType.Decimal, DBNull.Value, ParameterDirection.Output, 1));
//Execute stored proc
DataSet outputSet;
bool retVal;
try
{
retVal = GlobalDataAccessor.Instance.OracleDA.issueSqlStoredProcCommand("ccsowner",
"service_pawn_loans", "get_cycles_late_cnt", oParams, null, "o_return_code",
"o_return_text", out outputSet);
}
catch (OracleException oEx)
{
BasicExceptionHandler.Instance.AddException("get_cycles_late_cnt Failed", oEx);
return(false);
}
if (retVal == false)
{
BasicExceptionHandler.Instance.AddException("get_cycles_late_cnt Failed: return value is false", new ApplicationException());
return(false);
}
//Get output number
int cyclesLate = 0;
int daysIntoCycle = 0;
DataTable outputDt = outputSet.Tables["OUTPUT"];
if (outputDt != null && outputDt.IsInitialized && outputDt.Rows != null && outputDt.Rows.Count > 0)
{
DataRow dr = outputDt.Rows[0];
if (dr != null && dr.ItemArray.Length > 0)
{
object nextNumObj = dr.ItemArray.GetValue(1);
if (nextNumObj != null)
{
var nextNumStr = (string)nextNumObj;
cyclesLate = Utilities.GetIntegerValue(nextNumStr);
}
}
DataRow dr1 = outputDt.Rows[1];
if (dr1 != null && dr1.ItemArray.Length > 0)
{
object nextNumObj = dr1.ItemArray.GetValue(1);
if (nextNumObj != null)
{
var nextNumStr = (string)nextNumObj;
daysIntoCycle = Utilities.GetIntegerValue(nextNumStr);
}
}
DataRow dr2 = outputDt.Rows[2];
if (dr2 != null && dr2.ItemArray.Length > 0)
{
object nextNumObj = dr2.ItemArray.GetValue(1);
if (nextNumObj != null)
{
var nextNumStr = (string)nextNumObj;
lastCycleEnd = Utilities.GetDateTimeValue(nextNumStr);
}
}
}
DateTime currentDateLoanStartDate = DateTime.MaxValue;
DateTime ppmtLoanStartDate = DateTime.MaxValue;
if (extensionType == ExtensionTerms.MONTHLY)
{
int cycles_late = 0;
int numberOfMonths;
DateTime next_cycle_end = loanDateMade;
if (partialPaymentDate == DateTime.MaxValue)
{
monthsToPay = 1;
if (cyclesLate == 0)
{
daysToPay = 0;
}
else
{
monthsToPay += cyclesLate;
if (currentDate > lastCycleEnd)
{
daysToPay = (currentDate - lastCycleEnd).Days;
}
else
{
daysToPay = (lastCycleEnd - currentDate).Days;
}
}
}
else
{
int ppmtLoanMonth = PartialPaymentProcedures.GetLoanMonth(loanDateMade, dueDate, pfiNote, pfiDate, partialPaymentDate, ref ppmtLoanStartDate);
int paidDays = (partialPaymentDate - ppmtLoanStartDate).Days;
int currentDateLoanMonth = PartialPaymentProcedures.GetLoanMonth(loanDateMade, dueDate, pfiNote, pfiDate, currentDate, ref currentDateLoanStartDate);
// 1 is added below so that when the loan month transition date is same as L_PartPmt_Date, it is not considered
int daysLeft = 30 - paidDays;
next_cycle_end = ppmtLoanStartDate.AddMonths(1);
while (currentDate >= next_cycle_end)
{
cycles_late++;
next_cycle_end = next_cycle_end.AddMonths(1);
}
daysToPay = daysLeft;
monthsToPay = cycles_late;
}
}
else
{
if (lastCycleEnd != DateTime.MaxValue && partialPaymentDate != DateTime.MaxValue)
{
int ppmtLoanMonth = PartialPaymentProcedures.GetLoanMonth(loanDateMade, dueDate, pfiNote, pfiDate, partialPaymentDate, ref ppmtLoanStartDate);
int paidDays = (partialPaymentDate - ppmtLoanStartDate).Days;
//int currentDateLoanMonth = PartialPaymentProcedures.GetLoanMonth(loanDateMade, dueDate, pfiNote, pfiDate, currentDate, ref currentDateLoanStartDate);
//int daysinPpmTdatecycle = (partialPaymentDate - currentDateLoanStartDate).Days;
int daysfromcurrenttoppmt = (currentDate - partialPaymentDate).Days;
if (paidDays + daysfromcurrenttoppmt > 30)
{
daysToPay = 30 - paidDays;
}
else
{
daysToPay = daysfromcurrenttoppmt;
}
}
}
return(true);
}
public static bool GetTransferInItems(DesktopSession cds, TransferVO transfer, out string errorCode, out string errorText)
{
if (transfer == null)
{
throw new ArgumentNullException("transfer");
}
//Initialize output vars
errorCode = string.Empty;
errorText = string.Empty;
OracleDataAccessor dA = GlobalDataAccessor.Instance.OracleDA;
//Create parameter list
List <OracleProcParam> oParams = new List <OracleProcParam>();
oParams.Add(new OracleProcParam("p_ticket_number", transfer.TransferTicketNumber));
oParams.Add(new OracleProcParam("p_store_number", cds.CurrentSiteId.StoreNumber));
oParams.Add(new OracleProcParam("p_user_id", cds.FullUserName));
oParams.Add(new OracleProcParam("p_transfer_type", (transfer.TransferSource == TransferSource.TOPSTICKET && transfer.TempStatus == TransferTempStatus.REJCT) ? TransferSource.CLXTICKET.ToString() : transfer.TransferSource.ToString()));
oParams.Add(new OracleProcParam("p_origination_store_number", transfer.OriginalStoreNumber));
//Setup ref cursor array
List <PairType <string, string> > refCursors = new List <PairType <string, string> >();
//Add general ref cursors
refCursors.Add(new PairType <string, string>("o_ti_mdse", "ti_mdse_ref_cursor"));
//Make stored proc call
bool retVal;
DataSet outputDataSet;
try
{
retVal = GlobalDataAccessor.Instance.OracleDA.issueSqlStoredProcCommand("ccsowner",
"transfers", "retrieve_TI_ticket_mdse", oParams, refCursors, "o_return_code",
"o_return_text", out outputDataSet);
errorCode = dA.ErrorCode;
errorText = dA.ErrorDescription;
}
catch (OracleException oEx)
{
BasicExceptionHandler.Instance.AddException("ExecuteGetTransferInTickets Failed", oEx);
errorCode = "ExecuteGetTransferInTicketsFailed";
errorText = "OracleException thrown: " + oEx.Message;
return(false);
}
if (retVal == false)
{
BasicExceptionHandler.Instance.AddException("ExecuteGetTransferInTickets Failed: return value is false", new ApplicationException());
errorCode = dA.ErrorCode + " --- ExecuteGetTransferInTickets";
errorText = dA.ErrorDescription + " -- Return value is false";
return(false);
}
if (outputDataSet != null && outputDataSet.Tables.Count == 1)
{
foreach (DataRow dr in outputDataSet.Tables[0].Rows)
{
TransferItemVO item = new TransferItemVO();
item.ICN = Utilities.GetStringValue(dr["icn"]);
item.TransferType = Utilities.ParseEnum <TransferTypes>(Utilities.GetStringValue(dr["trantype"], string.Empty), TransferTypes.STORETOSTORE);
item.MdseRecordDesc = Utilities.GetStringValue(dr["md_desc"]);
item.PfiAmount = Utilities.GetDecimalValue(dr["pfi_amount"], 0);
item.RefurbNumber = Utilities.GetIntegerValue(dr["rfb_no"], 0);
item.ICNQty = Utilities.GetStringValue(dr["quantity"]);
item.Transfer = transfer;
transfer.Items.Add(item);
}
}
return(true);
}
public static bool UpdateAssignedItems(
string ItemArray,
out string errorCode,
out string errorText)
{
//Set default output values
errorCode = string.Empty;
errorText = string.Empty;
DataSet outputDataSet = null;
//Verify that the accessor is valid
if (GlobalDataAccessor.Instance == null ||
GlobalDataAccessor.Instance.OracleDA == null)
{
errorCode = "UpdateAssignedItems";
errorText = "Invalid desktop session or data accessor instance";
BasicExceptionHandler.Instance.AddException("UpdateAssignedItems", new ApplicationException("Cannot execute the UpdateAssignedItems retrieval stored procedure"));
return(false);
}
//Get data accessor object
OracleDataAccessor dA = GlobalDataAccessor.Instance.OracleDA;
//Create input list
List <OracleProcParam> inParams = new List <OracleProcParam>();
OracleProcParam strParam = new OracleProcParam(ParameterDirection.Input,
DataTypeConstants.PawnDataType.LISTSTRING,
"p_input_string",
ItemArray.Length);
foreach (string s in ItemArray.Split(new string[] { "|" }, StringSplitOptions.None))
{
strParam.AddValue(s);
}
//Add cat pointer
inParams.Add(strParam);
//Create output data set names
bool retVal = false;
try
{
retVal = dA.issueSqlStoredProcCommand(
"ccsowner", SQL_PACKAGE, "update_itemsAssignment",
inParams, null, "o_error_code", "o_error_text",
out outputDataSet);
}
catch (OracleException oEx)
{
errorCode = "UpdateAssignedItemsFailed";
errorText = "Invocation of UpdateAssignedItems stored proc failed";
BasicExceptionHandler.Instance.AddException("OracleException thrown when invoking UpdateAssignedItems stored proc", oEx);
return(false);
}
//See if retVal is false
if (retVal == false)
{
errorCode = dA.ErrorCode;
errorText = dA.ErrorDescription;
return(false);
}
else
{
errorCode = "0";
errorText = string.Empty;
return(true);
}
}
