/// <summary>
/// 增加一条数据
/// </summary>
public string Add(UFIDA.U8.UAP.CustomApp.ControlForm.Model.PurBillVouchs_Import model)
{
StringBuilder strSql = new StringBuilder();
StringBuilder strSql1 = new StringBuilder();
StringBuilder strSql2 = new StringBuilder();
if (model.ImportTime != null)
{
strSql1.Append("ImportTime,");
strSql2.Append("'" + model.ImportTime + "',");
}
if (model.InvoiceNo != null)
{
strSql1.Append("InvoiceNo,");
strSql2.Append("'" + model.InvoiceNo + "',");
}
if (model.Date != null)
{
strSql1.Append("Date,");
strSql2.Append("'" + model.Date + "',");
}
if (model.Companyname != null)
{
strSql1.Append("Companyname,");
strSql2.Append("'" + model.Companyname + "',");
}
if (model.PONO != null)
{
strSql1.Append("PONO,");
strSql2.Append("'" + model.PONO + "',");
}
if (model.PARTNO != null)
{
strSql1.Append("PARTNO,");
strSql2.Append("'" + model.PARTNO + "',");
}
if (model.DESCRIPTIONENGLISH != null)
{
strSql1.Append("DESCRIPTIONENGLISH,");
strSql2.Append("'" + model.DESCRIPTIONENGLISH + "',");
}
if (model.QUANTITY != null)
{
strSql1.Append("QUANTITY,");
strSql2.Append("" + model.QUANTITY + ",");
}
if (model.NW != null)
{
strSql1.Append("NW,");
strSql2.Append("" + model.NW + ",");
}
if (model.GW != null)
{
strSql1.Append("GW,");
strSql2.Append("" + model.GW + ",");
}
if (model.PRICEPERUNIT != null)
{
strSql1.Append("PRICEPERUNIT,");
strSql2.Append("" + model.PRICEPERUNIT + ",");
}
if (model.UNIT != null)
{
strSql1.Append("UNIT,");
strSql2.Append("'" + model.UNIT + "',");
}
if (model.TOTALPRICE != null)
{
strSql1.Append("TOTALPRICE,");
strSql2.Append("" + model.TOTALPRICE + ",");
}
strSql.Append("insert into PurBillVouchs_Import(");
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());
}
private void btnImprot_Click(object sender, EventArgs e)
{
try
{
gridViewIN.FocusedRowHandle -= 1;
gridViewIN.FocusedRowHandle += 1;
gridViewPL.FocusedRowHandle -= 1;
gridViewPL.FocusedRowHandle += 1;
}
catch { }
try
{
if (dateEdit1.DateTime < BaseFunction.ReturnDate("2017-01-01"))
{
throw new Exception("Date is err");
}
decimal dPLSum = BaseFunction.ReturnDecimal(gridViewPL.Columns["QUANTITY"].SummaryItem.SummaryValue);
decimal dINSum = BaseFunction.ReturnDecimal(gridViewIN.Columns["QUANTITY"].SummaryItem.SummaryValue);
if (dPLSum != dINSum)
{
throw new Exception("Please check the quantity");
}
if (dtmBL.Text.Trim() == "")
{
dtmBL.Focus();
throw new Exception("Please set b/l date");
}
//if (dtmBL.DateTime < BaseFunction.ReturnDate(sLogDate).AddMonths(-1))
//{
// dtmBL.Focus();
// throw new Exception("Please set b/l date");
//}
if (txtBL.Text.Trim() == "")
{
txtBL.Focus();
throw new Exception("Please set b/l no");
}
if (gridViewIN.RowCount == 0 || gridViewPL.RowCount == 0)
{
throw new Exception("No data");
}
string sErr = "";
string sWarn = "";
int iCount = 0;
SqlConnection conn = new SqlConnection(Conn);
conn.Open();
//启用事务
SqlTransaction tran = conn.BeginTransaction();
try
{
string sSQL = "select getdate()";
DateTime dNow = BaseFunction.ReturnDate(DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0].Rows[0][0]);
DateTime dNowDate = BaseFunction.ReturnDate(dNow.ToString("yyyy-MM-dd"));
string sDateTxt = dNow.ToString("yyyyMMddHHmmss");
CheckData(tran);
sSQL = @"
select a.cPOID,b.*,inv.cInvAddCode
from PO_Pomain a
inner join PO_Podetails b on a.poid = b.poid
inner join Inventory inv on inv.cInvCode = b.cInvCode
where a.cPOID in (select PONO from RdRecords01_temp)
";
DataTable dtPO = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
for (int i = 0; i < gridViewPL.RowCount; i++)
{
Model.RdRecords01_Import modRdsImport = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.RdRecords01_Import();
modRdsImport.ImportTime = sDateTxt;
modRdsImport.InvoiceNo = txtInvoiceNO.Text.Trim();
modRdsImport.Date = dateEdit1.DateTime;
modRdsImport.Companyname = txtCompany.Text.Trim();
modRdsImport.PONO = gridViewPL.GetRowCellValue(i, gridColPONO).ToString().Trim();
modRdsImport.CONTAINERNO = gridViewPL.GetRowCellValue(i, gridColCONTAINERNO).ToString().Trim();
modRdsImport.DESCRIPTIONOFPRODUCTSEN = gridViewPL.GetRowCellValue(i, gridColDESCRIPTIONOFPRODUCTSEN).ToString().Trim();
modRdsImport.CASENO = gridViewPL.GetRowCellValue(i, gridColCASENO).ToString().Trim();
modRdsImport.BOXNO = gridViewPL.GetRowCellValue(i, gridColBOXNO).ToString().Trim();
modRdsImport.BOXQTY = BaseFunction.ReturnDecimal(gridViewPL.GetRowCellValue(i, gridColBOXQTY), 6);
modRdsImport.QUANTITY = BaseFunction.ReturnDecimal(gridViewPL.GetRowCellValue(i, gridColQUANTITY), 6);
modRdsImport.UNIT = gridViewPL.GetRowCellValue(i, gridColUNIT).ToString().Trim();
modRdsImport.NWKGS = BaseFunction.ReturnDecimal(gridViewPL.GetRowCellValue(i, gridColNWKGS), 6);
modRdsImport.GWKGS = BaseFunction.ReturnDecimal(gridViewPL.GetRowCellValue(i, gridColGWKGS), 6);
modRdsImport.BOXCBM = BaseFunction.ReturnDecimal(gridViewPL.GetRowCellValue(i, gridColBOXCBM), 6);
modRdsImport.CONNO = gridViewPL.GetRowCellValue(i, gridColCONNO).ToString().Trim();
modRdsImport.BLDate = dtmBL.DateTime;
modRdsImport.BLNo = txtBL.Text.Trim();
modRdsImport.Currency = txtCurrency.Text.Trim();
DataRow[] dr = dtPO.Select("cPOID = '" + modRdsImport.PONO + "' and (cInvAddCode = '" + gridViewPL.GetRowCellValue(i, gridColPARTNO).ToString().Trim() + "' or cInvCode = '" + gridViewPL.GetRowCellValue(i, gridColPARTNO).ToString().Trim() + "')");
if (dr.Length <= 0)
{
sErr = sErr + "Line " + (i + 1).ToString() + " PO No does not match\n";
continue;
}
modRdsImport.PARTNO = dr[0]["cInvCode"].ToString().Trim();
modRdsImport.POsID = BaseFunction.ReturnInt(dr[0]["ID"]);
DAL.RdRecords01_Import dalRdImport = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.RdRecords01_Import();
sSQL = dalRdImport.Add(modRdsImport);
iCount = iCount + DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
for (int i = 0; i < gridViewIN.RowCount; i++)
{
Model.PurBillVouchs_Import modINImport = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.PurBillVouchs_Import();
modINImport.ImportTime = sDateTxt;
modINImport.InvoiceNo = txtInvoiceNO.Text.Trim();
modINImport.Date = dateEdit1.DateTime;
modINImport.Companyname = txtCompany.Text.Trim();
modINImport.PONO = gridViewIN.GetRowCellValue(i, gridCol_PONO).ToString().Trim();
sSQL = @"
select inv.cInvCode,inv.cInvAddCode
from Inventory inv
inner join (
select distinct b.cInvCode
from PO_Pomain a inner join po_podetails b on a.POID = b.POID
where a.cPOID = '{1}'
) po on inv.cInvCode = po.cInvCode
where inv.cInvAddCode = '{0}' or inv.cInvCode = '{0}'
";
sSQL = string.Format(sSQL, gridViewIN.GetRowCellValue(i, gridCol_PARTNO).ToString().Trim(), gridViewIN.GetRowCellValue(i, gridCol_PONO).ToString().Trim());
DataTable dtInv = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtInv == null || dtInv.Rows.Count == 0)
{
sErr = sErr + "Line " + (i + 1).ToString() + " Part No does not match\n";
continue;
}
modINImport.PARTNO = dtInv.Rows[0]["cInvCode"].ToString().Trim();
modINImport.DESCRIPTIONENGLISH = gridViewIN.GetRowCellValue(i, gridCol_DESCRIPTIONENGLISH).ToString().Trim();
modINImport.QUANTITY = BaseFunction.ReturnDecimal(gridViewIN.GetRowCellValue(i, gridCol_QUANTITY), 6);
modINImport.NW = BaseFunction.ReturnDecimal(gridViewIN.GetRowCellValue(i, gridCol_NW), 6);
modINImport.GW = BaseFunction.ReturnDecimal(gridViewIN.GetRowCellValue(i, gridCol_GW), 6);
modINImport.PRICEPERUNIT = BaseFunction.ReturnDecimal(gridViewIN.GetRowCellValue(i, gridCol_PRICEPERUNIT), 6);
modINImport.UNIT = gridViewIN.GetRowCellValue(i, gridCol_UNIT).ToString().Trim();
modINImport.TOTALPRICE = BaseFunction.ReturnDecimal(gridViewIN.GetRowCellValue(i, gridCol_TOTALPRICE), 6);
DAL.PurBillVouchs_Import dalInImprot = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.PurBillVouchs_Import();
sSQL = dalInImprot.Add(modINImport);
iCount = iCount + DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
//按照发票刷新装箱单单价
sSQL = @"
update a set a.UnitPrice = b.priceperunit
from RdRecords01_Import a inner join PurBillVouchs_Import b on a.ImportTime = b.ImportTime and a.PONO = b.PONO and a.PARTNO = b.PARTNO
";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
if (sWarn.Length > 0)
{
FrmMsgBoxYesOrNo frm = new FrmMsgBoxYesOrNo();
frm.richTextBox1.Text = sWarn;
if (DialogResult.Yes != frm.ShowDialog())
{
throw new Exception("User cancelled");
}
}
if (sErr.Length > 0)
{
throw new Exception(sErr);
}
if (iCount > 0)
{
tran.Commit();
MessageBox.Show("Success");
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();
}
}
