protected function find()
 {
     $this->startKeyInDate = Input::get('start');
     $this->endKeyInDate = Input::get('end');
     if (!empty($this->startKeyInDate) && !empty($this->endKeyInDate)) {
         if ($this->endKeyInDate < $this->startKeyInDate) {
             $tmp = $this->startKeyInDate;
             $this->startKeyInDate = $this->endKeyInDate;
             $this->endKeyInDate = $tmp;
         }
     }
     $q = Processor::table('CCS_OrderDivIndex WITH(NOLOCK)')->select(DB::raw("MAX(CCS_OrderIndex.OrderNo) AS 單號,MAX(CCS_OrderIndex.KeyInDate) AS 出貨日,MAX(CCS_OrderIndex.OrderDate) AS 訂單日,MAX(CCS_OrderDivIndex.IndexSerNo) AS 流水號,MAX(CCS_OrderIndex.MustPayTotal) AS 應付帳款, MAX(POS_Member.Name) AS 會員姓名,COUNT(*) AS 分寄單數, MAX(CCS_OrderIndex.VerifyDate) AS 覆核日, MAX(CCS_OrderIndex.Status) AS 狀態, MAX(CCS_OrderDivIndex.City) AS 縣市, MAX(CCS_OrderDivIndex.Town) AS 區, MAX(CCS_OrderDivIndex.Address) AS 地址"))->leftJoin('CCS_OrderIndex WITH(NOLOCK)', 'CCS_OrderIndex.SerNo', '=', 'CCS_OrderDivIndex.IndexSerNo')->leftJoin('POS_Member WITH(NOLOCK)', 'POS_Member.SerNo', '=', 'CCS_OrderIndex.MemberSerNo')->groupBy('CCS_OrderDivIndex.IndexSerNo')->orderBy('MAX(CCS_OrderIndex.KeyInDate)');
     if (!empty($this->startKeyInDate)) {
         $q->where('CCS_OrderIndex.KeyInDate', '>=', $this->startKeyInDate);
     }
     if (!empty($this->endKeyInDate)) {
         $q->where('CCS_OrderIndex.KeyInDate', '<=', $this->endKeyInDate);
     }
     if (empty($this->startKeyInDate) && empty($this->endKeyInDate)) {
         $this->startKeyInDate = Carbon::now()->format('Ymd');
         $this->endKeyInDate = Carbon::now()->format('Ymd');
         $q->where('CCS_OrderIndex.KeyInDate', '>=', $this->startKeyInDate);
         $q->where('CCS_OrderIndex.KeyInDate', '<=', $this->endKeyInDate);
     }
     $result = Processor::getArrayResult($q);
     return array_values($this->filter($result));
 }
 public function get(User $user)
 {
     $list = [];
     $qb = str_replace('$empserno', $user->serno, $this->getQueryProtoType());
     Processor::fetchArray($qb, $this->getInsertFunc(), $list);
     return $list;
 }
Exemple #3
0
 public static function testSQL()
 {
     $options = [];
     $q = Processor::table('Customer_lubri')->where('member_id', 'NOT LIKE', 'CT%')->where(self::getOr($options))->orderBy('member_id', 'DESC');
     pr(get_class($q));
     dd(Processor::toSql($q));
 }
 public function index()
 {
     $corpCodes = explode(',', env('CORPS'));
     $emps = HRS_Employee::findByCorps($corpCodes);
     $corps = Processor::getArrayResult(Processor::table('FAS_Corp')->select('Name, Code')->whereIn('Code', $corpCodes));
     $campaigns = Campaign::findValid();
     return view('report.ctilayout.index', ['corps' => $corps, 'empCorpGroups' => array_group($emps, 'FName'), 'campaigns' => $campaigns]);
 }
 public function rollback()
 {
     $members = Processor::getArrayResult("SELECT TOP 3000 * FROM POS_Member WITH(NOLOCK) WHERE Code LIKE 'T%' AND CRT_TIME>= '2016-02-18 00:00:00' ORDER BY SerNo DESC");
     foreach ($members as $member) {
         print "EXEC dbo.sp_DeleteHBMember '{$member['SerNo']}'<br/>";
     }
     return '<hr>請將畫面上的SQL語句貼在欲直行的資料庫進行還原~';
 }
