Ejemplo n.º 1
0
        public NaklFull GetFullNaklById(int naklId)
        {
            const string commandText = @"Select chz_nakl_id,
             chz_address_id As provider_id,
             chz_companies.chz_company_id As company_id,
             operation_data,
             doc_data,
             doc_num,
             chz_receive_types.chz_receive_type_id As chz_receive_type_id,

             chz_source_types.chz_source_type_id As chz_source_type_id,chz_contract_types.chz_contract_type_id As chz_contract_type_id,chz_turnover_types.chz_turnover_tupe_id As chz_turnover_tupe_id,
            contract_num

            From chz_nakls Inner Join chz_addresses On chz_nakls.provider_chz_address_id = chz_addresses.chz_address_id
			Inner Join chz_companies On chz_companies.chz_company_id = chz_addresses.chz_company_id
            Inner Join chz_receive_types On chz_nakls.chz_receive_type_id = chz_receive_types.chz_receive_type_id
            Inner Join chz_source_types On chz_nakls.chz_source_type_id = chz_source_types.chz_source_type_id
            Inner Join chz_contract_types On chz_nakls.chz_contract_type_id = chz_contract_types.chz_contract_type_id
            Inner Join chz_turnover_types On chz_nakls.chz_turnover_tupe_id = chz_turnover_types.chz_turnover_tupe_id

            Where chz_nakls.chz_nakl_id = @chz_nakl_id";

            var parameter = new NaklIdParameter()
            {
                Id = naklId
            };
            var transaction = Transact <NaklFull> .Create(ConnectionString, commandText, parameter : parameter);

            return(transaction.ExecuteReader().FirstOrDefault());
        }
Ejemplo n.º 2
0
        public IEnumerable <NaklItem> GetDirectAcceptance(int naklId)
        {
            const string commandText = @"Select chz_nakl_item_id,
            name,
            (Select Count(*) From chz_sgtins Where chz_sgtins.chz_nakl_item_id=chz_nakl_items.chz_nakl_item_id) As count,
            (vat_value * (Select Count(*) From chz_sgtins Where chz_sgtins.chz_nakl_item_id=chz_nakl_items.chz_nakl_item_id)) As nds,
            ((cost - vat_value)*(Select Count(*) From chz_sgtins Where chz_sgtins.chz_nakl_item_id=chz_nakl_items.chz_nakl_item_id)) As price,
            (cost * (Select Count(*) From chz_sgtins Where chz_sgtins.chz_nakl_item_id=chz_nakl_items.chz_nakl_item_id)) As sum,
            (Select Count(*) From chz_scanned_sgtins Where chz_scanned_sgtins.chz_nakl_item_id = chz_nakl_items.chz_nakl_item_id) As scaned_count,
            (Select Count(*) From chz_sgtins Inner Join chz_scanned_sgtins On chz_sgtins.sgtin=chz_scanned_sgtins.value 
            Where chz_scanned_sgtins.chz_nakl_item_id = chz_nakl_items.chz_nakl_item_id And
            chz_sgtins.chz_nakl_item_id = chz_nakl_items.chz_nakl_item_id
            )  As count_matched

            From chz_nakl_items

            Where chz_nakl_items.chz_nakl_id = @chz_nakl_id";

            var parameter = new NaklIdParameter()
            {
                Id = naklId
            };
            var transaction = Transact <NaklItem> .Create(ConnectionString, commandText, parameter : parameter);

            return(transaction.ExecuteReader());
        }
Ejemplo n.º 3
0
        public IEnumerable <NaklGrid> Get(int?companyId, int?year, int?month, int?statusId)
        {
            const string commandText = @"Select chz_nakls.chz_nakl_id As chz_nakl_id,doc_num,doc_data,ProviderCompany.name As provider_name,chz_nakls__list_of_accept_types.value As accept_types_value,
            chz_contract_types.value As contract_types_value,chz_nakl_statuses.value As nakl_statuses_value,class_name,(Select Sum(cost) From chz_nakl_items Where chz_nakl_items.chz_nakl_id =chz_nakls.chz_nakl_id) As sum

            From chz_nakls Inner Join  chz_addresses ProviderAddresses On chz_nakls.provider_chz_address_id = ProviderAddresses.chz_address_id
            Inner Join chz_addresses ReceiverAddresses On chz_nakls.receiver_chz_address_id=ReceiverAddresses.chz_address_id
            Inner Join chz_companies ProviderCompany On ProviderAddresses.chz_company_id=ProviderCompany.chz_company_id
            Inner Join chz_companies ReceiverCompany On ReceiverAddresses.chz_company_id=ReceiverCompany.chz_company_id
            Inner Join chz_nakls__list_of_accept_types On chz_nakls.chz_nakls__list_of_accept_type_id=chz_nakls__list_of_accept_types.chz_nakls__list_of_accept_type_id
            Inner Join chz_contract_types On chz_nakls.chz_contract_type_id=chz_contract_types.chz_contract_type_id
            Inner Join chz_nakl_statuses On chz_nakls.chz_nakl_status_id = chz_nakl_statuses.chz_nakl_status_id

             Where (@company_id is Null or ProviderCompany.chz_company_id=@company_id) And
            (@year is Null Or YEAR(chz_nakls.doc_data)=@year) And
            (@month is Null Or MONTH(chz_nakls.doc_data)=@month) And
            (@statusId is Null Or chz_nakls.chz_nakl_status_id = @statusId)";

            var parameter = new NaklFiltrParameter()
            {
                CompanyId = companyId, Year = year, Month = month, StatusId = statusId
            };
            var transaction = Transact <NaklGrid> .Create(ConnectionString, commandText, parameter : parameter);

            return(transaction.ExecuteReader());
        }
