1 //生成导入模板
2 void creatTemplate()
3 {
4 SysExcelApplication m_application;
5 SysExcelWorkbooks m_workbooks;
6 SysExcelWorkbook m_workbook;
7 SysExcelWorksheets m_worksheets;
8 SysExcelWorksheet m_worksheet;
9 SysExcelCells m_cells;
10 int m_row = 1;
11 ;
12
13 m_application = SysExcelApplication::construct();
14 m_workbooks = m_application.workbooks();
15 m_workbook = m_workbooks.add();
16 m_worksheets = m_workbook.worksheets();
17 m_worksheet = m_worksheets.itemFromNum(1);
18 m_cells = m_worksheet.cells();
19
20 m_cells.item(1,1).value("Item number");
21 m_cells.item(1,2).value("Config number");
22 m_cells.item(1,3).value("Sales quantity");
23
24 m_worksheet.name("CustomsReport");
25 m_worksheet.columns().item(2).autoFit();
26 m_application.visible(true);
27 }
28
29 //导入数据
30 void clicked()
31 {
32 SysExcelApplication Excel;
33 SysExcelWorkbooks Books;
34 SysExcelWorkbook Book;
35 SysExcelWorksheets Sheets;
36 SysExcelWorksheet Sheet;
37 SysExcelCells Cells;
38 SysExcelCell Cell;
39 SysExcelStyles Styles;
40 SysExcelStyle Style;
41 int RowCount,RecNum,n,i;
42
43 Filename _FilenameOpen;
44 InventTable InventTable;
45 ConfigTable ConfigTable;
46 BomVersion BomVersion;
47 InventDim t_InventDim;
48 InventBatch t_InventBatch;
49 InventStdPic t_InventStdPic;
50
51 str a_ItemId[],a_ConfigId[];
52 real a_Qty[];
53
54 ItemId ItemId;
55 Qty Qty;
56 ConfigId ConfigId;
57
58 Boolean isContinue = true,isBlocked;
59
60 Dialog _Dialog;
61 DialogField dlgPath;
62 System.Exception ex;
63 #WINAPI
64 FileNameFilter filter = ['Excel Files','*.xls;*.xlsx'];//['Image Files','*.bmp;*.jpg;*.gif;*.jpeg'];
65 ;
66
67 if(!SalesLine.ItemId)
68 SalesLine_DS.delete();
69 try
70 {
71 if(!box::yesNo("Do you have import Excel template?",DialogButton::No,'Import template'))
72 {
73 this.creatTemplate();
74 return;
75 }
76 _FilenameOpen = Winapi::getOpenFileName(element.hWnd(),filter,WinAPI::getFolderPath(#CSIDL_Personal), "@SYS53008", '','');
77 if(!_FilenameOpen)
78 return ;
79 if(!box::yesNo("Are you sure Import to AX 2009?",DialogButton::No,'Import to AX2009'))
80 return ;
81
82 Excel = SysExcelApplication::construct();
83 Books = Excel.workbooks();
84 Books.open(_FilenameOpen,true);
85
86 Book = Books.item(1);
87 Sheets = Book.worksheets();
88 Sheet = Sheets.itemFromNum(1);
89 Cells = Sheet.cells();
90
91 RowCount = 2; //from second line fect data
92
93 for(RowCount = 2;Cells.item(RowCount,1).value().bStr() != "";RowCount++)
94 {
95 n = 1;
96
97 ItemId = Cells.item(RowCount,n).value().bStr(); n++;
98 ConfigId = Cells.item(RowCount,n).value().bStr(); n++;
99 Qty = Cells.item(RowCount,n).value().double();
100
101 InventTable = InventTable::find(ItemId);
102 if(InventTable)
103 {
104 //if(InventTable.QVS_ItemBlock)
105 // isBlocked = true;
106 if(isBlocked)
107 {
108 isContinue = false;
109 info(strfmt("Item number %1 is blocked!",ItemId));
110 }
111 select firstonly t_InventStdPic
112 where t_InventStdPic.ItemId == InventTable.ItemId;
113 if(!t_InventStdPic)
114 {
115 isContinue = false;
116 info(strfmt("Item number %1 has no picture!",ItemId));
117 }
118
119 select firstonly ConfigTable
120 where ConfigTable.ItemId == InventTable.ItemId;
121
122 if(ConfigTable)
123 {
124 ConfigTable = ConfigTable::find(ItemId,ConfigId);
125 select firstonly BomVersion
126 where BomVersion.BOMId == ConfigTable.BOMId
127 && BomVersion.ItemId == ItemId
128 //&& BomVersion.Active == noYes::Yes
129 && BomVersion.Approved == noYes::Yes;
130 if(!ConfigTable)
131 {
132 isContinue = false;
133 info(strfmt("Item number %1 has no configId %2!",ItemId,ConfigId));
134 }
135 if(ConfigTable.DisUsed)
136 {
137 isContinue = false;
138 info(strfmt("Item number %1's Config id %2 is 'DisUse'!",ItemId,ConfigId));
139 }
140 //if(!BomVersion)
141 //{
142 // isContinue = false;
143 // info(strfmt("Item number %1's BOM version %2 is DisUse''!",ItemId,ConfigTable.BOMId));
144 //}
145 }
146 }
147 else
148 {
149 isContinue = false;
150 info(strfmt("Item number %1 can't been found!",ItemId,ConfigId));
151 }
152
153 if(!isContinue)
154 {
155 isContinue = true;
156 continue;
157 }
158
159 if(ItemId && Qty)
160 {
161 RecNum++;
162
163 a_ItemId[RecNum] = ItemId;
164 a_ConfigId[RecNum] = ConfigId;
165 a_Qty[RecNum] = Qty;
166 }
167 }
168 }
169 catch(Exception::CLRError)
170 {
171 Excel.quit();
172 ex = CLRInterop::getLastException();
173 while( ex )
174 {
175 info( ex.get_Message() );
176 ex = ex.get_InnerException();
177 }
178 }
179 catch(Exception::Error)
180 {
181 Excel.quit();
182 global::exceptionTextFallThrough();
183 }
184 Excel.quit();
185
186 info("------------------------------------------------");
187 for(i = 1;i <= RecNum;i++)
188 {
189 SalesLine_DS.create();
190 //SalesLine.InventDimId
191 SalesLine.ItemId = strupr(a_ItemId[i]);
192
193 t_InventBatch.clear();
194 select firstonly t_InventBatch
195 where t_InventBatch.itemId == SalesLine.ItemId &&
196 (t_InventBatch.expDate == datenull() ||
197 t_InventBatch.expDate >= today());
198
199 t_InventDim.clear();
200 t_InventDim.initValue();
201 t_InventDim.configId = a_ConfigId[i];
202 t_InventDim.inventBatchId = t_InventBatch.inventBatchId;
203 t_InventDim.InventLocationId = "FPW";
204
205 SalesLine.InventDimId = InventDim::findOrCreate(t_InventDim).inventDimId;
206 SalesLine.SalesQty = a_Qty[i];
207
208 SalesLine_DS.itemIdModified(); //这里面的逻辑非常重要
209 SalesLine_DS.Write();
210
211 //info(strfmt("%1: %2",a_ItemId[i],a_Qty[i]));
212 }
213
214 info(strfmt("Import into total of %1 records!",RecNum));
215
216 SalesLine_ds.executeQuery();
217 SalesLine_ds.research();
218 }
