public void CreateDB()
{
db = new Database(_connectionStringName);
db.Execute(@"
DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS authors;
CREATE TABLE posts (
id bigint AUTO_INCREMENT NOT NULL,
title varchar(127) NOT NULL,
author bigint NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE authors (
id bigint AUTO_INCREMENT NOT NULL,
name varchar(127) NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
");
var a1 = new author();
a1.name = "Bill";
db.Insert(a1);
var a2 = new author();
a2.name = "Ted";
db.Insert(a2);
var p = new post();
p.title = "post1";
p.author = a1.id;
db.Insert(p);
p = new post();
p.title = "post2";
p.author = a1.id;
db.Insert(p);
p = new post();
p.title = "post3";
p.author = a2.id;
db.Insert(p);
}
private void ProcessStats(IList<FeatureStatistics> stats)
{
try
{
Logger.Debug("Adding Stats to database");
using (var database = new PetaPoco.Database(_connectionStringName))
{
using (ITransaction transaction = database.GetTransaction())
{
foreach (var stat in stats)
{
foreach (var reading in stat.Readings)
{
var data = new FeatureData()
{
Reading = reading.Name,
Timestamp = DateTime.Now,
Name = stat.Name,
Group = stat.Group
};
data.ValueType = reading.GetType().Name;
SetValue(data, reading);
database.Insert("FeatureData", "Id", true, data);
}
}
transaction.Complete();
}
}
}
catch (Exception ex)
{
Logger.Error("Error saving stats to the database", ex);
}
}
/// <summary>
/// Craetes/updates an index
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public RetreaveIndex SaveOrUpdate(RetreaveIndex entity)
{
if (_database.IsNew(entity))
{
_database.Insert(entity);
}
else
{
_database.Update(entity);
}
//update the associated users
_database.Execute("Delete from Users_Indexes where indexId=@0", entity.IndexId);
foreach (RegisteredUser user in entity.AssociatedUsers)
{
if (_database.IsNew(user))
{
throw new Exception("You must save the User entity first");
}
_database.Execute("Insert into Users_indexes (UserId, IndexId) values (@0, @1)", user.UserId,
entity.IndexId);
}
return(entity);
}
public static void Save(Database db, Purchase purchase)
{
var existing = db.SingleOrDefault<Purchase>("SELECT * FROM purchases WHERE UniqueId=@0 ", purchase.UniqueId);
if (existing != null)
db.Update("purchases", "PurchaseId", existing);
else
db.Insert("purchases", "PurchaseId", purchase);
}
/// <summary>
/// UpSerts the user
/// </summary>
public RegisteredUser SaveOrUpdate(RegisteredUser entity)
{
if (_database.IsNew(entity))
{
_database.Insert(entity);
//save the authentication details
_database.Insert(entity.AuthDetails);
}
else
{
_database.Update(entity);
}
_database.Update <RegisteredUser>("SET AuthenticationDetails = @0 where UserId=@1",
entity.AuthDetails.AuthenticationDetailsId, entity.UserId);
return(entity);
}
private static void IndexFiles(Database database, IEnumerable<CatalogedFile> files)
{
foreach (var catalogedFile in files)
{
Console.WriteLine("Adding {0} to catalog.", catalogedFile.Path);
database.Insert(catalogedFile);
}
}
private void btnOK_Click(object sender, EventArgs e)
{
if (!dxValidationProvider1.Validate()) return;
if (spnOrder.EditValue != null) {
using (var db = new Database(this.ConnString))
{
var count = db.ExecuteScalar<int>(" select count(1) from T_ERP_Procedure where OrderNo = @0 and Guid <> @1 ", Convert.ToInt32(spnOrder.EditValue), Entity==null?Guid.Empty:Entity.Guid);
if (count > 0) {
MsgBox.ShowMessage("当前序号已经存在");
return;
}
}
}
if (EditMode == Business.Core.Define.EnViewEditMode.New)
{
var r = new UltraDbEntity.T_ERP_Procedure
{
Guid = Guid.NewGuid(),
Creator = CurUser,
Updator = CurUser,
Remark = string.Empty,
Reserved1 = 0,
Reserved2 = string.Empty,
Reserved3 = false,
ProcedureName = txtName.Text,
LabourCost = 0,
OrderNo = Convert.ToInt32(spnOrder.EditValue),
BatchNo = Convert.ToInt32(spnBatch.EditValue),
IsUsing = chkusing.Checked
};
using (var db = new Database(this.ConnString))
{
db.Insert(r);
Entity = r;
DialogResult = System.Windows.Forms.DialogResult.OK; Close();
}
}
else if (EditMode == Business.Core.Define.EnViewEditMode.Edit)
{
Entity.IsUsing = chkusing.Checked;
Entity.Updator = CurUser;
Entity.ProcedureName = txtName.Text;
Entity.LabourCost = 0;
Entity.OrderNo = Convert.ToInt32(spnOrder.EditValue);
Entity.BatchNo = Convert.ToInt32(spnBatch.EditValue);
using (var db = new Database(this.ConnString))
{
db.Update(Entity);
DialogResult = System.Windows.Forms.DialogResult.OK; Close();
}
}
}
public IHttpActionResult Post([FromUri] string id, [FromBody] Entities.Storytime storytime)
{
var db = new PetaPoco.Database("AGSoftware");
storytime.DateCreated = System.DateTime.Now;
storytime.UserId = Storytime.Providers.UserHelper.GetUserId(this.User.Identity.Name);
storytime.UsersNotified = false;
db.Insert(storytime);
if (storytime.StorytimeType == StorytimeType.Group)
{
StorytimeGroup storytimegroup = new StorytimeGroup();
storytimegroup.StorytimeId = storytime.StorytimeId;
storytimegroup.UserGroupId = storytime.UserGroupId;
storytimegroup.UsersNotified = false;
db.Insert(storytimegroup);
return(Ok(storytime.StorytimeId));
}
else if (storytime.StorytimeType == StorytimeType.User)
{
StorytimeUserList storytimeuserlist = new StorytimeUserList();
foreach (AspNetUsers user in storytime.Users)
{
storytimeuserlist.StorytimeId = storytime.StorytimeId;
storytimeuserlist.UserId = user.Id;
storytimeuserlist.UserNotified = false;
db.Insert(storytimeuserlist);
}
storytimeuserlist.StorytimeId = storytime.StorytimeId;
storytimeuserlist.UserId = storytime.UserId;
storytimeuserlist.UserNotified = false;
db.Insert(storytimeuserlist);
return(Ok(storytime.StorytimeId));
}
else
{
return(BadRequest("StorytimeType is invalid"));
}
}
protected override void InsertEntities(List<Entity> entities)
{
using (var database = new Database("SQLiteTest"))
{
using (var transaction = database.GetTransaction())
{
entities.ForEach(entity => database.Insert(entity));
transaction.Complete();
}
}
}
public IHttpActionResult Post([FromUri] string id, [FromBody]Entities.Storytime storytime)
{
var db = new PetaPoco.Database("AGSoftware");
storytime.DateCreated = System.DateTime.Now;
storytime.UserId = Storytime.Providers.UserHelper.GetUserId(this.User.Identity.Name);
storytime.UsersNotified = false;
db.Insert(storytime);
if (storytime.StorytimeType == StorytimeType.Group)
{
StorytimeGroup storytimegroup = new StorytimeGroup();
storytimegroup.StorytimeId = storytime.StorytimeId;
storytimegroup.UserGroupId = storytime.UserGroupId;
storytimegroup.UsersNotified = false;
db.Insert(storytimegroup);
return Ok(storytime.StorytimeId);
}
else if (storytime.StorytimeType == StorytimeType.User)
{
StorytimeUserList storytimeuserlist = new StorytimeUserList();
foreach (AspNetUsers user in storytime.Users)
{
storytimeuserlist.StorytimeId = storytime.StorytimeId;
storytimeuserlist.UserId = user.Id;
storytimeuserlist.UserNotified = false;
db.Insert(storytimeuserlist);
}
storytimeuserlist.StorytimeId = storytime.StorytimeId;
storytimeuserlist.UserId = storytime.UserId;
storytimeuserlist.UserNotified = false;
db.Insert(storytimeuserlist);
return Ok(storytime.StorytimeId);
}
else
return BadRequest("StorytimeType is invalid");
}
public Guid CreateUser(string email, string displayName, string passwordHash)
{
var db = new Database(_config.ConnectionString, "System.Data.SqlClient");
var userId = (int)db.Insert("MR.tUser", "UserId", true, new
{
Email = email,
Passwd = passwordHash,
DisplayName = displayName,
PasswdLastChange = DateTime.UtcNow
});
return db.Single<Guid>("select AuthId from MR.tUser where UserId = @0", userId);
}
public bool addProvider(Provider provider)
{
try
{
using (var db = new PetaPoco.Database("linkSTEM"))
{
db.Insert("Providers", "provider_ID", provider);
return(true);
}
}
catch (Exception)
{
return(false);
}
}
public bool addMarker(Marker marker)
{
try
{
using (var db = new PetaPoco.Database("linkSTEM"))
{
db.Insert("Markers", "marker_ID", marker);
return(true);
}
}
catch (Exception)
{
return(false);
}
}
public static Response QueryHelper <T>(string Query = "", string httpVerb = "GET", dynamic payload = null, bool getOneElement = false) where T : class
{
try
{
using (var db = new PetaPoco.Database("SWATDB"))
{
if (httpVerb == "GET")
{
if (!getOneElement)
{
var elements = db.Query <T>(Query).ToList();
return(ResponseHelper(ResponseContent: elements, Verb: httpVerb));
}
else
{
var elements = db.FirstOrDefault <T>(Query);
return(ResponseHelper(ResponseContent: elements, Verb: httpVerb));
}
}
if (httpVerb == "DELETE")
{
var elements = db.Execute(Query);
return(ResponseHelper(ResponseCode: HttpStatusCode.OK, Verb: httpVerb));
}
if (httpVerb == "POST" && payload != null)
{
var element = db.Insert(payload);
return(ResponseHelper(ResponseContent: element, Verb: httpVerb));
}
if (httpVerb == "PUT" && payload != null)
{
db.Save(payload);
return(ResponseHelper(Verb: httpVerb));
}
return(null);
}
}
catch (Exception ex)
{
ConsoleMessage(httpVerb, string.Format("Error in {0} Query", typeof(T).GetType()), ex.Message);
return(HttpStatusCode.InternalServerError);
}
}
public static BoolMessage PpInsert(object obj)
{
_checkVal.Data = null;
try
{
if (string.IsNullOrEmpty(_cstring))
{
throw new Exception("No connection string provided");
}
_pp.Insert(obj);
_checkVal.Success = true;
_checkVal.Message = string.Format("Successfully inserted {0}", obj.ToString());
}
catch (Exception ex)
{
_checkVal.Success = false;
_checkVal.Message = ex.Message;
}
return(_checkVal);
}
private int SaveABSAMemberRecord(BSAMemberViewModel item)
{
//Connect to the Umbraco DB
var db = new PetaPoco.Database(sConnectString);
BSAMember oModel = new BSAMember();
oModel.BSAMemberId = item.BSAMemberId;
oModel.UserName = item.UserName;
oModel.Password = item.Password;
oModel.BSAMemberType = item.BSAMemberType;
oModel.FirstName = item.FirstName;
oModel.LastName = item.LastName;
oModel.StreetAddress = item.StreetAddress;
oModel.City = item.City;
oModel.State = item.State;
oModel.ZipCode = item.ZipCode;
oModel.District = item.District;
oModel.Council = item.Council;
oModel.Troop = item.Troop;
oModel.Crew = item.Crew;
oModel.ScoutMasterId = item.ScoutMasterId;
oModel.Telephone = item.Telephone;
oModel.BirthDate = item.BirthDate;
oModel.EmailAddress = item.EmailAddress;
oModel.BackGroundCheck = item.BackGroundCheck;
oModel.DeleteRecordFlag = false;
if (item.BSAMemberId > 0)
{
db.Update(oModel);
}
else
{
db.Insert(oModel);
}
//Return the view with our model and comments
return(oModel.BSAMemberId);
}
static public void PopulateTestDB(TestContext ctx)
{
Database db = new Database("openpermit");
string[] types = new string[] { "BLDG", "FIRE", "ELEC", "MECH", "PLUM" };
string[] currStatus = new string[] { "APPLIED", "ISSUED", "CLOSED", "EXPIRED" };
string[] statusMapped = new string[] { "Application Accepted", "Permit Issued", "Permit Finaled", "Permit Cancelled" };
string[] applied = new string[] { "01/01/2012", "01/01/2013", "01/01/2014", "01/01/2015" };
string[] issued = new string[] { "01/02/2012", "01/02/2013", "01/02/2014", "01/02/2015" };
string[] closed = new string[] { "03/01/2012", "03/01/2013", "03/01/2014", "03/01/2015" };
string[] expired = new string[] { "06/01/2012", "06/01/2013", "06/01/2014", "06/01/2015" };
for (int i = 0; i < 30; i++)
{
Permit permit = new Permit();
permit.AddedSqFt = i;
permit.MasterPermitNum = (i % 2).ToString();
permit.AppliedDate = Convert.ToDateTime(applied[i % 4]);
permit.COIssuedDate = DateTime.Now;
permit.CompletedDate = Convert.ToDateTime(closed[i % 4]);
permit.ContractorAddress1 = String.Format("29{0} SW {1} Ave", i, i + 6);
permit.ContractorAddress2 = "Address2_" + i.ToString();
permit.ContractorCity = "Miami";
permit.ContractorCompanyDesc = "Company Description " + i.ToString();
permit.ContractorCompanyName = "Company Name " + i.ToString();
permit.ContractorEmail = String.Format("Contractor_{0}@aecosoft.com", i);
permit.ContractorFullName = "ContractorName_" + i.ToString();
permit.ContractorLicNum = "34RT568903" + i.ToString();
permit.ContractorPhone = "305-444-55" + (i + 10).ToString();
permit.ContractorState = "FL";
permit.ContractorStateLic = "FL5467021";
permit.ContractorTrade = "ContractorTrade_" + i.ToString();
permit.ContractorTradeMapped = "ContractrorTradeMapped_" + i.ToString();
permit.ContractorZip = "331" + (i + 40).ToString();
permit.Description = "PermitDescription_" + i.ToString();
permit.EstProjectCost = 30000 + i;
permit.ExpiresDate = Convert.ToDateTime(expired[i % 4]);
permit.ExtraFields = "{'blah': 'blue'}";
permit.Fee = 30 + i;
permit.HoldDate = DateTime.Now;
permit.HousingUnits = i;
permit.IssuedDate = Convert.ToDateTime(issued[i % 4]);
permit.Jurisdiction = "Miami-Dade";
permit.Latitude = 25.700189 - (double)i/100;
permit.Link = String.Format("http://permiturl{0}.com", i);
permit.Longitude = -80.288020 - (double)i/100;
permit.OriginalAddress1 = String.Format("8{0} NW 1{1}th Ave", i, i + 4);
permit.OriginalAddress2 = "OrgAddress2_" + i.ToString();
permit.OriginalCity = "Miami";
permit.OriginalState = "FL";
permit.OriginalZip = "331" + (i + 57).ToString();
permit.PermitClass = "PERM_" + i.ToString();
permit.PermitClassMapped = "PERM_" + i.ToString() + "_CLASS";
permit.PermitNum = "PERMNUM_" + i.ToString();
permit.PermitType = types[i % 5];
permit.PermitTypeDesc = "TYPEDESC_" + i.ToString();
permit.PermitTypeMapped = "TYPEMAPPEDDESC_" + i.ToString();
permit.PIN = "456" + (10 + i).ToString();
permit.ProjectId = "PROJID_" + i.ToString();
permit.ProjectName = "PROJNAME_" + i.ToString();
permit.ProposedUse = "PORPUSE_" + i.ToString();
permit.Publisher = "PUBLISH_" + i.ToString();
permit.RemovedSqFt = i;
permit.StatusCurrent = currStatus[i % 4];
permit.StatusCurrentMapped = statusMapped[i % 4];
permit.StatusDate = DateTime.Now;
permit.TotalAccSqFt = 10000 + i;
permit.TotalFinishedSqFt = 5000 + i;
permit.TotalHeatedSqFt = 5000 + i;
permit.TotalSprinkledSqFt = 4000 + i;
permit.TotalSqFt = 2500 + i;
permit.TotalUnfinishedSqFt = 500 + i;
permit.TotalUnheatedSqFt = 1000 + i;
permit.VoidDate = DateTime.Now;
permit.WorkClass = "WORKCLASS_" + i.ToString();
permit.WorkClassMapped = "WORKCLASS_" + i.ToString();
db.Insert("Permit", "PermitNum", false, permit);
db.Execute("UPDATE Permit SET Location=geography::Point(Latitude, Longitude, 4326)");
}
for (int j = 0; j < 10; j++)
{
PermitStatus status = new PermitStatus();
status.PermitNum = "PERMNUM_15";
status.StatusPrevious = "STATUS_" + j;
status.StatusPreviousDate = DateTime.Now;
status.StatusPreviousMapped = "STATUSMAPPED_" + j;
status.Comments = "COMMENTS_" + j;
db.Insert("PermitStatus", "id", true, status);
}
for (int j = 0; j < 10; j++)
{
Inspection inspection = new Inspection();
inspection.PermitNum = "PERMNUM_15";
inspection.InspectedDate = DateTime.Now;
inspection.InspectionNotes = "MYNOTES_" + j;
inspection.Inspector = "INSPECTOR_" + j;
inspection.InspType = "TYPE_" + j;
inspection.InspTypeMapped = "TYPEMAPPED_" + j;
inspection.ReInspection = 0;
inspection.RequestDate = DateTime.Now;
inspection.Result = "PASSED";
inspection.ResultMapped = "PASSED_MAPPED";
inspection.ScheduledDate = DateTime.Now;
inspection.Description = "DESCRIPTION_" + j;
inspection.DesiredDate = DateTime.Now;
inspection.ExtraFields = "{'blah': 'blue'}";
inspection.Final = 1;
db.Insert("Inspection", "Id", true, inspection);
}
}
private static void addTagToTopic(int topicId, Tag tag)
{
var db = new Database("umbracoDbDSN");
if (tag.Id == 0)
{
db.Insert(tag);
}
var topicTag = db.SingleOrDefault<TopicTag>("WHERE tagId = @0 and topicId = @1", tag.Id, topicId);
if (topicTag == null)
{
topicTag = new TopicTag(tag.Id, topicId, tag.Weight);
db.Insert(topicTag);
}
}
static void Main(string[] args)
{
try
{
SendEMail("Started MDC OpenPermit Sync");
Console.WriteLine("Sync Job Started on: " + DateTime.Now.ToString());
Console.WriteLine("Bringing data from Socrata");
var permits = getPermitsToSync();
Database db = new Database("openpermit");
Console.WriteLine("Inserting Data into DB");
foreach (Permit permit in permits.Item1)
{
try
{
processMDCInpsections(permit, db);
db.Insert("Permit", "PermitNum", false, permit);
}
catch (Exception ex)
{
string errorMsg = String.Format("Error found Inserting Permit {0}: Error: '{1}' with Stack Trace: {2}",
permit.PermitNum, ex.Message, ex.StackTrace);
Console.WriteLine(errorMsg);
SendEMail(errorMsg);
}
}
SendEMail(String.Format("Inserted {0} new permit records.", permits.Item1.Count));
foreach (Permit permit in permits.Item2)
{
try
{
if (!checkIfExpired(permit))
{
processMDCInpsections(permit, db);
}
db.Update("Permit", "PermitNum", permit);
}
catch (Exception ex)
{
string errorMsg = String.Format("Error found Processing Permit {0}: Error: '{1}' with Stack Trace: {2}",
permit.PermitNum, ex.Message, ex.StackTrace);
Console.WriteLine(errorMsg);
SendEMail(errorMsg);
}
}
SendEMail(String.Format("Processed {0} existing permit records.", permits.Item2.Count));
Console.WriteLine("Populating Geography Field");
db.Execute("UPDATE Permit SET Location=geography::Point(Latitude, Longitude, 4326) WHERE Location is NULL");
Console.WriteLine("Sync Job Done on: " + DateTime.Now.ToString());
SendEMail("MDC OpenPermit Sync is done");
}
catch (Exception ex)
{
string errorMsg = String.Format("Error found running MDC OpenPermit Sync. Error: '{0}' with Stack Trace: {1}",
ex.Message, ex.StackTrace);
Console.WriteLine(errorMsg);
SendEMail(errorMsg);
}
}
private static void processMDCInpsections(Permit permit, Database db)
{
//Cleanup DB to bring new points for permit
Console.WriteLine("Cleaning Data from DB for Permit: " + permit.PermitNum);
db.Execute(String.Format("DELETE FROM Inspection WHERE PermitNum = '{0}'", permit.PermitNum));
db.Execute(String.Format("DELETE FROM PermitStatus WHERE PermitNum = '{0}'", permit.PermitNum));
Console.WriteLine("Getting Inspections for Permit: " + permit.PermitNum);
List<Inspection> inspections = getMDCInspections(permit.PermitNum);
DateTime? lastApprovedInspectionDate = permit.StatusDate;
if (permit.AppliedDate != null)
{
PermitStatus status = new PermitStatus();
status.PermitNum = permit.PermitNum;
status.StatusPrevious = "APPLIED";
status.StatusPreviousMapped = "Application Accepted";
status.StatusPreviousDate = permit.AppliedDate;
db.Insert("PermitStatus", "id", true, status);
permit.StatusCurrent = status.StatusPrevious;
permit.StatusCurrentMapped = status.StatusPreviousMapped;
permit.StatusDate = status.StatusPreviousDate;
}
if (permit.IssuedDate != null)
{
PermitStatus status = new PermitStatus();
status.PermitNum = permit.PermitNum;
status.StatusPrevious = "ISSUED";
status.StatusPreviousMapped = "Permit Issued";
status.StatusPreviousDate = permit.IssuedDate;
db.Insert("PermitStatus", "id", true, status);
permit.StatusCurrent = status.StatusPrevious;
permit.StatusCurrentMapped = status.StatusPreviousMapped;
permit.StatusDate = status.StatusPreviousDate;
}
if (isPermitClosed(inspections))
{
PermitStatus status = new PermitStatus();
status.PermitNum = permit.PermitNum;
status.StatusPrevious = "CLOSED";
status.StatusPreviousMapped = "Permit Finaled";
status.StatusPreviousDate = lastApprovedInspectionDate;
db.Insert("PermitStatus", "id", true, status);
permit.StatusCurrent = status.StatusPrevious;
permit.StatusCurrentMapped = status.StatusPreviousMapped;
permit.StatusDate = status.StatusPreviousDate;
permit.CompletedDate = status.StatusPreviousDate;
}
foreach (Inspection inspection in inspections)
{
db.Insert("Inspection", "Id", true, inspection);
}
}
public bool Post(MixERP.Net.Entities.Core.TaxMaster item)
{
if (item == null)
{
throw new HttpResponseException(HttpStatusCode.BadRequest);
}
ApiAccessPolicy policy = new ApiAccessPolicy(typeof(MixERP.Net.Entities.Core.TaxMaster), "POST");
policy.Authorize();
if (!policy.IsAuthorized)
{
throw new HttpResponseException(HttpStatusCode.Forbidden);
}
try
{
using (Database db = new Database(Factory.GetConnectionString(), "Npgsql"))
{
db.Insert(item);
}
}
catch
{
throw new HttpResponseException(HttpStatusCode.InternalServerError);
}
return true;
}
private static void TestCreateOperation(Database db, Container newContainer)
{
// CREATE
Console.WriteLine("tallennetaan: {0} - {1}, {2}", newContainer.ContainerId, newContainer.Modificator, newContainer.ModificationDate);
db.Insert("Container", "ContainerId", false, newContainer);
Console.WriteLine("tallennettu: {0} - {1}, {2}", newContainer.ContainerId, newContainer.Modificator, newContainer.ModificationDate);
Console.WriteLine();
}
private void btnOK_Click(object sender, EventArgs e)
{
if (!dxValidationProvider1.Validate()) return;
var pro = lookUpEdit1.EditValue as T_ERP_Procedure;
if(ExistsRng!=null && ExistsRng.Where(j => j.ProcedureGuid == pro.Guid && j.Guid != Ent.Guid).Count() > 0){
MsgBox.ShowMessage("已存在该工序的工价");
return;
}
if (EditMode == Business.Core.Define.EnViewEditMode.New)
{
var oj = new UltraDbEntity.T_ERP_ProducePrice
{
Creator = CurUser,
Updator = CurUser,
Guid = Guid.NewGuid(),
Remark = string.Empty,
Reserved1 = 0,
Reserved2 = string.Empty,
Reserved3 = false,
CostPrice = labelSpinEdit1.Value,
OuterIid = Item.OuterIid,
OuterSkuId = Item.OuterSkuId,
ItemGuid = Item.Guid,
NumIid = Item.NumIid,
ProcedureGuid = pro.Guid,
ProcedureName = pro.ProcedureName
};
using (var db = new Database(this.ConnString))
{
db.Insert(oj);
}
DialogResult = System.Windows.Forms.DialogResult.OK;
Close();
}
else if (EditMode == Business.Core.Define.EnViewEditMode.Edit)
{
Ent.CostPrice = labelSpinEdit1.Value;
Ent.ProcedureGuid = pro.Guid;
Ent.ProcedureName = pro.ProcedureName;
using (var db = new Database(this.ConnString))
{
db.Update(Ent);
}
DialogResult = System.Windows.Forms.DialogResult.OK;
Close();
}
}
protected override void Process()
{
using (var unit = GetUnitOfWork())
{
try
{
var _assortmentRepo = unit.Scope.Repository <VendorAssortment>();
var connectors = (from c in this.Connectors
where
(((ConnectorType)c.ConnectorType).Has(ConnectorType.WebAssortment) ||
((ConnectorType)c.ConnectorType).Has(ConnectorType.ShopAssortment))
#if !DEBUG
&& c.IsActive
#endif
select c).ToList();
foreach (Connector conn in connectors)
{
bool excludeProducts = conn.ConnectorSettings.GetValueByKey <bool>("ExcludeProducts", false);
using (var pDb = new PetaPoco.Database(Connection, "System.Data.SqlClient"))
{
pDb.CommandTimeout = 15 * 60;
var productMatches = pDb.Query <ProductMatch>("SELECT * FROM ProductMatch WHERE isMatched = 1").ToList();
List <string> vendorItemNumbersToExclude = new List <string>();
Dictionary <int, string> vendorItemNumbers = new Dictionary <int, string>();
Dictionary <int, Content> contentList = new Dictionary <int, Content>();
log.DebugFormat("Start Generating Assortment for {0}", conn.Name);
log.DebugFormat("Connector {0}({1}) has {2} rules for assortment generation", conn.Name, conn.ConnectorID, conn.ContentProducts.Count);
bool assortmentLoaded = false;
var publicationRules = pDb.Fetch <ConnectorPublication>("SELECT * FROM ConnectorPublication WHERE ConnectorID = @0", conn.ConnectorID);
if (excludeProducts)
{
vendorItemNumbersToExclude = pDb.Fetch <string>("Select value from ExcludeProduct where ConnectorID = @0", conn.ConnectorID).ToList();
vendorItemNumbers = pDb.Fetch <Product>("select productID, VendorItemNumber from product").ToDictionary(x => x.ProductID, y => y.VendorItemNumber);
}
foreach (ContentProduct rule in conn.ContentProducts.OrderBy(x => x.ProductContentIndex))
{
var connectorPublicationRules = publicationRules.Where(x => x.VendorID == rule.VendorID && (!x.FromDate.HasValue || (x.FromDate.HasValue && x.FromDate <= DateTime.Now)) && (!x.ToDate.HasValue || (x.ToDate.HasValue && x.ToDate >= DateTime.Now))).ToList();
if (!assortmentLoaded)
{
assortmentLoaded = rule.IsAssortment;
}
#region content logic
log.DebugFormat("Processing Rule {0} for connectorid {1}", rule.ProductContentID, conn.ConnectorID);
if (rule.ProductID.HasValue)
{
#region Single Product
if (assortmentLoaded && !rule.IsAssortment)
{
// exclude product
if (rule.ProductID.HasValue)
{
contentList.Remove(rule.ProductID.Value);
}
}
else
{
var vass = _assortmentRepo.GetSingle(a => a.VendorID == rule.VendorID &&
a.ProductID == rule.ProductID.Value &&
a.IsActive);
if (vass != null)
{
var groups = (from va in _assortmentRepo.GetAllAsQueryable()
from pa in va.ProductGroupVendors
select new
{
va.VendorAssortmentID,
pa.ProductGroupVendorID
}).ToList();
var vendoradd = (from va in _assortmentRepo.GetAllAsQueryable()
where va.VendorAssortmentID == vass.VendorAssortmentID
select new VendorAdds
{
VendorAssortmentID = va.VendorAssortmentID,
ConcentratorStatusID = va.VendorPrices.FirstOrDefault().ConcentratorStatusID,
QuantityOnHand = unit.Scope.Repository <VendorStock>().GetAllAsQueryable(c => c.ProductID == va.ProductID && c.VendorID == va.VendorID).Sum(c => c.QuantityOnHand),
ProductID = va.ProductID,
BrandID = va.Product.BrandID
}).ToList();
Content content = null;
if (!contentList.ContainsKey(vass.ProductID))
{
if (vass.ProductGroupVendors == null)
{
vass.ProductGroupVendors = new List <ProductGroupVendor>();
}
content = new Content
{
ConnectorID = conn.ConnectorID,
ProductID = rule.ProductID.Value,
ShortDescription = vass.ShortDescription,
LongDescription = vass.LongDescription,
ProductContentID = rule.ProductContentID,
ProductGroupVendors = vass.ProductGroupVendors.Select(c => c.ProductGroupID).ToList(),
Product = unit.Scope.Repository <Product>().GetSingle(c => c.ProductID == rule.ProductID.Value)
};
contentList.Add(rule.ProductID.Value, content);
CheckConnectorPublication(content, connectorPublicationRules, vendoradd.FirstOrDefault(x => x.VendorAssortmentID == vass.VendorAssortmentID), unit, contentList);
}
}
else
{
log.DebugFormat(
"Could not add product {0} to content, does not exists in vender assortment (VendorID {1})",
rule.ProductID.Value, rule.VendorID);
}
}
#endregion
}
else
{
#region Non Product
//include scenario
string additionalFilters = String.Empty;
if (rule.BrandID.HasValue)
{
additionalFilters += String.Format(" AND P.BrandID = {0}", rule.BrandID);
}
//if (rule.ProductGroupID.HasValue)
//{
// vendorAssortment = vendorAssortment.Where(c => c.ProductGroupVendors.Any(l => l.ProductGroupID == rule.ProductGroupID.Value && l.VendorID == rule.VendorID));
//}
string query = String.Format(@"SELECT VA.VendorAssortmentID, VP.ConcentratorStatusID, P.BrandID, VA.ProductID, ISNULL(SUM(VS.QuantityOnHand),0) AS QuantityOnHand
FROM VendorAssortment VA
INNER JOIN Product P ON (VA.ProductID = P.ProductID)
INNER JOIN VendorStock VS ON (VA.VendorID = VS.VendorID AND VA.ProductID = VS.ProductID)
INNER JOIN VendorPrice VP ON (VA.VendorAssortmentID = VP.VendorAssortmentID)
WHERE VA.VendorID = {0} {1}
GROUP BY VA.VendorAssortmentID, VP.ConcentratorStatusID, P.BrandID, VA.ProductID
", rule.VendorID, additionalFilters);
var vendoradd = pDb.Query <VendorAdds>(query).ToDictionary(x => x.VendorAssortmentID, y => y);
// base collection
//var vendorAssortment = pDb.Fetch<VendorAssortment>("SELECT * FROM VendorAssortment WHERE VendorID = @0 AND IsActive = 1", rule.VendorID);
var vendorAssortmentQuery = string.Format("SELECT va.*, pavSentToWehkamp.Value as 'SentToWehkamp', pavSentAsDummy.Value as 'SentToWehkampAsDummy' FROM VendorAssortment va LEFT OUTER JOIN ProductAttributeValue pavSentToWehkamp ON va.ProductID = pavSentToWehkamp.ProductID AND pavSentToWehkamp.AttributeID = (SELECT AttributeID FROM ProductAttributeMetaData pamd WHERE pamd.AttributeCode = 'SentToWehkamp') LEFT OUTER JOIN ProductAttributeValue pavSentAsDummy ON va.ProductID = pavSentAsDummy.ProductID AND pavSentAsDummy.AttributeID = (SELECT AttributeID FROM ProductAttributeMetaData pamd WHERE pamd.AttributeCode = 'SentToWehkampAsDummy') WHERE va.VendorID = {0} AND va.IsActive = 1", rule.VendorID);
var vendorAssortment = pDb.Fetch <VendorAssortmentWehkampSent>(vendorAssortmentQuery);
var productGroupVendors = pDb.Fetch <ProductGroupVendor>("SELECT * FROM ProductGroupVendor WHERE VendorID = @0 OR VendorID = @1", rule.VendorID, rule.Vendor.ParentVendorID ?? rule.VendorID);
string groupsQuery = string.Format(@"SELECT DISTINCT VA.VendorAssortmentID, PGV.ProductGroupID
FROM VendorAssortment VA
INNER JOIN VendorProductGroupAssortment VPGA ON (VPGA.VendorAssortmentID = VA.VendorAssortmentID)
INNER JOIN ProductGroupVendor PGV ON (PGV.ProductGroupVendorID = VPGA.ProductGroupVendorID)
WHERE VA.VendorID = {0} AND VA.IsActive=1", rule.VendorID);
var groupSource = pDb.Fetch <VaGroupInfo>(groupsQuery);
var groups = (from g in groupSource
group g by g.VendorAssortmentID into grouped
select grouped).ToDictionary(x => x.Key, y => (from pg in y select pg.ProductGroupID).ToList());
groupSource = null;
var productRepo = pDb.Fetch <Product>("SELECT * FROM Product").ToDictionary(x => x.ProductID, y => y);
List <ProductAttributeValue> values = pDb.Query <ProductAttributeValue>(string.Format(@"select pav.* from productattributevalue pav
inner join connectorpublication cp on pav.attributeid = cp.attributeid
where cp.connectorid = {0}", rule.ConnectorID)).ToList();
foreach (var va in vendorAssortment)
{
if (!groups.ContainsKey(va.VendorAssortmentID))
{
continue;
}
if (assortmentLoaded && !rule.IsAssortment)
{
contentList.Remove(va.ProductID);
}
else
{
if (excludeProducts &&
(string.IsNullOrEmpty(va.SentToWehkamp) || va.SentToWehkamp.ToLowerInvariant() == "false") &&
(string.IsNullOrEmpty(va.SentToWehkampAsDummy) || va.SentToWehkampAsDummy.ToLowerInvariant() == "false"))
{
string vendorItemNumber = vendorItemNumbers[va.ProductID];
if (ExcludeProduct(vendorItemNumber, vendorItemNumbersToExclude))
{
continue;
}
}
Content content = null;
if (!contentList.ContainsKey(va.ProductID))
{
content = new Content
{
ConnectorID = conn.ConnectorID,
ProductID = va.ProductID,
ShortDescription = va.ShortDescription,
LongDescription = va.LongDescription,
LineType = va.LineType,
ProductContentID = rule.ProductContentID,
ProductGroupVendors = groups[va.VendorAssortmentID],
Product = productRepo[va.ProductID]
};
//content.Product.RelatedProductsSource = relatedProducts.Where(c => c.ProductID == content.ProductID).ToList();
content.Product.ProductAttributeValues = values.Where(c => c.ProductID == content.ProductID).ToList();
contentList.Add(va.ProductID, content);
}
VendorAdds vad = null;
vendoradd.TryGetValue(va.VendorAssortmentID, out vad);
CheckConnectorPublication(content, connectorPublicationRules, vad, unit, contentList);
}
}
#endregion
}
log.DebugFormat("Finished Processing Rule {0} for Connector {1}", rule.ProductContentID, conn.Name);
#endregion
}
///put logic here
try
{
var vendorsettings = pDb.Query <ContentProduct>("SELECT * FROM ContentProduct WHERE ConnectorID = @0", conn.ConnectorID)
.Select(v => new
{
v.ProductContentIndex,
v.ProductContentID
}).Distinct().ToDictionary(x => x.ProductContentID, x => x.ProductContentIndex);
var copyContentList = (from c in contentList.Values
join pm in productMatches on c.ProductID equals pm.ProductID
select c).ToList();
foreach (var c in copyContentList)
{
var match = productMatches.Where(x => x.ProductID == c.ProductID).FirstOrDefault();
if (match != null)
{
var matches = productMatches.Where(x => x.ProductMatchID == match.ProductMatchID && x.ProductID != c.ProductID).ToList();
if (matches.Count > 0)
{
foreach (var product in matches)
{
if (!c.ProductContentID.HasValue)
{
continue;
}
int contentVendorIndex = vendorsettings[c.ProductContentID.Value];
Content matchContent = null;
if (contentList.TryGetValue(product.ProductID, out matchContent))
{
if (!matchContent.ProductContentID.HasValue)
{
continue;
}
int matchContentIndex = vendorsettings[matchContent.ProductContentID.Value];
if (contentVendorIndex > matchContentIndex)
{
contentList.Remove(c.ProductID);
}
else
{
contentList.Remove(matchContent.ProductID);
}
}
}
}
}
}
}
catch (Exception ex)
{
log.AuditError("Cleaning Matches failed for {0}", conn.Name);
}
var curContent = pDb.Fetch <Content>("SELECT * FROM Content WHERE ConnectorID = @0", conn.ConnectorID).ToDictionary(x => x.ProductID, y => y);
try
{
log.DebugFormat("Cleaning up assortment for {0}", conn.Name);
var delcontent = (from c in curContent
where !contentList.ContainsKey(c.Key)
select c.Value).ToList();
foreach (var rec in delcontent)
{
pDb.Delete("Content", "ProductID, ConnectorID", rec);
}
log.DebugFormat("Finished cleaning up for {0}", conn.Name);
}
catch (Exception ex)
{
log.FatalFormat("Assorment cleanup failed for {0} error {1}", conn.ConnectorID, ex.StackTrace);
}
#region Existing Content
//OBSOLETE:
//log.Debug("Processing Existing Content");
//foreach (var curCon in contentList.Values)
//{
// Content exCon = null;
// curContent.TryGetValue(curCon.ProductID, out exCon);
// if (exCon != null)
// {
// exCon.ExtendedCatalog = curCon.ExtendedCatalog;
// exCon.LedgerClass = curCon.LedgerClass;
// exCon.LineType = curCon.LineType;
// exCon.LongDescription = curCon.LongDescription;
// exCon.ProductDesk = curCon.ProductDesk;
// exCon.ShortDescription = curCon.ShortDescription;
// exCon.ProductContentID = curCon.ProductContentID;
// pDb.Update("Content", "ProductID, ConnectorID", exCon, new List<String>() { "ShortDescription", "LongDescription", "LineType", "LedgerClass", "ProductDesk", "ExtendedCatalog", "ProductContentID" });
// }
//}
////unit.Save();
//log.Debug("Finished processing existing content");
#endregion
var newContent = (from c in contentList
where !curContent.ContainsKey(c.Key)
select c.Value).ToList();
log.DebugFormat("Inserting {0} rows for connector {1}", newContent.Count(), conn.ConnectorID);
foreach (var c in newContent)
{
pDb.Insert("Content", "ProductID, ConnectorID", false, new { ProductID = c.ProductID, ConnectorID = c.ConnectorID, c.ProductContentID, c.ShortDescription, c.LongDescription, c.ExtendedCatalog, c.LedgerClass, c.LineType, c.ProductDesk, CreatedBy = c.CreatedBy });
}
#region contentproductgroups
try
{
var contentProductGroups = pDb.Fetch <ContentProductGroup>("SELECT * FROM ContentProductGroup WHERE IsCustom = 0 AND ConnectorID = @0", conn.ConnectorID);
//List<ProductGroupMapping> productGroupMappings = pDb.Fetch<ProductGroupMapping>("SELECT * FROM ProductGroupMapping WHERE Depth = 0 AND (ConnectorID = @0 OR ConnectorID = @1)",
// conn.ConnectorID, conn.ParentConnectorID ?? conn.ConnectorID);
List <ProductGroupMapping> productGroupMappings = pDb.Fetch <ProductGroupMapping>("SELECT * FROM ProductGroupMapping WHERE (ConnectorID = @0 OR ConnectorID = @1)",
conn.ConnectorID, conn.ParentConnectorID ?? conn.ConnectorID);
Dictionary <int, List <int> > MappingProductGroup = (
from c in productGroupMappings
group c by c.ProductGroupID into gr
select new { gr.Key, Value = gr.Select(c => c.ProductGroupMappingID) })
.ToDictionary(c => c.Key, c => c.Value.ToList());
//Start assortment tree processes
var tree = BuildTree(productGroupMappings);
var products = GetContentsPerMapping(contentList.Values.ToList(), productGroupMappings, MappingProductGroup);
//add products to tree
AddProductsToTree(tree, products, contentList.Values.ToList(), MappingProductGroup);
//end assortment tree processes
var toSync = tree.FlattenTreeToList(conn.ConnectorID, 1);
SyncNewContentProductGroups(toSync, pDb, conn.ConnectorID);
log.DebugFormat("Finish Remove unused contentproductgroups");
}
catch (Exception ex)
{
log.Fatal("Error processing contentproductgroups for connectorID" + conn.ConnectorID.ToString(), ex);
}
#endregion
log.DebugFormat("Finish insert assortment for connector {0}: {1}", conn.ConnectorID, conn.Name);
if (conn.ConnectorSettings.GetValueByKey <bool>("SyncContentProductGroupsWithParentConnector", false))
{
SyncCustomContentProducts(conn, pDb);
}
}
}
}
catch (Exception ex)
{
log.Fatal("Processs assortment error", ex);
}
}
}
public static Ride add(
int bike_id,
int rider_id,
int route_id,
DateTime ride_date,
String notes,
decimal reward,
double distance)
{
Ride ride = new Ride();
ride.bike_id = bike_id;
ride.rider_id = rider_id;
ride.route_id = route_id;
//if no names supplied then look up from ids
ride.bike = Bike.getBike(bike_id).name;
ride.rider = Rider.getRider(rider_id).name;
ride.route = Route.getRoute(route_id).name;
ride.ride_date = ride_date;
ride.notes = notes;
ride.distance = distance;
ride.reward = reward;
ride.paid = false;
using (Database db = new PetaPoco.Database(ModelConfig.connectionStringName("bikes")))
{
db.Insert(ride);
}
return ride;
}
public void Insert(Contato novoContato)
{
Database db = new PetaPoco.Database("PRWDEV");
db.Insert("contatosbandre", "ID", novoContato);
}
/// <summary>
/// Inserts or updates multiple instances of Currency class on the database table "core.currencies";
/// </summary>
/// <param name="currencies">List of "Currency" class to import.</param>
/// <returns></returns>
public List<object> BulkImport(List<ExpandoObject> currencies)
{
if (!this.SkipValidation)
{
if (!this.Validated)
{
this.Validate(AccessTypeEnum.ImportData, this._LoginId, this._Catalog, false);
}
if (!this.HasAccess)
{
Log.Information("Access to import entity \"Currency\" was denied to the user with Login ID {LoginId}. {currencies}", this._LoginId, currencies);
throw new UnauthorizedException("Access is denied.");
}
}
var result = new List<object>();
int line = 0;
try
{
using (Database db = new Database(Factory.GetConnectionString(this._Catalog), Factory.ProviderName))
{
using (Transaction transaction = db.GetTransaction())
{
foreach (dynamic currency in currencies)
{
line++;
currency.audit_user_id = this._UserId;
currency.audit_ts = System.DateTime.UtcNow;
object primaryKeyValue = currency.currency_code;
if (!string.IsNullOrWhiteSpace(currency.currency_code))
{
result.Add(currency.currency_code);
db.Update("core.currencies", "currency_code", currency, currency.currency_code);
}
else
{
result.Add(db.Insert("core.currencies", "currency_code", currency));
}
}
transaction.Complete();
}
return result;
}
}
catch (NpgsqlException ex)
{
string errorMessage = $"Error on line {line} ";
if (ex.Code.StartsWith("P"))
{
errorMessage += Factory.GetDBErrorResource(ex);
throw new MixERPException(errorMessage, ex);
}
errorMessage += ex.Message;
throw new MixERPException(errorMessage, ex);
}
catch (System.Exception ex)
{
string errorMessage = $"Error on line {line} ";
throw new MixERPException(errorMessage, ex);
}
}
/// <summary>
/// Inserts or updates multiple instances of ScrudFactory class on the database table "config.scrud_factory";
/// </summary>
/// <param name="scrudFactories">List of "ScrudFactory" class to import.</param>
/// <returns></returns>
public List<object> BulkImport(List<MixERP.Net.Entities.Config.ScrudFactory> scrudFactories)
{
if (!this.SkipValidation)
{
if (!this.Validated)
{
this.Validate(AccessTypeEnum.ImportData, this.LoginId, false);
}
if (!this.HasAccess)
{
Log.Information("Access to import entity \"ScrudFactory\" was denied to the user with Login ID {LoginId}. {scrudFactories}", this.LoginId, scrudFactories);
throw new UnauthorizedException("Access is denied.");
}
}
var result = new List<object>();
int line = 0;
try
{
using (Database db = new Database(Factory.GetConnectionString(this.Catalog), Factory.ProviderName))
{
using (Transaction transaction = db.GetTransaction())
{
foreach (var scrudFactory in scrudFactories)
{
line++;
scrudFactory.AuditUserId = this.UserId;
scrudFactory.AuditTs = System.DateTime.UtcNow;
if (!string.IsNullOrWhiteSpace(scrudFactory.Key))
{
result.Add(scrudFactory.Key);
db.Update(scrudFactory, scrudFactory.Key);
}
else
{
result.Add(db.Insert(scrudFactory));
}
}
transaction.Complete();
}
return result;
}
}
catch (NpgsqlException ex)
{
string errorMessage = $"Error on line {line} ";
if (ex.Code.StartsWith("P"))
{
errorMessage += Factory.GetDBErrorResource(ex);
throw new MixERPException(errorMessage, ex);
}
errorMessage += ex.Message;
throw new MixERPException(errorMessage, ex);
}
catch (System.Exception ex)
{
string errorMessage = $"Error on line {line} ";
throw new MixERPException(errorMessage, ex);
}
}
public ActionResult SubmitLease()
{
// Pick up parameters
var price = -1;
int.TryParse(Request.Params["price"], out price);
// Validation
var hasError = false;
var errorMessage = "";
// Validate the email with MailMessage
var courriel = "*****@*****.**";
try
{
var message = new MailMessage { From = new MailAddress(courriel) };
}
catch
{
hasError = true;
errorMessage += "Invalid email format" + "<br/>";
}
// Required fields
if (string.IsNullOrWhiteSpace(courriel) || price == -1)
{
hasError = true;
errorMessage += "All fields required" + "<br/>";
}
if (hasError)
{
return Content("no - validation failed - " + errorMessage);
}
// Database entry
var db = new PetaPoco.Database("umbracoDbDSN");
var lease = new Lease();
// lease.Id = "";
lease.Price = price;
lease.DateCreated = DateTime.Now;
lease.IpAddress = Request.ServerVariables["REMOTE_ADDR"];
lease.StartDate = new DateTime(2014, 7, 1);
lease.EndDate = new DateTime(2015, 7, 1);
lease.AddressNbr = "";
lease.Street = "";
lease.AppartmentNbr = "";
lease.Street = "";
lease.PostalCode = "";
lease.City = "Montréal";
Borough borough = new Borough();
borough.Id = 9;
borough.Name = "Mercier–Hochelaga-Maisonneuve";
lease.Borough = borough;
lease.Details = "";
lease.Inclusions = "";
db.Insert(lease);
return Content("ok");
}
/// <summary>
/// Inserts or updates multiple instances of LeaveApplication class on the database table "hrm.leave_applications";
/// </summary>
/// <param name="leaveApplications">List of "LeaveApplication" class to import.</param>
/// <returns></returns>
public List<object> BulkImport(List<MixERP.Net.Entities.HRM.LeaveApplication> leaveApplications)
{
if (!this.SkipValidation)
{
if (!this.Validated)
{
this.Validate(AccessTypeEnum.ImportData, this._LoginId, false);
}
if (!this.HasAccess)
{
Log.Information("Access to import entity \"LeaveApplication\" was denied to the user with Login ID {LoginId}. {leaveApplications}", this._LoginId, leaveApplications);
throw new UnauthorizedException("Access is denied.");
}
}
var result = new List<object>();
int line = 0;
try
{
using (Database db = new Database(Factory.GetConnectionString(this._Catalog), Factory.ProviderName))
{
using (Transaction transaction = db.GetTransaction())
{
foreach (var leaveApplication in leaveApplications)
{
line++;
leaveApplication.AuditUserId = this._UserId;
leaveApplication.EnteredBy = this._UserId;
leaveApplication.AuditTs = System.DateTime.UtcNow;
if (leaveApplication.LeaveApplicationId > 0)
{
result.Add(leaveApplication.LeaveApplicationId);
db.Update(leaveApplication, leaveApplication.LeaveApplicationId);
}
else
{
result.Add(db.Insert(leaveApplication));
}
}
transaction.Complete();
}
return result;
}
}
catch (NpgsqlException ex)
{
string errorMessage = $"Error on line {line} ";
if (ex.Code.StartsWith("P"))
{
errorMessage += Factory.GetDBErrorResource(ex);
throw new MixERPException(errorMessage, ex);
}
errorMessage += ex.Message;
throw new MixERPException(errorMessage, ex);
}
catch (System.Exception ex)
{
string errorMessage = $"Error on line {line} ";
throw new MixERPException(errorMessage, ex);
}
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
Int64 iTicks = 1;
byte[] by = new byte[] { 0x4b, 0x0d, 0x0a };
// 打开所有串口
foreach (KeyValuePair<string, SerialPortManager> item in dicSerialPort)
{
try
{
item.Value.StartListening(item.Key, 9600, System.IO.Ports.Parity.None, 8, System.IO.Ports.StopBits.One);
}
catch (Exception err)
{
tbData.SafeCall(delegate()
{
tbData.AppendText("初始化串口" + item.Key + "发生异常: " + err.Message + Environment.NewLine);
});
}
}
while (isRun)
{
foreach (KeyValuePair<string, SerialPortManager> item in dicSerialPort)
{
try
{
item.Value.SendMsg(by);
}
catch (Exception err)
{
tbData.SafeCall(delegate()
{
tbData.AppendText("向串口" + item.Key + "发送数据失败: " + err.Message);
});
}
}
tbData.SafeCall(delegate()
{
tbData.Clear();
});
foreach (KeyValuePair<string, SerialPortManager> item in dicSerialPort)
{
try
{
string sData = item.Value.ReadData();
tbData.SafeCall(delegate()
{
tbData.AppendText(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss ") + item.Key + ": " + sData + Environment.NewLine);
});
if (sData.Length > 20)
{
try
{
string sDataTmp = sData.Substring(0, sData.Length - 4);
// 写入数据库
PetaPoco.Database db = new Database("XE");
DEVICECOMMUNICATION_LOG log = new DEVICECOMMUNICATION_LOG();
log.MSG = sData.Length > 3999 ? sData.Substring(0, 3999) : sData;
log.REMOTE_IP = toolStripTextBox1.Text + item.Key;
//log.REMOTE_PORT = remotePort;
log.RECEIVE_TIME = DateTime.Now;
db.Insert(log);
// 写入到台湾人要的表
var log1 = GetLog1Entity(sDataTmp, item.Key);
db.Insert(log1);
if (iTicks % 60 == 0)
{
BLOODCLEANUP_TEMP bt = new BLOODCLEANUP_TEMP();
bt = db.SingleOrDefault<BLOODCLEANUP_TEMP>("where to_char( ANA_DATE, 'yyyy-mm-dd') = @0 and SERIAL_PORT_NUM = @1",
new object[] { DateTime.Now.ToString("yyyy-MM-dd"), toolStripTextBox1.Text + item.Key });
if (bt != null)
{
string sTmp = sDataTmp.Substring(log.MSG.LastIndexOf('F') + 1, 5);
string sVp = sDataTmp.Substring(log.MSG.LastIndexOf('H') + 1, 5);
string sBf = sDataTmp.Substring(log.MSG.LastIndexOf('D') + 1, 5);
string sMaxBp = sDataTmp.Substring(log.MSG.LastIndexOf('N') + 1, 5);
string sMinBp = sDataTmp.Substring(log.MSG.LastIndexOf('O') + 1, 5);
string sPulse = sDataTmp.Substring(log.MSG.LastIndexOf('P') + 1, 5);
string sTotalUFAmount = sDataTmp.Substring(log.MSG.LastIndexOf('B') + 1, 5);
string sDC = sDataTmp.Substring(log.MSG.LastIndexOf('G') + 1, 5);
string sTMP = sDataTmp.Substring(log.MSG.LastIndexOf('J') + 1, 5);
BLOODCLEANUP_PROCESS proc = new BLOODCLEANUP_PROCESS();
proc.ANA_TIME = log.RECEIVE_TIME;
proc.TEMP = decimal.Parse(sTmp);
proc.VENOUS_PRESSURE = decimal.Parse(sVp);
proc.BLOOD_FLOW = decimal.Parse(sBf);
proc.BP = decimal.Parse(sMaxBp).ToString() + "~" + decimal.Parse(sMinBp);
proc.P = decimal.Parse(sPulse);
proc.ULTRAFILTRATION = decimal.Parse(sTotalUFAmount);
proc.CONDUCTIVITY = sDC;
proc.ARTERIAL_PRESSURE = decimal.Parse(sTMP);
proc.LOG_TIME = DateTime.Now;
proc.BLOODCLEANUP_ID = bt.BLOOD_CLEANUP_ID;
db.Insert(proc);
//proc.OPERATOR = ClsFrmMng.WorkerID;
}
}
}
catch (Exception err)
{
tbData.SafeCall(delegate()
{
tbData.AppendText("写数据失败: " + err.Message + Environment.NewLine);
});
}
}
}
catch (Exception err)
{
tbData.SafeCall(delegate()
{
tbData.AppendText("读取串口" + item.Key + "数据失败: " + err.Message + Environment.NewLine);
});
}
}
Thread.Sleep(3000);
iTicks++;
}
}
public void RecreateFilters(string objectName, string filterName, List<MixERP.Net.Entities.Core.Filter> filters)
{
if (!this.SkipValidation)
{
if (!this.Validated)
{
this.Validate(AccessTypeEnum.Create, this._LoginId, this._Catalog, false);
}
if (!this.HasAccess)
{
Log.Information("Access to add entity \"Filter\" was denied to the user with Login ID {LoginId}. {filters}", this._LoginId, filters);
throw new UnauthorizedException("Access is denied.");
}
}
using (Database db = new Database(Factory.GetConnectionString(this._Catalog), Factory.ProviderName))
{
using (Transaction transaction = db.GetTransaction())
{
var toDelete = this.GetWhere(1, new List<EntityParser.Filter>
{
new EntityParser.Filter { ColumnName = "object_name", FilterCondition = (int) FilterCondition.IsEqualTo, FilterValue = objectName },
new EntityParser.Filter { ColumnName = "filter_name", FilterCondition = (int) FilterCondition.IsEqualTo, FilterValue = filterName }
});
foreach (var filter in toDelete)
{
db.Delete(filter);
}
foreach (var filter in filters)
{
filter.AuditUserId = this._UserId;
filter.AuditTs = System.DateTime.UtcNow;
db.Insert(filter);
}
transaction.Complete();
}
}
}
/// <summary>
/// Inserts or updates multiple instances of AttachmentLookup class on the database table "core.attachment_lookup";
/// </summary>
/// <param name="attachmentLookups">List of "AttachmentLookup" class to import.</param>
/// <returns></returns>
public List<object> BulkImport(List<MixERP.Net.Entities.Core.AttachmentLookup> attachmentLookups)
{
if (!this.SkipValidation)
{
if (!this.Validated)
{
this.Validate(AccessTypeEnum.ImportData, this.LoginId, false);
}
if (!this.HasAccess)
{
Log.Information("Access to import entity \"AttachmentLookup\" was denied to the user with Login ID {LoginId}. {attachmentLookups}", this.LoginId, attachmentLookups);
throw new UnauthorizedException("Access is denied.");
}
}
var result = new List<object>();
int line = 0;
try
{
using (Database db = new Database(Factory.GetConnectionString(this.Catalog), Factory.ProviderName))
{
using (Transaction transaction = db.GetTransaction())
{
foreach (var attachmentLookup in attachmentLookups)
{
line++;
if (attachmentLookup.AttachmentLookupId > 0)
{
result.Add(attachmentLookup.AttachmentLookupId);
db.Update(attachmentLookup, attachmentLookup.AttachmentLookupId);
}
else
{
result.Add(db.Insert(attachmentLookup));
}
}
transaction.Complete();
}
return result;
}
}
catch (NpgsqlException ex)
{
string errorMessage = $"Error on line {line} ";
if (ex.Code.StartsWith("P"))
{
errorMessage += Factory.GetDBErrorResource(ex);
throw new MixERPException(errorMessage, ex);
}
errorMessage += ex.Message;
throw new MixERPException(errorMessage, ex);
}
catch (System.Exception ex)
{
string errorMessage = $"Error on line {line} ";
throw new MixERPException(errorMessage, ex);
}
}
/// <summary>
/// Inserts or updates multiple instances of WidgetGroup class on the database table "core.widget_groups";
/// </summary>
/// <param name="widgetGroups">List of "WidgetGroup" class to import.</param>
/// <returns></returns>
public List<object> BulkImport(List<ExpandoObject> widgetGroups)
{
if (!this.SkipValidation)
{
if (!this.Validated)
{
this.Validate(AccessTypeEnum.ImportData, this._LoginId, this._Catalog, false);
}
if (!this.HasAccess)
{
Log.Information("Access to import entity \"WidgetGroup\" was denied to the user with Login ID {LoginId}. {widgetGroups}", this._LoginId, widgetGroups);
throw new UnauthorizedException("Access is denied.");
}
}
var result = new List<object>();
int line = 0;
try
{
using (Database db = new Database(Factory.GetConnectionString(this._Catalog), Factory.ProviderName))
{
using (Transaction transaction = db.GetTransaction())
{
foreach (dynamic widgetGroup in widgetGroups)
{
line++;
object primaryKeyValue = widgetGroup.widget_group_name;
if (!string.IsNullOrWhiteSpace(widgetGroup.widget_group_name))
{
result.Add(widgetGroup.widget_group_name);
db.Update("core.widget_groups", "widget_group_name", widgetGroup, widgetGroup.widget_group_name);
}
else
{
result.Add(db.Insert("core.widget_groups", "widget_group_name", widgetGroup));
}
}
transaction.Complete();
}
return result;
}
}
catch (NpgsqlException ex)
{
string errorMessage = $"Error on line {line} ";
if (ex.Code.StartsWith("P"))
{
errorMessage += Factory.GetDBErrorResource(ex);
throw new MixERPException(errorMessage, ex);
}
errorMessage += ex.Message;
throw new MixERPException(errorMessage, ex);
}
catch (System.Exception ex)
{
string errorMessage = $"Error on line {line} ";
throw new MixERPException(errorMessage, ex);
}
}
private void btnOK_Click(object sender, EventArgs e)
{
if (MsgBox.ShowYesNoMessage(null, string.Format("确定要保存对角色：{0} 的修改吗?", roleGridEdit2.SelectedValue.Name))
== System.Windows.Forms.DialogResult.No)
return;
var role = roleGridEdit2.SelectedValue;
using (var db = new Database()) {
try {
db.BeginTransaction();
db.Execute("delete from t_roleuser where RoleId=@0", role.Id);
var ets = gridalloc.GetDataSource<t_user>();
if (null != ets && ets.Count > 0)
foreach (var k in ets)//.ForEach(k =>
{
db.Insert(new t_roleuser {
Guid = Guid.NewGuid(),
RoleId = role.Id,
UserId = k.Id,
IsUsing = true,
Creator = this.CurUser,
CreateDate = TimeSync.Default.CurrentSyncTime
});
};
db.CompleteTransaction();
} catch (Exception ex) {
db.AbortTransaction();
throw;
}
}
MsgBox.ShowMessage(null, "保存成功!");
}
/// <summary>
/// Inserts or updates multiple instances of ExchangeRate class on the database table "core.exchange_rates";
/// </summary>
/// <param name="exchangeRates">List of "ExchangeRate" class to import.</param>
/// <returns></returns>
public List<object> BulkImport(List<ExpandoObject> exchangeRates)
{
if (!this.SkipValidation)
{
if (!this.Validated)
{
this.Validate(AccessTypeEnum.ImportData, this._LoginId, this._Catalog, false);
}
if (!this.HasAccess)
{
Log.Information("Access to import entity \"ExchangeRate\" was denied to the user with Login ID {LoginId}. {exchangeRates}", this._LoginId, exchangeRates);
throw new UnauthorizedException("Access is denied.");
}
}
var result = new List<object>();
int line = 0;
try
{
using (Database db = new Database(Factory.GetConnectionString(this._Catalog), Factory.ProviderName))
{
using (Transaction transaction = db.GetTransaction())
{
foreach (dynamic exchangeRate in exchangeRates)
{
line++;
object primaryKeyValue = exchangeRate.exchange_rate_id;
if (Cast.To<long>(primaryKeyValue) > 0)
{
result.Add(exchangeRate.exchange_rate_id);
db.Update("core.exchange_rates", "exchange_rate_id", exchangeRate, exchangeRate.exchange_rate_id);
}
else
{
result.Add(db.Insert("core.exchange_rates", "exchange_rate_id", exchangeRate));
}
}
transaction.Complete();
}
return result;
}
}
catch (NpgsqlException ex)
{
string errorMessage = $"Error on line {line} ";
if (ex.Code.StartsWith("P"))
{
errorMessage += Factory.GetDBErrorResource(ex);
throw new MixERPException(errorMessage, ex);
}
errorMessage += ex.Message;
throw new MixERPException(errorMessage, ex);
}
catch (System.Exception ex)
{
string errorMessage = $"Error on line {line} ";
throw new MixERPException(errorMessage, ex);
}
}
/// <summary>
/// Inserts or updates multiple instances of WorkCenter class on the database table "office.work_centers";
/// </summary>
/// <param name="workCenters">List of "WorkCenter" class to import.</param>
/// <returns></returns>
public List<object> BulkImport(List<ExpandoObject> workCenters)
{
if (!this.SkipValidation)
{
if (!this.Validated)
{
this.Validate(AccessTypeEnum.ImportData, this._LoginId, this._Catalog, false);
}
if (!this.HasAccess)
{
Log.Information("Access to import entity \"WorkCenter\" was denied to the user with Login ID {LoginId}. {workCenters}", this._LoginId, workCenters);
throw new UnauthorizedException("Access is denied.");
}
}
var result = new List<object>();
int line = 0;
try
{
using (Database db = new Database(Factory.GetConnectionString(this._Catalog), Factory.ProviderName))
{
using (Transaction transaction = db.GetTransaction())
{
foreach (dynamic workCenter in workCenters)
{
line++;
workCenter.audit_user_id = this._UserId;
workCenter.audit_ts = System.DateTime.UtcNow;
object primaryKeyValue = workCenter.work_center_id;
if (Cast.To<int>(primaryKeyValue) > 0)
{
result.Add(workCenter.work_center_id);
db.Update("office.work_centers", "work_center_id", workCenter, workCenter.work_center_id);
}
else
{
result.Add(db.Insert("office.work_centers", "work_center_id", workCenter));
}
}
transaction.Complete();
}
return result;
}
}
catch (NpgsqlException ex)
{
string errorMessage = $"Error on line {line} ";
if (ex.Code.StartsWith("P"))
{
errorMessage += Factory.GetDBErrorResource(ex);
throw new MixERPException(errorMessage, ex);
}
errorMessage += ex.Message;
throw new MixERPException(errorMessage, ex);
}
catch (System.Exception ex)
{
string errorMessage = $"Error on line {line} ";
throw new MixERPException(errorMessage, ex);
}
}
public bool Save()
{
try
{
Database db = new Database("Database");
string destPath = Path.Combine(ConfigurationManager.AppSettings["destinationFolder"], this.FilePath, this.FileName);
if (!Directory.Exists(Path.GetDirectoryName(destPath))) Directory.CreateDirectory(Path.GetDirectoryName(destPath));
// If this throws an error the archive code is not working
fileInfo.MoveTo(destPath);
// Read from file
using (MagickImage image = new MagickImage(destPath))
{
MagickGeometry size = new MagickGeometry(200, 200);
size.IgnoreAspectRatio = true;
image.Resize(size);
image.Write(destPath + "_small");
}
using (MagickImage image = new MagickImage(destPath))
{
MagickGeometry size = new MagickGeometry(800, 800);
size.IgnoreAspectRatio = true;
image.Resize(size);
image.Write(destPath + "_medium");
}
FTP.UploadFile(this);
db.Insert(this);
return true;
}
catch (Exception ex)
{
Logger.WriteLine("Could not insert {0} into the database: {1}", this.Name, ex);
return false;
}
}
