public static function upload($filepath)
{
\DB::beginTransaction();
try {
$reader = ReaderFactory::create(Type::XLSX);
// for XLSX files
$reader->open($filepath);
$cnt = 0;
Store::where('active', 1)->update(['active' => 0]);
foreach ($reader->getSheetIterator() as $sheet) {
foreach ($sheet->getRowIterator() as $row) {
if ($cnt > 0) {
// dd($row);
$area = Area::firstOrCreate(['area' => strtoupper($row[0])]);
$enrollment = Enrollment::firstOrCreate(['enrollment' => strtoupper($row[1])]);
$distributor = Distributor::firstOrCreate(['distributor_code' => strtoupper($row[2]), 'distributor' => strtoupper($row[3])]);
$client = Client::firstOrCreate(['client_code' => strtoupper($row[8]), 'client_name' => strtoupper($row[9])]);
$channel = Channel::firstOrCreate(['channel_code' => strtoupper($row[10]), 'channel_desc' => strtoupper($row[11])]);
$agency = Agency::firstOrCreate(['agency_code' => strtoupper($row[19]), 'agency_name' => strtoupper($row[20])]);
$region = Region::firstOrCreate(['region_code' => strtoupper($row[16]), 'region' => strtoupper($row[15]), 'region_short' => strtoupper($row[14])]);
$customer = Customer::firstOrCreate(['customer_code' => strtoupper($row[12]), 'customer_name' => strtoupper($row[13])]);
$user = User::where('username', strtoupper($row[22]))->first();
if (empty($user) && !empty($row[22])) {
// dd($row);
$user = User::firstOrCreate(['username' => strtoupper($row[22]), 'name' => strtoupper($row[22]), 'email' => strtoupper($row[22]) . '@pcount.com', 'password' => \Hash::make($row[22])]);
$user->roles()->attach(2);
}
$storeExist = Store::where('store_code', strtoupper($row[5]))->first();
if (empty($storeExist) && !empty($row[22])) {
$store = Store::create(['storeid' => strtoupper($row[4]), 'store_code' => strtoupper($row[5]), 'store_code_psup' => strtoupper($row[6]), 'store_name' => strtoupper($row[7]), 'area_id' => $area->id, 'enrollment_id' => $enrollment->id, 'distributor_id' => $distributor->id, 'client_id' => $client->id, 'channel_id' => $channel->id, 'customer_id' => $customer->id, 'region_id' => $region->id, 'agency_id' => $agency->id, 'active' => 1]);
if (!empty($row[22])) {
StoreUser::insert(['store_id' => $store->id, 'user_id' => $user->id]);
}
} else {
$storeExist->storeid = strtoupper($row[4]);
$storeExist->store_code = strtoupper($row[5]);
$storeExist->store_code_psup = strtoupper($row[6]);
$storeExist->store_name = strtoupper($row[7]);
$storeExist->area_id = $area->id;
$storeExist->enrollment_id = $enrollment->id;
$storeExist->distributor_id = $distributor->id;
$storeExist->client_id = $client->id;
$storeExist->channel_id = $channel->id;
$storeExist->customer_id = $customer->id;
$storeExist->region_id = $region->id;
$storeExist->agency_id = $agency->id;
$storeExist->active = 1;
$storeExist->save();
StoreUser::where('store_id', $storeExist->id)->delete();
StoreUser::insert(['store_id' => $storeExist->id, 'user_id' => $user->id]);
}
}
$cnt++;
}
}
\DB::commit();
} catch (Exception $e) {
dd($e);
\DB::rollback();
}
}
/**
* Run the database seeds.
*
* @return void
*/
public function run()
{
//
Model::unguard();
DB::statement('SET FOREIGN_KEY_CHECKS=0;');
$folderpath = base_path() . '/database/seeds/templates/';
$folders = File::directories($folderpath);
$latest = '11232015';
foreach ($folders as $value) {
$_dir = explode("/", str_replace('\\', '/', $value));
$cnt = count($_dir);
$name = $_dir[$cnt - 1];
$latest_date = DateTime::createFromFormat('mdY', $latest);
$now = DateTime::createFromFormat('mdY', $name);
if ($now > $latest_date) {
$latest = $name;
}
}
$filePath = $folderpath . $latest . '/Masterfile.xlsx';
$reader = ReaderFactory::create(Type::XLSX);
// for XLSX files
$reader->open($filePath);
DB::table('channel_items')->truncate();
foreach ($reader->getSheetIterator() as $sheet) {
if ($sheet->getName() == 'MKL Mapping') {
$cnt = 0;
foreach ($sheet->getRowIterator() as $row) {
if ($row[0] != '') {
if ($cnt > 0) {
// dd($row);
if (!ctype_digit(trim($row[4])) || !ctype_digit(trim($row[5])) || !ctype_digit(trim($row[6]))) {
InvalidMapping::create(['premise_code' => trim($row[0]), 'customer_code' => trim($row[1]), 'store_code' => trim($row[2]), 'sku_code' => trim($row[3]), 'ig' => trim($row[4]), 'multiplier' => trim($row[5]), 'minstock' => trim($row[6]), 'type' => 'MKL Mapping', 'remarks' => 'Invalid mapping']);
} else {
$channel = '';
if (trim($row[0]) != '') {
$channel = Channel::where('channel_code', trim($row[0]))->first();
}
$item = Item::where('sku_code', trim($row[3]))->first();
if (!empty($item)) {
$item_type = ItemType::where('type', "MKL")->first();
$osa_tagging = 0;
if (isset($row[7])) {
$osa_tagging = trim($row[7]);
}
$npi_tagging = 0;
if (isset($row[8])) {
$npi_tagging = trim($row[8]);
}
ChannelItem::firstOrCreate(['channel_id' => $channel->id, 'item_id' => $item->id, 'item_type_id' => $item_type->id, 'ig' => trim($row[4]), 'fso_multiplier' => trim($row[5]), 'min_stock' => trim($row[6]), 'osa_tagged' => $osa_tagging, 'npi_tagged' => $npi_tagging]);
}
}
}
$cnt++;
}
}
}
}
$reader->close();
}
public function run()
{
Model::unguard();
DB::statement('SET FOREIGN_KEY_CHECKS=0;');
$folderpath = base_path() . '/database/seeds/seed_files/';
$folders = File::directories($folderpath);
$latest = '11232015';
foreach ($folders as $value) {
$_dir = explode("/", str_replace('\\', '/', $value));
$cnt = count($_dir);
$name = $_dir[$cnt - 1];
$latest_date = DateTime::createFromFormat('mdY', $latest);
$now = DateTime::createFromFormat('mdY', $name);
if ($now > $latest_date) {
$latest = $name;
}
}
$filePath = $folderpath . $latest . '/Masterfile.xlsx';
$reader = ReaderFactory::create(Type::XLSX);
// for XLSX files
$reader->open($filePath);
// DB::table('store_items');
foreach ($reader->getSheetIterator() as $sheet) {
if ($sheet->getName() == 'Assortment Mapping') {
$cnt = 0;
foreach ($sheet->getRowIterator() as $row) {
if ($row[0] != '') {
if ($cnt > 0) {
if (!ctype_digit(trim($row[4]))) {
InvalidMapping::create(['premise_code' => trim($row[0]), 'customer_code' => trim($row[1]), 'store_code' => trim($row[2]), 'sku_code' => trim($row[3]), 'ig' => trim($row[4]), 'multiplier' => trim($row[5]), 'minstock' => trim($row[6]), 'type' => 'Assortment Mapping', 'remarks' => 'Invalid mapping']);
} else {
$channel = '';
$customer = '';
$store = '';
if (trim($row[0]) != '') {
$channel = Channel::where('channel_code', trim($row[0]))->get();
}
if (trim($row[1]) != '') {
$customer = Customer::where('customer_code', trim($row[1]))->get();
}
if (trim($row[2]) != '') {
$store = Store::where('store_code', trim($row[2]))->first();
}
// dd($store);
$stores = Store::where(function ($query) use($channel) {
if (!empty($channel)) {
$channel_id = [];
foreach ($channel as $value) {
$channel_id[] = $value->id;
}
$query->whereIn('channel_id', $channel_id);
}
})->where(function ($query) use($customer) {
if (!empty($customer)) {
$customer_id = [];
foreach ($customer as $value) {
$customer_id[] = $value->id;
}
$query->whereIn('customer_id', $customer_id);
}
})->where(function ($query) use($store) {
if (!empty($store)) {
$query->where('store', $store->id);
}
})->get();
// dd($stores);
$item = Item::where('sku_code', trim($row[3]))->first();
if (!empty($item)) {
$item_type = ItemType::where('type', "ASSORTMENT")->first();
foreach ($stores as $store) {
$w_mkl = StoreItem::where('store_id', $store->id)->where('item_id', $item->id)->get();
$cw_mkl = ChannelItem::where('channel_id', $store->channel_id)->where('item_id', $item->id)->get();
if (count($w_mkl) == 0) {
StoreItem::firstOrCreate(['store_id' => $store->id, 'item_id' => $item->id, 'item_type_id' => $item_type->id, 'ig' => trim($row[4]), 'fso_multiplier' => trim($row[5]), 'min_stock' => trim($row[6]), 'osa_tagged' => 0, 'npi_tagged' => 0]);
}
if (count($cw_mkl) == 0) {
ChannelItem::firstOrCreate(['channel_id' => $store->channel_id, 'item_id' => $item->id, 'item_type_id' => $item_type->id, 'ig' => trim($row[4]), 'fso_multiplier' => trim($row[5]), 'min_stock' => trim($row[6]), 'osa_tagged' => 0, 'npi_tagged' => 0]);
}
}
}
}
}
$cnt++;
}
}
}
}
$reader->close();
$hash = UpdateHash::find(1);
if (empty($hash)) {
UpdateHash::create(['hash' => \Hash::make(date('Y-m-d H:i:s'))]);
} else {
$hash->hash = md5(date('Y-m-d H:i:s'));
$hash->update();
}
DB::statement('SET FOREIGN_KEY_CHECKS=1;');
Model::reguard();
}
public function actionGetData($channelName)
{
$channel = Channel::find()->where(['channelName' => $channelName])->one();
if (!Yii::$app->user->isGuest) {
UserChannel::updateUserLastAccess($channel, Yii::$app->user->identity->id);
}
UserChannel::clearOfflineUsers();
$result = json_encode(['usersList' => $channel->formattedUsers, 'messages' => $channel->formattedMessages]);
return $result;
}
public function actionChannelreport()
{
$listChannel = Channel::find()->where('enabled<>0')->asArray()->all();
$startDate = strtotime(Yii::$app->request->get('startdate') . ' 00:00:00');
$endDate = strtotime(Yii::$app->request->get('enddate') . ' 23:59:59');
for ($i = 0; $i < count($listChannel); $i++) {
$baseQuery = Record::find()->andWhere(['channel_id' => $listChannel[$i]['id']]);
$baseQuery->andWhere(['>', 'created_at', $startDate]);
$baseQuery->andWhere(['<', 'created_at', $endDate]);
$tempQuery = clone $baseQuery;
$listChannel[$i]['total'] = $tempQuery->count();
$tempQuery = clone $baseQuery;
$listChannel[$i]['validcount'] = $tempQuery->andWhere(['is_valid' => 1])->count();
$tempQuery = clone $baseQuery;
$listChannel[$i]['reservecount'] = $tempQuery->andWhere(['is_reserve' => 1])->count();
$tempQuery = clone $baseQuery;
$listChannel[$i]['arrivedcount'] = $tempQuery->andWhere(['is_arrive' => 1])->count();
//消费
$totalCost = 0;
$cost = ChannelCost::find()->where('startdate>=:startdate and enddate<=:enddate and channel_id=:channel_id', [':startdate' => $startDate, ':enddate' => $endDate, ':channel_id' => $listChannel[$i]['id']])->sum('fee');
$totalCost += $cost;
//后开始还未到结束日期的费用
$channelCost = ChannelCost::find()->where('startdate>=:startdate and enddate>:enddate and channel_id=:channel_id', [':startdate' => $startDate, ':enddate' => $endDate, ':channel_id' => $listChannel[$i]['id']])->all();
foreach ($channelCost as $tmpCost) {
$days = ceil(($tmpCost['enddate'] - $tmpCost['startdate']) / 86400);
$avgCost = $tmpCost['fee'] / $days;
//平均每天费用
$days = ceil(($endDate - $tmpCost['startdate']) / 86400);
$cost = round($avgCost * $days, 2);
$totalCost += $cost;
}
//先开始提前结束的费用
$channelCost = ChannelCost::find()->where('startdate<:startdate and enddate<=:enddate and channel_id=:channel_id', [':startdate' => $startDate, ':enddate' => $endDate, ':channel_id' => $listChannel[$i]['id']])->all();
foreach ($channelCost as $tmpCost) {
$days = ceil(($tmpCost['enddate'] - $tmpCost['startdate']) / 86400);
$avgCost = $tmpCost['fee'] / $days;
$days = ceil(($tmpCost['enddate'] - $startDate) / 86400);
$cost = round($avgCost * $days, 2);
$totalCost += $cost;
}
//提前开始还未结束的费用
$channelCost = ChannelCost::find()->where('startdate<:startdate and enddate>:enddate and channel_id=:channel_id', [':startdate' => $startDate, ':enddate' => $endDate, ':channel_id' => $listChannel[$i]['id']])->all();
foreach ($channelCost as $tmpCost) {
$days = ceil(($tmpCost['enddate'] - $tmpCost['startdate']) / 86400);
$avgCost = $tmpCost['fee'] / $days;
$days = ceil(($endDate - $startDate) / 86400);
$cost = round($avgCost * $days, 2);
$totalCost += $cost;
}
$listChannel[$i]['cost'] = $totalCost;
}
return $this->renderPartial('channelreport', ['model' => $listChannel]);
}
/**
* Creates data provider instance with search query applied
*
* @param array $params
*
* @return ActiveDataProvider
*/
public function search($params)
{
$query = Channel::find();
$dataProvider = new ActiveDataProvider(['query' => $query]);
$this->load($params);
if (!$this->validate()) {
// uncomment the following line if you do not want to return any records when validation fails
// $query->where('0=1');
return $dataProvider;
}
$query->andFilterWhere(['id' => $this->id]);
$query->andFilterWhere(['like', 'channelName', $this->channelName])->andFilterWhere(['like', 'description', $this->description]);
return $dataProvider;
}
/**
* @param array $data
* Пытается распарсить значения сообщения из массива, что приходит с пост-запросом обычно
*/
public function parse($data)
{
if (!Yii::$app->user->isGuest) {
$this->senderId = Yii::$app->user->identity->id;
}
if ($data['message']) {
$this->messageText = $data['message'];
}
if ($data['channelName']) {
$channel = Channel::find()->where(['channelName' => $data['channelName']])->one();
if ($channel) {
$this->channelId = $channel->id;
}
}
if ($data['receiver'] && strlen($data['receiver']) > 0) {
$receiverUser = User::find()->where(['username' => $data['receiver']])->one();
if ($receiverUser) {
$this->receiverId = $receiverUser->id;
} else {
$this->receiverId = 0;
}
}
}
public function getChannel()
{
return $this->hasOne(Channel::className(), ['id' => 'channel_id']);
}
public function actionAppointment()
{
$listDepartment = ArrayHelper::map(Department::find()->where('enabled<>0')->all(), 'id', 'name');
$listChannel = ArrayHelper::map(Channel::find()->where('enabled<>0')->all(), 'id', 'name');
$listDoctor = ArrayHelper::map(Doctor::find()->where('enabled<>0')->all(), 'id', 'name');
$listUser = ArrayHelper::map(User::find()->where('enabled<>0')->all(), 'id', 'username');
return $this->render('appointment', ['model' => new Record(), 'listDepartment' => $listDepartment, 'listChannel' => $listChannel, 'listDoctor' => $listDoctor, 'listUser' => $listUser]);
}
public function actionCost()
{
$listChannel = ArrayHelper::map(Channel::find()->where('enabled<>0')->all(), 'id', 'name');
return $this->render('cost', ['model' => new ChannelCost(), 'listChannel' => $listChannel]);
}
public function run()
{
Model::unguard();
DB::statement('SET FOREIGN_KEY_CHECKS=0;');
$folderpath = base_path() . '/database/seeds/seed_files/';
$folders = File::directories($folderpath);
$latest = '11232015';
foreach ($folders as $value) {
$_dir = explode("/", str_replace('\\', '/', $value));
$cnt = count($_dir);
$name = $_dir[$cnt - 1];
$latest_date = DateTime::createFromFormat('mdY', $latest);
$now = DateTime::createFromFormat('mdY', $name);
if ($now > $latest_date) {
$latest = $name;
}
}
echo $latest;
$filePath = $folderpath . $latest . '/Masterfile.xlsx';
$reader = ReaderFactory::create(Type::XLSX);
// for XLSX files
$reader->open($filePath);
echo 'Seeding ' . $filePath . PHP_EOL;
// DB::table('areas')->truncate();
// DB::table('enrollments')->truncate();
// DB::table('distributors')->truncate();
// DB::table('clients')->truncate();
// DB::table('channels')->truncate();
// DB::table('agencies')->truncate();
// DB::table('regions')->truncate();
// DB::table('customers')->truncate();
// DB::table('stores')->truncate();
// DB::table('invalid_stores')->truncate();
// DB::table('store_users')->truncate();
// $role = Role::find(2)->users()->delete();
// dd($role);
// add masterfiles
foreach ($reader->getSheetIterator() as $sheet) {
if ($sheet->getName() == 'Stores') {
$cnt = 0;
Store::where('active', 1)->update(['active' => 0]);
foreach ($sheet->getRowIterator() as $row) {
if ($row[0] != '') {
if ($cnt > 0) {
// if(strtoupper($row[23]) == 'INACTIVE'){
// InvalidStore::invalid($row,'Inactive Store');
// }else{
$area = Area::firstOrCreate(['area' => strtoupper($row[0])]);
$enrollment = Enrollment::firstOrCreate(['enrollment' => strtoupper($row[1])]);
$distributor = Distributor::firstOrCreate(['distributor_code' => strtoupper($row[2]), 'distributor' => strtoupper($row[3])]);
$client = Client::firstOrCreate(['client_code' => strtoupper($row[8]), 'client_name' => strtoupper($row[9])]);
$channel = Channel::firstOrCreate(['channel_code' => strtoupper($row[10]), 'channel_desc' => strtoupper($row[11])]);
$agency = Agency::firstOrCreate(['agency_code' => strtoupper($row[19]), 'agency_name' => strtoupper($row[20])]);
$region = Region::firstOrCreate(['region_code' => strtoupper($row[16]), 'region' => strtoupper($row[15]), 'region_short' => strtoupper($row[14])]);
$customer = Customer::firstOrCreate(['customer_code' => strtoupper($row[12]), 'customer_name' => strtoupper($row[13])]);
$user = User::where('username', strtoupper($row[22]))->first();
if (empty($user) && !empty($row[22])) {
$user = User::firstOrCreate(['username' => strtoupper($row[22]), 'name' => strtoupper($row[22]), 'email' => strtoupper($row[22]) . '@pcount.com', 'password' => Hash::make($row[22])]);
$user->roles()->attach(2);
}
$storeExist = Store::where('store_code', strtoupper($row[5]))->first();
if (empty($storeExist) && !empty($row[22])) {
$store = Store::create(['storeid' => strtoupper($row[4]), 'store_code' => strtoupper($row[5]), 'store_code_psup' => strtoupper($row[6]), 'store_name' => strtoupper($row[7]), 'area_id' => $area->id, 'enrollment_id' => $enrollment->id, 'distributor_id' => $distributor->id, 'client_id' => $client->id, 'channel_id' => $channel->id, 'customer_id' => $customer->id, 'region_id' => $region->id, 'agency_id' => $agency->id, 'active' => 1]);
if (!empty($row[22])) {
StoreUser::insert(['store_id' => $store->id, 'user_id' => $user->id]);
}
} else {
// InvalidStore::invalid($row,'Duplicate Store Code');
$storeExist->storeid = strtoupper($row[4]);
$storeExist->store_code = strtoupper($row[5]);
$storeExist->store_code_psup = strtoupper($row[6]);
$storeExist->store_name = strtoupper($row[7]);
$storeExist->area_id = $area->id;
$storeExist->enrollment_id = $enrollment->id;
$storeExist->distributor_id = $distributor->id;
$storeExist->client_id = $client->id;
$storeExist->channel_id = $channel->id;
$storeExist->customer_id = $customer->id;
$storeExist->region_id = $region->id;
$storeExist->agency_id = $agency->id;
$storeExist->active = 1;
$storeExist->save();
StoreUser::where('store_id', $storeExist->id)->delete();
StoreUser::insert(['store_id' => $storeExist->id, 'user_id' => $user->id]);
}
// }
}
$cnt++;
}
}
}
}
$reader->close();
DB::statement('SET FOREIGN_KEY_CHECKS=1;');
Model::reguard();
}
/**
* Finds the Channel model based on its primary key value.
* If the model is not found, a 404 HTTP exception will be thrown.
* @param integer $id
* @return Channel the loaded model
* @throws NotFoundHttpException if the model cannot be found
*/
protected function findModel($id)
{
if (($model = Channel::findOne($id)) !== null) {
return $model;
} else {
throw new NotFoundHttpException('The requested page does not exist.');
}
}
