public SqlDataReader GetMaxCatID()
{
String query = "SELECT MAX([ITEMCAT_ID]) FROM [dbo].[Item_Category]";
DataReaderManager drm = new DataReaderManager();
return(drm.getDataReader(query));
}
public SqlDataReader GetDiscountForTotal()
{
String query = "SELECT [AMOUNT_FROM] ,[AMOUNT_TO],[DISCOUNT_PERIOADICALLY] ,[DISCOUNT] ,[DISCOUNT_TYPE] ,[DISCOUNT_FROM] ,[DISCOUNT_TO],[DISCOUNT_ID] FROM [dbo].[Discount_Type2]";
DataReaderManager drm = new DataReaderManager();
return(drm.getDataReader(query));
}
public SqlDataReader GetItemList()
{
String query = "SELECT [ITEM_ID],[ITEM_NAME] FROM [dbo].[Items]";
SqlDataAdapter adapter = new SqlDataAdapter();
DataReaderManager drm = new DataReaderManager();
return(drm.getDataReader(query));
}
public SqlDataReader GetMaxExchangeNo()
{
String query = "SELECT MAX([INVOICE_NO]) FROM [POS].[dbo].[Transaction] WHERE [TRANSACTION_TYPE]='Exchange'";
DataReaderManager drm = new DataReaderManager();
return(drm.getDataReader(query));
}
public SqlDataReader GetDailySales(DateTime DateVal)
{
String query = "DECLARE @return_value int EXEC @return_value = [dbo].[sp_rpt_Daily_Sales] @Date = '"+ DateVal.ToShortDateString() + "' SELECT 'Return Value' = @return_value";
DataReaderManager drm = new DataReaderManager();
return(drm.getDataReader(query));
}
public SqlDataReader GetItemDetails(String Item_ID)
{
String query = "SELECT [ITEM_NAME],[ITEM_DISCRIPTION],[ITEM_STATUS] FROM [dbo].[Items] where [ITEM_ID]=@Item_ID";
SqlParameter sqlParam = new SqlParameter("@Item_ID", SqlDbType.Int);
sqlParam.Value = Item_ID;
DataReaderManager drm = new DataReaderManager();
return(drm.getDataReader(query, ref sqlParam));
}
public SqlDataReader GetCategoryDetailsByBarcodeAll(String Barcode)
{
String query = "SELECT Item_Category.BARCODE, Item_Category.CATEGORY_NAME, Item_Category.CAT_DISCRIPTION, Item_Category.UNIT, Item_Category.BUYING_COST, Item_Category.SELLING_COST,Item_Category.CAT_STATUS, Items.ITEM_NAME,Item_Category.DISCOUNT,Item_Category.DISCOUNT_TYPE,BRAND.BRAND_NAME,BRAND.BRAND_ID FROM Item_Category INNER JOIN Brand ON Brand.BRAND_ID=Item_Category.BRAND_ID INNER JOIN Items ON Items.ITEM_ID = Item_Category.ITEM_ID where Item_Category.BARCODE = @Barcode";
SqlParameter sqlParam = new SqlParameter("@Barcode", SqlDbType.VarChar);
sqlParam.Value = Barcode;
DataReaderManager drm = new DataReaderManager();
return(drm.getDataReader(query, ref sqlParam));
}
public SqlDataReader GetBrandDetails(String Item_ID)
{
String query = "SELECT [BRAND_ID],[BRAND_NAME],[BRAND_STATUS] FROM [dbo].[Brand] WHERE [ITEM_ID]=@ITEM_ID";
SqlParameter sqlParam = new SqlParameter("@Item_ID", SqlDbType.Int);
sqlParam.Value = Item_ID;
DataReaderManager drm = new DataReaderManager();
return(drm.getDataReader(query, ref sqlParam));
}
public SqlDataReader GetItemCatagoryDetailsByBrand(String Brand_ID)
{
String query = "SELECT Items.ITEM_NAME, Item_Category.CATEGORY_NAME, Item_Category.CAT_DISCRIPTION, Item_Category.UNIT, Item_Category.BUYING_COST, Item_Category.SELLING_COST,Item_Category.CAT_STATUS, Item_Category.DISCOUNT,Item_Category.DISCOUNT_TYPE,BRAND.BRAND_NAME,BRAND.BRAND_ID,[DISCOUNT_FROM] ,[DISCOUNT_TO] ,[DISCOUNT_PERIOADICALLY],Item_Category.ITEMCAT_ID FROM Item_Category INNER JOIN Brand ON Brand.BRAND_ID=Item_Category.BRAND_ID INNER JOIN Items ON Items.ITEM_ID = Item_Category.ITEM_ID where Item_Category.BRAND_ID = @Brand_ID";
SqlParameter sqlParam = new SqlParameter("@Brand_ID", SqlDbType.Int);
sqlParam.Value = Brand_ID;
DataReaderManager drm = new DataReaderManager();
return(drm.getDataReader(query, ref sqlParam));
}
public SqlDataReader GetInvoiceDetails(String INVOICE_NO)
{
String query = "SELECT[Transaction].TRANSACTION_ID, Item_Category.BARCODE, Item_Category.CATEGORY_NAME, Item_Category.UNIT, Item_Category.SELLING_COST / [Transaction].CAT_QUANTITY AS UNIT_PRICE, [Transaction].DISCOUNT_AMOUNT, [Transaction].CAT_QUANTITY, [Transaction].NET_AMOUNT,[Transaction].ITEMCAT_ID FROM[Transaction] INNER JOIN Item_Category ON [Transaction].ITEMCAT_ID = Item_Category.ITEMCAT_ID WHERE([Transaction].TRANSACTION_TYPE = 'Customer_Invoice') AND [TRANSACTION_STATUS]='1' AND([Transaction].INVOICE_NO = @INVOICE_NO)";
SqlParameter sqlParam = new SqlParameter("@INVOICE_NO", SqlDbType.Int);
sqlParam.Value = INVOICE_NO;
DataReaderManager drm = new DataReaderManager();
return(drm.getDataReader(query, ref sqlParam));
}
public SqlDataReader GetStockBalance(int ITEMCAT_ID)
{
String query = "SELECT [BALANCE] FROM [POS].[dbo].[Stock_Balance] WHERE [ITEMCAT_ID]=@ITEMCAT_ID";
SqlParameter sqlParam = new SqlParameter("@ITEMCAT_ID", SqlDbType.Int);
sqlParam.Value = ITEMCAT_ID;
DataReaderManager drm = new DataReaderManager();
return(drm.getDataReader(query, ref sqlParam));
}
public SqlDataReader GetRecallInvoiceDetails(String INVOICE_NO)
{
String query = "SELECT SUM([Transaction].NET_AMOUNT) FROM[Transaction] INNER JOIN Item_Category ON[Transaction].ITEMCAT_ID = Item_Category.ITEMCAT_ID WHERE([Transaction].TRANSACTION_TYPE = 'Customer_Invoice') AND([Transaction].INVOICE_NO = @INVOICE_NO) AND([Transaction].[TRANSACTION_STATUS] = '0')";
SqlParameter sqlParam = new SqlParameter("@INVOICE_NO", SqlDbType.Int);
sqlParam.Value = INVOICE_NO;
DataReaderManager drm = new DataReaderManager();
return(drm.getDataReader(query, ref sqlParam));
}
public SqlDataReader GetVenderDetails(String Vender_ID)
{
String query = "SELECT [VENDER_NAME],[ADDRES_LINE_1],[ADDRES_LINE_2],[ADDRES_LINE_3],[TELEPHONE_1],[TELEPHONE_2],[BUISSNESS_REG]FROM [dbo].[Vender_Details]where [VENDER_ID]=@VENDER_ID";
SqlParameter sqlParam = new SqlParameter("@VENDER_ID", SqlDbType.Int);
sqlParam.Value = Vender_ID;
DataReaderManager drm = new DataReaderManager();
return(drm.getDataReader(query, ref sqlParam));
}
public SqlDataReader GetBrandDetailsbyID(String Brand_ID)
{
String query = "SELECT [BRAND_NAME],[BRAND_DISCRIPTION],[BRAND_STATUS] FROM [dbo].[Brand] where [BRAND_ID] = @BRAND_ID";
SqlParameter sqlParam = new SqlParameter("@BRAND_ID", SqlDbType.Int);
sqlParam.Value = Brand_ID;
DataReaderManager drm = new DataReaderManager();
return(drm.getDataReader(query, ref sqlParam));
}
public SqlDataReader GetCategoryDetails(String BRAND_ID)
{
String query = "SELECT [ITEMCAT_ID] ,[CATAGORY_NAME] FROM [dbo].[Item_Catagory] WHERE [BRAND_ID]=@BRAND_ID";
SqlParameter sqlParam = new SqlParameter("@BRAND_ID", SqlDbType.Int);
sqlParam.Value = BRAND_ID;
DataReaderManager drm = new DataReaderManager();
return(drm.getDataReader(query, ref sqlParam));
}
public SqlDataReader GetUserDetails(String NIC)
{
String query = "SELECT [USER_NAME] ,[STATUS] ,[USER_TYPE] ,[FIRST_NAME] ,[LAST_NAME] ,[CONTACT_NO] ,[ADDRESS_NO] ,[STREET] ,[CITY] ,[DISTRICT] ,[STORE_ID] FROM [POS].[dbo].[User_Details] WHERE NIC=@NIC";
SqlParameter sqlParam = new SqlParameter("@NIC", SqlDbType.Char);
sqlParam.Value = NIC;
DataReaderManager drm = new DataReaderManager();
return(drm.getDataReader(query, ref sqlParam));
}
public SqlDataReader GetDiscountForTotal(Decimal TotalAmount)
{
String query = "SELECT [DISCOUNT_PERIOADICALLY] ,[DISCOUNT] ,[DISCOUNT_TYPE] ,[DISCOUNT_FROM] ,[DISCOUNT_TO],[DISCOUNT_ID] FROM [dbo].[Discount_Type2] WHERE [AMOUNT_FROM] <= @TotalAmount AND [AMOUNT_TO]>=@TotalAmount";
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@TotalAmount", SqlDbType.Decimal),
};
sqlParams[0].Value = TotalAmount;
DataReaderManager drm = new DataReaderManager();
return(drm.getDataReader(query, ref sqlParams));
}
public SqlDataReader getVoucherbyBrCode(String BARCODE)
{
String selectCommand = "SELECT [ID],[VOUCHER_NO],[AMOUNT],[VOUCHER_STATUS] FROM [POS].[dbo].[Voucher] WHERE [BARCODE]=@BARCODE";
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@BARCODE", SqlDbType.VarChar)
};
sqlParams[0].Value = BARCODE;
DataReaderManager drm = new DataReaderManager();
return(drm.getDataReader(selectCommand, ref sqlParams));
}
public SqlDataReader GetItemCatagoryDetails_Combobox(String Item_ID, String Brand_ID)
{
String query = "SELECT Item_Category.ITEMCAT_ID, Item_Category.CATEGORY_NAME FROM [dbo].[Item_Category] where Item_Category.ITEM_ID = @ItemID AND Item_Category.BRAND_ID = @BrandID";
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@ItemID", SqlDbType.Int),
new SqlParameter("@BrandID", SqlDbType.Int),
};
sqlParams[0].Value = Item_ID;
sqlParams[1].Value = Brand_ID;
DataReaderManager drm = new DataReaderManager();
return(drm.getDataReader(query, ref sqlParams));
}
public SqlDataReader CheckLogin(String USER_NAME, String PASSWORD)
{
String selectCommand = "SELECT [USER_NAME] FROM [User_Details] WHERE [USER_NAME] = @username AND [PASSWORD] = @password";
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@username", SqlDbType.VarChar),
new SqlParameter("@password", SqlDbType.VarChar)
};
sqlParams[0].Value = USER_NAME;
sqlParams[1].Value = PASSWORD;
DataReaderManager drm = new DataReaderManager();
return(drm.getDataReader(selectCommand, ref sqlParams));
}