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