//删除CUST_TABLE重复记录
DROP PROCEDURE IF EXISTS proc_tempPro;
CREATE PROCEDURE proc_tempPro()
BEGIN
SELECT count(*) into @count FROM INFORMATION_SCHEMA.statistics  
WHERE table_schema= DATABASE() AND table_name='cust_table' AND  index_name = 'index_cust_table_custID';
if(@count<=0) THEN 
ALTER TABLE cust_table ADD INDEX index_cust_table_custID (custID);
end if;
end;
call proc_tempPro;


DROP TABLE IF EXISTS `tmp_phoneNo`;
create table tmp_phoneNo as select phoneNo from cust_table  group by phoneNo having count(*) >1;
ALTER TABLE tmp_phoneNo ADD INDEX index_tmp_phoneNo_phoneNo (phoneNo);
DROP TABLE IF EXISTS `tmp_custID`;
create table tmp_custID as select max(custID) as custID from cust_table  group by phoneNo having count(*) >1;
ALTER TABLE tmp_custID ADD INDEX index_tmp_custID_custID (custID);


delete from cust_table where custID not in (select custID from tmp_custID) and phoneNo in (select PhoneNo from tmp_phoneNo);

	
//BATCH清除记录

DROP PROCEDURE IF EXISTS proc_tempPro;
CREATE PROCEDURE proc_tempPro()
BEGIN
SELECT max(BatchID) into @batchID FROM pax_batch_record_table;
insert into pax_batch_record_table (batchID,hostname,opuserID,createtime) values (@batchID+1,'sys',1,now());
insert into pax_batch_detail_table (batchID,`ReaderID`, `SeqNo`, `OrderID`, `PaymentID`, `SplitGroupID`, `Amount`, `Tip`, `ResultCode`, `ResultTxt`, `ExtData`, `Timestamp`, `RequestedAmount`, `ExtraBalance`, `RemainingBalance`, `RawResponse`, `RefNum`, `Message`, `HostResponse`, `HostCode`, `CvResponse`, `CardType`, `BogusAccountNum`, `AvsResponse`, `ApprovedAmount`, `AuthCode`, `Status`, `CreateTime`, `HostName`, `CardBin`, `ExtDate`) select @batchID+1,`ReaderID`, `SeqNo`, `OrderID`, `PaymentID`, `SplitGroupID`, `Amount`, `Tip`, `ResultCode`, `ResultTxt`, `ExtData`, `Timestamp`, `RequestedAmount`, `ExtraBalance`, `RemainingBalance`, `RawResponse`, `RefNum`, `Message`, `HostResponse`, `HostCode`, `CvResponse`, `CardType`, `BogusAccountNum`, `AvsResponse`, `ApprovedAmount`, `AuthCode`, `Status`, `CreateTime`, `HostName`, `CardBin`, `ExtDate` from pax_cardreader_record_table where status=1;
delete from pax_cardreader_record_table;
end;
call proc_tempPro();

//复制菜单组
DROP PROCEDURE IF EXISTS proc_tempPro;
CREATE PROCEDURE proc_tempPro(IN fromCategoryID integer,IN toCategoryID integer)
BEGIN
drop table if exists tmp_category_table;
drop table if exists tmp_menu_table;
SELECT max(categoryID) into @count from category_table;
create table tmp_category_table as select * from category_table where CategoryGroupID=fromCategoryID;
create table tmp_menu_table as select * from menu_table where categoryid in (select categoryID from category_table where CategoryGroupID=fromCategoryID);
update tmp_category_table set categoryID=categoryID+@count,categoryGroupID=toCategoryID;
select max(menuID) into @menuCount from menu_table;
update tmp_menu_table set categoryID=categoryID+@count,menuID=menuID+@menuCount;
insert into category_table select * from tmp_category_table;
insert into menu_table select * from tmp_menu_table;
drop table if exists tmp_category_table;
drop table if exists tmp_menu_table;
end;
call proc_tempPro(1,2);

//复制某个分类菜单
DROP PROCEDURE IF EXISTS proc_tempPro;
CREATE PROCEDURE proc_tempPro(IN categoryIDs varchar(1000),IN toCategoryID integer)
BEGIN
drop table if exists tmp_category_table;
drop table if exists tmp_menu_table;
SELECT max(categoryID) into @count from category_table;
create table tmp_category_table as select * from category_table where CategoryID in (categoryIDs);
create table tmp_menu_table as select * from menu_table where categoryid in (select categoryID from category_table where CategoryID in (categoryIDs));
update tmp_category_table set categoryID=categoryID+@count,categoryGroupID=toCategoryID;
select max(menuID) into @menuCount from menu_table;
update tmp_menu_table set categoryID=categoryID+@count,menuID=menuID+@menuCount;
insert into category_table select * from tmp_category_table;
insert into menu_table select * from tmp_menu_table;
drop table if exists tmp_category_table;
drop table if exists tmp_menu_table;
end;

