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