ERP/TradeData/JC_BaseCodeService.cs

using System;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;
using NetLibrary;
using NetLibrary.Data;
using NetLibrary.ReportPrint;
using TradeModel;

namespace TradeData
{
    public class JC_BaseCodeService 
    {
        #region 读取模块所属设置列表
        public static List<JC_BaseCode> GetJC_BaseCode(int CompanyID, string ModuleName)
        {
            string tsql = @"
select KeyName,BaseCodeName,IsSys from JC_BaseCode where (CompanyID=0 or CompanyID=@CompanyID) and IsUse=1 and ModuleName=@ModuleName order by Sort
";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
            db.AddInParameter(cmd, "@ModuleName", DbType.String, ModuleName);
            DataTable tb=db.ExecuteDataTable(cmd);
            return tb.ToList<JC_BaseCode>();
        }
        #endregion
        #region 读取单项设置
        public static List<JC_BaseCodeDetail> GetJC_BaseCodeDetail(int CompanyID, string KeyName)
        {
            string tsql = @"
select * from JC_BaseCodeDetail 
where CompanyID=@CompanyID and KeyName=@KeyName and IsUse=1
order by SortNo
";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
            db.AddInParameter(cmd, "@KeyName", DbType.String, KeyName);
            DataTable tb = db.ExecuteDataTable(cmd);
            //if (tb.Rows.Count == 0)
            //{
            //    tsql = "select * from JC_BaseCodeDetail where KeyName=@KeyName and IsUse=1 order by SortNo";
            //    cmd = db.GetSqlStringCommand(tsql);
            //    db.AddInParameter(cmd, "@KeyName", DbType.String, KeyName);
            //    tb = db.ExecuteDataTable(cmd);
            //}
            return tb.ToList<JC_BaseCodeDetail>();
        }
        #endregion

        #region 读取平台类型
        public static List<JC_BaseCodeDetail> GetJC_BaseCodePTLX(int CompanyID, string KeyName)
        {
            string tsql = @"
select a.*,UseState=case when b.Code is null then 'false' else 'true' end from JC_BaseCodeDetail a
left join JC_BaseCodeDetail b on a.sortno=b.SortNo and b.KeyName=@KeyName and b.CompanyId=@CompanyID
where a.KeyName=@KeyName and  a.CompanyId=0
order by a.SortNo
";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
            db.AddInParameter(cmd, "@KeyName", DbType.String, KeyName);
            DataTable tb = db.ExecuteDataTable(cmd);
           
            return tb.ToList<JC_BaseCodeDetail>();
        }
        #endregion
        #region 删除
        public static void DeleteCodeDetail(int Code)
        {
            string tsql = @"
delete from JC_BaseCodeDetail where Code=@Code
";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@Code", DbType.Int32, Code);
            db.ExecuteNonQuery(cmd);
        }
        #endregion
        #region 删除
        public void DeleteKeyName(int CompanyId,string  KeyName)
        {
            string tsql = @"
delete from JC_BaseCodeDetail where CompanyId=@CompanyId and KeyName=@KeyName
";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
            db.AddInParameter(cmd, "@KeyName", DbType.String, KeyName);
            db.ExecuteNonQuery(cmd);
        }
        #endregion
        #region 保存
        public static void Save(int CompanyID, string KeyName, List<JC_BaseCodeDetail> ListModel)
        {
            string tsql= @"
if @Code>0
begin
Update [JC_BaseCodeDetail] set [Name]=@Name,[SortNo]=@SortNo where Code=@Code
end
else
begin
INSERT INTO [JC_BaseCodeDetail](CompanyID,[KeyName],[Name],[IsUse],[SortNo])values(@CompanyID,@KeyName,@Name,1,@SortNo)
set @Code=SCOPE_IDENTITY()
end
select @Code
";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            string ids = "";
            foreach (var item in ListModel)
            {
                if (item.CompanyId == 0) continue;
              
                cmd.Parameters.Clear();
                db.AddInParameter(cmd, "@Code", DbType.Int32, item.Code);
                db.AddInParameter(cmd,"@KeyName", DbType.String,KeyName);
                db.AddInParameter(cmd,"@Name", DbType.String,item.Name);
                db.AddInParameter(cmd,"@IsUse", DbType.Int32,item.IsUse);
                db.AddInParameter(cmd,"@SortNo", DbType.Int32,item.SortNo);
                db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
                item.Code=Convert.ToInt32(db.ExecuteScalar(cmd));
                ids += "," + item.Code;
            }
            if (string.IsNullOrEmpty(ids)==false)
            {
                ids = ids.Substring(1);
                tsql = "update JC_BaseCodeDetail set IsUse=0 where CompanyID=@CompanyID and KeyName=@KeyName and Code not in (" + ids + ")";
                cmd = db.GetSqlStringCommand(tsql);
                db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
                db.AddInParameter(cmd, "@KeyName", DbType.String, KeyName);
                db.ExecuteNonQuery(cmd);
            }
        }
        #endregion

        #region 保存
        public  int SaveBaseCode(JC_BaseCodeDetail Model)
        {
            string tsql = @"
if @Code>0
begin
Update [JC_BaseCodeDetail] set [KeyName]=@KeyName,[Name]=@Name,[IsUse]=@IsUse,[SortNo]=@SortNo,[CompanyId]=@CompanyId where Code=@Code
end
else
begin
INSERT INTO [JC_BaseCodeDetail]([KeyName],[Name],[IsUse],[SortNo],[CompanyId])values(@KeyName,@Name,@IsUse,@SortNo,@CompanyId)
set @Code=SCOPE_IDENTITY()
end
select @Code";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@Code", DbType.Int32, Model.Code);
            db.AddInParameter(cmd, "@KeyName", DbType.String, Model.KeyName);
            db.AddInParameter(cmd, "@Name", DbType.String, Model.Name);
            db.AddInParameter(cmd, "@IsUse", DbType.Int32, Model.IsUse);
            db.AddInParameter(cmd, "@SortNo", DbType.Int32, Model.SortNo);
            db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
            int a = Convert.ToInt32(db.ExecuteScalar(cmd));
            return a;
        }
        #endregion
    }
}