public DataTable GetUser(string userCode) { if (HelperMediator.GetInstance().stringHelper.CheckSpecialCharacters(userCode)) { return(null); } H_HDBUsers h_HDBUsers = new H_HDBUsers() { UserCodeHDB = userCode }; return(ServiceConfig.GetInstance().GetOperation().SimpleQuery(h_HDBUsers)); }
public bool CheckRoleCode(string UserCode, string roleCode) { if (HelperMediator.GetInstance().stringHelper.CheckSpecialCharacters(UserCode, roleCode)) { return(false); } H_HDBUser_Role r_HDBUser_Role = new H_HDBUser_Role() { UserCodeHDB = UserCode }; DataTable dataTable = ServiceConfig.GetInstance().GetOperation().SimpleQuery(r_HDBUser_Role); DataRow[] dataRows = dataTable.Select(string.Format("RoleCode = '{0}'", roleCode)); if (dataRows.Length > 0) { return(true); } return(false); }
public DataTable GetChartData(string dateStart, string dateEnd, string createCenterCode, string exType) { string STType = "1"; string realStatisDays = "3"; try { if (HelperMediator.GetInstance().stringHelper.CheckSpecialCharacters(dateStart, dateEnd, createCenterCode, exType)) { return(null); } switch (exType) { case "4": realStatisDays = "1"; break; case "7": realStatisDays = "5"; break; case "8": STType = "8"; realStatisDays = "7"; break; } R_LogisticsExceptionReportDetail rpt_LogisticsExceptionReportDetail0 = new R_LogisticsExceptionReportDetail() { ModifyTimeStart = dateStart, ModifyTimeEnd = dateEnd, CreateCenterCodeIn = createCenterCode, STTypeIn = "2,3", RealStatisDays = realStatisDays }; R_LogisticsExceptionReportDetail rpt_LogisticsExceptionReportDetail1 = new R_LogisticsExceptionReportDetail() { ModifyTimeStart = dateStart, ModifyTimeEnd = dateEnd, CreateCenterCodeIn = createCenterCode, STTypeIn = "4", RealStatisDays = "1" }; R_LogisticsExceptionReportDetail rpt_LogisticsExceptionReportDetail2 = new R_LogisticsExceptionReportDetail() { ModifyTimeStart = dateStart, ModifyTimeEnd = dateEnd, CreateCenterCodeIn = createCenterCode, STTypeIn = "5,6", RealStatisDays = realStatisDays }; R_LogisticsExceptionReportDetail rpt_LogisticsExceptionReportDetail3 = new R_LogisticsExceptionReportDetail() { ModifyTimeStart = dateStart, ModifyTimeEnd = dateEnd, CreateCenterCodeIn = createCenterCode, STType = exType, RealStatisDays = realStatisDays }; StringBuilder sql = new StringBuilder(); sql.AppendLine("DECLARE @SQL NVARCHAR(500); "); sql.AppendLine("DECLARE @ExType INT; "); sql.AppendLine($"SET @ExType = {exType}; "); sql.AppendLine("IF ( "); sql.AppendLine("@ExType = 1 "); sql.AppendLine("OR @ExType = 8 "); sql.AppendLine(") "); sql.AppendLine("BEGIN "); sql.AppendLine("SELECT SUM([T].[Total]) AS [Total] "); sql.AppendLine(",CONVERT(VARCHAR(10), [T].[ModifyTime], 23) AS ModifyTime "); sql.AppendLine($",'{STType}' AS [STType] "); sql.AppendLine("FROM ( "); sql.AppendLine("SELECT COUNT(*) AS [Total] "); sql.AppendLine(",[ModifyTime] "); sql.AppendLine("FROM [dbo].[rpt_LogisticsExceptionReportDetail] T "); sql.AppendLine("WHERE 1 = 1 "); sql.AppendLine("*[T]*{0} "); sql.AppendLine("GROUP BY [ModifyTime] "); sql.AppendLine("UNION "); sql.AppendLine("SELECT COUNT(*) AS [Total] "); sql.AppendLine(",[T].[ModifyTime] "); sql.AppendLine("FROM ( "); sql.AppendLine("SELECT [T].[BillCode] "); sql.AppendLine(",[T].[CreateCenterCode] "); sql.AppendLine(",CONVERT(VARCHAR(100), [T].[ModifyTime], 23) AS [ModifyTime] "); sql.AppendLine("FROM [dbo].[rpt_LogisticsExceptionReportDetail] T "); sql.AppendLine("WHERE 1 = 1 "); sql.AppendLine("*[T]*{1} "); sql.AppendLine(") T "); sql.AppendLine("LEFT JOIN ( "); sql.AppendLine("SELECT [BattleCode] "); sql.AppendLine("FROM [dbo].[G_Battle_Box_T] "); sql.AppendLine("WHERE [DelState] = 0 "); sql.AppendLine(") T1 "); sql.AppendLine("ON [T].[BillCode] = [T1].[BattleCode] "); sql.AppendLine("GROUP BY [T].[ModifyTime] "); sql.AppendLine("UNION "); sql.AppendLine("SELECT COUNT(*) AS [Total] "); sql.AppendLine(",[T].[ModifyTime] "); sql.AppendLine("FROM ( "); sql.AppendLine("SELECT [T].[BillCode] "); sql.AppendLine(",[T].[CreateCenterCode] "); sql.AppendLine(",CONVERT(VARCHAR(100), [T].[ModifyTime], 23) AS [ModifyTime] "); sql.AppendLine("FROM [dbo].[rpt_LogisticsExceptionReportDetail] T "); sql.AppendLine("WHERE 1 = 1 "); sql.AppendLine("*[T]*{2} "); sql.AppendLine(") T "); sql.AppendLine("LEFT JOIN ( "); sql.AppendLine("SELECT [BattleCode] "); sql.AppendLine("FROM [dbo].[G_Battle_Box_T] "); sql.AppendLine("WHERE [DelState] = 0 "); sql.AppendLine(") T1 "); sql.AppendLine("ON [T].[BillCode] = [T1].[BattleCode] "); sql.AppendLine("GROUP BY [T].[ModifyTime] "); sql.AppendLine("UNION "); //sql.AppendLine("SELECT COUNT(*) AS [Total] "); //sql.AppendLine(",GETDATE() AS ModifyTime "); //sql.AppendLine("FROM ( "); //sql.AppendLine("SELECT * "); //sql.AppendLine("FROM RT_RouteExcpStatistics "); //sql.AppendLine("WHERE ISNULL(HKSHRK, 0) + ISNULL(HKRKDB, 0) "); //sql.AppendLine("+ ISNULL(HKDBJJ, 0) + ISNULL(HKJJJL, 0) "); //sql.AppendLine("+ ISNULL(JLFH, 0) > 3 "); //sql.AppendLine("AND IsEnd = 0 "); //sql.AppendLine("AND DelState = 0 "); //sql.AppendLine($"AND CreateCenterCode IN ({createCenterCode}) "); //sql.AppendLine(") T "); sql.AppendLine("SELECT SUM([HKRKJL]) AS [Total] "); sql.AppendLine(",CONVERT(varchar(100), [DayReport], 23) AS [ModifyTime] "); sql.AppendLine("FROM RT_SpecialExcpStatistics "); sql.AppendLine("WHERE 1=1 "); sql.AppendLine($"AND [DayReport] >= N'{dateStart}' "); sql.AppendLine($"AND [DayReport] <= N'{dateEnd}' "); sql.AppendLine($"AND [CreateCenterCode] IN ({createCenterCode}) "); sql.AppendLine("GROUP BY CONVERT(varchar(100), [DayReport], 23) "); sql.AppendLine(") T "); sql.AppendLine("GROUP BY CONVERT(VARCHAR(10), [T].[ModifyTime], 23); "); sql.AppendLine("END; "); sql.AppendLine("ELSE "); sql.AppendLine("IF ( "); sql.AppendLine("@ExType > 1 "); sql.AppendLine("AND @ExType <= 3 "); sql.AppendLine(") "); sql.AppendLine("BEGIN "); sql.AppendLine("SELECT COUNT(*) AS [Total] "); sql.AppendLine(",CONVERT(VARCHAR(100), [T].[ModifyTime], 23) AS [ModifyTime] "); sql.AppendLine(",[T].[STType] "); sql.AppendLine("FROM [dbo].[rpt_LogisticsExceptionReportDetail] T "); sql.AppendLine("WHERE 1 = 1 "); sql.AppendLine("*[T]*{3} "); sql.AppendLine("GROUP BY CONVERT(VARCHAR(100), [T].[ModifyTime], 23) "); sql.AppendLine(",[T].[STType]; "); sql.AppendLine("END; "); sql.AppendLine("ELSE "); sql.AppendLine("IF ( "); sql.AppendLine("@ExType > 3 "); sql.AppendLine("AND @ExType <= 6 "); sql.AppendLine(") "); sql.AppendLine("BEGIN "); sql.AppendLine("SELECT COUNT(*) AS [Total] "); sql.AppendLine(",[T].[ModifyTime] "); sql.AppendLine(",[T].[STType] "); sql.AppendLine("FROM ( "); sql.AppendLine("SELECT [T].[BillCode] "); sql.AppendLine(",[T].[CreateCenterCode] "); sql.AppendLine(",CONVERT(VARCHAR(100), [T].[ModifyTime], 23) AS [ModifyTime] "); sql.AppendLine(",[T].[STType] "); sql.AppendLine("FROM [dbo].[rpt_LogisticsExceptionReportDetail] T "); sql.AppendLine("WHERE 1 = 1 "); sql.AppendLine("*[T]*{3} "); sql.AppendLine(") T "); sql.AppendLine("LEFT JOIN ( "); sql.AppendLine("SELECT [BattleCode] "); sql.AppendLine("FROM [dbo].[G_Battle_Box_T] "); sql.AppendLine("WHERE [DelState] = 0 "); sql.AppendLine(") T1 "); sql.AppendLine("ON [T].[BillCode] = [T1].[BattleCode] "); sql.AppendLine("GROUP BY [T].[ModifyTime] "); sql.AppendLine(",[T].[STType]; "); sql.AppendLine("END; "); sql.AppendLine("ELSE "); sql.AppendLine("IF @ExType = 7 "); sql.AppendLine("BEGIN "); sql.AppendLine("SELECT SUM([HKRKJL]) AS [Total] "); sql.AppendLine(",CONVERT(varchar(100), [DayReport], 23) AS [ModifyTime] "); sql.AppendLine(",'7' AS [STType] "); sql.AppendLine("FROM RT_SpecialExcpStatistics "); sql.AppendLine("WHERE 1=1 "); sql.AppendLine($"AND [DayReport] >= N'{dateStart}' "); sql.AppendLine($"AND [DayReport] <= N'{dateEnd}' "); sql.AppendLine($"AND [CreateCenterCode] IN ({createCenterCode}) "); sql.AppendLine("GROUP BY CONVERT(varchar(100), [DayReport], 23) "); sql.AppendLine("END; "); DataTable serDt = ServiceConfig.GetInstance().GetOperation().CusQuery( sql.ToString() , out string checkSql , rpt_LogisticsExceptionReportDetail0 , rpt_LogisticsExceptionReportDetail1 , rpt_LogisticsExceptionReportDetail2 , rpt_LogisticsExceptionReportDetail3); Console.WriteLine(checkSql); return(serDt); } catch (Exception ex) { throw ex; } }
public DataTable GetTotal(string dateStart, string dateEnd, string realStatisDays, string createCenterCode) { try { if (HelperMediator.GetInstance().stringHelper.CheckSpecialCharacters(dateStart, dateEnd)) { return(null); } R_LogisticsExceptionReportDetail rpt_LogisticsExceptionReportDetail1 = new R_LogisticsExceptionReportDetail() { ModifyTimeStart = dateStart, ModifyTimeEnd = dateEnd, CreateCenterCodeIn = createCenterCode, STTypeIn = "5,6", DelState = "0", RealStatisDays = realStatisDays }; R_LogisticsExceptionReportDetail rpt_LogisticsExceptionReportDetail2 = new R_LogisticsExceptionReportDetail() { ModifyTimeStart = dateStart, ModifyTimeEnd = dateEnd, CreateCenterCodeIn = createCenterCode, STTypeIn = "4", DelState = "0", RealStatisDays = "1" }; R_LogisticsExceptionReportDetail rpt_LogisticsExceptionReportDetail3 = new R_LogisticsExceptionReportDetail() { ModifyTimeStart = dateStart, ModifyTimeEnd = dateEnd, CreateCenterCodeIn = createCenterCode, STTypeIn = "2,3", DelState = "0", RealStatisDays = realStatisDays }; StringBuilder sql = new StringBuilder(); sql.AppendLine("SELECT * "); sql.AppendLine("FROM ( "); sql.AppendLine("SELECT COUNT(*) AS Total "); sql.AppendLine(",[T].[STType] "); sql.AppendLine("FROM ( "); sql.AppendLine("SELECT * "); sql.AppendLine("FROM [dbo].[rpt_LogisticsExceptionReportDetail] T "); sql.AppendLine("WHERE 1 = 1 "); sql.AppendLine("*[T]*{0} "); sql.AppendLine(") T "); sql.AppendLine("LEFT JOIN ( "); sql.AppendLine("SELECT [BattleCode] "); sql.AppendLine("FROM [dbo].[G_Battle_Box_T] "); sql.AppendLine("WHERE [DelState] = 0 "); sql.AppendLine(") T1 "); sql.AppendLine("ON [T].[BillCode] = [T1].[BattleCode] "); sql.AppendLine("GROUP BY [T].[STType] "); sql.AppendLine("UNION "); sql.AppendLine("SELECT COUNT(*) AS Total "); sql.AppendLine(",'4' AS [STType] "); sql.AppendLine("FROM ( "); sql.AppendLine("SELECT * "); sql.AppendLine("FROM [dbo].[rpt_LogisticsExceptionReportDetail] T "); sql.AppendLine("WHERE 1 = 1 "); sql.AppendLine("*[T]*{1} "); sql.AppendLine(") T "); sql.AppendLine("LEFT JOIN ( "); sql.AppendLine("SELECT [BattleCode] "); sql.AppendLine("FROM [dbo].[G_Battle_Box_T] "); sql.AppendLine("WHERE [DelState] = 0 "); sql.AppendLine(") T1 "); sql.AppendLine("ON [T].[BillCode] = [T1].[BattleCode] "); sql.AppendLine("UNION "); sql.AppendLine("SELECT COUNT(*) AS Total "); sql.AppendLine(",[T].[STType] "); sql.AppendLine("FROM ( "); sql.AppendLine("SELECT * "); sql.AppendLine("FROM [dbo].[rpt_LogisticsExceptionReportDetail] T "); sql.AppendLine("WHERE 1 = 1 "); sql.AppendLine("*[T]*{2} "); sql.AppendLine(") T "); sql.AppendLine("GROUP BY [T].[STType] "); sql.AppendLine("UNION "); sql.AppendLine("SELECT SUM([HKRKJL]) AS [Total] "); sql.AppendLine(",'7' AS [STType] "); sql.AppendLine("FROM RT_SpecialExcpStatistics "); sql.AppendLine("WHERE 1=1 "); sql.AppendLine($"AND [DayReport] >= N'{dateStart}' "); sql.AppendLine($"AND [DayReport] <= N'{dateEnd}' "); sql.AppendLine("GROUP BY CONVERT(varchar(100), [DayReport], 111) "); sql.AppendLine(") T "); sql.AppendLine("ORDER BY T.[STType]; "); DataTable serDt = ServiceConfig.GetInstance().GetOperation().CusQuery( sql.ToString() , out string checkSql , rpt_LogisticsExceptionReportDetail1 , rpt_LogisticsExceptionReportDetail2 , rpt_LogisticsExceptionReportDetail3); Console.WriteLine(checkSql); return(serDt); } catch (System.Data.SqlClient.SqlException ex) { throw ex; } }