RuiTengJb/ICSSoft.AppSln/ICSSoft.Frame.Data.DAL/FormPurchasingStorageDAL.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 System.Data.Sql;
using System.Data.Linq;
using ICSSoft.Base.Config.DBHelper;
using System.Data.SqlClient;

namespace ICSSoft.Frame.Data.DAL
{
    public class FormPurchasingStorageDAL
    {
//        public static void beginSave(ICSPOArrive context)
//        {
//            SqlConnection conn = new SqlConnection(AppConfig.AppConnectString);
//            SqlCommand com = conn.CreateCommand();
//            SqlTransaction tran;
//            conn.Open();
//            tran = conn.BeginTransaction();
//            com.Transaction = tran;
//            try
//            {
//                string sqlStr = @"IF EXISTS(SELECT * FROM dbo.ICSPurchasingStorage WHERE BatchCode='" + context.BatchCode + @"')
//                                RAISERROR('批次已经存在!!',16,0)";
//                com.CommandText = sqlStr;
//                com.ExecuteNonQuery();
//                    string guid = AppConfig.GetGuid();
//                    string sql = @"INSERT INTO [dbo].[ICSPurchasingStorage]
//                       ([Serial]
//                       ,[ItemCode]
//                       ,[ItemName]
//                       ,[ItemStd]
//                       ,[VenCode]
//                       ,[VenName]
//                       ,[ComUnit]
//                       ,[AssComUnit]
//                       ,[dDate]
//                       ,[BatchCode]
//                       ,[Quantity]
//                       ,[OutQuantity]
//                       ,[Mtime]
//                       ,[Muser]
//                       ,[cFree1]
//                       ,[cFree2],PackQuantiy,Rd01AutoID,QCStatus)
//                 VALUES
//                       (@Serial
//                       ,@ItemCode
//                       ,@ItemName
//                       ,@ItemStd
//                       ,@VenCode
//                       ,@VenName
//                       ,@ComUnit
//                       ,@AssComUnit
//                       ,@dDate
//                       ,@BatchCode
//                       ,@Quantity
//                       ,@OutQuantity
//                       ,@Mtime
//                       ,@Muser
//                       ,@cFree1
//                       ,@cFree2,@PackQuantiy,@Rd01AutoID,'合格')";
//                    com.CommandText = sql;
//                    com.Parameters.Clear();
//                    com.Parameters.AddWithValue("@Serial", guid);
//                    com.Parameters.AddWithValue("@ItemCode", context.ItemCode);
//                    com.Parameters.AddWithValue("@ItemName", context.ItemName);
//                    com.Parameters.AddWithValue("@ItemStd", context.ItemStd);
//                    com.Parameters.AddWithValue("@VenCode", DBNull.Value);
//                    com.Parameters.AddWithValue("@VenName", DBNull.Value);
//                    com.Parameters.AddWithValue("@ComUnit", context.ComUnit);
//                    com.Parameters.AddWithValue("@AssComUnit", context.AssComUnit);
//                    com.Parameters.AddWithValue("@dDate",DBNull.Value);
//                    com.Parameters.AddWithValue("@BatchCode", context.BatchCode);
//                    com.Parameters.AddWithValue("@Quantity", context.Quantity);
//                    com.Parameters.AddWithValue("@OutQuantity", "0");
//                    com.Parameters.AddWithValue("@Mtime", DateTime.Now.ToString());
//                    com.Parameters.AddWithValue("@Muser", AppConfig.UserName);
//                    com.Parameters.AddWithValue("@cFree1", context.cFree1);
//                    com.Parameters.AddWithValue("@cFree2", DBNull.Value);
//                    com.Parameters.AddWithValue("@PackQuantiy", "0");
//                    com.Parameters.AddWithValue("@Rd01AutoID", DBNull.Value);

//                    com.ExecuteNonQuery();
//                tran.Commit();

//            }
//            catch (Exception ex)
//            {
//                tran.Rollback();
//                throw ex;
//            }
//        }

