USE [TNG_KMS]
GO
/****** Object: StoredProcedure [dbo].[SP_S_GetCompletedInjectionReport] Script Date: 18/7/2016 10:52:36 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP PROCEDURE [dbo].[SP_S_GetCompletedInjectionReport]
GO
CREATE PROCEDURE [dbo].[SP_S_GetCompletedInjectionReport]
--(
-- -- Add the parameters for the stored procedure here
@dt_DateFrom datetime ,
@dt_DateTo datetime
--)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET FMTONLY OFF;
-- Insert statements for procedure here
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(name)
FROM (select name from LookupItems where CategoryID=4) AS AppSector
ORDER BY name
SET @DynamicPivotQuery =
'SELECT * FROM (
select
@@ROWCOUNT No, FormRefNo,
ISNULL(TotalSam,0) BigTotalSam,
R.CreatedTime,R.CompletionDate,
Datediff(dd,CONVERT(Date,R.CreatedTime),R.CompletionDate) ''DaysCompleted'',
LI.Name [AppSector]
from
[TNG_KMS].[dbo].[Request] R,
[TNG_KMS].[dbo].[ProfileGroup] PG,
[TNG_KMS].[dbo].[LookupItems] LI
where R.ProfileGroupID=PG.ID
and PG.AppSectorID=LI.ItemIndex
and LI.CategoryID = 4
and R.RequestStatusID=5
and (R.CompletionDate between @dt_DateFrom and @dt_DateTo)
) as s
PIVOT
(
SUM(BigTotalSam) FOR [AppSector] IN (' +@ColumnName+ ')
) AS pvt'
;
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery,
N'@dt_DateFrom DATETIME, @dt_DateTo DATETIME',
@dt_DateFrom,
@dt_DateTo;
END
GO
atoksyahCODEzz
error, issue, RnD, notes for my colleagues ... anything related with my job. A good programmer should be a good and fast reader. This log just to cut the costs of my RnD. Nothing personal :)
Monday, 18 July 2016
SQLSERVER Stored Procedure Dynamic Query Pivot Table DateTime parameter
Wednesday, 13 July 2016
Fixed: The selected stored procedure returns no columns
Hi johnjohn123123,
The problem maybe caused by your stored procedures may contain:
Dynamic queries
Temporary tables
While EF doesn't support importing stored procedures above.
The reason is that to import the procedure EF must execute it. Such operation can be dangerous because it can trigger some changes in the database
I suggest you add following code in the start of your stored procedure
SET FMTONLY OFF
then save the Stored Procedure, Update the model and retry.
Hope it can help you.
Best Regards,
Kevin Shen.
Wednesday, 20 May 2015
Nota ASP.Net 4
- 3 Different ways to add AjaxControlToolkit in Asp .Net Website.
- Method Error 500 Cascadingdropdown Ajax
- Tip/Trick: How to Register User Controls and Custom Controls in Web.config
AUTOCOMPLETE
- http://www.c-sharpcorner.com/UploadFile/0c1bb2/autocomplete-extender-using-Asp-Net/
- ASP.Net AJAX AutoCompleteExtender: Pass Additional Parameter to WebMethod using ContextKey
$get() & $find()
UPDATE PANEL
Invalid PostBack : Cascading dropdown
DYNAMIC GRIDVIEW
- http://stackoverflow.com/questions/11685470/how-to-add-dynamic-n-checkbox-columns-to-gridview-asp-net
- http://highoncoding.com/Articles/219_GridView_All_Rows_in_Edit_Mode.aspx
LOGIN
GRIDVIEW
TIPS
Quartz
MEDIA
Friday, 1 November 2013
Java: @ModelAttribute
Masalah: properties yang tiada element pada .jsp disetkan sebagai null.
Cara mengatasinya adalah menggunakan @ModelAttribute di mana ada 2 jenis yang boleh disemak pada docs spring-framework.
@ModelAttribute pada function akan dipanggil pabila sesuatu model yang serupa pada parameter ditemui.
UPDATE! walaupun tanpa @ModelAttribute("studyLeaveSupervisor") di hadapan StudyLeaveSupervisor dalam parameter @RequestMapping, function pada @ModelAttribute tetap dipanggil.
Jadi kita boleh gunakan function ini untuk dapatkan data daripada DB contohnya. Kemudian data ini akan di-override oleh value pada page.
@ModelAttribute
/**
* Init data daripada DB ke dalam model sebelum di-override oleh data daripada form apabila jumpa parameter yang declared
* sebagai <code>@ModelAttribute("studyLeaveSupervisor") </code>. Ini adalah untuk elakkan field yang tiada dalam form di-set value-nya kepada null.
* ModelAttribute akan dipanggil sebelum mana-mana RequestMapping yang berkaitan.
* @param p2 nama field dalam page
* @param slsStudyleaveRefcode nama field dalam page
* @param slsSupervisorId nama field dalam page
* @return
*/
@ModelAttribute("studyLeaveSupervisor")
public StudyLeaveSupervisor preloadStudyLeaveSupervisor( String p2, String slsStudyleaveRefcode, String slsSupervisorId) {
if( !StringUtil.isEmpty(slsStudyleaveRefcode) && !StringUtil.isEmpty(p2) && !StringUtil.isEmpty(slsSupervisorId) )
{
StudyLeaveSupervisorKey key = new StudyLeaveSupervisorKey();
key.setSlsStartDate( DateUtil.parseYYYYMMDDDate( p2 ) );
key.setSlsStudyleaveRefcode(slsStudyleaveRefcode);
key.setSlsSupervisorId(slsSupervisorId);
System.out.println("\t>> loadStudyLeaveSupervisor = " + key.toString() );
return studyLeaveService.getSupervisorById(key);
}
else{
return new StudyLeaveSupervisor();
}
}
@RequestMapping
@RequestMapping(method = RequestMethod.PUT)
public String update(@ModelAttribute("studyLeaveSupervisor") @Valid StudyLeaveSupervisor studyLeaveSupervisor,
BindingResult bindingResult, Model uiModel, HttpServletRequest request, HttpSession session)
{
MessageObject msj = new MessageObject();
try{
if (studyLeaveSupervisor != null){
studyLeaveSupervisor.setSlsUpdateDate( new Date() );
studyLeaveSupervisor.setSlsUpdateBy( StringUtil.getString( session.getAttribute( SystemConstant.VAR_SESSION_USERID ) ) );
logger.debug("\tupdate: \tsupervisor = " + studyLeaveSupervisor.toString());
studyLeaveService.updateStudyLeaveSupervisor(studyLeaveSupervisor);
}
msj = new MessageObject(false, "Successfully update record.", null, studyLeaveSupervisor.getSlsSupervisorId());
}catch(Exception e){
msj = new MessageObject(true, "Fail to update record.", e.getMessage(), studyLeaveSupervisor.getSlsSupervisorId());
e.printStackTrace();
uiModel.addAttribute( ViewVariables.VAR_MSJ_GENERIC, msj);
return ADD_EDIT_PAGE_URL;
}
uiModel.addAttribute( ViewVariables.VAR_MSJ_GENERIC, msj);
return "redirect:"+LIST_FULLMAPPING;
}
Friday, 18 October 2013
JS: Toggle Visibility
<!-- function toggle_visibility(id, div_id) { var e1 = document.getElementById(id); var e2 = document.getElementById(div_id); if(e1.value=='OTHERS') e2.style.display='block'; else e2.style.display='none'; } //-->
Thursday, 17 October 2013
Tuesday, 3 September 2013
Simple ANT build.xml run in STS
ANT akan memudahkan proses kompil projek ke dalam pakej yang sesuai untuk deployment.
ANT menggunakan satu fail yang lazimnya dinamakan build.xml yang mengandungi arahan salinan fail daripada source-code kepada arkib.
Spring Tool Suite telah mempunyai plug-in yang memudahkan penggunaan ANT.
Right click build.xml |
Pilih target yang sesuai, default telah disetkan kepada war di mana *.war akan dihasilkan. |
Mesej setelah semua proses selesai. |
Boleh rujuk kepada Help dalam STS untuk bantuan menggunakan ANT Support:
Rujukan:
- http://ant.apache.org/
- http://ant.apache.org/external.html; external tool ~ contohnya plugin GUI untuk Eclipse.
Subscribe to:
Posts (Atom)