using System; using System.Collections.Concurrent; using System.Collections.Generic; using System.Diagnostics; using System.Linq; using System.Text; using System.Threading; namespace POSV.Utils { public class InitUtils { private static NLog.Logger logger = NLog.LogManager.GetCurrentClassLogger(); private static object _lock = new object(); private static InitUtils _instance; public static InitUtils Instance { get { if (_instance == null) { lock (_lock) { _instance = new InitUtils(); } } return _instance; } } /// /// 系统初始化，清理数据库数据 /// /// /// public bool Init(string tenantId,string storeId,bool tenantChanged , bool storeChanged) { bool result = false; try { var lists = new ConcurrentQueue(); //租户或者门店任意一个变更，都清除数据 if (tenantChanged || storeChanged) { //变更开放接口的租户编码 lists.Enqueue(string.Format("update [pos_apis] set [tenantId] = '{0}';" , tenantId)); //变更开放地址的租户编码 lists.Enqueue(string.Format("update [pos_urls] set [tenantId] = '{0}';" , tenantId)); //变更参数配置的租户编码 lists.Enqueue(string.Format("update [pos_config] set [tenantId] = '{0}';" , tenantId)); //变更参数配置-流水序号 lists.Enqueue(string.Format("update [pos_config] set [values] = '{0}' where [group] = '{1}' and [keys] = '{2}';" , "0001" , ConfigConstant.GROUP_BUSINESS , ConfigConstant.VOUCHER_NO)); //变更模块表的租户编码 lists.Enqueue(string.Format("update [pos_module] set [tenantId] = '{0}';" , tenantId)); //变更资源表的租户编码 lists.Enqueue(string.Format("update [pos_resources] set [tenantId] = '{0}';" , tenantId)); //变更快捷表的租户编码 lists.Enqueue(string.Format("update [pos_shortcut] set [tenantId] = '{0}';" , tenantId)); //清理厨打方案表信息 lists.Enqueue("delete from [pos_kit_plan];"); //清理订单主表信息 lists.Enqueue("delete from [pos_order];"); //清理订单商品明细表信息 lists.Enqueue("delete from [pos_order_item];"); //清理订单商品做法表信息 lists.Enqueue("delete from [pos_order_item_make];"); //清理订单商品优惠表信息 lists.Enqueue("delete from [pos_order_item_promotion];"); //清理订单优惠表信息 lists.Enqueue("delete from [pos_order_promotion];"); //清理订单支付表信息 lists.Enqueue("delete from [pos_order_pay];"); //清理耗料数据 lists.Enqueue("delete from [pos_order_product_burden];"); //清理厨显方案表信息 lists.Enqueue("delete from [pos_kds_plan];"); //清理授权日志信息 lists.Enqueue("delete from [pos_log];"); //清理沽清信息 lists.Enqueue("delete from [pos_sale_clear];"); //清理交班信息 lists.Enqueue("delete from [pos_store_handover];"); lists.Enqueue("delete from [pos_store_handover_detail];"); lists.Enqueue("delete from [pos_store_handover_detail_part];"); //清理未上传交班 lists.Enqueue("delete from [pos_upload_handover_json];"); //清理未上传非营业数据 lists.Enqueue("delete from [pos_upload_cost_revenue_json];"); //清理非营业收入支出 lists.Enqueue("delete from [pos_store_cost_revenue];"); //清理熟客信息 lists.Enqueue("delete from [pos_visitor];"); lists.Enqueue("delete from [pos_visitor_address];"); lists.Enqueue("delete from [pos_visitor_tag];"); //清理挂单信息 lists.Enqueue("delete from [pos_order_temp];"); //清理外送单信息 lists.Enqueue("delete from [pos_order_delivery];"); //清理交班信息 lists.Enqueue("delete from [pos_business_plan_log];"); //清理卡充值信息 lists.Enqueue("delete from [pos_card_recharge_pay];"); lists.Enqueue("delete from [pos_card_recharge];"); lists.Enqueue("delete from [pos_card_account_destroy];"); //清理核销记录 lists.Enqueue("delete from [pos_saoma_pay_ticket];"); //清理礼品卡销售记录 lists.Enqueue("delete from [pos_card_sale];"); lists.Enqueue("delete from [pos_card_sale_pay];"); //清理操作日志 lists.Enqueue("delete from [pos_store_operation_log];"); } //zhangy 2020-02-25 Edit //清理数据版本,无论是否更换门店或者租户，数据都重新下载 lists.Enqueue("delete from [pos_server_data_version];"); lock (Global.Instance.SyncLock) { SqliteUtils.ExecuteTransaction(lists); Thread.Sleep(30); } result = true; } catch(Exception ex) { logger.Error(ex, "数据库初始化异常"); result = false; } return result; } /// /// 清理3个月前已上传数据 /// /// public bool CleanHistoryData() { bool result = false; try { logger.Info("开始删除数据"); Stopwatch sw = Stopwatch.StartNew(); //删除3个月前的记录 var startMonth = DateTime.Now.AddMonths(-3).ToString("yyyy-MM-dd 00:00:00"); //删除5个月前的核销记录 var startHXMonth = DateTime.Now.AddMonths(-5).ToString("yyyy-MM-dd 00:00:00"); var list = new ConcurrentQueue(); list.Enqueue(string.Format("delete from [pos_order_delivery] where orderId in (select id from [pos_order] where syncStatus = 1 and finishDate < '{0}');", startMonth)); list.Enqueue(string.Format("delete from [pos_order_item] where orderId in (select id from [pos_order] where syncStatus = 1 and finishDate < '{0}');", startMonth)); list.Enqueue(string.Format("delete from [pos_order_item_make] where orderId in (select id from [pos_order] where syncStatus = 1 and finishDate < '{0}');", startMonth)); list.Enqueue(string.Format("delete from [pos_order_item_promotion] where orderId in (select id from [pos_order] where syncStatus = 1 and finishDate < '{0}');", startMonth)); list.Enqueue(string.Format("delete from [pos_order_pay] where orderId in (select id from [pos_order] where syncStatus = 1 and finishDate < '{0}');", startMonth)); list.Enqueue(string.Format("delete from [pos_order_promotion] where orderId in (select id from [pos_order] where syncStatus = 1 and finishDate < '{0}');", startMonth)); list.Enqueue(string.Format("delete from [pos_order] where syncStatus = 1 and finishDate < '{0}';", startMonth)); list.Enqueue(string.Format("delete from [pos_order_product_burden] where uploadStatus = 1 and createDate < '{0}';", startMonth)); list.Enqueue(string.Format("delete from [pos_saoma_pay_ticket] where createDate < '{0}';", startHXMonth)); list.Enqueue(string.Format("delete from [pos_store_operation_log] where createDate < '{0}';", startHXMonth)); //清理卡务数据 lock (Global.Instance.SyncLock) { SqliteUtils.ExecuteTransaction(list); Thread.Sleep(30); } logger.Info("完成清理[{0}]之前的历史数据，耗时[{1}]", startMonth, sw.ElapsedMilliseconds); sw.Stop(); result = true; } catch(Exception ex) { logger.Error(ex, "清理历史数据发生异常"); } return result; } } }