{
    "Ni": {
        "add.clearSMS":{
            "command":"
                update l_sms set DelFlag = 0 ,sign = '' where DelFlag = 2;

                INSERT INTO l_smshis (ID, Mobile, Content, TypeName, sign, rest, createTime, DelFlag, finishTime, ret)
                select ID, Mobile, Content, TypeName, sign, rest, createTime, DelFlag, now(),1 from l_sms where rest = 0;

                delete from l_sms where rest = 0;
            ",
            "params":{},
            "template":"w_add"
        },
        "add.getSMSData":{
            "command":"
                set @ID = replace(uuid(),'-','');
                update l_sms a,(select ID from l_sms where DelFlag = 0 and rest>0 limit ?count) oa
                set a.DelFlag = 2,a.sign = @ID,a.rest = a.rest-1
                where a.ID = oa.ID;
                select *,@ID as num from l_sms where sign = @ID;

                select @ID as 'num';
            ",
            "params":{"count":{"type":"Int"}},
            "template":"w_add"
        },
        "add.finishSMSData":{
            "command":"
                INSERT INTO l_smshis (ID, Mobile, Content, TypeName, sign, rest, createTime, DelFlag, finishTime, ret)
                select ID, Mobile, Content, TypeName, sign, rest, createTime, DelFlag, now(),?ret from l_sms where ID = ?ID;
                delete from l_sms where ID = ?ID;
            ",
            "params":{"ID":{"type":"Int"},"ret":{"type":"Int"}},
            "template":"w_add"
        },
        "add.failSMSData":{
            "command":"
                update l_sms set DelFlag = 0,sign = '' where ID = ?ID and DelFlag =2;
                INSERT INTO l_smshis (ID, Mobile, Content, TypeName, sign, rest, createTime, DelFlag, finishTime, ret)
                select ID, Mobile, Content, TypeName, sign, rest, createTime, DelFlag, now(),?ret from l_sms where ID = ?ID and rest <= 0;
                delete from l_sms where ID = ?ID and rest <= 0;
            ",
            "params":{"ID":{"type":"Int"},"ret":{"type":"Int"}},
            "template":"w_add"
        },
        "add.sendSMS":{
          "command": "sendSMS", 
          "params": {
            "arg0": { "type": "VarChar" },
            "arg1": { "type": "VarChar" },
            "arg2": { "type": "VarChar" },
            "arg3": { "type": "Array" },
            "arg4": { "type": "VarChar" },
            "arg5": { "type": "VarChar" },
            "arg6": { "type": "VarChar" },
            "arg7": { "type": "Int" },
            "arg8": { "type": "Int" }
          }, "template": "smstemp"
        },
        "add.autoreceipt":{
            "command":"
                INSERT INTO add_announcereceipt (AnnounceID, UserID, Files, Content, ProjectID, autoReceipt, creater, createTime, delFlag)
                select a.ID,ifnull(c.ID,''),'',ifnull(c.receiptTemp,?Content),a.ProjectID,1,ifnull(ifnull(c.Name,c.Nick),a.Receiver),now(),4
                 from add_announce a 
                left join add_code b
                on a.Code = b.code
                left join add_user c
                on b.OpenID = c.OpenID
                where a.hasReceipt = 0 and date_add(a.createTime, INTERVAL 12 HOUR)<= now() and a.delFlag = 0 and (c.DelFlag is null or c.DelFlag = 0);
                
                INSERT INTO add_send (AnnounceID, ReceiptID, ProjectID, Code, Mobile, Files, Content, rest, num, createTime, nextTime, delFlag)
                select b.AnnounceID,b.ID,b.ProjectID,a.Code,a.Mobile,'',b.Content,10,'',now(),now(),0
                from add_announce a inner join add_announcereceipt b on b.DelFlag = 4 and a.ID = b.AnnounceID;

                update add_announce a,add_announcereceipt b
                set a.hasReceipt = 1,a.lastUpdate=now(),b.DelFlag = 0
                where a.ID = b.AnnounceID and b.DelFlag = 4;
                select row_count() as count;
            ",
            "params":{"Content":{"type":"VarChar"}},
            "template":"w_add"
        },

        "Module.admin.countProject":{
            "command":"
                select count(ID) into @AnnounceIncrease
                from add_announce where ProjectID = ?ID and DelFlag in (0,2,3,4) and createTime between date(date_add(now(),interval -1 day)) and date(now());

                select count(ID) into @AnnounceCount
                from add_announce where ProjectID = ?ID and DelFlag in (0,2,3,4) and createTime < date(now());

                select count(ID) into @AnnounceReceiptCount
                from add_announce where ProjectID = ?ID and hasReceipt = 1 and DelFlag in (0,2,3,4) and createTime < date(now());

                select count(ID) into @ReceiptIncrease
                from add_announcereceipt where ProjectID = ?ID and DelFlag in (0,2,3,4) and createTime between date(date_add(now(),interval -1 day)) and date(now());
                select count(ID) into @ReceiptCount
                from add_announcereceipt where ProjectID = ?ID and DelFlag in (0,2,3,4) and createTime < date(now());

                select count(ID) into @AutoReceiptIncrease
                from add_announcereceipt where ProjectID = ?ID and autoReceipt = 1 and DelFlag in (0,2,3,4) and createTime between date(date_add(now(),interval -1 day)) and date(now());
                select count(ID) into @AutoReceiptCount
                from add_announcereceipt where ProjectID = ?ID and autoReceipt = 1 and DelFlag in (0,2,3,4) and createTime < date(now());

                select count(distinct AnnounceID) into @ManReceiptCount
                from add_announcereceipt where ProjectID = ?ID and autoReceipt = 0 and DelFlag in (0,2,3,4) and createTime < date(now());
                
                select count(ID) into @AnnounceYearCount
                from add_announce where ProjectID = ?ID and DelFlag in (0,2,3,4) and createTime <= date_sub(date(now()),interval 1 year);

                select count(ID) into @AnnounceMonthCount
                from add_announce where ProjectID = ?ID and DelFlag in (0,2,3,4) and createTime <= date_sub(date(now()),interval 1 month);

                select @AnnounceIncrease as AnnounceIncrease,@AnnounceCount as AnnounceCount,@ReceiptIncrease as ReceiptIncrease,@ReceiptCount as ReceiptCount,@AutoReceiptIncrease as AutoReceiptIncrease,@AutoReceiptCount as AutoReceiptCount,@AnnounceReceiptCount as AnnounceReceiptCount,@AnnounceYearCount as AnnounceYearCount,@AnnounceMonthCount as AnnounceMonthCount,@ManReceiptCount as ManReceiptCount;
            ",
            "params":{"ID":{"type":"VarChar"}},
            "template":"r_add"
        },

        "Module.admin.autoCaseIncrease":{
            "command":"
                update add_case set virtualcount = ifnull(virtualcount,0)+ifnull(0,increase)
                where (endTime is null or now()<endTime) and increase>0;
                select row_count() as count;
            ",
            "params":{},
            "template":"w_add"
        },
    }
}