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