//---------------------------------------------------------------------------
// Змінює дані строки в таблиці.
//---------------------------------------------------------------------------
bool UpdateTable(AnsiString tname, int identity, TMyColumns args, AnsiString id_name)
{
bool rezult = true;
TMyColumns::iterator iter;
AnsiString str = "update "+tname+" set ";
unsigned int i=0;
for(iter = args.begin(); iter != args.end(); ++iter)
{
str += iter->first + "=:p_"+iter->first + (i == args.size()-1?" ":", ");
i++;
}
str += " where "+((id_name == NULL)? ("ID_" + tname) : id_name)+" = :p_ID_"+tname;
TIBQuery *q = CreateQuery(0, str, false);
i=0;
for(iter = args.begin(); iter != args.end(); ++iter)
{
q->ParamByName("p_"+iter->first)->Value = iter->second;
i++;
}
try{
if(identity > 0)
{
q->ParamByName("p_ID_"+tname)->Value = identity;
q->ExecSQL();
q->Transaction->Commit();
}else{
rezult = false;
}
}catch(...){
rezult = false;
}
delete q;
return rezult;
}
//---------------------------------------------------------------------------
// Вставляє стоку в таблицю.
//---------------------------------------------------------------------------
int InsertIntoTable(AnsiString tname, TMyColumns args)
{
int rezult = true;
TMyColumns::iterator iter;
AnsiString str = "insert into "+tname+" (";
AnsiString str_p = " values (";
unsigned int i=0;
for(iter = args.begin(); iter != args.end(); ++iter)
{
str += iter->first + (i == args.size()-1?" ) ":", ");
str_p += ":p_"+iter->first + (i == args.size()-1?" ) ":", ");
i++;
}
str += str_p;
TIBQuery *q = CreateQuery(0, str, false);
for(iter = args.begin(); iter != args.end(); ++iter)
{
q->ParamByName("p_"+iter->first)->Value = iter->second;
i++;
}
try{
q->ExecSQL();
q->Transaction->Commit();
// q->SQL->Clear();
// q->SQL->Add( "select @@identity as 'id'" );
// q->Open();
// rezult = q->FieldByName("id")->AsInteger;
rezult = 0;
}catch(...){
rezult = false;
}
delete q;
return rezult;
}
//---------------------------------------------------------------------------
// Удаляє строку з таблиці.
//---------------------------------------------------------------------------
bool DeleteFromTable(AnsiString tname, int identity,bool message, AnsiString id_name )
{
bool rezult = true;
bool del = false;
if(message)
{
if(MessageDlg("Ви дійсно хочете знищити запис?",
mtConfirmation, TMsgDlgButtons() << mbYes << mbNo, 0) == mrYes)
{
del = true;
}
}else{
del = true;
}
TIBQuery *q = CreateQuery(0,
"delete from "+tname+" where "+((id_name == NULL)? ("ID_" + tname) : id_name)+" = "+IntToStr(identity)
, false);
try
{
if(del){
q->ExecSQL();
q->Transaction->Commit();
}else{
rezult = false;
}
}catch(...){
rezult = false;
}
delete q;
return rezult;
}
//---------------------------------------------------------------------------
int __fastcall TDocPrihod::GetNewNomer()
{
int result = -1;
TIBQuery * q = CreateQuery(0, "select gen_id(gen_nomer_prihod,1) from rdb$database", true);
if(!q->FieldByName("gen_id")->IsNull)
result = q->FieldByName("gen_id")->AsInteger;
FNomer = result;
return result;
}
//---------------------------------------------------------------------------
float __fastcall TKassa::GetOplataNakladna(int Nomer, int Partner)
{
float result = 0;
TIBQuery * q = CreateQuery(NULL,
" select sum(suma) sumaoplata from kassa where typedok = 2 and nomerdok = "
+ IntToStr(Nomer)+ " and id_partner = "+IntToStr(Partner),true);
result = q->FieldByName("sumaoplata")->AsFloat;
delete q;
return result;
}
//---------------------------------------------------------------------------
void TDocPrihod::Fill(int nomer)
{
TIBQuery * q = CreateQuery(0,
"select nomer, datadok, id_partner, sum(kilxkist*cina) as suma from prihod where nomer = "
+IntToStr(nomer)+
" group by nomer, datadok, id_partner"
,true);
FDataDok = q->FieldByName("datadok")->AsDateTime;
FNomer = nomer;
FSuma = q->FieldByName("suma")->AsFloat;
if(!FPartner)
FPartner = new TPartner(q->FieldByName("id_partner")->AsInteger);
}
//---------------------------------------------------------------------------
void __fastcall TKassa::Fill(int id)
{
TIBQuery * q = CreateQuery(0, "select * from kassa where id_kassa="+IntToStr(id),true);
FID_Kassa = q->FieldByName("id_kassa")->AsInteger;
FID_Partner = q->FieldByName("ID_Partner")->AsInteger;
FSuma = q->FieldByName("Suma")->AsFloat;
FTypeDok = (TTypeDok)q->FieldByName("TypeDok")->AsInteger;
FNomerDok = q->FieldByName("NomerDok")->AsInteger;
FData = q->FieldByName("Data")->AsDateTime;
FComment = q->FieldByName("Comment")->AsString;
FPartner = new TPartner(q->FieldByName("id_partner")->AsInteger);
delete q;
}
//---------------------------------------------------------------------------
TIBQuery * CreateQuery(TComponent * Owner, AnsiString str, bool open)
{
TIBQuery *q = new TIBQuery(Owner);
q->Database = IBDatabase;
q->Transaction = new TIBTransaction(Application);
q->Transaction->DefaultDatabase = IBDatabase;
if(!str.IsEmpty() )
{
q->SQL->Clear();
q->SQL->Add(str);
if(open && IsConnected())
q->Open();
return q;
}else{
return new TIBQuery(Application);
}
}
//---------------------------------------------------------------------------
void TRashod::Fill(int id)
{
TIBQuery * q = CreateQuery(0, "select * from rashod where id_rashod="+IntToStr(id),true);
FID_Rashod = q->FieldByName("ID_Rashod")->AsInteger;
FID_Partner = q->FieldByName("ID_Partner")->AsInteger;
FCina = q->FieldByName("Cina")->AsFloat;
FKilxkist = q->FieldByName("Kilxkist")->AsFloat;
FID_Tovar = q->FieldByName("ID_Tovar")->AsInteger;
FNomer = q->FieldByName("Nomer")->AsInteger;
FDataDok = q->FieldByName("datadok")->AsDateTime;
}