call proc_tempPro(109,2);
call proc_tempPro(104,2);


//复制一个类成为跟随组
DROP PROCEDURE IF EXISTS proc_tempPro;
CREATE PROCEDURE proc_tempPro(IN p_categoryID integer,IN p_followGroupID varchar(1000))
BEGIN
insert into follow_table (followGroupID,ItemNo,L1,L2,Price,isMain,PriceMode,Printer,IsNoTax,IsDiscount,Weight,createTime,followItemType) (select p_followGroupID,ItemNo,L1,L2,Price1,1,PriceMode,Printer,IsNoTax,IsDiscount,Weight,now(),0 from menu_table where categoryid=p_categoryID);
end;

call proc_tempPro(47,'ROL');



//复制一个跟随组
DROP PROCEDURE IF EXISTS proc_tempPro;
CREATE PROCEDURE proc_tempPro(IN p_sGroupID  varchar(1000),IN p_tGroupID varchar(1000))
BEGIN
drop table if exists tmp_follow_table;
SELECT max(followID) into @count from  follow_table;
create table tmp_follow_table as select * from follow_table where followGroupID=p_sGroupID;
update  tmp_follow_table set followID=followID+@count,followGroupID=p_tGroupID;
Insert into follow_table select * from tmp_follow_table;
drop table if exists tmp_follow_table;
end;

call proc_tempPro('201','201A');


//复制一个跟随组到某个分类下
DROP PROCEDURE IF EXISTS proc_tempPro;
CREATE PROCEDURE proc_tempPro(IN p_sGroupID  integer,IN p_tGroupID varchar(1000))
BEGIN
insert into menu_table (`CategoryID`, `ItemNo`, `L1`, `L2`, `Price1`, `Price2`, `Price3`, `Price4`, `Price5`, `Price6`, `Price7`, `Price8`, `Price9`, `Price10`,   
`isMain`, `PriceMode`, `Printer`, `IsNoTax`, `IsDiscount`, `Weight`,   
`CreateTime`,  `LunchDinnerShow`, `StoreNum`, `Commission`, `OnlineShowFlag`,`NewFlag`, `HotFlag`, `HotLevel`)
(select p_sGroupID,itemNo,l1,l2,price,0,0,0,0,0,0,0,0,0, 0,0,printer,0,0,0,  now(),0,-1,0,0,0,0,0   from follow_table where FollowGroupID=p_tGroupID);
end;
call proc_tempPro(53,'201j');
call proc_tempPro(54,'203j');

//复制桌子区域
//先查询出区域ID
select * from table_area_table;
//找出ID，再进行复制
DROP PROCEDURE IF EXISTS proc_tempPro;
CREATE PROCEDURE proc_tempPro(IN p_sTableArea  varchar(1000),IN p_tTableArea varchar(1000))
BEGIN
drop table if exists tmp_table_table;
SELECT max(tableID) into @count from  table_table;
create table tmp_table_table as select * from table_table where areaID=p_sTableArea;
update  tmp_table_table set tableID=tableID+@count,areaID=p_tTableArea;
Insert into table_table select * from tmp_table_table;
drop table if exists tmp_table_table;
end;

call proc_tempPro(18,19);



//清理数据
delete from order_split_detail_table where orderID in (select orderid from order_table where orderTime<='20190129');
delete from order_split_group_table where orderID in (select orderid from order_table where orderTime<='20190129');
delete from order_payment_table where orderID in (select orderid from order_table where orderTime<='20190129');
delete from order_detail_table where orderID in (select orderid from order_table where orderTime<='20190129');
delete from order_table where orderTime<='20190129';


//删除客户重复数据
create table tmp_cust_double as (select PhoneNo from cust_table group by PhoneNo having count(*)>1);
ALTER TABLE tmp_cust_double ADD INDEX index_tmp_cust_double_phoneno (PhoneNo);
create table tmp_min_custid as select min(custID) as custID  from cust_table where PhoneNo in (select PhoneNo from tmp_cust_double) group by phoneNo;
ALTER TABLE tmp_min_custid ADD INDEX index_tmp_min_custid (custID);
delete from cust_table where custID not in ( select custID from tmp_min_custid) and phoneNo in (select phoneNo from tmp_cust_double);
drop table tmp_cust_double;
drop table tmp_min_custid;

