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