Exemple #6
0
 public function setStartCode()
 {
     $query = 'SELECT TOP 1 MemberCardNo FROM POS_Member WHERE MemberCardNo LIKE \'T%\' ORDER BY Code DESC';
     $res = Processor::execErp($query);
     $member = odbc_fetch_array($res);
     $this->code = getArrayVal($member, 'MemberCardNo');
     return $this;
 }
Exemple #7
0
 public static function getBase(array $options)
 {
     return Processor::table('BasicDataDef')->where(function ($q) use($options) {
         foreach ($options as $column => $value) {
             $q->where($column, '=', $value);
         }
     });
 }
 public function copy(array $codes)
 {
     foreach ($codes as $code) {
         $lastSerNo = array_get(Processor::getArrayResult($this->genFetchLastSerNoQuery()), '0.SerNo');
         $row = Processor::getArrayResult($this->genSelectQuery([$code]));
         Processor::execErp($this->genInsertQuery($row[0], $lastSerNo));
     }
     return $this;
 }
 protected function fetchEachMonthData($dateString)
 {
     $data = [];
     for ($i = 0; $i < 12; $i++) {
         $date = with(new Carbon($dateString))->modify("+ {$i} months");
         $data[] = Processor::getArrayResult($this->getFetchQuery($date->modify('first day of this month')->format('Ymd'), $date->modify('last day of this month')->format('Ymd')));
     }
     return $data;
 }
Exemple #10
0
 public function import()
 {
     $insertRows = Processor::getArrayResult($this->getUserDataQuery());
     foreach ($insertRows as $row) {
         $this->insert($row);
     }
     Session::flash('success', '輔翼使用者資料匯入完成');
     return redirect('user');
 }
Exemple #11
0
 public static function fetchMightExistMembers($result)
 {
     $mightExistMembers = [];
     $nameQuery = self::getNameQuery(self::getNameList($result));
     if ($res = Processor::execErp($nameQuery)) {
         while ($mightExistMembers[] = odbc_fetch_array($res)) {
         }
     }
     return $mightExistMembers;
 }
Exemple #12
0
 protected static function getBasicSheetCallback($columnFormatArray, $borderRange, $query, $headArray)
 {
     return function ($sheet) use($columnFormatArray, $borderRange, $query, $headArray) {
         $sheet->setAutoSize(true)->setFontFamily(ExportExcel::FONT_DEFAULT)->setFontSize(12)->setColumnFormat($columnFormatArray)->freezeFirstRow();
         $sheet->cells('A1:' . $borderRange . '1', function ($cells) {
             $cells->setBackground('#000000')->setFontColor('#ffffff')->setAlignment('center');
         });
         if ($res = Processor::execErp($query)) {
             $i = 0;
             $sheet->row(++$i, $headArray);
             while ($row = odbc_fetch_array($res)) {
                 c8res($row);
                 $sheet->row(++$i, $row);
             }
         }
     };
 }
Exemple #13
0
 public function index()
 {
     $startTime = microtime(true);
     $i = 0;
     $codeIndexArray = $this->convertToCodeIndexArray();
     $res = Processor::execErp($this->genFetchQuery());
     while ($member = odbc_fetch_array($res)) {
         $cityAndStateArr = $this->getArrayVal($codeIndexArray, $member['HomeAddress_ZipCode'], ['city' => '', 'state' => '']);
         $member['HomeAddress'] = $cityAndStateArr['state'] . '$' . $cityAndStateArr['city'] . '$' . $this->c8($member['HomeAddress_Address']);
         $member['HomeAddress_Address'] = $this->c8($member['HomeAddress_Address']);
         $member['HomeAddress_City'] = $cityAndStateArr['city'];
         $member['HomeAddress_State'] = $cityAndStateArr['state'];
         $i++;
     }
     $endTime = microtime(true);
     $exectime = floor($endTime - $startTime);
     return view('basic.simple', ['title' => '會員地址修正', 'des' => '修正台北縣,空白的縣市和區,統一地址格式', 'res' => '共修正了' . $i . '筆,耗時' . $exectime . '秒']);
 }