//查询付款异常记录
select p.PaymentAmount,o.PaymentAmount,o.orderAmount,o.orderNo,o.orderTime from order_payment_table p left join order_table o on o.orderID=p.orderID where  o.PaymentAmount!=p.PaymentAmount and o.PaymentAmount!= o.orderAmount order by o.orderTime desc,o.orderNo


//EXCEL生成VIP数据脚本
=("insert into vip_card_table (cardNo,cardType,amount,createTime) values ('"&A2&"',0,"&B2&"*100,now());")

//底部打印图片脚本

 	Thank you very much!<br/><div style="width:100%;text-align: center;"><img style="width:60px; height:60px;" src="file:///H:/logo.png"/></div>
 

//备份脚本

@echo off
set backpath=\\192.168.31.173\d$\back
set username=root
set password=honorpos123
set port=33016
set dbname=honorpos
set savealldata=1
set saveallsetting=1


set "$=%temp%\Spring"
>%$% Echo WScript.Echo((new Date()).getTime())
for /f %%a in ('cscript -nologo -e:jscript %$%') do set timestamp=%%a
del /f /q %$%
echo %timestamp%

if not "%saveallsetting%"=="1" goto 1
echo allsetting_%timestamp%.sql backup... 
mysqldump -u%username% -p%password% -P%port% %dbname% category_group_table category_table menu_table spec_group_table spec_op_table spec_table follow_table cust_table default_setting_table config_cmd_table botton_main_table pay_type_table cust_type_table table_area_table table_table common_conf_table setting_menu_table tip_suggestion_table address_conf_table driver_table utop_info_table org_table > allsetting_%timestamp%.sql
copy allsetting_%timestamp%.sql %backpath%\allsetting_%timestamp%.sql


:1
if not "%savealldata%"=="1" goto 2
echo alldata_%timestamp%.sql backup... 
mysqldump -u%username% -p%password% -P%port% %dbname% > alldata_%timestamp%.sql
copy alldata_%timestamp%.sql %backpath%\alldata_%timestamp%.sql

:2

//清除打印队列脚本
net stop spooler
del %systemroot%\system32\spool\printers\*.shd
del %systemroot%\system32\spool\printers\*.spl
net start spooler


//颜色
update default_setting_table set settingValue='#333'  where settingKey='main_category_bgcolor';
update default_setting_table set settingValue='#FFF'  where settingKey='main_category_bordercolor';
update default_setting_table set settingValue='#333'  where settingKey='main_category_fitbgcolor';
update default_setting_table set settingValue='#FFF'  where settingKey='main_category_fitbordercolor';
update default_setting_table set settingValue='#FFAE5D'  where settingKey='main_category_activebgcolor';
update default_setting_table set settingValue='#fff'  where settingKey='main_category_color';
update default_setting_table set settingValue='#000'  where settingKey='main_category_activecolor';
update default_setting_table set settingValue='#FFAE5D'  where settingKey='main_category_cmdbgcolor';
update default_setting_table set settingValue='#000'  where settingKey='main_category_cmdcolor';
update default_setting_table set settingValue='#dfdfdf'  where settingKey='main_menu_bgcolor';
update default_setting_table set settingValue='#fff'  where settingKey='main_menu_bordercolor';
update default_setting_table set settingValue='#dfdfdf'  where settingKey='main_menu_fitbgcolor';
update default_setting_table set settingValue='#fff'  where settingKey='main_menu_fitbordercolor';
update default_setting_table set settingValue='#000'  where settingKey='main_menu_color';
update default_setting_table set settingValue='#dfdfdf'  where settingKey='main_menu_cmdbgcolor';
update default_setting_table set settingValue='#fff'  where settingKey='main_menu_cmdcolor';
update default_setting_table set settingValue='rgba(0,0,0,0.9)'  where settingKey='main_follow_bgcolor';
update default_setting_table set settingValue='#fff'  where settingKey='main_follow_bordercolor';
update default_setting_table set settingValue='rgba(0,0,0,0.9)'  where settingKey='main_follow_fitbgcolor';
update default_setting_table set settingValue='#fff'  where settingKey='main_follow_fitbordercolor';
update default_setting_table set settingValue='#fff'  where settingKey='main_follow_color';