public String SalesGratuityToday(string paymentType, string orderstatus) //SalesToday
{
string SQL_str = "select Convert(nvarchar(10), " +
"DatePart(year, dCreatedDate)) + '-' + Convert(nvarchar(10), " +
"DatePart(month, dCreatedDate)) + '-' + Convert(nvarchar(10), DatePart(day, dCreatedDate))" +
" OrderDate,COALESCE(sum(dOrderServiceFee),0) SalesGratuity,sum(dOrderTax) Tax" +
" from pos_orderheader" +
" where vOrderStatus='" + orderstatus + "' and Convert(nvarchar(10), DatePart(year, dCreatedDate)) + '-' + Convert(nvarchar(10), DatePart(month, dCreatedDate)) + '-' + Convert(nvarchar(10), DatePart(day, dCreatedDate)) ='" + System.DateTime.Today.ToString("yyyy-M-d") + "'" +
"group by Convert(nvarchar(10), DatePart(year, dCreatedDate)) + '-' + Convert(nvarchar(10), DatePart(month, dCreatedDate)) + '-' + Convert(nvarchar(10), DatePart(day, dCreatedDate))";
//Connect to database and retrieve
SqlCEDataAccess POSdbAccess = new SqlCEDataAccess();
DataSet ResultSet;
DataTable ResultTable;
ResultSet = POSdbAccess.dsGetData(SQL_str, "POSsalesTipsToday");
ResultTable = ResultSet.Tables[0];
String RtnString;
Decimal SalesValue, TaxValue;
// SalesValue = (from DataRow dr in ResultTable.Rows where dr["Sales"] == EnvironmentVariableTarget select (int)dr["id"].FirstOrDefault);
if (ResultTable.Rows.Count > 0 && ResultTable.Rows != null)
{
if (ResultTable.Rows[0]["SalesGratuity"] != null)
{
SalesValue = (Decimal)ResultTable.Rows[0]["SalesGratuity"];
}
else
{
SalesValue = 0;
}
TaxValue = (Decimal)ResultTable.Rows[0]["Tax"];
// SalesValue = SalesValue; // + TaxValue; Tax is included in dOrderAmount......5/8/16
RtnString = "$ " + SalesValue.ToString("##,##.00");
}
else
{
RtnString = "$ 0.00";
}
return(RtnString);
}
public String YTDSales() //MonthlyAvg
{
string SQL_str = "select " +
"Convert(nvarchar(10), " +
"DatePart(year, dCreatedDate))" +
" OrderDate,sum(dOrderAmount) Sales,sum(dOrderTax) Tax" +
" from pos_orderheader" +
" where Convert(nvarchar(10), DatePart(year, dCreatedDate)) ='" + System.DateTime.Today.Year + "'" +
"group by Convert(nvarchar(10), DatePart(year, dCreatedDate)) ";
Int16 MonthCount = (Int16)System.DateTime.Today.Month;
//Connect to database and retrieve
SqlCEDataAccess POSdbAccess = new SqlCEDataAccess();
DataSet ResultSet;
DataTable ResultTable;
ResultSet = POSdbAccess.dsGetData(SQL_str, "POSytdSales");
ResultTable = ResultSet.Tables[0];
String RtnString;
Decimal SalesValue, TaxValue;
// SalesValue = (from DataRow dr in ResultTable.Rows where dr["Sales"] == EnvironmentVariableTarget select (int)dr["id"].FirstOrDefault);
if (ResultTable.Rows.Count > 0 && ResultTable.Rows != null)
{
SalesValue = (Decimal)ResultTable.Rows[0]["Sales"];
TaxValue = (Decimal)ResultTable.Rows[0]["Tax"];
// SalesValue = SalesValue + TaxValue; //tax included in dOrderAmount. 5/8/16 saa
RtnString = "$ " + SalesValue.ToString("##,##.00");
}
else
{
RtnString = "$ 0.00";
}
return(RtnString);
}
public String TransactionList() //Transaction List.
{
string SQL_str = "select Convert(nvarchar(10), " +
"DatePart(year, dCreatedDate)) + '-' + Convert(nvarchar(10), " +
"DatePart(month, dCreatedDate)) + '-' + Convert(nvarchar(10), DatePart(day, dCreatedDate))" +
" OrderDate,sum(dOrderAmount) Sales,sum(dOrderTax) Tax" +
" from pos_orderheader" +
" where Convert(nvarchar(10), DatePart(year, dCreatedDate)) + '-' + Convert(nvarchar(10), DatePart(month, dCreatedDate)) + '-' + Convert(nvarchar(10), DatePart(day, dCreatedDate)) ='" + System.DateTime.Today.ToString("yyyy-M-dd") + "'" +
"and vOrderStatus='Pending' group by Convert(nvarchar(10), DatePart(year, dCreatedDate)) + '-' + Convert(nvarchar(10), DatePart(month, dCreatedDate)) + '-' + Convert(nvarchar(10), DatePart(day, dCreatedDate))";
//Connect to database and retrieve
SqlCEDataAccess POSdbAccess = new SqlCEDataAccess();
DataSet ResultSet;
DataTable ResultTable;
ResultSet = POSdbAccess.dsGetData(SQL_str, "POSsalesPendingToday");
ResultTable = ResultSet.Tables[0];
String RtnString;
Decimal SalesValue, TaxValue;
// SalesValue = (from DataRow dr in ResultTable.Rows where dr["Sales"] == EnvironmentVariableTarget select (int)dr["id"].FirstOrDefault);
if (ResultTable.Rows.Count > 0 && ResultTable.Rows != null)
{
SalesValue = (Decimal)ResultTable.Rows[0]["Sales"];
TaxValue = (Decimal)ResultTable.Rows[0]["Tax"];
// SalesValue = SalesValue + TaxValue;//tax included in dOrderAmount. 5/8/16 saa
RtnString = "$ " + SalesValue.ToString("##,##.00");
}
else
{
RtnString = "$ 0.00";
}
return(RtnString);
}