ERP/TradeData/JC_CompanyService.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_CompanyService 
    {
        #region 保存
        public static int Save(JC_Company Model)
        {
            string tsql = @"
if @CompanyId>0
begin
Update [JC_Company] set [CompanyCode]=@CompanyCode,[ParentCompanyId]=@ParentCompanyId,[CompanyName]=@CompanyName,[SimpleName]=@SimpleName,[CompanyType]=@CompanyType,[Address]=@Address,[Phone]=@Phone,[Fax]=@Fax,[Email]=@Email,[HomePage]=@HomePage,[ChargeName]=@ChargeName,[Shen]=@Shen,[Shi]=@Shi,[Qu]=@Qu,[CompanyDesc]=@CompanyDesc,[State]=@State,[PostCode]=@PostCode,[province]=@province,[city]=@city,[county]=@county,[street]=@street,[CompanyEnglishName]=@CompanyEnglishName,[ChargeEnglishName]=@ChargeEnglishName,[EnglishAddress]=@EnglishAddress,[WeightRate]=@WeightRate where CompanyId=@CompanyId
end
else
begin
INSERT INTO [JC_Company]([CompanyCode],[ParentCompanyId],[CompanyName],[SimpleName],[CompanyType],[Address],[Phone],[Fax],[Email],[HomePage],[ChargeName],[Shen],[Shi],[Qu],[CompanyDesc],[State],[PostCode],[province],[city],[county],[street],[CompanyEnglishName],[ChargeEnglishName],[EnglishAddress],[WeightRate])values(@CompanyCode,@ParentCompanyId,@CompanyName,@SimpleName,@CompanyType,@Address,@Phone,@Fax,@Email,@HomePage,@ChargeName,@Shen,@Shi,@Qu,@CompanyDesc,@State,@PostCode,@province,@city,@county,@street,@CompanyEnglishName,@ChargeEnglishName,@EnglishAddress,@WeightRate)
set @CompanyId=SCOPE_IDENTITY()
end
select @CompanyId";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyID);
            db.AddInParameter(cmd, "@CompanyCode", DbType.String, Model.CompanyCode);
            db.AddInParameter(cmd, "@ParentCompanyId", DbType.Int32, Model.ParentCompanyId);
            db.AddInParameter(cmd, "@CompanyName", DbType.String, Model.CompanyName);
            db.AddInParameter(cmd, "@SimpleName", DbType.String, Model.SimpleName);
            db.AddInParameter(cmd, "@CompanyType", DbType.Int32, Model.CompanyType);
            db.AddInParameter(cmd, "@Address", DbType.String, Model.Address);
            db.AddInParameter(cmd, "@Phone", DbType.String, Model.Phone);
            db.AddInParameter(cmd, "@Fax", DbType.String, Model.Fax);
            db.AddInParameter(cmd, "@Email", DbType.String, Model.Email);
            db.AddInParameter(cmd, "@HomePage", DbType.String, Model.HomePage);
            db.AddInParameter(cmd, "@ChargeName", DbType.String, Model.ChargeName);
            db.AddInParameter(cmd, "@Shen", DbType.String, Model.Shen);
            db.AddInParameter(cmd, "@Shi", DbType.String, Model.Shi);
            db.AddInParameter(cmd, "@Qu", DbType.String, Model.Qu);
            db.AddInParameter(cmd, "@CompanyDesc", DbType.String, Model.CompanyDesc);
            db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
            db.AddInParameter(cmd, "@PostCode", DbType.String, Model.PostCode);
            db.AddInParameter(cmd, "@province", DbType.String, Model.province);
            db.AddInParameter(cmd, "@city", DbType.String, Model.city);
            db.AddInParameter(cmd, "@county", DbType.String, Model.county);
            db.AddInParameter(cmd, "@street", DbType.String, Model.street);
            db.AddInParameter(cmd, "@CompanyEnglishName", DbType.String, Model.CompanyEnglishName);
            db.AddInParameter(cmd, "@ChargeEnglishName", DbType.String, Model.ChargeEnglishName);
            db.AddInParameter(cmd, "@EnglishAddress", DbType.String, Model.EnglishAddress);
            db.AddInParameter(cmd, "@WeightRate", DbType.Int32, Model.WeightRate);
            int a = Convert.ToInt32(db.ExecuteScalar(cmd));
            return a;
        }
        #endregion
        #region 删除
        public static void Delete(int CompanyId)
        {
            string tsql=@"
update  JC_Company set state=0 where CompanyId=@CompanyId
";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd,"@CompanyId", DbType.Int32,CompanyId);
            db.ExecuteNonQuery(cmd);
        }
        #endregion
        #region 删除
        public static void UpdateCompany(int CompanyId,int State)
        {
            string tsql = @"
update  JC_Company set state=@State where CompanyId=@CompanyId
";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
            db.AddInParameter(cmd, "@State", DbType.Int32, State);
            db.ExecuteNonQuery(cmd);
        }
        #endregion
        #region 返回Model
        public static JC_Company GetModel(int CompanyId)
        {
            JC_Company model = null;
            string tsql="select * from JC_Company where CompanyId=@CompanyId";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd,"@CompanyId", DbType.Int32,CompanyId);
            DataTable tb=db.ExecuteDataTable(cmd);
            if (tb.Rows.Count>0)model=tb.Rows[0].ToModel<JC_Company>();
            return model;
        }
        #endregion

        #region 保存人工费
        public static void  UpdatePersonFee(int CompanyId,decimal PersonFee)
        {
           
            string tsql = "update JC_Company set PersonFee=@PersonFee  where CompanyId=@CompanyId";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
              db.AddInParameter(cmd, "@PersonFee", DbType.Decimal, PersonFee);
          db.ExecuteNonQuery(cmd);
        
        }
        #endregion
        #region 分页查询
         public static List<JC_Company> GetListJC_Company(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
        {
            if (where == null) where = new RefParameterCollection();
            QueryService ser = new QueryService();
            ser.Fields = @"CompanyId=cast(a.CompanyId as int),a.CompanyCode,a.ParentCompanyId,a.CompanyName,a.SimpleName,a.CompanyType,a.Address,a.Phone,a.Fax,a.Email,a.HomePage,a.ChargeName,a.Shen,a.Shi,a.Qu,a.PostCode,a.CompanyDesc,a.State";
            ser.Tables = @"JC_Company a";
            ser.Filter = where.GetWhere(System.Data.CommandType.Text);
            ser.PageIndex = PageIndex;
            ser.PageSize = PageSize;
            ser.Sort = Sort;
            ser.KeyName = "CompanyId";
            string tsql=ser.GetText();
            where.AddOutParameter("RowCount",System.Data.DbType.Int32);;
            List<JC_Company> ListModel = null;
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, where);
            DataTable tb=db.ExecuteDataTable(cmd);
            RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
            ListModel=tb.ToList<JC_Company>();
            return ListModel;
        }
        #endregion
        #region 判断名称是否重复
         public static bool CheckCompanyName(int CompanyID, string CompanyName)
         {
             string tsql = @"select count(0) from JC_Company where CompanyID<>@CompanyID and CompanyName=@CompanyName and State=1";
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
             db.AddInParameter(cmd, "@CompanyName", DbType.String, CompanyName);
             int a = Convert.ToInt32(db.ExecuteScalar(cmd));
             if (a == 0) return true;
             return false;
         }
         #endregion
        #region 判断代码是否重复
         public static bool CheckCompanyCode(int CompanyID, string CompanyCode)
         {
             string tsql = @"select count(0) from JC_Company where CompanyID<>@CompanyID and CompanyCode=@CompanyCode and State=1";
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
             db.AddInParameter(cmd, "@CompanyCode", DbType.String, CompanyCode);
             int a = Convert.ToInt32(db.ExecuteScalar(cmd));
             if (a == 0) return true;
             return false;
         }
         #endregion
        #region 判断简称是否重复
         public static bool CheckSimpleName(int CompanyID, string SimpleName)
         {
             string tsql = @"select count(0) from JC_Company where CompanyID<>@CompanyID and SimpleName=@SimpleName and State=1";
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
             db.AddInParameter(cmd, "@SimpleName", DbType.String, SimpleName);
             int a = Convert.ToInt32(db.ExecuteScalar(cmd));
             if (a == 0) return true;
             return false;
         }
         #endregion

         #region 返回机构列表
         public static List<JC_Company> GetCompanyList(string Name)
         {

             string tsql = "select * from JC_Company where CompanyName like '%'+@Name+'%' order by CompanyName";
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, "@Name", DbType.String, Name);
             DataTable tb = db.ExecuteDataTable(cmd);
             return tb.ToList<JC_Company>();
         }
         #endregion


         #region 保存
         public static int Save_CompanyFee(JC_CompanyFee Model)
         {
             string tsql = @"
if @Id>0
begin
Update [JC_CompanyFee] set [InDate]=@InDate,[InName]=@InName,[InFee]=@InFee,[InUSDFee]=@InUSDFee,CompanyId=@CompanyId where Id=@Id
end
else
begin
INSERT INTO [JC_CompanyFee]([InDate],[InName],[InFee],[InUSDFee],CompanyId)values(@InDate,@InName,@InFee,@InUSDFee,@CompanyId)
set @Id=SCOPE_IDENTITY()
update JC_Company set NowFee=isnull(NowFee,0)+@InUSDFee where CompanyId=@CompanyId
end
select @Id";
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
             db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
             db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
             db.AddInParameter(cmd, "@InName", DbType.String, Model.InName);
             db.AddInParameter(cmd, "@InFee", DbType.Decimal, Model.InFee);
             db.AddInParameter(cmd, "@InUSDFee", DbType.Decimal, Model.InUSDFee);
             int a = Convert.ToInt32(db.ExecuteScalar(cmd));
             return a;
         }
         #endregion
         #region 删除
         public static void Delete_CompanyFee(int Id)
         {
             string tsql = @"
            delete from JC_CompanyFee where Id=@Id";
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
             db.ExecuteNonQuery(cmd);
         }
         #endregion
         #region 返回Model
         public static JC_CompanyFee GetModel_CompanyFee(int Id)
         {
             JC_CompanyFee model = null;
             //string tsql = "select a.Id,a.InDate,a.InName,a.InFee,a.InUSDFee from JC_CompanyFee";
             string tsql = @"
            select * from JC_CompanyFee where Id=@Id";
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
             DataSet ds = db.ExecuteDataSet(cmd);
             if (ds.Tables[0].Rows.Count > 0)
             {
                 model = ds.Tables[0].Rows[0].ToModel<JC_CompanyFee>();
             }
             return model;
         }
         #endregion
         #region 分页查询
         public static List<JC_CompanyFee> GetListCompanyFee(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
         {
             if (where == null) where = new RefParameterCollection();
             QueryService ser = new QueryService();
             ser.Fields = @"Id=cast(a.Id as int),a.InDate,a.InName,a.InFee,a.InUSDFee";
             ser.Tables = @"JC_CompanyFee a";
             ser.Filter = where.GetWhere(System.Data.CommandType.Text);
             ser.PageIndex = PageIndex;
             ser.PageSize = PageSize;
             ser.Sort = Sort;
             ser.KeyName = "Id";
             string tsql = ser.GetText();
             where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
             List<JC_CompanyFee> ListModel = null;
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, where);
             DataTable tb = db.ExecuteDataTable(cmd);
             RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
             ListModel = tb.ToList<JC_CompanyFee>();
             return ListModel;
         }
         #endregion

         #region 多条件普通查询
         public static List<JC_CompanyFee> GetListCompanyFee(RefParameterCollection where, string Sort)
         {
             if (where == null) where = new RefParameterCollection();
             QueryOrdService ser = new QueryOrdService();
             //ser.Fields = "a.Id,a.InDate,a.InName,a.InFee,a.InUSDFee";
             ser.Fields = "a.*";
             ser.Tables = @"JC_CompanyFee a";
             ser.Filter = where.GetWhere(System.Data.CommandType.Text);
             ser.KeyName = "Id";
             ser.Sort = Sort;
             string tsql = ser.GetText();
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, where);
             DataTable tb = db.ExecuteDataTable(cmd);
             List<JC_CompanyFee> ListModel = tb.ToList<JC_CompanyFee>();
             return ListModel;
         }
         #endregion


         #region 返回列表
         public static List<JC_CompanyFee> GetListCompanyFee()
         {
             //string tsql = "select a.Id,a.InDate,a.InName,a.InFee,a.InUSDFee from JC_CompanyFee";
             string tsql = "select * from JC_CompanyFee";
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             DataTable tb = db.ExecuteDataTable(cmd);
             List<JC_CompanyFee> ListModel = tb.ToList<JC_CompanyFee>();
             return ListModel;
         }
         #endregion


         #region 分页动态条件查询
         public static List<JC_CompanyFee> GetListCompanyFee(string where, int PageIndex, int PageSize, string Sort, out int RowCount)
         {
             QueryService2 ser = new QueryService2();
             ser.Tsql = "select Id=cast(a.Id as int),a.InDate,a.InName,a.InFee,a.InUSDFee from JC_CompanyFee a";
             ser.Tsql += " " + ser.Filter(where);
             ser.PageIndex = PageIndex;
             ser.PageSize = PageSize;
             if (string.IsNullOrEmpty(Sort) == true) { ser.Sort = "a.Id desc"; }
             else { ser.Sort = Sort; }
             string tsql = ser.GetText();
             List<JC_CompanyFee> ListModel = null;
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddOutParameter(cmd, "@RowCount", DbType.Int32, 4);
             DataTable tb = db.ExecuteDataTable(cmd);
             RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
             ListModel = tb.ToList<JC_CompanyFee>();
             return ListModel;
         }
         #endregion


         #region 判断是否重复
         public static bool CheckName_CompanyFee(int Id, string Name)
         {
             string tsql = @"select count(0) from JC_CompanyFee where Id<>@Id and Name=@Name";
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
             db.AddInParameter(cmd, "@Name", DbType.String, Name);
             int a = Convert.ToInt32(db.ExecuteScalar(cmd));
             if (a == 0) return true;
             return false;
         }
         #endregion
    }
}