public async Task <ServiceStatusListItem <CategoryItem> > CategoryAllAsync()
{
ServiceStatusListItem <CategoryItem> result = new ServiceStatusListItem <CategoryItem>();
try
{
result.Data = new List <CategoryItem>();
using (var db = new DBCommand(_connectionString, Queries.Categories))
{
result.ReturnValue = await db.ExecuteReaderWhileAsync((dr, rowNumber) =>
{
result.Data.Add(new CategoryItem
{
ID = dr.GetInt("ID"),
Category = dr.GetString("Category"),
Color = dr.GetString("Color")
});
});
}
result.Success = true;
}
catch (Exception ex)
{
result.AddError(ex);
}
return(result);
}
public MeetingModel GetMeetingInfo(long meetingId)
{
var meetingInfo = new MeetingModel();
using (var db = new DBCommand("GetICMeetingsById"))
{
db.AddParameter("@meetingId", meetingId);
var ds = db.ExecuteDataSet().Tables[0];
if (ds.Rows.Count > 0)
{
foreach (System.Data.DataRow r in ds.Rows)
{
meetingInfo.MeetingId = r["MeetingId"].ToLng();
meetingInfo.MeetingDate = r["MeetingDate"].ToShortDateFormatEx();
meetingInfo.MeetingDocLocation = r["MeetingDocLocation"].ToString();
meetingInfo.MeetingStartAt3PM = r["MeetingStartAt3PM"].ToBool();
meetingInfo.MeetingStartTime = r["MeetingStartTime"].ToString();
meetingInfo.MeetingEndTime = r["MeetingEndTime"].ToString();
meetingInfo.OrigNotes1PageInLength = r["OriginalNotes1PageInLength"].ToBool();
meetingInfo.OrigLengthOfNotes = r["OriginalLengthOfNotes"].ToIntEx();
meetingInfo.FinalLengthOfNotes = r["FinalLengthOfNotes"].ToIntEx();
meetingInfo.IsLocked = r["IsLocked"].ToBool();
}
meetingInfo.MeetingAttributes = GetMeetignAttributes(meetingId);
}
}
return(meetingInfo);
}
public async Task <ServiceStatusItem <TodoItem> > TodoToggleAsync(int id)
{
ServiceStatusItem <TodoItem> result = new ServiceStatusItem <TodoItem>();
try
{
//result.Data = new TodoItem();
using (var db = new DBCommand(_connectionString, Queries.Toggle + Queries.Read))
{
db.AddParameterInt("@id", id);
result.ReturnValue = await db.ExecuteReaderWhileAsync((dr, rowNumber) =>
{
result.Data = new TodoItem
{
ID = dr.GetInt("id"),
Date = dr.GetDateTime("date").Date,
Title = dr.GetString("title"),
IDCategory = dr.GetInt("idCategory"),
Category = dr.GetString("category"),
Completed = dr.GetDateTimeNull("completed"),
Modified = dr.GetDateTime("modified"),
Created = dr.GetDateTime("created")
};
});
result.Success = true;
}
}
catch (Exception ex)
{
result.AddError(ex);
}
return(result);
}
public async Task <ServiceStatusItem <TodoItem> > TodoCategoryAsync(int id, int idCategory)
{
ServiceStatusItem <TodoItem> result = new ServiceStatusItem <TodoItem>();
try
{
using (var db = new DBCommand(_connectionString, Queries.UpdateCategory))
{
db.AddParameterInt("@id", id);
db.AddParameterInt("@idCategory", idCategory);
result.ReturnValue = await db.ExecuteReaderWhileAsync((dr, rowNumber) =>
{
result.Data = new TodoItem
{
ID = dr.GetInt("id"),
IDCategory = dr.GetInt("idCategory")
};
});
result.Success = true;
}
}
catch (Exception ex)
{
result.AddError(ex);
}
return(result);
}
public DataTable getDBdata()
{
DBCommand cmd = new DBCommand("select * from table1");
DataTable data = DB.FillDataTable(cmd);
return(data);
}
public async Task <ServiceStatusItem> TodoDeleteAsync(int id)
{
ServiceStatusItem result = new ServiceStatusItem();
try
{
if (id == 0)
{
result.AddError("Ivalid `id`");
}
if (result.Messages.Count > 0)
{
return(result);
}
using (var db = new DBCommand(_connectionString, Queries.Remove))
{
db.AddParameterInt("@id", id);
await db.ExecuteNonQueryAsync();
result.AddSuccess($"Deleted id: {id}");
result.Success = true;
}
}
catch (Exception ex)
{
result.AddError(ex);
}
return(result);
}
public bool Del(int id)
{
DBCommand command = new DBCommand("[PandUser].[DCOSP_DelResto]", true);
command.AddParameter("Id", id);
return(_connection.ExecuteNonQuery(command) == 1);
}
public int IsUsed(int id)
{
DBCommand command = new DBCommand("[PandUser].[DCOSP_ChkResto]", true);
command.AddParameter("Id", id);
return((int)_connection.ExecuteScalar(command));
}
public Restaurant Get(int id)
{
DBCommand command = new DBCommand(Str_Get + " Where [Id] = @Id;");
command.AddParameter("Id", id);
return(_connection.ExecuteReader(command, dr => dr.ToRestaurant()).SingleOrDefault());
}
public bool Upd(User user)
{
if (user is null)
{
throw new NullReferenceException($"Upd User Data empty ({where}) (UPD)");
}
if (EmailIsUsed(user.Email, user.Id))
{
throw new DataException($"Email Is already Used {user.Id} ({where}) UPD)");
}
if (NatRegNbrIsUsed(user.NatRegNbr, user.Id))
{
throw new DataException($"National Register Nbr Is already Used {user.Id} ({where}) UPD)");
}
DBCommand command = new DBCommand("[PandUser].[DCOSP_UpdUser]", true);
command.AddParameter("Id", user.Id);
command.AddParameter("Email", user.Email);
command.AddParameter("NatRegNbr", user.NatRegNbr);
command.AddParameter("LastName", user.LastName);
command.AddParameter("FirstName", user.FirstName);
command.AddParameter("UserStatus", user.UserStatus);
return(_connection.ExecuteNonQuery(command) == 1);
}
public User Add(User user)
{
if (user is null)
{
throw new NullReferenceException($"User Data empty ({where}) (ADD)");
}
if (user.Email.Length == 0 || user.Passwd.Length == 0)
{
throw new DataException("Email &/or Password Data empty (" + where + ") ADD)");
}
if (EmailIsUsed(user.Email, 0))
{
throw new DataException("$Email Is already Used ({where}) ADD)");
}
if (NatRegNbrIsUsed(user.NatRegNbr, 0))
{
throw new DataException($"National Register Nbr Is already Used ({where}) ADD)");
}
DBCommand command = new DBCommand("[PandUser].[DCOSP_AddUser]", true);
command.AddParameter("Email", user.Email);
command.AddParameter("NatRegNbr", user.NatRegNbr);
command.AddParameter("Passwd", user.Passwd);
command.AddParameter("LastName", user.LastName);
command.AddParameter("FirstName", user.FirstName);
user.Id = (int)_connection.ExecuteScalar(command);
user.Passwd = "";
return(user);
}
public void InsertPositionRecommendations(List <PositionRecommendation> recommendations)
{
string login = HttpContext.Current.Request.ServerVariables["Auth_User"];
if (login.IndexOf("\\") != -1)
{
login = login.Split('\\')[1];
}
if (recommendations != null)
{
for (int i = 0; i < recommendations.Count(); i++)
{
using (var db = new DBCommand("InsertPositionRecommendations"))
{
db.AddParameter("@tagSecurityId", recommendations[i].TagSecurityId);
db.AddParameter("@pros", recommendations[i].Pros);
db.AddParameter("@cons", recommendations[i].Cons);
db.AddParameter("@convertAllocation", recommendations[i].ConvertAllocation);
db.AddParameter("@login", login);
db.ExecuteNonQuery();
}
}
}
}
public static void SaveRequestDetails(
string sessionId
, string appRelativeCurrentExecutionFilePath
, string filePath
, string physicalApplicationPath
, string rawUrl
, string userAgent
, bool isAuthenticated
, string machineName
, string authUser
)
{
var oParams = new DBParamCollection
{
{ "@SessionID", sessionId },
{ "@AppRelativeCurrentExecutionFilePath", appRelativeCurrentExecutionFilePath },
{ "@FilePath", filePath },
{ "@PhysicalApplicationPath", physicalApplicationPath },
{ "@RawUrl", rawUrl },
{ "@UserAgent", userAgent },
{ "@IsAuthenticated", isAuthenticated },
{ "@MachineName", machineName },
{ "@AuthUser", authUser }
};
using (
var oCommand = new DBCommand("[dbo].[spINSERT_RequestDetails]", QueryType.StoredProcedure, oParams))
{
oCommand.Execute();
}
}
public static ICase Update(DBCommand cmd, ICase instance, string type, string text)
{
instance.Type = type;
instance.Text = text;
return cmd.Update("Case", instance);
}
public virtual void ExecuteReader()
{
SetUpProviderAndConnection();
using (DBConnection)
{
DBCommand = DBConnection.CreateCommand();
DBCommand.CommandText = QueryString != null?QueryString:"Select * from State";
DBCommand.CommandType = CommandType.Text;
try
{
//Open Connection
DBConnection.Open();
//Execute Reader
DBDataReader = DBCommand.ExecuteReader();
//SE: update this with something usable
var retList = new List <string>();
while (DBDataReader.Read())
{
retList.Add(DBDataReader[0].ToString());
}
}
catch (Exception ex)
{
LogWrapper.Log(ex.Message);
}
}
}
protected override void OnLoad(EventArgs e)
{
base.OnLoad(e);
try {
DBHost host;
DBLane lane;
DBCommand command = null;
GetViewWorkTableDataResponse response;
response = Master.WebService.GetViewWorkTableData(Master.WebServiceLogin,
Utils.TryParseInt32(Request ["lane_id"]), Request ["lane"],
Utils.TryParseInt32(Request ["host_id"]), Request ["host"],
Utils.TryParseInt32(Request ["command_id"]), Request ["command"]);
lane = response.Lane;
host = response.Host;
command = response.Command;
if (lane == null || host == null || command == null)
{
Response.Redirect("index.aspx", false);
return;
}
header.InnerHtml = GenerateHeader(response, lane, host, command);
buildtable.InnerHtml = GenerateLane(response, lane, host, command);
} catch (Exception ex) {
Response.Write(ex.ToString().Replace("\n", "<br/>"));
}
}
protected virtual void Dispose(bool disposing)
{
if (!disposed)
{
if (disposing)
{
if (DBDataReader != null)
{
DBDataReader.Close();
}
if (DBCommand != null)
{
DBCommand.Dispose();
}
if (DBConnection != null)
{
DBConnection.Close();
}
}
disposed = true;
}
}
public static int ExecuteNonQuery(String format, params Object[] args)
{
var cmd = new DBCommand(String.Format(format, args), Connection);
LogVerbose(cmd.CommandText);
return(cmd.ExecuteNonQuery());
}
public UpdateKeuangan(DaftarKeuangan dk, ModelKeuangan ku)
{
InitializeComponent();
conn = DBConnection.dbConnection();
cmd = new DBCommand(conn);
this.dk = dk;
sp = new SmartCardOperation();
if (sp.IsReaderAvailable())
{
}
else
{
MessageBox.Show("Tidak ada reader tersedia, pastikan reader sudah terhubung dengan komputer.", "Error",
MessageBoxButton.OK, MessageBoxImage.Error);
}
DataContext = new ModelKeuangan(ku.id, ku.nama, ku.telp, ku.jenis_kelamin, ku.password, ku.alamat);
if (ku.jenis_kelamin == "Pria")
{
cbJenisKelamin.SelectedIndex = 0;
}
else if (ku.jenis_kelamin == "Wanita")
{
cbJenisKelamin.SelectedIndex = 1;
}
DataContext = ku;
}
public StudentNextOfKinAddEdit(Student student, DBCommand dbc) : base(student, dbc)
{
_database = new NextOfKinData(_dbc);
foreach (NextOfKin nextOfKin in student.NextOfKin)
{
if (nextOfKin.PersonId == 0)
{
_database.Add(nextOfKin, student.PersonId);
}
else
{
bool personExists = _database.PersonExists(nextOfKin.PersonId, student.PersonId);
if (!personExists)
{
_database.Allocate(nextOfKin, student.PersonId);
}
else
{
_database.Update(nextOfKin); // update relationship
}
}
}
foreach (object obj in student.RemovedObjects)
{
if (obj is NextOfKin)
{
_database.Remove((NextOfKin)obj, student.PersonId);
}
}
}
public UbahApoteker(string id, string nama, string alamat, string no_telp, string jenisK, DaftarApoteker ua)
{
InitializeComponent();
conn = DBConnection.dbConnection();
cmd = new DBCommand(conn);
sp = new SmartCardOperation();
if (sp.IsReaderAvailable())
{
}
else
{
MessageBox.Show("Tidak ada reader tersedia, pastikan reader sudah terhubung dengan komputer.", "Error",
MessageBoxButton.OK, MessageBoxImage.Error);
}
DataContext = new MApoteker(id, nama, no_telp, alamat, " ");
da = ua;
if (jenisK == "Pria")
{
cbJenisKelamin.SelectedIndex = 0;
}
else if (jenisK == "Wanita")
{
cbJenisKelamin.SelectedIndex = 1;
}
}
public int Save()
{
var oParams = new DBParamCollection {
{ "@PartyId", this.PartyId },
{ "@TaxpayerReferenceNumber", this.TaxpayerReferenceNumber },
{ "@YearofAssessment", this.YearofAssessment },
{ "@RegisteredName", this.RegisteredName },
{ "@TradingName", this.TradingName },
{ "@RegistrationNumber", this.RegistrationNumber },
{ "@FinancialYearEnd", this.FinancialYearEnd },
{ "@TurnoverAmount", this.TurnoverAmount },
{ "@NameUltimateHoldingCo", this.NameUltimateHoldingCo },
{ "@UltimateHoldingCompanyResOutSAInd", this.UltimateHoldingCompanyResOutSAInd },
{ "@TaxResidencyCountryCodeUltimateHoldingCompany", this.TaxResidencyCountryCodeUltimateHoldingCompany },
{ "@UltimateHoldingCOIncomeTaxRefNo", this.UltimateHoldingCOIncomeTaxRefNo },
{ "@MasterLocalFileRequiredInd", this.MasterLocalFileRequiredInd },
{ "@CbCReportRequiredInd", this.CbCReportRequiredInd },
{ "@Datestamp", this.Datestamp },
{ "@Return_Value", null, System.Data.ParameterDirection.ReturnValue }
};
using (var oCommand = new DBCommand("[dbo].[uspINSERT_MultiNationalEntityList]", QueryType.StoredProcedure, oParams))
{
System.Collections.Hashtable oHashTable;
int scope_identity = 0;
scope_identity = oCommand.Execute(out oHashTable);
if (oHashTable.Count > 0 && oHashTable.ContainsKey("@Return_Value"))
{
scope_identity = int.Parse(oHashTable["@Return_Value"].ToString());
}
return(scope_identity);
}
}
public void Insert(Types t)
{
DBCommand command = new DBCommand("[RegChacha].[Type_Add]", true);
command.AddParameter("Type", t.Type);
_connection.ExecuteNonQuery(command);
}
public void Delete(int id)
{
DBCommand command = new DBCommand("[RegChacha].[User_del]", true);
command.AddParameter("id", id);
_connection.ExecuteNonQuery(command);
}
public Types GetOne(int id)
{
DBCommand command = new DBCommand("Select * from [RegChacha].[V_Types] where id=@id");
command.AddParameter("id", id);
return(_connection.ExecuteReader(command, (dr) => dr.ToTypes()).SingleOrDefault());
}
private void SetupMock(DBCommand dBCommand, Person person = null, int rows = 1)
{
var mock = new Mock <IDbCommand>();
switch (dBCommand)
{
case DBCommand.Reader:
mock.Setup(m => m.ExecuteReader()).Returns(MockReader(person, rows).Object).Verifiable();
break;
case DBCommand.ExecuteNoneQuery:
mock.Setup(m => m.ExecuteNonQuery()).Returns(1).Verifiable();
break;
default:
break;
}
var connectionMock = new Mock <IDbConnection>();
connectionMock.Setup(m => m.CreateCommand()).Returns(mock.Object);
adoConnectionFactory = new Mock <IADOConnectionFactory>();
adoConnectionFactory.Setup(x => x.CreateConnection(It.IsAny <string>())).Returns(connectionMock.Object);
AdoNet = new ADONetContext(adoConnectionFactory.Object, "Server=myServerName,myPortNumber;Database=myDataBase;User Id=myUsername;Password=myPassword;");
}
public static ICollection Update(DBCommand cmd, ICollection instance, decimal? value, decimal? offset)
{
instance.Offset = offset;
instance.Value = value;
return cmd.Update("Collection", instance);
}
public bool SaveToMDB(string sBC, string PartName, string SetID, int iOK)
{
if (connStr == null)
{
return(false);
}
if (connStr.Length < 5)
{
return(false);
}
OleDbConnection DBConn;
OleDbCommand DBCommand;
try
{
label19.Text = DateTime.Now.ToString() + ": \r\n" + sBC + " " + PartName + " " + SetID + " OK=" + iOK.ToString();
string strConnection = connStr;
DBConn = new OleDbConnection(strConnection);
DBConn.Open();
DBCommand = DBConn.CreateCommand();
DBCommand.CommandText = "insert into LaserParts(ID,Barcode,PartName,MachineNo,SetNum,isOK,Transdate) values(0,'" + sBC + "','" + PartName + "','" + StrMachineNo + "','" + SetID + "'," + iOK.ToString() + ",getdate())";
DBCommand.ExecuteNonQuery();
}
catch (Exception e)
{
System.Console.WriteLine("Err");
//Log(e.Message);
MessageBox.Show(e.Message);
return(false);
}
return(false);
}
public MainWindow()
{
InitializeComponent();
conn = DBConnection.dbConnection();
cmd = new DBCommand(conn);
tbjudul.Text = "Antrian Poli " + Properties.Settings.Default.Poliklinik;
lbNamaPoli.Text = "Poli " + Properties.Settings.Default.Poliklinik;
LoadDataAntrian();
try
{
sck = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);
sck.Connect(Properties.Settings.Default.SocketServer, Properties.Settings.Default.SocketPort);
}
catch (Exception)
{
//Do nothing
}
//MessageBox.Show(cmd.GetLastNoUrut().ToString());
var userPrefs = new UserPreferences();
listener = new Listener(14000);
listener.SocketAccepted += Listener_SocketAccepted;
Loaded += MainWindow_Loaded;
Height = userPrefs.WindowHeight;
Width = userPrefs.WindowWidth;
Top = userPrefs.WindowTop;
Left = userPrefs.WindowLeft;
WindowState = userPrefs.WindowState;
}
public InputResep(string no_rm, ViewRekamMedis vrm)
{
InitializeComponent();
conn = DBConnection.dbConnection();
cmd = new DBCommand(conn);
this.vrm = vrm;
dataObat = new ObservableCollection <ModelDetailResep>();
var dataDokter = cmd.GetDataDokter();
lbNamaDokter.Content = "Dokter:\t Dr. " + dataDokter.First().nama;
this.no_rm = no_rm;
lbNoRM.Text = no_rm;
_mDetailResep = new ModelDetailResep(" ", " ", " ", " ", " ", " ", " ");
DataContext = _mDetailResep;
lstNoResep = cmd.GetLastNoResep(no_rm);
if (lstNoResep == 0)
{
lstNoResep = 1;
}
else
{
lstNoResep += 1;
}
var no = no_rm + '-' + lstNoResep;
txtKodeResep.Text = no;
LoadResep();
}
public static void InsertRow(SQLiteCommand command, TableModel model)
{
List <string> variables = new List <string> {
DBTableName.HANDLE,
DBTableName.POSITION_ID,
DBTableName.MATRIX_ID,
DBTableName.FILE_ID,
DBTableName.ALIAS,
DBTableName.A_VALUE
};
Dictionary <string, object> paraDict = new Dictionary <string, object>
{
{ DBTableName_AT.handle, model.handle },
{ DBTableName_AT.position, model.position.ID },
{ DBTableName_AT.matrix, model.matrixTransform.ID },
{ DBTableName_AT.file, model.file.ID },
{ DBTableName_AT.alias, model.ALIAS },
{ DBTableName_AT.a_value, model.A_VALUE }
};
DBCommand.InsertCommand(DBTableName.name, variables, paraDict, command);
}
/// <summary>
/// 通过通知单ID查询移库通知单信息
/// </summary>
/// <param name="id">移库通知单ID</param>
public static DataTable QueryMovePlanById(int id)
{
DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager();
DBCommand cmd = dm.CreateDBCommand(CommandType.Text);
string sql = @"select mlb.*,m.MaterialNumber,m.MaterialName,m.MaterialModel,
suOut.SUNumber as outSUNumber,suOut.SUName as outSUName,
whOut.WHNumber as outWHNumber,whOut.WHName as outWHName,
wpOut.WPNumber as outWPNumber,wpOut.WPName as outWPName,
suIn.SUNumber as inSUNumber,suIn.SUName as inSUName,
whIn.WHNumber as inWHNumber,whIn.WHName as inWHName,
wpIn.WPNumber as inWPNumber,wpIn.WPName as inWPName
from MoveLocationBill mlb
left join Material m on mlb.MaterialId=m.MaterialId
left join StorageUnit suOut on mlb.outSUID=suOut.SUID
left join WareHouse whOut on mlb.OutWHID=whOut.WHID
left join WarePlace wpOut on mlb.OutWPID=wpOut.WPID
left join StorageUnit suIn on mlb.inSUID=suIn.SUID
left join WareHouse whIn on mlb.InWHID=whIn.whId
left join WarePlace wpIn on mlb.InWPID=wpIn.wpId
where mlb.Id=@Id";
cmd.ClearParameters();
cmd.AddParameter("@Id", id);
cmd.setCommandText(sql);
return(dm.ExecDataSetCommand(cmd).Tables[0]);
}
public static IList<IApplication> GetAllPaged(DBCommand cmd, IDictionary<IAttributeDefinition, object> criterias, ref int pageNum, int pageSize, ref int pageCount, ref int itemCount)
{
if (!AppCore.AppSingleton.ObjectDefinitions.ContainsKey("Application"))
Debug.Assert(false, "Unimplemented entity [Application]");
ApplicationObjDef appOd = AppCore.AppSingleton.FindObjDef<ApplicationObjDef>();
if (!AppCore.AppSingleton.ObjectDefinitions.ContainsKey("Status"))
Debug.Assert(false, "Unimplemented entity [Status]");
StatusObjDef statusOd = AppCore.AppSingleton.FindObjDef<StatusObjDef>();
if (!AppCore.AppSingleton.ObjectDefinitions.ContainsKey("User"))
Debug.Assert(false, "Unimplemented entity [User]");
UserObjDef userOd = AppCore.AppSingleton.FindObjDef<UserObjDef>();
if(!criterias.ContainsKey(userOd.Role))
Debug.Assert(false, "Criterias should contains UserRole of entity [User]");
UserRole role = (UserRole) Convert.ChangeType(criterias[userOd.Role], typeof (UserRole));
if(role==UserRole.None)role=UserRole.Client;
criterias.Remove(userOd.Role);
var sqlBuilder = new StringBuilder();
string table = string.Format("[{0}]", appOd.TableName);
string tabStatus = string.Format("[{0}]", statusOd.TableName);
string fileds = string.Join(",", appOd.Attributes.Values.Select(a => string.Format("[{0}]", a.Column)).ToArray());
string criterion = string.Join(" and ",
criterias.Keys.Where(a=>a!=userOd.Role).Select(a => string.Format("[{0}]=@{0}", a.Column)).ToArray());
string fieldsWithAlias = "comp." + fileds.Replace(",", ",comp.");
string criterionWithAlias = string.IsNullOrEmpty(criterion) ? "" : "comp." + criterion.Replace(" and ", " and comp.");
string statusCriterion = "";
switch (role)
{
case UserRole.Client:
statusCriterion = string.Format("s.[{0}] in ( {1} ) or s.[{0}] is Null or s.[{0}]=''",
statusOd.Value.Column, string.Join(",",role.GetVisibleStatus().Select(r =>"'" + r.ToLabel() + "'").ToArray()));
break;
case UserRole.Business:
case UserRole.Administrator:
default:
statusCriterion = string.Format("s.[{0}] in ( {1} )",statusOd.Value.Column, string.Join(",", role.GetVisibleStatus().Select(r => "'" + r.ToLabel() + "'").ToArray()));
break;
}
if (!string.IsNullOrEmpty(criterionWithAlias))
statusCriterion = string.Format("({0}) and ({1})", statusCriterion, criterionWithAlias);
sqlBuilder.AppendLine(string.Format("SELECT {1}, (select count(org.[{2}])+1 from {0} [org] where org.[{2}] <comp.[{2}]) as Rank",
table, fieldsWithAlias, appOd.PrimaryKey.Column));
sqlBuilder.AppendLine(string.Format("FROM {0} [comp] left join {1} [s] on s.[{2}]=comp.[{3}]", table,tabStatus,statusOd.PrimaryKey.Column,appOd.PrimaryKey.Column));
if (!string.IsNullOrEmpty(statusCriterion))
sqlBuilder.AppendLine(string.Format("Where {0}", statusCriterion));
sqlBuilder.AppendLine(string.Format("ORDER BY comp.[{0}]", appOd.PrimaryKey.Column));
string sql = sqlBuilder.ToString();
return cmd.GetAllPaged<IApplication>("Application",sql, criterias, ref pageCount, ref pageNum,ref itemCount, pageSize);
}
public static IUser Update(DBCommand cmd, IUser instance, string name, string password, UserRole? role)
{
instance.Name = name;
instance.Password = password;
instance.Role = role;
return cmd.Update("User", instance);
}
public static ICustomer Update(DBCommand cmd, ICustomer instance,string id, string name, string sex, string phone, string address)
{
instance.ID = id;
instance.Name = name;
instance.Sex = sex;
instance.Phone = phone;
instance.Address = address;
return cmd.Update("Customer", instance);
}
public static IStatus Create(DBCommand cmd, string id,string value)
{
var od = AppCore.AppSingleton.FindObjDef<StatusObjDef>();
return cmd.Create<IStatus>("Status", new Dictionary<IAttributeDefinition, object>()
{
{od.PrimaryKey, id},
{od.Value, value},
});
}
public static ICollection Create(DBCommand cmd, string id, decimal? value, decimal? offset)
{
var od = AppCore.AppSingleton.FindObjDef<CollectionObjDef>();
return cmd.Create<ICollection>("Collection", new Dictionary<IAttributeDefinition, object>()
{
{od.PrimaryKey, id},
{od.Value, value == null ? null : value.Value + ""},
{od.Offset, offset == null ? null : offset.Value + ""}
});
}
public static ICase Create(DBCommand cmd, string id, string type, string text)
{
var od = AppCore.AppSingleton.FindObjDef<CaseObjDef>();
return cmd.Create<ICase>("Case", new Dictionary<IAttributeDefinition, object>()
{
{od.PrimaryKey, id},
{od.Type, type},
{od.Text, text}
});
}
public string GenerateHeader (GetViewWorkTableDataResponse response, DBLane lane, DBHost host, DBCommand command)
{
if (!Authentication.IsInRole (response, MonkeyWrench.DataClasses.Logic.Roles.Administrator)) {
return string.Format (@"
<h2>Step {4} on lane '{2}' on '{3}' (<a href='ViewTable.aspx?lane_id={0}&host_id={1}'>table</a>)</h2><br/>", lane.id, host.id, lane.lane, host.host, command.command);
} else {
return string.Format (@"
<h2>Step {4} on lane '<a href='EditLane.aspx?lane_id={0}'>{2}</a>' on '<a href='EditHost.aspx?host_id={1}'>{3}</a>'
(<a href='ViewTable.aspx?lane_id={0}&host_id={1}'>table</a>)</h2><br/>", lane.id, host.id, lane.lane, host.host, command.command);
}
}
public static IUser Create(DBCommand cmd, string id, string name, string password, UserRole? role)
{
var od = AppCore.AppSingleton.FindObjDef<UserObjDef>();
return cmd.Create<IUser>("User", new Dictionary<IAttributeDefinition, object>()
{
{od.PrimaryKey, id},
{od.Name, name},
{od.Password, password},
{od.Role, role}
});
}
public static ICustomer Create(DBCommand cmd, string id, string name, string sex, string phone, string address)
{
var od = AppCore.AppSingleton.FindObjDef<CustomerObjDef>();
return cmd.Create<ICustomer>("Customer", new Dictionary<IAttributeDefinition, object>()
{
{od.PrimaryKey, id},
{od.Name, name},
{od.Sex, sex},
{od.Phone, phone},
{od.Address, address}
});
}
public static int GetItemCount(string idCode,DBCommand conn)
{
string strSQL = string.Empty;
DataTable dttTmp = null;
int intCount = 0;
strSQL = string.Format("select count(*) As ItemCount from dsznfmjlb where idcode='{0}'",idCode);
dttTmp = conn.CreateDataTable(strSQL);
if (dttTmp != null && dttTmp.Rows.Count > 0)
{
intCount = Convert.ToInt32(dttTmp.Rows[0]["ItemCount"] .ToString());
}
return intCount;
}
public IList<ICustomer> GetAll(IDictionary<IAttributeDefinition, object> criterias)
{
using (var cmd = new DBCommand())
{
try
{
return CustomerDAL.GetAll(cmd, criterias);
}
catch (Exception ex)
{
using (var builder = new MessageBuilder())
{
string err = builder.AppendLine("读取客户信息列表错误！").AppendLine(ex).Message;
throw new Exception(err);
}
}
}
}
public IStatus Get(string id)
{
try
{
using (var cmd = new DBCommand())
{
return StatusDAL.Get(cmd, id);
}
}
catch (Exception ex)
{
using (var builder = new MessageBuilder())
{
string err = builder.AppendLine("读取状态信息错误！").AppendLine(ex).Message;
throw new Exception(err);
}
}
}
public rwDB()
{
try
{
string strConn = string.Empty;
conn = new DBCommand();
strConn = ConfigurationManager.ConnectionStrings["ConnStr"].ToString();
conn.ConnectionString = strConn;
conn.DataBaseDriverType = enmDataAccessType.DB_MSSQL;
conn.Open();
bConnected = conn.Connected;
}
catch (System.Exception ex)
{
string strErr = ex.Message;
bConnected = false;
}
}
/// <summary>
///
/// </summary>
/// <param name="sid"></param>
/// <param name="date"></param>
/// <returns></returns>
public static HappyIndex GetHappyIndex( string sid, DateTime date )
{
using( DBCommand cmd = new DBCommand( Con, CommandType.Text ) ) {
cmd.CommandText = "SELECT * FROM HappyIndexes WHERE [User_ID] = @User_ID AND [Date] = @date";
cmd.AddWithValue( "@User_ID", GetUser().ID );
cmd.AddWithValue( "@date", date.Format() );
if( cmd.Read() ) {
return new HappyIndex {
ID = cmd.GetInt( "HappyIndex_ID" ),
Date = date,
EmotionalComment = cmd.GetString( "EmotionalComment" ),
EmotionalIndex = cmd.GetDouble( "EmotionalIndex" ),
ProductivityIndex = cmd.GetDouble( "ProductivityIndex" ),
MotivationIndex = cmd.GetDouble( "MotivationIndex" ),
IndexComment = cmd.GetString( "IndexComment" )
};
}
}
return null;
}
public rwDB()
{
try
{
string strConn = string.Empty;
strConn = ConfigurationManager.ConnectionStrings["ConnectStr"].ToString();
conn = new DBCommand();
conn.ConnectionString = strConn;
conn.ConnectionString = @"Server=Localhost;Integrated Security=True;DataBase=lydxDB;";
conn.DataBaseDriverType = enmDataAccessType.DB_SQL;
conn.Open();
bConnected = conn.Connected;
}
catch (System.Exception ex)
{
string strErr = ex.Message;
}
}
public bool IsExisting(string appId)
{
using (var cmd = new DBCommand())
{
try
{
var od = AppCore.AppSingleton.FindObjDef<StatusObjDef>();
return StatusDAL.IsExisting(cmd,
new Dictionary<IAttributeDefinition, object>() { { od.ID, appId } });
}
catch (Exception ex)
{
using (var builder = new MessageBuilder())
{
string err = builder.AppendLine("查找状态记录错误！").AppendLine(ex).Message;
throw new Exception(err);
}
}
}
}
public ICollection Create(string id, decimal? value, decimal? offset)
{
using (var cmd = new DBCommand())
{
try
{
ICollection instance = CollectionDAL.Create(cmd, id, value,offset);
cmd.Commit();
return instance;
}
catch (Exception ex)
{
cmd.RollBack();
using (var builder = new MessageBuilder())
{
string err = builder.AppendLine("创建付费信息错误！").AppendLine(ex).Message;
throw new Exception(err);
}
}
}
}
public IStatus Create(string id, string value)
{
using (var cmd = new DBCommand())
{
try
{
IStatus instance = StatusDAL.Create(cmd, id,value);
cmd.Commit();
return instance;
}
catch (Exception ex)
{
cmd.RollBack();
using (var builder = new MessageBuilder())
{
string err = builder.AppendLine("创建状态信息错误！").AppendLine(ex).Message;
throw new Exception(err);
}
}
}
}
public ICase Create(string id, string type, string text)
{
using (var cmd = new DBCommand())
{
try
{
ICase instance = CaseDAL.Create(cmd, id, type, text);
cmd.Commit();
return instance;
}
catch (Exception ex)
{
cmd.RollBack();
using (var builder = new MessageBuilder())
{
string err = builder.AppendLine("创建问题信息错误！").AppendLine(ex).Message;
throw new Exception(err);
}
}
}
}
public IStatus Delete(IStatus instance)
{
using (var cmd = new DBCommand())
{
try
{
StatusDAL.Delete(cmd, instance);
cmd.Commit();
return instance;
}
catch (Exception ex)
{
cmd.RollBack();
using (var builder = new MessageBuilder())
{
string err = builder.AppendLine("删除状态信息错误！").AppendLine(ex).Message;
throw new Exception(err);
}
}
}
}
public ICustomer Create(string id, string name, string sex, string phone, string address)
{
using (var cmd = new DBCommand())
{
try
{
ICustomer instance = CustomerDAL.Create(cmd, id,name,sex,phone,address);
cmd.Commit();
return instance;
}
catch (Exception ex)
{
cmd.RollBack();
using (var builder = new MessageBuilder())
{
string err = builder.AppendLine("创建客户信息错误！").AppendLine(ex).Message;
throw new Exception(err);
}
}
}
}
public IUser Create(string name, string password, UserRole? role)
{
using (var cmd = new DBCommand())
{
try
{
string id = Guid.NewGuid().ToString();
IUser instance = UserDAL.Create(cmd, id,name, password, role);
cmd.Commit();
return instance;
}
catch (Exception ex)
{
cmd.RollBack();
using (var builder = new MessageBuilder())
{
string err = builder.AppendLine("创建用户信息错误！").AppendLine(ex).Message;
throw new Exception(err);
}
}
}
}
public static IApplication Create(DBCommand cmd, string id, /*string type, string subType,*/
string customerId, string region, DateTime? dateApplied,
DateTime? dateTraved, string offNoteNo, DateTime? offNoteDate, string remark)
{
var od = AppCore.AppSingleton.FindObjDef<ApplicationObjDef>();
return cmd.Create<IApplication>("Application", new Dictionary<IAttributeDefinition, object>()
{
{od.PrimaryKey, id},
//{od.Type, type},
//{od.SubType, subType},
{od.CustomerID, customerId},
//{od.CustomerName, customerName},
//{od.CustomerSex, customerSex},
{od.Region, region},
{od.DateApplied, dateApplied},
{od.DateTraved, dateTraved},
{od.OffNoteNo, offNoteNo},
{od.OffNoteDate, offNoteDate},
{od.Remark, remark}
});
}
public IApplication Create(string id, string customerId, string region, DateTime? dateApplied, DateTime? dateTraved, string offNoteNo, DateTime? offNoteDate, string remark)
{
using (var cmd = new DBCommand())
{
try
{
IApplication instance = ApplicationDAL.Create(cmd, id, customerId, region, dateApplied, dateTraved,
offNoteNo, offNoteDate,
remark);
cmd.Commit();
return instance;
}
catch (Exception ex)
{
cmd.RollBack();
using (var builder = new MessageBuilder())
{
string err = builder.AppendLine("创建申请单错误！").AppendLine(ex).Message;
throw new Exception(err);
}
}
}
}
public static bool IsExisting(DBCommand cmd, IDictionary<IAttributeDefinition, object> criterias)
{
int count = cmd.Count("Case", criterias);
return count > 0;
}
public static ICase Get(DBCommand cmd, string id)
{
return cmd.Get<ICase>("Case", id);
}
public static ICase Delete(DBCommand cmd, ICase instance)
{
return cmd.Delete("Case", instance);
}
public static ICollection Get(DBCommand cmd, string id)
{
return cmd.Get<ICollection>("Collection", id);
}
