private void saveToolStripButton_Click(object sender, EventArgs e)
{
int i;
decimal fTemp, fTemp1;
decimal dKUL = 0, dKCCBJ = 0, dZGJJ = 0, dZDJJ = 0, dZZJJ = 0;
decimal dKUL1 = 0, dKCCBJ1 = 0, dZGJJ1 = 0, dZDJJ1 = 0, dZZJJ1 = 0;
decimal dKCJE = 0, dKCJE1 = 0, dYSYE = 0, dYSYE1 = 0;
//保存完毕
if (isSaved)
{
MessageBox.Show("库存盘点数据登录完毕,单据号为：" + labelDJBH.Text, "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
if (intPDID == 0)
{
MessageBox.Show("请选择库存盘点表", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
countAmount();
if (toolStripStatusLabelMXJLS.Text == "0")
{
MessageBox.Show("没有选择记录", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (MessageBox.Show("请检查库存实盘内容,是否继续保存？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.No)
{
return;
}
saveToolStripButton.Enabled = false;
string strCount = "", strDateSYS = "";
System.Data.SqlClient.SqlTransaction sqlta;
sqlConn.Open();
sqlta = sqlConn.BeginTransaction();
sqlComm.Transaction = sqlta;
try
{
//单据汇总
cGetInformation.getSystemDateTime();
strDateSYS = cGetInformation.strSYSDATATIME;
sqlComm.CommandText = "UPDATE 库存盘点汇总表 SET 盘损数量合计 = " + labelPSSLHJ.Text + ", 盘损金额合计 = " + labelPSJEHJ.Text + ", 盘点时间 = '" + strDateSYS + "', 盘点标记 = 1 WHERE (ID = " + intPDID.ToString() + ")";
sqlComm.ExecuteNonQuery();
//单据明细
for (i = 0; i < dataGridViewDJMX.Rows.Count; i++)
{
if (dataGridViewDJMX.Rows[i].IsNewRow)
{
continue;
}
//记录选择标志
if (!Convert.ToBoolean(dataGridViewDJMX.Rows[i].Cells[0].Value))
{
continue;
}
sqlComm.CommandText = "UPDATE 库存盘点明细表 SET 实盘数量 = " + dataGridViewDJMX.Rows[i].Cells[4].Value.ToString() + ", 备注 = N'" + dataGridViewDJMX.Rows[i].Cells[9].Value.ToString() + "', 盘点标志 = 1 , 盘损数量=" + dataGridViewDJMX.Rows[i].Cells[7].Value.ToString() + ", 盘损金额=" + dataGridViewDJMX.Rows[i].Cells[8].Value.ToString() + " WHERE (ID = " + dataGridViewDJMX.Rows[i].Cells[10].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
//总库存
fTemp = 0; fTemp1 = 0;
sqlComm.CommandText = "SELECT 库存数量, 库存金额, 库存成本价 FROM 商品表 WHERE (ID = " + dataGridViewDJMX.Rows[i].Cells[11].Value.ToString() + ")";
sqldr = sqlComm.ExecuteReader();
//库存成本价
dKUL1 = Convert.ToDecimal(dataGridViewDJMX.Rows[i].Cells[7].Value.ToString());
dKCJE1 = Convert.ToDecimal(dataGridViewDJMX.Rows[i].Cells[8].Value.ToString());
while (sqldr.Read())
{
dKUL = Convert.ToDecimal(sqldr.GetValue(0).ToString());
dKCJE = Convert.ToDecimal(sqldr.GetValue(1).ToString());
dKCCBJ = Convert.ToDecimal(sqldr.GetValue(2).ToString());
}
dKUL -= dKUL1;
//dKCJE -= dKCJE1;
dKCJE = dKCCBJ * dKUL;
sqldr.Close();
sqlComm.CommandText = "UPDATE 商品表 SET 库存数量 = " + dKUL.ToString() + ", 库存金额 = " + dKCJE.ToString() + " WHERE (ID = " + dataGridViewDJMX.Rows[i].Cells[11].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
//历史纪录
sqlComm.CommandText = "INSERT INTO 商品历史账表 (日期, 商品ID, 业务员ID, 单据编号, 摘要, 总结存数量, 总结存金额, 盘损数量, 盘损单价, 盘损金额, BeActive) VALUES ('" + strDateSYS + "', " + dataGridViewDJMX.Rows[i].Cells[11].Value.ToString() + ", " + comboBoxYWY.SelectedValue.ToString() + ", N'" + labelDJBH.Text + "', N'库存盘点表', " + dKUL.ToString() + ", " + dKCJE.ToString() + ", " + dKUL1.ToString() + ", " + dKCCBJ.ToString() + ", " + dKCJE1.ToString() + ", 1)";
sqlComm.ExecuteNonQuery();
//更改分库存
fTemp = 0;
//sqlComm.CommandText = "SELECT 库存数量, 库存金额, 库存成本价 FROM 库存表 WHERE (库房ID = " + intKFID.ToString() + ") AND (商品ID = " + dataGridViewDJMX.Rows[i].Cells[11].Value.ToString() + ")";
sqlComm.CommandText = "SELECT 库存数量, 库存金额, 库存成本价 FROM 库存表 WHERE (库房ID = " + dataGridViewDJMX.Rows[i].Cells[13].Value.ToString() + ") AND (商品ID = " + dataGridViewDJMX.Rows[i].Cells[11].Value.ToString() + ")";
sqldr = sqlComm.ExecuteReader();
//库存成本价
dKUL = 0; dKCJE = 0; dKCCBJ = 0;
while (sqldr.Read())
{
dKUL = Convert.ToDecimal(sqldr.GetValue(0).ToString());
dKCJE = Convert.ToDecimal(sqldr.GetValue(1).ToString());
dKCCBJ = Convert.ToDecimal(sqldr.GetValue(2).ToString());
}
sqldr.Close();
//库存金额
dKUL -= dKUL1;
//dKCJE -= dKCJE1;
dKCJE = dKCCBJ * dKUL;
sqlComm.CommandText = "UPDATE 库存表 SET 库存数量 = " + dKUL.ToString() + ", 库存金额 = " + dKCJE.ToString() + " WHERE (库房ID = " + dataGridViewDJMX.Rows[i].Cells[13].Value.ToString() + ") AND (商品ID = " + dataGridViewDJMX.Rows[i].Cells[11].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
//库房历史纪录
//sqlComm.CommandText = "INSERT INTO 商品库房历史账表 (库房ID, 日期, 商品ID, 业务员ID, 单据编号, 摘要, 库房结存数量, 库房结存金额, 盘损数量, 盘损单价, 盘损金额, BeActive) VALUES (" + intKFID.ToString() + ", '" + strDateSYS + "', " + dataGridViewDJMX.Rows[i].Cells[11].Value.ToString() + ", " + comboBoxYWY.SelectedValue.ToString() + ", N'" + labelDJBH.Text + "', N'库存盘点表', " + dKUL.ToString() + ", " + dKCJE.ToString() + ", " + dKUL1.ToString() + ", " + dKCCBJ.ToString() + ", " + dKCJE1.ToString() + ", 1)";
sqlComm.CommandText = "INSERT INTO 商品库房历史账表 (库房ID, 日期, 商品ID, 业务员ID, 单据编号, 摘要, 库房结存数量, 库房结存金额, 盘损数量, 盘损单价, 盘损金额, BeActive) VALUES (" + dataGridViewDJMX.Rows[i].Cells[13].Value.ToString() + ", '" + strDateSYS + "', " + dataGridViewDJMX.Rows[i].Cells[11].Value.ToString() + ", " + comboBoxYWY.SelectedValue.ToString() + ", N'" + labelDJBH.Text + "', N'库存盘点表', " + dKUL.ToString() + ", " + dKCJE.ToString() + ", " + dKUL1.ToString() + ", " + dKCCBJ.ToString() + ", " + dKCJE1.ToString() + ", 1)";
sqlComm.ExecuteNonQuery();
}
sqlta.Commit();
}
catch (Exception ex)
{
MessageBox.Show("数据库错误：" + ex.Message.ToString(), "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlta.Rollback();
saveToolStripButton.Enabled = true;
return;
}
finally
{
sqlConn.Close();
}
//MessageBox.Show("库存盘点数据登录完毕", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
//labelDJBH.Text = strCount;
this.Text = "库存盘点数据登录：" + labelDJBH.Text;
isSaved = true;
if (MessageBox.Show("库存盘点数据登录完毕，是否继续开始另一份单据？", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
MDIBusiness mdiT = (MDIBusiness)this.MdiParent;
mdiT.实盘数据登录CToolStripMenuItem_Click(null, null);
}
if (MessageBox.Show("是否关闭制单窗口", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
this.Close();
}
}
private void saveToolStripButton_Click(object sender, EventArgs e)
{
int i, j;
decimal dKUL = 0;
decimal dKUL1 = 0;
textBoxHTH.Focus();
//保存完毕
if (isSaved)
{
MessageBox.Show("销售出库校对单已经保存,单据号为：" + labelDJBH.Text, "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
if (iSupplyCompany == 0)
{
MessageBox.Show("请选择销售单位", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (intBKP == 0)
{
MessageBox.Show("请选择销售单", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//格式确认
if (!countAmount())
{
MessageBox.Show("销售销售出库校对单明细格式错误", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
if (toolStripStatusLabelMXJLS.Text == "0")
{
MessageBox.Show("没有销售出库校对单商品", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//if (MessageBox.Show("请检查销售销售出库校对单内容,是否继续保存？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.No)
// return;
saveToolStripButton.Enabled = false;
string strCount = "", strDateSYS = "", strKey = "BCK";
System.Data.SqlClient.SqlTransaction sqlta;
sqlConn.Open();
sqlta = sqlConn.BeginTransaction();
sqlComm.Transaction = sqlta;
try
{
//得到表单号
//得到服务器日期
sqlComm.CommandText = "SELECT GETDATE() AS 日期";
sqldr = sqlComm.ExecuteReader();
while (sqldr.Read())
{
strDateSYS = Convert.ToDateTime(sqldr.GetValue(0).ToString()).ToShortDateString();
}
sqldr.Close();
//得到日期
sqlComm.CommandText = "SELECT 时间 FROM 表单计数表 WHERE (时间 = CONVERT(DATETIME, '" + strDateSYS + " 00:00:00', 102))";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Close();
}
else //服务器时间不吻合
{
sqldr.Close();
//修正日期及计数器
sqlComm.CommandText = "UPDATE 表单计数表 SET 时间 = '" + strDateSYS + "', 计数 = 1";
sqlComm.ExecuteNonQuery();
}
//得到计数器
sqlComm.CommandText = "SELECT 计数 FROM 表单计数表 WHERE (关键词 = N'" + strKey + "')";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Read();
strCount = sqldr.GetValue(0).ToString();
sqldr.Close();
//增加计数器
sqlComm.CommandText = "UPDATE 表单计数表 SET 计数 = 计数 + 1 WHERE (关键词 = N'" + strKey + "')";
sqlComm.ExecuteNonQuery();
}
else
{
sqldr.Close();
}
if (strCount != "")
{
strCount = string.Format("{0:D3}", Int32.Parse(strCount));
strCount = strKey.ToUpper() + Convert.ToDateTime(strDateSYS).ToString("yyyyMMdd") + strCount;
}
else
{
MessageBox.Show("数据错误", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
sqlConn.Close();
return;
}
//表单汇总
string sBMID = "NULL";
if (comboBoxBM.SelectedValue.ToString() != "0")
{
sBMID = comboBoxBM.SelectedValue.ToString();
}
sqlComm.CommandText = "INSERT INTO 销售出库汇总表 (单位ID, 单据编号, 日期, 发票号, 支票号, 价税合计, 业务员ID, BeActive, 操作员ID, 未付款金额, 已付款金额, 付款标记, 备注, 合同号, 销售ID, 部门ID) VALUES (" + iSupplyCompany.ToString() + ", N'" + strCount + "', '" + strDateSYS + "', N'" + textBoxFPH.Text + "', N'" + textBoxZPH.Text + "', " + labelSJJE.Text + ", " + comboBoxYWY.SelectedValue.ToString() + ", 1, " + intUserID.ToString() + ", " + labelSJJE.Text + ", 0, 0, N'" + textBoxBZ.Text + "', N'" + textBoxHTH.Text + "', " + intBKP.ToString() + "," + sBMID + ")";
sqlComm.ExecuteNonQuery();
//取得单据号
sqlComm.CommandText = "SELECT @@IDENTITY";
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
string sBillNo = sqldr.GetValue(0).ToString();
sqldr.Close();
//单据明细
string strTemp = "";
for (i = 0; i < dataGridViewDJMX.Rows.Count; i++)
{
if (dataGridViewDJMX.Rows[i].IsNewRow)
{
continue;
}
//出库标志
if (dataGridViewDJMX.Rows[i].Cells[0].Value.ToString() == "")
{
dataGridViewDJMX.Rows[i].Cells[0].Value = 0;
}
if (!Convert.ToBoolean(dataGridViewDJMX.Rows[i].Cells[0].Value))
{
continue;
}
strTemp = "";
if (dataGridViewDJMX.Rows[i].Cells[15].Value.ToString() == "" || dataGridViewDJMX.Rows[i].Cells[15].Value.ToString() == "0") //无校对
{
dataGridViewDJMX.Rows[i].Cells[0].Value = 0;
continue;
}
if (Convert.ToBoolean(dataGridViewDJMX.Rows[i].Cells[17].Value)) //赠品
{
sqlComm.CommandText = "INSERT INTO 销售出库明细表 (单据ID, 商品ID, 库房ID, 原单据ID, 数量, 单价, 金额, 扣率, 赠品, 实计金额, BeActive, 未付款金额, 已付款金额, 未付款数量, 已付款数量, 毛利, 原单据明细ID) VALUES (" + sBillNo + ", " + dataGridViewDJMX.Rows[i].Cells[14].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[15].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[18].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[8].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[9].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[10].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[11].Value.ToString() + ", " + Convert.ToInt32(dataGridViewDJMX.Rows[i].Cells[17].Value).ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[12].Value.ToString() + ", 1, 0, 0, 0, 0, 0, " + dataGridViewDJMX.Rows[i].Cells[20].Value.ToString() + ")";
}
else
{
sqlComm.CommandText = "INSERT INTO 销售出库明细表 (单据ID, 商品ID, 库房ID, 原单据ID, 数量, 单价, 金额, 扣率, 赠品, 实计金额, BeActive, 未付款金额, 已付款金额, 未付款数量, 已付款数量, 毛利, 原单据明细ID) VALUES (" + sBillNo + ", " + dataGridViewDJMX.Rows[i].Cells[14].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[15].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[18].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[8].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[9].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[10].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[11].Value.ToString() + ", " + Convert.ToInt32(dataGridViewDJMX.Rows[i].Cells[17].Value).ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[12].Value.ToString() + ", 1, " + dataGridViewDJMX.Rows[i].Cells[12].Value.ToString() + ", 0," + dataGridViewDJMX.Rows[i].Cells[8].Value.ToString() + ", 0, " + dataGridViewDJMX.Rows[i].Cells[19].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[20].Value.ToString() + ")";
}
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "UPDATE 销售商品制单明细表 SET 未出库数量 =未出库数量-" + dataGridViewDJMX.Rows[i].Cells[8].Value.ToString() + " WHERE (ID = " + dataGridViewDJMX.Rows[i].Cells[16].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
}
//应付账
//sqlComm.CommandText = "UPDATE 单位表 SET 应收账款 = 应收账款 + " + labelSJJE.Text + " WHERE (ID = " + iSupplyCompany.ToString() + ")";
//sqlComm.ExecuteNonQuery();
sqlta.Commit();
}
catch (Exception ex)
{
MessageBox.Show("数据库错误：" + ex.Message.ToString(), "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlta.Rollback();
saveToolStripButton.Enabled = true;
return;
}
finally
{
sqlConn.Close();
}
//changeKC();
checkRKView();
//MessageBox.Show("销售出库校对单保存成功", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
labelDJBH.Text = strCount;
this.Text = "销售出库校对单：" + labelDJBH.Text;
isSaved = true;
bool bClose = false;
//if (MessageBox.Show("销售出库校对单保存成功，是否关闭单据窗口？", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
//{
bClose = true;
//}
if (MessageBox.Show("销售出库校对单保存成功，是否继续开始另一份单据？", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
MDIBusiness mdiT = (MDIBusiness)this.MdiParent;
mdiT.销售出库校对CToolStripMenuItem_Click(null, null);
}
if (bClose)
{
this.Close();
}
}
private void saveToolStripButton_Click(object sender, EventArgs e)
{
int i, j;
decimal dKUL = 0;
decimal dKUL1 = 0;
//保存完毕
if (isSaved)
{
MessageBox.Show("库存商品账目处理单已经保存,单据号为：" + labelDJBH.Text, "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
//格式确认
if (!countAmount())
{
MessageBox.Show("库存商品账目处理单明细格式错误", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
if (toolStripStatusLabelMXJLS.Text == "0")
{
MessageBox.Show("没有库存商品账目单商品", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (MessageBox.Show("请检库存商品账目处理单内容，是否继续保存？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.No)
{
return;
}
string strCount = "", strDateSYS = "", strKey = "CTZ";
System.Data.SqlClient.SqlTransaction sqlta;
sqlConn.Open();
sqlta = sqlConn.BeginTransaction();
sqlComm.Transaction = sqlta;
try
{
//得到表单号
//得到服务器日期
sqlComm.CommandText = "SELECT GETDATE() AS 日期";
sqldr = sqlComm.ExecuteReader();
while (sqldr.Read())
{
strDateSYS = Convert.ToDateTime(sqldr.GetValue(0).ToString()).ToShortDateString();
}
sqldr.Close();
//得到日期
sqlComm.CommandText = "SELECT 时间 FROM 表单计数表 WHERE (时间 = CONVERT(DATETIME, '" + strDateSYS + " 00:00:00', 102))";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Close();
}
else //服务器时间不吻合
{
sqldr.Close();
//修正日期及计数器
sqlComm.CommandText = "UPDATE 表单计数表 SET 时间 = '" + strDateSYS + "', 计数 = 1";
sqlComm.ExecuteNonQuery();
}
//得到计数器
sqlComm.CommandText = "SELECT 计数 FROM 表单计数表 WHERE (关键词 = N'" + strKey + "')";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Read();
strCount = sqldr.GetValue(0).ToString();
sqldr.Close();
//增加计数器
sqlComm.CommandText = "UPDATE 表单计数表 SET 计数 = 计数 + 1 WHERE (关键词 = N'" + strKey + "')";
sqlComm.ExecuteNonQuery();
}
else
{
sqldr.Close();
}
if (strCount != "")
{
strCount = string.Format("{0:D3}", Int32.Parse(strCount));
strCount = strKey.ToUpper() + Convert.ToDateTime(strDateSYS).ToString("yyyyMMdd") + strCount;
}
else
{
MessageBox.Show("数据错误", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
sqlConn.Close();
return;
}
//表单汇总
sqlComm.CommandText = "INSERT INTO 库存商品账目处理汇总表 (单据编号, 日期, 业务员ID, 操作员ID, 备注, BeActive) VALUES (N'" + strCount + "', '" + strDateSYS + "', " + comboBoxYWY.SelectedValue.ToString() + ", " + intUserID.ToString() + ", N'" + textBoxBZ.Text + "', 1)";
sqlComm.ExecuteNonQuery();
//取得单据号
sqlComm.CommandText = "SELECT @@IDENTITY";
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
string sBillNo = sqldr.GetValue(0).ToString();
sqldr.Close();
//单据明细
for (i = 0; i < dataGridViewDJMX.Rows.Count; i++)
{
if (dataGridViewDJMX.Rows[i].IsNewRow)
{
continue;
}
sqlComm.CommandText = "INSERT INTO 库存商品账目处理明细表 (单据ID, 商品ID, 库房ID, 原库存成本价, 原库存数量, 原库存金额, 库存成本价, 库存数量, 库存金额) VALUES (" + sBillNo + ", " + dataGridViewDJMX.Rows[i].Cells[13].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[14].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[10].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[11].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[12].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[7].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[8].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[9].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
//总库存
sqlComm.CommandText = "UPDATE 商品表 SET 库存成本价 = " + dataGridViewDJMX.Rows[i].Cells[7].Value.ToString() + ", 库存数量 = " + dataGridViewDJMX.Rows[i].Cells[8].Value.ToString() + ", 库存金额 = " + dataGridViewDJMX.Rows[i].Cells[9].Value.ToString() + " WHERE (ID = " + dataGridViewDJMX.Rows[i].Cells[13].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "INSERT INTO 商品历史账表 (日期, 商品ID, 业务员ID, 单据编号, 摘要, 总结存数量, 总结存金额, BeActive) VALUES ('" + strDateSYS + "', " + dataGridViewDJMX.Rows[i].Cells[13].Value.ToString() + ", " + comboBoxYWY.SelectedValue.ToString() + ", N'" + strCount + "', N'库存商品账目处理', " + dataGridViewDJMX.Rows[i].Cells[8].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[9].Value.ToString() + ", 1)";
sqlComm.ExecuteNonQuery();
//分库存
sqlComm.CommandText = "UPDATE 库存表 SET 库存成本价 = " + dataGridViewDJMX.Rows[i].Cells[7].Value.ToString() + ", 库存数量 = " + dataGridViewDJMX.Rows[i].Cells[8].Value.ToString() + ", 库存金额 = " + dataGridViewDJMX.Rows[i].Cells[9].Value.ToString() + " WHERE (库房ID = " + dataGridViewDJMX.Rows[i].Cells[14].Value.ToString() + ") AND (商品ID = " + dataGridViewDJMX.Rows[i].Cells[13].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "INSERT INTO 商品库房历史账表 (库房ID , 日期, 商品ID, 业务员ID, 单据编号, 摘要, 库房结存数量, 库房结存金额, BeActive) VALUES (" + dataGridViewDJMX.Rows[i].Cells[14].Value.ToString() + ", '" + strDateSYS + "', " + dataGridViewDJMX.Rows[i].Cells[13].Value.ToString() + ", " + comboBoxYWY.SelectedValue.ToString() + ", N'" + strCount + "', N'库存商品账目处理', " + dataGridViewDJMX.Rows[i].Cells[8].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[9].Value.ToString() + ", 1)";
sqlComm.ExecuteNonQuery();
}
sqlta.Commit();
}
catch (Exception ex)
{
MessageBox.Show("数据库错误：" + ex.Message.ToString(), "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlta.Rollback();
return;
}
finally
{
sqlConn.Close();
}
//MessageBox.Show("库存商品账目处理单保存成功", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
labelDJBH.Text = strCount;
this.Text = "库存商品账目处理单：" + labelDJBH.Text;
isSaved = true;
bool bClose = false;
if (MessageBox.Show("库存商品账目处理单保存成功，是否关闭单据窗口？", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
bClose = true;
}
if (MessageBox.Show("是否继续开始另一份单椐？", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
MDIBusiness mdiT = (MDIBusiness)this.MdiParent;
mdiT.商品账目处理AToolStripMenuItem_Click(null, null);
}
if (bClose)
{
this.Close();
}
}
private void saveToolStripButton_Click(object sender, EventArgs e)
{
int i, j;
//保存完毕
if (isSaved)
{
MessageBox.Show("库存盘点表已经保存,单据号为：" + labelDJBH.Text, "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
if (alKF.Count == 0)
{
MessageBox.Show("请选择商品库房", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (dataGridViewDJMX.RowCount < 1)
{
MessageBox.Show("没有盘点商品", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (MessageBox.Show("请检查库存盘点表内容,该制单内容不可更改，是否继续保存？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.No)
{
return;
}
saveToolStripButton.Enabled = false;
string strCount = "", strDateSYS = "", strKey = "CPD";
System.Data.SqlClient.SqlTransaction sqlta;
sqlConn.Open();
sqlta = sqlConn.BeginTransaction();
sqlComm.Transaction = sqlta;
try
{
//得到表单号
//得到服务器日期
sqlComm.CommandText = "SELECT GETDATE() AS 日期";
sqldr = sqlComm.ExecuteReader();
while (sqldr.Read())
{
strDateSYS = Convert.ToDateTime(sqldr.GetValue(0).ToString()).ToShortDateString();
}
sqldr.Close();
//得到日期
sqlComm.CommandText = "SELECT 时间 FROM 表单计数表 WHERE (时间 = CONVERT(DATETIME, '" + strDateSYS + " 00:00:00', 102))";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Close();
}
else //服务器时间不吻合
{
sqldr.Close();
//修正日期及计数器
sqlComm.CommandText = "UPDATE 表单计数表 SET 时间 = '" + strDateSYS + "', 计数 = 1";
sqlComm.ExecuteNonQuery();
}
//得到计数器
sqlComm.CommandText = "SELECT 计数 FROM 表单计数表 WHERE (关键词 = N'" + strKey + "')";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Read();
strCount = sqldr.GetValue(0).ToString();
sqldr.Close();
//增加计数器
sqlComm.CommandText = "UPDATE 表单计数表 SET 计数 = 计数 + 1 WHERE (关键词 = N'" + strKey + "')";
sqlComm.ExecuteNonQuery();
}
else
{
sqldr.Close();
}
if (strCount != "")
{
strCount = string.Format("{0:D3}", Int32.Parse(strCount));
strCount = strKey.ToUpper() + Convert.ToDateTime(strDateSYS).ToString("yyyyMMdd") + strCount;
}
else
{
MessageBox.Show("数据错误", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
sqlConn.Close();
return;
}
//表单汇总
sqlComm.CommandText = "INSERT INTO 库存盘点汇总表(单据编号, 日期, 业务员ID, 操作员ID, 盘点标记, 备注, 库房ID, 商品ID, 分类ID, 数量合计, 金额合计, 盘损数量合计, 盘损金额合计, BeActive) VALUES (N'" + strCount + "', '" + strDateSYS + "', " + comboBoxYWY.SelectedValue.ToString() + ", " + intUserID.ToString() + ", 0, N'" + textBoxBZ.Text.Trim() + "', " + intKFID.ToString() + ", " + intCommID.ToString() + ", " + intClassID.ToString() + ", " + labelSLHJ.Text + ", " + labelJEHJ.Text + ", 0, 0, 1)";
sqlComm.ExecuteNonQuery();
//取得单据号
sqlComm.CommandText = "SELECT @@IDENTITY";
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
string sBillNo = sqldr.GetValue(0).ToString();
sqldr.Close();
//单据明细
for (i = 0; i < dataGridViewDJMX.Rows.Count; i++)
{
if (dataGridViewDJMX.Rows[i].IsNewRow)
{
continue;
}
sqlComm.CommandText = "INSERT INTO 库存盘点明细表 (单据ID, 商品ID, 结存数量, 结存金额, 实盘数量, 备注, 盘点标志,库房ID) VALUES (" + sBillNo + ", " + dataGridViewDJMX.Rows[i].Cells[6].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[3].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[4].Value.ToString() + ", 0, N'" + dataGridViewDJMX.Rows[i].Cells[5].Value.ToString() + "', 0, " + dataGridViewDJMX.Rows[i].Cells[7].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
}
sqlta.Commit();
}
catch (Exception ex)
{
MessageBox.Show("数据库错误：" + ex.Message.ToString(), "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlta.Rollback();
saveToolStripButton.Enabled = true;
return;
}
finally
{
sqlConn.Close();
}
//MessageBox.Show("库存盘点表保存成功", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
labelDJBH.Text = strCount;
this.Text = "库存盘点表：" + labelDJBH.Text;
isSaved = true;
if (MessageBox.Show("库存盘点表保存成功，是否继续开始另一份单据？", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
MDIBusiness mdiT = (MDIBusiness)this.MdiParent;
mdiT.准备盘点表AToolStripMenuItem_Click(null, null);
}
if (MessageBox.Show("是否关闭制单窗口", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
this.Close();
}
}
private void saveToolStripButton_Click(object sender, EventArgs e)
{
int i, j;
string strDT;
string sTemp;
cGetInformation.getSystemDateTime();
//strDT = cGetInformation.strSYSDATATIME;
strDT = dateTimePickerKPRQ.Value.ToShortDateString();
//保存完毕
if (isSaved)
{
MessageBox.Show("发票已经开具，发票号为：" + textBoxFPH.Text, "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
if (textBoxFPH.Text.Trim() == "")
{
MessageBox.Show("请输入发票号", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
if (iSupplyCompany == 0)
{
MessageBox.Show("请选择单位", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (!countAmount())
{
MessageBox.Show("发票明细错误", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
if (dataGridViewDJMX.RowCount < 1)
{
MessageBox.Show("没有选择开票项目", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (MessageBox.Show("请检查发票内容，是否继续保存？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.No)
{
return;
}
System.Data.SqlClient.SqlTransaction sqlta;
sqlConn.Open();
//发票号查重
try
{
sTemp = textBoxFPH.Text.Trim().Substring(0, 3);
}
catch
{
sTemp = "";
}
if (textBoxFPH.Text.Trim() != "现金不开票" && sTemp != "不开票")
{
sqlComm.CommandText = "SELECT 发票汇总表.ID, 发票汇总表.发票号, 单位表.单位名称, 发票汇总表.发票总额, 发票汇总表.日期 FROM 发票汇总表 INNER JOIN 单位表 ON 发票汇总表.单位ID = 单位表.ID WHERE (发票汇总表.发票号 = N'" + textBoxFPH.Text + "') AND (发票汇总表.BeActive <> 0)";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Read();
MessageBox.Show("发票号重复：" + sqldr.GetValue(2).ToString() + "(" + sqldr.GetValue(4).ToString() + " ￥" + sqldr.GetValue(3).ToString() + ")", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
sqlConn.Close();
return;
}
sqldr.Close();
}
sqlta = sqlConn.BeginTransaction();
sqlComm.Transaction = sqlta;
try
{
//表单汇总
sqlComm.CommandText = "INSERT INTO 发票汇总表 (发票号, 单位ID, 备注, 发货方式, 单号, 操作员ID, 原开票金额, 发票总额, BeActive, 发票类型, 日期) VALUES (N'" + textBoxFPH.Text + "', " + iSupplyCompany.ToString() + ", N'" + textBoxBZ.Text + "', N'" + comboBoxFHFS.Text + "', N'" + textBoxDH.Text + "', " + intUserID.ToString() + ", " + labelJEHJ.Text + ", " + labelSJJE.Text + ", 1, " + comboBoxStyle.SelectedIndex.ToString() + ", '" + strDT + "')";
sqlComm.ExecuteNonQuery();
//取得单据号
sqlComm.CommandText = "SELECT @@IDENTITY";
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
string sBillNo = sqldr.GetValue(0).ToString();
sqldr.Close();
//明细
for (i = 0; i < dataGridViewDJMX.Rows.Count; i++)
{
switch (comboBoxStyle.SelectedIndex)
{
case 0: //购进
switch (comboBoxGD.SelectedIndex)
{
case 0: //按照单据
sqlComm.CommandText = "INSERT INTO 发票明细表 (发票ID, 单据ID, 冲抵ID, 单据编号, 冲抵编号, 原开票总额, 发票总额, 发货方式, 单号, 备注1, 备注2) VALUES (" + sBillNo + ", " + dataGridViewDJMX.Rows[i].Cells[0].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[1].Value.ToString() + ", N'" + dataGridViewDJMX.Rows[i].Cells[2].Value.ToString() + "', N'" + dataGridViewDJMX.Rows[i].Cells[3].Value.ToString() + "', " + dataGridViewDJMX.Rows[i].Cells[4].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[5].Value.ToString() + ", N'" + dataGridViewDJMX.Rows[i].Cells[6].Value.ToString() + "', N'" + dataGridViewDJMX.Rows[i].Cells[7].Value.ToString() + "', N'" + dataGridViewDJMX.Rows[i].Cells[8].Value.ToString() + "', N'" + dataGridViewDJMX.Rows[i].Cells[9].Value.ToString() + "')";
sqlComm.ExecuteNonQuery();
sTemp = dataGridViewDJMX.Rows[i].Cells[2].Value.ToString().Substring(0, 3);
switch (sTemp)
{
case "ADH":
sqlComm.CommandText = "UPDATE 进货入库汇总表 SET 发票号 = N'" + textBoxFPH.Text + "' WHERE (ID = " + dataGridViewDJMX.Rows[i].Cells[0].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "UPDATE 购进商品制单表 SET 发票号 = N'" + textBoxFPH.Text + "' WHERE (ID = " + dataGridViewDJMX.Rows[i].Cells[1].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
break;
case "ATH":
sqlComm.CommandText = "UPDATE 进货退出汇总表 SET 发票号 = N'" + textBoxFPH.Text + "' WHERE (ID = " + dataGridViewDJMX.Rows[i].Cells[0].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
break;
case "ATB":
sqlComm.CommandText = "UPDATE 购进退补差价汇总表 SET 发票号 = N'" + textBoxFPH.Text + "' WHERE (ID = " + dataGridViewDJMX.Rows[i].Cells[0].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
break;
default:
break;
}
break;
case 1: //按照商品
sqlComm.CommandText = "INSERT INTO 发票明细表 (发票ID, 冲抵ID, 单据ID, 原开票总额, 发票总额, 发货方式, 单号, 商品ID, 单据编号) VALUES (" + sBillNo + ", " + dataGridViewDJMX.Rows[i].Cells[7].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[6].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[5].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[8].Value.ToString() + ", N'', N''," + dataGridViewDJMX.Rows[i].Cells[9].Value.ToString() + ",N'" + dataGridViewDJMX.Rows[i].Cells[1].Value.ToString() + "')";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "UPDATE 进货入库汇总表 SET 发票号 = N'" + textBoxFPH.Text + "' WHERE (ID = " + dataGridViewDJMX.Rows[i].Cells[6].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "UPDATE 购进商品制单表 SET 发票号 = N'" + textBoxFPH.Text + "' WHERE (ID = " + dataGridViewDJMX.Rows[i].Cells[7].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
break;
}
break;
case 1: //销售
switch (comboBoxGD.SelectedIndex)
{
case 0: //按照单据
sqlComm.CommandText = "INSERT INTO 发票明细表 (发票ID, 单据ID, 冲抵ID, 单据编号, 冲抵编号, 原开票总额, 发票总额, 发货方式, 单号, 备注1, 备注2) VALUES (" + sBillNo + ", " + dataGridViewDJMX.Rows[i].Cells[0].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[1].Value.ToString() + ", N'" + dataGridViewDJMX.Rows[i].Cells[2].Value.ToString() + "', N'" + dataGridViewDJMX.Rows[i].Cells[3].Value.ToString() + "', " + dataGridViewDJMX.Rows[i].Cells[4].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[5].Value.ToString() + ", N'" + dataGridViewDJMX.Rows[i].Cells[6].Value.ToString() + "', N'" + dataGridViewDJMX.Rows[i].Cells[7].Value.ToString() + "', N'" + dataGridViewDJMX.Rows[i].Cells[8].Value.ToString() + "', N'" + dataGridViewDJMX.Rows[i].Cells[9].Value.ToString() + "')";
sqlComm.ExecuteNonQuery();
sTemp = dataGridViewDJMX.Rows[i].Cells[2].Value.ToString().Substring(0, 3);
switch (sTemp)
{
case "BKP":
sqlComm.CommandText = "UPDATE 销售商品制单表 SET 发票号 = N'" + textBoxFPH.Text + "' WHERE (ID = " + dataGridViewDJMX.Rows[i].Cells[0].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
break;
case "BTH":
sqlComm.CommandText = "UPDATE 销售退出汇总表 SET 发票号 = N'" + textBoxFPH.Text + "' WHERE (ID = " + dataGridViewDJMX.Rows[i].Cells[0].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
break;
case "BTB":
sqlComm.CommandText = "UPDATE 销售退补差价汇总表 SET 发票号 = N'" + textBoxFPH.Text + "' WHERE (ID = " + dataGridViewDJMX.Rows[i].Cells[0].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
break;
default:
break;
}
break;
case 1: //按照商品
sqlComm.CommandText = "INSERT INTO 发票明细表 (发票ID, 冲抵ID, 单据ID, 原开票总额, 发票总额, 发货方式, 单号, 商品ID, 单据编号) VALUES (" + sBillNo + ", " + dataGridViewDJMX.Rows[i].Cells[8].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[8].Value.ToString() + ", " + dataGridViewDJMX.Rows[4].Cells[2].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[5].Value.ToString() + ", N'" + dataGridViewDJMX.Rows[i].Cells[6].Value.ToString() + "', N'" + dataGridViewDJMX.Rows[i].Cells[7].Value.ToString() + "'," + dataGridViewDJMX.Rows[i].Cells[9].Value.ToString() + ",N'" + dataGridViewDJMX.Rows[i].Cells[1].Value.ToString() + "')";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "UPDATE 销售商品制单表 SET 发票号 = N'" + textBoxFPH.Text + "' WHERE (ID = " + dataGridViewDJMX.Rows[i].Cells[6].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
break;
}
break;
}
}
sqlta.Commit();
}
catch (Exception ex)
{
MessageBox.Show("数据库错误：" + ex.Message.ToString(), "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlta.Rollback();
return;
}
finally
{
sqlConn.Close();
}
bool bClose = false;
if (MessageBox.Show("发票开具成功，是否关闭单据窗口？", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
bClose = true;
}
//MessageBox.Show("发票开具成功", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
isSaved = true;
textBoxFPH.Enabled = false;
if (MessageBox.Show("是否继续发票开具？", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
MDIBusiness mdiT = (MDIBusiness)this.MdiParent;
mdiT.开具发票AToolStripMenuItem_Click(null, null);
}
if (bClose)
{
this.Close();
}
}