        public static DataTable findByBatch(String batch, String Appconstr)
        {
            try
            {
                string sql = @"SELECT Serial,BatchCode,ItemCode,ItemName,ItemStd,LotQty,cComUnitCode
            FROM dbo.ICSItemLot WHERE BatchCode='" + batch + "' and QCResult='待检'";
                return DBHelper.ExecuteDataset(Appconstr, CommandType.Text, sql).Tables[0];

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static void upQc(List<string> batch, List<string> qc)
        {
            SqlConnection conn = new SqlConnection(AppConfig.AppConnectString);
            SqlCommand com = conn.CreateCommand();
            SqlTransaction tran;
            conn.Open();
            tran = conn.BeginTransaction();
            com.Transaction = tran;
            try
            {
                for (int i = 0; i < batch.Count;i++ )
                {
                    string sql = @"UPDATE dbo.ICSItemLot SET QCResult='" + qc[i] + "',QCUser='" + AppConfig.UserName + "',QCTime= GETDATE() WHERE BatchCode='" + batch[i] + "'";
                    com.CommandText = sql;
                    com.ExecuteNonQuery();
                }
                tran.Commit();

            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw new Exception(ex.Message);
            }
        }

        #region delete
        public static void delete(List<String> guidList)
        {
            FramDataContext db = new FramDataContext(AppConfig.AppConnectString);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                //var lines = db.ICSPurchasingStorage.Where(a => guidList.Contains(a.Serial));
                //db.ICSPurchasingStorage.DeleteAllOnSubmit(lines);
                //db.SubmitChanges();
                //db.Transaction.Commit();

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

        public static void save(DataTable dt,string batchCode)
        {
            SqlConnection conn = new SqlConnection(AppConfig.AppConnectString);
            SqlCommand com = conn.CreateCommand();
            SqlTransaction tran;
            conn.Open();
            tran = conn.BeginTransaction();
            com.Transaction = tran;
            try
            {
                string sqlStr = @"IF EXISTS(SELECT * FROM dbo.ICSPurchasingStorage WHERE BatchCode='"+batchCode+@"')
                                RAISERROR('批次已经存在!!',16,0)";
                com.CommandText = sqlStr;
                com.ExecuteNonQuery();

                foreach(DataRow row in dt.Rows)
                {
                    string guid = AppConfig.GetGuid();
                    string sql = @"INSERT INTO [dbo].[ICSPurchasingStorage]
                       ([Serial]
                       ,[ItemCode]
                       ,[ItemName]
                       ,[ItemStd]
                       ,[VenCode]
                       ,[VenName]
                       ,[ComUnit]
                       ,[AssComUnit]
                       ,[dDate]
                       ,[BatchCode]
                       ,[Quantity]
                       ,[OutQuantity]
                       ,[Mtime]
                       ,[Muser]
                       ,[cFree1]
                       ,[cFree2],PackQuantiy,Rd01AutoID,QCStatus)
                 VALUES
                       (@Serial
                       ,@ItemCode
                       ,@ItemName
                       ,@ItemStd
                       ,@VenCode
                       ,@VenName
                       ,@ComUnit
                       ,@AssComUnit
                       ,@dDate
                       ,@BatchCode
                       ,@Quantity
                       ,@OutQuantity
                       ,@Mtime
                       ,@Muser
                       ,@cFree1
                       ,@cFree2,@PackQuantiy,@Rd01AutoID,'待检')";
                    com.CommandText = sql;
                    com.Parameters.Clear();
                    com.Parameters.AddWithValue("@Serial", guid);
                    com.Parameters.AddWithValue("@ItemCode",row["存货编码"].ToString());
                    com.Parameters.AddWithValue("@ItemName",row["存货名称"].ToString());
                    com.Parameters.AddWithValue("@ItemStd",row["规格型号"].ToString());
                    com.Parameters.AddWithValue("@VenCode",row["供应商编码"].ToString());
                    com.Parameters.AddWithValue("@VenName",row["供应商名称"].ToString());
                    com.Parameters.AddWithValue("@ComUnit",row["主计量单位"].ToString());
                    com.Parameters.AddWithValue("@AssComUnit", row["辅计量单位编码"].ToString());
                    com.Parameters.AddWithValue("@dDate", row["到货日期"].ToString());
                    com.Parameters.AddWithValue("@BatchCode", batchCode);
                    com.Parameters.AddWithValue("@Quantity", row["到货数量"].ToString());
                    com.Parameters.AddWithValue("@OutQuantity","0");
                    com.Parameters.AddWithValue("@Mtime", DateTime.Now.ToString());
                    com.Parameters.AddWithValue("@Muser",AppConfig.UserName);
                    com.Parameters.AddWithValue("@cFree1","");
                    com.Parameters.AddWithValue("@cFree2", "");
                    com.Parameters.AddWithValue("@PackQuantiy", "0");
                    com.Parameters.AddWithValue("@Rd01AutoID", row["AutoID"].ToString());
                   
                    com.ExecuteNonQuery();
                }
                tran.Commit();

            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw ex;
            }
        }

    }
}