Beispiel #1
0
        public string Save材料出库单(DataTable dtData)
        {
            string s = "";

            try
            {
                string sErr = "";

                if (dtData == null || dtData.Rows.Count < 1)
                {
                    throw new Exception("没有需要保存的数据");
                }

                SqlConnection conn = new SqlConnection(ClsBaseClass.ClsBaseDataInfo.sConnString);
                conn.Open();
                //启用事务
                SqlTransaction tran = conn.BeginTransaction();

                try
                {
                    string   sSQL     = "select getdate()";
                    DateTime d当前服务器时间 = Convert.ToDateTime(SqlHelper.ExecuteScalar(tran, CommandType.Text, sSQL));

                    //1.   判断是否结账
                    sSQL = "select * from gl_mend where iyear=year(getdate()) and iperiod=month(getdate())";
                    DataTable dtTemp = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                    if (dtTemp == null || dtTemp.Rows.Count < 1)
                    {
                        throw new Exception("判断模块结账失败");
                    }
                    int iR = ClsBaseDataInfo.ReturnObjectToInt(dtTemp.Rows[0]["bflag_ST"]);
                    if (iR == 1)
                    {
                        throw new Exception("当前月份已经结账");
                    }


                    //2. 获得单据ID
                    long      lID       = 1;
                    long      lIDDetail = 1;
                    ClsU8基础档案 cls       = new ClsU8基础档案();
                    cls.GetRdID(out lID, out lIDDetail, ClsBaseDataInfo.sUFDataBaseName.Trim().Substring(7, 3));


                    //3. 获得单据号
                    long iCode = 0;
                    sSQL   = "select * From VoucherHistory  with (ROWLOCK) Where  CardNumber='0412'";
                    dtTemp = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                    if (dtTemp == null || dtTemp.Rows.Count < 1)
                    {
                        iCode = 1;
                    }
                    else
                    {
                        iCode = ClsBaseDataInfo.ReturnObjectToLong(dtTemp.Rows[0]["cNumber"]);
                    }

                    sSQL = "select * from mom_order a inner join mom_orderdetail b on a.moid = b.moid where a.mocode = '" + dtData.Rows[0]["单据号"].ToString().Trim() + "' and b.SortSeq = '" + dtData.Rows[0]["行号"].ToString().Trim() + "'";
                    DataTable dt生产订单表头 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                    if (dt生产订单表头 == null || dt生产订单表头.Rows.Count < 1)
                    {
                        throw new Exception("获得生产订单信息失败");
                    }

                    //4. 组装表头
                    lID += 1;
                    string s仓库       = dtData.Rows[0]["仓库"].ToString().Trim();
                    string s订单号      = dtData.Rows[0]["单据号"].ToString().Trim();
                    string s行号       = dtData.Rows[0]["行号"].ToString().Trim();
                    string s制单人      = dtData.Rows[0]["制单人"].ToString().Trim();
                    string s产量       = dt生产订单表头.Rows[0]["Qty"].ToString().Trim();
                    string s产品编码     = dt生产订单表头.Rows[0]["InvCode"].ToString().Trim();
                    string s生产订单主表标志 = dt生产订单表头.Rows[0]["MoId"].ToString().Trim();
                    iCode += 1;
                    string s单据号 = sGetCode(iCode, 10);

                    sSQL = " insert into rdrecord11(id,brdflag,cvouchtype,cbustype,csource,cwhcode,ddate,ccode,crdcode,cdepcode " +
                           ",cmaker,imquantity,vt_id,cpspcode,cmpocode,iproorderid,bomfirst,biscomplement,iswfcontrolled,dnmaketime " +
                           ",dnmodifytime,dnverifytime,bmotran,cdefine1)  " +
                           "values (N'" + lID + "',N'0',N'11',N'领料',N'生产订单',N'" + s仓库 + "','" + d当前服务器时间.ToString("yyyy-MM-dd") + "',N'" + s单据号 + "',N'21',N'05' " +
                           ",N'" + s制单人 + "'," + s产量 + ",65,N'" + s产品编码 + "',N'" + s订单号 + "'," + s生产订单主表标志 + ",0,0,0, getdate()" +
                           ", Null , Null ,N'0','" + s订单号 + "' )";
                    SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                    for (int i = 0; i < dtData.Rows.Count; i++)
                    {
                        //判断条码是否已经使用
                        sSQL = "select * from rdrecords11 where BarCode = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["条形码"].ToString()) + " ";
                        int iCou = ClsBaseDataInfo.ReturnObjectToInt(SqlHelper.ExecuteScalar(tran, CommandType.Text, sSQL));
                        if (iCou > 0)
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "已经使用\n";
                            continue;
                        }

                        sSQL = "select a.moid, b.MoDId ,c.AllocateId ,c.InvCode ,c.Qty ,c.IssQty  from mom_order a inner join mom_orderdetail b on a.moid = b.moid inner join mom_moallocate c on c.MoDId  = b.Modid where a.mocode = '" + s订单号 + "' and b.SortSeq = '" + s行号 + "' and c.InvCode = '" + dtData.Rows[i]["存货编码"].ToString().Trim() + "' and isnull(LotNo,'') = '" + dtData.Rows[i]["批号"].ToString().Trim().Substring(0, dtData.Rows[i]["批号"].ToString().Trim().Length - 3) + "'";
                        DataTable dt生产订单信息 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                        if (dt生产订单信息 == null || dt生产订单信息.Rows.Count < 1)
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "获得生产订单材料信息失败,请核实材料或批号是否匹配\n";
                            continue;
                        }

                        decimal d应领数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt生产订单信息.Rows[0]["Qty"]);
                        decimal d已领数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt生产订单信息.Rows[0]["IssQty"]);
                        decimal d数量   = ClsBaseDataInfo.ReturnObjectToDecimal(dtData.Rows[i]["数量"], 6);
                        if (d应领数量 * (decimal)1.2 < d已领数量 + d数量)
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + " 超订单领料\n";
                            continue;
                        }

                        sSQL = "select * from Inventory where cInvCode = '" + dtData.Rows[i]["存货编码"].ToString().Trim() + "'";
                        DataTable dt存货信息 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                        if (dt存货信息 == null || dt存货信息.Rows.Count < 1)
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "获得存货信息失败\n";
                            continue;
                        }

                        int i是否批次 = ClsBaseDataInfo.ReturnObjectToInt(dt存货信息.Rows[0]["bInvBatch"]);
                        if (i是否批次 != 0 && dtData.Rows[i]["批号"].ToString().Trim() == "")
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "是批次管理物料,必须输入批号\n";
                            continue;
                        }
                        int i是否自由项1 = ClsBaseDataInfo.ReturnObjectToInt(dt存货信息.Rows[0]["bFree1"]);
                        if (i是否自由项1 != 0 && dtData.Rows[i]["长度"].ToString().Trim() == "")
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "必须输入长度\n";
                            continue;
                        }

                        //组装表体
                        sSQL = "select * from CurrentStock where cWhCode = '" + dtData.Rows[i]["仓库"].ToString().Trim() + "' and cInvCode = '" + dtData.Rows[i]["存货编码"].ToString().Trim() + "' and isnull(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString().Trim() + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "'   ";
                        DataTable dt现存量 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];

                        lIDDetail += 1;
                        string s存货编码 = dtData.Rows[i]["存货编码"].ToString().Trim();
                        //decimal d数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dtData.Rows[i]["数量"], 6);
                        decimal d件数 = 0;

                        decimal d现存数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt现存量.Rows[0]["iQuantity"], 6);
                        decimal d现存件数 = ClsBaseDataInfo.ReturnObjectToDecimal(dt现存量.Rows[0]["iNum"], 6);
                        if (d现存件数 > 0)
                        {
                            if (d数量 == d现存数量)
                            {
                                d件数 = d现存件数;
                            }
                            else
                            {
                                d件数 = ClsBaseDataInfo.ReturnObjectToDecimal(d数量 * d现存件数 / d现存数量, 6);
                            }
                        }


                        decimal d生产订单子件数量   = ClsBaseDataInfo.ReturnObjectToDecimal(dt生产订单信息.Rows[0]["Qty"], 6);
                        decimal d生产订单子件已领数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt生产订单信息.Rows[0]["IssQty"], 6);
                        decimal d生产订单待领数量   = d生产订单子件数量 - d生产订单子件已领数量;

                        //if (d生产订单待领数量 < d数量)
                        //{
                        //    sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "超订单领料\n";
                        //    continue;
                        //}

                        sSQL = "select * from Warehouse where cWhCode = '" + s仓库 + "'";
                        DataTable dtWH = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];


                        sSQL = "Insert Into rdrecords11(autoid,id,cinvcode,inum,iquantity,iunitcost,iprice,ipunitcost,ipprice,cbatch " +
                               "	,cobjcode,cvouchcode,cinvouchcode,cinvouchtype,isoutquantity,isoutnum,coutvouchid,coutvouchtype,isredoutquantity,isredoutnum "+
                               "	,cfree1,cfree2,isquantity,ifnum,ifquantity,dvdate,itrids,cposition,cdefine22,cdefine23 "+
                               "	,cdefine24,cdefine25,cdefine26,cdefine27,citem_class,citemcode,cname,citemcname,cfree3,cfree4 "+
                               "	,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10,BarCode,inquantity,innum,cassunit "+
                               "	,dmadedate,imassdate,cdefine28,cdefine29,cdefine30,cdefine31,cdefine32,cdefine33,cdefine34,cdefine35 "+
                               "	,cdefine36,cdefine37,impoids,icheckids,cbvencode,crejectcode,cmassunit,cmolotcode,imaterialfee,dmsdate "+
                               "	,ismaterialfee,iomodid,iomomid,cmworkcentercode,irsrowno,cbaccounter,dbkeepdate,bcosting,bvmiused,ivmisettlequantity "+
                               "	,ivmisettlenum,cvmivencode,iinvsncount,cwhpersoncode,cwhpersonname,imaids,iinvexchrate,corufts,comcode,cmocode "+
                               "	,invcode,imoseq,iopseq,copdesc,iexpiratdatecalcu,cexpirationdate,dexpirationdate,cciqbookcode,ibondedsumqty,productinids "+
                               "	,iorderdid,iordertype,iordercode,iorderseq,isodid,isotype,csocode,isoseq,ipesodid,ipesotype "+
                               "	,cpesocode,ipesoseq,cbatchproperty1,cbatchproperty2,cbatchproperty3,cbatchproperty4,cbatchproperty5,cbatchproperty6,cbatchproperty7,cbatchproperty8 "+
                               "	,cbatchproperty9,cbatchproperty10,cbmemo,applydid,applycode,irowno,strowguid,cservicecode) "+
                               "Values (" + lIDDetail + "," + lID + ",N'" + s存货编码 + "'," + d件数 + "," + d数量 + ",Null,Null,Null,Null,N'" + dtData.Rows[i]["批号"] + "' " +
                               "	,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null "+
                               "	,"+ ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString().Trim()) + ",Null,Null,Null,Null,Null,Null," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + ",Null,Null " +
                               "	,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null "+
                               "	,Null,Null,Null,Null,Null,Null,'"+ dtData.Rows[i]["条形码"].ToString() + "'," + d数量 + "," + d件数 + ",N'" + dt存货信息.Rows[0]["cAssComUnitCode"] + "' " +
                               "	,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null "+
                               "	,Null,Null,"+ ClsBaseDataInfo.ReturnCol(dt生产订单信息.Rows[0]["AllocateId"].ToString()) + ",Null,Null,Null,Null,Null,Null,Null " +
                               "	,Null,Null,Null,Null,Null,Null,Null,1,0,Null "+
                               "	,Null,Null,Null,Null,Null,Null,1,N'13497.1105',Null,N'"+ s订单号 + "' " +
                               "	,N'"+ s产品编码 + "'," + dtData.Rows[i]["行号"].ToString().Trim() + ",N'0000',Null,0,Null,Null,Null,Null,Null " +
                               "	,"+ dt生产订单信息.Rows[0]["Modid"] + ",1,N'" + s订单号 + "',1,Null,0,Null,Null," + dt生产订单信息.Rows[0]["Modid"] + ",7 " +
                               "	,'"+ s订单号 + "'," + dtData.Rows[i]["行号"].ToString().Trim() + ",Null,Null,Null,Null,Null," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["炉号"].ToString().Trim()) + ",Null,Null " +
                               "	,Null,Null,Null,Null,Null,1,Null,Null)";
                        SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                        //更新现存量

                        sSQL = "select * from SCM_Item where cInvCode = '" + s存货编码 + "' and ISNULL(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' ";
                        DataTable dtTemp2 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                        long      lItemID = 0;
                        if (dtTemp2 == null || dtTemp2.Rows.Count == 0)
                        {
                            sSQL = "insert into SCM_Item(cInvCode,cFree1,PartId) " +
                                   "values('" + s存货编码 + "','" + dtData.Rows[i]["长度"].ToString().Trim() + "',0)";
                            SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                            sSQL    = "select * from SCM_Item where cInvCode = '" + s存货编码 + "' and ISNULL(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' ";
                            dtTemp2 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                            lItemID = Convert.ToInt64(dtTemp2.Rows[0]["id"]);
                        }
                        else
                        {
                            lItemID = Convert.ToInt64(dtTemp2.Rows[0]["id"]);
                        }

                        sSQL = "declare @itmeid int " +
                               "select @itmeid = MAX(ItemId) + 1 from CurrentStock " +
                               "if exists(select * from CurrentStock where cinvcode = '" + s存货编码 + "' and isnull(cBatch,'') = isnull(" + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ",'') and cWhCode = '" + s仓库 + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "') " +
                               "    update CurrentStock set iQuantity = isnull(iQuantity,0) - " + ClsBaseDataInfo.ReturnCol(d数量) + ",iNum = isnull(iNum,0) - " + ClsBaseDataInfo.ReturnCol(d件数) + " where cinvcode = '" + s存货编码 + "' and isnull(cBatch,'') = isnull(" + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ",'') and cWhCode = '" + s仓库 + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' " +
                               "else " +
                               "    insert into CurrentStock(cWhCode,cInvCode,cBatch,iSoType,iQuantity,iNum,ItemId,cFree1)  " +
                               "    values('" + s仓库 + "', '" + s存货编码 + "'," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ",0,-" + ClsBaseDataInfo.ReturnCol(d数量) + ",-" + ClsBaseDataInfo.ReturnCol(d件数) + "," + lItemID + "," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ")";
                        SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                        sSQL = "update mom_moallocate set IssQty  = isnull(IssQty ,0) + " + ClsBaseDataInfo.ReturnCol(d数量) + "  where AllocateId = " + ClsBaseDataInfo.ReturnCol(dt生产订单信息.Rows[0]["AllocateId"].ToString());
                        SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                        if (Convert.ToInt32(dtWH.Rows[0]["bWhPos"]) != 0)
                        {
                            //货位登记
                            sSQL = "insert into InvPosition( RdsID, RdID, cWhCode, cPosCode, cInvCode, cBatch, cFree1, cFree2, dVDate, iQuantity" +
                                   ", iNum, cMemo, cHandler, dDate, bRdFlag, cSource, cFree3, cFree4, cFree5, cFree6" +
                                   ", cFree7, cFree8, cFree9, cFree10, cAssUnit, cBVencode, iTrackId,  dMadeDate, iMassDate" +
                                   ", cMassUnit, cvmivencode, iExpiratDateCalcu, cExpirationdate, dExpirationdate, cvouchtype, cInVouchType, cVerifier, dVeriDate, dVouchDate) " +
                                   "values(" + lIDDetail + "," + lID + ",'" + s仓库 + "'," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + ",'" + s存货编码 + "'," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + "," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ",null,null," + ClsBaseDataInfo.ReturnCol(d数量) + " " +
                                   "," + ClsBaseDataInfo.ReturnCol(d件数) + ",null,'" + s制单人 + "','" + d当前服务器时间.ToString("yyyy-MM-dd") + "',0,null,null,null,null,null" +
                                   ",null,null,null,null," + ClsBaseDataInfo.ReturnCol(dt存货信息.Rows[0]["cAssComUnitCode"].ToString()) + ",null,0,null,null" +
                                   ",null,null,0,null,null,'11','',null,null,'" + d当前服务器时间.ToString("yyyy-MM-dd") + "')";
                            SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                            sSQL = "if exists( select * from InvPositionSum where cInvCode = '" + s存货编码 + "' and cWhCode = '" + s仓库 + "' and cPosCode = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + " and ISNULL(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString().Trim() + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "') " +
                                   "   update InvPositionSum set iQuantity = iQuantity - " + ClsBaseDataInfo.ReturnCol(d数量) + ",iNum = iNum - " + ClsBaseDataInfo.ReturnCol(d件数) + " where cInvCode = '" + s存货编码 + "' and cWhCode = '" + s仓库 + "' and cPosCode = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + " and ISNULL(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString().Trim() + "'  and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().ToString() + "' " +
                                   "else " +
                                   "insert InvPositionSum(cWhCode, cPosCode, cInvCode, iQuantity, inum, cBatch, cFree1, cFree2, cFree3" +
                                   " , cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, iTrackid, cvmivencode, cMassUnit" +
                                   ", iMassDate, dMadeDate, dVDate, iExpiratDateCalcu, cExpirationdate, dExpirationdate, cInVouchType) " +
                                   "values(  '" + s仓库 + "', " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + ", '" + s存货编码 + "',  -1 * " + ClsBaseDataInfo.ReturnCol(d数量) + ",  -1 * isnull(" + ClsBaseDataInfo.ReturnCol(d件数) + ",0), " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ", " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ", null, null" +
                                   " , null, null, null, null, null, null, null, 0, null, null" +
                                   ", null, null, null, 0, null, null, null)";
                            SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
                        }
                    }

                    //7. 更新历史单据号表
                    sSQL = "update VoucherHistory set cNumber='" + iCode + "' Where  CardNumber='0412' and cContent is NULL";
                    SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                    //8. 登记未记账
                    sSQL = @"exec IA_SP_WriteUnAccountVouchForST aaaaaa,N'11'";
                    sSQL = sSQL.Replace("aaaaaa", lID.ToString());
                    SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                    //9. 更新单据ID号表
                    string s1 = lID.ToString().Trim();
                    string s2 = lIDDetail.ToString().Trim();
                    s1        = s1.Substring(1);
                    s2        = s2.Substring(1);
                    lID       = Convert.ToInt64(s1);
                    lIDDetail = Convert.ToInt64(s2);
                    sSQL      = "update  UFSystem..UA_Identity set iFatherID = " + lID + ",iChildID = " + lIDDetail + " where cAcc_Id = '" + ClsBaseDataInfo.sUFDataBaseName.Trim().Substring(7, 3) + "' and cVouchType = 'rd'";
                    SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                    if (sErr.Trim().Length > 0)
                    {
                        throw new Exception(sErr);
                    }


                    tran.Commit();
                    s = "生成单据号:" + s单据号;
                }
                catch (Exception error)
                {
                    tran.Rollback();
                    throw new Exception(error.Message);
                }
            }
            catch (Exception ee)
            {
                s = ee.Message;
            }
            return(s);
        }
