import groovy.sql.Sql
def mysql
//print binding.getVariable("a");
if(binding.getVariables().get('__ds')!=null){
mysql=new Sql(__ds)
println "载入datasource"
}
else{
mysql = Sql.newInstance("jdbc:mysql://10.10.243.182:3306/pop_cis_uat?useUnicode=true&characterEncoding=UTF8", "root","123", "com.mysql.jdbc.Driver")
println "single file dev mode"
}
//clear mysql data only for dev
//mysql.execute("delete from price_count");
def h2 = Sql.newInstance("jdbc:h2:mem:;MODE=MySQL","org.h2.Driver")
//try {
h2.execute("""
DROP TABLE IF EXISTS `price_count`;
CREATE TABLE `price_count` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`create_by` varchar(30) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`higher_price_count` int(11) default 0,
`equaler_price_count` int(11) default 0,
`lower_price_count` int(11) default 0,
`total_count` int(11) default 0,
`category_id` int(11),
PRIMARY KEY (`id`)
)
""")
// } catch(Exception e){}
cal=Calendar.instance
Y=cal.get(Calendar.YEAR)
M = cal.get(Calendar.MONTH) + 1
D = cal.get(Calendar.DATE)
date="$Y-$M-$D 00:00:00"
date="Mon May 28 10:06:11 CST 2012"
println "$Y-$M-$D 00:00:00"
println new Date()
def now = new GregorianCalendar()
def today = new GregorianCalendar(now.get(Calendar.YEAR), now.get(Calendar.MONTH), now.get(Calendar.DAY_OF_MONTH))
date=today.getTime()
println date
mysql.eachRow("select category_l1 as cid,count(category_l1) as count from jd_product where price_spread<0 and match_status=1 and available=1 and enabled=1 group by category_l1"){
r=h2.firstRow("select * from price_count where category_id=?", [it.cid])
if(r==null)
{
h2.execute("insert into price_count (create_by,create_time,lower_price_count,category_id) values (?,?,?,?);",["pricemargintask worker",date,it.count,it.cid])
}
else
{
h2.execute("update price_count set create_by=?,create_time=?,lower_price_count=? where category_id=?",["pricemargintask worker",date,it.count,it.cid])
println "update lower"
}
println it.cid+" "+it.count;
println r
println "========================="
}
println "TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTtt"
mysql.eachRow("select category_l1 as cid,count(category_l1) as count from jd_product where price_spread=0 and match_status=1 and available=1 and enabled=1 group by category_l1"){
r=h2.firstRow("select * from price_count where category_id=?", [it.cid])
if(r==null)
{
h2.execute("insert into price_count (create_by,create_time,equaler_price_count,category_id) values (?,?,?,?);",["pricemargintask worker",date,it.count,it.cid])
}
else
{
h2.execute("update price_count set create_by=?,create_time=?,equaler_price_count=? where category_id=?",["pricemargintask worker",date,it.count,it.cid])
println "update equaler"
}
println it.cid+" "+it.count;
// println r
println "ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZz========================="
}
mysql.eachRow("select category_l1 as cid,count(category_l1) as count from jd_product where price_spread>0 and match_status=1 and available=1 and enabled=1 group by category_l1"){
r=h2.firstRow("select * from price_count where category_id=?", [it.cid])
if(r==null)
{
h2.execute("insert into price_count (create_by,create_time,higher_price_count,category_id) values (?,?,?,?);",["pricemargintask worker",date,it.count,it.cid])
}
else
{
h2.execute("update price_count set create_by=?,create_time=?,higher_price_count=? where category_id=?",["pricemargintask worker",date,it.count,it.cid])
println "update higher"
}
println it.cid+" "+it.count;
// println r
println "ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZz========================="
}
mysql.eachRow("select category_l2 as cid,count(category_l2) as count from jd_product where price_spread<0 and match_status=1 and available=1 and enabled=1 group by category_l2"){
r=h2.firstRow("select * from price_count where category_id=?", [it.cid])
if(r==null)
{
h2.execute("insert into price_count (create_by,create_time,lower_price_count,category_id) values (?,?,?,?);",["pricemargintask worker",date,it.count,it.cid])
}
else
{
h2.execute("update price_count set create_by=?,create_time=?,lower_price_count=? where category_id=?",["pricemargintask worker",date,it.count,it.cid])
println "update lower"
}
println it.cid+" "+it.count;
println r
println "========================="
}
println "TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTtt"
mysql.eachRow("select category_l2 as cid,count(category_l2) as count from jd_product where price_spread=0 and match_status=1 and available=1 and enabled=1 group by category_l2"){
r=h2.firstRow("select * from price_count where category_id=?", [it.cid])
if(r==null)
{
h2.execute("insert into price_count (create_by,create_time,equaler_price_count,category_id) values (?,?,?,?);",["pricemargintask worker",date,it.count,it.cid])
}
else
{
h2.execute("update price_count set create_by=?,create_time=?,equaler_price_count=? where category_id=?",["pricemargintask worker",date,it.count,it.cid])
println "update equaler"
}
println it.cid+" "+it.count;
// println r
println "ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZz========================="
}
mysql.eachRow("select category_l2 as cid,count(category_l2) as count from jd_product where price_spread>0 and match_status=1 and available=1 and enabled=1 group by category_l2"){
r=h2.firstRow("select * from price_count where category_id=?", [it.cid])
if(r==null)
{
h2.execute("insert into price_count (create_by,create_time,higher_price_count,category_id) values (?,?,?,?);",["pricemargintask worker",date,it.count,it.cid])
}
else
{
h2.execute("update price_count set create_by=?,create_time=?,higher_price_count=? where category_id=?",["pricemargintask worker",date,it.count,it.cid])
println "update higher"
}
println it.cid+" "+it.count;
// println r
println "ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZz========================="
}
mysql.eachRow("select count(1) as count from jd_product where price_spread<0 and match_status=1 and available=1 and enabled=1"){
r=h2.firstRow("select * from price_count where category_id=?",[0])
if(r==null)
{
h2.execute("insert into price_count (create_by,create_time,lower_price_count,category_id) values (?,?,?,?);",["pricemargintask worker",date,it.count,0])
}
else
{
h2.execute("update price_count set create_by=?,create_time=?,lower_price_count=? where category_id=?",["pricemargintask worker",date,it.count,0])
println "update lower"
}
println "0"+" "+it.count;
println r
println "========================="
}
println "TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTtt"
mysql.eachRow("select count(1) as count from jd_product where price_spread=0 and match_status=1 and available=1 and enabled=1"){
r=h2.firstRow("select * from price_count where category_id=?", [0])
if(r==null)
{
h2.execute("insert into price_count (create_by,create_time,equaler_price_count,category_id) values (?,?,?,?);",["pricemargintask worker",date,it.count,0])
}
else
{
h2.execute("update price_count set create_by=?,create_time=?,equaler_price_count=? where category_id=?",["pricemargintask worker",date,it.count,0])
println "update equaler"
}
println "0"+" "+it.count;
// println r
println "ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZz========================="
}
mysql.eachRow("select count(1) as count from jd_product where price_spread>0 and match_status=1 and available=1 and enabled=1"){
r=h2.firstRow("select * from price_count where category_id=?", [0])
if(r==null)
{
h2.execute("insert into price_count (create_by,create_time,higher_price_count,category_id) values (?,?,?,?);",["pricemargintask worker",date,it.count,0])
}
else
{
h2.execute("update price_count set create_by=?,create_time=?,higher_price_count=? where category_id=?",["pricemargintask worker",date,it.count,0])
println "update higher"
}
println "0"+" "+it.count;
// println r
println "ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZz========================="
}
h2.execute("update price_count set total_count=higher_price_count+equaler_price_count+lower_price_count")
// h2.execute("insert into price_count (create_by,create_time) values ('maolingzhi','2010-5-24');");
// h2.eachRow("""select * from price_count;""")
// {
// println"=========="
// println it.id
// println it.create_by
//}
println "==============================================================================="
println h2.firstRow("select count(*) as count from price_count").count
println "id\ttotal_count\thigher_price_count\tequaler_price_count\tlower_price_countcategory_idcreate_time\tcreate_by\tcreate_time\tcreate_by"
h2.eachRow("select * from price_count") {
println it.id+"\t"+it.total_count+"\t"+it.higher_price_count+"\t"+it.equaler_price_count+"\t"+it.lower_price_count+"\t"+it.category_id+"\t"+it.create_time+"\t"+it.create_by+"\t"
r=mysql.firstRow("select count(1) as count from price_count where category_id=? and create_time=?", [it.category_id,date]).count
if(r==0)
{
println "execute insert"
mysql.execute("insert into price_count (create_by,create_time,total_count,higher_price_count,equaler_price_count,lower_price_count,category_id) values (?,?,?,?,?,?,?);", [it.create_by,it.create_time,it.total_count,it.higher_price_count,it.equaler_price_count,it.lower_price_count,it.category_id])
}
else
{
println "execute update"
mysql.execute("update price_count set create_by=?,create_time=?,total_count=?,higher_price_count=?,equaler_price_count=?,lower_price_count=? where category_id=? and create_time=?",[it.create_by,it.create_time,it.total_count,it.higher_price_count,it.equaler_price_count,it.lower_price_count,it.category_id,it.create_time])
}
}
println mysql.firstRow("select count(1) as count from price_count").count + "total records"