鞋业管理系统定期执行任务

每小时运行一次
exec P_Shop_GetJinChunXiao

日报每小时一次
declare @date datetime;
set @date=convert(char(10),getdate(),120);
EXEC P_Sale_Report_Duan @date,7;

每天四次，6小时一次，定时备份
exec p_base_backup 'Dsideal_WuQiXieYe_db','d:\数据库\'
EXEC P_Shop_SendTiaoHuoMail 1

 

USE [msdb]
GO

/****** Object:  Job [KillLog]    Script Date: 10/21/2016 12:55:24 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 10/21/2016 12:55:24 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'KillLog',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'无描述。',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Kill]    Script Date: 10/21/2016 12:55:24 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Kill',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'
--截断日志
USE Dsideal_WuQiXieYe_DB;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE Dsideal_WuQiXieYe_DB SET RECOVERY SIMPLE;
GO
---- Shrink the truncated log file to 1 MB.
DECLARE @Log_FileName VARCHAR(256)
SELECT @Log_FileName=name FROM sys.database_files WHERE FILE_ID=2
PRINT @Log_FileName

DBCC SHRINKFILE (@Log_FileName, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE Dsideal_WuQiXieYe_DB SET RECOVERY FULL;
GO',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'EveryHour',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=8,
        @freq_subday_interval=1,
        @freq_relative_interval=0,
        @freq_recurrence_factor=0,
        @active_start_date=20161021,
        @active_end_date=99991231,
        @active_start_time=0,
        @active_end_time=235959,
        @schedule_uid=N'02ce9b74-a2e0-4bb1-9840-b9d78fe6cbae'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO


 

 

 

2016-09-04发现一个BUG，在KuanShi目录下ManagerKuanShi.aspx中，输入奥蝶的简拉AD，无法找到此品牌，只能找到一个adidas,无法完成查询，后来发现是因为jquery的autocomplete有问题，修改如下：

 //自动完成
        /*
        $("#t1").autocomplete('SearchPinPai.ashx', { delay: 100 }, { minChars: 0 }, { width: 310 }, { matchContains: false },
         { autoFill: false }, { max: 10 }); */

        $("#t1").autocomplete('SearchPinPai.ashx',
        {
            minChars: 0,        //至少输入的字符数，default：1；
            width: 220,            //下拉框的宽度，default：input元素的宽度
            max: 100,            //下拉项目的个数，default：10
            scrollHeight: 300,    // 下拉框的高度， Default: 180 
            scroll: true,        //当结果集大于默认高度时，是否使用滚动条，Default: true
            multiple: false,    //是否允许输入多个值. Default: false
          });

 

web.config修改一下

<system.webServer>
    <httpProtocol>
      <customHeaders>
        <add name="X-UA-Compatible" value="IE=EmulateIE8" />
      </customHeaders>
    </httpProtocol>
        <defaultDocument>
            <files>
                <add value="login.aspx" />
            </files>
        </defaultDocument>
  </system.webServer>

 

发现的按类型查看销售情况有BUG，修复如下：

USE [Dsideal_WuQiXieYe_DB]
GO
/****** Object:  StoredProcedure [dbo].[P_Shop_GetSaleInfoByLeiXingDetail]    Script Date: 09/07/2016 08:00:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
功能：按类型统计销售情况
*/
ALTER PROC [dbo].[P_Shop_GetSaleInfoByLeiXingDetail]
(
@StartTime DATETIME ,
@EndTime DATETIME,
@Store_ID INT 
)
AS
BEGIN


IF(@StartTime=@EndTime)
SET @EndTime=DATEADD(dd,1,@StartTime)
ELSE
SET @EndTime=DATEADD( SECOND,-1 ,CONVERT(datetime,DATEADD(dd,1,@EndTime),120))

IF(@Store_ID=-1)
BEGIN    
    SELECT LeiXing_Name+'->'+LeiXingDetail_Name AS LeiXing_Name,
    sum(CASE SaleType WHEN 1 THEN 1 ELSE -1 END)AS 个数,
    ISNULL(SUM(Out_Price),0) AS 售出金额,
    ISNULL(SUM(CASE  WHEN SaleType=1 THEN IN_price else 0-in_price END),0) AS 进货金额,
    ISNULL(SUM(Out_Price),0)-ISNULL(SUM(CASE  WHEN SaleType=1 THEN IN_price else 0-in_price END),0) 
     AS 利润,
    (
    CASE SUM(Out_Price) WHEN 0 THEN '0%' ELSE 
    STR(convert   (numeric   (12,2),1.0*(ISNULL(SUM(Out_Price),0)-ISNULL(SUM(CASE  WHEN SaleType=1 THEN IN_price else 0-in_price END),0) )/SUM(Out_Price)*100) )+'%'    
    END 
    ) AS 利润率
             
     FROM dbo.V_Shop_Sales   WHERE (Happen_Time >= @StartTime AND Happen_Time<@EndTime)
     GROUP BY LeiXing_Name,LeiXingDetail_Name ORDER BY LeiXing_Name,LeiXingDetail_Name
    

