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());
}