Beispiel #1
0
 /// <summary>
 /// 根据短信ID查询内容
 /// </summary>
 /// <param name="smsID"></param>
 /// <returns></returns>
 public EMSMSQueryVehicle GetSMSQuery(ulong smsID)
 {
     EMSMSQueryVehicle smsve = new EMSMSQueryVehicle ();
     if (!string.IsNullOrEmpty(smsID.ToString()))
     {
         string sCmdText = string.Format(@"select * from gps_smsqueryvehiclesetting where sms_vehicle_set_id={0}", smsID);
         using (DbDataReader sdr = MySqlDB.GetDataReader(CommandType.Text, sCmdText))
         {
             if (sdr != null)
             {
                 while (sdr.Read())
                 {
                      
                     smsve.CreateDate = sdr["CreateDate"].ToDateTime();
                     smsve.CustomerName = sdr["CustomerName"].ToString();
                     smsve.CustomerTelephone = sdr["CustomerTelephone"].ToString();
                     smsve.IsProfessional = sdr["IsProfessional"].ToBoolean();
                     smsve.sms_vehicle_set_id = sdr["sms_vehicle_set_id"].ToULong();
                     smsve.TenantCode = sdr["TenantCode"].ToString();
                     smsve.SMSVehicle = GetSMSVehicleBySmsID(sdr["sms_vehicle_set_id"].ToULong());
                 }
             }
             sdr.Close();
         }
     }
     return smsve;
 }
Beispiel #2
0
        /// <summary>
        /// 修改短信查车
        /// </summary>
        /// <param name="smsQuery"></param>
        /// <returns></returns>
        public void UpdateSmsVehicle(EMSMSQueryVehicle smsQuery)
        {
            MySqlConnection con = new MySqlConnection(MySqlDB.ConnString);
            con.Open();
            MySqlTransaction trans = con.BeginTransaction();
            MySqlCommand cmd = con.CreateCommand();
            cmd.Transaction = trans;
            try
            {
                string sCmdText = string.Format(@"update gps_smsqueryvehiclesetting set TenantCode=@TenantCode,CustomerName=@CustomerName,CustomerTelephone=@CustomerTelephone,CreateDate=@CreateDate,IsProfessional=@IsProfessional where sms_vehicle_set_id=@sms_vehicle_set_id");
                MySqlParameter[] parm = new MySqlParameter[] { 
                new  MySqlParameter("sms_vehicle_set_id",smsQuery.sms_vehicle_set_id),
                new  MySqlParameter("TenantCode",smsQuery.TenantCode),
                new  MySqlParameter("CustomerName",smsQuery.CustomerName),
                new  MySqlParameter("CustomerTelephone",smsQuery.CustomerTelephone),
                new  MySqlParameter("CreateDate",smsQuery.CreateDate),
                new  MySqlParameter("IsProfessional",smsQuery.IsProfessional),
                new  MySqlParameter("LastSendTime",null)
                };
                cmd.CommandText = sCmdText;
                cmd.Parameters.AddRange(parm);
                cmd.ExecuteNonQuery();

                string delsql = string.Format(@"delete from gps_SMSVehicle where sms_vehicle_set_id={0}", smsQuery.sms_vehicle_set_id);
                cmd.CommandText = delsql;
                cmd.ExecuteNonQuery();

                IList<EMSMSVehicle> list = smsQuery.SMSVehicle;
                if (list != null && list.Count > 0)
                {
                    for (int i = 0; i < list.Count; i++)
                    {
                        ulong ismsvehicle_id = GetMaxSMSVehicleID() + ulong.Parse(i.ToString()) + 1;
                        string sTenantCode = smsQuery.TenantCode;
                        string sVehicleCode = list[i].VehicleCode.ToString();
                        string sLicenseNumber = list[i].LicenseNumber;
                        ulong isms_vehicle_set_id = smsQuery.sms_vehicle_set_id;
                        string sInsert = string.Format(@"insert into gps_SMSVehicle(smsvehicle_id,TenantCode,VehicleCode,LicenseNumber,sms_vehicle_set_id) values({0},'{1}','{2}','{3}',{4})", ismsvehicle_id, sTenantCode, sVehicleCode, sLicenseNumber, isms_vehicle_set_id);
                        cmd.CommandText = sInsert;
                        cmd.ExecuteNonQuery();
                    }
                }

                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
            }
            finally
            {
                con.Close();
            }
        }
        public void Add(EMSMSQueryVehicle smsQuery)
        {
            bool exist = IsExistTelephone(smsQuery.CustomerTelephone, null);
            if (exist)
            {
                throw new BusinessException("手机号码己存在,请输入其它号码。");
            }
            //using (IRepository repository = SessionManager.CreateRepository(typeof(EMSMSQueryVehicle)))
            //{

            //    repository.Save(smsQuery);
            //}
            //优化代码
            else
            {
                SMSVehicleDAL dal = new SMSVehicleDAL();
                dal.AddSmsVehicle(smsQuery);
            }
        }
        // 新增
        public ResultContent AddSMSQuery(SmsQueryVehicleParam para)
        {
            ResultContent rc = new ResultContent();
            try
            {
                // 要校验
                IdentityNoService identity = new IdentityNoService();
                EMSMSQueryVehicle entity = new EMSMSQueryVehicle();
                entity.sms_vehicle_set_id = identity.GetSMSQueryVehicleSettingID();
                entity.CreateDate = DateTime.Now;
                entity.TenantCode = para.TenantCode;
                entity.CustomerName = para.CustomerName;
                entity.CustomerTelephone = para.Telphone;
                entity.IsProfessional = false;
                if (!para.vehicleCodelist.IsNullOrEmpty())
                {
                    entity.SMSVehicle = new List<EMSMSVehicle>();
                    for (int i = 0; i < para.vehicleCodelist.Count; i++)
                    {
                        EMSMSVehicle vehicle = new EMSMSVehicle()
                        {
                            smsvehicle_id = identity.GetSMSVehicleID(),
                            VehicleCode = para.vehicleCodelist[i],
                            TenantCode = para.TenantCode,
                            LicenseNumber = para.LicenceNumberlist[i]
                        };

                        entity.SMSVehicle.Add(vehicle);
                    }
                }

                SMSQueryVehicleService sqv = new SMSQueryVehicleService();
                sqv.Add(entity);
                rc.Message = "新增成功!";
                rc.Result = true;
            }
            catch (Exception ex)
            {
                rc.Result = false;
                rc.Message = ex.Message;
                Logger.Error(ex.Message, ex);
            }
            return rc;
        }
