void btnReView_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
var et = gcloginfail.GetFocusedDataSource<t_loginfail>();
if (null == et)
return;
if (MsgBox.ShowYesNoMessage(null,
string.Format("确定要将 {0} MAC:{1} 审核通过吗?", et.UserName, et.LoginMAC)) == System.Windows.Forms.DialogResult.No)
return;
var usr = GetCurUser<t_user>();
using (var db = new Database()) {
try {
db.BeginTransaction();
db.Delete("t_loginfail", "Id", et, et.Id);
db.Execute(sql_addmac, et.LoginMAC, usr.UserName, usr.Guid);
db.CompleteTransaction();
} catch (Exception) {
db.AbortTransaction();
#if DEBUG
throw;
#endif
}
}
using (var db = new Database()) {
var etfail = db.Fetch<t_loginfail>("select * from t_loginfail");
gcloginfail.DataSource = etfail;
gcloginfail.RefreshDataSource();
var etmac = db.Fetch<t_loginfail>("select * from t_mac");
gcMac.DataSource = etmac;
gcMac.RefreshDataSource();
}
}
public void CreateTables()
{
using (var db = new Database("DataSource=\"test.sdf\"; Password=\"chrissiespassword\"", "System.Data.SqlServerCe.4.0")) {
db.Execute("CREATE TABLE Person (Id int IDENTITY(1,1) PRIMARY KEY, LastName nvarchar (40) NOT NULL, FirstName nvarchar (40), AddressId int NOT NULL);");
db.Execute("CREATE TABLE Address (Id int IDENTITY(1,1) PRIMARY KEY, Street nvarchar (40) NOT NULL, HouseNumber nvarchar (10));");
}
}
/// <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);
}
protected override void ResetDatabase()
{
using (var database = new Database("SQLiteTest"))
{
database.BeginTransaction();
database.Execute(new Sql("DELETE FROM Entity"));
database.Execute(new Sql("DELETE FROM sqlite_sequence WHERE name = @0", "Entity"));
database.CompleteTransaction();
}
}
public static void deleteRoute(int id)
{
using (Database db = new PetaPoco.Database(ModelConfig.connectionStringName("bikes")))
{
db.Execute("UPDATE route SET deleted = TRUE WHERE id = @0", id);
}
}
public void SetPoints(int trackId, TrackPoint[] points)
{
var sb = new StringBuilder();
for (int i = 0; i < points.Length; i++)
{
if (i % 1000 == 0)
{
if(sb.Length>0) sb.Remove(sb.Length - 1, 1);
sb.AppendLine(";").AppendLine("insert into MR.tTrackPoint (TrackId, Idx, Lat, Lon, Elevation, PointTime) values ");
}
var p = points[i];
sb.AppendLine().Append("(")
.Append(trackId).Append(",")
.Append(i).Append(",")
.Append(p.Lat.ToString(CultureInfo.InvariantCulture)).Append(",")
.Append(p.Lon.ToString(CultureInfo.InvariantCulture)).Append(",")
.Append(p.Elevation.ToString(CultureInfo.InvariantCulture)).Append(",")
.Append("'").Append(p.PointTime.ToString("o",CultureInfo.InvariantCulture)).Append("'")
.Append("),");
}
var sql = sb.ToString().TrimEnd(',');
var db = new Database(_config.ConnectionString, "System.Data.SqlClient");
try
{
db.Execute(sql);
}
catch (SqlException ex)
{
throw new Exception(sql, ex);
}
}
public void CreateDatabaseAndStuff()
{
//creating the database
_createDatabaseConn = new Database("create_db_conn");
_createDatabaseConn.Execute("CREATE DATABASE TavernWenchTestDb");
//creating test tables
_testDatabaseConn = new Database("test_db_conn");
_testDatabaseConn.Execute(@"CREATE TABLE Fruit (
Name VARCHAR(60),
Color VARCHAR(60)
);");
_testDatabaseConn.Execute(@"CREATE TABLE Actor (
Id INT IDENTITY(1,1) NOT NULL,
LastName VARCHAR(60)
);");
}
public static void SaveOffice(string catalog, string regionalDataFile, string officeCode, string officeName,
string nickName,
DateTime registrationDate, string currencyCode, string currencySymbol, string currencyName,
string hundredthName, string fiscalYearCode,
string fiscalYearName, DateTime startsFrom, DateTime endsOn,
bool salesTaxIsVat, bool hasStateSalesTax, bool hasCountySalesTax,
int quotationValidDays, decimal incomeTaxRate, int weekStartDay, DateTime transactionStartDate,
bool isPerpetual, string valuationMethod, string logo,
string adminName, string username, string password)
{
try
{
using (Database db = new Database(Factory.GetConnectionString(catalog), Factory.ProviderName))
{
using (Transaction transaction = db.GetTransaction())
{
string sql = File.ReadAllText(regionalDataFile, Encoding.UTF8);
db.Execute(sql);
sql =
"SELECT * FROM office.add_office(@0::varchar(12), @1::varchar(150), @2::varchar(50), @3::date, @4::varchar(12), @5::varchar(12), @6::varchar(48), @7::varchar(48), @8::varchar(12), @9::varchar(50), @10::date,@11::date, @12::boolean, @13::boolean, @14::boolean, @15::integer, @16::numeric, @17::integer, @18::date, @19::boolean, @20::character varying(5), @21::text, @22::varchar(100), @23::varchar(50), @24::varchar(48));";
db.Execute(sql, officeCode, officeName, nickName, registrationDate, currencyCode,
currencySymbol, currencyName, hundredthName, fiscalYearCode, fiscalYearName, startsFrom,
endsOn,
salesTaxIsVat, hasStateSalesTax, hasCountySalesTax, quotationValidDays,
incomeTaxRate, weekStartDay, transactionStartDate, isPerpetual, valuationMethod, logo,
adminName,
username, password);
transaction.Complete();
}
}
}
catch (NpgsqlException ex)
{
if (ex.Code.StartsWith("P"))
{
string errorMessage = Factory.GetDBErrorResource(ex);
throw new MixERPException(errorMessage, ex);
}
throw;
}
}
/// <summary>
/// 存入中獎名單
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnSave_Click(object sender, EventArgs e)
{
PetaPoco.Sql sql = PetaPoco.Sql.Builder;
sql.Append(string.Format("UPDATE {0} SET {1}='1' WHERE {2} IN (SELECT MAX({2}) FROM {0} WHERE {4} IN ({3}) GROUP BY {4})", TableName, WinFlagColumn, PKColumn, Session["DrawIDs"], DistinctColumn));
patwGridView1.Visible = false;
btnExport.Visible = false;
btnSave.Visible = false;
db.Execute(sql);
PatwCommon.RegisterClientScriptAlert(this.Page, "存入成功");
Query();
}
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);
}
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);
}
}
protected void patwGridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.ToLower() == "exporttoexcel")
{
BackendSearchControl.Control_Binding(this.ViewState, plSearch);
AspNetPager1.CurrentPageIndex = 1;
Query();
}
if (e.CommandName == "UpdatasValid")
{
db.Execute(String.Format("UPDATE {0} SET sValid=(sValid+1)%2 WHERE sID=@0", TableName), e.CommandArgument);
Query(AspNetPager1.CurrentPageIndex);
}
}
private void btnOK_Click(object sender, EventArgs e) {
if (!dxValidationProvider1.Validate()) { return; }
if (txtPassword.Text != txtComfirmPassword.Text) {
MsgBox.ShowMessage("","两次输入的密码不一致!");
return;
}
try {
using (var db = new Database()) {
db.Execute("update t_user set Pwd=@0 where Username=@1", Ultra.Surface.Common.Util.EncryptPwd(txtPassword.Text), this.CurUser);
}
this.DialogResult = System.Windows.Forms.DialogResult.OK;
Close();
} catch (Exception ex) {
throw ex;
}
}
private void btnOK_Click(object sender, EventArgs e)
{
if (!dxValidationProvider1.Validate())
return;
var odrs = gcOrder.GetDataSource<t_retorder>();
if (odrs == null || odrs.Count < 1) {
MsgBox.ShowMessage("提示", "没有退货商品不能保存退货单!");
return;
}
if (odrs.Any(k=>string.IsNullOrEmpty(k.LocName))) {
MsgBox.ShowMessage("提示", "所有商品都必须选择库位!");
return;
}
if (EditMode == Ultra.Web.Core.Enums.EnViewEditMode.New) {
var rettrd = this.Trade;
rettrd.Guid = Guid.NewGuid();
rettrd.Id = 0;
rettrd.IsAudit = false;
using (var db = new Database()) {
try {
db.BeginTransaction();
db.Save(rettrd);
odrs.ForEach(k => {
k.Guid = Guid.NewGuid();
k.Id = 0;
k.TradeGuid = rettrd.Guid;
db.Save(k);
});
db.CompleteTransaction();
} catch (Exception) {
db.AbortTransaction();
throw;
}
}
} else if (EditMode == Ultra.Web.Core.Enums.EnViewEditMode.Edit) {
using (var db = new Database()) {
var trd = db.FirstOrDefault<t_rettrade>(" where guid=@0", GuidKey);
trd.ReceiverName = Trade.ReceiverName;
trd.ReceiverMobile = txtMobile.Text;
trd.ReceiverAddress = txtReceiverAddress.Text;
trd.MemberGuid = Trade.Guid;
trd.DeliveryDate = TimeSync.Default.CurrentSyncTime;
trd.DeliveryDate = dateDeliveryDate.DateTime;
try {
db.BeginTransaction();
db.Save(trd);
db.Execute("delete t_retorder where tradeguid=@0", trd.Guid);
odrs.ForEach(k => {
k.Id = 0;
db.Save(k);
});
db.CompleteTransaction();
} catch (Exception) {
db.AbortTransaction();
throw;
}
}
}
DialogResult = System.Windows.Forms.DialogResult.OK;
Close();
}
private void btnOK_Click(object sender, EventArgs e)
{
var items = gcItem.GetDataSource<t_instockitem>();
if (items == null || items.Count < 1) {
MsgBox.ShowMessage("", "没有商品信息,不能保存!");
return;
}
if (items.Any(K => string.IsNullOrEmpty(K.ItemNo))) {
MsgBox.ShowMessage("", "商品信息不完整!");
return;
}
if (EditMode == Web.Core.Enums.EnViewEditMode.Edit) {
using (var db = new Database()) {
try {
db.BeginTransaction();
db.Execute("delete t_instockitem where instockno=@0", InStockNo);
InStock.Remark = txtRemark.Text;
InStock.Num = items.Sum(k => k.Num);
InStock.OuterNo = txtOuterNo.Text;
db.Save(InStock);
items.ForEach(k => { k.Id = 0; db.Save(k); });
db.CompleteTransaction();
} catch (Exception) {
db.AbortTransaction();
throw;
}
}
} else {
InStock = new t_instock();
InStock.Remark = txtRemark.Text;
InStock.Guid = GuidKey;
InStock.InStockNo = InStockNo;
InStock.OuterNo = txtOuterNo.Text;
InStock.Creator = this.CurUser;
InStock.AuditDate = InStock.CreateDate = TimeSync.Default.CurrentSyncTime;
InStock.Num = items.Sum(k => k.Num);
using (var db = new Database()) {
try {
db.BeginTransaction();
db.Save(InStock);
items.ForEach(k => db.Save(k));
db.CompleteTransaction();
} catch (Exception) {
db.AbortTransaction();
throw;
}
}
}
DialogResult = DialogResult.OK;
Close();
}
public void SetPassword(string email, string passwordHash)
{
var db = new Database(_config.ConnectionString, "System.Data.SqlClient");
db.Execute("update MR.tUser set Passwd = @1 where Email = @0", email, passwordHash);
}
void btnReView_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
var trd = gcUnAudit.GetFocusedDataSource<t_rettrade>();
if (trd == null)
return;
using (var db = new Database()) {
try {
db.BeginTransaction();
//入库更新库存
db.Execute("exec p_retgoodsupdateinvt @0", trd.Guid);
db.Execute("update t_rettrade set isaudit=1 where guid=@0", trd.Guid);
db.CompleteTransaction();
gcUnAudit.RemoveSelected();
} catch (Exception) {
db.AbortTransaction();
throw;
}
}
}
void btnInvalid_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
var trd = gcUnAudit.GetFocusedDataSource<t_rettrade>();
if (trd == null)
return;
using (var db = new Database()) {
db.Execute("update t_rettrade set isinvalid=1 where guid=@0", trd.Guid);
}
gcUnAudit.RemoveSelected();
}
public void setPwd(string newPwd)
{
RNGCryptoServiceProvider csp = new RNGCryptoServiceProvider();
HashAlgorithm algorithm = SHA256.Create();
byte[] src = Encoding.Unicode.GetBytes(newPwd);
byte[] hash = algorithm.ComputeHash(src);
string pwd = Convert.ToBase64String(hash);
using (Database db = new PetaPoco.Database(ModelConfig.connectionStringName("bikes")))
{
db.Execute("UPDATE rider SET pwd=@1 WHERE id = @0", id, pwd);
}
}
public void SetApiKey(int userId, string apiKey)
{
var db = new Database(_config.ConnectionString, "System.Data.SqlClient");
db.Execute("update MR.tUser set ApiKey = @1 where UserId = @0", userId, apiKey);
}
private void btnOK_Click(object sender, EventArgs e)
{
if (!dxValidationProvider1.Validate())
return;
var odrs = gcOrder.GetDataSource<t_order>();
if (odrs == null || odrs.Count < 1) {
MsgBox.ShowMessage("提示", "没有添加商品不能保存!");
return;
}
if (EditMode == Ultra.Web.Core.Enums.EnViewEditMode.New) {
var trdnew = new t_trade() {
Guid = GuidKey,
ReceiverName = Trade.ReceiverName,
ReceiverMobile = Trade.ReceiverMobile,
ReceiverAddress = Trade.ReceiverAddress,
MemberGuid = Trade.Guid,
DeliveryDate = dateDeliveryDate.DateTime,
CreateDate = TimeSync.Default.CurrentSyncTime,
Creator = this.CurUser,
};
using (var db = new Database()) {
try {
db.BeginTransaction();
db.Save(trdnew);
odrs.ForEach(k => db.Save(k));
db.CompleteTransaction();
} catch (Exception) {
db.AbortTransaction();
throw;
}
}
} else if (EditMode == Ultra.Web.Core.Enums.EnViewEditMode.Edit) {
using (var db = new Database()) {
var trd = db.FirstOrDefault<t_trade>(" where guid=@0", GuidKey);
trd.ReceiverName = Trade.ReceiverName;
trd.ReceiverMobile = txtMobile.Text;
trd.ReceiverAddress = txtReceiverAddress.Text;
trd.MemberGuid = Trade.Guid;
trd.DeliveryDate = TimeSync.Default.CurrentSyncTime;
trd.DeliveryDate = dateDeliveryDate.DateTime;
try {
db.BeginTransaction();
db.Save(trd);
db.Execute("delete t_order where tradeguid=@0", trd.Guid);
odrs.ForEach(k => {
k.Id = 0;
db.Save(k);
});
db.CompleteTransaction();
} catch (Exception) {
db.AbortTransaction();
throw;
}
}
}
DialogResult = System.Windows.Forms.DialogResult.OK;
Close();
}
public void CreateDB()
{
db = new Database(_connectionStringName);
db.OpenSharedConnection(); // <-- Wow, this is crucial to getting SqlCE to perform.
db.Execute(Utils.LoadTextResource(string.Format("PetaPoco.Tests.{0}_init.sql", _connectionStringName)));
}
private void btnOK_Click(object sender, EventArgs e)
{
if (!dxValidationProvider1.Validate())
return;
Common.GetMACs(out LocalMAC);
SqlHelper.ExecuteNonQuery(ConnString, CommandType.Text, "delete t_forcedoffline where LoginMAC=@LoginMAC", new SqlParameter("@LoginMAC", LocalMAC));
Common.GetLocalIpv4(out LocalIP);
//try { RemoteIP = Common.GetRemoteIP(); }
//catch { RemoteIP = string.Empty; }
RemoteIP = string.Empty;
var pwd = Util.EncryptPwd(txtpwd.Text);
using (var db = new Database())
{
db.Execute(Sql_AddDef);
var kt = db.FirstOrDefault<t_user>("select * from t_user where username=@0 and pwd=@1 and IsUsing=1", txtAct.Text.Trim(), pwd);
if (null == kt)
{
MsgBox.ShowMessage("登录无效", "无效的登录名或密码不正确");
return;
}
DialogResult = System.Windows.Forms.DialogResult.OK;
this.Cacher.Put<t_user>("CurrentUser", kt);
this.Cacher.Put<string>("CurUser", kt.UserName);
if (!"admin".EqualIgnorCase(kt.UserName))
{
//读取权限信息
var rst = db.Fetch<t_roleset>("select * from t_roleset where RoleId in (select RoleId from t_roleuser where userId=@0)", kt.Id);
var rle = db.Fetch<t_role>("select * from t_role where IsUsing=1");
rst = rst.Where(k => rle.Any(j => j.Id == k.RoleId)).ToList();
var rlet = rst.ToList();
List<MenuCtlData> mcd = null;
if (rlet.Count > 0)
{
mcd = new List<MenuCtlData>(rlet.Count);
rlet.ForEach(k =>
{
var ks = (Ultra.Web.Core.Common.ObjectHelper.DeSerialize<List<MenuCtlData>>(k.RoleSetTree));
ks = ks.Where(j => j.IsEnabled).ToList();
ks.ForEach(j =>
{
if (!mcd.Exists(m => m.ClsName == j.ClsName && m.ModName == j.ModName && j.CtlType == m.CtlType
&& j.ControlName == m.ControlName))
mcd.Add(j);
});
});
}
this.Cacher.Put<List<MenuCtlData>>("Permission", mcd);
}
}
this.OptConfig.Set<string>("LastLogin", txtAct.Text.Trim());
Close();
}
static public void CleanupTestDB()
{
Database db = new Database("openpermit");
db.Execute("DELETE FROM Permit");
db.Execute("DELETE FROM PermitStatus");
db.Execute("DELETE FROM Inspection");
}
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);
}
}
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);
}
}
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);
}
}
public static StatusCode deleteRide(int id)
{
StatusCode status = StatusCode.OK;
Ride ride = getRide(id);
//do not allow rides that have been paid to be deleted
if (ride != null && ride.payment_id == Payment.NullPaymentId)
{
using (Database db = new PetaPoco.Database(ModelConfig.connectionStringName("bikes")))
{
db.Execute("DELETE FROM ride WHERE id = @0", id);
}
}
else
{
status = StatusCode.ALREADY_PAID;
}
return status;
}
void btnInvalid_ItemClick(object sender, ItemClickEventArgs e)
{
var et = gcUnAudit.GetFocusedDataSource<t_instock>();
if (et == null)
return;
using (var db = new Database()) {
db.Execute(" update t_instock set isinvalid=1 where InStockNo=@0", et.InStockNo);
}
gcUnAudit.RemoveSelected();
}
void btnReView_ItemClick(object sender, ItemClickEventArgs e)
{
var et=gcUnAudit.GetFocusedDataSource<t_instock>();
if (et == null)
return;
using (var db = new Database()) {
try {
db.BeginTransaction();
db.Execute(" update t_instock set isaudit=1,auditdate=getdate() where instockno=@0", et.InStockNo);
//更新库存
db.Execute("exec p_instockupdateinvt @0", et.InStockNo);
db.CompleteTransaction();
gcUnAudit.RemoveSelected();
} catch (Exception) {
db.AbortTransaction();
throw;
}
}
}
void btnUsing_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
var et = gc.GetFocusedDataSource<t_location>();
if (et == null)
return;
using (var db = new Database()) {
db.Execute("update t_location set isusing=1 where guid = @0 ", et.Guid);
btnRefresh_ItemClick(null, null);
}
}
/// <summary>
/// 审核
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void btnReView_ItemClick(object sender, ItemClickEventArgs e)
{
var et = gcUnSub.GetFocusedDataSource<t_trade>();
if (null == et)
return;
if (MsgBox.ShowYesNoMessage("", "确定要审核此出货单?") == DialogResult.Yes) {
using (var db = new Database()) {
try {
var result = new SqlParameter() {
Direction = ParameterDirection.Output,
SqlDbType = SqlDbType.Bit,
ParameterName = "@result"
};
db.BeginTransaction();
db.Update<t_trade>(" set IsAudit=1 where guid=@0", et.Guid);
db.Execute("exec p_tradeupdateinvt @0,@1 output", et.Guid, result);
if (!(bool)result.Value) {
MsgBox.ShowMessage("", "库存不足!");
db.AbortTransaction();
} else {
db.CompleteTransaction();
gcUnSub.RemoveSelected();
}
} catch (Exception) {
db.AbortTransaction();
throw;
}
}
}
}
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, "保存成功!");
}
private static void DeletePreviousImports()
{
var db = new Database("RedRobin");
// have to delete the suckas in order to maintain referential integrity
db.Execute("delete from xmlimportingredients");
db.Execute("delete from xmlimportmenuitems");
db.Execute("Delete from xmlimports");
}