Ejemplo n.º 1
0
        public static string StorageReturnUpdate(XBase.Model.Office.StorageManager.StorageReturn sr, List <XBase.Model.Office.StorageManager.StorageReturnDetail> srdList, Hashtable ht)
        {
            // return "1|";
            //定义返回变量
            string res = string.Empty;

            /*
             * 定义日志内容变量
             * 增删改相关的日志,需要输出操作日志,该类型日志插入到数据库
             * 其他的 如出现异常时,需要输出系统日志,该类型日志保存到日志文件
             */
            //获取当前用户信息
            UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];

            //执行操作
            try
            {
                //执行操作
                res = XBase.Data.Office.StorageManager.StorageReturnDBHelper.StorageReturnUpdate(sr, srdList, ht);
            }
            catch (Exception ex)
            {
                //输出日志
                WriteSystemLog(userInfo, ex);
            }
            //定义变量
            string remark;

            //成功时
            if (res.Split('|')[0] == "1")
            {
                //设置操作成功标识
                remark = ConstUtil.LOG_PROCESS_SUCCESS;
            }
            else
            {
                //设置操作成功标识
                remark = ConstUtil.LOG_PROCESS_FAILED;
            }
            //操作日志
            LogInfoModel logModel = InitLogInfo(sr.ReturnNo);

            //涉及关键元素 这个需要根据每个页面具体设置,本页面暂时设置为空
            logModel.Element = ConstUtil.LOG_PROCESS_UPDATE;
            //设置操作成功标识
            logModel.Remark = remark;

            //登陆日志
            LogDBHelper.InsertLog(logModel);
            return(res);
        }
        public static string StorageReturnAdd(StorageReturn sr, List<StorageReturnDetail> srdList, Hashtable ht)
        {

            if (!StorageReturnDetail(sr.ReturnNo, sr.CompanyCD))
                return "1|该借货单货单据编号已经存在";

            #region 构造字符串
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into officedba.StorageReturn(");
            strSql.Append("CompanyCD,ReturnNo,Title,FromType,FromBillID,StorageID,DeptID,ReturnPerson,ReturnDate,TotalPrice,CountTotal,Summary,Transactor,Remark,Creator,CreateDate,BillStatus)");
            strSql.Append(" values (");
            strSql.Append("@CompanyCD,@ReturnNo,@Title,@FromType,@FromBillID,@StorageID,@DeptID,@ReturnPerson,@ReturnDate,@TotalPrice,@CountTotal,@Summary,@Transactor,@Remark,@Creator,@CreateDate,@BillStatus)");
            strSql.Append(";select @ID=@@IDENTITY ");
            SqlParameter[] parameters = {
					new SqlParameter("@CompanyCD", SqlDbType.VarChar,8),
					new SqlParameter("@ReturnNo", SqlDbType.VarChar,50),
					new SqlParameter("@Title", SqlDbType.VarChar,100),
					new SqlParameter("@FromType", SqlDbType.Char,1),
					new SqlParameter("@FromBillID", SqlDbType.Int,4),
					new SqlParameter("@StorageID", SqlDbType.Int,4),
					new SqlParameter("@DeptID", SqlDbType.Int,4),
					new SqlParameter("@ReturnPerson", SqlDbType.Int,4),
					new SqlParameter("@ReturnDate", SqlDbType.DateTime),
					new SqlParameter("@TotalPrice", SqlDbType.Decimal,9),
					new SqlParameter("@CountTotal", SqlDbType.Decimal,9),
					new SqlParameter("@Summary", SqlDbType.VarChar,200),
					new SqlParameter("@Transactor", SqlDbType.Int,4),
					new SqlParameter("@Remark", SqlDbType.VarChar,800),
                    new SqlParameter("@Creator",SqlDbType.Int),
					new SqlParameter("@CreateDate", SqlDbType.DateTime),
					new SqlParameter("@BillStatus", SqlDbType.Char,1),
                    new SqlParameter("@ID",SqlDbType.Int)};
            parameters[0].Value = sr.CompanyCD;
            parameters[1].Value = sr.ReturnNo;
            parameters[2].Value = sr.Title;
            parameters[3].Value = sr.FromType;
            parameters[4].Value = sr.FromBillID;
            parameters[5].Value = sr.StorageID;
            parameters[6].Value = sr.DeptID;
            parameters[7].Value = sr.ReturnPerson;
            parameters[8].Value = sr.ReturnDate;
            parameters[9].Value = sr.TotalPrice;
            parameters[10].Value = sr.CountTotal;
            parameters[11].Value = sr.Summary;
            parameters[12].Value = sr.Transactor;
            parameters[13].Value = sr.Remark;
            parameters[14].Value = sr.Creator;
            parameters[15].Value = sr.CreateDate;
            parameters[16].Value = sr.BillStatus;
            parameters[17].Direction = ParameterDirection.Output;
            #endregion

            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.Parameters.AddRange(parameters);
            sqlCmd.CommandText = strSql.ToString();
            ArrayList SqlCmdList = new ArrayList();
            SqlCmdList.Add(sqlCmd);

            #region 拓展属性
            SqlCommand cmd = new SqlCommand();
            GetExtAttrCmd(sr, ht, cmd);
            if (ht.Count > 0)
                SqlCmdList.Add(cmd);
            #endregion

            #region 构造明细字符串
            if (srdList != null && srdList.Count > 0)
            {
                foreach (StorageReturnDetail model in srdList)
                {
                    #region 构造字符串
                    StringBuilder strSqlDetail = new StringBuilder();
                    strSqlDetail.Append("insert into officedba.StorageReturnDetail(");
                    strSqlDetail.Append("CompanyCD,ReturnNo,SortNo,ProductID,ProductName,UnitID,ProductCount,ReturnCount,UnitPrice,TotalPrice,FromType,FromBillID,FromLineNo,Remark,UsedUnitID,UsedUnitCount,UsedPrice,ExRate,BatchNo)");
                    strSqlDetail.Append(" values (");
                    strSqlDetail.Append("@CompanyCD,@ReturnNo,@SortNo,@ProductID,@ProductName,@UnitID,@ProductCount,@ReturnCount,@UnitPrice,@TotalPrice,@FromType,@FromBillID,@FromLineNo,@Remark,@UsedUnitID,@UsedUnitCount,@UsedPrice,@ExRate,@BatchNo)");
                    strSql.Append(";select @ID= @@IDENTITY");
                    SqlParameter[] paras = {
					new SqlParameter("@CompanyCD", SqlDbType.VarChar,8),
					new SqlParameter("@ReturnNo", SqlDbType.VarChar,50),
					new SqlParameter("@SortNo", SqlDbType.Int,4),
					new SqlParameter("@ProductID", SqlDbType.Int,4),
					new SqlParameter("@ProductName", SqlDbType.VarChar,100),
					new SqlParameter("@UnitID", SqlDbType.Int,4),
					new SqlParameter("@ProductCount", SqlDbType.Decimal,9),
					new SqlParameter("@ReturnCount", SqlDbType.Decimal,9),
					new SqlParameter("@UnitPrice", SqlDbType.Decimal,9),
					new SqlParameter("@TotalPrice", SqlDbType.Decimal,9),
					new SqlParameter("@FromType", SqlDbType.Char,1),
					new SqlParameter("@FromBillID", SqlDbType.Int,4),
					new SqlParameter("@FromLineNo", SqlDbType.Int,4),
					new SqlParameter("@Remark", SqlDbType.VarChar,100),
                    new SqlParameter("@UsedUnitID", SqlDbType.Int,4),
					new SqlParameter("@UsedUnitCount", SqlDbType.Decimal,9),
					new SqlParameter("@UsedPrice", SqlDbType.Decimal,9),
					new SqlParameter("@ExRate", SqlDbType.Decimal,9),
                    new SqlParameter("@ID",SqlDbType.Int),
                    new SqlParameter("@BatchNo",SqlDbType.VarChar)

                                           };
                    paras[0].Value = model.CompanyCD;
                    paras[1].Value = model.ReturnNo;
                    paras[2].Value = model.SortNo;
                    paras[3].Value = model.ProductID;
                    paras[4].Value = model.ProductName;
                    if (model.UnitID != null)
                        paras[5].Value = model.UnitID;
                    else
                        paras[5].Value = DBNull.Value;
                    paras[6].Value = model.ProductCount;
                    paras[7].Value = model.ReturnCount;
                    paras[8].Value = model.UnitPrice;
                    paras[9].Value = model.TotalPrice;
                    paras[10].Value = model.FromType;
                    paras[11].Value = model.FromBillID;
                    paras[12].Value = model.FromLineNo;
                    paras[13].Value = model.Remark;
                    paras[14].Value = model.UsedUnitID;
                    paras[15].Value = model.UsedUnitCount;
                    paras[16].Value = model.UsedPrice;
                    paras[17].Value = model.ExRate;
                    paras[18].Direction = ParameterDirection.Output;
                    paras[19].Value = model.BatchNo;
                    #endregion

                    SqlCommand sqlCmdDetail = new SqlCommand();
                    sqlCmdDetail.CommandText = strSqlDetail.ToString();
                    sqlCmdDetail.Parameters.AddRange(paras);
                    SqlCmdList.Add(sqlCmdDetail);
                }
            }
            #endregion

            bool result = SqlHelper.ExecuteTransWithArrayList(SqlCmdList);
            if (result)
            {
                string ID = string.Empty;

                ID = ((SqlCommand)SqlCmdList[0]).Parameters["@ID"].Value.ToString();
                return "2|" + ID + "#" + sr.ReturnNo;

            }
            else
                return "3|保存数据失败";
        }
        /// <summary>
        /// 扩展属性保存操作
        /// </summary>
        /// <returns></returns>
        private static void GetExtAttrCmd(StorageReturn model, Hashtable htExtAttr, SqlCommand cmd)
        {
            try
            {
                string strSql = string.Empty;

                strSql = "UPDATE officedba.StorageReturn set ";
                foreach (DictionaryEntry de in htExtAttr)// fileht为一个Hashtable实例
                {
                    strSql += de.Key.ToString().Trim() + "=@" + de.Key.ToString().Trim() + ",";
                    cmd.Parameters.AddWithValue("@" + de.Key.ToString().Trim(), de.Value.ToString().Trim());
                }
                int iLength = strSql.Length - 1;
                strSql = strSql.Substring(0, iLength);
                strSql += " where CompanyCD = @CompanyCD  AND ReturnNo = @ReturnNo";
                cmd.Parameters.AddWithValue("@CompanyCD", model.CompanyCD);
                cmd.Parameters.AddWithValue("@ReturnNo", model.ReturnNo);
                cmd.CommandText = strSql;
            }
            catch (Exception)
            { }


        }
        public static bool UpdateStatus(StorageReturn sr, string stype)
        {
            /***********************************
             * stype 1:确认 2:结单 3:取消结单
             * *********************************/
            StringBuilder sbUpdate = new StringBuilder();
            sbUpdate.Append("UPDATE officedba.StorageReturn SET ");
            if (stype == "1") //确认
            {

                #region 构造更新状态SQL
                sbUpdate.Append(" BillStatus=@BillStatus ,Confirmor=@Confirmor,ConfirmDate=@ConfirmDate ");
                sbUpdate.Append(", ModifiedDate=@ModifiedDate,ModifiedUserID=@ModifiedUserID ");
                sbUpdate.Append(" WHERE ID=@ID");

                SqlParameter[] Paras ={
                                         new SqlParameter("@BillStatus",SqlDbType.VarChar),
                                         new SqlParameter("@Confirmor",SqlDbType.Int),
                                         new SqlParameter("@ConfirmDate",SqlDbType.DateTime),
                                         new SqlParameter("@ID",SqlDbType.Int),
                                         new SqlParameter("@ModifiedDate",SqlDbType.DateTime),
                                         new SqlParameter("@ModifiedUserID",SqlDbType.VarChar)
                                     };
                Paras[0].Value = sr.BillStatus;
                Paras[1].Value = sr.Confirmor;
                Paras[2].Value = sr.ConfirmDate;
                Paras[3].Value = sr.ID;
                Paras[4].Value = sr.ModifiedDate;
                Paras[5].Value = sr.ModifiedUserID;

                #endregion


                ArrayList SqlCmdList = new ArrayList();
                SqlCommand srSqlCmd = new SqlCommand();
                srSqlCmd.CommandText = sbUpdate.ToString();
                srSqlCmd.Parameters.AddRange(Paras);
                SqlCmdList.Add(srSqlCmd);


                #region 构造 业务逻辑 实现SQL
                StringBuilder sbSql = new StringBuilder();
                sbSql.Append("SELECT srd.*,(SELECT sr.StorageID FROM officedba.StorageReturn as sr WHERE sr.ID=@ID ) AS StorageID   FROM officedba.StorageReturnDetail as srd ");
                sbSql.Append(" WHERE srd.CompanyCD=@CompanyCD AND srd.ReturnNo=@ReturnNo");
                SqlParameter[] sPara = { 
                                           new SqlParameter("@ID",SqlDbType.Int),
                                           new SqlParameter("@CompanyCD",SqlDbType.VarChar),
                                           new SqlParameter("@ReturnNo",SqlDbType.VarChar),
                                       };
                sPara[0].Value = sr.ID;
                sPara[1].Value = sr.CompanyCD;
                sPara[2].Value = sr.ReturnNo;


                DataTable dt = SqlHelper.ExecuteSql(sbSql.ToString(), sPara);




                if (dt != null && dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {

                        bool isBatchNo = false;
                        if (row["BatchNo"] != null || row["BatchNo"].ToString().ToLower() != "nobatch" || row["BatchNo"].ToString() != "")
                            isBatchNo = true;


                        /*更新被借仓库 更新 现有存量(现有库存不变)+ 借出量- */
                        StringBuilder SubSql = new StringBuilder();
                        SubSql.Append("UPDATE officedba.StorageProduct SET  OutCount=isnull(OutCount,0)-@ProductCount ");
                        SubSql.Append(" WHERE CompanyCD=@CompanyCD AND StorageID=@StorageID AND ProductID=@ProductID " + (isBatchNo ? "AND BatchNo=@BatchNo" : string.Empty));

                        SqlParameter[] subSqlParams = isBatchNo ? new SqlParameter[5] : new SqlParameter[4];
                        int index = 0;
                        subSqlParams[index++] = SqlHelper.GetParameter("@ProductCount", row["ReturnCount"].ToString());
                        subSqlParams[index++] = SqlHelper.GetParameter("@CompanyCD", row["CompanyCD"].ToString());
                        subSqlParams[index++] = SqlHelper.GetParameter("@StorageID", row["StorageID"].ToString());
                        subSqlParams[index++] = SqlHelper.GetParameter("@ProductID", row["ProductID"].ToString());
                        if (isBatchNo)
                            subSqlParams[index++] = SqlHelper.GetParameter("@BatchNo", row["BatchNo"].ToString());

                        SqlCommand subSqlCmd = new SqlCommand();
                        subSqlCmd.CommandText = SubSql.ToString();
                        subSqlCmd.Parameters.AddRange(subSqlParams);
                        SqlCmdList.Add(subSqlCmd);
                        /*更新借货单明细中的 已返还数量+*/
                        StringBuilder sbUpdateBorrowDetail = new StringBuilder();
                        sbUpdateBorrowDetail.Append(" UPDATE officedba.StorageBorrowDetail set RealReturnCount=isnull(RealReturnCount,0)+@ReturnCount ");
                        sbUpdateBorrowDetail.Append(" WHERE CompanyCD=@CompanyCD AND SortNo=@SortNo AND BorrowNo=(SELECT sb.BorrowNo FROM officedba.StorageBorrow as sb WHERE sb.ID=@BorrowID )");
                        SqlParameter[] updateSqlParas = { 
                                                        new SqlParameter("@CompanyCD",SqlDbType.VarChar),
                                                        new SqlParameter("@SortNo",SqlDbType.Int),
                                                        new SqlParameter("@BorrowID",SqlDbType.Int),
                                                        new SqlParameter("@ReturnCount",SqlDbType.Decimal)};
                        updateSqlParas[0].Value = row["CompanyCD"].ToString();
                        updateSqlParas[1].Value = row["FromLineNo"].ToString();
                        updateSqlParas[2].Value = row["FromBillID"].ToString();
                        updateSqlParas[3].Value = row["ReturnCount"].ToString();

                        SqlCmdList.Add(SqlHelper.GetNewSqlCommond(sbUpdateBorrowDetail.ToString(), updateSqlParas));

                    }


                    bool result = SqlHelper.ExecuteTransWithArrayList(SqlCmdList);
                    return result;

                }
                else
                    return false;
                #endregion
            }
            else if (stype == "2") //结单
            {
                sbUpdate.Append("BillStatus=@BillStatus,Closer=@Closer,CloseDate=@CloseDate ");
                sbUpdate.Append(", ModifiedDate=@ModifiedDate,ModifiedUserID=@ModifiedUserID ");
                sbUpdate.Append(" WHERE ID=@ID ");
                SqlParameter[] Paras = { 
                                           new SqlParameter("@BillStatus",SqlDbType.VarChar),
                                           new SqlParameter("@Closer",SqlDbType.Int),
                                           new SqlParameter("@CloseDate",SqlDbType.DateTime),
                                           new SqlParameter("@ModifiedDate",SqlDbType.DateTime),
                                           new SqlParameter("@ModifiedUserID",SqlDbType.VarChar),
                                           new SqlParameter("@ID",SqlDbType.Int)
                                       };
                Paras[0].Value = sr.BillStatus;
                Paras[1].Value = sr.Closer;
                Paras[2].Value = sr.CloseDate;
                Paras[3].Value = sr.ModifiedDate;
                Paras[4].Value = sr.ModifiedUserID;
                Paras[5].Value = sr.ID;
                SqlCommand sqlcmd = new SqlCommand();
                sqlcmd.Parameters.AddRange(Paras);
                sqlcmd.CommandText = sbUpdate.ToString();
                ArrayList sqlcmdlist = new ArrayList();
                sqlcmdlist.Add(sqlcmd);
                return SqlHelper.ExecuteTransWithArrayList(sqlcmdlist);
            }
            else if (stype == "3") //取消结单
            {
                sbUpdate.Append("BillStatus=@BillStatus, ModifiedDate=@ModifiedDate,ModifiedUserID=@ModifiedUserID ");
                sbUpdate.Append(" WHERE ID=@ID");
                SqlParameter[] Paras = { 
                                      new SqlParameter("@BillStatus",SqlDbType.VarChar),
                                      new  SqlParameter("@ModifiedDate",SqlDbType.DateTime),
                                      new SqlParameter("@ModifiedUserID",SqlDbType.VarChar),
                                       new SqlParameter("@ID",SqlDbType.Int)};
                Paras[0].Value = sr.BillStatus;
                Paras[1].Value = sr.ModifiedDate;
                Paras[2].Value = sr.ModifiedUserID;
                Paras[3].Value = sr.ID;

                SqlCommand sqlcmd = new SqlCommand();
                sqlcmd.Parameters.AddRange(Paras);
                sqlcmd.CommandText = sbUpdate.ToString();
                ArrayList sqlcmdlist = new ArrayList();
                sqlcmdlist.Add(sqlcmd);
                return SqlHelper.ExecuteTransWithArrayList(sqlcmdlist);
            }
            else
                return false;

        }