Exemple #14
0
 protected function getNextCTOrderNo($ctOrderNo)
 {
     $res = Processor::getArrayResult("SELECT TOP 1 * FROM CCS_OrderIndex WHERE OrderNo='{$ctOrderNo}' ORDER BY SerNo DESC");
     $numPart = substr(array_get($res, '0.OrderNo'), 2);
     return self::COMETRUES_PREFIX . ++$numPart;
 }
Exemple #15
0
 public function updateProc(PosMemberImportContent $content)
 {
     Processor::execErp($this->getUpdateProcQuery($content));
     return $this->memberflagUpdateProc($content)->contentUpdateProc($content);
 }
Exemple #16
0
 protected function fetchCampaignsCount()
 {
     $sql = str_replace(['$yesterday', '$tomorrow'], [Carbon::now()->format('Y-m-d H:i:s'), Carbon::now()->format('Y-m-d H:i:s')], Processor::getStorageSql('FV/Import/campaign_count.sql'));
     return array_get(Processor::getArrayResult($sql, Processor::DB_CTI), 0)['_count'];
 }
 public function index()
 {
     dd(Processor::getArrayResult("select * FROM [dbo].[lubri_DSR_Product]('20160522', '20160531')"));
 }
Exemple #18
0
 protected function genAndGetEngSql($whereStr)
 {
     return str_replace('$whereStr', $whereStr, Processor::getStorageSql('CTILayout_ENG.sql'));
 }
 protected function getThisYearRetailSaleRes()
 {
     $startDate = with(new Carbon('first day of january'))->format('Ymd');
     $endDate = with(new Carbon('last day of last month'))->format('Ymd');
     return Processor::getArrayResult($this->getRetailSaleQuery($startDate, $endDate), 'pos');
 }
Exemple #20
0
 protected static function getBase(array $options)
 {
     return Processor::table('HRS_Employee')->where('Code', '=', array_get($options, 'code'))->where('Leave', '=', 0);
 }
Exemple #21
0
 protected function fetchEntitysCountImplement($flag = 'Erp')
 {
     $sql = str_replace(['$mdtTime', '$dependLimitTime'], [$this->mdtTime->format('Y-m-d H:i:s'), $this->dependLimitTime->format('Y-m-d H:i:s')], Processor::getStorageSql("FV/Sync/{$this->type}_count.sql"));
     return array_get(Processor::getArrayResult($sql, $flag), 0)['_count'];
 }
Exemple #22
0
 protected function fetchMembers(array $ids)
 {
     $whereCondition = 'WHERE POS_Member.Code IN(' . sqlInWrap($ids) . ')';
     return Processor::getArrayResult(str_replace(['$whereCondition', '$begin', '$end'], [$whereCondition, 0, self::MAX_LIMIT_ROWS], Processor::getStorageSql('FV/Import/member.sql')));
 }
