//public bool ApplyPromoValue(ContactDetails clsContactDetails, Int64 ProductID, Int64 VariationMatrixID, out PromoTypes PromoType, out decimal PromoQuantity, out decimal PromoValue, out bool InPercent, int BranchID = 0) //{ // Int64 ContactID = clsContactDetails.ContactID; // string ContactIDs = ""; // if (clsContactDetails.ContactID != Constants.ZERO) // { // if (!string.IsNullOrEmpty(ContactIDs)) // ContactIDs = "," + clsContactDetails.ContactID.ToString(); // else // ContactIDs = clsContactDetails.ContactID.ToString(); // } // // rewardcardmember // if (clsContactDetails.RewardDetails.RewardCardStatus == RewardCardStatus.ManualActivated) // { // if (!string.IsNullOrEmpty(ContactIDs)) // ContactIDs = "," + Constants.PLUSCARDMEMBERSID_STRING; // else // ContactIDs = Constants.PLUSCARDMEMBERSID_STRING; // } // // icc card members // if (clsContactDetails.CreditDetails.GuarantorID == Constants.ZERO && // clsContactDetails.CreditDetails.CreditCardStatus == CreditCardStatus.ManualActivated) // { // if (!string.IsNullOrEmpty(ContactIDs)) // ContactIDs = "," + Constants.PLUSCARDMEMBERSID_STRING; // else // ContactIDs = Constants.PLUSCARDMEMBERSID_STRING; // } // PromoType = PromoTypes.NotApplicable; // PromoQuantity = 0; // PromoValue = 0; // InPercent = false; // bool boHasPromo = false; // try // { // Data.Products clsProduct = new Data.Products(base.Connection, base.Transaction); // Data.ProductDetails clsProductDetails = clsProduct.Details1(BranchID, ProductID); // Int64 ProductSubGroupID = clsProductDetails.ProductSubGroupID; // Int64 ProductGroupID = clsProductDetails.ProductGroupID; // MySqlCommand cmd = new MySqlCommand(); // cmd.CommandType = System.Data.CommandType.Text; // string SQL = "SELECT " + // "PromoID " + // "FROM tblPromo " + // "WHERE 1=1 " + // "AND Status = 1 " + // "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + // "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; // cmd.CommandText = SQL; // string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName); // base.MySqlDataAdapterFill(cmd, dt); // foreach (System.Data.DataRow dr in dt.Rows) // { // boHasPromo = true; // break; // } // if (boHasPromo == false) //return agad if no Promo is affected by date // return boHasPromo; // /*******************************Up to Contact, Group, Sub, Prod and VarM ID only*****************************/ // SQL = "SELECT " + // "PromoItemsID, " + // "a.PromoID, " + // "PromoTypeID, " + // "ProductGroupID, " + // "ProductSubGroupID, " + // "ProductID, " + // "VariationMatrixID, " + // "Quantity, " + // "PromoValue, " + // "InPercent " + // "FROM tblPromoItems a " + // "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + // "WHERE ContactID = @ContactID " + // "AND ProductGroupID = @ProductGroupID " + // "AND ProductSubGroupID = @ProductSubGroupID " + // "AND ProductID = @ProductID " + // "AND VariationMatrixID = @VariationMatrixID " + // "AND Status = 1 " + // "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + // "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; // cmd = new MySqlCommand(); // cmd.CommandType = System.Data.CommandType.Text; // cmd.Parameters.AddWithValue("@ContactID", ContactID); // cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID); // cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); // cmd.Parameters.AddWithValue("@ProductID", ProductID); // cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); // cmd.CommandText = SQL; // dt = new System.Data.DataTable(strDataTableName); // base.MySqlDataAdapterFill(cmd, dt); // foreach (System.Data.DataRow dr in dt.Rows) // { // PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); // PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); // PromoValue = decimal.Parse(dr["PromoValue"].ToString()); // InPercent = bool.Parse(dr["InPercent"].ToString()); // return boHasPromo; // } // /*******************************Up to Contact, Sub, Prod and VariationsMatrix ID only*****************************/ // SQL = "SELECT " + // "PromoItemsID, " + // "a.PromoID, " + // "PromoTypeID, " + // "ProductGroupID, " + // "ProductSubGroupID, " + // "ProductID, " + // "VariationMatrixID, " + // "Quantity, " + // "PromoValue, " + // "InPercent " + // "FROM tblPromoItems a " + // "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + // "WHERE ContactID = @ContactID " + // "AND ProductGroupID = 0 " + // "AND ProductSubGroupID = @ProductSubGroupID " + // "AND ProductID = @ProductID " + // "AND VariationMatrixID = @VariationMatrixID " + // "AND Status = 1 " + // "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + // "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; // cmd = new MySqlCommand(); // cmd.CommandType = System.Data.CommandType.Text; // cmd.Parameters.AddWithValue("@ContactID", ContactID); // cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); // cmd.Parameters.AddWithValue("@ProductID", ProductID); // cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); // cmd.CommandText = SQL; // dt = new System.Data.DataTable(strDataTableName); // base.MySqlDataAdapterFill(cmd, dt); // foreach (System.Data.DataRow dr in dt.Rows) // { // PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); // PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); // PromoValue = decimal.Parse(dr["PromoValue"].ToString()); // InPercent = bool.Parse(dr["InPercent"].ToString()); // return boHasPromo; // } // /*******************************Up to Contact, Prod and VariationsMatrix ID only*****************************/ // SQL = "SELECT " + // "PromoItemsID, " + // "a.PromoID, " + // "PromoTypeID, " + // "ProductGroupID, " + // "ProductSubGroupID, " + // "ProductID, " + // "VariationMatrixID, " + // "Quantity, " + // "PromoValue, " + // "InPercent " + // "FROM tblPromoItems a " + // "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + // "WHERE ContactID = @ContactID " + // "AND ProductGroupID = 0 " + // "AND ProductSubGroupID = 0 " + // "AND ProductID = @ProductID " + // "AND VariationMatrixID = @VariationMatrixID " + // "AND Status = 1 " + // "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + // "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; // cmd = new MySqlCommand(); // cmd.CommandType = System.Data.CommandType.Text; // cmd.Parameters.AddWithValue("@ContactID", ContactID); // cmd.Parameters.AddWithValue("@ProductID", ProductID); // cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); // cmd.CommandText = SQL; // dt = new System.Data.DataTable(strDataTableName); // base.MySqlDataAdapterFill(cmd, dt); // foreach (System.Data.DataRow dr in dt.Rows) // { // PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); // PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); // PromoValue = decimal.Parse(dr["PromoValue"].ToString()); // InPercent = bool.Parse(dr["InPercent"].ToString()); // return boHasPromo; // } // /*******************************Up to Contact, VariationsMatrix ID only*****************************/ // SQL = "SELECT " + // "PromoItemsID, " + // "a.PromoID, " + // "PromoTypeID, " + // "ProductGroupID, " + // "ProductSubGroupID, " + // "ProductID, " + // "VariationMatrixID, " + // "Quantity, " + // "PromoValue, " + // "InPercent " + // "FROM tblPromoItems a " + // "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + // "WHERE ContactID = @ContactID " + // "AND ProductGroupID = 0 " + // "AND ProductSubGroupID = 0 " + // "AND ProductID = 0 " + // "AND VariationMatrixID = @VariationMatrixID " + // "AND Status = 1 " + // "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + // "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; // cmd = new MySqlCommand(); // cmd.CommandType = System.Data.CommandType.Text; // cmd.Parameters.AddWithValue("@ContactID", ContactID); // cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); // cmd.CommandText = SQL; // dt = new System.Data.DataTable(strDataTableName); // base.MySqlDataAdapterFill(cmd, dt); // foreach (System.Data.DataRow dr in dt.Rows) // { // PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); // PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); // PromoValue = decimal.Parse(dr["PromoValue"].ToString()); // InPercent = bool.Parse(dr["InPercent"].ToString()); // return boHasPromo; // } // /*******************************Up to Contact, Group, Sub, Prod ID only*****************************/ // SQL = "SELECT " + // "PromoItemsID, " + // "a.PromoID, " + // "PromoTypeID, " + // "ProductGroupID, " + // "ProductSubGroupID, " + // "ProductID, " + // "VariationMatrixID, " + // "Quantity, " + // "PromoValue, " + // "InPercent " + // "FROM tblPromoItems a " + // "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + // "WHERE ContactID = @ContactID " + // "AND ProductGroupID = @ProductGroupID " + // "AND ProductSubGroupID = @ProductSubGroupID " + // "AND ProductID = @ProductID " + // "AND VariationMatrixID = 0 " + // "AND Status = 1 " + // "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + // "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; // cmd = new MySqlCommand(); // cmd.CommandType = System.Data.CommandType.Text; // cmd.Parameters.AddWithValue("@ContactID", ContactID); // cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID); // cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); // cmd.Parameters.AddWithValue("@ProductID", ProductID); // cmd.CommandText = SQL; // dt = new System.Data.DataTable(strDataTableName); // base.MySqlDataAdapterFill(cmd, dt); // foreach (System.Data.DataRow dr in dt.Rows) // { // PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); // PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); // PromoValue = decimal.Parse(dr["PromoValue"].ToString()); // InPercent = bool.Parse(dr["InPercent"].ToString()); // return boHasPromo; // } // /*******************************Up to Contact, Sub, Prod ID only*****************************/ // SQL = "SELECT " + // "PromoItemsID, " + // "a.PromoID, " + // "PromoTypeID, " + // "ProductGroupID, " + // "ProductSubGroupID, " + // "ProductID, " + // "VariationMatrixID, " + // "Quantity, " + // "PromoValue, " + // "InPercent " + // "FROM tblPromoItems a " + // "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + // "WHERE ContactID = @ContactID " + // "AND ProductGroupID = 0 " + // "AND ProductSubGroupID = @ProductSubGroupID " + // "AND ProductID = @ProductID " + // "AND VariationMatrixID = 0 " + // "AND Status = 1 " + // "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + // "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; // cmd = new MySqlCommand(); // cmd.CommandType = System.Data.CommandType.Text; // cmd.Parameters.AddWithValue("@ContactID", ContactID); // cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); // cmd.Parameters.AddWithValue("@ProductID", ProductID); // cmd.CommandText = SQL; // dt = new System.Data.DataTable(strDataTableName); // base.MySqlDataAdapterFill(cmd, dt); // foreach (System.Data.DataRow dr in dt.Rows) // { // PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); // PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); // PromoValue = decimal.Parse(dr["PromoValue"].ToString()); // InPercent = bool.Parse(dr["InPercent"].ToString()); // return boHasPromo; // } // /*******************************Up to Contact, Prod ID only*****************************/ // SQL = "SELECT " + // "PromoItemsID, " + // "a.PromoID, " + // "PromoTypeID, " + // "ProductGroupID, " + // "ProductSubGroupID, " + // "ProductID, " + // "VariationMatrixID, " + // "Quantity, " + // "PromoValue, " + // "InPercent " + // "FROM tblPromoItems a " + // "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + // "WHERE ContactID = @ContactID " + // "AND ProductGroupID = 0 " + // "AND ProductSubGroupID = 0 " + // "AND ProductID = @ProductID " + // "AND VariationMatrixID = 0 " + // "AND Status = 1 " + // "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + // "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; // cmd = new MySqlCommand(); // cmd.CommandType = System.Data.CommandType.Text; // cmd.Parameters.AddWithValue("@ContactID", ContactID); // cmd.Parameters.AddWithValue("@ProductID", ProductID); // cmd.CommandText = SQL; // dt = new System.Data.DataTable(strDataTableName); // base.MySqlDataAdapterFill(cmd, dt); // foreach (System.Data.DataRow dr in dt.Rows) // { // PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); // PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); // PromoValue = decimal.Parse(dr["PromoValue"].ToString()); // InPercent = bool.Parse(dr["InPercent"].ToString()); // return boHasPromo; // } // /*******************************Up to Contact, Group, Sub only*****************************/ // SQL = "SELECT " + // "PromoItemsID, " + // "a.PromoID, " + // "PromoTypeID, " + // "ProductGroupID, " + // "ProductSubGroupID, " + // "ProductID, " + // "VariationMatrixID, " + // "Quantity, " + // "PromoValue, " + // "InPercent " + // "FROM tblPromoItems a " + // "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + // "WHERE ContactID = @ContactID " + // "AND ProductGroupID = @ProductGroupID " + // "AND ProductSubGroupID = @ProductSubGroupID " + // "AND ProductID = 0 " + // "AND VariationMatrixID = 0 " + // "AND Status = 1 " + // "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + // "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; // cmd = new MySqlCommand(); // cmd.CommandType = System.Data.CommandType.Text; // cmd.Parameters.AddWithValue("@ContactID", ContactID); // cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID); // cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); // cmd.CommandText = SQL; // dt = new System.Data.DataTable(strDataTableName); // base.MySqlDataAdapterFill(cmd, dt); // foreach (System.Data.DataRow dr in dt.Rows) // { // PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); // PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); // PromoValue = decimal.Parse(dr["PromoValue"].ToString()); // InPercent = bool.Parse(dr["InPercent"].ToString()); // return boHasPromo; // } // /*******************************Up to Contact, Sub only*****************************/ // SQL = "SELECT " + // "PromoItemsID, " + // "a.PromoID, " + // "PromoTypeID, " + // "ProductGroupID, " + // "ProductSubGroupID, " + // "ProductID, " + // "VariationMatrixID, " + // "Quantity, " + // "PromoValue, " + // "InPercent " + // "FROM tblPromoItems a " + // "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + // "WHERE ContactID = @ContactID " + // "AND ProductGroupID = 0 " + // "AND ProductSubGroupID = @ProductSubGroupID " + // "AND ProductID = 0 " + // "AND VariationMatrixID = 0 " + // "AND Status = 1 " + // "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + // "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; // cmd = new MySqlCommand(); // cmd.CommandType = System.Data.CommandType.Text; // cmd.Parameters.AddWithValue("@ContactID", ContactID); // cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); // cmd.CommandText = SQL; // dt = new System.Data.DataTable(strDataTableName); // base.MySqlDataAdapterFill(cmd, dt); // foreach (System.Data.DataRow dr in dt.Rows) // { // PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); // PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); // PromoValue = decimal.Parse(dr["PromoValue"].ToString()); // InPercent = bool.Parse(dr["InPercent"].ToString()); // return boHasPromo; // } // /*******************************Up to Contact only*****************************/ // SQL = "SELECT " + // "PromoItemsID, " + // "a.PromoID, " + // "PromoTypeID, " + // "ProductGroupID, " + // "ProductSubGroupID, " + // "ProductID, " + // "VariationMatrixID, " + // "Quantity, " + // "PromoValue, " + // "InPercent " + // "FROM tblPromoItems a " + // "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + // "WHERE ContactID = @ContactID " + // "AND ProductGroupID = 0 " + // "AND ProductSubGroupID = 0 " + // "AND ProductID = 0 " + // "AND VariationMatrixID = 0 " + // "AND Status = 1 " + // "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + // "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; // cmd = new MySqlCommand(); // cmd.CommandType = System.Data.CommandType.Text; // cmd.Parameters.AddWithValue("@ContactID", ContactID); // cmd.CommandText = SQL; // dt = new System.Data.DataTable(strDataTableName); // base.MySqlDataAdapterFill(cmd, dt); // foreach (System.Data.DataRow dr in dt.Rows) // { // PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); // PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); // PromoValue = decimal.Parse(dr["PromoValue"].ToString()); // InPercent = bool.Parse(dr["InPercent"].ToString()); // return boHasPromo; // } // /*******************************Up to Group, Sub, Prod and VarM ID only*****************************/ // SQL = "SELECT " + // "PromoItemsID, " + // "a.PromoID, " + // "PromoTypeID, " + // "ProductGroupID, " + // "ProductSubGroupID, " + // "ProductID, " + // "VariationMatrixID, " + // "Quantity, " + // "PromoValue, " + // "InPercent " + // "FROM tblPromoItems a " + // "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + // "WHERE ContactID = 0 " + // "AND ProductGroupID = @ProductGroupID " + // "AND ProductSubGroupID = @ProductSubGroupID " + // "AND ProductID = @ProductID " + // "AND VariationMatrixID = @VariationMatrixID " + // "AND Status = 1 " + // "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + // "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; // cmd = new MySqlCommand(); // cmd.CommandType = System.Data.CommandType.Text; // cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID); // cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); // cmd.Parameters.AddWithValue("@ProductID", ProductID); // cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); // cmd.CommandText = SQL; // dt = new System.Data.DataTable(strDataTableName); // base.MySqlDataAdapterFill(cmd, dt); // foreach (System.Data.DataRow dr in dt.Rows) // { // PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); // PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); // PromoValue = decimal.Parse(dr["PromoValue"].ToString()); // InPercent = bool.Parse(dr["InPercent"].ToString()); // return boHasPromo; // } // /*******************************Up to Sub, Prod and VariationMatrix ID only*****************************/ // SQL = "SELECT " + // "PromoItemsID, " + // "a.PromoID, " + // "PromoTypeID, " + // "ProductGroupID, " + // "ProductSubGroupID, " + // "ProductID, " + // "VariationMatrixID, " + // "Quantity, " + // "PromoValue, " + // "InPercent " + // "FROM tblPromoItems a " + // "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + // "WHERE ContactID = 0 " + // "AND ProductGroupID =0 " + // "AND ProductSubGroupID = @ProductSubGroupID " + // "AND ProductID = @ProductID " + // "AND VariationMatrixID = @VariationMatrixID " + // "AND Status = 1 " + // "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + // "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; // cmd = new MySqlCommand(); // cmd.CommandType = System.Data.CommandType.Text; // cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); // cmd.Parameters.AddWithValue("@ProductID", ProductID); // cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); // cmd.CommandText = SQL; // dt = new System.Data.DataTable(strDataTableName); // base.MySqlDataAdapterFill(cmd, dt); // foreach (System.Data.DataRow dr in dt.Rows) // { // PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); // PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); // PromoValue = decimal.Parse(dr["PromoValue"].ToString()); // InPercent = bool.Parse(dr["InPercent"].ToString()); // return boHasPromo; // } // /*******************************Up to Prod and VariationMatrix ID only*****************************/ // SQL = "SELECT " + // "PromoItemsID, " + // "a.PromoID, " + // "PromoTypeID, " + // "ProductGroupID, " + // "ProductSubGroupID, " + // "ProductID, " + // "VariationMatrixID, " + // "Quantity, " + // "PromoValue, " + // "InPercent " + // "FROM tblPromoItems a " + // "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + // "WHERE ContactID = 0 " + // "AND ProductGroupID = 0 " + // "AND ProductSubGroupID = 0 " + // "AND ProductID = @ProductID " + // "AND VariationMatrixID = @VariationMatrixID " + // "AND Status = 1 " + // "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + // "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; // cmd = new MySqlCommand(); // cmd.CommandType = System.Data.CommandType.Text; // cmd.Parameters.AddWithValue("@ProductID", ProductID); // cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); // cmd.CommandText = SQL; // dt = new System.Data.DataTable(strDataTableName); // base.MySqlDataAdapterFill(cmd, dt); // foreach (System.Data.DataRow dr in dt.Rows) // { // PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); // PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); // PromoValue = decimal.Parse(dr["PromoValue"].ToString()); // InPercent = bool.Parse(dr["InPercent"].ToString()); // return boHasPromo; // } // /*******************************Up to VariationsMatrix ID only*****************************/ // SQL = "SELECT " + // "PromoItemsID, " + // "a.PromoID, " + // "PromoTypeID, " + // "ProductGroupID, " + // "ProductSubGroupID, " + // "ProductID, " + // "VariationMatrixID, " + // "Quantity, " + // "PromoValue, " + // "InPercent " + // "FROM tblPromoItems a " + // "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + // "WHERE ContactID = 0 " + // "AND ProductGroupID = 0 " + // "AND ProductSubGroupID = 0 " + // "AND ProductID = 0 " + // "AND VariationMatrixID = @VariationMatrixID " + // "AND Status = 1 " + // "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + // "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; // cmd = new MySqlCommand(); // cmd.CommandType = System.Data.CommandType.Text; // cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); // cmd.CommandText = SQL; // dt = new System.Data.DataTable(strDataTableName); // base.MySqlDataAdapterFill(cmd, dt); // foreach (System.Data.DataRow dr in dt.Rows) // { // PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); // PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); // PromoValue = decimal.Parse(dr["PromoValue"].ToString()); // InPercent = bool.Parse(dr["InPercent"].ToString()); // return boHasPromo; // } // /*******************************Up to group, Sub, Prod ID only*****************************/ // SQL = "SELECT " + // "PromoItemsID, " + // "a.PromoID, " + // "PromoTypeID, " + // "ProductGroupID, " + // "ProductSubGroupID, " + // "ProductID, " + // "VariationMatrixID, " + // "Quantity, " + // "PromoValue, " + // "InPercent " + // "FROM tblPromoItems a " + // "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + // "WHERE ContactID = 0 " + // "AND ProductGroupID = @ProductGroupID " + // "AND ProductSubGroupID = @ProductSubGroupID " + // "AND ProductID = @ProductID " + // "AND VariationMatrixID = 0 " + // "AND Status = 1 " + // "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + // "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; // cmd = new MySqlCommand(); // cmd.CommandType = System.Data.CommandType.Text; // cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID); // cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); // cmd.Parameters.AddWithValue("@ProductID", ProductID); // cmd.CommandText = SQL; // dt = new System.Data.DataTable(strDataTableName); // base.MySqlDataAdapterFill(cmd, dt); // foreach (System.Data.DataRow dr in dt.Rows) // { // PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); // PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); // PromoValue = decimal.Parse(dr["PromoValue"].ToString()); // InPercent = bool.Parse(dr["InPercent"].ToString()); // return boHasPromo; // } // /*******************************Up to Sub, Prod ID only*****************************/ // SQL = "SELECT " + // "PromoItemsID, " + // "a.PromoID, " + // "PromoTypeID, " + // "ProductGroupID, " + // "ProductSubGroupID, " + // "ProductID, " + // "VariationMatrixID, " + // "Quantity, " + // "PromoValue, " + // "InPercent " + // "FROM tblPromoItems a " + // "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + // "WHERE ContactID = 0 " + // "AND ProductGroupID = 0 " + // "AND ProductSubGroupID = @ProductSubGroupID " + // "AND ProductID = @ProductID " + // "AND VariationMatrixID = 0 " + // "AND Status = 1 " + // "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + // "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; // cmd = new MySqlCommand(); // cmd.CommandType = System.Data.CommandType.Text; // cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); // cmd.Parameters.AddWithValue("@ProductID", ProductID); // cmd.CommandText = SQL; // dt = new System.Data.DataTable(strDataTableName); // base.MySqlDataAdapterFill(cmd, dt); // foreach (System.Data.DataRow dr in dt.Rows) // { // PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); // PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); // PromoValue = decimal.Parse(dr["PromoValue"].ToString()); // InPercent = bool.Parse(dr["InPercent"].ToString()); // return boHasPromo; // } // /*******************************Up to group, Sub, Prod and VariationMatrix ID only*****************************/ // SQL = "SELECT " + // "PromoItemsID, " + // "a.PromoID, " + // "PromoTypeID, " + // "ProductGroupID, " + // "ProductSubGroupID, " + // "ProductID, " + // "VariationMatrixID, " + // "Quantity, " + // "PromoValue, " + // "InPercent " + // "FROM tblPromoItems a " + // "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + // "WHERE ContactID = 0 " + // "AND ProductGroupID = 0 " + // "AND ProductSubGroupID = 0 " + // "AND ProductID = @ProductID " + // "AND VariationMatrixID = 0 " + // "AND Status = 1 " + // "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + // "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; // cmd = new MySqlCommand(); // cmd.CommandType = System.Data.CommandType.Text; // cmd.Parameters.AddWithValue("@ProductID", ProductID); // cmd.CommandText = SQL; // dt = new System.Data.DataTable(strDataTableName); // base.MySqlDataAdapterFill(cmd, dt); // foreach (System.Data.DataRow dr in dt.Rows) // { // PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); // PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); // PromoValue = decimal.Parse(dr["PromoValue"].ToString()); // InPercent = bool.Parse(dr["InPercent"].ToString()); // return boHasPromo; // } // /*******************************Up to group, Sub ID only*****************************/ // SQL = "SELECT " + // "PromoItemsID, " + // "a.PromoID, " + // "PromoTypeID, " + // "ProductGroupID, " + // "ProductSubGroupID, " + // "ProductID, " + // "VariationMatrixID, " + // "Quantity, " + // "PromoValue, " + // "InPercent " + // "FROM tblPromoItems a " + // "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + // "WHERE ContactID = 0 " + // "AND ProductGroupID = @ProductGroupID " + // "AND ProductSubGroupID = @ProductSubGroupID " + // "AND ProductID = 0 " + // "AND VariationMatrixID = 0 " + // "AND Status = 1 " + // "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + // "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; // cmd = new MySqlCommand(); // cmd.CommandType = System.Data.CommandType.Text; // cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID); // cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); // cmd.CommandText = SQL; // dt = new System.Data.DataTable(strDataTableName); // base.MySqlDataAdapterFill(cmd, dt); // foreach (System.Data.DataRow dr in dt.Rows) // { // PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); // PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); // PromoValue = decimal.Parse(dr["PromoValue"].ToString()); // InPercent = bool.Parse(dr["InPercent"].ToString()); // return boHasPromo; // } // /*******************************Up to Sub ID only*****************************/ // SQL = "SELECT " + // "PromoItemsID, " + // "a.PromoID, " + // "PromoTypeID, " + // "ProductGroupID, " + // "ProductSubGroupID, " + // "ProductID, " + // "VariationMatrixID, " + // "Quantity, " + // "PromoValue, " + // "InPercent " + // "FROM tblPromoItems a " + // "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + // "WHERE ContactID = 0 " + // "AND ProductGroupID = 0 " + // "AND ProductSubGroupID = @ProductSubGroupID " + // "AND ProductID = 0 " + // "AND VariationMatrixID = 0 " + // "AND Status = 1 " + // "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + // "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; // cmd = new MySqlCommand(); // cmd.CommandType = System.Data.CommandType.Text; // cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); // cmd.CommandText = SQL; // dt = new System.Data.DataTable(strDataTableName); // base.MySqlDataAdapterFill(cmd, dt); // foreach (System.Data.DataRow dr in dt.Rows) // { // PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); // PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); // PromoValue = decimal.Parse(dr["PromoValue"].ToString()); // InPercent = bool.Parse(dr["InPercent"].ToString()); // return boHasPromo; // } // /*******************************Up to group ID only*****************************/ // SQL = "SELECT " + // "PromoItemsID, " + // "a.PromoID, " + // "PromoTypeID, " + // "ProductGroupID, " + // "ProductSubGroupID, " + // "ProductID, " + // "VariationMatrixID, " + // "Quantity, " + // "PromoValue, " + // "InPercent " + // "FROM tblPromoItems a " + // "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + // "WHERE ContactID = 0 " + // "AND ProductGroupID = @ProductGroupID " + // "AND ProductSubGroupID = 0 " + // "AND ProductID = 0 " + // "AND VariationMatrixID = 0 " + // "AND Status = 1 " + // "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + // "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; // cmd = new MySqlCommand(); // cmd.CommandType = System.Data.CommandType.Text; // cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID); // cmd.CommandText = SQL; // dt = new System.Data.DataTable(strDataTableName); // base.MySqlDataAdapterFill(cmd, dt); // foreach (System.Data.DataRow dr in dt.Rows) // { // PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); // PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); // PromoValue = decimal.Parse(dr["PromoValue"].ToString()); // InPercent = bool.Parse(dr["InPercent"].ToString()); // return boHasPromo; // } // /*******************************Up to all only*****************************/ // SQL = "SELECT " + // "PromoItemsID, " + // "a.PromoID, " + // "PromoTypeID, " + // "ProductGroupID, " + // "ProductSubGroupID, " + // "ProductID, " + // "VariationMatrixID, " + // "Quantity, " + // "PromoValue, " + // "InPercent " + // "FROM tblPromoItems a " + // "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + // "WHERE ContactID = 0 " + // "AND ProductGroupID = 0 " + // "AND ProductSubGroupID = 0 " + // "AND ProductID = 0 " + // "AND VariationMatrixID = 0 " + // "AND Status = 1 " + // "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + // "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; // cmd = new MySqlCommand(); // cmd.CommandType = System.Data.CommandType.Text; // cmd.CommandText = SQL; // dt = new System.Data.DataTable(strDataTableName); // base.MySqlDataAdapterFill(cmd, dt); // foreach (System.Data.DataRow dr in dt.Rows) // { // PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); // PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); // PromoValue = decimal.Parse(dr["PromoValue"].ToString()); // InPercent = bool.Parse(dr["InPercent"].ToString()); // return boHasPromo; // } // } // catch (Exception ex) // { // base.ThrowException(ex); // } // return false; //} public bool ApplyPromoValue(ContactDetails clsContactDetails, Int64 ProductID, Int64 VariationMatrixID, out PromoTypes PromoType, out decimal PromoQuantity, out decimal PromoValue, out bool InPercent, int BranchID = 0) { Int64 ContactID = clsContactDetails.ContactID; string strSpecialContactIDs = ""; // rewardcardmember // make sure that it is not the default if (clsContactDetails.ContactID != Constants.C_RETAILPLUS_CUSTOMERID && clsContactDetails.RewardDetails.RewardActive) { if (!string.IsNullOrEmpty(strSpecialContactIDs)) strSpecialContactIDs += "," + Constants.PLUSCARDMEMBERSID_STRING; else strSpecialContactIDs = Constants.PLUSCARDMEMBERSID_STRING; } // icc card members // make sure that it is not the default if (clsContactDetails.ContactID != Constants.C_RETAILPLUS_CUSTOMERID && clsContactDetails.CreditDetails.GuarantorID == Constants.ZERO && clsContactDetails.CreditDetails.CreditActive) { if (!string.IsNullOrEmpty(strSpecialContactIDs)) strSpecialContactIDs += "," + Constants.ICCARDMEMBERSID_STRING; else strSpecialContactIDs = Constants.ICCARDMEMBERSID_STRING; } // gcc card members // make sure that it is not the default if (clsContactDetails.ContactID != Constants.C_RETAILPLUS_CUSTOMERID && clsContactDetails.CreditDetails.GuarantorID != Constants.ZERO && clsContactDetails.CreditDetails.CreditActive) { if (!string.IsNullOrEmpty(strSpecialContactIDs)) strSpecialContactIDs += "," + Constants.GCCARDMEMBERSID_STRING; else strSpecialContactIDs = Constants.GCCARDMEMBERSID_STRING; } PromoType = PromoTypes.NotApplicable; PromoQuantity = 0; PromoValue = 0; InPercent = false; bool boHasPromo = false; try { Data.Products clsProduct = new Data.Products(base.Connection, base.Transaction); Data.ProductDetails clsProductDetails = clsProduct.Details1(BranchID, ProductID); Int64 ProductSubGroupID = clsProductDetails.ProductSubGroupID; Int64 ProductGroupID = clsProductDetails.ProductGroupID; MySqlCommand cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; string SQL = "SELECT " + "PromoID " + "FROM tblPromo " + "WHERE 1=1 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');"; cmd.CommandText = SQL; string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { boHasPromo = true; break; } if (boHasPromo == false) //return agad if no Promo is affected by date return boHasPromo; /*******************************Up to Contact, Group, Sub, Prod and VarM ID only*****************************/ SQL = "SELECT " + "PromoItemsID, " + "a.PromoID, " + "PromoTypeID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "Quantity, " + "PromoValue, " + "InPercent " + "FROM tblPromoItems a " + "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + "WHERE " + (string.IsNullOrEmpty(strSpecialContactIDs) ? "ContactID = @ContactID " : "ContactID IN (@ContactID, " + strSpecialContactIDs + ") ") + "AND ProductGroupID = @ProductGroupID " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = @ProductID " + "AND VariationMatrixID = @VariationMatrixID " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@ContactID", ContactID); cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID); cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); PromoValue = decimal.Parse(dr["PromoValue"].ToString()); InPercent = bool.Parse(dr["InPercent"].ToString()); return boHasPromo; } /*******************************Up to Contact, Sub, Prod and VariationsMatrix ID only*****************************/ SQL = "SELECT " + "PromoItemsID, " + "a.PromoID, " + "PromoTypeID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "Quantity, " + "PromoValue, " + "InPercent " + "FROM tblPromoItems a " + "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + "WHERE " + (string.IsNullOrEmpty(strSpecialContactIDs) ? "ContactID = @ContactID " : "ContactID IN (@ContactID, " + strSpecialContactIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = @ProductID " + "AND VariationMatrixID = @VariationMatrixID " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@ContactID", ContactID); cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); PromoValue = decimal.Parse(dr["PromoValue"].ToString()); InPercent = bool.Parse(dr["InPercent"].ToString()); return boHasPromo; } /*******************************Up to Contact, Prod and VariationsMatrix ID only*****************************/ SQL = "SELECT " + "PromoItemsID, " + "a.PromoID, " + "PromoTypeID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "Quantity, " + "PromoValue, " + "InPercent " + "FROM tblPromoItems a " + "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + "WHERE " + (string.IsNullOrEmpty(strSpecialContactIDs) ? "ContactID = @ContactID " : "ContactID IN (@ContactID, " + strSpecialContactIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = 0 " + "AND ProductID = @ProductID " + "AND VariationMatrixID = @VariationMatrixID " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@ContactID", ContactID); cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); PromoValue = decimal.Parse(dr["PromoValue"].ToString()); InPercent = bool.Parse(dr["InPercent"].ToString()); return boHasPromo; } /*******************************Up to Contact, VariationsMatrix ID only*****************************/ SQL = "SELECT " + "PromoItemsID, " + "a.PromoID, " + "PromoTypeID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "Quantity, " + "PromoValue, " + "InPercent " + "FROM tblPromoItems a " + "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + "WHERE " + (string.IsNullOrEmpty(strSpecialContactIDs) ? "ContactID = @ContactID " : "ContactID IN (@ContactID, " + strSpecialContactIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = 0 " + "AND ProductID = 0 " + "AND VariationMatrixID = @VariationMatrixID " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@ContactID", ContactID); cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); PromoValue = decimal.Parse(dr["PromoValue"].ToString()); InPercent = bool.Parse(dr["InPercent"].ToString()); return boHasPromo; } /*******************************Up to Contact, Group, Sub, Prod ID only*****************************/ SQL = "SELECT " + "PromoItemsID, " + "a.PromoID, " + "PromoTypeID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "Quantity, " + "PromoValue, " + "InPercent " + "FROM tblPromoItems a " + "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + "WHERE " + (string.IsNullOrEmpty(strSpecialContactIDs) ? "ContactID = @ContactID " : "ContactID IN (@ContactID, " + strSpecialContactIDs + ") ") + "AND ProductGroupID = @ProductGroupID " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = @ProductID " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@ContactID", ContactID); cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID); cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); PromoValue = decimal.Parse(dr["PromoValue"].ToString()); InPercent = bool.Parse(dr["InPercent"].ToString()); return boHasPromo; } /*******************************Up to Contact, Sub, Prod ID only*****************************/ SQL = "SELECT " + "PromoItemsID, " + "a.PromoID, " + "PromoTypeID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "Quantity, " + "PromoValue, " + "InPercent " + "FROM tblPromoItems a " + "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + "WHERE " + (string.IsNullOrEmpty(strSpecialContactIDs) ? "ContactID = @ContactID " : "ContactID IN (@ContactID, " + strSpecialContactIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = @ProductID " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@ContactID", ContactID); cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); PromoValue = decimal.Parse(dr["PromoValue"].ToString()); InPercent = bool.Parse(dr["InPercent"].ToString()); return boHasPromo; } /*******************************Up to Contact, Prod ID only*****************************/ SQL = "SELECT " + "PromoItemsID, " + "a.PromoID, " + "PromoTypeID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "Quantity, " + "PromoValue, " + "InPercent " + "FROM tblPromoItems a " + "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + "WHERE " + (string.IsNullOrEmpty(strSpecialContactIDs) ? "ContactID = @ContactID " : "ContactID IN (@ContactID, " + strSpecialContactIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = 0 " + "AND ProductID = @ProductID " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@ContactID", ContactID); cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); PromoValue = decimal.Parse(dr["PromoValue"].ToString()); InPercent = bool.Parse(dr["InPercent"].ToString()); return boHasPromo; } /*******************************Up to Contact, Group, Sub only*****************************/ SQL = "SELECT " + "PromoItemsID, " + "a.PromoID, " + "PromoTypeID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "Quantity, " + "PromoValue, " + "InPercent " + "FROM tblPromoItems a " + "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + "WHERE " + (string.IsNullOrEmpty(strSpecialContactIDs) ? "ContactID = @ContactID " : "ContactID IN (@ContactID, " + strSpecialContactIDs + ") ") + "AND ProductGroupID = @ProductGroupID " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = 0 " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@ContactID", ContactID); cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID); cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); PromoValue = decimal.Parse(dr["PromoValue"].ToString()); InPercent = bool.Parse(dr["InPercent"].ToString()); return boHasPromo; } /*******************************Up to Contact, Sub only*****************************/ SQL = "SELECT " + "PromoItemsID, " + "a.PromoID, " + "PromoTypeID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "Quantity, " + "PromoValue, " + "InPercent " + "FROM tblPromoItems a " + "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + "WHERE " + (string.IsNullOrEmpty(strSpecialContactIDs) ? "ContactID = @ContactID " : "ContactID IN (@ContactID, " + strSpecialContactIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = 0 " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@ContactID", ContactID); cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); PromoValue = decimal.Parse(dr["PromoValue"].ToString()); InPercent = bool.Parse(dr["InPercent"].ToString()); return boHasPromo; } /*******************************Up to Contact only*****************************/ SQL = "SELECT " + "PromoItemsID, " + "a.PromoID, " + "PromoTypeID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "Quantity, " + "PromoValue, " + "InPercent " + "FROM tblPromoItems a " + "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + "WHERE " + (string.IsNullOrEmpty(strSpecialContactIDs) ? "ContactID = @ContactID " : "ContactID IN (@ContactID, " + strSpecialContactIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = 0 " + "AND ProductID = 0 " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@ContactID", ContactID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); PromoValue = decimal.Parse(dr["PromoValue"].ToString()); InPercent = bool.Parse(dr["InPercent"].ToString()); return boHasPromo; } /*******************************Up to Group, Sub, Prod and VarM ID only*****************************/ SQL = "SELECT " + "PromoItemsID, " + "a.PromoID, " + "PromoTypeID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "Quantity, " + "PromoValue, " + "InPercent " + "FROM tblPromoItems a " + "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + "WHERE " + (string.IsNullOrEmpty(strSpecialContactIDs) ? "ContactID = 0 " : "ContactID IN (0, " + strSpecialContactIDs + ") ") + "AND ProductGroupID = @ProductGroupID " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = @ProductID " + "AND VariationMatrixID = @VariationMatrixID " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID); cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); PromoValue = decimal.Parse(dr["PromoValue"].ToString()); InPercent = bool.Parse(dr["InPercent"].ToString()); return boHasPromo; } /*******************************Up to Sub, Prod and VariationMatrix ID only*****************************/ SQL = "SELECT " + "PromoItemsID, " + "a.PromoID, " + "PromoTypeID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "Quantity, " + "PromoValue, " + "InPercent " + "FROM tblPromoItems a " + "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + "WHERE " + (string.IsNullOrEmpty(strSpecialContactIDs) ? "ContactID = 0 " : "ContactID IN (0, " + strSpecialContactIDs + ") ") + "AND ProductGroupID =0 " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = @ProductID " + "AND VariationMatrixID = @VariationMatrixID " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); PromoValue = decimal.Parse(dr["PromoValue"].ToString()); InPercent = bool.Parse(dr["InPercent"].ToString()); return boHasPromo; } /*******************************Up to Prod and VariationMatrix ID only*****************************/ SQL = "SELECT " + "PromoItemsID, " + "a.PromoID, " + "PromoTypeID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "Quantity, " + "PromoValue, " + "InPercent " + "FROM tblPromoItems a " + "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + "WHERE " + (string.IsNullOrEmpty(strSpecialContactIDs) ? "ContactID = 0 " : "ContactID IN (0, " + strSpecialContactIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = 0 " + "AND ProductID = @ProductID " + "AND VariationMatrixID = @VariationMatrixID " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); PromoValue = decimal.Parse(dr["PromoValue"].ToString()); InPercent = bool.Parse(dr["InPercent"].ToString()); return boHasPromo; } /*******************************Up to VariationsMatrix ID only*****************************/ SQL = "SELECT " + "PromoItemsID, " + "a.PromoID, " + "PromoTypeID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "Quantity, " + "PromoValue, " + "InPercent " + "FROM tblPromoItems a " + "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + "WHERE " + (string.IsNullOrEmpty(strSpecialContactIDs) ? "ContactID = 0 " : "ContactID IN (0, " + strSpecialContactIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = 0 " + "AND ProductID = 0 " + "AND VariationMatrixID = @VariationMatrixID " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); PromoValue = decimal.Parse(dr["PromoValue"].ToString()); InPercent = bool.Parse(dr["InPercent"].ToString()); return boHasPromo; } /*******************************Up to group, Sub, Prod ID only*****************************/ SQL = "SELECT " + "PromoItemsID, " + "a.PromoID, " + "PromoTypeID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "Quantity, " + "PromoValue, " + "InPercent " + "FROM tblPromoItems a " + "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + "WHERE " + (string.IsNullOrEmpty(strSpecialContactIDs) ? "ContactID = 0 " : "ContactID IN (0, " + strSpecialContactIDs + ") ") + "AND ProductGroupID = @ProductGroupID " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = @ProductID " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID); cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); PromoValue = decimal.Parse(dr["PromoValue"].ToString()); InPercent = bool.Parse(dr["InPercent"].ToString()); return boHasPromo; } /*******************************Up to Sub, Prod ID only*****************************/ SQL = "SELECT " + "PromoItemsID, " + "a.PromoID, " + "PromoTypeID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "Quantity, " + "PromoValue, " + "InPercent " + "FROM tblPromoItems a " + "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + "WHERE " + (string.IsNullOrEmpty(strSpecialContactIDs) ? "ContactID = 0 " : "ContactID IN (0, " + strSpecialContactIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = @ProductID " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); PromoValue = decimal.Parse(dr["PromoValue"].ToString()); InPercent = bool.Parse(dr["InPercent"].ToString()); return boHasPromo; } /*******************************Up to group, Sub, Prod and VariationMatrix ID only*****************************/ SQL = "SELECT " + "PromoItemsID, " + "a.PromoID, " + "PromoTypeID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "Quantity, " + "PromoValue, " + "InPercent " + "FROM tblPromoItems a " + "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + "WHERE " + (string.IsNullOrEmpty(strSpecialContactIDs) ? "ContactID = 0 " : "ContactID IN (0, " + strSpecialContactIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = 0 " + "AND ProductID = @ProductID " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); PromoValue = decimal.Parse(dr["PromoValue"].ToString()); InPercent = bool.Parse(dr["InPercent"].ToString()); return boHasPromo; } /*******************************Up to group, Sub ID only*****************************/ SQL = "SELECT " + "PromoItemsID, " + "a.PromoID, " + "PromoTypeID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "Quantity, " + "PromoValue, " + "InPercent " + "FROM tblPromoItems a " + "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + "WHERE " + (string.IsNullOrEmpty(strSpecialContactIDs) ? "ContactID = 0 " : "ContactID IN (0, " + strSpecialContactIDs + ") ") + "AND ProductGroupID = @ProductGroupID " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = 0 " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID); cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); PromoValue = decimal.Parse(dr["PromoValue"].ToString()); InPercent = bool.Parse(dr["InPercent"].ToString()); return boHasPromo; } /*******************************Up to Sub ID only*****************************/ SQL = "SELECT " + "PromoItemsID, " + "a.PromoID, " + "PromoTypeID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "Quantity, " + "PromoValue, " + "InPercent " + "FROM tblPromoItems a " + "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + "WHERE " + (string.IsNullOrEmpty(strSpecialContactIDs) ? "ContactID = 0 " : "ContactID IN (0, " + strSpecialContactIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = 0 " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); PromoValue = decimal.Parse(dr["PromoValue"].ToString()); InPercent = bool.Parse(dr["InPercent"].ToString()); return boHasPromo; } /*******************************Up to group ID only*****************************/ SQL = "SELECT " + "PromoItemsID, " + "a.PromoID, " + "PromoTypeID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "Quantity, " + "PromoValue, " + "InPercent " + "FROM tblPromoItems a " + "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + "WHERE " + (string.IsNullOrEmpty(strSpecialContactIDs) ? "ContactID = 0 " : "ContactID IN (0, " + strSpecialContactIDs + ") ") + "AND ProductGroupID = @ProductGroupID " + "AND ProductSubGroupID = 0 " + "AND ProductID = 0 " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); PromoValue = decimal.Parse(dr["PromoValue"].ToString()); InPercent = bool.Parse(dr["InPercent"].ToString()); return boHasPromo; } /*******************************Up to all only*****************************/ SQL = "SELECT " + "PromoItemsID, " + "a.PromoID, " + "PromoTypeID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "Quantity, " + "PromoValue, " + "InPercent " + "FROM tblPromoItems a " + "INNER JOIN tblPromo b ON a.PromoID = b.PromoID " + "WHERE " + (string.IsNullOrEmpty(strSpecialContactIDs) ? "ContactID = 0 " : "ContactID IN (0, " + strSpecialContactIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = 0 " + "AND ProductID = 0 " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoType = (PromoTypes)Enum.Parse(typeof(PromoTypes), dr["PromoTypeID"].ToString()); PromoQuantity = decimal.Parse(dr["Quantity"].ToString()); PromoValue = decimal.Parse(dr["PromoValue"].ToString()); InPercent = bool.Parse(dr["InPercent"].ToString()); return boHasPromo; } } catch (Exception ex) { base.ThrowException(ex); } return false; }
public bool ApplyPromoBySupplierValue(PromoBySupplierDetails clsPromoBySupplierDetails, Int64 ContactID, Int64 ProductGroupID, Int64 ProductSubGroupID, Int64 ProductID, Int64 VariationMatrixID, out decimal PromoBySupplierValue, out string CouponRemarks, int BranchID = 0) { string strSpecialSupplierIDs = ""; PromoBySupplierValue = 0; CouponRemarks = ""; bool boHasPromoBySupplier = true; try { if (ProductID != 0) { Data.Products clsProduct = new Data.Products(base.Connection, base.Transaction); Data.ProductDetails clsProductDetails = clsProduct.Details1(BranchID, ProductID); ProductSubGroupID = clsProductDetails.ProductSubGroupID; ProductGroupID = clsProductDetails.ProductGroupID; } MySqlCommand cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; string SQL = ""; string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName); /*******************************Up to Contact, Group, Sub, Prod and VarM ID *****************************/ SQL = "SELECT " + "PromoBySupplierItemsID, " + "a.PromoBySupplierID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "PromoBySupplierValue, " + "CouponRemarks " + "FROM tblPromoBySupplierItems a " + "INNER JOIN tblPromoBySupplier b ON a.PromoBySupplierID = b.PromoBySupplierID AND a.PromoBySupplierID = @PromoBySupplierID " + "WHERE " + (string.IsNullOrEmpty(strSpecialSupplierIDs) ? "ContactID = @ContactID " : "ContactID IN (@ContactID, " + strSpecialSupplierIDs + ") ") + "AND ProductGroupID = @ProductGroupID " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = @ProductID " + "AND VariationMatrixID = @VariationMatrixID " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoBySupplierValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@PromoBySupplierID", clsPromoBySupplierDetails.PromoBySupplierID); cmd.Parameters.AddWithValue("@ContactID", ContactID); cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID); cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoBySupplierValue = decimal.Parse(dr["PromoBySupplierValue"].ToString()); CouponRemarks = dr["CouponRemarks"].ToString(); return boHasPromoBySupplier; } /*******************************Up to Contact, Sub, Prod and VariationsMatrix ID *****************************/ SQL = "SELECT " + "PromoBySupplierItemsID, " + "a.PromoBySupplierID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "PromoBySupplierValue, " + "CouponRemarks " + "FROM tblPromoBySupplierItems a " + "INNER JOIN tblPromoBySupplier b ON a.PromoBySupplierID = b.PromoBySupplierID AND a.PromoBySupplierID = @PromoBySupplierID " + "WHERE " + (string.IsNullOrEmpty(strSpecialSupplierIDs) ? "ContactID = @ContactID " : "ContactID IN (@ContactID, " + strSpecialSupplierIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = @ProductID " + "AND VariationMatrixID = @VariationMatrixID " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoBySupplierValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@PromoBySupplierID", clsPromoBySupplierDetails.PromoBySupplierID); cmd.Parameters.AddWithValue("@ContactID", ContactID); cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoBySupplierValue = decimal.Parse(dr["PromoBySupplierValue"].ToString()); CouponRemarks = dr["CouponRemarks"].ToString(); return boHasPromoBySupplier; } /*******************************Up to Contact, Prod and VariationsMatrix ID *****************************/ SQL = "SELECT " + "PromoBySupplierItemsID, " + "a.PromoBySupplierID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "PromoBySupplierValue, " + "CouponRemarks " + "FROM tblPromoBySupplierItems a " + "INNER JOIN tblPromoBySupplier b ON a.PromoBySupplierID = b.PromoBySupplierID AND a.PromoBySupplierID = @PromoBySupplierID " + "WHERE " + (string.IsNullOrEmpty(strSpecialSupplierIDs) ? "ContactID = @ContactID " : "ContactID IN (@ContactID, " + strSpecialSupplierIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = 0 " + "AND ProductID = @ProductID " + "AND VariationMatrixID = @VariationMatrixID " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoBySupplierValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@PromoBySupplierID", clsPromoBySupplierDetails.PromoBySupplierID); cmd.Parameters.AddWithValue("@ContactID", ContactID); cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoBySupplierValue = decimal.Parse(dr["PromoBySupplierValue"].ToString()); CouponRemarks = dr["CouponRemarks"].ToString(); return boHasPromoBySupplier; } /*******************************Up to Contact, VariationsMatrix ID *****************************/ SQL = "SELECT " + "PromoBySupplierItemsID, " + "a.PromoBySupplierID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "PromoBySupplierValue, " + "CouponRemarks " + "FROM tblPromoBySupplierItems a " + "INNER JOIN tblPromoBySupplier b ON a.PromoBySupplierID = b.PromoBySupplierID AND a.PromoBySupplierID = @PromoBySupplierID " + "WHERE " + (string.IsNullOrEmpty(strSpecialSupplierIDs) ? "ContactID = @ContactID " : "ContactID IN (@ContactID, " + strSpecialSupplierIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = 0 " + "AND ProductID = 0 " + "AND VariationMatrixID = @VariationMatrixID " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoBySupplierValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@PromoBySupplierID", clsPromoBySupplierDetails.PromoBySupplierID); cmd.Parameters.AddWithValue("@ContactID", ContactID); cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoBySupplierValue = decimal.Parse(dr["PromoBySupplierValue"].ToString()); CouponRemarks = dr["CouponRemarks"].ToString(); return boHasPromoBySupplier; } /*******************************Up to Contact, Group, Sub, Prod ID *****************************/ SQL = "SELECT " + "PromoBySupplierItemsID, " + "a.PromoBySupplierID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "PromoBySupplierValue, " + "CouponRemarks " + "FROM tblPromoBySupplierItems a " + "INNER JOIN tblPromoBySupplier b ON a.PromoBySupplierID = b.PromoBySupplierID AND a.PromoBySupplierID = @PromoBySupplierID " + "WHERE " + (string.IsNullOrEmpty(strSpecialSupplierIDs) ? "ContactID = @ContactID " : "ContactID IN (@ContactID, " + strSpecialSupplierIDs + ") ") + "AND ProductGroupID = @ProductGroupID " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = @ProductID " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoBySupplierValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@PromoBySupplierID", clsPromoBySupplierDetails.PromoBySupplierID); cmd.Parameters.AddWithValue("@ContactID", ContactID); cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID); cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoBySupplierValue = decimal.Parse(dr["PromoBySupplierValue"].ToString()); CouponRemarks = dr["CouponRemarks"].ToString(); return boHasPromoBySupplier; } /*******************************Up to Contact, Sub, Prod ID *****************************/ SQL = "SELECT " + "PromoBySupplierItemsID, " + "a.PromoBySupplierID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "PromoBySupplierValue, " + "CouponRemarks " + "FROM tblPromoBySupplierItems a " + "INNER JOIN tblPromoBySupplier b ON a.PromoBySupplierID = b.PromoBySupplierID AND a.PromoBySupplierID = @PromoBySupplierID " + "WHERE " + (string.IsNullOrEmpty(strSpecialSupplierIDs) ? "ContactID = @ContactID " : "ContactID IN (@ContactID, " + strSpecialSupplierIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = @ProductID " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoBySupplierValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@PromoBySupplierID", clsPromoBySupplierDetails.PromoBySupplierID); cmd.Parameters.AddWithValue("@ContactID", ContactID); cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoBySupplierValue = decimal.Parse(dr["PromoBySupplierValue"].ToString()); CouponRemarks = dr["CouponRemarks"].ToString(); return boHasPromoBySupplier; } /*******************************Up to Contact, Prod ID *****************************/ SQL = "SELECT " + "PromoBySupplierItemsID, " + "a.PromoBySupplierID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "PromoBySupplierValue, " + "CouponRemarks " + "FROM tblPromoBySupplierItems a " + "INNER JOIN tblPromoBySupplier b ON a.PromoBySupplierID = b.PromoBySupplierID AND a.PromoBySupplierID = @PromoBySupplierID " + "WHERE " + (string.IsNullOrEmpty(strSpecialSupplierIDs) ? "ContactID = @ContactID " : "ContactID IN (@ContactID, " + strSpecialSupplierIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = 0 " + "AND ProductID = @ProductID " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoBySupplierValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@PromoBySupplierID", clsPromoBySupplierDetails.PromoBySupplierID); cmd.Parameters.AddWithValue("@ContactID", ContactID); cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoBySupplierValue = decimal.Parse(dr["PromoBySupplierValue"].ToString()); CouponRemarks = dr["CouponRemarks"].ToString(); return boHasPromoBySupplier; } /*******************************Up to Contact, Group, Sub *****************************/ SQL = "SELECT " + "PromoBySupplierItemsID, " + "a.PromoBySupplierID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "PromoBySupplierValue, " + "CouponRemarks " + "FROM tblPromoBySupplierItems a " + "INNER JOIN tblPromoBySupplier b ON a.PromoBySupplierID = b.PromoBySupplierID AND a.PromoBySupplierID = @PromoBySupplierID " + "WHERE " + (string.IsNullOrEmpty(strSpecialSupplierIDs) ? "ContactID = @ContactID " : "ContactID IN (@ContactID, " + strSpecialSupplierIDs + ") ") + "AND ProductGroupID = @ProductGroupID " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = 0 " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoBySupplierValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@PromoBySupplierID", clsPromoBySupplierDetails.PromoBySupplierID); cmd.Parameters.AddWithValue("@ContactID", ContactID); cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID); cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoBySupplierValue = decimal.Parse(dr["PromoBySupplierValue"].ToString()); CouponRemarks = dr["CouponRemarks"].ToString(); return boHasPromoBySupplier; } /*******************************Up to Contact, Sub *****************************/ SQL = "SELECT " + "PromoBySupplierItemsID, " + "a.PromoBySupplierID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "PromoBySupplierValue, " + "CouponRemarks " + "FROM tblPromoBySupplierItems a " + "INNER JOIN tblPromoBySupplier b ON a.PromoBySupplierID = b.PromoBySupplierID AND a.PromoBySupplierID = @PromoBySupplierID " + "WHERE " + (string.IsNullOrEmpty(strSpecialSupplierIDs) ? "ContactID = @ContactID " : "ContactID IN (@ContactID, " + strSpecialSupplierIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = 0 " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoBySupplierValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@PromoBySupplierID", clsPromoBySupplierDetails.PromoBySupplierID); cmd.Parameters.AddWithValue("@ContactID", ContactID); cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoBySupplierValue = decimal.Parse(dr["PromoBySupplierValue"].ToString()); CouponRemarks = dr["CouponRemarks"].ToString(); return boHasPromoBySupplier; } /*******************************Up to Contact *****************************/ SQL = "SELECT " + "PromoBySupplierItemsID, " + "a.PromoBySupplierID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "PromoBySupplierValue, " + "CouponRemarks " + "FROM tblPromoBySupplierItems a " + "INNER JOIN tblPromoBySupplier b ON a.PromoBySupplierID = b.PromoBySupplierID AND a.PromoBySupplierID = @PromoBySupplierID " + "WHERE " + (string.IsNullOrEmpty(strSpecialSupplierIDs) ? "ContactID = @ContactID " : "ContactID IN (@ContactID, " + strSpecialSupplierIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = 0 " + "AND ProductID = 0 " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoBySupplierValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@PromoBySupplierID", clsPromoBySupplierDetails.PromoBySupplierID); cmd.Parameters.AddWithValue("@ContactID", ContactID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoBySupplierValue = decimal.Parse(dr["PromoBySupplierValue"].ToString()); CouponRemarks = dr["CouponRemarks"].ToString(); return boHasPromoBySupplier; } /*******************************Up to Group, Sub, Prod and VarM ID *****************************/ SQL = "SELECT " + "PromoBySupplierItemsID, " + "a.PromoBySupplierID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "PromoBySupplierValue, " + "CouponRemarks " + "FROM tblPromoBySupplierItems a " + "INNER JOIN tblPromoBySupplier b ON a.PromoBySupplierID = b.PromoBySupplierID AND a.PromoBySupplierID = @PromoBySupplierID " + "WHERE " + (string.IsNullOrEmpty(strSpecialSupplierIDs) ? "ContactID = 0 " : "ContactID IN (0, " + strSpecialSupplierIDs + ") ") + "AND ProductGroupID = @ProductGroupID " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = @ProductID " + "AND VariationMatrixID = @VariationMatrixID " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoBySupplierValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@PromoBySupplierID", clsPromoBySupplierDetails.PromoBySupplierID); cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID); cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoBySupplierValue = decimal.Parse(dr["PromoBySupplierValue"].ToString()); CouponRemarks = dr["CouponRemarks"].ToString(); return boHasPromoBySupplier; } /*******************************Up to Sub, Prod and VariationMatrix ID *****************************/ SQL = "SELECT " + "PromoBySupplierItemsID, " + "a.PromoBySupplierID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "PromoBySupplierValue, " + "CouponRemarks " + "FROM tblPromoBySupplierItems a " + "INNER JOIN tblPromoBySupplier b ON a.PromoBySupplierID = b.PromoBySupplierID AND a.PromoBySupplierID = @PromoBySupplierID " + "WHERE " + (string.IsNullOrEmpty(strSpecialSupplierIDs) ? "ContactID = 0 " : "ContactID IN (0, " + strSpecialSupplierIDs + ") ") + "AND ProductGroupID =0 " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = @ProductID " + "AND VariationMatrixID = @VariationMatrixID " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoBySupplierValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@PromoBySupplierID", clsPromoBySupplierDetails.PromoBySupplierID); cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoBySupplierValue = decimal.Parse(dr["PromoBySupplierValue"].ToString()); CouponRemarks = dr["CouponRemarks"].ToString(); return boHasPromoBySupplier; } /*******************************Up to Prod and VariationMatrix ID *****************************/ SQL = "SELECT " + "PromoBySupplierItemsID, " + "a.PromoBySupplierID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "PromoBySupplierValue, " + "CouponRemarks " + "FROM tblPromoBySupplierItems a " + "INNER JOIN tblPromoBySupplier b ON a.PromoBySupplierID = b.PromoBySupplierID AND a.PromoBySupplierID = @PromoBySupplierID " + "WHERE " + (string.IsNullOrEmpty(strSpecialSupplierIDs) ? "ContactID = 0 " : "ContactID IN (0, " + strSpecialSupplierIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = 0 " + "AND ProductID = @ProductID " + "AND VariationMatrixID = @VariationMatrixID " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoBySupplierValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@PromoBySupplierID", clsPromoBySupplierDetails.PromoBySupplierID); cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoBySupplierValue = decimal.Parse(dr["PromoBySupplierValue"].ToString()); CouponRemarks = dr["CouponRemarks"].ToString(); return boHasPromoBySupplier; } /*******************************Up to VariationsMatrix ID *****************************/ SQL = "SELECT " + "PromoBySupplierItemsID, " + "a.PromoBySupplierID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "PromoBySupplierValue, " + "CouponRemarks " + "FROM tblPromoBySupplierItems a " + "INNER JOIN tblPromoBySupplier b ON a.PromoBySupplierID = b.PromoBySupplierID AND a.PromoBySupplierID = @PromoBySupplierID " + "WHERE " + (string.IsNullOrEmpty(strSpecialSupplierIDs) ? "ContactID = 0 " : "ContactID IN (0, " + strSpecialSupplierIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = 0 " + "AND ProductID = 0 " + "AND VariationMatrixID = @VariationMatrixID " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoBySupplierValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@PromoBySupplierID", clsPromoBySupplierDetails.PromoBySupplierID); cmd.Parameters.AddWithValue("@VariationMatrixID", VariationMatrixID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoBySupplierValue = decimal.Parse(dr["PromoBySupplierValue"].ToString()); CouponRemarks = dr["CouponRemarks"].ToString(); return boHasPromoBySupplier; } /*******************************Up to group, Sub, Prod ID *****************************/ SQL = "SELECT " + "PromoBySupplierItemsID, " + "a.PromoBySupplierID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "PromoBySupplierValue, " + "CouponRemarks " + "FROM tblPromoBySupplierItems a " + "INNER JOIN tblPromoBySupplier b ON a.PromoBySupplierID = b.PromoBySupplierID AND a.PromoBySupplierID = @PromoBySupplierID " + "WHERE " + (string.IsNullOrEmpty(strSpecialSupplierIDs) ? "ContactID = 0 " : "ContactID IN (0, " + strSpecialSupplierIDs + ") ") + "AND ProductGroupID = @ProductGroupID " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = @ProductID " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoBySupplierValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@PromoBySupplierID", clsPromoBySupplierDetails.PromoBySupplierID); cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID); cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoBySupplierValue = decimal.Parse(dr["PromoBySupplierValue"].ToString()); CouponRemarks = dr["CouponRemarks"].ToString(); return boHasPromoBySupplier; } /*******************************Up to Sub, Prod ID *****************************/ SQL = "SELECT " + "PromoBySupplierItemsID, " + "a.PromoBySupplierID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "PromoBySupplierValue, " + "CouponRemarks " + "FROM tblPromoBySupplierItems a " + "INNER JOIN tblPromoBySupplier b ON a.PromoBySupplierID = b.PromoBySupplierID AND a.PromoBySupplierID = @PromoBySupplierID " + "WHERE " + (string.IsNullOrEmpty(strSpecialSupplierIDs) ? "ContactID = 0 " : "ContactID IN (0, " + strSpecialSupplierIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = @ProductID " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoBySupplierValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@PromoBySupplierID", clsPromoBySupplierDetails.PromoBySupplierID); cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoBySupplierValue = decimal.Parse(dr["PromoBySupplierValue"].ToString()); CouponRemarks = dr["CouponRemarks"].ToString(); return boHasPromoBySupplier; } /*******************************Up to group, Sub, Prod and VariationMatrix ID *****************************/ SQL = "SELECT " + "PromoBySupplierItemsID, " + "a.PromoBySupplierID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "PromoBySupplierValue, " + "CouponRemarks " + "FROM tblPromoBySupplierItems a " + "INNER JOIN tblPromoBySupplier b ON a.PromoBySupplierID = b.PromoBySupplierID AND a.PromoBySupplierID = @PromoBySupplierID " + "WHERE " + (string.IsNullOrEmpty(strSpecialSupplierIDs) ? "ContactID = 0 " : "ContactID IN (0, " + strSpecialSupplierIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = 0 " + "AND ProductID = @ProductID " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoBySupplierValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@PromoBySupplierID", clsPromoBySupplierDetails.PromoBySupplierID); cmd.Parameters.AddWithValue("@ProductID", ProductID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoBySupplierValue = decimal.Parse(dr["PromoBySupplierValue"].ToString()); CouponRemarks = dr["CouponRemarks"].ToString(); return boHasPromoBySupplier; } /*******************************Up to group, Sub ID *****************************/ SQL = "SELECT " + "PromoBySupplierItemsID, " + "a.PromoBySupplierID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "PromoBySupplierValue, " + "CouponRemarks " + "FROM tblPromoBySupplierItems a " + "INNER JOIN tblPromoBySupplier b ON a.PromoBySupplierID = b.PromoBySupplierID AND a.PromoBySupplierID = @PromoBySupplierID " + "WHERE " + (string.IsNullOrEmpty(strSpecialSupplierIDs) ? "ContactID = 0 " : "ContactID IN (0, " + strSpecialSupplierIDs + ") ") + "AND ProductGroupID = @ProductGroupID " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = 0 " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoBySupplierValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@PromoBySupplierID", clsPromoBySupplierDetails.PromoBySupplierID); cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID); cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoBySupplierValue = decimal.Parse(dr["PromoBySupplierValue"].ToString()); CouponRemarks = dr["CouponRemarks"].ToString(); return boHasPromoBySupplier; } /*******************************Up to Sub ID *****************************/ SQL = "SELECT " + "PromoBySupplierItemsID, " + "a.PromoBySupplierID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "PromoBySupplierValue, " + "CouponRemarks " + "FROM tblPromoBySupplierItems a " + "INNER JOIN tblPromoBySupplier b ON a.PromoBySupplierID = b.PromoBySupplierID AND a.PromoBySupplierID = @PromoBySupplierID " + "WHERE " + (string.IsNullOrEmpty(strSpecialSupplierIDs) ? "ContactID = 0 " : "ContactID IN (0, " + strSpecialSupplierIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = @ProductSubGroupID " + "AND ProductID = 0 " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoBySupplierValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@PromoBySupplierID", clsPromoBySupplierDetails.PromoBySupplierID); cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoBySupplierValue = decimal.Parse(dr["PromoBySupplierValue"].ToString()); CouponRemarks = dr["CouponRemarks"].ToString(); return boHasPromoBySupplier; } /*******************************Up to group ID *****************************/ SQL = "SELECT " + "PromoBySupplierItemsID, " + "a.PromoBySupplierID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "PromoBySupplierValue, " + "CouponRemarks " + "FROM tblPromoBySupplierItems a " + "INNER JOIN tblPromoBySupplier b ON a.PromoBySupplierID = b.PromoBySupplierID AND a.PromoBySupplierID = @PromoBySupplierID " + "WHERE " + (string.IsNullOrEmpty(strSpecialSupplierIDs) ? "ContactID = 0 " : "ContactID IN (0, " + strSpecialSupplierIDs + ") ") + "AND ProductGroupID = @ProductGroupID " + "AND ProductSubGroupID = 0 " + "AND ProductID = 0 " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoBySupplierValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@PromoBySupplierID", clsPromoBySupplierDetails.PromoBySupplierID); cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoBySupplierValue = decimal.Parse(dr["PromoBySupplierValue"].ToString()); CouponRemarks = dr["CouponRemarks"].ToString(); return boHasPromoBySupplier; } /*******************************Up to all *****************************/ SQL = "SELECT " + "PromoBySupplierItemsID, " + "a.PromoBySupplierID, " + "ProductGroupID, " + "ProductSubGroupID, " + "ProductID, " + "VariationMatrixID, " + "PromoBySupplierValue, " + "CouponRemarks " + "FROM tblPromoBySupplierItems a " + "INNER JOIN tblPromoBySupplier b ON a.PromoBySupplierID = b.PromoBySupplierID AND a.PromoBySupplierID = @PromoBySupplierID " + "WHERE " + (string.IsNullOrEmpty(strSpecialSupplierIDs) ? "ContactID = 0 " : "ContactID IN (0, " + strSpecialSupplierIDs + ") ") + "AND ProductGroupID = 0 " + "AND ProductSubGroupID = 0 " + "AND ProductID = 0 " + "AND VariationMatrixID = 0 " + "AND Status = 1 " + "AND DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') " + "AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') ORDER BY PromoBySupplierValue ASC LIMIT 1;"; cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@PromoBySupplierID", clsPromoBySupplierDetails.PromoBySupplierID); cmd.CommandText = SQL; dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); foreach (System.Data.DataRow dr in dt.Rows) { PromoBySupplierValue = decimal.Parse(dr["PromoBySupplierValue"].ToString()); CouponRemarks = dr["CouponRemarks"].ToString(); return boHasPromoBySupplier; } } catch (Exception ex) { base.ThrowException(ex); } return false; }