Android手持机扫码出入库的开发详解-5.基础数据下载更新

Android手持机扫码出入库的开发详解-5.基础数据下载更新

基础数据下载/更新

基础数据下载程序图

flowchart TD
    A[用户操作] --> B{操作类型}
    B -->|获取开票日期| C1[启动QueryAddressTask异步任务]
    B -->|下载领料单| C2[启动QueryAddressTask异步任务]
    B -->|下载材料编码表| C3[启动QueryAddressTask异步任务]
    B -->|下载库管员编码表| C4[启动QueryAddressTask异步任务]
    B -->|下载厂家编码表| C5[启动QueryAddressTask异步任务]
    
    C1 --> D[doInBackground执行]
    C2 --> D
    C3 --> D
    C4 --> D
    C5 --> D
    
    D --> E{根据参数执行查询}
    E -->|获取开票日期| F1[GetTheDate]
    E -->|领料单| F2[QueryServerBillingData]
    E -->|材料编码表| F3[QueryServerBillOfMaterialData]
    E -->|库管员编码表| F4[QueryServerStoreKeeperData]
    E -->|厂家编码表| F5[QueryServerManufacturerData]
    
    F1 --> G1[构建MSSQL查询日期语句]
    F2 --> G2[构建MSSQL查询领料单语句]
    F3 --> G3[构建MSSQL查询材料编码表语句]
    F4 --> G4[构建MSSQL查询库管员编码表语句]
    F5 --> G5[构建MSSQL查询厂家编码表语句]
    
    G1 --> H[调用QueryData.RsRowsColsNotPares执行MSSQL查询]
    G2 --> H
    G3 --> H
    G4 --> H
    G5 --> H
    
    H --> I[获取查询结果]
    I --> J{结果类型}
    J -->|日期数据| K1[将结果添加到日期列表]
    J -->|其他数据| K2[将结果映射到实体对象列表]
    
    K1 --> L1[返回日期获取结果]
    K2 --> L2[打开或创建SQLite数据库]
    L2 --> M[调用DAO的insertDatas方法]
    M --> N[开始SQLite事务]
    N --> O[批量插入数据到SQLite]
    O --> P[设置事务成功]
    P --> Q[结束事务]
    Q --> R[返回下载结果]
    
    L1 --> S[onPostExecute执行]
    R --> S
    
    S --> T{根据操作类型更新UI}
    T -->|获取日期| U1[更新日期下拉列表]
    T -->|下载数据| U2[显示下载结果统计信息]

基础数据下载源代码

核心控制器：DownloadData.java

// 异步任务处理数据下载
class QueryAddressTask extends AsyncTask<String, Integer, String> {
    String result = "";

