// Retrieve manufactures list from database
public List <ManufacturerProperties> getManufacturersList(string status)
{
ManufacturerProperties manufacturerProperties;
List <ManufacturerProperties> manufacturersList = new List <ManufacturerProperties>();
DataSet resultDataset;
try {
string selectQueryString = "SELECT MANUFACTURER_ID, MANUFACTURER_NAME, MANUFACTURER_DETAIL, MANUFACTURER_STATUS FROM MANUFACTURER WHERE MANUFACTURER_STATUS=@STATUS";
List <KeyValuePair <string, string> > tableParamsValues = new List <KeyValuePair <string, string> >();
tableParamsValues.Add(new KeyValuePair <string, string>("@STATUS", status));
resultDataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, tableParamsValues);
if (resultDataset != null)
{
foreach (DataRow resultrow in resultDataset.Tables[0].Rows)
{
manufacturerProperties = new ManufacturerProperties();
manufacturerProperties.Manufacturer_Id = resultrow["Manufacturer_Id"].ToString();
manufacturerProperties.Manufacturer_Name = resultrow["Manufacturer_Name"].ToString();
manufacturerProperties.Manufacturer_Detail = resultrow["Manufacturer_Detail"].ToString();
manufacturerProperties.Manufacturer_Status = resultrow["manufacturer_Status"].ToString();
manufacturersList.Add(manufacturerProperties);
}
}
}catch (Exception exp) { Console.WriteLine(exp.StackTrace); }
return(manufacturersList);
}
public List <OrderReturnProperties> getMultipleReturnOrderInfo(string status)
{
DataSet dataset = new DataSet();
String selectQueryString = "SELECT RETURN_ORDER_ID,ORDER_ID,VENDOR_ID,USER_ID,PRODUCT_ID,PRODUCT_TYPE,COUNT,PRICE_PER_UNIT,RETURN_DESCRIPTION,RETURN_STATUS,CREATED_BY FROM RETURN_ORDER WHERE RETURN_STATUS=@RETURN_STATUS;";
List <OrderReturnProperties> orderReturnsList = new List <OrderReturnProperties>();
List <KeyValuePair <string, string> > queryParameter = new List <KeyValuePair <string, string> >();
queryParameter.Add(new KeyValuePair <string, string>("@RETURN_STATUS", status));
dataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, queryParameter);
if (dataset != null)
{
foreach (DataRow row in dataset.Tables[0].Rows)
{
OrderReturnProperties returnOrders = new OrderReturnProperties();
returnOrders.Return_Order_Id = row["Return_Order_ID"].ToString();
returnOrders.Order_Id = row["Order_Id"].ToString();
returnOrders.Vendor_Id = row["Vendor_Id"].ToString();
returnOrders.User_Id = row["User_Id"].ToString();
returnOrders.Product_Id = row["Product_Id"].ToString();
returnOrders.Product_Type = row["Product_Type"].ToString();
returnOrders.Count = Convert.ToInt16(row["Count"].ToString());
returnOrders.Price_Per_Unit = Convert.ToDouble(row["Price_Per_Unit"].ToString());
returnOrders.Return_Description = row["Return_Description"].ToString();
returnOrders.Return_Status = row["Return_status"].ToString();
returnOrders.Created_By = row["Created_By"].ToString();
orderReturnsList.Add(returnOrders);
}
}
return(orderReturnsList);
}
//Get single user profile details
public UserInformationProperties getSingleUserInfo(string userId)
{
DataSet dataset = new DataSet();
UserInformationProperties userinformation = new UserInformationProperties();
string selectQueryString = "SELECT NAME,CONTACT,ADDRESS,EMAIL_ID,USER_ID,PASSWORD,ROLE,IS_VENDOR,VENDOR_ID,VENDOR_NAME,STATUS,CREATED_BY,CREATED_DATE,MODIFIED_BY,MODIFIED_DATE FROM USER_TABLE WHERE USER_ID=@USER_ID";
List <KeyValuePair <string, string> > tableQueryData = new List <KeyValuePair <string, string> >();
tableQueryData.Add(new KeyValuePair <string, string>("@USER_ID", userId));
dataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, tableQueryData);
if (dataset != null)
{
foreach (DataRow row in dataset.Tables[0].Rows)
{
userinformation.Name = row["Name"].ToString();
userinformation.Contact = row["Contact"].ToString();
userinformation.Address = row["Address"].ToString();
userinformation.EMail_Id = row["Email_id"].ToString();
userinformation.User_Id = row["User_Id"].ToString();
userinformation.Password = row["Password"].ToString();
userinformation.Role = row["Role"].ToString();
userinformation.Is_Vendor = row["Is_Vendor"].ToString();
userinformation.Vendor_ID = row["Vendor_Id"].ToString();
userinformation.Vendor_Name = row["Vendor_Name"].ToString();
userinformation.Status = row["Status"].ToString();
userinformation.Created_By = row["Created_By"].ToString();
userinformation.Created_date = row["Created_date"].ToString();
userinformation.Modified_By = row["Modified_By"].ToString();
userinformation.Modified_Date = row["Modified_Date"].ToString();
}
}
return(userinformation);
}
//retrieving single vendor profile information from vendor table
public CustomerProperties getSingleCustomerInfo(string vendorId)
{
DataSet dataset = new DataSet();
CustomerProperties vendorInfo = new CustomerProperties();
string selectQueryString = "SELECT VENDOR_NAME,VENDOR_ID,VENDOR_ADDRESS,VENDOR_CONTACT,VENDOR_EMAIL_ID,VENDOR_STATUS,VENDOR_DISCOUNT_ID,VENDOR_BALANCE_AMOUNT FROM VENDOR WHERE VENDOR_ID=@VENDOR_ID";
List <KeyValuePair <string, string> > tableQueryData = new List <KeyValuePair <string, string> >();
tableQueryData.Add(new KeyValuePair <string, string>("@VENDOR_ID", vendorId));
dataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, tableQueryData);
if (dataset != null)
{
foreach (DataRow row in dataset.Tables[0].Rows)
{
vendorInfo.Vendor_Name = row["Vendor_Name"].ToString();
vendorInfo.Vendor_Id = row["Vendor_Id"].ToString();
vendorInfo.Vendor_Address = row["Vendor_Address"].ToString();
vendorInfo.Vendor_Contact = row["Vendor_Contact"].ToString();
vendorInfo.Vendor_EMail_Id = row["Vendor_Email_Id"].ToString();
vendorInfo.Vendor_Status = row["Vendor_Status"].ToString();
vendorInfo.Vendor_Discount_ID = row["Vendor_Discount_ID"].ToString();
vendorInfo.Vendor_Balance_Amount = Convert.ToDouble(row["Vendor_Balance_Amount"].ToString());
}
}
return(vendorInfo);
}
//Retrieve all the discount information from discount table
public List <DiscountProperties> getDiscountList()
{
System.Data.DataSet dataset = new System.Data.DataSet();
List <DiscountProperties> discountList = new List <DiscountProperties>();
string selectQueryString = "SELECT DISCOUNT_ID,DISCOUNT_RATE,CREATED_BY,CREATED_DATE,MODIFY_BY,MODIFY_DATE FROM DISOCUNT";
dataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, null);
if (dataset != null)
{
foreach (System.Data.DataRow row in dataset.Tables[0].Rows)
{
DiscountProperties discountData = new DiscountProperties();
discountData.Discount_Id = row["Discount_Id"].ToString();
discountData.Discount_Rate = row["Discount_rate"].ToString();
discountData.audit_Information.createdBy = row["Created_by"].ToString();
discountData.audit_Information.createdOn = row["Created_date"].ToString();
discountData.audit_Information.modifiedBy = row["Modify_by"].ToString();
discountData.audit_Information.modifiedOn = row["Modify_date"].ToString();
discountList.Add(discountData);
}
}
return(discountList);
}
//retrive Product details from database
public Dictionary <string, List <ProductProperties> > getProducts()
{
DataSet resultDataset;
Dictionary <string, List <ProductProperties> > productCollection = new Dictionary <string, List <ProductProperties> >();
try {
List <ProductProperties> productList = new List <ProductProperties>();
string selectQueryString = "SELECT PRODUCT.PRODUCT_ID,PRODUCT_TYPE,PRODUCT.PRODUCT_NAME,PRODUCT.MINIMUM_COUNT,PRODUCT.PRODUCT_CURRENT_COUNT,PRODUCT.PRICE_PER_UNIT,MANUFACTURER.MANUFACTURER_ID, MANUFACTURER.MANUFACTURER_NAME FROM PRODUCT INNER JOIN MANUFACTURER ON PRODUCT.MANUFACTURER_ID = MANUFACTURER.MANUFACTURER_ID WHERE MANUFACTURER.MANUFACTURER_STATUS='ACTIVE' ORDER BY MANUFACTURER.MANUFACTURER_NAME;";
resultDataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, null);
if (resultDataset != null)
{
foreach (DataRow resultrow in resultDataset.Tables[0].Rows)
{
ProductProperties productProperties = new ProductProperties();
productProperties.Product_Id = resultrow["Product_id"].ToString();
productProperties.Product_Type = resultrow["Product_type"].ToString();
productProperties.Product_Name = resultrow["Product_name"].ToString();
productProperties.Minimum_Count = int.Parse(resultrow["Minimum_count"].ToString());
productProperties.Product_Current_Count = int.Parse(resultrow["Product_Current_Count"].ToString());
productProperties.Price_Per_Unit = double.Parse(resultrow["Price_per_unit"].ToString());
productProperties.Manufacturer_Id = resultrow["Manufacturer_id"].ToString();
productProperties.Manufacturer_Name = resultrow["Manufacturer_Name"].ToString();
productList.Add(productProperties);
_product_type.Add(productProperties.Product_Type);
_product_list.Add(productProperties);
}
}
else
{
productList = null;
}
//LINQ query to get the distict string value of manufacturers in List collection type
List <string> manufacturer_id = (from manId in productList select manId.Manufacturer_Id).Distinct().ToList();
foreach (string manf_id in manufacturer_id)
{
List <ProductProperties> tempProductList = new List <ProductProperties>();
foreach (ProductProperties props in productList)
{
if (manf_id.Equals(props.Manufacturer_Id))
{
tempProductList.Add(props);
}
}
productCollection.Add(manf_id, tempProductList);
}
_inventory_list = productCollection;
}catch (Exception exp)
{
Console.WriteLine(exp.StackTrace);
}
return(productCollection);
}
public List <OrderProperties> getMultipleOrdersInfo(string vendorInfo, string orderStatus)
{
DataSet dataset = new DataSet();
string selectQueryString = null;
if (orderStatus == null)
{
selectQueryString = "SELECT ORDER_ID,VENDOR_ID,MANUFACTURER_NAME,PRODUCT_ID,PRODUCT_TYPE,PRODUCT_NAME,COUNT,PRICE_PER_UNIT,TOTAL_PRICE,PAID_AMOUNT,BALANCE_AMOUNT,DISCOUNT_RATE,ORDER_STATUS,DESCRIPTION,CREATED_DATE FROM SALES_ORDER WHERE VENDOR_ID=@VENDOR_ID ORDER BY ORDER_ID";
List <KeyValuePair <string, string> > queryParameter = new List <KeyValuePair <string, string> >();
queryParameter.Add(new KeyValuePair <string, string>("@VENDOR_ID", vendorInfo.ToString()));
dataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, queryParameter);
}
else if (vendorInfo == null)
{
selectQueryString = "SELECT ORDER_ID,VENDOR_ID,MANUFACTURER_NAME,PRODUCT_ID,PRODUCT_TYPE,PRODUCT_NAME,COUNT,PRICE_PER_UNIT,TOTAL_PRICE,PAID_AMOUNT,BALANCE_AMOUNT,DISCOUNT_RATE,ORDER_STATUS,DESCRIPTION,CREATED_DATE FROM SALES_ORDER WHERE ORDER_STATUS=@ORDER_STATUS ORDER BY ORDER_ID";
List <KeyValuePair <string, string> > queryParameter = new List <KeyValuePair <string, string> >();
queryParameter.Add(new KeyValuePair <string, string>("@ORDER_STATUS", orderStatus));
dataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, queryParameter);
}
else if ((vendorInfo != null) && (orderStatus != null))
{
selectQueryString = "SELECT ORDER_ID,VENDOR_ID,MANUFACTURER_NAME,PRODUCT_ID,PRODUCT_TYPE,PRODUCT_NAME,COUNT,PRICE_PER_UNIT,TOTAL_PRICE,PAID_AMOUNT,BALANCE_AMOUNT,DISCOUNT_RATE,ORDER_STATUS,DESCRIPTION,CREATED_DATE FROM SALES_ORDER WHERE VENDOR_ID=@VENDOR_ID AND ORDER_STATUS=@ORDER_STATUS ORDER BY ORDER_ID";
List <KeyValuePair <string, string> > queryParameter = new List <KeyValuePair <string, string> >();
queryParameter.Add(new KeyValuePair <string, string>("@VENDOR_ID", vendorInfo.ToString()));
queryParameter.Add(new KeyValuePair <string, string>("@ORDER_STATUS", orderStatus.ToString()));
dataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, queryParameter);
}
List <OrderProperties> listOfOrders = new List <OrderProperties>();
if (dataset != null)
{
foreach (DataRow row in dataset.Tables[0].Rows)
{
OrderProperties orderPropereties = new OrderProperties();
orderPropereties.Order_Id = row["Order_Id"].ToString();
orderPropereties.Vendor_Id = row["Vendor_Id"].ToString();
orderPropereties.Manufacturer_Name = row["Manufacturer_Name"].ToString();
orderPropereties.Product_Id = row["Product_Id"].ToString();
orderPropereties.Product_Name = row["Product_Name"].ToString();
orderPropereties.Product_Type = row["Product_Type"].ToString();
orderPropereties.Count = Convert.ToInt16(row["Count"].ToString());
orderPropereties.Price_Per_Unit = Convert.ToDouble(row["Price_Per_Unit"].ToString());
orderPropereties.Total_Price = Convert.ToDouble(row["Total_Price"].ToString());
orderPropereties.Paid_Amount = Convert.ToDouble(row["Paid_Amount"].ToString());
orderPropereties.Balance_Amount = Convert.ToDouble(row["Balance_Amount"].ToString());
orderPropereties.Description = row["Description"].ToString();
orderPropereties.Order_Status = row["Order_Status"].ToString();
orderPropereties.OrderDate = row["Created_Date"].ToString().Substring(0, 11);
orderPropereties.Discount_Rate = row["Discount_Rate"].ToString();
listOfOrders.Add(orderPropereties);
}
}
return(listOfOrders);
}
//retrieve products of a manufacturer from database
public List <ProductProperties> getManufacturerProductsList(ProductProperties manufacturer)
{
DataSet resultDataset;
List <ProductProperties> manufacturerProducts = new List <ProductProperties>();
try {
string selectQueryString = "SELECT PRODUCT.PRODUCT_ID,PRODUCT_TYPE,PRODUCT.PRODUCT_NAME,PRODUCT.PRODUCT_CURRENT_COUNT,PRODUCT.PRICE_PER_UNIT,PRODUCT.MINIMUM_COUNT,MANUFACTURER.MANUFACTURER_ID, MANUFACTURER.MANUFACTURER_NAME,MANUFACTURER_DETAIL FROM PRODUCT INNER JOIN MANUFACTURER ON PRODUCT.MANUFACTURER_ID = MANUFACTURER.MANUFACTURER_ID WHERE MANUFACTURER.MANUFACTURER_ID = @MANFACTURERID ORDER BY MANUFACTURER.MANUFACTURER_NAME;";
List <KeyValuePair <string, string> > tableQueryData = new List <KeyValuePair <string, string> >();
tableQueryData.Add(new KeyValuePair <string, string>("@MANFACTURERID", manufacturer.Manufacturer_Id));
resultDataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, tableQueryData);
}catch (Exception ex)
{
Console.WriteLine(ex.StackTrace);
}
return(manufacturerProducts);
}
//Retrieve multiple user information from database
public List <UserInformationProperties> getMultipleUserInfo(string userStatus)
{
DataSet dataset = new DataSet();
List <UserInformationProperties> usersList = new List <UserInformationProperties>();
string selectQueryString = null;
if (userStatus.Equals("ALL"))
{
selectQueryString = "SELECT NAME,CONTACT,ADDRESS,EMAIL_ID,USER_ID,ROLE,IS_VENDOR,VENDOR_ID,VENDOR_NAME,STATUS,CREATED_BY,CREATED_DATE,MODIFIED_BY,MODIFIED_DATE FROM USER_TABLE";
dataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, null);
}
else
{
selectQueryString = "SELECT NAME,CONTACT,ADDRESS,EMAIL_ID,ROLE,USER_ID,IS_VENDOR,VENDOR_ID,VENDOR_NAME,STATUS,CREATED_BY,CREATED_DATE,MODIFIED_BY,MODIFIED_DATE FROM USER_TABLE WHERE STATUS=@STATUS";
List <KeyValuePair <string, string> > tableQueryData = new List <KeyValuePair <string, string> >();
tableQueryData.Add(new KeyValuePair <string, string>("@STATUS", userStatus));
dataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, tableQueryData);
}
if (dataset != null)
{
foreach (DataRow row in dataset.Tables[0].Rows)
{
UserInformationProperties userinformation = new UserInformationProperties();
userinformation.Name = row["Name"].ToString();
userinformation.Contact = row["Contact"].ToString();
userinformation.Address = row["Address"].ToString();
userinformation.EMail_Id = row["Email_id"].ToString();
userinformation.User_Id = row["User_Id"].ToString();
userinformation.Role = row["Role"].ToString();
userinformation.Is_Vendor = row["Is_Vendor"].ToString();
userinformation.Vendor_ID = row["Vendor_Id"].ToString();
userinformation.Vendor_Name = row["Vendor_Name"].ToString();
userinformation.Status = row["Status"].ToString();
userinformation.Created_By = row["Created_By"].ToString();
userinformation.Created_date = row["Created_date"].ToString();
userinformation.Modified_By = row["Modified_By"].ToString();
userinformation.Modified_Date = row["Modified_Date"].ToString();
usersList.Add(userinformation);
}
}
return(usersList);
}
public static DiscountProperties getDiscountOfCustomer(string vendor_Discount_Id)
{
System.Data.DataSet dataset = new System.Data.DataSet();
DiscountProperties discount = new DiscountProperties();
string selectQueryString = "SELECT DISCOUNT_RATE,DISCOUNT_ID FROM DISCOUNT WHERE DISCOUNT_ID=@DISCOUNT_ID;";
List <KeyValuePair <string, string> > parameters = new List <KeyValuePair <string, string> >();
parameters.Add(new KeyValuePair <string, string>("@DISCOUNT_ID", vendor_Discount_Id));
dataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, parameters);
if (dataset != null)
{
foreach (System.Data.DataRow row in dataset.Tables[0].Rows)
{
discount.Discount_Id = row["Discount_Id"].ToString();
discount.Discount_Rate = row["Discount_Rate"].ToString();
}
}
return(discount);
}
//Validate user login with database
public UserInformationProperties validateUserLogin(LoginProperties userCreds)
{
DataSet reader;
List <KeyValuePair <string, string> > tableQueryData = new List <KeyValuePair <string, string> >();
UserInformationProperties userProfileInformation = new UserInformationProperties();
try
{
string queryString = "SELECT NAME,CONTACT,ADDRESS,EMAIL_ID,USER_ID,PASSWORD,ROLE,IS_VENDOR,VENDOR_ID,VENDOR_NAME,STATUS FROM USER_TABLE WHERE USER_ID=@USERID AND PASSWORD=@PASSWORD AND STATUS='ACTIVE'";
tableQueryData.Add(new KeyValuePair <string, string>("@USERID", userCreds.Username));
tableQueryData.Add(new KeyValuePair <string, string>("@PASSWORD", userCreds.Password));
reader = DatabaseConnectionHandler.executeSelectQuery(queryString, tableQueryData);
if (reader != null)
{
userProfileInformation = getSingleUserInfo(userCreds.Username);
}
}
catch (Exception ex) { Console.WriteLine("Login issue occured", ex.Message); }
return(userProfileInformation);
}