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();
}
}
public function delete($id)
{
if (Agency::destroy($id)) {
return redirect()->back()->with('msg', 'Агентство удалено');
}
return redirect()->back()->with('warning', 'Не удалось удалить');
}
/**
* Finds user by [[username]]
*
* @return User|null
*/
protected function getUser()
{
if ($this->_user === null) {
//$this->_user = User::findByUsername($this->username);
$this->_user = Agency::findByUsername($this->username);
}
return $this->_user;
}
/**
* Экшн для выведения отчета за выбраный период времени
* @return string
*/
public function actionSecondResult()
{
$startDate = \Yii::$app->request->post('from');
$endDate = \Yii::$app->request->post('to');
$dateRange = array($startDate, $endDate);
$model = Agency::find()->with('network')->all();
return $this->render('second-result', ['model' => $model, 'dateRange' => $dateRange]);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getAgency()
{
return $this->hasOne(Agency::className(), ['agency_id' => 'agency_id']);
}
/**
* Update the specified resource in storage.
*
* @param \Illuminate\Http\Request $request
* @param int $id
* @return \Illuminate\Http\Response
*/
public function update(Request $request, $id)
{
//
$this->validate($request, ['area_id' => 'required', 'enrollment_id' => 'required', 'distributor_id' => 'required', 'client_id' => 'required', 'channel_id' => 'required', 'customer_id' => 'required', 'region_id' => 'required', 'agency_id' => 'required', 'store_name' => 'required', 'store_id' => 'required']);
// $diff_items = array_diff( $channel_items, $store_items );
// $same_items = array_intersect( $channel_items, $store_items );
// $add_store_items = ChannelItem::select('item_id',
// 'item_type_id',
// 'ig',
// 'fso_multiplier',
// 'min_stock',
// 'ig_updated',
// 'osa_tagged',
// 'npi_tagged' )
// ->whereIn('item_id',$diff_items)
// ->where('channel_id',$request->channel_id)
// ->get();
// foreach ($add_store_items as &$data) {
// $data->store_id = $id;
// }
// $delete = StoreItem::where('store_id',$id)->whereNotIn('item_id',$same_items)->delete();
// foreach ($add_store_items as $data) {
// $check[] = StoreItem::firstOrCreate([
// 'store_id' => $data->store_id,
// 'item_id' => $data->item_id,
// 'item_type_id' => $data->item_type_id,
// 'ig' => $data->ig,
// 'fso_multiplier' => $data->fso_multiplier,
// 'min_stock' => $data->min_stock,
// 'ig_updated' => $data->ig_updated,
// 'osa_tagged' => $data->npi_tagged ]);
// }
$store = Store::findOrFail($id);
//for mkl
$mkl_store_items = StoreItem::where('store_id', $id)->where('item_type_id', 1)->get()->pluck('item_id')->toArray();
//get all the item from store mkl
$mkl_channel_items = ChannelItem::where('channel_id', $request->channel_id)->where('item_type_id', 1)->get()->pluck('item_id')->toArray();
//for assortment
$assortment_store_items = StoreItem::where('store_id', $id)->where('item_type_id', 2)->get()->pluck('item_id')->toArray();
//get all the item from store assortment
$assortment_channel_items = ChannelItem::where('channel_id', $request->channel_id)->where('item_type_id', 2)->get()->pluck('item_id')->toArray();
//for mkl
foreach ($mkl_store_items as $value) {
if (!in_array($value, $mkl_channel_items)) {
$delete = StoreItem::where('store_id', $id)->where('item_type_id', 1)->where('item_id', $value)->delete();
}
}
$mkl_remaining_items = StoreItem::where('store_id', $id)->where('item_type_id', 1)->get()->pluck('item_id')->toArray();
foreach ($mkl_channel_items as $value) {
if (!in_array($value, $mkl_remaining_items)) {
$data = ChannelItem::where('item_id', $value)->where('channel_id', $request->channel_id)->where('item_type_id', 1)->first();
StoreItem::firstOrCreate(['store_id' => $id, 'item_id' => $data->item_id, 'item_type_id' => $data->item_type_id, 'ig' => $data->ig, 'fso_multiplier' => $data->fso_multiplier, 'min_stock' => $data->min_stock, 'ig_updated' => $data->ig_updated, 'osa_tagged' => $data->npi_tagged]);
}
}
//for assortment
foreach ($assortment_store_items as $value) {
if (!in_array($value, $assortment_channel_items)) {
$delete = StoreItem::where('store_id', $id)->where('item_type_id', 2)->where('item_id', $value)->delete();
}
}
$assortment_remaining_items = StoreItem::where('store_id', $id)->where('item_type_id', 2)->get()->pluck('item_id')->toArray();
foreach ($assortment_channel_items as $value) {
if (!in_array($value, $assortment_remaining_items)) {
$data = ChannelItem::where('item_id', $value)->where('channel_id', $request->channel_id)->where('item_type_id', 2)->first();
$w_mkl = StoreItem::where('store_id', $id)->where('item_id', $value)->get();
if (count($w_mkl) == 0) {
StoreItem::firstOrCreate(['store_id' => $id, 'item_id' => $data->item_id, 'item_type_id' => $data->item_type_id, 'ig' => $data->ig, 'fso_multiplier' => $data->fso_multiplier, 'min_stock' => $data->min_stock, 'ig_updated' => $data->ig_updated, 'osa_tagged' => $data->npi_tagged]);
}
}
}
//end
$store->area_id = $request->area_id;
$store->enrollment_id = $request->enrollment_id;
$store->distributor_id = $request->distributor_id;
$store->client_id = $request->client_id;
$store->channel_id = $request->channel_id;
$store->customer_id = $request->customer_id;
$store->region_id = $request->region_id;
$store->agency_id = $request->agency_id;
$store->store_name = $request->store_name;
$store->storeid = $request->store_id;
$store->store_code = $request->store_code;
$store->store_code_psup = $request->store_code_psup;
$store->active = $request->status;
$store->update();
\DB::table('store_users')->where('user_id', $request->userid)->where('store_id', $id)->update(['user_id' => $request->user_id]);
$store = Store::findOrFail($id);
$area = Area::orderBy('area', 'ASC')->lists('area', 'id');
$enrollment = Enrollment::orderBy('enrollment', 'ASC')->lists('enrollment', 'id');
$distributor = Distributor::orderBy('distributor', 'ASC')->lists('distributor', 'id');
$client = Client::orderBy('client_name', 'ASC')->lists('client_name', 'id');
$channel = channel::orderBY('channel_desc', 'ASC')->lists('channel_desc', 'id');
$customer = Customer::orderBy('customer_name', 'ASC')->lists('customer_name', 'id');
$region = Region::orderBy('region_short', 'ASC')->lists('region_short', 'id');
$agency = Agency::orderBy('agency_name', 'ASC')->lists('agency_name', 'id');
$status = ['0' => 'In-active', '1' => 'Active'];
$user = StoreUser::where('store_id', $id)->first();
$alluser = User::all()->lists('username', 'id');
$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();
}
Session::flash('flash_class', 'alert-success');
Session::flash('flash_message', 'Store successfully updated.');
return view('store.edit', ['store' => $store, 'area' => $area, 'enrollment' => $enrollment, 'distributor' => $distributor, 'client' => $client, 'channel' => $channel, 'customer' => $customer, 'region' => $region, 'agency' => $agency, 'status' => $status, 'user' => $user, 'alluser' => $alluser]);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getAgencies()
{
return $this->hasMany(Agency::className(), ['network_id' => 'agency_network_id']);
}
public function actionQ2()
{
$rows = [];
$process_going = file_exists(Yii::$app->basePath . '/data/2/lock');
$cmd = Yii::$app->request->post('startload');
if (!$process_going && $cmd !== null) {
$cmd = "php " . Yii::$app->basePath . "/yii data/load2";
$ret = exec($cmd, $output);
$process_going = true;
} else {
}
$rows = Agency::find()->select(['{{AgencyNetwork}}.name as networkname', '{{Agency}}.name', 'SUM({{Billing}}.amount) AS agencysum'])->joinWith(['network'])->joinWith('billings', true, 'LEFT JOIN')->groupBy('{{Agency}}.name')->all();
$total = 0;
foreach ($rows as $row) {
$total += $row->agencysum;
}
return $this->render('q2', ['total' => $total, 'rows' => $rows, 'process_going' => $process_going, 'codefile' => __FILE__]);
}
/**
* Finds the Agency model based on its primary key value.
* If the model is not found, a 404 HTTP exception will be thrown.
* @param integer $id
* @return Agency the loaded model
* @throws NotFoundHttpException if the model cannot be found
*/
protected function findModel($id)
{
if (($model = Agency::findOne($id)) !== null) {
return $model;
} else {
throw new NotFoundHttpException('The requested page does not exist.');
}
}
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();
}
