private void error_name()
{
name_py_text.Text = MyPinYin.GetFirst(name_text.Text.Trim());
bool jytt = true;
string errorstring = "";
if (name_text.Text.Trim() == "")
{
errorstring = "书名不能为空！"; jytt = false;;
}
if (name_text.Text.Trim().Length > 30)
{
errorstring = "书名的长度不能超过30个字符！"; jytt = false;
}
if (!jytt)
{
label2_name_text.Text = errorstring;
label2_name_text.Visible = true;
}
else
{
label2_name_text.Visible = false;
name_text.Tag = true;
}
}
private void qg_button1_Click(object sender, EventArgs e)
{
try
{
string lxid = qg_radio_group1.Tag.ToString();
string lxname = qg_radio_group1.AccessibleDescription.ToString();
//if (string.IsNullOrEmpty(fpxx1.Text))
if (fpxx1.Text.IsNullOrEmpty())
{
MessageBox.Show("请输入废品现象!");
return;
}
if (fpyy1.Text.IsNullOrEmpty())
{
MessageBox.Show("请输入废品原因!");
return;
}
////if (!a_sqlconn.Simple_all.isNumberic(sl1.Text.ToString()))
//{
// MessageBox.Show("请输入正确的数量!");
// return;
//}
int sl = sl1.Text.ToInt();
if (sl <= 0)
{
MessageBox.Show("数量不正确！");
return;
}
string fpxx = fpxx1.Text.ToString().Trim();
string fpyy = fpyy1.Text.ToString().Trim();
string fpxx_py = MyPinYin.GetFirst(fpxx);
string fpyy_py = MyPinYin.GetFirst(fpyy);
#region
string sqlstring = "select * from calibrate where 产品ID='" + pprid + "' and 属性=0 and LTRIM(RTRIM(考核名称))=LTRIM(RTRIM('" + fpxx + "'))";
DataTable dt = return_select(sqlstring);
string fpxxid = "0";
if (dt.Rows.Count > 0)
{
fpxxid = dt.Rows[0]["ID"].ToString();
}
else
{
sqlstring = "insert into calibrate(产品ID,考核名称,拼音,属性) values ('" + pprid + "',LTRIM(RTRIM('" + fpxx + "')),LTRIM(RTRIM('" + fpxx_py + "')),2)";
insert_update_delete(sqlstring);
sqlstring = "select * from calibrate where 产品ID='" + pprid + "' and 属性=0 and LTRIM(RTRIM(考核名称))=LTRIM(RTRIM('" + fpxx + "'))";
dt = return_select(sqlstring);
fpxxid = dt.Rows[0]["ID"].ToString();
}
#endregion
#region
sqlstring = "select * from calibrate where 属性=2 and LTRIM(RTRIM(考核名称))=LTRIM(RTRIM('" + fpyy + "'))";
dt = return_select(sqlstring);
string fpyyid = "0";
if (dt.Rows.Count > 0)
{
fpyyid = dt.Rows[0]["ID"].ToString();
}
else
{
sqlstring = "insert into calibrate(考核名称,拼音,属性) values (LTRIM(RTRIM('" + fpyy + "')),LTRIM(RTRIM('" + fpyy_py + "')),2)";
insert_update_delete(sqlstring);
sqlstring = "select * from calibrate where 属性=2 and LTRIM(RTRIM(考核名称))=LTRIM(RTRIM('" + fpyy + "'))";
dt = return_select(sqlstring);
fpyyid = dt.Rows[0]["ID"].ToString();
}
#endregion
//INSERT into dayworkcal(ID, 考核ID, 废品现象, 数量, 原因详ID, 具体原因, 类型名称, 类型) values(maxid, calid1, daycalx1, daycalx2, calid3, daycalx4, templx, daycalx5)
daywork day1 = (daywork)Owner;
DataRow dr = day1.dt_dayworkcal.NewRow();
dr["考核ID"] = fpxxid;
dr["废品现象"] = fpxx;
dr["数量"] = sl;
dr["原因详ID"] = fpyyid;
dr["具体原因"] = fpyy;
dr["类型名称"] = lxname;
dr["类型"] = lxid;
day1.dt_dayworkcal.Rows.Add(dr);
fpxx1.Text = "";
calaaa.Text = "";
fpyy1.Text = "";
calbbb.Text = "";
sl1.Text = "0";
}
catch (Exception ex)
{
ex.errormess();
}
}
private void qg_button1_Click(object sender, EventArgs e)
{
if (!ValidateOK())
{
ISBN_text_Validating(ISBN_text, null);
name_text_Validating(name_text, null);
dj_text_Validating(dj_text, null);
data1_text_Validating(data1_text, null);
pressaaa_text_Validating(pressaaa_text, null);
typeaaa_text_Validating(typeaaa_text, null);
sl_text_Validating(sl_text, null);
return;
}
//if (ISBN_text.Text.Trim() == "")
//{
// MessageBox.Show("请输入ISBN号！");
// return;
//}
//if (name_text.Text.Trim() == "")
//{
// MessageBox.Show("请输入书名！");
// return;
//}
//if (dj_text.Text.Trim() == "" || !dj_text.Text.IsDecimal() || dj_text.Text.ToDecimal() < 0)
//{
// MessageBox.Show("单价不正常!");
// return;
//}
//if (pressaaa_text.Text.Trim() == "")
//{
// MessageBox.Show("请输入出版社！");
// return;
//}
//if (typeaaa_text.Text.Trim() == "")
//{
// MessageBox.Show("请输入图书类别！");
// return;
//}
//if (sl_text.Text.Trim() == "" || !sl_text.Text.IsInt() || sl_text.Text.ToInt() < 0)
//{
// MessageBox.Show("册数不正常!");
// return;
//}
string sqlstring = "";
DataTable dt;
sqlstring = "select ISBN号,书名 from booksall where ID!=" + bookid + " and ISBN号='" + ISBN_text.Text.Trim() + "'";
dt = return_select(sqlstring);
if (dt.Rows.Count > 0)
{
string temp1 = "";
temp1 = temp1 + "\rISBN号：" + dt.Rows[0]["ISBN号"].ToString();
temp1 = temp1 + "\r书名：" + dt.Rows[0]["书名"].ToString();
MessageBox.Show("数据库中已经存在以下ISBN号的图书" + temp1 + "\r无法修改！");
return;
}
string pressidtempid = "0";
string press1temp1 = pressaaa_text.Text.Trim();
string presspytemp1 = MyPinYin.GetFirst(press1temp1);//
sqlstring = "select ID from press where 出版社='" + press1temp1.Trim() + "'";
dt = return_select(sqlstring);
if (dt.Rows.Count <= 0)
{
sqlstring = "insert into press(出版社,拼音) values ('" + press1temp1.Trim() + "','" + presspytemp1 + "')";
insert_update_delete(sqlstring);
sqlstring = "select ID from press where 出版社='" + press1temp1.Trim() + "'";
dt = return_select(sqlstring);
}
pressidtempid = dt.Rows[0]["ID"].ToString();
string typeidtempid = "0";
string type1temp1 = typeaaa_text.Text.Trim();
string typepytemp1 = MyPinYin.GetFirst(type1temp1);//
sqlstring = "select ID from types where 图书类别='" + type1temp1.Trim() + "'";
dt = return_select(sqlstring);
if (dt.Rows.Count <= 0)
{
sqlstring = "insert into types(图书类别,拼音) values ('" + type1temp1.Trim() + "','" + typepytemp1 + "')";
insert_update_delete(sqlstring);
sqlstring = "select ID from types where 图书类别='" + type1temp1.Trim() + "'";
dt = return_select(sqlstring);
}
typeidtempid = dt.Rows[0]["ID"].ToString();
sqlstring = "select ID from booksall where 出版社ID=" + pressidtempid + " and 书名='" + name_text.Text.Trim() + "'";
sqlstring = sqlstring + " and ID!=" + bookid;
dt = return_select(sqlstring);
if (dt.Rows.Count > 0)
{
string temp1 = "";
temp1 = temp1 + "\rISBN号：" + dt.Rows[0]["ISBN号"].ToString();
temp1 = temp1 + "\r书名：" + name_text.Text.Trim();
temp1 = temp1 + "\r出版社：" + pressaaa_text.Text.Trim();
DialogResult dr = MessageBox.Show("数据库中已经存在以下图书目录？\r" + temp1 + "\r是否确定修改？", "提示", MessageBoxButtons.OKCancel);
if (dr == DialogResult.Cancel)
{
return;
}
}
string namebytemp1 = MyPinYin.GetFirst(name_text.Text.Trim());
sqlstring = "update booksall set ISBN号='" + ISBN_text.Text.Trim() + "',书名='" + name_text.Text.Trim() + "',拼音='" + namebytemp1 + "'"
+ ",单价=" + dj_text.Text + ",出版社ID=" + pressidtempid + ",类别ID=" + typeidtempid + ",册数=" + sl_text.Text
+ ",出版日期=(convert(varchar(10),'" + data1_text.Value.ToShortDateString().ToString() + "',120))"
+ " where ID=" + bookid;
insert_update_delete(sqlstring);
booksall frm1 = (booksall)this.Owner;
//frm1.auto();
DataTable owner_dt = frm1.grid_datasource();
DataRow[] rows = owner_dt.Select("ID=" + bookid);
rows[0]["ISBN号"] = ISBN_text.Text.Trim();
rows[0]["书名"] = name_text.Text.Trim();
rows[0]["单价"] = dj_text.Text.Trim();
rows[0]["出版社ID"] = pressidtempid;
rows[0]["类别ID"] = typeidtempid;
rows[0]["册数"] = sl_text.Text.Trim();
rows[0]["出版日期"] = data1_text.Value.ToShortDateString().ToString();
rows[0]["出版社"] = pressaaa_text.Text.Trim();
rows[0]["图书类别"] = typeaaa_text.Text.Trim();
Close();
}
private void qg_button2_Click(object sender, EventArgs e)
{
try
{
if (qg_grid1.DataSource == null)
{
MessageBox.Show("尚未从excel表中导入图书目录信息！");
return;
}
DataTable dt_all1 = (DataTable)qg_grid1.DataSource;
DataTable dt_all_copy = dt_all1.Copy();
if (dt_all_copy.Rows.Count <= 0)
{
MessageBox.Show("本次图书目录信息记录数为0，无法导入！");
return;
}
DialogResult err = MessageBox.Show("确认将本次图书目录信息提交至后台数据库吗？", "提示", MessageBoxButtons.OKCancel);
if (err == DialogResult.Cancel)
{
return;
}
WaitFormService.Show();
string sqlstring;
DataTable dt_temp;
//保存出版社信息
#region
//如果“出版社”为空，则取上一行的“出版社”
string bmname = dt_all_copy.Rows[0]["出版社"].ToString();
foreach (DataRow dr in dt_all_copy.Rows)
{
WaitFormService.SetText("正在整理出版社！\n\r " + bmname.Trim());
if (dr["出版社"].ToString().Trim() == "")
{
dr["出版社"] = bmname;
}
bmname = dr["出版社"].ToString().Trim();
}
DataColumn dc1 = new DataColumn("出版社ID", Type.GetType("System.Int64"));
dc1.DefaultValue = 0;
dt_all_copy.Columns.Add(dc1);
DataView dataView = dt_all_copy.DefaultView;
DataTable dt_Distinct = dataView.ToTable(true, "出版社");//注：其中ToTable（）的第一个参数为是否distinct
for (int i = 0; i < dt_Distinct.Rows.Count; i++)
{
string bmnametemp1 = dt_Distinct.Rows[i]["出版社"].ToString().Trim();
string namepytemp1 = MyPinYin.GetFirst(bmnametemp1);//
sqlstring = "select ID from press where 出版社=ltrim(rtrim('" + bmnametemp1.Trim() + "'))";
dt_temp = return_select(sqlstring);
if (dt_temp.Rows.Count <= 0)
{
sqlstring = "insert into press(出版社,拼音) values (ltrim(rtrim('" + bmnametemp1.Trim() + "')),ltrim(rtrim('" + namepytemp1.Trim() + "')))";
insert_update_delete(sqlstring);
sqlstring = "select ID from press where 出版社=ltrim(rtrim('" + bmnametemp1.Trim() + "'))";
dt_temp = return_select(sqlstring);
}
string bmid = dt_temp.Rows[0]["ID"].ToString();
DataRow[] row_update = dt_all_copy.Select("出版社='" + bmnametemp1.Trim() + "'");
for (int ii = 0; ii < row_update.Count(); ii++)
{
row_update[ii]["出版社ID"] = bmid.ToInt();
}
}
#endregion
//保存出版社信息
//保存图书类别至types
#region
dc1 = new DataColumn("类别ID", Type.GetType("System.Int64"));
dc1.DefaultValue = 0;
dt_all_copy.Columns.Add(dc1);
foreach (DataRow dr in dt_all_copy.Rows)
{
string pernametemp1 = dr["图书类别"].ToString().Trim();
string namepytemp1 = MyPinYin.GetFirst(pernametemp1);// ChineseToPinYin.ToPinYin_one(pernametemp1);
WaitFormService.SetText("正在整理图书类别信息！\n\r" + pernametemp1.Trim());
sqlstring = "select ID from types where 图书类别=ltrim(rtrim('" + pernametemp1.Trim() + "'))";
dt_temp = return_select(sqlstring);
if (dt_temp.Rows.Count <= 0)
{
sqlstring = "insert into types(图书类别,拼音) values (ltrim(rtrim('" + pernametemp1.Trim() + "')),ltrim(rtrim('" + namepytemp1.Trim() + "')))";
insert_update_delete(sqlstring);
sqlstring = "select ID from types where 图书类别=ltrim(rtrim('" + pernametemp1.Trim() + "'))";
dt_temp = return_select(sqlstring);
}
string perid = dt_temp.Rows[0]["ID"].ToString();
DataRow[] row_update = dt_all_copy.Select("图书类别='" + pernametemp1.Trim() + "'");
for (int ii = 0; ii < row_update.Count(); ii++)
{
row_update[ii]["类别ID"] = perid.ToInt();
}
}
#endregion
//保存图书类别至types
////保存图书信息
//#region
//for (int i = 0; i < dt_all_copy.Columns.Count; i++)
//{
// string column_name = dt_all_copy.Columns[i].ColumnName.ToString();
// WaitFormService.SetText("正在处理图书目录条目！\n\r" + column_name.Trim());
// //MessageBox.Show(column_name.Trim() + " 类型：" + dt_all_copy.Columns[i].DataType.ToString());
// sqlstring = "select ID from Subject_name where 条目名称=ltrim(rtrim('" + column_name.Trim() + "')) and 删除=0";
// dt_temp = return_select(sqlstring);
// if (dt_temp.Rows.Count <= 0)
// {
// sqlstring = "insert into subject_name(条目名称) values (ltrim(rtrim('" + column_name.Trim() + "')))";
// insert_update_delete(sqlstring);
// sqlstring = "select ID from Subject_name where 条目名称=ltrim(rtrim('" + column_name.Trim() + "')) and 删除=0";
// dt_temp = return_select(sqlstring);
// }
// string jectid = dt_temp.Rows[0]["ID"].ToString();
// //判断column的列datatype,只分为string和decimal
// object test = dt_all_copy.Compute("max(" + column_name.Trim() + ")", "");
// int column_type = 1;//为0表示是数字，为1表示是string
// if (test.ToString().IsInt() || test.ToString().IsDecimal())
// {
// column_type = 0;
// }
// //判断column的列datatype,只分为string和decimal
// sqlstring = "select ID from subject_mon where 月份='" + sql_month.Trim() + "' and 条目ID=" + jectid.Trim();
// dt_temp = return_select(sqlstring);
// if (dt_temp.Rows.Count <= 0)
// {
// sqlstring = "insert into subject_mon(月份,条目ID,排序,类型) values ('" + sql_month.Trim() + "'," + jectid.Trim() + "," + i.ToString() + "," + column_type + ")";
// insert_update_delete(sqlstring);
// }
//}
//#endregion
////保存工资条目信息
//将工资信息拷贝至后台数据库
#region
for (int i = 0; i < dt_all_copy.Rows.Count; i++)
{
string pernametemp1 = dt_all_copy.Rows[i]["书名"].ToString();
string bmidtempid = dt_all_copy.Rows[i]["出版社ID"].ToString();
string peridtempid = dt_all_copy.Rows[i]["类别ID"].ToString();
WaitFormService.SetText("正在拷贝第" + i.ToString() + "条图书记录/共" + dt_all_copy.Rows.Count.ToString() + "条记录！\n\r" + " 员工姓名：" + pernametemp1.ToString());
string lr1 = ""; //ISBN号
string lr2 = ""; //书名
string lr2_px = ""; //书名拼音
decimal lr3 = 0; //单价
string lr4 = ""; //出版社ID
DateTime lr5 = DateTime.Now; //出版日期
string lr6 = ""; //类别ID
int lr7 = 0; //册数
for (int j = 0; j < dt_all_copy.Columns.Count; j++)
{
string columnnametemp1 = dt_all_copy.Columns[j].ToString();
//if (columnnametemp1.Trim() == "出版社"
// || columnnametemp1.Trim() == "图书类别"
// || columnnametemp1.Trim() == "出版社ID"
// || columnnametemp1.Trim() == "类别ID"
// ) { continue; }
string dt_all_copy_one_z = dt_all_copy.Rows[i][j].ToString().Trim();
if (columnnametemp1.Trim() == "ISBN号")
{
lr1 = dt_all_copy_one_z;
}
if (columnnametemp1.Trim() == "书名")
{
lr2 = dt_all_copy_one_z;
lr2_px = MyPinYin.GetFirst(dt_all_copy_one_z);;
}
if (columnnametemp1.Trim() == "单价" && dt_all_copy_one_z != "" && dt_all_copy_one_z.IsDecimal())
{
lr3 = dt_all_copy_one_z.ToDecimal();
}
if (columnnametemp1.Trim() == "出版社ID")
{
lr4 = dt_all_copy_one_z;
}
if (columnnametemp1.Trim() == "出版日期" && dt_all_copy_one_z != "")
{
lr5 = Convert.ToDateTime(dt_all_copy_one_z);
}
if (columnnametemp1.Trim() == "类别ID")
{
lr6 = dt_all_copy_one_z;
}
if (columnnametemp1.Trim() == "册数" && dt_all_copy_one_z != "" && dt_all_copy_one_z.IsInt())
{
lr7 = dt_all_copy_one_z.ToInt();
}
//if (dt_all_copy_one_z.Trim() == "" || (dt_all_copy_one_z.IsDecimal() && dt_all_copy_one_z.ToDecimal() == 0)) { continue; }
//sqlstring = "select ID from press where 出版社=ltrim(rtrim('" + columnnametemp1.Trim() + "')) and 删除=0";
//dt_temp = return_select(sqlstring);
//if (dt_temp.Rows.Count <= 0)
//{
// MessageBox.Show("没有在后台数据库中查询到：" + columnnametemp1.Trim() + "的条目记录，请重新导入工资信息，或与系统管理员联系！");
// return;
//}
//string jectidtemp1 = dt_temp.Rows[0]["ID"].ToString();
//sqlstring = "select * from Subject_mon where 月份='" + sql_month.Trim() + "' and 条目ID=" + jectidtemp1.Trim();
//dt_temp = return_select(sqlstring);
//if (dt_temp.Rows.Count <= 0)
//{
// MessageBox.Show("没有在后台数据库中查询到：" + sql_month.Trim() + "月份的" + columnnametemp1.Trim() + "的条目记录，请重新导入工资信息，或与系统管理员联系！");
// return;
//}
//int ject_typetemp1 = dt_temp.Rows[0]["类型"].ToString().ToInt();
////如果单元格里是string,就用permoney_lr中的ID代替单元格里的内容
//if (ject_typetemp1 == 1)
//{
// sqlstring = "select ID from permoney_lr where 文字内容=ltrim(rtrim('" + dt_all_copy_one_z.Trim() + "'))";
// dt_temp = return_select(sqlstring);
// if (dt_temp.Rows.Count <= 0)
// {
// sqlstring = "insert into permoney_lr(文字内容) values (ltrim(rtrim('" + dt_all_copy_one_z.Trim() + "')))";
// insert_update_delete(sqlstring);
// sqlstring = "select ID from permoney_lr where 文字内容=ltrim(rtrim('" + dt_all_copy_one_z.Trim() + "'))";
// dt_temp = return_select(sqlstring);
// }
// dt_all_copy_one_z = dt_temp.Rows[0]["ID"].ToString();
//}
////MessageBox.Show(pernametemp1+" "+columnnametemp1 + " " + ject_typetemp1.ToString()+" "+ dt_all_copy_one_z);
//sqlstring = "insert into permoney(部门ID,员工ID,条目ID,金额,月份,排序) values ('" + bmidtempid + "','" + peridtempid + "','" + jectidtemp1 + "'," + dt_all_copy_one_z.ToDecimal().ToString() + ",'" + sql_month.Trim() + "'," + i.ToString() + ")";
//insert_update_delete(sqlstring);
}
sqlstring = "insert into booksall(ISBN号,书名,拼音,单价,出版社ID,出版日期,类别ID,册数"
+ ") values ("
+ "'" + lr1 + "','" + lr2 + "','" + lr2_px + "'," + lr3 + "," + lr4 + ",(convert(varchar(10),'" + lr5.ToShortDateString().ToString() + "',120))," + lr6 + "," + lr7 + ")";
insert_update_delete(sqlstring);
}
#endregion
//将工资信息拷贝至后台数据库
WaitFormService.Close();
MessageBox.Show("图书目录已经导入至后台数据库！");
}
catch (Exception ex)
{
ex.errormess();
}
}
private void qg_button2_Click(object sender, EventArgs e)
{
try
{
if (qg_grid1.DataSource == null)
{
MessageBox.Show("尚未从excel表中导入工资信息！");
return;
}
DataTable dt_all1 = (DataTable)qg_grid1.DataSource;
DataTable dt_all_copy = dt_all1.Copy();
if (dt_all_copy.Rows.Count <= 0)
{
MessageBox.Show("本次工资信息记录数为0，无法导入！");
return;
}
DialogResult err = MessageBox.Show("确认将本次工资信息提交至后台数据库吗？", "提示", MessageBoxButtons.OKCancel);
if (err == DialogResult.Cancel)
{
return;
}
wage_month form_mon = new wage_month();
form_mon.Owner = this;//重要的一步，主要是使Form2的Owner指针指向Form1
form_mon.ShowDialog();
//如果“选择月份”窗体 点击 的不是确定键，则退出
if (form_mon.DialogResult != DialogResult.OK)
{
return;
}
string sqlstring;
DataTable dt_temp;
WaitFormService.Show();
//如果需要删除当月已经在后台数据库中的工资信息
if (delete_old_jytt)
{
sqlstring = "delete from Subject_mon where 月份='" + sql_month.Trim() + "'";
sqlstring = sqlstring + "\n\r" + "delete from permoney where 月份='" + sql_month + "'";
insert_update_delete(sqlstring);
}
//保存月份
sqlstring = "select ID from allmax where months=ltrim(rtrim('" + sql_month.Trim() + "'))";
dt_temp = return_select(sqlstring);
if (dt_temp.Rows.Count <= 0)
{
sqlstring = "insert into allmax(months) values (ltrim(rtrim('" + sql_month.Trim() + "')))";
insert_update_delete(sqlstring);
}
//保存月份
//保存部门信息
#region
//如果“部门名称”为空，则取上一行的“部门名称”
string bmname = dt_all_copy.Rows[0]["部门名称"].ToString();
foreach (DataRow dr in dt_all_copy.Rows)
{
WaitFormService.SetText("正在整理部门名称！\n\r " + bmname.Trim());
if (dr["部门名称"].ToString().Trim() == "")
{
dr["部门名称"] = bmname;
}
bmname = dr["部门名称"].ToString().Trim();
}
DataColumn dc1 = new DataColumn("部门ID", Type.GetType("System.Int64"));
dc1.DefaultValue = 0;
dt_all_copy.Columns.Add(dc1);
DataView dataView = dt_all_copy.DefaultView;
DataTable dt_Distinct = dataView.ToTable(true, "部门名称");//注：其中ToTable（）的第一个参数为是否distinct
for (int i = 0; i < dt_Distinct.Rows.Count; i++)
{
string bmnametemp1 = dt_Distinct.Rows[i]["部门名称"].ToString().Trim();
sqlstring = "select ID from z_fcname where 部门名称=ltrim(rtrim('" + bmnametemp1.Trim() + "')) and 删除=0";
dt_temp = return_select(sqlstring);
if (dt_temp.Rows.Count <= 0)
{
sqlstring = "insert into z_fcname(部门名称) values (ltrim(rtrim('" + bmnametemp1.Trim() + "')))";
insert_update_delete(sqlstring);
sqlstring = "select ID from z_fcname where 部门名称=ltrim(rtrim('" + bmnametemp1.Trim() + "')) and 删除=0";
dt_temp = return_select(sqlstring);
}
string bmid = dt_temp.Rows[0]["ID"].ToString();
DataRow[] row_update = dt_all_copy.Select("部门名称='" + bmnametemp1.Trim() + "'");
for (int ii = 0; ii < row_update.Count(); ii++)
{
row_update[ii]["部门ID"] = bmid.ToInt();
}
}
#endregion
//保存部门信息
//保存员工姓名至person
#region
dc1 = new DataColumn("员工ID", Type.GetType("System.Int64"));
dc1.DefaultValue = 0;
dt_all_copy.Columns.Add(dc1);
foreach (DataRow dr in dt_all_copy.Rows)
{
string pernametemp1 = dr["姓名"].ToString().Trim();
string namepytemp1 = MyPinYin.GetFirst(pernametemp1);// ChineseToPinYin.ToPinYin_one(pernametemp1);
WaitFormService.SetText("正在整理员工姓名信息！\n\r" + pernametemp1.Trim());
sqlstring = "select ID from person where 姓名=ltrim(rtrim('" + pernametemp1.Trim() + "'))";
dt_temp = return_select(sqlstring);
if (dt_temp.Rows.Count <= 0)
{
sqlstring = "insert into person(姓名,拼音) values (ltrim(rtrim('" + pernametemp1.Trim() + "')),ltrim(rtrim('" + namepytemp1.Trim() + "')))";
insert_update_delete(sqlstring);
sqlstring = "select ID from person where 姓名=ltrim(rtrim('" + pernametemp1.Trim() + "'))";
dt_temp = return_select(sqlstring);
}
string perid = dt_temp.Rows[0]["ID"].ToString();
DataRow[] row_update = dt_all_copy.Select("姓名='" + pernametemp1.Trim() + "'");
for (int ii = 0; ii < row_update.Count(); ii++)
{
row_update[ii]["员工ID"] = perid.ToInt();
}
}
#endregion
//保存员工姓名至person
//保存工资条目信息
#region
for (int i = 0; i < dt_all_copy.Columns.Count; i++)
{
string column_name = dt_all_copy.Columns[i].ColumnName.ToString();
WaitFormService.SetText("正在处理工资条目！\n\r" + column_name.Trim());
//MessageBox.Show(column_name.Trim() + " 类型：" + dt_all_copy.Columns[i].DataType.ToString());
sqlstring = "select ID from Subject_name where 条目名称=ltrim(rtrim('" + column_name.Trim() + "')) and 删除=0";
dt_temp = return_select(sqlstring);
if (dt_temp.Rows.Count <= 0)
{
sqlstring = "insert into subject_name(条目名称) values (ltrim(rtrim('" + column_name.Trim() + "')))";
insert_update_delete(sqlstring);
sqlstring = "select ID from Subject_name where 条目名称=ltrim(rtrim('" + column_name.Trim() + "')) and 删除=0";
dt_temp = return_select(sqlstring);
}
string jectid = dt_temp.Rows[0]["ID"].ToString();
//判断column的列datatype,只分为string和decimal
object test = dt_all_copy.Compute("max(" + column_name.Trim() + ")", "");
int column_type = 1;//为0表示是数字，为1表示是string
if (test.ToString().IsInt() || test.ToString().IsDecimal())
{
column_type = 0;
}
//判断column的列datatype,只分为string和decimal
sqlstring = "select ID from subject_mon where 月份='" + sql_month.Trim() + "' and 条目ID=" + jectid.Trim();
dt_temp = return_select(sqlstring);
if (dt_temp.Rows.Count <= 0)
{
sqlstring = "insert into subject_mon(月份,条目ID,排序,类型) values ('" + sql_month.Trim() + "'," + jectid.Trim() + "," + i.ToString() + "," + column_type + ")";
insert_update_delete(sqlstring);
}
}
#endregion
//保存工资条目信息
//将工资信息拷贝至后台数据库
#region
for (int i = 0; i < dt_all_copy.Rows.Count; i++)
{
string pernametemp1 = dt_all_copy.Rows[i]["姓名"].ToString();
string bmidtempid = dt_all_copy.Rows[i]["部门ID"].ToString();
string peridtempid = dt_all_copy.Rows[i]["员工ID"].ToString();
WaitFormService.SetText("正在拷贝第" + i.ToString() + "条工资记录/共" + dt_all_copy.Rows.Count.ToString() + "条记录！\n\r" + " 员工姓名：" + pernametemp1.ToString());
for (int j = 0; j < dt_all_copy.Columns.Count; j++)
{
string columnnametemp1 = dt_all_copy.Columns[j].ToString();
if (columnnametemp1.Trim() == "姓名" ||
columnnametemp1.Trim() == "部门名称" ||
columnnametemp1.Trim() == "部门ID" ||
columnnametemp1.Trim() == "员工ID"
)
{
continue;
}
string dt_all_copy_one_z = dt_all_copy.Rows[i][j].ToString().Trim();
if (dt_all_copy_one_z.Trim() == "" || (dt_all_copy_one_z.IsDecimal() && dt_all_copy_one_z.ToDecimal() == 0))
{
continue;
}
sqlstring = "select ID from Subject_name where 条目名称=ltrim(rtrim('" + columnnametemp1.Trim() + "')) and 删除=0";
dt_temp = return_select(sqlstring);
if (dt_temp.Rows.Count <= 0)
{
MessageBox.Show("没有在后台数据库中查询到：" + columnnametemp1.Trim() + "的条目记录，请重新导入工资信息，或与系统管理员联系！");
return;
}
string jectidtemp1 = dt_temp.Rows[0]["ID"].ToString();
sqlstring = "select * from Subject_mon where 月份='" + sql_month.Trim() + "' and 条目ID=" + jectidtemp1.Trim();
dt_temp = return_select(sqlstring);
if (dt_temp.Rows.Count <= 0)
{
MessageBox.Show("没有在后台数据库中查询到：" + sql_month.Trim() + "月份的" + columnnametemp1.Trim() + "的条目记录，请重新导入工资信息，或与系统管理员联系！");
return;
}
int ject_typetemp1 = dt_temp.Rows[0]["类型"].ToString().ToInt();
//如果单元格里是string,就用permoney_lr中的ID代替单元格里的内容
if (ject_typetemp1 == 1)
{
sqlstring = "select ID from permoney_lr where 文字内容=ltrim(rtrim('" + dt_all_copy_one_z.Trim() + "'))";
dt_temp = return_select(sqlstring);
if (dt_temp.Rows.Count <= 0)
{
sqlstring = "insert into permoney_lr(文字内容) values (ltrim(rtrim('" + dt_all_copy_one_z.Trim() + "')))";
insert_update_delete(sqlstring);
sqlstring = "select ID from permoney_lr where 文字内容=ltrim(rtrim('" + dt_all_copy_one_z.Trim() + "'))";
dt_temp = return_select(sqlstring);
}
dt_all_copy_one_z = dt_temp.Rows[0]["ID"].ToString();
}
//MessageBox.Show(pernametemp1+" "+columnnametemp1 + " " + ject_typetemp1.ToString()+" "+ dt_all_copy_one_z);
sqlstring = "insert into permoney(部门ID,员工ID,条目ID,金额,月份,排序) values ('" + bmidtempid + "','" + peridtempid + "','" + jectidtemp1 + "'," + dt_all_copy_one_z.ToDecimal().ToString() + ",'" + sql_month.Trim() + "'," + i.ToString() + ")";
insert_update_delete(sqlstring);
}
}
#endregion
//将工资信息拷贝至后台数据库
WaitFormService.Close();
MessageBox.Show(sql_month.Trim() + "月份的员工工资已经导入至后台数据库！");
}
catch (Exception ex)
{
ex.errormess();
}
}
private void qg_button1_Click(object sender, EventArgs e)
{
monthCalendar1.Visible = false;
if (!ValidateOK())
{
ISBN_text_Validating(ISBN_text, null);
name_text_Validating(name_text, null);
dj_text_Validating(dj_text, null);
data1_text_Validating(data1_text, null);
pressaaa_text_Validating(pressaaa_text, null);
typeaaa_text_Validating(typeaaa_text, null);
sl_text_Validating(sl_text, null);
return;
}
//if (ISBN_text.Text.Trim() == "")
//{
// MessageBox.Show("请输入ISBN号！");
// return;
//}
//if (name_text.Text.Trim() == "")
//{
// MessageBox.Show("请输入书名！");
// return;
//}
//if (dj_text.Text.Trim()=="" || !dj_text.Text.IsDecimal()|| dj_text.Text.ToDecimal()<0)
//{
// MessageBox.Show("单价不正常!");
// return;
//}
//if (pressaaa_text.Text.Trim() == "")
//{
// MessageBox.Show("请输入出版社！");
// return;
//}
//if (typeaaa_text.Text.Trim() == "")
//{
// MessageBox.Show("请输入图书类别！");
// return;
//}
//if (sl_text.Text.Trim()=="" || !sl_text.Text.IsInt() || sl_text.Text.ToInt()<0)
//{
// MessageBox.Show("册数不正常!");
// return;
//}
string isbnstring = ISBN_text.Text.Trim();
string namestring = name_text.Text.Trim();
decimal djtemp1 = dj_text.Value;
string pressstring = pressaaa_text.Text.Trim();
string typestring = typeaaa_text.Text.Trim();
decimal sltemp1 = sl_text.Value;
string sqlstring = "";
DataTable dt;
sqlstring = "select *,出版社=isnull((select 出版社 from press where ID=b.出版社ID),'') from booksall b where ISBN号='" + isbnstring + "'";
dt = return_select(sqlstring);
if (dt.Rows.Count > 0)
{
string temp1 = "\rISBN号：" + isbnstring;
temp1 = temp1 + "\r 书名：" + dt.Rows[0]["书名"].ToString();
temp1 = temp1 + "\r 出版社：" + dt.Rows[0]["出版社"].ToString();
MessageBox.Show("图书目录表中已经存在相同的ISBN号" + temp1 + "\r 请修改ISBN号！");
return;
}
string pressidtempid = "0";
string press1temp1 = pressaaa_text.Text.Trim();
string presspytemp1 = MyPinYin.GetFirst(press1temp1);//
sqlstring = "select ID from press where 出版社='" + press1temp1.Trim() + "'";
dt = return_select(sqlstring);
if (dt.Rows.Count <= 0)
{
sqlstring = "insert into press(出版社,拼音) values ('" + press1temp1.Trim() + "','" + presspytemp1 + "')";
insert_update_delete(sqlstring);
sqlstring = "select ID from press where 出版社='" + press1temp1.Trim() + "'";
dt = return_select(sqlstring);
}
pressidtempid = dt.Rows[0]["ID"].ToString();
string typeidtempid = "0";
string type1temp1 = typeaaa_text.Text.Trim();
string typepytemp1 = MyPinYin.GetFirst(type1temp1);//
sqlstring = "select ID from types where 图书类别='" + type1temp1.Trim() + "'";
dt = return_select(sqlstring);
if (dt.Rows.Count <= 0)
{
sqlstring = "insert into types(图书类别,拼音) values ('" + type1temp1.Trim() + "','" + typepytemp1 + "')";
insert_update_delete(sqlstring);
sqlstring = "select ID from types where 图书类别='" + type1temp1.Trim() + "'";
dt = return_select(sqlstring);
}
typeidtempid = dt.Rows[0]["ID"].ToString();
sqlstring = "select * from booksall where 出版社ID=" + pressidtempid + " and 书名='" + name_text.Text.Trim() + "'";
dt = return_select(sqlstring);
if (dt.Rows.Count > 0)
{
string temp1 = "";
temp1 = temp1 + "\rISBN号：" + dt.Rows[0]["ISBN号"].ToString();
temp1 = temp1 + "\r书名：" + name_text.Text.Trim();
temp1 = temp1 + "\r出版社：" + pressaaa_text.Text.Trim();
DialogResult dr = MessageBox.Show("数据库中已经存在以下图书目录？" + temp1 + "\r是否继续增加？", "提示", MessageBoxButtons.OKCancel);
if (dr == DialogResult.Cancel)
{
return;
}
}
string namebytemp1 = MyPinYin.GetFirst(name_text.Text.Trim());
sqlstring = "insert into booksall(ISBN号,书名,拼音,单价,出版社ID,类别ID,册数,出版日期"
+ ") values ("
+ "'" + ISBN_text.Text.Trim() + "','" + name_text.Text.Trim() + "','" + namebytemp1 + "'," + dj_text.Text + "," + pressidtempid + "," + typeidtempid + "," + sl_text.Text + ""
+ ",(convert(varchar(10),'" + data1_text.Value.ToShortDateString().ToString() + "',120))"
+ ")";
insert_update_delete(sqlstring);
sqlstring = "select max(ID) ID from booksall where ISBN号='" + isbnstring.Trim() + "'";
dt = return_select(sqlstring);
string bookidtempid = "0";
if (dt.Rows.Count > 0)
{
bookidtempid = dt.Rows[0]["ID"].ToString();
}
#region 执行父窗体的命令
booksall frm1 = (booksall)this.Owner;
DataTable owner_dt = frm1.grid_datasource();
DataRow row = owner_dt.NewRow();
row["ID"] = bookidtempid;
row["ISBN号"] = ISBN_text.Text.Trim();
row["书名"] = name_text.Text.Trim();
row["单价"] = dj_text.Text;
row["出版社ID"] = pressidtempid;
row["出版社"] = pressaaa_text.Text.Trim();
row["类别ID"] = typeidtempid;
row["图书类别"] = typeaaa_text.Text.Trim();
row["册数"] = sl_text.Text;
row["出版日期"] = data1_text.Value.ToShortDateString().ToString();
row["金额"] = Math.Round(dj_text.Value * sl_text.Value, 2);
frm1.grid_add(row);
#endregion
auto();
auto_tag();
//booksall frm1 = (booksall)this.Owner;
//frm1.auto();
////((booksall)Parent).auto();
//if ((this.MdiParent != null) && (this.MdiParent is booksall))
// (this.MdiParent as booksall).auto();
//Close();
}