Пример #1
0
        /// <summary>
        /// 上传POS机支付和销售流水数据
        /// </summary>
        /// <param name="connectString">数据库连接字符串</param>
        /// <param name="isok">成功true失败false</param>
        /// <param name="payTable">支付流水数据</param>
        /// <param name="saleTable">销售流水数据</param>
        /// <returns></returns>
        public object UploadFlow(string connectString, ref bool isok, DataTable payTable, DataTable saleTable)
        {
            object obj = null;

            try
            {
                isok = DbUtilityMySql.Instance.TestConnect(connectString); //检查数据库连接
                if (isok)                                                  //连接成功
                {
                    string sql          = string.Empty;
                    string errorMessage = string.Empty;
                    List <SqlParaEntity> _sqlParaList = new List <SqlParaEntity>();
                    #region 支付流水
                    sql = "insert into t_pos_payflow(flow_id,flow_no,sale_amount,pay_way,pay_amount,coin_type,pay_name,coin_rate,convert_amt,card_no,memo,oper_date,oper_id,voucher_no,branch_no,pos_id,sale_way,com_flag)" +
                          "   values(@flow_id,@flow_no,@sale_amount,@pay_way ,@pay_amount,@coin_type,@pay_name,@coin_rate,@convert_amt,@card_no,@memo,@oper_date,@oper_id,@voucher_no,@branch_no,@pos_id,@sale_way,@com_flag)";
                    foreach (DataRow dr in payTable.Rows)
                    {
                        MySqlParameter[] parameters = new MySqlParameter[18];//vip_no,@vip_no
                        #region 参数定义
                        parameters[0]        = new MySqlParameter("@flow_id", MySqlDbType.Int64);
                        parameters[0].Value  = ExtendUtility.Instance.ParseToInt64(dr["flow_id"]);
                        parameters[1]        = new MySqlParameter("@flow_no", MySqlDbType.VarChar);
                        parameters[1].Value  = ExtendUtility.Instance.ParseToString(dr["flow_no"]);
                        parameters[2]        = new MySqlParameter("@sale_amount", MySqlDbType.Decimal);
                        parameters[2].Value  = ExtendUtility.Instance.ParseToDecimal(dr["sale_amount"]);
                        parameters[3]        = new MySqlParameter("@pay_way", MySqlDbType.VarChar);
                        parameters[3].Value  = ExtendUtility.Instance.ParseToString(dr["pay_way"]);
                        parameters[4]        = new MySqlParameter("@pay_amount", MySqlDbType.Decimal);
                        parameters[4].Value  = ExtendUtility.Instance.ParseToDecimal(dr["pay_amount"]);
                        parameters[5]        = new MySqlParameter("@coin_type", MySqlDbType.VarChar);
                        parameters[5].Value  = ExtendUtility.Instance.ParseToDecimal(dr["coin_type"]);
                        parameters[6]        = new MySqlParameter("@pay_name", MySqlDbType.VarChar);
                        parameters[6].Value  = ExtendUtility.Instance.ParseToString(dr["pay_name"]);
                        parameters[7]        = new MySqlParameter("@coin_rate", MySqlDbType.Decimal);
                        parameters[7].Value  = ExtendUtility.Instance.ParseToDecimal(dr["coin_rate"]);
                        parameters[8]        = new MySqlParameter("@convert_amt", MySqlDbType.Decimal);
                        parameters[8].Value  = ExtendUtility.Instance.ParseToDecimal(dr["convert_amt"]);
                        parameters[9]        = new MySqlParameter("@card_no", MySqlDbType.VarChar);
                        parameters[9].Value  = ExtendUtility.Instance.ParseToString(dr["card_no"]);
                        parameters[10]       = new MySqlParameter("@memo", MySqlDbType.VarChar);
                        parameters[10].Value = ExtendUtility.Instance.ParseToString(dr["memo"]);
                        //parameters[11] = new MySqlParameter("@vip_no", MySqlDbType.VarChar);
                        //parameters[11].Value = ExtendUtility.Instance.ParseToString(dr["vip_no"]);
                        parameters[11]       = new MySqlParameter("@oper_date", MySqlDbType.DateTime);
                        parameters[11].Value = ExtendUtility.Instance.ParseToDateTime(dr["oper_date"]);
                        parameters[12]       = new MySqlParameter("@oper_id", MySqlDbType.VarChar);
                        parameters[12].Value = ExtendUtility.Instance.ParseToString(dr["oper_id"]);
                        parameters[13]       = new MySqlParameter("@voucher_no", MySqlDbType.VarChar);
                        parameters[13].Value = ExtendUtility.Instance.ParseToString(dr["voucher_no"]);
                        parameters[14]       = new MySqlParameter("@branch_no", MySqlDbType.VarChar);
                        parameters[14].Value = ExtendUtility.Instance.ParseToString(dr["branch_no"]);
                        parameters[15]       = new MySqlParameter("@pos_id", MySqlDbType.VarChar);
                        parameters[15].Value = ExtendUtility.Instance.ParseToString(dr["pos_id"]);
                        parameters[16]       = new MySqlParameter("@sale_way", MySqlDbType.VarChar);
                        parameters[16].Value = ExtendUtility.Instance.ParseToString(dr["sale_way"]);
                        parameters[17]       = new MySqlParameter("@com_flag", MySqlDbType.VarChar);
                        parameters[17].Value = "1";

                        #endregion
                        SqlParaEntity _sqlPara = new SqlParaEntity()
                        {
                            Sql        = sql,
                            parameters = parameters
                        };
                        _sqlParaList.Add(_sqlPara);
                    }
                    #endregion
                    #region 销售流水
                    sql = "insert into t_pos_saleflow(flow_id,flow_no,item_no,sale_price,sale_qnty,sale_money,in_price,sell_way,discount_rate,oper_id,oper_date,reasonid,branch_no,pos_id,com_flag,unit_price,plan_no,item_clsno,item_brand)" +  //item_subno,item_clsno,item_name,item_status,item_subname,
                          "   values(@flow_id,@flow_no,@item_no,@sale_price,@sale_qnty,@sale_money,@in_price,@sell_way,@discount_rate,@oper_id,@oper_date,@reasonid,@branch_no,@pos_id,@com_flag,@unit_price,@plan_no,@item_clsno,@item_brand)"; //@item_subno,@item_clsno,@item_name,@item_status,@item_subname,

                    foreach (DataRow dr in saleTable.Rows)
                    {
                        MySqlParameter[] parameters = new MySqlParameter[19];//in_price,@in_price,unit_price,@unit_price,
                        #region 参数定义
                        parameters[0]        = new MySqlParameter("@flow_id", MySqlDbType.Int64);
                        parameters[0].Value  = ExtendUtility.Instance.ParseToInt64(dr["flow_id"]);
                        parameters[1]        = new MySqlParameter("@flow_no", MySqlDbType.VarChar);
                        parameters[1].Value  = ExtendUtility.Instance.ParseToString(dr["flow_no"]);
                        parameters[2]        = new MySqlParameter("@item_no", MySqlDbType.VarChar);
                        parameters[2].Value  = ExtendUtility.Instance.ParseToString(dr["item_no"]);
                        parameters[3]        = new MySqlParameter("@sale_price", MySqlDbType.Decimal);
                        parameters[3].Value  = ExtendUtility.Instance.ParseToDecimal(dr["unit_price"]);
                        parameters[4]        = new MySqlParameter("@sale_qnty", MySqlDbType.Decimal);
                        parameters[4].Value  = ExtendUtility.Instance.ParseToDecimal(dr["sale_qnty"]);
                        parameters[5]        = new MySqlParameter("@sale_money", MySqlDbType.Decimal);
                        parameters[5].Value  = ExtendUtility.Instance.ParseToDecimal(dr["sale_money"]);
                        parameters[6]        = new MySqlParameter("@in_price", MySqlDbType.Decimal);
                        parameters[6].Value  = ExtendUtility.Instance.ParseToDecimal(dr["price"]);
                        parameters[7]        = new MySqlParameter("@sell_way", MySqlDbType.VarChar);
                        parameters[7].Value  = ExtendUtility.Instance.ParseToString(dr["sale_way"]);
                        parameters[8]        = new MySqlParameter("@discount_rate", MySqlDbType.Decimal);
                        parameters[8].Value  = ExtendUtility.Instance.ParseToDecimal(dr["discount_rate"]);
                        parameters[9]        = new MySqlParameter("@oper_id", MySqlDbType.VarChar);
                        parameters[9].Value  = ExtendUtility.Instance.ParseToString(dr["oper_id"]);
                        parameters[10]       = new MySqlParameter("@oper_date", MySqlDbType.DateTime);
                        parameters[10].Value = ExtendUtility.Instance.ParseToDateTime(dr["oper_date"]);
                        parameters[11]       = new MySqlParameter("@reasonid", MySqlDbType.Int32);
                        parameters[11].Value = ExtendUtility.Instance.ParseToInt32(dr["reasonid"]);
                        parameters[12]       = new MySqlParameter("@branch_no", MySqlDbType.VarChar);
                        parameters[12].Value = ExtendUtility.Instance.ParseToString(dr["branch_no"]);
                        parameters[13]       = new MySqlParameter("@pos_id", MySqlDbType.VarChar);
                        parameters[13].Value = ExtendUtility.Instance.ParseToString(dr["pos_id"]);
                        parameters[14]       = new MySqlParameter("@com_flag", MySqlDbType.VarChar);
                        parameters[14].Value = "1";
                        parameters[15]       = new MySqlParameter("@unit_price", MySqlDbType.Decimal);
                        parameters[15].Value = ExtendUtility.Instance.ParseToInt32(dr["sale_price"]);
                        parameters[16]       = new MySqlParameter("@plan_no", MySqlDbType.VarChar);
                        parameters[16].Value = ExtendUtility.Instance.ParseToString(dr["plan_no"]);
                        parameters[17]       = new MySqlParameter("@item_clsno", MySqlDbType.VarChar);
                        parameters[17].Value = ExtendUtility.Instance.ParseToString(dr["item_clsno"]);
                        parameters[18]       = new MySqlParameter("@item_brand", MySqlDbType.VarChar);
                        parameters[18].Value = ExtendUtility.Instance.ParseToString(dr["item_brand"]);
                        #endregion
                        SqlParaEntity _sqlPara = new SqlParaEntity()
                        {
                            Sql        = sql,
                            parameters = parameters
                        };
                        _sqlParaList.Add(_sqlPara);
                    }
                    #endregion
                    if (DbUtilityMySql.Instance.ExecuteSqlsByTrans(connectString, _sqlParaList, ref errorMessage) > 0)
                    {
                        obj = true;
                    }
                    else
                    {
                        obj = false;
                    }
                }
            }
            catch (Exception ex)
            {
                LoggerHelper.Log("MsmkLogger", "LL.POS.Common->PosUploadServiceProvider-->UploadFlow--->" + ex.ToString(), LogEnum.ExceptionLog);
                obj  = null;
                isok = false;
            }
            return(obj);
        }