Beispiel #5
0
        /// <summary>
        /// 查询短信列表
        /// </summary>
        /// <param name="vehicleCodeList"></param>
        /// <param name="customerName"></param>
        /// <param name="telephone"></param>
        /// <param name="tenantCode"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowCount"></param>
        /// <returns></returns>
        public List<EMSMSQueryVehicle> SMSQueryList(IList<Guid> vehicleCodeList, string customerName, string telephone,
         string tenantCode, int pageIndex, int pageSize, ref int rowCount)
        {
            List<EMSMSQueryVehicle> smsVeList = new List<EMSMSQueryVehicle>();
            rowCount = 0;
            string vehicleCodeStr = "";
            if (vehicleCodeList != null && vehicleCodeList.Count > 0)
            {
                for (int i = 0; i < vehicleCodeList.Count; i++)
                {
                    if (i != vehicleCodeList.Count - 1)
                    {
                        vehicleCodeStr = vehicleCodeStr + "'" + vehicleCodeList[i] + "'" + ",";
                    }
                    else
                    {
                        vehicleCodeStr = vehicleCodeStr + "'" + vehicleCodeList[vehicleCodeList.Count - 1] + "'";
                    }
                }
            }
            else
            {
                vehicleCodeStr = "''";
            }
            string sCmdText = string.Format(@"select * from gps_smsqueryvehiclesetting where TenantCode='{0}' and IsProfessional=false ", tenantCode);
            string sql = string.Format(@"select count(*) from gps_smsqueryvehiclesetting where TenantCode='{0}' and IsProfessional=false ", tenantCode);
            string wheresql = "";
            if (vehicleCodeList != null && vehicleCodeList.Count > 0)
            {
                wheresql = wheresql + string.Format(@"  and sms_vehicle_set_id in (select distinct sms_vehicle_set_id from gps_smsVehicle where vehiclecode in ({0}) ) ", vehicleCodeStr);
            }
            if (customerName != "")
            {
                wheresql = wheresql + string.Format(@" and CustomerName like '%{0}%'", customerName);
            }
            if (telephone != "")
            {
                wheresql = wheresql + string.Format(@" and CustomerTelephone like '%{0}%'", telephone);
            }
            sCmdText = sCmdText + wheresql + string.Format(@" order by CreateDate desc  limit {0},{1}", pageIndex, pageSize);
            sql = sql + wheresql;
            using (DbDataReader sdrCount = MySqlDB.GetDataReader(CommandType.Text, sql))
            {
                if (sdrCount != null)
                {
                    while (sdrCount.Read())
                    {
                        rowCount = rowCount + Convert.ToInt32(sdrCount[0]);
                    }
                }
                sdrCount.Close();
            }
            using (DbDataReader sdr = MySqlDB.GetDataReader(CommandType.Text, sCmdText))
            {
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        EMSMSQueryVehicle smsVe = new EMSMSQueryVehicle();
                        smsVe.CreateDate = sdr["CreateDate"].ToDateTime();
                        smsVe.CustomerName = sdr["CustomerName"].ToString();
                        smsVe.CustomerTelephone = sdr["CustomerTelephone"].ToString();
                        smsVe.IsProfessional = sdr["IsProfessional"].ToBoolean();
                        smsVe.sms_vehicle_set_id = sdr["sms_vehicle_set_id"].ToULong();
                        smsVe.TenantCode = sdr["TenantCode"].ToString();
                        smsVe.SMSVehicle = GetSMSVehicleBySmsID(sdr["sms_vehicle_set_id"].ToULong());
                        smsVeList.Add(smsVe);
                    }
                }
                sdr.Close();
            }

            return smsVeList;
        }
