public DDPerformanceSummaryViewModel(UserInformation userInformation, WPF.MDI.MdiChild mdiChild, System.Windows.Controls.DataGrid dgDDPerformance)
{
try
{
_userInformation = userInformation;
_mdiChild = mdiChild;
_ddPerformanceBll = new DDPerformanceBll(_userInformation);
DDPerformanceSummaryModelReport = new DDPerformanceSummaryModel();
this.awaitingPartNoAllocationCommand = new DelegateCommand(this.AwaitingPartNoAllocation);
this.samplesSubmittedCommand = new DelegateCommand(this.SamplesSubmitted);
this.documentsReleasedCommand = new DelegateCommand(this.DocumentsReleased);
this.partNosAllottedCommand = new DelegateCommand(this.PartNosAllotted);
DgDDPerformance = dgDDPerformance;
_performanceOption = performanceOption.Dummy;
GetDummyData();
GetDDPerformanceSummary();
HeaderDetails = "Result Lists";
}
catch (Exception ex)
{
throw ex.LogException();
}
}
public void GetDDPerformanceSummary(DDPerformanceSummaryModel ddpfsm)
{
try
{
sbsql = new StringBuilder();
//Cost sheet received Last Month
sbsql.Append("select ISNULL(COUNT(CI_REFERENCE),0) FROM DDCI_INFO ");
sbsql.Append("WHERE CI_REFERENCE NOT LIKE 'O%' AND CI_REFERENCE NOT LIKE 'U*' ");
sbsql.Append("AND CONVERT(nvarchar(6), ENQU_RECD_ON, 112) = Convert(nvarchar(6),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),112) ");
ddpfsm.CSR_LM = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
//Cost Sheet Completed Last Month
sbsql = new StringBuilder();
sbsql.Append("select ISNULL(COUNT(CI_REFERENCE),0) FROM DDCI_INFO ");
sbsql.Append("WHERE CI_REFERENCE NOT LIKE 'O%' AND CI_REFERENCE NOT LIKE 'U*' ");
sbsql.Append("AND CONVERT(nvarchar(6), ENQU_RECD_ON, 112) = Convert(nvarchar(6),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),112) ");
sbsql.Append(" AND PENDING = '0' ");
ddpfsm.CSC_LM = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
//Part No Allotted Last Month
sbsql = new StringBuilder();
sbsql.Append("SELECT ISNULL(COUNT(PART_NO),0) FROM PRD_MAST ");
sbsql.Append("WHERE CONVERT(nvarchar(6), ALLOT_DATE, 112) = Convert(nvarchar(6),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),112) ");
ddpfsm.PNA_LM = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
//Document Released Last Month
sbsql = new StringBuilder();
sbsql.Append("SELECT ISNULL(COUNT(PART_NO),0) FROM PRD_MAST ");
sbsql.Append("WHERE CONVERT(nvarchar(6), DOC_REL_DATE, 112) = Convert(nvarchar(6),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),112) ");
ddpfsm.DR_LM = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
//Samples Submitted Last Month
sbsql = new StringBuilder();
sbsql.Append("SELECT ISNULL(COUNT(PART_NO),0) FROM PRD_MAST ");
sbsql.Append("WHERE CONVERT(nvarchar(6), SAMP_SUBMIT_DATE, 112) = Convert(nvarchar(6),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),112) ");
ddpfsm.SS_LM = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
sbsql = new StringBuilder();
//Cost sheet received This Month
sbsql.Append("select ISNULL(COUNT(CI_REFERENCE),0) FROM DDCI_INFO ");
sbsql.Append("WHERE CI_REFERENCE NOT LIKE 'O%' AND CI_REFERENCE NOT LIKE 'U*' ");
sbsql.Append("AND CONVERT(nvarchar(6), ENQU_RECD_ON, 112) = Convert(nvarchar(6),GETDATE(),112) ");
ddpfsm.CSR_TM = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
//Cost Sheet Completed This Month
sbsql = new StringBuilder();
sbsql.Append("select ISNULL(COUNT(CI_REFERENCE),0) FROM DDCI_INFO ");
sbsql.Append("WHERE CI_REFERENCE NOT LIKE 'O%' AND CI_REFERENCE NOT LIKE 'U*' ");
sbsql.Append("AND CONVERT(nvarchar(6), ENQU_RECD_ON, 112) = Convert(nvarchar(6),GETDATE(),112) ");
sbsql.Append(" AND PENDING = '0' ");
ddpfsm.CSC_TM = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
//Part No Allotted This Month
sbsql = new StringBuilder();
sbsql.Append("SELECT ISNULL(COUNT(PART_NO),0) FROM PRD_MAST ");
sbsql.Append("WHERE CONVERT(nvarchar(6), ALLOT_DATE, 112) = Convert(nvarchar(6),GETDATE(),112) ");
ddpfsm.PNA_TM = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
//Document Released This Month
sbsql = new StringBuilder();
sbsql.Append("SELECT ISNULL(COUNT(PART_NO),0) FROM PRD_MAST ");
sbsql.Append("WHERE CONVERT(nvarchar(6), DOC_REL_DATE, 112) = Convert(nvarchar(6),GETDATE(),112) ");
ddpfsm.DR_TM = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
//Samples Submitted This Month
sbsql = new StringBuilder();
sbsql.Append("SELECT ISNULL(COUNT(PART_NO),0) FROM PRD_MAST ");
sbsql.Append("WHERE CONVERT(nvarchar(6), SAMP_SUBMIT_DATE, 112) = Convert(nvarchar(6),GETDATE(),112) ");
ddpfsm.SS_TM = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
//Cost sheet received this year
sbsql = new StringBuilder();
sbsql.Append("select ISNULL(COUNT(CI_REFERENCE),0) FROM DDCI_INFO ");
sbsql.Append("WHERE CI_REFERENCE NOT LIKE 'O%' AND CI_REFERENCE NOT LIKE 'U*' ");
sbsql.Append("AND CONVERT(nvarchar(6), ENQU_RECD_ON, 112) ");
sbsql.Append("BETWEEN (CASE WHEN MONTH(GETDATE()) < 4 THEN ");
sbsql.Append("RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE()) - 1))) ");
sbsql.Append("ELSE RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE())))) END)+'04' ");
sbsql.Append("AND Convert(nvarchar(6),GETDATE(),112) ");
ddpfsm.CSR_TY = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
//Cost Sheet Completed This year
sbsql = new StringBuilder();
sbsql.Append("select ISNULL(COUNT(CI_REFERENCE),0) FROM DDCI_INFO ");
sbsql.Append("WHERE CI_REFERENCE NOT LIKE 'O%' AND CI_REFERENCE NOT LIKE 'U*' ");
sbsql.Append("AND CONVERT(nvarchar(6), ENQU_RECD_ON, 112) ");
sbsql.Append("BETWEEN (CASE WHEN MONTH(GETDATE()) < 4 THEN ");
sbsql.Append("RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE()) - 1))) ");
sbsql.Append("ELSE RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE())))) END)+'04' ");
sbsql.Append("AND Convert(nvarchar(6),GETDATE(),112) ");
sbsql.Append(" AND PENDING = '0' ");
ddpfsm.CSC_TY = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
//Part No Allotted This Year
sbsql = new StringBuilder();
sbsql.Append("SELECT ISNULL(COUNT(PART_NO),0) FROM PRD_MAST ");
//sbsql.Append("WHERE CONVERT(nvarchar(6), ALLOT_DATE, 112) = Convert(nvarchar(6),GETDATE(),112) ");
sbsql.Append("WHERE CONVERT(nvarchar(6), ALLOT_DATE, 112) ");
sbsql.Append("BETWEEN (CASE WHEN MONTH(GETDATE()) < 4 THEN ");
sbsql.Append("RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE()) - 1))) ");
sbsql.Append("ELSE RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE())))) END)+'04' ");
sbsql.Append("AND Convert(nvarchar(6),GETDATE(),112) ");
ddpfsm.PNA_TY = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
//Document Released This Year
sbsql = new StringBuilder();
sbsql.Append("SELECT ISNULL(COUNT(PART_NO),0) FROM PRD_MAST ");
//sbsql.Append("WHERE CONVERT(nvarchar(6), DOC_REL_DATE, 112) = Convert(nvarchar(6),GETDATE(),112) ");
sbsql.Append("WHERE CONVERT(nvarchar(6), DOC_REL_DATE, 112) ");
sbsql.Append("BETWEEN (CASE WHEN MONTH(GETDATE()) < 4 THEN ");
sbsql.Append("RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE()) - 1))) ");
sbsql.Append("ELSE RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE())))) END)+'04' ");
sbsql.Append("AND Convert(nvarchar(6),GETDATE(),112) ");
ddpfsm.DR_TY = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
//Samples Submitted This Year
sbsql = new StringBuilder();
sbsql.Append("SELECT ISNULL(COUNT(PART_NO),0) FROM PRD_MAST ");
//sbsql.Append("WHERE CONVERT(nvarchar(6), SAMP_SUBMIT_DATE, 112) = Convert(nvarchar(6),GETDATE(),112) ");
sbsql.Append("WHERE CONVERT(nvarchar(6), SAMP_SUBMIT_DATE, 112) ");
sbsql.Append("BETWEEN (CASE WHEN MONTH(GETDATE()) < 4 THEN ");
sbsql.Append("RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE()) - 1))) ");
sbsql.Append("ELSE RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE())))) END)+'04' ");
sbsql.Append("AND Convert(nvarchar(6),GETDATE(),112) ");
ddpfsm.SS_TY = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
//Cost sheet received last year
sbsql = new StringBuilder();
sbsql.Append("select ISNULL(COUNT(CI_REFERENCE),0) FROM DDCI_INFO ");
sbsql.Append("WHERE CI_REFERENCE NOT LIKE 'O%' AND CI_REFERENCE NOT LIKE 'U*' ");
sbsql.Append("AND CONVERT(nvarchar(6), ENQU_RECD_ON, 112) ");
sbsql.Append("BETWEEN (CASE WHEN MONTH(GETDATE()) < 4 THEN ");
sbsql.Append("RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE()) - 2))) ");
sbsql.Append("ELSE RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE()) - 1))) END)+'04' ");
sbsql.Append("AND ");
sbsql.Append("(CASE WHEN MONTH(GETDATE()) < 4 THEN ");
sbsql.Append("RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE()) - 1))) ");
sbsql.Append("ELSE RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE())))) END)+'03' ");
ddpfsm.CSR_LY = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
//Cost Sheet Completed last year
sbsql = new StringBuilder();
sbsql.Append("select ISNULL(COUNT(CI_REFERENCE),0) FROM DDCI_INFO ");
sbsql.Append("WHERE CI_REFERENCE NOT LIKE 'O%' AND CI_REFERENCE NOT LIKE 'U*' ");
sbsql.Append("AND CONVERT(nvarchar(6), ENQU_RECD_ON, 112) ");
sbsql.Append("BETWEEN (CASE WHEN MONTH(GETDATE()) < 4 THEN ");
sbsql.Append("RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE()) - 2))) ");
sbsql.Append("ELSE RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE()) - 1))) END)+'04' ");
sbsql.Append("AND ");
sbsql.Append("(CASE WHEN MONTH(GETDATE()) < 4 THEN ");
sbsql.Append("RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE()) - 1))) ");
sbsql.Append("ELSE RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE())))) END)+'03' ");
sbsql.Append(" AND PENDING = '0' ");
ddpfsm.CSC_LY = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
//Part No Allotted last Year
sbsql = new StringBuilder();
sbsql.Append("SELECT ISNULL(COUNT(PART_NO),0) FROM PRD_MAST ");
//sbsql.Append("WHERE CONVERT(nvarchar(6), ALLOT_DATE, 112) = Convert(nvarchar(6),GETDATE(),112) ");
sbsql.Append("WHERE CONVERT(nvarchar(6), ALLOT_DATE, 112) ");
sbsql.Append("BETWEEN (CASE WHEN MONTH(GETDATE()) < 4 THEN ");
sbsql.Append("RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE()) - 2))) ");
sbsql.Append("ELSE RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE()) - 1))) END)+'04' ");
sbsql.Append("AND ");
sbsql.Append("(CASE WHEN MONTH(GETDATE()) < 4 THEN ");
sbsql.Append("RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE()) - 1))) ");
sbsql.Append("ELSE RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE())))) END)+'03' ");
ddpfsm.PNA_LY = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
//Document Released last Year
sbsql = new StringBuilder();
sbsql.Append("SELECT ISNULL(COUNT(PART_NO),0) FROM PRD_MAST ");
//sbsql.Append("WHERE CONVERT(nvarchar(6), DOC_REL_DATE, 112) = Convert(nvarchar(6),GETDATE(),112) ");
sbsql.Append("WHERE CONVERT(nvarchar(6), DOC_REL_DATE, 112) ");
sbsql.Append("BETWEEN (CASE WHEN MONTH(GETDATE()) < 4 THEN ");
sbsql.Append("RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE()) - 2))) ");
sbsql.Append("ELSE RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE()) - 1))) END)+'04' ");
sbsql.Append("AND ");
sbsql.Append("(CASE WHEN MONTH(GETDATE()) < 4 THEN ");
sbsql.Append("RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE()) - 1))) ");
sbsql.Append("ELSE RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE())))) END)+'03' ");
ddpfsm.DR_LY = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
//Samples Submitted last Year
sbsql = new StringBuilder();
sbsql.Append("SELECT ISNULL(COUNT(PART_NO),0) FROM PRD_MAST ");
//sbsql.Append("WHERE CONVERT(nvarchar(6), SAMP_SUBMIT_DATE, 112) = Convert(nvarchar(6),GETDATE(),112) ");
sbsql.Append("WHERE CONVERT(nvarchar(6), SAMP_SUBMIT_DATE, 112) ");
sbsql.Append("BETWEEN (CASE WHEN MONTH(GETDATE()) < 4 THEN ");
sbsql.Append("RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE()) - 2))) ");
sbsql.Append("ELSE RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE()) - 1))) END)+'04' ");
sbsql.Append("AND ");
sbsql.Append("(CASE WHEN MONTH(GETDATE()) < 4 THEN ");
sbsql.Append("RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE()) - 1))) ");
sbsql.Append("ELSE RTRIM(LTRIM(CONVERT(nvarchar(4),YEAR(GETDATE())))) END)+'03' ");
ddpfsm.SS_LY = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
//Cost Sheet Pending Domestic
sbsql = new StringBuilder();
sbsql.Append("SELECT ISNULL(COUNT(CI_REFERENCE),0) FROM DDCI_INFO WHERE ");
sbsql.Append("CI_REFERENCE NOT LIKE 'O%' AND CI_REFERENCE NOT LIKE 'U%' ");
sbsql.Append(" AND CI_REFERENCE NOT LIKE 'X%' ");
sbsql.Append(" AND PENDING = '1'");
ddpfsm.CSPD = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
//Cost Sheet Pending Export
sbsql = new StringBuilder();
sbsql.Append("SELECT ISNULL(COUNT(CI_REFERENCE),0) FROM DDCI_INFO WHERE ");
sbsql.Append("CI_REFERENCE LIKE 'X%' ");
sbsql.Append(" AND PENDING = '1'");
ddpfsm.CSPE = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
//Part No Allocation Pending
sbsql = new StringBuilder();
sbsql.Append("SELECT ISNULL(COUNT(CI_REFERENCE),0) FROM DDCI_INFO WHERE ");
sbsql.Append("ALLOT_PART_NO = 1");
ddpfsm.PNAP = DB.ExecuteQuery <int>(sbsql.ToString()).FirstOrDefault <int>().ToValueAsString();
}
catch (Exception ex)
{
throw ex.LogException();
}
}
