unit1.cpp

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

        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("out_code")->Value) + 61;
                tempstr += ",to_date('";
                tempstr += qry->Fields->FieldByName("out_date")->Value;
                tempstr += "','yyyy/mm/dd'),";
                tempstr += 1;
                tempstr += ",";
                tempstr += get_store_id(qry->Fields->FieldByName("out_from")->Value);
                tempstr += ",";
                tempstr += get_store_id(qry->Fields->FieldByName("out_to")->Value);
                tempstr += ",'";
                tempstr += qry->Fields->FieldByName("remark")->Value;
                tempstr += "',";
                tempstr += 1008;
                tempstr += ")";

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

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

}
//---------------------------------------------------------------------------
void __fastcall TForm1::bt_instore_listClick(TObject *Sender)
{
        Memo1->Clear();

        qry->SQL->Clear();
        sqlstr = "select a.instore_code, merch_code, amount from tb_md_instore_detail a inner join tb_md_instore b on a.instore_code = b.instore_code where b.status = 1";
        qry->SQL->Add(sqlstr);
        qry->ExecSQL();
        qry->Open();
        for(int i = 0; i != qry->RecordCount; i++)
        {
                tempstr = "insert into tbstockinoutlist(sio_code, merch_code, amount) 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 += ",";
                temp_merch = qry->Fields->FieldByName("merch_code")->Value;
                temp_merch.SetLength(4);
                tempstr += temp_merch;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("amount")->Value;
                tempstr += ")";

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

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

        qry->SQL->Clear();
        sqlstr = "select a.sts_code, merch_code, amount from tb_store_to_store_detail a inner join tb_store_to_store b on a.sts_code = b.sts_code where b.status = 1";
        qry->SQL->Add(sqlstr);
        qry->ExecSQL();
        qry->Open();
        for(int i = 0; i != qry->RecordCount; i++)
        {
                tempstr = "insert into tbstockinoutlist(sio_code, merch_code, amount) values (";
        temp_code = StrToInt(qry->Fields->FieldByName("sts_code")->Value) + 10;
        if (temp_code == 1023)  temp_code = 1021;
                tempstr += temp_code;
                tempstr += ",";
                temp_merch = qry->Fields->FieldByName("merch_code")->Value;
                temp_merch.SetLength(4);
                tempstr += temp_merch;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("amount")->Value;
                tempstr += ")";

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

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

        qry->SQL->Clear();
        sqlstr = "select in_code, sd_id, amount from tb_instore";
        qry->SQL->Add(sqlstr);
        qry->ExecSQL();
        qry->Open();
        for(int i = 0; i != qry->RecordCount; i++)
        {
                tempstr = "insert into tbstockinoutlist(sio_code, merch_code, amount) values (";
                tempstr += StrToInt(qry->Fields->FieldByName("in_code")->Value) + 22;
                tempstr += ",";
                tempstr += get_merch_code(qry->Fields->FieldByName("sd_id")->Value);
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("amount")->Value;
                tempstr += ")";

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

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

        qry->SQL->Clear();
        sqlstr = "select a.out_code, sd_id, amount from tb_outstore_detail a inner join tb_outstore b on a.out_code = b.out_code where b.status = 1 and b.out_type = 0";
        qry->SQL->Add(sqlstr);
        qry->ExecSQL();
        qry->Open();
        for(int i = 0; i != qry->RecordCount; i++)
        {
                tempstr = "insert into tbstockinoutlist(sio_code, merch_code, amount) values (";
                tempstr += StrToInt(qry->Fields->FieldByName("out_code")->Value) + 61;
                tempstr += ",";
                tempstr += get_merch_code(qry->Fields->FieldByName("sd_id")->Value);
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("amount")->Value;
                tempstr += ")";

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

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

        sqlstr = "select agent_id, merch_code, amount from tbInvent where agent_id < 10000000 order by agent_id";
        qry->SQL->Add(sqlstr);
        qry->ExecSQL();
        qry->Open();
        for(int i = 0; i != qry->RecordCount; i++)
        {
                tempstr = "insert into tbshopstock(shop_id, merch_code, amount) values (";
                tempstr += get_store_id(qry->Fields->FieldByName("agent_id")->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 += ")";

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

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

        qry->SQL->Clear();

        sqlstr = "select iv_id, sd_id, amount from tb_Invent where iv_id < 10000000 order by iv_id";
        qry->SQL->Add(sqlstr);
        qry->ExecSQL();
        qry->Open();
        for(int i = 0; i != qry->RecordCount; i++)
        {
                tempstr = "insert into tbshopstock(shop_id, merch_code, amount) values (";
                tempstr += get_store_id(qry->Fields->FieldByName("iv_id")->Value);
                tempstr += ",";
                tempstr += get_merch_code(qry->Fields->FieldByName("sd_id")->Value);
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("amount")->Value;
                tempstr += ")";

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

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

        sqlstr = "select agent_id, merch_code, amount from tbInvent where agent_id > 10000000 order by agent_id";
        qry->SQL->Add(sqlstr);
        qry->ExecSQL();
        qry->Open();
        for(int i = 0; i != qry->RecordCount; i++)
        {
                tempstr = "insert into tbagentstock(agent_id, merch_code, amount, agent_sales, client_bonus) values (";
                tempstr += qry->Fields->FieldByName("agent_id")->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 += 0;
                tempstr += ",";
                tempstr += 0;
                tempstr += ")";

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

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

        qry->SQL->Clear();

        sqlstr = "select iv_id, sd_id, amount from tb_Invent where iv_id > 10000000 order by iv_id";
        qry->SQL->Add(sqlstr);
        qry->ExecSQL();
        qry->Open();
        for(int i = 0; i != qry->RecordCount; i++)
        {
                tempstr = "insert into tbagentstock(agent_id, merch_code, amount, agent_sales, client_bonus) values (";
                tempstr += qry->Fields->FieldByName("iv_id")->Value;
                tempstr += ",";
                tempstr += get_merch_code(qry->Fields->FieldByName("sd_id")->Value);
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("amount")->Value;
                tempstr += ",";
                tempstr += 0;
                tempstr += ",";
                tempstr += 0;
                tempstr += ")";

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

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

        sqlstr = "select out_code, out_date, out_from, out_to, remark from tb_super_outStore where status = 1";
        qry->SQL->Add(sqlstr);
        qry->ExecSQL();
        qry->Open();
        for(int i = 0; i != qry->RecordCount; i++)
        {
                tempstr = "insert into tbspcsales(sales_code, sales_date, method, purchase_name, provider_id, total_money, details, opid) values (";
                tempstr += StrToInt(qry->Fields->FieldByName("out_code")->Value) + 349;
                tempstr += ",to_date('";
                tempstr += qry->Fields->FieldByName("out_date")->Value;
                tempstr += "','yyyy/mm/dd'),";
                tempstr += 0;
                tempstr += ",'";
                tempstr += qry->Fields->FieldByName("out_to")->Value;
                tempstr += "',";
                tempstr += get_store_id(qry->Fields->FieldByName("out_from")->Value);
                tempstr += ",";
                tempstr += 0;
                tempstr += ",'";
        if (!qry->Fields->FieldByName("remark")->Value.IsNull())
                tempstr += qry->Fields->FieldByName("remark")->Value;
                tempstr += "',";
                tempstr += 1009;
                tempstr += ")";

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

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

        qry->SQL->Clear();

        sqlstr = "select sales_code, sales_date, provider, purchase, total_money from tb_inner_sales where status = 1";
        qry->SQL->Add(sqlstr);
        qry->ExecSQL();
        qry->Open();
        for(int i = 0; i != qry->RecordCount; i++)
        {
                tempstr = "insert into tbspcsales(sales_code, sales_date, method, purchase_name, provider_id, total_money, details, opid) values (";
                tempstr += StrToInt(qry->Fields->FieldByName("sales_code")->Value) + 391;
                tempstr += ",to_date('";
                tempstr += qry->Fields->FieldByName("sales_date")->Value;
                tempstr += "','yyyy/mm/dd'),";
                tempstr += 1;
                tempstr += ",'";
                tempstr += qry->Fields->FieldByName("purchase")->Value;
                tempstr += "',";
                tempstr += get_store_id(qry->Fields->FieldByName("provider")->Value);
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("total_money")->Value;
                tempstr += ",'";
                tempstr += "',";
                tempstr += 1009;
                tempstr += ")";