Beispiel #6
0
        /// <summary>
        /// 新增短信查车(gps_smsqueryvehiclesetting,gps_SMSVehicle)
        /// </summary>
        /// <returns></returns>
        public void AddSmsVehicle(EMSMSQueryVehicle smsQuery)
        {
            using (MySqlConnection conn = new MySqlConnection(MySqlDB.ConnString))
            {
                conn.Open();
                MySqlTransaction trans = conn.BeginTransaction();
                MySqlCommand cmd = conn.CreateCommand();
                cmd.Transaction = trans;
                try
                {
                    cmd.CommandText = "INSERT INTO gps_smsqueryvehiclesetting(sms_vehicle_set_id,TenantCode,CustomerName,CustomerTelephone,CreateDate,IsProfessional,LastSendTime) values(@sms_vehicle_set_id,@TenantCode,@CustomerName,@CustomerTelephone,@CreateDate,@IsProfessional,@LastSendTime)";
                    cmd.Parameters.AddRange(new MySqlParameter[] { 
                        new MySqlParameter("sms_vehicle_set_id", smsQuery.sms_vehicle_set_id),
                        new MySqlParameter("TenantCode", smsQuery.TenantCode),
                        new MySqlParameter("CustomerName", smsQuery.CustomerName),
                        new MySqlParameter("CustomerTelephone", smsQuery.CustomerTelephone),
                        new MySqlParameter("CreateDate", smsQuery.CreateDate),
                        new MySqlParameter("IsProfessional", smsQuery.IsProfessional),
                        new MySqlParameter("LastSendTime", null)
                    });
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();

                    if (!smsQuery.SMSVehicle.IsEmpty()) //短信查车子表记录
                    {
                        ulong _MaxSMSVehicleID = GetMaxSMSVehicleID();
                        cmd.CommandText = string.Format("INSERT INTO gps_SMSVehicle(smsvehicle_id,TenantCode,VehicleCode,LicenseNumber,sms_vehicle_set_id) values(@smsvehicle_id,@TenantCode,@VehicleCode,@LicenseNumber,{0})", smsQuery.sms_vehicle_set_id);
                        cmd.Parameters.AddRange(new MySqlParameter[] {
                            new MySqlParameter("smsvehicle_id", _MaxSMSVehicleID),
                            new MySqlParameter("TenantCode", smsQuery.TenantCode),
                            new MySqlParameter("VehicleCode", ""),
                            new MySqlParameter("LicenseNumber", "")
                        });

                        foreach (var item in smsQuery.SMSVehicle)
                        {
                            _MaxSMSVehicleID++;
                            cmd.Parameters["smsvehicle_id"].Value = _MaxSMSVehicleID;
                            cmd.Parameters["VehicleCode"].Value = item.VehicleCode.ToString();
                            cmd.Parameters["LicenseNumber"].Value = item.LicenseNumber;
                            cmd.ExecuteNonQuery();
                        }
                        cmd.Parameters.Clear();
                    }

                    trans.Commit();
                }
                catch (Exception ex)
                {
                    Logger.Error("SMSVehicleDAL.AddSmsVehicle('" + smsQuery.sms_vehicle_set_id + "')错误", ex);
                    try
                    {
                        trans.Rollback();
                    }
                    catch (Exception exRollback)
                    {
                        Logger.Error("SMSVehicleDAL.AddSmsVehicle('" + smsQuery.sms_vehicle_set_id + "')事物回滚错误", exRollback);
                    }
                }
            }
        }
        public void Modify(EMSMSQueryVehicle smsQuery)
        {
            EMSMSQueryVehicle oldSetting = GetSMSQuery(smsQuery.sms_vehicle_set_id);
            bool exist = IsExistTelephone(smsQuery.CustomerTelephone, oldSetting.sms_vehicle_set_id.ToString());
            if (exist)
            {
                throw new BusinessException("手机号码己存在,请输入其它号码。");
            }
            //using (IRepository repository = SessionManager.CreateRepository(typeof(EMSMSQueryVehicle)))
            //{

            //    repository.Update(smsQuery);
            //}
            //代码优化
            else
            {
                SMSVehicleDAL dal = new SMSVehicleDAL();
                dal.UpdateSmsVehicle(smsQuery);
            }
        }