""" constants used throughout the data import script"""

#ConnectionString = 'Data Source=WOLF\\SQLEXPRESS; Initial Catalog=carleyfoundry2; User=sa; Password='
#ConnectionString = 'Data Source=SELENIUM\\SQLEXPRESS; Initial Catalog=carley; User=sa; Password=123'
ConnectionString = 'Data Source=SELENIUM; Initial Catalog=carley; User=sa; Password=123'
#ConnectionStringDest = 'Data Source=SELENIUM\\SQLEXPRESS; Initial Catalog=carleyfoundry2; User=sa; Password=123'
ConnectionStringDest = 'Data Source=SELENIUM; Initial Catalog=carleyfoundry2_preview; User=sa; Password=123'

ProcMapFile = 'process-mapping.csv'

#ImageSourceDir = 'D:\\temp\\carley-foundry\\qadisp4\\qadisp4\\IMAGES\\'
#ImageSourceDir = 'D:\\temp\\carley-foundry\\qadisp4\\qadisp4\\'
#ImageSourceDir = 'D:\\temp\\bgz20602-carleyfoundry\\img\\'
#ImageSourceDir = 'C:\\proj\\carley-work\\img\\'
ImageSourceDir = 'f:\\carley-work\\img\\'

ImageDstDir = 'files' # this gets added to the working dir

DoFileCopy = True


##########################
# NO EDITS BELOW THIS LINE
##########################

#AnnDllFiles = [
#    'C:\\LEADTOOLS 19\\Bin\\Dotnet\\Win32\\Leadtools.Kernel.Annotations.dll',
#    'D:\\LEADTOOLS 18\\Bin\\Dotnet\\Win32\\Leadtools.Annotations.dll',
#]

#AnnDllFile = 'C:\\LEADTOOLS 19\\Bin\\Dotnet\\Win32\\Leadtools.Annotations.Core.dll'


#FileCopyPattern = [(0, 2), (2, 4)] # used by ImporterUtil.copyFile
FileCopyPattern = []

ImageMainTmpl = '{}-main' # this is how formatted main images for parts

LtannDllFile = 'D:\\temp\\carley-foundry\\qadisp4\\qadisp4\\Dll\\LTANN62N.DLL'  # not needed anymore
LtannExt = '.ann'
AnnExt = '.wmf'

GcThreshold = 100

# ImporterUtil module adds more functions to FormControlMap
FormControlMap = {
    '1': { 'new_id': '2' }, # one more textarea (obsolete textarea)
    '2': { 'new_id': '7' }, # image
    '3': { 'new_id': '2' }, # memo/textarea
    '4': { 'new_id': '1' }, # edit/input
    '5': { 'new_id': '5' }, # checkbox
    '6': { 'new_id': '6' }, # radiogroup
    '7': { 'new_id': '3' }, # label
    #'8': { 'new_id': '0' }, # bevel
    '9': { 'new_id': '7' }, # legend
    '10': { 'new_id': '2' }, # notebox - ?
    '11': { 'new_id': '7' }, # video
    '12': { 'new_id': '7' }, # drawing - ?
    '16': { 'new_id': '3' }, # label for header
    #'18': { 'new_id': '0' }, # QA button - ?
    #'20': { 'new_id': '0' }, # QA grid (1) - N/A
    #'21': { 'new_id': '0' }, # richtext (1) - N/A
}
FormControlEmpty = { 'new_id': '0' }

_tempTableName = '#fmap'

SqlTempTable = """ 
CREATE TABLE [dbo].[""" + _tempTableName + """](
	[fname] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[fpath] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[duplicate] bit default 0,
    PRIMARY KEY CLUSTERED ([fname] ASC, [fpath] ASC)
)"""

SqlTempInsert = """insert """ + _tempTableName + """ (fname, fpath, duplicate) values(@filename, @filepath, @duplicate)"""