END
ELSE
    BEGIN
        SELECT LeiXing_Name+'->'+LeiXingDetail_Name AS LeiXing_Name,
        sum(CASE SaleType WHEN 1 THEN 1 ELSE -1 END)AS 个数,
        ISNULL(SUM(Out_Price),0) AS 售出金额,
    ISNULL(SUM(CASE  WHEN SaleType=1 THEN IN_price else 0-in_price END),0) AS 进货金额,
    ISNULL(SUM(Out_Price),0)-ISNULL(SUM(CASE  WHEN SaleType=1 THEN IN_price else 0-in_price END),0) 
     AS 利润,
    (
    CASE SUM(Out_Price) WHEN 0 THEN '0%' ELSE 
    STR(convert   (numeric   (12,2),1.0*(ISNULL(SUM(Out_Price),0)-ISNULL(SUM(CASE  WHEN SaleType=1 THEN IN_price else 0-in_price END),0) )/SUM(Out_Price)*100) )+'%'    
    END 
    ) AS 利润率
             
     FROM dbo.V_Shop_Sales WHERE (Happen_Time >= @StartTime AND Happen_Time<@EndTime) AND Store_ID=@Store_ID 
     
     GROUP BY LeiXing_Name,LeiXingDetail_Name ORDER BY LeiXing_Name,LeiXingDetail_Name
    END
    
END


USE [Dsideal_WuQiXieYe_DB]
GO
/****** Object:  StoredProcedure [dbo].[P_Shop_GetSaleInfoByLeiXing]    Script Date: 09/07/2016 08:02:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
功能：按类型统计销售情况
*/
ALTER PROC [dbo].[P_Shop_GetSaleInfoByLeiXing]
(
@StartTime DATETIME ,
@EndTime DATETIME,
@Store_ID INT 
)
AS
BEGIN

IF(@StartTime=@EndTime)
SET @EndTime=DATEADD(dd,1,@StartTime)
ELSE
SET @EndTime=DATEADD( SECOND,-1 ,CONVERT(datetime,DATEADD(dd,1,@EndTime),120))


IF(@Store_ID=-1)
BEGIN    
    SELECT LeiXing_Name,
    SUM( CASE SaleType WHEN 1 THEN 1 ELSE -1 END ) AS 个数,
    ISNULL(SUM(Out_Price),0) AS 售出金额,
    ISNULL(SUM(CASE  WHEN SaleType=1 THEN IN_price else 0-in_price END),0) AS 进货金额,
    ISNULL(SUM(Out_Price),0)-ISNULL(SUM(CASE  WHEN SaleType=1 THEN IN_price else 0-in_price END),0) 
     AS 利润,
    (
    CASE SUM(Out_Price) WHEN 0 THEN '0%' ELSE 
    STR(convert   (numeric   (12,2),1.0*(ISNULL(SUM(Out_Price),0)-ISNULL(SUM(CASE  WHEN SaleType=1 THEN IN_price else 0-in_price END),0) )/SUM(Out_Price)*100) )+'%'    
    END 
    ) AS 利润率
             
     FROM dbo.V_Shop_Sales   WHERE (Happen_Time >= @StartTime AND Happen_Time<@EndTime)
     GROUP BY LeiXing_Name ORDER BY LeiXing_Name
    

END
ELSE
    BEGIN
    SELECT LeiXing_Name,
    SUM( CASE SaleType WHEN 1 THEN 1 ELSE -1 END ) AS 个数,
    ISNULL(SUM(Out_Price),0) AS 售出金额,
    ISNULL(SUM(CASE  WHEN SaleType=1 THEN IN_price else 0-in_price END),0) AS 进货金额,
    ISNULL(SUM(Out_Price),0)-ISNULL(SUM(CASE  WHEN SaleType=1 THEN IN_price else 0-in_price END),0) 
     AS 利润,
    (
    CASE SUM(Out_Price) WHEN 0 THEN '0%' ELSE 
    STR(convert   (numeric   (12,2),1.0*(ISNULL(SUM(Out_Price),0)-ISNULL(SUM(CASE  WHEN SaleType=1 THEN IN_price else 0-in_price END),0) )/SUM(Out_Price)*100) )+'%'    
    END 
    ) AS 利润率
             
             
     FROM dbo.V_Shop_Sales WHERE (Happen_Time >= @StartTime AND Happen_Time< @EndTime) AND Store_ID=@Store_ID 
     
     GROUP BY LeiXing_Name ORDER BY LeiXing_Name
    END
    
END

 

USE [Dsideal_WuQiXieYe_DB]
GO
/****** Object:  StoredProcedure [dbo].[P_Shop_GetEnableTuiHuoByStoreID]    Script Date: 11/14/2016 12:56:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
功能：输入店铺ID和发生时间，得到此时间段内销售出去，可以退货的货物
作者：黄海
时间：2008－12－1
*/
ALTER PROC [dbo].[P_Shop_GetEnableTuiHuoByStoreID]
(
@Happen_Time DATETIME,
@Store_ID INT 
)
AS
BEGIN
    
if OBJECT_ID('tempdb..#temp') is not null drop table #temp    

SELECT id,TiaoMa,PinPai_ID,PinPai_Name,KuanShi_ID,KuanShi_Name,ChiMa_ID,ChiMa_Name,
In_Price,Out_Price,SaleType,Happen_Time,Position_ID,B_Normal,Memo,In_Time,LiXiangPrice,
Sale_Person,Employee_Name,Area_ID,Area_Name,Store_ID,Store_Name,Happen_Date  into #temp
FROM dbo.V_Shop_Sales AS T1 
WHERE (Happen_Time BETWEEN @Happen_Time AND 
DATEADD(SS,-1,DATEADD(DAY,1,@Happen_Time)) )
AND Store_ID=@Store_ID 



delete from #temp where ID not in 
(
select max(id) from #temp  group by tiaoma
)

select * from #temp WHERE SaleType=1 ORDER BY PinPai_ID,KuanShi_ID,ChiMa_ID 


END