Пример #2
0
        /// <summary>
        /// 新增or更新单据
        /// </summary>
        /// <param name="connectString">数据库连接字符串</param>
        /// <param name="isok">成功true否则false</param>
        /// <param name="sheet_no">单据编码</param>
        /// <param name="trans_no">单据类型</param>
        /// <param name="branch_no">发货分店</param>
        /// <param name="d_branch_no">要货分店</param>
        /// <param name="oper_id">操作员</param>
        /// <param name="valid_date">有限期限</param>
        /// <param name="sheet_amt">单据金额</param>
        /// <param name="details">明细数据</param>
        /// <param name="memo">备注</param>
        /// <returns>成功true否则false</returns>
        public bool AddOrUpdateSheet(string connectString, ref bool isok, ref string sheet_no, string trans_no, string branch_no, string d_branch_no, string oper_id, string valid_date, decimal sheet_amt,
                                     DataTable details, string memo)
        {
            bool   isok1 = true;
            string sql   = string.Empty;
            List <SqlParaEntity> _sqlParaList = null;
            string errorMessage = string.Empty;

            try
            {
                _sqlParaList = new List <SqlParaEntity>();
                if (trans_no == "YH")
                {
                    if (sheet_no.Length > 0)//编辑操作
                    {
                        sql = "update t_pm_sheet_master set branch_no=@branch_no,d_branch_no=@d_branch_no,valid_date=@valid_date,oper_id=@oper_id,sheet_amt=@sheet_amt,oper_date=@oper_date,memo=@memo" +
                              "   where sheet_no=@sheet_no ";
                        MySqlParameter[] parameters = new MySqlParameter[8];
                        #region 准备操作主表的参数
                        parameters[0]       = new MySqlParameter("@sheet_no", MySqlDbType.VarChar);
                        parameters[0].Value = sheet_no;
                        parameters[1]       = new MySqlParameter("@branch_no", MySqlDbType.VarChar);
                        parameters[1].Value = branch_no;
                        parameters[2]       = new MySqlParameter("@d_branch_no", MySqlDbType.VarChar);
                        parameters[2].Value = d_branch_no;
                        parameters[3]       = new MySqlParameter("@valid_date", MySqlDbType.VarChar);
                        parameters[3].Value = valid_date;
                        parameters[4]       = new MySqlParameter("@oper_id", MySqlDbType.VarChar);
                        parameters[4].Value = oper_id;
                        parameters[5]       = new MySqlParameter("@sheet_amt", MySqlDbType.Decimal);
                        parameters[5].Value = sheet_amt;
                        parameters[6]       = new MySqlParameter("@oper_date", MySqlDbType.VarChar);
                        parameters[6].Value = System.DateTime.Now.ToString();
                        parameters[7]       = new MySqlParameter("@memo", MySqlDbType.VarChar);
                        parameters[7].Value = memo;
                        #endregion
                        SqlParaEntity _sqlPara = new SqlParaEntity();
                        _sqlPara.Sql        = sql;
                        _sqlPara.parameters = parameters;
                        _sqlParaList.Add(_sqlPara);
                        sql                 = "delete  from  t_pm_sheet_detail where sheet_no=@sheet_no";
                        parameters          = new MySqlParameter[1];
                        parameters[0]       = new MySqlParameter("@sheet_no", MySqlDbType.VarChar);
                        parameters[0].Value = sheet_no;
                        _sqlPara            = new SqlParaEntity();
                        _sqlPara.Sql        = sql;
                        _sqlPara.parameters = parameters;
                        _sqlParaList.Add(_sqlPara);
                    }
                    else//新增操作
                    {
                        sheet_no = GetSheetNo(connectString, trans_no, d_branch_no);
                        if (sheet_no.Length > 0)
                        {
                            sql = "insert into t_pm_sheet_master(sheet_no,trans_no,branch_no,d_branch_no,valid_date,oper_id,sheet_amt,oper_date,memo)" +
                                  "   values(@sheet_no,@trans_no,@branch_no,@d_branch_no,@valid_date,@oper_id,@sheet_amt,@oper_date,@memo)";
                            MySqlParameter[] parameters = new MySqlParameter[9];
                            #region 准备操作主表的参数
                            parameters[0]       = new MySqlParameter("@sheet_no", MySqlDbType.VarChar);
                            parameters[0].Value = sheet_no;
                            parameters[1]       = new MySqlParameter("@trans_no", MySqlDbType.VarChar);
                            parameters[1].Value = trans_no;
                            parameters[2]       = new MySqlParameter("@branch_no", MySqlDbType.VarChar);
                            parameters[2].Value = branch_no;
                            parameters[3]       = new MySqlParameter("@d_branch_no", MySqlDbType.VarChar);
                            parameters[3].Value = d_branch_no;
                            parameters[4]       = new MySqlParameter("@valid_date", MySqlDbType.VarChar);
                            parameters[4].Value = valid_date;
                            parameters[5]       = new MySqlParameter("@oper_id", MySqlDbType.VarChar);
                            parameters[5].Value = oper_id;
                            parameters[6]       = new MySqlParameter("@sheet_amt", MySqlDbType.Decimal);
                            parameters[6].Value = sheet_amt;
                            parameters[7]       = new MySqlParameter("@oper_date", MySqlDbType.VarChar);
                            parameters[7].Value = System.DateTime.Now.ToString();
                            parameters[8]       = new MySqlParameter("@memo", MySqlDbType.VarChar);
                            parameters[8].Value = memo;
                            #endregion
                            SqlParaEntity _sqlPara = new SqlParaEntity();
                            _sqlPara.Sql        = sql;
                            _sqlPara.parameters = parameters;
                            _sqlParaList.Add(_sqlPara);

                            sql = "";
                        }
                        else
                        {
                            isok1 = false;
                        }
                    }
                    #region 插入明细数据
                    if (isok1)
                    {
                        sql = "insert into t_pm_sheet_detail(sheet_no,item_no,real_qty,other1) values(@sheet_no,@item_no,@real_qty,@other1)";
                        foreach (DataRow dr in details.Rows)
                        {
                            MySqlParameter[] parameters = new MySqlParameter[4];
                            parameters[0]       = new MySqlParameter("@sheet_no", MySqlDbType.VarChar);
                            parameters[0].Value = sheet_no;
                            parameters[1]       = new MySqlParameter("@item_no", MySqlDbType.VarChar);
                            parameters[1].Value = ExtendUtility.Instance.ParseToString(dr["item_no"]);
                            parameters[2]       = new MySqlParameter("@real_qty", MySqlDbType.Decimal);
                            parameters[2].Value = ExtendUtility.Instance.ParseToDecimal(dr["real_qty"]);
                            parameters[3]       = new MySqlParameter("@other1", MySqlDbType.VarChar);
                            parameters[3].Value = ExtendUtility.Instance.ParseToString(dr["other1"]);
                            SqlParaEntity _sqlPara = new SqlParaEntity();
                            _sqlPara.Sql        = sql;
                            _sqlPara.parameters = parameters;
                            _sqlParaList.Add(_sqlPara);
                        }
                        isok1 = DbUtilityMySql.Instance.ExecuteSqlsByTrans(connectString, _sqlParaList, ref errorMessage) > 0 ? true : false;
                    }
                    #endregion
                }
            }
            catch (Exception ex)
            {
                isok1 = false;
                LoggerHelper.Log("MsmkLogger", "LL.POS.Common->PosUploadServiceProvider-->GetSheetNo--->" + ex.ToString(), LogEnum.ExceptionLog);
            }
            return(isok1);
        }