/// <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);
}
/// <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);
}