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());
}
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());
}
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());
}
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();
}
}
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());
}
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());
}
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>());
}
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();
}
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());
}
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());
}
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();
}
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();
}
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());
}
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());
}
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());
}
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();
}
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();
}
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();
}
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();
}
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();
}
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();
}
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>());
}
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();
}
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();
}
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());
}
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();
}
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());
}
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();
}
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());
}
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());
}