{"text_format":"2","workbook":"xlsx","n_sheets":9,"sheets":["工作表2","Log記錄流程","Table Schema","sp_ChangeSalesStatus","DB Trigger","Function","ZH修改記錄","後台介面","Notice"],"not_rendered":{"charts":0,"images":2,"macros":0,"external_links":0,"defined_names":0,"comments":1,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"run_cost":"one","rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"工作表2","index":0,"n_rows":2,"n_cols":3,"rows_kept":2,"cut":false,"hidden":false,"merged":[[6,2,6,8],[7,2,7,8],[8,2,8,8],[0,2,0,8],[1,2,1,8],[2,2,2,8],[3,2,3,8],[4,2,4,8],[5,2,5,8]]}
["Time","Name","Remark"]
["2021-01-25","Tobey","透過ZH增加Oid_Log 統整規格書"]

{"sheet":"Log記錄流程","index":1,"n_rows":1,"n_cols":1,"rows_kept":1,"cut":false,"hidden":false,"merged":[]}
["Workflow"]

{"sheet":"Table Schema","index":2,"n_rows":26,"n_cols":11,"rows_kept":26,"cut":false,"hidden":false,"merged":[[0,8,0,9]]}
["Oid_Log","name","type","null","desc",null,null,null,"DataType清單",null,"ZH"]
["PK","Serno","bigint",{"$nulls":6},"DynaSmart受注(contract)","DynaSmart訂單(contract)"]
[null,"DataType","nvarchar(30)",{"$nulls":6},"DynaSmart受注(sales)","DynaSmart訂單(sales)"]
[null,"Action","nvarchar(10)",null,"Modify,CSVダウンロード",{"$nulls":4},"DynaSmart受注(ship)","DynaSmart訂單(ship)"]
[null,"csvType","nvarchar(20)","V",{"$nulls":5},"DynaSmart受注(status)"]
[null,"Keyword","nvarchar(4000)","V",{"$nulls":5},"DynaSmart受注管理","年度授權訂單資料"]
[null,"Modify","nvarchar(4000)","V",{"$nulls":5},"DynaSmart契約(contract)","DynaSmart契約(contract)"]
[null,"Modify_Serno","bigint","V","修改記錄序號",{"$nulls":4},"DynaSmart契約(contract_list)","DynaSmart契約(contract_list)"]
[null,"Cdate","datetime",{"$nulls":6},"DynaSmart契約(license)"]
[null,"Oid","int",{"$nulls":6},"DynaSmart契約管理","年度授權產品契約管理"]
[{"$nulls":9},"EC受注(sales)","EC訂單(sales)"]
[{"$nulls":9},"EC受注(ship)","EC訂單(ship)"]
[{"$nulls":9},"EC受注(status)"]
[{"$nulls":9},"EC受注管理","EC訂單資料"]
[{"$nulls":9},"ライセンス受注(contract)"]
[{"$nulls":9},"ライセンス受注(status)"]
[{"$nulls":9},"ライセンス受注管理"]
[{"$nulls":9},"ライセンス契約(contract)"]
[{"$nulls":9},"ライセンス契約(contract_list)"]
[{"$nulls":9},"ライセンス契約(license)"]
[{"$nulls":9},"ライセンス契約管理","盒裝授權契約管理"]
[{"$nulls":9},"顧客アンケート"]
[{"$nulls":9},"顧客管理"]
[{"$nulls":9},"顧客管理(法人)","顧客管理(法人)"]
[{"$nulls":9},"顧客管理(個人)","顧客管理(個人)"]
[{"$nulls":9},"ライセンス受注(ship)","License訂單(ship)"]

{"sheet":"sp_ChangeSalesStatus","index":3,"n_rows":28,"n_cols":9,"rows_kept":28,"cut":false,"hidden":false,"merged":[]}
["銷售狀態異動log 表"]
[]
[null,null,null,"DECLARE @sales_type_str nvarchar(30) = '';"]
[{"$nulls":6},"SET @sales_type_str = "]
[{"$nulls":7},"CASE"]
[{"$nulls":7},"WHEN @sales_type = 1 THEN 'EC訂單(status)'"]
[{"$nulls":7},"WHEN @sales_type = 2 THEN 'DynaSmart訂單(status)'"]
[{"$nulls":7},"WHEN @sales_type = 3 THEN N'License訂單(status)'"]
[{"$nulls":7},"ELSE ''"]
[{"$nulls":7},"END;"]
[{"$nulls":6},"INSERT INTO [Oid_Log] ("]
[null,"                            [DataType]"]
[null,"                            ,[Action]"]
[null,"                            ,[Modify_Serno]"]
[null,"                            ,[Oid]"]
[null,"                        ) VALUES ("]
[null,"                            @sales_type_str"]
[null,"                            ,'Modify'"]
[null,"                            ,(SELECT [sales_status_id] from [sales_status] where sales_status_flag = 1 AND sales_id = @sales_id)"]
[null,"                            ,@staff_id"]
[null,"                        );"]
[{"$nulls":7},"UPDATE [Oid_Log] SET [Modify] = (SELECT "]
[{"$nulls":8},"  CASE WHEN (ISNULL(CONVERT(nvarchar(max),@old_status_id),'') <> ISNULL(CONVERT(nvarchar(max),@new_status_id),'')) THEN N'Status：' + ISNULL((SELECT CONVERT(nvarchar(max),[status_name]) FROM [status] WHERE [status_id]=@old_status_id),'')+'->'+ISNULL((SELECT CONVERT(nvarchar(max),[status_name]) FROM [status] WHERE [status_id]=@new_status_id),'')+',' ELSE '' END "]
[{"$nulls":8},"  )"]
[{"$nulls":7},"WHERE [DataType] = @sales_type_str"]
[{"$nulls":7},"AND [Action] = 'Modify' "]
[{"$nulls":7},"AND [Modify_Serno] = (SELECT [sales_status_id] from [sales_status] where sales_status_flag = 1 AND sales_id = @sales_id) AND ([Modify] is NULL or [Modify] = '');"]
[{"$nulls":7},"DELETE FROM [Oid_Log] WHERE ([Modify] is NULL or [Modify] = '') AND [DataType] = @sales_type_str AND [Action] = 'Modify' ;"]

{"sheet":"DB Trigger","index":4,"n_rows":10,"n_cols":4,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
["目的：當下列資料表的資料異動時寫一筆log"]
["Trigger","tablename","table desc"]
[null,"contract","契約基本情報"]
[null,"contract_list","契約明細ログ"]
[null,"customer","顧客情報"]
[null,"ec_cart_list","購物車名細","作用非log記錄"]
[null,"license",null,"DataType:EC受注(status),DynaSmart受注(status),ライセンス受注(status)"]
[null,"person","顧客連絡人情報"]
[null,"sales","銷售記錄"]
[null,"ship","送貨記錄"]

{"sheet":"Function","index":5,"n_rows":15,"n_cols":6,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
[null,"File Name","Function Name","Input","Output","Desc"]
["app_code","dboperator.cs","Get_oid_log_List"]
[null,null,"Get_oid_log_Count"]
[null,null,"GetOidDatatype"]
[null,null,"GetOidAction"]
[null,null,"GetOidname"]
[null,null,"Get_Oid_Logexport_List"]
["Form","Oid_logexport.aspx.cs",null,null,null,"匯出log"]
["Form","Oid_log.cs",null,null,null,"顯示log"]
["app_code","dbcontract.cs",null,null,null,"記錄log-契約基本情報、契約明細ログ"]
["app_code","dbcustomer.cs",null,null,null,"記錄log-顧客情報、顧客連絡人情報"]
["app_code","dblicense.cs",null,null,null,"記錄log"]
["app_code","dbsales.cs",null,null,null,"記錄log-銷售記錄"]
["app_code","dbcustomer.cs","InsertOidLog",null,null,"CSVダウンロード Log"]
["app_code","dbcustomer.cs","InsertModifyOidLog",null,null,"Normal Modify Log\n目前僅有change_customer_password.aspx.cs使用"]

{"sheet":"ZH修改記錄","index":6,"n_rows":12,"n_cols":2,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
["FilePath","Operation Path"]
["/backend/customer/customerlist.aspx","客戶管理/客戶資料/CSV下載"]
["/backend/dynasmart/dynasmartlist.aspx","年度授權產品契約管理/契約資料年度授權產品契約管理"]
["/backend/dynasmartsales/dynasmartsaleslist.aspx","訂單管理/年度授權訂單資料/CSV下載"]
["/backend/licensecontract/licensecontractlist.aspx","盒裝授權訂單管理/契約資料/CSV下載"]
["dynasmartsaleslist","已設無效"]
["backend/member/questionnaireexport.aspx.cs","產品註冊管理/客戶問卷/CSV下載"]
["backend/sales/saleslist.aspx.cs","訂單管理/EC訂單資料/CSV下載"]
["app_code\\backend\\backend_dbcontract.cs","update"]
["app_code\\backend\\backend_dbcustomer.cs","update"]
["app_code\\backend\\backend_dbsales.cs","update"]
["app_code/backend/backend_dblicense.cs","update"]

{"sheet":"後台介面","index":7,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Notice","index":8,"n_rows":3,"n_cols":2,"rows_kept":3,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
["No","Remark"]
[1,"[Update_DS] Update sales 表時，sales_total與其他欄位會分開下update語法"]
[2,"[Trigger]型態是ntext無法在'inserted' 和 'deleted' 資料表上使用 (JP是欄位型能改nvarchar)\n因此所有_voice「備註」、customer.customer_businesstypeother「行業別~其他」欄位不做log\n"]