private void btnAudit_Click(object sender, EventArgs e)
{
try
{
string sErr = "";
try
{
gridView1.FocusedRowHandle -= 1;
gridView1.FocusedRowHandle += 1;
}
catch { }
int iCou = 0;
SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString);
conn.Open();
//启用事务
SqlTransaction tran = conn.BeginTransaction();
try
{
string s部门编码 = l部门编码.Text.Trim();
if (s部门编码 == "")
{
lookUpEditcDepCode.Focus();
throw new Exception("部门编码不能为空");
}
string s会计期间 = l会计期间.Text.Trim();
if (s会计期间 == "")
{
lookUpEditcDepCode.Focus();
throw new Exception("会计期间不能为空");
}
string sSQL = @"
select cbaccounter ,*
from rdrecord11 a inner join rdrecords11 b on a.ID = b.ID inner join Inventory c on b.cInvCode = c.cInvCode
where a.dDate >= '111111' and a.dDate < '222222'
and a.cDepCode = '333333'
and a.cWhCode in ('01','02','03','04','05','06','14','19')
and c.cInvCCode not like '8%'
and ISNULL(cbaccounter ,'') = ''
";
sSQL = sSQL.Replace("111111", s会计期间 + "-01");
sSQL = sSQL.Replace("222222", TH.BaseClass.BaseFunction.ReturnDate(s会计期间 + "-01").AddMonths(1).ToString("yyyy-MM-dd"));
sSQL = sSQL.Replace("333333", s部门编码);
DataTable dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
throw new Exception("当前年度，当前部门有尚未记账的出库单");
}
string s下一会计期间 = BaseFunction.ReturnDate(lookUpEdit会计期间.Text.Trim() + "-01").AddMonths(1).ToString("yyyy-MM");
sSQL = @"
delete _QCMaterial where 1=1
";
sSQL = sSQL.Replace("1=1", "1=1 and 会计期间 = '" + s下一会计期间 + "' and 部门 = '" + s部门编码 + "'");
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
for (int i = 0; i < gridView1.RowCount; i++)
{
for (int j = 0; j < gridView1.Columns.Count; j++)
{
string sColName = gridView1.Columns[j].Name.Trim();
if (sColName.StartsWith("gridColtemp产品数量_"))
{
string sProInvCode = sColName.Substring(16);
int iLength = sProInvCode.LastIndexOf('_');
sProInvCode = sProInvCode.Substring(0, iLength);
decimal d单价 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridCol本月耗用单价), 6);
string sCol金额 = sColName.Replace("gridColtemp产品数量_", "产品金额_");
decimal d产品耗用材料金额 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, sCol金额), 6);
sSQL = "update _ProMaterial set 审核人 = '" + sUserID + "',审核日期 = getdate(),单价 = 333333,金额 = 444444 where 1=1";
sSQL = sSQL.Replace("1=1", "1=1 and 会计期间 = '" + lookUpEdit会计期间.Text.Trim() + "' and 部门 = '" + s部门编码 + "' and 产品编码 = '" + sProInvCode + "' and 存货编码 = '" + gridView1.GetRowCellValue(i, gridCol材料编码).ToString().Trim() + "'");
sSQL = sSQL.Replace("333333", d单价.ToString());
sSQL = sSQL.Replace("444444", d产品耗用材料金额.ToString());
iCou = iCou + DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
}
decimal d金额 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridCol月末结存金额), 6);
decimal d数量 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridCol月末结存数量), 6);
if (d金额 == 0)
{
continue;
}
sSQL = @"
insert into _QCMaterial(会计期间, 部门, 存货编码, 期初数量, 期初金额)
values('111111','222222','333333',444444,555555)
";
sSQL = sSQL.Replace("111111", s下一会计期间);
sSQL = sSQL.Replace("222222", s部门编码);
sSQL = sSQL.Replace("333333", gridView1.GetRowCellValue(i, gridCol材料编码).ToString().Trim());
sSQL = sSQL.Replace("444444", d数量.ToString());
sSQL = sSQL.Replace("555555", d金额.ToString());
iCou = iCou + DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "update _QCMaterial set 审核人 = '111111' where 会计期间 = '222222' and 部门 = '333333' ";
sSQL = sSQL.Replace("111111", sUserID);
sSQL = sSQL.Replace("222222", s会计期间);
sSQL = sSQL.Replace("333333", s部门编码);
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
sSQL = "delete _车间材料领用汇总月报 where 会计期间 = 'aaaaaa' and 部门编码 = 'bbbbbb'";
sSQL = sSQL.Replace("aaaaaa", s会计期间);
sSQL = sSQL.Replace("bbbbbb", s部门编码);
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
for (int i = 0; i < gridView1.RowCount; i++)
{
for (int j = 0; j < gridView1.Columns.Count; j++)
{
string sColText = gridView1.Columns[j].Caption.ToString().Trim();
string sColName = gridView1.Columns[j].Name.ToString().Trim();
string sFieldName = gridView1.Columns[j].FieldName.ToString().Trim();
if (sColName.StartsWith("gridColtemp产品数量"))
{
string[] s产品 = sFieldName.Split('_');
string s产品编码 = s产品[1].ToString().Trim();
string sFieldName2 = sFieldName.Replace("产品数量", "产品单价");
string sFieldName3 = sFieldName.Replace("产品数量", "产品金额");
decimal d产品数量 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridView1.Columns[sFieldName]), 6);
decimal d产品单价 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridView1.Columns[sFieldName2]), 6);
decimal d产品金额 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridView1.Columns[sFieldName3]), 6);
if (d产品单价 == 0 && d产品数量 == 0)
{
continue;
}
Model._车间材料领用汇总月报 mod = new UFIDA.U8.UAP.CustomApp.ControlForm.Model._车间材料领用汇总月报();
mod.会计期间 = s会计期间;
mod.部门编码 = s部门编码;
mod.产品编码 = s产品编码;
mod.材料编码 = gridView1.GetRowCellValue(i, gridCol材料编码).ToString().Trim();
mod.月初存料单价 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridCol月初存料单价), 6);
mod.月初存料数量 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridCol月初存料数量), 6);
mod.月初存料金额 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridCol月初存料金额), 6);
mod.收发存汇总出库单价 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridCol收发存出库单价), 6);
mod.收发存汇总出库数量 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridCol收发存出库数量), 6);
mod.收发存汇总出库金额 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridCol收发存出库金额), 6);
mod.月末结存单价 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridCol月末结存单价), 6);
mod.月末结存数量 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridCol月末结存数量), 6);
mod.月末结存金额 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridCol月末结存金额), 6);
mod.本月耗用单价 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridCol本月耗用单价), 6);
mod.本月耗用数量 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridCol本月耗用数量), 6);
mod.本月耗用金额 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridCol本月耗用金额), 6);
mod.产品单价 = d产品单价;
mod.产品数量 = d产品数量;
mod.产品金额 = d产品金额;
DAL._车间材料领用汇总月报 dal = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL._车间材料领用汇总月报();
sSQL = dal.Add(mod);
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
}
}
tran.Commit();
label审核.Text = "已审核";
MessageBox.Show("审核成功");
}
catch (Exception error)
{
tran.Rollback();
throw new Exception(error.Message);
}
}
catch (Exception ee)
{
FrmMsgBox msg = new FrmMsgBox();
msg.Text = "审核失败";
msg.richTextBox1.Text = ee.Message;
msg.StartPosition = FormStartPosition.CenterScreen;
msg.ShowDialog();
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public string Add(UFIDA.U8.UAP.CustomApp.ControlForm.Model._车间材料领用汇总月报 model)
{
StringBuilder strSql = new StringBuilder();
StringBuilder strSql1 = new StringBuilder();
StringBuilder strSql2 = new StringBuilder();
if (model.会计期间 != null)
{
strSql1.Append("会计期间,");
strSql2.Append("'" + model.会计期间 + "',");
}
if (model.部门编码 != null)
{
strSql1.Append("部门编码,");
strSql2.Append("'" + model.部门编码 + "',");
}
if (model.产品编码 != null)
{
strSql1.Append("产品编码,");
strSql2.Append("'" + model.产品编码 + "',");
}
if (model.材料编码 != null)
{
strSql1.Append("材料编码,");
strSql2.Append("'" + model.材料编码 + "',");
}
if (model.月初存料数量 != null)
{
strSql1.Append("月初存料数量,");
strSql2.Append("" + model.月初存料数量 + ",");
}
if (model.月初存料单价 != null)
{
strSql1.Append("月初存料单价,");
strSql2.Append("" + model.月初存料单价 + ",");
}
if (model.月初存料金额 != null)
{
strSql1.Append("月初存料金额,");
strSql2.Append("" + model.月初存料金额 + ",");
}
if (model.收发存汇总出库数量 != null)
{
strSql1.Append("收发存汇总出库数量,");
strSql2.Append("" + model.收发存汇总出库数量 + ",");
}
if (model.收发存汇总出库单价 != null)
{
strSql1.Append("收发存汇总出库单价,");
strSql2.Append("" + model.收发存汇总出库单价 + ",");
}
if (model.收发存汇总出库金额 != null)
{
strSql1.Append("收发存汇总出库金额,");
strSql2.Append("" + model.收发存汇总出库金额 + ",");
}
if (model.月末结存数量 != null)
{
strSql1.Append("月末结存数量,");
strSql2.Append("" + model.月末结存数量 + ",");
}
if (model.月末结存单价 != null)
{
strSql1.Append("月末结存单价,");
strSql2.Append("" + model.月末结存单价 + ",");
}
if (model.月末结存金额 != null)
{
strSql1.Append("月末结存金额,");
strSql2.Append("" + model.月末结存金额 + ",");
}
if (model.本月耗用数量 != null)
{
strSql1.Append("本月耗用数量,");
strSql2.Append("" + model.本月耗用数量 + ",");
}
if (model.本月耗用单价 != null)
{
strSql1.Append("本月耗用单价,");
strSql2.Append("" + model.本月耗用单价 + ",");
}
if (model.本月耗用金额 != null)
{
strSql1.Append("本月耗用金额,");
strSql2.Append("" + model.本月耗用金额 + ",");
}
if (model.产品数量 != null)
{
strSql1.Append("产品数量,");
strSql2.Append("" + model.产品数量 + ",");
}
if (model.产品单价 != null)
{
strSql1.Append("产品单价,");
strSql2.Append("" + model.产品单价 + ",");
}
if (model.产品金额 != null)
{
strSql1.Append("产品金额,");
strSql2.Append("" + model.产品金额 + ",");
}
if (model.CreateUid != null)
{
strSql1.Append("CreateUid,");
strSql2.Append("'" + model.CreateUid + "',");
}
if (model.CreateDate != null)
{
strSql1.Append("CreateDate,");
strSql2.Append("'" + model.CreateDate + "',");
}
strSql.Append("insert into _车间材料领用汇总月报(");
strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
strSql.Append(")");
strSql.Append(" values (");
strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
strSql.Append(")");
return(strSql.ToString());
}