    @Override
    protected String doInBackground(String... params) {
        try {
            switch (params[0].toString()) {
                case "获取开票日期":
                    result = GetTheDate();
                    break;
                case "A库":
                case "B库":
                case "C库":
                    result = QueryServerBillingData();
                    break;
                case "材料编码表":
                    result = QueryServerBillOfMaterialData();
                    break;
                case "库管员编码表":
                    result = QueryServerStoreKeeperData();
                    break;
                case "厂家编码表":
                    result = QueryServerManufacturerData();
                    break;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    protected void onPostExecute(String result) {
        switch (str_Action.toString()) {
            case "A库":
            case "B库":
            case "C库":
            case "材料编码表":
            case "库管员编码表":
            case "厂家编码表":
                textView_DownloadData_QueryInfo.setText("数据读取完毕！");
                editText_DownloadData_QueryData.setText(result);
                break;
            case "获取开票日期":
                textView_DownloadData_QueryInfo.setText(result);
                dropDownListView_DownloadData_Date.setItemsData(list_Date);
                break;
        }
    }
}

// 获取开票日期
public String GetTheDate() {
    Date date = new Date();
    long begin_date = date.getTime();
    String str = "";
    String oSql = "";

    // 根据仓库名称构建不同的查询语句
    switch (str_WarehouseName.toString()) {
        case "A库":
            oSql = "SELECT DISTINCT TOP 30 CONVERT(VARCHAR(10),开票时间,120) AS 开票时间  " +
                    "FROM MES_CBHS..成本核算_月领料单A " +
                    "WHERE 开票时间 BETWEEN GETDATE()-30 AND GETDATE() AND 材料价格=0  ORDER BY 开票时间 DESC";
            break;
        case "B库":
            oSql = "SELECT DISTINCT TOP 30 CONVERT(VARCHAR(10),开票时间,120) AS 开票时间  " +
                    "FROM MES_CBHS..成本核算_月领料单B " +
                    "WHERE 开票时间 BETWEEN GETDATE()-30 AND GETDATE() AND 材料价格=0  ORDER BY 开票时间 DESC";
            break;
        case "C库":
            oSql = "SELECT DISTINCT TOP 30 CONVERT(VARCHAR(10),开票时间,120) AS 开票时间  " +
                    "FROM MES_CBHS..成本核算_月领料单C " +
                    "WHERE 开票时间 BETWEEN GETDATE()-30 AND GETDATE() AND 材料价格=0  ORDER BY 开票时间 DESC";
            break;
    }

    String[][] rs = new String[0][];
    try {
        rs = QueryData.RsRowsColsNotPares(oSql.toString());
    } catch (SQLException e) {
        e.printStackTrace();
    }

    list_Date.clear();
    for (int i = 0; i < rs.length; i++) {
        list_Date.add(rs[i][0].toString());
    }

    date = new Date();
    long end_date = date.getTime();
    str = "共耗时" + String.valueOf(end_date - begin_date).toString() + "毫秒\n" + "本次共获取" + list_Date.size() + "个日期";
    return str;
}

// 查询服务器领料单数据下载
public String QueryServerBillingData() {
    Date date = new Date();
    long begin_date = date.getTime();
    String str = "";
    
    // 构建MSSQL查询语句
    String oSql = "SELECT " +
        "'" + str_WarehouseName + "' as 扫码仓库," +
        "A.编号," +
        "A.材料编号," +
        "A.材料名称," +
        "A.材料规格," +
        "A.单位," +
        "CAST(A.实领数量 AS INT) AS 实领数量," +
        "A.库管员," +
        "CONVERT(VARCHAR(10),A.日期,120) AS 日期," +
        "A.开票人," +
        "CONVERT(VARCHAR(19),A.开票时间,120) AS 开票时间," +
        "ISNULL(B.材料条码,'') AS 材料条码" +
        " FROM (" +
        "SELECT *,'' AS 材料规格A" +
        " FROM MES_CBHS..成本核算_月领料单" + str_WarehouseName.replace("库", "").toString() +
        " WHERE 开票时间 BETWEEN '" + str_Date.toString() + "' AND '" + str_Date.toString() + "  23:59:59' AND  材料价格=0" +
        ") A " +
        " LEFT JOIN (" +
        "SELECT *,'' AS 材料规格A" +
        " FROM MES_CBHS..成本核算_价格库" +
        " WHERE 材料编号1 like 'B%' and 材料条码<>''" +
        ") B " +
        " ON A.材料编号=B.材料编号1 AND A.材料名称=B.材料名称";
    
    // 执行MSSQL查询
    String[][] rs = new String[0][];
    try {
        rs = QueryData.RsRowsColsNotPares(oSql.toString());
    } catch (SQLException e) {
        e.printStackTrace();
    }
    
    // 将结果映射到实体对象列表
    List<Billing> billings = new ArrayList<Billing>();
    for (int i = 0; i < rs.length; i++) {
        Billing billing = new Billing();
        billing.setStr_扫码仓库(rs[i][0].toString());
        billing.setStr_编号(rs[i][1].toString());
        billing.setStr_材料编号(rs[i][2].toString());
        billing.setStr_材料名称(rs[i][3].toString());
        billing.setStr_材料规格(rs[i][4].toString());
        billing.setStr_单位(rs[i][5].toString());
        billing.setInt_实领数量(Integer.parseInt(rs[i][6].toString()));
        billing.setStr_库管员(rs[i][7].toString());
        billing.setStr_日期(rs[i][8].toString());
        billing.setStr_开票人(rs[i][9].toString());
        billing.setStr_开票时间(rs[i][10].toString());
        billing.setStr_材料条码(rs[i][11].toString());
        billings.add(billing);
    }
    
    // 保存到SQLite数据库
    try {
        if (sda == null) {
            OpenOrCreateDatabase();// 打开或创建数据库
        }
        Cursor cursor = dao_Billing.insertDatas(db, billings);
    } catch (Exception e) {
        e.printStackTrace();
    }
    
    date = new Date();
    long end_date = date.getTime();
    str = "共耗时" + String.valueOf(end_date - begin_date).toString() + "毫秒\n" + "本次共下载" + billings.size() + "条记录";
    return str;
}

// 查询服务器材料编码表数据下载
public String QueryServerBillOfMaterialData() {
    Date date = new Date();
    long begin_date = date.getTime();
    String str = "";
    
    // 构建MSSQL查询语句
    String oSql = "SELECT " +
        "ISNULL(材料名称,SPACE(20)) AS 材料名称," +
        "ISNULL(材料规格,SPACE(50)) AS 材料规格," +
        "ISNULL(颜色,SPACE(20)) 颜色," +
        "ISNULL(CBW对照号,SPACE(15)) as CBW对照号," +
        "ISNULL(编码,SPACE(10)) as 材料条码," +
        "ISNULL(单位,SPACE(10)) as 单位" +
        " FROM 材料编码表 " +
        " ORDER BY 材料条码";
    
    // 执行MSSQL查询
    String[][] rs = new String[0][];
    try {
        rs = QueryData.RsRowsColsNotPares(oSql.toString());
    } catch (SQLException e) {
        e.printStackTrace();
    }
    
    // 将结果映射到实体对象列表
    List<BillOfMaterial> materials = new ArrayList<BillOfMaterial>();
    for (int i = 0; i < rs.length; i++) {
        BillOfMaterial material = new BillOfMaterial();
        material.setStr_材料名称(rs[i][0].toString());
        material.setStr_材料规格(rs[i][1].toString());
        material.setStr_颜色(rs[i][2].toString());
        material.setStr_CBW对照号(rs[i][3].toString());
        material.setStr_材料条码(rs[i][4].toString());
        material.setStr_单位(rs[i][5].toString());
        materials.add(material);
    }
    
    // 保存到SQLite数据库
    try {
        if (sda == null) {
            OpenOrCreateDatabase();// 打开或创建数据库
        }
        Cursor cursor = dao_BillOfMaterial.insertDatas(db, materials);
    } catch (Exception e) {
        e.printStackTrace();
    }
    
    date = new Date();
    long end_date = date.getTime();
    str = "共耗时" + String.valueOf(end_date - begin_date).toString() + "毫秒\n" + "本次共下载" + materials.size() + "条记录";
    return str;
}

// 查询服务器库管员编码表数据下载
public String QueryServerStoreKeeperData() {
    Date date = new Date();
    long begin_date = date.getTime();
    String str = "";
    
    // 构建MSSQL查询语句
    String oSql = "SELECT " +
        "ISNULL(保管员姓名,SPACE(10)) AS 库管员," +
        "ISNULL(保管员编码,SPACE(2)) AS 库管编码" +
        " FROM 保管员编码表 " +
        " ORDER BY 库管编码";
    
    // 执行MSSQL查询
    String[][] rs = new String[0][];
    try {
        rs = QueryData.RsRowsColsNotPares(oSql.toString());
    } catch (SQLException e) {
        e.printStackTrace();
    }
    
    // 将结果映射到实体对象列表
    List<StoreKeeper> keepers = new ArrayList<StoreKeeper>();
    for (int i = 0; i < rs.length; i++) {
        StoreKeeper keeper = new StoreKeeper();
        keeper.setStr_库管员(rs[i][0].toString());
        keeper.setStr_库管编码(rs[i][1].toString());
        keepers.add(keeper);
    }
    
    // 保存到SQLite数据库
    try {
        if (sda == null) {
            OpenOrCreateDatabase();// 打开或创建数据库
        }
        Cursor cursor = dao_StoreKeeper.insertDatas(db, keepers);
    } catch (Exception e) {
        e.printStackTrace();
    }
    
    date = new Date();
    long end_date = date.getTime();
    str = "共耗时" + String.valueOf(end_date - begin_date).toString() + "毫秒\n" + "本次共下载" + keepers.size() + "条记录";
    return str;
}

// 查询服务器厂家编码表数据下载
public String QueryServerManufacturerData() {
    Date date = new Date();
    long begin_date = date.getTime();
    String str = "";
    
    // 构建MSSQL查询语句
    String oSql = "SELECT " +
        "ISNULL(厂家缩写,SPACE(20)) AS 厂家缩写," +
        "ISNULL(厂家编码,SPACE(10)) AS 厂家代码" +
        " FROM 厂家编码表 " +
        " WHERE DEPT='采购部'" +
        " ORDER BY 厂家代码";
    
    // 执行MSSQL查询
    String[][] rs = new String[0][];
    try {
        rs = QueryData.RsRowsColsNotPares(oSql.toString());
    } catch (SQLException e) {
        e.printStackTrace();
    }
    
    // 将结果映射到实体对象列表
    List<Manufacturer> manufacturers = new ArrayList<Manufacturer>();
    for (int i = 0; i < rs.length; i++) {
        Manufacturer manufacturer = new Manufacturer();
        manufacturer.setStr_厂家缩写(rs[i][0].toString());
        manufacturer.setStr_厂家代码(rs[i][1].toString());
        manufacturers.add(manufacturer);
    }
    
    // 保存到SQLite数据库
    try {
        if (sda == null) {
            OpenOrCreateDatabase();// 打开或创建数据库
        }
        Cursor cursor = dao_Manufacturer.insertDatas(db, manufacturers);
    } catch (Exception e) {
        e.printStackTrace();
    }
    
    date = new Date();
    long end_date = date.getTime();
    str = "共耗时" + String.valueOf(end_date - begin_date).toString() + "毫秒\n" + "本次共下载" + manufacturers.size() + "条记录";
    return str;
}

// 数据库操作方法
private void OpenOrCreateDatabase() {
    sda = new SqliteDBConnection(this);
    sda.open();
    db = sda.getDb();
}

private void CloseDatabase() {
    if (db != null && db.isOpen()) {
        db.close();
        sda.close();
        db = null;
        sda = null;
    }
}

实体类：Billing.java

public class Billing implements Serializable {
    private String str_扫码仓库;
    private String str_编号;
    private String str_材料编号;
    private String str_材料名称;
    private String str_材料规格;
    private String str_单位;
    private int int_实领数量;
    private String str_库管员;
    private String str_日期;
    private String str_开票人;
    private String str_开票时间;
    private String str_材料条码;
    private long Long_ID;
    
    // getter和setter方法省略
}

DAO接口：BillingDAO.java

public interface BillingDAO {
    public Cursor insertData(SQLiteDatabase db, Billing billing) throws Exception;
    public Cursor insertDatas(SQLiteDatabase db, List<Billing> billings) throws Exception;
    public String createTable(SQLiteDatabase db) throws Exception;
    // 其他方法省略
}

DAO实现类：BillingDAOSqliteImpl.java

@Override
public Cursor insertDatas(SQLiteDatabase db, List<Billing> billings) throws Exception {
    ContentValues initialValues;
    Cursor cursor=null;
    db.beginTransaction(); // 开始事务
    try {
        for(Billing billing:billings){
            initialValues = new ContentValues();
            initialValues.put(KEY_扫码仓库,billing.getStr_扫码仓库());
            initialValues.put(KEY_编号,billing.getStr_编号());
            initialValues.put(KEY_材料编号,billing.getStr_材料编号());
            initialValues.put(KEY_材料名称,billing.getStr_材料名称());
            initialValues.put(KEY_材料规格,billing.getStr_材料规格());
            initialValues.put(KEY_单位,billing.getStr_单位());
            initialValues.put(KEY_实领数量,billing.getInt_实领数量());
            initialValues.put(KEY_库管员,billing.getStr_库管员());
            initialValues.put(KEY_日期,billing.getStr_日期());
            initialValues.put(KEY_开票人,billing.getStr_开票人());
            initialValues.put(KEY_开票时间,billing.getStr_开票时间());
            initialValues.put(KEY_材料条码,billing.getStr_材料条码());
            db.insert(DATABASE_TABLE, null, initialValues);
        }
        db.setTransactionSuccessful(); // 设置事务成功
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        db.endTransaction(); // 结束事务
    }
    
    // 查询所有数据并返回
    String str_Sql = "select _id,扫码仓库,编号,材料编号,材料名称,材料规格,单位,实领数量,库管员,日期,开票人,开票时间,材料条码 from billings";
    cursor = sqliteQueryData.RowsCols(db, str_Sql);
    return cursor;
}