SqlQueries = {
    'clients': 'select CustomerID, CustomerName from CUSTOMER',

    'parts1': """
        select 
        m.MasterID partId,
        case when m.IsReleased = 1 then 'n' else 'y' end preProduction,
        m.PartNumber number,
        m.MasterName name,
        m.CustomerID clientId,
        dp.Weight weight,
        dp.Alloy alloyTemper,
        case when (select count(*) from REVMAST rm where rm.MasterID = m.MasterId) > 0 then 'tracked' else 'untracked' end status,
        m.ThumbnailImage imageThumb,
        0 imageThumbSize,
        (
	        select top 1 isnull(lt.ValueMemo, d.ValueString)
	        from MASTPROC mp 
	        join PROCESS p on mp.DeptID = p.DeptID and mp.ProcessID = p.ProcessID
	        join DEPT de on p.DeptID = de.DeptID
	        join LAYOUT l on p.LayoutID = l.LayoutID
	        join DETAIL d on m.MasterID = d.MasterID and p.DeptID = d.DeptID and p.ProcessID = d.ProcessID and l.FormComponentID = d.FormComponentID
	        left join LONGTEXT lt on d.IsLong = 1 and (case when isnumeric(d.ValueString) = 1 and d.IsLong = 1 then cast(d.ValueString as int) else 0 end) = lt.LongID
	        where mp.MasterID = m.MasterID 
	        and l.FormComponentType = 2
	        order by de.RecordOrder, p.RecordOrder, d.PageNum
        ) imageMain,
        0 imageMainSize,
        (select top 1 lower(fpath) from """ + _tempTableName + """ fm join DETAIL dd on fm.fname = dd.ValueString where dd.MasterID = m.MasterID group by fpath order by count(*) desc) pathHint
        from "MASTER" m
        left join DetailPart dp on m.PartNumber = dp.PartNumber""",
    'parts2': """ 
        select 
        MasterID partId, 
        DeptID, 
        ProcessID,
        '' Dept1,
        '' Dept2,
        '' Process
        from MASTPROC""",

    'forms1': """
        select 
        '' Dept1,
        '' Dept2,
        '' Process,
        p.DeptID,
        p.ProcessID,
        p.LayoutID,
        case when 
            (select count(*) from LAYOUT l11 where ld.LayoutID = l11.LayoutID) = 1 
            and exists (select 1 from LAYOUT l12 where ld.LayoutID = l12.LayoutID and l12.FormComponentType in (2, 11))
            then 'y' 
            else 'n' end isMedia,
        (select max(l21.FormComponentTop + l21.FormComponentHeight) from LAYOUT l21 where ld.LayoutID = l21.LayoutID) height
        from PROCESS p
        join LAYDESC ld on p.LayoutID = ld.LayoutID""",
    'forms2': """
        select 
        '' Dept1,
        '' Dept2,
        '' Process,
        l.FormComponentID controlId,
        p.DeptID,
        p.ProcessID,
        p.LayoutID,
        l.FormComponentType typeIdOld,
        l.FormComponentType typeId,
        l.FormComponentLeft "left",
        l.FormComponentTop "top",
        l.FormComponentWidth "width",
        l.FormComponentHeight "height",
        l.FormComponentTitle "title", 
        l.Description "descriptionAltTitle",
        '' defaultValues,
        0 defaultValuesSize,
        (l.FormComponentID + 1) * 10 zIndex,
        l.MaxLength maxLength,
        l.FontName fontName,
        l.FontSize fontSize
        from PROCESS p
        join LAYDESC ld on p.LayoutID = ld.LayoutID
        join LAYOUT l on ld.LayoutID = l.LayoutID""",
    'formdata': [
        {
            'source': '0',
            'query': """ 
                select 
                m.MasterID partId,
                m.PartNumber partNumber,
                '' Dept1,
                '' Dept2,
                '' Process,
                l.FormComponentID controlId,
                d.PageNum pageNum,
                p.DeptID DeptID,
                replace(de.DeptCaption, '^^', ' ') DeptCaption,
                p.ProcessID ProcessID,
                replace(p.ProcessCaption, '^^', ' ') ProcessCaption,
                p.LayoutID LayoutID,
                l.FormComponentType typeIdOld,
                l.FormComponentType typeId,
                l.FormComponentWidth orgWidth,
                l.FormComponentHeight orgHeight,
                '' fieldName,
                isnull(lt.ValueMemo, d.ValueString) resultValue,
                0 fileSize,
                (select top 1 lower(fpath) from """ + _tempTableName + """ fm join DETAIL dd on fm.fname = dd.ValueString where dd.MasterID = m.MasterID group by fpath order by count(*) desc) pathHint,
                0 fileExists
                from "MASTER" m
                join MASTPROC mp on m.MasterID = mp.MasterID
                join PROCESS p on mp.DeptID = p.DeptID and mp.ProcessID = p.ProcessID
                join DEPT de on p.DeptID = de.DeptID
                join LAYOUT l on p.LayoutID = l.LayoutID
                join DETAIL d on m.MasterID = d.MasterID and p.DeptID = d.DeptID and p.ProcessID = d.ProcessID and l.FormComponentID = d.FormComponentID
                left join LONGTEXT lt on d.IsLong = 1 and (case when isnumeric(d.ValueString) = 1 and d.IsLong = 1 then cast(d.ValueString as int) else 0 end) = lt.LongID
                where not exists (select 1 from FIELDS f where p.DeptID = f.DeptID and p.ProcessID = f.ProcessID and l.FormComponentID = f.FormComponentID)""",
            'aux_query': '',
            'check_path_hint': True,
        },
        {
            'source': '901',
            'query': """ 
                select 
                m.MasterID partId,
                m.PartNumber partNumber,
                '' Dept1,
                '' Dept2,
                '' Process,
                l.FormComponentID controlId,
                od.PageNum pageNum,
                p.DeptID DeptID,
                replace(de.DeptCaption, '^^', ' ') DeptCaption,
                p.ProcessID ProcessID,
                replace(p.ProcessCaption, '^^', ' ') ProcessCaption,
                p.LayoutID LayoutID,
                l.FormComponentType typeIdOld,
                l.FormComponentType typeId,
                l.FormComponentWidth orgWidth,
                l.FormComponentHeight orgHeight,
                f.FieldName fieldName,
                od.FileName resultValue,
                0 fileSize,
                '' pathHint,
                0 fileExists
                from "MASTER" m
                join MASTPROC mp on m.MasterID = mp.MasterID
                join PROCESS p on mp.DeptID = p.DeptID and mp.ProcessID = p.ProcessID
                join DEPT de on p.DeptID = de.DeptID
                join LAYOUT l on p.LayoutID = l.LayoutID
                join FIELDS f on p.DeptID = f.DeptID and p.ProcessID = f.ProcessID and l.FormComponentID = f.FormComponentID and f.SourceID = 901
                left join ObjectsDetail od on m.PartNumber = od.PartNumber and f.DeptID = od.DeptID and f.ProcessID = od.ProcessID and f.FormComponentID = od.FormComponentID""",
            'aux_query': '',
            'check_path_hint': False,
        },
    ],
}

