Exemple #1
0
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public string Add(TH.clsU8.Model.Ap_CloseBills model)
        {
            StringBuilder strSql  = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();

            if (model.iID != null)
            {
                strSql1.Append("iID,");
                strSql2.Append("" + model.iID + ",");
            }
            if (model.ID != null)
            {
                strSql1.Append("ID,");
                strSql2.Append("" + model.ID + ",");
            }
            if (model.iType != null)
            {
                strSql1.Append("iType,");
                strSql2.Append("" + model.iType + ",");
            }
            if (model.bPrePay != null)
            {
                strSql1.Append("bPrePay,");
                strSql2.Append("" + (model.bPrePay ? 1 : 0) + ",");
            }
            if (model.cCusVen != null)
            {
                strSql1.Append("cCusVen,");
                strSql2.Append("'" + model.cCusVen + "',");
            }
            if (model.iAmt_f != null)
            {
                strSql1.Append("iAmt_f,");
                strSql2.Append("" + model.iAmt_f + ",");
            }
            if (model.iAmt != null)
            {
                strSql1.Append("iAmt,");
                strSql2.Append("" + model.iAmt + ",");
            }
            if (model.iRAmt_f != null)
            {
                strSql1.Append("iRAmt_f,");
                strSql2.Append("" + model.iRAmt_f + ",");
            }
            if (model.iRAmt != null)
            {
                strSql1.Append("iRAmt,");
                strSql2.Append("" + model.iRAmt + ",");
            }
            if (model.cKm != null)
            {
                strSql1.Append("cKm,");
                strSql2.Append("'" + model.cKm + "',");
            }
            if (model.cXmClass != null)
            {
                strSql1.Append("cXmClass,");
                strSql2.Append("'" + model.cXmClass + "',");
            }
            if (model.cXm != null)
            {
                strSql1.Append("cXm,");
                strSql2.Append("'" + model.cXm + "',");
            }
            if (model.cDepCode != null)
            {
                strSql1.Append("cDepCode,");
                strSql2.Append("'" + model.cDepCode + "',");
            }
            if (model.cPersonCode != null)
            {
                strSql1.Append("cPersonCode,");
                strSql2.Append("'" + model.cPersonCode + "',");
            }
            if (model.cOrderID != null)
            {
                strSql1.Append("cOrderID,");
                strSql2.Append("'" + model.cOrderID + "',");
            }
            if (model.cItemName != null)
            {
                strSql1.Append("cItemName,");
                strSql2.Append("'" + model.cItemName + "',");
            }
            if (model.cConType != null)
            {
                strSql1.Append("cConType,");
                strSql2.Append("'" + model.cConType + "',");
            }
            if (model.cConID != null)
            {
                strSql1.Append("cConID,");
                strSql2.Append("'" + model.cConID + "',");
            }
            if (model.iAmt_s != null)
            {
                strSql1.Append("iAmt_s,");
                strSql2.Append("" + model.iAmt_s + ",");
            }
            if (model.iRAmt_s != null)
            {
                strSql1.Append("iRAmt_s,");
                strSql2.Append("" + model.iRAmt_s + ",");
            }
            if (model.iOrderType != null)
            {
                strSql1.Append("iOrderType,");
                strSql2.Append("" + model.iOrderType + ",");
            }
            if (model.cDLCode != null)
            {
                strSql1.Append("cDLCode,");
                strSql2.Append("'" + model.cDLCode + "',");
            }
            if (model.ccItemCode != null)
            {
                strSql1.Append("ccItemCode,");
                strSql2.Append("'" + model.ccItemCode + "',");
            }
            if (model.RegisterFlag != null)
            {
                strSql1.Append("RegisterFlag,");
                strSql2.Append("" + model.RegisterFlag + ",");
            }
            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.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.cStageCode != null)
            {
                strSql1.Append("cStageCode,");
                strSql2.Append("'" + model.cStageCode + "',");
            }
            if (model.cCoVouchID != null)
            {
                strSql1.Append("cCoVouchID,");
                strSql2.Append("'" + model.cCoVouchID + "',");
            }
            if (model.cExecID != null)
            {
                strSql1.Append("cExecID,");
                strSql2.Append("'" + model.cExecID + "',");
            }
            if (model.cMemo != null)
            {
                strSql1.Append("cMemo,");
                strSql2.Append("'" + model.cMemo + "',");
            }
            if (model.iSrcClosesID != null)
            {
                strSql1.Append("iSrcClosesID,");
                strSql2.Append("" + model.iSrcClosesID + ",");
            }
            if (model.ifaresettled_f != null)
            {
                strSql1.Append("ifaresettled_f,");
                strSql2.Append("" + model.ifaresettled_f + ",");
            }
            if (model.cEBOrderCode != null)
            {
                strSql1.Append("cEBOrderCode,");
                strSql2.Append("'" + model.cEBOrderCode + "',");
            }
            strSql.Append("insert into Ap_CloseBills(");
            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());
        }
