public static decimal GetSessionPeriodMovementTotal(POS_WorkSessionPeriod pWorkSessionPeriod, MovementTypeTotal pMovementTypeTotal) { return(GetSessionPeriodMovementTotal(GlobalFramework.SessionXpo, pWorkSessionPeriod, pMovementTypeTotal)); }
public static decimal GetSessionPeriodMovementTotal(Session pSession, POS_WorkSessionPeriod pWorkSessionPeriod, MovementTypeTotal pMovementTypeTotal) { string sqlShared = @" SELECT SUM(wsmMovementAmount) AS Total FROM view_worksessionmovement WHERE ((dfmDocumentStatusStatus IS NULL OR dfmDocumentStatusStatus <> 'A') AND (dfpPaymentStatus IS NULL OR dfpPaymentStatus <> 'A')) AND {0} ;" ; decimal resultTotal = 0.0m; string sqlWhereTypeTotal = String.Empty; string sqlWherePeriod = String.Empty; if (pMovementTypeTotal != MovementTypeTotal.None) { try { switch (pMovementTypeTotal) { case MovementTypeTotal.All: sqlWhereTypeTotal = @"((cpmPaymentMethodToken = 'MONEY' AND wmtMovementTypeToken <> 'CASHDRAWER_OPEN' AND wmtMovementTypeToken <> 'FINANCE_DOCUMENT')) OR (cpmPaymentMethodToken <> 'MONEY')"; break; case MovementTypeTotal.AllNonMoney: sqlWhereTypeTotal = @"(cpmPaymentMethodToken <> 'MONEY' AND wmtMovementTypeToken <> 'FINANCE_DOCUMENT')"; break; case MovementTypeTotal.Money: sqlWhereTypeTotal = @"(cpmPaymentMethodToken = 'MONEY' AND wmtMovementTypeToken <> 'FINANCE_DOCUMENT')"; break; //Total Money Ins and Outs, includes Money Payments, and CashOpen case MovementTypeTotal.MoneyInCashDrawer: //sqlWhereTypeTotal = @"(wmtMovementTypeToken = 'CASHDRAWER_IN' OR wmtMovementTypeToken = 'CASHDRAWER_OUT')"; sqlWhereTypeTotal = @"(wmtMovementTypeToken = 'CASHDRAWER_OPEN' OR wmtMovementTypeToken = 'CASHDRAWER_IN' OR wmtMovementTypeToken = 'CASHDRAWER_OUT')"; break; //Total Money Ins and Outs (Without Sells) case MovementTypeTotal.MoneyIn: sqlWhereTypeTotal = @"(wmtMovementTypeToken = 'CASHDRAWER_IN' AND wsmPaymentMethod IS NULL)"; break; case MovementTypeTotal.MoneyOut: sqlWhereTypeTotal = @"(wmtMovementTypeToken = 'CASHDRAWER_OUT' AND wsmPaymentMethod IS NULL)"; break; case MovementTypeTotal.BankCheck: sqlWhereTypeTotal = @"(cpmPaymentMethodToken <> 'MONEY' AND cpmPaymentMethodToken = 'BANK_CHECK')"; break; case MovementTypeTotal.CashMachine: sqlWhereTypeTotal = @"(cpmPaymentMethodToken <> 'MONEY' AND cpmPaymentMethodToken = 'CASH_MACHINE')"; break; case MovementTypeTotal.Credit: sqlWhereTypeTotal = @"(cpmPaymentMethodToken <> 'MONEY' AND cpmPaymentMethodToken = 'CREDIT_CARD')"; break; case MovementTypeTotal.Visa: sqlWhereTypeTotal = @"(cpmPaymentMethodToken <> 'MONEY' AND cpmPaymentMethodToken = 'VISA')"; break; case MovementTypeTotal.CurrentAccount: sqlWhereTypeTotal = @"(cpmPaymentMethodToken <> 'MONEY' AND cpmPaymentMethodToken = 'CURRENT_ACCOUNT')"; break; default: break; } switch (pWorkSessionPeriod.PeriodType) { case WorkSessionPeriodType.Day: //sqlWherePeriod = string.Format(@"AND wspPeriodStatus = 0 AND (wspPeriodParent = '{0}')", pWorkSessionPeriod.Oid); sqlWherePeriod = string.Format(@"AND (wspPeriodParent = '{0}')", pWorkSessionPeriod.Oid); break; case WorkSessionPeriodType.Terminal: //sqlWherePeriod = string.Format(@"AND wspPeriodStatus = 0 AND (wspPeriod = '{0}')", pWorkSessionPeriod.Oid/*GlobalFramework.WorkSessionPeriodTerminal.Oid*/); sqlWherePeriod = string.Format(@"AND (wspPeriod = '{0}')", pWorkSessionPeriod.Oid /*GlobalFramework.WorkSessionPeriodTerminal.Oid*/); break; default: break; } string sql = string.Format(sqlShared, string.Format("(({0}) {1})", sqlWhereTypeTotal, sqlWherePeriod)); resultTotal = Convert.ToDecimal(pSession.ExecuteScalar(sql)); //_log.Debug(string.Format("pMovementTypeTotal: [{0}], resultTotal: [{1}], sql: [{2}]", pMovementTypeTotal, resultTotal, sql)); } catch (Exception ex) { _log.Error(ex.Message, ex); } } return(resultTotal); }
public static bool PersistWorkSessionTotals(POS_WorkSessionPeriod pWorkSessionPeriod) { try { //Start UnitOfWork using (UnitOfWork uowSession = new UnitOfWork()) { uint paymentMethodOrd = 0; string paymentMethodToken = String.Empty; MovementTypeTotal movementTypeTotal = MovementTypeTotal.None; POS_WorkSessionPeriod workSessionPeriod; FIN_ConfigurationPaymentMethod configurationPaymentMethod; POS_WorkSessionPeriodTotal workSessionPeriodTotal; //Can filter by Day or Terminal type string wherePeriodField = (pWorkSessionPeriod.PeriodType == WorkSessionPeriodType.Terminal) ? "wspPeriod" : "wspPeriodParent"; string sql = string.Format(@" SELECT DISTINCT(cpmPaymentMethodToken) as Token, cpmPaymentMethodOrd as Ord FROM view_worksessionmovement WHERE cpmPaymentMethodToken IS NOT NULL AND {0} = '{1}' ORDER BY cpmPaymentMethodOrd ;" , wherePeriodField , pWorkSessionPeriod.Oid ); XPSelectData xPSelectData = FrameworkUtils.GetSelectedDataFromQuery(uowSession, sql); foreach (SelectStatementResultRow row in xPSelectData.Data) { paymentMethodOrd = Convert.ToUInt16(row.Values[xPSelectData.GetFieldIndex("Ord")]); paymentMethodToken = row.Values[xPSelectData.GetFieldIndex("Token")].ToString(); switch (paymentMethodToken) { case "MONEY": movementTypeTotal = MovementTypeTotal.Money; break; case "BANK_CHECK": movementTypeTotal = MovementTypeTotal.BankCheck; break; case "CASH_MACHINE": movementTypeTotal = MovementTypeTotal.CashMachine; break; case "CREDIT_CARD": movementTypeTotal = MovementTypeTotal.Credit; break; case "VISA": movementTypeTotal = MovementTypeTotal.Visa; break; case "CURRENT_ACCOUNT": movementTypeTotal = MovementTypeTotal.CurrentAccount; break; } if (movementTypeTotal != MovementTypeTotal.None) { //Get XPObjects workSessionPeriod = uowSession.GetObjectByKey <POS_WorkSessionPeriod>(pWorkSessionPeriod.Oid); configurationPaymentMethod = (FIN_ConfigurationPaymentMethod)FrameworkUtils.GetXPGuidObjectFromField(uowSession, typeof(FIN_ConfigurationPaymentMethod), "Token", paymentMethodToken); //Persist WorkSessionPeriodTotal workSessionPeriodTotal = new POS_WorkSessionPeriodTotal(uowSession) { Ord = paymentMethodOrd, PaymentMethod = configurationPaymentMethod, Total = GetSessionPeriodMovementTotal(workSessionPeriod, movementTypeTotal), Period = workSessionPeriod }; } } ; try { //Commit UOW Changes uowSession.CommitChanges(); return(true); } catch (Exception ex) { //Rollback uowSession.RollbackTransaction(); _log.Error(ex.Message, ex); return(false); } } } catch (Exception ex) { _log.Error(ex.Message, ex); return(false); } }