Dropdown list from different table CakePHP

I am working on a form that will allow the user to edit a certain order. I would like to be able to make a drop down list of Sales Agents that are already stored in a different table.

The foreign key is UserID (Alias for User table is SalesAgent).

This is in my view for the edit_sales_agent action: In my controller I have this:

echo $this->Form->create("Order");
echo $this->Form->input("UserID");

I thought that a drop down list should automatically be created when I do this, but a text area is created instead.

Does anyone know what to do?

Thanks!

via Chebli Mohamed

SQL query to get the total machine running hours between two dates but split into 3 time zones: standard time, peak time and off-peak time

SQL query to get the total machine running hours between two dates but split into 3 time zones: standard time, peak time and off-peak time.

Context
Programming environment: Wonderware ArchestrA
Programming language: ArchestrA Quick Script .Net
Database: Historian - SQL Server (InSQL)
External: A couple of pumps in the mining industry, need to know the pump usage during 3 different power tarif times (peak, standard, off-peak times).
Standard Time: 10:00 to 17:00 AND 20:00 to 22:00
Peak Time: 6:00 to 10:00 AND 17:00 to 20:00
Off-Peak Time: 22:00 to 06:00

I need
Between two dates:

• Total Hours pump ran during Peak Time
• Total Hours pump ran during Off-Peak Time Total Hours pump ran during Standard Time

What I've tried, it works sometimes.

-- this script only gets the total peak time hours
SET NOCOUNT ON  

DECLARE @StartDate DateTime  
DECLARE @EndDate DateTime  
DECLARE @var1 REAL  
DECLARE @Date24 DateTime  

SET @StartDate = '2015/07/01 05:00:00 AM'  
SET @EndDate = GetDate()  
SET NOCOUNT OFF  

-- Get the Date and time where the running hours is running hours minus 24
SET @Date24 = (SELECT TOP 1 [DateTime] 
               FROM  
                   (SELECT 
                        History.TagName, DateTime, Value, StartDateTime  
                    FROM 
                        History  
                    WHERE 
                        History.TagName IN ('me.a0_MainPump.RunningHours.Tagname.FA_PV')  
                        AND Value IS NOT NULL  
                        AND Value = " + Text(me.a0_MainPump.RunningHours.FA_PV - 24, "#") 
                        AND wwVersion = 'Latest'  
                        AND DateTime >= @StartDate) temp)  

-- Get the first portion of the peak time hours  
SET @var1 =  
(  
SELECT  'Count' = Count(DiscreteHistory.Value)/60.0  
FROM DiscreteHistory  
WHERE DiscreteHistory.TagName IN ('KDCE_S04_22PMP01_Machine.FA_RF')  
AND DiscreteHistory.Value = 1  
AND wwRetrievalMode = 'Cyclic'  
AND wwResolution = 60000  
AND DateTime >= @Date24  
AND (CAST(DateTime as time) >= '06:00:00' AND CAST(DateTime as time) < '10:00:00')  
GROUP BY DiscreteHistory.TagName  
)  -- Get the second portion of the peak time hours
SELECT 'Count' = (Count(DiscreteHistory.Value)/60.0 )+@var1  
FROM DiscreteHistory  
WHERE DiscreteHistory.TagName IN ('KDCE_S04_22PMP01_Machine.FA_RF')  
AND DiscreteHistory.Value = 1  
AND wwRetrievalMode = 'Cyclic'  
AND wwResolution = 60000  
AND DateTime >= @Date24  
AND (CAST(DateTime as time) >= '17:00:00' AND CAST(DateTime as time) <= '20:00:00')  

Thank you.

Sample Data

I log the following information into the database:

A unique Tag Name for Run Feedback: KDCE_S04_22PMP01_Machine.FA_RF This is a run feedback which is a "1" or "0" or "null"

A unique Tag Name for Machine running hours: me.a0_MainPump.RunningHours.FA_PV which is an integer value of the amount of pump running hours.

Both tag names gets logged with TagName, Value, DateTime, quality, etc.

I have a table that include the following columns:

| DateTime | TagName | Value | QualityDetail |   

This works:

SET NOCOUNT ON  

DECLARE @StartDate DateTime  
DECLARE @EndDate DateTime  

SET @StartDate = '20150701 05:00:00.000'  
SET @EndDate = '20150731 05:00:00.000'  
SET NOCOUNT OFF  

