每小时运行一次
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 TRANSACTIONDECLARE @ReturnCode INTSELECT @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)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @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 OUTPUTIF (@@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=2PRINT @Log_FileNameDBCC SHRINKFILE (@Log_FileName, 1);GO-- Reset the database recovery model.ALTER DATABASE Dsideal_WuQiXieYe_DB SET RECOVERY FULL;GO', @database_name=N'master', @flags=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @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 QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave: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修改一下
发现的按类型查看销售情况有BUG，修复如下：
USE [Dsideal_WuQiXieYe_DB]GO/****** Object: StoredProcedure [dbo].[P_Shop_GetSaleInfoByLeiXingDetail] Script Date: 09/07/2016 08:00:37 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*功能：按类型统计销售情况*/ALTER PROC [dbo].[P_Shop_GetSaleInfoByLeiXingDetail](@StartTime DATETIME ,@EndTime DATETIME,@Store_ID INT )ASBEGINIF(@StartTime=@EndTime)SET @EndTime=DATEADD(dd,1,@StartTime)ELSESET @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 ENDELSE 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 ENDUSE [Dsideal_WuQiXieYe_DB]GO/****** Object: StoredProcedure [dbo].[P_Shop_GetSaleInfoByLeiXing] Script Date: 09/07/2016 08:02:14 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*功能：按类型统计销售情况*/ALTER PROC [dbo].[P_Shop_GetSaleInfoByLeiXing](@StartTime DATETIME ,@EndTime DATETIME,@Store_ID INT )ASBEGINIF(@StartTime=@EndTime)SET @EndTime=DATEADD(dd,1,@StartTime)ELSESET @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 ENDELSE 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 ONGOSET QUOTED_IDENTIFIER ONGO/*功能：输入店铺ID和发生时间，得到此时间段内销售出去，可以退货的货物作者：黄海时间：2008－12－1*/ALTER PROC [dbo].[P_Shop_GetEnableTuiHuoByStoreID](@Happen_Time DATETIME,@Store_ID INT )ASBEGIN 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 #tempFROM 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