public String CustomersCount() //ItemsCount
{
string SQL_str = "select count(iCustID) CustomerCount from POS_Customer";
//Connect to database and retrieve
SqlCEDataAccess POSdbAccess = new SqlCEDataAccess();
DataSet ResultSet;
DataTable ResultTable;
ResultSet = POSdbAccess.dsGetData(SQL_str, "POSCustomerCount");
ResultTable = ResultSet.Tables[0];
String RtnString;
// SalesValue = (from DataRow dr in ResultTable.Rows where dr["Sales"] == EnvironmentVariableTarget select (int)dr["id"].FirstOrDefault);
if (ResultTable.Rows.Count > 0 && ResultTable.Rows != null)
{
// OrderCount = (Int32)ResultTable.Rows[0]["OrderCount"];
// RtnString = OrderCount.ToString();
RtnString = ResultTable.Rows[0]["CustomerCount"].ToString();
}
else
{
RtnString = "0";
}
return(RtnString);
}
public Int32 RetrieveNextItemID() //Next Item id
{
string _vItemStatus = "Publish";
string SQL_str = "SELECT max([itemID]) ItemID " +
"FROM [POS_ItemMaster]" +
"where itemID< 9000 ";
//Connect to database and retrieve
SqlCEDataAccess POSdbAccess = new SqlCEDataAccess();
DataSet ResultSet;
DataTable ResultTable;
ResultSet = POSdbAccess.dsGetData(SQL_str, "POSitemMasterItemID");
ResultTable = ResultSet.Tables[0];
DataRow dr;
dr = ResultTable.Rows[0];
Int32 NextItemNumber = 1;
if (ResultTable.Rows.Count > 0)
{
NextItemNumber = ResultTable.Rows[0].Field <Int32>("ItemID"); //.ToString();
NextItemNumber++;
}
return(NextItemNumber);
}
public ObservableCollection <SettingsCategoryListForDisplay> SettingsCategoriesListDisplay(string inputParam) //List of Categories
{
string _vStatus = "Publish";
string SQL_str = "SELECT Id" +
",CatID" +
",vCategoryCode" +
",vCategoryDesc" +
",vCategoryShortDesc" +
",vComments" +
",vStatus" +
",dStartDt" +
",dEndDt" +
",vEntryBy" +
",tTimestamp" +
" FROM POS_ItemCategory"; // + " where vItemStatus='" + _vItemStatus + "'";
//Connect to database and retrieve
SettingsCategoryListForDisplay Obj = new SettingsCategoryListForDisplay();
IList <SettingsCategoryListForDisplay> ALLcategoryList;
SqlCEDataAccess POSdbAccess = new SqlCEDataAccess();
ALLcategoryList = POSdbAccess.connection.ConvertSqlQueryToIList(Obj, SQL_str);
ObservableCollection <SettingsCategoryListForDisplay> ALLcategory = new ObservableCollection <SettingsCategoryListForDisplay>(ALLcategoryList);
return(ALLcategory);
}
public IList<ItemCategoryListForDisplay> ItemCategoryListDisplay() //List of Items
{
string _vStatus = "Publish";
string SQL_str = "SELECT Id" +
",CatID" +
",vCategoryCode" +
",vCategoryDesc" +
",vCategoryShortDesc" +
",vComments" +
",vStatus" +
",dStartDt" +
",dEndDt" +
",vEntryBy" +
",tTimestamp" +
" FROM POS_ItemCategory"; // + " where vItemStatus='" + _vItemStatus + "'";
//Connect to database and retrieve
ItemCategoryListForDisplay Obj = new ItemCategoryListForDisplay();
IList<ItemCategoryListForDisplay> ALLcategory;
SqlCEDataAccess POSdbAccess = new SqlCEDataAccess();
ALLcategory = POSdbAccess.connection.ConvertSqlQueryToIList(Obj, SQL_str);
return ALLcategory;
}
public DataTable Top5SellingDays() //Top5SellingItems
{
//+ sum(dOrderTax) Sales ///not to be added in dorderamount
string SQL_str = "select top(5) Convert(nvarchar(10), DatePart(year, dCreatedDate)) + '-' + " +
"Convert(nvarchar(10), DatePart(month, dCreatedDate)) + '-' + Convert(nvarchar(10), " +
" DatePart(day, dCreatedDate)) OrderDate,sum(dOrderAmount) Sales " +
" from pos_orderheader where Convert(nvarchar(10), DatePart(month, dCreatedDate))= '" + System.DateTime.Today.Month + "'" +
" and Convert(nvarchar(10), DatePart(year, dCreatedDate))= '" + System.DateTime.Today.Year + "'" +
" group by Convert(nvarchar(10), DatePart(year, dCreatedDate)) +'-' + " +
" Convert(nvarchar(10), DatePart(month, dCreatedDate)) + '-' + Convert(nvarchar(10), " +
" DatePart(day, dCreatedDate)) order by sales desc";
//Connect to database and retrieve
SqlCEDataAccess POSdbAccess = new SqlCEDataAccess();
DataSet ResultSet;
DataTable ResultTable;
ResultSet = POSdbAccess.dsGetData(SQL_str, "POStopDays");
ResultTable = ResultSet.Tables[0];
return(ResultTable);
}
public ObservableCollection <SalesForDataVisual> WeeklyPaymentTrendVisual() //Last 7 days payment Trend.
{
string _vStatus = "Publish";
//+ sum(dOrderTax) Sales ///not to be added in dorderamount
string SQL_str = "select Convert(nvarchar(10), DatePart(year, dCreatedDate)) + '-' + " +
"Convert(nvarchar(10), DatePart(month, dCreatedDate)) " +
" OrderDate,sum(dOrderAmount) Sales " +
" from pos_orderheader where " +
" Convert(nvarchar(10), DatePart(year, dCreatedDate))= '" + System.DateTime.Today.Year + "'" +
" group by Convert(nvarchar(10), DatePart(year, dCreatedDate)) +'-' + " +
" Convert(nvarchar(10), DatePart(month, dCreatedDate)) " +
" order by sales desc";
//Connect to database and retrieve
SalesForDataVisual Obj = new SalesForDataVisual();
IList <SalesForDataVisual> SalesByMonthList;
SqlCEDataAccess POSdbAccess = new SqlCEDataAccess();
SalesByMonthList = POSdbAccess.connection.ConvertSqlQueryToIList(Obj, SQL_str);
ObservableCollection <SalesForDataVisual> SalesByMonth = new ObservableCollection <SalesForDataVisual>(SalesByMonthList);
return(SalesByMonth);
}
public IList<ItemListForDisplay> ItemListDisplay() //List of Items
{
string _vItemStatus = "Publish";
string SQL_str = "SELECT Id" +
",itemID" +
",vItemSku" +
",vItemDesc1" +
",vItemDesc2" +
",vItemPrice" +
",vItemRelatedID" +
",vItemMinPrice" +
",vItemVendor" +
",vItemVendorContact" +
",vItemVendorPhone" +
",vItemNotes" +
",vItemStatus" +
",vItemAvailability" +
",dStartDate" +
",dEndDate" +
",vEntryBy" +
",tTimestamp" +
",POS_ItemCategoryId" +
" FROM POS_ItemMaster" + " where vItemStatus='" + _vItemStatus + "'";
//Connect to database and retrieve
ItemListForDisplay Obj = new ItemListForDisplay();
IList<ItemListForDisplay> ALLitems;
SqlCEDataAccess POSdbAccess = new SqlCEDataAccess();
ALLitems = POSdbAccess.connection.ConvertSqlQueryToIList(Obj, SQL_str);
return ALLitems;
}
public DataTable ItemOnStockList() //List of Stock Items
{
string _vItemStatus = "Publish";
string SQL_str = "SELECT " +
"itemID" + '"' + "Item #" + '"' +
",vItemDesc1" + '"' + "Description" + '"' +
",vItemPrice" + '"' + "Price" + '"' +
",vItemVendor" + '"' + "Vendor" + '"' +
",vItemVendorContact" + '"' + "Contact Name" + '"' +
",vItemVendorPhone" + '"' + "Phone" + '"' +
",POS_ItemCategoryId" + '"' + "Category" + '"' +
" FROM POS_ItemMaster" + " where vItemStatus='" + _vItemStatus + "'";
//Connect to database and retrieve
SqlCEDataAccess POSdbAccess = new SqlCEDataAccess();
DataSet ResultSet;
DataTable ResultTable;
ResultSet = POSdbAccess.dsGetData(SQL_str, "POSitemMaster");
ResultTable = ResultSet.Tables[0];
return(ResultTable);
}
public bool ItemUpdate(ItemListForDisplay InputRecordObj) //Update Item
{
string _vItemStatus = "Publish";
string SQL_str = "Update POS_ItemMaster SET " +
"[vItemSku]=@vItemSku" +
",[vItemDesc1]=@vItemDesc1" +
",[vItemDesc2]=@vItemDesc2" +
",[vItemPrice]=@vItemPrice" +
",[vItemRelatedID]=@vItemRelatedID" +
",[vItemMinPrice]=@vItemMinPrice" +
",[vItemVendor]=@vItemVendor" +
",[vItemVendorContact]=@vItemVendorContact" +
",[vItemVendorPhone]=@vItemVendorPhone" +
",[vItemNotes]=@vItemNotes" +
",[vItemStatus]=@vItemStatus" +
",[vItemAvailability]=@vItemAvailability" +
",[dStartDate]=@dStartDate" +
",[dEndDate]=@dEndDate" +
",[vEntryBy]=@vEntryBy" +
",[tTimestamp]=@tTimestamp" +
",[vItemmodifier]=@vItemmodifier" +
" Where ItemID = " + InputRecordObj.itemID;
//Connect to database and retrieve
SqlCEDataAccess POSdbAccess = new SqlCEDataAccess();
POSdbAccess.connection.Execute(SQL_str, new
{
InputRecordObj.vItemSku
, InputRecordObj.vItemDesc1
, InputRecordObj.vItemDesc2
, InputRecordObj.vItemPrice
, InputRecordObj.vItemRelatedID
, InputRecordObj.vItemMinPrice
, InputRecordObj.vItemVendor
, InputRecordObj.vItemVendorContact
, InputRecordObj.vItemVendorPhone
, InputRecordObj.vItemNotes
, InputRecordObj.vItemStatus
, InputRecordObj.vItemAvailability
, InputRecordObj.dStartDate
, InputRecordObj.dEndDate
, InputRecordObj.vEntryBy
, InputRecordObj.tTimestamp
, InputRecordObj.vItemmodifier
});
bool Result;
Result = true;
// DataTable ResultTable;
// Result = POSdbAccess.execScalar(SQL_str);
//ResultTable = ResultSet.Tables[0];
return(Result);
}
public IList<CustomerListForDisplay> CustomerListDisplayAdvance(string paramCategory) //List of customers
{
string SQL_str = "Select id" +
",iCustid" +
",vCustName" +
",vCustContactNme" +
",vCustPrimaryPh" +
",vCustPhone2" +
",vCustPhone3" +
",vCustPhone4" +
",vCustFax1" +
",vCustFax2" +
",vCustEmail" +
",vCustAddress1" +
",vCustAddress2" +
",vCustCity" +
",vCustState" +
",vCustCountry" +
",vCustZipCode" +
",vCustBillAddress1" +
",vCustBillAddress2" +
",vCustBillCity" +
",vCustBillCountry" +
",vCustBillZipCode" +
",vCustShipAddress1" +
",vCustShipAddress2" +
",vCustShipCity" +
",vCustShipState" +
",vCustShipCountry" +
",vCustShipZipCode" +
",vCustAccountNum" +
",vCustNote1" +
",vCustNote2" +
",vCustNote3" +
",vCustCategory" +
",vCustComments" +
",vCustStatus" +
",vEntryBy" +
",tTimestamp" +
",POS_Setup_Id" +
" FROM POS_Customer" ;
//Connect to database and retrieve
CustomerListForDisplay Obj = new CustomerListForDisplay();
IList<CustomerListForDisplay> ALLcustomers;
SqlCEDataAccess POSdbAccess = new SqlCEDataAccess();
ALLcustomers = POSdbAccess.connection.ConvertSqlQueryToIList(Obj, SQL_str);
return ALLcustomers;
}
public DataTable CustomerList() //List of Customers
{
string SQL_str = "SELECT " +
"iCustid" +
",vCustName" +
",vCustContactNme" +
",vCustPrimaryPh" +
",vCustPhone2" +
",vCustPhone3" +
",vCustPhone4" +
",vCustFax1" +
",vCustFax2" +
",vCustEmail" +
",vCustAddress1" +
",vCustAddress2" +
",vCustCity" +
",vCustState" +
",vCustCountry" +
",vCustZipCode" +
",vCustBillAddress1" +
",vCustBillAddress2" +
",vCustBillCity" +
",vCustBillCountry" +
",vCustBillZipCode" +
",vCustShipAddress1" +
",vCustShipAddress2" +
",vCustShipCity" +
",vCustShipState" +
",vCustShipCountry" +
",vCustShipZipCode" +
",vCustAccountNum" +
",vCustNote1" +
",vCustNote2" +
",vCustNote3" +
",vCustCategory" +
",vCustComments" +
",vCustStatus" +
",vEntryBy" +
",tTimestamp" +
",POS_Setup_Id" +
" FROM POS_Customer"; // + " where vCustStatus='" + _vCustStatus + "'";
//Connect to database and retrieve
SqlCEDataAccess POSdbAccess = new SqlCEDataAccess();
DataSet ResultSet;
DataTable ResultTable;
ResultSet = POSdbAccess.dsGetData(SQL_str, "POScustomer");
ResultTable = ResultSet.Tables[0];
return(ResultTable);
}
//this is called from Item Module.
public IList <ItemListForDisplay> ItemListDisplayAdvance(string paramCategory) //List of Items
{
string _vItemStatus = "Publish";
string _querySign = "=";
if (paramCategory == "All Items")
{
_querySign = "<>";
}
string SQL_str = "SELECT item.Id" +
",itemID" +
",vItemSku" +
",vItemDesc1" +
",vItemDesc2" +
",vItemPrice" +
",vItemRelatedID" +
",vItemMinPrice" +
",vItemVendor" +
",vItemVendorContact" +
",vItemVendorPhone" +
",vItemNotes" +
",vItemStatus" +
",vItemAvailability" +
",item.dStartDate" +
",item.dEndDate" +
",item.vEntryBy" +
",item.tTimestamp" +
",POS_ItemCategoryId" +
",vCategoryCode" +
",vCategoryShortDesc" +
",CatID" +
",vItemmodifier" +
" FROM POS_ItemMaster item,POS_ItemCategory itemCat" +
" where item.pos_itemCategoryId=itemCat.ID and vItemStatus='" + _vItemStatus + "'" +
" and itemCat.vCategoryShortDesc" + _querySign + "'" + paramCategory + "'";
//Connect to database and retrieve
ItemListForDisplay Obj = new ItemListForDisplay();
IList <ItemListForDisplay> ALLitems;
SqlCEDataAccess POSdbAccess = new SqlCEDataAccess();
ALLitems = POSdbAccess.connection.ConvertSqlQueryToIList(Obj, SQL_str);
return(ALLitems);
}
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 DataTable Top5SellingItems() //Top5SellingItems
{
string SQL_str = "SELECT top(5) vItemDesc1 Item," +
" sum([fOrderQty]) Quantity " +
" FROM[POS_OrderDetails] Ord,[POS_ItemMaster] " +
"Item where Ord.POS_ItemMasterId = Item.id and " +
"Convert(nvarchar(10), DatePart(year, dCreatedDate)) = '" + System.DateTime.Today.Year + "'" +
" group by vItemDesc1 " +
" order by Quantity Desc";
//Connect to database and retrieve
SqlCEDataAccess POSdbAccess = new SqlCEDataAccess();
DataSet ResultSet;
DataTable ResultTable;
ResultSet = POSdbAccess.dsGetData(SQL_str, "POStopItems");
ResultTable = ResultSet.Tables[0];
return(ResultTable);
}
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 IList <SettingsListForDisplay> SettingsListDisplayAdvance() //List of Settings
{
string _vItemStatus = "Publish";
string SQL_str = "SELECT item.Id" +
",itemID" +
",vItemSku" +
",vItemDesc1" +
",vItemDesc2" +
",vItemPrice" +
",vItemRelatedID" +
",vItemMinPrice" +
",vItemVendor" +
",vItemVendorContact" +
",vItemVendorPhone" +
",vItemNotes" +
",vItemStatus" +
",vItemAvailability" +
",item.dStartDate" +
",item.dEndDate" +
",item.vEntryBy" +
",item.tTimestamp" +
",POS_ItemCategoryId" +
",vCategoryCode" +
",CatID" +
" FROM POS_ItemMaster item,POS_ItemCategory itemCat" +
" where item.pos_itemCategoryId=itemCat.ID and vItemStatus='" + _vItemStatus + "'";
//Connect to database and retrieve
SettingsListForDisplay Obj = new SettingsListForDisplay();
IList <SettingsListForDisplay> ALLitems;
SqlCEDataAccess POSdbAccess = new SqlCEDataAccess();
ALLitems = POSdbAccess.connection.ConvertSqlQueryToIList(Obj, SQL_str);
return(ALLitems);
}
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);
}
public DataTable ItemList() //List of Items
{
string _vItemStatus = "Publish";
string SQL_str = "SELECT Id" +
",itemID" +
",vItemSku" +
",vItemDesc1" +
",vItemDesc2" +
",vItemPrice" +
",vItemRelatedID" +
",vItemMinPrice" +
",vItemVendor" +
",vItemVendorContact" +
",vItemVendorPhone" +
",vItemNotes" +
",vItemStatus" +
",vItemAvailability" +
",dStartDate" +
",dEndDate" +
",vEntryBy" +
",tTimestamp" +
",POS_ItemCategoryId" +
" FROM POS_ItemMaster" + " where vItemStatus='" + _vItemStatus + "'";
//Connect to database and retrieve
SqlCEDataAccess POSdbAccess = new SqlCEDataAccess();
DataSet ResultSet;
DataTable ResultTable;
ResultSet = POSdbAccess.dsGetData(SQL_str, "POSitemMaster");
ResultTable = ResultSet.Tables[0];
return(ResultTable);
}
public String OrdersToday(string orderStatus) //OrdersToday
{
string SQL_str = "select Convert(nvarchar(10), " +
"DatePart(year, dCreatedDate)) + '-' + Convert(nvarchar(10), " +
"DatePart(month, dCreatedDate)) + '-' + Convert(nvarchar(10), DatePart(day, dCreatedDate))" +
" OrderDate,Count(iOrderID) OrderCount" +
" 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, "POSordersToday");
ResultTable = ResultSet.Tables[0];
String RtnString;
// SalesValue = (from DataRow dr in ResultTable.Rows where dr["Sales"] == EnvironmentVariableTarget select (int)dr["id"].FirstOrDefault);
if (ResultTable.Rows.Count > 0 && ResultTable.Rows != null)
{
// OrderCount = (Int32)ResultTable.Rows[0]["OrderCount"];
// RtnString = OrderCount.ToString();
RtnString = ResultTable.Rows[0]["OrderCount"].ToString();
}
else
{
RtnString = "0";
}
return(RtnString);
}
public bool ItemAdd(ItemListForDisplay InputRecordObj) //Add Item
{
InputRecordObj.itemID = RetrieveNextItemID();
string SQL_str = "Insert into POS_ItemMaster ([itemID],[vItemSku],[vItemDesc1]," +
"[vItemDesc2]," +
"[vItemPrice]," +
"[vItemRelatedID]," +
"[vItemMinPrice]," +
"[vItemVendor]," +
"[vItemVendorContact]," +
"[vItemVendorPhone]," +
"[vItemNotes]," +
"[vItemStatus]," +
"[vItemAvailability]," +
"[dStartDate]," +
"[dEndDate]," +
"[vEntryBy]," +
"[tTimestamp]," +
"[POS_ItemCategoryid]," +
"[vItemmodifier]" +
") values (@itemID" +
",@vItemSku" +
",@vItemDesc1" +
",@vItemDesc2" +
",@vItemPrice" +
",@vItemRelatedID" +
",@vItemMinPrice" +
",@vItemVendor" +
",@vItemVendorContact" +
",@vItemVendorPhone" +
",@vItemNotes" +
",@vItemStatus" +
",@vItemAvailability" +
",@dStartDate" +
",@dEndDate" +
",@vEntryBy" +
",@tTimestamp" +
",@POS_ItemCategoryid" +
",@vItemmodifier" +
")";
//Connect to database and retrieve
SqlCEDataAccess POSdbAccess = new SqlCEDataAccess();
POSdbAccess.connection.Execute(SQL_str, new
{
InputRecordObj.itemID,
InputRecordObj.vItemSku,
InputRecordObj.vItemDesc1,
InputRecordObj.vItemDesc2,
InputRecordObj.vItemPrice,
InputRecordObj.vItemRelatedID,
InputRecordObj.vItemMinPrice,
InputRecordObj.vItemVendor,
InputRecordObj.vItemVendorContact,
InputRecordObj.vItemVendorPhone,
InputRecordObj.vItemNotes,
InputRecordObj.vItemStatus,
InputRecordObj.vItemAvailability,
InputRecordObj.dStartDate,
InputRecordObj.dEndDate,
InputRecordObj.vEntryBy,
InputRecordObj.tTimestamp,
InputRecordObj.POS_ItemCategoryId,
InputRecordObj.vItemmodifier
});
bool Result;
Result = true;
return(Result);
}
public bool CustomerAdd(CustomerListForDisplay InputRecordObj) //Add Customer
{
string _vItemStatus = "Publish";
string SQL_str = "Insert Into POS_Customer (id" +
",iCustid" +
",vCustName" +
",vCustContactNme" +
",vCustPrimaryPh" +
",vCustPhone2" +
",vCustPhone3" +
",vCustPhone4" +
",vCustFax1" +
",vCustFax2" +
",vCustEmail" +
",vCustAddress1" +
",vCustAddress2" +
",vCustCity" +
",vCustState" +
",vCustCountry" +
",vCustZipCode" +
",vCustBillAddress1" +
",vCustBillAddress2" +
",vCustBillCity" +
",vCustBillCountry" +
",vCustBillZipCode" +
",vCustShipAddress1" +
",vCustShipAddress2" +
",vCustShipCity" +
",vCustShipState" +
",vCustShipCountry" +
",vCustShipZipCode" +
",vCustAccountNum" +
",vCustNote1" +
",vCustNote2" +
",vCustNote3" +
",vCustCategory" +
",vCustComments" +
",vCustStatus" +
",vEntryBy" +
",tTimestamp" +
",POS_Setup_Id)" +
"Values ( " + "@id" +
",@iCustid" +
",@vCustName" +
",@vCustContactNme" +
",@vCustPrimaryPh" +
",@vCustPhone2" +
",@vCustPhone3" +
",@vCustPhone4" +
",@vCustFax1" +
",@vCustFax2" +
",@vCustEmail" +
",@vCustAddress1" +
",@vCustAddress2" +
",@vCustCity" +
",@vCustState" +
",@vCustCountry" +
",@vCustZipCode" +
",@vCustBillAddress1" +
",@vCustBillAddress2" +
",@vCustBillCity" +
",@vCustBillCountry" +
",@vCustBillZipCode" +
",@vCustShipAddress1" +
",@vCustShipAddress2" +
",@vCustShipCity" +
",@vCustShipState" +
",@vCustShipCountry" +
",@vCustShipZipCode" +
",@vCustAccountNum" +
",@vCustNote1" +
",@vCustNote2" +
",@vCustNote3" +
",@vCustCategory" +
",@vCustComments" +
",@vCustStatus" +
",@vEntryBy" +
",@tTimestamp" +
",@POS_Setup_Id)" ;
//Connect to database and retrieve
SqlCEDataAccess POSdbAccess = new SqlCEDataAccess();
bool Result;
DataTable ResultTable;
Result = POSdbAccess.execScalar(SQL_str);
//ResultTable = ResultSet.Tables[0];
return Result;
}
public bool CustomerUpdate(CustomerListForDisplay InputRecordObj) //Update Customer
{
string _vItemStatus = "Publish";
string SQL_str = "Update POS_Customer set " +
"[vCustName]=@vCustName" +
",[vCustContactNme]=@vCustContactNme" +
",[vCustPrimaryPh]=@vCustPrimaryPh" +
",[vCustPhone2]=@vCustPhone2" +
",[vCustPhone3]=@vCustPhone3" +
",[vCustPhone4]=@vCustPhone4" +
",[vCustFax1]=@vCustFax1" +
",[vCustFax2]=@vCustFax2" +
",[vCustEmail]=@vCustEmail" +
",[vCustAddress1]=@vCustAddress1" +
",[vCustAddress2]=@vCustAddress2" +
",[vCustCity]=@vCustCity" +
",[vCustState]=@vCustState" +
",[vCustCountry]=@vCustCountry" +
",[vCustZipCode]=@vCustZipCode" +
",[vCustBillAddress1]=@vCustBillAddress1" +
",[vCustBillAddress2]=@vCustBillAddress2" +
",[vCustBillCity]=@vCustBillCity" +
",[vCustBillCountry]=@vCustBillCountry" +
",[vCustBillZipCode]=@vCustBillZipCode" +
",[vCustShipAddress1]=@vCustShipAddress1" +
",[vCustShipAddress2]=@vCustShipAddress2" +
",[vCustShipCity]=@vCustShipCity" +
",[vCustShipState]=@vCustShipState" +
",[vCustShipCountry]=@vCustShipCountry" +
",[vCustShipZipCode]=@vCustShipZipCode" +
",[vCustAccountNum]=@vCustAccountNum" +
",[vCustNote1]=@vCustNote1" +
",[vCustNote2]=@vCustNote2" +
",[vCustNote3]=@vCustNote3" +
",[vCustCategory]=@vCustCategory" +
",[vCustComments]=@vCustComments" +
",[vCustStatus]=@vCustStatus" +
",[vEntryBy]=@vEntryBy" +
",[tTimestamp]=@tTimestamp" +
" Where id = " + InputRecordObj.Id;
//Connect to database and retrieve
SqlCEDataAccess POSdbAccess = new SqlCEDataAccess();
POSdbAccess.connection.Execute(SQL_str, new {InputRecordObj.vCustName
,InputRecordObj.iCustid
,
InputRecordObj.vCustContactNme
,
InputRecordObj.vCustPrimaryPh
,
InputRecordObj.vCustPhone2
,
InputRecordObj.vCustPhone3
,
InputRecordObj.vCustPhone4
,
InputRecordObj.vCustFax1
,
InputRecordObj.vCustFax2
,
InputRecordObj.vCustEmail
,
InputRecordObj.vCustAddress1
,
InputRecordObj.vCustAddress2
,
InputRecordObj.vCustCity
,
InputRecordObj.vCustState
,
InputRecordObj.vCustCountry
,
InputRecordObj.vCustZipCode
,
InputRecordObj.vCustBillAddress1
,
InputRecordObj.vCustBillAddress2
,
InputRecordObj.vCustBillCity
,
InputRecordObj.vCustBillCountry
,
InputRecordObj.vCustBillZipCode
,
InputRecordObj.vCustShipAddress1
,
InputRecordObj.vCustShipAddress2
,
InputRecordObj.vCustShipCity
,
InputRecordObj.vCustShipState
,
InputRecordObj.vCustShipCountry
,
InputRecordObj.vCustShipZipCode
,
InputRecordObj.vCustAccountNum
,
InputRecordObj.vCustNote1
,
InputRecordObj.vCustNote2
,
InputRecordObj.vCustNote3
,
InputRecordObj.vCustCategory
,
InputRecordObj.vCustComments
,
InputRecordObj.vCustStatus
,
InputRecordObj.vEntryBy
,
InputRecordObj.tTimestamp
});
bool Result;
Result = true;
// DataTable ResultTable;
// Result = POSdbAccess.execScalar(SQL_str);
//ResultTable = ResultSet.Tables[0];
return Result;
}
