예제 #1
0
        /// <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());
        }
예제 #2
0
        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();
            }
        }