public DataSet QueryNewLine(ISession session, DateTime start, DateTime end, string po, string barCode, int pageIndex, int pageSize, bool fetch, ref int count)
{
DbSession dbsession = session.DbSession as DbSession;
StringBuilder sql = new StringBuilder();
IDbCommand cmd = dbsession.CreateSqlStringCommand("");
sql.Append(@"
Select pol.Ord_Num As PONumber,pol.line_num As POLine
,sku.itm_barcode As BarCode,i.itm_code As ItemCode,i.itm_name As ItemName,sku.color_code As ColorCode,sku.size_code As SizeCode
,color.Name As ColorText
,pol.rcv_qty As Qty,Rownum As rowindex
,sku.sku_id as SKUID
From ord_pur_line pol
Inner Join ord_pur_head poh On pol.Ord_Num=poh.ord_num
Inner Join prd_item_sku sku On sku.sku_id=pol.sku_id
Inner Join prd_item i On i.itm_code=sku.itm_code
Left Join prd_item_color color On color.code=sku.color_code
Where poh.ven_id=:venId And poh.loc_code=:locCode And pol.rcv_qty>0
and poh.ord_status in (2,3) and poh.aprv_rslt=1 and pol.line_status in (1,3)");
dbsession.AddParameter(cmd, ":venId", DbTypeInfo.Int32(), this.VendorID);
dbsession.AddParameter(cmd, ":locCode", DbTypeInfo.AnsiString(8), this.LocationCode);
if (start > new DateTime(1900, 1, 1))
{
sql.Append(" And poh.create_time>=:startDate");
dbsession.AddParameter(cmd, ":startDate", DbTypeInfo.Date(), start);
}
if (end > new DateTime(1900, 1, 1))
{
sql.Append(" And poh.create_time<:endDate");
dbsession.AddParameter(cmd, ":endDate", DbTypeInfo.Date(), end.AddDays(1));
}
if (!string.IsNullOrEmpty(po) && po.Trim().Length > 0)
{
sql.Append(" And poh.ord_num=:po");
dbsession.AddParameter(cmd, ":po", DbTypeInfo.AnsiString(16), po.Trim().ToUpper());
}
if (!string.IsNullOrEmpty(barCode) && barCode.Trim().Length > 0)
{
sql.Append(" And sku.itm_barcode Like :barCode");
dbsession.AddParameter(cmd, ":barCode", DbTypeInfo.AnsiString(20), "%" + barCode.Trim().ToUpper() + "%");
}
if (fetch)
{
cmd.CommandText = "select count(*) from (" + sql.ToString() + ")t";
count = Magic.Framework.Utils.Cast.Int(dbsession.ExecuteScalar(cmd));
}
int startIndex = (pageIndex - 1) * pageSize + 1, endIndex = pageIndex * pageSize;
sql.Append(" order by poh.ord_num desc,pol.line_num)t1 where t1.rowindex>=").Append(startIndex)
.Append(" and t1.rowindex<=").Append(endIndex);
cmd.CommandText = "select * from(" + sql.ToString();
return(dbsession.ExecuteDataSet(cmd));
}
public void AddLine(ISession session, string po, string poLine, string area, string section, decimal qty)
{
if (this.Status != POReturnStatus.New)
{
throw new Exception("采购退货单不是新建状态，无法添加明细");
}
POLine line = POLine.Retrieve(session, po, poLine);
if (line == null)
{
throw new Exception(po + "行" + poLine + "不存在");
}
if (line.ReceiveQty < qty)
{
throw new Exception(po + "行" + poLine + "退货数量" + qty.ToString() + "大于收货数量" + line.ReceiveQty.ToString());
}
StockDetail sto = StockDetail.Retrieve(session, line.SKUID, this.LocationCode, area, section);
if (sto == null)
{
throw new Exception(po + "行" + poLine + "库存明细(" + area + "," + section + ")不存在");
}
if (sto.StockQty < qty)
{
throw new Exception(po + "行" + poLine + "退货数量" + qty.ToString() + "大于库存量" + sto.StockQty.ToString());
}
DbSession dbsession = session.DbSession as DbSession;
IDbCommand cmd = dbsession.CreateSqlStringCommand("Select Sum(a.rtn_qty) From ord_pur_rtn_line a Where a.po_num=:po And a.po_line=:poline");
dbsession.AddParameter(cmd, ":po", DbTypeInfo.AnsiString(16), po);
dbsession.AddParameter(cmd, ":poline", DbTypeInfo.AnsiString(4), poLine);
decimal returnedQty = Cast.Decimal(dbsession.ExecuteScalar(cmd));
if (returnedQty + qty > line.ReceiveQty)
{
throw new Exception(po + "行" + poLine + "，已退货数量" + returnedQty.ToString() + "本次退货数量" + qty.ToString() + "大于收货数量" + line.ReceiveQty.ToString());
}
POReturnLine rtnLine = new POReturnLine();
rtnLine.OrderNumber = this.OrderNumber;
rtnLine.LineNumber = this.NextLineNumber();
rtnLine.PONumber = po;
rtnLine.POLine = poLine;
rtnLine.SKUID = line.SKUID;
rtnLine.Price = line.Price;
rtnLine.Quantity = qty;
rtnLine.TaxValue = line.TaxValue;
rtnLine.StockDetailID = sto.StockDetailID;
rtnLine.Create(session);
}
private DataSet Query(ISession session, int pageIndex, int pageSize, bool fetch, ref int count)
{
DbSession dbsession = session.DbSession as DbSession;
StringBuilder sql = new StringBuilder();
IDbCommand cmd = dbsession.CreateSqlStringCommand("");
DateTime dtstart = Cast.DateTime(this.txtDateFrom.Text, new DateTime(1900, 1, 1)), dtend = Cast.DateTime(this.txtDateTo.Text, new DateTime(1900, 1, 1));
int intstart = Cast.Int(dtstart.ToString("yyyyMMdd")), intend = Cast.Int(dtend.ToString("yyyyMMdd"));
sql.Append(@"
Select sku.itm_barcode As BarCode,sku.itm_code As ItemCode,i.itm_name As ItemName,sku.color_code As ColorCode,c.Name As ColorText,sku.size_code As SizeCode
,Sum(tl.trans_qty) As TransQty
,Sum(tl.trans_qty*tl.trans_price) As TaxInAmt
,Sum(tl.trans_qty*tl.trans_price*tl.tax_val/(1+tl.tax_val)) As TaxAmt
,Sum(tl.trans_qty*tl.trans_price/(1+tl.tax_val)) As TaxExAmt
From inv_trans_line tl
Inner Join inv_trans_head th On th.trans_num=tl.trans_num
Inner Join prd_item_sku sku On sku.sku_id=tl.sku_id
Inner Join prd_item i On i.itm_code=sku.itm_code
Inner Join ord_pur_head po On po.Ord_Num=th.Ref_Ord_Num
Left Join prd_item_color c On c.code=sku.color_code
Where tl.trans_type_code='101'")
.Append(" And th.commit_date>=").Append(intstart)
.Append(" And th.commit_date<=").Append(intend)
.Append(" And tl.trans_date>=").Append(intstart)
.Append(" And tl.trans_date<=").Append(intend);
int ven = Cast.Int(this.drpVendor.SelectedValue);
if (ven > 0)
{
sql.Append(" And po.ven_id=").Append(ven);
}
if (this.txtPO.Text.Trim().Length > 0)
{
sql.Append(" And th.Ref_Ord_Num=:po");
dbsession.AddParameter(cmd, ":po", DbTypeInfo.AnsiString(16), this.txtPO.Text.Trim());
}
if (this.txtItemCode.Text.Trim().Length > 0)
{
sql.Append(" And sku.itm_code=:icode");
dbsession.AddParameter(cmd, ":icode", DbTypeInfo.AnsiString(18), this.txtItemCode.Text.Trim());
}
if (this.txtColor.Text.Trim().Length > 0)
{
sql.Append(" And sku.color_code=:icolor");
dbsession.AddParameter(cmd, ":icolor", DbTypeInfo.AnsiString(6), this.txtColor.Text.Trim());
}
if (this.txtSize.Text.Trim().Length > 0)
{
sql.Append(" And sku.size_code=:isize");
dbsession.AddParameter(cmd, ":isize", DbTypeInfo.AnsiString(6), this.txtSize.Text.Trim());
}
sql.Append(@"
Group By sku.itm_barcode,sku.itm_code,i.itm_name,sku.color_code,c.Name,sku.size_code
Order By sku.itm_code,sku.color_code,sku.size_code");
if (fetch)
{
cmd.CommandText = "select count(1) from(" + sql.ToString() + ")t";
count = Cast.Int(dbsession.ExecuteScalar(cmd));
}
if (pageIndex <= 0)
{
cmd.CommandText = sql.ToString();
return(dbsession.ExecuteDataSet(cmd));
}
int startIndex = (pageIndex - 1) * pageSize + 1, endIndex = pageIndex * pageSize;
StringBuilder sql2 = new StringBuilder();
sql2.Append("SELECT * FROM(SELECT mf_t1.*,Rownum AS mf_r FROM(")
.Append(sql.ToString())
.Append(") mf_t1) mf_t2 WHERE mf_r>=")
.Append(startIndex)
.Append(" AND mf_r<=")
.Append(endIndex);
cmd.CommandText = sql2.ToString();
return(dbsession.ExecuteDataSet(cmd));
}
private void QueryAndBindData(ISession session, int pageIndex, int pageSize, bool fetchRecordCount)
{
DbSession dbsession = session.DbSession as DbSession;
IDbCommand cmd = dbsession.CreateSqlStringCommand("");
System.Text.StringBuilder sql = new System.Text.StringBuilder();
sql.Append(@"
select Case When c.type=1 Then '咨询' Else '投诉' End As Type
,c.cmpt_type_name As MainType,b.cmpt_type_name As SubType
,u.Name As CreateUser,a.mbr_id As MbrId,m.card_id As MbrNum,m.Name As MbrName,a.Create_Date
,a.cmpt_content As Content
from magic.mbr_complaint a
Inner Join magic.complaint_type b On a.cmpt_type_id=b.cmpt_type_id
Inner Join magic.complaint_type c On b.parent_id=c.cmpt_type_id
Inner Join magic.mbr_members m On m.Id=a.mbr_id
Left Join magic.org_persons u On u.Id=a.creator
Where b.cmpt_type_id in(Select sub_id From ext_cs_log_main_type)");
int mainType = Cast.Int(this.drpMainType.SelectedValue);
if (mainType > 0)
{
sql.Append(" And c.cmpt_type_id=").Append(mainType);
}
int subType = Cast.Int(this.drpSubType.SelectedValue);
if (subType > 0)
{
sql.Append(" And b.cmpt_type_id=").Append(subType);
}
int userId = Cast.Int(this.drpUser.SelectedValue);
if (userId > 0)
{
sql.Append(" And a.creator=").Append(userId);
}
if (!string.IsNullOrEmpty(this.txtMbrNum.Text) && this.txtMbrNum.Text.Trim().Length > 0)
{
sql.Append(" And m.card_id Like :mbrNum");
dbsession.AddParameter(cmd, ":mbrNum", DbTypeInfo.AnsiString(10), "%" + this.txtMbrNum.Text.Trim() + "%");
}
if (!string.IsNullOrEmpty(this.txtMbrName.Text) && this.txtMbrName.Text.Trim().Length > 0)
{
sql.Append(" And And m.Name Like :mbrName");
dbsession.AddParameter(cmd, ":mbrName", DbTypeInfo.AnsiString(40), "%" + this.txtMbrName.Text.Trim() + "%");
}
DateTime dateFrom = Cast.DateTime(this.txtDateFrom.Text), dateTo = Cast.DateTime(this.txtDateTo.Text);
if (dateFrom > new DateTime(1900, 1, 1))
{
sql.Append(" And a.create_date>=:startDate");
dbsession.AddParameter(cmd, ":startDate", DbTypeInfo.DateTime(), dateFrom);
}
if (dateTo > new DateTime(1900, 1, 1))
{
sql.Append(" And a.create_date<=:endDate");
dbsession.AddParameter(cmd, ":endDate", DbTypeInfo.DateTime(), dateTo.AddDays(1));
}
if (fetchRecordCount)
{
cmd.CommandText = "select count(1) from(" + sql.ToString() + ")t";
int count = Cast.Int(dbsession.ExecuteScalar(cmd));
this.magicPagerMain.RecordCount = this.magicPagerSub.RecordCount = count;
}
sql.Append(" Order By a.create_date Desc) mf_t1) mf_t2 WHERE mf_r>=")
.Append((pageIndex - 1) * pageSize + 1)
.Append(" AND mf_r<=").Append(pageIndex * pageSize);
cmd.CommandText = "SELECT * FROM(SELECT mf_t1.*,Rownum AS mf_r FROM(" + sql.ToString();
this.repeatControl.DataSource = dbsession.ExecuteDataSet(cmd);
this.repeatControl.DataBind();
WebUtil.SetMagicPager(magicPagerMain, pageSize, pageIndex);
WebUtil.SetMagicPager(magicPagerSub, pageSize, pageIndex);
}
void IWHTransHead.AfterTransaction(ISession session)
{
DbSession dbSession = session.DbSession as DbSession;
IDbCommand command = null;
//CRM接口需要的退货类型
int returnType = 0;
switch (this.OrderTypeCode)
{
case ORDER_TYPE_MBR_RTN: returnType = 0; break; //会员退货
case ORDER_TYPE_LOGISTICS_RTN: returnType = 1; break; //物流退货
case ORDER_TYPE_INNER_RTN: returnType = 2; break; //内部退货
case ORDER_TYPE_EXCHANGE_RTN: //换货退货，不需要调用CRM存储过程，只更新换货订单状态
{
SOHead saleOrder = SOHead.Query(session, this.ExchangeOrder);
if (saleOrder == null)
{
return;
}
//saleOrder.UpdateStatus(session, 25).UpdateLineStatus(session, 30);
command = dbSession.CreateStoredProcCommand("f_order_change_confirm", new object[] { 0, saleOrder.ID });
dbSession.ExecuteNonQuery(command);
int r = Cast.Int((command.Parameters[0] as IDataParameter).Value);
if (r == 0 || r == -2)
{
return;
}
else
{
throw new Exception("更新换货订单状态时出错，f_order_change_confirm返回" + r.ToString());
}
};
default: throw new Exception("无效的退货类型");
}
//判断是否全退
bool isReturnAll = false;
int snLineCount = session.CreateEntityQuery <CRMSNLine>()
.Where(Exp.Eq("SNID", this.RefOrderID))
.Count();
isReturnAll = snLineCount == this._lines.Count; //第一步，发货单明细与退货明细数量不相等，则一定不是全退
StringBuilder snLineIds = new StringBuilder();
for (int i = 0; i < this._lines.Count; i++)
{
if (this._lines[i].Quantity != this._lines[i].DeliverQuantity)
{
isReturnAll = false;
}
if (i != 0)
{
snLineIds.Append(",");
}
snLineIds.Append(this._lines[i].RefOrderLineID.ToString()).Append("-").Append(this._lines[i].Quantity.ToString());
}
//检查：物流退货、内部退货必须是全退
if (!isReturnAll && (this.OrderTypeCode == ORDER_TYPE_LOGISTICS_RTN || this.OrderTypeCode == ORDER_TYPE_INNER_RTN))
{
throw new Exception("物流退货、内部退货必须是全退");
}
//调用CRM存储过程
if (isReturnAll)
{
//全退: ORDERS.P_RETURN_SHIPPINGNOTICES
// v_shipping_id number, --CRM发货单ID
// v_vouch varchar2, --退货单号码
// v_comments VARCHAR2, --退货备注信息
// v_operator_id NUMBER, --退货人，ERP与CRM用户不同，现在没有对应起来
// v_return_order_type INTEGER, --退货类型: 0会员退货，1物流退货，2内部退货
// v_is_bad INTEGER, --是否恶意退货
// v_return OUT NUMBER --返回值
command = dbSession.CreateStoredProcCommand("ORDERS.P_RETURN_SHIPPINGNOTICES", new object[] { this.RefOrderID, this.OrderNumber, 0, returnType, this.IsMalicious ? 1 : 0, 0 });
}
else
{
//部分退货，目前只支持针对部分发货明细全退
//2008-11-03: 部分退货可以指定退货数量了
// : ORDERS.P_PARTLY_RETURN2
// v_shipping_id number, --CRM发货单ID
// v_lines VARCHAR2, --发货单明细，格式为 明细ID1,明细ID2,明细ID3...
// v_vouch varchar2, --退货单号码
// v_return OUT NUMBER --返回值
command = dbSession.CreateStoredProcCommand("ORDERS.p_partly_return", new object[] { this.RefOrderID, snLineIds.ToString(), this.OrderNumber, 0 });
}
dbSession.ExecuteNonQuery(command);
//存储过程中
IDataParameter param = command.Parameters[command.Parameters.Count - 1] as IDataParameter;
int result = Cast.Int(param.Value);
if (result < 0)
{
throw new Exception("退货时发生异常，异常信息: " + result.ToString());
}
//退货完成后添加退货统计信息
command = dbSession.CreateStoredProcCommand("p_rpt_fi_sale_return", new object[] { this.OrderNumber, 0, 0 });
dbSession.ExecuteNonQuery(command);
param = command.Parameters[1] as IDataParameter;
result = Cast.Int(param.Value);
//退货后出现不正常现象，原因是因为礼品、套装等优惠的幅度太大，退货后礼品和解套的套装都按原价计算，因此可能出现会员退货之后还需要再支付一定金额
//解决方法：检查会员当前帐户余额是否足够（不能出现负数），出现负数时（例如退货后帐户余额变成-127）提示仓库人员，并且回滚事务
// 仓库人员通知客服和财务，客服与会员进行确认，财务在会员帐户上手工充值（充值127），再由仓库执行退货操作
// 上面这些事情做完之后，退货后会员帐户余额变成0
decimal returnAmt = Cast.Decimal((command.Parameters[2] as IDataParameter).Value);
if (returnAmt < 0)
{
//如果退货金额小于0，查会员帐户余额是否出现小于0的情况
command = dbSession.CreateSqlStringCommand(@"
Select m.deposit
From mbr_members m
Inner Join ord_headers o On o.buyer_id=m.Id
Where o.so_number=:sonum");
dbSession.AddParameter(command, ":sonum", DbTypeInfo.AnsiString(16), this.OrginalOrderNumber);
decimal accountAmt = Cast.Decimal(dbSession.ExecuteScalar(command));
if (accountAmt < 0)
{
throw new Exception("该退货单退货后导致会员帐户出现负数，请联系客服人员进行处理（退货金额为" + returnAmt.ToString("#0.#0") + "，退货后帐户余额为" + accountAmt.ToString("#0.#0") + "）");
}
}
switch (result)
{
case -1001:
throw new Exception("本月的库存期间没有正常开启，请联系系统维护人员");
case -1002:
command = dbSession.CreateSqlStringCommand("select * from fi_rpt_sale_return where rt_number='" + this.OrderNumber + "'");
DataSet ds = dbSession.ExecuteDataSet(command);
throw new Exception("退货统计数据错误，请联系系统维护人员" + "<br />" +
"销售:" + Cast.Decimal(ds.Tables[0].Rows[0]["sale_amt"]).ToString() + ", " +
"发送费:" + Cast.Decimal(ds.Tables[0].Rows[0]["transport_amt"]).ToString() + ", " +
"包装费:" + Cast.Decimal(ds.Tables[0].Rows[0]["package_amt"]).ToString() + ", " +
"礼券:" + Cast.Decimal(ds.Tables[0].Rows[0]["coupons_amt"]).ToString() + ", " +
"折扣:" + Cast.Decimal(ds.Tables[0].Rows[0]["discount_amt"]).ToString() + ", " +
"礼金:" + Cast.Decimal(ds.Tables[0].Rows[0]["emoney_amt"]).ToString() + ", " +
"帐户:" + Cast.Decimal(ds.Tables[0].Rows[0]["account_receivable"]).ToString() + ", " +
"POS机:" + Cast.Decimal(ds.Tables[0].Rows[0]["pos_receivable"]).ToString() + ", " +
"物流应收:" + Cast.Decimal(ds.Tables[0].Rows[0]["logis_receivable"]).ToString());
default: break;
}
}
