/// <summary>
/// 是否存在该记录
/// </summary>
public string Exists(OM.Model.Ven_Inv_Price model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from @u8.Ven_Inv_Price");
strSql.Append(" where cvencode = '" + model.cVenCode + "' and cInvCode = '" + model.cInvCode + "' and isupplytype = " + model.iSupplyType.ToString() + " and ilowerlimit = " + model.iLowerLimit.ToString());
return(strSql.ToString());
}
/// <summary>
/// 增加一条数据
/// </summary>
public string Add(OM.Model.Ven_Inv_Price model)
{
StringBuilder strSql = new StringBuilder();
StringBuilder strSql1 = new StringBuilder();
StringBuilder strSql2 = new StringBuilder();
if (model.cVenCode != null)
{
strSql1.Append("cVenCode,");
strSql2.Append("'" + model.cVenCode + "',");
}
if (model.cInvCode != null)
{
strSql1.Append("cInvCode,");
strSql2.Append("'" + model.cInvCode + "',");
}
if (model.dEnableDate != null)
{
strSql1.Append("dEnableDate,");
strSql2.Append("'" + model.dEnableDate + "',");
}
if (model.dDisableDate != null)
{
strSql1.Append("dDisableDate,");
strSql2.Append("'" + model.dDisableDate + "',");
}
if (model.cExch_Name != null)
{
strSql1.Append("cExch_Name,");
strSql2.Append("'" + model.cExch_Name + "',");
}
if (model.bPromotion != null)
{
strSql1.Append("bPromotion,");
strSql2.Append("" + model.bPromotion + ",");
}
if (model.cMemo != null)
{
strSql1.Append("cMemo,");
strSql2.Append("'" + model.cMemo + "',");
}
if (model.iSupplyType != null)
{
strSql1.Append("iSupplyType,");
strSql2.Append("" + model.iSupplyType + ",");
}
if (model.btaxcost != null)
{
strSql1.Append("btaxcost,");
strSql2.Append("" + model.btaxcost + ",");
}
if (model.cTermCode != null)
{
strSql1.Append("cTermCode,");
strSql2.Append("'" + model.cTermCode + "',");
}
if (model.iLowerLimit != null)
{
strSql1.Append("iLowerLimit,");
strSql2.Append("" + model.iLowerLimit + ",");
}
if (model.iUpperLimit != null)
{
strSql1.Append("iUpperLimit,");
strSql2.Append("" + model.iUpperLimit + ",");
}
if (model.iUnitPrice != null)
{
strSql1.Append("iUnitPrice,");
strSql2.Append("" + model.iUnitPrice + ",");
}
if (model.iTaxRate != null)
{
strSql1.Append("iTaxRate,");
strSql2.Append("" + model.iTaxRate + ",");
}
if (model.iTaxUnitPrice != null)
{
strSql1.Append("iTaxUnitPrice,");
strSql2.Append("" + model.iTaxUnitPrice + ",");
}
if (model.ipriceautoid != null)
{
strSql1.Append("ipriceautoid,");
strSql2.Append("" + model.ipriceautoid + ",");
}
if (model.cfree1 != null)
{
strSql1.Append("cfree1,");
strSql2.Append("'" + model.cfree1 + "',");
}
if (model.cfree2 != null)
{
strSql1.Append("cfree2,");
strSql2.Append("'" + model.cfree2 + "',");
}
if (model.cfree3 != null)
{
strSql1.Append("cfree3,");
strSql2.Append("'" + model.cfree3 + "',");
}
if (model.cfree4 != null)
{
strSql1.Append("cfree4,");
strSql2.Append("'" + model.cfree4 + "',");
}
if (model.cfree5 != null)
{
strSql1.Append("cfree5,");
strSql2.Append("'" + model.cfree5 + "',");
}
if (model.cfree6 != null)
{
strSql1.Append("cfree6,");
strSql2.Append("'" + model.cfree6 + "',");
}
if (model.cfree7 != null)
{
strSql1.Append("cfree7,");
strSql2.Append("'" + model.cfree7 + "',");
}
if (model.cfree8 != null)
{
strSql1.Append("cfree8,");
strSql2.Append("'" + model.cfree8 + "',");
}
if (model.cfree9 != null)
{
strSql1.Append("cfree9,");
strSql2.Append("'" + model.cfree9 + "',");
}
if (model.cfree10 != null)
{
strSql1.Append("cfree10,");
strSql2.Append("'" + model.cfree10 + "',");
}
if (model.cSource != null)
{
strSql1.Append("cSource,");
strSql2.Append("'" + model.cSource + "',");
}
if (model.cSourceCode != null)
{
strSql1.Append("cSourceCode,");
strSql2.Append("'" + model.cSourceCode + "',");
}
if (model.cSourceAutoid != null)
{
strSql1.Append("cSourceAutoid,");
strSql2.Append("'" + model.cSourceAutoid + "',");
}
strSql.Append("insert into @u8.Ven_Inv_Price(");
strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
strSql.Append(")");
strSql.Append(" values (");
strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
strSql.Append(")");
strSql.Append(";select @@IDENTITY");
return(strSql.ToString());
}
/// <summary>
/// 更新一条数据
/// </summary>
public string Update(OM.Model.Ven_Inv_Price model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update @u8.Ven_Inv_Price set ");
if (model.cVenCode != null)
{
strSql.Append("cVenCode='" + model.cVenCode + "',");
}
if (model.cInvCode != null)
{
strSql.Append("cInvCode='" + model.cInvCode + "',");
}
if (model.dEnableDate != null)
{
strSql.Append("dEnableDate='" + model.dEnableDate + "',");
}
if (model.dDisableDate != null)
{
strSql.Append("dDisableDate='" + model.dDisableDate + "',");
}
else
{
strSql.Append("dDisableDate= null ,");
}
if (model.cExch_Name != null)
{
strSql.Append("cExch_Name='" + model.cExch_Name + "',");
}
else
{
strSql.Append("cExch_Name= null ,");
}
if (model.bPromotion != null)
{
strSql.Append("bPromotion=" + model.bPromotion + ",");
}
if (model.cMemo != null)
{
strSql.Append("cMemo='" + model.cMemo + "',");
}
else
{
strSql.Append("cMemo= null ,");
}
if (model.iSupplyType != null)
{
strSql.Append("iSupplyType=" + model.iSupplyType + ",");
}
if (model.btaxcost != null)
{
strSql.Append("btaxcost=" + model.btaxcost + ",");
}
if (model.cTermCode != null)
{
strSql.Append("cTermCode='" + model.cTermCode + "',");
}
else
{
strSql.Append("cTermCode= null ,");
}
if (model.iLowerLimit != null)
{
strSql.Append("iLowerLimit=" + model.iLowerLimit + ",");
}
if (model.iUpperLimit != null)
{
strSql.Append("iUpperLimit=" + model.iUpperLimit + ",");
}
else
{
strSql.Append("iUpperLimit= null ,");
}
if (model.iUnitPrice != null)
{
strSql.Append("iUnitPrice=" + model.iUnitPrice + ",");
}
if (model.iTaxRate != null)
{
strSql.Append("iTaxRate=" + model.iTaxRate + ",");
}
if (model.iTaxUnitPrice != null)
{
strSql.Append("iTaxUnitPrice=" + model.iTaxUnitPrice + ",");
}
if (model.ipriceautoid != null)
{
strSql.Append("ipriceautoid=" + model.ipriceautoid + ",");
}
else
{
strSql.Append("ipriceautoid= null ,");
}
if (model.cfree1 != null)
{
strSql.Append("cfree1='" + model.cfree1 + "',");
}
else
{
strSql.Append("cfree1= null ,");
}
if (model.cfree2 != null)
{
strSql.Append("cfree2='" + model.cfree2 + "',");
}
else
{
strSql.Append("cfree2= null ,");
}
if (model.cfree3 != null)
{
strSql.Append("cfree3='" + model.cfree3 + "',");
}
else
{
strSql.Append("cfree3= null ,");
}
if (model.cfree4 != null)
{
strSql.Append("cfree4='" + model.cfree4 + "',");
}
else
{
strSql.Append("cfree4= null ,");
}
if (model.cfree5 != null)
{
strSql.Append("cfree5='" + model.cfree5 + "',");
}
else
{
strSql.Append("cfree5= null ,");
}
if (model.cfree6 != null)
{
strSql.Append("cfree6='" + model.cfree6 + "',");
}
else
{
strSql.Append("cfree6= null ,");
}
if (model.cfree7 != null)
{
strSql.Append("cfree7='" + model.cfree7 + "',");
}
else
{
strSql.Append("cfree7= null ,");
}
if (model.cfree8 != null)
{
strSql.Append("cfree8='" + model.cfree8 + "',");
}
else
{
strSql.Append("cfree8= null ,");
}
if (model.cfree9 != null)
{
strSql.Append("cfree9='" + model.cfree9 + "',");
}
else
{
strSql.Append("cfree9= null ,");
}
if (model.cfree10 != null)
{
strSql.Append("cfree10='" + model.cfree10 + "',");
}
else
{
strSql.Append("cfree10= null ,");
}
if (model.cSource != null)
{
strSql.Append("cSource='" + model.cSource + "',");
}
else
{
strSql.Append("cSource= null ,");
}
if (model.cSourceCode != null)
{
strSql.Append("cSourceCode='" + model.cSourceCode + "',");
}
else
{
strSql.Append("cSourceCode= null ,");
}
if (model.cSourceAutoid != null)
{
strSql.Append("cSourceAutoid='" + model.cSourceAutoid + "',");
}
else
{
strSql.Append("cSourceAutoid= null ,");
}
int n = strSql.ToString().LastIndexOf(",");
strSql.Remove(n, 1);
strSql.Append(" where Autoid=" + model.Autoid + "");
return(strSql.ToString());
}
private void btnSave()
{
try
{
try
{
gridView1.FocusedRowHandle -= 1;
gridView1.FocusedRowHandle += 1;
}
catch { }
string sErr = "";
string sSQL = "";
for (int i = 0; i < gridView1.RowCount; i++)
{
string sInvName = gridView1.GetRowCellDisplayText(i, gridColcInvName).ToString().Trim();
if (sInvName == "")
{
sErr = sErr + "行" + (i + 1).ToString() + " 存货不正确\n";
}
string sVenName = gridView1.GetRowCellDisplayText(i, gridColcVenName).ToString().Trim();
if (sVenName == "")
{
sErr = sErr + "行" + (i + 1).ToString() + " 供应商不正确\n";
}
decimal d含税单价 = ReturnObjectToDecimal(gridView1.GetRowCellDisplayText(i, gridCol含税单价), 6);
if (d含税单价 <= 0)
{
sErr = sErr + "行" + (i + 1).ToString() + " 含税单价不正确\n";
}
if (gridView1.GetRowCellDisplayText(i, gridCol税率).ToString().Trim() == "")
{
sErr = sErr + "行" + (i + 1).ToString() + " 税率不正确\n";
}
decimal d税率 = ReturnObjectToDecimal(gridView1.GetRowCellDisplayText(i, gridCol税率), 6);
if (d税率 < 0 || d税率 > 100)
{
sErr = sErr + "行" + (i + 1).ToString() + " 税率不正确\n";
}
sSQL = "select isnull(bProxyForeign,0) as bProxyForeign from @u8.Inventory where cInvCode = 'aaaaaaaaaa'";
sSQL = sSQL.Replace("aaaaaaaaaa", gridView1.GetRowCellDisplayText(i, gridColcinvcode).ToString().Trim());
int iReturn = BaseFunction.ReturnInt(clsSQLCommond.ExecGetScalar(sSQL));
if (iReturn == 0)
{
sErr = sErr + "行" + (i + 1).ToString() + " 存货【" + gridView1.GetRowCellDisplayText(i, gridColcinvcode).ToString().Trim() + "】" + sInvName + " 不是委外件\n";
}
}
if (sErr.Length > 0)
{
throw new Exception(sErr);
}
ArrayList aList = new ArrayList();
sSQL = @"
declare @p5 int
set @p5=1000003974
declare @p6 int
set @p6=1000042663
exec @u8.sp_getID N'00',N'200',N'puprice',1,@p5 output,@p6 output
select @p5, @p6
";
DataTable dt = clsSQLCommond.ExecQuery(sSQL);
long lID = ReturnObjectToLong(dt.Rows[0][0]);
long lIDs = ReturnObjectToLong(dt.Rows[0][1]);
sSQL = @"
select cNumber as Maxnumber From @u8.VoucherHistory with (NOLOCK) Where CardNumber='PU03' and cContent is NULL
";
dt = clsSQLCommond.ExecQuery(sSQL);
long lCode = ReturnObjectToLong(dt.Rows[0][0]) + 1;
string sCode = lCode.ToString();
while (sCode.Length < 10)
{
sCode = "0" + sCode;
}
Model.PU_PriceJustMain mod = new OM.Model.PU_PriceJustMain();
mod.id = lID;
mod.ddate = BaseFunction.ReturnDate(FrameBaseFunction.ClsBaseDataInfo.sLogDate);
mod.ccode = sCode;
mod.cmaker = FrameBaseFunction.ClsBaseDataInfo.sUserName;
mod.ivtid = 31338;
mod.bTaxCost = 1;
mod.iSupplyType = 2;
mod.cMakeTime = DateTime.Now;
mod.iPrintCount = 0;
//mod.iverifystate = 0;
mod.cverifier = FrameBaseFunction.ClsBaseDataInfo.sUserName;
mod.dverifydate = BaseFunction.ReturnDate(FrameBaseFunction.ClsBaseDataInfo.sLogDate);
mod.iverifystate = 2;
mod.cAuditTime = mod.cMakeTime;
mod.cmainmemo = gridView1.GetRowCellDisplayText(0, gridCol表头备注).ToString().Trim();
mod.csysbarcode = @"||putj|" + mod.ccode;
DAL.PU_PriceJustMain dal = new OM.DAL.PU_PriceJustMain();
sSQL = dal.Add(mod);
aList.Add(sSQL);
int iRow = 0;
bool bHas = false;
for (int i = 0; i < gridView1.RowCount; i++)
{
if (!ReturnObjectToBool(gridView1.GetRowCellValue(i, gridCol选择)))
{
continue;
}
iRow += 1;
lIDs += 1;
Model.PU_PriceJustDetail mods = new OM.Model.PU_PriceJustDetail();
mods.autoid = lIDs;
mods.id = lID;
mods.operationtype = "0";
mods.cvencode = gridView1.GetRowCellDisplayText(i, gridColcVenCode).ToString().Trim();
mods.cinvcode = gridView1.GetRowCellDisplayText(i, gridColcinvcode).ToString().Trim();
mods.bsales = 0;
mods.fminquantity = 0;
decimal dTaxRate = ReturnObjectToDecimal(gridView1.GetRowCellDisplayText(i, gridCol税率), 6);
if (dTaxRate == 0)
{
dTaxRate = 16;
}
mods.iTaxRate = dTaxRate;
mods.iTaxUnitPrice = ReturnObjectToDecimal(gridView1.GetRowCellDisplayText(i, gridCol含税单价), 6);
mods.iUnitPrice = ReturnObjectToDecimal(mods.iTaxUnitPrice / (1 + mods.iTaxRate / 100), 6);
mods.cexch_name = "人民币";
mods.ivouchrowno = iRow;
mods.cbodymemo = gridView1.GetRowCellDisplayText(i, gridCol表体备注).ToString().Trim();
mods.cbsysbarcode = @"||putj|" + mod.ccode + @"|" + mods.ivouchrowno.ToString();
mods.dstartdate = BaseFunction.ReturnDate(DateTime.Today.ToString("yyyy-MM-dd"));
DAL.PU_PriceJustDetail dals = new OM.DAL.PU_PriceJustDetail();
sSQL = dals.Add(mods);
aList.Add(sSQL);
Model.Ven_Inv_Price modInv = new OM.Model.Ven_Inv_Price();
modInv.cVenCode = mods.cvencode;
modInv.cInvCode = mods.cinvcode;
modInv.dEnableDate = ReturnObjectToDatetime(mods.dstartdate);
modInv.cExch_Name = mods.cexch_name;
modInv.bPromotion = 0;
modInv.iSupplyType = ReturnObjectToInt(mod.iSupplyType);
modInv.btaxcost = 1;
modInv.iLowerLimit = 0;
modInv.iUnitPrice = Convert.ToDecimal(mods.iUnitPrice);
modInv.iTaxRate = Convert.ToDecimal(mods.iTaxRate);
modInv.iTaxUnitPrice = Convert.ToDecimal(mods.iTaxUnitPrice);
sSQL = "select max(ipriceautoid) from @u8.ven_inv_price";
long l = ReturnObjectToLong(clsSQLCommond.ExecGetScalar(sSQL));
DAL.Ven_Inv_Price dalInv = new OM.DAL.Ven_Inv_Price();
sSQL = dalInv.Exists(modInv);
DataTable dtInvPrice = clsSQLCommond.ExecQuery(sSQL);
if (ReturnObjectToInt(dtInvPrice.Rows.Count) == 0)
{
l += 1;
modInv.ipriceautoid = l;
sSQL = dalInv.Add(modInv);
aList.Add(sSQL);
}
else
{
modInv.Autoid = ReturnObjectToInt(dtInvPrice.Rows[0]["autoid"]);
sSQL = dalInv.Update(modInv);
aList.Add(sSQL);
}
bHas = true;
}
if (!bHas)
{
throw new Exception("没有需要保存的行");
}
sSQL = @"
update @u8.VoucherHistory set cNumber='aaaaaa' Where CardNumber='PU03' and cContent is NULL
";
sSQL = sSQL.Replace("aaaaaa", lCode.ToString());
aList.Add(sSQL);
if (lID > 1000000000)
{
lID = lID - 1000000000;
}
if (lIDs > 1000000000)
{
lIDs = lIDs - 1000000000;
}
sSQL = "update UFSystem..UA_Identity set iFatherId = " + lID.ToString() + ",iChildId = " + lIDs + " where cAcc_Id = '" + FrameBaseFunction.ClsBaseDataInfo.sUFDataBaseName.Substring(7, 3) + "' and cVouchType = 'puprice'";
aList.Add(sSQL);
if (aList.Count > 0)
{
clsSQLCommond.ExecSqlTran(aList);
MessageBox.Show("保存成功！\n" + mod.ccode);
((DataTable)gridControl1.DataSource).Rows.Clear();
}
}
catch (Exception ee)
{
throw new Exception(ee.Message);
}
}