Ejemplo n.º 4
0
        public static void Write(HttpRequest httpRequest, HttpResponse httpResponse)
        {
            if (isDebug == true)
            {
                const string commandText = @"INSERT INTO chz_ui_log
                (request_data,request_host,request_method,request_url,request_body,request_query_string,response_status_code,response_result,login)
                VALUES (@request_data,@request_host,@request_method,@request_url,@request_body,@request_query_string,@response_status_code,@response_result,@login)";

                var parameter = new InsertParameter()
                {
                    Body            = httpRequest.Body,
                    Url             = httpRequest.Url,
                    Method          = httpRequest.Method,
                    QueryString     = httpRequest.QueryString,
                    ReguestDateTime = httpRequest.DateTime,
                    Host            = httpRequest.Host,
                    Login           = httpRequest.Login,
                    StatusCode      = httpResponse.StatusCode,
                    Result          = httpResponse.Result
                };

                var str = Regex.Replace(connectionString, "{login}", login);

                var conStr = Regex.Replace(str, "{password}", password);

                var transaction = Transact <object> .Create(conStr, commandText, parameter : parameter);

                transaction.ExecuteNonQuery();
            }
        }
Ejemplo n.º 5
0
        public IEnumerable <NaklStatusModel> GetNaklStatusModels()
        {
            const string commandText = "Select chz_nakl_status_id,chz_nakl_status_name From chz_nakl__list_of_status";

            var transact = Transact <NaklStatusModel> .Create(ConnectionString, commandText);

            return(transact.ExecuteReader());
        }
Ejemplo n.º 6
0
        public IEnumerable <NaklItemStatus> GetAll()
        {
            const string commandText = @"Select chz_nakl_item_status_id,value,style From chz_nakl_item_statuses";

            var transaction = Transact <NaklItemStatus> .Create(ConnectionString, commandText);

            return(transaction.ExecuteReader());
        }
Ejemplo n.º 7
0
        public int GetMaxId()
        {
            const string commandText = @"Select Max(chz_nakl_id) From chz_nakls";

            var transaction = Transact <object> .Create(ConnectionString, commandText);

            return(transaction.ExecuteScalar <int>());
        }
Ejemplo n.º 8
0
        public IEnumerable<NdsValue> GetAll()
        {
            const string commandText = @"Select chz_nds_value_id, value,is_default 
            From chz_nakls__list_of_nds_values";

            var transaction = Transact<NdsValue>.Create(ConnectionString, commandText);
            return transaction.ExecuteReader();
        }
Ejemplo n.º 9
0
        public IEnumerable <SourceTypeModel> Get()
        {
            const string commandText = @"Select chz_source_type_id,name From chz_source_types";

            var transaction = Transact <SourceTypeModel> .Create(ConnectionString, commandText);

            return(transaction.ExecuteReader());
        }
Ejemplo n.º 10
0
        public IEnumerable <Company> GetAll()
        {
            const string commandText = @"Select chz_company_id,inn,name 
            From chz_companies";

            var transaction = Transact <Company> .Create(ConnectionString, commandText);

            return(transaction.ExecuteReader());
        }
Ejemplo n.º 11
0
        public void Add(CreateNaklModel createNaklModel)
        {
            const string commandText = @"Insert Into chz_nakls(subject_id,shipper_id,operation_date,doc_num,doc_date,receive_type,source,contract_type,contract_num,is_direct,chz_nakl_status_id)
            Values(@subject_id,@shipper_id,@operation_date,@doc_num,@doc_date,@receive_type,@source,@contract_type,@contract_num,0,1)";

            var transaction = Transact <object> .Create(ConnectionString, commandText, parameter : createNaklModel);

            transaction.ExecuteNonQuery();
        }
