private void btnSubmit_Click(object sender, System.EventArgs e)
{
if (this.cmbDateType.SelectedIndex >= 0)
{
switch (this.cmbDateType.Items[this.cmbDateType.SelectedIndex].ToString())
{
case "付款日期":
searchDateType = 0;
break;
case "起始日期":
searchDateType = 1;
break;
case "结束日期":
searchDateType = 2;
break;
}
if (searchDateType != -1)
{
DataSet infoSet = SubscribeInfoManager.RetrieveNullInfo(searchDateType);
MainForm.Form.CurrentDataSet = infoSet;
this.DialogResult = DialogResult.OK;
this.Close();
}
}
}
void ExportDataToExcel()
{
string fileName;
this.openFileDialog1.Filter = "Excel数据文件|*.xls";
this.openFileDialog1.RestoreDirectory = true;
if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
{
fileName = this.openFileDialog1.FileName;
string path = Path.GetDirectoryName(fileName);
if (!path.EndsWith(@"\"))
{
path = path + @"\";
}
string name = Path.GetFileName(fileName);
SubscribeInfoManager.OutputDataToExcel(path, name, "TempInfo");
foreach (Process process in Process.GetProcessesByName("EXCEL"))
{
process.Kill();
}
MessageBox.Show("数据已成功保存到 " + fileName);
}
}
private void btnSubmit_Click(object sender, System.EventArgs e)
{
DateTime inputDate;
if (this.txtDate.Text.Trim().Length == 0)
{
MessageBox.Show("请输入月份!");
return;
}
try
{
inputDate = Convert.ToDateTime(this.txtDate.Text.Trim() + "-1");
DataTable tbl = SubscribeInfoManager.RetrieveInfoByDateOnly(inputDate);
MainForm.Form.StatResultTable = tbl;
MainForm.Form.CurrentInputDate = inputDate;
this.DialogResult = DialogResult.OK;
this.Close();
}
catch (Exception e1)
{
if (e1.InnerException != null)
{
MessageBox.Show(e1.InnerException.Message);
}
else
{
MessageBox.Show("无效的日期格式!");
}
}
}
void ExportData(DataTable tbl)
{
this.openFileDialog1.Filter = "Excel数据文件|*.xls";
this.openFileDialog1.RestoreDirectory = true;
if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
{
//先获取一个要导出的Excel文件名
fileName = this.openFileDialog1.FileName;
string path = Path.GetDirectoryName(fileName);
if (!path.EndsWith(@"\"))
{
path = path + @"\";
}
string name1 = Path.GetFileName(fileName);
//清空保存统计信息的表中的记录
SubscribeInfoManager.ClearStatInfoTable();
//将记录插入到StatInfoTable中
foreach (DataRow row in tbl.Rows)
{
AssignValues(row);
SubscribeInfoManager.InsertIntoStatInfoTable(name, post, company, region, source, invoice, telephone, mobilephone, number, monthCount, totalMoney, averageMoney, monthMoney, startDate, endDate, giveDate);
}
//将StatInfoTable表中的记录导出到Excel文件中
SubscribeInfoManager.OutputDataToExcel(path, name1, "StatInfoTable");
foreach (Process process in Process.GetProcessesByName("EXCEL"))
{
process.Kill();
}
MessageBox.Show("数据已成功保存到 " + fileName);
}
}
private void btnSubmit_Click(object sender, System.EventArgs e)
{
try
{
AssignValueToData();
}
catch (Exception e1)
{
MessageBox.Show(e1.Message);
return;
}
if (this.number == -1 && this.monthCount == -1 && this.totalMoney == -1 && this.subscription == "汤" && this.client == "华" && this.startDate == DateTime.Parse("1000-1-1") && this.endDate == DateTime.Parse("1000-1-1") && this.giveDate == DateTime.Parse("1000-1-1"))
{
MessageBox.Show("请输入一个要批量修改的值!");
return;
}
if (MessageBox.Show("是否确认要批量修改？", "警告", MessageBoxButtons.YesNo, MessageBoxIcon.Information, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
{
BatchUpdateSelectedRows();
MainForm.Form.CurrentDataSet = SubscribeInfoManager.RetriveDataFromTempInfo();
this.DialogResult = DialogResult.OK;
this.Close();
}
}
private void button1_Click(object sender, System.EventArgs e)
{
DataRowView row = this.comboBox1.SelectedItem as DataRowView;
if (row != null)
{
if (row["name"].ToString().Trim().Length == 0)
{
MessageBox.Show("请选择一个地区进行删除!");
return;
}
}
else
{
MessageBox.Show("请选择一个地区进行删除!");
return;
}
if (MessageBox.Show("确认是否删除?", "警告", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
SubscribeInfoManager.DeleteRegion(Int32.Parse(this.comboBox1.SelectedValue.ToString().Trim()));
BindToComboBox();
MessageBox.Show("删除成功!");
}
}
bool DeleteSelectedRows()
{
if (TestSelectedRows() == false)
{
MessageBox.Show("请选中一行进行删除！");
return(false);
}
if (MessageBox.Show("删除后不能恢复,是否删除？", "警告", MessageBoxButtons.YesNo, MessageBoxIcon.Information, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
{
for (int i = 0; i < this.currentDataSet.Tables[0].Rows.Count; i++) //通过循环删除所有被选中的行
{
if (this.dataGrid1.IsSelected(i) == true)
{
string name = this.currentDataSet.Tables[0].Rows[i]["姓名"].ToString();
string company = this.currentDataSet.Tables[0].Rows[i]["公司"].ToString();
SubscribeInfoManager.DeleteSubscribeInfo(name, company);
}
}
this.currentDataSet = SubscribeInfoManager.RetriveDataFromTempInfo();
return(true);
}
else
{
return(false);
}
}
void InitData()
{
DataTable tblRegions = SubscribeInfoManager.RetrieveRegions(); //得到所有的地区
DataTable tblEmployees = EmployeeManager.RetrieveAllEmployees(); //得到所有的业务员
this.txtNumber.Text = "1";
//bind data to region comboBox
this.cmbRegion.DataSource = tblRegions;
this.cmbRegion.DisplayMember = "name";
this.cmbRegion.ValueMember = "name";
//bind data to payment comboBox
this.cmbPayment.Items.Add(" ");
this.cmbPayment.Items.Add("现金支付");
this.cmbPayment.Items.Add("银行转帐");
this.cmbPayment.Items.Add("邮局汇款");
this.cmbPayment.Items.Add("网上支付");
this.cmbPayment.Items.Add("货到付款");
this.cmbPayment.SelectedIndex = 0;
//bind data to client comboBox
this.cmbOperator.DataSource = tblEmployees;
this.cmbOperator.DisplayMember = "name";
this.cmbOperator.ValueMember = "name";
//add items to subscription comboBox
this.cmbSubscription.Items.Add(" ");
this.cmbSubscription.Items.Add("订阅");
this.cmbSubscription.Items.Add("赠阅");
this.cmbSubscription.SelectedIndex = 0;
//add items to clientCategory comboBox
this.cmbClient.Items.Add(" ");
this.cmbClient.Items.Add("A");
this.cmbClient.Items.Add("B");
this.cmbClient.Items.Add("C");
this.cmbClient.SelectedIndex = 0;
//add items to bonus comboBox
this.cmbBonus.Items.Add(" ");
this.cmbBonus.Items.Add("已提");
this.cmbBonus.Items.Add("未提");
this.cmbBonus.SelectedIndex = 0;
//add years items
this.cmbStartYear.Items.AddRange(new string[] { "", "2000", "2001", "2002", "2003", "2004", "2005", "2006", "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" });
this.cmbEndYear.Items.AddRange(new string[] { "", "2000", "2001", "2002", "2003", "2004", "2005", "2006", "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" });
this.cmbGiveYear.Items.AddRange(new string[] { "", "2000", "2001", "2002", "2003", "2004", "2005", "2006", "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" });
//add month items
this.cmbStartMonth.Items.AddRange(new string[] { "", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12" });
this.cmbEndMonth.Items.AddRange(new string[] { "", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12" });
this.cmbGiveMonth.Items.AddRange(new string[] { "", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12" });
this.cmbStartYear.SelectedIndex = 0;
this.cmbEndYear.SelectedIndex = 0;
this.cmbGiveYear.SelectedIndex = 0;
this.cmbStartMonth.SelectedIndex = 0;
this.cmbEndMonth.SelectedIndex = 0;
this.cmbGiveMonth.SelectedIndex = 0;
}
private void BindToComboBox()
{
DataTable tblRegions = SubscribeInfoManager.RetrieveRegions();
this.comboBox1.DataSource = tblRegions;
this.comboBox1.DisplayMember = "name";
this.comboBox1.ValueMember = "Id";
}
private void btnSubmit_Click(object sender, System.EventArgs e)
{
GetSearchConditions();
MainForm.Form.CurrentDataSet = SubscribeInfoManager.RetriveSubscribeInfo(name, post, company, address, region, postcode, telephone, mobilePhone, startDate, endDate, compareDirection, number, monthCount, totalMoney, inscribe, source, payment, invoice, client, operator1, bonus, localAddress, subscription);
this.DialogResult = DialogResult.OK;
this.Close();
}
void BatchUpdateSelectedRows()
{
for (int i = 0; i < MainForm.Form.CurrentDataSet.Tables[0].Rows.Count; i++)
{
if (MainForm.Form.ShowResultDataGrid.IsSelected(i) == true)
{
GetRowValue(MainForm.Form.CurrentDataSet.Tables[0].Rows[i]);
SubscribeInfoManager.UpdateSubscribeInfo(oldNameTemp, newNameTemp, postTemp, oldCompanyTemp, newCompanyTemp, addressTemp, regionTemp, postcodeTemp, telephoneTemp, mobilePhoneTemp, startDateTemp, endDateTemp, giveDateTemp, numberTemp, monthCountTemp, totalMoneyTemp, inscribeTemp, sourceTemp, paymentTemp, invoiceTemp, clientTemp, operator1Temp, bonusTemp, localAddressTemp, subscriptionTemp);
}
}
}
private void MainForm_Load(object sender, System.EventArgs e)
{
CreateMainMenu(); //创建菜单栏
CreateToolbar(); //创建工具栏
CreateSplitter(); //创建一个分割条
SetButtonEnabled(); //设置工具栏上按钮的活动状态
SubscribeInfoManager.ClearTempInfo(); //清楚临时表中的数据
this.Text = this.Text + " 当前用户: " + this.currentManager.Name;
}
private void button1_Click(object sender, System.EventArgs e)
{
if (this.textBox1.Text.Trim().Length == 0)
{
MessageBox.Show("地区名字不能为空!");
return;
}
int i = SubscribeInfoManager.CreateRegion(this.textBox1.Text.Trim());
if (i == -1)
{
MessageBox.Show("该地区已经存在!");
return;
}
this.Close();
}
private void btnSubmit_Click(object sender, System.EventArgs e)
{
try
{
if (this.cmbRegion.SelectedValue != null)
{
if (this.cmbRegion.SelectedValue.ToString().Trim() == "")
{
MessageBox.Show("地区不能为空!");
return;
}
}
else
{
MessageBox.Show("地区不能为空!");
return;
}
GetValues();
int id = SubscribeInfoManager.CreateSubscribeInfo(name, post, company, address, region, postcode, telephone,
mobilePhone, startDate, endDate, giveDate, number, monthCount, totalMoney, inscribe, source, payment, invoice, client, operator1, bonus,
localAddress, subscription);
if (id == 1) //添加成功
{
MainForm.Form.CurrentDataSet = SubscribeInfoManager.RetriveDataFromTempInfo();
this.DialogResult = DialogResult.OK;
this.Close();
}
else //添加失败
{
MessageBox.Show("已经存在该记录！");
}
}
catch (Exception e1)
{
MessageBox.Show(e1.Message);
}
}
DataTable CheckDataReduplicateWithDatabase(DataTable sourceTable) //检查在Excel表中是否有于数据库重复的记录,如果有,则用DataTable来返回
{
DataTable tbl = CreateTable(sourceTable);
foreach (DataRow row in sourceTable.Rows)
{
int count = SubscribeInfoManager.CheckSubscribeExist(row["姓名"].ToString().Trim(), row["公司"].ToString().Trim());
if (count >= 1) //说明当前记录在数据库主表中已经存在
{
if (row["姓名"].ToString().Trim() == "")
{
row["姓名"] = " ";
}
if (row["公司"].ToString().Trim() == "")
{
row["公司"] = " ";
}
AddRowToTable(tbl, row);
}
}
return(tbl);
}
bool ImportExcelData()
{
openFileDialog1.Filter = "Excel数据文件|*.xls";
openFileDialog1.RestoreDirectory = true;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
DataSet ds = null;
DataTable tempTable = null;
try
{
ds = GetData(openFileDialog1.FileName);
CheckTableFormat(ds.Tables[0]);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return(false);
}
//到这里为止,说明Excel表中字段的结构已经正确
//1.检查Excel中是否有重复的记录
tempTable = CheckDataReduplicateInExcel(ds.Tables[0]);
if (tempTable.Rows.Count > 0)
{
DataSet tempDs = new DataSet();
tempDs.Tables.Add(tempTable);
//跳出一个窗体,显示Excel表格中重复的记录
TempForm tempForm = new TempForm("以下" + tempDs.Tables[0].Rows.Count + "条记录在Excel表中有重复,请核对后再导入");
tempForm.CurrentDataSet = tempDs;
tempForm.StartPosition = FormStartPosition.CenterParent;
tempForm.ShowDialog();
return(false);
}
//2.检查Excel中是否有和数据库重复的记录
tempTable = CheckDataReduplicateWithDatabase(ds.Tables[0]);
if (tempTable.Rows.Count > 0)
{
DataSet tempDs = new DataSet();
tempDs.Tables.Add(tempTable);
//跳出一个窗体,显示与数据库中记录重复的记录
TempForm tempForm = new TempForm("以下" + tempDs.Tables[0].Rows.Count + "条记录在数据库中已存在,请核对后再导入");
tempForm.CurrentDataSet = tempDs;
tempForm.StartPosition = FormStartPosition.CenterParent;
tempForm.ShowDialog();
return(false);
}
//到这里可以安全的将数据导入到数据库
ImportDataToDatabase(ds.Tables[0]);
this.currentDataSet = SubscribeInfoManager.RetriveDataFromTempInfo();
return(true);
}
return(false);
}
void ImportDataToDatabase(DataTable sourceTable)
{
SubscribeInfoManager.ClearTempInfo();
foreach (DataRow row in sourceTable.Rows)
{
DateTime startDate = DateTime.Parse("1900-1-1");
DateTime endDate = DateTime.Parse("1900-1-1");
DateTime giveDate = DateTime.Parse("1900-1-1");
int number = -1;
int monthCount = -1;
int totalMoney = -1;
string name;
string localAddress;
string company;
string subscription;
string post;
string region;
string address;
string postcode;
string mobilePhone;
string telephone;
string inscribe;
string source;
string payment;
string invoice;
string client;
string operator1;
string bonus;
if (row["起始日期"] != DBNull.Value)
{
startDate = DateTime.Parse(row["起始日期"].ToString().Trim()); //注意:这里一定能够转换,因为如果格式不正确则它的值必定为DBNull.Value
}
if (row["结束日期"] != DBNull.Value)
{
endDate = DateTime.Parse(row["结束日期"].ToString().Trim()); //同上
}
if (row["付款日期"] != DBNull.Value)
{
giveDate = DateTime.Parse(row["付款日期"].ToString().Trim()); //同上
}
if (row["份数"] != DBNull.Value)
{
number = Int32.Parse(row["份数"].ToString()); //同上
}
if (row["期数"] != DBNull.Value)
{
monthCount = Int32.Parse(row["期数"].ToString()); //同上
}
if (row["金额"] != DBNull.Value)
{
totalMoney = Int32.Parse(row["金额"].ToString()); //同上
}
name = row["姓名"].ToString().Trim() == String.Empty ? " " : row["姓名"].ToString().Trim();
localAddress = row["县级地区"].ToString().Trim() == String.Empty ? " " : row["县级地区"].ToString().Trim();
region = row["地区"].ToString().Trim() == String.Empty ? " " : row["地区"].ToString().Trim();
company = row["公司"].ToString().Trim() == String.Empty ? " " : row["公司"].ToString().Trim();
subscription = row["订阅形式"].ToString().Trim() == String.Empty ? " " : row["订阅形式"].ToString().Trim();
post = row["职位"].ToString().Trim() == String.Empty ? " " : row["职位"].ToString().Trim();
address = row["地址"].ToString().Trim() == String.Empty ? " " : row["地址"].ToString().Trim();
postcode = row["邮编"].ToString().Trim() == String.Empty ? " " : row["邮编"].ToString().Trim();
mobilePhone = row["手机"].ToString().Trim() == String.Empty ? " " : row["手机"].ToString().Trim();
telephone = row["电话"].ToString().Trim() == String.Empty ? " " : row["电话"].ToString().Trim();
inscribe = row["落款"].ToString().Trim() == String.Empty ? " " : row["落款"].ToString().Trim();
source = row["来源"].ToString().Trim() == String.Empty ? " " : row["来源"].ToString().Trim();
payment = row["支付方式"].ToString().Trim() == String.Empty ? " " : row["支付方式"].ToString().Trim();
invoice = row["发票号"].ToString().Trim() == String.Empty ? " " : row["发票号"].ToString().Trim();
client = row["客户类别"].ToString().Trim() == String.Empty ? " " : row["客户类别"].ToString().Trim();
operator1 = row["业务员"].ToString().Trim() == String.Empty ? " " : row["业务员"].ToString().Trim();
bonus = row["奖金提取"].ToString().Trim() == String.Empty ? " " : row["奖金提取"].ToString().Trim();
SubscribeInfoManager.CreateSubscribeInfo(name, post, company, address, region, postcode, telephone, mobilePhone, startDate, endDate, giveDate, number, monthCount, totalMoney, inscribe, source, payment, invoice, client, operator1, bonus, localAddress, subscription);
}
}
