ScanClient/code/HXX.Scanner.Biz/biz/biz_db.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Configuration;

using SqlSugar;
using HXX.Scanner.Database;

namespace HXX.Scanner.Biz
{
    /// <summary>
    /// 数据库 业务类
    /// </summary>
    public class biz_db : biz_db_base
    {
        /// <summary>
        /// 向扫描数据表中增加一条记录
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public tb_file_info Add(file_info file)
        {
            tb_file_info entity = new tb_file_info();

            entity.fi_SN = file.SN;
            entity.fi_schema = file.paperSchema;
            entity.fi_side = file.current_side;
            entity.fi_paper_size = file.paperSize;

            entity.fi_name = file.file_name;
            entity.fi_full_name = file.file_full_name;
            entity.fi_ext = file.file_ext;
            entity.fi_size = 0;
            entity.fi_upload_flag = 0;
            entity.fi_createTime = DateTime.Now;

            entity.fi_accountId = file.accountId.ToString();
            entity.fi_examId = file.examId;
            entity.fi_examPaperId = file.examPaperId;
            entity.fi_zipId = file.zipId;
            entity.fi_batchSeq = file.batchNumber;
            entity.fi_isTest = file.isTest;
            entity.fi_token = file.token;
            entity.fi_server_url = ""; // file.uploadUrl;
            entity.fi_subjectCode = file.subjectCode;

            using (var db = SqlSugarHelper.GetClient())
            {
                db.Insertable<tb_file_info>(entity).ExecuteCommandIdentityIntoEntity();
            }

            return entity;
        }

        /// <summary>
        /// 获取本批次扫描数量
        /// </summary>
        /// <param name="batchNumber"></param>
        /// <param name="zipId"></param>
        /// <param name="ab">正反面</param>
        /// <returns></returns>
        public int get_count_by_seq(string batchNumber, int? zipId, int ab)
        {
            int result = 0;

            using (var db = SqlSugarHelper.GetClient())
            {
                result = db.Queryable<tb_file_info>().Count(x => x.fi_batchSeq == batchNumber && x.fi_zipId == zipId && x.fi_side == ab);
            }

            return result;
        }

        /// <summary>
        /// 根据sn获取记录组，相同一张纸，正反面拥有同一个sn
        /// </summary>
        /// <param name="SN"></param>
        /// <returns></returns>
        public List<tb_file_info> get_by_sn(long? SN)
        {
            List<tb_file_info> list;

            using (var db = SqlSugarHelper.GetClient())
            {
                list = db.Queryable<tb_file_info>().Where(x => x.fi_SN == SN).ToList();
            }

            return list;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="subjectCode"></param>
        /// <param name="batchNumber"></param>
        /// <returns></returns>
        public List<tb_file_info> get_by_subjectCode_batchNumber(string subjectCode, string batchNumber)
        {
            List<tb_file_info> list;

            using (var db = SqlSugarHelper.GetClient())
            {
                list = db.Queryable<tb_file_info>().Where(x => x.fi_batchSeq == batchNumber && x.fi_subjectCode == subjectCode).ToList();
            }

            return list;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="subjectCode"></param>
        /// <param name="batchNumber"></param>
        /// <returns></returns>
        public void deleteBatch(string subjectCode, string batchNumber)
        {
            using (var db = SqlSugarHelper.GetClient())
            {
                db.Deleteable<tb_file_info>(x => x.fi_batchSeq == batchNumber && x.fi_subjectCode == subjectCode).ExecuteCommand();
            }
        }

        //上传后，更新上传成功或失败标志
        public void update_upload_flag_by_id(int id, int status, string upload_url)
        {
            using (var db = SqlSugarHelper.GetClient())
            {
                var entity = db.Queryable<tb_file_info>().First(x => x.fi_id == id);

                if (entity != null)
                {
                    entity.fi_upload_flag = status;
                    entity.fi_server_url = upload_url;

                    db.Updateable<tb_file_info>(entity).ExecuteCommand();
                }
            }
        }

        //上传后，更新上传成功或失败标志
        public void update_upload_flag_by_SN(long sn, int status)
        {
            using (var db = SqlSugarHelper.GetClient())
            {
                var list = db.Queryable<tb_file_info>().Where(x => x.fi_SN == sn).ToList();

                foreach(var entity in list)
                {
                    entity.fi_upload_flag = status;

                    db.Updateable<tb_file_info>(entity).ExecuteCommand();
                }
            }
        }

        //更新文件位置
        public void update_file_name(int id, string fileName)
        {
            using (var db = SqlSugarHelper.GetClient())
            {
                var entity = db.Queryable<tb_file_info>().First(x => x.fi_id == id);

                if (entity != null)
                {
                    entity.fi_full_name = fileName;

                    db.Updateable<tb_file_info>(entity).ExecuteCommand();
                }
            }
        }


        //获取某批次所有数量
        public int get_count_by_batchNumber(string batchNumber, string subjectCode)
        {
            int count = 0;

            using (var db = SqlSugarHelper.GetClient())
            {
                count = db.Queryable<tb_file_info>().Count(x => x.fi_batchSeq == batchNumber && x.fi_subjectCode == subjectCode && x.fi_side == 1);
            }

            return count;
        }

        //获取某批次上传成功的数量
        public qty_of_upload get_upload_count_by_batchNumber(string batchNumber, string subjectCode)
        {
            qty_of_upload qty = new qty_of_upload();

            using (var db = SqlSugarHelper.GetClient())
            {
                qty.qty_success = db.Queryable<tb_file_info>().Count(x => x.fi_batchSeq == batchNumber && x.fi_subjectCode == subjectCode && x.fi_upload_flag == 1 && x.fi_side == 1);
                qty.qty_fail = db.Queryable<tb_file_info>().Count(x => x.fi_batchSeq == batchNumber && x.fi_subjectCode == subjectCode && x.fi_upload_flag != 1 && x.fi_side == 1);
            }

            return qty;
        }

        //获取某批次所有未上传记录
        public List<tb_file_info> get_not_upload_list(string batchNumber, string subjectCode)
        {
            List<tb_file_info> result;

            using (var db = SqlSugarHelper.GetClient())
            {
                result = db.Queryable<tb_file_info>().Where(x => x.fi_batchSeq == batchNumber && x.fi_subjectCode == subjectCode && x.fi_upload_flag != 1).ToList();                
            }

            return result;
        }

        //获取多批次所有未上传记录
        public List<tb_file_info> get_not_upload_list2(string[] batchList, string subjectCode)
        {
            List<tb_file_info> result;

            using (var db = SqlSugarHelper.GetClient())
            {
                result = db.Queryable<tb_file_info>().Where(x => batchList.Contains(x.fi_batchSeq) && x.fi_subjectCode == subjectCode && x.fi_upload_flag != 1).ToList();
            }

            return result;
        }

        //获取某些id的上传失败情况
        public int get_not_upload_status(string id_list)
        {
            int count = 0;

            using (var db = SqlSugarHelper.GetClient())
            {
                var sql = "select 1 from tb_file_info where fi_id in ({0}) and fi_upload_flag <> 1 and fi_side=1";

                sql = string.Format(sql, id_list);

                count = db.Ado.SqlQuery<tb_file_info>(sql).Count;
            }

            return count;
        }

        /// <summary>
        /// test
        /// </summary>
        /// <returns></returns>
        public List<tb_file_info> test()
        {
            List<tb_file_info> list;

            using (var db = SqlSugarHelper.GetClient())
            {
                list = db.Queryable<tb_file_info>().ToPageList(1, 10);
            }

            return list;
        }
    }
}