|
|
using System;
|
|
|
using System.Linq;
|
|
|
using System.Web.Mvc;
|
|
|
using DSWeb.TruckMng.DAL.MsOpTruckBulkDAL;
|
|
|
using DSWeb.TruckMng.Models.MsOpTruckBulk;
|
|
|
using DSWeb.MvcShipping.Helper;
|
|
|
using HcUtility.Comm;
|
|
|
using HcUtility.Core;
|
|
|
using System.Collections.Generic;
|
|
|
using DSWeb.EntityDA;
|
|
|
using DSWeb.Attributes;
|
|
|
using DSWeb.MvcShipping.DAL.MsSysBillNoSet;
|
|
|
using DSWeb.MvcShipping.DAL.MsInfoClient;
|
|
|
using System.IO;
|
|
|
using System.Data;
|
|
|
using System.Data.OleDb;
|
|
|
using DSWeb.MvcShipping.Comm.Cookie;
|
|
|
using DSWeb.Areas.RptMng.Comm;
|
|
|
using DSWeb.Areas.CommMng.DAL;
|
|
|
using DSWeb.Areas.CommMng.Models;
|
|
|
using System.Text;
|
|
|
using System.Web.Configuration;
|
|
|
using DSWeb.SoftMng.Filter;
|
|
|
using com.sun.org.apache.bcel.@internal.generic;
|
|
|
using DSWeb.TruckMng.Models.MsCustTruckFeeTemplate;
|
|
|
using static NPOI.HSSF.Util.HSSFColor;
|
|
|
using NPOI.SS.Formula.Functions;
|
|
|
using System.Web.UI.WebControls;
|
|
|
|
|
|
namespace DSWeb.Areas.TruckMng.Controllers
|
|
|
{
|
|
|
[JsonRequestBehavior]
|
|
|
public class MsOpTruckBulkController : Controller
|
|
|
{
|
|
|
public ActionResult Index()
|
|
|
{
|
|
|
return View();
|
|
|
}
|
|
|
public ActionResult HistryFeeIndex()
|
|
|
{
|
|
|
return View();
|
|
|
}
|
|
|
public ActionResult Edit()
|
|
|
{
|
|
|
return View();
|
|
|
}
|
|
|
|
|
|
public ActionResult AuditIndex()
|
|
|
{
|
|
|
return View();
|
|
|
}
|
|
|
public ActionResult FeeEdit()
|
|
|
{
|
|
|
return View();
|
|
|
}
|
|
|
public ActionResult OtherFeeView()
|
|
|
{
|
|
|
return View();
|
|
|
}
|
|
|
|
|
|
public ActionResult AmendIndex()
|
|
|
{
|
|
|
return View();
|
|
|
}
|
|
|
public ActionResult AmendFeeEdit()
|
|
|
{
|
|
|
return View();
|
|
|
}
|
|
|
|
|
|
public ActionResult PiLiangFeeEdit()
|
|
|
{
|
|
|
return View();
|
|
|
}
|
|
|
public ActionResult CtnIndex()
|
|
|
{
|
|
|
return View();
|
|
|
}
|
|
|
public ActionResult CtnEdit()
|
|
|
{
|
|
|
return View();
|
|
|
}
|
|
|
public ActionResult CtnAuditIndex()
|
|
|
{
|
|
|
return View();
|
|
|
}
|
|
|
public ActionResult RptLrIndex()
|
|
|
{
|
|
|
return View();
|
|
|
}
|
|
|
public ActionResult RptLrCtnIndex()
|
|
|
{
|
|
|
return View();
|
|
|
}
|
|
|
[SqlKeyWordsFilter(Type = "Action")]//sql 防注入过滤器
|
|
|
public ContentResult GetDataList(int start, int limit, string sort, string condition)
|
|
|
{
|
|
|
var dataList = MsOpTruckBulkDAL.GetDataList(start, limit,condition, Convert.ToString(Session["USERID"]), Convert.ToString(Session["SHOWNAME"]), Convert.ToString(Session["COMPANYID"]), sort);
|
|
|
int count = MsOpTruckBulkDAL.getTotalCount(condition, CookieConfig.GetCookie_UserId(Request), Convert.ToString(Session["SHOWNAME"]), Convert.ToString(Session["COMPANYID"]));
|
|
|
var json = JsonConvert.Serialize(new { Success = true, Message = "查询成功", totalCount = count, data = dataList.ToList() });
|
|
|
return new ContentResult() { Content = json };
|
|
|
}
|
|
|
|
|
|
[SqlKeyWordsFilter(Type = "Action")]//sql 防注入过滤器
|
|
|
public ContentResult GetAuditDataList(int start, int limit, string sort, string condition,string isaudit)
|
|
|
{
|
|
|
var dataList = MsOpTruckBulkDAL.GetAuditDataList(condition, isaudit,Convert.ToString(Session["USERID"]), Convert.ToString(Session["SHOWNAME"]), Convert.ToString(Session["COMPANYID"]), sort);
|
|
|
var list = dataList.Skip(start).Take(limit);
|
|
|
var json = JsonConvert.Serialize(new { Success = true, Message = "查询成功", totalCount = dataList.Count, data = list.ToList() });
|
|
|
return new ContentResult() { Content = json };
|
|
|
}
|
|
|
[SqlKeyWordsFilter(Type = "Action")]//sql 防注入过滤器
|
|
|
public ContentResult GetAmendDataList(int start, int limit, string sort, string condition)
|
|
|
{
|
|
|
var dataList = MsOpTruckBulkDAL.GetAmendDataList(condition, Convert.ToString(Session["USERID"]), Convert.ToString(Session["SHOWNAME"]), Convert.ToString(Session["COMPANYID"]), sort);
|
|
|
var list = dataList.Skip(start).Take(limit);
|
|
|
var json = JsonConvert.Serialize(new { Success = true, Message = "查询成功", totalCount = dataList.Count, data = list.ToList() });
|
|
|
return new ContentResult() { Content = json };
|
|
|
}
|
|
|
[SqlKeyWordsFilter(Type = "Action")]//sql 防注入过滤器
|
|
|
public ContentResult GetDataListStr(string sort, string condition)
|
|
|
{
|
|
|
var dataListStr = MsOpTruckBulkDAL.GetDataListStr(condition, Convert.ToString(Session["USERID"]), Convert.ToString(Session["SHOWNAME"]), Convert.ToString(Session["COMPANYID"]), sort);
|
|
|
var json = JsonConvert.Serialize(new { Success = true, Message = "查询成功", data = dataListStr });
|
|
|
return new ContentResult() { Content = json };
|
|
|
}
|
|
|
|
|
|
[SqlKeyWordsFilter(Type = "Action")]//sql 防注入过滤器
|
|
|
public ContentResult GetBodyList(int start, int limit, string sort, string condition)
|
|
|
{
|
|
|
var dataList = MsOpTruckBulkDAL.GetBodyList(condition, sort);
|
|
|
|
|
|
|
|
|
var json = JsonConvert.Serialize(new { Success = true, Message = "查询成功", totalCount = dataList.Count, data = dataList.ToList() });
|
|
|
return new ContentResult() { Content = json };
|
|
|
}
|
|
|
[SqlKeyWordsFilter(Type = "Action")]//sql 防注入过滤器
|
|
|
public ContentResult GetData(string handle, string condition)
|
|
|
{
|
|
|
MsOpTruckBulk head = null;
|
|
|
if (handle == "edit")
|
|
|
{
|
|
|
head = MsOpTruckBulkDAL.GetData(condition);
|
|
|
}
|
|
|
if (head == null)
|
|
|
{
|
|
|
head = new MsOpTruckBulk();
|
|
|
head.OP = Convert.ToString(Session["SHOWNAME"]);
|
|
|
head.PCKGS = 0;
|
|
|
head.NOPCKGS = 0;
|
|
|
head.SALE = Convert.ToString(Session["SHOWNAME"]);
|
|
|
head.NETWEIGHT = 0;
|
|
|
head.TTLYFDR = "0";
|
|
|
head.TRUCKNUM = 0;
|
|
|
|
|
|
//head.BSTYPE = "2";
|
|
|
}
|
|
|
|
|
|
|
|
|
var json = JsonConvert.Serialize(
|
|
|
new { Success = true, Message = "查询成功", data = head });
|
|
|
return new ContentResult() { Content = json };
|
|
|
}
|
|
|
[SqlKeyWordsFilter(Type = "Action")]//sql 防注入过滤器
|
|
|
public ContentResult GetAmendData(string condition)
|
|
|
{
|
|
|
MsOpTruckBulk head = null;
|
|
|
|
|
|
var headlist = MsOpTruckBulkDAL.GetAmendDataList(condition, Convert.ToString(Session["USERID"]), Convert.ToString(Session["SHOWNAME"]), Convert.ToString(Session["COMPANYID"]));
|
|
|
if (headlist.Count > 0)
|
|
|
head = headlist[0];
|
|
|
else
|
|
|
head = new MsOpTruckBulk();
|
|
|
|
|
|
var json = JsonConvert.Serialize(
|
|
|
new { Success = true, Message = "查询成功", data = head });
|
|
|
return new ContentResult() { Content = json };
|
|
|
}
|
|
|
|
|
|
public ContentResult Save(string opstatus, string data, string data2,string body)
|
|
|
{
|
|
|
if (Convert.ToString(Session["COMPANYID"]).ToString().Trim() == "" || Convert.ToString(Session["USERID"]).ToString().Trim() == "" || Convert.ToString(Session["CODENAME"]).ToString().Trim() == "" || Convert.ToString(Session["SHOWNAME"]).ToString().Trim() == "" || Convert.ToString(Session["DEPTNAME"]).ToString().Trim() == "")
|
|
|
{
|
|
|
var jsonRespose2 = new JsonResponse { Success = false, Message = "登录超时，请退出系统重新登录！" };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose2) };
|
|
|
}
|
|
|
//
|
|
|
T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA();
|
|
|
AttributeManage attributeManager = new AttributeManage();
|
|
|
var dataall = data.Replace("}", ",") + data2.Replace("{", "");
|
|
|
var headData = JsonConvert.Deserialize<MsOpTruckBulk>(dataall);
|
|
|
var bodyList = JsonConvert.Deserialize<List<MsOpTruckBulkDetail>>(body);
|
|
|
|
|
|
|
|
|
var isPost = true;
|
|
|
var errorstr = "";
|
|
|
var iscreatecustno = false;
|
|
|
|
|
|
//分公司代码
|
|
|
if (headData.CORPID == "" || headData.CORPID == null)
|
|
|
{
|
|
|
headData.CORPID = Convert.ToString(Session["COMPANYID"]);
|
|
|
}
|
|
|
|
|
|
//分公司代码
|
|
|
if (headData.BSNO == "" || headData.BSNO == null)
|
|
|
{
|
|
|
headData.BSNO = "toptruck" + Guid.NewGuid().ToString().Replace("-","");
|
|
|
}
|
|
|
|
|
|
#region 委托编号规则
|
|
|
var isuse = false;
|
|
|
var transstr = "陆运普货订单";
|
|
|
if (headData.TRANSTYPE=="集装箱") transstr = "陆运集运订单";
|
|
|
var billnoset = MsSysBillNoSetDAL.GetData("OPLBNAME='"+ transstr + "'", Convert.ToString(Session["COMPANYID"]));
|
|
|
if (billnoset.BILLTYPE != "")
|
|
|
isuse = true;
|
|
|
if (headData.CUSTNO == "")
|
|
|
{
|
|
|
|
|
|
if (isuse)
|
|
|
{
|
|
|
if (billnoset.BILLTYPE != "")
|
|
|
{
|
|
|
headData.CUSTNO = MsSysBillNoSetDAL.GetBillNo(billnoset, headData.ETD.ToString().Trim(), headData.ACCDATE.ToString().Trim(), "", headData.OP, headData.SALECORPID);
|
|
|
iscreatecustno = true;
|
|
|
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
else {
|
|
|
if (!isuse) {
|
|
|
var ct = MsOpTruckBulkDAL.GetRdCount("BSNO<>'" + headData.BSNO + "' AND CUSTNO='" + headData.CUSTNO + "' and CORPID='" + Convert.ToString(Session["COMPANYID"]) + "'");
|
|
|
if (ct != 0)
|
|
|
{
|
|
|
isPost = false;
|
|
|
errorstr = "委托编号";
|
|
|
}
|
|
|
}
|
|
|
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
//
|
|
|
|
|
|
if (headData.ETD != "" && headData.CUSTOMERNAME != "")// headData.STLDATE == "" &&
|
|
|
{
|
|
|
//根据客户、开船日计算账期
|
|
|
|
|
|
var _dmb = MsInfoClientDAL.GetSTLDATE(headData.CUSTOMERNAME, "陆运散货", headData.ETD, headData.SALE);
|
|
|
headData.STLNAME = _dmb.STLNAME;
|
|
|
headData.STLDATE = _dmb.STLDATE;
|
|
|
|
|
|
if (_dmb.STLNAME == "")
|
|
|
{
|
|
|
headData.STLNAME = "现结买单";
|
|
|
headData.STLDATE = headData.ETD;
|
|
|
}
|
|
|
}
|
|
|
if (headData.ORDERNO != "") {
|
|
|
|
|
|
var ct = MsOpTruckBulkDAL.GetRdCount("BSNO<>'" + headData.BSNO + "' AND ORDERNO='" + headData.ORDERNO + "' ");
|
|
|
if (ct != 0)
|
|
|
{
|
|
|
isPost = false;
|
|
|
errorstr = "客户订单编号" + headData.ORDERNO + "重复";
|
|
|
}
|
|
|
|
|
|
}
|
|
|
|
|
|
string rq = T_ALL_DA.GetStrSQL("rq", "select getdate() as rq");
|
|
|
//
|
|
|
if (opstatus == "add")
|
|
|
{
|
|
|
headData.DbOperationType = DbOperationType.DbotIns;
|
|
|
//
|
|
|
headData.BSSTATUS = false;//业务状态
|
|
|
headData.FEESTATUS = false;//费用状态
|
|
|
headData.ISVOU = false;//是否生成凭证
|
|
|
headData.VOUNO="";//凭证号
|
|
|
headData.CREATEUSER = Convert.ToString(Session["USERID"]);//创建人
|
|
|
headData.CREATETIME =DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");//创建时间
|
|
|
headData.MODIFIEDUSER = Convert.ToString(Session["USERID"]);//最后一次操作人
|
|
|
headData.MODIFIEDTIME = DateTime.Parse(rq);//最后一次操作时间
|
|
|
headData.ISFEE = "0";
|
|
|
}
|
|
|
else if (opstatus == "edit")
|
|
|
{
|
|
|
headData.DbOperationType = DbOperationType.DbotUpd;
|
|
|
headData.ModelUIStatus = "E";
|
|
|
//
|
|
|
headData.MODIFIEDUSER = Convert.ToString(Session["USERID"]);//最后一次操作人
|
|
|
headData.MODIFIEDTIME = DateTime.Parse(rq);//最后一次操作时间
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
headData.DbOperationType = DbOperationType.DbotDel;
|
|
|
}
|
|
|
//if (headData.CTNNUM == null) { headData.CTNNUM = 0; }
|
|
|
|
|
|
if (headData.ETD == "") headData.ETD = null;
|
|
|
if (headData.ETA == "") headData.ETA = null;
|
|
|
if (headData.CLOSINGDATE == "") headData.CLOSINGDATE = null;
|
|
|
|
|
|
if (isPost)
|
|
|
{
|
|
|
var modb = new ModelObjectDB();
|
|
|
DBResult result = modb.Save(headData);
|
|
|
var BSNO = headData.BSNO;
|
|
|
if (result.Success == true)
|
|
|
{
|
|
|
|
|
|
result = MsOpTruckBulkDAL.SaveDetail(headData, bodyList, Convert.ToString(Session["USERID"]));
|
|
|
//--result = MsOpOtherDAL.SaveDetail(headData, bodyList, Convert.ToString(Session["USERID"]));
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
MsSysBillNoSetDAL.DeleteBsNo(headData.CUSTNO);
|
|
|
}
|
|
|
var jsonRespose = new JsonResponse
|
|
|
{
|
|
|
Success = result.Success,
|
|
|
Message = result.Message,
|
|
|
Data = MsOpTruckBulkDAL.GetData(" op_truck_bulk.BSNO='" + BSNO + "'")
|
|
|
};
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
else {
|
|
|
if (iscreatecustno == true)
|
|
|
MsSysBillNoSetDAL.DeleteBsNo(headData.CUSTNO);
|
|
|
var jsonRespose = new JsonResponse { Success = false, Message =errorstr };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public ContentResult Delete(string data)
|
|
|
{
|
|
|
if (Convert.ToString(Session["COMPANYID"]).ToString().Trim() == "" || Convert.ToString(Session["USERID"]).ToString().Trim() == "" || Convert.ToString(Session["CODENAME"]).ToString().Trim() == "" || Convert.ToString(Session["SHOWNAME"]).ToString().Trim() == "" || Convert.ToString(Session["DEPTNAME"]).ToString().Trim() == "")
|
|
|
{
|
|
|
var jsonRespose2 = new JsonResponse { Success = false, Message = "登录超时，请退出系统重新登录！" };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose2) };
|
|
|
}
|
|
|
//
|
|
|
var head = JsonConvert.Deserialize<MsOpTruckBulk>(data);
|
|
|
var isfee = MsOpTruckBulkDAL.GetFeeCount(head.BSNO);
|
|
|
if (isfee)
|
|
|
{
|
|
|
var jsonRespose = new JsonResponse { Success = false, Message ="此票业务存在费用，请先删除费用才能删除此票业务！" };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
else {
|
|
|
var BSNO = head.BSNO;
|
|
|
var modb = new ModelObjectDB();
|
|
|
DBResult result = modb.Delete(head);
|
|
|
if (result.Success == true) { MsSysBillNoSetDAL.DeleteBsNo(head.CUSTNO); }
|
|
|
|
|
|
if (result.Success == true)
|
|
|
{
|
|
|
MsOpTruckBulkDAL.DeleteDetail(head);
|
|
|
}
|
|
|
var jsonRespose = new JsonResponse { Success = result.Success, Message = result.Message };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public ContentResult DeleteList(string data)
|
|
|
{
|
|
|
if (Convert.ToString(Session["COMPANYID"]).ToString().Trim() == "" || Convert.ToString(Session["USERID"]).ToString().Trim() == "" || Convert.ToString(Session["CODENAME"]).ToString().Trim() == "" || Convert.ToString(Session["SHOWNAME"]).ToString().Trim() == "" || Convert.ToString(Session["DEPTNAME"]).ToString().Trim() == "")
|
|
|
{
|
|
|
var jsonRespose2 = new JsonResponse { Success = false, Message = "登录超时，请退出系统重新登录！" };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose2) };
|
|
|
}
|
|
|
var headData = JsonConvert.Deserialize<List<MsOpTruckBulk>>(data);
|
|
|
var errstr = "";
|
|
|
|
|
|
if (headData != null)
|
|
|
{
|
|
|
foreach (var enumValue in headData)
|
|
|
{
|
|
|
var isfee = MsOpTruckBulkDAL.GetFeeCount(enumValue.BSNO);
|
|
|
if (isfee)
|
|
|
{
|
|
|
if (errstr == "")
|
|
|
errstr = enumValue.CUSTNO;
|
|
|
else
|
|
|
errstr = errstr + "," + enumValue.CUSTNO;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
var BSNO = enumValue.BSNO;
|
|
|
var modb = new ModelObjectDB();
|
|
|
DBResult result = modb.Delete(enumValue);
|
|
|
if (result.Success == true) { MsSysBillNoSetDAL.DeleteBsNo(enumValue.CUSTNO); }
|
|
|
|
|
|
if (result.Success == true)
|
|
|
{
|
|
|
MsOpTruckBulkDAL.DeleteDetail(enumValue);
|
|
|
}
|
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
}
|
|
|
}
|
|
|
|
|
|
if (errstr == "") errstr = "删除成功！";
|
|
|
else errstr = errstr + "存在费用，请先删除费用才能删除此票业务！";
|
|
|
|
|
|
var jsonRespose = new JsonResponse { Success = true, Message = errstr };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
|
|
|
}
|
|
|
|
|
|
public ContentResult CreateFee(string opstatus, string data, string data2, string body)
|
|
|
{
|
|
|
if (Convert.ToString(Session["COMPANYID"]).ToString().Trim() == "" || Convert.ToString(Session["USERID"]).ToString().Trim() == "" || Convert.ToString(Session["CODENAME"]).ToString().Trim() == "" || Convert.ToString(Session["SHOWNAME"]).ToString().Trim() == "" || Convert.ToString(Session["DEPTNAME"]).ToString().Trim() == "")
|
|
|
{
|
|
|
var jsonRespose2 = new JsonResponse { Success = false, Message = "登录超时，请退出系统重新登录！" };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose2) };
|
|
|
}
|
|
|
//
|
|
|
T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA();
|
|
|
AttributeManage attributeManager = new AttributeManage();
|
|
|
var dataall = data.Replace("}", ",") + data2.Replace("{", "");
|
|
|
var headData = JsonConvert.Deserialize<MsOpTruckBulk>(dataall);
|
|
|
var bodyList = JsonConvert.Deserialize<List<MsOpTruckBulkDetail>>(body);
|
|
|
|
|
|
|
|
|
var isPost = true;
|
|
|
var errorstr = "";
|
|
|
var iscreatecustno = false;
|
|
|
|
|
|
//分公司代码
|
|
|
if (headData.CORPID == "" || headData.CORPID == null)
|
|
|
{
|
|
|
headData.CORPID = Convert.ToString(Session["COMPANYID"]);
|
|
|
}
|
|
|
|
|
|
//分公司代码
|
|
|
if (headData.BSNO == "" || headData.BSNO == null)
|
|
|
{
|
|
|
headData.BSNO = "toptruck" + Guid.NewGuid().ToString().Replace("-", "");
|
|
|
}
|
|
|
|
|
|
#region 委托编号规则
|
|
|
var isuse = false;
|
|
|
var billnoset = MsSysBillNoSetDAL.GetData("OPLBNAME='陆运普货订单'", Convert.ToString(Session["COMPANYID"]));
|
|
|
if (billnoset.BILLTYPE != "")
|
|
|
isuse = true;
|
|
|
if (headData.CUSTNO == "")
|
|
|
{
|
|
|
|
|
|
if (isuse)
|
|
|
{
|
|
|
if (billnoset.BILLTYPE != "")
|
|
|
{
|
|
|
headData.CUSTNO = MsSysBillNoSetDAL.GetBillNo(billnoset, headData.ETD.ToString().Trim(), headData.ACCDATE.ToString().Trim(), "", headData.OP, headData.SALECORPID);
|
|
|
iscreatecustno = true;
|
|
|
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
if (!isuse)
|
|
|
{
|
|
|
var ct = MsOpTruckBulkDAL.GetRdCount("BSNO<>'" + headData.BSNO + "' AND CUSTNO='" + headData.CUSTNO + "' and CORPID='" + Convert.ToString(Session["COMPANYID"]) + "'");
|
|
|
if (ct != 0)
|
|
|
{
|
|
|
isPost = false;
|
|
|
errorstr = "委托编号";
|
|
|
}
|
|
|
}
|
|
|
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
//
|
|
|
|
|
|
if (headData.ETD != "" && headData.CUSTOMERNAME != "")// headData.STLDATE == "" &&
|
|
|
{
|
|
|
//根据客户、开船日计算账期
|
|
|
|
|
|
var _dmb = MsInfoClientDAL.GetSTLDATE(headData.CUSTOMERNAME, "陆运散货", headData.ETD, headData.SALE);
|
|
|
headData.STLNAME = _dmb.STLNAME;
|
|
|
headData.STLDATE = _dmb.STLDATE;
|
|
|
|
|
|
if (_dmb.STLNAME == "")
|
|
|
{
|
|
|
headData.STLNAME = "现结买单";
|
|
|
headData.STLDATE = headData.ETD;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
string rq = T_ALL_DA.GetStrSQL("rq", "select getdate() as rq");
|
|
|
//
|
|
|
if (opstatus == "add")
|
|
|
{
|
|
|
headData.DbOperationType = DbOperationType.DbotIns;
|
|
|
//
|
|
|
headData.BSSTATUS = false;//业务状态
|
|
|
headData.FEESTATUS = false;//费用状态
|
|
|
headData.ISVOU = false;//是否生成凭证
|
|
|
headData.VOUNO = "";//凭证号
|
|
|
headData.CREATEUSER = Convert.ToString(Session["USERID"]);//创建人
|
|
|
headData.CREATETIME = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");//创建时间
|
|
|
headData.MODIFIEDUSER = Convert.ToString(Session["USERID"]);//最后一次操作人
|
|
|
headData.MODIFIEDTIME = DateTime.Parse(rq);//最后一次操作时间
|
|
|
}
|
|
|
else if (opstatus == "edit")
|
|
|
{
|
|
|
headData.DbOperationType = DbOperationType.DbotUpd;
|
|
|
headData.ModelUIStatus = "E";
|
|
|
//
|
|
|
headData.MODIFIEDUSER = Convert.ToString(Session["USERID"]);//最后一次操作人
|
|
|
headData.MODIFIEDTIME = DateTime.Parse(rq);//最后一次操作时间
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
headData.DbOperationType = DbOperationType.DbotDel;
|
|
|
}
|
|
|
//if (headData.CTNNUM == null) { headData.CTNNUM = 0; }
|
|
|
|
|
|
if (headData.ETD == "") headData.ETD = null;
|
|
|
if (headData.ETA == "") headData.ETA = null;
|
|
|
|
|
|
if (isPost)
|
|
|
{
|
|
|
var modb = new ModelObjectDB();
|
|
|
DBResult result = modb.Save(headData);
|
|
|
var BSNO = headData.BSNO;
|
|
|
if (result.Success == true)
|
|
|
{
|
|
|
|
|
|
result = MsOpTruckBulkDAL.SaveDetail(headData, bodyList, Convert.ToString(Session["USERID"]));
|
|
|
if (result.Success == true)
|
|
|
{
|
|
|
|
|
|
result = MsOpTruckBulkDAL.CreateFeeBl(headData,Convert.ToString(Session["USERID"]));
|
|
|
|
|
|
}
|
|
|
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
MsSysBillNoSetDAL.DeleteBsNo(headData.CUSTNO);
|
|
|
}
|
|
|
var jsonRespose = new JsonResponse
|
|
|
{
|
|
|
Success = result.Success,
|
|
|
Message = result.Message,
|
|
|
Data = MsOpTruckBulkDAL.GetData(" op_truck_bulk.BSNO='" + BSNO + "'")
|
|
|
};
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
if (iscreatecustno == true)
|
|
|
MsSysBillNoSetDAL.DeleteBsNo(headData.CUSTNO);
|
|
|
var jsonRespose = new JsonResponse { Success = false, Message = errorstr };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
}
|
|
|
//
|
|
|
|
|
|
public ContentResult DeletetruckBulkDetail(string data)
|
|
|
{
|
|
|
var head = JsonConvert.Deserialize<MsOpTruckBulkDetail>(data);
|
|
|
DBResult result = MsOpTruckBulkDAL.DeletetruckBulkDetail(head);
|
|
|
var jsonRespose = new JsonResponse { Success = result.Success, Message = result.Message };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
public ContentResult CreateFeeDetail(string data, string data2, string body)
|
|
|
{
|
|
|
var dataall = data.Replace("}", ",") + data2.Replace("{", "");
|
|
|
var headData = JsonConvert.Deserialize<MsOpTruckBulk>(dataall);
|
|
|
var bodyList = JsonConvert.Deserialize<List<MsOpTruckBulkDetail>>(body);
|
|
|
DBResult result = MsOpTruckBulkDAL.CreateFeeDetail(headData, bodyList, Convert.ToString(Session["USERID"]));
|
|
|
var jsonRespose = new JsonResponse { Success = result.Success, Message = result.Message };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
|
|
|
#region 审核、撤销审核
|
|
|
|
|
|
public ContentResult SubmitAudit(string opstatus, string data, string data2, string body)
|
|
|
{
|
|
|
if (Convert.ToString(Session["COMPANYID"]).ToString().Trim() == "" || Convert.ToString(Session["USERID"]).ToString().Trim() == "" || Convert.ToString(Session["CODENAME"]).ToString().Trim() == "" || Convert.ToString(Session["SHOWNAME"]).ToString().Trim() == "" || Convert.ToString(Session["DEPTNAME"]).ToString().Trim() == "")
|
|
|
{
|
|
|
var jsonRespose2 = new JsonResponse { Success = false, Message = "登录超时，请退出系统重新登录！" };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose2) };
|
|
|
}
|
|
|
//
|
|
|
T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA();
|
|
|
AttributeManage attributeManager = new AttributeManage();
|
|
|
var dataall = data.Replace("}", ",") + data2.Replace("{", "");
|
|
|
var headData = JsonConvert.Deserialize<MsOpTruckBulk>(dataall);
|
|
|
var bodyList = JsonConvert.Deserialize<List<MsOpTruckBulkDetail>>(body);
|
|
|
|
|
|
|
|
|
var isPost = true;
|
|
|
var errorstr = "";
|
|
|
var iscreatecustno = false;
|
|
|
|
|
|
//分公司代码
|
|
|
if (headData.CORPID == "" || headData.CORPID == null)
|
|
|
{
|
|
|
headData.CORPID = Convert.ToString(Session["COMPANYID"]);
|
|
|
}
|
|
|
|
|
|
//分公司代码
|
|
|
if (headData.BSNO == "" || headData.BSNO == null)
|
|
|
{
|
|
|
headData.BSNO = "toptruck" + Guid.NewGuid().ToString().Replace("-", "");
|
|
|
}
|
|
|
|
|
|
#region 委托编号规则
|
|
|
var isuse = false;
|
|
|
var transstr = "陆运普货订单";
|
|
|
if (headData.TRANSTYPE == "集装箱") transstr = "陆运集运订单";
|
|
|
var billnoset = MsSysBillNoSetDAL.GetData("OPLBNAME='"+ transstr + "'", Convert.ToString(Session["COMPANYID"]));
|
|
|
if (billnoset.BILLTYPE != "")
|
|
|
isuse = true;
|
|
|
if (headData.CUSTNO == "")
|
|
|
{
|
|
|
|
|
|
if (isuse)
|
|
|
{
|
|
|
if (billnoset.BILLTYPE != "")
|
|
|
{
|
|
|
headData.CUSTNO = MsSysBillNoSetDAL.GetBillNo(billnoset, headData.ETD.ToString().Trim(), headData.ACCDATE.ToString().Trim(), "", headData.OP, headData.SALECORPID);
|
|
|
iscreatecustno = true;
|
|
|
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
if (!isuse)
|
|
|
{
|
|
|
var ct = MsOpTruckBulkDAL.GetRdCount("BSNO<>'" + headData.BSNO + "' AND CUSTNO='" + headData.CUSTNO + "' and CORPID='" + Convert.ToString(Session["COMPANYID"]) + "'");
|
|
|
if (ct != 0)
|
|
|
{
|
|
|
isPost = false;
|
|
|
errorstr = "委托编号";
|
|
|
}
|
|
|
}
|
|
|
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
//
|
|
|
|
|
|
if (headData.ETD != "" && headData.CUSTOMERNAME != "")// headData.STLDATE == "" &&
|
|
|
{
|
|
|
//根据客户、开船日计算账期
|
|
|
|
|
|
var _dmb = MsInfoClientDAL.GetSTLDATE(headData.CUSTOMERNAME, "陆运散货", headData.ETD, headData.SALE);
|
|
|
headData.STLNAME = _dmb.STLNAME;
|
|
|
headData.STLDATE = _dmb.STLDATE;
|
|
|
|
|
|
if (_dmb.STLNAME == "")
|
|
|
{
|
|
|
headData.STLNAME = "现结买单";
|
|
|
headData.STLDATE = headData.ETD;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
string rq = T_ALL_DA.GetStrSQL("rq", "select getdate() as rq");
|
|
|
//
|
|
|
if (opstatus == "add")
|
|
|
{
|
|
|
headData.DbOperationType = DbOperationType.DbotIns;
|
|
|
//
|
|
|
headData.BSSTATUS = false;//业务状态
|
|
|
headData.FEESTATUS = false;//费用状态
|
|
|
headData.ISVOU = false;//是否生成凭证
|
|
|
headData.VOUNO = "";//凭证号
|
|
|
headData.CREATEUSER = Convert.ToString(Session["USERID"]);//创建人
|
|
|
headData.CREATETIME = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");//创建时间
|
|
|
headData.MODIFIEDUSER = Convert.ToString(Session["USERID"]);//最后一次操作人
|
|
|
headData.MODIFIEDTIME = DateTime.Parse(rq);//最后一次操作时间
|
|
|
}
|
|
|
else if (opstatus == "edit")
|
|
|
{
|
|
|
headData.DbOperationType = DbOperationType.DbotUpd;
|
|
|
headData.ModelUIStatus = "E";
|
|
|
//
|
|
|
headData.MODIFIEDUSER = Convert.ToString(Session["USERID"]);//最后一次操作人
|
|
|
headData.MODIFIEDTIME = DateTime.Parse(rq);//最后一次操作时间
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
headData.DbOperationType = DbOperationType.DbotDel;
|
|
|
}
|
|
|
//if (headData.CTNNUM == null) { headData.CTNNUM = 0; }
|
|
|
|
|
|
if (headData.ETD == "") headData.ETD = null;
|
|
|
if (headData.ETA == "") headData.ETA = null;
|
|
|
if (headData.CLOSINGDATE == "") headData.CLOSINGDATE = null;
|
|
|
|
|
|
|
|
|
if (isPost)
|
|
|
{
|
|
|
var modb = new ModelObjectDB();
|
|
|
DBResult result = modb.Save(headData);
|
|
|
var BSNO = headData.BSNO;
|
|
|
if (result.Success == true)
|
|
|
{
|
|
|
|
|
|
result = MsOpTruckBulkDAL.SaveDetail(headData, bodyList, Convert.ToString(Session["USERID"]));
|
|
|
if (result.Success == true)
|
|
|
{
|
|
|
var headList = new List<MsOpTruckBulk>();
|
|
|
headList.Add(headData);
|
|
|
result = MsOpTruckBulkDAL.SubmitAudit(Convert.ToString(Session["USERID"]), headList, Convert.ToString(Session["COMPANYID"]));
|
|
|
if (headData.TRANSTYPE == "集装箱"&&result.Data!=null)
|
|
|
{
|
|
|
if (result.Success == true && headData.CONTRACTNO != "" && headData.TEMPLATENAME != "" && result.Data.ToString() == "审核通过")
|
|
|
{
|
|
|
result = MsOpTruckBulkDAL.CreateCtnFeeBl(headData, bodyList, Convert.ToString(Session["USERID"]));
|
|
|
|
|
|
}
|
|
|
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
if (result.Success == true && headData.CONTRACTNO != "" && headData.TEMPLATENAME != "" && headData.UNITPRICE == headData.ORUNITPRICE && headData.FREIGHT == headData.ORFREIGHT)
|
|
|
{
|
|
|
result = MsOpTruckBulkDAL.CreateFeeBl(headData, Convert.ToString(Session["USERID"]));
|
|
|
}
|
|
|
|
|
|
}
|
|
|
}
|
|
|
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
MsSysBillNoSetDAL.DeleteBsNo(headData.CUSTNO);
|
|
|
}
|
|
|
var jsonRespose = new JsonResponse
|
|
|
{
|
|
|
Success = result.Success,
|
|
|
Message = result.Message,
|
|
|
Data = MsOpTruckBulkDAL.GetData(" op_truck_bulk.BSNO='" + BSNO + "'")
|
|
|
};
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
if (iscreatecustno == true)
|
|
|
MsSysBillNoSetDAL.DeleteBsNo(headData.CUSTNO);
|
|
|
var jsonRespose = new JsonResponse { Success = false, Message = errorstr };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public ContentResult SubmitAuditList(string data)
|
|
|
{
|
|
|
var headList = JsonConvert.Deserialize<List<MsOpTruckBulk>>(data);
|
|
|
var modb = new ModelObjectDB();
|
|
|
DBResult result = MsOpTruckBulkDAL.SubmitAudit(Convert.ToString(Session["USERID"]), headList, Convert.ToString(Session["COMPANYID"]));
|
|
|
if (result.Success == true)
|
|
|
{
|
|
|
if (headList != null)
|
|
|
{
|
|
|
foreach (var headData in headList)
|
|
|
{
|
|
|
if (headData.TRANSTYPE == "集装箱")
|
|
|
{
|
|
|
var iscreatefee = MsOpTruckBulkDAL.GetCreateFeeCount(headData.BSNO);
|
|
|
if (headData.CONTRACTNO != "" && headData.TEMPLATENAME != "" && !iscreatefee)
|
|
|
{
|
|
|
var bodylist = MsOpTruckBulkDAL.GetBodyList("BSNO='" + headData.BSNO + "'");
|
|
|
result = MsOpTruckBulkDAL.CreateCtnFeeBl(headData, bodylist, Convert.ToString(Session["USERID"]));
|
|
|
}
|
|
|
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
if (headData.CONTRACTNO != "" && headData.TEMPLATENAME != "" && headData.UNITPRICE == headData.ORUNITPRICE && headData.FREIGHT == headData.ORFREIGHT)
|
|
|
result = MsOpTruckBulkDAL.CreateFeeBl(headData, Convert.ToString(Session["USERID"]));
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
}
|
|
|
var jsonRespose = new JsonResponse { Success = result.Success, Message = result.Message };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
|
|
|
public ContentResult SubmitAuditBack(string data, string data2)
|
|
|
{
|
|
|
if (Convert.ToString(Session["COMPANYID"]).ToString().Trim() == "" || Convert.ToString(Session["USERID"]).ToString().Trim() == "" || Convert.ToString(Session["CODENAME"]).ToString().Trim() == "" || Convert.ToString(Session["SHOWNAME"]).ToString().Trim() == "" || Convert.ToString(Session["DEPTNAME"]).ToString().Trim() == "")
|
|
|
{
|
|
|
var jsonRespose2 = new JsonResponse { Success = false, Message = "登录超时，请退出系统重新登录！" };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose2) };
|
|
|
}
|
|
|
//
|
|
|
var dataall = data.Replace("}", ",") + data2.Replace("{", "");
|
|
|
var headData = JsonConvert.Deserialize<MsOpTruckBulk>(dataall);
|
|
|
|
|
|
var isfee = MsOpTruckBulkDAL.GetFeeCount(headData.BSNO, " (SETTLEMENT<>0 or INVOICE<>0 or ORDERAMOUNT<>0 or ORDERINVOICE<>0) ");
|
|
|
if (isfee)
|
|
|
{
|
|
|
var jsonRespose2 = new JsonResponse { Success = false, Message = "此票生成费用已做申请或结算或开票,不能驳回！" };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose2) };
|
|
|
}
|
|
|
|
|
|
var headList = new List<MsOpTruckBulk>();
|
|
|
headList.Add(headData);
|
|
|
var result = MsOpTruckBulkDAL.SubmitAuditBack(Convert.ToString(Session["USERID"]), headList);
|
|
|
if (result.Success == true)
|
|
|
{
|
|
|
|
|
|
result = MsOpTruckBulkDAL.DelCreateFeeBl(headData, Convert.ToString(Session["USERID"]));
|
|
|
|
|
|
}
|
|
|
|
|
|
var jsonRespose = new JsonResponse
|
|
|
{
|
|
|
Success = result.Success,
|
|
|
Message = result.Message,
|
|
|
Data = MsOpTruckBulkDAL.GetData(" op_truck_bulk.BSNO='" + headData.BSNO + "'")
|
|
|
};
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
|
|
|
public ContentResult SubmitAuditBackList(string data)
|
|
|
{
|
|
|
var headList = JsonConvert.Deserialize<List<MsOpTruckBulk>>(data);
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
var modb = new ModelObjectDB();
|
|
|
DBResult result = MsOpTruckBulkDAL.SubmitAuditBack(Convert.ToString(Session["USERID"]), headList);
|
|
|
if (result.Success == true)
|
|
|
{
|
|
|
if (headList != null)
|
|
|
{
|
|
|
foreach (var headData in headList)
|
|
|
{
|
|
|
var isfee = MsOpTruckBulkDAL.GetFeeCount(headData.BSNO, " (SETTLEMENT<>0 or INVOICE<>0 or ORDERAMOUNT<>0 or ORDERINVOICE<>0) ");
|
|
|
if (!isfee)
|
|
|
result = MsOpTruckBulkDAL.DelCreateFeeBl(headData, Convert.ToString(Session["USERID"]));
|
|
|
}
|
|
|
}
|
|
|
|
|
|
}
|
|
|
|
|
|
var jsonRespose = new JsonResponse { Success = result.Success, Message = result.Message };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
|
|
|
|
|
|
public ContentResult Audit(string data, string data2)
|
|
|
{
|
|
|
if (Convert.ToString(Session["COMPANYID"]).ToString().Trim() == "" || Convert.ToString(Session["USERID"]).ToString().Trim() == "" || Convert.ToString(Session["CODENAME"]).ToString().Trim() == "" || Convert.ToString(Session["SHOWNAME"]).ToString().Trim() == "" || Convert.ToString(Session["DEPTNAME"]).ToString().Trim() == "")
|
|
|
{
|
|
|
var jsonRespose2 = new JsonResponse { Success = false, Message = "登录超时，请退出系统重新登录！" };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose2) };
|
|
|
}
|
|
|
//
|
|
|
var dataall = data.Replace("}", ",") + data2.Replace("{", "");
|
|
|
var headData = JsonConvert.Deserialize<MsOpTruckBulk>(dataall);
|
|
|
var headList = new List<MsOpTruckBulk>();
|
|
|
headList.Add(headData);
|
|
|
var result = MsOpTruckBulkDAL.AuditList(headList, Convert.ToString(Session["USERID"]));
|
|
|
if (result.Success) {
|
|
|
List<MsOpTruckBulk> AuditList = result.Data as List<MsOpTruckBulk>;
|
|
|
if (AuditList != null)
|
|
|
{
|
|
|
foreach (var enumValue in AuditList)
|
|
|
{
|
|
|
if (enumValue.TRANSTYPE == "集装箱")
|
|
|
{
|
|
|
if (enumValue.ISFEE != "1")
|
|
|
{
|
|
|
var bodylist = MsOpTruckBulkDAL.GetBodyList("BSNO='" + enumValue.BSNO + "'");
|
|
|
result = MsOpTruckBulkDAL.CreateCtnFeeBl(enumValue, bodylist, Convert.ToString(Session["USERID"]));
|
|
|
}
|
|
|
|
|
|
}
|
|
|
else
|
|
|
MsOpTruckBulkDAL.CreateFeeBl(enumValue, Convert.ToString(Session["USERID"]));
|
|
|
}
|
|
|
}
|
|
|
|
|
|
}
|
|
|
|
|
|
var jsonRespose = new JsonResponse
|
|
|
{
|
|
|
Success = result.Success,
|
|
|
Message = result.Message,
|
|
|
Data = MsOpTruckBulkDAL.GetData(" op_truck_bulk.BSNO='" + headData.BSNO + "'")
|
|
|
};
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
|
|
|
public ContentResult AuditList(string data)
|
|
|
{
|
|
|
var headList = JsonConvert.Deserialize<List<MsOpTruckBulk>>(data);
|
|
|
var modb = new ModelObjectDB();
|
|
|
DBResult result = MsOpTruckBulkDAL.AuditList(headList,Convert.ToString(Session["USERID"]));
|
|
|
if (result.Success)
|
|
|
{
|
|
|
List<MsOpTruckBulk> AuditList = result.Data as List<MsOpTruckBulk>;
|
|
|
if (AuditList != null)
|
|
|
{
|
|
|
foreach (var enumValue in AuditList)
|
|
|
{
|
|
|
if (enumValue.TRANSTYPE == "集装箱")
|
|
|
{
|
|
|
if (enumValue.ISFEE != "1")
|
|
|
{
|
|
|
var bodylist = MsOpTruckBulkDAL.GetBodyList("BSNO='" + enumValue.BSNO + "'");
|
|
|
result = MsOpTruckBulkDAL.CreateCtnFeeBl(enumValue, bodylist, Convert.ToString(Session["USERID"]));
|
|
|
}
|
|
|
|
|
|
}
|
|
|
else
|
|
|
MsOpTruckBulkDAL.CreateFeeBl(enumValue, Convert.ToString(Session["USERID"]));
|
|
|
}
|
|
|
}
|
|
|
|
|
|
}
|
|
|
var jsonRespose = new JsonResponse { Success = result.Success, Message = result.Message };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
public ContentResult AuditBack(string data, string data2,string reason)
|
|
|
{
|
|
|
if (Convert.ToString(Session["COMPANYID"]).ToString().Trim() == "" || Convert.ToString(Session["USERID"]).ToString().Trim() == "" || Convert.ToString(Session["CODENAME"]).ToString().Trim() == "" || Convert.ToString(Session["SHOWNAME"]).ToString().Trim() == "" || Convert.ToString(Session["DEPTNAME"]).ToString().Trim() == "")
|
|
|
{
|
|
|
var jsonRespose2 = new JsonResponse { Success = false, Message = "登录超时，请退出系统重新登录！" };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose2) };
|
|
|
}
|
|
|
//
|
|
|
var dataall = data.Replace("}", ",") + data2.Replace("{", "");
|
|
|
var headData = JsonConvert.Deserialize<MsOpTruckBulk>(dataall);
|
|
|
|
|
|
var isfee = MsOpTruckBulkDAL.GetFeeCount(headData.BSNO, " CARGO_GID='" + headData.BSNO + "' and (SETTLEMENT<>0 or INVOICE<>0 or ORDERAMOUNT<>0 or ORDERINVOICE<>0) ");
|
|
|
if (isfee)
|
|
|
{
|
|
|
var jsonRespose2 = new JsonResponse { Success = false, Message = "此票生成费用已做申请或结算或开票,不能驳回！" };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose2) };
|
|
|
}
|
|
|
|
|
|
|
|
|
var headList = new List<MsOpTruckBulk>();
|
|
|
headList.Add(headData);
|
|
|
var result = MsOpTruckBulkDAL.AuditBackList(headList, Convert.ToString(Session["USERID"]),reason);
|
|
|
MsOpTruckBulkDAL.DelCreateFeeBl(headData, Convert.ToString(Session["USERID"]));
|
|
|
|
|
|
var jsonRespose = new JsonResponse
|
|
|
{
|
|
|
Success = result.Success,
|
|
|
Message = result.Message,
|
|
|
Data = MsOpTruckBulkDAL.GetData(" op_truck_bulk.BSNO='" + headData.BSNO + "'")
|
|
|
};
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
|
|
|
public ContentResult AuditBackList(string data, string reason)
|
|
|
{
|
|
|
var headList = JsonConvert.Deserialize<List<MsOpTruckBulk>>(data);
|
|
|
var modb = new ModelObjectDB();
|
|
|
if (headList != null)
|
|
|
{
|
|
|
foreach (var headData in headList)
|
|
|
{
|
|
|
var isfee = MsOpTruckBulkDAL.GetFeeCount(headData.BSNO, " CARGO_GID='" + headData.BSNO + "' and (SETTLEMENT<>0 or INVOICE<>0 or ORDERAMOUNT<>0 or ORDERINVOICE<>0) ");
|
|
|
if (isfee)
|
|
|
{
|
|
|
var jsonRespose2 = new JsonResponse { Success = false, Message = "此票生成费用已做申请或结算或开票,不能驳回！" };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose2) };
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
|
|
|
DBResult result = MsOpTruckBulkDAL.AuditBackList(headList, Convert.ToString(Session["USERID"]), reason);
|
|
|
if (result.Success == true)
|
|
|
{
|
|
|
if (headList != null)
|
|
|
{
|
|
|
foreach (var headData in headList)
|
|
|
{
|
|
|
var isfee = MsOpTruckBulkDAL.GetFeeCount(headData.BSNO, " (SETTLEMENT<>0 or INVOICE<>0 or ORDERAMOUNT<>0 or ORDERINVOICE<>0) ");
|
|
|
if (!isfee)
|
|
|
result = MsOpTruckBulkDAL.DelCreateFeeBl(headData, Convert.ToString(Session["USERID"]));
|
|
|
}
|
|
|
}
|
|
|
|
|
|
}
|
|
|
var jsonRespose = new JsonResponse { Success = result.Success, Message = result.Message };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
#endregion
|
|
|
public List<string> ExcelSheetName(string filepath,string ExcelExt)
|
|
|
{
|
|
|
var al = new List<string>();
|
|
|
|
|
|
try
|
|
|
{
|
|
|
string strConn;
|
|
|
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties="+ ExcelExt;
|
|
|
if (filepath.ToLower().IndexOf(".xlsx") > 0 || filepath.ToLower().IndexOf(".xlsm") > 0)
|
|
|
{
|
|
|
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath +
|
|
|
";Extended Properties=\"Excel 12.0 Xml;HDR=Yes\"";
|
|
|
|
|
|
}
|
|
|
OleDbConnection conn = new OleDbConnection(strConn);
|
|
|
conn.Open();
|
|
|
System.Data.DataTable sheetNames = conn.GetOleDbSchemaTable
|
|
|
(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
|
|
|
conn.Close();
|
|
|
|
|
|
foreach (DataRow dr in sheetNames.Rows)
|
|
|
{
|
|
|
al.Add(dr[2].ToString());
|
|
|
}
|
|
|
}
|
|
|
catch (Exception)
|
|
|
{
|
|
|
return new List<string>();
|
|
|
}
|
|
|
|
|
|
return al;
|
|
|
}
|
|
|
|
|
|
|
|
|
[HttpPost]
|
|
|
public ContentResult ImportBs()
|
|
|
{
|
|
|
if (Convert.ToString(Session["COMPANYID"]).ToString().Trim() == "" || Convert.ToString(Session["USERID"]).ToString().Trim() == "" || Convert.ToString(Session["CODENAME"]).ToString().Trim() == "" || Convert.ToString(Session["SHOWNAME"]).ToString().Trim() == "" || Convert.ToString(Session["DEPTNAME"]).ToString().Trim() == "")
|
|
|
{
|
|
|
var jsonRespose2 = new JsonResponse { Success = false, Message = "登录超时，请退出系统重新登录！" };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose2) };
|
|
|
}
|
|
|
|
|
|
var jsonRespose = new JsonResponse { Success = false, Message = "" };
|
|
|
|
|
|
if (Request.Files.Count != 1)
|
|
|
{
|
|
|
jsonRespose.Success = false;
|
|
|
jsonRespose.Message = "请选择上传的文件";
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
|
|
|
var file = Request.Files["LoadExcel"];
|
|
|
if (file == null)
|
|
|
{
|
|
|
jsonRespose.Success = false;
|
|
|
jsonRespose.Message = "上传文件发生未知错误，请重新上传";
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
if (Convert.ToString(Session["COMPANYID"]).ToString().Trim() == "" || Convert.ToString(Session["USERID"]).ToString().Trim() == "" || Convert.ToString(Session["CODENAME"]).ToString().Trim() == "" || Convert.ToString(Session["SHOWNAME"]).ToString().Trim() == "" || Convert.ToString(Session["DEPTNAME"]).ToString().Trim() == "")
|
|
|
{
|
|
|
var jsonRespose2 = new JsonResponse { Success = false, Message = "登录超时，请退出系统重新登录！" };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose2) };
|
|
|
}
|
|
|
string ext = Path.GetExtension(file.FileName).ToLower();
|
|
|
if (ext == ".asp" || ext == ".aspx")
|
|
|
{
|
|
|
jsonRespose.Success = false;
|
|
|
jsonRespose.Message = "不允许上传ASP或ASPX文件";
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
|
|
|
var path = Server.MapPath("../../UploadFiles/BulkDetail");
|
|
|
|
|
|
if (!Directory.Exists(path))
|
|
|
{
|
|
|
Directory.CreateDirectory(path);
|
|
|
}
|
|
|
|
|
|
var size = file.ContentLength;
|
|
|
var name = Path.GetFileName(file.FileName);
|
|
|
var bsno = Request.Form["bsno"];
|
|
|
|
|
|
var usercode = CookieConfig.GetCookie_UserCode(Request);
|
|
|
string filename = path + "\\" + usercode + DateTime.Now.ToString("yyyyMMddHHmmssfff") + name;
|
|
|
|
|
|
if (System.IO.File.Exists(filename))
|
|
|
{
|
|
|
System.IO.File.Delete(filename);
|
|
|
}
|
|
|
file.SaveAs(filename);
|
|
|
|
|
|
if (!System.IO.File.Exists(filename))
|
|
|
{
|
|
|
jsonRespose.Success = false;
|
|
|
jsonRespose.Message = "上传的Excel不包含数据01";
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
|
|
|
//var ExcelExt = "";
|
|
|
//try
|
|
|
//{
|
|
|
// ExcelExt = WebConfigurationManager.AppSettings["ExcelExt"].ToString();
|
|
|
//}
|
|
|
//catch
|
|
|
//{
|
|
|
|
|
|
//}
|
|
|
//if (ExcelExt == "" || ExcelExt == null) ExcelExt = "Excel 8.0;HDR=Yes;IMEX=1";
|
|
|
|
|
|
|
|
|
//List<string> sheets = ExcelSheetName(filename, ExcelExt);
|
|
|
//if (sheets.Count == 0)
|
|
|
//{
|
|
|
// jsonRespose.Success = false;
|
|
|
// jsonRespose.Message = "上传的Excel不包含数据02";
|
|
|
// return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
//}
|
|
|
|
|
|
try
|
|
|
{
|
|
|
//var sheetname = sheets[0];
|
|
|
//string excelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename +
|
|
|
// ";Extended Properties="+ ExcelExt;
|
|
|
//if (filename.ToLower().IndexOf(".xlsx") > 0)
|
|
|
//{
|
|
|
// excelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename +
|
|
|
// ";Extended Properties=\"Excel 12.0 Xml;IMEX=1;HDR=Yes\"";
|
|
|
|
|
|
//}
|
|
|
//OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "A1:AA]", excelConn);
|
|
|
//DataSet ds = new DataSet();
|
|
|
//oada.Fill(ds);
|
|
|
|
|
|
//if (ds.Tables.Count == 0)
|
|
|
//{
|
|
|
// jsonRespose.Success = false;
|
|
|
// jsonRespose.Message = "上传的Excel不包含数据03";
|
|
|
// return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
//}
|
|
|
|
|
|
//var table = ds.Tables[0];
|
|
|
|
|
|
if (ext != ".xls" && ext != ".xlsx")
|
|
|
{
|
|
|
jsonRespose.Success = false;
|
|
|
jsonRespose.Message = "上传的文件不是Excel文件";
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
var strMsg = "";
|
|
|
System.Data.DataTable table = ExcelHelper.ExcelToDatatable(file.InputStream, ext, out strMsg);
|
|
|
if (!string.IsNullOrEmpty(strMsg))
|
|
|
{
|
|
|
jsonRespose.Success = false;
|
|
|
jsonRespose.Message = strMsg;
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
|
|
|
var message = string.Empty;
|
|
|
List<MsOpTruckBulk> headList;
|
|
|
|
|
|
bool isSucess = false;
|
|
|
isSucess = MsOpTruckBulkDAL.ImportBsData(Request, table, Convert.ToString(Session["SHOWNAME"]), Convert.ToString(Session["USERID"]), Convert.ToString(Session["COMPANYID"]), out message, out headList);
|
|
|
|
|
|
|
|
|
if (!isSucess)
|
|
|
{
|
|
|
jsonRespose.Success = false;
|
|
|
jsonRespose.Message = message;
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
var json = JsonConvert.Serialize(new { success = true, Message = "上传成功，共生成" + Convert.ToString(headList.Count) + "个托单", data = headList.ToList() });
|
|
|
return new ContentResult() { Content = json };
|
|
|
}
|
|
|
catch (Exception e)
|
|
|
{
|
|
|
|
|
|
jsonRespose.Success = false;
|
|
|
jsonRespose.Message = "读取Excel文件出错，请确认文件正确性"+e.Message;
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
[HttpPost]
|
|
|
public ContentResult ImportCtnBs()
|
|
|
{
|
|
|
|
|
|
if (Convert.ToString(Session["COMPANYID"]).ToString().Trim() == "" || Convert.ToString(Session["USERID"]).ToString().Trim() == "" || Convert.ToString(Session["CODENAME"]).ToString().Trim() == "" || Convert.ToString(Session["SHOWNAME"]).ToString().Trim() == "" || Convert.ToString(Session["DEPTNAME"]).ToString().Trim() == "")
|
|
|
{
|
|
|
var jsonRespose2 = new JsonResponse { Success = false, Message = "登录超时，请退出系统重新登录！" };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose2) };
|
|
|
}
|
|
|
|
|
|
var jsonRespose = new JsonResponse { Success = false, Message = "" };
|
|
|
|
|
|
if (Request.Files.Count != 1)
|
|
|
{
|
|
|
jsonRespose.Success = false;
|
|
|
jsonRespose.Message = "请选择上传的文件";
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
|
|
|
var file = Request.Files["LoadExcel"];
|
|
|
if (file == null)
|
|
|
{
|
|
|
jsonRespose.Success = false;
|
|
|
jsonRespose.Message = "上传文件发生未知错误，请重新上传";
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
if (Convert.ToString(Session["COMPANYID"]).ToString().Trim() == "" || Convert.ToString(Session["USERID"]).ToString().Trim() == "" || Convert.ToString(Session["CODENAME"]).ToString().Trim() == "" || Convert.ToString(Session["SHOWNAME"]).ToString().Trim() == "" || Convert.ToString(Session["DEPTNAME"]).ToString().Trim() == "")
|
|
|
{
|
|
|
var jsonRespose2 = new JsonResponse { Success = false, Message = "登录超时，请退出系统重新登录！" };
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose2) };
|
|
|
}
|
|
|
string ext = Path.GetExtension(file.FileName).ToLower();
|
|
|
if (ext == ".asp" || ext == ".aspx")
|
|
|
{
|
|
|
jsonRespose.Success = false;
|
|
|
jsonRespose.Message = "不允许上传ASP或ASPX文件";
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
|
|
|
var path = Server.MapPath("../../UploadFiles/BulkDetail");
|
|
|
|
|
|
if (!Directory.Exists(path))
|
|
|
{
|
|
|
Directory.CreateDirectory(path);
|
|
|
}
|
|
|
|
|
|
var size = file.ContentLength;
|
|
|
var name = Path.GetFileName(file.FileName);
|
|
|
var bsno = Request.Form["bsno"];
|
|
|
|
|
|
var usercode = CookieConfig.GetCookie_UserCode(Request);
|
|
|
string filename = path + "\\" + usercode + DateTime.Now.ToString("yyyyMMddHHmmssfff") + name;
|
|
|
|
|
|
if (System.IO.File.Exists(filename))
|
|
|
{
|
|
|
System.IO.File.Delete(filename);
|
|
|
}
|
|
|
file.SaveAs(filename);
|
|
|
|
|
|
if (!System.IO.File.Exists(filename))
|
|
|
{
|
|
|
jsonRespose.Success = false;
|
|
|
jsonRespose.Message = "上传的Excel不包含数据01";
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
|
|
|
//var ExcelExt = "";
|
|
|
//try
|
|
|
//{
|
|
|
// ExcelExt = WebConfigurationManager.AppSettings["ExcelExt"].ToString();
|
|
|
//}
|
|
|
//catch
|
|
|
//{
|
|
|
|
|
|
//}
|
|
|
//if (ExcelExt == "" || ExcelExt == null) ExcelExt = "Excel 8.0;HDR=Yes;IMEX=1";
|
|
|
|
|
|
|
|
|
//List<string> sheets = ExcelSheetName(filename, ExcelExt);
|
|
|
//if (sheets.Count == 0)
|
|
|
//{
|
|
|
// jsonRespose.Success = false;
|
|
|
// jsonRespose.Message = "上传的Excel不包含数据02";
|
|
|
// return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
//}
|
|
|
|
|
|
try
|
|
|
{
|
|
|
//var sheetname = sheets[0];
|
|
|
//string excelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename +
|
|
|
// ";Extended Properties="+ ExcelExt;
|
|
|
//if (filename.ToLower().IndexOf(".xlsx") > 0)
|
|
|
//{
|
|
|
// excelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename +
|
|
|
// ";Extended Properties=\"Excel 12.0 Xml;IMEX=1;HDR=Yes\"";
|
|
|
|
|
|
//}
|
|
|
//OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "A1:AA]", excelConn);
|
|
|
//DataSet ds = new DataSet();
|
|
|
//oada.Fill(ds);
|
|
|
|
|
|
//if (ds.Tables.Count == 0)
|
|
|
//{
|
|
|
// jsonRespose.Success = false;
|
|
|
// jsonRespose.Message = "上传的Excel不包含数据03";
|
|
|
// return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
//}
|
|
|
|
|
|
//var table = ds.Tables[0];
|
|
|
|
|
|
if (ext != ".xls" && ext != ".xlsx")
|
|
|
{
|
|
|
jsonRespose.Success = false;
|
|
|
jsonRespose.Message = "上传的文件不是Excel文件";
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
var strMsg = "";
|
|
|
System.Data.DataTable table = ExcelHelper.ExcelToDatatable(file.InputStream, ext, out strMsg);
|
|
|
if (!string.IsNullOrEmpty(strMsg))
|
|
|
{
|
|
|
jsonRespose.Success = false;
|
|
|
jsonRespose.Message = strMsg;
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
|
|
|
|
|
|
var message = string.Empty;
|
|
|
List<MsOpTruckBulk> headList;
|
|
|
|
|
|
bool isSucess = false;
|
|
|
isSucess = MsOpTruckBulkDAL.ImportBsCtnData(Request, table, Convert.ToString(Session["SHOWNAME"]), Convert.ToString(Session["USERID"]), Convert.ToString(Session["COMPANYID"]), out message, out headList);
|
|
|
|
|
|
|
|
|
if (!isSucess)
|
|
|
{
|
|
|
jsonRespose.Success = false;
|
|
|
jsonRespose.Message = message;
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
var json = JsonConvert.Serialize(new { success = true, Message = "上传成功，共生成" + Convert.ToString(headList.Count) + "个托单", data = headList.ToList() });
|
|
|
return new ContentResult() { Content = json };
|
|
|
}
|
|
|
catch (Exception e)
|
|
|
{
|
|
|
|
|
|
jsonRespose.Success = false;
|
|
|
jsonRespose.Message = "读取Excel文件出错，请确认文件正确性" + e.Message;
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
}
|
|
|
[SqlKeyWordsFilter(Type = "Action")]//sql 防注入过滤器
|
|
|
public ContentResult RptLRData(int start, int limit, string condition,string sort, string printstr, string sortstr)
|
|
|
{
|
|
|
var strDa = MsOpTruckBulkDAL.GetRpRangDAStr("index", Convert.ToString(Session["USERID"]), Convert.ToString(Session["SHOWNAME"]), Convert.ToString(Session["COMPANYID"]));
|
|
|
|
|
|
if (!string.IsNullOrEmpty(strDa))
|
|
|
{
|
|
|
if (!string.IsNullOrEmpty(condition))
|
|
|
{
|
|
|
condition = condition + " and " + strDa;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
condition = strDa;
|
|
|
}
|
|
|
}
|
|
|
var strSql = new StringBuilder();
|
|
|
strSql.Append("SELECT * ");
|
|
|
strSql.Append(",INVOICEBILLNO = stuff((SELECT distinct t.BILLNO + ',' FROM CH_FEE_DO AS t WHERE t.CATEGORY = 6 and t.BSNO = B.BSNO FOR xml path('')), 1, 0, '') ");
|
|
|
strSql.Append(" FROM (SELECT B.BSNO,B.CUSTOMERNAME,B.ORDERNO,B.ORDERNO1,B.GOODSNAME,B.PORTLOAD,B.PORTDISCHARGE,B.ETD,B.ETA,B.TRANSTYPE,B.PKGS,B.KGS,B.NETWEIGHT,B.CNTRTOTAL ");
|
|
|
strSql.Append(",case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(B.FREIGHT/B.KGS*P.PCKGS,2) as numeric(20,2)) END FREIGHT");
|
|
|
strSql.Append(",case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.KGS*P.PCKGS+(P.TTLDR*P.PCKGS),2) as numeric(20,2)) END TTLDR");
|
|
|
strSql.Append(",case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(G.TTLOTDR/B.KGS*P.PCKGS,2) as numeric(20,2)) END TTLOTDR");
|
|
|
strSql.Append(",case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(G.TTKHFDR/B.KGS*P.PCKGS,2) as numeric(20,2)) END TTKHFDR");
|
|
|
strSql.Append(",(case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(G.TTKHFCR/B.KGS*P.PCKGS,2) as numeric(20,2)) END)+(P.TTKHFCR*P.PCKGS) TTKHFCR");
|
|
|
|
|
|
strSql.Append(",B.TRADETYPE,B.MBLNO,P.ETD PCETD,P.CREATETIME,P.DELAYDAYS,P.TRUCKER,P.DRIVER,P.TRUCKNO,P.DRIVERTEL,P.BSNO PCBSNO,P.TRANSSTATUS,P.PCKGS,P.PCCTNNUM,P.PCPKGS,P.PCCBM,P.PCNETWEIGHT,B.SALE,P.CUSTSERVICE,P.REMARK PCREMARK");
|
|
|
strSql.Append(",P.FREIGHT*P.PCKGS PCFREIGHT,P.TAXRATE,P.TTLFREIGHT*P.PCKGS PCTTLFREIGHT,P.TTLOTCR*P.PCKGS TTLOTCR,P.TTLCR*P.PCKGS+(case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(G.TTKHFCR/B.KGS*P.PCKGS,2) as numeric(20,2)) END) TTLCR,");
|
|
|
strSql.Append("(case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.KGS*P.PCKGS+(P.TTLDR*P.PCKGS),2) as numeric(20,2)) END)-((P.TTLCR*P.PCKGS)+(case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(G.TTKHFCR/B.KGS*P.PCKGS,2) as numeric(20,2)) END)) AS PROFIT");
|
|
|
strSql.Append(",CASE WHEN (case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.KGS*P.PCKGS,2) as numeric(20,2)) END)=0 THEN 0 ELSE cast(round((((case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.KGS*P.PCKGS+(P.TTLDR*P.PCKGS),2) as numeric(20,2)) END)-");
|
|
|
strSql.Append("((P.TTLCR*P.PCKGS)+(case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(G.TTKHFCR/B.KGS*P.PCKGS,2) as numeric(20,2)) END)))/(case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.KGS*P.PCKGS+(P.TTLDR*P.PCKGS),2) as numeric(20,2)) END)*100),2) AS numeric(20,2)) END PROFITRATE ");
|
|
|
strSql.Append(" ,(SELECT DESCRIPTION FROM info_client WHERE SHORTNAME=B.CUSTOMERNAME) AS CUSTOMERNAMEREF");
|
|
|
strSql.Append(" ,(SELECT DESCRIPTION FROM info_client WHERE SHORTNAME=P.TRUCKER) AS TRUCKERREF,ISTEMPLATENAME");
|
|
|
strSql.Append(" FROM op_truck_bulk B LEFT JOIN v_op_gain_dr_INV_truck G ON (G.BSNO=B.BSNO)");
|
|
|
strSql.Append(" LEFT JOIN (select P.BSNO,P.TRANSSTATUS,P.ETD,P.CREATETIME,datediff(day,P.ETD,P.CREATETIME) DELAYDAYS,P.TRUCKER,P.DRIVER,P.TRUCKNO,P.DRIVERTEL,P.REMARK,CASE WHEN P.KGS=0 THEN 0 ELSE P.FREIGHT/P.KGS END FREIGHT,");
|
|
|
strSql.Append(" P.TAXRATE,CASE WHEN P.KGS=0 THEN 0 ELSE P.TTLFREIGHT/P.KGS END TTLFREIGHT,CASE WHEN P.KGS=0 THEN 0 ELSE G.TTLOTCR/P.KGS END TTLOTCR,CASE WHEN P.KGS=0 THEN 0 ELSE G.TTKHFCR/P.KGS END TTKHFCR");
|
|
|
strSql.Append(",CASE WHEN P.KGS=0 THEN 0 ELSE G.TTLCR/P.KGS END TTLCR,CASE WHEN P.KGS=0 THEN 0 ELSE G.TTLDR/P.KGS END TTLDR,PD.LINKBSNO,PD.PCKGS,PD.PCCTNNUM,PD.PCPKGS,PD.PCCBM,PD.PCNETWEIGHT ");
|
|
|
strSql.Append(",P.SALE,P.CUSTSERVICE,P.KGS TTLPCKGS,CASE WHEN ISNULL(P.TEMPLATENAME,'')='' THEN '是' ELSE CASE WHEN (SELECT isnull(COUNT(1),0) FROM Cust_Truck_feetemplate WHERE CONTRACTNO<>'' AND TEMPLATENAME=P.TEMPLATENAME)>0 THEN '否' ELSE '是' END END ISTEMPLATENAME ");
|
|
|
strSql.Append(" from (select BSNO,LINKBSNO,SUM(KGS)PCKGS,SUM(CTNNUM) PCCTNNUM,SUM(PKGS) PCPKGS,SUM(CBM) PCCBM,SUM(NETWEIGHT) PCNETWEIGHT from op_truck_bulk_pc_detail pd GROUP BY BSNO,LINKBSNO) PD");
|
|
|
strSql.Append(" LEFT JOIN op_truck_bulk_pc p ON (P.BSNO=pD.BSNO) LEFT JOIN v_op_gain_dr_INV_truck G ON (G.BSNO=p.BSNO)) P ON (P.LINKBSNO=B.BSNO)");
|
|
|
strSql.Append(" where B.transtype='普运' ");
|
|
|
if (!string.IsNullOrEmpty(condition))
|
|
|
{
|
|
|
strSql.Append(" and " + condition);
|
|
|
}
|
|
|
strSql.Append(") B WHERE TRANSSTATUS IN ('审核通过','已到货','已回单','已回单','在途') ");
|
|
|
|
|
|
|
|
|
if ((!string.IsNullOrEmpty(printstr)) && (printstr == "true"))
|
|
|
{
|
|
|
|
|
|
if (!string.IsNullOrEmpty(sortstr))
|
|
|
{
|
|
|
strSql.Append(" order by " + sortstr);
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
|
|
|
strSql.Append(" order by ETD DESC");
|
|
|
|
|
|
}
|
|
|
var jsonRespose = new JsonResponse
|
|
|
{
|
|
|
Success = true,
|
|
|
Message = "完成",
|
|
|
Data = strSql.ToString()
|
|
|
};
|
|
|
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
|
|
|
var sortstring = DatasetSort.Getsortstring(sort);
|
|
|
if (!string.IsNullOrEmpty(sortstring))
|
|
|
{
|
|
|
strSql.Append(" order by " + sortstring);
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
|
|
|
strSql.Append(" order by ETD DESC");
|
|
|
|
|
|
}
|
|
|
|
|
|
var dbparams = new List<CustomDbParamter>();
|
|
|
|
|
|
var paramps_sSQL = new CustomDbParamter();
|
|
|
paramps_sSQL.ParameterName = "@sSQL";
|
|
|
paramps_sSQL.DbType = DbType.String;
|
|
|
paramps_sSQL.Direction = ParameterDirection.Input;
|
|
|
paramps_sSQL.Value = strSql.ToString();
|
|
|
dbparams.Add(paramps_sSQL);
|
|
|
|
|
|
|
|
|
|
|
|
var dbRptResult = PubSysDAL.GetMsSqlPrcDataSet("sMsExesqlQry", dbparams, "Result_Set");
|
|
|
var json = RptHelper.GetRptJsonResult(start, limit, dbRptResult, "Result_Set", true);
|
|
|
return new ContentResult() { Content = json };
|
|
|
}
|
|
|
}
|
|
|
|
|
|
[SqlKeyWordsFilter(Type = "Action")]//sql 防注入过滤器
|
|
|
public ContentResult RptSumLRData(int start, int limit, string condition, string sort, string printstr, string sortstr)
|
|
|
{
|
|
|
var strDa = MsOpTruckBulkDAL.GetRpRangDAStr("index", Convert.ToString(Session["USERID"]), Convert.ToString(Session["SHOWNAME"]), Convert.ToString(Session["COMPANYID"]));
|
|
|
|
|
|
if (!string.IsNullOrEmpty(strDa))
|
|
|
{
|
|
|
if (!string.IsNullOrEmpty(condition))
|
|
|
{
|
|
|
condition = condition + " and " + strDa;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
condition = strDa;
|
|
|
}
|
|
|
}
|
|
|
var strSql = new StringBuilder();
|
|
|
strSql.Append("SELECT sum(PCKGS) PCKGS,SUM(PCPKGS) PCPKGS,SUM(PCCBM) PCCBM,SUM(PCNETWEIGHT) PCNETWEIGHT,sum(FREIGHT) FREIGHT,sum(TTLDR) TTLDR,sum(TTLCR) TTLCR,SUM(PROFIT) PROFIT,SUM(PCFREIGHT) PCFREIGHT,SUM(PCTTLFREIGHT) PCTTLFREIGHT,SUM(TTLOTCR) TTLOTCR,SUM(TTLOTDR) TTLOTDR,SUM(TTKHFDR) TTKHFDR,SUM(TTKHFCR) TTKHFCR,CASE WHEN SUM(TTLDR)=0 THEN 0 ELSE cast(round(sum(PROFIT)/sum(TTLDR)*100,2) AS numeric(20,2)) END PROFITRATE,sum(DELAYDAYS) DELAYDAYS");
|
|
|
strSql.Append(" FROM (SELECT B.BSNO,B.CUSTOMERNAME,B.ORDERNO,B.ORDERNO1,B.GOODSNAME,B.PORTLOAD,B.PORTDISCHARGE,B.ETD,B.ETA,B.TRANSTYPE,B.PKGS,B.KGS,B.NETWEIGHT,B.CNTRTOTAL ");
|
|
|
strSql.Append(",case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(B.FREIGHT/B.KGS*P.PCKGS,2) as numeric(20,2)) END FREIGHT");
|
|
|
strSql.Append(",case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.KGS*P.PCKGS+(P.TTLDR*P.PCKGS),2) as numeric(20,2)) END TTLDR");
|
|
|
strSql.Append(",case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(G.TTLOTDR/B.KGS*P.PCKGS,2) as numeric(20,2)) END TTLOTDR");
|
|
|
strSql.Append(",(case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(G.TTKHFDR/B.KGS*P.PCKGS,2) as numeric(20,2)) END) TTKHFDR");
|
|
|
strSql.Append(",(case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(G.TTKHFCR/B.KGS*P.PCKGS,2) as numeric(20,2)) END)+(P.TTKHFCR*P.PCKGS) TTKHFCR");
|
|
|
|
|
|
strSql.Append(",B.TRADETYPE,B.MBLNO,P.ETD PCETD,P.DELAYDAYS,P.TRUCKER,P.DRIVER,P.TRUCKNO,P.DRIVERTEL,P.BSNO PCBSNO,P.TRANSSTATUS,P.PCKGS,P.PCCTNNUM,P.PCPKGS,P.PCCBM,P.PCNETWEIGHT,B.SALE,P.CUSTSERVICE,P.REMARK PCREMARK");
|
|
|
strSql.Append(",P.FREIGHT*P.PCKGS PCFREIGHT,P.TAXRATE,P.TTLFREIGHT*P.PCKGS PCTTLFREIGHT,P.TTLOTCR*P.PCKGS TTLOTCR, P.TTLCR*P.PCKGS+(case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(G.TTKHFCR/B.KGS*P.PCKGS,2) as numeric(20,2)) END) TTLCR,");
|
|
|
strSql.Append("(case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.KGS*P.PCKGS+(P.TTLDR*P.PCKGS),2) as numeric(20,2)) END)-((P.TTLCR*P.PCKGS)+(case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(G.TTKHFCR/B.KGS*P.PCKGS,2) as numeric(20,2)) END)) AS PROFIT");
|
|
|
strSql.Append(",CASE WHEN (case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.KGS*P.PCKGS,2) as numeric(20,2)) END)=0 THEN 0 ELSE cast(round((((case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.KGS*P.PCKGS+(P.TTLDR*P.PCKGS),2) as numeric(20,2)) END)");
|
|
|
strSql.Append("-((P.TTLCR*P.PCKGS)+(case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(G.TTKHFCR/B.KGS*P.PCKGS,2) as numeric(20,2)) END)))/(case when ISNULL(B.KGS,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.KGS*P.PCKGS+(P.TTLDR*P.PCKGS),2) as numeric(20,2)) END)*100),2) AS numeric(20,2)) END PROFITRATE ");
|
|
|
strSql.Append(" ,(SELECT DESCRIPTION FROM info_client WHERE SHORTNAME=B.CUSTOMERNAME) AS CUSTOMERNAMEREF");
|
|
|
strSql.Append(" ,(SELECT DESCRIPTION FROM info_client WHERE SHORTNAME=P.TRUCKER) AS TRUCKERREF");
|
|
|
strSql.Append(" FROM op_truck_bulk B LEFT JOIN v_op_gain_dr_INV_truck G ON (G.BSNO=B.BSNO)");
|
|
|
strSql.Append(" LEFT JOIN (select P.BSNO,P.TRANSSTATUS,P.ETD,P.CREATETIME,datediff(day,P.ETD,P.CREATETIME) DELAYDAYS,P.TRUCKER,P.DRIVER,P.TRUCKNO,P.DRIVERTEL,P.REMARK,CASE WHEN P.KGS=0 THEN 0 ELSE P.FREIGHT/P.KGS END FREIGHT,");
|
|
|
strSql.Append(" P.TAXRATE,CASE WHEN P.KGS=0 THEN 0 ELSE P.TTLFREIGHT/P.KGS END TTLFREIGHT,CASE WHEN P.KGS=0 THEN 0 ELSE G.TTLOTCR/P.KGS END TTLOTCR,CASE WHEN P.KGS=0 THEN 0 ELSE G.TTKHFCR/P.KGS END TTKHFCR");
|
|
|
strSql.Append(",CASE WHEN P.KGS=0 THEN 0 ELSE G.TTLCR/P.KGS END TTLCR,CASE WHEN P.KGS=0 THEN 0 ELSE G.TTLDR/P.KGS END TTLDR,PD.LINKBSNO,PD.PCKGS,PD.PCCTNNUM,PD.PCPKGS,PD.PCCBM,PD.PCNETWEIGHT");
|
|
|
strSql.Append(",P.SALE,P.CUSTSERVICE,P.KGS TTLPCKGS from (select BSNO,LINKBSNO,SUM(KGS)PCKGS,SUM(CTNNUM) PCCTNNUM,SUM(PKGS) PCPKGS,SUM(CBM) PCCBM,SUM(NETWEIGHT) PCNETWEIGHT from op_truck_bulk_pc_detail pd GROUP BY BSNO,LINKBSNO) PD");
|
|
|
strSql.Append(" LEFT JOIN op_truck_bulk_pc p ON (P.BSNO=pD.BSNO) LEFT JOIN v_op_gain_dr_INV_truck G ON (G.BSNO=p.BSNO)) P ON (P.LINKBSNO=B.BSNO)");
|
|
|
strSql.Append(" where B.transtype='普运' ");
|
|
|
if (!string.IsNullOrEmpty(condition))
|
|
|
{
|
|
|
strSql.Append(" and " + condition);
|
|
|
}
|
|
|
strSql.Append(") B WHERE TRANSSTATUS IN ('审核通过','已到货','已回单','已回单','在途') ");
|
|
|
|
|
|
|
|
|
if ((!string.IsNullOrEmpty(printstr)) && (printstr == "true"))
|
|
|
{
|
|
|
|
|
|
//if (!string.IsNullOrEmpty(sortstr))
|
|
|
//{
|
|
|
// strSql.Append(" order by " + sortstr);
|
|
|
//}
|
|
|
//else
|
|
|
//{
|
|
|
|
|
|
// strSql.Append(" order by ETD DESC");
|
|
|
|
|
|
//}
|
|
|
var jsonRespose = new JsonResponse
|
|
|
{
|
|
|
Success = true,
|
|
|
Message = "完成",
|
|
|
Data = strSql.ToString()
|
|
|
};
|
|
|
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
|
|
|
//var sortstring = DatasetSort.Getsortstring(sort);
|
|
|
//if (!string.IsNullOrEmpty(sortstring))
|
|
|
//{
|
|
|
// strSql.Append(" order by " + sortstring);
|
|
|
//}
|
|
|
//else
|
|
|
//{
|
|
|
|
|
|
// strSql.Append(" order by ETD DESC");
|
|
|
|
|
|
//}
|
|
|
|
|
|
var dbparams = new List<CustomDbParamter>();
|
|
|
|
|
|
var paramps_sSQL = new CustomDbParamter();
|
|
|
paramps_sSQL.ParameterName = "@sSQL";
|
|
|
paramps_sSQL.DbType = DbType.String;
|
|
|
paramps_sSQL.Direction = ParameterDirection.Input;
|
|
|
paramps_sSQL.Value = strSql.ToString();
|
|
|
dbparams.Add(paramps_sSQL);
|
|
|
|
|
|
|
|
|
|
|
|
var dbRptResult = PubSysDAL.GetMsSqlPrcDataSet("sMsExesqlQry", dbparams, "Result_Set");
|
|
|
var json = RptHelper.GetRptJsonResult(start, limit, dbRptResult, "Result_Set", true);
|
|
|
return new ContentResult() { Content = json };
|
|
|
}
|
|
|
}
|
|
|
[SqlKeyWordsFilter(Type = "Action")]//sql 防注入过滤器
|
|
|
public ContentResult RptCtnLRData(int start, int limit, string condition, string sort, string printstr, string sortstr)
|
|
|
{
|
|
|
var strDa = MsOpTruckBulkDAL.GetRpRangDAStr("index", Convert.ToString(Session["USERID"]), Convert.ToString(Session["SHOWNAME"]), Convert.ToString(Session["COMPANYID"]));
|
|
|
|
|
|
if (!string.IsNullOrEmpty(strDa))
|
|
|
{
|
|
|
if (!string.IsNullOrEmpty(condition))
|
|
|
{
|
|
|
condition = condition + " and " + strDa;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
condition = strDa;
|
|
|
}
|
|
|
}
|
|
|
var strSql = new StringBuilder();
|
|
|
strSql.Append("SELECT * ");
|
|
|
strSql.Append(",INVOICEBILLNO = stuff((SELECT distinct t.BILLNO + ',' FROM CH_FEE_DO AS t WHERE t.CATEGORY = 6 and t.BSNO = B.BSNO FOR xml path('')), 1, 0, '') ");
|
|
|
|
|
|
strSql.Append(" FROM (SELECT B.BSNO,B.CUSTOMERNAME,B.ORDERNO,B.ORDERNO1,B.GOODSNAME,B.PORTLOAD,B.PORTDISCHARGE,B.ETD,B.ETA,B.TRANSTYPE,B.PKGS,B.KGS,B.NETWEIGHT,B.CNTRTOTAL ");
|
|
|
strSql.Append(",case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(B.FREIGHT/B.CTNNUM*P.PCCTNNUM,2) as numeric(20,2)) END FREIGHT");
|
|
|
strSql.Append(",case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.CTNNUM*P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM),2) as numeric(20,2)) END TTLDR");
|
|
|
strSql.Append(",case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTLOTDR/B.CTNNUM*P.PCCTNNUM,2) as numeric(20,2)) END TTLOTDR");
|
|
|
strSql.Append(",case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTKHFDR/B.CTNNUM*P.PCCTNNUM,2) as numeric(20,2)) END TTKHFDR");
|
|
|
strSql.Append(",(case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTKHFCR/B.CTNNUM*P.PCCTNNUM,2) as numeric(20,2)) END)+(P.TTKHFCR*P.PCCTNNUM) TTKHFCR");
|
|
|
|
|
|
strSql.Append(",B.TRADETYPE,B.MBLNO,P.ETA PCETD,P.CREATETIME,P.DELAYDAYS,P.TRUCKER,P.DRIVER,P.TRUCKNO,P.DRIVERTEL,P.BSNO PCBSNO,P.TRANSSTATUS,P.PCKGS,P.PCCTNNUM,B.SALE,P.CUSTSERVICE,P.REMARK PCREMARK");
|
|
|
strSql.Append(",P.FREIGHT*P.PCCTNNUM PCFREIGHT,P.TAXRATE,P.TTLFREIGHT*P.PCCTNNUM PCTTLFREIGHT,P.TTLOTCR*P.PCCTNNUM TTLOTCR,P.TTLCR*P.PCCTNNUM+(case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTKHFCR/B.CTNNUM*P.PCCTNNUM,2) as numeric(20,2)) END) TTLCR");
|
|
|
strSql.Append(",(case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.CTNNUM*P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM),2) as numeric(20,2)) END)-((P.TTLCR*P.PCCTNNUM)+(case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTKHFCR/B.CTNNUM*P.PCCTNNUM,2) as numeric(20,2)) END)) AS PROFIT");
|
|
|
// strSql.Append(",cast(round(CASE WHEN (case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.CTNNUM*P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM),2) as numeric(20,2)) END)=0 THEN 0 ELSE(case when ISNULL(B.CTNNUM, 0) = 0 THEN 0 ELSE cast(round(G.TTLDR / B.CTNNUM * P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM), 2) as numeric(20, 2)) END) - (P.TTLCR * P.PCCTNNUM)/(case when ISNULL(B.CTNNUM, 0)= 0 THEN 0 ELSE cast(round(G.TTLDR / B.CTNNUM * P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM), 2) as numeric(20, 2)) END) END,2) AS numeric(20, 2)) PROFITRATE ");
|
|
|
strSql.Append(",CASE WHEN (case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.CTNNUM*P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM),2) as numeric(20,2)) END)=0 THEN 0 ELSE cast(round((((case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.CTNNUM*P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM),2) as numeric(20,2)) END)");
|
|
|
strSql.Append("-((P.TTLCR*P.PCCTNNUM)+(case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTKHFCR/B.CTNNUM*P.PCCTNNUM,2) as numeric(20,2)) END)))/(case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.CTNNUM*P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM),2) as numeric(20,2)) END)*100),2) AS numeric(20,2)) END PROFITRATE ");
|
|
|
strSql.Append(" ,(SELECT DESCRIPTION FROM info_client WHERE SHORTNAME=B.CUSTOMERNAME) AS CUSTOMERNAMEREF");
|
|
|
strSql.Append(" ,(SELECT DESCRIPTION FROM info_client WHERE SHORTNAME=P.TRUCKER) AS TRUCKERREF,ISTEMPLATENAME");
|
|
|
strSql.Append(" FROM (select *,(select sum(CTNNUM) from op_truck_bulk_detail where BSNO=op_truck_bulk.BSNO) CTNNUM from op_truck_bulk where transtype='集装箱') B LEFT JOIN v_op_gain_dr_INV_truck G ON (G.BSNO=B.BSNO)");
|
|
|
strSql.Append(" LEFT JOIN (select P.BSNO,P.TRANSSTATUS,P.ETD,P.ETA,P.CREATETIME,datediff(day,P.ETA,P.CREATETIME) DELAYDAYS,P.TRUCKER,P.DRIVER,P.TRUCKNO,P.DRIVERTEL,P.REMARK,CASE WHEN P.CTNNUM=0 THEN 0 ELSE P.FREIGHT/P.CTNNUM END FREIGHT,P.TAXRATE");
|
|
|
strSql.Append(",CASE WHEN P.CTNNUM=0 THEN 0 ELSE P.TTLFREIGHT/P.CTNNUM END TTLFREIGHT,CASE WHEN P.CTNNUM=0 THEN 0 ELSE G.TTLOTCR/P.CTNNUM END TTLOTCR,CASE WHEN P.CTNNUM=0 THEN 0 ELSE G.TTKHFCR/P.CTNNUM END TTKHFCR");
|
|
|
strSql.Append(",CASE WHEN P.CTNNUM=0 THEN 0 ELSE G.TTLCR/P.CTNNUM END TTLCR,CASE WHEN P.CTNNUM=0 THEN 0 ELSE G.TTLDR/P.CTNNUM END TTLDR,PD.LINKBSNO,PD.PCKGS,PD.PCCTNNUM");
|
|
|
strSql.Append(",CASE WHEN ISNULL(P.TEMPLATENAME,'')='' THEN '是' ELSE CASE WHEN (SELECT isnull(COUNT(1),0) FROM Cust_Truck_feetemplate WHERE CONTRACTNO<>'' AND TEMPLATENAME=P.TEMPLATENAME)>0 THEN '否' ELSE '是' END END ISTEMPLATENAME ");
|
|
|
|
|
|
strSql.Append(",P.SALE,P.CUSTSERVICE from (select BSNO,LINKBSNO,SUM(KGS)PCKGS,SUM(CTNNUM) PCCTNNUM from op_truck_bulk_pc_detail pd GROUP BY BSNO,LINKBSNO) PD");
|
|
|
|
|
|
strSql.Append(" LEFT JOIN op_truck_bulk_pc p ON (P.BSNO=pD.BSNO) LEFT JOIN v_op_gain_dr_INV_truck G ON (G.BSNO=p.BSNO)) P ON (P.LINKBSNO=B.BSNO)");
|
|
|
strSql.Append(" where B.transtype='集装箱' ");
|
|
|
if (!string.IsNullOrEmpty(condition))
|
|
|
{
|
|
|
strSql.Append(" and " + condition);
|
|
|
}
|
|
|
strSql.Append(") B WHERE TRANSSTATUS IN ('审核通过','已到货','已回单','已回单','在途') ");
|
|
|
|
|
|
|
|
|
if ((!string.IsNullOrEmpty(printstr)) && (printstr == "true"))
|
|
|
{
|
|
|
|
|
|
if (!string.IsNullOrEmpty(sortstr))
|
|
|
{
|
|
|
strSql.Append(" order by " + sortstr);
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
|
|
|
strSql.Append(" order by ETD DESC");
|
|
|
|
|
|
}
|
|
|
var jsonRespose = new JsonResponse
|
|
|
{
|
|
|
Success = true,
|
|
|
Message = "完成",
|
|
|
Data = strSql.ToString()
|
|
|
};
|
|
|
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
|
|
|
var sortstring = DatasetSort.Getsortstring(sort);
|
|
|
if (!string.IsNullOrEmpty(sortstring))
|
|
|
{
|
|
|
strSql.Append(" order by " + sortstring);
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
|
|
|
strSql.Append(" order by ETD DESC");
|
|
|
|
|
|
}
|
|
|
|
|
|
var dbparams = new List<CustomDbParamter>();
|
|
|
|
|
|
var paramps_sSQL = new CustomDbParamter();
|
|
|
paramps_sSQL.ParameterName = "@sSQL";
|
|
|
paramps_sSQL.DbType = DbType.String;
|
|
|
paramps_sSQL.Direction = ParameterDirection.Input;
|
|
|
paramps_sSQL.Value = strSql.ToString();
|
|
|
dbparams.Add(paramps_sSQL);
|
|
|
|
|
|
|
|
|
|
|
|
var dbRptResult = PubSysDAL.GetMsSqlPrcDataSet("sMsExesqlQry", dbparams, "Result_Set");
|
|
|
var json = RptHelper.GetRptJsonResult(start, limit, dbRptResult, "Result_Set", true);
|
|
|
return new ContentResult() { Content = json };
|
|
|
}
|
|
|
}
|
|
|
|
|
|
|
|
|
[SqlKeyWordsFilter(Type = "Action")]//sql 防注入过滤器
|
|
|
public ContentResult RptCtnSumLRData(int start, int limit, string condition, string sort, string printstr, string sortstr)
|
|
|
{
|
|
|
var strDa = MsOpTruckBulkDAL.GetRpRangDAStr("index", Convert.ToString(Session["USERID"]), Convert.ToString(Session["SHOWNAME"]), Convert.ToString(Session["COMPANYID"]));
|
|
|
|
|
|
if (!string.IsNullOrEmpty(strDa))
|
|
|
{
|
|
|
if (!string.IsNullOrEmpty(condition))
|
|
|
{
|
|
|
condition = condition + " and " + strDa;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
condition = strDa;
|
|
|
}
|
|
|
}
|
|
|
var strSql = new StringBuilder();
|
|
|
strSql.Append("SELECT sum(FREIGHT) FREIGHT,sum(TTLDR) TTLDR,sum(TTLCR) TTLCR,SUM(PROFIT) PROFIT,SUM(PCFREIGHT) PCFREIGHT,SUM(PCTTLFREIGHT) PCTTLFREIGHT,SUM(TTLOTCR) TTLOTCR,SUM(TTLOTDR) TTLOTDR,SUM(TTKHFDR) TTKHFDR,SUM(TTKHFCR) TTKHFCR,CASE WHEN SUM(TTLDR)=0 THEN 0 ELSE cast(round(sum(PROFIT)/sum(TTLDR)*100,2) AS numeric(20,2)) END PROFITRATE,sum(DELAYDAYS) DELAYDAYS ");
|
|
|
strSql.Append(" FROM (SELECT B.BSNO,B.CUSTOMERNAME,B.ORDERNO,B.ORDERNO1,B.GOODSNAME,B.PORTLOAD,B.PORTDISCHARGE,B.ETD,B.ETA,B.TRANSTYPE,B.PKGS,B.KGS,B.NETWEIGHT,B.CNTRTOTAL ");
|
|
|
strSql.Append(",case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(B.FREIGHT/B.CTNNUM*P.PCCTNNUM,2) as numeric(20,2)) END FREIGHT");
|
|
|
strSql.Append(",case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.CTNNUM*P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM),2) as numeric(20,2)) END TTLDR");
|
|
|
strSql.Append(",case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTLOTDR/B.CTNNUM*P.PCCTNNUM,2) as numeric(20,2)) END TTLOTDR");
|
|
|
strSql.Append(",case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTKHFDR/B.CTNNUM*P.PCCTNNUM,2) as numeric(20,2)) END TTKHFDR");
|
|
|
strSql.Append(",(case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTKHFCR/B.CTNNUM*P.PCCTNNUM,2) as numeric(20,2)) END)+(P.TTKHFCR*P.PCCTNNUM) TTKHFCR");
|
|
|
|
|
|
//strSql.Append(",B.TRADETYPE,B.MBLNO,P.ETD PCETD,P.DELAYDAYS,P.TRUCKER,P.DRIVER,P.TRUCKNO,P.DRIVERTEL,P.BSNO PCBSNO,P.TRANSSTATUS,P.PCKGS,P.PCCTNNUM,B.SALE,P.CUSTSERVICE");
|
|
|
//strSql.Append(",P.FREIGHT*P.PCCTNNUM PCFREIGHT,P.TAXRATE,P.TTLFREIGHT*P.PCCTNNUM PCTTLFREIGHT,P.TTLOTCR*P.PCCTNNUM TTLOTCR,P.TTLCR*P.PCCTNNUM+(case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTKHFCR/B.CTNNUM*P.PCCTNNUM,2) as numeric(20,2)) END) TTLCR");
|
|
|
//strSql.Append(",(case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.CTNNUM*P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM),2) as numeric(20,2)) END)-((P.TTLCR*P.PCCTNNUM)+(case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTKHFCR/B.CTNNUM*P.PCCTNNUM,2) as numeric(20,2)) END)) AS PROFIT");
|
|
|
//// strSql.Append(",cast(round(CASE WHEN (case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.CTNNUM*P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM),2) as numeric(20,2)) END)=0 THEN 0 ELSE(case when ISNULL(B.CTNNUM, 0) = 0 THEN 0 ELSE cast(round(G.TTLDR / B.CTNNUM * P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM), 2) as numeric(20, 2)) END) - (P.TTLCR * P.PCCTNNUM)/(case when ISNULL(B.CTNNUM, 0)= 0 THEN 0 ELSE cast(round(G.TTLDR / B.CTNNUM * P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM), 2) as numeric(20, 2)) END) END,2) AS numeric(20, 2)) PROFITRATE ");
|
|
|
//strSql.Append(",CASE WHEN (case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.CTNNUM*P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM),2) as numeric(20,2)) END)=0 THEN 0 ELSE cast(round((((case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.CTNNUM*P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM),2) as numeric(20,2)) END)");
|
|
|
//strSql.Append("-((P.TTLCR*P.PCCTNNUM)+(case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTKHFCR/B.CTNNUM*P.PCCTNNUM,2) as numeric(20,2)) END)))/(case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.CTNNUM*P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM),2) as numeric(20,2)) END)*100),2) AS numeric(20,2)) END PROFITRATE ");
|
|
|
//strSql.Append(" ,(SELECT DESCRIPTION FROM info_client WHERE SHORTNAME=B.CUSTOMERNAME) AS CUSTOMERNAMEREF");
|
|
|
//strSql.Append(" ,(SELECT DESCRIPTION FROM info_client WHERE SHORTNAME=P.TRUCKER) AS TRUCKERREF");
|
|
|
//strSql.Append(" FROM (select *,(select sum(CTNNUM) from op_truck_bulk_detail where BSNO=op_truck_bulk.BSNO) CTNNUM from op_truck_bulk where transtype='集装箱') B LEFT JOIN v_op_gain_dr_INV_truck G ON (G.BSNO=B.BSNO)");
|
|
|
//strSql.Append(" LEFT JOIN (select P.BSNO,P.TRANSSTATUS,P.ETD,P.CREATETIME,datediff(day,P.ETD,P.CREATETIME) DELAYDAYS,P.TRUCKER,P.DRIVER,P.TRUCKNO,P.DRIVERTEL,CASE WHEN P.CTNNUM=0 THEN 0 ELSE P.FREIGHT/P.CTNNUM END FREIGHT,P.TAXRATE");
|
|
|
//strSql.Append(",CASE WHEN P.CTNNUM=0 THEN 0 ELSE P.TTLFREIGHT/P.CTNNUM END TTLFREIGHT,CASE WHEN P.CTNNUM=0 THEN 0 ELSE G.TTLOTCR/P.CTNNUM END TTLOTCR,CASE WHEN P.CTNNUM=0 THEN 0 ELSE G.TTKHFCR/P.CTNNUM END TTKHFCR");
|
|
|
//strSql.Append(",CASE WHEN P.CTNNUM=0 THEN 0 ELSE G.TTLCR/P.CTNNUM END TTLCR,CASE WHEN P.CTNNUM=0 THEN 0 ELSE G.TTLDR/P.CTNNUM END TTLDR,PD.LINKBSNO,PD.PCKGS,PD.PCCTNNUM");
|
|
|
//strSql.Append(",P.SALE,P.CUSTSERVICE from (select BSNO,LINKBSNO,SUM(KGS)PCKGS,SUM(CTNNUM) PCCTNNUM from op_truck_bulk_pc_detail pd GROUP BY BSNO,LINKBSNO) PD");
|
|
|
//strSql.Append(" LEFT JOIN op_truck_bulk_pc p ON (P.BSNO=pD.BSNO) LEFT JOIN v_op_gain_dr_INV_truck G ON (G.BSNO=p.BSNO)) P ON (P.LINKBSNO=B.BSNO)");
|
|
|
|
|
|
strSql.Append(",B.TRADETYPE,B.MBLNO,P.ETA PCETD,P.CREATETIME,P.DELAYDAYS,P.TRUCKER,P.DRIVER,P.TRUCKNO,P.DRIVERTEL,P.BSNO PCBSNO,P.TRANSSTATUS,P.PCKGS,P.PCCTNNUM,B.SALE,P.CUSTSERVICE,P.REMARK PCREMARK");
|
|
|
strSql.Append(",P.FREIGHT*P.PCCTNNUM PCFREIGHT,P.TAXRATE,P.TTLFREIGHT*P.PCCTNNUM PCTTLFREIGHT,P.TTLOTCR*P.PCCTNNUM TTLOTCR,P.TTLCR*P.PCCTNNUM+(case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTKHFCR/B.CTNNUM*P.PCCTNNUM,2) as numeric(20,2)) END) TTLCR");
|
|
|
strSql.Append(",(case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.CTNNUM*P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM),2) as numeric(20,2)) END)-((P.TTLCR*P.PCCTNNUM)+(case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTKHFCR/B.CTNNUM*P.PCCTNNUM,2) as numeric(20,2)) END)) AS PROFIT");
|
|
|
// strSql.Append(",cast(round(CASE WHEN (case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.CTNNUM*P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM),2) as numeric(20,2)) END)=0 THEN 0 ELSE(case when ISNULL(B.CTNNUM, 0) = 0 THEN 0 ELSE cast(round(G.TTLDR / B.CTNNUM * P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM), 2) as numeric(20, 2)) END) - (P.TTLCR * P.PCCTNNUM)/(case when ISNULL(B.CTNNUM, 0)= 0 THEN 0 ELSE cast(round(G.TTLDR / B.CTNNUM * P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM), 2) as numeric(20, 2)) END) END,2) AS numeric(20, 2)) PROFITRATE ");
|
|
|
strSql.Append(",CASE WHEN (case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.CTNNUM*P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM),2) as numeric(20,2)) END)=0 THEN 0 ELSE cast(round((((case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.CTNNUM*P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM),2) as numeric(20,2)) END)");
|
|
|
strSql.Append("-((P.TTLCR*P.PCCTNNUM)+(case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTKHFCR/B.CTNNUM*P.PCCTNNUM,2) as numeric(20,2)) END)))/(case when ISNULL(B.CTNNUM,0)=0 THEN 0 ELSE cast(round(G.TTLDR/B.CTNNUM*P.PCCTNNUM+(P.TTLDR*P.PCCTNNUM),2) as numeric(20,2)) END)*100),2) AS numeric(20,2)) END PROFITRATE ");
|
|
|
strSql.Append(" ,(SELECT DESCRIPTION FROM info_client WHERE SHORTNAME=B.CUSTOMERNAME) AS CUSTOMERNAMEREF");
|
|
|
strSql.Append(" ,(SELECT DESCRIPTION FROM info_client WHERE SHORTNAME=P.TRUCKER) AS TRUCKERREF");
|
|
|
strSql.Append(" FROM (select *,(select sum(CTNNUM) from op_truck_bulk_detail where BSNO=op_truck_bulk.BSNO) CTNNUM from op_truck_bulk where transtype='集装箱') B LEFT JOIN v_op_gain_dr_INV_truck G ON (G.BSNO=B.BSNO)");
|
|
|
strSql.Append(" LEFT JOIN (select P.BSNO,P.TRANSSTATUS,P.ETD,P.ETA,P.CREATETIME,datediff(day,P.ETA,P.CREATETIME) DELAYDAYS,P.TRUCKER,P.DRIVER,P.TRUCKNO,P.DRIVERTEL,P.REMARK,CASE WHEN P.CTNNUM=0 THEN 0 ELSE P.FREIGHT/P.CTNNUM END FREIGHT,P.TAXRATE");
|
|
|
strSql.Append(",CASE WHEN P.CTNNUM=0 THEN 0 ELSE P.TTLFREIGHT/P.CTNNUM END TTLFREIGHT,CASE WHEN P.CTNNUM=0 THEN 0 ELSE G.TTLOTCR/P.CTNNUM END TTLOTCR,CASE WHEN P.CTNNUM=0 THEN 0 ELSE G.TTKHFCR/P.CTNNUM END TTKHFCR");
|
|
|
strSql.Append(",CASE WHEN P.CTNNUM=0 THEN 0 ELSE G.TTLCR/P.CTNNUM END TTLCR,CASE WHEN P.CTNNUM=0 THEN 0 ELSE G.TTLDR/P.CTNNUM END TTLDR,PD.LINKBSNO,PD.PCKGS,PD.PCCTNNUM");
|
|
|
strSql.Append(",P.SALE,P.CUSTSERVICE from (select BSNO,LINKBSNO,SUM(KGS)PCKGS,SUM(CTNNUM) PCCTNNUM from op_truck_bulk_pc_detail pd GROUP BY BSNO,LINKBSNO) PD");
|
|
|
strSql.Append(" LEFT JOIN op_truck_bulk_pc p ON (P.BSNO=pD.BSNO) LEFT JOIN v_op_gain_dr_INV_truck G ON (G.BSNO=p.BSNO)) P ON (P.LINKBSNO=B.BSNO)");
|
|
|
|
|
|
strSql.Append(" where B.transtype='集装箱' ");
|
|
|
if (!string.IsNullOrEmpty(condition))
|
|
|
{
|
|
|
strSql.Append(" and " + condition);
|
|
|
}
|
|
|
strSql.Append(") B WHERE TRANSSTATUS IN ('审核通过','已到货','已回单','已回单','在途') ");
|
|
|
|
|
|
|
|
|
if ((!string.IsNullOrEmpty(printstr)) && (printstr == "true"))
|
|
|
{
|
|
|
|
|
|
//if (!string.IsNullOrEmpty(sortstr))
|
|
|
//{
|
|
|
// strSql.Append(" order by " + sortstr);
|
|
|
//}
|
|
|
//else
|
|
|
//{
|
|
|
|
|
|
// strSql.Append(" order by ETD DESC");
|
|
|
|
|
|
//}
|
|
|
var jsonRespose = new JsonResponse
|
|
|
{
|
|
|
Success = true,
|
|
|
Message = "完成",
|
|
|
Data = strSql.ToString()
|
|
|
};
|
|
|
|
|
|
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
|
|
|
//var sortstring = DatasetSort.Getsortstring(sort);
|
|
|
//if (!string.IsNullOrEmpty(sortstring))
|
|
|
//{
|
|
|
// strSql.Append(" order by " + sortstring);
|
|
|
//}
|
|
|
//else
|
|
|
//{
|
|
|
|
|
|
// strSql.Append(" order by ETD DESC");
|
|
|
|
|
|
//}
|
|
|
|
|
|
var dbparams = new List<CustomDbParamter>();
|
|
|
|
|
|
var paramps_sSQL = new CustomDbParamter();
|
|
|
paramps_sSQL.ParameterName = "@sSQL";
|
|
|
paramps_sSQL.DbType = DbType.String;
|
|
|
paramps_sSQL.Direction = ParameterDirection.Input;
|
|
|
paramps_sSQL.Value = strSql.ToString();
|
|
|
dbparams.Add(paramps_sSQL);
|
|
|
|
|
|
|
|
|
|
|
|
var dbRptResult = PubSysDAL.GetMsSqlPrcDataSet("sMsExesqlQry", dbparams, "Result_Set");
|
|
|
var json = RptHelper.GetRptJsonResult(start, limit, dbRptResult, "Result_Set", true);
|
|
|
return new ContentResult() { Content = json };
|
|
|
}
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
}
|
|
|
|