Пример #1
0
        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));
        }
Пример #2
0
        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);
        }
Пример #3
0
        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;
            }
        }
Пример #4
0
        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;
            }
        }