public void GetAlert() { DataBaseHelper _db = new DataBaseHelper(Command, CommandType.Text); try { DataSet _ds; _ds = _db.Run(base.ConnectionString, ReturnParams()); _alert.AlertTime = _alertTime; if (_ds.Tables[0].Rows.Count > 0) { _alert.AlertType = _ds.Tables[0].Rows[0]["alertType"].ToString(); _alert.AlertMessage =_ds.Tables[0].Rows[0]["alertMessage"].ToString(); } else { _alert.AlertType = "None"; _alert.AlertMessage = ""; } } catch (Exception ex) { throw new Exception("AlarmasABC.DAL.AlertSelect: " + ex.Message); } finally { _db = null; } }
public void SecurityQuestionDropDownList(IList<SecurityQuestion> _securityQes) { NpgsqlDataReader _dr = null; DataBaseHelper _db = new DataBaseHelper(Command, CommandType.Text); try { _dr = _db.ExecuteReader(base.ConnectionString); _securityQes.Add(new SecurityQuestion(0, "Select Question")); while (_dr.Read()) { _securityQes.Add(new SecurityQuestion(int.Parse(_dr[0].ToString()), _dr[1].ToString())); } } catch (Exception ex) { throw new Exception(" :: " + ex.Message); } finally { if(_dr!=null) { _db = null; _dr=null; _securityQes = null; } } }
public void CreateUnitNameDropDownList(IList<Units> _Units) { NpgsqlDataReader _dr = null; DataBaseHelper _db = new DataBaseHelper(Command, CommandType.Text); try { _dr = _db.ExecuteReader(returnParam()); _Units.Add(new Units(0, "Create Unit")); while (_dr.Read()) { _Units.Add(new Units(int.Parse(_dr[0].ToString()), _dr[1].ToString())); } } catch (Exception ex) { throw new Exception(" :: " + ex.Message); } finally { if (_dr != null) { _db = null; _dr = null; _Units = null; } } }
public void UserGroupDropDownList(IList<UserGroup> _userGroup) { NpgsqlDataReader _dr = null; DataBaseHelper _db = new DataBaseHelper(Command, CommandType.Text); try { _dr = _db.ExecuteReader(returnParam()); _userGroup.Add(new UserGroup(0, "Select Group")); while (_dr.Read()) { _userGroup.Add(new UserGroup(int.Parse(_dr[0].ToString()), _dr[1].ToString())); } } catch (Exception ex) { throw new Exception(" :: " + ex.Message); } finally { if (_dr != null) { _db = null; _dr = null; _userGroup = null; } } }
public void Add(SqlTransaction transaction) { _orderInsertDataParameters = new OrderInsertDataParameters(Orders); DataBaseHelper dbHelper = new DataBaseHelper(StoredProcedureName); object id = dbHelper.RunScalar(transaction, _orderInsertDataParameters.Parameters); Orders.OrderId = int.Parse(id.ToString()); }
public void Add() { _showcaseInsertDataParameters = new ShowcaseInsertDataParameters(PageId, ProductId); DataBaseHelper dbHelper = new DataBaseHelper(StoredProcedureName); dbHelper.Parameters = _showcaseInsertDataParameters.Parameters; dbHelper.Run(); }
public void TimeZoneDropDownList(IList<RptTimeZone> _TimeZone) { NpgsqlDataReader _dr = null; DataBaseHelper _db = new DataBaseHelper(Command, CommandType.Text); try { _dr = _db.ExecuteReader(base.ConnectionString); _TimeZone.Add(new RptTimeZone(0, "Select Time Zone")); while (_dr.Read()) { _TimeZone.Add(new RptTimeZone(float.Parse(_dr[0].ToString()), _dr[1].ToString())); } } catch (Exception ex) { throw new Exception(" :: " + ex.Message); } finally { if (_dr != null) { _db = null; _dr = null; _TimeZone = null; } } }
public void UnitTypeDropDownList(IList<UnitType> _unitType) { NpgsqlDataReader _dr = null; DataBaseHelper _db = new DataBaseHelper(Command, CommandType.Text); try { _dr = _db.ExecuteReader(returnParams()); _unitType.Add(new UnitType(0, "Select Unit Category")); while (_dr.Read()) { _unitType.Add(new UnitType(int.Parse(_dr[0].ToString()), _dr[1].ToString())); } } catch (Exception ex) { throw new Exception(" :: " + ex.Message); } finally { if (_dr != null) { _db = null; _dr = null; _unitType = null; } } }
public void LoadListedUnitGroups(IList<UserGroup> _GLists) { Command = @"SELECT typeID,typeName FROM tblUnitType WHERE typeID IN" + @" (SELECT DISTINCT t.typeID FROM tblUnitType t INNER JOIN tblUnits u" + @" ON t.typeID = u.typeID AND u.unitID IN (SELECT unitID FROM tblUnitUserWise" + @" WHERE uID = :uID)) AND comID = :comID AND coalesce(isDelete,'0') != '1'" + @" ORDER BY typeName ASC;"; NpgsqlDataReader _dr = null; DataBaseHelper _db = new DataBaseHelper(Command, CommandType.Text); try { _dr = _db.ExecuteReader(returnUnitGroupParam()); // _GLists.Add(new UserGroup(0, )); while (_dr.Read()) { _GLists.Add(new UserGroup(int.Parse(_dr[0].ToString()), _dr[1].ToString())); } } catch (Exception ex) { throw new Exception(" :: " + ex.Message); } finally { if (_dr != null) { _db = null; _dr = null; _GLists = null; } } }
public void Delete() { _shoppingCartByGuidDeleteDataParameters = new ShoppingCartByGuidDeleteDataParameters(ShoppingCart); DataBaseHelper dbHelper = new DataBaseHelper(StoredProcedureName); dbHelper.Parameters = _shoppingCartByGuidDeleteDataParameters.Parameters; dbHelper.Run(); }
public void IconLoadOnDataList(IList<IconSetup> _ICLists) { NpgsqlDataReader _dr = null; DataBaseHelper _db = new DataBaseHelper(Command, CommandType.Text); try { _dr = _db.ExecuteReader(base.ConnectionString); // _GLists.Add(new UserGroup(0, )); while (_dr.Read()) { _ICLists.Add(new IconSetup(int.Parse(_dr[0].ToString()),_dr[1].ToString(), _dr[2].ToString())); } } catch (Exception ex) { throw new Exception(" :: " + ex.Message); } finally { if (_dr != null) { _db = null; _dr = null; _ICLists = null; } } }
public int saveSchemePermission() { DataSet _ds = new DataSet(); try { DataBaseHelper _db =new DataBaseHelper(Command, CommandType.StoredProcedure); _ds= _db.Run(base.ConnectionString,returnParam()); } catch (Exception ex) { throw new Exception(ex.Message.ToString()); } finally { } if (_ds.Tables[0].Rows.Count > 0) { return int.Parse(_ds.Tables[0].Rows[0][0].ToString()); } else { return -1; } }
public void SelectData1() { Command = "SELECT unitName,lat,long,deviceID,CAST(velocity * 0.621 AS int) AS velocity," + "recTime,recTimeRevised,iconName FROM unitRecords WHERE deviceID = :deviceID" + " AND comID = :comID AND recTime BETWEEN :recTime AND (SELECT MAX(recTime) FROM tblGPRS" + " WHERE deviceID = :deviceID AND msgType != 4) ORDER BY recTime DESC LIMIT 25;"; DataBaseHelper _db = new DataBaseHelper(Command, CommandType.Text); NpgsqlDataReader _dr = null; try { _dr = _db.ExecuteReader(ReturnParams1()); while(_dr.Read()) { this._mapdata.Add(new MapData( _dr[0].ToString(), decimal.Parse(_dr[1].ToString()), decimal.Parse(_dr[2].ToString()), _dr[3].ToString(),_dr[4].ToString(),_dr[5].ToString(),_dr[6].ToString(),_dr[7].ToString())); } } catch (Exception ex) { throw new Exception("DAL::SelectData1::"+ex.Message); } finally { _dr = null; } }
public void GetReverseGeocoding() { DataBaseHelper _db = new DataBaseHelper(Command, CommandType.StoredProcedure); DataSet ds = new DataSet(); try { // Use the reverse geocoding database connection string. VERY IMPORTANT so // Reverse Geocoding will work! ds = _db.Run(base.RGConnectionString, ReturnParams()); if (ds.Tables[0].Rows.Count > 0) { // Get the postal code postalCode = ds.Tables[0].Rows[0]["postalCode"].ToString(); // Get the city city = ds.Tables[0].Rows[0]["placeName"].ToString(); // Get the county county = ds.Tables[0].Rows[0]["adminName2"].ToString(); // Get the state state = ds.Tables[0].Rows[0]["adminName1"].ToString(); // Get the country country = ds.Tables[0].Rows[0]["countryCode"].ToString(); } } catch (Exception ex) { throw new Exception(" AlarmasABC.DAL.ReverseGeocoding:: " + ex.Message); } finally { _db = null; } }
public void FuelDropDownList(IList<Fuel> _fuels) { NpgsqlDataReader _dr = null; DataBaseHelper _db = new DataBaseHelper(Command, CommandType.Text); try { _dr = _db.ExecuteReader(base.ConnectionString); _fuels.Add(new Fuel(0, "Select Fuel")); while (_dr.Read()) { _fuels.Add(new Fuel(int.Parse(_dr[0].ToString()), _dr[1].ToString())); } } catch (Exception ex) { throw new Exception(" :: " + ex.Message); } finally { if(_dr!=null) { _db = null; _dr=null; _fuels = null; } } }
public DataSet Get() { DataSet ds; DataBaseHelper dbHelper = new DataBaseHelper(StoredProcedureName); ds = dbHelper.Run(ConnectionString); return ds; }
public void getMapData() { NpgsqlDataReader _dr = null; DataBaseHelper _db = new DataBaseHelper(Command, CommandType.StoredProcedure); try { _dr = _db.ExecuteReader(returnParams()); while (_dr.Read()) { this._mapdata.Add(new MapData(_dr[0].ToString(), decimal.Parse(_dr[1].ToString()), decimal.Parse(_dr[2].ToString()), _dr[3].ToString(),_dr[4].ToString(),_dr[5].ToString(),_dr[6].ToString(),_dr[7].ToString())); } } catch (Exception ex) { throw new Exception(" DAL::Select: MainMapData "+ex.Message); } finally { _dr=null; _db=null; } }
public void Add() { _shoppingCartInsertDataParameters = new ShoppingCartInsertDataParameters(ShoppingCart); DataBaseHelper dbHelper = new DataBaseHelper(StoredProcedureName); dbHelper.Parameters = _shoppingCartInsertDataParameters.Parameters; dbHelper.Run(); }
public void LoadGroupListBox(IList<UserGroup> _GLists) { NpgsqlDataReader _dr = null; DataBaseHelper _db = new DataBaseHelper(Command, CommandType.Text); try { _dr = _db.ExecuteReader(returnParam()); while (_dr.Read()) { _GLists.Add(new UserGroup(int.Parse(_dr[0].ToString()), _dr[1].ToString())); } } catch (Exception ex) { throw new Exception(" :: " + ex.Message); } finally { if (_dr != null) { _db = null; _dr = null; _GLists = null; } } }
public void PatternDropDownList(IList<Pattern> _Pattern) { NpgsqlDataReader _dr = null; DataBaseHelper _db = new DataBaseHelper(Command, CommandType.Text); try { _dr = _db.ExecuteReader(returnParam()); _Pattern.Add(new Pattern(0, "Select Pattern")); while (_dr.Read()) { _Pattern.Add(new Pattern(int.Parse(_dr[0].ToString()), _dr[1].ToString())); } } catch (Exception ex) { throw new Exception(" :: " + ex.Message); } finally { if(_dr!=null) { _db = null; _dr=null; _Pattern = null; } } }
public void SchemeDropDownList(IList<SecurityScheme> _Scheme) { NpgsqlDataReader _dr = null; DataBaseHelper _db = new DataBaseHelper(Command, CommandType.Text); try { _dr = _db.ExecuteReader(GetParams()); _Scheme.Add(new SecurityScheme(0, "Select Scheme")); while (_dr.Read()) { _Scheme.Add(new SecurityScheme(int.Parse(_dr[0].ToString()), _dr[1].ToString())); } } catch (Exception ex) { throw new Exception(" :: " + ex.Message); } finally { if (_dr != null) { _db = null; _dr = null; _Scheme = null; } } }
public void Update() { _newsletterUpdateDataParameters = new NewsletterUpdateDataParameters(EndUser); DataBaseHelper dbHelper = new DataBaseHelper(StoredProcedureName); dbHelper.Parameters = _newsletterUpdateDataParameters.Parameters; dbHelper.Run(); }
public void Update() { _productUpdateDataParameters = new ProductUpdateDataParameters(Product, StockLevels); DataBaseHelper dbHelper = new DataBaseHelper(StoredProcedureName); dbHelper.Parameters = _productUpdateDataParameters.Parameters; dbHelper.Run(); }
public DataSet Get() { DataSet ds; ProductsSelectByNameDataParameters _productSelectByNameParameters = new ProductsSelectByNameDataParameters(ProductName, ProductColourId); DataBaseHelper dbHelper = new DataBaseHelper(StoredProcedureName); ds = dbHelper.Run(base.ConnectionString, _productSelectByNameParameters.Parameters); return ds; }
public DataSet Get() { DataSet ds; ShoppingCartSelectDataParameters _shoppingCartSelectDataParameters = new ShoppingCartSelectDataParameters(ShoppingCart); DataBaseHelper dbHelper = new DataBaseHelper(StoredProcedureName); ds = dbHelper.Run(base.ConnectionString, _shoppingCartSelectDataParameters.Parameters); return ds; }
public DataSet Get() { DataSet ds; EndUserLoginSelectDataParameters _endUserSelectDataParameters = new EndUserLoginSelectDataParameters(EndUser); DataBaseHelper dbHelper = new DataBaseHelper(StoredProcedureName); ds = dbHelper.Run(base.ConnectionString, _endUserSelectDataParameters.Parameters); return ds; }
public DataSet Get() { DataSet ds; ShowcaseSelectByPageIdDataParameters _shocaseSelectByPageIdParameters = new ShowcaseSelectByPageIdDataParameters(PageId); DataBaseHelper dbHelper = new DataBaseHelper(StoredProcedureName); ds = dbHelper.Run(base.ConnectionString, _shocaseSelectByPageIdParameters.Parameters); return ds; }
public DataSet Get() { DataSet ds; ProductSelectByProductIdDataParamters _productSelectByIdParameters = new ProductSelectByProductIdDataParamters(Product); DataBaseHelper dbHelper = new DataBaseHelper(StoredProcedureName); ds = dbHelper.Run(base.ConnectionString, _productSelectByIdParameters.Parameters); return ds; }
public DataSet Get() { DataSet ds; ProductsSelectBySubCategoryIdDataParameters _productSelectPatameters = new ProductsSelectBySubCategoryIdDataParameters(SubCategoryId); DataBaseHelper dbHelper = new DataBaseHelper(StoredProcedureName); ds = dbHelper.Run(ConnectionString, _productSelectPatameters.Parameters); return ds; }
public DataSet Get() { DataSet ds; SubCategorySelectByProductCategoryIdDataParameters _subCategorySelectByProductCategoryIdParameters = new SubCategorySelectByProductCategoryIdDataParameters(SubCategory); DataBaseHelper dbHelper = new DataBaseHelper(StoredProcedureName); ds = dbHelper.Run(base.ConnectionString, _subCategorySelectByProductCategoryIdParameters.Parameters); return ds; }
private int NoLogin(out string str_error, string mobile) { str_error = ""; string sql = "", error = ""; try { sql = string.Format("select user_id from sys_user where user_mobile = '{0}' and delete_mark = 0", mobile); int id = Convert.ToInt32(DataBaseHelper.ExecuteScalar(sql, out error)); if (error == "") { return(id); } else { throw new Exception(error); } } catch (Exception ex) { SystemLog.WriteErrorLog("获取用户id失败_" + mobile, "1010", ex.Message, ex.StackTrace); } return(0); }
public override void Execute(Update update, TelegramBotClient client, Exception e = null) { var Message = update.Message ?? update.CallbackQuery.Message; try { var eventId = update.CallbackQuery.Data.Split(":"); var id = eventId.Length - 1; DataBaseHelper db = new DataBaseHelper(); var ev = db.GetAllEvents().Where(x => x.Id == Convert.ToInt32(eventId[id])).FirstOrDefault(); var users = db.GetEventSubs(ev.Id); string list = $"Общее количество записавшихся на ваше событие: {users.Count}"; foreach (var u in users) { list = list + $"\n{u.Login}"; } if (users.Count == 0) { list = "Еще никто не записался на ваше событие, расскажите о нем друзьям!"; } client.SendTextMessageAsync(Message.Chat.Id, list, parseMode: default, false, false, 0);
public bool InsertUpdateAudition(AuditionModel entity) { var result = false; try { Dictionary <string, object> inputparam = new Dictionary <string, object>(); inputparam.Add("Id", entity.AuditionId); inputparam.Add("CityID", entity.CityId); inputparam.Add("Venue", entity.Venue); inputparam.Add("Day", entity.Day); if (string.IsNullOrWhiteSpace(entity.PlaceId)) { inputparam.Add("PlaceId", DBNull.Value); } else { inputparam.Add("PlaceId", entity.PlaceId); } inputparam.Add("Date", entity.Date); inputparam.Add("MaxDate", DateTime.UtcNow);//entity.MaxDate inputparam.Add("IsActive", entity.IsActive); inputparam.Add("CreatedBy", entity.CreatedBy); inputparam.Add("CreatedDate", entity.CreatedDate); result = DataBaseHelper.ExecuteStoreProcedure <int>(connectionString, "dbo.prcInsertUpdateAudition", inputparam).FirstOrDefault() > 0; } catch (Exception ex) { LogService.Log("Error in InsertUpdateAudition of AuditionDataAccess: " + ex.Message); throw ex; } return(result); }
public Response ChangePassword(InValidateUser input) { string connectionString = DataBaseHelper.GetConnectionString("DLG"); Response response = new Response(); try { var ora = new OracleServer(connectionString); var pi_User = new OracleParameter("fa_cedula", OracleDbType.Double, input.userID, ParameterDirection.Input); var pi_Password = new OracleParameter("fa_clave", OracleDbType.Varchar2, input.password, ParameterDirection.Input); var po_ErrorCode = new OracleParameter("fa_Error", OracleDbType.Double, ParameterDirection.Output); var po_ErrorMessage = new OracleParameter("fa_Descripcion_Error", OracleDbType.Varchar2, ParameterDirection.Output); po_ErrorMessage.Size = 100; ora.AddParameter(pi_User); ora.AddParameter(pi_Password); ora.AddParameter(po_ErrorCode); ora.AddParameter(po_ErrorMessage); ora.ExecuteProcedureNonQuery("BBS_PORTAL_F_CAMBIO_CLAVE"); response.errorCode = ora.GetParameter("fa_Error").ToString(); response.errorMessage = ora.GetParameter("fa_Descripcion_Error").ToString(); ora.Dispose(); } catch (Exception ex) { throw new Exception("ChangePassword", ex); } return(response); }
public static Ventas traerPorId(int idVenta) { if (idVenta > 0) { Dictionary <string, object> parametros = new Dictionary <string, object>(); parametros.Add("@idVenta", idVenta); DataTable dt = new DataTable(); DataBaseHelper.Fill(dt, "dbo.SPSVentas", parametros); Ventas oResultado = null; foreach (DataRow item in dt.Rows) { oResultado = new Ventas(item); break; } return(oResultado); } else { throw new Exception("Id inválido"); } }
private void ImportClick(object sender, EventArgs e) { try { if (this.openFileDialog1.ShowDialog() == DialogResult.OK) { string file = this.openFileDialog1.FileName; string error = string.Empty; ReadOnlyCollection <PropertyInfo> collection = new ContactDO().GetOrderedProperties(); DataTable data = ExcelHelper.Import(file, collection, ref error); this.ShowErrorIfNotEmpty(error); error = string.Empty; List <ContactDO> contacts = DomainObjectHelper.GetDomainObjectFromDataTable <ContactDO>(data, ref error); this.ShowErrorIfNotEmpty(error); foreach (ContactDO record in contacts) { error = string.Empty; DataBaseHelper.Insert(Program.ConnectionString, ContactDO.TableName, record, ref error); this.ShowErrorIfNotEmpty(error); } this.ReloadContacts(); } } catch (Exception ex) { MessageBox.Show("Error while trying to import data: " + ex.Message); } }
static internal void UpdateInTable(string tableName, System.Collections.Generic.Dictionary <string, string> Value, System.Collections.Generic.Dictionary <string, string> where) { string Values = DataBaseHelper.UpdateFormateHelper(Value); string StringWhere = DataBaseHelper.WhereFormateHelper(where); using (SqlConnection connection = DataBaseConnect.Connect()) { try { connection.Open(); SqlCommand command = new SqlCommand( string.Format(DataBaseConstants.UpdQuery, tableName, Values, StringWhere), connection); command.ExecuteNonQuery(); } catch (System.Exception e) { connection.Close(); throw e; } } }
private string CountUrinalysisCommunity(string regionCode, string startTime, string endTime) { string result = ""; string sql = "", error = ""; if (!string.IsNullOrWhiteSpace(regionCode)) { sql = string.Format("exec CountLevel1DepUrinalysis '{0}', '{1}', '{2}'", regionCode, startTime, endTime); DataTable dt = DataBaseHelper.ExecuteTable(sql, out error); if (error == "") { List<StatisticForUrinalysis> list = new List<StatisticForUrinalysis>(); foreach (DataRow dr in dt.Rows) { StatisticForUrinalysis info = new StatisticForUrinalysis(); info.name = dr[0].ToString(); info.total = Convert.ToInt32(dr[1]); info.check = Convert.ToInt32(dr[2]); info.uncheck = Convert.ToInt32(dr[3]); list.Add(info); } result = JsonConvert.SerializeObject(list); } else { throw new Exception(error); } } else { throw new Exception("参数错误 "); } return result; }
/// <summary> /// 创建数据库对象 /// </summary> /// <param name="dbType"></param> /// <param name="dataBaseInfo"></param> /// <returns></returns> private static IDatabase CreateDatabaseInstance(Type dbType, DataBaseInfoEntity dataBaseInfo) { if (dataBaseInfo == null) { return(null); } string connString = DataBaseHelper.CreateConnectionString(dataBaseInfo); ConstructorInfo constructor = dbType.GetConstructor(Type.EmptyTypes); if (constructor == null) { return(null); } IDatabase database = constructor.Invoke(null) as IDatabase; if (database != null && !string.IsNullOrEmpty(connString)) { database.ConnectionString = connString; } return(database); }
public override bool OnOptionsItemSelected(IMenuItem item) { ConfiguratorPathBD configurator = new ConfiguratorPathBD(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal)); if (item.TitleFormatted.ToString() == "guardar") { var posicionesSelected = venuesListView.CheckedItemPositions; for (int i = 0; i < posicionesSelected.Size(); i++) { if (posicionesSelected.ValueAt(i)) { var textoCelda = venuesListView.Adapter.GetItem(posicionesSelected.KeyAt(i)).ToString(); var venueSelected = lstVenues.Where(v => textoCelda.Contains(v.name)).First(); LugarDeInteres lugarDeInteres = new LugarDeInteres(venueSelected, viaje.Id); if (DataBaseHelper.Insertar(ref lugarDeInteres, configurator.RutaCompleta)) { Toast.MakeText(this, "Item insertado", ToastLength.Long).Show(); } } } } return(base.OnOptionsItemSelected(item)); }
/// <summary> /// Se utiliza para guardar un registro nuevo o existente. /// </summary> public void guardar() { List <SqlParameter> parametros = new List <SqlParameter>(); parametros.Add(new SqlParameter("@nombre", nombre)); parametros.Add(new SqlParameter("@descripcion", descripcion)); try { if (idRol > 0) { //Update parametros.Add(new SqlParameter("@idRol", idRol)); if (DataBaseHelper.ExecuteNonQuery("dbo.SPURoles", parametros.ToArray()) == 0) { throw new Exception("No se actualizo el registro"); } } else { // Insert if (DataBaseHelper.ExecuteNonQuery("dbo.SPIRoles", parametros.ToArray()) == 0) { throw new Exception("No se creo el registro "); } } } catch (Exception ex) { #if DEBUG throw new Exception(ex.Message); #else throw new Exception("Ha ocurrido un error con la base de dato "); #endif } }
/// <summary> /// 根据病历ID获取病历信息 /// </summary> /// <param name="strDoctorGid"></param> /// <param name="strPatientGid"></param> /// <param name="strSickness"></param> /// <returns></returns> public List <TMedicHisDetailInfo> GetMedicalHisDetailByHisgid(String strHisGid) { List <TMedicHisDetailInfo> lstDetailInfo = new List <TMedicHisDetailInfo>(); String strSql = "SELECT * FROM T_Medical_HisDetail WHERE HIS_ID = @HIS_ID AND STATUS =1 "; ParamMap param = ParamMap.newMap(); param.setParameter("HIS_ID", strHisGid); List <TMedicalHisDetail> lstdetail = DataBaseHelper.FindBySql <TMedicalHisDetail>(strSql, param); if (lstdetail != null && lstdetail.Count > 0) { foreach (TMedicalHisDetail detail in lstdetail) { TMedicHisDetailInfo info = new TMedicHisDetailInfo(); info.DetailGid = detail.DetailGid; info.HisID = detail.HisID; info.DetailTitle = detail.DetailTitle; info.DetailDescribe = detail.DetailDescribe; info.CreateUser = detail.CreateUser; info.CreateTime = detail.CreateTime; info.UpdateTime = detail.UpdateTime; info.Status = detail.Status; strSql = "select * from T_Medical_HisDetailPic where Detail_Gid=@Detail_Gid and status =1"; ParamMap paramDetail = ParamMap.newMap(); param.setParameter("Detail_Gid", detail.DetailGid); info.PicList = DataBaseHelper.FindBySql <TMedicalHisDetailPic>(strSql, paramDetail); lstDetailInfo.Add(info); } return(lstDetailInfo); } else { return(null); } }
public void Update(Product entity) { var queryString = ""; if (entity.CatalogId == 0) { queryString = String.Format("UPDATE {0} SET Name = '{1}', Description = '{2}' WHERE Id = {3}", c_productsDatabaseName, entity.Name, entity.Description, entity.Id); } else { queryString = String.Format("UPDATE {0} SET CatalogId = '{1}', Name = '{2}', Description = '{3}' WHERE Id = {4}", c_productsDatabaseName, entity.CatalogId, entity.Name, entity.Description, entity.Id); } DataBaseHelper.ExecuteCommand(queryString); }
public IActionResult Save([FromBody] InitializationModel model) { try { var builder = new ConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory()).AddJsonFile("appsettings.json", true, reloadOnChange: true); var config = builder.Build(); config["DataBase:ConnectionString"] = DataBaseHelper.GetDbConfiguration(model.DataServerName, model.DataAccountName, model.DataPassword, model.DatabaseName, model.CommandTimeOut); config["Initialization:IsInitialization"] = "true"; WriteJson("DataBase:ConnectionString", config["DataBase:ConnectionString"], config["Initialization:IsInitialization"]); if (model.OrganizationBases != null) { DataBaseOptions options = new DataBaseOptions() { ConnectionString = config["DataBase:ConnectionString"] }; IOrganizationBaseRepository organizationBaseRepository = new OrganizationBaseRepository(options); IOrganizationBaseService _organizationBaseService = new Organization.OrganizationBaseService(organizationBaseRepository); model.OrganizationBases.ForEach(x => { _organizationBaseService.Update(x); }); } return(new JsonResult(new JsonResultObject() { IsSuccess = true })); } catch (Exception ex) { return(new JsonResult(new JsonResultObject() { IsSuccess = false, Content = ex.Message })); } }
public static List <Cliente> traerTodos(bool soloActivos = false) { List <SqlParameter> parametros = new List <SqlParameter>(); //Se pregunta si se deseea traer solo Clientes activos if (soloActivos == true) { parametros.Add(new SqlParameter("@esActivo", true)); } DataTable dt = new DataTable(); DataBaseHelper.Fill(dt, nombreSPS, parametros.ToArray()); List <Cliente> listado = new List <Cliente>(); foreach (DataRow item in dt.Rows) { Cliente fila = new Cliente(item); listado.Add(fila); } return(listado); }
private static List<EstadoPublicacion> GetEstados(string descripcionEstado, DataBaseHelper db) { List<EstadoPublicacion> estados = new List<EstadoPublicacion>(); List<SqlParameter> parameters = new List<SqlParameter>(); SqlParameter descripcionParameter = new SqlParameter("@Descripcion", SqlDbType.NVarChar); descripcionParameter.Value = descripcionEstado; parameters.Add(descripcionParameter); DataTable res = db.GetDataAsTable("MASTERDBA.SP_GetEstados", parameters); foreach (DataRow row in res.Rows) { var estado = new EstadoPublicacion { IdEstado = Convert.ToInt32(row["IdEstado"]), Descripcion = Convert.ToString(row["Descripcion"]) }; estados.Add(estado); } return estados; }
private bool AddVideo(out string str_error, string title, string uploader, string url, int dep = 0) { str_error = ""; try { string sql = string.Format("insert into video_records values('{0}', '{1}', {2}, '{3}', getdate(), 0)", title, uploader, dep, url); string error = ""; int count = DataBaseHelper.ExecuteNonQuery(sql, out error); if (error == "" && count > 0) { return(true); } else { throw new Exception(error); } } catch (Exception ex) { str_error = ex.Message; SystemLog.WriteErrorLog("添加视频失败", "1041", ex.Message, ex.StackTrace); } return(false); }
public static List <Factura> traerTodos(bool soloPorStatus = false, int status = 0) { List <SqlParameter> parametros = new List <SqlParameter>(); //Se pregunta si se deseea traer solo Facturas activos if (soloPorStatus == true) { parametros.Add(new SqlParameter("@status", status)); } DataTable dt = new DataTable(); DataBaseHelper.Fill(dt, nombreSPS, parametros.ToArray()); List <Factura> listado = new List <Factura>(); foreach (DataRow item in dt.Rows) { Factura fila = new Factura(item); listado.Add(fila); } return(listado); }
private bool DeleteVideo(out string str_error, int id) { str_error = ""; try { string sql = string.Format("update video_records set delete_mark = 1 where video_id = {0}", id); string error = ""; int count = DataBaseHelper.ExecuteNonQuery(sql, out error); if (error == "" && count > 0) { return(true); } else { throw new Exception(error); } } catch (Exception ex) { str_error = ex.Message; SystemLog.WriteErrorLog("删除视频失败", "1043", ex.Message, ex.StackTrace); } return(false); }
public object LoadStoredProcedureMenu(dynamic obj) { Dictionary <string, object> result = new Dictionary <string, object>(); string strSql = "存储过程"; Database db = DataBaseHelper.CreateDataBase(); DataTable dt; using (DbConnection conn = db.CreateConnection()) { if (conn.State == ConnectionState.Closed) { conn.Open(); } dt = db.ExecuteDataSet(CommandType.StoredProcedure, strSql).Tables[0]; } ArrayList list = FormatJsonExtension.DataTableArrayList(dt); result.Add("code", "200"); result.Add("msg", "ok"); result.Add("data", list); result.Add("count", list.Count); return(result); }
public static Rol buscarPorID(int idRol) { List <SqlParameter> parametros = new List <SqlParameter>(); parametros.Add(new SqlParameter("@idRol", idRol)); DataTable dt = new DataTable(); try { DataBaseHelper.Fill(dt, "dbo.SPRoles", parametros.ToArray()); Rol resultado = null; foreach (DataRow fila in dt.Rows) { resultado = new Rol(fila); break; } if (resultado == null) { throw new Exception("No se han Encontrado Coincidencia"); } return(resultado); } catch (Exception ex) { #if DEBUG throw new Exception(ex.Message); #else throw new Exception("Ha ocurrido un error con la base de dato "); #endif } }
/// <summary> /// 查询全省行政区域的初始用能权 /// </summary> /// <param name="CATEGORY">查询类别</param> /// <param name="REGIONCODE">行政区域code</param> /// <param name="EXECUTEDATE">执行时间</param> /// <param name="page">页数</param> /// <param name="rowpage">行数</param> /// <returns>返回2种情况(行政区,数量;企业,数量)</returns> public static Dictionary <String, Object> SearchCS(string CATEGORY) { List <EnergyConsumptionModel> oList = new List <EnergyConsumptionModel>(); //创建对象和变量。 int toalCount; string col = string.Empty; SqlPage oPage = new SqlPage(); //new 分页查询对象 string strWhere = string.Empty; //where条件 string strSql = string.Empty; //sql语句 string strCol = string.Empty; //查询的字段 string strGb = string.Empty; //group by 分组 string strOb = string.Empty; //order by 排序 ModelHandler <EnergyConsumptionModel> opp = new ModelHandler <EnergyConsumptionModel>(); Dictionary <String, Object> result = new Dictionary <string, object>(); DataTable dt = new DataTable(); //new DataTable对象 Database dbbase = DataBaseHelper.CreateDataBase(DataBaseHelper.typedb.zlyjgisdb); using (DbConnection dbconn = dbbase.CreateConnection()) using (DbCommand cmd = dbconn.CreateCommand()) { if (dbconn.State != ConnectionState.Open) { dbconn.Open(); } try { logs.Info("555555"); return(result); } catch (Exception ex) { JNJC.Utility.Help.HelpUtils.ErrorLog("初始用能权-加载GRID数据错误! ||" + ex.ToString()); return(result); } } }
public static void InsertPatient(string patientName, string Gender, DateTime dateOfBirth, long number, string email, string address, string department, string consultant, int age, string typeOfConsultation, string connectionString) { List <SqlParameter> sqlParameters = new List <SqlParameter>(); sqlParameters.Add(new SqlParameter { ParameterName = "@PatientName", DbType = DbType.String, Value = patientName }); sqlParameters.Add(new SqlParameter { ParameterName = "@Gender", DbType = DbType.String, Value = Gender }); sqlParameters.Add(new SqlParameter { ParameterName = "@DateOfBirth", DbType = DbType.DateTime, Value = dateOfBirth }); sqlParameters.Add(new SqlParameter { ParameterName = "@Number", DbType = DbType.Int64, Value = number }); sqlParameters.Add(new SqlParameter { ParameterName = "@Email", DbType = DbType.String, Value = email }); sqlParameters.Add(new SqlParameter { ParameterName = "@Address", DbType = DbType.String, Value = address }); sqlParameters.Add(new SqlParameter { ParameterName = "@Department", DbType = DbType.String, Value = department }); sqlParameters.Add(new SqlParameter { ParameterName = "@Consultant", DbType = DbType.String, Value = consultant }); sqlParameters.Add(new SqlParameter { ParameterName = "@Age", DbType = DbType.Int32, Value = age }); sqlParameters.Add(new SqlParameter { ParameterName = "@TypeOfConsultation", DbType = DbType.String, Value = typeOfConsultation }); DataBaseHelper.GetExecuteNonQueryByStoredProcedure("Patient_Insert", connectionString, sqlParameters); }
public GridPage <List <BnsAmazonReportDetail> > GetAmazonDetailList(DatetimePointPageReq pageReq, int?id) { if (id != null) { FilterNode node = new FilterNode(); node.andorop = "and"; node.binaryop = "eq"; node.key = "BnsAmazonReport.Id"; node.value = id; if (pageReq == null) { pageReq = new DatetimePointPageReq(); } pageReq.query.Add(node); } var res = new GridPage <List <BnsAmazonReportDetail> >() { code = ResCode.Success }; res = DataBaseHelper <BnsAmazonReportDetail> .GetList(_uowProvider, res, pageReq); return(res); }
public MessageControl(Message message) : this() { _selected = false; _messageId = message.Id; userNameLabel.Text = message.Sender.Name; try { avatarBox.Image = DataBaseHelper.DeserializeImage(message.Sender.Picture); } catch { avatarBox.Image = Properties.Resources.DefaultImage; } if (message.Attachs.Any()) { ShowAttachs(message.Attachs.ToFileList()); } else { HideAttachs(); } messageTextLabel.Text = message.Text; dateLabel.Text = message.Date.ToString("HH:mm:ss"); }
public void InsertarNuevoPedidoConDeliveryExitosamente() { //arrange string horaActual = DateTime.Now.HoraActualFormateada(); PedidoDelivery pedido = new PedidoDelivery( "Grande de muzza delivery (test)", Pedido.EEstado.Preparacion, 200f, true, "Santiago", "Banfield", "CalleFalsa", 123 ); //act DataBaseHelper.InsertarPedido(pedido); string horarioPedidoInsertado = DataBaseHelper.GetPedidoPorHorario(horaActual).HorarioPedido; DataBaseHelper.EliminarPedido(pedido.NombrePedido); //assert Assert.AreEqual(horaActual, horarioPedidoInsertado); }
public string logicAddSum(string strArgument) { double money = 0; string strStartTime = getStartDay(); string strEndTime = getEndDay(); string strBranchCenter = null; string strDataPath = string.Empty; try { string[] strArgumentArray = strArgument.Split('/'); switch (strArgumentArray.Count()) { case 1: money = double.Parse(strArgumentArray[0].Trim()); break; case 2: money = double.Parse(strArgumentArray[0].Trim()); strBranchCenter = strArgumentArray[1].Trim(); break; case 3: money = double.Parse(strArgumentArray[0].Trim()); strStartTime = strArgumentArray[1].Trim().Remove(4) + "-" + strArgumentArray[1].Trim().Substring(4).Remove(2) + "-" + strArgumentArray[1].Trim().Substring(6); strEndTime = strArgumentArray[2].Trim().Remove(4) + "-" + strArgumentArray[2].Trim().Substring(4).Remove(2) + "-" + strArgumentArray[2].Trim().Substring(6); break; case 4: money = double.Parse(strArgumentArray[0].Trim()); strStartTime = strArgumentArray[1].Trim().Remove(4) + "-" + strArgumentArray[1].Trim().Substring(4).Remove(2) + "-" + strArgumentArray[1].Trim().Substring(6); strEndTime = strArgumentArray[2].Trim().Remove(4) + "-" + strArgumentArray[2].Trim().Substring(4).Remove(2) + "-" + strArgumentArray[2].Trim().Substring(6); strBranchCenter = strArgumentArray[3].Trim(); break; default: LogWriter.WriteLogInfo(DateTime.Now.ToString() + ":" + "传入参数:" + strArgument, LOGSTATE.Error); return("传入参数分割出错请重新输入"); } DataBaseHelper dataBase = new DataBaseHelper(); //判断电脑位数,和文件存放位置 if (Environment.Is64BitOperatingSystem) { Microsoft.Win32.RegistryKey key = Microsoft.Win32.Registry.LocalMachine; Microsoft.Win32.RegistryKey subKey = key.OpenSubKey("SOFTWARE\\Wow6432Node\\CDSI"); strDataPath = subKey.GetValue("InstallPath").ToString() + "\\db\\CDSI.mdb"; } else { strDataPath = "D:\\Program Files\\医付通db\\CDSI.mdb"; } if (dataBase.connection(strDataPath)) { string strSelectSQL = string.Empty; switch (strBranchCenter) { case null: strSelectSQL = "SELECT 记账流水号,个人编码,费用总额," + "全自费部分,挂钩自付部分,符合范围部分," + "个人账户支付总额,社保基金支付总额,经办时间," + "清算分中心 FROM JY结算主记录" + " WHERE 经办时间 > #" + strStartTime + "#" + " AND 经办时间 < #" + strEndTime + "#"; break; default: strSelectSQL = "SELECT 记账流水号,个人编码,费用总额," + "全自费部分,挂钩自付部分,符合范围部分," + "个人账户支付总额,社保基金支付总额,经办时间," + "清算分中心 FROM JY结算主记录 WHERE 经办时间 > #" + strStartTime + "#" + " AND 经办时间 < #" + strEndTime + "#" + " AND 清算分中心= \"" + strBranchCenter + "\""; break; } DataTable dtData = dataBase.selectData(strSelectSQL); double dSum = 0; foreach (DataRow row in dtData.Rows) { dSum += Double.Parse(row["费用总额"].ToString()); } if (money > dSum) { DataRow changeRow = dtData.Rows[0]; string strSerialNum = changeRow["记账流水号"].ToString(); money = money - dSum + double.Parse(changeRow["费用总额"].ToString()); string strUPSQL = "UPDATE JY结算主记录 SET 费用总额 = \"" + (float)money + " \",符合范围部分= \"" + (float)money + "\",个人账户支付总额= \"" + (float)money + "\" WHERE 记账流水号=\"" + strSerialNum + "\""; int InfluenceROW = dataBase.excuteSQL(strUPSQL); //写入log 更改的所有信息记录。 LogWriter.WriteLogInfo(changeRow["记账流水号"].ToString() + ":" + changeRow["个人编码"].ToString() + ":" + changeRow["费用总额"].ToString() + ":" + changeRow["全自费部分"].ToString() + ":" + changeRow["挂钩自付部分"].ToString() + ":" + changeRow["符合范围部分"].ToString() + ":" + changeRow["个人账户支付总额"].ToString() + ":" + changeRow["社保基金支付总额"].ToString() + ":" + changeRow["经办时间"].ToString() + ":" + changeRow["清算分中心"].ToString() , LOGSTATE.Event); return("成功,影响行数:" + InfluenceROW + ";影响记账流水号:" + strSerialNum + ";"); } else { double poor = dSum - money; int InfluenceRow = 0; DataTable changeDT = new DataTable(); DataRow changeRow = null; for (int i = 0; poor > 0; i++) { changeRow = dtData.Rows[i]; double totalAmount = double.Parse(changeRow["费用总额"].ToString()); if (poor > totalAmount) { poor -= totalAmount; string strUPSQL = "UPDATE JY结算主记录 SET 费用总额 =0,符合范围部分=0,个人账户支付总额=0 WHERE 记账流水号=\"" + changeRow["记账流水号"].ToString() + "\""; dataBase.excuteSQL(strUPSQL); //写入log 更改的所有信息记录。 LogWriter.WriteLogInfo(changeRow["记账流水号"].ToString() + ":" + changeRow["个人编码"].ToString() + ":" + changeRow["费用总额"].ToString() + ":" + changeRow["全自费部分"].ToString() + ":" + changeRow["挂钩自付部分"].ToString() + ":" + changeRow["符合范围部分"].ToString() + ":" + changeRow["个人账户支付总额"].ToString() + ":" + changeRow["社保基金支付总额"].ToString() + ":" + changeRow["经办时间"].ToString() + ":" + changeRow["清算分中心"].ToString() , LOGSTATE.Event); } else { money = totalAmount - poor; poor = 0; string strUPSQL = "UPDATE JY结算主记录 SET 费用总额 = \"" + (float)money + " \",符合范围部分= \"" + (float)money + "\",个人账户支付总额= \"" + (float)money + "\" WHERE 记账流水号=\"" + changeRow["记账流水号"].ToString() + "\""; dataBase.excuteSQL(strUPSQL); //写入log 更改的所有信息记录。 LogWriter.WriteLogInfo(changeRow["记账流水号"].ToString() + ":" + changeRow["个人编码"].ToString() + ":" + changeRow["费用总额"].ToString() + ":" + changeRow["全自费部分"].ToString() + ":" + changeRow["挂钩自付部分"].ToString() + ":" + changeRow["符合范围部分"].ToString() + ":" + changeRow["个人账户支付总额"].ToString() + ":" + changeRow["社保基金支付总额"].ToString() + ":" + changeRow["经办时间"].ToString() + ":" + changeRow["清算分中心"].ToString() , LOGSTATE.Event); InfluenceRow = i + 1; } } return("成功,影响行数:" + InfluenceRow + ";影响记账流水号:已经保存在Event Log文件夹中,请查看;"); } } else { LogWriter.WriteLogInfo(DateTime.Now.ToString() + ":" + "传入参数:" + strArgument + ";连接数据库出错", LOGSTATE.Error); return("连接数据库出错"); } } catch (System.Security.SecurityException e) { LogWriter.WriteLogInfo(DateTime.Now.ToString() + ":" + "传入参数:" + strArgument + ";" + e.ToString(), LOGSTATE.Error); return("检测到安全时出现异常,是否是杀毒软件拦截"); }catch (ObjectDisposedException e) { LogWriter.WriteLogInfo(DateTime.Now.ToString() + ":" + "传入参数:" + strArgument + ";" + e.ToString(), LOGSTATE.Error); return("查找安装位置出错"); } catch (ArgumentNullException e) { LogWriter.WriteLogInfo(DateTime.Now.ToString() + ":" + "传入参数:" + strArgument + ";" + e.ToString(), LOGSTATE.Error); return("必要参数为空"); }catch (FormatException e) { LogWriter.WriteLogInfo(DateTime.Now.ToString() + ":" + "传入参数:" + strArgument + ";" + e.ToString(), LOGSTATE.Error); return("格式化出错"); }catch (OverflowException e) { LogWriter.WriteLogInfo(DateTime.Now.ToString() + ":" + "传入参数:" + strArgument + ";" + e.ToString(), LOGSTATE.Error); return("内存溢出"); }catch (Exception e) { LogWriter.WriteLogInfo(DateTime.Now.ToString() + ":" + "传入参数:" + strArgument + ";" + e.ToString(), LOGSTATE.Error); throw e; } }
public SignInfo[] QuerySignForApp(out string str_error, int user1, int user2, string type, string state, string time) { List <SignInfo> result = new List <SignInfo>(); str_error = ""; string sql, error; DataTable dt; string where = "s.delete_mark = 0"; try { if (user1 != 0) { where += string.Format(" and user_id1 = {0}", user1); } else if (user2 != 0) { List <int> ids = new List <int>(); sql = string.Format("exec QuerySubUserByUserID {0}, {1}", user2, ConfCenter.ImportantUserRoleLevel); dt = DataBaseHelper.ExecuteTable(sql, out error); if (error == "") { foreach (DataRow dr in dt.Rows) { ids.Add(Convert.ToInt32(dr[0])); } if (ids.Count > 0) { where += string.Format(" and user_id1 in ({0})", string.Join(",", ids)); } } //where += string.Format(" and user_id2 = {0}", user2); } if (!string.IsNullOrWhiteSpace(type)) { where += string.Format(" and sign_type = '{0}'", type); } if (!string.IsNullOrWhiteSpace(state)) { if (state.IndexOf(',') >= 0) { string[] temp = state.Split(','); string w_temp = ""; for (int i = 0; i < temp.Length; i++) { if (w_temp != "") { w_temp += " or "; } w_temp += string.Format("sign_state = '{0}'", temp[i]); } where += " and (" + w_temp + ")"; } else { where += string.Format(" and sign_state = '{0}'", state); } } if (!string.IsNullOrWhiteSpace(time)) { //if(time.IndexOf(' ') >= 0) //{ // time = time.Split(' ')[0]; //} //where += string.Format(" and s.sign_time1 >= '{0} 0:0:0' and s.sign_time1 <= '{0} 23:59:59'", time); where += string.Format(" and s.create_time >= '{0}'", time); } //else //{ // where += string.Format(" and s.create_time >= '{0}' and s.create_time <= '{1}'", DateTime.Now.Year + "-" + DateTime.Now.Month + "-1 0:0:0",DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); //} sql = string.Format(@"select s.sign_id, user_id1, sign_location1, sign_address1, sign_time1, sign_photo, sign_type, sign_state, sign_remark, user_id2, sign_location2, sign_address2, sign_time2, sign_appointment, sign_appointment_time, sign_distance, s.update_time, u1.user_name, u2.user_name from user_sign s left join sys_user u1 on u1.user_id = s.user_id1 and u1.delete_mark = 0 left join sys_user u2 on u2.user_id = s.user_id2 and u2.delete_mark = 0 where {0} order by sign_time1 desc, s.create_time desc", where); dt = DataBaseHelper.ExecuteTable(sql, out error); if (error != "") { throw new Exception(error); } foreach (DataRow dr in dt.Rows) { SignInfo info = new SignInfo(); info.id = Convert.ToInt32(dr[0]); info.user1 = Convert.ToInt32(dr[1]); info.location1 = dr[2].ToString(); info.address1 = dr[3].ToString(); string tmp = dr[4].ToString(); if (tmp != "") { info.time1 = Convert.ToDateTime(tmp).ToString("yyyy-MM-dd HH:mm:ss"); } info.photo = dr[5].ToString(); info.type = dr[6].ToString(); info.state = dr[7].ToString(); info.remark = dr[8].ToString(); info.user2 = Convert.ToInt32(dr[9]); info.location2 = dr[10].ToString(); info.address2 = dr[11].ToString(); tmp = dr[12].ToString(); if (tmp != "") { info.time2 = Convert.ToDateTime(tmp).ToString("yyyy-MM-dd HH:mm:ss"); } info.appointment = dr[13].ToString(); tmp = dr[14].ToString(); if (tmp != "") { info.appointment_time = Convert.ToDateTime(tmp).ToString("yyyy-MM-dd HH:mm:ss"); } tmp = dr[15].ToString(); if (tmp != "") { info.distance = Convert.ToDouble(tmp); } tmp = dr[16].ToString(); if (tmp != "") { info.update_time = Convert.ToDateTime(tmp).ToString("yyyy-MM-dd HH:mm:ss"); } info.user1_name = dr[17].ToString(); info.user2_name = dr[18].ToString(); result.Add(info); } } catch (Exception ex) { str_error = ex.Message; SystemLog.WriteErrorLog("查询签到信息失败", "1104", ex.Message, ex.StackTrace); } return(result.ToArray()); }
public bool UpdateSign(out string str_error, int id, int user1, string location1, string address1, string time1, string photo, string type, string state, string remark, int user2, string location2, string address2, string time2, string appointment) { str_error = ""; string sql, error; string where = ""; try { if (!string.IsNullOrWhiteSpace(location1)) { if (location1 == "4.9E-324,4.9E-324" || location1 == "0,0") { throw new Exception("手机定位失败"); } where += string.Format(", sign_location1 = '{0}'", location1); } if (!string.IsNullOrWhiteSpace(address1)) { where += string.Format(", sign_address1 = '{0}'", address1); } if (!string.IsNullOrWhiteSpace(time1)) { where += string.Format(", sign_time1 = '{0}'", time1); } if (!string.IsNullOrWhiteSpace(photo)) { where += string.Format(", sign_photo = '{0}'", photo); } if (!string.IsNullOrWhiteSpace(state)) { where += string.Format(", sign_state = '{0}'", state); } if (!string.IsNullOrWhiteSpace(location2)) { where += string.Format(", sign_location2 = '{0}'", location2); } if (!string.IsNullOrWhiteSpace(address2)) { where += string.Format(", sign_address2 = '{0}'", address2); } if (!string.IsNullOrWhiteSpace(time2)) { where += string.Format(", sign_time2 = '{0}'", time2); } if (!string.IsNullOrWhiteSpace(appointment)) { where += string.Format(", sign_appointment = '{0}'", appointment); } if (where != "") { where = where.Substring(1); where += ", update_time = getdate()"; } else { throw new Exception("请传入更新参数"); } sql = string.Format("update user_sign set {0} where sign_id = {1} and delete_mark = 0", where, id); int count = DataBaseHelper.ExecuteNonQuery(sql, out error); if (count > 0 && error == "") { //写入操作日志 return(true); } else { throw new Exception(error); } } catch (Exception ex) { str_error = ex.Message; SystemLog.WriteErrorLog("更新签到信息失败", "1104", ex.Message, ex.StackTrace); } return(false); }
public SignInfo[] QuerySign(out string str_error, int leader, int user, int year, int month, string state, int dep) { List <SignInfo> result = new List <SignInfo>(); str_error = ""; string sql, error; DataTable dt; string where = "s.delete_mark = 0"; try { if (user != 0) { where += string.Format(" and user_id1 = {0}", user); } else if (dep != 0) { sql = string.Format(@"select stuff((select ',' + convert(varchar(100),u.user_id ) from sys_user u, sys_dep d, sys_user_dep_relationship t, sys_role r, sys_user_role_relationship t2 where u.user_id = t.user_id and t.dep_id = d.dep_id and u.user_id = t2.user_id and t2.role_id = r.role_id and u.delete_mark = 0 and d.dep_id = {0} and r.role_level = {1} for xml path('')),1,1,'')", dep, ConfCenter.ImportantUserRoleLevel); string userid = DataBaseHelper.ExecuteScalar(sql, out error).ToString(); if (userid != "" && error == "") { where += string.Format(" and user_id1 in ({0})", userid); } } else if (leader != 0) { List <int> ids = new List <int>(); sql = string.Format("exec QuerySubUserByUserID {0}, {1}", leader, ConfCenter.ImportantUserRoleLevel); dt = DataBaseHelper.ExecuteTable(sql, out error); if (error == "") { foreach (DataRow dr in dt.Rows) { ids.Add(Convert.ToInt32(dr[0])); } if (ids.Count > 0) { where += string.Format(" and user_id1 in ({0})", string.Join(",", ids)); } else { where += " and user_id1 = 0"; } } } else { throw new Exception("人员相关查询参数错误"); } if (year != 0 && month != 0) { int nextMonth = 0; int nextYear = 0; if (month == 12) { nextMonth = 1; nextYear = year + 1; } else { nextMonth = month + 1; nextYear = year; } where += string.Format(" and s.create_time >= '{0}-{1}-1 0:0:0' and s.create_time < '{2}-{3}-1 0:0:0'", year, month, nextYear, nextMonth); } if (!string.IsNullOrWhiteSpace(state)) { where += string.Format(" and sign_state = '{0}'", state); } //if (!string.IsNullOrWhiteSpace(mobile)) //{ // where += string.Format(" and u1.user_mobile = '{0}", mobile); //} //if (!string.IsNullOrWhiteSpace(identity)) //{ // where += string.Format(" and u1.user_identity = '{0}'", identity); //} sql = string.Format(@"select s.sign_id, user_id1, sign_location1, sign_address1, sign_time1, sign_photo, sign_type, sign_state, sign_remark, user_id2, sign_location2, sign_address2, sign_time2, sign_appointment, sign_appointment_time, sign_distance, s.update_time, u1.user_name, u2.user_name from user_sign s left join sys_user u1 on u1.user_id = s.user_id1 and u1.delete_mark = 0 left join sys_user u2 on u2.user_id = s.user_id2 and u2.delete_mark = 0 where {0} order by s.create_time desc", where); dt = DataBaseHelper.ExecuteTable(sql, out error); if (error != "") { throw new Exception(error); } foreach (DataRow dr in dt.Rows) { SignInfo info = new SignInfo(); info.id = Convert.ToInt32(dr[0]); info.user1 = Convert.ToInt32(dr[1]); info.location1 = dr[2].ToString(); info.address1 = dr[3].ToString(); string tmp = dr[4].ToString(); if (tmp != "") { info.time1 = Convert.ToDateTime(tmp).ToString("yyyy-MM-dd HH:mm:ss"); } info.photo = dr[5].ToString(); info.type = dr[6].ToString(); info.state = dr[7].ToString(); info.remark = dr[8].ToString(); info.user2 = Convert.ToInt32(dr[9]); info.location2 = dr[10].ToString(); info.address2 = dr[11].ToString(); tmp = dr[12].ToString(); if (tmp != "") { info.time2 = Convert.ToDateTime(tmp).ToString("yyyy-MM-dd HH:mm:ss"); } info.appointment = dr[13].ToString(); tmp = dr[14].ToString(); if (tmp != "") { info.appointment_time = Convert.ToDateTime(tmp).ToString("yyyy-MM-dd HH:mm:ss"); } tmp = dr[15].ToString(); if (tmp != "") { info.distance = Convert.ToDouble(tmp); } tmp = dr[16].ToString(); if (tmp != "") { info.update_time = Convert.ToDateTime(tmp).ToString("yyyy-MM-dd HH:mm:ss"); } info.user1_name = dr[17].ToString(); info.user2_name = dr[18].ToString(); result.Add(info); } } catch (Exception ex) { str_error = ex.Message; SystemLog.WriteErrorLog("查询签到信息失败", "1104", ex.Message, ex.StackTrace); } return(result.ToArray()); }