Ejemplo n.º 12
0
        public void Add(CreateNaklItemModel createNaklItemModel)
        {
            const string commandText = @"Insert Into chz_gtins(chz_nakl_id,count,const,vat_value,prod_sell_name)
            Values(@chz_nakl_id,@count,@const,@vat_value,@prod_sell_name)";

            var transaction = Transact <object> .Create(ConnectionString, commandText, parameter : createNaklItemModel);

            transaction.ExecuteNonQuery();
        }
Ejemplo n.º 13
0
        public IEnumerable <AcceptType> GetAll()
        {
            const string commandText = @"Select chz_nakls__list_of_accept_type_id,value 
            From chz_nakls__list_of_accept_types";

            var transaction = Transact <AcceptType> .Create(ConnectionString, commandText);

            return(transaction.ExecuteReader());
        }
Ejemplo n.º 14
0
        public IEnumerable <CompanyResult> Get()
        {
            const string commandText = @"Select chz_companys.chz_company_id,org_name,inn,chz_address_id,text
            From chz_companys Inner Join chz_addresses On chz_companys.chz_company_id = chz_addresses.chz_company_id";

            var transaction = Transact <CompanyResult> .Create(ConnectionString, commandText);

            return(transaction.ExecuteReader());
        }
Ejemplo n.º 15
0
        public IEnumerable <RecesiveType> GetAll()
        {
            const string commandText = @"Select chz_receive_type_id,value,is_default 
            From chz_receive_types";

            var transaction = Transact <RecesiveType> .Create(ConnectionString, commandText);

            return(transaction.ExecuteReader());
        }
Ejemplo n.º 16
0
        public void SaveChanges(Doctor user)
        {
            const string commandText = @"Update chz_users
            Set fio=@fio,login=@login,password=@password,access_tokent=@access_tokent
            Where chz_user_id=@chz_user_id";

            var transaction = Transact <object> .Create(ConnectionString, commandText, parameter : user);

            transaction.ExecuteNonQuery();
        }
Ejemplo n.º 17
0
        public void SaveChanges(CreateNaklItemModel createNaklItemModel)
        {
            const string commandText = @"Update chz_gtins
            Set chz_nakl_id = @chz_nakl_id, const=@const, vat_value=@vat_value,count=@count,prod_sell_name=@prod_sell_name
            Where chz_gtin_id=@chz_gtin_id";

            var transaction = Transact <object> .Create(ConnectionString, commandText, parameter : createNaklItemModel);

            transaction.ExecuteNonQuery();
        }
Ejemplo n.º 18
0
        public NdsValue GetById(int id)
        {
            const string commandText = @"Select chz_nds_value_id, value,is_default
            From chz_nakls__list_of_nds_values
            Where chz_nds_value_id=@chz_nds_value_id";

            var parameter = new NdsIdParameter() { Id = id };
            var transaction = Transact<NdsValue>.Create(ConnectionString, commandText,parameter:parameter);
            return transaction.ExecuteReader().FirstOrDefault();
        }
Ejemplo n.º 19
0
        public IEnumerable<Address> GetCompanyAddresses(int companyId)
        {
            const string commandText = @"Select chz_address_id,text
            From chz_companies Inner Join chz_addresses On chz_companies.chz_company_id=chz_addresses.chz_company_id
            Where chz_companies.chz_company_id=@chz_company_id";

            var parameter = new Companies.CompanyIdParameter() { Id = companyId };
            var transaction = Transact<Address>.Create(ConnectionString, commandText, parameter: parameter);
            return transaction.ExecuteReader();
        }
Ejemplo n.º 20
0
        public void SaveChanges(UserModel userModel)
        {
            const string commandText = @"Update chz_users
            Set first_name=@first_name,last_name=@last_name,login=@login,password=@password,access_token=@access_token,date_time=@date_time,is_lock=@is_lock
            Where chz_user_id = @chz_user_id";

            var transact = Transact <UserModel> .Create(ConnectionString, commandText, parameter : userModel);

            transact.ExecuteNonQuery();
        }
Ejemplo n.º 21
0
        public IEnumerable<Address> GetUserAddresses(int userId)
        {
            const string commandText = @"Select chz_address_id, chz_addresses.sys_id,text
            From chz_addresses Inner Join chz_companies On chz_addresses.chz_company_id=chz_companies.chz_company_id
            Inner Join doctors On doctors.chz_company_id=chz_companies.chz_company_id
            Where doctor_id=@doctor_id";

            var parameter = new Doctor.IdParameter() { Id = userId };
            var transaction = Transact<Address>.Create(ConnectionString, commandText, parameter: parameter);
            return transaction.ExecuteReader();
        }
