/// <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>
/// 执行服务方法
/// </summary>
/// <param name="connectString">数据库连接字符串</param>
/// <param name="mark">执行方法返回的信息</param>
/// <param name="isok">成功true否则false</param>
/// <param name="branch_no">门店编号</param>
/// <param name="item_no">商品编号</param>
/// <param name="order_qty">商品数量</param>
/// <param name="db_no">出售/赠送商品(-)退货商品(+)</param>
/// <returns>执行服务返回的数据</returns>
public object InvokeService(string connectString, string mark, ref bool isok, string branch_no, string item_no,
decimal order_qty, string db_no)
{
object obj = null;
try
{
isok = DbUtilityMySql.Instance.TestConnect(connectString); //检查数据库连接
if (isok) //连接成功
{
string sql = string.Empty;
string errorMessage = string.Empty;
switch (mark)
{
case "GetBranchList": //门店信息
sql = "select * from t_pos_branch_info";
obj = DbUtilityMySql.Instance.GetDataTable(connectString, sql, null, ref errorMessage);
break;
case "GetItemClsList": //商品分类信息
sql = "select * from t_bd_item_cls";
obj = DbUtilityMySql.Instance.GetDataTable(connectString, sql, null, ref errorMessage);
break;
case "GetBarCodeList": //BarCode列表信息
sql = "select * from t_bd_item_barcode";
obj = DbUtilityMySql.Instance.GetDataTable(connectString, sql, null, ref errorMessage);
break;
case "GetItemList": //商品信息
sql = "select * from t_bd_item_info";
obj = DbUtilityMySql.Instance.GetDataTable(connectString, sql, null, ref errorMessage);
break;
case "GetBaseCodeType": //基础代码分类信息
sql = "select * from t_bd_basecode_type";
obj = DbUtilityMySql.Instance.GetDataTable(connectString, sql, null, ref errorMessage);
break;
case "GetBaseCodeInfo": //基础代码
sql = "select * from t_bd_base_code";
obj = DbUtilityMySql.Instance.GetDataTable(connectString, sql, null, ref errorMessage);
break;
case "GetCashierList": //操作员信息
sql = "select * from t_pos_operator";
obj = DbUtilityMySql.Instance.GetDataTable(connectString, sql, null, ref errorMessage);
break;
case "GetItemStock": //获取门店库存信息
sql = "select s.branch_no,s.item_no,a.item_name,a.item_size,s.stock_qty,b.item_clsname,c.code_name from t_pos_branch_stock s" +
" left join t_bd_item_info as a on s.item_no=a.item_no" +
" left join t_bd_item_cls as b on a.item_clsno=b.item_clsno" +
" left join t_bd_base_code as c on a.item_brand=c.code_id" +
" where s.branch_no='{0}' and c.type_no='PP'";
sql = string.Format(sql, branch_no);
obj = DbUtilityMySql.Instance.GetDataTable(connectString, sql, null, ref errorMessage);
break;
case "UpdateItemStock": //更新库存
if (db_no == "-")
{
sql = "update t_pos_branch_stock set stock_qty=stock_qty-{0},oper_date='{3}' where branch_no='{1}' and item_no='{2}'";
}
else
{
sql = "update t_pos_branch_stock set stock_qty=stock_qty+{0},oper_date='{3}' where branch_no='{1}' and item_no='{2}'";
}
sql = string.Format(sql, order_qty, branch_no, item_no, System.DateTime.Now.ToString());
obj = DbUtilityMySql.Instance.ExecuteSql(connectString, sql, null, ref errorMessage);
break;
case "GetPaymentInfo": //支付方式
sql = "select * from t_bd_payment_info";
obj = DbUtilityMySql.Instance.GetDataTable(connectString, sql, null, ref errorMessage);
break;
case "GetFunction": //快捷键
sql = "select * from t_pos_function where branch_no='{0}'";
sql = string.Format(sql, branch_no);
obj = DbUtilityMySql.Instance.GetDataTable(connectString, sql, null, ref errorMessage);
break;
case "GetPosStatus": //获取Pos状态
sql = "select * from t_pos_status where branch_no='{0}'";
sql = string.Format(sql, branch_no);
obj = DbUtilityMySql.Instance.GetDataTable(connectString, sql, null, ref errorMessage);
break;
//case "GetPosSet"://POS机配置
// break;
case "GetItemPrice": //获取门店商品售价
sql = "select branch_no,item_no,price,sale_price,vip_price from t_pc_branch_price where branch_no='{0}'";
sql = string.Format(sql, branch_no);
obj = DbUtilityMySql.Instance.GetDataTable(connectString, sql, null, ref errorMessage);
break;
case "GetPlanRule":
sql = "select rule_no,range_flag,rule_describe,rule_condition,rule_result,plu_flag from t_pos_plan_rule";
obj = DbUtilityMySql.Instance.GetDataTable(connectString, sql, null, ref errorMessage);
break;
case "GetPlanMaster":
sql = "select m.plan_no,m.plan_name,m.plan_memo,m.begin_date,m.end_date," +
" m.week,m.vip_type,m.oper_date,m.oper_man,m.confirm_date," +
" m.confirm_man,m.stop_date,m.stop_man,m.approve_flag,m.rule_no,m.range_flag " +
" from t_pos_plan_master m " +
" left join t_pos_plan_branch b on m.plan_no=b.plan_no" +
" where b.branch_no='{0}' and m.approve_flag='1'";
sql = string.Format(sql, branch_no);
obj = DbUtilityMySql.Instance.GetDataTable(connectString, sql, null, ref errorMessage);
break;
case "GetPlanDetail":
sql = "select d.plan_no,d.row_id,d.rule_para,d.rule_val " +
" from t_pos_plan_detail d" +
" left join t_pos_plan_branch b on d.plan_no=b.plan_no " +
" where b.branch_no='{0}'";
sql = string.Format(sql, branch_no);
obj = DbUtilityMySql.Instance.GetDataTable(connectString, sql, null, ref errorMessage);
break;
default:
break;
}
}
}
catch (Exception ex)
{
LoggerHelper.Log("MsmkLogger", "LL.POS.Common->PosDownServiceProvider-->InvokeService--->" + mark + ":" + 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);
}