using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ICS.WCF.Base;
using ICSSoft.Common;
using ICSSoft.Entity;
using Newtonsoft.Json;

namespace ICSSoft.DataProject
{
    public class ICSGetU9WhQty
    {
        private static log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        private static string connString = System.Configuration.ConfigurationManager.AppSettings["ERPConnStr"];
        public string CurrentStock(SearchInfo Info)
        {
            DataSet ds = new DataSet();
            DataTable dt = null;
            string mag = "";
            string sql = "";
            string connS = "";
            VouchKey key = new VouchKey();
            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();

            try
            {
                connS = string.Format(connString, Info.WorkPoint);
                conn = new System.Data.SqlClient.SqlConnection(connS);
                conn.Open();
                SqlTransaction sqlTran = conn.BeginTransaction();
                cmd = new SqlCommand();
                cmd.Transaction = sqlTran;
                cmd.Connection = conn;
                string OrgID = "";
                string ItemID = "";
                string BatchID = "";
                #region 获取组织ID
                string searchsql = @"select ID from Base_Organization
                                     where Code='{0}'";
                searchsql = string.Format(searchsql, Info.WorkPoint);
                DataTable searchdt = DBhlper.Query(searchsql, Appconfig.GetU9ConnStr());
                if (searchdt.Rows.Count != 0)
                {
                    OrgID = searchdt.Rows[0]["ID"].ToString();
                }
                #endregion

                #region 获取料品ID
                searchsql = @"select ID FROM CBO_ItemMaster
                              WHERE Code='{0}' and Org='{1}'";
                searchsql = string.Format(searchsql, Info.InvCode, OrgID);
                searchdt = DBhlper.Query(searchsql, Appconfig.GetU9ConnStr());
                if (searchdt.Rows.Count != 0)
                {
                    ItemID = searchdt.Rows[0]["ID"].ToString();
                }
                #endregion

                if (!string.IsNullOrEmpty(Info.BatchCode))
                {
                    #region 获取批号ID
                    searchsql = @"select ID from Lot_LotMaster
                                  where LotCode='{0}' and Item='{1}'";
                    searchsql = string.Format(searchsql, Info.BatchCode, ItemID);
                    searchdt = DBhlper.Query(searchsql, Appconfig.GetU9ConnStr());
                    if (searchdt.Rows.Count != 0)
                    {
                        BatchID = searchdt.Rows[0]["ID"].ToString();
                    }
                    #endregion
                }
                sql = @"select B.Code AS INVCode,B.Name AS INVName,B.SPECS AS SPECS,A.StoreQty AS Quantity 
                        ,C.Code AS WHCode,D.Name AS WHName,A.LotInfo_LotCode AS BatchCode from InvTrans_WhQoh A
                        LEFT JOIN CBO_ItemMaster B ON B.ID=A.ItemInfo_ItemID
                        LEFT JOIN CBO_Wh C ON C.ID=A.Wh
                        LEFT JOIN CBO_Wh_Trl D ON D.ID=C.ID
                        WHERE 1=1 AND A.ItemInfo_ItemID='" + ItemID + "'";
                if (BatchID != "")
                {
                    sql += " AND A.LotInfo_LotMaster_EntityID='" + BatchID + "' ";
                }
                dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
                ds.Tables.Add(dt);
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                    cmd.Transaction.Rollback();
                log.Error(ex.Message);
                throw new Exception(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
            }

            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Clear();

            mag = JsonConvert.SerializeObject(ds);
            return mag;

        }

        public string GetCurrentStockByNameAndSpec(SearchInfoByNameAndSpec Info)
        {
            DataSet ds = new DataSet();
            DataTable dt = null;
            string mag = "";
            string sql = "";
            string connS = "";
            VouchKey key = new VouchKey();
            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();

            try
            {
                connS = string.Format(connString, Info.WorkPoint);
                conn = new System.Data.SqlClient.SqlConnection(connS);
                conn.Open();
                SqlTransaction sqlTran = conn.BeginTransaction();
                cmd = new SqlCommand();
                cmd.Transaction = sqlTran;
                cmd.Connection = conn;
                string OrgID = "";
                #region 获取组织ID
                string searchsql = @"select ID from Base_Organization
                                     where Code='{0}'";
                searchsql = string.Format(searchsql, Info.WorkPoint);
                DataTable searchdt = DBhlper.Query(searchsql, Appconfig.GetU9ConnStr());
                if (searchdt.Rows.Count != 0)
                {
                    OrgID = searchdt.Rows[0]["ID"].ToString();
                }
                #endregion

                sql = @"select A.ID,C.Code AS cWhcode,B.Code AS cInvCode,B.Name AS cInvName,B.SPECS AS cInvStd,A.StoreQty AS iQuantity 
                        ,0 AS AmountEnable,E.Code AS cInvccode from InvTrans_WhQoh A
                        LEFT JOIN CBO_ItemMaster B ON B.ID=A.ItemInfo_ItemID
                        LEFT JOIN CBO_Wh C ON C.ID=A.Wh
                        LEFT JOIN CBO_Wh_Trl D ON D.ID=C.ID
						LEFT JOIN CBO_Category E ON E.ID=B.MainItemCategory
                        WHERE 1=1 ";
                if (Info.INVNAME != "")
                {
                    sql += " AND B.Name like '%"+ Info.INVNAME + "%' ";
                }
                if (Info.INVSTD != "")
                {
                    sql += " AND B.SPECS like '%" + Info.INVSTD + "%' ";
                }
                dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
                ds.Tables.Add(dt);
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                    cmd.Transaction.Rollback();
                log.Error(ex.Message);
                throw new Exception(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
            }

            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Clear();

            mag = JsonConvert.SerializeObject(ds);
            return mag;

        }

        public class SearchInfo
        {
            public string InvCode;
            public string BatchCode;
            public string WorkPoint;
        }

        public class SearchInfoByNameAndSpec
        {
            public string INVNAME;
            public string INVSTD;
            public string WorkPoint;
        }
    }
}
