DS7/DSWeb/Areas/MvcShipping/DAL/MsOpStatus/MsOpStatusDAL.cs


using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using DSWeb.MvcShipping.Models.MsOpStatus;
using Microsoft.Practices.EnterpriseLibrary.Data;
using DSWeb.EntityDA;
using HcUtility.Comm;
using DSWeb.Areas.Account.Models.MsOpBill;
using DSWeb.Areas.Dispatch.Helper;
using Newtonsoft.Json;
using System.Configuration;
using DSWeb.Areas.CommMng.DAL;
using System.IO;
using DSWeb.MvcShipping.DAL.MsSysParamSet;
using System.Linq;

namespace DSWeb.Areas.MvcShipping.DAL
{
    public class MsOpStatusDAL
    {
        #region Inquery DataList

        static public List<MsOpStatus> GetDataList(string strCondition)
        {

            var strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("ST_ID,BSNO,STATUS,ISCOMP,COMPTIME,COMPOP,REMARK,INPUTBY,ISOPEN,");
            strSql.Append("INPUTTIME,ORDNO,STATUS_OPSEAE,(CASE WHEN ISOPEN='1' THEN '公开' ELSE '不公开' END) ISOPENREF,OPTIME ");
            strSql.Append(" from op_status  "); 

            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" where " + strCondition);
            }
            strSql.Append(" order by ORDNO,COMPTIME  "); 

