Example #1
0
 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));
 }
Example #2
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));
 }
Example #3
0
 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]);
 }
Example #4
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);
         }
     });
 }
Example #5
0
 /**
  * getNotYetModifyedTodayQuery
  * 
  * @return Illuminate\Database\Query\Builder
  */
 protected function getNotYetModifyedQuery()
 {
     return Processor::table('CCS_OrderDivIndex')->leftJoin('CCS_OrderIndex', 'CCS_OrderIndex.SerNo', '=', 'CCS_OrderDivIndex.IndexSerNo')->leftJoin('FAS_Corp', 'CCS_OrderIndex.DeptSerNo', '=', 'FAS_Corp.SerNo')->select('CCS_OrderIndex.SerNo, CCS_OrderIndex.OrderNo', 'CCS_OrderDivIndex.No')->where('CCS_OrderIndex.OrderNo', 'NOT LIKE', 'CT%')->where($this->corpConditionCallback());
 }
Example #6
0
 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');
 }
Example #7
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');
 }
Example #8
0
 protected function genFetchMemberCodesQ($empCodes)
 {
     return Processor::table('Customer_lubri')->whereIn('emp_id', $empCodes);
 }
Example #9
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');
 }
Example #10
0
 protected static function getBase(array $options)
 {
     return Processor::table('HRS_Employee')->where('Code', '=', array_get($options, 'code'))->where('Leave', '=', 0);
 }
Example #11
0
 protected function getQuery(array $ctPrefixCodes)
 {
     return Processor::table('PIS_Goods')->whereIn('Code', $ctPrefixCodes);
 }
Example #12
0
 public function getQuery(array $codes)
 {
     return Processor::table('PIS_Goods')->whereIn('Code', $codes)->where('Code', 'NOT LIKE', 'CT%');
 }
Example #13
0
 protected function getFindLatestCCodeQuery()
 {
     return Processor::table('PIS_Goods')->select('TOP 1 Code')->where('Code', 'LIKE', 'C%')->where('Code', 'NOT LIKE', 'CT%')->where('Code', '<>', 'C1470')->orderBy('Code', 'DESC');
 }
Example #14
0
 protected function getQuery()
 {
     return Processor::table('INFORMATION_SCHEMA.COLUMNS')->select('INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, INFORMATION_SCHEMA.COLUMNS.TABLE_NAME')->leftJoin('information_schema.tables', 'information_schema.tables.TABLE_NAME', '=', 'INFORMATION_SCHEMA.COLUMNS.TABLE_NAME')->where('INFORMATION_SCHEMA.COLUMNS.ordinal_position', '<=', 20)->where('INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME', 'NOT LIKE', '%time%')->whereNotIn('INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME', ['uid', 'Key', 'Open'])->where('INFORMATION_SCHEMA.tables.TABLE_TYPE', '=', 'BASE TABLE')->where('INFORMATION_SCHEMA.tables.TABLE_NAME', 'NOT LIKE', '%BAK_%')->where('INFORMATION_SCHEMA.tables.TABLE_NAME', 'NOT LIKE', '%_BAK%')->where('INFORMATION_SCHEMA.tables.TABLE_NAME', 'NOT LIKE', 'Z_%')->where('INFORMATION_SCHEMA.tables.TABLE_NAME', 'NOT LIKE', 'SYS%')->orderBy('INFORMATION_SCHEMA.COLUMNS.TABLE_NAME');
 }
Example #15
0
 protected function getQb(User $user)
 {
     return Processor::table('Customer_lubri')->where('emp_id', strval($user->code))->where('distflags_7', 'Y')->where('cust_status', '1')->select(implode(',', $this->getColumns()));
 }