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());
}