Beispiel #2
0
        public string Save销售出库单(DataTable dtData)
        {
            string s = "";

            try
            {
                string sErr = "";

                if (dtData == null || dtData.Rows.Count < 1)
                {
                    throw new Exception("没有需要保存的数据");
                }

                SqlConnection conn = new SqlConnection(ClsBaseClass.ClsBaseDataInfo.sConnString);
                conn.Open();
                //启用事务
                SqlTransaction tran = conn.BeginTransaction();

                try
                {
                    string   sSQL     = "select getdate()";
                    DateTime d当前服务器时间 = Convert.ToDateTime(SqlHelper.ExecuteScalar(tran, CommandType.Text, sSQL));

                    //1.  判断是否结账
                    sSQL = "select * from gl_mend where iyear=year(getdate()) and iperiod=month(getdate())";
                    DataTable dtTemp = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                    if (dtTemp == null || dtTemp.Rows.Count < 1)
                    {
                        throw new Exception("判断模块结账失败");
                    }
                    int iR = ClsBaseDataInfo.ReturnObjectToInt(dtTemp.Rows[0]["bflag_ST"]);
                    if (iR == 1)
                    {
                        throw new Exception("当前月份已经结账");
                    }


                    //2. 获得单据ID
                    long      lID       = 1;
                    long      lIDDetail = 1;
                    ClsU8基础档案 cls       = new ClsU8基础档案();
                    cls.GetRdID(out lID, out lIDDetail, ClsBaseDataInfo.sUFDataBaseName.Trim().Substring(7, 3));


                    //3. 获得单据号
                    long iCode = 0;
                    sSQL   = "select * From VoucherHistory  with (ROWLOCK) Where  CardNumber='0303'";
                    dtTemp = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                    if (dtTemp == null || dtTemp.Rows.Count < 1)
                    {
                        iCode = 1;
                    }
                    else
                    {
                        iCode = ClsBaseDataInfo.ReturnObjectToLong(dtTemp.Rows[0]["cNumber"]);
                    }

                    //4. 组装表头
                    lID += 1;
                    string s仓库  = dtData.Rows[0]["仓库"].ToString().Trim();
                    string s订单号 = dtData.Rows[0]["单据号"].ToString().Trim();
                    string s制单人 = dtData.Rows[0]["制单人"].ToString().Trim();
                    //string s产量 = dt生产订单表头.Rows[0]["Qty"].ToString().Trim();
                    //string s产品编码 = dt生产订单表头.Rows[0]["InvCode"].ToString().Trim();
                    //string s生产订单主表标志 = dt生产订单表头.Rows[0]["MoId"].ToString().Trim();
                    iCode += 1;
                    string s单据号 = sGetCode(iCode, 10);

                    sSQL = "select * from DispatchList  where cDLCode  = '" + s订单号 + "' and isnull(bReturnFlag,0) = 0 ";
                    DataTable dt发货单 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                    if (dt发货单 == null || dt发货单.Rows.Count < 1)
                    {
                        throw new Exception("获得发货单信息失败");
                    }

                    sSQL = "insert into rdrecord32(id,brdflag,cvouchtype,cbustype,csource,cbuscode,cwhcode,ddate,ccode,crdcode" +
                           ",cdepcode,cpersoncode,cstcode,ccuscode,cdlcode,cmaker,vt_id,iswfcontrolled,dnmaketime,dnmodifytime" +
                           ",dnverifytime) " +
                           "values (N'" + lID + "',N'0',N'32',N'普通销售',N'发货单',N'" + s订单号 + "',N'" + s仓库 + "','" + d当前服务器时间.ToString("yyyy-MM-dd") + "',N'" + s单据号 + "',N'24'" +
                           ",N'" + dt发货单.Rows[0]["cDepCode"] + "',N'" + dt发货单.Rows[0]["cPersonCode"] + "',N'" + dt发货单.Rows[0]["cstcode"] + "',N'" + dt发货单.Rows[0]["cCusCode"] + "',N'" + dt发货单.Rows[0]["DLID"] + "',N'" + s制单人 + "',87,0, getdate(), Null " +
                           ", Null )";
                    SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                    for (int i = 0; i < dtData.Rows.Count; i++)
                    {
                        //判断条码是否已经使用
                        sSQL = "select * from rdrecords32 where cdefine28 = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["条形码"].ToString()) + " ";
                        int iCou = ClsBaseDataInfo.ReturnObjectToInt(SqlHelper.ExecuteScalar(tran, CommandType.Text, sSQL));
                        if (iCou > 0)
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "已经使用\n";
                            continue;
                        }

                        sSQL = "select * from DispatchList a inner join DispatchLists b on a.DLID = b.DLID where cDLCode = '" + s订单号 + "' and isnull(a.bReturnFlag,0) = 0 and cinvcode = '" + dtData.Rows[i]["存货编码"].ToString().Trim() + "' and isnull(b.cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "'";
                        DataTable dt发货单信息 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                        if (dt发货单信息 == null || dt发货单信息.Rows.Count < 1)
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "获得发货单信息失败\n";
                            continue;
                        }

                        sSQL = "select * from Inventory where cInvCode = '" + dtData.Rows[i]["存货编码"].ToString().Trim() + "'";
                        DataTable dt存货信息 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                        if (dt存货信息 == null || dt存货信息.Rows.Count < 1)
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "获得存货信息失败\n";
                            continue;
                        }

                        int i是否批次 = ClsBaseDataInfo.ReturnObjectToInt(dt存货信息.Rows[0]["bInvBatch"]);
                        if (i是否批次 != 0 && dtData.Rows[i]["批号"].ToString().Trim() == "")
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "是批次管理物料,必须输入批号\n";
                            continue;
                        }
                        int i是否自由项1 = ClsBaseDataInfo.ReturnObjectToInt(dt存货信息.Rows[0]["bFree1"]);
                        if (i是否自由项1 != 0 && dtData.Rows[i]["长度"].ToString().Trim() == "")
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "必须输入长度\n";
                            continue;
                        }

                        //组装表体
                        sSQL = "select * from CurrentStock where cWhCode = '" + dtData.Rows[i]["仓库"].ToString().Trim() + "' and cInvCode = '" + dtData.Rows[i]["存货编码"].ToString().Trim() + "' and isnull(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString().Trim() + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "'   ";
                        DataTable dt现存量 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];



                        lIDDetail += 1;
                        string  s存货编码 = dtData.Rows[i]["存货编码"].ToString().Trim();
                        decimal d数量   = ClsBaseDataInfo.ReturnObjectToDecimal(dtData.Rows[i]["数量"], 6);
                        decimal d件数   = 0;

                        decimal d现存数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt现存量.Rows[0]["iQuantity"], 6);
                        decimal d现存件数 = ClsBaseDataInfo.ReturnObjectToDecimal(dt现存量.Rows[0]["iNum"], 6);

                        decimal d发货单数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt发货单信息.Rows[0]["iQuantity"]);
                        decimal d发货单件数 = ClsBaseDataInfo.ReturnObjectToDecimal(dt发货单信息.Rows[0]["iNum"]);
                        //if (d发货单件数 > 0)
                        //{
                        //    d件数 = ClsBaseDataInfo.ReturnObjectToDecimal(d数量 * d发货单件数 / d发货单数量, 6);
                        //}
                        d件数 = 1;

                        decimal d累计出库数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt发货单信息.Rows[0]["fOutQuantity"]);
                        decimal d累计出库件数 = ClsBaseDataInfo.ReturnObjectToDecimal(dt发货单信息.Rows[0]["fOutNum"]);
                        //if (d发货单数量 - d累计出库数量 < d数量)
                        //{
                        //    sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "超单据发货\n";
                        //    continue;
                        //}

                        sSQL = "Insert Into rdrecords32(autoid,id,cinvcode,inum,iquantity,iunitcost,iprice,ipunitcost,ipprice,cbatch" +
                               ",cvouchcode,cinvouchcode,cinvouchtype,isoutquantity,isoutnum,coutvouchid,coutvouchtype,isredoutquantity,isredoutnum,cfree1" +
                               ",cfree2,dvdate,cposition,cdefine22,cdefine23,cdefine24,cdefine25,cdefine26,cdefine27,citem_class" +
                               ",citemcode,idlsid,isbsid,isendquantity,isendnum,iensid,cname,citemcname,cfree3,cfree4" +
                               ",cfree5,cfree6,cfree7,cfree8,cfree9,cfree10,BarCode,inquantity,innum,cassunit" +
                               ",dmadedate,imassdate,cdefine28,cdefine29,cdefine30,cdefine31,cdefine32,cdefine33,cdefine34,cdefine35" +
                               ",cdefine36,cdefine37,icheckids,cbvencode,bgsp,cgspstate,cmassunit,irefundinspectflag,strcontractid,strcode" +
                               ",bchecked,cbaccounter,dbkeepdate,bcosting,bvmiused,ivmisettlequantity,ivmisettlenum,cvmivencode,iinvsncount,iinvexchrate" +
                               ",cbdlcode,corufts,strcontractguid,iexpiratdatecalcu,cexpirationdate,dexpirationdate,cciqbookcode,ibondedsumqty,ccusinvcode,ccusinvname" +
                               ",iorderdid,iordertype,iordercode,iorderseq,ipesodid,ipesotype,cpesocode,ipesoseq,isodid,isotype" +
                               ",csocode,isoseq,cbatchproperty1,cbatchproperty2,cbatchproperty3,cbatchproperty4,cbatchproperty5,cbatchproperty6,cbatchproperty7,cbatchproperty8" +
                               ",cbatchproperty9,cbatchproperty10,cbmemo,irowno,strowguid) " +
                               "Values (" + lIDDetail + "," + lID + ",N'" + s存货编码 + "'," + d件数 + "," + d数量 + ",Null,Null,Null,Null,N'" + dtData.Rows[i]["批号"] + "'" +
                               ",Null,Null,Null,Null,Null,Null,Null,Null,Null,N'" + dtData.Rows[i]["长度"] + "'" +
                               ",Null,Null,N'" + dtData.Rows[i]["货位"].ToString() + "',null,'" + dt发货单信息.Rows[0]["cdefine23"].ToString().Trim() + "','" + dt发货单信息.Rows[0]["cdefine24"].ToString().Trim() + "','" + dt发货单信息.Rows[0]["cdefine25"].ToString().Trim() + "',Null,Null,Null" +
                               ",Null," + dt发货单信息.Rows[0]["iDLsID"] + ",Null,Null,Null,Null,Null,Null,Null,Null" +
                               ",Null,Null,Null,Null,Null,Null,'" + dtData.Rows[i]["条形码"].ToString() + "'," + dt发货单信息.Rows[0]["iQuantity"] + "," + dt发货单信息.Rows[0]["iNum"] + ",N'" + dt存货信息.Rows[0]["cAssComUnitCode"] + "'" +
                               ",Null,Null,'" + dtData.Rows[i]["条形码"].ToString() + "',Null,Null,Null,Null,Null,Null,Null" +
                               ",Null,Null,Null,Null,Null,Null,Null,Null,Null,Null" +
                               ",Null,Null,Null,1,0,Null,Null,Null,Null,0" +
                               ",N'" + dt发货单信息.Rows[0]["cDLCode"] + "',N'13552.7479',Null,0,Null,Null,Null,Null,Null,Null" +
                               "," + dt发货单信息.Rows[0]["iSOsID"] + ",'1',N'" + dt发货单信息.Rows[0]["cSOCode"] + "'," + dt发货单信息.Rows[0]["iorderrowno"] + ",N'" + dt发货单信息.Rows[0]["iSOsID"] + "',1,N'" + dt发货单信息.Rows[0]["cSOCode"] + "'," + dt发货单信息.Rows[0]["iorderrowno"] + ",Null,0" +
                               ",Null,Null,Null,Null,Null,Null,Null,Null,Null,Null" +
                               ",Null,Null,Null," + dt发货单信息.Rows[0]["irowno"] + ",Null)";
                        SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                        //更新现存量
                        sSQL = "select * from SCM_Item where cInvCode = '" + s存货编码 + "' and ISNULL(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' ";
                        DataTable dtTemp2 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                        long      lItemID = 0;
                        if (dtTemp2 == null || dtTemp2.Rows.Count == 0)
                        {
                            sSQL = "insert into SCM_Item(cInvCode,cFree1,PartId) " +
                                   "values('" + s存货编码 + "','" + dtData.Rows[i]["长度"].ToString().Trim() + "',0)";
                            SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                            sSQL    = "select * from SCM_Item where cInvCode = '" + s存货编码 + "' and ISNULL(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' ";
                            dtTemp2 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                            lItemID = Convert.ToInt64(dtTemp2.Rows[0]["id"]);
                        }
                        else
                        {
                            lItemID = Convert.ToInt64(dtTemp2.Rows[0]["id"]);
                        }

                        sSQL = "declare @itmeid int " +
                               "select @itmeid = MAX(ItemId) + 1 from CurrentStock " +
                               "if exists(select * from CurrentStock where cinvcode = '" + s存货编码 + "' and isnull(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString().Trim() + "' and cWhCode = '" + s仓库 + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "') " +
                               "    update CurrentStock set iQuantity = isnull(iQuantity,0) - " + ClsBaseDataInfo.ReturnCol(d数量) + ",iNum = isnull(iNum,0) - " + ClsBaseDataInfo.ReturnCol(d件数) + " where cinvcode = '" + s存货编码 + "' and isnull(cBatch,'') = isnull(" + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ",'') and cWhCode = '" + s仓库 + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' " +
                               "else " +
                               "    insert into CurrentStock(cWhCode,cInvCode,cBatch,iSoType,iQuantity,iNum,ItemId,cFree1)  " +
                               "    values('" + s仓库 + "', '" + s存货编码 + "'," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ",0,-" + ClsBaseDataInfo.ReturnCol(d数量) + ",-" + ClsBaseDataInfo.ReturnCol(d件数) + "," + lItemID + "," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ")";
                        SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);


                        for (int ii = 0; ii < dt发货单信息.Rows.Count; ii++)
                        {
                            decimal d累计出库 = ClsBaseDataInfo.ReturnObjectToDecimal(dt发货单信息.Rows[ii]["fOutQuantity"]);
                            decimal d出库   = ClsBaseDataInfo.ReturnObjectToDecimal(dt发货单信息.Rows[ii]["iQuantity"]);

                            if (d出库 > d累计出库 || ii == dt发货单信息.Rows.Count - 1)
                            {
                                sSQL = "update DispatchLists set fOutQuantity = isnull(fOutQuantity,0) + " + ClsBaseDataInfo.ReturnCol(d数量) + " ,fOutNum  = isnull(fOutNum ,0) + " + ClsBaseDataInfo.ReturnCol(d件数) + " " +
                                       "where AutoID = " + ClsBaseDataInfo.ReturnCol(dt发货单信息.Rows[ii]["AutoID"].ToString());
                                SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);


                                sSQL = "update DispatchLists set iQuantity = fOutQuantity,iNum  = fOutNum " +
                                       "where AutoID = " + ClsBaseDataInfo.ReturnCol(dt发货单信息.Rows[ii]["AutoID"].ToString());
                                SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                                sSQL = @"
update DispatchLists set iSum = cast(iTaxUnitPrice*iquantity as decimal(16,2)) ,iNatSum = cast(iTaxUnitPrice*iExchRate*iquantity as decimal(16,2))
	,iMoney = cast(iTaxUnitPrice*iExchRate*iquantity as decimal(16,2)),iNatMoney = cast(cast(iTaxUnitPrice*iExchRate *iquantity as decimal(16,2)) / (1 + a.iTaxRate/100) as decimal(16,2))
	,iTax = cast(iTaxUnitPrice*iquantity as decimal(16,2)) -  cast(cast(iTaxUnitPrice * iquantity as decimal(16,2)) / (1 + a.iTaxRate/100) as decimal(16,2)) 
	,iNatTax = cast(iTaxUnitPrice*iExchRate*iquantity as decimal(16,2)) - cast(cast(iTaxUnitPrice*iExchRate *iquantity as decimal(16,2)) / (1 + a.iTaxRate/100) as decimal(16,2))
from DispatchList a 
where a.DLID = DispatchLists.DLID  and DispatchLists.AutoID = 111111
";

                                sSQL = sSQL.Replace("111111", ClsBaseDataInfo.ReturnCol(dt发货单信息.Rows[ii]["AutoID"].ToString()));
                                SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                                //如果不标记,销售出库单就不能手工删除,出现“此单据不是库存生成的,不能删除”的错误提示
                                sSQL = "update DispatchList set cSaleOut = 'ST' where  cDLCode = '" + s订单号 + "'";
                                SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                                break;
                            }
                        }

                        //货位登记
                        sSQL = "insert into InvPosition( RdsID, RdID, cWhCode, cPosCode, cInvCode, cBatch, cFree1, cFree2, dVDate, iQuantity" +
                               ", iNum, cMemo, cHandler, dDate, bRdFlag, cSource, cFree3, cFree4, cFree5, cFree6" +
                               ", cFree7, cFree8, cFree9, cFree10, cAssUnit, cBVencode, iTrackId,  dMadeDate, iMassDate" +
                               ", cMassUnit, cvmivencode, iExpiratDateCalcu, cExpirationdate, dExpirationdate, cvouchtype, cInVouchType, cVerifier, dVeriDate, dVouchDate) " +
                               "values(" + lIDDetail + "," + lID + ",'" + s仓库 + "'," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + ",'" + s存货编码 + "'," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + "," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ",null,null," + ClsBaseDataInfo.ReturnCol(d数量) + " " +
                               "," + ClsBaseDataInfo.ReturnCol(d件数) + ",null,'" + s制单人 + "','" + d当前服务器时间.ToString("yyyy-MM-dd") + "',0,null,null,null,null,null" +
                               ",null,null,null,null," + ClsBaseDataInfo.ReturnCol(dt存货信息.Rows[0]["cAssComUnitCode"].ToString()) + ",null,0,null,null" +
                               ",null,null,0,null,null,'32','',null,null,'" + d当前服务器时间.ToString("yyyy-MM-dd") + "')";
                        SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                        sSQL = "if exists( select * from InvPositionSum where cInvCode = '" + s存货编码 + "' and cWhCode = '" + s仓库 + "' and cPosCode = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + " and ISNULL(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString().Trim() + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "') " +
                               "   update InvPositionSum set iQuantity = iQuantity - " + ClsBaseDataInfo.ReturnCol(d数量) + ",iNum = iNum - " + ClsBaseDataInfo.ReturnCol(d件数) + " where cInvCode = '" + s存货编码 + "' and cWhCode = '" + s仓库 + "' and cPosCode = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + " and ISNULL(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString().Trim() + "'  and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().ToString() + "' " +
                               "else " +
                               "insert InvPositionSum(cWhCode, cPosCode, cInvCode, iQuantity, inum, cBatch, cFree1, cFree2, cFree3" +
                               " , cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, iTrackid, cvmivencode, cMassUnit" +
                               ", iMassDate, dMadeDate, dVDate, iExpiratDateCalcu, cExpirationdate, dExpirationdate, cInVouchType) " +
                               "values(  '" + s仓库 + "', " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + ", '" + s存货编码 + "',  -1 * " + ClsBaseDataInfo.ReturnCol(d数量) + ",  -1 * isnull(" + ClsBaseDataInfo.ReturnCol(d件数) + ",0), " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ", " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ", null, null" +
                               " , null, null, null, null, null, null, null, 0, null, null" +
                               ", null, null, null, 0, null, null, null)";
                        SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
                    }

                    //7. 更新历史单据号表
                    sSQL = "update VoucherHistory set cNumber='" + iCode + "' Where  CardNumber='0303' and cContent is NULL";
                    SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);


                    //8. 更新单据ID号表
                    string s1 = lID.ToString().Trim();
                    string s2 = lIDDetail.ToString().Trim();
                    s1        = s1.Substring(1);
                    s2        = s2.Substring(1);
                    lID       = Convert.ToInt64(s1);
                    lIDDetail = Convert.ToInt64(s2);
                    sSQL      = "update  UFSystem..UA_Identity set iFatherID = " + lID + ",iChildID = " + lIDDetail + " where cAcc_Id = '" + ClsBaseDataInfo.sUFDataBaseName.Trim().Substring(7, 3) + "' and cVouchType = 'rd'";
                    SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                    if (sErr.Trim().Length > 0)
                    {
                        throw new Exception(sErr);
                    }

                    tran.Commit();
                    s = "生成单据号:" + s单据号;
                }
                catch (Exception error)
                {
                    tran.Rollback();
                    throw new Exception(error.Message);
                }
            }
            catch (Exception ee)
            {
                s = ee.Message;
            }
            return(s);
        }
