/// <summary>
/// 增加一条数据
/// </summary>
public string Add(UFIDA.U8.UAP.CustomApp.ControlForm.Model._Requisition model)
{
StringBuilder strSql = new StringBuilder();
StringBuilder strSql1 = new StringBuilder();
StringBuilder strSql2 = new StringBuilder();
if (model.GUID != null)
{
strSql1.Append("GUID,");
strSql2.Append("N'" + model.GUID.ToString() + "',");
}
if (model.cCode != null)
{
strSql1.Append("cCode,");
strSql2.Append("N'" + model.cCode + "',");
}
if (model.dDate != null)
{
strSql1.Append("dDate,");
strSql2.Append("N'" + model.dDate + "',");
}
if (model.Person != null)
{
strSql1.Append("Person,");
strSql2.Append("N'" + model.Person + "',");
}
if (model.DepCode != null)
{
strSql1.Append("DepCode,");
strSql2.Append("N'" + model.DepCode + "',");
}
if (model.Remark != null)
{
strSql1.Append("Remark,");
strSql2.Append("N'" + model.Remark + "',");
}
if (model.CreateUserName != null)
{
strSql1.Append("CreateUserName,");
strSql2.Append("N'" + model.CreateUserName + "',");
}
if (model.CreateDate != null)
{
strSql1.Append("CreateDate,");
strSql2.Append("N'" + model.CreateDate + "',");
}
if (model.AuditUserName != null)
{
strSql1.Append("AuditUserName,");
strSql2.Append("N'" + model.AuditUserName + "',");
}
if (model.AuditDate != null)
{
strSql1.Append("AuditDate,");
strSql2.Append("N'" + model.AuditDate + "',");
}
strSql.Append("insert into _Requisition(");
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());
}
/// <summary>
/// 更新一条数据
/// </summary>
public string Update(UFIDA.U8.UAP.CustomApp.ControlForm.Model._Requisition model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update _Requisition set ");
if (model.GUID != null)
{
strSql.Append("GUID=N'" + model.GUID + "',");
}
else
{
strSql.Append("GUID= null ,");
}
if (model.dDate != null)
{
strSql.Append("dDate=N'" + model.dDate + "',");
}
else
{
strSql.Append("dDate= null ,");
}
if (model.Person != null)
{
strSql.Append("Person=N'" + model.Person + "',");
}
else
{
strSql.Append("Person= null ,");
}
if (model.DepCode != null)
{
strSql.Append("DepCode=N'" + model.DepCode + "',");
}
else
{
strSql.Append("DepCode= null ,");
}
if (model.Remark != null)
{
strSql.Append("Remark=N'" + model.Remark + "',");
}
else
{
strSql.Append("Remark= null ,");
}
if (model.CreateUserName != null)
{
strSql.Append("CreateUserName=N'" + model.CreateUserName + "',");
}
else
{
strSql.Append("CreateUserName= null ,");
}
if (model.CreateDate != null)
{
strSql.Append("CreateDate=N'" + model.CreateDate + "',");
}
else
{
strSql.Append("CreateDate= null ,");
}
if (model.AuditUserName != null)
{
strSql.Append("AuditUserName=N'" + model.AuditUserName + "',");
}
else
{
strSql.Append("AuditUserName= null ,");
}
if (model.AuditDate != null)
{
strSql.Append("AuditDate=N'" + model.AuditDate + "',");
}
else
{
strSql.Append("AuditDate= null ,");
}
int n = strSql.ToString().LastIndexOf(",");
strSql.Remove(n, 1);
strSql.Append(" where cCode=" + model.cCode + "");
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);
}
}
