unit1.cpp

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

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

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

        qry->SQL->Clear();

        sqlstr = "select out_code, out_date, out_type, out_to, out_from, total_money, remark from tb_outstore where status = 1 and out_type != 0";
        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) + 462;
                tempstr += ",to_date('";
                tempstr += qry->Fields->FieldByName("out_date")->Value;
                tempstr += "','yyyy/mm/dd'),";
        temp_code = StrToInt(qry->Fields->FieldByName("out_type")->Value);
        switch (temp_code)
        {
                case 1:
                        temp_code = 0;
                        break;
                case 2:
                        temp_code = 4;
                        break;
                case 4:
                        temp_code = 13;
                        break;
                default:
                        temp_code = 13;
                        break;
        }
                tempstr += temp_code;
                tempstr += ",'";
                tempstr += qry->Fields->FieldByName("out_to")->Value;
                tempstr += "',";
                tempstr += get_store_id(qry->Fields->FieldByName("out_from")->Value);
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("total_money")->Value;
                tempstr += ",'";
                tempstr += qry->Fields->FieldByName("remark")->Value;
                tempstr += "',";
                tempstr += 1009;
                tempstr += ")";

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

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

        sqlstr = "select a.out_code, merch_code, amount from tb_super_outStore_detail a inner join tb_super_outStore b on a.out_code = b.out_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 += StrToInt(qry->Fields->FieldByName("out_code")->Value) + 349;
                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 += ")";

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

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

        qry->SQL->Clear();

        sqlstr = "select a.sales_code, merch_code, amount, price from tb_inner_sales_detail a inner join tb_inner_sales b on a.sales_code = b.sales_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 += StrToInt(qry->Fields->FieldByName("sales_code")->Value) + 391;
                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();

        qry->SQL->Clear();

        sqlstr = "select a.out_code, sd_id, amount, price 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 tbsaleslist(sales_code, merch_code, amount, sales_price) values (";
                tempstr += StrToInt(qry->Fields->FieldByName("out_code")->Value) + 462;
                tempstr += ",";
                tempstr += get_merch_code(qry->Fields->FieldByName("sd_id")->Value);
                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::bt_switchClick(TObject *Sender)
{
        Memo1->Clear();
        qry->SQL->Clear();

        sqlstr = "select store_id, cp_date, cp_type, sd_id, amount from tb_sd_compages_record";
        qry->SQL->Add(sqlstr);
        qry->ExecSQL();
        qry->Open();
        for(int i = 0; i != qry->RecordCount; i++)
        {
                tempstr = "insert into tbswitchmerch(sw_code, sw_date, sw_type, dept_type, dept_id, merch_code, amount, opid) values (";
                tempstr += "SEQ_TBSWITCHMERCH.nextval";
                tempstr += ",to_date('";
                tempstr += qry->Fields->FieldByName("cp_date")->Value;
                tempstr += "','yyyy/mm/dd'),";
                tempstr += 1 - StrToInt(qry->Fields->FieldByName("cp_type")->Value);
                tempstr += ",";
                tempstr += 1;
                tempstr += ",";
                tempstr += get_store_id(qry->Fields->FieldByName("store_id")->Value);
                tempstr += ",";
                tempstr += get_merch_code(qry->Fields->FieldByName("sd_id")->Value);
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("amount")->Value;
                tempstr += ",";
                tempstr += 1008;
                tempstr += ")";

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

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

}
//---------------------------------------------------------------------------

void __fastcall TForm1::bt_commClick(TObject *Sender)
{
        Memo1->Clear();
        qry->SQL->Clear();

        sqlstr = "select cyear, cmonth, agent_id, pv, gv, mpv, mgv, mpv_comm, mgv_comm, mpv_rate, mgv_rate, store_cost, grade_comm, balance_in, balance_out, payed from tbcomm";
        qry->SQL->Add(sqlstr);
        qry->ExecSQL();
        qry->Open();
        for(int i = 0; i != qry->RecordCount; i++)
        {
                tempstr = "insert into tbagentcomm(year_no, month_no, agent_id, curr_grade, pv, mpv, mdv, msv, mgv, pv_rate, dv_rate, sv_rate, gv_class, pv_comm, dv_comm, sv_comm, gv_comm, exchange_rate, intra_mp, cosmetic_mp, intra_subsidy, cosmetic_subsidy, status) values (";
                tempstr += qry->Fields->FieldByName("cyear")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("cmonth")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("agent_id")->Value;
                tempstr += ",";
                tempstr += 4;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("pv")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("mpv")->Value;
                tempstr += ",";
                tempstr += 0;
                tempstr += ",";
                tempstr += 0;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("mgv")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("mpv_rate")->Value;
                tempstr += ",";
                tempstr += 0;
                tempstr += ",";
                tempstr += 0;
                tempstr += ","; 
                tempstr += 0;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("mpv_comm")->Value;
                tempstr += ",";
                tempstr += 0;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("grade_comm")->Value;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("mgv_comm")->Value;
                tempstr += ",";
                tempstr += 0;
                tempstr += ",";
                tempstr += 0;
                tempstr += ",";
                tempstr += 0;
                tempstr += ",";
                tempstr += qry->Fields->FieldByName("store_cost")->Value;
                tempstr += ",";
                tempstr += FloatToStr(StrToFloat(qry->Fields->FieldByName("balance_in")->Value) - StrToFloat(qry->Fields->FieldByName("balance_out")->Value));
                tempstr += ",";
                tempstr += StrToInt(qry->Fields->FieldByName("payed")->Value) + 1;
                tempstr += ")";

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

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

}
//---------------------------------------------------------------------------

void __fastcall TForm1::bt_pt_partClick(TObject *Sender)
{
        Memo1->Clear();
        this->tbc->Active = true;
        for(int i = 0; i != tbc->RecordCount; i++)
        {
                        tempstr = "insert into pt_part (COMP_CD,PART_NO,PART_NM,COLOR,PART_SIZE,PART_SIZE_NM,OLD_PART_NO,PURC_PRICE,WHOLE_PRICE,PART_NO_OLD,PART_NO_OLD,PART_GROUP,ATTR1,ATTR2,ATTR3,ATTR4,ATTR5,ATTR6) values (";
                        tempstr += "'SAMHWA','";
                if(!tbc->Fields->FieldByName("PART_NO")->Value.IsNull())
                        tempstr += tbc->Fields->FieldByName("PART_NO")->Value;
                        tempstr += "','";
                if(!tbc->Fields->FieldByName("PART_NM")->Value.IsNull())
                        tempstr += tbc->Fields->FieldByName("PART_NM")->Value;
                        tempstr += "','";
                if(!tbc->Fields->FieldByName("COLOR")->Value.IsNull())
                        tempstr += tbc->Fields->FieldByName("COLOR")->Value;
                        tempstr += "','";
                if(!tbc->Fields->FieldByName("PART_SIZE")->Value.IsNull())
                        tempstr += tbc->Fields->FieldByName("PART_SIZE")->Value;
                        tempstr += "','";
                if(!tbc->Fields->FieldByName("PART_SIZE_NM")->Value.IsNull())
                        tempstr += tbc->Fields->FieldByName("PART_SIZE_NM")->Value;
                        tempstr += "','";
                if(!tbc->Fields->FieldByName("OLD_PART_NO")->Value.IsNull())
                        tempstr += tbc->Fields->FieldByName("OLD_PART_NO")->Value;
                        tempstr += "','";
                if(!tbc->Fields->FieldByName("PURC_PRICE")->Value.IsNull())
                        tempstr += tbc->Fields->FieldByName("PURC_PRICE")->Value;
                        tempstr += "','";
                if(!tbc->Fields->FieldByName("WHOLE_PRICE")->Value.IsNull())
                        tempstr += tbc->Fields->FieldByName("WHOLE_PRICE")->Value;
                        tempstr += "','";
                if(!tbc->Fields->FieldByName("PART_NO_OLD")->Value.IsNull())
                        tempstr += tbc->Fields->FieldByName("PART_NO_OLD")->Value;
                        tempstr += "');";
/* THIS IS SUBSTR()
                if(!tbc->Fields->FieldByName("SUBSTR(PART_NO,1,1)")->Value.IsNull())
                        tempstr += tbc->Fields->FieldByName("SUBSTR(PART_NO,1,1)")->Value;
                        tempstr += "','";
                if(!tbc->Fields->FieldByName("SUBSTR(PART_NO,1,1)")->Value.IsNull())
                        tempstr += tbc->Fields->FieldByName("SUBSTR(PART_NO,2,1)")->Value;
                        tempstr += "','";
                if(!tbc->Fields->FieldByName("SUBSTR(PART_NO,1,1)")->Value.IsNull())
                        tempstr += tbc->Fields->FieldByName("SUBSTR(PART_NO,3,1)")->Value;
                        tempstr += "','";
                if(!tbc->Fields->FieldByName("SUBSTR(PART_NO,1,1)")->Value.IsNull())
                        tempstr += tbc->Fields->FieldByName("SUBSTR(PART_NO,4,2)")->Value;
                        tempstr += "','";
                if(!tbc->Fields->FieldByName("SUBSTR(PART_NO,1,1)")->Value.IsNull())
                        tempstr += tbc->Fields->FieldByName("SUBSTR(PART_NO,6,3)")->Value;
                        tempstr += "','";
                if(!tbc->Fields->FieldByName("SUBSTR(PART_NO,1,1)")->Value.IsNull())
                        tempstr += tbc->Fields->FieldByName("SUBSTR(PART_NO,9,1)")->Value;
                        tempstr += "','";
                if(!tbc->Fields->FieldByName("SUBSTR(PART_NO,1,1)")->Value.IsNull())
                        tempstr += tbc->Fields->FieldByName("SUBSTR(PART_NO,10,1)")->Value;
                        tempstr += "';";  */
                        tempstr += "','";
                Memo1->Lines->Add(tempstr);
                Memo1->Lines->Add("/");
                tbc->Next();
        }
        tbc->Close();
        Memo1->Lines->SaveToFile("E:\\kkk.txt");     //save to file
}
//------------------------------------------------------------------------------