$this->Conn->Query('UPDATE '.$object->TableName.' SET IsPrimary = 0 WHERE '.$table_info['ForeignKey'].' = '.$table_info['ParentId']);
$this->Conn->Query('UPDATE '.$object->TableName.' SET IsPrimary = 1 WHERE ('.$table_info['ForeignKey'].' = '.$table_info['ParentId'].') AND (PriceId = '.$id.')');
- $primary_group = $this->Conn->GetOne('SELECT GroupId FROM '.TABLE_PREFIX.'UserGroup WHERE PortalUserId='.$this->Application->GetVar('u_id').' AND PrimaryGroup=1');
- $pricing_group = $primary_group;
- if ($pricing_group) {
- $pricing_for_group_exists = $this->Conn->GetOne('SELECT COUNT(*) FROM '.TABLE_PREFIX.'ProductsPricing WHERE ProductId='.$this->Application->GetVar('p_id').' AND GroupId='.$primary_group.' AND Price IS NOT NULL');
- }
- if ($pricing_group && $pricing_for_group_exists > 0){
+ $primary_group = $this->Conn->GetOne('SELECT GroupId FROM '.TABLE_PREFIX.'UserGroup WHERE PortalUserId='.$this->Application->GetVar('u_id').' AND PrimaryGroup=1');
+ $pricing_group = $primary_group;
+ if ($pricing_group) {
+ $pricing_for_group_exists = $this->Conn->GetOne('SELECT COUNT(*) FROM '.TABLE_PREFIX.'ProductsPricing WHERE ProductId='.$this->Application->GetVar('p_id').' AND GroupId='.$primary_group.' AND Price IS NOT NULL');
+ }
+ if ($pricing_group && $pricing_for_group_exists > 0) {
+ $pricing_group = $primary_group;
}
else {
- //$cheapest_group = $this->Conn->GetOne('SELECT GroupId FROM '.$object->TableName.' WHERE ProductId='.$this->Application->GetVar('p_id').' AND Price IS NOT NULL AND GroupId IN ('.$user_groups.') AND MinQty = 1 GROUP BY GroupId ORDER BY Price ASC');
- $effective_brackets = $this->Conn->Query('SELECT PriceId, Price, GroupId FROM '.$object->TableName.'
- WHERE ProductId='.$this->Application->GetVar('p_id').' AND Price IS NOT NULL AND GroupId IN ('.$user_groups.') ORDER BY GroupId ASC, MinQty ASC', 'PriceId');
- $group_prices = array();
- $min_price = -1;
- $cheapest_group = 0;
-
- foreach ($effective_brackets as $bracket) {
- if (!isset($group_prices[$bracket['GroupId']])) {
+ //$cheapest_group = $this->Conn->GetOne('SELECT GroupId FROM '.$object->TableName.' WHERE ProductId='.$this->Application->GetVar('p_id').' AND Price IS NOT NULL AND GroupId IN ('.$user_groups.') AND MinQty = 1 GROUP BY GroupId ORDER BY Price ASC');
+ $effective_brackets = $this->Conn->Query('SELECT PriceId, Price, GroupId FROM '.$object->TableName.'
+ WHERE ProductId='.$this->Application->GetVar('p_id').' AND Price IS NOT NULL AND GroupId IN ('.$user_groups.') ORDER BY GroupId ASC, MinQty ASC', 'PriceId');
+ $group_prices = array();
+ $min_price = -1;
+ $cheapest_group = 0;
+
+ foreach ($effective_brackets as $bracket) {
+ if (!isset($group_prices[$bracket['GroupId']])) {
if (array_intersect($shipping_type_groups, $user_groups)) {
$filteres_shipping_types[$key] = $shipping_type;
}
}
$shipping_types = $filteres_shipping_types;
}
return $shipping_types;
}
function QueryShippingCost($user_country_abbr, $user_state_abbr, $user_zip, $weight, $items, $amount, $shipping_type=null, $promo_params)
{
$db =& $this->Application->GetADODBConnection();
$user_country_id = (int) $db->GetOne('SELECT DestId FROM '.TABLE_PREFIX.'StdDestinations WHERE DestType=1 AND DestAbbr = '.$db->qstr($user_country_abbr));
$user_state_id = (int) $db->GetOne('SELECT DestId FROM '.TABLE_PREFIX.'StdDestinations WHERE DestType=2 AND DestAbbr = '.$db->qstr($user_state_abbr));
$user_zip = (string) $user_zip;
$weight = (float) $weight;
$items = (int) $items;
$amount = (float) $amount;
$promo_weight = $promo_params['weight'];
$promo_amount = (float) $promo_params['amount'];
$promo_items = (float) $promo_params['items'];
if (isset($shipping_type)) $shipping_type = (int) $shipping_type;
$query = 'SELECT
CONCAT("CUST_", st.ShippingID) as ShippingId,
IF(st.Type = 4, st.BaseFee, '.# taking only base fee for handling
'IF (
st.IsFreePromoShipping = 1 AND
( (st.FreeShippingMinAmount != 0 AND st.FreeShippingMinAmount <= '.$amount.') OR
(st.Type = 1 AND '.$promo_weight.' = 0) OR
(st.Type = 2 AND '.$promo_items.' = 0) OR
(st.Type = 3 AND '.$promo_amount.' = 0)
), 0,
MIN(
IF(st.BaseFee IS NULL, 0, st.BaseFee) +
IF(st.CostType IN (1,3), IF(sc.Flat IS NULL, 0, sc.Flat), 0) +
IF(st.CostType IN (2,3),
IF(sc.PerUnit IS NULL, 0, sc.PerUnit)
*
( CASE st.Type
WHEN 1 THEN IF(st.IsFreePromoShipping = 1, '.$promo_weight.', '.$weight.')
WHEN 2 THEN IF(st.IsFreePromoShipping = 1, '.$promo_items.', '.$items.')
WHEN 3 THEN IF(st.IsFreePromoShipping = 1, '.$promo_amount.', '.$amount.')
ELSE 0 END ),
0)
)
)
) AS TotalCost,
st.Name as ShippingName,
st.Type as Type,
st.CODFlatSurcharge as CODFlat,
st.CODPercentSurcharge as CODPercent,
st.PortalGroups as PortalGroups,
IF (
st.InsuranceType = 1,
st.InsuranceFee,
st.InsuranceFee * '.$amount.' / 100
) AS InsuranceFee,
IF(sz.CODallowed IS NULL, 0, sz.CODallowed) AS COD,
st.Status = 2 as SelectedOnly,
st.Code
FROM '.TABLE_PREFIX.'ShippingType AS st '. #all shipping types
'LEFT JOIN '.TABLE_PREFIX.'ShippingBrackets AS sb '. #getting brackets by shipping type
'ON sb.ShippingTypeID = st.ShippingID '.
'LEFT JOIN '.TABLE_PREFIX.'ShippingZones AS sz '. #getting zones by shipping type
'ON sz.ShippingTypeID = st.ShippingID '.
'LEFT JOIN '.TABLE_PREFIX.'ShippingZonesDestinations AS szd '. #getting destinations by shipping type
'ON szd.ShippingZoneId = sz.ZoneID '.
'LEFT JOIN '.TABLE_PREFIX.'ShippingCosts AS sc '. #getting costs by bracket and zone
'ON sc.BracketId = sb.BracketId AND sc.ZoneID = sz.ZoneID '.
'WHERE
'.(isset($shipping_type) ? 'st.ShippingID = '.$shipping_type.' AND ' : '').'
(st.Status >= 1) '. # enabled (1) or Selected Only (2) shipping types
'AND
(
'.# handlign should require brackets/zones (st.Type = 4) # handling - does not required brackets
# OR
'(
( st.Type IN (1,2,3,4) ) '. # bracket dependant types
'AND
'.# Zone match
'( (sz.Type = 1 AND
(szd.StdDestId = '.$user_country_id.') '.# user country id
')
OR
(sz.Type = 2 AND
(szd.StdDestId = '.$user_state_id.') '.# user state id
')
OR
(sz.Type = 3 AND
(szd.StdDestId = '.$user_country_id.') '.# user country id
'AND
(szd.DestValue = '.$db->qstr($user_zip).') '.# user zip code
')
)
AND
'.# Bracket match
'( (st.Type = 1
AND
sb.Start <= IF(st.IsFreePromoShipping = 1, '.$promo_weight.', '.$weight.') AND (sb.End > IF(st.IsFreePromoShipping = 1, '.$promo_weight.', '.$weight.') OR sb.End = -1) '.# items total weight
')
OR
(st.Type = 2
AND
sb.Start <= IF(st.IsFreePromoShipping = 1, '.$promo_items.', '.$items.') AND (sb.End > IF(st.IsFreePromoShipping = 1, '.$promo_items.', '.$items.') OR sb.End = -1) '.# items total qty
')
OR
(st.Type = 3
AND
sb.Start <= IF(st.IsFreePromoShipping = 1, '.$promo_amount.', '.$amount.') AND (sb.End > IF(st.IsFreePromoShipping = 1, '.$promo_amount.', '.$amount.') OR sb.End = -1) '.# items total amount
')
OR
st.Type = 4 '.# handling - does not depend on brackets
')
AND
'.# Empty costs handling
'( (st.ZeroIfEmpty = 0 AND (sc.Flat IS NOT NULL OR sc.PerUnit IS NOT NULL) ) '.# if no shimpent on empty - flat or perunit should be filled in
'OR
(st.ZeroIfEmpty = 1) '. # zero on empty - is ok (nulls will be converted in SELECT part)
'OR
(st.Type = 4) '. # ignore costs for handling
')
)
)
GROUP BY st.ShippingId '. # getting minimal price (possible with closest address match) for every shipping type
if ($event->status == erSUCCESS && !$event->redirect) {
$event->redirect_params['pass'] = 'm';
$event->redirect_params['pass_category'] = 0; //otherwise mod-rewrite shop-cart URL will include category
$event->redirect = true;
}
else {
- if ($this->Application->IsAdmin()) {
+ if ($this->Application->isAdminUser) {
$event->redirect_params['opener'] = 'u';
}
}
}
/**
* Check if required options are selected & selected option combination is in stock
*
* @param kEvent $event
* @param Array $options
* @param int $product_id
* @param int $qty
* @param int $selection_mode
* @return bool
*/
function CheckOptions(&$event, &$options, $product_id, $qty, $selection_mode)
{
// 1. check for required options
$selection_filter = $selection_mode == 1 ? ' AND OptionType IN (1,3,6) ' : '';
$req_options = $this->Conn->GetCol('SELECT ProductOptionId FROM '.TABLE_PREFIX.'ProductOptions WHERE ProductId = '.$product_id.' AND Required = 1 '.$selection_filter);
$result = true;
foreach ($req_options as $opt_id) {
if (!getArrayValue($options, $opt_id)) {
$this->Application->SetVar('opt_error', 1); //let the template know we have an error
LEFT JOIN '.TABLE_PREFIX.'Products ON '.TABLE_PREFIX.'Products.ProductId = '.TABLE_PREFIX.'OrderItems.ProductId
LEFT JOIN '.$poc_table.' ON ('.$poc_table.'.CombinationCRC = '.TABLE_PREFIX.'OrderItems.OptionsSalt) AND ('.$poc_table.'.ProductId = '.TABLE_PREFIX.'OrderItems.ProductId)
$sql = 'UPDATE '.$table.' SET Status = 0 WHERE '.$id_field.' = '.$id;
$this->Conn->Query($sql);
}
/* ======================== COMMON CODE ======================== */
/**
* Split one timestamp field into 2 virtual fields
*
* @param kEvent $event
* @access public
*/
function OnAfterItemLoad(&$event)
{
// get user fields
$object =& $event->getObject();
$user_id = $object->GetDBField('PortalUserId');
if($user_id)
{
$user_info = $this->Conn->GetRow('SELECT *, CONCAT(FirstName,\' \',LastName) AS UserTo FROM '.TABLE_PREFIX.'PortalUser WHERE PortalUserId = '.$user_id);
IF('.TABLE_PREFIX.'Products.InventoryStatus = 2, '.$poc_table.'.QtyInStock, '.TABLE_PREFIX.'Products.QtyInStock) AS QtyInStock, '.
TABLE_PREFIX.'Products.QtyInStockMin, '.
$table_prefix.'OrderItems.ProductId, '.
TABLE_PREFIX.'Products.InventoryStatus,'.
$table_prefix.'OrderItems.OptionsSalt AS CombinationCRC
FROM '.$table_prefix.'OrderItems
LEFT JOIN '.TABLE_PREFIX.'Products ON '.TABLE_PREFIX.'Products.ProductId = '.$table_prefix.'OrderItems.ProductId
LEFT JOIN '.$poc_table.' ON ('.$poc_table.'.CombinationCRC = '.$table_prefix.'OrderItems.OptionsSalt) AND ('.$poc_table.'.ProductId = '.$table_prefix.'OrderItems.ProductId)
WHERE OrderId = '.$ord_id.' AND '.TABLE_PREFIX.'Products.Type = 1
if ($an_item['BackOrderFlag'] > 0) { // we don't need to reserve if it's backordered item
$to_reserve = 0;
}
else {
$to_reserve = min($an_item['Quantity']-$an_item['QuantityReserved'], $an_item['QtyInStock']-$an_item['QtyInStockMin']); //it should be equal, but just in case
$has_required = $this->Conn->GetOne('SELECT COUNT(*) FROM '.$opt_object->TableName.' WHERE Required = 1 AND ProductId = '.$object->GetDBField('ProductId'));
//we need to imitate data sumbit, as parent' PreSave sets object values from $items_info
$total_weight = $this->Conn->GetOne('SELECT SUM(Weight) FROM '.TABLE_PREFIX.'Products WHERE ProductId IN ('.implode(', ', $content_ids).') AND Type=1');
if (!$total_weight) $total_weight = 0;
$this->Conn->Query('UPDATE '.$object->TableName.' SET Weight='.$total_weight.' WHERE ProductId='.$object->GetID());
$sql = 'SELECT AffiliateCommission, (SubTotal+ShippingCost+VAT) AS TotalAmount, OrderId FROM '.$order->TableName.' WHERE OrderDate >= %s AND OrderDate <= %s';
$amount_to_pay_before = 0;
$amount_to_pay_after = 0;
$order_update_sql = 'UPDATE '.$order->TableName.' SET AffiliateCommission = %s WHERE '.$order->IDField.' = %s';