private void 业主姓名ComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
this.业主姓名ComboBox.Items.Clear();
this.业主姓名ComboBox.Items.Add("全部");
String MySQLConnectionString = global::MyCommunity.Properties.Settings.Default.DBCommunityConnectionString;
string query = "SELECT * FROM 业主信息 WHERE (楼栋名称 ='" + this.楼栋名称ComboBox.Text + "')";
DataTable myTable = DataHelper.GetDataTable(query);
foreach (DataRow dRow in myTable.Rows)
{
this.业主姓名ComboBox.Items.Add(dRow["业主姓名"].ToString());
}
}
private void 楼栋名称ComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
this.业主姓名ComboBox.Items.Clear();
this.业主姓名ComboBox.Items.Add("全部");
string query = "SELECT * FROM 业主信息 WHERE (楼栋名称 ='" + this.楼栋名称ComboBox.Text.Trim() + "')";
DataTable myTable = DataHelper.GetDataTable(query);
foreach (DataRow dRow in myTable.Rows)
{
this.业主姓名ComboBox.Items.Add(dRow["业主姓名"].ToString());
}
this.业主姓名ComboBox.SelectedIndex = 0;
}
private void 楼栋名称ComboBox_SelectedIndexChanged(object sender, EventArgs e)
{//获取指定楼栋的业主姓名
string buildName = this.楼栋名称ComboBox.Text;
if (!string.IsNullOrEmpty(buildName))
{
string query = string.Format("SELECT * FROM 业主信息 WHERE (业主编号 NOT IN (SELECT 业主编号 FROM 迁出信息)) AND (楼栋名称='{0}')", buildName);
System.Data.DataTable dt = DataHelper.GetDataTable(query);
this.业主姓名ComboBox.DataSource = dt;
this.业主姓名ComboBox.DisplayMember = "业主姓名";
this.业主姓名ComboBox.ValueMember = "业主编号";
}
}
private void 新增ToolStripButton_Click(object sender, EventArgs e)
{//新增物管费用信息
string v_计费年份 = this.计费年份ToolStripComboBox.Text.Trim();
string v_计费月份 = this.计费月份ToolStripComboBox.Text.Trim();
string v_楼栋名称 = this.楼栋名称ToolStripComboBox.Text.Trim();
if (string.IsNullOrEmpty(v_计费年份) || string.IsNullOrEmpty(v_计费月份) || string.IsNullOrEmpty(v_楼栋名称))
{
new MsgBoxForm("提示", "没有选择正确的计费年份，计费月份，楼栋名称等信息！").ShowDialog();
return;
}
if (new MsgBoxForm("提示", string.Format("现在将新增 {0}{1}年{2}月物业管理费数据，是否继续？", v_楼栋名称, v_计费年份, v_计费月份), true).ShowDialog() != DialogResult.OK)
{
return;
}
string query = "Select 自动编号 From 物管费用 WHERE 计费年份=" + v_计费年份 + " AND 计费月份=" + v_计费月份 + " AND 楼栋名称='" + v_楼栋名称 + "'";
DataTable myTable = DataHelper.GetDataTable(query);
if (myTable.Rows.Count == 0)
{
//插入新月份的物管费用基本信息
string insertQuery = "INSERT INTO 物管费用 (楼栋名称,业主编号,业主姓名,建筑面积,套内面积) SELECT 楼栋名称,业主编号,业主姓名,建筑面积,套内面积 FROM 业主信息 WHERE (业主编号 NOT IN (SELECT 业主编号 FROM 迁出信息)) AND (楼栋名称='" + v_楼栋名称 + "')";
DataHelper.UpdateOrDeleteRecord(insertQuery);
string updateQuery = "UPDATE 物管费用 SET 登记标志='正在登记',计费年份=" + v_计费年份 + ",计费月份=" + v_计费月份 + " WHERE 登记标志 IS NULL AND (楼栋名称='" + v_楼栋名称 + "')";
DataHelper.UpdateOrDeleteRecord(updateQuery);
int myLastMonth = Convert.ToInt16(v_计费月份) - 1;
int myLastYear = Convert.ToInt16(v_计费年份);
if (myLastMonth == 0)
{
myLastMonth = 12;
myLastYear = Convert.ToInt16(v_计费年份) - 1;
}
//将上月计费单价作为新月物管费用计费单价
query = "Select 业主编号,计费单价 FROM 物管费用 WHERE 计费年份=" + myLastYear + " AND 计费月份=" + myLastMonth + " AND 楼栋名称='" + v_楼栋名称 + "' AND 登记标志='完成登记'";
DataTable myComputeTable = DataHelper.GetDataTable(query);
foreach (DataRow dRow in myComputeTable.Rows)
{
string v_业主编号 = Helper.Obj2String(dRow[0]);
double v_计费单价 = Helper.Obj2Double(dRow[1]);
query = "UPDATE 物管费用 SET 计费单价=" + v_计费单价 + " WHERE 计费年份=" + v_计费年份 + " AND 计费月份=" + v_计费月份 + " AND 楼栋名称='" + v_楼栋名称 + "' AND 业主编号='" + v_业主编号 + "'";
DataHelper.UpdateOrDeleteRecord(query);
}
DataHelper.CommitUpdate();
SynManageRegisterInfo(v_计费年份, v_计费月份, v_楼栋名称, "正在登记");
}
else
{
new MsgBoxForm("提示", "当月物业管理费已登记！").ShowDialog();
return;
}
}
private void InitCmbCommunity()
{
string query = "select * from 小区信息";
DataTable dt = DataHelper.GetDataTable(query);
foreach (DataRow dRow in dt.Rows)
{
this.小区名称ComboBox.Items.Add(dRow["小区名称"].ToString());
}
if (dt.Rows.Count > 0)
{
this.小区名称ComboBox.SelectedIndex = 0;
}
}
private void 查询Button_Click(object sender, EventArgs e)
{//查询业主未交费信息
string v_业主编号 = "";
if (this.业主姓名ComboBox.SelectedValue != null && this.业主姓名ComboBox.SelectedValue.ToString() != "System.Data.DataRowView")
{
v_业主编号 = this.业主姓名ComboBox.SelectedValue.ToString();
}
string query = "SELECT 自动编号, 计费年份, 计费月份, 费用类型, 表编号, 表底数, 表止数, 表用量, 计费单价, 应交金额 FROM 水电气费 WHERE (业主编号 ='" + v_业主编号 + "') AND (费用状态='未交费') ORDER BY 计费年份,计费月份,费用类型";
DataTable myTable = DataHelper.GetDataTable(query);
this.未交DataGridView.DataSource = myTable;
this.交款人员TextBox.Text = this.业主姓名ComboBox.Text;
}
private void 小区名称ComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
this.楼栋名称ComboBox.Items.Clear();
this.楼栋名称ComboBox.Items.Add("全部");
string v_小区名称 = this.小区名称ComboBox.Text;
string query = "SELECT * FROM 楼栋信息 WHERE (小区名称 = '" + v_小区名称 + "')";
DataTable myTable = DataHelper.GetDataTable(query);
foreach (DataRow dRow in myTable.Rows)
{
this.楼栋名称ComboBox.Items.Add(dRow["楼栋名称"].ToString());
}
this.楼栋名称ComboBox.SelectedIndex = 0;
}
private void 查询Button_Click(object sender, EventArgs e)
{//查询业主未交费信息
string v_业主编号 = "";
string v_业主姓名 = this.业主姓名ComboBox.Text.Trim();
if (this.业主姓名ComboBox.SelectedValue != null && this.业主姓名ComboBox.SelectedValue.ToString() != "System.Data.DataRowView")
{
v_业主编号 = this.业主姓名ComboBox.SelectedValue.ToString();
}
string query = "SELECT 自动编号,计费年份,计费月份,建筑面积,套内面积,计费单价,应交金额,补充说明 FROM 物管费用 WHERE (业主编号 ='" + v_业主编号 + "') AND (业主姓名 ='" + v_业主姓名 + "') AND (费用状态='未交费') ORDER BY 计费年份,计费月份";
DataTable myTable = DataHelper.GetDataTable(query);
this.未交DataGridView.DataSource = myTable;
this.交款人员TextBox.Text = v_业主姓名;
}
private void SynEvent()
{
string eventNo = this.事件编号ToolStripTextBox.Text.Trim();
string query = string.Empty;
if (!string.IsNullOrEmpty(eventNo))
{
query = string.Format("select * from 突发事件 where 事件编号 like '%{0}%' order by 事件编号 desc", eventNo);
}
else
{
query = "select * from 突发事件 order by 事件编号 desc";
}
dtData = DataHelper.GetDataTable(query);
this.突发事件DataGridView.DataSource = dtData;
}
private void SynOwnerRepair()
{
string repairNo = this.维修编号ToolStripTextBox.Text.Trim();
string query = string.Empty;
if (!string.IsNullOrEmpty(repairNo))
{
query = string.Format("select * from 业主维修 where 维修编号 like '%{0}%' order by 维修编号 desc", repairNo);
}
else
{
query = "select * from 业主维修 order by 维修编号 desc";
}
dtData = DataHelper.GetDataTable(query);
this.业主维修DataGridView.DataSource = dtData;
}
private void SynComplain()
{
string complainNo = this.投诉编号ToolStripTextBox.Text.Trim();
string query = string.Empty;
if (!string.IsNullOrEmpty(complainNo))
{
query = string.Format("select * from 服务投诉 where 投诉编号 like '%{0}%' order by 投诉编号 desc", complainNo);
}
else
{
query = "select * from 服务投诉 order by 投诉编号 desc";
}
dtData = DataHelper.GetDataTable(query);
this.务投诉DataGridView.DataSource = dtData;
}
private void SynOwner()
{
string ownerNo = this.业主编号ToolStripTextBox.Text.Trim();
string buildName = this.楼栋名称ComboBox.Text.Trim();
string query = string.Empty;
if (!string.IsNullOrEmpty(ownerNo) && !string.IsNullOrEmpty(buildName))
{
query = string.Format("select 业主信息.*,楼栋信息.小区名称 from 业主信息 left join 楼栋信息 on 业主信息.楼栋名称=楼栋信息.楼栋名称 where 业主信息.业主编号 like '%{0}%' and 业主信息.楼栋名称='{1}'", ownerNo, buildName);
}
else
{
if (!string.IsNullOrEmpty(ownerNo))
{
query = string.Format("select 业主信息.*,楼栋信息.小区名称 from 业主信息 left join 楼栋信息 on 业主信息.楼栋名称=楼栋信息.楼栋名称 where 业主信息.业主编号 like '%{0}%'", ownerNo);
}
else if (!string.IsNullOrEmpty(buildName))
{
query = string.Format("select 业主信息.*,楼栋信息.小区名称 from 业主信息 left join 楼栋信息 on 业主信息.楼栋名称=楼栋信息.楼栋名称 where 业主信息.楼栋名称='{0}'", buildName);
}
else
{
query = "select 业主信息.*,楼栋信息.小区名称 from 业主信息 left join 楼栋信息 on 业主信息.楼栋名称=楼栋信息.楼栋名称";
}
}
dtData = DataHelper.GetDataTable(query);
this.业主信息DataGridView.Rows.Clear();
foreach (DataRow dRow in dtData.Rows)
{
object[] objs = new object[] {
Helper.Obj2String(dRow["业主编号"]),
Helper.Obj2String(dRow["业主姓名"]),
Helper.Obj2String(dRow["楼栋名称"]),
Helper.Obj2String(dRow["单元名称"]),
Helper.Obj2String(dRow["房号名称"]),
Helper.Obj2String(dRow["物业费用"]),
Helper.Obj2String(dRow["预存金额"]),
Helper.Obj2String(dRow["联系电话"]),
Helper.Obj2Date(dRow["入住日期"]).ToString("yyyy年M月d日"),
Helper.Obj2String(dRow["当前状态"]),
Helper.Obj2String(dRow["补充说明"]),
Helper.Obj2String(dRow["小区名称"])
};
this.业主信息DataGridView.Rows.Add(objs);
}
//this.业主信息DataGridView.DataSource = dtData;
}
private void SynStation()
{
string stationNo = this.车位编号ToolStripTextBox.Text.Trim();
string query = string.Empty;
if (!string.IsNullOrEmpty(stationNo))
{
query = string.Format("select * from 车位使用 where 车位编号 like '%{0}%'", stationNo);
}
else
{
query = "select * from 车位使用";
}
dtData = DataHelper.GetDataTable(query);
this.车位使用DataGridView.DataSource = dtData;
this.车位使用DataGridView.Columns["自动编号"].Visible = false;
}
private void ReportOwnerForm_Load(object sender, EventArgs e)
{
this.小区名称ComboBox.Items.Add("全部");
string query = "SELECT * FROM 小区信息";
DataTable myTable = DataHelper.GetDataTable(query);
foreach (DataRow dRow in myTable.Rows)
{
this.小区名称ComboBox.Items.Add(dRow["小区名称"].ToString());
}
this.小区名称ComboBox.SelectedIndex = 0;
this.当前状态ComboBox.Items.Add("全部");
this.当前状态ComboBox.Items.Add("已入住");
this.当前状态ComboBox.Items.Add("未入住");
this.当前状态ComboBox.Items.Add("其它");
this.当前状态ComboBox.SelectedIndex = 0;
}
private void ManageRegisterForm_Load(object sender, EventArgs e)
{
for (int i = 2009; i <= 2099; i++)
{
this.计费年份ToolStripComboBox.Items.Add(i.ToString());
}
for (int i = 1; i <= 12; i++)
{
this.计费月份ToolStripComboBox.Items.Add(i.ToString());
}
string query = "SELECT * FROM 楼栋信息";
DataTable myTable = DataHelper.GetDataTable(query);
foreach (DataRow dRow in myTable.Rows)
{
this.楼栋名称ToolStripComboBox.Items.Add(dRow["楼栋名称"].ToString());
}
}
private void 查询Button_Click(object sender, EventArgs e)
{//查询收入统计信息
dtData.Rows.Clear();
string queryBordy = "FROM 费用收据 WHERE (收款日期 BETWEEN '" + this.开始日期DateTimePicker.Value.ToString("yyyy-MM-dd") +
"' AND '" + this.结束日期DateTimePicker.Value.ToString("yyyy-MM-dd") + "') AND (收款事由 LIKE '%" + this.收款事由TextBox.Text + "%') ORDER BY 收款日期";
string query = "Select * " + queryBordy;
dtData = DataHelper.GetDataTable(query);
query = "Select sum(收款金额) " + queryBordy;
DataTable dt = DataHelper.GetDataTable(query);
if (dtData.Rows.Count > 0 && dt.Rows.Count > 0)
{
DataRow dRow = dtData.NewRow();
dRow["交款人员"] = "收款总计";
dRow["收款金额"] = dt.Rows[0][0].ToString();
dtData.Rows.Add(dRow);
}
this.收入统计报表DataGridView.DataSource = dtData;
}
private void 查询Button_Click(object sender, EventArgs e)
{//查询业主未交费信息
string v_业主编号 = "";
if (this.业主姓名ComboBox.SelectedValue != null && this.业主姓名ComboBox.SelectedValue.ToString() != "System.Data.DataRowView")
{
v_业主编号 = this.业主姓名ComboBox.SelectedValue.ToString();
}
string query = "SELECT 自动编号, 计费年份, 计费月份, 费用类型, 表编号, 上月数, 本月数, 表用量, 计费单价, 应交金额,补充说明 FROM 水电气费 WHERE (业主编号 ='" + v_业主编号 + "') AND (费用状态='未交费') ORDER BY 业主编号,表编号,计费年份,计费月份";
DataTable myTable = DataHelper.GetDataTable(query);
this.未交DataGridView.DataSource = myTable;
this.交款人员TextBox.Text = this.业主姓名ComboBox.Text;
AutoInputFeeInfo(v_业主编号);
if (this.未交DataGridView.Rows.Count < 1 || string.IsNullOrEmpty(v_业主编号) || !ownerInfoDic.ContainsKey(v_业主编号))
{
new MsgBoxForm("提示", "未查询到该业主的未交费信息！").ShowDialog();
return;
}
}
private void InitCmbCommunity()
{
string query = "select * from 小区信息";
DataTable dt = DataHelper.GetDataTable(query);
communityInfoDic.Clear();
foreach (System.Data.DataRow dRow in dt.Rows)
{
string v_小区名称 = Helper.Obj2String(dRow["小区名称"]);
string v_服务电话 = Helper.Obj2String(dRow["服务电话"]);
this.小区名称ComboBox.Items.Add(v_小区名称);
if (!communityInfoDic.ContainsKey(v_小区名称))
{
communityInfoDic.Add(v_小区名称, v_服务电话);
}
}
if (dt.Rows.Count > 0)
{
this.小区名称ComboBox.SelectedIndex = 0;
}
}
private void SynGasRegisterInfo(string v_计费年份, string v_计费月份, string v_费用类型, string v_楼栋名称, string v_登记标志)
{
string query = string.Format("select * from 水电气费 where 计费年份={0} and 计费月份={1} and 费用类型='{2}' and 楼栋名称='{3}' and 登记标志='{4}'", v_计费年份, v_计费月份, v_费用类型, v_楼栋名称, v_登记标志);
DataTable dt = DataHelper.GetDataTable(query);
this.水电气费DataGridView.Rows.Clear();
foreach (DataRow dRow in dt.Rows)
{
object[] objs = new object[dt.Columns.Count];
for (int i = 0; i < dt.Columns.Count; i++)
{
string columnName = dt.Columns[i].ColumnName;
objs[i] = dRow[columnName].ToString();
}
this.水电气费DataGridView.Rows.Add(objs);
}
if (dt.Rows.Count > 0)
{
this.bindingNavigatorDeleteItem.Enabled = true;
}
}
private void GasRegisterForm_Load(object sender, EventArgs e)
{
for (int i = 2009; i <= 2099; i++)
{
this.计费年份ToolStripComboBox.Items.Add(i.ToString());
}
for (int i = 1; i <= 12; i++)
{
this.计费月份ToolStripComboBox.Items.Add(i.ToString());
}
this.费用类型ToolStripComboBox.Items.Add("水费");
this.费用类型ToolStripComboBox.Items.Add("电费");
this.费用类型ToolStripComboBox.Items.Add("气费");
string query = "select * from 楼栋信息";
System.Data.DataTable dt = DataHelper.GetDataTable(query);
foreach (System.Data.DataRow dRow in dt.Rows)
{
this.楼栋名称ToolStripComboBox.Items.Add(dRow["楼栋名称"].ToString());
}
}
private void 查询Button_Click(object sender, EventArgs e)
{//查询业主信息
dtData.Rows.Clear();
string v_楼栋名称 = this.楼栋名称ComboBox.Text.Trim();
string v_单元名称 = this.单元名称TextBox.Text.Trim();
string v_房屋性质 = this.房屋性质ComboBox.Text.Trim();
string v_房屋类型 = this.房屋类型ComboBox.Text.Trim();
string v_常住人员 = this.常住人员TextBox.Text.Trim();
string v_当前状态 = this.当前状态ComboBox.Text.Trim();
string query = "Select * FROM 业主信息 WHERE (业主编号 NOT IN (Select 业主编号 FROM 迁出信息)) ";
if (v_楼栋名称 != "全部")
{
query += " AND (楼栋名称='" + v_楼栋名称 + "')";
}
if (!string.IsNullOrEmpty(v_单元名称))
{
query += " AND (单元名称 LIKE '%" + v_单元名称 + "%')";
}
if (v_房屋性质 != "全部")
{
query += " AND (房屋性质='" + v_房屋性质 + "')";
}
if (!string.IsNullOrEmpty(v_房屋类型))
{
query += " AND (房屋类型 LIKE '%" + v_房屋类型 + "%')";
}
if (!string.IsNullOrEmpty(v_常住人员))
{
query += " AND (常住人员 LIKE '%" + v_常住人员 + "%')";
}
if (v_当前状态 != "全部")
{
query += " AND (当前状态='" + v_当前状态 + "')";
}
query += " ORDER BY 楼栋名称";
dtData = DataHelper.GetDataTable(query);
this.业主报表DataGridView.DataSource = dtData;
}
private void GetMeterInfo(string v_业主编号)
{
MeterInfoList = new List <string>();
string query = string.Format("select * from 电表信息 where 业主编号='{0}'", v_业主编号);
DataTable dt = DataHelper.GetDataTable(query);
string meterInfoStr = string.Empty;
foreach (DataRow dRow in dt.Rows)
{
string v_电表编号 = Helper.Obj2String(dRow["电表编号"]);
if (!string.IsNullOrEmpty(v_电表编号) && !MeterInfoList.Contains(v_电表编号))
{
MeterInfoList.Add(v_电表编号);
meterInfoStr += v_电表编号 + ",";
}
}
if (MeterInfoList.Count > 0)
{
meterInfoStr = meterInfoStr.Substring(0, meterInfoStr.Length - 1);
this.电表序列TextBox.Text = meterInfoStr;
}
}
private void 小区名称ComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
string v_小区名称 = this.小区名称ComboBox.Text.Trim();
this.楼栋名称ComboBox.Items.Clear();
this.MyCommunity = v_小区名称;
string query = string.Format("select * from 楼栋信息 where 小区名称='{0}'", v_小区名称);
DataTable dt = DataHelper.GetDataTable(query);
foreach (DataRow dRow in dt.Rows)
{
this.楼栋名称ComboBox.Items.Add(dRow["楼栋名称"].ToString());
}
if (dt.Rows.Count > 0)
{
this.楼栋名称ComboBox.SelectedIndex = 0;
}
else
{
this.业主姓名ComboBox.DataSource = null;
}
}
private void ReportOwnerForm_Load(object sender, EventArgs e)
{
this.房屋性质ComboBox.Items.Add("全部");
this.房屋性质ComboBox.Items.Add("住宅");
this.房屋性质ComboBox.Items.Add("商用");
this.房屋性质ComboBox.Items.Add("其它");
this.房屋类型ComboBox.Items.Add("全部");
this.房屋类型ComboBox.Items.Add("高层");
this.房屋类型ComboBox.Items.Add("别墅");
this.房屋类型ComboBox.Items.Add("其它");
this.楼栋名称ComboBox.Items.Add("全部");
string query = "SELECT * FROM 楼栋信息";
DataTable myTable = DataHelper.GetDataTable(query);
foreach (DataRow dRow in myTable.Rows)
{
this.楼栋名称ComboBox.Items.Add(dRow["楼栋名称"].ToString());
}
this.当前状态ComboBox.Items.Add("全部");
this.当前状态ComboBox.Items.Add("已入住");
this.当前状态ComboBox.Items.Add("未入住");
this.当前状态ComboBox.Items.Add("其它");
}
private void 保存Button_Click(object sender, EventArgs e)
{//保存水电气费交款信息
string v_业主编号 = "";
if (this.业主姓名ComboBox.SelectedValue != null && this.业主姓名ComboBox.SelectedValue.ToString() != "System.Data.DataRowView")
{
v_业主编号 = this.业主姓名ComboBox.SelectedValue.ToString();
}
string v_楼栋名称 = this.楼栋名称ComboBox.Text;
string v_收据编号 = Helper.GetTextBoxCtrlValue(this.Controls, "收据编号");
if (string.IsNullOrEmpty(v_收据编号))
{
new MsgBoxForm("提示", "没有输入正确的信息！").ShowDialog();
return;
}
if (new MsgBoxForm("警告", "现在将保存业主交纳的物业费数据，是否继续？", true).ShowDialog() != DialogResult.OK)
{
return;
}
try
{
string v_收款日期 = Helper.GetDateCtrlValue(this.Controls, "收款日期");
string v_交款人员 = Helper.GetTextBoxCtrlValue(this.Controls, "交款人员");
string v_应收金额 = Helper.GetTextBoxCtrlValue(this.Controls, "应收金额");
string v_实收金额 = Helper.GetTextBoxCtrlValue(this.Controls, "实收金额");
string v_收款形式 = Helper.GetComBoxCtrlValue(this.Controls, "收款形式");
string v_收款人员 = Helper.GetTextBoxCtrlValue(this.Controls, "收款人员");
string v_收款事由 = Helper.GetTextBoxCtrlValue(this.Controls, "收款事由");
string v_补充说明 = Helper.GetTextBoxCtrlValue(this.Controls, "补充说明");
if (string.IsNullOrEmpty(v_实收金额))
{
new MsgBoxForm("提示", "请输入实收金额！").ShowDialog();
return;
}
float f_应收金额 = 0, f_实收金额 = 0, f_预存金额 = 0;
float.TryParse(v_应收金额, out f_应收金额);
float.TryParse(v_实收金额, out f_实收金额);
f_预存金额 = (float)Math.Round(f_实收金额 - f_应收金额, 2);
if (f_实收金额 < f_应收金额)
{
new MsgBoxForm("提示", "实收金额不能小于应收金额！").ShowDialog();
return;
}
string query = string.Format("INSERT INTO 费用收据(收据编号,收款日期,交款人员,应收金额,实收金额,收款形式,收款人员,收款事由,补充说明) VALUES('{0}','{1}','{2}',{3},{4},'{5}','{6}','{7}','{8}')",
v_收据编号, v_收款日期, v_交款人员, v_应收金额, v_实收金额, v_收款形式, v_收款人员, v_收款事由, v_补充说明);
DataHelper.UpdateOrDeleteRecord(query);
foreach (DataRow dRow in dtData.Rows)
{
string v_自动编号 = dRow[0].ToString();
string My收据编号 = this.收据编号TextBox.Text;
query = "UPDATE 水电气费 SET 费用状态='已交费',收据编号='" + My收据编号 + "' WHERE 自动编号=" + v_自动编号;
DataHelper.UpdateOrDeleteRecord(query);
}
query = "UPDATE 业主信息 SET 预存金额=" + f_预存金额 + " WHERE 业主编号='" + v_业主编号 + "' AND 楼栋名称='" + v_楼栋名称 + "'";
int nRet = DataHelper.UpdateOrDeleteRecord(query);
if (nRet > 0) //更新预存金额
{
if (ownerInfoDic.ContainsKey(v_业主编号))
{
ownerInfoDic[v_业主编号].d_预存金额 = f_预存金额;
}
}
DataHelper.CommitUpdate();
new MsgBoxForm("提示", "保存成功！").ShowDialog();
}
catch (Exception ex)
{
new MsgBoxForm("提示", "保存失败，请检查所填数据类型！").ShowDialog();
LogHelper.LogError(ex);
}
string querySelect = "SELECT 自动编号, 计费年份, 计费月份, 费用类型, 表编号, 上月数, 本月数, 表用量, 计费单价, 应交金额,补充说明 FROM 水电气费 WHERE (业主编号 ='" + v_业主编号 + "') AND (费用状态='未交费') ORDER BY 业主编号,表编号,计费年份,计费月份";
DataTable myTable = DataHelper.GetDataTable(querySelect);
this.未交DataGridView.DataSource = myTable;
this.交款人员TextBox.Text = this.业主姓名ComboBox.Text;
AutoInputFeeInfo(v_业主编号);
}
private bool IsNewAdded(string repairNo)
{
string query = string.Format("select * from 业主维修 where 维修编号='{0}'", repairNo);
System.Data.DataTable dt = DataHelper.GetDataTable(query);
return dt.Rows.Count < 1;
}
//根据用户的操作权限配置系统菜单
public void SetUserOperateMenu(string userId)
{
string query = "Select * From 操作用户 Where 用户名称='" + userId + "'";
DataTable MyTable = DataHelper.GetDataTable(query);
if ((int)MyTable.Rows[0]["基本管理之业主登记管理"] == 0)
{
this.业主登记管理ToolStripMenuItem.Visible = false;
this.业主登记管理ToolStripSeparator.Visible = false;
this.业主登记管理ToolStripButton.Visible = false;
}
if ((int)MyTable.Rows[0]["基本管理之业主迁出管理"] == 0)
{
this.业主迁出管理ToolStripMenuItem.Visible = false;
this.业主迁出管理ToolStripSeparator.Visible = false;
this.业主迁出管理ToolStripButton.Visible = false;
}
if ((int)MyTable.Rows[0]["基本管理之楼栋信息管理"] == 0)
{
this.楼栋信息管理ToolStripMenuItem.Visible = false;
this.楼栋信息管理ToolStripSeparator.Visible = false;
this.楼栋信息管理ToolStripButton.Visible = false;
}
if ((int)MyTable.Rows[0]["基本管理之公共财产管理"] == 0)
{
this.公共财产管理ToolStripMenuItem.Visible = false;
this.公共财产管理ToolStripButton.Visible = false;
}
if ((int)MyTable.Rows[0]["物业管理之服务投诉管理"] == 0)
{
this.务投诉管理ToolStripMenuItem.Visible = false;
this.务投诉管理ToolStripSeparator.Visible = false;
this.务投诉管理ToolStripButton.Visible = false;
}
if ((int)MyTable.Rows[0]["物业管理之业主维修管理"] == 0)
{
this.业主维修管理ToolStripMenuItem.Visible = false;
this.业主维修管理ToolStripSeparator.Visible = false;
this.业主维修管理ToolStripButton.Visible = false;
}
if ((int)MyTable.Rows[0]["物业管理之公共维修管理"] == 0)
{
this.公共维修管理ToolStripMenuItem.Visible = false;
this.公共维修管理ToolStripSeparator.Visible = false;
this.公共维修管理ToolStripButton.Visible = false;
}
if ((int)MyTable.Rows[0]["物业管理之车位使用管理"] == 0)
{
this.车位使用管理ToolStripMenuItem.Visible = false;
this.车位使用管理ToolStripSeparator.Visible = false;
this.车位使用管理ToolStripButton.Visible = false;
}
if ((int)MyTable.Rows[0]["物业管理之突发事件管理"] == 0)
{
this.突发事件管理ToolStripMenuItem.Visible = false;
this.突发事件管理ToolStripButton.Visible = false;
}
if ((int)MyTable.Rows[0]["费用管理之水电气费登记"] == 0)
{
this.水电气费登记ToolStripMenuItem.Visible = false;
this.水电气费登记ToolStripSeparator.Visible = false;
this.水电气费登记ToolStripButton.Visible = false;
}
if ((int)MyTable.Rows[0]["费用管理之水电气费查询"] == 0)
{
this.水电气费查询ToolStripMenuItem.Visible = false;
this.水电气费查询ToolStripSeparator.Visible = false;
this.水电气费查询ToolStripButton.Visible = false;
}
if ((int)MyTable.Rows[0]["费用管理之水电气费交纳"] == 0)
{
this.水电气费交纳ToolStripMenuItem.Visible = false;
this.水电气费交纳ToolStripSeparator.Visible = false;
this.水电气费交纳ToolStripButton.Visible = false;
}
if ((int)MyTable.Rows[0]["费用管理之物管费用登记"] == 0)
{
this.物管费用登记ToolStripMenuItem.Visible = false;
this.物管费用登记ToolStripSeparator.Visible = false;
this.物管费用登记ToolStripButton.Visible = false;
}
if ((int)MyTable.Rows[0]["费用管理之物管费用查询"] == 0)
{
this.物管费用查询ToolStripMenuItem.Visible = false;
this.物管费用查询ToolStripSeparator.Visible = false;
this.物管费用查询ToolStripButton.Visible = false;
}
if ((int)MyTable.Rows[0]["费用管理之物管费用交纳"] == 0)
{
this.物管费用交纳ToolStripMenuItem.Visible = false;
this.物管费用交纳ToolStripSeparator.Visible = false;
this.物管费用交纳ToolStripButton.Visible = false;
}
if ((int)MyTable.Rows[0]["费用管理之其他费用交纳"] == 0)
{
this.其他费用交纳ToolStripMenuItem.Visible = false;
this.其他费用交纳ToolStripButton.Visible = false;
}
if ((int)MyTable.Rows[0]["报表管理之业主统计报表"] == 0)
{
this.业主统计报表ToolStripMenuItem.Visible = false;
this.业主统计报表ToolStripSeparator.Visible = false;
this.业主统计报表ToolStripButton.Visible = false;
}
if ((int)MyTable.Rows[0]["报表管理之投诉统计报表"] == 0)
{
this.投诉统计报表ToolStripMenuItem.Visible = false;
this.投诉统计报表ToolStripSeparator.Visible = false;
this.投诉统计报表ToolStripButton.Visible = false;
}
if ((int)MyTable.Rows[0]["报表管理之业主维修报表"] == 0)
{
this.业主维修报表ToolStripMenuItem.Visible = false;
this.业主维修报表ToolStripSeparator.Visible = false;
this.业主维修报表ToolStripButton.Visible = false;
}
if ((int)MyTable.Rows[0]["报表管理之公共维修报表"] == 0)
{
this.公共维修报表ToolStripMenuItem.Visible = false;
this.公共维修报表ToolStripSeparator.Visible = false;
this.公共维修报表ToolStripButton.Visible = false;
}
if ((int)MyTable.Rows[0]["报表管理之收入统计报表"] == 0)
{
this.收入统计报表ToolStripMenuItem.Visible = false;
this.收入统计报表ToolStripButton.Visible = false;
}
if ((int)MyTable.Rows[0]["系统管理之分配用户权限"] == 0)
{
this.分配用户权限ToolStripMenuItem.Visible = false;
this.分配用户权限ToolStripSeparator.Visible = false;
this.分配用户权限ToolStripSeparator.Visible = false;
}
if ((int)MyTable.Rows[0]["系统管理之小区信息设置"] == 0)
{
this.小区信息设置ToolStripMenuItem.Visible = false;
this.小区信息设置ToolStripSeparator.Visible = false;
this.小区信息设置ToolStripSeparator.Visible = false;
}
}
private void 新增ToolStripButton_Click(object sender, EventArgs e)
{//新增水电气费
string v_费用类型 = "物业费用";
string v_小区名称 = this.小区名称ToolStripComboBox.Text.Trim();
string v_楼栋名称 = this.楼栋名称ToolStripComboBox.Text.Trim();
string v_计费年份 = this.计费年份ToolStripComboBox.Text.Trim();
string v_计费月份 = this.计费月份ToolStripComboBox.Text.Trim();
if (string.IsNullOrEmpty(v_计费年份) || string.IsNullOrEmpty(v_计费月份) || string.IsNullOrEmpty(v_小区名称) || string.IsNullOrEmpty(v_楼栋名称))
{
new MsgBoxForm("提示", "没有选择正确的计费年份，计费月份，小区名称，楼栋名称等信息！").ShowDialog();
return;
}
double d_计费单价 = PriceHelper.GetPrice("电费");
if (d_计费单价 <= 0)
{
new MsgBoxForm("提示", string.Format("{0}计费单价还未设定，请先在小区信息设置界面进行设置！", "电费")).ShowDialog();
return;
}
if (new MsgBoxForm("提示", string.Format("现在将新增{0}-{1}{2}年{3}月的{4}数据，是否继续？", v_小区名称, v_楼栋名称, v_计费年份, v_计费月份, v_费用类型), true).ShowDialog() != DialogResult.OK)
{
return;
}
string query = string.Format("select 自动编号 from 水电气费 where 计费年份={0} and 计费月份={1} and 费用类型='{2}' and 楼栋名称='{3}'", v_计费年份, v_计费月份, v_费用类型, v_楼栋名称);
DataTable dt = DataHelper.GetDataTable(query);
if (dt.Rows.Count == 0)
{//插入新月份费用的业主基本信息
string insertQuery = "INSERT INTO 水电气费 (楼栋名称,业主编号,业主姓名,表编号) SELECT 业主信息.楼栋名称,业主信息.业主编号,业主信息.业主姓名,电表信息.电表编号 from 业主信息 right join 电表信息 on 业主信息.业主编号=电表信息.业主编号 WHERE (业主信息.业主编号 NOT IN (SELECT 业主编号 FROM 迁出信息)) AND (业主信息.楼栋名称='" + v_楼栋名称 + "')";
DataHelper.UpdateOrDeleteRecord(insertQuery);
string updateQuery = "UPDATE 水电气费 SET 登记标志='正在登记',费用类型='" + v_费用类型 + "',计费年份=" + v_计费年份 + ",计费月份=" + v_计费月份 + ",计费单价=" + d_计费单价 + " WHERE 登记标志 IS NULL AND (楼栋名称='" + v_楼栋名称 + "')";
DataHelper.UpdateOrDeleteRecord(updateQuery);
int MyLastMonth = Convert.ToInt16(v_计费月份) - 1;
int MyLastYear = Convert.ToInt16(v_计费年份);
if (MyLastMonth == 0)
{
MyLastMonth = 12;
MyLastYear = Convert.ToInt16(v_计费年份) - 1;
}
//将上月止数作为新月份底数
string lastMonthQuery = "Select 楼栋名称,业主编号,业主姓名,计费年份,计费月份,费用类型,表编号,上月数,本月数 FROM 水电气费 WHERE 计费年份=" + MyLastYear + " AND 计费月份=" + MyLastMonth + " AND 费用类型='" + v_费用类型 + "' AND 楼栋名称='" + v_楼栋名称 + "' AND 登记标志='完成登记'";
DataTable myLastMonthTable = DataHelper.GetDataTable(lastMonthQuery);
if (myLastMonthTable.Rows.Count > 0)
{
foreach (DataRow dRow in myLastMonthTable.Rows)
{
string My楼栋名称 = Helper.Obj2String(dRow[0]);
string My业主编号 = Helper.Obj2String(dRow[1]);
string My计费年份 = Helper.Obj2String(dRow[3]);
string My计费月份 = Helper.Obj2String(dRow[4]);
string My费用类型 = Helper.Obj2String(dRow[5]);
string My表编号 = Helper.Obj2String(dRow[6]);
double My上月数 = Helper.Obj2Double(dRow[7]);
double My本月数 = Helper.Obj2Double(dRow[8]);
updateQuery = "UPDATE 水电气费 SET 上月数=" + My本月数 + " WHERE 计费年份=" + v_计费年份 + " AND 计费月份=" + v_计费月份 + " AND 费用类型='" + v_费用类型 + "' AND 楼栋名称='" + v_楼栋名称 + "' AND 业主编号='" + My业主编号 + "' AND 表编号='" + My表编号 + "'";
DataHelper.UpdateOrDeleteRecord(updateQuery);
}
}
DataHelper.CommitUpdate();
SynGasRegisterInfo(v_计费年份, v_计费月份, v_楼栋名称, "正在登记");
}
else
{
new MsgBoxForm("提示", "当月" + v_费用类型 + "已登记！").ShowDialog();
return;
}
}
private void 查询Button_Click(object sender, EventArgs e)
{
string v_计费年份 = this.计费年份ComboBox.Text.Trim();
string v_计费月份 = this.计费月份ComboBox.Text.Trim();
string v_楼栋名称 = this.楼栋名称ComboBox.Text.Trim();
string v_业主姓名 = this.业主姓名ComboBox.Text.Trim();
string v_小区名称 = this.小区名称ComboBox.Text.Trim();
string v_费用状态 = this.费用状态ComboBox.Text.Trim();
if (string.IsNullOrEmpty(v_计费年份) || string.IsNullOrEmpty(v_计费月份) || string.IsNullOrEmpty(v_小区名称) || string.IsNullOrEmpty(v_楼栋名称))
{
new MsgBoxForm("提示", "没有选择正确的计费年份，计费月份，小区名称，楼栋名称等信息！").ShowDialog();
return;
}
dtData.Rows.Clear();
string query = "Select 楼栋名称,业主编号,业主姓名,计费年份,计费月份,费用状态,收据编号,round(sum(应交金额),2) as 应交金额 FROM 水电气费 WHERE (登记标志='完成登记') AND (计费年份= " + v_计费年份 + ")";
string ownerQuery = "Select 业主编号,业主姓名,楼栋名称,物业费用,预存金额,联系电话 from 业主信息 WHERE (业主编号 NOT IN (SELECT 业主编号 FROM 迁出信息))";
string reciptNoQuery = "Select 收据编号 FROM 水电气费 WHERE (登记标志='完成登记') AND (费用状态='已交费') AND (计费年份= " + v_计费年份 + ")";
if (v_计费月份 == "全年")
{
query += "";
}
else
{
query += " AND (计费月份=" + v_计费月份 + ")";
reciptNoQuery += " AND (计费月份=" + v_计费月份 + ")";
}
if (v_小区名称 == "全部")
{
query += "";
}
else
{
if (v_楼栋名称 == "全部")
{
query += " AND (楼栋名称 in (select 楼栋名称 from 楼栋信息 where 小区名称='" + v_小区名称 + "'))";
ownerQuery += " AND (楼栋名称 in (select 楼栋名称 from 楼栋信息 where 小区名称='" + v_小区名称 + "'))";
reciptNoQuery += " AND (楼栋名称 in (select 楼栋名称 from 楼栋信息 where 小区名称='" + v_小区名称 + "'))";
}
else
{
query += " AND (楼栋名称='" + v_楼栋名称 + "')";
ownerQuery += " AND (楼栋名称='" + v_楼栋名称 + "')";
reciptNoQuery += " AND (楼栋名称='" + v_楼栋名称 + "')";
}
}
if (v_业主姓名 == "全部")
{
query += "";
}
else
{
query += " AND (业主姓名='" + v_业主姓名 + "')";
ownerQuery += " AND (业主姓名='" + v_业主姓名 + "')";
reciptNoQuery += " AND (业主姓名='" + v_业主姓名 + "')";
}
if (v_费用状态 == "全部")
{
query += "";
}
else
{
query += " AND (费用状态='" + v_费用状态 + "')";
}
query += " group by 业主编号,计费年份,计费月份";
dtData = DataHelper.GetDataTable(query);
if (dtData.Rows.Count == 0)
{
return;
}
DataTable ownerTable = DataHelper.GetDataTable(ownerQuery);
string reciptQuery = string.Format("select * from 费用收据 where 收据编号 in ({0})", reciptNoQuery);
DataTable reciptTable = DataHelper.GetDataTable(reciptQuery);
RefreshDataGridView(ownerTable, reciptTable);
}