public bool GiveUpTempTrans(SaleBillDetails_Model detail, out string strErrMsg)
{
try
{
string sql = @"update salebillvouchs_extradefine set cbdefine5 = null,cbdefine6 = null where autoid = " + detail.autoid;
int i = OperationSql.ExecuteNonQueryForERP(CommandType.Text, sql, null);
if (i <= 0)
{
strErrMsg = "数据库操作失败,未能弃审记录";
return(false);
}
sql = @"update T_temptrans set verifydate = null where cinvcode = '" + detail.cinvcode + "' and ssbvcode = '" + detail.ssbvcode + "'";
i = OperationSql.ExecuteNonQuery(CommandType.Text, sql, null);
if (i <= 0)
{
strErrMsg = "数据库操作失败,未能弃审记录";
return(false);
}
strErrMsg = "";
return(true);
}
catch (Exception ex)
{
strErrMsg = ex.Message;
return(false);
}
}
public bool GetOldSaleBillVouch(SaleBillDetails_Model detail, out List <SaleBillDetails_Model> list, out string strErrMsg)
{
try
{
string strSql = @"select * from T_temptrans where ssbvcode = '" + detail.ssbvcode + "' and ssbvrowno = '" + detail.ssbvrowno + "'";
list = new List <SaleBillDetails_Model>();
using (SqlDataReader dr = OperationSql.ExecuteReader(System.Data.CommandType.Text, strSql))
{
while (dr.Read())
{
SaleBillDetails_Model SM = new SaleBillDetails_Model();
SM.cinvcode = dr["cinvcode"].ToDBString();
SM.cinvname = dr["cinvname"].ToDBString();
SM.cinvstd = dr["cinvstd"].ToDBString();
SM.ssorowno = dr["ssorowno"].ToDBString();
SM.ssbvrowno = dr["ssbvrowno"].ToDBString();
SM.ssoqty = dr["ssoqty"].ToDecimal();
SM.ssbvqty = dr["ssbvqty"].ToDecimal();
SM.ssocode = dr["ssocode"].ToDBString();
SM.ssbvcode = dr["ssbvcode"].ToDBString();
SM.creater = dr["creater"].ToString();
SM.createdate = dr["createdate"].ToString();
SM.verifydate = dr["verifydate"].ToString();
SM.qty = dr["qty"].ToDecimal();
SM.dsocode = dr["dsocode"].ToDBString();
SM.dsorowno = dr["dsorowno"].ToDBString();
SM.dsoqty = dr["dsoqty"].ToDecimal();
SM.RealQty = dr["RealQty"].ToDecimal();
//获取autoid
SM.autoid = OperationSql.ExecuteScalarForERP(System.Data.CommandType.Text, @"Select SaleBillVouchZW.autoid
From SaleBillVouchZW
left join (select cbdefine1 as cbdefine1,cbdefine5 as cbdefine5,cbdefine6 as cbdefine6,autoid as keyextend_b_autoid_salebillvouchs_extradefine_autoid
from salebillvouchs_extradefine) extend_b_autoid_salebillvouchs_extradefine on
keyextend_b_autoid_salebillvouchs_extradefine_autoid=salebillvouchzw.autoid
join SaleBillVouchZT on SaleBillVouchZT.sbvid=SaleBillVouchZW.sbvid
left join SO_SODetails on (SaleBillVouchZW.cSOCode = SO_SODetails.cSOCode and SaleBillVouchZW.iorderrowno = SO_SODetails.iRowNo)
where cSource=N'销售' and csbvcode = '" + SM.ssbvcode + "' and SaleBillVouchZW.cinvcode ='" + SM.cinvcode + "'").ToString();
list.Add(SM);
}
}
strErrMsg = "";
return(true);
}
catch (Exception ex)
{
strErrMsg = ex.Message;
list = null;
return(false);
}
}
public bool GetSaleBillVouchByCode(string code, out SaleBillVouch_Model model, out string strErrMsg)
{
try
{
string strSql = @"Select SaleBillVouchZW.cSOCode as ssocode,csbvcode as ssbvcode, SaleBillVouchZW.sbvid, SaleBillVouchZW.autoid,SaleBillVouchZW.SBVID,SO_SODetails.iquantity as ssoqty,SaleBillVouchZW.iquantity as ssbvqty,cWhCode,cWhName,SaleBillVouchZW.cInvCode,SaleBillVouchZW.cinvname,cinvstd,SaleBillVouchZW.irowno as ssbvrowno,SO_SODetails.iRowNo as ssorowno,cbdefine1
From SaleBillVouchZW
left join (select cbdefine1 as cbdefine1,cbdefine5 as cbdefine5,cbdefine6 as cbdefine6,autoid as keyextend_b_autoid_salebillvouchs_extradefine_autoid
from salebillvouchs_extradefine) extend_b_autoid_salebillvouchs_extradefine on
keyextend_b_autoid_salebillvouchs_extradefine_autoid=salebillvouchzw.autoid
join SaleBillVouchZT on SaleBillVouchZT.sbvid=SaleBillVouchZW.sbvid
left join SO_SODetails on (SaleBillVouchZW.cSOCode = SO_SODetails.cSOCode and SaleBillVouchZW.iorderrowno = SO_SODetails.iRowNo)
where cSource=N'销售' and csbvcode='" + code + "'";
model = new SaleBillVouch_Model();
model.csbvcode = code;
model.details = new List <SaleBillDetails_Model>();
using (SqlDataReader dr = OperationSql.ExecuteReaderForERP(System.Data.CommandType.Text, strSql))
{
while (dr.Read())
{
model.sbvid = dr["SBVID"].ToDBString();
SaleBillDetails_Model SM = new SaleBillDetails_Model();
SM.autoid = dr["autoid"].ToDBString();
SM.cinvcode = dr["cinvcode"].ToDBString();
SM.cinvname = dr["cinvname"].ToDBString();
SM.cinvstd = dr["cinvstd"].ToDBString();
SM.ssorowno = dr["ssorowno"].ToDBString();
SM.ssbvrowno = dr["ssbvrowno"].ToDBString();
SM.cWhCode = dr["cWhCode"].ToDBString();
SM.cWhName = dr["cWhName"].ToDBString();
SM.ssoqty = dr["ssoqty"].ToDecimal();
SM.ssbvqty = dr["ssbvqty"].ToDecimal();
SM.ssocode = dr["ssocode"].ToDBString();
SM.ssbvcode = dr["ssbvcode"].ToDBString();
model.details.Add(SM);
}
}
strErrMsg = "";
return(true);
}
catch (Exception ex)
{
strErrMsg = ex.Message;
model = null;
return(false);
}
}
public bool DelTempTrans(SaleBillDetails_Model detail, out string strErrMsg)
{
try
{
string sql = @"delete T_temptrans where cinvcode = '" + detail.cinvcode + "' and ssbvcode = '" + detail.ssbvcode + "' and ssbvrowno = '" + detail.ssbvrowno + "'";
int i = OperationSql.ExecuteNonQuery(CommandType.Text, sql, null);
if (i <= 0)
{
strErrMsg = "数据库操作失败,未能删除记录";
return(false);
}
strErrMsg = "";
return(true);
}
catch (Exception ex)
{
strErrMsg = ex.Message;
return(false);
}
}
//借调记录表
//id
//cinvcode物料编码
//cinvname物料名称
//cinvstd规格型号
//qty借调数量
//creater借调人员
//createdate借调生单时间
//verifydate借调审核时间
//ssocode原销售订单号
//ssorowno原销售订单行号
//ssoqty原销售订单数量
//ssbvcode原销售发票号
//ssbvrowno原销售发票行号
//ssbvqty原销售发票数量
//dsocode被借销售订单号
//dsorowno被借销售订单行号
//dsoqty被借销售订单数量
public bool SaveTempTrans(string creater, SaleBillDetails_Model detail, out string strErrMsg)
{
try
{
string sql = @"insert T_temptrans (cinvcode,cinvname,cinvstd,qty,creater,createdate,ssocode,ssorowno,ssoqty,ssbvcode,ssbvrowno,ssbvqty,dsocode,dsorowno,dsoqty)
values ('" + detail.cinvcode + "','" + detail.cinvname + "','" + detail.cinvstd + "'," + detail.qty.ToString() + ",'" + creater + "',GETDATE(),'" + detail.ssocode + "','" + detail.ssorowno + "',"
+ detail.ssoqty.ToString() + ",'" + detail.ssbvcode + "','" + detail.ssbvrowno + "'," + detail.ssbvqty.ToString() + ",'" + detail.dsocode + "','" + detail.dsorowno + "'," + detail.dsoqty.ToString() + ")";
int i = OperationSql.ExecuteNonQuery(CommandType.Text, sql, null);
if (i <= 0)
{
strErrMsg = "数据库操作失败,未能保存记录";
return(false);
}
strErrMsg = "";
return(true);
}
catch (Exception ex)
{
strErrMsg = ex.Message;
return(false);
}
}
public bool GiveUpTempTrans(SaleBillDetails_Model detail, out string strErrMsg)
{
return(_db.GiveUpTempTrans(detail, out strErrMsg));
}
public bool VerifyTempTrans(SaleBillDetails_Model detail, out string strErrMsg)
{
return(_db.VerifyTempTrans(detail, out strErrMsg));
}
public bool SaveTempTrans(string creater, SaleBillDetails_Model detail, out string strErrMsg)
{
return(_db.SaveTempTrans(creater, detail, out strErrMsg));
}
public bool GetSaleBillDetailsForTrans(SaleBillDetails_Model detail, out List <SaleBillDetails_Model> list, out string strErrMsg)
{
return(_db.GetSaleBillDetailsForTrans(detail, out list, out strErrMsg));
}
public bool GetOldSaleBillVouch(SaleBillDetails_Model detail, out List <SaleBillDetails_Model> list, out string strErrMsg)
{
return(_db.GetOldSaleBillVouch(detail, out list, out strErrMsg));
}
public bool QueryTempTrans(string cinvcode, string cinvstd, string ssocode, string ssbvcode, string dsocode, string dsbvcode, out List <SaleBillDetails_Model> list, out string strErrMsg)
{
try
{
//先判断查询条件中是否有被借销售发票号
string strSql;
if (dsbvcode != null && dsocode == null)
{
strSql = @"Select distinct SO_SODetails.cSOCode
From SaleBillVouchZW
left join (select cbdefine1 as cbdefine1,cbdefine5 as cbdefine5,cbdefine6 as cbdefine6,autoid as keyextend_b_autoid_salebillvouchs_extradefine_autoid
from salebillvouchs_extradefine) extend_b_autoid_salebillvouchs_extradefine on
keyextend_b_autoid_salebillvouchs_extradefine_autoid=salebillvouchzw.autoid
join SaleBillVouchZT on SaleBillVouchZT.sbvid=SaleBillVouchZW.sbvid
left join SO_SODetails on (SaleBillVouchZW.cSOCode = SO_SODetails.cSOCode and SaleBillVouchZW.iorderrowno = SO_SODetails.iRowNo)
where cSource=N'销售' and csbvcode = '" + dsbvcode + "'";
dsocode = OperationSql.ExecuteScalarForERP(System.Data.CommandType.Text, strSql).ToDBString();
}
strSql = @"select * from T_temptrans where (1=1) ";
if (cinvcode != null)
{
strSql += " and cinvcode = '" + cinvcode + "' ";
}
if (cinvstd != null)
{
strSql += " and cinvstd like '%" + cinvstd + "%' ";
}
if (ssocode != null)
{
strSql += " and ssocode = '" + ssocode + "' ";
}
if (ssbvcode != null)
{
strSql += " and ssbvcode = '" + ssbvcode + "' ";
}
if (dsocode != null)
{
strSql += " and dsocode = '" + dsocode + "' ";
}
list = new List <SaleBillDetails_Model>();
using (SqlDataReader dr = OperationSql.ExecuteReader(System.Data.CommandType.Text, strSql))
{
while (dr.Read())
{
SaleBillDetails_Model SM = new SaleBillDetails_Model();
SM.cinvcode = dr["cinvcode"].ToDBString();
SM.cinvname = dr["cinvname"].ToDBString();
SM.cinvstd = dr["cinvstd"].ToDBString();
SM.ssorowno = dr["ssorowno"].ToDBString();
SM.ssbvrowno = dr["ssbvrowno"].ToDBString();
SM.ssoqty = dr["ssoqty"].ToDecimal();
SM.ssbvqty = dr["ssbvqty"].ToDecimal();
SM.ssocode = dr["ssocode"].ToDBString();
SM.ssbvcode = dr["ssbvcode"].ToDBString();
SM.creater = dr["creater"].ToString();
SM.createdate = dr["createdate"].ToString();
SM.verifydate = dr["verifydate"].ToString();
SM.qty = dr["qty"].ToDecimal();
SM.dsocode = dr["dsocode"].ToDBString();
SM.dsorowno = dr["dsorowno"].ToDBString();
SM.dsoqty = dr["dsoqty"].ToDecimal();
SM.RealQty = dr["RealQty"].ToDecimal();
list.Add(SM);
}
}
strErrMsg = "";
return(true);
}
catch (Exception ex)
{
list = null;
strErrMsg = ex.Message;
return(false);
}
}
public bool GetSaleBillDetailsForTrans(SaleBillDetails_Model detail, out List <SaleBillDetails_Model> list, out string strErrMsg)
{
try
{
list = new List <SaleBillDetails_Model>();
string strSql = @"select cSOCode,iRowNo,iQuantity from SO_SODetails
where dbclosedate is null and cInvCode = '" + detail.cinvcode + "' and cSOCode != '" + detail.ssocode + "'";
using (SqlDataReader dr = OperationSql.ExecuteReaderForERP(System.Data.CommandType.Text, strSql))
{
while (dr.Read())
{
strSql = "select * from T_temptrans where RealQty < " + detail.qty + " and ssocode = '" + dr["cSOCode"].ToDBString() + "' and cinvcode = '" + detail.cinvcode + "'";
if (OperationSql.ExecuteReader(System.Data.CommandType.Text, strSql).Read())
{
break;
}
strSql = "select SUM(qty) from T_temptrans where cinvcode = '" + detail.cinvcode + "' and dsocode = '" + dr["cSOCode"].ToDBString() + "'";
decimal sum = OperationSql.ExecuteScalar(System.Data.CommandType.Text, strSql).ToDecimal();
if (sum != null && (dr["iQuantity"].ToDecimal() - sum < detail.qty))
{
continue;
}
strSql = "select SUM(T_STOCK.qty) from T_OUTBARCODE join T_STOCK on T_STOCK.serialno = T_OUTBARCODE.serialno where T_STOCK.warehouseno = '14' and T_OUTBARCODE.materialno = '" + detail.cinvcode + "' and SoCode = '" + dr["cSOCode"].ToDBString() + "'";
sum = OperationSql.ExecuteScalar(System.Data.CommandType.Text, strSql).ToDecimal();
if (sum == null)
{
continue;
}
else if (sum < detail.qty)
{
continue;
}
SaleBillDetails_Model SM = new SaleBillDetails_Model();
SM.autoid = detail.autoid;
SM.cinvcode = detail.cinvcode;
SM.cinvname = detail.cinvname;
SM.cinvstd = detail.cinvstd;
SM.ssorowno = detail.ssorowno;
SM.ssbvrowno = detail.ssbvrowno;
SM.cWhCode = detail.cWhCode;
SM.cWhName = detail.cWhName;
SM.ssoqty = detail.ssoqty;
SM.ssbvqty = detail.ssbvqty;
SM.ssocode = detail.ssocode;
SM.ssbvcode = detail.ssbvcode;
SM.dsocode = dr["cSOCode"].ToDBString();
SM.dsorowno = dr["iRowNo"].ToDBString();
SM.dsoqty = sum;
SM.qty = detail.qty;
//SM.dsoqty = dr["iQuantity"].ToDecimal();
list.Add(SM);
}
}
strErrMsg = "";
return(true);
}
catch (Exception ex)
{
strErrMsg = ex.Message;
list = null;
return(false);
}
}