Exemple #2
0
        private void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                gridView1.PostEditor();

                string        sErr   = "";
                int           iCount = 0;
                SqlConnection conn   = new SqlConnection(sConnString);
                conn.Open();
                //启用事务
                SqlTransaction tran = conn.BeginTransaction();
                string         sSQL = "";
                try
                {
                    ////获得单据号
                    //sSQL = "select max(cNumber) as Maxnumber From VoucherHistory  with (ROWLOCK)  Where  CardNumber='RR' and cContent is NULL";
                    //dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];

                    //long lCode = 0;
                    //if (dt != null && dt.Rows.Count > 0)
                    //{
                    //    lCode = BaseFunction.ReturnLong(dt.Rows[0]["Maxnumber"]);
                    //}
                    //else
                    //{
                    //    lCode = 0;
                    //}

                    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'SK',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", 1.ToString());
                    sSQL = sSQL.Replace("dddddd", s数据库.Substring(7, 3));
                    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]) - 1;

                    ArrayList aList = new ArrayList();

                    for (int i = 0; i < gridView1.RowCount; i++)
                    {
                        if (!BaseFunction.ReturnBool(gridView1.GetRowCellValue(i, gridColchoose)))
                        {
                            continue;
                        }

                        bool bExists = false;

                        string sDocumentNo = gridView1.GetRowCellValue(i, gridColDocumentNo).ToString().Trim();
                        for (int j = 0; j < aList.Count; j++)
                        {
                            if (sDocumentNo == aList[j].ToString().Trim())
                            {
                                bExists = true;
                                break;
                            }
                        }
                        if (bExists)
                        {
                            continue;
                        }

                        aList.Add(sDocumentNo);

                        DateTime dDate = BaseFunction.ReturnDate(gridView1.GetRowCellValue(i, gridColDate));
                        sSQL = "select isnull(bflag_AR,0) as bflag from GL_mend where iYPeriod = '" + dDate.ToString("yyyyMM") + "'";
                        dt   = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                        if (dt == null || dt.Rows.Count == 0)
                        {
                            sErr = sErr + "Row " + (i + 1).ToString() + " access module state failure\n";
                            continue;
                        }
                        int i结账 = BaseFunction.ReturnInt(dt.Rows[0]["bflag"]);
                        if (i结账 > 0)
                        {
                            sErr = sErr + "Row " + (i + 1).ToString() + " date is err\n";
                            continue;
                        }

                        sSQL = "select count(1) from Ap_CloseBills where isnull(cDefine22,'') = 'aaaaaa'";
                        sSQL = sSQL.Replace("aaaaaa", sDocumentNo);
                        DataTable dtDoc = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];

                        if (dtDoc != null && dtDoc.Rows.Count > 0 && BaseFunction.ReturnInt(dtDoc.Rows[0][0]) > 0)
                        {
                            sErr = sErr + "row " + i.ToString() + " Document No. is exists err\n";
                            continue;
                        }

                        //lCode += 1;
                        //string sCode = lCode.ToString();
                        //while (sCode.Length < 10)
                        //{
                        //    sCode = "0" + sCode;
                        //}

                        lID += 1;

                        TH.clsU8.Model.Ap_CloseBill model = new TH.clsU8.Model.Ap_CloseBill();
                        model.cVouchType = "48";

                        model.cVouchID   = sDocumentNo;
                        model.dVouchDate = BaseFunction.ReturnDate(dDate.ToString("yyyy-MM-dd"));
                        model.iPeriod    = model.dVouchDate.Month - 3;
                        if (model.iPeriod <= 0)
                        {
                            model.iPeriod = model.iPeriod + 12;
                        }

                        sSQL = "select * from Customer where isnull(cCusDefine2,'') = '" + gridView1.GetRowCellValue(i, gridColGMCVCode).ToString().Trim() + "'";
                        DataTable dtCus = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                        if (dtCus == null || dtCus.Rows.Count == 0)
                        {
                            sErr = sErr + "row " + i.ToString() + "GMCV Code  is not exists err\n";
                            continue;
                        }
                        model.cDwCode = dtCus.Rows[0]["cCusCode"].ToString().Trim();
                        model.cSSCode = "1";        //需要默认值

                        sSQL = "select * from foreigncurrency where cexch_name = 'aaaaaa' or cexch_code = 'aaaaaa'";
                        sSQL = sSQL.Replace("aaaaaa", gridView1.GetRowCellValue(i, gridColCurency).ToString().Trim());
                        DataTable dtForeigncurrency = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                        if (dtForeigncurrency == null || dtForeigncurrency.Rows.Count == 0)
                        {
                            sErr = sErr + "row " + i.ToString() + "Curency is not exists err\n";
                            continue;
                        }
                        model.cexch_name = dtForeigncurrency.Rows[0]["cexch_name"].ToString().Trim();
                        model.iExchRate  = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridColExchangeRate));
                        if (model.iExchRate == 0)
                        {
                            sErr = sErr + "row " + i.ToString() + " Please set Exchange Rate\n";
                            continue;
                        }
                        model.cOperator      = sUserName;
                        model.bPrePay        = false;
                        model.bStartFlag     = false;
                        model.iPayForOther   = false;
                        model.cFlag          = "AR";
                        model.bSend          = false;
                        model.bReceived      = false;
                        model.iID            = lID;
                        model.bFromBank      = false;
                        model.bToBank        = false;
                        model.bSure          = false;
                        model.VT_ID          = 8052;
                        model.iAmount        = 0;
                        model.IsWfControlled = false;
                        model.RegisterFlag   = 0;
                        model.dcreatesystime = DateTime.Now;
                        model.ibg_ctrl       = false;
                        model.ibg_ctrl       = false;
                        model.iPrintCount    = 0;
                        model.iPayForOther   = false;
                        model.iAmount_s      = 0;

                        decimal d本币 = 0;
                        decimal d原币 = 0;

                        ArrayList aListDetails = new ArrayList();
                        for (int j = i; j < gridView1.RowCount; j++)
                        {
                            string sDocumentNo2 = gridView1.GetRowCellValue(j, gridColDocumentNo).ToString().Trim();
                            if (sDocumentNo != sDocumentNo2)
                            {
                                continue;
                            }

                            TH.clsU8.Model.Ap_CloseBills models = new TH.clsU8.Model.Ap_CloseBills();
                            models.iID = model.iID;

                            lIDDetails    += 1;
                            models.ID      = lIDDetails;
                            models.iType   = 0;
                            models.bPrePay = false;
                            models.cCusVen = model.cDwCode;
                            models.iAmt_f  = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(j, gridColOriginal));
                            models.iRAmt_f = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(j, gridColOriginal));
                            d原币            = d原币 + models.iAmt_f;

                            models.iAmt  = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(j, gridColLocalAMT));
                            models.iRAmt = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(j, gridColLocalAMT));
                            d本币          = d本币 + models.iAmt;

                            models.iAmt_s         = 0;
                            models.RegisterFlag   = 0;
                            models.iSrcClosesID   = 0;
                            models.ifaresettled_f = 0;
                            models.cMemo          = gridView1.GetRowCellValue(j, gridColDiscription).ToString().Trim();
                            models.cDefine22      = gridView1.GetRowCellValue(j, gridColDocumentNo).ToString().Trim();
                            models.cDefine23      = gridView1.GetRowCellValue(j, gridColInvoiceNo).ToString().Trim();
                            models.iRAmt_s        = 0;

                            TH.clsU8.DAL.Ap_CloseBills dals = new TH.clsU8.DAL.Ap_CloseBills();
                            sSQL = dals.Add(models);
                            aListDetails.Add(sSQL);
                        }
                        model.iAmount    = d本币;
                        model.iAmount_f  = d原币;
                        model.iRAmount   = d本币;
                        model.iRAmount_f = d原币;

                        TH.clsU8.DAL.Ap_CloseBill dal = new TH.clsU8.DAL.Ap_CloseBill();
                        sSQL = dal.Add(model);
                        DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
                        for (int ii = 0; ii < aListDetails.Count; ii++)
                        {
                            iCount = iCount + DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, aListDetails[ii].ToString());
                        }
                    }

                    if (sErr != "")
                    {
                        throw new Exception(sErr);
                    }

                    if (lID > 1000000000)
                    {
                        lID = lID - 1000000000;
                    }
                    if (lIDDetails > 1000000000)
                    {
                        lIDDetails = lIDDetails - 1000000000;
                    }
                    sSQL = "update UFSystem..UA_Identity set iFatherId = " + lID.ToString() + ",iChildId = " + lIDDetails + " where cAcc_Id = '" + s数据库.Substring(7, 3) + "' and cVouchType = 'SK'";
                    DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);

//                    sSQL = @"
//if exists(select * from VoucherHistory where CardNumber='RR' and cContent is NULL)
//	update VoucherHistory set cNumber = aaaaaa  where CardNumber='RR' and cContent is NULL
//else
//	insert into VoucherHistory(CardNumber,cContent,cContentRule,cSeed,cNumber,bEmpty)
//	values('RR',null,null,null,'aaaaaa',0)
//";
//                    sSQL = sSQL.Replace("aaaaaa", lCode.ToString());
//                    DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                    if (iCount > 0)
                    {
                        tran.Commit();

                        MessageBox.Show("OK\n");
                    }
                    else
                    {
                        throw new Exception("Save failed");
                    }
                }
                catch (Exception error)
                {
                    tran.Rollback();
                    throw new Exception(error.Message);
                }
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.Message);
            }
        }