            return SetData(strSql);
        }

        static public MsOpStatus GetData(string condition)
        {
            var list = GetDataList(condition);
            if (list.Count > 0)
                return list[0];

            return new MsOpStatus();
        }


        private static List<MsOpStatus> SetData(StringBuilder strSql)
        {
            var headList = new List<MsOpStatus>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    MsOpStatus data = new MsOpStatus();
                    #region Set DB data to Object
                    data.ST_ID = Convert.ToString(reader["ST_ID"]);
                    data.BSNO = Convert.ToString(reader["BSNO"]);
                    data.STATUS = Convert.ToString(reader["STATUS"]);
                    data.ISCOMP = reader["COMPTIME"] == DBNull.Value ? false : Convert.ToBoolean(reader["ISCOMP"]);
                    if (reader["COMPTIME"] != DBNull.Value)
                        data.COMPTIME = Convert.ToDateTime(reader["COMPTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
                    data.COMPOP = Convert.ToString(reader["COMPOP"]);
                    data.INPUTBY = Convert.ToString(reader["INPUTBY"]);
                    data.INPUTTIME = Convert.ToString(reader["INPUTTIME"]);
                    if (reader["ORDNO"] != DBNull.Value)
                    data.ORDNO = Convert.ToInt16(reader["ORDNO"]);
                    data.REMARK = Convert.ToString(reader["REMARK"]);
                    data.ISOPEN = Convert.ToString(reader["ISOPEN"]);
                    if (data.ISOPEN != "1") data.ISOPEN = "0";
                    data.ISOPENREF = Convert.ToString(reader["ISOPENREF"]);
                    if (reader["OPTIME"] != DBNull.Value)
                        data.OPTIME = Convert.ToDateTime(reader["OPTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion

        #region 业务状态保存，删除

        public static DBResult SaveUpdate( List<MsOpStatus> bodyList,string BsNo,string username)
        {
            var result = new DBResult();


            Database db = DatabaseFactory.CreateDatabase();
            using (var conn = db.CreateConnection())
            {
                conn.Open();
                var tran = conn.BeginTransaction();

                try
                {

                    var cmdInsert =
                        db.GetSqlStringCommand(
                               @"insert into op_status (ST_ID,BSNO,STATUS,ISCOMP,COMPTIME,COMPOP,REMARK,INPUTBY,INPUTTIME,ORDNO,STATUS_OPSEAE,ISOPEN) 
                                   values (@ST_ID,@BSNO,@STATUS,@ISCOMP,@COMPTIME,@COMPOP,@REMARK,@INPUTBY,@INPUTTIME,@ORDNO,@STATUS_OPSEAE,@ISOPEN) ");

                    var cmdUpdate =
                        db.GetSqlStringCommand(
                            @"update op_status set BSNO=@BSNO,STATUS=@STATUS,ISCOMP=@ISCOMP,COMPTIME=@COMPTIME,REMARK=@REMARK,INPUTBY=@INPUTBY
                               ,INPUTTIME=@INPUTTIME,ORDNO=@ORDNO,STATUS_OPSEAE=@STATUS_OPSEAE,ISOPEN=@ISOPEN where ST_ID=@GID ");

                    if (bodyList != null)
                    {
                        foreach (var enumValue in bodyList)
                        {

                            if (enumValue.BSNO == "*" || enumValue.ST_ID == "")
                            {
                                cmdInsert.Parameters.Clear();
                                db.AddInParameter(cmdInsert, "@ST_ID", DbType.String, Guid.NewGuid().ToString());
                                db.AddInParameter(cmdInsert, "@BSNO", DbType.String, BsNo);
                                db.AddInParameter(cmdInsert, "@ORDNO", DbType.Int32, enumValue.ORDNO);
                                db.AddInParameter(cmdInsert, "@STATUS", DbType.String, enumValue.STATUS);
                                db.AddInParameter(cmdInsert, "@ISCOMP", DbType.String, enumValue.ISCOMP);
                                db.AddInParameter(cmdInsert, "@COMPTIME", DbType.String, enumValue.COMPTIME);
                                db.AddInParameter(cmdInsert, "@COMPOP", DbType.String, enumValue.COMPOP);
                                db.AddInParameter(cmdInsert, "@REMARK", DbType.String, enumValue.REMARK);
                                db.AddInParameter(cmdInsert, "@INPUTBY", DbType.String, username);
                                db.AddInParameter(cmdInsert, "@INPUTTIME", DbType.String, DateTime.Now.ToShortDateString());
                                db.AddInParameter(cmdInsert, "@STATUS_OPSEAE", DbType.String, enumValue.STATUS_OPSEAE);
                                db.AddInParameter(cmdInsert, "@ISOPEN", DbType.String, enumValue.ISOPEN);
                                db.ExecuteNonQuery(cmdInsert, tran);
                            }
                            else
                            {

                                cmdUpdate.Parameters.Clear();
                                //db.AddInParameter(cmdUpdate, "@ST_ID", DbType.String, enumValue.ST_ID);
                                db.AddInParameter(cmdUpdate, "@GID", DbType.String, enumValue.ST_ID);
                                db.AddInParameter(cmdUpdate, "@BSNO", DbType.String, enumValue.BSNO);
                                db.AddInParameter(cmdUpdate, "@ORDNO", DbType.Int32, enumValue.ORDNO);
                                db.AddInParameter(cmdUpdate, "@STATUS", DbType.String, enumValue.STATUS);
                                db.AddInParameter(cmdUpdate, "@ISCOMP", DbType.String, enumValue.ISCOMP);
                                db.AddInParameter(cmdUpdate, "@COMPTIME", DbType.String, enumValue.COMPTIME);
                                db.AddInParameter(cmdUpdate, "@COMPOP", DbType.String, enumValue.COMPOP);
                                db.AddInParameter(cmdUpdate, "@REMARK", DbType.String, enumValue.REMARK);
                                db.AddInParameter(cmdUpdate, "@INPUTBY", DbType.String, username);
                                db.AddInParameter(cmdUpdate, "@INPUTTIME", DbType.String, DateTime.Now.ToShortDateString());
                                db.AddInParameter(cmdUpdate, "@STATUS_OPSEAE", DbType.String, enumValue.STATUS_OPSEAE);
                                db.AddInParameter(cmdUpdate, "@ISOPEN", DbType.String, enumValue.ISOPEN);
                                db.ExecuteNonQuery(cmdUpdate, tran);
                            }
                        }
                    }



                    tran.Commit();
                }
                catch (Exception)
                {
                    tran.Rollback();

                    result.Success = false;
                    result.Message = "保存出现错误，请重试或联系系统管理员";

                    return result;
                }
            }

            result.Success = true;
            result.Message = "保存成功" + result.Message;

            return result;
        }

        public static DBResult SavePiliangUpdate(List<MsOpBill> billList, List<MsOpStatus> bodyList, string username)
        {
            var result = new DBResult();


            Database db = DatabaseFactory.CreateDatabase();
            using (var conn = db.CreateConnection())
            {
                conn.Open();
                var tran = conn.BeginTransaction();

                try
                {

                    var cmdInsert =
                        db.GetSqlStringCommand(
                               @"insert into op_status (ST_ID,BSNO,STATUS,ISCOMP,COMPTIME,COMPOP,REMARK,INPUTBY,INPUTTIME,ORDNO,STATUS_OPSEAE,ISOPEN) 
                                   values (@ST_ID,@BSNO,@STATUS,@ISCOMP,@COMPTIME,@COMPOP,@REMARK,@INPUTBY,@INPUTTIME,@ORDNO,@STATUS_OPSEAE,@ISOPEN) ");


                    if (billList != null)
                    {
                        foreach (var enumbillValue in billList)
                        {
                            if (bodyList != null)
                            {
                                foreach (var enumValue in bodyList)
                                {
                                    cmdInsert.Parameters.Clear();
                                    db.AddInParameter(cmdInsert, "@ST_ID", DbType.String, Guid.NewGuid().ToString());
                                    db.AddInParameter(cmdInsert, "@BSNO", DbType.String, enumbillValue.BSNO);
                                    db.AddInParameter(cmdInsert, "@ORDNO", DbType.Int32, enumValue.ORDNO);
                                    db.AddInParameter(cmdInsert, "@STATUS", DbType.String, enumValue.STATUS);
                                    db.AddInParameter(cmdInsert, "@ISCOMP", DbType.String, enumValue.ISCOMP);
                                    db.AddInParameter(cmdInsert, "@COMPTIME", DbType.String, enumValue.COMPTIME);
                                    db.AddInParameter(cmdInsert, "@COMPOP", DbType.String, enumValue.COMPOP);
                                    db.AddInParameter(cmdInsert, "@REMARK", DbType.String, enumValue.REMARK);
                                    db.AddInParameter(cmdInsert, "@INPUTBY", DbType.String, username);
                                    db.AddInParameter(cmdInsert, "@INPUTTIME", DbType.String, DateTime.Now.ToShortDateString());
                                    db.AddInParameter(cmdInsert, "@STATUS_OPSEAE", DbType.String, enumValue.STATUS_OPSEAE);
                                    db.AddInParameter(cmdInsert, "@ISOPEN", DbType.String, enumValue.ISOPEN);
                                    db.ExecuteNonQuery(cmdInsert, tran);
                                }
                            }

                        }
                    }



                    tran.Commit();
                }
                catch (Exception)
                {
                    tran.Rollback();

                    result.Success = false;
                    result.Message = "保存出现错误，请重试或联系系统管理员";

                    return result;
                }
            }

            result.Success = true;
            result.Message = "保存成功" + result.Message;

            return result;
        }

        
        public static DBResult DeleteStatus(List<MsOpStatus> bodyList)
        {
            var result = new DBResult();


            Database db = DatabaseFactory.CreateDatabase();
            using (var conn = db.CreateConnection())
            {
                conn.Open();
                var tran = conn.BeginTransaction();

                try
                {

                    var cmdInsert =
                        db.GetSqlStringCommand(
                               @"delete from op_status where ST_ID=@ST_ID ");

                    if (bodyList != null)
                    {
                        foreach (var enumValue in bodyList)
                        {

                            cmdInsert.Parameters.Clear();
                            db.AddInParameter(cmdInsert, "@ST_ID", DbType.String, enumValue.ST_ID);
                            db.ExecuteNonQuery(cmdInsert, tran);

                        }
                    }

                    tran.Commit();
                }
                catch (Exception)
                {
                    tran.Rollback();

                    result.Success = false;
                    result.Message = "删除出现错误，请重试或联系系统管理员";

                    return result;
                }
            }

            result.Success = true;
            result.Message = "删除成功" + result.Message;

            return result;
        }
        public static DBResult WxMsgStatus(string bsno,List<MsOpStatus> bodyList,string userid)
        {
            var result = new DBResult();
            var opstatuslist = GetDataList("BSNO='"+bsno+"'");

                        var OpStatusWxTemplateID= MsSysParamSetDAL.GetData("PARAMNAME='OpStatusWxTemplateID'");
            if (OpStatusWxTemplateID.PARAMVALUE == "3l_G9E1lvSEr4nhbwInWsfK_J4LV8pA2NmiVXp4ft4U")
            {
                if (bodyList != null)
                {
                    foreach (var enumValue in bodyList)
                    {
                        if (enumValue.STATUS == "通关完成") {
                            var isfind = false;
                            foreach (var opStatus in opstatuslist)
                            {
                                if (opStatus.STATUS == "接单完成") {
                                    if (opStatus.OPTIME != null && opStatus.OPTIME != "") {
                                        isfind = true;
                                    }

                                }
                            }
                            if (!isfind) {
                                result.Success = false;
                                result.Message = "请先推送 接单完成 状态";

                                return result;
                            }

                         }
                        if (enumValue.STATUS == "入库状态")
                        {
                            var isfind = false;
                            foreach (var opStatus in opstatuslist)
                            {
                                if (opStatus.STATUS == "通关完成")
                                {
                                    if (opStatus.OPTIME != null && opStatus.OPTIME != "")
                                    {
                                        isfind = true;
                                    }

                                }
                            }
                            if (!isfind)
                            {
                                result.Success = false;
                                result.Message = "请先推送 通关完成 状态";

                                return result;
                            }

                        }

                    }
                }
            }

                var headdata = MsOpBillDAL.GetData("BSNO='"+bsno+"'");

            var clientlist = GetDataDispClientList("InfoClient='"+headdata.CUSTOMERNAME+ "' and IsAudit=1");
            if (clientlist == null) {
                result.Success = false;
                result.Message = "此委托单位没有微信公众号中没有注册,无法发送消息！";

                return result;

            }
             var appid = ConfigurationManager.AppSettings["BeitongDispatchAppId"];
             var appsecret = ConfigurationManager.AppSettings["BeitongDispatchAppSecret"];
             var dispatchServerUrl = ConfigurationManager.AppSettings["BeitongDispatchServerUrl"];
            var errorMsg= string.Empty;
             var token= string.Empty;
             int expiresIn = 0;
             bool success = GetToken(appid, appsecret, out token, out expiresIn, out errorMsg);

            result.Success = true;
            result.Message = "发送成功" + result.Message;
            var query = from items in bodyList orderby items.COMPTIME select items;
            T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA();
            if (success)
            {
                if (bodyList != null && clientlist != null)
                {
                    foreach (var enumValue in query)
                    {

                        foreach (var client in clientlist)
                        {

                            //todo:发送消息
                            List<Tuple<string, string, string>> values = new List<Tuple<string, string, string>>();
                            var url = "";

                            if (OpStatusWxTemplateID.PARAMVALUE == "3l_G9E1lvSEr4nhbwInWsfK_J4LV8pA2NmiVXp4ft4U")
                            {
                                url = $"{dispatchServerUrl}/Dispatch/Dispatch/OpStatusZc?BSNO={headdata.BSNO}";
                                values.Add(new Tuple<string, string, string>("first", "您有一条新的货物状态通知\r\n", ""));
                                values.Add(new Tuple<string, string, string>("keyword1", headdata.MBLNO, ""));
                                values.Add(new Tuple<string, string, string>("keyword2", headdata.GOODSNAME, ""));
                                values.Add(new Tuple<string, string, string>("keyword3", enumValue.STATUS, ""));
                                values.Add(new Tuple<string, string, string>("remark", enumValue.REMARK, ""));
                            } else if (OpStatusWxTemplateID.PARAMVALUE == "9_p_rnwjEB0w_ULTw1wUjghRMzjVAKjLIQiO3MPSBbs")
                            {
                                values.Add(new Tuple<string, string, string>("first", "您有一条新的货物状态通知\r\n", ""));
                                values.Add(new Tuple<string, string, string>("keyword1", headdata.ORDERNO, ""));
                                values.Add(new Tuple<string, string, string>("keyword2", headdata.MBLNO, ""));
                                values.Add(new Tuple<string, string, string>("keyword3", headdata.ETD, ""));
                                values.Add(new Tuple<string, string, string>("keyword4", headdata.DESTINATION, ""));
                                values.Add(new Tuple<string, string, string>("remark","最新状态为"+ enumValue.STATUS+" "+enumValue.REMARK, ""));
                            }

                            string mess = string.Empty;
                            bool issuccess = SendTemplateMessage(appid, OpStatusWxTemplateID.PARAMVALUE, values, client.OpenId,url, token, out mess);
                            //                            result.Success = true;
                            if (!issuccess)
                            {
                                errorMsg = errorMsg + mess;
                                result.Success = false;
                                result.Message = "发送消息是错误！" + mess;
                                BasicDataRefDAL.SaveLog(mess, userid, "发送微信消息", "发送微信消息出错");

                            }
                            else
                            {

                                var blUpSQL = " update op_status set  OPTIME=GETDATE() WHERE ST_ID='"+ enumValue.ST_ID+ "'";
                                bool bl = T_ALL_DA.GetExecuteSqlCommand(blUpSQL);

                                result.Success =true;
                                result.Message = "";
                            }

                        }

                    }
                }

            }
            else
            {
                result.Success = false;
                result.Message = "获取公众号的token时出错！"+ errorMsg;

                return result;
            }


            if (errorMsg == "") errorMsg = "发送成功";
            result.Message = errorMsg;
            return result;
        }


        #endregion


        #region 参照部分
        static public List<MsDispClient> GetDataDispClientList(string strCondition)
        {

            var strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("GID,Name,Tel,OpenId,InfoClient");
            strSql.Append(" from Disp_InfoClient  ");

            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" where " + strCondition);
            }

            return SetDispClientData(strSql);
        }

        private static List<MsDispClient> SetDispClientData(StringBuilder strSql)
        {
            var headList = new List<MsDispClient>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    MsDispClient data = new MsDispClient();
                    #region Set DB data to Object
                    data.GID = Convert.ToString(reader["GID"]);
                    data.Name = Convert.ToString(reader["Name"]);
                    data.OpenId = Convert.ToString(reader["OpenId"]);
                    data.InfoClient = Convert.ToString(reader["InfoClient"]);
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        private static bool GetToken(string appid, string appsecret, out string token, out int expiresIn, out string errorMsg)
        {
            string tokenUrl = $"https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid={appid}&secret={appsecret}";
            string tokenStr = WebRequestHelper.DoGet(tokenUrl);

            if (tokenStr.IndexOf("errcode") > -1)
            {
                var obj = new { errcode = 0, errmsg = "" };
                var errorJson = JsonConvert.DeserializeAnonymousType(tokenStr, obj);
                errorMsg = errorJson.errmsg;
                token = null;
                expiresIn = 0;

                return false;
            }
            else
            {
                var obj = new { access_token = "", expires_in = 0 };
                var tokenJson = JsonConvert.DeserializeAnonymousType(tokenStr, obj);

                token = tokenJson.access_token;
                expiresIn = tokenJson.expires_in;
                errorMsg = null;

                return true;
            }

        }
        public static bool SendTemplateMessage(string appid, string templateId, List<Tuple<string, string, string>> values, string toUser, string url,string token, out string message)
        {

            StringWriter strWriter = new StringWriter();
            JsonWriter jwriter = new JsonTextWriter(strWriter);
            jwriter.WriteStartObject();
            jwriter.WritePropertyName("touser");
            jwriter.WriteValue(toUser);
            jwriter.WritePropertyName("template_id");
            jwriter.WriteValue(templateId);
            jwriter.WritePropertyName("url");
            jwriter.WriteValue(url);
            jwriter.WritePropertyName("data");

            //data
            jwriter.WriteStartObject();
            foreach (var item in values)
            {
                jwriter.WritePropertyName(item.Item1);
                jwriter.WriteStartObject();
                jwriter.WritePropertyName("value");
                jwriter.WriteValue(item.Item2);
                if (!string.IsNullOrWhiteSpace(item.Item3))
                {
                    jwriter.WritePropertyName("color");
                    jwriter.WriteValue(item.Item3);
                }

                jwriter.WriteEndObject();
            }
            jwriter.WriteEndObject();

            jwriter.WriteEndObject();

            string strJson = strWriter.ToString();
            try
            {

                string errorMsg = string.Empty;

                    //请求模板发送
                    string postUrl = $"https://api.weixin.qq.com/cgi-bin/message/template/send?access_token={token}";
                    string rtnPost = WebRequestHelper.DoPost(postUrl, strJson);
                    var obj = new { errcode = "", errmsg = "", msgid = "" };
                    var jObj = JsonConvert.DeserializeAnonymousType(rtnPost, obj);
                    if (jObj.errcode == "0")
                    {
                        message = "";
                        return true;
                    }
                    else
                    {
                        message = $"{jObj.errmsg},code:{jObj.errcode}";
                        return false;
                    }

            }
            catch (Exception ex)
            {
                message = $"{ex.Message}";
                return false;
            }
        }


        #endregion
    }
}