public static bool GetInventorySummaryReportFieldsCACC(ref List <AuditReportsObject.InventorySummaryChargeOffsField> chargeoffields, string storenumber, int auditID, CommonDatabaseContext dataContext)
{
OracleDataAccessor dA = GlobalDataAccessor.Instance.OracleDA;
string lastInvAudit = string.Empty;
//Create parameter list
List <OracleProcParam> oParams = new List <OracleProcParam>();
oParams.Add(new OracleProcParam("p_storenumber", storenumber));
oParams.Add(new OracleProcParam("p_AuditID", auditID));
//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_last_inv_audit", "o_last_inv_audit"));
refCursors.Add(new PairType <string, string>("o_cacc_info", "o_cacc_info"));
//Make stored proc call
bool retVal;
DataSet outputDataSet;
try
{
retVal = GlobalDataAccessor.Instance.OracleDA.issueSqlStoredProcCommand("ccsowner",
"AUDIT_REPORTS", "getCACCInfo", oParams, refCursors, "o_return_code",
"o_return_text", out outputDataSet);
dataContext.ErrorCode = dA.ErrorCode;
dataContext.ErrorText = dA.ErrorDescription;
}
catch (OracleException oEx)
{
BasicExceptionHandler.Instance.AddException("GetInventorySummaryReportFields Failed", oEx);
dataContext.ErrorCode = "GetInventorySummaryReportFields";
dataContext.ErrorText = "OracleException thrown: " + oEx.Message;
return(false);
}
if (retVal == false)
{
BasicExceptionHandler.Instance.AddException("GetInventorySummaryReportFields Failed: return value is false", new ApplicationException());
dataContext.ErrorCode = dA.ErrorCode + " --- GetInventorySummaryReportFields";
dataContext.ErrorText = dA.ErrorDescription + " -- Return value is false";
return(false);
}
if (outputDataSet == null)
{
return(false);
}
if (outputDataSet.Tables.Count > 0)
{
try
{
if (outputDataSet.Tables[TBL_INVENTORYSUMMARY_CHARGEOFF_CACC] != null && outputDataSet.Tables[TBL_INVENTORYSUMMARY_CHARGEOFF_CACC].Rows.Count > 0)
{
foreach (DataRow dr in outputDataSet.Tables[TBL_INVENTORYSUMMARY_CHARGEOFF_CACC].Rows)
{
AuditReportsObject.InventorySummaryChargeOffsField field = new AuditReportsObject.InventorySummaryChargeOffsField();
field.Category = Utilities.GetIntegerValue(dr["CAT_CODE"], 0);
field.CategoryDescription = Utilities.GetStringValue(dr["CAT_DESC"], string.Empty);
field.TotalItems = Utilities.GetIntegerValue(dr["ORG_QTY"], 0);
field.TotalAmount = Utilities.GetDecimalValue(dr["ORG_COST"], 0.00M);
field.TotalItemsNF = Utilities.GetIntegerValue(dr["NEW_QTY"], 0);
field.TotalAmountNF = Utilities.GetDecimalValue(dr["NEW_COST"], 0.00M);
if (field.TotalItems > field.TotalItemsNF)
{
field.TotalItemsNF = field.TotalItems - field.TotalItemsNF;
}
else
{
field.TotalItemsNF = 0;
}
if (field.TotalAmount > field.TotalAmountNF)
{
field.TotalAmountNF = field.TotalAmount - field.TotalAmountNF;
}
else
{
field.TotalAmountNF = 0.00m;
}
field.PercentItemsNF = (field.TotalItemsNF > 0 && field.TotalItems > 0) ? Math.Round((decimal)field.TotalItemsNF / (decimal)field.TotalItems * 100, 2) : 0.00M;
field.PercentNFAmount = (field.TotalAmountNF > 0.00m & field.TotalAmount > 0.00m) ? Math.Round(field.TotalAmountNF / field.TotalAmount * 100, 2) : 0.00M;
chargeoffields.Add(field);
}
}
}
catch (Exception e)
{
return(false);
}
}
dataContext.ErrorCode = "0";
dataContext.ErrorText = "Success";
return(true);
}
public static bool GetInventorySummaryReportFields(ref List <AuditReportsObject.InventorySummaryChargeOffsField> listInvChargeoffields,
ref List <AuditReportsObject.InventorySummaryHistoryField> listInvHistoryfields,
ref StringBuilder stringbuilderEmployeesPresent,
ref StringBuilder stringbuilderTermEmployees,
string storenumber, int auditID, CommonDatabaseContext dataContext)
{
OracleDataAccessor dA = GlobalDataAccessor.Instance.OracleDA;
string lastInvAudit = string.Empty;
//Create parameter list
List <OracleProcParam> oParams = new List <OracleProcParam>();
oParams.Add(new OracleProcParam("p_storenumber", storenumber));
oParams.Add(new OracleProcParam("p_AuditID", auditID));
oParams.Add(new OracleProcParam("o_last_inv_audit", OracleDbType.Varchar2, lastInvAudit, ParameterDirection.Output, 100));
//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_last_inv_audit", "o_last_inv_audit"));
refCursors.Add(new PairType <string, string>("o_psnl", "o_psnl"));
refCursors.Add(new PairType <string, string>("o_term", "o_term"));
refCursors.Add(new PairType <string, string>("o_chgOff", "o_chgOff"));
refCursors.Add(new PairType <string, string>("o_Hist", "o_Hist"));
//Make stored proc call
bool retVal;
DataSet outputDataSet;
try
{
retVal = GlobalDataAccessor.Instance.OracleDA.issueSqlStoredProcCommand("ccsowner",
"AUDIT_REPORTS", "SummaryReport", oParams, refCursors, "o_return_code",
"o_return_text", out outputDataSet);
dataContext.ErrorCode = dA.ErrorCode;
dataContext.ErrorText = dA.ErrorDescription;
}
catch (OracleException oEx)
{
BasicExceptionHandler.Instance.AddException("GetInventorySummaryReportFields Failed", oEx);
dataContext.ErrorCode = "GetInventorySummaryReportFields";
dataContext.ErrorText = "OracleException thrown: " + oEx.Message;
return(false);
}
if (retVal == false)
{
BasicExceptionHandler.Instance.AddException("GetInventorySummaryReportFields Failed: return value is false", new ApplicationException());
dataContext.ErrorCode = dA.ErrorCode + " --- GetInventorySummaryReportFields";
dataContext.ErrorText = dA.ErrorDescription + " -- Return value is false";
return(false);
}
if (outputDataSet == null)
{
return(false);
}
if (outputDataSet.Tables.Count > 0)
{
try
{
if (outputDataSet.Tables[TBL_INVENTORYSUMMARY_CHARGEOFF] != null && outputDataSet.Tables[TBL_INVENTORYSUMMARY_CHARGEOFF].Rows.Count > 0)
{
foreach (DataRow dr in outputDataSet.Tables[TBL_INVENTORYSUMMARY_CHARGEOFF].Rows)
{
AuditReportsObject.InventorySummaryChargeOffsField field = new AuditReportsObject.InventorySummaryChargeOffsField();
field.Category = Utilities.GetIntegerValue(dr["CAT_CODE"], 0);
field.CategoryDescription = Utilities.GetStringValue(dr["CAT_DESC"], string.Empty);
field.TotalItems = Utilities.GetIntegerValue(dr["QTY"], 0);
field.TotalAmount = Utilities.GetDecimalValue(dr["COST"], 0.00M);
field.TotalItemsNF = Utilities.GetIntegerValue(dr["NF_CNT"], 0);
field.TotalAmountNF = Utilities.GetDecimalValue(dr["NF_AMT"], 0.00M);
field.PercentItemsNF = (field.TotalItemsNF > 0 && field.TotalItems > 0) ? Math.Round((decimal)field.TotalItemsNF / (decimal)field.TotalItems * 100, 2) : 0.00M;
field.PercentNFAmount = (field.TotalAmountNF > 0.00m & field.TotalAmount > 0.00m) ? Math.Round(field.TotalAmountNF / field.TotalAmount * 100, 2) : 0.00M;
listInvChargeoffields.Add(field);
}
}
if (outputDataSet.Tables[TBL_INVENTORYSUMMARY_HIST] != null && outputDataSet.Tables[TBL_INVENTORYSUMMARY_HIST].Rows.Count > 0)
{
foreach (DataRow dr in outputDataSet.Tables[TBL_INVENTORYSUMMARY_HIST].Rows)
{
listInvHistoryfields.Add(new AuditReportsObject.InventorySummaryHistoryField()
{
InventoryDate = Utilities.GetDateTimeValue(dr["DATE_INITIATED"], DateTime.MinValue),
Manager = Utilities.GetStringValue(dr["STORE_MANAGER"], string.Empty),
InvType = Utilities.GetStringValue(dr["AUDIT_SCOPE"], string.Empty),
InvSubType = Utilities.GetStringValue(dr["AUDIT_REASON"], string.Empty),
OverShort = Utilities.GetDecimalValue(dr["OVER_UNDER"], 0.00M),
Adjustment = Utilities.GetDecimalValue(dr["ADJ"], 0.00M),
NetOverShort = Utilities.GetDecimalValue(dr["NET"], 0.00M),
PAScore = Utilities.GetIntegerValue(dr["AUDIT_SCORE"], 0),
});
}
}
if (outputDataSet.Tables[TBL_INVENTORYSUMMARY_PSNL] != null && outputDataSet.Tables[TBL_INVENTORYSUMMARY_PSNL].Rows.Count > 0)
{
foreach (DataRow dr in outputDataSet.Tables[TBL_INVENTORYSUMMARY_PSNL].Rows)
{
string name = Utilities.GetStringValue(dr["NAME"], string.Empty);
if (!string.IsNullOrEmpty(name))
{
if (string.IsNullOrEmpty(stringbuilderEmployeesPresent.ToString()))
{
stringbuilderEmployeesPresent.Append(name);
}
else
{
stringbuilderEmployeesPresent.Append(", " + name);
}
}
}
}
if (outputDataSet.Tables[TBL_INVENTORYSUMMARY_TERM] != null && outputDataSet.Tables[TBL_INVENTORYSUMMARY_TERM].Rows.Count > 0)
{
foreach (DataRow dr in outputDataSet.Tables[TBL_INVENTORYSUMMARY_TERM].Rows)
{
string name = Utilities.GetStringValue(dr["NAME"], string.Empty);
if (!string.IsNullOrEmpty(name) || !name.Equals(" "))
{
if (string.IsNullOrEmpty(stringbuilderTermEmployees.ToString()))
{
stringbuilderTermEmployees.Append(name);
}
else
{
stringbuilderTermEmployees.Append(", " + name);
}
}
}
}
}
catch (Exception e)
{
return(false);
}
}
dataContext.ErrorCode = "0";
dataContext.ErrorText = "Success";
return(true);
}
