md_YOS/src/custom/restcontroller/webmanage/sale/period/period.java

package restcontroller.webmanage.sale.period;

import beans.data.BatchDeleteErr;
import beans.datacontrllog.DataContrlLog;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING;
import common.annotation.CACHEING_CLEAN;
import common.data.*;
import org.apache.commons.lang.StringUtils;
import restcontroller.R;

import java.time.LocalDate;
import java.time.YearMonth;
import java.util.ArrayList;
import java.util.Calendar;

@API(title = "会计期间")
public class period extends Controller {
    public period(JSONObject content) throws YosException {
        super(content);
    }



    @API(title = "新增会计期间", apiversion = R.ID2025072314205903.v1.class)
    @CACHEING_CLEAN(apiClass = {period.class})
    public String insertormodify_period() throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        // 表名

        String tableName = "st_period";
        Long st_periodid = content.getLong("st_periodid");
        Long month = content.getLongValue("month");
        long year= content.getLongValue("year");
        String enddate = content.getStringValue("enddate");
        String begdate = content.getStringValue("begdate");

        if (st_periodid <= 0) {

            st_periodid = createTableID(tableName);
            InsertSQL insertSQL = SQLFactory.createInsertSQL(this, tableName);
            insertSQL.setUniqueid(st_periodid);
            insertSQL.setSiteid(siteid);
            insertSQL.setValue("month", month);
            insertSQL.setValue("year", year);
            insertSQL.setValue("enddate", StringUtils.isBlank(enddate) ?"null":enddate);
            insertSQL.setValue("begdate", StringUtils.isBlank(begdate) ?"null":begdate);
            sqlList.add(insertSQL.getSQL());

            sqlList.add(DataContrlLog.createLog(this, "st_period", st_periodid, "新增", "会计期间新增成功").getSQL());
        } else {
            Rows rows = dbConnect.runSqlQuery(
                    "SELECT isclose from st_period WHERE st_periodid = "
                            + st_periodid);
            if (rows.isNotEmpty()) {
                if (!rows.get(0).getBoolean("isclose")) {
                    UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, tableName);
                    updateSQL.setUniqueid(st_periodid);
                    updateSQL.setSiteid(siteid);
                    updateSQL.setValue("month", month);
                    updateSQL.setValue("year", year);
                    updateSQL.setValue("enddate", StringUtils.isBlank(enddate) ?"null":enddate);
                    updateSQL.setValue("begdate", StringUtils.isBlank(begdate) ?"null":begdate);
                    sqlList.add(updateSQL.getSQL());

                    sqlList.add(DataContrlLog.createLog(this, "st_period", st_periodid, "更新", "会计期间更新成功").getSQL());
                } else {
                    return getErrReturnObject().setErrMsg("已结账的会计期间无法编辑").toString();
                }

            } else {
                return getErrReturnObject().setErrMsg("该会计期间不存在").toString();
            }

        }
        dbConnect.runSqlUpdate(sqlList);
        content.put("st_periodid", st_periodid);

        return queryperiodMain();
    }



    @API(title = "会计期间详情", apiversion = R.ID2025072314211003.v1.class)
    @CACHEING
    public String queryperiodMain() throws YosException {
        Long st_periodid = content.getLong("st_periodid");
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "st_period","*");
        querySQL.setTableAlias("t1");
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere("t1.st_periodid", st_periodid);
        Rows rows = querySQL.query();
        Row row = rows.isNotEmpty() ? rows.get(0) : new Row();
        return getSucReturnObject().setData(row).toString();
    }


    @API(title = "查询会计期间列表", apiversion = R.ID2025072314211703.v1.class)
    @CACHEING
    public String queryperiodList() throws YosException {
        StringBuffer where = new StringBuffer(" 1=1 ");

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "st_period","*");
        querySQL.setTableAlias("t1");
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere(where);
        if (pageSorting.equals("''")) {
            pageSorting = "concat(t1.year,'-', LPAD(t1.month, 2, '0')) desc";
        }
        querySQL.setOrderBy(pageSorting);
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();

        return getSucReturnObject().setData(rows).toString();
    }



    @API(title = "删除", apiversion = R.ID2025072314212603.v1.class)
    @CACHEING_CLEAN(apiClass = {period.class})
    public String delete() throws YosException {

        JSONArray st_periodids = content.getJSONArray("st_periodids");
        BatchDeleteErr batchDeleteErr = BatchDeleteErr.create(this, st_periodids.size());
        for (Object o : st_periodids) {
            long st_periodid = Long.parseLong(o.toString());
            Rows RowsStatus = dbConnect.runSqlQuery("select st_periodid,isclose from st_period where siteid='"
                    + siteid + "' and st_periodid='" + st_periodid + "'");

            if (RowsStatus.isNotEmpty()) {
                if (RowsStatus.get(0).getBoolean("isclose")) {
                    batchDeleteErr.addErr(st_periodid, "已结账的会计期间无法删除");
                    continue;
                }
            }

            dbConnect.runSqlUpdate(
                    "delete from st_period where siteid='" + siteid + "' and st_periodid=" + st_periodid);
        }
        return batchDeleteErr.getReturnObject().toString();

    }

    @API(title = "账户结账", apiversion = R.ID2025091310230303.v1.class)
    @CACHEING_CLEAN( apiClass = {period.class})
    public String cashbeginbalclose() throws YosException {
        Long st_periodid = content.getLong("st_periodid");
        boolean isclose = content.getBoolean("isclose");
        Rows rows = dbConnect.runSqlQuery("select * from st_period where siteid='"+siteid+"' and st_periodid="+st_periodid);
        if(rows.isEmpty()){
            return getErrReturnObject().setErrMsg("会计期间不存在").toString();
        }
        Rows accountrows = dbConnect.runSqlQuery("select sa_accountclassid from sa_accountclass where siteid='"+siteid+"' and accountnoa='01'");
        if(accountrows.isEmpty()){
            return getErrReturnObject().setErrMsg("01账户不存在").toString();
        }
        ArrayList<String> sqlList = new ArrayList<>();
        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "st_period");
        updateSQL.setUniqueid(st_periodid);
        updateSQL.setSiteid(siteid);
        updateSQL.setValue("isclose",isclose);
        updateSQL.setValue("closeby", isclose?username:"null");
        if(isclose){
            updateSQL.setDateValue("closedate");
        }else{
            updateSQL.setValue("closedate","null");
        }

        sqlList.add(updateSQL.getSQL());

        LocalDate today = LocalDate.of(rows.get(0).getInteger("year"), rows.get(0).getInteger("month"), 1);
        // 获取下一个月的第一天
        LocalDate nextMonthFirstDay = today.with(today.plusMonths(1)).withDayOfMonth(1);
        if (isclose) {

            SQLFactory sqlFactory = new SQLFactory(this, "获取暂存账户数据");
            sqlFactory.addParameter_SQL("begindate", nextMonthFirstDay.getYear() +"-12-31");
            sqlFactory.addParameter_SQL("enddate", nextMonthFirstDay.getYear()+"-12-31");
            Rows cashbeginbalRows =dbConnect.runSqlQuery(sqlFactory);

            if(cashbeginbalRows.isNotEmpty()){
                for (Row row :cashbeginbalRows){
                    InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_cashbeginbal");
                    insertSQL.setUniqueid(createTableID("sa_cashbeginbal"));
                    insertSQL.setSiteid(siteid);
                    insertSQL.setValue("month", nextMonthFirstDay.getMonthValue());
                    insertSQL.setValue("year", nextMonthFirstDay.getYear() );
                    insertSQL.setValue("sys_enterpriseid",  row.getLong("sys_enterpriseid"));
                    insertSQL.setValue("balance", row.getBigDecimal("bqjy"));
                    insertSQL.setValue("creditquota", 0);
                    insertSQL.setValue("discountamount", row.getBigDecimal("kyyh"));
                    insertSQL.setValue("sa_accountclassid", accountrows.get(0).getLong("sa_accountclassid"));
                    insertSQL.setValue("remarks", "年结转入");

                    insertSQL.setValue("createby", username);
                    insertSQL.setDateValue("createdate");
                    insertSQL.setValue("changeby", username);
                    insertSQL.setDateValue("changedate");
                    sqlList.add(insertSQL.getSQL());
                }
            }
            sqlList.add(
                    DataContrlLog.createLog(this, "st_period", st_periodid, "账户结账", "会计期间账户结账成功").getSQL());
        } else {
            sqlList.add("delete from sa_cashbeginbal where year="+nextMonthFirstDay.getYear()+" and month="+nextMonthFirstDay.getMonthValue());
            sqlList.add(
                    DataContrlLog.createLog(this, "st_period", st_periodid, "反账户结账", "会计期间反账户结账成功").getSQL());
        }
        dbConnect.runSqlUpdate(sqlList);

        return getSucReturnObject().toString();
    }


    @API(title = "库存结账", apiversion = R.ID2025072314213503.v1.class)
    @CACHEING_CLEAN( apiClass = {period.class})
    public String invbalclose() throws YosException {
        Long st_periodid = content.getLong("st_periodid");
        boolean isclose = content.getBoolean("isclose");
        Rows rows = dbConnect.runSqlQuery("select * from st_period where siteid='"+siteid+"' and st_periodid="+st_periodid);
        if(rows.isEmpty()){
            return getErrReturnObject().setErrMsg("会计期间不存在").toString();
        }
        ArrayList<String> sqlList = new ArrayList<>();
        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "st_period");
        updateSQL.setUniqueid(st_periodid);
        updateSQL.setSiteid(siteid);
        updateSQL.setValue("isclose",isclose);
        updateSQL.setValue("closeby", isclose?username:"null");
        if(isclose){
            updateSQL.setDateValue("closedate");
        }else{
            updateSQL.setValue("closedate","null");
        }

        sqlList.add(updateSQL.getSQL());
        if (isclose) {
            YearMonth specificYearMonth = YearMonth.of(rows.get(0).getInteger("year"), rows.get(0).getInteger("month"));
            // 获取上一个月
            YearMonth previousMonth = specificYearMonth.minusMonths(1);

            System.err.println("\n" +
                    "\t\t\t\t\tselect t1.itemid,t3.stockid,ifnull(t2.qty,0) + ifnull(t3.qty,0) qty from plm_item t1 left join st_stock t0 on t1.stockno=t0.stockno and t1.siteid=t0.siteid and t0.isused=1 \n" +
                    "\t\t\t\t\tleft join (\n" +
                    "\t\t\t\t\tselect itemid,stockid,qty,siteid from  sa_inventoryear\n" +
                    "\t\t\t\t\twhere year ='"+previousMonth.getYear()+"' and month ='"+ previousMonth.getMonthValue()+"'\n" +
                    "\t\t\t\t\t)t2 on t1.itemid = t2.itemid and t1.siteid = t2.siteid\n" +
                    "\t\t\t\tleft join (\n" +
                    "\t\t\t\t\tselect itemid,stockid,siteid,sum(qty) qty from ( \n" +
                    "\t\t\t\t\t\tselect t2.itemid,t2.stockid,t1.siteid, \n" +
                    "\t\t\t\t\t\tcase when type like '%入%' and rb ='1' then  t2.qty\n" +
                    "\t\t\t\t\t\t\t when type like '%入%' and rb ='0' then -t2.qty\n" +
                    "\t\t\t\t\t\t\t when type like '%出%' and rb ='1' then -t2.qty\n" +
                    "\t\t\t\t\t\t\t when type like '%出%' and rb ='0' then  t2.qty\n" +
                    "\t\t\t\t\t\tend qty\n" +
                    "\t\t\t\t\t\t from st_stockbill t1\n" +
                    "\t\t\t\t\t\tjoin st_stockbill_items t2 on t1.st_stockbillid = t2.st_stockbillid and t1.siteid = t2.siteid \n" +
                    "\t\t\t\t\t\twhere t1.status ='审核' and ifnull(t1.typemx,'') != '正品入库'\n" +
                    "\t\t\t\t\t\tand  DATE_FORMAT(t1.billdate, '%Y-%m-%d') >='"+rows.get(0).getString("begdate")+"'\n" +
                    "\t\t\t\t\t\tand  DATE_FORMAT(t1.billdate, '%Y-%m-%d') <='"+rows.get(0).getString("enddate")+"'\n" +
                    "\t\t\t\t\t\tunion all \n" +
                    "\t\t\t\t\t\tselect t2.itemid,t2.stockid,t1.siteid,  \n" +
                    "\t\t\t\t\t\tcase when type like '%入%' and rb ='1' then  t2.qty\n" +
                    "\t\t\t\t\t\t\t when type like '%入%' and rb ='0' then -t2.qty\n" +
                    "\t\t\t\t\t\tend qty\n" +
                    "\t\t\t\t\t\t from st_stockbill t1\n" +
                    "\t\t\t\t\t\tjoin st_stockbill_items t2 on t1.st_stockbillid = t2.st_stockbillid and t1.siteid = t2.siteid\n" +
                    "\t\t\t\t\t\twhere t1.status ='审核' and ifnull(t1.typemx,'')  = '正品入库' and ifnull(t2.amount,0) != 0\n" +
                    "\t\t\t\t\t\tand  DATE_FORMAT(t1.billdate, '%Y-%m-%d') >='"+rows.get(0).getString("begdate")+"'\n" +
                    "\t\t\t\t\t\tand  DATE_FORMAT(t1.billdate, '%Y-%m-%d') <='"+rows.get(0).getString("enddate")+"'\n" +
                    "\t\t\t\t\t)t group by itemid,stockid,siteid\n" +
                    "\t\t\t\t)t3 on t1.itemid = t3.itemid and t0.stockid = t3.stockid and t1.siteid = t3.siteid \n" +
                    "\t\t\t\twhere  ifnull(t2.qty,0) + ifnull(t3.qty,0)!=0 ");
            Rows invbalRows= dbConnect.runSqlQuery("\n" +
                    "\t\t\t\t\tselect t1.itemid,t3.stockid,ifnull(t2.qty,0) + ifnull(t3.qty,0) qty from plm_item t1 left join st_stock t0 on t1.stockno=t0.stockno and t1.siteid=t0.siteid and t0.isused=1 \n" +
                    "\t\t\t\t\tleft join (\n" +
                    "\t\t\t\t\tselect itemid,stockid,qty,siteid from  sa_inventoryear\n" +
                    "\t\t\t\t\twhere year ='"+previousMonth.getYear()+"' and month ='"+ previousMonth.getMonthValue()+"'\n" +
                    "\t\t\t\t\t)t2 on t1.itemid = t2.itemid and t1.siteid = t2.siteid\n" +
                    "\t\t\t\tleft join (\n" +
                    "\t\t\t\t\tselect itemid,stockid,siteid,sum(qty) qty from ( \n" +
                    "\t\t\t\t\t\tselect t2.itemid,t2.stockid,t1.siteid, \n" +
                    "\t\t\t\t\t\tcase when type like '%入%' and rb ='1' then  t2.qty\n" +
                    "\t\t\t\t\t\t\t when type like '%入%' and rb ='0' then -t2.qty\n" +
                    "\t\t\t\t\t\t\t when type like '%出%' and rb ='1' then -t2.qty\n" +
                    "\t\t\t\t\t\t\t when type like '%出%' and rb ='0' then  t2.qty\n" +
                    "\t\t\t\t\t\tend qty\n" +
                    "\t\t\t\t\t\t from st_stockbill t1\n" +
                    "\t\t\t\t\t\tjoin st_stockbill_items t2 on t1.st_stockbillid = t2.st_stockbillid and t1.siteid = t2.siteid \n" +
                    "\t\t\t\t\t\twhere t1.status ='审核' and ifnull(t1.typemx,'') != '正品入库'\n" +
                    "\t\t\t\t\t\tand  DATE_FORMAT(t1.billdate, '%Y-%m-%d') >='"+rows.get(0).getString("begdate")+"'\n" +
                    "\t\t\t\t\t\tand  DATE_FORMAT(t1.billdate, '%Y-%m-%d') <='"+rows.get(0).getString("enddate")+"'\n" +
                    "\t\t\t\t\t\tunion all \n" +
                    "\t\t\t\t\t\tselect t2.itemid,t2.stockid,t1.siteid,  \n" +
                    "\t\t\t\t\t\tcase when type like '%入%' and rb ='1' then  t2.qty\n" +
                    "\t\t\t\t\t\t\t when type like '%入%' and rb ='0' then -t2.qty\n" +
                    "\t\t\t\t\t\tend qty\n" +
                    "\t\t\t\t\t\t from st_stockbill t1\n" +
                    "\t\t\t\t\t\tjoin st_stockbill_items t2 on t1.st_stockbillid = t2.st_stockbillid and t1.siteid = t2.siteid\n" +
                    "\t\t\t\t\t\twhere t1.status ='审核' and ifnull(t1.typemx,'')  = '正品入库' and ifnull(t2.amount,0) != 0\n" +
                    "\t\t\t\t\t\tand  DATE_FORMAT(t1.billdate, '%Y-%m-%d') >='"+rows.get(0).getString("begdate")+"'\n" +
                    "\t\t\t\t\t\tand  DATE_FORMAT(t1.billdate, '%Y-%m-%d') <='"+rows.get(0).getString("enddate")+"'\n" +
                    "\t\t\t\t\t)t group by itemid,stockid,siteid\n" +
                    "\t\t\t\t)t3 on t1.itemid = t3.itemid and t0.stockid = t3.stockid and t1.siteid = t3.siteid \n" +
                    "\t\t\t\twhere  ifnull(t2.qty,0) + ifnull(t3.qty,0)!=0 ");
            if(invbalRows.isNotEmpty()){
                for (Row row :invbalRows){
                    InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_inventoryear");
                    insertSQL.setUniqueid(createTableID("sa_inventoryear"));
                    insertSQL.setSiteid(siteid);
                    insertSQL.setValue("month", rows.get(0).getLong("month"));
                    insertSQL.setValue("year", rows.get(0).getLong("year") );
                    insertSQL.setValue("itemid",  row.getLong("itemid"));
                    insertSQL.setValue("stockid", row.getLong("stockid"));
                    insertSQL.setValue("qty",row.getBigDecimal("qty"));
                    insertSQL.setValue("remarks", "月结转入");

                    insertSQL.setValue("createby", username);
                    insertSQL.setDateValue("createdate");
                    insertSQL.setValue("changeby", username);
                    insertSQL.setDateValue("changedate");
                    sqlList.add(insertSQL.getSQL());
                }
            }
            sqlList.add(
                    DataContrlLog.createLog(this, "st_period", st_periodid, "库存结账", "会计期间库存结账成功").getSQL());
        } else {
            sqlList.add("delete from sa_inventoryear where year="+rows.get(0).getLong("year")+" and month="+rows.get(0).getLong("month"));
            sqlList.add(
                    DataContrlLog.createLog(this, "st_period", st_periodid, "反库存结账", "会计期间反库存结账成功").getSQL());
        }

        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

    @API(title = "生成下一年会计期间", apiversion = R.ID2025072314214503.v1.class)
    @CACHEING_CLEAN( apiClass = {period.class})
    public String createnewyearperiod() throws YosException {

        ArrayList<String> sqlList = new ArrayList<>();
        Calendar calendar = Calendar.getInstance();
        int currentYear = calendar.get(Calendar.YEAR);
        int nextYear = currentYear + 1;
        for(int i=1;i<13;i++){
            YearMonth yearMonth = YearMonth.of(nextYear, i);
            LocalDate firstDayOfMonth = yearMonth.atDay(1);
            LocalDate lastDayOfMonth = yearMonth.atEndOfMonth();

            InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "st_period");
            insertSQL.setUniqueid(createTableID("st_period"));
            insertSQL.setSiteid(siteid);
            insertSQL.setValue("month", i);
            insertSQL.setValue("year", nextYear);
            insertSQL.setValue("enddate",firstDayOfMonth);
            insertSQL.setValue("begdate", lastDayOfMonth);
            sqlList.add(insertSQL.getSQL());
        }
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

}