SELECT 
    DateTime, TagName, Value, Quality  
FROM 
    DiscreteHistory  
WHERE 
    DiscreteHistory.TagName IN ('KDCE_S04_22PMP01_Machine.FA_RF')  
    AND DateTime >= @StartDate AND DateTime <= @EndDate  

It returns this output if I export to csv:

DateTime,TagName,Value,Quality
2015/07/01 05:00:00 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,133
2015/07/01 05:09:46 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/01 05:09:53 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/01 06:44:20 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/01 06:45:54 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/01 07:36:22 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/01 07:36:48 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/01 01:53:44 PM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/01 01:53:44 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/01 02:04:52 PM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/01 02:05:27 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/01 02:07:25 PM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/01 02:09:13 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/01 02:14:54 PM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/02 12:10:48 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/02 05:24:06 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/02 05:24:16 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/02 05:50:52 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/02 05:50:59 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/02 06:00:15 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/02 06:55:18 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/02 06:55:18 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/02 09:46:58 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/02 09:46:58 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/02 01:30:27 PM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/02 01:30:27 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/02 05:38:03 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/02 07:01:56 PM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/03 03:41:09 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/03 09:05:18 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/03 10:42:00 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/03 10:57:31 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/03 04:53:36 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/04 10:08:17 PM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/05 06:43:50 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/05 09:43:08 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/05 01:04:03 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/06 09:37:53 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/06 11:07:15 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/06 11:29:48 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/06 05:02:38 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/07 06:15:33 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/07 06:32:24 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/07 09:05:20 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/07 01:10:09 PM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/07 01:10:16 PM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/07 04:45:12 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/07 08:19:40 PM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/07 09:01:35 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0

via Chebli Mohamed

Sql Order by Student Grade No

I want to order my data,i have following data.

Below is my Data

• 1
• 1+
• 1-
• 2
• 2+
• 2-
• 3
• 3+
• 3-
• 4
• 4+
• 4-

I want to order Like this

• 1+
• 1
• 1-
• 2+
• 2
• 2-
• 3+
• 3
• 3-
• 4+
• 4
• 4-

I tried this

select * from RatingGrade where ScoreCardID=1   order by GradeNo

via Chebli Mohamed

How to GroupBy a part of DateTime using System.Dynamic.LINQ library

I have tables that contain a lot of rows, each record is excellent by whom and on what date was added. I want to group all records, based on AddedBy and AddedOn fields.

The problem is that the dates are in full including the miliseconds and I only want to group by day, month or year.

I hereby noted that at Compilation time I do not know which table it is and Therefore I use with System.Dynamic.LINQ library.

Below I demonstrate how did a grouping by datetime using System.Dynamic.LINQ library:

Dim groupColl= myColl.GroupBy("new(AddedName, AddedOn), it").Select("new(Key.AddedName, Key.AddedOn, Count() AS Count)")

But the problem is that I need to grouping by day, month or year.

In sql server I found how to do it, by day or by month or by year. In Lambda Expression also found the way how to do it, but only by day.

But through System.Dynamic.LINQ not find any way how to do it.

Below I demonstrate how I'm doing this in sql server and Lambda Expression:

Using SQL SERVER:

SELECT AddedBy, DATEADD(DAY, DATEDIFF(DAY, 0, AddedOn), 0) as AddedOn, count(*)
FROM myTable
GROUP BY AddedBy, DATEADD(DAY, DATEDIFF(DAY, 0, AddedOn), 0)

Using Lambda Expression vb.net code (Imports)

Dim groupColl = myCollection.GroupBy(Function(x) New With {Key x.AddedBy, Key .AddedeOn_Day = DbFunctions.TruncateTime(x.AddedOn)}).Select(Function(y) New With {y.Key.AddedBy, y.Key.AddedeOn_Day, y.Count})

I would be grateful to anyone who would help me how to do it using System.Dynamic.LINQ Library

Thanks

via Chebli Mohamed

SQL amount of pages bigger than it should be

I have a DB in SQL Express 2012 and there is one particular table which I am struggling to understand. Said table is has a CI and the following properties:

CREATE TABLE [dbo].[Hourly_Flows](
    [S_ID] [smallint] NOT NULL,
    [FromID] [tinyint] NOT NULL,
    [ToID] [tinyint] NOT NULL,
    [Hour8760] [smallint] NOT NULL,
    [FromTo] [smallint] NULL,
    [ToFrom] [smallint] NULL,
 CONSTRAINT [PK__oHF_Hour__60BEC9D565570293] PRIMARY KEY CLUSTERED 
(
    [S_ID] ASC,
    [FromID] ASC,
    [ToID] ASC,
    [Hour8760] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

I created a new empty DB, with the exact same table and inserted all the records into it, the size of the new and old table is below:

+--------------+--------------+--------------+
|    Table     |     Old      |     New      |
+--------------+--------------+--------------+
| RowCounts    | 109,911,720  | 109,911,720  |
| TotalPages   | 436,323      | 259,137      |
| UsedPages    | 436,270      | 259,098      |
| DataPages    | 434,434      | 258,009      |
| TotalSpaceMB | 3,408        | 2,024        |
| UsedSpaceMB  | 3,408        | 2,024        |
| DataSpaceMB  | 3,394        | 2,015        |
+--------------+--------------+--------------+

As you can see the amount of pages and size is much smaller in the new table. Before inserting the data in the original table I tried truncated the table, dropped and recreated, cleantable and always get the same results.

Not sure what I am missing here, I have the same results if I re-insert the data into the original table. The only other issue could be that the DB is growing over the 10GB size limit of SQL Express DBs, but not sure why this could make a difference.

The table used to have more columns which were deleted (hence we truncated it), we do this often but this is the first time it does not reduce its size.

via Chebli Mohamed

Trying to complete simple SQL query involving a case and a table join

I have an SQL Select query that I am using to return a column of Ids that matched from one table to another and a column of Ids that didn't match. There are a couple other columns that are in the query as well, but they don't matter as much here. There is another column (not currently in the query) in table2 called StatusId. I would like the Ids to only show up in the matched column if the StatusId = 3. Otherwise, I would like them to show up in the Unmatched Column. Sounds super simple, but I can't figure out how to work the WHERE statement in.

Query:

Select  t2.Id as MatchedId,
case when t2.Id is null
then t1.Id end as UnmatchedId
, t2.Number,t1.Date
from Table1 t1 
left join Table2 t2 
on t2.Id = t1.Id;

via Chebli Mohamed

First record in SQL Cursor missing a variable

I am sending a newsletter in SQL server using a cursor. All is working fine except the first email has no html.

Here is the stored procedure:.....

DECLARE
  @html varchar(max)
  SET @html = (SELECT html from NewsLetter where nLID=@nLID)
    DECLARE crsEmailList CURSOR FOR
    SELECT email, ListID from lists where category=@Category AND (DLC < DATEADD(DAY, -1,GETDATE()) OR DLC IS NULL)
  OPEN crsEmailList
  FETCH NEXT FROM crsEmailList INTO @email, @ListID
    while @@FETCH_STATUS = 0 
    BEGIN
    DECLARE @UniqueKey varchar(20),
    @UnSubscribeURL varchar(200),
    @ClickURL varchar(200)
    SET @UnSubscribeURL='<a href=''http://.../userfiles/OHP/UnSubscribe.aspx?listID=' + convert(varchar, @ListID) + '''>Unsubscribe</a>'
    SET @ClickURL='<a href=''http://.../userfiles/OHP/clicked.aspx?Key=' + convert(varchar, @UniqueKey ) + '&URL='
    EXEC [register_system_email_audits] @ListID, @email, @Date, @UniqueKey output
    SET @html = (SELECT html from NewsLetter where nLID=@nLID)
    SET @html = Replace(@html,'[keyvalue]', @UniqueKey)
    SET @html = Replace(@html,'<a href=\''',@ClickURL)
    SET @html = Replace(@html,'[UnSubscribe]', @UnSubscribeURL )
    SET @html = Replace(@html,'[date]', DATENAME(month, getdate()) )
          EXEC msdb.dbo.sp_send_dbmail 
       @profile_Name ='Local Server',
       @recipients= @email ,
       @subject = @Subject,
       @body = @html,
       @body_format='HTML'
    FETCH NEXT FROM crsEmailList INTO @email, @ListID
    END

I have tried moving the line SET @html = (SELECT html from NewsLetter where nLID=@nLID) to different locations but no positive results.

via Chebli Mohamed