_detailSqlTmpl = """ 
    select 
    m.MasterID partId,
    m.PartNumber partNumber,
    '' Dept1,
    '' Dept2,
    '' Process,
    l.FormComponentID controlId,
    -1 pageNum,
    p.DeptID DeptID,
    replace(de.DeptCaption, '^^', ' ') DeptCaption,
    p.ProcessID ProcessID,
    replace(p.ProcessCaption, '^^', ' ') ProcessCaption,
    p.LayoutID LayoutID,
    l.FormComponentType typeIdOld,
    l.FormComponentType typeId,
    l.FormComponentWidth orgWidth,
    l.FormComponentHeight orgHeight,
    f.FieldName fieldName,
    '' resultValue,
    0 fileSize,
    '' pathHint,
    0 fileExists
    from "MASTER" m
    join MASTPROC mp on m.MasterID = mp.MasterID
    join PROCESS p on mp.DeptID = p.DeptID and mp.ProcessID = p.ProcessID
    join DEPT de on p.DeptID = de.DeptID
    join LAYOUT l on p.LayoutID = l.LayoutID
    join FIELDS f on p.DeptID = f.DeptID and p.ProcessID = f.ProcessID and l.FormComponentID = f.FormComponentID and f.SourceID = {source}"""

_detailAuxSqlTmpl = 'select * from {table}'

_detailSqlData = [
    (1, 'DetailPart', 'PartNumber'),
    (2, 'detailInspect', 'PartNumber'),
    (3, 'DetailColdBox', 'PartNumber'),
    (4, 'DetailDrySand', 'PartNumber'),
    (5, 'DetailShell', 'PartNumber'),
    (6, 'detailpart2', 'partnumber'),
    (7, 'Rec_Insp', 'partnumber'),
    (8, 'Detail_Invest', 'PartNumber'),
    (9, 'Detail_Quality', 'PartNumber'),
    (10, 'Detail_cleaning', 'PartNumber'),
]

SqlQueries['formdata'] += [{
                            'source': source, 
                            'query': _detailSqlTmpl.format(source=source), 
                            'aux_query': _detailAuxSqlTmpl.format(table=table_name), 
                            'key': 'partNumber',
                            'aux_key': key_name,
                            'check_path_hint': False,
                            } 
                           for source, table_name, key_name in _detailSqlData]


