/// <summary> /// 更新一条数据 /// </summary> public string Update(UFIDA.U8.UAP.CustomApp.ControlForm.Model._Requisitions model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update _Requisitions set "); if (model.GUIDHead != null) { strSql.Append("GUIDHead=N'" + model.GUIDHead + "',"); } else { strSql.Append("GUIDHead= null ,"); } if (model.Remark != null) { strSql.Append("Remark=N'" + model.Remark + "',"); } else { strSql.Append("Remark= null ,"); } int n = strSql.ToString().LastIndexOf(","); strSql.Remove(n, 1); strSql.Append(" where iID=" + model.iID + ""); return(strSql.ToString()); }
/// <summary> /// 增加一条数据 /// </summary> public string Add(UFIDA.U8.UAP.CustomApp.ControlForm.Model._Requisitions model) { StringBuilder strSql = new StringBuilder(); StringBuilder strSql1 = new StringBuilder(); StringBuilder strSql2 = new StringBuilder(); if (model.GUIDHead != null) { strSql1.Append("GUIDHead,"); strSql2.Append("N'" + model.GUIDHead.ToString() + "',"); } if (model.cCode != null) { strSql1.Append("cCode,"); strSql2.Append("N'" + model.cCode + "',"); } if (model.SerialNo != null) { strSql1.Append("SerialNo,"); strSql2.Append("N'" + model.SerialNo + "',"); } if (model.Remark != null) { strSql1.Append("Remark,"); strSql2.Append("N'" + model.Remark + "',"); } strSql.Append("insert into _Requisitions("); 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()); }
private void btnOK_Click(object sender, EventArgs e) { try { if (!clsUserRight.chkRight(sUserID, 4010)) { throw new Exception("没有权限"); } if (gridView1.RowCount == 0) { throw new Exception("请输入表体"); } string sErr = ""; try { gridView1.FocusedRowHandle -= 1; gridView1.FocusedRowHandle += 1; } catch { } if (lookUpEditPerson.EditValue == null) { lookUpEditPerson.Focus(); throw new Exception("请设置领用人"); } int iCou = 0; SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString); conn.Open(); SqlTransaction tran = conn.BeginTransaction(); try { string sSerCode = txtCode.Text.Trim(); Model._Requisition model = new UFIDA.U8.UAP.CustomApp.ControlForm.Model._Requisition(); string sState = ""; string sSQL = "select * from _Requisition where cCode = N'" + txtCode.Text.Trim() + "'"; DataTable dtTemp = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtTemp != null && dtTemp.Rows.Count > 0) { if (dtTemp.Rows[0]["AuditUserName"].ToString().Trim() != "") { throw new Exception("单据已经审核,不能保存"); } } sSQL = "select getdate()"; dtTemp = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; DateTime dateNow = BaseFunction.ReturnDate(dtTemp.Rows[0][0]); //新增 if (txtCode.Text.Trim() == "") { sState = "add"; model.CreateUserName = sUserName; model.CreateDate = DateTime.Today; long iCode = 0; sSQL = "select max(cCode) as cCode from [_Requisition] where cCode like '111111%'"; sSQL = sSQL.Replace("111111", dtmCode.DateTime.ToString("yyMMdd")); DataTable dtCode = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtCode == null && dtCode.Rows.Count == 0) { iCode = 1; } else { string s = dtCode.Rows[0]["cCode"].ToString().Trim(); if (s.Length > 6) { iCode = BaseFunction.ReturnLong(s.Substring(6)); } else { iCode = BaseFunction.ReturnLong(s); } iCode += 1; } string sCode = iCode.ToString().Trim(); while (sCode.Length < 4) { sCode = "0" + sCode; } sCode = dtmCode.DateTime.ToString("yyMMdd") + sCode; model.cCode = sCode; model.dDate = dtmCode.DateTime; model.Person = lookUpEditPerson.EditValue.ToString().Trim(); if (lookUpEditDep.EditValue != null) { model.DepCode = lookUpEditDep.EditValue.ToString().Trim(); } model.Remark = txtRemark.Text.Trim(); model.CreateUserName = sUserName; model.CreateDate = DateTime.Now; DAL._Requisition dal = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL._Requisition(); sSQL = dal.Add(model); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); } else { sState = "edit"; model.cCode = txtCode.Text.Trim(); model.dDate = dtmCode.DateTime; model.Person = lookUpEditPerson.EditValue.ToString().Trim(); model.DepCode = lookUpEditDep.EditValue.ToString().Trim(); model.Remark = txtRemark.Text.Trim(); model.CreateUserName = txtCreateUserName.Text.Trim(); model.CreateDate = dtmCreate.DateTime; DAL._Requisition dal = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL._Requisition(); sSQL = dal.Update(model); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); } sSQL = @"update _FrockClamp set iState = 1 where SerialNo in (select SerialNo from _Requisitions where cCode = N'" + txtCode.Text.Trim() + "') and isnull(Closed,'') = '' "; DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); sSQL = "delete _Requisitions where cCode = '" + model.cCode + "'"; DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); for (int i = 0; i < gridView1.RowCount; i++) { if (gridView1.GetRowCellValue(i, gridColSerialNo).ToString().Trim() != "") { sSQL = @" select a.*,b.Timesed from [dbo].[_FrockClamp] a inner join (select sum(times) as Timesed,SerialNo from [_GiveBacks] group by SerialNo) b on a.SerialNo = b.SerialNo where a.SerialNo = N'111111' "; sSQL = sSQL.Replace("111111", gridView1.GetRowCellValue(i, gridColSerialNo).ToString().Trim()); DataTable dtChk = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtChk != null && dtChk.Rows.Count > 0) { //寿命次数 long l1 = BaseFunction.ReturnLong(dtChk.Rows[0]["Times"]); long l2 = BaseFunction.ReturnLong(dtChk.Rows[0]["Timesed"]); if (l1 > 0 && l1 <= l2) { sErr = sErr + "行" + (i + 1).ToString() + "寿命次数已到\n"; continue; } DateTime dLC = BaseFunction.ReturnDate(dtChk.Rows[0]["LC"]); if (dLC <= dateNow) { sErr = sErr + "行" + (i + 1).ToString() + "寿命期限已到\n"; continue; } } bool b = true; for (int j = 0; j < i; j++) { if (gridView1.GetRowCellValue(i, gridColSerialNo).ToString().Trim() == gridView1.GetRowCellValue(j, gridColSerialNo).ToString().Trim()) { sErr = sErr + "行" + (i + 1).ToString() + "工装重复\n"; b = false; continue; } } if (b == false) { continue; } Model._Requisitions models = new UFIDA.U8.UAP.CustomApp.ControlForm.Model._Requisitions(); models.cCode = model.cCode; models.SerialNo = gridView1.GetRowCellValue(i, gridColSerialNo).ToString().Trim(); models.Remark = gridView1.GetRowCellValue(i, gridColRemark).ToString().Trim(); DAL._Requisitions dals = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL._Requisitions(); sSQL = dals.Add(models); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); sSQL = "select iState from _FrockClamp where SerialNo = N'111111'"; sSQL = sSQL.Replace("111111", gridView1.GetRowCellValue(i, gridColSerialNo).ToString().Trim()); dtTemp = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtTemp != null && BaseFunction.ReturnInt(dtTemp.Rows[0]["iState"]) == 1) { sSQL = "update _FrockClamp set iState = 2 where SerialNo = N'111111'"; sSQL = sSQL.Replace("111111", gridView1.GetRowCellValue(i, gridColSerialNo).ToString().Trim()); iCou = iCou + DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); } else { sErr = sErr + "行" + (i + 1).ToString() + "工装不是可用状态\n"; continue; } } } if (sErr.Length > 0) { throw new Exception(sErr); } if (iCou > 0) { tran.Commit(); MessageBox.Show("OK\n"); } else { throw new Exception("no data"); } this.DialogResult = DialogResult.OK; } catch (Exception ee) { MessageBox.Show(ee.Message); tran.Rollback(); } } catch (Exception ee) { MessageBox.Show(ee.Message); } }