private void button1_Click(object sender, EventArgs e) { string db = Path.Combine(Utils.GetUserPath(), "alarm.db"); string sql = "SELECT COUNT(*) FROM sqlite_master where type = 'table' and name = 'record'"; object obj = SqlLiteHelper.ExecuteScalar(db, sql); if (Convert.ToInt32(obj) == 0) { string createSql = "CREATE TABLE record(time varchar(50),field varchar(50),groupname varchar(50),comment varchar(50),state varchar(50))"; int res = SqlLiteHelper.ExecuteNonQuery(db, createSql); } }
public void Init() { db = Path.Combine(Utils.GetUserPath(), "alarm.db"); string sql = "SELECT COUNT(*) FROM sqlite_master where type = 'table' and name = 'record'"; object obj = SqlLiteHelper.ExecuteScalar(db, sql); if (Convert.ToInt32(obj) == 0) { string createSql = "CREATE TABLE record(time varchar(50),field varchar(50),groupname varchar(50),comment varchar(50),state varchar(50))"; int res = SqlLiteHelper.ExecuteNonQuery(db, createSql); } foreach (Field field in ConfigData.allFields) { field.StateChanged += Field_StateChanged; } cdv3 = new CustomDataView(); cdv3.ContextMenuStrip = contextMenuStrip1; cdv3.Font = new Font("微软雅黑", 15); cdv3.Parent = this; cdv3.Dock = DockStyle.Fill; cdv3.CellFormatting += Cdv_CellFormatting; cdv = new CustomDataView(); cdv.ContextMenuStrip = contextMenuStrip1; cdv.Font = new Font("微软雅黑", 15); cdv.Parent = this; cdv.Dock = DockStyle.Fill; dt = SqlLiteHelper.ExecuteReader(db, "SELECT * FROM record"); cdv.DataSource = dt; cdv.Columns["time"].HeaderText = "时间"; cdv.Columns["field"].HeaderText = "监测项"; cdv.Columns["groupname"].HeaderText = "位置"; cdv.Columns["comment"].HeaderText = "备注"; cdv.Columns["state"].HeaderText = "状态"; cdv.CellFormatting += Cdv_CellFormatting; cdv.BringToFront(); }
/// <summary> /// 根据条件查询消费总金额 /// </summary> /// <param name="hash"></param> /// <param name="sqlWhere"></param> /// <returns></returns> public double GetTotalPrice(HashTableExp hash, string sqlWhere) { List <VLifingCost> list = new List <VLifingCost>(); string sql = "SELECT ifnull(round(SUM(Price),2),0) FROM V_LIFING_COST where 1=1 "; #region 查询条件 if (hash != null) { if (hash["Id"] != null) { sqlWhere += string.Format(" and Id='{0}'", hash["Id"]); } if (hash["Time"] != null) { sqlWhere += string.Format(" and TIME='{0}'", hash["Time"]); } if (hash["Reason"] != null) { sqlWhere += string.Format(" and Reason='{0}'", hash["Reason"]); } if (hash["Price"] != null) { sqlWhere += string.Format(" and Price='{0}'", hash["Price"]); } if (hash["CostTypeId"] != null) { sqlWhere += string.Format(" and Cost_Type_Id='{0}'", hash["CostTypeId"]); } if (hash["Notes"] != null) { sqlWhere += string.Format(" and Notes='{0}'", hash["Notes"]); } if (hash["ImgUrl"] != null) { sqlWhere += string.Format(" and Img_Url='{0}'", hash["ImgUrl"]); } if (hash["IsMark"] != null) { sqlWhere += string.Format(" and IsMark='{0}'", hash["IsMark"]); } if (hash["FamilyPay"] != null) { sqlWhere += string.Format(" and FamilyPay='{0}'", hash["FamilyPay"]); } if (hash["CreateBy"] != null) { sqlWhere += string.Format(" and Create_By='{0}'", hash["CreateBy"]); } if (hash["CreateTime"] != null) { sqlWhere += string.Format(" and Create_Time='{0}'", hash["CreateTime"]); } if (hash["UpDateBy"] != null) { sqlWhere += string.Format(" and UpDate_By='{0}'", hash["UpDateBy"]); } if (hash["UpDateTime"] != null) { sqlWhere += string.Format(" and UpDate_Time='{0}'", hash["UpDateTime"]); } if (hash["CusGroup"] != null) { sqlWhere += string.Format(" and CusGroup='{0}'", hash["CusGroup"]); } if (hash["CostTypeName"] != null) { sqlWhere += string.Format(" and COST_TYPE_NAME='{0}'", hash["CostTypeName"]); } if (hash["CreateName"] != null) { sqlWhere += string.Format(" and CREATE_NAME='{0}'", hash["CreateName"]); } if (hash["UpdateName"] != null) { sqlWhere += string.Format(" and UPDATE_NAME='{0}'", hash["UpdateName"]); } //格式2013-01 if (hash["YearMonth"] != null) { sqlWhere += string.Format(" and strftime('%Y-%m',Time) in({0})", hash["YearMonth"]); } //多个自定义分组用逗号分隔 if (hash["CusGroupMore"] != null) { sqlWhere += string.Format(" and CusGroup in({0})", hash["CusGroupMore"].GetIds()); } //不包含的标识 if (hash["CusGroupNo"] != null) { sqlWhere += string.Format(" and (CusGroup not in({0}) OR CusGroup IS NULL)", hash["CusGroupNo"].GetIds()); } } #endregion sql += sqlWhere; Object result = SqlLiteHelper.ExecuteScalar(sql, CommandType.Text); return(result.GetDouble()); }
/// <summary> /// 根据条件查询收入总金额 /// </summary> /// <param name="hash"></param> /// <param name="sqlWhere"></param> /// <returns></returns> public double GetTotalPrice(HashTableExp hash, string sqlWhere) { List <VIncome> list = new List <VIncome>(); string sql = "SELECT isnull(SUM(Price),0) FROM V_Income where 1=1 "; #region 查询条件 if (hash != null) { if (hash["Id"] != null) { sqlWhere += string.Format(" and Id='{0}'", hash["Id"]); } if (hash["Time"] != null) { sqlWhere += string.Format(" and TIME='{0}'", hash["Time"]); } if (hash["Price"] != null) { sqlWhere += string.Format(" and Price='{0}'", hash["Price"]); } if (hash["Note"] != null) { sqlWhere += string.Format(" and Note='{0}'", hash["Note"]); } if (hash["FamilyIncome"] != null) { sqlWhere += string.Format(" and FamilyIncome='{0}'", hash["FamilyIncome"]); } if (hash["IsMark"] != null) { sqlWhere += string.Format(" and IsMark='{0}'", hash["IsMark"]); } if (hash["CreateBy"] != null) { sqlWhere += string.Format(" and Create_By='{0}'", hash["CreateBy"]); } if (hash["CreateTime"] != null) { sqlWhere += string.Format(" and Create_Time='{0}'", hash["CreateTime"]); } if (hash["UpDateBy"] != null) { sqlWhere += string.Format(" and UpDate_By='{0}'", hash["UpDateBy"]); } if (hash["UpDateTime"] != null) { sqlWhere += string.Format(" and UpDate_Time='{0}'", hash["UpDateTime"]); } if (hash["CusGroup"] != null) { sqlWhere += string.Format(" and CusGroup='{0}'", hash["CusGroup"]); } if (hash["CreateName"] != null) { sqlWhere += string.Format(" and Create_Name='{0}'", hash["CreateName"]); } if (hash["UpdateName"] != null) { sqlWhere += string.Format(" and Update_Name='{0}'", hash["UpdateName"]); } //格式2013-01 if (hash["YearMonth"] != null) { sqlWhere += string.Format(" and substr(time,1,7)='{0}'", hash["YearMonth"]); } //多个自定义分组用逗号分隔 if (hash["CusGroupMore"] != null) { sqlWhere += string.Format(" and CusGroup in({0})", hash["CusGroupMore"].GetIds()); } //不包含的标识 if (hash["CusGroupNo"] != null) { sqlWhere += string.Format(" and (CusGroup not in({0}) OR CusGroup IS NULL)", hash["CusGroupNo"].GetIds()); } } #endregion sql += sqlWhere; Object result = SqlLiteHelper.ExecuteScalar(sql, CommandType.Text); return(result.GetDouble()); }