Пример #1
1
 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();
     }
 }
Пример #2
0
 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));");
     }
 }
Пример #3
0
        /// <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();
            }
        }
Пример #5
0
 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);
     }
 }
Пример #6
0
        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)
                                        );");
        }
Пример #8
0
        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;
            }
        }
Пример #9
0
    /// <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();
    }
Пример #10
0
		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);

		}
Пример #11
0
        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);
            }
        }
Пример #12
0
    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);
        }
    }
Пример #13
0
        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;
            }

        }
Пример #14
0
 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();
 }
Пример #15
0
        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();
        }
Пример #16
0
 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);
 }
Пример #17
0
 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;
         }
     }
 }
Пример #18
0
 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();
 }
Пример #19
0
        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);
            }
        }
Пример #20
0
 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);
 }
Пример #21
0
        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();
        }
Пример #22
0
		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)));
		}
Пример #23
0
        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");

        }
Пример #25
0
        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);

            }

        }
Пример #27
0
        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);
            }

        }
Пример #28
0
        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;
        }
Пример #29
0
 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();
 }
Пример #30
0
 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;
         }
     }
 }
Пример #31
0
 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);
     }
 }
Пример #32
0
        /// <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;
                    }
                }
            }
        }
Пример #33
0
 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, "保存成功!");
 }
Пример #34
0
 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");
 }