private void btnImprot_Click(object sender, EventArgs e)
{
try
{
gridViewPL.FocusedRowHandle -= 1;
gridViewPL.FocusedRowHandle += 1;
}
catch { }
try
{
if (txtCurrency.Text.Trim() == "")
{
throw new Exception("Please set currency");
}
decimal dExch = BaseFunction.ReturnDecimal(txtExchangeRate.Text.Trim());
if (dExch == 0)
{
throw new Exception("Please set the exchange rate");
}
int iCou = gridViewPL.RowCount;
if (iCou == 0)
{
throw new Exception("no data");
}
int iCodeCou = gridViewPL.RowCount;
string sErr = "";
string sWarn = "";
int iCount = 0;
SqlConnection conn = new SqlConnection(Conn);
conn.Open();
//启用事务
SqlTransaction tran = conn.BeginTransaction();
try
{
DateTime dLogDate = BaseFunction.ReturnDate(sLogDate);
//string sDate = dateEdit1.DateTime.ToString("yyyy-MM-dd");
string sDate = sLogDate;
string sSQL = @"
select bflag_PU from gl_mend where iyear = aaaaaa and iperiod = bbbbbb
";
sSQL = sSQL.Replace("aaaaaa", dLogDate.Year.ToString());
sSQL = sSQL.Replace("bbbbbb", dLogDate.Month.ToString());
DataTable dtTemp = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (BaseFunction.ReturnBool(dtTemp.Rows[0]["bflag_PU"]))
{
throw new Exception("The current date has been checked out");
}
string sDateTime = dateEditQuery.DateTime.ToString("yyyy-MM-dd");
long lID = -1;
long lIDDetails = -1;
sSQL = @"
declare @p5 int
set @p5=aaaaaa
declare @p6 int
set @p6=bbbbbb
exec sp_GetId N'00',N'dddddd',N'tr',cccccc,@p5 output,@p6 output,default
select @p5, @p6
";
sSQL = sSQL.Replace("aaaaaa", lID.ToString());
sSQL = sSQL.Replace("bbbbbb", lIDDetails.ToString());
sSQL = sSQL.Replace("cccccc", iCou.ToString());
sSQL = sSQL.Replace("dddddd", sAccID);
DataTable dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
lID = BaseFunction.ReturnLong(dt.Rows[0][0]) - 1;
lIDDetails = BaseFunction.ReturnLong(dt.Rows[0][1]) - iCodeCou;
sSQL = @"
select a.EntryNO,b.*
from RdRecords01_Import a
inner join (
select a.cCode,a.cVouchType ,a.cDepCode,a.dDate,a.cBusType ,a.cPTCode ,a.cExch_Name,a.cVenCode ,a.cRdCode ,a.cWhCode ,a.bRdFlag ,a.cPersonCode ,a.cSource,a.iExchRate
,b.AutoID,b.cInvCode,b.iQuantity,b.irowno,b.iOriCost,b.iOriMoney,b.iOriTaxPrice,b.iOriSum,b.iMoney,b.iTaxPrice,b.iSum,b.iTaxRate,b.iPOsID ,b.iOriTaxCost
,a.cDefine1, a.cDefine2, a.cDefine3, a.cDefine4, a.cDefine5, a.cDefine6, a.cDefine7, a.cDefine8, a.cDefine9, a.cDefine10, a.cDefine11, a.cDefine12, a.cDefine13, a.cDefine14, a.cDefine15, a.cDefine16
,b.cDefine22, cDefine23, b.cDefine24, b.cDefine25, b.cDefine26, b.cDefine27, b.cDefine28, b.cDefine29, b.cDefine30, b.cDefine31, cDefine32, b.cDefine33, b.cDefine34, b.cDefine35, b.cDefine36, b.cDefine37
from RdRecord01 a inner join Rdrecords01 b on a.id = b.id
)b on a.Rds01ID = b.Autoid
where a.InvoiceNo = 'aaaaaa'
";
sSQL = sSQL.Replace("aaaaaa", txtInvoiceNO.Text.Trim());
DataTable dtRd01 = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtRd01 == null || dtRd01.Rows.Count == 0)
{
throw new Exception("Purchase receipt warrant is not exists");
}
Model.PurBillVouch mod = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.PurBillVouch();
mod.PBVID = lID.ToString();
mod.cPBVBillType = "01";
mod.cPBVCode = txtInvoiceNO.Text.Trim();
mod.cPTCode = dtRd01.Rows[0]["cPTCode"].ToString().Trim();
mod.dPBVDate = dLogDate;
mod.cVenCode = dtRd01.Rows[0]["cVenCode"].ToString().Trim();
mod.cUnitCode = dtRd01.Rows[0]["cVenCode"].ToString().Trim(); //代垫单位代码
mod.cDepCode = dtRd01.Rows[0]["cDepCode"].ToString().Trim();
mod.cexch_name = txtCurrency.Text.Trim();
mod.cExchRate = dExch;
mod.iPBVTaxRate = BaseFunction.ReturnDecimal(dtRd01.Rows[0]["iTaxRate"]);
mod.cInCode = dtRd01.Rows[0]["cCode"].ToString().Trim();
mod.cBusType = dtRd01.Rows[0]["cBusType"].ToString().Trim();
mod.cPBVMaker = sUserName;
mod.bNegative = false;
mod.bOriginal = false;
mod.bFirst = false;
mod.iNetLock = 0;
mod.dVouDate = dLogDate;
mod.iVTid = 8163;
mod.cSource = "采购";
mod.iDiscountTaxType = 0;
//mod.cVenPUOMProtocol
mod.dCreditStart = dLogDate;
mod.iCreditPeriod = 0;
mod.dGatheringDate = dLogDate;
mod.bCredit = 1;
mod.iPrintCount = 0;
mod.IsWfControlled = false;
mod.csysbarcode = "||puzl|" + mod.cPBVCode;
mod.cmaketime = DateTime.Now;
mod.cCurrentAuditor = null;
mod.cDefine1 = dtRd01.Rows[0]["cDefine1"].ToString().Trim();
mod.cDefine10 = dtRd01.Rows[0]["cDefine10"].ToString().Trim();
//mod.cDefine11 = txtBL.Text.Trim();
DAL.PurBillVouch dal = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.PurBillVouch();
sSQL = dal.Add(mod);
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//回写采购入库单表头自定义项10 为发票号
sSQL = @"
Update Rdrecord01 set cDefine10 = 'aaaaaaaa' where ID in (select ID from Rdrecords01 where Autoid = bbbbbbbb)
";
sSQL = sSQL.Replace("aaaaaaaa", mod.cPBVCode);
sSQL = sSQL.Replace("bbbbbbbb", gridViewPL.GetRowCellValue(0, gridColRds01ID).ToString());
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
int iRow = 0;
for (int i = 0; i < gridViewPL.RowCount; i++)
{
//判断是否生成了调拨单
string sTrIDs = gridViewPL.GetRowCellValue(i, gridColU8TrsID).ToString().Trim();
if (sTrIDs != "")
{
sSQL = @"
select autoid from TransVouchs where autoid = aaaaaaaa
";
sSQL = sSQL.Replace("aaaaaaaa", sTrIDs);
DataTable dtTr = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtTr == null || dtTr.Rows.Count == 0)
{
sWarn = sWarn + "Line " + (i + 1).ToString() + " no transfer order\n";
}
}
else
{
sWarn = sWarn + "Line " + (i + 1).ToString() + " no transfer order\n";
}
//判断是否生成了发票
string sPurIDs = gridViewPL.GetRowCellValue(i, gridColPurBillIDs).ToString().Trim();
if (sPurIDs != "")
{
sSQL = @"
select id from PurBillVouchs where ID = aaaaaaaa
";
sSQL = sSQL.Replace("aaaaaaaa", sPurIDs);
DataTable dtPur = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtPur != null && dtPur.Rows.Count > 0)
{
sErr = sErr + "Line " + (i + 1).ToString() + " imported invoice\n";
continue;
}
}
DataRow[] drs;
string sRds01ID = gridViewPL.GetRowCellValue(i, gridColRds01ID).ToString().Trim();
if (sRds01ID == "")
{
sErr = sErr + "Line " + (i + 1).ToString() + " purchase receipt warrant is not exists\n";
continue;
}
else
{
drs = dtRd01.Select("Autoid = " + sRds01ID);
if (drs.Length == 0)
{
sErr = sErr + "Line " + (i + 1).ToString() + " purchase receipt warrant is not exists\n";
continue;
}
}
Model.PurBillVouchs mods = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.PurBillVouchs();
sSQL = @"
Select b.iQuantity - isnull(b.iSumBillQuantity,0) as QtyUnRDIn
,b.*
from Rdrecord01 a inner join Rdrecords01 b on a.ID = b.ID
where AutoID = bbbbbb
";
sSQL = sSQL.Replace("bbbbbb", gridViewPL.GetRowCellValue(i, gridColRds01ID).ToString());
DataTable dtRds01 = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtRds01 == null || dtRds01.Rows.Count == 0)
{
sErr = sErr + "Line " + (i + 1).ToString() + " purchase entry form is not imported\n";
continue;
}
decimal dQtyUnRDIn = BaseFunction.ReturnDecimal(dtRds01.Rows[0]["QtyUnRDIn"]);
lIDDetails += 1;
mods.ID = lIDDetails;
mods.PBVID = mod.PBVID;
mods.cInvCode = gridViewPL.GetRowCellValue(i, gridColPARTNO).ToString().Trim();
mods.bExBill = false;
mods.iPBVQuantity = BaseFunction.ReturnDecimal(gridViewPL.GetRowCellValue(i, gridColQUANTITY));
if (mods.iPBVQuantity > dQtyUnRDIn)
{
sErr = sErr + "Row " + (i + 1).ToString() + " qty err\n";
continue;
}
mods.iTaxRate = BaseFunction.ReturnDecimal(drs[0]["iTaxRate"]);
mods.iOriCost = BaseFunction.ReturnDecimal(drs[0]["iOriCost"]);
mods.iOriMoney = BaseFunction.ReturnDecimal(drs[0]["iOriMoney"]); //原币无税金额
mods.iOriSum = BaseFunction.ReturnDecimal(drs[0]["iOriSum"]);
mods.iOriTaxCost = BaseFunction.ReturnDecimal(drs[0]["iOriTaxCost"]);
mods.iOriTaxPrice = mods.iOriSum - mods.iOriMoney;
mods.iCost = BaseFunction.ReturnDecimal(mods.iOriCost * dExch);
mods.iMoney = BaseFunction.ReturnDecimal(mods.iOriMoney * dExch);
mods.iSum = BaseFunction.ReturnDecimal(mods.iOriSum * dExch);
mods.iOriTotal = 0;
mods.iTotal = 0;
mods.iTaxRate = BaseFunction.ReturnDecimal(drs[0]["iTaxRate"]);
mods.iTaxPrice = mods.iSum - mods.iMoney;
mods.cDefine22 = gridViewPL.GetRowCellValue(i, gridColCONTAINERNO).ToString().Trim();
mods.cDefine23 = gridViewPL.GetRowCellValue(i, gridColBOXNO).ToString().Trim();
mods.cDefine24 = gridViewPL.GetRowCellValue(i, gridColCASENO).ToString().Trim();
mods.cDefine25 = gridViewPL.GetRowCellValue(i, gridColGWKGS).ToString().Trim();
mods.cDefine28 = gridViewPL.GetRowCellValue(i, gridColNWKGS).ToString().Trim();
mods.iPOsID = BaseFunction.ReturnLong(drs[0]["iPOsID"]);
mods.RdsId = BaseFunction.ReturnLong(drs[0]["AutoID"]);
mods.UpSoType = "rd";
mods.bCosting = false;
mods.bTaxCost = true;
mods.iinvexchrate = 0;
mods.brettax = 0;
mods.ivouchrowno = i + 1;
mods.cbsysbarcode = "||puzl|" + mod.cPBVCode + "|" + mods.ivouchrowno.ToString();
DAL.PurBillVouchs dals = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.PurBillVouchs();
sSQL = dals.Add(mods);
iCount += DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//回写导入中间表 Rdrecords10_Import
sSQL = @"
update RdRecords01_Import set PurBillIDs = AAAAAA
WHERE AUTOID = BBBBBB
";
sSQL = sSQL.Replace("AAAAAA", mods.ID.ToString());
sSQL = sSQL.Replace("BBBBBB", gridViewPL.GetRowCellValue(i, gridColAutoID).ToString());
int iRet = DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
if (iRet != 1)
{
sErr = sErr + "Row " + (i + 1) + " update dl err\n";
}
//回写采购入库单
sSQL = @"
update Rdrecords01 set iSumBillQuantity = isnull(iSumBillQuantity,0) + aaaaaa
where AutoID = bbbbbb
";
sSQL = sSQL.Replace("aaaaaa", gridViewPL.GetRowCellValue(i, gridColQUANTITY).ToString());
sSQL = sSQL.Replace("bbbbbb", gridViewPL.GetRowCellValue(i, gridColRds01ID).ToString());
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//回写采购订单
sSQL = @"
update PO_Podetails set iInvQTY = isnull(iInvQTY ,0) + aaaaaa,iInvMoney = isnull(iInvMoney ,0) + cccccc,iNatInvMoney = isnull(iNatInvMoney ,0) + dddddd
where ID = bbbbbb
";
sSQL = sSQL.Replace("aaaaaa", mods.iPBVQuantity.ToString());
sSQL = sSQL.Replace("bbbbbb", mods.iPOsID.ToString());
sSQL = sSQL.Replace("cccccc", mods.iOriMoney.ToString());
sSQL = sSQL.Replace("dddddd", mods.iMoney.ToString());
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
if (sErr != "")
{
throw new Exception(sErr);
}
if (sWarn != "")
{
if (MessageBox.Show(sWarn, "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) != DialogResult.Yes)
{
throw new Exception("User canceled ");
}
}
if (iCount > 0)
{
tran.Commit();
MessageBox.Show("Success：" + mod.cPBVCode);
SetTxtNull();
}
else
{
throw new Exception("Save failed");
}
}
catch (Exception error)
{
tran.Rollback();
throw new Exception(error.Message);
}
}
catch (Exception ee)
{
FrmMsgBox frm = new FrmMsgBox();
frm.richTextBox1.Text = ee.Message;
frm.ShowDialog();
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public string Add(UFIDA.U8.UAP.CustomApp.ControlForm.Model.PurBillVouchs model)
{
StringBuilder strSql = new StringBuilder();
StringBuilder strSql1 = new StringBuilder();
StringBuilder strSql2 = new StringBuilder();
if (model.ID != null)
{
strSql1.Append("ID,");
strSql2.Append("" + model.ID + ",");
}
if (model.PBVID != null)
{
strSql1.Append("PBVID,");
strSql2.Append("'" + model.PBVID + "',");
}
if (model.cInvCode != null)
{
strSql1.Append("cInvCode,");
strSql2.Append("'" + model.cInvCode + "',");
}
if (model.bExBill != null)
{
strSql1.Append("bExBill,");
strSql2.Append("" + (model.bExBill ? 1 : 0) + ",");
}
if (model.dInDate != null)
{
strSql1.Append("dInDate,");
strSql2.Append("'" + model.dInDate + "',");
}
if (model.iPBVQuantity != null)
{
strSql1.Append("iPBVQuantity,");
strSql2.Append("" + model.iPBVQuantity + ",");
}
if (model.iNum != null)
{
strSql1.Append("iNum,");
strSql2.Append("" + model.iNum + ",");
}
if (model.iOriCost != null)
{
strSql1.Append("iOriCost,");
strSql2.Append("" + model.iOriCost + ",");
}
if (model.iOriMoney != null)
{
strSql1.Append("iOriMoney,");
strSql2.Append("" + model.iOriMoney + ",");
}
if (model.iOriTaxPrice != null)
{
strSql1.Append("iOriTaxPrice,");
strSql2.Append("" + model.iOriTaxPrice + ",");
}
if (model.iOriSum != null)
{
strSql1.Append("iOriSum,");
strSql2.Append("" + model.iOriSum + ",");
}
if (model.iCost != null)
{
strSql1.Append("iCost,");
strSql2.Append("" + model.iCost + ",");
}
if (model.iMoney != null)
{
strSql1.Append("iMoney,");
strSql2.Append("" + model.iMoney + ",");
}
if (model.iTaxPrice != null)
{
strSql1.Append("iTaxPrice,");
strSql2.Append("" + model.iTaxPrice + ",");
}
if (model.iSum != null)
{
strSql1.Append("iSum,");
strSql2.Append("" + model.iSum + ",");
}
if (model.iExMoney != null)
{
strSql1.Append("iExMoney,");
strSql2.Append("" + model.iExMoney + ",");
}
if (model.iLostQuan != null)
{
strSql1.Append("iLostQuan,");
strSql2.Append("" + model.iLostQuan + ",");
}
if (model.iNLostQuan != null)
{
strSql1.Append("iNLostQuan,");
strSql2.Append("" + model.iNLostQuan + ",");
}
if (model.iNLostMoney != null)
{
strSql1.Append("iNLostMoney,");
strSql2.Append("" + model.iNLostMoney + ",");
}
if (model.iOriTotal != null)
{
strSql1.Append("iOriTotal,");
strSql2.Append("" + model.iOriTotal + ",");
}
if (model.iTotal != null)
{
strSql1.Append("iTotal,");
strSql2.Append("" + model.iTotal + ",");
}
if (model.cDebitHead != null)
{
strSql1.Append("cDebitHead,");
strSql2.Append("'" + model.cDebitHead + "',");
}
if (model.cTaxHead != null)
{
strSql1.Append("cTaxHead,");
strSql2.Append("'" + model.cTaxHead + "',");
}
if (model.cClue != null)
{
strSql1.Append("cClue,");
strSql2.Append("'" + model.cClue + "',");
}
if (model.dSignDate != null)
{
strSql1.Append("dSignDate,");
strSql2.Append("'" + model.dSignDate + "',");
}
if (model.cCorInvCode != null)
{
strSql1.Append("cCorInvCode,");
strSql2.Append("'" + model.cCorInvCode + "',");
}
if (model.cFree1 != null)
{
strSql1.Append("cFree1,");
strSql2.Append("'" + model.cFree1 + "',");
}
if (model.cFree2 != null)
{
strSql1.Append("cFree2,");
strSql2.Append("'" + model.cFree2 + "',");
}
if (model.iTaxRate != null)
{
strSql1.Append("iTaxRate,");
strSql2.Append("" + model.iTaxRate + ",");
}
if (model.cDefine22 != null)
{
strSql1.Append("cDefine22,");
strSql2.Append("'" + model.cDefine22 + "',");
}
if (model.cDefine23 != null)
{
strSql1.Append("cDefine23,");
strSql2.Append("'" + model.cDefine23 + "',");
}
if (model.cDefine24 != null)
{
strSql1.Append("cDefine24,");
strSql2.Append("'" + model.cDefine24 + "',");
}
if (model.cDefine25 != null)
{
strSql1.Append("cDefine25,");
strSql2.Append("'" + model.cDefine25 + "',");
}
if (model.cDefine26 != null)
{
strSql1.Append("cDefine26,");
strSql2.Append("" + model.cDefine26 + ",");
}
if (model.cDefine27 != null)
{
strSql1.Append("cDefine27,");
strSql2.Append("" + model.cDefine27 + ",");
}
if (model.iPOsID != null)
{
strSql1.Append("iPOsID,");
strSql2.Append("" + model.iPOsID + ",");
}
if (model.cItemCode != null)
{
strSql1.Append("cItemCode,");
strSql2.Append("'" + model.cItemCode + "',");
}
if (model.cItem_class != null)
{
strSql1.Append("cItem_class,");
strSql2.Append("'" + model.cItem_class + "',");
}
if (model.cNLostType != null)
{
strSql1.Append("cNLostType,");
strSql2.Append("'" + model.cNLostType + "',");
}
if (model.mNLostTax != null)
{
strSql1.Append("mNLostTax,");
strSql2.Append("" + model.mNLostTax + ",");
}
if (model.cItemName != null)
{
strSql1.Append("cItemName,");
strSql2.Append("'" + model.cItemName + "',");
}
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.dSDate != null)
{
strSql1.Append("dSDate,");
strSql2.Append("'" + model.dSDate + "',");
}
if (model.cUnitID != null)
{
strSql1.Append("cUnitID,");
strSql2.Append("'" + model.cUnitID + "',");
}
if (model.cDefine28 != null)
{
strSql1.Append("cDefine28,");
strSql2.Append("'" + model.cDefine28 + "',");
}
if (model.cDefine29 != null)
{
strSql1.Append("cDefine29,");
strSql2.Append("'" + model.cDefine29 + "',");
}
if (model.cDefine30 != null)
{
strSql1.Append("cDefine30,");
strSql2.Append("'" + model.cDefine30 + "',");
}
if (model.cDefine31 != null)
{
strSql1.Append("cDefine31,");
strSql2.Append("'" + model.cDefine31 + "',");
}
if (model.cDefine32 != null)
{
strSql1.Append("cDefine32,");
strSql2.Append("'" + model.cDefine32 + "',");
}
if (model.cDefine33 != null)
{
strSql1.Append("cDefine33,");
strSql2.Append("'" + model.cDefine33 + "',");
}
if (model.cDefine34 != null)
{
strSql1.Append("cDefine34,");
strSql2.Append("" + model.cDefine34 + ",");
}
if (model.cDefine35 != null)
{
strSql1.Append("cDefine35,");
strSql2.Append("" + model.cDefine35 + ",");
}
if (model.cDefine36 != null)
{
strSql1.Append("cDefine36,");
strSql2.Append("'" + model.cDefine36 + "',");
}
if (model.cDefine37 != null)
{
strSql1.Append("cDefine37,");
strSql2.Append("'" + model.cDefine37 + "',");
}
if (model.iSBsID != null)
{
strSql1.Append("iSBsID,");
strSql2.Append("" + model.iSBsID + ",");
}
if (model.RdsId != null)
{
strSql1.Append("RdsId,");
strSql2.Append("" + model.RdsId + ",");
}
if (model.ContractRowGUID != null)
{
strSql1.Append("ContractRowGUID,");
strSql2.Append("'" + Guid.NewGuid().ToString() + "',");
}
if (model.iOriTaxCost != null)
{
strSql1.Append("iOriTaxCost,");
strSql2.Append("" + model.iOriTaxCost + ",");
}
if (model.UpSoType != null)
{
strSql1.Append("UpSoType,");
strSql2.Append("'" + model.UpSoType + "',");
}
if (model.cBAccounter != null)
{
strSql1.Append("cBAccounter,");
strSql2.Append("'" + model.cBAccounter + "',");
}
if (model.bCosting != null)
{
strSql1.Append("bCosting,");
strSql2.Append("" + (model.bCosting ? 1 : 0) + ",");
}
if (model.iInvMPCost != null)
{
strSql1.Append("iInvMPCost,");
strSql2.Append("" + model.iInvMPCost + ",");
}
if (model.ContractCode != null)
{
strSql1.Append("ContractCode,");
strSql2.Append("'" + model.ContractCode + "',");
}
if (model.ContractRowNo != null)
{
strSql1.Append("ContractRowNo,");
strSql2.Append("'" + model.ContractRowNo + "',");
}
if (model.copcode != null)
{
strSql1.Append("copcode,");
strSql2.Append("'" + model.copcode + "',");
}
if (model.cdescription != null)
{
strSql1.Append("cdescription,");
strSql2.Append("'" + model.cdescription + "',");
}
if (model.bTaxCost != null)
{
strSql1.Append("bTaxCost,");
strSql2.Append("" + (model.bTaxCost ? 1 : 0) + ",");
}
if (model.chyordercode != null)
{
strSql1.Append("chyordercode,");
strSql2.Append("'" + model.chyordercode + "',");
}
if (model.ihyorderdid != null)
{
strSql1.Append("ihyorderdid,");
strSql2.Append("" + model.ihyorderdid + ",");
}
if (model.inattaxprice != null)
{
strSql1.Append("inattaxprice,");
strSql2.Append("" + model.inattaxprice + ",");
}
if (model.iinvexchrate != null)
{
strSql1.Append("iinvexchrate,");
strSql2.Append("" + model.iinvexchrate + ",");
}
if (model.opseq != null)
{
strSql1.Append("opseq,");
strSql2.Append("'" + model.opseq + "',");
}
if (model.cBG_ItemCode != null)
{
strSql1.Append("cBG_ItemCode,");
strSql2.Append("'" + model.cBG_ItemCode + "',");
}
if (model.cBG_ItemName != null)
{
strSql1.Append("cBG_ItemName,");
strSql2.Append("'" + model.cBG_ItemName + "',");
}
if (model.cBG_CaliberKey1 != null)
{
strSql1.Append("cBG_CaliberKey1,");
strSql2.Append("'" + model.cBG_CaliberKey1 + "',");
}
if (model.cBG_CaliberKeyName1 != null)
{
strSql1.Append("cBG_CaliberKeyName1,");
strSql2.Append("'" + model.cBG_CaliberKeyName1 + "',");
}
if (model.cBG_CaliberKey2 != null)
{
strSql1.Append("cBG_CaliberKey2,");
strSql2.Append("'" + model.cBG_CaliberKey2 + "',");
}
if (model.cBG_CaliberKeyName2 != null)
{
strSql1.Append("cBG_CaliberKeyName2,");
strSql2.Append("'" + model.cBG_CaliberKeyName2 + "',");
}
if (model.cBG_CaliberKey3 != null)
{
strSql1.Append("cBG_CaliberKey3,");
strSql2.Append("'" + model.cBG_CaliberKey3 + "',");
}
if (model.cBG_CaliberKeyName3 != null)
{
strSql1.Append("cBG_CaliberKeyName3,");
strSql2.Append("'" + model.cBG_CaliberKeyName3 + "',");
}
if (model.cBG_CaliberCode1 != null)
{
strSql1.Append("cBG_CaliberCode1,");
strSql2.Append("'" + model.cBG_CaliberCode1 + "',");
}
if (model.cBG_CaliberName1 != null)
{
strSql1.Append("cBG_CaliberName1,");
strSql2.Append("'" + model.cBG_CaliberName1 + "',");
}
if (model.cBG_CaliberCode2 != null)
{
strSql1.Append("cBG_CaliberCode2,");
strSql2.Append("'" + model.cBG_CaliberCode2 + "',");
}
if (model.cBG_CaliberName2 != null)
{
strSql1.Append("cBG_CaliberName2,");
strSql2.Append("'" + model.cBG_CaliberName2 + "',");
}
if (model.cBG_CaliberCode3 != null)
{
strSql1.Append("cBG_CaliberCode3,");
strSql2.Append("'" + model.cBG_CaliberCode3 + "',");
}
if (model.cBG_CaliberName3 != null)
{
strSql1.Append("cBG_CaliberName3,");
strSql2.Append("'" + model.cBG_CaliberName3 + "',");
}
if (model.iBG_Ctrl != null)
{
strSql1.Append("iBG_Ctrl,");
strSql2.Append("" + model.iBG_Ctrl + ",");
}
if (model.cBG_Auditopinion != null)
{
strSql1.Append("cBG_Auditopinion,");
strSql2.Append("'" + model.cBG_Auditopinion + "',");
}
if (model.cConExecID != null)
{
strSql1.Append("cConExecID,");
strSql2.Append("'" + model.cConExecID + "',");
}
if (model.cConRowID != null)
{
strSql1.Append("cConRowID,");
strSql2.Append("'" + model.cConRowID + "',");
}
if (model.brettax != null)
{
strSql1.Append("brettax,");
strSql2.Append("" + model.brettax + ",");
}
if (model.fretquantity != null)
{
strSql1.Append("fretquantity,");
strSql2.Append("" + model.fretquantity + ",");
}
if (model.dretdate != null)
{
strSql1.Append("dretdate,");
strSql2.Append("'" + model.dretdate + "',");
}
if (model.dlastretdate != null)
{
strSql1.Append("dlastretdate,");
strSql2.Append("'" + model.dlastretdate + "',");
}
if (model.fretmoney != null)
{
strSql1.Append("fretmoney,");
strSql2.Append("" + model.fretmoney + ",");
}
if (model.iOriZbjMoney != null)
{
strSql1.Append("iOriZbjMoney,");
strSql2.Append("" + model.iOriZbjMoney + ",");
}
if (model.iOriNoRateZbjMoney != null)
{
strSql1.Append("iOriNoRateZbjMoney,");
strSql2.Append("" + model.iOriNoRateZbjMoney + ",");
}
if (model.iZbjMoney != null)
{
strSql1.Append("iZbjMoney,");
strSql2.Append("" + model.iZbjMoney + ",");
}
if (model.iNoRateZbjMoney != null)
{
strSql1.Append("iNoRateZbjMoney,");
strSql2.Append("" + model.iNoRateZbjMoney + ",");
}
if (model.ivouchrowno != null)
{
strSql1.Append("ivouchrowno,");
strSql2.Append("" + model.ivouchrowno + ",");
}
if (model.cPZNum != null)
{
strSql1.Append("cPZNum,");
strSql2.Append("'" + model.cPZNum + "',");
}
if (model.dkeepdate != null)
{
strSql1.Append("dkeepdate,");
strSql2.Append("'" + model.dkeepdate + "',");
}
if (model.cBG_CaliberKey4 != null)
{
strSql1.Append("cBG_CaliberKey4,");
strSql2.Append("'" + model.cBG_CaliberKey4 + "',");
}
if (model.cBG_CaliberKeyName4 != null)
{
strSql1.Append("cBG_CaliberKeyName4,");
strSql2.Append("'" + model.cBG_CaliberKeyName4 + "',");
}
if (model.cBG_CaliberKey5 != null)
{
strSql1.Append("cBG_CaliberKey5,");
strSql2.Append("'" + model.cBG_CaliberKey5 + "',");
}
if (model.cBG_CaliberKeyName5 != null)
{
strSql1.Append("cBG_CaliberKeyName5,");
strSql2.Append("'" + model.cBG_CaliberKeyName5 + "',");
}
if (model.cBG_CaliberKey6 != null)
{
strSql1.Append("cBG_CaliberKey6,");
strSql2.Append("'" + model.cBG_CaliberKey6 + "',");
}
if (model.cBG_CaliberKeyName6 != null)
{
strSql1.Append("cBG_CaliberKeyName6,");
strSql2.Append("'" + model.cBG_CaliberKeyName6 + "',");
}
if (model.cBG_CaliberCode4 != null)
{
strSql1.Append("cBG_CaliberCode4,");
strSql2.Append("'" + model.cBG_CaliberCode4 + "',");
}
if (model.cBG_CaliberName4 != null)
{
strSql1.Append("cBG_CaliberName4,");
strSql2.Append("'" + model.cBG_CaliberName4 + "',");
}
if (model.cBG_CaliberCode5 != null)
{
strSql1.Append("cBG_CaliberCode5,");
strSql2.Append("'" + model.cBG_CaliberCode5 + "',");
}
if (model.cBG_CaliberName5 != null)
{
strSql1.Append("cBG_CaliberName5,");
strSql2.Append("'" + model.cBG_CaliberName5 + "',");
}
if (model.cBG_CaliberCode6 != null)
{
strSql1.Append("cBG_CaliberCode6,");
strSql2.Append("'" + model.cBG_CaliberCode6 + "',");
}
if (model.cBG_CaliberName6 != null)
{
strSql1.Append("cBG_CaliberName6,");
strSql2.Append("'" + model.cBG_CaliberName6 + "',");
}
if (model.cbMemo != null)
{
strSql1.Append("cbMemo,");
strSql2.Append("'" + model.cbMemo + "',");
}
if (model.cbsysbarcode != null)
{
strSql1.Append("cbsysbarcode,");
strSql2.Append("'" + model.cbsysbarcode + "',");
}
strSql.Append("insert into PurBillVouchs(");
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 btnCheck_Click(object sender, EventArgs e)
{
try
{
gridViewPL.FocusedRowHandle -= 1;
gridViewPL.FocusedRowHandle += 1;
}
catch { }
try
{
string sErr = "";
if (txtCurrency.Text.Trim() == "")
{
sErr = sErr + "Please set currency\n";
}
decimal dExch = BaseFunction.ReturnDecimal(txtExchangeRate.Text.Trim());
if (dExch == 0)
{
sErr = sErr + "Please set the exchange rate\n";
}
int iCou = gridViewPL.RowCount;
if (iCou == 0)
{
sErr = sErr + "No data\n";
}
int iCodeCou = gridViewPL.RowCount;
string sWarn = "";
int iCount = 0;
SqlConnection conn = new SqlConnection(Conn);
conn.Open();
//启用事务
SqlTransaction tran = conn.BeginTransaction();
try
{
DateTime dLogDate = BaseFunction.ReturnDate(sLogDate);
//string sDate = dateEdit1.DateTime.ToString("yyyy-MM-dd");
string sDate = sLogDate;
string sSQL = @"
select bflag_PU from gl_mend where iyear = aaaaaa and iperiod = bbbbbb
";
sSQL = sSQL.Replace("aaaaaa", dLogDate.Year.ToString());
sSQL = sSQL.Replace("bbbbbb", dLogDate.Month.ToString());
DataTable dtTemp = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (BaseFunction.ReturnBool(dtTemp.Rows[0]["bflag_PU"]))
{
sErr = sErr + "The current date has been checked out\n";
}
for (int i = 0; i < gridViewPL.RowCount; i++)
{
string sCONTAINERNO = gridViewPL.GetRowCellValue(i, gridColCONTAINERNO).ToString().Trim();
sSQL = @"
select a.EntryNO,b.*
from RdRecords01_Import a
inner join (
select a.cCode,a.cVouchType ,a.cDepCode,a.dDate,a.cBusType ,a.cPTCode ,a.cExch_Name,a.cVenCode ,a.cRdCode ,a.cWhCode ,a.bRdFlag ,a.cPersonCode ,a.cSource,a.iExchRate
,b.AutoID,b.cInvCode,b.iQuantity,b.irowno,b.iOriCost,b.iOriMoney,b.iOriTaxPrice,b.iOriSum,b.iMoney,b.iTaxPrice,b.iSum,b.iTaxRate,b.iPOsID ,b.iOriTaxCost
from RdRecord01 a inner join Rdrecords01 b on a.id = b.id
)b on a.Rds01ID = b.Autoid
where a.InvoiceNo = 'aaaaaa' and a.autoid = 'bbbbbb'
";
sSQL = sSQL.Replace("aaaaaa", txtInvoiceNO.Text.Trim());
sSQL = sSQL.Replace("bbbbbb", gridViewPL.GetRowCellValue(i, gridColAutoID).ToString().Trim());
DataTable dtRd01 = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtRd01 == null || dtRd01.Rows.Count == 0)
{
sErr = sErr + "Line " + (i + 1).ToString() + " purchase receipt warrant is not exists [" + sCONTAINERNO + "]\n";
continue;
}
//判断是否生成了调拨单
string sTrIDs = gridViewPL.GetRowCellValue(i, gridColU8TrsID).ToString().Trim();
if (sTrIDs == "")
{
sWarn = sWarn + "Line " + (i + 1).ToString() + " no transfer order [" + sCONTAINERNO + "]\n";
}
else
{
sSQL = @"
select autoid from TransVouchs where autoid = aaaaaaaa
";
sSQL = sSQL.Replace("aaaaaaaa", sTrIDs);
DataTable dtTr = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtTr == null || dtTr.Rows.Count == 0)
{
sWarn = sWarn + "Line " + (i + 1).ToString() + " no transfer order [" + sCONTAINERNO + "]\n";
}
}
//判断是否生成了发票
string sPurIDs = gridViewPL.GetRowCellValue(i, gridColPurBillIDs).ToString().Trim();
if (sPurIDs != "")
{
sSQL = @"
select id from PurBillVouchs where ID = aaaaaaaa
";
sSQL = sSQL.Replace("aaaaaaaa", sPurIDs);
DataTable dtPur = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtPur != null && dtPur.Rows.Count > 0)
{
sErr = sErr + "Line " + (i + 1).ToString() + " imported invoice [" + sCONTAINERNO + "]\n";
continue;
}
}
DataRow[] drs = dtRd01.Select("Autoid = " + gridViewPL.GetRowCellValue(i, gridColRds01ID).ToString().Trim());
if (drs.Length == 0)
{
sErr = sErr + "Line " + (i + 1).ToString() + " purchase receipt warrant is not exists [" + sCONTAINERNO + "]\n";
continue;
}
Model.PurBillVouchs mods = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.PurBillVouchs();
sSQL = @"
Select b.iQuantity - isnull(b.iSumBillQuantity,0) as QtyUnRDIn
,b.*
from Rdrecord01 a inner join Rdrecords01 b on a.ID = b.ID
where AutoID = bbbbbb
";
sSQL = sSQL.Replace("bbbbbb", gridViewPL.GetRowCellValue(i, gridColRds01ID).ToString());
DataTable dtRds01 = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtRds01 == null || dtRds01.Rows.Count == 0)
{
sErr = sErr + "Line " + (i + 1).ToString() + " purchase entry form is not imported [" + sCONTAINERNO + "]\n";
continue;
}
decimal dQtyUnRDIn = BaseFunction.ReturnDecimal(dtRds01.Rows[0]["QtyUnRDIn"]);
mods.iPBVQuantity = BaseFunction.ReturnDecimal(gridViewPL.GetRowCellValue(i, gridColQUANTITY));
if (mods.iPBVQuantity > dQtyUnRDIn)
{
sErr = sErr + "Row " + (i + 1).ToString() + " qty err [" + sCONTAINERNO + "]\n";
continue;
}
}
if (sErr != "")
{
string sInfo = "";
if (sWarn != "")
{
sInfo = "Warn:\n" + sWarn + "\n\n";
}
sInfo = sInfo + "Err:\n" + sErr;
throw new Exception(sInfo);
}
tran.Commit();
MessageBox.Show("CHECK OK");
}
catch (Exception error)
{
tran.Rollback();
throw new Exception(error.Message);
}
}
catch (Exception ee)
{
FrmMsgBox frm = new FrmMsgBox();
frm.richTextBox1.Text = ee.Message;
frm.ShowDialog();
}
}