Ejemplo n.º 22
0
        public int GetAccepTypeId(int nakId)
        {
            const string commandText = @"Select chz_nakls__list_of_accept_type_id From chz_nakls
            Where chz_nakls.chz_nakl_id=@chz_nakl_id";

            var parameter = new NaklIdParameter()
            {
                Id = nakId
            };
            var transaction = Transact <object> .Create(ConnectionString, commandText, parameter : parameter);

            return(transaction.ExecuteScalar <int>());
        }
Ejemplo n.º 23
0
        public void Delete(int naklId)
        {
            const string commandText = @"Delete chz_nakls
            Where chz_nakl_id = @chz_nakl_id";

            var parameter = new NaklIdParameter()
            {
                NaklId = naklId
            };
            var transaction = Transact <object> .Create(ConnectionString, commandText, parameter : parameter);

            transaction.ExecuteNonQuery();
        }
Ejemplo n.º 24
0
        public void Reset(int gtinId)
        {
            const string commandText = @"Delete From chz_scanned_sgtins
            Where chz_gtin_id =  @chz_gtin_id";

            var parameter = new GtinIdParameter()
            {
                Id = gtinId
            };
            var transaction = Transact <SgtinModel> .Create(ConnectionString, commandText, parameter : parameter);

            transaction.ExecuteNonQuery();
        }
Ejemplo n.º 25
0
        public IEnumerable <Sgtin> Get(int naklItemId)
        {
            const string commandText = @"Select value From chz_scanned_sgtins
             Where chz_nakl_item_id=@chz_nakl_item_id";

            var parameter = new NaklItemIdParameter()
            {
                Id = naklItemId
            };
            var transaction = Transact <Sgtin> .Create(ConnectionString, commandText, parameter : parameter);

            return(transaction.ExecuteReader());
        }
Ejemplo n.º 26
0
        public void Update(int naklItemId, decimal cost, decimal vat_value, int?ndsValue)
        {
            const string commandText = @"Update chz_nakl_items
            Set cost=@cost, vat_value=@vat_value, nds=@nds
            Where chz_nakl_item_id=@chz_nakl_item_id;";

            var parameter = new NaklItemUpdateParameter()
            {
                Id = naklItemId, Cost = cost, VatValue = vat_value, Nds = ndsValue
            };
            var transaction = Transact <object> .Create(ConnectionString, commandText, parameter : parameter);

            transaction.ExecuteNonQuery();
        }
Ejemplo n.º 27
0
        public Company GetUserCompany(int userId)
        {
            const string commandText = @"Select chz_companies.chz_company_id, chz_companies.inn,name
            From doctors Inner Join chz_companies On doctors.chz_company_id=chz_companies.chz_company_id
			where doctor_id = @doctor_id"            ;

            var parameter = new Doctor.IdParameter()
            {
                Id = userId
            };
            var transaction = Transact <Company> .Create(ConnectionString, commandText, parameter : parameter);

            return(transaction.ExecuteReader().FirstOrDefault());
        }
Ejemplo n.º 28
0
        public void Exit(string accessToken)
        {
            const string commandText = @"Update chz_users
            Set access_token = null
            Where access_token = @access_token";

            var parameter = new UserAccessTokenParameter()
            {
                AccessToken = accessToken
            };
            var transaction = Transact <object> .Create(ConnectionString, commandText, parameter : parameter);

            transaction.ExecuteNonQuery();
        }
Ejemplo n.º 29
0
        public UserModel Get(string accessToken)
        {
            const string commandText = @"Select chz_user_id,first_name,last_name,login,password,access_token,date_time,is_lock
            From chz_users
            Where is_lock=0 And access_token = @access_token";

            var parameter = new UserAccessTokenParameter()
            {
                AccessToken = accessToken
            };
            var transaction = Transact <UserModel> .Create(ConnectionString, commandText, parameter : parameter);

            return(transaction.ExecuteReader().FirstOrDefault());
        }
Ejemplo n.º 30
0
        public UserModel Get(string login, string password)
        {
            const string commandText = @"Select chz_user_id,first_name,last_name,login,password,access_token,date_time,is_lock
            From chz_users
            Where is_lock=0 And login=@login And password = @password";

            var parametr = new GetUserParametr()
            {
                Login = login, Password = password
            };
            var transact = Transact <UserModel> .Create(ConnectionString, commandText, parameter : parametr);

            return(transact.ExecuteReader().FirstOrDefault());
        }