using NFine.Code;
using NFine.Data.Extensions;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NFine.Domain._03_Entity.SRM;
using NFine.Repository;
using System.Security.Cryptography;

namespace NFine.Application.SRM
{
    public class RCVListApp : RepositoryFactory<ICSVendor>
    {

        public DataTable GetRcvLineSubGridJson(string queryJson, ref Pagination jqgridparam)
        {
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sqlU9 = "SELECT DBIpAddress,DBName FROM dbo.Sys_DataBase WHERE DBSourceName='ERP'";
            DataTable dtU9 = SqlHelper.GetDataTableBySql(sqlU9);
            string U9IP = dtU9.Rows[0]["DBIpAddress"].ToString();
            string DBName = dtU9.Rows[0]["DBName"].ToString();
            string sql = @"SELECT RCV.DOCNO cCode ,--到货单号
A41.name cVenName,--供应商编码
po.DocNO cPOID ,--采购单ID
POL.DOCLINENO  ivouchrowno,--采购行号
A7.Name PurOper, --业务员
rcvl.ArrivedTime,--入库时间
rcvl.RcvQtyTU, --入库数量
Item.Code,
Item.Name,
D.Name AS InvUnit,
rcvl.ConfirmedQtyTU,
rcvl.FinallyPriceTC,  --最终价
rcvl.ArriveQtyPU,  --实到数量
rcvl.DescFlexSegments_PubDescSeg14,--重量单位
rcvl.DescFlexSegments_PubDescSeg13,--实称总重
rcvl.DescFlexSegments_PubDescSeg12,--实称单重 
rcvl.TotalNetMnyTC iOriMoney,--立账未税额
rcvl.TotalTaxTC iOriTaxPrice,--立账税额
rcvl.TotalMnyTC ioriSum, --立账价税合计
pol.TotalRtnDeductQtyTU,
rcvl.ArriveQtyPU-pol.TotalRtnDeductQtyTU as JSQty,
CASE WHEN rcvl.ConfirmedQtyTU='0' AND ISNULL(rcsa.DocNO,'')='' THEN '未对账' WHEN rcvl.ConfirmedQtyTU='0' AND ISNULL(rcsa.DocNO,'')<>'' THEN '对账中'WHEN rcvl.RcvQtyTU=rcvl.ConfirmedQtyTU AND rcvl.ConfirmedQtyTU<>'0'  THEN '对账完成'END ststuas
  ,case  when srmpo.signBackstate=1 or ISNULL(srmpo.signBackstate,'')='' then '未回签' WHEN srmpo.signBackstate=2 then '待确认'WHEN srmpo.signBackstate=3 then '已确认'WHEN srmpo.signBackstate=4 then '退回' END as  signBackstate
 FROM [{0}].{1}.dbo.PM_Receivement rcv 
LEFT JOIN [{0}].{1}.dbo.PM_RcvLine rcvl  WITH(NOLOCK) ON rcv.id=rcvl.Receivement AND rcvl.SplitFlag IN (0,1)
LEFT JOIN  [{0}].{1}.dbo.PM_POLine pol WITH(NOLOCK)  ON rcvl.SrcDoc_SrcDocLine_EntityID = pol.id
LEFT JOIN [{0}].{1}.dbo.PM_PurchaseOrder po  WITH(NOLOCK) ON po.ID=pol.PurchaseOrder 
LEFT JOIN [{0}].{1}.dbo.CBO_ItemMaster ii  WITH(NOLOCK) ON ii.id=pol.ItemInfo_ItemID  
LEFT JOIN [{0}].{1}.dbo.CBO_Supplier  sup   WITH(NOLOCK) ON sup .ID=po.SUPPLIER_SUPPLIER  
LEFT JOIN [{0}].{1}.dbo.CBO_Supplier_Trl A41 ON ( sup.ID = A41.ID AND A41.SysMLFlag = 'zh-CN')
left join [{0}].{1}.dbo.[CBO_ItemMaster] as Item on pol.[ItemInfo_ItemID] = Item.[ID]
LEFT JOIN [{0}].{1}.dbo.[Base_UOM] AS C ON ( Item.[CostUOM] = C.[ID] )
LEFT JOIN [{0}].{1}.dbo.[Base_UOM_Trl] AS D ON ( C.[ID] = D.[ID]   AND D.SysMLFlag='zh-CN')
left join [{0}].{1}.dbo.[CBO_Operators] as A6 on (po.PurOper = A6.[ID]) 
left join [{0}].{1}.dbo.[CBO_Operators_Trl] as A7 on (A7.SysMlFlag = 'zh-CN') and (A6.[ID] = A7.[ID])
LEFT JOIN [dbo].[ICSCANewDetail] rcsa ON rcsa.EATTRIBUTE1=rcvl.id  AND rcsa.EATTRIBUTE2=RCV.DOCNO
LEFT JOIN ICSPO_POmain srmpo ON srmpo.pocode=po.DocNO AND srmpo.porow=pol.DOCLINENO
WHERE 1=1

";
            sql = string.Format(sql, U9IP, DBName);
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["cPOID"].ToString()))
                {
                    sql += " and po.DocNO like '%" + queryParam["cPOID"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["cCode"].ToString()))
                {
                    sql += " and RCV.DOCNO like '%" + queryParam["cCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["PurOper"].ToString()))
                {
                    sql += " and po.PurOper like '%" + queryParam["PurOper"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["Code"].ToString()))
                {
                    sql += " and Item.Code like '%" + queryParam["Code"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["BeginDate"].ToString()))
                {
                    sql += " and rcvl.ArrivedTime >= '" + queryParam["BeginDate"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
                {
                    sql += " and rcvl.ArrivedTime <= '" + queryParam["EndDate"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["IsReceive"].ToString()))
                {
                    string IsReceive = queryParam["IsReceive"].ToString();
                    if(IsReceive == "0")
                    {

                    }
                    else if (IsReceive == "1")
                    {
                        sql += " and CASE WHEN rcvl.ConfirmedQtyTU='0' AND ISNULL(rcsa.DocNO,'')='' THEN '未对账' WHEN rcvl.ConfirmedQtyTU='0' AND ISNULL(rcsa.DocNO,'')<>'' THEN '对账中'WHEN rcvl.RcvQtyTU=rcvl.ConfirmedQtyTU AND rcvl.ConfirmedQtyTU<>'0'  THEN '对账完成'END ='未对账' ";
                    }
                    else if (IsReceive == "2")
                    {
                        sql += " and CASE WHEN rcvl.ConfirmedQtyTU='0' AND ISNULL(rcsa.DocNO,'')='' THEN '未对账' WHEN rcvl.ConfirmedQtyTU='0' AND ISNULL(rcsa.DocNO,'')<>'' THEN '对账中'WHEN rcvl.RcvQtyTU=rcvl.ConfirmedQtyTU AND rcvl.ConfirmedQtyTU<>'0'  THEN '对账完成'END ='对账中' ";
                    }
                    else if (IsReceive == "3")
                    {
                        sql += " and CASE WHEN rcvl.ConfirmedQtyTU='0' AND ISNULL(rcsa.DocNO,'')='' THEN '未对账' WHEN rcvl.ConfirmedQtyTU='0' AND ISNULL(rcsa.DocNO,'')<>'' THEN '对账中'WHEN rcvl.RcvQtyTU=rcvl.ConfirmedQtyTU AND rcvl.ConfirmedQtyTU<>'0'  THEN '对账完成'END ='对账完成' ";
                    }
                    else
                    {
                        sql += " and CASE WHEN rcvl.ConfirmedQtyTU='0' AND ISNULL(rcsa.DocNO,'')='' THEN '未对账' WHEN rcvl.ConfirmedQtyTU='0' AND ISNULL(rcsa.DocNO,'')<>'' THEN '对账中'WHEN rcvl.RcvQtyTU=rcvl.ConfirmedQtyTU AND rcvl.ConfirmedQtyTU<>'0'  THEN '对账完成'END in('未对账','对账中') ";
                    }
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VenName"].ToString()))
                {
                    sql += " and A41.name like '%" + queryParam["VenName"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["ReleaseState"].ToString()))
                {
                    string ReleaseState = queryParam["ReleaseState"].ToString();
                    if (ReleaseState == "1")
                        sql += " and srmpo.SignBackState = '1'";
                    else if (ReleaseState == "2")
                        sql += " and srmpo.SignBackState = '2'";
                    else if (ReleaseState == "3")
                        sql += " and srmpo.SignBackState = '3'";
                    else if (ReleaseState == "0")
                        sql += " and ISNULL(srmpo.signBackstate,'0') = '0'";
                    else if (ReleaseState == "4")
                    {
                        sql += " and ISNULL(srmpo.signBackstate,'') = '4'";
                    }
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
            {
                sql += " and sup.code='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);


        }

        public DataTable ExportRcvLineAll(string cPOID, string cCode, string PurOper, string Code, string BeginDate, string EndDate, string IsReceive, string VenName)
        {
            string ParentId = SqlHelper.Organize_F_ParentId(NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode);
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();

            string sqlU9 = "SELECT DBIpAddress,DBName FROM dbo.Sys_DataBase WHERE DBSourceName='ERP'";
            DataTable dtU9 = SqlHelper.GetDataTableBySql(sqlU9);
            string U9IP = dtU9.Rows[0]["DBIpAddress"].ToString();
            string DBName = dtU9.Rows[0]["DBName"].ToString();

            string sql = @"SELECT RCV.DOCNO 入库单号 ,--到货单号
  case  when srmpo.signBackstate=1 or ISNULL(srmpo.signBackstate,'')='' then '未回签' WHEN srmpo.signBackstate=2 then '待确认'WHEN srmpo.signBackstate=3 then '已确认'WHEN srmpo.signBackstate=4 then '退回' END as  回签状态,
A41.name 供应商名称,--供应商编码
po.DocNO 采购订单号 ,--采购单ID
POL.DOCLINENO  采购订单行号,--采购行号
A7.Name 业务员, --业务员
rcvl.ArrivedTime 入库时间,--入库时间
rcvl.RcvQtyTU 入库数量, --入库数量
Item.Code 物料代码 ,
Item.Name 物料名称,
D.Name AS 规格型号,
rcvl.ConfirmedQtyTU 已对账数量,
rcvl.FinallyPriceTC 最终价,  --最终价
rcvl.ArriveQtyPU 实到数量,  --实到数量
rcvl.DescFlexSegments_PubDescSeg14 重量单位,--重量单位
rcvl.DescFlexSegments_PubDescSeg13 实称总重,--实称总重
rcvl.DescFlexSegments_PubDescSeg12 实称单重,--实称单重 
rcvl.TotalMnyTC 实到价税合计, --立账价税合计 
rcvl.TotalNetMnyTC 实到未税额,--立账未税额
rcvl.TotalTaxTC 实到税额,--立账税额
 
CASE WHEN rcvl.ConfirmedQtyTU='0' AND ISNULL(rcsa.DocNO,'')='' THEN '未对账' WHEN rcvl.ConfirmedQtyTU='0' AND ISNULL(rcsa.DocNO,'')<>'' THEN '对账中'WHEN rcvl.RcvQtyTU=rcvl.ConfirmedQtyTU AND rcvl.ConfirmedQtyTU<>'0'  THEN '对账完成'END 对账状态
 FROM [{0}].{1}.dbo.PM_Receivement rcv 
LEFT JOIN [{0}].{1}.dbo.PM_RcvLine rcvl  WITH(NOLOCK) ON rcv.id=rcvl.Receivement AND rcvl.SplitFlag IN (0,1)
LEFT JOIN  [{0}].{1}.dbo.PM_POLine pol WITH(NOLOCK)  ON rcvl.SrcDoc_SrcDocLine_EntityID = pol.id
LEFT JOIN [{0}].{1}.dbo.PM_PurchaseOrder po  WITH(NOLOCK) ON po.ID=pol.PurchaseOrder 
LEFT JOIN [{0}].{1}.dbo.CBO_ItemMaster ii  WITH(NOLOCK) ON ii.id=pol.ItemInfo_ItemID  
LEFT JOIN [{0}].{1}.dbo.CBO_Supplier  sup   WITH(NOLOCK) ON sup .ID=po.SUPPLIER_SUPPLIER  
LEFT JOIN [{0}].{1}.dbo.CBO_Supplier_Trl A41 ON ( sup.ID = A41.ID AND A41.SysMLFlag = 'zh-CN')
left join [{0}].{1}.dbo.[CBO_ItemMaster] as Item on pol.[ItemInfo_ItemID] = Item.[ID]
LEFT JOIN [{0}].{1}.dbo.[Base_UOM] AS C ON ( Item.[CostUOM] = C.[ID] )
LEFT JOIN [{0}].{1}.dbo.[Base_UOM_Trl] AS D ON ( C.[ID] = D.[ID]   AND D.SysMLFlag='zh-CN')
left join [{0}].{1}.dbo.[CBO_Operators] as A6 on (po.PurOper = A6.[ID]) 
left join [{0}].{1}.dbo.[CBO_Operators_Trl] as A7 on (A7.SysMlFlag = 'zh-CN') and (A6.[ID] = A7.[ID])
LEFT JOIN [dbo].[ICSCANewDetail] rcsa ON rcsa.EATTRIBUTE1=rcvl.id  AND rcsa.EATTRIBUTE2=RCV.DOCNO
LEFT JOIN ICSPO_POmain srmpo ON srmpo.pocode=po.DocNO AND srmpo.porow=pol.DOCLINENO
WHERE 1=1";
            sql = string.Format(sql, U9IP, DBName);
            if (!string.IsNullOrWhiteSpace(cPOID))
                sql += " and po.DocNO like '%" + cPOID + "%'";
            if (!string.IsNullOrWhiteSpace(cCode))
                sql += " and RCV.DOCNO like '%" + cCode + "%'";
            if (!string.IsNullOrWhiteSpace(PurOper))
                sql += " and po.PurOper like '%" + PurOper + "%'";
            if (!string.IsNullOrWhiteSpace(Code))
                sql += " and Item.Code like '%" + Code + "%'";
            if (!string.IsNullOrWhiteSpace(BeginDate))
                sql += " and rcvl.ArrivedTime >= '" + BeginDate + "'";
            if (!string.IsNullOrWhiteSpace(BeginDate))
                sql += " and rcvl.ArrivedTime <= '" + EndDate + "'";
            if (!string.IsNullOrWhiteSpace(IsReceive))
            {
                if (IsReceive == "0")
                {

                }
                else if (IsReceive == "1")
                {
                    sql += " and CASE WHEN rcvl.ConfirmedQtyTU='0' AND ISNULL(rcsa.DocNO,'')='' THEN '未对账' WHEN rcvl.ConfirmedQtyTU='0' AND ISNULL(rcsa.DocNO,'')<>'' THEN '对账中'WHEN rcvl.RcvQtyTU=rcvl.ConfirmedQtyTU AND rcvl.ConfirmedQtyTU<>'0'  THEN '对账完成'END ='未对账' ";
                }
                else if (IsReceive == "2")
                {
                    sql += " and CASE WHEN rcvl.ConfirmedQtyTU='0' AND ISNULL(rcsa.DocNO,'')='' THEN '未对账' WHEN rcvl.ConfirmedQtyTU='0' AND ISNULL(rcsa.DocNO,'')<>'' THEN '对账中'WHEN rcvl.RcvQtyTU=rcvl.ConfirmedQtyTU AND rcvl.ConfirmedQtyTU<>'0'  THEN '对账完成'END ='对账中' ";
                }
                else if (IsReceive == "3")
                {
                    sql += " and CASE WHEN rcvl.ConfirmedQtyTU='0' AND ISNULL(rcsa.DocNO,'')='' THEN '未对账' WHEN rcvl.ConfirmedQtyTU='0' AND ISNULL(rcsa.DocNO,'')<>'' THEN '对账中'WHEN rcvl.RcvQtyTU=rcvl.ConfirmedQtyTU AND rcvl.ConfirmedQtyTU<>'0'  THEN '对账完成'END ='对账完成' ";
                }
                else
                {
                    sql += " and CASE WHEN rcvl.ConfirmedQtyTU='0' AND ISNULL(rcsa.DocNO,'')='' THEN '未对账' WHEN rcvl.ConfirmedQtyTU='0' AND ISNULL(rcsa.DocNO,'')<>'' THEN '对账中'WHEN rcvl.RcvQtyTU=rcvl.ConfirmedQtyTU AND rcvl.ConfirmedQtyTU<>'0'  THEN '对账完成'END in('未对账','对账中') ";
                }
            }
            if (!string.IsNullOrWhiteSpace(VenName))
                sql += " and A41.name like '%" + VenName + "%'";
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
            {
                sql += " and sup.code='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
            }
            
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }



    }
}