SQL_TEMP_TABLE_CLIENT = 'tmp_Client'
SQL_TEMP_TABLE_PART = 'tmp_Part'
SQL_TEMP_TABLE_PROCESS = 'tmp_Process'
SQL_TEMP_TABLE_PROCESS_ELEMENTS = 'tmp_ProcessElements'
SQL_TEMP_TABLE_PROCESS_PARTS = 'tmp_ProcessParts'
SQL_TEMP_TABLE_PROCESS_DATA = 'tmp_ProcessData'

SQL_DROP_IF_EXISTS = """
if exists(select 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME = '{0}') begin
    drop table {0};
end;
"""

SqlDestinationTables = \
    SQL_DROP_IF_EXISTS.format(SQL_TEMP_TABLE_CLIENT) \
    + """
    create table """ + SQL_TEMP_TABLE_CLIENT + """(
        [number] varchar(255) not null primary key,
        [name] varchar(255) not null
    );
    """ \
    + SQL_DROP_IF_EXISTS.format(SQL_TEMP_TABLE_PART) \
    + """
    create table """ + SQL_TEMP_TABLE_PART + """(
        [oldPartId] int not null default 0,
        [preProduction] char(1) not null default 'y',
        [number] varchar(255) not null default '',
        [name] varchar(255) not null default '',
        [clientId] int not null default 0,
        [weight] varchar(20) not null default '',
        [alloyTemper] varchar(255) not null default '',
        [status] varchar(255) not null default '',
        [thumb] varchar(255) default null,
        [thumbSize] int not null default 0,
        [image] varchar(255) default null,
        [imageSize] int not null default 0,
        [pathHint] varchar(255) not null default ''
    );
    """ \
    + SQL_DROP_IF_EXISTS.format(SQL_TEMP_TABLE_PROCESS) \
    + """
    create table """ + SQL_TEMP_TABLE_PROCESS + """(
        [departmentName] varchar(255) not null,
        [subDepartmentName] varchar(255) not null,
        [processName] varchar(255) not null,
        [departmentId] int not null,
        [processId] int not null,
        [layoutId] int not null,
        [isMedia] char(1) not null,
        [height] int not null
    );
    """ \
    + SQL_DROP_IF_EXISTS.format(SQL_TEMP_TABLE_PROCESS_ELEMENTS) \
    + """
    create table """ + SQL_TEMP_TABLE_PROCESS_ELEMENTS + """(
        [departmentName] varchar(255) not null,
        [subDepartmentName] varchar(255) not null,
        [processName] varchar(255) not null,
        [controlId] int not null,
        [departmentId] int not null,
        [processId] int not null,
        [layoutId] int not null,
        [typeOldId] int not null,
        [typeId] int not null,
        [left] int not null,
        [top] int not null,
        [width] int not null,
        [height] int not null,
        [title] varchar(255) not null,
        [description] varchar(255) not null,
        [defaultValue] varchar(255) not null,
        [defaultValuesSize] int not null,
        [zIndex] int not null,
        [maxLength] varchar(255) not null,
        [fontName] varchar(255) not null,
        [fontSize] int not null
    );
    """ \
    + SQL_DROP_IF_EXISTS.format(SQL_TEMP_TABLE_PROCESS_PARTS) \
    + """
    create table """ + SQL_TEMP_TABLE_PROCESS_PARTS + """(
        [partId] int not null,
        [departmentId] int not null,
        [processId] int not null,
        [departmentName] varchar(255) not null,
        [subDepartmentName] varchar(255) not null,
        [processName] varchar(255) not null
    );
    """ \
    + SQL_DROP_IF_EXISTS.format(SQL_TEMP_TABLE_PROCESS_DATA) \
    + """
    create table """ + SQL_TEMP_TABLE_PROCESS_DATA + """ (
        [partId] int not null,
        [partNumber] varchar(255) not null,
        [departmentName] varchar(255) not null,
        [subDepartmentName] varchar(255) not null,
        [processName] varchar(255) not null,
        [controlId] int not null,
        [pageNum] int not null,
        [departmentId] int not null,
        [departmentCaption] varchar(255) not null,
        [processId] int not null,
        [processCaption] varchar(255) not null,
        [layoutId] int not null,
        [typeOldId] int not null,
        [typeId] int not null,
        [width] int not null,
        [height] int not null,
        [fieldName] varchar(255) not null,
        [resultValue] varchar(max),
        [fileSize] int not null,
        [pathHint] varchar(255) not null default '',
        [fileExists] int not null
    );
    """