Remove Item and Give Reward

//
//
//  Author      Goddameit
//  Version     2015/01/12
//  Web     http://goo.gl/0vY9GV
//
//
prontera,155,180,4  script  Clean Item  100,{
    if(getgroupid() < 99) {
        mes "You're not GM";
        close;
    } else if(getusers(1) > 1) {
        mes "You need to kick others before using this.";
        close;
    }
    if(select("[Start]", "[End]") == 2)
        end;
    .@chose = select("[Use Default List]","[Input ItemID]");
    if(.@chose == 1) {
        //Default List
        setarray .@id, 4001, 1101;
    } else
        input .@id[0];

    //Give @CASH on each removing
    .@reward = 10000;

    .@size = getarraysize(.@id);
    query_sql "INSERT INTO `global_reg_value` (`char_id`,`str`,`value`,`type`,`account_id`) SELECT `char_id`, '#CASHPOINTS', '-1', '2', `account_id` FROM `char` WHERE `account_id` NOT IN (SELECT `account_id` FROM `global_reg_value`)";
    query_sql "UPDATE `global_reg_value` SET `global_reg_value`.`char_id` = ( SELECT `char`.`char_id` FROM `char` WHERE `char`.`account_id` = `global_reg_value`.`account_id` ) WHERE `global_reg_value`.`char_id` = 0 AND `global_reg_value`.`str` = '#CASHPOINTS'";
    query_sql "UPDATE `global_reg_value` SET `value` = CAST( (CAST(`value` AS DECIMAL( 10, 0 ))+( `value` = '-1' )) as CHAR(255) ) WHERE `str` = '#CASHPOINTS'";
    query_sql "DROP TABLE IF EXISTS `godd_remove_tmp`";
    query_sql "CREATE TABLE IF NOT EXISTS `godd_remove_tmp` ( `id` int(11) unsigned NOT NULL default '0', `var` int(11) unsigned NOT NULL default '0' ) ENGINE=MyISAM;";
    query_sql "INSERT INTO `godd_remove_tmp` (`id`,`var`) SELECT `account_id`, CAST(`value` AS DECIMAL( 10, 2 )) FROM `global_reg_value` WHERE `str` = '#CASHPOINTS' AND `account_id` NOT IN ( SELECT `account_id` FROM `godd_remove_tmp` )";
    //
    setarray .@table$, "`inventory`", "`cart_inventory`", "`mail`", "`storage`", "`auction`";
    setarray .@compare2$, "`char_id`", "`char_id`", "`dest_id`", "`account_id`", "`seller_id`";
    //
    for(.@i = 0; .@i < .@size; .@i++) {
        if(getiteminfo(.@id[.@i], 2) == -1) {
            mes "Error Item: "+.@id[.@i];
            continue;
        }
        for(.@j = 0; .@j < 5; .@j++) {
            if(.@table$[.@j] == "`auction`") {
                query_sql "INSERT INTO `godd_remove_tmp` (`id`,`var`) SELECT "+.@compare2$[.@j]+", (( COUNT("+.@table$[.@j]+".`nameid`) )*"+.@reward+") FROM "+.@table$[.@j]+" WHERE "+.@table$[.@j]+".`nameid` = "+.@id[.@i]+" GROUP BY "+.@compare2$[.@j]+"";
            } else {
                query_sql "INSERT INTO `godd_remove_tmp` (`id`,`var`) SELECT "+.@compare2$[.@j]+", (( SUM("+.@table$[.@j]+".`amount`) )*"+.@reward+") FROM "+.@table$[.@j]+" WHERE "+.@table$[.@j]+".`nameid` = "+.@id[.@i]+" GROUP BY "+.@compare2$[.@j]+"";
            }

            query_sql "INSERT INTO `godd_remove_tmp` (`id`,`var`) SELECT "+.@compare2$[.@j]+", (( COUNT("+.@table$[.@j]+".`card0`) )*"+.@reward+") FROM "+.@table$[.@j]+" WHERE "+.@table$[.@j]+".`card0` = "+.@id[.@i]+" GROUP BY "+.@compare2$[.@j]+"";
            query_sql "INSERT INTO `godd_remove_tmp` (`id`,`var`) SELECT "+.@compare2$[.@j]+", (( COUNT("+.@table$[.@j]+".`card1`) )*"+.@reward+") FROM "+.@table$[.@j]+" WHERE "+.@table$[.@j]+".`card1` = "+.@id[.@i]+" GROUP BY "+.@compare2$[.@j]+"";
            query_sql "INSERT INTO `godd_remove_tmp` (`id`,`var`) SELECT "+.@compare2$[.@j]+", (( COUNT("+.@table$[.@j]+".`card2`) )*"+.@reward+") FROM "+.@table$[.@j]+" WHERE "+.@table$[.@j]+".`card2` = "+.@id[.@i]+" GROUP BY "+.@compare2$[.@j]+"";
            query_sql "INSERT INTO `godd_remove_tmp` (`id`,`var`) SELECT "+.@compare2$[.@j]+", (( COUNT("+.@table$[.@j]+".`card3`) )*"+.@reward+") FROM "+.@table$[.@j]+" WHERE "+.@table$[.@j]+".`card3` = "+.@id[.@i]+" GROUP BY "+.@compare2$[.@j]+"";

            query_sql "UPDATE `godd_remove_tmp` SET `id` = ( SELECT `char`.`account_id` FROM `char` WHERE `char`.`account_id` = `godd_remove_tmp`.`id` OR `char`.`char_id` = `godd_remove_tmp`.`id` )";

            query_sql "DELETE FROM "+.@table$[.@j]+" WHERE `nameid` = "+.@id[.@i];
            query_sql "UPDATE "+.@table$[.@j]+" SET `card0` = 0 WHERE `card0` = "+.@id[.@i];
            query_sql "UPDATE "+.@table$[.@j]+" SET `card1` = 0 WHERE `card1` = "+.@id[.@i];
            query_sql "UPDATE "+.@table$[.@j]+" SET `card2` = 0 WHERE `card2` = "+.@id[.@i];
            query_sql "UPDATE "+.@table$[.@j]+" SET `card3` = 0 WHERE `card3` = "+.@id[.@i];
        }
        //
        query_sql "DELETE FROM `guild_storage` WHERE `nameid` = "+.@id[.@i];
        query_sql "UPDATE `guild_storage` SET `card0` = 0 WHERE `card0` = "+.@id[.@i];
        query_sql "UPDATE `guild_storage` SET `card1` = 0 WHERE `card1` = "+.@id[.@i];
        query_sql "UPDATE `guild_storage` SET `card2` = 0 WHERE `card2` = "+.@id[.@i];
        query_sql "UPDATE `guild_storage` SET `card3` = 0 WHERE `card3` = "+.@id[.@i];
    }
    query_sql "UPDATE `global_reg_value` SET `char_id` = 0 WHERE `str` = '#CASHPOINTS'";
    query_sql "UPDATE `global_reg_value` SET `value` = CAST( (SELECT SUM(`var`) FROM `godd_remove_tmp` WHERE `godd_remove_tmp`.`id` = `global_reg_value`.`account_id`) AS CHAR(255)) WHERE `str` = '#CASHPOINTS'";
    query_sql "DELETE FROM `global_reg_value` WHERE CAST(`value` AS DECIMAL( 10, 0 )) <= 0 AND `str` = '#CASHPOINTS'";
    query_sql "DROP TABLE IF EXISTS `godd_remove_tmp`";
    mes " ";
    next;
    mes "Done";
    close;
}