public Certificate GetCertificate(string id)
{
Certificate result = null;
var sql =
@"select dbill,dchk,stockid,stockname,
(select sum(itemamttpur) from michkinvitem where billnoid=michkinvmain.billnoid) as m,
(select sum(qfact)-sum(amtaccount) from michkinvitem where billnoid=michkinvmain.billnoid ) as f
from michkinvmain where billno='{0}'";
try
{
this._ado.Open();
var reader = this._ado.ExecuteReader(string.Format(sql, id));
try
{
var cer = new Certificate();
var borrow = new CertificateItem();
var lend = new CertificateItem();
if (reader.Read())
{
//设置凭证信息
cer.Dbill_date = reader["dbill"] as DateTime?;
//设置借项
borrow.SubjectId = reader["stockid"].ToString();
borrow.SubjectName = reader["stockname"].ToString();
borrow.Summary = ((decimal)reader["f"]) >= 0 ? "库存商品" : "其他应付款-" + borrow.SubjectName;
borrow.Money = (decimal)reader["m"];
//设置贷项
lend.SubjectId = reader["stockid"].ToString();
lend.SubjectName = reader["stockname"].ToString();
lend.Summary = ((decimal)reader["f"]) >= 0 ? "其他应付款-" + lend.SubjectName : "库存商品-" + lend.SubjectName;
lend.Money = (decimal)reader["m"];
//
cer.SetItem(borrow, lend);
//
result = cer;
}
}
finally
{
reader.Close();
}
}
finally
{
this._ado.Close();
}
return result;
}
public Certificate GetCertificate(string id)
{
Certificate result = null;
var sql = @"select DBill,DChk,StockId,StockName,SupID,AllName,MainAmtTPur from MiNPIMain where BillNo='{0}'";
try
{
this._ado.Open();
var reader = this._ado.ExecuteReader(string.Format(sql, id));
try
{
var cer = new Certificate();
var borrow = new CertificateItem();
var lend = new CertificateItem();
if (reader.Read())
{
//设置凭证信息
cer.Dbill_date = reader["dbill"] as DateTime?;
//设置借项
borrow.SubjectId = reader["stockid"].ToString();
borrow.SubjectName = reader["stockname"].ToString();
borrow.Summary = "库存商品-" + borrow.SubjectName;
borrow.Money = (decimal)reader["MainAmtTPur"];
//设置贷项
lend.SubjectId = reader["SupID"].ToString();
lend.SubjectName = reader["AllName"].ToString();
lend.Summary = "暂估应付账款-" + lend.SubjectName;
lend.Money = (decimal)reader["MainAmtTPur"];
//
cer.SetItem(borrow, lend);
//
result = cer;
}
}
finally
{
reader.Close();
}
}
finally
{
this._ado.Close();
}
return result;
}
public Certificate GetCertificate(string id)
{
Certificate result = null;
var sql =
@"SELECT DBill,StockOut,StockOutName,StockIn,StockInName,MainAmtPur FROM MiStockMoveMain WHERE BillNo='{0}'";
try
{
this._ado.Open();
var reader = this._ado.ExecuteReader(string.Format(sql, id));
try
{
if (reader.Read())
{
var cer = new Certificate();
var borrow = new CertificateItem();
var lend = new CertificateItem();
cer.Dbill_date = reader["DBill"] as DateTime?;
//
borrow.SubjectId = reader["StockOut"].ToString();
borrow.SubjectName = reader["StockOutName"].ToString();
borrow.Money = (decimal)reader["MainAmtPur"];
borrow.Summary = "库存商品-" + borrow.SubjectName;
//
lend.SubjectId = reader["StockIn"].ToString();
lend.SubjectName = reader["StockInName"].ToString();
lend.Money = (decimal)reader["MainAmtPur"];
lend.Summary = "库存商品-" + lend.SubjectName;
//
cer.SetItem(borrow, lend);
result = cer;
}
}
finally
{
reader.Close();
}
}
finally
{
this._ado.Close();
}
return result;
}
public int In(Certificate cer)
{
var ino_id = this.Ino_id();
StringBuilder sql = new StringBuilder();
//创建借凭证
sql.Append(string.Format(SQL, 1, cer.Dbill_date,
cer.BorrowItem.SubjectId, cer.BorrowItem.Money, 0,
cer.Iyear, cer.Iyperiod,
ino_id,
cer.Csign, cer.BorrowItem.Summary,
string.IsNullOrEmpty(cer.BorrowItem.Cdept_id) ? "NULL" : "'" + cer.BorrowItem.Cdept_id + "'",
string.IsNullOrEmpty(cer.BorrowItem.Csup_id) ? "NULL" : "'" + cer.BorrowItem.Csup_id + "'",
cer.Cbill,
cer.Dbill_date != null && cer.Dbill_date.HasValue ? cer.Dbill_date.Value.Month : 1));
//创建贷凭证
sql.Append(string.Format(SQL, 2, cer.Dbill_date,
cer.LendItem.SubjectId, 0, cer.LendItem.Money,
cer.Iyear, cer.Iyperiod,
ino_id,
cer.Csign, cer.LendItem.Summary,
string.IsNullOrEmpty(cer.LendItem.Cdept_id) ? "NULL" : "'" + cer.LendItem.Cdept_id + "'",
string.IsNullOrEmpty(cer.LendItem.Csup_id) ? "NULL" : "'" + cer.LendItem.Csup_id + "'",
cer.Cbill,
cer.Dbill_date != null && cer.Dbill_date.HasValue ? cer.Dbill_date.Value.Month : 1));
//入库
try
{
this._ado.Open();
this._ado.ExecuteNonQuery(sql.ToString());
return ino_id;
}
catch
{
return -1;
}
finally
{
this._ado.Close();
}
}
public Certificate.DomainModel.Certificate GetCertificate(string id)
{
Certificate.DomainModel.Certificate result = null;
try
{
this._ado.Open();
var reader = this._ado.ExecuteReader(string.Format(this._sql, id));
try
{
if (reader.Read())
{
var cer = new Certificate.DomainModel.Certificate();
var borrow = new Certificate.DomainModel.CertificateItem();
var lend = new Certificate.DomainModel.CertificateItem();
cer.Dbill_date = reader["created"] as DateTime?;
cer.Audited = reader["audited"] as DateTime?;
cer.Cbill = reader["createUser"] as string;
//
borrow.Summary = reader["borrowRemark"] as string;
borrow.SubjectId = reader["borrowSubject"] as string;
//borrow.SubjectName = reader["borrowSubjectName"] as string;
borrow.Money = reader["borrowMoney"] is decimal ? (decimal)reader["borrowMoney"] : 0;
try
{
borrow.Cdept_id = reader["borrowDept"] as string;
}
catch
{
}
try
{
borrow.Csup_id = reader["borrowSup"] as string;
}
catch
{
}
//
lend.Summary = reader["lendRemark"] as string;
lend.SubjectId = reader["lendSubject"] as string;
//lend.SubjectName = reader["lendSubjectName"] as string;
lend.Money = reader["lendMoney"] is decimal ? (decimal)reader["lendMoney"] : 0;
try
{
lend.Cdept_id = reader["lendDept"] as string;
}
catch
{
}
try
{
lend.Csup_id = reader["lendSup"] as string;
}
catch
{
}
//
cer.SetItem(borrow, lend);
//
result = cer;
}
}
catch (Exception e)
{
throw e;
}
finally
{
reader.Close();
}
}
catch (Exception e)
{
throw e;
}
finally
{
this._ado.Close();
}
return result;
}
