RuiTengJb/ICSSoft.AppSln/ICSSoft.Frame.Data.DAL/FormPhysicalInventoryDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ICSSoft.Frame.Data.Entity;
using ICSSoft.Base.Config.AppConfig;
using System.Data;
using ICSSoft.Base.Config.DBHelper;
using System.Data.SqlClient;

namespace ICSSoft.Frame.Data.DAL
{
    public class FormPhysicalInventoryDAL
    {
        #region ERPCheckVouch
        public static void ERPCheckVouch(CheckContext Context, string Appconstr)
        {
            string sql = "";
            SqlConnection conn = new SqlConnection(Appconstr);
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            SqlTransaction trans = conn.BeginTransaction();
            cmd.Transaction = trans;
            cmd.Connection = conn;

            try
            {
                string maxNo = @"select max(ToCheckNO ) as ToCheckNO  from dbo.ICSToCheck";
                DataTable dtNo = DBHelper.ExecuteDataset(Appconstr, CommandType.Text, maxNo).Tables[0];

                string strSql = @"SELECT a.[ItemCode]
                              ,SUM(LotQty) AS  inum
                              ,SUM(LotQty) AS iquantity
                              ,b.WHCode as StorageCode INTO ##sum
                          FROM [dbo].[ICSToChecks] a 
                          LEFT JOIN dbo.ICSWareHouseLotInfo b ON a.BarCode = b.LotNO
                          WHERE a.ToCheckNO = '" + dtNo.Rows[0]["ToCheckNO"].ToString() + "' and a.ItemCode = '" + Context.itemCode + @"'
                         and b.WHCode = '" + Context.whCode + "' group by a.[ItemCode],b.WHCode";

                cmd.CommandText = strSql;
                cmd.ExecuteNonQuery();

                strSql = @"SELECT cWhCode,cInvCode,ISNULL(cFree1,'') AS cFree1,ISNULL(cFree2,'') AS cFree2
            ,ISNULL(cFree3,'') AS cFree3,ISNULL(cFree4,'') AS cFree4,SUM(iQuantity) AS qty INTO ##stock
            FROM {0}.dbo.CurrentStock 
            GROUP BY cWhCode,cInvCode,cFree1,cFree2,cFree3,cFree4";
                strSql = string.Format(strSql, Context.ERPDataName);
                cmd.CommandText = strSql;
                cmd.ExecuteNonQuery();

                #region 更新表体
                sql = @"update a set a.icvquantity =isnull(d.qty,0),
                        icvcnum=isnull(c.inum,0),
                        icvcquantity=isnull(c.iquantity,0),
                      iactualwaste=select( CASE WHEN c.iquantity-d.qty>0 AND d.qty <> 0 THEN ((c.iquantity-d.qty)/d.qty)*100
                   WHEN c.iquantity-d.qty < 0 AND d.qty <> 0 THEN ((d.qty-c.iquantity)/d.qty)*100*-1 ELSE 0 END )
        from {0}.dbo.checkvouchs a
        left join CheckVouch b on a.ID=b.ID
        inner join  ##sum c ON b.cWhCode = c.StorageCode AND a.cItemCode = c.ItemCode
        left join ##stock d c.b.StorageCode = d.cWhCode AND c.ItemCode=d.cInvCode

inner join ICSToCheck b on a.INVCode = b.ItemCode
        where b.ToCheckNO ='" + dtNo.Rows[0]["ToCheckNO"] + "' ";

                sql = @"update e
set icvquantity =isnull(a.qty,0),
icvcnum=isnull(b.inum,0),
icvcquantity=isnull(b.iquantity,0),
iactualwaste=(select CASE WHEN b.iquantity-a.qty>0 AND a.qty <> 0 THEN ((b.iquantity-a.qty)/a.qty)*100
 WHEN b.iquantity-a.qty < 0 AND a.qty <> 0 THEN ((a.qty-b.iquantity)/a.qty)*100*-1 ELSE 0 END)

    FROM ##stock a 
    INNER JOIN ##sum b ON a.cWhCode = b.StorageCode AND a.cInvCode = b.ItemCode
    LEFT JOIN {0}.dbo.Inventory c ON a.cInvCode = c.cInvCode 
    LEFT JOIN {0}.dbo.CheckVouch d on b. StorageCode=d.cWhCode
    LEFT JOIN {0}.dbo.CheckVouchs e ON b.ItemCode = e.cInvCode and d.ID=e.ID 
     WHERE  a.cWhCode = '{1}' and b.ItemCode='{2}' and e.checkcode='{3}'";
                sql = string.Format(sql, Context.ERPDataName, Context.whCode, Context.itemCode, Context.ckNO);

                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                #endregion
                #endregion


                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
        }

        #region AddandEdit
        public static void AddandEdit(ICSToCheck ItemLot, string Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                string sql = @"SELECT Serial FROM dbo.ICSToCheck WHERE ToCheckNO='" + ItemLot.ToCheckNO + "'";
                DataTable dt = DBHelper.ExecuteDataset(Appconstr, CommandType.Text, sql).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    throw new Exception("盘点单号已经存在!!");
                }
                sql = @"select * from ICSWareHouseLotInfo where WHCode='" + ItemLot.StorageCode + "'";
                dt = DBHelper.ExecuteDataset(Appconstr, CommandType.Text, sql).Tables[0];
                if (dt.Rows.Count == 0)
                {
                    throw new Exception("所选仓库没有库存!!");
                }
//                insert into icstocheck
//values
//('newid', 'Tocheckno', 'tocheckdate', 'itemcode', 'itemname'
//, 'CurrentQty', 'actualqty', 'muser', 'musername', 'mtime', 'workpoint', 'cfree1', 'itemtype', 'checksource', 'storagecode')
                string insertsql = @"  insert into dbo.ICSToCheck
  SELECT distinct '{0}','{1}','{2}',
  a.INVCode AS ItemCode,
  c.INVNAME AS ItemName,
  SUM(a.LotQty) AS CurrentQty,
  '0','{3}','{4}','{5}',
  '{6}','',
  '' as ItemType,
  'WMS' as CheckSource,
  '{7}' as StorageCode
    FROM dbo.ICSWareHouseLotInfo a 
        LEFT JOIN dbo.ICSITEMLot b ON a.LotNO=b.LotNO
        LEFT JOIN dbo.ICSINVENTORY c on a.INVCode=c.INVCODE
        where a.WHCode = '{7}'
        GROUP BY 
  a.INVCode,
  c.INVNAME,
  a.WHCode";
                insertsql = string.Format(insertsql, AppConfig.GetGuid(), ItemLot.ToCheckNO,
                    ItemLot.ToCheckDate, AppConfig.UserCode, AppConfig.UserName, AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss")
                    , AppConfig.WorkPointCode, ItemLot.StorageCode);
                DBHelper.ExecuteNonQuery(Appconstr, CommandType.Text, insertsql);

                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region AddToCheck
        public static void AddToCheck(ICSToCheck ToCheck, string Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                string sql = @"SELECT * FROM ICSToCheck WHERE ToCheckNO = '" + ToCheck.ToCheckNO + "' AND StorageCode = '" + ToCheck.StorageCode + "'";
                DataTable dt = DBHelper.ExecuteDataset(Appconstr, CommandType.Text, sql).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    throw new Exception("盘点单号已经存在!!");
                }
                string insertsql = @"INSERT INTO ICSToCheck
                                                   SELECT DISTINCT '{0}', '{1}', '{2}', A.INVCode AS ItemCode, C.INVNAME AS ItemName, SUM(a.LotQty) AS CurrentQty,
                                                   '0', '{3}', '{4}', '{5}', '{6}', '', '' AS ItemType, 'WMS' AS CheckSource, '{7}'
                                                   FROM ICSWareHouseLotInfo A 
                                                   LEFT JOIN dbo.ICSITEMLot B ON A.LotNO = B.LotNO
                                                   LEFT JOIN dbo.ICSINVENTORY C on A.INVCode = C.INVCODE
                                                   WHERE A.WHCode = '{7}' GROUP BY A.INVCode, C.INVNAME";
                insertsql = string.Format(insertsql, AppConfig.GetGuid(), ToCheck.ToCheckNO, ToCheck.ToCheckDate, AppConfig.UserCode,
                    AppConfig.UserName, AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss"), AppConfig.WorkPointCode, ToCheck.StorageCode);
                DBHelper.ExecuteNonQuery(Appconstr, CommandType.Text, insertsql);
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region select
        public static ICSToCheck select(String guid, String Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line = db.ICSToCheck.SingleOrDefault(a => a.Serial == guid);
                return (ICSToCheck)line;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region FindAll
        public static DataTable FindAll(string Appconstr)
        {
            try
            {
                string sql = @"SELECT distinct a.ItemCode,
                                b.INVNAME as ItemName,
                                b.INVSTD as ItemStd,
                                a.BarCode,
                                a.BarCodeQty,
                                a.BarCodeActualQty,
                                b.INVUOM as ComUnit,
                                c.VenderLotNO as Lot,
                                a.ToCheckNO,
                                d.WHCode as StorageCode,
                                d.BinCode as StackCode,
								CAST((c.EATTRIBUTE3*c.LOTQTY) as NUMERIC(10,2)) as MeasureQtys,
								b.INVEXPORTIMPORT as AssComUnit
                                FROM ICSToChecks a 
                                left join ICSINVENTORY b on a.ItemCode=b.INVCODE
                                left join ICSITEMLot c on a.BarCode=c.LotNO 
                                left join ICSWareHouseLotInfo d on c.LotNO=d.LotNO
                        ";
                return DBHelper.ExecuteDataset(Appconstr, CommandType.Text, sql).Tables[0];

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region delete
        public static void delete(string ToCheckNO, string Appconstr)
        {
            SqlConnection conn = new SqlConnection(Appconstr);
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            SqlTransaction trans = conn.BeginTransaction();
            cmd.Transaction = trans;
            cmd.Connection = conn;
            try
            {
                string sqls = @"DELETE dbo.ICSToChecks WHERE ToCheckNO='" + ToCheckNO + "'";
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sqls;
                cmd.ExecuteNonQuery();

                string sql = @"DELETE dbo.ICSToCheck WHERE ToCheckNO='" + ToCheckNO + "'";
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
                trans.Commit();

            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
        }
        #endregion

        #region deletedetail 删除盘点详情
        public static void deletedetail(string LotNo, string ToCheckNO, decimal Actualqty, string itemno, string Appconstr)
        {
            SqlConnection conn = new SqlConnection(Appconstr);
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            SqlTransaction trans = conn.BeginTransaction();
            cmd.Transaction = trans;
            cmd.Connection = conn;
            try
            {
                string sqls = @"DELETE dbo.ICSToChecks WHERE ToCheckNO='" + ToCheckNO + "' AND BarCode='" + LotNo + "'";
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sqls;
                cmd.ExecuteNonQuery();

                string sql = "";
                sql = @"Update ICSToCheck set ActualQty=ActualQty-" + Actualqty + " where ToCheckNO='" + ToCheckNO + "' AND ItemCode='" + itemno + "'";
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
                trans.Commit();

            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
        }
        #endregion

        #region Update
        public static void CheckIn(string CheckNo, string BarCode, string LotQty, string Appconstr)
        {
            SqlConnection conn = new SqlConnection(Appconstr);
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            SqlTransaction trans = conn.BeginTransaction();
            cmd.Transaction = trans;
            cmd.Connection = conn;

            try
            {
                //string maxNo = @"select max(ToCheckNO ) as ToCheckNO  from dbo.ICSToCheck";
                //DataTable dtNo = DBHelper.ExecuteDataset(Appconstr, CommandType.Text, maxNo).Tables[0];

                string Sql = @"SELECT Serial,BarCodeActualQty FROM dbo.ICSToChecks  WHERE BarCode ='" + BarCode + "' and ToCheckNO='" + CheckNo + "'";
                DataTable dt = DBHelper.ExecuteDataset(Appconstr, CommandType.Text, Sql).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    string strSql = @"update b set b.ActualQty = b.ActualQty-'" + Convert.ToDecimal(dt.Rows[0]["BarCodeActualQty"].ToString()) + @"'
                                    from (select LotQty,INVCode,WHCode from ICSWareHouseLotInfo where LotNO= '" + BarCode + @"') a
                                    inner join ICSToCheck b on a.INVCode = b.ItemCode AND a.WHCode = b.StorageCode
                                    where b.ToCheckNO ='" + CheckNo + @"' 
                                                update b set b.ActualQty = b.ActualQty+'" + Convert.ToDecimal(LotQty) + @"'
                                    from (select LotQty,INVCode,WHCode from ICSWareHouseLotInfo where LotNO= '" + BarCode + @"') a
                                    inner join ICSToCheck b on a.INVCode = b.ItemCode AND a.WHCode = b.StorageCode
                                    where b.ToCheckNO ='" + CheckNo + @"'";

                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = strSql;
                    cmd.ExecuteNonQuery();

                    string lineSql = @"UPDATE ICSToChecks set BarCodeActualQty={0},Mtime=GETDATE(),Updater='{3}',UpdateTime=GetDate(),Status='Update'
                                       where BarCode='{1}' AND ToCheckNO='{2}'";
                    lineSql = string.Format(lineSql, Convert.ToDecimal(LotQty), BarCode, CheckNo, AppConfig.UserCode);
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = lineSql;
                    cmd.ExecuteNonQuery();
                }
                else
                {
                    string strSql = @"update b set b.ActualQty = b.ActualQty+'" + Convert.ToDecimal(LotQty) + @"'
        from (select LotQty,INVCode,WHCode from ICSWareHouseLotInfo where LotNO= '" + BarCode + @"') a
        inner join ICSToCheck b on a.INVCode = b.ItemCode AND a.WHCode = b.StorageCode
        where b.ToCheckNO ='" + CheckNo + "' ";

                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = strSql;
                    cmd.ExecuteNonQuery();
                    
                    string lineSql = @"insert into dbo.ICSToChecks
            select NEWID(),a.INVCode AS ItemCode,
            a.LotNO AS BarCode,
            a.LotQty AS BarCodeQty,
            '{5}',
            '{1}',GETDATE(),'{3}',
             '{0}',
            b.VenderLotNO as BatchCodes,
            '{4}',
            'Insert',
            '{4}',
            GETDATE()
             from dbo.ICSWareHouseLotInfo a 
            left join dbo.ICSITEMLot b on a.LotNO = b.LotNO 
            where a.LotNO = '{2}'";
                    lineSql = string.Format(lineSql, CheckNo, AppConfig.UserName, BarCode, AppConfig.WorkPointCode, AppConfig.UserCode, LotQty);
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = lineSql;
                    cmd.ExecuteNonQuery();
                }
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
        }
        #endregion

        public static DataTable Check(string BarCode, string ToCheckNo, string Appconstr)
        {

            try
            {

                string lineSql = @"SELECT a.ItemCode 
				FROM ICSToCheck a        
                left join ICSWareHouseLotInfo b on a.ItemCode=b.INVCode
                 WHERE b.LotNO = '" + BarCode + "'and a.ToCheckNO='" + ToCheckNo + "'";
                return DBHelper.ExecuteDataset(Appconstr, CommandType.Text, lineSql).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static DataTable CheckNo(string BarCode, string Appconstr)
        {

            try
            {

                string lineSql = @"SELECT LotNO,WHCode FROM dbo.ICSWareHouseLotInfo WHERE LotNO = '" + BarCode + "'";
                return DBHelper.ExecuteDataset(Appconstr, CommandType.Text, lineSql).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        private static DataTable SQlReturnData(SqlCommand cmd)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter dr = new System.Data.SqlClient.SqlDataAdapter();
            dr.SelectCommand = cmd;
            dr.Fill(dt);
            return dt;
        }

        private static void SaveGetrdIDandAutoID(string connectstring, string returnTxt, string IDtype, string cAcc_id, int rowCount, out int id, out int autoID)
        {
            try
            {
                string str = @"DECLARE @ID int
								DECLARE @DID int
								SET @ID = 0
								SET @DID = 0
								IF NOT EXISTS (SELECT * FROM ufsystem..ua_identity   WHERE cacc_id = '{0}'  AND cVouchType = '{1}')
								INSERT INTO ufsystem..ua_identity(cAcc_Id,cVouchType,iFatherId,iChildId) VALUES('{0}','{1}',1,1)
								SELECT @ID = ifatherID + 1 ,@DID = ichildID + {2}
								 FROM  ufsystem..ua_identity  
								 WHERE cVouchType = '{1}'  
								 AND cAcc_id = '{0}'
 
								 UPDATE ufsystem..ua_identity  
								  SET ifatherID = ifatherID + 1,ichildID = ichildID + {2}
								 WHERE cVouchType = '{1}'  AND cAcc_id = '{0}' 
								select @ID as ID,@DID as DID";
                str = string.Format(str, cAcc_id, IDtype, rowCount.ToString());
                DataTable dt = DBHelper.ExecuteDataset(connectstring, CommandType.Text, str).Tables[0];
                if (dt.Rows.Count == 0)
                {
                    throw new Exception("ID取得失败");
                }
                id = Convert.ToInt32(dt.Rows[0]["ID"]);
                autoID = Convert.ToInt32(dt.Rows[0]["DID"]);
            }
            catch (Exception ex)
            {
                throw new Exception(returnTxt + ex.Message);
            }
        }


    }

    public class CheckContext
    {
        public string ERPDataName { get; set; }
        public string WorkPointCode { get; set; }
        public string UserName { get; set; }
        public DateTime UserTime { get; set; }
        public string Storage_Serial { get; set; }
        public string Stack_Serial { get; set; }
        public string ERPDeptCode { get; set; }
        public string ERPrdCode { get; set; }
        public string ERPrdcRdCode { get; set; }
        public int ERPrdID { get; set; }
        public int ERPrdDid { get; set; }
        public int ERPrdRowCount { get; set; }
        public string ERPcWhCode { get; set; }
        public string ERPrdPerson { get; set; }
        public string ERPcMaker { get; set; }
        public string ERPrdcDate { get; set; }
        public string ERPDEF_ID { get; set; }
        public string ERPCardNumber { get; set; }
        public string ERPcPersonCode { get; set; }
        public string MoDid { get; set; }
        public string MoId { get; set; }

        public string ckNO { get; set; }
        public string whCode { get; set; }
        public string itemCode { get; set; }
        public Dictionary<string, string> Bar_Qty { get; set; }


    }
}