Beispiel #3
0
        public string Save产品入库单(DataTable dtData)
        {
            string s = "";

            try
            {
                string sErr = "";

                if (dtData == null || dtData.Rows.Count < 1)
                {
                    throw new Exception("没有需要保存的数据");
                }

                SqlConnection conn = new SqlConnection(ClsBaseClass.ClsBaseDataInfo.sConnString);
                conn.Open();
                //启用事务
                SqlTransaction tran = conn.BeginTransaction();

                try
                {
                    string   sSQL     = "select getdate()";
                    DateTime d当前服务器时间 = Convert.ToDateTime(SqlHelper.ExecuteScalar(tran, CommandType.Text, sSQL));

                    //1.   判断是否结账
                    sSQL = "select * from gl_mend where iyear=year(getdate()) and iperiod=month(getdate())";
                    DataTable dtTemp = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                    if (dtTemp == null || dtTemp.Rows.Count < 1)
                    {
                        throw new Exception("判断模块结账失败");
                    }
                    int iR = ClsBaseDataInfo.ReturnObjectToInt(dtTemp.Rows[0]["bflag_ST"]);
                    if (iR == 1)
                    {
                        throw new Exception("当前月份已经结账");
                    }


                    //2. 获得单据ID
                    long      lID       = 1;
                    long      lIDDetail = 1;
                    ClsU8基础档案 cls       = new ClsU8基础档案();
                    cls.GetRdID(out lID, out lIDDetail, ClsBaseDataInfo.sUFDataBaseName.Trim().Substring(7, 3));


                    //3. 获得单据号
                    long iCode = 0;
                    sSQL   = "select * From VoucherHistory  with (ROWLOCK) Where  CardNumber='0411'";
                    dtTemp = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                    if (dtTemp == null || dtTemp.Rows.Count < 1)
                    {
                        iCode = 1;
                    }
                    else
                    {
                        iCode = ClsBaseDataInfo.ReturnObjectToLong(dtTemp.Rows[0]["cNumber"]);
                    }


                    sSQL = "select * from mom_order a inner join mom_orderdetail b on a.moid = b.moid where a.mocode = '" + dtData.Rows[0]["单据号"].ToString().Trim() + "' ";
                    DataTable dt生产订单表头 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                    if (dt生产订单表头 == null || dt生产订单表头.Rows.Count < 1)
                    {
                        throw new Exception("获得生产订单信息失败");
                    }

                    //4. 组装表头
                    lID += 1;
                    string s仓库       = dtData.Rows[0]["仓库"].ToString().Trim();
                    string s订单号      = dtData.Rows[0]["单据号"].ToString().Trim();
                    string s行号       = dtData.Rows[0]["行号"].ToString().Trim();
                    string s制单人      = dtData.Rows[0]["制单人"].ToString().Trim();
                    string s生产订单主表标志 = dt生产订单表头.Rows[0]["MOID"].ToString().Trim();
                    iCode += 1;
                    string s单据号 = sGetCode(iCode, 10);

                    sSQL = " insert into rdrecord10(id,brdflag,cvouchtype,cbustype,csource,cwhcode,ddate,ccode,cmaker,vt_id" +
                           ",cmpocode,iproorderid,iswfcontrolled,dnmaketime,dnmodifytime,dnverifytime,cRdCode,cdefine1) " +
                           " values (N'" + lID + "',N'1',N'10',N'成品入库',N'生产订单',N'" + s仓库 + "','" + d当前服务器时间.ToString("yyyy-MM-dd") + "',N'" + s单据号 + "',N'" + s制单人 + "',63" +
                           ",N'" + s订单号 + "',N'" + s生产订单主表标志 + "',0, getdate(), Null , Null,12 ,N'" + s订单号 + "')";
                    SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                    //5. [更新采购订单表头] 暂时未发现需要更新的内容

                    for (int i = 0; i < dtData.Rows.Count; i++)
                    {
                        //判断条码是否已经使用
                        sSQL = "select * from rdrecords10 where BarCode = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["条形码"].ToString()) + " ";
                        int iCou = ClsBaseDataInfo.ReturnObjectToInt(SqlHelper.ExecuteScalar(tran, CommandType.Text, sSQL));
                        if (iCou > 0)
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "已经使用\n";
                            continue;
                        }

                        sSQL = @"
select * ,d.CompleteQty - b.QualifiedInQty   AS QtyUnIn
from mom_order a inner join mom_orderdetail b on a.moid = b.moid 
	LEFT JOIN sfc_moroutingdetail d ON b.MoDId = d.MoDId AND d.LastFlag = 1 
where a.mocode = '" + dtData.Rows[i]["单据号"].ToString().Trim() + "' and b.SortSeq = '" + dtData.Rows[i]["行号"].ToString().Trim() + "'";


                        DataTable dt生产订单 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                        if (dt生产订单 == null || dt生产订单.Rows.Count < 1)
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "获得生产订单信息失败\n";
                            continue;
                        }

                        if (ClsBaseClass.ClsBaseDataInfo.ReturnObjectToDecimal(dt生产订单.Rows[0]["CompleteQty"], 2) == 0 && ClsBaseClass.ClsBaseDataInfo.ReturnObjectToInt(dt生产订单.Rows[0]["MoClass"]) == 1)
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "对应生产订单订单工序未完成\n";
                            continue;
                        }

                        sSQL = "select * from Inventory where cInvCode = '" + dtData.Rows[i]["存货编码"].ToString().Trim() + "'";
                        DataTable dt存货信息 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                        if (dt存货信息 == null || dt存货信息.Rows.Count < 1)
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "获得存货信息失败\n";
                            continue;
                        }

                        int i是否批次 = ClsBaseDataInfo.ReturnObjectToInt(dt存货信息.Rows[0]["bInvBatch"]);
                        if (i是否批次 != 0 && dtData.Rows[i]["批号"].ToString().Trim() == "")
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "是批次管理物料,必须输入批号\n";
                            continue;
                        }
                        int i是否自由项1 = ClsBaseDataInfo.ReturnObjectToInt(dt存货信息.Rows[0]["bFree1"]);
                        if (i是否自由项1 != 0 && dtData.Rows[i]["长度"].ToString().Trim() == "")
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "必须输入长度\n";
                            continue;
                        }

                        //组装表体
                        lIDDetail += 1;
                        string  s存货编码 = dtData.Rows[i]["存货编码"].ToString().Trim();
                        decimal d数量   = ClsBaseDataInfo.ReturnObjectToDecimal(dtData.Rows[i]["数量"], 6);


                        decimal d生产订单数量  = ClsBaseDataInfo.ReturnObjectToDecimal(dt生产订单.Rows[0]["Qty"], 6);
                        decimal d生产订单数件数 = ClsBaseDataInfo.ReturnObjectToDecimal(dt生产订单.Rows[0]["AuxQty"], 6);

                        decimal d累计入库数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt生产订单.Rows[0]["QualifiedInQty"], 6);

                        decimal d件数 = 1;
                        //if (d生产订单数件数 != 0)
                        //{
                        //    d件数 = ClsBaseDataInfo.ReturnObjectToDecimal(d生产订单数件数 * d数量 / d生产订单数量, 6);
                        //}

                        decimal d换算率 = d数量;

                        decimal d入库超额上限 = ClsBaseDataInfo.ReturnObjectToDecimal(dt存货信息.Rows[0]["fInExcess"], 6);
                        //if (d生产订单数量 * (1 + d入库超额上限 / 100) < d累计入库数量 + d数量)
                        //{
                        //    sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "累计入库数量超订单\n";
                        //    continue;
                        //}


                        sSQL = "select * from Warehouse where cWhCode = '" + s仓库 + "'";
                        DataTable dtWH = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];

                        string s销售订单号  = dt生产订单.Rows[0]["OrderCode"].ToString().Trim();
                        string s销售订单行号 = dt生产订单.Rows[0]["OrderSeq"].ToString().Trim();

                        sSQL = "Insert Into rdrecords10(autoid,id,cinvcode,inum,iquantity,iunitcost,iprice,ipunitcost,ipprice,cbatch" +
                               ",cvouchcode,cinvouchcode,cinvouchtype,isoutquantity,isoutnum,cfree1,cfree2,ifnum,ifquantity,dvdate" +
                               ",cposition,cdefine22,cdefine23,cdefine24,cdefine25,cdefine26,cdefine27,citem_class,citemcode,cname" +
                               ",citemcname,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10,BarCode" +
                               ",inquantity,innum,cassunit,dmadedate,imassdate,cdefine28,cdefine29,cdefine30,cdefine31,cdefine32" +
                               ",cdefine33,cdefine34,cdefine35,cdefine36,cdefine37,impoids,icheckids,cbvencode,ccheckcode,icheckidbaks" +
                               ",crejectcode,irejectids,ccheckpersoncode,dcheckdate,cmassunit,cmolotcode,brelated,cmworkcentercode,cbaccounter,dbkeepdate" +
                               ",bcosting,bvmiused,ivmisettlequantity,ivmisettlenum,cvmivencode,iinvsncount,iinvexchrate,corufts,cmocode,imoseq" +
                               ",iopseq,copdesc,iexpiratdatecalcu,cexpirationdate,dexpirationdate,cciqbookcode,ibondedsumqty,productinids,iorderdid,iordertype" +
                               ",iordercode,iorderseq,isodid,isotype,csocode,isoseq,cbatchproperty1,cbatchproperty2,cbatchproperty3,cbatchproperty4" +
                               ",cbatchproperty5,cbatchproperty6,cbatchproperty7,cbatchproperty8,cbatchproperty9,cbatchproperty10,cbmemo,irowno,strowguid,cservicecode) " +
                               "Values (" + lIDDetail + "," + lID + ",N'" + s存货编码 + "'," + ClsBaseDataInfo.ReturnCol(d件数) + "," + ClsBaseDataInfo.ReturnCol(d数量) + ",Null,Null,Null,Null," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + "" +
                               ",Null,Null,Null,Null,Null," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ",Null,Null,Null,Null" +
                               "," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + "," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["条形码"].ToString()) + ",null,null,null,Null,Null,Null,Null,Null" +
                               ",Null,Null,Null,Null,Null,Null,Null,Null,Null," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["条形码"].ToString()) + "" +
                               "," + d生产订单数量 + "," + d生产订单数件数 + ",N'" + dt存货信息.Rows[0]["cAssComUnitCode"].ToString().Trim() + "',Null,Null,Null,Null,Null,Null,Null" +
                               ",Null,Null,Null,Null,Null," + dt生产订单.Rows[0]["Modid"] + ",Null,Null,Null,Null" +
                               ",Null,Null,Null,Null,Null,Null,0,Null,Null,Null" +
                               ",1,Null,Null,Null,Null,Null," + d换算率 + ",N'13548.9432',N'" + dtData.Rows[i]["单据号"].ToString() + "'," + dtData.Rows[i]["行号"].ToString() + "" +
                               ",Null,Null,0,Null,Null,Null,Null,Null," + dt生产订单.Rows[0]["Modid"] + ",1" +
                               ",N'" + s销售订单号 + "','" + s销售订单行号 + "',Null,0,Null,Null,Null,Null,Null,Null" +
                               ",Null," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["炉号"].ToString()) + ",Null,Null,Null,Null,Null," + (i + 1).ToString() + ",Null,Null)";
                        SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                        sSQL = "Update 条形码信息 set  产成品入库单号 = '" + s单据号 + "',产成品入库单行号 = " + (i + 1).ToString() + " where 条形码 = '" + dtData.Rows[i]["条形码"].ToString().Trim() + "'";
                        SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                        //sSQL = "insert into IA_ST_UnAccountVouch10(idun,idsun,cvoutypeun,cbustypeun)values " +
                        //        "('" + lID + "','" + lIDDetail + "','10','成品入库')";
                        //SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                        sSQL = "select * from SCM_Item where cInvCode = '" + s存货编码 + "' and ISNULL(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' ";
                        DataTable dtTemp2 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                        long      lItemID = 0;
                        if (dtTemp2 == null || dtTemp2.Rows.Count == 0)
                        {
                            sSQL = "insert into SCM_Item(cInvCode,cFree1,PartId) " +
                                   "values('" + s存货编码 + "','" + dtData.Rows[i]["长度"].ToString().Trim() + "',0)";
                            SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                            sSQL    = "select * from SCM_Item where cInvCode = '" + s存货编码 + "' and ISNULL(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' ";
                            dtTemp2 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                            lItemID = Convert.ToInt64(dtTemp2.Rows[0]["id"]);
                        }
                        else
                        {
                            lItemID = Convert.ToInt64(dtTemp2.Rows[0]["id"]);
                        }


                        //更新现存量
                        sSQL = "declare @itmeid int " +
                               "select @itmeid = MAX(ItemId) + 1 from CurrentStock " +
                               "if exists(select * from CurrentStock where cinvcode = '" + s存货编码 + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "'  and isnull(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString() + "' and cWhCode = '" + s仓库 + "') " +
                               "    update CurrentStock set iQuantity = isnull(iQuantity,0) + " + ClsBaseDataInfo.ReturnCol(d数量) + ",iNum = isnull(iNum,0) + " + ClsBaseDataInfo.ReturnCol(d件数) + " where cinvcode = '" + s存货编码 + "' and isnull(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString() + "' and cWhCode = '" + s仓库 + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' " +
                               "else " +
                               "    insert into CurrentStock(cWhCode,cInvCode,cBatch,iSoType,iQuantity,iNum,ItemId,cFree1)  " +
                               "    values('" + s仓库 + "', '" + s存货编码 + "'," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ",0," + ClsBaseDataInfo.ReturnCol(d数量) + "," + ClsBaseDataInfo.ReturnCol(d件数) + "," + lItemID + "," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ")";
                        SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);


                        sSQL = "update mom_orderdetail set QualifiedInQty  = isnull(QualifiedInQty ,0) + " + ClsBaseDataInfo.ReturnCol(d数量) + "  where Modid = " + ClsBaseDataInfo.ReturnCol(dt生产订单.Rows[0]["Modid"].ToString());
                        SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                        if (Convert.ToInt32(dtWH.Rows[0]["bWhPos"]) != 0)
                        {
                            //货位登记
                            sSQL = "insert into InvPosition( RdsID, RdID, cWhCode, cPosCode, cInvCode, cBatch, cFree1, cFree2, dVDate, iQuantity" +
                                   ", iNum, cMemo, cHandler, dDate, bRdFlag, cSource, cFree3, cFree4, cFree5, cFree6" +
                                   ", cFree7, cFree8, cFree9, cFree10, cAssUnit, cBVencode, iTrackId,  dMadeDate, iMassDate" +
                                   ", cMassUnit, cvmivencode, iExpiratDateCalcu, cExpirationdate, dExpirationdate, cvouchtype, cInVouchType, cVerifier, dVeriDate, dVouchDate) " +
                                   "values(" + lIDDetail + "," + lID + ",'" + s仓库 + "'," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + ",'" + s存货编码 + "'," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + "," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ",null,null," + ClsBaseDataInfo.ReturnCol(d数量) + " " +
                                   "," + ClsBaseDataInfo.ReturnCol(d件数) + ",null,'" + s制单人 + "','" + d当前服务器时间.ToString("yyyy-MM-dd") + "',1,null,null,null,null,null" +
                                   ",null,null,null,null," + ClsBaseDataInfo.ReturnCol(dt存货信息.Rows[0]["cAssComUnitCode"].ToString()) + ",null,0,null,null" +
                                   ",null,null,0,null,null,'10','',null,null,'" + d当前服务器时间.ToString("yyyy-MM-dd") + "')";
                            SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                            sSQL = "if exists( select * from InvPositionSum where cInvCode = '" + s存货编码 + "' and cWhCode = '" + s仓库 + "' and cPosCode = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + " and ISNULL(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString().Trim() + "' and isnull(cFree1,'') = " + dtData.Rows[i]["长度"].ToString().Trim() + ") " +
                                   "   update InvPositionSum set iQuantity = iQuantity +  " + ClsBaseDataInfo.ReturnCol(d数量) + ",iNum = iNum +  " + ClsBaseDataInfo.ReturnCol(d件数) + " where cInvCode = '" + s存货编码 + "' and cWhCode = '" + s仓库 + "' and cPosCode = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + " and ISNULL(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString().Trim() + "'  and isnull(cFree1,'') = " + dtData.Rows[i]["长度"].ToString().Trim() + " " +
                                   "else " +
                                   "insert InvPositionSum(cWhCode, cPosCode, cInvCode, iQuantity, inum, cBatch, cFree1, cFree2, cFree3" +
                                   " , cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, iTrackid, cvmivencode, cMassUnit" +
                                   ", iMassDate, dMadeDate, dVDate, iExpiratDateCalcu, cExpirationdate, dExpirationdate, cInVouchType) " +
                                   "values(  '" + s仓库 + "', " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + ", '" + s存货编码 + "',  " + ClsBaseDataInfo.ReturnCol(d数量) + ",  " + ClsBaseDataInfo.ReturnCol(d件数) + ", " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ", " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ", null, null" +
                                   " , null, null, null, null, null, null, null, 0, null, null" +
                                   ", null, null, null, 0, null, null, null)";
                            SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
                        }
                    }

                    //更新批次属性档案
                    sSQL = @"

IF Object_id('Tempdb..#STBatchArchief') IS NOT NULL   
	DROP TABLE #STBatchArchief

 select distinct convert(int,1) as autoid , a.cinvcode as cinvcode , isnull(a.cbatch ,'') as cbatch, isnull(a.cfree1,'') as cfree1
	, isnull(a.cfree2,'') as cfree2, isnull(a.cfree3,'') as cfree3, isnull(a.cfree4,'') as cfree4, isnull(a.cfree5,'') as cfree5
	, isnull(a.cfree6,'') as cfree6, isnull(a.cfree7,'') as cfree7, isnull(a.cfree8,'') as cfree8, isnull(a.cfree9,'') as cfree9
	, isnull(a.cfree10,'') as cfree10, a.cBatchProperty1 as cBatchProperty1, a.cBatchProperty2 as cBatchProperty2
	, a.cBatchProperty3 as cBatchProperty3, a.cBatchProperty4 as cBatchProperty4, a.cBatchProperty5 as cBatchProperty5
	, a.cBatchProperty6 as cBatchProperty6, a.cBatchProperty7 as cBatchProperty7, a.cBatchProperty8 as cBatchProperty8
	, a.cBatchProperty9 as cBatchProperty9, a.cBatchProperty10 as cBatchProperty10 
	into #STBatchArchief  
from rdrecords10 a with (nolock)  inner join inventory_sub b   with (nolock)  on a.cinvcode = b.cinvsubcode and isnull(bbatchcreate,0)=1  
where a.id =aaaaaaaaaa and isnull(a.cbatch ,'')<>'' 

 exec Scm_SaveBatchProperty '1','autoid','#STBatchArchief',N'demo'

 drop table #STBatchArchief
 
";
                    sSQL = sSQL.Replace("aaaaaaaaaa", lID.ToString());
                    SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);


                    //7. 更新历史单据号表
                    sSQL = "update VoucherHistory set cNumber='" + iCode + "' Where  CardNumber='0411' and cContent is NULL";
                    SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);


                    //8. 登记未记账
                    sSQL = @"exec IA_SP_WriteUnAccountVouchForST aaaaaa,N'10'";
                    sSQL = sSQL.Replace("aaaaaa", lID.ToString());
                    SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                    //9. 更新单据ID号表
                    string s1 = lID.ToString().Trim();
                    string s2 = lIDDetail.ToString().Trim();
                    s1        = s1.Substring(1);
                    s2        = s2.Substring(1);
                    lID       = Convert.ToInt64(s1);
                    lIDDetail = Convert.ToInt64(s2);
                    sSQL      = "update  UFSystem..UA_Identity set iFatherID = " + lID + ",iChildID = " + lIDDetail + " where cAcc_Id = '" + ClsBaseDataInfo.sUFDataBaseName.Trim().Substring(7, 3) + "' and cVouchType = 'rd'";
                    SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                    if (sErr.Trim().Length > 0)
                    {
                        throw new Exception(sErr);
                    }

                    tran.Commit();
                    s = "生成单据号:" + s单据号;
                }
                catch (Exception error)
                {
                    tran.Rollback();
                    throw new Exception(error.Message);
                }
            }
            catch (Exception ee)
            {
                s = ee.Message;
            }
            return(s);
        }