Exemple #23
0
 public static function fetchCtiRes(array $options)
 {
     $selectStr = 'DataSchema.SchemaCD,DataSchema.SchemaName,CampaignCallList.CampaignCD,CampaignCallList.CampaignName,Campaign.StartDate,Campaign.EndDate,CampaignCallList.AgentCD,CampaignCallList.AgentName,CampaignCallList.CustName,CampaignCallList.ID,CampaignCallList.Tel1,CampaignCallList.Tel2,CampaignCallList.Tel3,CampaignCallList.TelHistory,CampaignCallList.StatusCD,CampaignCallList.StatusName,CampaignCallList.ResultCD,CampaignCallList.ResultName,CampaignCallList.SourceCD,CampaignCallList.FollowupDate,CampaignCallList.DialingTime,CampaignCallList.Payday,CampaignCallList.AssignDate,CampaignCallList.Data01,CampaignCallList.Data02,CampaignCallList.Data03,CampaignCallList.Data04,CampaignCallList.Data05,CampaignCallList.Data06,CampaignCallList.Data08,CampaignCallList.Data09,CampaignCallList.Data11,CampaignCallList.Data12,CampaignCallList.Data15,CampaignCallList.Data16,CampaignCallList.Data17,CampaignCallList.Data20,CampaignCallList.Note,CampaignCallList.modified_by,CampaignCallList.modified_at,CampaignCallList.created_by,CampaignCallList.created_at';
     $q = Processor::table('CampaignCallList WITH(NOLOCK)')->select($selectStr)->leftJoin('Campaign', 'CampaignCallList.CampaignCD', '=', 'Campaign.CampaignCD')->leftJoin('DataSchema', 'Campaign.DefSchemaCD', '=', 'DataSchema.SchemaCD');
     return Processor::getArrayResult(self::condition($q, $options), 'Cti');
 }
 protected function fetch($promoteQ)
 {
     $query = $this->getQuery($this->getDateString($promoteQ, 'start_at', self::DEFAULT_START), $this->getDateString($promoteQ, 'end_at', self::DEFAULT_END), array_get($promoteQ, 'code', self::DEFAULT_CODE));
     return Processor::getArrayResult($query);
 }
Exemple #25
0
 protected function getMarqGroupQuery()
 {
     $startOfMonth = Carbon::now()->modify('first day of this month')->format('Ymd');
     $today = Carbon::now()->format('Ymd');
     return str_replace(['$whereCondition', '$today', '$weekStart', '$weekEnd'], ["CCS_OrderIndex.Status = 1 AND CCS_OrderIndex.KeyInDate BETWEEN {$startOfMonth} AND {$today} AND FAS_Corp.Code IN('CH53000','CH54000','CH54100')", Carbon::now()->format('Ymd'), Carbon::now()->startOfWeek()->format('Ymd'), Carbon::now()->endOfWeek()->format('Ymd')], Processor::getStorageSql('Board/Marq/record_group_corp.sql'));
 }
Exemple #26
0
 protected function getQuery(Carbon $date)
 {
     // Prevent reference link error
     $cloneDate = new Carbon($date->format('Y-m-d H:i:s'));
     $pszCurrentYear = $cloneDate->format('Y');
     $pszCurrentMonth = $cloneDate->format('m');
     $pszCurrentDay = $cloneDate->format('d');
     $pszPastYear = $cloneDate->subYear()->format('Y');
     $pszTailDate = $pszCurrentMonth . $pszCurrentDay;
     $pszPastYearLastDayThisMonth = $cloneDate->modify('last day of this month')->format('d');
     return str_replace(['$pszCurrentYear', '$pszCurrentMonth', '$pszCurrentDay', '$pszPastYearLastDayThisMonth', '$pszPastYear', '$pszTailDate'], [$pszCurrentYear, $pszCurrentMonth, $pszCurrentDay, $pszPastYearLastDayThisMonth, $pszPastYear, $pszTailDate], Processor::getStorageSql('RetailSales.sql'));
 }
 public static function getCategorySerNo($str)
 {
     $q = Processor::table('POS_MemberCategory')->select('TOP 1 SerNo')->where('Code', '=', $str);
     return array_get(Processor::getArrayResult($q), '0.SerNo');
 }
 protected function getQuery(array $ctPrefixCodes)
 {
     return Processor::table('PIS_Goods')->whereIn('Code', $ctPrefixCodes);
 }
Exemple #29
0
 public static function findValid()
 {
     return Processor::getArrayResult(Processor::table('Campaign')->select('*')->where('Campaign.Enabled', '=', 1)->where('Campaign.StartDate', '<=', Carbon::now()->format('Ymd'))->where('Campaign.EndDate', '>=', Carbon::now()->format('Ymd')), 'Cti');
 }
Exemple #30
0
 protected function fetchMembers(array $chunk)
 {
     return Processor::getArrayResult(str_replace('$memberCode', sqlInWrap($chunk), Processor::getStorageSql('CTILayout.sql')));
 }