unit1.~cpp

从ms-sql move data to oracle DB 的客户端

        if(!tbc->Fields->FieldByName("e_mail")->Value.IsNull())
                tempstr += tbc->Fields->FieldByName("e_mail")->Value;
                tempstr += "',to_date('";
                tempstr += tbc->Fields->FieldByName("birth_date")->Value;
                tempstr += "','yyyy/mm/dd'),";
                tempstr += get_opid(tbc->Fields->FieldByName("operator_id")->Value);
                tempstr += ",";
                tempstr += get_shop_id(tbc->Fields->FieldByName("master_id")->Value);
                tempstr += ")";

                Memo1->Lines->Add(tempstr);
                Memo1->Lines->Add("/");

                tbc->Next();                       
        }
        tbc->Close();
}
//---------------------------------------------------------------------------


void __fastcall TForm1::bt_agentClick(TObject *Sender)
{
        Memo1->Clear();
        qry->SQL->Clear();
        sqlstr = "select agent_id,b.agent_grade as grade,b.bank as bank_name,bank_address,account as bank_account,depositor as bank_depositor, china_flag as country, 0 as capacity, 0 as store_flag, a.operator_id as opid, b.master_id as mgrid, propose_date as reg_date from tbagent a inner join tbclient b on a.agent_id = b.client_id";
        qry->SQL->Add(sqlstr);
        qry->ExecSQL();
        qry->Open();
        for(int i = 0; i != qry->RecordCount; i++)
        {
                tempstr = "insert into tbagent(agent_id,grade,bank_name,bank_address,bank_account, bank_depositor,country,capacity,store_flag,opid,mgrid,reg_date) values(";
                tempstr += qry->Fields->FieldByName("agent_id")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("grade")->Value;
                tempstr += ",'";
                tempstr += qry->Fields->FieldByName("bank_name")->Value;
                tempstr += "','";
                tempstr += qry->Fields->FieldByName("bank_address")->Value;
                tempstr += "','";
                tempstr += qry->Fields->FieldByName("bank_account")->Value;
                tempstr += "','";
                tempstr += qry->Fields->FieldByName("bank_depositor")->Value;
                tempstr += "',";
                tempstr += qry->Fields->FieldByName("country")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("capacity")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("store_flag")->Value;
                tempstr += ",";
                tempstr += get_opid(qry->Fields->FieldByName("opid")->Value);
                tempstr += ",";
                tempstr += get_shop_id(qry->Fields->FieldByName("mgrid")->Value);
                tempstr += ",to_date('";
                tempstr += qry->Fields->FieldByName("reg_date")->Value;
                tempstr += "','yyyy/mm/dd'))";

                Memo1->Lines->Add(tempstr);
                Memo1->Lines->Add("/");

                qry->Next();
        }
        qry->Close();
}
//---------------------------------------------------------------------------
void __fastcall TForm1::bt_csalesClick(TObject *Sender)
{
        Memo1->Clear();
        qry->SQL->Clear();
        sqlstr = "select sales_code, sales_date, (method - 1) as method, provider_id, purchase_id, dealer_id, total_money, discount_rate, real_money, total_point, total_money as total_mpoint, 0 as intra_subsidy, 0 as cosmetic_mp, 0 as cosmetic_subsidy, 0 as capacity_flag, operator_id as opid from tbsales where status = 1 and method < 3";
        qry->SQL->Add(sqlstr);
        qry->ExecSQL();
        qry->Open();
        for(int i = 0; i != qry->RecordCount; i++)
        {
                tempstr = "insert into tbclientsales(sales_code, sales_date, method, provider_id, purchase_id, dealer_id, total_money, discount_rate, real_money, total_point, total_mpoint, intra_subsidy, cosmetic_mp, cosmetic_subsidy, capacity_flag, opid, mgrid) values (";
                tempstr += qry->Fields->FieldByName("sales_code")->Value;
                tempstr += ",to_date('";
                tempstr += qry->Fields->FieldByName("sales_date")->Value;
                tempstr += "','yyyy/mm/dd'),";
                tempstr += qry->Fields->FieldByName("method")->Value;
                tempstr += ",";
                tempstr += get_store_id(qry->Fields->FieldByName("provider_id")->Value);
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("purchase_id")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("dealer_id")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("total_money")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("discount_rate")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("real_money")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("total_point")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("total_mpoint")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("intra_subsidy")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("cosmetic_mp")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("cosmetic_subsidy")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("capacity_flag")->Value;
                tempstr += ",";
                tempstr += get_opid(qry->Fields->FieldByName("opid")->Value);
                tempstr += ",";
                tempstr += get_sales_mgrid(qry->Fields->FieldByName("opid")->Value);
                tempstr += ")";

                Memo1->Lines->Add(tempstr);
                Memo1->Lines->Add("/");

                qry->Next();
        }
        qry->Close();
}
//---------------------------------------------------------------------------
void __fastcall TForm1::bt_asalesClick(TObject *Sender)
{
        Memo1->Clear();
        qry->SQL->Clear();
        sqlstr = "select sales_code, sales_date, provider_id, purchase_id, total_money, real_money, discount_rate, total_money as total_mpoint, 0 as intra_subsidy, 0 as cosmetic_mp, 0 as cosmetic_subsidy, 0 as capacity_flag, operator_id as opid, 0 as status from tbsales where status = 1 and method = 3";
        qry->SQL->Add(sqlstr);
        qry->ExecSQL();
        qry->Open();
        for(int i = 0; i != qry->RecordCount; i++)
        {
                tempstr = "insert into tbagentsales(sales_code, sales_date, provider_id, purchase_id, total_money, discount_rate, real_money, total_mpoint, intra_subsidy, cosmetic_mp, cosmetic_subsidy, capacity_flag, opid, mgrid, status) values (";
                tempstr += qry->Fields->FieldByName("sales_code")->Value;
                tempstr += ",to_date('";
                tempstr += qry->Fields->FieldByName("sales_date")->Value;
                tempstr += "','yyyy/mm/dd'),";
                tempstr += get_store_id(qry->Fields->FieldByName("provider_id")->Value);
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("purchase_id")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("total_money")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("discount_rate")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("real_money")->Value;
                tempstr += ",";
        if (StrToInt(qry->Fields->FieldByName("provider_id")->Value) < 10000000)
                tempstr += 0;
        else
                tempstr += qry->Fields->FieldByName("total_mpoint")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("intra_subsidy")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("cosmetic_mp")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("cosmetic_subsidy")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("capacity_flag")->Value;
                tempstr += ",";
                tempstr += get_opid(qry->Fields->FieldByName("opid")->Value);
                tempstr += ",";
                tempstr += get_sales_mgrid(qry->Fields->FieldByName("opid")->Value);
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("status")->Value;
                tempstr += ")";

                Memo1->Lines->Add(tempstr);
                Memo1->Lines->Add("/");

                qry->Next();
        }
        qry->Close();
}
//---------------------------------------------------------------------------
void __fastcall TForm1::tb_slistClick(TObject *Sender)
{
        Memo1->Clear();
        qry->SQL->Clear();
        sqlstr = "select a.sales_code, a.merch_code, amount, c.price from tbslist a inner join tbsales b on a.sales_code = b.sales_code inner join tbmerch c on a.merch_code = c.merch_code where b.status = 1";
        qry->SQL->Add(sqlstr);
        qry->ExecSQL();
        qry->Open();
        for(int i = 0; i != qry->RecordCount; i++)
        {
                tempstr = "insert into tbsaleslist(sales_code, merch_code, amount, sales_price) values (";
                tempstr += qry->Fields->FieldByName("sales_code")->Value;
                tempstr += ",";
                temp_merch = qry->Fields->FieldByName("merch_code")->Value;
                temp_merch.SetLength(4);
                tempstr += temp_merch;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("amount")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("price")->Value;
                tempstr += ")";

                Memo1->Lines->Add(tempstr);
                Memo1->Lines->Add("/");

                qry->Next();
        }
        qry->Close();
}
//---------------------------------------------------------------------------
void __fastcall TForm1::tb_instoreClick(TObject *Sender)
{
        Memo1->Clear();
        qry->SQL->Clear();

        sqlstr = "select instore_code, instore_date, instore_from, instore_to, remark from tb_md_instore where status = 1";
        qry->SQL->Add(sqlstr);
        qry->ExecSQL();
        qry->Open();
        for(int i = 0; i != qry->RecordCount; i++)
        {
                tempstr = "insert into tbstockinout(sio_code, sio_date, sio_type, from_shop, to_shop, remark, opid) values (";
        temp_code = StrToInt(qry->Fields->FieldByName("instore_code")->Value);
        switch (temp_code)
        {
                case 1007:
                        temp_code = 1006;
                        break;
                case 1009:
                        temp_code = 1007;
                        break;
                case 1011:
                        temp_code = 1008;
                        break;
                case 1012:
                        temp_code = 1009;
                        break;
                default:
                        break;
        }
                tempstr += temp_code;
                tempstr += ",to_date('";
                tempstr += qry->Fields->FieldByName("instore_date")->Value;
                tempstr += "','yyyy/mm/dd'),";
                tempstr += 0;
                tempstr += ",";
                tempstr += 0;
                tempstr += ",";
                tempstr += get_store_id(qry->Fields->FieldByName("instore_to")->Value);
                tempstr += ",'";
                tempstr += qry->Fields->FieldByName("instore_from")->Value;
                tempstr += qry->Fields->FieldByName("remark")->Value;
                tempstr += "',";
                tempstr += 1008;
                tempstr += ")";

                Memo1->Lines->Add(tempstr);
                Memo1->Lines->Add("/");

                qry->Next();
        }
        qry->Close();

        qry->SQL->Clear();

        sqlstr = "select sts_code, sts_date, sts_from, sts_to, remark from tb_store_to_store where status = 1";
        qry->SQL->Add(sqlstr);
        qry->ExecSQL();
        qry->Open();
        for(int i = 0; i != qry->RecordCount; i++)
        {
                tempstr = "insert into tbstockinout(sio_code, sio_date, sio_type, from_shop, to_shop, remark, opid) values (";
        temp_code = StrToInt(qry->Fields->FieldByName("sts_code")->Value) + 10;
        if (temp_code == 1023)  temp_code = 1021;
                tempstr += temp_code;
                tempstr += ",to_date('";
                tempstr += qry->Fields->FieldByName("sts_date")->Value;
                tempstr += "','yyyy/mm/dd'),";
                tempstr += 1;
                tempstr += ",";
                tempstr += get_store_id(qry->Fields->FieldByName("sts_from")->Value);
                tempstr += ",";
                tempstr += get_store_id(qry->Fields->FieldByName("sts_to")->Value);
                tempstr += ",'";
                tempstr += qry->Fields->FieldByName("remark")->Value;
                tempstr += "',";
                tempstr += 1008;
                tempstr += ")";

                Memo1->Lines->Add(tempstr);
                Memo1->Lines->Add("/");

                qry->Next();
        }
        qry->Close();

        qry->SQL->Clear();

        sqlstr = "select in_code, in_date from tb_instore";
        qry->SQL->Add(sqlstr);
        qry->ExecSQL();
        qry->Open();
        for(int i = 0; i != qry->RecordCount; i++)
        {
                tempstr = "insert into tbstockinout(sio_code, sio_date, sio_type, from_shop, to_shop, remark, opid) values (";
                tempstr += StrToInt(qry->Fields->FieldByName("in_code")->Value) + 22;
                tempstr += ",to_date('";
                tempstr += qry->Fields->FieldByName("in_date")->Value;
                tempstr += "','yyyy/mm/dd'),";
                tempstr += 0;
                tempstr += ",";
                tempstr += 0;
                tempstr += ",";
                tempstr += 0;
                tempstr += ",'";
                tempstr += "',";
                tempstr += 1008;
                tempstr += ")";

                Memo1->Lines->Add(tempstr);
                Memo1->Lines->Add("/");

                qry->Next();
        }
        qry->Close();

        qry->SQL->Clear();

        sqlstr = "select out_code, out_date, out_from, out_to, remark from tb_outstore where status = 1 and out_type = 0";