Beispiel #4
0
        public string Save采购入库单(DataTable dtData)
        {
            string s = "";

            try
            {
                string sErr = "";

                if (dtData == null || dtData.Rows.Count < 1)
                {
                    throw new Exception("没有需要保存的数据");
                }

                SqlConnection conn = new SqlConnection(ClsBaseClass.ClsBaseDataInfo.sConnString);
                conn.Open();
                //启用事务
                SqlTransaction tran = conn.BeginTransaction();

                try
                {
                    string   sSQL     = "select getdate()";
                    DateTime d当前服务器时间 = Convert.ToDateTime(SqlHelper.ExecuteScalar(tran, CommandType.Text, sSQL));

                    //1.   判断是否结账
                    sSQL = "select * from gl_mend where iyear=year(getdate()) and iperiod=month(getdate())";
                    DataTable dtTemp = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                    if (dtTemp == null || dtTemp.Rows.Count < 1)
                    {
                        throw new Exception("判断模块结账失败");
                    }
                    int iR = ClsBaseDataInfo.ReturnObjectToInt(dtTemp.Rows[0]["bflag_ST"]);
                    if (iR == 1)
                    {
                        throw new Exception("当前月份已经结账");
                    }


                    //2. 获得单据ID
                    long      lID       = 1;
                    long      lIDDetail = 1;
                    ClsU8基础档案 cls       = new ClsU8基础档案();
                    cls.GetRdID(out lID, out lIDDetail, ClsBaseDataInfo.sUFDataBaseName.Trim().Substring(7, 3));

                    //3. 获得单据号
                    long iCode = 0;
                    sSQL   = "select * From VoucherHistory  with (ROWLOCK) Where  CardNumber='24'";
                    dtTemp = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                    if (dtTemp == null || dtTemp.Rows.Count < 1)
                    {
                        iCode = 1;
                    }
                    else
                    {
                        iCode = ClsBaseDataInfo.ReturnObjectToLong(dtTemp.Rows[0]["cNumber"]);
                    }


                    sSQL = "select * from PO_Pomain where cPOID = " + dtData.Rows[0]["单据号"] + " ";
                    DataTable dt采购订单 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                    if (dt采购订单 == null || dt采购订单.Rows.Count < 1)
                    {
                        throw new Exception("获得采购订单信息失败");
                    }

                    //4. 组装表头
                    lID += 1;
                    string s仓库       = dtData.Rows[0]["仓库"].ToString().Trim();
                    string s供应商      = dtData.Rows[0]["供应商"].ToString().Trim();
                    string s订单号      = dtData.Rows[0]["单据号"].ToString().Trim();
                    string s制单人      = dtData.Rows[0]["制单人"].ToString().Trim();
                    string s采购订单主表标志 = dt采购订单.Rows[0]["POID"].ToString().Trim();
                    iCode += 1;
                    string s单据号 = sGetCode(iCode, 10);
                    sSQL = "insert into rdrecord01(id,brdflag,cvouchtype,cbustype,csource,cwhcode,ddate,ccode,crdcode,cptcode" +
                           ",cvencode,cordercode,cmaker,bpufirst,vt_id,bisstqc,ipurorderid,itaxrate,iexchrate,cexch_name" +
                           ",bomfirst,idiscounttaxtype,iswfcontrolled,dnmaketime,dnmodifytime,dnverifytime,bredvouch,bcredit) " +
                           "values (N'" + lID + "',N'1',N'01',N'普通采购',N'采购订单',N'" + s仓库 + "','" + d当前服务器时间.ToString("yyyy-MM-dd") + "',N'" + s单据号 + "',N'11',N'01'" +
                           ",N'" + s供应商 + "',N'" + s订单号 + "',N'" + s制单人 + "',0,27,0,N'" + s采购订单主表标志 + "',N'17',1,N'人民币'" +
                           ",0,N'0',0, getdate(), Null , Null ,0,N'0')";
                    SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                    //5. [更新采购订单表头] 暂时未发现需要更新的内容

                    for (int i = 0; i < dtData.Rows.Count; i++)
                    {
                        //判断条码是否已经使用
                        sSQL = "select * from rdrecords01 where BarCode = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["条形码"].ToString()) + " ";
                        int iCou = ClsBaseDataInfo.ReturnObjectToInt(SqlHelper.ExecuteScalar(tran, CommandType.Text, sSQL));
                        if (iCou > 0)
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "已经使用\n";
                            continue;
                        }


                        sSQL = "select * from PO_Pomain a inner join PO_Podetails b on a.POID = b.POID where a.cPOID = '" + dtData.Rows[i]["单据号"] + "' and b.cInvCode = '" + dtData.Rows[i]["存货编码"].ToString().Trim() + "' order by b.ID";
                        DataTable dt采购订单信息 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                        if (dt采购订单信息 == null || dt采购订单信息.Rows.Count < 1)
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "获得采购订单信息失败\n";
                            continue;
                        }

                        sSQL = "select * from Inventory where cInvCode = '" + dtData.Rows[i]["存货编码"].ToString().Trim() + "'";
                        DataTable dt存货信息 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                        if (dt存货信息 == null || dt存货信息.Rows.Count < 1)
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "获得存货信息失败\n";
                            continue;
                        }

                        int i是否批次 = ClsBaseDataInfo.ReturnObjectToInt(dt存货信息.Rows[0]["bInvBatch"]);
                        if (i是否批次 != 0 && dtData.Rows[i]["批号"].ToString().Trim() == "")
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "是批次管理物料,必须输入批号\n";
                            continue;
                        }
                        int i是否自由项1 = ClsBaseDataInfo.ReturnObjectToInt(dt存货信息.Rows[0]["bFree1"]);
                        if (i是否自由项1 != 0 && dtData.Rows[i]["长度"].ToString().Trim() == "")
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "必须输入长度\n";
                            continue;
                        }

                        //组装表体
                        lIDDetail += 1;
                        string  s存货编码 = dtData.Rows[i]["存货编码"].ToString().Trim();
                        decimal d数量   = ClsBaseDataInfo.ReturnObjectToDecimal(dtData.Rows[i]["数量"], 6);
                        decimal d税率   = ClsBaseDataInfo.ReturnObjectToDecimal(dt采购订单信息.Rows[0]["iPerTaxRate"], 6);

                        decimal d采购订单数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt采购订单信息.Rows[0]["iQuantity"], 6);
                        decimal d采购订单件数 = ClsBaseDataInfo.ReturnObjectToDecimal(dt采购订单信息.Rows[0]["iNum"], 6);

                        decimal d累计入库数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt采购订单信息.Rows[0]["iReceivedQTY"], 6);
                        decimal d累计入库件数 = ClsBaseDataInfo.ReturnObjectToDecimal(dt采购订单信息.Rows[0]["iReceivedNum"], 6);

                        decimal d件数 = 0;
                        if (d采购订单件数 != 0)
                        {
                            d件数 = ClsBaseDataInfo.ReturnObjectToDecimal(d采购订单件数 * d数量 / d采购订单数量, 6);
                        }

                        decimal d入库超额上限 = ClsBaseDataInfo.ReturnObjectToDecimal(dt存货信息.Rows[0]["fInExcess"], 6);
                        if (d采购订单数量 * (1 + d入库超额上限 / 100) < d累计入库数量 + d数量 || d采购订单件数 * (1 + d入库超额上限 / 100) < d累计入库件数 + d件数)
                        {
                            sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "累计入库数量超订单\n";
                            continue;
                        }

                        decimal d含税单价 = ClsBaseDataInfo.ReturnObjectToDecimal(dt采购订单信息.Rows[0]["iTaxPrice"], 6);
                        decimal d含税金额 = ClsBaseDataInfo.ReturnObjectToDecimal(d含税单价 * d数量, 2);
                        decimal d无税金额 = ClsBaseDataInfo.ReturnObjectToDecimal(d含税金额 / (1 + d税率 / 100), 2);
                        decimal d税额   = d含税金额 - d无税金额;
                        decimal d无税单价 = ClsBaseDataInfo.ReturnObjectToDecimal(d无税金额 / d数量, 3);

                        sSQL = "Insert Into rdrecords01(autoid,id,cinvcode,inum,iquantity,iunitcost,iprice,iaprice,ipunitcost,ipprice" +
                               ",cbatch,cvouchcode,cinvouchcode,cinvouchtype,isoutquantity,isoutnum,cfree1,cfree2,dsdate,itax" +
                               ",isquantity,isnum,imoney,ifnum,ifquantity,dvdate,cposition,cdefine22,cdefine23,cdefine24" +
                               ",cdefine25,cdefine26,cdefine27,citem_class,citemcode,iposid,facost,cname,citemcname,cfree3" +
                               ",cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10,BarCode,inquantity,innum" +
                               ",cassunit,dmadedate,imassdate,cdefine28,cdefine29,cdefine30,cdefine31,cdefine32,cdefine33,cdefine34" +
                               ",cdefine35,cdefine36,cdefine37,icheckids,cbvencode,cgspstate,iarrsid,ccheckcode,icheckidbaks,crejectcode" +
                               ",irejectids,ccheckpersoncode,dcheckdate,ioritaxcost,ioricost,iorimoney,ioritaxprice,iorisum,itaxrate,itaxprice" +
                               ",isum,btaxcost,cpoid,cmassunit,imaterialfee,iprocesscost,iprocessfee,dmsdate,ismaterialfee,isprocessfee" +
                               ",iomodid,strcontractid,strcode,cbaccounter,dbkeepdate,bcosting,isumbillquantity,bvmiused,ivmisettlequantity,ivmisettlenum" +
                               ",cvmivencode,iinvsncount,impcost,iimosid,iimbsid,cbarvcode,dbarvdate,iinvexchrate,corufts,iexpiratdatecalcu" +
                               ",cexpirationdate,dexpirationdate,cciqbookcode,ibondedsumqty,iordertype,iorderdid,iordercode,iorderseq,isodid,isotype" +
                               ",csocode,isoseq,cbatchproperty1,cbatchproperty2,cbatchproperty3,cbatchproperty4,cbatchproperty5,cbatchproperty6,cbatchproperty7,cbatchproperty8" +
                               ",cbatchproperty9,cbatchproperty10,cbmemo,ifaqty,istax,irowno,strowguid) " +
                               "Values (" + lIDDetail + "," + lID + ",N'" + s存货编码 + "'," + ClsBaseDataInfo.ReturnCol(d件数) + "," + ClsBaseDataInfo.ReturnCol(d数量) + "," + ClsBaseDataInfo.ReturnCol(d无税单价) + "," + ClsBaseDataInfo.ReturnCol(d无税金额) + "," + ClsBaseDataInfo.ReturnCol(d无税金额) + ",Null,Null" +
                               "," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ",Null,Null,Null,Null,Null," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ",Null,Null,Null" +
                               ",0,0,0,Null,Null,Null," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + " ," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["条形码"].ToString()) + ",Null,Null" +
                               ",Null,Null,Null,Null,Null," + ClsBaseDataInfo.ReturnCol(dt采购订单信息.Rows[0]["ID"].ToString()) + "," + ClsBaseDataInfo.ReturnCol(d无税单价) + ",Null,Null,Null" +
                               ",Null,Null,Null,Null,Null,Null,Null," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["条形码"].ToString()) + "," + ClsBaseDataInfo.ReturnCol(d数量) + "," + ClsBaseDataInfo.ReturnCol(d件数) + "" +
                               ",Null,Null,Null,Null,Null,Null,Null,Null,Null,Null" +
                               ",Null,Null,Null,Null,Null,Null,Null,Null,Null,Null" +
                               ",Null,Null,Null," + ClsBaseDataInfo.ReturnCol(d含税单价) + "," + ClsBaseDataInfo.ReturnCol(d无税单价) + "," + ClsBaseDataInfo.ReturnCol(d无税金额) + "," + ClsBaseDataInfo.ReturnCol(d税额) + "," + ClsBaseDataInfo.ReturnCol(d含税金额) + "," + ClsBaseDataInfo.ReturnCol(d税率) + "," + ClsBaseDataInfo.ReturnCol(d税额) + "" +
                               "," + ClsBaseDataInfo.ReturnCol(d含税金额) + ",0," + dtData.Rows[i]["单据号"].ToString() + ",Null,Null,Null,Null,Null,Null,Null" +
                               ",Null,Null,Null,Null,Null,1,Null,Null,Null,Null" +
                               ",Null,Null,Null,Null,Null,Null,Null,Null,N'13502.0346',0" +
                               ",Null,Null,Null,Null,Null,Null,Null,Null,Null,0" +
                               ",Null,Null,Null,Null,Null,Null,Null," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["炉号"].ToString()) + ",Null,Null" +
                               ",Null,Null,Null,Null,Null," + ClsBaseDataInfo.ReturnCol(dt采购订单信息.Rows[0]["irowno"].ToString()) + ",Null)";
                        SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                        //更新现存量

                        sSQL = "select * from SCM_Item where cInvCode = '" + s存货编码 + "' and ISNULL(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' ";
                        DataTable dtTemp2 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                        long      lItemID = 0;
                        if (dtTemp2 == null || dtTemp2.Rows.Count == 0)
                        {
                            sSQL = "insert into SCM_Item(cInvCode,cFree1,PartId) " +
                                   "values('" + s存货编码 + "','" + dtData.Rows[i]["长度"].ToString().Trim() + "',0)";
                            SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                            sSQL    = "select * from SCM_Item where cInvCode = '" + s存货编码 + "' and ISNULL(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' ";
                            dtTemp2 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                            lItemID = Convert.ToInt64(dtTemp2.Rows[0]["id"]);
                        }
                        else
                        {
                            lItemID = Convert.ToInt64(dtTemp2.Rows[0]["id"]);
                        }

                        sSQL = "declare @itmeid int " +
                               "select @itmeid = MAX(ItemId) + 1 from CurrentStock " +
                               "if exists(select * from CurrentStock where cinvcode = '" + s存货编码 + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "'  and isnull(cBatch,'') = isnull(" + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ",'') and cWhCode = '" + s仓库 + "') " +
                               "    update CurrentStock set iQuantity = isnull(iQuantity,0) + " + ClsBaseDataInfo.ReturnCol(d数量) + ",iNum = isnull(iNum,0) + " + ClsBaseDataInfo.ReturnCol(d件数) + " where cinvcode = '" + s存货编码 + "' and isnull(cBatch,'') = isnull(" + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ",'') and cWhCode = '" + s仓库 + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' " +
                               "else " +
                               "    insert into CurrentStock(cWhCode,cInvCode,cBatch,iSoType,iQuantity,iNum,ItemId,cFree1)  " +
                               "    values('" + s仓库 + "', '" + s存货编码 + "','" + dtData.Rows[i]["批号"].ToString() + "',0," + ClsBaseDataInfo.ReturnCol(d数量) + "," + ClsBaseDataInfo.ReturnCol(d件数) + "," + lItemID + "," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ")";
                        SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);


                        sSQL = "update Po_Podetails set iReceivedQTY = isnull(iReceivedQTY,0) + " + ClsBaseDataInfo.ReturnCol(d数量) + " ,iReceivedNum = isnull(iReceivedNum,0) + " + ClsBaseDataInfo.ReturnCol(d件数) + ",iReceivedMoney = isnull(iReceivedMoney,0) + " + ClsBaseDataInfo.ReturnCol(d含税金额) + " " +
                               "where ID = " + ClsBaseDataInfo.ReturnCol(dt采购订单信息.Rows[0]["ID"].ToString());
                        SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);


                        //货位登记
                        sSQL = "insert into InvPosition( RdsID, RdID, cWhCode, cPosCode, cInvCode, cBatch, cFree1, cFree2, dVDate, iQuantity" +
                               ", iNum, cMemo, cHandler, dDate, bRdFlag, cSource, cFree3, cFree4, cFree5, cFree6" +
                               ", cFree7, cFree8, cFree9, cFree10, cAssUnit, cBVencode, iTrackId,  dMadeDate, iMassDate" +
                               ", cMassUnit, cvmivencode, iExpiratDateCalcu, cExpirationdate, dExpirationdate, cvouchtype, cInVouchType, cVerifier, dVeriDate, dVouchDate) " +
                               "values(" + lIDDetail + "," + lID + ",'" + s仓库 + "'," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + ",'" + s存货编码 + "'," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + "," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ",null,null," + ClsBaseDataInfo.ReturnCol(d数量) + " " +
                               "," + ClsBaseDataInfo.ReturnCol(d件数) + ",null,'" + s制单人 + "','" + d当前服务器时间.ToString("yyyy-MM-dd") + "',1,null,null,null,null,null" +
                               ",null,null,null,null," + ClsBaseDataInfo.ReturnCol(dt存货信息.Rows[0]["cAssComUnitCode"].ToString()) + ",null,0,null,null" +
                               ",null,null,0,null,null,'01','',null,null,'" + d当前服务器时间.ToString("yyyy-MM-dd") + "')";
                        SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                        sSQL = "if exists( select * from InvPositionSum where cInvCode = '" + s存货编码 + "' and cWhCode = '" + s仓库 + "' and cPosCode = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + " and ISNULL(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString().Trim() + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "') " +
                               "   update InvPositionSum set iQuantity = iQuantity +  " + ClsBaseDataInfo.ReturnCol(d数量) + ",iNum = iNum +  " + ClsBaseDataInfo.ReturnCol(d件数) + " where cInvCode = '" + s存货编码 + "' and cWhCode = '" + s仓库 + "' and cPosCode = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + " and ISNULL(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString().Trim() + "'  and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' " +
                               "else " +
                               "insert InvPositionSum(cWhCode, cPosCode, cInvCode, iQuantity, inum, cBatch, cFree1, cFree2, cFree3" +
                               " , cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, iTrackid, cvmivencode, cMassUnit" +
                               ", iMassDate, dMadeDate, dVDate, iExpiratDateCalcu, cExpirationdate, dExpirationdate, cInVouchType) " +
                               "values(  '" + s仓库 + "', " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + ", '" + s存货编码 + "',  " + ClsBaseDataInfo.ReturnCol(d数量) + ",  " + ClsBaseDataInfo.ReturnCol(d件数) + ", " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ", " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ", null, null" +
                               " , null, null, null, null, null, null, null, 0, null, null" +
                               ", null, null, null, 0, null, null, null)";
                        SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
                    }

                    //7. 更新历史单据号表

                    sSQL = "update VoucherHistory set cNumber='" + iCode + "' Where  CardNumber='24' and cContent is NULL";
                    SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);


                    //8. 更新单据ID号表

                    string s1 = lID.ToString().Trim();
                    string s2 = lIDDetail.ToString().Trim();
                    s1        = s1.Substring(1);
                    s2        = s2.Substring(1);
                    lID       = Convert.ToInt64(s1);
                    lIDDetail = Convert.ToInt64(s2);
                    sSQL      = "update  UFSystem..UA_Identity set iFatherID = " + lID + ",iChildID = " + lIDDetail + " where cAcc_Id = '" + ClsBaseDataInfo.sUFDataBaseName.Trim().Substring(7, 3) + "' and cVouchType = 'rd'";
                    SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);

                    if (sErr.Trim().Length > 0)
                    {
                        throw new Exception(sErr);
                    }

                    tran.Commit();
                    s = "生成单据号:" + s单据号;
                }
                catch (Exception error)
                {
                    tran.Rollback();
                    throw new Exception(error.Message);
                }
            }
            catch (Exception ee)
            {
                s = ee.Message;
            }
            return(s);
        }