public static function getRoutingCode()
{
$criteria = new CDbCriteria();
$criteria->distinct = true;
$criteria->select = 'code';
$builder = new CDbCommandBuilder(Yii::app()->db->Schema);
$command = $builder->createFindCommand('intra_city_routing', $criteria);
$data = $command->queryAll();
return $data;
}
public static function getProvinceData($rawProvinceName)
{
$criteria = new CDbCriteria();
$criteria->select = 'name,id';
$criteria->addSearchCondition('name', $rawProvinceName, true, 'AND', $rawProvinceName = 'LIKE');
$builder = new CDbCommandBuilder(Yii::app()->db->Schema);
$command = $builder->createFindCommand('province', $criteria);
$province_datas = $command->queryAll();
$data = array();
foreach ($province_datas as $province_data) {
$data[] = array('value' => $province_data['name'], 'label' => $province_data['name'], 'id' => $province_data['id']);
}
return $data;
}
public static function getSql($criteria, $modelName, $tableAlias = 't')
{
$tableName = $modelName::model()->tableName();
$schema = Yii::app()->db->schema;
$cbuilder = new CDbCommandBuilder($schema);
$sqlCommand = $cbuilder->createFindCommand($tableName, $criteria, $tableAlias);
$sql = $sqlCommand->text;
foreach (array_reverse($criteria->params) as $key => $value) {
if (is_string($value)) {
$sql = str_replace($key, "'" . addslashes($value) . "'", $sql);
} else {
$sql = str_replace($key, addslashes($value), $sql);
}
}
// return $sql;
echo '<pre>';
print_r($sql);
echo '<pre>';
exit;
}
/**
*belum tentu dipakai :
*
* @param type $province
* @param type $district
* @param type $postal
*/
public static function getZoneIdOrDistrictId($province, $district, $postal)
{
if (empty($postal)) {
$criteria = new CDbCriteria();
$criteria->select = 't.id';
$criteria->join = 'JOIN district ON t.district_id = district.id JOIN province ON province.id = district.province_id';
$criteria->addSearchCondition('district.name', $district, true, 'AND');
$criteria->addSearchCondition('province.name', $province, true, 'AND');
$criteria->limit = 1;
$builder = new CDbCommandBuilder(Yii::app()->db->Schema);
$command = $builder->createFindCommand('zone', $criteria);
$zone_id = $command->queryAll();
} else {
$zone_id = Area::model()->findByAttributes(array('postcode' => $postal));
}
print_r($zone_id);
exit;
}
public function actionGetExtTypeService()
{
$result = array('status' => 'error', 'data' => '');
$data = array();
if (isset($_REQUEST['Shipment'])) {
$shipment = new Shipment();
$shipment->setAttributes($_REQUEST['Shipment']);
if ($shipment->customer_id != '') {
$join = 'INNER JOIN product_service t2 ON t.id = t2.product_id INNER JOIN service_detail t3 ON t2.id = t3.product_service_id INNER JOIN rate_company_service t4 ON t4.id = t3.rate_company_service_id JOIN rate_company t5 ON t5.id = t4.rate_company_id JOIN customer_discount t6 ON t3.id = t6.service_id';
$select = 't4.id as service_id,t2.name as service_name,t2.code,t4.name as carrier_service,t5.name vendor_name,t6.use_rate';
} else {
$join = 'INNER JOIN product_service t2 ON t.id = t2.product_id INNER JOIN service_detail t3 ON t2.id = t3.product_service_id INNER JOIN rate_company_service t4 ON t4.id = t3.rate_company_service_id JOIN rate_company t5 ON t5.id = t4.rate_company_id';
$select = 't4.id as service_id,t2.name as service_name,t2.code,t4.name as carrier_service,t5.name vendor_name';
}
$cek_routing = RateCompany::model()->findByAttributes(array('code' => $shipment->destination_code));
$criteria = new CDbCriteria();
$criteria->join = $join;
$criteria->select = $select;
$criteria->addSearchCondition('t.name', $shipment->service_type);
if ($shipment->customer_id != '') {
$criteria->params[':customer_id'] = $shipment->customer_id;
$criteria->addCondition('t6.customer_id =:customer_id AND use_rate = 1');
}
if ($shipment->service_type == 'City Courier') {
if ($cek_routing instanceof RateCompany) {
$criteria->addSearchCondition('t5.code', $shipment->destination_code);
} else {
$criteria->addSearchCondition('t5.id', 5);
}
}
if ($shipment->service_type == 'Domestic') {
$criteria->addSearchCondition('t5.code', $shipment->destination_code);
}
if ($shipment->service_type == 'International') {
$criteria->addSearchCondition('t5.code', $shipment->destination_code);
if (ucfirst($shipment->receiver_country) != 'indonesia') {
$criteria->addSearchCondition('t2.id', 10);
} else {
$criteria->addSearchCondition('t2.id', 11);
}
}
$builder = new CDbCommandBuilder(Yii::app()->db->Schema);
$command = $builder->createFindCommand('product', $criteria);
$services = $command->queryAll();
if (!!$services) {
$result = array('status' => 'success', 'data' => $services);
}
}
echo CJSON::encode($result);
Yii::app()->end();
}
protected function createFindCommand($criteria)
{
$builder = new CDbCommandBuilder(Yii::app()->db->getSchema());
$command = $builder->createFindCommand($this->tableName, $criteria);
return $command;
}
/**
* @return array of available attributes in category
*/
public function getEavAttributes()
{
if (is_array($this->_eavAttributes)) {
return $this->_eavAttributes;
}
// Find category types
$model = new StoreProduct(null);
$criteria = $model->applyCategories($this->model)->active()->getDbCriteria();
unset($model);
$builder = new CDbCommandBuilder(Yii::app()->db->getSchema());
$criteria->select = 'type_id';
$criteria->group = 'type_id';
$criteria->distinct = true;
$typesUsed = $builder->createFindCommand(StoreProduct::model()->tableName(), $criteria)->queryColumn();
// Find attributes by type
$criteria = new CDbCriteria();
$criteria->addInCondition('types.type_id', $typesUsed);
$query = StoreAttribute::model()->useInFilter()->with(array('types', 'options'))->findAll($criteria);
$this->_eavAttributes = array();
foreach ($query as $attr) {
$this->_eavAttributes[$attr->name] = $attr;
}
return $this->_eavAttributes;
}
public static function getZoneID($data, $mode = 'postcode')
{
$criteria = new CDbCriteria();
$criteria->select = 't.zone_id,t2.district_id';
$criteria->join = 'JOIN zone t2 ON t2.id=t.zone_id JOIN district t3 ON t3.id = t2.district_id';
if ($mode == 'postcode') {
$criteria->addCondition('t.postcode = ' . $data);
} elseif ($mode == 'city') {
$criteria->condition = 't3.name = "' . $data . '" AND t3.type = "kota"';
// $criteria->addCondition('t2.name = "'.$data.'"', 'OR');
}
$builder = new CDbCommandBuilder(Yii::app()->db->Schema);
$command = $builder->createFindCommand('area', $criteria);
$result = $command->queryRow();
return $result;
}
public static function getCustomerDiscountRate($service_id, $customer_id)
{
$criteria = new CDbCriteria();
$criteria->join = 'JOIN service_detail t2 on t2.id = t.service_id JOIN rate_company_service t3 on t3.id = t2.rate_company_service_id';
$criteria->select = 't.discount, t.harga_invoice, t.vendor_discount';
$criteria->condition = 't3.id =:service_id AND t.customer_id =:customer_id';
$criteria->params = array(':service_id' => $service_id, ':customer_id' => $customer_id);
$builder = new CDbCommandBuilder(Yii::app()->db->Schema);
$command = $builder->createFindCommand('customer_discount', $criteria);
$qa = $command->queryRow();
return $qa;
}
/**
* Получение массива идентификаторов потомков текущей категории
*/
public function getChildsArray()
{
$childs = array();
$criteria = new CDbCriteria();
$criteria->select = 'id';
$criteria->condition = 'upline LIKE :upline';
$criteria->params = array('upline' => $this->id . '.%');
$builder = new CDbCommandBuilder(Yii::app()->db->getSchema());
$command = $builder->createFindCommand('shop_categories', $criteria);
$parents = $command->queryAll();
$childs = array_map(function ($element) {
return $element['id'];
}, $parents);
return $childs;
}
public static function getCityRate($product_id, $routing_code, $weight, $customer_id = null, $use_rate = array())
{
$criteria = new CDbCriteria();
if (!!$customer_id && count($use_rate) == 0) {
return array();
}
if (!!$customer_id) {
$join = 'JOIN rate_company_service t2 on t.service_id = t2.id JOIN rate_company t3 ON t2.rate_company_id = t3.id JOIN service_detail t4 ON t4.rate_company_service_id = t2.id JOIN product_service t5 ON t5.id = t4.product_service_id JOIN customer_discount t6 on t6.service_id = t4.id';
$select = 't.service_id as service_id,t.weight_inc ,t.price , t5.code as service_code, t3.code as carrier_code,t3.name as carrier_name,t5.name as service_name,t.id as id,t6.harga_api,t6.discount_api,t.transit_time';
} else {
$join = 'JOIN rate_company_service t2 on t.service_id = t2.id JOIN rate_company t3 ON t2.rate_company_id = t3.id JOIN service_detail t4 ON t4.rate_company_service_id = t2.id JOIN product_service t5 ON t5.id = t4.product_service_id';
$select = 't.service_id as service_id,t.weight_inc ,t.price , t5.code as service_code, t3.code as carrier_code,t3.name as carrier_name,t5.name as service_name,t.id as id,t.transit_time';
}
$criteria->join = $join;
$criteria->select = $select;
$builder = new CDbCommandBuilder(Yii::app()->db->Schema);
$criteria->params = array(':product_id' => $product_id);
$criteria->condition = 't5.product_id=:product_id';
if (!!$customer_id) {
$criteria->params[':customer_id'] = $customer_id;
$criteria->addCondition('t6.customer_id =:customer_id AND t6.use_rate = 1');
}
$command = $builder->createFindCommand('rate_city', $criteria);
$rates = $command->queryAll();
$services = array();
foreach ($rates as $rate) {
if (!!$customer_id) {
if ($rate['harga_api'] != 0) {
$price = $rate['harga_api'] * self::increment($weight, $rate['weight_inc']);
} else {
$price = ($rate['price'] - $rate['price'] * ($rate['discount_api'] / 100)) * self::increment($weight, $rate['weight_inc']);
}
} else {
$price = $rate['price'] * self::increment($weight, $rate['weight_inc']);
}
$service = array('id' => $rate['id'], 'service_id' => $rate['service_id'], 'routing_code' => $routing_code, 'carrier_name' => $rate['carrier_name'], 'service_code' => $rate['service_code'], 'service_name' => $rate['service_name'], 'price' => $price, 'transits_days' => $rate['transit_time'] . ' hari');
array_push($services, $service);
}
return $services;
}
public static function getServices($total_weight, $zone, $transit_time, $customer_id = null, $use_rate = array())
{
$weight_to_search = self::modulo05($total_weight);
$service = array();
$price = 0;
if (!!$customer_id && count($use_rate) == 0) {
return array();
}
if (!!$customer_id) {
$join = 'JOIN rate_company_service t2 on t.service_id = t2.id JOIN rate_company t3 ON t2.rate_company_id = t3.id JOIN service_detail t4 ON t4.rate_company_service_id = t2.id JOIN product_service t5 ON t5.id = t4.product_service_id JOIN customer_discount t6 on t6.service_id = t4.id';
$select = ',t5.code as service_code, t3.code as carrier_code, t.type, t.service_id, t3.name as carrier_name,t5.name as service_name,t6.discount_api,t.id as id';
} else {
$join = 'JOIN rate_company_service t2 on t.service_id = t2.id JOIN rate_company t3 ON t2.rate_company_id = t3.id JOIN service_detail t4 ON t4.rate_company_service_id = t2.id JOIN product_service t5 ON t5.id = t4.product_service_id';
$select = ',t5.code as service_code, t3.code as carrier_code, t.type, t.service_id, t3.name as carrier_name,t5.name as service_name,t.id as id';
}
$criteria = new CDbCriteria();
$criteria->join = $join;
$criteria->select = $zone . $select;
$criteria->addColumnCondition(array('t.weight' => $weight_to_search));
if (!!$customer_id) {
$criteria->addCondition('t6.use_rate = 1');
}
$builder = new CDbCommandBuilder(Yii::app()->db->Schema);
$command = $builder->createFindCommand('rate_international', $criteria);
$rates = $command->queryAll();
if (!empty($rates)) {
foreach ($rates as $rate) {
if (!!$customer_id) {
$price = $rate[$zone] - $rate[$zone] * ($rate['discount_api'] / 100);
} else {
$price = $rate[$zone];
}
array_push($service, array('price' => $price, 'id' => $rate['id'], 'service_id' => $rate['service_id'], 'transits_days' => $transit_time . ' days', 'carrier_name' => $rate['carrier_name'], 'service_name' => $rate['service_name'], 'service_code' => $rate['service_code'], 'routing_code' => $rate['carrier_code'], 'package_type' => $rate['type']));
}
}
return $service;
}
public static function getListPostcode($area, $district)
{
$criteria = new CDbCriteria();
$criteria->select = 't.name,t.postcode';
$criteria->join = 'JOIN zone t2 ON t2.id = t.zone_id JOIN district t3 ON t3.id = t2.district_id';
$criteria->condition = 't.name LIKE :sterm AND t3.name LIKE :district';
$criteria->params = array(":sterm" => "%{$area}%", ":district" => "%{$district}%");
$builder = new CDbCommandBuilder(Yii::app()->db->Schema);
$command = $builder->createFindCommand('area', $criteria);
$data_postcodes = $command->queryAll();
$data = array();
foreach ($data_postcodes as $data_postcode) {
$data[] = array('value' => $data_postcode['postcode'], 'label' => $data_postcode['name']);
}
return $data;
}
public static function getServiceList($from, $did, $zid, $weight = 0, $product_id, $customer_id = null, $use_rate = array())
{
$services = array();
$rates = array();
if (!!$customer_id && count($use_rate) == 0) {
return array();
}
if (!!$customer_id) {
$join = 'JOIN rate_company_service t2 on t.service_id = t2.id JOIN rate_company t3 ON t2.rate_company_id = t3.id JOIN service_detail t4 ON t4.rate_company_service_id = t2.id JOIN product_service t5 ON t5.id = t4.product_service_id JOIN customer_discount t6 on t6.service_id = t4.id';
$select = 't.service_id as service_id,t.first_kg as first_kg,t.next_kg as next_kg,t5.code as service_code, t3.code as carrier_code,t3.name as carrier_name,t.max_transit_time as transit_day,t5.name as service_name,t6.discount_api,t.id as id';
} else {
$join = 'JOIN rate_company_service t2 on t.service_id = t2.id JOIN rate_company t3 ON t2.rate_company_id = t3.id JOIN service_detail t4 ON t4.rate_company_service_id = t2.id JOIN product_service t5 ON t5.id = t4.product_service_id';
$select = 't.service_id as service_id,t.first_kg as first_kg,t.next_kg as next_kg,t5.code as service_code, t3.code as carrier_code,t3.name as carrier_name,t.max_transit_time as transit_day,t5.name as service_name,t.id as id';
}
$criteria = new CDbCriteria();
$criteria->join = $join;
$criteria->select = $select;
$criteria->compare('t5.product_id', $product_id, FALSE);
$criteria->compare('origin_id', $from, FALSE);
if ($zid == 0) {
$criteria->params = array(':zone_id' => 0, ':district_id' => $did);
$criteria->condition = 'zone_id=:zone_id AND district_id=:district_id';
if (!!$customer_id) {
$criteria->params[':customer_id'] = $customer_id;
$criteria->addCondition('t6.customer_id =:customer_id AND t6.use_rate = 1');
}
$builder = new CDbCommandBuilder(Yii::app()->db->Schema);
$command = $builder->createFindCommand('rate_domestic', $criteria);
$rates_by_did = $command->queryAll();
if (count($rates_by_did) > 0) {
$rates = $rates_by_did;
}
} else {
$criteria->params = array(':zone_id' => $zid);
$criteria->condition = 'zone_id=:zone_id';
if (!!$customer_id) {
$criteria->params[':customer_id'] = $customer_id;
$criteria->addCondition('t6.customer_id =:customer_id AND t6.use_rate = 1');
}
$builder = new CDbCommandBuilder(Yii::app()->db->Schema);
$command = $builder->createFindCommand('rate_domestic', $criteria);
$rates_by_zid = $command->queryAll();
if (count($rates_by_zid) > 0) {
$rates = $rates_by_zid;
} else {
$criteria->params = array(':zone_id' => 0, ':district_id' => $did);
$criteria->condition = 'zone_id=:zone_id AND district_id=:district_id';
if (!!$customer_id) {
$criteria->params[':customer_id'] = $customer_id;
$criteria->addCondition('t6.customer_id =:customer_id AND t6.use_rate = 1');
}
$builder = new CDbCommandBuilder(Yii::app()->db->Schema);
$command = $builder->createFindCommand('rate_domestic', $criteria);
$rates_by_did = $command->queryAll();
if ($rates_by_did > 0) {
$rates = $rates_by_did;
}
}
}
if (count($rates) > 0) {
foreach ($rates as $rate) {
if ($weight < 1) {
$ratesAvailable = 0;
} else {
if ($weight >= 1) {
$ratesAvailable = $rate['first_kg'];
if ($weight != 1) {
$ratesAvailable = $ratesAvailable + $rate['next_kg'] * ceil($weight - 1);
}
}
}
if (!!$customer_id) {
$ratesAvailable = $ratesAvailable - $ratesAvailable * ($rate['discount_api'] / 100);
}
$service = array('id' => $rate['id'], 'service_id' => $rate['service_id'], 'routing_code' => $rate['carrier_code'], 'carrier_name' => $rate['carrier_name'], 'service_code' => $rate['service_code'], 'service_name' => $rate['service_name'], 'price' => $ratesAvailable, 'transits_days' => $rate['transit_day'] . ' hari');
array_push($services, $service);
}
}
return $services;
}
public static function getZoneCountryData($country)
{
$criteria = new CDbCriteria();
$criteria->select = 'zone';
$criteria->compare('country', ucfirst($country), 'AND', FALSE);
$builder = new CDbCommandBuilder(Yii::app()->db->Schema);
$command = $builder->createFindCommand('zone_international', $criteria);
$data = $command->queryRow();
return $data;
}
/**
* Создает команду SELECT для отдельной таблицы.
* Переопределяет родительскую реализацию для проверки наличия выражения ORDER BY в случае
* наличия определенного смещения
* @param mixed $table схема таблицы ({@link CDbTableSchema}) или имя таблицы (строка)
* @param CDbCriteria $criteria критерий запроса
* @param string $alias псевдоним первичной таблицы. По умолчанию - 't'
* @return CDbCommand команда запроса
*/
public function createFindCommand($table, $criteria, $alias = 't')
{
$criteria = $this->checkCriteria($table, $criteria);
return parent::createFindCommand($table, $criteria, $alias);
}
/**
* Retrieves a list of models based on the current search/filter conditions.
* @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
*/
public function searchFrontend($criteria = false, $category = false)
{
if (!$criteria) {
$criteria = new SDbCriteria();
}
$criteria->compare('active', self::ACTIVE);
if ($this->searchNameIndex) {
$criteria->compare('name_index', $this->searchNameIndex, true);
} else {
$criteria->compare('id_brand', $this->id_brand);
$criteria->group = 't.id';
if ($this->price) {
list($priceFrom, $priceTo) = explode(',', $this->price);
$criteria->addCondition("price >= :priceFrom AND price <= :priceTo");
$criteria->params += [':priceFrom' => $priceFrom, ':priceTo' => $priceTo];
}
$criteria->together = true;
}
if ($category) {
$criteria->compare('t.id_category', ProductCategory::descendantsIds($category->id, true));
}
if (!$this->searchNameIndex) {
//Для получения минимальной и максимальной цены продукта, чтобы проставить в фильтре цены
$builder = new CDbCommandBuilder(Yii::app()->db->getSchema());
$minMaxPriceCriteria = clone $criteria;
$minMaxPriceCriteria->select = "t.price";
$command = $builder->createFindCommand(Product::model()->tableName(), $minMaxPriceCriteria);
$result = $command->queryColumn();
if ($result) {
$this->max_price = max($result);
}
if ($this->productOptions) {
$criteria->with[] = 'options';
$criteria->compare('options.id_variation', $this->productOptions);
}
}
$pageSize = Cookie::get('product-list-view') == 'thumbnail' ? self::PAGESIZE_THUMBNAIL : self::PAGESIZE_LIST;
return new SActiveDataProvider($this, array('criteria' => $criteria, 'pagination' => array('pageSize' => $pageSize, 'pageVar' => 'page'), 'sort' => ['attributes' => ['price', 'name', 'viewed']]));
}
